eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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');
431     RAISE;
432 
433 --
434 END post_aol_process;
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.
573 l_current_phase_status := NVL(hr_dm_utility.get_phase_status('UA',
574                                                      p_migration_id), 'NS');
575 
576 
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, '# ');
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 ||
715                   ' @usrwkflw.sql');
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;