1 package body fnd_conc_clone as
2 /* $Header: AFCPCLNB.pls 120.1 2005/07/02 03:58:34 appldev 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 running_processes = 0;
179
180 -- Reset control codes in fnd_concurrent_queues table
181 Update fnd_concurrent_queues
182 set control_code = NULL
183 where control_code not in ('E', 'R', 'X')
184 and control_code IS NOT NULL;
185
186 /*
187 * DELETE all PRINTER Profile option values other than 'noprint'
188 */
189
190 -- Delete all PRITNER Profile option values other than 'noprint'
191 Delete from fnd_profile_option_values
192 where profile_option_value <> 'noprint'
193 and profile_option_id =
194 (select profile_option_id
195 from fnd_profile_options
196 where profile_option_name = 'PRINTER');
197
198 -- Delete fnd_printer info
199 Delete from fnd_printer
200 where printer_name <> 'noprint';
201
202 Delete from fnd_printer_tl
203 where printer_name <> 'noprint';
204
205 -- Update FND_CONCURRENT_PROGRAMS to reset PRINTER_NAME, ENABLE_TRACE
206 Update fnd_concurrent_programs
207 set printer_name = null,
208 enable_trace = 'N'
209 where printer_name is not null or enable_trace = 'Y';
210
211 -- Delete All completed/running Concurrent Requests
212 Delete from fnd_concurrent_requests
213 where phase_code in ('C', 'R');
214
215 -- Delete all pending Oracle Report requests
216 Delete from fnd_concurrent_requests
217 where phase_code = 'P'
218 and (PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID) in
219 (select application_id, concurrent_program_id
220 from fnd_concurrent_programs
221 where execution_method_code = 'P');
222
223 --
224 -- TRUNCATE TABLES
225 --
226 -- Find out the database user for FND and JTF objects
227 get_database_user;
228
229 truncate_table(OracleUserFND, 'FND_ENV_CONTEXT');
230
231 truncate_table(OracleUserFND, 'FND_LOG_MESSAGES');
232
233 truncate_table(OracleUserFND, 'FND_EVENTS');
234
235 truncate_table(OracleUserFND, 'FND_EVENT_TOKENS');
236
237 truncate_table(OracleUserFND, 'FND_CONCURRENT_DEBUG_INFO');
238
239 truncate_table(OracleUserFND, 'FND_CONC_REQ_STAT');
240
241 truncate_table(OracleUserFND, 'FND_CRM_HISTORY');
242
243 truncate_table(OracleUserFND, 'FND_TM_EVENTS');
244
245 -- Login Related tables.
246
247 truncate_table(OracleUserFND, 'FND_LOGINS');
248
249 truncate_table(OracleUserFND, 'FND_LOGIN_RESPONSIBILITIES');
250
251 truncate_table(OracleUserFND, 'FND_LOGIN_RESP_FORMS');
252
253 truncate_table(OracleUserFND, 'FND_UNSUCCESSFUL_LOGINS');
254
255 -- FND_LOG related tables.
256
257 truncate_table(OracleUserFND, 'FND_LOG_MESSAGES');
258
259 truncate_table(OracleUserFND, 'FND_LOG_EXCEPTIONS');
260
261 truncate_table(OracleUserFND, 'FND_LOG_METRICS');
262
263 truncate_table(OracleUserFND, 'FND_LOG_TRANSACTION_CONTEXT');
264
265 truncate_table(OracleUserFND, 'FND_LOG_UNIQUE_EXCEPTIONS');
266
267 -- OAM Transaction related tables.
268
269 truncate_table(OracleUserFND, 'FND_OAM_APP_SYS_STATUS');
270
271 truncate_table(OracleUserFND, 'FND_OAM_FORMS_RTI');
272
273 truncate_table(OracleUserFND, 'FND_OAM_FRD_LOG');
274
275 truncate_table(OracleUserFND, 'FND_OAM_UPLOAD_STATUS');
276
277 -- execute immediate 'TRUNCATE TABLE ' || OracleUserFND || '.FND_OAM_METVAL';
278
279 truncate_table(OracleUserFND, 'FND_OAM_CONTEXT_FILES');
280
281
282 -- Added following JTF tables based on bug 2949216
283
284 truncate_table(OracleUserJTF, 'JTF_PREFAB_HA_COMPS');
285 truncate_table(OracleUserJTF, 'JTF_PREFAB_HA_FILTERS');
286 truncate_table(OracleUserJTF, 'JTF_PREFAB_HOST_APPS');
287 truncate_table(OracleUserJTF, 'JTF_PREFAB_WSH_POES_B');
288 truncate_table(OracleUserJTF, 'JTF_PREFAB_WSH_POES_TL');
289 truncate_table(OracleUserJTF, 'JTF_PREFAB_WSHP_POLICIES');
290 truncate_table(OracleUserJTF, 'JTF_PREFAB_CACHE_STATS');
291
292 exception
293 when TableNotFound then
294 null;
295 end;
296
297
298
299 /*
300 * procedure: truncate_table_topology
301 *
302 * Purpose: To clean all the topology related tables
303 *
304 * Arguments: null
305 *
306 */
307 procedure truncate_table_topology is
308 begin
309
310 -- Find out the database user for FND and JTF objects
311 get_database_user;
312
313 truncate_table(OracleUserFND,'FND_APPS_SYSTEM');
314 truncate_table(OracleUserFND,'FND_APP_SERVERS');
315 truncate_table(OracleUserFND,'FND_SYSTEM_SERVER_MAP');
316 truncate_table(OracleUserFND,'FND_DATABASES');
317 truncate_table(OracleUserFND,'FND_DATABASE_INSTANCES');
318 truncate_table(OracleUserFND,'FND_DATABASE_SERVICES');
319 truncate_table(OracleUserFND,'FND_DB_SERVICE_MEMBERS');
320 truncate_table(OracleUserFND,'FND_DATABASE_ASSIGNMENTS');
321 truncate_table(OracleUserFND,'FND_TNS_LISTENERS');
322 truncate_table(OracleUserFND,'FND_TNS_LISTENER_PORTS');
323 truncate_table(OracleUserFND,'FND_TNS_ALIASES');
324 truncate_table(OracleUserFND,'FND_TNS_ALIAS_ADDRESSES');
325 truncate_table(OracleUserFND,'FND_TNS_ALIAS_SETS');
326 truncate_table(OracleUserFND,'FND_TNS_ALIAS_SET_USAGE');
327 truncate_table(OracleUserFND,'FND_OAM_CONTEXT_FILES');
328 truncate_table(OracleUserFND,'FND_APPL_TOPS');
329 truncate_table(OracleUserFND,'FND_ORACLE_HOMES');
330 truncate_table(OracleUserFND,'FND_TNS_ALIAS_DESCRIPTIONS');
331 truncate_table(OracleUserFND,'FND_TNS_ALIAS_ADDRESS_LISTS');
332 truncate_table(OracleUserFND,'FND_DB_INSTANCE_PARAMS');
333 end;
334
335 /*
336 * Procedure: setup_clean
337 *
338 * ***************************************************************************
339 * NOTE: If you are not sure what you are doing do not run this procedure.
340 * This API is for Cloning instance and will be used by cloning.
341 * ***************************************************************************
342 *
343 * Purpose: To clean up target database in a cloning case.
344 * It is callers responsibility to do the commit after calling
345 * setup_clean. setup_clean does not handle any exceptions.
346 *
347 * Arguments: none
348 *
349 */
350
351 procedure setup_clean is
352 begin
353 -- Delete info from FND_CONCURRENT_QUEUE_SIZE table
354
355 Delete From fnd_Concurrent_Queue_Size
356 where concurrent_queue_id in
357 (Select concurrent_queue_id
358 from fnd_concurrent_queues
359 where manager_type in (2,6));
360
361 Delete from fnd_concurrent_queue_size
362 where concurrent_queue_id in
363 (select concurrent_queue_id
364 from fnd_concurrent_queues
365 where manager_type in
366 ( select service_id
367 from fnd_cp_services
368 where upper(service_handle) in
369 ('FORMSL', 'FORMSMS', 'FORMSMC',
370 'REPSERV', 'TCF', 'APACHE',
371 'JSERV', 'OAMGCS')));
372
373 -- Delete from FND_CONCURRENT_QUEUES_TL table
374 Delete From fnd_Concurrent_Queues_tl
375 where concurrent_queue_id in
376 (Select concurrent_queue_id
377 from fnd_concurrent_queues
378 where manager_type in (2,6));
379
380 Delete from fnd_concurrent_queues_tl
381 where concurrent_queue_id in
382 (select concurrent_queue_id
383 from fnd_concurrent_queues
384 where manager_type in
385 (select service_id
386 from fnd_cp_services
387 where upper(service_handle) in
388 ('FORMSL', 'FORMSMS', 'FORMSMC',
389 'REPSERV', 'TCF', 'APACHE',
390 'JSERV', 'OAMGCS')));
391
392 -- Delete from FND_CONCURRENT_QUEUES table
393 Delete from fnd_concurrent_queues
394 where manager_type in (2,6);
395
396 Delete from fnd_concurrent_queues
397 where manager_type in
398 (select service_id
399 from fnd_cp_services
400 where upper(service_handle) in
401 ('FORMSL', 'FORMSMS', 'FORMSMC',
402 'REPSERV', 'TCF', 'APACHE',
403 'JSERV', 'OAMGCS'));
404
405 -- Delete from FND_NODES table
406 Delete from fnd_nodes;
407
408 --
409 -- TRUNCATE TABLES
410 --
411 -- Find out the database user for FND and JTF objects
412 get_database_user;
413
414 truncate_table(OracleUserFND , 'FND_OAM_CONTEXT_FILES');
415 truncate_table(OracleUserFND , 'FND_OAM_APP_SYS_STATUS');
416
417 -- Added following JTF tables based on bug 2949216
418 truncate_table(OracleUserJTF , 'JTF_PREFAB_HA_COMPS');
419 truncate_table(OracleUserJTF , 'JTF_PREFAB_HA_FILTERS');
420 truncate_table(OracleUserJTF , 'JTF_PREFAB_HOST_APPS');
421 truncate_table(OracleUserJTF , 'JTF_PREFAB_WSH_POES_B');
422 truncate_table(OracleUserJTF , 'JTF_PREFAB_WSH_POES_TL');
423 truncate_table(OracleUserJTF , 'JTF_PREFAB_WSHP_POLICIES');
424 truncate_table(OracleUserJTF , 'JTF_PREFAB_CACHE_STATS');
425
426
427 -- TRUNCATE TABLES RELATED TO TOPOLOGY
428 truncate_table_topology;
429 end;
430
431 end;
[Home] [Help]