[Home] [Help]
16: g_global_start_date date;
17: g_object_name constant varchar2(30) := 'ISC_FS_TASK_BAC_DATES_F';
18: g_max_date constant date := to_date('4712/01/01','yyyy/mm/dd');
19:
20: procedure bis_collection_utilities_log
21: ( m varchar2, indent number default null )
22: as
23: begin
24: --if indent is not null then
27: -- end loop;
28: --end if;
29: --dbms_output.put_line(substr(m,1,254));
30:
31: bis_collection_utilities.log( substr(m,1,2000-(nvl(indent,0)*3)), nvl(indent,0) );
32:
33: end bis_collection_utilities_log;
34:
35: procedure local_init
29: --dbms_output.put_line(substr(m,1,254));
30:
31: bis_collection_utilities.log( substr(m,1,2000-(nvl(indent,0)*3)), nvl(indent,0) );
32:
33: end bis_collection_utilities_log;
34:
35: procedure local_init
36: as
37: begin
50: , p_message varchar2
51: )
52: as
53: begin
54: bis_collection_utilities_log( g_pkg_name || '.' || p_proc_name ||
55: ' #' || p_stmt_id || ' ' ||
56: p_message
57: , 3 );
58: end logger;
133: l_refresh_date date;
134:
135: begin
136:
137: l_refresh_date := fnd_date.displaydt_to_date(bis_collection_utilities.get_last_refresh_period(p_object_name));
138: x_refresh_date := l_refresh_date;
139: return 0;
140:
141: exception
166:
167: l_collect_to_date := p_collect_to_date;
168: l_collect_to_date_trunc := trunc(p_collect_to_date);
169:
170: bis_collection_utilities_log( 'Begin current backlog age dates load', 1 );
171:
172: -- check last as at date if doing incremental_load
173: l_stmt_id := 10;
174: if p_mode = 'incremental_load' then
191: , program_application_id = g_program_application_id
192: , request_id = g_request_id
193: where aging_date = l_max_aging_date;
194:
195: bis_collection_utilities_log( 'Previous current as at date row updated in task current backlog age dates table', 2 );
196:
197: bis_collection_utilities_log( 'End current backlog age dates load', 1 );
198:
199: return 0;
193: where aging_date = l_max_aging_date;
194:
195: bis_collection_utilities_log( 'Previous current as at date row updated in task current backlog age dates table', 2 );
196:
197: bis_collection_utilities_log( 'End current backlog age dates load', 1 );
198:
199: return 0;
200:
201: end if;
209: logger( l_proc_name, l_stmt_id, x_error_message );
210: return -1;
211: end if;
212:
213: bis_collection_utilities_log( 'Task current backlog age dates table truncated', 2 );
214:
215: -- insert into task current backlog age dates tables
216: l_stmt_id := 40;
217:
412: group by aging_date;
413:
414: x_rowcount := sql%rowcount;
415:
416: bis_collection_utilities_log( x_rowcount || ' rows inserted into task current backlog age dates table', 2 );
417:
418: commit;
419:
420: -- gather stats for staging table
426: logger( l_proc_name, l_stmt_id, x_error_message );
427: return -1;
428: end if;
429:
430: bis_collection_utilities_log( 'Gathered stats for current backlog age dates table', 2 );
431:
432: bis_collection_utilities_log( 'End current backlog age dates load', 1 );
433:
434: return 0;
428: end if;
429:
430: bis_collection_utilities_log( 'Gathered stats for current backlog age dates table', 2 );
431:
432: bis_collection_utilities_log( 'End current backlog age dates load', 1 );
433:
434: return 0;
435:
436: exception
464: begin
465:
466: local_init;
467:
468: bis_collection_utilities_log( 'Begin Initial Load' );
469:
470: l_stmt_id := 0;
471: if not bis_collection_utilities.setup( g_object_name ) then
472: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
467:
468: bis_collection_utilities_log( 'Begin Initial Load' );
469:
470: l_stmt_id := 0;
471: if not bis_collection_utilities.setup( g_object_name ) then
472: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
473: logger( l_proc_name, l_stmt_id, l_error_message );
474: raise g_bis_setup_exception;
475: end if;
468: bis_collection_utilities_log( 'Begin Initial Load' );
469:
470: l_stmt_id := 0;
471: if not bis_collection_utilities.setup( g_object_name ) then
472: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
473: logger( l_proc_name, l_stmt_id, l_error_message );
474: raise g_bis_setup_exception;
475: end if;
476:
490: end if;
491:
492: l_collect_from_date := l_collect_to_date;
493:
494: bis_collection_utilities_log( 'As At ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
495:
496: -- get the isc schema name
497: l_stmt_id := 20;
498: if get_schema_name
523: logger( l_proc_name, l_stmt_id, l_error_message );
524: raise l_exception;
525: end if;
526:
527: bis_collection_utilities_log( 'Task backlog age dates base summary table truncated', 1 );
528:
529: -- insert into isc_fs_task_bac_dates_f fact table
530: l_stmt_id := 50;
531: insert /*+ append f */
560: isc_fs_task_bac_dates_c;
561:
562: l_temp_rowcount := sql%rowcount;
563:
564: bis_collection_utilities_log( l_temp_rowcount || ' rows inserted into task backlog age dates base summary table', 1 );
565:
566: l_rowcount := nvl(l_rowcount,0) + l_temp_rowcount;
567:
568: commit;
567:
568: commit;
569:
570: l_stmt_id := 60;
571: bis_collection_utilities.wrapup( p_status => true
572: , p_period_from => l_collect_from_date
573: , p_period_to => l_collect_to_date
574: , p_count => l_rowcount
575: );
573: , p_period_to => l_collect_to_date
574: , p_count => l_rowcount
575: );
576:
577: bis_collection_utilities_log('End Initial Load');
578:
579: errbuf := null;
580: retcode := g_success;
581:
583: when g_bis_setup_exception then
584: rollback;
585: errbuf := l_error_message;
586: retcode := g_error;
587: bis_collection_utilities_log('End Initial Load with Error');
588:
589: when l_exception then
590: rollback;
591: if l_error_message is null then
590: rollback;
591: if l_error_message is null then
592: l_error_message := substr(sqlerrm,1,4000);
593: end if;
594: bis_collection_utilities.wrapup( p_status => false
595: , p_message => l_error_message
596: , p_period_from => l_collect_from_date
597: , p_period_to => l_collect_to_date
598: );
597: , p_period_to => l_collect_to_date
598: );
599: errbuf := l_error_message;
600: retcode := g_error;
601: bis_collection_utilities_log('End Initial Load with Error');
602:
603: when others then
604: rollback;
605: if l_error_message is null then
605: if l_error_message is null then
606: l_error_message := substr(sqlerrm,1,4000);
607: end if;
608: logger( l_proc_name, l_stmt_id, l_error_message );
609: bis_collection_utilities.wrapup( p_status => false
610: , p_message => l_error_message
611: , p_period_from => l_collect_from_date
612: , p_period_to => l_collect_to_date
613: );
612: , p_period_to => l_collect_to_date
613: );
614: errbuf := l_error_message;
615: retcode := g_error;
616: bis_collection_utilities_log('End Initial Load with Error');
617:
618: end initial_load;
619:
620: procedure incremental_load
638: begin
639:
640: local_init;
641:
642: bis_collection_utilities_log( 'Begin Incremental Load' );
643:
644: l_stmt_id := 0;
645: if not bis_collection_utilities.setup( g_object_name ) then
646: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
641:
642: bis_collection_utilities_log( 'Begin Incremental Load' );
643:
644: l_stmt_id := 0;
645: if not bis_collection_utilities.setup( g_object_name ) then
646: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
647: logger( l_proc_name, l_stmt_id, l_error_message );
648: raise g_bis_setup_exception;
649: end if;
642: bis_collection_utilities_log( 'Begin Incremental Load' );
643:
644: l_stmt_id := 0;
645: if not bis_collection_utilities.setup( g_object_name ) then
646: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
647: logger( l_proc_name, l_stmt_id, l_error_message );
648: raise g_bis_setup_exception;
649: end if;
650:
664: end if;
665:
666: l_collect_from_date := l_collect_to_date;
667:
668: bis_collection_utilities_log( 'As At ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
669:
670: -- get the isc schema name
671: l_stmt_id := 20;
672: if get_schema_name
694: where report_date = (select max(report_date) from isc_fs_task_bac_dates_f)
695: and aging_date <> l_collect_to_date;
696:
697: if sql%rowcount > 0 then
698: bis_collection_utilities_log( 'Previous current as at date row deleted from task backlog age dates base summary table', 1 );
699: end if;
700:
701: -- cleanup isc_fs_task_bac_dates_f, remove old rows that are not for period ends
702: l_stmt_id := 50;
704: where report_date not in (select trunc(aging_date) from isc_fs_task_bac_dates_c);
705:
706: l_temp_rowcount := sql%rowcount;
707:
708: bis_collection_utilities_log( l_temp_rowcount || ' rows deleted from task backlog age dates base summary table', 1 );
709: l_rowcount := l_rowcount + l_temp_rowcount;
710:
711: -- insert into isc_fs_task_bac_dates_f fact table
712: l_stmt_id := 60;
744: trunc(aging_date) not in (select report_date from isc_fs_task_bac_dates_f);
745:
746: l_temp_rowcount := sql%rowcount;
747:
748: bis_collection_utilities_log( l_temp_rowcount || ' rows inserted into task backlog age dates base summary table', 1 );
749: l_rowcount := l_rowcount + l_temp_rowcount;
750:
751: commit;
752:
750:
751: commit;
752:
753: l_stmt_id := 50;
754: bis_collection_utilities.wrapup( p_status => true
755: , p_period_from => l_collect_from_date
756: , p_period_to => l_collect_to_date
757: , p_count => l_rowcount
758: );
756: , p_period_to => l_collect_to_date
757: , p_count => l_rowcount
758: );
759:
760: bis_collection_utilities_log('End Incremental Load');
761:
762: errbuf := null;
763: retcode := g_success;
764:
766: when g_bis_setup_exception then
767: rollback;
768: errbuf := l_error_message;
769: retcode := g_error;
770: bis_collection_utilities_log('End Incremental Load with Error');
771:
772: when l_exception then
773: rollback;
774: if l_error_message is null then
773: rollback;
774: if l_error_message is null then
775: l_error_message := substr(sqlerrm,1,4000);
776: end if;
777: bis_collection_utilities.wrapup( p_status => false
778: , p_message => l_error_message
779: , p_period_from => l_collect_from_date
780: , p_period_to => l_collect_to_date
781: );
780: , p_period_to => l_collect_to_date
781: );
782: errbuf := l_error_message;
783: retcode := g_error;
784: bis_collection_utilities_log('End Incremental Load with Error');
785:
786: when others then
787: rollback;
788: if l_error_message is null then
788: if l_error_message is null then
789: l_error_message := substr(sqlerrm,1,4000);
790: end if;
791: logger( l_proc_name, l_stmt_id, l_error_message );
792: bis_collection_utilities.wrapup( p_status => false
793: , p_message => l_error_message
794: , p_period_from => l_collect_from_date
795: , p_period_to => l_collect_to_date
796: );
795: , p_period_to => l_collect_to_date
796: );
797: errbuf := l_error_message;
798: retcode := g_error;
799: bis_collection_utilities_log('End Incremental Load with Error');
800:
801: end incremental_load;
802:
803: function get_period_bit_tbl