[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select p_pl_id,
'Y',
lsf.los_det_cd,
lsf.los_dt_to_use_cd,
lsf.use_overid_svc_dt_flag,
lsf.los_uom,
lsf.los_det_rl,
lsf.los_dt_to_use_rl,
lsf.los_calc_rl,
lsf.rndg_cd,
lsf.rndg_rl,
lsf.mn_los_num,
lsf.mx_los_num
from ben_los_fctr lsf,
ben_los_rt_f lsr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.pl_id = p_pl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id
and vpf.business_group_id = lsr.business_group_id
and p_effective_date
between lsr.effective_start_date
and lsr.effective_end_date
and lsr.los_fctr_id = lsf.los_fctr_id
and lsr.business_group_id = lsf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(lsf.mn_los_num,p_new_val) and
--p_new_val < ceil( nvl(lsf.mx_los_num,p_new_val) + 0.001 ) )
p_new_val < decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(lsf.mn_los_num,p_new_val) or
--p_new_val >= ceil( nvl(lsf.mx_los_num,p_new_val))+ 0.001 ) and
p_new_val >= decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(lsf.mn_los_num,p_old_val) and
--p_old_val < ceil(nvl(lsf.mx_los_num,p_old_val))+ 0.001 )
p_old_val < decode(nvl(lsf.mx_los_num,p_old_val) , trunc(nvl(lsf.mx_los_num,p_old_val))
,decode(nvl(lsf.mn_los_num,p_old_val), trunc(nvl(lsf.mn_los_num,p_old_val)),
nvl(lsf.mx_los_num,p_old_val)+1,nvl(lsf.mx_los_num,p_old_val)+0.000000001),
nvl(lsf.mx_los_num,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_oipl_id,
'Y',
lsf.los_det_cd,
lsf.los_dt_to_use_cd,
lsf.use_overid_svc_dt_flag,
lsf.los_uom,
lsf.los_det_rl,
lsf.los_dt_to_use_rl,
lsf.los_calc_rl,
lsf.rndg_cd,
lsf.rndg_rl,
lsf.mn_los_num,
lsf.mx_los_num
from ben_los_fctr lsf,
ben_los_rt_f lsr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.oipl_id = p_oipl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id
and vpf.business_group_id = lsr.business_group_id
and p_effective_date
between lsr.effective_start_date
and lsr.effective_end_date
and lsr.los_fctr_id = lsf.los_fctr_id
and lsr.business_group_id = lsf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(lsf.mn_los_num,p_new_val) and
--p_new_val < ceil( nvl(lsf.mx_los_num,p_new_val) + 0.001 ) )
p_new_val < decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(lsf.mn_los_num,p_new_val) or
--p_new_val >= ceil( nvl(lsf.mx_los_num,p_new_val))+ 0.001 ) and
p_new_val >= decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(lsf.mn_los_num,p_old_val) and
--p_old_val < ceil(nvl(lsf.mx_los_num,p_old_val))+ 0.001 )
p_old_val < decode(nvl(lsf.mx_los_num,p_old_val) , trunc(nvl(lsf.mx_los_num,p_old_val))
,decode(nvl(lsf.mn_los_num,p_old_val), trunc(nvl(lsf.mn_los_num,p_old_val)),
nvl(lsf.mx_los_num,p_old_val)+1,nvl(lsf.mx_los_num,p_old_val)+0.000000001),
nvl(lsf.mx_los_num,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_plip_id,
'Y',
lsf.los_det_cd,
lsf.los_dt_to_use_cd,
lsf.use_overid_svc_dt_flag,
lsf.los_uom,
lsf.los_det_rl,
lsf.los_dt_to_use_rl,
lsf.los_calc_rl,
lsf.rndg_cd,
lsf.rndg_rl,
lsf.mn_los_num,
lsf.mx_los_num
from ben_los_fctr lsf,
ben_los_rt_f lsr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.plip_id = p_plip_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id
and vpf.business_group_id = lsr.business_group_id
and p_effective_date
between lsr.effective_start_date
and lsr.effective_end_date
and lsr.los_fctr_id = lsf.los_fctr_id
and lsr.business_group_id = lsf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(lsf.mn_los_num,p_new_val) and
--p_new_val < ceil( nvl(lsf.mx_los_num,p_new_val)+0.001))
p_new_val < decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(lsf.mn_los_num,p_new_val) or
--p_new_val >= ceil(nvl(lsf.mx_los_num,p_new_val))+0.001) and
p_new_val >= decode(nvl(lsf.mx_los_num,p_new_val) , trunc(nvl(lsf.mx_los_num,p_new_val))
,decode(nvl(lsf.mn_los_num,p_new_val), trunc(nvl(lsf.mn_los_num,p_new_val)),
nvl(lsf.mx_los_num,p_new_val)+1,nvl(lsf.mx_los_num,p_new_val)+0.000000001),
nvl(lsf.mx_los_num,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(lsf.mn_los_num,p_old_val) and
--p_old_val < ceil(nvl(lsf.mx_los_num,p_old_val))+0.001)
p_old_val < decode(nvl(lsf.mx_los_num,p_old_val) , trunc(nvl(lsf.mx_los_num,p_old_val))
,decode(nvl(lsf.mn_los_num,p_old_val), trunc(nvl(lsf.mn_los_num,p_old_val)),
nvl(lsf.mx_los_num,p_old_val)+1,nvl(lsf.mx_los_num,p_old_val)+0.000000001),
nvl(lsf.mx_los_num,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_pl_id,
'Y',
agf.age_det_cd,
agf.age_to_use_cd,
agf.age_uom,
agf.age_det_rl,
agf.rndg_cd,
agf.rndg_rl,
agf.age_calc_rl,
agf.mn_age_num,
agf.mx_age_num
from ben_age_fctr agf,
ben_age_rt_f art,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.pl_id = p_pl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id
and vpf.business_group_id = art.business_group_id
and p_effective_date
between art.effective_start_date
and art.effective_end_date
and art.age_fctr_id = agf.age_fctr_id
and art.business_group_id = agf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(agf.mn_age_num,p_new_val) and
--p_new_val < ceil( nvl(agf.mx_age_num,p_new_val)+0.001))
p_new_val < decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
nvl(agf.mx_age_num,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(agf.mn_age_num,p_new_val) or
--p_new_val >= ceil(nvl(agf.mx_age_num,p_new_val))+0.001) and
p_new_val >= decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
nvl(agf.mx_age_num,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(agf.mn_age_num,p_old_val) and
--p_old_val < ceil(nvl(agf.mx_age_num,p_old_val))+0.001)
p_old_val < decode(nvl(agf.mx_age_num,p_old_val) , trunc(nvl(agf.mx_age_num,p_old_val))
,decode(nvl(agf.mn_age_num,p_old_val), trunc(nvl(agf.mn_age_num,p_old_val)),
nvl(agf.mx_age_num,p_old_val)+1,nvl(agf.mx_age_num,p_old_val)+0.000000001),
nvl(agf.mx_age_num,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_oipl_id,
'Y',
agf.age_det_cd,
agf.age_to_use_cd,
agf.age_uom,
agf.age_det_rl,
agf.rndg_cd,
agf.rndg_rl,
agf.age_calc_rl,
agf.mn_age_num,
agf.mx_age_num
from ben_age_fctr agf,
ben_age_rt_f art,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.oipl_id = p_oipl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id
and vpf.business_group_id = art.business_group_id
and p_effective_date
between art.effective_start_date
and art.effective_end_date
and art.age_fctr_id = agf.age_fctr_id
and art.business_group_id = agf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(agf.mn_age_num,p_new_val) and
--p_new_val < ceil(nvl(agf.mx_age_num,p_new_val)+0.001 ) )
p_new_val < decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
nvl(agf.mx_age_num,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(agf.mn_age_num,p_new_val) or
--p_new_val >= ceil(nvl(agf.mx_age_num,p_new_val))+0.001 ) and
p_new_val >= decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
nvl(agf.mx_age_num,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(agf.mn_age_num,p_old_val) and
--p_old_val < ceil(nvl(agf.mx_age_num,p_old_val))+0.001 )
p_old_val < decode(nvl(agf.mx_age_num,p_old_val) , trunc(nvl(agf.mx_age_num,p_old_val))
,decode(nvl(agf.mn_age_num,p_old_val), trunc(nvl(agf.mn_age_num,p_old_val)),
nvl(agf.mx_age_num,p_old_val)+1,nvl(agf.mx_age_num,p_old_val)+0.000000001),
nvl(agf.mx_age_num,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_plip_id,
'Y',
agf.age_det_cd,
agf.age_to_use_cd,
agf.age_uom,
agf.age_det_rl,
agf.rndg_cd,
agf.rndg_rl,
agf.age_calc_rl,
agf.mn_age_num,
agf.mx_age_num
from ben_cvg_amt_calc_mthd_f abr,
ben_bnft_vrbl_rt_f avr,
ben_vrbl_rt_prfl_f vpf,
ben_age_rt_f art,
ben_age_fctr agf
where abr.plip_id = p_plip_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id
and p_effective_date
between art.effective_start_date
and art.effective_end_date
and art.age_fctr_id = agf.age_fctr_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(agf.mn_age_num,p_new_val) and
--p_new_val < ceil(nvl(agf.mx_age_num,p_new_val)+0.001 ) )
p_new_val < decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
nvl(agf.mx_age_num,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(agf.mn_age_num,p_new_val) or
--p_new_val >= ceil(nvl(agf.mx_age_num,p_new_val))+0.001 ) and
p_new_val >= decode(nvl(agf.mx_age_num,p_new_val) , trunc(nvl(agf.mx_age_num,p_new_val))
,decode(nvl(agf.mn_age_num,p_new_val), trunc(nvl(agf.mn_age_num,p_new_val)),
nvl(agf.mx_age_num,p_new_val)+1,nvl(agf.mx_age_num,p_new_val)+0.000000001),
nvl(agf.mx_age_num,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(agf.mn_age_num,p_old_val) and
--p_old_val < ceil(nvl(agf.mx_age_num,p_old_val))+0.001 )
p_old_val < decode(nvl(agf.mx_age_num,p_old_val) , trunc(nvl(agf.mx_age_num,p_old_val))
,decode(nvl(agf.mn_age_num,p_old_val), trunc(nvl(agf.mn_age_num,p_old_val)),
nvl(agf.mx_age_num,p_old_val)+1,nvl(agf.mx_age_num,p_old_val)+0.000000001),
nvl(agf.mx_age_num,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_pl_id,
'Y',
clf.comp_lvl_uom,
clf.comp_src_cd,
clf.comp_lvl_det_cd,
clf.comp_lvl_det_rl,
clf.rndg_cd,
clf.rndg_rl,
clf.mn_comp_val,
clf.mx_comp_val,
clf.bnfts_bal_id,
clf.defined_balance_id,
clf.sttd_sal_prdcty_cd,
clf.comp_lvl_fctr_id,
clf.comp_calc_rl
from ben_comp_lvl_fctr clf,
ben_comp_lvl_rt_f clr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.pl_id = p_pl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id
and vpf.business_group_id = clr.business_group_id
and p_effective_date
between clr.effective_start_date
and clr.effective_end_date
and clr.comp_lvl_fctr_id = clf.comp_lvl_fctr_id
and clr.business_group_id = clf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(clf.mn_comp_val,p_new_val) and
--p_new_val < ceil(nvl(clf.mx_comp_val,p_new_val)+0.001 ) )
p_new_val < decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(clf.mn_comp_val,p_new_val) or
--p_new_val >= ceil(nvl(clf.mx_comp_val,p_new_val))+0.001 ) and
p_new_val >= decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(clf.mn_comp_val,p_old_val) and
--p_old_val < ceil(nvl(clf.mx_comp_val,p_old_val))+0.001 )
p_old_val < decode(nvl(clf.mx_comp_val,p_old_val) , trunc(nvl(clf.mx_comp_val,p_old_val))
,decode(nvl(clf.mn_comp_val,p_old_val), trunc(nvl(clf.mn_comp_val,p_old_val)),
nvl(clf.mx_comp_val,p_old_val)+1,nvl(clf.mx_comp_val,p_old_val)+0.000000001),
nvl(clf.mx_comp_val,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_oipl_id,
'Y',
clf.comp_lvl_uom,
clf.comp_src_cd,
clf.comp_lvl_det_cd,
clf.comp_lvl_det_rl,
clf.rndg_cd,
clf.rndg_rl,
clf.mn_comp_val,
clf.mx_comp_val,
clf.bnfts_bal_id,
clf.defined_balance_id,
clf.sttd_sal_prdcty_cd,
clf.comp_lvl_fctr_id,
clf.comp_calc_rl
from ben_comp_lvl_fctr clf,
ben_comp_lvl_rt_f clr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.oipl_id = p_oipl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id
and vpf.business_group_id = clr.business_group_id
and p_effective_date
between clr.effective_start_date
and clr.effective_end_date
and clr.comp_lvl_fctr_id = clf.comp_lvl_fctr_id
and clr.business_group_id = clf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(clf.mn_comp_val,p_new_val) and
--p_new_val < ceil(nvl(clf.mx_comp_val,p_new_val)+ 0.001 ) )
p_new_val < decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(clf.mn_comp_val,p_new_val) or
--p_new_val >= ceil(nvl(clf.mx_comp_val,p_new_val))+ 0.001 ) and
p_new_val >= decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(clf.mn_comp_val,p_old_val) and
--p_old_val < ceil(nvl(clf.mx_comp_val,p_old_val))+ 0.001 )
p_old_val < decode(nvl(clf.mx_comp_val,p_old_val) , trunc(nvl(clf.mx_comp_val,p_old_val))
,decode(nvl(clf.mn_comp_val,p_old_val), trunc(nvl(clf.mn_comp_val,p_old_val)),
nvl(clf.mx_comp_val,p_old_val)+1,nvl(clf.mx_comp_val,p_old_val)+0.000000001),
nvl(clf.mx_comp_val,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_plip_id,
'Y',
clf.comp_lvl_uom,
clf.comp_src_cd,
clf.comp_lvl_det_cd,
clf.comp_lvl_det_rl,
clf.rndg_cd,
clf.rndg_rl,
clf.mn_comp_val,
clf.mx_comp_val,
clf.bnfts_bal_id,
clf.defined_balance_id,
clf.sttd_sal_prdcty_cd,
clf.comp_lvl_fctr_id,
clf.comp_calc_rl
from ben_comp_lvl_fctr clf,
ben_comp_lvl_rt_f clr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.plip_id = p_plip_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id
and vpf.business_group_id = clr.business_group_id
and p_effective_date
between clr.effective_start_date
and clr.effective_end_date
and clr.comp_lvl_fctr_id = clf.comp_lvl_fctr_id
and clr.business_group_id = clf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(clf.mn_comp_val,p_new_val) and
--p_new_val < ceil(nvl(clf.mx_comp_val,p_new_val)+0.001 ) )
p_new_val < decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(clf.mn_comp_val,p_new_val) or
--p_new_val >= ceil(nvl(clf.mx_comp_val,p_new_val))+0.001 ) and
p_new_val >= decode(nvl(clf.mx_comp_val,p_new_val) , trunc(nvl(clf.mx_comp_val,p_new_val))
,decode(nvl(clf.mn_comp_val,p_new_val), trunc(nvl(clf.mn_comp_val,p_new_val)),
nvl(clf.mx_comp_val,p_new_val)+1,nvl(clf.mx_comp_val,p_new_val)+0.000000001),
nvl(clf.mx_comp_val,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(clf.mn_comp_val,p_old_val) and
--p_old_val < ceil(nvl(clf.mx_comp_val,p_old_val))+0.001 )
p_old_val < decode(nvl(clf.mx_comp_val,p_old_val) , trunc(nvl(clf.mx_comp_val,p_old_val))
,decode(nvl(clf.mn_comp_val,p_old_val), trunc(nvl(clf.mn_comp_val,p_old_val)),
nvl(clf.mx_comp_val,p_old_val)+1,nvl(clf.mx_comp_val,p_old_val)+0.000000001),
nvl(clf.mx_comp_val,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_pl_id,
'Y',
cla.los_fctr_id,
cla.age_fctr_id,
cla.cmbnd_min_val,
cla.cmbnd_max_val
from ben_cmbn_age_los_fctr cla,
ben_cmbn_age_los_rt_f cmr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.pl_id = p_pl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id
and vpf.business_group_id = cmr.business_group_id
and p_effective_date
between cmr.effective_start_date
and cmr.effective_end_date
and cmr.cmbn_age_los_fctr_id = cla.cmbn_age_los_fctr_id
and cmr.business_group_id = cla.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(cla.cmbnd_min_val,p_new_val) and
--p_new_val < ceil( nvl(cla.cmbnd_max_val,p_new_val)+ 0.001 ) )
p_new_val < decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(cla.cmbnd_min_val,p_new_val) or
--p_new_val >= ceil(nvl(cla.cmbnd_max_val,p_new_val))+0.001 ) and
p_new_val >= decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(cla.cmbnd_min_val,p_old_val) and
--p_old_val < ceil(nvl(cla.cmbnd_max_val,p_old_val))+0.001 )
p_old_val < decode(nvl(cla.cmbnd_max_val,p_old_val) , trunc(nvl(cla.cmbnd_max_val,p_old_val))
,decode(nvl(cla.cmbnd_min_val,p_old_val), trunc(nvl(cla.cmbnd_min_val,p_old_val)),
nvl(cla.cmbnd_max_val,p_old_val)+1,nvl(cla.cmbnd_max_val,p_old_val)+0.000000001),
nvl(cla.cmbnd_max_val,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_oipl_id,
'Y',
cla.los_fctr_id,
cla.age_fctr_id,
cla.cmbnd_min_val,
cla.cmbnd_max_val
from ben_cmbn_age_los_fctr cla,
ben_cmbn_age_los_rt_f cmr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.oipl_id = p_oipl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id
and vpf.business_group_id = cmr.business_group_id
and p_effective_date
between cmr.effective_start_date
and cmr.effective_end_date
and cmr.cmbn_age_los_fctr_id = cla.cmbn_age_los_fctr_id
and cmr.business_group_id = cla.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(cla.cmbnd_min_val,p_new_val) and
--p_new_val < ceil(nvl(cla.cmbnd_max_val,p_new_val)+0.001 ) )
p_new_val < decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(cla.cmbnd_min_val,p_new_val) or
--p_new_val >= ceil(nvl(cla.cmbnd_max_val,p_new_val))+0.001 ) and
p_new_val >= decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(cla.cmbnd_min_val,p_old_val) and
--p_old_val < ceil(nvl(cla.cmbnd_max_val,p_old_val))+0.001 )
p_old_val < decode(nvl(cla.cmbnd_max_val,p_old_val) , trunc(nvl(cla.cmbnd_max_val,p_old_val))
,decode(nvl(cla.cmbnd_min_val,p_old_val), trunc(nvl(cla.cmbnd_min_val,p_old_val)),
nvl(cla.cmbnd_max_val,p_old_val)+1,nvl(cla.cmbnd_max_val,p_old_val)+0.000000001),
nvl(cla.cmbnd_max_val,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_plip_id,
'Y',
cla.los_fctr_id,
cla.age_fctr_id,
cla.cmbnd_min_val,
cla.cmbnd_max_val
from ben_cmbn_age_los_fctr cla,
ben_cmbn_age_los_rt_f cmr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.plip_id = p_plip_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id
and vpf.business_group_id = cmr.business_group_id
and p_effective_date
between cmr.effective_start_date
and cmr.effective_end_date
and cmr.cmbn_age_los_fctr_id = cla.cmbn_age_los_fctr_id
and cmr.business_group_id = cla.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(cla.cmbnd_min_val,p_new_val) and
--p_new_val < ceil(nvl(cla.cmbnd_max_val,p_new_val)+ 0.001 ) )
p_new_val < decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(cla.cmbnd_min_val,p_new_val) or
--p_new_val >= ceil(nvl(cla.cmbnd_max_val,p_new_val))+ 0.001 ) and
p_new_val >= decode(nvl(cla.cmbnd_max_val,p_new_val) , trunc(nvl(cla.cmbnd_max_val,p_new_val))
,decode(nvl(cla.cmbnd_min_val,p_new_val), trunc(nvl(cla.cmbnd_min_val,p_new_val)),
nvl(cla.cmbnd_max_val,p_new_val)+1,nvl(cla.cmbnd_max_val,p_new_val)+0.000000001),
nvl(cla.cmbnd_max_val,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(cla.cmbnd_min_val,p_old_val) and
--p_old_val < ceil(nvl(cla.cmbnd_max_val,p_old_val))+ 0.001 )
p_old_val < decode(nvl(cla.cmbnd_max_val,p_old_val) , trunc(nvl(cla.cmbnd_max_val,p_old_val))
,decode(nvl(cla.cmbnd_min_val,p_old_val), trunc(nvl(cla.cmbnd_min_val,p_old_val)),
nvl(cla.cmbnd_max_val,p_old_val)+1,nvl(cla.cmbnd_max_val,p_old_val)+0.000000001),
nvl(cla.cmbnd_max_val,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_pl_id,
'Y',
pff.use_prmry_asnt_only_flag,
pff.use_sum_of_all_asnts_flag,
pff.rndg_cd,
pff.rndg_rl,
pff.mn_pct_val,
pff.mx_pct_val
from ben_pct_fl_tm_fctr pff,
ben_pct_fl_tm_rt_f pfr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.pl_id = p_pl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id
and vpf.business_group_id = pfr.business_group_id
and p_effective_date
between pfr.effective_start_date
and pfr.effective_end_date
and pfr.pct_fl_tm_fctr_id = pff.pct_fl_tm_fctr_id
and pfr.business_group_id = pff.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(pff.mn_pct_val,p_new_val) and
(p_new_val*100) < (nvl(pff.mx_pct_val,p_new_val)*100)+1)
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(pff.mn_pct_val,p_new_val) or
(p_new_val*100) >= (nvl(pff.mx_pct_val,p_new_val)*100)+1) and
p_old_val >= nvl(pff.mn_pct_val,p_old_val) and
(p_old_val*100) < (nvl(pff.mx_pct_val,p_old_val)*100)+1)
or
(p_new_val is null and
p_old_val is null));
select p_oipl_id,
'Y',
pff.use_prmry_asnt_only_flag,
pff.use_sum_of_all_asnts_flag,
pff.rndg_cd,
pff.rndg_rl,
pff.mn_pct_val,
pff.mx_pct_val
from ben_pct_fl_tm_fctr pff,
ben_pct_fl_tm_rt_f pfr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.oipl_id = p_oipl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id
and vpf.business_group_id = pfr.business_group_id
and p_effective_date
between pfr.effective_start_date
and pfr.effective_end_date
and pfr.pct_fl_tm_fctr_id = pff.pct_fl_tm_fctr_id
and pfr.business_group_id = pff.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(pff.mn_pct_val,p_new_val) and
(p_new_val*100) < (nvl(pff.mx_pct_val,p_new_val)*100)+1)
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(pff.mn_pct_val,p_new_val) or
(p_new_val*100) >= (nvl(pff.mx_pct_val,p_new_val)*100)+1) and
p_old_val >= nvl(pff.mn_pct_val,p_old_val) and
(p_old_val*100) < (nvl(pff.mx_pct_val,p_old_val)*100)+1)
or
(p_new_val is null and
p_old_val is null));
select p_plip_id,
'Y',
pff.use_prmry_asnt_only_flag,
pff.use_sum_of_all_asnts_flag,
pff.rndg_cd,
pff.rndg_rl,
pff.mn_pct_val,
pff.mx_pct_val
from ben_pct_fl_tm_fctr pff,
ben_pct_fl_tm_rt_f pfr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.plip_id = p_plip_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id
and vpf.business_group_id = pfr.business_group_id
and p_effective_date
between pfr.effective_start_date
and pfr.effective_end_date
and pfr.pct_fl_tm_fctr_id = pff.pct_fl_tm_fctr_id
and pfr.business_group_id = pff.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(pff.mn_pct_val,p_new_val) and
(p_new_val*100) < (nvl(pff.mx_pct_val,p_new_val)*100)+1)
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(pff.mn_pct_val,p_new_val) or
(p_new_val*100) >= (nvl(pff.mx_pct_val,p_new_val)*100)+1) and
p_old_val >= nvl(pff.mn_pct_val,p_old_val) and
(p_new_val*100) < (nvl(pff.mx_pct_val,p_old_val)*100)+1)
or
(p_new_val is null and
p_old_val is null));
select p_pl_id,
'Y',
hwf.hrs_src_cd,
hwf.hrs_wkd_det_cd,
hwf.hrs_wkd_det_rl,
hwf.rndg_cd,
hwf.rndg_rl,
hwf.defined_balance_id,
hwf.bnfts_bal_id,
hwf.mn_hrs_num,
hwf.mx_hrs_num,
hwf.once_r_cntug_cd,
hwf.hrs_wkd_calc_rl
from ben_hrs_wkd_in_perd_fctr hwf,
ben_hrs_wkd_in_perd_rt_f hwr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.pl_id = p_pl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id
and vpf.business_group_id = hwr.business_group_id
and p_effective_date
between hwr.effective_start_date
and hwr.effective_end_date
and hwr.hrs_wkd_in_perd_fctr_id = hwf.hrs_wkd_in_perd_fctr_id
and hwr.business_group_id = hwf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(hwf.mn_hrs_num,p_new_val) and
--p_new_val < ceil(nvl(hwf.mx_hrs_num,p_new_val)+0.001) )
p_new_val < decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(hwf.mn_hrs_num,p_new_val) or
--p_new_val >= ceil(nvl(hwf.mx_hrs_num,p_new_val))+0.001) and
p_new_val >= decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(hwf.mn_hrs_num,p_old_val) and
--p_old_val < ceil(nvl(hwf.mx_hrs_num,p_old_val)) +0.001)
p_old_val < decode(nvl(hwf.mx_hrs_num,p_old_val) , trunc(nvl(hwf.mx_hrs_num,p_old_val))
,decode(nvl(hwf.mn_hrs_num,p_old_val), trunc(nvl(hwf.mn_hrs_num,p_old_val)),
nvl(hwf.mx_hrs_num,p_old_val)+1,nvl(hwf.mx_hrs_num,p_old_val)+0.000000001),
nvl(hwf.mx_hrs_num,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_oipl_id,
'Y',
hwf.hrs_src_cd,
hwf.hrs_wkd_det_cd,
hwf.hrs_wkd_det_rl,
hwf.rndg_cd,
hwf.rndg_rl,
hwf.defined_balance_id,
hwf.bnfts_bal_id,
hwf.mn_hrs_num,
hwf.mx_hrs_num,
hwf.once_r_cntug_cd,
hwf.hrs_wkd_calc_rl
from ben_hrs_wkd_in_perd_fctr hwf,
ben_hrs_wkd_in_perd_rt_f hwr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.oipl_id = p_oipl_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id
and vpf.business_group_id = hwr.business_group_id
and p_effective_date
between hwr.effective_start_date
and hwr.effective_end_date
and hwr.hrs_wkd_in_perd_fctr_id = hwf.hrs_wkd_in_perd_fctr_id
and hwr.business_group_id = hwf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(hwf.mn_hrs_num,p_new_val) and
--p_new_val < ceil( nvl(hwf.mx_hrs_num,p_new_val) +0.001))
p_new_val < decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(hwf.mn_hrs_num,p_new_val) or
--p_new_val >= ceil(nvl(hwf.mx_hrs_num,p_new_val))+0.001) and
p_new_val >= decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(hwf.mn_hrs_num,p_old_val) and
--p_old_val < ceil(nvl(hwf.mx_hrs_num,p_old_val))+0.001)
p_old_val < decode(nvl(hwf.mx_hrs_num,p_old_val) , trunc(nvl(hwf.mx_hrs_num,p_old_val))
,decode(nvl(hwf.mn_hrs_num,p_old_val), trunc(nvl(hwf.mn_hrs_num,p_old_val)),
nvl(hwf.mx_hrs_num,p_old_val)+1,nvl(hwf.mx_hrs_num,p_old_val)+0.000000001),
nvl(hwf.mx_hrs_num,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
select p_plip_id,
'Y',
hwf.hrs_src_cd,
hwf.hrs_wkd_det_cd,
hwf.hrs_wkd_det_rl,
hwf.rndg_cd,
hwf.rndg_rl,
hwf.defined_balance_id,
hwf.bnfts_bal_id,
hwf.mn_hrs_num,
hwf.mx_hrs_num,
hwf.once_r_cntug_cd,
hwf.hrs_wkd_calc_rl
from ben_hrs_wkd_in_perd_fctr hwf,
ben_hrs_wkd_in_perd_rt_f hwr,
ben_vrbl_rt_prfl_f vpf,
ben_bnft_vrbl_rt_f avr,
ben_cvg_amt_calc_mthd_f abr
where abr.plip_id = p_plip_id
and abr.business_group_id = p_business_group_id
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date
and abr.cvg_amt_calc_mthd_id = avr.cvg_amt_calc_mthd_id
and abr.business_group_id = avr.business_group_id
and p_effective_date
between avr.effective_start_date
and avr.effective_end_date
and avr.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
and avr.business_group_id = vpf.business_group_id
and p_effective_date
between vpf.effective_start_date
and vpf.effective_end_date
and vpf.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id
and vpf.business_group_id = hwr.business_group_id
and p_effective_date
between hwr.effective_start_date
and hwr.effective_end_date
and hwr.hrs_wkd_in_perd_fctr_id = hwf.hrs_wkd_in_perd_fctr_id
and hwr.business_group_id = hwf.business_group_id
and ((p_new_val is not null and
p_old_val is not null and
p_new_val >= nvl(hwf.mn_hrs_num,p_new_val) and
--p_new_val < ceil(nvl(hwf.mx_hrs_num,p_new_val)+0.001 ) )
p_new_val < decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) )
or
(p_new_val is not null and
p_old_val is not null and
(p_new_val < nvl(hwf.mn_hrs_num,p_new_val) or
--p_new_val >= ceil(nvl(hwf.mx_hrs_num,p_new_val))+0.001 ) and
p_new_val >= decode(nvl(hwf.mx_hrs_num,p_new_val) , trunc(nvl(hwf.mx_hrs_num,p_new_val))
,decode(nvl(hwf.mn_hrs_num,p_new_val), trunc(nvl(hwf.mn_hrs_num,p_new_val)),
nvl(hwf.mx_hrs_num,p_new_val)+1,nvl(hwf.mx_hrs_num,p_new_val)+0.000000001),
nvl(hwf.mx_hrs_num,p_new_val)+0.000000001 ) ) and
p_old_val >= nvl(hwf.mn_hrs_num,p_old_val) and
--p_old_val < ceil(nvl(hwf.mx_hrs_num,p_old_val))+0.001 )
p_old_val < decode(nvl(hwf.mx_hrs_num,p_old_val) , trunc(nvl(hwf.mx_hrs_num,p_old_val))
,decode(nvl(hwf.mn_hrs_num,p_old_val), trunc(nvl(hwf.mn_hrs_num,p_old_val)),
nvl(hwf.mx_hrs_num,p_old_val)+1,nvl(hwf.mx_hrs_num,p_old_val)+0.000000001),
nvl(hwf.mx_hrs_num,p_old_val)+0.000000001 ) )
or
(p_new_val is null and
p_old_val is null));
g_cache_pl_los_rt_rec.delete;
g_cache_oipl_los_rt_rec.delete;
g_cache_plip_los_rt_rec.delete;
g_cache_pl_age_rt_rec.delete;
g_cache_oipl_age_rt_rec.delete;
g_cache_plip_age_rt_rec.delete;
g_cache_pl_clf_rt_rec.delete;
g_cache_oipl_clf_rt_rec.delete;
g_cache_plip_clf_rt_rec.delete;
g_cache_pl_cla_rt_rec.delete;
g_cache_oipl_cla_rt_rec.delete;
g_cache_plip_cla_rt_rec.delete;
g_cache_pl_pff_rt_rec.delete;
g_cache_oipl_pff_rt_rec.delete;
g_cache_plip_pff_rt_rec.delete;
g_cache_pl_hwf_rt_rec.delete;
g_cache_oipl_hwf_rt_rec.delete;
g_cache_plip_hwf_rt_rec.delete;