[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_conv_rate_selected VARCHAR2(200);
l_outer_select VARCHAR2(5000);
l_insert_stmnt VARCHAR2(5000);
l_inner_select VARCHAR2(5000);
,x_conv_rate_selected => l_conv_rate_selected
,x_sg_id => l_sg_id
,x_parent_sg_id => l_parent_sg_id_num
,x_resource_id => l_resource_id
,x_prodcat_id => l_prodcat_id
,x_curr_page_time_id => l_curr_page_time_id
,x_prev_page_time_id => l_prev_page_time_id
,x_comp_type => l_comp_type
,x_parameter_valid => l_parameter_valid
,x_as_of_date => l_curr_as_of_date
,x_page_period_type => l_page_period_type
,x_prior_as_of_date => l_prev_date
,x_record_type_id => l_record_type_id
,x_viewby => l_viewby );
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '_s';
'l_conv_rate_selected => '|| l_conv_rate_selected ||', ' ||
'l_bitand_id => '|| l_bitand_id ||', ' ||
'l_period_type => '|| l_period_type ||', ' ||
'l_sg_id => '|| l_sg_id ||', ' ||
'l_resource_id => '|| l_resource_id ||', ' ||
'l_bis_sysdate => '|| l_bis_sysdate ||', ' ||
'l_fst_crdt_type => '|| l_fst_crdt_type ||', '||
'l_prodcat_id => '|| l_prodcat_id ||', '||
'l_record_type_id => '|| l_record_type_id ||', '||
'l_prodcat_id => '|| l_prodcat_id;
l_outer_select := 'SELECT VIEWBY ';
l_outer_select := l_outer_select ||',DECODE(BIL_URL1,NULL,VIEWBYID||''.''||:l_sg_id_num,VIEWBYID) VIEWBYID ';
l_outer_select := l_outer_select ||',VIEWBYID ';
l_outer_select := l_outer_select ||',(BIL_MEASURE5/DECODE(BIL_MEASURE2,0,NULL,BIL_MEASURE2)) * 100 BIL_MEASURE1 '||
',BIL_MEASURE2 '||
',BIL_MEASURE3 '||
',(BIL_MEASURE2-BIL_MEASURE3)/ABS(DECODE(BIL_MEASURE3,0,NULL,BIL_MEASURE3))*100 BIL_MEASURE4 '||
',BIL_MEASURE5 '||
',BIL_MEASURE6 '||
',(BIL_MEASURE5-BIL_MEASURE6)/ABS(DECODE(BIL_MEASURE6,0,NULL,BIL_MEASURE6))*100 BIL_MEASURE7 '||
',DECODE(BIL_MEASURE8,0,NULL,BIL_MEASURE8) BIL_MEASURE8 '||
',BIL_MEASURE9 '||
',(BIL_MEASURE8-BIL_MEASURE9)/ABS(DECODE(BIL_MEASURE9, 0, NULL, BIL_MEASURE9))*100 BIL_MEASURE10 '||
',DECODE(BIL_MEASURE14,0,NULL,BIL_MEASURE14) BIL_MEASURE14 '||
',BIL_MEASURE15 '||
',(BIL_MEASURE14-BIL_MEASURE15)/ABS(DECODE(BIL_MEASURE15, 0, NULL, BIL_MEASURE15))*100 BIL_MEASURE16 '||
',(BIL_MEASURE14/(DECODE(BIL_MEASURE2,0,NULL,BIL_MEASURE2))*100) BIL_MEASURE17 '||
',(SUM(BIL_MEASURE5) OVER()/DECODE(SUM(BIL_MEASURE2) OVER(),0,NULL,SUM(BIL_MEASURE2) OVER())) * 100 BIL_MEASURE24 '||
',SUM(BIL_MEASURE2) OVER() BIL_MEASURE25 '||
',SUM(BIL_MEASURE3) OVER() BIL_MEASURE26'||
',(SUM(BIL_MEASURE2) OVER() - SUM(BIL_MEASURE3) OVER())/ABS(DECODE(SUM(BIL_MEASURE3) OVER(), 0, NULL, '||
' SUM(BIL_MEASURE3) OVER()))*100 BIL_MEASURE27 '||
',SUM(BIL_MEASURE5) OVER() BIL_MEASURE28 '||
',SUM(BIL_MEASURE6) OVER() BIL_MEASURE29 '||
',(SUM(BIL_MEASURE5) OVER() - SUM(BIL_MEASURE6) OVER())/ABS(DECODE(SUM(BIL_MEASURE6) OVER(), 0, NULL, '||
' SUM(BIL_MEASURE6) OVER()))*100 BIL_MEASURE30 '||
',SUM(DECODE(BIL_MEASURE8,0,NULL,BIL_MEASURE8)) OVER() BIL_MEASURE31 '||
',SUM(BIL_MEASURE9) OVER() BIL_MEASURE32 '||
',(SUM(BIL_MEASURE8) OVER() - SUM(BIL_MEASURE9) OVER())/ABS(DECODE(SUM(BIL_MEASURE9) OVER(), 0, NULL, '||
' SUM(BIL_MEASURE9) OVER()))*100 BIL_MEASURE33 '||
',SUM(DECODE(BIL_MEASURE14,0,NULL,BIL_MEASURE14)) OVER() BIL_MEASURE37 '||
',SUM(BIL_MEASURE15) OVER() BIL_MEASURE38 '||
',(SUM(BIL_MEASURE14) OVER() - SUM(BIL_MEASURE15) OVER())/ABS(DECODE(SUM(BIL_MEASURE15) OVER(), 0, NULL '||
' , SUM(BIL_MEASURE15) OVER()))*100 BIL_MEASURE39 '||
',(SUM(BIL_MEASURE14) OVER()/(DECODE(SUM(BIL_MEASURE2) OVER(),0,NULL,SUM(BIL_MEASURE2) OVER())))*100 BIL_MEASURE40 '||
',BIL_URL1 '||
',BIL_URL2 '||
','''||l_netBooked_URL||''' BIL_URL3 ' ||
','''||l_Revenue_URL||''' BIL_URL4
,DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'',
DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=WON'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=WON'''||'))
BIL_URL5
';
l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1 (VIEWBY, VIEWBYID, SORTORDER,BIL_MEASURE2,BIL_MEASURE3,
BIL_MEASURE5,BIL_MEASURE6,BIL_MEASURE8, BIL_MEASURE9,
BIL_MEASURE14,BIL_MEASURE15, BIL_URL1, BIL_URL2)';
l_inner_select := ' SELECT VIEWBY,VIEWBYID,SORTORDER,SUM(BIL_MEASURE2) BIL_MEASURE2,SUM(BIL_MEASURE3) BIL_MEASURE3
,SUM(BIL_MEASURE5) BIL_MEASURE5,SUM(BIL_MEASURE6) BIL_MEASURE6
,SUM(BIL_MEASURE8) BIL_MEASURE8,SUM(BIL_MEASURE9) BIL_MEASURE9
,SUM(BIL_MEASURE14) BIL_MEASURE14,SUM(BIL_MEASURE15) BIL_MEASURE15 ';
l_inner_select := l_inner_select ||',BIL_URL1, NULL BIL_URL2 FROM (';
x_custom_sql := 'SELECT decode(sumry.salesrep_id, NULL, grptl.group_name,
restl.resource_name) VIEWBY,
decode(sumry.salesrep_id, NULL, sumry.sales_group_id,
sumry.salesrep_id) VIEWBYID,
SORTORDER,BIL_MEASURE2,BIL_MEASURE3,BIL_MEASURE5,BIL_MEASURE6,
BIL_MEASURE8,BIL_MEASURE9,BIL_MEASURE14,BIL_MEASURE15 ,
BIL_URL1,
DECODE(sumry.salesrep_id, NULL, NULL,'''||l_drill_link||''') BIL_URL2
FROM (
SELECT /*+ NO_MERGE */ salesrep_id, sales_group_id,
SORTORDER,SUM(BIL_MEASURE2) BIL_MEASURE2,SUM(BIL_MEASURE3) BIL_MEASURE3
,SUM(BIL_MEASURE5) BIL_MEASURE5,SUM(BIL_MEASURE6) BIL_MEASURE6
,SUM(BIL_MEASURE8) BIL_MEASURE8,SUM(BIL_MEASURE9) BIL_MEASURE9
,SUM(BIL_MEASURE14) BIL_MEASURE14,SUM(BIL_MEASURE15) BIL_MEASURE15
,BIL_URL1
,NULL BIL_URL2
FROM (';
x_custom_sql := x_custom_sql ||'SELECT /*+ leading (cal) */ sumry.salesrep_id, sumry.sales_group_id
,(CASE WHEN sumry.salesrep_id IS NULL THEN 1 ELSE 2 END) SORTORDER
,'||l_sql_stmnt1||
',(CASE WHEN sumry.salesrep_id IS NULL THEN '''||l_url_str||''' ELSE NULL END) BIL_URL1
,NULL BIL_URL2
'||l_from1||'
'||l_opty_denorm||'
'||l_where_clause1||'
'||l_product_where_clause;
SELECT /*+ leading (cal) */ sumry.salesrep_id, sumry.sales_group_id
,(CASE WHEN sumry.salesrep_id IS NULL THEN 1 ELSE 2 END) SORTORDER
,'||l_sql_stmnt2||
',(CASE WHEN sumry.salesrep_id IS NULL THEN '''||l_url_str||''' ELSE NULL END) BIL_URL1
,NULL BIL_URL2
'||l_from2||'
'||l_denorm||'
'||l_where_clause2||'
'||l_product_where_clause1;
SELECT /*+ leading (cal) */ sumry.resource_id salesrep_id,sumry.sales_grp_id sales_group_id
,(CASE WHEN sumry.resource_id IS NULL THEN 1 ELSE 2 END) SORTORDER
,'||l_sql_stmnt3||
',(CASE WHEN sumry.resource_id IS NULL THEN '''||l_url_str||''' ELSE NULL END) BIL_URL1
,NULL BIL_URL2
'||l_from3||'
'||l_denorm||'
'||l_where_clause3||'
'||l_product_where_clause2;
EXECUTE IMMEDIATE l_insert_stmnt || x_custom_sql
USING l_curr_as_of_date
, l_prev_date, l_record_type_id, l_record_type_id
, l_sg_id_num, l_curr_as_of_date, l_prev_date --Opp
, l_curr_page_time_id, l_curr_as_of_date
, l_prev_page_time_id, l_prev_date, l_bitand_id
, l_bitand_id,l_period_type, l_sg_id_num
, l_fst_crdt_type, l_fst_crdt_type
, l_curr_as_of_date, l_prev_date
, l_curr_page_time_id, l_prev_page_time_id --Frcst
, l_curr_as_of_date, l_prev_date
, l_curr_as_of_date, l_prev_date
, l_sg_id_num, l_curr_as_of_date
, l_prev_date, l_record_type_id, l_record_type_id;
EXECUTE IMMEDIATE l_insert_stmnt || x_custom_sql
USING l_curr_as_of_date
, l_prev_date, l_record_type_id, l_record_type_id
, l_sg_id_num, l_curr_as_of_date
, l_prev_date, l_prodcat_id --Opp
, l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id
, l_prev_date, l_bitand_id,l_bitand_id, l_period_type, l_sg_id_num
, l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date
, l_prev_date, l_curr_page_time_id, l_prev_page_time_id, l_prodcat_id --Frcst
, l_curr_as_of_date, l_prev_date
, l_curr_as_of_date, l_prev_date
, l_sg_id_num, l_curr_as_of_date
, l_prev_date, l_record_type_id,l_record_type_id,l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt || x_custom_sql
USING l_curr_as_of_date, l_prev_date
, l_record_type_id, l_record_type_id,l_sg_id_num, l_curr_as_of_date
, l_prev_date,l_resource_id --Opp
, l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id
, l_prev_date, l_bitand_id, l_bitand_id,l_period_type, l_sg_id_num
, l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date
, l_prev_date, l_curr_page_time_id, l_prev_page_time_id,l_resource_id --Frcst
, l_curr_as_of_date, l_prev_date
, l_curr_as_of_date, l_prev_date
, l_sg_id_num, l_curr_as_of_date
, l_prev_date, l_record_type_id, l_record_type_id,l_resource_id;
EXECUTE IMMEDIATE l_insert_stmnt || x_custom_sql
USING l_curr_as_of_date, l_prev_date
, l_record_type_id, l_record_type_id, l_sg_id_num, l_curr_as_of_date
, l_prev_date, l_prodcat_id,l_resource_id --Opp
, l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id
, l_prev_date, l_bitand_id,l_bitand_id, l_period_type, l_sg_id_num
, l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date
, l_prev_date, l_curr_page_time_id, l_prev_page_time_id, l_prodcat_id,l_resource_id --Frcst
, l_curr_as_of_date, l_prev_date
, l_curr_as_of_date, l_prev_date
, l_sg_id_num, l_curr_as_of_date
, l_prev_date, l_record_type_id,l_record_type_id,l_prodcat_id,l_resource_id;
x_custom_sql := ' SELECT * FROM ('|| l_outer_select ||' FROM
(SELECT VIEWBY,VIEWBYID,SORTORDER,SUM(BIL_MEASURE2) BIL_MEASURE2,
SUM(BIL_MEASURE3) BIL_MEASURE3
,SUM(BIL_MEASURE5) BIL_MEASURE5,SUM(BIL_MEASURE6) BIL_MEASURE6
,SUM(BIL_MEASURE8) BIL_MEASURE8,SUM(BIL_MEASURE9) BIL_MEASURE9
,SUM(BIL_MEASURE14) BIL_MEASURE14,SUM(BIL_MEASURE15) BIL_MEASURE15, BIL_URL1, BIL_URL2 FROM BIL_BI_RPT_TMP1
GROUP BY VIEWBY, VIEWBYID, SORTORDER, BIL_URL1, BIL_URL2) ORDER BY SORTORDER,UPPER(VIEWBY))'|| l_null_rem_clause;
l_inner_select := l_inner_select ||
',DECODE(VIEWBY,'''||l_cat_assign||''',NULL,'''||l_drill_link||''') BIL_URL1 '||
',BIL_URL2 FROM ';
x_custom_sql := l_inner_select ||
' (select decode(opty.viewbyid, -1,:l_unassigned_value,
mtl.DESCRIPTION || '' ('' || mtl.CATEGORY_CONCAT_SEGS ||'')'') VIEWBY, VIEWBYID, SORTORDER,BIL_MEASURE2,
BIL_MEASURE3,
BIL_MEASURE5,
BIL_MEASURE6,
BIL_MEASURE8,
BIL_MEASURE9,
BIL_MEASURE14,
BIL_MEASURE15,
NULL BIL_URL1,
DECODE(opty.viewbyid,''-1'',NULL,'''||l_cat_url||''') BIL_URL2 '||
' from (
SELECT /*+ leading (cal) */
pcd.parent_id VIEWBYID
,1 SORTORDER
,'||l_sql_stmnt1||
l_from1 ||' '||l_opty_denorm||' '|| l_where_clause4 ||
' '||l_product_where_clause||'
GROUP BY pcd.parent_id
) opty,
mtl_categories_v mtl '||
' WHERE mtl.category_id (+) = opty.viewbyid';
SELECT /*+ leading (cal) */ pcd.value VIEWBY
,pcd.id VIEWBYID
,1 SORTORDER
,'||l_sql_stmnt4||
',NULL BIL_URL1
,DECODE(pcd.id, ''-1'',NULL, '''||l_cat_url||''') BIL_URL2 '||
l_from2 ||' '|| l_denorm ||' '|| l_where_clause5 ||' '|| l_product_where_clause1
||' GROUP BY pcd.value, pcd.id';
SELECT /*+ leading (cal) */ pcd.value VIEWBY
,pcd.id VIEWBYID
,1 SORTORDER
,'||l_sql_stmnt3||
',NULL BIL_URL1
,DECODE(pcd.id, ''-1'',NULL, '''||l_cat_url||''') BIL_URL2
FROM '||
l_from3 ||' '|| l_denorm ||' '|| l_where_clause6 ||' '|| l_product_where_clause2
||' GROUP BY pcd.value, pcd.id';
EXECUTE IMMEDIATE l_insert_stmnt||x_custom_sql
USING l_unassigned_value, l_curr_as_of_date,l_prev_date
,l_record_type_id, l_record_type_id
,l_sg_id_num,l_resource_id,l_sg_id_num,l_curr_as_of_date
,l_prev_date,'Y' --Opp
,l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id
,l_prev_date,l_bitand_id, l_bitand_id,l_period_type
,l_sg_id_num,l_resource_id,l_sg_id_num
,l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date
,l_prev_date, l_curr_page_time_id, l_prev_page_time_id --Forecast
,l_curr_as_of_date, l_prev_date
,l_curr_as_of_date, l_prev_date
,l_sg_id_num,l_resource_id,l_sg_id_num,l_curr_as_of_date
,l_prev_date,l_record_type_id,l_record_type_id;
EXECUTE IMMEDIATE l_insert_stmnt||x_custom_sql
USING l_unassigned_value, l_curr_as_of_date,l_prev_date
,l_record_type_id, l_record_type_id
,l_sg_id_num,l_parent_sg_id_num,l_curr_as_of_date
,l_prev_date,'Y' --Opp
,l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id
,l_prev_date, l_bitand_id, l_bitand_id,l_period_type
,l_sg_id_num,l_parent_sg_id_num ,l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date
,l_prev_date, l_curr_page_time_id, l_prev_page_time_id --Forecast
,l_curr_as_of_date, l_prev_date
,l_curr_as_of_date, l_prev_date
,l_sg_id_num,l_parent_sg_id_num,l_curr_as_of_date
,l_prev_date,l_record_type_id,l_record_type_id;
EXECUTE IMMEDIATE l_insert_stmnt||x_custom_sql
USING l_unassigned_value, l_curr_as_of_date,l_prev_date
,l_record_type_id, l_record_type_id
,l_sg_id_num,l_curr_as_of_date
,l_prev_date,'Y' --Opp
,l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id
,l_prev_date, l_bitand_id, l_bitand_id,l_period_type
,l_sg_id_num,l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date
,l_prev_date, l_curr_page_time_id, l_prev_page_time_id --Forecast
,l_curr_as_of_date, l_prev_date
,l_curr_as_of_date, l_prev_date
,l_sg_id_num,l_curr_as_of_date
,l_prev_date,l_record_type_id,l_record_type_id;
x_custom_sql := ' SELECT * FROM ('|| l_outer_select ||' FROM
(SELECT VIEWBY,VIEWBYID,SORTORDER,SUM(BIL_MEASURE2) BIL_MEASURE2,
SUM(BIL_MEASURE3) BIL_MEASURE3
,SUM(BIL_MEASURE5) BIL_MEASURE5,SUM(BIL_MEASURE6) BIL_MEASURE6
,SUM(BIL_MEASURE8) BIL_MEASURE8,SUM(BIL_MEASURE9) BIL_MEASURE9
,SUM(BIL_MEASURE14) BIL_MEASURE14,SUM(BIL_MEASURE15) BIL_MEASURE15, BIL_URL1, BIL_URL2 FROM BIL_BI_RPT_TMP1
GROUP BY VIEWBY, VIEWBYID, SORTORDER, BIL_URL1, BIL_URL2) ORDER BY SORTORDER,UPPER(VIEWBY))'|| l_null_rem_clause;
ELSE -- prodcat selected
-- l_cat_assign := bil_bi_util_pkg.getLookupMeaning(p_lookuptype => 'BIL_BI_LOOKUPS'
-- ,p_lookupcode => 'ASSIGN_CATEG');
x_custom_sql := l_inner_select;
' (SELECT /*+ leading (cal) */ decode(pcd.parent_id,pcd.child_id,'||
' decode(sumry.assign_to_cat,0,pcd.value,:l_cat_assign), '||
' pcd.value) VIEWBY '||
',pcd.id VIEWBYID'||
', decode(pcd.parent_id,pcd.id, 1, 2) sortorder,'
||l_sql_stmnt4||
' , NULL BIL_URL1
,DECODE(pcd.parent_id, pcd.child_id, NULL, '''||l_cat_url||''') BIL_URL2 '||
l_from2 ||' '|| l_denorm || l_where_clause5 || l_product_where_clause1||
' GROUP BY decode(pcd.parent_id,pcd.child_id,'||
' decode(sumry.assign_to_cat,0,pcd.value,:l_cat_assign), '||
' pcd.value),pcd.id,decode(pcd.parent_id,pcd.id, 1, 2),
DECODE(pcd.parent_id, pcd.child_id, NULL, '''||l_cat_url||''') ';
EXECUTE IMMEDIATE l_insert_stmnt||x_custom_sql
USING l_cat_assign,l_curr_page_time_id, l_curr_as_of_date
, l_prev_page_time_id, l_prev_date
, l_bitand_id, l_bitand_id, l_period_type
, l_sg_id_num,l_resource_id,l_sg_id_num,l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date
, l_prev_date, l_curr_page_time_id, l_prev_page_time_id
, l_prodcat_id,l_cat_assign --Frcst
;
EXECUTE IMMEDIATE l_insert_stmnt||x_custom_sql
USING l_cat_assign,l_curr_page_time_id, l_curr_as_of_date
, l_prev_page_time_id, l_prev_date
, l_bitand_id, l_bitand_id, l_period_type
, l_sg_id_num,l_parent_sg_id_num, l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date
, l_prev_date, l_curr_page_time_id, l_prev_page_time_id
, l_prodcat_id,l_cat_assign --Frcst
;
EXECUTE IMMEDIATE l_insert_stmnt||x_custom_sql
USING l_cat_assign,l_curr_page_time_id, l_curr_as_of_date
, l_prev_page_time_id, l_prev_date
, l_bitand_id, l_bitand_id, l_period_type
,l_sg_id_num,l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date
, l_prev_date, l_curr_page_time_id, l_prev_page_time_id
, l_prodcat_id,l_cat_assign --Frcst
;
x_custom_sql := l_inner_select;
x_custom_sql := x_custom_sql ||' (SELECT /*+ leading (cal) */
DECODE(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'', :l_cat_assign, pcd.value), pcd.value) VIEWBY
,pcd.id VIEWBYID
,DECODE(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'', 1, 2), 2) SORTORDER '||
','||l_sql_stmnt1||
',NULL BIL_URL1
,DECODE(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL2 '||
l_from1 ||' '|| l_opty_denorm ||' '|| l_where_clause4 ||' '|| l_product_where_clause||
' GROUP BY DECODE(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'', :l_cat_assign, pcd.value), pcd.value),pcd.id,
DECODE(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'', 1, 2), 2),
DECODE(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''')';
SELECT pcd.value VIEWBY
,pcd.id VIEWBYID
,2 SORTORDER,'||l_sql_stmnt3||
',NULL BIL_URL1
,DECODE(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL2
FROM ENI_ITEM_PROD_CAT_LOOKUP_V pcd, ' || l_from3 ||' '|| l_where_clause6 ||' '|| l_product_where_clause2
||' GROUP BY 2,
pcd.value,pcd.id,DECODE(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') ';
EXECUTE IMMEDIATE l_insert_stmnt||x_custom_sql
USING l_cat_assign,l_curr_as_of_date, l_prev_date
, l_record_type_id, l_record_type_id
, l_sg_id_num,l_resource_id,l_sg_id_num, l_curr_as_of_date
, l_prev_date,l_prodcat_id,l_cat_assign --Oppty
, l_curr_as_of_date, l_prev_date
, l_curr_as_of_date, l_prev_date
, l_sg_id_num,l_resource_id,l_sg_id_num
,l_curr_as_of_date
, l_prev_date, l_record_type_id, l_record_type_id,l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt||x_custom_sql
USING l_cat_assign, l_curr_as_of_date, l_prev_date
, l_record_type_id, l_record_type_id
, l_sg_id_num,l_parent_sg_id_num, l_curr_as_of_date
, l_prev_date,l_prodcat_id,l_cat_assign --Oppty
, l_curr_as_of_date, l_prev_date
, l_curr_as_of_date, l_prev_date
, l_sg_id_num,l_parent_sg_id_num
, l_curr_as_of_date
, l_prev_date, l_record_type_id, l_record_type_id,l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt||x_custom_sql
USING l_cat_assign, l_curr_as_of_date, l_prev_date
, l_record_type_id, l_record_type_id
, l_sg_id_num,l_curr_as_of_date
, l_prev_date,l_prodcat_id,l_cat_assign --Oppty
, l_curr_as_of_date, l_prev_date
, l_curr_as_of_date, l_prev_date
, l_sg_id_num,l_curr_as_of_date
, l_prev_date, l_record_type_id, l_record_type_id,l_prodcat_id;
x_custom_sql := 'SELECT * FROM ('|| l_outer_select ||
' FROM ('||
' SELECT VIEWBY, VIEWBYID, sortorder,BIL_MEASURE2,
BIL_MEASURE3, BIL_MEASURE5,BIL_MEASURE6,
BIL_MEASURE8, BIL_MEASURE9, '||
'BIL_MEASURE14,BIL_MEASURE15, BIL_URL1, BIL_URL2 '||
' FROM BIL_BI_RPT_TMP1 '||
' WHERE SORTORDER = 1 '||
' UNION ALL '||
' SELECT VIEWBY, VIEWBYID, ''2'' SORTORDER, BIL_MEASURE2,
BIL_MEASURE3, BIL_MEASURE5,BIL_MEASURE6,
BIL_MEASURE8, BIL_MEASURE9, '||
'BIL_MEASURE14,BIL_MEASURE15, '||
'DECODE(viewby,'''||l_cat_assign||''',NULL,'''||l_drill_link||''') BIL_URL1, ' ||
' NULL BIL_URL2 '||
' FROM ('||
' SELECT SUM(RN) RN, MAX(VIEWBY) VIEWBY, MAX(VIEWBYID) VIEWBYID, '||
' SUM(BIL_MEASURE2) BIL_MEASURE2, SUM(BIL_MEASURE3) BIL_MEASURE3, '||
' SUM(BIL_MEASURE5) BIL_MEASURE5, SUM(BIL_MEASURE6) BIL_MEASURE6, '||
' SUM(BIL_MEASURE8) BIL_MEASURE8, SUM(BIL_MEASURE9) BIL_MEASURE9, '||
' SUM(BIL_MEASURE14) BIL_MEASURE14, SUM(BIL_MEASURE15) BIL_MEASURE15 '||
' FROM ('||
' SELECT ROWNUM RN, VIEWBY, VIEWBYID, TRUNC(BIL_MEASURE2,3) BIL_MEASURE2,'||
' TRUNC(BIL_MEASURE3,3) BIL_MEASURE3, BIL_MEASURE5, BIL_MEASURE6,TRUNC(BIL_MEASURE8,3) BIL_MEASURE8, '||
' TRUNC(BIL_MEASURE9,3) BIL_MEASURE9, TRUNC(BIL_MEASURE14,3) BIL_MEASURE14, TRUNC(BIL_MEASURE15,3) BIL_MEASURE15 '||
' FROM BIL_BI_RPT_TMP1 '||
' WHERE SORTORDER <> 1 '||
' UNION ALL '||
' SELECT -ROWNUM RN, NULL VIEWBY, VIEWBYID, NULL BIL_MEASURE2,'||
' NULL BIL_MEASURE3, NULL BIL_MEASURE5, NULL BIL_MEASURE6,-TRUNC(BIL_MEASURE8,3) BIL_MEASURE8, '||
' -TRUNC(BIL_MEASURE9,3) BIL_MEASURE9, -TRUNC(BIL_MEASURE14,3) BIL_MEASURE14, -TRUNC(BIL_MEASURE15,3) BIL_MEASURE15 '||
' FROM BIL_BI_RPT_TMP1 '||
' WHERE SORTORDER = 1 '||
' ) '||
' ) '||
' WHERE NOT(RN = 0 AND BIL_MEASURE2 = 0 AND BIL_MEASURE3 = 0 '||
' AND BIL_MEASURE5 = 0 AND BIL_MEASURE6 = 0 AND BIL_MEASURE8 = 0'||
' AND BIL_MEASURE9 = 0 AND BIL_MEASURE14 = 0 AND BIL_MEASURE15 = 0 ) '||
' ) ORDER BY SORTORDER, UPPER(VIEWBY)'||
') '|| l_null_rem_clause;
x_custom_sql := ' SELECT * FROM ('|| l_outer_select ||' FROM
(SELECT VIEWBY,VIEWBYID,SORTORDER,SUM(BIL_MEASURE2) BIL_MEASURE2,
SUM(BIL_MEASURE3) BIL_MEASURE3
,SUM(BIL_MEASURE5) BIL_MEASURE5,SUM(BIL_MEASURE6) BIL_MEASURE6
,SUM(BIL_MEASURE8) BIL_MEASURE8,SUM(BIL_MEASURE9) BIL_MEASURE9
,SUM(BIL_MEASURE14) BIL_MEASURE14,SUM(BIL_MEASURE15) BIL_MEASURE15, BIL_URL1, BIL_URL2 FROM BIL_BI_RPT_TMP1
GROUP BY VIEWBY, VIEWBYID, SORTORDER, BIL_URL1, BIL_URL2) ORDER BY SORTORDER,UPPER(VIEWBY))'|| l_null_rem_clause;
END IF; -- End category selected check
l_conv_rate_selected VARCHAR2(50);
l_outer_select VARCHAR2(8000);
l_inner_select VARCHAR2(8000);
l_inner_select1 VARCHAR2(8000);
l_inner_select2 VARCHAR2(8000);
l_inner_select3 VARCHAR2(8000);
l_insert_stmnt VARCHAR2(8000);
l_select VARCHAR2(4000);
l_pipe_select1 varchar2(4000);
l_pipe_select2 varchar2(4000);
l_pipe_select3 varchar2(4000);
,x_conv_rate_selected => l_conv_rate_selected
,x_sg_id => l_sg_id
,x_parent_sg_id => l_parent_sales_group_id
,x_resource_id => l_resource_id
,x_prodcat_id => l_prodcat_id
,x_curr_page_time_id => l_curr_page_time_id
,x_prev_page_time_id => l_prev_page_time_id
,x_comp_type => l_comp_type
,x_parameter_valid => l_parameter_valid
,x_as_of_date => l_curr_as_of_date
,x_page_period_type => l_page_period_type
,x_prior_as_of_date => l_prev_date
,x_record_type_id => l_record_type_id
,x_viewby => l_viewby );
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '_s';
'l_conv_rate_selected => '|| l_conv_rate_selected ||', ' ||
'l_bitand_id => '|| l_bitand_id ||', ' ||
'l_period_type => '|| l_period_type ||', ' ||
'l_sg_id => '|| l_sg_id ||', ' ||
'l_resource_id => '|| l_resource_id ||', ' ||
'l_bis_sysdate => '|| l_bis_sysdate ||', ' ||
'l_record_type_id => '|| l_record_type_id ||', ' ||
'l_calendar_id => '|| l_calendar_id;
l_outer_select:= 'SELECT VIEWBY
,VIEWBYID
, BIL_MEASURE2
, BIL_MEASURE3
,(((BIL_MEASURE2 - BIL_MEASURE3) / ABS(DECODE(BIL_MEASURE3, 0, NULL, BIL_MEASURE3))) * 100) BIL_MEASURE4
,BIL_MEASURE6
,BIL_MEASURE7
,(((BIL_MEASURE6 - BIL_MEASURE7) / ABS(DECODE(BIL_MEASURE7, 0, NULL, BIL_MEASURE7))) * 100) BIL_MEASURE8
,BIL_MEASURE10
,BIL_MEASURE11
,(((BIL_MEASURE10 - BIL_MEASURE11) / ABS(DECODE(BIL_MEASURE11, 0, NULL, BIL_MEASURE11))) * 100) BIL_MEASURE12
,BIL_MEASURE14
,BIL_MEASURE15
,(((BIL_MEASURE14 - BIL_MEASURE15) / ABS(DECODE(BIL_MEASURE15, 0, NULL, BIL_MEASURE15))) * 100) BIL_MEASURE16
,BIL_MEASURE18
,BIL_MEASURE19
,(((BIL_MEASURE18 - BIL_MEASURE19) / ABS(DECODE(BIL_MEASURE19, 0, NULL, BIL_MEASURE19))) * 100) BIL_MEASURE20
,BIL_MEASURE22
,BIL_MEASURE23
,(((BIL_MEASURE22 - BIL_MEASURE23) / ABS(DECODE(BIL_MEASURE23, 0, NULL, BIL_MEASURE23))) * 100) BIL_MEASURE24
,(((BIL_MEASURE6) / DECODE(BIL_MEASURE2, 0, NULL, BIL_MEASURE2)) * 100) BIL_MEASURE25
,(((BIL_MEASURE7) / DECODE(BIL_MEASURE3, 0, NULL, BIL_MEASURE3)) * 100) BIL_MEASURE74
,(((BIL_MEASURE10) / DECODE(BIL_MEASURE2, 0, NULL, BIL_MEASURE2)) * 100) BIL_MEASURE26
,(((BIL_MEASURE14) / DECODE(BIL_MEASURE2, 0, NULL, BIL_MEASURE2)) * 100) BIL_MEASURE27
,(BIL_MEASURE6 / DECODE(BIL_MEASURE10, 0, NULL, BIL_MEASURE10)) BIL_MEASURE28
,(BIL_MEASURE7 / DECODE(BIL_MEASURE11, 0, NULL, BIL_MEASURE11)) BIL_MEASURE34
,(SUM(BIL_MEASURE2) OVER()) BIL_MEASURE36
,(SUM(BIL_MEASURE3) OVER()) BIL_MEASURE37
,(((( SUM(BIL_MEASURE2) OVER() ) - ( SUM(BIL_MEASURE3) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE3) OVER(), 0, NULL, SUM(BIL_MEASURE3)OVER()))) * 100) BIL_MEASURE38
,SUM(BIL_MEASURE6) OVER() BIL_MEASURE40
,SUM(BIL_MEASURE7) OVER() BIL_MEASURE41
,(((( SUM(BIL_MEASURE6) OVER() ) - ( SUM(BIL_MEASURE7) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE7) OVER(), 0, NULL, SUM(BIL_MEASURE7) OVER()) )) * 100) BIL_MEASURE42
,(SUM(BIL_MEASURE10) OVER()) BIL_MEASURE44
,(SUM(BIL_MEASURE11) OVER()) BIL_MEASURE45
,(((( SUM(BIL_MEASURE10) OVER() ) - ( SUM(BIL_MEASURE11) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE11) OVER(), 0, NULL, SUM(BIL_MEASURE11) OVER()) )) * 100) BIL_MEASURE46
,(SUM(BIL_MEASURE14) OVER()) BIL_MEASURE48
,(SUM(BIL_MEASURE15) OVER()) BIL_MEASURE49
,(((( SUM(BIL_MEASURE14) OVER() ) - ( SUM(BIL_MEASURE15) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE15) OVER(), 0, NULL, SUM(BIL_MEASURE15) OVER()) )) * 100) BIL_MEASURE50
,(SUM(BIL_MEASURE18) OVER()) BIL_MEASURE52
,(SUM(BIL_MEASURE19) OVER()) BIL_MEASURE53
,(((( SUM(BIL_MEASURE18) OVER() ) - ( SUM(BIL_MEASURE19) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE19) OVER(), 0, NULL, SUM(BIL_MEASURE19) OVER()) )) * 100) BIL_MEASURE54
,(SUM(BIL_MEASURE22) OVER()) BIL_MEASURE56
,(SUM(BIL_MEASURE23) OVER()) BIL_MEASURE57
,(((( SUM(BIL_MEASURE22) OVER() ) - ( SUM(BIL_MEASURE23) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE23) OVER(), 0, NULL, SUM(BIL_MEASURE23) OVER()) )) * 100) BIL_MEASURE58
,( SUM(BIL_MEASURE6) OVER() / (DECODE(SUM(BIL_MEASURE2) OVER(), 0, NULL, SUM(BIL_MEASURE2) OVER())) * 100 ) BIL_MEASURE59
,( SUM(BIL_MEASURE7) OVER() / (DECODE(SUM(BIL_MEASURE3) OVER(), 0, NULL, SUM(BIL_MEASURE3) OVER())) * 100 )
BIL_MEASURE76
,( SUM(BIL_MEASURE10) OVER() / (DECODE(SUM(BIL_MEASURE2) OVER(), 0, NULL, SUM(BIL_MEASURE2) OVER())) * 100 ) BIL_MEASURE60
,(SUM(BIL_MEASURE14) OVER() / (DECODE(SUM(BIL_MEASURE2) OVER(), 0, NULL, SUM(BIL_MEASURE2) OVER())) *100 ) BIL_MEASURE61
,( SUM(BIL_MEASURE6) OVER() / (DECODE(SUM(BIL_MEASURE10) OVER(), 0, NULL, SUM(BIL_MEASURE10) OVER()) ) ) BIL_MEASURE62
,( SUM(BIL_MEASURE7) OVER() / (DECODE(SUM(BIL_MEASURE11) OVER(), 0, NULL, SUM(BIL_MEASURE11) OVER()) ) ) BIL_MEASURE68 '||
' ,BIL_URL1
, BIL_URL2
,DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'',
DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=WON'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=WON'''||'))
BIL_URL3
,DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'',
DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=LOST'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=LOST'''||'))
BIL_URL4
,DECODE('''||l_curr_as_of_date||''',TRUNC(SYSDATE),
DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'',
DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=OPEN'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=OPEN'''||')),
NULL) BIL_URL5
,DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'',
DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=NO OPPORTUNITY'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=NO OPPORTUNITY'''||') )
BIL_URL6 ';
l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1 (VIEWBY, VIEWBYID, SORTORDER, BIL_MEASURE2,'||
'BIL_MEASURE3, '||
'BIL_MEASURE6,BIL_MEASURE7, BIL_MEASURE10, BIL_MEASURE11, '||
'BIL_MEASURE14,BIL_MEASURE15,BIL_MEASURE18,BIL_MEASURE19, '||
'BIL_MEASURE22,BIL_MEASURE23,BIL_URL1, BIL_URL2)';
l_inner_select:=' select VIEWBY '||
' ,VIEWBYID '||
' ,SORTORDER '||
' ,(CASE '||
' WHEN NOT(SUM(BIL_MEASURE2) IS NULL AND SUM(BIL_MEASURE14) IS NULL) '||
' THEN '||
' NVL(SUM(BIL_MEASURE2),0) + NVL(SUM(BIL_MEASURE14),0) '||
' ELSE NULL '||
' END) BIL_MEASURE2 '||
' ,(CASE '||
' WHEN NOT(SUM(BIL_MEASURE3) IS NULL AND SUM(BIL_MEASURE15) IS NULL) '||
' THEN '||
' NVL(SUM(BIL_MEASURE3),0) + NVL(SUM(BIL_MEASURE15),0) '||
' ELSE NULL '||
' END) BIL_MEASURE3 '||
' ,SUM(BIL_MEASURE6) BIL_MEASURE6 '||
' ,SUM(BIL_MEASURE7) BIL_MEASURE7 '||
' ,SUM(BIL_MEASURE10) BIL_MEASURE10 '||
' ,SUM(BIL_MEASURE11) BIL_MEASURE11 '||
' ,SUM(BIL_MEASURE14) BIL_MEASURE14 '||
' ,SUM(BIL_MEASURE15) BIL_MEASURE15 '||
' ,SUM(BIL_MEASURE18) BIL_MEASURE18 '||
' ,SUM(BIL_MEASURE19) BIL_MEASURE19 '||
' ,SUM(BIL_MEASURE22) BIL_MEASURE22 '||
' ,SUM(BIL_MEASURE23) BIL_MEASURE23 '||
' ,BIL_URL1 '||
' ,BIL_URL2 '||
' from ';
l_inner_select1:=
' (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN '||
' (CASE '||
' WHEN NOT(sumry.won_opty_amt'||l_currency_suffix||' IS NULL
AND sumry.lost_opty_amt'||l_currency_suffix||' IS NULL AND '||
' sumry.no_opty_amt'||l_currency_suffix||' IS NULL) '||
' THEN '||
' (NVL(sumry.won_opty_amt'||l_currency_suffix||',0) + '||
' NVL(sumry.lost_opty_amt'||l_currency_suffix||',0) + '||
' NVL(sumry.no_opty_amt'||l_currency_suffix||',0) '||
' ) '||
' ELSE NULL '||
' END) '||
' ELSE NULL '||
' END) BIL_MEASURE2 '||
' , (CASE WHEN cal.report_date =:l_prev_date '||
' THEN '||
' (CASE '||
' WHEN NOT(sumry.won_opty_amt'||l_currency_suffix||' IS NULL
AND sumry.lost_opty_amt'||l_currency_suffix||' IS NULL '||
' AND sumry.no_opty_amt'||l_currency_suffix||' IS NULL) '||
' THEN '||
' (NVL(sumry.won_opty_amt'||l_currency_suffix||',0) + '||
' NVL(sumry.lost_opty_amt'||l_currency_suffix||',0) + '||
' NVL(sumry.no_opty_amt'||l_currency_suffix||',0) '||
' ) '||
' ELSE NULL '||
' END) '||
' ELSE NULL '||
' END) BIL_MEASURE3 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.won_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE6 '||
' , (CASE WHEN cal.report_date =:l_prev_date '||
' THEN sumry.won_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE7 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.lost_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE10 '||
' , (CASE WHEN cal.report_date =:l_prev_date '||
' THEN sumry.lost_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE11 '||
' , NULL BIL_MEASURE14 '||
' , NULL BIL_MEASURE15 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.new_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE18 '||
' , (CASE WHEN cal.report_date =:l_prev_date '||
' THEN sumry.new_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE19 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.no_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE22 '||
' , (CASE WHEN cal.report_date =:l_prev_date '||
' THEN sumry.no_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE23';
l_inner_select3:= ' NULL BIL_MEASURE2 '||
' ,NULL BIL_MEASURE3 '||
' ,NULL BIL_MEASURE6 '||
' ,NULL BIL_MEASURE7 '||
' ,NULL BIL_MEASURE10 '||
' ,NULL BIL_MEASURE11 '||
' ,(CASE WHEN sumry.snap_date =:l_snap_date THEN '||
' decode(:l_period_type, '||
' 128,open_amt_year'||l_currency_suffix||', '||
' 64,open_amt_quarter'||l_currency_suffix||', '||
' 32,open_amt_period'||l_currency_suffix||', '||
' 16,open_amt_week'||l_currency_suffix||' '||
' ) '||
' ELSE NULL '||
' END) BIL_MEASURE14 '||
' ,(CASE WHEN sumry.snap_date =:l_prev_date THEN '||
' decode(:l_period_type, '||
' 128,open_amt_year'||l_currency_suffix||', '||
' 64,open_amt_quarter'||l_currency_suffix||', '||
' 32,open_amt_period'||l_currency_suffix||', '||
' 16,open_amt_week'||l_currency_suffix||' '||
' ) '||
' ELSE NULL '||
' END) BIL_MEASURE15 '||
' ,NULL BIL_MEASURE18 '||
' ,NULL BIL_MEASURE19 '||
' ,NULL BIL_MEASURE22 '||
' ,NULL BIL_MEASURE23';
l_pipe_select1 := ' NULL BIL_MEASURE2 '||
' ,NULL BIL_MEASURE3 '||
' ,NULL BIL_MEASURE6 '||
' ,NULL BIL_MEASURE7 '||
' ,NULL BIL_MEASURE10 '||
' ,NULL BIL_MEASURE11 '||
' ,(CASE WHEN sumry.snap_date =:l_snapshot_date THEN '||
' decode(:l_period_type, '||
' 128,open_amt_year'||l_currency_suffix||', '||
' 64,open_amt_quarter'||l_currency_suffix||', '||
' 32,open_amt_period'||l_currency_suffix||', '||
' 16,open_amt_week'||l_currency_suffix||' '||
' ) '||
' ELSE NULL '||
' END) BIL_MEASURE14 ';
l_pipe_select2 := ' ,(CASE WHEN sumry.snap_date =:l_prev_snap_date THEN '||
' decode(:l_period_type, '||
' 128,open_amt_year'||l_currency_suffix||', '||
' 64,open_amt_quarter'||l_currency_suffix||', '||
' 32,open_amt_period'||l_currency_suffix||', '||
' 16,open_amt_week'||l_currency_suffix||' '||
' ) '||
' ELSE NULL '||
' END) BIL_MEASURE15 ';
l_pipe_select2 := ' ,(CASE WHEN sumry.snap_date =:l_snapshot_date THEN '||
''||l_prev_amt||' '||
' ELSE NULL '||
' END) BIL_MEASURE15 ';
l_pipe_select3 := ' ,NULL BIL_MEASURE18 '||
' ,NULL BIL_MEASURE19 '||
' ,NULL BIL_MEASURE22 '||
' ,NULL BIL_MEASURE23';
l_inner_select3:= l_pipe_select1 || l_pipe_select2 || l_pipe_select3;
l_select := ' SELECT '||
' sumry.sales_group_id sales_group_id '||
' ,sumry.salesrep_id salesrep_id '||
' ,SUM(sumry.BIL_MEASURE2) BIL_MEASURE2 '||
' ,SUM(sumry.BIL_MEASURE3) BIL_MEASURE3 '||
' ,SUM(sumry.BIL_MEASURE6) BIL_MEASURE6 '||
' ,SUM(sumry.BIL_MEASURE7) BIL_MEASURE7 '||
' ,SUM(sumry.BIL_MEASURE10) BIL_MEASURE10 '||
' ,SUM(sumry.BIL_MEASURE11) BIL_MEASURE11 '||
' ,SUM(sumry.BIL_MEASURE14) BIL_MEASURE14 '||
' ,SUM(sumry.BIL_MEASURE15) BIL_MEASURE15 '||
' ,SUM(sumry.BIL_MEASURE18) BIL_MEASURE18 '||
' ,SUM(sumry.BIL_MEASURE19) BIL_MEASURE19 '||
' ,SUM(sumry.BIL_MEASURE22) BIL_MEASURE22 '||
' ,SUM(sumry.BIL_MEASURE23) BIL_MEASURE23 '||
' ,BIL_URL1 '||
' ,BIL_URL2 ';
l_custom_sql :=l_insert_stmnt || l_inner_select ||
' ('||
' SELECT /*+ NO_MERGE(tmp1) */ '||
' DECODE(tmp1.salesrep_id, NULL, grptl.group_name,restl.resource_name) VIEWBY '||
' ,DECODE(tmp1.salesrep_id, NULL, to_char(tmp1.sales_group_id), '||
' tmp1.salesrep_id||''.''||tmp1.sales_group_id) VIEWBYID '||
' ,DECODE(tmp1.salesrep_id, NULL, 1, 2) sortorder '||
' ,SUM(tmp1.BIL_MEASURE2) BIL_MEASURE2 '||
' ,SUM(tmp1.BIL_MEASURE3) BIL_MEASURE3 '||
' ,SUM(tmp1.BIL_MEASURE6) BIL_MEASURE6 '||
' ,SUM(tmp1.BIL_MEASURE7) BIL_MEASURE7 '||
' ,SUM(tmp1.BIL_MEASURE10) BIL_MEASURE10 '||
' ,SUM(tmp1.BIL_MEASURE11) BIL_MEASURE11 '||
' ,SUM(tmp1.BIL_MEASURE14) BIL_MEASURE14 '||
' ,SUM(tmp1.BIL_MEASURE15) BIL_MEASURE15 '||
' ,SUM(tmp1.BIL_MEASURE18) BIL_MEASURE18 '||
' ,SUM(tmp1.BIL_MEASURE19) BIL_MEASURE19 '||
' ,SUM(tmp1.BIL_MEASURE22) BIL_MEASURE22 '||
' ,SUM(tmp1.BIL_MEASURE23) BIL_MEASURE23 '||
' ,DECODE(tmp1.salesrep_id, NULL, BIL_URL1, NULL) BIL_URL1 '||
' ,DECODE(tmp1.salesrep_id, NULL, NULL,'''||l_drill_link||''') BIL_URL2 '||
' FROM ('||
l_select||
' FROM ('||
'SELECT /*+ LEADING(cal) */ '||
l_pc_sel||' '||
l_inner_select1||
',sumry.sales_group_id sales_group_id '||
',sumry.salesrep_id salesrep_id '||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '|| l_from1 ||
' WHERE cal.xtd_flag=:l_yes AND '
||l_inner_where_clause||' ';
'SELECT '||
l_pc_sel||' '||
l_inner_select3||
',sumry.sales_group_id sales_group_id '||
',sumry.salesrep_id salesrep_id '||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '||
l_from2 ||
' WHERE '||l_inner_where_clause2||' ';
l_custom_sql :=l_insert_stmnt || l_inner_select ||
' ('||
' SELECT /*+ NO_MERGE(tmp1) */ '||
' resource_name VIEWBY '||
' ,tmp1.salesrep_id||''.''||tmp1.sales_group_id VIEWBYID '||
' ,tmp1.sortorder sortorder '||
' ,SUM(tmp1.BIL_MEASURE2) BIL_MEASURE2 '||
' ,SUM(tmp1.BIL_MEASURE3) BIL_MEASURE3 '||
' ,SUM(tmp1.BIL_MEASURE6) BIL_MEASURE6 '||
' ,SUM(tmp1.BIL_MEASURE7) BIL_MEASURE7 '||
' ,SUM(tmp1.BIL_MEASURE10) BIL_MEASURE10 '||
' ,SUM(tmp1.BIL_MEASURE11) BIL_MEASURE11 '||
' ,SUM(tmp1.BIL_MEASURE14) BIL_MEASURE14 '||
' ,SUM(tmp1.BIL_MEASURE15) BIL_MEASURE15 '||
' ,SUM(tmp1.BIL_MEASURE18) BIL_MEASURE18 '||
' ,SUM(tmp1.BIL_MEASURE19) BIL_MEASURE19 '||
' ,SUM(tmp1.BIL_MEASURE22) BIL_MEASURE22 '||
' ,SUM(tmp1.BIL_MEASURE23) BIL_MEASURE23 '||
' ,DECODE(tmp1.salesrep_id, NULL, BIL_URL1, NULL) BIL_URL1 '||
' ,'''||l_drill_link||''' BIL_URL2 '||
' FROM ('||
l_select||',sortorder '||
' FROM ('||
'SELECT /*+ LEADING(cal) */ '||
l_pc_sel||
'1 sortorder, '||
l_inner_select1||
',sumry.sales_group_id '||
',sumry.salesrep_id salesrep_id '||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '|| l_from1 ||
' WHERE cal.xtd_flag=:l_yes AND '
||l_inner_where_clause;
'SELECT '||l_pc_sel||' 1 sortorder, '||
l_inner_select3||
',sumry.sales_group_id sales_group_id '||
',sumry.salesrep_id salesrep_id '||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '||
l_from2 ||
' WHERE '||l_inner_where_clause2;
x_custom_sql := ' SELECT * FROM ( '||
l_outer_select||' FROM BIL_BI_RPT_TMP1 '||
' ORDER BY SORTORDER, UPPER(VIEWBY) '||
' ) WHERE NOT('||l_null_rem_where_clause||')';
/* Basically the only case when a parent_id = id (immediate child id) will be if we have selected a
self node (see the new code in the l_product_where). So the first time we show a leaf category (C)
it will be when we select its parent (A),so we show Assigned to Category for the parent (A), plus
that categorys children - category (C). In that case parent_id <> child_id for category C, so we
assign l_cat_url. When we click on it, we re-run the query. Now we get Assigned to category for
category C, and in the second part of the union all we select self (see the new code in
product where clause). So now parent_id=id, and we can assign l_prod_url to switch the view by to
product
*/
IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
MODULE => g_pkg || l_proc || ' Prod cat view by ',
MESSAGE => 'Product where clause '||l_product_where_clause);
l_custom_sql :=l_insert_stmnt || l_inner_select ||
'('||
' SELECT /*+ LEADING(cal) */ null VIEWBY ,'||
'1 sortorder, '||
l_inner_select1||
',sumry.product_category_id VIEWBYID'||
',NULL BIL_URL1'||
',NULL BIL_URL2 '||
l_from1 ||
' WHERE cal.xtd_flag = :l_yes AND '||l_inner_where_clause1||
' AND sumry.salesrep_id IS NULL ';
' SELECT null VIEWBY ,'||
'1 sortorder, '||
l_inner_select3||
',sumry.product_category_id VIEWBYID'||
',NULL BIL_URL1'||
',NULL BIL_URL2 '||
l_from2 ||
' WHERE '||l_inner_where_clause3||
' AND sumry.salesrep_id IS NULL ';
ELSE -- salesrep is selected
l_custom_sql :=l_insert_stmnt || l_inner_select ||
'('||
' SELECT /*+ LEADING(cal) */ null VIEWBY ,'||
'1 sortorder, '||
l_inner_select1||
',sumry.product_category_id VIEWBYID'||
',NULL BIL_URL1'||
',NULL BIL_URL2 '||
l_from1 ||
' WHERE cal.xtd_flag = :l_yes AND '||l_inner_where_clause1||
' AND sumry.salesrep_id = :l_resource_id ';
' SELECT null VIEWBY ,'||
'1 sortorder, '||
l_inner_select3||
',sumry.product_category_id VIEWBYID'||
',NULL BIL_URL1'||
',NULL BIL_URL2 '||
l_from2 ||
' WHERE '||l_inner_where_clause3||
' AND sumry.salesrep_id = :l_resource_id ';
' SELECT * FROM ('||
l_outer_select||' FROM
(SELECT VIEWBY, VIEWBYID,SUM(BIL_MEASURE2) BIL_MEASURE2,
SUM(BIL_MEASURE3) BIL_MEASURE3, SUM(BIL_MEASURE6) BIL_MEASURE6,
SUM(BIL_MEASURE7) BIL_MEASURE7, SUM(BIL_MEASURE10) BIL_MEASURE10,
SUM(BIL_MEASURE11) BIL_MEASURE11, SUM(BIL_MEASURE14) BIL_MEASURE14,
SUM(BIL_MEASURE15) BIL_MEASURE15, SUM(BIL_MEASURE18) BIL_MEASURE18,
SUM(BIL_MEASURE19) BIL_MEASURE19, SUM(BIL_MEASURE22) BIL_MEASURE22,
SUM(BIL_MEASURE23) BIL_MEASURE23, '''||l_drill_link||''' BIL_URL1, BIL_URL2
FROM
(SELECT decode(opty.viewbyid, -1,:l_unassigned_value,
mtl.DESCRIPTION || '' ('' || mtl.CATEGORY_CONCAT_SEGS ||'')'') VIEWBY,
VIEWBYID, product_category_id,
SORTORDER, BIL_MEASURE2,'||
'BIL_MEASURE3, '||
'BIL_MEASURE6,BIL_MEASURE7, BIL_MEASURE10, BIL_MEASURE11, '||
'BIL_MEASURE14,BIL_MEASURE15,BIL_MEASURE18,BIL_MEASURE19, '||
'BIL_MEASURE22,BIL_MEASURE23
,NULL BIL_URL1
,DECODE(opty.viewbyid,''-1'',NULL,'''||l_cat_url||''') BIL_URL2 '||
' FROM
(select pcd.parent_id VIEWBYID, product_category_id,
SORTORDER, BIL_MEASURE2,'||
'BIL_MEASURE3, '||
'BIL_MEASURE6,BIL_MEASURE7, BIL_MEASURE10, BIL_MEASURE11, '||
'BIL_MEASURE14,BIL_MEASURE15,BIL_MEASURE18,BIL_MEASURE19, '||
'BIL_MEASURE22,BIL_MEASURE23
from (select VIEWBYID, VIEWBYID product_category_id,
SORTORDER, BIL_MEASURE2,'||
'BIL_MEASURE3, '||
'BIL_MEASURE6,BIL_MEASURE7, BIL_MEASURE10, BIL_MEASURE11, '||
'BIL_MEASURE14,BIL_MEASURE15,BIL_MEASURE18,BIL_MEASURE19, '||
'BIL_MEASURE22,BIL_MEASURE23 FROM BIL_BI_RPT_TMP1) sumry '||l_denorm||' where
sortorder = 1 '||l_product_where_clause||
') OPTY, mtl_categories_v mtl '||
' WHERE mtl.category_id (+) = opty.viewbyid '
||
') GROUP BY VIEWBY, VIEWBYID, '''||l_drill_link||''', BIL_URL2
) '||
' ) WHERE NOT('||l_null_rem_where_clause||') ORDER BY UPPER(VIEWBY)';
* for the category selected, (which used to be called unassigned) the second part of
* the union all gets the children categories
*/
IF l_resource_id is NULL THEN
l_custom_sql := l_inner_select ||
'('||
' SELECT /*+ LEADING(cal) */ decode(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'', :l_cat_assign, pcd.value), pcd.value) VIEWBY ,'||
' decode(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'',1, 2),2) SORTORDER, '||
l_inner_select1||
',pcd.id VIEWBYID'||
',NULL BIL_URL1'||
',decode(pcd.parent_id, pcd.id, NULL, '||
' '''||l_cat_url||''')'||
' BIL_URL2 '||
l_from1 ||
' WHERE cal.xtd_flag = :l_yes AND '||l_inner_where_clause1||
' AND sumry.salesrep_id IS NULL '||
' '||l_product_where_clause||' ';
' SELECT decode(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'', :l_cat_assign, pcd.value), pcd.value) VIEWBY ,'||
' decode(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'',1, 2),2) SORTORDER, '||
l_inner_select3||
',pcd.id VIEWBYID'||
',NULL BIL_URL1'||
',DECODE(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL2 '||
l_from2 ||
' WHERE '||l_inner_where_clause3||
' AND sumry.salesrep_id IS NULL '||
' '||l_product_where_clause;
l_custom_sql := l_inner_select ||
'('||
' SELECT /*+ LEADING(cal) */ decode(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'', :l_cat_assign, pcd.value), pcd.value) VIEWBY ,'||
' decode(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'',1, 2),2) SORTORDER, '||
l_inner_select1||
',pcd.id VIEWBYID'||
',decode(sumry.item_id, ''-1'', decode(pcd.parent_id, pcd.id,NULL,'''||l_drill_link||'''),'''||l_drill_link||''') BIL_URL1'||
',decode(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''')'||
' BIL_URL2 '||
l_from1 ||
' WHERE cal.xtd_flag = :l_yes AND '||l_inner_where_clause1||
' AND sumry.salesrep_id = :l_resource_id '||
' '||l_product_where_clause||' ';
' SELECT decode(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'', :l_cat_assign, pcd.value), pcd.value) VIEWBY ,'||
' decode(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'',1, 2),2) SORTORDER, '||
l_inner_select3||
',pcd.id VIEWBYID'||
',decode(sumry.item_id, ''-1'', decode(pcd.parent_id, pcd.id,NULL,'''||l_drill_link||'''),'''||l_drill_link||''') BIL_URL1'||
',DECODE(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL2 '||
l_from2 ||
' WHERE '||l_inner_where_clause3||
' AND sumry.salesrep_id = :l_resource_id '||
' '||l_product_where_clause||' ';
EXECUTE IMMEDIATE l_insert_stmnt || l_custom_sql
USING l_cat_assign,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_yes,
l_record_type_id,l_record_type_id,
l_curr_as_of_date, l_prev_date,
l_sg_id_num,l_prodcat_id,
l_cat_assign,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
l_sg_id_num,l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt || l_custom_sql
USING l_cat_assign,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_yes,
l_record_type_id,l_record_type_id,
l_curr_as_of_date, l_prev_date,
l_sg_id_num,l_prodcat_id,
l_cat_assign,
l_snapshot_date,l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date, l_prev_snap_date,
l_sg_id_num,l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt || l_custom_sql
USING l_cat_assign,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_yes,
l_record_type_id,l_record_type_id,
l_curr_as_of_date, l_prev_date,
l_sg_id_num,l_resource_id,
l_prodcat_id,
l_cat_assign,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
l_sg_id_num,l_resource_id,
l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt || l_custom_sql
USING l_cat_assign,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_yes,
l_record_type_id,l_record_type_id,
l_curr_as_of_date, l_prev_date,
l_sg_id_num,l_resource_id,
l_prodcat_id,
l_cat_assign,
l_snapshot_date,l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date, l_prev_snap_date,
l_sg_id_num,l_resource_id,
l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt || l_custom_sql
USING l_cat_assign,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_yes,
l_record_type_id,l_record_type_id,
l_curr_as_of_date, l_prev_date,
l_parent_sales_group_id,l_sg_id_num,l_prodcat_id,
l_cat_assign,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
l_parent_sales_group_id,
l_sg_id_num,l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt || l_custom_sql
USING l_cat_assign,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_yes,
l_record_type_id,l_record_type_id,
l_curr_as_of_date, l_prev_date,
l_parent_sales_group_id,l_sg_id_num,l_prodcat_id,
l_cat_assign,
l_snapshot_date,l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date, l_prev_snap_date,
l_parent_sales_group_id,
l_sg_id_num,l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt || l_custom_sql
USING l_cat_assign,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_yes,
l_record_type_id,l_record_type_id,
l_curr_as_of_date, l_prev_date,
l_sg_id_num,l_sg_id_num,l_resource_id,l_prodcat_id,
l_cat_assign,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
l_sg_id_num,
l_sg_id_num,l_resource_id,l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt || l_custom_sql
USING l_cat_assign,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_curr_as_of_date,l_prev_date,
l_curr_as_of_date,l_prev_date,l_yes,
l_record_type_id,l_record_type_id,
l_curr_as_of_date, l_prev_date,
l_sg_id_num,l_sg_id_num,l_resource_id,l_prodcat_id,
l_cat_assign,
l_snapshot_date,l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date, l_prev_snap_date,
l_sg_id_num,
l_sg_id_num,l_resource_id,l_prodcat_id;
x_custom_sql := ' SELECT * FROM ( '||
l_outer_select||
' FROM ('||
' SELECT VIEWBY, VIEWBYID, SORTORDER, '||
' CASE WHEN NOT(BIL_MEASURE6 IS NULL AND BIL_MEASURE10 IS NULL AND BIL_MEASURE14 IS NULL AND '||
' BIL_MEASURE22 IS NULL) '||
' THEN '||
' (NVL(BIL_MEASURE6,0)+NVL(BIL_MEASURE10,0)+NVL(BIL_MEASURE14,0)+NVL(BIL_MEASURE22,0)) '||
' ELSE NULL '||
' END BIL_MEASURE2,'||
' CASE WHEN NOT(BIL_MEASURE7 IS NULL AND BIL_MEASURE11 IS NULL AND BIL_MEASURE15 IS NULL AND '||
' BIL_MEASURE23 IS NULL) '||
' THEN '||
' (NVL(BIL_MEASURE7,0)+NVL(BIL_MEASURE11,0)+NVL(BIL_MEASURE15,0)+NVL(BIL_MEASURE23,0)) '||
' ELSE NULL '||
' END BIL_MEASURE3, '||
' BIL_MEASURE6,BIL_MEASURE7, '||
' BIL_MEASURE10, BIL_MEASURE11, '||
' BIL_MEASURE14, BIL_MEASURE15, '||
' BIL_MEASURE18,BIL_MEASURE19, BIL_MEASURE22, '||
' BIL_MEASURE23,BIL_URL1, BIL_URL2 '||
' FROM ('||
' SELECT VIEWBY, VIEWBYID, SORTORDER, BIL_MEASURE2,'||
' BIL_MEASURE3, BIL_MEASURE6,BIL_MEASURE7, '||
' BIL_MEASURE10, BIL_MEASURE11, '||
' BIL_MEASURE14, BIL_MEASURE15, '||
' BIL_MEASURE18,BIL_MEASURE19, BIL_MEASURE22, '||
' BIL_MEASURE23,BIL_URL1, BIL_URL2 '||
' FROM BIL_BI_RPT_TMP1 '||
' WHERE SORTORDER = 1 '||
' UNION ALL
SELECT VIEWBY, VIEWBYID, SORTORDER, BIL_MEASURE2,'||
' BIL_MEASURE3, BIL_MEASURE6,BIL_MEASURE7, '||
' BIL_MEASURE10, BIL_MEASURE11, '||
' BIL_MEASURE14, BIL_MEASURE15, '||
' BIL_MEASURE18,BIL_MEASURE19, BIL_MEASURE22, '||
' BIL_MEASURE23,BIL_URL1, NULL BIL_URL2 '||
' FROM BIL_BI_RPT_TMP1 '||
' WHERE SORTORDER = 2 '||
' ) '||
')'||' ORDER BY SORTORDER, UPPER(VIEWBY) '||
' ) WHERE NOT('||l_null_rem_where_clause||')';
x_custom_sql := ' SELECT * FROM ( '||
l_outer_select||' FROM BIL_BI_RPT_TMP1 '||
' ORDER BY SORTORDER, UPPER(VIEWBY) '||
') WHERE NOT('||l_null_rem_where_clause||')';
l_conv_rate_selected VARCHAR2(50);
l_outer_select VARCHAR2(8000);
l_inner_select VARCHAR2(8000);
l_inner_select1 VARCHAR2(8000);
l_inner_select2 VARCHAR2(8000);
l_inner_select3 VARCHAR2(8000);
l_inner_select4 VARCHAR2(8000);
l_insert_stmnt VARCHAR2(8000);
l_pipe_select1 varchar2(4000);
l_pipe_select2 varchar2(4000);
l_pipe_select3 varchar2(4000);
,x_conv_rate_selected => l_conv_rate_selected
,x_sg_id => l_sg_id
,x_parent_sg_id => l_parent_sales_group_id
,x_resource_id => l_resource_id
,x_prodcat_id => l_prodcat_id
,x_curr_page_time_id => l_curr_page_time_id
,x_prev_page_time_id => l_prev_page_time_id
,x_comp_type => l_comp_type
,x_parameter_valid => l_parameter_valid
,x_as_of_date => l_curr_as_of_date
,x_page_period_type => l_page_period_type
,x_prior_as_of_date => l_prev_date
,x_record_type_id => l_record_type_id
,x_viewby => l_viewby );
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '_s';
l_conv_rate_selected := TO_NUMBER(REPLACE(l_conv_rate_selected, ''''));
'l_conv_rate_selected => '|| l_conv_rate_selected ||', ' ||
'l_bitand_id => '|| l_bitand_id ||', ' ||
'l_period_type => '|| l_period_type ||', ' ||
'l_sg_id => '|| l_sg_id ||', ' ||
'l_resource_id => '|| l_resource_id ||', ' ||
'l_bis_sysdate => '|| l_bis_sysdate ||', ' ||
'l_record_type_id => '|| l_record_type_id ||', ' ||
'l_calendar_id => '|| l_calendar_id;
l_outer_select:= 'SELECT VIEWBY
,VIEWBYID
, BIL_MEASURE2
, BIL_MEASURE3
,(((BIL_MEASURE2 - BIL_MEASURE3) / ABS(DECODE(BIL_MEASURE3, 0, NULL, BIL_MEASURE3))) * 100) BIL_MEASURE4
,BIL_MEASURE5
,BIL_MEASURE6
,BIL_MEASURE7
,(((BIL_MEASURE6 - BIL_MEASURE7) / ABS(DECODE(BIL_MEASURE7, 0, NULL, BIL_MEASURE7))) * 100) BIL_MEASURE8
,BIL_MEASURE9
,BIL_MEASURE10
,BIL_MEASURE11
,(((BIL_MEASURE10 - BIL_MEASURE11) / ABS(DECODE(BIL_MEASURE11, 0, NULL, BIL_MEASURE11))) * 100) BIL_MEASURE12
,BIL_MEASURE13
,BIL_MEASURE14
,BIL_MEASURE15
,(((BIL_MEASURE14 - BIL_MEASURE15) / ABS(DECODE(BIL_MEASURE15, 0, NULL, BIL_MEASURE15))) * 100) BIL_MEASURE16
,BIL_MEASURE17
,BIL_MEASURE18
,BIL_MEASURE19
,(((BIL_MEASURE18 - BIL_MEASURE19) / ABS(DECODE(BIL_MEASURE19, 0, NULL, BIL_MEASURE19))) * 100) BIL_MEASURE20
,BIL_MEASURE21
,BIL_MEASURE22
,BIL_MEASURE23
,(((BIL_MEASURE22 - BIL_MEASURE23) / ABS(DECODE(BIL_MEASURE23, 0, NULL, BIL_MEASURE23))) * 100) BIL_MEASURE24
,(((BIL_MEASURE6) / DECODE(BIL_MEASURE2, 0, NULL, BIL_MEASURE2)) * 100) BIL_MEASURE25
,(((BIL_MEASURE10) / DECODE(BIL_MEASURE2, 0, NULL, BIL_MEASURE2)) * 100) BIL_MEASURE26
,(((BIL_MEASURE14) / DECODE(BIL_MEASURE2, 0, NULL, BIL_MEASURE2)) * 100) BIL_MEASURE27
,(BIL_MEASURE6 / DECODE(BIL_MEASURE10, 0, NULL, BIL_MEASURE10)) BIL_MEASURE28
,(SUM(BIL_MEASURE2) OVER()) BIL_MEASURE36
,(SUM(BIL_MEASURE3) OVER()) BIL_MEASURE37
,(((( SUM(BIL_MEASURE2) OVER() ) - ( SUM(BIL_MEASURE3) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE3) OVER(), 0, NULL, SUM(BIL_MEASURE3) OVER())) '||
' )) * 100 BIL_MEASURE38
,SUM(BIL_MEASURE6) OVER() BIL_MEASURE40
,SUM(BIL_MEASURE7) OVER() BIL_MEASURE41
,(((( SUM(BIL_MEASURE6) OVER() ) - ( SUM(BIL_MEASURE7) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE7) OVER(), 0, NULL, SUM(BIL_MEASURE7) OVER())) '||
' )) * 100 BIL_MEASURE42
,(SUM(BIL_MEASURE10) OVER()) BIL_MEASURE44
,(SUM(BIL_MEASURE11) OVER()) BIL_MEASURE45
,(((( SUM(BIL_MEASURE10) OVER() ) - ( SUM(BIL_MEASURE11) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE11) OVER(), 0, NULL, SUM(BIL_MEASURE11) OVER()))'||
' )) * 100 BIL_MEASURE46
,(SUM(BIL_MEASURE14) OVER()) BIL_MEASURE48
,(SUM(BIL_MEASURE15) OVER()) BIL_MEASURE49
,(((( SUM(BIL_MEASURE14) OVER() ) - ( SUM(BIL_MEASURE15) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE15) OVER(), 0, NULL, SUM(BIL_MEASURE15) OVER() '||
' )) )) * 100 BIL_MEASURE50
,(SUM(BIL_MEASURE18) OVER()) BIL_MEASURE52
,(SUM(BIL_MEASURE19) OVER()) BIL_MEASURE53
,(((( SUM(BIL_MEASURE18) OVER() ) - ( SUM(BIL_MEASURE19) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE19) OVER(), 0, NULL, SUM(BIL_MEASURE19) OVER() '||
' )) )) * 100 BIL_MEASURE54
,(SUM(BIL_MEASURE22) OVER()) BIL_MEASURE56
,(SUM(BIL_MEASURE23) OVER()) BIL_MEASURE57
,(((( SUM(BIL_MEASURE22) OVER() ) - ( SUM(BIL_MEASURE23) OVER() )) / ABS(DECODE(SUM(BIL_MEASURE23) OVER(), 0, NULL, SUM(BIL_MEASURE23) OVER() '||
' )) )) * 100 BIL_MEASURE58
,( SUM(BIL_MEASURE6) OVER() / (DECODE(SUM(BIL_MEASURE2) OVER(), 0, NULL, SUM(BIL_MEASURE2) OVER())) ) * 100 BIL_MEASURE59
,( SUM(BIL_MEASURE10) OVER() / (DECODE(SUM(BIL_MEASURE2) OVER(), 0, NULL, SUM(BIL_MEASURE2) OVER())) ) * 100 BIL_MEASURE60
,( SUM(BIL_MEASURE14) OVER() / (DECODE(SUM(BIL_MEASURE2) OVER(), 0, NULL, SUM(BIL_MEASURE2) OVER())) ) *100 BIL_MEASURE61
,( SUM(BIL_MEASURE6) OVER() / (DECODE(SUM(BIL_MEASURE10) OVER(), 0, NULL, SUM(BIL_MEASURE10) OVER())) ) BIL_MEASURE62
,BIL_URL1
,BIL_URL2
,DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'',
DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=LOST'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=LOST'''||'))
BIL_URL3
,DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'',
DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=No Opportunity'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=No Opportunity'''||'))
BIL_URL4
,DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'',
DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=WON'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=WON'''||'))
BIL_URL5
,DECODE('''||l_curr_as_of_date||''',TRUNC(SYSDATE),
DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'',
DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=OPEN'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=OPEN'''||')),
NULL) BIL_URL6
' ;
l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1 (VIEWBY, VIEWBYID, SORTORDER, BIL_MEASURE2,'||
'BIL_MEASURE3, '||
'BIL_MEASURE5,BIL_MEASURE6,BIL_MEASURE7, BIL_MEASURE9, BIL_MEASURE10, BIL_MEASURE11, '||
'BIL_MEASURE13,BIL_MEASURE14,BIL_MEASURE15,BIL_MEASURE17,BIL_MEASURE18,BIL_MEASURE19, '||
'BIL_MEASURE21,BIL_MEASURE22,BIL_MEASURE23,BIL_URL1, BIL_URL2)';
l_inner_select:=' select VIEWBY '||
' ,VIEWBYID '||
' ,SORTORDER '||
' ,(CASE '||
' WHEN NOT(SUM(BIL_MEASURE2) IS NULL AND SUM(BIL_MEASURE14) IS NULL) '||
' THEN '||
' NVL(SUM(BIL_MEASURE2),0) + NVL(SUM(BIL_MEASURE14),0) '||
' ELSE NULL '||
' END) BIL_MEASURE2 '||
' ,(CASE '||
' WHEN NOT(SUM(BIL_MEASURE3) IS NULL AND SUM(BIL_MEASURE15) IS NULL) '||
' THEN '||
' NVL(SUM(BIL_MEASURE3),0) + NVL(SUM(BIL_MEASURE15),0) '||
' ELSE NULL '||
' END) BIL_MEASURE3 '||
' ,SUM(BIL_MEASURE5) BIL_MEASURE5 '||
' ,SUM(BIL_MEASURE6) BIL_MEASURE6 '||
' ,SUM(BIL_MEASURE7) BIL_MEASURE7 '||
' ,SUM(BIL_MEASURE9) BIL_MEASURE9 '||
' ,SUM(BIL_MEASURE10) BIL_MEASURE10 '||
' ,SUM(BIL_MEASURE11) BIL_MEASURE11 '||
' ,SUM(BIL_MEASURE13) BIL_MEASURE13 '||
' ,SUM(BIL_MEASURE14) BIL_MEASURE14 '||
' ,SUM(BIL_MEASURE15) BIL_MEASURE15 '||
' ,SUM(BIL_MEASURE17) BIL_MEASURE17 '||
' ,SUM(BIL_MEASURE18) BIL_MEASURE18 '||
' ,SUM(BIL_MEASURE19) BIL_MEASURE19 '||
' ,SUM(BIL_MEASURE21) BIL_MEASURE21 '||
' ,SUM(BIL_MEASURE22) BIL_MEASURE22 '||
' ,SUM(BIL_MEASURE23) BIL_MEASURE23 '||
' ,BIL_URL1 '||
' ,BIL_URL2 '||
' from ';
l_inner_select1:=
' (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN '||
' (CASE '||
' WHEN NOT(sumry.won_opty_amt'||l_currency_suffix||' IS NULL
AND sumry.lost_opty_amt'||l_currency_suffix||' IS NULL AND '||
' sumry.no_opty_amt'||l_currency_suffix||' IS NULL) '||
' THEN '||
' (NVL(sumry.won_opty_amt'||l_currency_suffix||',0) + '||
' NVL(sumry.lost_opty_amt'||l_currency_suffix||',0) + '||
' NVL(sumry.no_opty_amt'||l_currency_suffix||',0) '||
' ) '||
' ELSE NULL '||
' END) '||
' ELSE NULL '||
' END) BIL_MEASURE2 '||
' , (CASE WHEN cal.report_date =:l_prev_date '||
' THEN '||
' (CASE '||
' WHEN NOT(sumry.won_opty_amt'||l_currency_suffix||' IS NULL
AND sumry.lost_opty_amt'||l_currency_suffix||' IS NULL '||
' AND sumry.no_opty_amt'||l_currency_suffix||' IS NULL) '||
' THEN '||
' (NVL(sumry.won_opty_amt'||l_currency_suffix||',0) + '||
' NVL(sumry.lost_opty_amt'||l_currency_suffix||',0) + '||
' NVL(sumry.no_opty_amt'||l_currency_suffix||',0) '||
' ) '||
' ELSE NULL '||
' END) '||
' ELSE NULL '||
' END) BIL_MEASURE3 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.won_opty_cnt '||
' ELSE NULL '||
' END) BIL_MEASURE5 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.won_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE6 '||
' , (CASE WHEN cal.report_date =:l_prev_date '||
' THEN sumry.won_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE7 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.lost_opty_cnt '||
' ELSE NULL '||
' END) BIL_MEASURE9 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.lost_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE10 '||
' , (CASE WHEN cal.report_date =:l_prev_date '||
' THEN sumry.lost_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE11 '||
' , NULL BIL_MEASURE13 '||
' , NULL BIL_MEASURE14 '||
' , NULL BIL_MEASURE15 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.new_opty_cnt '||
' ELSE NULL '||
' END) BIL_MEASURE17 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.new_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE18 '||
' , (CASE WHEN cal.report_date =:l_prev_date '||
' THEN sumry.new_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE19 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.no_opty_cnt '||
' ELSE NULL '||
' END) BIL_MEASURE21 '||
' , (CASE WHEN cal.report_date =:l_curr_as_of_date '||
' THEN sumry.no_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE22 '||
' , (CASE WHEN cal.report_date =:l_prev_date '||
' THEN sumry.no_opty_amt'||l_currency_suffix||' '||
' ELSE NULL '||
' END) BIL_MEASURE23';
l_inner_select3:= ' NULL BIL_MEASURE2 '||
' ,NULL BIL_MEASURE3 '||
' ,NULL BIL_MEASURE5 '||
' ,NULL BIL_MEASURE6 '||
' ,NULL BIL_MEASURE7 '||
' ,NULL BIL_MEASURE9 '||
' ,NULL BIL_MEASURE10 '||
' ,NULL BIL_MEASURE11 '||
' ,NULL BIL_MEASURE13 '||
' ,(CASE WHEN sumry.snap_date =:l_snap_date THEN '||
' decode(:l_period_type, '||
' 128,open_amt_year'||l_currency_suffix||', '||
' 64,open_amt_quarter'||l_currency_suffix||', '||
' 32,open_amt_period'||l_currency_suffix||', '||
' 16,open_amt_week'||l_currency_suffix||' '||
' ) '||
' ELSE NULL '||
' END) BIL_MEASURE14 '||
' ,(CASE WHEN sumry.snap_date =:l_prev_date THEN '||
' decode(:l_period_type, '||
' 128,open_amt_year'||l_currency_suffix||', '||
' 64,open_amt_quarter'||l_currency_suffix||', '||
' 32,open_amt_period'||l_currency_suffix||', '||
' 16,open_amt_week'||l_currency_suffix||' '||
' ) '||
' ELSE NULL '||
' END) BIL_MEASURE15 '||
' ,NULL BIL_MEASURE17 '||
' ,NULL BIL_MEASURE18 '||
' ,NULL BIL_MEASURE19 '||
' ,NULL BIL_MEASURE21 '||
' ,NULL BIL_MEASURE22 '||
' ,NULL BIL_MEASURE23';
l_pipe_select1 := ' NULL BIL_MEASURE2 '||
' ,NULL BIL_MEASURE3 '||
' ,NULL BIL_MEASURE5 '||
' ,NULL BIL_MEASURE6 '||
' ,NULL BIL_MEASURE7 '||
' ,NULL BIL_MEASURE9 '||
' ,NULL BIL_MEASURE10 '||
' ,NULL BIL_MEASURE11 '||
' ,NULL BIL_MEASURE13 '||
' ,(CASE WHEN sumry.snap_date =:l_snapshot_date THEN '||
' decode(:l_period_type, '||
' 128,open_amt_year'||l_currency_suffix||', '||
' 64,open_amt_quarter'||l_currency_suffix||', '||
' 32,open_amt_period'||l_currency_suffix||', '||
' 16,open_amt_week'||l_currency_suffix||' '||
' ) '||
' ELSE NULL '||
' END) BIL_MEASURE14 ';
l_pipe_select2 := ' ,(CASE WHEN sumry.snap_date =:l_prev_snap_date THEN '||
' decode(:l_period_type, '||
' 128,open_amt_year'||l_currency_suffix||', '||
' 64,open_amt_quarter'||l_currency_suffix||', '||
' 32,open_amt_period'||l_currency_suffix||', '||
' 16,open_amt_week'||l_currency_suffix||' '||
' ) '||
' ELSE NULL '||
' END) BIL_MEASURE14 ';
l_pipe_select2 := ' ,(CASE WHEN sumry.snap_date =:l_snapshot_date THEN '||
''||l_prev_amt||' '||
' ELSE NULL '||
' END) BIL_MEASURE15 ';
l_pipe_select3 := ' ,NULL BIL_MEASURE17 '||
' ,NULL BIL_MEASURE18 '||
' ,NULL BIL_MEASURE19 '||
' ,NULL BIL_MEASURE21 '||
' ,NULL BIL_MEASURE22 '||
' ,NULL BIL_MEASURE23';
l_inner_select3:= l_pipe_select1 || l_pipe_select2 || l_pipe_select3;
l_inner_select4:= ' NULL BIL_MEASURE2 '||
' ,NULL BIL_MEASURE3 '||
' ,NULL BIL_MEASURE5 '||
' ,NULL BIL_MEASURE6 '||
' ,NULL BIL_MEASURE7 '||
' ,NULL BIL_MEASURE9 '||
' ,NULL BIL_MEASURE10 '||
' ,NULL BIL_MEASURE11 '||
' ,(sumry.latest_open_opty_cnt) BIL_MEASURE13 '||
' ,NULL BIL_MEASURE14 '||
' ,NULL BIL_MEASURE15 '||
' ,NULL BIL_MEASURE17 '||
' ,NULL BIL_MEASURE18 '||
' ,NULL BIL_MEASURE19 '||
' ,NULL BIL_MEASURE21 '||
' ,NULL BIL_MEASURE22 '||
' ,NULL BIL_MEASURE23';
l_custom_sql :=l_insert_stmnt || l_inner_select ||
' ('||
' SELECT '||
' DECODE(tmp1.salesrep_id, NULL, grptl.group_name,restl.resource_name) VIEWBY '||
' ,DECODE(tmp1.salesrep_id, NULL, to_char(tmp1.sales_group_id), '||
' tmp1.salesrep_id||''.''||tmp1.sales_group_id) VIEWBYID '||
' ,DECODE(tmp1.salesrep_id, NULL, 1, 2) sortorder '||
' ,SUM(tmp1.BIL_MEASURE2) BIL_MEASURE2 '||
' ,SUM(tmp1.BIL_MEASURE3) BIL_MEASURE3 '||
' ,SUM(tmp1.BIL_MEASURE5) BIL_MEASURE5 '||
' ,SUM(tmp1.BIL_MEASURE6) BIL_MEASURE6 '||
' ,SUM(tmp1.BIL_MEASURE7) BIL_MEASURE7 '||
' ,SUM(tmp1.BIL_MEASURE9) BIL_MEASURE9 '||
' ,SUM(tmp1.BIL_MEASURE10) BIL_MEASURE10 '||
' ,SUM(tmp1.BIL_MEASURE11) BIL_MEASURE11 '||
' ,SUM(tmp1.BIL_MEASURE13) BIL_MEASURE13 '||
' ,SUM(tmp1.BIL_MEASURE14) BIL_MEASURE14 '||
' ,SUM(tmp1.BIL_MEASURE15) BIL_MEASURE15 '||
' ,SUM(tmp1.BIL_MEASURE17) BIL_MEASURE17 '||
' ,SUM(tmp1.BIL_MEASURE18) BIL_MEASURE18 '||
' ,SUM(tmp1.BIL_MEASURE19) BIL_MEASURE19 '||
' ,SUM(tmp1.BIL_MEASURE21) BIL_MEASURE21 '||
' ,SUM(tmp1.BIL_MEASURE22) BIL_MEASURE22 '||
' ,SUM(tmp1.BIL_MEASURE23) BIL_MEASURE23 '||
' ,DECODE(tmp1.salesrep_id, NULL, BIL_URL1, NULL) BIL_URL1 '||
' ,DECODE(tmp1.salesrep_id, NULL, NULL,'''||l_drill_link||''') BIL_URL2 '||
' FROM ('||
'SELECT /*+ LEADING(cal) */ '||
l_inner_select1||
',sumry.sales_group_id '||
',sumry.salesrep_id salesrep_id '||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '|| l_from1 ||
' WHERE cal.xtd_flag=:l_yes AND '
||l_inner_where_clause||
l_product_where_clause ;
'SELECT '||
l_inner_select3||
',sumry.sales_group_id sales_group_id '||
',sumry.salesrep_id salesrep_id '||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '||
l_from2 ||
' WHERE '||l_inner_where_clause2||
l_pipe_product_where_clause||
' UNION ALL '||
'SELECT '||
l_inner_select4||
',sumry.sales_group_id sales_group_id '||
',sumry.salesrep_id salesrep_id '||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '||
l_from3 ||
' WHERE '||l_inner_where_clause4||
' AND sumry.parent_sales_group_id = :l_sg_id_num '||
l_product_where_clause||' ' ;
l_custom_sql :=l_insert_stmnt || l_inner_select ||
' ('||
' SELECT '||
' resource_name VIEWBY '||
' ,tmp1.salesrep_id||''.''||tmp1.sales_group_id VIEWBYID '||
' ,tmp1.sortorder sortorder '||
' ,SUM(tmp1.BIL_MEASURE2) BIL_MEASURE2 '||
' ,SUM(tmp1.BIL_MEASURE3) BIL_MEASURE3 '||
' ,SUM(tmp1.BIL_MEASURE5) BIL_MEASURE5 '||
' ,SUM(tmp1.BIL_MEASURE6) BIL_MEASURE6 '||
' ,SUM(tmp1.BIL_MEASURE7) BIL_MEASURE7 '||
' ,SUM(tmp1.BIL_MEASURE9) BIL_MEASURE9 '||
' ,SUM(tmp1.BIL_MEASURE10) BIL_MEASURE10 '||
' ,SUM(tmp1.BIL_MEASURE11) BIL_MEASURE11 '||
' ,SUM(tmp1.BIL_MEASURE13) BIL_MEASURE13 '||
' ,SUM(tmp1.BIL_MEASURE14) BIL_MEASURE14 '||
' ,SUM(tmp1.BIL_MEASURE15) BIL_MEASURE15 '||
' ,SUM(tmp1.BIL_MEASURE17) BIL_MEASURE17 '||
' ,SUM(tmp1.BIL_MEASURE18) BIL_MEASURE18 '||
' ,SUM(tmp1.BIL_MEASURE19) BIL_MEASURE19 '||
' ,SUM(tmp1.BIL_MEASURE21) BIL_MEASURE21 '||
' ,SUM(tmp1.BIL_MEASURE22) BIL_MEASURE22 '||
' ,SUM(tmp1.BIL_MEASURE23) BIL_MEASURE23 '||
' ,DECODE(tmp1.salesrep_id, NULL, BIL_URL1, NULL) BIL_URL1 '||
' ,'''||l_drill_link||''' BIL_URL2 '||
' FROM ('||
'SELECT /*+ LEADING(cal) */ 1 sortorder, '||
l_inner_select1||
',sumry.sales_group_id '||
',sumry.salesrep_id salesrep_id '||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '|| l_from1 ||
' WHERE cal.xtd_flag=:l_yes AND '
||l_inner_where_clause||
l_product_where_clause ;
'SELECT 1 sortorder, '||
l_inner_select3||
',sumry.sales_group_id sales_group_id '||
',sumry.salesrep_id salesrep_id '||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '||
l_from2 ||
' WHERE '||l_inner_where_clause2||
l_pipe_product_where_clause ||
' UNION ALL '||
'SELECT 1 sortorder, '||
l_inner_select4||
',sumry.sales_group_id sales_group_id '||
',sumry.salesrep_id salesrep_id '||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '||
l_from3 ||
' WHERE '||l_inner_where_clause4||
' AND sumry.parent_sales_group_id = :l_sg_id_num '||
l_product_where_clause;
x_custom_sql := ' SELECT * FROM ( '||
l_outer_select||' FROM BIL_BI_RPT_TMP1 '||
' ORDER BY SORTORDER, UPPER(VIEWBY) '||
' ) WHERE NOT('||l_null_rem_where_clause||')';
l_conv_rate_selected VARCHAR2(50);
,x_conv_rate_selected => l_conv_rate_selected
,x_sg_id => l_sg_id
,x_parent_sg_id => l_parent_sales_group_id
,x_resource_id => l_resource_id
,x_prodcat_id => l_prodcat_id
,x_curr_page_time_id => l_curr_page_time_id
,x_prev_page_time_id => l_prev_page_time_id
,x_comp_type => l_comp_type
,x_parameter_valid => l_parameter_valid
,x_as_of_date => l_curr_as_of_date
,x_page_period_type => l_page_period_type
,x_prior_as_of_date => l_prev_date
,x_record_type_id => l_record_type_id
,x_viewby => l_viewby);
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '1';
'SELECT grptl.group_name VIEWBY
,SUM(revenue) BIL_MEASURE1
,SUM(priorRevenue) BIL_MEASURE2
,SUM(SUM(revenue)) over() BIL_MEASURE3
,SUM(SUM(priorRevenue)) over() BIL_MEASURE4
,VIEWBYID FROM
(
SELECT /*+ leading (cal) */
SUM(CASE WHEN cal.report_date=:l_curr_as_of_date THEN
sumry.recognized_amt_g'||l_currency_suffix||'
ELSE NULL
END) revenue
,SUM(CASE WHEN cal.report_date =:l_prev_date THEN
sumry.recognized_amt_g'||l_currency_suffix||'
ELSE NULL
END) priorRevenue
,sumry.sales_grp_id AS VIEWBYID
FROM '||l_from1 ||'sumry, '||l_fii_struct||' cal '||
l_inner_where_clause||' '||
' GROUP BY sumry.sales_grp_id
) tmp1,
jtf_rs_groups_tl grptl
WHERE grptl.group_id = tmp1.viewbyid
AND grptl.language = USERENV(''LANG'')
GROUP BY grptl.group_name, VIEWBYID';
'SELECT grptl.resource_name VIEWBY
,SUM(revenue) BIL_MEASURE1
,SUM(priorRevenue) BIL_MEASURE2
,SUM(SUM(revenue)) over() BIL_MEASURE3
,SUM(SUM(priorRevenue)) over() BIL_MEASURE4
,VIEWBYID FROM
(
SELECT /*+ LEADING(cal) */
SUM(CASE WHEN cal.report_date=:l_curr_as_of_date THEN
sumry.recognized_amt_g'||l_currency_suffix||'
ELSE NULL
END) revenue,
SUM(CASE WHEN cal.report_date =:l_prev_date THEN
sumry.recognized_amt_g'||l_currency_suffix||'
ELSE NULL
END) priorRevenue
,sumry.sales_grp_id, sumry.resource_id AS VIEWBYID
FROM '||l_from1 ||'sumry, '||l_fii_struct||' cal '||
l_inner_where_clause||' '||
' AND sumry.resource_id = :l_resource_id '||
' GROUP BY sumry.sales_grp_id, sumry.resource_id
) tmp1,
jtf_rs_resource_extns_tl grptl
WHERE grptl.resource_id = tmp1.viewbyid
AND grptl.language = USERENV(''LANG'')
GROUP BY grptl.resource_name, VIEWBYID';
'l_conv_rate_selected => '|| l_conv_rate_selected ||', ' ||
'l_bitand_id => '|| l_bitand_id ||', ' ||
'l_period_type => '|| l_period_type ||', ' ||
'l_sg_id_num => '|| l_sg_id_num ||', ' ||
'l_calendar_id => '|| l_calendar_id;
l_conv_rate_selected VARCHAR2(50);
l_outer_select VARCHAR2(8000);
l_inner_select VARCHAR2(4000);
l_insert_stmnt VARCHAR2(32000);
l_pipe_select1 varchar2(4000);
l_pipe_select2 varchar2(4000);
l_pipe_select3 varchar2(4000);
,x_conv_rate_selected => l_conv_rate_selected
,x_sg_id => l_sg_id
,x_parent_sg_id => l_parent_sg_id_num
,x_resource_id => l_resource_id
,x_prodcat_id => l_prodcat_id
,x_curr_page_time_id => l_curr_page_time_id
,x_prev_page_time_id => l_prev_page_time_id
,x_comp_type => l_comp_type
,x_parameter_valid => l_parameter_valid
,x_as_of_date => l_curr_as_of_date
,x_page_period_type => l_page_period_type
,x_prior_as_of_date => l_prev_date
,x_record_type_id => l_record_type_id
,x_viewby => l_viewby );
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '_s';
'l_conv_rate_selected => '|| l_conv_rate_selected ||', ' ||
'l_bitand_id => '|| l_bitand_id ||', ' ||
'l_period_type => '|| l_period_type ||', ' ||
'l_sg_id_num => '|| l_sg_id_num ||', ' ||
'l_resource_id => '|| l_resource_id ||', '||
'l_bis_sysdate => '|| l_bis_sysdate ||', ' ||
'l_fst_crdt_type => ' || l_fst_crdt_type||', '||
'l_record_type_id => '|| l_record_type_id;
l_outer_select:= 'SELECT VIEWBY';
l_outer_select := l_outer_select ||',DECODE(BIL_URL1,NULL,VIEWBYID||''.''||:l_sg_id_num,VIEWBYID) VIEWBYID ';
l_outer_select := l_outer_select ||',VIEWBYID ';
l_outer_select := l_outer_select ||',DECODE(BIL_MEASURE28,0,NULL,BIL_MEASURE28) BIL_MEASURE1'||
',BIL_MEASURE2'||
',(BIL_MEASURE28-BIL_MEASURE2)/ABS(DECODE(BIL_MEASURE2,0,null,BIL_MEASURE2)) * 100 BIL_MEASURE3 '||
',BIL_MEASURE4'||
',BIL_MEASURE5'||
',(BIL_MEASURE4-BIL_MEASURE5)/ABS(DECODE(BIL_MEASURE5,0,null,BIL_MEASURE5)) * 100 BIL_MEASURE6'||
',DECODE(BIL_MEASURE7,0,NULL,BIL_MEASURE7) BIL_MEASURE7'||
',BIL_MEASURE8'||
',(BIL_MEASURE7-BIL_MEASURE8)/ABS(DECODE(BIL_MEASURE8,0,null,BIL_MEASURE8)) * 100 BIL_MEASURE9'||
',DECODE(BIL_MEASURE10,0,NULL,BIL_MEASURE10) BIL_MEASURE10'||
',BIL_MEASURE11'||
',(BIL_MEASURE10-BIL_MEASURE11)/ABS(DECODE(BIL_MEASURE11,0,null,BIL_MEASURE11)) * 100 BIL_MEASURE12 '||
', BIL_MEASURE4 BIL_MEASURE25 '||
',SUM(DECODE(BIL_MEASURE28,0,NULL,BIL_MEASURE28)) OVER() BIL_MEASURE13'||
',SUM(BIL_MEASURE2) OVER() BIL_MEASURE14'||
',(SUM(BIL_MEASURE28) OVER() - SUM(BIL_MEASURE2) OVER())/ABS(DECODE(SUM(BIL_MEASURE2) OVER(), 0, null, SUM(BIL_MEASURE2)
OVER()))*100 BIL_MEASURE15 '||
',SUM(BIL_MEASURE4) OVER() BIL_MEASURE16'||
',SUM(BIL_MEASURE5) OVER() BIL_MEASURE17'||
',(SUM(BIL_MEASURE4) OVER() - SUM(BIL_MEASURE5) OVER())/ABS(DECODE(SUM(BIL_MEASURE5) OVER(), 0, null, SUM(BIL_MEASURE5)
OVER()))*100 BIL_MEASURE18 '||
',SUM(DECODE(BIL_MEASURE7,0,NULL,BIL_MEASURE7)) OVER() BIL_MEASURE19'||
',SUM(BIL_MEASURE8) OVER() BIL_MEASURE20'||
',(SUM(BIL_MEASURE7) OVER() - SUM(BIL_MEASURE8) OVER())/ABS(DECODE(SUM(BIL_MEASURE8) OVER(), 0, null, SUM(BIL_MEASURE8)
OVER()))*100 BIL_MEASURE21 '||
',SUM(DECODE(BIL_MEASURE10,0,NULL,BIL_MEASURE10)) OVER() BIL_MEASURE22'||
',SUM(BIL_MEASURE11) OVER() BIL_MEASURE23'||
',(SUM(BIL_MEASURE10) OVER() - SUM(BIL_MEASURE11) OVER())/ABS(DECODE(SUM(BIL_MEASURE11) OVER(), 0, null, SUM(BIL_MEASURE11)
OVER()))*100 BIL_MEASURE24 '||
',SUM(BIL_MEASURE4) OVER() BIL_MEASURE26'||
',BIL_URL1'||
',BIL_URL2
,DECODE('''||l_curr_as_of_date||''',TRUNC(SYSDATE),
DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'',
DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=OPEN'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=OPEN'''||')),
NULL) BIL_URL3
';
l_inner_select := 'SELECT VIEWBY
,SORTORDER
,SUM(BIL_MEASURE28) BIL_MEASURE28
,SUM(BIL_MEASURE2) BIL_MEASURE2
,SUM(BIL_MEASURE4) BIL_MEASURE4
,SUM(BIL_MEASURE5) BIL_MEASURE5
,SUM(BIL_MEASURE7) BIL_MEASURE7
,SUM(BIL_MEASURE8) BIL_MEASURE8
,SUM(BIL_MEASURE10) BIL_MEASURE10
,SUM(BIL_MEASURE11) BIL_MEASURE11
,VIEWBYID
,BIL_URL1
,BIL_URL2 ';
l_insert_stmnt := ' INSERT INTO bil_bi_rpt_tmp1(VIEWBY,VIEWBYID,SORTORDER,
BIL_MEASURE28, BIL_MEASURE2,'||
'BIL_MEASURE4,BIL_MEASURE5,BIL_MEASURE7,BIL_MEASURE8,'||
'BIL_MEASURE10,BIL_MEASURE11, BIL_URL1,BIL_URL2)';
l_pipe_select1 := ' NULL BIL_MEASURE28 '||
',NULL BIL_MEASURE2 '||
', '||
'SUM(CASE WHEN sumry.snap_date =:l_snap_date THEN '||
'sumry.'||l_open_col ||' '||
'ELSE NULL '||
'END '||
' ) BIL_MEASURE4 ';
l_pipe_select2 := ',SUM( '||
'CASE WHEN sumry.snap_date =:l_prev_snap_date THEN '||
'sumry.'||l_open_col ||' '||
'ELSE NULL '||
'END '||
' ) BIL_MEASURE5 ';
l_pipe_select2 := ',SUM( '||
'CASE WHEN sumry.snap_date =:l_snap_date THEN '||
''||l_prev_amt||' '||
'ELSE NULL '||
'END '||
' ) BIL_MEASURE5 ';
l_pipe_select3 := ',NULL BIL_MEASURE7 '||
',NULL BIL_MEASURE8 '||
',NULL BIL_MEASURE10 '||
',NULL BIL_MEASURE11 ';
l_sql_stmt1:= l_pipe_select1 || l_pipe_select2 || l_pipe_select3;
' AND sumry.update_time_id = ''-1'' '||
' AND sumry.update_period_type_id = ''-1''';
'AND sumry.update_time_id = ''-1'' '||
'AND sumry.update_period_type_id = ''-1'' ';
l_custom_sql:= 'SELECT decode(sumry.salesrep_id, NULL, grptl.group_name,
restl.resource_name) VIEWBY, decode(sumry.salesrep_id, NULL,
sumry.sales_group_id, sumry.salesrep_id) VIEWBYID,
SORTORDER, BIL_MEASURE28, BIL_MEASURE2, BIL_MEASURE4,
BIL_MEASURE5, BIL_MEASURE7, BIL_MEASURE8, BIL_MEASURE10,
BIL_MEASURE11, BIL_URL1,
DECODE(sumry.salesrep_id, NULL, NULL,'''||l_drill_link||''') BIL_URL2
FROM (
SELECT /*+ NO_MERGE */ salesrep_id, sales_group_id, sortorder,
sum(bil_measure28) bil_measure28,sum(bil_measure2) bil_measure2,
sum(bil_measure4) bil_measure4, sum(bil_measure5) bil_measure5,
SUM(BIL_MEASURE7) BIL_MEASURE7 ,SUM(BIL_MEASURE8) BIL_MEASURE8 ,
SUM(BIL_MEASURE10) BIL_MEASURE10,
SUM(BIL_MEASURE11) BIL_MEASURE11, BIL_URL1, NULL BIL_URL2
FROM (
SELECT salesrep_id, sales_group_id, sortorder,
sum(bil_measure28) bil_measure28,
sum(bil_measure2) bil_measure2,
sum(bil_measure4) bil_measure4,
sum(bil_measure5) bil_measure5,
NULL BIL_MEASURE7 ,NULL BIL_MEASURE8 ,
NULL BIL_MEASURE10 ,NULL BIL_MEASURE11,
BIL_URL1, NULL BIL_URL2
FROM ( ';
l_custom_sql :=l_custom_sql||'SELECT sumry.salesrep_id,sumry.sales_group_id,
'||l_prodcat_flag||'
(CASE WHEN sumry.salesrep_id IS NULL THEN 1 ELSE 2 END) SORTORDER' ||
','||l_sql_stmt1||
',(CASE WHEN sumry.salesrep_id IS NULL
THEN '''||l_url_str||''' ELSE NULL END) BIL_URL1
,NULL BIL_URL2
FROM '||l_sumry1||' sumry
'||l_where_clause1||'
'||l_group_flag;
l_custom_sql := l_custom_sql||' UNION ALL SELECT /*+ leading (cal) */ null salesrep_id,
sumry.group_id sales_group_id , '||l_prodcat_flag||
'1 SORTORDER,' ||
' '||l_sql_stmt2||
','''||l_url_str||''' BIL_URL1 '||
',null BIL_URL2 '||
' FROM '||l_sumry2||' sumry,
jtf_rs_grp_relations rels, '||
l_fii_struct||' cal '||
l_where_clause2||
' AND rels.related_group_id = :l_sg_id_num '||
' AND rels.relation_type = ''PARENT_GROUP'' '||
' AND rels.group_id <> rels.related_group_id '||
' AND :l_bis_sysdate BETWEEN rels.start_date_active '||
' AND NVL(rels.end_date_active, :l_bis_sysdate) '||
' AND NVL(rels.delete_flag, ''N'') <> ''Y'' '||
' AND sumry.group_id = rels.group_id '||
' AND sumry.resource_id = ''-1'' ' ||
' GROUP BY '||l_prodcat_flag||' sumry.group_id ';
l_custom_sql := l_custom_sql||' UNION ALL SELECT /*+ leading (cal) */ sumry.resource_id salesrep_id ,
sumry.group_id sales_group_id, '||l_prodcat_flag||
' 2 SORTORDER '||
','||l_sql_stmt2||
',NULL BIL_URL1 '||
',NULL BIL_URL2 '||
' FROM '||l_sumry2||' sumry '||
','||l_fii_struct||' cal '||
' '||l_where_clause2||
' AND sumry.group_id = :l_sg_id_num ';
l_custom_sql := l_custom_sql||' UNION ALL SELECT /*+ leading (cal) */ sumry.resource_id salesrep_id,
sumry.sales_grp_id sales_group_id '||
',(CASE WHEN sumry.resource_id IS NULL THEN 1 ELSE 2 END) SORTORDER' ||
','||l_sql_stmt3||
',(CASE WHEN sumry.resource_id IS NULL
THEN '''||l_url_str||''' ELSE NULL END) BIL_URL1
,NULL BIL_URL2
FROM '||l_sumry3||' sumry
,'||l_fii_struct||' cal '
||l_where_clause3||'
'||l_product_where_clause1;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
--open oppties
USING l_snap_date,
l_snap_date, l_sg_id_num,
l_snap_date,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id, l_bitand_id, l_curr_as_of_date, l_prev_date
, l_sg_id_num, l_bis_sysdate, l_bis_sysdate,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id, l_bitand_id, l_curr_as_of_date, l_prev_date,
l_sg_id_num,
--backlog
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id,l_bitand_id
;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
--open oppties
USING l_snap_date,
l_prev_snap_date, l_sg_id_num,
l_snap_date, l_prev_snap_date,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id, l_bitand_id, l_curr_as_of_date, l_prev_date
, l_sg_id_num, l_bis_sysdate, l_bis_sysdate,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id, l_bitand_id, l_curr_as_of_date, l_prev_date,
l_sg_id_num,
--backlog
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id,l_bitand_id
;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_snap_date,
l_snap_date, l_sg_id_num,
l_snap_date,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id, l_bitand_id, l_curr_as_of_date, l_prev_date,
l_sg_id_num,
--backlog
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id,l_bitand_id
;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_snap_date,
l_prev_snap_date, l_sg_id_num,
l_snap_date, l_prev_snap_date,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id, l_bitand_id, l_curr_as_of_date, l_prev_date,
l_sg_id_num,
--backlog
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id,l_bitand_id
;
ELSE --prodcat selected
if l_resource_id is null then
IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
--open oppties
USING l_snap_date,
l_snap_date, l_sg_id_num,
l_snap_date,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id,l_bitand_id,l_curr_as_of_date,l_prev_date
, l_sg_id_num,l_bis_sysdate, l_bis_sysdate,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id, l_bitand_id, l_curr_as_of_date, l_prev_date
, l_sg_id_num, l_prodcat_id,
--backlog
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id, l_bitand_id, l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
--open oppties
USING l_snap_date,
l_prev_snap_date, l_sg_id_num,
l_snap_date, l_prev_snap_date,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id,l_bitand_id,l_curr_as_of_date,l_prev_date
, l_sg_id_num,l_bis_sysdate, l_bis_sysdate,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id, l_bitand_id, l_curr_as_of_date, l_prev_date
, l_sg_id_num, l_prodcat_id,
--backlog
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id, l_bitand_id, l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
--open oppties
USING l_snap_date,
l_snap_date, l_sg_id_num,
l_snap_date,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id, l_bitand_id, l_curr_as_of_date, l_prev_date
, l_sg_id_num, l_prodcat_id,
--backlog
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id, l_bitand_id, l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
--open oppties
USING l_snap_date,
l_prev_snap_date, l_sg_id_num,
l_snap_date, l_prev_snap_date,
--open leads
l_curr_as_of_date, l_prev_date
, l_bitand_id, l_bitand_id, l_curr_as_of_date, l_prev_date
, l_sg_id_num, l_prodcat_id,
--backlog
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id, l_bitand_id, l_prodcat_id;
x_custom_sql:= l_outer_select ||
' FROM( '||
'SELECT VIEWBY, SORTORDER,
SUM(BIL_MEASURE28) BIL_MEASURE28, '||
'SUM(BIL_MEASURE2) BIL_MEASURE2, '||
'SUM(BIL_MEASURE4) BIL_MEASURE4,
SUM(BIL_MEASURE5) BIL_MEASURE5, '||
'SUM(BIL_MEASURE7) BIL_MEASURE7,
SUM(BIL_MEASURE8) BIL_MEASURE8, '||
'SUM(BIL_MEASURE10) BIL_MEASURE10,
SUM(BIL_MEASURE11) BIL_MEASURE11, '||
'VIEWBYID, BIL_URL1, BIL_URL2 '||
' FROM BIL_BI_RPT_TMP1 GROUP BY VIEWBY, SORTORDER, VIEWBYID, BIL_URL1, BIL_URL2 '||
') '|| l_null_rem_clause ||' ORDER BY SORTORDER, VIEWBY ';
l_custom_sql := ' SELECT null VIEWBY'||
',sumry.product_category_id VIEWBYID'||
', 1 SORTORDER'||
','||l_sql_stmt1||
',NULL BIL_URL1'||
',NULL BIL_URL2 '||
' FROM '||l_sumry1||' sumry'||
' '||l_where_clause4||
' '||l_group_flag||
' GROUP BY sumry.product_category_id';
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_snap_date,
l_snap_date,
l_snap_date,
l_sg_id_num,l_resource_id,l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_snap_date,
l_prev_snap_date,
l_snap_date, l_prev_snap_date,
l_sg_id_num,l_resource_id,l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_snap_date,
l_snap_date,
l_snap_date,
l_sg_id_num,l_parent_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_snap_date,
l_prev_snap_date,
l_snap_date, l_prev_snap_date,
l_sg_id_num,l_parent_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_snap_date,
l_snap_date,
l_snap_date, l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_snap_date,
l_prev_snap_date,
l_snap_date, l_prev_snap_date,l_sg_id_num;
l_custom_sql := ' SELECT /*+ leading (cal) */ null VIEWBY'||
',sumry.product_category_id VIEWBYID'||
', 1 SORTORDER'||
','||l_sql_stmt2||
',NULL BIL_URL1'||
',NULL BIL_URL2 '||
' FROM '||l_sumry2||' sumry'||
','||l_fii_struct||' cal'||
' '||l_where_clause5;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_curr_as_of_date,l_prev_date,
l_bitand_id,l_bitand_id,l_curr_as_of_date,l_prev_date,l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_curr_as_of_date,l_prev_date,
l_bitand_id,l_bitand_id,l_curr_as_of_date,l_prev_date,l_sg_id_num,l_resource_id;
x_custom_sql := l_outer_select||' FROM (
SELECT VIEWBY, VIEWBYID, SORTORDER, SUM(BIL_MEASURE28) BIL_MEASURE28,
SUM(BIL_MEASURE2) BIL_MEASURE2, SUM(BIL_MEASURE4) BIL_MEASURE4,
SUM(BIL_MEASURE5) BIL_MEASURE5, SUM(BIL_MEASURE7) BIL_MEASURE7,
SUM(BIL_MEASURE8) BIL_MEASURE8, SUM(BIL_MEASURE10) BIL_MEASURE10,
SUM(BIL_MEASURE11) BIL_MEASURE11, BIL_URL1, BIL_URL2 FROM
(select decode(parent_id, -1,:l_unassigned_value,
mtl.DESCRIPTION || '' ('' || mtl.CATEGORY_CONCAT_SEGS ||'')'') VIEWBY,
parent_id VIEWBYID,
1 SORTORDER, BIL_MEASURE28,'||
'BIL_MEASURE2, '||
'BIL_MEASURE4,BIL_MEASURE5, BIL_MEASURE7, BIL_MEASURE8, '||
'BIL_MEASURE10,BIL_MEASURE11,BIL_URL1, DECODE(parent_id,''-1'',NULL,'''||l_cat_url||''') BIL_URL2
from (select pcd.parent_id parent_id,
SORTORDER, BIL_MEASURE28,'||
'BIL_MEASURE2, '||
'BIL_MEASURE4,BIL_MEASURE5, BIL_MEASURE7, BIL_MEASURE8, '||
'BIL_MEASURE10,BIL_MEASURE11,
'''||l_drill_link||''' BIL_URL1, BIL_URL2
from (select VIEWBYID product_category_id,
SORTORDER, BIL_MEASURE28,'||
'BIL_MEASURE2, '||
'BIL_MEASURE4,BIL_MEASURE5, BIL_MEASURE7, BIL_MEASURE8, '||
'BIL_MEASURE10,BIL_MEASURE11,BIL_URL1, BIL_URL2 FROM BIL_BI_RPT_TMP1) sumry '||l_denorm||' where
sortorder = 1 '||l_product_where_clause||') opty, mtl_categories_v mtl '||
' WHERE mtl.category_id (+) = opty.parent_id
UNION ALL
SELECT /*+ leading (cal) */ pcd.value VIEWBY'||
',pcd.id VIEWBYID'||
', 1 SORTORDER'||
','||l_sql_stmt3||
', '''||l_drill_link||''' BIL_URL1'||
',decode(pcd.id, ''-1'',NULL, '''||l_cat_url||''') BIL_URL2 '||
' FROM '||l_sumry3||' sumry'||
','||l_fii_struct||' cal,ENI_ITEM_PROD_CAT_LOOKUP_V pcd
'||l_where_clause6||
' '||l_product_where_clause1
||' GROUP BY pcd.value, pcd.id '||
')
'|| l_null_rem_clause||
' GROUP BY VIEWBY, VIEWBYID, SORTORDER,
BIL_URL1, BIL_URL2)
ORDER BY SORTORDER, VIEWBY ';
l_custom_sql := 'SELECT DECODE(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'', :l_cat_assign, pcd.value), pcd.value) VIEWBY
,pcd.id VIEWBYID'||
',DECODE(pcd.parent_id, pcd.id, decode(sumry.item_id, ''-1'', 1, 2), 2) SORTORDER '||
',BIL_MEASURE28,BIL_MEASURE2, BIL_MEASURE4,BIL_MEASURE5,
NULL BIL_MEASURE7 ,NULL BIL_MEASURE8 ,NULL BIL_MEASURE10 ,
NULL BIL_MEASURE11,
decode(sumry.item_id, ''-1'', decode(pcd.parent_id, pcd.id,NULL,'''||l_drill_link||'''),'''||l_drill_link||''') BIL_URL1,
DECODE(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL2
FROM
( ';
SELECT sumry.product_category_id, to_char(sumry.item_id) item_id '||
','||l_sql_stmt1||
',NULL BIL_URL1'||
',NULL BIL_URL2
FROM '||l_sumry1||' sumry'||
' '||
l_where_clause4||' '
||l_group_flag
|| l_group_by;
SELECT sumry.product_category_id, sumry.item_id,
'||l_sql_stmt2||
',NULL BIL_URL1
,NULL BIL_URL2
FROM '||l_sumry2||' sumry '||
','||l_fii_struct||' cal'||
l_where_clause5 ;
SELECT pcd.value VIEWBY
,pcd.id VIEWBYID
,2 SORTORDER,'||l_sql_stmt3||
', DECODE(pcd.parent_id, pcd.id, NULL, '''||l_drill_link||''') BIL_URL1
, DECODE(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL2
FROM ENI_ITEM_PROD_CAT_LOOKUP_V pcd,'||l_fii_struct||' cal, '||l_sumry3||' sumry '||
l_where_clause6 ||' '
||l_product_where_clause1||
' GROUP BY 2
,pcd.value
,pcd.id
,DECODE(pcd.parent_id, pcd.id, NULL, '''||l_drill_link||''')
, DECODE(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') ';
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_cat_assign,
l_snap_date, l_snap_date,
l_snap_date,
l_sg_id_num,l_resource_id,l_sg_id_num,
l_curr_as_of_date,l_prev_date,
l_bitand_id,l_bitand_id,l_curr_as_of_date,l_prev_date,
l_sg_id_num, l_resource_id, l_prodcat_id,
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_resource_id,l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id, l_bitand_id, l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_cat_assign,
l_snap_date, l_prev_snap_date,
l_snap_date, l_prev_snap_date,
l_sg_id_num,l_resource_id,l_sg_id_num,
l_curr_as_of_date,l_prev_date,
l_bitand_id,l_bitand_id,l_curr_as_of_date,l_prev_date,
l_sg_id_num, l_resource_id, l_prodcat_id,
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_resource_id,l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id, l_bitand_id, l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_cat_assign,
l_snap_date, l_snap_date,
l_snap_date,
l_sg_id_num,l_parent_sg_id_num,
l_curr_as_of_date,l_prev_date,
l_bitand_id,l_bitand_id,l_curr_as_of_date,l_prev_date,
l_sg_id_num,l_prodcat_id,
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_parent_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id, l_bitand_id, l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_cat_assign,
l_snap_date, l_prev_snap_date,
l_snap_date, l_prev_snap_date,
l_sg_id_num,l_parent_sg_id_num,
l_curr_as_of_date,l_prev_date,
l_bitand_id,l_bitand_id,l_curr_as_of_date,l_prev_date,
l_sg_id_num,l_prodcat_id,
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_parent_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id, l_bitand_id, l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_cat_assign,
l_snap_date, l_snap_date,
l_snap_date,
l_sg_id_num,
l_curr_as_of_date,l_prev_date,
l_bitand_id,l_bitand_id,l_curr_as_of_date,l_prev_date,
l_sg_id_num,l_prodcat_id,
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id, l_bitand_id, l_prodcat_id;
EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
USING l_cat_assign,
l_snap_date, l_prev_snap_date,
l_snap_date, l_prev_snap_date,
l_sg_id_num,
l_curr_as_of_date,l_prev_date,
l_bitand_id,l_bitand_id,l_curr_as_of_date,l_prev_date,
l_sg_id_num,l_prodcat_id,
l_curr_as_of_date,
l_prev_date,
l_curr_as_of_date,
l_prev_date,
l_sg_id_num,l_curr_as_of_date, l_prev_date,
l_bitand_id, l_bitand_id, l_prodcat_id;
x_custom_sql := l_outer_select||
' FROM
(SELECT VIEWBY, VIEWBYID, SORTORDER,
SUM(BIL_MEASURE28) BIL_MEASURE28, SUM(BIL_MEASURE2) BIL_MEASURE2,
SUM(BIL_MEASURE4) BIL_MEASURE4, SUM(BIL_MEASURE5) BIL_MEASURE5,
SUM(BIL_MEASURE7) BIL_MEASURE7,SUM(BIL_MEASURE8) BIL_MEASURE8,
SUM(BIL_MEASURE10) BIL_MEASURE10,
SUM(BIL_MEASURE11) BIL_MEASURE11, BIL_URL1, BIL_URL2 FROM
('||
' SELECT VIEWBY, VIEWBYID, 1 SORTORDER,
SUM(BIL_MEASURE28) BIL_MEASURE28, SUM(BIL_MEASURE2) BIL_MEASURE2,
SUM(BIL_MEASURE4) BIL_MEASURE4, SUM(BIL_MEASURE5) BIL_MEASURE5,
SUM(BIL_MEASURE7) BIL_MEASURE7,SUM(BIL_MEASURE8) BIL_MEASURE8,
SUM(BIL_MEASURE10) BIL_MEASURE10,
SUM(BIL_MEASURE11) BIL_MEASURE11, BIL_URL1, BIL_URL2 '||
' FROM bil_bi_rpt_tmp1 '||
' WHERE SORTORDER = 1 GROUP BY VIEWBY, VIEWBYID, SORTORDER,
BIL_URL1, BIL_URL2
'||
' UNION ALL '||
' SELECT VIEWBY, VIEWBYID, 2 SORTORDER, BIL_MEASURE28, BIL_MEASURE2,
BIL_MEASURE4, BIL_MEASURE5,BIL_MEASURE7,
BIL_MEASURE8, BIL_MEASURE10, '||
'BIL_MEASURE11,'''||l_drill_link||''' BIL_URL1, NULL BIL_URL2 '||
' FROM
('||
' SELECT SUM(RN) RN, MAX(VIEWBY) VIEWBY, MAX(VIEWBYID) VIEWBYID, '||
' SUM(BIL_MEASURE28) BIL_MEASURE28, SUM(BIL_MEASURE2) BIL_MEASURE2, '||
' SUM(BIL_MEASURE4) BIL_MEASURE4, SUM(BIL_MEASURE5) BIL_MEASURE5, '||
' SUM(BIL_MEASURE7) BIL_MEASURE7, SUM(BIL_MEASURE8) BIL_MEASURE8, '||
' SUM(BIL_MEASURE10) BIL_MEASURE10, SUM(BIL_MEASURE11) BIL_MEASURE11 '||
' FROM
('||
' SELECT ROWNUM RN, VIEWBY, VIEWBYID, BIL_MEASURE28,'||
' BIL_MEASURE2, BIL_MEASURE4, BIL_MEASURE5,TRUNC(BIL_MEASURE7,3) BIL_MEASURE7, '||
' TRUNC(BIL_MEASURE8,3) BIL_MEASURE8,
TRUNC(BIL_MEASURE10,3) BIL_MEASURE10, TRUNC(BIL_MEASURE11,3) BIL_MEASURE11 '||
' FROM
bil_bi_rpt_tmp1 '||
' WHERE SORTORDER <> 1
'||
' UNION ALL '||
' SELECT -ROWNUM RN, NULL VIEWBY, VIEWBYID, NULL BIL_MEASURE28,'||
'NULL BIL_MEASURE2, NULL BIL_MEASURE4, NULL BIL_MEASURE5,-TRUNC(BIL_MEASURE7,3) BIL_MEASURE7, '||
' -TRUNC(BIL_MEASURE8,3) BIL_MEASURE8, -TRUNC(BIL_MEASURE10,3) BIL_MEASURE10,
-TRUNC(BIL_MEASURE11,3) BIL_MEASURE11 '||
' FROM
( SELECT VIEWBYID, SUM(BIL_MEASURE28) BIL_MEASURE28,
SUM(BIL_MEASURE2) BIL_MEASURE2, SUM(BIL_MEASURE4) BIL_MEASURE4,
SUM(BIL_MEASURE5) BIL_MEASURE5, SUM(BIL_MEASURE7) BIL_MEASURE7,
SUM(BIL_MEASURE8) BIL_MEASURE8, SUM(BIL_MEASURE10) BIL_MEASURE10,
SUM(BIL_MEASURE11) BIL_MEASURE11
FROM bil_bi_rpt_tmp1 '||
' WHERE SORTORDER = 1 GROUP BY VIEWBYID
)'||
') '||
' ) '||
' WHERE NOT( RN = 0 AND BIL_MEASURE28 = 0 AND BIL_MEASURE2 = 0 '||
' AND BIL_MEASURE4 = 0 AND BIL_MEASURE5 = 0 AND BIL_MEASURE7 = 0'||
' AND BIL_MEASURE8 = 0 AND BIL_MEASURE10 = 0 AND BIL_MEASURE11 = 0 ) '||
' )
GROUP BY VIEWBY, VIEWBYID, SORTORDER, BIL_URL1, BIL_URL2
)'|| l_null_rem_clause||'
ORDER BY SORTORDER, VIEWBY ';
x_custom_sql:= l_outer_select ||
' FROM( '||
'SELECT VIEWBY, SORTORDER, SUM(BIL_MEASURE28) BIL_MEASURE28, '||
'SUM(BIL_MEASURE2) BIL_MEASURE2, '||
'SUM(BIL_MEASURE4) BIL_MEASURE4, SUM(BIL_MEASURE5) BIL_MEASURE5, '||
'SUM(BIL_MEASURE7) BIL_MEASURE7, SUM(BIL_MEASURE8) BIL_MEASURE8, '||
'SUM(BIL_MEASURE10) BIL_MEASURE10, SUM(BIL_MEASURE11) BIL_MEASURE11, '||
'VIEWBYID, BIL_URL1, BIL_URL2 '||
' FROM BIL_BI_RPT_TMP1 GROUP BY VIEWBY, SORTORDER, VIEWBYID, BIL_URL1, BIL_URL2 '||
') '|| l_null_rem_clause||' ORDER BY SORTORDER, VIEWBY';
END IF; -- end category selected check
l_conv_rate_selected VARCHAR2(50);
l_conv_rate_selected_num Number;
l_outer_select VARCHAR2(8000);
l_inner_select VARCHAR2(8000);
l_inner_select1 VARCHAR2(8000);
l_inner_select2 VARCHAR2(8000);
l_insert_stmnt VARCHAR2(8000);
,x_conv_rate_selected => l_conv_rate_selected
,x_sg_id => l_sg_id
,x_parent_sg_id => l_parent_sg_id_num
,x_resource_id => l_resource_id
,x_prodcat_id => l_prodcat_id
,x_curr_page_time_id => l_curr_page_time_id
,x_prev_page_time_id => l_prev_page_time_id
,x_comp_type => l_comp_type
,x_parameter_valid => l_parameter_valid
,x_as_of_date => l_curr_as_of_date
,x_page_period_type => l_page_period_type
,x_prior_as_of_date => l_prev_date
,x_record_type_id => l_record_type_id
,x_viewby => l_viewby
);
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '_s';
l_conv_rate_selected_num := TO_NUMBER(REPLACE(l_conv_rate_selected, ''''));
'l_conv_rate_selected => '|| l_conv_rate_selected ||', ' ||
'l_bitand_id => '|| l_bitand_id ||', ' ||
'l_period_type => '|| l_period_type ||', ' ||
'l_sg_id => '|| l_sg_id ||', ' ||
'l_resource_id => '|| l_resource_id ||', ' ||
'l_record_type_id => '|| l_record_type_id||', ' ||
'l_calendar_id => '|| l_calendar_id||', ' ||
'l_campaign_id: => '||l_campaign_id;
l_insert_stmnt is used to insert into temp table1.
l_innerselect is the core select that hits the MVs.
*/
--BIL_BI_RPT_TMP1
l_insert_stmnt :=
'INSERT INTO BIL_BI_RPT_TMP1
(
VIEWBYID,
BIL_MEASURE28,
BIL_MEASURE2,
BIL_MEASURE3,
BIL_MEASURE5,
BIL_MEASURE6,
BIL_MEASURE7,
BIL_MEASURE8,
BIL_MEASURE9,
BIL_MEASURE10,
BIL_MEASURE11,
BIL_MEASURE12,
BIL_MEASURE13,
BIL_MEASURE14,
BIL_MEASURE15
) ';
l_inner_select :=
' SELECT /*+ NO_MERGE */
VIEWBYID
,SUM(BIL_MEASURE28) BIL_MEASURE28
,SUM(BIL_MEASURE2) BIL_MEASURE2
,SUM(BIL_MEASURE3) BIL_MEASURE3
,SUM(BIL_MEASURE5) BIL_MEASURE5
,SUM(BIL_MEASURE6) BIL_MEASURE6
,SUM(BIL_MEASURE7) BIL_MEASURE7
,SUM(BIL_MEASURE8) BIL_MEASURE8
,SUM(BIL_MEASURE9) BIL_MEASURE9
,SUM(BIL_MEASURE10) BIL_MEASURE10
,SUM(BIL_MEASURE11) BIL_MEASURE11
,SUM(BIL_MEASURE12) BIL_MEASURE12
,SUM(BIL_MEASURE13) BIL_MEASURE13
,SUM(BIL_MEASURE14) BIL_MEASURE14
,BIL_MEASURE15
FROM ';
'SELECT sumry.source_code_id VIEWBYID
,SUM(CASE WHEN cal.report_date =:l_curr_as_of_date
THEN new_leads_cnt ELSE NULL END) BIL_MEASURE28
,SUM(CASE WHEN cal.report_date =:l_curr_as_of_date
THEN cnv_leads_cnt ELSE NULL END) BIL_MEASURE2
,NULL BIL_MEASURE3
,SUM(CASE WHEN cal.report_date =:l_curr_as_of_date
THEN sumry.new_opty_amt'||l_currency_suffix||' ELSE NULL END) BIL_MEASURE5
,SUM(CASE WHEN cal.report_date =:l_prev_date
THEN sumry.new_opty_amt'||l_currency_suffix||' ELSE NULL END) BIL_MEASURE6
,SUM(CASE WHEN cal.report_date =:l_curr_as_of_date
THEN sumry.cnv_opty_amt'||l_currency_suffix||' ELSE NULL END) BIL_MEASURE7
,SUM(CASE WHEN cal.report_date =:l_prev_date
THEN sumry.cnv_opty_amt'||l_currency_suffix||' ELSE NULL END) BIL_MEASURE8
,SUM(CASE WHEN cal.report_date =:l_curr_as_of_date
THEN sumry.won_opty_amt'||l_currency_suffix||' ELSE NULL END) BIL_MEASURE9
,SUM(CASE WHEN cal.report_date =:l_prev_date
THEN sumry.won_opty_amt'||l_currency_suffix||' ELSE NULL END) BIL_MEASURE10
,SUM(CASE WHEN cal.report_date =:l_curr_as_of_date
THEN sumry.lost_opty_amt'||l_currency_suffix||' ELSE NULL END) BIL_MEASURE11
,SUM(CASE WHEN cal.report_date =:l_prev_date
THEN sumry.lost_opty_amt'||l_currency_suffix||' ELSE NULL END) BIL_MEASURE12
,SUM(CASE WHEN cal.report_date =:l_curr_as_of_date
THEN sumry.no_opty_amt'||l_currency_suffix||' ELSE NULL END) BIL_MEASURE13
,SUM(CASE WHEN cal.report_date =:l_prev_date
THEN sumry.no_opty_amt'||l_currency_suffix||' ELSE NULL END) BIL_MEASURE14
,DECODE(sumry.leaf_node_flag,''Y'', -1,1) BIL_MEASURE15';
SELECT VIEWBYID
,NULL BIL_MEASURE28
,NULL BIL_MEASURE2
,(CASE WHEN (new - (closed+cnv+dead))=0 THEN NULL ELSE (new - (closed+cnv+dead)) END) BIL_MEASURE3
,NULL BIL_MEASURE5
,NULL BIL_MEASURE6
,NULL BIL_MEASURE7
,NULL BIL_MEASURE8
,NULL BIL_MEASURE9
,NULL BIL_MEASURE10
,NULL BIL_MEASURE11
,NULL BIL_MEASURE12
,NULL BIL_MEASURE13
,NULL BIL_MEASURE14
,BIL_MEASURE15';
SELECT sumry.source_code_id VIEWBYID
,NVL(SUM(new_leads_cnt),0) new
,NVL(SUM(cnv_leads_cnt),0) cnv
,NVL(SUM(dead_leads_cnt),0) dead
,NVL(SUM(closed_leads_cnt),0) closed
,DECODE(sumry.leaf_node_flag,''Y'', -1,1) BIL_MEASURE15';
l_custom_sql :=l_inner_select ||' ( select * from ('|| l_custom_sql1 ||' )
where NOT (BIL_MEASURE28 IS NULL AND BIL_MEASURE2 IS NULL AND BIL_MEASURE3 IS NULL AND BIL_MEASURE5 IS NULL
AND BIL_MEASURE7 IS NULL AND BIL_MEASURE9 IS NULL AND BIL_MEASURE11 IS NULL AND BIL_MEASURE13 IS NULL ) )inner'||l_prodcat_from||
' WHERE '||l_prodcat_where||
' GROUP BY BIL_MEASURE15, VIEWBYID ';
l_custom_sql := l_inner_select ||' ('|| l_custom_sql1 ||' )
where NOT (BIL_MEASURE28 IS NULL AND BIL_MEASURE2 IS NULL AND BIL_MEASURE3 IS NULL AND BIL_MEASURE5 IS NULL
AND BIL_MEASURE7 IS NULL AND BIL_MEASURE9 IS NULL AND BIL_MEASURE11 IS NULL AND BIL_MEASURE13 IS NULL ) '||
' GROUP BY BIL_MEASURE15, VIEWBYID ';
l_outer_select :='SELECT * FROM ('||
'SELECT '||
'VIEWBY'||
',VIEWBYID'||
',BIL_MEASURE28 BIL_MEASURE2 '||
',BIL_MEASURE28 BIL_MEASURE31 '||
',BIL_MEASURE3 BIL_MEASURE3 '||
',BIL_MEASURE3 BIL_MEASURE32 '||
',BIL_MEASURE2 BIL_MEASURE4 '||
',BIL_MEASURE2 BIL_MEASURE33 '||
',BIL_MEASURE5 BIL_MEASURE6 '||
',(((BIL_MEASURE5 - BIL_MEASURE6) / ABS(DECODE(BIL_MEASURE6, 0, NULL, BIL_MEASURE6))) * 100) BIL_MEASURE7 '||
',BIL_MEASURE7 BIL_MEASURE8 '||
',(((BIL_MEASURE7 - BIL_MEASURE8) / ABS(DECODE(BIL_MEASURE8, 0, NULL, BIL_MEASURE8))) * 100) BIL_MEASURE9 '||
',BIL_MEASURE9 BIL_MEASURE10 '||
',BIL_MEASURE9 BIL_MEASURE12 '||
',BIL_MEASURE9 BIL_MEASURE34 '||
',(((BIL_MEASURE9 - BIL_MEASURE10) / ABS(DECODE(BIL_MEASURE10, 0, NULL, BIL_MEASURE10))) * 100) BIL_MEASURE13 '||
',BIL_MEASURE11 BIL_MEASURE14 '||
',BIL_MEASURE11 BIL_MEASURE35 '||
',(((BIL_MEASURE11 - BIL_MEASURE12) / ABS(DECODE(BIL_MEASURE12, 0, NULL, BIL_MEASURE12))) * 100) BIL_MEASURE15 '||
',BIL_MEASURE13 BIL_MEASURE16 '||
',(((BIL_MEASURE13 - BIL_MEASURE14) / ABS(DECODE(BIL_MEASURE14, 0, NULL, BIL_MEASURE14))) * 100) BIL_MEASURE17 '||
',SUM(BIL_MEASURE28) OVER() BIL_MEASURE18 '||
',SUM(BIL_MEASURE3) OVER() BIL_MEASURE19 '||
',SUM(BIL_MEASURE2) OVER() BIL_MEASURE20 '||
',SUM(BIL_MEASURE5) OVER() BIL_MEASURE21 '||
',(((( SUM(BIL_MEASURE5) OVER() ) - ( SUM(BIL_MEASURE6) OVER() )) / '||
'ABS(DECODE(SUM(BIL_MEASURE6) OVER(), 0, NULL, SUM(BIL_MEASURE6) OVER())) )) * 100 BIL_MEASURE22 '||
',(SUM(BIL_MEASURE7) OVER()) BIL_MEASURE23 '||
',(((( SUM(BIL_MEASURE7) OVER() ) - ( SUM(BIL_MEASURE8) OVER() )) / '||
'ABS(DECODE(SUM(BIL_MEASURE8) OVER(), 0, NULL, SUM(BIL_MEASURE8) OVER())) )) * 100 BIL_MEASURE24 '||
',(SUM(BIL_MEASURE9) OVER()) BIL_MEASURE25 '||
',(((( SUM(BIL_MEASURE9) OVER() ) - ( SUM(BIL_MEASURE10) OVER() )) / '||
'ABS(DECODE(SUM(BIL_MEASURE10) OVER(), 0, NULL, SUM(BIL_MEASURE10) OVER())) )) * 100 BIL_MEASURE26 '||
',(SUM(BIL_MEASURE11) OVER()) BIL_MEASURE27 '||
',(((( SUM(BIL_MEASURE11) OVER() ) - ( SUM(BIL_MEASURE12) OVER() )) / '||
'ABS(DECODE(SUM(BIL_MEASURE12) OVER(), 0, NULL, SUM(BIL_MEASURE12) OVER())) )) * 100 BIL_MEASURE28 '||
',(SUM(BIL_MEASURE13) OVER()) BIL_MEASURE29 '||
',(((( SUM(BIL_MEASURE13) OVER() ) - ( SUM(BIL_MEASURE14) OVER() )) / '||
'ABS(DECODE(SUM(BIL_MEASURE14) OVER(), 0, NULL, SUM(BIL_MEASURE14) OVER())) )) * 100 BIL_MEASURE30 '||
', (CASE WHEN VIEWBYID = -1 THEN NULL WHEN BIL_MEASURE15 = -1 THEN NULL '||
'ELSE '''||l_url_str||''' END) BIL_URL1 ' ;
l_inner_select1 :=
',VIEWBYID'||
',SUM(BIL_MEASURE28) BIL_MEASURE28'||
',SUM(BIL_MEASURE2) BIL_MEASURE2'||
',SUM(BIL_MEASURE3) BIL_MEASURE3'||
',SUM(BIL_MEASURE5) BIL_MEASURE5'||
',SUM(BIL_MEASURE6) BIL_MEASURE6'||
',SUM(BIL_MEASURE7) BIL_MEASURE7'||
',SUM(BIL_MEASURE8) BIL_MEASURE8'||
',SUM(BIL_MEASURE9) BIL_MEASURE9'||
',SUM(BIL_MEASURE10) BIL_MEASURE10'||
',SUM(BIL_MEASURE11) BIL_MEASURE11'||
',SUM(BIL_MEASURE12) BIL_MEASURE12'||
',SUM(BIL_MEASURE13) BIL_MEASURE13'||
',SUM(BIL_MEASURE14) BIL_MEASURE14'||
',BIL_MEASURE15'||
' FROM ('||l_custom_sql||') mv, ';
SELECT MEANING
INTO
l_null_camp
FROM
FND_LOOKUP_VALUES
WHERE
LOOKUP_TYPE = 'BIL_BI_LOOKUPS'
AND LOOKUP_CODE = 'UNASSIGN'
AND LANGUAGE = USERENV('LANG');
' SELECT '||
' camp.name VIEWBY, '
||' decode(mv.VIEWBYID, -1,2,1) SORTORDER '
|| l_inner_select1 ||
' bim_i_obj_name_mv camp '||
' where '||
' mv.VIEWBYID = camp.source_code_id '||
' and camp.language= USERENV(''LANG'')'||
' group by '||
' mv.VIEWBYID, '||
' camp.name, '||
' decode(mv.VIEWBYID, -1,2,1),'
||'BIL_MEASURE15'
||') '||
' GROUP BY '||
'VIEWBY,VIEWBYID,BIL_MEASURE15,SORTORDER ';
l_inner_select2 :=
' SELECT '||
'VIEWBY'||
',VIEWBYID'||
',SUM(BIL_MEASURE28) BIL_MEASURE28'||
',SUM(BIL_MEASURE2) BIL_MEASURE2'||
',SUM(BIL_MEASURE3) BIL_MEASURE3'||
',SUM(BIL_MEASURE5) BIL_MEASURE5'||
',SUM(BIL_MEASURE6) BIL_MEASURE6'||
',SUM(BIL_MEASURE7) BIL_MEASURE7'||
',SUM(BIL_MEASURE8) BIL_MEASURE8'||
',SUM(BIL_MEASURE9) BIL_MEASURE9'||
',SUM(BIL_MEASURE10) BIL_MEASURE10'||
',SUM(BIL_MEASURE11) BIL_MEASURE11'||
',SUM(BIL_MEASURE12) BIL_MEASURE12'||
',SUM(BIL_MEASURE13) BIL_MEASURE13'||
',SUM(BIL_MEASURE14) BIL_MEASURE14'||
',SORTORDER'||
',BIL_MEASURE15'||
' FROM '||l_outer_query1;
l_outer_select ||' FROM ( '||l_inner_select2 ||' ) ORDER BY SORTORDER,VIEWBY) ';
l_select VARCHAR2(4000);
l_select1 VARCHAR2(4000);
l_select3 VARCHAR2(4000);
l_select2 VARCHAR2(4000);
l_rank_select VARCHAR2(2000);
l_rep_select1 varchar2(4000);
l_rep_select2 varchar2(4000);
l_rep_select3 varchar2(4000);
/* Check for Period Type selected */
IF l_page_period_type = 'FII_TIME_WEEK' THEN
l_per_type := 'WEEK';
THEN l_rank_select := ' '||l_rank_pre||''||l_rep_suffix||''||l_per_type||'_RANK ' ;
THEN l_rank_select := ' ('||l_rep_suffix||''||l_per_type||'_LAST_RANK + 1 ) -
'||l_rank_pre||''||l_rep_suffix||''||l_per_type||'_RANK ';
WHEN 'TOP' THEN l_rank_select := ' '||l_rank_pre||''||l_rep_suffix||''||l_per_type||'_RANK ' ;
THEN l_rank_select := ' ('||l_rep_suffix||''||l_per_type||'_LAST_RANK + 1 ) -
'||l_rank_pre||''||l_rep_suffix||''||l_per_type||'_RANK ';
l_rank_select := 'NULL';
l_select1 := 'SELECT
BIL_MEASURE1,
(SELECT source_job_title FROM Jtf_rs_resource_extns WHERE resource_id = BIL_MEASURE3) BIL_MEASURE2,
(SELECT rstl.resource_name FROM jtf_rs_resource_extns_tl rstl
WHERE rstl.resource_id=BIL_MEASURE3 AND USERENV(''LANG'')=rstl.LANGUAGE) BIL_MEASURE3,
(SELECT group_name FROM jtf_rs_groups_tl grtl
WHERE BIL_MEASURE4=grtl.group_id AND USERENV(''LANG'')=grtl.LANGUAGE) BIL_MEASURE4,
BIL_MEASURE6,
BIL_MEASURE7,
BIL_MEASURE8,
BIL_MEASURE9,
BIL_MEASURE10,
BIL_MEASURE11,
BIL_MEASURE12,
BIL_MEASURE13,
BIL_MEASURE14,
BIL_MEASURE15,
BIL_MEASURE16,
BIL_MEASURE17,
BIL_MEASURE18,
BIL_MEASURE19,
BIL_MEASURE20,
BIL_MEASURE21,
BIL_MEASURE22,
BIL_MEASURE23,
BIL_MEASURE24,
BIL_URL1||'||'''BIL_DIMENSION1=WON'''||' BIL_URL1,
BIL_URL1||'||'''BIL_DIMENSION1=PIPELINE'''||' BIL_URL2
FROM
(
';
l_select3 := '
select * from(
select
(ROWNUM - 1) RN,
BIL_MEASURE1,
BIL_MEASURE2,
BIL_MEASURE3,
BIL_MEASURE4,
BIL_MEASURE6,
BIL_MEASURE7,
BIL_MEASURE8,
BIL_MEASURE9,
BIL_MEASURE10,
BIL_MEASURE11,
BIL_MEASURE12,
BIL_MEASURE13,
BIL_MEASURE14,
BIL_MEASURE15,
BIL_MEASURE16,
BIL_MEASURE17,
BIL_MEASURE18,
BIL_MEASURE19,
BIL_MEASURE20,
BIL_MEASURE21,
BIL_MEASURE22,
BIL_MEASURE23,
BIL_MEASURE24,
BIL_URL1
from
(
';
l_select2 := ' SELECT
'||l_rank_select||' BIL_MEASURE1 ,
NULL BIL_MEASURE2,
salesrep_id BIL_MEASURE3 ,
sales_group_id BIL_MEASURE4 ,
SUM(frcst_amt_'||l_per_type||''||l_curr_suffix||') bil_measure6,
SUM('||l_comp||''||l_per_type||'_frcst_amt'||l_curr_suffix||') bil_measure7,
(((SUM(frcst_amt_'||l_per_type||''||l_curr_suffix||') -
SUM('||l_comp||''||l_per_type||'_frcst_amt'||l_curr_suffix||'))/
DECODE(SUM('||l_comp||''||l_per_type||'_frcst_amt'||l_curr_suffix||'),0,NULL,
SUM('||l_comp||''||l_per_type||'_frcst_amt'||l_curr_suffix||'))) * 100) bil_measure8,
SUM( pipeline_amt_'||l_per_type||''||l_curr_suffix||') bil_measure9,
SUM('||l_comp||''||l_per_type||'_pip_amt'||l_curr_suffix||') bil_measure10,
(((SUM( pipeline_amt_'||l_per_type||''||l_curr_suffix||') -
SUM('||l_comp||''||l_per_type||'_pip_amt'||l_curr_suffix||'))/
DECODE(SUM('||l_comp||''||l_per_type||'_pip_amt'||l_curr_suffix||'),0,NULL,
SUM('||l_comp||''||l_per_type||'_pip_amt'||l_curr_suffix||'))) * 100) bil_measure11,
SUM(won_amt_'||l_per_type||''||l_curr_suffix||') bil_measure12,
SUM('||l_comp||''||l_per_type||'_won_amt'||l_curr_suffix||') bil_measure13,
(((SUM(won_amt_'||l_per_type||''||l_curr_suffix||') -
SUM('||l_comp||''||l_per_type||'_won_amt'||l_curr_suffix||'))/
DECODE(SUM('||l_comp||''||l_per_type||'_won_amt'||l_curr_suffix||'),0,NULL,
SUM('||l_comp||''||l_per_type||'_won_amt'||l_curr_suffix||'))) * 100) bil_measure14,
SUM(winloss_ratio_'||l_per_type||''||l_curr_suffix||') bil_measure15,
SUM('||l_comp||''||l_per_type||'_wlratio'||l_curr_suffix||') bil_measure16,
(SUM(winloss_ratio_'||l_per_type||''||l_curr_suffix||') -
SUM('||l_comp||''||l_per_type||'_wlratio'||l_curr_suffix||')) bil_measure17,
SUM(booked_amt_'||l_per_type||''||l_curr_suffix||') bil_measure18,
SUM('||l_comp||''||l_per_type||'_booked_amt'||l_curr_suffix||') bil_measure19 ,
(((SUM(booked_amt_'||l_per_type||''||l_curr_suffix||') -
SUM('||l_comp||''||l_per_type||'_booked_amt'||l_curr_suffix||'))/
DECODE(SUM('||l_comp||''||l_per_type||'_booked_amt'||l_curr_suffix||'),0,
NULL,SUM('||l_comp||''||l_per_type||'_booked_amt'||l_curr_suffix||'))) * 100) bil_measure20,
SUM(SUM(frcst_amt_'||l_per_type||''||l_curr_suffix||')) OVER() BIL_MEASURE22 ,
SUM(SUM(pipeline_amt_'||l_per_type||''||l_curr_suffix||')) OVER() BIL_MEASURE23,
SUM(SUM(won_amt_'||l_per_type||''||l_curr_suffix||')) OVER() BIL_MEASURE24,
SUM(SUM(booked_amt_'||l_per_type||''||l_curr_suffix||')) OVER() BIL_MEASURE21,
'''||l_drill_link||'''||MV.salesrep_id||'''||l_drill_link3||'''||''+''||'''||l_drill_link2||'''||MV.salesrep_id||
''.''||MV.sales_group_id||'''||l_drill_link1||'''||MV.sales_group_id||''&''||'''' BIL_URL1
FROM
BIL_BI_SLS_PERF_MV MV
'|| l_where_clause ||'
)
order by BIL_MEASURE1
)
where
RN >= &START_INDEX
AND RN <= &END_INDEX
)
'|| l_outer_where_clause ||'
'|| l_order_rank ||'
';
l_rep_select2 := '
(
SELECT
salesrep_id BIL_MEASURE3,
sales_group_id BIL_MEASURE4,
SUM(frcst) BIL_MEASURE6,
sum(priorFrcst) BIL_MEASURE7,
sum(pipeline) BIL_MEASURE9,
sum(priorPipeline) BIL_MEASURE10,
sum(won) BIL_MEASURE12,
sum(priorWon) BIL_MEASURE13,
sum(won) / DECODE(sum(lost), 0, NULL, sum(lost)) BIL_MEASURE15,
sum(priorWon) / DECODE(sum(priorLost), 0, NULL, sum(priorLost)) BIL_MEASURE16,
sum(booked) BIL_MEASURE18,
SUM(priorBooked) BIL_MEASURE19,
'''||l_drill_link||'''||salesrep_id||'''||l_drill_link3||'''||''+''||'''||l_drill_link2||'''||salesrep_id||
''.''||sales_group_id||'''||l_drill_link1||'''||sales_group_id||''&''||'''' BIL_URL1
from (
SELECT /*+ LEADING(cal) */
sumry.salesrep_id,
sumry.sales_group_id,
NULL AS frcst,
NULL AS priorFrcst,
NULL pipeline,
NULL priorPipeline,
(case
when cal.report_date = :l_asof_date then
sumry.won_opty_amt'||l_curr_suffix||'
else
NULL
end) AS won,
(case
when cal.report_date = :l_prev_date then
sumry.won_opty_amt'||l_curr_suffix||'
else
NULL
end) AS priorWon,
(case
when cal.report_date = :l_asof_date then
sumry.lost_opty_amt'||l_curr_suffix||'
else
NULL
end) AS lost,
(case
when cal.report_date = :l_prev_date then
sumry.lost_opty_amt'||l_curr_suffix||'
else
NULL
end) AS priorLost,
NULL booked,
NULL priorBooked
FROM FII_TIME_STRUCTURES cal, BIL_BI_OPTY_G_MV sumry
WHERE sumry.effective_time_id = cal.time_id
AND sumry.effective_period_type_id = cal.PERIOD_TYPE_ID
AND bitand(cal.record_type_id, :l_record_type_id) = :l_record_type_id
AND cal.report_date in (:l_asof_date, :l_prev_date)
AND sumry.parent_sales_group_id = :l_salesgroup_id
AND cal.xtd_flag = ''Y''
AND sumry.salesrep_id = :l_resource_id
AND sumry.sales_group_id = :l_salesgroup_id
AND sumry.won_opty_amt is not NULL
UNION ALL
SELECT /*+ leading (cal) */
sumry.salesrep_id,
sumry.sales_group_id,
NULL AS frcst,
NULL AS priorFrcst,
(case
when sumry.snap_date = :l_snapshot_date then
decode(:l_period_type,
128,
PIPELINE_AMT_YEAR'||l_curr_suffix||',
64,
PIPELINE_AMT_QUARTER'||l_curr_suffix||',
32,
PIPELINE_AMT_PERIOD'||l_curr_suffix||',
16,
PIPELINE_AMT_WEEK'||l_curr_suffix||')
end) AS pipeline,
(CASE
WHEN sumry.snap_date = :l_snapshot_date THEN
'|| l_prev_amt ||'
ELSE
NULL
END) AS priorPipeline,
NULL AS won,
NULL AS priorWon,
NULL AS lost,
NULL AS prorLost,
NULL booked,
NULL priorBooked
FROM
'|| l_open_mv_new ||' sumry
WHERE sumry.snap_date in (:l_snapshot_date)
AND sumry.grp_total_flag = 1
AND sumry.parent_sales_group_id = :l_salesgroup_id
AND sumry.salesrep_id = :l_resource_id
AND sumry.sales_group_id = :l_salesgroup_id
UNION ALL
';
l_rep_select3 := '
SELECT /*+ LEADING(cal) */
sumry.salesrep_id,
sumry.sales_group_id,
(case
when sumry.effective_time_id = :l_curr_page_time_id AND
cal.report_date = :l_asof_date then
sumry.forecast_amt'||l_curr_suffix||'
else
NULL
end) AS frcst,
(case
when sumry.effective_time_id = :l_prev_page_time_id AND
cal.report_date = :l_prev_date then
sumry.forecast_amt'||l_curr_suffix||'
else
NULL
end) AS priorFrcst,
NULL AS pipeline,
NULL AS priorPipeline,
NULL AS won,
NULL AS priorWon,
NULL AS lost,
NULL AS prorLost,
NULL booked,
NULL priorBooked
FROM FII_TIME_STRUCTURES cal, BIL_BI_FST_G_MV sumry
WHERE sumry.TXN_TIME_ID = cal.TIME_ID
AND sumry.TXN_PERIOD_TYPE_ID = cal.PERIOD_TYPE_ID
AND bitand(cal.record_type_id, :l_bitand_id) = :l_bitand_id
AND sumry.EFFECTIVE_PERIOD_TYPE_ID = :l_period_type
AND NVL(sumry.credit_type_id, :l_fst_crdt_type) = :l_fst_crdt_type
AND cal.report_date in (:l_asof_date, :l_prev_date)
AND sumry.effective_time_id in
(:l_curr_page_time_id, :l_prev_page_time_id)
AND sumry.parent_sales_group_id = :l_salesgroup_id
AND cal.xtd_flag = ''Y''
AND sumry.forecast_amt is not NULL
AND sumry.salesrep_id = :l_resource_id
AND sumry.sales_group_id = :l_salesgroup_id
UNION ALL
SELECT /*+ leading (cal) */
resource_id salesrep_id,
sales_grp_id sales_group_id,
NULL AS frcst,
NULL AS priorFrcst,
NULL AS pipeline,
NULL AS priorPipeline,
NULL AS won,
NULL AS priorWon,
NULL AS lost,
NULL AS priorLost,
(CASE
WHEN cal.report_date = :l_asof_date THEN
(sumry.net_booked_amt_g'||l_booked_suffix||')
ELSE
NULL
END) As booked,
(CASE
WHEN cal.report_date = :l_prev_date THEN
(sumry.net_booked_amt_g'||l_booked_suffix||')
ELSE
NULL
END) AS priorBooked
FROM FII_TIME_STRUCTURES cal, isc_dbi_scr_001_mv sumry
WHERE sumry.time_id = cal.time_id
AND sumry.period_type_id = cal.period_type_id
AND cal.xtd_flag = ''Y''
AND cal.report_date in (:l_asof_date, :l_prev_date)
AND bitand(cal.record_type_id, :l_record_type_id) = :l_record_type_id
AND sumry.GRP_MARKER = ''SALES REP''
AND sumry.resource_id = :l_resource_id
AND sumry.customer_flag = 0
AND sumry.item_cat_flag = 0
AND sumry.net_booked_amt_g is not NULL
AND sumry.sales_grp_id = :l_salesgroup_id
AND sumry.parent_grp_id = :l_salesgroup_id)
GROUP BY salesrep_id, sales_group_id
)
';
l_rep_select1 := '
SELECT
NULL BIL_MEASURE1,
(SELECT source_job_title FROM Jtf_rs_resource_extns WHERE resource_id = BIL_MEASURE3) BIL_MEASURE2,
(SELECT rstl.resource_name FROM jtf_rs_resource_extns_tl rstl
WHERE rstl.resource_id=BIL_MEASURE3 AND USERENV(''LANG'')=rstl.LANGUAGE) BIL_MEASURE3,
(SELECT group_name FROM jtf_rs_groups_tl grtl
WHERE BIL_MEASURE4=grtl.group_id AND USERENV(''LANG'')=grtl.LANGUAGE) BIL_MEASURE4,
BIL_MEASURE6,
BIL_MEASURE7,
(((BIL_MEASURE6 - BIL_MEASURE7) / (DECODE(BIL_MEASURE7, 0, NULL, BIL_MEASURE7))) * 100) BIL_MEASURE8,
BIL_MEASURE9,
BIL_MEASURE10,
(((BIL_MEASURE9 - BIL_MEASURE10) / (DECODE(BIL_MEASURE10, 0, NULL, BIL_MEASURE10))) * 100) BIL_MEASURE11,
BIL_MEASURE12,
BIL_MEASURE13,
(((BIL_MEASURE12 - BIL_MEASURE13) / (DECODE(BIL_MEASURE13, 0, NULL, BIL_MEASURE13))) * 100) BIL_MEASURE14,
BIL_MEASURE15,
BIL_MEASURE16,
(BIL_MEASURE15 - BIL_MEASURE16) BIL_MEASURE17,
BIL_MEASURE18,
BIL_MEASURE19,
(((BIL_MEASURE18 - BIL_MEASURE19) / (DECODE(BIL_MEASURE19, 0, NULL, BIL_MEASURE19))) * 100) BIL_MEASURE20,
SUM(BIL_MEASURE18) OVER() BIL_MEASURE21 ,
SUM(BIL_MEASURE6) OVER() BIL_MEASURE22 ,
SUM(BIL_MEASURE9) OVER() BIL_MEASURE23 ,
SUM(BIL_MEASURE12) OVER() BIL_MEASURE24 ,
BIL_URL1||'||'''BIL_DIMENSION1=WON'''||' BIL_URL1,
BIL_URL1||'||'''BIL_DIMENSION1=PIPELINE'''||' BIL_URL2
FROM
';
x_custom_sql := l_select1 ||
l_select3 ||
l_select2 ;
x_custom_sql := l_rep_select1 ||
l_rep_select2 ||
l_rep_select3 ;