[Home] [Help]
267: -- added cursor for bug: 5499162
268: CURSOR c_oipl
269: IS
270: SELECT oipl_id
271: FROM ben_prtt_enrt_rslt_f
272: WHERE prtt_enrt_rslt_id IN (
273: SELECT prtt_enrt_rslt_id
274: FROM ben_prtt_enrt_actn_f
275: WHERE prtt_enrt_actn_id =
273: SELECT prtt_enrt_rslt_id
274: FROM ben_prtt_enrt_actn_f
275: WHERE prtt_enrt_actn_id =
276: p_prtt_enrt_actn_id);
277: l_oipl_id ben_prtt_enrt_rslt_f.oipl_id%TYPE;
278: -- end addition
279:
280: cursor c_elig_rec(p_eff_date date) is
281: select obj.ELIG_OBJ_ID from ben_elig_obj_f obj ,
1545: ctu.pl_id, -- Bug 1555557
1546: ctu.pgm_id,
1547: ctu.pl_typ_id
1548: from ben_cm_typ_usg_f ctu,
1549: ben_prtt_enrt_rslt_f pen
1550: where ctu.business_group_id = p_business_group_id
1551: /* First join comp objects */
1552: and pen.business_group_id = ctu.business_group_id
1553: and pen.person_id = p_person_id
1595: and pen.prtt_enrt_rslt_stat_cd is null;
1596: --
1597: cursor c2 is
1598: select null
1599: from ben_prtt_enrt_rslt_f pen
1600: where pen.business_group_id = p_business_group_id
1601: and pen.person_id = p_person_id
1602: and pen.per_in_ler_id = p_per_in_ler_id
1603: and p_effective_date
2149: ctu.pl_id, -- Bug 1555557
2150: ctu.pgm_id,
2151: ctu.pl_typ_id
2152: from ben_cm_typ_usg_f ctu,
2153: ben_prtt_enrt_rslt_f pen,
2154: ben_per_in_ler pil
2155: where ctu.business_group_id = p_business_group_id
2156: /* First join comp objects */
2157: and pen.business_group_id = ctu.business_group_id
2199: and pen.prtt_enrt_rslt_stat_cd is null;
2200: --
2201: cursor c2 is
2202: select null
2203: from ben_prtt_enrt_rslt_f pen,
2204: ben_per_in_ler pil
2205: where pen.business_group_id = p_business_group_id
2206: and pen.per_in_ler_id = p_per_in_ler_id
2207: and pen.per_in_ler_id = pil.per_in_ler_id -- 5926672 join to pil
2336: ctu.pl_id, -- Bug 1555557
2337: ctu.pgm_id,
2338: ctu.pl_typ_id
2339: from ben_cm_typ_usg_f ctu,
2340: ben_prtt_enrt_rslt_f pen,
2341: ben_pil_elctbl_chc_popl pel
2342: where ctu.business_group_id = p_business_group_id
2343: /* First join comp objects */
2344: and pen.business_group_id = ctu.business_group_id
2503: ctu.pl_id, -- Bug 1555557
2504: ctu.pgm_id,
2505: ctu.pl_typ_id
2506: from ben_cm_typ_usg_f ctu,
2507: ben_prtt_enrt_rslt_f pen,
2508: ben_pil_elctbl_chc_popl pel
2509: where ctu.business_group_id = p_business_group_id
2510: /* First join comp objects */
2511: and pen.business_group_id = ctu.business_group_id
3406: ctu.pl_id, -- Bug 1555557
3407: ctu.pgm_id,
3408: ctu.pl_typ_id
3409: from ben_cm_typ_usg_f ctu,
3410: ben_prtt_enrt_rslt_f pen,
3411: ben_prtt_rt_val prv
3412: where ctu.business_group_id = p_business_group_id
3413: /* First join comp objects */
3414: and prv.per_in_ler_id = p_per_in_ler_id
3454: --
3455: cursor c2 is
3456: select null
3457: from ben_prtt_rt_val prv,
3458: ben_prtt_enrt_rslt_f pen
3459: where prv.business_group_id = p_business_group_id
3460: and prv.per_in_ler_id = p_per_in_ler_id
3461: and prv.elctns_made_dt = p_effective_date
3462: and prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
3736: -- and regulation.
3737: --
3738: cursor c_prev_enrt is
3739: select distinct pen.pgm_id, pen.pl_typ_id
3740: from ben_prtt_enrt_rslt_f pen,
3741: ben_pl_f pln,
3742: ben_pl_regn_f plrg,
3743: ben_regn_f regn,
3744: ben_oipl_f cop,
3807: --
3808: cursor c_crntly_enrd(v_pgm_id in number,
3809: v_pl_typ_id in number) is
3810: select 'Y'
3811: from ben_prtt_enrt_rslt_f pen,
3812: ben_pl_f pln,
3813: ben_oipl_f cop,
3814: ben_opt_f opt
3815: where pen.person_id = p_person_id
3840: -- bwharton bug 1619271 added cursor below.
3841: cursor c_revise_date (v_pgm_id number, v_pl_typ_id number,
3842: v_per_in_ler_id number, v_ler_id number) is
3843: select pgm_id, pl_id, oipl_id, business_group_id
3844: from ben_prtt_enrt_rslt_f
3845: where pl_typ_id = v_pl_typ_id
3846: and pgm_id = v_pgm_id
3847: and per_in_ler_id = v_per_in_ler_id
3848: and ler_id = v_ler_id