[Home] [Help]
PACKAGE BODY: APPS.BIL_BI_FST_DTL_F_PKG
Source
1 PACKAGE BODY BIL_BI_FST_DTL_F_PKG AS
2 /*$Header: bilfstb.pls 120.2 2005/10/06 03:45:04 asolaiy noship $*/
3
4 -- Global variables for WHO variables and Concurrent program
5
6 G_request_id NUMBER;
7 G_appl_id NUMBER;
8 G_program_id NUMBER;
9 G_user_id NUMBER;
10 G_login_id NUMBER;
11
12 g_first_run boolean;
13 g_mode VARCHAR2(30);
14 g_truncate_flag boolean;
15 g_debug boolean;
16 g_conv_rate_cnt number;
17 g_degree Number;
18 g_num_row_proc Number;
19 g_fact_row_proc Number;
20 g_obj_name VARCHAR2(30);
21 g_retcode VARCHAR2(20);
22 g_cal_start_date DATE;
23 g_cal VARCHAR2(50);
24 g_cal_per_type VARCHAR2(50);
25 g_fsct_per_type VARCHAR2(50);
26 g_asf_calendar VARCHAR2(50);
27 g_map_ent_per_type VARCHAR2(50);
28 G_Resume_Flag VARCHAR2(1);
29 g_warn_flag VARCHAR2(1);
30 g_missing_rates NUMBER;
31 g_missing_time NUMBER;
32 G_End_Date DATE;
33 G_Start_Date DATE;
34 G_Bil_Schema VARCHAR2(30);
35 G_Prim_Currency VARCHAR2(10);
36 G_Prim_Rate_Type VARCHAR2(15);
37
38 G_sec_Currency VARCHAR2(10);
39 G_sec_Rate_Type VARCHAR2(15);
40
41
42 G_Phase VARCHAR2(100);
43 g_program_start_time DATE;
44
45 G_INIT_FAILED EXCEPTION;
46 G_SETUP_VALID_ERROR EXCEPTION;
47
48 g_fst_rollup VARCHAR2(100);
49
50 g_pkg VARCHAR2(100);
51
52 g_asn_date DATE;
53
54 -- ---------------------------------------------------------------
55 -- Prototypes of Private procedures and Functions;
56 -- ---------------------------------------------------------------
57 -- ***********************************************************************
58
59 -- Initialize all the globals.
60 PROCEDURE Init(p_obj_name in varchar2);
61
62
63 -- Check for errors in the data set collected in staging table. Verify currency, time , .etc
64 PROCEDURE Summary_Err_Check;
65
66 -- Clean up...
67 PROCEDURE Clean_Up(ErrorMsg in varchar2);
68
69
70
71 -- Report missing currencies in a standard format.
72 --we'll report the missing rates from the staging table
73 PROCEDURE REPORT_MISSING_RATES;
74
75
76 -- Summarize data for forecast time period
77 PROCEDURE Summarize_Frcsts_Periods;
78
79
80 --Populate table with distinct cuurency exchange rate, date combinations
81 PROCEDURE POPULATE_CURRENCY_RATE;
82
83 -- If the collected data set is a valid, then merge into the summary table.
84 PROCEDURE Insert_From_Stg( ERRBUF IN OUT NOCOPY VARCHAR2
85 ,RETCODE IN OUT NOCOPY VARCHAR2
86 );
87
88
89 --insert into staging.
90 PROCEDURE Insert_Into_Stg
91 (
92 p_mode IN VARCHAR2
93 );
94
95
96
97 -- Get the number of new forecast records since last run
98 FUNCTION New_Forecasts(P_Start_Date IN DATE , P_End_Date IN DATE) RETURN NUMBER;
99
100 -- Adjust amounts in incremental mode to maitain correct history
101 PROCEDURE summary_adjust;
102
103 --the normal validate setup proc used for validating the setup
104 PROCEDURE validate_setup(ret_status out nocopy boolean);
105
106
107 PROCEDURE check_profiles(ret_status out nocopy boolean);
108
109
110 PROCEDURE main ( errbuf in out nocopy varchar2
111 ,retcode in out nocopy varchar2
112 ,p_start_date in varchar2
113 ,p_end_date in varchar2
114 ,p_truncate in varchar2
115 );
116
117
118 PROCEDURE init_load
119 (
120 errbuf in out nocopy varchar2,
121 retcode in out nocopy varchar2,
122 p_start_date in varchar2,
123 p_truncate in varchar2
124 ) is
125
126 l_proc varchar2(100);
127
128 begin
129
130 /* initialization of variable */
131 g_request_id := 0;
132 g_appl_id := 0;
133 G_program_id := 0;
134 G_user_id := 0;
135 G_login_id := 0;
136 g_first_run :=FALSE;
137 g_truncate_flag := FALSE;
138 g_debug := FALSE;
139 g_conv_rate_cnt := 0;
140 g_degree :=1;
141 g_num_row_proc :=0;
142 g_fact_row_proc :=0;
143 g_obj_name := 'BIL_BI_FST_DTL_F';
144 g_missing_rates := 0;
145 g_missing_time := 0;
146 g_pkg := 'bil.patch.115.sql.BIL_BI_FST_DTL_F_PKG.';
147
148 l_proc := 'INIT_LOAD.';
149 /* end initialization of variable */
150
151 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
152 bil_bi_util_collection_pkg.writeLog(
153 p_log_level => fnd_log.LEVEL_PROCEDURE,
154 p_module => g_pkg || l_proc || ' begin ',
155 p_msg => ' Start of Procedure '|| l_proc);
156 END IF;
157
158 IF p_truncate = 'Y' THEN
159 g_mode := 'INITIAL';
160 ELSE
161 g_mode := '';
162 END IF;
163 -- in the initial load mode, default start date to global start date
164 -- default end date to sysdate , this is taken care by the main program
165 Main
166 (
167 ERRBUF,
168 RETCODE,
169 p_start_date,
170 TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
171 p_truncate
172 );
173
174
175 /* Set the return code for SUCESS/WARNING/ERROR */
176
177 IF(retcode=0 AND g_warn_flag='Y') THEN
178 retcode := 1;
179 END IF;
180
181 IF(retcode=-1 OR g_retcode=-1) THEN
182 retcode := -1;
183 END IF;
184
185
186 /*
187 A generic line in the log file that requests the user to see the o/p file for
188 further info.
189 */
190 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
191 bil_bi_util_collection_pkg.writeLog
192 (
193 p_log_level => fnd_log.LEVEL_EVENT,
194 p_module => g_pkg || l_proc,
195 p_msg =>
196 ' If there have been errors, Please refer to the Concurrent program output file for more information '
197 );
198 END IF;
199
200 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
201 bil_bi_util_collection_pkg.writeLog(
202 p_log_level => fnd_log.LEVEL_PROCEDURE,
203 p_module => g_pkg || l_proc || ' end ',
204 p_msg => ' End of Procedure '|| l_proc);
205 END IF;
206
207
208 END;
209
210
211 PROCEDURE INCR_LOAD
212 (
213 ERRBUF IN OUT NOCOPY VARCHAR2 ,
214 RETCODE IN OUT NOCOPY VARCHAR2
215 ) IS
216
217
218 l_start_date DATE;
219 l_end_date DATE;
220 l_period_from DATE;
221 l_period_to DATE;
222 l_proc VARCHAR2(100);
223
224 BEGIN
225 /* initialization of variable */
226 G_request_id := 0;
227 G_appl_id := 0;
228 G_program_id := 0;
229 G_user_id := 0;
230 G_login_id := 0;
231 g_first_run :=FALSE;
232 g_truncate_flag := FALSE;
233 g_debug := FALSE;
234 g_conv_rate_cnt := 0;
235 g_degree :=1;
236 g_num_row_proc :=0;
237 g_fact_row_proc :=0;
238 g_obj_name := 'BIL_BI_FST_DTL_F';
239 g_missing_rates := 0;
240 g_missing_time := 0;
241 g_pkg := 'bil.patch.115.sql.BIL_BI_FST_DTL_F_PKG.';
242
243 l_start_date :=NULL;
244 l_end_date :=NULL;
245 l_period_from :=NULL;
246 l_period_to :=NULL;
247 l_proc := 'INCR_LOAD.';
248
249 /* end initialization of variable */
250
251 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
252 bil_bi_util_collection_pkg.writeLog(
253 p_log_level => fnd_log.LEVEL_PROCEDURE,
254 p_module => g_pkg || l_proc || ' begin ',
255 p_msg => ' Start of Procedure '|| l_proc);
256 END IF;
257
258
259
260 BIS_COLLECTION_UTILITIES.get_last_refresh_dates(
261 g_obj_name,
262 l_start_date,
263 l_end_date,
264 l_period_from,
265 l_period_to
266 );
267
268 g_mode := '';
269 g_first_run := FALSE;
270 g_truncate_flag := FALSE;
271
272 l_end_date := SYSDATE;
273 Main
274 (
275 ERRBUF,
276 RETCODE,
277 TO_CHAR(l_period_to, 'YYYY/MM/DD HH24:MI:SS'),
278 TO_CHAR(l_end_date,'YYYY/MM/DD HH24:MI:SS'),
279 'N'
280 );
281
282 /* Set the return code for SUCESS/WARNING/ERROR */
283
284 IF(retcode=0 AND g_warn_flag='Y') THEN
285 retcode := 1;
286 END IF;
287
288 IF(retcode=-1 OR g_retcode=-1) THEN
289 retcode := -1;
290 END IF;
291
292
293 /*
294 A generic line in the log file that requests the user to see the o/p file for
295 further info.
296 */
297 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
298 bil_bi_util_collection_pkg.writeLog
299 (
300 p_log_level => fnd_log.LEVEL_EVENT,
301 p_module => g_pkg || l_proc,
302 p_msg =>
303 ' If there have been errors, Please refer to the Concurrent program output file for more information '
304 );
305 END IF;
306
307 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
308 bil_bi_util_collection_pkg.writeLog(
309 p_log_level => fnd_log.LEVEL_PROCEDURE,
310 p_module => g_pkg || l_proc || ' end ',
311 p_msg => ' End of Procedure '|| l_proc);
312 END IF;
313
314
315 END INCR_LOAD;
316
317
318
319 -- ********************************************************************
320 -- ------------------------------------------------------------
321 -- Public Functions and Procedures
322 -- ------------------------------------------------------------
323
324 PROCEDURE Main ( ERRBUF IN OUT NOCOPY VARCHAR2
325 ,RETCODE IN OUT NOCOPY VARCHAR2
326 ,p_start_date IN VARCHAR2
327 ,p_end_date IN VARCHAR2
328 ,p_truncate IN VARCHAR2
329 ) IS
330
331 l_proc VARCHAR2(100);
332
333 l_ids_count NUMBER;
334 stg_count NUMBER;
335 l_asf_calendar VARCHAR2(100);
336
337 l_start_date DATE;
338 l_end_date DATE;
339 l_period_from DATE;
340 l_period_to DATE;
341 l_sysdate DATE;
342
343 l_setup_valid BOOLEAN;
344 l_setup_warn BOOLEAN;
345
346 l_stg_cnt NUMBER;
347
348 /* all temp variables used in resume only */
349
350 l_temp_first_run BOOLEAN;
351 l_temp_mode VARCHAR2(100);
352 l_temp_truncate_flag BOOLEAN;
353 l_temp_start_date DATE;
354 l_temp_end_date DATE;
355 l_temp_collect_mode VARCHAR2(200);
356
357 BEGIN
358
359 /* initialization of variable */
360 l_proc := 'Main.';
361
362 l_ids_count := 0;
363 stg_count := 0;
364
365 l_start_date :=NULL;
366 l_end_date :=NULL;
367 l_period_from :=NULL;
368 l_period_to :=NULL;
369 l_sysdate :=SYSDATE;
370
371 l_stg_cnt :=0;
372 /* end initialization of variable */
373
374 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
375 bil_bi_util_collection_pkg.writeLog(
376 p_log_level => fnd_log.LEVEL_PROCEDURE,
377 p_module => g_pkg || l_proc || ' begin ',
378 p_msg => ' Start of Procedure '|| l_proc);
379 END IF;
380
381 errbuf := NULL;
382 retcode := 0;
383
384 BIS_COLLECTION_UTILITIES.get_last_refresh_dates(
385 g_obj_name,
386 l_start_date,
387 l_end_date,
388 l_period_from,
389 l_period_to
390 );
391
392 IF (l_start_date IS NULL OR p_truncate='Y' OR p_truncate='y') THEN
393 g_first_run:=TRUE;
394 g_mode:= 'INITIAL';
395 END IF;
396
397
398 ------------------------------------------------
399 -- Initialize Global Variables
400 ------------------------------------------------
401 g_obj_name := 'BIL_BI_FST_DTL_F';
402 init(g_obj_name);
403
404 -- only set these for main parent and for initial run
405 IF(g_mode='INITIAL') THEN
406 EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE=100000000';
407 EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=100000000';
408 END IF;
409
410 -- chech for mode of operation, if full refresh, truncate tables
411 IF (g_mode='INITIAL') THEN
412 bil_bi_util_collection_pkg.Truncate_table('BIL_BI_FST_DTL_F');
413 bil_bi_util_collection_pkg.Truncate_table('BIL_BI_FST_DTL_STG');
414 bil_bi_util_collection_pkg.Truncate_table('BIL_BI_PROCESSED_FST_ID');
415 bil_bi_util_collection_pkg.Truncate_table('BIL_BI_NEW_FST_ID');
416 BIS_COLLECTION_UTILITIES.deleteLogForObject (g_obj_name);
417 g_truncate_flag := TRUE;
418 COMMIT;
419 END IF;
420
421
422 -- Set the STart and End Dates of This Run
423
424 IF(p_truncate = 'Y' or p_truncate= 'y') THEN
425 IF p_start_date IS NULL THEN
426 G_Start_Date := g_cal_start_date;
427 ELSIF (TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS') > l_sysdate) THEN
428 fnd_message.set_name('BIL','BIL_BI_DATE_PARAM_RESET');
429 fnd_message.set_token('RESET_DATE',
430 TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'));
431 bil_bi_util_collection_pkg.writeLog
432 (
433 p_log_level => fnd_log.LEVEL_ERROR,
434 p_module => g_pkg || l_proc ,
435 p_msg => fnd_message.get,
436 p_force_log => TRUE
437 );
438 g_start_date := l_sysdate;
439 g_warn_flag := 'Y';
440 ELSE
441 G_Start_Date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
442 END IF;
443 ELSE
444 IF p_start_date IS NULL THEN
445 G_Start_Date := g_cal_start_date;
446 ELSE
447 IF ((TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS') < l_period_to) AND p_truncate NOT IN ('y','Y')) THEN
448 fnd_message.set_name('BIL','BIL_BI_INVALID_DATE_RANGE');
449 bil_bi_util_collection_pkg.writeLog
450 (
451 p_log_level => fnd_log.LEVEL_ERROR,
452 p_module => g_pkg || l_proc ,
453 p_msg => fnd_message.get,
454 p_force_log => TRUE
455 );
456 g_start_date := l_period_to;
457 g_warn_flag := 'Y';
458 ELSIF ( TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS') > l_period_to ) THEN
459 fnd_message.set_name('BIL','BIL_BI_DATE_PARAM_RESET');
460 fnd_message.set_token('RESET_DATE',
461 TO_CHAR(l_period_to, 'YYYY/MM/DD HH24:MI:SS'));
462 bil_bi_util_collection_pkg.writeLog
463 (
464 p_log_level => fnd_log.LEVEL_ERROR,
465 p_module => g_pkg || l_proc ,
466 p_msg => FND_MESSAGE.get,
467 p_force_log => TRUE
468 );
469 g_start_date := l_period_to;
470 g_warn_flag := 'Y';
471 ELSE
472 G_Start_Date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
473 END IF;
474 END IF;
475 END IF;
476
477 G_End_Date := TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
478
479 -------------------------------------------------
480 -- Print out useful parameter information
481 -------------------------------------------------
482
483 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
484 bil_bi_util_collection_pkg.writeLog
485 (
486 p_log_level => fnd_log.LEVEL_EVENT,
487 p_module => g_pkg || l_proc || ' g_start_date ',
488 p_msg => ' Start date range: ' || to_char(g_start_date, 'YYYY/MM/DD HH24:MI:SS')
489 );
490
491 bil_bi_util_collection_pkg.writeLog
492 (
493 p_log_level => fnd_log.LEVEL_EVENT,
494 p_module => g_pkg || l_proc || ' g_end_date ',
495 p_msg => ' End date range: ' || to_char(g_end_date, 'YYYY/MM/DD HH24:MI:SS')
496 );
497
498 bil_bi_util_collection_pkg.writeLog
499 (
500 p_log_level => fnd_log.LEVEL_EVENT,
501 p_module => g_pkg || l_proc || ' p_truncate ',
502 p_msg => ' Truncate: ' || p_truncate
503 );
504
505 bil_bi_util_collection_pkg.writeLog
506 (
507 p_log_level => fnd_log.LEVEL_EVENT,
508 p_module => g_pkg || l_proc || ' g_mode ',
509 p_msg => ' Mode: ' || g_mode
510 );
511
512 END IF;
513
514 Validate_Setup(l_setup_valid);
515
516 IF (NOT l_setup_valid) THEN
517 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
518 bil_bi_util_collection_pkg.writeLog
519 (
520 p_log_level => fnd_log.LEVEL_EVENT,
521 p_module => g_pkg || l_proc || ' proc_error ',
522 p_msg => ' Initial setup validation failed',
523 p_force_log => TRUE
524 );
525 END IF;
526 RAISE G_SETUP_VALID_ERROR;
527 END IF;
528
529 SELECT
530 COUNT(*)
531 INTO
532 l_stg_cnt
533 FROM BIL_BI_NEW_FST_ID
534 WHERE ROWNUM < 2;
535
536 IF (l_stg_cnt > 0) THEN
537 G_Resume_Flag := 'Y';
538 END IF;
539
540 IF (g_resume_Flag = 'Y') THEN
541
542 g_phase := 'Resuming';
543
544 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
545 bil_bi_util_collection_pkg.writeLog(
546 p_log_level => fnd_log.LEVEL_EVENT,
547 p_module => g_pkg || l_proc,
548 p_msg => g_phase);
549 END IF;
550
551 /* The forecasts to resume are present in the new fst id table. Resume from there */
552
553 /*
554 -----------------------------------------------------------------------------
555 Determine the previous run type:
556 1.Initial with truncate = Y
557 2.Initial with truncate = N
558 3.Incremental
559
560 And then put current g_* variables in temporary variables and
561 set g_* with resume type and proceed to insert into the stg and pfi tables
562 -----------------------------------------------------------------------------
563 */
564
565 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
566 bil_bi_util_collection_pkg.writeLog
567 (
568 p_log_level => fnd_log.LEVEL_STATEMENT,
569 p_module => g_pkg || l_proc ,
570 p_msg =>
571 'g_* variables before exchange = g_end_date,g_start_date,g_mode,'||
572 ' => '||g_end_date||','||g_start_date||','||','||g_mode
573 );
574 END IF;
575
576 l_temp_first_run := g_first_run;
577 l_temp_mode := g_mode;
578 l_temp_truncate_flag := g_truncate_flag;
579 l_temp_start_date := g_start_date;
580 l_temp_end_date := g_end_date;
581
582
583 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
584 bil_bi_util_collection_pkg.writeLog
585 (
586 p_log_level => fnd_log.LEVEL_STATEMENT,
587 p_module => g_pkg || l_proc ,
588 p_msg =>
589 'temp variables after exchange = l_temp_end_date,l_temp_start_date,l_temp_mode,'||
590 l_temp_end_date||','||l_temp_start_date||','||','||l_temp_mode
591 );
592 END IF;
593
594
595 SELECT
596 MIN(submission_date) start_date,
597 MAX(submission_date) end_date,
598 MAX(collect_mode) collect_mode
599 INTO
600 g_start_date,
601 g_end_date,
602 l_temp_collect_mode
603 FROM
604 BIL_BI_NEW_FST_ID;
605
606 IF SUBSTR(l_temp_collect_mode,1,1) = 'Y' THEN
607 g_truncate_flag := TRUE;
608 ELSE
609 g_truncate_flag := FALSE;
610 END IF;
611
612
613 IF SUBSTR(l_temp_collect_mode,3,1) = 'Y' THEN
614 g_first_run := TRUE;
615 ELSE
616 g_first_run := FALSE;
617 END IF;
618
619
620 IF g_truncate_flag THEN
621 g_mode := 'INITIAL';
622 ELSE
623 g_mode := '';
624 END IF;
625
626
627 --------------------------------------------------------------
628 -- Populate currency rates with the dates and currency codes
629 -- in the new fst ids table
630 --------------------------------------------------------------
631
632 POPULATE_CURRENCY_RATE;
633
634 --------------------------------------------------------------
635 -- Check for missing currency, submission
636 -- and forecast time dimensions
637 --------------------------------------------------------------
638
639 SUMMARY_ERR_CHECK;
640
641 IF (g_missing_rates = 0 AND g_missing_time = 0) THEN
642
643 g_phase := 'Resume: Insert into staging';
644
645 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
646 bil_bi_util_collection_pkg.writeLog(
647 p_log_level => fnd_log.LEVEL_EVENT,
648 p_module => g_pkg || l_proc ,
649 p_msg => g_phase);
650 END IF;
651
652 Insert_into_Stg
653 (
654 g_mode
655 );
656
657 g_phase := 'Resume: adjust forecast amount for staging table';
658
659 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
660 bil_bi_util_collection_pkg.writeLog
661 (
662 p_log_level => fnd_log.LEVEL_EVENT,
663 p_module => g_pkg || l_proc ,
664 p_msg => g_phase
665 );
666 END IF;
667
668
669 Summary_Adjust;
670
671 -------------------------------------------------------------
672 -- Call Summarization_aggreagte forecast data along the forecast period
673 -- dimension
674 -------------------------------------------------------------
675
676 g_phase := 'Resume: Aggregating summarized data';
677
678 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
679 bil_bi_util_collection_pkg.writeLog(
680 p_log_level => fnd_log.LEVEL_EVENT,
681 p_module => g_pkg || l_proc ,
682 p_msg => g_phase);
683 END IF;
684
685 Summarize_Frcsts_Periods;
686
687 --------------------------------------------------------
688 -- Call Merge routine to insert validated data into
689 -- the base summary table
690 --------------------------------------------------------
691 g_phase := 'Resume:Merging records into base summary table';
692 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
693 bil_bi_util_collection_pkg.writeLog(
694 p_log_level => fnd_log.LEVEL_EVENT,
695 p_module => g_pkg || l_proc ,
696 p_msg => g_phase);
697 END IF;
698
699 Insert_From_Stg
700 (
701 ERRBUF => ERRBUF
702 ,RETCODE => RETCODE
703 );
704
705 --------------------------------------------------------
706 -- Call routine to insert validated data into
707 -- the processed id table.
708 --------------------------------------------------------
709 g_phase := 'RESUME: Completed insert into fact';
710
711
712 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
713 bil_bi_util_collection_pkg.writeLog(
714 p_log_level => fnd_log.LEVEL_EVENT,
715 p_module => g_pkg || l_proc ,
716 p_msg => g_phase);
717 END IF;
718 -- clean the table once resume is complete and commit
719 bil_bi_util_collection_pkg.truncate_table('BIL_BI_FST_DTL_STG');
720 bil_bi_util_collection_pkg.truncate_table('BIL_BI_NEW_FST_ID');
721 COMMIT;
722 -- end of new code
723 G_Resume_Flag := 'N';
724
725 /* Reassign all the g_* variables, their original values */
726
727 g_first_run := l_temp_first_run;
728 g_mode := l_temp_mode;
729 g_truncate_flag := l_temp_truncate_flag;
730 g_start_date := l_temp_start_date;
731 g_end_date := l_temp_end_date;
732
733 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
734 bil_bi_util_collection_pkg.writeLog
735 (
736 p_log_level => fnd_log.LEVEL_STATEMENT,
737 p_module => g_pkg || l_proc ,
738 p_msg =>
739 'g_* variables after exchange = g_end_date,g_start_date,g_mode,'||
740 'g_first_run => '||g_end_date||','||g_start_date||','||g_mode
741 );
742 END IF;
743
744
745 ELSE
746
747 /* if sumry err check returned missing date/curr*/
748
749 retcode := - 1;
750 g_phase := 'MISSING RATES FOUND WHILE RESUMING';
751 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
752 bil_bi_util_collection_pkg.writeLog
753 (
754 p_log_level => fnd_log.LEVEL_EVENT,
755 p_module => g_pkg || l_proc ,
756 p_msg => g_phase
757 );
758 END IF;
759 commit;
760 Clean_up('Missing currencies or dimension values');
761 return;
762 END IF;
763 ELSE
764 G_Resume_Flag := 'N';
765 END IF;
766
767 -----------------------------------------------------------------
768 -- If resume flag is 'N', then this program starts from the
769 -- beginning:
770 -- 1. Identify Forecasts IDs to process
771 -- 2. insert day-level summarized
772 -- records into temporary staging table
773 -- Otherwise, it would first check if all missing rates have been
774 -- fixed, and then resume the normal process which includes:
775 -- 3. Insert higher time level summarized records into
776 -- temporary staging table.
777 -- 4. Merging summarized records into base summary table
778 -- 5. Insert processed Header IDs into a processed table
779 ------------------------------------------------------------------
780 IF(g_resume_flag = 'N') THEN
781 ---------------------------------------------------------------
782 -- Call New_Forecasts routine to insert Forecasts ids into
783 -- BIL_BI_FRCST_TEMP
784 ----------------------------------------------------------------
785
786 g_phase := 'Identify New Forecasts to process';
787 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
788 bil_bi_util_collection_pkg.writeLog(
789 p_log_level => fnd_log.LEVEL_EVENT,
790 p_module => g_pkg || l_proc ,
791 p_msg => g_phase);
792 END IF;
793
794 --------------------------------------------------------
795 -- New_Forecasts will identify the new Forecasts which
796 -- need to be processed based on the user entered
797 -- date range. If there are no new journals to process
798 -- the program will exit immediately with complete
799 -- successful status
800 --------------------------------------------------------
801 /*Fetch the range of forecasts into new fst id table that needs to be processed. */
802 l_ids_count := New_Forecasts (g_start_date, g_end_date);
803
804 IF (l_ids_count = 0) THEN
805 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
806 bil_bi_util_collection_pkg.writeLog
807 (
808 p_log_level => fnd_log.LEVEL_EVENT,
809 p_module => g_pkg || l_proc ,
810 p_msg => 'No new forecasts to process, exiting.'
811 );
812 END IF;
813 COMMIT;
814 BIS_COLLECTION_UTILITIES.wrapup(TRUE, 0, NULL, g_start_date, g_end_date);
815 RETURN;
816 END IF;
817
818 COMMIT;
819
820 g_phase := 'going to populate currency rates';
821 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
822 bil_bi_util_collection_pkg.writeLog
823 (
824 p_log_level => fnd_log.LEVEL_EVENT,
825 p_module => g_pkg || l_proc,
826 p_msg =>g_phase
827 );
828 END IF;
829
830 --------------------------------------------------------------
831 -- Populate currency rates with the dates and currency codes
832 -- in the new fst ids table
833 --------------------------------------------------------------
834
835 POPULATE_CURRENCY_RATE;
836
837
838 g_phase := 'Doing summary error check';
839
840 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
841 bil_bi_util_collection_pkg.writeLog
842 (
843 p_log_level => fnd_log.LEVEL_EVENT,
844 p_module => g_pkg || l_proc,
845 p_msg =>g_phase
846 );
847 END IF;
848
849
850 --------------------------------------------------------------
851 -- Check for missing currency, submission
852 -- and forecast time dimensions
853 --------------------------------------------------------------
854
855 SUMMARY_ERR_CHECK;
856
857
858 -----------------------------------------------------------------
859 -- If completed successfully then we cab proceed with inserting
860 -- into the stg and processed ids table
861 -----------------------------------------------------------------
862
863 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
864 bil_bi_util_collection_pkg.writeLog(
865 p_log_level => fnd_log.LEVEL_EVENT,
866 p_module => g_pkg || l_proc ,
867 p_msg => 'Summarization Error Check completed');
868 END IF;
869
870 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
871 bil_bi_util_collection_pkg.writeLog
872 (
873 p_log_level => fnd_log.LEVEL_EVENT,
874 p_module => g_pkg || l_proc ,
875 p_msg => 'g_missing_rates= ' || g_missing_rates ||' g_missing_time= ' || g_missing_time
876 );
877 END IF;
878
879 IF (g_missing_rates = 0 AND g_missing_time = 0) THEN
880
881 Insert_into_Stg
882 (
883 g_mode
884 );
885
886 COMMIT;
887
888 -----------------------------------------------------------------------------------
889 -- Call summary adjust
890 -- if INITIAL WITH TRUNCATE then only check for correct periods
891 -- and update the forecast period column
892 -- for INITIAL WITHOUT TRUNCATE AND INCR do the above as well as adjust amounts
893 -----------------------------------------------------------------------------------
894
895
896 g_phase := ' Calling SUMMARY ADJUST - check for periods and adjust amount(incr)';
897 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
898 bil_bi_util_collection_pkg.writeLog
899 (
900 p_log_level => fnd_log.LEVEL_EVENT,
901 p_module => g_pkg || l_proc ,
902 p_msg => g_phase
903 );
904 END IF;
905
906 Summary_Adjust;
907
908 -------------------------------------------------------------
909 -- Call Summarization_aggreagte forecast data along the forecast period
910 -- dimension
911 -------------------------------------------------------------
912
913 g_phase := 'Aggregating summarized data';
914 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
915 bil_bi_util_collection_pkg.writeLog(
916 p_log_level => fnd_log.LEVEL_EVENT,
917 p_module => g_pkg || l_proc ,
918 p_msg => g_phase);
919 END IF;
920
921
922 Summarize_Frcsts_Periods;
923
924 --------------------------------------------------------
925 -- Call Merge routine to insert validated data into
926 -- the base summary table
927 --------------------------------------------------------
928
929 g_phase := 'Merging records into base summary table';
930 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
931 bil_bi_util_collection_pkg.writeLog(
932 p_log_level => fnd_log.LEVEL_EVENT,
933 p_module => g_pkg || l_proc ,
934 p_msg => g_phase);
935 END IF;
936
937 Insert_From_Stg
938 (
939 ERRBUF => ERRBUF
940 ,RETCODE => RETCODE
941 );
942
943
944 ----------------------------------------------------------------------
945 -- Call routine to insert validated data into the processed id table.
946 ----------------------------------------------------------------------
947
948 g_phase := 'truncate new fst id table after a successful colelction';
949 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
950 bil_bi_util_collection_pkg.writeLog(
951 p_log_level => fnd_log.LEVEL_EVENT,
952 p_module => g_pkg || l_proc ,
953 p_msg => g_phase);
954 END IF;
955
956 /* truncate the new fst ids table after a successful collection */
957 bil_bi_util_collection_pkg.Truncate_table('BIL_BI_NEW_FST_ID');
958
959
960 COMMIT;
961
962 -- Cleaning phase
963 -- Truncate staging summary table if all the processes completed
964 -- successfully.
965 g_phase := 'Final Cleanup';
966
967 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
968 bil_bi_util_collection_pkg.writeLog(
969 p_log_level => fnd_log.LEVEL_EVENT,
970 p_module => g_pkg || l_proc ,
971 p_msg => g_phase);
972 END IF;
973 Clean_up(NULL);
974 IF (g_truncate_flag or g_first_run) THEN
975 bil_bi_util_collection_pkg.analyze_table('BIL_BI_FST_DTL_F', TRUE, 10, 'GLOBAL');
976 END IF;
977 retcode := 0;
978 ELSE
979 -- don't move a record to base summary table in case of error but still commit
980 retcode := - 1;
981 g_phase:='Summarization Error Check Positive';
982 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
983 bil_bi_util_collection_pkg.writeLog
984 (
985 p_log_level => fnd_log.LEVEL_EVENT,
986 p_module => g_pkg || l_proc ,
987 p_msg => g_phase
988 );
989 END IF;
990 Clean_up('Missing currencies or dimension values');
991 END IF;
992 END IF; -- IF (g_resume_flag = 'N')
993
994
995
996 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
997 bil_bi_util_collection_pkg.writeLog(
998 p_log_level => fnd_log.LEVEL_STATEMENT,
999 p_module => g_pkg || l_proc ,
1000 p_msg => 'Warn Flag := ' || retcode);
1001 END IF;
1002
1003 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1004 bil_bi_util_collection_pkg.writeLog(
1005 p_log_level => fnd_log.LEVEL_PROCEDURE,
1006 p_module => g_pkg || l_proc || ' end ',
1007 p_msg => ' End of Procedure ' || l_proc);
1008 END IF;
1009
1010
1011 EXCEPTION
1012
1013 WHEN G_SETUP_VALID_ERROR THEN
1014 g_retcode := -1;
1015 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1016 bil_bi_util_collection_pkg.writeLog
1017 (
1018 p_log_level => fnd_log.LEVEL_EVENT,
1019 p_module => g_pkg || l_proc || ' proc_error',
1020 p_msg => 'The Time, Currency or Product Dimensions are not properly setup ',
1021 p_force_log => TRUE
1022 );
1023 END IF;
1024 ROLLBACK;
1025 retcode := g_retcode;
1026 Clean_up(sqlerrm);
1027
1028 WHEN OTHERS Then
1029 g_retcode := -1;
1030 ROLLBACK;
1031 retcode := g_retcode;
1032 Clean_up(sqlerrm);
1033 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1034 fnd_message.set_token('ERRNO' ,SQLCODE);
1035 fnd_message.set_token('REASON' ,SQLERRM);
1036 fnd_message.set_token('ROUTINE' , l_proc);
1037 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
1038 p_module => g_pkg || l_proc || 'proc_error',
1039 p_msg => fnd_message.get,
1040 p_force_log => TRUE);
1041 RAISE;
1042 END Main;
1043 /************************************************************************************************/
1044
1045
1046
1047
1048 ----------------------------------------------------------------------
1049 -- FUNCTION NEW_JOURNALS
1050 ----------------------------------------------------------------------
1051 FUNCTION New_Forecasts
1052 (
1053 P_Start_Date IN DATE ,
1054 P_End_Date IN DATE
1055 ) RETURN NUMBER IS
1056
1057 l_number_of_rows NUMBER;
1058 l_proc VARCHAR2(100);
1059 l_collect_mode VARCHAR2(100);
1060
1061 BEGIN
1062
1063 /* initialization of variable */
1064 l_number_of_rows :=0;
1065 l_proc := 'New_forecasts.';
1066 /* end initialization of variable */
1067
1068 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1069 bil_bi_util_collection_pkg.writeLog(
1070 p_log_level => fnd_log.LEVEL_PROCEDURE,
1071 p_module => g_pkg || l_proc || ' begin ',
1072 p_msg => ' Start of Procedure ' || l_proc);
1073 END IF;
1074
1075 ----------------------------------------------------------------------
1076 -- Insert into a table to hold forecast ids which are never
1077 -- processed (Not exist processed id table.
1078 -----------------------------------------------------------------------
1079 IF g_truncate_flag THEN
1080 l_collect_mode := 'Y';
1081 ELSE
1082 l_collect_mode := 'N';
1083 END IF;
1084
1085 IF g_first_run THEN
1086 l_collect_mode := l_collect_mode||':Y';
1087 ELSE
1088 l_collect_mode := l_collect_mode||':N';
1089 END IF;
1090
1091
1092 IF (g_mode='INITIAL') THEN
1093
1094 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
1095 bil_bi_util_collection_pkg.writeLog
1096 (
1097 p_log_level => fnd_log.LEVEL_STATEMENT,
1098 p_module => g_pkg || l_proc,
1099 p_msg => 'Inserting in INITIAL mode'
1100 );
1101 END IF;
1102
1103
1104
1105 INSERT /*+ PARALLEL(nfst) */ INTO BIL_BI_NEW_FST_ID nfst
1106 (
1107 record_id,
1108 forecast_id,
1109 currency_code,
1110 submission_date,
1111 collect_mode,
1112 period_name
1113 )
1114 SELECT /*+ USE_HASH(aif) PARALLEL(aif) PARALLEL(glp) */
1115 rownum,
1116 aif.forecast_id,
1117 aif.currency_code,
1118 aif.submission_date,
1119 l_collect_mode,
1120 aif.period_name
1121 FROM
1122 as_internal_forecasts aif,
1123 gl_periods glp
1124 WHERE
1125 aif.submission_date BETWEEN p_start_date AND p_end_date
1126 AND aif.status_code = 'SUBMITTED'
1127 AND glp.period_set_name = g_cal
1128 AND glp.period_name = aif.period_name
1129 AND glp.period_type = g_fsct_per_type;
1130
1131 l_number_of_rows := SQL%ROWCOUNT;
1132
1133 ELSE
1134
1135 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
1136 bil_bi_util_collection_pkg.writeLog
1137 (
1138 p_log_level => fnd_log.LEVEL_STATEMENT,
1139 p_module => g_pkg || l_proc,
1140 p_msg => 'Inserting in Incremental mode'
1141 );
1142 END IF;
1143
1144
1145 INSERT INTO BIL_BI_NEW_FST_ID
1146 (
1147 record_id,
1148 forecast_id,
1149 currency_code,
1150 submission_date,
1151 collect_mode,
1152 period_name
1153 )
1154 SELECT
1155 ROWNUM,
1156 aif.forecast_id,
1157 aif.currency_code,
1158 aif.submission_date,
1159 l_collect_mode,
1160 aif.period_name
1161 FROM
1162 as_internal_forecasts aif,
1163 gl_periods glp
1164 WHERE
1165 NOT EXISTS (SELECT forecast_id FROM bil_bi_processed_fst_id bpfi
1166 WHERE aif.forecast_id = bpfi.forecast_id)
1167 AND aif.submission_date BETWEEN P_Start_Date AND P_End_Date
1168 AND aif.status_code = 'SUBMITTED'
1169 AND glp.period_set_name = g_cal
1170 AND glp.period_name = aif.period_name
1171 AND glp.period_type = g_fsct_per_type;
1172
1173
1174
1175 l_number_of_rows := SQL%ROWCOUNT;
1176
1177 END IF;
1178
1179
1180
1181 COMMIT;
1182
1183 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1184 bil_bi_util_collection_pkg.writeLog(
1185 p_log_level => fnd_log.LEVEL_PROCEDURE,
1186 p_module => g_pkg || l_proc,
1187 p_msg => 'Inserted '||l_number_of_rows||' forecast IDs into BIL_BI_NEW_FST_ID');
1188 END IF;
1189
1190 bil_bi_util_collection_pkg.analyze_table('BIL_BI_NEW_FST_ID', TRUE, 10, 'GLOBAL');
1191
1192 return(l_number_of_rows);
1193
1194 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1195 bil_bi_util_collection_pkg.writeLog(
1196 p_log_level => fnd_log.LEVEL_PROCEDURE,
1197 p_module => g_pkg || l_proc || ' end ',
1198 p_msg => ' End of Procedure ' || l_proc);
1199 END IF;
1200
1201
1202 EXCEPTION
1203
1204 WHEN OTHERS Then
1205 g_retcode := -1;
1206 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1207 fnd_message.set_token('ERRNO' ,SQLCODE);
1208 fnd_message.set_token('REASON', SQLERRM);
1209 fnd_message.set_token('ROUTINE' , l_proc);
1210 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
1211 p_module => g_pkg || l_proc || ' proc_error ',
1212 p_msg => fnd_message.get,
1213 p_force_log => TRUE);
1214 RAISE;
1215
1216 END New_Forecasts;
1217
1218
1219 /**************************************************************************************
1220 *Init: This procedure is used to intialize the global variables viz- the values for
1221 *who columns.
1222 **************************************************************************************/
1223 PROCEDURE Init (p_obj_name in varchar2) IS
1224 l_status VARCHAR2(30);
1225 l_industry VARCHAR2(30);
1226 l_proc VARCHAR2(100);
1227 l_ret_status BOOLEAN;
1228 BEGIN
1229
1230 /* initialization of variable */
1231 l_proc := 'Init.';
1232 /* end initialization of variable */
1233
1234 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1235 bil_bi_util_collection_pkg.writeLog(
1236 p_log_level => fnd_log.LEVEL_PROCEDURE,
1237 p_module => g_pkg || l_proc || ' begin ',
1238 p_msg => ' Start of Procedure ' || l_proc);
1239 END IF;
1240
1241
1242 IF (NOT BIS_COLLECTION_UTILITIES.setup(p_obj_name)) THEN
1243
1244 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1245 bil_bi_util_collection_pkg.writeLog(
1246 p_log_level => fnd_log.LEVEL_EVENT,
1247 p_module => g_pkg || l_proc,
1248 p_msg => ' BIS Setup Failed');
1249 END IF;
1250 RAISE G_INIT_FAILED;
1251 END IF;
1252
1253
1254 g_debug := BIS_COLLECTION_UTILITIES.g_debug;
1255
1256
1257 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1258 bil_bi_util_collection_pkg.writeLog(
1259 p_log_level => fnd_log.LEVEL_EVENT,
1260 p_module => g_pkg || l_proc,
1261 p_msg => ' Calling procedure: INIT');
1262 END IF;
1263
1264
1265 Check_Profiles(l_ret_status);
1266
1267
1268 IF (NOT l_ret_status) THEN
1269 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1270 bil_bi_util_collection_pkg.writeLog
1271 (
1272 p_log_level => fnd_log.LEVEL_EVENT,
1273 p_module => g_pkg || l_proc ,
1274 p_msg => ' Profiles have not been setup',
1275 p_force_log => TRUE);
1276 END IF;
1277 RAISE G_SETUP_VALID_ERROR;
1278 ELSE
1279 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1280 bil_bi_util_collection_pkg.writeLog
1281 (
1282 p_log_level => fnd_log.LEVEL_EVENT,
1283 p_module => g_pkg || l_proc ,
1284 p_msg => ' Profiles setup properly',
1285 p_force_log => TRUE
1286 );
1287 END IF;
1288 END IF;
1289
1290 ----------------------------------------------------------------
1291 -- Set program start time. We need this variable to delete
1292 -- records inserted into staging table in this run. Any records
1293 -- with creation date greater than program start time will be
1294 -- deleted in the event of error
1295 ----------------------------------------------------------------
1296 g_program_start_time := SYSDATE;
1297
1298 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1299
1300 bil_bi_util_collection_pkg.writeLog(
1301 p_log_level => fnd_log.LEVEL_EVENT,
1302 p_module => g_pkg || l_proc ,
1303 p_msg => 'Initialize global variables');
1304 END IF;
1305
1306
1307 g_phase := 'Find BIL schema';
1308 g_bil_schema := bil_bi_util_collection_pkg.get_schema_name('BIL');
1309
1310 G_request_id := FND_GLOBAL.CONC_REQUEST_ID();
1311 G_appl_id := FND_GLOBAL.PROG_APPL_ID();
1312 G_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
1313 G_user_id := FND_GLOBAL.USER_ID();
1314 G_login_id := FND_GLOBAL.CONC_LOGIN_ID();
1315
1316 G_DEGREE := NVL(bis_common_parameters.get_degree_of_parallelism,1);
1317
1318 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1319 bil_bi_util_collection_pkg.writeLog(
1320 p_log_level => fnd_log.LEVEL_PROCEDURE,
1321 p_module => g_pkg || l_proc || ' end ',
1322 p_msg => ' End of Procedure ' || l_proc);
1323 END IF;
1324
1325
1326 END Init;
1327
1328
1329
1330
1331 /****************************************************************************************
1332 --PROCEDURE Populate_Currency_Rate
1333 --This procedure populates the currency rate table with distinct combinations
1334 --of currency codes and dates
1335 --it gets the distinct combinations from the forecast staging table
1336 ****************************************************************************************/
1337
1338 PROCEDURE Populate_Currency_Rate IS
1339 l_proc VARCHAR2(100);
1340
1341 BEGIN
1342
1343 /* initialization of variable */
1344 l_proc := 'Populate_Currency_Rate.';
1345 /* end initialization of variable */
1346
1347 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1348 bil_bi_util_collection_pkg.writeLog(
1349 p_log_level => fnd_log.LEVEL_PROCEDURE,
1350 p_module => g_pkg || l_proc || ' begin',
1351 p_msg => 'Start of Procedure '|| l_proc);
1352 END IF;
1353
1354
1355 MERGE INTO BIL_BI_CURRENCY_RATE sumry
1356 USING
1357 (
1358 SELECT
1359 currency_code,
1360 submission_date,
1361 DECODE(currency_code,g_prim_currency,1,
1362 fii_currency.get_global_rate_primary(currency_code,trunc(least(sysdate, submission_date)))) prate,
1363 DECODE(g_sec_currency,NULL,NULL,DECODE(currency_code,g_sec_currency,1,
1364 fii_currency.get_global_rate_secondary(currency_code,trunc(least(sysdate, submission_date))))) srate
1365 FROM
1366 (
1367 SELECT /*+ PARALLEL(nfi) */
1368 DISTINCT currency_code currency_code,
1369 TRUNC(submission_date) submission_date
1370 FROM
1371 bil_bi_new_fst_id nfi
1372 )
1373 ) rates
1374 ON
1375 (
1376 rates.currency_code = sumry.currency_code
1377 AND rates.submission_date = sumry.exchange_date
1378 )
1379 WHEN MATCHED THEN
1380 UPDATE SET
1381 sumry.exchange_rate = rates.prate,
1382 sumry.EXCHANGE_RATE_S = rates.srate
1383 WHEN NOT MATCHED THEN
1384 INSERT
1385 (
1386 sumry.currency_code,
1387 sumry.exchange_date,
1388 sumry.exchange_rate,
1389 sumry.exchange_rate_s
1390 )
1391 VALUES
1392 (
1393 rates.currency_code,
1394 rates.submission_date,
1395 rates.prate,
1396 rates.srate
1397 );
1398
1399 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1400 bil_bi_util_collection_pkg.writeLog(
1401 p_log_level => fnd_log.LEVEL_EVENT,
1402 p_module => g_pkg || l_proc ,
1403 p_msg => 'Merged '||sql%rowcount||' into bil_bi_currency_rate table');
1404 END IF;
1405
1406 --update bil.bil_bi_currency_rate set exchange_rate = -1,exchange_rate_s = -1 where rownum < 2;
1407
1408 COMMIT;
1409
1410 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1411 bil_bi_util_collection_pkg.writeLog(
1412 p_log_level => fnd_log.LEVEL_PROCEDURE,
1413 p_module => g_pkg || l_proc || ' end',
1414 p_msg => 'End of Procedure '|| l_proc);
1415 END IF;
1416 EXCEPTION
1417 WHEN OTHERS THEN
1418
1419 g_retcode := -1;
1420 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1421 fnd_message.set_token('ERRNO' ,SQLCODE);
1422 fnd_message.set_token('REASON' ,SQLERRM);
1423 fnd_message.set_token('ROUTINE' , l_proc);
1424 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
1425 p_module => g_pkg || l_proc || 'proc_error',
1426 p_msg => fnd_message.get,
1427 p_force_log => TRUE);
1428 RAISE;
1429 END Populate_Currency_Rate;
1430
1431
1432
1433
1434 /**************************************************************************************
1435 * PROCEDURE Insert_Into_Stg
1436 * This procedure is used to insert a range of forecasts in to the staging table.
1437 **************************************************************************************/
1438
1439 PROCEDURE Insert_Into_Stg
1440 (
1441 p_mode IN VARCHAR2
1442 ) IS
1443
1444 l_number_of_rows NUMBER;
1445 l_stime DATE;
1446 l_proc varchar2(100);
1447
1448
1449 BEGIN
1450
1451 /* initialization of variable */
1452 l_number_of_rows :=0;
1453 l_stime := SYSDATE;
1454 l_proc := 'Insert_Into_Stg.';
1455 /* end initialization of variable */
1456
1457 ------------------------------------------------------------------
1458 -- Insert Forecasts in the given range from AS_INTERNAL_FORECASTS,
1459 -- AS_FST_SALES_CATEGORIES and AS_FORECAST_CATEGORIES.
1460 ------------------------------------------------------------------
1461
1462 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1463 bil_bi_util_collection_pkg.writeLog(
1464 p_log_level => fnd_log.LEVEL_PROCEDURE,
1465 p_module => g_pkg || l_proc || ' begin ',
1466 p_msg => ' Start of Procedure ' || l_proc);
1467 END IF;
1468
1469 IF (p_mode='INITIAL') THEN
1470
1471 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1472 bil_bi_util_collection_pkg.writeLog
1473 (
1474 p_log_level => fnd_log.LEVEL_EVENT,
1475 p_module => g_pkg || l_proc,
1476 p_msg => 'insert into staging - initial'
1477 );
1478 END IF;
1479
1480
1481 INSERT ALL
1482 /*+ PARALLEL(bil_bi_fst_dtl_stg) */ INTO BIL_BI_FST_DTL_STG
1483 (
1484 Txn_Day,Txn_Week,Txn_Period,Txn_Quarter,Txn_Year
1485 ,forecast_period_day,forecast_period_week,forecast_period_period,forecast_period_quarter,forecast_period_year
1486 ,sales_group_id,salesrep_id,forecast_amt,forecast_amt_s,valid_flag,functional_currency
1487 ,Primary_Conversion_Rate,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
1488 opp_forecast_amt,opp_forecast_amt_s
1489 )
1490 VALUES
1491 (
1492 Txn_Day,Txn_Week,Txn_Period,Txn_Quarter,Txn_Year
1493 ,forecast_period_day,forecast_period_week,forecast_period_Period,forecast_period_quarter,forecast_period_year
1494 ,sales_group_id,salesrep_id,adjusted_amt_p,adjusted_amt_s,valid_flag,functional_currency
1495 ,primary_Conversion_Rate,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
1496 adjusted_opp_forecast_amt_p,adjusted_opp_forecast_amt_s
1497 )
1498 /*+ PARALLEL(bil_bi_processed_fst_id) */ INTO bil_bi_processed_fst_id
1499 (
1500 creation_date,created_by,last_update_date,last_updated_by,LAST_UPDATE_LOGIN,Txn_Day
1501 ,sales_group_id,salesrep_id,forecast_amt,forecast_amt_s,functional_currency
1502 ,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
1503 opp_forecast_amt,opp_forecast_amt_s
1504 )
1505 VALUES
1506 (
1507 SYSDATE,G_user_id,SYSDATE,G_user_id,g_login_id,Txn_Day
1508 ,sales_group_id,salesrep_id,forecast_amt_p,forecast_amt_s,functional_currency
1509 ,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
1510 opp_forecast_amt_p,opp_forecast_amt_s
1511 )
1512 (
1513 SELECT
1514 /*+ PARALLEL(aif) PARALLEL(bnfi) PARALLEL(asfc)
1515 USE_HASH(aif) USE_HASH(bnfi) USE_HASH(asfc) */
1516 to_char(submission_date, 'J') txn_DAY,
1517 to_number(NULL, 999) txn_WEEK,
1518 to_number(NULL, 999) txn_PERIOD,
1519 to_number(NULL, 999) txn_QUARTER,
1520 to_number(NULL, 999) txn_YEAR,
1521 to_number(NULL,999) forecast_period_day,
1522 to_number(NULL,999)forecast_period_week,
1523 to_number(NULL,999)forecast_period_period,
1524 to_number(NULL,999)forecast_period_quarter,
1525 to_number(NULL,999)forecast_period_year,
1526 sales_group_id,
1527 salesforce_id salesrep_id,
1528 forecast_amt_p,
1529 forecast_amt_s,
1530 adjusted_amt_p,
1531 adjusted_amt_s,
1532 'T' valid_flag,
1533 currency_code functional_currency,
1534 NULL primary_conversion_rate,
1535 product_category_id,
1536 credit_type_id,
1537 period_name,
1538 submission_date,
1539 forecast_id,
1540 opp_forecast_amt_p,
1541 opp_forecast_amt_s,
1542 adjusted_opp_forecast_amt_p,
1543 adjusted_opp_forecast_amt_s
1544 FROM
1545 (
1546 SELECT
1547 forecast_id,
1548 submission_date,
1549 sales_group_id,
1550 salesforce_id,
1551 product_category_id,
1552 period_name,
1553 currency_code,
1554 credit_type_id,
1555 forecast_amount,
1556 forecast_amt_p,
1557 forecast_amt_s,
1558 forecast_amt_p-NVL(lag_forecast_amt_p,0) adjusted_amt_p,
1559 forecast_amt_s-NVL(lag_forecast_amt_s,0) adjusted_amt_s,
1560 opp_forecast_amt_p,
1561 opp_forecast_amt_s,
1562 opp_forecast_amt_p-NVL(lag_opp_forecast_amt_p,0) adjusted_opp_forecast_amt_p,
1563 opp_forecast_amt_s-NVL(lag_opp_forecast_amt_s,0) adjusted_opp_forecast_amt_s
1564 FROM
1565 (
1566 SELECT
1567 forecast_id,
1568 submission_date,
1569 sales_group_id,
1570 salesforce_id,
1571 product_category_id,
1572 period_name,
1573 currency_code,
1574 credit_type_id,
1575 forecast_amount,
1576 forecast_amt_p,
1577 forecast_amt_s,
1578 opp_forecast_amt_p,
1579 opp_forecast_amt_s,
1580 LAG((forecast_amt_p)) OVER (PARTITION BY sales_group_id,salesforce_id,product_category_id
1581 ,period_name,credit_type_id ORDER BY submission_date ASC) lag_forecast_amt_p,
1582 LAG((forecast_amt_s)) OVER (PARTITION BY sales_group_id,salesforce_id,product_category_id
1583 ,period_name,credit_type_id ORDER BY submission_date ASC) lag_forecast_amt_s,
1584 LAG((opp_forecast_amt_p)) OVER (PARTITION BY sales_group_id,salesforce_id,product_category_id
1585 ,period_name,credit_type_id ORDER BY submission_date ASC) lag_opp_forecast_amt_p,
1586 LAG((opp_forecast_amt_s)) OVER (PARTITION BY sales_group_id,salesforce_id,product_category_id
1587 ,period_name,credit_type_id ORDER BY submission_date ASC) lag_opp_forecast_amt_s
1588 FROM
1589 (
1590 SELECT
1591 /*+
1592 PARALLEL(aif) PARALLEL(bnfi) PARALLEL(asfc) PARALLEL(rates)
1593 USE_HASH(aif) USE_HASH(bnfi) USE_HASH(asfc) USE_HASH(rates)
1594 */
1595 aif.forecast_id,
1596 aif.submission_date,
1597 aif.sales_group_id,
1598 aif.salesforce_id,
1599 asfc.product_category_id,
1600 aif.period_name,
1601 aif.currency_code,
1602 aif.credit_type_id,
1603 aif.forecast_amount,
1604 aif.forecast_amount*rates.exchange_rate forecast_amt_p,
1605 aif.forecast_amount*rates.exchange_rate_s forecast_amt_s,
1606 NULL opp_forecast_amt_p,
1607 NULL opp_forecast_amt_s
1608 FROM
1609 as_internal_forecasts aif,
1610 bil_bi_new_fst_id bnfi,
1611 as_fst_sales_categories asfc,
1612 bil_bi_currency_rate rates
1613 WHERE
1614 aif.forecast_id = bnfi.forecast_id
1615 AND aif.status_code = 'SUBMITTED'
1616 AND aif.submission_date >= g_start_date
1617 AND aif.submission_date <= LEAST(g_end_date,(g_asn_date-(1/(24*60*60))))
1618 AND NVL(aif.FORECAST_AMOUNT_FLAG,'Y') = 'Y'
1619 AND aif.forecast_category_id = asfc.forecast_category_id
1620 AND NVL(asfc.end_date_active,SYSDATE) >= SYSDATE
1621 AND asfc.start_date_active <= SYSDATE
1622 AND TRUNC(aif.submission_date) = rates.exchange_date
1623 AND aif.currency_code = rates.currency_code
1624 AND product_category_id IS NOT NULL
1625 AND aif.forecast_category_id IN
1626 (
1627 SELECT
1628 afsc1.forecast_category_id
1629 FROM
1630 as_fst_sales_categories afsc1
1631 WHERE
1632 NVL(afsc1.end_date_active,SYSDATE) >= SYSDATE
1633 AND afsc1.start_date_active <= SYSDATE
1634 AND NOT(NVL(interest_type_id,-1)<0 AND product_category_id IS NULL)
1635 GROUP BY
1636 afsc1.forecast_category_id
1637 HAVING COUNT(1) = 1
1638 )
1639 UNION ALL
1640 SELECT
1641 /*+
1642 PARALLEL(aif) PARALLEL(bnfi) PARALLEL(asfc) PARALLEL(apwsl)
1643 USE_HASH(aif) USE_HASH(bnfi) USE_HASH(asfc) USE_HASH(apwsl)
1644 */
1645 aif.forecast_id,
1646 aif.submission_date,
1647 aif.sales_group_id,
1648 aif.salesforce_id,
1649 apwsl.product_category_id,
1650 aif.period_name,
1651 aif.currency_code,
1652 aif.credit_type_id,
1653 apwsl.forecast_amount,
1654 apwsl.forecast_amount*rates.exchange_rate forecast_amt_p,
1655 apwsl.forecast_amount*rates.exchange_rate_s forecast_amt_s,
1656 apwsl.opp_forecast_amount*rates.exchange_rate opp_forecast_amt_p,
1657 apwsl.opp_forecast_amount*rates.exchange_rate_s opp_forecast_amt_s
1658 FROM
1659 as_internal_forecasts aif,
1660 as_prod_worksheet_lines apwsl,
1661 bil_bi_new_fst_id bnfi,
1662 bil_bi_currency_rate rates
1663 WHERE
1664 aif.forecast_id = bnfi.forecast_id
1665 AND aif.status_code = 'SUBMITTED'
1666 AND apwsl.status_code = 'SUBMITTED'
1667 AND aif.submission_date >= GREATEST(g_start_date,g_asn_date)
1668 AND aif.submission_date <= g_end_date
1669 AND nvl(aif.FORECAST_AMOUNT_FLAG,'Y') = 'Y'
1670 AND aif.forecast_id = apwsl.forecast_id
1671 AND TRUNC(aif.submission_date) = rates.exchange_date
1672 AND aif.currency_code = rates.currency_code
1673 )
1674 )
1675 )
1676 );
1677
1678 l_number_of_rows := SQL%ROWCOUNT;
1679
1680 COMMIT;
1681
1682
1683 ELSE -- Incremental Load
1684
1685 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1686 bil_bi_util_collection_pkg.writeLog
1687 (
1688 p_log_level => fnd_log.LEVEL_EVENT,
1689 p_module => g_pkg || l_proc,
1690 p_msg => 'insert into staging - incremental'
1691 );
1692 END IF;
1693
1694 INSERT ALL
1695 INTO BIL_BI_FST_DTL_STG
1696 (
1697 Txn_Day,Txn_Week,Txn_Period,Txn_Quarter,Txn_Year
1698 ,forecast_period_day,forecast_period_week,forecast_period_period,forecast_period_quarter,forecast_period_year
1699 ,sales_group_id,salesrep_id,forecast_amt,forecast_amt_s,valid_flag,functional_currency
1700 ,Primary_Conversion_Rate,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
1701 opp_forecast_amt,opp_forecast_amt_s
1702 )
1703 VALUES
1704 (
1705 Txn_Day,Txn_Week,Txn_Period,Txn_Quarter,Txn_Year
1706 ,forecast_period_day,forecast_period_week,forecast_period_Period,forecast_period_quarter,forecast_period_year
1707 ,sales_group_id,salesrep_id,forecast_amt_p,forecast_amt_s,valid_flag,functional_currency
1708 ,primary_Conversion_Rate,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
1709 opp_forecast_amt_p,opp_forecast_amt_s
1710 )
1711 INTO bil_bi_processed_fst_id
1712 (
1713 creation_date,created_by,last_update_date,last_updated_by,LAST_UPDATE_LOGIN,Txn_Day
1714 ,sales_group_id,salesrep_id,forecast_amt,forecast_amt_s,functional_currency
1715 ,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
1716 opp_forecast_amt,opp_forecast_amt_s
1717 )
1718 VALUES
1719 (
1720 SYSDATE,G_user_id,SYSDATE,G_user_id,g_login_id,Txn_Day
1721 ,sales_group_id,salesrep_id,forecast_amt_p,forecast_amt_s,functional_currency
1722 ,product_category_id,credit_type_id,period_name,submission_date,forecast_id,
1723 opp_forecast_amt_p,opp_forecast_amt_s
1724 )
1725 (
1726 SELECT
1727 to_char(submission_date, 'J') txn_DAY,
1728 to_number(NULL, 999) txn_WEEK,
1729 to_number(NULL, 999) txn_PERIOD,
1730 to_number(NULL, 999) txn_QUARTER,
1731 to_number(NULL, 999) txn_YEAR,
1732 to_number(NULL,999) forecast_period_day,
1733 to_number(NULL,999)forecast_period_week,
1734 to_number(NULL,999)forecast_period_period,
1735 to_number(NULL,999)forecast_period_quarter,
1736 to_number(NULL,999)forecast_period_year,
1737 sales_group_id,
1738 salesforce_id salesrep_id,
1739 forecast_amt_p,
1740 forecast_amt_s,
1741 'T' valid_flag,
1742 currency_code functional_currency,
1743 NULL primary_conversion_rate,
1744 product_category_id,
1745 credit_type_id,
1746 period_name,
1747 submission_date,
1748 forecast_id,
1749 opp_forecast_amt_p,
1750 opp_forecast_amt_s
1751 FROM
1752 (
1753 SELECT
1754 forecast_id,
1755 submission_date,
1756 sales_group_id,
1757 salesforce_id,
1758 product_category_id,
1759 period_name,
1760 currency_code,
1761 credit_type_id,
1762 forecast_amount,
1763 forecast_amt_p,
1764 forecast_amt_s,
1765 opp_forecast_amt_p,
1766 opp_forecast_amt_s
1767 FROM
1768 (
1769 SELECT
1770 aif.forecast_id,
1771 aif.submission_date,
1772 aif.sales_group_id,
1773 aif.salesforce_id,
1774 apwsl.product_category_id,
1775 aif.period_name,
1776 aif.currency_code,
1777 aif.credit_type_id,
1778 apwsl.forecast_amount,
1779 apwsl.forecast_amount*rates.exchange_rate forecast_amt_p,
1780 apwsl.forecast_amount*rates.exchange_rate_s forecast_amt_s,
1781 NVL(apwsl.opp_forecast_amount,0)*rates.exchange_rate opp_forecast_amt_p,
1782 NVL(apwsl.opp_forecast_amount,0)*rates.exchange_rate_s opp_forecast_amt_s
1783 FROM
1784 as_internal_forecasts aif,
1785 as_prod_worksheet_lines apwsl,
1786 bil_bi_new_fst_id bnfi,
1787 bil_bi_currency_rate rates
1788 WHERE
1789 aif.forecast_id = bnfi.forecast_id
1790 AND aif.status_code = 'SUBMITTED'
1791 AND apwsl.status_code = 'SUBMITTED'
1792 AND aif.submission_date >= g_start_date
1793 AND aif.submission_date <= g_end_date
1794 AND nvl(aif.FORECAST_AMOUNT_FLAG,'Y') = 'Y'
1795 AND aif.forecast_id = apwsl.forecast_id
1796 AND TRUNC(aif.submission_date) = rates.exchange_date
1797 AND aif.currency_code = rates.currency_code
1798 AND NOT EXISTS
1799 (
1800 SELECT 1
1801 FROM bil_bi_processed_fst_id bpfi
1802 WHERE bpfi.forecast_id = aif.forecast_id
1803 )
1804 )
1805 )
1806 );
1807
1808 l_number_of_rows := SQL%ROWCOUNT ;
1809 COMMIT;
1810
1811 END IF;
1812
1813 COMMIT;
1814
1815
1816 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1817 bil_bi_util_collection_pkg.writeLog
1818 (
1819 p_log_level => fnd_log.LEVEL_EVENT,
1820 p_module => g_pkg || l_proc,
1821 p_msg => ' Rows Inserted into Staging + Processed fst id Table: '||l_number_of_rows
1822 );
1823 END IF;
1824
1825 bil_bi_util_collection_pkg.analyze_table('BIL_BI_PROCESSED_FST_ID', TRUE, 10, 'GLOBAL');
1826
1827 bil_bi_util_collection_pkg.analyze_table('BIL_BI_FST_DTL_STG', TRUE, 10, 'GLOBAL');
1828
1829
1830 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1831 bil_bi_util_collection_pkg.writeLog(
1832 p_log_level => fnd_log.LEVEL_PROCEDURE,
1833 p_module => g_pkg || l_proc || ' end ',
1834 p_msg => ' End of Procedure ' || l_proc);
1835 END IF;
1836
1837 EXCEPTION
1838 WHEN OTHERS Then
1839 g_retcode := -1;
1840 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1841 fnd_message.set_token('ERRNO' ,SQLCODE);
1842 fnd_message.set_token('REASON', SQLERRM);
1843 fnd_message.set_token('ROUTINE' , l_proc);
1844 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
1845 p_module => g_pkg || l_proc || ' proc_error ',
1846 p_msg => fnd_message.get,
1847 p_force_log => TRUE);
1848
1849 raise;
1850
1851 END Insert_Into_Stg;
1852
1853
1854
1855 -- ***********************************************************************
1856 PROCEDURE Clean_Up( ErrorMsg in varchar2 ) IS
1857
1858 l_proc VARCHAR2(100);
1859
1860 BEGIN
1861
1862 /* initialization of variable */
1863 l_proc := 'Clean_Up.';
1864 /* end initialization of variable */
1865
1866 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1867 bil_bi_util_collection_pkg.writeLog(
1868 p_log_level => fnd_log.LEVEL_PROCEDURE,
1869 p_module => g_pkg || l_proc || ' begin ',
1870 p_msg => ' Start of Procedure ' || l_proc);
1871 END IF;
1872
1873
1874 IF g_phase = 'Final Cleanup' THEN
1875
1876 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1877 bil_bi_util_collection_pkg.writeLog(
1878 p_log_level => fnd_log.LEVEL_EVENT,
1879 p_module => g_pkg || l_proc,
1880 p_msg => 'Calling wrap up');
1881 END IF;
1882
1883 COMMIT;
1884 BIS_COLLECTION_UTILITIES.wrapup(TRUE,g_fact_row_proc,NULL,g_start_date,g_end_date);
1885 ELSE
1886 COMMIT;
1887 BIS_COLLECTION_UTILITIES.wrapup(FALSE,0,ErrorMsg,g_start_date,g_end_date);
1888 END IF;
1889
1890 /* Always truncate staging table */
1891 bil_bi_util_collection_pkg.truncate_table('BIL_BI_FST_DTL_STG');
1892
1893 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1894 bil_bi_util_collection_pkg.writeLog(
1895 p_log_level => fnd_log.LEVEL_PROCEDURE,
1896 p_module => g_pkg || l_proc || ' end ',
1897 p_msg => ' End of Procedure ' || l_proc);
1898 END IF;
1899
1900
1901 EXCEPTION
1902
1903 WHEN OTHERS THEN
1904
1905 ROLLBACK;
1906
1907 g_retcode:=-1;
1908 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1909 fnd_message.set_token('ERRNO' ,SQLCODE);
1910 fnd_message.set_token('REASON', SQLERRM);
1911 fnd_message.set_token('ROUTINE' , l_proc);
1912 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
1913 p_module => g_pkg || l_proc || ' proc_error ',
1914 p_msg => fnd_message.get,
1915 p_force_log => TRUE);
1916 RAISE;
1917
1918 END Clean_up;
1919
1920 -- **********************************************************************
1921 PROCEDURE Summarize_Frcsts_Periods IS
1922
1923 l_stmt VARCHAR2(4000);
1924
1925 l_select VARCHAR2(3000);
1926 l_rollup VARCHAR2(100);
1927 l_where VARCHAR2(200);
1928 l_from VARCHAR2(100);
1929 l_cnt number;
1930 l_proc VARCHAR2(100);
1931
1932
1933 BEGIN
1934
1935 /* initialization of variable */
1936 l_cnt := 0;
1937 l_proc := ' Summarize_Frcsts_Periods.';
1938 /* end initialization of variable */
1939
1940 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1941 bil_bi_util_collection_pkg.writeLog(
1942 p_log_level => fnd_log.LEVEL_PROCEDURE,
1943 p_module => g_pkg || l_proc || ' begin ',
1944 p_msg => ' Start of Procedure ' || l_proc);
1945 END IF;
1946
1947
1948
1949 /*
1950 * Here Using period start and end dates of all the start and end periods, the forecasts are updated accordingly.
1951 * Need to determine logic here.
1952 */
1953
1954 -- use dynamic sql since this query is run once per collection, so
1955 -- should not cause too manu soft parse.
1956
1957
1958 CASE
1959 WHEN g_map_ent_per_type = 'FII_TIME_WEEK' THEN
1960 BEGIN
1961 NULL;
1962 END;
1963
1964 WHEN g_map_ent_per_type = 'FII_TIME_ENT_PERIOD' THEN
1965 BEGIN
1966 l_select := '
1967 Txn_Day
1968 ,Txn_Week
1969 ,Txn_Period
1970 ,Txn_Quarter
1971 ,Txn_Year ,
1972 to_number(NULL,999) DAY ,
1973 to_number(NULL,999) week ,
1974 to_number(NULL,999) Period ,
1975 fep.ENT_QTR_ID,
1976 fep.ENT_YEAR_ID,
1977 sales_group_id ,
1978 salesrep_id ,
1979 sum(forecast_amt) forecast_amt,
1980 sum(forecast_amt_s) forecast_amt_s,
1981 sum(opp_forecast_amt) opp_forecast_amt,
1982 sum(opp_forecast_amt_s) opp_forecast_amt_s,
1983 ''T'' ,
1984 ''NA'' ,
1985 1,
1986 PRODUCT_CATEGORY_ID,
1987 CREDIT_TYPE_ID';
1988
1989 l_rollup := ' grouping sets (fep.ENT_Year_ID, fep.ENT_Qtr_ID)';
1990 l_FROM := ' FROM BIL_BI_FST_DTL_STG stg, FII_TIME_ENT_Period fep';
1991 l_WHERE := ' WHERE stg.FORECAST_PERIOD_Period = fep.ENT_PERIOD_ID ';
1992 END;
1993
1994 WHEN g_map_ent_per_type = 'FII_TIME_ENT_QTR' THEN
1995 BEGIN
1996
1997 l_select := '
1998 Txn_Day
1999 ,Txn_Week
2000 ,Txn_Period
2001 ,Txn_Quarter
2002 ,Txn_Year ,
2003 to_number(NULL,999) DAY ,
2004 to_number(NULL,999) week ,
2005 to_number(NULL,999) Period ,
2006 to_number(NULL,999) Quarter ,
2007 fep.ENT_YEAR_ID,
2008 sales_group_id ,
2009 salesrep_id ,
2010 sum(forecast_amt) forecast_amt,
2011 sum(forecast_amt_s) forecast_amt_s,
2012 sum(opp_forecast_amt) opp_forecast_amt,
2013 sum(opp_forecast_amt_s) opp_forecast_amt_s,
2014 ''T'' ,
2015 ''NA'' ,
2016 1,
2017 PRODUCT_CATEGORY_ID,
2018 CREDIT_TYPE_ID';
2019
2020
2021 l_rollup := ' grouping sets (fep.ENT_Year_ID)';
2022 l_from := ' FROM BIL_BI_FST_DTL_STG stg, FII_TIME_ENT_QTR fep';
2023 l_where := ' WHERE stg.FORECAST_PERIOD_Quarter = fep.ENT_QTR_ID ';
2024 END;
2025
2026 WHEN g_map_ent_per_type = 'FII_TIME_ENT_YEAR' THEN
2027 BEGIN
2028 NULL ;
2029 END;
2030 END CASE;
2031
2032
2033 IF g_map_ent_per_type = 'FII_TIME_ENT_PERIOD' or
2034 g_map_ent_per_type = 'FII_TIME_ENT_QTR' THEN
2035
2036 IF (g_mode='INITIAL') THEN
2037 l_stmt := ' INSERT /*+ PARALLEL(stg1) */ into BIL_BI_FST_DTL_STG stg1 (';
2038 ELSE
2039 l_stmt := ' INSERT into BIL_BI_FST_DTL_STG (';
2040 END IF;
2041
2042 l_stmt := l_stmt ||
2043 'Txn_Day
2044 ,Txn_Week
2045 ,Txn_Period
2046 ,Txn_Quarter
2047 ,Txn_Year
2048 ,FORECAST_PERIOD_DAY
2049 ,FORECAST_PERIOD_WEEK
2050 ,FORECAST_PERIOD_Period
2051 ,FORECAST_PERIOD_Quarter
2052 ,FORECAST_PERIOD_Year,
2053 SALES_GROUP_ID,
2054 SALESREP_ID,
2055 FORECAST_AMT,
2056 forecast_amt_s,
2057 OPP_FORECAST_AMT,
2058 opp_forecast_amt_s,
2059 VALID_FLAG,
2060 functional_currency,
2061 Primary_Conversion_Rate,
2062 PRODUCT_CATEGORY_ID,
2063 CREDIT_TYPE_ID
2064 )';
2065
2066 IF (g_mode='INITIAL') THEN
2067 l_stmt := l_stmt || ' (SELECT /*+ PARALLEL(stg) PARALLEL(fep) */' ;
2068 ELSE
2069 l_stmt := l_stmt || ' (SELECT ';
2070 END IF;
2071
2072 IF (g_fst_rollup = 'Y') THEN -- Rollup on Forecast Period
2073
2074 l_stmt := l_stmt ||
2075 l_select ||
2076 l_from ||
2077 l_where || ' GROUP BY
2078 stg.Txn_Day,
2079 stg.Txn_Week,
2080 stg.Txn_Period,
2081 stg.Txn_Quarter,
2082 stg.Txn_Year,
2083 stg.sales_group_id,
2084 stg.SALESREP_ID,
2085 stg.PRODUCT_CATEGORY_ID,
2086 stg.CREDIT_TYPE_ID,'
2087 || l_rollup || ')';
2088
2089 EXECUTE IMMEDIATE l_stmt ;
2090 l_cnt:=SQL%ROWCOUNT;
2091 COMMIT;
2092
2093
2094 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
2095 bil_bi_util_collection_pkg.writeLog(
2096 p_log_level => fnd_log.LEVEL_STATEMENT,
2097 p_module => g_pkg ,
2098 p_msg => ' Statemnt to execute is ' || l_stmt);
2099 END IF;
2100
2101
2102 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2103 bil_bi_util_collection_pkg.writeLog
2104 (
2105 p_log_level => fnd_log.LEVEL_EVENT,
2106 p_module => g_pkg || l_proc,
2107 p_msg => 'Inserted ' || l_cnt || ' rows of aggregated (based on fst period) data into summary table'
2108 );
2109 END IF;
2110 END IF;
2111 END IF;
2112
2113
2114 -- rolllup along time dimension
2115 IF (g_mode='INITIAL') THEN
2116 INSERT /*+ PARALLEL(stg1) */ INTO BIL_BI_FST_DTL_STG stg1(
2117 Txn_DAY
2118 ,Txn_Week
2119 ,Txn_Period
2120 ,Txn_Quarter
2121 ,Txn_Year
2122 ,FORECAST_PERIOD_DAY
2123 ,FORECAST_PERIOD_WEEK
2124 ,FORECAST_PERIOD_Period
2125 ,FORECAST_PERIOD_Quarter
2126 ,FORECAST_PERIOD_Year
2127 , SALES_GROUP_ID
2128 , SALESREP_ID
2129 ,FORECAST_AMT
2130 ,forecast_amt_s
2131 ,OPP_FORECAST_AMT
2132 ,opp_forecast_amt_s
2133 ,VALID_FLAG
2134 ,functional_currency
2135 ,Primary_Conversion_Rate
2136 ,PRODUCT_CATEGORY_ID
2137 ,CREDIT_TYPE_ID )
2138 SELECT /*+ PARALLEL(stg) PARALLEL(fday) */
2139 to_number(NULL,999),
2140 fday.week_id,
2141 fday.ent_period_id,
2142 fday.ENT_QTR_ID,
2143 fday.ENT_YEAR_ID,
2144 FORECAST_PERIOD_DAY
2145 ,FORECAST_PERIOD_WEEK
2146 ,FORECAST_PERIOD_Period
2147 ,FORECAST_PERIOD_Quarter
2148 ,FORECAST_PERIOD_Year ,
2149 sales_group_id ,
2150 salesrep_id ,
2151 sum(forecast_amt),
2152 sum(forecast_amt_s),
2153 sum(opp_forecast_amt),
2154 sum(opp_forecast_amt_s),
2155 'T' ,
2156 'NA' ,
2157 1,
2158 PRODUCT_CATEGORY_ID,
2159 CREDIT_TYPE_ID
2160 FROM BIL_BI_FST_DTL_STG stg,
2161 FII_TIME_Day fday
2162 WHERE stg.txn_day = fday.report_date_julian
2163 GROUP BY
2164 stg.FORECAST_PERIOD_DAY
2165 ,stg.FORECAST_PERIOD_WEEK
2166 ,stg.FORECAST_PERIOD_Period
2167 ,stg.FORECAST_PERIOD_Quarter
2168 ,stg.FORECAST_PERIOD_Year,
2169 stg.sales_group_id,
2170 stg.SALESREP_ID,
2171 stg.PRODUCT_CATEGORY_ID,
2172 stg.CREDIT_TYPE_ID,
2173 grouping sets((fday.ENT_Year_ID,
2174 fday.ENT_Qtr_ID,fday.ent_period_id,fday.week_id),(fday.ENT_Year_ID,
2175 fday.ENT_Qtr_ID,fday.ent_period_id), (fday.ENT_Year_ID,
2176 fday.ENT_Qtr_ID), fday.ENT_Year_ID);
2177 l_cnt:=SQL%ROWCOUNT;
2178 ELSE
2179
2180 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2181 bil_bi_util_collection_pkg.writeLog
2182 (
2183 p_log_level => fnd_log.LEVEL_EVENT,
2184 p_module => g_pkg || l_proc,
2185 p_msg => 'in incremental rollup on txn time.'
2186 );
2187 END IF;
2188
2189 INSERT into BIL_BI_FST_DTL_STG (
2190 Txn_DAY
2191 ,Txn_Week
2192 ,Txn_Period
2193 ,Txn_Quarter
2194 ,Txn_Year
2195 ,FORECAST_PERIOD_DAY
2196 ,FORECAST_PERIOD_WEEK
2197 ,FORECAST_PERIOD_Period
2198 ,FORECAST_PERIOD_Quarter
2199 ,FORECAST_PERIOD_Year
2200 , SALES_GROUP_ID
2201 , SALESREP_ID
2202 ,FORECAST_AMT
2203 ,forecast_amt_s
2204 ,OPP_FORECAST_AMT
2205 ,opp_forecast_amt_s
2206 ,VALID_FLAG
2207 ,functional_currency
2208 ,Primary_Conversion_Rate
2209 ,PRODUCT_CATEGORY_ID
2210 ,CREDIT_TYPE_ID )
2211 SELECT
2212 to_number(NULL,999),
2213 fday.week_id,
2214 fday.ent_period_id,
2215 fday.ENT_QTR_ID,
2216 fday.ENT_YEAR_ID,
2217 FORECAST_PERIOD_DAY
2218 ,FORECAST_PERIOD_WEEK
2219 ,FORECAST_PERIOD_Period
2220 ,FORECAST_PERIOD_Quarter
2221 ,FORECAST_PERIOD_Year ,
2222 sales_group_id ,
2223 salesrep_id ,
2224 sum(forecast_amt),
2225 sum(forecast_amt_s),
2226 sum(opp_forecast_amt),
2227 sum(opp_forecast_amt_s),
2228 'T' ,
2229 'NA' ,
2230 1,
2231 PRODUCT_CATEGORY_ID,
2232 CREDIT_TYPE_ID
2233 FROM BIL_BI_FST_DTL_STG stg,
2234 FII_TIME_Day fday
2235 WHERE stg.txn_day = fday.report_date_julian
2236 GROUP BY
2237 stg.FORECAST_PERIOD_DAY
2238 ,stg.FORECAST_PERIOD_WEEK
2239 ,stg.FORECAST_PERIOD_Period
2240 ,stg.FORECAST_PERIOD_Quarter
2241 ,stg.FORECAST_PERIOD_Year,
2242 stg.sales_group_id,
2243 stg.SALESREP_ID,
2244 stg.PRODUCT_CATEGORY_ID,
2245 stg.CREDIT_TYPE_ID,
2246 grouping sets((fday.ENT_Year_ID,
2247 fday.ENT_Qtr_ID,fday.ent_period_id,fday.week_id),(fday.ENT_Year_ID,
2248 fday.ENT_Qtr_ID,fday.ent_period_id), (fday.ENT_Year_ID,
2249 fday.ENT_Qtr_ID), fday.ENT_Year_ID);
2250
2251 l_cnt:=SQL%ROWCOUNT;
2252 END IF;
2253
2254 COMMIT;
2255
2256
2257 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2258 bil_bi_util_collection_pkg.writeLog(
2259 p_log_level => fnd_log.LEVEL_EVENT,
2260 p_module => g_pkg || l_proc,
2261 p_msg => 'Inserted ' || l_cnt || ' rows of aggregated (based on time) data into summary table');
2262 END IF;
2263
2264
2265 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2266 bil_bi_util_collection_pkg.writeLog(
2267 p_log_level => fnd_log.LEVEL_PROCEDURE,
2268 p_module => g_pkg || l_proc || ' end ',
2269 p_msg => ' End of Procedure ' || l_proc);
2270 END IF;
2271
2272
2273 EXCEPTION
2274 WHEN OTHERS Then
2275 g_retcode := -1;
2276 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2277 fnd_message.set_token('ERRNO' ,SQLCODE);
2278 fnd_message.set_token('REASON', SQLERRM);
2279 fnd_message.set_token('ROUTINE' , l_proc);
2280 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
2281 p_module => g_pkg || l_proc || ' proc_error ',
2282 p_msg => fnd_message.get,
2283 p_force_log => TRUE);
2284 raise;
2285
2286 END Summarize_Frcsts_Periods;
2287 -- **********************************************************************
2288
2289 PROCEDURE Insert_From_Stg( ERRBUF IN OUT NOCOPY VARCHAR2
2290 ,RETCODE IN OUT NOCOPY VARCHAR2
2291 )
2292 IS
2293 l_number_of_rows number;
2294 l_sysdate DATE;
2295 l_proc VARCHAR2(100);
2296
2297 BEGIN
2298
2299 /* initialization of variable */
2300 l_sysdate := SYSDATE;
2301 l_proc := 'Insert_From_Stg.';
2302 /* end initialization of variable */
2303
2304 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2305 bil_bi_util_collection_pkg.writeLog(
2306 p_log_level => fnd_log.LEVEL_PROCEDURE,
2307 p_module => g_pkg || l_proc || ' begin ',
2308 p_msg => ' Start of Procedure ' || l_proc);
2309 END IF;
2310
2311
2312 ----------------------------------------------------------------------
2313 -- Merges will not be required since a new recoreded is created each tim
2314 -- a time is made to a forecast.
2315 -----------------------------------------------------------------------
2316
2317 IF (g_mode='INITIAL' and g_first_run) THEN
2318
2319 INSERT /*+ PARALLEL(fact) */ into BIL_BI_FST_DTL_F fact
2320 (TXN_TIME_ID,
2321 TXN_PERIOD_TYPE_ID,
2322 FORECAST_TIME_ID,
2323 FORECAST_PERIOD_TYPE_ID,
2324 SALES_GROUP_ID,
2325 PRODUCT_CATEGORY_ID,
2326 CREDIT_TYPE_ID,
2327 CREATION_DATE,
2328 CREATED_BY,
2329 LAST_UPDATE_DATE,
2330 LAST_UPDATED_BY,
2331 LAST_UPDATE_LOGIN,
2332 SALESREP_ID,
2333 FORECAST_AMT,
2334 forecast_amt_s,
2335 opp_forecast_amt,
2336 opp_forecast_amt_s,
2337 REQUEST_ID,
2338 PROGRAM_APPLICATION_ID,
2339 PROGRAM_ID,
2340 PROGRAM_UPDATE_DATE,
2341 SECURITY_GROUP_ID)
2342 (select
2343 STAGE.TXN_TIME_ID ,
2344 STAGE.TXN_PERIOD_TYPE_ID ,
2345 STAGE.FORECAST_TIME_ID ,
2346 STAGE.FORECAST_PERIOD_TYPE_ID,
2347 STAGE.SALES_GROUP_ID,
2348 STAGE.PRODUCT_CATEGORY_ID,
2349 STAGE.CREDIT_TYPE_ID,
2350 sysdate,
2351 g_user_id ,
2352 sysdate,
2353 g_user_id ,
2354 g_login_id ,
2355 STAGE.SALESREP_ID,
2356 STAGE.AMOUNT,
2357 STAGE.SEC_AMOUNT,
2358 stage.opp_amount,
2359 stage.sec_opp_amount,
2360 G_request_id,
2361 G_appl_id,
2362 G_program_id,
2363 sysdate,
2364 NULL FROM (select /*+ PARALLEL (stg) */
2365 SUM(forecast_amt) AMOUNT,
2366 SUM(forecast_amt_s) SEC_AMOUNT,
2367 SUM(opp_forecast_amt) opp_amount,
2368 SUM(opp_forecast_amt_s) sec_opp_amount,
2369 (CASE WHEN txn_day IS NOT NULL THEN txn_day
2370 WHEN Txn_Week IS NOT NULL THEN Txn_Week
2371 WHEN Txn_Period IS NOT NULL THEN Txn_Period
2372 WHEN Txn_Quarter IS NOT NULL THEN Txn_Quarter
2373 WHEN Txn_Year IS NOT NULL THEN Txn_Year END) TXN_TIME_ID,
2374 (CASE WHEN txn_day IS NOT NULL THEN 1
2375 WHEN Txn_Week IS NOT NULL THEN 16
2376 WHEN Txn_Period IS NOT NULL THEN 32
2377 WHEN Txn_Quarter IS NOT NULL THEN 64
2378 WHEN Txn_Year IS NOT NULL THEN 128 END) TXN_PERIOD_TYPE_ID,
2379 (CASE WHEN forecast_period_week IS NOT NULL THEN forecast_period_week
2380 WHEN FORECAST_PERIOD_Period IS NOT NULL THEN FORECAST_PERIOD_Period
2381 WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN FORECAST_PERIOD_Quarter
2382 WHEN FORECAST_PERIOD_Year IS NOT NULL THEN FORECAST_PERIOD_Year END) FORECAST_TIME_ID,
2383 (CASE WHEN forecast_period_week IS NOT NULL THEN 16
2384 WHEN FORECAST_PERIOD_Period IS NOT NULL THEN 32
2385 WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN 64
2386 WHEN FORECAST_PERIOD_Year IS NOT NULL THEN 128 END) FORECAST_PERIOD_TYPE_ID,
2387 SALES_GROUP_ID,
2388 SALESREP_ID,
2389 PRODUCT_CATEGORY_ID,
2390 CREDIT_TYPE_ID
2391 FROM BIL_BI_FST_DTL_STG stg
2392 GROUP BY
2393 (CASE WHEN txn_day IS NOT NULL THEN txn_day
2394 WHEN Txn_Week IS NOT NULL THEN Txn_Week
2395 WHEN Txn_Period IS NOT NULL THEN Txn_Period
2396 WHEN Txn_Quarter IS NOT NULL THEN Txn_Quarter
2397 WHEN Txn_Year IS NOT NULL THEN Txn_Year END),
2398 (CASE WHEN txn_day IS NOT NULL THEN 1
2399 WHEN Txn_Week IS NOT NULL THEN 16
2400 WHEN Txn_Period IS NOT NULL THEN 32
2401 WHEN Txn_Quarter IS NOT NULL THEN 64
2402 WHEN Txn_Year IS NOT NULL THEN 128 END),
2403 (CASE WHEN forecast_period_week IS NOT NULL THEN forecast_period_week
2404 WHEN FORECAST_PERIOD_Period IS NOT NULL THEN FORECAST_PERIOD_Period
2405 WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN FORECAST_PERIOD_Quarter
2406 WHEN FORECAST_PERIOD_Year IS NOT NULL THEN FORECAST_PERIOD_Year END),
2407 (CASE WHEN forecast_period_week IS NOT NULL THEN 16
2408 WHEN FORECAST_PERIOD_Period IS NOT NULL THEN 32
2409 WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN 64
2410 WHEN FORECAST_PERIOD_Year IS NOT NULL THEN 128 END),
2411 SALES_GROUP_ID,
2412 SALESREP_ID,
2413 PRODUCT_CATEGORY_ID,
2414 CREDIT_TYPE_ID) STAGE);
2415
2416 l_number_of_rows := SQL%ROWCOUNT;
2417 ELSE
2418 -- added the index hint per jais suggestions
2419 MERGE /*+ index(bsum) */ INTO BIL_BI_FST_DTL_F bsum
2420 USING (select
2421 SUM(forecast_amt) AMOUNT,
2422 SUM(forecast_amt_s) SEC_AMOUNT,
2423 SUM(opp_forecast_amt) opp_amount,
2424 SUM(opp_forecast_amt_s) sec_opp_amount,
2425 (CASE WHEN txn_day IS NOT NULL THEN txn_day
2426 WHEN Txn_Week IS NOT NULL THEN Txn_Week
2427 WHEN Txn_Period IS NOT NULL THEN Txn_Period
2428 WHEN Txn_Quarter IS NOT NULL THEN Txn_Quarter
2429 WHEN Txn_Year IS NOT NULL THEN Txn_Year END) TXN_TIME_ID,
2430 (CASE WHEN txn_day IS NOT NULL THEN 1
2431 WHEN Txn_Week IS NOT NULL THEN 16
2432 WHEN Txn_Period IS NOT NULL THEN 32
2433 WHEN Txn_Quarter IS NOT NULL THEN 64
2434 WHEN Txn_Year IS NOT NULL THEN 128 END) TXN_PERIOD_TYPE_ID,
2435 (CASE WHEN forecast_period_week IS NOT NULL THEN forecast_period_week
2436 WHEN FORECAST_PERIOD_Period IS NOT NULL THEN FORECAST_PERIOD_Period
2437 WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN FORECAST_PERIOD_Quarter
2438 WHEN FORECAST_PERIOD_Year IS NOT NULL THEN FORECAST_PERIOD_Year END) FORECAST_TIME_ID,
2439 (CASE WHEN forecast_period_week IS NOT NULL THEN 16
2440 WHEN FORECAST_PERIOD_Period IS NOT NULL THEN 32
2441 WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN 64
2442 WHEN FORECAST_PERIOD_Year IS NOT NULL THEN 128 END) FORECAST_PERIOD_TYPE_ID,
2443 SALES_GROUP_ID,
2444 SALESREP_ID,
2445 PRODUCT_CATEGORY_ID,
2446 CREDIT_TYPE_ID
2447 FROM BIL_BI_FST_DTL_STG stg
2448 GROUP BY
2449 (CASE WHEN txn_day IS NOT NULL THEN txn_day
2450 WHEN Txn_Week IS NOT NULL THEN Txn_Week
2451 WHEN Txn_Period IS NOT NULL THEN Txn_Period
2452 WHEN Txn_Quarter IS NOT NULL THEN Txn_Quarter
2453 WHEN Txn_Year IS NOT NULL THEN Txn_Year END),
2454 (CASE WHEN txn_day IS NOT NULL THEN 1
2455 WHEN Txn_Week IS NOT NULL THEN 16
2456 WHEN Txn_Period IS NOT NULL THEN 32
2457 WHEN Txn_Quarter IS NOT NULL THEN 64
2458 WHEN Txn_Year IS NOT NULL THEN 128 END),
2459 (CASE WHEN forecast_period_week IS NOT NULL THEN forecast_period_week
2460 WHEN FORECAST_PERIOD_Period IS NOT NULL THEN FORECAST_PERIOD_Period
2461 WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN FORECAST_PERIOD_Quarter
2462 WHEN FORECAST_PERIOD_Year IS NOT NULL THEN FORECAST_PERIOD_Year END),
2463 (CASE WHEN forecast_period_week IS NOT NULL THEN 16
2464 WHEN FORECAST_PERIOD_Period IS NOT NULL THEN 32
2465 WHEN FORECAST_PERIOD_Quarter IS NOT NULL THEN 64
2466 WHEN FORECAST_PERIOD_Year IS NOT NULL THEN 128 END),
2467 SALES_GROUP_ID,
2468 SALESREP_ID,
2469 PRODUCT_CATEGORY_ID,
2470 CREDIT_TYPE_ID) STAGE
2471 ON (bsum.txn_time_id = stage.txn_time_id AND
2472 bsum.txn_period_type_id = stage.txn_period_type_id AND
2473 bsum.forecast_time_id = stage.forecast_time_id AND
2474 bsum.forecast_period_type_id = stage.forecast_period_type_id AND
2475 bsum.SALES_GROUP_ID = stage.SALES_GROUP_ID AND
2476 nvl(bsum.SALESREP_ID, -999) = nvl(stage.SALESREP_ID, -999) AND
2477 bsum.PRODUCT_CATEGORY_ID = stage.PRODUCT_CATEGORY_ID AND
2478 bsum.CREDIT_TYPE_ID = stage.CREDIT_TYPE_ID
2479 )
2480 WHEN MATCHED THEN UPDATE SET bsum.forecast_amt = (bsum.forecast_amt+ stage.amount)
2481 ,bsum.forecast_amt_s = (bsum.forecast_amt_s+ stage.sec_amount)
2482 ,bsum.opp_forecast_amt = (bsum.opp_forecast_amt+ stage.opp_amount)
2483 ,bsum.opp_forecast_amt_s = (bsum.opp_forecast_amt_s+ stage.sec_opp_amount)
2484 ,bsum.LAST_UPDATED_BY = g_user_id
2485 ,bsum.LAST_UPDATE_DATE = l_sysdate
2486 ,bsum.LAST_UPDATE_LOGIN= G_Login_Id
2487
2488 WHEN NOT MATCHED THEN INSERT
2489 (TXN_TIME_ID ,
2490 TXN_PERIOD_TYPE_ID ,
2491 FORECAST_TIME_ID ,
2492 FORECAST_PERIOD_TYPE_ID ,
2493 SALES_GROUP_ID ,
2494 SALESREP_ID ,
2495 FORECAST_AMT ,
2496 forecast_amt_s ,
2497 OPP_FORECAST_AMT ,
2498 opp_forecast_amt_s ,
2499 PRODUCT_CATEGORY_ID,
2500 CREDIT_TYPE_ID,
2501 CREATION_DATE ,
2502 CREATED_BY ,
2503 LAST_UPDATE_DATE ,
2504 LAST_UPDATE_LOGIN ,
2505 LAST_UPDATED_BY,
2506 REQUEST_ID,
2507 PROGRAM_APPLICATION_ID,
2508 PROGRAM_ID,
2509 PROGRAM_UPDATE_DATE
2510 )
2511 values(
2512 STAGE.TXN_TIME_ID ,
2513 STAGE.TXN_PERIOD_TYPE_ID ,
2514 STAGE.FORECAST_TIME_ID ,
2515 STAGE.FORECAST_PERIOD_TYPE_ID,
2516 STAGE.SALES_GROUP_ID,
2517 STAGE.SALESREP_ID,
2518 stage.amount,
2519 stage.sec_amount,
2520 stage.opp_amount,
2521 stage.sec_opp_amount,
2522 STAGE.PRODUCT_CATEGORY_ID,
2523 STAGE.CREDIT_TYPE_ID,
2524 sysdate,
2525 g_user_id,
2526 sysdate,
2527 g_user_id ,
2528 g_login_id ,
2529 G_request_id,
2530 G_appl_id,
2531 G_program_id,
2532 sysdate);
2533
2534 l_number_of_rows := SQL%ROWCOUNT;
2535 END IF;
2536
2537 commit;
2538
2539 g_fact_row_proc := l_number_of_rows;
2540
2541 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2542 bil_bi_util_collection_pkg.writeLog(
2543 p_log_level => fnd_log.LEVEL_EVENT,
2544 p_module => g_pkg || l_proc,
2545 p_msg => 'Merged ' || l_number_of_rows || ' rows of records into fact table');
2546 END IF;
2547
2548
2549 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2550 bil_bi_util_collection_pkg.writeLog(
2551 p_log_level => fnd_log.LEVEL_PROCEDURE,
2552 p_module => g_pkg || l_proc || ' end ',
2553 p_msg => ' End of Procedure ' || l_proc);
2554 END IF;
2555
2556 COMMIT;
2557
2558 EXCEPTION
2559
2560 WHEN OTHERS Then
2561 g_retcode := -1;
2562 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2563 fnd_message.set_token('ERRNO' ,SQLCODE);
2564 fnd_message.set_token('REASON', SQLERRM);
2565 fnd_message.set_token('ROUTINE' , l_proc);
2566 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
2567 p_module => g_pkg || l_proc || ' proc_error ',
2568 p_msg => fnd_message.get,
2569 p_force_log => TRUE);
2570
2571 raise;
2572
2573 END Insert_From_Stg;
2574
2575
2576
2577
2578 --------------------------------------------------------------------
2579 -- PROCEDURE SUMMARY_ADJUST is
2580 --------------------------------------------------------------------
2581 PROCEDURE Summary_Adjust IS
2582
2583 l_period_name VARCHAR2(15) ;
2584 l_number_of_rows number;
2585 l_proc VARCHAR2(100);
2586
2587 cursor c5 is select distinct stg.period_name
2588 FROM
2589 BIL_BI_FST_DTL_STG stg
2590 WHERE valid_flag = 'F';
2591
2592 BEGIN
2593
2594 /* initialization of variable */
2595 l_number_of_rows :=0;
2596 l_proc := 'Summary_Adjust.';
2597 /* end initialization of variable */
2598
2599 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2600 bil_bi_util_collection_pkg.writeLog(
2601 p_log_level => fnd_log.LEVEL_PROCEDURE,
2602 p_module => g_pkg || l_proc || ' begin ',
2603 p_msg => ' Start of Procedure ' || l_proc);
2604 END IF;
2605
2606
2607 CASE
2608 WHEN g_map_ent_per_type = 'FII_TIME_WEEK' THEN
2609 BEGIN
2610
2611 update BIL_BI_FST_DTL_STG stg set valid_flag = 'F' WHERE
2612 not exists
2613 (select '1' FROM
2614 gl_periods glp,
2615 FII_TIME_WEEK fep
2616 WHERE
2617 fep.start_date = glp.start_date and
2618 fep.end_date = glp.end_date and
2619 glp.period_set_name = g_cal and
2620 glp.period_name = stg.period_name and
2621 glp.period_type = g_fsct_per_type );
2622
2623 END;
2624
2625 WHEN g_map_ent_per_type = 'FII_TIME_ENT_PERIOD' THEN
2626 BEGIN
2627
2628 update BIL_BI_FST_DTL_STG stg set valid_flag = 'F' WHERE
2629 not exists
2630 (select '1' FROM
2631 gl_periods glp,
2632 FII_TIME_ENT_PERIOD fep
2633 WHERE
2634 fep.start_date = glp.start_date and
2635 fep.end_date = glp.end_date and
2636 glp.period_set_name = g_cal and
2637 glp.period_name = stg.period_name and
2638 glp.period_type = g_fsct_per_type );
2639 END;
2640
2641 WHEN g_map_ent_per_type = 'FII_TIME_ENT_QTR' THEN
2642 BEGIN
2643
2644 update BIL_BI_FST_DTL_STG stg set valid_flag = 'F' WHERE
2645 not exists
2646 (select '1' FROM
2647 gl_periods glp,
2648 FII_TIME_ENT_QTR fep
2649 WHERE
2650 fep.start_date = glp.start_date and
2651 fep.end_date = glp.end_date and
2652 glp.period_set_name = g_cal and
2653 glp.period_name = stg.period_name and
2654 glp.period_type = g_fsct_per_type );
2655 END;
2656
2657 WHEN g_map_ent_per_type = 'FII_TIME_ENT_YEAR' THEN
2658 BEGIN
2659
2660
2661 update BIL_BI_FST_DTL_STG stg set valid_flag = 'F' WHERE
2662 not exists
2663 (select '1' FROM
2664 gl_periods glp,
2665 FII_TIME_ENT_YEAR fep
2666 WHERE
2667 fep.start_date = glp.start_date and
2668 fep.end_date = glp.end_date and
2669 glp.period_set_name = g_cal and
2670 glp.period_name = stg.period_name and
2671 glp.period_type = g_fsct_per_type );
2672 END;
2673
2674 END CASE;
2675
2676 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2677 bil_bi_util_collection_pkg.writeLog(
2678 p_log_level => fnd_log.LEVEL_EVENT,
2679 p_module => g_pkg || l_proc,
2680 p_msg => 'updated status bits of ' || sql%rowcount ||' rows,
2681 they will be deleted due to invalid mapping forecast period');
2682 END IF;
2683
2684 -- wzhu added fix for bug 3792767
2685 commit;
2686
2687 -- delete the records before the amt are adjusted
2688
2689 -- Check to see if all gl periods exists in enterprise calendar
2690
2691
2692 OPEN c5;
2693 LOOP
2694 FETCH c5 into
2695 l_period_name ;
2696 EXIT WHEN c5%NOTFOUND ;
2697 l_number_of_rows :=l_number_of_rows + 1;
2698
2699
2700 IF(l_number_of_rows=1) THEN
2701 fnd_message.set_name('BIL','BIL_BI_FST_PERIOD_MAP_ERROR');
2702 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_ERROR) THEN
2703 bil_bi_util_collection_pkg.writeLog(
2704 p_log_level => fnd_log.LEVEL_ERROR,
2705 p_module => g_pkg || l_proc,
2706 p_msg =>fnd_message.get);
2707 END IF;
2708
2709 END IF;
2710
2711 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2712 bil_bi_util_collection_pkg.writeLog(
2713 p_log_level => fnd_log.LEVEL_EVENT,
2714 p_module => g_pkg || l_proc,
2715 p_msg =>l_period_name);
2716 END IF;
2717
2718 END LOOP;
2719 CLOSE c5;
2720
2721 IF(l_number_of_rows > 0) THEN
2722
2723 DELETE FROM bil_bi_processed_fst_id WHERE forecast_id IN
2724 (SELECT forecast_id FROM BIL_BI_FST_DTL_STG
2725 WHERE valid_flag = 'F');
2726
2727 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
2728 bil_bi_util_collection_pkg.writeLog(
2729 p_log_level => fnd_log.LEVEL_STATEMENT,
2730 p_module => g_pkg || l_proc,
2731 p_msg =>'deleted '||SQL%ROWCOUNT||' rows FROM bil_bi_processed_fst_id table due to valid_flag');
2732 END IF;
2733
2734
2735 DELETE FROM BIL_BI_FST_DTL_STG stg WHERE valid_flag = 'F';
2736
2737 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
2738 bil_bi_util_collection_pkg.writeLog(
2739 p_log_level => fnd_log.LEVEL_STATEMENT,
2740 p_module => g_pkg || l_proc,
2741 p_msg =>'deleted '||SQL%ROWCOUNT||' rows from BIL_BI_FST_DTL_STG table due to valid_flag');
2742 END IF;
2743
2744
2745 END IF;
2746
2747 commit;
2748
2749
2750 /* summary adjustments takes place here */
2751
2752
2753 IF (g_conv_rate_cnt = 0) THEN
2754 CASE WHEN g_map_ent_per_type = 'FII_TIME_WEEK' THEN
2755
2756 CASE WHEN g_mode='INITIAL' OR g_first_run THEN
2757
2758 UPDATE /*+ parallel(stg) */ BIL_BI_FST_DTL_STG stg
2759 SET stg.FORECAST_PERIOD_Week
2760 = (select fep.week_id FROM FII_TIME_WEEK fep , gl_periods glp
2761 WHERE
2762 fep.start_date = glp.start_date and
2763 fep.end_date = glp.end_date and
2764 glp.period_set_name = g_cal and
2765 glp.period_name = stg.period_name and
2766 glp.period_type = g_fsct_per_type
2767 );
2768
2769 ELSE
2770
2771 UPDATE BIL_BI_FST_DTL_STG stg
2772 SET stg.FORECAST_PERIOD_Week
2773 = (select fep.week_id FROM FII_TIME_WEEK fep , gl_periods glp
2774 WHERE
2775 fep.start_date = glp.start_date and
2776 fep.end_date = glp.end_date and
2777 glp.period_set_name = g_cal and
2778 glp.period_name = stg.period_name and
2779 glp.period_type = g_fsct_per_type
2780 ),
2781 (stg.forecast_amt_1,stg.forecast_amt_s_1,stg.opp_forecast_amt_1,stg.opp_forecast_amt_s_1) =
2782 (
2783 SELECT
2784 (stg.forecast_amt-pfi.forecast_amt),(stg.forecast_amt_s - pfi.forecast_amt_s),
2785 (stg.opp_forecast_amt-pfi.opp_forecast_amt),(stg.opp_forecast_amt_s - pfi.opp_forecast_amt_s)
2786 FROM
2787 bil_bi_processed_fst_id pfi
2788 WHERE
2789 pfi.product_category_id=stg.product_category_id
2790 AND pfi.sales_group_id=stg.sales_group_id
2791 AND NVL(pfi.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
2792 AND pfi.period_name=stg.period_name
2793 AND pfi.credit_type_id=stg.credit_type_id
2794 AND rownum < 2
2795 AND pfi.submission_date=
2796 (
2797 SELECT MAX(submission_date) FROM bil_bi_processed_fst_id pfi1
2798 WHERE
2799 pfi1.product_category_id=stg.product_category_id
2800 AND pfi1.sales_group_id=stg.sales_group_id
2801 AND NVL(pfi1.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
2802 AND pfi1.period_name=stg.period_name
2803 AND pfi1.credit_type_id=stg.credit_type_id
2804 AND pfi1.submission_date < stg.submission_date
2805 )
2806 );
2807
2808 END CASE;
2809
2810
2811 WHEN g_map_ent_per_type = 'FII_TIME_ENT_PERIOD' THEN
2812
2813 CASE WHEN g_mode='INITIAL' OR g_first_run THEN
2814
2815 UPDATE /*+ parallel(stg) */ BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_PERIOD
2816 = (SELECT fep.ent_period_id FROM FII_TIME_ENT_PERIOD fep,gl_periods glp
2817 WHERE
2818 fep.start_date = glp.start_date and
2819 fep.end_date = glp.end_date and
2820 glp.period_set_name = g_cal and
2821 glp.period_name = stg.period_name and
2822 glp.period_type = g_fsct_per_type
2823 );
2824
2825 ELSE
2826
2827 UPDATE BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_PERIOD
2828 = (select fep.ent_period_id FROM FII_TIME_ENT_PERIOD fep , gl_periods glp
2829 WHERE
2830 fep.start_date = glp.start_date and
2831 fep.end_date = glp.end_date and
2832 glp.period_set_name = g_cal and
2833 glp.period_name = stg.period_name and
2834 glp.period_type = g_fsct_per_type
2835 ),
2836 (stg.forecast_amt_1,stg.forecast_amt_s_1,stg.opp_forecast_amt_1,stg.opp_forecast_amt_s_1) =
2837 (
2838 SELECT
2839 (stg.forecast_amt-pfi.forecast_amt),(stg.forecast_amt_s - pfi.forecast_amt_s),
2840 (stg.opp_forecast_amt-pfi.opp_forecast_amt),(stg.opp_forecast_amt_s - pfi.opp_forecast_amt_s)
2841 FROM
2842 bil_bi_processed_fst_id pfi
2843 WHERE
2844 pfi.product_category_id=stg.product_category_id
2845 AND pfi.sales_group_id=stg.sales_group_id
2846 AND NVL(pfi.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
2847 AND pfi.period_name=stg.period_name
2848 AND pfi.credit_type_id=stg.credit_type_id
2849 AND rownum < 2
2850 AND pfi.submission_date=
2851 (
2852 SELECT MAX(submission_date) FROM bil_bi_processed_fst_id pfi1
2853 WHERE
2854 pfi1.product_category_id=stg.product_category_id
2855 AND pfi1.sales_group_id=stg.sales_group_id
2856 AND NVL(pfi1.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
2857 AND pfi1.period_name=stg.period_name
2858 AND pfi1.credit_type_id=stg.credit_type_id
2859 AND pfi1.submission_date < stg.submission_date
2860 )
2861 );
2862
2863 END CASE;
2864
2865 WHEN g_map_ent_per_type = 'FII_TIME_ENT_QTR' THEN
2866
2867 CASE WHEN g_mode='INITIAL' OR g_first_run THEN
2868
2869 UPDATE /*+ parallel(stg) */ BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_QUARTER
2870 = (SELECT fep.ent_qtr_id FROM FII_TIME_ENT_QTR fep,gl_periods glp
2871 WHERE
2872 fep.start_date = glp.start_date and
2873 fep.end_date = glp.end_date and
2874 glp.period_set_name = g_cal and
2875 glp.period_name = stg.period_name and
2876 glp.period_type = g_fsct_per_type
2877 );
2878
2879 ELSE
2880
2881 UPDATE BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_QUARTER
2882 = (select fep.ent_qtr_id FROM FII_TIME_ENT_QTR fep , gl_periods glp
2883 WHERE
2884 fep.start_date = glp.start_date and
2885 fep.end_date = glp.end_date and
2886 glp.period_set_name = g_cal and
2887 glp.period_name = stg.period_name and
2888 glp.period_type = g_fsct_per_type
2889 ),
2890 (stg.forecast_amt_1,stg.forecast_amt_s_1,stg.opp_forecast_amt_1,stg.opp_forecast_amt_s_1) =
2891 (
2892 SELECT
2893 (stg.forecast_amt-pfi.forecast_amt),(stg.forecast_amt_s - pfi.forecast_amt_s),
2894 (stg.opp_forecast_amt-pfi.opp_forecast_amt),(stg.opp_forecast_amt_s - pfi.opp_forecast_amt_s)
2895 FROM
2896 bil_bi_processed_fst_id pfi
2897 WHERE
2898 pfi.product_category_id=stg.product_category_id
2899 AND pfi.sales_group_id=stg.sales_group_id
2900 AND NVL(pfi.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
2901 AND pfi.period_name=stg.period_name
2902 AND pfi.credit_type_id=stg.credit_type_id
2903 AND rownum < 2
2904 AND pfi.submission_date=
2905 (
2906 SELECT MAX(submission_date) FROM bil_bi_processed_fst_id pfi1
2907 WHERE
2908 pfi1.product_category_id=stg.product_category_id
2909 AND pfi1.sales_group_id=stg.sales_group_id
2910 AND NVL(pfi1.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
2911 AND pfi1.period_name=stg.period_name
2912 AND pfi1.credit_type_id=stg.credit_type_id
2913 AND pfi1.submission_date < stg.submission_date
2914 )
2915 );
2916
2917 END CASE;
2918
2919 WHEN g_map_ent_per_type = 'FII_TIME_ENT_YEAR' THEN
2920
2921 CASE WHEN g_mode='INITIAL' OR g_first_run THEN
2922
2923 UPDATE BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_YEAR
2924 = (SELECT fep.ent_year_id FROM FII_TIME_ENT_YEAR fep,gl_periods glp
2925 WHERE
2926 fep.start_date = glp.start_date and
2927 fep.end_date = glp.end_date and
2928 glp.period_set_name = g_cal and
2929 glp.period_name = stg.period_name and
2930 glp.period_type = g_fsct_per_type
2931 );
2932
2933 ELSE
2934
2935 UPDATE BIL_BI_FST_DTL_STG stg set stg.FORECAST_PERIOD_YEAR
2936 = (select fep.ent_year_id FROM FII_TIME_ENT_YEAR fep , gl_periods glp
2937 WHERE
2938 fep.start_date = glp.start_date and
2939 fep.end_date = glp.end_date and
2940 glp.period_set_name = g_cal and
2941 glp.period_name = stg.period_name and
2942 glp.period_type = g_fsct_per_type
2943 ),
2944 (stg.forecast_amt_1,stg.forecast_amt_s_1,stg.opp_forecast_amt_1,stg.opp_forecast_amt_s_1) =
2945 (
2946 SELECT
2947 (stg.forecast_amt-pfi.forecast_amt),(stg.forecast_amt_s - pfi.forecast_amt_s),
2948 (stg.opp_forecast_amt-pfi.opp_forecast_amt),(stg.opp_forecast_amt_s - pfi.opp_forecast_amt_s)
2949 FROM
2950 bil_bi_processed_fst_id pfi
2951 WHERE
2952 pfi.product_category_id=stg.product_category_id
2953 AND pfi.sales_group_id=stg.sales_group_id
2954 AND NVL(pfi.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
2955 AND pfi.period_name=stg.period_name
2956 AND pfi.credit_type_id=stg.credit_type_id
2957 AND rownum < 2
2958 AND pfi.submission_date=
2959 (
2960 SELECT MAX(submission_date) FROM bil_bi_processed_fst_id pfi1
2961 WHERE
2962 pfi1.product_category_id=stg.product_category_id
2963 AND pfi1.sales_group_id=stg.sales_group_id
2964 AND NVL(pfi1.salesrep_id,-999) = NVL(stg.salesrep_id,-999)
2965 AND pfi1.period_name=stg.period_name
2966 AND pfi1.credit_type_id=stg.credit_type_id
2967 AND pfi1.submission_date < stg.submission_date
2968 )
2969 );
2970 END CASE;
2971 END CASE;
2972 END IF;
2973
2974 /* end of summary adjustments */
2975
2976
2977 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2978 bil_bi_util_collection_pkg.writeLog
2979 (
2980 p_log_level => fnd_log.LEVEL_EVENT,
2981 p_module => g_pkg || l_proc,
2982 p_msg => 'smmary adjusted forecast time of ' || sql%rowcount ||' rows'
2983 );
2984 END IF;
2985
2986 commit;
2987
2988 --update forecast_amt and forecast_amt_s columns to have the correct values
2989
2990 UPDATE
2991 bil_bi_fst_dtl_stg
2992 SET
2993 forecast_amt = forecast_amt_1,forecast_amt_s=forecast_amt_s_1,
2994 opp_forecast_amt=opp_forecast_amt_1,opp_forecast_amt_s=opp_forecast_amt_s_1
2995 WHERE forecast_amt_1 IS NOT NULL;
2996
2997 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2998 bil_bi_util_collection_pkg.writeLog(
2999 p_log_level => fnd_log.LEVEL_EVENT,
3000 p_module => g_pkg || l_proc,
3001 p_msg => 'secondary currency update done for' || sql%rowcount ||' rows');
3002 END IF;
3003
3004
3005
3006 COMMIT;
3007
3008
3009 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
3010 bil_bi_util_collection_pkg.writeLog(
3011 p_log_level => fnd_log.LEVEL_PROCEDURE,
3012 p_module => g_pkg || l_proc || ' end ',
3013 p_msg => ' End of Procedure ' || l_proc);
3014 END IF;
3015
3016
3017 EXCEPTION
3018
3019 WHEN OTHERS Then
3020 g_retcode := -1;
3021 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
3022 fnd_message.set_token('ERRNO' ,SQLCODE);
3023 fnd_message.set_token('REASON', SQLERRM);
3024 fnd_message.set_token('ROUTINE' , l_proc);
3025 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
3026 p_module => g_pkg || l_proc || ' proc_error ',
3027 p_msg => fnd_message.get,
3028 p_force_log => TRUE);
3029
3030 raise;
3031 END summary_adjust;
3032 --------------------------------------------------------------------
3033 -- PROCEDURE SUMMARY_ERR_CHECK
3034 --------------------------------------------------------------------
3035 PROCEDURE SUMMARY_ERR_CHECK IS
3036
3037 l_time_cnt NUMBER;
3038 l_stg_min DATE;
3039 l_stg_max DATE;
3040 l_day_min DATE;
3041 l_day_max DATE;
3042 l_miss_date BOOLEAN;
3043 l_period_name VARCHAR2(15) ;
3044 l_number_of_rows NUMBER;
3045 l_proc VARCHAR2(100);
3046
3047 BEGIN
3048
3049 /* initialization of variable */
3050 l_time_cnt :=0;
3051 l_miss_date := FALSE;
3052 l_number_of_rows :=0;
3053 l_proc := 'SUMMARY_ERR_CHECK.';
3054 /* end initialization of variable */
3055
3056 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
3057 bil_bi_util_collection_pkg.writeLog(
3058 p_log_level => fnd_log.LEVEL_PROCEDURE,
3059 p_module => g_pkg || l_proc || ' begin ',
3060 p_msg => ' Start of Procedure ' || l_proc);
3061 END IF;
3062
3063 ------------------------------------------------------
3064 -- If there are missing exchange rates indicated in
3065 -- the staging table, then call report_missing_rates
3066 -- API to print out the missing rates report
3067 ------------------------------------------------------
3068
3069
3070 IF (g_truncate_flag OR g_first_run) THEN
3071
3072 SELECT /*+ PARALLEL(rates) */ COUNT(1)
3073 INTO g_conv_rate_cnt
3074 FROM bil_bi_currency_rate rates
3075 WHERE ((exchange_rate < 0 OR exchange_rate is NULL)
3076 OR (g_sec_currency IS NOT NULL AND (exchange_rate_s < 0 OR exchange_rate_s is NULL)))
3077 AND exchange_date IN (SELECT DISTINCT TRUNC(submission_date) FROM bil_bi_new_fst_id)
3078 AND rownum < 2;
3079 ELSE
3080 SELECT COUNT(1)
3081 INTO g_conv_rate_cnt
3082 FROM bil_bi_currency_rate
3083 WHERE ((exchange_rate < 0 OR exchange_rate is NULL)
3084 OR (g_sec_currency IS NOT NULL AND (exchange_rate_s < 0 OR exchange_rate_s is NULL)))
3085 AND exchange_date IN (SELECT DISTINCT TRUNC(submission_date) FROM bil_bi_new_fst_id)
3086 AND rownum < 2;
3087 END IF;
3088
3089 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3090 bil_bi_util_collection_pkg.writeLog(
3091 p_log_level => fnd_log.LEVEL_EVENT,
3092 p_module => g_pkg || l_proc,
3093 p_msg => 'err check: value in variable g_conv_rate_cnt is '||g_conv_rate_cnt);
3094 END IF;
3095
3096 IF (g_conv_rate_cnt >0) THEN -- Missing Rates Starts Here
3097 -------------------------------------------------
3098 -- Write out translated message to let user know
3099 -- there are missing exchange rate information
3100 -------------------------------------------------
3101 FII_MESSAGE.write_log(msg_name => 'BIS_DBI_CURR_PARTIAL_LOAD',token_num => 0);
3102
3103 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3104 bil_bi_util_collection_pkg.writeLog(
3105 p_log_level => fnd_log.LEVEL_EVENT,
3106 p_module => g_pkg || l_proc,
3107 p_msg => 'Missing currency conversion rates found, '
3108 ||'program will exit with warning status. '
3109 ||'Please fix the missing conversion rates');
3110 END IF;
3111 g_retcode := -1;
3112 g_missing_rates := 1;
3113 REPORT_MISSING_RATES;
3114 ELSE
3115 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3116 bil_bi_util_collection_pkg.writeLog
3117 (
3118 p_log_level => fnd_log.LEVEL_EVENT,
3119 p_module => g_pkg || l_proc,
3120 p_msg => 'No Missing rates found!'
3121 );
3122 END IF;
3123
3124 END IF; -- Missing Rates Ends Here
3125
3126
3127 -----------------------------------------------------------
3128 -- If we find record in the aif table which references
3129 -- time records which does not exist in FII_TIME_DAY
3130 -- table, then we will exit the program with warning
3131 -- status
3132 -----------------------------------------------------------
3133 -- Check for Missing Time Dimension for Submission Date Time ID
3134
3135 SELECT MIN(nfi.submission_date),
3136 MAX(nfi.submission_date)
3137 INTO l_stg_min,
3138 l_stg_max
3139 FROM bil_bi_new_fst_id nfi;
3140
3141 IF (l_stg_min IS NOT NULL AND l_stg_max IS NOT NULL) THEN
3142 FII_TIME_API.check_missing_date
3143 (
3144 l_stg_min,
3145 l_stg_max,
3146 l_miss_date
3147 );
3148 END IF;
3149
3150
3151 IF (l_miss_date) THEN
3152 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3153 bil_bi_util_collection_pkg.writeLog
3154 (
3155 p_log_level => fnd_log.LEVEL_EVENT,
3156 p_module => g_pkg || l_proc,
3157 p_msg => 'Time dimension is not fully populated.'
3158 );
3159 END IF;
3160 g_retcode := -1;
3161 g_missing_time := 1;
3162 ELSE
3163 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3164 bil_bi_util_collection_pkg.writeLog
3165 (
3166 p_log_level => fnd_log.LEVEL_EVENT,
3167 p_module => g_pkg || l_proc,
3168 p_msg => 'Time dimension is fully populated for transaction time id'
3169 );
3170 END IF;
3171 END IF;
3172
3173 -- Check for Missing Time Dimension for Forecast Time ID
3174
3175 SELECT
3176 MIN(glp.start_date),
3177 MAX(glp.end_date)
3178 INTO
3179 l_day_min,
3180 l_day_max
3181 FROM
3182 bil_bi_new_fst_id nfi,
3183 gl_periods glp
3184 WHERE
3185 glp.period_set_name = g_cal
3186 AND glp.period_name = nfi.period_name
3187 AND glp.period_type = g_fsct_per_type;
3188
3189 IF(l_day_min IS NULL OR l_day_max IS NULL) THEN
3190 RETURN ;
3191 ELSE
3192 BEGIN
3193 FII_TIME_API.check_missing_date (l_day_min,l_day_max, l_miss_date);
3194
3195 IF (l_miss_date) THEN
3196 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3197 bil_bi_util_collection_pkg.writeLog(
3198 p_log_level => fnd_log.LEVEL_EVENT,
3199 p_module => g_pkg || l_proc,
3200 p_msg => 'Time dimension is not fully populated.');
3201 END IF;
3202 g_retcode := -1;
3203 g_missing_time := 1;
3204 ELSE
3205 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3206 bil_bi_util_collection_pkg.writeLog(
3207 p_log_level => fnd_log.LEVEL_EVENT,
3208 p_module => g_pkg || l_proc,
3209 p_msg => 'Time dimension is fully populated for forecast time id');
3210 END IF;
3211 END IF;
3212
3213 END;
3214 END IF;
3215
3216 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3217 bil_bi_util_collection_pkg.writeLog(
3218 p_log_level => fnd_log.LEVEL_EVENT,
3219 p_module => g_pkg || l_proc,
3220 p_msg => 'Summary error check completed!');
3221 END IF;
3222
3223 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
3224 bil_bi_util_collection_pkg.writeLog(
3225 p_log_level => fnd_log.LEVEL_PROCEDURE,
3226 p_module => g_pkg || l_proc || ' end ',
3227 p_msg => ' End of Procedure ' || l_proc);
3228 END IF;
3229
3230 EXCEPTION
3231 WHEN OTHERS THEN
3232 g_retcode:=-1;
3233 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
3234 fnd_message.set_token('ERRNO' ,SQLCODE);
3235 fnd_message.set_token('REASON', SQLERRM);
3236 fnd_message.set_token('ROUTINE' , l_proc);
3237 bil_bi_util_collection_pkg.writeLog
3238 (
3239 p_log_level => fnd_log.LEVEL_UNEXPECTED,
3240 p_module => g_pkg || l_proc || ' proc_error ',
3241 p_msg => fnd_message.get
3242 );
3243 Raise;
3244
3245 END Summary_err_check;
3246
3247 -- ---------------------------------------------------------------
3248 -- PROCEDURE REPORT_MISSING_RATES
3249 -- ---------------------------------------------------------------
3250 PROCEDURE REPORT_MISSING_RATES IS
3251 TYPE cursorType is REF CURSOR;
3252 l_curr CURSORTYPE;
3253 l_proc VARCHAR2(100);
3254
3255 CURSOR MissingRate_p IS
3256 SELECT
3257 rate.currency_code,
3258 TRUNC(DECODE(rate.exchange_rate,-3,
3259 TO_DATE('01/01/1999','MM/DD/RRRR'), LEAST(SYSDATE,report_date))) report_date,
3260 decode(sign(nvl(rate.exchange_rate,-1)),-1,'P') prim_curr_type,
3261 decode(sign(nvl(rate.exchange_rate_s,-1)),-1,'S') sec_curr_type
3262 FROM
3263 bil_bi_currency_rate rate,
3264 fii_time_day fday
3265 WHERE
3266 rate.exchange_date IN (SELECT DISTINCT TRUNC(submission_date) FROM bil_bi_new_fst_id)
3267 AND rate.exchange_date = fday.report_date
3268 AND ((exchange_rate < 0 OR exchange_rate IS NULL)
3269 OR (g_sec_currency IS NOT NULL AND (exchange_rate_s < 0 OR exchange_rate_s IS NULL)));
3270
3271 BEGIN
3272
3273 /* initialization of variable */
3274 l_proc := 'REPORT_MISSING_RATES.';
3275 /* end initialization of variable */
3276
3277 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
3278 bil_bi_util_collection_pkg.writeLog(
3279 p_log_level => fnd_log.LEVEL_PROCEDURE,
3280 p_module => g_pkg || l_proc || ' begin ',
3281 p_msg => ' Start of Procedure ' || l_proc||' For missing conversion ');
3282 END IF;
3283
3284 -------------------------------------------------
3285 -- Write out translated message to let user know
3286 -- there are missing exchange rate information
3287 -------------------------------------------------
3288 FII_MESSAGE.write_log(msg_name => 'BIS_DBI_CURR_PARTIAL_LOAD',token_num => 0);
3289
3290 BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
3291
3292 FOR rate_record in MissingRate_p LOOP
3293
3294 IF (rate_record.prim_curr_type = 'P') THEN
3295 BIS_COLLECTION_UTILITIES.writemissingrate(
3296 g_prim_rate_type,
3297 rate_record.currency_code,
3298 g_prim_currency,
3299 rate_record.report_date);
3300 END IF;
3301
3302 IF (rate_record.sec_curr_type='S') THEN
3303 BIS_COLLECTION_UTILITIES.writemissingrate(
3304 g_sec_rate_type,
3305 rate_record.currency_code,
3306 g_sec_currency,
3307 rate_record.report_date);
3308 END IF;
3309
3310 END LOOP;
3311
3312
3313 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
3314 bil_bi_util_collection_pkg.writeLog(
3315 p_log_level => fnd_log.LEVEL_PROCEDURE,
3316 p_module => g_pkg || l_proc || ' end ',
3317 p_msg => ' End of Procedure ' || l_proc);
3318 END IF;
3319
3320 EXCEPTION
3321
3322 WHEN OTHERS THEN
3323 g_retcode := -1;
3324 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
3325 fnd_message.set_token('ERRNO' ,SQLCODE);
3326 fnd_message.set_token('REASON', SQLERRM);
3327 fnd_message.set_token('ROUTINE' , l_proc);
3328 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
3329 p_module => g_pkg || l_proc || ' proc_error ',
3330 p_msg => fnd_message.get,
3331 p_force_log => TRUE);
3332 RAISE;
3333
3334 END REPORT_MISSING_RATES;
3335
3336
3337
3338
3339 PROCEDURE Validate_Setup (ret_status OUT NOCOPY BOOLEAN) IS
3340
3341 l_ret_status BOOLEAN;
3342 l_conv_rate_count NUMBER;
3343 l_min_date DATE;
3344 l_max_date DATE;
3345 l_gl_min_date DATE;
3346 l_gl_max_date DATE;
3347 l_miss_date BOOLEAN;
3348 l_proc VARCHAR2(100);
3349 l_fst_id NUMBER;
3350 l_fst_name VARCHAR2(100);
3351 l_number_of_rows NUMBER;
3352 l_cnt NUMBER;
3353 l_per_name VARCHAR2(100);
3354 l_stg_cnt NUMBER;
3355
3356
3357 CURSOR fst_prod_csr IS
3358 SELECT
3359 afsc1.forecast_category_id,
3360 afsc_tl.forecast_category_name
3361 FROM
3362 as_fst_sales_categories afsc1,
3363 as_forecast_categories_tl afsc_tl
3364 WHERE
3365 afsc1.forecast_category_id = afsc_tl.forecast_category_id
3366 AND afsc_tl.LANGUAGE = userenv('LANG')
3367 AND NVL(afsc1.end_date_active,SYSDATE) >= SYSDATE
3368 AND afsc1.start_date_active <= SYSDATE
3369 AND NOT(NVL(interest_type_id,-1)<0 AND product_category_id IS NULL)
3370 GROUP BY afsc1.forecast_category_id,
3371 afsc_tl.forecast_category_name
3372 HAVING COUNT(1) > 1;
3373
3374 CURSOR asf_bil_per_csr IS
3375 SELECT
3376 DISTINCT glp2.period_name
3377 FROM
3378 gl_periods glp1,
3379 gl_periods glp2
3380 WHERE glp1.period_set_name = g_cal
3381 AND glp2.period_set_name = g_asf_calendar
3382 AND glp2.period_name = glp1.period_name
3383 AND (glp1.start_date <> glp2.start_date OR glp1.end_date <> glp2.end_date);
3384
3385 BEGIN
3386
3387 /* initialization of variable */
3388 l_ret_status := TRUE;
3389 l_conv_rate_count := 0;
3390 l_miss_date := FALSE;
3391 l_proc := 'Validate_Setup.';
3392 l_number_of_rows := 0;
3393 l_cnt := 0;
3394 l_stg_cnt := 0;
3395 /* end initialization of variable */
3396
3397 /*
3398 Check to see if there are product category id as NULL!
3399 In such a case, error out.
3400 this is fix for bug - 3560477
3401 */
3402
3403 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
3404 bil_bi_util_collection_pkg.writeLog(
3405 p_log_level => fnd_log.LEVEL_PROCEDURE,
3406 p_module => g_pkg || l_proc || ' Start ',
3407 p_msg => ' Start of Procedure ' || l_proc);
3408 END IF;
3409
3410 l_cnt := 0; -- reset the counter
3411
3412
3413 SELECT
3414 COUNT(1)
3415 INTO l_cnt
3416 FROM
3417 as_fst_sales_categories afsc1
3418 WHERE
3419 NVL(afsc1.end_date_active,SYSDATE) >= SYSDATE
3420 AND afsc1.start_date_active <= SYSDATE
3421 AND afsc1.product_category_id IS NULL
3422 AND NVL(afsc1.interest_type_id,-1) > 0;
3423
3424
3425 IF(l_cnt>0) THEN -- Product categories are NULL in as_fst_sales_categories table
3426
3427 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3428 bil_bi_util_collection_pkg.writeLog
3429 (
3430 p_log_level => fnd_log.LEVEL_EVENT,
3431 p_module => g_pkg || l_proc,
3432 p_msg => 'There are product categories in as_fst_sales_categories with value NULL'
3433 );
3434 END IF;
3435
3436
3437 fnd_message.set_name('BIL','BIL_BI_FST_PROD_CAT_NUL_ERR');
3438 bil_bi_util_collection_pkg.writeLog
3439 (
3440 p_log_level => fnd_log.LEVEL_ERROR,
3441 p_module => g_pkg || l_proc || 'proc_error',
3442 p_msg => fnd_message.get
3443 );
3444
3445 FOR ro IN
3446 (
3447 SELECT
3448 afsc1.forecast_category_id,
3449 afsc_tl.forecast_category_name
3450 FROM
3451 as_fst_sales_categories afsc1,
3452 as_forecast_categories_tl afsc_tl
3453 WHERE
3454 afsc1.forecast_category_id = afsc_tl.forecast_category_id
3455 AND afsc_tl.LANGUAGE = userenv('LANG')
3456 and NVL(afsc1.end_date_active,SYSDATE) >= SYSDATE
3457 and afsc1.start_date_active <= SYSDATE
3458 and afsc1.product_category_id IS NULL
3459 AND NVL(afsc1.interest_type_id,-1) > 0
3460 GROUP BY
3461 afsc1.forecast_category_id,
3462 afsc_tl.forecast_category_name
3463 )
3464 LOOP
3465
3466
3467 fnd_message.set_name('BIL','BIL_BI_FST_CAT_MAP_ERR_DTL');
3468 fnd_message.set_token('FORECAST_CATEGORY_ID', ro.forecast_category_id);
3469 fnd_message.set_token('FORECAST_CATEGORY_NAME', ro.forecast_category_name);
3470 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3471 p_module => g_pkg || l_proc || 'proc_error',
3472 p_msg => fnd_message.get,
3473 p_force_log => TRUE);
3474
3475
3476 END LOOP;
3477
3478 l_ret_status:=FALSE;
3479 END IF;
3480
3481 -- end of product categry id IS NULL check
3482
3483 -- Check for OSO and BIL Calendar Mismatch
3484
3485 OPEN asf_bil_per_csr;
3486 LOOP
3487 FETCH asf_bil_per_csr
3488 INTO l_per_name;
3489 EXIT WHEN asf_bil_per_csr%NOTFOUND ;
3490 l_number_of_rows :=l_number_of_rows + 1;
3491
3492 IF(l_number_of_rows=1) THEN -- OS vs BIS Calendar Period Mismatch
3493 -- print header
3494 fnd_message.set_name('BIL','BIL_BI_SETUP_INCOMPLETE');
3495 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3496 p_module => g_pkg || l_proc || 'proc_error',
3497 p_msg => fnd_message.get,
3498 p_force_log => TRUE);
3499 fnd_message.set_name('BIL','BIL_BI_PER_MISMATCH_HDR');
3500 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3501 p_module => g_pkg || l_proc || 'proc_error',
3502 p_msg => fnd_message.get,
3503 p_force_log => TRUE);
3504 l_ret_status:=FALSE;
3505
3506 END IF;
3507 -- print detail
3508 fnd_message.set_name('BIL','BIL_BI_PER_MISMATCH_DTL');
3509 fnd_message.set_token('OFFENDING_PERIOD_NAME', l_per_name);
3510 bil_bi_util_collection_pkg.writeLog
3511 (
3512 p_log_level => fnd_log.LEVEL_ERROR,
3513 p_module => g_pkg || l_proc || 'proc_error',
3514 p_msg => fnd_message.get,
3515 p_force_log => TRUE
3516 );
3517 END LOOP;
3518 CLOSE asf_bil_per_csr;
3519
3520 -- Check Forecast and Product Category 1-to-1 Mapping
3521 -- If there are 1:M FC:PC mappings then warn that such data will not be collected.
3522 l_number_of_rows := 0; -- reset the counter
3523
3524
3525 IF (g_mode = 'INITIAL') THEN -- the 1:M check should be perfomed only for initial load
3526 OPEN fst_prod_csr;
3527 LOOP
3528 FETCH fst_prod_csr
3529 INTO l_fst_id,
3530 l_fst_name;
3531 EXIT WHEN fst_prod_csr%NOTFOUND ;
3532 l_number_of_rows :=l_number_of_rows + 1;
3533 IF(l_number_of_rows=1) THEN -- Forecast to Product Category Mapping Not 1-to-1
3534 -- print header
3535 fnd_message.set_name('BIL','BIL_BI_FST_CAT_MAP_ERR_HDR');
3536 fnd_message.set_token('ASN_IMPLEM_DATE', g_asn_date);
3537 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3538 p_module => g_pkg || l_proc || 'proc_error',
3539 p_msg => fnd_message.get,
3540 p_force_log => TRUE);
3541 g_warn_flag := 'Y';
3542 END IF;
3543 -- print detail
3544 fnd_message.set_name('BIL','BIL_BI_FST_CAT_MAP_ERR_DTL');
3545 fnd_message.set_token('FORECAST_CATEGORY_ID', l_fst_id);
3546 fnd_message.set_token('FORECAST_CATEGORY_NAME', l_fst_name);
3547 bil_bi_util_collection_pkg.writeLog
3548 (
3549 p_log_level => fnd_log.LEVEL_ERROR,
3550 p_module => g_pkg || l_proc || 'proc_error',
3551 p_msg => fnd_message.get,
3552 p_force_log => TRUE
3553 );
3554 END LOOP;
3555 CLOSE fst_prod_csr;
3556 END IF;
3557
3558 ret_status := l_ret_status;
3559
3560 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
3561 bil_bi_util_collection_pkg.writeLog(
3562 p_log_level => fnd_log.LEVEL_PROCEDURE,
3563 p_module => g_pkg || l_proc || ' end ',
3564 p_msg => ' End of Procedure ' || l_proc);
3565 END IF;
3566
3567
3568 EXCEPTION
3569
3570 WHEN OTHERS THEN
3571 g_retcode := -1;
3572 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
3573 fnd_message.set_token('ERRNO' ,SQLCODE);
3574 fnd_message.set_token('REASON', SQLERRM);
3575 fnd_message.set_token('ROUTINE' , l_proc);
3576 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
3577 p_module => g_pkg || l_proc || ' proc_error ',
3578 p_msg => fnd_message.get,
3579 p_force_log => TRUE);
3580 RAISE;
3581
3582
3583 END Validate_Setup;
3584
3585
3586 PROCEDURE Check_Profiles(ret_status OUT NOCOPY BOOLEAN) IS
3587 l_list DBMS_SQL.VARCHAR2_TABLE;
3588 l_val DBMS_SQL.VARCHAR2_TABLE;
3589 l_ret_status BOOLEAN;
3590 l_proc VARCHAR2(100);
3591
3592 BEGIN
3593
3594 /* initialization of variable */
3595 l_ret_status := FALSE;
3596 l_proc := 'Check_Profiles.';
3597 /* end initialization of variable */
3598
3599 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
3600 bil_bi_util_collection_pkg.writeLog(
3601 p_log_level => fnd_log.LEVEL_PROCEDURE,
3602 p_module => g_pkg || l_proc || ' begin',
3603 p_msg => 'Start of Procedure '|| l_proc);
3604 END IF;
3605
3606
3607 -- List of Profile for setup check
3608 l_list(1) := 'BIS_GLOBAL_START_DATE';
3609 l_list(2) := 'BIS_ENTERPRISE_CALENDAR';
3610 l_list(3) := 'BIS_PERIOD_TYPE';
3611 l_list(4) := 'BIL_BI_BASE_FST_PERIOD_TYPE';
3612 l_list(5) := 'BIL_BI_MAP_ENT_FST_PERIOD_TYPE';
3613 l_list(6) := 'BIL_BI_FST_ROLLUP';
3614
3615
3616 l_list(7) := 'ASN_FRCST_FORECAST_CALENDAR';
3617
3618 l_list(8) := 'BIS_PRIMARY_CURRENCY_CODE';
3619 l_list(9) := 'BIS_PRIMARY_RATE_TYPE';
3620
3621 -- Check if Profiles are setup
3622 IF (NOT bis_common_parameters.check_global_parameters(l_list)) THEN -- Check Parameters
3623 bis_common_parameters.get_global_parameters(l_list, l_val);
3624 l_ret_status := FALSE;
3625
3626 fnd_message.set_name('BIL','BIL_BI_SETUP_INCOMPLETE');
3627
3628 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3629 p_module => g_pkg || l_proc || 'proc_error',
3630 p_msg => fnd_message.get,
3631 p_force_log => TRUE);
3632 FOR v_counter IN 1..9 LOOP
3633 IF (l_val(v_counter) IS NULL) THEN
3634 fnd_message.set_name('BIL','BIL_BI_PROFILE_MISSING');
3635 fnd_message.set_token('PROFILE_USER_NAME' ,
3636 bil_bi_util_collection_pkg.get_user_profile_name(l_list(v_counter)));
3637 fnd_message.set_token('PROFILE_INTERNAL_NAME' ,l_list(v_counter));
3638
3639 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3640 p_module => g_pkg || l_proc || 'proc_error',
3641 p_msg => fnd_message.get,
3642 p_force_log => TRUE);
3643 END IF;
3644 END LOOP;
3645 ELSE
3646
3647 bis_common_parameters.get_global_parameters(l_list, l_val);
3648 l_ret_status := TRUE;
3649 END IF; -- Check Parameters Ends Here
3650
3651
3652 ------------------------------
3653 --Secondary currency chek
3654 ------------------------------
3655
3656 l_list(10) := 'BIS_SECONDARY_CURRENCY_CODE';
3657 l_list(11) := 'BIS_SECONDARY_RATE_TYPE';
3658 l_list(12) := 'BIL_BI_ASN_IMPLEMENTED';
3659
3660 -- reget all values with the 2 new profiles!!
3661 bis_common_parameters.get_global_parameters(l_list, l_val);
3662
3663 -- Assign the primary currency code and rate type to the corresponding gloabal variables
3664 g_prim_currency := l_val(8);
3665 g_prim_rate_type := l_val(9);
3666
3667 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
3668 bil_bi_util_collection_pkg.writeLog
3669 (
3670 p_log_level => fnd_log.LEVEL_STATEMENT,
3671 p_module => g_pkg || l_proc ,
3672 p_msg => 'prim curr : prim rate type = '||g_prim_currency||' : '||g_prim_rate_type
3673 );
3674 END IF;
3675
3676 -- sec curr not set up at all
3677 IF (l_val(10) IS NULL) THEN
3678 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
3679 bil_bi_util_collection_pkg.writeLog
3680 (p_log_level => fnd_log.LEVEL_STATEMENT,p_module => g_pkg || l_proc ,
3681 p_msg => ' Secondary curency not set up '
3682 );
3683 END IF;
3684 END IF;
3685
3686 -- sec curr set up but rate type not set up : ERROR
3687 IF (l_val(10) IS NOT NULL AND l_val(11) IS NULL ) THEN
3688 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
3689 bil_bi_util_collection_pkg.writeLog
3690 (p_log_level => fnd_log.LEVEL_STATEMENT,p_module => g_pkg || l_proc ,
3691 p_msg => ' Secondary curency set up but rate type not set up: ERROR '
3692 );
3693 END IF;
3694 IF(l_ret_status = FALSE) THEN-- already a profile error reported.. so dont print header etc
3695 fnd_message.set_name('BIL','BIL_BI_PROFILE_MISSING');
3696 fnd_message.set_token('PROFILE_USER_NAME' ,
3697 bil_bi_util_collection_pkg.get_user_profile_name(l_list(11)));
3698 fnd_message.set_token('PROFILE_INTERNAL_NAME' ,l_list(11));
3699 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3700 p_module => g_pkg || l_proc || 'proc_error',
3701 p_msg => fnd_message.get,
3702 p_force_log => TRUE); -- for fnd_message.get
3703 ELSE -- print error msg with header also
3704 l_ret_status := FALSE;
3705 fnd_message.set_name('BIL','BIL_BI_SETUP_INCOMPLETE');
3706 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3707 p_module => g_pkg || l_proc || 'proc_error',
3708 p_msg => fnd_message.get,
3709 p_force_log => TRUE); -- for fnd_message.get
3710 fnd_message.set_name('BIL','BIL_BI_PROFILE_MISSING');
3711 fnd_message.set_token('PROFILE_USER_NAME' ,
3712 bil_bi_util_collection_pkg.get_user_profile_name(l_list(11)));
3713 fnd_message.set_token('PROFILE_INTERNAL_NAME' ,l_list(11));
3714 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3715 p_module => g_pkg || l_proc || 'proc_error',
3716 p_msg => fnd_message.get,
3717 p_force_log => TRUE); -- for fnd_message.get
3718 END IF;
3719 END IF;
3720
3721 -- sec curr and rate type properly set up
3722 IF (l_val(10) IS NOT NULL AND l_val(11) IS NOT NULL ) THEN
3723
3724 g_sec_currency := bis_common_parameters.get_secondary_currency_code;
3725 g_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
3726
3727 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
3728 bil_bi_util_collection_pkg.writeLog
3729 (
3730 p_log_level => fnd_log.LEVEL_STATEMENT,
3731 p_module => g_pkg || l_proc ,
3732 p_msg => 'sec curr : rate type = '||g_sec_currency||' : '||g_sec_rate_type
3733 );
3734 END IF;
3735
3736 END IF;
3737
3738 IF (l_val(12) IS NULL OR l_val(12)='N' OR l_val(12)='NO' OR l_val(12)='Y' OR l_val(12)='YES') THEN
3739 --ASN implementaion date has not been setup
3740 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
3741 bil_bi_util_collection_pkg.writeLog
3742 (
3743 p_log_level => fnd_log.LEVEL_STATEMENT,
3744 p_module => g_pkg || l_proc ,
3745 p_msg => 'Oracle Sales Implementation date is needed for data collection purpose.'||
3746 ' Please specify a date and try again.'
3747 );
3748 END IF;
3749
3750 IF(l_ret_status = FALSE) THEN-- already a profile error reported.. so dont print header etc
3751 fnd_message.set_name('BIL','BIL_BI_PROFILE_MISSING');
3752 fnd_message.set_token('PROFILE_USER_NAME' ,
3753 bil_bi_util_collection_pkg.get_user_profile_name(l_list(12)));
3754 fnd_message.set_token('PROFILE_INTERNAL_NAME' ,l_list(12));
3755 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3756 p_module => g_pkg || l_proc || 'proc_error',
3757 p_msg => fnd_message.get,
3758 p_force_log => TRUE); -- for fnd_message.get
3759 ELSE -- print error msg with header also
3760 l_ret_status := FALSE;
3761 fnd_message.set_name('BIL','BIL_BI_SETUP_INCOMPLETE');
3762 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3763 p_module => g_pkg || l_proc || 'proc_error',
3764 p_msg => fnd_message.get,
3765 p_force_log => TRUE); -- for fnd_message.get
3766 fnd_message.set_name('BIL','BIL_BI_PROFILE_MISSING');
3767 fnd_message.set_token('PROFILE_USER_NAME' ,
3768 bil_bi_util_collection_pkg.get_user_profile_name(l_list(12)));
3769 fnd_message.set_token('PROFILE_INTERNAL_NAME' ,l_list(12));
3770 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3771 p_module => g_pkg || l_proc || 'proc_error',
3772 p_msg => fnd_message.get,
3773 p_force_log => TRUE); -- for fnd_message.get
3774 END IF;
3775
3776 ELSE
3777
3778 -- There is a valid ASN date
3779 g_asn_date := TO_DATE(l_val(12),'MM/DD/YYYY');
3780 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
3781 bil_bi_util_collection_pkg.writeLog
3782 (
3783 p_log_level => fnd_log.LEVEL_STATEMENT,
3784 p_module => g_pkg || l_proc ,
3785 p_msg => 'ASN implemented. Date = '||g_asn_date
3786 );
3787 END IF;
3788
3789 -- New logic
3790 -- Make sure that the ASN implementation is <= sysdate
3791 -- This check to be enforced only for INITIAL collection
3792
3793 IF ((g_mode = 'INITIAL') AND (g_asn_date > trunc(sysdate))) THEN
3794
3795 -- Set the status to error
3796 l_ret_status := FALSE;
3797
3798 -- Log a message in the CP log file and the CP ouput file
3799 fnd_message.set_name('BIL','BIL_BI_FUTURE_ASN_DATE');
3800 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
3801 p_module => g_pkg || l_proc || 'proc_error',
3802 p_msg => fnd_message.get,
3803 p_force_log => TRUE); -- for fnd_message.get
3804 END IF;
3805
3806 END IF;
3807
3808
3809 ret_status := l_ret_status;
3810
3811 g_cal_start_date := TO_DATE(l_val(1), 'MM/DD/YYYY');
3812 g_cal := l_val(2);
3813 g_cal_per_type := l_val(3);
3814 g_asf_calendar := l_val(7);
3815 g_fsct_per_type := l_val(4);
3816 g_map_ent_per_type := l_val(5);
3817 g_fst_rollup := l_val(6);
3818
3819 FOR i IN 1..11 LOOP
3820 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
3821 bil_bi_util_collection_pkg.writeLog(
3822 p_log_level => fnd_log.LEVEL_EVENT,
3823 p_module => g_pkg || l_proc || ' prof',
3824 p_msg => 'Profile Name: '||l_list(i)||'<-> Value: '||l_val(i));
3825 END IF;
3826 END LOOP;
3827
3828 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
3829 bil_bi_util_collection_pkg.writeLog(
3830 p_log_level => fnd_log.LEVEL_PROCEDURE,
3831 p_module => g_pkg || l_proc || ' end',
3832 p_msg => 'End of Procedure '|| l_proc);
3833 END IF;
3834
3835
3836 EXCEPTION
3837 WHEN OTHERS THEN
3838
3839 g_retcode := -1;
3840 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
3841 fnd_message.set_token('ERRNO' ,SQLCODE);
3842 fnd_message.set_token('REASON' ,SQLERRM);
3843 fnd_message.set_token('ROUTINE' , l_proc);
3844 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
3845 p_module => g_pkg || l_proc || 'proc_error',
3846 p_msg => fnd_message.get,
3847 p_force_log => TRUE);
3848
3849 RAISE;
3850
3851 END Check_Profiles;
3852
3853
3854 END BIL_BI_FST_DTL_F_PKG;