[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'(select '
|| l_group_and_sel_clause
|| l_col_names
|| '
from '
|| p_fact_name
|| ' fact
where fact.report_date in '
|| l_inlist
|| p_where_clause
|| '
group by '
|| l_group_and_sel_clause
|| ' ) ) ' || l_filter_where || ' ) oset ';
return '( select start_date, end_date, end_date report_date, to_char(end_date,''dd-Mon-yy'') name, ordinal from '
|| '( select distinct '
|| 'decode(t.current_ind, 2, &BIV_PREV_EFFEC_END_DATE'
|| ', 4, (&BIV_CURR_EFFEC_START_DATE - 1)'
|| ', &BIV_CURR_EFFEC_END_DATE)+(t.offset*&RLX_DAYS) '
|| '- &RLX_DAYS_TO_START start_date'
|| ', decode(t.current_ind, 2, &BIV_PREV_EFFEC_END_DATE'
|| ', 4, (&BIV_CURR_EFFEC_START_DATE - 1)'
|| ', &BIV_CURR_EFFEC_END_DATE)+(t.offset*&RLX_DAYS) end_date'
|| ', decode(&BIS_TIME_COMPARISON_TYPE,''SEQUENTIAL'',-1,decode(t.current_ind,4,0,2,1,2)) ordinal '
|| 'from biv_trend_rpt t '
|| 'where t.offset > &RLX_ROWS_OFFSET '
|| 'and current_ind = 1'
|| ' )'
|| ' )';
return '( select start_date, end_date, end_date report_date, name
from ' || poa_dbi_util_pkg.get_calendar_table(p_xtd) || '
where start_date between to_date('''||l_report_start_date ||''', ''dd-mon-yyyy'') and &BIV_LAST_REFRESH_DATE )';
select max(trunc(report_date))
into l_current_date
from biv_dbi_backlog_age_dates;
'(select
CASE when fact.report_date = &BIV_CURR_EFFEC_START_DATE then &BIV_CURR_EFFEC_END_DATE else fact.REPORT_DATE END report_date
' ||'
, cur, pri '
|| l_col_names
|| '
from '
|| p_fact_name
|| ' fact ,
'
|| '( '
|| ' select trunc(report_date) report_date'
|| ' , decode(bitand(record_type_id, &BIV_CURR_PATTERN), &BIV_CURR_PATTERN,''Y'',''N'') Cur '
|| ' , decode(bitand(record_type_id, &BIV_PREV_PATTERN), &BIV_PREV_PATTERN,''Y'',''N'') Pri '
|| ' from biv_dbi_backlog_age_dates '
|| ' where '
|| ' bitand(record_type_id, &BIV_CURR_PATTERN)= &BIV_CURR_PATTERN /* current */ '
|| ' or bitand(record_type_id, &BIV_PREV_PATTERN)= &BIV_PREV_PATTERN /* prior */ '
|| ' ) join_dates '
|| '
where'
|| ' fact.report_date = join_dates.report_date
'
|| p_where_clause
|| '
group by '
|| 'fact.REPORT_DATE, cur, pri '
|| ') iset '
|| ',
'
|| get_calendar_table(p_xtd) || 'cal
'
||'where iset.report_date BETWEEN cal.START_DATE AND cal.end_Date'
||' GROUP BY cal.report_date )iset,'
|| get_calendar_table(p_xtd)
|| ' cal
'
|| ' where cal.end_date = iset.report_date(+) '
|| ' order by cal.end_date';
'(select '
|| 'fact.REPORT_DATE , cur, pri '
|| l_col_names
|| '
from '
|| p_fact_name
|| ' fact ,
'
|| '( '
|| ' select trunc(report_date) report_date'
|| ' , decode(bitand(record_type_id, &BIV_CURR_PATTERN), &BIV_CURR_PATTERN,''Y'',''N'') Cur '
|| ' , decode(bitand(record_type_id, &BIV_PREV_PATTERN), &BIV_PREV_PATTERN,''Y'',''N'') Pri '
|| ' from biv_dbi_backlog_age_dates '
|| ' where '
|| ' bitand(record_type_id, &BIV_CURR_PATTERN)= &BIV_CURR_PATTERN /* current */ '
|| ' or bitand(record_type_id, &BIV_PREV_PATTERN)= &BIV_PREV_PATTERN /* prior */ '
|| ' ) join_dates '
|| '
where'
|| ' fact.report_date = join_dates.report_date
'
|| p_where_clause
|| '
group by '
|| 'fact.REPORT_DATE, cur, pri '
|| ') iset '
|| ',
'
|| get_calendar_table(p_xtd)
|| ' cal
'
|| ' where cal.end_date = iset.report_date(+) '
|| ' order by cal.end_date';
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
/* Backlog Prior */
, nvl(oset.p_backlog,0) BIV_MEASURE1
/* Backlog Current */
, nvl(oset.c_backlog,0) BIV_MEASURE2
/* Backlog Change */
, ' ||
biv_dbi_tmpl_util.change_column('oset.c_backlog'
,'oset.p_backlog'
,'BIV_MEASURE4') ||
'
/* Average Age Prior */
, ' ||
biv_dbi_tmpl_util.rate_column('oset.p_backlog_age'
,'oset.p_backlog'
,'BIV_MEASURE5'
,'N') ||
'
/* Average Age Current */
, ' ||
biv_dbi_tmpl_util.rate_column('oset.c_backlog_age'
,'oset.c_backlog'
,'BIV_MEASURE6'
,'N') ||
'
/* Average Age Change */
, ' ||
biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_backlog_age'
,'oset.c_backlog'
,null
,'N')
,biv_dbi_tmpl_util.rate_column('oset.p_backlog_age'
,'oset.p_backlog'
,null
,'N')
,'BIV_MEASURE8'
,'N') ||
'
/* Aging Buckets */
' || biv_dbi_tmpl_util.get_bucket_outer_query
( p_bucket_rec => l_bucket_rec
, p_column_name_base => 'oset.c_age_bucket'
, p_alias_base => 'BIV_MEASURE10'
, p_total_flag => 'N'
, p_backlog_col => case
when p_distribution = 'Y' then 'oset.c_backlog'
else null
end
) ||
'
/* GT Backlog Current */
, nvl(oset.c_backlog_total,0) BIV_MEASURE11
/* GT Backlog Change */
, ' ||
biv_dbi_tmpl_util.change_column('oset.c_backlog_total'
,'oset.p_backlog_total'
,'BIV_MEASURE12') ||
'
/* GT Average Age Current */
, ' ||
biv_dbi_tmpl_util.rate_column('oset.c_backlog_age_total'
,'oset.c_backlog_total'
,'BIV_MEASURE13'
,'N') ||
'
/* GT Average Age Change */
, ' ||
biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('oset.c_backlog_age_total'
,'oset.c_backlog_total'
,null
,'N')
,biv_dbi_tmpl_util.rate_column('oset.p_backlog_age_total'
,'oset.p_backlog_total'
,null
,'N')
,'BIV_MEASURE14'
,'N') ||
'
/* GT Aging Buckets */
' || biv_dbi_tmpl_util.get_bucket_outer_query
( p_bucket_rec => l_bucket_rec
, p_column_name_base => 'oset.c_age_bucket'
, p_alias_base => 'BIV_MEASURE15'
, p_total_flag => 'Y'
, p_backlog_col => case
when p_distribution = 'Y' then 'oset.c_backlog'
else null
end
) ||
'
, ' ||
biv_dbi_tmpl_util.get_category_drill_down(l_view_by, case
when p_distribution = 'N' then
g_backlog_age_rep_func
else
g_backlog_age_dbn_rep_func
end) ||
biv_dbi_tmpl_util.drill_detail( g_backlog_detail_rep_func
, 0
, null
, 'BIV_ATTRIBUTE6') ||
case
when p_distribution = 'N' then
biv_dbi_tmpl_util.bucket_detail_drill( g_backlog_detail_rep_func
, l_bucket_rec
, 'BIV_ATTRIBUTE7' )
else
null
end ||
'
from
( select * from ( ' || 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_filter_where => '(c_backlog > 0 or p_backlog > 0)'
, p_generate_viewby => 'Y'
);
l_stmt := 'select
cal.name VIEWBY
/* Backlog Prior */
, nvl(iset.p_backlog,0) BIV_MEASURE1
/* Backlog Current */
, nvl(iset.c_backlog,0) BIV_MEASURE2
/* Backlog Change */
, ' ||
biv_dbi_tmpl_util.change_column('iset.c_backlog'
,'iset.p_backlog'
,'BIV_MEASURE4') ||
'
/* Average Age Prior */
, ' ||
biv_dbi_tmpl_util.rate_column('iset.p_backlog_age'
,'iset.p_backlog'
,'BIV_MEASURE5'
,'N') ||
'
/* Average Age Current */
, ' ||
biv_dbi_tmpl_util.rate_column('iset.c_backlog_age'
,'iset.c_backlog'
,'BIV_MEASURE6'
,'N') ||
'
/* Average Age Change */
, ' ||
biv_dbi_tmpl_util.change_column(biv_dbi_tmpl_util.rate_column('iset.c_backlog_age'
,'iset.c_backlog'
,null
,'N')
,biv_dbi_tmpl_util.rate_column('iset.p_backlog_age'
,'iset.p_backlog'
,null
,'N')
,'BIV_MEASURE8'
,'N') ||
'
/* Aging Buckets */
' || biv_dbi_tmpl_util.get_bucket_outer_query
( p_bucket_rec => l_bucket_rec
, p_column_name_base => 'iset.c_age_bucket'
, p_alias_base => 'BIV_MEASURE10'
, p_total_flag => 'N'
, p_backlog_col => case
when p_distribution = 'Y' then 'iset.c_backlog'
else null
end
)
|| ', NULL BIV_DYNAMIC_URL1, NULL BIV_DYNAMIC_URL2';
SELECT cal.report_date,
sum(c_backlog) c_backlog,
sum(p_backlog) p_backlog,
sum(c_backlog_age) c_backlog_age,
sum(p_backlog_age) p_backlog_age,
sum(c_age_bucket_b1) c_age_bucket_b1,
sum(c_age_bucket_b2) c_age_bucket_b2,
sum(c_age_bucket_b3) c_age_bucket_b3,
sum(c_age_bucket_b4) c_age_bucket_b4,
sum(c_age_bucket_b5) c_age_bucket_b5
';
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
, st.value biv_measure8 -- status
, decode(fact.escalated_date,null,&NO,&YES) biv_measure9
, decode(fact.unowned_date,null,&NO,&YES) biv_measure10
, fact.age biv_measure11
, fnd_date.date_to_displaydate(fact.incident_date) biv_measure12' ||
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.backlog_date_to = to_date(''31-12-4712'',''DD-MM-YYYY'')
' || 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)
;
select last_update_date into last_refresh_date
from bis_refresh_log
where object_name = p_object_name and status='SUCCESS'
and last_update_date =( select max(last_update_date)
from bis_refresh_log
where object_name= p_object_name and status='SUCCESS' );
SELECT SEQUENCE
INTO l_curr_year
FROM fii_time_ent_year
WHERE as_of_date BETWEEN start_date AND end_date;
SELECT start_date
INTO l_date
FROM fii_time_ent_year
WHERE SEQUENCE = l_curr_year - 3;
SELECT SEQUENCE
, ent_year_id
INTO l_curr_qtr
, l_curr_year
FROM fii_time_ent_qtr
WHERE as_of_date BETWEEN start_date AND end_date;
SELECT start_date
INTO l_date
FROM fii_time_ent_qtr
WHERE SEQUENCE = l_curr_qtr + 1
AND ent_year_id = l_curr_year - 2;
SELECT p.SEQUENCE
, q.ent_year_id
INTO l_curr_period
, l_curr_year
FROM fii_time_ent_period p
, fii_time_ent_qtr q
WHERE p.ent_qtr_id = q.ent_qtr_id
AND as_of_date BETWEEN p.start_date AND p.end_date;
SELECT start_date
INTO l_date
FROM (SELECT p.start_date
FROM fii_time_ent_period p
, fii_time_ent_qtr q
WHERE p.ent_qtr_id = q.ent_qtr_id
AND ( ( p.SEQUENCE = l_curr_period + 1
AND q.ent_year_id = l_curr_year - 1)
OR ( p.SEQUENCE = 1
AND q.ent_year_id = l_curr_year))
ORDER BY p.start_date)
WHERE ROWNUM <= 1;
/* select p.start_date
into l_date
from fii_time_ent_period p, fii_time_ent_qtr q
where p.ent_qtr_id=q.ent_qtr_id
and p.sequence=l_curr_period+1 -- temp fix for 12 points on graph else 13 points brrao modified
and q.ent_year_id=l_curr_year-1;
SELECT start_date
INTO l_week_start_date
FROM fii_time_week
WHERE as_of_date BETWEEN start_date AND end_date;
SELECT start_date
INTO l_date
FROM fii_time_week
WHERE start_date = l_week_start_date - 7 * 12;
SELECT start_date
INTO l_date
FROM fii_time_day
WHERE start_date = as_of_date - 6;