eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BEN_EXTRACT SQL Statements

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

Line: 30


     select distinct a.low_lvl_cd
       from ben_ext_rcd          a,
            ben_ext_rcd_in_file  b
       where a.ext_rcd_id  = b.ext_rcd_id
         and b.ext_file_id = p_ext_file_id
         and b.rqd_flag = 'Y'
         -- Bug fix 1702733 - header/trailer type records should not be checked for required flag = 'Y' here
         --                   since they never get proccessed in xtrct_skltn. Also, ben_ext_person.process_ext_recs
         --                   processes only Detail Type records.
         --                   Hence the gtt_rcd_rqd_vals.rcd_found flag for 'H'/'T' type records, if retreived,
         --                   remains unchanged as FALSE, and this leads to raising of required_error exception
         --                   in ben_ext_person.process_ext_levels.
         and a.rcd_type_cd = 'D'
         -- end fix 1702733
         and a.low_lvl_cd <> 'P';

Line: 53


     select a.low_lvl_cd,b.seq_num
       from ben_ext_rcd          a,
            ben_ext_rcd_in_file  b
       where a.ext_rcd_id  = b.ext_rcd_id
         and b.ext_file_id = p_ext_file_id
         and b.rqd_flag = 'Y'
         and a.rcd_type_cd in ( 'D','S') ;   -- subheader

Line: 69


     select a.ext_rcd_id,
            b.sort1_data_elmt_in_rcd_id,
            b.sort2_data_elmt_in_rcd_id,
            b.sort3_data_elmt_in_rcd_id,
            b.sort4_data_elmt_in_rcd_id,
            b.ext_rcd_in_file_id,
            b.seq_num,
            b.sprs_cd,
            b.any_or_all_cd,
            a.rcd_type_cd,
            a.low_lvl_cd
       from ben_ext_rcd          a,
            ben_ext_rcd_in_file  b
       where a.ext_rcd_id  = b.ext_rcd_id
         and b.ext_file_id = p_ext_file_id
     order by b.seq_num;

Line: 238


    select name
    from per_business_groups_perf
    where business_group_id  = p_business_group_id;

Line: 244


/*    select spcl_hndl_flag,
           upd_cm_sent_dt_flag,
           ext_global_flag
    from ben_ext_dfn xdf
    where xdf.ext_dfn_id = p_ext_dfn_id;

Line: 251


    select xdf.spcl_hndl_flag,
           xdf.upd_cm_sent_dt_flag,
           decode(xdf.data_typ_cd,'CW','Y',xcr.ext_global_flag) ext_global_flag
    from ben_ext_dfn xdf,
         ben_ext_crit_prfl xcr
    where xdf.ext_dfn_id = p_ext_dfn_id
    and   xdf.ext_crit_prfl_id = xcr.ext_crit_prfl_id (+) ;

Line: 260


    select xcv.val_1
    from ben_ext_crit_val xcv,
         ben_ext_crit_typ xct
    where xct.ext_crit_prfl_id = p_ext_crit_prfl_id
    and   xct.ext_crit_typ_id = xcv.ext_crit_typ_id
    and   xct.crit_typ_cd = p_crit_typ_cd ;

Line: 360


    gtt_rcd_rqd_vals.DELETE;

Line: 420


         ben_person_actions_api.update_person_actions
          (p_person_action_id      =>l_pactid_va(vaen)
          ,p_action_status_cd      => 'P'
          ,p_object_version_number =>l_pactovn_va(vaen)
          ,p_effective_date        => p_effective_date);

Line: 434


           update ben_batch_ranges set range_status_cd = 'W'
           where range_id = p_range_id and range_status_cd = 'P';

Line: 458


           update ben_batch_ranges set range_status_cd = 'W'
           where range_id = p_range_id and range_status_cd = 'P';

Line: 503


   update ben_person_actions act
      set action_status_cd = 'P'
      where   person_id = person.person_id
        and   benefit_action_id = p_benefit_action_id;

Line: 522


    update ben_batch_ranges set range_status_cd = 'E'
      where range_id = p_range_id;

Line: 554


    select
      decode(sum(decode(a.low_lvl_cd,'P',1,0)),0,'N','Y')  g_per_lvl,
      decode(sum(decode(a.low_lvl_cd,'E',1,0)),0,'N','Y')  g_enrt_lvl,
      decode(sum(decode(a.low_lvl_cd,'PR',1,0)),0,'N','Y') g_prem_lvl,
      decode(sum(decode(a.low_lvl_cd,'D',1,0)),0,'N','Y')  g_dpnt_lvl,
      decode(sum(decode(a.low_lvl_cd,'Y',1,0)),0,'N','Y')  g_payroll_lvl,
      decode(sum(decode(a.low_lvl_cd,'G',1,0)),0,'N','Y')  g_elig_lvl,
      decode(sum(decode(a.low_lvl_cd,'F',1,0)),0,'N','Y')  g_flex_lvl,
      decode(sum(decode(a.low_lvl_cd,'B',1,0)),0,'N','Y')  g_bnf_lvl,
      decode(sum(decode(a.low_lvl_cd,'A',1,0)),0,'N','Y')  g_actn_lvl,
      decode(sum(decode(a.low_lvl_cd,'R',1,0)),0,'N','Y')  g_runrslt_lvl,
      decode(sum(decode(a.low_lvl_cd,'CO',1,0)),0,'N','Y') g_contact_lvl,
      decode(sum(decode(a.low_lvl_cd,'ED',1,0)),0,'N','Y') g_eligdpnt_lvl,
      decode(sum(decode(a.low_lvl_cd,'WG',1,0)),0,'N','Y') g_cwbgr_lvl,
      decode(sum(decode(a.low_lvl_cd,'WR',1,0)),0,'N','Y') g_cwbrt_lvl,
      decode(sum(decode(a.low_lvl_cd,'OR',1,0)),0,'N','Y') g_org_lvl,               -- subheader
      decode(sum(decode(a.low_lvl_cd,'PO',1,0)),0,'N','Y') g_pos_lvl,               -- subheader
      decode(sum(decode(a.low_lvl_cd,'JB',1,0)),0,'N','Y') g_job_lvl,               -- subheader
      decode(sum(decode(a.low_lvl_cd,'GR',1,0)),0,'N','Y') g_grd_lvl,               -- subheader
      decode(sum(decode(a.low_lvl_cd,'LO',1,0)),0,'N','Y') g_loc_lvl,               -- subheader
      decode(sum(decode(a.low_lvl_cd,'PY',1,0)),0,'N','Y') g_pay_lvl,               -- subheader
      decode(sum(decode(a.low_lvl_cd,'T',1,0)),0,'N','Y') g_otl_summ_lvl,
      decode(sum(decode(a.low_lvl_cd,'TS',1,0)),0,'N','Y') g_otl_detl_lvl
      from  ben_ext_rcd             a,
            ben_ext_rcd_in_file     b
      where a.ext_rcd_id  = b.ext_rcd_id
      and   b.ext_file_id = p_ext_file_id;

Line: 583


    select
       decode(sum(decode(e.csr_cd,'ADR',1,0)),0,'N','Y')   g_addr_csr,
       decode(sum(decode(e.csr_cd,'ASG',1,0)),0,'N','Y')   g_asg_csr,
       decode(sum(decode(e.csr_cd,'PHN',1,0)),0,'N','Y')   g_phn_csr,
       decode(sum(decode(e.csr_cd,'RT',1,0)),0,'N','Y')    g_rt_csr,
       decode(sum(decode(e.csr_cd,'LER',1,0)),0,'N','Y')   g_ler_csr,
       decode(sum(decode(e.csr_cd,'BGR',1,0)),0,'N','Y')   g_bgr_csr,
       decode(sum(decode(e.csr_cd,'MA',1,0)),0,'N','Y')    g_ma_csr,
       decode(sum(decode(e.csr_cd,'BP',1,0)),0,'N','Y')    g_bp_csr,
       decode(sum(decode(e.csr_cd,'BA',1,0)),0,'N','Y')    g_ba_csr,
       decode(sum(decode(e.csr_cd,'CHCRT',1,0)),0,'N','Y') g_chcrt_csr,
       decode(sum(decode(e.csr_cd,'CHC',1,0)),0,'N','Y')   g_chc_csr,
       decode(sum(decode(e.csr_cd,'CMA',1,0)),0,'N','Y')   g_cma_csr,
       decode(sum(decode(e.csr_cd,'DP',1,0)),0,'N','Y')    g_dp_csr,
       decode(sum(decode(e.csr_cd,'DA',1,0)),0,'N','Y')    g_da_csr,
       decode(sum(decode(e.csr_cd,'DPCP',1,0)),0,'N','Y')  g_dpcp_csr,
       decode(sum(decode(e.csr_cd,'BG',1,0)),0,'N','Y')    g_bg_csr,
       decode(sum(decode(e.csr_cd,'BB1',1,0)),0,'N','Y')   g_bb1_csr,
       decode(sum(decode(e.csr_cd,'BB2',1,0)),0,'N','Y')   g_bb2_csr,
       decode(sum(decode(e.csr_cd,'BB3',1,0)),0,'N','Y')   g_bb3_csr,
       decode(sum(decode(e.csr_cd,'BB4',1,0)),0,'N','Y')   g_bb4_csr,
       decode(sum(decode(e.csr_cd,'BB5',1,0)),0,'N','Y')   g_bb5_csr,
       decode(sum(decode(e.csr_cd,'PPCP',1,0)),0,'N','Y')  g_ppcp_csr,
       decode(sum(decode(e.csr_cd,'PGN',1,0)),0,'N','Y')   g_pgn_csr,
       decode(sum(decode(e.csr_cd,'ABS',1,0)),0,'N','Y')   g_abs_csr,
       decode(sum(decode(e.csr_cd,'PPREM',1,0)),0,'N','Y') g_pprem_csr,
       decode(sum(decode(e.csr_cd,'EPREM',1,0)),0,'N','Y') g_eprem_csr,
       decode(sum(decode(e.csr_cd,'FLXCR',1,0)),0,'N','Y') g_flxcr_csr,
       decode(sum(decode(e.csr_cd,'ERGRP',1,0)),0,'N','Y') g_ergrp_csr,
       decode(sum(decode(e.csr_cd,'PRGRP',1,0)),0,'N','Y') g_prgrp_csr,
       decode(sum(decode(e.csr_cd,'ASA',1,0)),0,'N','Y')   g_asa_csr,
       decode(sum(decode(e.csr_cd,'EPLYR',1,0)),0,'N','Y') g_eplyr_csr,
       decode(sum(decode(e.csr_cd,'PPLYR',1,0)),0,'N','Y') g_pplyr_csr,
       decode(sum(decode(e.csr_cd,'ELER',1,0)),0,'N','Y')  g_eler_csr,
       decode(sum(decode(e.csr_cd,'PLER',1,0)),0,'N','Y')  g_pler_csr,
       decode(sum(decode(e.csr_cd,'PMPR',1,0)),0,'N','Y')  g_pmpr_csr,
       decode(sum(decode(e.csr_cd,'PMTPR',1,0)),0,'N','Y') g_pmtpr_csr,
       decode(sum(decode(e.csr_cd,'INTRM',1,0)),0,'N','Y') g_intrm_csr,
       decode(sum(decode(e.csr_cd,'INT',1,0)),0,'N','Y')   g_int_csr,
       decode(sum(decode(e.csr_cd,'CBRA',1,0)),0,'N','Y')  g_cbra_csr,
       decode(sum(decode(e.csr_cd,'COA',1,0)),0,'N','Y')   g_coa_csr,
       decode(sum(decode(e.csr_cd,'COP',1,0)),0,'N','Y')   g_cop_csr,
       decode(sum(decode(e.csr_cd,'COED',1,0)),0,'N','Y')  g_coed_csr,
       decode(sum(decode(e.csr_cd,'COCD',1,0)),0,'N','Y')  g_cocd_csr,
       decode(sum(decode(e.csr_cd,'COB',1,0)),0,'N','Y')   g_cob_csr,
       decode(sum(decode(e.csr_cd,'COSL',1,0)),0,'N','Y')  g_cosl_csr,
       decode(sum(decode(e.csr_cd,'COEL',1,0)),0,'N','Y')  g_coel_csr,
       decode(sum(decode(e.csr_cd,'EDP',1,0)),0,'N','Y')   g_edp_csr,
       decode(sum(decode(e.csr_cd,'EDA',1,0)),0,'N','Y')   g_eda_csr,
       decode(sum(decode(e.csr_cd,'POS',1,0)),0,'N','Y')   g_pos_csr,
       decode(sum(decode(e.csr_cd,'SUP',1,0)),0,'N','Y')   g_sup_csr,
       decode(sum(decode(e.csr_cd,'BSL',1,0)),0,'N','Y')   g_bsl_csr,
       decode(sum(decode(e.csr_cd,'SHL',1,0)),0,'N','Y')   g_shl_csr,
       decode(sum(decode(e.csr_cd,'CWPG',1,0)),0,'N','Y')  g_cwbdg_csr ,
       decode(sum(decode(e.csr_cd,'CWPR',1,0)),0,'N','Y')  g_cwbawr_csr,
       decode(sum(decode(e.csr_cd,'CBRADM',1,0)),0,'N','Y')   g_cbradm_csr
       from ben_ext_rcd_in_file        a,
            ben_ext_rcd                b,
            ben_ext_data_elmt_in_rcd   c,
            ben_ext_data_elmt          d,
            ben_ext_fld                e
       where a.ext_file_id      = p_ext_file_id
       and   a.ext_rcd_id       = b.ext_rcd_id
       and   c.ext_rcd_id       = b.ext_rcd_id
       and   d.ext_data_elmt_id = c.ext_data_elmt_id
       and   e.ext_fld_id       = d.ext_fld_id;

Line: 652


  select ext_data_elmt_in_rcd_id1,
         ext_data_elmt_in_rcd_id2
  from  ben_Ext_file exf
  where exf.ext_file_id = p_file_id ;

Line: 660


  select  exf.short_name
  from ben_ext_fld  exf,
       ben_Ext_data_elmt_in_rcd edr,
       ben_ext_data_elmt        ede
  where edr.ext_data_elmt_in_rcd_id = p_data_elmt_in_rcd_id
    and edr.ext_data_elmt_id     = ede.ext_Data_elmt_id
    and ede.ext_fld_id           = exf.ext_fld_id (+)
    ;