[Home] [Help]
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);
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:
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,
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: );
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);
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:
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);
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:
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,
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: );
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);
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:
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);
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:
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: );
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: );
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: );
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 ',
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:
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:
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:
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:
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 ',
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: );
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);
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:
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 ,
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
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 ,
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
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);
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:
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 ,
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;
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);
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:
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);
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:
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);
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
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 ,
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
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 ,
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;
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);
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:
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 ,
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;
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,
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;
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,
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;
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');
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:
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 ,
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;
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 ,
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;
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);
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:
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);
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:
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);
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:
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);
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);
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 ,
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;
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);
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:
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);
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:
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',
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: );
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;
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);
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:
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,
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;
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,
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;
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');
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);
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:
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;
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);
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:
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');
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;
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');
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:
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 ,
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;
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 ,
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: );
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 ,
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:
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);
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:
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);
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:
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');
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);
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
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;
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);
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:
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,
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;
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,
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;
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,
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;
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);
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:
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:
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);
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:
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');
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:
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);
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:
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;
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);
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:
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);
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:
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,
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;
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,
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;
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');
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:
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);
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:
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;
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);
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:
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');
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:
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);
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:
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:
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);
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:
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,
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;
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);
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:
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);
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:
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');
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:
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');
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:
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,
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;
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');
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:
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);
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:
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:
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);
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:
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);
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:
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, '
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');
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,
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;
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,
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;
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,
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;
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.');
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;
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');
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!');
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:
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);
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:
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;
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 ');
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:
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);
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:
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;
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);
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:
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,
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;
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:
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:
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');
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;
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: );
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';
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: );
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);
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:
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;
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);
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:
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
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;
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 ,
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;
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: );
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;
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: );
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
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');
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;
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 ,
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;
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 ,
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: );
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
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');
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;
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 ,
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;
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;
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));
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;
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);
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:
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: