[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 distinct paa.assignment_id
from pay_payroll_actions xppa,
pay_payroll_actions rppa,
pay_assignment_actions rpac,
per_assignments_f paa,
per_assignment_extra_info aei1,
per_assignment_extra_info aei2,
hr_organization_information hoi
where xppa.payroll_action_id = p_payroll_action_id
and 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.payroll_id in ( select payroll_id
from pay_payrolls_f
where 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','Q')
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 hoi.organization_id = rpac.tax_unit_id
and hoi.org_information_context = 'SG_LEGAL_ENTITY'
and paa.assignment_id = aei1.assignment_id(+)
and aei1.information_type(+) = 'HR_MULTI_CSN_SG'
and substr(aei1.aei_information1(+),11,3)='PTE'
and paa.assignment_id = aei2.assignment_id(+)
and aei2.information_type(+) = 'HR_MULTI_CSN_SG'
and substr(aei2.aei_information1(+),11,3)='VCT'
and (hoi.org_information10=g_pact.csn
or aei1.aei_information1=g_pact.csn
or aei2.aei_information1=g_pact.csn)
and g_pact.start_date between nvl(fnd_date.canonical_to_date(aei1.aei_information2(+)),to_date('01/01/1900','DD/MM/YYYY')) and nvl(fnd_date.canonical_to_date(aei1.aei_information3(+)),to_date('31/12/4712','DD/MM/YYYY'))
and g_pact.start_date between nvl(fnd_date.canonical_to_date(aei2.aei_information2(+)),to_date('01/01/1900','DD/MM/YYYY')) and nvl(fnd_date.canonical_to_date(aei2.aei_information3(+)),to_date('31/12/4712','DD/MM/YYYY'));
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,
to_number(pay_core_utils.get_parameter('MONTH',ppa.legislative_parameters)) month_date,
to_number(pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppa.legislative_parameters)) legal_entity_id,
replace(pay_core_utils.get_parameter('CSN',ppa.legislative_parameters),'#',' ') csn,
to_date(pay_core_utils.get_parameter('MONTH',ppa.legislative_parameters)||'01','YYYYMMDD'),
last_day(to_date(pay_core_utils.get_parameter('MONTH',ppa.legislative_parameters)|| '01','YYYYMMDD'))
into g_pact
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id;
select substr(pap.per_information1,1,22),
substr(pap.employee_number,1,15),
fnd_date.date_to_canonical(pps.actual_termination_date),
decode(g_pact.month_date,to_char(pap.start_date,'YYYYMM'),'NEW','EE') emp_status,
rpac.assignment_action_id,
fnd_date.date_to_canonical(rppa.effective_date),
pap.per_information6,
substr(hou.name,1,80),
nvl(pap.per_information14,pap.national_identifier),
fnd_date.date_to_canonical(pps.date_start),
pap.person_id,
fnd_date.date_to_canonical(paa.effective_start_date),
nvl(aei1.aei_information1,hoi.org_information10),
aei2.aei_information1
from pay_assignment_actions pac,
pay_payroll_actions rppa,
pay_assignment_actions rpac,
per_assignments_f paa,
per_assignment_extra_info aei1,
per_assignment_extra_info aei2,
per_people_f pap,
per_periods_of_service pps,
hr_organization_units hou,
hr_organization_information hoi
where pac.assignment_action_id = p_assignment_action_id
and pac.assignment_id = rpac.assignment_id
and rpac.payroll_action_id = rppa.payroll_action_id
and rppa.action_type in ('R','Q')
and rpac.action_status = 'C'
and rppa.effective_date between g_pact.start_date
and g_pact.end_date
and pac.assignment_id = paa.assignment_id
and rppa.effective_date between paa.effective_start_date
and paa.effective_end_date
and paa.person_id = pap.person_id
and rppa.effective_date between pap.effective_start_date
and pap.effective_end_date
and pap.person_id = pps.person_id
and paa.period_of_service_id = pps.period_of_service_id
and paa.organization_id = hou.organization_id
and hoi.organization_id = g_pact.legal_entity_id
and hoi.org_information_context = 'SG_LEGAL_ENTITY'
and paa.assignment_id = aei1.assignment_id(+)
and aei1.information_type(+) = 'HR_MULTI_CSN_SG'
and substr(aei1.aei_information1(+),11,3)='PTE'
and paa.assignment_id = aei2.assignment_id(+)
and aei2.information_type(+) = 'HR_MULTI_CSN_SG'
and substr(aei2.aei_information1(+),11,3)='VCT'
and (hoi.org_information10=g_pact.csn
or aei1.aei_information1=g_pact.csn
or aei2.aei_information1=g_pact.csn)
and g_pact.start_date between nvl(fnd_date.canonical_to_date(aei1.aei_information2(+)),to_date('01/01/1900','DD/MM/YYYY')) and nvl(fnd_date.canonical_to_date(aei1.aei_information3(+)),to_date('31/12/4712','DD/MM/YYYY'))
and g_pact.start_date between nvl(fnd_date.canonical_to_date(aei2.aei_information2(+)),to_date('01/01/1900','DD/MM/YYYY')) and nvl(fnd_date.canonical_to_date(aei2.aei_information3(+)),to_date('31/12/4712','DD/MM/YYYY'))
order by rppa.action_sequence desc;
g_balance_value_tab.delete;
g_context_tab.delete;
g_detailed_bal_out_tab.delete;
hr_utility.set_location(l_proc_name||' Before Insert into pay_action_information',40);
insert into pay_action_information (
action_information_id,
action_context_id,
action_context_type,
effective_date,
source_id,
tax_unit_id,
action_information_category,
action_information1,
action_information2,
action_information3,
action_information4,
action_information5,
action_information6,
action_information7,
action_information8,
action_information9,
action_information10,
action_information11,
action_information12,
action_information13,
action_information14,
action_information15,
action_information16,
action_information17,
action_information18,
action_information19,
action_information20,
action_information21,
action_information22,
action_information23,
action_information24,
action_information25)
values (
pay_action_information_s.nextval,
p_assignment_action_id,
'AAC',
fnd_date.canonical_to_date(rec_action_info.action_information20),
l_person_id,
g_pact.legal_entity_id,
'SG CPF DETAILS',
rec_action_info.action_information1,
rec_action_info.action_information2,
rec_action_info.action_information3,
rec_action_info.action_information4,
rec_action_info.action_information5,
rec_action_info.action_information6,
rec_action_info.action_information7,
rec_action_info.action_information8,
rec_action_info.action_information9,
rec_action_info.action_information10,
rec_action_info.action_information11,
rec_action_info.action_information12,
rec_action_info.action_information13,
rec_action_info.action_information14,
rec_action_info.action_information15,
rec_action_info.action_information16,
rec_action_info.action_information17,
rec_action_info.action_information18,
rec_action_info.action_information19,
rec_action_info.action_information20,
rec_action_info.action_information21,
rec_action_info.action_information22,
rec_action_info.action_information23,
rec_action_info.action_information24,
rec_action_info.action_information25) ;
hr_utility.set_location(l_proc_name||' After Insert into pay_action_information',40);
select pay_core_utils.get_parameter('RETAIN_ARCHIVE_DATA',ppa.legislative_parameters)
into retain_archive_flag
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id ;
delete from pay_action_information
where action_context_id in ( select assignment_action_id
from pay_assignment_actions
where payroll_action_id = p_payroll_action_id )
and action_information_category = 'SG CPF DETAILS'
and action_context_type = 'AAC';
select pdb.defined_balance_id,pbt.balance_name
bulk collect into g_def_bal_id , g_def_bal_name
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_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbd.dimension_name = '_ASG_LE_MONTH'
and pbt.balance_name in ('CDAC Withheld',
'CPF Additional Earnings Eligible Comp',
'CPF Liability',
'CPF Ordinary Earnings Eligible Comp',
'CPF Withheld',
'Community Chest Withheld',
'ECF Withheld',
'FWL Liability',
'S Pass Liability',
'MBMF Withheld',
'SDL Liability',
'SINDA Withheld',
'Voluntary CPF Liability',
'Voluntary CPF Withheld',
'CPF Elig Comp 1984 Frozen Salary and Other Earnings') /*Bug 3501915 */
order by 2 ;