1 PACKAGE BODY hr_dm_upload AS
2 /* $Header: perdmup.pkb 120.0 2005/05/30 21:17:52 appldev noship $ */
3
4
5 /*---------------------------- PRIVATE ROUTINES -----------------------------*/
6
7 --
8 PROCEDURE spawn_slave(p_current_phase IN VARCHAR2,
9 p_migration_id IN NUMBER,
10 p_phase_id IN NUMBER,
11 p_phase_item_id IN NUMBER,
12 p_batch_id IN NUMBER ) IS
13 --
14
15 l_request_id NUMBER;
16 e_fatal_error EXCEPTION;
17 l_fatal_error_message VARCHAR2(200);
18 l_application VARCHAR2(30);
19 l_program VARCHAR2(30);
20
21
22 --
23 BEGIN
24 --
25
26 hr_dm_utility.message('ROUT','entry:hr_dm_upload.spawn_slave', 5);
27
28 -- set up local data
29 l_application := 'PER';
30 l_program := 'DATAPUMP';
31
32 -- spawn slave
33
34 l_request_id := fnd_request.submit_request(
35 application => l_application,
36 program => l_program,
37 sub_request => TRUE,
38 argument1 => p_batch_id,
39 argument2 => 'N' );
40
41
42 -- update table hr_dm_migration_requests
43 hr_dm_master.insert_request(p_phase => p_current_phase,
44 p_request_id => l_request_id,
45 p_master_slave => 'S',
46 p_migration_id => p_migration_id,
47 p_phase_id => p_phase_id,
48 p_phase_item_id => p_phase_item_id);
49
50
51 COMMIT;
52 hr_dm_utility.message('INFO','Slave request ID#' || l_request_id, 15);
53 IF (l_request_id = 0) THEN
54 l_fatal_error_message := 'Unable to start slave process';
55 hr_dm_master.report_error(p_current_phase, p_migration_id, l_fatal_error_message, 'P');
56 RAISE e_fatal_error;
57 END IF;
58
59
60 hr_dm_utility.message('INFO','Spawned datapump as slave process', 15);
61 hr_dm_utility.message('SUMM','Spawned datapump as slave process', 20);
62 hr_dm_utility.message('ROUT','exit:hr_dm_upload.spawn_slave', 25);
63 hr_dm_utility.message('PARA','(none)', 30);
64
65
66 -- error handling
67 EXCEPTION
68 WHEN e_fatal_error THEN
69 hr_dm_utility.error(SQLCODE,'hr_dm_upload.spawn_slave',l_fatal_error_message,'R');
70 hr_dm_master.report_error(p_current_phase, p_migration_id,
71 'Error in hr_dm_upload.spawn_slave', 'P');
72 RAISE;
73 WHEN OTHERS THEN
74 hr_dm_utility.error(SQLCODE,'hr_dm_upload.spawn_slave','(none)','R');
75 hr_dm_master.report_error(p_current_phase, p_migration_id,
76 'Untrapped error in hr_dm_upload.spawn_slave', 'P');
77 RAISE;
78 --
79 END spawn_slave;
80 --
81
82
83 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
84
85 -- ------------------------- main ------------------------
86 -- Description: This is the upload phase slave. It reads an item from the
87 -- hr_dm_phase_items table for the upload phase. It spawns the slave process
88 -- to start data pump which in turns spawns multiple processes to process
89 -- a batch or group of table. If the group is uploaded succesfully, then the
90 -- above process is repeated for an unprocessed batch to be uploaded from
91 -- hr_dm_phase_items table. When all the upload batches are finished in
92 -- hr_dm_phase_items table then the processing is stopped.
93 --
94 --
95 -- Input Parameters
96 -- p_migration_id - of current migration
97 --
98 -- p_concurrent_process - Y if program called from CM, otherwise
99 -- N prevents message logging
100 --
101 -- p_last_migration_date - date of last sucessful migration
102 --
103 -- p_process_number - process number given to slave process by
104 -- master process. The first process gets
105 -- number 1, second gets number 2 and so on
106 -- the maximum nuber being equal to the
107 -- number of threads.
108 --
109 --
110 -- Output Parameters
111 -- errbuf - buffer for output message (for CM manager)
112 --
113 -- retcode - program return code (for CM manager)
114 --
115 --
116 -- ------------------------------------------------------------------------
117 --
118 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
119 retcode OUT NOCOPY NUMBER,
120 p_migration_id IN NUMBER,
121 p_concurrent_process IN VARCHAR2 DEFAULT 'Y',
122 p_last_migration_date IN DATE,
123 p_process_number IN NUMBER
124 ) IS
125 --
126 l_dummy number;
127 l_current_phase_status VARCHAR2(30);
128 l_phase_id NUMBER;
129 e_fatal_error EXCEPTION;
130 l_fatal_error_message VARCHAR2(200);
131 e_slave_error EXCEPTION;
132 l_slave_error_message VARCHAR2(200);
133 l_status VARCHAR2(30);
134 l_batch_id NUMBER;
135 l_phase_item_id NUMBER;
136 l_request_data VARCHAR2(100);
137 l_request_id NUMBER;
138 l_call_status BOOLEAN;
139 l_phase VARCHAR2(30);
140 l_dev_phase VARCHAR2(30);
141 l_dev_status VARCHAR2(30);
142 l_message VARCHAR2(240);
143
144
145 -- get the unprocessed groups to upload
146 CURSOR csr_get_pi IS
147 SELECT pi.phase_item_id,
148 pi.status,
149 pi.batch_id
150 FROM hr_dm_phase_items pi,
151 hr_dm_application_groups apg
152 WHERE pi.status = 'NS'
153 AND pi.phase_id = l_phase_id
154 AND pi.group_id = apg.group_id
155 ORDER BY apg.group_order;
156
157
158 -- get the details of the last data pump process spawned by this slave process.
159 CURSOR csr_req_id IS
160 SELECT req.request_id,
161 pi.batch_id
162 FROM hr_dm_migration_requests req,
163 hr_dm_phase_items pi
164 WHERE pi.phase_item_id = l_phase_item_id
165 and pi.phase_item_id = req.phase_item_id;
166
167
168 -- check whether all the child processes spawned by datapump has been completed.
169 -- This cursor checks is there any process which is still running.
170
171 CURSOR csr_chk_dp_child_proc_status (p_request_id number) IS
172 SELECT 1
173 FROM fnd_concurrent_requests
174 WHERE parent_request_id = p_request_id
175 AND phase_code <> 'C';
176
177 -- get info of all the data pump processes which failed.
178 CURSOR csr_failed_dp_child_proc (p_request_id number) IS
179 SELECT request_id
180 FROM fnd_concurrent_requests
181 WHERE (parent_request_id = p_request_id OR
182 request_id = p_request_id)
183 AND status_code <> 'C';
184
185 -- get the status of data pump process spawned by this slave process. Read the status
186 -- from hr_pump_batch_headers table.
187
188 CURSOR csr_dp_batch_status IS
189 SELECT batch_status
190 FROM hr_pump_batch_headers
191 WHERE batch_id = l_batch_id;
192 --
193 BEGIN
194 --
195
196 -- initialize messaging (only for concurrent processing)
197 IF (p_concurrent_process = 'Y') THEN
198 hr_dm_utility.message_init;
199 END IF;
200
201 hr_dm_utility.message('ROUT','entry:hr_dm_upload.main', 5);
202 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
203 ')(p_last_migration_date - ' || p_last_migration_date ||
204 ')', 10);
205
206
207 -- Set the variable so as to disable the trigger on the table.
208 hr_general.g_data_migrator_mode := 'Y';
209
210 -- set the profile to disable the audit trigger
211 fnd_profile.put (name => 'AUDITTRAIL:ACTIVATE'
212 ,val => 'N'
213 );
214
215 -- get the current phase_id
216 l_phase_id := hr_dm_utility.get_phase_id('UP', p_migration_id);
217
218
219 -- see if this is the first run? (l_request_data = NULL or '?')
220 -- or
221 -- is it a restart after a slave has finished? (l_request_data = paused phase item code)
222
223 l_request_data := fnd_conc_global.request_data;
224 hr_dm_utility.message('INFO','l_request_data ' || l_request_data, 11);
225
226 --
227 -- l_request_data will be 'null' when the upload process is invoked by the
228 -- concurrent manager. It then spawns the data pump and pauses until the datapump
229 -- finsh processing. It is re-started automatically after data pump processing
230 -- is completed. l_request_data will contain the value set before pausing this
231 -- program.
232 --
233
234 IF l_request_data IS NOT NULL THEN
235
236 -- It is a restart after a slave process has finished.
237
238 -- get the phase_item_id stored in request_data i.e Upload group processed by the slave
239 -- process.
240
241 l_phase_item_id := TO_NUMBER(l_request_data);
242
243 -- find details about the finished slave process like request_id and data pump batch
244 -- which it processed.
245 --
246
247 OPEN csr_req_id;
248 FETCH csr_req_id INTO l_request_id,
249 l_batch_id;
250 IF csr_req_id%NOTFOUND THEN
251 CLOSE csr_req_id;
252 l_fatal_error_message := 'Could not find the details of phase by slave ' ||
253 'process. l_phase_item_id = ' || to_char(l_phase_item_id);
254 RAISE e_fatal_error;
255 END IF;
256 CLOSE csr_req_id;
257
258 -- check whether all the data pump slave processes have been completed, if not then
259 -- sleep for 5 seconds. Sleep is implemented by reading from a non-existant pipe,
260 -- using time out feature to give delay time.
261 LOOP
262 OPEN csr_chk_dp_child_proc_status (l_request_id);
263 FETCH csr_chk_dp_child_proc_status INTO l_dummy;
264
265 -- if no row found it means all child processes spawned by datapump have been
266 -- completed hence exit this loop.
267
268 IF csr_chk_dp_child_proc_status%NOTFOUND THEN
269 EXIT;
270 END IF;
271 CLOSE csr_chk_dp_child_proc_status;
272
273 -- some datapump child processes are still running. Pause this process
274 -- for 5 seconds and check the status again after 5 seconds.
275
276 l_dummy := DBMS_PIPE.RECEIVE_MESSAGE('temporary_unused_hrdm_pipe', 5);
277
278 END LOOP;
279
280 -- All the datapump child processes are completed. Get the status of data pump
281 -- batch header.
282
283 OPEN csr_dp_batch_status;
284 FETCH csr_dp_batch_status INTO l_status;
285 IF csr_dp_batch_status%NOTFOUND THEN
286 CLOSE csr_dp_batch_status;
287 l_fatal_error_message := 'Could not find the data pump batch for ' ||
288 'l_batch_id = ' || to_char(l_batch_id);
289 RAISE e_fatal_error;
290 END IF;
291 CLOSE csr_dp_batch_status;
292
293 hr_dm_utility.message('INFO','Data Pump batch status ' || l_status, 12);
294
295 -- if l_status = 'C', it means the group has been uploaded successfully.
296 -- - update the group status to complete in phase_items.
297 -- - check the status of the child process so as if any has erred then
298 -- write to log file for information purpose. This is for information
299 -- only as it does not matter if one of the child process has error as
300 -- far as group is uploaded successfully by other slave processes.
301 -- if status is other than 'C', it means group has not been uploaded
302 -- succesfully.
303
304 IF l_status = 'C' then
305 -- update status to completed
306 hr_dm_utility.update_phase_items(p_new_status => 'C',
307 p_id => l_phase_item_id);
308
309 -- check if any data pump child process failed by checking status of
310 -- all the data pump process status.
311
312 FOR csr_failed_dp_child_proc_rec in csr_failed_dp_child_proc (l_request_id)
313 LOOP
314
315 -- write into log file about the failed child processes.
316 hr_dm_utility.message('INFO','Warning :- Failed Data Pump process ' ||
317 'request_id =' || TO_CHAR(l_request_id), 13);
318 END LOOP;
319
320 ELSE
321 l_fatal_error_message := 'Batch not uploaded successfully for phase_item_id = ' ||
322 to_char(l_phase_item_id) || ' - slave exiting.';
323 RAISE e_fatal_error;
324 END IF;
325
326 -- set the concurrent process global var to null.
327 l_request_data := NULL;
328 END IF;
329
330
331 -- get status of UP phase, is phase completed?
332 -- if null returned, then assume it is NS.
333 l_current_phase_status := NVL(hr_dm_utility.get_phase_status('UP', p_migration_id), 'NS');
334
335 -- if status is error, then raise an exception
336 IF (l_current_phase_status = 'E') THEN
337 l_slave_error_message := 'Current phase in error - slave exiting';
338 RAISE e_slave_error;
339 END IF;
340
341 --
342 -- if it is first call to submit the data pump request then l_status value will be null.
343 -- For subsequent calls it depends whether the group has been uploaded successfully by
344 -- previous data pump procedure. If the previous process has failed then we have to stop
345 -- the upload process.
346 --
347
348 IF NVL(l_status,'OK') <> 'E' then
349 -- Get the unprocessed group to br uploaded from phase items table to upload.
350 OPEN csr_get_pi;
351 FETCH csr_get_pi INTO l_phase_item_id,
352 l_status,
353 l_batch_id;
354 IF (csr_get_pi%FOUND) THEN
355
356 close csr_get_pi;
357 -- update status to started
358 hr_dm_utility.update_phase_items(p_new_status => 'S',
359 p_id => l_phase_item_id);
360
361 -- send info on current table to logfile
362 hr_dm_utility.message('INFO','Processing batch- ' || l_batch_id , 13);
363
364
365 -- call code to trigger data pump to upload the data.
366
367 spawn_slave(p_current_phase => 'UP',
368 p_migration_id => p_migration_id,
369 p_phase_id => l_phase_id,
370 p_phase_item_id => l_phase_item_id,
371 p_batch_id => l_batch_id);
372
373
374 -- pause master whilst slaves process data...
375 -- set request data to indicate paused phase
376 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
377 request_data => l_phase_item_id);
378
379 errbuf := 'No errors - examine logfiles for detailed reports.';
380 ELSE
381 close csr_get_pi;
382 END IF;
383 END IF; -- NVL(l_status,'OK') <> 'E'
384
385
386 -- set up return values to concurrent manager
387 retcode := 0;
388 IF (NVL(l_request_data, '?') <> '?') THEN
389 errbuf := 'No errors - examine logfiles for detailed reports.';
390 ELSE
391 errbuf := 'Slave Controller is paused.';
392 END IF;
393
394
395 hr_dm_utility.message('INFO','UP - main controller', 15);
396 hr_dm_utility.message('SUMM','UP - main controller', 20);
397 hr_dm_utility.message('ROUT','exit:hr_dm_upload.main', 25);
398 hr_dm_utility.message('PARA','(retcode - ' || retcode ||
399 ')(errbuf - ' || errbuf || ')', 30);
400
401 -- error handling
402 EXCEPTION
403 WHEN e_fatal_error THEN
404 retcode := 2;
405 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
406 hr_dm_utility.error(SQLCODE,'hr_dm_upload.main',l_fatal_error_message,'R');
407 hr_dm_utility.update_phase_items(p_new_status => 'E',
408 p_id => l_phase_item_id);
409 hr_dm_utility.error(SQLCODE,'hr_dm_upload.main','(none)','R');
410 WHEN e_slave_error THEN
411 retcode := 0;
412 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
413 hr_dm_utility.error(SQLCODE,'hr_dm_upload.main',l_fatal_error_message,'R');
414 hr_dm_utility.error(SQLCODE,'hr_dm_upload.main','(none)','R');
415 WHEN OTHERS THEN
416 retcode := 2;
417 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
418 -- update status to error
419 hr_dm_utility.update_phase_items(p_new_status => 'E',
420 p_id => l_phase_item_id);
421 hr_dm_utility.error(SQLCODE,'hr_dm_upload.main','(none)','R');
422
423 --
424 END main;
425 --
426
427
428 -- ------------------------- set_globals ------------------------
429 -- Description: This function is called from the header of each TUPs package
430 -- to ensure that the global variables are set for the current session.
431 -- The return value is a dummy value (can be any number).
432 --
433 --
434 -- Input Parameters
435 -- <none>
436 --
437 -- Output Parameters
438 -- 1 - dummy value
439 --
440 --
441 -- ------------------------------------------------------------------------
442 --
443 FUNCTION set_globals RETURN NUMBER IS
444 --
445
446 e_fatal_error EXCEPTION;
447 l_fatal_error_message VARCHAR2(200);
448
449
450 CURSOR csr_src_db IS
451 SELECT source_database_instance
452 FROM hr_dm_migrations
453 WHERE status NOT IN ('F', 'A');
454
455 --
456 BEGIN
457 --
458
459 hr_dm_utility.message('ROUT','entry:hr_dm_upload.set_globals', 5);
460 hr_dm_utility.message('PARA','(none)', 10);
461
462 -- Set the variable so as to disable the trigger on the table.
463 hr_general.g_data_migrator_mode := 'Y';
464
465 -- set the profile to disable the audit trigger
466 fnd_profile.put (name => 'AUDITTRAIL:ACTIVATE'
467 ,val => 'N'
468 );
469
470 -- store the current migration's source_database_name
471 -- for access from the TUPS
472 OPEN csr_src_db;
473 FETCH csr_src_db INTO hr_dm_upload.g_data_migrator_source_db;
474 CLOSE csr_src_db;
475
476 HR_DATA_PUMP.message('source db is - ' ||
477 NVL(hr_dm_upload.g_data_migrator_source_db, '<null>'));
478
479 IF hr_dm_upload.g_data_migrator_source_db IS NULL THEN
480 l_fatal_error_message := 'No Started migration could be identified.';
481 RAISE e_fatal_error;
482 END IF;
483
484
485
486 hr_dm_utility.message('INFO','UP - main controller', 15);
487 hr_dm_utility.message('SUMM','UP - main controller', 20);
488 hr_dm_utility.message('ROUT','exit:hr_dm_upload.set_globals', 25);
489 hr_dm_utility.message('PARA','(1 - 1)', 30);
490
491
492 RETURN(1);
493
494 EXCEPTION
495 WHEN e_fatal_error THEN
496 hr_dm_utility.error(SQLCODE,'hr_dm_upload.set_globals',
497 l_fatal_error_message,'R');
498
499 WHEN OTHERS THEN
500 hr_dm_utility.error(SQLCODE,'hr_dm_upload.set_globals','(none)','R');
501 RAISE;
502
503 --
504 END set_globals;
505 --
506
507
508 END hr_dm_upload;
[Home] [Help]