[Home] [Help]
PACKAGE BODY: APPS.BEN_DM_GEN_MASTER
Source
1 PACKAGE BODY ben_dm_gen_master AS
2 /* $Header: benfdmgenm.pkb 120.0 2006/05/04 04:48:29 nkkrishn noship $ */
3 g_package varchar2(50) := 'ben_dm_gen_master.' ;
4 type numTab is table of number index by binary_integer;
5 type charTab is table of varchar2(60) index by binary_integer;
6 t_tab_short_name charTab;
7
8
9 procedure main_generator
10 (
11 errbuf out nocopy varchar2,
12 retcode out nocopy number ,
13 p_migration_id in number ,
14 p_concurrent_process in varchar2 default 'Y',
15 p_last_migration_date in varchar2,
16 p_process_number in number ,
17 p_dir_name in varchar2,
18 p_file_name in varchar2,
19 p_delimiter in varchar2 default fnd_global.local_chr(01),
20 p_business_group_id in number default null
21 )
22 is
23
24 -- used for indexing of pl/sql table.
25 l_count number;
26 l_generator_version ben_dm_tables.generator_version%type;
27
28 l_phase_item_id ben_dm_phase_items.phase_item_id%type;
29 l_phase_id ben_dm_phases.phase_id%type;
30
31 l_current_phase_status varchar2(30);
32 e_fatal_error exception;
33 e_fatal_error2 exception;
34 l_fatal_error_message varchar2(200);
35 l_missing_who_info varchar2(1);
36 l_no_of_threads number;
37 l_last_migration_date date ;
38
39 -- cursor to get table for which TUPS/TDS have to be genrated
40
41 cursor csr_get_table is
42 select tbl.table_id
43 ,tbl.table_name
44 ,tbl.table_alias
45 ,phs.phase_id
46 ,itm.phase_item_id
47 from ben_dm_tables tbl,
48 ben_dm_phase_items itm,
49 ben_dm_phases phs
50 where phs.migration_id = p_migration_id
51 and phs.phase_name = 'G'
52 and phs.phase_id = itm.phase_id
53 and mod(itm.phase_item_id,l_no_of_threads) + 1 = p_process_number
54 and itm.status = 'NS'
55 and itm.table_name = tbl.table_name
56 and rownum < 2;
57
58
59 l_csr_get_table_rec csr_get_table%rowtype;
60 l_proc varchar2(75) ;
61
62
63
64 begin
65
66 l_proc := g_package || 'main_generator' ;
67
68 hr_utility.set_location('Entering:'||l_proc, 5);
69
70
71 l_last_migration_date := to_date(p_last_migration_date, 'YYYY/MM/DD HH24:MI:SS');
72 -- initialize messaging
73 if p_concurrent_process = 'Y' then
74 ben_dm_utility.message_init;
75 end if;
76
77 ben_dm_utility.message('ROUT','entry:'||l_proc , 5);
78 ben_dm_utility.message('PARA','(errbuf - ' || errbuf ||
79 ')(retcode - ' || retcode ||
80 ')(p_migration_id - ' || p_migration_id ||
81 ')(p_concurrent_process - ' || p_concurrent_process ||
82 ')(p_last_migration_date - '|| l_last_migration_date ||
83 ')', 10);
84
85 l_no_of_threads := ben_dm_utility.number_of_threads(p_business_group_id);
86
87 -- assign the default to 3
88 if l_no_of_threads is null then
89 l_no_of_threads := 3 ;
90 end if ;
91 -- initialise the counter.
92 l_count := 1;
93 --
94 -- Get the table for which TUPS/TDS has to be generated
95 --
96 loop
97 l_phase_item_id := NULL;
98
99 --
100 -- get status of generate phase. If phase has error status set by other slave
101 -- process then we need to stop the processing of this slave.
102 -- if null returned, then assume it is not started.
103 --
104 l_current_phase_status := nvl(ben_dm_utility.get_phase_status('G',p_migration_id), 'NS');
105
106 -- if status is error, then raise an exception
107 if (l_current_phase_status = 'E') then
108 l_fatal_error_message := 'Encountered error in generator phase caused by ' ||
109 'another process - slave exiting';
110 raise e_fatal_error2;
111 end if;
112
113 open csr_get_table;
114 fetch csr_get_table into l_csr_get_table_rec;
115 if csr_get_table%notfound then
116 close csr_get_table;
117 exit;
118 end if;
119 close csr_get_table;
120
121 -- update status to started
122 ben_dm_utility.update_phase_items(p_new_status => 'S',
123 p_id => l_csr_get_table_rec.phase_item_id);
124
125 l_phase_item_id := l_csr_get_table_rec.phase_item_id;
126 l_phase_id := l_csr_get_table_rec.phase_id;
127
128
129 ben_dm_utility.message('INFO','Started Generating TUPS/TDS for ' ||
130 l_csr_get_table_rec.table_name || ', Table Id - ' ||
131 l_csr_get_table_rec.table_id,20);
132
133 ben_dm_utility.message('SUMM','Started Generating TUPS/TDS for ' ||
134 l_csr_get_table_rec.table_name || ', Table Id - ' ||
135 l_csr_get_table_rec.table_id,30);
136 --
137 -- Call TUPS genrator to create TUPS for the table
138 --
139 hr_utility.set_location('Calling Upload for :'||l_csr_get_table_rec.table_alias , 40);
140 ben_dm_utility.message('INFO',' Started Generating TUPS for ' ||
141 l_csr_get_table_rec.table_name,50);
142
143 BEN_DM_GEN_UPLOAD.main (
144 p_table_alias => l_csr_get_table_rec.table_alias ,
145 p_migration_id => p_migration_id
146 );
147
148
149 ben_dm_utility.message('INFO',' Successfully Generated TUPS for ' ||
150 l_csr_get_table_rec.table_name,60);
151 --
152 --
153 -- Call TDS generator to create TDS for the table
154 --
155 ben_dm_utility.message('INFO',' Started Generating TDS for ' ||
156 l_csr_get_table_rec.table_name,70);
157
158 hr_utility.set_location('Calling Download for :'||l_csr_get_table_rec.table_alias , 10);
159
160 BEN_DM_GEN_DOWNLOAD.main (
161 p_table_alias => l_csr_get_table_rec.table_alias,
162 p_migration_id => p_migration_id
163 ) ;
164
165 l_count := l_count + 1;
166
167 -- get generator version used to generated this TUPS/TDS
168 hr_dm_library.get_generator_version(l_generator_version);
169 --
170 -- update the last generated date for TUP/TDS for this table in hr_dm_tables
171 --
172 update ben_dm_tables
173 set last_generated_date = sysdate,
174 generator_version = l_generator_version
175 where table_id = l_csr_get_table_rec.table_id;
176
177 -- update status to completed
178 ben_dm_utility.update_phase_items(p_new_status => 'C',
179 p_id => l_phase_item_id);
180
181 ben_dm_utility.message('INFO','Generated TUPS/TDS succesfully for ' ||
182 l_csr_get_table_rec.table_name || ', Table Id - ' ||
183 l_csr_get_table_rec.table_id,110);
184
185 ben_dm_utility.message('SUMM','Generated TUPS/TDS successfully for ' ||
186 l_csr_get_table_rec.table_name || ', Table Id - ' ||
187 l_csr_get_table_rec.table_id,120);
188
189 end loop;
190 /*
191 -- when the process number is one call for generating self reference
192 -- package
193 if p_process_number = 1 then
194
195 ben_dm_utility.message('INFO',' Started Generating ben_dm_resolve_reference ' ,130);
196 BEN_DM_GEN_SELF_REF.main( p_business_group_id => p_business_group_id ,
197 p_migration_id => p_migration_id
198 ) ;
199 ben_dm_utility.message('INFO',' Generated ben_dm_resolve_reference ' ,140);
200 end if ;
201 */
202
203 -- set up return values to concurrent manager
204 retcode := 0;
205 errbuf := 'No errors - examine logfiles for detailed reports.';
206
207 ben_dm_utility.message('ROUT','exit:' || l_proc , 150);
208 hr_utility.set_location('Leaving:'||l_proc, 10);
209 -- error handling
210 exception
211 when e_fatal_error then
212 if csr_get_table%isopen then
213 close csr_get_table;
214 end if;
215 retcode := 2;
216 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
217 ben_dm_utility.error(SQLCODE,l_proc , l_fatal_error_message,'R');
218
219 -- if the error is caused because the other process has set the generator phase to 'Error'
220 -- then the phase_item_id is 'NULL' , otherwise, the error is caused within this process
221 -- while generating TUPS/TDS.
222
223 if l_phase_item_id is not null then
224 ben_dm_utility.update_phase_items(p_new_status => 'E',
225 p_id => l_phase_item_id);
226 else
227 ben_dm_utility.update_phases(p_new_status => 'E',
228 p_id => l_phase_id);
229 end if;
230
231 ben_dm_utility.error(SQLCODE,l_proc,'(none)','R');
232 when e_fatal_error2 then
233 if csr_get_table%isopen then
234 close csr_get_table;
235 end if;
236 retcode := 0;
237 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
238 ben_dm_utility.error(SQLCODE,l_proc , l_fatal_error_message,'R');
239
240 -- if the error is caused because the other process has set the generator phase to 'Error'
241 -- then the phase_item_id is 'NULL' , otherwise, the error is caused within this process
242 -- while generating TUPS/TDS.
243
244 if l_phase_item_id is not null then
245 ben_dm_utility.update_phase_items(p_new_status => 'E',
246 p_id => l_phase_item_id);
247 else
248 ben_dm_utility.update_phases(p_new_status => 'E',
249 p_id => l_phase_id);
250 end if;
251
252 ben_dm_utility.error(SQLCODE,l_proc,'(none)','R');
253 when others then
254 if csr_get_table%isopen then
255 close csr_get_table;
256 end if;
257 retcode := 2;
258 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
259 -- update status to error
260 ben_dm_utility.update_phase_items(p_new_status => 'E',
261 p_id => l_phase_item_id);
262 ben_dm_utility.error(SQLCODE,l_proc,'(none)','R');
263
264
265 end main_generator ;
266
267
268 Procedure download
269 (
270 errbuf out nocopy varchar2,
271 retcode out nocopy number ,
272 p_migration_id in number ,
273 p_concurrent_process in varchar2 default 'Y',
274 p_last_migration_date in varchar2,
275 p_process_number in number,
276 p_dir_name in varchar2,
277 p_file_name in varchar2,
278 p_delimiter in varchar2 default fnd_global.local_chr(01),
279 p_business_group_id in number default null
280 ) is
281
282
283 l_proc varchar2(75) ;
284 l_no_of_threads number;
285
286 -- cursor to get table for which
287
288 cursor csr_get_table is
289 select tbl.short_name
290 from ben_dm_tables tbl,
291 ben_dm_table_order dto
292 where tbl.table_id = dto.table_id
293 order by dto.table_order ;
294
295
296 cursor c_input_file(p_input_file_id number) is
297 select dif.input_file_id
298 ,dif.source_business_group_name
299 ,dif.source_person_id
300 ,dif.source_national_identifier
301 ,dif.target_business_group_name
302 ,dif.group_order
303 from ben_dm_input_file dif
304 where dif.input_file_id= p_input_file_id ;
305
306 cursor c_input_file2 is
307 select itm.phase_item_id
308 ,itm.input_file_id
309 from ben_dm_phase_items itm,
310 ben_dm_phases phs
311 where phs.migration_id = p_migration_id
312 and phs.phase_name = 'DP'
313 and phs.phase_id = itm.phase_id
314 and mod(itm.phase_item_id,l_no_of_threads) + 1 = p_process_number
315 and itm.status = 'NS' ;
316
317 cursor c_bg (l_name varchar2) is
318 select business_group_id
319 from per_business_groups
320 where name = l_name ;
321
322 l_csr_get_table_rec csr_get_table%rowtype;
323 t_phase_item_id numTab;
327 l_fatal_error_message varchar2(200);
324 t_input_file_id numTab;
325 l_input_file_rec c_input_file%rowtype;
326 l_last_migration_date date ;
328 l_max_ext number := 32767;
329 l_pre_fix varchar2(10) ;
330 l_short_name ben_dm_tables.short_name%type ;
331 l_business_group_id number ;
332 l_phase_item_id number ;
333 l_phase_id number ;
334 l_current_phase_status varchar2(10) ;
335 lstring varchar2(4000) ;
336 l_rec_downloaded number ;
337 e_fatal_error exception;
338 e_fatal_error2 exception;
339
340 begin
341 l_proc := g_package || 'download' ;
342 hr_utility.set_location('Entering:'||l_proc, 10);
343 hr_general.g_data_migrator_mode := 'Y';
344 l_pre_fix := 'BEN_DMD' ;
345 l_last_migration_date := to_date(p_last_migration_date, 'YYYY/MM/DD HH24:MI:SS');
346 -- initialize messaging
347 if p_concurrent_process = 'Y' then
348 ben_dm_utility.message_init;
349 end if;
350
351 --open file handler
352 g_file_handle := utl_file.fopen(p_dir_name,p_file_name||'.'||p_process_number,'w',l_max_ext);
353
354 ben_dm_utility.message('ROUT','entry:'||l_proc , 5);
355 ben_dm_utility.message('PARA','(errbuf - ' || errbuf ||
356 ')(retcode - ' || retcode ||
357 ')(p_migration_id - ' || p_migration_id ||
358 ')(p_concurrent_process - ' || p_concurrent_process ||
359 ')(p_last_migration_date - '|| l_last_migration_date ||
360 ')(p_process_number - ' || p_process_number ||
361 ')(p_business_group_id - '|| p_business_group_id ||
362 ')', 10);
363
364 l_no_of_threads := ben_dm_utility.number_of_threads(p_business_group_id);
365 -- assign the default to 3
366 if l_no_of_threads is null then
367 l_no_of_threads := 3 ;
368 end if ;
369 --
370 open c_input_file2;
371 fetch c_input_file2 bulk collect into t_phase_item_id,t_input_file_id;
372 close c_input_file2;
373
374 open csr_get_table;
375 fetch csr_get_table bulk collect into t_tab_short_name;
376 close csr_get_table;
377
378 for i in 1..t_phase_item_id.count
379 loop
380 l_phase_item_id := t_phase_item_id(i);
381
382 --
383 -- get status of generate phase. If phase has error status set by other slave
384 -- process then we need to stop the processing of this slave.
385 -- if null returned, then assume it is not started.
386 --
387 l_current_phase_status := nvl(ben_dm_utility.get_phase_status('DP',p_migration_id), 'NS');
388
389 -- if status is error, then raise an exception
390 if (l_current_phase_status = 'E') then
391 l_fatal_error_message := 'Encountered error in download phase caused by ' ||
392 'another process - slave exiting';
393 raise e_fatal_error2;
394 end if;
395
396
397 open c_input_file(t_input_file_id(i));
398 fetch c_input_file into l_input_file_rec;
399 close c_input_file;
400
401 l_business_group_id := null;
402
403 open c_bg(l_input_file_rec.source_business_group_name) ;
404 fetch c_bg into l_business_group_id ;
405 close c_bg ;
406
407 ben_dm_utility.message('INFO','Source Business Group ID :'||l_business_group_id , 30);
408
409 if l_business_group_id is null then
410 l_fatal_error_message := 'Encountered error in download Source Business Group ' || l_input_file_rec.SOURCE_BUSINESS_GROUP_NAME || ' not found ! ';
411 raise e_fatal_error2;
412 end if ;
413
414 -- update status to started
415 ben_dm_utility.update_phase_items(p_new_status => 'S',
416 p_id => l_phase_item_id);
417
418
419 ben_dm_utility.message('INFO','Started download data for ' ||
420 l_input_file_rec.source_person_id || ', SSN - ' ||
421 l_input_file_rec.SOURCE_NATIONAL_IDENTIFIER,40);
422
423 ben_dm_utility.message('SUMM','Started download data for ' ||
424 l_input_file_rec.source_person_id || ', SSN - ' ||
425 l_input_file_rec.SOURCE_NATIONAL_IDENTIFIER,50);
426
427
428 -- for every person loop through tables
429 for i in 1..t_tab_short_name.count
430 Loop
431
432 lstring := 'Begin ' || l_pre_fix||t_tab_short_name(i)||'.DOWNLOAD(' ;
433 lstring := lstring || 'p_migration_id => :1 ' ;
434 lstring := lstring || ',p_business_group_id => :2 ' ;
435 lstring := lstring || ',p_business_group_name => :3 ' ;
436 lstring := lstring || ',p_person_id => :4 ' ;
437 lstring := lstring || ',p_group_order => :5 ' ;
438 lstring := lstring || ',p_rec_downloaded => :6 ) ; END ; ' ;
439
440 ben_dm_utility.message('INFO','calling download procedure :'||l_pre_fix||t_tab_short_name(i)||'.DOWNALOAD' , 5);
441
442 begin
443 execute immediate lstring using
444 p_migration_id,
445 l_business_group_id ,
446 l_input_file_rec.target_business_group_name,
447 l_input_file_rec.source_person_id,
448 l_input_file_rec.group_order ,
449 OUT l_rec_downloaded ;
450 exception
451 when others then
452 ben_dm_utility.message('INFO','calling download procedure SQL error ' , 60);
453 ben_dm_utility.message('INFO', substr(sqlerrm,1,150) , 60);
454 l_fatal_error_message := 'Encountered error in download phase caused by ' || t_tab_short_name(i) ;
458 ben_dm_utility.message('INFO','download completed for :'||t_tab_short_name(i) , 70);
455 raise e_fatal_error2;
456
457 end;
459
460 end loop ;
461
462 ben_dm_utility.update_phase_items(p_new_status => 'C',
463 p_id => l_phase_item_id);
464
465 ben_dm_utility.message('INFO','Downloaded succesfully for ' ||
466 l_input_file_rec.source_person_id || ', SSN - ' ||
467 l_input_file_rec.SOURCE_NATIONAL_IDENTIFIER,80);
468
469
470 ben_dm_utility.message('SUMM','Downloaded succesfully for ' ||
471 l_input_file_rec.source_person_id || ', SSN - ' ||
472 l_input_file_rec.SOURCE_NATIONAL_IDENTIFIER,90);
473
474 End Loop ;
475 hr_general.g_data_migrator_mode := 'N';
476 hr_utility.set_location('Leaving:'||l_proc, 10);
477 ben_dm_utility.message('ROUT','EXIT ' || l_proc ,100);
478 Exception
479
480 when e_fatal_error2 then
481 hr_general.g_data_migrator_mode := 'N';
482 retcode := 0;
483 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
484 ben_dm_utility.error(SQLCODE,l_proc , l_fatal_error_message,'R');
485
486 if l_phase_item_id is not null then
487 ben_dm_utility.update_phase_items(p_new_status => 'E',
488 p_id => l_phase_item_id);
489 end if;
490
491 ben_dm_utility.error(SQLCODE,l_proc,'(none)','R');
492 when others then
493 hr_general.g_data_migrator_mode := 'N';
494 retcode := 2;
495 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
496 -- update status to error
497 ben_dm_utility.update_phase_items(p_new_status => 'E',
498 p_id => l_phase_item_id);
499 ben_dm_utility.error(SQLCODE,l_proc,'(none)','R');
500
501 end download ;
502
503 Procedure upload
504 (
505 errbuf out nocopy varchar2,
506 retcode out nocopy number ,
507 p_migration_id in number ,
508 p_concurrent_process in varchar2 default 'Y',
509 p_last_migration_date in varchar2,
510 p_process_number in number,
511 p_dir_name in varchar2,
512 p_file_name in varchar2,
513 p_delimiter in varchar2 default fnd_global.local_chr(01),
514 p_business_group_id in number default null
515 ) is
516
517
518 l_proc varchar2(75) ;
519 l_no_of_threads number;
520
521 -- cursor to get table for which
522
523 cursor csr_get_table is
524 select tbl.short_name
525 from ben_dm_tables tbl,
526 ben_dm_table_order dto
527 where tbl.table_id = dto.table_id
528 order by dto.table_order ;
529
530 cursor c_phase is
531 select itm.phase_item_id
532 ,itm.phase_id
533 ,itm.group_order
534 from ben_dm_phase_items itm,
535 ben_dm_phases phs
536 where phs.migration_id = p_migration_id
537 and phs.phase_id = itm.phase_id
538 and phs.phase_name = 'UP'
539 and mod(itm.phase_item_id,l_no_of_threads) + 1 = p_process_number
540 and itm.status = 'NS';
541
542 cursor c_input_file (l_group_order number) is
543 select dif.target_business_group_name
544 ,dif.group_order
545 from ben_dm_input_file dif
546 where dif.group_order = l_group_order
547 and dif. status = 'NS'
548 order by dif.group_order;
549
550
551 cursor c_bg (l_name varchar2) is
552 select business_group_id
553 from per_business_groups
554 where name = l_name ;
555
556 l_csr_get_table_rec csr_get_table%rowtype;
557 l_input_file_rec c_input_file%rowtype ;
558 l_phase_rec c_phase%rowtype;
559 l_last_migration_date date ;
560 l_fatal_error_message varchar2(200);
561 l_pre_fix varchar2(10) ;
562 l_short_name ben_dm_tables.short_name%type ;
563 l_business_group_id number ;
564 l_phase_item_id number ;
565 l_phase_id number ;
566 l_current_phase_status varchar2(10) ;
567 lstring varchar2(4000) ;
568 l_Count number ;
569 l_rec_downloaded number ;
570 l_max_ext number := 32767;
571 e_fatal_error exception;
572 e_fatal_error2 exception;
573
574 begin
575 l_proc := g_package || 'upload' ;
576 hr_utility.set_location('Entering:'||l_proc, 10);
577 hr_general.g_data_migrator_mode := 'Y';
578 l_pre_fix := 'BEN_DMU' ;
579 l_last_migration_date := to_date(p_last_migration_date, 'YYYY/MM/DD HH24:MI:SS');
580 -- initialize messaging
581 if p_concurrent_process = 'Y' then
582 ben_dm_utility.message_init;
583 end if;
584
585 ben_dm_utility.message('ROUT','entry:'||l_proc , 5);
586 ben_dm_utility.message('PARA','(errbuf - ' || errbuf ||
587 ')(retcode - ' || retcode ||
588 ')(p_migration_id - ' || p_migration_id ||
589 ')(p_concurrent_process - ' || p_concurrent_process ||
590 ')(p_last_migration_date - '|| l_last_migration_date ||
591 ')', 10);
592
593 l_no_of_threads := ben_dm_utility.number_of_threads(p_business_group_id);
594 -- assign the default to 3
595 if l_no_of_threads is null then
596 l_no_of_threads := 3 ;
597 end if ;
598 -- initialise the counter.
599 l_count := 1;
600 -- build the cache from dm mapping table
601
602 open csr_get_table;
606 ben_dm_data_util.create_fk_cache ;
603 fetch csr_get_table bulk collect into t_tab_short_name;
604 close csr_get_table;
605
607
608 ben_dm_utility.g_out_file_handle := utl_file.fopen(p_dir_name,p_file_name||'.out'||p_process_number,'w',l_max_ext);
609 ---
610 loop
611 l_phase_item_id := NULL;
612
613 --
614 -- get status of generate phase. If phase has error status set by other slave
615 -- process then we need to stop the processing of this slave.
616 -- if null returned, then assume it is not started.
617 --
618 l_current_phase_status := nvl(ben_dm_utility.get_phase_status('UP',p_migration_id), 'NS');
619 ben_dm_utility.message('INFO',' current phase status ' || l_current_phase_status,70);
620
621 -- if status is error, then raise an exception
622 if (l_current_phase_status = 'E') then
623 l_fatal_error_message := 'Encountered error in download phase caused by ' ||
624 'another process - slave exiting';
625 raise e_fatal_error2;
626 end if;
627
628 open c_phase;
629 fetch c_phase into l_phase_rec ;
630 if c_phase%notfound then
631 close c_phase;
632 ben_dm_utility.message('INFO','exit without phase data :'||l_proc , 20);
633 exit;
634 end if;
635 close c_phase;
636
637
638 ben_dm_utility.message('INFO',' group order ' || l_phase_rec.group_order,70);
639
640 -- update status to started
641 ben_dm_utility.update_phase_items(p_new_status => 'S',
642 p_id => l_phase_rec.phase_item_id);
643
644 l_phase_item_id := l_phase_rec.phase_item_id;
645 l_phase_id := l_phase_rec.phase_id;
646 l_business_group_id := null ;
647 l_input_file_rec := null ;
648
649 open c_input_file(l_phase_rec.group_order) ;
650 fetch c_input_file into l_input_file_rec ;
651 close c_input_file ;
652 --
653 -- Assume different group order will be generated from different target BG
654 -- if not use loop for distinct target bg for same group order
655 --
656 if l_input_file_rec.group_order is not null then
657 -- group order for for every target bg is unique
658 open c_bg(l_input_file_rec.target_business_group_name) ;
659 fetch c_bg into l_business_group_id ;
660 close c_bg ;
661 ben_dm_utility.message('INFO','Business Group ID :'||l_business_group_id , 30);
662 if l_business_group_id is null then
663 l_fatal_error_message := 'Encountered error in upload target Business Group '
664 ||l_input_file_rec.TARGET_BUSINESS_GROUP_NAME || ' not found ! ';
665 raise e_fatal_error2;
666 end if ;
667
668
669 hr_utility.set_location(' starting ' || l_input_file_rec.group_order , 99 ) ;
670
671 ben_dm_utility.message('INFO','Started upload data for ' ||
672 l_input_file_rec.group_order || ', Group - BG ' ||
673 l_input_file_rec.target_business_group_name,40);
674
675 ben_dm_utility.message('SUMM','Started upload data for ' ||
676 l_input_file_rec.group_order || ', SSN - BG ' ||
677 l_input_file_rec.target_business_group_name,50);
678
679
680 -- for every table for the group order loop through tables
681 for i in 1..t_tab_short_name.count
682 Loop
683
684 hr_utility.set_location(' building sql for ' || t_tab_short_name(i), 99 ) ;
685 ben_dm_utility.message('INFO',' Building SQL for ' || t_tab_short_name(i),70);
686
687 lstring := 'Begin ' || l_pre_fix||t_tab_short_name(i)||'.UPLOAD(' ;
688 lstring := lstring || 'p_migration_id => :1 ' ;
689 lstring := lstring || ',p_business_group_id => :2 ' ;
690 lstring := lstring || ',p_business_group_name => :3 ' ;
691 lstring := lstring || ',p_group_order => :4 ' ;
692 lstring := lstring || ',p_delimiter => :5 ' ;
693 lstring := lstring || ' ) ; END ; ' ;
694
695
696 hr_utility.set_location(' executing sql for ' || t_tab_short_name(i), 99 ) ;
697 ben_dm_utility.message('INFO',' Executing ' || t_tab_short_name(i),70);
698 begin
699 execute immediate lstring using
700 p_migration_id ,
701 l_business_group_id,
702 l_input_file_rec.target_business_group_name,
703 l_input_file_rec.group_order,
704 p_delimiter ;
705 exception
706 when others then
707 ben_dm_utility.message('INFO','calling upload procedure SQL error ' , 60);
708 ben_dm_utility.message('INFO', substr(sqlerrm,1,150) , 60);
709 l_fatal_error_message := 'Encountered error in upload phase caused by ' ||
710 t_tab_short_name(i) ;
711 raise e_fatal_error2;
712
713 end;
714
715 hr_utility.set_location(' upload completed for ' || t_tab_short_name(i), 99 ) ;
716 ben_dm_utility.message('INFO',' Upload Completed for ' || t_tab_short_name(i),70);
717
718 end loop ;
719
720
721 /*
722
723 -- call this for every order once , if the order changed to loop , move below the loop
724 hr_utility.set_location(' upload completed for ' || l_input_file_rec.group_order , 99 ) ;
725 ben_dm_utility.message('INFO','Call for Self reference :'||l_phase_rec.group_order , 60);
726
727 lstring := 'Begin ben_dm_resolve_reference.main( ' ;
728 lstring := lstring || 'p_migration_id => :1 ' ;
729 lstring := lstring || ',p_business_group_name => :2 ' ;
730 lstring := lstring || ',p_group_order => :3 ' ;
731 lstring := lstring || ' ) ; END ; ' ;
732
733 begin
734 execute immediate lstring using p_migration_id ,
735 l_business_group_id,
736 l_input_file_rec.group_order ;
737 exception
738 when others then
739 ben_dm_utility.message('INFO','calling procedure ben_dm_resolve_reference error ' , 60);
740 ben_dm_utility.message('INFO', substr(sqlerrm,1,150) , 60);
741 l_fatal_error_message := 'Encountered error caused by ben_dm_resolve_reference error ' ;
742 raise e_fatal_error2;
743
744 end;
745 */
746 End if ;
747
748
749 ben_dm_utility.update_phase_items(p_new_status => 'C',
750 p_id => l_phase_item_id);
751
752 -- clear the cache for every group order
753 ben_dm_data_util.g_pk_maping_tbl.delete ;
754
755
756 hr_utility.set_location(' download completed for ' || l_phase_rec.group_order , 99 ) ;
757
758 ben_dm_utility.message('INFO','Uploded succesfully for ' || l_phase_rec.group_order,70);
759
760
761
762 ben_dm_utility.message('SUMM','Uploded succesfully for ' ||
763 l_phase_rec.group_order,80);
764
765
766 End Loop ;
767
768 -- clear the fk cache
769 ben_dm_data_util.g_fk_maping_tbl.delete ;
770
771 ben_dm_utility.message('ROUT','EXIT ' || l_proc,100);
772 hr_general.g_data_migrator_mode := 'N';
773 hr_utility.set_location('Leaving:'||l_proc, 10);
774 Exception
775 when e_fatal_error2 then
776 hr_general.g_data_migrator_mode := 'N';
777 retcode := 0;
778 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
779 ben_dm_utility.error(SQLCODE,l_proc , l_fatal_error_message,'R');
780
781 if l_phase_item_id is not null then
782 ben_dm_utility.update_phase_items(p_new_status => 'E',
783 p_id => l_phase_item_id);
784 else
785 ben_dm_utility.update_phases(p_new_status => 'E',
786 p_id => l_phase_id);
787 end if;
788
789 ben_dm_utility.error(SQLCODE,l_proc,'(none)','R');
790 when others then
791 hr_general.g_data_migrator_mode := 'N';
792 retcode := 2;
793 errbuf := 'An error occurred during the migration - examine logfiles for detailed reports.';
794 -- update status to error
795 ben_dm_utility.update_phase_items(p_new_status => 'E',
796 p_id => l_phase_item_id);
797 ben_dm_utility.error(SQLCODE,l_proc,'(none)','R');
798
799 end upload ;
800
801
802
803 end ben_dm_gen_master;