[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)
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_SETUP'
and action_information2 between p_start_person_id and p_end_person_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 pay_core_utils.get_parameter('PERSON_ID',legislative_parameters),
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('LEGAL_ENTITY_ID',legislative_parameters),
pay_core_utils.get_parameter('BASIS_YEAR',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 max(paf.assignment_id)
from per_assignments_f paf,
pay_payroll_actions ppa1
where ppa1.payroll_action_id = c_payroll_action_id
and paf.person_id between c_start_person_id and c_end_person_id
and ppa1.business_group_id = paf.business_group_id
and paf.person_id = nvl(c_person_id,paf.person_id)
and paf.assignment_type = 'E' /* Bug 5033609 */
and decode(c_assignment_set_id,null,'Y',
decode(hr_assignment_set.ASSIGNMENT_IN_SET(c_assignment_set_id,paf.assignment_id),'Y','Y','N')) = 'Y'
and exists
( select null
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = c_legal_entity_id
and ppa.effective_date between c_basis_start and c_basis_end
and ppa.action_type in ('R','B','I','Q','V')
and ppa.action_status = 'C'
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date )
group by paf.person_id;
select pay_core_utils.get_parameter('PERSON_ID',legislative_parameters),
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('LEGAL_ENTITY_ID',legislative_parameters),
pay_core_utils.get_parameter('BASIS_YEAR',legislative_parameters),
pay_core_utils.get_parameter('ASSIGNMENT_SET_ID',legislative_parameters),
report_type
from pay_payroll_actions
where payroll_action_id = c_payroll_action_id;
select fai.value
from ff_archive_items fai,
ff_user_entities fue
where fai.context1 = p_assignment_action_id
and fai.user_entity_id = fue.user_entity_id
and fue.user_entity_name = p_user_entity_name;
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 'IR8%' ) )
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_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' ;
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),
'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;
t_user_entity_id.delete;
t_archive_value.delete;
t_date_earned.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),
'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 );
select 'Y',
oldpaaf.assignment_id,
newpaaf.assignment_id
from per_people_f pap,
per_assignments_f oldpaaf,
per_assignments_f newpaaf
where pap.person_id = c_person_id
and pap.person_id = oldpaaf.person_id
and oldpaaf.person_id = newpaaf.person_id
and oldpaaf.assignment_type = 'E' /* Bug 5033609 */
and newpaaf.assignment_type = 'E' /* Bug 5033609 */
and oldpaaf.assignment_id <> newpaaf.assignment_id
and oldpaaf.effective_end_date between to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
and newpaaf.effective_start_date
and newpaaf.effective_start_date between oldpaaf.effective_end_date
and to_date('31-12-'||c_basis_year,'DD-MM-YYYY');
select assact1.assignment_action_id
from pay_assignment_actions assact1,
pay_payroll_actions pact1,
per_assignments_f paaf1
where assact1.tax_unit_id = c_tax_unit_id
and paaf1.assignment_id = c_prev_ass_id
and paaf1.assignment_id = assact1.assignment_id
and pact1.payroll_action_id = assact1.payroll_action_id
and paaf1.business_group_id = c_business_group_id
and pact1.action_status = 'C'
and assact1.action_sequence =
( select max(assact.action_sequence)
from pay_assignment_actions assact,
pay_payroll_actions pact,
per_assignments_f paaf
where paaf.assignment_id = paaf1.assignment_id
and paaf.assignment_id = assact.assignment_id
and pact.payroll_action_id = assact.payroll_action_id
and paaf.business_group_id = paaf1.business_group_id
and assact.tax_unit_id = assact1.tax_unit_id
and pact.action_type in ('Q','R','B')
and pact.action_status = 'C'
and pact.effective_date between to_date('01-01-'||c_basis_year,'DD-MM-YYYY')
and to_date('31-12-'||c_basis_year,'DD-MM-YYYY'));
select fnd_date.date_to_canonical(min(pact.date_earned)) date_from,
fnd_date.date_to_canonical(max(pact.date_earned)) date_to,
sum(decode(pact.action_type,'V',-1,1)) no_of_times
from pay_run_result_values target,
pay_balance_feeds_f feed,
pay_balance_types pbt,
pay_run_results rr,
pay_assignment_actions assact,
pay_assignment_actions bal_assact,
pay_payroll_actions pact,
pay_payroll_actions bact,
per_assignments_f ass
where bal_assact.assignment_action_id = p_asg_action_id
and bal_assact.payroll_action_id = bact.payroll_action_id
and feed.balance_type_id = pbt.balance_type_id + decode(target.input_value_id,null,0,0)
and pbt.legislation_code = 'SG'
and pbt.balance_name = p_balance_name
and feed.input_value_id = target.input_value_id
and nvl(target.result_value, '0') <> '0'
and target.run_result_id = rr.run_result_id
and rr.assignment_action_id = assact.assignment_action_id
and assact.payroll_action_id = pact.payroll_action_id
and pact.effective_date between feed.effective_start_date and feed.effective_end_date
and rr.status in ('P','PA')
and assact.action_sequence <= bal_assact.action_sequence
and assact.assignment_id = ass.assignment_id
and bal_assact.assignment_id = assact.assignment_id /* added the join for bug#2227759 */
and exists ( select null
from per_assignments_f start_ass
where start_ass.assignment_id = bal_assact.assignment_id
and person_id = ass.person_id )
and pact.effective_date between ass.effective_start_date and ass.effective_end_date
and assact.tax_unit_id = p_tax_unit_id
and pact.effective_date >= trunc(bact.effective_date,'Y');
select fnd_date.date_to_canonical(min(prb.effective_date)) date_from,
fnd_date.date_to_canonical(max(prb.effective_date)) date_to,
sum(decode(ppa.action_type,'V',-1,1)) no_of_times
from pay_run_balances prb,
pay_assignment_actions ASSACT,
pay_payroll_actions PACT,
per_assignments_f ass,
pay_payroll_actions ppa,
pay_assignment_actions paa
where prb.defined_balance_id = c_def_balance_id
and assact.assignment_action_id = p_asg_action_id
and assact.payroll_action_id = pact.payroll_action_id
and prb.assignment_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and prb.action_sequence <= assact.action_sequence
and prb.effective_date <= pact.effective_date
and prb.balance_value <> 0
and ASS.person_id = (select person_id
from per_assignments_f START_ASS
where START_ASS.assignment_id = assact.assignment_id
and rownum = 1)
and prb.effective_date between ASS.effective_start_date
and ASS.effective_end_date
and prb.assignment_id = ass.assignment_id
and prb.tax_unit_id = p_tax_unit_id
and prb.effective_date >= trunc(PACT.effective_date,'Y');
select pdb.defined_balance_id
from pay_defined_balances pdb,
pay_balance_types pbt,
pay_balance_dimensions pbd
where pbt.balance_name = c_balance_name
and pbd.dimension_name = '_ASG_LE_RUN'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pdb.legislation_code = 'SG'
and pbt.legislation_code = 'SG'
and pbd.legislation_code = 'SG';
select run_balance_status
from pay_balance_validation
where defined_balance_id = c_def_balance_id
and business_group_id = c_business_group_id;
select target.org_information1,
target.org_information4,
target.org_information10,
target.org_information11,
target.org_information12,
target.org_information13,
target.org_information14,
target.org_information15,
target.org_information17,
target.org_information18,
target.org_information8
from hr_organization_units org,
hr_organization_information target,
hr_soft_coding_keyflex scl
where org.business_group_id = c_business_group_id
and org.organization_id = c_legal_entity_id
and org.organization_id = target.organization_id
and target.org_information_context = 'SG_LEGAL_ENTITY'
and to_char(org.organization_id) = scl.segment1;
select target.org_information5,
target.org_information7
from hr_organization_units org,
hr_organization_information target,
hr_soft_coding_keyflex scl
where org.business_group_id = c_business_group_id
and org.organization_id = c_legal_entity_id
and org.organization_id = target.organization_id
and target.org_information_context = 'SG_LE_IRAS'
and to_char(org.organization_id) = scl.segment1;
select fnd_date.date_to_canonical(max(ppamax.effective_date))
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');
select people.national_identifier,
h.meaning,
fnd_date.date_to_canonical(people.date_of_birth)
from per_people_f people,
hr_lookups h
where people.person_id = c_person_id
and people.effective_start_date = (
select max(people1.effective_start_date)
from per_people_f people1
where people1.person_id = people.person_id
and people1.effective_start_date <= c_basis_end
and people1.effective_end_date >= c_basis_start
)
and h.lookup_type (+)= 'SEX'
and h.lookup_code (+)= people.sex
and h.application_id (+)= 800;
select addr.style,
addr.address_type,
addr.address_line1,
addr.address_line2,
addr.address_line3,
lpad(nvl(addr.postal_code,'999999'),6,'0'),
addr.country
from per_addresses addr,
fnd_territories_tl a
where addr.person_id (+)= c_person_id
and addr.primary_flag (+)= 'Y'
and c_basis_end between nvl(addr.date_from, c_basis_start)
and nvl(addr.date_to, c_basis_end) /* Bug 2654499 */
and a.territory_code (+)= addr.country
and a.language (+)= userenv('LANG');
select addr.address_line1,
addr.address_line2,
addr.address_line3,
fnd_date.date_to_canonical(addr.date_from),
fnd_date.date_to_canonical(nvl(addr.date_to,c_basis_end))/* if its not blank, return the real end date, bug 2654499 */
from per_addresses addr,
fnd_territories_tl a
where addr.person_id (+) = c_person_id
and a.territory_code (+)= addr.country
and a.language (+)= userenv('LANG')
and addr.address_type = 'SG_CQ' -- SG specific Company Quarters Address Type
and nvl(addr.date_to, c_basis_end) =
(select max(nvl(date_to, c_basis_end))
from per_addresses
where address_type = 'SG_CQ'
and person_id = addr.person_id
and (date_from <= c_basis_end
and nvl(date_to, c_basis_end) >= c_basis_start));/*Bug 2654499*/
select fnd_date.date_to_canonical(min(service.date_start))
from per_periods_of_service service
where service.person_id = c_person_id
and service.date_start <= c_basis_end; /*Bug 2668599*/
select fnd_date.date_to_canonical(service.actual_termination_date)
from per_assignments_f assign,
per_periods_of_service service
where service.person_id = c_person_id
and service.period_of_service_id (+)= assign.period_of_service_id
and assign.effective_start_date = (
select max(assign1.effective_start_date)
from per_assignments_f assign1
where assign1.person_id = service.person_id
and assign1.assignment_type = 'E' /* Bug 5033609 */
and (assign1.effective_start_date <= c_basis_end
and assign1.effective_end_date >= c_basis_start)
);/*Bug 2654499*/
select people.per_information1,
people.per_information3,
people.per_information6,
to_char(fnd_date.canonical_to_date(people.per_information9),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(people.per_information9),'MM')||'/'||to_char(fnd_date.canonical_to_date(people.per_information9),'DD'), -- Bug 5435088
-- fnd_date.canonical_to_date(people.per_information9), -- Bug 5435088
people.per_information12,
people.per_information23
from per_people_f people
where people.person_id = c_person_id
and people.effective_start_date = (
select max(people1.effective_start_date)
from per_people_f people1
where people1.person_id = people.person_id
and people1.effective_start_date <= c_basis_end
and people1.effective_end_date >= c_basis_start);
select indicators.pei_information1,
indicators.pei_information2,
indicators.pei_information3,
indicators.pei_information4, -- Exempt Remission
indicators.pei_information5, -- Approval from IRAS
to_char(fnd_date.canonical_to_date(indicators.pei_information6),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(indicators.pei_information6),'MM')||'/'||to_char(fnd_date.canonical_to_date(indicators.pei_information6),'DD'), -- Date of Approval
details.pei_information3,
details.pei_information4,
details.pei_information6, -- Name of bank
info.pei_information1
from per_people_extra_info indicators,
per_people_extra_info info,
per_people_extra_info details,
per_people_f people
where people.person_id = c_person_id
and people.effective_start_date = (
select max(people1.effective_start_date)
from per_people_f people1
where people1.person_id = people.person_id
and people1.effective_start_date <= c_basis_end
and people1.effective_end_date >= c_basis_start)/*Bug 2645599*/
and people.person_id = indicators.person_id(+)
and indicators.information_type(+) = 'HR_IR8A_INDICATORS_SG'
and people.person_id = details.person_id(+)
and details.information_type(+) = 'HR_IR8A_FURTHER_DETAILS_SG'
and people.person_id = info.person_id(+)
and info.information_type(+) = 'HR_IRAS_ADDITIONAL_INFO_SG';
select /*+ USE_NL(aei) */
aei.aei_information2,
aei.aei_information3
from per_assignments_f assign,
per_assignment_extra_info aei,
hr_soft_coding_keyflex hsc
where assign.person_id = c_person_id
and assign.assignment_id = c_assignment_id
and assign.assignment_id = aei.assignment_id
and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_legal_entity_id
and aei.information_type = 'HR_IR8S_INDICATORS_SG';
select /*+ USE_NL(aei) */
to_char(fnd_date.canonical_to_date(aei.aei_information1),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(aei.aei_information1),'MM')||'/'||
to_char(fnd_date.canonical_to_date(aei.aei_information1),'DD')
from per_assignments_f assign,
per_assignment_extra_info aei,
hr_soft_coding_keyflex hsc
where assign.person_id = c_person_id
and assign.assignment_id = c_assignment_id
and assign.assignment_id = aei.assignment_id
and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_legal_entity_id
and aei.information_type = 'HR_NON_CONT_BONUS_INFO_SG'
and aei.aei_information1 is not NULL
and assign.assignment_type = 'E'
and to_char(fnd_date.canonical_to_date(aei.aei_information1),'YYYY') = to_char(c_basis_end,'YYYY');
SELECT pea.segment4 bank_name
FROM per_assignments_f assign,
hr_soft_coding_keyflex hsc,
pay_external_accounts pea,
pay_personal_payment_methods_f ppm,
hr_lookups hl
WHERE assign.person_id = c_person_id
AND assign.assignment_id = c_assignment_id
AND assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
AND hsc.segment1 = c_legal_entity_id
AND assign.assignment_id = ppm.assignment_id
AND pea.segment3 = hl.lookup_code (+)
AND hl.lookup_type(+) = 'SG_ACCOUNT_TYPE'
AND pea.external_account_id(+) = ppm.external_account_id
AND assign.effective_start_date <= c_basis_end
AND assign.effective_end_date >= c_basis_start
AND ppm.effective_start_date <= c_basis_end
AND ppm.effective_end_date >= c_basis_start
and priority = 1
and ppm.effective_start_date =
(select max(ppm1.effective_start_date)
from pay_personal_payment_methods_f ppm1
where ppm1.assignment_id = ppm.assignment_id
and ppm1.effective_start_date <= c_basis_end
and ppm1.effective_end_date >= c_basis_start); /* Bug 5868910*/
select grade.name
from per_assignments_f assign,
per_grades grade,
hr_soft_coding_keyflex hsc
where assign.person_id = c_person_id
and assign.assignment_id = c_assignment_id
and grade.grade_id = assign.grade_id
and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_legal_entity_id
and assign.assignment_type = 'E'
and assign.effective_start_date =
(select max(paf1.effective_start_date)
from per_assignments_f paf1
where paf1.person_id = assign.person_id
and paf1.assignment_id = assign.assignment_id
and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
and paf1.effective_start_date <= c_basis_end
and paf1.effective_end_date >= c_basis_start); /* Bug 5868910 */
select jbt.name
from per_assignments_f assign,
per_jobs_tl jbt,
hr_soft_coding_keyflex hsc
where assign.person_id = c_person_id
and assign.assignment_id = c_assignment_id
and jbt.job_id = assign.job_id
and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_legal_entity_id
and jbt.language = userenv('LANG')
and assign.assignment_type = 'E'
and assign.effective_start_date =
(select max(paf1.effective_start_date)
from per_assignments_f paf1
where paf1.person_id = assign.person_id
and paf1.assignment_id = assign.assignment_id
and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
and paf1.effective_start_date <= c_basis_end
and paf1.effective_end_date >= c_basis_start); /* Bug 5868910 */
select pst.name
from per_assignments_f assign,
hr_all_positions_f_tl pst,
hr_all_positions_f pos,
hr_soft_coding_keyflex hsc
where assign.person_id = c_person_id
and assign.assignment_id = c_assignment_id
and pos.position_id = assign.position_id
and pst.position_id = pos.position_id
and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_legal_entity_id
and pst.language = userenv('LANG')
and assign.effective_start_date between NVL(pos.effective_start_date,to_date('01-01-1900','DD-MM-YYYY')) and NVL(pos.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
and assign.assignment_type = 'E'
and assign.effective_start_date =
(select max(paf1.effective_start_date)
from per_assignments_f paf1
where paf1.person_id = assign.person_id
and paf1.assignment_id = assign.assignment_id
and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
and paf1.effective_start_date <= c_basis_end
and paf1.effective_end_date >= c_basis_start); /* Bug 5868910 */
select decode(fifs.application_column_name, 'SEGMENT1', ppd.segment1,
'SEGMENT2', ppd.segment2,
'SEGMENT3', ppd.segment3,
'SEGMENT4', ppd.segment4,
'SEGMENT5', ppd.segment5,
'SEGMENT6', ppd.segment6,
'SEGMENT7', ppd.segment7,
'SEGMENT8', ppd.segment8,
'SEGMENT9', ppd.segment9,
'SEGMENT10',ppd.segment10,
'SEGMENT11',ppd.segment11,
'SEGMENT12',ppd.segment12,
'SEGMENT13',ppd.segment13,
'SEGMENT14',ppd.segment14,
'SEGMENT15',ppd.segment15,
'SEGMENT16',ppd.segment16,
'SEGMENT17',ppd.segment17,
'SEGMENT18',ppd.segment18,
'SEGMENT19',ppd.segment19,
'SEGMENT20',ppd.segment20,
'SEGMENT21',ppd.segment21,
'SEGMENT22',ppd.segment22,
'SEGMENT23',ppd.segment23,
'SEGMENT24',ppd.segment24,
'SEGMENT25',ppd.segment25,
'SEGMENT26',ppd.segment26,
'SEGMENT27',ppd.segment27,
'SEGMENt28',ppd.segment28,
'SEGMENT29',ppd.segment29,
'SEGMENT30',ppd.segment30)
from per_assignments_f assign,
hr_soft_coding_keyflex hsc,
hr_all_positions_f pos,
hr_all_positions_f_tl pst,
per_position_definitions ppd,
hr_organization_units hou,
hr_organization_information hoi,
fnd_id_flex_segments fifs,
fnd_id_flex_structures fift
where assign.person_id = c_person_id
and assign.assignment_id = c_assignment_id
and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_legal_entity_id
and assign.position_id = pos.position_id
and pst.position_id = pos.position_id
and pst.language = userenv('LANG')
and (assign.effective_start_date <= c_basis_end
and assign.effective_end_date >= c_basis_start)
and (pos.effective_start_date <= c_basis_end
and pos.effective_end_date >= c_basis_start)
and assign.assignment_type = 'E'
and assign.business_group_id = hou.business_group_id
and hsc.segment1 = hou.organization_id
and hou.business_group_id = hoi.organization_id
and hoi.org_information_context = 'Business Group Information'
and hoi.org_information10 = 'SGD'
and hoi.org_information8 = fift.id_flex_num
and fifs.id_flex_num = fift.id_flex_num
and fifs.application_id = '800'
and fifs.application_id = fift.application_id
and fifs.id_flex_code = 'POS'
and fifs.id_flex_code = fift.id_flex_code
and fifs.segment_name = c_er_position_seg_type
and pos.position_definition_id = ppd.position_definition_id
and assign.effective_start_date =
(select max(paf1.effective_start_date)
from per_assignments_f paf1
where paf1.person_id = assign.person_id
and paf1.assignment_id = assign.assignment_id
and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
and paf1.effective_start_date <= c_basis_end
and paf1.effective_end_date >= c_basis_start); /* Bug 5868910 */
select /*+ USE_NL(aei) */
aei.aei_information1
from per_assignments_f assign,
per_assignment_extra_info aei,
hr_soft_coding_keyflex hsc
where assign.person_id = c_person_id
and assign.assignment_id = c_assignment_id
and assign.assignment_id = aei.assignment_id
and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_legal_entity_id
and aei.information_type = 'HR_JOB_DESIGNATION_SG'
and aei.aei_information1 is not NULL
and assign.assignment_type = 'E';
select aei.aei_information1,
min(fnd_date.date_to_canonical(assign.effective_start_date)),
max(fnd_date.date_to_canonical(nvl(assign.effective_end_date,c_basis_end)))
from per_assignments_f assign,
per_assignment_extra_info aei,
hr_soft_coding_keyflex hsc
where assign.person_id = c_person_id
and assign.assignment_id = aei.assignment_id
and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_legal_entity_id
and aei.information_type = 'HR_IR8S_INDICATORS_SG'
and aei.aei_information1 is not NULL -- CPF overseas post obligatory flag, bug 2261267, 2373475
and assign.effective_start_date <= c_basis_end
and assign.effective_end_date >= c_basis_start
and assign.assignment_type = 'E' /* Bug 5033609 */
group by aei.aei_information1;
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 count(distinct (paei.assignment_extra_info_id))
from per_assignment_extra_info paei,
per_assignments_f paa,
hr_soft_coding_keyflex hsc
where paa.person_id = c_person_id
and paa.assignment_id = paei.assignment_id
and paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
and hsc.segment1 = c_tax_unit_id
and paa.business_group_id = c_business_group_id
and paa.assignment_type = 'E' /* Bug 5033609 */
and paei.information_type = 'HR_CPF_CLAIMED_SG'
and paei.aei_information1 = to_char(c_basis_end,'YYYY')
and (paa.effective_start_date <= c_basis_end
and paa.effective_end_date >= c_basis_start);
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 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_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_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);
select meaning
from hr_lookups
where lookup_type = 'SG_COUNTRY_CODE'
and lookup_code = c_country;