[Home] [Help]
PACKAGE BODY: APPS.AD_OBSOLETE_PRODUCTS
Source
1 package body AD_OBSOLETE_PRODUCTS as
2 /* $Header: adobsprb.pls 120.15 2006/04/13 15:14:08 hxue noship $*/
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
26 begin
27
28 -- Special cases handling
29 -- GL --> SQLGL / AP --> SQLAP / SO --> SQLSO / FA ----> OFA
30
31 if (UPPER(x_app_short_name)= 'SQLGL')
32 then
33 l_schema_name := 'GL';
34
35 elsif (UPPER(x_app_short_name)= 'SQLAP')
36 then
37 l_schema_name := 'AP';
38
39 elsif (UPPER(x_app_short_name)= 'SQLSO')
40 then
41 l_schema_name := 'SO';
42
43 elsif (UPPER(x_app_short_name)= 'OFA')
44 then
45 l_schema_name := 'FA';
46
47 else
48
49 l_schema_name := x_app_short_name;
50 end if;
51
52 -- dbms_output.put_line('l_schema_name: ' || l_schema_name);
53
54 -- Do a bulk collect of non table objects from AOO
55
56 select object_name,object_type
57 bulk collect into obs_object_name, obs_object_type
58 from AD_OBSOLETE_OBJECTS
59 where APPLICATION_ID = x_appl_id
60 and object_type not in ('TABLE', 'INDEX')
61 and (UPPER(dropped) = 'N' or upper(dropped) is null);
62
63
64 -- Now loop through this list and drop the objects one by one.
65
66 FOR n IN 1 .. obs_object_name.count LOOP
67
68 begin
69
70 l_drop_statement := 'drop '|| obs_object_type(n)
71 || ' ' || '"'
72 || obs_object_name(n)
73 || '"';
74
75
76 l_upd_statement := 'update AD_OBSOLETE_OBJECTS set DROPPED = '
77 ||'''Y'''||' where APPLICATION_ID = '|| x_appl_id
78 ||' and OBJECT_NAME = '||''''||
79 obs_object_name(n)||''''||' and OBJECT_TYPE = '||
80 ''''||obs_object_type(n)||'''';
81
82 -- dbms_output.put_line('Drop statement: ' || l_drop_statement );
83 -- dbms_output.put_line('Update statement: ' || l_upd_statement );
84 -- dbms_output.put_line(' ');
85 execute immediate l_drop_statement;
86 execute immediate l_upd_statement;
87 exception
88 when others then
89 null;
90 end;
91
92 END LOOP;
93
94
95
96 -- Do a bulk collect of table and objects from AOO
97
98 select object_name,object_type
99 bulk collect into obs_object_name, obs_object_type
100 from AD_OBSOLETE_OBJECTS
101 where APPLICATION_ID = x_appl_id
102 and object_type in ('TABLE', 'INDEX')
103 and (UPPER(dropped) = 'N' or upper(dropped) is null);
104
105
106 -- Now loop through this list and drop the objects one by one.
107
108 FOR n IN 1 .. obs_object_name.count LOOP
109
110 begin
111
112 l_drop_statement := 'drop '|| obs_object_type(n)
113 || ' ' || l_schema_name
114 || '."'
115 || obs_object_name(n)
116 || '"';
117
118
119 l_upd_statement := 'update AD_OBSOLETE_OBJECTS set DROPPED = '
120 ||'''Y'''||' where APPLICATION_ID = '|| x_appl_id
121 ||' and OBJECT_NAME = '||''''||
122 obs_object_name(n)||''''||' and OBJECT_TYPE = '||
123 ''''||obs_object_type(n)||'''';
124
125 -- dbms_output.put_line('Drop statement: ' || l_drop_statement );
126 -- dbms_output.put_line('Update statement: ' || l_upd_statement );
127 -- dbms_output.put_line(' ');
128 execute immediate l_drop_statement;
129 execute immediate l_upd_statement;
130 exception
131 when others then
132 null;
133 end;
134
135 END LOOP;
136
137 end drop_synonym_list;
138 --
139 --
140
141
142 procedure drop_synonym_all (x_appl_id in number,
143 x_app_short_name in varchar2) is
144 TYPE ds_obj_name IS TABLE OF dba_synonyms.synonym_name%TYPE;
145 TYPE obs_obj_type IS TABLE OF AD_OBSOLETE_OBJECTS.object_type%TYPE;
146 TYPE obs_obj_name IS TABLE OF AD_OBSOLETE_OBJECTS.object_name%TYPE;
147
148 ds_object_name ds_obj_name;
149 obs_object_type obs_obj_type;
150 obs_object_name obs_obj_name;
151
152 l_schema_name varchar2(50);
153 l_insert_statement varchar2(500);
154 l_drop_statement varchar2(500);
155
156 begin
157
158 -- Special cases handling
159 -- GL --> SQLGL / AP --> SQLAP / SO --> SQLSO / FA ----> OFA
160
161 if (UPPER(x_app_short_name)= 'SQLGL')
162 then
163 l_schema_name := 'GL';
164
165 elsif (UPPER(x_app_short_name)= 'SQLAP')
166 then
167 l_schema_name := 'AP';
168
169 elsif (UPPER(x_app_short_name)= 'SQLSO')
170 then
171 l_schema_name := 'SO';
172
173 elsif (UPPER(x_app_short_name)= 'OFA')
174 then
175 l_schema_name := 'FA';
176
177 else
178
179 l_schema_name := x_app_short_name;
180 end if;
181
182 -- dbms_output.put_line('l_schema_name: ' || l_schema_name);
183
184 -- get the list of all
185
186 select synonym_name
187 bulk collect into ds_object_name
188 from dba_synonyms
189 where TABLE_OWNER = l_schema_name
190 and synonym_name not in
191 (select object_name
192 from AD_OBSOLETE_OBJECTS
193 where APPLICATION_ID = x_appl_id
194 and OBJECT_TYPE = 'SYNONYM'
195 and (UPPER(dropped) = 'N' or upper(dropped) is null));
196
197 -- Now loop through this list and drop the objects one by one.
198
199 FOR n IN 1 .. ds_object_name.count LOOP
200
201 begin
202
203 l_drop_statement := 'drop SYNONYM '||'"'
204 || ds_object_name(n)
205 || '"';
206
207
208 l_insert_statement := 'insert into AD_OBSOLETE_OBJECTS '||
209 '(APPLICATION_ID, OBJECT_NAME, '||
210 'OBJECT_TYPE, LAST_UPDATED_BY, '||
211 ' CREATED_BY, CREATION_DATE, '||
212 'LAST_UPDATE_DATE, DROPPED) values ('||x_appl_id||
213 ', '||''''||ds_object_name(n)||''''||', '||''''||
214 'SYNONYM'||''''||', '||'1, 1, '||
215 'sysdate, sysdate, '||''''||'Y'||''''||')';
216
217 -- dbms_output.put_line('Drop statement: ' || l_drop_statement );
218 -- dbms_output.put_line('Insert statement: ' || l_insert_statement );
219 -- dbms_output.put_line(' ');
220 execute immediate l_drop_statement;
221 execute immediate l_insert_statement;
222 exception
223 when others then
224 --raise_application_error(-20001, 'here');
225 null;
226 end;
227
228 END LOOP;
229
230 -- then call the drop list
231
232 drop_synonym_list(x_appl_id, x_app_short_name);
233
234
235 -- Do a bulk collect
236
237 select object_name,object_type
238 bulk collect into obs_object_name, obs_object_type
239 from DBA_OBJECTS DO, FND_APPLICATION fa
240 where fa.APPLICATION_ID = x_appl_id
241 --and fa.APPLICATION_SHORT_NAME = do.OWNER
242 and do.OWNER = decode (fa.APPLICATION_SHORT_NAME,
243 'SQLGL', 'GL',
244 'SQLAP', 'AP',
245 'SQLSO', 'SO',
246 'OFA', 'FA',
247 fa.APPLICATION_SHORT_NAME)
248 and do.OBJECT_TYPE <> 'LOB';
249
250
251 -- Now loop through this list and drop the objects one by one.
252
253 FOR n IN 1 .. obs_object_name.count LOOP
254
255 begin
256
257 l_drop_statement := 'drop '|| obs_object_type(n)
258 || ' ' || l_schema_name
259 || '."'
260 || obs_object_name(n)
261 || '"';
262
263 l_insert_statement := 'insert into AD_OBSOLETE_OBJECTS '||
264 '(APPLICATION_ID, OBJECT_NAME, '||
265 'OBJECT_TYPE, LAST_UPDATED_BY, '||
266 ' CREATED_BY, CREATION_DATE, '||
267 'LAST_UPDATE_DATE, DROPPED) values ('||x_appl_id||
268 ', '||''''||obs_object_name(n)||''''||', '||''''||
269 obs_object_type(n)||''''||', '||'1, 1, '||
273 -- dbms_output.put_line('Drop statement: ' || l_drop_statement );
270 'sysdate, sysdate, '||''''||'Y'||''''||')';
271
272
274 -- dbms_output.put_line('Insert statement: ' || l_insert_statement );
275 execute immediate l_drop_statement;
276 execute immediate l_insert_statement;
277 exception
278 when others then
279 null;
280 --raise_application_error(-20001, 'here');
281 end;
282
283 END LOOP;
284
285
286
287 -- Update FND dictionary: FND_ORACLE_USERID
288
289 delete from FND_ORACLE_USERID
290 where oracle_id =
291 ( select oracle_id from FND_PRODUCT_INSTALLATIONS where application_id = x_appl_id)
292 and oracle_username = l_schema_name;
293
294
295 -- Update FND dictionary: FND_PRODUCT_INSTALLATIONS
296
297 delete from FND_PRODUCT_INSTALLATIONS where application_id = x_appl_id;
298
299
300 -- Update FND dictionary: FND_PRODUCT_DEPENDENCIES
301
302 delete from FND_PRODUCT_DEPENDENCIES where required_application_id = x_appl_id;
303
304
305 end drop_synonym_all;
306 --
307 --
308
309
310
311 procedure drop_apps_objects(x_appl_id in number) is
312 TYPE obs_obj_type IS TABLE OF AD_OBSOLETE_OBJECTS.object_type%TYPE;
313 TYPE obs_obj_name IS TABLE OF AD_OBSOLETE_OBJECTS.object_name%TYPE;
314 obs_object_type obs_obj_type;
315 obs_object_name obs_obj_name;
316 l_drop_statement varchar2(200);
317 l_upd_statement varchar2(200);
318 l_apps_oracle_name varchar2(30);
319
320 begin
321
322 -- Get APPSSchema Info
323
324 Begin
325
326 SELECT oracle_username
327 INTO l_apps_oracle_name
328 FROM fnd_oracle_userid
329 WHERE oracle_id BETWEEN 900 and 999
330 and read_only_flag = 'U';
331
332 exception
333 when no_data_found then
334 raise_application_error(-20001,
335 'oracle_username for APPS does not'
336 ||' exist in fnd_oracle_userid. ');
337 end;
338
339 -- dbms_output.put_line('l_apps_oracle_name: ' || l_apps_oracle_name);
340
341 -- Do a bulk collect
342
343 select aoo.object_name, aoo.object_type
344 bulk collect into obs_object_name, obs_object_type
345 from AD_OBSOLETE_OBJECTS aoo
346 where aoo.APPLICATION_ID = x_appl_id
347 and (UPPER(dropped) = 'N' or upper(dropped) is null)
348 and aoo.object_type <> 'SYNONYM'
349 and exists
350 (select do.object_name from dba_objects do
351 where do.owner = l_apps_oracle_name
352 and do.OBJECT_NAME = aoo.OBJECT_NAME
353 and do.object_type = aoo.object_type);
354
355 -- Now loop through this list and drop the objects one by one.
356
357 FOR n IN 1 .. obs_object_name.count LOOP
358
359 begin
360
361 l_drop_statement := 'drop '|| obs_object_type(n)
362 || ' ' || '"'
363 || obs_object_name(n)
364 || '"';
365
366
367 l_upd_statement := 'update AD_OBSOLETE_OBJECTS set DROPPED = '
368 ||'''Y'''||' where OBJECT_NAME = '||''''||
369 obs_object_name(n)||''''||' and OBJECT_TYPE = '||
370 ''''||obs_object_type(n)||''''||
371 ' and APPLICATION_ID = '|| x_appl_id;
372
373 -- dbms_output.put_line('Drop statement: ' || l_drop_statement );
374 -- dbms_output.put_line('Update statement: ' || l_upd_statement );
375 -- dbms_output.put_line(' ');
376 execute immediate l_drop_statement;
377 execute immediate l_upd_statement;
378 exception
379 when others then
380 null;
381 end;
382
383 END LOOP;
384
385 end drop_apps_objects;
386 --
387 --
388
389
390
391 procedure drop_schema_objects
392 (aSqlcode IN OUT NOCOPY NUMBER,
393 aSqlerrm IN OUT NOCOPY VARCHAR2,
394 x_appl_id IN NUMBER,
395 x_flag IN VARCHAR2) is
396
397 l_app_short_name varchar2(50);
398
399
400 begin
401
402 -- get the schema name
403
404 begin
405 select APPLICATION_SHORT_NAME
406 into l_app_short_name
407 from FND_APPLICATION
408 where APPLICATION_ID = x_appl_id;
409
410 exception
411 when no_data_found then
412 raise_application_error(-20001,
413 'Application ID "'|| x_appl_id
414 ||'" is not registered in FND_APPLICATION. ');
415
416 end;
417
418 -- Verify if a shared base schema for multiple products
419
420 if x_appl_id= 0 or x_appl_id = 800
421
422 then
423 raise_application_error(-20001,
424 'Can not drop APPLICATION "'||l_app_short_name||
425 '" with APPLICATION ID is "'||UPPER(x_appl_id)||
426 '". This is a shared base schema for multiple products.');
427 end if;
428
429 --
430 -- Valid x_flag
431 --
432
433 if (UPPER(x_flag) <> 'ALL'
434 AND
435 UPPER(x_flag) <> 'LIST')
436
437 then
438 raise_application_error(-20001,
439 'Unknown flag "'||UPPER(x_flag)||
440 '". Valid object types are ALL or LIST.');
441 end if;
442
443 --
444 -- based on x_flag to call different processure
445 --
446
447 if (UPPER(x_flag) = 'ALL')
448 then
449 drop_synonym_all (x_appl_id , l_app_short_name);
450 else
451 drop_synonym_list (x_appl_id, l_app_short_name);
452 end if;
453
454
455 end drop_schema_objects;
456 --
457
458 end AD_OBSOLETE_PRODUCTS;