The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor dt_row_exists is select 'Y' from pay_element_entries_f a, pay_element_entries_f b
where a.element_entry_id = p_element_entry_id
and a.effective_start_date = p_effective_start_Date
and b.element_entry_id = a.element_entry_id
and b.effective_start_date = a.effective_end_Date+1;
if(hr_api.g_update = p_datetrack_mode) then
if( p_effective_date < p_effective_start_date)then
-- throw an error.
--'Please check the effective date as the changes can not become effective before the record started.'
null;
l_datetrack_mode:=hr_api.g_update_change_insert;
l_datetrack_mode:= hr_api.g_update;
elsif(hr_api.g_delete = p_datetrack_mode) then
if( p_effective_date < p_effective_start_date)then
null;
l_datetrack_mode:= hr_api.g_delete;
PROCEDURE ICD_UPDATE_ELEMENT_ENTRY
(p_validate in number default 0
,p_datetrack_update_mode in varchar2
,p_effective_date in date
,p_business_group_id in number
,p_element_entry_id in number
,p_object_version_number in number
,p_cost_allocation_keyflex_id in number
,p_attribute_category in varchar2
,p_attribute1 in varchar2
,p_attribute2 in varchar2
,p_attribute3 in varchar2
,p_attribute4 in varchar2
,p_attribute5 in varchar2
,p_attribute6 in varchar2
,p_attribute7 in varchar2
,p_attribute8 in varchar2
,p_attribute9 in varchar2
,p_attribute10 in varchar2
,p_attribute11 in varchar2
,p_attribute12 in varchar2
,p_attribute13 in varchar2
,p_attribute14 in varchar2
,p_attribute15 in varchar2
,p_attribute16 in varchar2
,p_attribute17 in varchar2
,p_attribute18 in varchar2
,p_attribute19 in varchar2
,p_attribute20 in varchar2
,p_input_value_id1 in number
,p_input_value_id2 in number
,p_input_value_id3 in number
,p_input_value_id4 in number
,p_input_value_id5 in number
,p_input_value_id6 in number
,p_input_value_id7 in number
,p_input_value_id8 in number
,p_input_value_id9 in number
,p_input_value_id10 in number
,p_input_value_id11 in number
,p_input_value_id12 in number
,p_input_value_id13 in number
,p_input_value_id14 in number
,p_input_value_id15 in number
,p_entry_value1 in varchar2
,p_entry_value2 in varchar2
,p_entry_value3 in varchar2
,p_entry_value4 in varchar2
,p_entry_value5 in varchar2
,p_entry_value6 in varchar2
,p_entry_value7 in varchar2
,p_entry_value8 in varchar2
,p_entry_value9 in varchar2
,p_entry_value10 in varchar2
,p_entry_value11 in varchar2
,p_entry_value12 in varchar2
,p_entry_value13 in varchar2
,p_entry_value14 in varchar2
,p_entry_value15 in varchar2
,p_entry_information_category in varchar2
,p_entry_information1 in varchar2
,p_entry_information2 in varchar2
,p_entry_information3 in varchar2
,p_entry_information4 in varchar2
,p_entry_information5 in varchar2
,p_entry_information6 in varchar2
,p_entry_information7 in varchar2
,p_entry_information8 in varchar2
,p_entry_information9 in varchar2
,p_entry_information10 in varchar2
,p_entry_information11 in varchar2
,p_entry_information12 in varchar2
,p_entry_information13 in varchar2
,p_entry_information14 in varchar2
,p_entry_information15 in varchar2
,p_entry_information16 in varchar2
,p_entry_information17 in varchar2
,p_entry_information18 in varchar2
,p_entry_information19 in varchar2
,p_entry_information20 in varchar2
,p_entry_information21 in varchar2
,p_entry_information22 in varchar2
,p_entry_information23 in varchar2
,p_entry_information24 in varchar2
,p_entry_information25 in varchar2
,p_entry_information26 in varchar2
,p_entry_information27 in varchar2
,p_entry_information28 in varchar2
,p_entry_information29 in varchar2
,p_entry_information30 in varchar2
,p_effective_start_date in date
,p_effective_end_date in date
-- ,p_icd_effective_date in date
-- ,p_warning out nocopy number
) is
cursor c_input_values is
select * from
pay_input_values_f
where element_type_id = (select element_type_id from pay_element_entries_f where
element_entry_id = p_element_entry_id and p_effective_date between effective_start_date and effective_end_date)
and p_effective_date between effective_start_date and effective_end_date
order by input_value_id asc;
l_update_warning boolean;
pay_element_entry_api.update_element_entry
(p_validate => l_validate
,p_datetrack_update_mode => get_datetrack_mode(p_element_entry_id,p_effective_start_date,p_effective_date,p_datetrack_update_mode)
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
,p_attribute_category => p_attribute_category
,p_attribute1 => p_attribute1
,p_attribute2 => p_attribute2
,p_attribute3 => p_attribute3
,p_attribute4 => p_attribute4
,p_attribute5 => p_attribute5
,p_attribute6 => p_attribute6
,p_attribute7 => p_attribute7
,p_attribute8 => p_attribute8
,p_attribute9 => p_attribute9
,p_attribute10 => p_attribute10
,p_attribute11 => p_attribute11
,p_attribute12 => p_attribute12
,p_attribute13 => p_attribute13
,p_attribute14 => p_attribute14
,p_attribute15 => p_attribute15
,p_attribute16 => p_attribute16
,p_attribute17 => p_attribute17
,p_attribute18 => p_attribute18
,p_attribute19 => p_attribute19
,p_attribute20 => p_attribute20
,p_input_value_id1 => p_input_value_id1
,p_input_value_id2 => p_input_value_id2
,p_input_value_id3 => p_input_value_id3
,p_input_value_id4 => p_input_value_id4
,p_input_value_id5 => p_input_value_id5
,p_input_value_id6 => p_input_value_id6
,p_input_value_id7 => p_input_value_id7
,p_input_value_id8 => p_input_value_id8
,p_input_value_id9 => p_input_value_id9
,p_input_value_id10 => p_input_value_id10
,p_input_value_id11 => p_input_value_id11
,p_input_value_id12 => p_input_value_id12
,p_input_value_id13 => p_input_value_id13
,p_input_value_id14 => p_input_value_id14
,p_input_value_id15 => p_input_value_id15
,p_entry_value1 => l_entry_value1
,p_entry_value2 => l_entry_value2
,p_entry_value3 => l_entry_value3
,p_entry_value4 => l_entry_value4
,p_entry_value5 => l_entry_value5
,p_entry_value6 => l_entry_value6
,p_entry_value7 => l_entry_value7
,p_entry_value8 => l_entry_value8
,p_entry_value9 => l_entry_value9
,p_entry_value10 => l_entry_value10
,p_entry_value11 => l_entry_value11
,p_entry_value12 => l_entry_value12
,p_entry_value13 => l_entry_value13
,p_entry_value14 => l_entry_value14
,p_entry_value15 => l_entry_value15
,p_entry_information_category => p_entry_information_category
,p_entry_information1 => p_entry_information1
,p_entry_information2 => p_entry_information2
,p_entry_information3 => p_entry_information3
,p_entry_information4 => p_entry_information4
,p_entry_information5 => p_entry_information5
,p_entry_information6 => p_entry_information6
,p_entry_information7 => p_entry_information7
,p_entry_information8 => p_entry_information8
,p_entry_information9 => p_entry_information9
,p_entry_information10 => p_entry_information10
,p_entry_information11 => p_entry_information11
,p_entry_information12 => p_entry_information12
,p_entry_information13 => p_entry_information13
,p_entry_information14 => p_entry_information14
,p_entry_information15 => p_entry_information15
,p_entry_information16 => p_entry_information16
,p_entry_information17 => p_entry_information17
,p_entry_information18 => p_entry_information18
,p_entry_information19 => p_entry_information19
,p_entry_information20 => p_entry_information20
,p_entry_information21 => p_entry_information21
,p_entry_information22 => p_entry_information22
,p_entry_information23 => p_entry_information23
,p_entry_information24 => p_entry_information24
,p_entry_information25 => p_entry_information25
,p_entry_information26 => p_entry_information26
,p_entry_information27 => p_entry_information27
,p_entry_information28 => p_entry_information28
,p_entry_information29 => p_entry_information29
,p_entry_information30 => p_entry_information30
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_update_warning
);
end ICD_UPDATE_ELEMENT_ENTRY;
select * from
pay_input_values_f
where element_type_id = (select element_type_id from pay_element_links_f where
element_link_id = p_element_link_id and p_effective_date between effective_start_date and effective_end_date)
and p_effective_date between effective_start_date and effective_end_date
order by input_value_id asc;
l_delete_warning boolean;
pay_element_entry_api.delete_element_entry
(p_validate => false
,p_datetrack_delete_mode => hr_api.g_delete
,p_effective_date => p_effective_end_date
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning
);
PROCEDURE ICD_DELETE_ELEMENT_ENTRY(
p_validate in number default 0
,p_datetrack_delete_mode in varchar2
,p_effective_date in date
,p_element_entry_id in number
,p_object_version_number in number
,p_effective_start_date in date
,p_effective_end_date in date
-- ,p_icd_effective_date in date
-- ,p_warning out nocopy number
) is
l_object_version_number number;
l_delete_warning boolean;
pay_element_entry_api.delete_element_entry
(p_validate => l_validate
,p_datetrack_delete_mode => get_datetrack_mode(p_element_entry_id,p_effective_start_date,p_effective_date,p_datetrack_delete_mode)
,p_effective_date => p_effective_date
,p_element_entry_id => p_element_entry_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_delete_warning
);
end ICD_DELETE_ELEMENT_ENTRY;
select hats.transaction_step_id
from hr_api_transaction_steps hats
where hats.transaction_id = p_transaction_id
and hats.api_name = upper(g_package || 'process_api')
order by hats.transaction_step_id;
IF 'UPDATE' = p_transaction_row.dml_operation then
if(p_transaction_row.effective_date is null )then
if(p_transaction_row.effective_start_date > trunc(sysdate) ) then
effectiveDate := p_transaction_row.effective_start_date;
ICD_UPDATE_ELEMENT_ENTRY
(p_validate => p_validate
,p_datetrack_update_mode => p_transaction_row.datetrack_mode
,p_effective_date => effectiveDate
,p_business_group_id => p_transaction_row.business_group_id
,p_element_entry_id => p_transaction_row.element_entry_id
,p_object_version_number => p_transaction_row.e_object_version_number
,p_cost_allocation_keyflex_id => p_transaction_row.cost_allocation_keyflex_id
,p_attribute_category => p_transaction_row.attribute_category
,p_attribute1 => p_transaction_row.attribute1
,p_attribute2 => p_transaction_row.attribute2
,p_attribute3 => p_transaction_row.attribute3
,p_attribute4 => p_transaction_row.attribute4
,p_attribute5 => p_transaction_row.attribute5
,p_attribute6 => p_transaction_row.attribute6
,p_attribute7 => p_transaction_row.attribute7
,p_attribute8 => p_transaction_row.attribute8
,p_attribute9 => p_transaction_row.attribute9
,p_attribute10 => p_transaction_row.attribute10
,p_attribute11 => p_transaction_row.attribute11
,p_attribute12 => p_transaction_row.attribute12
,p_attribute13 => p_transaction_row.attribute13
,p_attribute14 => p_transaction_row.attribute14
,p_attribute15 => p_transaction_row.attribute15
,p_attribute16 => p_transaction_row.attribute16
,p_attribute17 => p_transaction_row.attribute17
,p_attribute18 => p_transaction_row.attribute18
,p_attribute19 => p_transaction_row.attribute19
,p_attribute20 => p_transaction_row.attribute20
,p_input_value_id1 => p_transaction_row.input_value_id1
,p_input_value_id2 => p_transaction_row.input_value_id2
,p_input_value_id3 => p_transaction_row.input_value_id3
,p_input_value_id4 => p_transaction_row.input_value_id4
,p_input_value_id5 => p_transaction_row.input_value_id5
,p_input_value_id6 => p_transaction_row.input_value_id6
,p_input_value_id7 => p_transaction_row.input_value_id7
,p_input_value_id8 => p_transaction_row.input_value_id8
,p_input_value_id9 => p_transaction_row.input_value_id9
,p_input_value_id10 => p_transaction_row.input_value_id10
,p_input_value_id11 => p_transaction_row.input_value_id11
,p_input_value_id12 => p_transaction_row.input_value_id12
,p_input_value_id13 => p_transaction_row.input_value_id13
,p_input_value_id14 => p_transaction_row.input_value_id14
,p_input_value_id15 => p_transaction_row.input_value_id15
,p_entry_value1 => p_transaction_row.input_value1
,p_entry_value2 => p_transaction_row.input_value2
,p_entry_value3 => p_transaction_row.input_value3
,p_entry_value4 => p_transaction_row.input_value4
,p_entry_value5 => p_transaction_row.input_value5
,p_entry_value6 => p_transaction_row.input_value6
,p_entry_value7 => p_transaction_row.input_value7
,p_entry_value8 => p_transaction_row.input_value8
,p_entry_value9 => p_transaction_row.input_value9
,p_entry_value10 => p_transaction_row.input_value10
,p_entry_value11 => p_transaction_row.input_value11
,p_entry_value12 => p_transaction_row.input_value12
,p_entry_value13 => p_transaction_row.input_value13
,p_entry_value14 => p_transaction_row.input_value14
,p_entry_value15 => p_transaction_row.input_value15
,p_entry_information_category => p_transaction_row.entry_information_category
,p_entry_information1 => p_transaction_row.entry_information1
,p_entry_information2 => p_transaction_row.entry_information2
,p_entry_information3 => p_transaction_row.entry_information3
,p_entry_information4 => p_transaction_row.entry_information4
,p_entry_information5 => p_transaction_row.entry_information5
,p_entry_information6 => p_transaction_row.entry_information6
,p_entry_information7 => p_transaction_row.entry_information7
,p_entry_information8 => p_transaction_row.entry_information8
,p_entry_information9 => p_transaction_row.entry_information9
,p_entry_information10 => p_transaction_row.entry_information10
,p_entry_information11 => p_transaction_row.entry_information11
,p_entry_information12 => p_transaction_row.entry_information12
,p_entry_information13 => p_transaction_row.entry_information13
,p_entry_information14 => p_transaction_row.entry_information14
,p_entry_information15 => p_transaction_row.entry_information15
,p_entry_information16 => p_transaction_row.entry_information16
,p_entry_information17 => p_transaction_row.entry_information17
,p_entry_information18 => p_transaction_row.entry_information18
,p_entry_information19 => p_transaction_row.entry_information19
,p_entry_information20 => p_transaction_row.entry_information20
,p_entry_information21 => p_transaction_row.entry_information21
,p_entry_information22 => p_transaction_row.entry_information22
,p_entry_information23 => p_transaction_row.entry_information23
,p_entry_information24 => p_transaction_row.entry_information24
,p_entry_information25 => p_transaction_row.entry_information25
,p_entry_information26 => p_transaction_row.entry_information26
,p_entry_information27 => p_transaction_row.entry_information27
,p_entry_information28 => p_transaction_row.entry_information28
,p_entry_information29 => p_transaction_row.entry_information29
,p_entry_information30 => p_transaction_row.entry_information30
,p_effective_start_date => p_transaction_row.effective_start_Date
,p_effective_end_date => p_transaction_row.effective_end_date
--,p_icd_effective_date => p_icd_effective_date
--,p_warning => l_warning
);
elsif ('INSERT' = p_transaction_row.dml_operation) then
ICD_CREATE_ELEMENT_ENTRY
(p_validate => p_validate
,p_effective_date => nvl(p_transaction_row.effective_date,TRUNC(SYSDATE))
,p_business_group_id => p_transaction_row.business_group_id
,p_assignment_id => p_transaction_row.assignment_id
,p_element_link_id => p_transaction_row.element_link_id
,p_entry_type => 'E'
,p_cost_allocation_keyflex_id => p_transaction_row.cost_allocation_keyflex_id
,p_attribute_category => p_transaction_row.attribute_category
,p_attribute1 => p_transaction_row.attribute1
,p_attribute2 => p_transaction_row.attribute2
,p_attribute3 => p_transaction_row.attribute3
,p_attribute4 => p_transaction_row.attribute4
,p_attribute5 => p_transaction_row.attribute5
,p_attribute6 => p_transaction_row.attribute6
,p_attribute7 => p_transaction_row.attribute7
,p_attribute8 => p_transaction_row.attribute8
,p_attribute9 => p_transaction_row.attribute9
,p_attribute10 => p_transaction_row.attribute10
,p_attribute11 => p_transaction_row.attribute11
,p_attribute12 => p_transaction_row.attribute12
,p_attribute13 => p_transaction_row.attribute13
,p_attribute14 => p_transaction_row.attribute14
,p_attribute15 => p_transaction_row.attribute15
,p_attribute16 => p_transaction_row.attribute16
,p_attribute17 => p_transaction_row.attribute17
,p_attribute18 => p_transaction_row.attribute18
,p_attribute19 => p_transaction_row.attribute19
,p_attribute20 => p_transaction_row.attribute20
,p_input_value_id1 => p_transaction_row.input_value_id1
,p_input_value_id2 => p_transaction_row.input_value_id2
,p_input_value_id3 => p_transaction_row.input_value_id3
,p_input_value_id4 => p_transaction_row.input_value_id4
,p_input_value_id5 => p_transaction_row.input_value_id5
,p_input_value_id6 => p_transaction_row.input_value_id6
,p_input_value_id7 => p_transaction_row.input_value_id7
,p_input_value_id8 => p_transaction_row.input_value_id8
,p_input_value_id9 => p_transaction_row.input_value_id9
,p_input_value_id10 => p_transaction_row.input_value_id10
,p_input_value_id11 => p_transaction_row.input_value_id11
,p_input_value_id12 => p_transaction_row.input_value_id12
,p_input_value_id13 => p_transaction_row.input_value_id13
,p_input_value_id14 => p_transaction_row.input_value_id14
,p_input_value_id15 => p_transaction_row.input_value_id15
,p_entry_value1 => p_transaction_row.input_value1
,p_entry_value2 => p_transaction_row.input_value2
,p_entry_value3 => p_transaction_row.input_value3
,p_entry_value4 => p_transaction_row.input_value4
,p_entry_value5 => p_transaction_row.input_value5
,p_entry_value6 => p_transaction_row.input_value6
,p_entry_value7 => p_transaction_row.input_value7
,p_entry_value8 => p_transaction_row.input_value8
,p_entry_value9 => p_transaction_row.input_value9
,p_entry_value10 => p_transaction_row.input_value10
,p_entry_value11 => p_transaction_row.input_value11
,p_entry_value12 => p_transaction_row.input_value12
,p_entry_value13 => p_transaction_row.input_value13
,p_entry_value14 => p_transaction_row.input_value14
,p_entry_value15 => p_transaction_row.input_value15
,p_entry_information_category => p_transaction_row.entry_information_category
,p_entry_information1 => p_transaction_row.entry_information1
,p_entry_information2 => p_transaction_row.entry_information2
,p_entry_information3 => p_transaction_row.entry_information3
,p_entry_information4 => p_transaction_row.entry_information4
,p_entry_information5 => p_transaction_row.entry_information5
,p_entry_information6 => p_transaction_row.entry_information6
,p_entry_information7 => p_transaction_row.entry_information7
,p_entry_information8 => p_transaction_row.entry_information8
,p_entry_information9 => p_transaction_row.entry_information9
,p_entry_information10 => p_transaction_row.entry_information10
,p_entry_information11 => p_transaction_row.entry_information11
,p_entry_information12 => p_transaction_row.entry_information12
,p_entry_information13 => p_transaction_row.entry_information13
,p_entry_information14 => p_transaction_row.entry_information14
,p_entry_information15 => p_transaction_row.entry_information15
,p_entry_information16 => p_transaction_row.entry_information16
,p_entry_information17 => p_transaction_row.entry_information17
,p_entry_information18 => p_transaction_row.entry_information18
,p_entry_information19 => p_transaction_row.entry_information19
,p_entry_information20 => p_transaction_row.entry_information20
,p_entry_information21 => p_transaction_row.entry_information21
,p_entry_information22 => p_transaction_row.entry_information22
,p_entry_information23 => p_transaction_row.entry_information23
,p_entry_information24 => p_transaction_row.entry_information24
,p_entry_information25 => p_transaction_row.entry_information25
,p_entry_information26 => p_transaction_row.entry_information26
,p_entry_information27 => p_transaction_row.entry_information27
,p_entry_information28 => p_transaction_row.entry_information28
,p_entry_information29 => p_transaction_row.entry_information29
,p_entry_information30 => p_transaction_row.entry_information30
,p_effective_start_date => p_transaction_row.effective_start_Date
,p_effective_end_date => p_transaction_row.effective_end_date
-- ,p_icd_effective_date => p_icd_effective_date
-- ,p_warning => l_warning
);
elsif ('DELETE' = p_transaction_row.dml_operation) then
ICD_DELETE_ELEMENT_ENTRY(
p_validate => p_validate
,p_datetrack_delete_mode => p_transaction_row.datetrack_mode
,p_effective_date => p_transaction_row.effective_date
,p_element_entry_id => p_transaction_row.element_entry_id
,p_object_version_number => p_transaction_row.e_object_version_number
,p_effective_start_date => p_transaction_row.effective_start_Date
,p_effective_end_date => p_transaction_row.effective_end_date
-- ,p_icd_effective_date => p_icd_effective_date
-- ,p_warning => l_warning
);
select actn.actn_typ_id,popl.pl_id,popl.mandatory,popl.actn_typ_due_dt_cd
from
ben_popl_actn_typ_f popl,ben_actn_typ_tl actn
where popl.pl_id = p_pl_id
and p_effective_Date between popl.effective_start_Date and popl.effective_end_date
and actn.language = userenv('LANG')
and popl.actn_typ_id = actn.actn_typ_id
and not exists
(select 'Y'
from ben_person_action_items per
where per.person_id = p_person_id
and per.complete_date is not null
and per.actn_typ_id = actn.actn_typ_id
and popl.once_or_always <> 'ALW'
and ((popl.once_or_always = 'ONCE') or
(popl.once_or_always = 'PLAN' and per.pl_id = popl.pl_id)));
insert into ben_person_action_items
(PERSON_ACTION_ITEM_ID
,ACTN_TYP_ID
,TRANSACTION_TYPE
,PERSON_ID
,ASSIGNMENT_ID
,EFFECTIVE_DATE
,PL_ID
,STATUS
,DUE_DATE
,OBJECT_VERSION_NUMBER
,COMPLETE_DATE
,VOID_DATE
,TRANSACTION_ID
)
values
(ben_person_action_items_s.nextval
,l_action_items.actn_typ_id
,'ICD'
,p_person_id
,p_assignment_id
,p_effective_date
,p_pl_id
,'OPEN'
,l_returned_date
,1
,null
,null
,p_icd_transaction_id
);
procedure delete_transaction_row(p_icd_transaction_id number)
is
begin
delete from ben_icd_transaction where icd_transaction_id = p_icd_transaction_id ;
end delete_transaction_row;
update ben_icd_transaction
set status = 'SP'
where icd_transaction_id = p_icd_transaction_id;
select *
from ben_icd_transaction
where transaction_id = (select transaction_id from hr_api_transaction_steps where transaction_step_id = p_transaction_step_id)
order by dml_operation;
if(l_transaction_row.dml_operation <> 'DELETE') then
create_person_action_items
(p_person_id => l_transaction_row.person_id
,p_assignment_id => l_transaction_row.assignment_id
,p_pl_id => l_transaction_row.pl_id
,p_icd_transaction_id =>l_transaction_row.icd_transaction_id
,p_effective_date => l_effective_date
,p_mandatory_action_item =>l_mandatory_action_item
);
cursor c_transaction_row is select * from ben_icd_transaction where
icd_transaction_id = p_icd_transaction_id
order by dml_operation,element_entry_id,effective_start_date desc;
delete_transaction_row(l_transaction_row.icd_transaction_id );