[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_cust_flag NUMBER; -- 0 for customer and 1 for no customer selected
SELECT fii.name VIEWBY
, nvl(s.prev_booked_value, 0) ENI_MEASURE1 -- book prior
, nvl(s.curr_booked_value, 0) ENI_MEASURE2 -- book
, (s.curr_booked_value-s.prev_booked_value)
/ decode(s.prev_booked_value, 0, NULL,
abs(s.prev_booked_value)) * 100 ENI_MEASURE3 -- book change
, nvl(s.prev_fulfill_value, 0) ENI_MEASURE4 -- fulf prior
, nvl(s.curr_fulfill_value, 0) ENI_MEASURE5 -- fulf
, (s.curr_fulfill_value-s.prev_fulfill_value)
/ decode(s.prev_fulfill_value, 0, NULL,
abs(s.prev_fulfill_value)) * 100 ENI_MEASURE6 -- fulf change
, s.prev_booked_value
/ decode(s.prev_fulfill_value, 0, NULL,
s.prev_fulfill_value) ENI_MEASURE7 -- book to fulf r prior
, s.curr_booked_value
/ decode(s.curr_fulfill_value, 0, NULL,
s.curr_fulfill_value) ENI_MEASURE8 -- book to fulf r
, s.curr_booked_value
/ decode(s.curr_fulfill_value, 0, NULL,
s.curr_fulfill_value) -
s.prev_booked_value
/ decode(s.prev_fulfill_value, 0, NULL,
s.prev_fulfill_value) ENI_MEASURE9 -- book to fulf r change
FROM (SELECT start_date START_DATE
, sum(curr_booked_value) CURR_BOOKED_VALUE
, sum(prev_booked_value) PREV_BOOKED_VALUE
, sum(curr_fulfill_value) CURR_FULFILL_VALUE
, sum(prev_fulfill_value) PREV_FULFILL_VALUE
FROM
(SELECT dates.start_date START_DATE
, fact.inv_org_id INV_ORG
, decode(dates.period, ''C'',
nvl(fact.booked_amt_'||l_curr_suffix||',0), 0) CURR_BOOKED_VALUE
, decode(dates.period, ''P'',
nvl(fact.booked_amt_'||l_curr_suffix||',0), 0) PREV_BOOKED_VALUE
, 0 CURR_FULFILL_VALUE
, 0 PREV_FULFILL_VALUE
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_mv1||' fact
, FII_TIME_RPT_STRUCT cal'||l_prod_cat_from||'
WHERE cal.report_date = dates.report_date
AND fact.time_id = cal.time_id
AND fact.period_type_id = cal.period_type_id'
||l_flags_where||'
AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
||l_prod_cat_where
||l_prod_where
||l_cust_where||'
UNION ALL
SELECT dates.start_date START_DATE
, fact.inv_org_id INV_ORG
, 0 CURR_BOOKED_VALUE
, 0 PREV_BOOKED_VALUE
, decode(dates.period, ''C'',
nvl(fact.fulfilled_amt_'||l_curr_suffix||',0), 0) CURR_FULFILL_VALUE
, decode(dates.period, ''P'',
nvl(fact.fulfilled_amt_'||l_curr_suffix||',0), 0) PREV_FULFILL_VALUE
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_mv2||' fact
, FII_TIME_RPT_STRUCT cal'||l_prod_cat_from||'
WHERE cal.report_date = dates.report_date
AND fact.time_id = cal.time_id
AND fact.period_type_id = cal.period_type_id'
||l_flags_where||'
AND fact.return_flag = 0
AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
||l_prod_cat_where
||l_prod_where
||l_cust_where||' )
-- WHERE '||l_inv_org_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 ' || l_order_by;