[Home] [Help]
PACKAGE BODY: APPS.AD_OBSOLETE_PRODUCTS
Source
1 package body AD_OBSOLETE_PRODUCTS as
2 /* $Header: adobsprb.pls 120.20.12020000.4 2012/12/12 16:39:57 asutrala ship $*/
3 -- Star of Comments
4 --
5 -- Name
6 --
7 -- Package name: AD_OBSOLETE_PRODUCTS
8 --
9 -- History
10 --
11 -- Aug-10-05 hxue Creation Date
12 --
13 -- End of Comments
14 --
15
16 procedure drop_synonym_list (x_appl_id in number,
17 x_app_short_name in varchar2) is
18 TYPE obs_obj_type IS TABLE OF AD_OBSOLETE_OBJECTS.object_type%TYPE;
19 TYPE obs_obj_name IS TABLE OF AD_OBSOLETE_OBJECTS.object_name%TYPE;
20 obs_object_type obs_obj_type;
21 obs_object_name obs_obj_name;
22 l_drop_statement varchar2(200);
23 l_upd_statement varchar2(200);
24 l_schema_name varchar2(50);
25 l_dropped varchar2(1);
26 l_editions_enabled varchar2(1);
27 begin
28
29 -- Special cases handling
30 -- GL --> SQLGL / AP --> SQLAP / SO --> SQLSO / FA ----> OFA
31
32 if (UPPER(x_app_short_name)= 'SQLGL')
33 then
34 l_schema_name := 'GL';
35
36 elsif (UPPER(x_app_short_name)= 'SQLAP')
37 then
38 l_schema_name := 'AP';
39
40 elsif (UPPER(x_app_short_name)= 'SQLSO')
41 then
42 l_schema_name := 'SO';
43
44 elsif (UPPER(x_app_short_name)= 'OFA')
45 then
46 l_schema_name := 'FA';
47
48 else
49
50 l_schema_name := x_app_short_name;
51 end if;
52
53 l_editions_enabled := ad_zd.is_editions_enabled;
54
55 -- dbms_output.put_line('l_schema_name: ' || l_schema_name);
56
57 -- Do a bulk collect of non table objects from AOO
58
59 select object_name,object_type
60 bulk collect into obs_object_name, obs_object_type
61 from AD_OBSOLETE_OBJECTS
62 where APPLICATION_ID = x_appl_id
63 and object_type not in ('TABLE', 'INDEX', 'SEQUENCE')
64 and (UPPER(dropped) = 'N' or upper(dropped) is null);
65
66
67 -- Now loop through this list and drop the objects one by one.
68
69 FOR n IN 1 .. obs_object_name.count LOOP
70
71 begin
72
73 l_drop_statement := 'drop '|| obs_object_type(n)
74 || ' ' || '"'
75 || obs_object_name(n)
76 || '"';
77
78 l_upd_statement := 'update AD_OBSOLETE_OBJECTS set DROPPED = '
79 ||'''Y'''||' where APPLICATION_ID = '|| x_appl_id
80 ||' and OBJECT_NAME = '||''''||
81 obs_object_name(n)||''''||' and OBJECT_TYPE = '||
82 ''''||obs_object_type(n)||'''';
83
84 -- dbms_output.put_line('Drop statement: ' || l_drop_statement );
85 -- dbms_output.put_line('Update statement: ' || l_upd_statement );
86 -- dbms_output.put_line(' ');
87
88 if (l_editions_enabled = 'Y' and UPPER(obs_object_type(n)) = 'MATERIALIZED VIEW')
89 then
90 ad_zd_mview.drop_mv(null,
91 obs_object_name(n),
92 l_drop_statement,
93 l_upd_statement,
94 l_dropped);
95 else
96 execute immediate l_drop_statement;
97 execute immediate l_upd_statement;
98 end if;
99
100 exception
101 when others then
102 null;
103 end;
104
105 END LOOP;
106
107
108
109 -- Do a bulk collect of table and objects from AOO
110
111 select object_name,object_type
112 bulk collect into obs_object_name, obs_object_type
113 from AD_OBSOLETE_OBJECTS
114 where APPLICATION_ID = x_appl_id
115 and object_type in ('TABLE', 'INDEX', 'SEQUENCE')
116 and (UPPER(dropped) = 'N' or upper(dropped) is null);
117
118
119 -- Now loop through this list and drop the objects one by one.
120
121 FOR n IN 1 .. obs_object_name.count LOOP
122
123 begin
124
125 l_drop_statement := 'drop '|| obs_object_type(n)
126 || ' ' || l_schema_name
127 || '."'
128 || obs_object_name(n)
129 || '"';
130
131
132 l_upd_statement := 'update AD_OBSOLETE_OBJECTS set DROPPED = '
133 ||'''Y'''||' where APPLICATION_ID = '|| x_appl_id
134 ||' and OBJECT_NAME = '||''''||
135 obs_object_name(n)||''''||' and OBJECT_TYPE = '||
136 ''''||obs_object_type(n)||'''';
137
138 -- dbms_output.put_line('Drop statement: ' || l_drop_statement );
139 -- dbms_output.put_line('Update statement: ' || l_upd_statement );
140 -- dbms_output.put_line(' ');
141
142 if (l_editions_enabled = 'Y' and UPPER(obs_object_type(n)) = 'TABLE')
143 then
144 ad_zd_table.drop_table(l_schema_name,
145 obs_object_name(n),
146 l_drop_statement,
147 l_upd_statement,
148 l_dropped);
149 else
150 execute immediate l_drop_statement;
151 execute immediate l_upd_statement;
152 end if;
153
154 exception
155 when others then
156 null;
157 end;
158
159 END LOOP;
160
161 end drop_synonym_list;
162 --
163 --
164
165
166 procedure drop_synonym_all (x_appl_id in number,
167 x_app_short_name in varchar2) is
168 TYPE ds_obj_name IS TABLE OF dba_synonyms.synonym_name%TYPE;
169 TYPE obs_obj_type IS TABLE OF AD_OBSOLETE_OBJECTS.object_type%TYPE;
170 TYPE obs_obj_name IS TABLE OF AD_OBSOLETE_OBJECTS.object_name%TYPE;
171
172 ds_object_name ds_obj_name;
173 obs_object_type obs_obj_type;
174 obs_object_name obs_obj_name;
175
176 l_schema_name varchar2(50);
177 l_insert_statement varchar2(500);
178 l_drop_statement varchar2(500);
179 l_dropped varchar2(1);
180 l_editions_enabled varchar2(1);
181 begin
182
183 -- Special cases handling
184 -- GL --> SQLGL / AP --> SQLAP / SO --> SQLSO / FA ----> OFA
185
186 if (UPPER(x_app_short_name)= 'SQLGL')
187 then
188 l_schema_name := 'GL';
189
190 elsif (UPPER(x_app_short_name)= 'SQLAP')
191 then
192 l_schema_name := 'AP';
193
194 elsif (UPPER(x_app_short_name)= 'SQLSO')
195 then
196 l_schema_name := 'SO';
197
198 elsif (UPPER(x_app_short_name)= 'OFA')
199 then
200 l_schema_name := 'FA';
201
202 else
203
204 l_schema_name := x_app_short_name;
205 end if;
206
207 -- dbms_output.put_line('l_schema_name: ' || l_schema_name);
208
209 l_editions_enabled := ad_zd.is_editions_enabled;
210
211 -- get the list of all
212
213 select synonym_name
214 bulk collect into ds_object_name
215 from dba_synonyms
216 where TABLE_OWNER = l_schema_name
217 and synonym_name not in
218 (select object_name
219 from AD_OBSOLETE_OBJECTS
220 where APPLICATION_ID = x_appl_id
221 and OBJECT_TYPE = 'SYNONYM'
222 and (UPPER(dropped) = 'N' or upper(dropped) is null));
223
224 -- Now loop through this list and drop the objects one by one.
225
226 FOR n IN 1 .. ds_object_name.count LOOP
227
228 begin
229
230 l_drop_statement := 'drop SYNONYM '||'"'
231 || ds_object_name(n)
232 || '"';
233
234
235 l_insert_statement := 'insert into AD_OBSOLETE_OBJECTS '||
236 '(APPLICATION_ID, OBJECT_NAME, '||
237 'OBJECT_TYPE, LAST_UPDATED_BY, '||
238 ' CREATED_BY, CREATION_DATE, '||
239 'LAST_UPDATE_DATE, DROPPED) values ('||x_appl_id||
240 ', '||''''||ds_object_name(n)||''''||', '||''''||
241 'SYNONYM'||''''||', '||'1, 1, '||
242 'sysdate, sysdate, '||''''||'Y'||''''||')';
243
244 -- dbms_output.put_line('Drop statement: ' || l_drop_statement );
245 -- dbms_output.put_line('Insert statement: ' || l_insert_statement );
246 -- dbms_output.put_line(' ');
247 execute immediate l_drop_statement;
248 execute immediate l_insert_statement;
249 exception
250 when others then
251 --raise_application_error(-20001, 'here');
252 null;
253 end;
254
255 END LOOP;
256
257 -- then call the drop list
258
259 drop_synonym_list(x_appl_id, x_app_short_name);
260
261
262 -- Do a bulk collect
263
264 select object_name,object_type
265 bulk collect into obs_object_name, obs_object_type
266 from DBA_OBJECTS DO, FND_APPLICATION fa
267 where fa.APPLICATION_ID = x_appl_id
268 --and fa.APPLICATION_SHORT_NAME = do.OWNER
269 and do.OWNER = decode (fa.APPLICATION_SHORT_NAME,
270 'SQLGL', 'GL',
271 'SQLAP', 'AP',
272 'SQLSO', 'SO',
273 'OFA', 'FA',
274 fa.APPLICATION_SHORT_NAME)
275 and do.OBJECT_TYPE <> 'LOB';
276
277
278 -- Now loop through this list and drop the objects one by one.
279
280 FOR n IN 1 .. obs_object_name.count LOOP
281
282 begin
283
284 l_drop_statement := 'drop '|| obs_object_type(n)
285 || ' ' || l_schema_name
286 || '."'
287 || obs_object_name(n)
288 || '"';
289
290 l_insert_statement := 'insert into AD_OBSOLETE_OBJECTS '||
291 '(APPLICATION_ID, OBJECT_NAME, '||
292 'OBJECT_TYPE, LAST_UPDATED_BY, '||
293 ' CREATED_BY, CREATION_DATE, '||
294 'LAST_UPDATE_DATE, DROPPED) values ('||x_appl_id||
295 ', '||''''||obs_object_name(n)||''''||', '||''''||
296 obs_object_type(n)||''''||', '||'1, 1, '||
297 'sysdate, sysdate, '||''''||'Y'||''''||')';
298
299
300 -- dbms_output.put_line('Drop statement: ' || l_drop_statement );
301 -- dbms_output.put_line('Insert statement: ' || l_insert_statement );
302
303 if (l_editions_enabled = 'Y' and UPPER(obs_object_type(n)) = 'MATERIALIZED VIEW')
304 then
305 ad_zd_mview.drop_mv(l_schema_name,
306 obs_object_name(n),
307 l_drop_statement,
308 l_insert_statement,
309 l_dropped);
310 elsif (l_editions_enabled = 'Y' and UPPER(obs_object_type(n)) = 'TABLE')
311 then
312 ad_zd_table.drop_table(l_schema_name,
313 obs_object_name(n),
314 l_drop_statement,
315 l_insert_statement,
316 l_dropped);
317 else
318 execute immediate l_drop_statement;
319 execute immediate l_insert_statement;
320 end if;
321
322 exception
323 when others then
324 null;
325 --raise_application_error(-20001, 'here');
326 end;
327
328 END LOOP;
329
330
331
332 -- Update FND dictionary: FND_ORACLE_USERID
333
334 delete from FND_ORACLE_USERID
335 where oracle_id =
336 ( select oracle_id from FND_PRODUCT_INSTALLATIONS where application_id = x_appl_id)
337 and oracle_username = l_schema_name;
338
339
340 -- Update FND dictionary: FND_PRODUCT_INSTALLATIONS
341
342 delete from FND_PRODUCT_INSTALLATIONS where application_id = x_appl_id;
343
344
345 -- Update FND dictionary: FND_PRODUCT_DEPENDENCIES
346
347 delete from FND_PRODUCT_DEPENDENCIES where required_application_id = x_appl_id;
348
349
350 end drop_synonym_all;
351 --
352 --
353
354
355
356 procedure drop_apps_objects(x_appl_id in number) is
357 TYPE obs_obj_type IS TABLE OF AD_OBSOLETE_OBJECTS.object_type%TYPE;
358 TYPE obs_obj_name IS TABLE OF AD_OBSOLETE_OBJECTS.object_name%TYPE;
359 TYPE obs_obj_flag IS TABLE OF AD_OBSOLETE_OBJECTS.dropped%TYPE;
360 obs_object_type obs_obj_type;
361 obs_object_name obs_obj_name;
362 obs_object_flag obs_obj_flag;
363 l_drop_statement varchar2(200);
364 l_upd_statement varchar2(200);
365 l_apps_oracle_name varchar2(30);
366 l_dropped varchar2(1);
367 l_editions_enabled varchar2(1);
368 begin
369
370 -- Get APPSSchema Info
371
372 Begin
373
374 SELECT oracle_username
375 INTO l_apps_oracle_name
376 FROM fnd_oracle_userid
377 WHERE oracle_id BETWEEN 900 and 999
378 and read_only_flag = 'U';
379
380 exception
381 when no_data_found then
382 raise_application_error(-20001,
383 'oracle_username for APPS does not'
384 ||' exist in fnd_oracle_userid. ');
385 end;
386
387 -- dbms_output.put_line('l_apps_oracle_name: ' || l_apps_oracle_name);
388
389 l_editions_enabled := ad_zd.is_editions_enabled;
390
391 -- Do a bulk collect
392
393 select aoo.object_name, aoo.object_type, aoo.dropped
394 bulk collect into obs_object_name, obs_object_type, obs_object_flag
395 from AD_OBSOLETE_OBJECTS aoo
396 where aoo.APPLICATION_ID = x_appl_id
397 and (UPPER(dropped) = 'N' or upper(dropped) is null)
398 and aoo.object_type <> 'SYNONYM'
399 and exists
400 (select do.object_name from dba_objects do
401 where do.owner = l_apps_oracle_name
402 and do.OBJECT_NAME = aoo.OBJECT_NAME
403 and do.object_type = aoo.object_type);
404
405 -- Now loop through this list and drop the objects one by one.
406
407 FOR n IN 1 .. obs_object_name.count LOOP
408
409 begin
410
411 l_drop_statement := 'drop '|| obs_object_type(n)
412 || ' ' || '"'
413 || obs_object_name(n);
414
415 l_drop_statement := l_drop_statement || '"';
416
417 if (obs_object_type(n) = 'TYPE') then
418 l_drop_statement := l_drop_statement || ' force';
419 end if;
420
421 -- dbms_output.put_line('Drop statement: ' || l_drop_statement );
422 -- dbms_output.put_line(' ');
423
424 if (l_editions_enabled = 'Y' and (UPPER(obs_object_type(n)) = 'TABLE' or
425 UPPER(obs_object_type(n)) = 'MATERIALIZED VIEW'))
426 then
427 l_upd_statement := 'update AD_OBSOLETE_OBJECTS set DROPPED = '
428 ||'''Y'''||' where APPLICATION_ID = '|| x_appl_id
429 ||' and OBJECT_NAME = '||''''||
430 obs_object_name(n)||''''||' and OBJECT_TYPE = '||
431 ''''||obs_object_type(n)||'''';
432
433 if (UPPER(obs_object_type(n)) = 'TABLE')
434 then
435 ad_zd_table.drop_table(null,
436 obs_object_name(n),
437 l_drop_statement,
438 l_upd_statement,
439 l_dropped);
440 else
441 ad_zd_mview.drop_mv(null,
442 obs_object_name(n),
443 l_drop_statement,
444 l_upd_statement,
445 l_dropped);
446 end if;
447
448 obs_object_flag(n) := l_dropped;
449
450 else
451 execute immediate l_drop_statement;
452 /* If it gets this far, then the object was dropped */
453 obs_object_flag(n) := 'Y';
454 end if;
455
456 exception
457 when others then
458 /* If we get into the exception block, then we failed */
459 obs_object_flag(n) := 'N';
460 end;
461
462 END LOOP;
463
464 /* Updates all object rows, but dropped is set to Y or N based on
465 success or failure */
466 FORALL n in obs_object_name.first .. obs_object_name.last
467 update ad_obsolete_objects set dropped = obs_object_flag(n)
468 where object_name = obs_object_name(n)
469 and object_type = obs_object_type(n)
470 and application_id = x_appl_id;
471
472 end drop_apps_objects;
473 --
474 --
475
476
477
478 procedure drop_schema_objects
479 (aSqlcode IN OUT NOCOPY NUMBER,
480 aSqlerrm IN OUT NOCOPY VARCHAR2,
481 x_appl_id IN NUMBER,
482 x_flag IN VARCHAR2) is
483
484 l_app_short_name varchar2(50);
485
486
487 begin
488
489 -- get the schema name
490
491 begin
492 select APPLICATION_SHORT_NAME
493 into l_app_short_name
494 from FND_APPLICATION
495 where APPLICATION_ID = x_appl_id;
496
497 exception
498 when no_data_found then
499 raise_application_error(-20001,
500 'Application ID "'|| x_appl_id
501 ||'" is not registered in FND_APPLICATION. ');
502
503 end;
504
505 -- Verify if a shared base schema for multiple products
506
507 if x_appl_id= 0 or x_appl_id = 800
508
509 then
510 raise_application_error(-20001,
511 'Can not drop APPLICATION "'||l_app_short_name||
512 '" with APPLICATION ID is "'||UPPER(x_appl_id)||
513 '". This is a shared base schema for multiple products.');
514 end if;
515
516 --
517 -- Valid x_flag
518 --
519
520 if (UPPER(x_flag) <> 'ALL'
521 AND
522 UPPER(x_flag) <> 'LIST')
523
524 then
525 raise_application_error(-20001,
526 'Unknown flag "'||UPPER(x_flag)||
527 '". Valid object types are ALL or LIST.');
528 end if;
529
530 --
531 -- based on x_flag to call different processure
532 --
533
534 if (UPPER(x_flag) = 'ALL')
535 then
536 drop_synonym_all (x_appl_id , l_app_short_name);
537 else
538 drop_synonym_list (x_appl_id, l_app_short_name);
539 end if;
540
541
542 end drop_schema_objects;
543 --
544
545 procedure undo_delete_object
546 ( x_appl_id in number,
547 x_object_name in varchar2,
548 x_object_type in varchar2) is
549 l_delete_statement varchar2(500);
550 begin
551 l_delete_statement := 'delete from AD_OBSOLETE_OBJECTS '||
552 'where APPLICATION_ID = '|| x_appl_id ||
553 ' and OBJECT_NAME = '''||x_object_name ||
554 ''' and OBJECT_TYPE = '''|| x_object_type
555 || '''';
556 execute immediate l_delete_statement;
557
558
559 end undo_delete_object;
560
561 end AD_OBSOLETE_PRODUCTS;