[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ELSE -- view by sales group, prod.cat selected
l_prod_cat_where :='
AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';
SELECT 0 ISC_MEASURE_2,
0 ISC_MEASURE_1,
0 ISC_MEASURE_3,
0 ISC_MEASURE_8,
0 ISC_MEASURE_7,
0 ISC_MEASURE_9,
0 ISC_MEASURE_10,
0 ISC_MEASURE_11,
0 ISC_MEASURE_12,
0 ISC_MEASURE_5,
0 ISC_MEASURE_4,
0 ISC_MEASURE_6
FROM dual
WHERE 1 = 2';
SELECT fii.name VIEWBY,
nvl(s.p_net_book, 0) ISC_MEASURE_2, -- Prior (Net Booked)
nvl(s.c_net_book, 0) ISC_MEASURE_1, -- Net Booked
(s.c_net_book - s.p_net_book)
/ decode(s.p_net_book, 0, NULL,
abs(s.p_net_book)) * 100 ISC_MEASURE_3, -- Change (Net Booked)
nvl(s.p_rev_rec, 0) ISC_MEASURE_8, -- Prior (Revenue)
nvl(s.c_rev_rec, 0) ISC_MEASURE_7, -- Revenue
(s.c_rev_rec - s.p_rev_rec)
/ decode(s.p_rev_rec, 0, NULL,
abs(s.p_rev_rec)) * 100 ISC_MEASURE_9, -- Change (Revenue)
nvl(s.p_rev_book, 0) ISC_MEASURE_10, -- Prior (Rev Booked this Per)
nvl(s.c_rev_book, 0) ISC_MEASURE_11, -- Revenue Booked this Period
(s.c_rev_book - s.p_rev_book)
/ decode(s.p_rev_book, 0, null,
abs(s.p_rev_book)) *100 ISC_MEASURE_12, -- Change (Rev Booked this Per)
nvl(s.p_rev_backlog, 0) ISC_MEASURE_5, -- Prior (Revenue Backlog)
nvl(s.c_rev_backlog, 0) ISC_MEASURE_4, -- Revenue Backlog
(s.c_rev_backlog - s.p_rev_backlog)
/ decode(s.p_rev_backlog, 0, NULL,
abs(s.p_rev_backlog)) * 100 ISC_MEASURE_6 /* Change (Revenue Backlog) */
FROM (SELECT start_date START_DATE,
sum(c_book_xtd) C_NET_BOOK,
sum(p_book_xtd) P_NET_BOOK,
sum(c_backlog) + sum(c_defer_rev) C_REV_BACKLOG,
sum(p_backlog) + sum(p_defer_rev) P_REV_BACKLOG,
sum(c_rev_rec_xtd) C_REV_REC,
sum(p_rev_rec_xtd) P_REV_REC,
sum(c_rev_book_xtd) C_REV_BOOK,
sum(p_rev_book_xtd) P_REV_BOOK
FROM /* Compute XTD components */
(SELECT dates.start_date START_DATE,
decode(dates.period, ''C'',
nvl(net_booked_amt_'||l_curr_suffix||', 0), 0) C_BOOK_XTD,
decode(dates.period, ''P'',
nvl(net_booked_amt_'||l_curr_suffix||', 0), 0) P_BOOK_XTD,
decode(dates.period, ''C'',
nvl(recognized_amt_'||l_curr_suffix||', 0), 0) C_REV_REC_XTD,
decode(dates.period, ''P'',
nvl(recognized_amt_'||l_curr_suffix||', 0), 0) P_REV_REC_XTD,
decode(dates.period, ''C'',
nvl('||l_rev_book||', 0), 0) C_REV_BOOK_XTD,
decode(dates.period, ''P'',
nvl('||l_rev_book||', 0), 0) P_REV_BOOK_XTD,
0 C_BACKLOG,
0 P_BACKLOG,
0 C_DEFER_REV,
0 P_DEFER_REV
FROM (SELECT fii.start_date START_DATE,
''C'' PERIOD,
least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY fii.start_date DESC) p1,
(SELECT fii.start_date START_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
ORDER BY fii.start_date DESC) p2
WHERE p1.id(+) = p2.id) dates,
'||l_mv||' f,
FII_TIME_RPT_STRUCT_V cal'
||l_prod_cat_from||'
WHERE cal.report_date = dates.report_date
AND f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
||l_flags
||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
UNION ALL /* Compute ITD components */
SELECT dates.start_date START_DATE,
0 C_BOOK_XTD,
0 P_BOOK_XTD,
0 C_REV_REC_XTD,
0 P_REV_REC_XTD,
0 C_REV_BOOK_XTD,
0 P_REV_BOOK_XTD,
decode(dates.period, ''C'',
nvl(backlog_amt_'||l_curr_suffix||', 0), 0) C_BACKLOG,
decode(dates.period, ''P'',
nvl(backlog_amt_'||l_curr_suffix||', 0), 0) P_BACKLOG,
decode(dates.period, ''C'',
nvl(deferred_amt_'||l_curr_suffix||', 0), 0) C_DEFER_REV,
decode(dates.period, ''P'',
nvl(deferred_amt_'||l_curr_suffix||', 0), 0) P_DEFER_REV
FROM (SELECT fii.start_date START_DATE,
''C'' PERIOD,
least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY fii.start_date DESC) p1,
(SELECT fii.start_date START_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
ORDER BY fii.start_date DESC) p2
WHERE p1.id(+) = p2.id) dates,
'||l_mv||' f,
FII_TIME_RPT_STRUCT_V cal'
||l_prod_cat_from||'
WHERE cal.report_date = dates.report_date
AND f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id, 1143) = cal.record_type_id'
||l_flags
||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||')
GROUP BY start_date) s,
'||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
AND fii.start_date = s.start_date(+)
ORDER BY fii.start_date';