[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
ELSE -- When item is selected, set the where clause and the item cat flag
l_prod_where := '
AND mv.master_item_id IN (&ITEM+ENI_ITEM)';
l_cust_flag := 0; -- customer selected
SELECT ret.value VIEWBY -- return reason
, ret.id VIEWBYID
, c.prev_return ENI_MEASURE7 -- return value (prior)
, c.curr_return ENI_MEASURE1 -- return value
, (c.curr_return - c.prev_return)
/ decode(c.prev_return, 0, NULL,
abs(c.prev_return)) * 100 ENI_MEASURE2 -- change (return value),
, c.curr_return
/ decode(sum(c.curr_return) over(), 0, NULL,
sum(c.curr_return) over())
* 100 ENI_MEASURE3 -- percent of total
, c.lines_cnt ENI_MEASURE4 -- lines affected
, (CASE WHEN c.lines_cnt IS NULL
OR c.lines_cnt = 0
THEN NULL
ELSE '|| l_open_url ||' END) as ENI_ATTRIBUTE2 -- drill for lines affected
, sum(c.curr_return) over() ENI_MEASURE5 -- grand total for return value
, (sum(c.curr_return) over() - sum(c.prev_return) over())
/ decode(sum(c.prev_return) over(), 0, NULL,
abs(sum(c.prev_return) over()))
* 100 ENI_MEASURE6 -- grand total for return value change
, sum(c.lines_cnt) over() ENI_MEASURE8 -- grand total for lines affected
FROM (SELECT mv.return_reason REASON
, sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
mv.returned_amt_' || l_curr_suffix || ', 0)) CURR_RETURN
, sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
mv.returned_amt_' || l_curr_suffix || ', 0)) PREV_RETURN
, sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
mv.lines_cnt, 0)) LINES_CNT
FROM ISC_DBI_CFM_007_MV mv
, FII_TIME_RPT_STRUCT cal'
|| l_prod_cat_from ||'
WHERE mv.time_id = cal.time_id
AND mv.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
AND mv.customer_flag = :ENI_CUST_FLAG
AND mv.item_cat_flag = :ENI_ITEM_CAT_FLAG
AND mv.return_reason_flag = 0'
|| l_prod_cat_where || l_prod_where || l_cust_where || l_ret_reason_where || '
GROUP BY mv.return_reason ) c
, BIS_ORDER_ITEM_RET_REASON_V ret
WHERE c.reason = ret.id
-- &ORDER_BY_CLAUSE NULLS LAST';