1 Package Body ben_dm_input_file_pkg as
2 /* $Header: benfdmdmfile.pkb 120.0 2006/05/04 04:48:06 nkkrishn noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 -- Package Globals
9 --
10 type numTab is table of number(15) index by binary_integer;
11 type charTab is table of varchar2(80) index by binary_integer;
12 Type inpRec is Record
13 (source_bg varchar2(80),
14 source_ssn varchar2(80),
15 source_person_id number,
16 target_bg varchar2(80),
17 target_ssn varchar2(80),
18 group_order number,
19 person_type varchar2(1),
20 data_source varchar2(1),
21 process_flag varchar2(1));
22 Type inpTab is Table of inpRec index by binary_integer;
23 --
24 Type contactRec is Record
25 (person_id number,
26 ssn varchar2(80),
27 ind number);
28 Type contactTab is Table of contactRec index by binary_integer;
29 --
30 g_debug boolean := hr_utility.debug_enabled;
31 g_package varchar2(33) := 'ben_dm_input_file.';
32 g_missing_fields exception;
33 g_invalid_record exception;
34 --
35 -- ----------------------------------------------------------------------------
36 -- |------------------------------< split_line>------------------------------|
37 -- ----------------------------------------------------------------------------
38 --
39 function split_line(p_text varchar2,p_delimiter varchar2,p_min_fields number)
40 return charTab is
41
42 l_fields charTab;
43 l_start_pos number := 1;
44 l_end_pos number;
45 l_counter number :=1;
46
47 begin
48
49 loop
50 l_end_pos := nvl(instr(p_text,p_delimiter,1,l_counter),0);
51 if l_end_pos =0 and l_counter =1 then
52 l_fields(l_counter) := p_text;
53 l_start_pos := nvl(length(p_text),0)+1;
54 elsif l_end_pos =0 then
55 l_fields(l_counter) := substr(p_text,l_start_pos);
56 l_start_pos := nvl(length(p_text),0)+1;
57 elsif l_end_pos > 0 and l_start_pos = l_end_pos then
58 l_fields(l_counter) := null;
59 l_start_pos := l_end_pos +1;
60 elsif l_end_pos > 0 and l_start_pos < l_end_pos then
61 l_fields(l_counter) := substr(p_text,l_start_pos,l_end_pos-l_start_pos);
62 l_start_pos := l_end_pos +1;
63 end if;
64
65 if l_start_pos >= nvl(length(p_text),0) then
66 exit;
67 end if;
68 l_counter := l_counter +1;
69 end loop;
70
71 if l_fields.count < p_min_fields then
72 for i in l_fields.count+1..p_min_fields
73 loop
74 l_fields(i) := null;
75 end loop;
76 end if;
77
78 return l_fields;
79
80 exception
81 when others then
82 hr_utility.set_location('error encountered ',10);
83 end split_line;
84 --
85 -- ----------------------------------------------------------------------------
86 -- |------------------------------< add_to_set>------------------------------|
87 -- ----------------------------------------------------------------------------
88 --
89 procedure add_to_set(p_text varchar2,
90 p_collection in out nocopy charTab) is
91
92 begin
93
94 for i in 1..p_collection.count
95 loop
96
97 if p_collection(i) = p_text then
98 return;
99 end if;
100
101 end loop;
102 p_collection(p_collection.count+1) := p_text;
103
104 end add_to_set;
105 --
106 -- ----------------------------------------------------------------------------
107 -- |--------------------------------< read_file>------------------------------|
108 -- ----------------------------------------------------------------------------
109 --
110 procedure read_file
111 (p_migration_data ben_dm_utility.r_migration_rec,
112 p_delimiter in varchar2 default ';'
113 ) is
114
115 l_proc varchar2(255) := g_package||'read_file';
116 l_file_handle utl_file.file_type;
117 l_max_ext number := 32767;
118 l_line_text varchar2(32767);
119 l_line_count number := 0;
120 l_group_order number;
121 l_duplicate_rec boolean;
122 l_fields charTab;
123 l_inp_rec inpTab;
124 l_inp_rec2 inpTab;
125 l_contact_rec contactTab;
126 l_skip_count number := 0;
127 l_migration_id number := p_migration_data.migration_id;
128 l_file_name varchar2(60) := p_migration_data.input_parameter_file_name;
129 l_dir_name varchar2(60) := p_migration_data.input_parameter_file_path;
130 l_dummy varchar2(1);
131
132 cursor c_chk_per(p_person_id number,
133 p_national_identifier varchar2,
134 p_name varchar2) is
135 select null
136 from per_all_people_f per,
137 per_business_groups bg
138 where per.person_id = p_person_id
139 and per.national_identifier = p_national_identifier
140 and per.business_group_id = bg.business_group_id
141 and bg.name = p_name;
142
143 cursor c_get_contact(p_person_id number) is
144 select distinct
145 per1.person_id,
146 per1.national_identifier ssn,
147 -1
148 from per_all_people_f per1,
149 per_contact_relationships pcr
150 where pcr.person_id = p_person_id
151 and per1.person_id = pcr.contact_person_id
152 and per1.effective_start_date =
153 (select min(per2.effective_start_date)
154 from per_all_people_f per2
155 where per2.person_id = per1.person_id);
156
157 begin
158 hr_utility.set_location('Entering '||l_proc,5);
159 --
160 -- Ensure that all the mandatory arguments are not null
161 --
162 hr_api.mandatory_arg_error(p_api_name => l_proc,
163 p_argument => 'l_migration_id',
164 p_argument_value => l_migration_id);
165
166 hr_api.mandatory_arg_error(p_api_name => l_proc,
167 p_argument => 'l_file_name',
168 p_argument_value => l_file_name);
169
170 hr_api.mandatory_arg_error(p_api_name => l_proc,
171 p_argument => 'l_dir_name',
172 p_argument_value => l_dir_name);
173
174 --
175 -- get the file handle
176 --
177 l_file_handle := utl_file.fopen(l_dir_name,l_file_name,'r',l_max_ext);
178
179 --
180 -- fetch records from the file
181 --
182 loop
183 --
184 begin
185 --
186 utl_file.get_line(l_file_handle,l_line_text);
187 l_line_count := l_line_count +1;
188 l_fields := split_line(l_line_text,p_delimiter,5);
189
190 l_inp_rec(l_line_count).source_bg := rtrim(ltrim(l_fields(1)));
191 l_inp_rec(l_line_count).source_ssn := rtrim(ltrim(l_fields(2)));
192 l_inp_rec(l_line_count).source_person_id := rtrim(ltrim(l_fields(3)));
193 l_inp_rec(l_line_count).target_bg := rtrim(ltrim(l_fields(4)));
194 l_inp_rec(l_line_count).target_ssn := rtrim(ltrim(l_fields(5)));
195 l_inp_rec(l_line_count).data_source := 'I';
196
197 --
198 -- check for required fields in the record
199 --
200 if (l_inp_rec(l_line_count).source_bg is null or
201 l_inp_rec(l_line_count).target_bg is null or
202 l_inp_rec(l_line_count).source_ssn is null or
203 l_inp_rec(l_line_count).source_person_id is null
204 ) then
205 raise g_missing_fields;
206 end if;
207
208 --
209 -- check if the fields have valid values
210 --
211 open c_chk_per(l_inp_rec(l_line_count).source_person_id,
212 l_inp_rec(l_line_count).source_ssn,
213 l_inp_rec(l_line_count).source_bg);
214 fetch c_chk_per into l_dummy;
215 if c_chk_per%notfound then
216 close c_chk_per;
217 raise g_invalid_record;
218 end if;
219 close c_chk_per;
220
221 --
222 exception
223 --
224 when no_data_found then
225 --
226 -- EOF reached
227 --
228 exit;
229 end;
230 end loop;
231 --
232 utl_file.fclose(l_file_handle);
233 --
234 for i in 1..l_inp_rec.count
235 loop
236
237 if l_inp_rec(i).group_order is null then
238 --
239 l_contact_rec.delete;
240 l_group_order := null;
241 --
242 open c_get_contact(l_inp_rec(i).source_person_id);
243 fetch c_get_contact bulk collect into l_contact_rec;
244 close c_get_contact;
245 --
246 for j in 1..l_contact_rec.count
247 loop
248 for k in 1..l_inp_rec.count
249 loop
250 if l_inp_rec(k).source_person_id = l_contact_rec(j).person_id then
251 if l_inp_rec(k).group_order is not null then
252 --
253 l_duplicate_rec := false;
254 --
255 for m in 1..l_inp_rec.count
256 loop
257 if l_inp_rec(m).group_order = l_inp_rec(k).group_order and
258 l_inp_rec(m).data_source = 'I' and
259 l_inp_rec(m).source_ssn = l_inp_rec(i).source_ssn and
260 (l_inp_rec(m).target_bg <> l_inp_rec(i).target_bg or
261 nvl(l_inp_rec(m).target_ssn,l_inp_rec(m).source_ssn) <> nvl(l_inp_rec(i).target_ssn,l_inp_rec(i).source_ssn)) then
262 l_duplicate_rec := true;
263 exit;
264 end if;
265 end loop;
266 --
267 if not l_duplicate_rec then
268 l_group_order := nvl(l_inp_rec(k).group_order,l_group_order);
269 l_contact_rec(j).ind := k;
270 end if;
271 --
272 else
273 --
274 --There is a person record in the file for the dependent. not yet processed for group order
275 l_duplicate_rec := false;
276 --
277 for m in 1..l_inp_rec.count
278 loop
279 if -- l_inp_rec(m).group_order = l_inp_rec(k).group_order and
280 l_inp_rec(m).data_source = 'I' and
281 l_inp_rec(m).source_ssn = l_inp_rec(i).source_ssn and
282 (l_inp_rec(m).target_bg <> l_inp_rec(i).target_bg or
283 nvl(l_inp_rec(m).target_ssn,l_inp_rec(m).source_ssn) <> nvl(l_inp_rec(i).target_ssn,l_inp_rec(i).source_ssn)) then
284 l_duplicate_rec := true;
285 exit;
286 end if;
287 end loop;
288 --
289 if not l_duplicate_rec then
290 -- l_group_order := nvl(l_inp_rec(k).group_order,l_group_order);
291 l_contact_rec(j).ind := k;
292 end if;
293 --entry for the dependent.
294 --
295 end if;
296 exit;
297 end if;
298 end loop;
299 end loop;
300 -- Check if this person is already got created as dependent to another person. If so mark the dependent person status
301 -- such that it won't be selected in the subsequent process.
302
303 --
304 if l_group_order is null then
305 select ben_dm_group_order_s.nextval
306 into l_group_order
307 from dual;
308 end if;
309
310 l_inp_rec(i).group_order := l_group_order;
311
312 for j in 1..l_contact_rec.count
313 loop
314 if l_contact_rec(j).ind <> -1 then
315 l_inp_rec(l_contact_rec(j).ind).group_order := l_group_order;
316 else
317 l_line_count := l_inp_rec.count +1;
318 l_inp_rec(l_line_count).source_bg := l_inp_rec(i).source_bg;
319 l_inp_rec(l_line_count).source_ssn := l_contact_rec(j).ssn;
320 l_inp_rec(l_line_count).source_person_id := l_contact_rec(j).person_id;
321 l_inp_rec(l_line_count).target_bg := l_inp_rec(i).target_bg;
322 l_inp_rec(l_line_count).group_order := l_group_order;
323 l_inp_rec(l_line_count).data_source := 'D';
324 l_inp_rec(l_line_count).person_type := 'D';
325
326 end if;
327
328 end loop;
329 end if;
330
331
332 end loop;
333 --
334 hr_utility.set_location('deleting existing records',10);
335 --
336 delete from ben_dm_input_file;
337 --
338 hr_utility.set_location('bulk inserting new records',10);
339 --
340 l_line_count := l_inp_rec.count;
341 --
342 -- skip groups that have one or more person records with null SSN
343 --
344 for i in 1..l_line_count
345 loop
346 --
347 if l_inp_rec(i).process_flag = 'N' then
348 null;
349 else
350 if l_inp_rec(i).source_ssn is null then
351 l_group_order := l_inp_rec(i).group_order;
352 for j in 1..l_line_count
353 loop
354 if l_group_order = l_inp_rec(j).group_order then
355 l_inp_rec(j).process_flag := 'N';
356 end if;
357 end loop;
358 end if;
359 end if;
360 --
361 end loop;
362 --
363 for i in 1..l_line_count
364 loop
365 if nvl(l_inp_rec(i).process_flag,'Y') <> 'N' then
366 insert into ben_dm_input_file
367 (input_file_id,
368 status,
369 source_business_group_name,
370 source_national_identifier,
371 source_person_id,
372 target_business_group_name,
373 target_national_identifier,
374 group_order,
375 person_type,
376 data_source)
377 values
378 (ben_dm_input_file_s.nextval,
379 'NS',
380 l_inp_rec(i).source_bg,
381 l_inp_rec(i).source_ssn,
382 l_inp_rec(i).source_person_id,
383 l_inp_rec(i).target_bg,
384 l_inp_rec(i).target_ssn,
385 l_inp_rec(i).group_order,
386 nvl(l_inp_rec(i).person_type,'P'),
387 l_inp_rec(i).data_source);
388 end if;
389 end loop;
390 --
391 --Check to remove the duplicate records from the ben_dm_input_file
392 --There should be always only one record for the following record combination
393 -- source_business_group_name
394 -- source_national_identifier
395 -- source_person_id
396 -- target_national_identifier
397 -- target_business_group_name
398 --
399 DELETE FROM ben_dm_input_file mas
400 WHERE ROWID > ( SELECT min(rowid)
401 FROM ben_dm_input_file chi
402 WHERE mas.source_business_group_name = chi.source_business_group_name
403 and mas.source_national_identifier = chi.source_national_identifier
404 and mas.source_person_id = chi.source_person_id
405 and mas.group_order = chi.group_order
406 and mas.target_business_group_name = chi.target_business_group_name
407 AND nvl(mas.target_national_identifier,mas.source_national_identifier) =
408 nvl(chi.target_national_identifier,chi.source_national_identifier)
409 ) ;
410 --
411 commit;
412 --
413 -- Report groups skipped
414 --
415 for i in 1..l_inp_rec.count
416 loop
417 if l_inp_rec(i).data_source = 'I' and
418 l_inp_rec(i).process_flag = 'N' then
419
420 l_skip_count := nvl(l_skip_count,0) +1;
421 if l_skip_count = 1 then
422 fnd_file.put_line(fnd_file.output,'Following records from the input file have been skipped because one or more of the dependents do not have an SSN');
423 end if;
424 fnd_file.put_line(fnd_file.output,
425 l_inp_rec(i).source_bg||p_delimiter||
426 l_inp_rec(i).source_ssn||p_delimiter||
427 l_inp_rec(i).source_person_id||p_delimiter||
428 l_inp_rec(i).target_bg||p_delimiter||
429 l_inp_rec(i).target_ssn);
430
431 end if;
432
433 end loop;
437 when utl_file.invalid_path then
434
435 hr_utility.set_location('Leaving '||l_proc,5);
436 exception
438 rollback;
439 fnd_message.set_name('BEN', 'BEN_91874_EXT_DRCTRY_ERR'); --9999
440 fnd_file.put_line(fnd_file.log, fnd_message.get);
441 if utl_file.is_open(l_file_handle) then
442 utl_file.fclose(l_file_handle);
443 end if;
444 fnd_message.raise_error;
445 --
446 when utl_file.invalid_mode then
447 rollback;
448 fnd_message.set_name('BEN', 'BEN_92249_UTL_INVLD_MODE');
449 fnd_file.put_line(fnd_file.log, fnd_message.get);
450 if utl_file.is_open(l_file_handle) then
451 utl_file.fclose(l_file_handle);
452 end if;
453 fnd_message.raise_error;
454 --
455 when utl_file.invalid_filehandle then
456 rollback;
457 fnd_message.set_name('BEN', 'BEN_92250_UTL_INVLD_FILEHANDLE');
458 fnd_file.put_line(fnd_file.log, fnd_message.get);
459 if utl_file.is_open(l_file_handle) then
460 utl_file.fclose(l_file_handle);
461 end if;
462 fnd_message.raise_error;
463 --
464 when utl_file.invalid_operation then
465 rollback;
466 fnd_message.set_name('BEN', 'BEN_92251_UTL_INVLD_OPER');
467 fnd_file.put_line(fnd_file.log, fnd_message.get);
468 if utl_file.is_open(l_file_handle) then
469 utl_file.fclose(l_file_handle);
470 end if;
471 fnd_message.raise_error;
472 --
473 when utl_file.read_error then
474 rollback;
475 fnd_message.set_name('BEN', 'BEN_92252_UTL_READ_ERROR');
476 fnd_file.put_line(fnd_file.log, fnd_message.get);
477 if utl_file.is_open(l_file_handle) then
478 utl_file.fclose(l_file_handle);
479 end if;
480 fnd_message.raise_error;
481 --
482 when utl_file.internal_error then
483 rollback;
484 fnd_message.set_name('BEN', 'BEN_92253_UTL_INTRNL_ERROR');
485 fnd_file.put_line(fnd_file.log, fnd_message.get);
486 if utl_file.is_open(l_file_handle) then
487 utl_file.fclose(l_file_handle);
488 end if;
489 fnd_message.raise_error;
490 --
491 when utl_file.invalid_maxlinesize then
492 rollback;
493 fnd_message.set_name ('BEN' ,'BEN_92492_UTL_LINESIZE_ERROR');
494 fnd_file.put_line(fnd_file.log , fnd_message.get );
495 if utl_file.is_open(l_file_handle) then
496 utl_file.fclose(l_file_handle);
497 end if;
498 fnd_message.raise_error ;
499 --
500 when g_missing_fields then
501 rollback;
502 fnd_file.put_line(fnd_file.log , 'One ore more fields missing in this Record: '||substr(l_line_text,1,80));
503 if utl_file.is_open(l_file_handle) then
504 utl_file.fclose(l_file_handle);
505 end if;
506 raise;
507 --
508 when g_invalid_record then
509 rollback;
510 fnd_file.put_line(fnd_file.log , 'Invalid fields in this Record: '||substr(l_line_text,1,80));
511 if utl_file.is_open(l_file_handle) then
512 utl_file.fclose(l_file_handle);
513 end if;
514 raise;
515 --
516 when others then
517 rollback;
518 if utl_file.is_open(l_file_handle) then
519 utl_file.fclose(l_file_handle);
520 end if;
521 raise;
522
523 end read_file;
524
525 end ben_dm_input_file_pkg;
[Home] [Help]