[Home] [Help]
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.
20-Jan-09 stee 115.61 7728455 Remove fix for bug 6399423. Cursor c_asg
should include other assignment statuses.
11-Feb-2009 velvanop 115.62 Bug 7414757: Added parameter p_entr_val_at_enrt_flag.
VAPRO rates which are 'Enter value at Enrollment', Form field
should allow the user to enter a value during enrollment.
01-Feb-2010 velvanop 115.63 Bug 9306764 : VAPRO rates which are 'Enter value at Enrollment',
Default value for the rate should be taken from the VAPRO.
24-Dec-2010 sagnanas 115.64 Bug 10407950: Fixed cursor c_asg to pick assignment type 'A'
to fix IREC issue
07-Jan-2010 sagnanas 115.65 Bug 10629555: Fixed cursor c_asg to pick the correct applicant
assignment for the correct job when a person has multiple
applicant assignments on the same date
20-Jan-2010 sagnanas 115.66 Bug 10649969: Fixed cursor c_asg to pick the correct applicant
assignment for the internal and international transfer
27-Apr-2010 sagnanas 115.67 Bug 12395523: For FLFXPCL code, pass the multiplier and add
the flat amount
28-Apr-2010 sagnanas 115.68 Corrected checkfile to get rid of gscc error
25-May-2011 sagnanas 115.69 Bug 12428282: Fixed cursor c_asg to pick the correct assignment
09-Aug-2011 amnaraya 115.70 Bug 12810996 : Fixed the cursor c_asg to fetch only one ler.typ_cd
*/
--------------------------------------------------------------------------------
--
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 'IREC' <> ( select ler.typ_cd
from ben_ler_f ler, ben_per_in_ler pil, ben_elig_per_elctbl_chc epe
where epe.per_in_ler_id = pil.per_in_ler_id
and pil.ler_id = ler.ler_id
and epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and cv_effective_date between ler.effective_start_date and ler.effective_end_date--12810996
and ler.business_group_id = epe.business_group_id))--10649969
or (asg.assignment_type = 'A'
and asg.primary_flag = 'N'
and ben_manage_life_events.g_irec_old_ass_rec.assignment_id = asg.assignment_id)--12428282
and asg.business_group_id = (select business_group_id
from ben_elig_per_elctbl_chc
where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id )) --10649969
and asg.assignment_status_type_id = ast.assignment_status_type_id(+)
and ast.per_system_status(+) = 'ACTIVE_ASSIGN' -- Bug 6399423 removed the outer join
-- Bug 7728455 Added back 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;