[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
pen.EFFECTIVE_END_DATE,
pen.ASSIGNMENT_ID,
pen.BNFT_AMT,
pen.BNFT_NNMNTRY_UOM,
pen.BNFT_ORDR_NUM,
pen.BNFT_TYP_CD,
pen.BUSINESS_GROUP_ID,
pen.COMP_LVL_CD,
pen.CREATED_BY,
pen.CREATION_DATE,
pen.EFFECTIVE_START_DATE,
pen.ENRT_CVG_STRT_DT,
pen.ENRT_CVG_THRU_DT,
pen.ENRT_MTHD_CD,
pen.ENRT_OVRIDN_FLAG,
pen.ENRT_OVRID_RSN_CD,
pen.ENRT_OVRID_THRU_DT,
pen.ERLST_DEENRT_DT,
pen.LAST_UPDATED_BY,
pen.LAST_UPDATE_DATE,
pen.LAST_UPDATE_LOGIN,
pen.LER_ID,
pen.NO_LNGR_ELIG_FLAG,
pen.OBJECT_VERSION_NUMBER,
pen.OIPL_ID,
pen.OIPL_ORDR_NUM,
pen.ORGNL_ENRT_DT,
pen.PEN_ATTRIBUTE1,
pen.PEN_ATTRIBUTE10,
pen.PEN_ATTRIBUTE11,
pen.PEN_ATTRIBUTE12,
pen.PEN_ATTRIBUTE13,
pen.PEN_ATTRIBUTE14,
pen.PEN_ATTRIBUTE15,
pen.PEN_ATTRIBUTE16,
pen.PEN_ATTRIBUTE17,
pen.PEN_ATTRIBUTE18,
pen.PEN_ATTRIBUTE19,
pen.PEN_ATTRIBUTE2,
pen.PEN_ATTRIBUTE20,
pen.PEN_ATTRIBUTE21,
pen.PEN_ATTRIBUTE22,
pen.PEN_ATTRIBUTE23,
pen.PEN_ATTRIBUTE24,
pen.PEN_ATTRIBUTE25,
pen.PEN_ATTRIBUTE26,
pen.PEN_ATTRIBUTE27,
pen.PEN_ATTRIBUTE28,
pen.PEN_ATTRIBUTE29,
pen.PEN_ATTRIBUTE3,
pen.PEN_ATTRIBUTE30,
pen.PEN_ATTRIBUTE4,
pen.PEN_ATTRIBUTE5,
pen.PEN_ATTRIBUTE6,
pen.PEN_ATTRIBUTE7,
pen.PEN_ATTRIBUTE8,
pen.PEN_ATTRIBUTE9,
pen.PEN_ATTRIBUTE_CATEGORY,
pen.PERSON_ID,
pen.PER_IN_LER_ID,
pen.PGM_ID,
pen.PLIP_ORDR_NUM,
pen.PL_ID,
pen.PL_ORDR_NUM,
pen.PL_TYP_ID,
pen.PROGRAM_APPLICATION_ID,
pen.PROGRAM_ID,
pen.PROGRAM_UPDATE_DATE,
pen.PRTT_ENRT_RSLT_ID,
pen.PRTT_ENRT_RSLT_STAT_CD,
pen.PRTT_IS_CVRD_FLAG,
pen.PTIP_ID,
pen.PTIP_ORDR_NUM,
pen.REQUEST_ID,
pen.RPLCS_SSPNDD_RSLT_ID,
pen.SSPNDD_FLAG,
pen.UOM
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
where pil.per_in_ler_id = c_bckdt_per_in_ler_id
and pil.person_id = c_person_id
and pil.per_in_ler_id = pen.per_in_ler_id
and pil.per_in_ler_id = pel.per_in_ler_id
and ((pel.pgm_id is null and c_pgm_id is null) or
pel.pgm_id = c_pgm_id )
and ( c_pl_id is null or
pel.pl_id = c_pl_id )
and (pel.dflt_asnd_dt is not null or
pel.elcns_made_dt is not null or
pel.auto_asnd_dt is not null) -- Bug 8305552, Check for Automatic Enrolled Plans
--To Handle Programs and Plans not in Program
and ((pen.pgm_id is null and c_pgm_id is null) or
pen.pgm_id = c_pgm_id )
and ( c_pl_id is null or
pen.pl_id = c_pl_id )
and (pen.effective_end_date = hr_api.g_eot or
pen.effective_end_date = (select max(effective_end_date)
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id =
pen.prtt_enrt_rslt_id))
and (pen.enrt_cvg_thru_dt is null or
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and pen.prtt_enrt_rslt_id not in (
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_prtt_enrt_rslt_f pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
and pil_inner.person_id = c_person_id
and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
union
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_le_clsn_n_rstr pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
and pil_inner.person_id = c_person_id
AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
)
union
select
pen.EFFECTIVE_END_DATE,
pen.ASSIGNMENT_ID,
pen.BNFT_AMT,
pen.BNFT_NNMNTRY_UOM,
pen.BNFT_ORDR_NUM,
pen.BNFT_TYP_CD,
pen.BUSINESS_GROUP_ID,
pen.COMP_LVL_CD,
pen.CREATED_BY,
pen.CREATION_DATE,
pen.EFFECTIVE_START_DATE,
pen.ENRT_CVG_STRT_DT,
pen.ENRT_CVG_THRU_DT,
pen.ENRT_MTHD_CD,
pen.ENRT_OVRIDN_FLAG,
pen.ENRT_OVRID_RSN_CD,
pen.ENRT_OVRID_THRU_DT,
pen.ERLST_DEENRT_DT,
pen.LAST_UPDATED_BY,
pen.LAST_UPDATE_DATE,
pen.LAST_UPDATE_LOGIN,
pen.LER_ID,
pen.NO_LNGR_ELIG_FLAG,
pen.OBJECT_VERSION_NUMBER,
pen.OIPL_ID,
pen.OIPL_ORDR_NUM,
pen.ORGNL_ENRT_DT,
pen.LCR_ATTRIBUTE1,
pen.LCR_ATTRIBUTE10,
pen.LCR_ATTRIBUTE11,
pen.LCR_ATTRIBUTE12,
pen.LCR_ATTRIBUTE13,
pen.LCR_ATTRIBUTE14,
pen.LCR_ATTRIBUTE15,
pen.LCR_ATTRIBUTE16,
pen.LCR_ATTRIBUTE17,
pen.LCR_ATTRIBUTE18,
pen.LCR_ATTRIBUTE19,
pen.LCR_ATTRIBUTE2,
pen.LCR_ATTRIBUTE20,
pen.LCR_ATTRIBUTE21,
pen.LCR_ATTRIBUTE22,
pen.LCR_ATTRIBUTE23,
pen.LCR_ATTRIBUTE24,
pen.LCR_ATTRIBUTE25,
pen.LCR_ATTRIBUTE26,
pen.LCR_ATTRIBUTE27,
pen.LCR_ATTRIBUTE28,
pen.LCR_ATTRIBUTE29,
pen.LCR_ATTRIBUTE3,
pen.LCR_ATTRIBUTE30,
pen.LCR_ATTRIBUTE4,
pen.LCR_ATTRIBUTE5,
pen.LCR_ATTRIBUTE6,
pen.LCR_ATTRIBUTE7,
pen.LCR_ATTRIBUTE8,
pen.LCR_ATTRIBUTE9,
pen.LCR_ATTRIBUTE_CATEGORY,
pen.PERSON_ID,
pen.PER_IN_LER_ID,
pen.PGM_ID,
pen.PLIP_ORDR_NUM,
pen.PL_ID,
pen.PL_ORDR_NUM,
pen.PL_TYP_ID,
pen.PROGRAM_APPLICATION_ID,
pen.PROGRAM_ID,
pen.PROGRAM_UPDATE_DATE,
pen.bkup_tbl_id, -- Mapped to PRTT_ENRT_RSLT_ID,
pen.PRTT_ENRT_RSLT_STAT_CD,
pen.PRTT_IS_CVRD_FLAG,
pen.PTIP_ID,
pen.PTIP_ORDR_NUM,
pen.REQUEST_ID,
pen.RPLCS_SSPNDD_RSLT_ID,
pen.SSPNDD_FLAG,
pen.UOM
from ben_le_clsn_n_rstr pen,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
where pil.per_in_ler_id = c_bckdt_per_in_ler_id
and pil.person_id = c_person_id
and pil.per_in_ler_id = pel.per_in_ler_id
and ((pel.pgm_id is null and c_pgm_id is null) or
pel.pgm_id = c_pgm_id )
and ( c_pl_id is null or
pel.pl_id = c_pl_id )
and (pel.dflt_asnd_dt is not null or
pel.elcns_made_dt is not null or
pel.auto_asnd_dt is not null) -- Bug 8305552, Check for Automatic Enrolled Plans
AND pil.per_in_ler_id = pen.per_in_ler_id
and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
--To Handle Programs and Plans not in Program
and ((pen.pgm_id is null and c_pgm_id is null) or
pen.pgm_id = c_pgm_id )
and ( c_pl_id is null or
pen.pl_id = c_pl_id )
and ((pen.enrt_cvg_thru_dt is null or
pen.enrt_cvg_thru_dt = hr_api.g_eot) and
pen.effective_end_date = hr_api.g_eot
)
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and pen.bkup_tbl_id not in (
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_prtt_enrt_rslt_f pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
and pil_inner.person_id = c_person_id
and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
union
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_le_clsn_n_rstr pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
and pil_inner.person_id = c_person_id
AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
)
order by 1;
select
pen.EFFECTIVE_END_DATE,
pen.BUSINESS_GROUP_ID,
pen.EFFECTIVE_START_DATE,
pen.ENRT_CVG_STRT_DT,
pen.ENRT_CVG_THRU_DT,
pen.ENRT_MTHD_CD,
pen.OBJECT_VERSION_NUMBER,
pen.OIPL_ID,
pen.PERSON_ID,
pen.PER_IN_LER_ID,
pen.PGM_ID,
pen.PL_ID,
pen.PL_TYP_ID,
pen.PRTT_ENRT_RSLT_ID,
pen.PRTT_ENRT_RSLT_STAT_CD,
pen.PTIP_ID,
pen.RPLCS_SSPNDD_RSLT_ID,
pen.SSPNDD_FLAG
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
where pil.per_in_ler_id = c_bckdt_per_in_ler_id
and pil.person_id = c_person_id
and pil.per_in_ler_id = pen.per_in_ler_id
and pil.per_in_ler_id = pel.per_in_ler_id
and (pel.dflt_asnd_dt is null and
pel.elcns_made_dt is null) -- Bug 8305552, Check for Automatic Enrolled Plans
and exists
(select '1' from ben_prtt_enrt_rslt_f epen where
epen.per_in_ler_id = c_bckdt_per_in_ler_id
and epen.person_id = c_person_id
and (epen.enrt_cvg_thru_dt is null or
epen.enrt_cvg_thru_dt = hr_api.g_eot)
and epen.effective_end_date = hr_api.g_eot)
--To Handle Programs and Plans not in Program
and (pen.effective_end_date = hr_api.g_eot or
pen.effective_end_date = (select max(effective_end_date)
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id =
pen.prtt_enrt_rslt_id))
and (pen.enrt_cvg_thru_dt is null or
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and pen.prtt_enrt_rslt_id not in (
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_prtt_enrt_rslt_f pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
and pil_inner.person_id = c_person_id
and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
union
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_le_clsn_n_rstr pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
and pil_inner.person_id = c_person_id
AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
)
union
select
pen.EFFECTIVE_END_DATE,
pen.BUSINESS_GROUP_ID,
pen.EFFECTIVE_START_DATE,
pen.ENRT_CVG_STRT_DT,
pen.ENRT_CVG_THRU_DT,
pen.ENRT_MTHD_CD,
pen.OBJECT_VERSION_NUMBER,
pen.OIPL_ID,
pen.PERSON_ID,
pen.PER_IN_LER_ID,
pen.PGM_ID,
pen.PL_ID,
pen.PL_TYP_ID,
pen.bkup_tbl_id, -- Mapped to PRTT_ENRT_RSLT_ID,
pen.PRTT_ENRT_RSLT_STAT_CD,
pen.PTIP_ID,
pen.RPLCS_SSPNDD_RSLT_ID,
pen.SSPNDD_FLAG
from ben_le_clsn_n_rstr pen,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
where pil.per_in_ler_id = c_bckdt_per_in_ler_id
and pil.person_id = c_person_id
and pil.per_in_ler_id = pel.per_in_ler_id
and (pel.dflt_asnd_dt is null and
pel.elcns_made_dt is null) -- Bug 8305552, Check for Automatic Enrolled Plans
and exists
(select '1' from ben_prtt_enrt_rslt_f epen where
epen.per_in_ler_id = c_bckdt_per_in_ler_id
and epen.person_id = c_person_id
and (epen.enrt_cvg_thru_dt is null or
epen.enrt_cvg_thru_dt = hr_api.g_eot)
and epen.effective_end_date = hr_api.g_eot)
AND pil.per_in_ler_id = pen.per_in_ler_id
and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and ((pen.enrt_cvg_thru_dt is null or
pen.enrt_cvg_thru_dt = hr_api.g_eot) and
pen.effective_end_date = hr_api.g_eot
)
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and pen.bkup_tbl_id not in (
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_prtt_enrt_rslt_f pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
and pil_inner.person_id = c_person_id
and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
union
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_le_clsn_n_rstr pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
and pil_inner.person_id = c_person_id
AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
)
order by 1;
SELECT epe.elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc epe
WHERE epe.oipl_id = c_oipl_id
AND epe.pgm_id = c_pgm_id
AND epe.per_in_ler_id = c_per_in_ler_id;
SELECT epe.elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc epe
WHERE epe.oipl_id = c_oipl_id
AND epe.pgm_id IS NULL
AND epe.per_in_ler_id = c_per_in_ler_id;
SELECT epe.elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc epe
WHERE epe.pl_id = c_pl_id
AND epe.oipl_id IS NULL
AND epe.pgm_id = c_pgm_id
AND epe.per_in_ler_id = c_per_in_ler_id;
SELECT epe.elig_per_elctbl_chc_id
FROM ben_elig_per_elctbl_chc epe
WHERE epe.pl_id = c_pl_id
AND epe.oipl_id IS NULL
AND epe.pgm_id IS NULL
AND epe.per_in_ler_id = c_per_in_ler_id;
select pil.per_in_ler_id,
ler.name,
pil.bckt_per_in_ler_id,
ler_cs_bckdt.name ler_cs_bckdt_name
from ben_per_in_ler pil
,ben_ler_f ler
,ben_per_in_ler pil_cs_bckdt
,ben_ler_f ler_cs_bckdt
,ben_ptnl_ler_for_per plr
where pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.ler_id = p_ler_id
and pil.ler_id = ler.ler_id
and ler.business_group_id = pil.business_group_id
and pil.per_in_ler_stat_cd = 'BCKDT'
and pil.bckt_per_in_ler_id = pil_cs_bckdt.per_in_ler_id(+)
and pil_cs_bckdt.ler_id = ler_cs_bckdt.ler_id(+)
and pil.ptnl_ler_for_per_id = plr.ptnl_ler_for_per_id
and plr.ptnl_ler_for_per_stat_cd <> 'VOIDD'
and nvl(pil_cs_bckdt.business_group_id, p_business_group_id) = p_business_group_id
and nvl(ler_cs_bckdt.business_group_id, p_business_group_id) = p_business_group_id
and pil.lf_evt_ocrd_dt = p_effective_date
and pil.ler_id <> ben_manage_life_events.g_ler_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date
and p_effective_date between nvl(ler_cs_bckdt.effective_start_date, p_effective_date)
and nvl(ler_cs_bckdt.effective_end_date, p_effective_date)
order by pil.per_in_ler_id desc
;
select 'Y'
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.business_group_id = p_business_group_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pen.business_group_id = pil.business_group_id
and pen.prtt_enrt_rslt_stat_cd = 'BCKDT'
and nvl(pen.prtt_enrt_rslt_stat_cd, 'XXXX') = 'BCKDT'
and p_effective_date between pen.effective_start_date
and pen.effective_end_date
/* 9999 complete this part from the backup table */
union
select 'Y'
from ben_le_clsn_n_rstr lcr
where lcr.per_in_ler_id = p_bckdt_per_in_ler_id
and lcr.business_group_id = p_business_group_id
and p_effective_date between lcr.effective_start_date
and lcr.effective_end_date;
select
pen.OIPL_ID,
pen.PGM_ID,
pen.PL_ID,
pen.SSPNDD_FLAG
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_pil_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = pen.per_in_ler_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP') ;
select csd_pil.lf_evt_ocrd_dt,
csd_pil.per_in_ler_id
from ben_per_in_ler csd_pil,
ben_per_in_ler bckt_pil
where bckt_pil.per_in_ler_id = p_bckdt_per_in_ler_id
and bckt_pil.BCKT_PER_IN_LER_ID = csd_pil.per_in_ler_id
and bckt_pil.business_group_id = p_business_group_id
and csd_pil.business_group_id = p_business_group_id;
select pil.per_in_ler_id, pil.object_version_number
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id <> p_per_in_ler_id
and pil.per_in_ler_id <> p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.business_group_id = p_business_group_id
and nvl(pil.per_in_ler_stat_cd, 'XXXX') not in('BCKDT', 'VOIDD')
and pil.lf_evt_ocrd_dt > cv_bckt_csd_lf_evt_ocrd_dt
and pil.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt
from ben_per_in_ler
where per_in_ler_id = p_bckdt_per_in_ler_id
and business_group_id = p_business_group_id
)
order by pil.lf_evt_ocrd_dt asc;
select 'Y'
from ben_prtt_enrt_rslt_f pen,
ben_ler_f ler
where pen.per_in_ler_id not in (p_per_in_ler_id,p_bckdt_per_in_ler_id)
/* Bug 8305552: Added 'not in (p_per_in_ler_id)'. Do not check elections for the current LifeEvent,because for Automatic enrollments
record will be present during validation of the cursor*/
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
-- and pen.enrt_cvg_thru_dt = hr_api.g_eot BUG 4642657
and pen.effective_end_date = hr_api.g_eot
and pen.ler_id = ler.ler_id
and p_effective_date between ler.effective_start_date and
ler.effective_end_date
and ler.typ_cd not in ('IREC','SCHEDDU', 'COMP', 'GSP', 'ABS')
/*Commented the below condition and added a new condition for Bug :8716679 */
/* Bug 12871602: Changed the below condition. If p_reinstate_cd = 'VALIDATE_EXPLICIT_ENRT' check elections
are made for the intervening LE based on per_in_ler_id of intervening LE*/
and ( ((p_reinstate_cd is NULL or p_reinstate_cd <> 'VALIDATE_EXPLICIT_ENRT' )
and ( pen.effective_start_date >= (select decode(PRVS_STAT_CD, 'STRTD',STRTD_DT,
'PROCD',PROCD_DT,LF_EVT_OCRD_DT)
from ben_per_in_ler
where per_in_ler_id = p_bckdt_per_in_ler_id
)
)) or (p_reinstate_cd = 'VALIDATE_EXPLICIT_ENRT' and pen.per_in_ler_id= l_prev_per_in_ler_id1 )
)
and rownum = 1 ;
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id not in (p_per_in_ler_id,p_bckt_csd_per_in_ler_id)
and pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
and pil.lf_evt_ocrd_dt in (select max(lf_evt_ocrd_dt)
from ben_per_in_ler pil2
where pil2.ler_id = pil.ler_id
and pil2.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pil2.person_id = p_person_id
and pil2.lf_evt_ocrd_dt < p_bckt_csd_lf_evt_ocrd_dt
);
select bckt_per_in_ler_id
from ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id;
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id not in (p_per_in_ler_id,p_bckdt_per_in_ler_id)
and pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
and pil.lf_evt_ocrd_dt in (select lf_evt_ocrd_dt
from ben_per_in_ler pil2,
ben_ler_f ler1
where pil2.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pil2.person_id = p_person_id
and pil2.ler_id = ler1.ler_id
and p_effective_date between
ler1.effective_start_date and ler1.effective_end_date
and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil2.lf_evt_ocrd_dt > (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
per_in_ler_id = l_int_pil_id)
and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
per_in_ler_id = p_bckdt_per_in_ler_id)
)
order by pil.lf_evt_ocrd_dt desc;
select epe_egd.*
from ben_elig_dpnt epe_egd
where epe_egd.elig_per_elctbl_chc_id = p_bckdt_epe_id
and epe_egd.business_group_id = p_business_group_id
and epe_egd.per_in_ler_id = p_bckdt_per_in_ler_id;
select epe_egd.*
from ben_elig_dpnt epe_egd
where epe_egd.elig_per_elctbl_chc_id = p_curr_epe_id
and epe_egd.business_group_id = p_business_group_id
and epe_egd.per_in_ler_id = p_per_in_ler_id;
select count(*) into l_bckdt_epe_egd_count
from ben_elig_dpnt epe_egd
where epe_egd.elig_per_elctbl_chc_id = p_bckdt_epe_id
and epe_egd.business_group_id = p_business_group_id
and epe_egd.per_in_ler_id = p_bckdt_per_in_ler_id;
l_curr_epe_egd_table.delete;
select enb_ecr.*
from ben_enrt_rt enb_ecr
where enb_ecr.enrt_bnft_id = p_bckdt_enb_id
and enb_ecr.business_group_id = p_business_group_id
and enb_ecr.elig_per_elctbl_chc_id is null;
select enb_ecr.*
from ben_enrt_rt enb_ecr
where enb_ecr.enrt_bnft_id = p_curr_enb_id
and enb_ecr.business_group_id = p_business_group_id
and enb_ecr.elig_per_elctbl_chc_id is null;
select count(*) into l_bckdt_enb_ecr_count
from ben_enrt_rt enb_ecr
where enb_ecr.enrt_bnft_id = p_bckdt_enb_id
and enb_ecr.business_group_id = p_business_group_id
and enb_ecr.elig_per_elctbl_chc_id is null;
l_curr_enb_ecr_table.delete;
select epe_ecr.*
from ben_enrt_rt epe_ecr
where epe_ecr.elig_per_elctbl_chc_id = p_bckdt_epe_id
and epe_ecr.business_group_id = p_business_group_id
and epe_ecr.enrt_bnft_id is null;
select epe_ecr.*
from ben_enrt_rt epe_ecr
where epe_ecr.elig_per_elctbl_chc_id = p_curr_epe_id
and epe_ecr.business_group_id = p_business_group_id
and epe_ecr.enrt_bnft_id is null;
select count(*) into l_bckdt_epe_ecr_count
from ben_enrt_rt epe_ecr
where epe_ecr.elig_per_elctbl_chc_id = p_bckdt_epe_id
and epe_ecr.business_group_id = p_business_group_id
and epe_ecr.enrt_bnft_id is null;
/* l_bckdt_epe_ecr_table.delete;
l_curr_epe_ecr_table.delete;
select enb.dflt_flag
,enb.bndry_perd_cd
,enb.val
,enb.bnft_typ_cd
,enb.mn_val
,enb.mx_val
,enb.incrmt_val
,enb.rt_typ_cd
,enb.cvg_mlt_cd
,enb.ctfn_rqd_flag
,enb.ordr_num
,enb.dflt_val
,enb.comp_lvl_fctr_id
,enb.enrt_bnft_id
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = p_bckdt_epe_id ;
select enb.dflt_flag
,enb.bndry_perd_cd
,enb.val
,enb.bnft_typ_cd
,enb.mn_val
,enb.mx_val
,enb.incrmt_val
,enb.rt_typ_cd
,enb.cvg_mlt_cd
,enb.ctfn_rqd_flag
,enb.ordr_num
,enb.dflt_val
,enb.comp_lvl_fctr_id
,enb.enrt_bnft_id
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = p_curr_epe_id ;
select count(*) into l_bckdt_enb_count
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = p_bckdt_epe_id
and enb.business_group_id = p_business_group_id;
l_bckdt_enb_table.delete;
l_curr_enb_table.delete;
select ecd.effective_start_date
,ecd.effective_end_date
,ecd.cvg_strt_dt
,ecd.cvg_thru_dt
,ecd.cvg_pndg_flag
,ecd.ovrdn_flag
,ecd.ovrdn_thru_dt
,ecd.dpnt_person_id
from ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil
where ecd.business_group_id=p_business_group_id
and p_effective_date between
ecd.effective_start_date and ecd.effective_end_date
and pil.per_in_ler_id=ecd.per_in_ler_id
and ecd.elig_per_elctbl_chc_id = p_bckdt_epe_id
and pil.per_in_ler_id=p_bckdt_per_in_ler_id
and pil.business_group_id=ecd.business_group_id
and pil.per_in_ler_stat_cd = 'BCKDT';
select ecd.effective_start_date
,ecd.effective_end_date
,ecd.cvg_strt_dt
,ecd.cvg_thru_dt
,ecd.cvg_pndg_flag
,ecd.ovrdn_flag
,ecd.ovrdn_thru_dt
,ecd.dpnt_person_id
from ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil
where p_effective_date between
ecd.effective_start_date and ecd.effective_end_date
and pil.per_in_ler_id=ecd.per_in_ler_id
and ecd.elig_per_elctbl_chc_id = p_curr_epe_id
and pil.per_in_ler_id=p_per_in_ler_id
and pil.business_group_id=ecd.business_group_id;
select count(*) into l_bckdt_ecd_count
from ben_elig_cvrd_dpnt_f ecd,
ben_per_in_ler pil
where ecd.business_group_id=p_business_group_id
and p_effective_date between
ecd.effective_start_date and ecd.effective_end_date
and pil.per_in_ler_id=ecd.per_in_ler_id
and ecd.elig_per_elctbl_chc_id = p_bckdt_epe_id
and pil.per_in_ler_id=p_bckdt_per_in_ler_id
and pil.business_group_id=ecd.business_group_id
and pil.per_in_ler_stat_cd = 'BCKDT';
l_curr_ecd_table.delete;
select count(*)
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = epe.per_in_ler_id;
select count(*)
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = epe.per_in_ler_id;
select epe.*
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = epe.per_in_ler_id;
select epe.*
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = epe.per_in_ler_id;
l_bckdt_epe_table.delete;
l_curr_epe_table.delete;
select count(*)
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.per_in_ler_id = pel.per_in_ler_id
and (pel.pgm_id = p_pgm_id or
(p_pgm_id is null and pel.pgm_id is null))
and (pel.pl_id = p_pl_id or
(pel.pl_id is null and p_pl_id is null))
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id;
select count(*)
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.person_id = p_person_id
and pil.per_in_ler_id = pel.per_in_ler_id
and (pel.pgm_id = p_pgm_id or
(p_pgm_id is null and pel.pgm_id is null))
and (pel.pl_id = p_pl_id or
(pel.pl_id is null and p_pl_id is null))
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id;
select epe.*
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.per_in_ler_id = pel.per_in_ler_id
and (pel.pgm_id = p_pgm_id or
(p_pgm_id is null and pel.pgm_id is null))
and (pel.pl_id = p_pl_id or
(pel.pl_id is null and p_pl_id is null))
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id ;
select epe.*
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.person_id = p_person_id
and pil.per_in_ler_id = pel.per_in_ler_id
and (pel.pgm_id = p_pgm_id or
(p_pgm_id is null and pel.pgm_id is null))
and (pel.pl_id = p_pl_id or
(pel.pl_id is null and p_pl_id is null))
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id ;
l_bckdt_epe_table.delete;
l_curr_epe_table.delete;
select pcd.*
from ben_per_cm_f pcm,
ben_per_cm_prvdd_f pcd
where pcm.person_id = p_person_id
and pcm.ler_id = p_ler_id
and pcm.business_group_id = p_business_group_id
and p_effective_date
between pcm.effective_start_date
and pcm.effective_end_date
and pcd.per_cm_id = pcm.per_cm_id
and pcd.sent_dt is null
and pcd.business_group_id = p_business_group_id
and p_effective_date
between pcd.effective_start_date
and pcd.effective_end_date;
ben_per_cm_prvdd_api.update_per_cm_prvdd
(p_validate => false
,p_per_cm_prvdd_id => per_cm_rec.per_cm_prvdd_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_per_cm_prvdd_stat_cd => 'VOID'
,p_object_version_number => per_cm_rec.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_correction);
select pcd.*
from ben_per_cm_f pcm,
ben_per_cm_prvdd_f pcd
where pcm.person_id = p_person_id
and pcm.ler_id = p_ler_id
and pcm.business_group_id = p_business_group_id
and p_effective_date
between pcm.effective_start_date
and pcm.effective_end_date
and pcd.per_cm_id = pcm.per_cm_id
and pcd.sent_dt is null
and pcd.business_group_id = p_business_group_id
and p_effective_date
between pcd.effective_start_date
and pcd.effective_end_date;
ben_per_cm_prvdd_api.update_per_cm_prvdd
(p_validate => false
,p_per_cm_prvdd_id => per_cm_rec.per_cm_prvdd_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_sent_dt => null
,p_resnd_rsn_cd => 'RPE' -- event reprocessed
,p_resnd_cmnt_txt => l_resnd_cmnt_txt
,p_object_version_number => per_cm_rec.object_version_number
,p_effective_date => p_effective_date
,p_datetrack_mode => hr_api.g_correction);
select pel.*
from ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and pel.per_in_ler_id = pil.per_in_ler_id
and pel.business_group_id = pil.business_group_id;
ben_Pil_Elctbl_chc_Popl_api.update_Pil_Elctbl_chc_Popl (
p_validate => false
,p_pil_elctbl_chc_popl_id => pel_rec.pil_elctbl_chc_popl_id
,p_object_version_number => pel_rec.object_version_number
,p_effective_date => p_effective_date
,p_enrt_perd_strt_dt => pel_rec.enrt_perd_strt_dt+l_move_out_by
,p_enrt_perd_end_dt => pel_rec.enrt_perd_end_dt+l_move_out_by
,p_dflt_enrt_dt => pel_rec.dflt_enrt_dt+l_move_out_by
,p_procg_end_dt => pel_rec.procg_end_dt+l_move_out_by
);
select crp.*
from ben_cbr_per_in_ler crp
where crp.per_in_ler_id = p_bckdt_per_in_ler_id
and crp.init_evt_flag = 'N'
and crp.business_group_id = p_business_group_id;
select cqb.*
from ben_le_clsn_n_rstr cqb
where cqb.per_in_ler_id = p_bckdt_per_in_ler_id
and cqb.business_group_id = p_business_group_id
and cqb.bkup_tbl_typ_cd = 'BEN_CBR_QUALD_BNF';
select cqb.*
from ben_cbr_quald_bnf cqb
where cqb.cbr_quald_bnf_id = p_cbr_quald_bnf_id
and cqb.business_group_id = p_business_group_id;
ben_cbr_quald_bnf_api.update_cbr_quald_bnf
(p_validate => false
,p_cbr_quald_bnf_id => l_cqb_rec.cbr_quald_bnf_id
,p_cbr_elig_perd_end_dt => l_bckdt_cqb_rec.elig_thru_dt
,p_business_group_id => p_business_group_id
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
);
delete from ben_le_clsn_n_rstr cqb
where cqb.per_in_ler_id = p_bckdt_per_in_ler_id
and cqb.business_group_id = p_business_group_id
and cqb.bkup_tbl_typ_cd = 'BEN_CBR_QUALD_BNF';
select prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_bckdt_pen_id
and prv.business_group_id = p_business_group_id;
select prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_curr_pen_id
and prv.business_group_id = p_business_group_id;
select count(*) into l_bckdt_prv_count
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = p_bckdt_pen_id
and prv.business_group_id = p_business_group_id;
l_curr_prv_table.delete;
select pen.ASSIGNMENT_ID,
pen.BNFT_AMT,
pen.BNFT_NNMNTRY_UOM,
pen.BNFT_ORDR_NUM,
pen.BNFT_TYP_CD,
pen.BUSINESS_GROUP_ID,
pen.COMP_LVL_CD,
pen.CREATED_BY,
pen.CREATION_DATE,
pen.EFFECTIVE_END_DATE,
pen.EFFECTIVE_START_DATE,
pen.ENRT_CVG_STRT_DT,
pen.ENRT_CVG_THRU_DT,
pen.ENRT_MTHD_CD,
pen.ENRT_OVRIDN_FLAG,
pen.ENRT_OVRID_RSN_CD,
pen.ENRT_OVRID_THRU_DT,
pen.ERLST_DEENRT_DT,
pen.LAST_UPDATED_BY,
pen.LAST_UPDATE_DATE,
pen.LAST_UPDATE_LOGIN,
pen.LER_ID,
pen.NO_LNGR_ELIG_FLAG,
pen.OBJECT_VERSION_NUMBER,
pen.OIPL_ID,
pen.OIPL_ORDR_NUM,
pen.ORGNL_ENRT_DT,
pen.PEN_ATTRIBUTE1,
pen.PEN_ATTRIBUTE10,
pen.PEN_ATTRIBUTE11,
pen.PEN_ATTRIBUTE12,
pen.PEN_ATTRIBUTE13,
pen.PEN_ATTRIBUTE14,
pen.PEN_ATTRIBUTE15,
pen.PEN_ATTRIBUTE16,
pen.PEN_ATTRIBUTE17,
pen.PEN_ATTRIBUTE18,
pen.PEN_ATTRIBUTE19,
pen.PEN_ATTRIBUTE2,
pen.PEN_ATTRIBUTE20,
pen.PEN_ATTRIBUTE21,
pen.PEN_ATTRIBUTE22,
pen.PEN_ATTRIBUTE23,
pen.PEN_ATTRIBUTE24,
pen.PEN_ATTRIBUTE25,
pen.PEN_ATTRIBUTE26,
pen.PEN_ATTRIBUTE27,
pen.PEN_ATTRIBUTE28,
pen.PEN_ATTRIBUTE29,
pen.PEN_ATTRIBUTE3,
pen.PEN_ATTRIBUTE30,
pen.PEN_ATTRIBUTE4,
pen.PEN_ATTRIBUTE5,
pen.PEN_ATTRIBUTE6,
pen.PEN_ATTRIBUTE7,
pen.PEN_ATTRIBUTE8,
pen.PEN_ATTRIBUTE9,
pen.PEN_ATTRIBUTE_CATEGORY,
pen.PERSON_ID,
pen.PER_IN_LER_ID,
pen.PGM_ID,
pen.PLIP_ORDR_NUM,
pen.PL_ID,
pen.PL_ORDR_NUM,
pen.PL_TYP_ID,
pen.PROGRAM_APPLICATION_ID,
pen.PROGRAM_ID,
pen.PROGRAM_UPDATE_DATE,
pen.PRTT_ENRT_RSLT_ID,
pen.PRTT_ENRT_RSLT_STAT_CD,
pen.PRTT_IS_CVRD_FLAG,
pen.PTIP_ID,
pen.PTIP_ORDR_NUM,
pen.REQUEST_ID,
pen.RPLCS_SSPNDD_RSLT_ID,
pen.SSPNDD_FLAG,
pen.UOM
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = pen.per_in_ler_id
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
union
select
pen.ASSIGNMENT_ID,
pen.BNFT_AMT,
pen.BNFT_NNMNTRY_UOM,
pen.BNFT_ORDR_NUM,
pen.BNFT_TYP_CD,
pen.BUSINESS_GROUP_ID,
pen.COMP_LVL_CD,
pen.CREATED_BY,
pen.CREATION_DATE,
pen.EFFECTIVE_END_DATE,
pen.EFFECTIVE_START_DATE,
pen.ENRT_CVG_STRT_DT,
pen.ENRT_CVG_THRU_DT,
pen.ENRT_MTHD_CD,
pen.ENRT_OVRIDN_FLAG,
pen.ENRT_OVRID_RSN_CD,
pen.ENRT_OVRID_THRU_DT,
pen.ERLST_DEENRT_DT,
pen.LAST_UPDATED_BY,
pen.LAST_UPDATE_DATE,
pen.LAST_UPDATE_LOGIN,
pen.LER_ID,
pen.NO_LNGR_ELIG_FLAG,
pen.OBJECT_VERSION_NUMBER,
pen.OIPL_ID,
pen.OIPL_ORDR_NUM,
pen.ORGNL_ENRT_DT,
pen.LCR_ATTRIBUTE1,
pen.LCR_ATTRIBUTE10,
pen.LCR_ATTRIBUTE11,
pen.LCR_ATTRIBUTE12,
pen.LCR_ATTRIBUTE13,
pen.LCR_ATTRIBUTE14,
pen.LCR_ATTRIBUTE15,
pen.LCR_ATTRIBUTE16,
pen.LCR_ATTRIBUTE17,
pen.LCR_ATTRIBUTE18,
pen.LCR_ATTRIBUTE19,
pen.LCR_ATTRIBUTE2,
pen.LCR_ATTRIBUTE20,
pen.LCR_ATTRIBUTE21,
pen.LCR_ATTRIBUTE22,
pen.LCR_ATTRIBUTE23,
pen.LCR_ATTRIBUTE24,
pen.LCR_ATTRIBUTE25,
pen.LCR_ATTRIBUTE26,
pen.LCR_ATTRIBUTE27,
pen.LCR_ATTRIBUTE28,
pen.LCR_ATTRIBUTE29,
pen.LCR_ATTRIBUTE3,
pen.LCR_ATTRIBUTE30,
pen.LCR_ATTRIBUTE4,
pen.LCR_ATTRIBUTE5,
pen.LCR_ATTRIBUTE6,
pen.LCR_ATTRIBUTE7,
pen.LCR_ATTRIBUTE8,
pen.LCR_ATTRIBUTE9,
pen.LCR_ATTRIBUTE_CATEGORY,
pen.PERSON_ID,
pen.PER_IN_LER_ID,
pen.PGM_ID,
pen.PLIP_ORDR_NUM,
pen.PL_ID,
pen.PL_ORDR_NUM,
pen.PL_TYP_ID,
pen.PROGRAM_APPLICATION_ID,
pen.PROGRAM_ID,
pen.PROGRAM_UPDATE_DATE,
pen.bkup_tbl_id, -- Mapped to PRTT_ENRT_RSLT_ID,
pen.PRTT_ENRT_RSLT_STAT_CD,
pen.PRTT_IS_CVRD_FLAG,
pen.PTIP_ID,
pen.PTIP_ORDR_NUM,
pen.REQUEST_ID,
pen.RPLCS_SSPNDD_RSLT_ID,
pen.SSPNDD_FLAG,
pen.UOM
from ben_le_clsn_n_rstr pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = pen.per_in_ler_id
and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP');
select pen.*
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = pen.per_in_ler_id
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP');
select count(*) into l_bckdt_pen_cnt_temp
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
AND pil.per_in_ler_id = pen.per_in_ler_id;
select count(*) into l_bckdt_pen_cnt
from ben_le_clsn_n_rstr pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = pen.per_in_ler_id
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F';
l_curr_pen_table.delete;
select csd_pil.lf_evt_ocrd_dt
from ben_per_in_ler csd_pil,
ben_per_in_ler bckt_pil
where bckt_pil.per_in_ler_id = p_bckdt_per_in_ler_id
and bckt_pil.BCKT_PER_IN_LER_ID = csd_pil.per_in_ler_id
and bckt_pil.business_group_id = p_business_group_id
and csd_pil.business_group_id = p_business_group_id;
select pil.per_in_ler_id, pil.object_version_number
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id <> p_per_in_ler_id
and pil.per_in_ler_id <> p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.business_group_id = p_business_group_id
and nvl(pil.per_in_ler_stat_cd, 'XXXX') not in('BCKDT', 'VOIDD')
and pil.lf_evt_ocrd_dt > cv_bckt_csd_lf_evt_ocrd_dt
and pil.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt
from ben_per_in_ler
where per_in_ler_id = p_bckdt_per_in_ler_id
and business_group_id = p_business_group_id
);
select pen.EFFECTIVE_END_DATE,
pen.OIPL_ID,
pen.prtt_enrt_rslt_id,
pen.OBJECT_VERSION_NUMBER,
pen.PGM_ID,
pen.PL_ID,
pen.PL_TYP_ID,
pen.PTIP_ID
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_id = pen.per_in_ler_id
and pen.comp_lvl_cd = 'PLANFC'
union
select pen.EFFECTIVE_END_DATE,
pen.OIPL_ID,
pen.bkup_tbl_id, -- Mapped to PRTT_ENRT_RSLT_ID,
pen.OBJECT_VERSION_NUMBER,
pen.PGM_ID,
pen.PL_ID,
pen.PL_TYP_ID,
pen.PTIP_ID
from ben_le_clsn_n_rstr pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = pen.per_in_ler_id
and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and pen.comp_lvl_cd = 'PLANFC'
order by 1; -- pen.effective_end_date; -- Low to High
select pen.EFFECTIVE_END_DATE,
pen.OIPL_ID,
pen.prtt_enrt_rslt_id,
pen.OBJECT_VERSION_NUMBER,
pen.PGM_ID,
pen.PL_ID,
pen.PL_TYP_ID,
pen.PTIP_ID,
pen.effective_start_date
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_id = pen.per_in_ler_id
and nvl(pen.pl_id, -1) = nvl(cp_pl_id, -1)
and nvl(pen.oipl_id, -1) = nvl(cp_oipl_id, -1)
and nvl(pen.pgm_id, -1) = nvl(cp_pgm_id, -1)
and pen.comp_lvl_cd = 'PLANFC';
select
bckdt_bpl.EFFECTIVE_END_DATE
,bckdt_bpl.BNFT_PRVDD_LDGR_ID
,bckdt_bpl.EFFECTIVE_START_DATE
,bckdt_bpl.PRTT_RO_OF_UNUSD_AMT_FLAG
,bckdt_bpl.FRFTD_VAL
,bckdt_bpl.PRVDD_VAL
,bckdt_bpl.USED_VAL
,bckdt_bpl.CASH_RECD_VAL
,bckdt_bpl.BNFT_PRVDR_POOL_ID
,bckdt_bpl.ACTY_BASE_RT_ID
,bckdt_bpl.PRTT_ENRT_RSLT_ID
,bckdt_bpl.BUSINESS_GROUP_ID
,bckdt_bpl.BPL_ATTRIBUTE_CATEGORY
,bckdt_bpl.BPL_ATTRIBUTE1
,bckdt_bpl.BPL_ATTRIBUTE2
,bckdt_bpl.BPL_ATTRIBUTE3
,bckdt_bpl.BPL_ATTRIBUTE4
,bckdt_bpl.BPL_ATTRIBUTE5
,bckdt_bpl.BPL_ATTRIBUTE6
,bckdt_bpl.BPL_ATTRIBUTE7
,bckdt_bpl.BPL_ATTRIBUTE8
,bckdt_bpl.BPL_ATTRIBUTE9
,bckdt_bpl.BPL_ATTRIBUTE10
,bckdt_bpl.BPL_ATTRIBUTE11
,bckdt_bpl.BPL_ATTRIBUTE12
,bckdt_bpl.BPL_ATTRIBUTE13
,bckdt_bpl.BPL_ATTRIBUTE14
,bckdt_bpl.BPL_ATTRIBUTE15
,bckdt_bpl.BPL_ATTRIBUTE16
,bckdt_bpl.BPL_ATTRIBUTE17
,bckdt_bpl.BPL_ATTRIBUTE18
,bckdt_bpl.BPL_ATTRIBUTE19
,bckdt_bpl.BPL_ATTRIBUTE20
,bckdt_bpl.BPL_ATTRIBUTE21
,bckdt_bpl.BPL_ATTRIBUTE22
,bckdt_bpl.BPL_ATTRIBUTE23
,bckdt_bpl.BPL_ATTRIBUTE24
,bckdt_bpl.BPL_ATTRIBUTE25
,bckdt_bpl.BPL_ATTRIBUTE26
,bckdt_bpl.BPL_ATTRIBUTE27
,bckdt_bpl.BPL_ATTRIBUTE28
,bckdt_bpl.BPL_ATTRIBUTE29
,bckdt_bpl.BPL_ATTRIBUTE30
,bckdt_bpl.LAST_UPDATE_DATE
,bckdt_bpl.LAST_UPDATED_BY
,bckdt_bpl.LAST_UPDATE_LOGIN
,bckdt_bpl.CREATED_BY
,bckdt_bpl.CREATION_DATE
,bckdt_bpl.OBJECT_VERSION_NUMBER
,bckdt_bpl.RLD_UP_VAL
,bckdt_bpl.PER_IN_LER_ID
from ben_bnft_prvdd_ldgr_f bckdt_bpl
where bckdt_bpl.per_in_ler_id = p_bckdt_per_in_ler_id
and bckdt_bpl.business_group_id = p_business_group_id
and bckdt_bpl.prtt_enrt_rslt_id = cp_bckdt_prtt_enrt_rslt_id
union
select
bckdt_bpl.EFFECTIVE_END_DATE
,bckdt_bpl.BKUP_TBL_ID
,bckdt_bpl.EFFECTIVE_START_DATE
,bckdt_bpl.NO_LNGR_ELIG_FLAG -- Used for PRTT_RO_OF_UNUSD_AMT_FLAG
,bckdt_bpl.AMT_DSGD_VAL -- Used for FRFTD_VAL
,bckdt_bpl.ANN_RT_VAL -- Used for PRVDD_VAL
,bckdt_bpl.CMCD_RT_VAL -- Used for USED_VAL
,bckdt_bpl.RT_VAL -- Used for CASH_RECD_VAL
,bckdt_bpl.COMP_LVL_FCTR_ID -- Used as BNFT_PRVDR_POOL_ID
,bckdt_bpl.ACTY_BASE_RT_ID
,bckdt_bpl.PRTT_ENRT_RSLT_ID
,bckdt_bpl.BUSINESS_GROUP_ID
,bckdt_bpl.LCR_ATTRIBUTE_CATEGORY
,bckdt_bpl.LCR_ATTRIBUTE1
,bckdt_bpl.LCR_ATTRIBUTE2
,bckdt_bpl.LCR_ATTRIBUTE3
,bckdt_bpl.LCR_ATTRIBUTE4
,bckdt_bpl.LCR_ATTRIBUTE5
,bckdt_bpl.LCR_ATTRIBUTE6
,bckdt_bpl.LCR_ATTRIBUTE7
,bckdt_bpl.LCR_ATTRIBUTE8
,bckdt_bpl.LCR_ATTRIBUTE9
,bckdt_bpl.LCR_ATTRIBUTE10
,bckdt_bpl.LCR_ATTRIBUTE11
,bckdt_bpl.LCR_ATTRIBUTE12
,bckdt_bpl.LCR_ATTRIBUTE13
,bckdt_bpl.LCR_ATTRIBUTE14
,bckdt_bpl.LCR_ATTRIBUTE15
,bckdt_bpl.LCR_ATTRIBUTE1
,bckdt_bpl.LCR_ATTRIBUTE17
,bckdt_bpl.LCR_ATTRIBUTE18
,bckdt_bpl.LCR_ATTRIBUTE19
,bckdt_bpl.LCR_ATTRIBUTE20
,bckdt_bpl.LCR_ATTRIBUTE21
,bckdt_bpl.LCR_ATTRIBUTE22
,bckdt_bpl.LCR_ATTRIBUTE23
,bckdt_bpl.LCR_ATTRIBUTE24
,bckdt_bpl.LCR_ATTRIBUTE25
,bckdt_bpl.LCR_ATTRIBUTE26
,bckdt_bpl.LCR_ATTRIBUTE27
,bckdt_bpl.LCR_ATTRIBUTE28
,bckdt_bpl.LCR_ATTRIBUTE29
,bckdt_bpl.LCR_ATTRIBUTE30
,bckdt_bpl.LAST_UPDATE_DATE
,bckdt_bpl.LAST_UPDATED_BY
,bckdt_bpl.LAST_UPDATE_LOGIN
,bckdt_bpl.CREATED_BY
,bckdt_bpl.CREATION_DATE
,bckdt_bpl.OBJECT_VERSION_NUMBER
,bckdt_bpl.VAL -- Used for RLD_UP_VAL
,bckdt_bpl.PER_IN_LER_ID
from ben_le_clsn_n_rstr bckdt_bpl
where bckdt_bpl.per_in_ler_id = p_bckdt_per_in_ler_id
and bckdt_bpl.business_group_id = p_business_group_id
and bckdt_bpl.prtt_enrt_rslt_id = cp_bckdt_prtt_enrt_rslt_id
and bckdt_bpl.BKUP_TBL_TYP_CD = 'BEN_BNFT_PRVDD_LDGR_F'
order by 1;
select bpl.*
from ben_bnft_prvdd_ldgr_f bpl
where bpl.per_in_ler_id = p_per_in_ler_id
and bpl.prtt_enrt_rslt_id = cp_prtt_enrt_rslt_id
--
and cp_effective_date between bpl.effective_start_date
and bpl.effective_end_date
and bpl.business_group_id = p_business_group_id
and bpl.acty_base_rt_id = cp_acty_base_rt_id
and nvl(bpl.bnft_prvdr_pool_id, -1) = nvl(cp_bnft_prvdr_pool_id, -1);
l_datetrack_mode := hr_api.g_update;
ben_Benefit_Prvdd_Ledger_api.update_Benefit_Prvdd_Ledger (
p_bnft_prvdd_ldgr_id => l_curr_bpl_rec.bnft_prvdd_ldgr_id
,p_effective_start_date => l_bpl_effective_start_date
,p_effective_end_date => l_bpl_effective_end_date
,p_object_version_number => l_curr_bpl_rec.object_version_number
,p_prtt_ro_of_unusd_amt_flag => l_curr_bpl_rec.prtt_ro_of_unusd_amt_flag
,p_frftd_val => l_curr_bpl_rec.frftd_val
,p_prvdd_val => l_curr_bpl_rec.prvdd_val
,p_used_val => l_curr_bpl_rec.used_val
,p_bnft_prvdr_pool_id => l_curr_bpl_rec.bnft_prvdr_pool_id
,p_acty_base_rt_id => l_curr_bpl_rec.acty_base_rt_id
,p_per_in_ler_id => p_per_in_ler_id
,p_prtt_enrt_rslt_id => l_curr_bpl_rec.prtt_enrt_rslt_id
,p_business_group_id => p_business_group_id
,p_cash_recd_val => l_curr_bpl_rec.cash_recd_val
,p_effective_date => l_curr_pen_rec.effective_start_date
,p_datetrack_mode => l_datetrack_mode
,p_process_enrt_flag => 'Y'
,p_from_reinstate_enrt_flag => 'Y'
);
select
ppr.EFFECTIVE_END_DATE
,ppr.PRMRY_CARE_PRVDR_ID
,ppr.EFFECTIVE_START_DATE
,ppr.PRMRY_CARE_PRVDR_TYP_CD
,ppr.NAME
,ppr.EXT_IDENT
,ppr.PRTT_ENRT_RSLT_ID
,ppr.ELIG_CVRD_DPNT_ID
,ppr.BUSINESS_GROUP_ID
,ppr.PPR_ATTRIBUTE_CATEGORY
,ppr.PPR_ATTRIBUTE1
,ppr.PPR_ATTRIBUTE2
,ppr.PPR_ATTRIBUTE3
,ppr.PPR_ATTRIBUTE4
,ppr.PPR_ATTRIBUTE5
,ppr.PPR_ATTRIBUTE6
,ppr.PPR_ATTRIBUTE7
,ppr.PPR_ATTRIBUTE8
,ppr.PPR_ATTRIBUTE9
,ppr.PPR_ATTRIBUTE10
,ppr.PPR_ATTRIBUTE11
,ppr.PPR_ATTRIBUTE12
,ppr.PPR_ATTRIBUTE13
,ppr.PPR_ATTRIBUTE14
,ppr.PPR_ATTRIBUTE15
,ppr.PPR_ATTRIBUTE16
,ppr.PPR_ATTRIBUTE17
,ppr.PPR_ATTRIBUTE18
,ppr.PPR_ATTRIBUTE19
,ppr.PPR_ATTRIBUTE20
,ppr.PPR_ATTRIBUTE21
,ppr.PPR_ATTRIBUTE22
,ppr.PPR_ATTRIBUTE23
,ppr.PPR_ATTRIBUTE24
,ppr.PPR_ATTRIBUTE25
,ppr.PPR_ATTRIBUTE26
,ppr.PPR_ATTRIBUTE27
,ppr.PPR_ATTRIBUTE28
,ppr.PPR_ATTRIBUTE29
,ppr.PPR_ATTRIBUTE30
,ppr.LAST_UPDATE_DATE
,ppr.LAST_UPDATED_BY
,ppr.LAST_UPDATE_LOGIN
,ppr.CREATED_BY
,ppr.CREATION_DATE
,ppr.REQUEST_ID
,ppr.PROGRAM_APPLICATION_ID
,ppr.PROGRAM_ID
,ppr.PROGRAM_UPDATE_DATE
,ppr.OBJECT_VERSION_NUMBER
from ben_prmry_care_prvdr_f ppr
where ppr.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id -- Bug 3709516
and ppr.business_group_id = p_business_group_id
and p_effective_date between ppr.effective_start_date and ppr.effective_end_date; --Added p_effective_date condition for Bug 7497016
select
ppr.EFFECTIVE_END_DATE
,ppr.PRMRY_CARE_PRVDR_ID
,ppr.EFFECTIVE_START_DATE
,ppr.PRMRY_CARE_PRVDR_TYP_CD
,ppr.NAME
,ppr.EXT_IDENT
,ppr.PRTT_ENRT_RSLT_ID
,ppr.ELIG_CVRD_DPNT_ID
,ppr.BUSINESS_GROUP_ID
,ppr.PPR_ATTRIBUTE_CATEGORY
,ppr.PPR_ATTRIBUTE1
,ppr.PPR_ATTRIBUTE2
,ppr.PPR_ATTRIBUTE3
,ppr.PPR_ATTRIBUTE4
,ppr.PPR_ATTRIBUTE5
,ppr.PPR_ATTRIBUTE6
,ppr.PPR_ATTRIBUTE7
,ppr.PPR_ATTRIBUTE8
,ppr.PPR_ATTRIBUTE9
,ppr.PPR_ATTRIBUTE10
,ppr.PPR_ATTRIBUTE11
,ppr.PPR_ATTRIBUTE12
,ppr.PPR_ATTRIBUTE13
,ppr.PPR_ATTRIBUTE14
,ppr.PPR_ATTRIBUTE15
,ppr.PPR_ATTRIBUTE16
,ppr.PPR_ATTRIBUTE17
,ppr.PPR_ATTRIBUTE18
,ppr.PPR_ATTRIBUTE19
,ppr.PPR_ATTRIBUTE20
,ppr.PPR_ATTRIBUTE21
,ppr.PPR_ATTRIBUTE22
,ppr.PPR_ATTRIBUTE23
,ppr.PPR_ATTRIBUTE24
,ppr.PPR_ATTRIBUTE25
,ppr.PPR_ATTRIBUTE26
,ppr.PPR_ATTRIBUTE27
,ppr.PPR_ATTRIBUTE28
,ppr.PPR_ATTRIBUTE29
,ppr.PPR_ATTRIBUTE30
,ppr.LAST_UPDATE_DATE
,ppr.LAST_UPDATED_BY
,ppr.LAST_UPDATE_LOGIN
,ppr.CREATED_BY
,ppr.CREATION_DATE
,ppr.REQUEST_ID
,ppr.PROGRAM_APPLICATION_ID
,ppr.PROGRAM_ID
,ppr.PROGRAM_UPDATE_DATE
,ppr.OBJECT_VERSION_NUMBER
from ben_prmry_care_prvdr_f ppr
where ppr.elig_cvrd_dpnt_id = p_bckdt_elig_cvrd_dpnt_id -- Bug 3709516
and ppr.business_group_id = p_business_group_id
and p_effective_date between ppr.effective_start_date and ppr.effective_end_date; -- Bug 11667555
SELECT *
FROM ben_prmry_care_prvdr_f ppr
WHERE ppr.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
AND ppr.business_group_id = p_business_group_id
AND ppr.prmry_care_prvdr_id = c_prmry_care_prvdr_id
AND NVL (ppr.elig_cvrd_dpnt_id, -1) = NVL (p_elig_cvrd_dpnt_id, -1)
and p_effective_date between ppr.effective_start_date and ppr.effective_end_date; --Added p_effective_date condition for Bug 7497016
p_program_update_date => l_old_ppr_rec.program_update_date,
p_object_version_number => l_ppr_object_version_number,
p_effective_date => p_effective_date -- Bug : 5124 As per Jeana, data
-- should be reinstated with system date
-- rather than p_effective_date
);
l_datetrack_mode := hr_api.g_update;
ben_prmry_care_prvdr_api.update_prmry_care_prvdr (p_validate => FALSE,
p_prmry_care_prvdr_id => l_old_ppr_rec.prmry_care_prvdr_id,
p_effective_start_date => l_ppr_effective_start_date,
p_effective_end_date => l_ppr_effective_end_date,
p_name => l_old_ppr_rec.NAME,
p_ext_ident => l_old_ppr_rec.ext_ident,
p_prmry_care_prvdr_typ_cd => l_old_ppr_rec.prmry_care_prvdr_typ_cd,
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
p_business_group_id => p_business_group_id,
p_ppr_attribute_category => l_old_ppr_rec.ppr_attribute_category,
p_ppr_attribute1 => l_old_ppr_rec.ppr_attribute1,
p_ppr_attribute2 => l_old_ppr_rec.ppr_attribute2,
p_ppr_attribute3 => l_old_ppr_rec.ppr_attribute3,
p_ppr_attribute4 => l_old_ppr_rec.ppr_attribute4,
p_ppr_attribute5 => l_old_ppr_rec.ppr_attribute5,
p_ppr_attribute6 => l_old_ppr_rec.ppr_attribute6,
p_ppr_attribute7 => l_old_ppr_rec.ppr_attribute7,
p_ppr_attribute8 => l_old_ppr_rec.ppr_attribute8,
p_ppr_attribute9 => l_old_ppr_rec.ppr_attribute9,
p_ppr_attribute10 => l_old_ppr_rec.ppr_attribute10,
p_ppr_attribute11 => l_old_ppr_rec.ppr_attribute11,
p_ppr_attribute12 => l_old_ppr_rec.ppr_attribute12,
p_ppr_attribute13 => l_old_ppr_rec.ppr_attribute13,
p_ppr_attribute14 => l_old_ppr_rec.ppr_attribute14,
p_ppr_attribute15 => l_old_ppr_rec.ppr_attribute15,
p_ppr_attribute16 => l_old_ppr_rec.ppr_attribute16,
p_ppr_attribute17 => l_old_ppr_rec.ppr_attribute17,
p_ppr_attribute18 => l_old_ppr_rec.ppr_attribute18,
p_ppr_attribute19 => l_old_ppr_rec.ppr_attribute19,
p_ppr_attribute20 => l_old_ppr_rec.ppr_attribute20,
p_ppr_attribute21 => l_old_ppr_rec.ppr_attribute21,
p_ppr_attribute22 => l_old_ppr_rec.ppr_attribute22,
p_ppr_attribute23 => l_old_ppr_rec.ppr_attribute23,
p_ppr_attribute24 => l_old_ppr_rec.ppr_attribute24,
p_ppr_attribute25 => l_old_ppr_rec.ppr_attribute25,
p_ppr_attribute26 => l_old_ppr_rec.ppr_attribute26,
p_ppr_attribute27 => l_old_ppr_rec.ppr_attribute27,
p_ppr_attribute28 => l_old_ppr_rec.ppr_attribute28,
p_ppr_attribute29 => l_old_ppr_rec.ppr_attribute29,
p_ppr_attribute30 => l_old_ppr_rec.ppr_attribute30,
p_request_id => l_old_ppr_rec.request_id,
p_program_application_id => l_old_ppr_rec.program_application_id,
p_program_id => l_old_ppr_rec.program_id,
p_program_update_date => l_old_ppr_rec.program_update_date,
p_object_version_number => l_old_ppr_rec.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => l_datetrack_mode
);
p_program_update_date => l_old_ppr_rec.program_update_date,
p_object_version_number => l_ppr_object_version_number,
p_effective_date => p_effective_date -- Bug : 5124 As per Jeana, data
-- should be reinstated with system date
-- rather than p_effective_date
);
l_datetrack_mode := hr_api.g_update;
ben_prmry_care_prvdr_api.update_prmry_care_prvdr (p_validate => FALSE,
p_prmry_care_prvdr_id => l_old_ppr_rec.prmry_care_prvdr_id,
p_effective_start_date => l_ppr_effective_start_date,
p_effective_end_date => l_ppr_effective_end_date,
p_name => l_old_ppr_rec.NAME,
p_ext_ident => l_old_ppr_rec.ext_ident,
p_prmry_care_prvdr_typ_cd => l_old_ppr_rec.prmry_care_prvdr_typ_cd,
p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
p_elig_cvrd_dpnt_id => p_elig_cvrd_dpnt_id,
p_business_group_id => p_business_group_id,
p_ppr_attribute_category => l_old_ppr_rec.ppr_attribute_category,
p_ppr_attribute1 => l_old_ppr_rec.ppr_attribute1,
p_ppr_attribute2 => l_old_ppr_rec.ppr_attribute2,
p_ppr_attribute3 => l_old_ppr_rec.ppr_attribute3,
p_ppr_attribute4 => l_old_ppr_rec.ppr_attribute4,
p_ppr_attribute5 => l_old_ppr_rec.ppr_attribute5,
p_ppr_attribute6 => l_old_ppr_rec.ppr_attribute6,
p_ppr_attribute7 => l_old_ppr_rec.ppr_attribute7,
p_ppr_attribute8 => l_old_ppr_rec.ppr_attribute8,
p_ppr_attribute9 => l_old_ppr_rec.ppr_attribute9,
p_ppr_attribute10 => l_old_ppr_rec.ppr_attribute10,
p_ppr_attribute11 => l_old_ppr_rec.ppr_attribute11,
p_ppr_attribute12 => l_old_ppr_rec.ppr_attribute12,
p_ppr_attribute13 => l_old_ppr_rec.ppr_attribute13,
p_ppr_attribute14 => l_old_ppr_rec.ppr_attribute14,
p_ppr_attribute15 => l_old_ppr_rec.ppr_attribute15,
p_ppr_attribute16 => l_old_ppr_rec.ppr_attribute16,
p_ppr_attribute17 => l_old_ppr_rec.ppr_attribute17,
p_ppr_attribute18 => l_old_ppr_rec.ppr_attribute18,
p_ppr_attribute19 => l_old_ppr_rec.ppr_attribute19,
p_ppr_attribute20 => l_old_ppr_rec.ppr_attribute20,
p_ppr_attribute21 => l_old_ppr_rec.ppr_attribute21,
p_ppr_attribute22 => l_old_ppr_rec.ppr_attribute22,
p_ppr_attribute23 => l_old_ppr_rec.ppr_attribute23,
p_ppr_attribute24 => l_old_ppr_rec.ppr_attribute24,
p_ppr_attribute25 => l_old_ppr_rec.ppr_attribute25,
p_ppr_attribute26 => l_old_ppr_rec.ppr_attribute26,
p_ppr_attribute27 => l_old_ppr_rec.ppr_attribute27,
p_ppr_attribute28 => l_old_ppr_rec.ppr_attribute28,
p_ppr_attribute29 => l_old_ppr_rec.ppr_attribute29,
p_ppr_attribute30 => l_old_ppr_rec.ppr_attribute30,
p_request_id => l_old_ppr_rec.request_id,
p_program_application_id => l_old_ppr_rec.program_application_id,
p_program_id => l_old_ppr_rec.program_id,
p_program_update_date => l_old_ppr_rec.program_update_date,
p_object_version_number => l_old_ppr_rec.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => l_datetrack_mode
);
select pea.CMPLTD_DT,
pea.ACTN_TYP_ID
from ben_prtt_enrt_actn_f pea
where pea.per_in_ler_id = p_bckdt_per_in_ler_id
and pea.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and pea.business_group_id = p_business_group_id
and nvl(pea.pl_bnf_id, -1) = nvl(p_old_pl_bnf_id, -1)
and nvl(pea.elig_cvrd_dpnt_id, -1) = nvl(p_old_elig_cvrd_dpnt_id, -1)
and pea.effective_end_date = hr_api.g_eot --BUG 4502165 fix
-- and pea.PL_BNF_ID is null
-- and pea.ELIG_CVRD_DPNT_ID is null
union
select pea.OVRDN_THRU_DT, -- used as pea.CMPLTD_DT
pea.PL_TYP_ID -- used as ACTN_TYP_ID
from ben_le_clsn_n_rstr pea
where pea.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and pea.business_group_id = p_business_group_id
and pea.per_in_ler_id = p_bckdt_per_in_ler_id
and p_effective_date between pea.effective_start_date
and pea.effective_end_date
and nvl(pea.enrt_bnft_id, -1) = nvl(p_old_pl_bnf_id, -1)
-- enrt_bnft_id used as PL_BNF_ID
and nvl(pea.dpnt_person_id, -1) = nvl(p_old_elig_cvrd_dpnt_id, -1)
-- dpnt_person_id used as elig_cvrd_dpnt_id
-- and pea.ENRT_BNFT_ID is null -- used as pea.PL_BNF_ID
-- and pea.DPNT_PERSON_ID is null -- used as ELIG_CVRD_DPNT_ID is null
and pea.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_ACTN_F'
order by 1; -- pbn.effective_end_date; -- Low to High
select pea.*
from ben_prtt_enrt_actn_f pea
where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pea.actn_typ_id = cp_actn_typ_id
and pea.business_group_id = p_business_group_id
and pea.per_in_ler_id = p_per_in_ler_id
and nvl(pea.PL_BNF_ID, -1) = nvl(p_PL_BNF_ID, -1)
and nvl(pea.ELIG_CVRD_DPNT_ID, -1) = nvl(p_ELIG_CVRD_DPNT_ID, -1)
-- and pea.PL_BNF_ID is null
-- and pea.ELIG_CVRD_DPNT_ID is null
--
and p_effective_date between pea.effective_start_date
and pea.effective_end_date;
ben_prtt_enrt_actn_api.update_prtt_enrt_actn
(p_cmpltd_dt => l_old_pea_rec.CMPLTD_DT
,p_prtt_enrt_actn_id => l_pea_rec.prtt_enrt_actn_id
,p_prtt_enrt_rslt_id => l_pea_rec.prtt_enrt_rslt_id
,p_rslt_object_version_number => l_rslt_ovn
,p_actn_typ_id => l_pea_rec.actn_typ_id
,p_rqd_flag => l_pea_rec.rqd_flag
,p_effective_date => p_effective_date
,p_post_rslt_flag => 'Y' -- p_post_rslt_flag
,p_business_group_id => p_business_group_id
,p_effective_start_date => l_pea_effective_start_date
,p_effective_end_date => l_pea_effective_end_date
,p_object_version_number => l_pea_rec.object_version_number
,p_datetrack_mode => l_datetrack_mode
);
select cpp.DPNT_DSGN_CTFN_RECD_DT,
pea.ACTN_TYP_ID,
cpp.DPNT_DSGN_CTFN_TYP_CD
from ben_prtt_enrt_actn_f pea,
ben_cvrd_dpnt_ctfn_prvdd_f cpp
where pea.per_in_ler_id = p_bckdt_per_in_ler_id
and pea.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and pea.business_group_id = p_business_group_id
and pea.elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
and cpp.PRTT_ENRT_ACTN_ID = pea.PRTT_ENRT_ACTN_ID
and pea.elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
and cpp.elig_cvrd_dpnt_id = p_old_elig_cvrd_dpnt_id
-- and pea.PL_BNF_ID is null
-- and pea.ELIG_CVRD_DPNT_ID is null
order by 1; -- pbn.effective_end_date; -- Low to High
select cpp.*
from ben_prtt_enrt_actn_f pea,
ben_cvrd_dpnt_ctfn_prvdd_f cpp
where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pea.actn_typ_id = cp_actn_typ_id
and pea.business_group_id = p_business_group_id
and pea.per_in_ler_id = p_per_in_ler_id
and cpp.PRTT_ENRT_ACTN_ID = pea.PRTT_ENRT_ACTN_ID
and pea.ELIG_CVRD_DPNT_ID = p_elig_cvrd_dpnt_id
and cpp.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and cpp.DPNT_DSGN_CTFN_TYP_CD = cp_DPNT_DSGN_CTFN_TYP_CD
--
and p_effective_date between pea.effective_start_date
and pea.effective_end_date
and p_effective_date between cpp.effective_start_date
and cpp.effective_end_date;
BEN_cvrd_dpnt_ctfn_prvdd_API.update_cvrd_dpnt_ctfn_prvdd
(p_validate => FALSE
,p_CVRD_DPNT_CTFN_PRVDD_ID => l_cpp_rec.CVRD_DPNT_CTFN_PRVDD_ID
,p_EFFECTIVE_START_DATE => l_cpp_EFFECTIVE_START_DATE
,p_EFFECTIVE_END_DATE => l_cpp_EFFECTIVE_END_DATE
,p_DPNT_DSGN_CTFN_TYP_CD => l_cpp_rec.DPNT_DSGN_CTFN_TYP_CD
,p_DPNT_DSGN_CTFN_RQD_FLAG => l_cpp_rec.DPNT_DSGN_CTFN_RQD_FLAG
,p_DPNT_DSGN_CTFN_RECD_DT => l_old_cpp_rec.DPNT_DSGN_CTFN_RECD_DT
,p_ELIG_CVRD_DPNT_ID => l_cpp_rec.ELIG_CVRD_DPNT_ID
,p_prtt_enrt_actn_id => l_cpp_rec.PRTT_ENRT_ACTN_ID
,p_BUSINESS_GROUP_ID => l_cpp_rec.BUSINESS_GROUP_ID
,p_CCP_ATTRIBUTE_CATEGORY => l_cpp_rec.CCP_ATTRIBUTE_CATEGORY
,p_CCP_ATTRIBUTE1 => l_cpp_rec.CCP_ATTRIBUTE1
,p_CCP_ATTRIBUTE2 => l_cpp_rec.CCP_ATTRIBUTE2
,p_CCP_ATTRIBUTE3 => l_cpp_rec.CCP_ATTRIBUTE3
,p_CCP_ATTRIBUTE4 => l_cpp_rec.CCP_ATTRIBUTE4
,p_CCP_ATTRIBUTE5 => l_cpp_rec.CCP_ATTRIBUTE5
,p_CCP_ATTRIBUTE6 => l_cpp_rec.CCP_ATTRIBUTE6
,p_CCP_ATTRIBUTE7 => l_cpp_rec.CCP_ATTRIBUTE7
,p_CCP_ATTRIBUTE8 => l_cpp_rec.CCP_ATTRIBUTE8
,p_CCP_ATTRIBUTE9 => l_cpp_rec.CCP_ATTRIBUTE9
,p_CCP_ATTRIBUTE10 => l_cpp_rec.CCP_ATTRIBUTE10
,p_CCP_ATTRIBUTE11 => l_cpp_rec.CCP_ATTRIBUTE11
,p_CCP_ATTRIBUTE12 => l_cpp_rec.CCP_ATTRIBUTE12
,p_CCP_ATTRIBUTE13 => l_cpp_rec.CCP_ATTRIBUTE13
,p_CCP_ATTRIBUTE14 => l_cpp_rec.CCP_ATTRIBUTE14
,p_CCP_ATTRIBUTE15 => l_cpp_rec.CCP_ATTRIBUTE15
,p_CCP_ATTRIBUTE16 => l_cpp_rec.CCP_ATTRIBUTE16
,p_CCP_ATTRIBUTE17 => l_cpp_rec.CCP_ATTRIBUTE17
,p_CCP_ATTRIBUTE18 => l_cpp_rec.CCP_ATTRIBUTE18
,p_CCP_ATTRIBUTE19 => l_cpp_rec.CCP_ATTRIBUTE19
,p_CCP_ATTRIBUTE20 => l_cpp_rec.CCP_ATTRIBUTE20
,p_CCP_ATTRIBUTE21 => l_cpp_rec.CCP_ATTRIBUTE21
,p_CCP_ATTRIBUTE22 => l_cpp_rec.CCP_ATTRIBUTE22
,p_CCP_ATTRIBUTE23 => l_cpp_rec.CCP_ATTRIBUTE23
,p_CCP_ATTRIBUTE24 => l_cpp_rec.CCP_ATTRIBUTE24
,p_CCP_ATTRIBUTE25 => l_cpp_rec.CCP_ATTRIBUTE25
,p_CCP_ATTRIBUTE26 => l_cpp_rec.CCP_ATTRIBUTE26
,p_CCP_ATTRIBUTE27 => l_cpp_rec.CCP_ATTRIBUTE27
,p_CCP_ATTRIBUTE28 => l_cpp_rec.CCP_ATTRIBUTE28
,p_CCP_ATTRIBUTE29 => l_cpp_rec.CCP_ATTRIBUTE29
,p_CCP_ATTRIBUTE30 => l_cpp_rec.CCP_ATTRIBUTE30
,p_request_id => l_cpp_rec.REQUEST_ID
,p_program_application_id => l_cpp_rec.PROGRAM_APPLICATION_ID
,p_program_id => l_cpp_rec.PROGRAM_ID
,p_program_update_date => l_cpp_rec.PROGRAM_UPDATE_DATE
,p_OBJECT_VERSION_NUMBER => l_cpp_rec.OBJECT_VERSION_NUMBER
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
select
pdp_old.EFFECTIVE_END_DATE,
pdp_old.CVG_STRT_DT,
pdp_old.CVG_THRU_DT,
pdp_old.CVG_PNDG_FLAG,
pdp_old.OVRDN_FLAG,
pdp_old.OVRDN_THRU_DT,
pdp_old.PRTT_ENRT_RSLT_ID,
pdp_old.DPNT_PERSON_ID,
pdp_old.PER_IN_LER_ID,
pdp_old.BUSINESS_GROUP_ID,
pdp_old.PDP_ATTRIBUTE_CATEGORY,
pdp_old.PDP_ATTRIBUTE1,
pdp_old.PDP_ATTRIBUTE2,
pdp_old.PDP_ATTRIBUTE3,
pdp_old.PDP_ATTRIBUTE4,
pdp_old.PDP_ATTRIBUTE5,
pdp_old.PDP_ATTRIBUTE6,
pdp_old.PDP_ATTRIBUTE7,
pdp_old.PDP_ATTRIBUTE8,
pdp_old.PDP_ATTRIBUTE9,
pdp_old.PDP_ATTRIBUTE10,
pdp_old.PDP_ATTRIBUTE11,
pdp_old.PDP_ATTRIBUTE12,
pdp_old.PDP_ATTRIBUTE13,
pdp_old.PDP_ATTRIBUTE14,
pdp_old.PDP_ATTRIBUTE15,
pdp_old.PDP_ATTRIBUTE16,
pdp_old.PDP_ATTRIBUTE17,
pdp_old.PDP_ATTRIBUTE18,
pdp_old.PDP_ATTRIBUTE19,
pdp_old.PDP_ATTRIBUTE20,
pdp_old.PDP_ATTRIBUTE21,
pdp_old.PDP_ATTRIBUTE22,
pdp_old.PDP_ATTRIBUTE23,
pdp_old.PDP_ATTRIBUTE24,
pdp_old.PDP_ATTRIBUTE25,
pdp_old.PDP_ATTRIBUTE26,
pdp_old.PDP_ATTRIBUTE27,
pdp_old.PDP_ATTRIBUTE28,
pdp_old.PDP_ATTRIBUTE29,
pdp_old.PDP_ATTRIBUTE30,
pdp_old.LAST_UPDATE_DATE,
pdp_old.LAST_UPDATED_BY,
pdp_old.LAST_UPDATE_LOGIN,
pdp_old.CREATED_BY,
pdp_old.CREATION_DATE,
pdp_old.REQUEST_ID,
pdp_old.PROGRAM_APPLICATION_ID,
pdp_old.PROGRAM_ID,
pdp_old.PROGRAM_UPDATE_DATE,
pdp_old.OBJECT_VERSION_NUMBER,
pdp_old.elig_cvrd_dpnt_id,
pdp_old.EFFECTIVE_START_DATE
from ben_elig_cvrd_dpnt_f pdp_old
where pdp_old.per_in_ler_id = p_bckdt_per_in_ler_id
and pdp_old.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and pdp_old.business_group_id = p_business_group_id
union
select
pdp_old.EFFECTIVE_END_DATE,
pdp_old.CVG_STRT_DT,
pdp_old.CVG_THRU_DT,
pdp_old.CVG_PNDG_FLAG,
pdp_old.OVRDN_FLAG,
pdp_old.OVRDN_THRU_DT,
pdp_old.PRTT_ENRT_RSLT_ID,
pdp_old.DPNT_PERSON_ID,
pdp_old.PER_IN_LER_ID,
pdp_old.BUSINESS_GROUP_ID,
pdp_old.LCR_ATTRIBUTE_CATEGORY,
pdp_old.LCR_ATTRIBUTE1,
pdp_old.LCR_ATTRIBUTE2,
pdp_old.LCR_ATTRIBUTE3,
pdp_old.LCR_ATTRIBUTE4,
pdp_old.LCR_ATTRIBUTE5,
pdp_old.LCR_ATTRIBUTE6,
pdp_old.LCR_ATTRIBUTE7,
pdp_old.LCR_ATTRIBUTE8,
pdp_old.LCR_ATTRIBUTE9,
pdp_old.LCR_ATTRIBUTE10,
pdp_old.LCR_ATTRIBUTE11,
pdp_old.LCR_ATTRIBUTE12,
pdp_old.LCR_ATTRIBUTE13,
pdp_old.LCR_ATTRIBUTE14,
pdp_old.LCR_ATTRIBUTE15,
pdp_old.LCR_ATTRIBUTE16,
pdp_old.LCR_ATTRIBUTE17,
pdp_old.LCR_ATTRIBUTE18,
pdp_old.LCR_ATTRIBUTE19,
pdp_old.LCR_ATTRIBUTE20,
pdp_old.LCR_ATTRIBUTE21,
pdp_old.LCR_ATTRIBUTE22,
pdp_old.LCR_ATTRIBUTE23,
pdp_old.LCR_ATTRIBUTE24,
pdp_old.LCR_ATTRIBUTE25,
pdp_old.LCR_ATTRIBUTE26,
pdp_old.LCR_ATTRIBUTE27,
pdp_old.LCR_ATTRIBUTE28,
pdp_old.LCR_ATTRIBUTE29,
pdp_old.LCR_ATTRIBUTE30,
pdp_old.LAST_UPDATE_DATE,
pdp_old.LAST_UPDATED_BY,
pdp_old.LAST_UPDATE_LOGIN,
pdp_old.CREATED_BY,
pdp_old.CREATION_DATE,
pdp_old.REQUEST_ID,
pdp_old.PROGRAM_APPLICATION_ID,
pdp_old.PROGRAM_ID,
pdp_old.PROGRAM_UPDATE_DATE,
pdp_old.OBJECT_VERSION_NUMBER,
pdp_old.BKUP_TBL_ID,
pdp_old.EFFECTIVE_START_DATE
from ben_le_clsn_n_rstr pdp_old
where pdp_old.per_in_ler_id = p_bckdt_per_in_ler_id
and pdp_old.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and pdp_old.business_group_id = p_business_group_id
and pdp_old.bkup_tbl_typ_cd = 'BEN_ELIG_CVRD_DPNT_F'
order by 1; -- pdp_old.effective_end_date; -- Low to High
select 'Y'
from ben_elig_cvrd_dpnt_f pdp_old
where pdp_old.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and pdp_old.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and (pdp_old.effective_end_date = hr_api.g_eot or
pdp_old.effective_end_date = (select max(effective_end_date)
from ben_elig_cvrd_dpnt_f
where elig_cvrd_dpnt_id = pdp_old.elig_cvrd_dpnt_id
and prtt_enrt_rslt_id = pdp_old.prtt_enrt_rslt_id))
and (pdp_old.cvg_thru_dt is null or pdp_old.cvg_thru_dt = hr_api.g_eot)
/*Bug 12419118: Added below exists clause*/
and exists
(select '1' from ben_elig_cvrd_dpnt_f
where elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and effective_end_date = hr_api.g_eot
and cvg_thru_dt = hr_api.g_eot
and per_in_ler_id = p_bckdt_per_in_ler_id
union
select '1' from ben_le_clsn_n_rstr
where BKUP_TBL_ID = p_elig_cvrd_dpnt_id
and prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and effective_end_date = hr_api.g_eot
and cvg_thru_dt = hr_api.g_eot
and per_in_ler_id = p_bckdt_per_in_ler_id
and bkup_tbl_typ_cd = 'BEN_ELIG_CVRD_DPNT_F'
)
union
select 'Y'
from ben_le_clsn_n_rstr pdp_old
where pdp_old.bkup_tbl_id = p_elig_cvrd_dpnt_id
and pdp_old.bkup_tbl_typ_cd = 'BEN_ELIG_CVRD_DPNT_F'
and pdp_old.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and ((pdp_old.cvg_thru_dt is null or pdp_old.cvg_thru_dt = hr_api.g_eot) and
pdp_old.effective_end_date = hr_api.g_eot
)
and pdp_old.per_in_ler_id = p_bckdt_per_in_ler_id; --Bug 12419118
select pdp.*
from ben_elig_cvrd_dpnt_f pdp ,
ben_per_in_ler pil
where pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pdp.cvg_strt_dt is not null
and nvl(pdp.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
and pdp.business_group_id = p_business_group_id
and pdp.dpnt_person_id = cp_dpnt_person_id
and p_effective_date between pdp.effective_start_date
and pdp.effective_end_date
and pdp.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select edg.*
from ben_elig_dpnt edg
where edg.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and edg.business_group_id = p_business_group_id
and edg.dpnt_person_id = cp_dpnt_person_id;
select enrt_cvg_strt_dt
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between effective_start_date
and effective_end_date;
p_datetrack_mode => hr_api.g_insert,
p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_pdp_object_version_number,
p_multi_row_actn => TRUE );
p_datetrack_mode => hr_api.g_update,
p_elig_cvrd_dpnt_id => l_elig_cvrd_dpnt_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_pdp_object_version_number,
p_multi_row_actn => TRUE );
select pbc.BNF_CTFN_RECD_DT,
pea.ACTN_TYP_ID,
pbc.BNF_CTFN_TYP_CD
from ben_prtt_enrt_actn_f pea,
ben_pl_bnf_ctfn_prvdd_f pbc
where pea.per_in_ler_id = p_bckdt_per_in_ler_id
and pea.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and pea.business_group_id = p_business_group_id
and pea.PL_BNF_ID = p_old_PL_BNF_ID
and pbc.PRTT_ENRT_ACTN_ID = pea.PRTT_ENRT_ACTN_ID
and pbc.PL_BNF_ID = p_old_PL_BNF_ID
order by 1; -- pbn.effective_end_date; -- Low to High
select pbc.*
from ben_prtt_enrt_actn_f pea,
ben_pl_bnf_ctfn_prvdd_f pbc
where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pea.actn_typ_id = cp_actn_typ_id
and pea.business_group_id = p_business_group_id
and pea.per_in_ler_id = p_per_in_ler_id
and pbc.PRTT_ENRT_ACTN_ID = pea.PRTT_ENRT_ACTN_ID
and pea.ELIG_CVRD_DPNT_ID = p_PL_BNF_ID
and pbc.PL_BNF_ID = p_PL_BNF_ID
and pbc.BNF_CTFN_TYP_CD = cp_BNF_CTFN_TYP_CD
and p_effective_date between pea.effective_start_date
and pea.effective_end_date
and p_effective_date between pbc.effective_start_date
and pbc.effective_end_date;
BEN_pl_bnf_ctfn_prvdd_API.update_pl_bnf_ctfn_prvdd
(p_validate => FALSE
,p_PL_BNF_CTFN_PRVDD_ID => l_pbc_rec.PL_BNF_CTFN_PRVDD_ID
,p_EFFECTIVE_START_DATE => l_pbc_EFFECTIVE_START_DATE
,p_EFFECTIVE_END_DATE => l_pbc_EFFECTIVE_END_DATE
,p_BNF_CTFN_TYP_CD => l_pbc_rec.BNF_CTFN_TYP_CD
,p_BNF_CTFN_RECD_DT => l_old_pbc_rec.BNF_CTFN_RECD_DT
,p_BNF_CTFN_RQD_FLAG => l_pbc_rec.BNF_CTFN_RQD_FLAG
,p_PL_BNF_ID => l_pbc_rec.PL_BNF_ID
,p_prtt_enrt_actn_id => l_pbc_rec.PRTT_ENRT_ACTN_ID
,p_BUSINESS_GROUP_ID => l_pbc_rec.BUSINESS_GROUP_ID
,p_PBC_ATTRIBUTE_CATEGORY => l_pbc_rec.PBC_ATTRIBUTE_CATEGORY
,p_PBC_ATTRIBUTE1 => l_pbc_rec.PBC_ATTRIBUTE1
,p_PBC_ATTRIBUTE2 => l_pbc_rec.PBC_ATTRIBUTE2
,p_PBC_ATTRIBUTE3 => l_pbc_rec.PBC_ATTRIBUTE3
,p_PBC_ATTRIBUTE4 => l_pbc_rec.PBC_ATTRIBUTE4
,p_PBC_ATTRIBUTE5 => l_pbc_rec.PBC_ATTRIBUTE5
,p_PBC_ATTRIBUTE6 => l_pbc_rec.PBC_ATTRIBUTE6
,p_PBC_ATTRIBUTE7 => l_pbc_rec.PBC_ATTRIBUTE7
,p_PBC_ATTRIBUTE8 => l_pbc_rec.PBC_ATTRIBUTE8
,p_PBC_ATTRIBUTE9 => l_pbc_rec.PBC_ATTRIBUTE9
,p_PBC_ATTRIBUTE10 => l_pbc_rec.PBC_ATTRIBUTE10
,p_PBC_ATTRIBUTE11 => l_pbc_rec.PBC_ATTRIBUTE11
,p_PBC_ATTRIBUTE12 => l_pbc_rec.PBC_ATTRIBUTE12
,p_PBC_ATTRIBUTE13 => l_pbc_rec.PBC_ATTRIBUTE13
,p_PBC_ATTRIBUTE14 => l_pbc_rec.PBC_ATTRIBUTE14
,p_PBC_ATTRIBUTE15 => l_pbc_rec.PBC_ATTRIBUTE15
,p_PBC_ATTRIBUTE16 => l_pbc_rec.PBC_ATTRIBUTE16
,p_PBC_ATTRIBUTE17 => l_pbc_rec.PBC_ATTRIBUTE17
,p_PBC_ATTRIBUTE18 => l_pbc_rec.PBC_ATTRIBUTE18
,p_PBC_ATTRIBUTE19 => l_pbc_rec.PBC_ATTRIBUTE19
,p_PBC_ATTRIBUTE20 => l_pbc_rec.PBC_ATTRIBUTE20
,p_PBC_ATTRIBUTE21 => l_pbc_rec.PBC_ATTRIBUTE21
,p_PBC_ATTRIBUTE22 => l_pbc_rec.PBC_ATTRIBUTE22
,p_PBC_ATTRIBUTE23 => l_pbc_rec.PBC_ATTRIBUTE23
,p_PBC_ATTRIBUTE24 => l_pbc_rec.PBC_ATTRIBUTE24
,p_PBC_ATTRIBUTE25 => l_pbc_rec.PBC_ATTRIBUTE25
,p_PBC_ATTRIBUTE26 => l_pbc_rec.PBC_ATTRIBUTE26
,p_PBC_ATTRIBUTE27 => l_pbc_rec.PBC_ATTRIBUTE27
,p_PBC_ATTRIBUTE28 => l_pbc_rec.PBC_ATTRIBUTE28
,p_PBC_ATTRIBUTE29 => l_pbc_rec.PBC_ATTRIBUTE29
,p_PBC_ATTRIBUTE30 => l_pbc_rec.PBC_ATTRIBUTE30
,p_request_id => l_pbc_rec.REQUEST_ID
,p_program_application_id => l_pbc_rec.PROGRAM_APPLICATION_ID
,p_program_id => l_pbc_rec.PROGRAM_ID
,p_program_update_date => l_pbc_rec.PROGRAM_UPDATE_DATE
,p_OBJECT_VERSION_NUMBER => l_pbc_rec.OBJECT_VERSION_NUMBER
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
select
pbn.EFFECTIVE_END_DATE,
pbn.pbn_ATTRIBUTE1,
pbn.pbn_ATTRIBUTE2,
pbn.pbn_ATTRIBUTE3,
pbn.pbn_ATTRIBUTE4,
pbn.pbn_ATTRIBUTE5,
pbn.pbn_ATTRIBUTE6,
pbn.pbn_ATTRIBUTE7,
pbn.pbn_ATTRIBUTE8,
pbn.pbn_ATTRIBUTE9,
pbn.pbn_ATTRIBUTE10,
pbn.pbn_ATTRIBUTE11,
pbn.pbn_ATTRIBUTE12,
pbn.pbn_ATTRIBUTE13,
pbn.pbn_ATTRIBUTE14,
pbn.pbn_ATTRIBUTE15,
pbn.pbn_ATTRIBUTE16,
pbn.pbn_ATTRIBUTE17,
pbn.pbn_ATTRIBUTE18,
pbn.pbn_ATTRIBUTE19,
pbn.pbn_ATTRIBUTE20,
pbn.pbn_ATTRIBUTE21,
pbn.pbn_ATTRIBUTE22,
pbn.pbn_ATTRIBUTE23,
pbn.pbn_ATTRIBUTE24,
pbn.pbn_ATTRIBUTE25,
pbn.pbn_ATTRIBUTE26,
pbn.pbn_ATTRIBUTE27,
pbn.pbn_ATTRIBUTE28,
pbn.pbn_ATTRIBUTE29,
pbn.pbn_ATTRIBUTE30,
pbn.LAST_UPDATE_DATE,
pbn.LAST_UPDATED_BY,
pbn.LAST_UPDATE_LOGIN,
pbn.CREATED_BY,
pbn.CREATION_DATE,
pbn.REQUEST_ID,
pbn.PROGRAM_APPLICATION_ID,
pbn.PROGRAM_ID,
pbn.PROGRAM_UPDATE_DATE,
pbn.OBJECT_VERSION_NUMBER,
pbn.pl_bnf_id,
pbn.EFFECTIVE_START_DATE,
pbn.PRMRY_CNTNGNT_CD,
pbn.PCT_DSGD_NUM,
pbn.AMT_DSGD_VAL,
pbn.AMT_DSGD_UOM,
pbn.ADDL_INSTRN_TXT,
pbn.DSGN_THRU_DT,
pbn.DSGN_STRT_DT,
pbn.PRTT_ENRT_RSLT_ID,
pbn.ORGANIZATION_ID,
pbn.BNF_PERSON_ID,
pbn.TTEE_PERSON_ID,
pbn.BUSINESS_GROUP_ID,
pbn.PER_IN_LER_ID,
pbn.pbn_ATTRIBUTE_CATEGORY,
/* Bug 12964358: Check whether there are date track updated records after the effective_start_date*/
decode( (select 'Y' from dual where exists
(select 'Y' from ben_pl_bnf_f pbn1
where pbn1.per_in_ler_id = pbn.per_in_ler_id
and pbn1.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
and pbn1.business_group_id = pbn.business_group_id
and pbn1.effective_start_date > pbn.effective_start_date
and pbn1.pl_bnf_id = pbn.pl_bnf_id)),
'Y','Y','N') ftr_rec
from ben_pl_bnf_f pbn
where pbn.per_in_ler_id = p_bckdt_per_in_ler_id
and pbn.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and pbn.business_group_id = p_business_group_id
union
select
pbn.EFFECTIVE_END_DATE,
pbn.LCR_ATTRIBUTE1,
pbn.LCR_ATTRIBUTE2,
pbn.LCR_ATTRIBUTE3,
pbn.LCR_ATTRIBUTE4,
pbn.LCR_ATTRIBUTE5,
pbn.LCR_ATTRIBUTE6,
pbn.LCR_ATTRIBUTE7,
pbn.LCR_ATTRIBUTE8,
pbn.LCR_ATTRIBUTE9,
pbn.LCR_ATTRIBUTE10,
pbn.LCR_ATTRIBUTE11,
pbn.LCR_ATTRIBUTE12,
pbn.LCR_ATTRIBUTE13,
pbn.LCR_ATTRIBUTE14,
pbn.LCR_ATTRIBUTE15,
pbn.LCR_ATTRIBUTE16,
pbn.LCR_ATTRIBUTE17,
pbn.LCR_ATTRIBUTE18,
pbn.LCR_ATTRIBUTE19,
pbn.LCR_ATTRIBUTE20,
pbn.LCR_ATTRIBUTE21,
pbn.LCR_ATTRIBUTE22,
pbn.LCR_ATTRIBUTE23,
pbn.LCR_ATTRIBUTE24,
pbn.LCR_ATTRIBUTE25,
pbn.LCR_ATTRIBUTE26,
pbn.LCR_ATTRIBUTE27,
pbn.LCR_ATTRIBUTE28,
pbn.LCR_ATTRIBUTE29,
pbn.LCR_ATTRIBUTE30,
pbn.LAST_UPDATE_DATE,
pbn.LAST_UPDATED_BY,
pbn.LAST_UPDATE_LOGIN,
pbn.CREATED_BY,
pbn.CREATION_DATE,
pbn.REQUEST_ID,
pbn.PROGRAM_APPLICATION_ID,
pbn.PROGRAM_ID,
pbn.PROGRAM_UPDATE_DATE,
pbn.OBJECT_VERSION_NUMBER,
pbn.BKUP_TBL_ID,
pbn.EFFECTIVE_START_DATE,
pbn.PRMRY_CNTNGNT_CD,
pbn.PCT_DSGD_NUM,
pbn.AMT_DSGD_VAL,
pbn.AMT_DSGD_UOM,
pbn.ADDL_INSTRN_TXT,
pbn.DSGN_THRU_DT,
pbn.DSGN_STRT_DT,
pbn.PRTT_ENRT_RSLT_ID,
pbn.ORGANIZATION_ID,
pbn.BNF_PERSON_ID,
pbn.PERSON_TTEE_ID,
pbn.BUSINESS_GROUP_ID,
pbn.PER_IN_LER_ID,
pbn.LCR_ATTRIBUTE_CATEGORY,
/* Bug 12964358: Check whether there are date track updated records after the effective_start_date*/
decode( (select 'Y' from dual where exists
(select 'Y' from ben_le_clsn_n_rstr pbn1
where pbn1.per_in_ler_id = pbn.per_in_ler_id
and pbn1.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
and pbn1.business_group_id = pbn.business_group_id
and pbn1.effective_start_date > pbn.effective_start_date
and pbn1.BKUP_TBL_ID = pbn.BKUP_TBL_ID)),
'Y','Y','N') ftr_rec
from ben_le_clsn_n_rstr pbn
where pbn.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and pbn.business_group_id = p_business_group_id
and pbn.per_in_ler_id = p_bckdt_per_in_ler_id
and p_effective_date between pbn.effective_start_date
and pbn.effective_end_date
and pbn.bkup_tbl_typ_cd = 'BEN_PL_BNF_F'
order by 1;
select pbn.*
from ben_pl_bnf_f pbn
where pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pbn.bnf_person_id = cp_bnf_person_id
and pbn.business_group_id = p_business_group_id
-- and pbn.per_in_ler_id = p_per_in_ler_id --BUG Bug 4178570
and p_effective_date between pbn.effective_start_date
and pbn.effective_end_date;
select pbn.*
from ben_pl_bnf_f pbn
where pbn.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pbn.pl_bnf_id = c_bnf_pl_id
and pbn.business_group_id = p_business_group_id
and c_effective_date between pbn.effective_start_date
and pbn.effective_end_date;
,p_program_update_date => sysdate
,p_object_version_number => l_bnf_object_version_number
,p_multi_row_actn => FALSE --TRUE -- bug 2552295
,p_effective_date => p_effective_date
,p_dsgn_thru_dt => l_old_bnf_rec.dsgn_thru_dt
,p_dsgn_strt_dt => l_old_bnf_rec.dsgn_strt_dt);
hr_utility.set_location('In delete call create' || l_bnf_effective_end_date,10);
ben_plan_beneficiary_api.delete_PLAN_BENEFICIARY
(p_validate => false
,p_pl_bnf_id => l_pl_bnf_id
,p_effective_start_date => l_bnf_effective_start_date
,p_effective_end_date => l_bnf_effective_end_date
,p_object_version_number => l_bnf_object_version_number
,p_business_group_id => p_business_group_id
,p_effective_date => l_del_date+1
,p_datetrack_mode => hr_api.g_delete
,p_multi_row_actn => FALSE
);
as of sysdate, then update mode will be 'update' else 'correction' . Flag l_upd_mode is set based upon the date on which
plan beneficiary is reinstated. Also end dated beneficiary should be reinstated correctly on reprocessing the LE. Added below 'if' */
if(l_upd_mode) then
l_datetrack_mode := hr_api.g_update;
l_datetrack_mode := hr_api.g_update;
hr_utility.set_location('update call ' ,10);
ben_plan_beneficiary_api.update_PLAN_BENEFICIARY
(p_validate => FALSE
,p_pl_bnf_id => l_bnf_rec.pl_bnf_id
,p_effective_start_date => l_bnf_effective_start_date
,p_effective_end_date => l_bnf_effective_end_date
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_bnf_person_id => l_old_bnf_rec.bnf_person_id
,p_organization_id => l_old_bnf_rec.organization_id
,p_ttee_person_id => l_old_bnf_rec.ttee_person_id
,p_prmry_cntngnt_cd => l_old_bnf_rec.prmry_cntngnt_cd
,p_pct_dsgd_num => l_old_bnf_rec.pct_dsgd_num
,p_amt_dsgd_val => l_old_bnf_rec.amt_dsgd_val
,p_amt_dsgd_uom => l_old_bnf_rec.amt_dsgd_uom
,p_dsgn_strt_dt => l_old_bnf_rec.dsgn_strt_dt
,p_dsgn_thru_dt => l_old_bnf_rec.dsgn_thru_dt
,p_addl_instrn_txt => l_old_bnf_rec.addl_instrn_txt
,p_pbn_attribute_category => l_old_bnf_rec.pbn_attribute_category
,p_pbn_attribute1 => l_old_bnf_rec.pbn_attribute1
,p_pbn_attribute2 => l_old_bnf_rec.pbn_attribute2
,p_pbn_attribute3 => l_old_bnf_rec.pbn_attribute3
,p_pbn_attribute4 => l_old_bnf_rec.pbn_attribute4
,p_pbn_attribute5 => l_old_bnf_rec.pbn_attribute5
,p_pbn_attribute6 => l_old_bnf_rec.pbn_attribute6
,p_pbn_attribute7 => l_old_bnf_rec.pbn_attribute7
,p_pbn_attribute8 => l_old_bnf_rec.pbn_attribute8
,p_pbn_attribute9 => l_old_bnf_rec.pbn_attribute9
,p_pbn_attribute10 => l_old_bnf_rec.pbn_attribute10
,p_pbn_attribute11 => l_old_bnf_rec.pbn_attribute11
,p_pbn_attribute12 => l_old_bnf_rec.pbn_attribute12
,p_pbn_attribute13 => l_old_bnf_rec.pbn_attribute13
,p_pbn_attribute14 => l_old_bnf_rec.pbn_attribute14
,p_pbn_attribute15 => l_old_bnf_rec.pbn_attribute15
,p_pbn_attribute16 => l_old_bnf_rec.pbn_attribute16
,p_pbn_attribute17 => l_old_bnf_rec.pbn_attribute17
,p_pbn_attribute18 => l_old_bnf_rec.pbn_attribute18
,p_pbn_attribute19 => l_old_bnf_rec.pbn_attribute19
,p_pbn_attribute20 => l_old_bnf_rec.pbn_attribute20
,p_pbn_attribute21 => l_old_bnf_rec.pbn_attribute21
,p_pbn_attribute22 => l_old_bnf_rec.pbn_attribute22
,p_pbn_attribute23 => l_old_bnf_rec.pbn_attribute23
,p_pbn_attribute24 => l_old_bnf_rec.pbn_attribute24
,p_pbn_attribute25 => l_old_bnf_rec.pbn_attribute25
,p_pbn_attribute26 => l_old_bnf_rec.pbn_attribute26
,p_pbn_attribute27 => l_old_bnf_rec.pbn_attribute27
,p_pbn_attribute28 => l_old_bnf_rec.pbn_attribute28
,p_pbn_attribute29 => l_old_bnf_rec.pbn_attribute29
,p_pbn_attribute30 => l_old_bnf_rec.pbn_attribute30
,p_request_id => fnd_global.conc_request_id
,p_program_application_id => fnd_global.prog_appl_id
,p_program_id => fnd_global.conc_program_id
,p_program_update_date => sysdate
,p_object_version_number => l_upd_obj_version_number
,p_per_in_ler_id => p_per_in_ler_id
,p_effective_date => l_upd_date
,p_datetrack_mode => l_datetrack_mode
,p_multi_row_actn => FALSE -- TRUE -- bug 2552295
);
hr_utility.set_location('In delete call upd' || l_bnf_effective_end_date,10);
ben_plan_beneficiary_api.delete_PLAN_BENEFICIARY
(p_validate => false
,p_pl_bnf_id => l_pl_bnf_id
,p_effective_start_date => l_bnf_effective_start_date
,p_effective_end_date => l_bnf_effective_end_date
,p_object_version_number => l_upd_obj_version_number
,p_business_group_id => p_business_group_id
,p_effective_date => l_del_date+1
,p_datetrack_mode => hr_api.g_delete
,p_multi_row_actn => FALSE
);
select pcs.*, pea.ACTN_TYP_ID
from ben_prtt_enrt_ctfn_prvdd_f pcs,
ben_prtt_enrt_actn_f pea
where pcs.prtt_enrt_actn_id = pea.prtt_enrt_actn_id
and pcs.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and pea.per_in_ler_id = p_bckdt_per_in_ler_id
and pea.prtt_enrt_rslt_id = pcs.prtt_enrt_rslt_id
and pcs.business_group_id = p_business_group_id
and p_effective_date between pcs.effective_start_date
and pcs.effective_end_date
and p_effective_date between pea.effective_start_date
and pea.effective_end_date
/* Bug 8910111: While reinstating the certifications, if the certification records
are end dated by the next life event, then as on p_effective_date cursor will not fetch any records.
Added union condition to get the latest certification record(if records exists in update mode) while
reinstating the certification record.*/
union
select pcs.*, pea.ACTN_TYP_ID
from ben_prtt_enrt_ctfn_prvdd_f pcs,
ben_prtt_enrt_actn_f pea
where pcs.prtt_enrt_actn_id = pea.prtt_enrt_actn_id
and pcs.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
and pea.per_in_ler_id = p_bckdt_per_in_ler_id
and pea.prtt_enrt_rslt_id = pcs.prtt_enrt_rslt_id
and pcs.business_group_id = p_business_group_id
and pea.effective_start_date = (select max(effective_start_date) from ben_prtt_enrt_actn_f pea1
where pea1.per_in_ler_id = pea.per_in_ler_id
and pea1.prtt_enrt_rslt_id = pea.prtt_enrt_rslt_id
and pea1.prtt_enrt_actn_id = pea.prtt_enrt_actn_id)
and pcs.effective_start_date = (select max(effective_start_date) from ben_prtt_enrt_ctfn_prvdd_f pcs1
where pcs1.prtt_enrt_rslt_id = pcs.prtt_enrt_rslt_id
and pcs1.prtt_enrt_actn_id = pcs.prtt_enrt_actn_id);
select pcs.*
from ben_prtt_enrt_ctfn_prvdd_f pcs,
ben_prtt_enrt_actn_f pea
where pcs.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and nvl(pcs.enrt_ctfn_typ_cd, -1) = nvl(cp_enrt_ctfn_typ_cd, -1)
and nvl(pcs.enrt_r_bnft_ctfn_cd, -1) = nvl(cp_enrt_r_bnft_ctfn_cd, -1)
and pea.prtt_enrt_rslt_id = pcs.prtt_enrt_rslt_id
and pea.ACTN_TYP_ID = cp_ACTN_TYP_ID
and pcs.business_group_id = p_business_group_id
and pcs.prtt_enrt_actn_id = pea.prtt_enrt_actn_id
and pea.per_in_ler_id = p_per_in_ler_id
and p_effective_date between pcs.effective_start_date
and pcs.effective_end_date
and p_effective_date between pea.effective_start_date
and pea.effective_end_date;
BEN_prtt_enrt_ctfn_prvdd_API.update_prtt_enrt_ctfn_prvdd (
p_prtt_enrt_ctfn_prvdd_id => l_pcs_rec.PRTT_ENRT_CTFN_PRVDD_ID
,p_prtt_enrt_actn_id => l_pcs_rec.prtt_enrt_actn_id
,p_prtt_enrt_rslt_id => l_pcs_rec.prtt_enrt_rslt_id
,p_business_group_id => p_business_group_id
,p_EFFECTIVE_START_DATE => l_pcs_EFFECTIVE_START_DATE
,p_EFFECTIVE_END_DATE => l_pcs_EFFECTIVE_END_DATE
,p_ENRT_CTFN_RECD_DT => l_old_pcs_rec.ENRT_CTFN_RECD_DT
,p_ENRT_CTFN_DND_DT => l_old_pcs_rec.ENRT_CTFN_DND_DT
,p_OBJECT_VERSION_NUMBER => l_pcs_rec.OBJECT_VERSION_NUMBER
,p_effective_date => p_effective_date
,p_datetrack_mode => l_datetrack_mode
);
select pil.PRVS_STAT_CD, pil.object_version_number, pil.BCKT_PER_IN_LER_ID
from ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.business_group_id = p_business_group_id;
select
pen.EFFECTIVE_END_DATE,
pen.ASSIGNMENT_ID,
pen.BNFT_AMT,
pen.BNFT_NNMNTRY_UOM,
pen.BNFT_ORDR_NUM,
pen.BNFT_TYP_CD,
pen.BUSINESS_GROUP_ID,
pen.COMP_LVL_CD,
pen.CREATED_BY,
pen.CREATION_DATE,
pen.EFFECTIVE_START_DATE,
pen.ENRT_CVG_STRT_DT,
pen.ENRT_CVG_THRU_DT,
pen.ENRT_MTHD_CD,
pen.ENRT_OVRIDN_FLAG,
pen.ENRT_OVRID_RSN_CD,
pen.ENRT_OVRID_THRU_DT,
pen.ERLST_DEENRT_DT,
pen.LAST_UPDATED_BY,
pen.LAST_UPDATE_DATE,
pen.LAST_UPDATE_LOGIN,
pen.LER_ID,
pen.NO_LNGR_ELIG_FLAG,
pen.OBJECT_VERSION_NUMBER,
pen.OIPL_ID,
pen.OIPL_ORDR_NUM,
pen.ORGNL_ENRT_DT,
pen.PEN_ATTRIBUTE1,
pen.PEN_ATTRIBUTE10,
pen.PEN_ATTRIBUTE11,
pen.PEN_ATTRIBUTE12,
pen.PEN_ATTRIBUTE13,
pen.PEN_ATTRIBUTE14,
pen.PEN_ATTRIBUTE15,
pen.PEN_ATTRIBUTE16,
pen.PEN_ATTRIBUTE17,
pen.PEN_ATTRIBUTE18,
pen.PEN_ATTRIBUTE19,
pen.PEN_ATTRIBUTE2,
pen.PEN_ATTRIBUTE20,
pen.PEN_ATTRIBUTE21,
pen.PEN_ATTRIBUTE22,
pen.PEN_ATTRIBUTE23,
pen.PEN_ATTRIBUTE24,
pen.PEN_ATTRIBUTE25,
pen.PEN_ATTRIBUTE26,
pen.PEN_ATTRIBUTE27,
pen.PEN_ATTRIBUTE28,
pen.PEN_ATTRIBUTE29,
pen.PEN_ATTRIBUTE3,
pen.PEN_ATTRIBUTE30,
pen.PEN_ATTRIBUTE4,
pen.PEN_ATTRIBUTE5,
pen.PEN_ATTRIBUTE6,
pen.PEN_ATTRIBUTE7,
pen.PEN_ATTRIBUTE8,
pen.PEN_ATTRIBUTE9,
pen.PEN_ATTRIBUTE_CATEGORY,
pen.PERSON_ID,
pen.PER_IN_LER_ID,
pen.PGM_ID,
pen.PLIP_ORDR_NUM,
pen.PL_ID,
pen.PL_ORDR_NUM,
pen.PL_TYP_ID,
pen.PROGRAM_APPLICATION_ID,
pen.PROGRAM_ID,
pen.PROGRAM_UPDATE_DATE,
pen.PRTT_ENRT_RSLT_ID,
pen.PRTT_ENRT_RSLT_STAT_CD,
pen.PRTT_IS_CVRD_FLAG,
pen.PTIP_ID,
pen.PTIP_ORDR_NUM,
pen.REQUEST_ID,
pen.RPLCS_SSPNDD_RSLT_ID,
pen.SSPNDD_FLAG,
pen.UOM
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_id = pen.per_in_ler_id
/*Pick up both end-dated and non-end-dated results*/
and (pen.effective_end_date = hr_api.g_eot or
pen.effective_end_date = (select max(effective_end_date)
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id =
pen.prtt_enrt_rslt_id))
and (pen.enrt_cvg_thru_dt is null or
pen.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and pen.prtt_enrt_rslt_id not in (
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_prtt_enrt_rslt_f pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
and pil_inner.person_id = p_person_id
and pil_inner.business_group_id = p_business_group_id
and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
union
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_le_clsn_n_rstr pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
and pil_inner.person_id = p_person_id
and pil_inner.business_group_id = p_business_group_id
AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
)
union
select
pen.EFFECTIVE_END_DATE,
pen.ASSIGNMENT_ID,
pen.BNFT_AMT,
pen.BNFT_NNMNTRY_UOM,
pen.BNFT_ORDR_NUM,
pen.BNFT_TYP_CD,
pen.BUSINESS_GROUP_ID,
pen.COMP_LVL_CD,
pen.CREATED_BY,
pen.CREATION_DATE,
pen.EFFECTIVE_START_DATE,
pen.ENRT_CVG_STRT_DT,
pen.ENRT_CVG_THRU_DT,
pen.ENRT_MTHD_CD,
pen.ENRT_OVRIDN_FLAG,
pen.ENRT_OVRID_RSN_CD,
pen.ENRT_OVRID_THRU_DT,
pen.ERLST_DEENRT_DT,
pen.LAST_UPDATED_BY,
pen.LAST_UPDATE_DATE,
pen.LAST_UPDATE_LOGIN,
pen.LER_ID,
pen.NO_LNGR_ELIG_FLAG,
pen.OBJECT_VERSION_NUMBER,
pen.OIPL_ID,
pen.OIPL_ORDR_NUM,
pen.ORGNL_ENRT_DT,
pen.LCR_ATTRIBUTE1,
pen.LCR_ATTRIBUTE10,
pen.LCR_ATTRIBUTE11,
pen.LCR_ATTRIBUTE12,
pen.LCR_ATTRIBUTE13,
pen.LCR_ATTRIBUTE14,
pen.LCR_ATTRIBUTE15,
pen.LCR_ATTRIBUTE16,
pen.LCR_ATTRIBUTE17,
pen.LCR_ATTRIBUTE18,
pen.LCR_ATTRIBUTE19,
pen.LCR_ATTRIBUTE2,
pen.LCR_ATTRIBUTE20,
pen.LCR_ATTRIBUTE21,
pen.LCR_ATTRIBUTE22,
pen.LCR_ATTRIBUTE23,
pen.LCR_ATTRIBUTE24,
pen.LCR_ATTRIBUTE25,
pen.LCR_ATTRIBUTE26,
pen.LCR_ATTRIBUTE27,
pen.LCR_ATTRIBUTE28,
pen.LCR_ATTRIBUTE29,
pen.LCR_ATTRIBUTE3,
pen.LCR_ATTRIBUTE30,
pen.LCR_ATTRIBUTE4,
pen.LCR_ATTRIBUTE5,
pen.LCR_ATTRIBUTE6,
pen.LCR_ATTRIBUTE7,
pen.LCR_ATTRIBUTE8,
pen.LCR_ATTRIBUTE9,
pen.LCR_ATTRIBUTE_CATEGORY,
pen.PERSON_ID,
pen.PER_IN_LER_ID,
pen.PGM_ID,
pen.PLIP_ORDR_NUM,
pen.PL_ID,
pen.PL_ORDR_NUM,
pen.PL_TYP_ID,
pen.PROGRAM_APPLICATION_ID,
pen.PROGRAM_ID,
pen.PROGRAM_UPDATE_DATE,
pen.bkup_tbl_id, -- Mapped to PRTT_ENRT_RSLT_ID,
pen.PRTT_ENRT_RSLT_STAT_CD,
pen.PRTT_IS_CVRD_FLAG,
pen.PTIP_ID,
pen.PTIP_ORDR_NUM,
pen.REQUEST_ID,
pen.RPLCS_SSPNDD_RSLT_ID,
pen.SSPNDD_FLAG,
pen.UOM
from ben_le_clsn_n_rstr pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = pen.per_in_ler_id
and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and ((pen.enrt_cvg_thru_dt is null or
pen.enrt_cvg_thru_dt = hr_api.g_eot) and
--bug#2604375 - added to control updated result rows for the same per_in_ler
pen.effective_end_date = hr_api.g_eot
)
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and pen.bkup_tbl_id not in (
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_prtt_enrt_rslt_f pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
and pil_inner.person_id = p_person_id
and pil_inner.business_group_id = p_business_group_id
and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
union
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_le_clsn_n_rstr pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
and pil_inner.person_id = p_person_id
and pil_inner.business_group_id = p_business_group_id
AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
)
order by 1, 11 desc; -- pen.effective_end_date; -- Low to High
select epe.*,
pel.enrt_typ_cycl_cd,
pel.enrt_perd_end_dt,
pel.enrt_perd_strt_dt,
to_date('31-12-4712','DD-MM-YYYY') enrt_cvg_end_dt,
pel.dflt_enrt_dt
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel
where epe.per_in_ler_id = p_per_in_ler_id
and epe.business_group_id = p_business_group_id
and epe.pl_id = cp_pl_id
and nvl(epe.pgm_id, -1) = nvl(cp_pgm_id, -1)
and nvl(epe.oipl_id, -1) = nvl(cp_oipl_id, -1)
and pil.business_group_id = p_business_group_id
and pel.business_group_id = epe.business_group_id
and pil.person_id = p_person_id
and epe.per_in_ler_id = pil.per_in_ler_id
and pel.per_in_ler_id = epe.per_in_ler_id
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id;
select enb.enrt_bnft_id,
enb.entr_val_at_enrt_flag,
enb.dflt_val,
enb.val,
enb.dflt_flag,
enb.cvg_mlt_cd --Bug 3315323
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = cp_elig_per_elctbl_chc_id
-- Bug 2526994 we need take the right one
-- and nvl(enb.mx_wo_ctfn_flag,'N') = 'N' ;
select ecr.enrt_rt_id,
ecr.dflt_val,
ecr.val,
ecr.entr_val_at_enrt_flag,
ecr.acty_base_rt_id
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = cp_elig_per_elctbl_chc_id
and ecr.business_group_id = p_business_group_id
and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.spcl_rt_enrt_rt_id is null
-- and ecr.prtt_rt_val_id is null
union
select ecr.enrt_rt_id,
ecr.dflt_val,
ecr.val,
ecr.entr_val_at_enrt_flag,
ecr.acty_base_rt_id
from ben_enrt_rt ecr
where ecr.enrt_bnft_id = cp_enrt_bnft_id
and ecr.business_group_id = p_business_group_id
and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.spcl_rt_enrt_rt_id is null;
select prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
and prv.per_in_ler_id = p_bckdt_per_in_ler_id
and prv.business_group_id = p_business_group_id
and prv.acty_base_rt_id = cv_acty_base_rt_id;
select pen.*, pil.lf_evt_ocrd_dt
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.per_in_ler_id = cv_per_in_ler_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pen.business_group_id = p_business_group_id
and pil.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = hr_api.g_eot
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and (pen.enrt_cvg_thru_dt is null or
pen.enrt_cvg_thru_dt = hr_api.g_eot
);
select prv2.prtt_rt_val_id new_prv_id,
prv2.object_version_number new_prv_ovn,
prv1.*
from ben_prtt_rt_val prv1, ben_prtt_rt_val prv2
where prv1.prtt_enrt_rslt_id = v_bckdt_pen_id
and prv2.prtt_enrt_rslt_id = v_new_pen_id
and prv1.acty_base_rt_id = prv2.acty_base_rt_id
and prv1.rt_ovridn_flag = 'Y'
and prv1.rt_end_dt <> hr_api.g_eot
and prv1.rt_ovridn_thru_dt >= prv2.rt_strt_dt
-- and prv1.prtt_rt_val_stat_cd is null
and prv2.prtt_rt_val_stat_cd is null
and prv2.per_in_ler_id = p_per_in_ler_id ;
select pdp2.elig_cvrd_dpnt_id new_pdp_id,
pdp2.object_version_number new_pdp_ovn,
pdp1.*
from ben_elig_cvrd_dpnt_f pdp1,
ben_elig_cvrd_dpnt_f pdp2
where pdp1.prtt_enrt_rslt_id = v_bckdt_pen_id
and pdp2.prtt_enrt_rslt_id = v_new_pen_id
and pdp1.dpnt_person_id = pdp2.dpnt_person_id
and pdp1.ovrdn_flag = 'Y'
and v_effective_date between pdp1.effective_start_date
and pdp1.effective_end_date
and v_effective_date between pdp2.effective_start_date
and pdp2.effective_end_date;
select object_version_number
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
and effective_end_date = hr_api.g_eot;
select prv.*
,abr.input_value_id
,abr.element_type_id
from ben_prtt_rt_val prv,
ben_acty_base_rt_f abr
where prtt_rt_val_id = v_prtt_rt_val_id
and abr.acty_base_rt_id=prv.acty_base_rt_id
and abr.business_group_id = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
l_pgm_table.delete;
l_pl_table.delete;
l_enrt_table.delete;
l_bckt_csd_pil_enrt_table.delete;
p_datetrack_mode => hr_api.g_insert, --
p_suspend_flag => l_suspend_flag,
p_called_from_sspnd => 'N',
p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
p_prtt_rt_val_id1 => l_prtt_rt_val_id1,
p_prtt_rt_val_id2 => l_prtt_rt_val_id2,
p_prtt_rt_val_id3 => l_prtt_rt_val_id3,
p_prtt_rt_val_id4 => l_prtt_rt_val_id4,
p_prtt_rt_val_id5 => l_prtt_rt_val_id5,
p_prtt_rt_val_id6 => l_prtt_rt_val_id6,
p_prtt_rt_val_id7 => l_prtt_rt_val_id7,
p_prtt_rt_val_id8 => l_prtt_rt_val_id8,
p_prtt_rt_val_id9 => l_prtt_rt_val_id9,
p_prtt_rt_val_id10 => l_prtt_rt_val_id10,
-- 6131609 : reinstate DFF values
p_pen_attribute_category => l_bckdt_pen_rec.pen_attribute_category,
p_pen_attribute1 => l_bckdt_pen_rec.pen_attribute1,
p_pen_attribute2 => l_bckdt_pen_rec.pen_attribute2,
p_pen_attribute3 => l_bckdt_pen_rec.pen_attribute3,
p_pen_attribute4 => l_bckdt_pen_rec.pen_attribute4,
p_pen_attribute5 => l_bckdt_pen_rec.pen_attribute5,
p_pen_attribute6 => l_bckdt_pen_rec.pen_attribute6,
p_pen_attribute7 => l_bckdt_pen_rec.pen_attribute7,
p_pen_attribute8 => l_bckdt_pen_rec.pen_attribute8,
p_pen_attribute9 => l_bckdt_pen_rec.pen_attribute9,
p_pen_attribute10 => l_bckdt_pen_rec.pen_attribute10,
p_pen_attribute11 => l_bckdt_pen_rec.pen_attribute11,
p_pen_attribute12 => l_bckdt_pen_rec.pen_attribute12,
p_pen_attribute13 => l_bckdt_pen_rec.pen_attribute13,
p_pen_attribute14 => l_bckdt_pen_rec.pen_attribute14,
p_pen_attribute15 => l_bckdt_pen_rec.pen_attribute15,
p_pen_attribute16 => l_bckdt_pen_rec.pen_attribute16,
p_pen_attribute17 => l_bckdt_pen_rec.pen_attribute17,
p_pen_attribute18 => l_bckdt_pen_rec.pen_attribute18,
p_pen_attribute19 => l_bckdt_pen_rec.pen_attribute19,
p_pen_attribute20 => l_bckdt_pen_rec.pen_attribute20,
p_pen_attribute21 => l_bckdt_pen_rec.pen_attribute21,
p_pen_attribute22 => l_bckdt_pen_rec.pen_attribute22,
p_pen_attribute23 => l_bckdt_pen_rec.pen_attribute23,
p_pen_attribute24 => l_bckdt_pen_rec.pen_attribute24,
p_pen_attribute25 => l_bckdt_pen_rec.pen_attribute25,
p_pen_attribute26 => l_bckdt_pen_rec.pen_attribute26,
p_pen_attribute27 => l_bckdt_pen_rec.pen_attribute27,
p_pen_attribute28 => l_bckdt_pen_rec.pen_attribute28,
p_pen_attribute29 => l_bckdt_pen_rec.pen_attribute29,
p_pen_attribute30 => l_bckdt_pen_rec.pen_attribute30,
--
p_object_version_number => l_object_version_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_dpnt_actn_warning => l_dpnt_actn_warning,
p_bnf_actn_warning => l_bnf_actn_warning,
p_ctfn_actn_warning => l_ctfn_actn_warning);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => l_prtt_rt_val_table(i)
,p_person_id => p_person_id
,p_rt_val => l_rt_table(i).calc_val
,p_acty_ref_perd_cd => l_prv_ovn.acty_ref_perd_cd
,p_cmcd_rt_val => l_rt_table(i).cmcd_rt_val
,p_cmcd_ref_perd_cd => l_prv_ovn.cmcd_ref_perd_cd
,p_ann_rt_val => l_rt_table(i).ann_rt_val
,p_business_group_id => p_business_group_id
,p_object_version_number => l_prv_ovn.object_version_number
,p_effective_date => g_sys_date);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_prtt_enrt_rslt_id => l_enrt_table(i).prtt_enrt_rslt_id
,p_effective_start_date => l_esd_out
,p_effective_end_date => l_eed_out
,p_enrt_cvg_strt_dt => l_enrt_table(i).bckdt_enrt_cvg_strt_dt
,p_enrt_cvg_thru_dt => l_enrt_table(i).bckdt_enrt_cvg_thru_dt
,p_enrt_ovridn_flag => 'Y'
,p_object_version_number => l_ovn
,p_effective_date => l_enrt_table(i).g_sys_date
,p_datetrack_mode => hr_api.g_correction
,p_multi_row_validate => FALSE);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => l_rt_rec.new_prv_id
,p_person_id => p_person_id
,p_rt_strt_dt => l_rt_rec.rt_strt_dt
,p_rt_val => l_rt_rec.rt_val
,p_acty_ref_perd_cd => l_rt_rec.acty_ref_perd_cd
,p_cmcd_rt_val => l_rt_rec.cmcd_rt_val
,p_cmcd_ref_perd_cd => l_rt_rec.cmcd_ref_perd_cd
,p_ann_rt_val => l_rt_rec.ann_rt_val
,p_rt_ovridn_flag => l_rt_rec.rt_ovridn_flag
,p_rt_ovridn_thru_dt => l_rt_rec.rt_ovridn_thru_dt
,p_business_group_id => p_business_group_id
,p_object_version_number => l_rt_rec.new_prv_ovn
,p_effective_date => l_enrt_table(i).g_sys_date);
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
(p_elig_cvrd_dpnt_id => l_dpnt_rec.new_pdp_id
,p_effective_start_date => l_esd_out
,p_effective_end_date => l_eed_out
,p_cvg_strt_dt => l_dpnt_rec.cvg_strt_dt
,p_cvg_thru_dt => l_dpnt_rec.cvg_thru_dt
,p_ovrdn_flag => l_dpnt_rec.ovrdn_flag
,p_ovrdn_thru_dt => l_dpnt_rec.ovrdn_thru_dt
,p_object_version_number => l_dpnt_rec.new_pdp_ovn
,p_datetrack_mode => hr_api.g_correction
,p_effective_date => l_enrt_table(i).g_sys_date);
ben_Person_Life_Event_api.update_person_life_event
(p_per_in_ler_id => p_bckdt_per_in_ler_id
,p_per_in_ler_stat_cd => 'VOIDD'
,p_object_version_number => l_bckdt_pil_ovn
,p_effective_date => nvl(l_max_enrt_esd,p_effective_date)
,P_PROCD_DT => l_procd_dt -- outputs
,P_STRTD_DT => l_strtd_dt
,P_VOIDD_DT => l_voidd_dt );
l_bkd_pilepe_inst_table.delete ;
l_bckdt_pen_table.delete ;
/**** NO LONGER USED.... NEEDS TO BE DELETED COMPLETD
-- ----------------------------------------------------------------------------
-- |------------------------< p_lf_evt_clps_restore >-------------------------|
-- ----------------------------------------------------------------------------
--
procedure p_lf_evt_clps_restore_old(p_person_id in number
,p_business_group_id in number
,p_effective_date in date
,p_per_in_ler_id in number
,p_bckdt_per_in_ler_id in number
) is
--
cursor c_pil(p_per_in_ler_id in number) is
select pil.object_version_number
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id;
select null
from ben_le_clsn_n_rstr
where BKUP_TBL_TYP_CD = 'MULTIPLE_RATE'
and per_in_ler_id = p_bckdt_per_in_ler_id;
select pil.PRVS_STAT_CD, pil.object_version_number, pil.BCKT_PER_IN_LER_ID
from ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.business_group_id = p_business_group_id;
select enb.enrt_bnft_id,
enb.entr_val_at_enrt_flag,
enb.dflt_val,
enb.val,
enb.dflt_flag,
enb.cvg_mlt_cd --Bug 3315323
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = cp_elig_per_elctbl_chc_id
-- Bug 2526994 we need take the right one
-- and nvl(enb.mx_wo_ctfn_flag,'N') = 'N' ;
select ecr.enrt_rt_id,
ecr.dflt_val,
ecr.val,
ecr.entr_val_at_enrt_flag,
ecr.acty_base_rt_id
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = cp_elig_per_elctbl_chc_id
and ecr.business_group_id = p_business_group_id
and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.spcl_rt_enrt_rt_id is null
-- and ecr.prtt_rt_val_id is null
union
select ecr.enrt_rt_id,
ecr.dflt_val,
ecr.val,
ecr.entr_val_at_enrt_flag,
ecr.acty_base_rt_id
from ben_enrt_rt ecr
where ecr.enrt_bnft_id = cp_enrt_bnft_id
and ecr.business_group_id = p_business_group_id
and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.spcl_rt_enrt_rt_id is null;
select prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
and prv.per_in_ler_id = p_bckdt_per_in_ler_id
and prv.business_group_id = p_business_group_id
and prv.acty_base_rt_id = cv_acty_base_rt_id;
select pen.*, pil.lf_evt_ocrd_dt
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.per_in_ler_id = cv_per_in_ler_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pen.business_group_id = p_business_group_id
and pil.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = hr_api.g_eot
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and (pen.enrt_cvg_thru_dt is null or
pen.enrt_cvg_thru_dt = hr_api.g_eot
);
select prv.*
,abr.input_value_id
,abr.element_type_id
from ben_prtt_rt_val prv,
ben_acty_base_rt_f abr
where prtt_rt_val_id = v_prtt_rt_val_id
and abr.acty_base_rt_id=prv.acty_base_rt_id
and abr.business_group_id = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc epe,
ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and pen.per_in_ler_id = c_per_in_ler_id
and epe.per_in_ler_id=pen.per_in_ler_id
and epe.pl_id=pen.pl_id
and nvl(epe.pgm_id,-1) = nvl(pen.pgm_id,-1)
and nvl(epe.oipl_id,-1) = nvl(pen.oipl_id,-1);
select enb.val bnft_amt,
enb.enrt_bnft_id
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
--Bug 14059090
and ((l_susp_interim_same_epe = 'Y' and enb.ordr_num in (0))
or (l_susp_interim_same_epe = 'N' and enb.ordr_num in (-1,1)))
-- and enb.ordr_num in (-1,1)
order by enb.ordr_num;
select newchc.elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc oldchc,
ben_elig_per_elctbl_chc newchc
where oldchc.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and newchc.per_in_ler_id = p_per_in_ler_id
and newchc.pl_id = oldchc.pl_id
and nvl(oldchc.pgm_id,-1) = nvl(newchc.pgm_id,-1)
and nvl(oldchc.oipl_id,-1) = nvl(newchc.oipl_id,-1);
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id not in (p_per_in_ler_id)
and pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
and pil.lf_evt_ocrd_dt in (select max(lf_evt_ocrd_dt)
from ben_per_in_ler pil2,
ben_ler_f ler1
where pil2.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pil2.person_id = p_person_id
and pil2.ler_id = ler1.ler_id
and p_effective_date between
ler1.effective_start_date and ler1.effective_end_date
and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
per_in_ler_id = p_per_in_ler_id)
);
select prevepe.elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc prevepe,
ben_elig_per_elctbl_chc newepe,
ben_prtt_enrt_rslt_f prevpen
where newepe.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
and prevepe.per_in_ler_id = c_per_in_ler_id
and newepe.pl_id = prevepe.pl_id
and nvl(prevepe.pgm_id,-1) = nvl(newepe.pgm_id,-1)
and nvl(prevepe.oipl_id,-1) = nvl(newepe.oipl_id,-1)
and prevpen.prtt_enrt_rslt_id = prevepe.prtt_enrt_rslt_id
and prevpen.enrt_cvg_thru_dt = hr_api.g_eot
--and prevpen.effective_end_date = hr_api.g_eot
and prevpen.prtt_enrt_rslt_stat_cd is null;
select epe.elig_per_elctbl_chc_id,
pen.prtt_enrt_rslt_id,
pen.bnft_amt,
epe.object_version_number
from
ben_elig_per_elctbl_chc epe,
ben_prtt_enrt_rslt_f pen,
ben_enrt_bnft bnft
where pen.per_in_ler_id = c_prev_pil_id
and pen.pl_id = epe.pl_id
and pen.pgm_id = epe.pgm_id
and pen.pl_typ_id = epe.pl_typ_id
and nvl(pen.oipl_id,-1) = nvl(epe.oipl_id,-1)
and epe.elig_per_elctbl_chc_id = c_epe_id
and pen.prtt_enrt_rslt_stat_cd is null
and bnft.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
and exists
(select '1' from
ben_prtt_enrt_rslt_f pen1
where pen1.RPLCS_SSPNDD_RSLT_ID = pen.prtt_enrt_rslt_id
and pen1.per_in_ler_id = pen.per_in_ler_id
and pen1.prtt_enrt_rslt_stat_cd is null
and pen.pl_id = pen1.pl_id
and pen.pgm_id = pen1.pgm_id
and pen.pl_typ_id = pen1.pl_typ_id
and nvl(pen.oipl_id,-1) = nvl(pen1.oipl_id,-1));
l_bckt_csd_pil_enrt_table.delete;
ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
(p_validate => FALSE
,p_elig_per_elctbl_chc_id => l_epe_pen_rec.elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => l_prev_pil_epe_id1.prtt_enrt_rslt_id
,p_object_version_number => l_prev_pil_epe_id1.object_version_number
,p_effective_date => g_sys_date
);
p_datetrack_mode => hr_api.g_insert,
p_suspend_flag => l_suspend_flag,
p_called_from_sspnd => 'N',
p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
p_prtt_rt_val_id1 => l_prtt_rt_val_id1,
p_prtt_rt_val_id2 => l_prtt_rt_val_id2,
p_prtt_rt_val_id3 => l_prtt_rt_val_id3,
p_prtt_rt_val_id4 => l_prtt_rt_val_id4,
p_prtt_rt_val_id5 => l_prtt_rt_val_id5,
p_prtt_rt_val_id6 => l_prtt_rt_val_id6,
p_prtt_rt_val_id7 => l_prtt_rt_val_id7,
p_prtt_rt_val_id8 => l_prtt_rt_val_id8,
p_prtt_rt_val_id9 => l_prtt_rt_val_id9,
p_prtt_rt_val_id10 => l_prtt_rt_val_id10,
-- 6131609 : reinstate DFF values
p_pen_attribute_category => l_bckdt_pen_rec.pen_attribute_category,
p_pen_attribute1 => l_bckdt_pen_rec.pen_attribute1,
p_pen_attribute2 => l_bckdt_pen_rec.pen_attribute2,
p_pen_attribute3 => l_bckdt_pen_rec.pen_attribute3,
p_pen_attribute4 => l_bckdt_pen_rec.pen_attribute4,
p_pen_attribute5 => l_bckdt_pen_rec.pen_attribute5,
p_pen_attribute6 => l_bckdt_pen_rec.pen_attribute6,
p_pen_attribute7 => l_bckdt_pen_rec.pen_attribute7,
p_pen_attribute8 => l_bckdt_pen_rec.pen_attribute8,
p_pen_attribute9 => l_bckdt_pen_rec.pen_attribute9,
p_pen_attribute10 => l_bckdt_pen_rec.pen_attribute10,
p_pen_attribute11 => l_bckdt_pen_rec.pen_attribute11,
p_pen_attribute12 => l_bckdt_pen_rec.pen_attribute12,
p_pen_attribute13 => l_bckdt_pen_rec.pen_attribute13,
p_pen_attribute14 => l_bckdt_pen_rec.pen_attribute14,
p_pen_attribute15 => l_bckdt_pen_rec.pen_attribute15,
p_pen_attribute16 => l_bckdt_pen_rec.pen_attribute16,
p_pen_attribute17 => l_bckdt_pen_rec.pen_attribute17,
p_pen_attribute18 => l_bckdt_pen_rec.pen_attribute18,
p_pen_attribute19 => l_bckdt_pen_rec.pen_attribute19,
p_pen_attribute20 => l_bckdt_pen_rec.pen_attribute20,
p_pen_attribute21 => l_bckdt_pen_rec.pen_attribute21,
p_pen_attribute22 => l_bckdt_pen_rec.pen_attribute22,
p_pen_attribute23 => l_bckdt_pen_rec.pen_attribute23,
p_pen_attribute24 => l_bckdt_pen_rec.pen_attribute24,
p_pen_attribute25 => l_bckdt_pen_rec.pen_attribute25,
p_pen_attribute26 => l_bckdt_pen_rec.pen_attribute26,
p_pen_attribute27 => l_bckdt_pen_rec.pen_attribute27,
p_pen_attribute28 => l_bckdt_pen_rec.pen_attribute28,
p_pen_attribute29 => l_bckdt_pen_rec.pen_attribute29,
p_pen_attribute30 => l_bckdt_pen_rec.pen_attribute30,
--
p_object_version_number => l_object_version_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_dpnt_actn_warning => l_dpnt_actn_warning,
p_bnf_actn_warning => l_bnf_actn_warning,
p_ctfn_actn_warning => l_ctfn_actn_warning);
delete from ben_le_clsn_n_rstr
where bkup_tbl_id = l_bckdt_pen_rec.prtt_enrt_rslt_id
and per_in_ler_id = p_bckdt_per_in_ler_id
and bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and person_id = p_person_id;
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => l_prtt_rt_val_table(i)
,p_person_id => p_person_id
,p_rt_val => l_rt_table(i).calc_val
,p_acty_ref_perd_cd => l_prv_ovn.acty_ref_perd_cd
,p_cmcd_rt_val => l_rt_table(i).cmcd_rt_val
,p_cmcd_ref_perd_cd => l_prv_ovn.cmcd_ref_perd_cd
,p_ann_rt_val => l_rt_table(i).ann_rt_val
,p_business_group_id => p_business_group_id
,p_object_version_number => l_prv_ovn.object_version_number
,p_effective_date => g_sys_date);
select 'Y'
from ben_per_cm_f pcf,
ben_per_cm_trgr_f pctf,
ben_cm_trgr bct
where pcf.per_in_ler_id = p_bckdt_per_in_ler_id
and pcf.person_id = p_person_id
and pcf.per_cm_id = pctf.per_cm_id
and bct.CM_TRGR_ID = pctf.CM_TRGR_ID
and bct.PROC_CD = 'WEBENRT' ;
select pen_int.* from
ben_prtt_enrt_rslt_f pen_curr,
ben_prtt_enrt_rslt_f pen_int
where pen_curr.prtt_enrt_rslt_id = c_sus_pen_id
and pen_curr.prtt_enrt_rslt_stat_cd is NULL
and pen_curr.per_in_ler_id = p_per_in_ler_id
and pen_curr.SSPNDD_FLAG = 'Y'
and pen_curr.RPLCS_SSPNDD_RSLT_ID is not NULL
and pen_int.prtt_enrt_rslt_stat_cd is NULL
and pen_int.per_in_ler_id = p_per_in_ler_id
and pen_curr.RPLCS_SSPNDD_RSLT_ID = pen_int.prtt_enrt_rslt_id
and pen_curr.effective_end_date = hr_api.g_eot
and pen_int.effective_end_date = hr_api.g_eot
and exists
(select '1' from ben_prtt_enrt_rslt_f pen_bckdt,
ben_prtt_enrt_rslt_f pen_int_bckdt
where pen_bckdt.prtt_enrt_rslt_id = c_bckdt_prtt_enrt_rslt_id
and pen_bckdt.per_in_ler_id = c_bckdt_per_in_ler_id
and pen_bckdt.SSPNDD_FLAG = 'Y'
and pen_bckdt.RPLCS_SSPNDD_RSLT_ID is not NULL
and pen_int_bckdt.per_in_ler_id = c_bckdt_per_in_ler_id
and pen_bckdt.RPLCS_SSPNDD_RSLT_ID = pen_int_bckdt.prtt_enrt_rslt_id
and nvl(pen_int_bckdt.pgm_id,-1) = nvl(pen_int.pgm_id,-1)
and nvl(pen_int_bckdt.pl_id,-1) = nvl(pen_int.pl_id,-1)
and nvl(pen_int_bckdt.oipl_id,-1) = nvl(pen_int.oipl_id,-1)
union
select '1' from ben_le_clsn_n_rstr pen_bckdt,
ben_le_clsn_n_rstr pen_int_bckdt
where pen_bckdt.BKUP_TBL_ID = c_bckdt_prtt_enrt_rslt_id
and pen_bckdt.per_in_ler_id = c_bckdt_per_in_ler_id
and pen_bckdt.SSPNDD_FLAG = 'Y'
and pen_bckdt.RPLCS_SSPNDD_RSLT_ID is not NULL
and pen_int_bckdt.per_in_ler_id = c_bckdt_per_in_ler_id
and pen_bckdt.RPLCS_SSPNDD_RSLT_ID = pen_int_bckdt.BKUP_TBL_ID
and nvl(pen_int_bckdt.pgm_id,-1) = nvl(pen_int.pgm_id,-1)
and nvl(pen_int_bckdt.pl_id,-1) = nvl(pen_int.pl_id,-1)
and nvl(pen_int_bckdt.oipl_id,-1) = nvl(pen_int.oipl_id,-1)
and pen_bckdt.bkup_tbl_typ_cd like 'BEN_PRTT_ENRT_RSLT%'
and pen_int_bckdt.bkup_tbl_typ_cd like 'BEN_PRTT_ENRT_RSLT%');
select pel.pil_elctbl_chc_popl_id,
pel.pgm_id,
pel.pl_id,
pel.reinstate_cd,
pel.reinstate_ovrdn_cd,
pel.enrt_perd_strt_dt
from ben_pil_elctbl_chc_popl pel
where pel.per_in_ler_id = c_per_in_ler_id
and ((pel.pgm_id = c_pgm_id and
pel.pl_id is null ) or
(pel.pl_id = c_pl_id and
pel.pgm_id is null ));
select prv2.prtt_rt_val_id new_prv_id,
prv2.object_version_number new_prv_ovn,
prv1.*
from ben_prtt_rt_val prv1, --backed out
ben_prtt_rt_val prv2 --current
where prv1.prtt_enrt_rslt_id = v_bckdt_pen_id
and prv2.prtt_enrt_rslt_id = v_new_pen_id
and prv1.acty_base_rt_id = prv2.acty_base_rt_id
and prv1.rt_ovridn_flag = 'Y'
and nvl(prv1.rt_ovridn_thru_dt,hr_api.g_eot) >= prv2.rt_strt_dt -- Bug 4384574
and prv1.prtt_rt_val_stat_cd = 'BCKDT'
and prv2.prtt_rt_val_stat_cd is null
and prv2.per_in_ler_id = p_per_in_ler_id
and prv1.per_in_ler_id = p_bckdt_per_in_ler_id ;
select pdp2.elig_cvrd_dpnt_id new_pdp_id,
pdp2.object_version_number new_pdp_ovn,
pdp1.*
from ben_elig_cvrd_dpnt_f pdp1,
ben_elig_cvrd_dpnt_f pdp2
where pdp1.prtt_enrt_rslt_id = v_bckdt_pen_id
and pdp2.prtt_enrt_rslt_id = v_new_pen_id
and pdp1.dpnt_person_id = pdp2.dpnt_person_id
and pdp1.ovrdn_flag = 'Y'
and v_effective_date between pdp1.effective_start_date
and pdp1.effective_end_date
and v_effective_date between pdp2.effective_start_date
and pdp2.effective_end_date;
select object_version_number
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
and effective_end_date = hr_api.g_eot;
select ecr.elig_per_elctbl_chc_id
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and ecr.acty_base_rt_id = v_acty_base_rt_id ;
select ecr.enrt_bnft_id
from ben_enrt_rt ecr,
ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
and enb.ordr_num > 0 --9999 Need to check this
and ecr.enrt_bnft_id = enb.enrt_bnft_id
and ecr.acty_base_rt_id = v_acty_base_rt_id ;
select pen.prtt_enrt_rslt_id,pen.pgm_id,pen.pl_id
from ben_prtt_enrt_rslt_f pen,
ben_pl_f pln
where pen.person_id = c_person_id
and pen.per_in_ler_id = c_per_in_ler_id
and pen.pl_id = pln.pl_id
and pln.invk_flx_cr_pl_flag = 'Y'
and c_effective_date between pen.effective_start_date
and pen.effective_end_date
and c_effective_date between pln.effective_start_date
and pln.effective_end_date;
select decode(pgm_typ_cd,'COBRAFLX','Y',
'FLEX','Y',
'FPC','Y','N') pgm_typ_cd
from ben_pgm_f pgm
where pgm.pgm_id = c_pgm_id
and c_effective_date between pgm.effective_start_date
and pgm.effective_end_date ;
select bpp.bnft_prvdr_pool_id
from
ben_aplcn_to_bnft_pool_f abp,
ben_bnft_prvdr_pool_f bpp
where
abp.acty_base_rt_id = c_acty_base_rt_id
and abp.bnft_prvdr_pool_id = bpp.bnft_prvdr_pool_id
and bpp.pgm_id = c_pgm_id
and c_effective_date between abp.effective_start_date
and abp.effective_end_date
and c_effective_date between bpp.effective_start_date
and bpp.effective_end_date ;
select elig_per_elctbl_chc_id
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = c_per_in_ler_id
and epe.pl_id = c_pl_id
and epe.pgm_id = c_pgm_id ;
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_prtt_enrt_rslt_id => p_enrt_table(i).prtt_enrt_rslt_id
,p_effective_start_date => l_esd_out
,p_effective_end_date => l_eed_out
,p_enrt_cvg_strt_dt => p_enrt_table(i).bckdt_enrt_cvg_strt_dt
,p_enrt_cvg_thru_dt => p_enrt_table(i).bckdt_enrt_cvg_thru_dt
,p_enrt_ovrid_thru_dt => p_enrt_table(i).enrt_ovrid_thru_dt
,p_enrt_ovrid_rsn_cd => p_enrt_table(i).enrt_ovrid_rsn_cd
,p_enrt_ovridn_flag => 'Y'
,p_object_version_number => l_ovn
,p_effective_date => p_enrt_table(i).g_sys_date
,p_datetrack_mode => hr_api.g_correction
,p_multi_row_validate => FALSE);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => l_rt_rec.new_prv_id
,p_person_id => p_person_id
,p_rt_strt_dt => l_rt_rec.rt_strt_dt
,p_rt_val => l_rt_rec.rt_val
,p_acty_ref_perd_cd => l_rt_rec.acty_ref_perd_cd
,p_cmcd_rt_val => l_rt_rec.cmcd_rt_val
,p_cmcd_ref_perd_cd => l_rt_rec.cmcd_ref_perd_cd
,p_ann_rt_val => l_rt_rec.ann_rt_val
,p_rt_ovridn_flag => l_rt_rec.rt_ovridn_flag
,p_rt_ovridn_thru_dt => l_rt_rec.rt_ovridn_thru_dt
,p_business_group_id => p_business_group_id
,p_object_version_number => l_rt_rec.new_prv_ovn
,p_effective_date => p_enrt_table(i).g_sys_date);
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
(p_elig_cvrd_dpnt_id => l_dpnt_rec.new_pdp_id
,p_effective_start_date => l_esd_out
,p_effective_end_date => l_eed_out
,p_cvg_strt_dt => l_dpnt_rec.cvg_strt_dt
,p_cvg_thru_dt => l_dpnt_rec.cvg_thru_dt
,p_ovrdn_flag => l_dpnt_rec.ovrdn_flag
,p_ovrdn_thru_dt => l_dpnt_rec.ovrdn_thru_dt
,p_object_version_number => l_dpnt_rec.new_pdp_ovn
,p_datetrack_mode => hr_api.g_correction
,p_effective_date => p_enrt_table(i).g_sys_date);
select pel.pil_elctbl_chc_popl_id,
pel.pgm_id,
pel.pl_id,
pel.reinstate_cd,
pel.reinstate_ovrdn_cd,
pel.enrt_perd_strt_dt
from ben_pil_elctbl_chc_popl pel
where pel.per_in_ler_id = p_per_in_ler_id;
select null
from ben_le_clsn_n_rstr
where BKUP_TBL_TYP_CD = 'MULTIPLE_RATE'
and per_in_ler_id = p_bckdt_per_in_ler_id;
select ler.name, pil.PRVS_STAT_CD, pil.object_version_number, pil.BCKT_PER_IN_LER_ID
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and ler.ler_id = pil.ler_id
and p_effective_date between ler.effective_start_date
and ler.effective_end_date ;
select pl_typ_id from
ben_pl_f
where pl_id = c_pl_id
and p_effective_date between effective_start_date
and effective_end_date ;
select 'Y' from
ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id = c_per_in_ler_id
and nvl(pen.pgm_id,-1) = nvl(c_pgm_id,-1)
and pen.pl_typ_id = c_pl_typ_id
and pen.enrt_mthd_cd = 'E'
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.enrt_cvg_thru_dt >= pen.enrt_cvg_strt_dt ;
select 'Y'
from ben_prtt_enrt_rslt_f pen,
ben_ler_f ler
where pen.per_in_ler_id not in (p_per_in_ler_id,p_bckdt_per_in_ler_id)
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot
and pen.ler_id = ler.ler_id
and p_effective_date between ler.effective_start_date and
ler.effective_end_date
and ler.typ_cd not in ('IREC','SCHEDDU', 'COMP', 'GSP', 'ABS')
and pen.per_in_ler_id= l_prev_pil_id
and pen.enrt_mthd_cd = 'E'
/* Bug 12688164 : To check whether elections are made for intervening LE, enrollment should
not be either suspended or interim*/
and nvl(pen.sspndd_flag,'N') = 'N'
and not exists
(select '1' from ben_prtt_enrt_rslt_f pen1
where pen1.RPLCS_SSPNDD_RSLT_ID = pen.prtt_enrt_rslt_id
and pen1.per_in_ler_id = pen.per_in_ler_id)
and exists
(select 'Y' from ben_elig_per_elctbl_chc epe1
where epe1.elctbl_flag = 'Y'
and epe1.per_in_ler_id = l_prev_pil_id)
and rownum = 1 ;
select bckt_per_in_ler_id
from ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id;
select 'Y'
from ben_per_in_ler pil
where pil.per_in_ler_id = c_pil_id
and pil.per_in_ler_stat_cd in('BCKDT', 'VOIDD');
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id not in (p_per_in_ler_id,p_bckdt_per_in_ler_id)
and pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
and pil.lf_evt_ocrd_dt in (select lf_evt_ocrd_dt
from ben_per_in_ler pil2,
ben_ler_f ler1
where pil2.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pil2.person_id = p_person_id
and pil2.ler_id = ler1.ler_id
and p_effective_date between
ler1.effective_start_date and ler1.effective_end_date
and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil2.lf_evt_ocrd_dt > (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
per_in_ler_id = l_int_pil_id)
and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
per_in_ler_id = p_bckdt_per_in_ler_id)
)
order by pil.lf_evt_ocrd_dt desc;
select le.ler_id
from ben_ler_f le,ben_per_in_ler pil
where pil.ler_id=le.ler_id
and p_effective_date between le.effective_start_date and le.effective_end_date
and pil.per_in_ler_id = p_bckdt_per_in_ler_id;
select '1' from dual where exists
(select '1' from ben_pil_elctbl_chc_popl pel
where pel.per_in_ler_id = p_per_in_ler_id
and pel.reopen_le_on_reprocess = 'Y');
SELECT pen_inner.bkup_tbl_id,
pen_inner.effective_start_date,
pen_inner.effective_end_date,
pen_inner.enrt_cvg_strt_dt,
pen_inner.enrt_cvg_thru_dt,
pen_inner.object_version_number
FROM ben_le_clsn_n_rstr pen_inner,
ben_per_in_ler pil_inner
WHERE pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
AND pil_inner.person_id = p_person_id
AND pil_inner.business_group_id = p_business_group_id
AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
AND pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
AND ( (pen_inner.enrt_cvg_thru_dt IS NOT NULL and pen_inner.enrt_cvg_thru_dt <> hr_api.g_eot )
and pen_inner.effective_end_date = hr_api.g_eot
)
AND pen_inner.comp_lvl_cd NOT IN('PLANFC','PLANIMP');
select 'x'
from ben_ler_f ler, ben_per_in_ler pil
where ler.ler_id = pil.ler_id
and ler.business_group_id = pil.business_group_id
and pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and ler.typ_cd = 'SCHEDDU';
select * from ben_pil_elctbl_chc_popl pel
where per_in_ler_id = c_per_in_ler_id
--Bug 13940072 added pgm_id and pl_id check to fetch correct record
and (
(p_pl_id is null and pgm_id = p_pgm_id) or
(p_pgm_id is null and pl_id = p_pl_id) or (p_pl_id is NULL and p_pgm_id is NULL)
) ;
l_pgm_table.delete;
l_pl_table.delete;
l_enrt_table.delete;
l_invalid_pen_table.delete;
l_invalid_epe_table.delete;
l_valid_pen_table.delete;
l_valid_epe_table.delete;
g_reinstated_defaults.delete;
ben_Pil_Elctbl_chc_Popl_api.update_Pil_Elctbl_chc_Popl
(p_validate => p_validate
,p_pil_elctbl_chc_popl_id => l_popl_rec.PIL_ELCTBL_CHC_POPL_ID
,p_elcns_made_dt => l_ele_date
,p_object_version_number => l_popl_rec.object_version_number
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
ben_prtt_enrt_result_api.delete_enrollment(
p_validate => false,
p_per_in_ler_id => p_per_in_ler_id,
p_prtt_enrt_rslt_id => l_en_dtd_pen_bckdt_pil.bkup_tbl_id,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_object_version_number => l_en_dtd_pen_bckdt_pil.object_version_number,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date,
p_datetrack_mode => 'DELETE',
p_multi_row_validate => false);
ben_Pil_Elctbl_chc_Popl_api.update_Pil_Elctbl_chc_Popl
(p_validate => p_validate
,p_pil_elctbl_chc_popl_id => l_popl_rec.PIL_ELCTBL_CHC_POPL_ID
,p_elcns_made_dt => l_ele_date
,p_object_version_number => l_popl_rec.object_version_number
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
ben_Pil_Elctbl_chc_Popl_api.update_Pil_Elctbl_chc_Popl
(p_validate => p_validate
,p_pil_elctbl_chc_popl_id => l_popl_rec.PIL_ELCTBL_CHC_POPL_ID
,p_elcns_made_dt => l_ele_date
,p_object_version_number => l_popl_rec.object_version_number
,p_effective_date => p_effective_date
,p_business_group_id => p_business_group_id
);
hr_utility.set_location('Calling Void update_person_life_event' ,700);
ben_Person_Life_Event_api.update_person_life_event
(p_per_in_ler_id => p_bckdt_per_in_ler_id
,p_per_in_ler_stat_cd => 'VOIDD'
,p_object_version_number => l_bckdt_pil.object_version_number
,p_effective_date => nvl(l_max_enrt_esd,p_effective_date)
,P_PROCD_DT => l_procd_dt -- outputs
,P_STRTD_DT => l_strtd_dt
,P_VOIDD_DT => l_voidd_dt );
hr_utility.set_location('Done update_person_life_event',710);
hr_utility.set_location('Calling Void update_person_life_event' ,700);
ben_Person_Life_Event_api.update_person_life_event
(p_per_in_ler_id => p_bckdt_per_in_ler_id
,p_per_in_ler_stat_cd => 'VOIDD'
,p_object_version_number => l_bckdt_pil.object_version_number
,p_effective_date => nvl(l_max_enrt_esd,p_effective_date)
,P_PROCD_DT => l_procd_dt -- outputs
,P_STRTD_DT => l_strtd_dt
,P_VOIDD_DT => l_voidd_dt );
procedure update_ptnl_per_for_ler(p_ptnl_ler_for_per_id in number
,p_business_group_id in number
,p_ptnl_ler_for_per_stat_cd in varchar2
,p_effective_date in date) is
--
l_proc varchar2(72) := g_package||'.update_ptnl_per_for_ler';
select *
from ben_ptnl_ler_for_per ptn
where ptn.ptnl_ler_for_per_id = p_ptnl_ler_for_per_id
and ptn.business_group_id = p_business_group_id;
ben_ptnl_ler_for_per_api.update_ptnl_ler_for_per
(p_validate => false
,p_ptnl_ler_for_per_id => l_ptnl_rec.ptnl_ler_for_per_id
,p_ptnl_ler_for_per_stat_cd => nvl(p_ptnl_ler_for_per_stat_cd,'VOIDD')
,p_object_version_number => l_ptnl_rec.object_version_number
,p_effective_date => p_effective_date
,p_mnl_dt => l_mnl_dt
,p_program_application_id => null
,p_program_id => null
,p_request_id => null
,p_program_update_date => sysdate
,p_voidd_dt => p_effective_date);
end update_ptnl_per_for_ler;
select pil.PER_IN_LER_STAT_CD, pil.object_version_number,
pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.business_group_id = p_business_group_id;
select
pen.EFFECTIVE_END_DATE,
pen.ASSIGNMENT_ID,
pen.BNFT_AMT,
pen.BNFT_NNMNTRY_UOM,
pen.BNFT_ORDR_NUM,
pen.BNFT_TYP_CD,
pen.BUSINESS_GROUP_ID,
pen.COMP_LVL_CD,
pen.CREATED_BY,
pen.CREATION_DATE,
pen.EFFECTIVE_START_DATE,
pen.ENRT_CVG_STRT_DT,
pen.ENRT_CVG_THRU_DT,
pen.ENRT_MTHD_CD,
pen.ENRT_OVRIDN_FLAG,
pen.ENRT_OVRID_RSN_CD,
pen.ENRT_OVRID_THRU_DT,
pen.ERLST_DEENRT_DT,
pen.LAST_UPDATED_BY,
pen.LAST_UPDATE_DATE,
pen.LAST_UPDATE_LOGIN,
pen.LER_ID,
pen.NO_LNGR_ELIG_FLAG,
pen.OBJECT_VERSION_NUMBER,
pen.OIPL_ID,
pen.OIPL_ORDR_NUM,
pen.ORGNL_ENRT_DT,
pen.LCR_ATTRIBUTE1,
pen.LCR_ATTRIBUTE10,
pen.LCR_ATTRIBUTE11,
pen.LCR_ATTRIBUTE12,
pen.LCR_ATTRIBUTE13,
pen.LCR_ATTRIBUTE14,
pen.LCR_ATTRIBUTE15,
pen.LCR_ATTRIBUTE16,
pen.LCR_ATTRIBUTE17,
pen.LCR_ATTRIBUTE18,
pen.LCR_ATTRIBUTE19,
pen.LCR_ATTRIBUTE2,
pen.LCR_ATTRIBUTE20,
pen.LCR_ATTRIBUTE21,
pen.LCR_ATTRIBUTE22,
pen.LCR_ATTRIBUTE23,
pen.LCR_ATTRIBUTE24,
pen.LCR_ATTRIBUTE25,
pen.LCR_ATTRIBUTE26,
pen.LCR_ATTRIBUTE27,
pen.LCR_ATTRIBUTE28,
pen.LCR_ATTRIBUTE29,
pen.LCR_ATTRIBUTE3,
pen.LCR_ATTRIBUTE30,
pen.LCR_ATTRIBUTE4,
pen.LCR_ATTRIBUTE5,
pen.LCR_ATTRIBUTE6,
pen.LCR_ATTRIBUTE7,
pen.LCR_ATTRIBUTE8,
pen.LCR_ATTRIBUTE9,
pen.LCR_ATTRIBUTE_CATEGORY,
pen.PERSON_ID,
pen.PER_IN_LER_ID,
pen.PGM_ID,
pen.PLIP_ORDR_NUM,
pen.PL_ID,
pen.PL_ORDR_NUM,
pen.PL_TYP_ID,
pen.PROGRAM_APPLICATION_ID,
pen.PROGRAM_ID,
pen.PROGRAM_UPDATE_DATE,
pen.bkup_tbl_id PRTT_ENRT_RSLT_ID,
pen.PRTT_ENRT_RSLT_STAT_CD,
pen.PRTT_IS_CVRD_FLAG,
pen.PTIP_ID,
pen.PTIP_ORDR_NUM,
pen.REQUEST_ID,
pen.RPLCS_SSPNDD_RSLT_ID,
pen.SSPNDD_FLAG,
pen.UOM
from ben_le_clsn_n_rstr pen,
ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
AND pil.per_in_ler_id = pen.per_in_ler_id
and pen.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and ((pen.enrt_cvg_thru_dt is null or
pen.enrt_cvg_thru_dt = hr_api.g_eot) and
--bug#2604375 - added to control updated result rows for the same per_in_ler
pen.effective_end_date = hr_api.g_eot
)
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and pen.bkup_tbl_id not in (
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_prtt_enrt_rslt_f pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
and pil_inner.person_id = p_person_id
and pil_inner.business_group_id = p_business_group_id
and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
union
select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
from ben_le_clsn_n_rstr pen_inner,
ben_per_in_ler pil_inner
where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
and pil_inner.person_id = p_person_id
and pil_inner.business_group_id = p_business_group_id
AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
and (pen_inner.enrt_cvg_thru_dt is null or
pen_inner.enrt_cvg_thru_dt = hr_api.g_eot
)
and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
)
order by 1; -- pen.effective_end_date; -- Low to High
select epe.*,
pel.enrt_typ_cycl_cd,
pel.enrt_perd_end_dt,
pel.enrt_perd_strt_dt,
to_date('31-12-4712','DD-MM-YYYY') enrt_cvg_end_dt,
pel.dflt_enrt_dt
from ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil,
ben_pil_elctbl_chc_popl pel
where epe.per_in_ler_id = cp_per_in_ler_id
and epe.business_group_id = p_business_group_id
and epe.pl_id = cp_pl_id
and nvl(epe.pgm_id, -1) = nvl(cp_pgm_id, -1)
and nvl(epe.oipl_id, -1) = nvl(cp_oipl_id, -1)
and pil.business_group_id = p_business_group_id
and pel.business_group_id = epe.business_group_id
and pil.person_id = p_person_id
and epe.per_in_ler_id = pil.per_in_ler_id
and pel.per_in_ler_id = epe.per_in_ler_id
and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id;
select enb.enrt_bnft_id,
enb.entr_val_at_enrt_flag,
enb.dflt_val,
enb.val,
enb.dflt_flag,
enb.object_version_number,
enb.prtt_enrt_rslt_id,
enb.cvg_mlt_cd --Bug 3315323
from ben_enrt_bnft enb
where enb.elig_per_elctbl_chc_id = cp_elig_per_elctbl_chc_id
-- Bug 2526994 we need take the right one
-- and nvl(enb.mx_wo_ctfn_flag,'N') = 'N' ;
select ecr.enrt_rt_id,
ecr.dflt_val,
ecr.val,
ecr.entr_val_at_enrt_flag,
ecr.acty_base_rt_id
from ben_enrt_rt ecr
where ecr.elig_per_elctbl_chc_id = cp_elig_per_elctbl_chc_id
and ecr.business_group_id = p_business_group_id
and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.spcl_rt_enrt_rt_id is null
-- and ecr.prtt_rt_val_id is null
union
select ecr.enrt_rt_id,
ecr.dflt_val,
ecr.val,
ecr.entr_val_at_enrt_flag,
ecr.acty_base_rt_id
from ben_enrt_rt ecr
where ecr.enrt_bnft_id = cp_enrt_bnft_id
and ecr.business_group_id = p_business_group_id
and ecr.entr_val_at_enrt_flag = 'Y'
and ecr.spcl_rt_enrt_rt_id is null;
select prv.*
from ben_prtt_rt_val prv
where prv.prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id
and prv.per_in_ler_id = p_bckdt_per_in_ler_id
and prv.business_group_id = p_business_group_id
and prv.acty_base_rt_id = cv_acty_base_rt_id;
select pen.*, pil.lf_evt_ocrd_dt
from ben_prtt_enrt_rslt_f pen,
ben_per_in_ler pil
where pen.per_in_ler_id = cv_per_in_ler_id
and pen.per_in_ler_id = pil.per_in_ler_id
and pen.business_group_id = p_business_group_id
and pil.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = hr_api.g_eot
and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
and (pen.enrt_cvg_thru_dt is null or
pen.enrt_cvg_thru_dt = hr_api.g_eot
);
select prv2.prtt_rt_val_id new_prv_id,
prv2.object_version_number new_prv_ovn,
prv1.*
from ben_prtt_rt_val prv1, ben_prtt_rt_val prv2
where prv1.prtt_enrt_rslt_id = v_bckdt_pen_id
and prv2.prtt_enrt_rslt_id = v_new_pen_id
and prv1.acty_base_rt_id = prv2.acty_base_rt_id
and prv1.rt_ovridn_flag = 'Y'
and prv1.rt_end_dt <> hr_api.g_eot
and prv1.rt_ovridn_thru_dt >= prv2.rt_strt_dt
-- and prv1.prtt_rt_val_stat_cd is null
and prv2.prtt_rt_val_stat_cd is null
and prv2.per_in_ler_id = p_per_in_ler_id ;
select pdp2.elig_cvrd_dpnt_id new_pdp_id,
pdp2.object_version_number new_pdp_ovn,
pdp1.*
from ben_elig_cvrd_dpnt_f pdp1,
ben_elig_cvrd_dpnt_f pdp2
where pdp1.prtt_enrt_rslt_id = v_bckdt_pen_id
and pdp2.prtt_enrt_rslt_id = v_new_pen_id
and pdp1.dpnt_person_id = pdp2.dpnt_person_id
and pdp1.ovrdn_flag = 'Y'
and v_effective_date between pdp1.effective_start_date
and pdp1.effective_end_date
and v_effective_date between pdp2.effective_start_date
and pdp2.effective_end_date;
select object_version_number
from ben_prtt_enrt_rslt_f
where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
and effective_end_date = hr_api.g_eot;
select prv.*
,abr.input_value_id
,abr.element_type_id
from ben_prtt_rt_val prv,
ben_acty_base_rt_f abr
where prtt_rt_val_id = v_prtt_rt_val_id
and abr.acty_base_rt_id=prv.acty_base_rt_id
and abr.business_group_id = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select 'Y'
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_person_id
and pen.pl_id = p_pl_id
and nvl(pen.oipl_id,-9999) = nvl(p_oipl_id,-9999) --5287988
and pen.per_in_ler_id = p_per_in_ler_id -- 8626297
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt = hr_api.g_eot
and pen.effective_end_date = hr_api.g_eot;
select elig_per_elctbl_chc_id from ben_elig_per_elctbl_chc epe,
ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
and epe.per_in_ler_id = c_per_in_ler_id
and epe.pl_id = pen.pl_id
and epe.pl_typ_id = pen.pl_typ_id
and nvl(epe.pgm_id,-1) = nvl(pen.pgm_id,-1)
and nvl(epe.oipl_id,-1) = nvl(pen.oipl_id,-1);
l_pgm_table.delete;
l_pl_table.delete;
l_enrt_table.delete;
l_bckt_csd_pil_enrt_table.delete;
ben_ELIG_PER_ELC_CHC_api.update_ELIG_PER_ELC_CHC
(p_validate => FALSE
,p_elig_per_elctbl_chc_id => l_epe_pen_rec.elig_per_elctbl_chc_id
,p_prtt_enrt_rslt_id => NULL
,p_object_version_number => l_object_version_number
,p_effective_date => p_effective_date
);
ben_enrt_bnft_api.update_enrt_bnft
(p_enrt_bnft_id => l_bnft_rec.enrt_bnft_id
,p_effective_date => p_effective_date
,p_object_version_number => l_object_version_number
,p_business_group_id => p_business_group_id
,p_prtt_enrt_rslt_id => NULL
);
p_datetrack_mode => hr_api.g_insert, -- 99999 l_datetrack_mode,
p_suspend_flag => l_suspend_flag,
p_called_from_sspnd => 'N',
p_prtt_enrt_interim_id => l_prtt_enrt_interim_id,
p_prtt_rt_val_id1 => l_prtt_rt_val_id1,
p_prtt_rt_val_id2 => l_prtt_rt_val_id2,
p_prtt_rt_val_id3 => l_prtt_rt_val_id3,
p_prtt_rt_val_id4 => l_prtt_rt_val_id4,
p_prtt_rt_val_id5 => l_prtt_rt_val_id5,
p_prtt_rt_val_id6 => l_prtt_rt_val_id6,
p_prtt_rt_val_id7 => l_prtt_rt_val_id7,
p_prtt_rt_val_id8 => l_prtt_rt_val_id8,
p_prtt_rt_val_id9 => l_prtt_rt_val_id9,
p_prtt_rt_val_id10 => l_prtt_rt_val_id10,
-- 6131609 : reinstate DFF values
p_pen_attribute_category => l_bckdt_pen_rec.lcr_attribute_category,
p_pen_attribute1 => l_bckdt_pen_rec.lcr_attribute1,
p_pen_attribute2 => l_bckdt_pen_rec.lcr_attribute2,
p_pen_attribute3 => l_bckdt_pen_rec.lcr_attribute3,
p_pen_attribute4 => l_bckdt_pen_rec.lcr_attribute4,
p_pen_attribute5 => l_bckdt_pen_rec.lcr_attribute5,
p_pen_attribute6 => l_bckdt_pen_rec.lcr_attribute6,
p_pen_attribute7 => l_bckdt_pen_rec.lcr_attribute7,
p_pen_attribute8 => l_bckdt_pen_rec.lcr_attribute8,
p_pen_attribute9 => l_bckdt_pen_rec.lcr_attribute9,
p_pen_attribute10 => l_bckdt_pen_rec.lcr_attribute10,
p_pen_attribute11 => l_bckdt_pen_rec.lcr_attribute11,
p_pen_attribute12 => l_bckdt_pen_rec.lcr_attribute12,
p_pen_attribute13 => l_bckdt_pen_rec.lcr_attribute13,
p_pen_attribute14 => l_bckdt_pen_rec.lcr_attribute14,
p_pen_attribute15 => l_bckdt_pen_rec.lcr_attribute15,
p_pen_attribute16 => l_bckdt_pen_rec.lcr_attribute16,
p_pen_attribute17 => l_bckdt_pen_rec.lcr_attribute17,
p_pen_attribute18 => l_bckdt_pen_rec.lcr_attribute18,
p_pen_attribute19 => l_bckdt_pen_rec.lcr_attribute19,
p_pen_attribute20 => l_bckdt_pen_rec.lcr_attribute20,
p_pen_attribute21 => l_bckdt_pen_rec.lcr_attribute21,
p_pen_attribute22 => l_bckdt_pen_rec.lcr_attribute22,
p_pen_attribute23 => l_bckdt_pen_rec.lcr_attribute23,
p_pen_attribute24 => l_bckdt_pen_rec.lcr_attribute24,
p_pen_attribute25 => l_bckdt_pen_rec.lcr_attribute25,
p_pen_attribute26 => l_bckdt_pen_rec.lcr_attribute26,
p_pen_attribute27 => l_bckdt_pen_rec.lcr_attribute27,
p_pen_attribute28 => l_bckdt_pen_rec.lcr_attribute28,
p_pen_attribute29 => l_bckdt_pen_rec.lcr_attribute29,
p_pen_attribute30 => l_bckdt_pen_rec.lcr_attribute30,
--
p_object_version_number => l_object_version_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_dpnt_actn_warning => l_dpnt_actn_warning,
p_bnf_actn_warning => l_bnf_actn_warning,
p_ctfn_actn_warning => l_ctfn_actn_warning);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => l_prtt_rt_val_table(i)
,p_person_id => p_person_id
,p_rt_val => l_rt_table(i).calc_val
,p_acty_ref_perd_cd => l_prv_ovn.acty_ref_perd_cd
,p_cmcd_rt_val => l_rt_table(i).cmcd_rt_val
,p_cmcd_ref_perd_cd => l_prv_ovn.cmcd_ref_perd_cd
,p_ann_rt_val => l_rt_table(i).ann_rt_val
,p_business_group_id => p_business_group_id
,p_object_version_number => l_prv_ovn.object_version_number
,p_effective_date => g_sys_date);
ben_prtt_enrt_result_api.update_prtt_enrt_result
(p_prtt_enrt_rslt_id => l_enrt_table(i).prtt_enrt_rslt_id
,p_effective_start_date => l_esd_out
,p_effective_end_date => l_eed_out
,p_enrt_cvg_strt_dt => l_enrt_table(i).bckdt_enrt_cvg_strt_dt
,p_enrt_cvg_thru_dt => l_enrt_table(i).bckdt_enrt_cvg_thru_dt
,p_enrt_ovridn_flag => 'Y'
,p_object_version_number => l_ovn
,p_effective_date => l_enrt_table(i).g_sys_date
,p_datetrack_mode => hr_api.g_correction
,p_multi_row_validate => FALSE);
ben_prtt_rt_val_api.update_prtt_rt_val
(p_prtt_rt_val_id => l_rt_rec.new_prv_id
,p_person_id => p_person_id
,p_rt_strt_dt => l_rt_rec.rt_strt_dt
,p_rt_val => l_rt_rec.rt_val
,p_acty_ref_perd_cd => l_rt_rec.acty_ref_perd_cd
,p_cmcd_rt_val => l_rt_rec.cmcd_rt_val
,p_cmcd_ref_perd_cd => l_rt_rec.cmcd_ref_perd_cd
,p_ann_rt_val => l_rt_rec.ann_rt_val
,p_rt_ovridn_flag => l_rt_rec.rt_ovridn_flag
,p_rt_ovridn_thru_dt => l_rt_rec.rt_ovridn_thru_dt
,p_business_group_id => p_business_group_id
,p_object_version_number => l_rt_rec.new_prv_ovn
,p_effective_date => l_enrt_table(i).g_sys_date);
ben_elig_cvrd_dpnt_api.update_elig_cvrd_dpnt
(p_elig_cvrd_dpnt_id => l_dpnt_rec.new_pdp_id
,p_effective_start_date => l_esd_out
,p_effective_end_date => l_eed_out
,p_cvg_strt_dt => l_dpnt_rec.cvg_strt_dt
,p_cvg_thru_dt => l_dpnt_rec.cvg_thru_dt
,p_ovrdn_flag => l_dpnt_rec.ovrdn_flag
,p_ovrdn_thru_dt => l_dpnt_rec.ovrdn_thru_dt
,p_object_version_number => l_dpnt_rec.new_pdp_ovn
,p_datetrack_mode => hr_api.g_correction
,p_effective_date => l_enrt_table(i).g_sys_date);
electability and no enrollments results then previous LE status will not be updated to 'STRTD' status.
So there is no need to force close the previous LE*/
if ( ben_back_out_life_event.g_no_reopen_flag = 'N') then
ben_close_enrollment.close_single_enrollment
(p_per_in_ler_id => p_per_in_ler_id
,p_effective_date => nvl(l_max_enrt_esd,p_effective_date)
,p_business_group_id => p_business_group_id
,p_close_cd => 'FORCE'
,p_validate => FALSE
,p_close_uneai_flag => NULL
,p_uneai_effective_date => NULL);
select pgm_id,pl_typ_id from
ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id;
select pil1.bckt_per_in_ler_id
from ben_per_in_ler pil,ben_per_in_ler pil1
where pil.per_in_ler_id = p_bckdt_per_in_ler_id
and pil.ptnl_ler_for_per_id = pil1.ptnl_ler_for_per_id
and pil1.bckt_per_in_ler_id is not null
order by pil.lf_evt_ocrd_dt asc;
select pil.bckt_per_in_ler_id
from ben_per_in_ler pil
where pil.per_in_ler_id = p_bckdt_per_in_ler_id;
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id not in (p_per_in_ler_id,p_bckdt_per_in_ler_id)
and pil.person_id = p_person_id
and pil.ler_id = ler.ler_id
and p_effective_date between
ler.effective_start_date and ler.effective_end_date
and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
and pil.lf_evt_ocrd_dt in (select lf_evt_ocrd_dt
from ben_per_in_ler pil2,
ben_ler_f ler1
where pil2.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
and pil2.person_id = p_person_id
and pil2.ler_id = ler1.ler_id
and p_effective_date between
ler1.effective_start_date and ler1.effective_end_date
and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
and pil2.lf_evt_ocrd_dt > (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
per_in_ler_id = l_intv_pil_id)
and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
per_in_ler_id = p_bckdt_per_in_ler_id)
)
order by pil.lf_evt_ocrd_dt desc;
select 'Y'
from ben_per_in_ler pil
where pil.per_in_ler_id = c_pil_id
and pil.per_in_ler_stat_cd in('BCKDT', 'VOIDD');