[Home] [Help]
15: g_bis_setup_exception exception;
16: g_global_start_date date;
17: g_max_date constant date := to_date('4712/12/31','yyyy/mm/dd');
18:
19: procedure bis_collection_utilities_log
20: ( m varchar2, indent number default null )
21: as
22: begin
23:
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;
135:
136: begin
137:
138: l_refresh_date := fnd_date.displaydt_to_date
139: ( bis_collection_utilities.get_last_refresh_period
140: ( nvl(p_object_name,g_object_name) )
141: );
142: if l_refresh_date = g_global_start_date then
143: x_error_message := 'Incremental Load can only be run after a completed initial or incremental load';
179: begin
180:
181: local_init;
182:
183: bis_collection_utilities_log( 'Begin Initial Load' );
184:
185: l_stmt_id := 0;
186: if not bis_collection_utilities.setup( g_object_name ) then
187: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
182:
183: bis_collection_utilities_log( 'Begin Initial Load' );
184:
185: l_stmt_id := 0;
186: if not bis_collection_utilities.setup( g_object_name ) then
187: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
188: logger( l_proc_name, l_stmt_id, l_error_message );
189: raise g_bis_setup_exception;
190: end if;
183: bis_collection_utilities_log( 'Begin Initial Load' );
184:
185: l_stmt_id := 0;
186: if not bis_collection_utilities.setup( g_object_name ) then
187: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
188: logger( l_proc_name, l_stmt_id, l_error_message );
189: raise g_bis_setup_exception;
190: end if;
191:
208: logger( l_proc_name, l_stmt_id, l_error_message );
209: raise l_exception;
210: end if;
211:
212: bis_collection_utilities_log( 'From ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
213: bis_collection_utilities_log( 'To ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
214:
215: -- get the isc schema name
216: l_stmt_id := 30;
209: raise l_exception;
210: end if;
211:
212: bis_collection_utilities_log( 'From ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
213: bis_collection_utilities_log( 'To ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
214:
215: -- get the isc schema name
216: l_stmt_id := 30;
217: if get_schema_name
230: logger( l_proc_name, l_stmt_id, l_error_message );
231: raise l_exception;
232: end if;
233:
234: bis_collection_utilities_log( 'Task Activity base summary table truncated', 1 );
235:
236: -- truncate the isc_fs_task_backlog_f fact table
237: l_stmt_id := 50;
238: if truncate_table
242: logger( l_proc_name, l_stmt_id, l_error_message );
243: raise l_exception;
244: end if;
245:
246: bis_collection_utilities_log( 'Task Backlog base summary table truncated', 1 );
247:
248: -- insert into base fact tables
249: l_stmt_id := 60;
250:
608: and a.old_task_status_id = s_old.task_status_id(+);
609:
610: l_rowcount := sql%rowcount;
611:
612: bis_collection_utilities_log( l_rowcount || ' rows inserted into base summaries', 1 );
613:
614: commit;
615:
616: l_stmt_id := 70;
613:
614: commit;
615:
616: l_stmt_id := 70;
617: bis_collection_utilities.wrapup( p_status => true
618: , p_period_from => l_collect_from_date
619: , p_period_to => l_collect_to_date
620: , p_count => l_rowcount
621: );
619: , p_period_to => l_collect_to_date
620: , p_count => l_rowcount
621: );
622:
623: bis_collection_utilities_log('End Initial Load');
624:
625: errbuf := null;
626: retcode := g_success;
627:
629: when g_bis_setup_exception then
630: rollback;
631: errbuf := l_error_message;
632: retcode := g_error;
633: bis_collection_utilities_log('End Initial Load with Error');
634:
635: when l_exception then
636: rollback;
637: if l_error_message is null then
636: rollback;
637: if l_error_message is null then
638: l_error_message := substr(sqlerrm,1,4000);
639: end if;
640: bis_collection_utilities.wrapup( p_status => false
641: , p_message => l_error_message
642: , p_period_from => l_collect_from_date
643: , p_period_to => l_collect_to_date
644: );
643: , p_period_to => l_collect_to_date
644: );
645: errbuf := l_error_message;
646: retcode := g_error;
647: bis_collection_utilities_log('End Initial Load with Error');
648:
649: when others then
650: rollback;
651: if l_error_message is null then
651: if l_error_message is null then
652: l_error_message := substr(sqlerrm,1,4000);
653: end if;
654: logger( l_proc_name, l_stmt_id, l_error_message );
655: bis_collection_utilities.wrapup( p_status => false
656: , p_message => l_error_message
657: , p_period_from => l_collect_from_date
658: , p_period_to => l_collect_to_date
659: );
658: , p_period_to => l_collect_to_date
659: );
660: errbuf := l_error_message;
661: retcode := g_error;
662: bis_collection_utilities_log('End Initial Load with Error');
663:
664: end initial_load;
665:
666: procedure incremental_load
709: begin
710:
711: local_init;
712:
713: bis_collection_utilities_log( 'Begin Incremental Load' );
714:
715: l_stmt_id := 0;
716: if not bis_collection_utilities.setup( g_object_name ) then
717: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
712:
713: bis_collection_utilities_log( 'Begin Incremental Load' );
714:
715: l_stmt_id := 0;
716: if not bis_collection_utilities.setup( g_object_name ) then
717: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
718: logger( l_proc_name, l_stmt_id, l_error_message );
719: raise g_bis_setup_exception;
720: end if;
713: bis_collection_utilities_log( 'Begin Incremental Load' );
714:
715: l_stmt_id := 0;
716: if not bis_collection_utilities.setup( g_object_name ) then
717: l_error_message := 'Error in BIS_COLLECTION_UTILITIES.Setup';
718: logger( l_proc_name, l_stmt_id, l_error_message );
719: raise g_bis_setup_exception;
720: end if;
721:
740: logger( l_proc_name, l_stmt_id, l_error_message );
741: raise l_exception;
742: end if;
743:
744: bis_collection_utilities_log( 'From: ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
745: bis_collection_utilities_log( 'To: ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
746:
747: if l_collect_from_date >= l_collect_to_date then
748:
741: raise l_exception;
742: end if;
743:
744: bis_collection_utilities_log( 'From: ' || fnd_date.date_to_displaydt(l_collect_from_date), 1 );
745: bis_collection_utilities_log( 'To: ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
746:
747: if l_collect_from_date >= l_collect_to_date then
748:
749: bis_collection_utilities_log( 'Nothing to process', 2 );
745: bis_collection_utilities_log( 'To: ' || fnd_date.date_to_displaydt(l_collect_to_date), 1 );
746:
747: if l_collect_from_date >= l_collect_to_date then
748:
749: bis_collection_utilities_log( 'Nothing to process', 2 );
750: bis_collection_utilities.wrapup( p_status => true
751: , p_period_from => l_collect_from_date
752: , p_period_to => l_collect_to_date
753: , p_count => 0
746:
747: if l_collect_from_date >= l_collect_to_date then
748:
749: bis_collection_utilities_log( 'Nothing to process', 2 );
750: bis_collection_utilities.wrapup( p_status => true
751: , p_period_from => l_collect_from_date
752: , p_period_to => l_collect_to_date
753: , p_count => 0
754: );
752: , p_period_to => l_collect_to_date
753: , p_count => 0
754: );
755:
756: bis_collection_utilities_log('End Incremental Load');
757: errbuf := null;
758: retcode := g_success;
759: return;
760: end if;
777: logger( l_proc_name, l_stmt_id, l_error_message );
778: raise l_exception;
779: end if;
780:
781: bis_collection_utilities_log( 'Staging table truncated', 1 );
782:
783: bis_collection_utilities_log( 'Inserting Task audit history into staging table', 1 );
784:
785: --
779: end if;
780:
781: bis_collection_utilities_log( 'Staging table truncated', 1 );
782:
783: bis_collection_utilities_log( 'Inserting Task audit history into staging table', 1 );
784:
785: --
786: -- insert rows based on tasks created or tasks updated
787: --
1020: --
1021:
1022: l_rowcount := sql%rowcount;
1023:
1024: bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1025:
1026: commit;
1027:
1028: bis_collection_utilities_log( 'Inserting beginning task backlog into staging table', 1 );
1024: bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1025:
1026: commit;
1027:
1028: bis_collection_utilities_log( 'Inserting beginning task backlog into staging table', 1 );
1029:
1030: -- insert a row for each task in the latest backlog
1031: -- this row will later be compared with the subsequent
1032: -- last row for day for the same task to determine if
1104: and b.task_id = t.task_id;
1105:
1106: l_rowcount := sql%rowcount;
1107:
1108: bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1109:
1110: commit;
1111:
1112: -- gather stats on staging table
1118: logger( l_proc_name, l_stmt_id, l_error_message );
1119: raise l_exception;
1120: end if;
1121:
1122: bis_collection_utilities_log( 'Gathered statistics on staging table', 1 );
1123:
1124: -- we need to deal with the case where the task has had a task assignement
1125: -- added or cancelled (even deleted) after the last audit row for that task.
1126: -- not sure how likely this is as testing has shown that task is updated when
1141: -- by adding an assignment to the task we need to recompute backlog_status_code.
1142: -- the reverse hold true, if the task assignment is cancelled, we may need to
1143: -- move backlog_status_code from WORKING etc back to IN PLANNING.
1144:
1145: bis_collection_utilities_log( 'Inserting closing backlog status into staging table', 1 );
1146:
1147: l_stmt_id := 80;
1148: insert into isc_fs_task_act_bac_stg
1149: ( task_id
1257: end;
1258:
1259: l_rowcount := sql%rowcount;
1260:
1261: bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1262:
1263: commit;
1264:
1265: bis_collection_utilities_log( 'Hiding ''duplicate'' rows from backlog query', 1 );
1261: bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1262:
1263: commit;
1264:
1265: bis_collection_utilities_log( 'Hiding ''duplicate'' rows from backlog query', 1 );
1266:
1267: -- hide 'duplicate' rows from backlog query
1268: l_stmt_id := 90;
1269: update isc_fs_task_act_bac_stg
1296: );
1297:
1298: l_temp_rowcount := sql%rowcount;
1299:
1300: bis_collection_utilities_log(l_temp_rowcount || ' rows updated',2);
1301:
1302: commit;
1303:
1304: bis_collection_utilities_log('Staging table complete');
1300: bis_collection_utilities_log(l_temp_rowcount || ' rows updated',2);
1301:
1302: commit;
1303:
1304: bis_collection_utilities_log('Staging table complete');
1305:
1306: -- ---------------------------------------------- --
1307: -- do not issue another commit until we are done!
1308: -- ---------------------------------------------- --
1306: -- ---------------------------------------------- --
1307: -- do not issue another commit until we are done!
1308: -- ---------------------------------------------- --
1309:
1310: bis_collection_utilities_log( 'Updating changes to denormalized data for existing rows', 1 );
1311:
1312: -- pick up changes in denormalized columns from isc_fs_tasks_f
1313: -- for existing activity and end dated backlog
1314: l_stmt_id := 92;
1394: l_temp_rowcount := l_temp_rowcount + sql%rowcount;
1395:
1396: end loop;
1397:
1398: bis_collection_utilities_log(l_rowcount || ' rows updated in activity base summary',2);
1399: bis_collection_utilities_log(l_temp_rowcount || ' rows updated in backlog base summary',2);
1400:
1401: bis_collection_utilities_log( 'Calculating changes to previous current task backlog rows', 1 );
1402:
1395:
1396: end loop;
1397:
1398: bis_collection_utilities_log(l_rowcount || ' rows updated in activity base summary',2);
1399: bis_collection_utilities_log(l_temp_rowcount || ' rows updated in backlog base summary',2);
1400:
1401: bis_collection_utilities_log( 'Calculating changes to previous current task backlog rows', 1 );
1402:
1403: --
1397:
1398: bis_collection_utilities_log(l_rowcount || ' rows updated in activity base summary',2);
1399: bis_collection_utilities_log(l_temp_rowcount || ' rows updated in backlog base summary',2);
1400:
1401: bis_collection_utilities_log( 'Calculating changes to previous current task backlog rows', 1 );
1402:
1403: --
1404: -- determine if the previous backlog row needs to be closed off
1405: -- as there is a subsequent row for the same task, either closed or
1433: and lead_audit_date is not null
1434: and ( lead_backlog_status_code <> backlog_status_code or
1435: lead_status_flag <> status_flag );
1436:
1437: bis_collection_utilities_log( 'Updating changed previous current task backlog rows', 2 );
1438: --
1439: -- updated the previous backlog row that need to be closed off
1440: -- as there is a subsequent row for the same task, either closed or
1441: -- open with different properties
1455: where rowid = l_rowid_tbl(i);
1456:
1457: l_rowcount := sql%rowcount;
1458:
1459: bis_collection_utilities_log( l_rowid_tbl.count || ' rows updated', 3 );
1460:
1461: bis_collection_utilities_log( 'Inserting activity', 1 );
1462:
1463: --
1457: l_rowcount := sql%rowcount;
1458:
1459: bis_collection_utilities_log( l_rowid_tbl.count || ' rows updated', 3 );
1460:
1461: bis_collection_utilities_log( 'Inserting activity', 1 );
1462:
1463: --
1464: -- insert the activity rows into isc_fs_task_activity_f
1465: --
1533: );
1534:
1535: l_rowcount := sql%rowcount;
1536:
1537: bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1538:
1539: bis_collection_utilities_log( 'Inserting backlog history', 1 );
1540:
1541: --
1535: l_rowcount := sql%rowcount;
1536:
1537: bis_collection_utilities_log( l_rowcount || ' rows inserted', 2 );
1538:
1539: bis_collection_utilities_log( 'Inserting backlog history', 1 );
1540:
1541: --
1542: -- insert the new backlog rows into isc_fs_task_backlog_f
1543: --
1636: and last_row_for_day_flag = 'Y';
1637:
1638: l_temp_rowcount := sql%rowcount;
1639:
1640: bis_collection_utilities_log( l_temp_rowcount || ' rows inserted', 2 );
1641:
1642: l_rowcount := l_rowcount + l_temp_rowcount;
1643:
1644: commit;
1652: logger( l_proc_name, l_stmt_id, l_error_message );
1653: raise l_exception;
1654: end if;
1655:
1656: bis_collection_utilities_log( 'Staging table truncated', 1 );
1657:
1658: l_stmt_id := 150;
1659: bis_collection_utilities.wrapup( p_status => true
1660: , p_period_from => l_collect_from_date
1655:
1656: bis_collection_utilities_log( 'Staging table truncated', 1 );
1657:
1658: l_stmt_id := 150;
1659: bis_collection_utilities.wrapup( p_status => true
1660: , p_period_from => l_collect_from_date
1661: , p_period_to => l_collect_to_date
1662: , p_count => l_rowcount
1663: );
1661: , p_period_to => l_collect_to_date
1662: , p_count => l_rowcount
1663: );
1664:
1665: bis_collection_utilities_log('End Incremental Load');
1666:
1667: errbuf := null;
1668: retcode := g_success;
1669:
1671: when g_bis_setup_exception then
1672: rollback;
1673: errbuf := l_error_message;
1674: retcode := g_error;
1675: bis_collection_utilities_log('End Incremential Load with Error');
1676:
1677: when l_exception then
1678: rollback;
1679: if l_error_message is null then
1678: rollback;
1679: if l_error_message is null then
1680: l_error_message := substr(sqlerrm,1,4000);
1681: end if;
1682: bis_collection_utilities.wrapup( p_status => false
1683: , p_message => l_error_message
1684: , p_period_from => l_collect_from_date
1685: , p_period_to => l_collect_to_date
1686: );
1685: , p_period_to => l_collect_to_date
1686: );
1687: errbuf := l_error_message;
1688: retcode := g_error;
1689: bis_collection_utilities_log('End Incremential Load with Error');
1690:
1691: when others then
1692: rollback;
1693: if l_error_message is null then
1693: if l_error_message is null then
1694: l_error_message := substr(sqlerrm,1,4000);
1695: end if;
1696: logger( l_proc_name, l_stmt_id, l_error_message );
1697: bis_collection_utilities.wrapup( p_status => false
1698: , p_message => l_error_message
1699: , p_period_from => l_collect_from_date
1700: , p_period_to => l_collect_to_date
1701: );
1700: , p_period_to => l_collect_to_date
1701: );
1702: errbuf := l_error_message;
1703: retcode := g_error;
1704: bis_collection_utilities_log('End Incremential Load with Error');
1705:
1706: end incremental_load;
1707:
1708: end isc_fs_task_act_bac_etl_pkg;