[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
type t_update_entry_rec is record
(cost_allocation_keyflex_id number,
updating_action_id number,
updating_action_type varchar2(30),
original_entry_id number,
creator_type varchar2(30),
comment_id number,
creator_id number,
reason varchar2(30),
subpriority number,
date_earned date,
personal_payment_method_id number,
attribute_category varchar2(60),
attribute1 varchar2(150),
attribute2 varchar2(150),
attribute3 varchar2(150),
attribute4 varchar2(150),
attribute5 varchar2(150),
attribute6 varchar2(150),
attribute7 varchar2(150),
attribute8 varchar2(150),
attribute9 varchar2(150),
attribute10 varchar2(150),
attribute11 varchar2(150),
attribute12 varchar2(150),
attribute13 varchar2(150),
attribute14 varchar2(150),
attribute15 varchar2(150),
attribute16 varchar2(150),
attribute17 varchar2(150),
attribute18 varchar2(150),
attribute19 varchar2(150),
attribute20 varchar2(150),
effective_start_date date,
effective_end_date date,
target_entry_id number,
entry_type varchar2(30),
element_link_id number,
assignment_id number,
element_type_id number,
processing_type varchar2(30),
input_currency_code varchar2(30),
output_currency_code varchar2(30),
entry_information_category varchar2(30),
entry_information1 varchar2(150),
entry_information2 varchar2(150),
entry_information3 varchar2(150),
entry_information4 varchar2(150),
entry_information5 varchar2(150),
entry_information6 varchar2(150),
entry_information7 varchar2(150),
entry_information8 varchar2(150),
entry_information9 varchar2(150),
entry_information10 varchar2(150),
entry_information11 varchar2(150),
entry_information12 varchar2(150),
entry_information13 varchar2(150),
entry_information14 varchar2(150),
entry_information15 varchar2(150),
entry_information16 varchar2(150),
entry_information17 varchar2(150),
entry_information18 varchar2(150),
entry_information19 varchar2(150),
entry_information20 varchar2(150),
entry_information21 varchar2(150),
entry_information22 varchar2(150),
entry_information23 varchar2(150),
entry_information24 varchar2(150),
entry_information25 varchar2(150),
entry_information26 varchar2(150),
entry_information27 varchar2(150),
entry_information28 varchar2(150),
entry_information29 varchar2(150),
entry_information30 varchar2(150)
);
type t_delete_entry_rec is record
(original_entry_id number,
effective_start_date date,
effective_end_date date,
assignment_id number,
element_link_id number,
entry_type varchar2(30),
creator_type varchar2(30),
creator_id number,
target_entry_id number,
element_type_id number,
processing_type varchar2(30),
non_payments_flag varchar2(30),
personal_payment_method_id number);
Cursor select_entry_value is
select peev.input_value_id,
peev.screen_entry_value
from pay_element_entry_values_f peev
where peev.element_entry_id = p_element_entry_id
and p_driving_date
between peev.effective_start_date
and peev.effective_end_date;
select 'Y'
into l_check_entry_values
from sys.dual
where exists
(select 1
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id
and p_driving_date
between pee.effective_start_date
and pee.effective_end_date
and nvl(pee.cost_allocation_keyflex_id,hr_api.g_number) =
decode(p_cost_allocation_keyflex_id,
hr_api.g_number, nvl(pee.cost_allocation_keyflex_id,hr_api.g_number),
p_cost_allocation_keyflex_id)
and nvl(pee.updating_action_id,hr_api.g_number) =
decode(p_updating_action_id,
hr_api.g_number, nvl(pee.updating_action_id,hr_api.g_number), p_updating_action_id)
and nvl(pee.updating_action_type,hr_api.g_varchar2) =
decode(p_updating_action_type,
hr_api.g_varchar2, nvl(pee.updating_action_type,hr_api.g_varchar2), p_updating_action_type)
and nvl(pee.original_entry_id,hr_api.g_number) =
decode(p_original_entry_id, hr_api.g_number, nvl(pee.original_entry_id,hr_api.g_number),
p_original_entry_id)
and pee.creator_type =
decode(p_creator_type, hr_api.g_varchar2, pee.creator_type, p_creator_type)
and nvl(pee.comment_id,hr_api.g_number) =
decode(p_comment_id, hr_api.g_number, nvl(pee.comment_id,hr_api.g_number), p_comment_id)
and nvl(pee.creator_id,hr_api.g_number) =
decode(p_creator_id, hr_api.g_number, nvl(pee.creator_id,hr_api.g_number), p_creator_id)
and nvl(pee.reason,hr_api.g_varchar2) =
decode(p_reason, hr_api.g_varchar2, nvl(pee.reason,hr_api.g_varchar2), p_reason)
and nvl(pee.subpriority,hr_api.g_number) =
decode(p_subpriority,
hr_api.g_number, nvl(pee.subpriority,hr_api.g_number), p_subpriority)
and nvl(pee.date_earned,hr_api.g_date) =
decode(p_date_earned,
hr_api.g_date, nvl(pee.date_earned,hr_api.g_date), p_date_earned)
and nvl(pee.personal_payment_method_id,hr_api.g_number) =
decode(p_personal_payment_method_id,
hr_api.g_number, nvl(pee.personal_payment_method_id,hr_api.g_number), p_personal_payment_method_id)
and nvl(pee.attribute_category, hr_api.g_varchar2) =
decode(p_attribute_category, hr_api.g_varchar2,
nvl(pee.attribute_category,hr_api.g_varchar2),
p_attribute_category)
and nvl(pee.attribute1,hr_api.g_varchar2) =
decode(p_attribute1, hr_api.g_varchar2, nvl(pee.attribute1,hr_api.g_varchar2),
p_attribute1)
and nvl(pee.attribute2,hr_api.g_varchar2) =
decode(p_attribute2, hr_api.g_varchar2, nvl(pee.attribute2,hr_api.g_varchar2),
p_attribute2)
and nvl(pee.attribute3,hr_api.g_varchar2) =
decode(p_attribute3, hr_api.g_varchar2, nvl(pee.attribute3,hr_api.g_varchar2),
p_attribute3)
and nvl(pee.attribute4,hr_api.g_varchar2) =
decode(p_attribute4, hr_api.g_varchar2, nvl(pee.attribute4,hr_api.g_varchar2),
p_attribute4)
and nvl(pee.attribute5,hr_api.g_varchar2) =
decode(p_attribute5, hr_api.g_varchar2, nvl(pee.attribute5,hr_api.g_varchar2),
p_attribute5)
and nvl(pee.attribute6,hr_api.g_varchar2) =
decode(p_attribute6, hr_api.g_varchar2, nvl(pee.attribute6,hr_api.g_varchar2),
p_attribute6)
and nvl(pee.attribute7,hr_api.g_varchar2) =
decode(p_attribute7, hr_api.g_varchar2, nvl(pee.attribute7,hr_api.g_varchar2),
p_attribute7)
and nvl(pee.attribute8,hr_api.g_varchar2) =
decode(p_attribute8, hr_api.g_varchar2, nvl(pee.attribute8,hr_api.g_varchar2),
p_attribute8)
and nvl(pee.attribute9,hr_api.g_varchar2) =
decode(p_attribute9, hr_api.g_varchar2, nvl(pee.attribute9,hr_api.g_varchar2),
p_attribute9)
and nvl(pee.attribute10,hr_api.g_varchar2) =
decode(p_attribute10, hr_api.g_varchar2, nvl(pee.attribute10,hr_api.g_varchar2),
p_attribute10)
and nvl(pee.attribute11,hr_api.g_varchar2) =
decode(p_attribute11, hr_api.g_varchar2, nvl(pee.attribute11,hr_api.g_varchar2),
p_attribute11)
and nvl(pee.attribute12,hr_api.g_varchar2) =
decode(p_attribute12, hr_api.g_varchar2, nvl(pee.attribute12,hr_api.g_varchar2),
p_attribute12)
and nvl(pee.attribute13,hr_api.g_varchar2) =
decode(p_attribute13, hr_api.g_varchar2, nvl(pee.attribute13,hr_api.g_varchar2),
p_attribute13)
and nvl(pee.attribute14,hr_api.g_varchar2) =
decode(p_attribute14, hr_api.g_varchar2, nvl(pee.attribute14,hr_api.g_varchar2),
p_attribute14)
and nvl(pee.attribute15,hr_api.g_varchar2) =
decode(p_attribute15, hr_api.g_varchar2, nvl(pee.attribute15,hr_api.g_varchar2),
p_attribute15)
and nvl(pee.attribute16,hr_api.g_varchar2) =
decode(p_attribute16, hr_api.g_varchar2, nvl(pee.attribute16,hr_api.g_varchar2),
p_attribute16)
and nvl(pee.attribute17,hr_api.g_varchar2) =
decode(p_attribute17, hr_api.g_varchar2, nvl(pee.attribute17,hr_api.g_varchar2),
p_attribute17)
and nvl(pee.attribute18,hr_api.g_varchar2) =
decode(p_attribute18, hr_api.g_varchar2, nvl(pee.attribute18,hr_api.g_varchar2),
p_attribute18)
and nvl(pee.attribute19,hr_api.g_varchar2) =
decode(p_attribute19, hr_api.g_varchar2, nvl(pee.attribute19,hr_api.g_varchar2),
p_attribute19)
and nvl(pee.attribute20,hr_api.g_varchar2) =
decode(p_attribute20, hr_api.g_varchar2, nvl(pee.attribute20,hr_api.g_varchar2),
p_attribute20)
-- --
and nvl(pee.entry_information_category, hr_api.g_varchar2) =
decode(p_entry_information_category, hr_api.g_varchar2,
nvl(pee.entry_information_category,hr_api.g_varchar2),
p_entry_information_category)
and nvl(pee.entry_information1,hr_api.g_varchar2) =
decode(p_entry_information1, hr_api.g_varchar2, nvl(pee.entry_information1,hr_api.g_varchar2),
p_entry_information1)
and nvl(pee.entry_information2,hr_api.g_varchar2) =
decode(p_entry_information2, hr_api.g_varchar2, nvl(pee.entry_information2,hr_api.g_varchar2),
p_entry_information2)
and nvl(pee.entry_information3,hr_api.g_varchar2) =
decode(p_entry_information3, hr_api.g_varchar2, nvl(pee.entry_information3,hr_api.g_varchar2),
p_entry_information3)
and nvl(pee.entry_information4,hr_api.g_varchar2) =
decode(p_entry_information4, hr_api.g_varchar2, nvl(pee.entry_information4,hr_api.g_varchar2),
p_entry_information4)
and nvl(pee.entry_information5,hr_api.g_varchar2) =
decode(p_entry_information5, hr_api.g_varchar2, nvl(pee.entry_information5,hr_api.g_varchar2),
p_entry_information5)
and nvl(pee.entry_information6,hr_api.g_varchar2) =
decode(p_entry_information6, hr_api.g_varchar2, nvl(pee.entry_information6,hr_api.g_varchar2),
p_entry_information6)
and nvl(pee.entry_information7,hr_api.g_varchar2) =
decode(p_entry_information7, hr_api.g_varchar2, nvl(pee.entry_information7,hr_api.g_varchar2),
p_entry_information7)
and nvl(pee.entry_information8,hr_api.g_varchar2) =
decode(p_entry_information8, hr_api.g_varchar2, nvl(pee.entry_information8,hr_api.g_varchar2),
p_entry_information8)
and nvl(pee.entry_information9,hr_api.g_varchar2) =
decode(p_entry_information9, hr_api.g_varchar2, nvl(pee.entry_information9,hr_api.g_varchar2),
p_entry_information9)
and nvl(pee.entry_information10,hr_api.g_varchar2) =
decode(p_entry_information10, hr_api.g_varchar2, nvl(pee.entry_information10,hr_api.g_varchar2),
p_entry_information10)
and nvl(pee.entry_information11,hr_api.g_varchar2) =
decode(p_entry_information11, hr_api.g_varchar2, nvl(pee.entry_information11,hr_api.g_varchar2),
p_entry_information11)
and nvl(pee.entry_information12,hr_api.g_varchar2) =
decode(p_entry_information12, hr_api.g_varchar2, nvl(pee.entry_information12,hr_api.g_varchar2),
p_entry_information12)
and nvl(pee.entry_information13,hr_api.g_varchar2) =
decode(p_entry_information13, hr_api.g_varchar2, nvl(pee.entry_information13,hr_api.g_varchar2),
p_entry_information13)
and nvl(pee.entry_information14,hr_api.g_varchar2) =
decode(p_entry_information14, hr_api.g_varchar2, nvl(pee.entry_information14,hr_api.g_varchar2),
p_entry_information14)
and nvl(pee.entry_information15,hr_api.g_varchar2) =
decode(p_entry_information15, hr_api.g_varchar2, nvl(pee.entry_information15,hr_api.g_varchar2),
p_entry_information15)
and nvl(pee.entry_information16,hr_api.g_varchar2) =
decode(p_entry_information16, hr_api.g_varchar2, nvl(pee.entry_information16,hr_api.g_varchar2),
p_entry_information16)
and nvl(pee.entry_information17,hr_api.g_varchar2) =
decode(p_entry_information17, hr_api.g_varchar2, nvl(pee.entry_information17,hr_api.g_varchar2),
p_entry_information17)
and nvl(pee.entry_information18,hr_api.g_varchar2) =
decode(p_entry_information18, hr_api.g_varchar2, nvl(pee.entry_information18,hr_api.g_varchar2),
p_entry_information18)
and nvl(pee.entry_information19,hr_api.g_varchar2) =
decode(p_entry_information19, hr_api.g_varchar2, nvl(pee.entry_information19,hr_api.g_varchar2),
p_entry_information19)
and nvl(pee.entry_information20,hr_api.g_varchar2) =
decode(p_entry_information20, hr_api.g_varchar2, nvl(pee.entry_information20,hr_api.g_varchar2),
p_entry_information20)
and nvl(pee.entry_information21,hr_api.g_varchar2) =
decode(p_entry_information21, hr_api.g_varchar2, nvl(pee.entry_information21,hr_api.g_varchar2),
p_entry_information21)
and nvl(pee.entry_information22,hr_api.g_varchar2) =
decode(p_entry_information22, hr_api.g_varchar2, nvl(pee.entry_information22,hr_api.g_varchar2),
p_entry_information22)
and nvl(pee.entry_information23,hr_api.g_varchar2) =
decode(p_entry_information23, hr_api.g_varchar2, nvl(pee.entry_information23,hr_api.g_varchar2),
p_entry_information23)
and nvl(pee.entry_information24,hr_api.g_varchar2) =
decode(p_entry_information24, hr_api.g_varchar2, nvl(pee.entry_information24,hr_api.g_varchar2),
p_entry_information24)
and nvl(pee.entry_information25,hr_api.g_varchar2) =
decode(p_entry_information25, hr_api.g_varchar2, nvl(pee.entry_information25,hr_api.g_varchar2),
p_entry_information25)
and nvl(pee.entry_information26,hr_api.g_varchar2) =
decode(p_entry_information26, hr_api.g_varchar2, nvl(pee.entry_information26,hr_api.g_varchar2),
p_entry_information26)
and nvl(pee.entry_information27,hr_api.g_varchar2) =
decode(p_entry_information27, hr_api.g_varchar2, nvl(pee.entry_information27,hr_api.g_varchar2),
p_entry_information27)
and nvl(pee.entry_information28,hr_api.g_varchar2) =
decode(p_entry_information28, hr_api.g_varchar2, nvl(pee.entry_information28,hr_api.g_varchar2),
p_entry_information28)
and nvl(pee.entry_information29,hr_api.g_varchar2) =
decode(p_entry_information29, hr_api.g_varchar2, nvl(pee.entry_information29,hr_api.g_varchar2),
p_entry_information29)
and nvl(pee.entry_information30,hr_api.g_varchar2) =
decode(p_entry_information30, hr_api.g_varchar2, nvl(pee.entry_information30,hr_api.g_varchar2),
p_entry_information30)
);
open select_entry_value;
fetch select_entry_value into l_input_value_id, l_screen_entry_value;
exit when select_entry_value%NOTFOUND;
close select_entry_value;
select ASG.business_group_id
into g_asg_info.business_group_id
from per_all_assignments_f ASG
where ASG.assignment_id = p_assignment_id
and p_effective_date between
ASG.effective_start_date and ASG.effective_end_date;
select plr.rule_mode
into g_asg_info.adj_ee_source
from pay_legislation_rules plr,
per_business_groups pbg
where pbg.business_group_id = g_asg_info.business_group_id
and pbg.legislation_code = plr.legislation_code
and plr.rule_type = 'ADJUSTMENT_EE_SOURCE';
select HL.lookup_code
from hr_lookups HL
where HL.lookup_type = p_lookup_type
and HL.meaning = p_meaning;
select IV.input_value_id
from pay_element_types_f ET,
pay_input_values_f IV,
ben_benefit_classifications BCL
where IV.input_value_id = p_input_value_id
and IV.name = 'Coverage'
and ET.element_type_id = IV.element_type_id
and BCL.benefit_classification_id = ET.benefit_classification_id
and BCL.contributions_used = 'Y'
and not exists (select null
from ben_benefit_contributions_f BCO
where BCO.element_type_id = ET.element_type_id
and BCO.coverage_type = p_lookup_code
and v_session_date between BCO.effective_start_date
and BCO.effective_end_date);
select input_value_id
from pay_input_values_f
where input_value_id = p_input_value_id and
name = 'Coverage';
select effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select piv.formula_id
from pay_input_values_f piv
where piv.input_value_id = p_input_value_id
and p_session_date between piv.effective_start_date
and piv.effective_end_date;
SELECT h.meaning
INTO l_default
FROM HR_LOOKUPS h
WHERE h.lookup_type = p_lookup_type
and h.lookup_code = p_default_value
;
select h.meaning
into l_default
from hr_lookups h
where h.lookup_type = p_lookup_type
and h.lookup_code = p_default_value;
select /*+ ORDERED
INDEX(pliv PAY_LINK_INPUT_VALUES_F_N1)
INDEX(piv PAY_INPUT_VALUES_F_PK)
INDEX(et PAY_ELEMENT_TYPES_F_PK)
USE_NL(PLIV ET PIV) */
pliv.input_value_id,
pliv.default_value pliv_default_value,
piv.hot_default_flag,
piv.lookup_type,
piv.value_set_id,
piv.uom,
piv.mandatory_flag,
piv.default_value piv_default_value,
et.input_currency_code,
decode(pliv.input_value_id,
p_input_value_id1,p_entry_value1,
p_input_value_id2,p_entry_value2,
p_input_value_id3,p_entry_value3,
p_input_value_id4,p_entry_value4,
p_input_value_id5,p_entry_value5,
p_input_value_id6,p_entry_value6,
p_input_value_id7,p_entry_value7,
p_input_value_id8,p_entry_value8,
p_input_value_id9,p_entry_value9,
p_input_value_id10,p_entry_value10,
p_input_value_id11,p_entry_value11,
p_input_value_id12,p_entry_value12,
p_input_value_id13,p_entry_value13,
p_input_value_id14,p_entry_value14,
p_input_value_id15,p_entry_value15,
null) entry_value,
decode(pliv.input_value_id,
p_input_value_id1,'SET',
p_input_value_id2,'SET',
p_input_value_id3,'SET',
p_input_value_id4,'SET',
p_input_value_id5,'SET',
p_input_value_id6,'SET',
p_input_value_id7,'SET',
p_input_value_id8,'SET',
p_input_value_id9,'SET',
p_input_value_id10,'SET',
p_input_value_id11,'SET',
p_input_value_id12,'SET',
p_input_value_id13,'SET',
p_input_value_id14,'SET',
p_input_value_id15,'SET',
'NOT_SET') entry_specified
from pay_link_input_values_f pliv,
pay_input_values_f piv,
pay_element_types_f et
where pliv.element_link_id = p_element_link_id
and piv.input_value_id = pliv.input_value_id
and et.element_type_id = piv.element_type_id
and p_session_date between pliv.effective_start_date
and pliv.effective_end_date
and p_session_date between piv.effective_start_date
and piv.effective_end_date
and p_session_date between et.effective_start_date
and et.effective_end_date;
select /*+ LEADING(ee) */ ee.element_link_id,
eev.input_value_id,
piv.lookup_type,
piv.value_set_id,
piv.uom,
piv.hot_default_flag,
et.input_currency_code,
piv.mandatory_flag,
eev.screen_entry_value peev_default_value,
decode(eev.input_value_id,
p_input_value_id1,p_entry_value1,
p_input_value_id2,p_entry_value2,
p_input_value_id3,p_entry_value3,
p_input_value_id4,p_entry_value4,
p_input_value_id5,p_entry_value5,
p_input_value_id6,p_entry_value6,
p_input_value_id7,p_entry_value7,
p_input_value_id8,p_entry_value8,
p_input_value_id9,p_entry_value9,
p_input_value_id10,p_entry_value10,
p_input_value_id11,p_entry_value11,
p_input_value_id12,p_entry_value12,
p_input_value_id13,p_entry_value13,
p_input_value_id14,p_entry_value14,
p_input_value_id15,p_entry_value15,
eev.screen_entry_value) entry_value,
decode(eev.input_value_id,
p_input_value_id1,'SET',
p_input_value_id2,'SET',
p_input_value_id3,'SET',
p_input_value_id4,'SET',
p_input_value_id5,'SET',
p_input_value_id6,'SET',
p_input_value_id7,'SET',
p_input_value_id8,'SET',
p_input_value_id9,'SET',
p_input_value_id10,'SET',
p_input_value_id11,'SET',
p_input_value_id12,'SET',
p_input_value_id13,'SET',
p_input_value_id14,'SET',
p_input_value_id15,'SET',
'NOT_SET') entry_specified
from pay_element_entry_values_f eev,
pay_element_entries_f ee,
pay_input_values_f piv,
pay_element_types_f et
where ee.element_entry_id = p_element_entry_id
and eev.element_entry_id = ee.element_entry_id
and piv.input_value_id = eev.input_value_id
and et.element_type_id = piv.element_type_id
and p_session_date between ee.effective_start_date
and ee.effective_end_date
and p_session_date between eev.effective_start_date
and eev.effective_end_date
and p_session_date between piv.effective_start_date
and piv.effective_end_date
and p_session_date between et.effective_start_date
and et.effective_end_date;
select ee.creator_type
from pay_element_entries_f ee
where ee.element_entry_id = l_element_entry_id
and l_session_date between ee.effective_start_date and ee.effective_end_date;
select /*+ ORDERED
INDEX(pliv PAY_LINK_INPUT_VALUES_F_N1)
INDEX(piv PAY_INPUT_VALUES_F_PK)
INDEX(et PAY_ELEMENT_TYPES_F_PK)
USE_NL(PLIV ET PIV) */
pliv.input_value_id,
pliv.default_value pliv_default_value,
piv.hot_default_flag,
piv.lookup_type,
piv.value_set_id,
piv.uom,
piv.mandatory_flag,
piv.default_value piv_default_value,
et.input_currency_code
from pay_link_input_values_f pliv,
pay_input_values_f piv,
pay_element_types_f et
where pliv.element_link_id = p_element_link_id
and piv.input_value_id = pliv.input_value_id
and et.element_type_id = piv.element_type_id
and p_session_date between pliv.effective_start_date
and pliv.effective_end_date
and p_session_date between piv.effective_start_date
and piv.effective_end_date
and p_session_date between et.effective_start_date
and et.effective_end_date;
select ee.element_link_id,
eev.input_value_id,
piv.lookup_type,
piv.value_set_id,
piv.uom,
piv.hot_default_flag,
et.input_currency_code,
piv.mandatory_flag,
eev.screen_entry_value peev_default_value,
eev.screen_entry_value
from pay_element_entry_values_f eev,
pay_element_entries_f ee,
pay_input_values_f piv,
pay_element_types_f et
where ee.element_entry_id = p_element_entry_id
and eev.element_entry_id = ee.element_entry_id
and piv.input_value_id = eev.input_value_id
and et.element_type_id = piv.element_type_id
and p_session_date between ee.effective_start_date
and ee.effective_end_date
and p_session_date between eev.effective_start_date
and eev.effective_end_date
and p_session_date between piv.effective_start_date
and piv.effective_end_date
and p_session_date between et.effective_start_date
and et.effective_end_date;
select ee.creator_type
from pay_element_entries_f ee
where ee.element_entry_id = l_element_entry_id
and l_session_date between ee.effective_start_date and ee.effective_end_date;
select el.element_link_id
into v_element_link_id
from per_assignments_f asg,
pay_element_links_f el
where asg.assignment_id = p_assignment_id
and el.business_group_id + 0 = asg.business_group_id + 0
and el.element_type_id = p_element_type_id
and p_session_date between asg.effective_start_date
and asg.effective_end_date
and p_session_date between el.effective_start_date
and el.effective_end_date
and ((el.payroll_id is not null and
el.payroll_id = asg.payroll_id)
or (el.link_to_all_payrolls_flag = 'Y' and
asg.payroll_id is not null)
or (el.payroll_id is null and
el.link_to_all_payrolls_flag = 'N'))
and (el.job_id is null or
el.job_id = asg.job_id)
and (el.grade_id is null or
el.grade_id = asg.grade_id)
and (el.position_id is null or
el.position_id = asg.position_id)
and (el.organization_id is null or
el.organization_id = asg.organization_id)
and (el.location_id is null or
el.location_id = asg.location_id)
and (el.pay_basis_id is null or
el.pay_basis_id = asg.pay_basis_id)
and (el.employment_category is null or
el.employment_category = asg.employment_category)
and (el.people_group_id is null or exists
(select null
from pay_assignment_link_usages_f alu
where alu.assignment_id = asg.assignment_id
and alu.element_link_id = el.element_link_id
and p_session_date between alu.effective_start_date
and alu.effective_end_date));
select min (ppm1.effective_start_date),
max (ppm2.effective_end_date)
from pay_personal_payment_methods_f PPM1,
pay_personal_payment_methods_f PPM2
where ppm1.personal_payment_method_id = p_personal_payment_method_id
and ppm2.personal_payment_method_id = p_personal_payment_method_id
and ppm1.assignment_id = p_assignment_id;
select 1
from pay_element_types_f elt,
pay_element_links_f eli
where eli.element_link_id = p_element_link_id
and eli.element_type_id = elt.element_type_id
and p_effective_start_date <= eli.effective_end_date
and p_effective_end_date >= eli.effective_start_date
and p_effective_start_date <= elt.effective_end_date
and p_effective_end_date >= elt.effective_start_date
and elt.third_party_pay_only_flag = 'Y';
select 1
from pay_element_types_f ELT,
pay_element_links_f ELI
where eli.element_link_id = p_element_link_id
and eli.element_type_id = elt.element_type_id
and p_effective_start_date <= eli.effective_end_date
and p_effective_end_date >= eli.effective_start_date
and p_effective_start_date <= elt.effective_end_date
and p_effective_end_date >= elt.effective_start_date
and elt.processing_type = 'N';
select asg.assignment_id
into v_number_variable
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_validation_start_date between asg.effective_start_date
and asg.effective_end_date;
select el.element_link_id
into v_number_variable
from pay_element_links_f el
where el.element_link_id = p_element_link_id
and p_validation_start_date between el.effective_start_date
and el.effective_end_date;
select null
from per_business_groups_perf bg
, pay_cost_allocation_keyflex cak
where cak.cost_allocation_keyflex_id = p_cost_allocation_keyflex_id
and bg.business_group_id = p_business_group_id
and to_char(cak.id_flex_num) = bg.cost_allocation_structure;
select cst.cost_allocation_keyflex_id
into v_number_variable
from pay_cost_allocation_keyflex cst
where cst.cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
select com.comment_id
into v_number_variable
from hr_comments com
where com.comment_id = p_comment_id;
select ee.element_entry_id
into v_number_variable
from pay_element_entries_f ee
where p_entry_type in ('R','A')
and ee.element_entry_id = p_target_entry_id
and ee.assignment_id = p_assignment_id
and ee.element_link_id = p_element_link_id
and ee.entry_type = 'E'
and exists
(select null
from pay_element_links_f el,
pay_element_types_f et
where el.element_link_id = p_element_link_id
and et.element_type_id = el.element_type_id
and et.processing_type = 'R')
and p_validation_start_date between ee.effective_start_date
and ee.effective_end_date;
select 1
from pay_element_entries_f ee
,pay_element_links_f el
,pay_element_types_f et
where ee.assignment_id = p_assignment_id
and nvl(ee.original_entry_id, ee.element_entry_id)
= p_original_entry_id
and ee.entry_type = 'E'
and ee.creator_type not in ('R','EE','RR','PR','NR')
and p_effective_date between ee.effective_start_date
and ee.effective_end_date
and el.element_link_id = ee.element_link_id
and p_effective_date between el.effective_start_date
and el.effective_end_date
and et.element_type_id = el.element_type_id
and et.processing_type = 'R'
and p_effective_date between et.effective_start_date
and et.effective_end_date
;
select 1
into v_number_variable
from sys.dual
where exists
(select null
from pay_element_entries_f ee
,pay_element_links_f el
,pay_element_types_f et
where ee.assignment_id = p_assignment_id
/*Bug fix 12364433 */
/*and ee.element_link_id = p_element_link_id*/
and ee.element_entry_id = p_original_entry_id
and ee.entry_type = 'E'
and el.element_link_id = ee.element_link_id
and ee.effective_start_date between el.effective_start_date
and el.effective_end_date
and et.element_type_id = el.element_type_id
and ee.effective_start_date between et.effective_start_date
and et.effective_end_date
and et.processing_type = 'R');
select hl.lookup_code
into v_varchar_variable
from hr_lookups hl
where hl.lookup_type = 'ELE_ENTRY_REASON'
and hl.lookup_code = p_reason
and hl.enabled_flag = 'Y'
and p_effective_date between
nvl(hl.start_date_active,p_effective_date) and nvl(hl.end_date_active,p_effective_date);
select element_type_id
into l_ele_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;
procedure ddf_insert_validate
(
p_entry_information_category varchar2 default null,
p_entry_information1 varchar2 default null,
p_entry_information2 varchar2 default null,
p_entry_information3 varchar2 default null,
p_entry_information4 varchar2 default null,
p_entry_information5 varchar2 default null,
p_entry_information6 varchar2 default null,
p_entry_information7 varchar2 default null,
p_entry_information8 varchar2 default null,
p_entry_information9 varchar2 default null,
p_entry_information10 varchar2 default null,
p_entry_information11 varchar2 default null,
p_entry_information12 varchar2 default null,
p_entry_information13 varchar2 default null,
p_entry_information14 varchar2 default null,
p_entry_information15 varchar2 default null,
p_entry_information16 varchar2 default null,
p_entry_information17 varchar2 default null,
p_entry_information18 varchar2 default null,
p_entry_information19 varchar2 default null,
p_entry_information20 varchar2 default null,
p_entry_information21 varchar2 default null,
p_entry_information22 varchar2 default null,
p_entry_information23 varchar2 default null,
p_entry_information24 varchar2 default null,
p_entry_information25 varchar2 default null,
p_entry_information26 varchar2 default null,
p_entry_information27 varchar2 default null,
p_entry_information28 varchar2 default null,
p_entry_information29 varchar2 default null,
p_entry_information30 varchar2 default null
) is
--
l_proc varchar2(72) := g_package||'ddf_insert_validate';
end ddf_insert_validate;
procedure ddf_update_validate
(
p_entry_information_category varchar2 default null,
p_entry_information1 varchar2 default null,
p_entry_information2 varchar2 default null,
p_entry_information3 varchar2 default null,
p_entry_information4 varchar2 default null,
p_entry_information5 varchar2 default null,
p_entry_information6 varchar2 default null,
p_entry_information7 varchar2 default null,
p_entry_information8 varchar2 default null,
p_entry_information9 varchar2 default null,
p_entry_information10 varchar2 default null,
p_entry_information11 varchar2 default null,
p_entry_information12 varchar2 default null,
p_entry_information13 varchar2 default null,
p_entry_information14 varchar2 default null,
p_entry_information15 varchar2 default null,
p_entry_information16 varchar2 default null,
p_entry_information17 varchar2 default null,
p_entry_information18 varchar2 default null,
p_entry_information19 varchar2 default null,
p_entry_information20 varchar2 default null,
p_entry_information21 varchar2 default null,
p_entry_information22 varchar2 default null,
p_entry_information23 varchar2 default null,
p_entry_information24 varchar2 default null,
p_entry_information25 varchar2 default null,
p_entry_information26 varchar2 default null,
p_entry_information27 varchar2 default null,
p_entry_information28 varchar2 default null,
p_entry_information29 varchar2 default null,
p_entry_information30 varchar2 default null,
p_entry_information_category_o varchar2 default null,
p_entry_information1_o varchar2 default null,
p_entry_information2_o varchar2 default null,
p_entry_information3_o varchar2 default null,
p_entry_information4_o varchar2 default null,
p_entry_information5_o varchar2 default null,
p_entry_information6_o varchar2 default null,
p_entry_information7_o varchar2 default null,
p_entry_information8_o varchar2 default null,
p_entry_information9_o varchar2 default null,
p_entry_information10_o varchar2 default null,
p_entry_information11_o varchar2 default null,
p_entry_information12_o varchar2 default null,
p_entry_information13_o varchar2 default null,
p_entry_information14_o varchar2 default null,
p_entry_information15_o varchar2 default null,
p_entry_information16_o varchar2 default null,
p_entry_information17_o varchar2 default null,
p_entry_information18_o varchar2 default null,
p_entry_information19_o varchar2 default null,
p_entry_information20_o varchar2 default null,
p_entry_information21_o varchar2 default null,
p_entry_information22_o varchar2 default null,
p_entry_information23_o varchar2 default null,
p_entry_information24_o varchar2 default null,
p_entry_information25_o varchar2 default null,
p_entry_information26_o varchar2 default null,
p_entry_information27_o varchar2 default null,
p_entry_information28_o varchar2 default null,
p_entry_information29_o varchar2 default null,
p_entry_information30_o varchar2 default null
) is
--
l_proc varchar2(72) := g_package||'ddf_update_validate';
end ddf_update_validate;
select object_version_number
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;
'INSERT',
p_target_entry_id,
p_creator_type);
hr_entry_api.ddf_insert_validate(
p_entry_information_category,
p_entry_information1,
p_entry_information2,
p_entry_information3,
p_entry_information4,
p_entry_information5,
p_entry_information6,
p_entry_information7,
p_entry_information8,
p_entry_information9,
p_entry_information10,
p_entry_information11,
p_entry_information12,
p_entry_information13,
p_entry_information14,
p_entry_information15,
p_entry_information16,
p_entry_information17,
p_entry_information18,
p_entry_information19,
p_entry_information20,
p_entry_information21,
p_entry_information22,
p_entry_information23,
p_entry_information24,
p_entry_information25,
p_entry_information26,
p_entry_information27,
p_entry_information28,
p_entry_information29,
p_entry_information30);
insert into pay_element_entries_f
(effective_start_date,
effective_end_date,
element_entry_id,
original_entry_id,
assignment_id,
element_link_id,
creator_type,
entry_type,
cost_allocation_keyflex_id,
updating_action_id,
updating_action_type,
comment_id,
creator_id,
reason,
target_entry_id,
subpriority,
date_earned,
personal_payment_method_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date,
--
-- J.Bailie 08-NOV-99 Element Entries DDF
--
entry_information_category,
entry_information1,
entry_information2,
entry_information3,
entry_information4,
entry_information5,
entry_information6,
entry_information7,
entry_information8,
entry_information9,
entry_information10,
entry_information11,
entry_information12,
entry_information13,
entry_information14,
entry_information15,
entry_information16,
entry_information17,
entry_information18,
entry_information19,
entry_information20,
entry_information21,
entry_information22,
entry_information23,
entry_information24,
entry_information25,
entry_information26,
entry_information27,
entry_information28,
entry_information29,
entry_information30,
element_type_id,
all_entry_values_null,
object_version_number,
label_identifier
)
values
(p_effective_start_date,
p_effective_end_date,
v_element_entry_id,
p_original_entry_id,
p_assignment_id,
p_element_link_id,
p_creator_type,
p_entry_type,
p_cost_allocation_keyflex_id,
p_updating_action_id,
p_updating_action_type,
p_comment_id,
p_creator_id,
p_reason,
p_target_entry_id,
p_subpriority,
p_date_earned,
p_personal_payment_method_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
trunc(sysdate),
0,
0,
-- Bug 570156 changed to null so the PAY_ELEMENT_ENTRIES_F_WHO trigger
-- can set it. These parameters are not needed here at all, as the form
-- is not passing them and api should not set them. It's always done via
-- the database trigger.
null,
null,
--
-- J.Bailie 08-NOV-99 Element Entries DDF
--
p_entry_information_category,
p_entry_information1,
p_entry_information2,
p_entry_information3,
p_entry_information4,
p_entry_information5,
p_entry_information6,
p_entry_information7,
p_entry_information8,
p_entry_information9,
p_entry_information10,
p_entry_information11,
p_entry_information12,
p_entry_information13,
p_entry_information14,
p_entry_information15,
p_entry_information16,
p_entry_information17,
p_entry_information18,
p_entry_information19,
p_entry_information20,
p_entry_information21,
p_entry_information22,
p_entry_information23,
p_entry_information24,
p_entry_information25,
p_entry_information26,
p_entry_information27,
p_entry_information28,
p_entry_information29,
p_entry_information30,
l_element_type_id,
l_all_entry_values_null,
l_object_version_number,
p_label_identifier
);
pay_ele_rki.after_insert
-- p_validation_start/end_date are not supported until the proper API implementation
( p_effective_date => l_effective_date
,p_validation_start_date => v_validation_start_date
,p_validation_end_date => v_validation_end_date
,p_effective_start_date => p_effective_start_date
,p_effective_end_date => p_effective_end_date
,p_element_entry_id => p_element_entry_id
,p_original_entry_id => p_original_entry_id
,p_assignment_id => p_assignment_id
,p_element_link_id => p_element_link_id
,p_creator_type => p_creator_type
,p_entry_type => p_entry_type
,p_cost_allocation_keyflex_id => p_cost_allocation_keyflex_id
,p_updating_action_id => p_updating_action_id
,p_updating_action_type => p_updating_action_type
,p_comment_id => p_comment_id
,p_creator_id => p_creator_id
,p_reason => p_reason
,p_target_entry_id => p_target_entry_id
,p_source_id => null
,p_subpriority => p_subpriority
,p_date_earned => p_date_earned
,p_personal_payment_method_id => p_personal_payment_method_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_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_object_version_number => l_object_version_number
,p_balance_adj_cost_flag => null
,p_comments => null
,p_element_type_id => l_element_type_id
,p_all_entry_values_null => l_all_entry_values_null
);
hr_utility.trace(' Hard call PAY_DYT_ELEMENT_ENTRY_VAL_PKG.after_insert');
PAY_DYT_ELEMENT_ENTRY_VAL_PKG.after_insert
(
P_EFFECTIVE_END_DATE => new_vals.effective_end_date
,P_EFFECTIVE_START_DATE => new_vals.effective_start_date
,P_ELEMENT_ENTRY_ID => new_vals.element_entry_id
,P_ELEMENT_ENTRY_VALUE_ID => new_vals.element_entry_value_id
,P_INPUT_VALUE_ID => new_vals.input_value_id
,P_SCREEN_ENTRY_VALUE => new_vals.screen_entry_value
,P_EFFECTIVE_END_DATE_O => new_vals.effective_end_date
,P_EFFECTIVE_START_DATE_O => new_vals.effective_start_date
,P_ELEMENT_ENTRY_ID_O => new_vals.element_entry_id
,P_ELEMENT_ENTRY_VALUE_ID_O => new_vals.element_entry_value_id
,P_INPUT_VALUE_ID_O => new_vals.input_value_id
,P_SCREEN_ENTRY_VALUE_O => new_vals.screen_entry_value
);
procedure insert_element_entry
(
--
-- Common Parameters
--
p_effective_start_date in out nocopy date,
p_effective_end_date in out nocopy date,
--
-- Element Entry Table
--
p_element_entry_id in out nocopy number,
p_original_entry_id number default null,
p_assignment_id number,
p_element_link_id number,
p_creator_type varchar2,
p_entry_type varchar2,
p_cost_allocation_keyflex_id number default null,
p_updating_action_id number default null,
p_updating_action_type varchar2 default null,
p_comment_id number default null,
p_creator_id number default null,
p_reason varchar2 default null,
p_target_entry_id number default null,
p_subpriority number default null,
p_date_earned date default null,
p_personal_payment_method_id number default null,
p_attribute_category varchar2 default null,
p_attribute1 varchar2 default null,
p_attribute2 varchar2 default null,
p_attribute3 varchar2 default null,
p_attribute4 varchar2 default null,
p_attribute5 varchar2 default null,
p_attribute6 varchar2 default null,
p_attribute7 varchar2 default null,
p_attribute8 varchar2 default null,
p_attribute9 varchar2 default null,
p_attribute10 varchar2 default null,
p_attribute11 varchar2 default null,
p_attribute12 varchar2 default null,
p_attribute13 varchar2 default null,
p_attribute14 varchar2 default null,
p_attribute15 varchar2 default null,
p_attribute16 varchar2 default null,
p_attribute17 varchar2 default null,
p_attribute18 varchar2 default null,
p_attribute19 varchar2 default null,
p_attribute20 varchar2 default null,
p_override_user_ent_chk varchar2 default 'N',
p_label_identifier varchar2 default null,
--
-- Element Entry Values Table
--
p_num_entry_values number,
p_input_value_id_tbl hr_entry.number_table,
p_entry_value_tbl hr_entry.varchar2_table
) is
--
-- Local Variables
--
v_num_entry_values number;
end insert_element_entry;
procedure insert_element_entry
(
--
-- Common Parameters
--
p_effective_start_date in out nocopy date,
p_effective_end_date in out nocopy date,
--
-- Element Entry Table
--
p_element_entry_id in out nocopy number,
p_original_entry_id number default null,
p_assignment_id number,
p_element_link_id number,
p_creator_type varchar2,
p_entry_type varchar2,
p_cost_allocation_keyflex_id number default null,
p_updating_action_id number default null,
p_updating_action_type varchar2 default null,
p_comment_id number default null,
p_creator_id number default null,
p_reason varchar2 default null,
p_target_entry_id number default null,
p_subpriority number default null,
p_date_earned date default null,
p_personal_payment_method_id number default null,
p_attribute_category varchar2 default null,
p_attribute1 varchar2 default null,
p_attribute2 varchar2 default null,
p_attribute3 varchar2 default null,
p_attribute4 varchar2 default null,
p_attribute5 varchar2 default null,
p_attribute6 varchar2 default null,
p_attribute7 varchar2 default null,
p_attribute8 varchar2 default null,
p_attribute9 varchar2 default null,
p_attribute10 varchar2 default null,
p_attribute11 varchar2 default null,
p_attribute12 varchar2 default null,
p_attribute13 varchar2 default null,
p_attribute14 varchar2 default null,
p_attribute15 varchar2 default null,
p_attribute16 varchar2 default null,
p_attribute17 varchar2 default null,
p_attribute18 varchar2 default null,
p_attribute19 varchar2 default null,
p_attribute20 varchar2 default null,
--
-- Element Entry Values Table
--
p_input_value_id1 number default null,
p_input_value_id2 number default null,
p_input_value_id3 number default null,
p_input_value_id4 number default null,
p_input_value_id5 number default null,
p_input_value_id6 number default null,
p_input_value_id7 number default null,
p_input_value_id8 number default null,
p_input_value_id9 number default null,
p_input_value_id10 number default null,
p_input_value_id11 number default null,
p_input_value_id12 number default null,
p_input_value_id13 number default null,
p_input_value_id14 number default null,
p_input_value_id15 number default null,
p_entry_value1 varchar2 default null,
p_entry_value2 varchar2 default null,
p_entry_value3 varchar2 default null,
p_entry_value4 varchar2 default null,
p_entry_value5 varchar2 default null,
p_entry_value6 varchar2 default null,
p_entry_value7 varchar2 default null,
p_entry_value8 varchar2 default null,
p_entry_value9 varchar2 default null,
p_entry_value10 varchar2 default null,
p_entry_value11 varchar2 default null,
p_entry_value12 varchar2 default null,
p_entry_value13 varchar2 default null,
p_entry_value14 varchar2 default null,
p_entry_value15 varchar2 default null,
p_override_user_ent_chk varchar2 default 'N',
p_label_identifier varchar2 default null
) is
--
-- Local Variables
--
v_num_entry_values number;
end insert_element_entry;
procedure insert_element_entry
(
--
-- Common Parameters
--
p_effective_start_date in out nocopy date,
p_effective_end_date in out nocopy date,
--
-- Element Entry Table
--
p_element_entry_id in out nocopy number,
p_original_entry_id number default null,
p_assignment_id number,
p_element_link_id number,
p_creator_type varchar2,
p_entry_type varchar2,
p_cost_allocation_keyflex_id number default null,
p_updating_action_id number default null,
p_updating_action_type varchar2 default null,
p_comment_id number default null,
p_creator_id number default null,
p_reason varchar2 default null,
p_target_entry_id number default null,
p_subpriority number default null,
p_date_earned date default null,
p_personal_payment_method_id number default null,
p_attribute_category varchar2 default null,
p_attribute1 varchar2 default null,
p_attribute2 varchar2 default null,
p_attribute3 varchar2 default null,
p_attribute4 varchar2 default null,
p_attribute5 varchar2 default null,
p_attribute6 varchar2 default null,
p_attribute7 varchar2 default null,
p_attribute8 varchar2 default null,
p_attribute9 varchar2 default null,
p_attribute10 varchar2 default null,
p_attribute11 varchar2 default null,
p_attribute12 varchar2 default null,
p_attribute13 varchar2 default null,
p_attribute14 varchar2 default null,
p_attribute15 varchar2 default null,
p_attribute16 varchar2 default null,
p_attribute17 varchar2 default null,
p_attribute18 varchar2 default null,
p_attribute19 varchar2 default null,
p_attribute20 varchar2 default null,
--
-- Element Entry Values Table
--
p_num_entry_values number,
p_input_value_id_tbl hr_entry.number_table,
p_entry_value_tbl hr_entry.varchar2_table,
--
-- J.Bailie Element Entries DDF
--
p_entry_information_category varchar2 ,
p_entry_information1 varchar2 default null,
p_entry_information2 varchar2 default null,
p_entry_information3 varchar2 default null,
p_entry_information4 varchar2 default null,
p_entry_information5 varchar2 default null,
p_entry_information6 varchar2 default null,
p_entry_information7 varchar2 default null,
p_entry_information8 varchar2 default null,
p_entry_information9 varchar2 default null,
p_entry_information10 varchar2 default null,
p_entry_information11 varchar2 default null,
p_entry_information12 varchar2 default null,
p_entry_information13 varchar2 default null,
p_entry_information14 varchar2 default null,
p_entry_information15 varchar2 default null,
p_entry_information16 varchar2 default null,
p_entry_information17 varchar2 default null,
p_entry_information18 varchar2 default null,
p_entry_information19 varchar2 default null,
p_entry_information20 varchar2 default null,
p_entry_information21 varchar2 default null,
p_entry_information22 varchar2 default null,
p_entry_information23 varchar2 default null,
p_entry_information24 varchar2 default null,
p_entry_information25 varchar2 default null,
p_entry_information26 varchar2 default null,
p_entry_information27 varchar2 default null,
p_entry_information28 varchar2 default null,
p_entry_information29 varchar2 default null,
p_entry_information30 varchar2 default null,
p_override_user_ent_chk varchar2 default 'N',
p_label_identifier varchar2 default null
) is
--
-- Local Variables
--
v_num_entry_values number;
end insert_element_entry;
procedure insert_element_entry
(
--
-- Common Parameters
--
p_effective_start_date in out nocopy date,
p_effective_end_date in out nocopy date,
--
-- Element Entry Table
--
p_element_entry_id in out nocopy number,
p_original_entry_id number default null,
p_assignment_id number,
p_element_link_id number,
p_creator_type varchar2,
p_entry_type varchar2,
p_cost_allocation_keyflex_id number default null,
p_updating_action_id number default null,
p_updating_action_type varchar2 default null,
p_comment_id number default null,
p_creator_id number default null,
p_reason varchar2 default null,
p_target_entry_id number default null,
p_subpriority number default null,
p_date_earned date default null,
p_personal_payment_method_id number default null,
p_attribute_category varchar2 default null,
p_attribute1 varchar2 default null,
p_attribute2 varchar2 default null,
p_attribute3 varchar2 default null,
p_attribute4 varchar2 default null,
p_attribute5 varchar2 default null,
p_attribute6 varchar2 default null,
p_attribute7 varchar2 default null,
p_attribute8 varchar2 default null,
p_attribute9 varchar2 default null,
p_attribute10 varchar2 default null,
p_attribute11 varchar2 default null,
p_attribute12 varchar2 default null,
p_attribute13 varchar2 default null,
p_attribute14 varchar2 default null,
p_attribute15 varchar2 default null,
p_attribute16 varchar2 default null,
p_attribute17 varchar2 default null,
p_attribute18 varchar2 default null,
p_attribute19 varchar2 default null,
p_attribute20 varchar2 default null,
--
-- Element Entry Values Table
--
p_input_value_id1 number default null,
p_input_value_id2 number default null,
p_input_value_id3 number default null,
p_input_value_id4 number default null,
p_input_value_id5 number default null,
p_input_value_id6 number default null,
p_input_value_id7 number default null,
p_input_value_id8 number default null,
p_input_value_id9 number default null,
p_input_value_id10 number default null,
p_input_value_id11 number default null,
p_input_value_id12 number default null,
p_input_value_id13 number default null,
p_input_value_id14 number default null,
p_input_value_id15 number default null,
p_entry_value1 varchar2 default null,
p_entry_value2 varchar2 default null,
p_entry_value3 varchar2 default null,
p_entry_value4 varchar2 default null,
p_entry_value5 varchar2 default null,
p_entry_value6 varchar2 default null,
p_entry_value7 varchar2 default null,
p_entry_value8 varchar2 default null,
p_entry_value9 varchar2 default null,
p_entry_value10 varchar2 default null,
p_entry_value11 varchar2 default null,
p_entry_value12 varchar2 default null,
p_entry_value13 varchar2 default null,
p_entry_value14 varchar2 default null,
p_entry_value15 varchar2 default null,
--
-- J.Bailie Element Entries DDF
--
p_entry_information_category varchar2 ,
p_entry_information1 varchar2 default null,
p_entry_information2 varchar2 default null,
p_entry_information3 varchar2 default null,
p_entry_information4 varchar2 default null,
p_entry_information5 varchar2 default null,
p_entry_information6 varchar2 default null,
p_entry_information7 varchar2 default null,
p_entry_information8 varchar2 default null,
p_entry_information9 varchar2 default null,
p_entry_information10 varchar2 default null,
p_entry_information11 varchar2 default null,
p_entry_information12 varchar2 default null,
p_entry_information13 varchar2 default null,
p_entry_information14 varchar2 default null,
p_entry_information15 varchar2 default null,
p_entry_information16 varchar2 default null,
p_entry_information17 varchar2 default null,
p_entry_information18 varchar2 default null,
p_entry_information19 varchar2 default null,
p_entry_information20 varchar2 default null,
p_entry_information21 varchar2 default null,
p_entry_information22 varchar2 default null,
p_entry_information23 varchar2 default null,
p_entry_information24 varchar2 default null,
p_entry_information25 varchar2 default null,
p_entry_information26 varchar2 default null,
p_entry_information27 varchar2 default null,
p_entry_information28 varchar2 default null,
p_entry_information29 varchar2 default null,
p_entry_information30 varchar2 default null,
p_override_user_ent_chk varchar2 default 'N',
p_label_identifier varchar2 default null
) is
--
-- Local Variables
--
v_num_entry_values number;
end insert_element_entry;
procedure check_salary_admin_updates
(
p_element_entry_id number,
p_new_entry hr_entry_api.t_update_entry_rec,
p_effective_date date,
p_dt_update_mode varchar2
) is
--
cursor csr_old_entry_details is
--
-- Get the old values of the entry for comparison with the updated
-- values
--
select *
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;
invalid_dt_update_mode exception;
hr_utility.trace('check_salary_admin_updates');
p_argument => 'p_dt_update_mode',
p_argument_value=> p_dt_update_mode);
l_proc := 'hr_entry_api.check_salary_admin_updates';
p_dt_update_mode not in ( 'CORRECTION',
'UPDATE_CHANGE_INSERT' )
and
(
-- and costing keyflex was updated
nvl (old_entry.cost_allocation_keyflex_id, 0)
<> nvl (p_new_entry.cost_allocation_keyflex_id, 0)
--
-- or reason was updated
or nvl (old_entry.reason,'x') <> nvl (p_new_entry.reason,'x')
--
-- or date_earned was updated
or nvl (old_entry.date_earned, hr_general.end_of_time)
<> nvl (p_new_entry.date_earned, hr_general.end_of_time)
--
-- or third party payee details were updated
or nvl (old_entry.personal_payment_method_id, 0)
<> nvl (p_new_entry.personal_payment_method_id, 0)
--
-- or subpriority was updated
or nvl (old_entry.subpriority, 0) <> nvl (p_new_entry.subpriority, 0)
--
-- or descriptive flex was updated
or (nvl (old_entry.attribute_category,'x')
<> nvl (p_new_entry.attribute_category,'x')
or nvl (old_entry.attribute1,'x') <> nvl (p_new_entry.attribute1, 'x')
or nvl (old_entry.attribute2,'x') <> nvl (p_new_entry.attribute2, 'x')
or nvl (old_entry.attribute3,'x') <> nvl (p_new_entry.attribute3, 'x')
or nvl (old_entry.attribute4,'x') <> nvl (p_new_entry.attribute4, 'x')
or nvl (old_entry.attribute5,'x') <> nvl (p_new_entry.attribute5, 'x')
or nvl (old_entry.attribute6,'x') <> nvl (p_new_entry.attribute6, 'x')
or nvl (old_entry.attribute7,'x') <> nvl (p_new_entry.attribute7, 'x')
or nvl (old_entry.attribute8,'x') <> nvl (p_new_entry.attribute8, 'x')
or nvl (old_entry.attribute9,'x') <> nvl (p_new_entry.attribute9, 'x')
or nvl(old_entry.attribute10,'x') <> nvl(p_new_entry.attribute10, 'x')
or nvl(old_entry.attribute11,'x') <> nvl(p_new_entry.attribute11, 'x')
or nvl(old_entry.attribute12,'x') <> nvl(p_new_entry.attribute12, 'x')
or nvl(old_entry.attribute13,'x') <> nvl(p_new_entry.attribute13, 'x')
or nvl(old_entry.attribute14,'x') <> nvl(p_new_entry.attribute14, 'x')
or nvl(old_entry.attribute15,'x') <> nvl(p_new_entry.attribute15, 'x')
or nvl(old_entry.attribute16,'x') <> nvl(p_new_entry.attribute16, 'x')
or nvl(old_entry.attribute17,'x') <> nvl(p_new_entry.attribute17, 'x')
or nvl(old_entry.attribute18,'x') <> nvl(p_new_entry.attribute18, 'x')
or nvl(old_entry.attribute19,'x') <> nvl(p_new_entry.attribute19, 'x')
or nvl(old_entry.attribute20,'x') <> nvl(p_new_entry.attribute20, 'x'))
)
then
--
raise invalid_dt_update_mode;
when invalid_dt_update_mode then
--
-- The entry attributes for salary admin entries may be updated only
-- in UPDATE_CHANGE_INSERT and CORRECTION datetrack modes.
--
hr_utility.set_message (801,'HR_51182_SAL_DT_MODE');
end check_salary_admin_updates;
p_dt_update_mode in out nocopy varchar2,
--
-- Date on which change is taking place
--
p_session_date date,
--
-- Element Entry Table
--
p_element_entry_id number,
p_cost_allocation_keyflex_id number,
p_updating_action_id number,
p_updating_action_type varchar2,
p_original_entry_id number,
p_creator_type varchar2,
p_comment_id number,
p_creator_id number,
p_reason varchar2,
p_subpriority number,
p_date_earned date,
p_personal_payment_method_id number,
p_attribute_category varchar2,
p_attribute1 varchar2,
p_attribute2 varchar2,
p_attribute3 varchar2,
p_attribute4 varchar2,
p_attribute5 varchar2,
p_attribute6 varchar2,
p_attribute7 varchar2,
p_attribute8 varchar2,
p_attribute9 varchar2,
p_attribute10 varchar2,
p_attribute11 varchar2,
p_attribute12 varchar2,
p_attribute13 varchar2,
p_attribute14 varchar2,
p_attribute15 varchar2,
p_attribute16 varchar2,
p_attribute17 varchar2,
p_attribute18 varchar2,
p_attribute19 varchar2,
p_attribute20 varchar2,
-- --
--
-- J.Bailie Element Entries DDF
--
p_entry_information_category varchar2,
p_entry_information1 varchar2,
p_entry_information2 varchar2,
p_entry_information3 varchar2,
p_entry_information4 varchar2,
p_entry_information5 varchar2,
p_entry_information6 varchar2,
p_entry_information7 varchar2,
p_entry_information8 varchar2,
p_entry_information9 varchar2,
p_entry_information10 varchar2,
p_entry_information11 varchar2,
p_entry_information12 varchar2,
p_entry_information13 varchar2,
p_entry_information14 varchar2,
p_entry_information15 varchar2,
p_entry_information16 varchar2,
p_entry_information17 varchar2,
p_entry_information18 varchar2,
p_entry_information19 varchar2,
p_entry_information20 varchar2,
p_entry_information21 varchar2,
p_entry_information22 varchar2,
p_entry_information23 varchar2,
p_entry_information24 varchar2,
p_entry_information25 varchar2,
p_entry_information26 varchar2,
p_entry_information27 varchar2,
p_entry_information28 varchar2,
p_entry_information29 varchar2,
p_entry_information30 varchar2,
p_entry_rec out nocopy hr_entry_api.t_update_entry_rec
) is
--
-- Local Variables
--
v_entry_rec hr_entry_api.t_update_entry_rec;
select decode(p_cost_allocation_keyflex_id,hr_api.g_number,
ee.cost_allocation_keyflex_id,p_cost_allocation_keyflex_id),
decode(p_updating_action_id,hr_api.g_number,
ee.updating_action_id,p_updating_action_id),
decode(p_updating_action_type,hr_api.g_varchar2,
ee.updating_action_type,p_updating_action_type),
decode(p_original_entry_id,hr_api.g_number,
ee.original_entry_id,p_original_entry_id),
decode(p_creator_type,hr_api.g_varchar2,ee.creator_type,p_creator_type),
decode(p_comment_id,hr_api.g_number,ee.comment_id,p_comment_id),
decode(p_creator_id,hr_api.g_number,ee.creator_id,p_creator_id),
decode(p_reason,hr_api.g_varchar2,ee.reason,p_reason),
decode(p_subpriority,hr_api.g_number,ee.subpriority,p_subpriority),
decode(p_date_earned ,hr_api.g_date,ee.date_earned ,p_date_earned),
decode(p_personal_payment_method_id,hr_api.g_number,ee.personal_payment_method_id ,p_personal_payment_method_id),
decode(p_attribute_category,hr_api.g_varchar2,
ee.attribute_category,p_attribute_category),
decode(p_attribute1,hr_api.g_varchar2,ee.attribute1,p_attribute1),
decode(p_attribute2,hr_api.g_varchar2,ee.attribute2,p_attribute2),
decode(p_attribute3,hr_api.g_varchar2,ee.attribute3,p_attribute3),
decode(p_attribute4,hr_api.g_varchar2,ee.attribute4,p_attribute4),
decode(p_attribute5,hr_api.g_varchar2,ee.attribute5,p_attribute5),
decode(p_attribute6,hr_api.g_varchar2,ee.attribute6,p_attribute6),
decode(p_attribute7,hr_api.g_varchar2,ee.attribute7,p_attribute7),
decode(p_attribute8,hr_api.g_varchar2,ee.attribute8,p_attribute8),
decode(p_attribute9,hr_api.g_varchar2,ee.attribute9,p_attribute9),
decode(p_attribute10,hr_api.g_varchar2,ee.attribute10,p_attribute10),
decode(p_attribute11,hr_api.g_varchar2,ee.attribute11,p_attribute11),
decode(p_attribute12,hr_api.g_varchar2,ee.attribute12,p_attribute12),
decode(p_attribute13,hr_api.g_varchar2,ee.attribute13,p_attribute13),
decode(p_attribute14,hr_api.g_varchar2,ee.attribute14,p_attribute14),
decode(p_attribute15,hr_api.g_varchar2,ee.attribute15,p_attribute15),
decode(p_attribute16,hr_api.g_varchar2,ee.attribute16,p_attribute16),
decode(p_attribute17,hr_api.g_varchar2,ee.attribute17,p_attribute17),
decode(p_attribute18,hr_api.g_varchar2,ee.attribute18,p_attribute18),
decode(p_attribute19,hr_api.g_varchar2,ee.attribute19,p_attribute19),
decode(p_attribute20,hr_api.g_varchar2,ee.attribute20,p_attribute20),
ee.effective_start_date,
ee.effective_end_date,
ee.target_entry_id,
ee.entry_type,
ee.element_link_id,
ee.assignment_id,
et.element_type_id,
et.processing_type,
et.input_currency_code,
et.output_currency_code,
-- --
decode(p_entry_information_category,hr_api.g_varchar2,
ee.entry_information_category,p_entry_information_category),
decode(p_entry_information1,hr_api.g_varchar2,ee.entry_information1,p_entry_information1),
decode(p_entry_information2,hr_api.g_varchar2,ee.entry_information2,p_entry_information2),
decode(p_entry_information3,hr_api.g_varchar2,ee.entry_information3,p_entry_information3),
decode(p_entry_information4,hr_api.g_varchar2,ee.entry_information4,p_entry_information4),
decode(p_entry_information5,hr_api.g_varchar2,ee.entry_information5,p_entry_information5),
decode(p_entry_information6,hr_api.g_varchar2,ee.entry_information6,p_entry_information6),
decode(p_entry_information7,hr_api.g_varchar2,ee.entry_information7,p_entry_information7),
decode(p_entry_information8,hr_api.g_varchar2,ee.entry_information8,p_entry_information8),
decode(p_entry_information9,hr_api.g_varchar2,ee.entry_information9,p_entry_information9),
decode(p_entry_information10,hr_api.g_varchar2,ee.entry_information10,p_entry_information10),
decode(p_entry_information11,hr_api.g_varchar2,ee.entry_information11,p_entry_information11),
decode(p_entry_information12,hr_api.g_varchar2,ee.entry_information12,p_entry_information12),
decode(p_entry_information13,hr_api.g_varchar2,ee.entry_information13,p_entry_information13),
decode(p_entry_information14,hr_api.g_varchar2,ee.entry_information14,p_entry_information14),
decode(p_entry_information15,hr_api.g_varchar2,ee.entry_information15,p_entry_information15),
decode(p_entry_information16,hr_api.g_varchar2,ee.entry_information16,p_entry_information16),
decode(p_entry_information17,hr_api.g_varchar2,ee.entry_information17,p_entry_information17),
decode(p_entry_information18,hr_api.g_varchar2,ee.entry_information18,p_entry_information18),
decode(p_entry_information19,hr_api.g_varchar2,ee.entry_information19,p_entry_information19),
decode(p_entry_information20,hr_api.g_varchar2,ee.entry_information20,p_entry_information20),
decode(p_entry_information21,hr_api.g_varchar2,ee.entry_information21,p_entry_information21),
decode(p_entry_information22,hr_api.g_varchar2,ee.entry_information22,p_entry_information22),
decode(p_entry_information23,hr_api.g_varchar2,ee.entry_information23,p_entry_information23),
decode(p_entry_information24,hr_api.g_varchar2,ee.entry_information24,p_entry_information24),
decode(p_entry_information25,hr_api.g_varchar2,ee.entry_information25,p_entry_information25),
decode(p_entry_information26,hr_api.g_varchar2,ee.entry_information26,p_entry_information26),
decode(p_entry_information27,hr_api.g_varchar2,ee.entry_information27,p_entry_information27),
decode(p_entry_information28,hr_api.g_varchar2,ee.entry_information28,p_entry_information28),
decode(p_entry_information29,hr_api.g_varchar2,ee.entry_information29,p_entry_information29),
decode(p_entry_information30,hr_api.g_varchar2,ee.entry_information30,p_entry_information30)
into v_entry_rec
from pay_element_entries_f ee,
pay_element_links_f el,
pay_element_types_f et
where ee.element_entry_id = p_element_entry_id
and el.element_link_id = ee.element_link_id
and et.element_type_id = el.element_type_id
and p_session_date between ee.effective_start_date
and ee.effective_end_date
and p_session_date between el.effective_start_date
and el.effective_end_date
and p_session_date between et.effective_start_date
and et.effective_end_date;
p_dt_update_mode := 'CORRECTION';
p_dt_update_mode := nvl(p_dt_update_mode, 'UPDATE');
p_dt_update_mode := 'CORRECTION';
elsif p_dt_update_mode = 'UPDATE' and
v_entry_rec.effective_end_date <> hr_general.end_of_time then
--
p_dt_update_mode := 'UPDATE_CHANGE_INSERT';
elsif p_dt_update_mode in ('UPDATE_CHANGE_INSERT','UPDATE_OVERRIDE') and
v_entry_rec.effective_end_date = hr_general.end_of_time then
--
p_dt_update_mode := 'UPDATE';
p_dt_update_mode varchar2,
--
-- Date on which change is taking place
--
p_session_date date,
--
-- Check to see if the entry is being updated
--
p_check_for_update varchar2,
--
-- Element Entry Table
--
p_element_entry_id number,
p_cost_allocation_keyflex_id number,
p_updating_action_id number,
p_updating_action_type varchar2,
p_original_entry_id number,
p_creator_type varchar2,
p_comment_id number,
p_creator_id number,
p_reason varchar2,
p_subpriority number,
p_date_earned date,
p_personal_payment_method_id number,
p_attribute_category varchar2,
p_attribute1 varchar2,
p_attribute2 varchar2,
p_attribute3 varchar2,
p_attribute4 varchar2,
p_attribute5 varchar2,
p_attribute6 varchar2,
p_attribute7 varchar2,
p_attribute8 varchar2,
p_attribute9 varchar2,
p_attribute10 varchar2,
p_attribute11 varchar2,
p_attribute12 varchar2,
p_attribute13 varchar2,
p_attribute14 varchar2,
p_attribute15 varchar2,
p_attribute16 varchar2,
p_attribute17 varchar2,
p_attribute18 varchar2,
p_attribute19 varchar2,
p_attribute20 varchar2,
--
-- Element Entry Values Table
--
p_num_entry_values number,
p_input_value_id_tbl hr_entry.number_table,
p_entry_value_tbl hr_entry.varchar2_table,
--
-- J.Bailie Element Entries DDF
--
p_entry_information_category varchar2,
p_entry_information1 varchar2,
p_entry_information2 varchar2,
p_entry_information3 varchar2,
p_entry_information4 varchar2,
p_entry_information5 varchar2,
p_entry_information6 varchar2,
p_entry_information7 varchar2,
p_entry_information8 varchar2,
p_entry_information9 varchar2,
p_entry_information10 varchar2,
p_entry_information11 varchar2,
p_entry_information12 varchar2,
p_entry_information13 varchar2,
p_entry_information14 varchar2,
p_entry_information15 varchar2,
p_entry_information16 varchar2,
p_entry_information17 varchar2,
p_entry_information18 varchar2,
p_entry_information19 varchar2,
p_entry_information20 varchar2,
p_entry_information21 varchar2,
p_entry_information22 varchar2,
p_entry_information23 varchar2,
p_entry_information24 varchar2,
p_entry_information25 varchar2,
p_entry_information26 varchar2,
p_entry_information27 varchar2,
p_entry_information28 varchar2,
p_entry_information29 varchar2,
p_entry_information30 varchar2
) is
--
-- Find all accepted pay proposals that use the element entry.
cursor csr_accepted_pay_proposals
(
p_element_entry_id number,
p_assignment_id number
) is
select pp.pay_proposal_id
from per_pay_proposals pp
where pp.assignment_id = p_assignment_id
and exists
(select null
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.creator_type = 'SP'
and ee.creator_id = pp.pay_proposal_id
and ee.effective_start_date = pp.change_date);
select object_version_number, effective_start_date, effective_end_date
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;
select
effective_start_date
,effective_end_date
,cost_allocation_keyflex_id
,assignment_id
,updating_action_id
,updating_action_type
,element_link_id
,original_entry_id
,creator_type
,entry_type
,comment_id
,creator_id
,reason
,target_entry_id
,source_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,subpriority
,personal_payment_method_id
,date_earned
,object_version_number
,balance_adj_cost_flag
--
-- J.Bailie 08-NOV-99 Element Entries DDF
--
,entry_information_category
,entry_information1
,entry_information2
,entry_information3
,entry_information4
,entry_information5
,entry_information6
,entry_information7
,entry_information8
,entry_information9
,entry_information10
,entry_information11
,entry_information12
,entry_information13
,entry_information14
,entry_information15
,entry_information16
,entry_information17
,entry_information18
,entry_information19
,entry_information20
,entry_information21
,entry_information22
,entry_information23
,entry_information24
,entry_information25
,entry_information26
,entry_information27
,entry_information28
,entry_information29
,entry_information30
,element_type_id
,all_entry_values_null
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
v_dt_update_mode varchar2(30) := p_dt_update_mode;
v_entry_rec hr_entry_api.t_update_entry_rec;
if (p_check_for_update = 'Y') then
v_entry_being_changed :=
hr_entry_api.element_entry_changed
(p_session_date,
p_element_entry_id,
p_cost_allocation_keyflex_id,
p_updating_action_id,
p_updating_action_type,
p_original_entry_id,
p_creator_type,
p_comment_id,
p_creator_id,
p_reason,
p_subpriority,
p_date_earned,
p_personal_payment_method_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_num_entry_values,
p_input_value_id_tbl,
p_entry_value_tbl,
p_entry_information_category,
p_entry_information1,
p_entry_information2,
p_entry_information3,
p_entry_information4,
p_entry_information5,
p_entry_information6,
p_entry_information7,
p_entry_information8,
p_entry_information9,
p_entry_information10,
p_entry_information11,
p_entry_information12,
p_entry_information13,
p_entry_information14,
p_entry_information15,
p_entry_information16,
p_entry_information17,
p_entry_information18,
p_entry_information19,
p_entry_information20,
p_entry_information21,
p_entry_information22,
p_entry_information23,
p_entry_information24,
p_entry_information25,
p_entry_information26,
p_entry_information27,
p_entry_information28,
p_entry_information29,
p_entry_information30
);
(v_dt_update_mode,
p_session_date,
p_element_entry_id,
p_cost_allocation_keyflex_id,
p_updating_action_id,
p_updating_action_type,
p_original_entry_id,
p_creator_type,
p_comment_id,
p_creator_id,
p_reason,
p_subpriority,
p_date_earned,
p_personal_payment_method_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
p_entry_information_category,
p_entry_information1,
p_entry_information2,
p_entry_information3,
p_entry_information4,
p_entry_information5,
p_entry_information6,
p_entry_information7,
p_entry_information8,
p_entry_information9,
p_entry_information10,
p_entry_information11,
p_entry_information12,
p_entry_information13,
p_entry_information14,
p_entry_information15,
p_entry_information16,
p_entry_information17,
p_entry_information18,
p_entry_information19,
p_entry_information20,
p_entry_information21,
p_entry_information22,
p_entry_information23,
p_entry_information24,
p_entry_information25,
p_entry_information26,
p_entry_information27,
p_entry_information28,
p_entry_information29,
p_entry_information30,
v_entry_rec
);
if v_dt_update_mode = 'UPDATE' or
v_dt_update_mode = 'UPDATE_CHANGE_INSERT' then
--
v_validation_start_date := p_session_date;
elsif v_dt_update_mode = 'UPDATE_OVERRIDE' then
--
begin
select max(ee.effective_end_date)
into v_max_effective_end_date
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id;
elsif v_dt_update_mode = 'CORRECTION' then
--
v_validation_start_date := v_entry_rec.effective_start_date;
v_dt_update_mode,
null,
'UPDATE', -- p_usage
v_entry_rec.target_entry_id,
p_creator_type);
hr_entry_api.check_salary_admin_updates (
--
p_element_entry_id => p_element_entry_id,
p_new_entry => v_entry_rec,
p_effective_date => p_session_date,
p_dt_update_mode => p_dt_update_mode);
hr_entry_api.ddf_update_validate(
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_entry_information_category_o => l_entry_information_category_o
, p_entry_information1_o => l_entry_information1_o
, p_entry_information2_o => l_entry_information2_o
, p_entry_information3_o => l_entry_information3_o
, p_entry_information4_o => l_entry_information4_o
, p_entry_information5_o => l_entry_information5_o
, p_entry_information6_o => l_entry_information6_o
, p_entry_information7_o => l_entry_information7_o
, p_entry_information8_o => l_entry_information8_o
, p_entry_information9_o => l_entry_information9_o
, p_entry_information10_o => l_entry_information10_o
, p_entry_information11_o => l_entry_information11_o
, p_entry_information12_o => l_entry_information12_o
, p_entry_information13_o => l_entry_information13_o
, p_entry_information14_o => l_entry_information14_o
, p_entry_information15_o => l_entry_information15_o
, p_entry_information16_o => l_entry_information16_o
, p_entry_information17_o => l_entry_information17_o
, p_entry_information18_o => l_entry_information18_o
, p_entry_information19_o => l_entry_information19_o
, p_entry_information20_o => l_entry_information20_o
, p_entry_information21_o => l_entry_information21_o
, p_entry_information22_o => l_entry_information22_o
, p_entry_information23_o => l_entry_information23_o
, p_entry_information24_o => l_entry_information24_o
, p_entry_information25_o => l_entry_information25_o
, p_entry_information26_o => l_entry_information26_o
, p_entry_information27_o => l_entry_information27_o
, p_entry_information28_o => l_entry_information28_o
, p_entry_information29_o => l_entry_information29_o
, p_entry_information30_o => l_entry_information30_o);
if v_dt_update_mode in ('UPDATE','UPDATE_CHANGE_INSERT',
'UPDATE_OVERRIDE') then
--
-- Clear out future records (if any).
if v_dt_update_mode = 'UPDATE_OVERRIDE' then
--
delete from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date > v_entry_rec.effective_start_date;
update pay_element_entries_f ee
set ee.effective_end_date = p_session_date - 1,
ee.updating_action_id = decode(ee.updating_action_type, 'U', ee.updating_action_id,
null),
ee.updating_action_type = decode(ee.updating_action_type, 'U', 'U', null)
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date = v_entry_rec.effective_start_date;
insert into pay_element_entries_f
(element_entry_id,
effective_start_date,
effective_end_date,
cost_allocation_keyflex_id,
assignment_id,
updating_action_id,
updating_action_type,
element_link_id,
original_entry_id,
creator_type,
entry_type,
comment_id,
creator_id,
reason,
target_entry_id,
subpriority,
date_earned,
personal_payment_method_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
--
-- J.Bailie 08-NOV-99 Element Entries DDF
--
entry_information_category,
entry_information1,
entry_information2,
entry_information3,
entry_information4,
entry_information5,
entry_information6,
entry_information7,
entry_information8,
entry_information9,
entry_information10,
entry_information11,
entry_information12,
entry_information13,
entry_information14,
entry_information15,
entry_information16,
entry_information17,
entry_information18,
entry_information19,
entry_information20,
entry_information21,
entry_information22,
entry_information23,
entry_information24,
entry_information25,
entry_information26,
entry_information27,
entry_information28,
entry_information29,
entry_information30,
element_type_id,
all_entry_values_null,
object_version_number,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
select
ee.element_entry_id,
v_validation_start_date,
v_validation_end_date,
v_entry_rec.cost_allocation_keyflex_id,
ee.assignment_id,
decode(ee.updating_action_type, 'S', ee.updating_action_id, null),
decode(ee.updating_action_type, 'S', 'S', null),
ee.element_link_id,
v_entry_rec.original_entry_id,
v_entry_rec.creator_type,
ee.entry_type,
v_entry_rec.comment_id,
v_entry_rec.creator_id,
v_entry_rec.reason,
ee.target_entry_id,
v_entry_rec.subpriority,
v_entry_rec.date_earned,
v_entry_rec.personal_payment_method_id,
v_entry_rec.attribute_category,
v_entry_rec.attribute1,
v_entry_rec.attribute2,
v_entry_rec.attribute3,
v_entry_rec.attribute4,
v_entry_rec.attribute5,
v_entry_rec.attribute6,
v_entry_rec.attribute7,
v_entry_rec.attribute8,
v_entry_rec.attribute9,
v_entry_rec.attribute10,
v_entry_rec.attribute11,
v_entry_rec.attribute12,
v_entry_rec.attribute13,
v_entry_rec.attribute14,
v_entry_rec.attribute15,
v_entry_rec.attribute16,
v_entry_rec.attribute17,
v_entry_rec.attribute18,
v_entry_rec.attribute19,
v_entry_rec.attribute20,
--
-- J.Bailie 08-NOV-99 Element Entries DDF
--
v_entry_rec.entry_information_category,
v_entry_rec.entry_information1,
v_entry_rec.entry_information2,
v_entry_rec.entry_information3,
v_entry_rec.entry_information4,
v_entry_rec.entry_information5,
v_entry_rec.entry_information6,
v_entry_rec.entry_information7,
v_entry_rec.entry_information8,
v_entry_rec.entry_information9,
v_entry_rec.entry_information10,
v_entry_rec.entry_information11,
v_entry_rec.entry_information12,
v_entry_rec.entry_information13,
v_entry_rec.entry_information14,
v_entry_rec.entry_information15,
v_entry_rec.entry_information16,
v_entry_rec.entry_information17,
v_entry_rec.entry_information18,
v_entry_rec.entry_information19,
v_entry_rec.entry_information20,
v_entry_rec.entry_information21,
v_entry_rec.entry_information22,
v_entry_rec.entry_information23,
v_entry_rec.entry_information24,
v_entry_rec.entry_information25,
v_entry_rec.entry_information26,
v_entry_rec.entry_information27,
v_entry_rec.entry_information28,
v_entry_rec.entry_information29,
v_entry_rec.entry_information30,
v_entry_rec.element_type_id,
l_all_entry_values_null,
l_object_version_number,
trunc(sysdate),
0,
0,
ee.created_by,
ee.creation_date
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date = v_entry_rec.effective_start_date;
elsif v_dt_update_mode = 'CORRECTION' then
update pay_element_entries_f ee
set ee.cost_allocation_keyflex_id =
v_entry_rec.cost_allocation_keyflex_id,
ee.updating_action_id = v_entry_rec.updating_action_id,
ee.updating_action_type = v_entry_rec.updating_action_type,
ee.original_entry_id = v_entry_rec.original_entry_id,
ee.creator_type = v_entry_rec.creator_type,
ee.comment_id = v_entry_rec.comment_id,
ee.creator_id = v_entry_rec.creator_id,
ee.reason = v_entry_rec.reason,
ee.subpriority = v_entry_rec.subpriority,
ee.date_earned = v_entry_rec.date_earned,
ee.personal_payment_method_id = v_entry_rec.personal_payment_method_id,
ee.all_entry_values_null = l_all_entry_values_null,
ee.attribute_category = v_entry_rec.attribute_category,
ee.attribute1 = v_entry_rec.attribute1,
ee.attribute2 = v_entry_rec.attribute2,
ee.attribute3 = v_entry_rec.attribute3,
ee.attribute4 = v_entry_rec.attribute4,
ee.attribute5 = v_entry_rec.attribute5,
ee.attribute6 = v_entry_rec.attribute6,
ee.attribute7 = v_entry_rec.attribute7,
ee.attribute8 = v_entry_rec.attribute8,
ee.attribute9 = v_entry_rec.attribute9,
ee.attribute10 = v_entry_rec.attribute10,
ee.attribute11 = v_entry_rec.attribute11,
ee.attribute12 = v_entry_rec.attribute12,
ee.attribute13 = v_entry_rec.attribute13,
ee.attribute14 = v_entry_rec.attribute14,
ee.attribute15 = v_entry_rec.attribute15,
ee.attribute16 = v_entry_rec.attribute16,
ee.attribute17 = v_entry_rec.attribute17,
ee.attribute18 = v_entry_rec.attribute18,
ee.attribute19 = v_entry_rec.attribute19,
ee.attribute20 = v_entry_rec.attribute20,
ee.entry_information_category = v_entry_rec.entry_information_category,
ee.entry_information1 = v_entry_rec.entry_information1,
ee.entry_information2 = v_entry_rec.entry_information2,
ee.entry_information3 = v_entry_rec.entry_information3,
ee.entry_information4 = v_entry_rec.entry_information4,
ee.entry_information5 = v_entry_rec.entry_information5,
ee.entry_information6 = v_entry_rec.entry_information6,
ee.entry_information7 = v_entry_rec.entry_information7,
ee.entry_information8 = v_entry_rec.entry_information8,
ee.entry_information9 = v_entry_rec.entry_information9,
ee.entry_information10 = v_entry_rec.entry_information10,
ee.entry_information11 = v_entry_rec.entry_information11,
ee.entry_information12 = v_entry_rec.entry_information12,
ee.entry_information13 = v_entry_rec.entry_information13,
ee.entry_information14 = v_entry_rec.entry_information14,
ee.entry_information15 = v_entry_rec.entry_information15,
ee.entry_information16 = v_entry_rec.entry_information16,
ee.entry_information17 = v_entry_rec.entry_information17,
ee.entry_information18 = v_entry_rec.entry_information18,
ee.entry_information19 = v_entry_rec.entry_information19,
ee.entry_information20 = v_entry_rec.entry_information20,
ee.entry_information21 = v_entry_rec.entry_information21,
ee.entry_information22 = v_entry_rec.entry_information22,
ee.entry_information23 = v_entry_rec.entry_information23,
ee.entry_information24 = v_entry_rec.entry_information24,
ee.entry_information25 = v_entry_rec.entry_information25,
ee.entry_information26 = v_entry_rec.entry_information26,
ee.entry_information27 = v_entry_rec.entry_information27,
ee.entry_information28 = v_entry_rec.entry_information28,
ee.entry_information29 = v_entry_rec.entry_information29,
ee.entry_information30 = v_entry_rec.entry_information30,
ee.last_update_date = trunc(sysdate),
ee.last_updated_by = 0,
ee.last_update_login = 0
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date = v_validation_start_date;
l_old_val_tab.delete;
v_dt_update_mode,
p_num_entry_values,
p_input_value_id_tbl,
p_entry_value_tbl);
delete from per_pay_proposal_components ppc
where ppc.pay_proposal_id in
( select pp.pay_proposal_id from per_pay_proposals pp
where pp.pay_proposal_id = v_pay_proposal_id
and not exists
(select null
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.creator_type = 'SP'
and ee.creator_id = pp.pay_proposal_id
and ee.effective_start_date = pp.change_date));
delete from per_pay_proposals pp
where pp.pay_proposal_id = v_pay_proposal_id
and not exists
(select null
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.creator_type = 'SP'
and ee.creator_id = pp.pay_proposal_id
and ee.effective_start_date = pp.change_date);
pay_ele_rku.after_update
-- p_validation_start/end_date are not supported until the proper API implementation
(
p_effective_date => p_session_date
,p_validation_start_date => v_validation_start_date
,p_validation_end_date => v_validation_end_date
,p_datetrack_mode => v_dt_update_mode --6144913
-- new values set
,p_element_entry_id => p_element_entry_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_original_entry_id => v_entry_rec.original_entry_id
,p_creator_type => v_entry_rec.creator_type
,p_cost_allocation_keyflex_id => v_entry_rec.cost_allocation_keyflex_id
-- Needed for row handler
,p_target_entry_id => null
,p_source_id => null
,p_balance_adj_cost_flag => null
,p_entry_type => null
--
,p_updating_action_id => v_entry_rec.updating_action_id
,p_updating_action_type => v_entry_rec.updating_action_type
,p_comment_id => v_entry_rec.comment_id
,p_creator_id => v_entry_rec.creator_id
,p_reason => v_entry_rec.reason
,p_subpriority => v_entry_rec.subpriority
,p_date_earned => v_entry_rec.date_earned
,p_personal_payment_method_id => v_entry_rec.personal_payment_method_id
,p_attribute_category => v_entry_rec.attribute_category
,p_attribute1 => v_entry_rec.attribute1
,p_attribute2 => v_entry_rec.attribute2
,p_attribute3 => v_entry_rec.attribute3
,p_attribute4 => v_entry_rec.attribute4
,p_attribute5 => v_entry_rec.attribute5
,p_attribute6 => v_entry_rec.attribute6
,p_attribute7 => v_entry_rec.attribute7
,p_attribute8 => v_entry_rec.attribute8
,p_attribute9 => v_entry_rec.attribute9
,p_attribute10 => v_entry_rec.attribute10
,p_attribute11 => v_entry_rec.attribute11
,p_attribute12 => v_entry_rec.attribute12
,p_attribute13 => v_entry_rec.attribute13
,p_attribute14 => v_entry_rec.attribute14
,p_attribute15 => v_entry_rec.attribute15
,p_attribute16 => v_entry_rec.attribute16
,p_attribute17 => v_entry_rec.attribute17
,p_attribute18 => v_entry_rec.attribute18
,p_attribute19 => v_entry_rec.attribute19
,p_attribute20 => v_entry_rec.attribute20
,p_entry_information_category => v_entry_rec.entry_information_category
,p_entry_information1 => v_entry_rec.entry_information1
,p_entry_information2 => v_entry_rec.entry_information2
,p_entry_information3 => v_entry_rec.entry_information3
,p_entry_information4 => v_entry_rec.entry_information4
,p_entry_information5 => v_entry_rec.entry_information5
,p_entry_information6 => v_entry_rec.entry_information6
,p_entry_information7 => v_entry_rec.entry_information7
,p_entry_information8 => v_entry_rec.entry_information8
,p_entry_information9 => v_entry_rec.entry_information9
,p_entry_information10 => v_entry_rec.entry_information10
,p_entry_information11 => v_entry_rec.entry_information11
,p_entry_information12 => v_entry_rec.entry_information12
,p_entry_information13 => v_entry_rec.entry_information13
,p_entry_information14 => v_entry_rec.entry_information14
,p_entry_information15 => v_entry_rec.entry_information15
,p_entry_information16 => v_entry_rec.entry_information16
,p_entry_information17 => v_entry_rec.entry_information17
,p_entry_information18 => v_entry_rec.entry_information18
,p_entry_information19 => v_entry_rec.entry_information19
,p_entry_information20 => v_entry_rec.entry_information20
,p_entry_information21 => v_entry_rec.entry_information21
,p_entry_information22 => v_entry_rec.entry_information22
,p_entry_information23 => v_entry_rec.entry_information23
,p_entry_information24 => v_entry_rec.entry_information24
,p_entry_information25 => v_entry_rec.entry_information25
,p_entry_information26 => v_entry_rec.entry_information26
,p_entry_information27 => v_entry_rec.entry_information27
,p_entry_information28 => v_entry_rec.entry_information28
,p_entry_information29 => v_entry_rec.entry_information29
,p_entry_information30 => v_entry_rec.entry_information30
,p_object_version_number => l_object_version_number
,p_comments => null
,p_all_entry_values_null => l_all_entry_values_null
-- old values set
,p_effective_start_date_o => l_effective_start_date_o
,p_effective_end_date_o => l_effective_end_date_o
,p_cost_allocation_keyflex_id_o => l_cost_allocation_keyflex_id_o
,p_assignment_id_o => l_assignment_id_o
,p_updating_action_id_o => l_updating_action_id_o
,p_updating_action_type_o => l_updating_action_type_o
,p_element_link_id_o => l_element_link_id_o
,p_original_entry_id_o => l_original_entry_id_o
,p_creator_type_o => l_creator_type_o
,p_entry_type_o => l_entry_type_o
,p_comment_id_o => l_comment_id_o
,p_creator_id_o => l_creator_id_o
,p_reason_o => l_reason_o
,p_target_entry_id_o => l_target_entry_id_o
,p_source_id_o => l_source_id_o
,p_attribute_category_o => l_attribute_category_o
,p_attribute1_o => l_attribute1_o
,p_attribute2_o => l_attribute2_o
,p_attribute3_o => l_attribute3_o
,p_attribute4_o => l_attribute4_o
,p_attribute5_o => l_attribute5_o
,p_attribute6_o => l_attribute6_o
,p_attribute7_o => l_attribute7_o
,p_attribute8_o => l_attribute8_o
,p_attribute9_o => l_attribute9_o
,p_attribute10_o => l_attribute10_o
,p_attribute11_o => l_attribute11_o
,p_attribute12_o => l_attribute12_o
,p_attribute13_o => l_attribute13_o
,p_attribute14_o => l_attribute14_o
,p_attribute15_o => l_attribute15_o
,p_attribute16_o => l_attribute16_o
,p_attribute17_o => l_attribute17_o
,p_attribute18_o => l_attribute18_o
,p_attribute19_o => l_attribute19_o
,p_attribute20_o => l_attribute20_o
,p_entry_information_category_o => l_entry_information_category_o
,p_entry_information1_o => l_entry_information1_o
,p_entry_information2_o => l_entry_information2_o
,p_entry_information3_o => l_entry_information3_o
,p_entry_information4_o => l_entry_information4_o
,p_entry_information5_o => l_entry_information5_o
,p_entry_information6_o => l_entry_information6_o
,p_entry_information7_o => l_entry_information7_o
,p_entry_information8_o => l_entry_information8_o
,p_entry_information9_o => l_entry_information9_o
,p_entry_information10_o => l_entry_information10_o
,p_entry_information11_o => l_entry_information11_o
,p_entry_information12_o => l_entry_information12_o
,p_entry_information13_o => l_entry_information13_o
,p_entry_information14_o => l_entry_information14_o
,p_entry_information15_o => l_entry_information15_o
,p_entry_information16_o => l_entry_information16_o
,p_entry_information17_o => l_entry_information17_o
,p_entry_information18_o => l_entry_information18_o
,p_entry_information19_o => l_entry_information19_o
,p_entry_information20_o => l_entry_information20_o
,p_entry_information21_o => l_entry_information21_o
,p_entry_information22_o => l_entry_information22_o
,p_entry_information23_o => l_entry_information23_o
,p_entry_information24_o => l_entry_information24_o
,p_entry_information25_o => l_entry_information25_o
,p_entry_information26_o => l_entry_information26_o
,p_entry_information27_o => l_entry_information27_o
,p_entry_information28_o => l_entry_information28_o
,p_entry_information29_o => l_entry_information29_o
,p_entry_information30_o => l_entry_information30_o
,p_subpriority_o => l_subpriority_o
,p_personal_payment_method_id_o => l_personal_payment_method_id_o
,p_date_earned_o => l_date_earned_o
,p_object_version_number_o => l_object_version_number_o
,p_balance_adj_cost_flag_o => l_balance_adj_cost_flag_o
,p_comments_o => null
,p_element_type_id_o => l_element_type_id_o
,p_all_entry_values_null_o => l_all_entry_values_null_o
);
hr_utility.trace(' Hard call PAY_DYT_ELEMENT_ENTRY_VAL_PKG.after_update');
PAY_DYT_ELEMENT_ENTRY_VAL_PKG.after_update
(
P_EFFECTIVE_END_DATE => new_vals.effective_end_date
,P_EFFECTIVE_START_DATE => new_vals.effective_start_date
,P_ELEMENT_ENTRY_ID => new_vals.element_entry_id
,P_ELEMENT_ENTRY_VALUE_ID => new_vals.element_entry_value_id
,P_INPUT_VALUE_ID => new_vals.input_value_id
,P_SCREEN_ENTRY_VALUE => new_vals.screen_entry_value
,P_EFFECTIVE_END_DATE_O
=> l_old_val_tab(g_counter).effective_end_date
,P_EFFECTIVE_START_DATE_O
=> l_old_val_tab(g_counter).effective_start_date
,P_ELEMENT_ENTRY_ID_O
=> l_old_val_tab(g_counter).element_entry_id
,P_ELEMENT_ENTRY_VALUE_ID_O
=> l_old_val_tab(g_counter).element_entry_value_id
,P_INPUT_VALUE_ID_O
=> l_old_val_tab(g_counter).input_value_id
,P_SCREEN_ENTRY_VALUE_O
=> l_old_val_tab(g_counter).screen_entry_value
,p_datetrack_mode => v_dt_update_mode --6144913
);
procedure update_element_entry
(
--
-- Update Mode
--
p_dt_update_mode varchar2 default null,
--
-- Date on which change is taking place
--
p_session_date date,
--
-- Check to see if the entry is being updated
--
p_check_for_update varchar2 default 'N',
--
-- Element Entry Table
--
p_element_entry_id number,
p_cost_allocation_keyflex_id number default hr_api.g_number,
p_updating_action_id number default hr_api.g_number,
p_updating_action_type varchar2 default hr_api.g_varchar2,
p_original_entry_id number default hr_api.g_number,
p_creator_type varchar2 default hr_api.g_varchar2,
p_comment_id number default hr_api.g_number,
p_creator_id number default hr_api.g_number,
p_reason varchar2 default hr_api.g_varchar2,
p_subpriority number default hr_api.g_number,
p_date_earned date default hr_api.g_date,
p_personal_payment_method_id number default hr_api.g_number,
p_attribute_category varchar2 default hr_api.g_varchar2,
p_attribute1 varchar2 default hr_api.g_varchar2,
p_attribute2 varchar2 default hr_api.g_varchar2,
p_attribute3 varchar2 default hr_api.g_varchar2,
p_attribute4 varchar2 default hr_api.g_varchar2,
p_attribute5 varchar2 default hr_api.g_varchar2,
p_attribute6 varchar2 default hr_api.g_varchar2,
p_attribute7 varchar2 default hr_api.g_varchar2,
p_attribute8 varchar2 default hr_api.g_varchar2,
p_attribute9 varchar2 default hr_api.g_varchar2,
p_attribute10 varchar2 default hr_api.g_varchar2,
p_attribute11 varchar2 default hr_api.g_varchar2,
p_attribute12 varchar2 default hr_api.g_varchar2,
p_attribute13 varchar2 default hr_api.g_varchar2,
p_attribute14 varchar2 default hr_api.g_varchar2,
p_attribute15 varchar2 default hr_api.g_varchar2,
p_attribute16 varchar2 default hr_api.g_varchar2,
p_attribute17 varchar2 default hr_api.g_varchar2,
p_attribute18 varchar2 default hr_api.g_varchar2,
p_attribute19 varchar2 default hr_api.g_varchar2,
p_attribute20 varchar2 default hr_api.g_varchar2,
p_override_user_ent_chk varchar2 default 'N',
--
-- Element Entry Values Table
--
p_num_entry_values number,
p_input_value_id_tbl hr_entry.number_table,
p_entry_value_tbl hr_entry.varchar2_table
) is
--
-- Local Variables
v_num_entry_values number;
SELECT pee.entry_type,
pee.assignment_id
FROM PAY_ELEMENT_ENTRIES_F pee
WHERE pee.element_entry_id = p_element_entry_id
and p_effective_date BETWEEN
pee.effective_start_date and pee.effective_end_date
;
(p_dt_update_mode,
p_session_date,
p_check_for_update,
p_element_entry_id,
p_cost_allocation_keyflex_id,
p_updating_action_id,
p_updating_action_type,
p_original_entry_id,
p_creator_type,
p_comment_id,
p_creator_id,
p_reason,
p_subpriority,
p_date_earned,
p_personal_payment_method_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
v_num_entry_values,
v_input_value_id_tbl,
v_entry_value_tbl,
-- --
--
-- J.Bailie 08-NOV-99 31 nulls passed to ins_element_entry
--
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
);
end update_element_entry;
procedure update_element_entry
(
--
-- Update Mode
--
p_dt_update_mode varchar2 default null,
--
-- Date on which change is taking place
--
p_session_date date,
--
-- Check to see if the entry is being updated
--
p_check_for_update varchar2 default 'N',
--
-- Element Entry Table
--
p_element_entry_id number,
p_cost_allocation_keyflex_id number default hr_api.g_number,
p_updating_action_id number default hr_api.g_number,
p_updating_action_type varchar2 default hr_api.g_varchar2,
p_original_entry_id number default hr_api.g_number,
p_creator_type varchar2 default hr_api.g_varchar2,
p_comment_id number default hr_api.g_number,
p_creator_id number default hr_api.g_number,
p_reason varchar2 default hr_api.g_varchar2,
p_subpriority number default hr_api.g_number,
p_date_earned date default hr_api.g_date,
p_personal_payment_method_id number default hr_api.g_number,
p_attribute_category varchar2 default hr_api.g_varchar2,
p_attribute1 varchar2 default hr_api.g_varchar2,
p_attribute2 varchar2 default hr_api.g_varchar2,
p_attribute3 varchar2 default hr_api.g_varchar2,
p_attribute4 varchar2 default hr_api.g_varchar2,
p_attribute5 varchar2 default hr_api.g_varchar2,
p_attribute6 varchar2 default hr_api.g_varchar2,
p_attribute7 varchar2 default hr_api.g_varchar2,
p_attribute8 varchar2 default hr_api.g_varchar2,
p_attribute9 varchar2 default hr_api.g_varchar2,
p_attribute10 varchar2 default hr_api.g_varchar2,
p_attribute11 varchar2 default hr_api.g_varchar2,
p_attribute12 varchar2 default hr_api.g_varchar2,
p_attribute13 varchar2 default hr_api.g_varchar2,
p_attribute14 varchar2 default hr_api.g_varchar2,
p_attribute15 varchar2 default hr_api.g_varchar2,
p_attribute16 varchar2 default hr_api.g_varchar2,
p_attribute17 varchar2 default hr_api.g_varchar2,
p_attribute18 varchar2 default hr_api.g_varchar2,
p_attribute19 varchar2 default hr_api.g_varchar2,
p_attribute20 varchar2 default hr_api.g_varchar2,
--
-- Element Entry Values Table
--
p_input_value_id1 number default null,
p_input_value_id2 number default null,
p_input_value_id3 number default null,
p_input_value_id4 number default null,
p_input_value_id5 number default null,
p_input_value_id6 number default null,
p_input_value_id7 number default null,
p_input_value_id8 number default null,
p_input_value_id9 number default null,
p_input_value_id10 number default null,
p_input_value_id11 number default null,
p_input_value_id12 number default null,
p_input_value_id13 number default null,
p_input_value_id14 number default null,
p_input_value_id15 number default null,
p_entry_value1 varchar2 default null,
p_entry_value2 varchar2 default null,
p_entry_value3 varchar2 default null,
p_entry_value4 varchar2 default null,
p_entry_value5 varchar2 default null,
p_entry_value6 varchar2 default null,
p_entry_value7 varchar2 default null,
p_entry_value8 varchar2 default null,
p_entry_value9 varchar2 default null,
p_entry_value10 varchar2 default null,
p_entry_value11 varchar2 default null,
p_entry_value12 varchar2 default null,
p_entry_value13 varchar2 default null,
p_entry_value14 varchar2 default null,
p_entry_value15 varchar2 default null,
p_override_user_ent_chk varchar2 default 'N'
) is
--
-- Local Variables
v_num_entry_values number;
SELECT pee.entry_type,
pee.assignment_id
FROM PAY_ELEMENT_ENTRIES_F pee
WHERE pee.element_entry_id = p_element_entry_id
and p_effective_date BETWEEN
pee.effective_start_date and pee.effective_end_date
;
(p_dt_update_mode,
p_session_date,
p_check_for_update,
p_element_entry_id,
p_cost_allocation_keyflex_id,
p_updating_action_id,
p_updating_action_type,
p_original_entry_id,
p_creator_type,
p_comment_id,
p_creator_id,
p_reason,
p_subpriority,
p_date_earned,
p_personal_payment_method_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
v_num_entry_values,
v_input_value_id_tbl,
v_entry_value_tbl,
-- --
--
-- J.Bailie 08-NOV-99 31 nulls passed to ins_element_entry
--
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
);
end update_element_entry;
procedure update_element_entry
(
--
-- Update Mode
--
p_dt_update_mode varchar2 default null,
--
-- Date on which change is taking place
--
p_session_date date,
--
-- Check to see if the entry is being updated
--
p_check_for_update varchar2 default 'N',
--
-- Element Entry Table
--
p_element_entry_id number,
p_cost_allocation_keyflex_id number default hr_api.g_number,
p_updating_action_id number default hr_api.g_number,
p_updating_action_type varchar2 default hr_api.g_varchar2,
p_original_entry_id number default hr_api.g_number,
p_creator_type varchar2 default hr_api.g_varchar2,
p_comment_id number default hr_api.g_number,
p_creator_id number default hr_api.g_number,
p_reason varchar2 default hr_api.g_varchar2,
p_subpriority number default hr_api.g_number,
p_date_earned date default hr_api.g_date,
p_personal_payment_method_id number default hr_api.g_number,
p_attribute_category varchar2 default hr_api.g_varchar2,
p_attribute1 varchar2 default hr_api.g_varchar2,
p_attribute2 varchar2 default hr_api.g_varchar2,
p_attribute3 varchar2 default hr_api.g_varchar2,
p_attribute4 varchar2 default hr_api.g_varchar2,
p_attribute5 varchar2 default hr_api.g_varchar2,
p_attribute6 varchar2 default hr_api.g_varchar2,
p_attribute7 varchar2 default hr_api.g_varchar2,
p_attribute8 varchar2 default hr_api.g_varchar2,
p_attribute9 varchar2 default hr_api.g_varchar2,
p_attribute10 varchar2 default hr_api.g_varchar2,
p_attribute11 varchar2 default hr_api.g_varchar2,
p_attribute12 varchar2 default hr_api.g_varchar2,
p_attribute13 varchar2 default hr_api.g_varchar2,
p_attribute14 varchar2 default hr_api.g_varchar2,
p_attribute15 varchar2 default hr_api.g_varchar2,
p_attribute16 varchar2 default hr_api.g_varchar2,
p_attribute17 varchar2 default hr_api.g_varchar2,
p_attribute18 varchar2 default hr_api.g_varchar2,
p_attribute19 varchar2 default hr_api.g_varchar2,
p_attribute20 varchar2 default hr_api.g_varchar2,
--
-- Element Entry Values Table
--
p_num_entry_values number,
p_input_value_id_tbl hr_entry.number_table,
p_entry_value_tbl hr_entry.varchar2_table,
p_entry_information_category varchar2 ,
p_entry_information1 varchar2 default hr_api.g_varchar2,
p_entry_information2 varchar2 default hr_api.g_varchar2,
p_entry_information3 varchar2 default hr_api.g_varchar2,
p_entry_information4 varchar2 default hr_api.g_varchar2,
p_entry_information5 varchar2 default hr_api.g_varchar2,
p_entry_information6 varchar2 default hr_api.g_varchar2,
p_entry_information7 varchar2 default hr_api.g_varchar2,
p_entry_information8 varchar2 default hr_api.g_varchar2,
p_entry_information9 varchar2 default hr_api.g_varchar2,
p_entry_information10 varchar2 default hr_api.g_varchar2,
p_entry_information11 varchar2 default hr_api.g_varchar2,
p_entry_information12 varchar2 default hr_api.g_varchar2,
p_entry_information13 varchar2 default hr_api.g_varchar2,
p_entry_information14 varchar2 default hr_api.g_varchar2,
p_entry_information15 varchar2 default hr_api.g_varchar2,
p_entry_information16 varchar2 default hr_api.g_varchar2,
p_entry_information17 varchar2 default hr_api.g_varchar2,
p_entry_information18 varchar2 default hr_api.g_varchar2,
p_entry_information19 varchar2 default hr_api.g_varchar2,
p_entry_information20 varchar2 default hr_api.g_varchar2,
p_entry_information21 varchar2 default hr_api.g_varchar2,
p_entry_information22 varchar2 default hr_api.g_varchar2,
p_entry_information23 varchar2 default hr_api.g_varchar2,
p_entry_information24 varchar2 default hr_api.g_varchar2,
p_entry_information25 varchar2 default hr_api.g_varchar2,
p_entry_information26 varchar2 default hr_api.g_varchar2,
p_entry_information27 varchar2 default hr_api.g_varchar2,
p_entry_information28 varchar2 default hr_api.g_varchar2,
p_entry_information29 varchar2 default hr_api.g_varchar2,
p_entry_information30 varchar2 default hr_api.g_varchar2,
p_override_user_ent_chk varchar2 default 'N'
) is
--
-- Local Variables
v_num_entry_values number;
SELECT pee.entry_type,
pee.assignment_id
FROM PAY_ELEMENT_ENTRIES_F pee
WHERE pee.element_entry_id = p_element_entry_id
and p_effective_date BETWEEN
pee.effective_start_date and pee.effective_end_date
;
(p_dt_update_mode,
p_session_date,
p_check_for_update,
p_element_entry_id,
p_cost_allocation_keyflex_id,
p_updating_action_id,
p_updating_action_type,
p_original_entry_id,
p_creator_type,
p_comment_id,
p_creator_id,
p_reason,
p_subpriority,
p_date_earned,
p_personal_payment_method_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
v_num_entry_values,
v_input_value_id_tbl,
v_entry_value_tbl,
--
-- J.Bailie 08-NOV-99 Element Entries DDF
--
p_entry_information_category,
p_entry_information1,
p_entry_information2,
p_entry_information3,
p_entry_information4,
p_entry_information5,
p_entry_information6,
p_entry_information7,
p_entry_information8,
p_entry_information9,
p_entry_information10,
p_entry_information11,
p_entry_information12,
p_entry_information13,
p_entry_information14,
p_entry_information15,
p_entry_information16,
p_entry_information17,
p_entry_information18,
p_entry_information19,
p_entry_information20,
p_entry_information21,
p_entry_information22,
p_entry_information23,
p_entry_information24,
p_entry_information25,
p_entry_information26,
p_entry_information27,
p_entry_information28,
p_entry_information29,
p_entry_information30
);
end update_element_entry;
procedure update_element_entry
(
--
-- Update Mode
--
p_dt_update_mode varchar2 default null,
--
-- Date on which change is taking place
--
p_session_date date,
--
-- Check to see if the entry is being updated
--
p_check_for_update varchar2 default 'N',
--
-- Element Entry Table
--
p_element_entry_id number,
p_cost_allocation_keyflex_id number default hr_api.g_number,
p_updating_action_id number default hr_api.g_number,
p_updating_action_type varchar2 default hr_api.g_varchar2,
p_original_entry_id number default hr_api.g_number,
p_creator_type varchar2 default hr_api.g_varchar2,
p_comment_id number default hr_api.g_number,
p_creator_id number default hr_api.g_number,
p_reason varchar2 default hr_api.g_varchar2,
p_subpriority number default hr_api.g_number,
p_date_earned date default hr_api.g_date,
p_personal_payment_method_id number default hr_api.g_number,
p_attribute_category varchar2 default hr_api.g_varchar2,
p_attribute1 varchar2 default hr_api.g_varchar2,
p_attribute2 varchar2 default hr_api.g_varchar2,
p_attribute3 varchar2 default hr_api.g_varchar2,
p_attribute4 varchar2 default hr_api.g_varchar2,
p_attribute5 varchar2 default hr_api.g_varchar2,
p_attribute6 varchar2 default hr_api.g_varchar2,
p_attribute7 varchar2 default hr_api.g_varchar2,
p_attribute8 varchar2 default hr_api.g_varchar2,
p_attribute9 varchar2 default hr_api.g_varchar2,
p_attribute10 varchar2 default hr_api.g_varchar2,
p_attribute11 varchar2 default hr_api.g_varchar2,
p_attribute12 varchar2 default hr_api.g_varchar2,
p_attribute13 varchar2 default hr_api.g_varchar2,
p_attribute14 varchar2 default hr_api.g_varchar2,
p_attribute15 varchar2 default hr_api.g_varchar2,
p_attribute16 varchar2 default hr_api.g_varchar2,
p_attribute17 varchar2 default hr_api.g_varchar2,
p_attribute18 varchar2 default hr_api.g_varchar2,
p_attribute19 varchar2 default hr_api.g_varchar2,
p_attribute20 varchar2 default hr_api.g_varchar2,
--
-- Element Entry Values Table
--
p_input_value_id1 number default null,
p_input_value_id2 number default null,
p_input_value_id3 number default null,
p_input_value_id4 number default null,
p_input_value_id5 number default null,
p_input_value_id6 number default null,
p_input_value_id7 number default null,
p_input_value_id8 number default null,
p_input_value_id9 number default null,
p_input_value_id10 number default null,
p_input_value_id11 number default null,
p_input_value_id12 number default null,
p_input_value_id13 number default null,
p_input_value_id14 number default null,
p_input_value_id15 number default null,
p_entry_value1 varchar2 default null,
p_entry_value2 varchar2 default null,
p_entry_value3 varchar2 default null,
p_entry_value4 varchar2 default null,
p_entry_value5 varchar2 default null,
p_entry_value6 varchar2 default null,
p_entry_value7 varchar2 default null,
p_entry_value8 varchar2 default null,
p_entry_value9 varchar2 default null,
p_entry_value10 varchar2 default null,
p_entry_value11 varchar2 default null,
p_entry_value12 varchar2 default null,
p_entry_value13 varchar2 default null,
p_entry_value14 varchar2 default null,
p_entry_value15 varchar2 default null,
p_entry_information_category varchar2 ,
p_entry_information1 varchar2 default hr_api.g_varchar2,
p_entry_information2 varchar2 default hr_api.g_varchar2,
p_entry_information3 varchar2 default hr_api.g_varchar2,
p_entry_information4 varchar2 default hr_api.g_varchar2,
p_entry_information5 varchar2 default hr_api.g_varchar2,
p_entry_information6 varchar2 default hr_api.g_varchar2,
p_entry_information7 varchar2 default hr_api.g_varchar2,
p_entry_information8 varchar2 default hr_api.g_varchar2,
p_entry_information9 varchar2 default hr_api.g_varchar2,
p_entry_information10 varchar2 default hr_api.g_varchar2,
p_entry_information11 varchar2 default hr_api.g_varchar2,
p_entry_information12 varchar2 default hr_api.g_varchar2,
p_entry_information13 varchar2 default hr_api.g_varchar2,
p_entry_information14 varchar2 default hr_api.g_varchar2,
p_entry_information15 varchar2 default hr_api.g_varchar2,
p_entry_information16 varchar2 default hr_api.g_varchar2,
p_entry_information17 varchar2 default hr_api.g_varchar2,
p_entry_information18 varchar2 default hr_api.g_varchar2,
p_entry_information19 varchar2 default hr_api.g_varchar2,
p_entry_information20 varchar2 default hr_api.g_varchar2,
p_entry_information21 varchar2 default hr_api.g_varchar2,
p_entry_information22 varchar2 default hr_api.g_varchar2,
p_entry_information23 varchar2 default hr_api.g_varchar2,
p_entry_information24 varchar2 default hr_api.g_varchar2,
p_entry_information25 varchar2 default hr_api.g_varchar2,
p_entry_information26 varchar2 default hr_api.g_varchar2,
p_entry_information27 varchar2 default hr_api.g_varchar2,
p_entry_information28 varchar2 default hr_api.g_varchar2,
p_entry_information29 varchar2 default hr_api.g_varchar2,
p_entry_information30 varchar2 default hr_api.g_varchar2,
p_override_user_ent_chk varchar2 default 'N'
) is
--
-- Local Variables
v_num_entry_values number;
SELECT pee.entry_type,
pee.assignment_id
FROM PAY_ELEMENT_ENTRIES_F pee
WHERE pee.element_entry_id = p_element_entry_id
and p_effective_date BETWEEN
pee.effective_start_date and pee.effective_end_date
;
(p_dt_update_mode,
p_session_date,
p_check_for_update,
p_element_entry_id,
p_cost_allocation_keyflex_id,
p_updating_action_id,
p_updating_action_type,
p_original_entry_id,
p_creator_type,
p_comment_id,
p_creator_id,
p_reason,
p_subpriority,
p_date_earned,
p_personal_payment_method_id,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
p_attribute16,
p_attribute17,
p_attribute18,
p_attribute19,
p_attribute20,
v_num_entry_values,
v_input_value_id_tbl,
v_entry_value_tbl,
--
-- J.Bailie 08-NOV-99 Element Entries DDF
--
p_entry_information_category,
p_entry_information1,
p_entry_information2,
p_entry_information3,
p_entry_information4,
p_entry_information5,
p_entry_information6,
p_entry_information7,
p_entry_information8,
p_entry_information9,
p_entry_information10,
p_entry_information11,
p_entry_information12,
p_entry_information13,
p_entry_information14,
p_entry_information15,
p_entry_information16,
p_entry_information17,
p_entry_information18,
p_entry_information19,
p_entry_information20,
p_entry_information21,
p_entry_information22,
p_entry_information23,
p_entry_information24,
p_entry_information25,
p_entry_information26,
p_entry_information27,
p_entry_information28,
p_entry_information29,
p_entry_information30
);
end update_element_entry;
p_dt_delete_mode in out nocopy varchar2,
p_session_date date,
p_element_entry_id number,
p_assignment_id number,
p_entry_rec out nocopy hr_entry_api.t_delete_entry_rec
) return boolean is
--
-- Local Variables
--
l_processed varchar2(1) := 'N';
v_entry_rec hr_entry_api.t_delete_entry_rec;
select pos.final_process_date
from per_periods_of_service pos,
per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
/* Added one more condition(PERIOD_OF_SERVICE_ID) for bug no: 6739960*/
and pos.PERIOD_OF_SERVICE_ID=asg.PERIOD_OF_SERVICE_ID
and asg.person_id = pos.person_id
and p_session_date between asg.effective_start_date
and asg.effective_end_date;
select ee.original_entry_id,
ee.effective_start_date,
ee.effective_end_date,
ee.assignment_id,
ee.element_link_id,
ee.entry_type,
ee.creator_type,
ee.creator_id,
ee.target_entry_id,
et.element_type_id,
et.processing_type,
nvl(ec.non_payments_flag, 'N'),
ee.personal_payment_method_id
into v_entry_rec
from pay_element_entries_f ee,
pay_element_links_f el,
pay_element_types_f et,
pay_element_classifications ec
where ee.element_entry_id = p_element_entry_id
and el.element_link_id = ee.element_link_id
and et.element_type_id = el.element_type_id
and ec.classification_id = et.classification_id
and p_session_date between ee.effective_start_date
and ee.effective_end_date
and p_session_date between el.effective_start_date
and el.effective_end_date
and p_session_date between et.effective_start_date
and et.effective_end_date;
'hr_entry_api.delete_element_entry');
'hr_entry_api.delete_element_entry');
select 'Y'
into l_processed
from pay_run_results
where source_id = p_element_entry_id
and source_type in ('E', 'I')
and status <> 'U';
SELECT 'Y'
INTO l_processed
FROM DUAL
WHERE EXISTS
(SELECT null
FROM PAY_RUN_RESULTS
WHERE source_id = decode(v_entry_rec.entry_type,
'A', decode (adjust_ee_source,
'T', v_entry_rec.target_entry_id,
p_element_entry_id),
'R', decode (adjust_ee_source,
'T', v_entry_rec.target_entry_id,
p_element_entry_id),
p_element_entry_id)
and entry_type = v_entry_rec.entry_type
and source_type in ('E', 'I')
and status <> 'U'
)
;
select nvl(count(*),0) into l_assign_exist
from per_all_assignments_f paaf,per_assignment_status_types past
where paaf.ASSIGNMENT_ID=p_assignment_id
and p_session_date+1 between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
and paaf.ASSIGNMENT_STATUS_TYPE_ID=past.ASSIGNMENT_STATUS_TYPE_ID
and past.per_system_status='ACTIVE_ASSIGN';
select nvl(count(*),0) into l_before_ovm
from per_all_assignments_f
where ASSIGNMENT_ID=p_assignment_id and
p_session_date between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
select nvl(count(*),0) into l_after_ovm
from per_all_assignments_f
where ASSIGNMENT_ID=p_assignment_id and
p_session_date+1 between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE;
if p_dt_delete_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
--
-- Bugfix 2085631
-- return false as we do not wish to remove these entries
--
return (false);
elsif p_dt_delete_mode = 'DELETE'
and p_session_date = v_entry_rec.effective_end_date then
--
-- Bugfix 4015749
-- return false, the non-recurring entry need not be zapped if it
-- currently ends on the same day that the link being deleted will
-- end, i.e.:
--
-- Before:
-- EL |----------------------------->
-- PAY |-----|-----|-----|-----|----->
-- EE |-----|
-- ^
-- p_session_date
-- After:
-- EL |-----------|
-- PAY |-----|-----|-----|-----|----->
-- EE |-----|
--
return (false);
elsif p_dt_delete_mode = 'DELETE'
and final_process_date(p_assignment_id, p_session_date) = p_session_date then
--
-- We do not wish to Zap this entry, it should be end-dated on
-- the final process date so proceed with the Delete...
return (true);
ELSIF p_dt_delete_mode <> 'ZAP' THEN -- bug 8225278
--
p_dt_delete_mode := 'DELETE';
elsif p_dt_delete_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') and
v_entry_rec.effective_end_date = hr_general.end_of_time then
--
return (false);
procedure delete_element_entry
(
--
-- Delete Mode
--
p_dt_delete_mode varchar2,
--
-- Date on which change is taking place
--
p_session_date date,
--
-- Element Entry Table
--
p_element_entry_id number
) is
--
-- Find all accepted pay proposals that use the element entry.
--
cursor csr_accepted_pay_proposals
(
p_element_entry_id number,
p_assignment_id number
) is
select pp.pay_proposal_id
from per_pay_proposals pp
where pp.assignment_id = p_assignment_id
and exists
(select null
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.creator_type = 'SP'
and ee.creator_id = pp.pay_proposal_id
and ee.effective_start_date = pp.change_date);
select object_version_number, effective_start_date, effective_end_date
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;
select
effective_start_date
,effective_end_date
,cost_allocation_keyflex_id
,assignment_id
,updating_action_id
,updating_action_type
,element_link_id
,original_entry_id
,creator_type
,entry_type
,comment_id
,creator_id
,reason
,target_entry_id
,source_id
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,subpriority
,personal_payment_method_id
,date_earned
,object_version_number
,balance_adj_cost_flag
,element_type_id
,all_entry_values_null
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and p_session_date between effective_start_date
and effective_end_date;
v_dt_delete_mode varchar2(30) := p_dt_delete_mode;
v_entry_rec hr_entry_api.t_delete_entry_rec;
select 'Y'
from pay_run_results prr,
pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id
/* Added the following condition for bug: 7344563 */
and pee.element_entry_id = prr.element_entry_id
and p_validation_start_date between pee.effective_start_date
and pee.effective_end_date
and prr.source_id = decode(pee.entry_type,
'A', decode (adjust_ee_source,
'T', pee.target_entry_id,
pee.element_entry_id),
'R', decode (adjust_ee_source,
'T', pee.target_entry_id,
pee.element_entry_id),
pee.element_entry_id)
and prr.entry_type = pee.entry_type
and prr.source_type in ('E', 'I')
and prr.status <> 'U';
select 'Y'
from pay_run_results RESULT,
pay_assignment_actions ASGT_ACTION,
pay_payroll_actions PAY_ACTION,
per_time_periods PERIOD
where result.source_id = nvl (p_original_entry_id,
p_element_entry_id)
and result.source_type in ('E', 'I')
and result.status <> 'U'
and result.assignment_action_id=asgt_action.assignment_action_id
and asgt_action.payroll_action_id=pay_action.payroll_action_id
and pay_action.time_period_id = period.time_period_id
and p_validation_start_date <= period.start_date;
hr_utility.set_location('hr_entry_api.delete_element_entry', 5);
select min(ASG.effective_start_date)
into l_session_date_cur
from per_all_assignments_f ASG
where ASG.assignment_id = l_assignment_id_o;
if v_dt_delete_mode='ZAP' and l_session_date_cur > p_session_date then
set_formula_contexts(l_assignment_id_o, l_session_date_cur);
(v_dt_delete_mode,
p_session_date,
p_element_entry_id,
l_assignment_id_o,
v_entry_rec) then
--
-- Set the validation start and end dates according to the delete mode
-- selected.
--
if v_dt_delete_mode = 'DELETE_NEXT_CHANGE' then
--
if g_debug then
hr_utility.set_location('hr_entry_api.delete_element_entry', 15);
select min(ee.effective_end_date)
into v_next_effective_end_date
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date > v_entry_rec.effective_end_date;
elsif v_dt_delete_mode = 'FUTURE_CHANGE' then
--
v_validation_start_date := v_entry_rec.effective_end_date + 1;
elsif v_dt_delete_mode = 'DELETE' then
--
v_validation_start_date := p_session_date + 1;
elsif v_dt_delete_mode = 'ZAP' then
--
-- It may be illegal for users to purge entries, depending on
-- the setting of a profile and what sort of entry is specified
-- for purge. We check this here because the v_dt_delete_mode
-- can be changed by the previous call.
--
if g_debug then
hr_utility.set_location('hr_entry_api.delete_element_entry', 18);
select min(pee.effective_start_date),
max(pee.effective_end_date)
into v_validation_start_date,
v_validation_end_date
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id;
hr_utility.set_location('hr_entry_api.delete_element_entry', 20);
if v_dt_delete_mode='ZAP' and l_session_date_cur > p_session_date then
hr_entry.chk_element_entry_main
(p_element_entry_id,
v_entry_rec.original_entry_id,
l_session_date_cur,
v_entry_rec.element_link_id,
v_entry_rec.assignment_id,
v_entry_rec.entry_type,
v_entry_rec.effective_start_date,
v_entry_rec.effective_end_date,
v_validation_start_date,
v_validation_end_date,
null,
v_dt_delete_mode,
'DELETE', -- p_usage
v_entry_rec.target_entry_id,
v_entry_rec.creator_type);
v_dt_delete_mode,
'DELETE', -- p_usage
v_entry_rec.target_entry_id,
v_entry_rec.creator_type);
hr_utility.set_location('hr_entry_api.delete_element_entry', 25);
if v_dt_delete_mode = 'DELETE' or p_dt_delete_mode = 'ZAP' then
--
if g_debug then
hr_utility.set_location('hr_entry_api.delete_element_entry', 30);
delete from pay_quickpay_exclusions pqe
where pqe.element_entry_id = p_element_entry_id
and exists
(select null
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = pqe.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned between v_validation_start_date
and v_validation_end_date);
delete from pay_quickpay_inclusions pqi
where pqi.element_entry_id = p_element_entry_id
and exists
(select null
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = pqi.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.date_earned between v_validation_start_date
and v_validation_end_date);
if v_dt_delete_mode = 'DELETE_NEXT_CHANGE' then
--
if g_debug then
hr_utility.set_location('hr_entry_api.delete_element_entry', 40);
delete from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date = v_validation_start_date;
update pay_element_entries_f ee
set ee.effective_end_date = decode(v_validation_end_date,
hr_general.end_of_time,
v_entry_rec.effective_end_date,
v_validation_end_date),
ee.updating_action_id = decode(ee.updating_action_type, 'U', ee.updating_action_id,
null),
ee.updating_action_type = decode(ee.updating_action_type, 'U', 'U', null)
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date = v_entry_rec.effective_start_date;
elsif v_dt_delete_mode = 'FUTURE_CHANGE' then
--
if g_debug then
hr_utility.set_location('hr_entry_api.delete_element_entry', 45);
delete from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date > v_entry_rec.effective_start_date;
update pay_element_entries_f ee
set ee.effective_end_date = v_entry_rec.effective_end_date,
ee.updating_action_id = decode(ee.updating_action_type, 'U', ee.updating_action_id,
null),
ee.updating_action_type = decode(ee.updating_action_type, 'U', 'U', null)
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date = v_entry_rec.effective_start_date;
elsif v_dt_delete_mode = 'DELETE' then
--
if g_debug then
hr_utility.set_location('hr_entry_api.delete_element_entry', 50);
delete from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date > p_session_date;
update pay_element_entries_f ee
set ee.effective_end_date = p_session_date,
ee.updating_action_id = decode(ee.updating_action_type, 'U', ee.updating_action_id,
null),
ee.updating_action_type = decode(ee.updating_action_type, 'U', 'U', null)
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date = v_entry_rec.effective_start_date;
elsif v_dt_delete_mode = 'ZAP' then
--
if g_debug then
hr_utility.set_location('hr_entry_api.delete_element_entry', 55);
delete from pay_grossup_bal_exclusions excl
where excl.source_id = p_element_entry_id
and excl.source_type = 'EE';
delete from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id;
hr_utility.set_location('hr_entry_api.delete_element_entry', 60);
v_dt_delete_mode,
p_session_date,
v_validation_start_date,
v_validation_end_date);
hr_utility.set_location('hr_entry_api.delete_element_entry', 65);
delete from per_pay_proposal_components ppc
where ppc.pay_proposal_id in
( select pp.pay_proposal_id from per_pay_proposals pp
where pp.pay_proposal_id = v_pay_proposal_id
and not exists
(select null
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.creator_type = 'SP'
and ee.creator_id = pp.pay_proposal_id
and ee.effective_start_date = pp.change_date));
delete from per_pay_proposals pp
where pp.pay_proposal_id = v_pay_proposal_id
and not exists
(select null
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.creator_type = 'SP'
and ee.creator_id = pp.pay_proposal_id
and ee.effective_start_date = pp.change_date);
v_dt_delete_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE','DELETE') then
--
if g_debug then
hr_utility.set_location('hr_entry_api.delete_element_entry', 67);
pay_ele_rkd.after_delete
( p_effective_date => p_session_date
,p_validation_start_date => v_validation_start_date
,p_validation_end_date => v_validation_end_date
,p_datetrack_mode => v_dt_delete_mode
,p_element_entry_id => p_element_entry_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
--,p_object_version_number => l_object_version_number
-- old values set
,p_effective_start_date_o => l_effective_start_date_o
,p_effective_end_date_o => l_effective_end_date_o
,p_cost_allocation_keyflex_id_o => l_cost_allocation_keyflex_id_o
,p_assignment_id_o => l_assignment_id_o
,p_updating_action_id_o => l_updating_action_id_o
,p_updating_action_type_o => l_updating_action_type_o
,p_element_link_id_o => l_element_link_id_o
,p_original_entry_id_o => l_original_entry_id_o
,p_creator_type_o => l_creator_type_o
,p_entry_type_o => l_entry_type_o
,p_comment_id_o => l_comment_id_o
,p_creator_id_o => l_creator_id_o
,p_reason_o => l_reason_o
,p_target_entry_id_o => l_target_entry_id_o
,p_source_id_o => l_source_id_o
,p_attribute_category_o => l_attribute_category_o
,p_attribute1_o => l_attribute1_o
,p_attribute2_o => l_attribute2_o
,p_attribute3_o => l_attribute3_o
,p_attribute4_o => l_attribute4_o
,p_attribute5_o => l_attribute5_o
,p_attribute6_o => l_attribute6_o
,p_attribute7_o => l_attribute7_o
,p_attribute8_o => l_attribute8_o
,p_attribute9_o => l_attribute9_o
,p_attribute10_o => l_attribute10_o
,p_attribute11_o => l_attribute11_o
,p_attribute12_o => l_attribute12_o
,p_attribute13_o => l_attribute13_o
,p_attribute14_o => l_attribute14_o
,p_attribute15_o => l_attribute15_o
,p_attribute16_o => l_attribute16_o
,p_attribute17_o => l_attribute17_o
,p_attribute18_o => l_attribute18_o
,p_attribute19_o => l_attribute19_o
,p_attribute20_o => l_attribute20_o
-- --
,p_entry_information_category_o => l_entry_information_category_o
,p_entry_information1_o => l_entry_information1_o
,p_entry_information2_o => l_entry_information2_o
,p_entry_information3_o => l_entry_information3_o
,p_entry_information4_o => l_entry_information4_o
,p_entry_information5_o => l_entry_information5_o
,p_entry_information6_o => l_entry_information6_o
,p_entry_information7_o => l_entry_information7_o
,p_entry_information8_o => l_entry_information8_o
,p_entry_information9_o => l_entry_information9_o
,p_entry_information10_o => l_entry_information10_o
,p_entry_information11_o => l_entry_information11_o
,p_entry_information12_o => l_entry_information12_o
,p_entry_information13_o => l_entry_information13_o
,p_entry_information14_o => l_entry_information14_o
,p_entry_information15_o => l_entry_information15_o
,p_entry_information16_o => l_entry_information16_o
,p_entry_information17_o => l_entry_information17_o
,p_entry_information18_o => l_entry_information18_o
,p_entry_information19_o => l_entry_information19_o
,p_entry_information20_o => l_entry_information20_o
,p_entry_information21_o => l_entry_information21_o
,p_entry_information22_o => l_entry_information22_o
,p_entry_information23_o => l_entry_information23_o
,p_entry_information24_o => l_entry_information24_o
,p_entry_information25_o => l_entry_information25_o
,p_entry_information26_o => l_entry_information26_o
,p_entry_information27_o => l_entry_information27_o
,p_entry_information28_o => l_entry_information28_o
,p_entry_information29_o => l_entry_information29_o
,p_entry_information30_o => l_entry_information30_o
,p_subpriority_o => l_subpriority_o
,p_personal_payment_method_id_o => l_personal_payment_method_id_o
,p_date_earned_o => l_date_earned_o
,p_object_version_number_o => l_object_version_number_o
,p_balance_adj_cost_flag_o => l_balance_adj_cost_flag_o
,p_comments_o => null
,p_element_type_id_o => l_element_type_id_o
,p_all_entry_values_null_o => l_all_entry_values_null_o
);
hr_utility.set_location('hr_entry_api.delete_element_entry', 70);
hr_utility.trace(' NO Hard call PAY_DYT_ELEMENT_ENTRY_VAL_PKG.after_delete');
hr_utility.set_location('Leaving: delete_element_entry', 900);
end delete_element_entry;