[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
:= 'select count(*), sum(decode(vpf.rt_age_flag,''Y'',1,0)),
sum(decode(vpf.rt_los_flag,''Y'',1,0)),
sum(decode(vpf.rt_comp_lvl_flag,''Y'',1,0)),
sum(decode(vpf.rt_pct_fl_tm_flag,''Y'',1,0)),
sum(decode(vpf.rt_hrs_wkd_flag,''Y'',1,0)),
sum(decode(vpf.rt_cmbn_age_los_flag,''Y'',1,0))
from ben_vrbl_rt_prfl_f vpf,
ben_acty_vrbl_rt_f avr,
ben_acty_base_rt_f abr
where abr.{OBJECT} = :cobj_id
and abr.business_group_id = :business_group_id
and :abr_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.business_group_id = abr.business_group_id
and :avr_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and vpf.business_group_id = avr.business_group_id
and :vpf_effective_date
between vpf.effective_start_date
and vpf.effective_end_date';
:= 'select sum(decode(vpf.rt_age_flag,''Y'',1,0)),
sum(decode(vpf.rt_los_flag,''Y'',1,0)),
sum(decode(vpf.rt_comp_lvl_flag,''Y'',1,0)),
sum(decode(vpf.rt_pct_fl_tm_flag,''Y'',1,0)),
sum(decode(vpf.rt_hrs_wkd_flag,''Y'',1,0)),
sum(decode(vpf.rt_cmbn_age_los_flag,''Y'',1,0))
from ben_vrbl_rt_prfl_f vpf,
ben_actl_prem_vrbl_rt_f apv,
ben_actl_prem_f apr
where apr.{OBJECT} = :cobj_id
and apr.business_group_id = :business_group_id
and :apr_effective_date
between apr.effective_start_date
and apr.effective_end_date
and apr.actl_prem_id = apv.actl_prem_id
and apv.business_group_id = apr.business_group_id
and :apv_effective_date
between apv.effective_start_date
and apv.effective_end_date
and apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and vpf.business_group_id = apr.business_group_id
and :vpf_effective_date
between vpf.effective_start_date
and vpf.effective_end_date';
:= 'select sum(decode(vpf.rt_age_flag,''Y'',1,0)),
sum(decode(vpf.rt_los_flag,''Y'',1,0)),
sum(decode(vpf.rt_comp_lvl_flag,''Y'',1,0)),
sum(decode(vpf.rt_pct_fl_tm_flag,''Y'',1,0)),
sum(decode(vpf.rt_hrs_wkd_flag,''Y'',1,0)),
sum(decode(vpf.rt_cmbn_age_los_flag,''Y'',1,0))
from ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f bvr,
ben_cvg_amt_calc_mthd_f ccm
where ccm.{OBJECT} = :cobj_id
and ccm.business_group_id = :business_group_id
and :ccm_effective_date
between ccm.effective_start_date
and ccm.effective_end_date
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.business_group_id = ccm.business_group_id
and :bvr_effective_date
between bvr.effective_start_date
and bvr.effective_end_date
and bvr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and vpf.business_group_id = bvr.business_group_id
and :vpf_effective_date
between vpf.effective_start_date
and vpf.effective_end_date';
:= 'select opt_id
from ben_oipl_f otp
where otp.oipl_id = :otp_oipl_id
and :otp_effective_date
between otp.effective_start_date
and otp.effective_end_date
and otp.business_group_id = :business_group_id' ;
SELECT hr.meaning
,hr.lookup_code
FROM hr_lookups hr
WHERE hr.lookup_type = 'BEN_COMP_OBJ'
AND hr.enabled_flag = 'Y'
AND p_effective_date BETWEEN NVL(hr.start_date_active
,p_effective_date)
AND NVL(hr.end_date_active, p_effective_date)
AND hr.lookup_code IN ('PLIP', 'PGM', 'PLTYP', 'PTIP', 'PL', 'OIPL');
select ben_comp_obj_cache_row_s.nextval
from sys.dual;
INSERT INTO ben_comp_obj_cache
(
comp_obj_cache_id
,effective_date
,business_group_id
,timestamp
,mode_cd
,pgm_id
,pl_id
,no_programs
,no_plans
,pl_typ_id)
VALUES(
ben_comp_obj_cache_s.nextval
,p_effective_date
,p_business_group_id
,SYSDATE
,p_mode
,p_pgm_id
,p_pl_id
,p_no_programs
,p_no_plans
,p_pl_typ_id
)
RETURNING comp_obj_cache_id
INTO l_comp_obj_cache_id;
select count(*) into l_count
from ben_comp_obj_cache
where business_group_id = p_business_group_id
and effective_date = p_effective_date;
INSERT INTO ben_comp_obj_cache_row
(comp_obj_cache_row_id
,comp_obj_cache_id
,pl_id
,pgm_id
,oipl_id
,ptip_id
,plip_id
,pl_nip
,elig_tran_state
,trk_inelig_per_flag
,par_pgm_id
,par_ptip_id
,par_plip_id
,par_pl_id
,par_opt_id
,flag_bit_val
,oiplip_flag_bit_val
,oiplip_id
)
VALUES
(l_comp_obj_cache_row_id_va(i)
,l_comp_obj_cache_id_va(i)
,l_pl_id_va(i)
,l_pgm_id_va(i)
,l_oipl_id_va(i)
,l_ptip_id_va(i)
,l_plip_id_va(i)
,l_pl_nip_va(i)
,l_elig_tran_state_va(i)
,l_trk_inelig_per_flag_va(i)
,l_par_pgm_id_va(i)
,l_par_ptip_id_va(i)
,l_par_plip_id_va(i)
,l_par_pl_id_va(i)
,l_par_opt_id_va(i)
,l_flag_bit_val_va(i)
,l_oiplip_flag_bit_val_va(i)
,l_oiplip_id_va(i)
);
select coc.comp_obj_cache_id
from ben_comp_obj_cache coc
where coc.business_group_id = c_bgp_id
and coc.effective_date = c_eff_date
and coc.mode_cd = p_mode; --bug 7700173
delete from ben_comp_obj_cache_row ccr
where ccr.comp_obj_cache_id = l_coc_id_va(ccrelenum);
DELETE
FROM ben_comp_obj_cache_row cjr
WHERE EXISTS(SELECT NULL
FROM ben_comp_obj_cache cjc
WHERE cjc.comp_obj_cache_id = cjr.comp_obj_cache_id
AND cjc.business_group_id = p_business_group_id
AND cjc.effective_date = p_effective_date
);
DELETE
FROM ben_comp_obj_cache
WHERE business_group_id = p_business_group_id
AND effective_date = p_effective_date
AND mode_cd = p_mode ; --bug 7700173
DELETE
FROM ben_comp_obj_cache_row cjr;
DELETE
FROM ben_comp_obj_cache;
,p_comp_selection_rule_id IN NUMBER DEFAULT NULL
,p_effective_date IN DATE
,p_pgm_id IN NUMBER DEFAULT NULL
,p_business_group_id IN NUMBER DEFAULT NULL
,p_pl_id IN NUMBER DEFAULT NULL
,p_oipl_id IN NUMBER DEFAULT NULL
--
-- PB : 5422 :
-- Pass on the asnd_lf_evt_dt
--
,p_asnd_lf_evt_dt IN DATE DEFAULT NULL
-- ,p_popl_enrt_typ_cycl_id IN NUMBER DEFAULT NULL
,p_no_programs IN VARCHAR2 DEFAULT 'N'
,p_no_plans IN VARCHAR2 DEFAULT 'N'
,p_rptg_grp_id IN NUMBER DEFAULT NULL
,p_pl_typ_id IN NUMBER DEFAULT NULL
,p_opt_id IN NUMBER DEFAULT NULL
,p_eligy_prfl_id IN NUMBER DEFAULT NULL
,p_vrbl_rt_prfl_id IN NUMBER DEFAULT NULL
,p_thread_id IN NUMBER DEFAULT NULL
,p_mode IN VARCHAR2
--
-- PB : Helathnet change
--
,p_person_id in number default null
,p_lmt_prpnip_by_org_flag in varchar2 default 'N') IS
--
l_package VARCHAR2(80)
:= g_package || '.build_comp_object_list';
select pgm.pgm_id,
pgm.drvbl_fctr_prtn_elig_flag,
pgm.drvbl_fctr_apls_rts_flag,
pgm.trk_inelig_per_flag
FROM -- ben_popl_yr_perd cpy
ben_pgm_f pgm
-- ,ben_yr_perd yrp
WHERE pgm.business_group_id = p_business_group_id
AND pgm.pgm_id = NVL(p_pgm_id, pgm.pgm_id)
AND pgm.pgm_stat_cd = 'A'
AND (
pgm.pgm_typ_cd NOT IN ('COBRANFLX', 'COBRAFLX')
OR p_mode NOT IN ('L', 'U'))
-- GRADE/STEP
AND ( (p_mode in('T', 'G') and pgm.pgm_typ_cd = 'GSP') OR
(p_mode <> 'G' and pgm.pgm_typ_cd <> 'GSP')
)
AND p_effective_date BETWEEN pgm.effective_start_date
AND pgm.effective_end_date
AND p_mode not in ('D','I')
AND (p_mode = 'G' or
exists (select null
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.pgm_id = pgm.pgm_id
AND cpy.yr_perd_id = yrp.yr_perd_id
AND cpy.business_group_id = pgm.business_group_id
AND p_effective_date BETWEEN yrp.start_date AND yrp.end_date))
AND pgm.alws_unrstrctd_enrt_flag =
DECODE(p_mode, 'U', 'Y', pgm.alws_unrstrctd_enrt_flag)
/* Make sure that program being linked to covers all the
plan types that may or may not have been stated by the
user. (PTIP)*/
AND (
EXISTS
(SELECT NULL
FROM ben_ptip_f ctp
WHERE ctp.pgm_id = pgm.pgm_id
AND ctp.pl_typ_id = NVL(p_pl_typ_id, ctp.pl_typ_id)
AND ctp.business_group_id = pgm.business_group_id
AND p_effective_date BETWEEN ctp.effective_start_date
AND ctp.effective_end_date
/* Make sure that the plan type in program is of the
variable rate profile that has been specified by
the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_acty_base_rt_f abr
,ben_acty_vrbl_rt_f avr
,ben_vrbl_rt_prfl_f vpf
WHERE abr.ptip_id = ctp.ptip_id
AND abr.business_group_id =
ctp.business_group_id
AND p_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
AND avr.acty_base_rt_id =
abr.acty_base_rt_id
AND avr.business_group_id =
abr.business_group_id
AND p_effective_date BETWEEN avr.effective_start_date
AND avr.effective_end_date
AND vpf.vrbl_rt_prfl_id =
avr.vrbl_rt_prfl_id
AND vpf.business_group_id =
avr.business_group_id
AND vpf.vrbl_rt_prfl_id =
p_vrbl_rt_prfl_id
AND p_effective_date BETWEEN vpf.effective_start_date
AND vpf.effective_end_date)
OR p_vrbl_rt_prfl_id IS NULL))
OR p_pl_typ_id IS NULL)
/* Make sure that program being linked to covers all the
plans that may or may not have been stated by the
user. (PLIP)*/
AND (
EXISTS
(SELECT NULL
FROM ben_plip_f cpp
WHERE cpp.pgm_id = pgm.pgm_id
AND cpp.pl_id = NVL(p_pl_id, cpp.pl_id)
AND cpp.business_group_id = pgm.business_group_id
AND cpp.plip_stat_cd = 'A'
AND p_effective_date BETWEEN cpp.effective_start_date
AND cpp.effective_end_date
/* Make sure that plan being linked to is of the
eligibility profile that has been specified by
the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_prtn_elig_f epa2
,ben_prtn_elig_prfl_f cep
,ben_eligy_prfl_f elp
WHERE epa2.pl_id = cpp.pl_id
AND epa2.business_group_id =
cpp.business_group_id
AND p_effective_date BETWEEN epa2.effective_start_date
AND epa2.effective_end_date
AND cep.prtn_elig_id =
epa2.prtn_elig_id
AND cep.business_group_id =
epa2.business_group_id
AND p_effective_date BETWEEN cep.effective_start_date
AND cep.effective_end_date
AND elp.eligy_prfl_id =
cep.eligy_prfl_id
AND elp.business_group_id =
cep.business_group_id
AND elp.eligy_prfl_id =
p_eligy_prfl_id
AND p_effective_date BETWEEN elp.effective_start_date
AND elp.effective_end_date)
OR p_eligy_prfl_id IS NULL)
/* Make sure that plan being linked to is of the
reporting group that has been specified by
the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_rptg_grp bnr
,ben_popl_rptg_grp_f rgr
WHERE bnr.rptg_grp_id = p_rptg_grp_id
AND nvl(bnr.business_group_id,cpp.business_group_id) =
cpp.business_group_id
AND rgr.rptg_grp_id = bnr.rptg_grp_id
AND p_effective_date BETWEEN rgr.effective_start_date
AND rgr.effective_end_date
AND rgr.business_group_id =
nvl(bnr.business_group_id,rgr.business_group_id)
AND rgr.pl_id = cpp.pl_id)
OR p_rptg_grp_id IS NULL)
/* Make sure that plan being linked to is of the
variable rate profile that has been specified
by the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_acty_base_rt_f abr
,ben_acty_vrbl_rt_f avr
,ben_vrbl_rt_prfl_f vpf
WHERE abr.pl_id = cpp.pl_id
AND abr.business_group_id =
pgm.business_group_id
AND p_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
AND avr.acty_base_rt_id =
abr.acty_base_rt_id
AND avr.business_group_id =
abr.business_group_id
AND p_effective_date BETWEEN avr.effective_start_date
AND avr.effective_end_date
AND vpf.vrbl_rt_prfl_id =
avr.vrbl_rt_prfl_id
AND vpf.business_group_id =
avr.business_group_id
AND vpf.vrbl_rt_prfl_id =
p_vrbl_rt_prfl_id
AND p_effective_date BETWEEN vpf.effective_start_date
AND vpf.effective_end_date)
OR p_vrbl_rt_prfl_id IS NULL))
OR p_pl_id IS NULL)
/* Make sure that program being linked to covers all the
options that may or may not have been stated by the
user. (OIPL) */
AND (
EXISTS
(SELECT NULL
FROM ben_oipl_f cop, ben_opt_f opt
WHERE cop.pl_id = NVL(p_pl_id, cop.pl_id)
AND cop.opt_id = p_opt_id
AND cop.oipl_stat_cd = 'A'
AND cop.business_group_id = pgm.business_group_id
AND p_effective_date BETWEEN cop.effective_start_date
AND cop.effective_end_date
AND opt.opt_id = cop.opt_id
AND opt.business_group_id = cop.business_group_id
AND p_effective_date BETWEEN opt.effective_start_date
AND opt.effective_end_date
/* Make sure that the option in the plan
being linked to is of the eligibility
profile that has been specified by the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_prtn_elig_f epa2
,ben_prtn_elig_prfl_f cep
,ben_eligy_prfl_f elp
WHERE epa2.oipl_id = cop.oipl_id
AND epa2.business_group_id =
cop.business_group_id
AND p_effective_date BETWEEN epa2.effective_start_date
AND epa2.effective_end_date
AND cep.prtn_elig_id =
epa2.prtn_elig_id
AND cep.business_group_id =
epa2.business_group_id
AND p_effective_date BETWEEN cep.effective_start_date
AND cep.effective_end_date
AND elp.eligy_prfl_id =
cep.eligy_prfl_id
AND elp.business_group_id =
cep.business_group_id
AND elp.eligy_prfl_id =
p_eligy_prfl_id
AND p_effective_date BETWEEN elp.effective_start_date
AND elp.effective_end_date)
OR p_eligy_prfl_id IS NULL)
/* Make sure that the options in plan being
linked to is of the variable rate profile
that has been specified by the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_acty_base_rt_f abr
,ben_acty_vrbl_rt_f avr
,ben_vrbl_rt_prfl_f vpf
WHERE abr.oipl_id = cop.oipl_id
AND abr.business_group_id =
cop.business_group_id
AND p_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
AND avr.acty_base_rt_id =
abr.acty_base_rt_id
AND avr.business_group_id =
abr.business_group_id
AND p_effective_date BETWEEN avr.effective_start_date
AND avr.effective_end_date
AND vpf.vrbl_rt_prfl_id =
avr.vrbl_rt_prfl_id
AND vpf.business_group_id =
avr.business_group_id
AND vpf.vrbl_rt_prfl_id =
p_vrbl_rt_prfl_id
AND p_effective_date BETWEEN vpf.effective_start_date
AND vpf.effective_end_date)
OR p_vrbl_rt_prfl_id IS NULL))
OR p_opt_id IS NULL)
/* Make sure that program being linked to is of the
variable rate profile that has been specified by the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_acty_base_rt_f abr
,ben_acty_vrbl_rt_f avr
,ben_vrbl_rt_prfl_f vpf
WHERE abr.pgm_id = pgm.pgm_id
AND abr.business_group_id = pgm.business_group_id
AND p_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
AND avr.acty_base_rt_id = abr.acty_base_rt_id
AND avr.business_group_id = abr.business_group_id
AND p_effective_date BETWEEN avr.effective_start_date
AND avr.effective_end_date
AND vpf.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
AND vpf.business_group_id = avr.business_group_id
AND vpf.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
AND p_effective_date BETWEEN vpf.effective_start_date
AND vpf.effective_end_date)
OR p_vrbl_rt_prfl_id IS NULL)
/* Make sure that program being linked to is of the
reporting group that has been specified by the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_rptg_grp bnr, ben_popl_rptg_grp_f rgr
WHERE bnr.rptg_grp_id = p_rptg_grp_id
AND nvl(bnr.business_group_id,pgm.business_group_id)
= pgm.business_group_id
AND rgr.rptg_grp_id = bnr.rptg_grp_id
AND p_effective_date BETWEEN rgr.effective_start_date
AND rgr.effective_end_date
AND rgr.business_group_id =
nvl(bnr.business_group_id,rgr.business_group_id)
AND rgr.pgm_id = pgm.pgm_id)
OR p_rptg_grp_id IS NULL)
/* Make sure that program being linked to is of the
eligibility profile that has been specified by the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_prtn_elig_f epa2
,ben_prtn_elig_prfl_f cep
,ben_eligy_prfl_f elp
WHERE epa2.pgm_id = pgm.pgm_id
AND epa2.business_group_id = pgm.business_group_id
AND p_effective_date BETWEEN epa2.effective_start_date
AND epa2.effective_end_date
AND cep.prtn_elig_id = epa2.prtn_elig_id
AND cep.business_group_id = epa2.business_group_id
AND p_effective_date BETWEEN cep.effective_start_date
AND cep.effective_end_date
AND elp.eligy_prfl_id = cep.eligy_prfl_id
AND elp.business_group_id = cep.business_group_id
AND elp.eligy_prfl_id = p_eligy_prfl_id
AND p_effective_date BETWEEN elp.effective_start_date
AND elp.effective_end_date)
OR p_eligy_prfl_id IS NULL)
/* Make sure that program being linked to is of the
enrollment type cycle that has been specified by the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_popl_enrt_typ_cycl_f pet,
ben_enrt_perd enp
WHERE pet.pgm_id = pgm.pgm_id
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.popl_enrt_typ_cycl_id =
enp.popl_enrt_typ_cycl_id
AND enp.asnd_lf_evt_dt = p_asnd_lf_evt_dt
/* PB : 5422 AND enp.strt_dt =
(SELECT enp1.strt_dt
FROM ben_enrt_perd enp1
WHERE enp1.enrt_perd_id =
p_popl_enrt_typ_cycl_id) */
AND enp.business_group_id = pet.business_group_id)
OR p_asnd_lf_evt_dt IS NULL)
/* Make sure that program being linked to org id of the person
if the program selection is limited based on person's org id. */
AND (
EXISTS
(SELECT NULL
FROM ben_popl_org_f cpo,
ben_popl_org_role_f cpr
WHERE cpo.pgm_id = pgm.pgm_id
AND p_effective_date BETWEEN cpo.effective_start_date
AND cpo.effective_end_date
AND cpo.popl_org_id = cpr.popl_org_id
AND p_effective_date BETWEEN cpr.effective_start_date
AND cpr.effective_end_date
AND cpo.business_group_id = cpr.business_group_id
AND cpr.org_role_typ_cd = 'POPLOWNR'
AND cpo.organization_id = l_per_org_id)
OR p_lmt_prpnip_by_org_flag = 'N'
OR l_per_org_id IS NULL)
-- PB 5422 OR p_popl_enrt_typ_cycl_id IS NULL)
ORDER BY pgm.name;
select pgm.pgm_id,
pgm.drvbl_fctr_prtn_elig_flag,
pgm.drvbl_fctr_apls_rts_flag,
pgm.trk_inelig_per_flag
FROM ben_popl_yr_perd cpy
,ben_pgm_f pgm
,ben_yr_perd yrp
WHERE pgm.business_group_id = p_business_group_id
AND pgm.pgm_stat_cd = 'A'
AND pgm.pgm_typ_cd LIKE 'COBRA%'
AND p_effective_date BETWEEN pgm.effective_start_date
AND pgm.effective_end_date
AND cpy.pgm_id = pgm.pgm_id
AND cpy.yr_perd_id = yrp.yr_perd_id
AND cpy.business_group_id = pgm.business_group_id
AND p_effective_date BETWEEN yrp.start_date AND yrp.end_date
AND pgm.alws_unrstrctd_enrt_flag =
DECODE(p_mode, 'U', 'Y', pgm.alws_unrstrctd_enrt_flag)
/* Make sure that program being linked to org id of the person
if the program selection is limited based on person's org id. */
AND (
EXISTS
(SELECT NULL
FROM ben_popl_org_f cpo,
ben_popl_org_role_f cpr
WHERE cpo.pgm_id = pgm.pgm_id
AND p_effective_date BETWEEN cpo.effective_start_date
AND cpo.effective_end_date
AND cpo.popl_org_id = cpr.popl_org_id
AND p_effective_date BETWEEN cpr.effective_start_date
AND cpr.effective_end_date
AND cpo.business_group_id = cpr.business_group_id
AND cpr.org_role_typ_cd = 'POPLOWNR'
AND cpo.organization_id = l_per_org_id)
OR p_lmt_prpnip_by_org_flag = 'N'
OR l_per_org_id IS NULL)
ORDER BY pgm.name;
select pln.pl_id,
pln.pl_typ_id,
ptp.opt_typ_cd,
pln.drvbl_fctr_prtn_elig_flag,
pln.drvbl_fctr_apls_rts_flag,
pln.trk_inelig_per_flag
FROM ben_pl_f pln,
ben_pl_typ_f ptp
-- ben_yr_perd yrp,
-- ben_popl_yr_perd cpy
WHERE pln.business_group_id = p_business_group_id
AND p_effective_date BETWEEN pln.effective_start_date
AND pln.effective_end_date
/* Bug No 4402873 Added condition to retrieve plans with code as
'May Not be in Program' so that the plans with code as
'Must be in Program' and not included in the program
shall not get picked up*/
and pln.pl_cd = 'MYNTBPGM'
and pln.pl_typ_id = ptp.pl_typ_id
and p_effective_date
between ptp.effective_start_date and ptp.effective_end_date
/* Make sure that the plan is not in the plip table.
or may not have been stated by the user. */
AND (p_mode IN ('P','G','D') or -- ICM
exists (select null
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.pl_id = pln.pl_id
AND cpy.yr_perd_id = yrp.yr_perd_id
AND cpy.business_group_id = pln.business_group_id
AND p_effective_date BETWEEN yrp.start_date AND yrp.end_date))
AND pln.pl_stat_cd = 'A'
AND pln.alws_unrstrctd_enrt_flag =
DECODE(p_mode, 'U', 'Y','D','Y', pln.alws_unrstrctd_enrt_flag) -- ICM
-- CWB changes
-- ABSENCES : pickup only absence plans
AND ((p_mode = 'W' and ptp.opt_typ_cd = 'CWB') or
(p_mode = 'M' and ptp.opt_typ_cd = 'ABS') or
(p_mode = 'P' and ptp.opt_typ_cd = 'PERACT') or
(p_mode = 'I' and ptp.opt_typ_cd = 'COMP') or -- iREC changes
(p_mode = 'D' and ptp.opt_typ_cd = 'ICM') or
(p_mode not in ('W','M', 'P','D') and ptp.opt_typ_cd not in ('CWB','ABS', 'PERACT','ICM')) -- ICM
)
AND ptp.opt_typ_cd <> 'GDRLDR'
AND NOT EXISTS(SELECT NULL
FROM ben_plip_f cpp
WHERE cpp.pl_id = pln.pl_id)
/* We only want to report on these plans when pgm_id is null */
AND p_pgm_id IS NULL
AND pln.pl_id = NVL(p_pl_id, pln.pl_id)
/* Make sure that plan being linked to covers all the options that may
or may not have been stated by the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_oipl_f cop
WHERE cop.opt_id = p_opt_id
AND cop.pl_id = pln.pl_id
AND cop.oipl_stat_cd = 'A'
AND cop.business_group_id = pln.business_group_id
AND p_effective_date BETWEEN cop.effective_start_date
AND cop.effective_end_date)
OR p_opt_id IS NULL)
/* Make sure that plan being linked to is in the correct benefit group */
AND (
EXISTS
(SELECT NULL
FROM ben_rptg_grp bnr, ben_popl_rptg_grp_f rgr
WHERE bnr.rptg_grp_id = nvl(p_rptg_grp_id, bnr.rptg_grp_id) --irec
AND nvl(bnr.business_group_id,pln.business_group_id)
= pln.business_group_id
AND rgr.rptg_grp_id = bnr.rptg_grp_id
AND p_effective_date BETWEEN rgr.effective_start_date
AND rgr.effective_end_date
AND rgr.business_group_id =
nvl(bnr.business_group_id,rgr.business_group_id)
AND rgr.pl_id = pln.pl_id
AND nvl(bnr.rptg_prps_cd, 'X') = decode (p_mode, 'I', 'IREC',nvl(bnr.rptg_prps_cd, 'X')) -- irec
)
OR
(p_rptg_grp_id IS NULL
and p_mode <>'I' -- iRec
)
)
/* Make sure that plan being linked to is of the variable rate profile
that has been specified by the user. */
AND (
EXISTS
(SELECT NULL
FROM ben_acty_base_rt_f abr
,ben_acty_vrbl_rt_f avr
,ben_vrbl_rt_prfl_f vpf
WHERE abr.pl_id = pln.pl_id
AND abr.business_group_id = pln.business_group_id
AND p_effective_date BETWEEN abr.effective_start_date
AND abr.effective_end_date
AND avr.acty_base_rt_id = abr.acty_base_rt_id
AND avr.business_group_id = abr.business_group_id
AND p_effective_date BETWEEN avr.effective_start_date
AND avr.effective_end_date
AND vpf.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
AND vpf.business_group_id = avr.business_group_id
AND vpf.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
AND p_effective_date BETWEEN vpf.effective_start_date
AND vpf.effective_end_date)
OR p_vrbl_rt_prfl_id IS NULL)
AND (
EXISTS
(SELECT NULL
FROM ben_prtn_elig_f epa2
,ben_prtn_elig_prfl_f cep
,ben_eligy_prfl_f elp
WHERE epa2.pl_id = pln.pl_id
AND epa2.business_group_id = pln.business_group_id
AND p_effective_date BETWEEN epa2.effective_start_date
AND epa2.effective_end_date
AND cep.prtn_elig_id = epa2.prtn_elig_id
AND cep.business_group_id = epa2.business_group_id
AND p_effective_date BETWEEN cep.effective_start_date
AND cep.effective_end_date
AND elp.eligy_prfl_id = cep.eligy_prfl_id
AND elp.business_group_id = cep.business_group_id
AND elp.eligy_prfl_id = p_eligy_prfl_id
AND p_effective_date BETWEEN elp.effective_start_date
AND elp.effective_end_date)
OR p_eligy_prfl_id IS NULL)
AND (
EXISTS
(SELECT NULL
FROM ben_popl_enrt_typ_cycl_f pet, ben_enrt_perd enp
WHERE pet.pl_id = pln.pl_id
AND p_effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.popl_enrt_typ_cycl_id =
enp.popl_enrt_typ_cycl_id
AND enp.asnd_lf_evt_dt = p_asnd_lf_evt_dt
AND enp.business_group_id = pet.business_group_id)
OR p_asnd_lf_evt_dt IS NULL)
AND (
EXISTS
(SELECT NULL
FROM ben_popl_org_f cpo,
ben_popl_org_role_f cpr
WHERE cpo.pl_id = pln.pl_id
AND p_effective_date BETWEEN cpo.effective_start_date
AND cpo.effective_end_date
AND cpo.popl_org_id = cpr.popl_org_id
AND p_effective_date BETWEEN cpr.effective_start_date
AND cpr.effective_end_date
AND cpo.business_group_id = cpr.business_group_id
AND cpr.org_role_typ_cd = 'POPLOWNR'
AND cpo.organization_id = l_per_org_id)
OR p_lmt_prpnip_by_org_flag = 'N'
OR l_per_org_id IS NULL)
ORDER BY nvl(pln.ordr_num,999999999999999),pln.name;
select pln.pl_id,
pln.pl_typ_id,
ptp.opt_typ_cd,
pln.drvbl_fctr_prtn_elig_flag,
pln.drvbl_fctr_apls_rts_flag,
pln.trk_inelig_per_flag
FROM ben_pl_f pln,
ben_pl_typ_f ptp,
-- ben_popl_yr_perd cpy,
-- ben_yr_perd yrp,
ben_plip_f plp,
ben_ptip_f ctp
WHERE pln.business_group_id = p_business_group_id
AND pln.pl_id = plp.pl_id
AND p_effective_date BETWEEN pln.effective_start_date
AND pln.effective_end_date
and pln.pl_typ_id = ptp.pl_typ_id
and p_effective_date
between ptp.effective_start_date and ptp.effective_end_date
AND plp.pgm_id = l_pgm_id
AND plp.business_group_id = pln.business_group_id
AND plp.plip_stat_cd = 'A'
AND pln.pl_stat_cd = 'A'
AND plp.alws_unrstrctd_enrt_flag =
DECODE(p_mode, 'U', 'Y', plp.alws_unrstrctd_enrt_flag)
AND p_effective_date BETWEEN plp.effective_start_date
AND plp.effective_end_date
AND (p_mode = 'G' or
exists (select null
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.pl_id = pln.pl_id
AND cpy.yr_perd_id = yrp.yr_perd_id
AND cpy.business_group_id = pln.business_group_id
AND p_effective_date BETWEEN yrp.start_date AND yrp.end_date))
AND ctp.pl_typ_id = pln.pl_typ_id
AND ctp.pgm_id = l_pgm_id
AND ctp.business_group_id = pln.business_group_id
AND ctp.ptip_stat_cd = 'A'
AND p_effective_date BETWEEN ctp.effective_start_date
AND ctp.effective_end_date
ORDER BY pln.name;
select cop.oipl_id,
cop.opt_id,
cop.drvbl_fctr_prtn_elig_flag,
cop.drvbl_fctr_apls_rts_flag,
cop.trk_inelig_per_flag
FROM ben_oipl_f cop
,ben_opt_f opt
-- ,ben_popl_yr_perd cpy
-- ,ben_yr_perd yrp
,ben_pl_f pln
WHERE cop.business_group_id = p_business_group_id
AND p_effective_date BETWEEN cop.effective_start_date
AND cop.effective_end_date
AND cop.pl_id = pln.pl_id
AND cop.oipl_stat_cd = 'A'
AND pln.pl_id = l_pl_id
AND pln.business_group_id = cop.business_group_id
AND pln.pl_stat_cd = 'A'
AND p_effective_date BETWEEN pln.effective_start_date
AND pln.effective_end_date
AND cop.opt_id = opt.opt_id
AND opt.business_group_id = cop.business_group_id
AND p_effective_date BETWEEN opt.effective_start_date
AND opt.effective_end_date
AND (p_mode in ('G','D') or
exists (select null
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.pl_id = cop.pl_id
AND cpy.yr_perd_id = yrp.yr_perd_id
AND cpy.business_group_id = cop.business_group_id
AND p_effective_date BETWEEN yrp.start_date AND yrp.end_date))
ORDER BY cop.ordr_num;
SELECT cpp.*
FROM ben_plip_f cpp
WHERE cpp.business_group_id = p_business_group_id
AND cpp.pl_id = l_pl_id
AND cpp.pgm_id = l_pgm_id
AND cpp.plip_stat_cd = 'A'
AND p_effective_date BETWEEN cpp.effective_start_date
AND cpp.effective_end_date;
SELECT ctp.*
FROM ben_ptip_f ctp
WHERE ctp.business_group_id = p_business_group_id
AND ctp.pgm_id = l_pgm_id
AND ctp.pl_typ_id = l_pln.pl_typ_id
AND ctp.ptip_stat_cd = 'A'
AND p_effective_date BETWEEN ctp.effective_start_date
AND ctp.effective_end_date;
SELECT opp.*
FROM ben_oiplip_f opp
WHERE opp.business_group_id = p_business_group_id
AND opp.plip_id = l_plip.plip_id
AND opp.oipl_id = l_cop.oipl_id
AND p_effective_date BETWEEN opp.effective_start_date
AND opp.effective_end_date;
SELECT comp_obj_cache_id
,timestamp
,mode_cd
,pgm_id
,pl_id
,no_programs
,no_plans
,pl_typ_id
FROM ben_comp_obj_cache
WHERE business_group_id = c_business_group_id
AND effective_date = c_effective_date
AND mode_cd = p_mode; --bug 7700173
SELECT NULL
FROM ben_pgm_f
WHERE c_timestamp < last_update_date;
select distinct alws_unrstrctd_enrt_flag
from ben_pgm_f
where pgm_id in ( SELECT distinct bcocr.pgm_id
FROM ben_comp_obj_cache_row bcocr
WHERE bcocr.comp_obj_cache_id = l_comp_obj_cache_id )
order by 1;
select distinct alws_unrstrctd_enrt_flag
from ben_pl_f
where pl_id in ( SELECT distinct bcocr.pl_id
FROM ben_comp_obj_cache_row bcocr
WHERE bcocr.comp_obj_cache_id = l_comp_obj_cache_id
and bcocr.pl_nip = 'Y' )
order by 1;
ben_manage_life_events.g_cache_proc_object.delete;
' select 1 ' || ' from sys.dual' || ' where exists(select null' ||
' from ' ||
l_tabname_set(tabele_num) ||
' where :timestamp < last_update_date)';
,p_comp_selection_rule_id => p_comp_selection_rule_id
,p_effective_date => p_effective_date
);
select pgm.drvbl_fctr_prtn_elig_flag, pgm.drvbl_fctr_apls_rts_flag, pgm.trk_inelig_per_flag
from ben_pgm_f pgm
where pgm.pgm_id = cv_pgm_id
and pgm.pgm_stat_cd = 'A'
and p_effective_date between pgm.effective_start_date and pgm.effective_end_date;
SELECT ctp.drvbl_fctr_prtn_elig_flag, ctp.drvbl_fctr_apls_rts_flag, ctp.trk_inelig_per_flag
FROM ben_ptip_f ctp
WHERE ctp.ptip_id = cv_ptip_id
AND ctp.ptip_stat_cd = 'A'
AND p_effective_date BETWEEN ctp.effective_start_date AND ctp.effective_end_date;
SELECT pln.drvbl_fctr_prtn_elig_flag, pln.drvbl_fctr_apls_rts_flag, pln.trk_inelig_per_flag
FROM ben_pl_f pln
WHERE pln.pl_id = cv_pl_id
AND pln.pl_stat_cd = 'A'
AND p_effective_date BETWEEN pln.effective_start_date AND pln.effective_end_date;
SELECT cpp.drvbl_fctr_prtn_elig_flag, cpp.drvbl_fctr_apls_rts_flag, cpp.trk_inelig_per_flag
FROM ben_plip_f cpp
WHERE cpp.plip_id = cv_plip_id
AND cpp.plip_stat_cd = 'A'
AND p_effective_date BETWEEN cpp.effective_start_date AND cpp.effective_end_date;
SELECT cop.drvbl_fctr_prtn_elig_flag, cop.drvbl_fctr_apls_rts_flag, cop.trk_inelig_per_flag
FROM ben_oipl_f cop
WHERE cop.oipl_id = cv_oipl_id
AND cop.oipl_stat_cd = 'A'
AND p_effective_date BETWEEN cop.effective_start_date AND cop.effective_end_date;
ben_manage_life_events.g_cache_proc_object.delete;