[Home] [Help]
PACKAGE BODY: APPS.BIL_BI_UTIL_PKG
Source
1 PACKAGE BODY BIL_BI_UTIL_PKG AS
2 /* $Header: bilbutb.pls 120.7 2005/11/21 01:46:56 hrpandey noship $ */
3
4 g_pkg VARCHAR2(1000);
5
6
7 -- **********************************************************************
8 -- FUNCTION chkLogLevel
9 --
10 -- Purpose
11 -- To check if log is Enabled for Messages
12 -- This function is a wrapper on FND APIs for OA Common Error
13 -- logging framework
14 --
15 -- p_log_level = Severity; valid values are -
16 -- 1. Statement Level (FND_LOG.LEVEL_STATEMENT)
17 -- 2. Procedure Level (FND_LOG.LEVEL_PROCEDURE)
18 -- 3. Event Level (FND_LOG.LEVEL_EVENT)
19 -- 4. Exception Level (FND_LOG.LEVEL_EXCEPTION)
20 -- 5. Error Level (FND_LOG.LEVEL_ERROR)
21 -- 6. Unexpected Level (FND_LOG.LEVEL_UNEXPECTED)
22 --
23 -- Output values:-
24 -- = TRUE if FND Log is Enabled
25 -- = FALSE if FND Log is DISABLED
26 --
27 -- **********************************************************************
28
29 FUNCTION chkLogLevel (p_log_level IN NUMBER) RETURN BOOLEAN AS
30
31
32 BEGIN
33 /* Variable Intialization */
34 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
35
36 IF (p_log_level >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
37
38 RETURN TRUE;
39
40 END IF;
41
42 RETURN FALSE;
43
44
45 EXCEPTION
46 WHEN OTHERS THEN
47
48 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
49
50 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
51 fnd_message.set_token('Error is : ' ,SQLCODE);
52 fnd_message.set_token('Reason is : ', SQLERRM);
53
54 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
55 MODULE => g_pkg || '.chkLogLevel',
56 MESSAGE => fnd_message.get );
57
58 END IF;
59
60 END chkLogLevel;
61
62
63
64 -- **********************************************************************
65 -- PROCEDURE GET_CONV_RATE
66 --
67 -- Purpose:
68 -- used by get_page_params procedure, and by the
69 -- Top Open Opportunities report to get the currency conversion rate
70 --
71 -- **********************************************************************
72 PROCEDURE GET_CONV_RATE( p_as_of_date IN DATE
73 ,p_currency IN VARCHAR2
74 ,x_conv_rate_selected OUT NOCOPY VARCHAR2
75 ,x_err_desc OUT NOCOPY VARCHAR2
76 ,x_err_msg OUT NOCOPY VARCHAR2
77 ,x_parameter_valid OUT NOCOPY BOOLEAN
78 ) AS
79
80 l_primary_currency VARCHAR2(50);
81 l_parameter_valid BOOLEAN;
82 l_conv_type VARCHAR2(20);
83 l_user_currency VARCHAR2(50);
84 l_as_of_date DATE;
85
86 BEGIN
87 /* Variable Intialization */
88 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
89 l_parameter_valid := TRUE;
90
91 -- if as_of_date is greater than sysdate, then pass sysdate
92 l_as_of_date:= p_as_of_date;
93 IF(l_as_of_date > sysdate) THEN
94 l_as_of_date:= sysdate;
95 END IF;
96
97 -- Retrieve global currency info
98 l_primary_currency := bis_common_parameters.get_currency_code;
99
100 --Update error message and error description in the case of null currency parameter
101
102 IF l_primary_currency IS NULL THEN
103 l_parameter_valid := FALSE;
104 x_err_msg := 'Null parameter(s)';
105 x_err_desc := x_err_desc || ' ,PRIMARY CURRENCY PROFILE';
106 END IF;
107
108 --Retrieve conversion rate
109 IF l_parameter_valid = TRUE THEN
110 IF INSTR(p_currency,'FII_GLOBAL1') > 0 THEN
111 x_conv_rate_selected := '1';
112 ELSIF INSTR(p_currency,'FII_GLOBAL2') > 0 THEN
113 x_conv_rate_selected := '0';
114 END IF;
115 END IF;
116
117 IF to_number(x_conv_rate_selected) < 0 THEN
118 x_conv_rate_selected := 'NULL';
119 END IF;
120
121
122 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
123 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
124 MODULE => g_pkg || '.get_conv_rate ',
125 MESSAGE => l_primary_currency ||', '||l_conv_type ||', '|| x_conv_rate_selected);
126 END IF;
127
128
129 x_parameter_valid := l_parameter_valid;
130
131 EXCEPTION
132 WHEN OTHERS THEN
133
134 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
135
136 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
137 fnd_message.set_token('Error is : ' ,SQLCODE);
138 fnd_message.set_token('Reason is : ', SQLERRM);
139
140 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
141 MODULE => g_pkg || '.get_conv_rate',
142 MESSAGE => fnd_message.get );
143
144 END IF;
145
146
147 END GET_CONV_RATE;
148
149
150 -- **********************************************************************
151 -- PROCEDURE GET_CURR_DATE
152 --
153 -- Purpose:
154 -- to get the current date from the bis_system_date
155 --
156 -- **********************************************************************
157 PROCEDURE GET_CURR_DATE(x_curr_date OUT NOCOPY DATE)
158
159 AS
160
161 BEGIN
162 /* Variable Intialization */
163 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
164
165 SELECT current_date_id INTO x_curr_date FROM bis_system_date;
166
167 EXCEPTION
168 WHEN OTHERS THEN
169
170 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
171
172 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
173 fnd_message.set_token('Error is : ' ,SQLCODE);
174 fnd_message.set_token('Reason is : ', SQLERRM);
175
176
177 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
178 MODULE => g_pkg || '.get_curr_date',
179 MESSAGE => fnd_message.get );
180
181 END IF;
182 END;
183
184
185
186 -- **********************************************************************
187 -- PROCEDURE GET_CURR_START_DATE
188 --
189 -- Purpose:
190 -- used by the reports to obtain the start date of the current period
191 -- **********************************************************************
192 PROCEDURE GET_CURR_START_DATE (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
193 p_as_of_date IN DATE,
194 p_period_type IN VARCHAR2,
195 x_curr_start_date OUT NOCOPY DATE)
196 AS
197
198 BEGIN
199 /* Variable Intialization */
200 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
201
202
203 IF (p_page_parameter_tbl.count > 0) THEN
204 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
205 IF (p_page_parameter_tbl(i).parameter_name = 'BIS_CURRENT_EFFECTIVE_START_DATE') THEN
206 x_curr_start_date := p_page_parameter_tbl(i).period_date;
207 END IF;
208 END LOOP;
209 END IF;
210
211 END;
212
213
214 -- **********************************************************************
215 -- FUNCTION GET_DBI_PARAMS
216 --
217 -- Purpose:
218 -- used by the portlet functions to get the default parameter values for
219 -- all parameters
220 -- **********************************************************************
221 FUNCTION GET_DBI_PARAMS(p_region_id IN VARCHAR2) RETURN VARCHAR2 AS
222
223 l_sg_id VARCHAR2(100);--:=-1111;
224
225
226 BEGIN
227
228 /* Variable Intialization */
229 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
230
231 BEGIN
232 l_sg_id:= JTF_RS_DBI_CONC_PUB.GET_SG_ID();
233
234 EXCEPTION
235
236 WHEN OTHERS THEN
237
238
239 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
240
241 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
242 fnd_message.set_token('Error is : ' ,SQLCODE);
243 fnd_message.set_token('Reason is : ', SQLERRM);
244
245 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
246 MODULE => g_pkg || '.get_dbi_params',
247 MESSAGE => fnd_message.get );
248
249 END IF;
250
251 END;
252
253
254 RETURN '&BIL_DIMENSION4_FROM=All'||
255 '&BIL_DIMENSION6='||'TIME_COMPARISON_TYPE+YEARLY'||
256 '&BIL_DIMENSION8='|| l_sg_id ||
257 '&JTF_ORG_SALES_GROUP='|| l_sg_id ||
258 '&BIL_DIMENSION9=FII_GLOBAL1'||
259 '&BIS_ENI_ITEM_VBH_CAT=All'||
260 '&BIL_DIMENSION1=2';
261
262 END GET_DBI_PARAMS;
263
264
265 -- **********************************************************************
266 -- FUNCTION GET_DBI_SALES_GROUP_ID
267 --
268 -- Purpose:
269 -- used by the report functions to get the default sales group id
270 -- here we cannot return the string of all parameters due to PMV limitations
271 -- **********************************************************************
272 FUNCTION get_dbi_sales_group_id RETURN VARCHAR2 AS
273
274 l_sg_id VARCHAR2(100);
275
276 BEGIN
277 /* Variable Intialization */
278 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
279
280 BEGIN
281 l_sg_id:= JTF_RS_DBI_CONC_PUB.GET_SG_ID();
282
283 EXCEPTION
284
285 WHEN OTHERS THEN
286
287 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
288
289 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
290 fnd_message.set_token('Error is : ' ,SQLCODE);
291 fnd_message.set_token('Reason is : ', SQLERRM);
292
293 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
294 MODULE => g_pkg || '.get_dbi_sales_group_id',
295 MESSAGE => fnd_message.get );
296 END IF;
297 END;
298
299 RETURN l_sg_id;
300
301 END get_dbi_sales_group_id;
302
303
304 -- **********************************************************************
305 -- PROCEDURE GET_DEFAULT_QUERY
306 --
307 -- Purpose:
308 -- Returns default blank query to be used by individual report procedure
309 -- if PMV parameters are not passed correctly.
310 -- --------------------------------------------------------------------
311 PROCEDURE GET_DEFAULT_QUERY(
312 p_RegionName IN VARCHAR2,
313 x_SqlStr OUT NOCOPY VARCHAR2
314 ) AS
315
316 CURSOR cAkRegionItem (pRegionName IN VARCHAR2,
317 pBilMeasureTxt IN VARCHAR2,
318 pGrandTotalTxt IN VARCHAR2,
319 pDrillTxt IN VARCHAR2,
320 pNATxt IN VARCHAR2,
321 pNVTxt IN VARCHAR2,
322 pWCTxt IN VARCHAR2)
323
324 IS
325
326
327 SELECT attribute_code FROM AK_REGION_ITEMS
328 WHERE REGION_CODE = pRegionName
329 AND (ATTRIBUTE3 LIKE pBilMeasureTxt OR ATTRIBUTE1 IN
330 (pGrandTotalTxt,pDrillTxt,pNaTxt))
331 AND NVL(ATTRIBUTE3, pNVTxt) NOT LIKE pWCTxt
332 ORDER BY DISPLAY_SEQUENCE;
333
334
335 temp_sql VARCHAR2(5000);
336
337
338 BEGIN
339 /* Variable Intialization */
340 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
341
342 temp_sql:= 'SELECT null viewby';
343
344
345 -- Open a FOR Loop to access every individual region items
346
347
348 FOR ITEM_REC IN cAkRegionItem(p_RegionName,'BIL_MEASURE%', 'GRAND_TOTAL',
349 'DRILL ACROSS URL','NOT_AVAILABLE_TEXT','NV','"%"')
350
351 LOOP
352 BEGIN
353 temp_sql:= temp_sql ||',null '||ITEM_REC.attribute_code;
354 END;
355
356
357 END LOOP;
358
359
360 temp_sql:= temp_sql ||' FROM DUAL WHERE 1=2 and rownum<0';
361 x_SqlStr:= temp_sql;
362
363
364 EXCEPTION
365 WHEN OTHERS THEN
366
367 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
368
369 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
370 fnd_message.set_token('Error is : ' ,SQLCODE);
371 fnd_message.set_token('Reason is : ', SQLERRM);
372
373 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
374 MODULE => g_pkg || '.get_default_query',
375 MESSAGE => fnd_message.get );
376
377 END IF;
378 END;
379
380
381 -- **********************************************************************
382 -- PROCEDURE GET_FORECAST_PROFILES
383 --
384 -- Purpose:
385 -- used to retrieve forecast category and forecast credit type profiles
386 --
387 -- **********************************************************************
388 PROCEDURE GET_FORECAST_PROFILES(
389 x_FstCrdtType OUT NOCOPY VARCHAR2
390 ) AS
391 BEGIN
392 /* Variable Intialization */
393 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
394
395 -- IF(FND_PROFILE.Value('BIL_BI_ASN_IMPLEMENTED') = 'Y') THEN
396
397 /*
398 IF(BIL_BI_UTIL_PKG.GET_ASN_PROFILE = 'Y') THEN
399 x_FstCrdtType := FND_PROFILE.Value('ASN_FRCST_CREDIT_TYPE_ID');
400 ELSE
401 x_FstCrdtType := FND_PROFILE.Value('AS_FORECAST_CREDIT_TYPE_ID');
402 END IF;
403 */
404
405 x_FstCrdtType := FND_PROFILE.Value('ASN_FRCST_CREDIT_TYPE_ID');
406
407
408
409 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
410
414
411 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
412 MODULE => g_pkg || '.get_forecast_profiles ',
413 MESSAGE => x_FstCrdtType);
415 END IF;
416
417 EXCEPTION
418 WHEN OTHERS THEN
419
420 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
421
422 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
423 fnd_message.set_token('Error is : ' ,SQLCODE);
424 fnd_message.set_token('Reason is : ', SQLERRM);
425
426 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
427 MODULE => g_pkg || '.get_forecast_profiles',
428 MESSAGE => fnd_message.get );
429
430 END IF;
431 END;
432
433
434
435 -- **********************************************************************
436 -- PROCEDURE GET_GLOBAL_CONTS
437 --
438 -- Purpose:
439 -- used to retrieve the bitand_id, calendar_id, current_date, and fii_struct
440 -- table name
441 --
442 -- **********************************************************************
443 PROCEDURE GET_GLOBAL_CONTS(
444 x_bitand_id OUT NOCOPY VARCHAR2,
445 x_calendar_id OUT NOCOPY VARCHAR2,
446 x_curr_date OUT NOCOPY DATE,
447 x_fii_struct OUT NOCOPY VARCHAR2
448
449
450 )AS
451 l_curr_date DATE;
452 BEGIN
453 /* Variable Intialization */
454 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
455
456 x_bitand_id := 512;
457 x_calendar_id := -1;
458 x_fii_struct := 'FII_TIME_STRUCTURES';
459
460
461
462 GET_CURR_DATE(x_curr_date => l_curr_date);
463
464 x_curr_date:=l_curr_date;
465
466
467 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
468
469 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
470 MODULE => g_pkg || '.get_global_conts ',
471 MESSAGE => x_bitand_id ||', '||x_calendar_id ||', '|| x_fii_struct);
472
473 END IF;
474
475
476 EXCEPTION
477 WHEN OTHERS THEN
478
479 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
480 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
481 fnd_message.set_token('Error is : ' ,SQLCODE);
482 fnd_message.set_token('Reason is : ', SQLERRM);
483
484 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
485 MODULE => g_pkg || '.get_global_conts',
486 MESSAGE => fnd_message.get );
487
488 END IF;
489 END GET_GLOBAL_CONTS;
490
491
492
493
494 -- **********************************************************************
495 -- PROCEDURE GET_LATEST_SNAP_DATE
496 --
497 -- Purpose:
498 -- used by the reports that display pipeline and/or open opportunity
499 -- to retrieve the date of the last snapshot. This date will be used
500 -- instead of the as_of_date in the front end query.
501 -- **********************************************************************
502 PROCEDURE GET_LATEST_SNAP_DATE(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
503 ,p_as_of_date IN DATE
504 ,p_period_type IN VARCHAR2
505 ,x_snapshot_date OUT NOCOPY DATE )
506 AS
507
508 l_as_of_date DATE;
509 l_start_date DATE;
510 l_yesterday DATE;
511 l_check_date DATE;
512 l_period_type VARCHAR2(50);
513 l_proc VARCHAR2(50);
514
515 BEGIN
516 /* Variable Intialization */
517 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
518 l_proc := 'GET_LATEST_SNAP_DATE';
519 l_as_of_date := trunc(p_as_of_date);
520 l_yesterday := trunc(sysdate) - 1;
521 l_period_type := p_period_type;
522
523 --if p_as_of_date is not today, then don't do anything
524 IF p_as_of_date <> trunc(sysdate) THEN
525 x_snapshot_date := p_as_of_date;
526 ELSE
527 --get current start_date
528 BIL_BI_UTIL_PKG.GET_CURR_START_DATE(p_page_parameter_tbl =>p_page_parameter_tbl,
529 p_as_of_date => l_as_of_date,
530 p_period_type => l_period_type,
531 x_curr_start_date => l_start_date);
532
533 l_start_date := trunc(l_start_date);
534 --if start date is before yesterday, set check_date to yesterday,
535 --if not, set check_date to start_date
539 l_check_date := l_start_date;
536 IF l_yesterday > l_start_date THEN
537 l_check_date := l_yesterday;
538 ELSE
540 END IF;
541
542 BEGIN
543 --if yesterday falls in the same period, execute the query
544 IF (l_start_date <= l_yesterday) THEN
545 select trunc(max(period_to)) into x_snapshot_date
546 from bis_refresh_log
547 where object_name = 'BIL_BI_PIPELINE_F'
548 and status = 'SUCCESS'
549 and period_to <= l_as_of_date
550 and period_to >= l_check_date;
551 ELSE -- yesterday falls in a different period, return as of date
552 x_snapshot_date := p_as_of_date;
553 END IF;
554
555
556
557 --esapozh added on July 7, 2004 to force all reports to execute pipe queries
558 if (x_snapshot_date is null) then
559 x_snapshot_date := p_as_of_date;
560 end if;
561
562 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
563
564 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
565 MODULE => g_pkg ||'.'|| l_proc || '.x_snapshot_date',
566 MESSAGE => x_snapshot_date);
567
568 END IF;
569
570 EXCEPTION
571 WHEN OTHERS THEN
572
573 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
574 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
575 fnd_message.set_token('Error is : ' ,SQLCODE);
576 fnd_message.set_token('Reason is : ', SQLERRM);
577
578 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
579 MODULE => g_pkg || '.get_latest_snapshot',
580 MESSAGE => fnd_message.get );
581
582 END IF;
583 END;
584
585 END IF;
586
587
588
589 END;
590
591
592 -- **********************************************************************
593 -- PROCEDURE GET_OTHER_PROFILES
594 --
595 -- Purpose:
596 -- used to retrieve the debug mode profile
597 --
598 -- **********************************************************************
599 PROCEDURE GET_OTHER_PROFILES(
600 x_DebugMode OUT NOCOPY VARCHAR2
601 ) AS
602
603 BEGIN
604 /* Variable Intialization */
605 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
606 x_DebugMode := FND_PROFILE.Value('BIS_PMF_DEBUG');
607
608 EXCEPTION
609 WHEN OTHERS THEN
610
611 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
612 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
613 fnd_message.set_token('Error is : ' ,SQLCODE);
614 fnd_message.set_token('Reason is : ', SQLERRM);
615
616 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
617 MODULE => g_pkg || '.get_other_profiles',
618 MESSAGE => fnd_message.get );
619 END IF;
620 END;
621
622
623 PROCEDURE GET_PAGE_PARAMS (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
624 p_region_id IN VARCHAR2,
625 x_as_of_date OUT NOCOPY DATE,
626 x_comp_type OUT NOCOPY VARCHAR2,
627 x_conv_rate_selected OUT NOCOPY VARCHAR2,
628 x_curr_page_time_id OUT NOCOPY NUMBER,
629 x_page_period_type OUT NOCOPY VARCHAR2,
630 x_parameter_valid OUT NOCOPY BOOLEAN,
631 x_period_type OUT NOCOPY VARCHAR2,
632 x_prev_page_time_id OUT NOCOPY NUMBER,
633 x_prior_as_of_date OUT NOCOPY DATE,
634 x_prodcat_id OUT NOCOPY VARCHAR2,
635 x_record_type_id OUT NOCOPY NUMBER,
636 x_resource_id OUT NOCOPY VARCHAR2,
637 x_sg_id OUT NOCOPY VARCHAR2,
638 x_parent_sg_id OUT NOCOPY NUMBER,
639 x_viewby OUT NOCOPY VARCHAR2)
640 AS
641
642
646 l_comp_type VARCHAR2(20);
643 l_currency VARCHAR2(20);
644 l_salesgroup_id VARCHAR2(100);
645 l_period_id VARCHAR2(20);
647 l_primary_currency VARCHAR2(30);
648 l_previous_date DATE;
649 l_current_date DATE;
650 l_as_of_date DATE;
651 l_parameter_valid BOOLEAN;
652 l_err_msg VARCHAR2(320);
653 l_err_desc VARCHAR2(4000);
654 l_err_msg1 VARCHAR2(320);
655 l_err_desc1 VARCHAR2(4000);
656 l_proc VARCHAR2(20);
657 l_log_str VARCHAR2(3000);
658 l_resource_id VARCHAR2(100);
659 l_conv_rate_selected VARCHAR2(100);
660 l_parent_sls_grp_id VARCHAR2(100);
661
662 BEGIN
663 /* Variable Intialization */
664 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
665 l_parameter_valid := True;
666 l_err_msg := 'Null parameter(s)';
667 l_err_desc := 'Please run with a valid ';
668 l_proc := ' GET.PAGE.PARAMS ';
669
670
671 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
672
673 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
674 MODULE => g_pkg ||'.'|| l_proc || '.begin',
675 MESSAGE => 'Start of Procedure '||l_proc);
676
677 END IF;
678
679
680 x_parameter_valid := l_parameter_valid;
681
682 --retrieve page parameters
683
684 IF p_page_parameter_tbl IS NOT NULL THEN
685
686 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
687
688 CASE p_page_parameter_tbl(i).parameter_name
689
690 WHEN 'VIEW_BY' THEN
691
692 x_viewby := p_page_parameter_tbl(i).parameter_value;
693
694 IF x_viewby IS NULL THEN
695
696 x_viewby := 'ORGANIZATION+JTF_ORG_SALES_GROUP';
697
698 END IF;
699 WHEN 'PERIOD_TYPE' THEN
700
701 x_page_period_type := p_page_parameter_tbl(i).parameter_value;
702
703 IF x_page_period_type IS NULL THEN
704 l_parameter_valid := FALSE;
705 x_parameter_valid := l_parameter_valid;
706 l_err_desc := l_err_desc || p_page_parameter_tbl(i).parameter_name;
707
708 END IF;
709
710 WHEN 'AS_OF_DATE' THEN
711
712 l_as_of_date := p_page_parameter_tbl(i).PERIOD_DATE;
713 x_as_of_date := p_page_parameter_tbl(i).PERIOD_DATE;
714
715 IF l_as_of_date IS NULL THEN
716
717 l_parameter_valid := FALSE;
718 x_parameter_valid := l_parameter_valid;
719 l_err_desc := l_err_desc || p_page_parameter_tbl(i).parameter_name;
720
721 END IF;
722
723 WHEN 'BIS_P_ASOF_DATE' THEN
724
725 x_prior_as_of_date:= p_page_parameter_tbl(i).PERIOD_DATE;
726
727 IF x_prior_as_of_date IS NULL THEN
728 l_parameter_valid := FALSE;
729 x_parameter_valid := l_parameter_valid;
730 l_err_desc := l_err_desc || p_page_parameter_tbl(i).parameter_name;
731
732 END IF;
733
734 WHEN 'TIME_COMPARISON_TYPE' THEN
735 x_comp_type := p_page_parameter_tbl(i).parameter_value;
736 IF x_comp_type IS NULL THEN
737 l_parameter_valid := FALSE;
738 x_parameter_valid := l_parameter_valid;
739 l_err_desc := l_err_desc || p_page_parameter_tbl(i).parameter_name;
740 END IF;
741
742 WHEN 'CURRENCY+FII_CURRENCIES' THEN
743 l_currency := p_page_parameter_tbl(i).parameter_id;
744
745 IF l_currency IS NULL THEN
746 l_parameter_valid := FALSE;
747 x_parameter_valid := l_parameter_valid;
748 l_err_desc := l_err_desc || p_page_parameter_tbl(i).parameter_name;
749
750 END IF;
751
752 WHEN 'ORGANIZATION+JTF_ORG_SALES_GROUP' THEN
753
754 l_salesgroup_id := p_page_parameter_tbl(i).parameter_id;
755
756 BIL_BI_UTIL_PKG.PARSE_SALES_GROUP_ID(
757 p_salesgroup_id =>l_salesgroup_id,
758 x_resource_id =>l_resource_id);
759
760 x_sg_id:= l_salesgroup_id;
761 x_resource_id:=l_resource_id;
762
763 IF x_sg_id IS NULL THEN
764 l_parameter_valid := FALSE;
765 x_parameter_valid := l_parameter_valid;
766 l_err_desc := l_err_desc || p_page_parameter_tbl(i).parameter_name;
767 END IF;
768
769
770 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
771
772 x_prodcat_id := p_page_parameter_tbl(i).parameter_id;
773
777 WHEN 'TIME+FII_TIME_WEEK_PFROM' THEN
774 -- ER #2467584 for gettig previous time_id e.g. TIME+FII_TIME_ENT_PERIOD_PFROM and TIME+FII_TIME_ENT_PERIOD_PTO
775 -- get values for p_prev_page_time_id to implement ER#2467584
776
778 x_prev_page_time_id := p_page_parameter_tbl(i).parameter_id;
779
780 WHEN 'TIME+FII_TIME_ENT_PERIOD_PFROM' THEN
781 x_prev_page_time_id := p_page_parameter_tbl(i).parameter_id;
782
783 WHEN 'TIME+FII_TIME_ENT_QTR_PFROM' THEN
784 x_prev_page_time_id := p_page_parameter_tbl(i).parameter_id;
785
786 WHEN 'TIME+FII_TIME_ENT_YEAR_PFROM' THEN
787 x_prev_page_time_id := p_page_parameter_tbl(i).parameter_id;
788
789 -- get values for x_curr_page_time_id
790 WHEN 'TIME+FII_TIME_WEEK_FROM' THEN
791 x_curr_page_time_id := p_page_parameter_tbl(i).parameter_id; --'+FII_TIME_WEEK_FROM';
792
793 WHEN 'TIME+FII_TIME_ENT_PERIOD_FROM' THEN
794 x_curr_page_time_id := p_page_parameter_tbl(i).parameter_id; --'+FII_TIME_ENT_PERIOD_FROM';
795
796 WHEN 'TIME+FII_TIME_ENT_QTR_FROM' THEN
797 x_curr_page_time_id := p_page_parameter_tbl(i).parameter_id; --'+FII_TIME_ENT_QTR_FROM';
798
799 WHEN 'TIME+FII_TIME_ENT_YEAR_FROM' THEN
800 x_curr_page_time_id := p_page_parameter_tbl(i).parameter_id; --'+FII_TIME_ENT_YEAR_FROM';
801
802 ELSE
803
804 NULL;
805
806 END CASE;
807 END LOOP;
808
809
810 -- Update error message and error description in the caes of null parameters
811
812 IF x_prev_page_time_id IS NULL THEN
813 l_parameter_valid := FALSE;
814 x_parameter_valid := l_parameter_valid;
815 l_err_desc := l_err_desc || ', PREV_PAGE_TIME_ID';
816
817 END IF;
818
819 IF x_curr_page_time_id IS NULL THEN
820 l_parameter_valid := FALSE;
821 x_parameter_valid := l_parameter_valid;
822 l_err_desc := l_err_desc || ', CURR_PAGE_TIME_ID';
823
824 END IF;
825
826 IF x_parameter_valid = TRUE THEN
827 l_log_str := 'View by : '||x_viewby||'p_page_period_type : '||x_page_period_type||
828 ' p_as_of_date : '||x_as_of_date||' p_comp_type : '||x_comp_type||
829 ' l_currency : '||l_currency||' p_sg_id : '||x_sg_id||'p_prodcat_id : '||x_prodcat_id||
830 ' p_prev_page_time_id : '||x_prev_page_time_id||' x_curr_page_time_id : '||x_curr_page_time_id;
831
832
833 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
834
835 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
836 MODULE => g_pkg ||'.'|| l_proc || '.params',
837 MESSAGE => l_log_str);
838
839 END IF;
840
841 ELSE
842
843 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
844
845 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
846 MODULE => g_pkg ||'.'|| l_proc ||'.'||l_err_msg,
847 MESSAGE => l_err_desc);
848
849 END IF;
850
851 END IF;
852
853 -- Retrieve Period_Type AND
854 -- Record_Type For non-BIL query rollup
855 --Year :119
856 --Qtr :55
857 --Month :23
858 --Week :11
859
860 CASE x_page_period_type
861 WHEN 'FII_TIME_WEEK' THEN x_period_type := 16; x_record_type_id := 32;
862 WHEN 'FII_TIME_ENT_PERIOD' THEN x_period_type := 32; x_record_type_id := 64;
863 WHEN 'FII_TIME_ENT_QTR' THEN x_period_type := 64; x_record_type_id := 128;
864 WHEN 'FII_TIME_ENT_YEAR' THEN x_period_type := 128; x_record_type_id := 256;
865
866 ELSE
867
868 l_parameter_valid := FALSE;
869 x_parameter_valid := l_parameter_valid;
870 l_err_msg1 := 'Invalid period type.';
871 l_err_desc1 := 'Invalid period type. ';
872
873
874 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
875
876 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
877 MODULE => g_pkg ||'.'||l_proc||'.'||l_err_msg,
878 MESSAGE => l_err_desc);
879
883
880 END IF;
881
882 END CASE;
884 IF x_parameter_valid = TRUE THEN
885 l_log_str := ' p_period_type : '||x_period_type||' x_record_type_id : '||x_record_type_id;
886
887 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
888
889 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
890 MODULE => g_pkg ||'.'|| l_proc,
891 MESSAGE => l_log_str);
892
893 END IF;
894
895 END IF;
896
897
898
899 --Update error message and error description in the case of null parameters
900 IF x_prior_as_of_date IS NULL THEN
901 l_parameter_valid := FALSE;
902 x_parameter_valid := l_parameter_valid;
903 l_err_desc := l_err_desc || ', PRIOR_AS_OF_DATE';
904 END IF;
905
906
907 bil_bi_util_pkg.GET_CONV_RATE(p_as_of_date => l_as_of_date
908 ,p_currency => l_currency
909 ,x_conv_rate_selected => l_conv_rate_selected
910 ,x_err_desc => l_err_desc
911 ,x_err_msg => l_err_msg
912 ,x_parameter_valid => l_parameter_valid);
913
914 x_conv_rate_selected := l_conv_rate_selected;
915 x_parameter_valid := l_parameter_valid;
916
917 IF x_parameter_valid THEN
918
919 l_log_str := ' x_prior_as_of_date : '||x_prior_as_of_date||
920 ' p_conv_rate_selected : '||x_conv_rate_selected;
921
922
923 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
924
925 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
926 MODULE => g_pkg ||'.'||l_proc,
927 MESSAGE => l_log_str);
928
929 END IF;
930
931 ELSE
932
933 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
934
935 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
936 MODULE => g_pkg ||'.'||l_proc||'.'||l_err_msg,
937 MESSAGE => l_err_desc);
938
939 END IF;
940
941 END IF;
942
943 BIL_BI_UTIL_PKG.GET_PARENT_SLS_GRP_ID(p_sales_grp_id => l_salesgroup_id,
944 x_parent_sls_grp_id => l_parent_sls_grp_id,
945 x_parameter_valid => l_parameter_valid);
946
947
948
949
950 x_parent_sg_id := l_parent_sls_grp_id;
951 x_parameter_valid := l_parameter_valid;
952
953 IF x_parameter_valid THEN
954
955 l_log_str := ' x_parent_sls_grp_id : '||x_parent_sg_id;
956
957 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
958
959 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
960 MODULE => g_pkg ||'.'||l_proc,
961 MESSAGE => l_log_str);
962
963 END IF;
964
965
966 ELSE
967
968 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
969
970 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
971 MODULE => g_pkg ||'.'||l_proc||'.'||l_err_msg,
972 MESSAGE => l_err_desc);
973
974 END IF;
975
976 END IF;
977
978 ELSE
979
980 l_parameter_valid := FALSE;
981 x_parameter_valid := l_parameter_valid;
982 IF p_page_parameter_tbl IS NULL THEN
983
984 l_log_str := ' Param table null! ';
985
986 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
987
988 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
989 MODULE => g_pkg ||'.'||l_proc,
990 MESSAGE => l_log_str);
991
992 END IF;
993
994
995 l_log_str := l_log_str || ' Length of param table '||
996 p_page_parameter_tbl.count;
997
998
999 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1000
1001 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1002 MODULE => g_pkg ||'.'||l_proc,
1006
1003 MESSAGE => l_log_str);
1004
1005 END IF;
1007 END IF;
1008
1009 END IF;
1010
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013
1014 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1015 l_parameter_valid := FALSE;
1016 x_parameter_valid := l_parameter_valid;
1017 l_err_msg := 'New: '||SQLERRM();
1018
1019 l_log_str := ' Exception_block!! ';
1020
1021 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1022 MODULE => g_pkg ||'.'||l_proc||'.'||l_log_str,
1023 MESSAGE => l_err_msg );
1024
1025 END IF;
1026 END GET_PAGE_PARAMS;
1027
1028
1029
1030 PROCEDURE GET_PARENT_SLS_GRP_ID(p_sales_grp_id IN NUMBER,
1031 x_parent_sls_grp_id OUT NOCOPY NUMBER,
1032 x_parameter_valid OUT NOCOPY BOOLEAN)
1033 AS
1034
1035 l_parameter_valid BOOLEAN;
1036 l_parent_sls_grp_id NUMBER;
1037
1038 BEGIN
1039
1040 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1041 l_parameter_valid := TRUE;
1042
1043 select parent_group_id
1044 into l_parent_sls_grp_id
1045 from jtf_rs_groups_denorm
1046 where group_id = p_sales_grp_id
1047 and immediate_parent_flag='Y'
1048 and latest_relationship_flag='Y';
1049
1050 x_parameter_valid := l_parameter_valid;
1051 x_parent_sls_grp_id := l_parent_sls_grp_id;
1052
1053 EXCEPTION
1054 WHEN NO_DATA_FOUND THEN
1055 x_parameter_valid := l_parameter_valid;
1056 x_parent_sls_grp_id := l_parent_sls_grp_id;
1057 WHEN OTHERS THEN
1058
1059 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1060 l_parameter_valid := FALSE;
1061 x_parameter_valid := l_parameter_valid;
1062 fnd_message.set_name('FND','SQL_PLSQL_ERROR');
1063 fnd_message.set_token('ERROR',SQLCODE);
1064 fnd_message.set_token('REASON', SQLERRM);
1065
1066 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1067 MODULE => g_pkg ||'.getParentSalesGroup',
1068 MESSAGE => fnd_message.get );
1069
1070 END IF;
1071 END get_parent_sls_grp_id;
1072
1073
1074 -- **********************************************************************
1075 -- PROCEDURE GET_PRODUCT_WHERE_CLAUSE
1076 --
1077 -- Purpose:
1078 -- Returns the where clause to be used by all reports where
1079 -- product category can be selected. Takes care of all view by's.
1080 -- Note:
1081 -- __Cannot__ be used for forecast measure since MV does not have
1082 -- item reference
1083 --
1084 -- **********************************************************************
1085 PROCEDURE get_Product_Where_Clause(p_prodcat IN VARCHAR2
1086 ,p_viewby IN VARCHAR2
1087 ,x_denorm OUT NOCOPY VARCHAR2
1088 ,x_where_clause OUT NOCOPY VARCHAR2)
1089 AS
1090 l_denorm VARCHAR2(100);
1091 l_product_where_clause VARCHAR2(1000);
1092 l_proc VARCHAR2(50);
1093 BEGIN
1094 /* Variable Intialization */
1095 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1096 l_denorm :=' ';
1097 l_product_where_clause := ' ';
1098 l_proc := 'get_Product_Where_Clause.';
1099
1100 IF 'ITEM+ENI_ITEM_VBH_CAT' = p_viewby THEN
1101
1102 l_denorm := ',ENI_ITEM_PROD_CAT_LOOKUP_V pcd ';
1103
1107 ' AND pcd.child_id = pcd.id '||
1104 IF 'ALL' = UPPER(p_prodcat) THEN
1105 /* l_product_where_clause := ' AND pcd.top_node_flag = :l_yes '||
1106 ' AND pcd.parent_id = pcd.child_id '||
1108 ' AND sumry.product_category_id = pcd.id '||
1109 ' AND sumry.item_id IS NULL ';*/
1110
1111 l_product_where_clause :=' AND pcd.top_node_flag = :l_yes '||
1112 ' AND pcd.parent_id = sumry.product_category_id '||
1113 ' AND pcd.child_id = sumry.product_category_id '||
1114 ' AND sumry.product_category_id = pcd.id '||
1115 ' AND sumry.item_id IS NULL ';
1116
1117 ELSE
1118 l_product_where_clause := ' AND pcd.parent_id = :l_prodcat '||
1119 ' AND pcd.id = pcd.child_id '||
1120 ' AND sumry.product_category_id = pcd.child_id ';
1121 IF NOT isLeafNode(p_prodcat) THEN
1122 l_product_where_clause := l_product_where_clause ||' AND NVL(sumry.item_id,''-2'') = DECODE(pcd.id,pcd.parent_id,''-1'',''-2'') ';
1123 END IF;
1124
1125 END IF;
1126
1127 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1128
1129 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1130 MODULE => g_pkg || l_proc ||'Prod cat view by',
1131 MESSAGE => ' Product where clause: '|| l_product_where_clause ||'; denorm: '|| l_denorm);
1132
1133 END IF;
1134
1135 ELSE
1136
1137 /* View by is one of ORGANIZATION+JTF_ORG_SALES_GROUP, TIME+FII_TIME_WEEK, TIME+FII_TIME_ENT_PERIOD
1138 , TIME+FII_TIME_ENT_QTR, TIME+FII_TIME_ENT_YEAR, CAMPAIGN+CAMPAIGN */
1139 IF 'ALL' <> UPPER(p_prodcat) THEN
1140 l_product_where_clause := ' AND sumry.product_category_id = :l_prodcat '||
1141 ' AND sumry.item_id IS NULL ';
1142 END IF;
1143
1144 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1145
1146 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1147 MODULE => g_pkg || l_proc || p_viewBy,
1148 MESSAGE => ' Product where clause: '|| l_product_where_clause ||'; denorm: '||l_denorm);
1149
1150 END IF;
1151
1152 END IF;
1153
1154 x_denorm := l_denorm;
1155 x_where_clause := l_product_where_clause;
1156 EXCEPTION
1157 WHEN OTHERS THEN
1158
1159 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1160
1161 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1162 fnd_message.set_token('ERROR' ,SQLCODE);
1163 fnd_message.set_token('REASON',SQLERRM);
1164 fnd_message.set_token('ROUTINE',l_proc);
1165
1166 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1167 MODULE => g_pkg || l_proc,
1168 MESSAGE => fnd_message.get );
1169 END IF;
1170 END get_Product_Where_Clause;
1171
1172 /* **********************************************************************
1173 REM PROCEDURE GET_PC_NOROLLUP_WHERE_CLAUSE
1174 REM
1175 REM Purpose:
1176 REM Returns the where clause to be used by all reports where
1177 REM product category rollup is done at runtime. Takes care of all view by's.
1178 REM Note:
1179 REM __Cannot__ be used for forecast measure since MV does not have
1180 REM item reference
1181 REM
1182 REM **********************************************************************/
1183 PROCEDURE GET_PC_NOROLLUP_WHERE_CLAUSE(p_prodcat IN VARCHAR2
1184 ,p_viewby IN VARCHAR2
1185 ,x_denorm OUT NOCOPY VARCHAR2
1186 ,x_where_clause OUT NOCOPY VARCHAR2)
1187 AS
1188 l_denorm VARCHAR2(100);
1189 l_product_where_clause VARCHAR2(1000);
1190 l_proc VARCHAR2(50);
1191 BEGIN
1192 /* Variable Intialization */
1193 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1194 l_denorm :=' ';
1195 l_product_where_clause := ' ';
1196 l_proc := 'get_Pipe_Product_Where_Clause.';
1197 IF 'ITEM+ENI_ITEM_VBH_CAT' = p_viewby THEN
1198 IF 'ALL' = UPPER(p_prodcat) THEN
1199 l_product_where_clause :=' and sumry.product_category_id = pcd.child_id
1200 and pcd.top_node_flag = :l_yes
1201 AND pcd.object_type = ''CATEGORY_SET''
1202 AND pcd.object_id = d.category_set_id
1203 AND d.functional_area_id = 11
1204 AND pcd.dbi_flag = ''Y''';
1205 l_denorm := ',eni_denorm_hierarchies pcd, mtl_default_category_sets d ';
1206 ELSE
1207 l_product_where_clause := ' AND pcd.parent_id = :l_prodcat '||
1208 ' AND sumry.product_category_id = pcd.child_id ';
1209 l_denorm := ',ENI_ITEM_PROD_CAT_LOOKUP_V pcd ';
1210 END IF;
1211
1212 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1213
1217
1214 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1215 MODULE => g_pkg || l_proc ||'Prod cat view by',
1216 MESSAGE => ' Product where clause: '|| l_product_where_clause ||'; denorm: '|| l_denorm);
1218 END IF;
1219
1220 ELSE
1221 /* View by is one of ORGANIZATION+JTF_ORG_SALES_GROUP, TIME+FII_TIME_WEEK, TIME+FII_TIME_ENT_PERIOD
1222 , TIME+FII_TIME_ENT_QTR, TIME+FII_TIME_ENT_YEAR, CAMPAIGN+CAMPAIGN */
1223 IF 'ALL' <> UPPER(p_prodcat) THEN
1224 l_product_where_clause := ' AND sumry.product_category_id = eni1.child_id
1225 AND eni1.object_type = ''CATEGORY_SET''
1226 AND eni1.object_id = d.category_set_id
1227 AND d.functional_area_id = 11
1228 AND eni1.dbi_flag = ''Y''
1229 AND eni1.parent_id = :l_prodcat ';
1230 l_denorm := ',eni_denorm_hierarchies eni1, mtl_default_category_sets d ';
1231 END IF;
1232
1233 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1234
1235 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1236 MODULE => g_pkg || l_proc || p_viewBy,
1237 MESSAGE => ' Product where clause: '|| l_product_where_clause ||'; denorm: '||l_denorm);
1238
1239 END IF;
1240
1241 END IF;
1242 x_denorm := l_denorm;
1243 x_where_clause := l_product_where_clause;
1244 EXCEPTION
1245 WHEN OTHERS THEN
1246
1247 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1248 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1249 fnd_message.set_token('ERROR' ,SQLCODE);
1250 fnd_message.set_token('REASON',SQLERRM);
1251 fnd_message.set_token('ROUTINE',l_proc);
1252
1253 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1254 MODULE => g_pkg || l_proc ,
1255 MESSAGE => fnd_message.get );
1256
1257 END IF;
1258 END get_PC_NoRollup_Where_Clause;
1259 -- **********************************************************************
1260 -- PROCEDURE GET_TREND_PARAMS
1261 --
1262 -- Purpose:
1263 -- used by the trend queries, returns the start and end dates of the trend
1264 -- span, time id, and fii time table depending on the period type
1265 -- week - fii_time_week
1266 -- month - fii_time_ent_period
1267 -- quarter - fii_time_ent_qtr
1268 -- year - fii_time_ent_year
1269 -- **********************************************************************
1270 PROCEDURE GET_TREND_PARAMS( p_comp_type IN VARCHAR2,
1271 p_curr_as_of_date IN DATE,
1272 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1273 p_page_period_type IN VARCHAR2,
1274 x_column_name OUT NOCOPY VARCHAR2,
1275 x_curr_eff_end_date OUT NOCOPY DATE,
1276 x_curr_start_date OUT NOCOPY DATE,
1277 x_prev_eff_end_date OUT NOCOPY DATE,
1278 x_prev_start_date OUT NOCOPY DATE,
1279 x_table_name OUT NOCOPY VARCHAR2
1280
1281 ) AS
1282
1283 l_err_msg VARCHAR2(200);
1284
1285
1286 BEGIN
1287 /* Variable Intialization */
1288 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1289
1290 IF (p_page_parameter_tbl.count > 0) THEN
1291 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
1292
1293 --get period start date
1294 IF p_page_parameter_tbl(i).parameter_name = 'BIS_CUR_REPORT_START_DATE' THEN
1295 x_curr_start_date:= p_page_parameter_tbl(i).period_date;
1296 END IF;
1297 IF p_page_parameter_tbl(i).parameter_name = 'BIS_PREV_REPORT_START_DATE' THEN
1298 x_prev_start_date:= p_page_parameter_tbl(i).period_date;
1299 END IF;
1300 -- GET values for &BIS_CUR_EFFECTIVE_END_DATE
1301 IF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_WEEK_TO' THEN
1302 x_curr_eff_end_date := p_page_parameter_tbl(i).period_date;
1303 END IF;
1304 IF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_TO' THEN
1305 x_curr_eff_end_date := p_page_parameter_tbl(i).period_date;
1306 END IF;
1307 IF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_TO' THEN
1308 x_curr_eff_end_date := p_page_parameter_tbl(i).period_date;
1309 END IF;
1310 IF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_TO' THEN
1311 x_curr_eff_end_date := p_page_parameter_tbl(i).period_date;
1312 END IF;
1313 -- GET values for &BIS_PREVIOUS_EFFECTIVE_END_DATE
1314 IF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_WEEK_PTO' THEN
1315 x_prev_eff_end_date := p_page_parameter_tbl(i).period_date;
1316 END IF;
1317 IF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_PTO' THEN
1318 x_prev_eff_end_date := p_page_parameter_tbl(i).period_date;
1319 END IF;
1320 IF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_PTO' THEN
1321 x_prev_eff_end_date := p_page_parameter_tbl(i).period_date;
1322 END IF;
1323 IF p_page_parameter_tbl(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_PTO' THEN
1324 x_prev_eff_end_date := p_page_parameter_tbl(i).period_date;
1325 END IF;
1326 END LOOP;
1327 END IF;
1328
1329
1330 CASE
1331
1332
1333 WHEN p_page_period_type = 'FII_TIME_ENT_YEAR' THEN
1334 x_table_name := 'fii_time_ent_year';
1335 x_column_name := 'ent_year_id';
1336
1337 /* SELECT ENT_YEAR_ID
1338 INTO x_curr_startprd_id
1339 FROM fii_time_ent_year
1340 WHERE x_curr_start_date BETWEEN start_date AND end_date;
1341 */
1342 WHEN p_page_period_type = 'FII_TIME_WEEK' then
1343 x_table_name := 'fii_time_week';
1344 x_column_name := 'week_id';
1345
1346 WHEN p_page_period_type = 'FII_TIME_ENT_PERIOD' then
1347 x_table_name := 'fii_time_ent_period';
1348 x_column_name := 'ent_period_id';
1349 /*
1350 -- Get p_startprd_id
1351 SELECT ent_period_id
1352 INTO x_curr_startprd_id
1353 FROM fii_time_ent_period
1354 WHERE x_curr_start_date BETWEEN start_date AND end_date;
1355
1356 IF p_comp_type = 'YEARLY' then
1357 -- Get p_prev_startprd_id
1358
1359 SELECT ent_period_id
1360 INTO x_prev_startprd_id
1361 FROM fii_time_ent_period
1362 WHERE x_prev_start_date BETWEEN start_date AND end_date;
1363
1364 END IF;
1365 */
1366
1367 WHEN p_page_period_type = 'FII_TIME_ENT_QTR' then
1368 x_table_name := 'fii_time_ent_qtr';
1369 x_column_name := 'ent_qtr_id';
1370 /*
1371 --Get p_startprd_id
1372
1373 SELECT ent_qtr_id
1374 INTO x_curr_startprd_id
1375 FROM fii_time_ent_qtr
1376 WHERE x_curr_start_date BETWEEN start_date AND end_date;
1377
1378
1379 IF p_comp_type = 'YEARLY' then
1380
1381 --Get p_prev_startprd_id
1382
1383 SELECT ent_qtr_id
1384 INTO x_prev_startprd_id
1385 FROM fii_time_ent_qtr
1386 WHERE x_prev_start_date BETWEEN start_date AND end_date;
1387
1388 END IF;
1389 */
1390
1391 END CASE;
1392
1393 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1394
1395 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1396 MODULE => g_pkg || '.get_conv_rate ',
1397 MESSAGE => 'x_column_name: '||x_column_name||'; '||
1398 'x_curr_eff_end_date: '|| x_curr_eff_end_date||'; '||
1399 'x_curr_start_date: '||x_curr_start_date||'; '||
1400 'x_prev_eff_end_date: '||x_prev_eff_end_date||'; '||
1401 'x_prev_start_date: '||x_prev_start_date||'; '||
1402 'x_table_name: '||x_table_name||'; ');
1403
1404 END IF;
1405
1406 EXCEPTION
1407
1408 WHEN OTHERS THEN
1409 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1410
1411 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1412 fnd_message.set_token('Error is : ' ,SQLCODE);
1413 fnd_message.set_token('Reason is : ', SQLERRM);
1414
1415 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1416 MODULE => g_pkg || '.get_trend_params',
1417 MESSAGE => fnd_message.get );
1418
1419 END IF;
1420 END GET_TREND_PARAMS;
1421 -- **********************************************************************
1422 -- FUNCTION GET_UNASSIGNED_PC
1423 -- Purpose:
1424 -- Returns Unassigned Categoty Used in FE queries
1425 -- when View By Product Category and Product Category = All
1426 -- **********************************************************************
1427
1428 FUNCTION GET_UNASSIGNED_PC RETURN VARCHAR2 AS
1429 l_unassigned_value VARCHAR2(100);
1430 BEGIN
1434 WHERE LOOKUP_TYPE = 'ITEM_CATG' AND
1431 SELECT DESCRIPTION || ' ('|| MEANING ||')'
1432 INTO l_unassigned_value
1433 FROM FND_LOOKUP_VALUES
1435 LOOKUP_CODE = '-1' AND
1436 LANGUAGE = USERENV('LANG');
1437 RETURN l_unassigned_value;
1438 EXCEPTION
1439 WHEN OTHERS THEN
1440
1441 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1442 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1443 fnd_message.set_token('Error is : ' ,SQLCODE);
1444 fnd_message.set_token('Reason is : ', SQLERRM);
1445
1446 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1447 MODULE => g_pkg || '. GET_UNASSIGNED_PC',
1448 MESSAGE => fnd_message.get );
1449
1450 END IF;
1451 END GET_UNASSIGNED_PC;
1452
1453
1454
1455 -- **********************************************************************
1456 -- FUNCTION isUserCurrency
1457 --
1458 -- Purpose:
1459 -- returns a boolean, used to determine whether the user currency is
1460 -- selected. Used by the Sales Management Summary report to disable
1461 -- the drill across URL to FII and ISC if user currency is selected.
1462 --
1463 -- **********************************************************************
1464
1465 FUNCTION isUserCurrency (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL)
1466 RETURN BOOLEAN AS
1467
1468 l_currency VARCHAR2(100);
1469 l_isUserCurrency BOOLEAN;
1470
1471 BEGIN
1472 /* Variable Intialization */
1473 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1474 l_isUserCurrency := FALSE;
1475
1476 IF p_page_parameter_tbl IS NOT NULL THEN
1477
1478 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
1479
1480 IF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' THEN
1481 l_currency := p_page_parameter_tbl(i).parameter_id;
1482
1483 IF INSTR(l_currency,'FII_GLOBAL1') > 0 THEN
1484 l_isUserCurrency := FALSE;
1485 ELSE
1486 l_isUserCurrency := TRUE;
1487 END IF;
1488 END IF;
1489 END LOOP;
1490 END IF;
1491
1492 RETURN l_isUserCurrency;
1493 END;
1494
1495
1496
1497
1498 -- **********************************************************************
1499 -- FUNCTION isLeafNode
1500 --
1501 -- Purpose:
1502 -- returns a boolean, used to determine whether a selected product category
1503 -- is at the leaf node. If so, then self row is selected. If not, then
1504 -- child categories are selected to be displayed in the report.
1505 --
1506 -- **********************************************************************
1507 FUNCTION isLeafNode (p_prodcat_id IN NUMBER) RETURN BOOLEAN AS
1508
1509
1510 leaf_node varchar2(1);
1511
1512
1513 BEGIN
1514 /* Variable Intialization */
1515 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1516
1517 SELECT a.leaf_node_flag INTO leaf_node
1518 FROM ENI_DENORM_HIERARCHIES A,
1519 MTL_DEFAULT_CATEGORY_SETS B
1520 WHERE B.FUNCTIONAL_AREA_ID = 11
1521 AND A.OBJECT_TYPE = 'CATEGORY_SET'
1522 AND A.OBJECT_ID = B.CATEGORY_SET_ID
1523 AND A.DBI_FLAG = 'Y'
1524 AND a.parent_id = p_prodcat_id
1525 AND a.parent_id = a.child_id;
1526
1527
1528 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1529
1530 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1531 MODULE => g_pkg ||'.Leaf_node ',
1532 MESSAGE => 'Leaf node: '||leaf_node
1533 || ', prodcat_id: ' || p_prodcat_id);
1534
1535 END IF;
1536
1537 IF 'Y' = leaf_node THEN
1538 RETURN TRUE;
1539 ELSE
1540 RETURN FALSE;
1541 END IF;
1542
1543
1544 EXCEPTION
1545
1546 WHEN OTHERS THEN
1547
1548 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1549 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1550 fnd_message.set_token('Error is : ' ,SQLCODE);
1551 fnd_message.set_token('Reason is : ', SQLERRM);
1552
1553 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1554 MODULE => g_pkg || '.isLeafNode',
1555 MESSAGE => fnd_message.get );
1556
1557
1558 END IF;
1559
1560 END isLeafNode;
1561
1562
1563 -- **********************************************************************
1564 -- PROCEDURE PARSE_SALES_GROUP_ID
1565 --
1566 -- Purpose: if a resource is selected, then PMV will pass a concatenated
1567 -- resource_id.sales_group_id in the sales_group parameter. Parsing it here
1568 -- into two parameters. Used by the get_page_params procedure, as well as
1569 -- by top_open_oppties report directly.
1570 --
1571 -- **********************************************************************
1572 PROCEDURE PARSE_SALES_GROUP_ID(
1573 p_salesgroup_id IN OUT NOCOPY VARCHAR2,
1574 x_resource_id OUT NOCOPY VARCHAR2
1575 ) AS
1576
1577 l_sg_id VARCHAR2(20);
1578 l_resource_id VARCHAR2(20);
1579 l_dot NUMBER;
1580
1581 BEGIN
1582 /* Variable Intialization */
1583 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1584
1585 l_dot:= INSTR(p_salesgroup_id, '.');
1589 ELSE
1586 IF(l_dot > 0) then
1587 l_sg_id := SUBSTR(p_salesgroup_id,l_dot + 1) ;
1588 l_resource_id := SUBSTR(p_salesgroup_id,1,l_dot - 1);
1590 l_sg_id := p_salesgroup_id;
1591 END IF;
1592
1593 p_salesgroup_id := REPLACE(l_sg_id,'''','');
1594 x_resource_id:= REPLACE(l_resource_id,'''','');
1595
1596
1597 END PARSE_SALES_GROUP_ID;
1598
1599
1600
1601 -- **********************************************************************
1602 -- PROCEDURE getLookupMeaning
1603 --
1604 -- Purpose:
1605 -- Returns meaning corresponding to the type and code passed in.
1606 -- Used to get messages like 'Assigned to Category' etc.
1607 --
1608 -- **********************************************************************
1609
1610 FUNCTION getLookupMeaning(p_lookuptype IN VARCHAR2,p_lookupcode IN VARCHAR2)
1611 RETURN VARCHAR2
1612
1613 AS
1614
1615 l_cat_assign VARCHAR2(4000);
1616
1617 BEGIN
1618 /* Variable Intialization */
1619 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1620
1621 SELECT Meaning INTO l_cat_assign
1622 FROM FND_LOOKUP_VALUES
1623 WHERE LOOKUP_TYPE = p_lookuptype
1624 AND LOOKUP_CODE = p_lookupcode
1625 AND LANGUAGE = USERENV('LANG');
1626
1627 RETURN l_cat_assign;
1628
1629 EXCEPTION
1630 WHEN OTHERS THEN
1631
1632 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1633 fnd_message.set_name('FND','SQL_PLSQL_ERROR');
1634 fnd_message.set_token('ERROR',SQLCODE);
1635 fnd_message.set_token('REASON', SQLERRM);
1636
1637 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1638 MODULE => g_pkg ||'.getLookupMeaning',
1639 MESSAGE => fnd_message.get );
1640
1641 END IF;
1642
1643 END getLookupMeaning;
1644
1645
1646
1647 -- **********************************************************************
1648 -- PROCEDURE GET_PRIOR_PRIOR_TIME
1649 --
1650 -- Purpose:
1651 -- to get prior prior timeid and date
1652 -- returns prior timeid and date for previous date passed as parameter
1653 -- **********************************************************************
1654
1655 PROCEDURE GET_PRIOR_PRIOR_TIME (p_comp_type IN VARCHAR2,
1656 p_period_type IN VARCHAR2,
1657 p_prev_date IN DATE,
1658 p_prev_page_time_id IN NUMBER,
1659 x_prior_prior_date OUT NOCOPY DATE,
1660 x_prior_prior_time_id OUT NOCOPY NUMBER) AS
1661
1662 l_timespan NUMBER;
1663 l_sequence NUMBER;
1664 l_year NUMBER;
1665 l_prior_prior_date DATE;
1666 l_prior_prior_time_id NUMBER;
1667 l_prev_date DATE;
1668 l_proc VARCHAR2(50);
1669
1670 BEGIN
1671
1672 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1673 l_proc := 'get_prior_prior_time';
1674
1675
1676 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1677
1678 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
1679 MODULE => g_pkg || l_proc || 'begin',
1680 MESSAGE => 'Start of Procedure '||l_proc);
1681
1682 END IF;
1683
1684 IF (TRUNC(p_prev_date)>(bis_common_parameters.get_global_start_date-1)) THEN
1685
1686 l_prev_date := TRUNC(p_prev_date);
1687
1688 IF (p_period_type = 'FII_TIME_WEEK') THEN
1689
1690 SELECT l_prev_date-w.start_date, w.sequence, p.year445_id
1691 INTO l_timespan, l_sequence, l_year
1692 FROM fii_time_week w, fii_time_p445 p
1693 WHERE w.period445_id=p.period445_id
1694 AND l_prev_date BETWEEN w.start_date AND w.end_date;
1695
1696 IF p_comp_type='SEQUENTIAL' THEN
1697 if l_sequence=1 then
1698
1699 l_year:=l_year-1;
1700
1701 SELECT MAX(w.sequence)
1702 INTO l_sequence
1703 FROM fii_time_week w, fii_time_p445 p
1704 WHERE w.period445_id=p.period445_id
1705 AND p.year445_id=l_year;
1706 else
1707
1708 l_sequence:=l_sequence-1;
1709 end if;
1710 ELSE
1711
1712 l_year:=l_year-1;
1713 END IF;
1714
1715 SELECT w.week_id, w.start_date+l_timespan
1716 INTO l_prior_prior_time_id, l_prior_prior_date
1717 FROM fii_time_week w, fii_time_p445 p
1718 WHERE w.period445_id=p.period445_id
1719 AND w.sequence=l_sequence
1720 AND p.year445_id=l_year;
1721
1722 ELSIF (p_period_type = 'FII_TIME_ENT_PERIOD') THEN
1723
1724 SELECT p.end_date-l_prev_date, p.sequence, p.ent_year_id
1725 INTO l_timespan, l_sequence, l_year
1726 FROM fii_time_ent_period p
1727 WHERE l_prev_date BETWEEN p.start_date AND p.end_date;
1728
1729 IF p_comp_type='SEQUENTIAL' THEN
1730 if l_sequence=1 then
1731
1732 l_year:=l_year-1;
1733
1734 SELECT MAX(p.sequence)
1735 INTO l_sequence
1736 FROM fii_time_ent_period p
1737 WHERE p.ent_year_id=l_year;
1738 else
1739
1740 l_sequence:=l_sequence-1;
1741 end if;
1742 ELSE
1743
1744 l_year:=l_year-1;
1745 END IF;
1746
1747 SELECT ent_period_id, GREATEST(p.start_date, p.end_date-l_timespan)
1751 AND p.ent_year_id=l_year;
1748 INTO l_prior_prior_time_id, l_prior_prior_date
1749 FROM fii_time_ent_period p
1750 WHERE p.sequence=l_sequence
1752
1753 ELSIF (p_period_type = 'FII_TIME_ENT_QTR') THEN
1754
1755 SELECT end_date-l_prev_date, sequence, ent_year_id
1756 INTO l_timespan, l_sequence, l_year
1757 FROM fii_time_ent_qtr
1758 WHERE l_prev_date BETWEEN start_date AND end_date;
1759
1760 IF p_comp_type='SEQUENTIAL' THEN
1761 if l_sequence=1 then
1762
1763 l_year:=l_year-1;
1764
1765 SELECT MAX(sequence)
1766 INTO l_sequence
1767 FROM fii_time_ent_qtr
1768 WHERE ent_year_id=l_year;
1769 else
1770
1771 l_sequence:=l_sequence-1;
1772 end if;
1773 ELSE
1774
1775 l_year:=l_year-1;
1776 END IF;
1777
1778 SELECT ent_qtr_id, GREATEST(start_date, end_date-l_timespan)
1779 INTO l_prior_prior_time_id, l_prior_prior_date
1780 FROM fii_time_ent_qtr
1781 WHERE sequence=l_sequence
1782 AND ent_year_id=l_year;
1783
1784 ELSIF (p_period_type = 'FII_TIME_ENT_YEAR') THEN
1785
1786 SELECT end_date-l_prev_date, sequence
1787 INTO l_timespan, l_year
1788 FROM fii_time_ent_year
1789 WHERE l_prev_date BETWEEN start_date AND end_date;
1790
1791 SELECT ent_year_id, GREATEST(start_date, end_date-l_timespan)
1792 INTO l_prior_prior_time_id, l_prior_prior_date
1793 FROM fii_time_ent_year
1794 WHERE sequence=l_year-1;
1795
1796 END IF;
1797
1798 /*Code to Handle the null prior prior date*/
1799
1800 IF l_prior_prior_date IS NULL THEN
1801 x_prior_prior_date:=TO_DATE('01/01/1900', 'MM/DD/YYYY');
1802 x_prior_prior_time_id:= -999;
1803 ELSE
1804 x_prior_prior_date:=l_prior_prior_date;
1805 x_prior_prior_time_id:=l_prior_prior_time_id;
1806 END IF;
1807
1808 ELSE
1809
1810 x_prior_prior_date:=TRUNC(p_prev_date);
1811 x_prior_prior_time_id:=p_prev_page_time_id;
1812
1813 END IF;
1814 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1815
1816 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
1817 MODULE => g_pkg || l_proc || 'End',
1818 MESSAGE => 'End of Procedure '||l_proc);
1819
1820 END IF;
1821
1822
1823 EXCEPTION
1824
1825 WHEN NO_DATA_FOUND THEN
1826 x_prior_prior_date:=TO_DATE('01/01/1900', 'MM/DD/YYYY');
1827 x_prior_prior_time_id:= -999;
1828
1829 WHEN OTHERS THEN
1830
1831 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1832 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1833 fnd_message.set_token('Error is : ' ,SQLCODE);
1834 fnd_message.set_token('Reason is : ', SQLERRM);
1835
1836 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1837 MODULE => g_pkg || '. GET_PRIOR_PRIOR_TIME',
1838 MESSAGE => fnd_message.get );
1839 END IF;
1840 END GET_PRIOR_PRIOR_TIME;
1841
1842 -- **********************************************************************************
1843 -- FUNCTION GET_DRILL_LINKS
1844 --
1845 -- Purpose:
1846 -- to get drill links
1847 -- returns drill link for viewby,salesgroup,salesrep passed as parameter
1848 -- **********************************************************************************
1849
1850 FUNCTION GET_DRILL_LINKS ( p_view_by IN VARCHAR2,
1851 p_salesgroup_id IN VARCHAR2,
1852 p_resource_id IN VARCHAR2
1853 ) RETURN VARCHAR2
1854
1855 AS
1856
1857 l_view_by VARCHAR2(200);
1858 l_salesgroup_id VARCHAR2(100);
1859 l_resource_id VARCHAR2(100);
1860 l_drill_link VARCHAR2(3000) := NULL;
1861 l_proc VARCHAR2(50);
1862
1863 BEGIN
1864
1865 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1866 l_proc := 'GET_DRILL_LINKS';
1867
1868
1869 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1870
1871 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
1872 MODULE => g_pkg || l_proc || 'begin',
1873 MESSAGE => 'Start of Procedure '||l_proc);
1874
1875 END IF;
1876
1877 l_view_by := p_view_by;
1878 l_resource_id := p_resource_id ;
1879 l_salesgroup_id := p_salesgroup_id ;
1880
1881 IF(l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') --View By = Product Categroy
1882 THEN
1883 IF (l_resource_id IS NOT NULL) -- Some SalesRep is selected in the salesgroup LOV
1884 THEN
1885 l_drill_link := 'pFunctionName=BIL_BI_OPPTY_LINE_DETAIL_R&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID&';
1886 END IF;
1887 ELSE -- VBY=Sales Group
1888 l_drill_link := 'pFunctionName=BIL_BI_OPPTY_LINE_DETAIL_R&pParamIds=Y&VIEW_BY_NAME=VIEW_BY_ID&';
1889 END IF;
1890
1891 IF FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1892
1893 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_EVENT,
1894 MODULE => g_pkg || l_proc,
1895 MESSAGE => 'Drill Link is =>'||l_drill_link);
1899
1896
1897 END IF;
1898
1900 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1901
1902 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
1903 MODULE => g_pkg || l_proc || 'End',
1904 MESSAGE => 'End of Procedure '||l_proc);
1905
1906 END IF;
1907
1908
1909 RETURN l_drill_link ;
1910
1911
1912 EXCEPTION
1913
1914 WHEN OTHERS THEN
1915 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1916 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1917 fnd_message.set_token('Error is : ' ,SQLCODE);
1918 fnd_message.set_token('Reason is : ', SQLERRM);
1919
1920 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1921 MODULE => g_pkg || l_proc,
1922 MESSAGE => fnd_message.get );
1923
1924 END IF;
1925
1926 END GET_DRILL_LINKS;
1927
1928 -- **********************************************************************************
1929 -- FUNCTION GET_LABEL_SGR
1930 -- Purpose: to change the column headng dynamically when Sales Group/Sales Rep is selected from SG LOV on report
1931 -- **********************************************************************************
1932
1933 FUNCTION GET_LBL_SGFST (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL) RETURN VARCHAR2
1934 AS
1935
1936 l_salesgroup_id VARCHAR2(100);
1937 l_resource_id VARCHAR2(100);
1938 l_label VARCHAR2(100);
1939 l_proc VARCHAR2(50);
1940
1941 BEGIN
1942
1943 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
1944 l_proc := 'GET_LBL_SGFST';
1945
1946 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1947
1948 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
1949 MODULE => g_pkg || l_proc || 'begin',
1950 MESSAGE => 'Start of Procedure '||l_proc);
1951
1952 END IF;
1953
1954
1955 FOR i IN 1..p_page_parameter_tbl.COUNT
1956 LOOP
1957 IF p_page_parameter_tbl(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP' THEN
1958
1959 l_salesgroup_id := p_page_parameter_tbl(i).parameter_id;
1960
1961 BIL_BI_UTIL_PKG.PARSE_SALES_GROUP_ID(
1962 p_salesgroup_id =>l_salesgroup_id,
1963 x_resource_id =>l_resource_id);
1964 END IF;
1965 END LOOP;
1966
1967
1968 IF l_resource_id is NULL THEN
1969 l_label:=FND_MESSAGE.GET_STRING('BIL','BIL_BI_LABEL_SGRP_FCST');
1970 ELSE
1971 l_label:=FND_MESSAGE.GET_STRING('BIL','BIL_BI_LABEL_SREP_FCST');
1972 END IF;
1973
1974 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1975
1976 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
1977 MODULE => g_pkg || l_proc || 'End',
1978 MESSAGE => 'End of Procedure '||l_proc);
1979
1980 END IF;
1981
1982 RETURN l_label;
1983
1984 EXCEPTION
1985
1986 WHEN OTHERS THEN
1987
1988 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1989 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1990 fnd_message.set_token('Error is : ' ,SQLCODE);
1991 fnd_message.set_token('Reason is : ', SQLERRM);
1992
1993 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1994 MODULE => g_pkg || l_proc,
1995 MESSAGE => fnd_message.get );
1996
1997 END IF;
1998 RETURN 'Direct Reports Forecast';
1999
2000 END GET_LBL_SGFST;
2001
2002 -- **********************************************************************************
2003 -- PROCEDURE GET_PIPE_SNAP_DATE
2004 -- Purpose: to get the snap date for the passed as_of_date
2005 -- **********************************************************************************
2006
2007 PROCEDURE GET_PIPE_SNAP_DATE( p_as_of_date IN DATE,
2008 p_prev_date IN DATE,
2009 p_period_type IN VARCHAR2,
2010 p_coll_st_date IN DATE,
2011 p_coll_end_date IN DATE,
2012 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
2013 x_snap_date OUT NOCOPY DATE,
2014 x_prev_snap_date OUT NOCOPY DATE
2015 )
2016
2017 AS
2018
2019 l_as_of_date DATE;
2020 l_proc VARCHAR2(1000);
2021 l_sysdate DATE:= TRUNC(sysdate);
2022 l_snapshot_date DATE;
2023 l_prev_snap_date DATE;
2024 l_coll_st_date DATE;
2025 l_coll_end_date DATE;
2026 l_period_start_date DATE;
2027 l_period_type VARCHAR2(1000);
2028
2029 BEGIN
2030
2031 l_as_of_date := trunc(p_as_of_date);
2032 l_coll_st_date := p_coll_st_date;
2033 l_coll_end_date := p_coll_end_date;
2034 l_period_type := p_period_type;
2035
2036 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
2037 l_proc := 'GET_PIPE_SNAP_DATE';
2038
2039 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2040
2044
2041 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2042 MODULE => g_pkg || l_proc || 'begin',
2043 MESSAGE => 'Start of Procedure '||l_proc);
2045 END IF;
2046 /*
2047 IF(l_as_of_date <> l_sysdate) THEN
2048
2049 IF (l_as_of_date > l_sysdate) THEN
2050 l_snapshot_date := l_as_of_date;
2051 ELSE
2052 IF(l_as_of_date >= l_coll_st_date) THEN
2053 l_snapshot_date := l_as_of_date;
2054 ELSE
2055 l_snapshot_date := BIL_BI_UTIL_PKG.GET_HIST_SNAPSHOT_DATE(l_as_of_date, p_period_type);
2056 END IF;
2057 END IF;
2058 */
2059
2060 IF(l_as_of_date <> l_sysdate) THEN
2061
2062 l_snapshot_date := l_as_of_date;
2063
2064 IF(l_as_of_date < l_coll_st_date) THEN
2065
2066 l_snapshot_date := BIL_BI_UTIL_PKG.GET_HIST_SNAPSHOT_DATE(l_as_of_date, p_period_type);
2067
2068 IF p_prev_date IS NOT NULL THEN
2069 l_prev_snap_date := BIL_BI_UTIL_PKG.GET_HIST_SNAPSHOT_DATE(p_prev_date, p_period_type);
2070 END IF;
2071 END IF;
2072
2073 ELSE --- (l_as_of_date = sysdate)
2074
2075 BIL_BI_UTIL_PKG.GET_CURR_START_DATE(p_page_parameter_tbl =>p_page_parameter_tbl,
2076 p_as_of_date => l_as_of_date,
2077 p_period_type => l_period_type,
2078 x_curr_start_date => l_period_start_date);
2079
2080 IF (l_coll_end_date = l_sysdate OR (l_coll_end_date = l_sysdate-1 and l_period_start_date<= l_sysdate-1) ) THEN
2081
2082 IF (l_coll_end_date =l_sysdate) THEN
2083 l_snapshot_date := l_as_of_date;
2084 ELSE
2085 l_snapshot_date := l_coll_end_date;
2086 END IF;
2087 ELSE
2088 --- Case where last collection was 2 days older than sysdate(as-of-date)
2089 --- This would ultimately not return any data, though the following parameters are passed to
2090 --- the query.
2091 l_snapshot_date := l_as_of_date;
2092 END IF;
2093 END IF;
2094
2095 -- l_prev_snap_date := BIL_BI_UTIL_PKG.GET_HIST_SNAPSHOT_DATE(p_prev_date, p_period_type);
2096
2097
2098 x_snap_date := l_snapshot_date;
2099 x_prev_snap_date := l_prev_snap_date;
2100
2101
2102 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2103
2104 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2105 MODULE => g_pkg || l_proc || 'End',
2106 MESSAGE => 'End of Procedure '||l_proc);
2107
2108 END IF;
2109
2110 EXCEPTION
2111
2112 WHEN OTHERS THEN
2113
2114 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2115 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2116 fnd_message.set_token('Error is : ' ,SQLCODE);
2117 fnd_message.set_token('Reason is : ', SQLERRM);
2118
2119 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
2120 MODULE => g_pkg || l_proc,
2121 MESSAGE => fnd_message.get );
2122 END IF;
2123
2124 END GET_PIPE_SNAP_DATE;
2125
2126 -- **********************************************************************************
2127 -- PROCEDURE GET_PIPE_MV
2128 -- Purpose: Based on the Date Current/Historical Pipeline MV is passed along with the
2129 -- snapshot date.
2130 -- **********************************************************************************
2131
2132 PROCEDURE GET_PIPE_MV(
2133 p_asof_date IN DATE,
2134 p_period_type IN VARCHAR2,
2135 p_compare_to IN VARCHAR2,
2136 p_prev_date IN DATE,
2137 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
2138 x_pipe_mv OUT NOCOPY VARCHAR2,
2139 x_snapshot_date OUT NOCOPY DATE,
2140 x_prev_snap_date OUT NOCOPY DATE
2141 )
2142 AS
2143
2144 l_pipe_mv VARCHAR2(1000);
2145 l_period_type VARCHAR2(1000);
2146 l_as_of_date DATE;
2147 l_coll_st_date DATE;
2148 l_coll_end_date DATE;
2149 l_period_start_date DATE;
2150 l_snapshot_date DATE;
2151 l_prev_snap_date DATE;
2152 l_proc VARCHAR2(1000);
2153 l_sysdate DATE := TRUNC(SYSDATE);
2154
2155
2156 BEGIN
2157
2158 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
2159 l_proc := 'GET_PIPE_MV';
2160 l_as_of_date := trunc(p_asof_date);
2161 l_period_type := p_period_type ;
2162
2163
2164 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2165
2166 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2167 MODULE => g_pkg || l_proc || 'begin',
2168 MESSAGE => 'Start of Procedure '||l_proc);
2169
2170 END IF;
2171
2172
2173 SELECT to_date(attribute1, 'dd/mm/yyyy'), MAX(to_date(attribute2, 'dd/mm/yyyy'))
2174 INTO l_coll_st_date,l_coll_end_date
2175 FROM bis_refresh_log a
2176 WHERE
2177 a.last_update_date = (
2178 SELECT MAX(b.last_update_date) FROM bis_refresh_log b
2179 WHERE b.object_name = 'BIL_BI_PIPEC_F'
2180 AND status = 'SUCCESS'
2181 )
2182 AND a.object_name = 'BIL_BI_PIPEC_F'
2183 AND status = 'SUCCESS'
2184 group by to_date(attribute1, 'dd/mm/yyyy');
2185
2186
2187
2188 /*
2189 IF(l_as_of_date <> l_sysdate) THEN
2190
2191 IF (l_as_of_date > l_sysdate) THEN
2192
2193 l_pipe_mv := 'BIL_BI_PIPEC_G_MV';
2194 ELSE
2195 IF(l_as_of_date >= l_coll_st_date) THEN
2196 l_pipe_mv := 'BIL_BI_PIPEC_G_MV';
2197 ELSE
2198 l_pipe_mv := 'BIL_BI_PIPE_G_MV';
2199 END IF;
2200 END IF;
2201 ELSE
2202 l_pipe_mv := 'BIL_BI_PIPEC_G_MV';
2203 END IF;
2204 */
2205 --to be evaluated
2206
2207 l_pipe_mv := 'BIL_BI_PIPEC_G_MV';
2208
2209 IF (l_as_of_date < l_coll_st_date) THEN
2210 l_pipe_mv := 'BIL_BI_PIPE_G_MV';
2211 END IF;
2212
2213
2214 BIL_BI_UTIL_PKG.GET_PIPE_SNAP_DATE( p_as_of_date => l_as_of_date
2215 ,p_prev_date => p_prev_date
2216 ,p_period_type => l_period_type
2217 ,p_coll_st_date => l_coll_st_date
2218 ,p_coll_end_date => l_coll_end_date
2219 ,p_page_parameter_tbl => p_page_parameter_tbl
2220 ,x_snap_date => l_snapshot_date
2221 ,x_prev_snap_date => l_prev_snap_date);
2222
2223
2224 x_pipe_mv := l_pipe_mv;
2225 x_snapshot_date := l_snapshot_date ;
2226 x_prev_snap_date := l_prev_snap_date;
2227
2228
2229 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2230
2231 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2232 MODULE => g_pkg || l_proc || 'End',
2233 MESSAGE => 'End of Procedure '||l_proc);
2234
2235 END IF;
2236
2237 EXCEPTION
2238
2239 WHEN NO_DATA_FOUND THEN
2240
2241 x_pipe_mv := 'BIL_BI_PIPEC_G_MV';
2242 x_snapshot_date := TRUNC(SYSDATE);
2246
2243 x_prev_snap_date := TRUNC(SYSDATE);
2244
2245 WHEN OTHERS THEN
2247 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2248 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2249 fnd_message.set_token('Error is : ' ,SQLCODE);
2250 fnd_message.set_token('Reason is : ', SQLERRM);
2251
2252 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
2253 MODULE => g_pkg || l_proc,
2254 MESSAGE => fnd_message.get );
2255
2256 END IF;
2257
2258 END GET_PIPE_MV;
2259
2260 -- **********************************************************************************
2261 -- FUNCTION GET_HIST_SNAPSHOT_DATE
2262 -- Purpose: Returns the snapshot date from Historical Pipeline MV closet date.
2263 -- **********************************************************************************
2264
2265 FUNCTION GET_HIST_SNAPSHOT_DATE ( p_asof_date IN DATE,
2266 x_period_type IN VARCHAR2
2267 ) RETURN DATE
2268 AS
2269
2270 l_period_type VARCHAR2(1000);
2271 l_end_date DATE;
2272 l_period_end_date DATE;
2273 l_qtr_end_date DATE;
2274 l_year_end_date DATE;
2275 l_month_end_date DATE;
2276 l_as_of_date DATE;
2277 l_week_end_date DATE;
2278 l_week_start_date DATE;
2279 l_proc VARCHAR2(1000);
2280
2281 BEGIN
2282
2283 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
2284 l_proc := 'GET_HIST_SNAPSHOT_DATE';
2285
2286 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2287
2288 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2289 MODULE => g_pkg || l_proc || 'begin',
2290 MESSAGE => 'Start of Function '||l_proc);
2291
2292 END IF;
2293
2294 l_as_of_date := trunc(p_asof_date);
2295 l_period_type := x_period_type;
2296
2297
2298 SELECT week_end_date, ent_period_end_date, ent_qtr_end_date, ent_year_end_date
2299 INTO l_week_end_date, l_period_end_date, l_qtr_end_date, l_year_end_date
2300 FROM FII_TIME_DAY
2301 WHERE report_date = l_as_of_date;
2302
2303
2304 IF (l_period_type = 'FII_TIME_ENT_PERIOD') THEN
2305
2306 IF (l_period_end_date= l_as_of_date) THEN
2307 l_end_date := l_as_of_date;
2308 ELSE
2309 l_end_date := LEAST(l_week_end_date, l_period_end_date);
2310 END IF;
2311
2312 ELSIF (l_period_type = 'FII_TIME_ENT_QTR') THEN
2313
2314 IF (l_qtr_end_date= l_as_of_date) THEN
2315 l_end_date := l_as_of_date;
2316 ELSE
2317 l_end_date := LEAST(l_week_end_date, l_qtr_end_date);
2318 END IF;
2319
2320 ELSIF (l_period_type = 'FII_TIME_ENT_YEAR') THEN
2321
2322 IF (l_year_end_date= l_as_of_date) THEN
2323 l_end_date := l_as_of_date;
2324 ELSE
2325 l_end_date := LEAST(l_week_end_date, l_year_end_date);
2326 END IF;
2327
2328 ELSE --(week)
2329
2330 l_end_date := l_week_end_date;
2331 END IF;
2332
2333
2334 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2335
2336 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2337 MODULE => g_pkg || l_proc || 'End',
2338 MESSAGE => 'End of Procedure '||l_proc);
2339
2340 END IF;
2341
2342 RETURN l_end_date;
2343
2344 EXCEPTION
2345
2346 WHEN NO_DATA_FOUND THEN
2347
2348 RETURN TRUNC(SYSDATE);
2349
2350 WHEN OTHERS THEN
2351
2352 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2353 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2354 fnd_message.set_token('Error is : ' ,SQLCODE);
2355 fnd_message.set_token('Reason is : ', SQLERRM);
2356
2357 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
2358 MODULE => g_pkg || l_proc,
2359 MESSAGE => fnd_message.get );
2360
2361 END IF;
2362
2363 END GET_HIST_SNAPSHOT_DATE;
2364
2365
2366 -- **********************************************************************************
2367 -- FUNCTION GET_PIPE_COL_NAMES
2368 -- Purpose: Returns the column names for previous Year/Period for Pipe/Open/WtdPipe amts.
2369 -- **********************************************************************************
2370
2371 FUNCTION GET_PIPE_COL_NAMES( p_period_type IN VARCHAR2,
2372 p_compare_to IN VARCHAR2,
2373 p_column_type IN VARCHAR2,
2374 p_curr_suffix IN VARCHAR2
2375 ) RETURN VARCHAR2
2376 AS
2377 l_period_type VARCHAR2(1000);
2378 l_compare_to VARCHAR2(1000);
2379 l_prev_amt VARCHAR2(1000);
2380 l_column_type VARCHAR2(1000);
2381 l_proc VARCHAR2(1000);
2382
2383 BEGIN
2384
2385 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
2386 l_proc := 'GET_PIPE_COL_NAMES';
2387
2388 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2389
2390 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2391 MODULE => g_pkg || l_proc || 'begin',
2392 MESSAGE => 'Start of Function '||l_proc);
2393
2397 l_period_type := p_period_type ;
2394 END IF;
2395
2396
2398 l_compare_to := p_compare_to;
2399 l_column_type := p_column_type;
2400
2401 IF l_compare_to IS NOT NULL THEN
2402
2403 CASE
2404 WHEN upper(l_column_type) = 'O' THEN l_prev_amt := '_OPEN_AMT_';
2405 WHEN upper(l_column_type) = 'P' THEN l_prev_amt := '_PIPE_AMT_';
2406 WHEN upper(l_column_type) = 'W' THEN l_prev_amt := '_WTD_PIPE_AMT_';
2407 END CASE;
2408
2409 -- TIME_COMPARISON_TYPE+YEARLY && TIME_COMPARISON_TYPE+SEQUENTIAL
2410
2411 IF(l_compare_to = 'SEQUENTIAL') THEN
2412 CASE
2413 WHEN l_period_type = 'FII_TIME_WEEK' THEN
2414 l_prev_amt := 'PRVPRD'||l_prev_amt||'WK';
2415
2416 WHEN l_period_type = 'FII_TIME_ENT_PERIOD' THEN
2417 l_prev_amt := 'PRVPRD'||l_prev_amt||'PRD';
2418
2419 WHEN l_period_type = 'FII_TIME_ENT_QTR' THEN
2420 l_prev_amt := 'PRVPRD'||l_prev_amt||'QTR';
2421
2422 WHEN l_period_type = 'FII_TIME_ENT_YEAR' THEN
2423 l_prev_amt := 'PRVPRD'||l_prev_amt||'YR';
2424 END CASE;
2425 ELSIF (l_compare_to = 'YEARLY') THEN
2426 CASE
2427 WHEN l_period_type = 'FII_TIME_WEEK' THEN
2428 l_prev_amt := 'PRVYR'||l_prev_amt||'WK';
2429
2430 WHEN l_period_type = 'FII_TIME_ENT_PERIOD' THEN
2431 l_prev_amt := 'PRVYR'||l_prev_amt||'PRD';
2432
2433 WHEN l_period_type = 'FII_TIME_ENT_QTR' THEN
2434 l_prev_amt := 'PRVYR'||l_prev_amt||'QTR';
2435
2436 WHEN l_period_type = 'FII_TIME_ENT_YEAR' THEN
2437 l_prev_amt := 'PRVYR'||l_prev_amt||'YR';
2438
2439 END CASE;
2440 END IF;
2441
2442 ELSE
2443
2444 CASE
2445 WHEN upper(l_column_type) = 'O' THEN l_prev_amt := 'OPEN_AMT_';
2446 WHEN upper(l_column_type) = 'P' THEN l_prev_amt := 'PIPELINE_AMT_';
2447 WHEN upper(l_column_type) = 'W' THEN l_prev_amt := 'WTD_PIPELINE_AMT_';
2448 END CASE;
2449
2450 CASE
2451 WHEN l_period_type = 'FII_TIME_WEEK' THEN
2452 l_prev_amt := l_prev_amt||'WEEK';
2453
2454 WHEN l_period_type = 'FII_TIME_ENT_PERIOD' THEN
2455 l_prev_amt := l_prev_amt||'PERIOD';
2456
2457 WHEN l_period_type = 'FII_TIME_ENT_QTR' THEN
2458 l_prev_amt := l_prev_amt||'QUARTER';
2459
2460 WHEN l_period_type = 'FII_TIME_ENT_YEAR' THEN
2461 l_prev_amt := l_prev_amt||'YEAR';
2462 END CASE;
2463 END IF;
2464
2465 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2466
2467 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2468 MODULE => g_pkg || l_proc || 'End',
2469 MESSAGE => 'End of Function '||l_proc);
2470
2471 END IF;
2472 RETURN l_prev_amt||p_curr_suffix;
2473
2474 EXCEPTION
2475
2476 WHEN OTHERS THEN
2477
2478 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2479 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2480 fnd_message.set_token('Error is : ' ,SQLCODE);
2481 fnd_message.set_token('Reason is : ', SQLERRM);
2482
2483 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
2484 MODULE => g_pkg || l_proc,
2485 MESSAGE => fnd_message.get );
2486
2487 END IF;
2488
2489 END GET_PIPE_COL_NAMES;
2490
2491
2492 -- **********************************************************************************
2493 -- PROCEDURE GET_PIPE_TREND_SOURCE
2494 -- Purpose: Returns the Pipeline MV Curr/Hist/View along with Snapshot Date.
2495 -- **********************************************************************************
2496
2497 PROCEDURE GET_PIPE_TREND_SOURCE (p_as_of_date IN DATE,
2498 p_prev_date IN DATE,
2499 p_trend_type IN VARCHAR2,
2500 p_period_type IN VARCHAR2,
2501 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
2502 x_pipe_mv OUT NOCOPY VARCHAR2,
2503 x_snap_date OUT NOCOPY DATE,
2504 x_prev_snap_date OUT NOCOPY DATE)
2505 AS
2506
2507 l_coll_st_date DATE;
2508 l_coll_end_date DATE;
2509 l_period_start_date DATE;
2510 l_tmp_date DATE;
2511 l_as_of_date DATE;
2512 l_snapshot_date DATE;
2513 l_prev_snap_date DATE;
2514 l_pipe_mv VARCHAR2(200);
2515 l_proc VARCHAR2(1000);
2516 l_sysdate DATE:= TRUNC(sysdate);
2517
2518 BEGIN
2519
2520 g_pkg := 'bil.patch.115.sql.BIL_BI_UTIL_PKG.';
2521 l_proc := 'GET_PIPE_TREND_SOURCE';
2522
2523 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2524
2525 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2526 MODULE => g_pkg || l_proc || 'begin',
2527 MESSAGE => 'Start of Procedure '||l_proc);
2528
2529 END IF;
2530
2531 l_as_of_date:= trunc(p_as_of_date);
2532
2533 SELECT to_date(attribute1, 'dd/mm/yyyy'), MAX(to_date(attribute2, 'dd/mm/yyyy'))
2534 INTO l_coll_st_date,l_coll_end_date
2535 FROM bis_refresh_log a
2536 WHERE
2537 a.last_update_date = (
2538 SELECT MAX(b.last_update_date) FROM bis_refresh_log b
2539 WHERE b.object_name = 'BIL_BI_PIPEC_F'
2540 AND status = 'SUCCESS'
2541 )
2542 AND a.object_name = 'BIL_BI_PIPEC_F'
2543 AND status = 'SUCCESS'
2544 group by to_date(attribute1, 'dd/mm/yyyy');
2545
2546 ---to get MV
2547
2548 IF (l_as_of_date < l_coll_st_date) THEN
2549 l_pipe_mv := 'BIL_BI_PIPE_G_MV';
2550 ELSE
2551 l_pipe_mv := 'BIL_BI_PIPE_G_V';
2552 END IF;
2553
2554
2555 IF p_trend_type = 'P' THEN
2556
2557 SELECT MIN(date2) INTO l_tmp_date FROM bil_bi_rpt_tmp1;
2558
2559 IF (l_tmp_date >= l_coll_st_date) THEN
2560 l_pipe_mv := 'BIL_BI_PIPEC_G_MV';
2561 END IF;
2562
2563 END IF;
2564
2565 /*
2566 IF p_trend_type = 'P' THEN
2567
2568 SELECT MIN(date2) INTO l_tmp_date FROM bil_bi_rpt_tmp1;
2569
2570 IF (l_tmp_date >= l_coll_st_date) THEN
2571 l_pipe_mv := 'BIL_BI_PIPEC_G_MV';
2572 ELSE
2573 IF (l_as_of_date < l_coll_st_date) THEN
2574 l_pipe_mv := 'BIL_BI_PIPE_G_MV';
2575 ELSE
2576 l_pipe_mv := 'BIL_BI_PIPE_G_V';
2577 END IF;
2578 END IF;
2579 ELSE
2580 IF (l_as_of_date < l_coll_st_date) THEN
2581 l_pipe_mv := 'BIL_BI_PIPE_G_MV';
2582 ELSE
2583 l_pipe_mv := 'BIL_BI_PIPE_G_V';
2584 END IF;
2585 END IF;
2586 */
2587 --to get snapshot date
2588
2589 BIL_BI_UTIL_PKG.GET_PIPE_SNAP_DATE(p_as_of_date => l_as_of_date
2590 ,p_prev_date => p_prev_date
2591 ,p_period_type => p_period_type
2592 ,p_coll_st_date => l_coll_st_date
2593 ,p_coll_end_date => l_coll_end_date
2594 ,p_page_parameter_tbl => p_page_parameter_tbl
2595 ,x_snap_date => l_snapshot_date
2596 ,x_prev_snap_date => l_prev_snap_date);
2597
2598 IF (p_prev_date IS NOT NULL AND l_prev_snap_date IS NULL) THEN
2599
2600 l_prev_snap_date := BIL_BI_UTIL_PKG.GET_HIST_SNAPSHOT_DATE(p_prev_date, p_period_type);
2601
2602 END IF;
2603
2604 x_pipe_mv := l_pipe_mv;
2605 x_snap_date := l_snapshot_date;
2606 x_prev_snap_date := l_prev_snap_date;
2607
2608
2609
2610
2611 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2612
2613 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2614 MODULE => g_pkg || l_proc || 'End',
2615 MESSAGE => 'End of Procedure '||l_proc);
2616
2617 END IF;
2618
2619 EXCEPTION
2620
2621 WHEN NO_DATA_FOUND THEN
2622
2623 x_pipe_mv := 'BIL_BI_PIPEC_G_MV';
2624 x_snap_date := TRUNC(SYSDATE);
2625 x_prev_snap_date := TRUNC(SYSDATE);
2626
2627 WHEN OTHERS THEN
2628
2629 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2630 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2631 fnd_message.set_token('Error is : ' ,SQLCODE);
2632 fnd_message.set_token('Reason is : ', SQLERRM);
2633
2634 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
2635 MODULE => g_pkg || l_proc,
2636 MESSAGE => fnd_message.get );
2637
2638 END IF;
2639
2640 END GET_PIPE_TREND_SOURCE;
2641
2642 END BIL_BI_UTIL_PKG;