The following lines contain the word 'select', 'insert', 'update' or 'delete':
08-Jun-02 pabodla 115.41 Do not select the contingent worker
assignment when assignment data is
fetched.
04-Sep-02 kmahendr 115.42 Added new acty_ref_perd_cd - PHR.
28-Oct-02 shdas 115.43 bug fix 2644319 -- initialize l_coverage_value before
everything.
13-Nov-2002 vsethi 115.44 Bug 1210355, if variable rate is calculated, store
mlt_code in g_vrbl_mlt_code. The rates mlt_cd should
be changed to mlt_cd of variable profile.
23-Dec-2002 rpgupta 115.45 Nocopy changes
09-Apr-2004 pbodla 115.46 FONM : Use rt or cvg start dates for
processing.
13-Aug-2004 tjesumic 115.47 FONM : dates are passed as param
30-dec-2004 nhunur 115.48 4031733 - No need to open cursor c_state.
28-Jun-2005 kmahendr 115.49 Bug#4422269 - nvl used to get default
value of enrt bnft
03-Oct-05 ssarkar 115.50 4644867 - Added order by clause to cursoe c_asg to Query 'E' assignment first
and then others .
07-oct-05 nhunur 115.51 4657978 - added support for ben_actl_prem_vrbl_rt_rl_f.
02-Feb-06 stee 115.52 Bug 4873847. CWB: If treatment code is RPLC,
calculate the dflt_val, elcn_mn_val and elcn_mx_val if
enter val at enrollment and mlt_cd is multiple of
compensation. Also round the values if rounding rule
or code is not null.
10-Mar-06 swjain 115.54 In cursor c_asg (procedure main), added condition to
fetch active assignments only
10-Apr-06 swjain 115.55 Updated cursor c_asg (procedure main)
09-Aug-06 maagrawa 115.56 5371364.Copied fix from benactbr
16-Jul-07 swjain 115.57 6219465 Updated the effective_date in benutils.formula call
so that rule effective the latest coverage date should get
picked up for imputed income calculations
14-Sep-07 rtagarra 115.58 Bug 6399423 removed the outer join in the cursor c_asg
12-May-08 dwkrishn 115.59 Bug 7003453 Recalculated the premium if the coverage is
Enterable at enrollment
27-Aug-08 bachakra 115.60 Bug 7331668 If treatment code is RPLC,
enter val at enrollment is 'Y' and mlt_cd is multiple of
compensation, then assign dflt_val to p_val.
*/
--------------------------------------------------------------------------------
--
g_package varchar2(80) := 'ben_determine_variable_rates';
select asg.assignment_id,asg.organization_id
from per_all_assignments_f asg, per_assignment_status_types ast
where asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and asg.assignment_status_type_id = ast.assignment_status_type_id(+)
and ast.per_system_status = 'ACTIVE_ASSIGN' -- Bug 6399423 removed the outer join
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between asg.effective_start_date
and asg.effective_end_date
order by assignment_type desc, effective_start_date desc; -- BUG 4644867
select avr.acty_vrbl_rt_id
from ben_acty_vrbl_rt_f avr
where avr.acty_base_rt_id = p_acty_base_rt_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between avr.effective_start_date
and avr.effective_end_date;
select avr.formula_id, avr.RT_TRTMT_CD
from ben_actl_prem_vrbl_rt_rl_f avr
where avr.actl_prem_id = p_actl_prem_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between avr.effective_start_date
and avr.effective_end_date;
select vrr.formula_id
from ben_vrbl_rt_rl_f vrr
where vrr.acty_base_rt_id = p_acty_base_rt_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between vrr.effective_start_date
and vrr.effective_end_date
order by vrr.ordr_to_aply_num;
select apv.actl_prem_vrbl_rt_id
from ben_actl_prem_vrbl_rt_f apv
where apv.actl_prem_id = p_actl_prem_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between apv.effective_start_date
and apv.effective_end_date;
select ava.vrbl_rt_add_on_calc_rl
from ben_actl_prem_f ava
where ava.actl_prem_id = p_actl_prem_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between ava.effective_start_date
and ava.effective_end_date;
select bvr.bnft_vrbl_rt_id
from ben_bnft_vrbl_rt_f bvr
where bvr.cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between bvr.effective_start_date
and bvr.effective_end_date;
select brr.formula_id
from ben_bnft_vrbl_rt_rl_f brr
where brr.cvg_amt_calc_mthd_id = p_cvg_amt_calc_mthd_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between brr.effective_start_date
and brr.effective_end_date
order by brr.ordr_to_aply_num;
select vpf.val,
vpf.val_calc_rl,
vpf.mx_elcn_val,
vpf.mn_elcn_val,
vpf.dflt_elcn_val,
vpf.incrmnt_elcn_val,
vpf.mlt_cd,
vpf.acty_typ_cd,
vpf.rt_typ_cd,
vpf.bnft_rt_typ_cd,
vpf.tx_typ_cd,
vpf.vrbl_rt_trtmt_cd,
vpf.comp_lvl_fctr_id,
vpf.lwr_lmt_val,
vpf.lwr_lmt_calc_rl,
vpf.upr_lmt_val,
vpf.upr_lmt_calc_rl,
vpf.rndg_cd,
vpf.rndg_rl,
vpf.ultmt_upr_lmt,
vpf.ultmt_lwr_lmt,
vpf.ultmt_upr_lmt_calc_rl,
vpf.ultmt_lwr_lmt_calc_rl,
vpf.ann_mn_elcn_val,
vpf.ann_mx_elcn_val,
vpf.no_mn_elcn_val_dfnd_flag
from ben_vrbl_rt_prfl_f vpf
where vpf.vrbl_rt_prfl_id = l_vrbl_rt_prfl_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between vpf.effective_start_date
and vpf.effective_end_date;
select epe.pl_id,
epe.pl_typ_id,
epe.oipl_id,
epe.pgm_id,
epe.business_group_id,
epe.per_in_ler_id,
pil.ler_id
from ben_elig_per_elctbl_chc epe,ben_per_in_ler pil
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.per_in_ler_id = pil.per_in_ler_id;
select opt_id
from ben_oipl_f oipl
where oipl_id = l_oipl_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between oipl.effective_start_date
and oipl.effective_end_date;
select loc.region_2
from hr_locations_all loc,per_all_assignments_f asg
where loc.location_id = asg.location_id
and asg.person_id = p_person_id
and asg.assignment_type <> 'C'
and asg.primary_flag = 'Y'
and cv_effective_date -- FONM p_effective_date
between
asg.effective_start_date and asg.effective_end_date;
select apr.actl_prem_id
from ben_actl_prem_f apr,
ben_pl_f pln,
ben_oipl_f cop
where ((pln.pl_id = l_epe.pl_id
and pln.actl_prem_id = apr.actl_prem_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pln.effective_start_date
and pln.effective_end_date)
or (cop.oipl_id = l_epe.oipl_id
and cop.actl_prem_id = apr.actl_prem_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between cop.effective_start_date
and cop.effective_end_date))
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between apr.effective_start_date
and apr.effective_end_date;
select pln.nip_acty_ref_perd_cd
from ben_pl_f pln
where pln.pl_id = l_epe.pl_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pln.effective_start_date
and pln.effective_end_date;
select pgm.acty_ref_perd_cd
from ben_pgm_f pgm
where pgm.pgm_id = l_epe.pgm_id
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between pgm.effective_start_date
and pgm.effective_end_date;
select nvl(enb.val,enb.dflt_val) -- used nvl to compute value based on default val
from ben_enrt_bnft enb
where enb.enrt_bnft_id = p_enrt_bnft_id;
select abr2.acty_base_rt_id
from ben_acty_base_rt_f abr,
ben_paird_rt_f prd,
ben_acty_base_rt_f abr2
where abr.acty_base_rt_id = p_acty_base_rt_id
and abr.acty_base_rt_id = prd.chld_acty_base_rt_id
and abr2.acty_base_rt_id = prd.parnt_acty_base_rt_id
and abr2.parnt_chld_cd = 'PARNT'
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr.effective_start_date
and abr.effective_end_date
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between prd.effective_start_date
and prd.effective_end_date
and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
between abr2.effective_start_date
and abr2.effective_end_date
and rownum = 1;
select abr.actl_prem_id
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id = p_acty_base_rt_id
and cv_effective_date between abr.effective_start_date and
abr.effective_end_date;
select ecr.val
from ben_enrt_prem ecr,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe
where ecr.actl_prem_id = l_actl_prem_id
and ecr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
and pil.per_in_ler_id = epe.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
select 'Y'
from ben_pl_f pln
where pln.imptd_incm_calc_cd in ('PRTT', 'SPS', 'DPNT') and
pln.pl_stat_cd = 'A' and
pln.pl_id = p_pl_id and
pln.business_group_id = p_business_group_id and
cv_effective_date -- FONM p_effective_date
between pln.effective_start_date and
pln.effective_end_date;
select ENTR_VAL_AT_ENRT_FLAG
from BEN_CVG_AMT_CALC_MTHD_f
WHERE (pl_id = p_pl_id
or oipl_id = p_oipl_id)
and p_effective_date
between effective_start_date
and effective_end_date;