APPS.BIV_RT_AGENT_MGR_PKG SQL Statements

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

Line: 63

    SELECT sr.incident_owner_id ,
           count(sr.incident_id) col2
      ' || l_from_list || l_where_clause || '
       and nvl(stat.close_flag,''N'') <> ''Y''
     group by sr.incident_owner_id
     order by 2 desc';

Line: 71

    SELECT sr.incident_owner_id,
           sum(decode(upper(sr.sr_creation_channel), ''WEB'',1,0)) col2
      ' || l_from_list || l_where_clause || '
       and sr.incident_date >= trunc(sysdate)
       and sr.incident_date <  trunc(sysdate+1)
       and sr.sr_creation_channel = ''WEB''
     group by sr.incident_owner_id
     order by 2 desc';

Line: 81

    SELECT sr.incident_owner_id,
           sum(decode(upper(sr.sr_creation_channel), ''PHONE'',1,0)) col2
      ' || l_from_list || l_where_clause || '
       and sr.incident_date >= trunc(sysdate)
       and sr.incident_date <  trunc(sysdate+1)
       and sr.sr_creation_channel = ''PHONE''
     group by sr.incident_owner_id
     order by 2 desc';

Line: 91

    SELECT sr.incident_owner_id ,
           count(sr.incident_id)  col2
      ' || l_from_list || l_where_clause || '
       and sr.close_date >= trunc(sysdate)
       and sr.close_date <  trunc(sysdate+1)
       and nvl(stat.close_flag,''N'') = ''Y''
     group by sr.incident_owner_id
     order by 2 desc';

Line: 103

    SELECT sr.incident_owner_id ,
           count(sr.incident_id) col2
      ' || l_from_list || l_where_clause || '
     group by sr.incident_owner_id
     order by 1 desc';

Line: 111

    SELECT distinct rsc.resource_id incident_owner_id,
           substr(rsc.source_name,1,50) col2
      ' || l_from_list3 || l_where_clause3 || '
     order by 2 asc';

Line: 118

      insert into biv_tmp_rt2(report_code, col1, col4, session_id)
       SELECT ''X'', incident_owner_id, col2, :session_id
         FROM (' || l_sql_sttmnt || ')
        WHERE rownum <= :rows_to_display ';-- || biv_core_pkg.g_disp;

Line: 135

   biv_core_pkg.update_base_col_desc('biv_tmp_rt2');

Line: 156

    SELECT sr.incident_owner_id col1,
           1 col4, 0 col6, 0 col8, 0 col10
      ' || l_from_list || l_where_clause || '
       and nvl(stat.close_flag,''N'') <> ''Y''
   UNION ALL
    SELECT sr.incident_owner_id,
           0,decode(upper(sr.sr_creation_channel), ''WEB'',1,0),
             decode(upper(sr.sr_creation_channel), ''PHONE'',1,0),0
      ' || l_from_list || l_where_clause || '
       and sr.incident_date >= trunc(sysdate)
       and sr.incident_date <  trunc(sysdate+1)
    UNION ALL
    SELECT sr.incident_owner_id ,
           0, 0, 0, 1
      ' || l_from_list || l_where_clause || '
       and sr.close_date >= trunc(sysdate)
       and sr.close_date <  trunc(sysdate+1)
       and nvl(stat.close_flag,''N'') = ''Y''';

Line: 178

      SELECT to_number(col1), 0,0,0,0
       FROM biv_tmp_rt2  rep
      WHERE session_id = :session_id
';

Line: 185

      SELECT col1, sum(col4) col4, sum(col6) col6,
                   sum(col8) col8, sum(col10) col10
        FROM (' || l_sql_sttmnt || ')
       group by col1';

Line: 191

       insert into biv_tmp_rt2 (report_code,rowno,
                         col1, col4, col6, col8, col10, col12, session_id)
         SELECT ''Y'', rownum,
                col1, col4, col6, col8, col10, ''N'', :session_id
           FROM (' || l_sql_sttmnt || ')
           ';

Line: 207

   biv_core_pkg.update_base_col_desc('biv_tmp_rt2');

Line: 217

       insert into biv_tmp_rt2(report_code,rowno,id,
                   col1,col2,col4,col6,col8,col10,col12,col3,col5,col7,col9,
                   session_id)
        SELECT ''BIV_RT_AGENT_REPORT'', rownum,col1,
               col1, col2, col4, col6, col8, col10,col12,
               col1 ,
               col1 ,
               col1 ,
               col1 ,
               session_id
         FROM (SELECT col2,col4, col6,col8,col10,col12, col1, session_id
                 FROM biv_tmp_rt2
                WHERE report_code = ''Y''
                 and session_id = :session_id
                order by ' || l_order_by || ', col2
              )
        WHERE rownum <= :rows_to_display ';-- || nvl(biv_core_pkg.g_disp,200);

Line: 255

   delete FROM biv_tmp_rt2
    WHERE report_code in ('X', 'Y')
      and session_id = l_session_id
     ;

Line: 260

   update biv_tmp_rt2
      set col1 = 'resource' || biv_core_pkg.g_param_sep ||
          --          'ID' || biv_core_pkg.g_value_sep || col1,
                  'p_resource_id' || biv_core_pkg.g_value_sep || col1,
          col3 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
                 biv_core_pkg.g_param_sep ||
                 'P_BLOG'|| biv_core_pkg.g_value_sep || 'Y',
          col7 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
                 biv_core_pkg.g_param_sep ||
                 'P_CHNL' || biv_core_pkg.g_value_sep || 'PHONE' ||
                 biv_core_pkg.g_param_sep || 'P_TODAY_ONLY' ||
                 biv_core_pkg.g_value_sep || 'Y',
          col5 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
                 biv_core_pkg.g_param_sep ||
                 'P_CHNL' || biv_core_pkg.g_value_sep || 'WEB' ||
                 biv_core_pkg.g_param_sep || 'P_TODAY_ONLY' ||
                 biv_core_pkg.g_value_sep || 'Y',
          col9 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
                 biv_core_pkg.g_param_sep ||
                 'P_CLOSE_SR' || biv_core_pkg.g_value_sep || 'Y' ||
                 biv_core_pkg.g_param_sep || 'P_CL_ST' ||
                 biv_core_pkg.g_value_sep || l_dt ||
                 biv_core_pkg.g_param_sep || 'P_CL_END' ||
                 biv_core_pkg.g_value_sep || l_dt ,
          creation_date = sysdate
    WHERE report_code = 'BIV_RT_AGENT_REPORT'
      and session_id = l_session_id;

Line: 289

    update biv_tmp_rt2 rpt
       set col12 = 'Y'
      WHERE session_id = l_session_id
       and report_code = 'BIV_RT_AGENT_REPORT'
       and exists ( SELECT 1 FROM JTF_RS_WEB_AVAILABLE_V avl
                     WHERE avl.resource_id = rpt.id);

Line: 299

  SELECT count(*) into l_ttl_recs
    FROM biv_tmp_rt2
   WHERE report_code = 'BIV_RT_AGENT_REPORT'
     and session_id = l_session_id;

Line: 307

     insert into biv_tmp_rt2 (report_code, rowno,
                              col4, col6, col8, col10, col13,session_id)
      SELECT report_code, max(rowno) + 1, sum(col4), sum(col6), sum(col8),
              sum(col10) ,'Y', session_id
        FROM biv_tmp_rt2
       WHERE session_id = l_session_id
        and report_code = 'BIV_RT_AGENT_REPORT'
       group by report_code, session_id;

Line: 323

   update biv_tmp_rt2
      set col2 = l_ttl_desc,
          col1 = 'resource' || biv_core_pkg.g_param_sep ||
                  'p_resource_id' || biv_core_pkg.g_value_sep || col1,
          col3 = l_new_param_str ||
                 'P_BLOG'|| biv_core_pkg.g_value_sep || 'Y',
          col7 = l_new_param_str ||
                 'P_CHNL' || biv_core_pkg.g_value_sep || 'PHONE' ||
                 biv_core_pkg.g_param_sep || 'P_TODAY_ONLY' ||
                 biv_core_pkg.g_value_sep || 'Y',
          col5 = l_new_param_str ||
                 'P_CHNL' || biv_core_pkg.g_value_sep || 'WEB' ||
                 biv_core_pkg.g_param_sep || 'P_TODAY_ONLY' ||
                 biv_core_pkg.g_value_sep || 'Y',
          col9 = l_new_param_str ||
                 'P_CLOSE_SR' || biv_core_pkg.g_value_sep || 'Y' ||
                 biv_core_pkg.g_param_sep || 'P_CL_ST' ||
                 biv_core_pkg.g_value_sep || l_dt ||
                 biv_core_pkg.g_param_sep || 'P_CL_END' ||
                 biv_core_pkg.g_value_sep || l_dt ,
          creation_date = sysdate
    WHERE report_code = 'BIV_RT_AGENT_REPORT'
      and session_id = l_session_id
      and col13 = 'Y';

Line: 360

         insert into biv_tmp_rt2 (report_code, session_id,col2)
          values('BIV_RT_AGENT_REPORT',l_session_id, l_new_param_str);

Line: 424

      not get selected. so total backlog in total line will not represent
      TOTAL backlog. when click on total backlog, it displays the TOTAL
      backlog which obivously does not match the total line value.
   if    (biv_core_pkg.g_srt_by = '2') then
      l_sql_sttmnt := '
        SELECT ' || biv_core_pkg.g_base_column || ' col1,
                        count(sr.incident_id) col2
                  ' || l_from_list || l_where_clause || '
                   and sr.incident_date >= trunc(sysdate)
                   and sr.incident_date <  trunc(sysdate+1)
                  group by ' || biv_core_pkg.g_base_column || '
                  order by 2 desc  ';

Line: 438

                SELECT ' || biv_core_pkg.g_base_column || ' col1,
                        count(sr.incident_id) col2
                  ' || l_from_list2 || l_where_clause2 || '
                   and nvl(stat.close_flag,''N'') = ''Y''
                   and sr.close_date >= trunc(sysdate)
                   and sr.close_date <  trunc(sysdate+1)
                   group by ' || biv_core_pkg.g_base_column || '
                  order by 2 desc  ';

Line: 448

                SELECT ' || biv_core_pkg.g_base_column || ' col1,
                        count(sr.incident_id) col2
                  ' || l_from_list2 || l_where_clause2 || '
                   and nvl(stat.close_flag,''N'') <> ''Y''
                   group by ' || biv_core_pkg.g_base_column || '
                  order by 2 desc ' ;

Line: 456

                SELECT ' || biv_core_pkg.g_base_column || ' col1, ' ||
                       biv_core_pkg.g_base_column || ' col2 '
                   || l_from_list || l_where_clause || '
                   group by ' || biv_core_pkg.g_base_column ;

Line: 463

      insert into biv_tmp_rt2(report_code, col1, col4,session_id)
       SELECT ''X'', col1, col2, :session_id
         FROM (' || l_sql_sttmnt || ')
        WHERE rownum <= :rows_to_display '; -- || biv_core_pkg.g_disp;

Line: 498

        SELECT col1, sum(col4) col4, sum(col6) col6, sum(col8) col8,
               :session_id session_id
          FROM ( SELECT ' || biv_core_pkg.g_base_column || ' col1,
                        1 col4, 0 col6, 0 col8
                  ' || l_from_list || l_where_clause || '
                   and sr.incident_date >= trunc(sysdate)
                   and sr.incident_date <  trunc(sysdate+1)
               UNION ALL
                SELECT ' || biv_core_pkg.g_base_column || ' col1,
                        0, 1, 0
                  ' || l_from_list2 || l_where_clause2 || '
                   and nvl(stat.close_flag,''N'') = ''Y''
                   and sr.close_date >= trunc(sysdate)
                   and sr.close_date <  trunc(sysdate+1)
               UNION ALL
                SELECT ' || biv_core_pkg.g_base_column || ' col1,
                        0, 0, 1
                  ' || l_from_list2 || l_where_clause2 || '
                   and nvl(stat.close_flag,''N'') <> ''Y''
                ) group by col1 ';

Line: 520

       insert into biv_tmp_rt2 (report_code,rowno,
                         col1, col4, col6, col8,session_id)
         SELECT ''Y'', rownum,
                col1, col4, col6, col8,session_id
           FROM (' || l_sql_sttmnt || ')';

Line: 536

   biv_core_pkg.update_base_col_desc('biv_tmp_rt2');

Line: 564

       insert into biv_tmp_rt2(report_code,rowno, col1, col2,
                                    col4, col6, col8,session_id)
        SELECT ''BIV_RT_MANAGER_REPORT'', rownum,
               col1, col2, col4, col6, col8,session_id
         FROM (SELECT col1, col2, col4, col6, col8,session_id
                 FROM biv_tmp_rt2
                WHERE report_code =''Y''
                  and session_id = :session_id
                order by ' || l_order_by || ')
        WHERE rownum <= :rows_to_display '; --|| nvl(biv_core_pkg.g_disp,'10');

Line: 583

   delete from biv_tmp_rt2
    where session_id = l_session_id
      and report_code in ('X', 'Y');

Line: 586

   l_pos := 'Before Update of odd columns';

Line: 587

   update biv_tmp_rt2
      set col7 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
                 biv_core_pkg.g_param_sep ||
                 'P_BLOG'||biv_core_pkg.g_value_sep||'Y',
          col3 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
                 biv_core_pkg.g_param_sep ||
                 'P_TODAY_ONLY'||biv_core_pkg.g_value_sep||'Y',
          col5 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
                 biv_core_pkg.g_param_sep ||
                 'P_CLOSE_SR' || biv_core_pkg.g_value_sep || 'Y' ||
                 biv_core_pkg.g_param_sep || 'P_CL_ST' ||
                 biv_core_pkg.g_value_sep || l_dt ||
                 biv_core_pkg.g_param_sep || 'P_CL_END' ||
                 biv_core_pkg.g_value_sep || l_dt ,
          creation_date = sysdate
     WHERE report_code = 'BIV_RT_MANAGER_REPORT'
       and session_id = l_session_id;

Line: 607

  SELECT count(*) into l_ttl_recs
    FROM biv_tmp_rt2
   WHERE report_code = 'BIV_RT_MANAGER_REPORT'
     and session_id = l_session_id;

Line: 616

     insert into biv_tmp_rt2 (report_code, rowno,
                              col4, col6, col8,  col13,session_id)
      SELECT report_code, max(rowno) + 1, sum(col4), sum(col6), sum(col8),
              'Y', session_id
        FROM biv_tmp_rt2
       WHERE session_id = l_session_id
        and report_code = 'BIV_RT_MANAGER_REPORT'
       group by report_code, session_id;

Line: 634

   update biv_tmp_rt2
      set col2 = l_ttl_desc,
          col7 = l_ttl_param_str ||
                 'P_BLOG'||biv_core_pkg.g_value_sep||'Y',
          col3 = l_ttl_param_str ||
                 'P_TODAY_ONLY'||biv_core_pkg.g_value_sep||'Y',
          col5 = l_ttl_param_str ||
                 'P_CLOSE_SR' || biv_core_pkg.g_value_sep || 'Y' ||
                 biv_core_pkg.g_param_sep || 'P_CL_ST' ||
                 biv_core_pkg.g_value_sep || l_dt ||
                 biv_core_pkg.g_param_sep || 'P_CL_END' ||
                 biv_core_pkg.g_value_sep || l_dt ,
          creation_date = sysdate
     WHERE report_code = 'BIV_RT_MANAGER_REPORT'
       and session_id = l_session_id
       and col13 = 'Y';