[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select legislative_parameters
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
g_legislative_parameter_tbl.delete;
select effective_date
into g_effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select
hr_ni_chk_pkg.chk_nat_id_format(per.national_identifier, 'DDDDDD-DDDDDDD') NI
from per_people_f per,
per_assignments_f asg
where asg.assignment_id = p_assignment_id
and per.person_id = asg.person_id
and p_effective_date between per.effective_start_date and per.effective_end_date
and p_effective_date between asg.effective_start_date and asg.effective_end_date;
select nvl(dis_information3, 'Y')
from per_disabilities_f
where person_id = p_person_id
and dis_information_category = 'KR'
and p_effective_date between effective_start_date and effective_end_date
order by dis_information3 ;
select pei.pei_information1
from per_people_f pap
,per_people_extra_info pei
,per_assignments_f paa
where paa.assignment_id = p_assignment_id
and paa.business_group_id = p_business_group_id
and paa.person_id = pap.person_id
and pap.person_id = pei.person_id
and pei.information_type = 'PER_KR_NP_EXCEPTIONS'
and p_date_earned between paa.effective_start_date and paa.effective_end_date
and p_date_earned between pap.effective_start_date and pap.effective_end_date
and p_date_earned between fnd_date.canonical_to_date(pei.pei_information2)
and fnd_date.canonical_to_date(pei.pei_information3);
SELECT lookup_code
FROM hr_leg_lookups
WHERE lookup_type = 'CONTACT'
AND meaning LIKE '%Child%'
AND lookup_code = p_contact_type;
select
nvl(cei.cei_information13, 'N')
from per_people_f per,
per_contact_relationships ctr,
per_assignments_f asg,
per_contact_extra_info_f cei
where asg.assignment_id = p_assignment_id
and p_date_earned
between asg.effective_start_date and asg.effective_end_date
and ctr.person_id = asg.person_id
and ctr.cont_information_category = 'KR'
and ctr.cont_information1 = 'Y'
and ((ctr.cont_information9 ='D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_date_earned))
or (nvl(ctr.cont_information9,'XXX') <>'D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(date_end, p_date_earned))
)
and per.person_id = ctr.contact_person_id
and cei.contact_relationship_id = ctr.contact_relationship_id
and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
and to_char(cei.effective_start_date, 'YYYY') = to_char(p_date_earned,'YYYY')
and (
(
p_date_earned
between per.effective_start_date and per.effective_end_date
)
or
(
per.start_date = per.effective_start_date
and not exists(
select null
from per_people_f per2
where per2.person_id = per.person_id
and p_date_earned
between per2.effective_start_date and per2.effective_end_date)
)
);
select
decode(ctr.cont_information2, 'Y', dpnt_spouse_flag(ctr.contact_type), 'N') DPNT_SPOUSE_FLAG,
decode(ctr.cont_information2, 'Y', aged_dpnt_flag(ctr.contact_type, per.national_identifier, p_date_earned), 'N') AGED_DPNT_FLAG,
decode(ctr.cont_information2, 'Y', adult_dpnt_flag(ctr.contact_type, per.national_identifier, p_date_earned,nvl(ctr.cont_information4, 'N'),nvl(ctr.cont_information8, 'N')), 'N') ADULT_DPNT_FLAG,
decode(ctr.cont_information2, 'Y', underaged_dpnt_flag(ctr.contact_type, per.national_identifier, p_date_earned), 'N') UNDERAGED_DPNT_FLAG,
decode(ctr.cont_information3, 'Y', aged_flag(per.national_identifier, p_date_earned), 'N') AGED_FLAG,
-- Bug 3172960
decode(ctr.cont_information3, 'Y', super_aged_flag(per.national_identifier, p_date_earned), 'N') SUPER_AGED_FLAG,
nvl(ctr.cont_information4, 'N') DISABLED_FLAG,
decode(ctr.cont_information7, 'Y', child_flag(per.national_identifier, p_date_earned), 'N') CHILD_FLAG,
-- Bug 6784288 Bug 6825145
decode(ctr.cont_information2, 'Y', decode(ctr.cont_information11,'4',underaged_dpnt_flag(ctr.contact_type, per.national_identifier, p_date_earned),addtl_child_flag(ctr.contact_type, per.national_identifier, p_date_earned)), 'N') ADDTL_CHILD
from per_people_f per,
per_contact_relationships ctr,
per_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_date_earned
between asg.effective_start_date and asg.effective_end_date
and ctr.person_id = asg.person_id
and ctr.cont_information_category = 'KR'
and ctr.cont_information1 = 'Y'
and ((ctr.cont_information9 ='D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_date_earned))
or (nvl(ctr.cont_information9,'XXX') <>'D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(date_end, p_date_earned))
)
and per.person_id = ctr.contact_person_id
and (
(
p_date_earned
between per.effective_start_date and per.effective_end_date
)
or
(
per.start_date = per.effective_start_date
and not exists(
select null
from per_people_f per2
where per2.person_id = per.person_id
and p_date_earned
between per2.effective_start_date and per2.effective_end_date)
)
);
select
per.national_identifier,
per.marital_status,
ni_sex(per.national_identifier) NI_SEX,
aged_flag(per.national_identifier, p_date_earned) AGED_FLAG,
disabled_flag(per.person_id, p_date_earned) DISABLED_FLAG,
child_flag(per.national_identifier, p_date_earned) CHILD_FLAG,
nvl(pei.pei_information3,'N') FEMALE_EMP_DOC,
super_aged_flag(per.national_identifier, p_date_earned) SUPER_AGED_FLAG -- Bug 4124430
from per_people_f per,
per_assignments_f asg,
per_people_extra_info pei
where asg.assignment_id = p_assignment_id
and p_date_earned
between asg.effective_start_date and asg.effective_end_date
and per.person_id = asg.person_id
and p_date_earned
between per.effective_start_date and per.effective_end_date
and per.person_id = pei.person_id(+)
and pei.information_type(+) = 'PER_KR_RELATED_YEA_INFORMATION';
select pei_information8 loss_code
,pei_information9 loss_date
from per_people_extra_info pei
,per_people_f pp
,per_assignments_f paa
,per_time_periods ptp
where paa.assignment_id = p_assignment_id
and paa.business_group_id = p_business_group_id
and pp.person_id = paa.person_id
and pei.person_id = pp.person_id
and pei.information_type = 'PER_KR_EMPLOYMENT_INS_INFO'
and ptp.payroll_id = paa.payroll_id
and p_date_earned between paa.effective_start_date and paa.effective_end_date
and p_date_earned between pp.effective_start_date and pp.effective_end_date
and p_date_earned between ptp.start_date and ptp.end_date
and ptp.end_date >= fnd_date.canonical_to_date(pei_information9);
select pei_information1 ei_exception_code
,pei_information4 ei_exception_type
from per_people_extra_info pei
,per_assignments_f paa
,per_time_periods ptp
where paa.assignment_id = p_assignment_id
and paa.business_group_id = p_business_group_id
and pei.person_id = paa.person_id
and pei.information_type ='PER_KR_EI_EXCEPTIONS'
and p_date_earned between paa.effective_start_date and paa.effective_end_date
and ptp.payroll_id = paa.payroll_id
and p_date_earned between ptp.start_date and ptp.end_date
and fnd_date.canonical_to_date(pei.pei_information2) <= ptp.end_date
and fnd_date.canonical_to_date(pei.pei_information3) >= ptp.start_date
order by pei.pei_information2 desc,pei.pei_information3 desc;
select nvl(cei_information1,0) cei_information1,
nvl(cei_information2,0) cei_information2,
nvl(cei_information3,0) cei_information3,
nvl(cei_information4,0) cei_information4,
nvl(cei_information5,0) cei_information5,
nvl(cei_information6,0) cei_information6,
nvl(cei_information7,0) cei_information7,
nvl(cei_information8,0) cei_information8,
nvl(cei_information9,0) cei_information9,
nvl(cei_information10,0) cei_information10,
nvl(cei_information11,0) cei_information11
--
from per_contact_extra_info_f
--
where contact_Extra_info_id = p_cont_extra_info_id;
SELECT paa.assignment_action_id ass_act_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and to_char(ppa.effective_date, 'YYYY') = p_effective_year
and ppa.report_type = 'YEA'
and ppa.report_qualifier = 'KR'
and ppa.action_type in ('B','X')
order by paa.action_sequence desc ;
select
ctr.cont_information2 itax_law,
nvl(ctr.cont_information11, '0') kr_cont_type,
decode(ctr.contact_type, 'P', '1', 'S', '3', 'A', '4', 'C', '4', 'R', '4', 'O', '4', 'T', '4', '6') cont_type
from
per_contact_relationships ctr
where
ctr.person_id = p_person_id
and ctr.cont_information_category = 'KR'
and ctr.cont_information1 = 'Y'
and ((ctr.cont_information9 ='D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(trunc(add_months(date_end,12),'YYYY')-1,p_date_earned))
or (nvl(ctr.cont_information9,'XXX') <>'D' and p_date_earned between nvl(date_start, p_date_earned) and nvl(date_end, p_date_earned))
)
and ctr.contact_person_id = p_contact_person_id
and ctr.contact_relationship_id = p_contact_relationship_id;
SELECT upper(nvl(prrv.result_value, 'N'))
FROM pay_input_values_f piv
,pay_run_result_values prrv
,pay_run_results prr
,pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_element_types_f pet
WHERE paa.assignment_action_id = l_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.status in ('P', 'PA')
and prr.element_type_id = pet.element_type_id
and pet.element_name = 'LTCI_PREM'
and piv.legislation_code = 'KR'
and pet.legislation_code = 'KR'
and prrv.run_result_id = prr.run_result_id
and piv.input_value_id = prrv.input_value_id
and piv.name = l_input_value_name
and ppa.effective_date
between piv.effective_start_date and piv.effective_end_date;