[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
from per_periods_of_service pds,
per_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_effective_date
between effective_start_date and effective_end_date
and pds.period_of_service_id = asg.period_of_service_id
for update of pds.period_of_service_id nowait;
select 'Y'
from dual
where not exists(
select null
from per_assignment_extra_info
where assignment_id = p_assignment_id
and assignment_extra_info_id <> p_assignment_extra_info_id
and information_type = p_information_type
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(l_effective_date, 'YYYY'));
procedure chk_insert_update(
p_assignment_id in number,
p_information_type in varchar2,
p_aei_information1 in varchar2,
p_aei_information3 in varchar2,
p_aei_information12 in varchar2,
p_aei_information13 in varchar2)
-----------------------------------------------------------------------------
is
l_nts_amt number;
select sum(nvl(aei_information3,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = p_information_type
and aei_information12 = l_aei_information12
and aei_information13 = l_aei_information13
and aei_information15 = '1'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select sum(nvl(aei_information3,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = p_information_type
and aei_information12 = l_aei_information12
and aei_information13 = l_aei_information13
and aei_information15 = '2'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select contact_relationship_id, national_identifier,
decode(nvl(cont_information11,decode(contact_type, 'S','3','P','1','B','5','SISTER','5',
'A','4','C','4','R','4','O','4','T','4','6')),'1','4','2','4','5','5','4','3','3','2','7','3','6') code -- Bug 12714266
from pay_kr_contact_v
where assignment_id = p_assignment_id;
select cei.contact_extra_info_id,
cei.cei_information1,
cei.cei_information2,
cei.cei_information3,
cei.cei_information4,
cei.cei_information5,
cei.cei_information6,
cei.cei_information7,
cei.cei_information8,
cei.cei_information9,
cei.cei_information10,
cei.cei_information11,
cei.cei_information12,
cei.cei_information13,
cei.cei_information14,
cei.cei_information15,
cei.cei_information16, -- Bug 9737699
cei.cei_information17, -- Bug 9737699
cei.cei_information18, -- Bug 14219478
cei.cei_information19, -- Bug 14219478
cei.cei_information20, -- Bug 14219478
cei.cei_information21, -- Bug 14219478
cei.cei_information22, -- Bug 14219478
cei.cei_information23, -- Bug 14219478
cei.cei_information24, -- Bug 14219478
cei.object_version_number,
cei.effective_start_date,
cei.effective_end_date
from per_contact_extra_info_f cei
where cei.contact_relationship_id = p_cont_rel_id
and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
end chk_insert_update;
procedure chk_delete(
p_assignment_id_o in number,
p_information_type_o in varchar2,
p_aei_information1_o in varchar2,
p_aei_information3_o in varchar2,
p_aei_information12_o in varchar2,
p_aei_information13_o in varchar2)
-----------------------------------------------------------------------------
is
l_nts_amt number;
select sum(nvl(aei_information3,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id_o
and information_type = p_information_type_o
and aei_information12 = l_aei_information12
and aei_information13 = l_aei_information13
and aei_information15 = '1'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select sum(nvl(aei_information3,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id_o
and information_type = p_information_type_o
and aei_information12 = l_aei_information12
and aei_information13 = l_aei_information13
and aei_information15 = '2'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select contact_relationship_id, national_identifier,
decode(nvl(cont_information11,decode(contact_type, 'S','3','P','1','B','5','SISTER','5',
'A','4','C','4','R','4','O','4','T','4','6')),'1','4','2','4','5','5','4','3','3','2','7','3','6') code -- Bug 12714266
from pay_kr_contact_v
where assignment_id = p_assignment_id_o;
select cei.contact_extra_info_id,
cei.cei_information1,
cei.cei_information2,
cei.cei_information3,
cei.cei_information4,
cei.cei_information5,
cei.cei_information6,
cei.cei_information7,
cei.cei_information8,
cei.cei_information9,
cei.cei_information10,
cei.cei_information11,
cei.cei_information12,
cei.cei_information13,
cei.cei_information14,
cei.cei_information15,
cei.cei_information16, -- Bug 9737699
cei.cei_information17, -- Bug 9737699
cei.cei_information18, -- Bug 14219478
cei.cei_information19, -- Bug 14219478
cei.cei_information20, -- Bug 14219478
cei.cei_information21, -- Bug 14219478
cei.cei_information22, -- Bug 14219478
cei.cei_information23, -- Bug 14219478
cei.cei_information24, -- Bug 14219478
cei.object_version_number,
cei.effective_start_date,
cei.effective_end_date
from per_contact_extra_info_f cei
where cei.contact_relationship_id = p_cont_rel_id
and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
end chk_delete;
select count(aei_information2)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and assignment_extra_info_id <> p_assignment_extra_info_id
and information_type = p_information_type
and aei_information2 = p_aei_information2
and nvl(aei_information7,'0') = nvl(p_aei_information7,'0') -- Bug 9213683
and nvl(aei_information8,'0') = nvl(p_aei_information8,'0')
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(l_effective_date, 'YYYY');
procedure chk_med_insert_update(
p_assignment_id in number,
p_information_type in varchar2,
p_aei_information1 in varchar2)
-----------------------------------------------------------------------------
is
l_nts_amt number;
select sum(nvl(aei_information3,0) + nvl(aei_information11,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = p_information_type
and aei_information8 = p_aei_information8
and aei_information13 = '1'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select sum(nvl(aei_information3,0) + nvl(aei_information11,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = p_information_type
and aei_information8 = p_aei_information8
and aei_information13 <> '1'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select distinct pkc.contact_relationship_id, pkc.national_identifier
from pay_kr_contact_v pkc
where pkc.assignment_id = p_assignment_id;
select cei.contact_extra_info_id,
cei.cei_information1,
cei.cei_information2,
cei.cei_information3,
cei.cei_information4,
cei.cei_information5,
cei.cei_information6,
cei.cei_information7,
cei.cei_information8,
cei.cei_information9,
cei.cei_information10,
cei.cei_information11,
cei.cei_information12,
cei.cei_information13,
cei.cei_information14,
cei.cei_information15,
cei.cei_information16, -- Bug 9737699
cei.cei_information17, -- Bug 9737699
cei.cei_information18, -- Bug 14219478
cei.cei_information19, -- Bug 14219478
cei.cei_information20, -- Bug 14219478
cei.cei_information21, -- Bug 14219478
cei.cei_information22, -- Bug 14219478
cei.cei_information23, -- Bug 14219478
cei.cei_information24, -- Bug 14219478
cei.object_version_number,
cei.effective_start_date,
cei.effective_end_date
from per_contact_extra_info_f cei
where cei.contact_relationship_id = p_cont_rel_id
and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
end chk_med_insert_update;
procedure chk_med_delete(
p_assignment_id_o in number,
p_information_type_o in varchar2,
p_aei_information1_o in varchar2)
-----------------------------------------------------------------------------
is
l_nts_amt number;
select sum(nvl(aei_information3,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id_o
and information_type = p_information_type_o
and aei_information8 = p_aei_information8
and aei_information13 = '1'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select sum(nvl(aei_information3,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id_o
and information_type = p_information_type_o
and aei_information8 = p_aei_information8
and aei_information13 <> '1'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select distinct contact_relationship_id, national_identifier
from pay_kr_contact_v pkc
where pkc.assignment_id = p_assignment_id_o;
select cei.contact_extra_info_id,
cei.cei_information1,
cei.cei_information2,
cei.cei_information3,
cei.cei_information4,
cei.cei_information5,
cei.cei_information6,
cei.cei_information7,
cei.cei_information8,
cei.cei_information9,
cei.cei_information10,
cei.cei_information11,
cei.cei_information12,
cei.cei_information13,
cei.cei_information14,
cei.cei_information15,
cei.cei_information16, -- Bug 9737699
cei.cei_information17, -- Bug 9737699
cei.cei_information18, -- Bug 14219478
cei.cei_information19, -- Bug 14219478
cei.cei_information20, -- Bug 14219478
cei.cei_information21, -- Bug 14219478
cei.cei_information22, -- Bug 14219478
cei.cei_information23, -- Bug 14219478
cei.cei_information24, -- Bug 14219478
cei.object_version_number,
cei.effective_start_date,
cei.effective_end_date
from per_contact_extra_info_f cei
where cei.contact_relationship_id = p_cont_rel_id
and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
end chk_med_delete;
select count(aei_information2)
from per_assignment_extra_info
where assignment_id = p_assignment_id
and assignment_extra_info_id <> p_assignment_extra_info_id
and information_type = p_information_type
and aei_information2 = p_aei_information2
and aei_information4 = p_aei_information4
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(l_effective_date, 'YYYY');
procedure chk_dpnt_educ_insert_update(
p_assignment_id in number,
p_information_type in varchar2,
p_aei_information1 in varchar2,
p_aei_information6 in varchar2,
p_aei_information7 in varchar2)
-----------------------------------------------------------------------------
is
l_nts_amt number;
select sum(nvl(aei_information4,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = p_information_type
and aei_information2 = l_aei_information2
and aei_information5 = l_aei_information5
and aei_information6 = '1'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select sum(nvl(aei_information4,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = p_information_type
and aei_information2 = l_aei_information2
and aei_information5 = l_aei_information5
and aei_information6 = '2'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select contact_relationship_id, national_identifier,
nvl(cont_information11,decode(contact_type,'P','1','S','3',
'A','4','C','4','R','4','O','4','T','4','BROTHER','5','SISTER','5','6') ) code
from pay_kr_contact_v
where assignment_id = p_assignment_id;
select cei.contact_extra_info_id,
cei.cei_information1,
cei.cei_information2,
cei.cei_information3,
cei.cei_information4,
cei.cei_information5,
cei.cei_information6,
cei.cei_information7,
cei.cei_information8,
cei.cei_information9,
cei.cei_information10,
cei.cei_information11,
cei.cei_information12,
cei.cei_information13,
cei.cei_information14,
cei.cei_information15,
cei.cei_information16, -- Bug 9737699
cei.cei_information17, -- Bug 9737699
cei.cei_information18, -- Bug 14219478
cei.cei_information19, -- Bug 14219478
cei.cei_information20, -- Bug 14219478
cei.cei_information21, -- Bug 14219478
cei.cei_information22, -- Bug 14219478
cei.cei_information23, -- Bug 14219478
cei.cei_information24, -- Bug 14219478
cei.object_version_number,
cei.effective_start_date,
cei.effective_end_date
from per_contact_extra_info_f cei
where cei.contact_relationship_id = p_cont_rel_id
and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
end chk_dpnt_educ_insert_update;
procedure chk_dpnt_educ_delete(
p_assignment_id_o in number,
p_information_type_o in varchar2,
p_aei_information1_o in varchar2)
-----------------------------------------------------------------------------
is
l_nts_amt number;
select sum(nvl(aei_information4,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id_o
and information_type = p_information_type_o
and aei_information2 = l_aei_information2
and aei_information5 = l_aei_information5
and aei_information6 = '1'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select sum(nvl(aei_information4,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id_o
and information_type = p_information_type_o
and aei_information2 = l_aei_information2
and aei_information5 = l_aei_information5
and aei_information6 = '2'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select contact_relationship_id, national_identifier,
nvl(cont_information11,decode(contact_type,'P','1','S','3',
'A','4','C','4','R','4','O','4','T','4','BROTHER','5','SISTER','5','6')) code
from pay_kr_contact_v
where assignment_id = p_assignment_id_o;
select cei.contact_extra_info_id,
cei.cei_information1,
cei.cei_information2,
cei.cei_information3,
cei.cei_information4,
cei.cei_information5,
cei.cei_information6,
cei.cei_information7,
cei.cei_information8,
cei.cei_information9,
cei.cei_information10,
cei.cei_information11,
cei.cei_information12,
cei.cei_information13,
cei.cei_information14,
cei.cei_information15,
cei.cei_information16, -- Bug 9737699
cei.cei_information17, -- Bug 9737699
cei.cei_information18, -- Bug 14219478
cei.cei_information19, -- Bug 14219478
cei.cei_information20, -- Bug 14219478
cei.cei_information21, -- Bug 14219478
cei.cei_information22, -- Bug 14219478
cei.cei_information23, -- Bug 14219478
cei.cei_information24, -- Bug 14219478
cei.object_version_number,
cei.effective_start_date,
cei.effective_end_date
from per_contact_extra_info_f cei
where cei.contact_relationship_id = p_cont_rel_id
and cei.information_type = 'KR_DPNT_EXPENSE_INFO'
and to_char(cei.effective_start_date, 'YYYY') = to_char(p_effective_date,'YYYY');
end chk_dpnt_educ_delete;
select pay_kr_ff_functions_pkg.aged_flag(p_aei_information8,l_effective_date)
from dual;
select 'Y'
from per_assignment_extra_info
where
assignment_id = p_assignment_id
and information_type = p_information_type
and aei_information6 = p_aei_information6
and assignment_extra_info_id <> nvl(p_assignment_extra_info_id,0)
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(l_effective_date, 'YYYY');
select 'Y'
from per_assignment_extra_info
where
assignment_id = p_assignment_id
and information_type = p_information_type
and aei_information2 = p_aei_information2
and aei_information4 = p_aei_information4
and assignment_extra_info_id <> nvl(p_assignment_extra_info_id,0)
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(l_effective_date, 'YYYY');
select to_number(glb.global_value,'99999999999999999999.99999') -- Bug 5726158
from ff_globals_f glb
where glb.global_name = p_glbvar
and p_process_date between glb.effective_start_date and glb.effective_end_date;
select fnd_date.canonical_to_date(aei_information2) cont_start_date,
fnd_date.canonical_to_date(aei_information3) cont_end_date
from per_assignment_extra_info
where
assignment_id = p_assignment_id
and information_type = p_information_type
and assignment_extra_info_id <> nvl(p_assignment_extra_info_id,0)
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(fnd_date.canonical_to_date(p_aei_information1), 'YYYY');
procedure chk_don_insert_update(
p_assignment_id in number,
p_information_type in varchar2,
p_aei_information1 in varchar2,
p_aei_information12 in varchar2)
-----------------------------------------------------------------------------
is
l_donated_amt number;
select sum(nvl(aei_information3,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = p_information_type
and aei_information5 = p_aei_information5
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select assignment_extra_info_id
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information2 = p_donated_year
and aei_information8 = p_aei_information5
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select assignment_extra_info_id
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information2 = p_donated_year
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY')
and aei_information8 not in (select distinct aei_information5
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DETAIL_DONATION_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY'));
select distinct aei_information5
from per_assignment_extra_info
where assignment_id = p_assignment_id
and information_type = 'KR_YEA_DETAIL_DONATION_INFO'
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_don_type_asg_info_id,
p_object_version_number => l_ovn,
p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
p_aei_information1 => fnd_date.date_to_canonical(l_effective_date),
p_aei_information2 => l_donated_year,
p_aei_information3 => '0',
p_aei_information4 => '0',
p_aei_information5 => '0',
p_aei_information6 => null,
p_aei_information7 => null,
p_aei_information8 => i.aei_information5,
p_aei_information9 => l_donated_amt,
p_aei_information10 => l_donated_amt,
p_aei_information11 => '0',
p_aei_information12 => null,
p_aei_information13 => null,
p_aei_information14 => null,
p_aei_information15 => null,
p_aei_information16 => null,
p_aei_information17 => null,
p_aei_information18 => null,
p_aei_information19 => null,
p_aei_information20 => null,
p_aei_information21 => null,
p_aei_information22 => null,
p_aei_information23 => null,
p_aei_information24 => null,
p_aei_information25 => null,
p_aei_information26 => null,
p_aei_information27 => null,
p_aei_information28 => null,
p_aei_information29 => null,
p_aei_information30 => null
);
delete
from per_assignment_extra_info
where assignment_extra_info_id = j.assignment_extra_info_id
and aei_information2 = l_donated_year;
end chk_don_insert_update;
procedure chk_don_delete(
p_assignment_id_o in number,
p_information_type_o in varchar2,
p_aei_information1_o in varchar2,
p_aei_information5_o in varchar2,
p_aei_information12_o in varchar2)
-----------------------------------------------------------------------------
is
l_donated_amt number;
select sum(nvl(aei_information3,0))
from per_assignment_extra_info
where assignment_id = p_assignment_id_o
and information_type = p_information_type_o
and aei_information5 = p_aei_information5_o
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
select assignment_extra_info_id,
object_version_number
from per_assignment_extra_info
where assignment_id = p_assignment_id_o
and information_type = 'KR_YEA_DONATION_TYPE_DETAIL'
and aei_information2 = p_donated_year
and aei_information8 = p_aei_information5_o
and trunc(fnd_date.canonical_to_date(aei_information1), 'YYYY') = trunc(p_effective_date, 'YYYY');
hr_assignment_extra_info_api.update_assignment_extra_info(
p_validate => false,
p_assignment_extra_info_id => l_don_type_asg_info_id,
p_object_version_number => l_ovn,
p_aei_information_category => 'KR_YEA_DONATION_TYPE_DETAIL',
p_aei_information1 => fnd_date.date_to_canonical(l_effective_date),
p_aei_information2 => l_donated_year,
p_aei_information3 => '0',
p_aei_information4 => '0',
p_aei_information5 => '0',
p_aei_information6 => null,
p_aei_information7 => null,
p_aei_information8 => p_aei_information5_o,
p_aei_information9 => l_donated_amt,
p_aei_information10 => l_donated_amt,
p_aei_information11 => '0',
p_aei_information12 => null,
p_aei_information13 => null,
p_aei_information14 => null,
p_aei_information15 => null,
p_aei_information16 => null,
p_aei_information17 => null,
p_aei_information18 => null,
p_aei_information19 => null,
p_aei_information20 => null,
p_aei_information21 => null,
p_aei_information22 => null,
p_aei_information23 => null,
p_aei_information24 => null,
p_aei_information25 => null,
p_aei_information26 => null,
p_aei_information27 => null,
p_aei_information28 => null,
p_aei_information29 => null,
p_aei_information30 => null
);
delete from per_assignment_extra_info
where assignment_extra_info_id = l_don_type_asg_info_id
and aei_information2 = l_donated_year;
end chk_don_delete;