PACKAGE BODY: APPS.BIS_INIT

Source

1 package body bis_init as
2 /* $Header: EDWINITB.pls 120.0 2005/06/01 17:23:18 appldev noship $*/
3 
4 -- This is initialization procedure for BIS/EDW
5 
6 PROCEDURE initialize IS
7 
8 --  PRAGMA AUTONOMOUS_TRANSACTION;
9 
10   sql_stmt			varchar2(2000);
11   x_step			varchar2(50);
12   v_Errorcode                   number;
13   v_ErrorText                   varchar2(200);
14 
15   sorc_records                  number := 0;
16   wh_records                    number := 0;
17 
18   impl_type			varchar2(30);
19 
20   --added fro bug 3871867
21   l_dummy1                      varchar2(32);
22   l_dummy2                      varchar2(32);
23   l_schema                      varchar2(32);
24 BEGIN
25 
26 --Get the schema name from an FND API bug 3871867
27 if FND_INSTALLATION.GET_APP_INFO('BIS',l_dummy1,l_dummy2,l_schema) = false then
28     l_schema := 'BIS';
29 end if;
30 
31 -- Identify if DBI env
32 
33 impl_type := FND_PROFILE.VALUE('BIS_IMPLEMENTATION_TYPE');
34 
35 IF ( impl_type = 'OLTP') THEN
36 
37 	null;
38 
39 ELSE
40 
41 
42 -- Identify if instance is source or/and warehouse
43 
44 -- Check if source
45 
46   select count(*) into sorc_records from dba_tables
47   where owner = l_schema  --bug 3871867
48   and table_name = 'EDW_LOCAL_INSTANCE';
49 
50   IF sorc_records > 0 THEN
51 	sql_stmt := 'select count(*) from edw_local_instance';
52 	EXECUTE IMMEDIATE sql_stmt into sorc_records;
53   END IF;
54 
55 
56 -- Check if warehouse
57 
58   select count(*) into wh_records from dba_tables
59   where owner = l_schema    --bug 3871867
60   and table_name = 'EDW_SOURCE_INSTANCES';
61 
62   IF wh_records > 0 THEN
63         sql_stmt := 'select count(*) from edw_source_instances';
64 	EXECUTE IMMEDIATE sql_stmt into wh_records;
65   END IF;
66 
67 
68   x_step := 'set_global_names_false';
69 
70   IF sorc_records > 0 THEN
71         -- Set Global_names to false
72         sql_stmt := ' ALTER SESSION SET global_names = false';
73 	EXECUTE IMMEDIATE sql_stmt;
74   END IF;
75 
76   x_step := 'set_security_context';
77 
78   IF wh_records > 0 THEN
79         -- Set context attributes for EDW Security
80         sql_stmt := 'BEGIN edw_sec_pkg.set_context; END;';
81         EXECUTE IMMEDIATE sql_stmt;
82   END IF;
83 
84 END IF;
85 
86 EXCEPTION
87 
88   WHEN OTHERS THEN
89 	v_ErrorCode := SQLCODE;
90 	v_ErrorText := SUBSTR(SQLERRM, 1, 200);
91 
92 --	Log error message into edw_error_log table
93 
94         insert into edw_error_log
95         (object_name, object_type, resp_id, message,
96         last_update_date, last_updated_by, creation_date, created_by, last_update_login)
97         values
98         ('BIS_INIT.INITIALIZE', 'BIS/EDW Initialization Procedure', NULL,
99 	'Oracle error occured in edw_init.initialize procedure at step : '|| x_step
100          || '. Errorcode is : ' || v_ErrorCode || ' and Errortext is : ' || v_ErrorText,
101         sysdate, 0, sysdate, 0, 0);
102         commit;
103 
104 -- Pass on control to FND_GLOBAL.INIT
105 
106         RAISE;
107 
108 END initialize;
109 
110 END bis_init;