[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
OR nvl(p_offer_assignment_rec_old.PROGRAM_UPDATE_DATE,hr_api.g_date) <> nvl(p_offer_assignment_rec_new.PROGRAM_UPDATE_DATE,hr_api.g_date)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE_CATEGORY ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE_CATEGORY ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE1 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE1,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE2 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE2 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE3 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE3 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE4 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE4 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE5 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE5 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE6 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE6,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE7 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE7 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE8 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE8 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE9 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE9 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE10,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE10,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE11 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE11,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE12 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE12 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE13 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE13 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE14 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE14 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE15 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE15 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE16 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE16 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE17 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE17 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE18 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE18 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE19 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE19 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE20 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE20 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE21 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE21 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE22 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE22 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE23 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE23 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE24 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE24 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE25 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE25 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE26 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE26 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE27 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE27 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE28 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE28 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE29 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE29 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASS_ATTRIBUTE30 ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASS_ATTRIBUTE30 ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.TITLE ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.TITLE ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.OBJECT_VERSION_NUMBER ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.OBJECT_VERSION_NUMBER,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.BARGAINING_UNIT_CODE ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.BARGAINING_UNIT_CODE ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.LABOUR_UNION_MEMBER_FLAG,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.LABOUR_UNION_MEMBER_FLAG ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.HOURLY_SALARIED_CODE ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.HOURLY_SALARIED_CODE ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.CONTRACT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.CONTRACT_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.COLLECTIVE_AGREEMENT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.COLLECTIVE_AGREEMENT_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.CAGR_ID_FLEX_NUM ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.CAGR_ID_FLEX_NUM ,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.CAGR_GRADE_DEF_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.CAGR_GRADE_DEF_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.ESTABLISHMENT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.ESTABLISHMENT_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.NOTICE_PERIOD ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.NOTICE_PERIOD ,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.NOTICE_PERIOD_UOM ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.NOTICE_PERIOD_UOM ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.EMPLOYEE_CATEGORY ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.EMPLOYEE_CATEGORY ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.WORK_AT_HOME ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.WORK_AT_HOME ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.JOB_POST_SOURCE_NAME ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.JOB_POST_SOURCE_NAME ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.POSTING_CONTENT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.POSTING_CONTENT_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.PERIOD_OF_PLACEMENT_DATE_START,hr_api.g_date) <> nvl(p_offer_assignment_rec_new.PERIOD_OF_PLACEMENT_DATE_START,hr_api.g_date)
OR nvl(p_offer_assignment_rec_old.VENDOR_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.VENDOR_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.VENDOR_EMPLOYEE_NUMBER ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.VENDOR_EMPLOYEE_NUMBER ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.VENDOR_ASSIGNMENT_NUMBER ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.VENDOR_ASSIGNMENT_NUMBER ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.ASSIGNMENT_CATEGORY ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.ASSIGNMENT_CATEGORY ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.PROJECT_TITLE ,hr_api.g_varchar2) <> nvl(p_offer_assignment_rec_new.PROJECT_TITLE ,hr_api.g_varchar2)
OR nvl(p_offer_assignment_rec_old.APPLICANT_RANK ,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.APPLICANT_RANK ,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.GRADE_LADDER_PGM_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.GRADE_LADDER_PGM_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.SUPERVISOR_ASSIGNMENT_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.SUPERVISOR_ASSIGNMENT_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.VENDOR_SITE_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.VENDOR_SITE_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.PO_HEADER_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PO_HEADER_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.PO_LINE_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.PO_LINE_ID,hr_api.g_number)
OR nvl(p_offer_assignment_rec_old.PROJECTED_ASSIGNMENT_END,hr_api.g_date) <> nvl(p_offer_assignment_rec_new.PROJECTED_ASSIGNMENT_END,hr_api.g_date)
OR nvl(p_offer_assignment_rec_old.BUSINESS_GROUP_ID,hr_api.g_number) <> nvl(p_offer_assignment_rec_new.BUSINESS_GROUP_ID,hr_api.g_number)
then
return true;
select max(last_update_date)
from (select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_addresses
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_all_assignments_f
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_all_people_f
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_contact_relationships
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(psl.last_update_date,p_pil_last_upd_date)) last_update_date
from per_pay_proposals psl, per_all_assignments_f asn
where psl.assignment_id = asn.assignment_id
and asn.person_id = p_person_id
and asn.business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_periods_of_service
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_qualifications
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_absence_attendances
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_person_type_usages_f
where person_id = p_person_id
);
select pil.last_update_date last_update_date
from ben_per_in_ler pil , ben_ler_f ler
where pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.assignment_id = p_assignment_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ler.ler_id = pil.ler_id
and ler.typ_cd = 'IREC'
and p_effective_date between ler.effective_start_date and ler.effective_end_date;
1. If yes, update present rate record with old rate record.
2. Otherwise do nothing.
***/
PROCEDURE post_irec_process_update (
p_person_id IN NUMBER,
p_business_group_id IN NUMBER,
p_assignment_id IN NUMBER,
p_effective_date IN DATE
)
IS
-- get the latest pil which has been voided.
CURSOR c_last_pil
IS
SELECT per_in_ler_id
FROM ben_per_in_ler pil, ben_ler_f ler
WHERE pil.person_id = p_person_id
AND pil.business_group_id = p_business_group_id
AND pil.assignment_id = p_assignment_id
AND pil.per_in_ler_stat_cd = 'BCKDT' -- 5068367
AND ler.ler_id = pil.ler_id
AND ler.typ_cd = 'IREC'
AND p_effective_date BETWEEN ler.effective_start_date
AND ler.effective_end_date
ORDER BY pil.last_update_date DESC;
SELECT per_in_ler_id
FROM ben_per_in_ler pil, ben_ler_f ler
WHERE pil.person_id = p_person_id
AND pil.business_group_id = p_business_group_id
AND pil.assignment_id = p_assignment_id
AND pil.per_in_ler_stat_cd = 'STRTD'
AND ler.ler_id = pil.ler_id
AND ler.typ_cd = 'IREC'
AND p_effective_date BETWEEN ler.effective_start_date
AND ler.effective_end_date;
SELECT oipl_id, pl_id, elig_per_elctbl_chc_id,comments
FROM ben_elig_per_elctbl_chc past_epe
WHERE past_epe.per_in_ler_id = p_past_pil
AND past_epe.business_group_id = p_business_group_id
AND past_epe.elctbl_flag = 'Y'
AND past_epe.approval_status_cd = 'IRC_BEN_A'
AND EXISTS (
SELECT NULL
FROM ben_elig_per_elctbl_chc present_epe
WHERE present_epe.per_in_ler_id = p_present_pil
AND present_epe.elctbl_flag = 'Y'
AND present_epe.elig_flag = 'Y'
AND present_epe.pl_id = past_epe.pl_id
AND NVL (present_epe.oipl_id, present_epe.pl_id) =
NVL (past_epe.oipl_id, past_epe.pl_id));
SELECT elig_per_elctbl_chc_id,object_version_number
FROM ben_elig_per_elctbl_chc present_pil
WHERE present_pil.per_in_ler_id = p_present_pil
AND present_pil.elctbl_flag = 'Y'
AND present_pil.elig_flag = 'Y'
AND present_pil.pl_id = p_pl_id
AND NVL (present_pil.oipl_id, present_pil.pl_id) =
NVL (p_oipl_id, p_pl_id)
AND present_pil.business_group_id = p_business_group_id;
SELECT enrt_rt_id, val, cmcd_val, ann_val
FROM ben_enrt_rt
WHERE elig_per_elctbl_chc_id = p_past_epe
AND entr_val_at_enrt_flag = 'Y'
AND p_business_group_id = p_business_group_id;
SELECT enrt_rt_id, mx_elcn_val max_val, mn_elcn_val min_val
FROM ben_enrt_rt
WHERE elig_per_elctbl_chc_id = p_present_epe
AND entr_val_at_enrt_flag = 'Y'
AND p_business_group_id = p_business_group_id;
ben_elig_per_elc_chc_api.update_perf_elig_per_elc_chc
(p_elig_per_elctbl_chc_id => l_present_epe_id,
p_effective_date => p_effective_date,
p_object_version_number => l_present_epe_ovn,
p_approval_status_cd => 'IRC_BEN_A',
p_comments => l_last_epe.comments
);
ben_enrollment_rate_api.update_enrollment_rate
(p_enrt_rt_id => l_present_ecr.enrt_rt_id,
p_val => l_past_ecr.val,
p_cmcd_val => l_past_ecr.cmcd_val,
p_ann_val => l_past_ecr.ann_val,
p_effective_date => p_effective_date,
p_object_version_number => l_object_version_number
);
END post_irec_process_update;
1.Firstly,this proc VOIDs ( read as DELETE) the pil,ppl,pel
corresponding to a Deleted Transaction of IREC
2.Secondly, it restores back the latest Backed out pil,pel
to STARTED state.
***/
PROCEDURE void_or_restore_life_event (
p_person_id IN NUMBER,
p_assignment_id IN NUMBER,
p_offer_assignment_id IN NUMBER DEFAULT NULL,
p_void_per_in_ler_id IN NUMBER DEFAULT NULL,
p_restore_per_in_ler_id IN NUMBER DEFAULT NULL,
p_status_cd IN VARCHAR2 DEFAULT NULL,
p_effective_date IN DATE
)
IS
CURSOR c_ptnl (c_ptnl_ler_for_per_id IN NUMBER)
IS
SELECT ptnl.*
FROM ben_ptnl_ler_for_per ptnl
WHERE ptnl.ptnl_ler_for_per_id = c_ptnl_ler_for_per_id;
SELECT pil.*
FROM ben_per_in_ler pil
WHERE pil.per_in_ler_id = c_per_in_ler_id
AND pil.assignment_id = p_assignment_id
AND pil.person_id = p_person_id;
SELECT pel.pil_elctbl_chc_popl_id, pel.object_version_number
FROM ben_pil_elctbl_chc_popl pel
WHERE pel.per_in_ler_id = p_per_in_ler_id;
SELECT pil.per_in_ler_id
FROM ben_per_in_ler pil, ben_ler_f ler
WHERE pil.person_id = p_person_id
AND pil.assignment_id = p_assignment_id
AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
AND pil.ler_id = ler.ler_id
AND ler.typ_cd = 'IREC'
AND p_effective_date BETWEEN ler.effective_start_date
AND ler.effective_end_date
ORDER BY pil.per_in_ler_id DESC;
SELECT pil.per_in_ler_id
FROM ben_per_in_ler pil, ben_ler_f ler
WHERE pil.person_id = p_person_id
AND pil.assignment_id = p_assignment_id
AND pil.per_in_ler_stat_cd = 'BCKDT'
AND pil.ler_id = ler.ler_id
AND ler.typ_cd = 'IREC'
AND p_effective_date BETWEEN ler.effective_start_date
AND ler.effective_end_date
ORDER BY pil.per_in_ler_id DESC;
SELECT NULL
FROM ben_per_in_ler pil
WHERE pil.per_in_ler_id <> p_restore_per_in_ler_id
AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
AND pil.assignment_id = p_assignment_id
AND pil.person_id = p_person_id;
SELECT *
FROM ben_per_in_ler
WHERE assignment_id = p_assignment_id
AND per_in_ler_stat_cd NOT IN ('VOIDD', 'PROCD')
AND per_in_ler_id >
(SELECT NVL (MAX (per_in_ler_id), -1)
FROM ben_pil_assignment
WHERE offer_assignment_id IS NOT NULL
AND applicant_assignment_id = p_assignment_id);
3.Update person_life_event (PIL)
4.update potential Life event (PPL)
5.update pil_electbl_choice_popl (PEL)
***/
hr_utility.set_location (' Entering ben_irc_util.void_or_restore_life_event ',
10
);
/*** Update PIl,PEL,PPL
***/
hr_utility.set_location ('Voiding starts ', 999);
hr_utility.set_location ('Before update_person_life_event ', 9901);
ben_person_life_event_api.update_person_life_event (p_per_in_ler_id => l_get_pil.per_in_ler_id,
p_bckt_per_in_ler_id => NULL,
p_per_in_ler_stat_cd => 'VOIDD',
p_prvs_stat_cd => l_get_pil.per_in_ler_stat_cd,
p_object_version_number => l_get_pil.object_version_number,
p_effective_date => p_effective_date,
p_procd_dt => l_procd_dt,
p_strtd_dt => l_strtd_dt,
p_voidd_dt => l_voidd_dt
);
hr_utility.set_location ('Before update_ptnl_ler_for_per_perf ',
9902);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf (p_validate => FALSE,
p_ptnl_ler_for_per_id => l_ptnl.ptnl_ler_for_per_id,
p_ptnl_ler_for_per_stat_cd => 'VOIDD',
p_person_id => l_ptnl.person_id,
p_business_group_id => l_ptnl.business_group_id,
p_object_version_number => l_ptnl.object_version_number,
p_effective_date => p_effective_date,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_request_id => fnd_global.conc_request_id,
p_program_update_date => SYSDATE,
p_voidd_dt => p_effective_date
);
hr_utility.set_location ('Before update_pil_elctbl_chc_popl ', 9903);
ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl (p_validate => FALSE,
p_pil_elctbl_chc_popl_id => l_pel_pk_id,
p_pil_elctbl_popl_stat_cd => 'BCKDT',
p_object_version_number => l_pel_object_version_number,
p_effective_date => p_effective_date
);
hr_utility.set_location ('After update_pil_elctbl_chc_popl ', 9904);
hr_utility.set_location ('Before update_person_life_event ', 111);
ben_person_life_event_api.update_person_life_event (p_per_in_ler_id => p_void_per_in_ler_id,
p_bckt_per_in_ler_id => NULL,
p_per_in_ler_stat_cd => 'VOIDD',
p_prvs_stat_cd => l_pil.per_in_ler_stat_cd,
p_object_version_number => l_pil.object_version_number,
p_effective_date => p_effective_date,
p_procd_dt => l_procd_dt,
p_strtd_dt => l_strtd_dt,
p_voidd_dt => l_voidd_dt
);
hr_utility.set_location ('After update_person_life_event ', 111);
hr_utility.set_location ('Before update_ptnl_ler_for_per_perf ', 111);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf (p_validate => FALSE,
p_ptnl_ler_for_per_id => l_ptnl.ptnl_ler_for_per_id,
p_ptnl_ler_for_per_stat_cd => 'VOIDD',
p_person_id => l_ptnl.person_id,
p_business_group_id => l_ptnl.business_group_id,
p_object_version_number => l_ptnl.object_version_number,
p_effective_date => p_effective_date,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_request_id => fnd_global.conc_request_id,
p_program_update_date => SYSDATE,
p_voidd_dt => p_effective_date
);
hr_utility.set_location ('After update_ptnl_ler_for_per_perf ', 111);
hr_utility.set_location ('Before update_pil_elctbl_chc_popl ', 111);
ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl (p_validate => FALSE,
p_pil_elctbl_chc_popl_id => l_pel_pk_id,
p_pil_elctbl_popl_stat_cd => 'BCKDT',
p_object_version_number => l_pel_object_version_number,
p_effective_date => p_effective_date
);
hr_utility.set_location ('After update_pil_elctbl_chc_popl ', 111);
3.Update person_life_event (PIL)
4.update potential Life event (PPL)
5.update pil_electbl_choice_popl (PEL)
***/
-- Step 1
IF p_restore_per_in_ler_id IS NOT NULL
THEN
OPEN c_pil (p_restore_per_in_ler_id);
hr_utility.set_location ('Before update_person_life_event ', 222);
ben_person_life_event_api.update_person_life_event (p_per_in_ler_id => p_restore_per_in_ler_id,
p_bckt_per_in_ler_id => NULL,
p_per_in_ler_stat_cd => 'STRTD',
p_prvs_stat_cd => l_pil.per_in_ler_stat_cd,
p_object_version_number => l_pil.object_version_number,
p_effective_date => p_effective_date,
p_procd_dt => l_procd_dt,
p_strtd_dt => l_strtd_dt,
p_voidd_dt => l_voidd_dt
);
hr_utility.set_location ('After update_person_life_event ', 222);
hr_utility.set_location ('Before update_ptnl_ler_for_per_perf ', 222);
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per_perf (p_validate => FALSE,
p_ptnl_ler_for_per_id => l_ptnl.ptnl_ler_for_per_id,
p_ptnl_ler_for_per_stat_cd => 'PROCD',
p_lf_evt_ocrd_dt => l_pil.lf_evt_ocrd_dt,
p_procd_dt => l_pil.lf_evt_ocrd_dt,
p_person_id => l_ptnl.person_id,
p_business_group_id => l_ptnl.business_group_id,
p_object_version_number => l_ptnl.object_version_number,
p_effective_date => p_effective_date,
p_program_application_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_request_id => fnd_global.conc_request_id,
p_program_update_date => SYSDATE
);
hr_utility.set_location ('After update_ptnl_ler_for_per_perf ', 222);
hr_utility.set_location ('Before update_pil_elctbl_chc_popl ', 222);
ben_pil_elctbl_chc_popl_api.update_pil_elctbl_chc_popl (p_validate => FALSE,
p_pil_elctbl_chc_popl_id => l_pel_pk_id,
p_pil_elctbl_popl_stat_cd => 'STRTD',
p_object_version_number => l_pel_object_version_number,
p_effective_date => p_effective_date
);
hr_utility.set_location ('After update_pil_elctbl_chc_popl ', 222);