[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct
epe.pgm_id
from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
where p_per_in_ler_id = epe.per_in_ler_id
and pgm.pgm_id = epe.pgm_id
and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pgm.effective_start_date
and pgm.effective_end_date
and nvl(epe.request_id,-1) = fnd_global.conc_request_id
order by 1;
select distinct epe.pgm_id, epe.plip_id, epe.plip_ordr_num,
epe.pl_id, epe.pl_typ_id, epe.ptip_id, epe.ptip_ordr_num
from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
where p_per_in_ler_id = epe.per_in_ler_id
and epe.plip_id is not null
and pgm.pgm_id = epe.pgm_id
and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pgm.effective_start_date
and pgm.effective_end_date
and nvl(epe.request_id,-1) = fnd_global.conc_request_id
order by 1, 2;
select distinct epe.pgm_id, epe.ptip_id,epe.ptip_ordr_num, epe.pl_typ_id
from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
where p_per_in_ler_id = epe.per_in_ler_id
and epe.ptip_id is not null
and pgm.pgm_id = epe.pgm_id
and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pgm.effective_start_date
and pgm.effective_end_date
and nvl(epe.request_id,-1) = fnd_global.conc_request_id
order by 1, 2;
select distinct epe.pgm_id,epe.plip_id, epe.ptip_id,epe.oipl_id,epe.oipl_ordr_num,
epe.oiplip_id, epe.pl_typ_id, epe.pl_id, epe.plip_ordr_num, epe.ptip_ordr_num
from ben_elig_per_elctbl_chc epe, ben_pgm_f pgm
where p_per_in_ler_id = epe.per_in_ler_id
and epe.oiplip_id is not null
and pgm.pgm_id = epe.pgm_id
and pgm.pgm_typ_cd in ('COBRAFLX', 'FLEX', 'FPC')
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pgm.effective_start_date
and pgm.effective_end_date
and nvl(epe.request_id,-1) = fnd_global.conc_request_id
order by 1, 2,3,4;
select abr.acty_base_rt_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_stat_cd = 'A'
and abr.rt_usg_cd in ('FLXCR')
and abr.pgm_id = l_pgm_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date;
select abr.acty_base_rt_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_stat_cd = 'A'
and abr.rt_usg_cd in ('FLXCR')
and abr.plip_id = l_plip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date
and abr.cmbn_plip_id is null;
select abr.acty_base_rt_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_stat_cd = 'A'
and abr.rt_usg_cd in ('FLXCR')
and abr.ptip_id = l_ptip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date
and abr.cmbn_ptip_id is null
and abr.cmbn_ptip_opt_id is null;
select abr.acty_base_rt_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_stat_cd = 'A'
and abr.rt_usg_cd in ('FLXCR')
and abr.cmbn_ptip_id = l_cmbn_ptip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date
and abr.cmbn_ptip_opt_id is null
and abr.ptip_id is null;
select abr.acty_base_rt_id, abr.cmbn_ptip_id
from ben_acty_base_rt_f abr,
ben_cmbn_ptip_f cptip,
ben_ptip_f ptip
where abr.acty_base_rt_stat_cd = 'A'
and abr.rt_usg_cd in ('FLXCR')
--and abr.cmbn_ptip_id = l_cmbn_ptip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date
and abr.cmbn_ptip_id = cptip.cmbn_ptip_id
and abr.cmbn_ptip_opt_id is null
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cptip.effective_start_date
and cptip.effective_end_date
and cptip.cmbn_ptip_id = ptip.cmbn_ptip_id
and cptip.pgm_id = ptip.pgm_id
and ptip.pgm_id = l_pgm_id
and ptip.ptip_id = l_ptip_id
and abr.ptip_id is null;
select abr.acty_base_rt_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_stat_cd = 'A'
and abr.rt_usg_cd in ('FLXCR')
and abr.cmbn_plip_id = l_cmbn_plip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date;
select abr.acty_base_rt_id, abr.cmbn_plip_id
from ben_acty_base_rt_f abr,
ben_plip_f plip,
ben_cmbn_plip_f cplip
where abr.acty_base_rt_stat_cd = 'A'
and abr.rt_usg_cd in ('FLXCR')
--and abr.cmbn_plip_id = l_cmbn_plip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date
and abr.cmbn_plip_id = cplip.cmbn_plip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cplip.effective_start_date
and cplip.effective_end_date
and cplip.cmbn_plip_id = plip.cmbn_plip_id
and cplip.pgm_id = plip.pgm_id
and plip.plip_id = l_plip_id
and plip.pgm_id = l_pgm_id ;
select abr.acty_base_rt_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_stat_cd = 'A'
and abr.rt_usg_cd in ('FLXCR')
and abr.cmbn_ptip_opt_id = l_cmbn_ptip_opt_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date
and abr.cmbn_ptip_id is null
and abr.ptip_id is null;
select abr.acty_base_rt_id, abr.cmbn_ptip_opt_id
from ben_acty_base_rt_f abr,
ben_optip_f otp,
ben_oipl_f oipl
where abr.acty_base_rt_stat_cd = 'A'
and abr.rt_usg_cd in ('FLXCR')
and abr.cmbn_ptip_opt_id = otp.cmbn_ptip_opt_id
and otp.ptip_id = l_ptip_id
and otp.pgm_id = l_pgm_id
and oipl.oipl_id = l_oipl_id
and oipl.opt_id = otp.opt_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date
and abr.cmbn_ptip_id is null
and abr.ptip_id is null;
select abr.acty_base_rt_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_stat_cd = 'A'
and abr.rt_usg_cd in ('FLXCR')
and abr.oiplip_id = l_oiplip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date;
select bpp.bnft_prvdr_pool_id
from ben_bnft_prvdr_pool_f bpp
where bpp.pgm_id = l_pgm_id
--bug 2251364 if the other pool created first
--- this will create problem , pgm_id stored for
--- all the levels
and cmbn_ptip_id is null
and cmbn_ptip_opt_id is null
and oiplip_id is null
and cmbn_plip_id is null
and plip_id is null
and ptip_id is null
--
-- Bug 2200783 : Removed as the flag do not have any significance and
-- similar check is not available at other levels.
--
-- and bpp.pgm_pool_flag = 'Y'
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between bpp.effective_start_date
and bpp.effective_end_date
and rownum = 1;
select bpp.bnft_prvdr_pool_id
from ben_bnft_prvdr_pool_f bpp
where bpp.plip_id = l_plip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between bpp.effective_start_date
and bpp.effective_end_date
and rownum = 1
and bpp.oiplip_id is null
and bpp.cmbn_plip_id is null;
select bpp.bnft_prvdr_pool_id
from ben_bnft_prvdr_pool_f bpp
where bpp.ptip_id = l_ptip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between bpp.effective_start_date
and bpp.effective_end_date
and rownum = 1
and bpp.cmbn_ptip_id is null
and bpp.cmbn_ptip_opt_id is null;
select bpp.bnft_prvdr_pool_id, bpp.cmbn_ptip_id
from ben_bnft_prvdr_pool_f bpp,ben_cmbn_ptip_f cbp,ben_ptip_f ctp
where ctp.ptip_id = l_ptip_id
and ctp.cmbn_ptip_id = cbp.cmbn_ptip_id
and bpp.cmbn_ptip_id = cbp.cmbn_ptip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between bpp.effective_start_date
and bpp.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cbp.effective_start_date
and cbp.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between ctp.effective_start_date
and ctp.effective_end_date
and rownum = 1
and bpp.ptip_id is null
and bpp.cmbn_ptip_opt_id is null;
select bpp.bnft_prvdr_pool_id, bpp.cmbn_plip_id
from ben_bnft_prvdr_pool_f bpp,ben_cmbn_plip_f cpl, ben_plip_f cpp
where cpp.plip_id = l_plip_id
and cpp.cmbn_plip_id = cpl.cmbn_plip_id
and bpp.cmbn_plip_id = cpl.cmbn_plip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between bpp.effective_start_date
and bpp.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cpl.effective_start_date
and cpl.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cpp.effective_start_date
and cpp.effective_end_date
and rownum = 1
and bpp.oiplip_id is null
and bpp.plip_id is null;
select bpp.bnft_prvdr_pool_id, bpp.cmbn_ptip_opt_id
from ben_bnft_prvdr_pool_f bpp,ben_cmbn_ptip_opt_f cpt, ben_opt_f opt,
ben_oipl_f oipl
where oipl.oipl_id = l_oipl_id
and opt.opt_id = oipl.opt_id
and cpt.ptip_id = l_ptip_id
and opt.cmbn_ptip_opt_id = cpt.cmbn_ptip_opt_id
and bpp.cmbn_ptip_opt_id = cpt.cmbn_ptip_opt_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between bpp.effective_start_date
and bpp.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cpt.effective_start_date
and cpt.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between opt.effective_start_date
and opt.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between oipl.effective_start_date
and oipl.effective_end_date
and rownum = 1
and bpp.ptip_id is null
and bpp.cmbn_ptip_id is null;
select bpp.bnft_prvdr_pool_id,
bpp.cmbn_ptip_opt_id
from ben_bnft_prvdr_pool_f bpp,
ben_cmbn_ptip_opt_f cpt,
ben_optip_f otp,
ben_oipl_f oipl
where oipl.oipl_id = l_oipl_id
and otp.opt_id = oipl.opt_id
and otp.ptip_id = l_ptip_id
and otp.cmbn_ptip_opt_id = cpt.cmbn_ptip_opt_id
and bpp.cmbn_ptip_opt_id = cpt.cmbn_ptip_opt_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between bpp.effective_start_date
and bpp.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cpt.effective_start_date
and cpt.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between otp.effective_start_date
and otp.effective_end_date
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between oipl.effective_start_date
and oipl.effective_end_date
and rownum = 1
and bpp.ptip_id is null
and bpp.cmbn_ptip_id is null;
select bpp.bnft_prvdr_pool_id
from ben_bnft_prvdr_pool_f bpp
where bpp.oiplip_id = l_oiplip_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between bpp.effective_start_date
and bpp.effective_end_date
and rownum = 1
and bpp.plip_id is null
and bpp.cmbn_plip_id is null;
select yp.yr_perd_id
from ben_yr_perd yp,
ben_popl_yr_perd pop
where pop.pgm_id = p_pgm_id
and pop.yr_perd_id = yp.yr_perd_id
and nvl(p_lf_evt_ocrd_dt,p_effective_date)
between yp.start_date
and yp.end_date
and yp.business_group_id = p_business_group_id;
select 'x'
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.pgm_id = c_pgm_id
and epe.comp_lvl_cd = 'PGM'
and epe.bnft_prvdr_pool_id is not null ;
select 'x'
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.plip_id = c_plip_id
and epe.comp_lvl_cd = 'PLIP'
and epe.bnft_prvdr_pool_id is not null ;
select 'x'
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.cmbn_plip_id = c_cmbn_plip_id
and epe.comp_lvl_cd = 'CPLIP'
and epe.bnft_prvdr_pool_id is not null ;
select 'x'
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.ptip_id = c_ptip_id
and epe.comp_lvl_cd = 'PTIP'
and epe.bnft_prvdr_pool_id is not null ;
select 'x'
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.cmbn_ptip_id = c_cmbn_ptip_id
and epe.comp_lvl_cd = 'CPTIP'
and epe.bnft_prvdr_pool_id is not null ;
select 'x'
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.oiplip_id = c_oiplip_id
and epe.comp_lvl_cd = 'OIPLIP'
and epe.bnft_prvdr_pool_id is not null ;
select 'x'
from ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = p_per_in_ler_id
and epe.cmbn_ptip_opt_id = c_cmbn_ptip_opt_id
and epe.comp_lvl_cd = 'CPTIPOPT'
and epe.bnft_prvdr_pool_id is not null ;
l_wrote_cmbn.delete;
ben_manage_unres_life_events.update_elig_per_elctbl_choice
(p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_comp_lvl_cd => 'PGM',
p_pgm_id => l_epe_pgm.pgm_id,
p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
p_per_in_ler_id => p_per_in_ler_id,
p_yr_perd_id => l_yr_perd_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
p_program_update_date => sysdate,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date);
ben_manage_unres_life_events.update_elig_per_elctbl_choice
(p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_comp_lvl_cd => 'PLIP',
p_pgm_id => l_epe_plip.pgm_id,
p_plip_id => l_epe_plip.plip_id,
p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
p_per_in_ler_id => p_per_in_ler_id,
p_yr_perd_id => l_yr_perd_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
p_program_update_date => sysdate,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date);
l_wrote_cmbn.delete;
ben_manage_unres_life_events.update_elig_per_elctbl_choice
(p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_comp_lvl_cd => 'CPLIP',
p_pgm_id => l_epe_plip.pgm_id,
p_cmbn_plip_id => l_cmbn_plip_id,
p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
p_per_in_ler_id => p_per_in_ler_id,
p_yr_perd_id => l_yr_perd_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
p_program_update_date => sysdate,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date);
ben_manage_unres_life_events.update_elig_per_elctbl_choice
(p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_comp_lvl_cd => 'PTIP',
p_pgm_id => l_epe_ptip.pgm_id,
p_ptip_id => l_epe_ptip.ptip_id,
p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
p_per_in_ler_id => p_per_in_ler_id,
p_yr_perd_id => l_yr_perd_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
p_program_update_date => sysdate,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date);
l_wrote_cmbn.delete;
ben_manage_unres_life_events.update_elig_per_elctbl_choice
(p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_comp_lvl_cd => 'CPTIP',
p_pgm_id => l_epe_ptip.pgm_id,
p_cmbn_ptip_id => l_cmbn_ptip_id,
p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
p_per_in_ler_id => p_per_in_ler_id,
p_yr_perd_id => l_yr_perd_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
p_program_update_date => sysdate,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date);
l_wrote_cmbn.delete;
ben_manage_unres_life_events.update_elig_per_elctbl_choice
(p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_comp_lvl_cd => 'OIPLIP',
p_pgm_id => l_epe_oipl.pgm_id,
p_oiplip_id => l_epe_oipl.oiplip_id,
p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
p_per_in_ler_id => p_per_in_ler_id,
p_yr_perd_id => l_yr_perd_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
p_program_update_date => sysdate,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date);
l_wrote_cmbn.delete;
ben_manage_unres_life_events.update_elig_per_elctbl_choice
(p_elig_per_elctbl_chc_id => l_elig_per_elctbl_chc_id,
p_elctbl_flag => 'N',
p_comp_lvl_cd => 'CPTIPOPT',
p_pgm_id => l_epe_oipl.pgm_id,
p_cmbn_ptip_opt_id => l_cmbn_ptip_opt_id,
p_bnft_prvdr_pool_id => l_bnft_prvdr_pool_id,
p_per_in_ler_id => p_per_in_ler_id,
p_yr_perd_id => l_yr_perd_id,
p_business_group_id => p_business_group_id,
p_effective_date => p_effective_date);
p_program_update_date => sysdate,
p_object_version_number => l_object_version_number,
p_effective_date => p_effective_date);