[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_conv_rate_selected VARCHAR2(50);
l_outer_select VARCHAR2(3000);
l_inter_select VARCHAR2(5000);
l_inner_select VARCHAR2(15000);
l_insert_stmnt VARCHAR2(5000);
l_pc_select VARCHAR2(5000);
l_outer_select1 VARCHAR2(500);
l_outer_select2 VARCHAR2(500);
l_pipe_select1 varchar2(4000);
l_pipe_select2 varchar2(4000);
l_pipe_select3 varchar2(4000);
l_pipe_select4 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
,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_calendar_id => '|| l_calendar_id ||',' ||
'l_prodcat => '|| l_prodcat;
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_select1 := l_outer_select1 || ',BIL_MEASURE2_B'||i;
l_outer_select2 := l_outer_select2 || ',SUM(BIL_MEASURE2_B' ||i|| ') OVER() BIL_MEASURE12_B'||i;
l_outer_select := l_outer_select ||',BIL_MEASURE22 '||
',BIL_MEASURE23 '||
',(BIL_MEASURE22 - BIL_MEASURE23) / '||
' ABS(DECODE(BIL_MEASURE23,0,NULL,BIL_MEASURE23)) * 100 BIL_MEASURE24 '||
',BIL_MEASURE2_B1 '|| l_outer_select1;
l_outer_select := l_outer_select || ',BIL_MEASURE7 '||
',BIL_MEASURE8 '||
',SUM(BIL_MEASURE2_B1) OVER() BIL_MEASURE12_B1 '|| l_outer_select2;
l_outer_select := l_outer_select ||
',SUM(BIL_MEASURE7) OVER() BIL_MEASURE17 '||
',SUM(BIL_MEASURE8) OVER() BIL_MEASURE18 '||
',BIL_MEASURE22 BIL_MEASURE20 '||
',(BIL_MEASURE7-BIL_MEASURE8)/ '||
'ABS(DECODE(BIL_MEASURE8,0,NULL,BIL_MEASURE8)) * 100 BIL_MEASURE25 '||
',SUM(BIL_MEASURE22) OVER() BIL_MEASURE34 '||
',SUM(BIL_MEASURE22) OVER() BIL_MEASURE26 '||
',SUM(BIL_MEASURE23) OVER() BIL_MEASURE27 '||
',(SUM(BIL_MEASURE22) OVER() - SUM(BIL_MEASURE23) OVER()) / '||
'ABS(DECODE(SUM(BIL_MEASURE23) OVER(), 0, NULL, '||
'SUM(BIL_MEASURE23) OVER())) * 100 BIL_MEASURE28 '||
',(SUM(BIL_MEASURE7) OVER()- SUM(BIL_MEASURE8) OVER()) / '||
'ABS(DECODE(SUM(BIL_MEASURE8) OVER(), 0, NULL, '||
'SUM(BIL_MEASURE8) OVER())) * 100 BIL_MEASURE29 '||
',BIL_MEASURE7 BIL_MEASURE32 '||
',SUM(BIL_MEASURE7) OVER() BIL_MEASURE33 '||
',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=PIPELINE'''||'),
DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=PIPELINE'''||')),
NULL) BIL_URL3 ';
l_inner_select := 'SORT_ORDER '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
' THEN '|| l_pipe ||
' ELSE NULL '||
'END) BIL_MEASURE22 '||
',SUM(CASE WHEN sumry.snap_date = :l_prev_date '||
' THEN '|| l_pipe ||
' ELSE NULL '||
' END) BIL_MEASURE23 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_pb1 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B1 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_pb2 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B2 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_pb3 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B3 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_pb4 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B4 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_pb5 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B5 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_pb6 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B6 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_pb7 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B7 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_pb8 ||
'ELSE NULL '||
'END) BIL_MEASURE2_B8 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_pb9 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B9 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_pb10 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B10 '||
',SUM(CASE WHEN sumry.snap_date = :l_snap_date '||
'THEN '|| l_wtd_pipe
||'
ELSE NULL '||
'END) BIL_MEASURE7 '||
', SUM(CASE WHEN sumry.snap_date = :l_prev_date '||
'THEN '|| l_wtd_pipe ||'
ELSE NULL '||
'END) BIL_MEASURE8 ';
l_pipe_select1 := 'SORT_ORDER '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
' THEN '|| l_pipe ||
' ELSE NULL '||
'END) BIL_MEASURE22 ';
l_pipe_select2 := ',SUM(CASE WHEN sumry.snap_date = :l_prev_snap_date '||
' THEN '|| l_pipe ||
' ELSE NULL '||
' END) BIL_MEASURE23 ';
l_pipe_select2 := ',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
' THEN '|| l_pipe_amt ||
' ELSE NULL '||
' END) BIL_MEASURE23 ';
l_pipe_select3 := ',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_pb1 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B1 '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_pb2 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B2 '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_pb3 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B3 '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_pb4 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B4 '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_pb5 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B5 '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_pb6 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B6 '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_pb7 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B7 '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_pb8 ||
'ELSE NULL '||
'END) BIL_MEASURE2_B8 '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_pb9 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B9 '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_pb10 ||
' ELSE NULL '||
'END) BIL_MEASURE2_B10 '||
',SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_wtd_pipe
||'
ELSE NULL '||
'END) BIL_MEASURE7 ';
l_pipe_select4 := ', SUM(CASE WHEN sumry.snap_date = :l_prev_snap_date '||
'THEN '|| l_wtd_pipe ||'
ELSE NULL '||
'END) BIL_MEASURE8 ';
l_pipe_select4 := ', SUM(CASE WHEN sumry.snap_date = :l_snapshot_date '||
'THEN '|| l_wt_pipe_amt ||'
ELSE NULL '||
'END) BIL_MEASURE8 ';
l_pipe_select4 := ', SUM(CASE WHEN sumry.snap_date = :l_prev_snap_date '||
'THEN '|| l_wt_pipe_amt ||'
ELSE NULL '||
'END) BIL_MEASURE8 ';
l_inner_select := l_pipe_select1 ||
l_pipe_select2 ||
l_pipe_select3 ||
l_pipe_select4 ;
l_outer_select := l_outer_select ||
'
FROM ( SELECT NVL(restl.resource_name,grptl.group_name) VIEWBY
,(CASE WHEN restl.resource_id IS NULL THEN 1 ELSE 2 END) SORT_ORDER
,BIL_MEASURE22,BIL_MEASURE23,BIL_MEASURE2_B1,BIL_MEASURE2_B2,BIL_MEASURE2_B3
,BIL_MEASURE2_B4,BIL_MEASURE2_B5,BIL_MEASURE2_B6,BIL_MEASURE2_B7,BIL_MEASURE2_B8
,BIL_MEASURE2_B9,BIL_MEASURE2_B10,BIL_MEASURE7, BIL_MEASURE8
,(CASE WHEN restl.resource_id IS NULL THEN grptl.group_id ELSE restl.resource_id END) VIEWBYID
,(CASE WHEN restl.resource_id IS NULL THEN ''' || l_url ||''' ' || ' ELSE NULL END) BIL_URL1
,DECODE(restl.resource_id, NULL, NULL,''' ||l_drill_link||''') BIL_URL2
';
l_inner_select := REPLACE(l_inner_select, 'SORT_ORDER', 'sumry.sales_group_id, sumry.salesrep_id');
l_custom_sql := 'SELECT /*+ NO_MERGE */ '
||l_inner_select||
' FROM '||l_sumry||' sumry '||
l_pipe_denorm||
' WHERE sumry.parent_sales_group_id = :l_sg_id_num '||
l_inner_where_clause || l_pipe_product_where_clause ||
' GROUP BY sumry.sales_group_id, sumry.salesrep_id ';
x_custom_sql := 'SELECT * FROM ( '||l_outer_select ||
' FROM ('||l_custom_sql||') '||
'sumry, jtf_rs_groups_tl grptl ,jtf_rs_resource_extns_tl restl
WHERE grptl.group_id = sumry.sales_group_id
AND grptl.language = USERENV(''LANG'')
AND restl.language(+) = USERENV(''LANG'')
AND restl.resource_id(+) = sumry.salesrep_id
)
ORDER BY SORT_ORDER,VIEWBY)'|| l_null_rem_clause;
l_custom_sql := 'SELECT restl.resource_name VIEWBY '||
',2 ' ||l_inner_select||
',restl.resource_id VIEWBYID '||
',NULL BIL_URL1 '||
',DECODE(restl.resource_id, NULL, NULL,'''||l_drill_link||''') BIL_URL2 '||
' FROM '||l_sumry||' sumry '||
l_pipe_denorm||
',jtf_rs_resource_extns_tl restl '||
' WHERE sumry.parent_sales_group_id = :l_sg_id_num '||
' AND restl.language = USERENV(''LANG'') '||
' AND restl.resource_id = :l_resource_id '||
' AND restl.resource_id = sumry.salesrep_id '||
l_inner_where_clause || l_pipe_product_where_clause ||
' GROUP BY restl.resource_id, restl.resource_name '||
' ,DECODE(restl.resource_id, NULL, NULL,'''||l_drill_link||''') ';
x_custom_sql := 'SELECT * FROM ( '||l_outer_select ||
' FROM ('||l_custom_sql||') '||
'ORDER BY SORT_ORDER,VIEWBY)'|| l_null_rem_clause;
l_custom_sql := 'SELECT NULL VIEWBY '||
', 1 '||l_inner_select||
',pcd.parent_id VIEWBYID '||
',NULL BIL_URL1 '||
',NULL BIL_URL2 '||
'FROM '||l_sumry||' sumry'||
l_pipe_denorm||
' WHERE sumry.sales_group_id = :l_sg_id_num '||
l_parent_sls_grp_where_clause ||
l_inner_where_clause ||
l_pipe_product_where_clause;
l_pc_select := ' SELECT
decode(sumry.viewbyid, -1,:l_unassigned_value,
mtl.DESCRIPTION || '' ('' || mtl.CATEGORY_CONCAT_SEGS ||'')'') VIEWBY
,SORT_ORDER
,SUM(BIL_MEASURE22) BIL_MEASURE22
,SUM(BIL_MEASURE23) BIL_MEASURE23
,SUM(BIL_MEASURE2_B1) BIL_MEASURE2_B1
,SUM(BIL_MEASURE2_B2) BIL_MEASURE2_B2
,SUM(BIL_MEASURE2_B3) BIL_MEASURE2_B3
,SUM(BIL_MEASURE2_B4) BIL_MEASURE2_B4
,SUM(BIL_MEASURE2_B5) BIL_MEASURE2_B5
,SUM(BIL_MEASURE2_B6) BIL_MEASURE2_B6
,SUM(BIL_MEASURE2_B7) BIL_MEASURE2_B7
,SUM(BIL_MEASURE2_B8) BIL_MEASURE2_B8
,SUM(BIL_MEASURE2_B9) BIL_MEASURE2_B9
,SUM(BIL_MEASURE2_B10) BIL_MEASURE2_B10
,SUM(BIL_MEASURE7) BIL_MEASURE7
,SUM(BIL_MEASURE8) BIL_MEASURE8
,VIEWBYID
,'''||l_drill_link||''' BIL_URL1,'||
' DECODE(sumry.viewbyid,''-1'',NULL, '''||l_url||''' '||
' ) BIL_URL2 ';
l_custom_sql := l_pc_select||
' FROM ('||l_custom_sql||
') sumry, mtl_categories_v mtl '||
' WHERE mtl.category_id (+) = sumry.viewbyid '||
' GROUP BY SORT_ORDER,
decode(sumry.viewbyid, -1,:l_unassigned_value,
mtl.DESCRIPTION || '' ('' || mtl.CATEGORY_CONCAT_SEGS ||'')''),
VIEWBYID, BIL_URL1, BIL_URL2 ';
MESSAGE => ' Length => '|| LENGTH('SELECT * FROM ( '||l_outer_select ||
' FROM ('||l_custom_sql||') '||
' ORDER BY SORT_ORDER,UPPER(VIEWBY))'|| l_null_rem_clause));
x_custom_sql := 'SELECT * FROM ( '||l_outer_select ||
' FROM ('||l_custom_sql||') '||
' ORDER BY SORT_ORDER,UPPER(VIEWBY))'|| l_null_rem_clause;
ELSE -- Product category selected
l_cat_assign := bil_bi_util_pkg.getLookupMeaning(p_lookuptype => 'BIL_BI_LOOKUPS'
,p_lookupcode => 'ASSIGN_CATEG');
l_inter_select := ' SELECT VIEWBY '||
',SORT_ORDER '||
',SUM(BIL_MEASURE22) BIL_MEASURE22 '||
',SUM(BIL_MEASURE23) BIL_MEASURE23 '||
',SUM(BIL_MEASURE2_B1) BIL_MEASURE2_B1 '||
',SUM(BIL_MEASURE2_B2) BIL_MEASURE2_B2 '||
',SUM(BIL_MEASURE2_B3) BIL_MEASURE2_B3 '||
',SUM(BIL_MEASURE2_B4) BIL_MEASURE2_B4 '||
',SUM(BIL_MEASURE2_B5) BIL_MEASURE2_B5 '||
',SUM(BIL_MEASURE2_B6) BIL_MEASURE2_B6 '||
',SUM(BIL_MEASURE2_B7) BIL_MEASURE2_B7 '||
',SUM(BIL_MEASURE2_B8) BIL_MEASURE2_B8 '||
',SUM(BIL_MEASURE2_B9) BIL_MEASURE2_B9 '||
',SUM(BIL_MEASURE2_B10) BIL_MEASURE2_B10 '||
',SUM(BIL_MEASURE7) BIL_MEASURE7 '||
',SUM(BIL_MEASURE8) BIL_MEASURE8 '||
',VIEWBYID '||
', BIL_URL1 '||
',BIL_URL2 ';
l_custom_sql := l_inter_select ||
'FROM '||
'(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) '||
' '||l_inner_select||
',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_url||''') '||
' BIL_URL2 '||
'FROM '||l_sumry||' sumry '||
l_pipe_denorm||
' WHERE sumry.sales_group_id = :l_sg_id_num '||
l_parent_sls_grp_where_clause||
l_pipe_product_where_clause ||
l_inner_where_clause;
x_custom_sql := 'SELECT * FROM ( '||l_outer_select ||
' FROM ('||l_custom_sql||') '||
'ORDER BY SORT_ORDER,VIEWBY)'|| l_null_rem_clause;
l_custom_rec.attribute_name := ':l_conv_rate_selected';
l_custom_rec.attribute_value := l_conv_rate_selected;
l_conv_rate_selected VARCHAR2(50);
l_outer_select VARCHAR2(8000);
l_others_select VARCHAR2(8000);
l_open_select VARCHAR2(8000);
l_inner_select VARCHAR2(8000);
l_pc_inner_select VARCHAR2(8000);
l_insert_stmt VARCHAR2(2000);
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_num => '|| l_sg_id ||', ' ||
'l_parent_sales_group_id => '|| l_parent_sales_group_id||', ' ||
'l_calendar_id => '|| l_calendar_id ||', '||
'l_record_type_id => '||l_record_type_id;
'SELECT end_date FROM FII_TIME_WEEK WHERE :l_start_date BETWEEN start_date AND end_date '
INTO l_start_date_new USING l_start_date ;
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 ||
',NVL(BIL_MEASURE28,0) BIL_MEASURE1
,NVL(BIL_MEASURE2,0) BIL_MEASURE2
,NVL(BIL_MEASURE3,0) BIL_MEASURE3
,NVL(BIL_MEASURE4,0) BIL_MEASURE4
,NVL(BIL_MEASURE5,0) BIL_MEASURE5
,NVL(BIL_MEASURE6,0) BIL_MEASURE6
,(NVL(BIL_MEASURE6,0) - ((NVL(BIL_MEASURE28,0) + NVL(BIL_MEASURE2,0))
- (NVL(BIL_MEASURE3,0) + NVL(BIL_MEASURE4,0) + NVL(BIL_MEASURE5,0)))) BIL_MEASURE7
,SUM(NVL(BIL_MEASURE28,0)) OVER() BIL_MEASURE8
,SUM(NVL(BIL_MEASURE2,0)) OVER() BIL_MEASURE9
,SUM(NVL(BIL_MEASURE3,0)) OVER() BIL_MEASURE10
,SUM(NVL(BIL_MEASURE4,0)) OVER() BIL_MEASURE11
,SUM(NVL(BIL_MEASURE5,0)) OVER() BIL_MEASURE12
,SUM(NVL(BIL_MEASURE6,0)) OVER() BIL_MEASURE13
,(SUM(NVL(BIL_MEASURE6,0)) OVER() - ((SUM(NVL(BIL_MEASURE28,0)) OVER()
+ SUM(NVL(BIL_MEASURE2,0)) OVER()) - (SUM(NVL(BIL_MEASURE3,0)) OVER()
+ SUM(NVL(BIL_MEASURE4,0)) OVER()
+ SUM(NVL(BIL_MEASURE5,0)) OVER()))) BIL_MEASURE14
,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_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_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_others_select := ' SORT_ORDER
,NULL BIL_MEASURE28
,(DECODE(:l_period_type,16,sumry.nfp_wk'||l_currency_suffix||',32,sumry.nfp_per'||l_currency_suffix||',64,sumry.nfp_qtr'||l_currency_suffix||'
,128,sumry.nfp_yr'||l_currency_suffix||')) BIL_MEASURE2
,(sumry.won_opty_amt'||l_currency_suffix||') BIL_MEASURE3
,(sumry.lost_opty_amt'||l_currency_suffix||') BIL_MEASURE4
,(sumry.no_opty_amt'||l_currency_suffix||') BIL_MEASURE5
,NULL BIL_MEASURE6 ';
l_open_select := ' SORT_ORDER ';
l_open_select := l_open_select ||
',(CASE WHEN sumry.snap_date = :l_start_date THEN
DECODE(:l_period_type,
16,sumry.open_amt_week'||l_currency_suffix||',
32,sumry.open_amt_period'||l_currency_suffix||',
64,sumry.open_amt_quarter'||l_currency_suffix||',
128,sumry.open_amt_year'||l_currency_suffix||'
)
ELSE NULL
END) BIL_MEASURE28 ';
/* l_open_select := l_open_select ||
',(CASE WHEN sumry.snap_date = :l_start_date THEN
DECODE(:l_period_type,16,sumry.open_amt_week'||l_currency_suffix||',
32,sumry.open_amt_period'||l_currency_suffix||',
64,sumry.open_amt_quarter'||l_currency_suffix||'
,128,sumry.open_amt_year'||l_currency_suffix||')
ELSE NULL
END) BIL_MEASURE28 ';
l_open_select := l_open_select || ' ,NULL BIL_MEASURE2,NULL BIL_MEASURE3,NULL BIL_MEASURE4,NULL BIL_MEASURE5 ';
/* l_open_select := l_open_select ||
',(CASE WHEN sumry.snap_date = :l_snap_date THEN
DECODE(:l_period_type,16,sumry.open_amt_week'||l_currency_suffix||',32,sumry.open_amt_period'||l_currency_suffix||',64,sumry.open_amt_quarter'||l_currency_suffix||'
,128,sumry.open_amt_year'||l_currency_suffix||')
ELSE NULL
END) BIL_MEASURE6 ';
l_open_select := l_open_select ||
',(CASE WHEN sumry.snap_date = :l_snapshot_date THEN
DECODE(:l_period_type,
16,sumry.open_amt_week'||l_currency_suffix||',
32,sumry.open_amt_period'||l_currency_suffix||',
64,sumry.open_amt_quarter'||l_currency_suffix||',
128,sumry.open_amt_year'||l_currency_suffix||'
)
ELSE NULL
END) BIL_MEASURE6 ';
l_insert_stmt := ' INSERT INTO BIL_BI_RPT_TMP1(VIEWBY,SORTORDER, BIL_MEASURE28, BIL_MEASURE2,
BIL_MEASURE3,BIL_MEASURE4,BIL_MEASURE5,BIL_MEASURE6, VIEWBYID, BIL_URL1,BIL_URL2) ';
l_custom_sql := 'SELECT /*+ LEADING(cal) */ '||
l_pc_sel||
' sumry.sales_group_id Group_id, sumry.salesrep_id Rep_id, 1 '||
l_others_select ||
' FROM '||l_fii_struct ||' cal, '||
l_others_mv ||' sumry '||
' WHERE '|| l_xtd_where_clause ||
' AND sumry.parent_sales_group_id = :l_sg_id_num '||
' UNION ALL '||
' SELECT '|| l_pc_sel||
' sumry.sales_group_id Group_id, sumry.salesrep_id Rep_id,1 '||
l_open_select ||
' FROM '|| l_open_mv ||' sumry '||
' WHERE '|| l_open_where_clause ||
' AND sumry.parent_sales_group_id = :l_sg_id_num '|| l_product_where_op ||' ';
l_custom_sql := ' SELECT SUM(BIL_MEASURE28) BIL_MEASURE28,SUM(BIL_MEASURE2) BIL_MEASURE2 '||
',SUM(BIL_MEASURE3) BIL_MEASURE3,SUM(BIL_MEASURE4) BIL_MEASURE4 '||
',SUM(BIL_MEASURE5) BIL_MEASURE5,SUM(BIL_MEASURE6) BIL_MEASURE6 '||
',Group_id, Rep_id '||
' FROM ('||l_custom_sql||') sumry '||l_pipe_denorm||' '||
' WHERE 1=1 '||l_pipe_product_where_clause||' '||
' GROUP BY Group_id, Rep_id ';
l_custom_sql := 'SELECT /*+ NO_MERGE(inn) */ BIL_MEASURE28,BIL_MEASURE2,'||
'BIL_MEASURE3,BIL_MEASURE4,'||
'BIL_MEASURE5,BIL_MEASURE6 '||
',DECODE(restl.resource_id,NULL,grptl.group_name,restl.resource_name) VIEWBY '||
',NVL(restl.resource_id,grptl.group_id) VIEWBYID '||
',DECODE(restl.resource_id,NULL,1,2) SORTORDER '||
',DECODE(restl.resource_id,NULL,'''|| l_url ||''') BIL_URL1 '||
',DECODE(inn.rep_id, NULL, NULL,'''||l_drill_link||''') BIL_URL2 '||
'FROM ( '|| l_custom_sql ||' ) inn '||
',jtf_rs_groups_tl grptl '||
',jtf_rs_resource_extns_tl restl '||
'WHERE grptl.group_id = inn.group_id '||
'AND restl.resource_id(+) = inn.rep_id '||
'AND restl.language(+) = USERENV(''LANG'') '||
'AND grptl.language = USERENV(''LANG'') ';
x_custom_sql := l_outer_select ||' FROM ('|| l_custom_sql ||') '|| l_null_rem_clause ||
' ORDER BY SORTORDER ,UPPER(VIEWBY) ';
l_custom_sql := ' SELECT /*+ LEADING(cal) */ '||
l_pc_sel||
' sumry.salesrep_id Rep_id, 1 ' ||
l_others_select||
' FROM '||l_fii_struct ||' cal, '||
l_others_mv ||' sumry '||
' WHERE '|| l_xtd_where_clause ||
' AND sumry.parent_sales_group_id = :l_sg_id_num '||
' AND sumry.salesrep_id = :l_resource_id '||
' AND cal.xtd_flag = :l_yes '||
' UNION ALL '||
' SELECT '||l_pc_sel||
' sumry.salesrep_id Rep_id,1 ' ||
l_open_select ||
' FROM '|| l_open_mv ||' sumry '||
' WHERE '|| l_open_where_clause ||
'AND sumry.parent_sales_group_id = :l_sg_id_num '||l_product_where_op ||
'AND sumry.salesrep_id = :l_resource_id ';
l_custom_sql := ' SELECT SUM(BIL_MEASURE28) BIL_MEASURE28,SUM(BIL_MEASURE2) BIL_MEASURE2 '||
',SUM(BIL_MEASURE3) BIL_MEASURE3,SUM(BIL_MEASURE4) BIL_MEASURE4 '||
',SUM(BIL_MEASURE5) BIL_MEASURE5,SUM(BIL_MEASURE6) BIL_MEASURE6 '||
', Rep_id,DECODE(rep_id, NULL, NULL,'''||l_drill_link||''') BIL_URL2 '||
' FROM ('||l_custom_sql||') sumry '||l_pipe_denorm||' '||
' WHERE 1=1 '||l_pipe_product_where_clause||' '||
' GROUP BY Rep_id ';
l_custom_sql := 'SELECT restl.resource_name VIEWBY,restl.resource_id VIEWBYID '||
',SUM(BIL_MEASURE28) BIL_MEASURE28,SUM(BIL_MEASURE2) BIL_MEASURE2 '||
',SUM(BIL_MEASURE3) BIL_MEASURE3,SUM(BIL_MEASURE4) BIL_MEASURE4 '||
',SUM(BIL_MEASURE5) BIL_MEASURE5,SUM(BIL_MEASURE6) BIL_MEASURE6 '||
',NULL BIL_URL1, BIL_URL2 '||
'FROM ('|| l_custom_sql ||') inn, '||
'jtf_rs_resource_extns_tl restl '||
'WHERE restl.resource_id = inn.Rep_id '||
'AND restl.language = USERENV(''LANG'') '||
'GROUP BY restl.resource_id, restl.resource_name '||
' , bil_url2 ';
x_custom_sql := l_outer_select ||' FROM ('|| l_custom_sql ||') '|| l_null_rem_clause ||
' ORDER BY UPPER(VIEWBY) ';
l_inner_select := 'SELECT VIEWBY
,SORT_ORDER
,SUM(BIL_MEASURE28) BIL_MEASURE28
,SUM(BIL_MEASURE2) BIL_MEASURE2
,SUM(BIL_MEASURE3) BIL_MEASURE3
,SUM(BIL_MEASURE4) BIL_MEASURE4
,SUM(BIL_MEASURE5) BIL_MEASURE5
,SUM(BIL_MEASURE6) BIL_MEASURE6
,VIEWBYID
,BIL_URL1
,BIL_URL2 ';
l_pc_inner_select := 'SELECT
decode(sumry.viewbyid, -1,:l_unassigned_value,
mtl.DESCRIPTION || '' ('' || mtl.CATEGORY_CONCAT_SEGS ||'')'') VIEWBY
,SORT_ORDER
,VIEWBYID
,BIL_MEASURE28
,BIL_MEASURE2
,BIL_MEASURE3
,BIL_MEASURE4
,BIL_MEASURE5
,BIL_MEASURE6
, '''||l_drill_link||''' BIL_URL1
,DECODE(VIEWBYID,''-1'',NULL,'''||l_url||''') BIL_URL2 ';
x_custom_sql := l_outer_select ||
' FROM ('||l_inner_select||
' FROM ( '||
l_pc_inner_select||
' FROM ('||
'SELECT
null VIEWBY
,SORT_ORDER
,pcd.parent_id VIEWBYID
,BIL_MEASURE28
,BIL_MEASURE2
,BIL_MEASURE3
,BIL_MEASURE4
,BIL_MEASURE5
,BIL_MEASURE6
,NULL BIL_URL1
,NULL BIL_URL2
FROM ('||
' SELECT /*+ LEADING(cal) */ '||
'1 '||l_others_select||
',sumry.product_category_id product_category_id '||
'FROM ' ||l_fii_struct||' cal,'
||l_others_mv||' sumry'||
' WHERE '|| l_xtd_where_clause ||
' AND sumry.sales_group_id = :l_sg_id_num '||
l_parent_sls_grp_where_clause||
' AND cal.xtd_flag = :l_yes ';
SELECT 1 '||l_open_select||
',sumry.product_category_id product_category_id
FROM '|| l_open_mv ||' sumry'||
' WHERE '|| l_open_where_clause ||
' AND sumry.sales_group_id = :l_sg_id_num '||
l_parent_sls_grp_where_clause;
l_custom_sql := l_inner_select||
' FROM
(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) '||
l_others_select||
',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_url||''') BIL_URL2
FROM '||l_fii_struct||' cal, '
||l_others_mv||' sumry '||
l_pipe_denorm||
' WHERE cal.xtd_flag = :l_yes AND '||
' sumry.sales_group_id = :l_sg_id_num '||
l_parent_sls_grp_where_clause||
l_pipe_product_where_clause||' AND '||
l_xtd_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) '||
l_open_select||
',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_url||''') BIL_URL2
FROM '||l_open_mv||' sumry '||
l_pipe_denorm||
' WHERE sumry.sales_group_id = :l_sg_id_num '||
l_parent_sls_grp_where_clause||
l_pipe_product_where_clause ||' AND '||
l_open_where_clause;
l_insert_stmt := ' INSERT INTO BIL_BI_RPT_TMP1(VIEWBY,SORTORDER,BIL_MEASURE28, '||
'BIL_MEASURE2,BIL_MEASURE3,BIL_MEASURE4 '||
',BIL_MEASURE5,BIL_MEASURE6,VIEWBYID,BIL_URL1,BIL_URL2) ';
EXECUTE IMMEDIATE l_insert_stmt || l_custom_sql
USING l_cat_assign
,l_period_type
,l_yes
,l_sg_id_num
,l_prodcat
,l_record_type_id,l_record_type_id,l_curr_as_of_date
,l_cat_assign
,l_start_date,l_period_type
,l_snapshot_date,l_period_type
,l_sg_id_num
,l_prodcat
,l_snapshot_date,l_start_date;
EXECUTE IMMEDIATE l_insert_stmt || l_custom_sql
USING l_cat_assign
,l_period_type
,l_yes
,l_sg_id_num
,l_prodcat
,l_record_type_id,l_record_type_id,l_curr_as_of_date
,l_resource_id
,l_cat_assign
,l_start_date,l_period_type
,l_snapshot_date,l_period_type
,l_sg_id_num
,l_prodcat
,l_snapshot_date,l_start_date
,l_resource_id;
EXECUTE IMMEDIATE l_insert_stmt || l_custom_sql
USING l_cat_assign
,l_period_type
,l_yes
,l_sg_id_num
,l_parent_sales_group_id
,l_prodcat
,l_record_type_id,l_record_type_id,l_curr_as_of_date
,l_cat_assign
,l_start_date,l_period_type
,l_snapshot_date,l_period_type
,l_sg_id_num
,l_parent_sales_group_id
,l_prodcat
,l_snapshot_date,l_start_date;
EXECUTE IMMEDIATE l_insert_stmt || l_custom_sql
USING l_cat_assign
,l_period_type
,l_yes
,l_sg_id_num
,l_sg_id_num
,l_prodcat
,l_record_type_id,l_record_type_id,l_curr_as_of_date
,l_resource_id
,l_cat_assign
,l_start_date,l_period_type
,l_snapshot_date,l_period_type
,l_sg_id_num
,l_sg_id_num
,l_prodcat
,l_snapshot_date,l_start_date
,l_resource_id;
x_custom_sql := l_outer_select || ' FROM BIL_BI_RPT_TMP1 '||
l_null_rem_clause||' ORDER BY SORTORDER ,UPPER(VIEWBY) ';
l_select_stmt VARCHAR2(8000);
l_conv_rate_selected VARCHAR2(50);
l_err_desc := l_err_desc||' l_conversion_rate '||l_conv_rate_selected||' Curr date '||SYSDATE;
l_inner_sql := 'SELECT '||l_period_sel||' ,OPTY_NUMBER, (SELECT description FROM as_leads_all ld WHERE smry.opty_id = ld.lead_id) OPTY_NAME,'||
'(OPTY_AMT'||l_curr_suffix||') OPTY_AMT, '||
'WIN_PROBABILITY, OPTY_CLOSE_DATE, OPTY_STATUS, OPTY_STATUS_CODE, '||
'SUM(opty_amt'||l_curr_suffix||') OVER() GRAND_TOTAL, SMRY.SALES_GROUP_ID SALES_GROUP_ID, '||
'SMRY.Customer_id BIL_MEASURE4,SMRY.SALESREP_ID SALESREP_ID, '||
'SMRY.SALES_STAGE_ID SALES_STAGE_ID, '||l_url||' BIL_URL1 , NULL BIL_URL2 '||
'FROM BIL_BI_TOPOP_G_MV SMRY '||
'WHERE SMRY.PARENT_SALES_GROUP_ID = :l_sg_id_num '||
l_period_where;
l_inner_sql := ' SELECT BIL_MEASURE1,OPTY_NUMBER, OPTY_NAME, OPTY_AMT, '||
'WIN_PROBABILITY, OPTY_CLOSE_DATE, OPTY_STATUS, OPTY_STATUS_CODE, '||
'GRAND_TOTAL, SALES_GROUP_ID, '||
'BIL_MEASURE4,SALESREP_ID,SALES_STAGE_ID, BIL_URL1 , BIL_URL2 '||
'FROM ( '|| l_inner_sql||') WHERE BIL_MEASURE1 < 26 '||
' ORDER BY BIL_MEASURE1, OPTY_NUMBER ';
l_inner_sql := 'SELECT (ROWNUM-1) RN, BIL_MEASURE1,OPTY_NUMBER, OPTY_NAME, OPTY_AMT, '||
'WIN_PROBABILITY, OPTY_CLOSE_DATE, OPTY_STATUS, OPTY_STATUS_CODE, '||
'GRAND_TOTAL, SALES_GROUP_ID, '||
'BIL_MEASURE4,SALESREP_ID,SALES_STAGE_ID, BIL_URL1, BIL_URL2 '||
'FROM ( '|| l_inner_sql||')';
'SELECT '||
'BIL_MEASURE1,OPTY_NAME BIL_MEASURE3,OPTY_NUMBER BIL_MEASURE2,'||
'HZP.PARTY_NAME BIL_MEASURE4, RSTL.RESOURCE_NAME BIL_MEASURE5, GRPTL.GROUP_NAME BIL_MEASURE6,'||
'OPTY_AMT BIL_MEASURE7, WIN_PROBABILITY BIL_MEASURE8, '||
'STG.NAME BIL_MEASURE9, OPTY_CLOSE_DATE BIL_MEASURE10, STS.MEANING BIL_MEASURE11,'||
'GRAND_TOTAL BIL_MEASURE12, BIL_URL1, '||l_cust_url||' BIL_URL2 '||
'FROM '||
'(SELECT * FROM '||
'('||l_inner_sql||')' ||
' WHERE RN >= &START_INDEX AND RN <= &END_INDEX )IV'||
',JTF_RS_RESOURCE_EXTNS_TL RSTL '||
',JTF_RS_GROUPS_TL GRPTL '||
',AS_SALES_STAGES_ALL_TL STG '||
',HZ_PARTIES HZP '||
',AS_STATUSES_TL STS '||
'WHERE '||
'RSTL.LANGUAGE = USERENV(''LANG'') '||
'AND RSTL.RESOURCE_ID = IV.SALESREP_ID '||
'AND GRPTL.LANGUAGE = USERENV(''LANG'') '||
'AND GRPTL.GROUP_ID = IV.SALES_GROUP_ID '||
'AND STG.LANGUAGE(+) = USERENV(''LANG'') '||
'AND STG.SALES_STAGE_ID(+) = IV.SALES_STAGE_ID '||
'AND STS.STATUS_CODE = IV.OPTY_STATUS_CODE '||
'AND STS.LANGUAGE = USERENV(''LANG'') '||
'AND HZP.PARTY_ID = IV.BIL_MEASURE4 '||
' ORDER BY BIL_MEASURE1, OPTY_NUMBER ' ;
BIL_BI_UTIL_PKG.get_default_query(p_regionname => l_region_id,x_sqlstr => l_select_stmt);
x_custom_sql := l_select_stmt;
l_select VARCHAR2(4000);
l_select1 VARCHAR2(4000);
l_select2 VARCHAR2(4000);
l_select := '
SELECT
(SELECT description FROM AS_LEADS_ALL ALDL WHERE ALDL.LEAD_ID=FACT.OPTY_ID) BIL_MEASURE1,
fact.lead_number BIL_MEASURE2,
(SELECT PARTY_NAME FROM HZ_PARTIES HZP WHERE HZP.PARTY_ID=FACT.CUSTOMER_ID) BIL_MEASURE3,
(SELECT rstl.resource_name from jtf_rs_resource_extns_tl rstl
WHERE rstl.resource_id=fact.salesrep_id and USERENV(''LANG'')=rstl.LANGUAGE) BIL_MEASURE4,
(SELECT group_name from jtf_rs_groups_tl grtl
WHERE fact.sales_group_id=grtl.group_id and USERENV(''LANG'')=grtl.LANGUAGE) BIL_MEASURE5,
prod_id_names.value BIL_MEASURE6,
sum(nvl(sales_credit_amt ,0)) BIL_MEASURE7,
fact.win_probability BIL_MEASURE8,
(SELECT stg.NAME FROM AS_SALES_STAGES_ALL_TL stg
WHERE stg.sales_stage_id = fact.sales_stage_id and USERENV(''LANG'')=stg.LANGUAGE) BIL_MEASURE9,
TO_DATE(fact.opty_close_time_id,''J'') BIL_MEASURE10,
(SELECT STS.MEANING FROM as_statuses_tl sts
WHERE sts.status_code=fact.status and USERENV(''LANG'')=sts.LANGUAGE) BIL_MEASURE11,
SUM(SUM(NVL(sales_credit_amt ,0)) ) OVER() BIL_MEASURE12,
'|| l_cust_url ||' BIL_URL2,
'|| l_url ||' BIL_URL1
FROM
(
select
base.lead_number,
base.opty_id,
base.sales_stage_id,
base.status,
base.win_probability,
base.customer_id,
base.salesrep_id,
base.sales_group_id,
base.sales_credit_amt'||l_curr_suffix||' sales_credit_amt,
base.opty_close_time_id,
base.product_category_id
from
bil_bi_opdtl_mv base,
(
SELECT
to_number(
to_char(
(CASE
WHEN :l_period_type =''FII_TIME_ENT_YEAR'' THEN day.ent_year_start_date
WHEN :l_period_type =''FII_TIME_ENT_QTR'' THEN day.ent_qtr_start_date
WHEN :l_period_type =''FII_TIME_ENT_PERIOD'' THEN day.ent_period_start_date
WHEN :l_period_type =''FII_TIME_WEEK'' THEN day.week_start_date END
),''J'')) start_date,
to_number(
to_char(
(CASE
WHEN :l_period_type =''FII_TIME_ENT_YEAR'' THEN day.ent_year_end_date
WHEN :l_period_type =''FII_TIME_ENT_QTR'' THEN day.ent_qtr_end_date
WHEN :l_period_type =''FII_TIME_ENT_PERIOD'' THEN day.ent_period_end_date
WHEN :l_period_type =''FII_TIME_WEEK'' THEN day.week_end_date END
),''J'')) end_date
FROM
fii_time_day day
WHERE
:l_asof_date = day.report_date
) times
where
base.sales_group_id = :l_salesgroup_id
and base.salesrep_id =:l_resource_id
' || l_where || '
)fact, ';
l_select1 := '
(
SELECT /*+ NO_MERGE */
distinct id,value
FROM
eni_item_prod_cat_lookup_v
) prod_id_names
';
l_select2 := '
(SELECT /*+ NO_MERGE */
DISTINCT id,value
FROM
eni_item_prod_cat_lookup_v
WHERE id IN
(SELECT child_id FROM eni_item_prod_cat_lookup_v a WHERE a.parent_id= :l_prodcat_id)
)prod_id_names ' ;
l_custom_sql := l_select ||
l_select1 ||
l_where_clause ;
l_custom_sql := l_select ||
l_select2 ||
l_where_clause ;