[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ELSE -- view by sales group, product category selected
l_prod_cat_where :=' AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';
ELSE -- a prod cat has been selected
l_prod_cat_from := ',
ENI_DENORM_HIERARCHIES eni_cat,
MTL_DEFAULT_CATEGORY_SETS mdcs';
SELECT '||l_viewby_col||',
SUM(FII_HIST_COL1) FII_HIST_COL1,
SUM(FII_HIST_COL2) FII_HIST_COL2,
SUM(FII_HIST_COL3) FII_HIST_COL3,
SUM(FII_HIST_COL4) FII_HIST_COL4,
SUM(FII_XTD_REV) FII_XTD_REV,
(((SUM(FII_XTD_REV) - SUM(PRIOR_REV)) / DECODE(SUM(PRIOR_REV),
0,NULL,SUM(PRIOR_REV))) * 100) FII_CHANGE,
SUM(PRIOR_REV) FII_PRIOR_XTD_REV,
SUM(FII_XTD_BOOKED) FII_XTD_BOOKED,
(((SUM(FII_XTD_BOOKED) - SUM(PRIOR_BOOKED)) / DECODE(SUM(PRIOR_BOOKED),0,
NULL,SUM(PRIOR_BOOKED))) * 100) FII_CHANGE_BOOKED,
SUM(PRIOR_BOOKED) FII_PRIOR_XTD_BOOKED,
(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) FII_BOOKED_PRIOR_XTD,
((((SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) - (SUM(PRIOR_REV)- SUM(PRIOR_BOOKED))) / DECODE((SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)),
0,NULL,(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)))) * 100) FII_BOOKED_PRIOR_CHANGE,
SUM(SUM(FII_HIST_COL1)) OVER () FII_GT_HIST_COL1,
SUM(SUM(FII_HIST_COL2)) OVER () FII_GT_HIST_COL2,
SUM(SUM(FII_HIST_COL3)) OVER () FII_GT_HIST_COL3,
SUM(SUM(FII_HIST_COL4)) OVER () FII_GT_HIST_COL4,
SUM(SUM(FII_XTD_REV)) OVER () FII_GT_XTD_REV,
SUM(SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_BOOKED,
((SUM(SUM(FII_XTD_REV)) OVER () - SUM(SUM(PRIOR_REV)) OVER ()) / DECODE(SUM(SUM(PRIOR_REV)) OVER (),
0,NULL,SUM(SUM(PRIOR_REV)) OVER ()) * 100) FII_GT_CHANGE,
((SUM(SUM(FII_XTD_BOOKED)) OVER () - SUM(SUM(PRIOR_BOOKED)) OVER ()) / DECODE(SUM(SUM(PRIOR_BOOKED)) OVER (),
0,NULL,SUM(SUM(PRIOR_BOOKED)) OVER ()) * 100) FII_GT_CHANGE_BOOKED,
SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_PRIOR_BOOKED,
(((SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER() - SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER()) / DECODE(SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER(),
0,NULL,SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER())) * 100) FII_GT_PRIOR_BOOKED_CHANGE,
( rank() over (ORDER BY SUM(FII_XTD_REV) '||l_sort||' nulls last, '||l_viewby_col||')) - 1 rnk
FROM (
SELECT '||l_viewby_col||',
0 FII_HIST_COL1,
0 FII_HIST_COL2,
0 FII_HIST_COL3,
0 FII_HIST_COL4,
SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_XTD_REV,
SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) PRIOR_REV,
SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
then f.booked_rev_wk_'||l_curr_suffix||' ELSE 0 END) FII_XTD_BOOKED,
SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
then f.booked_rev_wk_'||l_curr_suffix||' ELSE 0 END) PRIOR_BOOKED
FROM '||l_mv_to_be_used ||' f,
fii_time_structures cal'
||l_prod_cat_from||'
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
AND cal.report_date in ((&BIS_PREVIOUS_ASOF_DATE),(&BIS_CURRENT_ASOF_DATE))
AND f.recognized_amt_'||l_curr_suffix||' <> 0
'||l_flags||l_sg_where||l_prod_cat_where||l_cust_where||'
GROUP BY '||l_viewby_col||') c group by '||l_viewby_col||') f,' ;
SELECT '||l_viewby_col||',
SUM(FII_HIST_COL1) FII_HIST_COL1,
SUM(FII_HIST_COL2) FII_HIST_COL2,
SUM(FII_HIST_COL3) FII_HIST_COL3,
SUM(FII_HIST_COL4) FII_HIST_COL4,
SUM(FII_XTD_REV) FII_XTD_REV,
(((SUM(FII_XTD_REV) - SUM(PRIOR_REV)) / DECODE(SUM(PRIOR_REV),
0,NULL,SUM(PRIOR_REV))) * 100) FII_CHANGE,
SUM(PRIOR_REV) FII_PRIOR_XTD_REV,
SUM(FII_XTD_BOOKED) FII_XTD_BOOKED,
(((SUM(FII_XTD_BOOKED) - SUM(PRIOR_BOOKED)) / DECODE(SUM(PRIOR_BOOKED),0,
NULL,SUM(PRIOR_BOOKED))) * 100) FII_CHANGE_BOOKED,
SUM(PRIOR_BOOKED) FII_PRIOR_XTD_BOOKED,
(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) FII_BOOKED_PRIOR_XTD,
((((SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) - (SUM(PRIOR_REV)- SUM(PRIOR_BOOKED))) / DECODE((SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)),
0,NULL,(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)))) * 100) FII_BOOKED_PRIOR_CHANGE,
SUM(SUM(FII_HIST_COL1)) OVER () FII_GT_HIST_COL1,
SUM(SUM(FII_HIST_COL2)) OVER () FII_GT_HIST_COL2,
SUM(SUM(FII_HIST_COL3)) OVER () FII_GT_HIST_COL3,
SUM(SUM(FII_HIST_COL4)) OVER () FII_GT_HIST_COL4,
SUM(SUM(FII_XTD_REV)) OVER () FII_GT_XTD_REV,
SUM(SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_BOOKED,
((SUM(SUM(FII_XTD_REV)) OVER () - SUM(SUM(PRIOR_REV)) OVER ()) / DECODE(SUM(SUM(PRIOR_REV)) OVER (),
0,NULL,SUM(SUM(PRIOR_REV)) OVER ()) * 100) FII_GT_CHANGE,
((SUM(SUM(FII_XTD_BOOKED)) OVER () - SUM(SUM(PRIOR_BOOKED)) OVER ()) / DECODE(SUM(SUM(PRIOR_BOOKED)) OVER (),
0,NULL,SUM(SUM(PRIOR_BOOKED)) OVER ()) * 100) FII_GT_CHANGE_BOOKED,
SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_PRIOR_BOOKED,
(((SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER() - SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER()) / DECODE(SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER(),
0,NULL,SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER())) * 100) FII_GT_PRIOR_BOOKED_CHANGE,
( rank() over (ORDER BY SUM(FII_XTD_REV) '||l_sort||' nulls last, '||l_viewby_col||')) - 1 rnk
FROM (
SELECT '||l_viewby_col||',
0 FII_HIST_COL1,
0 FII_HIST_COL2,
0 FII_HIST_COL3,
0 FII_HIST_COL4,
SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_XTD_REV,
SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) PRIOR_REV,
SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
then f.booked_rev_pe_'||l_curr_suffix||' ELSE 0 END) FII_XTD_BOOKED,
SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
then f.booked_rev_pe_'||l_curr_suffix||' ELSE 0 END) PRIOR_BOOKED
FROM '||l_mv_to_be_used ||' f,
fii_time_structures cal'
||l_prod_cat_from||'
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
AND cal.report_date in ((&BIS_PREVIOUS_ASOF_DATE),(&BIS_CURRENT_ASOF_DATE))
AND f.recognized_amt_'||l_curr_suffix||' <> 0
'||l_flags||l_sg_where||l_prod_cat_where||l_cust_where||'
GROUP BY '||l_viewby_col||') c group by '||l_viewby_col||') f,' ;
SELECT '||l_viewby_col||',
SUM(FII_HIST_COL1) FII_HIST_COL1,
SUM(FII_HIST_COL2) FII_HIST_COL2,
SUM(FII_HIST_COL3) FII_HIST_COL3,
SUM(FII_HIST_COL4) FII_HIST_COL4,
SUM(FII_XTD_REV) FII_XTD_REV,
(((SUM(FII_XTD_REV) - SUM(PRIOR_REV)) / DECODE(SUM(PRIOR_REV),
0,NULL,SUM(PRIOR_REV))) * 100) FII_CHANGE,
SUM(PRIOR_REV) FII_PRIOR_XTD_REV,
SUM(FII_XTD_BOOKED) FII_XTD_BOOKED,
(((SUM(FII_XTD_BOOKED) - SUM(PRIOR_BOOKED)) / DECODE(SUM(PRIOR_BOOKED),0,
NULL,SUM(PRIOR_BOOKED))) * 100) FII_CHANGE_BOOKED,
SUM(PRIOR_BOOKED) FII_PRIOR_XTD_BOOKED,
(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) FII_BOOKED_PRIOR_XTD,
((((SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) - (SUM(PRIOR_REV)- SUM(PRIOR_BOOKED))) / DECODE((SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)),
0,NULL,(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)))) * 100) FII_BOOKED_PRIOR_CHANGE,
SUM(SUM(FII_HIST_COL1)) OVER () FII_GT_HIST_COL1,
SUM(SUM(FII_HIST_COL2)) OVER () FII_GT_HIST_COL2,
SUM(SUM(FII_HIST_COL3)) OVER () FII_GT_HIST_COL3,
SUM(SUM(FII_HIST_COL4)) OVER () FII_GT_HIST_COL4,
SUM(SUM(FII_XTD_REV)) OVER () FII_GT_XTD_REV,
SUM(SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_BOOKED,
((SUM(SUM(FII_XTD_REV)) OVER () - SUM(SUM(PRIOR_REV)) OVER ()) / DECODE(SUM(SUM(PRIOR_REV)) OVER (),
0,NULL,SUM(SUM(PRIOR_REV)) OVER ()) * 100) FII_GT_CHANGE,
((SUM(SUM(FII_XTD_BOOKED)) OVER () - SUM(SUM(PRIOR_BOOKED)) OVER ()) / DECODE(SUM(SUM(PRIOR_BOOKED)) OVER (),
0,NULL,SUM(SUM(PRIOR_BOOKED)) OVER ()) * 100) FII_GT_CHANGE_BOOKED,
SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER () FII_GT_XTD_PRIOR_BOOKED,
(((SUM(SUM(FII_XTD_REV) - SUM(FII_XTD_BOOKED)) OVER() - SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER()) / DECODE(SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER(),
0,NULL,SUM(SUM(PRIOR_REV)- SUM(PRIOR_BOOKED)) OVER())) * 100) FII_GT_PRIOR_BOOKED_CHANGE,
( rank() over (ORDER BY SUM(FII_XTD_REV) '||l_sort||' nulls last, '||l_viewby_col||')) - 1 rnk
FROM (
SELECT '||l_viewby_col||',
SUM(CASE WHEN cal.report_date in to_date(:FII_THREE_PERIOD_BACK,''DD-MM-YYYY'')
THEN f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_HIST_COL1,
SUM(CASE WHEN cal.report_date in to_date(:FII_TWO_PERIOD_BACK,''DD-MM-YYYY'')
THEN f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_HIST_COL2,
SUM(CASE WHEN cal.report_date in to_date(:FII_ONE_PERIOD_BACK,''DD-MM-YYYY'')
THEN f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_HIST_COL3,
SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
THEN f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_HIST_COL4,
0 FII_XTD_REV,
0 PRIOR_REV,
0 FII_XTD_BOOKED,
0 PRIOR_BOOKED
FROM '||l_mv_to_be_used ||' f,
fii_time_structures cal'
||l_prod_cat_from||'
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id, :FII_RECORD_ID) = :FII_RECORD_ID
AND cal.report_date in (to_date(:FII_THREE_PERIOD_BACK,''DD-MM-YYYY''),to_date(:FII_TWO_PERIOD_BACK,''DD-MM-YYYY''),to_date(:FII_ONE_PERIOD_BACK,''DD-MM-YYYY''),
(&BIS_CURRENT_ASOF_DATE))
AND f.recognized_amt_'||l_curr_suffix||' <> 0
'||l_flags||l_sg_where||l_prod_cat_where||l_cust_where||'
GROUP BY '||l_viewby_col||'
UNION ALL
SELECT '||l_viewby_col||',
0 FII_HIST_COL1,
0 FII_HIST_COL2,
0 FII_HIST_COL3,
0 FII_HIST_COL4,
SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) FII_XTD_REV,
SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
then f.recognized_amt_'||l_curr_suffix||' ELSE 0 END) PRIOR_REV,
SUM(CASE WHEN cal.report_date in (&BIS_CURRENT_ASOF_DATE)
then f.booked_rev_'||l_pertype_for_booked||'_'||l_curr_suffix||' ELSE 0 END) FII_XTD_BOOKED,
SUM(CASE WHEN cal.report_date in (&BIS_PREVIOUS_ASOF_DATE)
then f.booked_rev_'||l_pertype_for_booked||'_'||l_curr_suffix||' ELSE 0 END) PRIOR_BOOKED
FROM '||l_mv_to_be_used ||' f,
fii_time_structures cal'
||l_prod_cat_from||'
WHERE f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
AND cal.report_date in ((&BIS_PREVIOUS_ASOF_DATE),(&BIS_CURRENT_ASOF_DATE))
AND f.recognized_amt_'||l_curr_suffix||' <> 0
'||l_flags||l_sg_where||l_prod_cat_where||l_cust_where||'
GROUP BY '||l_viewby_col||') c group by '||l_viewby_col||') f,' ;
SELECT DECODE(f.resource_id,NULL,g.group_name,
r.resource_name) VIEWBY,
DECODE(f.resource_id,NULL,to_char(f.sales_grp_id),
f.resource_id||''.''||f.sales_grp_id)
VIEWBYID,
DECODE(sum(fii_xtd_rev),0,null,decode(f.resource_id, NULL,
''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'',
NULL)) FII_SALES_GROUP_DRILL,
NULL FII_PROD_CAT_DRILL,
'||l_inner_sql||'
JTF_RS_GROUPS_VL g,
JTF_RS_RESOURCE_EXTNS_VL r
WHERE f.sales_grp_id = g.group_id
AND f.resource_id = r.resource_id(+)
AND ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))
GROUP BY f.resource_id,g.group_name,r.resource_name,f.sales_grp_id
&ORDER_BY_CLAUSE ' ;
SELECT ecat.value VIEWBY,
ecat.id VIEWBYID,
NULL FII_SALES_GROUP_DRILL, -- Drill - Sales Group
DECODE(SUM(fii_xtd_rev),0,NULL,DECODE(ecat.leaf_node_flag, ''Y'',NULL,
''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y''))
FII_PROD_CAT_DRILL, -- Drill - Prod Category
'||l_inner_sql||'
ENI_ITEM_VBH_NODES_V ecat
WHERE ecat.parent_id = ecat.child_id
'||l_cat_join||'
AND ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))
GROUP BY ecat.value,ecat.id,ecat.leaf_node_flag
&ORDER_BY_CLAUSE ';
SELECT cust.value VIEWBY,
cust.id VIEWBYID,
NULL FII_SALES_GROUP_DRILL,
NULL FII_PROD_CAT_DRILL, '
||l_inner_sql||'
FII_CUSTOMERS_V cust
WHERE f.customer_id = cust.id
AND ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))
GROUP BY cust.value,cust.id
&ORDER_BY_CLAUSE ';