[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
l_cust_flag := 0; -- customer selected
SELECT t.name VIEWBY
, s.cur_return_amt ENI_MEASURE1 -- curr return value
, s.pre_return_amt ENI_MEASURE2 -- prev return value
, (s.cur_return_amt - s.pre_return_amt)
/ decode( s.pre_return_amt,0, NULL,
abs(s.pre_return_amt)) * 100 ENI_MEASURE3 -- return value change
FROM ( SELECT dates.start_date START_DATE
, sum(decode(dates.period, ''C'', fact.returned_amt_'||l_curr_suffix||', 0)) CUR_RETURN_AMT
, sum(decode(dates.period, ''P'', fact.returned_amt_'||l_curr_suffix||', 0)) PRE_RETURN_AMT
FROM ( SELECT c.start_date START_DATE
, ''C'' PERIOD
, least(c.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' c
WHERE c.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT c.start_date START_DATE
, ''P'' PERIOD
, least(p.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' p, '||l_period_type||' c
WHERE ( p.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE )
AND ( c.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE )
AND '||l_time_comp_where||'
) dates
, ISC_DBI_CFM_'||l_mv||'_MV fact
, FII_TIME_RPT_STRUCT cal'
||l_prod_cat_from||'
WHERE cal.report_date = dates.report_date
AND bitand( cal.record_type_id, &BIS_NESTED_PATTERN ) = cal.record_type_id
AND fact.time_id = cal.time_id
AND fact.period_type_id = cal.period_type_id'
||l_flags_where||l_org_where||l_prod_cat_where||l_prod_where||l_cust_where||l_ret_reason_where||'
GROUP BY dates.start_date ) s
, '||l_period_type||' t
WHERE t.start_date = s.start_date(+)
AND t.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
' || l_order_by;