[Home] [Help]
60: ---- this is not taking the first piece of dentrolled row but the second piece
61:
62: cursor c_previous_ptip_oipl_result is
63: select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
64: from ben_prtt_enrt_rslt_f pen,
65: ben_oipl_f oipl
66: where oipl.oipl_id = pen.oipl_id
67: and pen.pl_typ_id = l_pl_typ_id
68: and oipl.opt_id = l_opt_id
77:
78:
79: cursor c_previous_pgm_ptip_result is
80: select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
81: from ben_prtt_enrt_rslt_f pen
82: where pen.pl_typ_id = l_pl_typ_id
83: and pen.person_id = p_person_id
84: and pen.per_in_ler_id = p_per_in_ler_id
85: and pen.effective_end_date = hr_api.g_eot
88: ;
89:
90: cursor c_previous_pl_oipl_result is
91: select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
92: from ben_prtt_enrt_rslt_f pen,
93: ben_oipl_f oipl
94: where oipl.oipl_id = pen.oipl_id
95: and pen.pl_typ_id = l_pl_typ_id
96: and oipl.opt_id = l_opt_id
109: --- when the previois result_id available
110:
111: cursor c_previous_result_id_result is
112: select pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
113: from ben_prtt_enrt_rslt_f pen
114: where
115: pen.prtt_enrt_rslt_id = p_prev_prtt_enrt_rslt_id
116: and pen.person_id = p_person_id
117: -- this condition removed to CFD from any result
123:
124:
125: cursor c_previous_pl_result is
126: select pen.prtt_enrt_rslt_id,pen.ENRT_CVG_STRT_DT ,pen.per_in_ler_id
127: from ben_prtt_enrt_rslt_f pen
128: where pen.pl_typ_id = l_pl_typ_id
129: and pen.person_id = p_person_id
130: and pen.per_in_ler_id = p_per_in_ler_id
131: and pen.effective_end_date = hr_api.g_eot
515: pen.pen_attribute28,
516: pen.pen_attribute29,
517: pen.pen_attribute30
518: -- 3517682 end
519: from ben_prtt_enrt_rslt_f pen
520: where pen.prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
521: and p_effective_date
522: between pen.effective_start_date
523: and pen.effective_end_date
588: and pil.per_in_ler_stat_cd = 'STRTD'
589: and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
590: and (epe.pgm_id is not null
591: and not exists(select null
592: from ben_prtt_enrt_rslt_f pen
593: where pen.pgm_id = epe.pgm_id
594: and pen.per_in_ler_id = epe.per_in_ler_id
595: and pen.enrt_cvg_thru_dt = hr_api.g_eot
596: and pen.effective_end_date = hr_api.g_eot)
595: and pen.enrt_cvg_thru_dt = hr_api.g_eot
596: and pen.effective_end_date = hr_api.g_eot)
597: or epe.pl_id is not null
598: and not exists(select null
599: from ben_prtt_enrt_rslt_f pen
600: where pen.pl_id = epe.pl_id
601: and pen.per_in_ler_id = epe.per_in_ler_id
602: and pen.enrt_cvg_thru_dt = hr_api.g_eot
603: and pen.effective_end_date = hr_api.g_eot)
623: and pil.per_in_ler_stat_cd = 'STRTD'
624: and pel.pil_elctbl_chc_popl_id = epe.pil_elctbl_chc_popl_id
625: and (epe.pgm_id is not null
626: and not exists(select null
627: from ben_prtt_enrt_rslt_f pen
628: where pen.pgm_id = epe.pgm_id
629: and pen.per_in_ler_id = epe.per_in_ler_id
630: and pen.enrt_cvg_thru_dt = hr_api.g_eot
631: and pen.effective_end_date = hr_api.g_eot)
630: and pen.enrt_cvg_thru_dt = hr_api.g_eot
631: and pen.effective_end_date = hr_api.g_eot)
632: or epe.pl_id is not null
633: and not exists(select null
634: from ben_prtt_enrt_rslt_f pen
635: where pen.pl_id = epe.pl_id
636: and pen.per_in_ler_id = epe.per_in_ler_id
637: and pen.enrt_cvg_thru_dt = hr_api.g_eot
638: and pen.effective_end_date = hr_api.g_eot));
724: --
725: cursor c_pgm_pnip_id(p_per_in_ler_id number) is
726: --
727: select distinct pen.pgm_id pgm_id, null pl_id
728: from ben_prtt_enrt_rslt_f pen,
729: ben_per_in_ler pil,
730: ben_elig_per_elctbl_chc epe
731: where pil.per_in_ler_id = p_per_in_ler_id
732: and pil.per_in_ler_Stat_cd = 'STRTD'
740: and epe.auto_enrt_flag = 'Y'
741: and epe.per_in_ler_id = pil.per_in_ler_id
742: UNION
743: select distinct null pgm_id, pen.pl_id pl_id
744: from ben_prtt_enrt_rslt_f pen,
745: ben_per_in_ler pil,
746: ben_elig_per_elctbl_chc epe
747: where pil.per_in_ler_id = p_per_in_ler_id
748: and pil.per_in_ler_Stat_cd = 'STRTD'