[Home] [Help]
142: pen.RPLCS_SSPNDD_RSLT_ID,
143: pen.SSPNDD_FLAG,
144: pen.UOM
145: from ben_prtt_enrt_rslt_f pen,
146: ben_per_in_ler pil,
147: ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
148: where pil.per_in_ler_id = c_bckdt_per_in_ler_id
149: and pil.person_id = c_person_id
150: and pil.per_in_ler_id = pen.per_in_ler_id
172: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
173: and pen.prtt_enrt_rslt_id not in (
174: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
175: from ben_prtt_enrt_rslt_f pen_inner,
176: ben_per_in_ler pil_inner
177: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
178: and pil_inner.person_id = c_person_id
179: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
180: and (pen_inner.enrt_cvg_thru_dt is null or
183: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
184: union
185: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
186: from ben_le_clsn_n_rstr pen_inner,
187: ben_per_in_ler pil_inner
188: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
189: and pil_inner.person_id = c_person_id
190: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
191: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
273: pen.RPLCS_SSPNDD_RSLT_ID,
274: pen.SSPNDD_FLAG,
275: pen.UOM
276: from ben_le_clsn_n_rstr pen,
277: ben_per_in_ler pil,
278: ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
279: where pil.per_in_ler_id = c_bckdt_per_in_ler_id
280: and pil.person_id = c_person_id
281: and pil.per_in_ler_id = pel.per_in_ler_id
300: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
301: and pen.bkup_tbl_id not in (
302: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
303: from ben_prtt_enrt_rslt_f pen_inner,
304: ben_per_in_ler pil_inner
305: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
306: and pil_inner.person_id = c_person_id
307: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
308: and (pen_inner.enrt_cvg_thru_dt is null or
311: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
312: union
313: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
314: from ben_le_clsn_n_rstr pen_inner,
315: ben_per_in_ler pil_inner
316: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
317: and pil_inner.person_id = c_person_id
318: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
319: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
353: pen.PTIP_ID,
354: pen.RPLCS_SSPNDD_RSLT_ID,
355: pen.SSPNDD_FLAG
356: from ben_prtt_enrt_rslt_f pen,
357: ben_per_in_ler pil,
358: ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
359: where pil.per_in_ler_id = c_bckdt_per_in_ler_id
360: and pil.person_id = c_person_id
361: and pil.per_in_ler_id = pen.per_in_ler_id
381: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
382: and pen.prtt_enrt_rslt_id not in (
383: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
384: from ben_prtt_enrt_rslt_f pen_inner,
385: ben_per_in_ler pil_inner
386: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
387: and pil_inner.person_id = c_person_id
388: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
389: and (pen_inner.enrt_cvg_thru_dt is null or
392: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
393: union
394: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
395: from ben_le_clsn_n_rstr pen_inner,
396: ben_per_in_ler pil_inner
397: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
398: and pil_inner.person_id = c_person_id
399: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
400: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
423: pen.PTIP_ID,
424: pen.RPLCS_SSPNDD_RSLT_ID,
425: pen.SSPNDD_FLAG
426: from ben_le_clsn_n_rstr pen,
427: ben_per_in_ler pil,
428: ben_pil_elctbl_chc_popl pel --Added for Bug 4423161
429: where pil.per_in_ler_id = c_bckdt_per_in_ler_id
430: and pil.person_id = c_person_id
431: and pil.per_in_ler_id = pel.per_in_ler_id
447: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
448: and pen.bkup_tbl_id not in (
449: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
450: from ben_prtt_enrt_rslt_f pen_inner,
451: ben_per_in_ler pil_inner
452: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
453: and pil_inner.person_id = c_person_id
454: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
455: and (pen_inner.enrt_cvg_thru_dt is null or
458: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
459: union
460: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
461: from ben_le_clsn_n_rstr pen_inner,
462: ben_per_in_ler pil_inner
463: where pil_inner.per_in_ler_id = c_bckdt_per_in_ler_id
464: and pil_inner.person_id = c_person_id
465: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
466: and pen_inner.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
651: select pil.per_in_ler_id,
652: ler.name,
653: pil.bckt_per_in_ler_id,
654: ler_cs_bckdt.name ler_cs_bckdt_name
655: from ben_per_in_ler pil
656: ,ben_ler_f ler
657: ,ben_per_in_ler pil_cs_bckdt
658: ,ben_ler_f ler_cs_bckdt
659: ,ben_ptnl_ler_for_per plr
653: pil.bckt_per_in_ler_id,
654: ler_cs_bckdt.name ler_cs_bckdt_name
655: from ben_per_in_ler pil
656: ,ben_ler_f ler
657: ,ben_per_in_ler pil_cs_bckdt
658: ,ben_ler_f ler_cs_bckdt
659: ,ben_ptnl_ler_for_per plr
660: where pil.person_id = p_person_id
661: and pil.business_group_id = p_business_group_id
727: --
728: cursor c_enrt_rslt_exists is
729: select 'Y'
730: from ben_prtt_enrt_rslt_f pen,
731: ben_per_in_ler pil
732: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
733: and pil.business_group_id = p_business_group_id
734: and pen.per_in_ler_id = pil.per_in_ler_id
735: and pen.business_group_id = pil.business_group_id
787: pen.PGM_ID,
788: pen.PL_ID,
789: pen.SSPNDD_FLAG
790: from ben_prtt_enrt_rslt_f pen,
791: ben_per_in_ler pil
792: where pil.per_in_ler_id = p_pil_id
793: and pil.person_id = p_person_id
794: and pil.business_group_id = p_business_group_id
795: AND pil.per_in_ler_id = pen.per_in_ler_id
908: --
909: cursor c_bckt_csd_pil is
910: select csd_pil.lf_evt_ocrd_dt,
911: csd_pil.per_in_ler_id
912: from ben_per_in_ler csd_pil,
913: ben_per_in_ler bckt_pil
914: where bckt_pil.per_in_ler_id = p_bckdt_per_in_ler_id
915: and bckt_pil.BCKT_PER_IN_LER_ID = csd_pil.per_in_ler_id
916: and bckt_pil.business_group_id = p_business_group_id
909: cursor c_bckt_csd_pil is
910: select csd_pil.lf_evt_ocrd_dt,
911: csd_pil.per_in_ler_id
912: from ben_per_in_ler csd_pil,
913: ben_per_in_ler bckt_pil
914: where bckt_pil.per_in_ler_id = p_bckdt_per_in_ler_id
915: and bckt_pil.BCKT_PER_IN_LER_ID = csd_pil.per_in_ler_id
916: and bckt_pil.business_group_id = p_business_group_id
917: and csd_pil.business_group_id = p_business_group_id;
925: -- iREC : do not consider iRec, ABS, COMP, GSP pils.
926: --
927: cursor c_inter_pil_cnt(cv_bckt_csd_lf_evt_ocrd_dt date) is
928: select pil.per_in_ler_id, pil.object_version_number
929: from ben_per_in_ler pil,
930: ben_ler_f ler
931: where pil.per_in_ler_id <> p_per_in_ler_id
932: and pil.per_in_ler_id <> p_bckdt_per_in_ler_id
933: and pil.person_id = p_person_id
938: and pil.business_group_id = p_business_group_id
939: and nvl(pil.per_in_ler_stat_cd, 'XXXX') not in('BCKDT', 'VOIDD')
940: and pil.lf_evt_ocrd_dt > cv_bckt_csd_lf_evt_ocrd_dt
941: and pil.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt
942: from ben_per_in_ler
943: where per_in_ler_id = p_bckdt_per_in_ler_id
944: and business_group_id = p_business_group_id
945: )
946: order by pil.lf_evt_ocrd_dt asc;
1024: are made for the intervening LE based on per_in_ler_id of intervening LE*/
1025: and ( ((p_reinstate_cd is NULL or p_reinstate_cd <> 'VALIDATE_EXPLICIT_ENRT' )
1026: and ( pen.effective_start_date >= (select decode(PRVS_STAT_CD, 'STRTD',STRTD_DT,
1027: 'PROCD',PROCD_DT,LF_EVT_OCRD_DT)
1028: from ben_per_in_ler
1029: where per_in_ler_id = p_bckdt_per_in_ler_id
1030: )
1031: )) or (p_reinstate_cd = 'VALIDATE_EXPLICIT_ENRT' and pen.per_in_ler_id= l_prev_per_in_ler_id1 )
1032: )
1034: --
1035:
1036: cursor c_prev_pil(p_bckt_csd_lf_evt_ocrd_dt date,p_bckt_csd_per_in_ler_id number ) is
1037: select pil.per_in_ler_id
1038: from ben_per_in_ler pil,
1039: ben_ler_f ler
1040: where pil.per_in_ler_id not in (p_per_in_ler_id,p_bckt_csd_per_in_ler_id)
1041: and pil.person_id = p_person_id
1042: and pil.ler_id = ler.ler_id
1044: ler.effective_start_date and ler.effective_end_date
1045: and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
1046: and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
1047: and pil.lf_evt_ocrd_dt in (select max(lf_evt_ocrd_dt)
1048: from ben_per_in_ler pil2
1049: where pil2.ler_id = pil.ler_id
1050: and pil2.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
1051: and pil2.person_id = p_person_id
1052: and pil2.lf_evt_ocrd_dt < p_bckt_csd_lf_evt_ocrd_dt
1059: l_int_pil_id number;
1060: /* Cursor to get the Intervening per_in_ler_id*/
1061: cursor c_int_pil_id is
1062: select bckt_per_in_ler_id
1063: from ben_per_in_ler pil
1064: where pil.per_in_ler_id = p_bckdt_per_in_ler_id;
1065:
1066: /*Cursor to get the latest Intervening per_in_ler_id that has been processed*/
1067: cursor c_prev_pil1 is
1065:
1066: /*Cursor to get the latest Intervening per_in_ler_id that has been processed*/
1067: cursor c_prev_pil1 is
1068: select pil.per_in_ler_id
1069: from ben_per_in_ler pil,
1070: ben_ler_f ler
1071: where pil.per_in_ler_id not in (p_per_in_ler_id,p_bckdt_per_in_ler_id)
1072: and pil.person_id = p_person_id
1073: and pil.ler_id = ler.ler_id
1075: ler.effective_start_date and ler.effective_end_date
1076: and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
1077: and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
1078: and pil.lf_evt_ocrd_dt in (select lf_evt_ocrd_dt
1079: from ben_per_in_ler pil2,
1080: ben_ler_f ler1
1081: where pil2.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
1082: and pil2.person_id = p_person_id
1083: and pil2.ler_id = ler1.ler_id
1083: and pil2.ler_id = ler1.ler_id
1084: and p_effective_date between
1085: ler1.effective_start_date and ler1.effective_end_date
1086: and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
1087: and pil2.lf_evt_ocrd_dt > (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
1088: per_in_ler_id = l_int_pil_id)
1089: and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
1090: per_in_ler_id = p_bckdt_per_in_ler_id)
1091: )
1085: ler1.effective_start_date and ler1.effective_end_date
1086: and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
1087: and pil2.lf_evt_ocrd_dt > (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
1088: per_in_ler_id = l_int_pil_id)
1089: and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
1090: per_in_ler_id = p_bckdt_per_in_ler_id)
1091: )
1092: order by pil.lf_evt_ocrd_dt desc;
1093: /* End of Enhancement Bug :8716679 */
1946: ,ecd.ovrdn_flag
1947: ,ecd.ovrdn_thru_dt
1948: ,ecd.dpnt_person_id
1949: from ben_elig_cvrd_dpnt_f ecd,
1950: ben_per_in_ler pil
1951: where ecd.business_group_id=p_business_group_id
1952: and p_effective_date between
1953: ecd.effective_start_date and ecd.effective_end_date
1954: and pil.per_in_ler_id=ecd.per_in_ler_id
1968: ,ecd.ovrdn_flag
1969: ,ecd.ovrdn_thru_dt
1970: ,ecd.dpnt_person_id
1971: from ben_elig_cvrd_dpnt_f ecd,
1972: ben_per_in_ler pil
1973: where p_effective_date between
1974: ecd.effective_start_date and ecd.effective_end_date
1975: and pil.per_in_ler_id=ecd.per_in_ler_id
1976: and ecd.elig_per_elctbl_chc_id = p_curr_epe_id
1994: hr_utility.set_location ('Entering '||l_proc,10);
1995: --
1996: select count(*) into l_bckdt_ecd_count
1997: from ben_elig_cvrd_dpnt_f ecd,
1998: ben_per_in_ler pil
1999: where ecd.business_group_id=p_business_group_id
2000: and p_effective_date between
2001: ecd.effective_start_date and ecd.effective_end_date
2002: and pil.per_in_ler_id=ecd.per_in_ler_id
2106: --
2107: cursor c_bckdt_epe_cnt is
2108: select count(*)
2109: from ben_elig_per_elctbl_chc epe,
2110: ben_per_in_ler pil
2111: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
2112: and pil.person_id = p_person_id
2113: and pil.business_group_id = p_business_group_id
2114: AND pil.per_in_ler_id = epe.per_in_ler_id;
2116: --
2117: cursor c_curr_epe_cnt is
2118: select count(*)
2119: from ben_elig_per_elctbl_chc epe,
2120: ben_per_in_ler pil
2121: where pil.per_in_ler_id = p_per_in_ler_id
2122: and pil.person_id = p_person_id
2123: and pil.business_group_id = p_business_group_id
2124: AND pil.per_in_ler_id = epe.per_in_ler_id;
2136: --
2137: cursor c_bckdt_epe_dat is
2138: select epe.*
2139: from ben_elig_per_elctbl_chc epe,
2140: ben_per_in_ler pil
2141: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
2142: and pil.person_id = p_person_id
2143: and pil.business_group_id = p_business_group_id
2144: AND pil.per_in_ler_id = epe.per_in_ler_id;
2146: --
2147: cursor c_curr_epe_dat is
2148: select epe.*
2149: from ben_elig_per_elctbl_chc epe,
2150: ben_per_in_ler pil
2151: where pil.per_in_ler_id = p_per_in_ler_id
2152: and pil.person_id = p_person_id
2153: and pil.business_group_id = p_business_group_id
2154: AND pil.per_in_ler_id = epe.per_in_ler_id;
2493: cursor c_bckdt_epe_cnt is
2494: select count(*)
2495: from ben_elig_per_elctbl_chc epe,
2496: ben_pil_elctbl_chc_popl pel,
2497: ben_per_in_ler pil
2498: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
2499: and pil.person_id = p_person_id
2500: and pil.per_in_ler_id = pel.per_in_ler_id
2501: and (pel.pgm_id = p_pgm_id or
2507: cursor c_curr_epe_cnt is
2508: select count(*)
2509: from ben_elig_per_elctbl_chc epe,
2510: ben_pil_elctbl_chc_popl pel,
2511: ben_per_in_ler pil
2512: where pil.per_in_ler_id = p_per_in_ler_id
2513: and pil.person_id = p_person_id
2514: and pil.per_in_ler_id = pel.per_in_ler_id
2515: and (pel.pgm_id = p_pgm_id or
2530: cursor c_bckdt_epe_dat is
2531: select epe.*
2532: from ben_elig_per_elctbl_chc epe,
2533: ben_pil_elctbl_chc_popl pel,
2534: ben_per_in_ler pil
2535: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
2536: and pil.person_id = p_person_id
2537: and pil.per_in_ler_id = pel.per_in_ler_id
2538: and (pel.pgm_id = p_pgm_id or
2544: cursor c_curr_epe_dat is
2545: select epe.*
2546: from ben_elig_per_elctbl_chc epe,
2547: ben_pil_elctbl_chc_popl pel,
2548: ben_per_in_ler pil
2549: where pil.per_in_ler_id = p_per_in_ler_id
2550: and pil.person_id = p_person_id
2551: and pil.per_in_ler_id = pel.per_in_ler_id
2552: and (pel.pgm_id = p_pgm_id or
2916: --
2917: cursor c_ben_pil_elctbl_chc_popl is
2918: select pel.*
2919: from ben_pil_elctbl_chc_popl pel,
2920: ben_per_in_ler pil
2921: where pil.per_in_ler_id = p_per_in_ler_id
2922: and pil.business_group_id = p_business_group_id
2923: and pel.per_in_ler_id = pil.per_in_ler_id
2924: and pel.business_group_id = pil.business_group_id;
3341: pen.RPLCS_SSPNDD_RSLT_ID,
3342: pen.SSPNDD_FLAG,
3343: pen.UOM
3344: from ben_prtt_enrt_rslt_f pen,
3345: ben_per_in_ler pil
3346: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3347: and pil.person_id = p_person_id
3348: and pil.business_group_id = p_business_group_id
3349: AND pil.per_in_ler_id = pen.per_in_ler_id
3427: pen.RPLCS_SSPNDD_RSLT_ID,
3428: pen.SSPNDD_FLAG,
3429: pen.UOM
3430: from ben_le_clsn_n_rstr pen,
3431: ben_per_in_ler pil
3432: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3433: and pil.person_id = p_person_id
3434: and pil.business_group_id = p_business_group_id
3435: AND pil.per_in_ler_id = pen.per_in_ler_id
3439: --
3440: cursor c_curr_pen_dat is
3441: select pen.*
3442: from ben_prtt_enrt_rslt_f pen,
3443: ben_per_in_ler pil
3444: where pil.per_in_ler_id = p_per_in_ler_id
3445: and pil.person_id = p_person_id
3446: and pil.business_group_id = p_business_group_id
3447: AND pil.per_in_ler_id = pen.per_in_ler_id
3468: -- imputed income as they are not considered in c_curr_pen_dat
3469: --
3470: select count(*) into l_bckdt_pen_cnt_temp
3471: from ben_prtt_enrt_rslt_f pen,
3472: ben_per_in_ler pil
3473: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3474: and pil.person_id = p_person_id
3475: and pil.business_group_id = p_business_group_id
3476: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
3477: AND pil.per_in_ler_id = pen.per_in_ler_id;
3478: --
3479: select count(*) into l_bckdt_pen_cnt
3480: from ben_le_clsn_n_rstr pen,
3481: ben_per_in_ler pil
3482: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3483: and pil.person_id = p_person_id
3484: and pil.business_group_id = p_business_group_id
3485: AND pil.per_in_ler_id = pen.per_in_ler_id
3759: -- backed out per in ler.
3760: --
3761: cursor c_bckt_csd_pil is
3762: select csd_pil.lf_evt_ocrd_dt
3763: from ben_per_in_ler csd_pil,
3764: ben_per_in_ler bckt_pil
3765: where bckt_pil.per_in_ler_id = p_bckdt_per_in_ler_id
3766: and bckt_pil.BCKT_PER_IN_LER_ID = csd_pil.per_in_ler_id
3767: and bckt_pil.business_group_id = p_business_group_id
3760: --
3761: cursor c_bckt_csd_pil is
3762: select csd_pil.lf_evt_ocrd_dt
3763: from ben_per_in_ler csd_pil,
3764: ben_per_in_ler bckt_pil
3765: where bckt_pil.per_in_ler_id = p_bckdt_per_in_ler_id
3766: and bckt_pil.BCKT_PER_IN_LER_ID = csd_pil.per_in_ler_id
3767: and bckt_pil.business_group_id = p_business_group_id
3768: and csd_pil.business_group_id = p_business_group_id;
3775: -- iREC : do not consider iRec, ABS, COMP, GSP pils.
3776: --
3777: cursor c_inter_pil_cnt(cv_bckt_csd_lf_evt_ocrd_dt date) is
3778: select pil.per_in_ler_id, pil.object_version_number
3779: from ben_per_in_ler pil,
3780: ben_ler_f ler
3781: where pil.per_in_ler_id <> p_per_in_ler_id
3782: and pil.per_in_ler_id <> p_bckdt_per_in_ler_id
3783: and pil.person_id = p_person_id
3788: and pil.business_group_id = p_business_group_id
3789: and nvl(pil.per_in_ler_stat_cd, 'XXXX') not in('BCKDT', 'VOIDD')
3790: and pil.lf_evt_ocrd_dt > cv_bckt_csd_lf_evt_ocrd_dt
3791: and pil.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt
3792: from ben_per_in_ler
3793: where per_in_ler_id = p_bckdt_per_in_ler_id
3794: and business_group_id = p_business_group_id
3795: );
3796: --
3847: pen.PL_ID,
3848: pen.PL_TYP_ID,
3849: pen.PTIP_ID
3850: from ben_prtt_enrt_rslt_f pen,
3851: ben_per_in_ler pil
3852: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3853: and pil.person_id = p_person_id
3854: and pil.business_group_id = p_business_group_id
3855: and pil.per_in_ler_id = pen.per_in_ler_id
3863: pen.PL_ID,
3864: pen.PL_TYP_ID,
3865: pen.PTIP_ID
3866: from ben_le_clsn_n_rstr pen,
3867: ben_per_in_ler pil
3868: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
3869: and pil.person_id = p_person_id
3870: and pil.business_group_id = p_business_group_id
3871: AND pil.per_in_ler_id = pen.per_in_ler_id
3884: pen.PL_TYP_ID,
3885: pen.PTIP_ID,
3886: pen.effective_start_date
3887: from ben_prtt_enrt_rslt_f pen,
3888: ben_per_in_ler pil
3889: where pil.per_in_ler_id = p_per_in_ler_id
3890: and pil.person_id = p_person_id
3891: and pil.business_group_id = p_business_group_id
3892: and pil.per_in_ler_id = pen.per_in_ler_id
5009: --
5010: cursor c_pen_dpnts(cp_dpnt_person_id in number) is
5011: select pdp.*
5012: from ben_elig_cvrd_dpnt_f pdp ,
5013: ben_per_in_ler pil
5014: where pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5015: and pdp.cvg_strt_dt is not null
5016: and nvl(pdp.cvg_thru_dt, hr_api.g_eot) = hr_api.g_eot
5017: and pdp.business_group_id = p_business_group_id
6000: l_proc varchar2(72) := g_package||'.reinstate_the_prev_enrt';
6001: --
6002: cursor c_bckdt_pil is
6003: select pil.PRVS_STAT_CD, pil.object_version_number, pil.BCKT_PER_IN_LER_ID
6004: from ben_per_in_ler pil
6005: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
6006: and pil.business_group_id = p_business_group_id;
6007: --
6008: l_bckt_csd_per_in_ler_id number;
6095: pen.RPLCS_SSPNDD_RSLT_ID,
6096: pen.SSPNDD_FLAG,
6097: pen.UOM
6098: from ben_prtt_enrt_rslt_f pen,
6099: ben_per_in_ler pil
6100: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
6101: and pil.person_id = p_person_id
6102: and pil.business_group_id = p_business_group_id
6103: and pil.per_in_ler_id = pen.per_in_ler_id
6113: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
6114: and pen.prtt_enrt_rslt_id not in (
6115: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
6116: from ben_prtt_enrt_rslt_f pen_inner,
6117: ben_per_in_ler pil_inner
6118: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
6119: and pil_inner.person_id = p_person_id
6120: and pil_inner.business_group_id = p_business_group_id
6121: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
6125: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
6126: union
6127: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
6128: from ben_le_clsn_n_rstr pen_inner,
6129: ben_per_in_ler pil_inner
6130: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
6131: and pil_inner.person_id = p_person_id
6132: and pil_inner.business_group_id = p_business_group_id
6133: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
6216: pen.RPLCS_SSPNDD_RSLT_ID,
6217: pen.SSPNDD_FLAG,
6218: pen.UOM
6219: from ben_le_clsn_n_rstr pen,
6220: ben_per_in_ler pil
6221: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
6222: and pil.person_id = p_person_id
6223: and pil.business_group_id = p_business_group_id
6224: AND pil.per_in_ler_id = pen.per_in_ler_id
6231: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
6232: and pen.bkup_tbl_id not in (
6233: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
6234: from ben_prtt_enrt_rslt_f pen_inner,
6235: ben_per_in_ler pil_inner
6236: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
6237: and pil_inner.person_id = p_person_id
6238: and pil_inner.business_group_id = p_business_group_id
6239: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
6243: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
6244: union
6245: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
6246: from ben_le_clsn_n_rstr pen_inner,
6247: ben_per_in_ler pil_inner
6248: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
6249: and pil_inner.person_id = p_person_id
6250: and pil_inner.business_group_id = p_business_group_id
6251: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
6268: pel.enrt_perd_strt_dt,
6269: to_date('31-12-4712','DD-MM-YYYY') enrt_cvg_end_dt,
6270: pel.dflt_enrt_dt
6271: from ben_elig_per_elctbl_chc epe,
6272: ben_per_in_ler pil,
6273: ben_pil_elctbl_chc_popl pel
6274: where epe.per_in_ler_id = p_per_in_ler_id
6275: and epe.business_group_id = p_business_group_id
6276: and epe.pl_id = cp_pl_id
6404: --
6405: cursor c_bckt_csd_pen(cv_per_in_ler_id in number) is
6406: select pen.*, pil.lf_evt_ocrd_dt
6407: from ben_prtt_enrt_rslt_f pen,
6408: ben_per_in_ler pil
6409: where pen.per_in_ler_id = cv_per_in_ler_id
6410: and pen.per_in_ler_id = pil.per_in_ler_id
6411: and pen.business_group_id = p_business_group_id
6412: and pil.business_group_id = p_business_group_id
7505: -- 9999 do I need to void any other data, status etc.,
7506: --
7507: -- Now restore the backed out pil to Started status.
7508: -- It should be restored to the prev status 99999 once the column is
7509: -- added to the ben_per_in_ler.
7510: --
7511: reinstate_the_prev_enrt(
7512: p_person_id => p_person_id
7513: ,p_business_group_id => p_business_group_id
7893: ) is
7894: --
7895: cursor c_pil(p_per_in_ler_id in number) is
7896: select pil.object_version_number
7897: from ben_per_in_ler pil
7898: where pil.per_in_ler_id = p_per_in_ler_id;
7899: --
7900: cursor c_multiple_rate is
7901: select null
8036: l_proc varchar2(72) := g_package||'.reinstate_prev_enrt_for_popl';
8037: --
8038: cursor c_bckdt_pil is
8039: select pil.PRVS_STAT_CD, pil.object_version_number, pil.BCKT_PER_IN_LER_ID
8040: from ben_per_in_ler pil
8041: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
8042: and pil.business_group_id = p_business_group_id;
8043: --
8044: l_bckt_csd_per_in_ler_id number;
8139: --
8140: cursor c_bckt_csd_pen(cv_per_in_ler_id in number) is
8141: select pen.*, pil.lf_evt_ocrd_dt
8142: from ben_prtt_enrt_rslt_f pen,
8143: ben_per_in_ler pil
8144: where pen.per_in_ler_id = cv_per_in_ler_id
8145: and pen.per_in_ler_id = pil.per_in_ler_id
8146: and pen.business_group_id = p_business_group_id
8147: and pil.business_group_id = p_business_group_id
8248: l_interim_chc_id number;
8249:
8250: cursor c_prev_pil is
8251: select pil.per_in_ler_id
8252: from ben_per_in_ler pil,
8253: ben_ler_f ler
8254: where pil.per_in_ler_id not in (p_per_in_ler_id)
8255: and pil.person_id = p_person_id
8256: and pil.ler_id = ler.ler_id
8258: ler.effective_start_date and ler.effective_end_date
8259: and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
8260: and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
8261: and pil.lf_evt_ocrd_dt in (select max(lf_evt_ocrd_dt)
8262: from ben_per_in_ler pil2,
8263: ben_ler_f ler1
8264: where pil2.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
8265: and pil2.person_id = p_person_id
8266: and pil2.ler_id = ler1.ler_id
8266: and pil2.ler_id = ler1.ler_id
8267: and p_effective_date between
8268: ler1.effective_start_date and ler1.effective_end_date
8269: and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
8270: and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
8271: per_in_ler_id = p_per_in_ler_id)
8272: );
8273:
8274: cursor c_prev_pil_epe_id( c_elig_per_elctbl_chc_id number,c_per_in_ler_id number) is
9943: and per_in_ler_id = p_bckdt_per_in_ler_id;
9944: --
9945: cursor c_bckdt_pil is
9946: select ler.name, pil.PRVS_STAT_CD, pil.object_version_number, pil.BCKT_PER_IN_LER_ID
9947: from ben_per_in_ler pil,
9948: ben_ler_f ler
9949: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
9950: and ler.ler_id = pil.ler_id
9951: and p_effective_date between ler.effective_start_date
10006: l_int_pil_id number;
10007:
10008: cursor c_int_pil_id is
10009: select bckt_per_in_ler_id
10010: from ben_per_in_ler pil
10011: where pil.per_in_ler_id = p_bckdt_per_in_ler_id;
10012:
10013: cursor c_chk_intevent_bckdt(c_pil_id number) is
10014: select 'Y'
10011: where pil.per_in_ler_id = p_bckdt_per_in_ler_id;
10012:
10013: cursor c_chk_intevent_bckdt(c_pil_id number) is
10014: select 'Y'
10015: from ben_per_in_ler pil
10016: where pil.per_in_ler_id = c_pil_id
10017: and pil.per_in_ler_stat_cd in('BCKDT', 'VOIDD');
10018:
10019: cursor c_future_pil is
10017: and pil.per_in_ler_stat_cd in('BCKDT', 'VOIDD');
10018:
10019: cursor c_future_pil is
10020: select pil.per_in_ler_id
10021: from ben_per_in_ler pil,
10022: ben_ler_f ler
10023: where pil.per_in_ler_id not in (p_per_in_ler_id,p_bckdt_per_in_ler_id)
10024: and pil.person_id = p_person_id
10025: and pil.ler_id = ler.ler_id
10027: ler.effective_start_date and ler.effective_end_date
10028: and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
10029: and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
10030: and pil.lf_evt_ocrd_dt in (select lf_evt_ocrd_dt
10031: from ben_per_in_ler pil2,
10032: ben_ler_f ler1
10033: where pil2.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
10034: and pil2.person_id = p_person_id
10035: and pil2.ler_id = ler1.ler_id
10035: and pil2.ler_id = ler1.ler_id
10036: and p_effective_date between
10037: ler1.effective_start_date and ler1.effective_end_date
10038: and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
10039: and pil2.lf_evt_ocrd_dt > (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
10040: per_in_ler_id = l_int_pil_id)
10041: and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
10042: per_in_ler_id = p_bckdt_per_in_ler_id)
10043: )
10037: ler1.effective_start_date and ler1.effective_end_date
10038: and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
10039: and pil2.lf_evt_ocrd_dt > (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
10040: per_in_ler_id = l_int_pil_id)
10041: and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
10042: per_in_ler_id = p_bckdt_per_in_ler_id)
10043: )
10044: order by pil.lf_evt_ocrd_dt desc;
10045:
10044: order by pil.lf_evt_ocrd_dt desc;
10045:
10046: cursor c_ler_id is
10047: select le.ler_id
10048: from ben_ler_f le,ben_per_in_ler pil
10049: where pil.ler_id=le.ler_id
10050: and p_effective_date between le.effective_start_date and le.effective_end_date
10051: and pil.per_in_ler_id = p_bckdt_per_in_ler_id;
10052:
10124: pen_inner.enrt_cvg_strt_dt,
10125: pen_inner.enrt_cvg_thru_dt,
10126: pen_inner.object_version_number
10127: FROM ben_le_clsn_n_rstr pen_inner,
10128: ben_per_in_ler pil_inner
10129: WHERE pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
10130: AND pil_inner.person_id = p_person_id
10131: AND pil_inner.business_group_id = p_business_group_id
10132: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
10144:
10145: cursor c_unres_per_in_ler
10146: is
10147: select 'x'
10148: from ben_ler_f ler, ben_per_in_ler pil
10149: where ler.ler_id = pil.ler_id
10150: and ler.business_group_id = pil.business_group_id
10151: and pil.per_in_ler_id = p_per_in_ler_id
10152: and pil.business_group_id = p_business_group_id
11438: --
11439: cursor c_bckdt_pil is
11440: select pil.PER_IN_LER_STAT_CD, pil.object_version_number,
11441: pil.lf_evt_ocrd_dt
11442: from ben_per_in_ler pil
11443: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
11444: and pil.business_group_id = p_business_group_id;
11445:
11446:
11527: pen.RPLCS_SSPNDD_RSLT_ID,
11528: pen.SSPNDD_FLAG,
11529: pen.UOM
11530: from ben_le_clsn_n_rstr pen,
11531: ben_per_in_ler pil
11532: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
11533: and pil.person_id = p_person_id
11534: and pil.business_group_id = p_business_group_id
11535: AND pil.per_in_ler_id = pen.per_in_ler_id
11542: and pen.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
11543: and pen.bkup_tbl_id not in (
11544: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
11545: from ben_prtt_enrt_rslt_f pen_inner,
11546: ben_per_in_ler pil_inner
11547: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
11548: and pil_inner.person_id = p_person_id
11549: and pil_inner.business_group_id = p_business_group_id
11550: and pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
11554: and pen_inner.comp_lvl_cd not in ('PLANFC', 'PLANIMP')
11555: union
11556: select nvl(pen_inner.RPLCS_SSPNDD_RSLT_ID, -1)
11557: from ben_le_clsn_n_rstr pen_inner,
11558: ben_per_in_ler pil_inner
11559: where pil_inner.per_in_ler_id = p_bckdt_per_in_ler_id
11560: and pil_inner.person_id = p_person_id
11561: and pil_inner.business_group_id = p_business_group_id
11562: AND pil_inner.per_in_ler_id = pen_inner.per_in_ler_id
11580: pel.enrt_perd_strt_dt,
11581: to_date('31-12-4712','DD-MM-YYYY') enrt_cvg_end_dt,
11582: pel.dflt_enrt_dt
11583: from ben_elig_per_elctbl_chc epe,
11584: ben_per_in_ler pil,
11585: ben_pil_elctbl_chc_popl pel
11586: where epe.per_in_ler_id = cp_per_in_ler_id
11587: and epe.business_group_id = p_business_group_id
11588: and epe.pl_id = cp_pl_id
11723: --
11724: cursor c_bckt_csd_pen(cv_per_in_ler_id in number) is
11725: select pen.*, pil.lf_evt_ocrd_dt
11726: from ben_prtt_enrt_rslt_f pen,
11727: ben_per_in_ler pil
11728: where pen.per_in_ler_id = cv_per_in_ler_id
11729: and pen.per_in_ler_id = pil.per_in_ler_id
11730: and pen.business_group_id = p_business_group_id
11731: and pil.business_group_id = p_business_group_id
12886: l_intv_pil_id number;
12887:
12888: cursor c_int_pil_id is
12889: select pil1.bckt_per_in_ler_id
12890: from ben_per_in_ler pil,ben_per_in_ler pil1
12891: where pil.per_in_ler_id = p_bckdt_per_in_ler_id
12892: and pil.ptnl_ler_for_per_id = pil1.ptnl_ler_for_per_id
12893: and pil1.bckt_per_in_ler_id is not null
12894: order by pil.lf_evt_ocrd_dt asc;
12894: order by pil.lf_evt_ocrd_dt asc;
12895:
12896: cursor c_bckdt_pil_id is
12897: select pil.bckt_per_in_ler_id
12898: from ben_per_in_ler pil
12899: where pil.per_in_ler_id = p_bckdt_per_in_ler_id;
12900:
12901: l_bckdt_pil_id number;
12902:
12901: l_bckdt_pil_id number;
12902:
12903: cursor c_future_pil is
12904: select pil.per_in_ler_id
12905: from ben_per_in_ler pil,
12906: ben_ler_f ler
12907: where pil.per_in_ler_id not in (p_per_in_ler_id,p_bckdt_per_in_ler_id)
12908: and pil.person_id = p_person_id
12909: and pil.ler_id = ler.ler_id
12911: ler.effective_start_date and ler.effective_end_date
12912: and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
12913: and pil.per_in_ler_stat_cd not in('BCKDT', 'VOIDD')
12914: and pil.lf_evt_ocrd_dt in (select lf_evt_ocrd_dt
12915: from ben_per_in_ler pil2,
12916: ben_ler_f ler1
12917: where pil2.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD')
12918: and pil2.person_id = p_person_id
12919: and pil2.ler_id = ler1.ler_id
12919: and pil2.ler_id = ler1.ler_id
12920: and p_effective_date between
12921: ler1.effective_start_date and ler1.effective_end_date
12922: and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
12923: and pil2.lf_evt_ocrd_dt > (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
12924: per_in_ler_id = l_intv_pil_id)
12925: and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
12926: per_in_ler_id = p_bckdt_per_in_ler_id)
12927: )
12921: ler1.effective_start_date and ler1.effective_end_date
12922: and ler1.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
12923: and pil2.lf_evt_ocrd_dt > (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
12924: per_in_ler_id = l_intv_pil_id)
12925: and pil2.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from ben_per_in_ler pil3 where
12926: per_in_ler_id = p_bckdt_per_in_ler_id)
12927: )
12928: order by pil.lf_evt_ocrd_dt desc;
12929:
12928: order by pil.lf_evt_ocrd_dt desc;
12929:
12930: cursor c_chk_intevent_bckdt(c_pil_id number) is
12931: select 'Y'
12932: from ben_per_in_ler pil
12933: where pil.per_in_ler_id = c_pil_id
12934: and pil.per_in_ler_stat_cd in('BCKDT', 'VOIDD');
12935:
12936: