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.BEN_DM_CREATE_CONTROL_FILES

Source


1 PACKAGE BODY  ben_dm_create_control_files  AS
2 /* $Header: benfdmmctl.pkb 120.1 2006/05/04 07:02:53 nkkrishn noship $ */
3 
4 procedure get_no_of_inp_files
5 (p_dir_name             in   varchar2,
6  p_data_file            in   varchar2,
7  p_no_of_files          out  nocopy number) is
8 
9 l_file_exists    boolean;
10 l_dummy          number;
11 l_file_count     number := 0;
12 
13 begin
14 
15 --
16 -- assuming a max of 10 threads
17 --
18 for i in 1..10
19 loop
20     l_file_exists := false;
21     utl_file.fgetattr(p_dir_name,p_data_file||'.'||i,l_file_exists,l_dummy,l_dummy);
22     if not l_file_exists then
23        exit;
24     elsif l_file_exists then
25        l_file_count := l_file_count + 1;
26     end if;
27 end loop;
28 
29 p_no_of_files := l_file_count;
30 
31 end get_no_of_inp_files;
32 
33 
34 procedure touch_files
35 (
36  p_dir_name             in   varchar2,
37  p_no_of_threads        in   number,
38  p_data_file            in   varchar2,
39  p_file_type            in   varchar2 default 'out')
40 is
41 
42 l_file_exists    boolean;
43 l_dummy          number;
44 l_max_size    number := 32767;
45 l_file_handle utl_file.file_type;
46 
47 begin
48 
49 if p_file_type = 'in' then
50    --assume a max of 10 threads
51    for i in 1..10
52    loop
53        l_file_exists := false;
54        utl_file.fgetattr(p_dir_name,p_data_file||'.'||i,l_file_exists,l_dummy,l_dummy);
55        if not l_file_exists then
56           null;
57        elsif l_file_exists then
58           l_file_handle := utl_file.fopen(p_dir_name,p_data_file||'.'||i,'w',l_max_size);
59           utl_file.fclose(l_file_handle);
60        end if;
61    end loop;
62 else
63   for i in 1..p_no_of_threads
64   loop
65       l_file_handle := utl_file.fopen(p_dir_name,p_data_file||'.out'||i,'w',l_max_size);
66       utl_file.fclose(l_file_handle);
67   end loop;
68 end if;
69 
70 exception
71    when others then
72         if utl_file.is_open(l_file_handle) then
73             utl_file.fclose(l_file_handle);
74         end if;
75 
76 end touch_files;
77 
78 procedure main
79 (
80  p_dir_name             in   varchar2,
81  p_no_of_threads        in   number,
82  p_transfer_file        in   varchar2 default null,
83  p_data_file             in   varchar2 default null
84 ) is
85 
86 cursor c1 is
87 select tab.table_name,
88        tab.column_name
89   from sys.all_tab_columns tab
90  where tab.table_name in ('BEN_DM_INPUT_FILE','BEN_DM_RESOLVE_MAPPINGS')
91    and tab.column_name not in
92       ('LAST_UPDATE_DATE',
93        'LAST_UPDATED_BY',
94        'LAST_UPDATE_LOGIN',
95        'CREATED_BY',
96        'CREATION_DATE')
97 order by tab.table_name,tab.column_id;
98 c1_rec c1%rowtype;
99 
100 cursor c2 is
101 select tab.table_name,
102        tab.column_name
103   from sys.all_tab_columns tab
104  where tab.table_name in ('BEN_DM_ENTITY_RESULTS')
105    and tab.column_name not in
106       ('LAST_UPDATE_DATE',
107        'LAST_UPDATED_BY',
108        'LAST_UPDATE_LOGIN',
109        'CREATED_BY',
110        'CREATION_DATE')
111 order by tab.column_id;
112 c2_rec c2%rowtype;
113 
114 cursor c3 is
115 select tab.table_name,
116        tab.column_name
117   from ben_dm_tables bdt,
118        ben_dm_column_mappings map,
119        sys.all_tab_columns tab
120  where tab.table_name in ('PER_ALL_PEOPLE_F')
121    and bdt.table_name = tab.table_name
122    and map.table_id = bdt.table_id
123    and map.column_name = tab.column_name
124 order by tab.column_id;
125 c3_rec c3%rowtype;
126 
127 cursor c4 is
128 select tab.table_name,
129        tab.column_name
130   from ben_dm_tables bdt,
131        ben_dm_column_mappings map,
132        sys.all_tab_columns tab
133  where tab.table_name in
134 ('PER_CONTACT_RELATIONSHIPS',
135  'PER_ALL_ASSIGNMENTS_F',
136  'BEN_PTNL_LER_FOR_PER',
137  'BEN_PER_IN_LER',
138  'BEN_PIL_ELCTBL_CHC_POPL')
139    and bdt.table_name = tab.table_name
140    and map.table_id = bdt.table_id
141    and map.column_name = tab.column_name
142 order by tab.table_name,tab.column_id;
143 c4_rec c4%rowtype;
144 
145 cursor c5 is
146 select tab.table_name,
147        tab.column_name
148   from ben_dm_tables bdt,
149        ben_dm_column_mappings map,
150        sys.all_tab_columns tab
151  where tab.table_name in
152 ('PER_ADDRESSES',
153  'PER_PERIODS_OF_SERVICE',
154  'PER_PERSON_TYPE_USAGES_F',
155  'PER_ASSIGNMENT_EXTRA_INFO',
156  'PAY_ELEMENT_ENTRIES_F',
157  'PAY_ELEMENT_ENTRY_VALUES_F',
158  'PAY_RUN_RESULTS',
159  'PAY_RUN_RESULT_VALUES',
160  'BEN_CBR_QUALD_BNF',
161  'BEN_CBR_PER_IN_LER',
162  'BEN_PER_CM_F',
163  'BEN_PER_CM_PRVDD_F',
164  'BEN_PER_CM_TRGR_F',
165  'BEN_PER_CM_USG_F',
166  'BEN_PER_BNFTS_BAL_F',
167  'BEN_PRTT_ENRT_RSLT_F',
168  'BEN_PRTT_PREM_F',
169  'BEN_PRTT_PREM_BY_MO_F',
170  'BEN_PRTT_RT_VAL',
171  'BEN_ELIG_CVRD_DPNT_F',
172  'BEN_ELIG_DPNT',
173  'BEN_PRTT_ENRT_ACTN_F',
174  'BEN_PRTT_ENRT_CTFN_PRVDD_F',
175  'BEN_ENRT_BNFT',
176  'BEN_ENRT_PREM',
177  'BEN_ENRT_RT',
178  'BEN_ELCTBL_CHC_CTFN',
179  'BEN_LE_CLSN_N_RSTR',
180  'BEN_PRMRY_CARE_PRVDR_F',
181  'PER_ABSENCE_ATTENDANCES')
182    and bdt.table_name = tab.table_name
183    and map.table_id = bdt.table_id
184    and map.column_name = tab.column_name
185 order by tab.table_name,tab.column_id;
186 c5_rec c5%rowtype;
187 
188 cursor c6 is
189 select tab.table_name,
190        tab.column_name
191   from ben_dm_tables bdt,
192        ben_dm_column_mappings map,
193        sys.all_tab_columns tab
194  where tab.table_name in
195 ('BEN_ELIG_PER_F',
196  'BEN_ELIG_PER_OPT_F')
197    and bdt.table_name = tab.table_name
198    and map.table_id = bdt.table_id
199    and map.column_name = tab.column_name
200 order by tab.table_name,tab.column_id;
201 c6_rec c6%rowtype;
202 
203 cursor c7 is
204 select tab.table_name,
205        tab.column_name
206   from ben_dm_tables bdt,
207        ben_dm_column_mappings map,
208        sys.all_tab_columns tab
209  where tab.table_name in
210 ('BEN_ELIG_PER_ELCTBL_CHC')
211    and bdt.table_name = tab.table_name
212    and map.table_id = bdt.table_id
213    and map.column_name = tab.column_name
214 order by tab.table_name,tab.column_id;
215 c7_rec c7%rowtype;
216 
217 type t_in_file     is table of varchar2(255) index by binary_integer;
218 l_in_file        t_in_file;
219 l_prev_tab_name  varchar2(30);
220 l_file_handle utl_file.file_type;
221 l_max_size    number := 32767;
222 l_file_exists boolean;
223 l_dummy       number;
224 
225 begin
226 
227 ben_dm_utility.message('ROUT','entry:ben_dm_create_control_files.main', 5);
228 
229   touch_files
230   (p_dir_name             => p_dir_name,
231    p_no_of_threads        => p_no_of_threads,
232    p_data_file            => p_data_file);
233 
234 l_file_handle := utl_file.fopen(p_dir_name,'pm00.ctl','w',l_max_size);
235 
236 open c1;
237 loop
238    fetch c1 into c1_rec;
239    if c1%notfound then
240       exit;
241    end if;
242 
243    if c1%rowcount = 1 then
244       utl_file.put_line(l_file_handle,'load data');
245       utl_file.put_line(l_file_handle,'infile "'||p_dir_name||'/'||p_transfer_file||'"');
246       utl_file.put_line(l_file_handle,'replace');
247    end if;
248 
249    if c1_rec.table_name <> nvl(l_prev_tab_name,'-1') then
250       if l_prev_tab_name is not null then
251          utl_file.put_line(l_file_handle,')');
252       end if;
253       utl_file.put_line(l_file_handle,'into table '||c1_rec.table_name);
254       utl_file.put_line(l_file_handle,'when fillertabname="'||c1_rec.table_name||'"');
255       utl_file.put_line(l_file_handle,'fields terminated by '||''''||fnd_global.local_chr(01)||'''');
256       utl_file.put_line(l_file_handle,'TRAILING NULLCOLS');
257       utl_file.put_line(l_file_handle,'(fillertabname    FILLER POSITION(1) CHAR');
258       l_prev_tab_name := c1_rec.table_name;
259    end if;
260    utl_file.put_line(l_file_handle,','||c1_rec.column_name);
261 
262 
263 end loop;
264 close c1;
265 utl_file.put_line(l_file_handle,')');
266 utl_file.fclose(l_file_handle);
267 
268 l_file_handle := utl_file.fopen(p_dir_name,'pm01.ctl','w',l_max_size);
269 
270 --
271 -- assuming a max of 10 threads
272 --
273 for i in 1..10
274 loop
275     l_file_exists := false;
276     utl_file.fgetattr(p_dir_name,p_data_file||'.'||i,l_file_exists,l_dummy,l_dummy);
277     if not l_file_exists then
278        exit;
279     elsif l_file_exists then
280        l_in_file(i) := 'infile "'||p_dir_name||'/'||p_data_file||'.'||i||'"';
281     end if;
282 end loop;
283 
284 if l_in_file.count = 0 then
285    return;
286 end if;
287 
288 open c2;
289 loop
290    fetch c2 into c2_rec;
291    if c2%notfound then
292       exit;
293    end if;
294 
295    if c2%rowcount = 1 then
296       utl_file.put_line(l_file_handle,'unrecoverable');
297       utl_file.put_line(l_file_handle,'load data');
298       if l_in_file is null then
299          exit;
300       end if;
301 
302       for i in 1..l_in_file.count
303       loop
304          utl_file.put_line(l_file_handle,l_in_file(i));
305       end loop;
306       utl_file.put_line(l_file_handle,'replace');
307       utl_file.put_line(l_file_handle,'into table '||c2_rec.table_name);
308       utl_file.put_line(l_file_handle,'fields terminated by '||''''||fnd_global.local_chr(01)||'''');
309       utl_file.put_line(l_file_handle,'TRAILING NULLCOLS');
310       utl_file.put_line(l_file_handle,'('||c2_rec.column_name);
311    else
312       utl_file.put_line(l_file_handle,','||c2_rec.column_name);
313    end if;
314 
315 
316 end loop;
317 close c2;
318 utl_file.put_line(l_file_handle,')');
319 utl_file.fclose(l_file_handle);
320 
321 l_file_handle := utl_file.fopen(p_dir_name,'pm02.ctl','w',l_max_size);
322 
323 l_in_file.delete;
324 
325 for i in 1..p_no_of_threads
326 loop
327     l_in_file(i) := 'infile "'||p_dir_name||'/'||p_data_file||'.out'||i||'"';
328 end loop;
329 
330 if l_in_file.count = 0 then
331    return;
332 end if;
333 
334 open c3;
335 loop
336    fetch c3 into c3_rec;
337    if c3%notfound then
338       exit;
339    end if;
340 
341    if c3%rowcount = 1 then
342       utl_file.put_line(l_file_handle,'load data');
343       if l_in_file is null then
344          exit;
345       end if;
346 
347       for i in 1..l_in_file.count
348       loop
349          utl_file.put_line(l_file_handle,l_in_file(i));
350       end loop;
351       utl_file.put_line(l_file_handle,'append');
352       utl_file.put_line(l_file_handle,'into table '||c3_rec.table_name);
353       utl_file.put_line(l_file_handle,'when fillertabname="'||c3_rec.table_name||'"');
354       utl_file.put_line(l_file_handle,'fields terminated by '||''''||fnd_global.local_chr(01)||'''');
355       utl_file.put_line(l_file_handle,'TRAILING NULLCOLS');
356       utl_file.put_line(l_file_handle,'(fillertabname    FILLER POSITION(1) CHAR');
357       utl_file.put_line(l_file_handle,','||c3_rec.column_name||'      "ben_dm_create_control_files.set_dm_flag(:'||c3_rec.column_name||')"');
358    else
359       utl_file.put_line(l_file_handle,','||c3_rec.column_name);
360    end if;
361 
362 
363 end loop;
364 close c3;
365 utl_file.put_line(l_file_handle,')');
366 utl_file.fclose(l_file_handle);
367 
368 l_file_handle := utl_file.fopen(p_dir_name,'pm03.ctl','w',l_max_size);
369 
370 open c4;
371 loop
372    fetch c4 into c4_rec;
373    if c4%notfound then
374       exit;
375    end if;
376 
377    if c4%rowcount = 1 then
378       utl_file.put_line(l_file_handle,'unrecoverable');
379       utl_file.put_line(l_file_handle,'load data');
380       for i in 1..l_in_file.count
381       loop
382          utl_file.put_line(l_file_handle,l_in_file(i));
383       end loop;
384       utl_file.put_line(l_file_handle,'append');
385       l_prev_tab_name := null;
386    end if;
387 
388    if c4_rec.table_name <> nvl(l_prev_tab_name,'-1') then
389       if l_prev_tab_name is not null then
390          utl_file.put_line(l_file_handle,')');
391       end if;
392       utl_file.put_line(l_file_handle,'into table '||c4_rec.table_name);
393       utl_file.put_line(l_file_handle,'reenable');
394       utl_file.put_line(l_file_handle,'when fillertabname="'||c4_rec.table_name||'"');
395       utl_file.put_line(l_file_handle,'fields terminated by '||''''||fnd_global.local_chr(01)||'''');
396       utl_file.put_line(l_file_handle,'TRAILING NULLCOLS');
397       utl_file.put_line(l_file_handle,'(fillertabname    FILLER POSITION(1) CHAR');
398       l_prev_tab_name := c4_rec.table_name;
399    end if;
400    utl_file.put_line(l_file_handle,','||c4_rec.column_name);
401 
402 
403 end loop;
404 close c4;
405 utl_file.put_line(l_file_handle,')');
406 utl_file.fclose(l_file_handle);
407 
408 l_file_handle := utl_file.fopen(p_dir_name,'pm04.ctl','w',l_max_size);
409 
410 l_in_file.delete;
411 for i in 1..p_no_of_threads
412 loop
413     l_in_file(i) := 'infile "'||p_dir_name||'/discardfile1'||i||'.dat"';
414 end loop;
415 
416 if l_in_file.count = 0 then
417    return;
418 end if;
419 
420 
421 open c5;
422 loop
423    fetch c5 into c5_rec;
424    if c5%notfound then
425       exit;
426    end if;
427 
428    if c5%rowcount = 1 then
429       utl_file.put_line(l_file_handle,'unrecoverable');
430       utl_file.put_line(l_file_handle,'load data');
431       for i in 1..l_in_file.count
432       loop
433          utl_file.put_line(l_file_handle,l_in_file(i));
434       end loop;
435       utl_file.put_line(l_file_handle,'append');
436       l_prev_tab_name := null;
437    end if;
438 
439    if c5_rec.table_name <> nvl(l_prev_tab_name,'-1') then
440       if l_prev_tab_name is not null then
441          utl_file.put_line(l_file_handle,')');
442          utl_file.put_line(l_file_handle,'');
443       end if;
444       utl_file.put_line(l_file_handle,'into table '||c5_rec.table_name);
445       utl_file.put_line(l_file_handle,'reenable');
446       utl_file.put_line(l_file_handle,'when fillertabname="'||c5_rec.table_name||'"');
447       utl_file.put_line(l_file_handle,'fields terminated by '||''''||fnd_global.local_chr(01)||'''');
448       utl_file.put_line(l_file_handle,'TRAILING NULLCOLS');
449       utl_file.put_line(l_file_handle,'(fillertabname    FILLER POSITION(1) CHAR');
450       l_prev_tab_name := c5_rec.table_name;
451    end if;
452    utl_file.put_line(l_file_handle,','||c5_rec.column_name);
453 
454 
455 end loop;
456 close c5;
457 utl_file.put_line(l_file_handle,')');
458 utl_file.fclose(l_file_handle);
459 
460 l_file_handle := utl_file.fopen(p_dir_name,'pm05.ctl','w',l_max_size);
461 
462 l_in_file.delete;
463 
464 for i in 1..p_no_of_threads
465 loop
466     l_in_file(i) := 'infile "'||p_dir_name||'/'||p_data_file||'.out'||i||'" discardfile "'||p_dir_name||'/discardfile0'||i||'.dat"';
467 end loop;
468 
469 if l_in_file.count = 0 then
470    return;
471 end if;
472 
473 open c6;
474 loop
475    fetch c6 into c6_rec;
476    if c6%notfound then
477       exit;
478    end if;
479 
480    if c6%rowcount = 1 then
481       utl_file.put_line(l_file_handle,'unrecoverable');
482       utl_file.put_line(l_file_handle,'load data');
483       for i in 1..l_in_file.count
484       loop
485          utl_file.put_line(l_file_handle,l_in_file(i));
486       end loop;
487       utl_file.put_line(l_file_handle,'append');
491    if c6_rec.table_name <> nvl(l_prev_tab_name,'-1') then
488       l_prev_tab_name := null;
489    end if;
490 
492       if l_prev_tab_name is not null then
493          utl_file.put_line(l_file_handle,')');
494          utl_file.put_line(l_file_handle,'');
495       end if;
496       utl_file.put_line(l_file_handle,'into table '||c6_rec.table_name);
497       utl_file.put_line(l_file_handle,'reenable');
498       utl_file.put_line(l_file_handle,'when fillertabname="'||c6_rec.table_name||'"');
499       utl_file.put_line(l_file_handle,'fields terminated by '||''''||fnd_global.local_chr(01)||'''');
500       utl_file.put_line(l_file_handle,'TRAILING NULLCOLS');
501       utl_file.put_line(l_file_handle,'(fillertabname    FILLER POSITION(1) CHAR');
502       l_prev_tab_name := c6_rec.table_name;
503    end if;
504    utl_file.put_line(l_file_handle,','||c6_rec.column_name);
505 
506 
507 end loop;
508 close c6;
509 utl_file.put_line(l_file_handle,')');
510 utl_file.fclose(l_file_handle);
511 
512 l_file_handle := utl_file.fopen(p_dir_name,'pm06.ctl','w',l_max_size);
513 
514 l_in_file.delete;
515 
516 for i in 1..p_no_of_threads
517 loop
518     l_in_file(i) := 'infile "'||p_dir_name||'/'||'discardfile0'||i||'.dat" discardfile "'||p_dir_name||'/discardfile1'||i||'.dat"';
519 end loop;
520 
521 if l_in_file.count = 0 then
522    return;
523 end if;
524 
525 open c7;
526 loop
527    fetch c7 into c7_rec;
528    if c7%notfound then
529       exit;
530    end if;
531 
532    if c7%rowcount = 1 then
533       utl_file.put_line(l_file_handle,'unrecoverable');
534       utl_file.put_line(l_file_handle,'load data');
535       for i in 1..l_in_file.count
536       loop
537          utl_file.put_line(l_file_handle,l_in_file(i));
538       end loop;
539       utl_file.put_line(l_file_handle,'append');
540       utl_file.put_line(l_file_handle,'into table '||c7_rec.table_name);
541       utl_file.put_line(l_file_handle,'reenable');
542       utl_file.put_line(l_file_handle,'when fillertabname="'||c7_rec.table_name||'"');
543       utl_file.put_line(l_file_handle,'fields terminated by '||''''||fnd_global.local_chr(01)||'''');
544       utl_file.put_line(l_file_handle,'TRAILING NULLCOLS');
545       utl_file.put_line(l_file_handle,'(fillertabname    FILLER POSITION(1) CHAR');
546    end if;
547    utl_file.put_line(l_file_handle,','||c7_rec.column_name);
548 
549 
550 end loop;
551 close c7;
552 utl_file.put_line(l_file_handle,')');
553 utl_file.fclose(l_file_handle);
554 
555 ben_dm_utility.message('ROUT','exit:ben_dm_create_control_files.main', 5);
556 
557 end main;
558 
559 procedure rebuild_indexes is
560 
561 cursor c1 is
562 select index_name,
563        owner
564   from sys.all_indexes
565  where (index_name like 'BEN%'
566         or index_name like 'HR%'
567         or index_name like 'PAY%'
568         or index_name like 'PER%')
569   and status = 'UNUSABLE';
570 c1_rec c1%rowtype;
571 
572 begin
573 
574 ben_dm_utility.message('ROUT','entry:ben_dm_create_control_files.rebuild_indexes', 5);
575 open c1;
576 loop
577    fetch c1 into c1_rec;
578    if c1%notfound then
579       exit;
580    end if;
581 
582    ben_dm_utility.message('INFO','rebuilding index '||c1_rec.index_name, 5);
583    execute immediate 'alter index '||c1_rec.owner||'.'||c1_rec.index_name||' rebuild';
584 
585 end loop;
586 close c1;
587 
588 open c1;
589 fetch c1 into c1_rec;
590 if c1%found then
591    ben_dm_utility.message('INFO','some indexes failed to rebuild',5);
592 end if;
593 close c1;
594 
595 ben_dm_utility.message('ROUT','exit:ben_dm_create_control_files.rebuild_indexes', 5);
596 end rebuild_indexes;
597 
598 function set_dm_flag(p_person_id number)
599 return number is
600 begin
601    hr_general.g_data_migrator_mode := 'Y';
602    return p_person_id;
603 end set_dm_flag;
604 
605 end ben_dm_create_control_files;