[Home] [Help]
PACKAGE BODY: APPS.HR_DM_CLEANUP
Source
1 PACKAGE BODY hr_dm_cleanup AS
2 /* $Header: perdmclu.pkb 120.0 2005/05/31 17:05:41 appldev noship $ */
3
4
5 /*--------------------------- PRIVATE ROUTINES ---------------------------*/
6
7 -- ------------------------- spawn_cleanup ------------------------
8 -- Description: The requested loader is spawned and details are entered into
9 -- the hr_dm_migration_requests table (via hr_dm_master.insert_request).
10 --
11 --
12 -- Input Parameters
13 -- p_migration - migration id
14 --
15 -- p_phase_id - of phase
16 --
17 -- p_phase_item - of phase item
18 --
19 -- p_loader_name - loader to be spawned
20 --
21 --
22 --
23 -- Output Parameters
24 -- <none>
25 --
26 --
27 -- ------------------------------------------------------------------------
28
29 --
30 PROCEDURE spawn_cleanup(p_migration_id IN NUMBER,
31 p_phase_id IN NUMBER,
32 p_phase_item_id IN NUMBER
33 ) IS
34 --
35
36 e_fatal_error EXCEPTION;
37 l_fatal_error_message VARCHAR2(200);
38 l_phase_id NUMBER;
39 l_request_id NUMBER;
40 l_loader_name VARCHAR2(30);
41 l_loader_conc_program VARCHAR2(30);
42 l_loader_config_file VARCHAR2(30);
43 l_loader_application VARCHAR2(50);
44 l_parameter1 VARCHAR2(100);
45 l_parameter2 VARCHAR2(100);
46 l_parameter3 VARCHAR2(100);
47 l_parameter4 VARCHAR2(100);
48 l_parameter5 VARCHAR2(100);
49 l_parameter6 VARCHAR2(100);
50 l_parameter7 VARCHAR2(100);
51 l_parameter8 VARCHAR2(100);
52 l_parameter9 VARCHAR2(100);
53 l_parameter10 VARCHAR2(100);
54 l_application_id NUMBER;
55
56
57 CURSOR csr_data IS
58 SELECT tbl.loader_name,
59 tbl.loader_conc_program,
60 tbl.loader_config_file,
61 tbl.loader_application,
62 lp.parameter1, lp.parameter2,
63 lp.parameter3, lp.parameter4,
64 lp.parameter4, lp.parameter6,
65 lp.parameter5, lp.parameter8,
66 lp.parameter7, lp.parameter10,
67 lp.application_id
68 FROM hr_dm_tables tbl,
69 hr_dm_loader_params lp,
70 hr_dm_phase_items pi
71 WHERE (pi.phase_item_id = p_phase_item_id)
72 AND (pi.loader_params_id = lp.loader_params_id)
73 AND (lp.table_id = tbl.table_id);
74
75
76 --
77 BEGIN
78 --
79
80 hr_dm_utility.message('ROUT','entry:hr_dm_cleanup.spawn_cleanup', 5);
81 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
82 '(p_phase_id - ' || p_phase_id ||
83 '(p_phase_item_id - ' || p_phase_item_id ||
84 ')', 10);
85
86
87 -- get data for current phase_item_id
88 OPEN csr_data;
89 FETCH csr_data INTO l_loader_name,
90 l_loader_conc_program,
91 l_loader_config_file,
92 l_loader_application,
93 l_parameter1, l_parameter2,
94 l_parameter3, l_parameter4,
95 l_parameter5, l_parameter6,
96 l_parameter7, l_parameter8,
97 l_parameter9, l_parameter10,
98 l_application_id;
99 IF (csr_data%NOTFOUND) THEN
100 l_fatal_error_message := 'Unable to find loader configuration data.';
101 RAISE e_fatal_error;
102 END IF;
103 CLOSE csr_data;
104
105
106
107 hr_dm_utility.message('INFO','Spawning ' || l_loader_name, 15);
108
109 hr_dm_utility.message('INFO','application ' || l_loader_application, 115);
110 hr_dm_utility.message('INFO','program ' || l_loader_conc_program, 115);
111 hr_dm_utility.message('INFO','sub_request TRUE', 115);
112 hr_dm_utility.message('INFO','argument1 ' || l_parameter1, 115);
113 hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
114 hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
115 hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
116 hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
117 hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
118 hr_dm_utility.message('INFO','argument7 ' || l_parameter7, 115);
119 hr_dm_utility.message('INFO','argument8 ' || l_parameter8, 115);
120 hr_dm_utility.message('INFO','argument9 ' || l_parameter9, 115);
121 hr_dm_utility.message('INFO','argument10 ' || l_parameter10, 115);
122
123
124 l_request_id := fnd_request.submit_request(
125 application => l_loader_application,
126 program => l_loader_conc_program,
127 sub_request => TRUE,
128 argument1 => l_parameter1,
129 argument2 => l_parameter2,
130 argument3 => l_parameter3,
131 argument4 => l_parameter4,
132 argument5 => l_parameter5,
133 argument6 => l_parameter6,
134 argument7 => l_parameter7,
135 argument8 => l_parameter8,
136 argument9 => l_parameter9,
137 argument10 => l_parameter10
138 );
139
140
141
142 -- update table hr_dm_migration_requests
143 hr_dm_master.insert_request(p_phase => 'C',
144 p_request_id => l_request_id,
145 p_master_slave => 'S',
146 p_migration_id => p_migration_id,
147 p_phase_id => p_phase_id,
148 p_phase_item_id => p_phase_item_id);
149
150
151 COMMIT;
152
153 hr_dm_utility.message('INFO','Slave request ID#' || l_request_id, 15);
154 IF (l_request_id = 0) THEN
155 l_fatal_error_message := 'Unable to start slave process';
156 hr_dm_master.report_error('C', p_migration_id, l_fatal_error_message,
157 'P');
158 RAISE e_fatal_error;
159 END IF;
160
161
162
163 hr_dm_utility.message('INFO','Spawned C slave', 115);
164 hr_dm_utility.message('SUMM','Spawned C slave', 120);
165 hr_dm_utility.message('ROUT','exit:hr_dm_cleanup.spawn_cleanup', 125);
166 hr_dm_utility.message('PARA','(none)', 130);
167
168
169 -- error handling
170 EXCEPTION
171 WHEN e_fatal_error THEN
172 hr_dm_utility.error(SQLCODE,'hr_dm_cleanup.spawn_cleanup',
173 l_fatal_error_message,'R');
174 hr_dm_master.report_error('C', p_migration_id,
175 'Error in hr_dm_cleanup.spawn_cleanup', 'P');
176 RAISE;
177 WHEN OTHERS THEN
178 hr_dm_utility.error(SQLCODE,'hr_dm_cleanup.spawn_cleanup','(none)','R');
179 hr_dm_master.report_error('C', p_migration_id,
180 'Untrapped error in hr_dm_cleanup.spawn_cleanup',
181 'P');
182 RAISE;
183
184 --
185 END spawn_cleanup;
186 --
187
188
189
190
191
192 /*--------------------------- PUBLIC ROUTINES ----------------------------*/
193
194 -- ------------------------- main ------------------------
195 -- Description: This is the cleanup phase slave. It reads an item from the
196 -- hr_dm_phase_items table for the cleanup phase and calls the appropriate
197 -- code (spawn_cleanup) to spawn slaves to process each phase item.
198 --
199 --
200 -- Input Parameters
201 -- p_migration_id - of current migration
202 --
203 -- p_concurrent_process - Y if program called from CM, otherwise
204 -- N prevents message logging
205 --
206 -- p_last_migration_date - date of last sucessful migration
207 --
208 -- p_process_number - the slave number to allow implicit locking
209 --
210 --
211 -- Output Parameters
212 -- errbuf - buffer for output message (for CM manager)
213 --
214 -- retcode - program return code (for CM manager)
215 --
216 --
217 -- ------------------------------------------------------------------------
218
219 --
220 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
221 retcode OUT NOCOPY NUMBER,
222 p_migration_id IN NUMBER,
223 p_concurrent_process IN VARCHAR2 DEFAULT 'Y',
224 p_last_migration_date IN DATE,
225 p_process_number IN NUMBER
226 ) IS
227 --
228
229 l_current_phase_status VARCHAR2(30);
230 l_phase_id NUMBER;
231 e_fatal_error EXCEPTION;
232 e_fatal_error2 EXCEPTION;
233 l_fatal_error_message VARCHAR2(200);
234 l_loader_name VARCHAR2(30);
235 l_status VARCHAR2(30);
236 l_phase_item_id NUMBER;
237 l_request_data VARCHAR2(100);
238 l_request_id NUMBER;
239 l_call_status BOOLEAN;
240 l_phase VARCHAR2(30);
241 l_dev_phase VARCHAR2(30);
242 l_dev_status VARCHAR2(30);
243 l_message VARCHAR2(240);
244 l_no_of_threads NUMBER;
245 l_business_group_id NUMBER;
246
247
248
249 CURSOR csr_get_pi IS
250 SELECT pi.phase_item_id, pi.loader_name, pi.status
251 FROM hr_dm_phase_items pi,
252 hr_dm_tables tbl
253 WHERE (pi.status = 'NS')
254 AND (pi.phase_id = l_phase_id)
255 AND (pi.loader_name = tbl.loader_name);
256
257
258 CURSOR csr_req_id IS
259 SELECT request_id
260 FROM hr_dm_migration_requests
261 WHERE phase_item_id = l_phase_item_id;
262
263 -- get the migration details
264 CURSOR csr_migration_info IS
265 SELECT business_group_id
266 FROM hr_dm_migrations
267 WHERE migration_id = p_migration_id;
268
269 --
270 BEGIN
271 --
272
273 -- initialize messaging (only for concurrent processing)
274 IF (p_concurrent_process = 'Y') THEN
275 hr_dm_utility.message_init;
276 END IF;
277
278 hr_dm_utility.message('ROUT','entry:hr_dm_cleanup.main', 5);
279 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
280 ')(p_last_migration_date - ' ||
281 p_last_migration_date ||
282 ')', 10);
283
284 -- get the current phase_id
285 l_phase_id := hr_dm_utility.get_phase_id('C', p_migration_id);
286
287
288 -- get the business_group_id
289 OPEN csr_migration_info;
290 FETCH csr_migration_info INTO l_business_group_id;
291 IF csr_migration_info%NOTFOUND THEN
292 CLOSE csr_migration_info;
293 l_fatal_error_message := 'hr_dm_cleanup.main :- Migration Id ' ||
294 TO_CHAR(p_migration_id) || ' not found.';
295 RAISE e_fatal_error2;
296 END IF;
297 CLOSE csr_migration_info;
298
299 -- get the number of threads to enable modulus locking
300 l_no_of_threads := hr_dm_utility.number_of_threads(l_business_group_id);
301
302
303 -- get status of C phase, is phase completed?
304 -- if null returned, then assume it is NS.
305 l_current_phase_status := NVL(hr_dm_utility.get_phase_status('C',
306 p_migration_id), 'NS');
307
308 -- if status is error, then raise an exception
309 IF (l_current_phase_status = 'E') THEN
310 l_fatal_error_message := 'Current phase in error - slave exiting';
311 RAISE e_fatal_error2;
312 END IF;
313
314
315 -- see if this is the first run? (l_request_data = NULL or '?')
316 -- or
317 -- is it a restart after a slave has finished? (l_request_data = paused phase
318 -- item code)
319 l_request_data := fnd_conc_global.request_data;
320 hr_dm_utility.message('INFO','l_request_data ' || l_request_data, 11);
321 IF (NVL(l_request_data, '?') IS NOT NULL) THEN
322 -- unpaused processing...
323
324 -- check for error in slave
325 l_phase_item_id := TO_NUMBER(l_request_data);
326
327 -- find request_id
328 OPEN csr_req_id;
329 FETCH csr_req_id INTO l_request_id;
330 CLOSE csr_req_id;
331
332 l_call_status := fnd_concurrent.get_request_status(l_request_id, '', '',
333 l_phase, l_status, l_dev_phase,
334 l_dev_status, l_message);
335 -- make sure that each slave is complete and normal, if not then log
336 IF ( NOT( (l_dev_phase = 'COMPLETE') AND (l_dev_status = 'NORMAL') )) THEN
337 -- update status to error
338 hr_dm_utility.update_phase_items(p_new_status => 'E',
339 p_id => l_phase_item_id);
340 l_fatal_error_message := 'Sub-slave process in error - slave exiting';
341 RAISE e_fatal_error;
342 ELSE
343 -- update status to completed
344 hr_dm_utility.update_phase_items(p_new_status => 'C',
345 p_id => l_phase_item_id);
346 END IF;
347
348 END IF;
349
350
351 -- get status of C phase, is phase completed?
352 -- if null returned, then assume it is NS.
353 l_current_phase_status := NVL(hr_dm_utility.get_phase_status('C',
354 p_migration_id), 'NS');
355
356 -- if status is error, then raise an exception
357 IF (l_current_phase_status = 'E') THEN
358 l_fatal_error_message := 'Current phase in error - slave exiting';
359 RAISE e_fatal_error2;
360 END IF;
361
362
363 -- now process remaining rows...
364
365
366 -- fetch a row from the phase items table
367 OPEN csr_get_pi;
368 FETCH csr_get_pi INTO l_phase_item_id, l_loader_name,
369 l_status;
370 IF (csr_get_pi%FOUND) THEN
371
372 -- update status to started
373 hr_dm_utility.update_phase_items(p_new_status => 'S',
374 p_id => l_phase_item_id);
375
376 -- send info on current table to logfile
377 hr_dm_utility.message('INFO','Processing - ' || l_loader_name, 13);
378
379
380 -- call code for cleanup item
381 spawn_cleanup(p_migration_id => p_migration_id,
382 p_phase_id => l_phase_id,
383 p_phase_item_id => l_phase_item_id);
384
385
386 -- pause master whilst slaves process data...
387 -- set request data to indicate paused phase
388 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
389 request_data => l_phase_item_id);
390
391 errbuf := 'No errors - examine logfiles for detailed reports.';
392 END IF;
393
394 IF (csr_get_pi%NOTFOUND) THEN
395 errbuf := 'No errors - examine logfiles for detailed reports.';
396 END IF;
397
398 CLOSE csr_get_pi;
399
400
401 -- set up return values to concurrent manager
402 retcode := 0;
403 IF (NVL(l_request_data, '?') <> '?') THEN
404 errbuf := 'No errors - examine logfiles for detailed reports.';
405 ELSE
406 errbuf := 'Slave Controller is paused.';
407 END IF;
408
409
410 hr_dm_utility.message('INFO','C - main controller', 15);
411 hr_dm_utility.message('SUMM','C - main controller', 20);
412 hr_dm_utility.message('ROUT','exit:hr_dm_cleanup.main', 25);
413 hr_dm_utility.message('PARA','(retcode - ' || retcode ||
414 ')(errbuf - ' || errbuf || ')', 30);
415
416 -- error handling
417 EXCEPTION
418 WHEN e_fatal_error THEN
419 retcode := 2;
420 errbuf := 'An error occurred during the migration - examine logfiles ' ||
421 'for detailed reports.';
422 hr_dm_utility.error(SQLCODE,'hr_dm_cleanup.main',l_fatal_error_message,
423 'C');
424 hr_dm_utility.update_phase_items(p_new_status => 'E',
425 p_id => l_phase_item_id);
429 errbuf := 'An error occurred during the migration - examine logfiles ' ||
426 hr_dm_utility.error(SQLCODE,'hr_dm_cleanup.main','(none)','R');
427 WHEN e_fatal_error2 THEN
428 retcode := 0;
430 'for detailed reports.';
431 hr_dm_utility.error(SQLCODE,'hr_dm_cleanup.main',l_fatal_error_message,
432 'C');
433 hr_dm_utility.update_phase_items(p_new_status => 'E',
434 p_id => l_phase_item_id);
435 hr_dm_utility.error(SQLCODE,'hr_dm_cleanup.main','(none)','R');
436 WHEN OTHERS THEN
437 retcode := 2;
438 errbuf := 'An error occurred during the migration - examine logfiles ' ||
439 'for detailed reports.';
440 -- update status to error
441 hr_dm_utility.update_phase_items(p_new_status => 'E',
442 p_id => l_phase_item_id);
443 hr_dm_utility.error(SQLCODE,'hr_dm_cleanup.main','(none)','R');
444
445
446 --
447 END main;
448 --
449
450
451 -- ------------------------- post_cleanup_process ------------------------
452 -- Description: This procedure can be used to call any code for the cleanup
453 -- phase that is not multi-threaded.
454 --
455 --
456 -- Input Parameters
457 -- r_migration_data - migration record
458 --
459 --
460 -- Output Parameters
461 -- <none>
462 --
463 --
464 -- ------------------------------------------------------------------------
465
466 --
467 PROCEDURE post_cleanup_process(r_migration_data IN
468 hr_dm_utility.r_migration_rec) IS
469 --
470
471
472 --
473 BEGIN
474 --
475
476 hr_dm_utility.message('ROUT','entry:hr_dm_cleanup.post_cleanup_process', 5);
477 hr_dm_utility.message('PARA','(r_migration_data - record)', 10);
478
479
480 --
481 -- non-concurrent program code for clean up phase
482 --
483 -- to be added...
484 -- ...if required
485 --
486
487
488 hr_dm_utility.message('INFO','Clean Up - non CM', 15);
489 hr_dm_utility.message('SUMM','Clean Up - non CM', 20);
490 hr_dm_utility.message('ROUT','exit:hr_dm_cleanup.post_cleanup_process', 25);
491 hr_dm_utility.message('PARA','(none)', 30);
492
493 -- error handling
494 EXCEPTION
495 WHEN OTHERS THEN
496 hr_dm_utility.error(SQLCODE,'hr_dm_cleanup.post_cleanup_process',
497 '(none)','R');
498 RAISE;
499
500 --
501 END post_cleanup_process;
502 --
503
504
505 END hr_dm_cleanup;