[Home] [Help]
PACKAGE BODY: APPS.BIL_BI_OPTY_MV_REFRESH_PKG
Source
1 PACKAGE BODY BIL_BI_OPTY_MV_REFRESH_PKG AS
2 /*$Header: bilrmvb.pls 120.2 2006/03/28 02:04:48 vchahal noship $*/
3
4 PROCEDURE MANAGE_LOG(P_MODE IN VARCHAR2, P_OBJ_NAME IN VARCHAR2);
5
6 PROCEDURE MANAGE_INDEX(P_MODE IN VARCHAR2, P_OBJ_NAME IN VARCHAR2, P_DB_VERSION IN VARCHAR2);
7
8 PROCEDURE MANAGE_MV_THRESHOLD
9 (
10 P_MODE VARCHAR2,
11 P_OBJ_NAME VARCHAR2,
12 p_refresh_mode OUT NOCOPY VARCHAR2
13 );
14
15 PROCEDURE MANAGE_SESSION (P_MODE IN VARCHAR2, P_OBJ_NAME IN VARCHAR2, P_DB_VERSION IN VARCHAR2);
16
17 PROCEDURE CUSTOM_API
18 (
19 p_param IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL
20 )
21 IS
22
23 L_API_TYPE varchar2(300);
24 L_MODE varchar2(300);
25 L_OBJ_NAME varchar2(300);
26 L_OBJ_TYPE varchar2(300);
27 l_mv_refresh_method varchar2(300);
28
29 --Variable to see the refresh mode (C for complete and F for fast!)
30 l_refresh_mode VARCHAR2(1);
31
32 db_version varchar2(100);
33
34 BEGIN
35
36 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('Inside Custom API ');
37
38 select version into db_version from v$instance;
39
40 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('DB Version: ' || db_version);
41
42 db_version := substr(db_version,1,instr(db_version,'.',1)-1);
43
44 /* Conform to Standard 2. Retrieving Parameters API_TYPE, MODE, OBJECT_NAM, OBJECT_TYPE */
45 L_API_TYPE := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_API_TYPE);
46 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG
47 ('Got value for ' || BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_API_TYPE || ': ' || L_API_TYPE);
48 L_MODE := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MODE);
49 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG
50 ('Got value for ' || BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MODE || ': ' || L_MODE);
51 l_mv_refresh_method := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MV_REFRESH_METHOD);
52 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('Got value for ' || BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MV_REFRESH_METHOD || ': ' || l_mv_refresh_method);
53
54 L_OBJ_NAME := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_OBJECT_NAME);
55 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG
56 ('Got value for ' || BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_OBJECT_NAME || ': ' || L_OBJ_NAME);
57
58 L_OBJ_TYPE := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_OBJECT_TYPE);
59 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG
60 ('Got value for ' || BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_OBJECT_TYPE || ': ' || L_OBJ_TYPE);
61
62
63 /** Performing Custom Actions based on the API type and calling mode**/
64 IF (L_API_TYPE = BIS_BIA_RSG_CUSTOM_API_MGMNT.TYPE_MV_LOG_MGT) THEN
65 MANAGE_LOG(L_MODE, L_OBJ_NAME);
66 ELSIF (L_API_TYPE = BIS_BIA_RSG_CUSTOM_API_MGMNT.TYPE_MV_OTHER_CUSTOM
67 AND l_mv_refresh_method = BIS_BIA_RSG_CUSTOM_API_MGMNT.METHOD_COMPLETE) THEN
68 MANAGE_SESSION(L_MODE, L_OBJ_NAME, db_version);
69 ELSIF (L_API_TYPE = BIS_BIA_RSG_CUSTOM_API_MGMNT.TYPE_MV_INDEX_MGT) THEN
70 MANAGE_INDEX(L_MODE, L_OBJ_NAME, db_version);
71 NULL;
72 ELSIF (L_API_TYPE = BIS_BIA_RSG_CUSTOM_API_MGMNT.TYPE_MV_THRESHOLD) THEN
73 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('calling MV thresh hold proc now');
74 MANAGE_MV_THRESHOLD(L_MODE, L_OBJ_NAME,l_refresh_mode);
75 IF(l_refresh_mode = 'C') THEN
76 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM
77 (
78 p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MV_REFRESH_METHOD,
79 BIS_BIA_RSG_CUSTOM_API_MGMNT.METHOD_COMPLETE
80 );
81 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('have set type as complete');
82 END IF;
83 END IF;
84
85 /* Conform to Standard 3. Setting Complete Status and Message */
86 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_COMPLETE_STATUS,
87 BIS_BIA_RSG_CUSTOM_API_MGMNT.STATUS_SUCCESS );
88 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MESSAGE,
89 'Succeeded' );
90 EXCEPTION WHEN OTHERS THEN
91 /* Conform to Standard 6. Error Handling */
92 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_COMPLETE_STATUS,
93 BIS_BIA_RSG_CUSTOM_API_MGMNT.STATUS_FAILURE );
94 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MESSAGE, sqlerrm );
95 END CUSTOM_API;
96
97
98
99 PROCEDURE MANAGE_LOG(P_MODE VARCHAR2, P_OBJ_NAME VARCHAR2) IS
100 BEGIN
101 NULL;
102 END;
103
104
105
106 PROCEDURE MANAGE_INDEX(P_MODE VARCHAR2, P_OBJ_NAME VARCHAR2, P_DB_VERSION VARCHAR2) IS
107 L_DYNAMIC_STMNT VARCHAR2(5000);
108 l_idx_present NUMBER DEFAULT 1;
109
110 BEGIN
111
112 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('inside the index management proc');
113
114 -- db_versn = 10 for 10g
115 -- db_versn = 9 for 9
116
117 IF (P_MODE = BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_BEFORE AND
118 P_DB_VERSION <= 9) THEN
119
120 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('starting the before loop');
121
122 -- Delete the entry from table for the index in context
123 DELETE FROM BIL_BI_INDEX_MGMT WHERE table_name = p_obj_name;
124
125 FOR i IN (SELECT index_name FROM user_indexes WHERE table_name=P_OBJ_NAME)
126 LOOP
127 INSERT INTO BIL_BI_INDEX_MGMT (table_name,index_name,ddl_stmnt)
128 VALUES (P_OBJ_NAME,i.INDEX_NAME,DBMS_METADATA.GET_DDL('INDEX',i.index_name));
129 EXECUTE IMMEDIATE 'DROP INDEX '||i.index_name;
130
131 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG(' dropped this index :'||i.index_name);
132
133 END LOOP;
134 ELSIF (P_MODE = BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_AFTER AND
135 P_DB_VERSION <= 9) THEN
136
137 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('starting the after loop');
138
139 FOR i in (SELECT table_name,index_name,ddl_stmnt FROM BIL_BI_INDEX_MGMT WHERE table_name = p_obj_name)
140 LOOP
141 -- Check for existance of the index
142 SELECT count(*) INTO l_idx_present
143 FROM user_indexes WHERE table_name=p_obj_name AND index_name=i.index_name;
144 -- Create index if is not existing in the DB
145
146 IF l_idx_present <> 1 THEN
147 L_DYNAMIC_STMNT := REPLACE(REPLACE(replace(replace(replace(i.ddl_stmnt,'NOPARALLEL',' '),
148 'PARALLEL',' '),
149 'NOLOGGING',' '),
150 'LOGGING',' '),
151 UPPER('COMPUTE STATISTICS'),' ');
152 L_DYNAMIC_STMNT := L_DYNAMIC_STMNT || ' NOLOGGING PARALLEL ';
153 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('the DDL statement is :'||L_DYNAMIC_STMNT);
154 EXECUTE IMMEDIATE (L_DYNAMIC_STMNT);
155 EXECUTE IMMEDIATE ('ALTER INDEX '||i.index_name||' LOGGING NOPARALLEL');
156 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('recreated the index:'||i.index_name);
157 END IF;
158
159 -- Delete the entry from bim_all_indexes table for the index in context
160 DELETE FROM BIL_BI_INDEX_MGMT WHERE table_name = p_obj_name AND index_name = i.index_name;
161
162 COMMIT;
163
164 l_idx_present := 1;
165
166 END LOOP;
167 END IF;
168 END;
169
170
171 PROCEDURE MANAGE_MV_THRESHOLD
172 (
173 P_MODE VARCHAR2,
174 P_OBJ_NAME VARCHAR2,
175 p_refresh_mode OUT NOCOPY VARCHAR2
176 ) IS
177 BEGIN
178 /*
179 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('Inside thresh hold proc');
180 for Top oppty MV, always complete refresh
181 IF (P_OBJ_NAME = 'BIL_BI_TOPOP_G_MV') THEN
182 p_refresh_mode := 'C';
183 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('Inside the if loop for mv thresh hold');
184 END IF;
185 */
186 NULL;
187 END;
188
189 PROCEDURE MANAGE_SESSION
190 (
191 P_MODE VARCHAR2,
192 P_OBJ_NAME VARCHAR2,
193 P_DB_VERSION VARCHAR2
194 ) IS
195 BEGIN
196
197 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG(' inside manager_session');
198
199 IF (P_MODE = BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_BEFORE) THEN
200
201 IF P_DB_VERSION > 9 THEN
202 EXECUTE IMMEDIATE 'alter session set "_idxrb_rowincr"=1000';
203 END IF;
204
205 FOR i IN (SELECT index_name FROM user_indexes WHERE table_name=P_OBJ_NAME)
206 LOOP
207 EXECUTE IMMEDIATE 'ALTER INDEX '||i.index_name|| ' parallel';
208 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG(' altered index ' || i.index_name || ' parallel');
209 END LOOP;
210
211
212 ELSIF (P_MODE = BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_AFTER) THEN
213 FOR i IN (SELECT index_name FROM user_indexes WHERE table_name=P_OBJ_NAME)
214 LOOP
215 EXECUTE IMMEDIATE 'ALTER INDEX '||i.index_name|| ' noparallel';
216 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG(' altered index ' || i.index_name || ' noparallel');
217 END LOOP;
218
219
220 END IF;
221 END;
222
223 END BIL_BI_OPTY_MV_REFRESH_PKG;