[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_stmt := 'select
' ||
biv_dbi_tmpl_util.get_view_by_col_name(l_view_by) || ' VIEWBY ' ||
', oset.' || l_view_by_col_name || ' VIEWBYID ' ||
case
when l_view_by = biv_dbi_tmpl_util.g_PRODUCT then
', v.description'
else
', null'
end
|| ' BIV_ATTRIBUTE5
/* Closed Prior */
, nvl(oset.p_closed,0) BIV_MEASURE1
/* Closed Current */
, nvl(oset.c_closed,0) BIV_MEASURE2
/* Closed Change */
, ' ||
biv_dbi_tmpl_util.change_column('oset.c_closed','oset.p_closed','BIV_MEASURE4') ||
'
/* Average Time To Close Prior */
, ' ||
biv_dbi_tmpl_util.rate_column('oset.p_time_to_close','oset.p_closed','BIV_MEASURE5','N') ||
'
/* Average Time To Close Current */
, ' ||
biv_dbi_tmpl_util.rate_column('oset.c_time_to_close','oset.c_closed','BIV_MEASURE6','N') ||
'
/* Average Time To Close Change */
, ' ||
biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_time_to_close','oset.c_closed',null,'N')
,biv_dbi_tmpl_util.rate_column('oset.p_time_to_close','oset.p_closed',null,'N')
,'BIV_MEASURE8'
,'N') ||
'
/* Time to Close Buckets */
' || biv_dbi_tmpl_util.get_bucket_outer_query
( p_bucket_rec => l_bucket_rec
, p_column_name_base => 'oset.c_close_bucket'
, p_alias_base => 'BIV_MEASURE10'
, p_total_flag => 'N'
, p_backlog_col => case
when p_distribution = 'Y' then 'oset.c_closed'
end
) ||
'
/* GT Closed Current */
, nvl(oset.c_closed_total,0) BIV_MEASURE11
/* GT Closed Change */
, ' ||
biv_dbi_tmpl_util.change_column('oset.c_closed_total','oset.p_closed_total','BIV_MEASURE12') ||
'
/* GT Average Time To Close Current */
, ' ||
biv_dbi_tmpl_util.rate_column('oset.c_time_to_close_total','oset.c_closed_total','BIV_MEASURE13','N') ||
'
/* GT Average Time To Close Change */
, ' ||
biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_time_to_close_total','oset.c_closed_total',null,'N')
,biv_dbi_tmpl_util.rate_column('oset.p_time_to_close_total','oset.p_closed_total',null,'N')
,'BIV_MEASURE14'
,'N') ||
'
/* GT Time to Close Buckets*/
' || biv_dbi_tmpl_util.get_bucket_outer_query
( p_bucket_rec => l_bucket_rec
, p_column_name_base => 'oset.c_close_bucket'
, p_alias_base => 'BIV_MEASURE15'
, p_total_flag => 'Y'
, p_backlog_col => case
when p_distribution = 'Y' then 'oset.c_closed'
end
) ||
'
/* KPI GT Average Time To Close Prior */
, ' ||
biv_dbi_tmpl_util.rate_column('oset.p_time_to_close_total','oset.p_closed_total','BIV_MEASURE16','N') ||
'
, ' ||
biv_dbi_tmpl_util.get_category_drill_down( l_view_by
, case
when p_distribution = 'Y' then g_closure_dbn_rep_func
else g_closure_rep_func
end ) ||
biv_dbi_tmpl_util.drill_detail( g_closed_detail_rep_func
, 0
, null
, 'BIV_ATTRIBUTE6') ||
case
when p_distribution = 'N' then
biv_dbi_tmpl_util.bucket_detail_drill( g_closed_detail_rep_func
, l_bucket_rec
, 'BIV_ATTRIBUTE7' )
else
null
end ||
'
from
( select * from ( ' || poa_dbi_template_pkg.status_sql
( P_FACT_NAME => l_mv
, P_WHERE_CLAUSE => l_where_clause
, 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_FILTER_WHERE => '(c_closed > 0 or p_closed > 0)'
, P_GENERATE_VIEWBY => 'Y'
);
l_stmt := 'select
cal.name VIEWBY
/* End date of the period */
, cal.end_date VIEWBYID
/* Closed Prior */
, nvl(iset.p_closed,0) BIV_MEASURE1
/* Closed Current */
, nvl(iset.c_closed,0) BIV_MEASURE2
/* Closed Change */
, ' ||
biv_dbi_tmpl_util.change_column('iset.c_closed','iset.p_closed','BIV_MEASURE4') ||
'
/* Average Time To Close Prior */
, ' ||
biv_dbi_tmpl_util.rate_column('iset.p_time_to_close','iset.p_closed','BIV_MEASURE5','N') ||
'
/* Average Time To Close Current */
, ' ||
biv_dbi_tmpl_util.rate_column('iset.c_time_to_close','iset.c_closed','BIV_MEASURE6','N') ||
'
/* Average Time To Close Change */
, ' ||
biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('iset.c_time_to_close','iset.c_closed',null,'N')
,biv_dbi_tmpl_util.rate_column('iset.p_time_to_close','iset.p_closed',null,'N')
,'BIV_MEASURE8'
,'N') ||
'
/* Time to Close Buckets */
' || biv_dbi_tmpl_util.get_bucket_outer_query
( p_bucket_rec => l_bucket_rec
, p_column_name_base => 'iset.c_close_bucket'
, p_alias_base => 'BIV_MEASURE10'
, p_total_flag => 'N'
, p_backlog_col => case
when p_distribution = 'Y' then 'iset.c_closed'
end
);
select
i.incident_number biv_measure1
, rt.value biv_measure2 -- request_type
, pr.value biv_measure3 -- product
, pr.description biv_measure4
, cu.value biv_measure5 -- customer
, sv.value biv_measure6 -- severity
, ag.value biv_measure7 -- assignment_group
, re.value biv_measure8 -- resolution
, ch.value biv_measure9 -- channel
, fact.age biv_measure10
, fnd_date.date_to_displaydate(fact.closed_date) biv_measure11 ' ||
case
when l_drill_url is not null then
'
, ''pFunctionName=' || l_drill_url || '&' || l_sr_id || '=''||fact.incident_id biv_attribute1'
else
'
, null biv_attribute1'
end ||
'
from
( select
fact.*
, rank() over(order by ' || l_order_by || ') -1 rnk
from
' || l_mv || ' fact
where
fact.closed_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_EFFECTIVE_END_DATE
' || l_where_clause || '
) fact
' || l_join_from || '
, cs_incidents_all_b i
where
1=1
and fact.incident_id = i.incident_id' || l_join_where || '
and (fact.rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
&ORDER_BY_CLAUSE
'
--|| biv_dbi_tmpl_util.dump_parameters(p_param)
;