[Home] [Help]
PACKAGE BODY: APPS.FND_CONC_CLONE
Source
1 package body fnd_conc_clone as
2 /* $Header: AFCPCLNB.pls 120.3 2011/10/06 20:18:38 pferguso ship $ */
3
4
5 --This two variable are used to store the database user name of
6 --FND and JTF objects
7 OracleUserFND varchar2(30) := null ;
8 OracleUserJTF varchar2(30) := null ;
9
10 /*
11 * procedure : get_database_user
12 *
13 * purpose : Database user for FND objects and JTF objects are retrieved
14 * form table and stored into the package
15 * variable OracleUserFND and OracleUserJTF
16 * Changed is done to remove the 'APPLSYS' and 'JTF' hard coding. Bug 3335806
17 */
18 procedure get_database_user is
19
20 begin
21 if (OracleUserFND is null) then
22 begin
23 SELECT fou.oracle_username into OracleUserFND
24 FROM fnd_oracle_userid fou,
25 fnd_product_installations fpi,
26 fnd_application a
27 WHERE fou.oracle_id = fpi.oracle_id
28 AND fpi.application_id = a.application_id
29 AND a.application_short_name = 'FND' ;
30 exception
31 when NO_DATA_FOUND then
32 OracleUserFND := null;
33 end;
34 end if;
35
36
37 if (OracleUserJTF is null) then
38 begin
39 SELECT fou.oracle_username into OracleUserJTF
40 FROM fnd_oracle_userid fou,
41 fnd_product_installations fpi,
42 fnd_application a
43 WHERE fou.oracle_id = fpi.oracle_id
44 AND fpi.application_id = a.application_id
45 AND a.application_short_name = 'JTF' ;
46 exception
47 when NO_DATA_FOUND then
48 OracleUserJTF := null;
49 end;
50 end if;
51
52 end;
53
54
55
56 /*
57 * procedure: truncate_table
58 *
59 * Purpose: To truncate a table
60 *
61 * Arguments: schema name, table_name
62 *
63 */
64
65 procedure truncate_table(p_schema varchar2,
66 p_table varchar2) is
67 TableNotFound EXCEPTION;
68 PRAGMA EXCEPTION_INIT(TableNotFound, -942);
69 begin
70
71 if ( p_schema is null ) then
72 return;
73 end if;
74
75 execute immediate 'truncate table ' || p_schema || '.' || p_table;
76
77
78 exception
79 when TableNotFound then
80 null;
81 end;
82
83
84 /*
85 * procedure: target_clean
86 *
87 * ***************************************************************************
88 * NOTE: If you are not sure what you are doing do not run this procedure.
89 * This API is for Rapid Install team use only.
90 * ***************************************************************************
91 *
92 * Purpose: To clean up target database for cloning purpose.
93 * It is callers responsibility to do the commit after calling target_clean
94 * target_clean does not handle any exceptions.
95 *
96 * Arguments: none
97 *
98 */
99 procedure target_clean is
100 TableNotFound EXCEPTION;
101 PRAGMA EXCEPTION_INIT(TableNotFound, -942);
102 begin
103
104 -- Delete info from FND_CONCURRENT_QUEUE_SIZE table
105 Delete From fnd_Concurrent_Queue_Size
106 where concurrent_queue_id in
107 (Select concurrent_queue_id
108 from fnd_concurrent_queues
109 where manager_type in (2,6));
110
111 Delete from fnd_concurrent_queue_size
112 where concurrent_queue_id in
113 (select concurrent_queue_id
114 from fnd_concurrent_queues
115 where manager_type in
116 ( select service_id
117 from fnd_cp_services
118 where upper(service_handle) in
119 ('FORMSL', 'FORMSMS', 'FORMSMC',
120 'REPSERV', 'TCF', 'APACHE',
121 'JSERV', 'OAMGCS')));
122
123 -- Delete from FND_CONCURRENT_QUEUES_TL table
124 Delete From fnd_Concurrent_Queues_tl
125 where concurrent_queue_id in
126 (Select concurrent_queue_id
127 from fnd_concurrent_queues
128 where manager_type in (2,6));
129
130 Delete from fnd_concurrent_queues_tl
131 where concurrent_queue_id in
132 (select concurrent_queue_id
133 from fnd_concurrent_queues
134 where manager_type in
135 (select service_id
136 from fnd_cp_services
137 where upper(service_handle) in
138 ('FORMSL', 'FORMSMS', 'FORMSMC',
139 'REPSERV', 'TCF', 'APACHE',
140 'JSERV', 'OAMGCS')));
141
142 -- Delete from FND_CONCURRENT_QUEUES table
143 Delete from fnd_concurrent_queues
144 where manager_type in (2,6);
145
146 Delete from fnd_concurrent_queues
147 where manager_type in
148 (select service_id
149 from fnd_cp_services
150 where upper(service_handle) in
151 ('FORMSL', 'FORMSMS', 'FORMSMC',
152 'REPSERV', 'TCF', 'APACHE',
153 'JSERV', 'OAMGCS'));
154
155 -- Delete from FND_CONCURRENT_PROCESSES table
156 Delete from fnd_concurrent_processes;
157
158 -- Delete from FND_NODES table
159 Delete from fnd_nodes;
160
161 -- Delete from FND_CONFLICTS_DOMAIN table
162 --
163 -- NOTE: DELETION from FND_CONFLICTS_DOMAIN uses
164 -- column DYNAMIC which was introduced in a patch.
165 -- This column exists from 11.5.9 onwards.
166 execute immediate 'delete from fnd_conflicts_domain fcd ' ||
167 ' where dynamic = ''Y''' ||
168 ' and not exists (select ''X''' ||
169 ' from fnd_concurrent_requests fcr ' ||
170 ' where fcr.cd_id = fcd.cd_id ' ||
171 ' and phase_code in (''P'', ''R''))';
172
173
174 -- Reset FND_CONCURRENT_QUEUES table
175 Update fnd_concurrent_queues
176 set diagnostic_level = null,
177 target_node = null, max_processes = 0,
178 node_name = null, node_name2 = null,
179 running_processes = 0;
180
181 -- Reset control codes in fnd_concurrent_queues table
182 Update fnd_concurrent_queues
183 set control_code = NULL
184 where control_code not in ('E', 'R', 'X')
185 and control_code IS NOT NULL;
186
187 /*
188 * DELETE all PRINTER Profile option values other than 'noprint'
189 */
190
191 -- Delete all PRITNER Profile option values other than 'noprint'
192 Delete from fnd_profile_option_values
193 where profile_option_value <> 'noprint'
194 and profile_option_id =
195 (select profile_option_id
196 from fnd_profile_options
197 where profile_option_name = 'PRINTER');
198
199 -- Delete fnd_printer info
200 Delete from fnd_printer
201 where printer_name <> 'noprint';
202
203 Delete from fnd_printer_tl
204 where printer_name <> 'noprint';
205
206 -- Update FND_CONCURRENT_PROGRAMS to reset PRINTER_NAME, ENABLE_TRACE
207 Update fnd_concurrent_programs
208 set printer_name = null,
209 enable_trace = 'N'
210 where printer_name is not null or enable_trace = 'Y';
211
212 -- Delete All completed/running Concurrent Requests
213 Delete from fnd_concurrent_requests
214 where phase_code in ('C', 'R');
215
216 -- Delete all pending Oracle Report requests
217 Delete from fnd_concurrent_requests
218 where phase_code = 'P'
219 and (PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID) in
220 (select application_id, concurrent_program_id
221 from fnd_concurrent_programs
222 where execution_method_code = 'P');
223
224 --
225 -- TRUNCATE TABLES
226 --
227 -- Find out the database user for FND and JTF objects
228 get_database_user;
229
230 truncate_table(OracleUserFND, 'FND_ENV_CONTEXT');
231
232 truncate_table(OracleUserFND, 'FND_EVENTS');
233
234 truncate_table(OracleUserFND, 'FND_EVENT_TOKENS');
235
236 truncate_table(OracleUserFND, 'FND_CONCURRENT_DEBUG_INFO');
237
238 truncate_table(OracleUserFND, 'FND_CONC_REQ_STAT');
239
240 truncate_table(OracleUserFND, 'FND_CRM_HISTORY');
241
242 truncate_table(OracleUserFND, 'FND_TM_EVENTS');
243
244 -- Login Related tables.
245
246 truncate_table(OracleUserFND, 'FND_LOGINS');
247
248 truncate_table(OracleUserFND, 'FND_LOGIN_RESPONSIBILITIES');
249
250 truncate_table(OracleUserFND, 'FND_LOGIN_RESP_FORMS');
251
252 truncate_table(OracleUserFND, 'FND_UNSUCCESSFUL_LOGINS');
253
254 -- FND_LOG related tables.
255
256 truncate_table(OracleUserFND, 'FND_LOG_MESSAGES');
257
258 truncate_table(OracleUserFND, 'FND_LOG_EXCEPTIONS');
259
260 truncate_table(OracleUserFND, 'FND_LOG_METRICS');
261
262 truncate_table(OracleUserFND, 'FND_LOG_TRANSACTION_CONTEXT');
263
264 truncate_table(OracleUserFND, 'FND_LOG_UNIQUE_EXCEPTIONS');
265
266 -- OAM Transaction related tables.
267
268 truncate_table(OracleUserFND, 'FND_OAM_APP_SYS_STATUS');
269
270 truncate_table(OracleUserFND, 'FND_OAM_FORMS_RTI');
271
272 truncate_table(OracleUserFND, 'FND_OAM_FRD_LOG');
273
274 truncate_table(OracleUserFND, 'FND_OAM_UPLOAD_STATUS');
275
276 -- execute immediate 'TRUNCATE TABLE ' || OracleUserFND || '.FND_OAM_METVAL';
277
278 truncate_table(OracleUserFND, 'FND_OAM_CONTEXT_FILES');
279
280
281 -- Added following JTF tables based on bug 2949216
282
283 truncate_table(OracleUserJTF, 'JTF_PREFAB_HA_COMPS');
284 truncate_table(OracleUserJTF, 'JTF_PREFAB_HA_FILTERS');
285 truncate_table(OracleUserJTF, 'JTF_PREFAB_HOST_APPS');
286 truncate_table(OracleUserJTF, 'JTF_PREFAB_WSH_POES_B');
287 truncate_table(OracleUserJTF, 'JTF_PREFAB_WSH_POES_TL');
288 truncate_table(OracleUserJTF, 'JTF_PREFAB_WSHP_POLICIES');
289 truncate_table(OracleUserJTF, 'JTF_PREFAB_CACHE_STATS');
290
291 exception
292 when TableNotFound then
293 null;
294 end;
295
296
297
298 /*
299 * procedure: truncate_table_topology
300 *
301 * Purpose: To clean all the topology related tables
302 *
303 * Arguments: null
304 *
305 */
306 procedure truncate_table_topology is
307 begin
308
309 -- Find out the database user for FND and JTF objects
310 get_database_user;
311
312 truncate_table(OracleUserFND,'FND_APPS_SYSTEM');
313 truncate_table(OracleUserFND,'FND_APP_SERVERS');
314 truncate_table(OracleUserFND,'FND_SYSTEM_SERVER_MAP');
315 truncate_table(OracleUserFND,'FND_DATABASES');
316 truncate_table(OracleUserFND,'FND_DATABASE_INSTANCES');
317 truncate_table(OracleUserFND,'FND_DATABASE_SERVICES');
318 truncate_table(OracleUserFND,'FND_DB_SERVICE_MEMBERS');
319 truncate_table(OracleUserFND,'FND_DATABASE_ASSIGNMENTS');
320 truncate_table(OracleUserFND,'FND_TNS_LISTENERS');
321 truncate_table(OracleUserFND,'FND_TNS_LISTENER_PORTS');
322 truncate_table(OracleUserFND,'FND_TNS_ALIASES');
323 truncate_table(OracleUserFND,'FND_TNS_ALIAS_ADDRESSES');
324 truncate_table(OracleUserFND,'FND_TNS_ALIAS_SETS');
325 truncate_table(OracleUserFND,'FND_TNS_ALIAS_SET_USAGE');
326 truncate_table(OracleUserFND,'FND_OAM_CONTEXT_FILES');
327 truncate_table(OracleUserFND,'FND_APPL_TOPS');
328 truncate_table(OracleUserFND,'FND_ORACLE_HOMES');
329 truncate_table(OracleUserFND,'FND_TNS_ALIAS_DESCRIPTIONS');
330 truncate_table(OracleUserFND,'FND_TNS_ALIAS_ADDRESS_LISTS');
331 truncate_table(OracleUserFND,'FND_DB_INSTANCE_PARAMS');
332 end;
333
334 /*
335 * Procedure: setup_clean
336 *
337 * ***************************************************************************
338 * NOTE: If you are not sure what you are doing do not run this procedure.
339 * This API is for Cloning instance and will be used by cloning.
340 * ***************************************************************************
341 *
342 * Purpose: To clean up target database in a cloning case.
343 * It is callers responsibility to do the commit after calling
344 * setup_clean. setup_clean does not handle any exceptions.
345 *
346 * Arguments: none
347 *
348 */
349
350 procedure setup_clean is
351 begin
352 -- Delete info from FND_CONCURRENT_QUEUE_SIZE table
353
354 Delete From fnd_Concurrent_Queue_Size
355 where concurrent_queue_id in
356 (Select concurrent_queue_id
357 from fnd_concurrent_queues
358 where manager_type in (2,6));
359
360 Delete from fnd_concurrent_queue_size
361 where concurrent_queue_id in
362 (select concurrent_queue_id
363 from fnd_concurrent_queues
364 where manager_type in
365 ( select service_id
366 from fnd_cp_services
367 where upper(service_handle) in
368 ('FORMSL', 'FORMSMS', 'FORMSMC',
369 'REPSERV', 'TCF', 'APACHE',
370 'JSERV', 'OAMGCS')));
371
372 -- Delete from FND_CONCURRENT_QUEUES_TL table
373 Delete From fnd_Concurrent_Queues_tl
374 where concurrent_queue_id in
375 (Select concurrent_queue_id
376 from fnd_concurrent_queues
377 where manager_type in (2,6));
378
379 Delete from fnd_concurrent_queues_tl
380 where concurrent_queue_id in
381 (select concurrent_queue_id
382 from fnd_concurrent_queues
383 where manager_type in
384 (select service_id
385 from fnd_cp_services
386 where upper(service_handle) in
387 ('FORMSL', 'FORMSMS', 'FORMSMC',
388 'REPSERV', 'TCF', 'APACHE',
389 'JSERV', 'OAMGCS')));
390
391 -- Delete from FND_CONCURRENT_QUEUES table
392 Delete from fnd_concurrent_queues
393 where manager_type in (2,6);
394
395 Delete from fnd_concurrent_queues
396 where manager_type in
397 (select service_id
398 from fnd_cp_services
399 where upper(service_handle) in
400 ('FORMSL', 'FORMSMS', 'FORMSMC',
401 'REPSERV', 'TCF', 'APACHE',
402 'JSERV', 'OAMGCS'));
403
404 -- Delete from FND_CONCURRENT_PROCESSES table
405 Delete from fnd_concurrent_processes;
406
407 -- Delete from FND_NODES table
408 Delete from fnd_nodes;
409
410 -- Delete from FND_CONFLICTS_DOMAIN table
411 Delete from fnd_conflicts_domain fcd
412 where dynamic = 'Y'
413 and not exists (select 'X'
414 from fnd_concurrent_requests fcr
415 where fcr.cd_id = fcd.cd_id
416 and phase_code in ('P', 'R'));
417
418 -- Reset FND_CONCURRENT_QUEUES table
419 Update fnd_concurrent_queues
420 set diagnostic_level = null,
421 target_node = null, max_processes = 0,
422 node_name = null, node_name2 = null,
423 running_processes = 0;
424
425 -- Reset control codes in fnd_concurrent_queues table
426 Update fnd_concurrent_queues
427 set control_code = NULL
428 where control_code not in ('E', 'R', 'X')
429 and control_code IS NOT NULL;
430
431 --
432 -- TRUNCATE TABLES
433 --
434 -- Find out the database user for FND and JTF objects
435 get_database_user;
436
437 truncate_table(OracleUserFND, 'FND_ENV_CONTEXT');
441 truncate_table(OracleUserFND, 'FND_TM_EVENTS');
438 truncate_table(OracleUserFND, 'FND_CONCURRENT_DEBUG_INFO');
439 truncate_table(OracleUserFND, 'FND_CONC_REQ_STAT');
440 truncate_table(OracleUserFND, 'FND_CRM_HISTORY');
442
443 truncate_table(OracleUserFND , 'FND_OAM_CONTEXT_FILES');
444 truncate_table(OracleUserFND , 'FND_OAM_APP_SYS_STATUS');
445
446 -- Added following JTF tables based on bug 2949216
447 truncate_table(OracleUserJTF , 'JTF_PREFAB_HA_COMPS');
448 truncate_table(OracleUserJTF , 'JTF_PREFAB_HA_FILTERS');
449 truncate_table(OracleUserJTF , 'JTF_PREFAB_HOST_APPS');
450 truncate_table(OracleUserJTF , 'JTF_PREFAB_WSH_POES_B');
451 truncate_table(OracleUserJTF , 'JTF_PREFAB_WSH_POES_TL');
452 truncate_table(OracleUserJTF , 'JTF_PREFAB_WSHP_POLICIES');
453 truncate_table(OracleUserJTF , 'JTF_PREFAB_CACHE_STATS');
454
455
456 -- TRUNCATE TABLES RELATED TO TOPOLOGY
457 truncate_table_topology;
458 end;
459
460 end;