[Home] [Help]
1356: /* Modified the condition for Enhancement Bug :8716679. Defaulting the explicit elections
1357: will only be called if p_reinstate_dflts_flag = 'Y' or else normal defaulting logic will work.*/
1358: and ( (p_reinstate_dflts_flag = 'N' and (epe.dflt_flag = 'Y' or epe.crntly_enrd_flag = 'Y') ) or
1359: (p_reinstate_dflts_flag = 'Y' and epe.crntly_enrd_flag = 'Y' and prtt_enrt_rslt_id is not null
1360: and 'Y' = ( select 'Y' from ben_prtt_enrt_rslt_f pen
1361: where pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
1362: and pen.per_in_ler_id = p_prev_per_in_ler_id
1363: and pen.prtt_enrt_rslt_stat_cd is null
1364: and pen.enrt_mthd_cd = 'E'
1363: and pen.prtt_enrt_rslt_stat_cd is null
1364: and pen.enrt_mthd_cd = 'E'
1365: and pen.sspndd_flag = 'N'
1366: and not exists
1367: (select 'Y' from ben_prtt_enrt_rslt_f pen2
1368: where pen.prtt_enrt_rslt_id = pen2.rplcs_sspndd_rslt_id
1369: and pen2.prtt_enrt_rslt_stat_cd is null
1370: and pen2.per_in_ler_id = p_prev_per_in_ler_id) )
1371: ) )
1369: and pen2.prtt_enrt_rslt_stat_cd is null
1370: and pen2.per_in_ler_id = p_prev_per_in_ler_id) )
1371: ) )
1372: /* End of change for Enhancement Bug :8716679 */
1373: and not exists (select null from ben_prtt_enrt_rslt_f pen
1374: where pen.per_in_ler_id = epe.per_in_ler_id
1375: -- Bug 6319484 Instead of checking for same pen_id,
1376: -- check if not already enrolled in same plan
1377: and nvl(pen.pgm_id,hr_api.g_number) = nvl(epe.pgm_id,hr_api.g_number)
1387: and pen.enrt_cvg_thru_dt = hr_api.g_eot )
1388: order by epe.pgm_id, epe.pl_id;
1389:
1390: cursor c_test(c_pen_id number) is
1391: select pen.prtt_enrt_rslt_id||'Y' from ben_prtt_enrt_rslt_f pen
1392: where pen.prtt_enrt_rslt_id = c_pen_id
1393: and pen.per_in_ler_id = l_prev_pil_id
1394: and pen.prtt_enrt_rslt_stat_cd is null
1395: and pen.enrt_mthd_cd = 'E'
1394: and pen.prtt_enrt_rslt_stat_cd is null
1395: and pen.enrt_mthd_cd = 'E'
1396: and pen.sspndd_flag = 'N'
1397: and not exists
1398: (select 'Y' from ben_prtt_enrt_rslt_f pen2
1399: where pen.prtt_enrt_rslt_id = pen2.rplcs_sspndd_rslt_id
1400: and pen2.prtt_enrt_rslt_stat_cd is null
1401: and pen2.per_in_ler_id = l_prev_pil_id);
1402: l_test varchar2(100);
1444: ,enrt_cvg_thru_dt
1445: ,ERLST_DEENRT_DT
1446: ,enrt_ovrid_thru_dt
1447: ,enrt_ovridn_flag
1448: From ben_prtt_enrt_rslt_f
1449: Where prtt_enrt_rslt_id = lc_prtt_enrt_rslt_id
1450: and p_effective_date between
1451: effective_start_date and effective_end_date
1452: and prtt_enrt_rslt_stat_cd IS NULL