[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_sql := 'select distinct person_id ' ||
'from per_people_f ppf, ' ||
'pay_payroll_actions ppa ' ||
'where ppa.payroll_action_id = :payroll_action_id ' ||
'and ppa.business_group_id = ppf.business_group_id ' ||
'order by ppf.person_id';
select pay_core_utils.get_parameter('SETUP_ACTION_ID',legislative_parameters)
,pay_core_utils.get_parameter('PERSON_ID',legislative_parameters)
,pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters)
from pay_payroll_actions
where payroll_action_id = c_payroll_Action_id;
select pay_assignment_actions_s.nextval
from dual;
select distinct pai.assignment_id
from pay_action_information pai
where pai.action_context_id = c_setup_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'SG_IRAS_AMEND_SETUP'
and decode(c_assignment_set_id,null,'Y',
decode(hr_assignment_set.ASSIGNMENT_IN_SET(c_assignment_set_id,pai.assignment_id),'Y','Y','N')) = 'Y'
and action_information2 between p_start_person_id and p_end_person_id
and action_information2 = nvl(c_person_id,action_information2)
and exists (SELECT 1
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year);
select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (
SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = p_assignment_id;
select intl.locking_action_id report_action_id
from pay_action_interlocks intl
where intl.locked_action_id = p_archive_action_id
and exists
( select null
from per_assignments_f paf,
pay_assignment_actions mcl,
pay_payroll_actions mpl
where paf.assignment_id = mcl.assignment_id
and mpl.payroll_action_id = mcl.payroll_action_id
and mcl.assignment_Action_id = intl.locking_action_id
and mpl.effective_date between g_basis_start and g_basis_end
and pay_core_utils.get_parameter('LEGAL_ENTITY_ID',mpl.legislative_parameters) = g_legal_entity_id
and mpl.report_type in ( 'SG_A8B','SG_IR8A','SG_IR8S','SG_A8A','SG_A_A8A' )
and mpl.action_type = 'X'
and mcl.action_status = 'C'
group by paf.assignment_id
) ;
select pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters),
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'),
pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),
pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters),
pay_core_utils.get_parameter('SETUP_ACTION_ID',legislative_parameters),
report_type
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
select org_information19
from hr_organization_information,
pay_payroll_actions
where org_information_context ='SG_LEGAL_ENTITY'
and organization_id = pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters)
and payroll_action_id = p_payroll_action_id;
select pap.person_id,
nvl(pap.national_identifier,pap.per_information12),
pac.assignment_id
from pay_assignment_actions pac,
per_assignments_f paa,
per_people_f pap
where pac.assignment_action_id = c_assignment_action_id
and paa.assignment_id = pac.assignment_id
and paa.person_id = pap.person_id ;
select fue.user_entity_id,
pdb.defined_balance_id def_bal_id
from ff_user_entities fue,
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
and fue.legislation_code = 'SG'
and pbt.legislation_code = 'SG'
and pbd.legislation_code = pbt.legislation_code
and pdb.legislation_code = pbt.legislation_code
and pbt.balance_name like 'A8A%'
and upper(pbt.reporting_name) not like '%OBSOLETE%'
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 asc;
select nvl(pei_information2, l_detailed_bal_out_tab(1).balance_value), --A8A_MOA_500
nvl(pei_information3, l_detailed_bal_out_tab(2).balance_value), --A8A_MOA_501
nvl(pei_information4, l_detailed_bal_out_tab(3).balance_value), --A8A_MOA_502
nvl(pei_information5, l_detailed_bal_out_tab(7).balance_value), --A8A_MOA_506
nvl(pei_information6, l_detailed_bal_out_tab(8).balance_value), --A8A_MOA_507
nvl(pei_information7, l_detailed_bal_out_tab(9).balance_value), --A8A_MOA_508
nvl(pei_information8, l_detailed_bal_out_tab(10).balance_value),--A8A_MOA_509
nvl(pei_information9, l_detailed_bal_out_tab(11).balance_value),--A8A_MOA_510
nvl(pei_information10,l_detailed_bal_out_tab(12).balance_value),--A8A_MOA_511
nvl(pei_information11,l_detailed_bal_out_tab(13).balance_value),--A8A_MOA_512
nvl(pei_information12,l_detailed_bal_out_tab(14).balance_value),--A8A_MOA_513
nvl(pei_information13,l_detailed_bal_out_tab(15).balance_value),--A8A_MOA_514
nvl(pei_information14,l_detailed_bal_out_tab(17).balance_value),--A8A_MOA_516
nvl(pei_information15,l_detailed_bal_out_tab(26).balance_value),--A8A_MOA_525
nvl(pei_information16,l_detailed_bal_out_tab(27).balance_value),--A8A_MOA_526
nvl(pei_information17,l_detailed_bal_out_tab(28).balance_value),--A8A_MOA_527
nvl(pei_information22,l_detailed_bal_out_tab(29).balance_value),--A8A_MOA_528
nvl(pei_information23,l_detailed_bal_out_tab(30).balance_value),--A8A_MOA_529
nvl(pei_information24,l_detailed_bal_out_tab(31).balance_value),--A8A_MOA_530
nvl(pei_information18,l_detailed_bal_out_tab(32).balance_value),--A8A_MOA_531
nvl(pei_information19,l_detailed_bal_out_tab(33).balance_value),--A8A_MOA_532
nvl(pei_information20,l_detailed_bal_out_tab(34).balance_value),--A8A_MOA_533
nvl(pei_information21,l_detailed_bal_out_tab(35).balance_value) --A8A_MOA_534
from per_people_extra_info pae
where person_id = p_person_id
and information_type = 'HR_A8A_BENEFITS_IN_KIND_SG'
and pei_information1 = g_basis_year;
select nvl(pei_information2, l_detailed_bal_out_tab(45).balance_value), --A8A_QTY_304
nvl(pei_information3, l_detailed_bal_out_tab(46).balance_value), --A8A_QTY_305
nvl(pei_information4, l_detailed_bal_out_tab(47).balance_value), --A8A_QTY_306
nvl(pei_information5, l_detailed_bal_out_tab(48).balance_value), --A8A_QTY_307
nvl(pei_information6, l_detailed_bal_out_tab(49).balance_value), --A8A_QTY_308
nvl(pei_information7, l_detailed_bal_out_tab(50).balance_value), --A8A_QTY_309
nvl(pei_information8, l_detailed_bal_out_tab(51).balance_value), --A8A_QTY_310
nvl(pei_information9, l_detailed_bal_out_tab(52).balance_value), --A8A_QTY_311
nvl(pei_information10,l_detailed_bal_out_tab(53).balance_value), --A8A_QTY_312
nvl(pei_information11,l_detailed_bal_out_tab(54).balance_value), --A8A_QTY_313
nvl(pei_information12,l_detailed_bal_out_tab(55).balance_value), --A8A_QTY_314
nvl(pei_information13,l_detailed_bal_out_tab(56).balance_value), --A8A_QTY_315
nvl(pei_information14,l_detailed_bal_out_tab(57).balance_value), --A8A_QTY_316
nvl(pei_information15,l_detailed_bal_out_tab(58).balance_value), --A8A_QTY_317
nvl(pei_information16,l_detailed_bal_out_tab(59).balance_value), --A8A_QTY_318
nvl(pei_information17,l_detailed_bal_out_tab(60).balance_value), --A8A_QTY_319
nvl(pei_information18,l_detailed_bal_out_tab(61).balance_value), --A8A_QTY_320
nvl(pei_information19,l_detailed_bal_out_tab(18).balance_value), --A8A_MOA_517
nvl(pei_information20,l_detailed_bal_out_tab(19).balance_value), --A8A_MOA_518
nvl(pei_information21,l_detailed_bal_out_tab(20).balance_value), --A8A_MOA_519
nvl(pei_information22,l_detailed_bal_out_tab(21).balance_value), --A8A_MOA_520
nvl(pei_information23,l_detailed_bal_out_tab(22).balance_value), --A8A_MOA_521
nvl(pei_information24,l_detailed_bal_out_tab(23).balance_value), --A8A_MOA_522
nvl(pei_information25,l_detailed_bal_out_tab(24).balance_value), --A8A_MOA_523
nvl(pei_information26,l_detailed_bal_out_tab(25).balance_value) --A8A_MOA_524
from per_people_extra_info pae
where person_id = p_person_id
and information_type ='HR_A8A_FURN_EXP_SG'
and pei_information1 = g_basis_year;
select nvl(pei_information2, l_detailed_bal_out_tab(62).balance_value), --A8A_QTY_321
nvl(pei_information3, l_detailed_bal_out_tab(63).balance_value), --A8A_QTY_322
nvl(pei_information4, l_detailed_bal_out_tab(64).balance_value), --A8A_QTY_323
nvl(pei_information5, l_detailed_bal_out_tab(65).balance_value), --A8A_QTY_324
nvl(pei_information6, l_detailed_bal_out_tab(66).balance_value), --A8A_QTY_325
nvl(pei_information7, l_detailed_bal_out_tab(67).balance_value), --A8A_QTY_326
nvl(pei_information8, l_detailed_bal_out_tab(68).balance_value), --A8A_QTY_327
nvl(pei_information9, l_detailed_bal_out_tab(69).balance_value), --A8A_QTY_328
nvl(pei_information10,l_detailed_bal_out_tab(40).balance_value), --A8A_MOA_539
nvl(pei_information11,l_detailed_bal_out_tab(41).balance_value), --A8A_QTY_300
nvl(pei_information12,l_detailed_bal_out_tab(42).balance_value), --A8A_QTY_301
nvl(pei_information13,l_detailed_bal_out_tab(43).balance_value), --A8A_QTY_302
nvl(pei_information14,l_detailed_bal_out_tab(44).balance_value), --A8A_QTY_303
nvl(pei_information15,l_detailed_bal_out_tab(6).balance_value) --A8A_MOA_505
, nvl(pei_information16,l_detailed_bal_out_tab(70).balance_value) --A8A_QTY_329, bug 5435088
from per_people_extra_info pae
where person_id = p_person_id
and information_type ='HR_A8A_HOTEL_ACCOM_SG'
and pei_information1 = g_basis_year;
select global_value
from ff_globals_f
where global_name = p_global_name;
l_balance_value_tab.delete;
l_context_tab.delete;
l_detailed_bal_out_tab.delete;
l_balance_value_tab1.delete;
l_detailed_bal_out_tab1.delete;
l_balance_value_tab2.delete;
l_detailed_bal_out_tab2.delete;
l_balance_value_tab3.delete;
l_detailed_bal_out_tab3.delete;
l_balance_value_tab4.delete;
l_detailed_bal_out_tab4.delete;
l_balance_value_tab5.delete;
l_detailed_bal_out_tab5.delete;
select fue.user_entity_id,
pdb.defined_balance_id def_bal_id
from ff_user_entities fue,
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
and fue.legislation_code = 'SG'
and pbt.legislation_code = 'SG'
and pbd.legislation_code = pbt.legislation_code
and pdb.legislation_code = pbt.legislation_code
and ( pbt.balance_name in ('Voluntary CPF Liability','CPF Liability',
'Voluntary CPF Withheld','CPF Withheld',
'Employee CPF Contributions Additional Earnings',
'Employee CPF Contributions Ordinary Earnings',
'Employer CPF Contributions Additional Earnings',
'Employer CPF Contributions Ordinary Earnings',
'Additional Earnings','Ordinary Earnings',
'Employer Vol CPF Contributions Ordinary Earnings',
'Employee Vol CPF Contributions Ordinary Earnings',
'Employer Vol CPF Contributions Additional Earnings',
'Employee Vol CPF Contributions Additional Earnings')
or
( pbt.balance_name like 'IR8S%' ) )
and upper(pbt.reporting_name) not like '%OBSOLETE%'
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';
select fue.user_entity_id,
pdb.defined_balance_id def_bal_id
from ff_user_entities fue,
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_YTD'
and fue.legislation_code = 'SG'
and pbt.legislation_code = 'SG'
and pbd.legislation_code = pbt.legislation_code
and pdb.legislation_code = pbt.legislation_code
and pbt.balance_name like 'IR8A%'
and upper(pbt.reporting_name) not like '%OBSOLETE%'
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';
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.effective_date between to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
and to_date('31-12-'||c_basis_year,'DD-MM-YYYY')
and ppamax.payroll_action_id = pacmax.payroll_action_id
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');
select /*+ ORDERED USE_NL(pac) */
pac.assignment_action_id assact_id,
decode(ppa.action_type,'V',fnd_date.date_to_canonical(ppa.effective_date),fnd_date.date_to_canonical(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 to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
and to_date('31-12-'||c_basis_year,'DD-MM-YYYY')
and ppa.payroll_action_id = pac.payroll_action_id
and pac.action_sequence = c_action_sequence;
select fue.user_entity_id,
pdb.defined_balance_id def_bal_id
from ff_user_entities fue,
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where fue.user_entity_name = 'X_' || upper(replace(pbt.balance_name,' ','_')) || '_PER_LE_MONTH'
and fue.legislation_code = 'SG'
and 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',
'Additional Earnings','Ordinary Earnings',
'Employer Vol CPF Contributions Ordinary Earnings',
'Employee Vol CPF Contributions Ordinary Earnings',
'Employer Vol CPF Contributions Additional Earnings',
'Employee Vol CPF Contributions Additional Earnings',
'IR8S_MOA_403','IR8S_MOA_407','CPF Liability' )
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';
t_archive_items.delete;
t_user_entity_id.delete;
t_archive_value.delete;
t_date_earned.delete;
ytd_a8a_balance_rec.delete;
g_balance_value_tab.delete;
g_context_tab.delete;
g_detailed_bal_out_tab.delete;
g_balance_value_tab.delete;
g_context_tab.delete;
g_detailed_bal_out_tab.delete;
g_balance_value_tab.delete;
g_context_tab.delete;
g_detailed_bal_out_tab.delete;
g_balance_value_tab.delete;
g_detailed_bal_out_tab.delete;
select context_id
into l_asac_cont_id
from ff_contexts
where context_name = 'ASSIGNMENT_ACTION_ID' ;
select context_id
into l_tax_cont_id
from ff_contexts
where context_name = 'TAX_UNIT_ID' ;
select context_id
into l_date_cont_id
from ff_contexts
where context_name = 'DATE_EARNED' ;
select user_entity_name
into l_name_ue
from ff_user_entities where user_entity_id = t_user_entity_id(i);
SELECT assignment_id
INTO l_assignment_id
FROM pay_assignment_Actions paa
WHERE paa.assignment_action_id = p_assignment_action_id;
SELECT nvl(sum(value),0)
INTO l_temp_value
FROM ff_archive_items arch
WHERE arch.user_entity_id = t_user_entity_id(i)
AND arch.context1 IN( select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = l_assignment_id)--ORGLEVELPREVSUMITTEDvalues
AND EXISTS (SELECT 1
FROM ff_archive_item_contexts con1
WHERE con1.archive_item_id = arch.archive_item_id
AND con1.context = p_tax_unit_id
AND con1.sequence_no =2)
AND EXISTS (SELECT 1
FROM ff_archive_item_contexts con2
WHERE con2.archive_item_id = arch.archive_item_id
AND fnd_date.canonical_to_date(con2.context) = fnd_date.canonical_to_date(t_date_earned(i))
AND con2.sequence_no =3);
select user_entity_name
into l_name_ue
from ff_user_entities
where user_entity_id = t_user_entity_id_ir8a(counter);
select sum(value)
into l_temp_value
from ff_archive_items arch
where arch.user_entity_id = t_user_entity_id_ir8a(counter)
and arch.context1 IN( select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = l_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = p_tax_unit_id
and con1.sequence_no =2);
select user_entity_name
into l_name_ue
from ff_user_entities
where user_entity_id = t_user_entity_id_ir8s(counter);
select sum(value)
into l_temp_value
from ff_archive_items arch
where arch.user_entity_id = t_user_entity_id_ir8s(counter)
and arch.context1 IN( select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = l_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = p_tax_unit_id
and con1.sequence_no =2);
select user_entity_name
into l_name_ue
from ff_user_entities
where user_entity_id = t_user_entity_id_a8a(i);
select sum(value)
into l_temp_value
from ff_archive_items arch
where arch.user_entity_id = t_user_entity_id_a8a(i)
and arch.context1 IN( select paa.assignment_action_id
-- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = l_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = p_tax_unit_id
and con1.sequence_no =2);
select ue.user_entity_id
bulk collect into t_amend_ue_id
from ff_user_entities ue
where ue.user_entity_name in ('X_IR8A_AMEND_INDICATOR',
'X_A8B_AMEND_INDICATOR',
'X_IR8S_AMEND_INDICATOR',
'X_A8A_AMEND_INDICATOR')
order by ue.user_entity_name;
insert into ff_archive_items
( archive_item_id,
user_entity_id,
context1,
value,
archive_type )
values
( ff_archive_items_s.nextval,
t_amend_ue_id(counter),
p_assignment_action_id,
t_flag(counter),
'AAP' )
returning archive_item_id bulk collect into t_archive_items ;
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items(counter),
1,
p_assignment_action_id,
l_asac_cont_id );
t_archive_items.delete;
insert into ff_archive_items
( archive_item_id,
user_entity_id,
context1,
value,
archive_type )
values
( ff_archive_items_s.nextval,
t_user_entity_id(counter),
p_assignment_action_id,
t_archive_value(counter) - t_amend_value(counter) ,-- T_AMEND-VALUE,
'AAP' )
returning archive_item_id bulk collect into t_archive_items ;
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items(counter),
1,
p_assignment_action_id,
l_asac_cont_id );
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items(counter),
2,
p_tax_unit_id,
l_tax_cont_id );
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items(counter),
3,
t_date_earned(counter),
l_date_cont_id );
t_archive_items.delete;
insert into ff_archive_items
( archive_item_id,
user_entity_id,
context1,
value,
archive_type )
values
( ff_archive_items_s.nextval,
t_user_entity_id_ir8a(counter),
p_assignment_action_id,
t_archive_value_ir8a(counter) - t_amend_value_ir8a(counter),
'AAP' )
returning archive_item_id bulk collect into t_archive_items_ir8a ;
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items_ir8a(counter),
1,
p_assignment_action_id,
l_asac_cont_id );
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items_ir8a(counter) ,
2,
p_tax_unit_id,
l_tax_cont_id );
t_archive_items_ir8a.delete;
insert into ff_archive_items
( archive_item_id,
user_entity_id,
context1,
value,
archive_type )
values
( ff_archive_items_s.nextval,
t_user_entity_id_ir8s(counter),
p_assignment_action_id,
t_archive_value_ir8s(counter) - t_amend_value_ir8s(counter) ,
'AAP' )
returning archive_item_id bulk collect into t_archive_items_ir8s ;
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items_ir8s(counter),
1,
p_assignment_action_id,
l_asac_cont_id );
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items_ir8s(counter),
2,
p_tax_unit_id,
l_tax_cont_id );
t_archive_items_ir8s.delete;
insert into ff_archive_items
( archive_item_id,
user_entity_id,
context1,
value,
archive_type )
values
( ff_archive_items_s.nextval,
t_user_entity_id_a8a(counter),
p_assignment_action_id,
t_archive_value_a8a(counter) - t_amend_value_a8a(counter) ,
'AAP' )
returning archive_item_id bulk collect into t_archive_items_a8a ;
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items_a8a(counter),
1,
p_assignment_action_id,
l_asac_cont_id );
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items_a8a(counter),
2,
p_tax_unit_id,
l_tax_cont_id );
t_archive_items_a8a.delete;
select arch.value,ue.user_entity_id
bulk collect into t_orig_value,t_orig_user_entity_id
from ff_user_entities ue ,
ff_archive_items arch
where ue.user_entity_name in ('X_PER_NATIONAL_IDENTIFIER',
'X_PER_SEX',
'X_PER_DATE_OF_BIRTH',
'X_PER_ADR_TYPE',
'X_PER_ADR_COUNTRY_CODE',
'X_PER_ADR_LINE_1',
'X_PER_ADR_LINE_2',
'X_PER_ADR_LINE_3',
'X_PER_ADR_POSTAL_CODE',
'X_PER_CQ_ADR_LINE_1',
'X_PER_CQ_ADR_LINE_2',
'X_PER_CQ_ADR_LINE_3',
'X_PER_CQ_DATE_FROM',
'X_PER_CQ_DATE_TO',
'X_EMP_TERM_DATE',
'X_EMP_HIRE_DATE',
'X_PEOPLE_FLEXFIELD_SG_SG_LEGAL_NAME',
'X_PEOPLE_FLEXFIELD_SG_SG_PP_COUNTRY',
'X_PEOPLE_FLEXFIELD_SG_SG_PERMIT_TYPE',
'X_PEOPLE_FLEXFIELD_SG_SG_INCOME_TAX_NUMBER',
'X_PER_PERMIT_STATUS_INDICATOR'
,'X_PER_EE_PAYEE_ID_CHECK',
'X_PEOPLE_FLEXFIELD_SG_SG_PAYEE_ID_TYPE',
'X_PER_NATIONALITY_CODE',
'X_HR_IR8A_INDICATORS_SG_PER_SECTION_45_APPLICABLE',
'X_HR_IR8A_INDICATORS_SG_PER_INCOME_TAX_BORNE_BY_EMPLOYER',
'X_HR_IR8A_INDICATORS_SG_PER_IR8S_APPLICABLE',
'X_HR_IR8A_INDICATORS_SG_EXEMPT'
,'X_HR_IR8A_INDICATORS_SG_APPR_IRAS',
'X_HR_IR8A_INDICATORS_SG_DATE_OF_APPR_IRAS',
'X_HR_IR8A_FURTHER_DETAILS_SG_PER_RETIREMENT_FUND',
'X_HR_IR8A_FURTHER_DETAILS_SG_PER_DESIGNATED_PENSION',
'X_HR_IRAS_ADDITIONAL_INFO_SG_PER_ADDITIONAL_INFORMATION',
'X_HR_IR8S_INDICATORS_SG_ASG_VOLUNTARY_CPF_OBLIGATORY',
'X_HR_IR8S_INDICATORS_SG_ASG_APPR_CPF',
'X_IR8A_MOA_369_DATE',
'X_HR_IR8A_FURTHER_DETAILS_SG_NAME_OF_BANK',
'X_ASG_DESIGNATION',
'X_HR_IR8S_INDICATORS_SG_ASG_CPF_OVERSEAS_POST_OBLIGATORY',
'X_ASG_OVERSEAS_DATE_FROM',
'X_ASG_OVERSEAS_DATE_TO',
'X_PER_PAYROLL_DATE',
'X_SG_LEGAL_ENTITY_SG_LEGAL_ENTITY_NAME',
'X_SG_LEGAL_ENTITY_SG_ER_INCOME_TAX_NUMBER',
'X_SG_LEGAL_ENTITY_SG_ER_OHQ_STATUS',
'X_SG_LEGAL_ENTITY_SG_ER_IRAS_CATEGORY',
'X_SG_LEGAL_ENTITY_SG_ER_TELEPHONE_NUMBER',
'X_SG_LEGAL_ENTITY_SG_ER_PAYER_ID',
'X_SG_LEGAL_ENTITY_SG_ER_JOB_DES_TYPE',
'X_SG_LEGAL_ENTITY_SG_ER_AUTH_PERSON_EMAIL',
'X_SG_LEGAL_ENTITY_SG_ER_DIVISION',
'X_SG_LEGAL_ENTITY_SG_ER_ID_CHECK',
'X_SG_LEGAL_ENTITY_SG_A8B_INCORP_DATE',
'X_IR8A_MOA_265_DATE_FROM',
'X_IR8A_MOA_265_DATE_TO',
'X_IR8A_MOA_265_INDICATOR',
'X_IR8A_MOA_340_DATE',
'X_ADDITIONAL_EARNINGS_DATE')
and ue.user_entity_id = arch.user_entity_id
AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
-- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type='SG_IRAS_ARCHIVE'
and paa.assignment_id = l_assignment_id) ;
insert into ff_archive_items
( archive_item_id,
user_entity_id,
context1,
value,
archive_type )
values
( ff_archive_items_s.nextval,
t_orig_user_entity_id(counter),
p_assignment_action_id,
t_orig_value(counter),
'AAP' )
returning archive_item_id bulk collect into t_archive_items_orig ;
insert into ff_archive_item_contexts
( archive_item_id,
sequence_no,
context,
context_id )
values
( t_archive_items_orig(counter),
1,
p_assignment_action_id,
l_asac_cont_id );
t_user_entity_id.delete;
t_archive_value.delete;
t_date_earned.delete;
t_archive_value_ir8a.delete;
t_user_entity_id_ir8a.delete;
t_archive_value_ir8s.delete;
t_user_entity_id_ir8s.delete;
t_archive_value_a8a.delete;
t_user_entity_id_a8a.delete;
select distinct pei.person_extra_info_id,
pei.pei_information1 stock_option,
pei.pei_information3 exercise_price,
pei.pei_information4 market_exercise_value,
to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||
to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,
pei.pei_information6 shares_acquired,
hoi1.org_information1 name_of_company,
hoi1.org_information4 RCB,
hoi1.org_information15 company_type,
pei2.pei_information2 market_grant_value,
pei2.pei_information5 grant_type,
decode(pei2.pei_information3, null, null, to_char(fnd_date.canonical_to_date(pei2.pei_information3),'YYYY')||'/'||
to_char(fnd_date.canonical_to_date(pei2.pei_information3),'MM')||'/'||to_char(fnd_date.canonical_to_date(pei2.pei_information3),'DD')) grant_date,
pei2.pei_information4 shares_granted
from per_all_people_f pap,
per_people_extra_info pei,
per_people_extra_info pei2,
hr_all_organization_units hou,
hr_organization_information hoi2,
hr_organization_information hoi1
where pap.person_id = c_person_id
and pap.person_id = pei.person_id
and pei.information_type = 'HR_STOCK_EXERCISE_SG'
and pap.person_id = pei2.person_id
and pei.pei_information2 = pei2.person_extra_info_id
and pei2.information_type = 'HR_STOCK_GRANT_SG'
and pei2.pei_information1 = hou.organization_id
and hou.organization_id = hoi1.organization_id(+)
and hou.organization_id = hoi2.organization_id
and hoi1.org_information_context||'' = 'SG_LEGAL_ENTITY'
and hoi2.org_information_context||'' = 'CLASS'
and hoi2.org_information1 = 'HR_LEGAL'
and hoi2.org_information2 = 'Y'
and to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY') = to_char(c_basis_end,'YYYY') /* Bug#2684645 */
and (pap.effective_start_date <= c_basis_end and pap.effective_end_date >= c_basis_start);
select distinct con2.context person_extra_info_id
from ff_archive_items arch,
ff_archive_item_contexts con2
where arch.user_entity_id = c_user_entity_id
and arch.context1 IN(
select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (
SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = c_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = c_tax_unit_id
and con1.sequence_no =2)
and arch.archive_item_id = con2.archive_item_id
and con2.sequence_no = 3
and not exists (select 1
from per_people_extra_info pei
where pei.person_id = c_person_id
and pei.person_extra_info_id = con2.context);
SELECT assignment_id
INTO l_assignment_id
FROM pay_assignment_Actions paa
WHERE paa.assignment_action_id = p_assignment_action_id;
select arch.value
into v_er_incorp_date
from ff_user_entities ue ,
ff_archive_items arch
where ue.user_entity_name = 'X_SG_LEGAL_ENTITY_SG_A8B_INCORP_DATE'
and ue.user_entity_id = arch.user_entity_id
AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
-- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (
SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type='SG_IRAS_ARCHIVE'
and paa.assignment_id = l_assignment_id);
select ue.user_entity_id, ue.user_entity_name
bulk collect into t_user_entity_id_a8b, t_user_entity_name_a8b
from ff_user_entities ue
where ue.user_entity_name in (
'X_A8B_COMPANY'
,'X_A8B_COMPANY_TYPE'
,'X_A8B_EXERCISED_DATE'
,'X_A8B_EXER_PRICE'
,'X_A8B_GRANTED_DATE'
,'X_A8B_GRANT_TYPE'
,'X_A8B_MK_EXER_VALUE'
,'X_A8B_MK_GRANT_VALUE'
,'X_A8B_OPTION'
,'X_A8B_RCB'
,'X_A8B_SHARES_ACQUIRED')
order by ue.user_entity_name desc;
select sum(value)
into l_temp_value
from ff_archive_items arch
where arch.user_entity_id = t_user_entity_id_a8b(1)
and arch.context1 IN(
select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (
SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = l_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = p_tax_unit_id
and con1.sequence_no =2)
and exists (select 1
from ff_archive_item_contexts con2
where con2.archive_item_id = arch.archive_item_id
and con2.context = shares_removed_rec.person_extra_info_id
and con2.sequence_no = 3);
select arch.value
bulk collect into t_amend_value_a8b
from ff_user_entities ue ,
ff_archive_items arch
where ue.user_entity_name in ('X_A8B_COMPANY'
,'X_A8B_RCB'
,'X_A8B_COMPANY_TYPE'
,'X_A8B_OPTION'
,'X_A8B_MK_EXER_VALUE'
,'X_A8B_MK_GRANT_VALUE'
,'X_A8B_SHARES_ACQUIRED'
,'X_A8B_EXER_PRICE'
,'X_A8B_EXERCISED_DATE'
,'X_A8B_GRANTED_DATE'
,'X_A8B_GRANT_TYPE')
and ue.user_entity_id = arch.user_entity_id
AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
-- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type='SG_IRAS_ARCHIVE'
and paa.assignment_id = l_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = p_tax_unit_id
and con1.sequence_no =2)
and exists (select 1
from ff_archive_item_contexts con2
where con2.archive_item_id = arch.archive_item_id
and con2.context = shares_removed_rec.person_extra_info_id
and con2.sequence_no = 3)
order by ue.user_entity_name desc;
select sum(value)
into l_temp_value
from ff_archive_items arch
where arch.user_entity_id = t_user_entity_id_a8b(1)
and arch.context1 IN(
select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (
SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = l_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = p_tax_unit_id
and con1.sequence_no =2)
and exists (select 1
from ff_archive_item_contexts con2
where con2.archive_item_id = arch.archive_item_id
and con2.context = share_rec.person_extra_info_id
and con2.sequence_no = 3);
select distinct aei.assignment_extra_info_id,
aei.aei_information2 add_wages,
aei.aei_information3 add_wages_from_date,
aei.aei_information4 add_wages_to_date,
aei.aei_information5 pay_date_add_wages,
aei.aei_information6 er_cpf,
aei.aei_information7 er_cpf_interest,
aei.aei_information8 er_cpf_date,
aei.aei_information9 ee_cpf,
aei.aei_information10 ee_cpf_interest,
aei.aei_information11 ee_cpf_date
from per_assignments_f ass,
per_assignment_extra_info aei,
hr_soft_coding_keyflex hsc
where ass.person_id = c_person_id
and ass.assignment_id = aei.assignment_id
and ass.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_tax_unit_id
and ass.business_group_id = c_business_group_id
and ass.assignment_type = 'E' /* Bug 5033609 */
and aei.information_type = 'HR_CPF_CLAIMED_SG'
and aei.aei_information1 = to_char(c_basis_end,'YYYY')
and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
and (ass.effective_start_date <= c_basis_end
and ass.effective_end_date >= c_basis_start);
select distinct con2.context assignment_extra_info_id
from ff_archive_items arch,
ff_archive_item_contexts con2
where arch.user_entity_id = c_user_entity_id
and arch.context1 IN(
select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (
SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = c_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = c_tax_unit_id
and con1.sequence_no =2)
and arch.archive_item_id = con2.archive_item_id
and con2.sequence_no = 3
and not exists (select 1
from per_assignment_extra_info aei
where aei.assignment_id = c_assignment_id
and aei.assignment_extra_info_id = con2.context);
select ue.user_entity_id, ue.user_entity_name
bulk collect into t_user_entity_id_ir8s_c, t_user_entity_name_ir8s_c
from ff_user_entities ue
where ue.user_entity_name in ('X_MOA410',
'X_MOA411',
'X_MOA412',
'X_MOA413',
'X_MOA414')
order by ue.user_entity_name;
SELECT assignment_id
INTO l_assignment_id
FROM pay_assignment_Actions paa
WHERE paa.assignment_action_id = p_assignment_action_id;
select sum(value)
into l_temp_value
from ff_archive_items arch
where arch.user_entity_id = t_user_entity_id_ir8s_c(counter)
and arch.context1 IN(
select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (
SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = l_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = p_tax_unit_id
and con1.sequence_no =2)
and exists (select 1
from ff_archive_item_contexts con2
where con2.archive_item_id = arch.archive_item_id
and con2.context = ir8s_c_removed_rec.assignment_extra_info_id
and con2.sequence_no = 3);
select arch.value
bulk collect into t_amend_value_ir8s_c1
from ff_user_entities ue ,
ff_archive_items arch
where ue.user_entity_name in ('X_DTM502'
,'X_DTM503'
,'X_DTM504'
,'X_DTM505'
,'X_DTM506')
and ue.user_entity_id = arch.user_entity_id
AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
-- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.report_type='SG_IRAS_ARCHIVE'
and paa.assignment_id = l_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = p_tax_unit_id
and con1.sequence_no =2)
and exists (select 1
from ff_archive_item_contexts con2
where con2.archive_item_id = arch.archive_item_id
and con2.context = ir8s_c_removed_rec.assignment_extra_info_id
and con2.sequence_no = 3)
order by ue.user_entity_name desc;
t_amend_value_ir8s_c.delete;
select user_entity_name
into l_name_ue
from ff_user_entities where user_entity_id = t_user_entity_id_ir8s_c(counter);
select sum(value)
into l_temp_value
from ff_archive_items arch
where arch.user_entity_id = t_user_entity_id_ir8s_c(counter)
and arch.context1 IN(
select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id in (
SELECT org_information2
FROM hr_organization_information
WHERE org_information_context = 'SG_IRAS_DETAILS'
AND organization_id = g_legal_entity_id
AND org_information1 = g_basis_year)
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = l_assignment_id)
and exists (select 1
from ff_archive_item_contexts con1
where con1.archive_item_id = arch.archive_item_id
and con1.context = p_tax_unit_id
and con1.sequence_no =2)
and exists (select 1
from ff_archive_item_contexts con2
where con2.archive_item_id = arch.archive_item_id
and con2.context = ir8s_c_rec.assignment_extra_info_id
and con2.sequence_no = 3);
t_amend_value_ir8s_c.delete;
t_archive_value_ir8s_c.delete;
select user_entity_id
from ff_user_entities
where user_entity_name = c_user_entity_name;
select user_entity_id
from ff_user_entities
where user_entity_name = c_user_entity_name;
select user_entity_id
from ff_user_entities
where user_entity_name = c_user_entity_name;
select report_type
into l_report_type
from pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id ;
select pay_core_utils.get_parameter('SETUP_ACTION_ID',legislative_parameters)
into v_setup_action
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id ;
-- Bug: 3910804 Delete data from pay_action_information
-------------------------------------------------------
delete from pay_action_information
where action_context_id = v_setup_action
and action_context_type = 'AAP'
and action_information_category = 'SG_IRAS_AMEND_SETUP';
g_person_id_tab.delete;
select distinct pai.action_information2 , fnd_date.canonical_to_date(pai.action_information3)
bulk collect into g_person_id_tab , g_person_start_date_tab
from pay_action_information pai
where pai.action_information1 = p_national_identifier
and pai.action_context_id = p_setup_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'SG_IRAS_AMEND_SETUP'
order by fnd_date.canonical_to_date(pai.action_information3) desc;
select 'X'
from per_assignments_f paf,
hr_soft_coding_keyflex hsc
where paf.person_id = c_person_id
and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_tax_unit_id
and paf.primary_flag = 'Y'
and paf.assignment_type = 'E' /* Bug 5033609 */
and paf.effective_start_date =
(select max(paf1.effective_start_date)
from per_assignments_f paf1 /* Bug 5858566 */
where paf1.person_id = paf.person_id
and paf1.assignment_type = 'E' /* Bug 5033609 */
and paf1.effective_start_date <= c_basis_end
and paf1.effective_end_date >= c_basis_start
and paf1.primary_flag = 'Y')
and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
select paf.assignment_id
from per_assignments_f paf,
hr_soft_coding_keyflex hsc
where paf.person_id = c_person_id
and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_tax_unit_id
and paf.primary_flag = 'Y'
and paf.assignment_type = 'E' /* Bug 5033609 */
and paf.effective_start_date =
(select max(paf1.effective_start_date)
from per_assignments_f paf1 /* Bug 5858566 */
where paf1.person_id = paf.person_id
and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and paf1.assignment_type = 'E' /* Bug 5033609 */
and paf1.effective_start_date <= c_basis_end
and paf1.effective_end_date >= c_basis_start
and paf1.primary_flag = 'Y')
and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);
select max(paf.assignment_id)
from per_assignments_f paf,
hr_soft_coding_keyflex hsc
where paf.person_id = c_person_id
and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_tax_unit_id
and paf.assignment_type = 'E'
and paf.effective_start_date = (
select max(paf1.effective_start_date)
from per_assignments_f paf1 /* Bug 5858566 */
where paf1.person_id = paf.person_id
and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and paf1.assignment_type = 'E' /*Bug5033609*/
and paf1.effective_start_date <= c_basis_end
and paf1.effective_end_date >= c_basis_start)
and (paf.effective_start_date <= c_basis_end and paf.effective_end_date >= c_basis_start);