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.BIX_PMV_AO_ACTCSCH_RPT_PKG SQL Statements

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

Line: 72


   l_source_code_where_clause := ' AND campaign_id in (select source_code_for_id from
   ams_source_codes where arc_source_code_for=''CAMP'' and active_flag=''Y'' and  source_code_id IN (:l_source_code_id) ) ';

Line: 98


'SELECT
10 BIX_PMV_AO_CAMPNAME,
10 BIX_PMV_AO_CONTPERHR_CP,
10 BIX_PMV_AO_CONTPERHR_CG,
10 BIX_PMV_AO_PORESP_CP,
10 BIX_PMV_AO_PORESP_CG,
10 BIX_PMV_AO_ABANRATE_CP,
10 BIX_PMV_AO_ABANRATE_CG,
10 BIX_PMV_AO_OUTCALLHAND_CP,
10 BIX_PMV_AO_OUTCALLHAND_CG,
10 BIX_PMV_AO_AVAILRATE_CP,
10 BIX_PMV_AO_AVAILRATE_CG,
10 BIX_PMV_AO_UTILRATE_CP,
10 BIX_PMV_AO_UTILRATE_CG,
10 BIX_PMV_AO_AVGTALK_CP,
10 BIX_PMV_AO_AVGTALK_CG,
10 BIX_PMV_AO_AVGWRAP_CP,
10 BIX_PMV_AO_AVGWRAP_CG,
10 BIX_PMV_AO_SRCR_CP,
10 BIX_PMV_AO_SRCR_CG,
10 BIX_PMV_AO_LECR_CP,
10 BIX_PMV_AO_LECR_CG,
10 BIX_PMV_AO_OPCR_CP,
10 BIX_PMV_AO_OPCR_CG,
10 BIX_PMV_AO_CUST_CP,
10 BIX_PMV_AO_CUST_CG,
100 BIX_PMV_TOTAL1,
100 BIX_PMV_TOTAL2,
100 BIX_PMV_TOTAL3,
100 BIX_PMV_TOTAL4,
100 BIX_PMV_TOTAL5,
100 BIX_PMV_TOTAL6,
100 BIX_PMV_TOTAL7,
100 BIX_PMV_TOTAL8,
100 BIX_PMV_TOTAL9,
100 BIX_PMV_TOTAL10,
100 BIX_PMV_TOTAL11,
100 BIX_PMV_TOTAL12,
100 BIX_PMV_TOTAL13,
100 BIX_PMV_TOTAL14,
100 BIX_PMV_TOTAL15,
100 BIX_PMV_TOTAL16,
100 BIX_PMV_TOTAL17,
100 BIX_PMV_TOTAL18,
100 BIX_PMV_TOTAL19,
100 BIX_PMV_TOTAL20,
100 BIX_PMV_TOTAL21,
100 BIX_PMV_TOTAL22
FROM DUAL ';

Line: 152


   l_sqltext := 'SELECT group_name VIEWBY ';

Line: 154


   l_sqltext := 'SELECT value  VIEWBY ';

Line: 161


  l_sqltext := 'SELECT schedule_name BIX_PMV_AO_CSCHNAME ';

Line: 292


     SELECT DECODE(a.schedule_id ,''-999'',''-999'',NVL(sched.schedule_name, :l_unknown)) schedule_name
     ,sum(c) c ,sum(d) d,sum(g) g,sum(h) h,sum(i) i  ,sum(j) j ,sum(ius) ius,sum(jus) jus,sum(k) k,sum(l) l
	,sum(m) m,sum(n) n,sum(s) s,sum(t) t,sum(u) u,sum(v) v,sum(w) w ,sum(x) x,sum(y) y,sum(z) z,sum(y1) y1
	,sum(z1) z1,sum(a1) a1,sum(a2) a2,sum(a9) a9,sum(a10) a10,sum(q) q ,sum(r) r,sum(g1) g1 ,sum(h1) h1
	,sum(prp) prp,sum(prc) prc,sum(contp) contp,sum(contc) contc
        ,min(loginp)  loginp
   ,min(loginc)  loginc
   ,min(idlep)  idlep
   ,min(idlec)  idlec
   ,min(availp)  availp
   ,min(availc)  availc FROM (' ;

Line: 305


l_sqltext := l_sqltext || '   SELECT '|| l_column_name ||'
   , c,d,g,h,i,j,ius,jus,k,l,m,n,s,t,u,v,w,x,y,z,y1,z1,a1,a2,a9,a10,q,r,g1,h1,prp,prc,contp,contc
   ,sum(loginp) over() loginp
   ,sum(loginc) over() loginc
   ,sum(idlep) over() idlep
   ,sum(idlec) over() idlec
   ,sum(availp) over() availp
   ,sum(availc) over() availc
   FROM (
  ';

Line: 317


    SELECT
       ' || l_column_name || '
      ,DECODE(SUM(c), 0, NULL, SUM(c)) c
      ,DECODE(SUM(d), 0, NULL, SUM(d)) d
      ,DECODE(SUM(g), 0, NULL, SUM(g)) g
      ,DECODE(SUM(h), 0, NULL, SUM(h)) h
      ,SUM(NVL(i,0)) i
      ,SUM(NVL(j,0)) j
      /* Added for US Abandonment rate */
      ,SUM(NVL(ius,0)) ius
      ,SUM(NVL(jus,0)) jus
      /* End of addition */
      ,SUM(NVL(k,0)) k
      ,SUM(NVL(l,0)) l
	 ,DECODE(SUM(m), 0, NULL, SUM(m)) m
	 ,DECODE(SUM(n), 0, NULL, SUM(n)) n
      ,SUM(NVL(s,0)) s
      ,SUM(NVL(t,0)) t
      ,SUM(NVL(u,0)) u
      ,SUM(NVL(v,0)) v
      ,SUM(NVL(w,0)) w
      ,SUM(NVL(x,0)) x
      ,SUM(NVL(y,0)) y
      ,SUM(NVL(z,0)) z
      ,SUM(NVL(y1,0)) y1
      ,SUM(NVL(z1,0)) z1
      ,COUNT(DISTINCT(CASE WHEN report_date = &BIS_CURRENT_ASOF_DATE
                           AND party_id <> -1
                      THEN PARTY_ID END ))
                       a1
      ,COUNT(DISTINCT(CASE WHEN report_date = &BIS_PREVIOUS_ASOF_DATE
                           AND party_id <> -1
                      THEN PARTY_ID END ))
                       a2
      ,MIN(a9) a9
      ,MIN(a10) a10
      ,DECODE(SUM(NVL(m,0) + NVL(a3,0)), 0, NULL, SUM(NVL(m,0) + NVL(a3,0))) q
      ,DECODE(SUM(NVL(n,0) + NVL(a4,0)), 0, NULL, SUM(NVL(n,0) + NVL(a4,0))) r
      ,DECODE(SUM(NVL(g,0) + NVL(a7,0)), 0, NULL, SUM(NVL(g,0) + NVL(a7,0))) g1
      ,DECODE(SUM(NVL(h,0) + NVL(a8,0)), 0, NULL, SUM(NVL(h,0) + NVL(a8,0))) h1
      ,sum(nvl(loginp,0)) loginp
      ,sum(nvl(loginc,0)) loginc
      ,sum(nvl(idlep,0))  idlep
      ,sum(nvl(idlec,0))  idlec
      ,sum(nvl(availp,0)) availp
      ,sum(nvl(availc,0)) availc
      ,sum(nvl(prp,0) ) prp
      ,sum(nvl(prc,0)) prc
      ,sum(nvl(contp,0)) contp
      ,sum(nvl(contc,0)) contc

    FROM ( ';

Line: 371


    SELECT
      ' || l_column_name || '
      ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_calls_handled_total, 0)
                       c
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_calls_handled_total, 0)
                       d
     ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_offered_total,0),0)
                       g
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,
      decode(dialing_method,''PRED'',call_calls_offered_total,0), 0)
                       h
      ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,
      decode(dialing_method,''PRED'',call_calls_abandoned,0)
      , 0)
                       i
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned,0), 0)
                       j
    /* Added for US Abandon rate */
     ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned_us,0), 0)
                       ius
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,decode(dialing_method,''PRED'',call_calls_abandoned_us,0), 0)
                       jus
    /* End of additions */
      ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_transferred, 0)
                       k
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_transferred, 0)
                       l
      ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_handled_total, 0)
                       m
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_handled_total, 0)
                       n
     ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_wrap_time_nac)
                       s
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_wrap_time_nac)
                       t
      ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_talk_time)
                       u
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_talk_time)
                       v
      ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_sr_created)
                       w
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_sr_created)
                       x
      ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_leads_created)
                       y
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_leads_created)
                       z
      ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agent_opportunities_created)
                       y1
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agent_opportunities_created)
                       z1
      ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agentcall_pr_count)
                       prc
      ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agentcall_pr_count)
                       prp
      ,party_id party_id
	 ,calendar.report_date report_date
      ,NULL            a3
      ,NULL            a4
	,NULL            a7
	 ,NULL            a8
      ,COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_CURRENT_ASOF_DATE
                           AND party_id <> -1
                      THEN PARTY_ID END )) OVER()
             a9
      ,COUNT(DISTINCT(CASE WHEN calendar.report_date = &BIS_PREVIOUS_ASOF_DATE
                           AND party_id <> -1
                      THEN PARTY_ID END )) OVER()
             a10
       ,0 loginp
       ,0 loginc
       ,0 idlep
       ,0 idlec
       ,0 availp
       ,0 availc
       ,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,agentcall_contact_count)
                       contp
       ,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,agentcall_contact_count)
                       contc
    FROM
    bix_ao_call_details_mv a,
    fii_time_rpt_struct calendar
    WHERE a.row_type = ''C''
    AND   a.time_id = calendar.time_id
    AND   a.period_type_id = calendar.period_type_id
    AND   calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
    AND   bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) =  calendar.record_type_id ';

Line: 461


    /* continue inline view and select session measures */
SELECT
       -999
      ,NULL  c
      ,NULL  d
      ,NULL  g
      ,NULL  h
      ,NULL  i
      ,NULL  j
      /* Added for US abandon measure */
      ,NULL  ius
      ,NULL  jus
      /* End of addition */
      ,NULL  k
      ,NULL  l
      ,NULL  m
      ,NULL  n
      ,NULL  s
      ,NULL  t
      ,NULL  u
      ,NULL  v
      ,NULL  w
      ,NULL  x
      ,NULL  y
      ,NULL  z
      ,NULL  y1
      ,NULL  z1
      ,0 prc
      ,0 prp
	 ,NULL party_id
	 ,NULL report_date
     ,0  a3
     ,0  a4
     ,0  a7
     ,0  a8
     ,0  a9
     ,0   a10
     , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,login_time))
              loginp
     , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,login_time))
              loginc
     , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,idle_time))
              idlep
     , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,idle_time))
              idlec
     , SUM(DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,available_time))
              availp
     , SUM(DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,available_time))
             availc
     , 0 contp
     ,0 contc
    FROM
      bix_agent_session_f fact,
      fii_time_rpt_struct calendar
    WHERE fact.time_id = calendar.time_id
    AND fact.application_id = 696
    AND fact.period_type_id = calendar.period_type_id
    AND calendar.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
    AND
    bitand(calendar.record_type_id,&BIS_NESTED_PATTERN ) =  calendar.record_type_id ';

Line: 525


    /*Continue inline view select continued measures */
    SELECT
       ' || l_column_name || '
      ,NULL  c
      ,NULL  d
      ,NULL  g
      ,NULL  h
      ,NULL  i
      ,NULL  j
      /* Added for US abandon measure */
      ,NULL  ius
      ,NULL  jus
      /* End of addition */
      ,NULL  k
      ,NULL  l
      ,NULL  m
      ,NULL  n
      ,NULL  s
      ,NULL  t
      ,NULL  u
      ,NULL  v
      ,NULL  w
      ,NULL  x
      ,NULL  y
      ,NULL  z
      ,NULL  y1
      ,NULL  z1
      ,0 prc
      ,0 prp
	 ,NULL party_id
	 ,NULL report_date
      ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na)
             a3
      ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),call_cont_calls_handled_tot_na)
             a4
     ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),decode(dialing_method,''PRED'',call_cont_calls_offered_na,0), 0)
             a7
      ,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),decode(dialing_method,''PRED'',call_cont_calls_offered_na,0), 0)
             a8
      ,NULL  a9
      ,NULL  a10
      ,0 loginp
      ,0 loginc
      ,0 idlep
      ,0 idlec
      ,0 availp
      ,0 availc
      ,0 contp
      ,0 contc
    FROM
      bix_ao_call_details_mv a
    WHERE row_type = ''C''
    AND   time_id IN (TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),
                          TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')))
    AND   period_type_id = 1 ';