APPS.BEN_TEST_HARNESS SQL Statements

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

Line: 39

    l_tmpclcode_va.delete;

Line: 40

    l_tmpclcodecnt_va.delete;

Line: 41

    l_tmpclsumovn_va.delete;

Line: 42

    l_tmpclminesd_va.delete;

Line: 43

    l_tmpclmineed_va.delete;

Line: 346

    select ERROR_MESSAGE_CODE,
           max(person_id) mx_perid,
           count(*) cnt
    from ben_reporting
    where benefit_action_id = c_bft_id
    and   ERROR_MESSAGE_CODE is not null
    group by ERROR_MESSAGE_CODE
    order by count(*) desc;

Line: 1396

    select bft.last_update_date
    from ben_benefit_actions bft
    where bft.benefit_action_id = c_bft_id;

Line: 1407

    select pil.ler_id,
           pil.LF_EVT_OCRD_DT
    from ben_per_in_ler pil
    where pil.per_in_ler_id =
      (select max(pil1.per_in_ler_id)
       from ben_per_in_ler pil1
       where pil1.person_id = pil.person_id
       and pil1.PER_IN_LER_STAT_CD not in ('VOIDD','BCKDT')
      )
    and pil.person_id = c_per_id
    and pil.ler_id = c_ler_id;

Line: 1425

    select rbv.rollup_code,
           rbv.rollup_count,
           rbv.rollup_sumovn,
           rbv.rollup_minesd,
           rbv.rollup_mineed,
           rbv.rollup_id_string
    from ben_rollup_rbv_summary rbv,
         ben_batch_actions pba
    where pba.batch_id = c_bft_id
    and   rbv.batch_action_id = pba.batch_action_id
    and   rbv.rollup_count > 0
    order by rbv.rollup_id;

Line: 1452

    select bft.benefit_action_id,
           bft.mode_cd,
           bft.process_date,
           bft.last_update_date,
           bft.business_group_id,
           bft.BENFTS_GRP_ID,
           bft.person_id,
           bft.pgm_id,
           bft.pl_id,
           bft.COMP_SELECTION_RL,
           bft.AUDIT_LOG_FLAG,
           bft.VALIDATE_FLAG,
           bft.LF_EVT_OCRD_DT
    from ben_benefit_actions bft
    where bft.last_update_date > c_bft_credt-c_duration
    and   bft.last_update_date < c_bft_credt
    and   bft.mode_cd                   = c_mode_cd
    and   bft.process_date              = c_process_date
    and   bft.business_group_id         = c_bgp_id
    and   nvl(bft.ler_id,-1)            = nvl(c_ler_id,-1)
    and   nvl(bft.person_id,-1)         = nvl(c_per_id,-1)
    and   nvl(bft.pgm_id,-1)            = nvl(c_pgm_id,-1)
    and   nvl(bft.pl_id,-1)             = nvl(c_pl_id,-1)
    and   nvl(bft.opt_id,-1)            = nvl(c_opt_id,-1)
    and   nvl(bft.BENFTS_GRP_ID,-1)     = nvl(c_bfg_id,-1)
    and   bft.VALIDATE_FLAG = 'B'
    and exists
      (select 1
       from ben_batch_actions pba
       where pba.batch_id = bft.benefit_action_id
      )
    order by bft.benefit_action_id desc;

Line: 1489

    select bgp.name
    from per_business_groups bgp
    where bgp.business_group_id = c_bgp_id;

Line: 1497

    select per.full_name
    from per_all_people_f per
    where per.person_id = c_per_id;

Line: 1505

    select pgm.name
    from ben_pgm_f pgm
    where pgm.pgm_id = c_pgm_id;

Line: 1513

    select pln.name
    from ben_pl_f pln
    where pln.pl_id = c_pl_id;

Line: 1521

    select ler.name
    from ben_ler_f ler
    where ler.ler_id = c_ler_id;

Line: 1529

    select opt.name
    from ben_opt_f opt
    where opt.opt_id = c_opt_id;

Line: 1537

    select text
    from ben_reporting
    where benefit_action_id = c_bft_id
    and   text like '%ORA-%'
    order by THREAD_ID, REPORTING_ID;

Line: 1548

    select adf.FILENAME,
           adv.VERSION,
           adv.last_update_date
    from ad_files adf,
         AD_FILE_VERSIONS adv
    where adf.file_id = adv.file_id
    and adf.LAST_UPDATE_DATE >
      (select bft.last_update_date
       from ben_benefit_actions bft
       where bft.benefit_action_id = c_basebft_id)
    and adf.LAST_UPDATE_DATE <
      (select bft.last_update_date
       from ben_benefit_actions bft
       where bft.benefit_action_id = c_bft_id)
    and adf.APP_SHORT_NAME = 'BEN'
    and adf.SUBDIR = 'patch/115/sql'
    order by adv.last_update_date desc;

Line: 1572

    select adb.APPLICATION_SHORT_NAME,
           adb.BUG_NUMBER,
           adb.last_update_date
    from ad_bugs adb
    where adb.LAST_UPDATE_DATE >
      (select bft.last_update_date
       from ben_benefit_actions bft
       where bft.benefit_action_id = c_basebft_id)
    and adb.LAST_UPDATE_DATE <
      (select bft.last_update_date
       from ben_benefit_actions bft
       where bft.benefit_action_id = c_bft_id)
    and adb.APPLICATION_SHORT_NAME = 'BEN'
    order by adb.last_update_date desc;

Line: 1593

    select rep.person_id
    from ben_reporting rep
    where rep.benefit_action_id  = c_bft_id
    and   rep.ERROR_MESSAGE_CODE = c_err_cd;

Line: 1602

    select bpi.ELPSD_TM
    from BEN_BATCH_PROC_INFO bpi
    where bpi.benefit_action_id = c_bft_id;

Line: 1623

      select count(*)
      from ben_person_actions
      where benefit_action_id = c_bft_id
      and   action_status_cd = 'P';

Line: 1632

      select count(*)
      from ben_person_actions
      where benefit_action_id = c_bft_id
      and   action_status_cd = 'E';

Line: 1641

      select count(*)
      from ben_person_actions
      where benefit_action_id = c_bft_id
      and   action_status_cd = 'U';

Line: 1706

      select bbr.last_update_login,
             bft.creation_date,
             max(bbr.last_update_date) max_lud,
             count(*) cnt,
             round((max(bbr.last_update_date)-bft.creation_date)*(24*3600),2) tot_secs,
             round(((max(bbr.last_update_date)-bft.creation_date)*(24*3600))/count(*),2) avg_secs
      from ben_batch_ranges bbr,
           ben_benefit_actions bft
      where bbr.benefit_action_id = c_bft_id
      and   bbr.benefit_action_id = bft.benefit_action_id
      group by bbr.last_update_login,
               bft.creation_date
      order by count(*) desc;

Line: 1818

      select ccr.request_id,
             ccr.ORACLE_PROCESS_ID,
             ccr.ORACLE_SESSION_ID,
             ccr.PARENT_REQUEST_ID,
             ccr.phase_code,
             ccr.status_code,
             substr(replace(replace(ccr.completion_text,fnd_global.local_chr(10),' ')
                                             ,fnd_global.local_chr(13),' '),1,1000),
             count(*)
      from fnd_concurrent_requests ccr,
           ben_benefit_actions bft,
           ben_batch_ranges bbr
      where ccr.parent_request_id = bft.request_id
      and   bbr.last_update_login = ccr.last_update_login
      and   bft.benefit_action_id = bbr.benefit_action_id
      and   bft.benefit_action_id = c_bft_id
      group by ccr.request_id,
               ccr.ORACLE_PROCESS_ID,
               ccr.ORACLE_SESSION_ID,
               ccr.PARENT_REQUEST_ID,
               ccr.phase_code,
               ccr.status_code,
               substr(replace(replace(ccr.completion_text,fnd_global.local_chr(10),' ')
                                             ,fnd_global.local_chr(13),' '),1,1000);

Line: 1847

      select ccr.request_id,
             ccr.ORACLE_PROCESS_ID,
             ccr.ORACLE_SESSION_ID,
             ccr.PARENT_REQUEST_ID,
             ccr.phase_code,
             ccr.status_code,
             substr(replace(replace(ccr.completion_text,fnd_global.local_chr(10),' ')
                                             ,fnd_global.local_chr(13),' '),1,1000),
             count(*)
      from fnd_concurrent_requests ccr,
           ben_benefit_actions bft
      where ccr.request_id = bft.request_id
      and   bft.benefit_action_id = c_bft_id
      group by ccr.request_id,
               ccr.ORACLE_PROCESS_ID,
               ccr.ORACLE_SESSION_ID,
               ccr.PARENT_REQUEST_ID,
               ccr.phase_code,
               ccr.status_code,
               substr(replace(replace(ccr.completion_text,fnd_global.local_chr(10),' ')
                                             ,fnd_global.local_chr(13),' '),1,1000);

Line: 2119

      select text,
             max(person_id) mx_perid,
             count(*) cnt
      from ben_reporting
      where benefit_action_id = c_bft_id
      and   text like '%ORA-%'
      group by text
      order by count(*) desc;

Line: 2202

  l_reptext_va.delete;

Line: 2226

                   ||' and bft.last_update_date '
                   ||'   between :st_date and :end_date ';

Line: 2241

    l_sel_str   := ' select bft.benefit_action_id, '
                   ||'      bft.business_group_id, '
                   ||'      bft.person_id, '
                   ||'      bft.ler_id, '
                   ||'      bft.process_date ';

Line: 2308

  l_where_str := ' where bft.last_update_date '
                 ||'   between :st_date and :end_date '
                 ||' and pba.batch_id          = bft.benefit_action_id '
                 ||' and pba.batch_type        = '||''''||'BEN_BFT'||'''';

Line: 2330

  l_sql_str := 'select max(bft.benefit_action_id) max_bftid, '
               ||'     bft.process_date, '
               ||'     bft.mode_cd, '
               ||'     bft.business_group_id, '
               ||'     bft.BENFTS_GRP_ID, '
               ||'     bft.person_id, '
               ||'     bft.pgm_id, '
               ||'     bft.pl_id, '
               ||'     bft.ler_id, '
               ||'     bft.opt_id, '
               ||'     max(bft.creation_date) max_credt, '
               ||'     max(bft.request_id) '
               ||' from ben_benefit_actions bft, '
               ||'      ben_batch_actions pba '
               ||' '||l_where_str
               ||' group by bft.process_date, '
               ||'          bft.mode_cd, '
               ||'          bft.business_group_id, '
               ||'          bft.BENFTS_GRP_ID, '
               ||'          bft.person_id, '
               ||'          bft.pgm_id, '
               ||'          bft.pl_id, '
               ||'          bft.ler_id, '
               ||'          bft.opt_id '
               ||' order by max(bft.benefit_action_id) desc ';

Line: 2463

      ,p_comp_selection_rl      => null
      ,p_person_selection_rl    => null
      ,p_ler_id                 => null
      ,p_organization_id        => null
      ,p_benfts_grp_id          => null
      ,p_location_id            => null
      ,p_pstl_zip_rng_id        => null
      ,p_rptg_grp_id            => null
      ,p_pl_typ_id              => null
      ,p_opt_id                 => null
      ,p_eligy_prfl_id          => null
      ,p_vrbl_rt_prfl_id        => null
      ,p_legal_entity_id        => null
      ,p_payroll_id             => null
      ,p_debug_messages_flag    => 'N'
      ,p_audit_log_flag         => 'N'
      ,p_lmt_prpnip_by_org_flag => 'N'
      ,p_request_id             => fnd_global.conc_request_id
      ,p_program_application_id => fnd_global.prog_appl_id
      ,p_program_id             => fnd_global.conc_program_id
      ,p_program_update_date    => sysdate
      ,p_object_version_number  => l_thnbft_ovn
      ,p_lf_evt_ocrd_dt         => null
      ,p_effective_date         => null
      --
      ,p_benefit_action_id      => l_thnbft_id
      );

Line: 2632

      l_ccrerrcomptxt_va.delete;

Line: 2633

      l_ccrhanderrcomptxt_va.delete;

Line: 2674

      g_alertsev1reas_va.delete;

Line: 2675

      g_alertsev1bftid_va.delete;

Line: 2676

      g_alertsev1prevbftid_va.delete;

Line: 2677

      g_alertsev2reas_va.delete;

Line: 2678

      g_alertsev2bftid_va.delete;

Line: 3202

        g_alertsev1reas_va.delete;

Line: 3203

        g_alertsev1bftid_va.delete;

Line: 3204

        g_alertsev1prevbftid_va.delete;

Line: 3205

        g_alertsev2reas_va.delete;

Line: 3206

        g_alertsev2bftid_va.delete;

Line: 3335

        l_mmclccode_va.delete;

Line: 3336

        l_mmoclcount_va.delete;

Line: 3337

        l_mmnclcount_va.delete;

Line: 3340

        l_mtclccode_va.delete;

Line: 3341

        l_mtoclcount_va.delete;

Line: 3342

        l_mtnclcount_va.delete;

Line: 3350

          l_mmrltyp_rbvclcd_va.delete;

Line: 3563

                                          ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');

Line: 3570

                                          ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');

Line: 3642

                                      ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');

Line: 3676

          l_apperrtypdiscr_messcd_va.delete;

Line: 3677

          l_apperrtypdiscr_cnt_va.delete;

Line: 3765

          l_newapperr_peridva.delete;

Line: 3771

            l_periddiscrep_errcd_va.delete;

Line: 3772

            l_periddiscrep_perid_va.delete;

Line: 3886

                                      ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');

Line: 3963

                                          ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');

Line: 4056

                (p_lud          => row.last_update_date
                ,p_mmperid_va   => l_mmperid_va
                ,p_mmperlud_va  => l_mmperlud_va
                ,p_mmcombnm_va  => l_mmcombnm_va
                ,p_mmcombnm2_va => l_mmcombnm2_va
                ,p_mmcombid_va  => l_mmcombid_va
                ,p_mmcombid2_va => l_mmcombid2_va
                ,p_mmcnt_va     => l_mmcnt_va
                ,p_exclperid_va => l_mmexclperid_leva
                );

Line: 4137

                    if l_mmperlud_va(subvaen) > row.last_update_date
                    then
                      --
                      l_reptext := 'LE Change: '||l_reptext;

Line: 4226

                                          ||' Time: '||to_char(row.LAST_UPDATE_DATE,'DD-MON-YYYY-HH24-MI-SS');

Line: 4344

                  (p_lud          => row.last_update_date
                  ,p_mmperid_va   => l_mmperid_va
                  ,p_mmperlud_va  => l_mmperlud_va
                  ,p_mmcombnm_va  => l_mmcombnm_va
                  ,p_mmcombnm2_va => l_mmcombnm2_va
                  ,p_mmcombid_va  => l_mmcombid_va
                  ,p_mmcombid2_va => l_mmcombid2_va
                  ,p_mmcnt_va     => l_mmcnt_va
                  ,p_exclperid_va => l_mmexclperid_leva
                  );

Line: 4431

                      if l_mmperlud_va(subvaen) > row.last_update_date
                      then
                        --
                        l_reptext := 'LE Change: '||l_reptext;

Line: 4624

        select ben_reporting_s.nextval into
        l_num1_col(repvaen)
        from sys.dual;

Line: 4695

        insert into ben_reporting
          (reporting_id,
           benefit_action_id,
           thread_id,
           sequence,
           text,
           object_version_number,
           rep_typ_cd,
           error_message_code,
           national_identifier,
           related_person_ler_id,
           temporal_ler_id,
           ler_id,
           person_id,
           pgm_id,
           pl_id,
           related_person_id,
           oipl_id,
           pl_typ_id,
           actl_prem_id,
           val,
           mo_num,
           yr_num)
         values
          (l_num1_col(insvaen),
           l_num2_col(insvaen),
           l_num3_col(insvaen),
           l_num4_col(insvaen),
           l_var1_col(insvaen),
           l_num5_col(insvaen),
           l_var2_col(insvaen),
           l_var3_col(insvaen),
           l_var4_col(insvaen),
           l_num6_col(insvaen),
           l_num7_col(insvaen),
           l_num8_col(insvaen),
           l_num9_col(insvaen),
           l_num10_col(insvaen),
           l_num11_col(insvaen),
           l_num12_col(insvaen),
           l_num13_col(insvaen),
           l_num14_col(insvaen),
           l_num15_col(insvaen),
           l_num16_col(insvaen),
           l_num17_col(insvaen),
           l_num18_col(insvaen));

Line: 4769

    select rep.ERROR_MESSAGE_CODE
    from ben_reporting rep
    where rep.benefit_action_id = c_bft_id
    and   rep.ERROR_MESSAGE_CODE is not null;

Line: 4778

    select ERROR_MESSAGE_CODE,
           max(person_id) mx_perid,
           count(*) cnt
    from ben_reporting
    where benefit_action_id = c_bft_id
    and   ERROR_MESSAGE_CODE is not null
    group by ERROR_MESSAGE_CODE
    order by count(*) desc;

Line: 4791

    select rep.text,
           rep.thread_id,
           rep.person_id,
           rep.last_update_login
    from ben_reporting rep
    where rep.benefit_action_id = c_bft_id
    order by thread_id, reporting_id;

Line: 4803

    select count(*)
    from BEN_EXT_RSLT_DTL
    where EXT_RSLT_ID = c_ext_rslt_id;

Line: 4811

    select TYP_CD,
           ERR_NUM,
           count(*) cnt
    from BEN_EXT_RSLT_ERR
    where EXT_RSLT_ID = c_ext_rslt_id
    group by TYP_CD,
          ERR_NUM
    order by count(*) desc;

Line: 4825

    select ERR_NUM,
           ERR_TXT,
           count(*) cnt
    from BEN_EXT_RSLT_ERR
    where EXT_RSLT_ID = c_ext_rslt_id
    and   TYP_CD = c_typ_cd
    group by ERR_NUM,
             ERR_TXT
    order by count(*) desc;