[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select /*+ ORDERED */
ppa.business_group_id,
ppa.effective_date,
ppa.legislative_parameters,
pbg.legislation_code
from pay_payroll_actions ppa,
per_business_groups_perf pbg
where ppa.payroll_action_id = p_payroll_action_id
and pbg.business_group_id = ppa.business_group_id;
select /*+ ORDERED */
nvl(nvl(pp.prl_information1, hoi.org_information2),'CTR_EE')
from /* Payroll and Business Group details */
pay_all_payrolls_f pp,
hr_organization_information hoi
where pp.payroll_id = g_payroll_id
and g_effective_date
between pp.effective_start_date and pp.effective_end_date
and hoi.organization_id(+) = pp.business_group_id
and hoi.org_information_context(+) = 'JP_BUSINESS_GROUP_INFO';
select 'Y'
from pay_jp_isdf_pact_v
where payroll_action_id = p_payroll_action_id;
select /*+ ORDERED */
hoi2.org_information1 tax_office_name,
hoi1.org_information1 salary_payer_name,
hoi1.org_information6||hoi1.org_information7||hoi1.org_information8 salary_payer_address
from hr_all_organization_units hou,
hr_organization_information hoi1,
hr_organization_information hoi2
where hou.organization_id = g_organization_id
and hoi1.organization_id(+) = hou.organization_id
and hoi1.org_information_context(+) = 'JP_TAX_SWOT_INFO'
and hoi2.organization_id(+) = hou.organization_id
and hoi2.org_information_context(+) = 'JP_ITAX_WITHHELD_INFO';
'select 1
from dual
where :payroll_action_id < 0';
'select /*+ ORDERED */
distinct pp.person_id
from pay_payroll_actions ppa,
per_all_people_f pp
where ppa.payroll_action_id = :payroll_action_id
and pp.business_group_id = ppa.business_group_id + 0
order by pp.person_id';
select /*+ ORDERED */
pa.assignment_id
from per_periods_of_service ppos,
per_all_assignments_f pa
where ppos.person_id
between p_start_person_id and p_end_person_id
and ppos.business_group_id + 0 = g_business_group_id
and g_effective_date
between ppos.date_start and nvl(ppos.final_process_date,g_effective_date)
and pa.period_of_service_id = ppos.period_of_service_id
and pa.primary_flag = 'Y' /*Added by JSAJJA as per Bug No 8435426*/
and g_effective_date
between pa.effective_start_date and pa.effective_end_date
and pa.payroll_id + 0 = g_payroll_id;
select pay_assignment_actions_s.nextval
into l_assignment_action_id
from dual;
select ff.formula_id
from ff_formulas_f ff
where ff.formula_name = p_ins_info_rec.calc_prem_ff
and nvl(ff.business_group_id,g_business_group_id) = g_business_group_id
and nvl(ff.legislation_code,g_legislation_code) = g_legislation_code
and g_effective_date
between ff.effective_start_date and ff.effective_end_date;
select ff.formula_id
from ff_formulas_f ff
where ff.formula_name = p_ins_info_rec.calc_prem_ff
and nvl(ff.business_group_id,g_business_group_id) = g_business_group_id
and nvl(ff.legislation_code,g_legislation_code) = g_legislation_code
and g_effective_date
between ff.effective_start_date and ff.effective_end_date;
function ee_datetrack_update_mode(
p_element_entry_id in number,
p_effective_start_date in date,
p_effective_end_date in date,
p_effective_date in date)
return varchar2
--
is
--
l_datetrack_mode varchar2(30);
end ee_datetrack_update_mode;
select /*+ ORDERED */
pee.element_entry_id,
pee.effective_start_date,
pee.effective_end_date,
pee.object_version_number,
peev.input_value_id,
peev.screen_entry_value
from pay_element_links_f pel,
pay_element_entries_f pee,
pay_element_entry_values_f peev
where pel.element_type_id = p_element_type_id
and pel.business_group_id + 0 = p_business_group_id
and p_effective_date
between pel.effective_start_date and pel.effective_end_date
and pee.assignment_id = p_assignment_id
and pee.element_link_id = pel.element_link_id
and p_effective_date
between pee.effective_start_date and pee.effective_end_date
and pee.entry_type = 'E'
and peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date;
p_entry_rec.ins_datetrack_update_mode := null;
p_entry_rec.is_datetrack_update_mode := null;
p_entry_rec.ins_datetrack_update_mode := ee_datetrack_update_mode(l_csr_entry.element_entry_id,l_csr_entry.effective_start_date,l_csr_entry.effective_end_date,p_effective_date);
p_entry_rec.is_datetrack_update_mode := ee_datetrack_update_mode(l_csr_entry.element_entry_id,l_csr_entry.effective_start_date,l_csr_entry.effective_end_date,p_effective_date);
select /*+ ORDERED */
pp.person_id,
pp.business_group_id,
pp.employee_number employee_number,
pp.last_name last_name_kana,
pp.first_name first_name_kana,
pp.per_information18 last_name,
pp.per_information19 first_name,
pp.per_information18||' '||pp.per_information19 full_name,
decode(par.address_id,null,pac.postal_code,par.postal_code) postal_code,
trim(substrb(decode(par.address_id,null,
pac.address_line1||pac.address_line2||pac.address_line3,
par.address_line1||par.address_line2||par.address_line3),1,240)) address
from per_all_assignments_f pa,
per_all_people_f pp,
per_addresses par,
per_addresses pac
where pa.assignment_id = p_assignment_id
and g_effective_date
between pa.effective_start_date and pa.effective_end_date
and pp.person_id = pa.person_id
and g_effective_date
between pp.effective_start_date and pp.effective_end_date
and par.person_id(+) = pp.person_id
and par.address_type(+) = 'JP_R'
and g_effective_date
between par.date_from(+) and nvl(par.date_to(+),g_effective_date)
and pac.person_id(+) = pp.person_id
and pac.address_type(+) = 'JP_C'
and g_effective_date
between pac.date_from(+) and nvl(pac.date_to(+),g_effective_date);
select /*+ ORDERED */
paei.assignment_extra_info_id,
paei.object_version_number aei_object_version_number,
paei.information_type info_type,
paei.aei_information1 gen_ins_class,
paei.aei_information2 gen_ins_company_code,
nvl(paei.aei_information12,'O') gen_ins_old_new,
hoi.org_information2 ins_company_name,
hoi.org_information3 calc_prem_ff,
hoi.org_information4 lig_prem_bal,
hoi.org_information5 lig_prem_mth_ele,
hoi.org_information6 lig_prem_bon_ele,
null lip_prem_bal,
null lip_prem_mth_ele,
null lip_prem_bon_ele,
null cmi_prem_bal,
null cmi_prem_mth_ele,
null cmi_prem_bon_ele,
fnd_date.canonical_to_date(paei.aei_information3) start_date,
fnd_date.canonical_to_date(paei.aei_information4) end_date,
paei.aei_information5 ins_type,
null ins_period_start_date,
paei.aei_information6 ins_period,
paei.aei_information7 contractor_name,
paei.aei_information8 beneficiary_name,
paei.aei_information9 beneficiary_relship,
fnd_number.canonical_to_number(paei.aei_information10) linc_prem
from per_assignment_extra_info paei,
hr_organization_information hoi
where paei.assignment_id = p_assignment_id
and paei.information_type = 'JP_ASS_LIG_INFO'
-- include PC for customized valuset
--and paei.aei_information1 in ('GIP','LINC')
and g_effective_date
between nvl(fnd_date.canonical_to_date(paei.aei_information3),nvl(fnd_date.canonical_to_date(paei.aei_information4)+1,hr_api.g_sot))
and nvl(fnd_date.canonical_to_date(paei.aei_information4),hr_api.g_eot)
and hoi.org_information1 = paei.aei_information2
and nvl(hoi.ORG_INFORMATION10,'O') = nvl(paei.AEI_INFORMATION12,'O')
and hoi.org_information_context
= decode(paei.aei_information1,'GIP','JP_LI_GIP_INFO','LINC','JP_LI_LINC_INFO','X')
and hoi.organization_id = g_organization_id
-- irregular case for duplicate org
and not exists(
select null
from hr_organization_information hoi2
where hoi2.org_information1 = hoi.org_information1
and hoi2.org_information_context = hoi.org_information_context
and hoi2.organization_id = hoi.organization_id
and nvl(hoi2.ORG_INFORMATION10,'O') = nvl(hoi.ORG_INFORMATION10,'O')
and hoi2.org_information_id < hoi.org_information_id)
order by
decode(paei.aei_information1,'GIP',1,2),
paei.aei_information2;
select /*+ ORDERED */
paei.assignment_extra_info_id,
paei.object_version_number aei_object_version_number,
paei.information_type info_type,
paei.aei_information1 cmi_ins_class,
paei.aei_information2 cmi_ins_company_code,
'N' care_ins_old_new,
hoi.org_information2 ins_company_name,
hoi.org_information3 calc_prem_ff,
null lig_prem_bal,
null lig_prem_mth_ele,
null lig_prem_bon_ele,
null lip_prem_bal,
null lip_prem_mth_ele,
null lip_prem_bon_ele,
hoi.org_information11 cmi_prem_bal,
hoi.org_information12 cmi_prem_mth_ele,
hoi.org_information13 cmi_prem_bon_ele,
fnd_date.canonical_to_date(paei.aei_information3) start_date,
fnd_date.canonical_to_date(paei.aei_information4) end_date,
paei.aei_information5 ins_type,
null ins_period_start_date,
paei.aei_information6 ins_period,
paei.aei_information7 contractor_name,
paei.aei_information8 beneficiary_name,
paei.aei_information9 beneficiary_relship,
fnd_number.canonical_to_number(paei.aei_information10) linc_prem
from per_assignment_extra_info paei,
hr_organization_information hoi
where paei.assignment_id = p_assignment_id
and paei.information_type = 'JP_ASS_CMI_INFO'
-- include PC for customized valuset
--and paei.aei_information1 in ('GIP','LINC')
and g_effective_date
between nvl(fnd_date.canonical_to_date(paei.aei_information3),nvl(fnd_date.canonical_to_date(paei.aei_information4)+1,hr_api.g_sot))
and nvl(fnd_date.canonical_to_date(paei.aei_information4),hr_api.g_eot)
and hoi.org_information1 = paei.aei_information2
and nvl(hoi.ORG_INFORMATION10,'N') = 'N'
and hoi.org_information_context
= decode(paei.aei_information1,'GIP','JP_LI_GIP_INFO','LINC','JP_LI_LINC_INFO','X')
and hoi.organization_id = g_organization_id
-- irregular case for duplicate org
and not exists(
select null
from hr_organization_information hoi2
where hoi2.org_information1 = hoi.org_information1
and hoi2.org_information_context = hoi.org_information_context
and hoi2.organization_id = hoi.organization_id
and nvl(hoi2.ORG_INFORMATION10,'N') = nvl(hoi.ORG_INFORMATION10,'N')
and hoi2.org_information_id < hoi.org_information_id)
order by
decode(paei.aei_information1,'GIP',1,2),
paei.aei_information2;
select /*+ ORDERED */
paei.assignment_extra_info_id,
paei.object_version_number aei_object_version_number,
paei.information_type info_type,
paei.aei_information1 pens_ins_class,
paei.aei_information2 pens_ins_company_code,
nvl(paei.aei_information13,'O') pens_ins_old_new,
hoi.org_information2 ins_company_name,
hoi.org_information3 calc_prem_ff,
null lig_prem_bal,
null lig_prem_mth_ele,
null lig_prem_bon_ele,
hoi.org_information7 lip_prem_bal,
hoi.org_information8 lip_prem_mth_ele,
hoi.org_information9 lip_prem_bon_ele,
null cmi_prem_bal,
null cmi_prem_mth_ele,
null cmi_prem_bon_ele,
fnd_date.canonical_to_date(paei.aei_information3) start_date,
fnd_date.canonical_to_date(paei.aei_information4) end_date,
paei.aei_information5 ins_type,
fnd_date.canonical_to_date(paei.aei_information6) ins_period_start_date,
paei.aei_information7 ins_period,
paei.aei_information8 contractor_name,
paei.aei_information9 beneficiary_name,
paei.aei_information10 beneficiary_relship,
fnd_number.canonical_to_number(paei.aei_information11) linc_prem
from per_assignment_extra_info paei,
hr_organization_information hoi
where paei.assignment_id = p_assignment_id
and paei.information_type = 'JP_ASS_LIP_INFO'
-- include PC for customized valuset
--and paei.aei_information1 in ('GIP','LINC')
and g_effective_date
between nvl(fnd_date.canonical_to_date(paei.aei_information3),nvl(fnd_date.canonical_to_date(paei.aei_information4)+1,hr_api.g_sot))
and nvl(fnd_date.canonical_to_date(paei.aei_information4),hr_api.g_eot)
and hoi.org_information1 = paei.aei_information2
and nvl(hoi.ORG_INFORMATION10,'O') = nvl(paei.AEI_INFORMATION13,'O')
and hoi.org_information_context
= decode(paei.aei_information1,'GIP','JP_LI_GIP_INFO','LINC','JP_LI_LINC_INFO','X')
and hoi.organization_id = g_organization_id
-- irregular case for duplicate org
and not exists(
select null
from hr_organization_information hoi2
where hoi2.org_information1 = hoi.org_information1
and hoi2.org_information_context = hoi.org_information_context
and hoi2.organization_id = hoi.organization_id
and nvl(hoi2.ORG_INFORMATION10,'O') = nvl(hoi.ORG_INFORMATION10,'O')
and hoi2.org_information_id < hoi.org_information_id)
order by
decode(paei.aei_information1,'GIP',1,2),
paei.aei_information2;
select /*+ ORDERED */
paei.assignment_extra_info_id,
paei.object_version_number aei_object_version_number,
paei.information_type info_type,
paei.aei_information13 nonlife_ins_class,
paei.aei_information1 nonlife_ins_term_type,
paei.aei_information2 nonlife_ins_company_code,
hoi.org_information2 ins_company_name,
hoi.org_information3 calc_prem_ff,
hoi.org_information7 eqi_prem_bal,
hoi.org_information8 eqi_prem_mth_ele,
hoi.org_information9 eqi_prem_bon_ele,
hoi.org_information4 ai_prem_bal,
hoi.org_information5 ai_prem_mth_ele,
hoi.org_information6 ai_prem_bon_ele,
fnd_date.canonical_to_date(paei.aei_information3) start_date,
fnd_date.canonical_to_date(paei.aei_information4) end_date,
paei.aei_information5 ins_type,
paei.aei_information6 ins_period,
paei.aei_information7 contractor_name,
paei.aei_information8 beneficiary_name,
paei.aei_information9 beneficiary_relship,
decode(to_char(sign(g_effective_date - c_st_upd_date_2007)),'-1',paei.aei_information10,null) maturity_repayment,
fnd_number.canonical_to_number(paei.aei_information11) annual_prem
from per_assignment_extra_info paei,
hr_organization_information hoi
where paei.assignment_id = p_assignment_id
and paei.information_type = 'JP_ASS_AI_INFO'
-- include PC for customized valuset
--and paei.aei_information13 = 'AP'
and paei.aei_information1 <> decode(to_char(sign(g_effective_date - c_st_upd_date_2007)),'-1','EQ','S')
and g_effective_date
between nvl(fnd_date.canonical_to_date(paei.aei_information3),nvl(fnd_date.canonical_to_date(paei.aei_information4)+1,hr_api.g_sot))
and nvl(fnd_date.canonical_to_date(paei.aei_information4),hr_api.g_eot)
and hoi.org_information1 = paei.aei_information2
and hoi.org_information_context = 'JP_ACCIDENT_INS_INFO'
and hoi.organization_id = g_organization_id
-- irregular case for duplicate org
and not exists(
select null
from hr_organization_information hoi2
where hoi2.org_information1 = hoi.org_information1
and hoi2.org_information_context = hoi.org_information_context
and hoi2.organization_id = hoi.organization_id
and hoi2.org_information_id < hoi.org_information_id)
order by paei.aei_information13,
decode(paei.aei_information1,'EQ',1,'L',2,3),
paei.aei_information2;
select /*+ ORDERED */
assact.assignment_action_id
from pay_assignment_actions paa,
pay_jp_isdf_assact_v assact
where paa.payroll_action_id = g_copy_archive_pact_id
and paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and assact.assignment_action_id = paa.assignment_action_id
and assact.transfer_status <> 'E'
and assact.transaction_status in ('A','F');
select *
from pay_jp_isdf_life_gen_v
where assignment_action_id = l_copy_archive_assact_id
and status <> 'D';
select *
from pay_jp_isdf_life_pens_v
where assignment_action_id = l_copy_archive_assact_id
and status <> 'D';
select *
from pay_jp_isdf_care_med_v
where assignment_action_id = l_copy_archive_assact_id
and status <> 'D';
select *
from pay_jp_isdf_nonlife_v
where assignment_action_id = l_copy_archive_assact_id
and status <> 'D';
select *
from pay_jp_isdf_social_v
where assignment_action_id = l_copy_archive_assact_id
and status <> 'D';
select *
from pay_jp_isdf_mutual_aid_v
where assignment_action_id = l_copy_archive_assact_id
and status <> 'D';
select *
from pay_jp_isdf_spouse_v
where assignment_action_id = l_copy_archive_assact_id
and status <> 'D';
select *
from pay_jp_isdf_spouse_inc_v
where assignment_action_id = l_copy_archive_assact_id
and status <> 'D';
p_ins_datetrack_update_mode => l_entry_rec.ins_datetrack_update_mode,
p_ins_element_entry_id => l_entry_rec.ins_element_entry_id,
p_ins_ee_object_version_number => l_entry_rec.ins_ee_object_version_number,
p_life_gen_ins_prem => l_entry_rec.life_gen_ins_prem,
p_life_gen_ins_prem_o => null,
p_life_pens_ins_prem => l_entry_rec.life_pens_ins_prem,
p_life_pens_ins_prem_o => null,
p_nonlife_long_ins_prem => l_entry_rec.nonlife_long_ins_prem,
p_nonlife_long_ins_prem_o => null,
p_nonlife_short_ins_prem => l_entry_rec.nonlife_short_ins_prem,
p_nonlife_short_ins_prem_o => null,
p_earthquake_ins_prem => l_entry_rec.earthquake_ins_prem,
p_earthquake_ins_prem_o => null,
p_is_datetrack_update_mode => l_entry_rec.is_datetrack_update_mode,
p_is_element_entry_id => l_entry_rec.is_element_entry_id,
p_is_ee_object_version_number => l_entry_rec.is_ee_object_version_number,
p_social_ins_prem => l_entry_rec.social_ins_prem,
p_social_ins_prem_o => null,
p_mutual_aid_prem => l_entry_rec.mutual_aid_prem,
p_mutual_aid_prem_o => null,
p_spouse_income => l_entry_rec.spouse_income,
p_spouse_income_o => null,
p_national_pens_ins_prem => l_entry_rec.national_pens_ins_prem,
p_national_pens_ins_prem_o => null,
p_life_gen_ins_prem_new => l_entry_rec.life_gen_ins_prem_new,
p_life_pens_ins_prem_new => l_entry_rec.life_pens_ins_prem_new,
p_care_med_ins_prem_new => l_entry_rec.care_med_ins_prem_new,
p_object_version_number => l_object_version_number);
pay_jp_isdf_dml_pkg.update_assact(
p_action_information_id => p_action_information_id,
p_object_version_number => p_object_version_number,
p_transaction_status => 'N',
p_finalized_date => null,
p_finalized_by => null,
p_user_comments => null,
p_admin_comments => null,
p_transfer_status => 'U',
p_transfer_date => null,
p_expiry_date => null);
select assignment_id
into l_assignment_id
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;