[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION GET_MEASURES(l_view_by_select VARCHAR2) RETURN VARCHAR2
IS
l_measure_txt VARCHAR2(32000);
l_view_by_select
||','||l_goal||' BIX_PMV_AI_SLGOAL,'
||bix_pmv_dbi_utl_pkg.get_simple_measure(
p_curr=>'to_number(NVL(c_g,0))'
,p_prev=>'NVL(p_g,0)'
,p_measurecol=>'BIX_PMV_AI_INCALLHAND_CP'
,p_totalcol=>'BIX_PMV_TOTAL9'
,p_changecol=>'BIX_PMV_AI_INCALLHAND_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL10'
)
/* Abandoned Calls */
||',' || bix_pmv_dbi_utl_pkg.get_simple_measure(
p_curr=>'NVL(c_e,0)'
,p_prev=>'NVL(p_e,0)'
,p_measurecol=>'BIX_ATTRIBUTE_7'
,p_totalcol=>'BIX_PMV_TOTAL21'
,p_changecol=>'BIX_ATTRIBUTE_9'
,p_changetotalcol=>'BIX_PMV_TOTAL22'
)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
p_curr=>'NVL(c_l,0)'
,p_prev=>'NVL(p_l,0)'
,p_measurecol=>'BIX_PMV_AI_SRCR_CP'
,p_totalcol=>'BIX_PMV_TOTAL17'
,p_changecol=>'BIX_PMV_AI_SRCR_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL18'
)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
p_curr=>'NVL(c_m,0)'
,p_prev=>'NVL(p_m,0)'
,p_measurecol=>'BIX_PMV_AI_LECR_CP'
,p_totalcol=>'BIX_PMV_TOTAL19'
,p_changecol=>'BIX_PMV_AI_LECR_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL20'
)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
p_curr=>'NVL(c_n,0)'
,p_prev=>'NVL(p_n,0)'
,p_measurecol=>'BIX_PMV_AI_OPCR_CP'
,p_totalcol=>'BIX_PMV_TOTAL23'
,p_changecol=>'BIX_PMV_AI_OPCR_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL24'
)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
p_curr=>'NVL(c_h,0)'
,p_prev=>'NVL(p_h,0)'
,p_measurecol=>'BIX_PMV_AI_WEBCALL_CP'
,p_totalcol=>'BIX_PMV_TOTAL11'
,p_changecol=>'BIX_PMV_AI_WEBCALL_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL12'
)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'Y'
,p_num=>'NVL(c_a, 0)'
,p_denom=>'NVL(c_d,0)'
,p_pnum=>'NVL(p_a, 0)'
,p_pdenom=>'NVL(p_d,0)'
,p_measurecol=>'BIX_PMV_AI_SL_CP'
,p_totalcol=>'BIX_PMV_TOTAL1'
,p_changecol=>'BIX_PMV_AI_SL_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL2'
)
||','
/* For KPI -Inbound Service Level -Current Value - got from BIX_PMV_AI_SL_CP*/
/* For KPI -Inbound Service Level -Prior Value*/
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'Y'
,p_num=>'NVL(p_a, 0)'
,p_denom=>'NVL(p_d,0)'
,p_measurecol=>'BIX_CALC_ITEM3'
,p_totalcol=>'BIX_CALC_ITEM4'
)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'N'
,p_num=>'NVL(c_c, 0)'
,p_denom=>'NVL(c_g,0)'
,p_pnum=>'NVL(p_c, 0)'
,p_pdenom=>'NVL(p_g,0)'
,p_measurecol=>'BIX_PMV_AI_SPANS_CP'
,p_totalcol=>'BIX_PMV_TOTAL3'
,p_changecol=>'BIX_PMV_AI_SPANS_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL4'
)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'Y'
,p_num=>'NVL(c_e, 0)'
,p_denom=>'NVL(c_d,0)'
,p_pnum=>'NVL(p_e, 0)'
,p_pdenom=>'NVL(p_d,0)'
,p_measurecol=>'BIX_PMV_AI_ABANRATE_CP'
,p_totalcol=>'BIX_PMV_TOTAL5'
,p_changecol=>'BIX_PMV_AI_ABANRATE_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL6'
)
||','
/* For KPI-Abandon rate Current period - got from BIX_PMV_AI_ABANRATE_CP*/
/* For KPI-Abandon rate Prior period */
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'Y'
,p_num=>'NVL(p_e, 0)'
,p_denom=>'NVL(p_d,0)'
,p_measurecol=>'BIX_CALC_ITEM11'
,p_totalcol=>'BIX_CALC_ITEM12'
)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'Y'
,p_num=>'NVL(c_f, 0)'
,p_denom=>'NVL(c_g,0)'
,p_pnum=>'NVL(p_f, 0)'
,p_pdenom=>'NVL(p_g,0)'
,p_measurecol=>'BIX_PMV_AI_TRANRATE_CP'
,p_totalcol=>'BIX_PMV_TOTAL7'
,p_changecol=>'BIX_PMV_AI_TRANRATE_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL8'
)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'N'
,p_num=>'NVL(c_k, 0)'
,p_denom=>'NVL(c_i,0)'
,p_pnum=>'NVL(p_k, 0)'
,p_pdenom=>'NVL(p_i,0)'
,p_measurecol=>'BIX_PMV_AI_AVGTALK_CP'
,p_totalcol=>'BIX_PMV_TOTAL13'
,p_changecol=>'BIX_PMV_AI_AVGTALK_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL14'
)
||','
/* For KPI- Average Talk Current got from BIX_PMV_AI_AVGTALK_CP*/
/* For KPI- Average Talk Prior*/
/*||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'N'
,p_num=>'NVL(p_k, 0)'
,p_denom=>'NVL(p_i,0)'
,p_measurecol=>'BIX_CALC_ITEM23'
,p_totalcol=>'BIX_CALC_ITEM24'
)
||','
*/
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'N'
,p_num=>'NVL(c_j, 0)'
,p_denom=>'NVL(c_i,0)'
,p_pnum=>'NVL(p_j, 0)'
,p_pdenom=>'NVL(p_i,0)'
,p_measurecol=>'BIX_PMV_AI_AVGWRAP_CP'
,p_totalcol=>'BIX_PMV_TOTAL15'
,p_changecol=>'BIX_PMV_AI_AVGWRAP_CG'
,p_changetotalcol=>'BIX_PMV_TOTAL16'
)
||','
/* For KPI- Average Wrap Current - got from BIX_PMV_AI_AVGWRAP_CP */
/* For KPI- Average Wrap Prior */
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'N'
,p_num=>'NVL(p_j, 0)'
,p_denom=>'NVL(p_i,0)'
,p_measurecol=>'BIX_CALC_ITEM27'
,p_totalcol=>'BIX_CALC_ITEM28'
)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'Y'
,p_num=>'NVL(p_a, 0)'
,p_denom=>'NVL(p_d,0)'
,p_measurecol=>'BIX_PMV_AI_SL_PP'
)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'N'
,p_num=>'NVL(p_c, 0)'
,p_denom=>'NVL(p_g,0)'
,p_measurecol=>'BIX_PMV_AI_SPANS_PP'
)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'Y'
,p_num=>'NVL(p_e, 0)'
,p_denom=>'NVL(p_d,0)'
,p_measurecol=>'BIX_PMV_AI_ABANRATE_PP'
)
||','
||bix_pmv_dbi_utl_pkg.get_divided_measure(
p_percentage=>'N'
,p_num=>'NVL(p_k, 0)'
,p_denom=>'NVL(p_i,0)'
,p_measurecol=>'BIX_PMV_AI_AVGTALK_PP'
)
||','
||bix_pmv_dbi_utl_pkg.get_simple_measure(
p_curr=>'NVL(p_g, 0)'
,p_measurecol=>'BIX_PMV_AI_INCALLHAND_PP'
)
||' FROM((';
l_view_by_select VARCHAR2(500) ;
, p_view_by_select => l_view_by_select
, p_view_by => l_view_by
);
l_sqltext :='select rset.*,BIX_PMV_AI_SL_CP BIX_CALC_ITEM1,BIX_PMV_TOTAL1 BIX_CALC_ITEM2,'
||'BIX_PMV_AI_ABANRATE_CP BIX_CALC_ITEM9,BIX_PMV_TOTAL5 BIX_CALC_ITEM10,'
||'BIX_PMV_AI_AVGWRAP_CP BIX_CALC_ITEM25,'
||'BIX_PMV_TOTAL15 BIX_CALC_ITEM26 from ('
|| get_measures(l_view_by_select) || l_sqltext_cont ||
poa_dbi_template_pkg.status_sql (
p_fact_name => l_mv
, p_where_clause => l_where_clause
, p_filter_where => l_filter_where
, p_join_tables => l_join_tbl
, p_use_windowing => 'N'
, p_col_name => l_col_tbl
, p_use_grpid => 'N'
, p_paren_count => 3
, p_generate_viewby => l_generate_viewby)
||l_group_by_clause|| get_zeronull_clause;
l_sqltext := 'SELECT group_name VIEWBY ';
l_sqltext := 'SELECT ' || l_column_name || ' VIEWBY ';
SELECT
' || l_column_name || '
,SUM(NVL(a,0)) a
,SUM(NVL(b,0)) b
,DECODE(SUM(c), 0, NULL, SUM(c)) c
,DECODE(SUM(d), 0, NULL, SUM(d)) d
,SUM(NVL(e,0)) e
,SUM(NVL(f,0)) f
,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
,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(o,0)) o
,SUM(NVL(p,0)) p
,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(q,0) + NVL(a3,0)), 0, NULL, SUM(NVL(q,0) + NVL(a3,0))) q
,DECODE(SUM(NVL(r,0) + NVL(a4,0)), 0, NULL, SUM(NVL(r,0) + NVL(a4,0))) r
,DECODE(SUM(NVL(m,0) + NVL(a5,0)), 0, NULL, SUM(NVL(m,0) + NVL(a5,0))) m1
,DECODE(SUM(NVL(n,0) + NVL(a6,0)), 0, NULL, SUM(NVL(n,0) + NVL(a6,0))) n1
,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
FROM ( ';
SELECT
' || l_column_name || '
,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',agent_calls_answered_by_goal,
''TELE_DIRECT'',agent_calls_answered_by_goal, 0))
a
,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',agent_calls_answered_by_goal,
''TELE_DIRECT'',agent_calls_answered_by_goal, 0))
b
,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',agent_calls_handled_total,
''TELE_DIRECT'',agent_calls_handled_total, 0))
c
,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',agent_calls_handled_total,
''TELE_DIRECT'',agent_calls_handled_total, 0))
d
,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_tot_queue_to_answer,
''TELE_DIRECT'',call_tot_queue_to_answer, 0))
e
,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_tot_queue_to_answer,
''TELE_DIRECT'',call_tot_queue_to_answer, 0))
f
,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_offered_total,
''TELE_DIRECT'',call_calls_offered_total, 0))
g
,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_offered_total,
''TELE_DIRECT'',call_calls_offered_total, 0))
h
,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_abandoned,
''TELE_DIRECT'',call_calls_abandoned, 0))
i
,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_abandoned,
''TELE_DIRECT'',call_calls_abandoned, 0))
j
,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_transferred,
''TELE_DIRECT'',call_calls_transferred, 0))
k
,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'',call_calls_transferred,
''TELE_DIRECT'',call_calls_transferred, 0))
l
,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'', call_calls_handled_total,
''TELE_DIRECT'', call_calls_handled_total, 0))
m
,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_INB'', call_calls_handled_total,
''TELE_DIRECT'', call_calls_handled_total, 0))
n
,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,DECODE(media_item_type, ''TELE_WEB_CALLBACK'',
call_calls_handled_total, 0))
o
,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,DECODE(media_item_type, ''TELE_WEB_CALLBACK'',
call_calls_handled_total, 0))
p
,DECODE(calendar.report_date,&BIS_CURRENT_ASOF_DATE,call_calls_handled_total)
q
,DECODE(calendar.report_date,&BIS_PREVIOUS_ASOF_DATE,call_calls_handled_total)
r
,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
,party_id party_id
,calendar.report_date report_date
,NULL a3
,NULL a4
,NULL a5
,NULL a6
,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
FROM
bix_ai_call_details_mv a,
fii_time_rpt_struct calendar
WHERE a.row_type = ''CDPR''
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 ';
SELECT
' || l_column_name || '
,NULL a
,NULL b
,NULL c
,NULL d
,NULL e
,NULL f
,NULL g
,NULL h
,NULL i
,NULL j
,NULL k
,NULL l
,NULL m
,NULL n
,NULL o
,NULL p
,NULL q
,NULL r
,NULL s
,NULL t
,NULL u
,NULL v
,NULL w
,NULL x
,NULL y
,NULL z
,NULL y1
,NULL z1
,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(media_item_type, ''TELE_INB'',
call_cont_calls_handled_tot_na, ''TELE_DIRECT'', call_cont_calls_handled_tot_na, 0))
a5
,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
call_cont_calls_handled_tot_na, ''TELE_DIRECT'', call_cont_calls_handled_tot_na, 0))
a6
,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_CURRENT_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
call_cont_calls_offered_na, ''TELE_DIRECT'', call_cont_calls_offered_na, 0))
a7
,DECODE(time_id,TO_NUMBER(TO_CHAR(&BIS_PREVIOUS_EFFECTIVE_START_DATE,''J'')),DECODE(media_item_type, ''TELE_INB'',
call_cont_calls_offered_na, ''TELE_DIRECT'', call_cont_calls_offered_na, 0))
a8
,NULL a9
,NULL a10
FROM
bix_ai_call_details_mv a
WHERE row_type = ''CDPR''
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 ';