[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select EL.element_link_id,
EL.effective_start_date,
EL.link_to_all_payrolls_flag,
EL.payroll_id,
EL.job_id,
EL.grade_id,
EL.position_id,
EL.organization_id,
EL.location_id,
EL.pay_basis_id,
EL.employment_category,
EL.people_group_id
from pay_element_links_f EL
where EL.element_link_id = p_element_link_id
order by EL.effective_start_date;
select ASG.effective_start_date,
ASG.effective_end_date
from per_all_assignments_f ASG
where ASG.assignment_id = p_assignment_id
and ASG.assignment_type = 'E'
and ASG.effective_start_date <= p_range_end_date
and ASG.effective_end_date >= p_range_start_date
and ((p_payroll_id is not null and
p_payroll_id = ASG.payroll_id)
or (p_link_to_all_payrolls_flag = 'Y' and
ASG.payroll_id is not null)
or (p_link_to_all_payrolls_flag = 'N' and
p_payroll_id is null))
and (p_job_id is null or
p_job_id = ASG.job_id)
and (p_grade_id is null or
p_grade_id = ASG.grade_id)
and (p_position_id is null or
p_position_id = ASG.position_id)
and (p_organization_id is null or
p_organization_id = ASG.organization_id)
and (p_location_id is null or
p_location_id = ASG.location_id)
and (p_pay_basis_id is null or
p_pay_basis_id = ASG.pay_basis_id)
and (p_employment_category is null or
p_employment_category = ASG.employment_category)
and (p_people_group_id is null or
exists
(select null
from pay_assignment_link_usages_f ALU
where ALU.assignment_id = p_assignment_id
and ALU.element_link_id = p_element_link_id
and ALU.effective_start_date <= ASG.effective_end_date
and ALU.effective_end_date >= ASG.effective_start_date))
order by ASG.effective_start_date desc;
select asg.effective_start_date,
asg.effective_end_date,
asg.people_group_id,
ppg.id_flex_num
from per_all_assignments_f asg,
pay_people_groups ppg
where asg.assignment_id = p_assignment_id
and asg.people_group_id is not null
and asg.assignment_type not in ('A' ,'O') -- non-applicant assignments only
and ppg.people_group_id = asg.people_group_id
order by asg.effective_start_date;
and p_dt_mode in ('UPDATE',
'UPDATE_CHANGE_INSERT',
'CORRECTION')
)
-- Always rebuild for the following DT modes:
or p_dt_mode in ('INSERT',
'UPDATE_OVERRIDE',
'DELETE',
'FUTURE_CHANGE',
'DELETE_NEXT_CHANGE',
'ZAP')
then --{ dt operation
--
open csr_assignment(p_assignment_id);
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.assignment_id = p_assignment_id
and pp.pay_proposal_id = p_pay_proposal_id
and not exists
(select null
from pay_element_entries_f ee
where ee.assignment_id = pp.assignment_id
and ee.creator_type = 'SP'
and ee.creator_id = pp.pay_proposal_id));
delete from per_pay_proposals pp
where pp.assignment_id = p_assignment_id
and pp.pay_proposal_id = p_pay_proposal_id
and not exists
(select null
from pay_element_entries_f ee
where ee.assignment_id = pp.assignment_id
and ee.creator_type = 'SP'
and ee.creator_id = pp.pay_proposal_id);
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 p_validation_start_date
and p_validation_end_date);
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 p_validation_start_date
and p_validation_end_date);
select pa.effective_date
from pay_assignment_actions aa,
pay_payroll_actions pa
where aa.assignment_id = p_assignment_id
and pa.action_type not in ('X', 'BEE')
and pa.payroll_action_id = aa.payroll_action_id
and ((pa.effective_date >= p_validation_start_date)
or
(pa.date_earned >= p_validation_start_date));
cursor csr_not_insert(l_validation_start_date date) is
select pa.effective_date
from pay_assignment_actions aa,
pay_payroll_actions pa
where aa.assignment_id = p_assignment_id
and pa.payroll_action_id = aa.payroll_action_id
and pa.action_type not in ('X', 'BEE')
and ((pa.effective_date between l_validation_start_date
and p_validation_end_date)
or
(pa.date_earned between l_validation_start_date
and p_validation_end_date))
and not (exists
(select null
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and pa.effective_date
between asg.effective_start_date
and asg.effective_end_date
and asg.payroll_id + 0 = p_payroll_id)
and exists
(select null
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and nvl(pa.date_earned,pa.effective_date)
between asg.effective_start_date
and asg.effective_end_date
and asg.payroll_id + 0 = p_payroll_id));
select 'Y'
from sys.dual
where not exists
(select null
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and p_date
between asg.effective_start_date
and asg.effective_end_date
and asg.payroll_id + 0 = p_payroll_id);
select asg.effective_start_date
from per_all_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;
(p_dt_mode = 'DELETE' or
p_dt_mode = 'ZAP') then
--
--
begin
select 'Y'
into v_check_failed
from sys.dual
where exists
(select null
from pay_payrolls_f pay
where pay.payroll_id = p_payroll_id
and p_validation_end_date >
(select max(pay2.effective_end_date)
from pay_payrolls_f pay2
where pay2.payroll_id = pay.payroll_id));
if p_dt_mode = 'DELETE' or
p_dt_mode = 'ZAP' then
--
--
-- begin
-- select 'Y'
-- into v_check_failed
-- from sys.dual
-- where exists
-- (select null
-- from pay_assignment_actions aa,
-- pay_payroll_actions pa
-- where aa.assignment_id = p_assignment_id
-- and pa.action_type not in ('X', 'BEE')
-- and pa.payroll_action_id = aa.payroll_action_id
-- and ((pa.effective_date >= p_validation_start_date)
-- or
-- (pa.date_earned >= p_validation_start_date)));
elsif not p_dt_mode = 'INSERT' then
l_validation_start_date := p_validation_start_date;
for l_rec in csr_not_insert(l_validation_start_date) loop
--
if (l_rec.effective_date >= l_validation_start_date and
l_rec.effective_date <= p_validation_end_date) then
--
open csr_valid_payroll(l_rec.effective_date);
select eev.input_value_id,
eev.screen_entry_value,
iv.uom,
-- change 115.30
iv.LOOKUP_TYPE,
-- Bugfix 2827092
iv.value_set_id,
et.input_currency_code
from pay_element_entry_values_f eev,
pay_input_values_f iv,
pay_element_types_f et
where eev.element_entry_id = p_element_entry_id
and iv.input_value_id = eev.input_value_id
and et.element_type_id = iv.element_type_id
and eev.effective_end_date = p_date
and p_date between iv.effective_start_date
and iv.effective_end_date
and p_date between et.effective_start_date
and et.effective_end_date;
select ee.cost_allocation_keyflex_id,
ee.creator_type,
ee.entry_type,
ee.comment_id,
ee.creator_id,
ee.reason,
ee.target_entry_id,
ee.subpriority,
ee.personal_payment_method_id,
ee.date_earned,
ee.balance_adj_cost_flag,
ee.source_asg_action_id,
ee.source_link_id,
ee.source_trigger_entry,
ee.source_period,
ee.source_run_type,
ee.source_start_date,
ee.source_end_date,
ee.attribute_category,
ee.attribute1,
ee.attribute2,
ee.attribute3,
ee.attribute4,
ee.attribute5,
ee.attribute6,
ee.attribute7,
ee.attribute8,
ee.attribute9,
ee.attribute10,
ee.attribute11,
ee.attribute12,
ee.attribute13,
ee.attribute14,
ee.attribute15,
ee.attribute16,
ee.attribute17,
ee.attribute18,
ee.attribute19,
ee.attribute20,
ee.entry_information_category,
ee.entry_information1,
ee.entry_information2,
ee.entry_information3,
ee.entry_information4,
ee.entry_information5,
ee.entry_information6,
ee.entry_information7,
ee.entry_information8,
ee.entry_information9,
ee.entry_information10,
ee.entry_information11,
ee.entry_information12,
ee.entry_information13,
ee.entry_information14,
ee.entry_information15,
ee.entry_information16,
ee.entry_information17,
ee.entry_information18,
ee.entry_information19,
ee.entry_information20,
ee.entry_information21,
ee.entry_information22,
ee.entry_information23,
ee.entry_information24,
ee.entry_information25,
ee.entry_information26,
ee.entry_information27,
ee.entry_information28,
ee.entry_information29,
ee.entry_information30
into v_ele_entry_rec
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and p_date between ee.effective_start_date
and ee.effective_end_date;
SELECT meaning
INTO v_screen_format
FROM HR_LOOKUPS
WHERE lookup_type = v_entry_value.lookup_type
and lookup_code = v_entry_value.screen_entry_value;
select 'Y'
into v_entry_overridden
from sys.dual
where exists
(select null
from pay_element_entries_f ee
where ee.assignment_id = p_assignment_id
and ee.entry_type in ('A','R')
and ee.target_entry_id = p_element_entry_id
and ee.effective_start_date <= p_validation_end_date
and ee.effective_end_date >= p_validation_start_date);
select ee.original_entry_id,
ee.cost_allocation_keyflex_id,
ee.creator_type,
ee.entry_type,
ee.comment_id,
ee.creator_id,
ee.reason,
ee.target_entry_id,
ee.subpriority,
ee.personal_payment_method_id,
ee.date_earned,
ee.source_id,
ee.balance_adj_cost_flag,
ee.source_asg_action_id,
ee.source_link_id,
ee.source_trigger_entry,
ee.source_period,
ee.source_run_type,
ee.source_start_date,
ee.source_end_date,
ee.assignment_id,
ee.updating_action_id,
ee.updating_action_type,
ee.element_link_id,
ee.element_type_id,
ee.object_version_number,
ee.attribute_category,
ee.attribute1,
ee.attribute2,
ee.attribute3,
ee.attribute4,
ee.attribute5,
ee.attribute6,
ee.attribute7,
ee.attribute8,
ee.attribute9,
ee.attribute10,
ee.attribute11,
ee.attribute12,
ee.attribute13,
ee.attribute14,
ee.attribute15,
ee.attribute16,
ee.attribute17,
ee.attribute18,
ee.attribute19,
ee.attribute20,
ee.entry_information_category,
ee.entry_information1,
ee.entry_information2,
ee.entry_information3,
ee.entry_information4,
ee.entry_information5,
ee.entry_information6,
ee.entry_information7,
ee.entry_information8,
ee.entry_information9,
ee.entry_information10,
ee.entry_information11,
ee.entry_information12,
ee.entry_information13,
ee.entry_information14,
ee.entry_information15,
ee.entry_information16,
ee.entry_information17,
ee.entry_information18,
ee.entry_information19,
ee.entry_information20,
ee.entry_information21,
ee.entry_information22,
ee.entry_information23,
ee.entry_information24,
ee.entry_information25,
ee.entry_information26,
ee.entry_information27,
ee.entry_information28,
ee.entry_information29,
ee.entry_information30
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date = decode(p_start_or_end_date,
'START',p_new_date,
ee.effective_start_date)
and ee.effective_end_date = decode(p_start_or_end_date,
'END',p_new_date,
ee.effective_end_date);
l_datetrack_mode := 'UPDATE';
l_datetrack_mode := 'DELETE';
is inserted in EFFECTIVE_DATE of the PROCESS_EVENTS. If need comes
need to revisit the code to store the proper date.*/
begin
select effective_date into l_effective_date
from fnd_sessions where session_id = userenv('sessionid');
/*Calling the AFTER-UPDATE API call to log the event in
PAY_PROCESS_EVENTS.*/
pay_ele_rku.after_update
(
p_effective_date => l_effective_date
,p_validation_start_date => p_old_start_date
,p_validation_end_date => p_old_end_date
,p_datetrack_mode => l_datetrack_mode
-- 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 => l_updating_action_id
,p_updating_action_type => l_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 => v_entry_rec.object_version_number
,p_comments => null
,p_all_entry_values_null => null
-- old values set
,p_effective_start_date_o => p_old_start_date
,p_effective_end_date_o => p_old_end_date
,p_cost_allocation_keyflex_id_o => v_entry_rec.cost_allocation_keyflex_id
,p_assignment_id_o => v_entry_rec.assignment_id
,p_updating_action_id_o => v_entry_rec.updating_action_id
,p_updating_action_type_o => v_entry_rec.updating_action_type
,p_element_link_id_o => v_entry_rec.element_link_id
,p_original_entry_id_o => v_entry_rec.original_entry_id
,p_creator_type_o => v_entry_rec.creator_type
,p_entry_type_o => v_entry_rec.entry_type
,p_comment_id_o => v_entry_rec.comment_id
,p_creator_id_o => v_entry_rec.creator_id
,p_reason_o => v_entry_rec.reason
,p_target_entry_id_o => v_entry_rec.target_entry_id
,p_source_id_o => v_entry_rec.source_id
,p_attribute_category_o => v_entry_rec.attribute_category
,p_attribute1_o => v_entry_rec.attribute1
,p_attribute2_o => v_entry_rec.attribute2
,p_attribute3_o => v_entry_rec.attribute3
,p_attribute4_o => v_entry_rec.attribute4
,p_attribute5_o => v_entry_rec.attribute5
,p_attribute6_o => v_entry_rec.attribute6
,p_attribute7_o => v_entry_rec.attribute7
,p_attribute8_o => v_entry_rec.attribute8
,p_attribute9_o => v_entry_rec.attribute9
,p_attribute10_o => v_entry_rec.attribute10
,p_attribute11_o => v_entry_rec.attribute11
,p_attribute12_o => v_entry_rec.attribute12
,p_attribute13_o => v_entry_rec.attribute13
,p_attribute14_o => v_entry_rec.attribute14
,p_attribute15_o => v_entry_rec.attribute15
,p_attribute16_o => v_entry_rec.attribute16
,p_attribute17_o => v_entry_rec.attribute17
,p_attribute18_o => v_entry_rec.attribute18
,p_attribute19_o => v_entry_rec.attribute19
,p_attribute20_o => v_entry_rec.attribute20
,p_entry_information_category_o => v_entry_rec.entry_information_category
,p_entry_information1_o => v_entry_rec.entry_information1
,p_entry_information2_o => v_entry_rec.entry_information2
,p_entry_information3_o => v_entry_rec.entry_information3
,p_entry_information4_o => v_entry_rec.entry_information4
,p_entry_information5_o => v_entry_rec.entry_information5
,p_entry_information6_o => v_entry_rec.entry_information6
,p_entry_information7_o => v_entry_rec.entry_information7
,p_entry_information8_o => v_entry_rec.entry_information8
,p_entry_information9_o => v_entry_rec.entry_information9
,p_entry_information10_o => v_entry_rec.entry_information10
,p_entry_information11_o => v_entry_rec.entry_information11
,p_entry_information12_o => v_entry_rec.entry_information12
,p_entry_information13_o => v_entry_rec.entry_information13
,p_entry_information14_o => v_entry_rec.entry_information14
,p_entry_information15_o => v_entry_rec.entry_information15
,p_entry_information16_o => v_entry_rec.entry_information16
,p_entry_information17_o => v_entry_rec.entry_information17
,p_entry_information18_o => v_entry_rec.entry_information18
,p_entry_information19_o => v_entry_rec.entry_information19
,p_entry_information20_o => v_entry_rec.entry_information20
,p_entry_information21_o => v_entry_rec.entry_information21
,p_entry_information22_o => v_entry_rec.entry_information22
,p_entry_information23_o => v_entry_rec.entry_information23
,p_entry_information24_o => v_entry_rec.entry_information24
,p_entry_information25_o => v_entry_rec.entry_information25
,p_entry_information26_o => v_entry_rec.entry_information26
,p_entry_information27_o => v_entry_rec.entry_information27
,p_entry_information28_o => v_entry_rec.entry_information28
,p_entry_information29_o => v_entry_rec.entry_information29
,p_entry_information30_o => v_entry_rec.entry_information30
,p_subpriority_o => v_entry_rec.subpriority
,p_personal_payment_method_id_o => v_entry_rec.personal_payment_method_id
,p_date_earned_o => v_entry_rec.date_earned
,p_object_version_number_o => v_entry_rec.object_version_number
,p_balance_adj_cost_flag_o => v_entry_rec.balance_adj_cost_flag
,p_comments_o => null
,p_element_type_id_o => v_entry_rec.element_type_id
,p_all_entry_values_null_o => null
);
select EL.element_type_id element_type_id,
'Y' salary_element
from pay_element_entries_f EE,
pay_element_links_f EL
where EE.element_entry_id = p_element_entry_id
and EL.element_link_id = EE.element_link_id
and nvl(p_old_date,p_effective_start_date) between
EE.effective_start_date and EE.effective_end_date
and nvl(p_old_date,p_effective_start_date) between
EL.effective_start_date and EL.effective_end_date
and exists (select null
from pay_input_values_f IV,
per_pay_bases PB
where IV.element_type_id = EL.element_type_id
and PB.input_value_id = IV.input_value_id)
UNION ALL
select EL.element_type_id element_type_id,
'N' salary_element
from pay_element_entries_f EE,
pay_element_links_f EL
where EE.element_entry_id = p_element_entry_id
and EL.element_link_id = EE.element_link_id
and nvl(p_old_date,p_effective_start_date) between
EE.effective_start_date and EE.effective_end_date
and nvl(p_old_date,p_effective_start_date) between
EL.effective_start_date and EL.effective_end_date
and not exists (select null
from pay_input_values_f IV,
per_pay_bases PB
where IV.element_type_id = EL.element_type_id
and PB.input_value_id = IV.input_value_id);
if p_mode = 'DELETE' then
--
v_change_start_date := p_effective_start_date;
elsif p_mode = 'UPDATE' then
--
v_change_start_date := least(p_old_date,p_new_date);
if p_mode = 'DELETE' or
(p_mode = 'UPDATE' and
((p_start_or_end_date = 'START' and
p_old_date < p_new_date) or
(p_start_or_end_date = 'END' and
p_old_date > p_new_date))) then
--
hrentmnt.check_entry_overridden
(p_assignment_id,
p_element_entry_id,
v_change_start_date,
v_change_end_date);
hr_entry.delete_covered_dependants(
p_validation_start_date => p_old_date,
p_element_entry_id => p_element_entry_id,
p_start_date => p_new_date);
hr_entry.delete_beneficiaries(
p_validation_start_date => p_old_date,
p_element_entry_id => p_element_entry_id,
p_start_date => p_new_date);
hr_entry.delete_covered_dependants(
p_validation_start_date => p_old_date,
p_element_entry_id => p_element_entry_id,
p_end_date => p_new_date);
hr_entry.delete_beneficiaries(
p_validation_start_date => p_old_date,
p_element_entry_id => p_element_entry_id,
p_end_date => p_new_date);
if p_mode = 'UPDATE' then
--
if p_start_or_end_date = 'START' then
--
/*Collecting old data to push the details into PAY_PROCESS_EVENTS*/
hr_utility.trace('Collecting the old data : For back-dating the start date');
select ee.effective_start_date,
ee.effective_end_date,
ee.updating_action_id,
ee.updating_action_type
into l_effective_start_date,
l_effective_end_date,
l_updating_action_id,
l_updating_action_type
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date = p_old_date;
update pay_element_entries_f ee
set ee.effective_start_date = p_new_date,
ee.updating_action_id = decode(ee.updating_action_type, 'S', ee.updating_action_id,
null),
ee.updating_action_type = decode(ee.updating_action_type, 'S', 'S', null)
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date = p_old_date;
update pay_element_entry_values_f eev
set eev.effective_start_date = p_new_date
where eev.element_entry_id = p_element_entry_id
and eev.effective_start_date = p_old_date;
select ee.effective_start_date,
ee.effective_end_date,
ee.updating_action_id,
ee.updating_action_type
into l_effective_start_date,
l_effective_end_date,
l_updating_action_id,
l_updating_action_type
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_end_date = p_old_date;
update pay_element_entries_f ee
set ee.effective_end_date = p_new_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_end_date = p_old_date;
update pay_element_entry_values_f eev
set eev.effective_end_date = p_new_date
where eev.element_entry_id = p_element_entry_id
and eev.effective_end_date = p_old_date;
we need to log this event by calling AFTER-UPDATE trigger.*/
hr_utility.trace('Calling LOG_ENTRY_EVENT to log the event of above date change');
select nvl(pec.non_payments_flag, 'N')
into l_non_payments_flag
from pay_element_links_f pel,
pay_element_types_f pet,
pay_element_classifications pec
where pel.element_link_id = p_element_link_id
and pet.element_type_id = pel.element_type_id
and pec.classification_id = pet.classification_id
and not exists (
select null
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id)
and rownum = 1;
select asg.organization_id,
asg.people_group_id,
asg.job_id,
asg.position_id,
asg.grade_id,
asg.location_id,
asg.employment_category,
asg.payroll_id,
asg.pay_basis_id,
asg.business_group_id
from per_all_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,
min(el.effective_start_date) effective_start_date,
max(el.effective_end_date) effective_end_date
from pay_element_links_f el
where el.element_type_id = p_element_type_id
and el.standard_link_flag = 'N'
and el.business_group_id = p_business_group_id
--
-- make sure EL piece overlaps validation period
--
-- Bugfix 4627931
-- Ensure the EL exists as at the validation start date.
-- This is because hr_entry_api expects the EL to exist as at this date
-- and raises error APP-PAY-07027 if it does not.
-- Also, we should not re-create entries after there has been any gap in
-- eligibility after the validation start date.
and p_validation_start_date between el.effective_start_date and el.effective_end_date
--
-- match crieria on EL to that on asg
--
and (
(el.payroll_id is not null and
el.payroll_id = p_payroll_id)
or
(el.link_to_all_payrolls_flag = 'Y' and
p_payroll_id is not null)
or
(el.link_to_all_payrolls_flag = 'N' and
el.payroll_id is null)
)
and (el.job_id is null or
el.job_id = p_job_id)
and (el.grade_id is null or
el.grade_id = p_grade_id)
and (el.position_id is null or
el.position_id = p_position_id)
and (el.organization_id is null or
el.organization_id = p_organization_id)
and (el.location_id is null or
el.location_id = p_location_id)
and (el.employment_category is null or
el.employment_category = p_employment_category)
and (
el.pay_basis_id = p_pay_basis_id
or
el.pay_basis_id is null and
not exists
(select pb.pay_basis_id
from per_pay_bases pb,
pay_input_values_f iv
where iv.element_type_id = el.element_type_id
and p_validation_start_date between
iv.effective_start_date and iv.effective_end_date
and pb.input_value_id = iv.input_value_id
and pb.business_group_id = p_business_group_id
)
or
el.pay_basis_id is null and
exists
(select pb.pay_basis_id
from per_pay_bases pb,
pay_input_values_f iv
where iv.element_type_id = el.element_type_id
and p_validation_start_date between
iv.effective_start_date and iv.effective_end_date
and pb.input_value_id = iv.input_value_id
and pb.pay_basis_id = p_pay_basis_id
)
or
el.pay_basis_id is null and
p_pay_basis_id is null and
exists
(select pb.pay_basis_id
from per_pay_bases pb,
pay_input_values_f iv
where iv.element_type_id = el.element_type_id
and p_validation_start_date between
iv.effective_start_date and iv.effective_end_date
and pb.input_value_id = iv.input_value_id
and pb.business_group_id = p_business_group_id
)
)
and (el.people_group_id is null
or exists
(select null
from pay_assignment_link_usages_f alu
where alu.assignment_id = p_assignment_id
and alu.element_link_id = el.element_link_id
and alu.effective_start_date <= p_validation_end_date
and alu.effective_end_date >= p_validation_start_date
)
)
group by el.element_link_id;
select 1
into sp_exists
from per_assignments_f
where assignment_id = p_assignment_id
and pay_basis_id is not null
and p_validation_start_date between effective_start_date and effective_end_date;
hr_utility.trace('EE being updated>' || p_element_entry_id || '<');
select 1
into v_original_entry_exists
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
and ee.element_entry_id = p_element_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');
hr_entry_api.insert_element_entry(
p_effective_start_date => l_calc_ee_esd,
p_effective_end_date => l_ee_eed_out,
p_element_entry_id => l_eeid_out,
p_original_entry_id => l_original_entry_id, -- Bug fix 12364433
p_assignment_id => p_assignment_id,
p_element_link_id => rec_eligible_links.element_link_id,
p_creator_type => p_rec_ee.creator_type,
p_entry_type => p_rec_ee.entry_type,
p_cost_allocation_keyflex_id
=> p_rec_ee.cost_allocation_keyflex_id,
p_comment_id => p_rec_ee.comment_id,
p_creator_id => p_rec_ee.creator_id,
p_reason => p_rec_ee.reason,
p_target_entry_id => p_rec_ee.target_entry_id,
p_subpriority => p_rec_ee.subpriority,
p_personal_payment_method_id
=> p_rec_ee.personal_payment_method_id,
p_date_earned => p_rec_ee.date_earned,
p_attribute_category => p_rec_ee.attribute_category,
p_attribute1 => p_rec_ee.attribute1,
p_attribute2 => p_rec_ee.attribute2,
p_attribute3 => p_rec_ee.attribute3,
p_attribute4 => p_rec_ee.attribute4,
p_attribute5 => p_rec_ee.attribute5,
p_attribute6 => p_rec_ee.attribute6,
p_attribute7 => p_rec_ee.attribute7,
p_attribute8 => p_rec_ee.attribute8,
p_attribute9 => p_rec_ee.attribute9,
p_attribute10 => p_rec_ee.attribute10,
p_attribute11 => p_rec_ee.attribute11,
p_attribute12 => p_rec_ee.attribute12,
p_attribute13 => p_rec_ee.attribute13,
p_attribute14 => p_rec_ee.attribute14,
p_attribute15 => p_rec_ee.attribute15,
p_attribute16 => p_rec_ee.attribute16,
p_attribute17 => p_rec_ee.attribute17,
p_attribute18 => p_rec_ee.attribute18,
p_attribute19 => p_rec_ee.attribute19,
p_attribute20 => p_rec_ee.attribute20,
p_entry_information_category
=> p_rec_ee.entry_information_category,
p_entry_information1 => p_rec_ee.entry_information1,
p_entry_information2 => p_rec_ee.entry_information2,
p_entry_information3 => p_rec_ee.entry_information3,
p_entry_information4 => p_rec_ee.entry_information4,
p_entry_information5 => p_rec_ee.entry_information5,
p_entry_information6 => p_rec_ee.entry_information6,
p_entry_information7 => p_rec_ee.entry_information7,
p_entry_information8 => p_rec_ee.entry_information8,
p_entry_information9 => p_rec_ee.entry_information9,
p_entry_information10 => p_rec_ee.entry_information10,
p_entry_information11 => p_rec_ee.entry_information11,
p_entry_information12 => p_rec_ee.entry_information12,
p_entry_information13 => p_rec_ee.entry_information13,
p_entry_information14 => p_rec_ee.entry_information14,
p_entry_information15 => p_rec_ee.entry_information15,
p_entry_information16 => p_rec_ee.entry_information16,
p_entry_information17 => p_rec_ee.entry_information17,
p_entry_information18 => p_rec_ee.entry_information18,
p_entry_information19 => p_rec_ee.entry_information19,
p_entry_information20 => p_rec_ee.entry_information20,
p_entry_information21 => p_rec_ee.entry_information21,
p_entry_information22 => p_rec_ee.entry_information22,
p_entry_information23 => p_rec_ee.entry_information23,
p_entry_information24 => p_rec_ee.entry_information24,
p_entry_information25 => p_rec_ee.entry_information25,
p_entry_information26 => p_rec_ee.entry_information26,
p_entry_information27 => p_rec_ee.entry_information27,
p_entry_information28 => p_rec_ee.entry_information28,
p_entry_information29 => p_rec_ee.entry_information29,
p_entry_information30 => p_rec_ee.entry_information30,
p_num_entry_values => p_num_eevs,
p_input_value_id_tbl => p_tbl_ivids,
p_entry_value_tbl => p_tbl_eevs
);
update pay_element_entry_values_f eev
set eev.effective_end_date = l_ee_eed_out
where eev.element_entry_id = l_eeid_out;
update pay_element_entries_f pee
set pee.effective_end_date = l_ee_eed_out,
pee.balance_adj_cost_flag = p_rec_ee.balance_adj_cost_flag,
pee.source_asg_action_id = p_rec_ee.source_asg_action_id,
pee.source_link_id = p_rec_ee.source_link_id,
pee.source_trigger_entry = p_rec_ee.source_trigger_entry,
pee.source_period = p_rec_ee.source_period,
pee.source_run_type = p_rec_ee.source_run_type,
pee.source_start_date = p_rec_ee.source_start_date,
pee.source_end_date = p_rec_ee.source_end_date
where pee.element_entry_id = l_eeid_out
and l_calc_ee_esd between
pee.effective_start_date and pee.effective_end_date;
select distinct ee.element_entry_id
from pay_element_entries_f ee
where ee.assignment_id = p_assignment_id;
select
distinct
ee.element_entry_id,
ee.creator_type,
ee.creator_id,
ee.effective_start_date,
ee.effective_end_date,
el.element_link_id,
el.standard_link_flag,
el.element_type_id
from
pay_element_entries_f ee,
pay_element_links_f el,
pay_element_types_f et
where ee.assignment_id = p_assignment_id
and ee.effective_start_date <= p_validation_end_date
and ee.effective_end_date >= p_validation_start_date
and ee.entry_type='E'
and ee.element_link_id=el.element_link_id
and el.effective_start_date <= ee.effective_end_date
and el.effective_end_date >= ee.effective_start_date
-- start of change 115.18 --
and ee.effective_start_date between
el.effective_start_date and el.effective_end_date
-- end of change 115.18 --
and el.element_type_id=et.element_type_id
and et.effective_start_date <= el.effective_end_date
and et.effective_end_date >= el.effective_start_date
-- start of change 115.19 --
and ee.effective_start_date between
et.effective_start_date and et.effective_end_date
-- end of change 115.19 --
and et.processing_type='R'
and (
not exists
(select null
from per_all_assignments_f asg
where asg.assignment_id = ee.assignment_id
/* Added Benefits assignment type to the below code to ensure
removal of entries wont happen in the case of benifits
assignment type also */
and (asg.assignment_type = 'E' or asg.assignment_type='B')
and asg.effective_start_date <= p_validation_end_date
and asg.effective_end_date >= p_validation_start_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)
-- start of change 115.22 --
and (
--
-- if EL is associated with a pay basis then this clause fails
--
el.pay_basis_id is null and
NOT EXISTS
(SELECT pb.pay_basis_id
FROM PER_PAY_BASES pb,
PAY_INPUT_VALUES_F iv,
PER_PAY_PROPOSALS pp
WHERE iv.element_type_id = el.element_type_id
and iv.effective_start_date <= asg.effective_end_date /*Bug 7662923 */
and iv.effective_end_date >= asg.effective_start_date
and pb.input_value_id = iv.input_value_id
and pb.business_group_id = asg.business_group_id
and pp.assignment_id = asg.assignment_id /*fix 176449*/
)
or
--
-- if EL is associated with a pay basis then the associated
-- PB_ID must match the PB_ID on ASG
--
el.pay_basis_id is null and
EXISTS
(SELECT pb.pay_basis_id
FROM PER_PAY_BASES pb,
PAY_INPUT_VALUES_F iv
WHERE iv.element_type_id = el.element_type_id
and iv.effective_start_date <= asg.effective_start_date
and iv.effective_end_date >= asg.effective_start_date
and pb.input_value_id =
iv.input_value_id
and pb.pay_basis_id = asg.pay_basis_id
)
-- change 115.26
or
el.standard_link_flag = 'Y' and
el.pay_basis_id is null and
asg.pay_basis_id is null and
EXISTS
(SELECT pb.pay_basis_id
FROM PER_PAY_BASES pb,
PAY_INPUT_VALUES_F iv
WHERE iv.element_type_id = el.element_type_id
and iv.effective_start_date <= asg.effective_start_date
and iv.effective_end_date >= asg.effective_start_date
and pb.input_value_id =
iv.input_value_id
and pb.business_group_id = asg.business_group_id
)
or
el.pay_basis_id = asg.pay_basis_id
)
-- end of change 115.22 --
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 = ee.assignment_id
and alu.element_link_id = ee.element_link_id
and alu.effective_start_date <=
asg.effective_end_date
and alu.effective_end_date >=
asg.effective_start_date))))
;
procedure do_normal_delete_of_ree (
p_assignment_id in number,
p_element_type_id in number,
p_element_link_id in number,
p_element_entry_id in number,
p_validation_start_date in date,
p_validation_end_date in date,
p_ee_effective_start_date in date,
p_ee_effective_end_date in date,
p_ee_creator_type in varchar2,
p_ee_creator_id in number,
p_entries_changed in out nocopy varchar2
)
is
-- cursor added for 8870436
CURSOR csr_bus_group(p_assignment_id number) IS
SELECT business_group_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT LEGISLATION_CODE
FROM per_business_groups
where business_group_id=p_business_group_id;
hr_utility.trace ('***** doing normal delete of REE');
hr_utility.trace ('***** caching EE before delete');
('DELETE',
p_assignment_id,
p_element_entry_id,
null,
null,
null,
p_ee_effective_start_date,
p_ee_effective_end_date,
p_entries_changed);
select ee.original_entry_id
into g_original_entry_id
from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date >= p_ee_effective_start_date
and ee.effective_end_date <= p_ee_effective_end_date;
delete from pay_element_entry_values_f eev
where eev.element_entry_id = p_element_entry_id
and eev.effective_start_date >= p_ee_effective_start_date
and eev.effective_end_date <= p_ee_effective_end_date;
delete from pay_element_entries_f ee
where ee.element_entry_id = p_element_entry_id
and ee.effective_start_date >= p_ee_effective_start_date
and ee.effective_end_date <= p_ee_effective_end_date;
hr_utility.trace(' Cached entry not recreated. Continuing with delete.');
delete from pay_grossup_bal_exclusions exc
where exc.source_id = p_element_entry_id
and exc.source_type = 'EE'
and not exists
( select null
from pay_element_entries_f pee
where pee.element_entry_id = p_element_entry_id);
end do_normal_delete_of_ree;
SELECT business_group_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT LEGISLATION_CODE
FROM per_business_groups
where business_group_id=p_business_group_id;
('DELETE',
p_assignment_id,
p_element_entry_id,
null,
null,
null,
--
-- bugfix 1115901
--
p_validation_start_date,
p_ee_effective_end_date,
p_entries_changed);
UPDATE PAY_ELEMENT_ENTRY_VALUES_F eev
SET eev.effective_end_date = p_validation_start_date - 1
WHERE eev.element_entry_id = p_element_entry_id
and eev.effective_start_date < p_validation_start_date
-- Change 115.60
-- and eev.effective_end_date > p_validation_start_date
and eev.effective_end_date >= p_validation_start_date
-- End of change 115.60
;
UPDATE PAY_ELEMENT_ENTRIES_F ee
SET ee.effective_end_date = p_validation_start_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 < p_validation_start_date
-- Change 115.60
-- and ee.effective_end_date > p_validation_start_date
and ee.effective_end_date >= p_validation_start_date
-- End of change 115.60
;
pay_dyn_triggers.g_dyt_mode := 'DELETE';
SELECT business_group_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT LEGISLATION_CODE
FROM per_business_groups
where business_group_id=p_business_group_id;
SELECT *
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and p_date between effective_start_date and effective_end_date;
('DELETE',
p_assignment_id,
p_element_entry_id,
null,
null,
null,
p_ee_effective_start_date,
p_validation_end_date,
p_entries_changed);
UPDATE PAY_ELEMENT_ENTRY_VALUES_F eev
SET eev.effective_start_date = p_validation_end_date + 1
WHERE eev.element_entry_id = p_element_entry_id
-- Change 115.60
-- and eev.effective_start_date < p_validation_end_date
and eev.effective_start_date <= p_validation_end_date
-- End of change 115.60
and eev.effective_end_date > p_validation_end_date
;
UPDATE PAY_ELEMENT_ENTRIES_F ee
SET ee.effective_start_date = p_validation_end_date + 1,
ee.updating_action_id = decode(ee.updating_action_type, 'S', ee.updating_action_id, null),
ee.updating_action_type = decode(ee.updating_action_type, 'S', 'S', null)
WHERE ee.element_entry_id = p_element_entry_id
-- Change 115.60
-- and ee.effective_start_date < p_validation_end_date
and ee.effective_start_date <= p_validation_end_date
-- End of change 115.60
and ee.effective_end_date > p_validation_end_date
;
UPDATE PER_PAY_PROPOSALS pp
SET pp.change_date = p_validation_end_date + 1
WHERE pp.assignment_id = p_assignment_id
and pp.pay_proposal_id = p_ee_creator_id
;
pay_dyn_triggers.g_dyt_mode := 'UPDATE';
delete from pay_element_entry_values_f eev
where eev.element_entry_id = v_entry.element_entry_id;
delete from pay_element_entries_f ee
where ee.element_entry_id = v_entry.element_entry_id;
do_normal_delete_of_ree (
p_assignment_id => p_assignment_id,
p_element_type_id => v_entry.element_type_id,
p_element_link_id => v_entry.element_link_id,
p_element_entry_id => v_entry.element_entry_id,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date,
p_ee_effective_start_date => v_entry.effective_start_date,
p_ee_effective_end_date => v_entry.effective_end_date,
p_ee_creator_type => v_entry.creator_type,
p_ee_creator_id => v_entry.creator_id,
p_entries_changed => p_entries_changed
);
select ee.element_entry_id,
ee.effective_start_date,
ee.effective_end_date,
ee.element_link_id
from pay_element_entries_f ee
where ee.assignment_id = p_assignment_id
and ee.effective_start_date <= p_validation_end_date
and ee.effective_end_date >= p_validation_start_date
and ee.creator_type in ('F','H')
and ((ee.entry_type <> 'E')
or (ee.entry_type = 'E' and
exists
(select null
from pay_element_links_f el,
pay_element_types_f et
where el.element_link_id = ee.element_link_id
and el.element_type_id = et.element_type_id
and et.processing_type = 'N')))
and not exists
(select null
from per_all_assignments_f asg,
pay_element_links_f el
where el.element_link_id = ee.element_link_id
and asg.assignment_id = ee.assignment_id
and asg.assignment_type = 'E'
and asg.effective_start_date <= ee.effective_end_date
-- changed to validation start date not effective start date
-- wmcveagh bug 586139 17/2/98
and asg.effective_end_date >= p_validation_start_date
and el.effective_start_date <= ee.effective_end_date
and el.effective_end_date >= ee.effective_start_date
and el.effective_start_date <= asg.effective_end_date
and el.effective_end_date >= asg.effective_start_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 = ee.assignment_id
and alu.element_link_id = ee.element_link_id
and alu.effective_start_date <=
asg.effective_end_date
and alu.effective_end_date >=
asg.effective_start_date)));
select 1
into lv_asg_exists
from dual
where exists (select 1
from per_all_assignments_f
where assignment_id =p_assignment_id);
('DELETE',
p_assignment_id,
v_entry.element_entry_id,
null,
null,
null,
v_entry.effective_start_date,
v_entry.effective_end_date,
p_entries_changed);
delete from pay_run_results rr
where rr.status not like 'P%'
and rr.source_type = 'E'
and rr.source_id = v_entry.element_entry_id;
select OBJECT_VERSION_NUMBER into l_obj_ver_num
from pay_element_entries_f
where element_entry_id=v_entry.element_entry_id;
/*Bug 9710104 If assignment doesnt exists delete the entries */
delete from pay_element_entry_values_f eev
where eev.element_entry_id = v_entry.element_entry_id;
delete from pay_element_entries_f ee
where ee.element_entry_id = v_entry.element_entry_id;
pay_element_entry_api.delete_element_entry
(p_datetrack_delete_mode => 'ZAP'
,p_effective_date => v_entry.effective_start_date -- 8230599, pass v_entry.effective_start_date
,p_element_entry_id => v_entry.element_entry_id
,p_object_version_number => l_obj_ver_num
,p_effective_start_date =>l_eff_str_date
,p_effective_end_date =>l_eff_end_date
,p_delete_warning =>l_del_war);
hr_utility.trace(' DELETE element entry ');
pay_element_entry_api.delete_element_entry
(p_datetrack_delete_mode => 'DELETE'
,p_effective_date => p_validation_start_date-1
,p_element_entry_id => v_entry.element_entry_id
,p_object_version_number => l_obj_ver_num
,p_effective_start_date =>l_eff_str_date
,p_effective_end_date =>l_eff_end_date
,p_delete_warning =>l_del_war);
/* delete from pay_element_entry_values_f eev
where eev.element_entry_id = v_entry.element_entry_id;
delete from pay_element_entries_f ee
where ee.element_entry_id = v_entry.element_entry_id;*/
select et.multiple_entries_allowed_flag
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;
select ee.*
from pay_element_entries_f ee
,pay_element_types_f et
where ee.assignment_id = p_assignment_id
and ee.effective_start_date <= p_validation_end_date
and ee.effective_end_date >= p_validation_start_date
and ee.element_type_id = et.element_type_id
and ee.effective_start_date between et.effective_start_date
and et.effective_end_date
--
-- Restrict to nonrecurring entries
--
and (ee.entry_type in ('S','R','A','D')
or (ee.entry_type = 'E'
-- and et.process_in_run_flag = 'Y' /* Bug 14529043*/
and et.processing_type = 'N'))
--
-- Restrict to ordinary creator-type entries which have been processed
-- or to special creator-type entries regardless of processing.
--
/* Bug 7603986. All processed and unprocessed entries are checked for any alternate element links
and ((ee.creator_type in ('F','H')
and not exists
(select null
from pay_run_results rr
where rr.source_id = decode(ee.entry_type,
'A', decode (p_adjust_ee_source,
'T', ee.target_entry_id,
ee.element_entry_id),
'R', decode (p_adjust_ee_source,
'T', ee.target_entry_id,
ee.element_entry_id),
ee.element_entry_id)
and rr.source_type = 'E'
and rr.entry_type = ee.entry_type
and rr.status like 'P%'))
or (ee.creator_type not in ('F','H')))
*/
--
-- Restrict to entries for links which no longer match the assignment
-- criteria.
--
and not exists
(select null
from per_all_assignments_f asg,
pay_element_links_f el
where asg.assignment_id = ee.assignment_id
and el.element_link_id = ee.element_link_id
and asg.assignment_type = 'E'
and asg.effective_start_date <= ee.effective_end_date
and asg.effective_end_date >= ee.effective_end_date -- bug 6485636
and el.effective_start_date <= ee.effective_end_date
and el.effective_end_date >= ee.effective_start_date
and el.effective_start_date <= asg.effective_end_date
and el.effective_end_date >= asg.effective_start_date
--
-- and the link does NOT match the assignment criteria
--
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 = ee.assignment_id
and alu.element_link_id = ee.element_link_id
and alu.effective_start_date <=
asg.effective_end_date
and alu.effective_end_date >=
asg.effective_start_date)));
select /*+ INDEX(paf PER_ASSIGNMENTS_F_PK)*/ plr.rule_mode
into l_adjust_ee_source
from pay_legislation_rules plr,
per_business_groups_perf pbg,
per_all_assignments_f paf
where paf.assignment_id = p_assignment_id
and p_validation_start_date between paf.effective_start_date
and paf.effective_end_date
and paf.business_group_id = pbg.business_group_id
and pbg.legislation_code = plr.legislation_code
and plr.rule_type = 'ADJUSTMENT_EE_SOURCE';
l_entry_table.element_entry_id.delete;
l_entry_table.element_link_id.delete;
l_entry_table.effective_start_date.delete;
l_entry_table.effective_end_date.delete;
select hl.meaning
into l_creator_type_meaning
from hr_lookups hl
where hl.lookup_type = 'CREATOR_TYPE'
and hl.lookup_code = r_entry.creator_type;
hr_utility.trace('Doing bulk update of element_link_id');
update pay_element_entries_f
set element_link_id = l_entry_table.element_link_id(i)
where element_entry_id = l_entry_table.element_entry_id(i)
and effective_start_date = l_entry_table.effective_start_date(i)
and effective_end_date = l_entry_table.effective_end_date(i);
select null
from dual
where exists
(select null
from pay_element_entries_f pee,
per_time_periods ptp
where pee.assignment_id = p_assignment_id
and pee.entry_type in ('A','R','S')
and pee.effective_start_date > p_val_start_date
and ptp.payroll_id = p_payroll_id
and pee.effective_start_date between ptp.start_date and ptp.end_date
and pee.effective_end_date between ptp.start_date and ptp.end_date
group by pee.target_entry_id ,ptp.time_period_id
having count(*) > 1);
select ee.element_entry_id,
ee.effective_start_date,
ee.effective_end_date,
ee.element_link_id
from pay_element_entries_f ee
where ee.assignment_id = p_assignment_id
and ee.effective_start_date <= p_val_end_date_plus_one
and ee.effective_end_date >= p_val_start_date_minus_one
and ((ee.entry_type <> 'E') or
(ee.entry_type = 'E' and
exists
(select null
from pay_element_links_f el,
pay_element_types_f et
where el.element_link_id = ee.element_link_id
and et.element_type_id = el.element_type_id
and et.processing_type = 'N')));
select min(effective_start_date)
from per_all_assignments_f
where assignment_id = p_assignment_id;
if p_dt_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
if g_debug then
hr_utility.trace ('1');
select count(*) into v_start_date_check from per_time_periods where payroll_id in(
select payroll_id from per_all_assignments_f where assignment_id=p_assignment_id
and p_val_start_date_minus_one between effective_start_date and effective_end_date)
and p_val_start_date_minus_one between start_date and end_date;
select count(*) into v_end_date_check from per_time_periods where payroll_id in(
select payroll_id from per_all_assignments_f where assignment_id=p_assignment_id
and p_val_end_date_plus_one between effective_start_date and effective_end_date)
and p_val_end_date_plus_one between start_date and end_date;
select end_date into v_vale_start_date from per_time_periods where payroll_id in(
select payroll_id from per_all_assignments_f where assignment_id=p_assignment_id
and v_chng_date between effective_start_date and effective_end_date)
and v_chng_date between start_date and end_date;
if p_dt_mode='DELETE' then
/* Code for termination of employee */
if v_entry.effective_start_date <= p_val_start_date_minus_one then
v_session_date := v_entry.effective_start_date;
('UPDATE',
p_assignment_id,
v_entry.element_entry_id,
'START',
v_entry.effective_start_date,
v_effective_start_date,
null,
null,
p_entries_changed);
('UPDATE',
p_assignment_id,
v_entry.element_entry_id,
'END',
v_entry.effective_end_date,
v_effective_end_date,
null,
null,
p_entries_changed);
SELECT dfc.descriptive_flex_context_code
FROM PER_BUSINESS_GROUPS_PERF bg,
PAY_ELEMENT_LINKS_F el,
PAY_ELEMENT_TYPES_F et,
PAY_ELEMENT_CLASSIFICATIONS ec,
FND_DESCR_FLEX_CONTEXTS dfc
WHERE bg.business_group_id =
el.business_group_id
and el.element_link_id = b_element_link_id
and b_effective_date between
el.effective_start_date and el.effective_end_date
and et.element_type_id =
el.element_type_id
and b_effective_date between
et.effective_start_date and et.effective_end_date
and ec.classification_id =
et.classification_id
and dfc.descriptive_flex_context_code = upper(
bg.legislation_code || '_' || ec.classification_name)
and dfc.application_id = 801
and dfc.descriptive_flexfield_name = 'Element Entry Developer DF';
select distinct nvl(ee.original_entry_id,ee.element_entry_id)
from pay_element_entries_f ee
where p_mult_ent_allowed_flag = 'Y'
and ee.entry_type = 'E'
and ee.assignment_id = p_assignment_id
and ee.element_link_id = p_element_link_id
and ee.effective_start_date <= p_entry_end_date
and ee.effective_end_date >= p_entry_start_date
UNION ALL
select to_number(null)
from sys.dual
where p_mult_ent_allowed_flag = 'N'
and exists
(select null
from pay_element_entries_f ee
where ee.entry_type = 'E'
and ee.assignment_id = p_assignment_id
and ee.element_link_id = p_element_link_id
and ee.effective_start_date <= p_entry_end_date
and ee.effective_end_date >= p_entry_start_date);
select ee.element_entry_id,
ee.original_entry_id,
ee.effective_start_date,
ee.effective_end_date,
ee.element_link_id,
ee.creator_type
from pay_element_entries_f ee
where ee.entry_type = 'E'
and ee.effective_start_date <= p_entry_end_date
and ee.effective_end_date >= p_entry_start_date
and p_mult_ent_allowed_flag = 'Y'
and ee.element_entry_id = p_element_entry_id
and ee.original_entry_id is null
UNION ALL
select ee.element_entry_id,
ee.original_entry_id,
ee.effective_start_date,
ee.effective_end_date,
ee.element_link_id,
ee.creator_type
from pay_element_entries_f ee
where ee.entry_type = 'E'
and ee.effective_start_date <= p_entry_end_date
and ee.effective_end_date >= p_entry_start_date
and p_mult_ent_allowed_flag = 'Y'
and ee.original_entry_id = p_element_entry_id
UNION ALL
select /*+ index(ee PAY_ELEMENT_ENTRIES_F_N51) */ ee.element_entry_id,
ee.original_entry_id,
ee.effective_start_date,
ee.effective_end_date,
ee.element_link_id,
ee.creator_type
from pay_element_entries_f ee
where ee.entry_type = 'E'
and ee.effective_start_date <= p_entry_end_date
and ee.effective_end_date >= p_entry_start_date
and p_mult_ent_allowed_flag = 'N'
and ee.assignment_id = p_assignment_id
and ee.element_link_id = p_element_link_id
order by 3;
select standard_link_flag
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;
('UPDATE',
p_assignment_id,
v_first_element_entry_id,
'START',
v_entry_start_date,
v_calc_entry_start_date,
null,
null,
p_entries_changed);
('UPDATE',
p_assignment_id,
v_first_element_entry_id,
'START',
v_entry_start_date,
v_calc_entry_start_date,
null,
null,
p_entries_changed);
('UPDATE',
p_assignment_id,
v_last_element_entry_id,
'END',
v_entry_end_date,
p_entry_end_date_tbl(v_loop),
null,
null,
p_entries_changed);
p_dt_mode <> 'UPDATE' and
p_dt_mode <> 'UPDATE_CHANGE_INSERT' and
--
-- boundary cases --
(
-- Bugfix 4221603
-- In UPDATE_OVERRIDE mode, only move
-- end date forwards when the entry is
-- a standard entry.
(p_dt_mode = 'UPDATE_OVERRIDE' and
standard_element(v_entry.element_link_id,
v_calc_entry_end_date) and
-- Bugfix 4765204
-- Only continue if entry ends within the
-- 'range of change'.
p_validation_start_date <= v_entry_end_date
)
or
-- Or when the entry was created by a
-- Salary Proposal
(p_dt_mode = 'UPDATE_OVERRIDE' and
v_entry.creator_type = 'SP' and
-- Bugfix 4765204
-- Only continue if entry ends within the
-- 'range of change'.
p_validation_start_date <= v_entry_end_date
)
or
(p_dt_mode <> 'UPDATE_OVERRIDE')
) and
(
(p_dt_mode = 'DELETE_NEXT_CHANGE' and
p_val_start_date_minus_one = v_entry_end_date and
standard_element(v_entry.element_link_id,
v_calc_entry_end_date)
)
or
(p_dt_mode = 'DELETE_NEXT_CHANGE' and
p_val_start_date_minus_one = v_entry_end_date and
--Bug 6809717 Added F to the following code to adjust recurring
--entries properly
/* Bug 12663687 - If entry was end dated by user, don't remove end date.
Logic: if element link for entry is valid in period beyond the entry end
end date, we can assume it was manually end dated and end date should be
retained. */
(v_entry.creator_type in ('SP', 'UT') or -- fix for bug 12663687
(v_entry.creator_type = 'F' and
check_prior_eligibility(p_assignment_id
,v_entry.element_link_id
,p_validation_start_date) = 'N')) -- 2437795
)
or
(p_dt_mode <> 'DELETE_NEXT_CHANGE')
) and
-- Bugfix 4354757
-- Only move end date forwards in FUTURE_CHANGE mode
-- for standard entries, salary proposal entries and
-- US tax entries (to be consistent with
-- DELETE_NEXT_CHANGE mode).
(
(p_dt_mode = 'FUTURE_CHANGE' and
p_val_start_date_minus_one = v_entry_end_date and
standard_element(v_entry.element_link_id,
v_calc_entry_end_date)
)
or
(p_dt_mode = 'FUTURE_CHANGE' and
p_val_start_date_minus_one = v_entry_end_date and
v_entry.creator_type in ('SP', 'UT')
)
or
(p_dt_mode <> 'FUTURE_CHANGE')
) and
(
(p_dt_mode = 'CORRECTION' and
p_val_start_date_minus_one = v_entry_end_date and
standard_element(v_entry.element_link_id,
v_calc_entry_end_date)
)
or
(p_dt_mode = 'CORRECTION' and
p_val_start_date_minus_one = v_entry_end_date and
v_entry.creator_type = 'SP'
)
or
(p_dt_mode <> 'CORRECTION')
) and
(
(p_dt_mode = 'INSERT' and
p_val_start_date_minus_one = v_entry_end_date and
standard_element(v_entry.element_link_id,
v_calc_entry_end_date)
)
or
(p_dt_mode = 'INSERT' and
p_val_start_date_minus_one = v_entry_end_date and
v_entry.creator_type in ('SP', 'UT')
)
or
(p_dt_mode <> 'INSERT')
) then
if g_debug then
hr_utility.trace ('***** bringing "end" date of current REE forwards *****');
('UPDATE',
p_assignment_id,
v_last_element_entry_id,
'END',
v_entry_end_date,
v_entry.effective_start_date - 1,
null,
null,
p_entries_changed);
('UPDATE',
p_assignment_id,
v_last_element_entry_id,
'END',
v_entry_end_date,
v_calc_entry_end_date,
null,
null,
p_entries_changed);
hr_entry_api.insert_element_entry
(p_effective_start_date => v_calc_entry_start_date,
p_effective_end_date => v_dummy_date,
p_element_entry_id => v_element_entry_id,
p_original_entry_id => v_distinct_entry_id,
p_assignment_id => p_assignment_id,
p_element_link_id => p_element_link_id,
p_creator_type => v_ele_entry_rec.creator_type,
p_entry_type => v_ele_entry_rec.entry_type,
p_cost_allocation_keyflex_id
=> v_ele_entry_rec.cost_allocation_keyflex_id,
p_comment_id => v_ele_entry_rec.comment_id,
p_creator_id => v_ele_entry_rec.creator_id,
p_reason => v_ele_entry_rec.reason,
p_target_entry_id => v_ele_entry_rec.target_entry_id,
p_subpriority => v_ele_entry_rec.subpriority,
p_personal_payment_method_id
=> v_ele_entry_rec.personal_payment_method_id,
p_date_earned => v_ele_entry_rec.date_earned,
p_attribute_category => v_ele_entry_rec.attribute_category,
p_attribute1 => v_ele_entry_rec.attribute1,
p_attribute2 => v_ele_entry_rec.attribute2,
p_attribute3 => v_ele_entry_rec.attribute3,
p_attribute4 => v_ele_entry_rec.attribute4,
p_attribute5 => v_ele_entry_rec.attribute5,
p_attribute6 => v_ele_entry_rec.attribute6,
p_attribute7 => v_ele_entry_rec.attribute7,
p_attribute8 => v_ele_entry_rec.attribute8,
p_attribute9 => v_ele_entry_rec.attribute9,
p_attribute10 => v_ele_entry_rec.attribute10,
p_attribute11 => v_ele_entry_rec.attribute11,
p_attribute12 => v_ele_entry_rec.attribute12,
p_attribute13 => v_ele_entry_rec.attribute13,
p_attribute14 => v_ele_entry_rec.attribute14,
p_attribute15 => v_ele_entry_rec.attribute15,
p_attribute16 => v_ele_entry_rec.attribute16,
p_attribute17 => v_ele_entry_rec.attribute17,
p_attribute18 => v_ele_entry_rec.attribute18,
p_attribute19 => v_ele_entry_rec.attribute19,
p_attribute20 => v_ele_entry_rec.attribute20,
p_entry_information_category
=> v_ele_entry_rec.entry_information_category,
p_entry_information1 => v_ele_entry_rec.entry_information1,
p_entry_information2 => v_ele_entry_rec.entry_information2,
p_entry_information3 => v_ele_entry_rec.entry_information3,
p_entry_information4 => v_ele_entry_rec.entry_information4,
p_entry_information5 => v_ele_entry_rec.entry_information5,
p_entry_information6 => v_ele_entry_rec.entry_information6,
p_entry_information7 => v_ele_entry_rec.entry_information7,
p_entry_information8 => v_ele_entry_rec.entry_information8,
p_entry_information9 => v_ele_entry_rec.entry_information9,
p_entry_information10 => v_ele_entry_rec.entry_information10,
p_entry_information11 => v_ele_entry_rec.entry_information11,
p_entry_information12 => v_ele_entry_rec.entry_information12,
p_entry_information13 => v_ele_entry_rec.entry_information13,
p_entry_information14 => v_ele_entry_rec.entry_information14,
p_entry_information15 => v_ele_entry_rec.entry_information15,
p_entry_information16 => v_ele_entry_rec.entry_information16,
p_entry_information17 => v_ele_entry_rec.entry_information17,
p_entry_information18 => v_ele_entry_rec.entry_information18,
p_entry_information19 => v_ele_entry_rec.entry_information19,
p_entry_information20 => v_ele_entry_rec.entry_information20,
p_entry_information21 => v_ele_entry_rec.entry_information21,
p_entry_information22 => v_ele_entry_rec.entry_information22,
p_entry_information23 => v_ele_entry_rec.entry_information23,
p_entry_information24 => v_ele_entry_rec.entry_information24,
p_entry_information25 => v_ele_entry_rec.entry_information25,
p_entry_information26 => v_ele_entry_rec.entry_information26,
p_entry_information27 => v_ele_entry_rec.entry_information27,
p_entry_information28 => v_ele_entry_rec.entry_information28,
p_entry_information29 => v_ele_entry_rec.entry_information29,
p_entry_information30 => v_ele_entry_rec.entry_information30,
p_num_entry_values => v_num_entry_values,
p_input_value_id_tbl => v_input_value_id_tbl,
p_entry_value_tbl => v_entry_value_tbl);
update pay_element_entries_f pee
set pee.balance_adj_cost_flag = v_ele_entry_rec.balance_adj_cost_flag,
pee.source_asg_action_id = v_ele_entry_rec.source_asg_action_id,
pee.source_link_id = v_ele_entry_rec.source_link_id,
pee.source_trigger_entry = v_ele_entry_rec.source_trigger_entry,
pee.source_period = v_ele_entry_rec.source_period,
pee.source_run_type = v_ele_entry_rec.source_run_type,
pee.source_start_date = v_ele_entry_rec.source_start_date,
pee.source_end_date = v_ele_entry_rec.source_end_date
where pee.element_entry_id = v_element_entry_id
and v_calc_entry_start_date between
pee.effective_start_date and pee.effective_end_date;
hr_utility.trace('********** for SL call EE insert interface');
hr_entry_api.insert_element_entry
(p_effective_start_date => v_calc_entry_start_date,
p_effective_end_date => v_dummy_date,
p_element_entry_id => v_element_entry_id,
p_assignment_id => p_assignment_id,
p_element_link_id => p_element_link_id,
p_creator_type => 'F',
p_entry_type => 'E');
UPDATE pay_element_entries_f
SET entry_information_category = l_category
WHERE element_entry_id = v_element_entry_id;
select ee.element_entry_id,
ee.creator_type,
ee.creator_id,
ee.effective_start_date,
ee.effective_end_date,
ee.element_link_id
from pay_element_entries_f ee
where ee.assignment_id = p_assignment_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 = ee.element_link_id
and el.standard_link_flag = 'Y'
and et.element_type_id = el.element_type_id
and et.processing_type = 'R')
order by ee.element_link_id, ee.effective_start_date;
('DELETE',
p_assignment_id,
v_entry.element_entry_id,
null,
null,
null,
v_entry.effective_start_date,
v_entry.effective_end_date,
p_entries_changed);
delete from pay_element_entry_values_f eev
where eev.element_entry_id = v_entry.element_entry_id
and eev.effective_start_date = v_entry.effective_start_date
and eev.effective_end_date = v_entry.effective_end_date;
delete from pay_element_entries_f ee
where ee.element_entry_id = v_entry.element_entry_id
and ee.effective_start_date = v_entry.effective_start_date
and ee.effective_end_date = v_entry.effective_end_date;
('UPDATE',
p_assignment_id,
v_entry.element_entry_id,
'START',
v_entry.effective_start_date,
v_new_entry_start_date,
null,
null,
p_entries_changed);
select ee.element_entry_id,
ee.effective_start_date,
ee.effective_end_date,
ee.element_link_id
from pay_element_entries_f ee
where ee.assignment_id = p_assignment_id
and ee.entry_type = 'E'
and nvl(ee.updating_action_type,'null') <> 'S' -- Bugfix 2249308
and exists
(select null
from pay_element_links_f el,
pay_element_types_f et
where el.element_link_id = ee.element_link_id
and el.element_type_id = et.element_type_id
and et.processing_type = 'R')
and ee.effective_end_date =
(select decode(et.post_termination_rule,
'A',nvl(p_actual_term_date,p_sot),
'L',nvl(p_last_standard_date,p_sot),
'F',nvl(p_final_process_date,p_sot),
p_sot)
from pay_element_links_f el,
pay_element_types_f et
where el.element_link_id = ee.element_link_id
and et.element_type_id = el.element_type_id
and ee.effective_start_date between el.effective_start_date
and el.effective_end_date
and ee.effective_start_date between et.effective_start_date
and et.effective_end_date);
select ptp.start_date
from per_assignments_f asg,
per_time_periods ptp
where asg.assignment_id = p_assignment_id
and p_final_process_date
between effective_start_date and asg.effective_end_date
and ptp.payroll_id = asg.payroll_id
and p_final_process_date between ptp.start_date and ptp.end_date
and exists ( select null from pay_element_entries_f ent,
pay_element_types_f et
where ent.assignment_id = asg.assignment_id
and et.element_type_id = ent.element_type_id
and et.processing_type = 'N'
and ent.effective_end_date >= ptp.start_date);
('UPDATE',
p_assignment_id,
v_entry.element_entry_id,
'END',
v_entry.effective_end_date,
v_entry_end_date,
null,
null,
p_entries_changed);
select el.element_link_id,
min(el.effective_start_date) effective_start_date,
max(el.effective_end_date) effective_end_date,
el.link_to_all_payrolls_flag,
el.payroll_id,
el.job_id,
el.grade_id,
el.position_id,
el.organization_id,
el.location_id,
el.pay_basis_id,
el.employment_category,
el.people_group_id,
el.element_type_id,
el.standard_link_flag
from pay_element_links_f el
where el.business_group_id = p_business_group_id
and ((el.standard_link_flag = 'Y' and
exists
(select null
from pay_element_links_f el2
where el2.element_link_id = el.element_link_id
and el.effective_start_date <= p_validation_end_date
and el.effective_end_date >= p_validation_start_date))
or (el.standard_link_flag = 'N' and
(exists
(
-- change 115.40
--select null
select /*+ index(ee pay_element_entries_f_n51) */ null
from pay_element_entries_f ee,
pay_element_types_f et
where ee.element_type_id = et.element_type_id
and et.processing_type = 'R'
and ee.entry_type = 'E'
and ee.assignment_id = p_assignment_id
and ee.element_link_id = el.element_link_id
-- Bugfix 7662923
and ee.effective_start_date <= p_val_end_date_plus_one
and ee.effective_end_date >= p_val_start_date_minus_one)) /*Bug 8879339 reverted fix for 7662923 ,it is now fixed in remove_eligible_recurring */
-- bug 15932432 removed exists() check for 'SP'
-- start of change 115.16 --
-- Ensure this non-standard link has not been changed from/to a standard link
and NOT EXISTS
(SELECT null
FROM PAY_ELEMENT_LINKS_F el_sub
WHERE el_sub.element_link_id = el.element_link_id
and el_sub.standard_link_flag = 'Y'
)
-- end of change 115.16 --
))
-- Change 115.60
-- Bugfix 2121907
-- Ensure no entries exist for this element link and assignment
and not exists (
select null
from pay_element_entries_f ee
where ee.assignment_id = p_assignment_id
and ee.element_link_id = el.element_link_id
and ee.effective_start_date <= p_validation_end_date
and ee.effective_end_date >= p_validation_start_date
and p_dt_mode in
('UPDATE','CORRECTION','UPDATE_CHANGE_INSERT'))
-- End of change 115.60
-- Change 115.146
-- Bugfix 10401986, changes start
-- Ensure when salary basis is removed in CORRECTION mode, salary
-- element link is not picked up for eligibility criteria validation.
and not exists (
SELECT null
FROM per_all_assignments_f asg_cur, pay_element_entries_f peef
WHERE asg_cur.assignment_id = p_assignment_id
AND asg_cur.effective_start_date = p_validation_start_date
AND asg_cur.pay_basis_id is null
AND EXISTS ( select null
from per_all_assignments_f asg_pre
where asg_pre.assignment_id = p_assignment_id
and asg_pre.effective_end_date = p_val_start_date_minus_one
and asg_pre.pay_basis_id is not null)
AND peef.element_link_id = el.element_link_id
AND peef.assignment_id = p_assignment_id
AND peef.creator_type = 'SP'
AND p_dt_mode in ('CORRECTION')
)
-- End of change 115.146
group by el.element_link_id,
el.link_to_all_payrolls_flag,
el.payroll_id,
el.job_id,
el.grade_id,
el.position_id,
el.organization_id,
el.location_id,
el.pay_basis_id,
el.employment_category,
el.people_group_id,
el.element_type_id,
el.standard_link_flag
;
select asg.effective_start_date,
asg.effective_end_date
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.assignment_type = 'E'
and asg.effective_start_date <= p_val_end_date_plus_one
and asg.effective_end_date >= p_val_start_date_minus_one
and asg.effective_start_date <= p_link_end_date
and asg.effective_end_date >= p_link_start_date
and ((p_payroll_id is not null and
p_payroll_id = asg.payroll_id)
or (p_link_to_all_payrolls_flag = 'Y' and
asg.payroll_id is not null)
or (p_link_to_all_payrolls_flag = 'N' and
p_payroll_id is null))
and (p_job_id is null or
p_job_id = asg.job_id)
and (p_grade_id is null or
p_grade_id = asg.grade_id)
and (p_position_id is null or
p_position_id = asg.position_id)
and (p_organization_id is null or
p_organization_id = asg.organization_id)
and (p_location_id is null or
p_location_id = asg.location_id)
and (
--
-- null passed down from EL,
-- if EL is NOT associated with a pay basis then return true
--
p_pay_basis_id is null and
NOT EXISTS
(SELECT /*+ ORDERED INDEX(pb PER_PAY_BASES_N1)*/
pb.pay_basis_id
FROM PAY_ELEMENT_LINKS_F el,
PAY_INPUT_VALUES_F iv,
PER_PAY_BASES pb
WHERE el.element_link_id = p_element_link_id
and el.effective_start_date <= asg.effective_start_date
and el.effective_end_date >= asg.effective_start_date
and iv.element_type_id =
el.element_type_id
and iv.effective_start_date <= el.effective_start_date
and iv.effective_end_date >= el.effective_start_date
and pb.input_value_id =
iv.input_value_id
and pb.business_group_id = asg.business_group_id
)
or
--
-- null passed down from EL,
-- if EL is associated with a pay basis then the associated PB_ID
-- must match the PB_ID on ASG
--
p_pay_basis_id is null and
EXISTS
(SELECT pb.pay_basis_id
FROM PER_PAY_BASES pb,
PAY_INPUT_VALUES_F iv,
PAY_ELEMENT_LINKS_F el
WHERE el.element_link_id = p_element_link_id
and el.effective_start_date <= asg.effective_start_date
and el.effective_end_date >= asg.effective_start_date
and iv.element_type_id =
el.element_type_id
and iv.effective_start_date <= el.effective_start_date
and iv.effective_end_date >= el.effective_start_date
and pb.input_value_id =
iv.input_value_id
and pb.pay_basis_id = asg.pay_basis_id
)
-- change 115.26
or
p_pay_basis_id is null and
asg.pay_basis_id is null and
EXISTS
(SELECT /*+ ORDERED INDEX(pb PER_PAY_BASES_N1)*/
pb.pay_basis_id
FROM PAY_ELEMENT_LINKS_F el,
PAY_INPUT_VALUES_F iv,
PER_PAY_BASES pb
WHERE el.element_link_id = p_element_link_id
and el.effective_start_date <= asg.effective_start_date
and el.effective_end_date >= asg.effective_start_date
and iv.element_type_id =
el.element_type_id
and iv.effective_start_date <= el.effective_start_date
and iv.effective_end_date >= el.effective_start_date
and pb.input_value_id =
iv.input_value_id
and pb.business_group_id = asg.business_group_id
)
or
p_pay_basis_id = asg.pay_basis_id
)
and (p_employment_category is null or
p_employment_category = asg.employment_category)
and (p_people_group_id is null or
exists
(select null
from pay_assignment_link_usages_f alu
where alu.assignment_id = p_assignment_id
and alu.element_link_id = p_element_link_id
and alu.effective_start_date <= asg.effective_end_date
and alu.effective_end_date >= asg.effective_start_date))
order by asg.effective_start_date
for update;
and p_dt_mode in ( 'UPDATE',
'UPDATE_CHANGE_INSERT',
'UPDATE_OVERRIDE',
'CORRECTION') )
--
or p_dt_mode in ( 'DELETE',
'FUTURE_CHANGE',
'DELETE_NEXT_CHANGE')
then
--
-- Adjust any nonrecurring entries which have been affected by a change
-- of payroll.
--
hrentmnt.adjust_nonrecurring_entries ( p_assignment_id,
v_val_start_date_minus_one,
v_val_end_date_plus_one,
p_entries_changed,
p_dt_mode);
select 1
from per_assignments_f
where assignment_id = p_asgid;
select asg.assignment_id,
asg.effective_start_date,
asg.effective_end_date
from per_all_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.assignment_type = 'E'
and asg.effective_start_date <= p_effective_end_date
and asg.effective_end_date >= p_effective_start_date
and ((p_payroll_id is not null and
p_payroll_id = asg.payroll_id)
or (p_link_to_all_payrolls_flag = 'Y' and
asg.payroll_id is not null)
or (p_link_to_all_payrolls_flag = 'N' and
p_payroll_id is null))
and (p_job_id is null or
p_job_id = asg.job_id)
and (p_grade_id is null or
p_grade_id = asg.grade_id)
and (p_position_id is null or
p_position_id = asg.position_id)
and (p_organization_id is null or
p_organization_id = asg.organization_id)
and (p_location_id is null or
p_location_id = asg.location_id)
and (p_employment_category is null or
p_employment_category = asg.employment_category)
and (p_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 = p_element_link_id
and alu.effective_start_date <= asg.effective_end_date
and alu.effective_end_date >= asg.effective_start_date))
and (p_pay_basis_id = asg.pay_basis_id
or (p_pay_basis_id is null and
(asg.pay_basis_id is null
-- Indirect salary basis check.
or not exists
(select pb.pay_basis_id
from per_pay_bases pb,
pay_input_values_f iv
where iv.element_type_id = p_element_type_id
and pb.input_value_id = iv.input_value_id
and pb.business_group_id = p_business_group_id
)
or exists
(select pb.pay_basis_id
from per_pay_bases pb,
pay_input_values_f iv
where iv.element_type_id = p_element_type_id
and pb.input_value_id = iv.input_value_id
and pb.pay_basis_id = asg.pay_basis_id
)
)
)
)
--
-- ensure no entries exist for the assignment
--
and not exists
(select null from pay_element_entries_f pee
where pee.assignment_id = asg.assignment_id
and pee.element_link_id = p_element_link_id
)
order by asg.assignment_id, asg.effective_start_date
for update nowait;
select asg.assignment_id,
asg.effective_start_date,
asg.effective_end_date
from per_all_assignments_f asg
where asg.business_group_id = p_business_group_id
and asg.assignment_type = 'E'
and asg.effective_start_date <= p_effective_end_date
and asg.effective_end_date >= p_effective_start_date
and ((p_payroll_id is not null and
p_payroll_id = asg.payroll_id)
or (p_link_to_all_payrolls_flag = 'Y' and
asg.payroll_id is not null)
or (p_link_to_all_payrolls_flag = 'N' and
p_payroll_id is null))
and (p_job_id is null or
p_job_id = asg.job_id)
and (p_grade_id is null or
p_grade_id = asg.grade_id)
and (p_position_id is null or
p_position_id = asg.position_id)
and (p_organization_id is null or
p_organization_id = asg.organization_id)
and (p_location_id is null or
p_location_id = asg.location_id)
and (p_employment_category is null or
p_employment_category = asg.employment_category)
and (p_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 = p_element_link_id
and alu.effective_start_date <= asg.effective_end_date
and alu.effective_end_date >= asg.effective_start_date))
and (p_pay_basis_id = asg.pay_basis_id
or (p_pay_basis_id is null and
(asg.pay_basis_id is null
-- Indirect salary basis check.
or not exists
(select pb.pay_basis_id
from per_pay_bases pb,
pay_input_values_f iv
where iv.element_type_id = p_element_type_id
and pb.input_value_id = iv.input_value_id
and pb.business_group_id = p_business_group_id
)
or exists
(select pb.pay_basis_id
from per_pay_bases pb,
pay_input_values_f iv
where iv.element_type_id = p_element_type_id
and pb.input_value_id = iv.input_value_id
and pb.pay_basis_id = asg.pay_basis_id
)
)
)
)
--
-- ensure no entries exist for the assignment
--
and not exists
(select null from pay_element_entries_f pee
where pee.assignment_id = asg.assignment_id
and pee.element_link_id = p_element_link_id
)
order by asg.assignment_id, asg.effective_start_date
for update nowait;
hr_utility.trace('********** for SL call EE insert interface');
hr_entry_api.insert_element_entry
(p_effective_start_date => v_entry_start_date,
p_effective_end_date => v_entry_end_date,
p_element_entry_id => v_dummy_number,
p_assignment_id => v_asg_id,
p_element_link_id => p_element_link_id,
p_creator_type => 'F',
p_entry_type => 'E');
UPDATE pay_element_entries_f
SET entry_information_category = l_category
WHERE element_entry_id = v_dummy_number;
SELECT asg.assignment_id,
asg.effective_start_date,
asg.effective_end_date,
asg.primary_flag,
--
-- begin criteria used for EL matching
--
asg.organization_id,
asg.people_group_id,
asg.job_id,
asg.position_id,
asg.grade_id,
asg.location_id,
asg.employment_category,
asg.payroll_id,
asg.pay_basis_id
--
-- end criteria used for EL matching
--
FROM PER_ALL_ASSIGNMENTS_F asg
WHERE asg.assignment_id = b_assignment_id
and asg.business_group_id = b_business_group_id
ORDER BY
asg.assignment_id,
asg.effective_start_date
;
select distinct
pee.element_entry_id,
pee.entry_type,
pee.creator_type,
to_char(pee.effective_start_date, 'YYYY/MM/DD') esd,
to_char(pee.effective_end_date, 'YYYY/MM/DD') eed,
pel.element_link_id,
pel.element_type_id,
pel.payroll_id,
pel.job_id,
pel.position_id,
pel.people_group_id,
pel.organization_id,
pel.location_id,
pel.grade_id,
pel.pay_basis_id,
pel.link_to_all_payrolls_flag,
pel.standard_link_flag
from pay_element_entries_f pee,
pay_element_links_f pel
where pee.assignment_id = p_assignment_id
and pel.element_link_id = pee.element_link_id;
select ppm.personal_payment_method_id,
ppm.org_payment_method_id,
greatest(ppm.effective_start_date,p_validation_start_date)
start_date,
least(ppm.effective_end_date,p_validation_end_date) end_date
from pay_personal_payment_methods_f ppm
where ppm.assignment_id = p_assignment_id
and ppm.effective_start_date <= p_validation_end_date
and ppm.effective_end_date >= p_validation_start_date;
if p_dt_mode = 'DELETE' or p_dt_mode = 'ZAP' then
--
begin
select 'Y'
into no_opmu
from sys.dual
where exists
(select null
from pay_personal_payment_methods_f ppm
where assignment_id = p_assignment_id
and ppm.effective_end_date >= p_validation_start_date);
hr_utility.trace('No opmu for DELETE or ZAP mode');
elsif not p_dt_mode = 'INSERT' then
--
-- For each PPM for the Assignment that has any part of its life within the
-- Assignment Validation Start Date and Validation End Date Range if the
-- following statements returns 'Y' then ERROR because OPMUs are
-- invalidated by the change of PAYROLL.
--
for v_ppm in csr_personal_payment_methods(p_assignment_id,
p_validation_start_date,
p_validation_end_date) loop
--
-- first ensure that there is a valid opmu on the start date of the ppm
-- if there isn't then flag an error
--
begin
select 'Y'
into no_opmu
from sys.dual
where not exists
(select null
from pay_org_pay_method_usages_f opmu,
pay_payrolls_f p
where v_ppm.start_date between opmu.effective_start_date
and opmu.effective_end_date
and opmu.org_payment_method_id =
v_ppm.org_payment_method_id
and opmu.effective_start_date
between p.effective_start_date and p.effective_end_date
and opmu.payroll_id = p.payroll_id
and p.payroll_id = p_payroll_id);
select 'Y'
into no_opmu
from sys.dual
where exists
(select null
from pay_org_pay_method_usages_f opmu,
pay_payrolls_f p
where opmu.effective_start_date <= v_ppm.end_date
and opmu.effective_end_date >= v_ppm.start_date
and opmu.org_payment_method_id =
v_ppm.org_payment_method_id
and opmu.effective_start_date between
p.effective_start_date and p.effective_end_date
and opmu.payroll_id = p.payroll_id
and p.payroll_id = p_payroll_id
and opmu.effective_end_date < v_ppm.end_date
and not exists
(select null
from pay_org_pay_method_usages_f opmu2,
pay_payrolls_f p2
where opmu2.effective_start_date <=
opmu.effective_end_date + 1
and opmu2.effective_end_date >
opmu.effective_end_date
and opmu2.org_payment_method_id =
v_ppm.org_payment_method_id
and opmu2.payroll_id = p2.payroll_id
and p2.payroll_id = p_payroll_id
and opmu2.effective_start_date between
p2.effective_start_date and p2.effective_end_date));
select asg.assignment_id,
ee.element_entry_id,
ee.element_link_id,
ee.original_entry_id,
ee.effective_start_date,
ee.effective_end_date,
ee.target_entry_id,
ee.entry_type,
ee.creator_type,
et.processing_type,
ee.updating_action_id,
ee.updating_action_type,
ee.object_version_number
from per_all_assignments_f asg
, pay_element_entries_f ee
, pay_element_types_f et
where asg.assignment_id = c_assignment_id
and asg.assignment_id = ee.assignment_id
and ee.element_type_id = et.element_type_id
and et.post_termination_rule = 'F'
and c_old_final_process_date between ee.effective_start_date and ee.effective_end_date
and c_old_final_process_date between et.effective_start_date and et.effective_end_date;
pay_element_entry_api.delete_element_entry
(p_validate => false
,p_datetrack_delete_mode => 'ZAP'
,p_effective_date => l_session_date
,p_element_entry_id => r_entry.element_entry_id
,p_object_version_number => r_entry.object_version_number
,p_effective_start_date => l_new_esd
,p_effective_end_date => l_new_eed
,p_delete_warning => l_warnings_exist
);
p_dt_update_mode => 'CORRECTION',
p_dt_delete_mode => null,
p_usage => 'UPDATE',
p_target_entry_id => r_entry.target_entry_id,
p_creator_type => r_entry.creator_type
);
-- means that the entry was updated by UPDATE_RECURRING or STOP_RECURRING formula result rules.
if r_entry.effective_end_date >= p_old_final_process_date
AND (r_entry.updating_action_id IS NULL -- bug 7315564
OR r_entry.updating_action_type = 'U') THEN -- bug 9069114
--Bug Fix 9069114, Modified condition to check element entries
-- NOT end dated by STOP_RECURRING formula result rules
hr_utility.set_location(l_proc,60);
update pay_element_entry_values_f
set effective_end_date = l_new_eed
where element_entry_id = r_entry.element_entry_id
and p_old_final_process_date between effective_start_date and effective_end_date;
update pay_element_entries_f
set effective_end_date = l_new_eed
where element_entry_id = r_entry.element_entry_id
and p_old_final_process_date between effective_start_date and effective_end_date;
update pay_element_entry_values_f
set effective_end_date = l_new_eed
where element_entry_id = r_entry.element_entry_id
and p_old_final_process_date between effective_start_date and effective_end_date
and l_new_eed >= effective_start_date;
update pay_element_entries_f
set effective_end_date = l_new_eed
where element_entry_id = r_entry.element_entry_id
and p_old_final_process_date between effective_start_date and effective_end_date
and l_new_eed >= effective_start_date;