[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from per_assignments_f paf
where paf.assignment_type in ('E','C')
and paf.supervisor_id = p_person_id
and p_new_start_date > paf.effective_start_date
and paf.effective_end_date >= p_old_start_date
-- start changes for bug 9304366
and not exists (
select 1
from per_assignment_status_types past
where past.assignment_status_type_id = paf.assignment_status_type_id
and past.per_system_status = 'TERM_ASSIGN'
and (past.business_group_id = paf.business_group_id
or past.business_group_id is NULL));
select 'y' from dual where exists
(select 'x'
from per_periods_of_service pds
where pds.person_id = p_person_id
and pds.date_start = p_old_start_date);
select 'y' from dual where exists
(select 'x'
from per_periods_of_placement pdp
where pdp.person_id = p_person_id
and pdp.date_start = p_old_start_date);
select 'y' from dual where exists
(select 'x'
from per_periods_of_service pds
where pds.person_id = p_person_id
and pds.actual_termination_date < p_new_start_date
and pds.final_process_date >= p_new_start_date);
select 'y' from dual where exists
(select 'x'
from per_periods_of_placement pdp
where pdp.person_id = p_person_id
and pdp.actual_termination_date < p_new_start_date
and pdp.final_process_date >= p_new_start_date);
SELECT null
FROM per_periods_of_service
WHERE person_id = p_person_id
AND p_old_start_date BETWEEN NVL(last_standard_process_date,
actual_termination_date)+1
AND final_process_date
AND p_new_start_date <= NVL(last_standard_process_date,actual_termination_date);
SELECT null
FROM per_periods_of_service
WHERE person_id = p_person_id
AND csr_overlap.p_start_date BETWEEN NVL(last_standard_process_date,
actual_termination_date)+1
AND NVL(final_process_date,hr_api.g_eot);
SELECT null
FROM per_periods_of_service pds
WHERE pds.person_id = p_person_id
AND pds.date_start = p_old_start_date
AND p_new_start_date BETWEEN pds.actual_termination_date
AND pds.final_process_date;
select 'y' from dual where exists
(select 'x'
from per_periods_of_placement pdp
where pdp.person_id = p_person_id
and p_new_start_date between pdp.date_start
and NVL(pdp.actual_termination_date,hr_api.g_eot));
select 'y' from dual where exists
(SELECT NULL
FROM pay_payroll_actions pac,
pay_assignment_actions act,
per_assignments_f asg
WHERE asg.person_id = p_person_id
AND act.assignment_id = asg.assignment_id
AND asg.assignment_type = p_type
AND pac.payroll_action_id = act.payroll_action_id
AND pac.action_status = 'C'
AND ((pac.effective_date BETWEEN p_old_start_date AND (p_new_start_date-1))
OR (pac.date_earned BETWEEN p_old_start_date AND (p_new_start_date-1))));
select date_start,actual_termination_date
from per_periods_of_service
WHERE PERSON_ID = P_PERSON_ID
ORDER BY date_start;
select date_start,actual_termination_date
from per_periods_of_placement
WHERE PERSON_ID = P_PERSON_ID
ORDER BY date_start;
select a.payroll_id , a.supervisor_id
from per_assignments_f a,
per_periods_of_service p
where a.period_of_service_id = p.period_of_service_id
and p.period_of_service_id = p_pds_or_pdp_id
and p_type= 'E'
and p.date_start = a.effective_start_date;
select a.payroll_id , a.supervisor_id
from per_assignments_f a,
per_periods_of_placement p
where a.period_of_placement_date_start = p.date_start
and a.person_id = p.person_id
and p.period_of_placement_id = p_pds_or_pdp_id
and p_type= 'C'
and p.date_start = a.effective_start_date;
select '1' into l_temp
from dual
where exists ( select payroll_id
from pay_payrolls_f
where payroll_id = l_payroll_id
and p_new_start_date between
effective_start_date and effective_end_date
);
select '1' into l_temp
from dual
where exists ( select person_id
from per_all_people_f -- Fix 3562224
where person_id = l_supervisor_id
and ( current_employee_flag = 'Y' OR current_npw_flag = 'Y') -- fix for the bug 9100657
and p_new_start_date between
effective_start_date and effective_end_date
);
select 'x' from dual where exists
(select 1
from per_spinal_point_placements_f sp,
per_periods_of_service p,
per_assignments_f a
where a.person_id = p_person_id
and a.period_of_service_id = p.period_of_service_id
and p.period_of_service_id = p_pds_or_pdp_id
and p_type = 'E'
and a.assignment_id = sp.assignment_id
-- Start changes for bug 9304366
and ((sp.effective_start_date > p.date_start
and
p_new_start_date >= sp.effective_start_date)
or
(sp.effective_start_date >= p.date_start
and
p_new_start_date > sp.effective_end_date)
)
);
select 'x' from dual where exists
(select 1
from per_spinal_point_placements_f sp,
per_periods_of_placement p,
per_assignments_f a
where a.person_id = p_person_id
and a.period_of_placement_date_start = p.date_start
and p.period_of_placement_id = p_pds_or_pdp_id
and p_type = 'C'
and a.assignment_id = sp.assignment_id
-- Start changes for bug 9304366
and ((sp.effective_start_date > p.date_start
and
p_new_start_date >= sp.effective_start_date)
or
(sp.effective_start_date >= p.date_start
and
p_new_start_date > sp.effective_end_date)
)
);
select 'x' from dual where exists
(select 1
from pay_grade_rules_f pgr,
per_periods_of_placement p,
per_assignments_f a
where a.person_id = p_person_id
and a.period_of_placement_date_start = p.date_start
and p.period_of_placement_id = p_pds_or_pdp_id
and a.assignment_type = p_type
and a.assignment_id = pgr.grade_or_spinal_point_id
and pgr.rate_type = 'A'
-- Start changes for bug 9304366
and ((pgr.effective_start_date > p.date_start
and
p_new_start_date >= pgr.effective_start_date)
or
(pgr.effective_start_date >= p.date_start
and
p_new_start_date > pgr.effective_end_date)
)
);
select 'x' from dual where exists
(select 1
from PAY_COST_ALLOCATIONS_F ca,
per_periods_of_service p,
per_assignments_f a
where a.person_id = p_person_id
and a.period_of_service_id = p.period_of_service_id
and p.period_of_service_id = p_pds_or_pdp_id
and p_type = 'E'
and a.assignment_id = ca.assignment_id
-- Start changes for bug 9304366
and ((ca.effective_start_date > p.date_start
and
p_new_start_date >= ca.effective_start_date)
or
(ca.effective_start_date >= p.date_start
and
p_new_start_date > ca.effective_end_date)
)
);
select 'x' from dual where exists
(select 1
from PAY_COST_ALLOCATIONS_F ca,
per_periods_of_placement p,
per_assignments_f a
where a.person_id = p_person_id
and a.period_of_placement_date_start = p.date_start
and p.period_of_placement_id = p_pds_or_pdp_id
and p_type = 'C'
and a.assignment_id = ca.assignment_id
-- Start changes for bug 9304366
and ((ca.effective_start_date > p.date_start
and
p_new_start_date >= ca.effective_start_date)
or
(ca.effective_start_date >= p.date_start
and
p_new_start_date > ca.effective_end_date)
)
);
select 'x' from dual where exists
(select 1
from per_assignment_budget_values_f bud,
per_periods_of_service p,
per_assignments_f a
where a.person_id = p_person_id
and a.period_of_service_id = p.period_of_service_id
and p.period_of_service_id = p_pds_or_pdp_id
and p_type = 'E'
and a.assignment_id = bud.assignment_id
-- Start changes for bug 9304366
and ((bud.effective_start_date > p.date_start
and
p_new_start_date >= bud.effective_start_date)
or
(bud.effective_start_date >= p.date_start
and
p_new_start_date > bud.effective_end_date)
)
);
select 'x' from dual where exists
(select 1
from per_assignment_budget_values_f bud,
per_periods_of_placement p,
per_assignments_f a
where a.person_id = p_person_id
and a.period_of_placement_date_start = p.date_start
and p.period_of_placement_id = p_pds_or_pdp_id
and p_type = 'C'
and a.assignment_id = bud.assignment_id
-- Start changes for bug 9304366
and ((bud.effective_start_date > p.date_start
and
p_new_start_date >= bud.effective_start_date)
or
(bud.effective_start_date >= p.date_start
and
p_new_start_date > bud.effective_end_date)
)
);
select 'x' from dual where exists
(select 1
from per_people_f p
where p.effective_start_date between p_earlier_date and p_later_date
and p.effective_start_date <> p_old_start_date
and p.person_id = p_person_id
union
select 1
from per_people_f p
where p.effective_start_date = p_old_start_date
and p.current_applicant_flag = 'Y'
and p.person_id = p_person_id
-- Start changes for bug 9304366
and exists (
select 1
from per_person_type_usages_f ptu, per_person_types ppt
where ptu.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'APL'
and ptu.person_id = p.person_id
and ptu.effective_start_date = p_old_start_date
)
-- End changes for bug 9304366
);
select 'x' from dual where exists
(select 1
from per_person_type_usages_f ptu,
per_person_types pt
where ptu.person_id = p_person_id
and ptu.effective_start_date <> p_old_start_date
and ptu.effective_start_date between p_earlier_date and p_later_date
and ptu.person_type_id = pt.person_type_id
and pt.system_person_type ='EMP');
select 'x' from dual where exists
(select 1
from per_assignments a
,per_assignments_f f
where f.effective_start_date between p_earlier_date and p_later_date
and f.effective_start_date <> p_old_start_date
and f.assignment_id = a.assignment_id
and a.assignment_type = p_type
and f.assignment_status_type_id <> a.assignment_status_type_id
and f.person_id = a.person_id
and a.person_id = p_person_id);
select 'x' from dual where exists
(select 1
from per_assignments_f f
where f.effective_start_date between p_earlier_date and p_later_date
and f.effective_start_date <> p_old_start_date
and f.person_id = p_person_id);
select 'x' from dual where exists
(select 1
from per_assignments_f f
where f.effective_start_date >= p_new_start_date
and f.effective_start_date < p_old_start_date
and f.assignment_type =p_type
and f.person_id = p_person_id);
select 'Y'
into l_warn
from 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 in
(select assignment_id
from per_assignments_f asg
where asg.person_id = p_person_id
and asg.effective_start_date between l_earlier_date and l_later_date)
and ee.element_link_id = el.element_link_id
and el.element_type_id = et.element_type_id
and et.processing_type = 'R');
select business_group_id
from per_all_people_f
where person_id = p_person_id
and p_new_start_date between effective_start_date and effective_end_date;
SELECT 1
FROM ben_ptnl_ler_for_per bplfp, ben_ler_f blf
where blf.ler_id = bplfp.ler_id
and blf.typ_cd <> 'GSP'
and blf.business_group_id = cl_business_group_id
and bplfp.business_group_id = cl_business_group_id
and bplfp.person_id = p_person_id
and bplfp.LF_EVT_OCRD_DT between cl_start_date and cl_end_date --Changes done for bug 7481343
and bplfp.ptnl_ler_for_per_stat_cd = 'PROCD';
procedure update_ben_GSP_enteries(p_person_id number,
p_new_start_date date,
p_old_start_date date,
p_effective_date date) is
Cursor Person_Info Is
Select Asgt.Business_Group_id,
Asgt.effective_start_date
from Per_All_Assignments_f Asgt
Where person_id = p_person_id
and primary_flag = 'Y'
and assignment_type = 'E'
and P_Effective_Date Between Asgt.Effective_Start_Date and Asgt.Effective_End_Date;
SELECT bplfp.ptnl_ler_for_per_id,
bplfp.object_version_number
FROM ben_ptnl_ler_for_per bplfp,
ben_ler_f blf
where blf.ler_id = bplfp.ler_id
and blf.typ_cd = 'GSP'
and blf.business_group_id = cl_business_group_id
and bplfp.business_group_id = cl_business_group_id
and bplfp.person_id = p_person_id
and bplfp.lf_evt_ocrd_dt between cl_start_date and cl_end_date
and bplfp.ptnl_ler_for_per_stat_cd = 'PROCD';
select per_in_ler_id,
object_version_number
from ben_per_in_ler
where business_group_id = cl_business_group_id
and person_id = p_person_id
and ptnl_ler_for_per_id = cl_ptnl_ler_for_per_id;
select bepec.elig_per_elctbl_chc_id,
bepec.object_version_number
from ben_elig_per_elctbl_chc bepec, ben_pil_elctbl_chc_popl bpecp
where bpecp.pil_elctbl_chc_popl_id = bepec.pil_elctbl_chc_popl_id
and bpecp.business_group_id = cl_business_group_id
and bepec.business_group_id = cl_business_group_id
and bepec.per_in_ler_id = cl_per_in_ler_id
and bpecp.per_in_ler_id = cl_per_in_ler_id;
select enrt_rt_id,
object_version_number
from ben_enrt_rt
where elig_per_elctbl_chc_id = cl_elig_per_elctbl_chc_id;
l_proc varchar2(100):= g_package||'.update_ben_GSP_enteries';
ben_Enrollment_Rate_api.update_Enrollment_Rate
(p_enrt_rt_id => l_enrt_rt_id
,p_rt_strt_dt => l_new_Enrlmt_Dt
,p_object_version_number => l_enrt_rt_ovn
,p_effective_date => l_new_Enrlmt_Dt);
ben_elig_per_elc_chc_api.update_perf_ELIG_PER_ELC_CHC
(p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id
,p_enrt_cvg_strt_dt => l_new_Enrlmt_Dt
,p_ENRT_CVG_STRT_DT_CD => l_new_Enrlmt_Dt
,p_object_version_number => l_Elctbl_Ovn
,p_effective_date => l_new_Enrlmt_Dt);
ben_person_life_event_api.update_Person_Life_Event
(p_per_in_ler_id => l_per_in_ler_id
,p_lf_evt_ocrd_dt => l_new_Enrlmt_Dt
,p_per_in_ler_stat_cd => 'STRTD'
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt
,p_object_version_number => L_Pil_Ovn
,p_effective_date => l_new_Enrlmt_Dt);
Ben_Person_Life_Event_api.update_person_life_event
(p_per_in_ler_id => l_per_in_ler_id
,p_per_in_ler_stat_cd => 'PROCD'
,p_procd_dt => l_procd_dt
,p_strtd_dt => l_strtd_dt
,p_voidd_dt => l_voidd_dt
,p_object_version_number => l_pil_ovn
,P_EFFECTIVE_DATE => l_new_Enrlmt_Dt);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf
(p_ptnl_ler_for_per_id => l_ptnl_ler_for_per_id
,p_lf_evt_ocrd_dt => l_new_Enrlmt_Dt
,p_object_version_number => l_Ptnl_Ovn
,p_effective_date => l_new_Enrlmt_Dt);
end update_ben_GSP_enteries;
procedure update_period(p_person_id number
,p_old_start_date date
,p_new_start_date date
,p_type VARCHAR2) is
cursor pds is select *
from per_periods_of_service pds
where person_id = p_person_id
and date_start = p_old_start_date
for update of date_start nowait;
cursor pdp is select *
from per_periods_of_placement pdp
where person_id = p_person_id
and date_start = p_old_start_date
for update of date_start nowait;
hr_utility.set_message_token('PROCEDURE','hr_change_start_date_api.update_period');
hr_utility.set_message_token('PROCEDURE','hr_change_start_date_api.update_period');
update per_periods_of_placement
set date_start = p_new_start_date
where period_of_placement_id = pdp_rec.period_of_placement_id
and date_start = p_old_start_date
and person_id = p_person_id;
end update_period;
procedure update_spinal_placement(p_person_id number
,p_old_start_date date
,p_new_start_date date
,p_type VARCHAR2) is
cursor csr_ssp is
select placement_id
from per_spinal_point_placements_f sp
where assignment_id in (select a.assignment_id
from per_assignments_f a
where person_id = p_person_id
and a.assignment_type = p_type
and a.effective_start_date = p_old_start_date)
and sp.effective_start_date = p_old_start_date;
update per_spinal_point_placements_f
set effective_start_date = p_new_start_date
where effective_start_date = p_old_start_date
and placement_id = l_sp_id;
hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
end update_spinal_placement;
procedure update_asg_rate(p_person_id number
,p_old_start_date date
,p_new_start_date date
,p_type VARCHAR2) is
cursor csr_rate is
select grade_rule_id
from pay_grade_rules_f pgr
where grade_or_spinal_point_id in (select a.assignment_id
from per_assignments_f a
where person_id = p_person_id
and a.assignment_type = p_type
and a.effective_start_date = p_old_start_date)
and pgr.rate_type = 'A'
and pgr.effective_start_date = p_old_start_date;
update pay_grade_rules_f
set effective_start_date = p_new_start_date
where effective_start_date = p_old_start_date
and grade_rule_id = l_pgr_id;
hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
end update_asg_rate;
procedure update_cost_allocation(p_person_id number
,p_old_start_date date
,p_new_start_date date
,p_type VARCHAR2) is
cursor csr_cost is
select COST_ALLOCATION_ID
from PAY_COST_ALLOCATIONS_F pca
where assignment_id in (select a.assignment_id
from per_assignments_f a
where person_id = p_person_id
and a.assignment_type = p_type
and a.effective_start_date = p_old_start_date)
and pca.effective_start_date = p_old_start_date;
update PAY_COST_ALLOCATIONS_F
set effective_start_date = p_new_start_date
where effective_start_date = p_old_start_date
and COST_ALLOCATION_ID = l_ca_id;
hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
end update_cost_allocation;
procedure update_asg_budget(p_person_id number
,p_old_start_date date
,p_new_start_date date
,p_type VARCHAR2) is
cursor csr_abv is
select abv.assignment_budget_value_id
from per_assignment_budget_values_f abv
where assignment_id in (select a.assignment_id
from per_assignments_f a
where person_id = p_person_id
and a.assignment_type = p_type
and a.effective_start_date = p_old_start_date)
and abv.effective_start_date = p_old_start_date;
update PER_ASSIGNMENT_BUDGET_VALUES_F
set effective_start_date = p_new_start_date
where effective_start_date = p_old_start_date
and effective_end_date >= p_new_start_date
and ASSIGNMENT_BUDGET_VALUE_ID = l_abv_id;
end update_asg_budget;
procedure update_tax(p_person_id number
,p_new_start_date date) is
cursor csr_get_bg is
select business_group_id
from per_people_f
where person_id = p_person_id;
hr_utility.set_message_token('PROCEDURE', 'hr_change_start_date_api.update_tax');
end update_tax;
procedure update_apl_asg(p_person_id number
,p_old_start_date date
,p_new_start_date date) is
cursor csr_apl_asg is
select assignment_id
from per_all_assignments_f a
where a.effective_end_date = p_old_start_date - 1
and a.assignment_type = 'A'
and a.person_id = p_person_id;
update per_assignments_f a
set a.effective_end_date = p_new_start_date - 1
where a.effective_end_date =
(select max(a2.effective_end_date)
from per_assignments_f a2
where a2.assignment_id = a.assignment_id
and a2.assignment_type = 'A')
and a.assignment_id = l_assignment_id;
hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
end update_apl_asg;
PROCEDURE update_appraisals(p_person_id IN NUMBER
,p_old_start_date IN DATE
,p_new_start_date IN DATE) IS
CURSOR csr_appraisals(p_person_id NUMBER,
p_old_date DATE,
p_new_date DATE) IS
SELECT pa.appraisal_id
,GREATEST(pa.appraisal_period_start_date,p_new_date) NEW_START_DATE
,GREATEST(pa.appraisal_period_end_date,p_new_date) NEW_END_DATE
FROM per_appraisals pa
WHERE pa.appraisee_person_id = p_person_id
AND ( pa.appraisal_period_start_date BETWEEN p_old_date AND p_new_date OR
pa.appraisal_period_end_date BETWEEN p_old_date AND p_new_date)
AND pa.appraisal_system_status <> 'DELETED';
hr_utility.set_location('Entering update_appraisals',10);
UPDATE per_appraisals
SET appraisal_period_start_date = i.new_start_date,
appraisal_period_end_date = i.new_end_date
WHERE appraisal_id = i.appraisal_id;
hr_utility.trace('Updated the appraisal');
hr_utility.set_message_token('PROCEDURE','Update_Appraisal_Row');
hr_utility.set_location('Leaving update_appraisals',80);
END update_appraisals;
procedure update_apl(p_person_id number
,p_old_start_date date
,p_new_start_date date) is
cursor csr_apl is
select application_id
from per_applications a
where a.person_id = p_person_id
and a.date_received =
(select max(a2.date_received)
from per_applications a2
where a2.person_id = a.person_id
and a2.date_received < p_new_start_date);
update per_applications a1
set a1.date_end = p_new_start_date - 1
where a1.application_id = l_application_id
and not exists (select 1
from per_people_f peo
where peo.person_id = p_person_id
and a1.person_id = peo.person_id
and peo.effective_start_date = p_old_start_date
and peo.current_applicant_flag = 'Y');
hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
end update_apl;
procedure update_pay_proposal(p_person_id number
,p_old_start_date date
,p_new_start_date date
,p_type VARCHAR2) is
cursor get_pay_proposal
is
select pay_proposal_id
from per_pay_proposals
where change_date = p_old_start_date
and exists (select 1
from per_assignments_f
where person_id = p_person_id
and per_pay_proposals.assignment_id = per_assignments_f.assignment_id
-- and primary_flag = 'Y'
and effective_start_date = p_new_start_date
and assignment_type = p_type);
select count(*)
from per_pay_proposals
where change_date <= p_new_start_date
and assignment_id =
(select assignment_id
from per_assignments_f
where person_id = p_person_id
and primary_flag = 'Y'
and effective_start_date = p_new_start_date
and assignment_type = p_type);
select pay_proposal_id
from per_pay_proposals
where Last_change_date = p_old_start_date
and exists (select 1
from per_assignments_f
where person_id = p_person_id
and per_pay_proposals.assignment_id = per_assignments_f.assignment_id
and effective_start_date = p_new_start_date
and assignment_type = p_type);
hr_utility.set_location('update_pay_proposal',1);
hr_utility.set_location('update_pay_proposal.count = '||l_count,2);
hr_utility.set_location('update_pay_proposal',10);
hr_utility.set_location('update_pay_proposal.p_new_start_date = '||to_char(p_new_start_date,'DD-MON-YYYY'),40);
hr_utility.set_location('update_pay_proposal.p_old_start_date = '||to_char(p_old_start_date,'DD-MON-YYYY'),40);
hr_utility.set_location('update_pay_proposal.p_person_id = '||p_person_id,40);
hr_utility.set_location('update_pay_proposal.p_type = '||p_type,40);
hr_utility.set_location('update_pay_proposal.pay_proposal_id = '||l_pay_proposal_id,40);
update per_pay_proposals
set change_date = p_new_start_date
where change_date = p_old_start_date
and pay_proposal_id = l_pay_proposal_id;
(select assignment_id
from per_assignments_f
where person_id = p_person_id
and primary_flag = 'Y'
and effective_start_date = p_new_start_date
and assignment_type = p_type);
hr_utility.set_location('update_pay_proposal',50);
hr_utility.set_message_token('PROCEDURE','Update_row');
hr_utility.set_location('update_pay_proposal.pay_proposal_id = '||l_pay_proposal_id,60);
update per_pay_proposals
set last_change_date = p_new_start_date
where last_change_date = p_old_start_date
and pay_proposal_id = l_pay_proposal_id;
hr_utility.set_location('update_pay_proposal',70);
hr_utility.set_message_token('PROCEDURE','Update_row');
end update_pay_proposal;
select business_group_id
from per_people_f
where person_id = p_person_id;
select assignment_id
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.assignment_type = p_type
and p_new_start_date between
paf.effective_start_date and paf.effective_end_date;
hr_utility.set_message_token('PROCEDURE', 'hr_change_start_date_api.update_tax');
PROCEDURE UPDATE_PROBATION_END(p_person_id number,
p_new_effective_date date) IS
--
-- select all the assignments for the person_id starting on the Hire date.
--
Cursor csr_assignments(p_person_id number, p_new_effective_date date) IS
select distinct paf.assignment_id
from per_assignments_f paf
where paf.person_id = p_person_id
and paf.date_probation_end is not null
and paf.effective_start_date = p_new_effective_date;
Cursor csr_asg_updates(p_assignment_id number, p_new_effective_date date) IS
select paf.effective_start_date
from per_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.effective_start_date >= p_new_effective_date
order by paf.effective_start_date;
select paf.date_probation_end
,paf.probation_period
,paf.probation_unit
from per_assignments_f paf
where paf.assignment_id = p_assignment_id
and paf.effective_start_date = p_effective_start_date;
l_proc varchar2(30):='update_probation_end';
For asg_update_rec in csr_asg_updates(l_assignment_id, p_new_effective_date) LOOP
--
-- for each updation on Assignment record loop through.
-- check the probation period of updated asg. and If it is update on some other field
-- then update the probation end date of this asg update.
--
l_effective_start_date := asg_update_rec.effective_start_date;
hr_utility.set_location('date probation end is not updated', 50);
hr_utility.set_location('date probation end is updated', 60);
update per_assignments_f paf
set paf.date_probation_end = l_new_date_probation_end
where paf.assignment_id = l_assignment_id
and paf.effective_start_date = l_effective_start_date;
END UPDATE_PROBATION_END;
select 'Y'
from pqp_assignment_attributes_f paa
,per_assignments_f paf
where paf.person_id = p_person_id
and paa.assignment_id = paf.assignment_id
and paf.effective_start_date = trunc(p_old_start_date)
and ( paa.effective_start_date between p_old_start_date+1 and p_new_start_date
OR
paa.effective_end_date between p_old_start_date and p_new_start_date);
SELECT GRADE_LADDER_PGM_ID
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND GRADE_LADDER_PGM_ID is not null
AND effective_start_date = p_old_start_date;
SELECT NULL
FROM ben_pgm_f pgm
WHERE pgm.pgm_id = p_pgm_id
AND p_new_start_date between pgm.effective_start_date
and pgm.effective_end_date;
select assignment_id, business_group_id
from per_all_assignments_f
where person_id = p_per
and effective_start_date = p_new_st_date;
PAY_POG_ALL_ASSIGNMENTS_PKG.AFTER_UPDATE
(
p_effective_date => p_new_start_date
,p_datetrack_mode => dt_mode
,p_validation_start_date => null
,p_validation_end_date => null
,P_APPLICANT_RANK => null
,P_APPLICATION_ID => null
,P_ASSIGNMENT_CATEGORY => null
,P_ASSIGNMENT_ID => asgrec.assignment_id
,P_ASSIGNMENT_NUMBER => null
,P_ASSIGNMENT_STATUS_TYPE_ID => null
,P_ASSIGNMENT_TYPE => null
,P_ASS_ATTRIBUTE1 => null
,P_ASS_ATTRIBUTE10 => null
,P_ASS_ATTRIBUTE11 => null
,P_ASS_ATTRIBUTE12 => null
,P_ASS_ATTRIBUTE13 => null
,P_ASS_ATTRIBUTE14 => null
,P_ASS_ATTRIBUTE15 => null
,P_ASS_ATTRIBUTE16 => null
,P_ASS_ATTRIBUTE17 => null
,P_ASS_ATTRIBUTE18 => null
,P_ASS_ATTRIBUTE19 => null
,P_ASS_ATTRIBUTE2 => null
,P_ASS_ATTRIBUTE20 => null
,P_ASS_ATTRIBUTE21 => null
,P_ASS_ATTRIBUTE22 => null
,P_ASS_ATTRIBUTE23 => null
,P_ASS_ATTRIBUTE24 => null
,P_ASS_ATTRIBUTE25 => null
,P_ASS_ATTRIBUTE26 => null
,P_ASS_ATTRIBUTE27 => null
,P_ASS_ATTRIBUTE28 => null
,P_ASS_ATTRIBUTE29 => null
,P_ASS_ATTRIBUTE3 => null
,P_ASS_ATTRIBUTE30 => null
,P_ASS_ATTRIBUTE4 => null
,P_ASS_ATTRIBUTE5 => null
,P_ASS_ATTRIBUTE6 => null
,P_ASS_ATTRIBUTE7 => null
,P_ASS_ATTRIBUTE8 => null
,P_ASS_ATTRIBUTE9 => null
,P_ASS_ATTRIBUTE_CATEGORY => null
,P_BARGAINING_UNIT_CODE => null
,P_CAGR_GRADE_DEF_ID => null
,P_CAGR_ID_FLEX_NUM => null
,P_CHANGE_REASON => null
,P_COLLECTIVE_AGREEMENT_ID => null
,P_COMMENTS => null
,P_COMMENT_ID => null
,P_CONTRACT_ID => null
,P_DATE_PROBATION_END => null
,P_DEFAULT_CODE_COMB_ID => null
,P_EFFECTIVE_END_DATE => null
,P_EFFECTIVE_START_DATE => p_new_start_date
,P_EMPLOYEE_CATEGORY => null
,P_EMPLOYMENT_CATEGORY => null
,P_ESTABLISHMENT_ID => null
,P_FREQUENCY => null
,P_GRADE_ID => null
,P_HOURLY_SALARIED_CODE => null
,P_HOURLY_SALARIED_WARNING => null
,P_INTERNAL_ADDRESS_LINE => null
,P_JOB_ID => null
,P_JOB_POST_SOURCE_NAME => null
,P_LABOUR_UNION_MEMBER_FLAG => null
,P_LOCATION_ID => null
,P_MANAGER_FLAG => null
,P_NORMAL_HOURS => null
,P_NOTICE_PERIOD => null
,P_NOTICE_PERIOD_UOM => null
,P_NO_MANAGERS_WARNING => null
,P_OBJECT_VERSION_NUMBER => null
,P_ORGANIZATION_ID => null
,P_ORG_NOW_NO_MANAGER_WARNING => null
,P_OTHER_MANAGER_WARNING => null
,P_PAYROLL_ID => null
,P_PAYROLL_ID_UPDATED => null
,P_PAY_BASIS_ID => null
,P_PEOPLE_GROUP_ID => null
,P_PERF_REVIEW_PERIOD => null
,P_PERF_REVIEW_PERIOD_FREQUEN => null
,P_PERIOD_OF_SERVICE_ID => null
,P_PERSON_REFERRED_BY_ID => null
,P_PLACEMENT_DATE_START => null
,P_POSITION_ID => null
,P_POSTING_CONTENT_ID => null
,P_PRIMARY_FLAG => null
,P_PROBATION_PERIOD => null
,P_PROBATION_UNIT => null
,P_PROGRAM_APPLICATION_ID => null
,P_PROGRAM_ID => null
,P_PROGRAM_UPDATE_DATE => null
,P_PROJECT_TITLE => null
,P_RECRUITER_ID => null
,P_RECRUITMENT_ACTIVITY_ID => null
,P_REQUEST_ID => null
,P_SAL_REVIEW_PERIOD => null
,P_SAL_REVIEW_PERIOD_FREQUEN => null
,P_SET_OF_BOOKS_ID => null
,P_SOFT_CODING_KEYFLEX_ID => null
,P_SOURCE_ORGANIZATION_ID => null
,P_SOURCE_TYPE => null
,P_SPECIAL_CEILING_STEP_ID => null
,P_SUPERVISOR_ID => null
,P_TIME_NORMAL_FINISH => null
,P_TIME_NORMAL_START => null
,P_TITLE => null
,P_VACANCY_ID => null
,P_VENDOR_ASSIGNMENT_NUMBER => null
,P_VENDOR_EMPLOYEE_NUMBER => null
,P_VENDOR_ID => null
,P_WORK_AT_HOME => null
,P_GRADE_LADDER_PGM_ID => null
,P_SUPERVISOR_ASSIGNMENT_ID => null
,P_VENDOR_SITE_ID => null
,P_PO_HEADER_ID => null
,P_PO_LINE_ID => null
,P_PROJECTED_ASSIGNMENT_END => null
,P_APPLICANT_RANK_O => null
,P_APPLICATION_ID_O => null
,P_ASSIGNMENT_CATEGORY_O => null
,P_ASSIGNMENT_NUMBER_O => null
,P_ASSIGNMENT_SEQUENCE_O => null
,P_ASSIGNMENT_STATUS_TYPE_ID_O => null
,P_ASSIGNMENT_TYPE_O => null
,P_ASS_ATTRIBUTE1_O => null
,P_ASS_ATTRIBUTE10_O => null
,P_ASS_ATTRIBUTE11_O => null
,P_ASS_ATTRIBUTE12_O => null
,P_ASS_ATTRIBUTE13_O => null
,P_ASS_ATTRIBUTE14_O => null
,P_ASS_ATTRIBUTE15_O => null
,P_ASS_ATTRIBUTE16_O => null
,P_ASS_ATTRIBUTE17_O => null
,P_ASS_ATTRIBUTE18_O => null
,P_ASS_ATTRIBUTE19_O => null
,P_ASS_ATTRIBUTE2_O => null
,P_ASS_ATTRIBUTE20_O => null
,P_ASS_ATTRIBUTE21_O => null
,P_ASS_ATTRIBUTE22_O => null
,P_ASS_ATTRIBUTE23_O => null
,P_ASS_ATTRIBUTE24_O => null
,P_ASS_ATTRIBUTE25_O => null
,P_ASS_ATTRIBUTE26_O => null
,P_ASS_ATTRIBUTE27_O => null
,P_ASS_ATTRIBUTE28_O => null
,P_ASS_ATTRIBUTE29_O => null
,P_ASS_ATTRIBUTE3_O => null
,P_ASS_ATTRIBUTE30_O => null
,P_ASS_ATTRIBUTE4_O => null
,P_ASS_ATTRIBUTE5_O => null
,P_ASS_ATTRIBUTE6_O => null
,P_ASS_ATTRIBUTE7_O => null
,P_ASS_ATTRIBUTE8_O => null
,P_ASS_ATTRIBUTE9_O => null
,P_ASS_ATTRIBUTE_CATEGORY_O => null
,P_BARGAINING_UNIT_CODE_O => null
,P_BUSINESS_GROUP_ID_O => asgrec.business_group_id
,P_CAGR_GRADE_DEF_ID_O => null
,P_CAGR_ID_FLEX_NUM_O => null
,P_CHANGE_REASON_O => null
,P_COLLECTIVE_AGREEMENT_ID_O => null
,P_COMMENT_ID_O => null
,P_CONTRACT_ID_O => null
,P_DATE_PROBATION_END_O => null
,P_DEFAULT_CODE_COMB_ID_O => null
,P_EFFECTIVE_END_DATE_O => null
,P_EFFECTIVE_START_DATE_O => p_old_start_date
,P_EMPLOYEE_CATEGORY_O => null
,P_EMPLOYMENT_CATEGORY_O => null
,P_ESTABLISHMENT_ID_O => null
,P_FREQUENCY_O => null
,P_GRADE_ID_O => null
,P_HOURLY_SALARIED_CODE_O => null
,P_INTERNAL_ADDRESS_LINE_O => null
,P_JOB_ID_O => null
,P_JOB_POST_SOURCE_NAME_O => null
,P_LABOUR_UNION_MEMBER_FLAG_O => null
,P_LOCATION_ID_O => null
,P_MANAGER_FLAG_O => null
,P_NORMAL_HOURS_O => null
,P_NOTICE_PERIOD_O => null
,P_NOTICE_PERIOD_UOM_O => null
,P_OBJECT_VERSION_NUMBER_O => null
,P_ORGANIZATION_ID_O => null
,P_PAYROLL_ID_O => null
,P_PAY_BASIS_ID_O => null
,P_PEOPLE_GROUP_ID_O => null
,P_PERF_REVIEW_PERIOD_O => null
,P_PERF_REVIEW_PERIOD_FREQUEN_O => null
,P_PERIOD_OF_SERVICE_ID_O => null
,P_PERSON_ID_O => p_person_id
,P_PERSON_REFERRED_BY_ID_O => null
,P_PLACEMENT_DATE_START_O => null
,P_POSITION_ID_O => null
,P_POSTING_CONTENT_ID_O => null
,P_PRIMARY_FLAG_O => null
,P_PROBATION_PERIOD_O => null
,P_PROBATION_UNIT_O => null
,P_PROGRAM_APPLICATION_ID_O => null
,P_PROGRAM_ID_O => null
,P_PROGRAM_UPDATE_DATE_O => null
,P_PROJECT_TITLE_O => null
,P_RECRUITER_ID_O => null
,P_RECRUITMENT_ACTIVITY_ID_O => null
,P_REQUEST_ID_O => null
,P_SAL_REVIEW_PERIOD_O => null
,P_SAL_REVIEW_PERIOD_FREQUEN_O => null
,P_SET_OF_BOOKS_ID_O => null
,P_SOFT_CODING_KEYFLEX_ID_O => null
,P_SOURCE_ORGANIZATION_ID_O => null
,P_SOURCE_TYPE_O => null
,P_SPECIAL_CEILING_STEP_ID_O => null
,P_SUPERVISOR_ID_O => null
,P_TIME_NORMAL_FINISH_O => null
,P_TIME_NORMAL_START_O => null
,P_TITLE_O => null
,P_VACANCY_ID_O => null
,P_VENDOR_ASSIGNMENT_NUMBER_O => null
,P_VENDOR_EMPLOYEE_NUMBER_O => null
,P_VENDOR_ID_O => null
,P_WORK_AT_HOME_O => null
,P_GRADE_LADDER_PGM_ID_O => null
,P_SUPERVISOR_ASSIGNMENT_ID_O => null
,P_VENDOR_SITE_ID_O => null
,P_PO_HEADER_ID_O => null
,P_PO_LINE_ID_O => null
,P_PROJECTED_ASSIGNMENT_END_O => null
);
procedure update_start_date
(p_validate in boolean
,p_person_id in number
,p_old_start_date in date
,p_new_start_date in date
,p_update_type in varchar2
,p_applicant_number in varchar2
,p_warn_ee out nocopy varchar2
) is
--
-- Declare cursors and local variables
--
cursor csr_pds
is
select period_of_service_id
from per_periods_of_service
where person_id = p_person_id
and date_start = p_old_start_date;
select period_of_placement_id
from per_periods_of_placement
where person_id = p_person_id
and date_start = p_old_start_date;
SELECT count(1)
FROM PER_ADDRESSES
WHERE PERSON_ID = csr_addresses.p_person_id;
SELECT bus.legislation_code
FROM per_people_f per
,per_business_groups bus
WHERE per.person_id = csr_legislation.p_person_id
AND per.business_group_id+0 = bus.business_group_id
AND csr_legislation.p_effective_date BETWEEN per.effective_start_date
AND per.effective_end_date;
l_proc varchar2(30):='update_start_date';
,p_argument => 'update_type'
,p_argument_value => p_update_type);
savepoint update_start_date;
if p_update_type = 'E' then
open csr_pds;
elsif p_update_type = 'C' then
open csr_pdp;
hr_utility.set_message_token('PROCEDURE','update_start_date');
hr_change_start_date_bk1.update_start_date_b
(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date
,p_update_type => p_update_type
,p_applicant_number => p_applicant_number
);
(p_module_name => 'update_start_date'
,p_hook_type => 'BP'
);
,p_type => p_update_type
);
,p_type => p_update_type
);
,p_type => p_update_type);
,p_type => p_update_type);
if ( p_update_type = 'C'
OR
( p_update_type = 'E'
AND
( nvl(fnd_profile.value('HR_MV_HIRE_SKIP_ACT_VALIDATION'),'N') = 'N'
OR
NOT l_hd_rule_found
OR
(l_hd_rule_found AND nvl(l_hd_rule_value,'N') = 'N')
)
)
) THEN
--
-- Disallow change hire date beyond PAY actions.
-- Retaining validation
--
--
-- 115.30 (END)
--
check_for_compl_actions(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date
,p_type => p_update_type);
,p_type => p_update_type);
,p_type => p_update_type);
,p_type => p_update_type);
,p_type => p_update_type);
,p_type => p_update_type);
,p_type => p_update_type
,p_old_start_date => l_old_start_date);
,p_type => p_update_type
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date);
update per_people_f p
set p.effective_start_date = decode(p.effective_start_date
,l_old_start_date,l_new_start_date,p.effective_start_date)
,p.effective_end_date = decode(p.effective_end_date
,l_old_start_date-1,l_new_start_date-1,p.effective_end_date)
,p.original_date_of_hire = decode(p.original_date_of_hire
,l_old_start_date, l_new_start_date,p.original_date_of_hire)
where p.person_id = p_person_id;
hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
update per_people_f p
set p.start_date = (select min(ppf.effective_start_date)
from per_people_f ppf
where ppf.person_id = p_person_id)
where p.person_id = p_person_id;
update_period(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date
,p_type => p_update_type);
update per_addresses a
set a.date_from = l_new_start_date
where a.date_from = l_old_start_date
and nvl(a.date_to,l_new_start_date) >= l_new_start_date
and a.person_id = p_person_id;
select greatest(max(o.effective_start_date),l_new_start_date)
into l_ppm_start_date
from pay_org_payment_methods_f o
where o.org_payment_method_id = ( select org_payment_method_id from(
select p.org_payment_method_id
from pay_personal_payment_methods_f p
where p.effective_start_date >= l_old_start_date
and exists
(select 1
from per_assignments_f a
where p.assignment_id = a.assignment_id
and a.assignment_type = p_update_type
and a.person_id = p_person_id) order by p.effective_start_date)
where rownum=1);
update pay_personal_payment_methods_f p
set p.effective_start_date = l_ppm_start_date
where p.effective_start_date = l_old_start_date
and p.effective_end_date >= l_new_start_date
and exists
(select 1
from per_assignments_f a
where p.assignment_id = a.assignment_id
and a.assignment_type = p_update_type
and a.person_id = p_person_id);
update_spinal_placement(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date
,p_type => p_update_type);
update_asg_rate(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date
,p_type => p_update_type);
update_cost_allocation(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date
,p_type => p_update_type);
update_asg_budget(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date
,p_type => p_update_type);
update_appraisals(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date);
if p_update_type='C' then
update per_assignments_f a
set a.period_of_placement_date_start = l_new_start_date
where a.period_of_placement_date_start = l_old_start_date
and a.assignment_type = p_update_type
and a.person_id = p_person_id;
hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
update per_assignments_f a
set a.effective_start_date = l_new_start_date
where a.effective_start_date = l_old_start_date
and a.assignment_type = p_update_type
and a.person_id = p_person_id;
hr_utility.set_message(801,'HR_6094_ALL_CANT_UPDATE');
UPDATE per_assignments_f A
SET A.effective_end_date = (l_new_start_date - 1)
WHERE A.effective_end_date = (l_old_start_date - 1)
AND (l_new_start_date - 1) >= A.effective_start_date
AND A.assignment_type = p_update_type
AND A.person_id = p_person_id;
update_probation_end( p_person_id, l_new_start_date);
update pqp_assignment_attributes_f paa
set paa.effective_start_date = l_new_start_date
where paa.effective_start_date = l_old_start_date
and paa.assignment_id in
(select paf.assignment_id
from per_assignments_f paf
where paf.effective_start_date = l_new_start_date
and paf.person_id = p_person_id);
update_tax(p_person_id => p_person_id
,p_new_start_date => l_new_start_date);
update_apl_asg(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date);
update_apl(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date);
update_pay_proposal(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date
,p_type => p_update_type);
update_ben_GSP_enteries(p_person_id => p_person_id
,p_new_start_date => l_new_start_date
,p_old_start_date => l_old_start_date
,p_effective_date => l_new_start_date);
UPDATE per_ben_identified_events
SET effective_date = l_new_start_date
WHERE person_id = p_person_id
AND effective_date = l_old_start_date
AND status <> 'Processed';
if p_update_type in ('E','C') then
--
-- 115.30 (START)
--
if ( nvl(fnd_profile.value('HR_MV_HIRE_SKIP_ACT_VALIDATION'),'N') = 'N'
OR
NOT l_hd_rule_found
OR
(l_hd_rule_found AND nvl(l_hd_rule_value,'N') = 'N')
)
then
--
-- Change Hire Date past PAY actions not allowed
--
--
-- 115.30 (END)
--
run_alu_ee(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date
,p_type => p_update_type);
,p_type => p_update_type);
p_adjustment_type => 'UPDATE');
hr_change_start_date_bk1.update_start_date_a
(p_person_id => p_person_id
,p_old_start_date => l_old_start_date
,p_new_start_date => l_new_start_date
,p_update_type => p_update_type
,p_applicant_number => p_applicant_number
,p_warn_ee => l_warn_ee
);
(p_module_name => 'update_start_date'
,p_hook_type => 'AP'
);
rollback to update_start_date;
rollback to update_start_date;
end update_start_date;