PACKAGE BODY: APPS.ISC_EDW_BOOK_DEL_HOOK

Source

1 PACKAGE BODY ISC_EDW_BOOK_DEL_HOOK AS
2 /*$Header: ISCHK00B.pls 115.4 2004/03/15 09:56:36 visgupta noship $ */
3 
4 PROCEDURE POST_FACT_COLL IS
5 
6   l_number_rows		NUMBER;
7 
8   l_isc_owner		VARCHAR2(40);
9   l_stmt 		VARCHAR2(4000);
10 
11 BEGIN
12 
13   EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('Post Collection Hook for the Bookings Fact');
14 
15   l_isc_owner := EDW_OWB_COLLECTION_UTIL.Get_Db_User('ISC');
16 
17   EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('');
18   EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('Going to truncate ISC_EDW_BOOK_DEL table');
19 
20   l_stmt := 'TRUNCATE TABLE '||l_isc_owner||'.ISC_EDW_BOOK_DEL';
21   EXECUTE IMMEDIATE l_stmt;
22 
23   EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('');
24   EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('Truncation of the table ISC_EDW_BOOK_DEL done');
25 
26 
27   EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('');
28   EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('Going to insert into ISC_EDW_BOOK_DEL table');
29 
30   l_stmt :=	'INSERT INTO '||l_isc_owner||'.ISC_EDW_BOOK_DEL'||
31 		'     SELECT book.bookings_pk, book.line_id, inst.inst_instance_pk'||
32 		'	FROM ISC_EDW_BOOKINGS_F 	book,'||
33 		'	     EDW_INSTANCE_M 		inst'||
34 		'      WHERE book.fulfillment_flag = ''N'''||
35 		'	 AND book.instance_fk_key = inst.inst_instance_pk_key';
36   EXECUTE IMMEDIATE l_stmt;
37 
38   EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('Finished inserting into the table ISC_EDW_BOOK_DEL');
39   EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('');
40 
41   l_stmt := 'SELECT count(*) FROM ISC_EDW_BOOK_DEL';
42   EXECUTE IMMEDIATE l_stmt INTO l_number_rows;
43 
44   EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('Inserted '|| nvl(l_number_rows,0) ||' rows into ISC_EDW_BOOK_DEL.');
45 
46   COMMIT;
47 
48 EXCEPTION
49 
50   WHEN OTHERS
51     THEN
52       ROLLBACK;
53       EDW_OWB_COLLECTION_UTIL.Write_To_Log_File('Error in Post-Load Hook for Bookings Fact: '||sqlerrm);
54 
55 END POST_FACT_COLL;
56 
57 END ISC_EDW_BOOK_DEL_HOOK;