APPS.BEN_DERIVABLE_FACTOR SQL Statements

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

Line: 60

    select null
    from   dba_tab_columns tab, user_synonyms syn
    where  syn.synonym_name = upper(p_tab_name)
    and    tab.column_name = upper(p_col_name)
    and    tab.owner      =  syn.table_owner
    and    tab.table_name = syn.table_name
    and    tab.owner=upper(p_oracle_schema);

Line: 101

  if p_event not in ('CREATE','DELETE') then
    --
    fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');

Line: 124

    l_strg := 'update '||p_table_name||' t  set drvbl_fctr_prtn_elig_flag = :1

               where '||p_col_name|| '= :2 and (
                   exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||' = :3 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = eap.eligy_prfl_id)  or
                  exists  (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||' = :4 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecp.eligy_prfl_id) or
                  exists  (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||'= :5 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecl.eligy_prfl_id) or
                  exists  (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||'= :6 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ehw.eligy_prfl_id) or
                  exists  (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||'= :7 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = els.eligy_prfl_id) or
                  exists  (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||'= :8  and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = epf.eligy_prfl_id))';

Line: 153

  elsif p_event = 'DELETE' then
    hr_utility.set_location('update  event ',10);

Line: 155

    l_strg := 'update '||p_table_name||' t  set drvbl_fctr_prtn_elig_flag = :1
               where '||p_col_name|| '= :2  and
               not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||' = :3 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = eap.eligy_prfl_id)  and
               not exists (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||' = :4 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecp.eligy_prfl_id) and
               not exists (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||'= :5 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecl.eligy_prfl_id) and
               not exists (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||'= :6 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ehw.eligy_prfl_id) and
               not exists (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||'= :7 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = els.eligy_prfl_id) and
               not exists (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.'||p_col_name||'= :8 and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = epf.eligy_prfl_id)';

Line: 195

  cursor c1 is select pgm_id,pl_id,ptip_id,plip_id,oipl_id
      from ben_prtn_elig_f epa, ben_prtn_elig_prfl_f cep
      where cep.eligy_prfl_id = p_eligy_prfl_id and
            cep.prtn_elig_id = epa.prtn_elig_id;

Line: 216

  if p_event not in ('CREATE','DELETE') then
    --
    fnd_message.set_name('BEN','BEN_92466_EVENT_HANDLER');

Line: 228

       update ben_pgm_f set drvbl_fctr_prtn_elig_flag = 'Y'
           where pgm_id = i.pgm_id;

Line: 233

       update ben_pl_f set drvbl_fctr_prtn_elig_flag = 'Y'
           where pl_id = i.pl_id;

Line: 238

       update ben_plip_f set drvbl_fctr_prtn_elig_flag = 'Y'
           where plip_id = i.plip_id;

Line: 243

       update ben_ptip_f set drvbl_fctr_prtn_elig_flag = 'Y'
           where ptip_id = i.ptip_id;

Line: 248

       update ben_oipl_f set drvbl_fctr_prtn_elig_flag = 'Y'
           where oipl_id = i.oipl_id;

Line: 252

  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_prtn_elig_flag = 'N'
         where pgm_id = i.pgm_id and
               not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = eap.eligy_prfl_id)  and
              not exists  (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecp.eligy_prfl_id) and
              not exists  (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecl.eligy_prfl_id) and
              not exists  (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ehw.eligy_prfl_id) and
              not exists  (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = els.eligy_prfl_id) and
              not exists  (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pgm_id = i.pgm_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = epf.eligy_prfl_id);

Line: 286

        update ben_pl_f  set drvbl_fctr_prtn_elig_flag = 'N'
         where pl_id = i.pl_id and
               not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = eap.eligy_prfl_id)  and
              not exists  (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecp.eligy_prfl_id) and
              not exists  (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecl.eligy_prfl_id) and
              not exists  (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ehw.eligy_prfl_id) and
              not exists  (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = els.eligy_prfl_id) and
              not exists  (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.pl_id = i.pl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = epf.eligy_prfl_id);

Line: 316

        update ben_plip_f  set drvbl_fctr_prtn_elig_flag = 'N'
         where plip_id = i.plip_id and
               not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = eap.eligy_prfl_id)  and
              not exists  (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecp.eligy_prfl_id) and
              not exists  (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecl.eligy_prfl_id) and
              not exists  (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ehw.eligy_prfl_id) and
              not exists  (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = els.eligy_prfl_id) and
              not exists  (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.plip_id = i.plip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = epf.eligy_prfl_id);

Line: 346

        update ben_ptip_f  set drvbl_fctr_prtn_elig_flag = 'N'
         where ptip_id = i.ptip_id and
               not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = eap.eligy_prfl_id)  and
              not exists  (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecp.eligy_prfl_id) and
              not exists  (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecl.eligy_prfl_id) and
              not exists  (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ehw.eligy_prfl_id) and
              not exists  (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = els.eligy_prfl_id) and
              not exists  (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.ptip_id = i.ptip_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = epf.eligy_prfl_id);

Line: 376

        update ben_oipl_f set drvbl_fctr_prtn_elig_flag = 'N'
         where oipl_id = i.oipl_id and
               not exists (select null from ben_elig_age_prte_f eap, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = eap.eligy_prfl_id)  and
              not exists  (select null from ben_elig_cmbn_age_los_prte_f ecp, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecp.eligy_prfl_id) and
              not exists  (select null from ben_elig_comp_lvl_prte_f ecl, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ecl.eligy_prfl_id) and
              not exists  (select null from ben_elig_hrs_wkd_prte_f ehw, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = ehw.eligy_prfl_id) and
              not exists  (select null from ben_elig_los_prte_f els, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = els.eligy_prfl_id) and
              not exists  (select null from ben_elig_pct_fl_tm_prte_f epf, ben_prtn_elig_f epa,
                           ben_prtn_elig_prfl_f cep
                           where epa.oipl_id = i.oipl_id and epa.prtn_elig_id = cep.prtn_elig_id and
                           cep.eligy_prfl_id = epf.eligy_prfl_id);