[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(asg.effective_end_date)
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.effective_start_date > p_effective_date;
l_payroll_id_updated boolean;
l_update boolean;
l_update_change_insert boolean;
l_update_override boolean;
select bus.legislation_code
from per_business_groups bus
where bus.business_group_id = l_business_group_id;
select asg.object_version_number
, asg.effective_start_date
, asg.effective_end_date
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date;
select asg.object_version_number
, asg.effective_start_date
, asg.effective_end_date
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.effective_start_date >= l_effective_date;
select null
from pay_assignment_link_usages_f alu
where alu.assignment_id = p_assignment_id
for update nowait;
select null
from pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
for update nowait;
select eev.element_entry_id
from pay_element_entry_values_f eev,
pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and eev.element_entry_id = ele.element_entry_id
for update nowait; */
select null
from pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date
for update nowait;
select eev.element_entry_id
from pay_element_entry_values_f eev,
pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and eev.element_entry_id = ele.element_entry_id
and p_effective_date between ele.effective_start_date and ele.effective_end_date
for update nowait;
select null
from per_pay_proposals pyp
where pyp.assignment_id = p_assignment_id
for update nowait;
select asa.assignment_action_id
from pay_assignment_actions asa
where asa.assignment_id = p_assignment_id
for update nowait;
select pyp.pay_proposal_id, pyp.object_version_number
from per_pay_proposals pyp
where pyp.assignment_id = p_assignment_id
and pyp.change_date > p_actual_termination_date
order by pyp.change_date desc;
select ppc.component_id, ppc.object_version_number
from per_pay_proposal_components ppc
where ppc.pay_proposal_id = l_proposal_id;
,p_update => l_update
,p_update_override => l_update_override
,p_update_change_insert => l_update_change_insert
);
if l_update_change_insert then
--
-- This is the case where there is a future dated assignment and
-- we need to insert a record betwen ATD+1 and that future change
-- with a TERM status. We need 'CORRECTION' of future records to
-- have the right status.
--
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
elsif l_update then
--
l_datetrack_mode := 'UPDATE';
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => p_actual_termination_date + 1
,p_datetrack_mode => l_datetrack_mode
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => c_asg_rec.object_version_number
,p_effective_date => c_asg_rec.effective_start_date
,p_datetrack_mode => 'CORRECTION'
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
delete
from per_pay_proposals pyp
where pyp.pay_proposal_id = rec_pay_prop.pay_proposal_id;
select pbg.business_group_id, pbg.legislation_code
from per_business_groups_perf pbg
where pbg.business_group_id = (select distinct asg.business_group_id from
per_assignments_f asg
where asg.assignment_id = p_assignment_id);
select null
from pay_cost_allocations_f csa
where csa.assignment_id = p_assignment_id
for update nowait;
select null
from pay_assignment_link_usages_f alu
where alu.assignment_id = p_assignment_id
for update nowait;
select null
from pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
for update nowait;
select eev.element_entry_id
from pay_element_entry_values_f eev,
pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and eev.element_entry_id = ele.element_entry_id
for update nowait; */
select null
from pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date
for update nowait;
select eev.element_entry_id
from pay_element_entry_values_f eev,
pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and eev.element_entry_id = ele.element_entry_id
and p_effective_date between ele.effective_start_date and ele.effective_end_date
for update nowait;
select null
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
for update nowait;
select null
from pay_personal_payment_methods_f ppm
where ppm.assignment_id = p_assignment_id
for update nowait;
select asa.assignment_action_id
from pay_assignment_actions asa
where asa.assignment_id = p_assignment_id
for update nowait;
select null
from per_secondary_ass_statuses sas
where sas.assignment_id = p_assignment_id
for update nowait;
select null
from per_pay_proposals pyp
where pyp.assignment_id = p_assignment_id
for update nowait;
select personal_payment_method_id,object_version_number,effective_start_date
from pay_personal_payment_methods_f
where assignment_id = p_assignment_id
and effective_start_date > p_final_process_date;
select personal_payment_method_id,object_version_number
from pay_personal_payment_methods_f
where assignment_id = p_assignment_id
and p_final_process_date between effective_start_date
and effective_end_date;
select assignment_budget_value_id
from per_assignment_budget_values_f
where assignment_id = p_assignment_id
and p_final_process_date between effective_start_date
and effective_end_date;
select pgr.grade_or_spinal_point_id
from pay_grade_rules_f pgr
where pgr.grade_or_spinal_point_id = p_assignment_id
and rate_type = 'A'
and p_final_process_Date between pgr.effective_start_date
and pgr.effective_end_date;
update per_secondary_ass_statuses sas
set sas.end_date = p_final_process_date
where sas.assignment_id = p_assignment_id
and sas.end_date IS NULL;
delete per_secondary_ass_statuses sas
where sas.assignment_id = p_assignment_id
and sas.start_date > p_final_process_date;
update pay_cost_allocations_f pca
set pca.effective_end_date = p_final_process_date
where pca.assignment_id = p_assignment_id
and p_final_process_date between pca.effective_start_date
and pca.effective_end_date;
delete pay_cost_allocations_f pca
where pca.assignment_id = p_assignment_id
and pca.effective_start_date > p_final_process_date;
update per_spinal_point_placements_f spp
set spp.effective_end_date = p_final_process_date
where spp.assignment_id = p_assignment_id
and p_final_process_date between spp.effective_start_date
and spp.effective_end_date;
delete per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date > p_final_process_date;
update per_assignment_budget_values_f abv
set abv.effective_end_date = p_final_process_date
where abv.assignment_id = p_assignment_id
and p_final_process_date between abv.effective_start_date
and abv.effective_end_date;
delete per_assignment_budget_values_f abv
where abv.assignment_id = p_assignment_id
and abv.effective_start_date > p_final_process_date;
update pay_grade_rules_f pgr
set pgr.effective_end_date = p_final_process_date
where pgr.grade_or_spinal_point_id = p_assignment_id
and pgr.rate_type = 'A'
and p_final_process_date between pgr.effective_start_date
and pgr.effective_end_date;
delete pay_grade_rules_f pgr
where grade_or_spinal_point_Id = p_assignment_id
and pgr.rate_type = 'A'
and pgr.effective_start_Date > p_final_process_date;
,p_datetrack_mode => 'DELETE');
,p_datetrack_mode => 'DELETE'
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
l_payroll_id_updated boolean;
l_update boolean;
l_update_change_insert boolean;
l_update_override boolean;
select bus.legislation_code
from per_business_groups bus
where bus.business_group_id = l_business_group_id;
select asg.object_version_number
, asg.effective_start_date
, asg.effective_end_date
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and l_effective_date between asg.effective_start_date
and asg.effective_end_date;
select asg.object_version_number
, asg.effective_start_date
, asg.effective_end_date
from per_assignments_f asg
where asg.assignment_id = p_assignment_id
and asg.effective_start_date >= l_effective_date;
select null
from pay_assignment_link_usages_f alu
where alu.assignment_id = p_assignment_id
for update nowait;
select null
from pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
for update nowait;
select eev.element_entry_id
from pay_element_entry_values_f eev,
pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and eev.element_entry_id = ele.element_entry_id
for update nowait;
select null
from pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date
for update nowait;
select eev.element_entry_id
from pay_element_entry_values_f eev,
pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and eev.element_entry_id = ele.element_entry_id
and p_effective_date between ele.effective_start_date and ele.effective_end_date
for update nowait;
select asa.assignment_action_id
from pay_assignment_actions asa
where asa.assignment_id = p_assignment_id
for update nowait;
select null
from per_pay_proposals pyp
where pyp.assignment_id = p_assignment_id
for update nowait;
select pyp.pay_proposal_id, pyp.object_version_number, pyp.business_group_id
from per_pay_proposals pyp
where pyp.assignment_id = p_assignment_id
and pyp.change_date > p_actual_termination_date
order by pyp.change_date desc;
select ppc.component_id, ppc.object_version_number
from per_pay_proposal_components ppc
where ppc.pay_proposal_id = l_proposal_id;
,p_update => l_update
,p_update_override => l_update_override
,p_update_change_insert => l_update_change_insert
);
,p_update => l_update
,p_update_override => l_update_override
,p_update_change_insert => l_update_change_insert
);
,p_update => l_update
,p_update_override => l_update_override
,p_update_change_insert => l_update_change_insert
);
if l_update_change_insert then
--
-- This is the case where there is a future dated assignment and
-- we need to insert a record betwen ATD+1 and that future change
-- with a TERM status. We need 'CORRECTION' of future records to
-- have the right status.
--
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
elsif l_update then
--
l_datetrack_mode := 'UPDATE';
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => p_actual_termination_date + 1
,p_datetrack_mode => l_datetrack_mode
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => c_asg_rec.object_version_number
,p_effective_date => c_asg_rec.effective_start_date
,p_datetrack_mode => 'CORRECTION'
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
delete
from per_pay_proposals pyp
where pyp.pay_proposal_id = rec_pay_prop.pay_proposal_id;
hr_maintain_proposal_api.delete_salary_proposal(
p_pay_proposal_id => rec_pay_prop.pay_proposal_id,
p_business_group_id => rec_pay_prop.business_group_id,
p_object_version_number => rec_pay_prop.object_version_number,
p_validate => NULL,
p_salary_warning => l_pay_proposal_warning
);
procedure delete_first_spp
(p_effective_date in date
,p_assignment_id in number
,p_validation_start_date in date
,p_validation_end_date in date
,p_future_spp_warning out nocopy boolean) is
l_effective_end_date date;
l_update number;
select spp.placement_id
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and p_validation_start_date between spp.effective_start_date
and spp.effective_end_date;
fetch csr_grade_step into l_update;
select placement_id,object_version_number,effective_end_date
into l_placement_id,l_object_version_number,l_effective_end_date
from per_spinal_point_placements_f
where assignment_id = p_assignment_id
and p_validation_start_date between effective_start_date
and effective_end_date;
l_datetrack_mode := 'DELETE_NEXT_CHANGE';
hr_sp_placement_api.delete_spp
(p_effective_date => p_validation_start_date
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
select effective_end_date
into l_effective_end_date
from per_spinal_point_placements_f
where placement_id = l_placement_id
and p_validation_start_date between effective_start_date
and effective_end_date;
delete from per_spinal_point_placements_f
where assignment_id = p_assignment_id
and placement_id = l_placement_id
and effective_start_date = p_validation_start_date;
end delete_first_spp;
select bus.location_id
, bus.default_end_time
, bus.default_start_time
, fnd_number.canonical_to_number(bus.working_hours)
, bus.frequency
, bus.legislation_code
from per_business_groups bus
where bus.business_group_id = p_business_group_id;
,p_dt_mode => 'INSERT'
,p_validation_start_date => l_effective_start_date
,p_validation_end_date => l_effective_end_date
,p_entries_changed => l_entries_changed
);
l_delete_asg_budgets boolean;
select pbg.business_group_id, pbg.legislation_code
from per_business_groups_perf pbg
where pbg.business_group_id = (select distinct asg.business_group_id from
per_assignments_f asg
where asg.assignment_id = p_assignment_id);
select null
from pay_cost_allocations_f csa
where csa.assignment_id = p_assignment_id
for update nowait;
select null
from pay_assignment_link_usages_f alu
where alu.assignment_id = p_assignment_id
for update nowait;
select null
from pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
for update nowait;
select eev.element_entry_id
from pay_element_entry_values_f eev,
pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and eev.element_entry_id = ele.element_entry_id
for update nowait; */
select null
from pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date
for update nowait;
select eev.element_entry_id
from pay_element_entry_values_f eev,
pay_element_entries_f ele
where ele.assignment_id = p_assignment_id
and eev.element_entry_id = ele.element_entry_id
and p_effective_date between ele.effective_start_date and ele.effective_end_date
for update nowait;
select null
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
for update nowait;
select null
from pay_personal_payment_methods_f ppm
where ppm.assignment_id = p_assignment_id
for update nowait;
select null
from per_secondary_ass_statuses sas
where sas.assignment_id = p_assignment_id
for update nowait;
select null
from per_pay_proposals pyp
where pyp.assignment_id = p_assignment_id
for update nowait;
select personal_payment_method_id,object_version_number,effective_start_date
from pay_personal_payment_methods_f
where assignment_id = p_assignment_id
and effective_start_date > p_final_process_date;
select personal_payment_method_id,object_version_number
from pay_personal_payment_methods_f
where assignment_id = p_assignment_id
and p_final_process_date between effective_start_date
and effective_end_date;
select asa.assignment_action_id
from pay_assignment_actions asa
where asa.assignment_id = p_assignment_id
for update nowait;
select assignment_budget_value_id
from per_assignment_budget_values_f
where assignment_id = p_assignment_id
and p_final_process_date between effective_start_date
and effective_end_date;
update per_secondary_ass_statuses sas
set sas.end_date = p_final_process_date
where sas.assignment_id = p_assignment_id
and sas.end_date IS NULL;
delete per_secondary_ass_statuses sas
where sas.assignment_id = p_assignment_id
and sas.start_date > p_final_process_date;
update pay_cost_allocations_f pca
set pca.effective_end_date = p_final_process_date
where pca.assignment_id = p_assignment_id
and p_final_process_date between pca.effective_start_date
and pca.effective_end_date;
delete pay_cost_allocations_f pca
where pca.assignment_id = p_assignment_id
and pca.effective_start_date > p_final_process_date;
update per_spinal_point_placements_f spp
set spp.effective_end_date = p_final_process_date
where spp.assignment_id = p_assignment_id
and p_final_process_date between spp.effective_start_date
and spp.effective_end_date;
delete per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date > p_final_process_date;
update per_assignment_budget_values_f abv
set abv.effective_end_date = p_final_process_date
where abv.assignment_id = p_assignment_id
and p_final_process_date between abv.effective_start_date
and abv.effective_end_date;
delete per_assignment_budget_values_f abv
where abv.assignment_id = p_assignment_id
and abv.effective_start_date > p_final_process_date;
,p_datetrack_mode => 'DELETE');
,p_datetrack_mode => 'DELETE'
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
);
SELECT spp.placement_id,
spp.object_version_number,
spp.effective_start_date
FROM per_spinal_point_placements_f spp
WHERE spp.assignment_id = p_assignment_id
ORDER BY placement_id;
hr_sp_placement_api.delete_spp
(p_effective_date => c_spp_record.effective_start_date
,p_datetrack_mode => hr_api.g_zap
,p_placement_id => c_spp_record.placement_id
,p_object_version_number => c_spp_record.object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
PROCEDURE spp_update_change_insert
(p_assignment_id IN per_assignments_f.assignment_id%TYPE
,p_placement_id IN per_spinal_point_placements_f.placement_id%TYPE
,p_validation_start_date IN DATE
,p_validation_end_date IN DATE
,p_spp_eff_start_date IN DATE
,p_datetrack_mode IN OUT NOCOPY VARCHAR2
,p_object_version_number IN OUT NOCOPY NUMBER) IS
--
-- Declare Local Variables
--
l_proc VARCHAR2(72) := g_package||'spp_update_change_insert';
SELECT spp.placement_id
FROM per_spinal_point_placements_f spp
WHERE spp.assignment_id = p_assignment_id
AND spp.effective_start_date > p_validation_start_date;
CURSOR csr_update_change_insert_rows IS
SELECT spp.placement_id,
spp.object_version_number,
spp.effective_start_date
FROM per_spinal_point_placements_f spp
WHERE effective_start_date BETWEEN p_spp_eff_start_date
AND p_validation_end_date
AND effective_end_date < p_validation_end_date
AND assignment_id = p_assignment_id
ORDER BY effective_start_date DESC;
FOR c1_rec IN csr_update_change_insert_rows LOOP
--
hr_utility.set_location(l_proc||'/'||c1_rec.object_version_number,30);
hr_sp_placement_api.delete_spp
(p_effective_date => c1_rec.effective_start_date
,p_datetrack_mode => hr_api.g_delete_next_change
,p_placement_id => p_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
l_datetrack_mode := 'UPDATE';
END spp_update_change_insert;
select 'Y'
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date > p_validation_start_date
and spp.placement_id <> p_placement_id;
select paa.effective_start_date,
paa.effective_end_date,
paa.grade_id
from per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and paa.effective_end_date >= p_validation_start_date - 1
order by paa.effective_start_date;
select spp.placement_id,
spp.object_version_number,
spp.effective_start_date,
spp.effective_end_date
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date >= l_asg_eff_start_date
and spp.effective_end_date <= l_asg_eff_end_date;
select pgs.grade_id,
spp.placement_id,
spp.effective_start_date,
spp.effective_end_date
from per_grade_spines_f pgs,
per_spinal_point_steps_f sps,
per_spinal_point_placements_f spp
where sps.grade_spine_id = pgs.grade_spine_id
and spp.step_id = sps.step_id
and pgs.parent_spine_id = spp.parent_spine_id
and spp.assignment_id = p_assignment_id
and spp.effective_start_date >= l_asg_eff_start_date
and spp.effective_end_date <= l_asg_eff_end_date;
select pgs.grade_id
from per_grade_spines_f pgs,
per_spinal_point_steps_f sps,
per_spinal_point_placements_f spp
where sps.grade_spine_id = pgs.grade_spine_id
and spp.step_id = sps.step_id
and pgs.parent_spine_id = spp.parent_spine_id
and spp.assignment_id = p_assignment_id
and spp.effective_start_date between l_asg_eff_start_date
and l_asg_eff_end_date
and spp.effective_end_date > l_asg_eff_end_date;
select spp.placement_id,
spp.effective_start_date,
spp.effective_end_date
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date >= p_validation_start_date;
delete from per_spinal_point_placements_f spp
where spp.placement_id = asg_spp_rec.placement_id
and spp.effective_start_date = asg_spp_rec.effective_start_date
and spp.effective_end_date = asg_spp_rec.effective_end_date;
delete from per_spinal_point_placements_f spp
where spp.placement_id = rec_spp_placement.placement_id
and spp.effective_start_date = rec_spp_placement.effective_start_date
and spp.effective_end_date = rec_spp_placement.effective_end_date;
if p_datetrack_mode = hr_api.g_delete_next_change then
--
open csr_asg_spp_error(csr_asg_rec.effective_start_date,
csr_asg_rec.effective_end_date);
hr_api.g_update_change_insert) then
--
for spp_rec in csr_spp_records loop
--
delete from per_spinal_point_placements_f spp
where spp.placement_id = spp_rec.placement_id
and spp.effective_start_date = spp_rec.effective_start_date
and spp.effective_end_date = spp_rec.effective_end_date;
procedure delete_next_change_spp
(p_assignment_id in per_all_assignments_f.assignment_id%Type
,p_placement_id in per_spinal_point_placements_f.placement_id%Type
,p_grade_id in per_grade_spines_f.grade_id%Type
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
,p_del_end_future_spp out nocopy boolean) is
--
-- Declare Local Variables
l_placement_id number;
l_proc varchar(72) := g_package||'delete_next_change_spp';
cursor csr_update_change_rows is
select spp.placement_id,
spp.effective_start_date,
spp.effective_end_date,
spp.object_version_number
from per_spinal_point_placements_f spp
where spp.effective_end_date between p_validation_start_date - 1
and p_validation_end_date
and spp.effective_end_date < p_validation_end_date
and spp.assignment_id = p_assignment_id
order by spp.effective_start_date desc;
select pgs.grade_id
from per_grade_spines_f pgs,
per_spinal_point_steps_f sps,
per_spinal_point_placements_f spp
where sps.grade_spine_id = pgs.grade_spine_id
and spp.step_id = sps.step_id
and pgs.parent_spine_id = spp.parent_spine_id
and spp.assignment_id = p_assignment_id
and spp.effective_start_date between p_validation_start_date
and p_validation_end_date;
select spp.placement_id,spp.effective_start_date
,spp.effective_end_date, spp.object_version_number
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date < p_validation_end_date
and spp.effective_end_date > p_validation_end_date;
select 'Y'
from per_spinal_point_placements_f
where placement_id = p_placement_id
and effective_start_date > p_date;
l_datetrack_mode := hr_api.g_update;
l_datetrack_mode := hr_api.g_update_change_insert;
hr_sp_placement_api.update_spp(
p_effective_date => p_validation_end_date+1
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
for csr_rec in csr_update_change_rows loop
--
if g_debug then
hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 30);
hr_sp_placement_api.delete_spp(
p_effective_date => csr_rec.effective_start_date
,p_datetrack_mode => hr_api.g_delete_next_change
,p_placement_id => csr_rec.placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
end delete_next_change_spp;
select spp.placement_id,
spp.object_version_number,
spp.effective_start_date,
spp.effective_end_date
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date < p_validation_start_date
order by effective_start_date desc;
hr_sp_placement_api.delete_spp(
p_effective_date => l_spp_eff_start_date
,p_datetrack_mode => p_datetrack_mode
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
procedure update_override_spp
(p_assignment_id in per_all_assignments_f.assignment_id%Type
,p_placement_id in per_spinal_point_placements_f.placement_id%Type
,p_datetrack_mode in varchar2
,p_validation_start_date in date
,p_validation_end_date in date
,p_spp_eff_start_date in date
,p_grade_id in number
,p_step_id in number
,p_object_version_number in number
,p_current_spp_exist in boolean
,p_pay_scale_defined in boolean
,p_del_end_future_spp out nocopy boolean) is
--
-- declare local variables
l_effective_start_date date;
l_proc varchar2(72) := g_package||'update_override_spp';
select paa.grade_id
from per_all_assignments_f paa
where paa.assignment_id = p_assignment_id
and p_validation_start_date - 1 between paa.effective_start_date
and paa.effective_end_date
order by paa.effective_start_date;
select spp.placement_id
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date > p_validation_start_date;
select spp.object_version_number,
spp.effective_start_date,
spp.effective_end_date
from per_spinal_point_placements_f spp
where spp.placement_id = l_placement_id
and p_validation_start_date - 1 between spp.effective_start_date
and spp.effective_end_date;
select spp.object_version_number
from per_spinal_point_placements_f spp
where spp.placement_id = l_placement_id
and spp.effective_start_date < p_validation_start_date
order by effective_start_date desc;
cursor csr_update_change_rows is
select spp.object_version_number,
spp.effective_start_date
from per_spinal_point_placements_f spp
where spp.effective_start_date between p_spp_eff_start_date
and p_validation_end_date
and spp.effective_end_date < p_validation_end_date
and spp.assignment_id = p_assignment_id
order by spp.effective_start_date desc;
l_datetrack_mode := hr_api.g_update;
hr_sp_placement_api.update_spp(
p_effective_date => p_validation_start_date
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => p_placement_id
,p_object_version_number => l_object_version_number
,p_step_id => p_step_id
,p_auto_increment_flag => 'N'
,p_reason => ''
,p_increment_number => NULL
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
for csr_rec in csr_update_change_rows loop
--
if g_debug then
hr_utility.set_location(l_proc||'/'||csr_rec.object_version_number, 90);
hr_sp_placement_api.delete_spp(
p_effective_date => csr_rec.effective_start_date
,p_datetrack_mode => hr_api.g_delete_next_change
,p_placement_id => p_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_sp_placement_api.update_spp(
p_effective_date => p_validation_start_date
,p_datetrack_mode => p_datetrack_mode
,p_placement_id => p_placement_id
,p_object_version_number => l_object_version_number
,p_step_id => p_step_id
,p_auto_increment_flag => 'N'
,p_reason => ''
,p_increment_number => NULL
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_sp_placement_api.delete_spp(
p_effective_date => p_validation_start_date - 1
,p_datetrack_mode => hr_api.g_delete_next_change
,p_placement_id => p_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_sp_placement_api.delete_spp(
p_effective_date => p_validation_start_date - 1
,p_datetrack_mode => hr_api.g_delete
,p_placement_id => p_placement_id
,p_object_version_number => l_previous_ovn
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
end update_override_spp;
select spp.placement_id
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.placement_id = p_placement_id
and spp.effective_start_date > p_validation_start_date;
select spp.object_version_number
from per_spinal_point_placements_f spp
where spp.placement_id = p_placement_id
and spp.effective_start_date < p_validation_start_date
order by effective_start_date desc;
hr_sp_placement_api.delete_spp(
p_effective_date => p_validation_start_date
,p_datetrack_mode => hr_api.g_future_change
,p_placement_id => p_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_sp_placement_api.delete_spp(
p_effective_date => p_validation_start_date - 1
,p_datetrack_mode => hr_api.g_delete
,p_placement_id => p_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
select spp.placement_id,spp.effective_start_date
,spp.effective_end_date, spp.object_version_number
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date < p_validation_start_date
-- and spp.effective_end_date > p_validation_start_date;
select spp.placement_id,spp.effective_start_date
,spp.effective_end_date, spp.object_version_number
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date < p_validation_end_date
and spp.effective_end_date > p_validation_end_date;
select spp.placement_id,spp.effective_start_date
,spp.effective_end_date, spp.object_version_number
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date between p_validation_start_date and p_validation_end_date
order by effective_end_date;
select placement_id, effective_start_date,
effective_end_date, object_version_number
from per_spinal_point_placements_f
where placement_id = p_placement_id
-- and effective_start_date >= p_date;--fix for bug 5067855 .
select 'Y'
from per_spinal_point_placements_f
where placement_id = p_placement_id
and effective_start_date > p_date;
l_datetrack_mode := hr_api.g_update;
l_datetrack_mode := hr_api.g_update_change_insert;
hr_sp_placement_api.update_spp(
p_effective_date => p_validation_end_date+1
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
l_datetrack_mode := hr_api.g_update;
l_datetrack_mode := hr_api.g_update_change_insert;
hr_sp_placement_api.update_spp(
p_effective_date => p_validation_start_date
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => l_placement_id
,p_step_id => l_min_step_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_utility.set_location('performing delete_spp',70);
hr_sp_placement_api.delete_spp(
p_effective_date => l_effective_date
,p_datetrack_mode => hr_api.g_delete_next_change
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_sp_placement_api.update_spp(
p_effective_date => p_validation_start_date
,p_datetrack_mode => hr_api.g_correction
,p_placement_id => l_placement_id
,p_step_id => l_min_step_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
PROCEDURE delete_future_spp_records
(p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
,p_datetrack_mode IN VARCHAR2
,p_placement_id IN per_spinal_point_placements_f.placement_id%TYPE
,p_object_version_number IN per_spinal_point_placements_f.object_version_number%TYPE
,p_effective_date IN DATE) IS
--
-- Declare Local Variables
--
l_proc VARCHAR2(72) := g_package||'delete_future_spp_records';
SELECT spp.placement_id,
spp.object_version_number,
spp.effective_start_date
FROM per_spinal_point_placements_f spp
WHERE spp.assignment_id = p_assignment_id
AND spp.effective_start_date > p_effective_date
AND spp.placement_id <> p_placement_id
ORDER BY placement_id;
SELECT spp.placement_id
FROM per_spinal_point_placements_f spp
WHERE spp.placement_id = p_placement_id
AND spp.effective_start_date < p_effective_date;
SELECT spp.object_version_number,
spp.effective_end_date
FROM per_spinal_point_placements_f spp
WHERE spp.placement_id = p_placement_id
AND spp.effective_start_date < p_effective_date
ORDER BY spp.effective_end_date desc;
hr_sp_placement_api.delete_spp
(p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_zap
,p_placement_id => p_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_sp_placement_api.delete_spp
(p_effective_date => l_previous_end_date
,p_datetrack_mode => hr_api.g_delete
,p_placement_id => p_placement_id
,p_object_version_number => l_previous_ovn
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_sp_placement_api.delete_spp
(p_effective_date => p_effective_date -1
,p_datetrack_mode => hr_api.g_delete
,p_placement_id => p_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
hr_sp_placement_api.delete_spp
(p_effective_date => c_future_spp.effective_start_date
,p_datetrack_mode => hr_api.g_zap
,p_placement_id => c_future_spp.placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
END delete_future_spp_records;
,p_spp_delete_warning out nocopy boolean) is
--
-- Declare local variables
l_effective_start_date date;
select spp.placement_id
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
for update nowait;
select spp.placement_id
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.effective_start_date > p_validation_start_date;
select spp.placement_id
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and spp.placement_id = p_placement_id
and spp.effective_start_date > p_validation_start_date;
select spp.placement_id,
spp.object_version_number,
spp.effective_start_date,
spp.effective_end_date
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id
and p_validation_start_date between spp.effective_start_date
and spp.effective_end_date;
select sps.step_id
from per_grade_spines_f pgs,
per_spinal_point_steps_f sps
where sps.grade_spine_id = pgs.grade_spine_id
and p_validation_start_date between sps.effective_start_date
and sps.effective_end_date
and pgs.grade_id = p_grade_id
and p_validation_start_date between pgs.effective_start_date
and pgs.effective_end_date
and sps.sequence in (
select min(sps2.sequence)
from per_spinal_point_steps_f sps2
where sps2.grade_spine_id = pgs.grade_spine_id
and p_validation_start_date between sps2.effective_start_date
and sps2.effective_end_date);
select grade_spine_id
from per_grade_spines_f pgs
where grade_id = p_grade_id;
select min(spp.effective_start_date)
from per_spinal_point_placements_f spp
where spp.assignment_id = p_assignment_id;
if p_datetrack_mode = hr_api.g_delete_next_change then
--
delete_next_change_spp(
p_assignment_id => p_assignment_id
,p_placement_id => l_placement_id
,p_grade_id => p_grade_id
,p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
,p_del_end_future_spp => l_del_end_future_spp);
if p_datetrack_mode = hr_api.g_update_override then
--
update_override_spp(
p_assignment_id => p_assignment_id
,p_placement_id => l_placement_id
,p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
,p_spp_eff_start_date => l_spp_eff_start_date
,p_grade_id => p_grade_id
,p_step_id => l_min_step_id
,p_object_version_number => l_object_version_number
,p_current_spp_exist => l_current_spp_exist
,p_pay_scale_defined => l_pay_scale_defined
,p_del_end_future_spp => l_del_end_future_spp);
hr_api.g_update_change_insert) then
--
If p_grade_id is not null then
--
correction_spp(
p_assignment_id => p_assignment_id
,p_placement_id => l_placement_id
,p_grade_id => p_grade_id
,p_min_step_id => l_min_step_id
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
,p_del_end_future_spp => l_del_end_future_spp);
elsif p_datetrack_mode = hr_api.g_update then
-- Check for future records SPP records if any for the same
-- placement id
open csr_spp_future_records(l_placement_id);
hr_sp_placement_api.delete_spp(
p_effective_date => p_validation_start_date
,p_datetrack_mode => hr_api.g_future_change
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
l_datetrack_mode := hr_api.g_update_override;
l_datetrack_mode := hr_api.g_update;
elsif p_datetrack_mode = hr_api.g_delete then
--
l_datetrack_mode := hr_api.g_delete;
if p_datetrack_mode in (hr_api.g_update, hr_api.g_delete) then
-- Check that the effective date of the process is not less than the min
-- effective start date for the spp record for the assignment
-- If it is then the process will not be able to update the current step
-- as there is none so raise an error
open csr_min_spp_date;
if not l_pay_scale_defined and p_datetrack_mode = hr_api.g_update then
--
close_spp_records(
p_assignment_id => p_assignment_id
,p_placement_id => l_placement_id
,p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date
,p_object_version_number => l_object_version_number
,p_current_spp_exist => l_current_spp_exist
,p_del_end_future_spp => l_del_end_future_spp);
hr_sp_placement_api.update_spp(
p_effective_date => p_validation_start_date
,p_datetrack_mode => l_datetrack_mode
,p_placement_id => l_placement_id
,p_object_version_number => l_object_version_number
,p_step_id => l_min_step_id
,p_auto_increment_flag => 'N'
,p_reason => ''
,p_increment_number => NULL
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
p_spp_delete_warning := l_del_end_future_spp;
procedure update_status_type_cwk_asg
(p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_change_reason in varchar2 default hr_api.g_varchar2
--
,p_object_version_number in out nocopy number
,p_expected_system_status in varchar2
,p_assignment_status_type_id in number default hr_api.g_number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_effective_end_date per_assignments_f.effective_end_date%TYPE;
l_payroll_id_updated boolean;
g_package||'update_status_type_cwk_asg';
select asg.assignment_type
, asg.business_group_id
, bus.legislation_code
from per_assignments_f asg
, per_business_groups_perf bus
where asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and bus.business_group_id+0 = asg.business_group_id;
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_update_mode
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
end update_status_type_cwk_asg;
procedure update_status_type_emp_asg
(p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_change_reason in varchar2 default hr_api.g_varchar2
--
,p_object_version_number in out nocopy number
,p_expected_system_status in varchar2
,p_assignment_status_type_id in number default hr_api.g_number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
--
-- Declare cursors and local variables
--
-- Out variables
--
l_effective_end_date per_assignments_f.effective_end_date%TYPE;
l_payroll_id_updated boolean;
g_package||'update_status_type_emp_asg';
select asg.assignment_type
, asg.business_group_id
, bus.legislation_code
from per_assignments_f asg
, per_business_groups_perf bus
where asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and bus.business_group_id+0 = asg.business_group_id;
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_update_mode
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
end update_status_type_emp_asg;
select bus.location_id
, bus.default_end_time
, bus.default_start_time
, fnd_number.canonical_to_number(bus.working_hours)
, bus.frequency
, bus.legislation_code
from per_business_groups bus
where bus.business_group_id = p_business_group_id;
select bus.location_id
, bus.default_start_time
, bus.default_end_time
, fnd_number.canonical_to_number(bus.working_hours)
, bus.frequency
, bus.legislation_code
from per_business_groups bus
where bus.business_group_id = p_business_group_id;
select location_id,people_group_id,recruiter_id,job_id,position_id,grade_id,organization_id
from PER_ALL_VACANCIES
where vacancy_id = p_vacancy_id
and p_effective_date between date_from
and nvl(date_to, hr_api.g_eot);
procedure update_status_type_apl_asg
(p_effective_date in date
,p_datetrack_update_mode in varchar2
,p_assignment_id in number
,p_object_version_number in out nocopy number
,p_expected_system_status in varchar2
,p_assignment_status_type_id in number default hr_api.g_number
,p_change_reason in varchar2 default hr_api.g_varchar2
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
) is
Cursor csr_vacancy_id is
Select vacancy_id
From per_all_assignments_f
Where assignment_id = p_assignment_id
And p_effective_date between effective_start_date and effective_end_date;
l_payroll_id_updated boolean;
g_package||'update_status_type_apl_asg';
select asg.assignment_type
, asg.person_id
, asg.business_group_id
, bus.legislation_code
from per_assignments_f asg
, per_business_groups_perf bus
where asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and bus.business_group_id+0 = asg.business_group_id;
select ast.per_system_status
from per_assignment_status_types ast
where ast.assignment_status_type_id = p_assignment_status_type_id;
select ast.per_system_status
from per_assignment_status_types ast
where ast.assignment_status_type_id = p_assignment_status_type_id;
select person_id,business_group_id,per_information1
from per_all_people_f
where person_id in (select person_id from per_all_assignments_f where assignment_id= p_assignment_id
and p_effective_date between effective_start_date and effective_end_date)
and p_effective_date between effective_start_date and effective_end_date;
select 1 from
per_periods_of_service
where person_id in (select person_id from per_all_assignments_f
where assignment_id= p_assignment_id
and p_effective_date between effective_start_date and effective_end_date);
,p_payroll_id_updated => l_payroll_id_updated
,p_other_manager_warning => l_other_manager_warning
,p_no_managers_warning => l_no_managers_warning
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_validation_start_date => l_validation_start_date
,p_validation_end_date => l_validation_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => p_datetrack_update_mode
,p_validate => FALSE
,p_hourly_salaried_warning => l_hourly_salaried_warning
);
IRC_ASG_STATUS_API.dt_update_irc_asg_status
(p_assignment_id => p_assignment_id
, p_datetrack_mode => 'INSERT'
, p_assignment_status_type_id => l_assignment_status_type_id
, p_status_change_reason => p_change_reason
, p_status_change_date => p_effective_date
, p_assignment_status_id => l_assignment_status_id
, p_object_version_number => l_asg_status_ovn);
end update_status_type_apl_asg;
procedure irc_delete_assgt_checks
(p_assignment_id in per_all_assignments_f.assignment_id%Type
,p_datetrack_mode in varchar2
,p_validation_start_date in date )
is
--
cursor irc_asgt_statuses is
SELECT ASSIGNMENT_STATUS_ID,OBJECT_VERSION_NUMBER
FROM IRC_ASSIGNMENT_STATUSES
WHERE ASSIGNMENT_ID = p_assignment_id
AND TRUNC(STATUS_CHANGE_DATE)= p_validation_start_date;
SELECT 'Y'
FROM IRC_OFFERS
WHERE ASSIGNMENT_ID = p_assignment_id; */
l_proc varchar2(72) := g_package||'irc_delete_assgt_checks';
procedure ben_delete_assgt_checks
(p_assignment_id in per_all_assignments_f.assignment_id%Type
,p_datetrack_mode in varchar2
,p_life_events_exists out NOCOPY boolean)
is
--
cursor ben_le_checks is
SELECT 'Y'
FROM BEN_PER_IN_LER
WHERE ASSIGNMENT_ID = p_assignment_id
AND PER_IN_LER_STAT_CD = 'STRTD';
l_proc varchar2(72) := g_package||'ben_delete_assgt_checks';
procedure pre_delete
(p_rec in per_asg_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date,
p_org_now_no_manager_warning out nocopy boolean,
p_loc_change_tax_issues OUT nocopy boolean,
p_delete_asg_budgets OUT nocopy boolean,
p_element_salary_warning OUT nocopy boolean,
p_element_entries_warning OUT nocopy boolean,
p_spp_warning OUT nocopy boolean,
p_cost_warning OUT nocopy boolean,
p_life_events_exists OUT nocopy boolean,
p_cobra_coverage_elements OUT nocopy boolean,
p_assgt_term_elements OUT nocopy boolean,
---
p_new_prim_ass_id OUT nocopy number,
p_prim_change_flag OUT nocopy varchar2,
p_new_end_date OUT nocopy date,
p_new_primary_flag OUT nocopy varchar2,
p_s_pay_id OUT nocopy number,
p_cancel_atd OUT nocopy date,
p_cancel_lspd OUT nocopy date,
p_reterm_atd OUT nocopy date,
p_reterm_lspd OUT nocopy date,
---
p_appl_asg_new_end_date OUT nocopy date
)
is
l_sys_status_type varchar2(100);
select per_system_status
from per_assignment_status_types
where assignment_status_type_id = per_asg_shd.g_old_rec.assignment_status_type_id;
select sequence
from per_spinal_point_steps_f
where step_id = per_asg_shd.g_old_rec.special_ceiling_step_id
and p_effective_date between effective_start_date and effective_end_date;
select rowid from per_all_assignments_f
where assignment_id = p_rec.assignment_id
and p_effective_date between effective_start_date and effective_end_date;
select location_code
from hr_locations
where location_id = per_asg_shd.g_old_rec.location_id;
select bus.legislation_code
from per_business_groups bus
where bus.business_group_id = per_asg_shd.g_old_rec.business_group_id;
l_proc varchar2(72) := g_package||'pre_delete';
irc_delete_assgt_checks
(p_assignment_id => p_rec.assignment_id
,p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date );
ben_delete_assgt_checks
(p_assignment_id => p_rec.assignment_id
,p_datetrack_mode => p_datetrack_mode
,p_life_events_exists => p_life_events_exists );
per_app_asg_pkg.pre_delete_validation (
p_business_group_id => per_asg_shd.g_old_rec.business_group_id,
p_assignment_id => p_rec.assignment_id,
p_application_id => per_asg_shd.g_old_rec.application_id,
p_person_id => per_asg_shd.g_old_rec.person_id,
p_session_date => p_effective_date, --:ctl_globals.session_date,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date,
p_delete_mode => p_datetrack_mode,
p_new_end_date => p_appl_asg_new_end_date); -- :assgt.c_new_end_date ) ;
-- the re-entry point for the update_and_delete_bundle S-S proc
-- called by the pre_delete S-S proc.
-- OR
-- the re-entry point after the check_future_primary warning in
-- the pre_delete S-S proc.
--
-- l_prim_change_flag := p_prim_change_flag;
per_assignments_f2_pkg.pre_delete(
p_datetrack_mode, -- p_del_mode,
p_validation_start_date, -- p_val_st_date,
per_asg_shd.g_old_rec.effective_start_date, -- p_eff_st_date,
per_asg_shd.g_old_rec.effective_end_date, -- p_eff_end_date,
per_asg_shd.g_old_rec.period_of_service_id, -- p_pd_os_id,
l_sys_status_type, -- p_per_sys_st,
p_rec.assignment_id, -- p_ass_id,
p_effective_date, -- p_sess_date,
p_new_end_date, -- need this in post_delete()
p_validation_end_date, -- p_val_end_date,
per_asg_shd.g_old_rec.payroll_id, -- p_pay_id,
per_asg_shd.g_old_rec.grade_id, -- p_grd_id,
per_asg_shd.g_old_rec.special_ceiling_step_id, -- p_sp_ceil_st_id,
l_ceil_seq, -- p_ceil_seq,
per_asg_shd.g_old_rec.person_id, -- p_per_id,
per_asg_shd.g_old_rec.primary_flag, -- p_prim_flag,
p_prim_change_flag, -- need this in post_delete()
p_new_primary_flag, -- need this in post_delete()
l_re_entry_point, -- no change
l_returned_warning, -- no change
p_cancel_atd, -- need this in post_delete()
p_cancel_lspd, -- need this in post_delete()
p_reterm_atd, -- need this in post_delete()
p_reterm_lspd, -- need this in post_delete()
l_prim_date_from, -- no change
p_new_prim_ass_id, -- need this in post_delete()
l_rowid, -- p_row_id,
l_s_pos_id, -- modified from p_
l_s_ass_num, -- modified from p_
l_s_org_id, -- modified from p_
l_s_pg_id, -- modified from p_
l_s_job_id, -- modified from p_
l_s_grd_id, -- modified from p_
p_s_pay_id, -- need this in post_delete()
l_s_def_code_comb_id, -- modified from p_
l_s_soft_code_kf_id, -- modified from p_
l_s_per_sys_st, -- modified from p_
l_s_ass_st_type_id, -- modified from p_
l_s_prim_flag, -- modified from p_
l_s_sp_ceil_step_id, -- modified from p_
l_s_pay_bas, -- modified from p_
l_old_emp_cat, -- Bug#13960540
l_new_emp_cat, -- Bug#13960540
l_pay_basis_id -- Added for Bug 4764140
);
-- assignment LOV and get user to select a new primary
-- assignment. It is not possible from API, so raise
-- an application error.
--
fnd_message.set_name('PER', 'HR_449745_DEL_PRIM_ASG');
-- to delete the record. and he will get an warning This is done as an
-- impact of date tracking of W4 screen
--
if g_debug then
hr_utility.set_location('Before check_payroll_run checks', 50);
p_event => 'DELETE_VALIDATE'
,p_rec => l_rec2
,p_effective_date => p_effective_date -- p_sess_date ,
,p_validation_start_date => p_validation_start_date -- l_validation_start_date
,p_validation_end_date => p_validation_end_date -- l_validation_end_date
,p_datetrack_mode => p_datetrack_mode -- l_del_mode
);
end; -- End of pre-delete checks
procedure post_delete
(p_rec in per_asg_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date,
p_org_now_no_manager_warning out nocopy boolean,
p_loc_change_tax_issues OUT nocopy boolean,
p_delete_asg_budgets OUT nocopy boolean,
p_element_salary_warning OUT nocopy boolean,
p_element_entries_warning OUT nocopy boolean,
p_spp_warning OUT nocopy boolean,
p_cost_warning OUT nocopy boolean,
p_life_events_exists OUT nocopy boolean,
p_cobra_coverage_elements OUT nocopy boolean,
p_assgt_term_elements OUT nocopy boolean,
---
p_new_prim_ass_id IN number,
p_prim_change_flag IN varchar2,
p_new_end_date IN date,
p_new_primary_flag IN varchar2,
p_s_pay_id IN number,
p_cancel_atd IN date,
p_cancel_lspd IN date,
p_reterm_atd IN date,
p_reterm_lspd IN date,
---
p_appl_asg_new_end_date IN date)
is
--
l_sys_status_type varchar2(100);
select per_system_status
from per_assignment_status_types
where assignment_status_type_id = p_rec.assignment_status_type_id;
l_proc varchar2(72) := g_package||'post_delete';
if ( p_datetrack_mode in ('FUTURE_CHANGE','DELETE_NEXT_CHANGE' ) ) then
if ( p_appl_asg_new_end_date is null ) then
if ( p_validation_end_date = hr_api.g_eot ) then
hr_assignment.tidy_up_ref_int ( p_rec.assignment_id, -- p_assignment_id,
'FUTURE',
p_validation_end_date, -- p_validation_end_date,
per_asg_shd.g_old_rec.effective_end_date, -- p_effective_end_date,
null,
null ,
l_appl_cost_warning) ; -- used to catch the cost warning
-- Sets an end date on rows which are deleted with delete mode FUTURE_CHANGES or NEXT_CHANGE
--
if g_debug then
hr_utility.set_location('Before updating assignment end date', 30);
update per_assignments_f a
set a.effective_end_date = p_appl_asg_new_end_date
where a.assignment_id = p_rec.assignment_id -- p_assignment_id
and a.effective_end_date = (
select max(a2.effective_end_date)
from per_assignments_f a2
where a2.assignment_id = a.assignment_id);
end if; -- end of code for 'FUTURE_CHANGE','DELETE_NEXT_CHANGE' modes.
per_app_asg_pkg.post_delete ( p_assignment_id => p_rec.assignment_id, -- :ASSGT.ASSIGNMENT_ID,
p_validation_start_date => p_validation_start_date); -- :ASSGT.VALIDATION_START_DATE ) ;
per_assignments_f1_pkg.post_delete(
p_rec.assignment_id, -- p_ass_id,
per_asg_shd.g_old_rec.grade_id, -- p_grd_id,
p_effective_date, -- p_sess_date,
p_new_end_date, -- from pre_del()
p_validation_end_date, -- p_val_end_date,
per_asg_shd.g_old_rec.effective_end_date, -- p_eff_end_date,
p_datetrack_mode, -- p_del_mode,
p_validation_start_date, -- p_val_st_date,
p_new_primary_flag, -- from pre_del()
hr_api.g_eot, -- p_eot,
per_asg_shd.g_old_rec.period_of_service_id, -- p_pd_os_id,
l_new_prim_ass_id, -- l_new_prim_ass_id,
l_prim_change_flag, -- l_prim_change_flag,
l_sys_status_type, -- p_per_sys_st,
per_asg_shd.g_old_rec.business_group_id, -- p_bg_id,
p_s_pay_id, -- p_old_pay_id,
per_asg_shd.g_old_rec.payroll_id, -- p_new_pay_id,
p_cancel_atd, -- from pre_del()
p_cancel_lspd, -- from pre_del()
p_reterm_atd, -- from pre_del()
p_reterm_lspd, -- from pre_del()
l_warning,
l_future_spp_warning,
l_cost_warning);
SELECT mtn.configuration_value_id
,mtn.configuration_name
,mtn.business_group_id
,mtn.legislation_code
,mtn.pcv_information1 uom
,mtn.pcv_information2 is_enabled
,def.configuration_value_id defn_config_value_id
,def.configuration_name defn_config_name
,def.business_group_id defn_bg_id
,def.legislation_code defn_leg_code
,def.pcv_information4 defn_custom_function
FROM pqp_configuration_values mtn
,pqp_configuration_values def
WHERE mtn.pcv_information_category = 'PQP_ABVM_MAINTENANCE'
AND def.pcv_information_category = 'PQP_ABVM_DEFINITION'
AND def.pcv_information1 = mtn.pcv_information1
AND mtn.pcv_information2 = 'Y'
AND ( mtn.business_group_id = p_business_group_id
OR
( ( mtn.business_group_id IS NULL AND mtn.legislation_code = p_legislation_code )
AND -- there does not exist a config for this UOM at bg level
NOT EXISTS
(SELECT 1
FROM pqp_configuration_values bgmtn
WHERE bgmtn.pcv_information_category = 'PQP_ABVM_MAINTENANCE'
AND bgmtn.pcv_information1 = mtn.pcv_information1
AND bgmtn.business_group_id = p_business_group_id
) -- NOT EXISTS
) -- OR
OR
( ( mtn.business_group_id IS NULL AND mtn.legislation_code IS NULL )
AND -- there does not exist a config for this UOM at a higher level
NOT EXISTS
(SELECT 1
FROM pqp_configuration_values hlmtn
WHERE hlmtn.pcv_information_category = 'PQP_ABVM_MAINTENANCE'
AND hlmtn.pcv_information1 = mtn.pcv_information1
AND ( hlmtn.business_group_id = p_business_group_id
OR
hlmtn.legislation_code = p_legislation_code
)
) -- NOT EXISTS
) -- OR
) -- AND
AND ( def.business_group_id = p_business_group_id
OR
def.business_group_id IS NULL AND def.legislation_code = p_legislation_code
OR
def.business_group_id IS NULL AND def.legislation_code IS NULL
);
select assignment_type, business_group_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and p_effective_date between effective_start_date and effective_end_date ;