[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT opt_id
FROM ben_oipl_f
WHERE oipl_id = p_oipl_id
AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
SELECT epo.elig_flag
FROM ben_elig_per_opt_f epo, ben_per_in_ler pil, ben_elig_per_f pep
WHERE pep.person_id = c_person_id
AND pep.pl_id = c_pl_id
AND epo.opt_id = c_opt_id
AND pep.elig_per_id = epo.elig_per_id
AND NVL(pep.pgm_id,-1) = NVL(c_pgm_id,-1)
AND epo.effective_start_date BETWEEN pep.effective_start_date
AND pep.effective_end_date
AND epo.effective_start_date < c_effective_date
AND epo.per_in_ler_id <> p_per_in_ler_id
AND pil.per_in_ler_id = epo.per_in_ler_id
AND pil.business_group_id = epo.business_group_id
AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
ORDER BY epo.effective_start_date DESC;
SELECT pep.elig_flag
FROM ben_elig_per_f pep, ben_per_in_ler pil
WHERE pep.person_id = c_person_id
AND NVL (pep.pgm_id, -1) = NVL (c_pgm_id, -1)
-- bug 5947036/ bug 6379215
AND pep.pl_id = c_pl_id
-- AND NVL (pep.pl_id, -1) = c_pl_id
--AND pep.plip_id IS NULL
--AND NVL (pep.ptip_id, -1) = nvl(c_ptip_id,-1)
AND pep.effective_start_date < c_effective_date
AND pep.per_in_ler_id <> p_per_in_ler_id
AND pil.per_in_ler_id = pep.per_in_ler_id
AND pil.business_group_id = pep.business_group_id
AND pil.per_in_ler_stat_cd IN ('STRTD', 'PROCD')
ORDER BY pep.effective_start_date DESC;
SELECT elig_per_elctbl_chc_id,
pl_id,
pgm_id,
oipl_id
FROM ben_elig_per_elctbl_chc
WHERE per_in_ler_id = p_per_in_ler_id AND elctbl_flag = 'Y';
select pen.*
from ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe
where pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
and pen.per_in_ler_id <> p_per_in_ler_id -- 5365585 . result ended in the same life event will not be picked up .
and epe.per_in_ler_id = p_per_in_ler_id
and epe.CRNTLY_ENRD_FLAG = 'Y'
-- and epe.ELCTBL_FLAG = 'Y'
and epe.BUSINESS_GROUP_ID = p_business_group_id
--Bug 5102337. we need to exclude interim enrollments being selected here.
and not exists ( select 'x'
from ben_prtt_enrt_rslt_f susp
where susp.RPLCS_SSPNDD_RSLT_ID = pen.prtt_enrt_rslt_id
and susp.effective_end_date <> hr_api.g_eot
and susp.prtt_enrt_rslt_stat_cd is null
and susp.enrt_cvg_thru_dt = hr_api.g_eot ) ;
select pen.*
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.person_id = p_person_id
and pen.effective_end_date = hr_api.g_eot
and pen.business_group_id = p_business_group_id
and pil.business_group_id = p_business_group_id
and pen.enrt_cvg_strt_dt > p_enrt_cvg_thru_dt
and pen.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
and pen.pl_typ_id = p_pl_typ_id -- 5376652
--and nvl(pen.oipl_id,-1) = nvl(p_oipl_id,-1) -- 5376652
and pen.prtt_enrt_rslt_stat_cd is null;
select epe.pgm_id,
epe.pl_typ_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.pgm_id is not null
and epe.business_group_id = p_business_group_id
group by pgm_id, pl_typ_id;
select prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id in
(select pen.prtt_enrt_rslt_id
from ben_prtt_enrt_rslt_f pen
where pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
and pen.person_id = p_person_id
and pen.pgm_id <> p_pgm_id
and pen.enrt_cvg_thru_dt < p_lf_evt_ocrd_dt
and pen.prtt_enrt_rslt_stat_cd is null
and pen.per_in_ler_id = p_per_in_ler_id
and pen.business_group_id = p_business_group_id
and not exists (select null from ben_elig_per_elctbl_chc
where per_in_ler_id = p_per_in_ler_id
and pgm_id = pen.pgm_id))
and prv.prtt_rt_val_stat_cd is null;
select pen.*
from ben_prtt_enrt_rslt_f pen
where pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
and pen.person_id = p_person_id
and pen.pgm_id <> p_pgm_id
and pen.pl_typ_id = p_pl_typ_id
and p_lf_evt_ocrd_dt between pen.enrt_cvg_strt_dt and
pen.enrt_cvg_thru_dt
and pen.prtt_enrt_rslt_stat_cd is null
and pen.per_in_ler_id <> p_per_in_ler_id
and pen.business_group_id = p_business_group_id
and not exists (select null from ben_elig_per_elctbl_chc
where per_in_ler_id = p_per_in_ler_id
and pgm_id = pen.pgm_id);
select pen.effective_start_date,
pen.object_version_number
from ben_prtt_enrt_rslt_f pen
where pen.effective_end_date = hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
select prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.ended_per_in_ler_id = p_per_in_ler_id
and prv.acty_base_rt_id = p_acty_base_rt_id
and prv.prtt_rt_val_stat_cd is not null;
select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id in (select per_in_ler_id
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id)
and epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
select 'Y'
from ben_per_in_ler pil
where pil.per_in_ler_id <> p_per_in_ler_id
and trunc(p_lf_evt_ocrd_dt, 'MM') = trunc(pil.lf_evt_ocrd_dt, 'MM')
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select prv.*
,abr.element_type_id
,abr.input_value_id
,pen.person_id
from ben_prtt_enrt_rslt_f pen
,ben_prtt_rt_val prv
,ben_acty_base_rt_f abr
where pen.effective_end_date = hr_api.g_eot
and pen.enrt_cvg_thru_dt <> hr_api.g_eot
and pen.prtt_enrt_rslt_stat_cd is null
and pen.person_id = p_person_id
and pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
and pen.ptip_id = p_ptip_id
and prv.prtt_rt_val_stat_cd is null
and prv.rt_end_dt >= p_rt_end_dt
and prv.acty_base_rt_id = abr.acty_base_rt_id
and p_effective_date between abr.effective_start_date
and abr.effective_end_date;
select pen.*
from ben_prtt_enrt_rslt_f pen
,ben_ptip_f ptip
where pen.effective_end_date = hr_api.g_eot -- '31-dec-4712'
and pen.enrt_cvg_thru_dt <> hr_api.g_eot -- '31-dec-4712'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.person_id = p_person_id -- 318321
and pen.business_group_id = p_business_group_id -- 81545
and pen.ptip_id = p_ptip_id -- 54444
and pen.enrt_cvg_thru_dt >= p_cvg_end_dt -- '20-jan-2008'
and pen.ptip_id = ptip.ptip_id
and p_effective_date between ptip.effective_start_date
and ptip.effective_end_date;
select distinct epe.pgm_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id;
select pgi_information1
from ben_pgm_extra_info
where information_type = 'ADJ_RATE_PREV_LF_EVT'
and pgm_id = p_pgm_id;
select pgi_information1
from ben_pgm_extra_info
where information_type = 'ADJ_CVG_PREV_LF_EVT'
and pgm_id = p_pgm_id;
select min(ecr.rt_strt_dt) rt_strt_dt
,epe.ptip_id
from ben_elig_per_elctbl_chc epe
,ben_enrt_rt ecr
,ben_enrt_bnft enb
where epe.per_in_ler_id = p_per_in_ler_id
and epe.business_group_id = p_business_group_id
and decode(ecr.enrt_bnft_id, null, ecr.elig_per_elctbl_chc_id,
enb.elig_per_elctbl_chc_id) = epe.elig_per_elctbl_chc_id
and enb.enrt_bnft_id (+) = ecr.enrt_bnft_id
and ecr.rt_strt_dt is not null
and ecr.business_group_id = p_business_group_id
group by epe.ptip_id;
select min(epe.enrt_cvg_strt_dt) enrt_cvg_strt_dt
,epe.ptip_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.business_group_id = p_business_group_id
group by epe.ptip_id;
select null
from ben_le_clsn_n_rstr
where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
AND BKUP_TBL_ID = p_prtt_rt_val_id
AND PER_IN_LER_ID = p_per_in_ler_id;
select null
from ben_le_clsn_n_rstr
where BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ'
AND BKUP_TBL_ID = p_prtt_enrt_rslt_id
AND PER_IN_LER_ID = p_per_in_ler_id;
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
COMP_LVL_CD,
LCR_ATTRIBUTE16,
LCR_ATTRIBUTE17,
LCR_ATTRIBUTE18,
LCR_ATTRIBUTE19,
LCR_ATTRIBUTE20,
LCR_ATTRIBUTE21,
LCR_ATTRIBUTE22,
LCR_ATTRIBUTE23,
LCR_ATTRIBUTE24,
LCR_ATTRIBUTE25,
LCR_ATTRIBUTE26,
LCR_ATTRIBUTE27,
LCR_ATTRIBUTE28,
LCR_ATTRIBUTE29,
LCR_ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
BKUP_TBL_ID, -- PRTT_ENRT_RSLT_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ENRT_CVG_STRT_DT,
ENRT_CVG_THRU_DT,
SSPNDD_FLAG,
PRTT_IS_CVRD_FLAG,
BNFT_AMT,
BNFT_NNMNTRY_UOM,
BNFT_TYP_CD,
UOM,
ORGNL_ENRT_DT,
ENRT_MTHD_CD,
ENRT_OVRIDN_FLAG,
ENRT_OVRID_RSN_CD,
ERLST_DEENRT_DT,
ENRT_OVRID_THRU_DT,
NO_LNGR_ELIG_FLAG,
BNFT_ORDR_NUM,
PERSON_ID,
ASSIGNMENT_ID,
PGM_ID,
PRTT_ENRT_RSLT_STAT_CD,
PL_ID,
OIPL_ID,
PTIP_ID,
PL_TYP_ID,
LER_ID,
PER_IN_LER_ID,
RPLCS_SSPNDD_RSLT_ID,
BUSINESS_GROUP_ID,
LCR_ATTRIBUTE_CATEGORY,
LCR_ATTRIBUTE1,
LCR_ATTRIBUTE2,
LCR_ATTRIBUTE3,
LCR_ATTRIBUTE4,
LCR_ATTRIBUTE5,
LCR_ATTRIBUTE6,
LCR_ATTRIBUTE7,
LCR_ATTRIBUTE8,
LCR_ATTRIBUTE9,
LCR_ATTRIBUTE10,
LCR_ATTRIBUTE11,
LCR_ATTRIBUTE12,
LCR_ATTRIBUTE13,
LCR_ATTRIBUTE14,
LCR_ATTRIBUTE15 ,
PER_IN_LER_ENDED_ID,
PL_ORDR_NUM,
PLIP_ORDR_NUM,
PTIP_ORDR_NUM,
OIPL_ORDR_NUM)
values (
'BEN_PRTT_ENRT_RSLT_F_DEL',
l_ended_result.COMP_LVL_CD,
l_ended_result.PEN_ATTRIBUTE16,
l_ended_result.PEN_ATTRIBUTE17,
l_ended_result.PEN_ATTRIBUTE18,
l_ended_result.PEN_ATTRIBUTE19,
l_ended_result.PEN_ATTRIBUTE20,
l_ended_result.PEN_ATTRIBUTE21,
l_ended_result.PEN_ATTRIBUTE22,
l_ended_result.PEN_ATTRIBUTE23,
l_ended_result.PEN_ATTRIBUTE24,
l_ended_result.PEN_ATTRIBUTE25,
l_ended_result.PEN_ATTRIBUTE26,
l_ended_result.PEN_ATTRIBUTE27,
l_ended_result.PEN_ATTRIBUTE28,
l_ended_result.PEN_ATTRIBUTE29,
l_ended_result.PEN_ATTRIBUTE30,
l_ended_result.LAST_UPDATE_DATE,
l_ended_result.LAST_UPDATED_BY,
l_ended_result.LAST_UPDATE_LOGIN,
l_ended_result.CREATED_BY,
l_ended_result.CREATION_DATE,
l_ended_result.REQUEST_ID,
l_ended_result.PROGRAM_APPLICATION_ID,
l_ended_result.PROGRAM_ID,
l_ended_result.PROGRAM_UPDATE_DATE,
l_ended_result.OBJECT_VERSION_NUMBER,
l_ended_result.PRTT_ENRT_RSLT_ID,
l_ended_result.EFFECTIVE_START_DATE,
l_ended_result.EFFECTIVE_END_DATE,
l_ended_result.ENRT_CVG_STRT_DT,
l_ended_result.ENRT_CVG_THRU_DT,
l_ended_result.SSPNDD_FLAG,
l_ended_result.PRTT_IS_CVRD_FLAG,
l_ended_result.BNFT_AMT,
l_ended_result.BNFT_NNMNTRY_UOM,
l_ended_result.BNFT_TYP_CD,
l_ended_result.UOM,
l_ended_result.ORGNL_ENRT_DT,
l_ended_result.ENRT_MTHD_CD,
l_ended_result.ENRT_OVRIDN_FLAG,
l_ended_result.ENRT_OVRID_RSN_CD,
l_ended_result.ERLST_DEENRT_DT,
l_ended_result.ENRT_OVRID_THRU_DT,
l_ended_result.NO_LNGR_ELIG_FLAG,
l_ended_result.BNFT_ORDR_NUM,
l_ended_result.PERSON_ID,
l_ended_result.ASSIGNMENT_ID,
l_ended_result.PGM_ID,
l_ended_result.PRTT_ENRT_RSLT_STAT_CD,
l_ended_result.PL_ID,
l_ended_result.OIPL_ID,
l_ended_result.PTIP_ID,
l_ended_result.PL_TYP_ID,
l_ended_result.LER_ID,
l_ended_result.PER_IN_LER_ID,
l_ended_result.RPLCS_SSPNDD_RSLT_ID,
l_ended_result.BUSINESS_GROUP_ID,
l_ended_result.PEN_ATTRIBUTE_CATEGORY,
l_ended_result.PEN_ATTRIBUTE1,
l_ended_result.PEN_ATTRIBUTE2,
l_ended_result.PEN_ATTRIBUTE3,
l_ended_result.PEN_ATTRIBUTE4,
l_ended_result.PEN_ATTRIBUTE5,
l_ended_result.PEN_ATTRIBUTE6,
l_ended_result.PEN_ATTRIBUTE7,
l_ended_result.PEN_ATTRIBUTE8,
l_ended_result.PEN_ATTRIBUTE9,
l_ended_result.PEN_ATTRIBUTE10,
l_ended_result.PEN_ATTRIBUTE11,
l_ended_result.PEN_ATTRIBUTE12,
l_ended_result.PEN_ATTRIBUTE13,
l_ended_result.PEN_ATTRIBUTE14,
l_ended_result.PEN_ATTRIBUTE15,
p_per_in_ler_id,
l_ended_result.PL_ORDR_NUM,
l_ended_result.PLIP_ORDR_NUM,
l_ended_result.PTIP_ORDR_NUM,
l_ended_result.OIPL_ORDR_NUM
);
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
COMP_LVL_CD,
LCR_ATTRIBUTE16,
LCR_ATTRIBUTE17,
LCR_ATTRIBUTE18,
LCR_ATTRIBUTE19,
LCR_ATTRIBUTE20,
LCR_ATTRIBUTE21,
LCR_ATTRIBUTE22,
LCR_ATTRIBUTE23,
LCR_ATTRIBUTE24,
LCR_ATTRIBUTE25,
LCR_ATTRIBUTE26,
LCR_ATTRIBUTE27,
LCR_ATTRIBUTE28,
LCR_ATTRIBUTE29,
LCR_ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
BKUP_TBL_ID, -- PRTT_ENRT_RSLT_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ENRT_CVG_STRT_DT,
ENRT_CVG_THRU_DT,
SSPNDD_FLAG,
PRTT_IS_CVRD_FLAG,
BNFT_AMT,
BNFT_NNMNTRY_UOM,
BNFT_TYP_CD,
UOM,
ORGNL_ENRT_DT,
ENRT_MTHD_CD,
ENRT_OVRIDN_FLAG,
ENRT_OVRID_RSN_CD,
ERLST_DEENRT_DT,
ENRT_OVRID_THRU_DT,
NO_LNGR_ELIG_FLAG,
BNFT_ORDR_NUM,
PERSON_ID,
ASSIGNMENT_ID,
PGM_ID,
PRTT_ENRT_RSLT_STAT_CD,
PL_ID,
OIPL_ID,
PTIP_ID,
PL_TYP_ID,
LER_ID,
PER_IN_LER_ID,
RPLCS_SSPNDD_RSLT_ID,
BUSINESS_GROUP_ID,
LCR_ATTRIBUTE_CATEGORY,
LCR_ATTRIBUTE1,
LCR_ATTRIBUTE2,
LCR_ATTRIBUTE3,
LCR_ATTRIBUTE4,
LCR_ATTRIBUTE5,
LCR_ATTRIBUTE6,
LCR_ATTRIBUTE7,
LCR_ATTRIBUTE8,
LCR_ATTRIBUTE9,
LCR_ATTRIBUTE10,
LCR_ATTRIBUTE11,
LCR_ATTRIBUTE12,
LCR_ATTRIBUTE13,
LCR_ATTRIBUTE14,
LCR_ATTRIBUTE15 ,
PER_IN_LER_ENDED_ID,
PL_ORDR_NUM,
PLIP_ORDR_NUM,
PTIP_ORDR_NUM,
OIPL_ORDR_NUM)
values (
'BEN_PRTT_ENRT_RSLT_F_DEL',
l_future_results.COMP_LVL_CD,
l_future_results.PEN_ATTRIBUTE16,
l_future_results.PEN_ATTRIBUTE17,
l_future_results.PEN_ATTRIBUTE18,
l_future_results.PEN_ATTRIBUTE19,
l_future_results.PEN_ATTRIBUTE20,
l_future_results.PEN_ATTRIBUTE21,
l_future_results.PEN_ATTRIBUTE22,
l_future_results.PEN_ATTRIBUTE23,
l_future_results.PEN_ATTRIBUTE24,
l_future_results.PEN_ATTRIBUTE25,
l_future_results.PEN_ATTRIBUTE26,
l_future_results.PEN_ATTRIBUTE27,
l_future_results.PEN_ATTRIBUTE28,
l_future_results.PEN_ATTRIBUTE29,
l_future_results.PEN_ATTRIBUTE30,
l_future_results.LAST_UPDATE_DATE,
l_future_results.LAST_UPDATED_BY,
l_future_results.LAST_UPDATE_LOGIN,
l_future_results.CREATED_BY,
l_future_results.CREATION_DATE,
l_future_results.REQUEST_ID,
l_future_results.PROGRAM_APPLICATION_ID,
l_future_results.PROGRAM_ID,
l_future_results.PROGRAM_UPDATE_DATE,
l_future_results.OBJECT_VERSION_NUMBER,
l_future_results.PRTT_ENRT_RSLT_ID,
l_future_results.EFFECTIVE_START_DATE,
l_future_results.EFFECTIVE_END_DATE,
l_future_results.ENRT_CVG_STRT_DT,
l_future_results.ENRT_CVG_THRU_DT,
l_future_results.SSPNDD_FLAG,
l_future_results.PRTT_IS_CVRD_FLAG,
l_future_results.BNFT_AMT,
l_future_results.BNFT_NNMNTRY_UOM,
l_future_results.BNFT_TYP_CD,
l_future_results.UOM,
l_future_results.ORGNL_ENRT_DT,
l_future_results.ENRT_MTHD_CD,
l_future_results.ENRT_OVRIDN_FLAG,
l_future_results.ENRT_OVRID_RSN_CD,
l_future_results.ERLST_DEENRT_DT,
l_future_results.ENRT_OVRID_THRU_DT,
l_future_results.NO_LNGR_ELIG_FLAG,
l_future_results.BNFT_ORDR_NUM,
l_future_results.PERSON_ID,
l_future_results.ASSIGNMENT_ID,
l_future_results.PGM_ID,
l_future_results.PRTT_ENRT_RSLT_STAT_CD,
l_future_results.PL_ID,
l_future_results.OIPL_ID,
l_future_results.PTIP_ID,
l_future_results.PL_TYP_ID,
l_future_results.LER_ID,
l_future_results.PER_IN_LER_ID,
l_future_results.RPLCS_SSPNDD_RSLT_ID,
l_future_results.BUSINESS_GROUP_ID,
l_future_results.PEN_ATTRIBUTE_CATEGORY,
l_future_results.PEN_ATTRIBUTE1,
l_future_results.PEN_ATTRIBUTE2,
l_future_results.PEN_ATTRIBUTE3,
l_future_results.PEN_ATTRIBUTE4,
l_future_results.PEN_ATTRIBUTE5,
l_future_results.PEN_ATTRIBUTE6,
l_future_results.PEN_ATTRIBUTE7,
l_future_results.PEN_ATTRIBUTE8,
l_future_results.PEN_ATTRIBUTE9,
l_future_results.PEN_ATTRIBUTE10,
l_future_results.PEN_ATTRIBUTE11,
l_future_results.PEN_ATTRIBUTE12,
l_future_results.PEN_ATTRIBUTE13,
l_future_results.PEN_ATTRIBUTE14,
l_future_results.PEN_ATTRIBUTE15,
p_per_in_ler_id,
l_future_results.PL_ORDR_NUM,
l_future_results.PLIP_ORDR_NUM,
l_future_results.PTIP_ORDR_NUM,
l_future_results.OIPL_ORDR_NUM
);
update ben_enrt_rt set prtt_rt_val_id = null
where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id;
update ben_elig_per_elctbl_chc set prtt_enrt_rslt_id = null,
CRNTLY_ENRD_FLAG = 'N'
where prtt_enrt_rslt_id = l_future_results.prtt_enrt_rslt_id
and per_in_ler_id = p_per_in_ler_id;
hr_utility.set_location('Update prtt rt val'||l_ended_prv.prtt_rt_val_id,100);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_ended_prv.prtt_rt_val_id
,P_RT_END_DT => l_prv.rt_end_dt
,p_person_id => p_person_id
,p_ended_per_in_ler_id => p_per_in_ler_id
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_ended_prv.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
COMP_LVL_CD,
LCR_ATTRIBUTE16,
LCR_ATTRIBUTE17,
LCR_ATTRIBUTE18,
LCR_ATTRIBUTE19,
LCR_ATTRIBUTE20,
LCR_ATTRIBUTE21,
LCR_ATTRIBUTE22,
LCR_ATTRIBUTE23,
LCR_ATTRIBUTE24,
LCR_ATTRIBUTE25,
LCR_ATTRIBUTE26,
LCR_ATTRIBUTE27,
LCR_ATTRIBUTE28,
LCR_ATTRIBUTE29,
LCR_ATTRIBUTE30,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OBJECT_VERSION_NUMBER,
BKUP_TBL_ID, -- PRTT_ENRT_RSLT_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
ENRT_CVG_STRT_DT,
ENRT_CVG_THRU_DT,
SSPNDD_FLAG,
PRTT_IS_CVRD_FLAG,
BNFT_AMT,
BNFT_NNMNTRY_UOM,
BNFT_TYP_CD,
UOM,
ORGNL_ENRT_DT,
ENRT_MTHD_CD,
ENRT_OVRIDN_FLAG,
ENRT_OVRID_RSN_CD,
ERLST_DEENRT_DT,
ENRT_OVRID_THRU_DT,
NO_LNGR_ELIG_FLAG,
BNFT_ORDR_NUM,
PERSON_ID,
ASSIGNMENT_ID,
PGM_ID,
PRTT_ENRT_RSLT_STAT_CD,
PL_ID,
OIPL_ID,
PTIP_ID,
PL_TYP_ID,
LER_ID,
PER_IN_LER_ID,
RPLCS_SSPNDD_RSLT_ID,
BUSINESS_GROUP_ID,
LCR_ATTRIBUTE_CATEGORY,
LCR_ATTRIBUTE1,
LCR_ATTRIBUTE2,
LCR_ATTRIBUTE3,
LCR_ATTRIBUTE4,
LCR_ATTRIBUTE5,
LCR_ATTRIBUTE6,
LCR_ATTRIBUTE7,
LCR_ATTRIBUTE8,
LCR_ATTRIBUTE9,
LCR_ATTRIBUTE10,
LCR_ATTRIBUTE11,
LCR_ATTRIBUTE12,
LCR_ATTRIBUTE13,
LCR_ATTRIBUTE14,
LCR_ATTRIBUTE15 ,
PER_IN_LER_ENDED_ID,
PL_ORDR_NUM,
PLIP_ORDR_NUM,
PTIP_ORDR_NUM,
OIPL_ORDR_NUM)
values (
'BEN_PRTT_ENRT_RSLT_F_DEL',
l_ended_result2.COMP_LVL_CD,
l_ended_result2.PEN_ATTRIBUTE16,
l_ended_result2.PEN_ATTRIBUTE17,
l_ended_result2.PEN_ATTRIBUTE18,
l_ended_result2.PEN_ATTRIBUTE19,
l_ended_result2.PEN_ATTRIBUTE20,
l_ended_result2.PEN_ATTRIBUTE21,
l_ended_result2.PEN_ATTRIBUTE22,
l_ended_result2.PEN_ATTRIBUTE23,
l_ended_result2.PEN_ATTRIBUTE24,
l_ended_result2.PEN_ATTRIBUTE25,
l_ended_result2.PEN_ATTRIBUTE26,
l_ended_result2.PEN_ATTRIBUTE27,
l_ended_result2.PEN_ATTRIBUTE28,
l_ended_result2.PEN_ATTRIBUTE29,
l_ended_result2.PEN_ATTRIBUTE30,
l_ended_result2.LAST_UPDATE_DATE,
l_ended_result2.LAST_UPDATED_BY,
l_ended_result2.LAST_UPDATE_LOGIN,
l_ended_result2.CREATED_BY,
l_ended_result2.CREATION_DATE,
l_ended_result2.REQUEST_ID,
l_ended_result2.PROGRAM_APPLICATION_ID,
l_ended_result2.PROGRAM_ID,
l_ended_result2.PROGRAM_UPDATE_DATE,
l_ended_result2.OBJECT_VERSION_NUMBER,
l_ended_result2.PRTT_ENRT_RSLT_ID,
l_ended_result2.EFFECTIVE_START_DATE,
l_ended_result2.EFFECTIVE_END_DATE,
l_ended_result2.ENRT_CVG_STRT_DT,
l_ended_result2.ENRT_CVG_THRU_DT,
l_ended_result2.SSPNDD_FLAG,
l_ended_result2.PRTT_IS_CVRD_FLAG,
l_ended_result2.BNFT_AMT,
l_ended_result2.BNFT_NNMNTRY_UOM,
l_ended_result2.BNFT_TYP_CD,
l_ended_result2.UOM,
l_ended_result2.ORGNL_ENRT_DT,
l_ended_result2.ENRT_MTHD_CD,
l_ended_result2.ENRT_OVRIDN_FLAG,
l_ended_result2.ENRT_OVRID_RSN_CD,
l_ended_result2.ERLST_DEENRT_DT,
l_ended_result2.ENRT_OVRID_THRU_DT,
l_ended_result2.NO_LNGR_ELIG_FLAG,
l_ended_result2.BNFT_ORDR_NUM,
l_ended_result2.PERSON_ID,
l_ended_result2.ASSIGNMENT_ID,
l_ended_result2.PGM_ID,
l_ended_result2.PRTT_ENRT_RSLT_STAT_CD,
l_ended_result2.PL_ID,
l_ended_result2.OIPL_ID,
l_ended_result2.PTIP_ID,
l_ended_result2.PL_TYP_ID,
l_ended_result2.LER_ID,
l_ended_result2.PER_IN_LER_ID,
l_ended_result2.RPLCS_SSPNDD_RSLT_ID,
l_ended_result2.BUSINESS_GROUP_ID,
l_ended_result2.PEN_ATTRIBUTE_CATEGORY,
l_ended_result2.PEN_ATTRIBUTE1,
l_ended_result2.PEN_ATTRIBUTE2,
l_ended_result2.PEN_ATTRIBUTE3,
l_ended_result2.PEN_ATTRIBUTE4,
l_ended_result2.PEN_ATTRIBUTE5,
l_ended_result2.PEN_ATTRIBUTE6,
l_ended_result2.PEN_ATTRIBUTE7,
l_ended_result2.PEN_ATTRIBUTE8,
l_ended_result2.PEN_ATTRIBUTE9,
l_ended_result2.PEN_ATTRIBUTE10,
l_ended_result2.PEN_ATTRIBUTE11,
l_ended_result2.PEN_ATTRIBUTE12,
l_ended_result2.PEN_ATTRIBUTE13,
l_ended_result2.PEN_ATTRIBUTE14,
l_ended_result2.PEN_ATTRIBUTE15,
p_per_in_ler_id,
l_ended_result2.PL_ORDR_NUM,
l_ended_result2.PLIP_ORDR_NUM,
l_ended_result2.PTIP_ORDR_NUM,
l_ended_result2.OIPL_ORDR_NUM
);
hr_utility.set_location ('Delete enrollment ',101);
ben_prtt_enrt_result_api.delete_enrollment
(p_validate => false ,
p_prtt_enrt_rslt_id => l_ended_result2.prtt_enrt_rslt_id,
p_per_in_ler_id => p_per_in_ler_id,
p_business_group_id => p_business_group_id ,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_pen.object_version_number,
p_effective_date => l_pen.effective_start_date,
p_datetrack_mode => 'DELETE',
p_multi_row_validate => false);
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
BKUP_TBL_ID,
per_in_ler_id,
person_id,
RT_END_DT,
business_group_id,
object_version_number)
values (
'BEN_PRTT_RT_VAL_ADJ',
l_pen.prtt_rt_val_id,
p_per_in_ler_id,
l_pen.person_id,
l_pen.rt_end_dt,
p_business_group_id,
l_pen.object_version_number
);
ben_prtt_rt_val_api.update_prtt_rt_val
(P_VALIDATE => FALSE
,P_PRTT_RT_VAL_ID => l_pen.prtt_rt_val_id
,P_RT_END_DT => l_epe.rt_strt_dt - 1
,p_person_id => l_pen.person_id
,p_input_value_id => l_pen.input_value_id
,p_element_type_id => l_pen.element_type_id
,p_business_group_id => p_business_group_id
,P_OBJECT_VERSION_NUMBER => l_pen.object_version_number
,P_EFFECTIVE_DATE => p_effective_date
);
insert into BEN_LE_CLSN_N_RSTR (
BKUP_TBL_TYP_CD,
BKUP_TBL_ID,
per_in_ler_id,
person_id,
ENRT_CVG_THRU_DT,
business_group_id,
object_version_number)
values (
'BEN_PRTT_ENRT_RSLT_F_ADJ',
l_get_enrt_rslts_for_pen.prtt_enrt_rslt_id,
p_per_in_ler_id,
l_get_enrt_rslts_for_pen.person_id,
l_get_enrt_rslts_for_pen.enrt_cvg_thru_dt,
p_business_group_id,
l_get_enrt_rslts_for_pen.object_version_number
);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_validate => FALSE,
p_prtt_enrt_rslt_id => l_get_enrt_rslts_for_pen.prtt_enrt_rslt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_business_group_id => p_business_group_id,
p_object_version_number => l_get_enrt_rslts_for_pen.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_correction,
p_multi_row_validate => FALSE,
p_enrt_cvg_thru_dt => l_get_elctbl_chc_for_cvg.enrt_cvg_strt_dt - 1
);