[Home] [Help]
109: -- Used for PGM
110: cursor c_epe_pgm is
111: select distinct
112: epe.pgm_id
113: from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
114: where p_per_in_ler_id = epe.per_in_ler_id
115: and pgm.pgm_id = epe.pgm_id
116: and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
117: and nvl(p_lf_evt_ocrd_dt,p_effective_date)
123: -- Used for PLIP and Combo PLIP
124: cursor c_epe_plip is
125: select distinct epe.pgm_id, epe.plip_id, epe.plip_ordr_num,
126: epe.pl_id, epe.pl_typ_id, epe.ptip_id, epe.ptip_ordr_num
127: from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
128: where p_per_in_ler_id = epe.per_in_ler_id
129: and epe.plip_id is not null
130: and pgm.pgm_id = epe.pgm_id
131: and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
137:
138: -- Used for PTIP and Combo PTIP
139: cursor c_epe_ptip is
140: select distinct epe.pgm_id, epe.ptip_id,epe.ptip_ordr_num, epe.pl_typ_id
141: from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
142: where p_per_in_ler_id = epe.per_in_ler_id
143: and epe.ptip_id is not null
144: and pgm.pgm_id = epe.pgm_id
145: and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
152: -- Used for OIPLIP and Combo PTIP Option
153: cursor c_epe_oipl is
154: select distinct epe.pgm_id,epe.plip_id, epe.ptip_id,epe.oipl_id,epe.oipl_ordr_num,
155: epe.oiplip_id, epe.pl_typ_id, epe.pl_id, epe.plip_ordr_num, epe.ptip_ordr_num
156: from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
157: where p_per_in_ler_id = epe.per_in_ler_id
158: and epe.oiplip_id is not null
159: and pgm.pgm_id = epe.pgm_id
160: and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
507: --
508: -- Override Cursors
509: cursor c_chk_pgm (c_pgm_id in number) is
510: select 'x'
511: from ben_elig_per_elctbl_chc epe
512: where epe.per_in_ler_id = p_per_in_ler_id
513: and epe.pgm_id = c_pgm_id
514: and epe.comp_lvl_cd = 'PGM'
515: and epe.bnft_prvdr_pool_id is not null ;
515: and epe.bnft_prvdr_pool_id is not null ;
516: -- PLIP
517: cursor c_chk_plip (c_plip_id in number) is
518: select 'x'
519: from ben_elig_per_elctbl_chc epe
520: where epe.per_in_ler_id = p_per_in_ler_id
521: and epe.plip_id = c_plip_id
522: and epe.comp_lvl_cd = 'PLIP'
523: and epe.bnft_prvdr_pool_id is not null ;
523: and epe.bnft_prvdr_pool_id is not null ;
524: -- 'CPLIP'
525: cursor c_chk_cplip (c_cmbn_plip_id in number) is
526: select 'x'
527: from ben_elig_per_elctbl_chc epe
528: where epe.per_in_ler_id = p_per_in_ler_id
529: and epe.cmbn_plip_id = c_cmbn_plip_id
530: and epe.comp_lvl_cd = 'CPLIP'
531: and epe.bnft_prvdr_pool_id is not null ;
531: and epe.bnft_prvdr_pool_id is not null ;
532: -- 'PTIP'
533: cursor c_chk_ptip (c_ptip_id in number) is
534: select 'x'
535: from ben_elig_per_elctbl_chc epe
536: where epe.per_in_ler_id = p_per_in_ler_id
537: and epe.ptip_id = c_ptip_id
538: and epe.comp_lvl_cd = 'PTIP'
539: and epe.bnft_prvdr_pool_id is not null ;
539: and epe.bnft_prvdr_pool_id is not null ;
540: -- 'CPTIP'
541: cursor c_chk_cptip (c_cmbn_ptip_id in number) is
542: select 'x'
543: from ben_elig_per_elctbl_chc epe
544: where epe.per_in_ler_id = p_per_in_ler_id
545: and epe.cmbn_ptip_id = c_cmbn_ptip_id
546: and epe.comp_lvl_cd = 'CPTIP'
547: and epe.bnft_prvdr_pool_id is not null ;
547: and epe.bnft_prvdr_pool_id is not null ;
548: -- 'OIPLIP'
549: cursor c_chk_oiplip (c_oiplip_id in number) is
550: select 'x'
551: from ben_elig_per_elctbl_chc epe
552: where epe.per_in_ler_id = p_per_in_ler_id
553: and epe.oiplip_id = c_oiplip_id
554: and epe.comp_lvl_cd = 'OIPLIP'
555: and epe.bnft_prvdr_pool_id is not null ;
555: and epe.bnft_prvdr_pool_id is not null ;
556: -- 'CPTIPOPT'
557: cursor c_chk_cptipopt (c_cmbn_ptip_opt_id in number) is
558: select 'x'
559: from ben_elig_per_elctbl_chc epe
560: where epe.per_in_ler_id = p_per_in_ler_id
561: and epe.cmbn_ptip_opt_id = c_cmbn_ptip_opt_id
562: and epe.comp_lvl_cd = 'CPTIPOPT'
563: and epe.bnft_prvdr_pool_id is not null ;