APPS.BIL_BI_FCST_MGMT_RPTS_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 31

    l_conv_rate_selected        VARCHAR2(50);

Line: 61

    l_insert_stmnt		VARCHAR2(5000);

Line: 62

    l_outer_select		VARCHAR2(5000);

Line: 94

    l_pc_select			VARCHAR2(32000);

Line: 114

    l_pipe_select1           VARCHAR2(4000);

Line: 115

    l_pipe_select2           VARCHAR2(4000);

Line: 116

    l_pipe_select3           VARCHAR2(4000);

Line: 117

    l_pipe_select4           VARCHAR2(4000);

Line: 118

    l_pipe_select5           VARCHAR2(4000);

Line: 119

    l_pc_pipe_select1           VARCHAR2(4000);

Line: 120

    l_pc_pipe_select2           VARCHAR2(4000);

Line: 121

    l_pc_pipe_select3           VARCHAR2(4000);

Line: 122

    l_pc_pipe_select4           VARCHAR2(4000);

Line: 123

    l_pc_pipe_select5           VARCHAR2(4000);

Line: 154

    SELECT Meaning INTO l_cat_assign
    FROM FND_LOOKUP_VALUES
    WHERE LOOKUP_TYPE = l_lookup_type
      AND LOOKUP_CODE = l_lookup_code
      AND LANGUAGE = l_lang;

Line: 163

                                    ,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 );

Line: 215

       IF l_conv_rate_selected = 0 THEN
            l_currency_suffix := '_s';

Line: 268

       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)';

Line: 273

       l_outer_select := 'SELECT VIEWBY '||
		',VIEWBYID '||
		',BIL_MEASURE28 BIL_MEASURE1 '||
		',BIL_MEASURE2 '||
		',(BIL_MEASURE28-BIL_MEASURE2)/ABS(DECODE(BIL_MEASURE2,0,null,BIL_MEASURE2))*100 BIL_MEASURE3 '||
		',BIL_MEASURE10 BIL_MEASURE27 '||
		',BIL_MEASURE4 '||
		',BIL_MEASURE5 '||
		',(BIL_MEASURE4-BIL_MEASURE5)/ABS(DECODE(BIL_MEASURE5,0,null,BIL_MEASURE5))*100  BIL_MEASURE6 '||
		',BIL_MEASURE28 BIL_MEASURE25 '||
		',BIL_MEASURE7 '||
		',BIL_MEASURE8 '||
		',(BIL_MEASURE7-BIL_MEASURE8)/ABS(DECODE(BIL_MEASURE8, 0, null, BIL_MEASURE8))*100  BIL_MEASURE9 '||
		',BIL_MEASURE10 '||
		',BIL_MEASURE11 '||
		',(BIL_MEASURE10-BIL_MEASURE11)/ABS(DECODE(BIL_MEASURE11, 0 ,null, BIL_MEASURE11))*100 BIL_MEASURE12 '||
		',SUM(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(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(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_MEASURE28) OVER() BIL_MEASURE26'||
		',SUM(BIL_MEASURE10) OVER() BIL_MEASURE28 '||
		', 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 '||
                ',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_URL4 ';

Line: 392

l_pipe_select1 := ' NULL AS frcst '||
                      ',NULL AS priorFrcst '||
                      ',SUM((case when  :l_snapshot_date = sumry.snap_date then '||
                      'decode(:l_period_type, '||
                      '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                      '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                      '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                      '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
                      ')'||
                      'end)) AS wtdPipeline ';

Line: 405

       l_pipe_select2 := ',SUM((case when  :l_prev_snap_date = sumry.snap_date then '||
                            'decode(:l_period_type, '||
                            '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                            '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                            '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                            '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
                            ')'||
                            'end))  AS priorWtdPipeline ';

Line: 414

       l_pipe_select2 := ' , sum((CASE WHEN sumry.snap_date = :l_snapshot_date THEN '||
                          ''||l_prv_wt_pipe_amt||' '||
                           ' ELSE NULL '||
                 ' END))  AS priorWtdPipeline ';

Line: 420

l_pipe_select3 := ',SUM((case when  :l_snapshot_date = sumry.snap_date then '||
                    'decode(:l_period_type, '||
                    '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                    '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                    '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                    '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
                    ')'||
                    'end)) AS pipeline ';

Line: 430

       l_pipe_select4 := ',SUM((case when  :l_prev_snap_date = sumry.snap_date then '||
                    'decode(:l_period_type, '||
                    '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                    '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                    '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                    '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
                    ')'||
                    'end)) AS priorPipeline ';

Line: 439

       l_pipe_select4 := ' , sum((CASE WHEN sumry.snap_date =:l_snapshot_date THEN '||
                          ''||l_prev_pipe_amt||' '||
                           ' ELSE NULL '||
                 ' END))  AS priorPipeline ';

Line: 446

       l_pipe_select5 :=',NULL AS won '||
                        ',NULL AS priorWon ';

Line: 449

l_sql_stmnt3_1 := l_pipe_select1 || l_pipe_select2 || l_pipe_select3 || l_pipe_select4 || l_pipe_select5 ;

Line: 491

l_pc_pipe_select1 := ' NULL AS frcst '||
                      ',NULL AS priorFrcst '||
                      ',((case when  :l_snapshot_date = sumry.snap_date then '||
                      'decode(:l_period_type, '||
                      '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                      '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                      '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                      '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
                      ')'||
                      'end)) AS wtdPipeline ';

Line: 504

       l_pc_pipe_select2 := ',((case when  :l_prev_snap_date = sumry.snap_date then '||
                            'decode(:l_period_type, '||
                            '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                            '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                            '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                            '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
                            ')'||
                            'end))  AS priorWtdPipeline ';

Line: 513

       l_pc_pipe_select2 := ' , ((CASE WHEN sumry.snap_date = :l_snapshot_date THEN '||
                          ''||l_prv_wt_pipe_amt||' '||
                           ' ELSE NULL '||
                 ' END))  AS priorWtdPipeline ';

Line: 519

l_pc_pipe_select3 := ', ((case when  :l_snapshot_date = sumry.snap_date then '||
                    'decode(:l_period_type, '||
                    '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                    '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                    '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                    '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
                    ')'||
                    'end)) AS pipeline ';

Line: 529

       l_pc_pipe_select4 := ', ((case when  :l_prev_snap_date = sumry.snap_date then '||
                    'decode(:l_period_type, '||
                    '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                    '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                    '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                    '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
                    ')'||
                    'end)) AS priorPipeline ';

Line: 538

       l_pc_pipe_select4 := ' , ((CASE WHEN sumry.snap_date =:l_snapshot_date THEN '||
                          ''||l_prev_pipe_amt||' '||
                           ' ELSE NULL '||
                 ' END))  AS priorPipeline ';

Line: 545

       l_pc_pipe_select5 :=',NULL AS won '||
                        ',NULL AS priorWon ';

Line: 548

l_sql_stmnt3 := l_pc_pipe_select1 ||
                  l_pc_pipe_select2 ||
                  l_pc_pipe_select3 ||
                  l_pc_pipe_select4 || l_pc_pipe_select5 ;

Line: 626

        		'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_fst_crdt_type => '|| l_fst_crdt_type ||', ' ||
        		'l_calendar_id => '|| l_calendar_id;

Line: 687

			       l_custom_sql:='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 '||
				   				', SORTORDER '||
				   				',BIL_MEASURE28
                                , BIL_MEASURE2, BIL_MEASURE4, BIL_MEASURE5, BIL_MEASURE7,
                                BIL_MEASURE8, BIL_MEASURE10, BIL_MEASURE11,
                                DECODE(tmp1.salesrep_id, NULL, '''||l_url_str||''', NULL) BIL_URL1,
                                DECODE(tmp1.salesrep_id, NULL, NULL,'''||l_drill_str||''') BIL_URL2 FROM (
                                SELECT /*+ NO_MERGE */ salesrep_id, sales_group_id,
                                sortorder, parent_sales_group_id
                                ,SUM(frcst) BIL_MEASURE28 '||
								',SUM(priorFrcst) BIL_MEASURE2 '||
								',SUM(wtdPipeline) BIL_MEASURE4 '||
								',SUM(priorWtdPipeline) BIL_MEASURE5 '||
								',SUM(pipeline) BIL_MEASURE7 '||
								',SUM(priorPipeline) BIL_MEASURE8 '||
								',SUM(won) BIL_MEASURE10 '||
								',SUM(priorWon) BIL_MEASURE11 '||
								', NULL BIL_URL1 '||
								', NULL BIL_URL2 '||
								' FROM  '||
								'( '||
					 'SELECT /*+ LEADING(cal) */ '||
					  	' '||l_prodcat_flag||'
                          decode(sumry.salesrep_id, NULL,1,2) sortorder, '||
				 		'sumry.salesrep_id, '||
				 		'sumry.sales_group_id, '||
						'sumry.parent_sales_group_id parent_sales_group_id, '||
						l_sql_stmnt1_1||
					 ' FROM '||l_fii_struct||' cal, '||l_sumry||' sumry '||
						 l_denorm1||
					l_where_clause1||
					' AND sumry.parent_sales_group_id = :l_sg_id_num  '||
					' AND cal.xtd_flag = :l_yes '||
					' '||l_product_where_clause2||
					' GROUP BY '||
					  ' decode(sumry.salesrep_id, NULL,1,2),sumry.salesrep_id,sumry.sales_group_id,'||
						l_prodcat_flag||' sumry.parent_sales_group_id '||
					' UNION ALL '||
					' select  '||l_prodcat_flag||' sortorder, salesrep_id,
                    sales_group_id, parent_sales_group_id,
                    sum(frcst) frcst, sum(priorFrcst) priorFrcst,
                    sum(wtdPipeline) wtdPipeline,
                    sum(priorWtdPipeline) priorWtdPipeline,
                    sum(pipeline) pipeline,
                    sum(priorPipeline) priorPipeline, sum(won) won,
                    sum(priorWon) priorWon from (
                    SELECT /*+ LEADING(cal) */ '||
                        ' '||l_prodcat_flag||'
						decode(sumry.salesrep_id, NULL,1,2) sortorder, '||
				 		'sumry.salesrep_id, '||
				 		'sumry.sales_group_id, '||
						'sumry.parent_sales_group_id parent_sales_group_id, '||
						l_sql_stmnt2_1||
					' FROM '||l_fii_struct||' cal, '||l_sumry1||' sumry '||
					l_where_clause2||
					' AND sumry.parent_sales_group_id = :l_sg_id_num '||
					' AND cal.xtd_flag = :l_yes '||
					' GROUP BY '||
					  ' decode(sumry.salesrep_id, NULL,1,2),sumry.salesrep_id,sumry.sales_group_id,'||
						l_prodcat_flag||' sumry.parent_sales_group_id ';

Line: 752

						  ' SELECT  '||l_prodcat_flag||'
                          decode(sumry.salesrep_id, NULL,1,2) sortorder, '||
				 		  	  'sumry.salesrep_id, '||
				 		  	  'sumry.sales_group_id, '||
							  'sumry.parent_sales_group_id parent_sales_group_id, '||
							  l_sql_stmnt3_1||
						  ' FROM '||l_sumry2||' sumry '||
						  l_where_clause3||
						  ' AND sumry.parent_sales_group_id = :l_sg_id_num '||
					' GROUP BY '||
					  ' decode(sumry.salesrep_id, NULL,1,2),sumry.salesrep_id,sumry.sales_group_id,'||
						l_prodcat_flag||' sumry.parent_sales_group_id ';

Line: 782

  				   l_custom_sql:='SELECT '||
				   				' restl.resource_name VIEWBY '||
				   				',tmp1.salesrep_id||''.''||tmp1.sales_group_id VIEWBYID '||
				   				', SORTORDER '||
                                                                ',BIL_MEASURE28
                                , BIL_MEASURE2, BIL_MEASURE4, BIL_MEASURE5, BIL_MEASURE7,
                                BIL_MEASURE8, BIL_MEASURE10, BIL_MEASURE11,
                                NULL IL_URL1,
                                DECODE(tmp1.salesrep_id, NULL, NULL,'''||l_drill_str||''') BIL_URL2 FROM (
                                SELECT /*+ NO_MERGE */ salesrep_id, sales_group_id,
                                sortorder, parent_sales_group_id
                                ,SUM(frcst) BIL_MEASURE28 '||
								',SUM(priorFrcst) BIL_MEASURE2 '||
								',SUM(wtdPipeline) BIL_MEASURE4 '||
								',SUM(priorWtdPipeline) BIL_MEASURE5 '||
								',SUM(pipeline) BIL_MEASURE7 '||
								',SUM(priorPipeline) BIL_MEASURE8 '||
								',SUM(won) BIL_MEASURE10 '||
								',SUM(priorWon) BIL_MEASURE11 '||
								', NULL BIL_URL1 '||
								', NULL BIL_URL2 '||
								' FROM  '||
								'( '||
								 'SELECT /*+ LEADING(cal) */ '||
								  ' '||l_prodcat_flag||'
                                  1 sortorder, '||
				 			  	  'sumry.salesrep_id, '||
				 			  	  'sumry.sales_group_id, '||
								  'sumry.parent_sales_group_id parent_sales_group_id, '||
								  l_sql_stmnt1_1||
								 ' FROM '||l_fii_struct||' cal, '||l_sumry||' sumry '||
								 l_denorm1||
								 l_where_clause1||
								 ' AND sumry.parent_sales_group_id = :l_sg_id_num  '||
								 ' AND cal.xtd_flag = :l_yes '||
								 ' '||l_product_where_clause2||' '||
					       ' GROUP BY '||
					         ' sumry.salesrep_id,sumry.sales_group_id,'||
						       l_prodcat_flag||' sumry.parent_sales_group_id  '||
								 ' UNION ALL '||
								 ' select  '||l_prodcat_flag||' sortorder, salesrep_id,
                                    sales_group_id, parent_sales_group_id,
                                    sum(frcst) frcst, sum(priorFrcst) priorFrcst,
                                    sum(wtdPipeline) wtdPipeline,
                                    sum(priorWtdPipeline) priorWtdPipeline,
                                    sum(pipeline) pipeline,
                                    sum(priorPipeline) priorPipeline, sum(won) won,
                                    sum(priorWon) priorWon from (
                                SELECT /*+ LEADING(cal) */ '||
								  ' '||l_prodcat_flag||'
                                  1 sortorder, '||
				 			  	  'sumry.salesrep_id, '||
				 			  	  'sumry.sales_group_id, '||
								  'sumry.parent_sales_group_id parent_sales_group_id, '||
								  l_sql_stmnt2_1||
								  ' FROM '||l_fii_struct||' cal, '||l_sumry1||' sumry '||
								  l_where_clause2||
								  ' AND sumry.parent_sales_group_id = :l_sg_id_num '||
								  ' AND cal.xtd_flag = :l_yes '||
							    ' GROUP BY '||
					         ' sumry.salesrep_id,sumry.sales_group_id,'||
						       ' '||l_prodcat_flag||' sumry.parent_sales_group_id ';

Line: 847

								  ' SELECT  '||l_prodcat_flag||'
                                  1 sortorder, '||
				 			  	  'sumry.salesrep_id, '||
				 			  	  'sumry.sales_group_id, '||
								  'sumry.parent_sales_group_id parent_sales_group_id, '||
								  l_sql_stmnt3_1||
								  ' FROM '||l_sumry2||' sumry '||
								  l_where_clause3||
								  ' AND sumry.parent_sales_group_id = :l_sg_id_num '||
					       ' GROUP BY '||
					         ' sumry.salesrep_id,sumry.sales_group_id,'||
						       ' '||l_prodcat_flag||' sumry.parent_sales_group_id';

Line: 903

                EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		USING 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_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_sg_id_num,l_yes,
		 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date,
		 l_sg_id_num,l_yes,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
		 l_sg_id_num,
		 l_sg_id_num;

Line: 918

                EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		USING 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_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_sg_id_num,l_yes,
		 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date,
		 l_sg_id_num,l_yes,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
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;

Line: 936

             EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	     USING 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_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_sg_id_num, l_yes, l_prodcat_id, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
             l_curr_as_of_date,l_prev_date, l_sg_id_num, l_yes,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
	     l_sg_id_num, l_prodcat_id, l_sg_id_num;

Line: 949

             EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	     USING 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_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_sg_id_num, l_yes, l_prodcat_id, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
             l_curr_as_of_date,l_prev_date, l_sg_id_num, l_yes,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
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, l_sg_id_num;

Line: 967

	  EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	   USING 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_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_sg_id_num,l_yes,
            l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date,
            l_sg_id_num,l_yes,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
	    l_sg_id_num, l_sg_id_num,l_resource_id;

Line: 980

	  EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	   USING 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_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_sg_id_num,l_yes,
            l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date,
            l_sg_id_num,l_yes,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
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;

Line: 995

	   EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	      USING 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_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_sg_id_num,l_yes,l_prodcat_id, l_curr_as_of_date, l_prev_date,
               l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date, l_sg_id_num,l_yes,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
               l_sg_id_num,l_prodcat_id, l_sg_id_num,l_resource_id;

Line: 1008

	   EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	      USING 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_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_sg_id_num,l_yes,l_prodcat_id, l_curr_as_of_date, l_prev_date,
               l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date, l_sg_id_num,l_yes,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
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, l_sg_id_num,l_resource_id;

Line: 1024

			  x_custom_sql := 'SELECT * FROM ('||
			  			l_outer_select||' FROM BIL_BI_RPT_TMP1 '||
		  					' ORDER BY SORTORDER, UPPER(VIEWBY)'||
							') '||
							' WHERE '||l_null_removal_clause;

Line: 1047

				l_pc_select := ' SELECT
              decode(tmp1.viewbyid, -1,:l_unassigned_value,
                                               mtl.DESCRIPTION || '' ('' || mtl.CATEGORY_CONCAT_SEGS ||'')'') VIEWBY,
		   				sortorder,
                        frcst ,
		   				priorFrcst,
		   				wtdPipeline,
		   				priorWtdPipeline ,
		   				pipeline,
		   				priorPipeline,
		   				won ,
		   				priorWon,
		   				VIEWBYID,
                        salesrep_id,
		   				BIL_URL1,
                          DECODE(tmp1.viewbyid,''-1'',NULL,'''||l_cat_url||''') BIL_URL2 '||
               ' FROM (
                SELECT
		  				1 SORTORDER,
		   				NULL frcst ,
		   				NULL priorFrcst,
		   				SUM(wtdPipeline) wtdPipeline,
		   				SUM(priorWtdPipeline)  priorWtdPipeline,
		   				SUM(pipeline) pipeline,
		   				SUM(priorPipeline) priorPipeline,
		   				SUM(won) won ,
		   				SUM(priorWon) priorWon,
                        pcd.parent_id VIEWBYID,
                        salesrep_id,
		   				NULL BIL_URL1
						';

Line: 1099

					l_custom_sql := ' SELECT VIEWBY '||
					   			', VIEWBYID '||
					   			', SORTORDER '||
								',SUM(frcst) BIL_MEASURE28 '||
								',SUM(priorFrcst) BIL_MEASURE2 '||
								',SUM(wtdPipeline) BIL_MEASURE4 '||
								',SUM(priorWtdPipeline) BIL_MEASURE5 '||
								',SUM(pipeline) BIL_MEASURE7 '||
								',SUM(priorPipeline) BIL_MEASURE8 '||
								',SUM(won) BIL_MEASURE10 '||
								',SUM(priorWon) BIL_MEASURE11 '||
								','''||l_drill_str||''' BIL_URL1 '||
								',BIL_URL2 '||
							' FROM  '||
							'( '||
							' SELECT /*+ LEADING(cal) */ '||
							    	' pcd.value VIEWBY'||
								', 1 sortorder, '||
								  l_sql_stmnt4||
								',pcd.id VIEWBYID'||
								',sumry.salesrep_id salesrep_id '||
								',NULL BIL_URL1'||
								',DECODE(pcd.id,''-1'',NULL,'''||l_cat_url||''') BIL_URL2 '||
							' FROM '||l_fii_struct||' cal,'||
							        l_sumry||' sumry '||
								l_denorm||' '||
							' '||l_where_clause1||' AND '||l_product_where_clause1||
							' AND sumry.sales_group_id = :l_sg_id_num '||
  							l_parent_sls_grp_where_clause||
							' AND cal.xtd_flag = :l_yes ';

Line: 1139

							l_pc_select ||
							' FROM ('||
							' SELECT /*+ LEADING(cal) */'||
								' NULL VIEWBY'||
								', 1 sortorder, '||l_sql_stmnt2||
								',NULL VIEWBYID'||
								',sumry.salesrep_id salesrep_id '||
								',NULL BIL_URL1'||
								',sumry.product_category_id product_category_id'||
							' FROM '||l_fii_struct||' cal,'||
							      l_sumry1||' sumry '||
							l_where_clause2||' '||
							' AND sumry.sales_group_id = :l_sg_id_num '||
							l_parent_sls_grp_where_clause||
							' AND cal.xtd_flag = :l_yes ';

Line: 1164

						' SELECT NULL VIEWBY'||
							', 1 sortorder, '||l_sql_stmnt3||
							',NULL VIEWBYID'||
							',sumry.salesrep_id salesrep_id '||
							',NULL BIL_URL1'||
							',sumry.product_category_id product_category_id'||
						' FROM '||l_sumry2||' sumry '||
						' '||l_where_clause3||' '||
						' AND sumry.sales_group_id = :l_sg_id_num '||
						l_parent_sls_grp_where_clause;

Line: 1230

	  EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	  USING 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_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_yes,l_sg_id_num, l_yes, l_unassigned_value,
            l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
            l_curr_as_of_date,l_prev_date, l_sg_id_num, l_yes,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
	     l_sg_id_num, l_yes; --pc where clause

Line: 1245

	  EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	  USING 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_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_yes,l_sg_id_num, l_yes, l_unassigned_value,
            l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
            l_curr_as_of_date,l_prev_date, l_sg_id_num, l_yes,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
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_yes; --pc where clause

Line: 1262

             EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	     USING 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_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_yes,l_sg_id_num,l_yes,l_resource_id, l_unassigned_value,
             l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
             l_curr_as_of_date,l_prev_date, l_sg_id_num,l_yes,l_resource_id,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
             l_sg_id_num,l_resource_id, l_yes; --pc where clause;

Line: 1277

             EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	     USING 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_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_yes,l_sg_id_num,l_yes,l_resource_id, l_unassigned_value,
             l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
             l_curr_as_of_date,l_prev_date, l_sg_id_num,l_yes,l_resource_id,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
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_yes; --pc where clause;

Line: 1296

	 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	 USING 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_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_yes,l_sg_id_num, l_parent_sales_group_id, l_yes, l_unassigned_value,
         l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
         l_curr_as_of_date,l_prev_date, l_sg_id_num, l_parent_sales_group_id, l_yes,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
          l_sg_id_num, l_parent_sales_group_id, l_yes; --pc where clause

Line: 1310

	 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	 USING 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_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_yes,l_sg_id_num, l_parent_sales_group_id, l_yes, l_unassigned_value,
         l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
         l_curr_as_of_date,l_prev_date, l_sg_id_num, l_parent_sales_group_id, l_yes,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
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_parent_sales_group_id, l_yes; --pc where clause

Line: 1326

	   EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	   USING 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_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_yes,l_sg_id_num,
           l_sg_id_num, l_yes,l_resource_id, l_unassigned_value, l_curr_as_of_date, l_prev_date,
           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_yes,l_resource_id,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
	   l_sg_id_num, l_sg_id_num, l_resource_id, l_yes; --pc where clause;

Line: 1340

	   EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	   USING 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_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_yes,l_sg_id_num,
           l_sg_id_num, l_yes,l_resource_id, l_unassigned_value, l_curr_as_of_date, l_prev_date,
           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_yes,l_resource_id,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
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_yes; --pc where clause;

Line: 1357

					x_custom_sql := 'SELECT * FROM ('||
			  					l_outer_select||' FROM BIL_BI_RPT_TMP1 '||
								' ORDER BY SORTORDER, UPPER(VIEWBY) '||
								') '||
								' WHERE '||l_null_removal_clause;

Line: 1363

				   l_custom_sql := ' SELECT VIEWBY '||
							', VIEWBYID '||
					   		', SORTORDER '||
							',SUM(frcst) BIL_MEASURE28 '||
							',SUM(priorFrcst) BIL_MEASURE2 '||
							',SUM(wtdPipeline) BIL_MEASURE4 '||
							',SUM(priorWtdPipeline) BIL_MEASURE5 '||
							',SUM(pipeline) BIL_MEASURE7 '||
							',SUM(priorPipeline) BIL_MEASURE8 '||
							',SUM(won) BIL_MEASURE10 '||
							',SUM(priorWon) BIL_MEASURE11 '||
							',DECODE(VIEWBY,'||':l_cat_assign'||',NULL,'''||l_drill_str||''') BIL_URL1 '||
							',BIL_URL2 '||
							' FROM  '||
							'( '||
							' SELECT /*+ LEADING(cal) */ '||
							    ' decode(pcd.parent_id,pcd.child_id,'||
								' decode(sumry.assign_to_cat,0,pcd.value,:l_cat_assign), '||
								' pcd.value) VIEWBY '||
							     ', decode(pcd.parent_id,pcd.id, 1, 2) sortorder, '||
							        l_sql_stmnt4||
							     ',pcd.id VIEWBYID'||
							     ',SUMRY.salesrep_id salesrep_id '||
							     ',NULL BIL_URL1'||
							     ', decode(pcd.parent_id, pcd.child_id, null, '||
							     ' '''||l_cat_url||''') BIL_URL2 '||
							' FROM '||l_fii_struct||' cal, '||
							        l_sumry||' sumry '||
							        l_denorm||' '||
							' '||l_where_clause1||' AND '||l_product_where_clause1||
							  ' AND sumry.sales_group_id = :l_sg_id_num '||
							  l_parent_sls_grp_where_clause||
							  ' AND cal.xtd_flag = :l_yes ';

Line: 1436

					EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
					  USING l_cat_assign, 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_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_sg_id_num, l_yes;

Line: 1447

					EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
					  USING l_cat_assign, 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_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_sg_id_num,l_yes,l_resource_id;

Line: 1461

					EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
					  USING l_cat_assign, 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_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_sg_id_num,
						l_parent_sales_group_id,
						l_yes;

Line: 1473

					EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
					  USING l_cat_assign, 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_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_sg_id_num,
						l_sg_id_num,
						l_yes,l_resource_id;

Line: 1488

				    	' SELECT VIEWBY '||
						', VIEWBYID '||
						', SORTORDER '||
						',NULL BIL_MEASURE28 '||
						',NULL BIL_MEASURE2 '||
						',SUM(wtdPipeline) BIL_MEASURE4 '||
						',SUM(priorWtdPipeline) BIL_MEASURE5 '||
						',SUM(pipeline) BIL_MEASURE7 '||
						',SUM(priorPipeline) BIL_MEASURE8 '||
						',SUM(won) BIL_MEASURE10 '||
						',SUM(priorWon) BIL_MEASURE11 '||
						',DECODE(VIEWBY,'||':l_cat_assign'||',NULL,'''||l_drill_str||''') BIL_URL1 '||
						',BIL_URL2 '||
					' 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) sortorder, '||
						l_sql_stmnt2||
						',pcd.id VIEWBYID'||
						',NULL BIL_URL1'||
						',decode(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL2'||
					' FROM '||l_fii_struct||' cal,'||
					      l_sumry1||' sumry'||
					      l_pipe_denorm||' '||
					' '||l_where_clause2||' '||
					   l_pipe_product_where_clause||
					  ' AND sumry.sales_group_id = :l_sg_id_num '||
					  l_parent_sls_grp_where_clause||
					  ' AND cal.xtd_flag = :l_yes ';

Line: 1531

					' 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_sql_stmnt3||
					',pcd.id VIEWBYID'||
					',NULL BIL_URL1'||
					',decode(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL2'||
					' FROM '||l_sumry2||' sumry'||
						l_pipe_denorm||' '||
					' '||l_where_clause3||' '||
					l_pipe_product_where_clause||
					' AND sumry.sales_group_id = :l_sg_id_num '||
					l_parent_sls_grp_where_clause;

Line: 1587

         EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date,
          l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num,
          l_yes, l_cat_assign,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
	   l_prodcat_id, l_sg_id_num;

Line: 1598

         EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date,
          l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num,
          l_yes, l_cat_assign,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date,l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date ,l_prev_snap_date,
	   l_prodcat_id, l_sg_id_num;

Line: 1611

           EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	   USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
                 l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num, l_yes, l_resource_id, l_cat_assign ,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
		  l_prodcat_id, l_sg_id_num, l_resource_id;

Line: 1621

           EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	   USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
                 l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num, l_yes, l_resource_id, l_cat_assign ,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date,l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date ,l_prev_snap_date,
		  l_prodcat_id, l_sg_id_num, l_resource_id;

Line: 1635

         EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
            l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num, l_parent_sales_group_id, l_yes, l_cat_assign,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
	    l_prodcat_id, l_sg_id_num, l_parent_sales_group_id;

Line: 1645

         EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
            l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num, l_parent_sales_group_id, l_yes, l_cat_assign,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date,l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date ,l_prev_snap_date,
	    l_prodcat_id, l_sg_id_num, l_parent_sales_group_id;

Line: 1657

	 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
          l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num, l_sg_id_num, l_yes, l_resource_id, l_cat_assign ,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,l_period_type,
l_snapshot_date,
l_snapshot_date,
	   l_prodcat_id, l_sg_id_num,  l_sg_id_num, l_resource_id;

Line: 1667

	 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
          l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num, l_sg_id_num, l_yes, l_resource_id, l_cat_assign ,
l_snapshot_date , l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date,l_period_type,
l_prev_snap_date,l_period_type,
l_snapshot_date ,l_prev_snap_date,
	   l_prodcat_id, l_sg_id_num,  l_sg_id_num, l_resource_id;

Line: 1679

	  			       x_custom_sql := 'SELECT * FROM ('||
						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 BIL_BI_RPT_TMP1 '||
						' GROUP BY VIEWBY, VIEWBYID, SORTORDER, '||
							' BIL_URL1, BIL_URL2 '||
						') '||
						' ORDER BY SORTORDER, UPPER(VIEWBY) '||
						') WHERE '||l_null_removal_clause;

Line: 1779

    l_conv_rate_selected        VARCHAR2(50);

Line: 1803

    l_insert_stmnt		VARCHAR2(5000);

Line: 1804

    l_outer_select		VARCHAR2(5000);

Line: 1827

    l_pc_select			VARCHAR2(32000);

Line: 1849

    l_pipe_select1           varchar2(4000);

Line: 1850

    l_pipe_select2           varchar2(4000);

Line: 1851

    l_pipe_select3           varchar2(4000);

Line: 1852

    l_pipe_select4           varchar2(4000);

Line: 1879

    SELECT Meaning INTO l_cat_assign
    FROM FND_LOOKUP_VALUES
    WHERE LOOKUP_TYPE = l_lookup_type
      AND LOOKUP_CODE = l_lookup_code
      AND LANGUAGE = l_lang;

Line: 1888

                                    ,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 );

Line: 1950

       IF l_conv_rate_selected = 0 THEN
            l_currency_suffix := '_s';

Line: 1963

       l_insert_stmnt  := 'INSERT INTO BIL_BI_RPT_TMP1 (VIEWBY, VIEWBYID, SORTORDER,BIL_MEASURE28,'||
			 	'BIL_MEASURE2,BIL_MEASURE3,BIL_MEASURE4,BIL_MEASURE5,'||
				'BIL_MEASURE6,BIL_MEASURE7,BIL_MEASURE8,BIL_MEASURE9,BIL_MEASURE10,BIL_MEASURE11,'||
                                'BIL_MEASURE12,BIL_URL1,BIL_URL2)';

Line: 2029

l_outer_select := 'SELECT VIEWBY '||
',VIEWBYID '||
',BIL_MEASURE28                                                                        BIL_MEASURE1  '||
',BIL_MEASURE2                                                                         BIL_MEASURE23 '||
',BIL_MEASURE3                                                                         BIL_MEASURE24 '||
',(BIL_MEASURE28-BIL_MEASURE2)/ABS(DECODE(BIL_MEASURE2,0,null,BIL_MEASURE2))*100       BIL_MEASURE2  '||
',BIL_MEASURE4                                                                         BIL_MEASURE25 '||
',BIL_MEASURE5                                                                         BIL_MEASURE26 '||
',BIL_MEASURE6                                                                         BIL_MEASURE27 '||
',BIL_MEASURE13                                                                        BIL_MEASURE3  '||
',(BIL_MEASURE13-BIL_MEASURE14)/ABS(DECODE(BIL_MEASURE14,0,null,BIL_MEASURE14))*100    BIL_MEASURE4  '||
',BIL_MEASURE14                                                                        BIL_MEASURE5  '||
',(BIL_MEASURE14-BIL_MEASURE15)/ABS(DECODE(BIL_MEASURE15,0,null,BIL_MEASURE15))*100    BIL_MEASURE6  '||
',BIL_MEASURE7                                                                         BIL_MEASURE7  '||
',BIL_MEASURE8                                                                         BIL_MEASURE28 '||
',(BIL_MEASURE7-BIL_MEASURE8)/ABS(DECODE(BIL_MEASURE8, 0, null, BIL_MEASURE8))*100     BIL_MEASURE8  '||
',BIL_MEASURE9                                                                         BIL_MEASURE9  '||
',BIL_MEASURE10                                                                        BIL_MEASURE29 '||
',(BIL_MEASURE9-BIL_MEASURE10)/ABS(DECODE(BIL_MEASURE10, 0 ,null, BIL_MEASURE10))*100  BIL_MEASURE10 '||
',BIL_MEASURE11                                                                        BIL_MEASURE11 '||
',BIL_MEASURE12                                                                        BIL_MEASURE30 '||
',(BIL_MEASURE11-BIL_MEASURE12)/ABS(DECODE(BIL_MEASURE12, 0 ,null, BIL_MEASURE12))*100 BIL_MEASURE12 '||
',SUM(BIL_MEASURE28) OVER()                                                                   BIL_MEASURE13 '||
',SUM(BIL_MEASURE2)  OVER()                                                                   BIL_MEASURE32 '||
',(SUM(BIL_MEASURE28) OVER()-SUM(BIL_MEASURE2) OVER())/ABS(DECODE(SUM(BIL_MEASURE2) OVER(),0,null, '||
'SUM(BIL_MEASURE2) OVER()))*100                                                               BIL_MEASURE14 '||
',SUM(BIL_MEASURE13) OVER()                                                                   BIL_MEASURE15 '||
',(SUM(BIL_MEASURE13) OVER()-SUM(BIL_MEASURE14) OVER())/ABS(DECODE(SUM(BIL_MEASURE14) OVER(),0,null, '||
'SUM(BIL_MEASURE14) OVER()))*100                                                              BIL_MEASURE16 '||
',SUM(BIL_MEASURE7) OVER()                                                                    BIL_MEASURE17 '||
',(SUM(BIL_MEASURE7) OVER()-SUM(BIL_MEASURE8) OVER())/ABS(DECODE(SUM(BIL_MEASURE8) OVER(), 0, null,  '||
'SUM(BIL_MEASURE8) OVER()))*100                                                               BIL_MEASURE18 '||
',SUM(BIL_MEASURE9) OVER()                                                                    BIL_MEASURE19 '||
',(SUM(BIL_MEASURE9) OVER()-SUM(BIL_MEASURE10) OVER())/ABS(DECODE(SUM(BIL_MEASURE10) OVER(), 0 ,null, '||
'SUM(BIL_MEASURE10) OVER()))*100                                                              BIL_MEASURE20 '||
',SUM(BIL_MEASURE11) OVER()                                                                   BIL_MEASURE21 '||
',(SUM(BIL_MEASURE11) OVER()-SUM(BIL_MEASURE12) OVER())/ABS(DECODE(SUM(BIL_MEASURE12) OVER(), 0 ,null, '||
'SUM(BIL_MEASURE12) OVER()))*100                                                              BIL_MEASURE22 '||
', BIL_URL1 '||
',DECODE('''||l_curr_as_of_date||''',TRUNC(SYSDATE),'||
'DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=PIPELINE'''||')) BIL_URL2 ';

Line: 2153

l_pipe_select1 := ' NULL AS frcst '||
                    ',NULL AS priorFrcst '||
                    ',NULL AS frcst_sub '||
                    ',NULL AS PriorFrcst_sub '||
                    ',(case when  :l_snapshot_date = sumry.snap_date then '||
                    'decode(:l_period_type, '||
                    '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                    '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                    '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                    '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
                    ')'||
                    'end) AS wtdPipeline ';

Line: 2168

       l_pipe_select2 := ',(case when  :l_prev_snap_date = sumry.snap_date then '||
                            'decode(:l_period_type, '||
                            '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                            '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                            '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                            '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
                            ')'||
                            'end)  AS priorWtdPipeline ';

Line: 2177

       l_pipe_select2 := ' ,(CASE WHEN sumry.snap_date = :l_snapshot_date THEN '||
                          ''||l_prv_wt_pipe_amt||' '||
                           ' ELSE NULL '||
                 ' END)  AS priorWtdPipeline ';

Line: 2184

l_pipe_select3 := ',(case when  :l_snapshot_date = sumry.snap_date then '||
                    'decode(:l_period_type, '||
                    '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                    '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                    '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                    '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
                    ')'||
                    'end) AS pipeline ';

Line: 2194

       l_pipe_select4 := ',(case when  :l_prev_snap_date = sumry.snap_date then '||
                          'decode(:l_period_type, '||
                          '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
                          '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
                          '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
                          '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
                          ')'||
                          'end) AS priorPipeline ';

Line: 2203

       l_pipe_select4 := ' ,(CASE WHEN sumry.snap_date =:l_snapshot_date THEN '||
                          ''||l_prev_pipe_amt||' '||
                           ' ELSE NULL '||
                 ' END)  AS priorPipeline ';

Line: 2211

l_sql_stmnt3 := l_pipe_select1 || l_pipe_select2 || l_pipe_select3 || l_pipe_select4 ;

Line: 2249

        		'l_conv_rate_selected => '|| l_conv_rate_selected ||', ' ||
        		'l_bitand_id => '|| l_bitand_id ||', ' ||
        		'l_period_type => '|| l_period_type ||', ' ||
                        'l_parent_sales_group_id => '|| l_parent_sales_group_id ||', '||
        		'l_sg_id_num => '|| l_sg_id_num ||', ' ||
			'l_resource_id => '||l_resource_id||', '||
        		'l_fst_crdt_type => '|| l_fst_crdt_type ||', ' ||
        		'l_calendar_id => '|| l_calendar_id;

Line: 2298

	      l_pc_select := ' SELECT
                               decode(tmp1.viewbyid, -1,:l_unassigned_value,
                               mtl.DESCRIPTION || '' ('' || mtl.CATEGORY_CONCAT_SEGS ||'')'') VIEWBY,
		   	       sortorder,
                               frcst ,
		   	       priorFrcst,
                               priorpriorFrcst,
                               oppfrcst,
                               prior_oppfrcst,
                               priorprior_oppfrcst,
                               frcst_sub,
                               priorFrcst_sub,
		   	       pipeline,
		   	       priorPipeline,
		   	       wtdPipeline,
		   	       priorWtdPipeline ,
		   	       VIEWBYID,
                               salesrep_id,
                               DECODE(tmp1.viewbyid,''-1'',NULL,'''||l_cat_url||''') BIL_URL1,
                               BIL_URL2 '||
                             ' FROM (
                                   SELECT
		  		   1 SORTORDER,
		   		   NULL frcst ,
		   		   NULL priorFrcst,
                                   NULL priorpriorfrcst,
                                   NULL oppfrcst,
                                   NULL prior_oppfrcst,
                                   NULL priorprior_oppfrcst,
                                   NULL frcst_sub,
                                   NULL priorFrcst_sub,
		   		   SUM(pipeline) pipeline,
		   		   SUM(priorPipeline) priorPipeline,
		   		   SUM(wtdPipeline) wtdPipeline,
		   		   SUM(priorWtdPipeline) priorWtdPipeline ,
		                   pcd.parent_id VIEWBYID,
                                   salesrep_id,
		   		   BIL_URL2
				   ';

Line: 2362

        ' SELECT   VIEWBY '||
               ',VIEWBYID '||
               ',SORTORDER '||
               ',SUM(frcst) BIL_MEASURE1 '||
               ',SUM(priorFrcst) BIL_MEASURE2 '||
               ',SUM(priorpriorFrcst) BIL_MEASURE3 '||
               ',SUM(oppFrcst) BIL_MEASURE4 '||
               ',SUM(prior_oppFrcst) BIL_MEASURE5 '||
               ',SUM(priorprior_oppFrcst) BIL_MEASURE6 '||
               ',SUM(frcst_sub) BIL_MEASURE7 '||
               ',SUM(priorFrcst_sub) BIL_MEASURE8 '||
               ',SUM(pipeline) BIL_MEASURE9 '||
               ',SUM(priorPipeline) BIL_MEASURE10 '||
               ',SUM(wtdPipeline) BIL_MEASURE11 '||
               ',SUM(priorWtdPipeline) BIL_MEASURE12 '||
               ',BIL_URL1 '||
               ','''||l_pipe_url||''' BIL_URL2 '||
      ' FROM  ( ' ||
	  ' SELECT /*+ LEADING(cal) */ '||
	           ' pcd.value VIEWBY '||
	           ', 1 sortorder, '||
	              l_sql_stmnt1||
	           ',pcd.id VIEWBYID '||
	           ',sumry.salesrep_id salesrep_id '||
	           ',DECODE(pcd.id,''-1'',NULL,'''||l_cat_url||''') BIL_URL1 '||
                   ',NULL BIL_URL2 '||
	  ' FROM '||l_fii_struct||' cal,'||
	            l_sumry||' sumry '||
		    l_denorm||' '||
          ' '||l_where_clause1||' AND '||
               l_product_where_clause1||
	  ' AND sumry.sales_group_id = :l_sg_id_num '||
	    l_parent_sls_grp_where_clause ||
          ' AND cal.xtd_flag = :l_yes ';

Line: 2405

                   l_pc_select ||
	          ' FROM ('||
                  ' SELECT NULL VIEWBY'||
		  ', 1 sortorder, '||l_sql_stmnt3||
		  ',NULL VIEWBYID'||
		  ',sumry.salesrep_id salesrep_id '||
		  ',sumry.product_category_id product_category_id'||
                  ',NULL BIL_URL2 '||
		  ' FROM '||l_sumry2||' sumry '||
		  ' '||l_where_clause3||' '||
		  ' AND sumry.sales_group_id = :l_sg_id_num '||
		    l_parent_sls_grp_where_clause;

Line: 2472

                  EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		       USING
                             l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
                             l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
			     l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
                             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_fst_crdt_type, l_fst_crdt_type,
                             l_curr_as_of_date, l_prev_date, l_prior_prior_date, l_curr_page_time_id,
                             l_prev_page_time_id, l_prior_prior_time_id, l_yes, l_sg_id_num, l_yes,
                             l_unassigned_value,
			     l_snapshot_date, l_period_type,
                             l_snapshot_date,
			     l_snapshot_date, l_period_type,
			     l_snapshot_date,
                             l_snapshot_date,
			     l_sg_id_num, l_yes;

Line: 2490

                  EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		       USING
                             l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
                             l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
			     l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
                             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_fst_crdt_type, l_fst_crdt_type,
                             l_curr_as_of_date, l_prev_date, l_prior_prior_date, l_curr_page_time_id,
                             l_prev_page_time_id, l_prior_prior_time_id, l_yes, l_sg_id_num, l_yes,
                             l_unassigned_value,
			     l_snapshot_date, l_period_type,
                             l_prev_snap_date, l_period_type,
			     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_yes;

Line: 2510

		  EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		   USING l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
                         l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
                         l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
                         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_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
                         l_prev_date, l_prior_prior_date, l_curr_page_time_id,
                         l_prev_page_time_id, l_prior_prior_time_id, l_yes, l_sg_id_num, l_yes, l_resource_id,
                         l_unassigned_value,
                         l_snapshot_date, l_period_type,
			 l_snapshot_date,
			 l_snapshot_date, l_period_type,
			 l_snapshot_date,
			 l_snapshot_date,
			 l_sg_id_num, l_resource_id, l_yes;

Line: 2526

              	    EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		          USING l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
                         l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
                         l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
                         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_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
                         l_prev_date, l_prior_prior_date, l_curr_page_time_id,
                         l_prev_page_time_id, l_prior_prior_time_id, l_yes, l_sg_id_num, l_yes, l_resource_id,
                         l_unassigned_value,
                         l_snapshot_date, l_period_type,
			 l_prev_snap_date, l_period_type,
			 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_yes;

Line: 2547

                   EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	             USING l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id,
                         l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
                         l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id,
                         l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
                         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_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
                         l_prev_date, l_prior_prior_date, l_curr_page_time_id, l_prev_page_time_id,
                         l_prior_prior_time_id, l_yes, l_sg_id_num, l_parent_sales_group_id, l_yes,
                         l_unassigned_value,
			 l_snapshot_date, l_period_type,
		         l_snapshot_date,
		         l_snapshot_date, l_period_type,
			 l_snapshot_date,
			 l_snapshot_date,
                         l_sg_id_num, l_parent_sales_group_id, l_yes;

Line: 2565

                   EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	             USING l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id,
                         l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
                         l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id,
                         l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
                         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_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
                         l_prev_date, l_prior_prior_date, l_curr_page_time_id, l_prev_page_time_id,
                         l_prior_prior_time_id, l_yes, l_sg_id_num, l_parent_sales_group_id, l_yes,
                         l_unassigned_value,
			 l_snapshot_date, l_period_type,
		         l_prev_snap_date, l_period_type,
		         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_parent_sales_group_id, l_yes;

Line: 2586

	         EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	              USING
                          l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
                          l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
			  l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
                          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_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
                          l_prev_date, l_prior_prior_date, l_curr_page_time_id, l_prev_page_time_id,
                          l_prior_prior_time_id, l_yes, l_sg_id_num, l_sg_id_num, l_yes, l_resource_id,
                          l_unassigned_value,
			 l_snapshot_date, l_period_type,
			 l_snapshot_date,
			 l_snapshot_date, l_period_type,
			 l_snapshot_date,
			 l_snapshot_date,
		           l_sg_id_num, l_sg_id_num, l_resource_id, l_yes;

Line: 2603

	           EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
	               USING
                          l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
                          l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
			  l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
                          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_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
                          l_prev_date, l_prior_prior_date, l_curr_page_time_id, l_prev_page_time_id,
                          l_prior_prior_time_id, l_yes, l_sg_id_num, l_sg_id_num, l_yes, l_resource_id,
                          l_unassigned_value,
			 l_snapshot_date, l_period_type,
			 l_prev_snap_date, l_period_type,
			 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_yes;

Line: 2627

         l_custom_sql := ' SELECT   VIEWBY '||
                         ',VIEWBYID '||
                         ',SORTORDER '||
                         ',SUM(frcst) BIL_MEASURE1 '||
                         ',SUM(priorFrcst) BIL_MEASURE2 '||
                         ',SUM(priorpriorFrcst) BIL_MEASURE3 '||
                         ',SUM(oppFrcst) BIL_MEASURE4 '||
                         ',SUM(prior_oppFrcst) BIL_MEASURE5 '||
                         ',SUM(priorprior_oppFrcst) BIL_MEASURE6 '||
                         ',SUM(frcst_sub) BIL_MEASURE7 '||
                         ',SUM(priorFrcst_sub) BIL_MEASURE8 '||
                         ',NULL BIL_MEASURE9 '||
                         ',NULL BIL_MEASURE10 '||
                         ',NULL BIL_MEASURE11 '||
                         ',NULL BIL_MEASURE12 '||
                         ',BIL_URL1 '||
                         ',DECODE(VIEWBY,'||':l_cat_assign'||',NULL,'''||l_pipe_url||''') BIL_URL2 '||
           	        ' FROM  '||
		      '( '||
		      ' SELECT /*+ LEADING(cal) */ '||
		      ' decode(pcd.parent_id,pcd.child_id,'||
		      ' decode(sumry.assign_to_cat,0,pcd.value,:l_cat_assign), '||
		      ' pcd.value) VIEWBY '||
		      ', decode(pcd.parent_id,pcd.id, 1, 2) sortorder, '||
		       l_sql_stmnt1||
		       ',pcd.id VIEWBYID'||
		       ',SUMRY.salesrep_id salesrep_id '||
		       ',decode(pcd.parent_id, pcd.child_id, null, '''||l_cat_url||''') BIL_URL1 '||
                       ',NULL BIL_URL2 '||
		       ' FROM '||l_fii_struct||' cal, '||
		                l_sumry||' sumry '||
				l_denorm||' '||
                        ' '||l_where_clause1||' AND '||l_product_where_clause1||
		        ' AND sumry.sales_group_id = :l_sg_id_num '||
			  l_parent_sls_grp_where_clause ||
			' AND cal.xtd_flag = :l_yes ';

Line: 2704

					EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
					  USING l_cat_assign, l_cat_assign,
				  		l_curr_page_time_id,
                                                l_curr_as_of_date,
						l_prev_page_time_id,
                                                l_prev_date,
                                                l_prior_prior_time_id,
                                                l_prior_prior_date,
				  		l_curr_page_time_id,
                                                l_curr_as_of_date,
						l_prev_page_time_id,
                                                l_prev_date,
                                                l_prior_prior_time_id,
                                                l_prior_prior_date,
				  		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_fst_crdt_type,
                                                l_fst_crdt_type,
						l_curr_as_of_date,
                                                l_prev_date,
                                                l_prior_prior_date,
                                                l_curr_page_time_id,
                                                l_prev_page_time_id,
                                                l_prior_prior_time_id,
						l_prodcat_id,
						l_sg_id_num,
                                                l_yes;

Line: 2737

					EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
					  USING l_cat_assign, l_cat_assign,
				  		l_curr_page_time_id,
                                                l_curr_as_of_date,
						l_prev_page_time_id,
                                                l_prev_date,
                                                l_prior_prior_time_id,
                                                l_prior_prior_date,
				  		l_curr_page_time_id,
                                                l_curr_as_of_date,
						l_prev_page_time_id,
                                                l_prev_date,
                                                l_prior_prior_time_id,
                                                l_prior_prior_date,
                                                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_fst_crdt_type,
                                                l_fst_crdt_type,
						l_curr_as_of_date,
                                                l_prev_date,
                                                l_prior_prior_date,
                                                l_curr_page_time_id,
                                                l_prev_page_time_id,
                                                l_prior_prior_time_id,
                                                l_prodcat_id,
						l_sg_id_num,
                                                l_yes,
                                                l_resource_id;

Line: 2773

					EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
					  USING l_cat_assign,l_cat_assign,
				  		l_curr_page_time_id,
                                                l_curr_as_of_date,
						l_prev_page_time_id,
                                                l_prev_date,
                                                l_prior_prior_time_id,
                                                l_prior_prior_date,
               			  		l_curr_page_time_id,
                                                l_curr_as_of_date,
						l_prev_page_time_id,
                                                l_prev_date,
                                                l_prior_prior_time_id,
                                                l_prior_prior_date,
				  		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_fst_crdt_type,
                                                l_fst_crdt_type,
						l_curr_as_of_date,
                                                l_prev_date,
                                                l_prior_prior_date,
                                                l_curr_page_time_id,
                                                l_prev_page_time_id,
                                                l_prior_prior_time_id,
						l_prodcat_id,
						l_sg_id_num,
						l_parent_sales_group_id,
						l_yes;

Line: 2807

					EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
					  USING l_cat_assign,l_cat_assign,
				  		l_curr_page_time_id,
                                                l_curr_as_of_date,
						l_prev_page_time_id,
                                                l_prev_date,
                                                l_prior_prior_time_id,
                                                l_prior_prior_date,
				  		l_curr_page_time_id,
                                                l_curr_as_of_date,
						l_prev_page_time_id,
                                                l_prev_date,
                                                l_prior_prior_time_id,
                                                l_prior_prior_date,
				  		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_fst_crdt_type,
                                                l_fst_crdt_type,
						l_curr_as_of_date,
                                                l_prev_date,
                                                l_prior_prior_date,
                                                l_curr_page_time_id,
                                                l_prev_page_time_id,
                                                l_prior_prior_time_id,
                                                l_prodcat_id,
						l_sg_id_num,
						l_sg_id_num,
						l_yes,
                                                l_resource_id;

Line: 2847

	 ' SELECT VIEWBY '||
	            ', VIEWBYID '||
	            ', SORTORDER '||
          	   ',NULL BIL_MEASURE1 '||
          	   ',NULL BIL_MEASURE2 '||
          	   ',NULL BIL_MEASURE3 '||
          	   ',NULL BIL_MEASURE4 '||
                   ',NULL BIL_MEASURE5 '||
          	   ',NULL BIL_MEASURE6 '||
          	   ',NULL BIL_MEASURE7 '||
          	   ',NULL BIL_MEASURE8 '||
          	   ',SUM(pipeline) BIL_MEASURE9 '||
          	   ',SUM(priorPipeline) BIL_MEASURE10 '||
          	   ',SUM(wtdPipeline) BIL_MEASURE11 '||
          	   ',SUM(priorWtdPipeline) BIL_MEASURE12 '||
          	   ',BIL_URL1 '||
                   ',DECODE(VIEWBY,'||':l_cat_assign'||',NULL,'''||l_pipe_url||''') BIL_URL2 '||
	 ' 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) SORTORDER, '||
              l_sql_stmnt3||
	      ',pcd.id VIEWBYID'||
	      ',decode(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL1 '||
              ',NULL BIL_URL2 '||
	 ' FROM '||l_sumry2||' sumry'||
            l_pipe_denorm||' '||
            ' '||l_where_clause3||' '||
            l_pipe_product_where_clause ||
            ' AND sumry.sales_group_id = :l_sg_id_num '||
            l_parent_sls_grp_where_clause;

Line: 2922

	              EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		            USING
                            l_cat_assign, l_cat_assign,
                            l_snapshot_date, l_period_type,
			    l_snapshot_date,
			    l_snapshot_date, l_period_type,
			    l_snapshot_date,
			    l_snapshot_date,
			    l_prodcat_id, l_sg_id_num;

Line: 2932

	              EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		            USING
                            l_cat_assign, l_cat_assign,
                            l_snapshot_date, l_period_type,
			    l_prev_snap_date, l_period_type,
			    l_snapshot_date, l_period_type,
			    l_prev_snap_date, l_period_type,
			    l_snapshot_date, l_prev_snap_date,
			    l_prodcat_id, l_sg_id_num;

Line: 2945

	  	      EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		            USING l_cat_assign, l_cat_assign,
                             l_snapshot_date, l_period_type,
			     l_snapshot_date,
			     l_snapshot_date, l_period_type,
			     l_snapshot_date,
			     l_snapshot_date,
                             l_prodcat_id, l_sg_id_num, l_resource_id;

Line: 2954

	  	      EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		            USING l_cat_assign, l_cat_assign,
                             l_snapshot_date, l_period_type,
			     l_prev_snap_date, l_period_type,
			     l_snapshot_date, l_period_type,
			     l_prev_snap_date, l_period_type,
			     l_snapshot_date, l_prev_snap_date,
                             l_prodcat_id, l_sg_id_num, l_resource_id;

Line: 2968

		   EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		        USING l_cat_assign,l_cat_assign,
                           l_snapshot_date, l_period_type,
			   l_snapshot_date,
			   l_snapshot_date, l_period_type,
			   l_snapshot_date,
			   l_snapshot_date,
			   l_prodcat_id, l_sg_id_num, l_parent_sales_group_id;

Line: 2977

		   EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		        USING l_cat_assign,l_cat_assign,
                           l_snapshot_date, l_period_type,
			   l_prev_snap_date, l_period_type,
			   l_snapshot_date, l_period_type,
			   l_prev_snap_date, l_period_type,
			   l_snapshot_date, l_prev_snap_date,
			   l_prodcat_id, l_sg_id_num, l_parent_sales_group_id;

Line: 2989

		    EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		         USING l_cat_assign, l_cat_assign,
                          l_snapshot_date, l_period_type,
			  l_snapshot_date,
			  l_snapshot_date, l_period_type,
			  l_snapshot_date,
			  l_snapshot_date,
                          l_prodcat_id, l_sg_id_num, l_sg_id_num, l_resource_id;

Line: 2998

		    EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
		         USING l_cat_assign, l_cat_assign,
                          l_snapshot_date, l_period_type,
			  l_prev_snap_date, l_period_type,
			  l_snapshot_date, l_period_type,
			  l_prev_snap_date, l_period_type,
			  l_snapshot_date, l_prev_snap_date,
                          l_prodcat_id, l_sg_id_num, l_sg_id_num, l_resource_id;

Line: 3031

x_custom_sql := 'SELECT '||
'VIEWBY, '||
'VIEWBYID, '||
'BIL_MEASURE1 ,  '||
'BIL_MEASURE23,  '||
'BIL_MEASURE24,  '||
'BIL_MEASURE2 ,  '||
'BIL_MEASURE25,  '||
'BIL_MEASURE26,  '||
'BIL_MEASURE27,  '||
'BIL_MEASURE3 ,  '||
'BIL_MEASURE4 ,  '||
'BIL_MEASURE5 ,  '||
'BIL_MEASURE6 ,  '||
'BIL_MEASURE7 ,  '||
'BIL_MEASURE28,  '||
'BIL_MEASURE8 ,  '||
'BIL_MEASURE9 ,  '||
'BIL_MEASURE29,  '||
'BIL_MEASURE10,  '||
'BIL_MEASURE11,  '||
'BIL_MEASURE30,  '||
'BIL_MEASURE12,  '||
'BIL_MEASURE13,  '||
'BIL_MEASURE14,  '||
'BIL_MEASURE15,  '||
'BIL_MEASURE16,  '||
'BIL_MEASURE17,  '||
'BIL_MEASURE18,  '||
'BIL_MEASURE19,  '||
'BIL_MEASURE20,  '||
'BIL_MEASURE21,  '||
'BIL_MEASURE22,  '||
'BIL_MEASURE32,  '||
'BIL_URL1,       '||
''''||l_url_str ||''' BIL_URL3,'||
'BIL_URL2        '||
  ' FROM ( '||
   l_outer_select ||
      ' FROM ( '||
        'SELECT VIEWBY, VIEWBYID, SORTORDER,  '||
        ' 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,  '||
        ' 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_MEASURE28) - SUM(BIL_MEASURE4) BIL_MEASURE13, '||
        ' SUM(BIL_MEASURE2)  - SUM(BIL_MEASURE5) BIL_MEASURE14, '||
        ' SUM(BIL_MEASURE3)  - SUM(BIL_MEASURE6) 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) '||')' ||
' WHERE '||l_null_removal_clause;

Line: 3098

x_custom_sql := 'SELECT '||
'VIEWBY, '||
'VIEWBYID, '||
'NULL BIL_MEASURE1 ,  '||
'BIL_MEASURE23,  '||
'BIL_MEASURE24,  '||
'NULL BIL_MEASURE2 ,  '||
'BIL_MEASURE25,  '||
'BIL_MEASURE26,  '||
'BIL_MEASURE27,  '||
'BIL_MEASURE3 ,  '||
'BIL_MEASURE4 ,  '||
'BIL_MEASURE5 ,  '||
'BIL_MEASURE6 ,  '||
'BIL_MEASURE1 BIL_MEASURE7, '||
'BIL_MEASURE28,  '||
'BIL_MEASURE2 BIL_MEASURE8, '||
'BIL_MEASURE9 ,  '||
'BIL_MEASURE29,  '||
'BIL_MEASURE10,  '||
'BIL_MEASURE11,  '||
'BIL_MEASURE30,  '||
'BIL_MEASURE12,  '||
'NULL BIL_MEASURE13,  '||
'NULL BIL_MEASURE14,  '||
'BIL_MEASURE15,  '||
'BIL_MEASURE16,  '||
'BIL_MEASURE13 BIL_MEASURE17, '||
'BIL_MEASURE14 BIL_MEASURE18, '||
'BIL_MEASURE19,  '||
'BIL_MEASURE20,  '||
'BIL_MEASURE21,  '||
'BIL_MEASURE22,  '||
'BIL_MEASURE32,  '||
'BIL_URL1,       '||
''''||l_url_str ||''' BIL_URL3,'||
'BIL_URL2        '||
  ' FROM ( '||
   l_outer_select ||
      ' FROM ( '||
        'SELECT VIEWBY, VIEWBYID, SORTORDER,  '||
        ' 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,  '||
        ' 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_MEASURE28) - SUM(BIL_MEASURE4) BIL_MEASURE13, '||
        ' SUM(BIL_MEASURE2)  - SUM(BIL_MEASURE5) BIL_MEASURE14, '||
        ' SUM(BIL_MEASURE3)  - SUM(BIL_MEASURE6) 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) '||')' ||
' WHERE '||l_null_removal_clause;