[Home] [Help]
PACKAGE BODY: APPS.BIL_BI_FCST_MGMT_RPTS_PKG
Source
1 PACKAGE BODY bil_bi_fcst_mgmt_rpts_pkg AS
2 /* $Header: bilbfsb.pls 120.4 2005/09/01 03:19:23 vchahal noship $ */
3 g_pkg VARCHAR2(100);
4 g_sch_name VARCHAR2(100);
5 /*******************************************************************************
6 * Name : Procedure BIL_BI_FRCST_OVERVIEW
7 * Author : Prasanna Patil
8 * Date : December 15, 2003
9 * Purpose : Forecast Overview.
10 *
11 * Copyright (c) 2003 Oracle Corporation
12 *
13 * Parameters
14 * p_page_parameter_tbl PL/SQL table containing dimension parameters
15 * x_custom_sql string containing sql query
16 * x_custom_attr PL/SQL table containing our bind vars
17 *
18 * Date Author Description
19 * ---- ------ -----------
20 * 12/15/03 ppatil Initial version
21 * 02/06/03 ppatil Modified for View By Product Category query.
22 ******************************************************************************/
23 PROCEDURE BIL_BI_FRCST_OVERVIEW ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
24 ,x_custom_sql OUT NOCOPY VARCHAR2
25 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL )
26
27 IS
28
29 l_period_type VARCHAR2(50);
30 l_sg_id VARCHAR2(50);
31 l_conv_rate_selected VARCHAR2(50);
32 l_curr_page_time_id NUMBER;
33 l_prev_page_time_id NUMBER;
34 l_region_id VARCHAR2(50);
35 l_comp_type VARCHAR2(50);
36 l_parameter_valid BOOLEAN;
37 l_bitand_id VARCHAR2(50);
38 l_calendar_id VARCHAR2(50);
39 l_curr_as_of_date DATE;
40 l_prev_date DATE;
41 l_page_period_type VARCHAR2(50);
42 l_bis_sysdate DATE;
43 l_fii_struct VARCHAR2(50);
44 l_record_type_id NUMBER;
45 l_sql_error_msg VARCHAR2(1000);
46 l_sql_error_desc VARCHAR2(1000);
47 l_sg_id_num NUMBER;
48 l_fst_crdt_type VARCHAR2(50);
49 l_inner_where_clause VARCHAR2(1000);
50 l_sql_stmnt1 VARCHAR2(32000);
51 l_sql_stmnt2 VARCHAR2(32000);
52 l_sql_stmnt3 VARCHAR2(32000);
53
54 /* thefollwoing 3 variables are used only for VB=SG*/
55 l_sql_stmnt1_1 VARCHAR2(32000);
56 l_sql_stmnt2_1 VARCHAR2(32000);
57 l_sql_stmnt3_1 VARCHAR2(32000);
58
59 l_sql_stmnt4 VARCHAR2(32000);
60 l_custom_sql VARCHAR2(32000);
61 l_insert_stmnt VARCHAR2(5000);
62 l_outer_select VARCHAR2(5000);
63 l_where_clause1 VARCHAR2(5000);
64 l_where_clause2 VARCHAR2(5000);
65 l_where_clause3 VARCHAR2(5000);
66 l_where_clause4 VARCHAR2(5000);
67 l_prodcat_id VARCHAR2(20);
68 l_viewby VARCHAR2(200);
69 l_resource_id VARCHAR2(20);
70 l_url_str VARCHAR2(1000);
71 l_drill_str VARCHAR2(1000);
72 l_cat_assign VARCHAR2(1000);
73 l_denorm VARCHAR2(50);
74 l_denorm1 VARCHAR2(50);
75 l_cat_url VARCHAR2(1000);
76 l_sumry VARCHAR2(50);
77 l_sumry1 VARCHAR2(50);
78 l_sumry2 VARCHAR2(50);
79 l_product_where_clause1 VARCHAR2(2000);
80 l_product_where_clause2 VARCHAR2(2000);
81 l_yes VARCHAR2(1);
82 l_no VARCHAR2(1);
83 l_assign_cat BOOLEAN;
84 l_dummy_cnt INTEGER;
85 l_snap_date DATE;
86 -- l_show_pipe_info BOOLEAN;
87 l_proc VARCHAR2(100);
88 l_null_removal_clause VARCHAR2(1000);
89 l_parent_sales_group_id NUMBER;
90 l_parent_sls_grp_where_clause VARCHAR2(1000);
91 l_pipe_product_where_clause VARCHAR2(1000);
92 l_pipe_denorm VARCHAR2(100);
93
94 l_pc_select VARCHAR2(32000);
95 l_unassigned_value VARCHAR2(100);
96 l_prodcat_flag VARCHAR2(200);
97
98 l_currency_suffix VARCHAR2(5);
99 l_lookup_type VARCHAR2(30);
100 l_lookup_code VARCHAR2(30);
101 l_lang VARCHAR2(30);
102
103 l_ind NUMBER;
104 l_str VARCHAR2(4000);
105 l_len NUMBER;
106
107 l_prev_pipe_amt VARCHAR2(300);
108 l_prv_wt_pipe_amt VARCHAR2(300);
109 l_column_type VARCHAR2(1000);
110 l_snapshot_date DATE;
111 l_open_mv_new VARCHAR2(1000);
112 l_open_mv_new1 VARCHAR2(1000);
113 l_prev_snap_date DATE;
114 l_pipe_select1 VARCHAR2(4000);
115 l_pipe_select2 VARCHAR2(4000);
116 l_pipe_select3 VARCHAR2(4000);
117 l_pipe_select4 VARCHAR2(4000);
118 l_pipe_select5 VARCHAR2(4000);
119 l_pc_pipe_select1 VARCHAR2(4000);
120 l_pc_pipe_select2 VARCHAR2(4000);
121 l_pc_pipe_select3 VARCHAR2(4000);
122 l_pc_pipe_select4 VARCHAR2(4000);
123 l_pc_pipe_select5 VARCHAR2(4000);
124 l_inner_where_pipe VARCHAR2(4000);
125
126
127
128 BEGIN
129
130 g_pkg := 'bil.patch.115.sql.BIL_BI_FCST_MGMT_RPTS_PKG.';
131 l_region_id := 'BIL_BI_FRCST_OVERVIEW';
132 l_parameter_valid := FALSE;
133 l_yes := 'Y';
134 l_no := 'N';
135 l_proc := 'BIL_BI_FRCST_OVERVIEW.';
136 g_sch_name := 'BIL';
137
138
139 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
140
141 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
142 MODULE => g_pkg || l_proc || 'begin',
143 MESSAGE => 'Start of Procedure '||l_proc);
144
145 END IF;
146
147
148 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
149
150 l_lookup_type := 'BIL_BI_LOOKUPS';
151 l_lookup_code := 'ASSIGN_CATEG';
152 l_lang := USERENV('LANG');
153
154 SELECT Meaning INTO l_cat_assign
155 FROM FND_LOOKUP_VALUES
156 WHERE LOOKUP_TYPE = l_lookup_type
157 AND LOOKUP_CODE = l_lookup_code
158 AND LANGUAGE = l_lang;
159
160 BIL_BI_UTIL_PKG.GET_PAGE_PARAMS(p_page_parameter_tbl => p_page_parameter_tbl
161 ,p_region_id => l_region_id
162 ,x_period_type => l_period_type
163 ,x_conv_rate_selected => l_conv_rate_selected
164 ,x_sg_id => l_sg_id
165 ,x_parent_sg_id => l_parent_sales_group_id
166 ,x_resource_id => l_resource_id
167 ,x_prodcat_id => l_prodcat_id
168 ,x_curr_page_time_id => l_curr_page_time_id
169 ,x_prev_page_time_id => l_prev_page_time_id
170 ,x_comp_type => l_comp_type
171 ,x_parameter_valid => l_parameter_valid
172 ,x_as_of_date => l_curr_as_of_date
173 ,x_page_period_type => l_page_period_type
174 ,x_prior_as_of_date => l_prev_date
175 ,x_record_type_id => l_record_type_id
176 ,x_viewby => l_viewby );
177
178 /*
179 BIL_BI_UTIL_PKG.GET_LATEST_SNAP_DATE(p_page_parameter_tbl => p_page_parameter_tbl
180 ,p_as_of_date => l_curr_as_of_date
181 ,p_period_type => NULL
182 ,x_snapshot_date => l_snap_date);
183 */
184
185
186 /*** Query column mapping ******************************************************
187 * Internal Name Grand Total Region Item Name
188 * BIL_MEASURE1 BIL_MEASURE13 Forecast
189 * BIL_MEASURE2 BIL_MEASURE14 Prior Forecast
190 * BIL_MEASURE3 BIL_MEASURE15 Change
191 * BIL_MEASURE4 BIL_MEASURE16 Weighted Pipeline
192 * BIL_MEASURE5 BIL_MEASURE17 Prior Weighted Pipeline
193 * BIL_MEASURE6 BIL_MEASURE18 Change
194 * BIL_MEASURE7 BIL_MEASURE19 Pipeline
195 * BIL_MEASURE8 BIL_MEASURE20 Prior Pipeline
196 * BIL_MEASURE9 BIL_MEASURE21 Change
197 * BIL_MEASURE10 BIL_MEASURE22 Won
198 * BIL_MEASURE11 BIL_MEASURE23 Prior Won
199 * BIL_MEASURE12 BIL_MEASURE24 Change
200 * BIL_MEASURE25 BIL_MEASURE26 Forecast - used for 2nd Graph
201 *******************************************************************************/
202
203 IF l_parameter_valid THEN
204
205 l_drill_str:=BIL_BI_UTIL_PKG.GET_DRILL_LINKS(p_view_by => l_viewby
206 ,p_salesgroup_id => l_sg_id
207 ,p_resource_id => l_resource_id);
208
209 IF l_prodcat_id IS NULL THEN
210 l_prodcat_id := 'All';
211 ELSE
212 l_prodcat_id := to_number(REPLACE(l_prodcat_id,'''',''));
213 END IF;
214
215 IF l_conv_rate_selected = 0 THEN
216 l_currency_suffix := '_s';
217 ELSE
218 l_currency_suffix := '';
219 END IF;
220
221 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
222
223 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
224 MODULE => g_pkg || l_proc,
225 MESSAGE => 'l_parameter_valid = true');
226
227 END IF;
228
229
230
231 /* Get the Prefix for the Open amt based upon Period Type and Compare To Params */
232
233
234 l_prev_pipe_amt := BIL_BI_UTIL_PKG.GET_PIPE_COL_NAMES(
235 p_period_type => l_page_period_type ,
236 p_compare_to => l_comp_type,
237 p_column_type => 'P',
238 p_curr_suffix => l_currency_suffix
239 );
240
241
242
243 l_prv_wt_pipe_amt := BIL_BI_UTIL_PKG.GET_PIPE_COL_NAMES(
244 p_period_type => l_page_period_type ,
245 p_compare_to => l_comp_type,
246 p_column_type => 'W',
247 p_curr_suffix => l_currency_suffix
248 );
249
250
251 /* Use the BIL_BI_UTIL_PKG.GET_PIPE_MV proc to get the MV name and snap date for Pipeline/Open Amts. */
252
253
254
255 BIL_BI_UTIL_PKG.GET_PIPE_MV(
256 p_asof_date => l_curr_as_of_date ,
257 p_period_type => l_page_period_type ,
258 p_compare_to => l_comp_type ,
259 p_prev_date => l_prev_date,
260 p_page_parameter_tbl => p_page_parameter_tbl,
261 x_pipe_mv => l_open_mv_new ,
262 x_snapshot_date => l_snapshot_date ,
263 x_prev_snap_date => l_prev_snap_date
264 );
265
266
267
268 l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1 (VIEWBY, VIEWBYID, SORTORDER,BIL_MEASURE28,'||
269 'BIL_MEASURE2,BIL_MEASURE4,BIL_MEASURE5,BIL_MEASURE7,BIL_MEASURE8, BIL_MEASURE10,'||
270 'BIL_MEASURE11, BIL_URL1, BIL_URL2)';
271
272 l_url_str:='pFunctionName=BIL_BI_FSTOVER_R&pParamIds=Y&VIEW_BY='||l_viewby||'&VIEW_BY_NAME=VIEW_BY_ID';
273 l_outer_select := 'SELECT VIEWBY '||
274 ',VIEWBYID '||
275 ',BIL_MEASURE28 BIL_MEASURE1 '||
276 ',BIL_MEASURE2 '||
277 ',(BIL_MEASURE28-BIL_MEASURE2)/ABS(DECODE(BIL_MEASURE2,0,null,BIL_MEASURE2))*100 BIL_MEASURE3 '||
278 ',BIL_MEASURE10 BIL_MEASURE27 '||
279 ',BIL_MEASURE4 '||
280 ',BIL_MEASURE5 '||
281 ',(BIL_MEASURE4-BIL_MEASURE5)/ABS(DECODE(BIL_MEASURE5,0,null,BIL_MEASURE5))*100 BIL_MEASURE6 '||
282 ',BIL_MEASURE28 BIL_MEASURE25 '||
283 ',BIL_MEASURE7 '||
284 ',BIL_MEASURE8 '||
285 ',(BIL_MEASURE7-BIL_MEASURE8)/ABS(DECODE(BIL_MEASURE8, 0, null, BIL_MEASURE8))*100 BIL_MEASURE9 '||
286 ',BIL_MEASURE10 '||
287 ',BIL_MEASURE11 '||
288 ',(BIL_MEASURE10-BIL_MEASURE11)/ABS(DECODE(BIL_MEASURE11, 0 ,null, BIL_MEASURE11))*100 BIL_MEASURE12 '||
289 ',SUM(BIL_MEASURE28) OVER() BIL_MEASURE13 '||
290 ',SUM(BIL_MEASURE2) OVER() BIL_MEASURE14 '||
291 ',(SUM(BIL_MEASURE28) OVER() - SUM(BIL_MEASURE2) OVER())/ABS(DECODE(SUM(BIL_MEASURE2) OVER(), 0, null, '||
292 'SUM(BIL_MEASURE2) OVER()))*100 BIL_MEASURE15 '||
293 ',SUM(BIL_MEASURE4) OVER() BIL_MEASURE16 '||
294 ',SUM(BIL_MEASURE5) OVER() BIL_MEASURE17 '||
295 ',(SUM(BIL_MEASURE4) OVER() - SUM(BIL_MEASURE5) OVER())/ABS(DECODE(SUM(BIL_MEASURE5) OVER(), 0, null, '||
296 'SUM(BIL_MEASURE5) OVER()))*100 BIL_MEASURE18 '||
297 ',SUM(BIL_MEASURE7) OVER() BIL_MEASURE19 '||
298 ',SUM(BIL_MEASURE8) OVER() BIL_MEASURE20 '||
299 ',(SUM(BIL_MEASURE7) OVER() - SUM(BIL_MEASURE8) OVER())/ABS(DECODE(SUM(BIL_MEASURE8) OVER(), 0, null, '||
300 'SUM(BIL_MEASURE8) OVER()))*100 BIL_MEASURE21'||
301 ',SUM(BIL_MEASURE10) OVER() BIL_MEASURE22 '||
302 ',SUM(BIL_MEASURE11) OVER() BIL_MEASURE23 '||
303 ',(SUM(BIL_MEASURE10) OVER() - SUM(BIL_MEASURE11) OVER())/ABS(DECODE(SUM(BIL_MEASURE11) OVER(), 0, null, '||
304 'SUM(BIL_MEASURE11) OVER()))*100 BIL_MEASURE24'||
305 ',SUM(BIL_MEASURE28) OVER() BIL_MEASURE26'||
306 ',SUM(BIL_MEASURE10) OVER() BIL_MEASURE28 '||
307 ', BIL_URL1 '||
308 ', BIL_URL2 '||
309 ',DECODE('''||l_curr_as_of_date||''',TRUNC(SYSDATE),DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'','||
310 'DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=PIPELINE'''||'),'||
311 'DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=PIPELINE'''||')),NULL) BIL_URL3 '||
312 ',DECODE('''||l_viewby||''',''ORGANIZATION+JTF_ORG_SALES_GROUP'','||
313 'DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=WON'''||'),'||
314 'DECODE(BIL_URL1,NULL,NULL,BIL_URL1||'||'''BIL_DIMENSION1=WON'''||')) BIL_URL4 ';
315
316
317 l_sql_stmnt1 := '(case when sumry.effective_time_id = :l_curr_page_time_id AND '||
318 'cal.report_date =:l_curr_as_of_date '||
319 'then sumry.forecast_amt'||l_currency_suffix||' else NULL end) AS frcst '||
320 ',(case when sumry.effective_time_id = :l_prev_page_time_id AND '||
321 'cal.report_date =:l_prev_date '||
322 'then sumry.forecast_amt'||l_currency_suffix||' else NULL end) AS priorFrcst '||
323 ',NULL AS wtdPipeline '||
324 ',NULL AS priorWtdPipeline '||
325 ',NULL AS pipeline '||
326 ',NULL AS priorPipeline '||
327 ',NULL AS won '||
328 ',NULL AS priorWon ';
329
330 l_sql_stmnt1_1 := 'SUM((case when sumry.effective_time_id = :l_curr_page_time_id AND '||
331 'cal.report_date =:l_curr_as_of_date '||
332 'then sumry.forecast_amt'||l_currency_suffix||' else NULL end)) AS frcst '||
333 ',SUM((case when sumry.effective_time_id = :l_prev_page_time_id AND '||
334 'cal.report_date =:l_prev_date '||
335 'then sumry.forecast_amt'||l_currency_suffix||' else NULL end)) AS priorFrcst '||
336 ',NULL AS wtdPipeline '||
337 ',NULL AS priorWtdPipeline '||
338 ',NULL AS pipeline '||
342
339 ',NULL AS priorPipeline '||
340 ',NULL AS won '||
341 ',NULL AS priorWon ';
343 l_sql_stmnt4 :='(case when sumry.effective_time_id = :l_curr_page_time_id AND '||
344 'cal.report_date =:l_curr_as_of_date '||
345 'then decode(sumry.salesrep_id, NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||') '||
346 ' else NULL end) AS frcst '||
347 ',(case when sumry.effective_time_id = :l_prev_page_time_id AND '||
348 'cal.report_date =:l_prev_date '||
349 'then decode(sumry.salesrep_id, NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||') '||
350 'else NULL end) AS priorFrcst '||
351 ',NULL AS wtdPipeline '||
352 ',NULL AS priorWtdPipeline '||
353 ',NULL AS pipeline '||
354 ',NULL AS priorPipeline '||
355 ',NULL AS won '||
356 ',NULL AS priorWon ';
357 l_where_clause1 := ' WHERE sumry.TXN_TIME_ID = cal.TIME_ID '||
358 'AND sumry.TXN_PERIOD_TYPE_ID = cal.PERIOD_TYPE_ID '||
359 'AND bitand(cal.record_type_id, :l_bitand_id)= :l_bitand_id '||
360 'AND sumry.EFFECTIVE_PERIOD_TYPE_ID = :l_period_type '||
361 'AND NVL(sumry.credit_type_id, :l_fst_crdt_type) = :l_fst_crdt_type '||
362 'AND cal.report_date in (:l_curr_as_of_date, :l_prev_date) '||
363 'AND sumry.effective_time_id in (:l_curr_page_time_id, :l_prev_page_time_id) ';
364
365 l_sql_stmnt2 :=' NULL AS frcst '||
366 ', NULL AS priorFrcst '||
367 ', NULL AS wtdPipeline '||
368 ', NULL priorWtdPipeline '||
369 ', NULL pipeline '||
370 ', NULL priorPipeline '||
371 ',(case when cal.report_date =:l_curr_as_of_date '||
372 'then sumry.won_opty_amt'||l_currency_suffix||' else NULL end) AS won '||
373 ', (case when cal.report_date =:l_prev_date '||
374 'then sumry.won_opty_amt'||l_currency_suffix||' else NULL end) AS priorWon';
375
376 l_sql_stmnt2_1 :=' NULL AS frcst '||
377 ', NULL AS priorFrcst '||
378 ', NULL AS wtdPipeline '||
379 ', NULL priorWtdPipeline '||
380 ', NULL pipeline '||
381 ', NULL priorPipeline '||
382 ', SUM((case when cal.report_date =:l_curr_as_of_date '||
383 'then sumry.won_opty_amt'||l_currency_suffix||' else NULL end)) AS won '||
384 ', SUM((case when cal.report_date =:l_prev_date '||
385 'then sumry.won_opty_amt'||l_currency_suffix||' else NULL end)) AS priorWon';
386
387 l_where_clause2:=' WHERE sumry.effective_time_id = cal.time_id '||
388 'AND sumry.effective_period_type_id = cal.PERIOD_TYPE_ID '||
389 'AND bitand(cal.record_type_id, :l_record_type_id )= :l_record_type_id '||
390 'AND cal.report_date in (:l_curr_as_of_date, :l_prev_date) ';
391
392 l_pipe_select1 := ' NULL AS frcst '||
393 ',NULL AS priorFrcst '||
394 ',SUM((case when :l_snapshot_date = sumry.snap_date then '||
395 'decode(:l_period_type, '||
396 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
397 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
398 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
399 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
400 ')'||
401 'end)) AS wtdPipeline ';
402
403 IF (l_open_mv_new = 'BIL_BI_PIPE_G_MV') THEN
404
405 l_pipe_select2 := ',SUM((case when :l_prev_snap_date = sumry.snap_date then '||
406 'decode(:l_period_type, '||
407 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
408 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
409 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
410 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
411 ')'||
412 'end)) AS priorWtdPipeline ';
413 ELSE
414 l_pipe_select2 := ' , sum((CASE WHEN sumry.snap_date = :l_snapshot_date THEN '||
418 END IF;
415 ''||l_prv_wt_pipe_amt||' '||
416 ' ELSE NULL '||
417 ' END)) AS priorWtdPipeline ';
419
420 l_pipe_select3 := ',SUM((case when :l_snapshot_date = sumry.snap_date then '||
421 'decode(:l_period_type, '||
422 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
423 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
424 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
425 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
426 ')'||
427 'end)) AS pipeline ';
428
429 IF (l_open_mv_new = 'BIL_BI_PIPE_G_MV') THEN
430 l_pipe_select4 := ',SUM((case when :l_prev_snap_date = sumry.snap_date then '||
431 'decode(:l_period_type, '||
432 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
433 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
434 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
435 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
436 ')'||
437 'end)) AS priorPipeline ';
438 ELSE
439 l_pipe_select4 := ' , sum((CASE WHEN sumry.snap_date =:l_snapshot_date THEN '||
440 ''||l_prev_pipe_amt||' '||
441 ' ELSE NULL '||
442 ' END)) AS priorPipeline ';
443
444 END IF;
445
446 l_pipe_select5 :=',NULL AS won '||
447 ',NULL AS priorWon ';
448
449 l_sql_stmnt3_1 := l_pipe_select1 || l_pipe_select2 || l_pipe_select3 || l_pipe_select4 || l_pipe_select5 ;
450
451
452 /*
453 l_sql_stmnt3_1 :=' NULL AS frcst '||
454 ',NULL AS priorFrcst '||
455 ',SUM((case when :l_snap_date = sumry.snap_date then '||
456 'decode(:l_period_type, '||
457 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
458 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
459 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
460 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
461 ')'||
462 'end)) AS wtdPipeline '||
463 ',SUM((case when :l_prev_date = sumry.snap_date then '||
464 'decode(:l_period_type, '||
465 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
466 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
467 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
468 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
469 ')'||
470 'end)) AS priorWtdPipeline '||
471 ',SUM((case when :l_snap_date = sumry.snap_date then '||
472 'decode(:l_period_type, '||
473 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
474 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
475 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
476 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
477 ')'||
478 'end)) AS pipeline '||
479 ',SUM((case when :l_prev_date = sumry.snap_date then '||
480 'decode(:l_period_type, '||
481 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
482 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
483 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
484 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
485 ')'||
486 'end)) AS priorPipeline '||
487 ',NULL AS won '||
488 ',NULL AS priorWon ';
489 */
490
491 l_pc_pipe_select1 := ' NULL AS frcst '||
492 ',NULL AS priorFrcst '||
493 ',((case when :l_snapshot_date = sumry.snap_date then '||
494 'decode(:l_period_type, '||
495 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
496 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
497 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
498 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
499 ')'||
500 'end)) AS wtdPipeline ';
501
502 IF (l_open_mv_new = 'BIL_BI_PIPE_G_MV') THEN
503
504 l_pc_pipe_select2 := ',((case when :l_prev_snap_date = sumry.snap_date then '||
505 'decode(:l_period_type, '||
506 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
507 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
508 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
509 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
510 ')'||
511 'end)) AS priorWtdPipeline ';
512 ELSE
513 l_pc_pipe_select2 := ' , ((CASE WHEN sumry.snap_date = :l_snapshot_date THEN '||
514 ''||l_prv_wt_pipe_amt||' '||
515 ' ELSE NULL '||
516 ' END)) AS priorWtdPipeline ';
517 END IF;
518
519 l_pc_pipe_select3 := ', ((case when :l_snapshot_date = sumry.snap_date then '||
520 'decode(:l_period_type, '||
521 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
522 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
523 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
524 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
525 ')'||
526 'end)) AS pipeline ';
527
528 IF (l_open_mv_new = 'BIL_BI_PIPE_G_MV') THEN
529 l_pc_pipe_select4 := ', ((case when :l_prev_snap_date = sumry.snap_date then '||
530 'decode(:l_period_type, '||
534 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
531 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
532 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
533 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
535 ')'||
536 'end)) AS priorPipeline ';
537 ELSE
538 l_pc_pipe_select4 := ' , ((CASE WHEN sumry.snap_date =:l_snapshot_date THEN '||
539 ''||l_prev_pipe_amt||' '||
540 ' ELSE NULL '||
541 ' END)) AS priorPipeline ';
542
543 END IF;
544
545 l_pc_pipe_select5 :=',NULL AS won '||
546 ',NULL AS priorWon ';
547
548 l_sql_stmnt3 := l_pc_pipe_select1 ||
549 l_pc_pipe_select2 ||
550 l_pc_pipe_select3 ||
551 l_pc_pipe_select4 || l_pc_pipe_select5 ;
552
553
554 /*
555 l_sql_stmnt3 :=' NULL AS frcst '||
556 ',NULL AS priorFrcst '||
557 ',(case when :l_snap_date = sumry.snap_date then '||
558 'decode(:l_period_type, '||
559 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
560 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
561 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
562 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
563 ')'||
564 'end) AS wtdPipeline '||
565 ',(case when :l_prev_date = sumry.snap_date then '||
566 'decode(:l_period_type, '||
567 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
568 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
569 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
570 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
571 ')'||
572 'end) AS priorWtdPipeline '||
573 ',(case when :l_snap_date = sumry.snap_date then '||
574 'decode(:l_period_type, '||
575 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
576 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
577 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
578 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
579 ')'||
580 'end) AS pipeline '||
581 ',(case when :l_prev_date = sumry.snap_date then '||
582 'decode(:l_period_type, '||
583 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
584 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
585 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
586 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
587 ')'||
588 'end) AS priorPipeline '||
589 ',NULL AS won '||
590 ',NULL AS priorWon ';
591
592 */
593
594
595 -- l_where_clause3 := ' WHERE sumry.snap_date in (:l_snap_date, :l_prev_date) ' ;
596
597
598 IF (l_open_mv_new = 'BIL_BI_PIPE_G_MV') THEN
599 l_where_clause3 := ' WHERE sumry.snap_date in (:l_snapshot_date, :l_prev_snap_date) ';
600 ELSE
601 l_where_clause3 := ' WHERE sumry.snap_date in (:l_snapshot_date) ';
602 END IF;
603
604 BIL_BI_UTIL_PKG.GET_FORECAST_PROFILES( x_fstcrdttype => l_fst_crdt_type );
605
606 BIL_BI_UTIL_PKG.GET_GLOBAL_CONTS(x_bitand_id =>l_bitand_id,
607 x_calendar_id =>l_calendar_id,
608 x_curr_date =>l_bis_sysdate,
609 x_fii_struct =>l_fii_struct);
610
611 l_sg_id_num := TO_NUMBER(REPLACE(l_sg_id, ''''));
612 l_null_removal_clause := 'NOT(BIL_MEASURE1 IS NULL AND BIL_MEASURE4 IS NULL AND '||
613 ' BIL_MEASURE7 IS NULL AND '||
614 ' BIL_MEASURE10 IS NULL) ';
615 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP1';
616
617
618 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
619
620 l_sql_error_desc :=
621 'l_viewby => '||l_viewby||', '||
622 'l_curr_page_time_id => '|| l_curr_page_time_id ||', ' ||
623 'l_prev_page_time_id => '|| l_prev_page_time_id ||', ' ||
624 'l_curr_as_of_date => '|| l_curr_as_of_date ||', ' ||
625 'l_prev_date => '|| l_prev_date ||', ' ||
626 'l_conv_rate_selected => '|| l_conv_rate_selected ||', ' ||
627 'l_bitand_id => '|| l_bitand_id ||', ' ||
628 'l_period_type => '|| l_period_type ||', ' ||
629 'l_sg_id_num => '|| l_sg_id_num ||', ' ||
630 'l_resource_id => '||l_resource_id||', '||
631 'l_fst_crdt_type => '|| l_fst_crdt_type ||', ' ||
632 'l_calendar_id => '|| l_calendar_id;
633
634
635 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
636 MODULE => g_pkg || l_proc || 'l_sql_error_desc',
637 MESSAGE => l_sql_error_desc);
638
639 END IF;
640
641
642
643 BIL_BI_UTIL_PKG.GET_PC_NOROLLUP_WHERE_CLAUSE(
644 p_prodcat => l_prodcat_id,
645 p_viewby => l_viewby,
646 x_denorm => l_pipe_denorm,
647 x_where_clause => l_pipe_product_where_clause);
648
649
650 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
651
652 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
653 MODULE => g_pkg || l_proc || ' l_viewby =>'||l_viewby,
654 MESSAGE => ' Prod cat '||l_prodcat_id);
655
659
656 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
657 MODULE => g_pkg || l_proc || ' l_pipe_prod_where =>',
658 MESSAGE => ' l_pipe_product_where_clause '||l_pipe_product_where_clause);
660 END IF;
661
662
663 CASE l_viewby
664 WHEN 'ORGANIZATION+JTF_ORG_SALES_GROUP' THEN
665 IF l_prodcat_id = 'All' THEN
666 l_pipe_product_where_clause := '';
667 l_denorm1 := '';
668 l_sumry := 'BIL_BI_FST_G_MV';
669 l_sumry1 := 'BIL_BI_OPTY_G_MV';
670 -- l_sumry2 := 'BIL_BI_PIPE_G_MV';
671 l_sumry2 := l_open_mv_new;
672 l_where_clause3 := l_where_clause3||' AND sumry.grp_total_flag = 1 ';
673 l_prodcat_flag := ' ';
674 ELSE
675 l_sumry := 'BIL_BI_FST_PG_MV';
676 l_sumry1 := 'BIL_BI_OPTY_PG_MV';
677 -- l_sumry2 := 'BIL_BI_PIPE_G_MV';
678 l_sumry2 := l_open_mv_new;
679 l_denorm := ' ';
680
681 l_product_where_clause2 := ' AND sumry.product_category_id = :l_productcat_id ';
682 l_where_clause3 := l_where_clause3||' AND sumry.grp_total_flag = 0 ';
683 l_prodcat_flag := 'sumry.product_category_id, ';
684 END IF;
685
686 IF l_resource_id IS NULL THEN
687 l_custom_sql:='SELECT DECODE(tmp1.salesrep_id, NULL, grptl.group_name,restl.resource_name) VIEWBY '||
688 ',DECODE(tmp1.salesrep_id, NULL, to_char(tmp1.sales_group_id),'||
689 ' tmp1.salesrep_id||''.''||tmp1.sales_group_id ) VIEWBYID '||
690 ', SORTORDER '||
691 ',BIL_MEASURE28
692 , BIL_MEASURE2, BIL_MEASURE4, BIL_MEASURE5, BIL_MEASURE7,
693 BIL_MEASURE8, BIL_MEASURE10, BIL_MEASURE11,
694 DECODE(tmp1.salesrep_id, NULL, '''||l_url_str||''', NULL) BIL_URL1,
695 DECODE(tmp1.salesrep_id, NULL, NULL,'''||l_drill_str||''') BIL_URL2 FROM (
696 SELECT /*+ NO_MERGE */ salesrep_id, sales_group_id,
697 sortorder, parent_sales_group_id
698 ,SUM(frcst) BIL_MEASURE28 '||
699 ',SUM(priorFrcst) BIL_MEASURE2 '||
700 ',SUM(wtdPipeline) BIL_MEASURE4 '||
701 ',SUM(priorWtdPipeline) BIL_MEASURE5 '||
702 ',SUM(pipeline) BIL_MEASURE7 '||
703 ',SUM(priorPipeline) BIL_MEASURE8 '||
704 ',SUM(won) BIL_MEASURE10 '||
705 ',SUM(priorWon) BIL_MEASURE11 '||
706 ', NULL BIL_URL1 '||
707 ', NULL BIL_URL2 '||
708 ' FROM '||
709 '( '||
710 'SELECT /*+ LEADING(cal) */ '||
711 ' '||l_prodcat_flag||'
712 decode(sumry.salesrep_id, NULL,1,2) sortorder, '||
713 'sumry.salesrep_id, '||
714 'sumry.sales_group_id, '||
715 'sumry.parent_sales_group_id parent_sales_group_id, '||
716 l_sql_stmnt1_1||
717 ' FROM '||l_fii_struct||' cal, '||l_sumry||' sumry '||
718 l_denorm1||
719 l_where_clause1||
720 ' AND sumry.parent_sales_group_id = :l_sg_id_num '||
721 ' AND cal.xtd_flag = :l_yes '||
722 ' '||l_product_where_clause2||
723 ' GROUP BY '||
724 ' decode(sumry.salesrep_id, NULL,1,2),sumry.salesrep_id,sumry.sales_group_id,'||
725 l_prodcat_flag||' sumry.parent_sales_group_id '||
726 ' UNION ALL '||
727 ' select '||l_prodcat_flag||' sortorder, salesrep_id,
728 sales_group_id, parent_sales_group_id,
729 sum(frcst) frcst, sum(priorFrcst) priorFrcst,
730 sum(wtdPipeline) wtdPipeline,
731 sum(priorWtdPipeline) priorWtdPipeline,
732 sum(pipeline) pipeline,
733 sum(priorPipeline) priorPipeline, sum(won) won,
734 sum(priorWon) priorWon from (
735 SELECT /*+ LEADING(cal) */ '||
736 ' '||l_prodcat_flag||'
737 decode(sumry.salesrep_id, NULL,1,2) sortorder, '||
738 'sumry.salesrep_id, '||
739 'sumry.sales_group_id, '||
740 'sumry.parent_sales_group_id parent_sales_group_id, '||
741 l_sql_stmnt2_1||
742 ' FROM '||l_fii_struct||' cal, '||l_sumry1||' sumry '||
743 l_where_clause2||
744 ' AND sumry.parent_sales_group_id = :l_sg_id_num '||
745 ' AND cal.xtd_flag = :l_yes '||
746 ' GROUP BY '||
747 ' decode(sumry.salesrep_id, NULL,1,2),sumry.salesrep_id,sumry.sales_group_id,'||
748 l_prodcat_flag||' sumry.parent_sales_group_id ';
749
750 l_custom_sql:= l_custom_sql||
751 ' UNION ALL '||
752 ' SELECT '||l_prodcat_flag||'
753 decode(sumry.salesrep_id, NULL,1,2) sortorder, '||
754 'sumry.salesrep_id, '||
755 'sumry.sales_group_id, '||
756 'sumry.parent_sales_group_id parent_sales_group_id, '||
757 l_sql_stmnt3_1||
758 ' FROM '||l_sumry2||' sumry '||
759 l_where_clause3||
760 ' AND sumry.parent_sales_group_id = :l_sg_id_num '||
761 ' GROUP BY '||
762 ' decode(sumry.salesrep_id, NULL,1,2),sumry.salesrep_id,sumry.sales_group_id,'||
763 l_prodcat_flag||' sumry.parent_sales_group_id ';
764
765 l_custom_sql:= l_custom_sql||
766 ' ) sumry '||l_pipe_denorm||' where
770 )
767 1=1 '||l_pipe_product_where_clause||
768 ' GROUP BY sortorder,salesrep_id,sales_group_id,'||l_prodcat_flag||' parent_sales_group_id
769 ) group by sortorder, salesrep_id,sales_group_id, parent_sales_group_id
771
772 tmp1 '||
773 ',jtf_rs_groups_tl grptl '||
774 ',jtf_rs_resource_extns_tl restl '||
775 ' WHERE tmp1.parent_sales_group_id = :l_sg_id_num '||
776 ' AND grptl.group_id = tmp1.sales_group_id '||
777 ' AND grptl.language = USERENV(''LANG'') '||
778 ' AND restl.language(+) = USERENV(''LANG'') '||
779 ' AND restl.resource_id(+) = tmp1.salesrep_id ';
780 ELSE
781
782 l_custom_sql:='SELECT '||
783 ' restl.resource_name VIEWBY '||
784 ',tmp1.salesrep_id||''.''||tmp1.sales_group_id VIEWBYID '||
785 ', SORTORDER '||
786 ',BIL_MEASURE28
787 , BIL_MEASURE2, BIL_MEASURE4, BIL_MEASURE5, BIL_MEASURE7,
788 BIL_MEASURE8, BIL_MEASURE10, BIL_MEASURE11,
789 NULL IL_URL1,
790 DECODE(tmp1.salesrep_id, NULL, NULL,'''||l_drill_str||''') BIL_URL2 FROM (
791 SELECT /*+ NO_MERGE */ salesrep_id, sales_group_id,
792 sortorder, parent_sales_group_id
793 ,SUM(frcst) BIL_MEASURE28 '||
794 ',SUM(priorFrcst) BIL_MEASURE2 '||
795 ',SUM(wtdPipeline) BIL_MEASURE4 '||
796 ',SUM(priorWtdPipeline) BIL_MEASURE5 '||
797 ',SUM(pipeline) BIL_MEASURE7 '||
798 ',SUM(priorPipeline) BIL_MEASURE8 '||
799 ',SUM(won) BIL_MEASURE10 '||
800 ',SUM(priorWon) BIL_MEASURE11 '||
801 ', NULL BIL_URL1 '||
802 ', NULL BIL_URL2 '||
803 ' FROM '||
804 '( '||
805 'SELECT /*+ LEADING(cal) */ '||
806 ' '||l_prodcat_flag||'
807 1 sortorder, '||
808 'sumry.salesrep_id, '||
809 'sumry.sales_group_id, '||
810 'sumry.parent_sales_group_id parent_sales_group_id, '||
811 l_sql_stmnt1_1||
812 ' FROM '||l_fii_struct||' cal, '||l_sumry||' sumry '||
813 l_denorm1||
814 l_where_clause1||
815 ' AND sumry.parent_sales_group_id = :l_sg_id_num '||
816 ' AND cal.xtd_flag = :l_yes '||
817 ' '||l_product_where_clause2||' '||
818 ' GROUP BY '||
819 ' sumry.salesrep_id,sumry.sales_group_id,'||
820 l_prodcat_flag||' sumry.parent_sales_group_id '||
821 ' UNION ALL '||
822 ' select '||l_prodcat_flag||' sortorder, salesrep_id,
823 sales_group_id, parent_sales_group_id,
824 sum(frcst) frcst, sum(priorFrcst) priorFrcst,
825 sum(wtdPipeline) wtdPipeline,
826 sum(priorWtdPipeline) priorWtdPipeline,
827 sum(pipeline) pipeline,
828 sum(priorPipeline) priorPipeline, sum(won) won,
829 sum(priorWon) priorWon from (
830 SELECT /*+ LEADING(cal) */ '||
831 ' '||l_prodcat_flag||'
832 1 sortorder, '||
833 'sumry.salesrep_id, '||
834 'sumry.sales_group_id, '||
835 'sumry.parent_sales_group_id parent_sales_group_id, '||
836 l_sql_stmnt2_1||
837 ' FROM '||l_fii_struct||' cal, '||l_sumry1||' sumry '||
838 l_where_clause2||
839 ' AND sumry.parent_sales_group_id = :l_sg_id_num '||
840 ' AND cal.xtd_flag = :l_yes '||
841 ' GROUP BY '||
842 ' sumry.salesrep_id,sumry.sales_group_id,'||
843 ' '||l_prodcat_flag||' sumry.parent_sales_group_id ';
844
845 l_custom_sql:= l_custom_sql||
846 ' UNION ALL '||
847 ' SELECT '||l_prodcat_flag||'
848 1 sortorder, '||
849 'sumry.salesrep_id, '||
850 'sumry.sales_group_id, '||
851 'sumry.parent_sales_group_id parent_sales_group_id, '||
852 l_sql_stmnt3_1||
853 ' FROM '||l_sumry2||' sumry '||
854 l_where_clause3||
855 ' AND sumry.parent_sales_group_id = :l_sg_id_num '||
856 ' GROUP BY '||
857 ' sumry.salesrep_id,sumry.sales_group_id,'||
858 ' '||l_prodcat_flag||' sumry.parent_sales_group_id';
859
860 l_custom_sql:= l_custom_sql||
861 ' ) sumry '||l_pipe_denorm||' where
862 1=1 '||l_pipe_product_where_clause||
863 ' GROUP BY sortorder,salesrep_id,sales_group_id,'||l_prodcat_flag||'parent_sales_group_id
864 ) group by sortorder, salesrep_id,sales_group_id, parent_sales_group_id
865 ) tmp1 '||
866 ',jtf_rs_resource_extns_tl restl '||
867 ' WHERE tmp1.parent_sales_group_id = :l_sg_id_num '||
868 ' AND restl.language = USERENV(''LANG'') '||
869 ' AND restl.resource_id = tmp1.salesrep_id '||
870 ' AND tmp1.salesrep_id = :l_resource_id';
871 END IF;
872
873
877 MODULE => g_pkg || l_proc,
874 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
875
876 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
878 MESSAGE => 'before exec imm');
879
880 END IF;
881
882
883 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
884 l_ind :=1;
885 l_len:= length(l_custom_sql);
886
887 WHILE l_ind <= l_len LOOP
888 l_str:= substr(l_custom_sql, l_ind, 4000);
889
890 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
891 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
892 MESSAGE => l_str);
893
894 l_ind := l_ind + 4000;
895
896 END LOOP;
897 END IF;
898
899
900 IF l_resource_id IS NULL THEN
901 IF l_prodcat_id = 'All' THEN
902 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
903 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
904 USING l_curr_page_time_id,l_curr_as_of_date, l_prev_page_time_id,l_prev_date,
905 l_bitand_id,l_bitand_id,l_period_type, l_fst_crdt_type,l_fst_crdt_type,
906 l_curr_as_of_date,l_prev_date, l_curr_page_time_id,l_prev_page_time_id, l_sg_id_num,l_yes,
907 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date,
908 l_sg_id_num,l_yes,
909 l_snapshot_date,l_period_type,
910 l_snapshot_date,
911 l_snapshot_date,l_period_type,
912 l_snapshot_date,
913 l_snapshot_date,
914 l_sg_id_num,
915 l_sg_id_num;
916 COMMIT;
917 ELSE
918 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
919 USING l_curr_page_time_id,l_curr_as_of_date, l_prev_page_time_id,l_prev_date,
920 l_bitand_id,l_bitand_id,l_period_type, l_fst_crdt_type,l_fst_crdt_type,
921 l_curr_as_of_date,l_prev_date, l_curr_page_time_id,l_prev_page_time_id, l_sg_id_num,l_yes,
922 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date,
923 l_sg_id_num,l_yes,
924 l_snapshot_date , l_period_type,
925 l_prev_snap_date,l_period_type,
926 l_snapshot_date,l_period_type,
927 l_prev_snap_date,l_period_type,
928 l_snapshot_date ,l_prev_snap_date,
929 l_sg_id_num,
930 l_sg_id_num;
931 COMMIT;
932 END IF;
933 ELSE
934
935 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
936 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
937 USING l_curr_page_time_id,l_curr_as_of_date,
938 l_prev_page_time_id,l_prev_date, l_bitand_id,l_bitand_id,l_period_type,
939 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,
940 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,
941 l_curr_as_of_date,l_prev_date, l_sg_id_num, l_yes,
942 l_snapshot_date,l_period_type,
943 l_snapshot_date,
944 l_snapshot_date,l_period_type,
945 l_snapshot_date,
946 l_snapshot_date,
947 l_sg_id_num, l_prodcat_id, l_sg_id_num;
948 ELSE
949 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
950 USING l_curr_page_time_id,l_curr_as_of_date,
951 l_prev_page_time_id,l_prev_date, l_bitand_id,l_bitand_id,l_period_type,
952 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,
953 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,
954 l_curr_as_of_date,l_prev_date, l_sg_id_num, l_yes,
955 l_snapshot_date , l_period_type,
956 l_prev_snap_date,l_period_type,
957 l_snapshot_date,l_period_type,
958 l_prev_snap_date,l_period_type,
959 l_snapshot_date ,l_prev_snap_date,
960 l_sg_id_num, l_prodcat_id, l_sg_id_num;
961 END IF;
962 END IF;
963 ELSE
964 IF l_prodcat_id = 'All' THEN
965
966 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
967 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
968 USING l_curr_page_time_id,l_curr_as_of_date,
969 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,
970 l_curr_as_of_date,l_prev_date, l_curr_page_time_id,l_prev_page_time_id, l_sg_id_num,l_yes,
971 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date,
972 l_sg_id_num,l_yes,
973 l_snapshot_date,l_period_type,
974 l_snapshot_date,
975 l_snapshot_date,l_period_type,
976 l_snapshot_date,
977 l_snapshot_date,
978 l_sg_id_num, l_sg_id_num,l_resource_id;
979 ELSE
980 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
981 USING l_curr_page_time_id,l_curr_as_of_date,
982 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,
983 l_curr_as_of_date,l_prev_date, l_curr_page_time_id,l_prev_page_time_id, l_sg_id_num,l_yes,
984 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date,
985 l_sg_id_num,l_yes,
986 l_snapshot_date , l_period_type,
987 l_prev_snap_date,l_period_type,
988 l_snapshot_date,l_period_type,
989 l_prev_snap_date,l_period_type,
990 l_snapshot_date ,l_prev_snap_date,
991 l_sg_id_num, l_sg_id_num,l_resource_id;
992 END IF;
993 ELSE
997 l_prev_page_time_id,l_prev_date, l_bitand_id,l_bitand_id,l_period_type,
994 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
995 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
996 USING l_curr_page_time_id,l_curr_as_of_date,
998 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,
999 l_sg_id_num,l_yes,l_prodcat_id, l_curr_as_of_date, l_prev_date,
1000 l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date, l_sg_id_num,l_yes,
1001 l_snapshot_date,l_period_type,
1002 l_snapshot_date,
1003 l_snapshot_date,l_period_type,
1004 l_snapshot_date,
1005 l_snapshot_date,
1006 l_sg_id_num,l_prodcat_id, l_sg_id_num,l_resource_id;
1007 ELSE
1008 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1009 USING l_curr_page_time_id,l_curr_as_of_date,
1010 l_prev_page_time_id,l_prev_date, l_bitand_id,l_bitand_id,l_period_type,
1011 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,
1012 l_sg_id_num,l_yes,l_prodcat_id, l_curr_as_of_date, l_prev_date,
1013 l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date, l_sg_id_num,l_yes,
1014 l_snapshot_date , l_period_type,
1015 l_prev_snap_date,l_period_type,
1016 l_snapshot_date,l_period_type,
1017 l_prev_snap_date,l_period_type,
1018 l_snapshot_date ,l_prev_snap_date,
1019 l_sg_id_num,l_prodcat_id, l_sg_id_num,l_resource_id;
1020 END IF;
1021 END IF;
1022 END IF;
1023
1024 x_custom_sql := 'SELECT * FROM ('||
1025 l_outer_select||' FROM BIL_BI_RPT_TMP1 '||
1026 ' ORDER BY SORTORDER, UPPER(VIEWBY)'||
1027 ') '||
1028 ' WHERE '||l_null_removal_clause;
1029 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
1030 IF l_parent_sales_group_id IS NULL THEN
1031 IF l_resource_id IS NULL THEN
1032 l_parent_sls_grp_where_clause := ' AND sumry.parent_sales_group_id IS NULL ';
1033 ELSE
1034 l_parent_sls_grp_where_clause := ' AND sumry.parent_sales_group_id = sumry.sales_group_id ';
1035 END IF;
1036 ELSE
1037 IF l_resource_id IS NULL THEN
1038 l_parent_sls_grp_where_clause :=
1039 ' AND sumry.parent_sales_group_id = :l_parent_sales_group_id ';
1040 ELSE
1041 l_parent_sls_grp_where_clause :=
1042 ' AND sumry.parent_sales_group_id = :l_sg_id_num ';
1043 END IF;
1044 END IF;
1045 l_cat_url := 'pFunctionName=BIL_BI_FSTOVER_R&pParamIds=Y&VIEW_BY='||l_viewby||
1046 '&VIEW_BY_NAME=VIEW_BY_ID';
1047 l_pc_select := ' SELECT
1048 decode(tmp1.viewbyid, -1,:l_unassigned_value,
1049 mtl.DESCRIPTION || '' ('' || mtl.CATEGORY_CONCAT_SEGS ||'')'') VIEWBY,
1050 sortorder,
1051 frcst ,
1052 priorFrcst,
1053 wtdPipeline,
1054 priorWtdPipeline ,
1055 pipeline,
1056 priorPipeline,
1057 won ,
1058 priorWon,
1059 VIEWBYID,
1060 salesrep_id,
1061 BIL_URL1,
1062 DECODE(tmp1.viewbyid,''-1'',NULL,'''||l_cat_url||''') BIL_URL2 '||
1063 ' FROM (
1064 SELECT
1065 1 SORTORDER,
1066 NULL frcst ,
1067 NULL priorFrcst,
1068 SUM(wtdPipeline) wtdPipeline,
1069 SUM(priorWtdPipeline) priorWtdPipeline,
1070 SUM(pipeline) pipeline,
1071 SUM(priorPipeline) priorPipeline,
1072 SUM(won) won ,
1073 SUM(priorWon) priorWon,
1074 pcd.parent_id VIEWBYID,
1075 salesrep_id,
1076 NULL BIL_URL1
1077 ';
1078
1079 l_unassigned_value := BIL_BI_UTIL_PKG.GET_UNASSIGNED_PC;
1080
1081 l_sumry := 'BIL_BI_FST_PG_MV';
1082 l_sumry1 := 'BIL_BI_OPTY_PG_MV';
1083 -- l_sumry2 := 'BIL_BI_PIPE_G_MV';
1084 l_sumry2 := l_open_mv_new;
1085 l_where_clause3 := l_where_clause3||' AND sumry.grp_total_flag = 0 ';
1086 l_denorm := ' ,ENI_ITEM_PROD_CAT_LOOKUP_V pcd ';
1087 IF l_prodcat_id = 'All' THEN
1088 l_product_where_clause1 := ' pcd.top_node_flag = :l_yes '||
1089 ' AND pcd.parent_id = sumry.product_category_id '||
1090 ' AND pcd.child_id = sumry.product_category_id '||
1091 ' AND sumry.product_category_id = pcd.id ';
1092 ELSE
1093 l_product_where_clause1 := ' sumry.product_category_id = pcd.child_id AND '||
1094 ' pcd.parent_id=:l_prodcat_id AND pcd.child_id = pcd.id AND '||
1095 ' NOT((assign_to_cat = 0 AND pcd.child_id = pcd.parent_id)) ' ;
1096
1097 END IF;
1098 IF l_prodcat_id = 'All' THEN
1099 l_custom_sql := ' SELECT VIEWBY '||
1100 ', VIEWBYID '||
1101 ', SORTORDER '||
1102 ',SUM(frcst) BIL_MEASURE28 '||
1103 ',SUM(priorFrcst) BIL_MEASURE2 '||
1104 ',SUM(wtdPipeline) BIL_MEASURE4 '||
1105 ',SUM(priorWtdPipeline) BIL_MEASURE5 '||
1106 ',SUM(pipeline) BIL_MEASURE7 '||
1107 ',SUM(priorPipeline) BIL_MEASURE8 '||
1108 ',SUM(won) BIL_MEASURE10 '||
1109 ',SUM(priorWon) BIL_MEASURE11 '||
1110 ','''||l_drill_str||''' BIL_URL1 '||
1114 ' SELECT /*+ LEADING(cal) */ '||
1111 ',BIL_URL2 '||
1112 ' FROM '||
1113 '( '||
1115 ' pcd.value VIEWBY'||
1116 ', 1 sortorder, '||
1117 l_sql_stmnt4||
1118 ',pcd.id VIEWBYID'||
1119 ',sumry.salesrep_id salesrep_id '||
1120 ',NULL BIL_URL1'||
1121 ',DECODE(pcd.id,''-1'',NULL,'''||l_cat_url||''') BIL_URL2 '||
1122 ' FROM '||l_fii_struct||' cal,'||
1123 l_sumry||' sumry '||
1124 l_denorm||' '||
1125 ' '||l_where_clause1||' AND '||l_product_where_clause1||
1126 ' AND sumry.sales_group_id = :l_sg_id_num '||
1127 l_parent_sls_grp_where_clause||
1128 ' AND cal.xtd_flag = :l_yes ';
1129
1130 IF l_resource_id IS NULL THEN
1131 l_custom_sql :=l_custom_sql ||
1132 ' AND sumry.salesrep_id IS NULL ';
1133 ELSE
1134 l_custom_sql :=l_custom_sql ||
1135 ' AND sumry.salesrep_id = :l_resource_id ';
1136 END IF;
1137 l_custom_sql := l_custom_sql ||
1138 ' UNION ALL '||
1139 l_pc_select ||
1140 ' FROM ('||
1141 ' SELECT /*+ LEADING(cal) */'||
1142 ' NULL VIEWBY'||
1143 ', 1 sortorder, '||l_sql_stmnt2||
1144 ',NULL VIEWBYID'||
1145 ',sumry.salesrep_id salesrep_id '||
1146 ',NULL BIL_URL1'||
1147 ',sumry.product_category_id product_category_id'||
1148 ' FROM '||l_fii_struct||' cal,'||
1149 l_sumry1||' sumry '||
1150 l_where_clause2||' '||
1151 ' AND sumry.sales_group_id = :l_sg_id_num '||
1152 l_parent_sls_grp_where_clause||
1153 ' AND cal.xtd_flag = :l_yes ';
1154 IF l_resource_id IS NULL THEN
1155 l_custom_sql :=l_custom_sql ||
1156 ' AND sumry.salesrep_id IS NULL ';
1157 ELSE
1158 l_custom_sql :=l_custom_sql ||
1159 ' AND sumry.salesrep_id = :l_resource_id ';
1160 END IF;
1161
1162 l_custom_sql := l_custom_sql||
1163 ' UNION ALL '||
1164 ' SELECT NULL VIEWBY'||
1165 ', 1 sortorder, '||l_sql_stmnt3||
1166 ',NULL VIEWBYID'||
1167 ',sumry.salesrep_id salesrep_id '||
1168 ',NULL BIL_URL1'||
1169 ',sumry.product_category_id product_category_id'||
1170 ' FROM '||l_sumry2||' sumry '||
1171 ' '||l_where_clause3||' '||
1172 ' AND sumry.sales_group_id = :l_sg_id_num '||
1173 l_parent_sls_grp_where_clause;
1174 IF l_resource_id IS NULL THEN
1175 l_custom_sql :=l_custom_sql ||
1176 ' AND sumry.salesrep_id IS NULL ';
1177 ELSE
1178 l_custom_sql :=l_custom_sql ||
1179 ' AND sumry.salesrep_id = :l_resource_id ';
1180 END IF;
1181
1182 /* l_pipe_product_where_clause := ' AND sumry.product_category_id =pcd.child_id
1183 AND pcd.object_type = ''CATEGORY_SET''
1184 AND pcd.object_id = d.category_set_id
1185 AND d.functional_area_id = 11
1186 AND pcd.dbi_flag = ''Y''
1187 AND pcd.top_node_flag = :l_yes ';
1188 l_pipe_denorm := ',eni_denorm_hierarchies pcd, mtl_default_category_sets d ';
1189 */
1190
1191 l_custom_sql := l_custom_sql||
1192 ' ) sumry '||l_pipe_denorm||
1193 ' WHERE 1=1 '||l_pipe_product_where_clause||
1194 ' GROUP BY pcd.parent_id, salesrep_id)tmp1 , mtl_categories_v mtl '||
1195 ' WHERE mtl.category_id (+) = tmp1.viewbyid)' ||
1196 ' GROUP BY VIEWBY, VIEWBYID, SORTORDER,BIL_URL2 ';
1197
1198
1199 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1200
1201 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1202 MODULE => g_pkg || l_proc || 'Prod cat Viewby ',
1203 MESSAGE => 'l_custom_sql length '||LENGTH(l_custom_sql));
1204
1205 END IF;
1206
1207
1208
1209 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1210 l_ind :=1;
1211 l_len:= length(l_custom_sql);
1212
1213 WHILE l_ind <= l_len LOOP
1214 l_str:= substr(l_custom_sql, l_ind, 4000);
1215
1216 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1217 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
1218 MESSAGE => l_str);
1219
1220 l_ind := l_ind + 4000;
1221
1222 END LOOP;
1223 END IF;
1224
1225
1226
1227 IF l_parent_sales_group_id IS NULL THEN
1228 IF l_resource_id IS NULL THEN
1229 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
1230 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1231 USING l_curr_page_time_id,l_curr_as_of_date,
1232 l_prev_page_time_id,l_prev_date, l_bitand_id,l_bitand_id,l_period_type,
1233 l_fst_crdt_type,l_fst_crdt_type, l_curr_as_of_date,l_prev_date,
1234 l_curr_page_time_id,l_prev_page_time_id, l_yes,l_sg_id_num, l_yes, l_unassigned_value,
1235 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1236 l_curr_as_of_date,l_prev_date, l_sg_id_num, l_yes,
1237 l_snapshot_date,l_period_type,
1238 l_snapshot_date,
1239 l_snapshot_date,l_period_type,
1243 COMMIT;
1240 l_snapshot_date,
1241 l_snapshot_date,
1242 l_sg_id_num, l_yes; --pc where clause
1244 ELSE
1245 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1246 USING l_curr_page_time_id,l_curr_as_of_date,
1247 l_prev_page_time_id,l_prev_date, l_bitand_id,l_bitand_id,l_period_type,
1248 l_fst_crdt_type,l_fst_crdt_type, l_curr_as_of_date,l_prev_date,
1249 l_curr_page_time_id,l_prev_page_time_id, l_yes,l_sg_id_num, l_yes, l_unassigned_value,
1250 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1251 l_curr_as_of_date,l_prev_date, l_sg_id_num, l_yes,
1252 l_snapshot_date , l_period_type,
1253 l_prev_snap_date,l_period_type,
1254 l_snapshot_date,l_period_type,
1255 l_prev_snap_date,l_period_type,
1256 l_snapshot_date ,l_prev_snap_date,
1257 l_sg_id_num, l_yes; --pc where clause
1258 COMMIT;
1259 END IF;
1260 ELSE
1261 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
1262 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1263 USING l_curr_page_time_id,l_curr_as_of_date,
1264 l_prev_page_time_id,l_prev_date, l_bitand_id,l_bitand_id,l_period_type,
1265 l_fst_crdt_type,l_fst_crdt_type,
1266 l_curr_as_of_date,l_prev_date,l_curr_page_time_id,l_prev_page_time_id,
1267 l_yes,l_sg_id_num,l_yes,l_resource_id, l_unassigned_value,
1268 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1269 l_curr_as_of_date,l_prev_date, l_sg_id_num,l_yes,l_resource_id,
1270 l_snapshot_date,l_period_type,
1271 l_snapshot_date,
1272 l_snapshot_date,l_period_type,
1273 l_snapshot_date,
1274 l_snapshot_date,
1275 l_sg_id_num,l_resource_id, l_yes; --pc where clause;
1276 ELSE
1277 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1278 USING l_curr_page_time_id,l_curr_as_of_date,
1279 l_prev_page_time_id,l_prev_date, l_bitand_id,l_bitand_id,l_period_type,
1280 l_fst_crdt_type,l_fst_crdt_type,
1281 l_curr_as_of_date,l_prev_date,l_curr_page_time_id,l_prev_page_time_id,
1282 l_yes,l_sg_id_num,l_yes,l_resource_id, l_unassigned_value,
1283 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1284 l_curr_as_of_date,l_prev_date, l_sg_id_num,l_yes,l_resource_id,
1285 l_snapshot_date , l_period_type,
1286 l_prev_snap_date,l_period_type,
1287 l_snapshot_date,l_period_type,
1288 l_prev_snap_date,l_period_type,
1289 l_snapshot_date ,l_prev_snap_date,
1290 l_sg_id_num,l_resource_id, l_yes; --pc where clause;
1291 END IF;
1292 END IF;
1293 ELSE -- parent sales group id is not null
1294 IF l_resource_id IS NULL THEN
1295 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
1296 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1297 USING l_curr_page_time_id,l_curr_as_of_date, l_prev_page_time_id,l_prev_date,
1298 l_bitand_id,l_bitand_id,l_period_type, l_fst_crdt_type,l_fst_crdt_type,
1299 l_curr_as_of_date,l_prev_date, l_curr_page_time_id,l_prev_page_time_id,
1300 l_yes,l_sg_id_num, l_parent_sales_group_id, l_yes, l_unassigned_value,
1301 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1302 l_curr_as_of_date,l_prev_date, l_sg_id_num, l_parent_sales_group_id, l_yes,
1303 l_snapshot_date,l_period_type,
1304 l_snapshot_date,
1305 l_snapshot_date,l_period_type,
1306 l_snapshot_date,
1307 l_snapshot_date,
1308 l_sg_id_num, l_parent_sales_group_id, l_yes; --pc where clause
1309 ELSE
1310 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1311 USING l_curr_page_time_id,l_curr_as_of_date, l_prev_page_time_id,l_prev_date,
1312 l_bitand_id,l_bitand_id,l_period_type, l_fst_crdt_type,l_fst_crdt_type,
1313 l_curr_as_of_date,l_prev_date, l_curr_page_time_id,l_prev_page_time_id,
1314 l_yes,l_sg_id_num, l_parent_sales_group_id, l_yes, l_unassigned_value,
1315 l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1316 l_curr_as_of_date,l_prev_date, l_sg_id_num, l_parent_sales_group_id, l_yes,
1317 l_snapshot_date , l_period_type,
1318 l_prev_snap_date,l_period_type,
1319 l_snapshot_date,l_period_type,
1320 l_prev_snap_date,l_period_type,
1321 l_snapshot_date ,l_prev_snap_date,
1322 l_sg_id_num, l_parent_sales_group_id, l_yes; --pc where clause
1323 END IF;
1324 ELSE
1325 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
1326 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1327 USING l_curr_page_time_id,l_curr_as_of_date, l_prev_page_time_id,l_prev_date,
1328 l_bitand_id,l_bitand_id,l_period_type, l_fst_crdt_type,l_fst_crdt_type,
1329 l_curr_as_of_date,l_prev_date,l_curr_page_time_id,l_prev_page_time_id, l_yes,l_sg_id_num,
1330 l_sg_id_num, l_yes,l_resource_id, l_unassigned_value, l_curr_as_of_date, l_prev_date,
1331 l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date, l_sg_id_num, l_sg_id_num,
1332 l_yes,l_resource_id,
1333 l_snapshot_date,l_period_type,
1334 l_snapshot_date,
1335 l_snapshot_date,l_period_type,
1336 l_snapshot_date,
1337 l_snapshot_date,
1338 l_sg_id_num, l_sg_id_num, l_resource_id, l_yes; --pc where clause;
1339 ELSE
1340 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1341 USING l_curr_page_time_id,l_curr_as_of_date, l_prev_page_time_id,l_prev_date,
1342 l_bitand_id,l_bitand_id,l_period_type, l_fst_crdt_type,l_fst_crdt_type,
1343 l_curr_as_of_date,l_prev_date,l_curr_page_time_id,l_prev_page_time_id, l_yes,l_sg_id_num,
1347 l_snapshot_date , l_period_type,
1344 l_sg_id_num, l_yes,l_resource_id, l_unassigned_value, l_curr_as_of_date, l_prev_date,
1345 l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date, l_sg_id_num, l_sg_id_num,
1346 l_yes,l_resource_id,
1348 l_prev_snap_date,l_period_type,
1349 l_snapshot_date,l_period_type,
1350 l_prev_snap_date,l_period_type,
1351 l_snapshot_date ,l_prev_snap_date,
1352 l_sg_id_num, l_sg_id_num, l_resource_id, l_yes; --pc where clause;
1353 END IF;
1354 END IF;
1355 END IF;
1356
1357 x_custom_sql := 'SELECT * FROM ('||
1358 l_outer_select||' FROM BIL_BI_RPT_TMP1 '||
1359 ' ORDER BY SORTORDER, UPPER(VIEWBY) '||
1360 ') '||
1361 ' WHERE '||l_null_removal_clause;
1362 ELSE -- product cat not all
1363 l_custom_sql := ' SELECT VIEWBY '||
1364 ', VIEWBYID '||
1365 ', SORTORDER '||
1366 ',SUM(frcst) BIL_MEASURE28 '||
1367 ',SUM(priorFrcst) BIL_MEASURE2 '||
1368 ',SUM(wtdPipeline) BIL_MEASURE4 '||
1369 ',SUM(priorWtdPipeline) BIL_MEASURE5 '||
1370 ',SUM(pipeline) BIL_MEASURE7 '||
1371 ',SUM(priorPipeline) BIL_MEASURE8 '||
1372 ',SUM(won) BIL_MEASURE10 '||
1373 ',SUM(priorWon) BIL_MEASURE11 '||
1374 ',DECODE(VIEWBY,'||':l_cat_assign'||',NULL,'''||l_drill_str||''') BIL_URL1 '||
1375 ',BIL_URL2 '||
1376 ' FROM '||
1377 '( '||
1378 ' SELECT /*+ LEADING(cal) */ '||
1379 ' decode(pcd.parent_id,pcd.child_id,'||
1380 ' decode(sumry.assign_to_cat,0,pcd.value,:l_cat_assign), '||
1381 ' pcd.value) VIEWBY '||
1382 ', decode(pcd.parent_id,pcd.id, 1, 2) sortorder, '||
1383 l_sql_stmnt4||
1384 ',pcd.id VIEWBYID'||
1385 ',SUMRY.salesrep_id salesrep_id '||
1386 ',NULL BIL_URL1'||
1387 ', decode(pcd.parent_id, pcd.child_id, null, '||
1388 ' '''||l_cat_url||''') BIL_URL2 '||
1389 ' FROM '||l_fii_struct||' cal, '||
1390 l_sumry||' sumry '||
1391 l_denorm||' '||
1392 ' '||l_where_clause1||' AND '||l_product_where_clause1||
1393 ' AND sumry.sales_group_id = :l_sg_id_num '||
1394 l_parent_sls_grp_where_clause||
1395 ' AND cal.xtd_flag = :l_yes ';
1396 IF l_resource_id IS NULL THEN
1397 l_custom_sql :=l_custom_sql ||
1398 ' AND sumry.salesrep_id IS NULL ';
1399 ELSE
1400 l_custom_sql :=l_custom_sql ||
1401 ' AND sumry.salesrep_id = :l_resource_id ';
1402 END IF;
1403 l_custom_sql := l_custom_sql ||
1404 ' )tmp1'||
1405 ' GROUP BY VIEWBY, VIEWBYID, SORTORDER,BIL_URL2 ';
1406
1407
1408 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1409
1410 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1411 MODULE => g_pkg || 'Prod cat Viewby ',
1412 MESSAGE => ' Forecast Query Product Cat not All ');
1413
1414 END IF;
1415
1416
1417 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1418 l_ind :=1;
1419 l_len:= length(l_custom_sql);
1420
1421 WHILE l_ind <= l_len LOOP
1422 l_str:= substr(l_custom_sql, l_ind, 4000);
1423
1424 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1425 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
1426 MESSAGE => l_str);
1427
1428 l_ind := l_ind + 4000;
1429
1430 END LOOP;
1431 END IF;
1432
1433
1434 IF l_parent_sales_group_id IS NULL THEN
1435 IF l_resource_id IS NULL THEN
1436 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1437 USING l_cat_assign, l_cat_assign,
1438 l_curr_page_time_id,l_curr_as_of_date,
1439 l_prev_page_time_id,l_prev_date,
1440
1441 l_bitand_id,l_bitand_id,l_period_type,
1442 l_fst_crdt_type,l_fst_crdt_type,
1443 l_curr_as_of_date,l_prev_date,l_curr_page_time_id,l_prev_page_time_id,
1444 l_prodcat_id,
1445 l_sg_id_num, l_yes;
1446 ELSE
1447 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1448 USING l_cat_assign, l_cat_assign,
1449 l_curr_page_time_id,l_curr_as_of_date,
1450 l_prev_page_time_id,l_prev_date,
1451
1452 l_bitand_id,l_bitand_id,l_period_type,
1453 l_fst_crdt_type,l_fst_crdt_type,
1454 l_curr_as_of_date,l_prev_date,l_curr_page_time_id,l_prev_page_time_id,
1455 l_prodcat_id,
1456 l_sg_id_num,l_yes,l_resource_id;
1457
1458 END IF;
1459 ELSE-- parent sales group is not null
1460 IF l_resource_id IS NULL THEN
1461 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1462 USING l_cat_assign, l_cat_assign,
1463 l_curr_page_time_id,l_curr_as_of_date,
1464 l_prev_page_time_id,l_prev_date,
1465 l_bitand_id,l_bitand_id,l_period_type,
1466 l_fst_crdt_type,l_fst_crdt_type,
1467 l_curr_as_of_date,l_prev_date,l_curr_page_time_id,l_prev_page_time_id,
1468 l_prodcat_id,
1469 l_sg_id_num,
1470 l_parent_sales_group_id,
1471 l_yes;
1472 ELSE
1473 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1474 USING l_cat_assign, l_cat_assign,
1478 l_fst_crdt_type,l_fst_crdt_type,
1475 l_curr_page_time_id,l_curr_as_of_date,
1476 l_prev_page_time_id,l_prev_date,
1477 l_bitand_id,l_bitand_id,l_period_type,
1479 l_curr_as_of_date,l_prev_date,l_curr_page_time_id,l_prev_page_time_id,
1480 l_prodcat_id,
1481 l_sg_id_num,
1482 l_sg_id_num,
1483 l_yes,l_resource_id;
1484 END IF;
1485 END IF;
1486 -- for won, pipeline measures
1487 l_custom_sql :=
1488 ' SELECT VIEWBY '||
1489 ', VIEWBYID '||
1490 ', SORTORDER '||
1491 ',NULL BIL_MEASURE28 '||
1492 ',NULL BIL_MEASURE2 '||
1493 ',SUM(wtdPipeline) BIL_MEASURE4 '||
1494 ',SUM(priorWtdPipeline) BIL_MEASURE5 '||
1495 ',SUM(pipeline) BIL_MEASURE7 '||
1496 ',SUM(priorPipeline) BIL_MEASURE8 '||
1497 ',SUM(won) BIL_MEASURE10 '||
1498 ',SUM(priorWon) BIL_MEASURE11 '||
1499 ',DECODE(VIEWBY,'||':l_cat_assign'||',NULL,'''||l_drill_str||''') BIL_URL1 '||
1500 ',BIL_URL2 '||
1501 ' FROM '||
1502 '( '||
1503 ' SELECT /*+ LEADING(cal) */ '||
1504 ' decode(pcd.parent_id,pcd.id, '||
1505 ' decode(sumry.item_id,''-1'',:l_cat_assign,pcd.value),pcd.value) '||
1506 ' VIEWBY'||
1507 ', decode(pcd.parent_id,pcd.id,'||
1508 'decode(sumry.item_id,''-1'', 1, 2),2) sortorder, '||
1509 l_sql_stmnt2||
1510 ',pcd.id VIEWBYID'||
1511 ',NULL BIL_URL1'||
1512 ',decode(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL2'||
1513 ' FROM '||l_fii_struct||' cal,'||
1514 l_sumry1||' sumry'||
1515 l_pipe_denorm||' '||
1516 ' '||l_where_clause2||' '||
1517 l_pipe_product_where_clause||
1518 ' AND sumry.sales_group_id = :l_sg_id_num '||
1519 l_parent_sls_grp_where_clause||
1520 ' AND cal.xtd_flag = :l_yes ';
1521
1522 IF l_resource_id IS NULL THEN
1523 l_custom_sql :=l_custom_sql ||
1524 ' AND sumry.salesrep_id IS NULL ';
1525 ELSE
1526 l_custom_sql :=l_custom_sql ||
1527 ' AND sumry.salesrep_id = :l_resource_id ';
1528 END IF;
1529 l_custom_sql := l_custom_sql||
1530 ' UNION ALL '||
1531 ' SELECT DECODE(pcd.parent_id, pcd.id,
1532 decode(sumry.item_id, ''-1'', :l_cat_assign, pcd.value), pcd.value) VIEWBY
1533 ,DECODE(pcd.parent_id, pcd.id,
1534 decode(sumry.item_id, ''-1'', 1, 2), 2) SORTORDER, '||
1535 l_sql_stmnt3||
1536 ',pcd.id VIEWBYID'||
1537 ',NULL BIL_URL1'||
1538 ',decode(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL2'||
1539 ' FROM '||l_sumry2||' sumry'||
1540 l_pipe_denorm||' '||
1541 ' '||l_where_clause3||' '||
1542 l_pipe_product_where_clause||
1543 ' AND sumry.sales_group_id = :l_sg_id_num '||
1544 l_parent_sls_grp_where_clause;
1545 IF l_resource_id IS NULL THEN
1546 l_custom_sql :=l_custom_sql ||
1547 ' AND sumry.salesrep_id IS NULL ';
1548 ELSE
1549 l_custom_sql :=l_custom_sql ||
1550 ' AND sumry.salesrep_id = :l_resource_id ';
1551 END IF;
1552
1553 l_custom_sql := l_custom_sql||
1554 ' )tmp1'||
1555 ' GROUP BY VIEWBY, VIEWBYID, SORTORDER,BIL_URL2 ';
1556
1557
1558 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1559
1560 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1561 MODULE => g_pkg || l_proc || 'Prod cat Viewby ',
1562 MESSAGE => ' x_custom_sql length '||LENGTH(l_custom_sql));
1563
1564 END IF;
1565
1566
1567 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1568 l_ind :=1;
1569 l_len:= length(l_custom_sql);
1570
1571 WHILE l_ind <= l_len LOOP
1572 l_str:= substr(l_custom_sql, l_ind, 4000);
1573
1574 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1575 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
1576 MESSAGE => l_str);
1577
1578 l_ind := l_ind + 4000;
1579
1580 END LOOP;
1581 END IF;
1582
1583
1584 IF l_parent_sales_group_id IS NULL THEN
1585 IF l_resource_id IS NULL THEN
1586 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
1587 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1588 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date,
1589 l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num,
1590 l_yes, l_cat_assign,
1591 l_snapshot_date,l_period_type,
1592 l_snapshot_date,
1593 l_snapshot_date,l_period_type,
1594 l_snapshot_date,
1595 l_snapshot_date,
1596 l_prodcat_id, l_sg_id_num;
1597 ELSE
1598 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1599 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date,
1600 l_record_type_id,l_record_type_id, l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num,
1601 l_yes, l_cat_assign,
1602 l_snapshot_date , l_period_type,
1603 l_prev_snap_date,l_period_type,
1604 l_snapshot_date,l_period_type,
1605 l_prev_snap_date,l_period_type,
1606 l_snapshot_date ,l_prev_snap_date,
1607 l_prodcat_id, l_sg_id_num;
1608 END IF;
1609 ELSE
1610 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
1611 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1612 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1613 l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num, l_yes, l_resource_id, l_cat_assign ,
1614 l_snapshot_date,l_period_type,
1615 l_snapshot_date,
1616 l_snapshot_date,l_period_type,
1617 l_snapshot_date,
1618 l_snapshot_date,
1619 l_prodcat_id, l_sg_id_num, l_resource_id;
1620 ELSE
1621 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1622 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1623 l_curr_as_of_date,l_prev_date, l_prodcat_id, l_sg_id_num, l_yes, l_resource_id, l_cat_assign ,
1624 l_snapshot_date , l_period_type,
1625 l_prev_snap_date,l_period_type,
1626 l_snapshot_date,l_period_type,
1627 l_prev_snap_date,l_period_type,
1628 l_snapshot_date ,l_prev_snap_date,
1629 l_prodcat_id, l_sg_id_num, l_resource_id;
1630 END IF;
1631 END IF;
1632 ELSE -- parent sales group id not null
1633 IF l_resource_id IS NULL THEN
1634 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
1635 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1636 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1637 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,
1638 l_snapshot_date,l_period_type,
1639 l_snapshot_date,
1640 l_snapshot_date,l_period_type,
1641 l_snapshot_date,
1642 l_snapshot_date,
1643 l_prodcat_id, l_sg_id_num, l_parent_sales_group_id;
1644 ELSE
1645 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1646 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1647 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,
1648 l_snapshot_date , l_period_type,
1649 l_prev_snap_date,l_period_type,
1650 l_snapshot_date,l_period_type,
1651 l_prev_snap_date,l_period_type,
1652 l_snapshot_date ,l_prev_snap_date,
1653 l_prodcat_id, l_sg_id_num, l_parent_sales_group_id;
1654 END IF;
1655 ELSE
1656 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
1657 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1658 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1659 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 ,
1660 l_snapshot_date,l_period_type,
1661 l_snapshot_date,
1662 l_snapshot_date,l_period_type,
1663 l_snapshot_date,
1664 l_snapshot_date,
1665 l_prodcat_id, l_sg_id_num, l_sg_id_num, l_resource_id;
1666 ELSE
1667 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
1668 USING l_cat_assign, l_cat_assign, l_curr_as_of_date, l_prev_date, l_record_type_id,l_record_type_id,
1669 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 ,
1670 l_snapshot_date , l_period_type,
1674 l_snapshot_date ,l_prev_snap_date,
1671 l_prev_snap_date,l_period_type,
1672 l_snapshot_date,l_period_type,
1673 l_prev_snap_date,l_period_type,
1675 l_prodcat_id, l_sg_id_num, l_sg_id_num, l_resource_id;
1676 END IF;
1677 END IF;
1678 END IF;
1679 x_custom_sql := 'SELECT * FROM ('||
1680 l_outer_select||
1681 ' FROM ( '||
1682 'SELECT VIEWBY, VIEWBYID, SORTORDER,SUM(BIL_MEASURE28) BIL_MEASURE28,'||
1683 ' SUM(BIL_MEASURE2) BIL_MEASURE2,SUM(BIL_MEASURE4) BIL_MEASURE4, '||
1684 ' SUM(BIL_MEASURE5) BIL_MEASURE5,SUM(BIL_MEASURE7) BIL_MEASURE7, '||
1685 ' SUM(BIL_MEASURE8) BIL_MEASURE8,SUM(BIL_MEASURE10) BIL_MEASURE10,'||
1686 ' SUM(BIL_MEASURE11) BIL_MEASURE11, BIL_URL1, BIL_URL2 '||
1687 ' FROM BIL_BI_RPT_TMP1 '||
1688 ' GROUP BY VIEWBY, VIEWBYID, SORTORDER, '||
1689 ' BIL_URL1, BIL_URL2 '||
1690 ') '||
1691 ' ORDER BY SORTORDER, UPPER(VIEWBY) '||
1692 ') WHERE '||l_null_removal_clause;
1693 END IF;
1694 END CASE;
1695
1696
1697 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1698 l_ind :=1;
1699 l_len:= length(l_custom_sql);
1700
1701 WHILE l_ind <= l_len LOOP
1702 l_str:= substr(l_custom_sql, l_ind, 4000);
1703
1704 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1705 MODULE => g_pkg || l_proc ||'.'|| 'Final Query =>',
1706 MESSAGE => l_str);
1707
1708 l_ind := l_ind + 4000;
1709
1710 END LOOP;
1711 END IF;
1712
1713
1714
1715 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1716
1717 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1718 MODULE => g_pkg || l_proc || 'Query Length=>',
1719 MESSAGE => length(x_custom_sql));
1720
1721 END IF;
1722
1723
1724 ELSE --no valid parameters
1725
1726 BIL_BI_UTIL_PKG.get_default_query(p_regionname => l_region_id
1727 ,x_sqlstr => x_custom_sql);
1728 END IF;
1729
1730 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1731
1732 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
1733 MODULE => g_pkg || l_proc || 'End',
1734 MESSAGE => 'End of Procedure '||l_proc);
1735 END IF;
1736
1737
1738 EXCEPTION
1739 WHEN OTHERS THEN
1740
1741 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1742
1743 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1744 fnd_message.set_token('Error is : ' ,SQLCODE);
1745 fnd_message.set_token('Reason is : ', SQLERRM);
1746
1747 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1748 MODULE => g_pkg || l_proc || 'proc_error',
1749 MESSAGE => fnd_message.get );
1750
1751 END IF;
1752 RAISE;
1753 END BIL_BI_FRCST_OVERVIEW;
1754
1755
1756 /*******************************************************************************
1757 * Name : Procedure BIL_BI_FRCST_PRODCAT
1758 * Author : Vikas Chahal
1759 * Date : Nov 22, 2004
1760 * Purpose : Forecast Overview By Product Category.
1761 *
1762 * Copyright (c) 2004 Oracle Corporation
1763 *
1764 * Parameters
1765 * p_page_parameter_tbl PL/SQL table containing dimension parameters
1766 * x_custom_sql string containing sql query
1767 * x_custom_attr PL/SQL table containing our bind vars
1768 *
1769 ******************************************************************************/
1770
1771 PROCEDURE BIL_BI_FRCST_PRODCAT ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1772 ,x_custom_sql OUT NOCOPY VARCHAR2
1773 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL )
1774
1775 IS
1776
1777 l_period_type VARCHAR2(50);
1778 l_sg_id VARCHAR2(50);
1779 l_conv_rate_selected VARCHAR2(50);
1780 l_curr_page_time_id NUMBER;
1781 l_prev_page_time_id NUMBER;
1782 l_prior_prior_time_id NUMBER;
1783 l_region_id VARCHAR2(50);
1784 l_comp_type VARCHAR2(50);
1785 l_parameter_valid BOOLEAN;
1786 l_bitand_id VARCHAR2(50);
1787 l_calendar_id VARCHAR2(50);
1788 l_curr_as_of_date DATE;
1789 l_prev_date DATE;
1790 l_prior_prior_date DATE;
1791 l_page_period_type VARCHAR2(50);
1792 l_bis_sysdate DATE;
1793 l_fii_struct VARCHAR2(50);
1794 l_record_type_id NUMBER;
1795 l_sql_error_msg VARCHAR2(1000);
1796 l_sql_error_desc VARCHAR2(1000);
1797 l_sg_id_num NUMBER;
1798 l_fst_crdt_type VARCHAR2(50);
1799 l_sql_stmnt1 VARCHAR2(32000);
1800 l_sql_stmnt3 VARCHAR2(32000);
1801
1802 l_custom_sql VARCHAR2(32000);
1803 l_insert_stmnt VARCHAR2(5000);
1804 l_outer_select VARCHAR2(5000);
1805 l_where_clause1 VARCHAR2(5000);
1806 l_where_clause3 VARCHAR2(5000);
1807 l_prodcat_id VARCHAR2(20);
1808 l_viewby VARCHAR2(200);
1809 l_resource_id VARCHAR2(20);
1810 l_url_str VARCHAR2(1000);
1811 l_cat_assign VARCHAR2(1000);
1815 l_sumry2 VARCHAR2(50);
1812 l_denorm VARCHAR2(50);
1813 l_cat_url VARCHAR2(1000);
1814 l_sumry VARCHAR2(50);
1816 l_product_where_clause1 VARCHAR2(2000);
1817 l_yes VARCHAR2(1);
1818 l_assign_cat BOOLEAN;
1819 l_snap_date DATE;
1820 l_proc VARCHAR2(100);
1821 l_null_removal_clause VARCHAR2(1000);
1822 l_parent_sales_group_id NUMBER;
1823 l_parent_sls_grp_where_clause VARCHAR2(1000);
1824 l_pipe_product_where_clause VARCHAR2(1000);
1825 l_pipe_denorm VARCHAR2(100);
1826
1827 l_pc_select VARCHAR2(32000);
1828 l_unassigned_value VARCHAR2(100);
1829 l_prodcat_flag VARCHAR2(200);
1830
1831 l_currency_suffix VARCHAR2(5);
1832 l_pipe_url VARCHAR2(1000);
1833
1834 l_lookup_type VARCHAR2(30);
1835 l_lookup_code VARCHAR2(30);
1836 l_lang VARCHAR2(30);
1837
1838 l_ind NUMBER;
1839 l_str VARCHAR2(4000);
1840 l_len NUMBER;
1841
1842 l_prev_pipe_amt VARCHAR2(300);
1843 l_prv_wt_pipe_amt VARCHAR2(300);
1844 l_column_type VARCHAR2(1000);
1845 l_snapshot_date DATE;
1846 l_open_mv_new VARCHAR2(1000);
1847 l_open_mv_new1 VARCHAR2(1000);
1848 l_prev_snap_date DATE;
1849 l_pipe_select1 varchar2(4000);
1850 l_pipe_select2 varchar2(4000);
1851 l_pipe_select3 varchar2(4000);
1852 l_pipe_select4 varchar2(4000);
1853 l_inner_where_pipe varchar2(4000);
1854
1855 BEGIN
1856
1857 g_pkg := 'bil.patch.115.sql.BIL_BI_FCST_MGMT_RPTS_PKG.';
1858 l_region_id := 'BIL_BI_FRCST_PRODCAT';
1859 l_parameter_valid := FALSE;
1860 l_yes := 'Y';
1861 l_proc := 'BIL_BI_FRCST_PRODCAT';
1862 g_sch_name := 'BIL';
1863
1864
1865 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1866
1867 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
1868 MODULE => g_pkg || l_proc || 'begin',
1869 MESSAGE => 'Start of Procedure '||l_proc);
1870
1871 END IF;
1872
1873 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
1874
1875 l_lookup_type := 'BIL_BI_LOOKUPS';
1876 l_lookup_code := 'ASSIGN_CATEG';
1877 l_lang := USERENV('LANG');
1878
1879 SELECT Meaning INTO l_cat_assign
1880 FROM FND_LOOKUP_VALUES
1881 WHERE LOOKUP_TYPE = l_lookup_type
1882 AND LOOKUP_CODE = l_lookup_code
1883 AND LANGUAGE = l_lang;
1884
1885 BIL_BI_UTIL_PKG.GET_PAGE_PARAMS(p_page_parameter_tbl => p_page_parameter_tbl
1886 ,p_region_id => l_region_id
1887 ,x_period_type => l_period_type
1888 ,x_conv_rate_selected => l_conv_rate_selected
1889 ,x_sg_id => l_sg_id
1890 ,x_parent_sg_id => l_parent_sales_group_id
1891 ,x_resource_id => l_resource_id
1892 ,x_prodcat_id => l_prodcat_id
1893 ,x_curr_page_time_id => l_curr_page_time_id
1894 ,x_prev_page_time_id => l_prev_page_time_id
1895 ,x_comp_type => l_comp_type
1896 ,x_parameter_valid => l_parameter_valid
1897 ,x_as_of_date => l_curr_as_of_date
1898 ,x_page_period_type => l_page_period_type
1899 ,x_prior_as_of_date => l_prev_date
1900 ,x_record_type_id => l_record_type_id
1901 ,x_viewby => l_viewby );
1902
1903
1904 /*
1905 BIL_BI_UTIL_PKG.GET_LATEST_SNAP_DATE(p_page_parameter_tbl => p_page_parameter_tbl
1906 ,p_as_of_date => l_curr_as_of_date
1907 ,p_period_type => NULL
1908 ,x_snapshot_date => l_snap_date);
1909 */
1910
1911
1912 /*** Query column mapping ******************************************************
1913 Internal Name Region Item Name Grand Total
1914 BIL_MEASURE1 Forecast BIL_MEASURE15
1915 BIL_MEASURE2 Prior Forecast BIL_MEASURE16
1916 BIL_MEASURE3 Change BIL_MEASURE17
1917 BIL_MEASURE4 Total Judgement BIL_MEASURE18
1918 BIL_MEASURE5 Change BIL_MEASURE19
1919 BIL_MEASURE6 Forecast Sub BIL_MEASURE20
1920 BIL_MEASURE7 Prior Forecast Sub BIL_MEASURE21
1921 BIL_MEASURE8 Change BIL_MEASURE22
1922 BIL_MEASURE9 Pipeline BIL_MEASURE23
1923 BIL_MEASURE10 Prior Pipeline BIL_MEASURE24
1924 BIL_MEASURE11 Change BIL_MEASURE25
1925 BIL_MEASURE12 Weighted Pipeline BIL_MEASURE26
1926 BIL_MEASURE13 Prior Weighted Pipeline BIL_MEASURE27
1927 BIL_MEASURE14 Change BIL_MEASURE28
1928 *******************************************************************************/
1929
1930
1931 IF l_parameter_valid THEN
1932
1933 l_viewby:='ITEM+ENI_ITEM_VBH_CAT';
1934
1935
1936 BIL_BI_UTIL_PKG.GET_PRIOR_PRIOR_TIME (p_comp_type => l_comp_type,
1937 p_period_type => l_page_period_type,
1938 p_prev_date => l_prev_date,
1939 p_prev_page_time_id => l_prev_page_time_id,
1940 x_prior_prior_date => l_prior_prior_date,
1941 x_prior_prior_time_id => l_prior_prior_time_id);
1942
1943
1944 IF l_prodcat_id IS NULL THEN
1948 END IF;
1945 l_prodcat_id := 'All';
1946 ELSE
1947 l_prodcat_id := to_number(REPLACE(l_prodcat_id,'''',''));
1949
1950 IF l_conv_rate_selected = 0 THEN
1951 l_currency_suffix := '_s';
1952 ELSE
1953 l_currency_suffix := '';
1954 END IF;
1955
1956 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1957
1958 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1959 MODULE => g_pkg || l_proc,
1960 MESSAGE => 'l_parameter_valid = true');
1961 END IF;
1962
1963 l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1 (VIEWBY, VIEWBYID, SORTORDER,BIL_MEASURE28,'||
1964 'BIL_MEASURE2,BIL_MEASURE3,BIL_MEASURE4,BIL_MEASURE5,'||
1965 'BIL_MEASURE6,BIL_MEASURE7,BIL_MEASURE8,BIL_MEASURE9,BIL_MEASURE10,BIL_MEASURE11,'||
1966 'BIL_MEASURE12,BIL_URL1,BIL_URL2)';
1967
1968
1969 l_pipe_url:=BIL_BI_UTIL_PKG.GET_DRILL_LINKS(p_view_by => l_viewby
1970 ,p_salesgroup_id => l_sg_id
1971 ,p_resource_id => l_resource_id);
1972
1973 l_url_str:='pFunctionName=BIL_BI_FSTOVER_R&pParamIds=Y&VIEW_BY='||l_viewby||'&VIEW_BY_NAME=VIEW_BY_ID';
1974 l_cat_url := 'pFunctionName=BIL_BI_FRCST_PRODCAT_R&pParamIds=Y&VIEW_BY='||l_viewby||'&VIEW_BY_NAME=VIEW_BY_ID';
1975
1976 /* Get the Prefix for the Open amt based upon Period Type and Compare To Params */
1977
1978
1979 l_prev_pipe_amt := BIL_BI_UTIL_PKG.GET_PIPE_COL_NAMES(
1980 p_period_type => l_page_period_type ,
1981 p_compare_to => l_comp_type,
1982 p_column_type => 'P',
1983 p_curr_suffix => l_currency_suffix
1984 );
1985
1986
1987
1988 l_prv_wt_pipe_amt := BIL_BI_UTIL_PKG.GET_PIPE_COL_NAMES(
1989 p_period_type => l_page_period_type ,
1990 p_compare_to => l_comp_type,
1991 p_column_type => 'W',
1992 p_curr_suffix => l_currency_suffix
1993 );
1994
1995
1996 /* Use the BIL_BI_UTIL_PKG.GET_PIPE_MV proc to get the MV name and snap date for Pipeline/Open Amts. */
1997
1998 BIL_BI_UTIL_PKG.GET_PIPE_MV(
1999 p_asof_date => l_curr_as_of_date ,
2000 p_period_type => l_page_period_type ,
2001 p_compare_to => l_comp_type ,
2002 p_prev_date => l_prev_date,
2003 p_page_parameter_tbl => p_page_parameter_tbl,
2004 x_pipe_mv => l_open_mv_new ,
2005 x_snapshot_date => l_snapshot_date ,
2006 x_prev_snap_date => l_prev_snap_date
2007 );
2008
2009
2010
2011 /*
2012 BIL_MEASURE28 frcst
2013 BIL_MEASURE2 priorfrcst
2014 BIL_MEASURE3 priorpriorfrcst
2015 BIL_MEASURE4 oppfcst
2016 BIL_MEASURE5 prior_oppfrcst
2017 BIL_MEASURE6 priorprior_oppfrcst
2018 BIL_MEASURE7 frcst_sub
2019 BIL_MEASURE8 priorfrcst_sub
2020 BIL_MEASURE9 pipeline
2021 BIL_MEASURE10 priorpipeline
2022 BIL_MEASURE11 wtdpipeline
2023 BIL_MEASURE12 priorwtdpipeline
2024 BIL_MEASURE13 Total Judgement SUM(BIL_MEASURE28) - SUM(BIL_MEASURE4)
2025 BIL_MEASURE14 Prior Total Judgement SUM(BIL_MEASURE2) - SUM(BIL_MEASURE5)
2026 BIL_MEASURE15 Prior Prior Total Judgement SUM(BIL_MEASURE3) - SUM(BIL_MEASURE6)
2027 */
2028
2029 l_outer_select := 'SELECT VIEWBY '||
2030 ',VIEWBYID '||
2031 ',BIL_MEASURE28 BIL_MEASURE1 '||
2032 ',BIL_MEASURE2 BIL_MEASURE23 '||
2033 ',BIL_MEASURE3 BIL_MEASURE24 '||
2034 ',(BIL_MEASURE28-BIL_MEASURE2)/ABS(DECODE(BIL_MEASURE2,0,null,BIL_MEASURE2))*100 BIL_MEASURE2 '||
2035 ',BIL_MEASURE4 BIL_MEASURE25 '||
2036 ',BIL_MEASURE5 BIL_MEASURE26 '||
2037 ',BIL_MEASURE6 BIL_MEASURE27 '||
2038 ',BIL_MEASURE13 BIL_MEASURE3 '||
2039 ',(BIL_MEASURE13-BIL_MEASURE14)/ABS(DECODE(BIL_MEASURE14,0,null,BIL_MEASURE14))*100 BIL_MEASURE4 '||
2040 ',BIL_MEASURE14 BIL_MEASURE5 '||
2041 ',(BIL_MEASURE14-BIL_MEASURE15)/ABS(DECODE(BIL_MEASURE15,0,null,BIL_MEASURE15))*100 BIL_MEASURE6 '||
2042 ',BIL_MEASURE7 BIL_MEASURE7 '||
2043 ',BIL_MEASURE8 BIL_MEASURE28 '||
2044 ',(BIL_MEASURE7-BIL_MEASURE8)/ABS(DECODE(BIL_MEASURE8, 0, null, BIL_MEASURE8))*100 BIL_MEASURE8 '||
2045 ',BIL_MEASURE9 BIL_MEASURE9 '||
2046 ',BIL_MEASURE10 BIL_MEASURE29 '||
2047 ',(BIL_MEASURE9-BIL_MEASURE10)/ABS(DECODE(BIL_MEASURE10, 0 ,null, BIL_MEASURE10))*100 BIL_MEASURE10 '||
2048 ',BIL_MEASURE11 BIL_MEASURE11 '||
2049 ',BIL_MEASURE12 BIL_MEASURE30 '||
2050 ',(BIL_MEASURE11-BIL_MEASURE12)/ABS(DECODE(BIL_MEASURE12, 0 ,null, BIL_MEASURE12))*100 BIL_MEASURE12 '||
2054 'SUM(BIL_MEASURE2) OVER()))*100 BIL_MEASURE14 '||
2051 ',SUM(BIL_MEASURE28) OVER() BIL_MEASURE13 '||
2052 ',SUM(BIL_MEASURE2) OVER() BIL_MEASURE32 '||
2053 ',(SUM(BIL_MEASURE28) OVER()-SUM(BIL_MEASURE2) OVER())/ABS(DECODE(SUM(BIL_MEASURE2) OVER(),0,null, '||
2055 ',SUM(BIL_MEASURE13) OVER() BIL_MEASURE15 '||
2056 ',(SUM(BIL_MEASURE13) OVER()-SUM(BIL_MEASURE14) OVER())/ABS(DECODE(SUM(BIL_MEASURE14) OVER(),0,null, '||
2057 'SUM(BIL_MEASURE14) OVER()))*100 BIL_MEASURE16 '||
2058 ',SUM(BIL_MEASURE7) OVER() BIL_MEASURE17 '||
2059 ',(SUM(BIL_MEASURE7) OVER()-SUM(BIL_MEASURE8) OVER())/ABS(DECODE(SUM(BIL_MEASURE8) OVER(), 0, null, '||
2060 'SUM(BIL_MEASURE8) OVER()))*100 BIL_MEASURE18 '||
2061 ',SUM(BIL_MEASURE9) OVER() BIL_MEASURE19 '||
2062 ',(SUM(BIL_MEASURE9) OVER()-SUM(BIL_MEASURE10) OVER())/ABS(DECODE(SUM(BIL_MEASURE10) OVER(), 0 ,null, '||
2063 'SUM(BIL_MEASURE10) OVER()))*100 BIL_MEASURE20 '||
2064 ',SUM(BIL_MEASURE11) OVER() BIL_MEASURE21 '||
2065 ',(SUM(BIL_MEASURE11) OVER()-SUM(BIL_MEASURE12) OVER())/ABS(DECODE(SUM(BIL_MEASURE12) OVER(), 0 ,null, '||
2066 'SUM(BIL_MEASURE12) OVER()))*100 BIL_MEASURE22 '||
2067 ', BIL_URL1 '||
2068 ',DECODE('''||l_curr_as_of_date||''',TRUNC(SYSDATE),'||
2069 'DECODE(BIL_URL2,NULL,NULL,BIL_URL2||'||'''BIL_DIMENSION1=PIPELINE'''||')) BIL_URL2 ';
2070
2071
2072 l_sql_stmnt1 := '(case when sumry.effective_time_id = :l_curr_page_time_id AND '||
2073 'cal.report_date =:l_curr_as_of_date '||
2074 'then sumry.forecast_amt'||l_currency_suffix||' else NULL end) AS frcst '||
2075 ',(case when sumry.effective_time_id = :l_prev_page_time_id AND '||
2076 'cal.report_date =:l_prev_date '||
2077 'then sumry.forecast_amt'||l_currency_suffix||' else NULL end) AS priorFrcst '||
2078 ',(case when sumry.effective_time_id = :l_prior_prior_time_id AND '||
2079 'cal.report_date =:l_prior_prior_date '||
2080 'then sumry.forecast_amt'||l_currency_suffix||' else NULL end) AS priorpriorFrcst '||
2081 ',(case when sumry.effective_time_id = :l_curr_page_time_id AND '||
2082 'cal.report_date =:l_curr_as_of_date '||
2083 'then sumry.opp_forecast_amt'||l_currency_suffix||' else NULL end) AS oppfrcst '||
2084 ',(case when sumry.effective_time_id = :l_prev_page_time_id AND '||
2085 'cal.report_date =:l_prev_date '||
2086 'then sumry.opp_forecast_amt'||l_currency_suffix||' else NULL end) AS prior_oppFrcst '||
2087 ',(case when sumry.effective_time_id = :l_prior_prior_time_id AND '||
2088 'cal.report_date =:l_prior_prior_date '||
2089 'then sumry.opp_forecast_amt'||l_currency_suffix||' else NULL end) AS priorprior_oppFrcst '||
2090 ',(case when sumry.effective_time_id = :l_curr_page_time_id AND '||
2091 'cal.report_date =:l_curr_as_of_date '||
2092 'then sumry.forecast_amt_sub'||l_currency_suffix||' else NULL end) AS frcst_sub '||
2093 ',(case when sumry.effective_time_id = :l_prev_page_time_id AND '||
2094 'cal.report_date =:l_prev_date '||
2095 'then sumry.forecast_amt_sub'||l_currency_suffix||' else NULL end) As PriorFrcst_sub '||
2096 ',NULL AS pipeline '||
2097 ',NULL AS priorPipeline '||
2098 ',NULL AS wtdPipeline '||
2099 ',NULL AS priorWtdPipeline ';
2100
2101
2102 l_where_clause1 := ' WHERE sumry.TXN_TIME_ID = cal.TIME_ID '||
2103 'AND sumry.TXN_PERIOD_TYPE_ID = cal.PERIOD_TYPE_ID '||
2104 'AND bitand(cal.record_type_id, :l_bitand_id)= :l_bitand_id '||
2105 'AND sumry.EFFECTIVE_PERIOD_TYPE_ID = :l_period_type '||
2106 'AND NVL(sumry.credit_type_id, :l_fst_crdt_type) = :l_fst_crdt_type '||
2107 'AND cal.report_date in (:l_curr_as_of_date, :l_prev_date, :l_prior_prior_date) '||
2108 'AND sumry.effective_time_id in (:l_curr_page_time_id, :l_prev_page_time_id, :l_prior_prior_time_id) ';
2109
2110 /*
2111 l_sql_stmnt3 :=' NULL AS frcst '||
2112 ',NULL AS priorFrcst '||
2113 ',NULL AS frcst_sub '||
2114 ',NULL AS PriorFrcst_sub '||
2115 ',(case when :l_snap_date = sumry.snap_date then '||
2116 'decode(:l_period_type, '||
2117 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
2118 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
2119 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
2120 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
2121 ')'||
2122 'end) AS wtdPipeline '||
2123 ',(case when :l_prev_date = sumry.snap_date then '||
2124 'decode(:l_period_type, '||
2125 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
2126 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
2127 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
2128 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
2129 ')'||
2130 'end) AS priorWtdPipeline '||
2131 ',(case when :l_snap_date = sumry.snap_date then '||
2132 'decode(:l_period_type, '||
2133 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
2134 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
2135 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
2136 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
2137 ')'||
2138 'end) AS pipeline '||
2139 ',(case when :l_prev_date = sumry.snap_date then '||
2140 'decode(:l_period_type, '||
2141 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
2142 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
2143 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
2144 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
2145 ')'||
2146 'end) AS priorPipeline ';
2147
2148
2149 l_where_clause3 := ' WHERE sumry.snap_date in (:l_snap_date, :l_prev_date) ' ;
2150
2151 */
2152
2153 l_pipe_select1 := ' NULL AS frcst '||
2154 ',NULL AS priorFrcst '||
2155 ',NULL AS frcst_sub '||
2159 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
2156 ',NULL AS PriorFrcst_sub '||
2157 ',(case when :l_snapshot_date = sumry.snap_date then '||
2158 'decode(:l_period_type, '||
2160 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
2161 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
2162 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
2163 ')'||
2164 'end) AS wtdPipeline ';
2165
2166 IF (l_open_mv_new = 'BIL_BI_PIPE_G_MV') THEN
2167
2168 l_pipe_select2 := ',(case when :l_prev_snap_date = sumry.snap_date then '||
2169 'decode(:l_period_type, '||
2170 '128,WTD_PIPELINE_AMT_YEAR'||l_currency_suffix||','||
2171 '64,WTD_PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
2172 '32,WTD_PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
2173 '16,WTD_PIPELINE_AMT_WEEK'||l_currency_suffix||
2174 ')'||
2175 'end) AS priorWtdPipeline ';
2176 ELSE
2177 l_pipe_select2 := ' ,(CASE WHEN sumry.snap_date = :l_snapshot_date THEN '||
2178 ''||l_prv_wt_pipe_amt||' '||
2179 ' ELSE NULL '||
2180 ' END) AS priorWtdPipeline ';
2181 END IF;
2182
2183
2184 l_pipe_select3 := ',(case when :l_snapshot_date = sumry.snap_date then '||
2185 'decode(:l_period_type, '||
2186 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
2187 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
2188 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
2189 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
2190 ')'||
2191 'end) AS pipeline ';
2192
2193 IF (l_open_mv_new = 'BIL_BI_PIPE_G_MV') THEN
2194 l_pipe_select4 := ',(case when :l_prev_snap_date = sumry.snap_date then '||
2195 'decode(:l_period_type, '||
2196 '128,PIPELINE_AMT_YEAR'||l_currency_suffix||','||
2197 '64,PIPELINE_AMT_QUARTER'||l_currency_suffix||','||
2198 '32,PIPELINE_AMT_PERIOD'||l_currency_suffix||','||
2199 '16,PIPELINE_AMT_WEEK'||l_currency_suffix||
2200 ')'||
2201 'end) AS priorPipeline ';
2202 ELSE
2203 l_pipe_select4 := ' ,(CASE WHEN sumry.snap_date =:l_snapshot_date THEN '||
2204 ''||l_prev_pipe_amt||' '||
2205 ' ELSE NULL '||
2206 ' END) AS priorPipeline ';
2207
2208 END IF;
2209
2210
2211 l_sql_stmnt3 := l_pipe_select1 || l_pipe_select2 || l_pipe_select3 || l_pipe_select4 ;
2212
2213
2214 IF (l_open_mv_new = 'BIL_BI_PIPE_G_MV') THEN
2215 l_where_clause3 := ' WHERE sumry.snap_date in (:l_snapshot_date, :l_prev_snap_date) ' ;
2216 ELSE
2217 l_where_clause3 := ' WHERE sumry.snap_date in (:l_snapshot_date ) ' ;
2218 END IF;
2219
2220
2221
2222 BIL_BI_UTIL_PKG.GET_FORECAST_PROFILES( x_fstcrdttype => l_fst_crdt_type );
2223
2224
2225 BIL_BI_UTIL_PKG.GET_GLOBAL_CONTS(x_bitand_id =>l_bitand_id,
2226 x_calendar_id =>l_calendar_id,
2227 x_curr_date =>l_bis_sysdate,
2228 x_fii_struct =>l_fii_struct);
2229
2230 l_sg_id_num := TO_NUMBER(REPLACE(l_sg_id, ''''));
2231
2232 l_null_removal_clause := 'NOT(BIL_MEASURE1 IS NULL AND BIL_MEASURE25 IS NULL AND BIL_MEASURE3 IS NULL AND '||
2233 ' BIL_MEASURE7 IS NULL AND BIL_MEASURE9 IS NULL AND'||
2234 ' BIL_MEASURE11 IS NULL) ';
2235
2236 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP1';
2237
2238
2239 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2240
2241 l_sql_error_desc :=
2242 'l_viewby => '||l_viewby||', '||
2243 'l_curr_page_time_id => '|| l_curr_page_time_id ||', ' ||
2244 'l_prev_page_time_id => '|| l_prev_page_time_id ||', ' ||
2245 'l_prior_prior_time_id => '|| l_prior_prior_time_id ||', ' ||
2246 'l_curr_as_of_date => '|| l_curr_as_of_date ||', ' ||
2247 'l_prev_date => '|| l_prev_date ||', ' ||
2248 'l_prior_prior_date => '|| l_prior_prior_date ||', ' ||
2249 'l_conv_rate_selected => '|| l_conv_rate_selected ||', ' ||
2250 'l_bitand_id => '|| l_bitand_id ||', ' ||
2251 'l_period_type => '|| l_period_type ||', ' ||
2252 'l_parent_sales_group_id => '|| l_parent_sales_group_id ||', '||
2253 'l_sg_id_num => '|| l_sg_id_num ||', ' ||
2254 'l_resource_id => '||l_resource_id||', '||
2255 'l_fst_crdt_type => '|| l_fst_crdt_type ||', ' ||
2256 'l_calendar_id => '|| l_calendar_id;
2257
2258
2259 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
2260 MODULE => g_pkg || l_proc || 'l_sql_error_desc',
2261 MESSAGE => l_sql_error_desc);
2262
2263 END IF;
2264
2265 BIL_BI_UTIL_PKG.GET_PC_NOROLLUP_WHERE_CLAUSE(
2266 p_prodcat => l_prodcat_id,
2267 p_viewby => l_viewby,
2268 x_denorm => l_pipe_denorm,
2269 x_where_clause => l_pipe_product_where_clause);
2270
2271 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2272
2273 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
2274 MODULE => g_pkg || l_proc || ' l_viewby =>'||l_viewby,
2275 MESSAGE => ' Prod cat '||l_prodcat_id);
2276
2277 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
2278 MODULE => g_pkg || l_proc || ' l_pipe_prod_where =>',
2279 MESSAGE => ' l_pipe_product_where_clause '||l_pipe_product_where_clause);
2280 END IF;
2281
2282 IF l_parent_sales_group_id IS NULL THEN
2283 IF l_resource_id IS NULL THEN
2284 l_parent_sls_grp_where_clause := ' AND sumry.parent_sales_group_id IS NULL ';
2285 ELSE
2286 l_parent_sls_grp_where_clause := ' AND sumry.parent_sales_group_id = sumry.sales_group_id ';
2287 END IF;
2288 ELSE
2289 IF l_resource_id IS NULL THEN
2290 l_parent_sls_grp_where_clause :=
2291 ' AND sumry.parent_sales_group_id = :l_parent_sales_group_id ';
2292 ELSE
2293 l_parent_sls_grp_where_clause :=
2294 ' AND sumry.parent_sales_group_id = :l_sg_id_num ';
2295 END IF;
2296 END IF;
2297
2298 l_pc_select := ' SELECT
2299 decode(tmp1.viewbyid, -1,:l_unassigned_value,
2300 mtl.DESCRIPTION || '' ('' || mtl.CATEGORY_CONCAT_SEGS ||'')'') VIEWBY,
2301 sortorder,
2302 frcst ,
2303 priorFrcst,
2304 priorpriorFrcst,
2305 oppfrcst,
2306 prior_oppfrcst,
2307 priorprior_oppfrcst,
2308 frcst_sub,
2309 priorFrcst_sub,
2310 pipeline,
2311 priorPipeline,
2312 wtdPipeline,
2313 priorWtdPipeline ,
2314 VIEWBYID,
2315 salesrep_id,
2316 DECODE(tmp1.viewbyid,''-1'',NULL,'''||l_cat_url||''') BIL_URL1,
2317 BIL_URL2 '||
2318 ' FROM (
2319 SELECT
2320 1 SORTORDER,
2321 NULL frcst ,
2322 NULL priorFrcst,
2323 NULL priorpriorfrcst,
2324 NULL oppfrcst,
2325 NULL prior_oppfrcst,
2326 NULL priorprior_oppfrcst,
2327 NULL frcst_sub,
2328 NULL priorFrcst_sub,
2329 SUM(pipeline) pipeline,
2330 SUM(priorPipeline) priorPipeline,
2331 SUM(wtdPipeline) wtdPipeline,
2332 SUM(priorWtdPipeline) priorWtdPipeline ,
2333 pcd.parent_id VIEWBYID,
2334 salesrep_id,
2335 BIL_URL2
2336 ';
2337
2338 l_unassigned_value := BIL_BI_UTIL_PKG.GET_UNASSIGNED_PC;
2339
2340 l_sumry := 'BIL_BI_FST_PG_MV';
2341 -- l_sumry2 := 'BIL_BI_PIPE_G_MV';
2342 l_sumry2 := l_open_mv_new;
2343 l_where_clause3 := l_where_clause3||' AND sumry.grp_total_flag = 0 ';
2344 l_denorm := ' ,ENI_ITEM_PROD_CAT_LOOKUP_V pcd ';
2345
2346 IF l_prodcat_id = 'All' THEN
2347 l_product_where_clause1 := ' pcd.top_node_flag = :l_yes '||
2348 ' AND pcd.parent_id = sumry.product_category_id '||
2349 ' AND pcd.child_id = sumry.product_category_id '||
2350 ' AND sumry.product_category_id = pcd.id ';
2351 ELSE
2352 l_product_where_clause1 := ' sumry.product_category_id = pcd.child_id AND '||
2353 ' pcd.parent_id=:l_prodcat_id
2354 AND sumry.product_category_id = pcd.id AND '||
2355 ' NOT((assign_to_cat = 0 AND pcd.child_id = pcd.parent_id)) ';
2356
2357 END IF;
2358
2359 IF l_prodcat_id = 'All' THEN
2360
2361 l_custom_sql :=
2362 ' SELECT VIEWBY '||
2363 ',VIEWBYID '||
2364 ',SORTORDER '||
2365 ',SUM(frcst) BIL_MEASURE1 '||
2366 ',SUM(priorFrcst) BIL_MEASURE2 '||
2367 ',SUM(priorpriorFrcst) BIL_MEASURE3 '||
2368 ',SUM(oppFrcst) BIL_MEASURE4 '||
2369 ',SUM(prior_oppFrcst) BIL_MEASURE5 '||
2370 ',SUM(priorprior_oppFrcst) BIL_MEASURE6 '||
2371 ',SUM(frcst_sub) BIL_MEASURE7 '||
2372 ',SUM(priorFrcst_sub) BIL_MEASURE8 '||
2373 ',SUM(pipeline) BIL_MEASURE9 '||
2374 ',SUM(priorPipeline) BIL_MEASURE10 '||
2375 ',SUM(wtdPipeline) BIL_MEASURE11 '||
2376 ',SUM(priorWtdPipeline) BIL_MEASURE12 '||
2380 ' SELECT /*+ LEADING(cal) */ '||
2377 ',BIL_URL1 '||
2378 ','''||l_pipe_url||''' BIL_URL2 '||
2379 ' FROM ( ' ||
2381 ' pcd.value VIEWBY '||
2382 ', 1 sortorder, '||
2383 l_sql_stmnt1||
2384 ',pcd.id VIEWBYID '||
2385 ',sumry.salesrep_id salesrep_id '||
2386 ',DECODE(pcd.id,''-1'',NULL,'''||l_cat_url||''') BIL_URL1 '||
2387 ',NULL BIL_URL2 '||
2388 ' FROM '||l_fii_struct||' cal,'||
2389 l_sumry||' sumry '||
2390 l_denorm||' '||
2391 ' '||l_where_clause1||' AND '||
2392 l_product_where_clause1||
2393 ' AND sumry.sales_group_id = :l_sg_id_num '||
2394 l_parent_sls_grp_where_clause ||
2395 ' AND cal.xtd_flag = :l_yes ';
2396
2397 IF l_resource_id IS NULL THEN
2398 l_custom_sql :=l_custom_sql ||' AND sumry.salesrep_id IS NULL ';
2399 ELSE
2400 l_custom_sql :=l_custom_sql ||' AND sumry.salesrep_id = :l_resource_id ';
2401 END IF;
2402
2403 l_custom_sql := l_custom_sql||
2404 ' UNION ALL '||
2405 l_pc_select ||
2406 ' FROM ('||
2407 ' SELECT NULL VIEWBY'||
2408 ', 1 sortorder, '||l_sql_stmnt3||
2409 ',NULL VIEWBYID'||
2410 ',sumry.salesrep_id salesrep_id '||
2411 ',sumry.product_category_id product_category_id'||
2412 ',NULL BIL_URL2 '||
2413 ' FROM '||l_sumry2||' sumry '||
2414 ' '||l_where_clause3||' '||
2415 ' AND sumry.sales_group_id = :l_sg_id_num '||
2416 l_parent_sls_grp_where_clause;
2417
2418
2419 IF l_resource_id IS NULL THEN
2420 l_custom_sql :=l_custom_sql ||' AND sumry.salesrep_id IS NULL ';
2421 ELSE
2422 l_custom_sql :=l_custom_sql ||' AND sumry.salesrep_id = :l_resource_id ';
2423 END IF;
2424
2425 /*
2426 l_pipe_product_where_clause := ' AND sumry.product_category_id =pcd.child_id
2427 AND pcd.object_type = ''CATEGORY_SET''
2428 AND pcd.object_id = d.category_set_id
2429 AND d.functional_area_id = 11
2430 AND pcd.dbi_flag = ''Y''
2431 AND pcd.top_node_flag = :l_yes ';
2432 l_pipe_denorm := ',eni_denorm_hierarchies pcd, mtl_default_category_sets d ';
2433 */
2434
2435 l_custom_sql := l_custom_sql||
2436 ' ) sumry '||l_pipe_denorm||
2437 ' WHERE 1=1 '||l_pipe_product_where_clause||
2438 ' GROUP BY pcd.parent_id, salesrep_id)tmp1 , mtl_categories_v mtl '||
2439 ' WHERE mtl.category_id (+) = tmp1.viewbyid)' ||
2440 ' GROUP BY VIEWBY, VIEWBYID, SORTORDER ,BIL_URL1, BIL_URL2 ';
2441
2442
2443 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2444
2445 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
2446 MODULE => g_pkg || l_proc || 'Prod cat Viewby ',
2447 MESSAGE => ' l_custom_sql length '||LENGTH(l_custom_sql));
2448
2449 END IF;
2450
2451
2452 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2453 l_ind :=1;
2454 l_len:= length(l_custom_sql);
2455
2456 WHILE l_ind <= l_len LOOP
2457 l_str:= substr(l_custom_sql, l_ind, 4000);
2458
2459 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
2460 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
2461 MESSAGE => l_str);
2462
2463 l_ind := l_ind + 4000;
2464
2465 END LOOP;
2466 END IF;
2467
2468 IF l_parent_sales_group_id IS NULL THEN
2469 IF l_resource_id IS NULL THEN
2470
2471 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
2472 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2473 USING
2474 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
2475 l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
2476 l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
2477 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
2478 l_bitand_id, l_bitand_id, l_period_type, l_fst_crdt_type, l_fst_crdt_type,
2479 l_curr_as_of_date, l_prev_date, l_prior_prior_date, l_curr_page_time_id,
2480 l_prev_page_time_id, l_prior_prior_time_id, l_yes, l_sg_id_num, l_yes,
2481 l_unassigned_value,
2482 l_snapshot_date, l_period_type,
2486 l_snapshot_date,
2483 l_snapshot_date,
2484 l_snapshot_date, l_period_type,
2485 l_snapshot_date,
2487 l_sg_id_num, l_yes;
2488 COMMIT;
2489 ELSE
2490 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2491 USING
2492 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
2493 l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
2494 l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
2495 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
2496 l_bitand_id, l_bitand_id, l_period_type, l_fst_crdt_type, l_fst_crdt_type,
2497 l_curr_as_of_date, l_prev_date, l_prior_prior_date, l_curr_page_time_id,
2498 l_prev_page_time_id, l_prior_prior_time_id, l_yes, l_sg_id_num, l_yes,
2499 l_unassigned_value,
2500 l_snapshot_date, l_period_type,
2501 l_prev_snap_date, l_period_type,
2502 l_snapshot_date, l_period_type,
2503 l_prev_snap_date, l_period_type,
2504 l_snapshot_date, l_prev_snap_date,
2505 l_sg_id_num, l_yes;
2506 COMMIT;
2507 END IF;
2508 ELSE
2509 IF ( l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
2510 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2511 USING l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
2512 l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
2513 l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
2514 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date, l_bitand_id,
2515 l_bitand_id, l_period_type, l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
2516 l_prev_date, l_prior_prior_date, l_curr_page_time_id,
2517 l_prev_page_time_id, l_prior_prior_time_id, l_yes, l_sg_id_num, l_yes, l_resource_id,
2518 l_unassigned_value,
2519 l_snapshot_date, l_period_type,
2520 l_snapshot_date,
2521 l_snapshot_date, l_period_type,
2522 l_snapshot_date,
2523 l_snapshot_date,
2524 l_sg_id_num, l_resource_id, l_yes;
2525 ELSE
2526 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2527 USING l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
2528 l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
2529 l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
2530 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date, l_bitand_id,
2531 l_bitand_id, l_period_type, l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
2532 l_prev_date, l_prior_prior_date, l_curr_page_time_id,
2533 l_prev_page_time_id, l_prior_prior_time_id, l_yes, l_sg_id_num, l_yes, l_resource_id,
2534 l_unassigned_value,
2535 l_snapshot_date, l_period_type,
2536 l_prev_snap_date, l_period_type,
2537 l_snapshot_date, l_period_type,
2538 l_prev_snap_date, l_period_type,
2539 l_snapshot_date, l_prev_snap_date,
2540 l_sg_id_num, l_resource_id, l_yes;
2541 END IF;
2542 END IF;
2543 ELSE -- parent sales group id is not null
2544 IF l_resource_id IS NULL THEN
2545
2546 IF ( l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
2547 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2548 USING l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id,
2549 l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
2550 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id,
2551 l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
2552 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id,
2553 l_prev_date, l_bitand_id, l_bitand_id, l_period_type,
2554 l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
2555 l_prev_date, l_prior_prior_date, l_curr_page_time_id, l_prev_page_time_id,
2556 l_prior_prior_time_id, l_yes, l_sg_id_num, l_parent_sales_group_id, l_yes,
2557 l_unassigned_value,
2558 l_snapshot_date, l_period_type,
2559 l_snapshot_date,
2560 l_snapshot_date, l_period_type,
2561 l_snapshot_date,
2562 l_snapshot_date,
2563 l_sg_id_num, l_parent_sales_group_id, l_yes;
2564 ELSE
2565 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2566 USING l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id,
2567 l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
2568 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id,
2569 l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
2570 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id,
2571 l_prev_date, l_bitand_id, l_bitand_id, l_period_type,
2572 l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
2573 l_prev_date, l_prior_prior_date, l_curr_page_time_id, l_prev_page_time_id,
2574 l_prior_prior_time_id, l_yes, l_sg_id_num, l_parent_sales_group_id, l_yes,
2575 l_unassigned_value,
2576 l_snapshot_date, l_period_type,
2577 l_prev_snap_date, l_period_type,
2578 l_snapshot_date, l_period_type,
2579 l_prev_snap_date, l_period_type,
2580 l_snapshot_date, l_prev_snap_date,
2581 l_sg_id_num, l_parent_sales_group_id, l_yes;
2582 END IF;
2583 ELSE
2584
2588 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
2585 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
2586 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2587 USING
2589 l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
2590 l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
2591 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date, l_bitand_id,
2592 l_bitand_id, l_period_type, l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
2593 l_prev_date, l_prior_prior_date, l_curr_page_time_id, l_prev_page_time_id,
2594 l_prior_prior_time_id, l_yes, l_sg_id_num, l_sg_id_num, l_yes, l_resource_id,
2595 l_unassigned_value,
2596 l_snapshot_date, l_period_type,
2597 l_snapshot_date,
2598 l_snapshot_date, l_period_type,
2599 l_snapshot_date,
2600 l_snapshot_date,
2601 l_sg_id_num, l_sg_id_num, l_resource_id, l_yes;
2602 ELSE
2603 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2604 USING
2605 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date,
2606 l_prior_prior_time_id, l_prior_prior_date, l_curr_page_time_id, l_curr_as_of_date,
2607 l_prev_page_time_id, l_prev_date, l_prior_prior_time_id, l_prior_prior_date,
2608 l_curr_page_time_id, l_curr_as_of_date, l_prev_page_time_id, l_prev_date, l_bitand_id,
2609 l_bitand_id, l_period_type, l_fst_crdt_type, l_fst_crdt_type, l_curr_as_of_date,
2610 l_prev_date, l_prior_prior_date, l_curr_page_time_id, l_prev_page_time_id,
2611 l_prior_prior_time_id, l_yes, l_sg_id_num, l_sg_id_num, l_yes, l_resource_id,
2612 l_unassigned_value,
2613 l_snapshot_date, l_period_type,
2614 l_prev_snap_date, l_period_type,
2615 l_snapshot_date, l_period_type,
2616 l_prev_snap_date, l_period_type,
2617 l_snapshot_date, l_prev_snap_date,
2618 l_sg_id_num, l_sg_id_num, l_resource_id, l_yes;
2619 END IF;
2620
2621 END IF;
2622 END IF;
2623
2624
2625 ELSE -- product cat not all
2626
2627 l_custom_sql := ' SELECT VIEWBY '||
2628 ',VIEWBYID '||
2629 ',SORTORDER '||
2630 ',SUM(frcst) BIL_MEASURE1 '||
2631 ',SUM(priorFrcst) BIL_MEASURE2 '||
2632 ',SUM(priorpriorFrcst) BIL_MEASURE3 '||
2633 ',SUM(oppFrcst) BIL_MEASURE4 '||
2634 ',SUM(prior_oppFrcst) BIL_MEASURE5 '||
2635 ',SUM(priorprior_oppFrcst) BIL_MEASURE6 '||
2636 ',SUM(frcst_sub) BIL_MEASURE7 '||
2637 ',SUM(priorFrcst_sub) BIL_MEASURE8 '||
2638 ',NULL BIL_MEASURE9 '||
2639 ',NULL BIL_MEASURE10 '||
2640 ',NULL BIL_MEASURE11 '||
2641 ',NULL BIL_MEASURE12 '||
2642 ',BIL_URL1 '||
2643 ',DECODE(VIEWBY,'||':l_cat_assign'||',NULL,'''||l_pipe_url||''') BIL_URL2 '||
2644 ' FROM '||
2645 '( '||
2646 ' SELECT /*+ LEADING(cal) */ '||
2647 ' decode(pcd.parent_id,pcd.child_id,'||
2648 ' decode(sumry.assign_to_cat,0,pcd.value,:l_cat_assign), '||
2649 ' pcd.value) VIEWBY '||
2650 ', decode(pcd.parent_id,pcd.id, 1, 2) sortorder, '||
2651 l_sql_stmnt1||
2652 ',pcd.id VIEWBYID'||
2653 ',SUMRY.salesrep_id salesrep_id '||
2654 ',decode(pcd.parent_id, pcd.child_id, null, '''||l_cat_url||''') BIL_URL1 '||
2655 ',NULL BIL_URL2 '||
2656 ' FROM '||l_fii_struct||' cal, '||
2657 l_sumry||' sumry '||
2658 l_denorm||' '||
2659 ' '||l_where_clause1||' AND '||l_product_where_clause1||
2660 ' AND sumry.sales_group_id = :l_sg_id_num '||
2661 l_parent_sls_grp_where_clause ||
2662 ' AND cal.xtd_flag = :l_yes ';
2663
2664
2665 IF l_resource_id IS NULL THEN
2666 l_custom_sql :=l_custom_sql ||' AND sumry.salesrep_id IS NULL ';
2667 ELSE
2668 l_custom_sql :=l_custom_sql ||' AND sumry.salesrep_id = :l_resource_id ';
2669 END IF;
2670
2671
2672 l_custom_sql := l_custom_sql ||
2673 ' )tmp1'||
2674 ' GROUP BY VIEWBY, VIEWBYID, SORTORDER,BIL_URL1,BIL_URL2 ';
2675
2676 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2677
2678 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
2679 MODULE => g_pkg || l_proc || 'Prod cat Viewby ',
2680 MESSAGE => ' Forecast Query Product Cat not All ');
2681 END IF;
2682
2683
2684
2685 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2686 l_ind :=1;
2687 l_len:= length(l_custom_sql);
2688
2689 WHILE l_ind <= l_len LOOP
2690 l_str:= substr(l_custom_sql, l_ind, 4000);
2691
2692 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
2693 MODULE => g_pkg || l_proc ||'.'|| 'Forecast Query =>',
2694 MESSAGE => l_str);
2695
2696 l_ind := l_ind + 4000;
2697
2698 END LOOP;
2699 END IF;
2700
2701
2702 IF l_parent_sales_group_id IS NULL THEN
2703 IF l_resource_id IS NULL THEN
2704 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2705 USING l_cat_assign, l_cat_assign,
2706 l_curr_page_time_id,
2707 l_curr_as_of_date,
2708 l_prev_page_time_id,
2709 l_prev_date,
2710 l_prior_prior_time_id,
2711 l_prior_prior_date,
2712 l_curr_page_time_id,
2713 l_curr_as_of_date,
2714 l_prev_page_time_id,
2715 l_prev_date,
2716 l_prior_prior_time_id,
2717 l_prior_prior_date,
2718 l_curr_page_time_id,
2719 l_curr_as_of_date,
2720 l_prev_page_time_id,
2721 l_prev_date,
2722 l_bitand_id,
2723 l_bitand_id,
2727 l_curr_as_of_date,
2724 l_period_type,
2725 l_fst_crdt_type,
2726 l_fst_crdt_type,
2728 l_prev_date,
2729 l_prior_prior_date,
2730 l_curr_page_time_id,
2731 l_prev_page_time_id,
2732 l_prior_prior_time_id,
2733 l_prodcat_id,
2734 l_sg_id_num,
2735 l_yes;
2736 ELSE
2737 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2738 USING l_cat_assign, l_cat_assign,
2739 l_curr_page_time_id,
2740 l_curr_as_of_date,
2741 l_prev_page_time_id,
2742 l_prev_date,
2743 l_prior_prior_time_id,
2744 l_prior_prior_date,
2745 l_curr_page_time_id,
2746 l_curr_as_of_date,
2747 l_prev_page_time_id,
2748 l_prev_date,
2749 l_prior_prior_time_id,
2750 l_prior_prior_date,
2751 l_curr_page_time_id,
2752 l_curr_as_of_date,
2753 l_prev_page_time_id,
2754 l_prev_date,
2755 l_bitand_id,
2756 l_bitand_id,
2757 l_period_type,
2758 l_fst_crdt_type,
2759 l_fst_crdt_type,
2760 l_curr_as_of_date,
2761 l_prev_date,
2762 l_prior_prior_date,
2763 l_curr_page_time_id,
2764 l_prev_page_time_id,
2765 l_prior_prior_time_id,
2766 l_prodcat_id,
2767 l_sg_id_num,
2768 l_yes,
2769 l_resource_id;
2770 END IF;
2771 ELSE-- parent sales group is not null
2772 IF l_resource_id IS NULL THEN
2773 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2774 USING l_cat_assign,l_cat_assign,
2775 l_curr_page_time_id,
2776 l_curr_as_of_date,
2777 l_prev_page_time_id,
2778 l_prev_date,
2782 l_curr_as_of_date,
2779 l_prior_prior_time_id,
2780 l_prior_prior_date,
2781 l_curr_page_time_id,
2783 l_prev_page_time_id,
2784 l_prev_date,
2785 l_prior_prior_time_id,
2786 l_prior_prior_date,
2787 l_curr_page_time_id,
2788 l_curr_as_of_date,
2789 l_prev_page_time_id,
2790 l_prev_date,
2791 l_bitand_id,
2792 l_bitand_id,
2793 l_period_type,
2794 l_fst_crdt_type,
2795 l_fst_crdt_type,
2796 l_curr_as_of_date,
2797 l_prev_date,
2798 l_prior_prior_date,
2799 l_curr_page_time_id,
2800 l_prev_page_time_id,
2801 l_prior_prior_time_id,
2802 l_prodcat_id,
2803 l_sg_id_num,
2804 l_parent_sales_group_id,
2805 l_yes;
2806 ELSE
2807 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2808 USING l_cat_assign,l_cat_assign,
2809 l_curr_page_time_id,
2810 l_curr_as_of_date,
2811 l_prev_page_time_id,
2812 l_prev_date,
2813 l_prior_prior_time_id,
2814 l_prior_prior_date,
2815 l_curr_page_time_id,
2816 l_curr_as_of_date,
2817 l_prev_page_time_id,
2818 l_prev_date,
2819 l_prior_prior_time_id,
2820 l_prior_prior_date,
2821 l_curr_page_time_id,
2822 l_curr_as_of_date,
2823 l_prev_page_time_id,
2824 l_prev_date,
2825 l_bitand_id,
2826 l_bitand_id,
2827 l_period_type,
2828 l_fst_crdt_type,
2829 l_fst_crdt_type,
2830 l_curr_as_of_date,
2831 l_prev_date,
2832 l_prior_prior_date,
2833 l_curr_page_time_id,
2834 l_prev_page_time_id,
2835 l_prior_prior_time_id,
2836 l_prodcat_id,
2837 l_sg_id_num,
2838 l_sg_id_num,
2839 l_yes,
2840 l_resource_id;
2841 END IF;
2842 END IF;
2843
2844 -- for pipeline measures
2845
2846 l_custom_sql :=
2847 ' SELECT VIEWBY '||
2848 ', VIEWBYID '||
2849 ', SORTORDER '||
2850 ',NULL BIL_MEASURE1 '||
2851 ',NULL BIL_MEASURE2 '||
2852 ',NULL BIL_MEASURE3 '||
2853 ',NULL BIL_MEASURE4 '||
2854 ',NULL BIL_MEASURE5 '||
2855 ',NULL BIL_MEASURE6 '||
2856 ',NULL BIL_MEASURE7 '||
2857 ',NULL BIL_MEASURE8 '||
2858 ',SUM(pipeline) BIL_MEASURE9 '||
2859 ',SUM(priorPipeline) BIL_MEASURE10 '||
2860 ',SUM(wtdPipeline) BIL_MEASURE11 '||
2861 ',SUM(priorWtdPipeline) BIL_MEASURE12 '||
2862 ',BIL_URL1 '||
2863 ',DECODE(VIEWBY,'||':l_cat_assign'||',NULL,'''||l_pipe_url||''') BIL_URL2 '||
2864 ' FROM '||
2865 '( '||
2866 ' SELECT DECODE(pcd.parent_id, pcd.id,
2867 decode(sumry.item_id, ''-1'', :l_cat_assign, pcd.value), pcd.value) VIEWBY
2868 ,DECODE(pcd.parent_id, pcd.id,
2869 decode(sumry.item_id, ''-1'', 1, 2), 2) SORTORDER, '||
2870 l_sql_stmnt3||
2871 ',pcd.id VIEWBYID'||
2872 ',decode(pcd.parent_id, pcd.id, NULL, '''||l_cat_url||''') BIL_URL1 '||
2873 ',NULL BIL_URL2 '||
2874 ' FROM '||l_sumry2||' sumry'||
2875 l_pipe_denorm||' '||
2876 ' '||l_where_clause3||' '||
2877 l_pipe_product_where_clause ||
2878 ' AND sumry.sales_group_id = :l_sg_id_num '||
2879 l_parent_sls_grp_where_clause;
2880
2881
2882 IF l_resource_id IS NULL THEN
2883 l_custom_sql :=l_custom_sql ||' AND sumry.salesrep_id IS NULL ';
2884 ELSE
2885 l_custom_sql :=l_custom_sql ||' AND sumry.salesrep_id = :l_resource_id ';
2886 END IF;
2887
2888 l_custom_sql := l_custom_sql||
2889 ' )tmp1'||
2890 ' GROUP BY VIEWBY, VIEWBYID, SORTORDER ,BIL_URL1, BIL_URL2 ';
2891
2892
2893 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2894
2895 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
2896 MODULE => g_pkg || l_proc || 'Prod cat Viewby ',
2897 MESSAGE => ' x_custom_sql length '||LENGTH(l_custom_sql));
2898
2899 END IF;
2900
2901
2902 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2903 l_ind :=1;
2904 l_len:= length(l_custom_sql);
2905
2909 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
2906 WHILE l_ind <= l_len LOOP
2907 l_str:= substr(l_custom_sql, l_ind, 4000);
2908
2910 MODULE => g_pkg || l_proc ||'.'|| ' Oppty Pipe query ',
2911 MESSAGE => l_str);
2912
2913 l_ind := l_ind + 4000;
2914
2915 END LOOP;
2916 END IF;
2917
2918
2919 IF l_parent_sales_group_id IS NULL THEN
2920 IF l_resource_id IS NULL THEN
2921 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
2922 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2923 USING
2924 l_cat_assign, l_cat_assign,
2925 l_snapshot_date, l_period_type,
2926 l_snapshot_date,
2927 l_snapshot_date, l_period_type,
2928 l_snapshot_date,
2929 l_snapshot_date,
2930 l_prodcat_id, l_sg_id_num;
2931 ELSE
2932 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2933 USING
2934 l_cat_assign, l_cat_assign,
2935 l_snapshot_date, l_period_type,
2936 l_prev_snap_date, l_period_type,
2937 l_snapshot_date, l_period_type,
2938 l_prev_snap_date, l_period_type,
2939 l_snapshot_date, l_prev_snap_date,
2940 l_prodcat_id, l_sg_id_num;
2941 END IF;
2942
2943 ELSE
2944 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
2945 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2946 USING l_cat_assign, l_cat_assign,
2947 l_snapshot_date, l_period_type,
2948 l_snapshot_date,
2949 l_snapshot_date, l_period_type,
2950 l_snapshot_date,
2951 l_snapshot_date,
2952 l_prodcat_id, l_sg_id_num, l_resource_id;
2953 ELSE
2954 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2955 USING l_cat_assign, l_cat_assign,
2956 l_snapshot_date, l_period_type,
2957 l_prev_snap_date, l_period_type,
2958 l_snapshot_date, l_period_type,
2959 l_prev_snap_date, l_period_type,
2960 l_snapshot_date, l_prev_snap_date,
2961 l_prodcat_id, l_sg_id_num, l_resource_id;
2962 END IF;
2963
2964 END IF;
2965 ELSE -- parent sales group id not null
2966 IF l_resource_id IS NULL THEN
2967 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
2968 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2969 USING l_cat_assign,l_cat_assign,
2970 l_snapshot_date, l_period_type,
2971 l_snapshot_date,
2972 l_snapshot_date, l_period_type,
2973 l_snapshot_date,
2974 l_snapshot_date,
2975 l_prodcat_id, l_sg_id_num, l_parent_sales_group_id;
2976 ELSE
2977 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2978 USING l_cat_assign,l_cat_assign,
2979 l_snapshot_date, l_period_type,
2980 l_prev_snap_date, l_period_type,
2981 l_snapshot_date, l_period_type,
2982 l_prev_snap_date, l_period_type,
2983 l_snapshot_date, l_prev_snap_date,
2984 l_prodcat_id, l_sg_id_num, l_parent_sales_group_id;
2985 END IF;
2986
2987 ELSE
2988 IF (l_open_mv_new <> 'BIL_BI_PIPE_G_MV') THEN
2989 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2990 USING l_cat_assign, l_cat_assign,
2991 l_snapshot_date, l_period_type,
2992 l_snapshot_date,
2993 l_snapshot_date, l_period_type,
2994 l_snapshot_date,
2995 l_snapshot_date,
2996 l_prodcat_id, l_sg_id_num, l_sg_id_num, l_resource_id;
2997 ELSE
2998 EXECUTE IMMEDIATE l_insert_stmnt||l_custom_sql
2999 USING l_cat_assign, l_cat_assign,
3000 l_snapshot_date, l_period_type,
3001 l_prev_snap_date, l_period_type,
3002 l_snapshot_date, l_period_type,
3003 l_prev_snap_date, l_period_type,
3004 l_snapshot_date, l_prev_snap_date,
3005 l_prodcat_id, l_sg_id_num, l_sg_id_num, l_resource_id;
3006 END IF;
3007 END IF;
3008 END IF;
3009 END IF;
3010
3011 /*
3012 BIL_MEASURE28 frcst
3013 BIL_MEASURE2 priorfrcst
3014 BIL_MEASURE3 priorpriorfrcst
3015 BIL_MEASURE4 oppfcst
3016 BIL_MEASURE5 prior_oppfrcst
3017 BIL_MEASURE6 priorprior_oppfrcst
3018 BIL_MEASURE7 frcst_sub
3019 BIL_MEASURE8 priorfrcst_sub
3020 BIL_MEASURE9 pipeline
3021 BIL_MEASURE10 priorpipeline
3025 BIL_MEASURE14 Prior Total Judgement SUM(BIL_MEASURE2) - SUM(BIL_MEASURE5)
3022 BIL_MEASURE11 wtdpipeline
3023 BIL_MEASURE12 priorwtdpipeline
3024 BIL_MEASURE13 Total Judgement SUM(BIL_MEASURE28) - SUM(BIL_MEASURE4)
3026 BIL_MEASURE15 Prior Prior Total Judgement SUM(BIL_MEASURE3) - SUM(BIL_MEASURE6)
3027 */
3028
3029 IF l_resource_id IS NULL THEN
3030
3031 x_custom_sql := 'SELECT '||
3032 'VIEWBY, '||
3033 'VIEWBYID, '||
3034 'BIL_MEASURE1 , '||
3035 'BIL_MEASURE23, '||
3036 'BIL_MEASURE24, '||
3037 'BIL_MEASURE2 , '||
3038 'BIL_MEASURE25, '||
3039 'BIL_MEASURE26, '||
3040 'BIL_MEASURE27, '||
3041 'BIL_MEASURE3 , '||
3042 'BIL_MEASURE4 , '||
3043 'BIL_MEASURE5 , '||
3044 'BIL_MEASURE6 , '||
3045 'BIL_MEASURE7 , '||
3046 'BIL_MEASURE28, '||
3047 'BIL_MEASURE8 , '||
3048 'BIL_MEASURE9 , '||
3049 'BIL_MEASURE29, '||
3050 'BIL_MEASURE10, '||
3051 'BIL_MEASURE11, '||
3052 'BIL_MEASURE30, '||
3053 'BIL_MEASURE12, '||
3054 'BIL_MEASURE13, '||
3055 'BIL_MEASURE14, '||
3056 'BIL_MEASURE15, '||
3057 'BIL_MEASURE16, '||
3058 'BIL_MEASURE17, '||
3059 'BIL_MEASURE18, '||
3060 'BIL_MEASURE19, '||
3061 'BIL_MEASURE20, '||
3062 'BIL_MEASURE21, '||
3063 'BIL_MEASURE22, '||
3064 'BIL_MEASURE32, '||
3065 'BIL_URL1, '||
3066 ''''||l_url_str ||''' BIL_URL3,'||
3067 'BIL_URL2 '||
3068 ' FROM ( '||
3069 l_outer_select ||
3070 ' FROM ( '||
3071 'SELECT VIEWBY, VIEWBYID, SORTORDER, '||
3072 ' SUM(BIL_MEASURE28) BIL_MEASURE28,'||
3073 ' SUM(BIL_MEASURE2) BIL_MEASURE2, '||
3074 ' SUM(BIL_MEASURE3) BIL_MEASURE3, '||
3075 ' SUM(BIL_MEASURE4) BIL_MEASURE4, '||
3076 ' SUM(BIL_MEASURE5) BIL_MEASURE5, '||
3077 ' SUM(BIL_MEASURE6) BIL_MEASURE6, '||
3078 ' SUM(BIL_MEASURE7) BIL_MEASURE7, '||
3079 ' SUM(BIL_MEASURE8) BIL_MEASURE8, '||
3080 ' SUM(BIL_MEASURE9) BIL_MEASURE9, '||
3081 ' SUM(BIL_MEASURE10) BIL_MEASURE10, '||
3082 ' SUM(BIL_MEASURE11) BIL_MEASURE11, '||
3083 ' SUM(BIL_MEASURE12) BIL_MEASURE12, '||
3084 ' SUM(BIL_MEASURE28) - SUM(BIL_MEASURE4) BIL_MEASURE13, '||
3085 ' SUM(BIL_MEASURE2) - SUM(BIL_MEASURE5) BIL_MEASURE14, '||
3086 ' SUM(BIL_MEASURE3) - SUM(BIL_MEASURE6) BIL_MEASURE15, '||
3087 ' BIL_URL1, '||
3088 ' BIL_URL2 ' ||
3089 ' FROM BIL_BI_RPT_TMP1 '||
3090 ' GROUP BY VIEWBY, VIEWBYID, SORTORDER, '||
3091 ' BIL_URL1, BIL_URL2 ' ||
3092 ') '||
3093 ' ORDER BY SORTORDER, UPPER(VIEWBY) '||')' ||
3094 ' WHERE '||l_null_removal_clause;
3095
3096 ELSE
3097
3098 x_custom_sql := 'SELECT '||
3099 'VIEWBY, '||
3100 'VIEWBYID, '||
3101 'NULL BIL_MEASURE1 , '||
3102 'BIL_MEASURE23, '||
3103 'BIL_MEASURE24, '||
3104 'NULL BIL_MEASURE2 , '||
3105 'BIL_MEASURE25, '||
3106 'BIL_MEASURE26, '||
3107 'BIL_MEASURE27, '||
3108 'BIL_MEASURE3 , '||
3109 'BIL_MEASURE4 , '||
3110 'BIL_MEASURE5 , '||
3111 'BIL_MEASURE6 , '||
3112 'BIL_MEASURE1 BIL_MEASURE7, '||
3113 'BIL_MEASURE28, '||
3114 'BIL_MEASURE2 BIL_MEASURE8, '||
3115 'BIL_MEASURE9 , '||
3116 'BIL_MEASURE29, '||
3117 'BIL_MEASURE10, '||
3118 'BIL_MEASURE11, '||
3119 'BIL_MEASURE30, '||
3120 'BIL_MEASURE12, '||
3121 'NULL BIL_MEASURE13, '||
3122 'NULL BIL_MEASURE14, '||
3123 'BIL_MEASURE15, '||
3124 'BIL_MEASURE16, '||
3125 'BIL_MEASURE13 BIL_MEASURE17, '||
3126 'BIL_MEASURE14 BIL_MEASURE18, '||
3127 'BIL_MEASURE19, '||
3128 'BIL_MEASURE20, '||
3129 'BIL_MEASURE21, '||
3130 'BIL_MEASURE22, '||
3131 'BIL_MEASURE32, '||
3132 'BIL_URL1, '||
3133 ''''||l_url_str ||''' BIL_URL3,'||
3134 'BIL_URL2 '||
3135 ' FROM ( '||
3136 l_outer_select ||
3137 ' FROM ( '||
3138 'SELECT VIEWBY, VIEWBYID, SORTORDER, '||
3139 ' SUM(BIL_MEASURE28) BIL_MEASURE28,'||
3140 ' SUM(BIL_MEASURE2) BIL_MEASURE2, '||
3141 ' SUM(BIL_MEASURE3) BIL_MEASURE3, '||
3142 ' SUM(BIL_MEASURE4) BIL_MEASURE4, '||
3143 ' SUM(BIL_MEASURE5) BIL_MEASURE5, '||
3144 ' SUM(BIL_MEASURE6) BIL_MEASURE6, '||
3145 ' SUM(BIL_MEASURE7) BIL_MEASURE7, '||
3146 ' SUM(BIL_MEASURE8) BIL_MEASURE8, '||
3147 ' SUM(BIL_MEASURE9) BIL_MEASURE9, '||
3148 ' SUM(BIL_MEASURE10) BIL_MEASURE10, '||
3149 ' SUM(BIL_MEASURE11) BIL_MEASURE11, '||
3150 ' SUM(BIL_MEASURE12) BIL_MEASURE12, '||
3151 ' SUM(BIL_MEASURE28) - SUM(BIL_MEASURE4) BIL_MEASURE13, '||
3152 ' SUM(BIL_MEASURE2) - SUM(BIL_MEASURE5) BIL_MEASURE14, '||
3153 ' SUM(BIL_MEASURE3) - SUM(BIL_MEASURE6) BIL_MEASURE15, '||
3154 ' BIL_URL1, '||
3155 ' BIL_URL2 '||
3156 ' FROM BIL_BI_RPT_TMP1 '||
3157 ' GROUP BY VIEWBY, VIEWBYID, SORTORDER, '||
3158 ' BIL_URL1, BIL_URL2 ' ||
3159 ') '||
3160 ' ORDER BY SORTORDER, UPPER(VIEWBY) '||')' ||
3161 ' WHERE '||l_null_removal_clause;
3162
3163 END IF;
3164
3165
3166 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3167 l_ind :=1;
3168 l_len:= length(l_custom_sql);
3169
3170 WHILE l_ind <= l_len LOOP
3171 l_str:= substr(l_custom_sql, l_ind, 4000);
3172
3173 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
3174 MODULE => g_pkg || l_proc ||'.'|| ' Final Query ',
3175 MESSAGE => l_str);
3176
3177 l_ind := l_ind + 4000;
3178
3179 END LOOP;
3180 END IF;
3181
3182
3183 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3184
3185 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
3186 MODULE => g_pkg || l_proc || 'Query Length=>',
3187 MESSAGE => length(x_custom_sql));
3188
3189 END IF;
3190
3191 ELSE --no valid parameters
3192
3193 BIL_BI_UTIL_PKG.get_default_query(p_regionname => l_region_id
3194 ,x_sqlstr => x_custom_sql);
3195 END IF;
3196
3197 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3198
3199 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
3200 MODULE => g_pkg || l_proc || 'End',
3201 MESSAGE => 'End of Procedure '||l_proc);
3202
3203 END IF;
3204
3205 EXCEPTION
3206 WHEN OTHERS THEN
3207
3208 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3209 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
3210 fnd_message.set_token('Error is : ' ,SQLCODE);
3211 fnd_message.set_token('Reason is : ', SQLERRM);
3212
3213 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
3214 MODULE => g_pkg || l_proc || 'proc_error',
3215 MESSAGE => fnd_message.get );
3216 END IF;
3217 RAISE;
3218 END BIL_BI_FRCST_PRODCAT;
3219
3220 END BIL_BI_FCST_MGMT_RPTS_PKG;
3221