eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BEN_BACK_OUT_LIFE_EVENT dependencies on BEN_PER_IN_LER

Line 218: ben_per_in_ler to get l_person_id

214: for not NULL l_person_id prior to
215: opening cursor c_prv_of_previous_
216: pil. Also modified c_prv_of_
217: previous_pil to include join to
218: ben_per_in_ler to get l_person_id
219: based on this cursor rather than
220: c_ben_prtt_rt_val.
221: 03-JAN-01 tilak 115.53 - Bug 1182293 reimburement reqist table added
222: 09-feb-01 ikasire 115.54 Bug 1627373 and 1584238 to fix the

Line 507: ben_per_in_ler pil,

503: epe.object_version_number,
504: epe.prtt_enrt_rslt_id
505: from
506: ben_prtt_enrt_rslt_f pen,
507: ben_per_in_ler pil,
508: ben_elig_per_elctbl_chc epe
509: where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
510: and pen.effective_end_date = hr_api.g_eot
511: and pen.enrt_cvg_thru_dt = hr_api.g_eot

Line 525: from ben_per_in_ler pil,

521: l_upd_epe c_get_pil_id%rowtype;
522:
523: cursor c_prev_pil_id is
524: select per_in_ler_id
525: from ben_per_in_ler pil,
526: ben_ler_f ler
527: where pil.per_in_ler_id <> p_per_in_ler_id
528: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
529: and pil.person_id = (select person_id from ben_per_in_ler where

Line 529: and pil.person_id = (select person_id from ben_per_in_ler where

525: from ben_per_in_ler pil,
526: ben_ler_f ler
527: where pil.per_in_ler_id <> p_per_in_ler_id
528: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
529: and pil.person_id = (select person_id from ben_per_in_ler where
530: per_in_ler_id = p_per_in_ler_id)
531: and pil.ler_id = ler.ler_id
532: and ler.typ_cd not in ('COMP', 'ABS', 'GSP', 'IREC')
533: and p_effective_date between ler.effective_start_date and ler.effective_end_date

Line 595: ben_per_in_ler pil,

591: cursor get_contacts_pils(cv_person_id in number,
592: cv_csd_by_ptnl_ler_for_per_id in number) is
593: select pil.*
594: from per_contact_relationships pcr,
595: ben_per_in_ler pil,
596: ben_PTNL_LER_FOR_PER ppl
597: where --pcr.contact_person_id = cv_person_id
598: pcr.person_id = cv_person_id -- Bug 6620291
599: and pcr.business_group_id = p_business_group_id

Line 620: ben_per_in_ler pil,

616: cursor get_fut_dtd_cntct_pils(cv_person_id in number
617: ) is
618: select pil.*
619: from per_contact_relationships pcr,
620: ben_per_in_ler pil,
621: ben_PTNL_LER_FOR_PER ppl
622: where pcr.contact_person_id = cv_person_id
623: and pcr.business_group_id = p_business_group_id
624: and pil.lf_evt_ocrd_dt > (Select pil1.lf_evt_ocrd_dt

Line 625: from ben_per_in_ler pil1

621: ben_PTNL_LER_FOR_PER ppl
622: where pcr.contact_person_id = cv_person_id
623: and pcr.business_group_id = p_business_group_id
624: and pil.lf_evt_ocrd_dt > (Select pil1.lf_evt_ocrd_dt
625: from ben_per_in_ler pil1
626: where pil1.per_in_ler_id= p_per_in_ler_id)
627: and pcr.personal_flag = 'Y'
628: and pil.person_id = pcr.person_id
629: and pil.per_in_ler_stat_cd in ('STRTD', 'PROCD')

Line 757: procedure unprocess_ptnl_ler(p_per_in_ler in out nocopy BEN_PER_IN_LER%ROWTYPE

753: --
754: -- ----------------------------------------------------------------------------
755: -- |------------------------< unprocess_ptnl_ler >-----------------------------|
756: -- ----------------------------------------------------------------------------
757: procedure unprocess_ptnl_ler(p_per_in_ler in out nocopy BEN_PER_IN_LER%ROWTYPE
758: ,p_bckt_stat_cd in varchar2
759: ,p_effective_date in date) is
760: --
761: l_proc varchar2(72) := g_package||'unprocess_ptnl_ler';

Line 1469: from ben_per_in_ler pil

1465: pil.ptnl_ler_for_per_id,
1466: pil.person_id,
1467: pil.lf_evt_ocrd_dt,
1468: pil.ler_id
1469: from ben_per_in_ler pil
1470: where pil.per_in_ler_id = p_per_in_ler_id;
1471:
1472:
1473:

Line 1476: from ben_per_in_ler pil

1472:
1473:
1474: cursor c_get_all_per_in_ler is
1475: select *
1476: from ben_per_in_ler pil
1477: where pil.per_in_ler_id = p_per_in_ler_id and
1478: pil.business_group_id=p_business_group_id
1479: ;
1480:

Line 1492: ben_per_in_ler pil

1488: --
1489: cursor c_ler_typ is
1490: select ler.typ_cd
1491: from ben_ler_f ler,
1492: ben_per_in_ler pil
1493: where pil.per_in_ler_id = p_per_in_ler_id
1494: and ler.ler_id = pil.ler_id
1495: /*and ler.business_group_id = p_business_group_id*/ --CWBGLOBAL
1496: and pil.lf_evt_ocrd_Dt between

Line 1514: from ben_per_in_ler

1510: -- 2982606
1511: cursor c_prev_pil_id (p_person_id number, p_lf_evt_ocrd_dt date) is
1512: select per_in_ler_id ,
1513: lf_evt_ocrd_dt
1514: from ben_per_in_ler
1515: where per_in_ler_id <> p_per_in_ler_id
1516: and per_in_ler_stat_cd = 'PROCD'
1517: and person_id = p_person_id
1518: and lf_evt_ocrd_dt <= p_lf_evt_ocrd_dt

Line 1543: from ben_per_in_ler pil,

1539:
1540: cursor c_prv_pil(p_person_id number, p_lf_evt_ocrd_dt date) is
1541: select per_in_ler_id ,
1542: lf_evt_ocrd_dt
1543: from ben_per_in_ler pil,
1544: ben_ler_f ler
1545: where per_in_ler_id <> p_per_in_ler_id
1546: and per_in_ler_stat_cd = 'PROCD'
1547: and person_id = p_person_id

Line 1631: -- BEN_PER_IN_LER (PIL)

1627: --
1628: -- Tables that will be deleted from include and must be deleted from in
1629: -- a bottom to top fashion :
1630: --
1631: -- BEN_PER_IN_LER (PIL)
1632: -- BEN_PRTT_ENRT_RSLT_F (PEN)
1633: -- BEN_PL_BNF_F (PBN) (join to pil to check status)
1634: -- BEN_PRTT_ENRT_ACTN_F (PEA) (join to pil to check status)
1635: -- BEN_BNFT_PRVDD_LDGR_F (BPL)(join to pil to check status)

Line 2440: ben_per_in_ler pil

2436: --
2437: cursor c_get_past_pil (p_per_in_ler_id number) is
2438: select max(pea.per_in_ler_id)
2439: from ben_prtt_enrt_actn_f pea,
2440: ben_per_in_ler pil
2441: where pea.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2442: and pea.per_in_ler_id <> p_per_in_ler_id
2443: and pea.business_group_id = p_business_group_id
2444: and pea.per_in_ler_id = pil.per_in_ler_id

Line 2811: -- BEN_PER_IN_LER

2807: --
2808: -- START ----> DELETE ROUTINE FOR BEN_ELIG_PER_OPT_F
2809: --
2810: -- Source Table
2811: -- BEN_PER_IN_LER
2812: --
2813: cursor c_ben_ELIG_PER_OPT_f is
2814: select epo.ELIG_PER_OPT_id,
2815: epo.object_version_number,

Line 2818: ben_per_in_ler pil

2814: select epo.ELIG_PER_OPT_id,
2815: epo.object_version_number,
2816: epo.effective_start_date --AMN bug 12732878
2817: from ben_ELIG_PER_OPT_f epo,
2818: ben_per_in_ler pil
2819: where pil.per_in_ler_id = p_per_in_ler_id
2820: and pil.business_group_id = p_business_group_id
2821: and epo.per_in_ler_id = pil.per_in_ler_id
2822: and epo.business_group_id = pil.business_group_id

Line 2866: from ben_per_in_ler

2862: */
2863: /*
2864: --RCHASE 5364
2865: and epo.effective_end_date <= (select lf_evt_ocrd_dt
2866: from ben_per_in_ler
2867: where per_in_ler_id = p_per_in_ler_id); */
2868: --
2869: cursor c_epo_max_esd_of_past_pil(v_ELIG_PER_OPT_id in number) is
2870: select epo.effective_end_date, epo.object_version_number

Line 2872: ben_per_in_ler pil

2868: --
2869: cursor c_epo_max_esd_of_past_pil(v_ELIG_PER_OPT_id in number) is
2870: select epo.effective_end_date, epo.object_version_number
2871: from ben_ELIG_PER_OPT_f epo,
2872: ben_per_in_ler pil
2873: where epo.ELIG_PER_OPT_id = v_ELIG_PER_OPT_id
2874: and nvl(epo.per_in_ler_id , -1) <> p_per_in_ler_id
2875: and epo.business_group_id = p_business_group_id
2876: and epo.per_in_ler_id = pil.per_in_ler_id

Line 2899: -- BEN_PER_IN_LER

2895: --
2896: -- START ----> DELETE ROUTINE FOR BEN_ELIG_PER_F
2897: --
2898: -- Source Table
2899: -- BEN_PER_IN_LER
2900: --
2901: cursor c_ben_elig_per_f is
2902: select pep.elig_per_id,
2903: pep.object_version_number,

Line 2906: ben_per_in_ler pil

2902: select pep.elig_per_id,
2903: pep.object_version_number,
2904: pep.effective_start_date --AMN bug 12732878
2905: from ben_elig_per_f pep,
2906: ben_per_in_ler pil
2907: where pil.per_in_ler_id = p_per_in_ler_id
2908: and pil.business_group_id = p_business_group_id
2909: and pep.per_in_ler_id = pil.per_in_ler_id
2910: and pep.business_group_id = pil.business_group_id

Line 2940: ben_per_in_ler pil

2936: */
2937: cursor c_pep_max_esd_of_past_pil(v_elig_per_id in number) is
2938: select pep.effective_end_date, pep.object_version_number
2939: from ben_elig_per_f pep,
2940: ben_per_in_ler pil
2941: where pep.elig_per_id = v_elig_per_id
2942: and ((pep.per_in_ler_id <> p_per_in_ler_id
2943: and pep.per_in_ler_id is not null)
2944: or

Line 2999: -- BEN_PER_IN_LER

2995: -- END ----> DELETE ROUTINE FOR BEN_ELIG_PER_F
2996: -- START ----> DELETE ROUTINE FOR BEN_PRTT_PREM_F
2997: --
2998: -- Source Table
2999: -- BEN_PER_IN_LER
3000: --
3001: ----7133998
3002: cursor c_ben_prtt_prem_f_corr is
3003: select ppe.*

Line 3005: ben_per_in_ler pil,

3001: ----7133998
3002: cursor c_ben_prtt_prem_f_corr is
3003: select ppe.*
3004: from ben_prtt_prem_f ppe,
3005: ben_per_in_ler pil,
3006: BEN_LE_CLSN_N_RSTR bkup
3007: where pil.per_in_ler_id = p_per_in_ler_id
3008: and pil.business_group_id = p_business_group_id
3009: and ppe.per_in_ler_id = pil.per_in_ler_id

Line 3037: ben_per_in_ler pil

3033: cursor c_ben_prtt_prem_f is
3034: select ppe.prtt_prem_id,
3035: ppe.object_version_number
3036: from ben_prtt_prem_f ppe,
3037: ben_per_in_ler pil
3038: where pil.per_in_ler_id = p_per_in_ler_id
3039: and pil.business_group_id = p_business_group_id
3040: and ppe.per_in_ler_id = pil.per_in_ler_id
3041: and ppe.business_group_id = pil.business_group_id

Line 3072: ben_per_in_ler pil

3068: */
3069: cursor c_ppe_max_esd_of_past_pil(v_prtt_prem_id in number) is
3070: select prm.effective_end_date, prm.object_version_number
3071: from ben_prtt_prem_f prm,
3072: ben_per_in_ler pil
3073: where prm.prtt_prem_id = v_prtt_prem_id
3074: and nvl(prm.per_in_ler_id , -1) <> p_per_in_ler_id
3075: and prm.business_group_id = p_business_group_id
3076: and prm.per_in_ler_id = pil.per_in_ler_id

Line 3096: -- BEN_PER_IN_LER

3092: --
3093: -- START ----> DELETE ROUTINE FOR BEN_ELIG_CVRD_DPNT_F
3094: --
3095: -- Source Table
3096: -- BEN_PER_IN_LER
3097: --
3098: -- Bug 5222 : Removed the effective date check.
3099: -- See comments at c_ben_prtt_enrt_rslt_f cursor.
3100: --

Line 3107: ben_per_in_ler pil

3103: pdp.object_version_number,
3104: -- bug 5668052
3105: pdp.effective_start_date
3106: from ben_elig_cvrd_dpnt_f pdp,
3107: ben_per_in_ler pil
3108: where pil.per_in_ler_id = p_per_in_ler_id
3109: and pil.business_group_id = p_business_group_id
3110: and pdp.per_in_ler_id = pil.per_in_ler_id
3111: and pdp.business_group_id = pil.business_group_id

Line 3135: ben_per_in_ler pil

3131: */
3132: cursor c_pdp_max_esd_of_past_pil(v_elig_cvrd_dpnt_id in number) is
3133: select pdp.effective_end_date, pdp.object_version_number
3134: from ben_elig_cvrd_dpnt_f pdp,
3135: ben_per_in_ler pil
3136: where pdp.elig_cvrd_dpnt_id = v_elig_cvrd_dpnt_id
3137: and nvl(pdp.per_in_ler_id , -1) <> p_per_in_ler_id
3138: and pdp.business_group_id = p_business_group_id
3139: and pdp.per_in_ler_id = pil.per_in_ler_id

Line 3161: -- BEN_PER_IN_LER

3157: --
3158: -- START ----> DELETE ROUTINE FOR BEN_PL_BNF_F
3159: --
3160: -- Source Table
3161: -- BEN_PER_IN_LER
3162: --
3163: cursor c_ben_pl_bnf_f is
3164: select pbn.pl_bnf_id,
3165: pbn.object_version_number,

Line 3169: ben_per_in_ler pil

3165: pbn.object_version_number,
3166: -- bug 5668052
3167: pbn.effective_start_date
3168: from ben_pl_bnf_f pbn,
3169: ben_per_in_ler pil
3170: where pil.per_in_ler_id = p_per_in_ler_id
3171: and pil.business_group_id = p_business_group_id
3172: and pbn.per_in_ler_id = pil.per_in_ler_id
3173: and pbn.business_group_id = pil.business_group_id

Line 3208: ben_per_in_ler pil

3204: -- 5895645 : typo fix
3205: cursor c_pbn_max_esd_of_past_pil(v_pl_bnf_id in number) is
3206: select pbn.effective_end_date, pbn.object_version_number
3207: from ben_pl_bnf_f pbn,
3208: ben_per_in_ler pil
3209: where pbn.pl_bnf_id = v_pl_bnf_id
3210: and nvl(pbn.per_in_ler_id , -1) <> p_per_in_ler_id
3211: and pbn.business_group_id = p_business_group_id
3212: and pbn.per_in_ler_id = pil.per_in_ler_id

Line 3234: -- BEN_PER_IN_LER

3230: --
3231: -- START ----> DELETE ROUTINE FOR BEN_PRMRY_CARE_PRVDR_F
3232: --
3233: -- Source Table
3234: -- BEN_PER_IN_LER
3235: -- Drive Routes
3236: -- BEN_PRTT_ENRT_RSLT_F
3237: --
3238: --

Line 3245: ben_per_in_ler pil

3241: ppr.effective_start_date,
3242: ppr.object_version_number
3243: from ben_prmry_care_prvdr_f ppr,
3244: ben_prtt_enrt_rslt_f pen,
3245: ben_per_in_ler pil
3246: where pil.per_in_ler_id = p_per_in_ler_id
3247: and pil.business_group_id = p_business_group_id
3248: and pen.per_in_ler_id = pil.per_in_ler_id
3249: and pen.business_group_id = pil.business_group_id

Line 3276: -- BEN_PER_IN_LER

3272: --
3273: -- START ----> DELETE ROUTINE FOR BEN_PRTT_RT_VAL
3274: --
3275: -- Source Table
3276: -- BEN_PER_IN_LER
3277: --
3278: cursor c_ben_prtt_rt_val is
3279: select prv.prtt_rt_val_id,
3280: prv.object_version_number,

Line 3288: ben_per_in_ler pil

3284: prv.prtt_enrt_rslt_id,
3285: prv.pk_id,
3286: prv.pk_id_table_name
3287: from ben_prtt_rt_val prv,
3288: ben_per_in_ler pil
3289: where pil.per_in_ler_id = p_per_in_ler_id
3290: and pil.business_group_id = p_business_group_id
3291: and prv.prtt_rt_val_stat_cd is null
3292: and prv.per_in_ler_id = pil.per_in_ler_id

Line 3318: ben_per_in_ler pil

3314: and bkp.BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
3315: )
3316: ), '1',2,1 ) adj_rec /* Bug 12576710: Added decode statement*/
3317: from ben_prtt_rt_val prv,
3318: ben_per_in_ler pil
3319: where prv.ended_per_in_ler_id = pil.per_in_ler_id
3320: and prv.ended_per_in_ler_id = p_per_in_ler_id
3321: and prv.per_in_ler_id <> p_per_in_ler_id -- Bug 8234902, not to consider the
3322: -- rates of backed out LifeEvent when determining previous pil

Line 3437: ben_per_in_ler pil

3433: /* BUG 3507554 Performance Changes
3434: cursor c_multiple_rate is
3435: select 'Y'
3436: from ben_prtt_rt_val prv,
3437: ben_per_in_ler pil
3438: where pil.per_in_ler_id = p_per_in_ler_id
3439: and pil.business_group_id = p_business_group_id
3440: and prv.prtt_rt_val_stat_cd is null
3441: and prv.per_in_ler_id = pil.per_in_ler_id

Line 3503: -- BEN_PER_IN_LER

3499: --
3500: -- START ----> DELETE ROUTINE FOR BEN_PRTT_ENRT_RSLT_F
3501: --
3502: -- Source Table
3503: -- BEN_PER_IN_LER
3504: --
3505:
3506:
3507: cursor c_ben_prtt_enrt_rslt_f is

Line 3520: ben_per_in_ler pil

3516: pen.effective_start_date,
3517: pen.per_in_ler_id
3518: ,pil.lf_evt_ocrd_dt
3519: from ben_prtt_enrt_rslt_f pen,
3520: ben_per_in_ler pil
3521: where pil.per_in_ler_id = p_per_in_ler_id
3522: and pil.business_group_id = p_business_group_id
3523: and pen.per_in_ler_id = pil.per_in_ler_id
3524: and pen.business_group_id = pil.business_group_id

Line 3685: ben_per_in_ler pil

3681: -- on order by clause.
3682: cursor c_pen_max_esd_of_past_pil(v_prtt_enrt_rslt_id in number) is
3683: select pen.effective_end_date,pen.object_version_number, pen.per_in_ler_id
3684: from ben_prtt_enrt_rslt_f pen,
3685: ben_per_in_ler pil
3686: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
3687: and nvl(pen.per_in_ler_id , -1) <> p_per_in_ler_id
3688: and pen.business_group_id = p_business_group_id
3689: and pen.per_in_ler_id = pil.per_in_ler_id

Line 3736: ben_per_in_ler pil

3732: bpl.effective_end_date,
3733: bpl.effective_start_date,
3734: bpl.per_in_ler_id
3735: from ben_bnft_prvdd_ldgr_f bpl,
3736: ben_per_in_ler pil
3737: where pil.per_in_ler_id = p_per_in_ler_id
3738: and pil.business_group_id = p_business_group_id
3739: and bpl.per_in_ler_id = pil.per_in_ler_id
3740: and bpl.business_group_id = pil.business_group_id

Line 3785: ben_per_in_ler pil

3781: -- Bug#2646851 - cursor modified with order by clause and max function removed
3782: cursor c_bpl_max_esd_of_past_pil(v_bnft_prvdd_ldgr_id in number) is
3783: select bpl.effective_end_date, bpl.object_version_number
3784: from ben_bnft_prvdd_ldgr_f bpl,
3785: ben_per_in_ler pil
3786: where bpl.bnft_prvdd_ldgr_id = v_bnft_prvdd_ldgr_id
3787: and nvl(bpl.per_in_ler_id , -1) <> p_per_in_ler_id
3788: and bpl.business_group_id = p_business_group_id
3789: and bpl.per_in_ler_id = pil.per_in_ler_id

Line 3795: -- -- Source Table -- BEN_PER_IN_LER --

3791: order by bpl.effective_end_date desc;
3792: -- END ---> DELETE ROUTINE FOR BEN_BNFT_PRVDD_LDGR_F
3793:
3794: -- START ----> DELETE ROUTINE FOR BEN_PIL_ELCTBL_CHC_POPL
3795: -- -- Source Table -- BEN_PER_IN_LER --
3796: cursor c_ben_pil_elctbl_chc_popl is
3797: select pel.pil_elctbl_chc_popl_id,
3798: pel.object_version_number
3799: from ben_pil_elctbl_chc_popl pel,

Line 3800: ben_per_in_ler pil

3796: cursor c_ben_pil_elctbl_chc_popl is
3797: select pel.pil_elctbl_chc_popl_id,
3798: pel.object_version_number
3799: from ben_pil_elctbl_chc_popl pel,
3800: ben_per_in_ler pil
3801: where pil.per_in_ler_id = p_per_in_ler_id
3802: and pil.business_group_id = p_business_group_id
3803: and pel.per_in_ler_id = pil.per_in_ler_id
3804: and pel.business_group_id = pil.business_group_id;

Line 3832: ,ben_per_in_ler pil

3828: ,prc.effective_start_date
3829: ,pil.person_id
3830: from ben_prtt_reimbmt_rqst_f prc
3831: ,ben_prtt_rt_val prv
3832: ,ben_per_in_ler pil
3833: where prv.per_in_ler_id = p_per_in_ler_id
3834: and prv.PRTT_REIMBMT_RQST_id = prc.PRTT_REIMBMT_RQST_id
3835: and pil.per_in_ler_id = p_per_in_ler_id
3836: -- hnarayan -- bug 2223214

Line 3870: (select per_in_ler_id from ben_per_in_ler pil,

3866: If Open is the first LE,pen.effective_start_date < pen.enrt_cvg_strt_dt holds good for enrollment records that are VOIDD and tries to do delete future
3867: changes which raises APP-PAY-07187 error.So check whether enrolment records exists for previous LE*/
3868: and exists
3869: (select '1' from ben_prtt_enrt_rslt_f pen1,
3870: (select per_in_ler_id from ben_per_in_ler pil,
3871: ben_ler_f ler
3872: where pil.person_id = (select person_id
3873: from ben_per_in_ler pil2
3874: where pil2.per_in_ler_id=p_per_in_ler_id)

Line 3873: from ben_per_in_ler pil2

3869: (select '1' from ben_prtt_enrt_rslt_f pen1,
3870: (select per_in_ler_id from ben_per_in_ler pil,
3871: ben_ler_f ler
3872: where pil.person_id = (select person_id
3873: from ben_per_in_ler pil2
3874: where pil2.per_in_ler_id=p_per_in_ler_id)
3875: and pil.per_in_ler_id <> p_per_in_ler_id
3876: and pil.ler_id = ler.ler_id
3877: and p_effective_date between

Line 3882: ben_per_in_ler pil1

3878: ler.effective_start_date and ler.effective_end_date
3879: and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
3880: and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
3881: and pil.lf_evt_ocrd_dt < (select lf_evt_ocrd_dt from
3882: ben_per_in_ler pil1
3883: where pil1.per_in_ler_id = p_per_in_ler_id)
3884: order by per_in_ler_id desc) prev_pil
3885: where pen1.per_in_ler_id = prev_pil.per_in_ler_id
3886: and pen1.effective_start_date < pen1.enrt_cvg_strt_dt

Line 3911: ben_per_in_ler pil

3907: cursor c_pen_sus is
3908: select pen.prtt_enrt_rslt_id,
3909: pen.per_in_ler_id
3910: from ben_prtt_enrt_rslt_f pen,
3911: ben_per_in_ler pil
3912: where pen.sspndd_flag = 'Y'
3913: and pen.per_in_ler_id <> pil.per_in_ler_id
3914: and pil.per_in_ler_id = p_per_in_ler_id
3915: and pen.person_id = pil.person_id