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_ENROLLMENT_ACTION_ITEMS dependencies on BEN_PRTT_ENRT_RSLT_F

Line 409: ben_prtt_enrt_rslt_f pen

405: cursor c_ctfns_ler_chg is
406: select ldc.susp_if_ctfn_not_prvd_flag
407: from ben_ler_chg_dpnt_cvg_ctfn_f lcc,
408: ben_ler_chg_dpnt_cvg_f ldc,
409: ben_prtt_enrt_rslt_f pen
410: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
411: and pen.business_group_id = p_business_group_id
412: and p_effective_date between pen.effective_start_date
413: and pen.effective_end_date

Line 650: from ben_prtt_enrt_rslt_f pen,

646: cursor c_elctbl_chc is
647: select epe.elig_per_elctbl_chc_id,
648: epe.pl_id,
649: epe.pgm_id
650: from ben_prtt_enrt_rslt_f pen,
651: ben_elig_per_elctbl_chc epe,
652: ben_per_in_ler pil
653: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
654: and pen.prtt_enrt_rslt_stat_cd is null

Line 1118: ben_prtt_enrt_rslt_f pen

1114: cursor c_pl_name is
1115: select pln.name,
1116: pen.person_id
1117: from ben_pl_f pln,
1118: ben_prtt_enrt_rslt_f pen
1119: where pln.pl_id = pen.pl_id
1120: and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id /* Bug 4039404 */
1121: and pen.prtt_enrt_rslt_stat_cd is null
1122: and p_effective_date between pln.effective_Start_date

Line 1652: ben_prtt_enrt_rslt_f pen

1648: pea.effective_start_date,
1649: pea.effective_end_date,
1650: pen.object_version_number rslt_object_version_number
1651: from ben_prtt_enrt_actn_f pea,
1652: ben_prtt_enrt_rslt_f pen
1653: where pea.prtt_enrt_actn_id = p_prtt_enrt_actn_id
1654: and pea.cmpltd_dt IS NULL
1655: and pen.prtt_enrt_rslt_stat_cd is null
1656: and p_effective_date between pea.effective_start_date

Line 2294: from ben_prtt_enrt_rslt_f pen

2290: pen.oipl_id,
2291: pen.ler_id,
2292: pen.person_id,
2293: pen.object_version_number
2294: from ben_prtt_enrt_rslt_f pen
2295: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2296: and pen.business_group_id = p_business_group_id
2297: and pen.prtt_enrt_rslt_stat_cd is null
2298: and p_effective_date between

Line 2350: ben_prtt_enrt_rslt_f pen

2346: --
2347: cursor c_ldc (p_lvl_cd varchar) is
2348: select ldc.susp_if_ctfn_not_prvd_flag
2349: from BEN_LER_CHG_DPNT_CVG_f ldc,
2350: ben_prtt_enrt_rslt_f pen
2351: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2352: and pen.business_group_id = p_business_group_id
2353: and p_effective_date between pen.effective_start_date
2354: and pen.effective_end_date

Line 2685: from per_contact_relationships pcr, ben_prtt_enrt_rslt_f perslt

2681: -- Cursor to check if person resides with participant
2682: --
2683: cursor c_rsds is
2684: select pcr.rltd_per_rsds_w_dsgntr_flag
2685: from per_contact_relationships pcr, ben_prtt_enrt_rslt_f perslt
2686: where perslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2687: and perslt.person_id = pcr.person_id
2688: and pcr.contact_person_id = p_dpnt_bnf_person_id
2689: and pcr.business_group_id = p_business_group_id

Line 2702: ben_prtt_enrt_rslt_f perslt

2698: select peradd.primary_flag,
2699: peradd.address_line1,
2700: peradd.postal_code
2701: from per_addresses peradd,
2702: ben_prtt_enrt_rslt_f perslt
2703: where perslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2704: and perslt.person_id = peradd.person_id
2705: and peradd.business_group_id = p_business_group_id
2706: and perslt.business_group_id = p_business_group_id

Line 3193: from ben_prtt_enrt_rslt_f rslt

3189: rslt.oipl_id,
3190: rslt.ler_id,
3191: rslt.person_id,
3192: rslt.business_group_id
3193: from ben_prtt_enrt_rslt_f rslt
3194: where rslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3195: and rslt.prtt_enrt_rslt_stat_cd is null
3196: and p_effective_date between
3197: rslt.effective_start_date and rslt.effective_end_date;

Line 3295: ben_prtt_enrt_rslt_f pen

3291: ldc.susp_if_ctfn_not_prvd_flag,
3292: ldc.ctfn_determine_cd
3293: from ben_ler_chg_dpnt_cvg_ctfn_f lcc,
3294: ben_ler_chg_dpnt_cvg_f ldc,
3295: ben_prtt_enrt_rslt_f pen
3296: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3297: and pen.business_group_id = p_business_group_id
3298: and p_effective_date between pen.effective_start_date
3299: and pen.effective_end_date

Line 3404: ben_prtt_enrt_rslt_f pen,

3400: pen.pgm_id,
3401: pen.ptip_id,
3402: pen.pl_id
3403: from ben_elig_cvrd_dpnt_f ecd,
3404: ben_prtt_enrt_rslt_f pen,
3405: ben_per_in_ler pil
3406: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3407: and pen.prtt_enrt_rslt_id = ecd.prtt_enrt_rslt_id
3408: and ecd.cvg_strt_dt is not null

Line 4446: ,ben_prtt_enrt_rslt_f rslt

4442: --
4443: cursor c_future_sal_inc is
4444: Select 'X'
4445: from per_pay_proposals pay
4446: ,ben_prtt_enrt_rslt_f rslt
4447: ,ben_pl_typ_f tyP
4448: Where rslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4449: and p_effective_date between rslt.effective_start_date and rslt.effective_end_date
4450: and pay.approved = 'Y'

Line 4699: ben_prtt_enrt_rslt_f pen

4695: from (select sum(nvl(drq.mn_dpnts_rqd_num, 0 )) tot_mn_dpnts_rqd_num,
4696: sum(nvl(drq.mx_dpnts_alwd_num,9999999999)) tot_mx_dpnts_alwd_num
4697: from ben_dsgn_rqmt_f drq,
4698: ben_oipl_f cop,
4699: ben_prtt_enrt_rslt_f pen
4700: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4701: -- and drq.oipl_id = pen.oipl_id -- 3730053: OPT level DSGN_RQMTS override OIPL level
4702: and pen.oipl_id = cop.oipl_id
4703: and ( (drq.oipl_id = pen.oipl_id

Line 4727: ben_prtt_enrt_rslt_f pen

4723: UNION -- 3730053: Added this to check PL-level designation requirements are specified at PLAN-LEVEL.
4724: select sum(nvl(drq.mn_dpnts_rqd_num, 0 )) tot_mn_dpnts_rqd_num,
4725: sum(nvl(drq.mx_dpnts_alwd_num,9999999999)) tot_mx_dpnts_alwd_num
4726: from ben_dsgn_rqmt_f drq,
4727: ben_prtt_enrt_rslt_f pen
4728: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4729: and drq.pl_id = pen.pl_id
4730: and drq.dsgn_typ_cd = 'DPNT'
4731: --and drq.mn_dpnts_rqd_num > 0

Line 4759: BEN_PRTT_ENRT_RSLT_F pen

4755: cursor c_elig_dpnts is
4756: SELECT 'S'
4757: from BEN_ELIG_DPNT egd,
4758: BEN_ELIG_PER_ELCTBL_CHC epe,
4759: BEN_PRTT_ENRT_RSLT_F pen
4760: where pen.PRTT_ENRT_RSLT_ID = p_prtt_enrt_rslt_id
4761: and pen.PRTT_ENRT_RSLT_ID = epe.PRTT_ENRT_RSLT_ID
4762: and egd.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
4763: and pen.prtt_enrt_rslt_stat_cd is null

Line 5067: from ben_prtt_enrt_rslt_f perslt,

5063: --
5064: -- cursor c_min_no_grp modified against Bug 7516987
5065: cursor c_min_no_grp (c_effective_date date) is
5066: select mn_dpnts_rqd_num
5067: from ben_prtt_enrt_rslt_f perslt,
5068: ben_dsgn_rqmt_f drq
5069: where perslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5070: and perslt.prtt_enrt_rslt_stat_cd is null
5071: and drq.dsgn_typ_cd = 'DPNT'

Line 5116: from ben_prtt_enrt_rslt_f perslt,

5112: --
5113: -- cursor c_min_typ modified against Bug 7516987
5114: cursor c_min_typ (c_effective_date date) is
5115: select distinct 'X' -- mn_dpnts_rqd_num
5116: from ben_prtt_enrt_rslt_f perslt,
5117: ben_dsgn_rqmt_f drq
5118: where perslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5119: and drq.dsgn_typ_cd = 'DPNT'
5120: and drq.grp_rlshp_cd is not null

Line 5168: from ben_prtt_enrt_rslt_f pen,

5164: -- Bug 7516987
5165: cursor c_get_ler_typ is
5166: select ler.typ_cd,
5167: pil.lf_evt_ocrd_dt
5168: from ben_prtt_enrt_rslt_f pen,
5169: ben_per_in_ler pil,
5170: ben_ler_f ler
5171: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5172: and pen.per_in_ler_id = pil.per_in_ler_id

Line 5418: from ben_prtt_enrt_rslt_f pen,

5414: l_dsgn_cd varchar2(30);
5415:
5416: cursor c_chc_flags is
5417: select epe.alws_dpnt_dsgn_flag, epe.dpnt_dsgn_cd
5418: from ben_prtt_enrt_rslt_f pen,
5419: ben_elig_per_elctbl_chc epe,
5420: ben_per_in_ler pil
5421: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5422: and pen.prtt_enrt_rslt_stat_cd is null

Line 5691: from ben_prtt_enrt_rslt_f pen,

5687: */
5688:
5689: cursor c_current_pl_enrollment (p_prtt_enrt_rslt_id number) is
5690: select 'Y'
5691: from ben_prtt_enrt_rslt_f pen,
5692: ben_prtt_enrt_rslt_f pen2
5693: where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5694: and nvl(pen2.rplcs_sspndd_rslt_id,-999) <> pen.prtt_enrt_rslt_id --NO Interim
5695: and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')

Line 5692: ben_prtt_enrt_rslt_f pen2

5688:
5689: cursor c_current_pl_enrollment (p_prtt_enrt_rslt_id number) is
5690: select 'Y'
5691: from ben_prtt_enrt_rslt_f pen,
5692: ben_prtt_enrt_rslt_f pen2
5693: where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5694: and nvl(pen2.rplcs_sspndd_rslt_id,-999) <> pen.prtt_enrt_rslt_id --NO Interim
5695: and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
5696: and pen2.pl_id = pen.pl_id

Line 5704: ben_prtt_enrt_rslt_f pen3

5700: ( pen2.per_in_ler_id = pen.per_in_ler_id and
5701: ( pen.enrt_cvg_thru_dt <> to_date('31-12-4712','dd-mm-yyyy') or
5702: ( (-- pen.prtt_enrt_rslt_id <> pen2.prtt_enrt_rslt_id and
5703: exists (select 'x' from ben_elig_per_elctbl_chc epe,
5704: ben_prtt_enrt_rslt_f pen3
5705: where epe.per_in_ler_id = pen.per_in_ler_id and
5706: epe.pl_id = pen.pl_id and
5707: ( epe.pgm_id = pen.pgm_id or pen.pgm_id is null) and
5708: ( epe.oipl_id = pen.oipl_id or pen.oipl_id is null) and

Line 5736: from ben_prtt_enrt_rslt_f pen,

5732: --Current Option enrollment
5733: --
5734: cursor c_current_oipl_enrollment (p_prtt_enrt_rslt_id number) is
5735: select 'Y'
5736: from ben_prtt_enrt_rslt_f pen,
5737: ben_prtt_enrt_rslt_f pen2
5738: where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5739: and nvl(pen2.rplcs_sspndd_rslt_id,-999) <> pen.prtt_enrt_rslt_id --NO Interim
5740: and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')

Line 5737: ben_prtt_enrt_rslt_f pen2

5733: --
5734: cursor c_current_oipl_enrollment (p_prtt_enrt_rslt_id number) is
5735: select 'Y'
5736: from ben_prtt_enrt_rslt_f pen,
5737: ben_prtt_enrt_rslt_f pen2
5738: where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5739: and nvl(pen2.rplcs_sspndd_rslt_id,-999) <> pen.prtt_enrt_rslt_id --NO Interim
5740: and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
5741: and pen2.pl_id = pen.pl_id

Line 5750: ben_prtt_enrt_rslt_f pen3

5746: ( pen2.per_in_ler_id = pen.per_in_ler_id and
5747: ( pen.enrt_cvg_thru_dt <> to_date('31-12-4712','dd-mm-yyyy') or
5748: ( (-- pen.prtt_enrt_rslt_id <> pen2.prtt_enrt_rslt_id or
5749: exists (select 'x' from ben_elig_per_elctbl_chc epe,
5750: ben_prtt_enrt_rslt_f pen3
5751: where epe.per_in_ler_id = pen2.per_in_ler_id and
5752: epe.pl_id = pen2.pl_id and
5753: ( epe.pgm_id = pen2.pgm_id or pen2.pgm_id is null) and
5754: ( epe.oipl_id = pen2.oipl_id ) and

Line 5790: (select distinct(pen1.prtt_enrt_rslt_id) from ben_prtt_enrt_rslt_f pen1, ben_prtt_enrt_rslt_f pen2

5786: cursor c_ctfn_prvdd (p_prtt_enrt_rslt_id number) is
5787: select null
5788: from ben_prtt_enrt_ctfn_prvdd_f pcs
5789: where pcs.prtt_enrt_rslt_id in
5790: (select distinct(pen1.prtt_enrt_rslt_id) from ben_prtt_enrt_rslt_f pen1, ben_prtt_enrt_rslt_f pen2
5791: where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5792: and pen1.person_id = pen2.person_id
5793: and pen1.prtt_enrt_rslt_stat_cd is null
5794: and pen2.prtt_enrt_rslt_stat_cd is null

Line 5819: (select distinct(pen1.prtt_enrt_rslt_id) from ben_prtt_enrt_rslt_f pen1, ben_prtt_enrt_rslt_f pen2

5815: cursor c_ctfn_prvdd2 (p_prtt_enrt_rslt_id number) is
5816: select null
5817: from ben_prtt_enrt_ctfn_prvdd_f pcs
5818: where pcs.prtt_enrt_rslt_id in
5819: (select distinct(pen1.prtt_enrt_rslt_id) from ben_prtt_enrt_rslt_f pen1, ben_prtt_enrt_rslt_f pen2
5820: where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5821: and pen1.person_id = pen2.person_id
5822: and pen1.prtt_enrt_rslt_stat_cd is null
5823: and pen2.prtt_enrt_rslt_stat_cd is null

Line 5848: from ben_prtt_enrt_rslt_f pen,

5844: -- End Bug 5965415
5845: --
5846: cursor c_prtt_enrt_rslt (p_prtt_enrt_rslt_id number) is
5847: select pen.prtt_enrt_rslt_id
5848: from ben_prtt_enrt_rslt_f pen,
5849: ben_prtt_enrt_rslt_f pen2
5850: where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5851: -- and pen2.bnft_amt is null -- Bug 5887665 Need to call this code for benefit level certifications as well
5852: and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')

Line 5849: ben_prtt_enrt_rslt_f pen2

5845: --
5846: cursor c_prtt_enrt_rslt (p_prtt_enrt_rslt_id number) is
5847: select pen.prtt_enrt_rslt_id
5848: from ben_prtt_enrt_rslt_f pen,
5849: ben_prtt_enrt_rslt_f pen2
5850: where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5851: -- and pen2.bnft_amt is null -- Bug 5887665 Need to call this code for benefit level certifications as well
5852: and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
5853: and pen2.pl_id = pen.pl_id

Line 5890: from ben_prtt_enrt_rslt_f pen,

5886: and pln.effective_end_date;
5887: --
5888: cursor c_prev_enrollment (p_prtt_enrt_rslt_id number) is
5889: select pen.bnft_amt
5890: from ben_prtt_enrt_rslt_f pen,
5891: ben_prtt_enrt_rslt_f pen2
5892: where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5893: and pen2.bnft_amt <> pen.bnft_amt
5894: and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')

Line 5891: ben_prtt_enrt_rslt_f pen2

5887: --
5888: cursor c_prev_enrollment (p_prtt_enrt_rslt_id number) is
5889: select pen.bnft_amt
5890: from ben_prtt_enrt_rslt_f pen,
5891: ben_prtt_enrt_rslt_f pen2
5892: where pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
5893: and pen2.bnft_amt <> pen.bnft_amt
5894: and pen2.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
5895: and pen2.pl_id = pen.pl_id

Line 6167: from ben_prtt_enrt_rslt_f pen,

6163: epe.per_in_ler_id,
6164: epe.pl_id,
6165: nvl(pen.bnft_amt, 0) bnft_amt,
6166: pen.oipl_id
6167: from ben_prtt_enrt_rslt_f pen,
6168: ben_elig_per_elctbl_chc epe,
6169: ben_per_in_ler pil
6170: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6171: and pen.prtt_enrt_rslt_stat_cd is null

Line 7037: from ben_prtt_enrt_rslt_f pen,

7033:
7034: cursor c_pcp_dpnt_cd_oipl is
7035: select cop.pcp_dpnt_dsgn_cd,
7036: ecd.elig_cvrd_dpnt_id
7037: from ben_prtt_enrt_rslt_f pen,
7038: ben_elig_cvrd_dpnt_f ecd,
7039: ben_oipl_f cop
7040: where cop.oipl_id = pen.oipl_id
7041: and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id

Line 7060: from ben_prtt_enrt_rslt_f pen,

7056: --
7057: cursor c_pcp_dpnt_cd is
7058: select pcp.pcp_dpnt_dsgn_cd ,
7059: ecd.elig_cvrd_dpnt_id
7060: from ben_prtt_enrt_rslt_f pen,
7061: ben_elig_cvrd_dpnt_f ecd,
7062: ben_pl_pcp pcp
7063: where pcp.pl_id = pen.pl_id
7064: and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id

Line 7224: from ben_prtt_enrt_rslt_f perf,

7220: l_pcp_actn_warning boolean := FALSE;
7221:
7222: cursor c_pcp_cd_oipl is
7223: select cop.pcp_dsgn_cd
7224: from ben_prtt_enrt_rslt_f perf,
7225: ben_oipl_f cop
7226: where cop.oipl_id = perf.oipl_id
7227: and perf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7228: and perf.business_group_id = p_business_group_id

Line 7238: from ben_prtt_enrt_rslt_f perf,

7234: and cop.pcp_dsgn_cd is not null ;
7235: -- added the cop.pcp_dsgn_cd is not null condition bug 1421978
7236: cursor c_pcp_cd is
7237: select pcp.pcp_dsgn_cd
7238: from ben_prtt_enrt_rslt_f perf,
7239: ben_pl_pcp pcp
7240: where pcp.pl_id = perf.pl_id
7241: and perf.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7242: and perf.business_group_id = p_business_group_id

Line 7711: from ben_prtt_enrt_rslt_f

7707: and effective_end_date;
7708: cursor c_person_id is
7709: select person_id,
7710: pl_id
7711: from ben_prtt_enrt_rslt_f
7712: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
7713: --
7714: l_bnf_actn_typ_dfnd boolean; -- bug 1169240
7715: l_rqd_flag varchar2(1);

Line 7722: FROM ben_prtt_enrt_rslt_f pen

7718: -- start bug 5667528
7719: cursor c_pen_info is
7720: SELECT pen.pgm_id, pen.pl_id, pen.pl_typ_id, pen.oipl_id, pen.ler_id,
7721: pen.person_id, pen.business_group_id
7722: FROM ben_prtt_enrt_rslt_f pen
7723: WHERE pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
7724: AND p_effective_date BETWEEN pen.effective_start_date
7725: AND pen.effective_end_date;
7726: --

Line 8748: from ben_prtt_enrt_rslt_f rslt

8744: rslt.oipl_id,
8745: rslt.ler_id,
8746: rslt.person_id,
8747: rslt.business_group_id
8748: from ben_prtt_enrt_rslt_f rslt
8749: where rslt.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
8750: and p_effective_date between
8751: rslt.effective_start_date and rslt.effective_end_date;
8752:

Line 8831: ben_prtt_enrt_rslt_f pen,

8827: from per_all_people_f per,
8828: per_contact_relationships pcr,
8829: ben_pl_f pl,
8830: ben_pl_bnf_f plb,
8831: ben_prtt_enrt_rslt_f pen,
8832: ben_per_in_ler pil
8833: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
8834: and pen.pl_id = pl.pl_id
8835: and pl.bnf_qdro_rl_apls_flag = 'Y'

Line 8870: ben_prtt_enrt_rslt_f pen,

8866: from per_all_people_f per,
8867: hr_all_organization_units o,
8868: ben_pl_f pl,
8869: ben_pl_bnf_f plb,
8870: ben_prtt_enrt_rslt_f pen,
8871: ben_per_in_ler pil
8872: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
8873: and pen.pl_id = pl.pl_id
8874: and pl.bnf_qdro_rl_apls_flag = 'Y'

Line 9398: l_comp_lvl_cd ben_prtt_enrt_rslt_f.comp_lvl_cd%type;

9394: --
9395: l_waive_flag varchar2(2) := 'N';
9396: l_pl_id ben_pl_f.pl_id%type;
9397: l_oipl_id ben_oipl_f.oipl_id%type;
9398: l_comp_lvl_cd ben_prtt_enrt_rslt_f.comp_lvl_cd%type;
9399:
9400: --
9401: cursor c_waive_info_flag is
9402: select pen.pl_id, pen.oipl_id,pen.sspndd_flag --Bug 2228123 added sspndd_flag

Line 9403: from ben_prtt_enrt_rslt_f pen

9399:
9400: --
9401: cursor c_waive_info_flag is
9402: select pen.pl_id, pen.oipl_id,pen.sspndd_flag --Bug 2228123 added sspndd_flag
9403: from ben_prtt_enrt_rslt_f pen
9404: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
9405: and pen.business_group_id = p_business_group_id
9406: and p_effective_date between pen.effective_start_date
9407: and pen.effective_end_date;

Line 9435: from ben_prtt_enrt_rslt_f pen

9431: --
9432: cursor c_info_for_msg is
9433: select pen.person_id,
9434: pln.name
9435: from ben_prtt_enrt_rslt_f pen
9436: ,ben_pl_f pln
9437: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
9438: and pen.pl_id= pln.pl_id
9439: and pen.business_group_id = p_business_group_id

Line 9454: from ben_prtt_enrt_rslt_f pen

9450: --BUG 3042379 Don't create action items for the interim
9451: --enrollment.
9452: cursor c_interim is
9453: select 'x'
9454: from ben_prtt_enrt_rslt_f pen
9455: where pen.rplcs_sspndd_rslt_id = p_prtt_enrt_rslt_id
9456: and p_effective_date between pen.effective_start_date
9457: and pen.effective_end_date
9458: and pen.prtt_enrt_rslt_stat_cd is null ;