[Home] [Help]
PACKAGE BODY: APPS.HR_DM_AOL_UP
Source
1 PACKAGE BODY hr_dm_aol_up AS
2 /* $Header: perdmaul.pkb 120.0 2005/05/31 17:03:39 appldev noship $ */
3
4
5 /*--------------------------- PRIVATE ROUTINES ---------------------------*/
6
7
8
9 -- ------------------------- add_details ------------------------
10 -- Description: The parameters for the requested loader are
11 -- written to the upload file.
12 --
13 --
14 -- Input Parameters
15 -- p_migration_id - migration id
16 --
17 -- p_phase_id - of phase
18 --
19 -- p_phase_item - of phase item
20 --
21 -- p_loader_name - loader to be added
22 --
23 -- p_file_handle - file handle to write commands to
24 --
25 --
26 --
27 -- Output Parameters
28 -- <none>
29 --
30 --
31 -- ------------------------------------------------------------------------
32
33
34 --
35 PROCEDURE add_details(p_migration_id IN NUMBER,
36 p_phase_id IN NUMBER,
37 p_phase_item_id IN NUMBER,
38 p_file_handle IN UTL_FILE.FILE_TYPE
39 ) IS
40 --
41
42 l_loader_name VARCHAR2(30);
43 l_loader_conc_program VARCHAR2(30);
44 l_loader_config_file VARCHAR2(30);
45 l_config VARCHAR2(100);
46 l_parameter1 VARCHAR2(100);
47 l_parameter2 VARCHAR2(100);
48 l_parameter3 VARCHAR2(100);
49 l_parameter4 VARCHAR2(100);
50 l_parameter5 VARCHAR2(100);
51 l_parameter6 VARCHAR2(100);
52 l_parameter7 VARCHAR2(100);
53 l_parameter8 VARCHAR2(100);
54 l_parameter9 VARCHAR2(100);
55 l_parameter10 VARCHAR2(100);
56 l_loader_application VARCHAR2(50);
57 l_application_id NUMBER;
58 l_program VARCHAR2(30);
59 e_fatal_error EXCEPTION;
60 l_fatal_error_message VARCHAR2(200);
61 l_filename VARCHAR2(50);
62 l_request_id NUMBER;
63 l_string VARCHAR2(2000);
64 l_destination_database VARCHAR2(30);
65 l_upload VARCHAR2(30);
66 l_security_group VARCHAR2(30);
67
68 CURSOR csr_data IS
69 SELECT lp.loader_name,
70 lp.loader_conc_program,
71 lp.loader_config_file,
72 lp.loader_application,
73 lp.parameter_1, lp.parameter_2,
74 lp.parameter_3, lp.parameter_4,
75 lp.parameter_5, lp.parameter_6,
76 lp.parameter_7, lp.parameter_8,
77 lp.parameter_9, lp.parameter_10,
78 lp.application_id,
79 lp.filename
80 FROM hr_dm_loader_phase_items lp,
81 hr_dm_phase_items pi
82 WHERE pi.phase_item_id = p_phase_item_id
83 AND pi.phase_item_id = lp.ua_phase_item_id;
84
85 CURSOR csr_migration_info IS
86 SELECT sg.security_group_key
87 FROM hr_dm_migrations dm,
88 fnd_security_groups sg,
89 per_business_groups pbg
90 WHERE dm.migration_id = p_migration_id
91 AND dm.business_group_id = pbg.business_group_id
92 AND pbg.security_group_id = sg.security_group_id;
93
94
95 --
96 BEGIN
97 --
98
99 hr_dm_utility.message('ROUT','entry:hr_dm_aol_up.add_details', 5);
100 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
101 '(p_phase_id - ' || p_phase_id ||
102 '(p_phase_item_id - ' || p_phase_item_id ||
103 ')', 10);
104
105 -- get data for current phase_item_id
106 OPEN csr_data;
107 FETCH csr_data INTO l_loader_name,
108 l_loader_conc_program,
109 l_loader_config_file,
110 l_loader_application,
111 l_parameter1, l_parameter2,
112 l_parameter3, l_parameter4,
113 l_parameter5, l_parameter6,
114 l_parameter7, l_parameter8,
115 l_parameter9, l_parameter10,
116 l_application_id,
117 l_filename;
118 IF (csr_data%NOTFOUND) THEN
119 l_fatal_error_message := 'Unable to find loader configuration data.';
120 RAISE e_fatal_error;
121 END IF;
122 CLOSE csr_data;
123
124 -- set up local data
125 l_program := 'HRDMSLVA';
126 IF (l_loader_conc_program = 'FNDLOAD') THEN
127 l_program := l_program || 'G';
128 ELSIF (l_loader_conc_program = 'FNDSLOAD') THEN
129 l_program := l_program || 'S';
130 END IF;
131
132 -- remove loader arguements from parameters
133 -- except for :mode, :data and :config
134 -- where the loader is FNDLOAD
135 IF (l_loader_conc_program = 'FNDLOAD') AND
136 (l_loader_name <> 'Lookups') THEN
137 l_parameter4 := NULL;
138 l_parameter5 := NULL;
139 l_parameter6 := NULL;
140 l_parameter7 := NULL;
141 l_parameter8 := NULL;
142 l_parameter9 := NULL;
143 l_parameter10 := NULL;
144 END IF;
145
146 -- remove loader arguements from parameters
147 -- where the loader is FNDSLOAD
148 IF (l_loader_conc_program = 'FNDSLOAD') THEN
149 l_parameter4 := NULL;
150 l_parameter5 := NULL;
151 l_parameter6 := NULL;
152 l_parameter7 := NULL;
153 l_parameter8 := NULL;
154 l_parameter9 := NULL;
155 l_parameter10 := NULL;
156 END IF;
157
158
159 -- use UPLOAD
160 -- unless we are uploading lookups, then use upload_partial
161
162 IF (l_loader_name = 'Lookups') THEN
163
164 -- also need the security group key
165 OPEN csr_migration_info;
166 FETCH csr_migration_info INTO l_security_group;
167 CLOSE csr_migration_info;
168
169 l_upload := 'UPLOAD_PARTIAL';
170
171 IF l_parameter5 = ':secgrp' THEN
172 l_parameter5 := 'SECURITY_GROUP=' || l_security_group;
173 END IF;
174
175 ELSE
176 l_upload := 'UPLOAD';
177 END IF;
178
179 -- find the parameter with the :mode and replace with l_upload
180 IF (l_parameter1 = ':mode') THEN l_parameter1 := l_upload;
181 ELSIF (l_parameter2 = ':mode') THEN l_parameter2 := l_upload;
182 ELSIF (l_parameter3 = ':mode') THEN l_parameter3 := l_upload;
183 ELSIF (l_parameter4 = ':mode') THEN l_parameter4 := l_upload;
184 ELSIF (l_parameter5 = ':mode') THEN l_parameter5 := l_upload;
185 ELSIF (l_parameter6 = ':mode') THEN l_parameter6 := l_upload;
186 ELSIF (l_parameter7 = ':mode') THEN l_parameter7 := l_upload;
187 ELSIF (l_parameter8 = ':mode') THEN l_parameter8 := l_upload;
188 ELSIF (l_parameter9 = ':mode') THEN l_parameter9 := l_upload;
189 ELSIF (l_parameter10 = ':mode') THEN l_parameter10 := l_upload;
190 END IF;
191
192 -- find the parameter with the :config and replace with the path
193 -- and config file filename
194 l_config := '@fnd:patch/115/import/' || l_loader_config_file;
195
196 IF (l_parameter1 = ':config') THEN l_parameter1 := l_config;
197 ELSIF (l_parameter2 = ':config') THEN l_parameter2 := l_config;
198 ELSIF (l_parameter3 = ':config') THEN l_parameter3 := l_config;
199 ELSIF (l_parameter4 = ':config') THEN l_parameter4 := l_config;
200 ELSIF (l_parameter5 = ':config') THEN l_parameter5 := l_config;
201 ELSIF (l_parameter6 = ':config') THEN l_parameter6 := l_config;
202 ELSIF (l_parameter7 = ':config') THEN l_parameter7 := l_config;
203 ELSIF (l_parameter8 = ':config') THEN l_parameter8 := l_config;
204 ELSIF (l_parameter9 = ':config') THEN l_parameter9 := l_config;
205 ELSIF (l_parameter10 = ':config') THEN l_parameter10 := l_config;
206 END IF;
207
208
209 -- find the data file and replace with the path and filename
210 IF (l_parameter1 = ':data') THEN l_parameter1 := l_filename;
211 ELSIF (l_parameter2 = ':data') THEN l_parameter2 := l_filename;
212 ELSIF (l_parameter3 = ':data') THEN l_parameter3 := l_filename;
213 ELSIF (l_parameter4 = ':data') THEN l_parameter4 := l_filename;
214 ELSIF (l_parameter5 = ':data') THEN l_parameter5 := l_filename;
215 ELSIF (l_parameter6 = ':data') THEN l_parameter6 := l_filename;
216 ELSIF (l_parameter7 = ':data') THEN l_parameter7 := l_filename;
217 ELSIF (l_parameter8 = ':data') THEN l_parameter8 := l_filename;
218 ELSIF (l_parameter9 = ':data') THEN l_parameter9 := l_filename;
219 ELSIF (l_parameter10 = ':data') THEN l_parameter10 := l_filename;
220 END IF;
221
222
223 -- remove any :selective tags from the parameter list
224
225 IF (l_parameter1 = ':selective') THEN l_parameter1 := null;
226 ELSIF (l_parameter2 = ':selective') THEN l_parameter2 := null;
227 ELSIF (l_parameter3 = ':selective') THEN l_parameter3 := null;
228 ELSIF (l_parameter4 = ':selective') THEN l_parameter4 := null;
229 ELSIF (l_parameter5 = ':selective') THEN l_parameter5 := null;
230 ELSIF (l_parameter6 = ':selective') THEN l_parameter6 := null;
231 ELSIF (l_parameter7 = ':selective') THEN l_parameter7 := null;
232 ELSIF (l_parameter8 = ':selective') THEN l_parameter8 := null;
233 ELSIF (l_parameter9 = ':selective') THEN l_parameter9 := null;
234 ELSIF (l_parameter10 = ':selective') THEN l_parameter10 := null;
235 END IF;
236
237 -- remove any :secgrp tags from the parameter list
238
239 IF (l_parameter1 = ':secgrp') THEN l_parameter1 := null;
240 ELSIF (l_parameter2 = ':secgrp') THEN l_parameter2 := null;
241 ELSIF (l_parameter3 = ':secgrp') THEN l_parameter3 := null;
242 ELSIF (l_parameter4 = ':secgrp') THEN l_parameter4 := null;
243 ELSIF (l_parameter5 = ':secgrp') THEN l_parameter5 := null;
244 ELSIF (l_parameter6 = ':secgrp') THEN l_parameter6 := null;
245 ELSIF (l_parameter7 = ':secgrp') THEN l_parameter7 := null;
246 ELSIF (l_parameter8 = ':secgrp') THEN l_parameter8 := null;
247 ELSIF (l_parameter9 = ':secgrp') THEN l_parameter9 := null;
248 ELSIF (l_parameter10 = ':secgrp') THEN l_parameter10 := null;
249 END IF;
250
251 hr_dm_utility.message('INFO','application ' || l_loader_application, 115);
252 hr_dm_utility.message('INFO','program ' || l_program, 115);
253 hr_dm_utility.message('INFO','sub_request TRUE', 115);
254 hr_dm_utility.message('INFO','argument1 ' || l_parameter1, 115);
255 hr_dm_utility.message('INFO','argument2 ' || l_parameter2, 115);
256 hr_dm_utility.message('INFO','argument3 ' || l_parameter3, 115);
257 hr_dm_utility.message('INFO','argument4 ' || l_parameter4, 115);
258 hr_dm_utility.message('INFO','argument5 ' || l_parameter5, 115);
259 hr_dm_utility.message('INFO','argument6 ' || l_parameter6, 115);
260 hr_dm_utility.message('INFO','argument7 ' || l_parameter7, 115);
261 hr_dm_utility.message('INFO','argument8 ' || l_parameter8, 115);
262 hr_dm_utility.message('INFO','argument9 ' || l_parameter9, 115);
263 hr_dm_utility.message('INFO','argument10 ' || l_parameter10, 115);
264
265 -- get the destination database name
266 SELECT DESTINATION_DATABASE_INSTANCE
267 INTO l_destination_database
268 FROM HR_DM_MIGRATIONS
269 WHERE migration_id = p_migration_id;
270
271
272 -- write data to UA file
273
274 l_string:= l_loader_conc_program || ' apps/apps@' ||
275 l_destination_database || ' 0 Y ' ||
276 l_parameter1 || ' ' ||
277 l_parameter2 || ' ' ||
278 l_parameter3 || ' ' ||
279 l_parameter4 || ' ' ||
280 l_parameter5 || ' ' ||
281 l_parameter6 || ' ' ||
282 l_parameter7 || ' ' ||
283 l_parameter8 || ' ' ||
284 l_parameter9 || ' ' ||
285 l_parameter10;
286
287
288 utl_file.put_line(p_file_handle, l_string);
289
290 hr_dm_utility.message('INFO','Added UA entry', 115);
291 hr_dm_utility.message('SUMM','Added UA entry', 120);
292 hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.add_details', 125);
293 hr_dm_utility.message('PARA','(none)', 130);
294
295
296 -- error handling
297 EXCEPTION
298 WHEN e_fatal_error THEN
299 hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.add_details',
300 l_fatal_error_message, 'R');
301 hr_dm_master.report_error('UA', p_migration_id,
302 'Error in hr_dm_aol_up.add_details', 'P');
303 RAISE;
304 WHEN OTHERS THEN
305 hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.add_details','(none)','R');
306 hr_dm_master.report_error('UA', p_migration_id,
307 'Untrapped error in hr_dm_aol_up.add_details',
308 'P');
309 RAISE;
310
311 --
312 END add_details;
313 --
314
315
316
317 /*--------------------------- PUBLIC ROUTINES ----------------------------*/
318
319 -- ------------------------- post_aol_process ------------------------
320 -- Description: This is the post processing code for the UA phase.
321 --
322 -- It copies across the ID_FLEX_STRUCTURE_NAMEs for the current business
323 -- group from the source database.
324 --
325 --
326 -- Input Parameters
327 -- p_migration_id - of current migration
328 --
329 --
330 -- Output Parameters
331 -- <none>
332 --
333 --
334 -- ------------------------------------------------------------------------
335
336 --
337 PROCEDURE post_aol_process(p_migration_id IN NUMBER) IS
338 --
339
340 l_org_information4 VARCHAR2(30);
341 l_org_information5 VARCHAR2(30);
342 l_org_information6 VARCHAR2(30);
343 l_org_information7 VARCHAR2(30);
344 l_org_information8 VARCHAR2(30);
345 l_org_information14 VARCHAR2(30);
346 l_org_information4_id NUMBER;
347 l_org_information5_id NUMBER;
348 l_org_information6_id NUMBER;
349 l_org_information7_id NUMBER;
350 l_org_information8_id NUMBER;
351 l_org_information14_id NUMBER;
352 l_business_group_id NUMBER;
353
354 --
355 BEGIN
356 --
357
358 hr_dm_utility.message('ROUT','entry:hr_dm_aol_up.post_aol_process', 5);
359 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
360 ')', 10);
361
362 SELECT business_group_id
363 INTO l_business_group_id
364 FROM hr_dm_migrations
365 WHERE migration_id = p_migration_id;
366
367
368 SELECT ORG_INFORMATION4,
369 ORG_INFORMATION5,
370 ORG_INFORMATION6,
371 ORG_INFORMATION7,
372 ORG_INFORMATION8,
373 ORG_INFORMATION14
374 INTO l_org_information4,
375 l_org_information5,
376 l_org_information6,
377 l_org_information7,
378 l_org_information8,
379 l_org_information14
380 FROM hr_dm_exp_hr_org_inf_flx_v;
381
382 SELECT ID_FLEX_NUM
383 INTO l_org_information4_id
384 FROM fnd_id_flex_structures_vl
385 WHERE ID_FLEX_STRUCTURE_NAME = l_org_information4
386 AND ID_FLEX_CODE = 'GRD';
387 SELECT ID_FLEX_NUM
388 INTO l_org_information5_id
389 FROM fnd_id_flex_structures_vl
390 WHERE ID_FLEX_STRUCTURE_NAME = l_org_information5
391 AND ID_FLEX_CODE = 'GRP';
392 SELECT ID_FLEX_NUM
393 INTO l_org_information6_id
394 FROM fnd_id_flex_structures_vl
395 WHERE ID_FLEX_STRUCTURE_NAME = l_org_information6
396 AND ID_FLEX_CODE = 'JOB';
397 SELECT ID_FLEX_NUM
398 INTO l_org_information7_id
399 FROM fnd_id_flex_structures_vl
400 WHERE ID_FLEX_STRUCTURE_NAME = l_org_information7
401 AND ID_FLEX_CODE = 'COST';
402 SELECT ID_FLEX_NUM
403 INTO l_org_information8_id
404 FROM fnd_id_flex_structures_vl
405 WHERE ID_FLEX_STRUCTURE_NAME = l_org_information8
406 AND ID_FLEX_CODE = 'POS';
407 SELECT security_group_id
408 INTO l_org_information14_id
409 FROM fnd_security_groups_vl
410 WHERE security_group_key = l_org_information14;
411
412 UPDATE hr_organization_information
413 SET ORG_INFORMATION4 = l_org_information4_id,
414 ORG_INFORMATION5 = l_org_information5_id,
415 ORG_INFORMATION6 = l_org_information6_id,
416 ORG_INFORMATION7 = l_org_information7_id,
417 ORG_INFORMATION8 = l_org_information8_id,
418 ORG_INFORMATION14 = l_org_information14_id
419 WHERE ORG_INFORMATION_CONTEXT = 'Business Group Information'
420 AND ORGANIZATION_ID = l_business_group_id;
421
422 COMMIT;
423
424
425 hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.post_aol_process', 25);
426 hr_dm_utility.message('PARA','(none)', 10);
427 EXCEPTION
428 WHEN OTHERS THEN
429 hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.post_aol_process',
430 '(none)','R');
434 END post_aol_process;
431 RAISE;
432
433 --
435 --
436
437
438
439
440
441 -- ------------------------- main ------------------------
442 -- Description: This is the aol upload phase slave. It reads an item from
443 -- the hr_dm_phase_items table for the aol upload phase and calls the
444 -- appropriate aol loader.
445 --
446 --
447 -- On a non-first run (l_request_data <> null), the status of the slave
448 -- is checked to ensure that it has completed. If not then the phase item
449 -- status is set to error and the slave exits. Otherwise the phase item is
450 -- marked as being completed.
451 --
452 --
453 -- The status of the phase is then checked to see if it has errored.
454 -- If so, the slave exits.
455 --
456 -- A row is fetched from the phase_items table that has the status of NS.
457 -- If no rows are returned then the slave exits as there are no more rows
458 -- for it to process.
459 --
460 --
461 --
462 -- Input Parameters
463 -- p_migration_id - of current migration
464 --
465 -- p_concurrent_process - Y if program called from CM, otherwise
466 -- N prevents message logging
467 --
468 -- p_last_migration_date - date of last sucessful migration
469 --
470 -- p_process_number - the slave number to allow implicit locking
471 --
472 --
473 -- Output Parameters
474 -- errbuf - buffer for output message (for CM manager)
475 --
476 -- retcode - program return code (for CM manager)
477 --
478 --
479 -- ------------------------------------------------------------------------
480
481
482 --
483 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
484 retcode OUT NOCOPY NUMBER,
485 p_migration_id IN NUMBER,
486 p_concurrent_process IN VARCHAR2 DEFAULT 'Y',
487 p_last_migration_date IN DATE,
488 p_process_number IN NUMBER
489 ) IS
490 --
491
492 l_current_phase_status VARCHAR2(30);
493 l_phase_id NUMBER;
494 e_fatal_error EXCEPTION;
495 e_fatal_error2 EXCEPTION;
496 l_fatal_error_message VARCHAR2(200);
497 l_loader_name VARCHAR2(30);
498 l_status VARCHAR2(30);
499 l_phase_item_id NUMBER;
500 l_phase VARCHAR2(30);
501 l_location VARCHAR2(2000);
502 l_aol_filename VARCHAR2(30);
503 l_filehandle UTL_FILE.FILE_TYPE;
504 l_sysdate VARCHAR2(30);
505 l_destination_database VARCHAR2(30);
506 l_loader_group VARCHAR2(30);
507 l_source_db VARCHAR2(30);
508 l_destination_db VARCHAR2(30);
509 l_migration_type VARCHAR2(30);
510 l_migration_type_meaning VARCHAR2(80);
511 l_business_group_id NUMBER;
512 l_business_group_name hr_dm_migrations.business_group_name%type;
513 l_selective_mc VARCHAR2(2000);
514
515
516 CURSOR csr_get_pi IS
517 SELECT pi.phase_item_id,
518 pi.status
519 FROM hr_dm_phase_items pi
520 WHERE (pi.status = 'NS')
521 AND (pi.phase_id = l_phase_id)
522 AND (pi.loader_name = l_loader_group)
523 ORDER BY pi.phase_item_id;
524
525 CURSOR csr_loader IS
526 SELECT tbl.loader_name
527 FROM hr_dm_groups grp,
528 hr_dm_application_groups apg,
529 hr_dm_table_groupings tbg,
530 hr_dm_tables tbl,
531 hr_dm_migrations mig
532 WHERE tbl.table_id = tbg.table_id
533 AND tbg.group_id = grp.group_id
534 AND grp.group_type = 'A'
535 AND grp.group_id = apg.group_id
536 AND apg.application_id = mig.application_id
537 AND apg.migration_type = mig.migration_type
538 AND mig.migration_id = p_migration_id;
539
540 CURSOR csr_mig_info IS
541 SELECT source_database_instance,
542 destination_database_instance,
543 migration_type,
544 hr_general.decode_lookup('HR_DM_MIGRATION_TYPE',
545 migration_type),
546 business_group_id,
547 business_group_name,
548 selective_migration_criteria
549 FROM hr_dm_migrations
550 WHERE migration_id = p_migration_id;
551
552
553 --
554 BEGIN
555 --
556
557 -- initialize messaging (only for concurrent processing)
558 IF (p_concurrent_process = 'Y') THEN
559 hr_dm_utility.message_init;
560 END IF;
561
562 hr_dm_utility.message('ROUT','entry:hr_dm_aol_up.main', 5);
563 hr_dm_utility.message('PARA','(p_migration_id - ' || p_migration_id ||
564 ')(p_last_migration_date - ' ||
565 p_last_migration_date || ')', 10);
566
567 -- get the current phase_id
568 l_phase_id := hr_dm_utility.get_phase_id('UA', p_migration_id);
569
570
571 -- get status of UA phase, is phase completed?
572 -- if null returned, then assume it is NS.
576
573 l_current_phase_status := NVL(hr_dm_utility.get_phase_status('UA',
574 p_migration_id), 'NS');
575
577 -- if status is error, then raise an exception
578 IF (l_current_phase_status = 'E') THEN
579 l_fatal_error_message := 'Current phase in error - slave exiting';
580 RAISE e_fatal_error2;
581 END IF;
582
583
584
585 -- find logfile directory and open file for AOL Loader commands
586 fnd_profile.get('UTL_FILE_LOG', l_location);
587 l_aol_filename := 'DM' || p_migration_id || '.txt';
588 hr_dm_utility.message('INFO','l_location ' || l_location, 13);
589 hr_dm_utility.message('INFO','l_aol_filename ' || l_aol_filename, 13);
590
591 IF l_location IS NULL THEN
592 l_fatal_error_message := 'The profile named Stored Procedure Log ' ||
593 'Directory has not been set. Set to a ' ||
594 'valid location where the database can ' ||
595 'write files to.';
596 RAISE e_fatal_error2;
597 END IF;
598
599 hr_dm_utility.message('INFO','Opening file', 13);
600 l_filehandle := utl_file.fopen(l_location, l_aol_filename, 'w');
601 hr_dm_utility.message('INFO','File opened ', 13);
602
603 -- get migration info
604 OPEN csr_mig_info;
605 FETCH csr_mig_info INTO
606 l_source_db,
607 l_destination_db,
608 l_migration_type,
609 l_migration_type_meaning,
610 l_business_group_id,
611 l_business_group_name,
612 l_selective_mc;
613 CLOSE csr_mig_info;
614
615
616 -- add header info
617 utl_file.put_line(l_filehandle, '# Data Migrator AOL Upload');
618 utl_file.put_line(l_filehandle, '# ');
619 utl_file.put_line(l_filehandle, '# ');
620 utl_file.put_line(l_filehandle, '# Migration Information:');
621 utl_file.put_line(l_filehandle, '# ');
622 utl_file.put_line(l_filehandle, '# Migration ID ' || p_migration_id);
623 SELECT to_char(sysdate,'HH:MI DD-MON-YYYY')
624 INTO l_sysdate
625 FROM dual;
626 utl_file.put_line(l_filehandle, '# Date ' || l_sysdate);
627
628 utl_file.put_line(l_filehandle, '# Source Database ' || l_source_db);
629 utl_file.put_line(l_filehandle, '# Destination Database ' || l_destination_db);
630 utl_file.put_line(l_filehandle, '# Migration Type (code) ' || l_migration_type);
631 utl_file.put_line(l_filehandle, '# Migration Type ' || l_migration_type_meaning);
632 utl_file.put_line(l_filehandle, '# Business Group ID ' || l_business_group_id);
633 utl_file.put_line(l_filehandle, '# Business Group Name ' || l_business_group_name);
634 utl_file.put_line(l_filehandle, '# Selective Migration Criteria');
635 utl_file.put_line(l_filehandle, '# ' ||
636 NVL(SUBSTR(l_selective_mc,1,240),'(Not Applicable)'));
637
638 utl_file.put_line(l_filehandle, '# ');
639 utl_file.put_line(l_filehandle, '# ');
640 utl_file.put_line(l_filehandle, '# AOL Loader commands:');
641 utl_file.put_line(l_filehandle, '# Replace apps/apps with the appropriate values.');
642 utl_file.put_line(l_filehandle, '# ');
643
644
645 -- loop around all groups
646 OPEN csr_loader;
647 LOOP
648 FETCH csr_loader INTO l_loader_group;
649 EXIT WHEN csr_loader%NOTFOUND;
650
651 -- send info on current table to logfile
652 hr_dm_utility.message('INFO','Processing - ' || l_loader_group, 13);
653
654 -- show start of group
655 utl_file.put_line(l_filehandle, '# ');
656 utl_file.put_line(l_filehandle, '# <' || l_loader_group || '>');
657 utl_file.put_line(l_filehandle, '# ');
658
659
660 -- process each group
661 OPEN csr_get_pi;
662 LOOP
663 FETCH csr_get_pi INTO l_phase_item_id,
664 l_status;
665 EXIT WHEN csr_get_pi%NOTFOUND;
666
667 -- add entry for this phase item
668
669 -- update status to started
670 hr_dm_utility.update_phase_items(p_new_status => 'S',
671 p_id => l_phase_item_id);
672
673 -- call code for AOL loader
674 add_details(p_migration_id => p_migration_id,
675 p_phase_id => l_phase_id,
676 p_phase_item_id => l_phase_item_id,
677 p_file_handle => l_filehandle);
678
679 -- update status to completed
680 hr_dm_utility.update_phase_items(p_new_status => 'C',
681 p_id => l_phase_item_id);
682
683 END LOOP;
684
685 CLOSE csr_get_pi;
686
687
688 -- show end of group
689 utl_file.put_line(l_filehandle, '# ');
690 utl_file.put_line(l_filehandle, '# </' || l_loader_group || '>');
691 utl_file.put_line(l_filehandle, '# ');
692
693 END LOOP;
694 CLOSE csr_loader;
695
696
697
698 utl_file.put_line(l_filehandle, '# ');
699 utl_file.put_line(l_filehandle, '# End of AOL Uploader commands.');
700 utl_file.put_line(l_filehandle, '# ');
701
702
703 -- get the destination database name
704 SELECT DESTINATION_DATABASE_INSTANCE
705 INTO l_destination_database
706 FROM HR_DM_MIGRATIONS
707 WHERE migration_id = p_migration_id;
708
709
710 utl_file.put_line(l_filehandle, '# Taskflow upload commands');
711 utl_file.put_line(l_filehandle, '# ');
715 ' @usrwkflw.sql');
712 utl_file.put_line(l_filehandle, 'sqlplus apps/apps@' || l_destination_database ||
713 ' @hrwkflow.sql');
714 utl_file.put_line(l_filehandle, 'sqlplus apps/apps@' || l_destination_database ||
716 utl_file.put_line(l_filehandle, '# ');
717 utl_file.put_line(l_filehandle, '# End of Taskflow upload commands');
718 utl_file.put_line(l_filehandle, '# ');
719
720 -- close file
721 utl_file.fclose(l_filehandle);
722
723
724
725
726
727 -- set up return values to concurrent manager
728 retcode := 0;
729 errbuf := 'No errors - examine logfiles for detailed reports.';
730
731
732 hr_dm_utility.message('INFO','UA - main controller', 15);
733 hr_dm_utility.message('SUMM','UA - main controller', 20);
734 hr_dm_utility.message('ROUT','exit:hr_dm_aol_up.main', 25);
735 hr_dm_utility.message('PARA','(retcode - ' || retcode ||
736 ')(errbuf - ' || errbuf || ')', 30);
737
738 -- error handling
739 EXCEPTION
740 WHEN e_fatal_error THEN
741 retcode := 2;
742 errbuf := 'An error occurred during the migration - ' ||
743 'examine logfiles for detailed reports.';
744 hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main',
745 l_fatal_error_message,'UA');
746 hr_dm_utility.update_phase_items(p_new_status => 'E',
747 p_id => l_phase_item_id);
748 hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
749 WHEN e_fatal_error2 THEN
750 retcode := 0;
751 errbuf := 'An error occurred during the migration - ' ||
752 'examine logfiles for detailed reports.';
753 hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main',
754 l_fatal_error_message,'UA');
755 hr_dm_utility.update_phases(p_new_status => 'E',
756 p_id => l_phase_id);
757 hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
758 WHEN OTHERS THEN
759 retcode := 2;
760 errbuf := 'An error occurred during the migration - ' ||
761 'examine logfiles for detailed reports.';
762 -- update status to error
763 hr_dm_utility.update_phase_items(p_new_status => 'E',
764 p_id => l_phase_item_id);
765 hr_dm_utility.error(SQLCODE,'hr_dm_aol_up.main','(none)','R');
766
767
768 --
769 END main;
770 --
771
772
773 END hr_dm_aol_up;