1 PACKAGE BODY OKI_DBI_SCM_RSG_API_PVT AS
2 /*$Header: OKIMVIMB.pls 120.2 2005/06/14 19:13:02 appldev $*/
3
4 PROCEDURE Oki_Custom_Api(p_param IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL) IS
5
6 l_api_type VARCHAR2(300);
7 l_mode VARCHAR2(300);
8 l_obj_name VARCHAR2(300);
9 l_retcode NUMBER := 0;
10 l_version VARCHAR2(20);
11
12 BEGIN -- Oki_Custom_Api;
13
14 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Inside OKI Custom API');
15
16 /* Conform to Standard 2. Retrieving Parameters API_TYPE, MODE, OBJECT_NAM, OBJECT_TYPE */
17 l_api_type := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Api_Type);
18 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Got value for '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Api_Type||': '|| l_api_type);
19
20 l_mode := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Mode);
21 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Got value for '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Mode||': '|| l_mode);
22
23 l_obj_name := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Object_Name);
24 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Got value for '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Object_Name||': '|| l_obj_name);
25
26 -- g_mv_refresh_method
27 -- := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Mv_Refresh_Method);
28 -- BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Got value for '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Mv_Refresh_Method|| ': '|| g_mv_refresh_method);
29
30 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('');
31 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('-------------------------');
32 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Start of Manage_Oki_Index');
33
34 /** Performing Custom Actions based on the API type and calling mode**/
35 IF (l_api_type = BIS_BIA_RSG_CUSTOM_API_MGMNT.Type_Mv_Log_Mgt) THEN
36 NULL; --MANAGE_LOG(l_mode, l_obj_name);
37 ELSIF (l_api_type = BIS_BIA_RSG_CUSTOM_API_MGMNT.Type_Mv_Index_Mgt) THEN
38 --only manage indexes if db version is older than 10G
39 SELECT version
40 INTO l_version
41 FROM v$instance;
42
43 IF substr(l_version,1,3) = '10.' THEN
44 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('The database version is '||l_version||' so no need for index management');
45 ELSE
46 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('The database version is '||l_version||' so proceed with index management');
47 Manage_Oki_Index(l_mode, l_obj_name, l_retcode);
48 END IF;
49 ELSIF (l_api_type = BIS_BIA_RSG_CUSTOM_API_MGMNT.Type_Mv_Threshold) THEN
50 NULL; --MANAGE_MV_THRESHOLD(L_MODE, L_OBJ_NAME);
51 END IF;
52
53 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('End of Manage_Oki_Index at '|| FND_DATE.Date_To_DisplayDt(sysdate));
54 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('-------------------------');
55 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('');
56
57 COMMIT;
58
59 /* Conform to Standard 3. Setting Complete Status and Message */
60 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Complete_Status, BIS_BIA_RSG_CUSTOM_API_MGMNT.Status_Success);
61
62 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Message, 'Succeeded');
63
64 EXCEPTION
65 WHEN OTHERS THEN
66 /* Conform to Standard 6. Error Handling */
67 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Complete_Status,BIS_BIA_RSG_CUSTOM_API_MGMNT.Status_Failure);
68 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Message, sqlerrm);
69 RAISE;
70
71 END Oki_Custom_Api;
72
73
74
75 PROCEDURE Manage_Oki_Index(p_mode VARCHAR2, p_obj_name VARCHAR2 , p_retcode IN OUT NOCOPY NUMBER) IS
76 l_owner VARCHAR2(100);
77 l_status VARCHAR2(30) ;
78 l_industry VARCHAR2(30) ;
79
80 BEGIN -- Manage_Oki_Index
81
82 IF (FND_INSTALLATION.GET_APP_INFO(
83 application_short_name => 'OKI'
84 , status => l_status
85 , industry => l_industry
86 , oracle_schema => l_owner)) THEN
87
88 IF (p_mode = 'BEFORE') THEN
89 OKI_DBI_SCM_RSG_API_PVT.Drop_Index(p_obj_name,l_owner, p_retcode);
90 ELSIF (p_mode = 'AFTER') THEN
91 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Creating the column indexes at '|| FND_DATE.Date_To_DisplayDt(sysdate));
92 OKI_DBI_SCM_RSG_API_PVT.Create_Index(p_obj_name, l_owner, p_retcode);
93 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Finished creating the column indexes at '|| FND_DATE.Date_To_DisplayDt(sysdate));
94 END IF;
95
96 COMMIT;
97 END IF;
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log(sqlerrm);
102 RAISE;
103
104 END Manage_Oki_Index;
105
106
107
108 PROCEDURE Drop_Index (
109 p_table_name VARCHAR2,
110 p_owner VARCHAR2,
111 p_retcode IN OUT NOCOPY NUMBER) IS
112
113 errbuf VARCHAR2(200) := NULL;
114 l_index_exists NUMBER := 0;
115 l_rows NUMBER := 0;
116
117 CURSOR c_oki_idx IS
118 SELECT *
119 FROM oki_dbi_indexes
120 WHERE table_name = p_table_name;
121
122 CURSOR c_idx IS
123 SELECT index_name
124 FROM all_indexes
125 WHERE table_name = p_table_name
126 AND OWNER = p_owner;
127
128 BEGIN
129
130 -- BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Check if index exists, if not then they have already been dropped');
131
132 SELECT count(1)
133 INTO l_index_exists
134 FROM all_indexes
135 WHERE table_name = p_table_name
136 AND OWNER = p_owner;
137
138 SELECT count(1)
139 INTO l_rows
140 FROM oki_dbi_indexes
141 WHERE table_name = p_table_name;
142
143 IF (l_index_exists = 0) THEN
144 -- index do not exist , so no action
145 -- if no information exists to recreate them, provide message
146 IF (l_rows = 0) THEN
147 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Indexes do not exist and no information found to create them, so please create manually or re-apply the XDF for MV: '|| p_table_name);
148 ELSE
149 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Indexes do not exist but found information to create them');
150 END IF; -- (l_rows = 0) THEN
151 ELSE
152
153 -- If all_indexes has index details
154 -- Update oki_dbi_indexes with latest index information
155 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Inserting Index definitions into oki_dbi_indexes - '
156 || FND_DATE.Date_To_DisplayDt(sysdate));
157 MERGE INTO OKI_DBI_INDEXES b
158 USING ( select table_name, index_name from all_indexes
159 where table_name = p_table_name AND OWNER = p_owner ) s
160 ON (b.index_name = s.index_name AND b.table_name = s.table_name)
161 WHEN MATCHED THEN
162 UPDATE
163 SET create_stmt = DBMS_METADATA.Get_Ddl('INDEX', s.index_name)
164 WHEN NOT MATCHED THEN
165 INSERT
166 (table_name,
167 index_name,
168 create_stmt,
169 creation_date,
170 created_by,
171 last_update_date,
172 last_updated_by,
173 last_update_login)
174 VALUES
175 (p_table_name,
176 s.index_name,
177 DBMS_METADATA.Get_Ddl('INDEX', s.index_name),
178 SYSDATE,
179 -1,
180 SYSDATE,
181 -1,
182 -1);
183
184 COMMIT;
185 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('FINISHED: Inserting Index definitions into oki_dbi_indexes for - '|| p_table_name
186 || ', ' || FND_DATE.Date_To_DisplayDt(sysdate));
187
188 -- Once all indexes are stored in oki_dbi_indexes
189 -- drop all the existing indexes of MV from the DB
190 FOR i IN c_idx LOOP
191 EXECUTE IMMEDIATE 'DROP INDEX ' || i.index_name;
192 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Dropped index: '|| i.index_name);
193 END LOOP;
194 END IF; -- End l_index_exists=0
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 errbuf := substr(sqlerrm,1,200);
199 p_retcode := sqlcode;
200 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('ERROR in Drop_Index--> ' || p_retcode || ':' || errbuf);
201 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('-----------------------------');
202 RAISE;
203
204 END Drop_Index;
205
206
207
208 PROCEDURE Create_index(
209 p_table_name VARCHAR2,
210 p_owner VARCHAR2,
211 p_retcode IN OUT NOCOPY NUMBER) IS
212
213 l_create_stmt VARCHAR2(32000) := NULL;
214 l_mod_create_stmt VARCHAR2(32000) := NULL;
215 errbuf VARCHAR2(200);
216
217 CURSOR c_oki_idx IS
218 SELECT index_name,
219 create_stmt
220 FROM oki_dbi_indexes
221 WHERE table_name = p_table_name;
222
223 CURSOR c_idx IS
224 SELECT index_name
225 FROM all_indexes
226 WHERE table_name like p_table_name
227 AND OWNER = p_owner ;
228
229 BEGIN
230
231 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('');
232 FOR i IN c_oki_idx LOOP
233 l_create_stmt := i.create_stmt;
234
235 IF(INSTR(l_create_stmt,'NOLOGGING') > 0) THEN
236 l_mod_create_stmt := l_create_stmt;
237 ELSIF(INSTR(l_create_stmt,'LOGGING') > 0) THEN
238 l_mod_create_stmt := REPLACE(l_create_stmt,'LOGGING','NOLOGGING');
239 ELSE
240 l_mod_create_stmt := l_create_stmt || ' NOLOGGING';
241 END IF;
242
243 IF(INSTR(l_mod_create_stmt,'NOPARALLEL') > 0) THEN
244 l_mod_create_stmt := replace(l_mod_create_stmt,'NOPARALLEL','PARALLEL');
245 ELSIF(INSTR(l_mod_create_stmt,'PARALLEL') <= 0) THEN
246 l_mod_create_stmt := l_mod_create_stmt || ' PARALLEL';
247 END IF;
248
249 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Starting to create index '||i.index_name||' from oki_dbi_indexes at: '|| FND_DATE.Date_To_DisplayDt(sysdate));
250 EXECUTE IMMEDIATE l_mod_create_stmt;
251 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log(' Created index '||i.index_name||' at: '|| FND_DATE.Date_To_DisplayDt(sysdate));
252 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('');
253
254 EXECUTE IMMEDIATE 'ALTER INDEX '||i.index_name||' LOGGING NOPARALLEL';
255
256 END LOOP;
257
258 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Indexes created at '||FND_DATE.Date_To_DisplayDt(sysdate));
259 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Cleaning the table oki_dbi_indexes for indexes of '||p_table_name);
260
261 DELETE FROM oki_dbi_indexes
262 WHERE table_name = p_table_name;
263 COMMIT;
264 -- raise_application_error(-20101, 'After Create index - Check Fail after clean up');
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 errbuf := substr(sqlerrm,1,200);
269 p_retcode := sqlcode;
270 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('ERROR in Create_Index--> ' || p_retcode || ':' || errbuf);
271 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Failing Statement: ' || l_mod_create_stmt);
272 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('-----------------------------');
273 RAISE;
274
275 END Create_Index;
276
277 PROCEDURE sleep(p_param IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL) IS
278
279 l_api_type VARCHAR2(300);
280 l_mode VARCHAR2(300);
281 l_obj_name VARCHAR2(300);
282 l_retcode NUMBER := 0;
283 l_version VARCHAR2(20);
284 l_mv_refresh_method VARCHAR2(50);
285
286 BEGIN -- Oki_Custom_Api;
287
288 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Inside OKI Custom API - Sleep');
289
290 /* Conform to Standard 2. Retrieving Parameters API_TYPE, MODE, OBJECT_NAM, OBJECT_TYPE */
291 l_api_type := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Api_Type);
292 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Got value for '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Api_Type||': '|| l_api_type);
293
294 l_mode := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Mode);
295 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Got value for '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Mode||': '|| l_mode);
296
297 l_obj_name := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Object_Name);
298 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Got value for '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Object_Name||': '|| l_obj_name);
299
300 l_mv_refresh_method
301 := BIS_BIA_RSG_CUSTOM_API_MGMNT.Get_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Mv_Refresh_Method);
302 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Got value for '|| BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Mv_Refresh_Method|| ': '|| l_mv_refresh_method);
303
304 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('');
305 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('-------------------------');
306 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('Start of Sleep at '|| FND_DATE.Date_To_DisplayDt(sysdate));
307
308 IF l_mode = 'BEFORE'
309 THEN
310 dbms_lock.sleep(10);
311 END IF;
312
313 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('End of Sleep at '|| FND_DATE.Date_To_DisplayDt(sysdate));
314 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('-------------------------');
315 BIS_BIA_RSG_CUSTOM_API_MGMNT.Log('');
316
317 COMMIT;
318
319 /* Conform to Standard 3. Setting Complete Status and Message */
320 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Complete_Status, BIS_BIA_RSG_CUSTOM_API_MGMNT.Status_Success);
321
322 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Message, 'Succeeded');
323
324 EXCEPTION
325 WHEN OTHERS THEN
326 /* Conform to Standard 6. Error Handling */
327 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Complete_Status,BIS_BIA_RSG_CUSTOM_API_MGMNT.Status_Failure);
328 BIS_BIA_RSG_CUSTOM_API_MGMNT.Set_Param(p_param, BIS_BIA_RSG_CUSTOM_API_MGMNT.Para_Message, sqlerrm);
329 RAISE;
330
331 END sleep;
332 END; -- OKI_DBI_SCM_RSG_API_PVT
[Home] [Help]