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