[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* select null
from all_tab_columns
where table_name = upper(p_tab_name)
and column_name = upper(p_col_name)
and owner = upper(p_oracle_schema);*/
select null
from user_synonyms syn,dba_tab_columns col
where syn.synonym_name = upper(p_tab_name)
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and COL.column_name = upper(p_col_name)
and COL.owner = upper(p_oracle_schema);
if p_event not in ('CREATE','DELETE') then
--
fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');
l_strg := 'update '||p_table_name||' set drvbl_fctr_apls_rts_flag = :1
where '||p_col_name|| '= :2 and
drvbl_fctr_apls_rts_flag = :3
and (
exists (select null
from ben_age_rt_f art,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :4
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) or
exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :5
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) or
exists (select null
from ben_comp_lvl_rt_f clr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :6
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) or
exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :7
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) or
exists (select null
from ben_los_rt_f lsr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :8
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) or
exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :9
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id))';
l_strg := 'update '||p_table_name||' set drvbl_fctr_apls_rts_flag = :1
where '||p_col_name|| '= :2 and
drvbl_fctr_apls_rts_flag = :3
and (
exists (select null
from ben_age_rt_f art,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||' = :4
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) or
exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||'= :5
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) or
exists (select null
from ben_comp_lvl_rt_f clr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||'= :6
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) or
exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||'= :7
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) or
exists (select null
from ben_los_rt_f lsr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||'= :8
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) or
exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||'= :9
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id))';
l_strg := 'update '||p_table_name||' set drvbl_fctr_apls_rts_flag = :1
where '||p_col_name|| '= :2 and
drvbl_fctr_apls_rts_flag = :3
and (
exists (select null
from ben_age_rt_f art,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||' = :4
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) or
exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||'= :5
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) or
exists (select null
from ben_comp_lvl_rt_f clr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||'= :6
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) or
exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||'= :7
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) or
exists (select null
from ben_los_rt_f lsr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||'= :8
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) or
exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||'= :9
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id))';
elsif p_event = 'DELETE' then
--
l_strg := 'update '||p_table_name||' set drvbl_fctr_apls_rts_flag = :1
where '||p_col_name|| '= :2 and
drvbl_fctr_apls_rts_flag = :3 and
not exists (select null
from ben_age_rt_f art,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||' = :4
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) and
not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :5
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) and
not exists (select null
from ben_comp_lvl_rt_f clr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :6
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) and
not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :7
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) and
not exists (select null
from ben_los_rt_f lsr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :8
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) and
not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.'||p_col_name||'= :9
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)';
not exists (select null
from ben_age_rt_f art,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||' = :10
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) and
not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||' = :11
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) and
not exists (select null
from ben_comp_lvl_rt_f clr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||'= :12
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) and
not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||'= :13
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) and
not exists (select null
from ben_los_rt_f lsr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||'= :14
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) and
not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.'||p_col_name||'= :15
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)';
not exists (select null
from ben_age_rt_f art,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||' = :16
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id) and
not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||'= :17
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id) and
not exists (select null
from ben_comp_lvl_rt_f clr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||'= :18
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id) and
not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||'= :19
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id) and
not exists (select null
from ben_los_rt_f lsr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||'= :20
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id) and
not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.'||p_col_name||'= :21
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)';
select pgm_id,
pl_id,
ptip_id,
plip_id,
oipl_id
from ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where avr.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
and avr.acty_base_rt_id = abr.acty_base_rt_id
-- RCHASE bug 1568561 - added union
union
select to_number(null) pgm_id,
pl_id,
to_number(null) ptip_id,
to_number(null) plip_id,
oipl_id
from ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
union
select to_number(null) pgm_id,
pl_id,
to_number(null) ptip_id,
plip_id,
oipl_id
from ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = p_vrbl_rt_prfl_id
;
if p_event not in ('CREATE','DELETE') then
--
fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');
update ben_pgm_f
set drvbl_fctr_apls_rts_flag = 'Y'
where pgm_id = i.pgm_id;
update ben_pl_f
set drvbl_fctr_apls_rts_flag = 'Y'
where pl_id = i.pl_id;
update ben_plip_f
set drvbl_fctr_apls_rts_flag = 'Y'
where plip_id = i.plip_id;
update ben_ptip_f
set drvbl_fctr_apls_rts_flag = 'Y'
where ptip_id = i.ptip_id;
update ben_oipl_f
set drvbl_fctr_apls_rts_flag = 'Y'
where oipl_id = i.oipl_id;
elsif p_event = 'DELETE' then
--
For i in c1 Loop
--
If i.pgm_id is not null then
--
update ben_pgm_f
set drvbl_fctr_apls_rts_flag = 'N'
where pgm_id = i.pgm_id
and drvbl_fctr_apls_rts_flag = 'Y'
and not exists (select null
from ben_age_rt_f art,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pgm_id = i.pgm_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
and not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pgm_id = i.pgm_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
and not exists (select null
from ben_comp_lvl_rt_f clr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pgm_id = i.pgm_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
and not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pgm_id = i.pgm_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
and not exists (select null
from ben_los_rt_f lsr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pgm_id = i.pgm_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
and not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pgm_id = i.pgm_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id);
update ben_pl_f
set drvbl_fctr_apls_rts_flag = 'N'
where pl_id = i.pl_id
and drvbl_fctr_apls_rts_flag = 'Y'
and not exists (select null
from ben_age_rt_f art,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pl_id = i.pl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
and not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pl_id = i.pl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
and not exists (select null
from ben_comp_lvl_rt_f clr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pl_id = i.pl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
and not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pl_id = i.pl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
and not exists (select null
from ben_los_rt_f lsr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pl_id = i.pl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
and not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.pl_id = i.pl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)
and not exists (select null
from ben_age_rt_f art,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.pl_id = i.pl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
and not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.pl_id = i.pl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
and not exists (select null
from ben_comp_lvl_rt_f clr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.pl_id = i.pl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
and not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.pl_id = i.pl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
and not exists (select null
from ben_los_rt_f lsr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.pl_id = i.pl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
and not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.pl_id = i.pl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)
and not exists (select null
from ben_age_rt_f art,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.pl_id = i.pl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
and not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.pl_id = i.pl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
and not exists (select null
from ben_comp_lvl_rt_f clr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.pl_id = i.pl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
and not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.pl_id = i.pl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
and not exists (select null
from ben_los_rt_f lsr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.pl_id = i.pl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
and not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.pl_id = i.pl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id);
update ben_plip_f
set drvbl_fctr_apls_rts_flag = 'N'
where plip_id = i.plip_id
and drvbl_fctr_apls_rts_flag = 'Y'
and not exists (select null
from ben_age_rt_f art,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.plip_id = i.plip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
and not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.plip_id = i.plip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
and not exists (select null
from ben_comp_lvl_rt_f clr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.plip_id = i.plip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
and not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.plip_id = i.plip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
and not exists (select null
from ben_los_rt_f lsr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.plip_id = i.plip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
and not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.plip_id = i.plip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)
and not exists (select null
from ben_age_rt_f art,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.plip_id = i.plip_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
and not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.plip_id = i.plip_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
and not exists (select null
from ben_comp_lvl_rt_f clr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.plip_id = i.plip_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
and not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.plip_id = i.plip_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
and not exists (select null
from ben_los_rt_f lsr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.plip_id = i.plip_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
and not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.plip_id = i.plip_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id);
update ben_ptip_f
set drvbl_fctr_apls_rts_flag = 'N'
where ptip_id = i.ptip_id
and drvbl_fctr_apls_rts_flag = 'Y'
and not exists (select null
from ben_age_rt_f art,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.ptip_id = i.ptip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
and not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.ptip_id = i.ptip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
and not exists (select null
from ben_comp_lvl_rt_f clr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.ptip_id = i.ptip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
and not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.ptip_id = i.ptip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
and not exists (select null
from ben_los_rt_f lsr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.ptip_id = i.ptip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
and not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.ptip_id = i.ptip_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id);
update ben_oipl_f
set drvbl_fctr_apls_rts_flag = 'N'
where oipl_id = i.oipl_id
and drvbl_fctr_apls_rts_flag = 'Y'
and not exists (select null
from ben_age_rt_f art,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.oipl_id = i.oipl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
and not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.oipl_id = i.oipl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
and not exists (select null
from ben_comp_lvl_rt_f clr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.oipl_id = i.oipl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
and not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.oipl_id = i.oipl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
and not exists (select null
from ben_los_rt_f lsr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.oipl_id = i.oipl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
and not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr
where abr.oipl_id = i.oipl_id
and abr.acty_base_rt_id = avr.acty_base_rt_id
and avr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)
and not exists (select null
from ben_age_rt_f art,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.oipl_id = i.oipl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
and not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.oipl_id = i.oipl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
and not exists (select null
from ben_comp_lvl_rt_f clr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.oipl_id = i.oipl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
and not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.oipl_id = i.oipl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
and not exists (select null
from ben_los_rt_f lsr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.oipl_id = i.oipl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
and not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_actl_prem_f apr,
ben_actl_prem_vrbl_rt_f apv
where apr.oipl_id = i.oipl_id
and apr.actl_prem_id = apv.actl_prem_id
and apv.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id)
and not exists (select null
from ben_age_rt_f art,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.oipl_id = i.oipl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = art.vrbl_rt_prfl_id)
and not exists (select null
from ben_cmbn_age_los_rt_f cmr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.oipl_id = i.oipl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = cmr.vrbl_rt_prfl_id)
and not exists (select null
from ben_comp_lvl_rt_f clr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.oipl_id = i.oipl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = clr.vrbl_rt_prfl_id)
and not exists (select null
from ben_hrs_wkd_in_perd_rt_f hwr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.oipl_id = i.oipl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = hwr.vrbl_rt_prfl_id)
and not exists (select null
from ben_los_rt_f lsr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.oipl_id = i.oipl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = lsr.vrbl_rt_prfl_id)
and not exists (select null
from ben_pct_fl_tm_rt_f pfr,
ben_cvg_amt_calc_mthd_f ccm,
ben_bnft_vrbl_rt_f bvr
where ccm.oipl_id = i.oipl_id
and ccm.cvg_amt_calc_mthd_id = bvr.cvg_amt_calc_mthd_id
and bvr.vrbl_rt_prfl_id = pfr.vrbl_rt_prfl_id);