[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
' select distinct pap.person_id
from pay_payroll_actions ppa,
per_people_f pap
where ppa.payroll_action_id = :payroll_action_id
and pap.business_group_id = ppa.business_group_id
order by pap.person_id ' ;
select max(paa.assignment_id) assignment_id
from pay_payroll_actions rppa,
pay_assignment_actions rpac,
per_assignments_f paa
where paa.person_id between p_start_person_id
and p_end_person_id
and rppa.business_group_id = g_pact.business_group_id
and rppa.effective_date between g_pact.start_date
and g_pact.end_date
and rppa.action_type in ('R','B','I','Q','V')
and rpac.action_status = 'C'
and rppa.payroll_action_id = rpac.payroll_action_id
and rpac.tax_unit_id = g_pact.legal_entity_id
and rpac.assignment_id = paa.assignment_id
and rppa.effective_date between paa.effective_start_date
and paa.effective_end_date
and paa.person_id + 0 = nvl(g_pact.person_id,paa.person_id)
group by paa.person_id;
select pay_assignment_actions_s.nextval
from dual;
select ppa.report_type,
ppa.report_qualifier,
ppa.report_category,
ppa.business_group_id,
ppa.effective_date,
pay_core_utils.get_parameter('RETAIN_ARCHIVE_DATA',legislative_parameters),
pay_core_utils.get_parameter('PERSON_ID',legislative_parameters),
to_number(pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters)),
to_number(pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppa.legislative_parameters)) legal_entity_id,
to_date('01-01-'||pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY'),
to_date('31-12-'|| pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),'DD-MM-YYYY')
into g_pact
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
select pac.assignment_id,
pps.person_id,
pps.final_process_date
from pay_assignment_actions pac,
per_assignments_f paa,
per_periods_of_service pps
where pac.assignment_action_id = p_assignment_action_id
and paa.assignment_id = pac.assignment_id
and pps.person_id = paa.person_id
order by pps.date_start desc;
select substr(pap.per_information1,1,50) legal_name, --Legal Name
pap.employee_number employee_number, --Employee Number
nvl(pap.per_information14,pap.national_identifier) cpf_number, --CPF Number/National Identifier
pap.person_id person_id, --Person ID
nvl(addr.telephone_number_1,nvl(addr.telephone_number_2,addr.telephone_number_3)) telephone_number
from per_people_f pap,
per_addresses addr
where pap.person_id = c_person_id
and addr.person_id (+)= pap.person_id
and addr.primary_flag (+)= 'Y'
and pap.effective_start_date = (
select max(people1.effective_start_date)
from per_people_f people1
where people1.person_id = pap.person_id);
select /*+ ORDERED USE_NL(pacmax) */
max(pacmax.action_sequence) act_seq,
to_char(ppamax.effective_date,'MM')
from per_assignments_f paamax,
pay_assignment_actions pacmax,
pay_payroll_actions ppamax
where ppamax.business_group_id = c_business_group_id
and pacmax.tax_unit_id = c_legal_entity_id
and paamax.person_id = c_person_id
and paamax.assignment_id = pacmax.assignment_id
and ppamax.payroll_action_id = pacmax.payroll_action_id
and ppamax.effective_date between g_pact.start_date
and g_pact.end_date
and ppamax.action_type in ('R','B','I','Q','V')
group by to_char(ppamax.effective_date,'MM')
order by to_char(ppamax.effective_date,'MM') desc;
select /*+ ORDERED USE_NL(pac) */
pac.assignment_action_id assact_id,
decode(ppa.action_type,'V',ppa.effective_date,ppa.date_earned) date_earned,
pac.tax_unit_id tax_uid
from per_assignments_f paa,
pay_assignment_actions pac,
pay_payroll_actions ppa
where ppa.business_group_id = c_business_group_id
and pac.tax_unit_id = c_legal_entity_id
and paa.person_id = c_person_id
and paa.assignment_id = pac.assignment_id
and ppa.effective_date between g_pact.start_date
and g_pact.end_date
and ppa.payroll_action_id = pac.payroll_action_id
and pac.action_sequence = c_action_sequence;
select global_value
from ff_globals_f
where global_name = 'CPF_TOT_EARN_CAP_AMT'
and g_pact.end_date between effective_start_date and effective_end_date ;
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
tax_unit_id,
assignment_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6, -- Additional Earnings
action_information7, -- CPF Additional Earnings Toward Cap
action_information8) -- Additional Wages Cap Recalculated
values ( pay_action_information_s.nextval,
p_assignment_action_id,
'AAC',
g_pact.legal_entity_id,
l_assignment_id,
'SG AWCAP DETAILS',
'HEADER',
emp_details_rec.employee_number,
emp_details_rec.employee_name,
emp_details_rec.cpf_number,
emp_details_rec.telephone_number,
ytd_balance_rec(1).balance_value,
ytd_balance_rec(2).balance_value,
l_aw_cap_recalculated ) ;
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
tax_unit_id,
assignment_id,
effective_date,
action_information_category,
action_information1,
action_information2, -- Additional Earnings
action_information3, -- EE CPF AE
action_information4, -- EE CPF OE
action_information5, -- EE VOL CPF AE
action_information6, -- EE VOL CPF OE
action_information7, -- ER CPF AE
action_information8, -- ER CPF OE
action_information9, -- ER VOL CPF AE
action_information10, -- ER VOL CPF OE
action_information11 ) -- Ordinary Earnings
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAC',
g_pact.legal_entity_id,
l_assignment_id,
mtd_balance_rec(10).date_earned ,
'SG AWCAP DETAILS',
'DETAIL',
mtd_balance_rec(1).balance_value,
mtd_balance_rec(2).balance_value,
mtd_balance_rec(3).balance_value,
mtd_balance_rec(4).balance_value,
mtd_balance_rec(5).balance_value,
mtd_balance_rec(6).balance_value,
mtd_balance_rec(7).balance_value,
mtd_balance_rec(8).balance_value,
mtd_balance_rec(9).balance_value,
mtd_balance_rec(10).balance_value ) ;
select /*+ ORDERED USE_NL(paa) */
max(paa.action_sequence),
to_number(to_char(ppa.effective_date,'MM'))
from per_assignments_f paaf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paaf.person_id = p_person_id
and paa.assignment_id = paaf.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R','Q','B','V','I')
and ppa.date_earned between trunc(c_date_earned,'Y')
and last_day(c_date_earned)
group by to_number(to_char(ppa.effective_date,'MM'))
order by to_number(to_char(ppa.effective_date,'MM')) desc;
select /*+ ORDERED USE_NL(paa) */
paa.assignment_action_id,
ppa.effective_date
from per_assignments_f paaf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paaf.person_id = p_person_id
and paa.assignment_id = paaf.assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.action_sequence = c_action_sequence
and ppa.date_earned between trunc(c_date_earned,'Y')
and last_day(c_date_earned);
select pdb.defined_balance_id
from pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
where pbt.balance_name = p_balance_name
and pbd.dimension_name = p_dimension_name
and pbt.balance_type_id = pdb.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.legislation_code = 'SG';
select global_value
from ff_globals_f
where global_name = 'CPF_ORD_MONTH_CAP_AMT'
and c_date_earned between effective_start_date and effective_end_date;
g_context_tab.delete;
g_detailed_bal_out_tab.delete;
select pdb.defined_balance_id def_bal_id,
pbt.balance_name
bulk collect into
g_ytd_def_bal_tbl,
g_bal_name_tbl
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.legislation_code = 'SG'
and pbd.legislation_code = pbt.legislation_code
and pdb.legislation_code = pbt.legislation_code
and pbt.balance_name in ( 'Additional Earnings',
'CPF Additional Earnings Toward Cap' )
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_name = '_PER_LE_YTD'
order by pbt.balance_name;
select pdb.defined_balance_id def_bal_id
bulk collect into
g_mtd_def_bal_tbl
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.legislation_code = 'SG'
and pbd.legislation_code = pbt.legislation_code
and pdb.legislation_code = pbt.legislation_code
and pbt.balance_name in ('Employee CPF Contributions Additional Earnings',
'Employee CPF Contributions Ordinary Earnings',
'Employer CPF Contributions Additional Earnings',
'Employer CPF Contributions Ordinary Earnings',
'Employee Vol CPF Contributions Additional Earnings',
'Employee Vol CPF Contributions Ordinary Earnings' ,
'Employer Vol CPF Contributions Additional Earnings',
'Employer Vol CPF Contributions Ordinary Earnings',
'Additional Earnings',
'Ordinary Earnings')
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_name = '_PER_LE_MONTH'
order by pbt.balance_name;