[Home] [Help]
PACKAGE BODY: APPS.FND_OAM_DSCFG_PROC_LIBRARY_PKG
Source
1 PACKAGE BODY FND_OAM_DSCFG_PROC_LIBRARY_PKG as
2 /* $Header: AFOAMDSCPROCLIBB.pls 120.3 2006/05/04 14:30 ilawler noship $ */
3
4 ----------------------------------------
5 -- Private Body Constants
6 ----------------------------------------
7 PKG_NAME CONSTANT VARCHAR2(30) := 'DSCFG_PROC_LIBRARY_PKG.';
8
9 -- Object Types
10 B_OTYPE_DISABLED_TRIGGER CONSTANT VARCHAR2(30) := FND_OAM_DSCFG_API_PKG.G_INTERNAL_PREFIX||'DISABLED_TRIGGER';
11 B_OTYPE_DISABLED_PRIMARY_KEY CONSTANT VARCHAR2(30) := FND_OAM_DSCFG_API_PKG.G_INTERNAL_PREFIX||'DISABLED_PRIMARY_KEY';
12
13 -- Property_Names
14 B_PROP_TRIGGER_OWNER CONSTANT VARCHAR2(30) := 'TRIGGER_OWNER';
15 B_PROP_TRIGGER_NAME CONSTANT VARCHAR2(30) := 'TRIGGER_NAME';
16 B_PROP_TRIGGER_DISABLED_DATE CONSTANT VARCHAR2(30) := 'TRIGGER_DISABLED_DATE';
17 B_PROP_TRIGGER_RE_ENABLED_DATE CONSTANT VARCHAR2(30) := 'TRIGGER_RE_ENABLED_DATE';
18
19 B_PROP_PRIM_KEY_TABLE_OWNER CONSTANT VARCHAR2(60) := 'PRIMARY_KEY_TABLE_OWNER';
20 B_PROP_PRIM_KEY_TABLE_NAME CONSTANT VARCHAR2(60) := 'PRIMARY_KEY_TABLE_NAME';
21 B_PROP_PRIM_KEY_DISABLED_DATE CONSTANT VARCHAR2(60) := 'PRIMARY_KEY_DISABLED_DATE';
22 B_PROP_PRIM_KEY_ENABLED_DATE CONSTANT VARCHAR2(60) := 'PRIMARY_KEY_RE_ENABLED_DATE';
23
24 ----------------------------------------
25 -- Public/Private Procedures/Functions
26 ----------------------------------------
27
28 -- Update object with an error message
29 PROCEDURE UPDATE_OBJECT_WITH_ERROR(p_ctxt IN VARCHAR2,
30 p_object_id IN NUMBER,
31 p_message IN VARCHAR2)
32 IS
33 l_msg VARCHAR2(4000);
34 BEGIN
35 fnd_oam_debug.log(3, p_ctxt, p_message);
36 l_msg := '['||p_ctxt||']'||p_message;
37 UPDATE fnd_oam_dscfg_objects
38 SET errors_found_flag = FND_API.G_TRUE,
39 message = l_msg
40 WHERE object_id = p_object_id;
41 END;
42
43 --Wrapper for unhandled exceptions
44 PROCEDURE UPDATE_OBJECT_WITH_ERROR(p_ctxt IN VARCHAR2,
45 p_object_id IN NUMBER,
46 p_error_code IN NUMBER,
47 p_error_msg IN VARCHAR2)
48 IS
49 l_msg VARCHAR2(4000);
50 BEGIN
51 l_msg := 'Exception: (Code('||p_error_code||'), Message("'||p_error_msg||'"))';
52 UPDATE_OBJECT_WITH_ERROR(p_ctxt,
53 p_object_id,
54 l_msg);
55 END;
56
57 -- Update object with warning message
58 PROCEDURE UPDATE_OBJECT_WITH_WARNING(p_ctxt IN VARCHAR2,
59 p_object_id IN NUMBER,
60 p_message IN VARCHAR2)
61 IS
62 l_msg VARCHAR2(4000);
63 BEGIN
64 l_msg := 'WARNING: '||p_message;
65 fnd_oam_debug.log(1, p_ctxt, l_msg);
66 UPDATE fnd_oam_dscfg_objects
67 SET errors_found_flag = NULL,
68 message = l_msg
69 WHERE object_id = p_object_id;
70 END;
71
72 -- Update object with status success
73 PROCEDURE UPDATE_OBJECT_WITH_SUCCESS(p_ctxt IN VARCHAR2,
74 p_object_id IN NUMBER)
75 IS
76 BEGIN
77 UPDATE fnd_oam_dscfg_objects
78 SET errors_found_flag = FND_API.G_FALSE,
79 message = NULL
80 WHERE object_id = p_object_id;
81 END;
82
83 -- Public
84 PROCEDURE DISABLE_TARGET_TABLES_TRIGGERS
85 IS
86 l_ctxt VARCHAR2(60) := PKG_NAME||'DISABLE_TARGET_TABLES_TRIGGERS';
87
88 l_table_owners DBMS_SQL.VARCHAR2_TABLE;
89 l_table_names DBMS_SQL.VARCHAR2_TABLE;
90
91 l_object_id NUMBER;
92 l_property_id NUMBER;
93
94 k NUMBER;
95 l_disabled NUMBER := 0;
96 l_seen NUMBER := 0;
97
98 -- cursor to query the triggers enabled for a given table owner/name
99 CURSOR c_table_triggers(p_table_owner VARCHAR2,
100 p_table_name VARCHAR2)
101 IS
102 SELECT owner as trigger_owner, trigger_name
103 FROM dba_triggers
104 WHERE table_owner = p_table_owner
105 AND table_name = p_table_name
106 AND status = 'ENABLED';
107 BEGIN
108 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
109
110 --get the owners/tables in scope of the engine
111 FND_OAM_DSCFG_API_PKG.GET_CURRENT_TARGET_TABLE_LIST(x_table_owners => l_table_owners,
112 x_table_names => l_table_names);
113 fnd_oam_debug.log(1, l_ctxt, 'Found '||l_table_owners.COUNT||' candidate target tables.');
114
115 -- iterate across the tables found
116 k := l_table_owners.FIRST;
117 WHILE k IS NOT NULL LOOP
118 -- only process if a corresponding names entry is present
119 IF l_table_names.EXISTS(k) THEN
120 FOR l_trig IN c_table_triggers(l_table_owners(k), l_table_names(k)) LOOP
121 l_seen := l_seen + 1;
122 fnd_oam_debug.log(1, l_ctxt, 'Processing Trigger('||l_seen||'): '||l_trig.trigger_owner||'.'||l_trig.trigger_name);
123
124 fnd_oam_debug.log(1, l_ctxt, 'Creating corresponding dscfg_object...');
125 --create the disabled trigger object first since the alter trigger call is immediate
126 FND_OAM_DSCFG_API_PKG.ADD_OBJECT(p_object_type => B_OTYPE_DISABLED_TRIGGER,
127 x_object_id => l_object_id);
128
129 --keep the object creation above the alter trigger since we want this object if the alter suceeds or fails.
130 --and properties for the trigger owner/name and when we disabled it
131 FND_OAM_DSCFG_API_PKG.ADD_OBJECT_PROPERTY(p_object_id => l_object_id,
132 p_property_name => B_PROP_TRIGGER_OWNER,
133 p_varchar2_value => l_trig.trigger_owner,
134 x_property_id => l_property_id);
135 FND_OAM_DSCFG_API_PKG.ADD_OBJECT_PROPERTY(p_object_id => l_object_id,
136 p_property_name => B_PROP_TRIGGER_NAME,
137 p_varchar2_value => l_trig.trigger_name,
138 x_property_id => l_property_id);
139 FND_OAM_DSCFG_API_PKG.ADD_OBJECT_PROPERTY(p_object_id => l_object_id,
140 p_property_name => B_PROP_TRIGGER_DISABLED_DATE,
141 p_date_value => SYSDATE,
142 x_property_id => l_property_id);
143
144 fnd_oam_debug.log(1, l_ctxt, 'Disabling the actual trigger...');
145
146 -- disable the trigger
147 BEGIN
148 EXECUTE IMMEDIATE 'ALTER TRIGGER '||l_trig.trigger_owner||'.'||l_trig.trigger_name||' DISABLE';
149 fnd_oam_debug.log(1, l_ctxt, 'Trigger disabled.');
150 l_disabled := l_disabled + 1;
151 EXCEPTION
152 WHEN OTHERS THEN
153 --don't rollback so we can commit the error along with the object
154 UPDATE_OBJECT_WITH_ERROR(l_ctxt,
155 l_object_id,
156 SQLCODE,
157 SQLERRM);
158 END;
159
160 -- commit the object/props since the execute succeeded
161 COMMIT;
162
163 fnd_oam_debug.log(1, l_ctxt, 'Processing finished.');
164 END LOOP;
165 END IF;
166 k := l_table_owners.NEXT(k);
167 END LOOP;
168 fnd_oam_debug.log(1, l_ctxt, 'Disabled '||l_disabled||' of '||l_seen||' triggers.');
169
170 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
171 EXCEPTION
172 WHEN NO_DATA_FOUND THEN
173 ROLLBACK;
174 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
175 RAISE;
176 WHEN OTHERS THEN
177 ROLLBACK;
178 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
179 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
180 RAISE;
181 END;
182
183 -- Public
184 PROCEDURE RE_ENABLE_DISABLED_TRIGGERS
185 IS
186 l_ctxt VARCHAR2(60) := PKG_NAME||'RE_ENABLE_DISABLED_TRIGGERS';
187
188 l_object_ids DBMS_SQL.NUMBER_TABLE;
189
190 l_trigger_owner VARCHAR2(30);
191 l_trigger_name VARCHAR2(30);
192
193 l_property_id NUMBER;
194 l_enabled NUMBER := 0;
195 l_seen NUMBER := 0;
196
197 k NUMBER;
198 BEGIN
199 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
200
201 --get all objects representing disabled triggers which don't have errors(flag=T) or have already been
202 --processed(flag=F).
203 FND_OAM_DSCFG_API_PKG.GET_OBJECTS_FOR_TYPE(p_object_type => B_OTYPE_DISABLED_TRIGGER,
204 p_errors_found_flag => NULL,
205 x_object_ids => l_object_ids);
206 fnd_oam_debug.log(1, l_ctxt, 'Found '||l_object_ids.COUNT||' processable disabled trigger objects.');
207
208 -- iterate across the objects found
209 k := l_object_ids.FIRST;
210 WHILE k IS NOT NULL LOOP
211 --get the trigger owner/name properties
212 FND_OAM_DSCFG_API_PKG.GET_OBJECT_PROPERTY_VALUE(p_object_id => l_object_ids(k),
213 p_property_name => B_PROP_TRIGGER_OWNER,
214 x_varchar2_value => l_trigger_owner);
215 FND_OAM_DSCFG_API_PKG.GET_OBJECT_PROPERTY_VALUE(p_object_id => l_object_ids(k),
216 p_property_name => B_PROP_TRIGGER_NAME,
217 x_varchar2_value => l_trigger_name);
218 l_seen := l_seen + 1;
219 fnd_oam_debug.log(1, l_ctxt, 'Processing Trigger('||l_seen||'): '||l_trigger_owner||'.'||l_trigger_name);
220
221
222 -- re-enable the trigger
223 fnd_oam_debug.log(1, l_ctxt, 'Performing the trigger enable...');
224 BEGIN
225 EXECUTE IMMEDIATE 'ALTER TRIGGER '||l_trigger_owner||'.'||l_trigger_name||' ENABLE';
226 fnd_oam_debug.log(1, l_ctxt, 'Trigger re-enabled.');
227 --add property for when the trigger was re-enabled
228 FND_OAM_DSCFG_API_PKG.ADD_OBJECT_PROPERTY(p_object_id => l_object_ids(k),
229 p_property_name => B_PROP_TRIGGER_RE_ENABLED_DATE,
230 p_date_value => SYSDATE,
231 x_property_id => l_property_id);
232 UPDATE_OBJECT_WITH_SUCCESS(l_ctxt,
233 l_object_ids(k));
234 l_enabled := l_enabled + 1;
235 EXCEPTION
236 WHEN OTHERS THEN
237 --log the error
238 UPDATE_OBJECT_WITH_ERROR(l_ctxt,
239 l_object_ids(k),
240 SQLCODE,
241 SQLERRM);
242 END;
243 fnd_oam_debug.log(1, l_ctxt, 'Processing finished.');
244
245 -- save the success or error flag/message
246 COMMIT;
247
248 k := l_object_ids.NEXT(k);
249 END LOOP;
250
251 fnd_oam_debug.log(1, l_ctxt, 'Re-enabled '||l_enabled||' of '||l_seen||' triggers.');
252
253 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
254 EXCEPTION
255 WHEN NO_DATA_FOUND THEN
256 ROLLBACK;
257 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
258 RAISE;
259 WHEN TOO_MANY_ROWS THEN
260 ROLLBACK;
261 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
262 RAISE;
263 WHEN OTHERS THEN
264 ROLLBACK;
265 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
266 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
267 RAISE;
268 END;
269
270 -- Public
271 PROCEDURE DISABLE_TARGET_PRIMARY_KEYS
272 IS
273 l_ctxt VARCHAR2(60) := PKG_NAME||'DISABLE_TARGET_PRIMARY_KEYS';
274
275 TABLE_DOES_NOT_EXIST EXCEPTION;
276 PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942);
277 NO_PRIMARY_KEY_DEFINED EXCEPTION;
278 PRAGMA EXCEPTION_INIT(NO_PRIMARY_KEY_DEFINED, -2433);
279
280 l_table_owners DBMS_SQL.VARCHAR2_TABLE;
281 l_table_names DBMS_SQL.VARCHAR2_TABLE;
282
283 l_object_id NUMBER;
284 l_property_id NUMBER;
285 l_disabled NUMBER := 0;
286 l_seen NUMBER := 0;
287 l_commit BOOLEAN;
288
289 k NUMBER;
290 BEGIN
291 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
292
293 --get the owners/tables in scope of the engine
294 FND_OAM_DSCFG_API_PKG.GET_CURRENT_TARGET_TABLE_LIST(x_table_owners => l_table_owners,
295 x_table_names => l_table_names);
296 fnd_oam_debug.log(1, l_ctxt, 'Found '||l_table_owners.COUNT||' candidate target tables.');
297
298 -- iterate across the tables found
299 k := l_table_owners.FIRST;
300 WHILE k IS NOT NULL LOOP
301 -- only process if a corresponding names entry is present
302 IF l_table_names.EXISTS(k) THEN
303 l_seen := l_seen + 1;
304 fnd_oam_debug.log(1, l_ctxt, 'Processing Table('||l_seen||'): '||l_table_owners(k)||'.'||l_table_names(k));
305
306 fnd_oam_debug.log(1, l_ctxt, 'Disabling any primary key constraint(s)...');
307
308 -- disable the primary key
309 l_commit := TRUE;
310 l_object_id := NULL;
311 BEGIN
312 EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owners(k)||'.'||l_table_names(k)||' DISABLE PRIMARY KEY CASCADE';
313 fnd_oam_debug.log(1, l_ctxt, 'Primary Key(s) disabled.');
314
315 l_disabled := l_disabled + 1;
316 EXCEPTION
317 WHEN NO_PRIMARY_KEY_DEFINED THEN
318 --don't need an entry
319 fnd_oam_debug.log(1, l_ctxt, 'No primary keys defined - skipping.');
320 l_commit := FALSE;
321 WHEN TABLE_DOES_NOT_EXIST THEN
322 --also don't need an entry
323 fnd_oam_debug.log(1, l_ctxt, 'Table does not exist - skipping.');
324 l_commit := FALSE;
325 WHEN OTHERS THEN
326 --create the object and store the error message
327 FND_OAM_DSCFG_API_PKG.ADD_OBJECT(p_object_type => B_OTYPE_DISABLED_PRIMARY_KEY,
328 x_object_id => l_object_id);
329 UPDATE_OBJECT_WITH_ERROR(l_ctxt,
330 l_object_id,
331 SQLCODE,
332 SQLERRM);
333 END;
334
335 -- if we have things to commit, add the other properties and commit it all
336 IF l_commit THEN
337 --create the object if we haven't already created an errored object
338 IF l_object_id IS NULL THEN
339 fnd_oam_debug.log(1, l_ctxt, 'Creating corresponding dscfg_object...');
340 FND_OAM_DSCFG_API_PKG.ADD_OBJECT(p_object_type => B_OTYPE_DISABLED_PRIMARY_KEY,
341 x_object_id => l_object_id);
342 END IF;
343
344 --and properties for the primary key owner/name and when we disabled it
345 FND_OAM_DSCFG_API_PKG.ADD_OBJECT_PROPERTY(p_object_id => l_object_id,
346 p_property_name => B_PROP_PRIM_KEY_TABLE_OWNER,
347 p_varchar2_value => l_table_owners(k),
348 x_property_id => l_property_id);
349 FND_OAM_DSCFG_API_PKG.ADD_OBJECT_PROPERTY(p_object_id => l_object_id,
350 p_property_name => B_PROP_PRIM_KEY_TABLE_NAME,
351 p_varchar2_value => l_table_names(k),
352 x_property_id => l_property_id);
353 FND_OAM_DSCFG_API_PKG.ADD_OBJECT_PROPERTY(p_object_id => l_object_id,
354 p_property_name => B_PROP_PRIM_KEY_DISABLED_DATE,
355 p_date_value => SYSDATE,
356 x_property_id => l_property_id);
357 fnd_oam_debug.log(1, l_ctxt, 'Comitting.');
358 COMMIT;
359 END IF;
360
361 fnd_oam_debug.log(1, l_ctxt, 'Processing finished.');
362 END IF;
363 k := l_table_owners.NEXT(k);
364 END LOOP;
365
366 fnd_oam_debug.log(1, l_ctxt, 'Disabled '||l_disabled||' of '||l_seen||' table primary keys.');
367
368 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN
371 ROLLBACK;
372 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
373 RAISE;
374 WHEN OTHERS THEN
375 ROLLBACK;
376 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
377 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
378 RAISE;
379 END;
380
381 -- Public
382 PROCEDURE ENABLE_DISABLED_PRIMARY_KEYS
383 IS
384 l_ctxt VARCHAR2(60) := PKG_NAME||'ENABLE_DISABLED_PRIMARY_KEYS';
385
386 l_object_ids DBMS_SQL.NUMBER_TABLE;
387
388 l_table_owner VARCHAR2(30);
389 l_table_name VARCHAR2(30);
390 l_enabled NUMBER := 0;
391 l_seen NUMBER := 0;
392
393 l_property_id NUMBER;
394 k NUMBER;
395 BEGIN
396 fnd_oam_debug.log(2, l_ctxt, 'ENTER');
397
398 --get all objects representing disabled primary keys
399 FND_OAM_DSCFG_API_PKG.GET_OBJECTS_FOR_TYPE(p_object_type => B_OTYPE_DISABLED_PRIMARY_KEY,
400 p_errors_found_flag => NULL,
401 x_object_ids => l_object_ids);
402 fnd_oam_debug.log(1, l_ctxt, 'Found '||l_object_ids.COUNT||' processable disabled primary key objects.');
403
404 -- iterate across the objects found
405 k := l_object_ids.FIRST;
406 WHILE k IS NOT NULL LOOP
407 --get the primary key table owner/name properties
408 FND_OAM_DSCFG_API_PKG.GET_OBJECT_PROPERTY_VALUE(p_object_id => l_object_ids(k),
409 p_property_name => B_PROP_PRIM_KEY_TABLE_OWNER,
410 x_varchar2_value => l_table_owner);
411 FND_OAM_DSCFG_API_PKG.GET_OBJECT_PROPERTY_VALUE(p_object_id => l_object_ids(k),
412 p_property_name => B_PROP_PRIM_KEY_TABLE_NAME,
413 x_varchar2_value => l_table_name);
414
415 l_seen := l_seen + 1;
416 fnd_oam_debug.log(1, l_ctxt, 'Processing Table('||l_seen||'): '||l_table_owner||'.'||l_table_name);
417
418 -- re-enable the primary key(s)
419 fnd_oam_debug.log(1, l_ctxt, 'Performing the primary key enable...');
420 BEGIN
421 EXECUTE IMMEDIATE 'ALTER TABLE '||l_table_owner||'.'||l_table_name||' ENABLE PRIMARY KEY';
422 fnd_oam_debug.log(1, l_ctxt, 'Primary Key(s) re-enabled.');
423 --add property for when the primary key was re-enabled
424 FND_OAM_DSCFG_API_PKG.ADD_OBJECT_PROPERTY(p_object_id => l_object_ids(k),
425 p_property_name => B_PROP_PRIM_KEY_ENABLED_DATE,
426 p_date_value => SYSDATE,
427 x_property_id => l_property_id);
428 UPDATE_OBJECT_WITH_SUCCESS(l_ctxt,
429 l_object_ids(k));
430 l_enabled := l_enabled + 1;
431 EXCEPTION
432 WHEN OTHERS THEN
433 --log the error
434 UPDATE_OBJECT_WITH_ERROR(l_ctxt,
435 l_object_ids(k),
436 SQLCODE,
437 SQLERRM);
438 END;
439 fnd_oam_debug.log(1, l_ctxt, 'Processing finished.');
440
441 COMMIT;
442
443 k := l_object_ids.NEXT(k);
444 END LOOP;
445
446 fnd_oam_debug.log(1, l_ctxt, 'Re-enabled '||l_enabled||' of '||l_seen||' table primary keys.');
447
448 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
449 EXCEPTION
450 WHEN NO_DATA_FOUND THEN
451 ROLLBACK;
452 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
453 RAISE;
454 WHEN TOO_MANY_ROWS THEN
455 ROLLBACK;
456 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
457 RAISE;
458 WHEN OTHERS THEN
459 ROLLBACK;
460 fnd_oam_debug.log(6, l_ctxt, 'Unexpected Error: (Code('||SQLCODE||'), Message("'||SQLERRM||'"))');
461 fnd_oam_debug.log(2, l_ctxt, 'EXIT');
462 RAISE;
463 END;
464
465
466 END FND_OAM_DSCFG_PROC_LIBRARY_PKG;