[Home] [Help]
170: AND cpp.pgm_id = ctpd.pgm_id
171: AND ( pln.enrt_pl_opt_flag = 'Y'
172: OR NOT EXISTS (
173: SELECT 'x'
174: FROM ben_oipl_f cop
175: WHERE cop.pl_id = pln.pl_id
176: AND l_effective_date BETWEEN cop.effective_start_date
177: AND cop.effective_end_date)
178: )
190: CURSOR c_oiplip IS
191: SELECT opp.oiplip_id oiplip_id, opp.oipl_id oipl_id, cop.opt_id opt_id,
192: opp.plip_id plip_id, cppd.pl_typ_id pl_typ_id,
193: cppd.ptip_id ptip_id, cppd.pl_id pl_id, cppd.pgm_id pgm_id, cop.ordr_num oipl_ordr_num
194: FROM ben_oiplip_f opp, ben_oipl_f cop, hri_cs_co_pgmh_plip_ct cppd
195: WHERE opp.plip_id = cppd.plip_id
196: AND cop.oipl_id = opp.oipl_id
197: AND l_effective_date BETWEEN opp.effective_start_date
198: AND opp.effective_end_date
567: AND l_effective_date BETWEEN pln.effective_start_date
568: AND pln.effective_end_date)
569: OR EXISTS (
570: SELECT 'x'
571: FROM ben_oipl_f cop, ben_pl_f pln
572: WHERE cop.pl_id = oppd.pl_id
573: AND cop.pl_id = pln.pl_id
574: AND l_effective_date BETWEEN pln.effective_start_date
575: AND pln.effective_end_date
677: AND cpp.pgm_id = ctpd.pgm_id
678: AND ( pln.enrt_pl_opt_flag = 'Y'
679: OR NOT EXISTS (
680: SELECT 'x'
681: FROM ben_oipl_f cop
682: WHERE cop.pl_id = pln.pl_id
683: AND l_effective_date BETWEEN cop.effective_start_date
684: AND cop.effective_end_date)
685: )
694: IS
695: SELECT opp.oiplip_id oiplip_id, opp.oipl_id oipl_id, cop.opt_id opt_id,
696: opp.plip_id plip_id, cppd.pl_typ_id pl_typ_id,
697: cppd.ptip_id ptip_id, cppd.pl_id pl_id, cppd.pgm_id pgm_id, cop.ordr_num oipl_ordr_num
698: FROM ben_oiplip_f opp, ben_oipl_f cop, hri_cs_co_pgmh_plip_ct cppd
699: WHERE opp.plip_id = cppd.plip_id
700: AND cop.oipl_id = opp.oipl_id
701: AND l_effective_date BETWEEN opp.effective_start_date
702: AND opp.effective_end_date