APPS.BEN_EXT_UTIL SQL Statements

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

Line: 137

     select 'x'
       from ben_ext_rslt_err xre
       where xre.ext_rslt_id = p_ext_rslt_id --xre.request_id = fnd_global.conc_request_id
             and xre.person_id = p_person_id
             and xre.err_num = p_err_num
             and ( /*p_typ_cd <> 'W'
                   or*/  p_err_name is null
                   or  p_err_name = xre.err_txt
                 ) ;

Line: 169

                     p_program_update_date   => sysdate,
                     p_effective_date        => sysdate
                    );

Line: 193

  SELECT r.name name,
         count(d.ext_rslt_dtl_id) count
  FROM   ben_ext_rslt_dtl d,
         ben_ext_rcd r,
         ben_ext_rcd_in_file f,
         ben_ext_rslt rs,
         ben_ext_dfn df
  WHERE  d.ext_rslt_id(+) = p_ext_rslt_id
  AND    f.ext_rcd_id  = r.ext_rcd_id
  AND    r.ext_rcd_id  = d.ext_rcd_id (+)
  AND    f.ext_file_id = df.ext_file_id
  AND    df.ext_dfn_id = rs.ext_dfn_id
  AND    rs.ext_rslt_id = p_ext_rslt_id
  GROUP BY r.name, f.seq_num, f.ext_rcd_id
  ORDER BY f.seq_num;

Line: 211

  SELECT r.name name,
         count(d.ext_rslt_dtl_id) count
  FROM   ben_ext_rslt_dtl d,
         ben_ext_rcd r,
         ben_ext_rcd_in_file f,
         ben_ext_rslt rs,
         ben_ext_dfn df
  WHERE  d.ext_rslt_id(+) = p_ext_rslt_id
  AND    d.request_id(+)  = p_request_id
  AND    f.ext_rcd_id  = r.ext_rcd_id
  AND    r.ext_rcd_id  = d.ext_rcd_id (+)
  AND    f.ext_file_id = df.ext_file_id
  AND    df.ext_dfn_id = rs.ext_dfn_id
  AND    rs.ext_rslt_id = p_ext_rslt_id
  GROUP BY r.name, f.seq_num, f.ext_rcd_id
  ORDER BY f.seq_num;

Line: 229

  SELECT b.name rec_name
        ,count(ext_rslt_dtl_id)
  FROM   ben_ext_rslt_dtl a
        ,ben_ext_rcd b
  WHERE  a.ext_rcd_id = b.ext_rcd_id
  AND    a.ext_rslt_id = p_ext_rslt_id  --a.request_id = p_request_id
  GROUP BY b.name
  ORDER BY upper(b.name);

Line: 240

  SELECT count(r.name) count
  FROM   ben_ext_rcd r,
         ben_ext_rcd_in_file f,
         ben_ext_rslt rs,
         ben_ext_dfn df
  WHERE  f.ext_rcd_id   = r.ext_rcd_id
  AND    f.ext_file_id  = df.ext_file_id
  AND    df.ext_dfn_id  = rs.ext_dfn_id
  AND    rs.ext_rslt_id = p_ext_rslt_id
  AND    r.name         = p_name
  GROUP BY r.name;

Line: 337

  SELECT count(decode(b.rcd_type_cd, 'H', b.rcd_type_cd))
        ,count(decode(b.rcd_type_cd, 'D', b.rcd_type_cd))
        ,count(decode(b.rcd_type_cd, 'T', b.rcd_type_cd))
  FROM   ben_ext_rslt_dtl a
        ,ben_ext_rcd b
  WHERE  a.ext_rcd_id = b.ext_rcd_id
  AND    a.ext_rslt_id = p_ext_rslt_id;

Line: 346

  SELECT count(decode(b.rcd_type_cd, 'H', b.rcd_type_cd))
        ,count(decode(b.rcd_type_cd, 'D', b.rcd_type_cd))
        ,count(decode(b.rcd_type_cd, 'T', b.rcd_type_cd))
  FROM   ben_ext_rslt_dtl a
        ,ben_ext_rcd b
  WHERE  a.ext_rcd_id = b.ext_rcd_id
  AND    a.ext_rslt_id = p_ext_rslt_id;  --a.request_id = p_request_id;

Line: 355

  SELECT count(decode(b.rcd_type_cd, 'H', b.rcd_type_cd))
        ,count(decode(b.rcd_type_cd, 'D', b.rcd_type_cd))
        ,count(decode(b.rcd_type_cd, 'T', b.rcd_type_cd))
  FROM   ben_ext_rslt_dtl a
        ,ben_ext_rcd b
  WHERE  a.ext_rcd_id = b.ext_rcd_id
  AND    a.request_id = p_request_id
  AND    a.ext_rslt_id = p_ext_rslt_id;

Line: 413

  SELECT count(distinct person_id)
  FROM   ben_ext_rslt_dtl
  WHERE  ext_rslt_id = p_ext_rslt_id
  AND    person_id not in (0, 999999999999);

Line: 420

  SELECT count(distinct person_id)
  FROM   ben_ext_rslt_dtl
  WHERE  ext_rslt_id = p_ext_rslt_id  --request_id = p_request_id
  AND    person_id not in (0, 999999999999);

Line: 426

  SELECT count(distinct person_id)
  FROM   ben_ext_rslt_err
  WHERE  ext_rslt_id = p_ext_rslt_id  --request_id = l_request_id
  AND    person_id not in (0, 999999999999)
  AND    typ_cd <> 'W';

Line: 433

  SELECT request_id
  FROM   ben_ext_rslt
  WHERE  ext_rslt_id = p_ext_rslt_id;

Line: 513

  SELECT count(decode(typ_cd, 'F', typ_cd))
        ,count(decode(typ_cd, 'E', typ_cd))
        ,count(decode(typ_cd, 'W', typ_cd))
  FROM   ben_ext_rslt_err
  WHERE  ext_rslt_id = p_ext_rslt_id;   --request_id = l_request_id;

Line: 520

  SELECT request_id
  FROM   ben_ext_rslt
  WHERE  ext_rslt_id = p_ext_rslt_id;

Line: 598

  SELECT decode(lookup_type, 'BEN_EXT_ERR_TYP',
                '1' || lookup_code,
                'BEN_EXT_PROMPT',
                '2' || lookup_code) lookup_code,
         meaning
  FROM hr_lookups
  WHERE lookup_type in ('BEN_EXT_ERR_TYP', 'BEN_EXT_PROMPT');

Line: 808

  l_string := 'SELECT val_' || l_char_seq_num ||
              ' FROM ben_ext_rslt_dtl ' ||
              'WHERE ext_rslt_dtl_id = ' ||
              to_char(p_ext_rslt_dtl_id);

Line: 842

    select xcv.val_1, xcv.val_2, xct.excld_flag
    from ben_ext_crit_val xcv,
         ben_ext_crit_typ xct,
         ben_ext_dfn xdf
    where xdf.ext_dfn_id = p_ext_dfn_id
    and   xdf.ext_crit_prfl_id = xct.ext_crit_prfl_id
    and   xct.ext_crit_typ_id = xcv.ext_crit_typ_id
    and   xct.crit_typ_cd = 'CAD'; -- change actual date

Line: 854

    select xcv.val_1, xcv.val_2, xct.excld_flag
    from ben_ext_crit_val xcv,
         ben_ext_crit_typ xct,
         ben_ext_dfn xdf
    where xdf.ext_dfn_id = p_ext_dfn_id
    and   xdf.ext_crit_prfl_id = xct.ext_crit_prfl_id
    and   xct.ext_crit_typ_id = xcv.ext_crit_typ_id
    and   xct.crit_typ_cd = 'CED'; -- change effective date

Line: 944

    select xcv.val_1, xcv.val_2, xct.excld_flag
    from ben_ext_crit_val xcv,
         ben_ext_crit_typ xct,
         ben_ext_dfn xdf
    where xdf.ext_dfn_id = p_ext_dfn_id
    and   xdf.ext_crit_prfl_id = xct.ext_crit_prfl_id
    and   xct.ext_crit_typ_id = xcv.ext_crit_typ_id
    and   xct.crit_typ_cd = 'MTBSDT'; -- comm to be sent date

Line: 1021

    select xcv.val_1
    from ben_ext_crit_val xcv,
         ben_ext_crit_typ xct,
         ben_ext_dfn xdf
    where xdf.ext_dfn_id = p_ext_dfn_id
    and   xdf.ext_crit_prfl_id = xct.ext_crit_prfl_id
    and   xct.ext_crit_typ_id = xcv.ext_crit_typ_id
    and   xct.crit_typ_cd = 'PASOR'; -- person datetrack override date

Line: 1035

    select xcv.val_1
    from ben_ext_crit_val xcv,
         ben_ext_crit_typ xct,
         ben_ext_dfn xdf
    where xdf.ext_dfn_id = p_ext_dfn_id
    and   xdf.ext_crit_prfl_id = xct.ext_crit_prfl_id
    and   xct.ext_crit_typ_id = xcv.ext_crit_typ_id
    and   xct.crit_typ_cd = 'BDTOR'; -- benefits datetrack override date

Line: 1050

        select 'x' from
        per_all_assignments_f
        where person_id = ben_ext_person.g_person_id
          and primary_flag = 'Y'
          and p_effective_Date between effective_start_date
          and effective_end_date ;

Line: 1059

        select 'x' from
        per_all_assignments_f
        where person_id = ben_ext_person.g_person_id
          and primary_flag = 'Y'
          and assignment_type = 'E'  -- added by hmani bug 3629576
          and p_effective_Date between effective_start_date
          and effective_end_date ;

Line: 1069

        select 'x' from
        per_all_assignments_f
        where person_id = ben_ext_person.g_person_id
          and assignment_type = 'A'  -- applicatn does not have any primary
          and p_effective_Date between effective_start_date
          and effective_end_date ;

Line: 1078

        select  effective_end_date from
        per_all_assignments_f
        where person_id = ben_ext_person.g_person_id
          and primary_flag = 'Y'
          and assignment_type = 'E'  -- added by hmani bug 3629576
          and effective_start_date < p_effective_Date
          order by  effective_end_Date desc ;

Line: 1233

  SELECT max(run_end_dt)
  FROM   ben_ext_rslt
  WHERE  ext_dfn_id = p_ext_dfn_id
  AND    ext_stat_cd IN ('S', 'E', 'A','W');

Line: 1239

  SELECT max(eff_dt)
  FROM   ben_ext_rslt
  WHERE  ext_dfn_id = p_ext_dfn_id
  AND    ext_stat_cd IN ('S', 'E', 'A','W');

Line: 1245

  select start_date , end_date
  from ben_popl_yr_perd cpy ,
       ben_yr_perd yrp
  where
     cpy.yr_perd_id = yrp.yr_perd_id
     and cpy.pl_id = p_pl_id
     and p_abs_date
       between yrp.start_date and yrp.end_date  ;

Line: 1590

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -2)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -2),0.99) ,0.99,7,0))
           into l_rslt_dt  from dual ;

Line: 1596

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -3)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -3),0.99) ,0.99,7,0))
           into l_rslt_dt  from dual ;

Line: 1601

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -4)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -4),0.99) ,0.99,7,0))
           into l_rslt_dt  from dual ;

Line: 1607

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -5)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -5),0.99) ,0.99,7,0))
           into l_rslt_dt  from dual ;

Line: 1613

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -6)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -6),0.99) ,0.99,7,0))
           into l_rslt_dt  from dual ;

Line: 1619

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -7)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -7),0.99) ,0.99,7,0))
           into l_rslt_dt  from dual ;

Line: 1625

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -8)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -8),0.99) ,0.99,7,0))
           into l_rslt_dt  from dual ;

Line: 1637

         select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -2)
               +decode(greatest(2-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
                into l_rslt_dt from dual;

Line: 1642

         select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -3)
               +decode(greatest(3-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
                into l_rslt_dt from dual;

Line: 1647

         select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -4)
               +decode(greatest(4-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
                into l_rslt_dt from dual;

Line: 1652

         select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -5)
               +decode(greatest(5-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
                into l_rslt_dt from dual;

Line: 1657

         select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -6)
               +decode(greatest(6-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
                into l_rslt_dt from dual;

Line: 1662

         select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -7)
               +decode(greatest(7-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
                into l_rslt_dt from dual;

Line: 1667

         select trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -1)
               +decode(greatest(1-(to_number(to_char(p_abs_date,'D'))),0.99) ,0.99,7,0))
                into l_rslt_dt from dual;

Line: 1726

     select trunc(trunc(p_abs_date-15,'MM')
              +decode(greatest(to_number(to_char(p_abs_date,'DD')),15.9) , 15.9 , 15,0 )
            )
       into l_rslt_dt from dual;

Line: 1734

     select trunc(trunc(p_abs_date,'MM')
              +decode(greatest(to_number(to_char(p_abs_date,'DD')),15.9) , 15.9 , 0,15 )
            )
       into l_rslt_dt from dual;

Line: 1742

     select trunc(p_abs_date,'MM')+decode(greatest(to_number(to_char(p_abs_date,'DD')),15),15, -1, 14)
       into l_rslt_dt from dual;

Line: 1748

        select   decode(greatest(to_number(to_char(p_abs_date,'DD')),15),15,
           trunc(p_abs_date,'MM') +14 , trunc(add_months(p_abs_date,1),'MM') -1)
           into l_rslt_dt from dual;

Line: 1766

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -2)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -2),0.99) ,0.99,14,7))
           into l_rslt_dt  from dual ;

Line: 1771

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -3)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -3),0.99) ,0.99,14,7))
           into l_rslt_dt  from dual ;

Line: 1776

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -4)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -4),0.99) ,0.99,14,7))
           into l_rslt_dt  from dual ;

Line: 1781

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -5)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -5),0.99) ,0.99,14,7))
           into l_rslt_dt  from dual ;

Line: 1786

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -6)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -6),0.99) ,0.99,14,7))
           into l_rslt_dt  from dual ;

Line: 1791

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -7)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -7),0.99) ,0.99,14,7))
           into l_rslt_dt  from dual ;

Line: 1796

         select  trunc(p_abs_date-(to_number(to_char((p_abs_date),'D')) -1)
           -decode(greatest((to_number(to_char(p_abs_date,'D')) -1),0.99) ,0.99,14,7))
           into l_rslt_dt  from dual ;

Line: 1915

   SELECT  distinct pee.element_entry_id
     FROM  pay_element_type_rules petr
          ,pay_element_entries_f pee
     WHERE petr.element_set_id = p_element_set_id
       AND pee.element_type_id = petr.element_type_id
       AND pee.assignment_id = p_assignment_id
       AND (
            pee.effective_start_date <= p_end_date
           AND
            pee.effective_end_date >= p_start_date
          );

Line: 1931

   SELECT datetracked_event_id
     FROM pay_datetracked_events pde
         ,pay_dated_tables pdt
     WHERE event_group_id = p_event_group_id
       AND pde.dated_table_id = pdt.dated_table_id
       AND pde.update_type = 'P'
       AND pdt.table_name = 'PAY_ELEMENT_ENTRIES_F';

Line: 1949

   SELECT  distinct ppe.surrogate_key
     FROM  pay_element_type_rules petr
          ,pay_process_events ppe
          ,pay_event_updates peu
     WHERE petr.element_set_id = p_element_set_id
       AND ppe.assignment_id    = p_assignment_id
       AND ppe.noted_value      = petr.element_type_id
       AND peu.event_update_id = ppe.event_update_id
       AND peu.event_type = 'ZAP'
       AND ppe.effective_date BETWEEN p_start_date AND p_end_date;

Line: 1968

   p_element_entries_tab.element_entry_id.DELETE;

Line: 1969

   p_element_entries_tab.datetracked_event_id.DELETE;

Line: 2002

       l_element_entries_tab.delete ;

Line: 2081

     p_element_entries_tab.element_entry_id.DELETE;

Line: 2082

     p_element_entries_tab.datetracked_event_id.DELETE;

Line: 2107

   SELECT 'Y'
   FROM pay_datetracked_events pde
     ,pay_dated_tables pdt
   WHERE event_group_id = p_event_group_id
     AND pde.dated_table_id = pdt.dated_table_id
     AND (pdt.table_name = 'PAY_ELEMENT_ENTRIES_F'
        OR
        pdt.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
       )
     AND ROWNUM < 2;

Line: 2120

    SELECT element_set_id
    FROM pay_event_group_usages
    WHERE event_group_id = p_event_group_id;

Line: 2125

    SELECT datetracked_event_id
    FROM pay_datetracked_events pde
    WHERE pde.event_group_id = p_event_group_id;

Line: 2131

    SELECT datetracked_event_id
    FROM pay_datetracked_events pde
        ,pay_dated_tables pdt
    WHERE event_group_id = p_event_group_id
      AND pde.dated_table_id = pdt.dated_table_id
      AND pde.update_type = 'P'
      AND pdt.table_name = 'PAY_ELEMENT_ENTRIES_F';