DBA Data[Home] [Help]

APPS.BEN_DERIVE_PART_AND_RATE_PREM SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 49

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.pl_id = p_pl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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));
Line: 237

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.oipl_id = p_oipl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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));
Line: 469

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.pl_id = p_pl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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));
Line: 658

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.oipl_id = p_oipl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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));
Line: 888

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.pl_id = p_pl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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) )
            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_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_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));
Line: 1092

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.oipl_id = p_oipl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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) )
            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_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_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));
Line: 1337

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.pl_id = p_pl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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) )
            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_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_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));
Line: 1532

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.oipl_id = p_oipl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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) )
            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_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_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));
Line: 1768

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.pl_id = p_pl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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));
Line: 1942

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.oipl_id = p_oipl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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));
Line: 2157

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.pl_id = p_pl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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) )
            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_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_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));
Line: 2359

    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_actl_prem_vrbl_rt_f apv,
           ben_actl_prem_f apr
    where  apr.oipl_id = p_oipl_id
    and    apr.business_group_id = p_business_group_id
    and    p_effective_date
           between apr.effective_start_date
           and     apr.effective_end_date
    and    apr.actl_prem_id = apv.actl_prem_id
    and    apr.business_group_id = apv.business_group_id
    and    p_effective_date
           between apv.effective_start_date
           and     apv.effective_end_date
    and    apv.vrbl_rt_prfl_id = vpf.vrbl_rt_prfl_id
    and    apv.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))
            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_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_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));
Line: 2599

  g_cache_pl_los_rt_rec.delete;
Line: 2600

  g_cache_oipl_los_rt_rec.delete;
Line: 2601

  g_cache_pl_age_rt_rec.delete;
Line: 2602

  g_cache_oipl_age_rt_rec.delete;
Line: 2603

  g_cache_pl_clf_rt_rec.delete;
Line: 2604

  g_cache_oipl_clf_rt_rec.delete;
Line: 2605

  g_cache_pl_cla_rt_rec.delete;
Line: 2606

  g_cache_oipl_cla_rt_rec.delete;
Line: 2607

  g_cache_pl_pff_rt_rec.delete;
Line: 2608

  g_cache_oipl_pff_rt_rec.delete;
Line: 2609

  g_cache_pl_hwf_rt_rec.delete;
Line: 2610

  g_cache_oipl_hwf_rt_rec.delete;