[Home] [Help]
PACKAGE BODY: APPS.HR_HRHD_INITIAL_LOAD
Source
1 PACKAGE BODY HR_HRHD_INITIAL_LOAD as
2 /* $Header: perhdfsyn.pkb 120.4.12020000.3 2013/02/22 15:20:38 pathota ship $ */
3
4 p_effective_date DATE default sysdate;
5
6 /*Procedure to extract Location Initial Load Extraction Begins*/
7 PROCEDURE HR_LOCATION_INITIAL_LOAD(errbuf OUT NOCOPY VARCHAR2
8 ,retcode OUT NOCOPY VARCHAR2
9 ,p_number_of_files in Number)
10 is
11
12 p_bg_id hr_locations_all.business_group_id%type;
13 p_loc_id hr_locations_all.LOCATION_ID%type;
14 p_active_date varchar2(10);
15 p_effecive_status varchar2(10);
16 p_lang_code varchar2(10);
17 p_loc_desc hr_locations_all.DESCRIPTION%type;
18 p_loc_style hr_locations_all.STYLE%type;
19 p_add_line_1 hr_locations_all.ADDRESS_LINE_1%type;
20 p_add_line_2 hr_locations_all.ADDRESS_LINE_2%type;
21 p_add_line_3 hr_locations_all.ADDRESS_LINE_3%type;
22 p_town_or_city hr_locations_all.TOWN_OR_CITY%type;
23 p_country hr_locations_all.COUNTRY%type;
24 p_postal_code hr_locations_all.POSTAL_CODE%type;
25 p_region_1 hr_locations_all.REGION_1%type;
26 p_region_2 hr_locations_all.REGION_2%type;
27 p_region_3 hr_locations_all.REGION_3%type;
28 p_tel_no_1 hr_locations_all.TELEPHONE_NUMBER_1%type;
29 p_tel_no_2 hr_locations_all.TELEPHONE_NUMBER_2%type;
30 p_tel_no_3 hr_locations_all.TELEPHONE_NUMBER_3%type;
31 p_loc_info_13 hr_locations_all.LOC_INFORMATION13%type;
32 p_loc_info_14 hr_locations_all.LOC_INFORMATION14%type;
33 p_loc_info_15 hr_locations_all.LOC_INFORMATION15%type;
34 p_loc_info_16 hr_locations_all.LOC_INFORMATION16%type;
35 p_loc_info_17 hr_locations_all.LOC_INFORMATION17%type;
36 p_loc_info_18 hr_locations_all.LOC_INFORMATION18%type;
37 p_loc_info_19 hr_locations_all.LOC_INFORMATION19%type;
38 p_loc_info_20 hr_locations_all.LOC_INFORMATION20%type;
39
40 l_record_counter Number :=0;
41 l_file_counter Number :=0;
42 r Number :=1;
43 number_of_files Number := p_number_of_files;
44 l_file_path Varchar2(500) := null;
45 l_file_name_part Varchar2(200) := null;
46 l_location_exception Exception;
47 l_file_pointer utl_file.file_type;
48 l_break_count Number;
49
50
51
52 /*CURSOR TO FETCH THE LOCATION DETAILS*/
53 cursor CSR_LOC_INITIAL_LOAD is
54 select hloc.BUSINESS_GROUP_ID,
55 to_char(hloc.CREATION_DATE,'YYYY-MM-DD'),
56 'A' ,
57 hloc.LOCATION_ID,
58 tl.language,
59 tl.DESCRIPTION,
60 STYLE,
61 COUNTRY,
62 ADDRESS_LINE_1,
63 ADDRESS_LINE_2,
64 ADDRESS_LINE_3,
65 TOWN_OR_CITY,
66 REGION_1,
67 REGION_2,
68 REGION_3,
69 POSTAL_CODE,
70 TELEPHONE_NUMBER_1,
71 TELEPHONE_NUMBER_2,
72 TELEPHONE_NUMBER_3,
73 LOC_INFORMATION13,
74 LOC_INFORMATION14,
75 LOC_INFORMATION15,
76 LOC_INFORMATION16,
77 LOC_INFORMATION17,
78 LOC_INFORMATION18,
79 LOC_INFORMATION19,
80 LOC_INFORMATION20
81 from
82 hr_locations_all hloc,hr_locations_all_tl tl
83 where tl.location_id = hloc.location_id
84 and nvl(inactive_date,to_date('31/12/4712','DD/MM/YYYY')) > sysdate
85 union
86 select hloc.BUSINESS_GROUP_ID,
87 to_char(inactive_date,'YYYY-MM-DD'),
88 'I',
89 hloc.LOCATION_ID,
90 tl.language,
91 tl.DESCRIPTION,
92 STYLE,
93 COUNTRY,
94 ADDRESS_LINE_1,
95 ADDRESS_LINE_2,
96 ADDRESS_LINE_3,
97 TOWN_OR_CITY,
98 REGION_1,
99 REGION_2,
100 REGION_3,
101 POSTAL_CODE,
102 TELEPHONE_NUMBER_1,
103 TELEPHONE_NUMBER_2,
104 TELEPHONE_NUMBER_3,
105 LOC_INFORMATION13,
106 LOC_INFORMATION14,
107 LOC_INFORMATION15,
108 LOC_INFORMATION16,
109 LOC_INFORMATION17,
110 LOC_INFORMATION18,
111 LOC_INFORMATION19,
112 LOC_INFORMATION20
113 from
114 hr_locations_all hloc,hr_locations_all_tl tl
115 where tl.location_id = hloc.location_id
116 and inactive_date is not null
117 order by business_group_id,location_id ;
118
119 begin
120
121
122 FND_FILE.NEW_LINE(FND_FILE.log, 1);
123 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
124 FND_FILE.put_line(fnd_file.log,'Location Initial Load Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
125
126 /*Start of logic to break the load into preset number of files*/
127 If number_of_files is not null
128 Then
129 r :=0;
130 For rec in CSR_LOC_INITIAL_LOAD
131 Loop
132 r := r+1;
133 End Loop;
134
135 --If r >0 then total_record_count := r; End If;
136
137 If r < number_of_files Then
138 FND_FILE.put_line(fnd_file.log,'No. of files is more than the Number of total records');
139 End If;
140 Else
141 number_of_files := 1;
142 r := 1;
143 End If;
144 FND_FILE.NEW_LINE(FND_FILE.log, 1);
145 FND_FILE.NEW_LINE(FND_FILE.log, 1);
146 FND_FILE.put_line(fnd_file.log,'Total number_of_files = '||number_of_files);
147 if (r > 1) then
148 FND_FILE.put_line(fnd_file.log,'Total Number of Records = '||r);
149 end if;
150 fnd_profile.get('UTL_FILE_OUT', l_file_path);
151 FND_FILE.put_line(fnd_file.log,'File Path = '||l_file_path);
152 l_file_name_part := fnd_global.conc_request_id;
153 FND_FILE.put_line(fnd_file.log,'File names start with : '||l_file_name_part);
154 if (l_file_path is not null)
155 Then
156
157 /*Generate the initial load extraction for location and the column delimiter used is to_char(400)*/
158 l_record_counter := 1;
159 l_file_counter := 1;
160 l_break_count := 0;
161 /*Generate the initial load extraction for location and the column delimiter used is to_char(400)*/
162 open CSR_LOC_INITIAL_LOAD;
163 loop
164
165 if ((l_record_counter = ((l_file_counter-1)*(TRUNC((r/number_of_files))))+1 ) )
166 Then
167 l_file_pointer := null;
168 l_file_pointer := UTL_FILE.FOPEN(l_file_path,l_file_name_part||'_'||l_file_counter||'.out','W',32767);
169 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out'||' - Opened.');
170 End If;
171
172 fetch CSR_LOC_INITIAL_LOAD into p_bg_id,p_active_date,p_effecive_status,p_loc_id,p_lang_code,
173 p_loc_desc, p_loc_style , p_country, p_add_line_1, p_add_line_2, p_add_line_3,
174 p_town_or_city,p_region_1,p_region_2,p_region_3,p_postal_code,p_tel_no_1,p_tel_no_2 ,
175 p_tel_no_3,p_loc_info_13, p_loc_info_14,p_loc_info_15,p_loc_info_16,p_loc_info_17,p_loc_info_18,
176 p_loc_info_19,p_loc_info_20;
177
178 exit when CSR_LOC_INITIAL_LOAD%NOTFOUND;
179
180
181 UTL_FILE.PUT_LINE(l_file_pointer,p_bg_id||
182 fnd_global.local_chr(400)||p_active_date||
183 fnd_global.local_chr(400)||p_effecive_status||
184 fnd_global.local_chr(400)||p_loc_id||
185 fnd_global.local_chr(400)||p_lang_code||
186 fnd_global.local_chr(400)||p_loc_desc||
187 fnd_global.local_chr(400)||p_loc_style ||
188 fnd_global.local_chr(400)|| p_add_line_1||
189 fnd_global.local_chr(400)||p_add_line_2||
190 fnd_global.local_chr(400)|| p_add_line_3||
191 fnd_global.local_chr(400)||p_town_or_city||
192 fnd_global.local_chr(400)||p_country||
193 fnd_global.local_chr(400)||p_postal_code||
194 fnd_global.local_chr(400)||p_region_1||
195 fnd_global.local_chr(400)||p_region_2||
196 fnd_global.local_chr(400)||p_region_3||
197 fnd_global.local_chr(400)||p_tel_no_1||
198 fnd_global.local_chr(400)||p_tel_no_2 ||
199 fnd_global.local_chr(400)||p_tel_no_3||
200 fnd_global.local_chr(400)||p_loc_info_13||
201 fnd_global.local_chr(400)||p_loc_info_14||
202 fnd_global.local_chr(400)||p_loc_info_15||
203 fnd_global.local_chr(400)||p_loc_info_16||
204 fnd_global.local_chr(400)||p_loc_info_17||
205 fnd_global.local_chr(400)||p_loc_info_18||
206 fnd_global.local_chr(400)||p_loc_info_19||
207 fnd_global.local_chr(400)||p_loc_info_20||
208 fnd_global.local_chr(10)||fnd_global.local_chr(13));
209
210 if (l_record_counter = ((l_file_counter)*(TRUNC(r/number_of_files))) and l_file_counter <> number_of_files)
211 Then
212 If UTL_FILE.IS_OPEN(l_file_pointer)
213 Then
214 UTL_FILE.FCLOSE(l_file_pointer);
215
216 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out - closed.');
217 FND_FILE.put_line(fnd_file.log,'Number of records written into '||l_file_name_part||'_'||l_file_counter||'.out = '||(l_record_counter - l_break_count));
218 l_break_count := l_record_counter;
219 End If;
220 l_file_counter := l_file_counter + 1;
221 End If;
222 l_record_counter := l_record_counter + 1;
223
224 end loop;
225 close CSR_LOC_INITIAL_LOAD;
226
227 If UTL_FILE.IS_OPEN(l_file_pointer)
228 Then
229 UTL_FILE.FCLOSE(l_file_pointer);
230 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out - closed.');
231 FND_FILE.put_line(fnd_file.log,'Number of records written into '||l_file_name_part||'_'||l_file_counter||'.out = '||((l_record_counter - 1) - l_break_count));
232 End If;
233 FND_FILE.PUT_LINE(fnd_file.log,'Total Number of records written into '||l_file_counter||' files = '|| (l_record_counter - 1) );
234 Else
235 FND_FILE.NEW_LINE(FND_FILE.log, 1);
236 FND_FILE.put_line(fnd_file.log,'Profile option FND_FILE_OUT has null value. Please specify valid path');
237 raise l_location_exception;
238 End If;
239
240
241 FND_FILE.NEW_LINE(FND_FILE.log, 1);
242 FND_FILE.put_line(fnd_file.log,'Location Initial Load Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
243
244
245 exception
246 when OTHERS then
247 errbuf := errbuf||SQLERRM;
248 retcode := '1';
249 FND_FILE.put_line(fnd_file.log, 'Error in Location Initial Load Extraction: '||SQLCODE);
250 FND_FILE.NEW_LINE(FND_FILE.log, 1);
251 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
252
253 END HR_LOCATION_INITIAL_LOAD;
254 /*Procedure to extract Location Initial Load Extraction Ends*/
255
256 /*Procedure to extract Job Initial Load Extraction Begins*/
257
258 PROCEDURE HR_JOBCODE_INITIAL_LOAD(errbuf OUT NOCOPY VARCHAR2
259 ,retcode OUT NOCOPY VARCHAR2
260 ,p_number_of_files in Number)
261 IS
262
263
264 p_job_id per_jobs.job_id%type;
265 p_business_grp_id per_jobs.business_group_id%type;
266 p_eff_date varchar2(10);
267 p_lang_code varchar2(10);
268 p_eff_status varchar2(10);
269 p_job_descr per_jobs_tl.name%type;
270
271 l_record_counter Number :=0;
272 l_file_counter Number :=0;
273 r Number :=1;
274 number_of_files Number := p_number_of_files;
275 l_file_path Varchar2(500) := null;
276 l_file_name_part Varchar2(200) := null;
277 l_location_exception Exception;
278 l_file_pointer utl_file.file_type;
279 l_break_count Number;
280
281
282 /*Cursor to fetch the job details*/
283
284 cursor csr_job_initial_load is
285 select pj.job_id,
286 business_group_id,
287 tl.language,
288 tl.name,
289 to_char(DATE_FROM,'YYYY-MM-DD') ,
290 'A'
291 from per_jobs pj,per_jobs_tl tl
292 where pj.job_id = tl.job_id
293 and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY')) > sysdate
294 union
295 select pj.job_id,
296 business_group_id,
297 tl.language,
298 tl.name,
299 to_char(DATE_TO,'YYYY-MM-DD') ,
300 'I'
301 from per_jobs pj,per_jobs_tl tl
302 where pj.job_id = tl.job_id
303 and date_to is not null
304 order by business_group_id,job_id;
305
306
307
308 begin
309
310
311 FND_FILE.NEW_LINE(FND_FILE.log, 1);
312 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
313 FND_FILE.put_line(fnd_file.log,'Job Code Initial Load Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
314 If number_of_files is not null
315 Then
316 r :=0;
317 For rec in csr_job_initial_load
318 Loop
319 r := r+1;
320 End Loop;
321
322 --If r >0 then total_record_count := r; End If;
323
324 If r < number_of_files Then
325 FND_FILE.put_line(fnd_file.log,'No. of files is more than the Number of total records');
326 End If;
327 Else
328 number_of_files := 1;
329 r := 1;
330 End If;
331 FND_FILE.NEW_LINE(FND_FILE.log, 1);
332 FND_FILE.NEW_LINE(FND_FILE.log, 1);
333 FND_FILE.put_line(fnd_file.log,'Total number_of_files = '||number_of_files);
334 if (r > 1) then
335 FND_FILE.put_line(fnd_file.log,'Total Number of Records = '||r);
336 end if;
337 fnd_profile.get('UTL_FILE_OUT', l_file_path);
338 FND_FILE.put_line(fnd_file.log,'File Path = '||l_file_path);
339 l_file_name_part := fnd_global.conc_request_id;
340 FND_FILE.put_line(fnd_file.log,'File names start with : '||l_file_name_part);
341 if (l_file_path is not null)
342 Then
343
344 /*Generate the initial load extraction for location and the column delimiter used is to_char(400)*/
345 l_record_counter := 1;
346 l_file_counter := 1;
347 l_break_count := 0;
348 /*Generate the initial load extraction for job and the column delimiter used is to_char(400)*/
349 OPEN csr_job_initial_load;
350
351 LOOP
352 if ((l_record_counter = ((l_file_counter-1)*(TRUNC((r/number_of_files))))+1 ) )
353 Then
354 l_file_pointer := null;
355 l_file_pointer := UTL_FILE.FOPEN(l_file_path,l_file_name_part||'_'||l_file_counter||'.out','W',32767);
356 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out'||' - Opened.');
357 End If;
358 FETCH csr_job_initial_load
359 INTO p_job_id,p_business_grp_id,p_lang_code,p_job_descr,p_eff_date,p_eff_status;
360 EXIT WHEN csr_job_initial_load%NOTFOUND;
361
362
363 UTL_FILE.PUT_LINE(l_file_pointer,
364 p_business_grp_id||fnd_global.local_chr(400)||
365 p_job_id||fnd_global.local_chr(400)||
366 p_eff_date||fnd_global.local_chr(400)||
367 p_eff_status||fnd_global.local_chr(400)||
368 p_lang_code||fnd_global.local_chr(400)||
369 p_job_descr||
370 fnd_global.local_chr(10)||fnd_global.local_chr(13));
371
372 if (l_record_counter = ((l_file_counter)*(TRUNC(r/number_of_files))) and l_file_counter <> number_of_files)
373 Then
374 If UTL_FILE.IS_OPEN(l_file_pointer)
375 Then
376 UTL_FILE.FCLOSE(l_file_pointer);
377
378 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out - closed.');
379 FND_FILE.put_line(fnd_file.log,'Number of records written into '||l_file_name_part||'_'||l_file_counter||'.out = '||(l_record_counter - l_break_count));
380 l_break_count := l_record_counter;
381 End If;
382 l_file_counter := l_file_counter + 1;
383 End If;
384 l_record_counter := l_record_counter + 1;
385 END Loop;
386 CLOSE csr_job_initial_load;
387
388 If UTL_FILE.IS_OPEN(l_file_pointer)
389 Then
390 UTL_FILE.FCLOSE(l_file_pointer);
391 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out - closed.');
392 FND_FILE.put_line(fnd_file.log,'Number of records written into '||l_file_name_part||'_'||l_file_counter||'.out = '||((l_record_counter - 1) - l_break_count));
393 End If;
394 FND_FILE.PUT_LINE(fnd_file.log,'Total Number of records written into '||l_file_counter||' files = '|| (l_record_counter - 1) );
395 Else
396 FND_FILE.NEW_LINE(FND_FILE.log, 1);
397 FND_FILE.put_line(fnd_file.log,'Profile option FND_FILE_OUT has null value. Please specify valid path');
398 raise l_location_exception;
399 End If;
400
401 FND_FILE.NEW_LINE(FND_FILE.log, 1);
402 FND_FILE.put_line(fnd_file.log,'Job Code Initial Load Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
403
404 EXCEPTION WHEN OTHERS THEN
405 errbuf := errbuf||SQLERRM;
406 retcode := '1';
407 FND_FILE.put_line(fnd_file.log, 'Error in Job Initial Load Extraction: '||SQLCODE);
408 FND_FILE.NEW_LINE(FND_FILE.log, 1);
409 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
410
411 END HR_JOBCODE_INITIAL_LOAD;
412
413 /*Procedure to extract Job Initial Load Ends*/
414
415
416
417 /*Procedure to extract Organization Initial Load Extraction Begins*/
418
419 PROCEDURE HR_ORGANIZATION_INITIAL_LOAD(errbuf OUT NOCOPY VARCHAR2
420 ,retcode OUT NOCOPY VARCHAR2,p_number_of_files in Number)
421 IS
422
423
424 p_bg_id hr_all_organization_units.business_group_id%type;
425 p_org_id hr_all_organization_units.organization_id%type;
426 p_lang_code varchar2(10);
427 p_bg_name hr_all_organization_units_tl.name%type;
428 p_eff_status varchar2(10);
429 p_loc_id hr_all_organization_units.location_id%type;
430 p_eff_date varchar2(10);
431 p_person_id per_org_manager_v.person_id%type;
432
433 l_record_counter Number :=0;
434 l_file_counter Number :=0;
435 r Number :=1;
436 number_of_files Number := p_number_of_files;
437 l_file_path Varchar2(500) := null;
438 l_file_name_part Varchar2(200) := null;
439 l_location_exception Exception;
440 l_file_pointer utl_file.file_type;
441 l_break_count Number;
442
443
444
445
446 /*Cursor to fetch the organization details
447 If date_to is in future then
448 two records has to be fetched in the format
449 Date_From 'A'
450 Date_To 'I'
451
452 If date_to is in past then
453 one record has to be fetched as
454 Date_To 'I'*/
455
456 cursor csr_org_initial_load is
457 select ORG.BUSINESS_GROUP_ID,
458 ORG.ORGANIZATION_ID,
459 to_char(DATE_FROM,'YYYY-MM-DD') ,
460 'A' ,
461 TL.LANGUAGE,
462 TL.NAME,
463 ORG.LOCATION_ID,
464 /*Fix for 7576511 - to fetch employee number*/
465 (select employee_number from per_all_people_f ppf,hr_organization_information hrorg1
466 where ppf.person_id = hrorg1.ORG_INFORMATION2
467 and ppf.business_group_id = org.business_group_id
468 and hrorg1.org_information_context = 'Organization Name Alias'
469 and hrorg1.organization_id = org.organization_id
470 and nvl(org.date_to,to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hrorg1.org_information3)
471 and nvl(fnd_date.canonical_to_date(hrorg1.org_information4),to_date('31/12/4712','DD/MM/YYYY'))
472 and fnd_date.canonical_to_date(hrorg1.org_information3) between ppf.effective_start_date and ppf.effective_end_date) MANAGER_ID
473
474 from hr_all_organization_units org,hr_all_organization_units_tl TL
475 ,hr_organization_information hrorg
476 where tl.organization_id = org.organization_id
477 and hrorg.organization_id = org.organization_id
478 and hrorg.org_information1 = 'HR_ORG'
479 and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY')) > sysdate
480 union
481 select ORG.BUSINESS_GROUP_ID,
482 ORG.ORGANIZATION_ID,
483 to_char(DATE_TO,'YYYY-MM-DD') ,
484 'I' ,
485 TL.LANGUAGE,
486 TL.NAME,
487 ORG.LOCATION_ID,
488 /*Fix for 7576511 - to fetch employee number*/
489 (select employee_number from per_all_people_f ppf,hr_organization_information hrorg1
490 where ppf.person_id = hrorg1.ORG_INFORMATION2
491 and ppf.business_group_id = org.business_group_id
492 and hrorg1.org_information_context = 'Organization Name Alias'
493 and hrorg1.organization_id = org.organization_id
494 and nvl(org.date_to,to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hrorg1.org_information3)
495 and nvl(fnd_date.canonical_to_date(hrorg1.org_information4),to_date('31/12/4712','DD/MM/YYYY'))
496 and fnd_date.canonical_to_date(hrorg1.org_information3) between ppf.effective_start_date and ppf.effective_end_date) MANAGER_ID
497
498 from hr_all_organization_units org,hr_all_organization_units_tl TL
499 ,hr_organization_information hrorg
500 where tl.organization_id = org.organization_id
501 and hrorg.organization_id = org.organization_id
502 and hrorg.org_information1 = 'HR_ORG'
503 and date_to is not null
504 order by business_group_id,organization_id;
505
506
507 begin
508
509
510 FND_FILE.NEW_LINE(FND_FILE.log, 1);
511 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
512 FND_FILE.put_line(fnd_file.log,'Organization Initial Load Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
513 If number_of_files is not null
514 Then
515 r :=0;
516 For rec in csr_org_initial_load
517 Loop
518 r := r+1;
519 End Loop;
520
521 --If r >0 then total_record_count := r; End If;
522
523 If r < number_of_files Then
524 FND_FILE.put_line(fnd_file.log,'No. of files is more than the Number of total records');
525 End If;
526 Else
527 number_of_files := 1;
528 r := 1;
529 End If;
530 FND_FILE.NEW_LINE(FND_FILE.log, 1);
531 FND_FILE.NEW_LINE(FND_FILE.log, 1);
532 FND_FILE.put_line(fnd_file.log,'Total number_of_files = '||number_of_files);
533 if (r > 1) then
534 FND_FILE.put_line(fnd_file.log,'Total Number of Records = '||r);
535 end if;
536 fnd_profile.get('UTL_FILE_OUT', l_file_path);
537 FND_FILE.put_line(fnd_file.log,'File Path = '||l_file_path);
538 l_file_name_part := fnd_global.conc_request_id;
539 FND_FILE.put_line(fnd_file.log,'File names start with : '||l_file_name_part);
540 if (l_file_path is not null)
541 Then
542
543 /*Generate the initial load extraction for location and the column delimiter used is to_char(400)*/
544 l_record_counter := 1;
545 l_file_counter := 1;
546 l_break_count := 0;
547 /*Generate the initial load extraction for organization and the column delimiter used is to_char(400)*/
548 OPEN csr_org_initial_load;
549
550 LOOP
551 if ((l_record_counter = ((l_file_counter-1)*(TRUNC((r/number_of_files))))+1 ) )
552 Then
553 l_file_pointer := null;
554 l_file_pointer := UTL_FILE.FOPEN(l_file_path,l_file_name_part||'_'||l_file_counter||'.out','W',32767);
555 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out'||' - Opened.');
556 End If;
557 FETCH csr_org_initial_load
558 INTO p_bg_id,p_org_id,p_eff_date,p_eff_status,p_lang_code,p_bg_name,p_loc_id,p_person_id;
559 EXIT WHEN csr_org_initial_load%NOTFOUND;
560
561
562 UTL_FILE.PUT_LINE(l_file_pointer,
563 p_bg_id||fnd_global.local_chr(400)||
564 p_org_id||fnd_global.local_chr(400)||
565 p_eff_date||fnd_global.local_chr(400)||
566 p_eff_status||fnd_global.local_chr(400)||
567 p_lang_code||fnd_global.local_chr(400)||
568 p_bg_name||fnd_global.local_chr(400)||
569 p_loc_id||fnd_global.local_chr(400)||
570 p_person_id||
571 fnd_global.local_chr(10)||fnd_global.local_chr(13));
572
573 if (l_record_counter = ((l_file_counter)*(TRUNC(r/number_of_files))) and l_file_counter <> number_of_files)
574 Then
575 If UTL_FILE.IS_OPEN(l_file_pointer)
576 Then
577 UTL_FILE.FCLOSE(l_file_pointer);
578
579 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out - closed.');
580 FND_FILE.put_line(fnd_file.log,'Number of records written into '||l_file_name_part||'_'||l_file_counter||'.out = '||(l_record_counter - l_break_count));
581 l_break_count := l_record_counter;
582 End If;
583 l_file_counter := l_file_counter + 1;
584 End If;
585 l_record_counter := l_record_counter + 1;
586
587
588 END Loop;
589 CLOSE csr_org_initial_load;
590 If UTL_FILE.IS_OPEN(l_file_pointer)
591 Then
592 UTL_FILE.FCLOSE(l_file_pointer);
593 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out - closed.');
594 FND_FILE.put_line(fnd_file.log,'Number of records written into '||l_file_name_part||'_'||l_file_counter||'.out = '||((l_record_counter - 1) - l_break_count));
595 End If;
596 FND_FILE.PUT_LINE(fnd_file.log,'Total Number of records written into '||l_file_counter||' files = '|| (l_record_counter - 1) );
597 Else
598 FND_FILE.NEW_LINE(FND_FILE.log, 1);
599 FND_FILE.put_line(fnd_file.log,'Profile option FND_FILE_OUT has null value. Please specify valid path');
600 raise l_location_exception;
601 End If;
602 FND_FILE.NEW_LINE(FND_FILE.log, 1);
603 FND_FILE.put_line(fnd_file.log,'Organization Initial Load Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
604
605 EXCEPTION WHEN OTHERS THEN
606 errbuf := errbuf||SQLERRM;
607 retcode := '1';
608 FND_FILE.put_line(fnd_file.log, 'Error in Organization Initial Load Extraction: '||SQLCODE);
609 FND_FILE.NEW_LINE(FND_FILE.log, 1);
610 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
611
612 END HR_ORGANIZATION_INITIAL_LOAD;
613
614 /*Procedure to extract Organization Initial Load Ends*/
615
616
617 /*Procedure to extract Workforce Initial Load Extraction Begins*/
618
619 PROCEDURE HR_WORKFORCE_INITIAL_LOAD(errbuf OUT NOCOPY VARCHAR2
620 ,retcode OUT NOCOPY VARCHAR2,p_number_of_files in Number)
621 IS
622
623
624 p_person_id per_all_people_f.person_id%type;
625 p_assignment_id per_all_assignments_f.assignment_id%type;
626 p_assignment_number per_all_assignments_f.assignment_number%type;
627 p_effective_start_date varchar2(10);
628 p_effective_end_date varchar2(10);
629 p_probation_period per_all_assignments_f.probation_period%type;
630 p_probation_units per_all_assignments_f.probation_unit%type;
631 p_organization_id per_all_assignments_f.organization_id%type;
632 p_job_id per_all_assignments_f.job_id%type;
633 p_position_id per_all_assignments_f.position_id%type;
634 p_assignment_status_type_id per_all_assignments_f.assignment_status_type_id%type;
635 p_location_id per_all_assignments_f.location_id%type;
636 p_employment_category per_all_assignments_f.employment_category%type;
637 p_business_group_id per_all_assignments_f.business_group_id%type;
638 p_normal_hours per_all_assignments_f.normal_hours%type;
639 p_frequency per_all_assignments_f.frequency%type;
640 p_grade_id per_all_assignments_f.grade_id%type;
641 p_supervisor_id per_all_assignments_f.supervisor_id%type;
642 p_act_termn_date varchar2(10);
643 p_final_prcs_date varchar2(10);
644 p_primary_flag per_all_assignments_f.primary_flag%type;
645
646 l_record_counter Number :=0;
647 l_file_counter Number :=0;
648 r Number :=1;
649 number_of_files Number := p_number_of_files;
650 l_file_path Varchar2(500) := null;
651 l_file_name_part Varchar2(200) := null;
652 l_location_exception Exception;
653 l_file_pointer utl_file.file_type;
654 l_break_count Number;
655
656
657 /*Cursor to fetch the workforce details*/
658
659 cursor csr_wkfrc_initial_load is
660 SELECT
661 pas.person_id,
662 pas.assignment_id,
663 pas.assignment_number,
664 to_char(pas.effective_start_date,'YYYY-MM-DD'),
665 to_char(pas.effective_end_date,'YYYY-MM-DD'),
666 pas.probation_period,
667 pas.probation_unit,
668 pas.organization_id,
669 pas.job_id,
670 pas.position_id,
671 pas.assignment_status_type_id,
672 pas.location_id,
673 pas.employment_category,
674 pas.business_group_id,
675 pas.normal_hours,
676 pas.frequency,
677 pas.grade_id,
678 pas.supervisor_id,
679
680 case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date) then
681 to_char(pos.final_process_date,'YYYY-MM-DD')
682 when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.final_process_date,'YYYY-MM-DD') end "final_prcs_date",
683
684 case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date)
685 then to_char(pos.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD')
686 when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD') end "act_termn_date",
687
688 primary_flag
689
690 FROM
691 per_all_assignments_f pas,
692 per_periods_of_service pos,
693 per_periods_of_placement pop
694 WHERE pas.person_id = pop.person_id (+)
695 AND pas.person_id = pos.person_id (+)
696 order by pas.business_group_id,pas.assignment_id,pas.effective_start_date;
697
698
699 begin
700
701
702 FND_FILE.NEW_LINE(FND_FILE.log, 1);
703 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
704 FND_FILE.put_line(fnd_file.log,'Workforce Initial Load Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
705
706 If number_of_files is not null
707 Then
708 r :=0;
709 For rec in csr_wkfrc_initial_load
710 Loop
711 r := r+1;
712 End Loop;
713
714 --If r >0 then total_record_count := r; End If;
715
716 If r < number_of_files Then
717 FND_FILE.put_line(fnd_file.log,'No. of files is more than the Number of total records');
718 End If;
719 Else
720 number_of_files := 1;
721 r := 1;
722 End If;
723 FND_FILE.NEW_LINE(FND_FILE.log, 1);
724 FND_FILE.NEW_LINE(FND_FILE.log, 1);
725 FND_FILE.put_line(fnd_file.log,'Total number_of_files = '||number_of_files);
726 if (r > 1) then
727 FND_FILE.put_line(fnd_file.log,'Total Number of Records = '||r);
728 end if;
729 fnd_profile.get('UTL_FILE_OUT', l_file_path);
730 FND_FILE.put_line(fnd_file.log,'File Path = '||l_file_path);
731 l_file_name_part := fnd_global.conc_request_id;
732 FND_FILE.put_line(fnd_file.log,'File names start with : '||l_file_name_part);
733 if (l_file_path is not null)
734 Then
735
736 l_record_counter := 1;
737 l_file_counter := 1;
738 l_break_count := 0;
739
740 /*Generate the initial load extraction for workforce and the column delimiter used is to_char(400)*/
741 OPEN csr_wkfrc_initial_load;
742
743 LOOP
744 if ((l_record_counter = ((l_file_counter-1)*(TRUNC((r/number_of_files))))+1 ) )
745 Then
746 l_file_pointer := null;
747 l_file_pointer := UTL_FILE.FOPEN(l_file_path,l_file_name_part||'_'||l_file_counter||'.out','W',32767);
748 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out'||' - Opened.');
749 End If;
750
751 FETCH csr_wkfrc_initial_load
752 INTO p_person_id,p_assignment_id,p_assignment_number,p_effective_start_date,p_effective_end_date,
753 p_probation_period,p_probation_units,p_organization_id,p_job_id,p_position_id,p_assignment_status_type_id,
754 p_location_id,p_employment_category,p_business_group_id,p_normal_hours,p_frequency,p_grade_id,
755 p_supervisor_id ,p_act_termn_date, p_final_prcs_date,p_primary_flag;
756 EXIT WHEN csr_wkfrc_initial_load%NOTFOUND;
757
758
759 UTL_FILE.PUT_LINE(l_file_pointer,
760 p_business_group_id||fnd_global.local_chr(400)||
761 p_person_id||fnd_global.local_chr(400)||
762 p_assignment_id||fnd_global.local_chr(400)||
763 p_assignment_number||fnd_global.local_chr(400)||
764 p_effective_start_date||fnd_global.local_chr(400)||
765 p_effective_end_date||fnd_global.local_chr(400)||
766 p_organization_id||fnd_global.local_chr(400)||
767 p_probation_period||fnd_global.local_chr(400)||
768 p_probation_units||fnd_global.local_chr(400)||
769 p_job_id||fnd_global.local_chr(400)||
770 p_assignment_status_type_id||fnd_global.local_chr(400)||
771 p_location_id||fnd_global.local_chr(400)||
772 p_employment_category||fnd_global.local_chr(400)||
773 p_normal_hours||fnd_global.local_chr(400)||
774 p_frequency||fnd_global.local_chr(400)||
775 p_grade_id||fnd_global.local_chr(400)||
776 p_position_id||fnd_global.local_chr(400)||
777 p_supervisor_id||fnd_global.local_chr(400)||
778 p_act_termn_date||fnd_global.local_chr(400)||
779 p_final_prcs_date||fnd_global.local_chr(400)||
780 p_primary_flag||
781 fnd_global.local_chr(10)||fnd_global.local_chr(13));
782
783 if (l_record_counter = ((l_file_counter)*(TRUNC(r/number_of_files))) and l_file_counter <> number_of_files)
784 Then
785 If UTL_FILE.IS_OPEN(l_file_pointer)
786 Then
787 UTL_FILE.FCLOSE(l_file_pointer);
788
789 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out - closed.');
790 FND_FILE.put_line(fnd_file.log,'Number of records written into '||l_file_name_part||'_'||l_file_counter||'.out = '||(l_record_counter - l_break_count));
791 l_break_count := l_record_counter;
792 End If;
793 l_file_counter := l_file_counter + 1;
794 End If;
795 l_record_counter := l_record_counter + 1;
796
797 END Loop;
798 CLOSE csr_wkfrc_initial_load;
799
800 If UTL_FILE.IS_OPEN(l_file_pointer)
801 Then
802 UTL_FILE.FCLOSE(l_file_pointer);
803 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out - closed.');
804 FND_FILE.put_line(fnd_file.log,'Number of records written into '||l_file_name_part||'_'||l_file_counter||'.out = '||((l_record_counter - 1) - l_break_count));
805 End If;
806 FND_FILE.PUT_LINE(fnd_file.log,'Total Number of records written into '||l_file_counter||' files = '|| (l_record_counter - 1) );
807 Else
808 FND_FILE.NEW_LINE(FND_FILE.log, 1);
809 FND_FILE.put_line(fnd_file.log,'Profile option FND_FILE_OUT has null value. Please specify valid path');
810 raise l_location_exception;
811 End If;
812
813 FND_FILE.NEW_LINE(FND_FILE.log, 1);
814 FND_FILE.put_line(fnd_file.log,'Workforce Initial Load Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
815
816 EXCEPTION WHEN OTHERS THEN
817 errbuf := errbuf||SQLERRM;
818 retcode := '1';
819 FND_FILE.put_line(fnd_file.log, 'Error in Workforce Initial Load Extraction: '||SQLCODE);
820 FND_FILE.NEW_LINE(FND_FILE.log, 1);
821 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
822
823 END HR_WORKFORCE_INITIAL_LOAD;
824
825 /*Procedure to extract Workforce Initial Load Ends*/
826
827
828 /*Procedure to extract Person Initial Load Extraction Begins*/
829
830 PROCEDURE HR_PERSON_INITIAL_LOAD(errbuf OUT NOCOPY VARCHAR2
831 ,retcode OUT NOCOPY VARCHAR2,p_number_of_files in Number)
832 IS
833
834 p_person_id per_all_people_f.person_id%type;
835 p_business_group_id per_all_people_f.business_group_id%type;
836 p_legislation_code hr_organization_information.org_information9%type;
837 p_employee_number per_all_people_f.employee_number%type;
838 p_applicant_number per_all_people_f.applicant_number%type;
839 p_npw_number per_all_people_f.npw_number%type;
840 p_person_type_id per_all_people_f.person_type_id%type;
841 p_date_of_birth varchar2(10);
842 p_town_of_birth per_all_people_f.town_of_birth%type;
843 p_cntry_of_birth per_all_people_f.country_of_birth%type;
844 p_date_of_death varchar2(10);
845 p_orig_dt_of_hire varchar2(10);
846 p_eff_start_date varchar2(10);
847 p_eff_end_date varchar2(10);
848 p_sex per_all_people_f.sex%type;
849 p_full_name per_all_people_f.full_name%type;
850 p_suffix per_all_people_f.suffix%type;
851 p_title per_all_people_f.title%type;
852 p_last_name per_all_people_f.last_name%type;
853 p_first_name per_all_people_f.first_name%type;
854 p_middle_names per_all_people_f.middle_names%type;
855 p_nationality per_all_people_f.nationality%type;
856 p_national_identifier per_all_people_f.national_identifier%type;
857 p_email_address per_all_people_f.email_address%type;
858 p_national_id_label varchar2(200);
859
860 l_record_counter Number :=0;
861 l_file_counter Number :=0;
862 r Number :=1;
863 number_of_files Number := p_number_of_files;
864 l_file_path Varchar2(500) := null;
865 l_file_name_part Varchar2(200) := null;
866 l_location_exception Exception;
867 l_file_pointer utl_file.file_type;
868 l_break_count Number;
869
870 TYPE ADDRESS IS RECORD
871 (
872
873 p_address_type per_addresses.address_type%type,
874 p_address_style per_addresses.style%type,
875 p_adr_date_from varchar2(10),
876 p_adr_date_to varchar2(10),
877 p_country per_addresses.country%type,
878 p_addr_line1 per_addresses.address_line1%type,
879 p_addr_line2 per_addresses.address_line2%type,
880 p_addr_line3 per_addresses.address_line3%type,
881 p_twn_or_city per_addresses.town_or_city%type,
882 p_tel_number1 per_addresses.telephone_number_1%type,
883 p_region1 per_addresses.region_1%type,
884 p_region2 per_addresses.region_2%type,
885 p_postal_code per_addresses.postal_code%type,
886 p_primary_flag per_addresses.primary_flag%type);
887
888 TYPE address_record is table of ADDRESS index by binary_integer;
889
890 TYPE PHONE IS RECORD
891 (
892
893 p_phn_date_from varchar2(10),
894 p_phn_date_to varchar2(10),
895 p_phone_type per_phones.phone_type%type,
896 p_phone_no per_phones.phone_number%type);
897
898 TYPE phone_record is table of PHONE index by binary_integer;
899
900 p_addr_type address_record;
901 p_phn_type phone_record;
902
903
904 /*Cursor to fetch the person details*/
905
906 cursor csr_person_data is
907 SELECT ppf.person_id,
908 ppf.business_group_id,
909 (select org_information9 from
910 hr_organization_information where organization_id = ppf.business_group_id
911 and org_information_context = 'Business Group Information') LEGISLATION_CODE,
912 EMPLOYEE_NUMBER,
913 APPLICANT_NUMBER,
914 NPW_NUMBER,
915 PERSON_TYPE_ID ,
916 to_char(DATE_OF_BIRTH,'YYYY-MM-DD'),
917 TOWN_OF_BIRTH,
918 COUNTRY_OF_BIRTH,
919 to_char(DATE_OF_DEATH,'YYYY-MM-DD'),
920 to_char(ORIGINAL_DATE_OF_HIRE,'YYYY-MM-DD'),
921 to_char(EFFECTIVE_START_DATE,'YYYY-MM-DD'),
922 to_char(EFFECTIVE_END_DATE,'YYYY-MM-DD'),
923 SEX,
924 FULL_NAME,
925 SUFFIX,
926 TITLE,
927 LAST_NAME,
928 FIRST_NAME,
929 MIDDLE_NAMES,
930 NATIONALITY,
931 NATIONAL_IDENTIFIER,
932 EMAIL_ADDRESS,
933 (select message_text from fnd_new_messages where message_name = 'HR_NATIONAL_ID_NUMBER_'|| (select to_char(org_information9) from
934 hr_organization_information where organization_id = ppf.business_group_id
935 and org_information_context = 'Business Group Information')
936 and language_code = USERENV('LANG') )NATIONAL_IDENTIFIER_LABEL
937
938 FROM PER_ALL_PEOPLE_F ppf
939 order by ppf.person_id,ppf.effective_start_date;
940
941 Cursor Csr_Address_Data(P_Person_Id Number,P_Eff_St_Dt Date,P_Eff_End_Dt Date) Is
942 Select
943
944 Address_Type,
945 style,
946 To_Char(Date_From,'YYYY-MM-DD'),
947 To_Char(Date_To,'YYYY-MM-DD'),
948 Country,
949 Address_Line1,
950 Address_Line2,
951 Address_Line3,
952 Town_Or_City,
953 Telephone_Number_1,
954 Region_1,
955 Region_2,
956 Postal_Code,
957 Primary_Flag
958 FROM per_addresses
959 where person_id = p_person_id
960 and date_from between P_Eff_St_Dt and P_Eff_End_Dt
961 order by date_from;
962
963 Cursor csr_phone_data(P_Person_Id Number,P_Eff_St_Dt Date,P_Eff_End_Dt Date) Is
964 Select
965
966 to_char(ppn.date_from,'YYYY-MM-DD'),
967 to_char(ppn.date_to,'YYYY-MM-DD'),
968 PHONE_TYPE,
969 PHONE_NUMBER
970 FROM per_phones ppn
971 where ppn.PARENT_ID (+) = P_PERSON_ID
972 AND PPN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
973 AND DATE_FROM between P_Eff_St_Dt and P_Eff_End_Dt;
974
975
976 begin
977
978 FND_FILE.NEW_LINE(FND_FILE.log, 1);
979 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
980 FND_FILE.put_line(fnd_file.log,'Person Initial Load Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
981
982 /*Generate the initial load extraction for location and the column delimiter used is to_char(400)*/
983 If number_of_files is not null
984 Then
985 r :=0;
986 For rec in csr_person_data
987 Loop
988 r := r+1;
989 End Loop;
990
991 --If r >0 then total_record_count := r; End If;
992
993 If r < number_of_files Then
994 FND_FILE.put_line(fnd_file.log,'No. of files is more than the Number of total records');
995 End If;
996 Else
997 number_of_files := 1;
998 r := 1;
999 End If;
1000 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1001 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1002 FND_FILE.put_line(fnd_file.log,'Total number_of_files = '||number_of_files);
1003 if (r > 1) then
1004 FND_FILE.put_line(fnd_file.log,'Total Number of Records = '||r);
1005 end if;
1006 fnd_profile.get('UTL_FILE_OUT', l_file_path);
1007 FND_FILE.put_line(fnd_file.log,'File Path = '||l_file_path);
1008 l_file_name_part := fnd_global.conc_request_id;
1009 FND_FILE.put_line(fnd_file.log,'File names start with : '||l_file_name_part);
1010 if (l_file_path is not null)
1011 Then
1012
1013 /*Generate the initial load extraction for location and the column delimiter used is to_char(400)*/
1014 l_record_counter := 1;
1015 l_file_counter := 1;
1016 l_break_count := 0;
1017 /*Generate the initial load extraction for person and the column delimiter used is to_char(400)*/
1018 OPEN csr_person_data;
1019 loop
1020
1021 if ((l_record_counter = ((l_file_counter-1)*(TRUNC((r/number_of_files))))+1 ) )
1022 Then
1023 l_file_pointer := null;
1024 l_file_pointer := UTL_FILE.FOPEN(l_file_path,l_file_name_part||'_'||l_file_counter||'.out','W',32767);
1025 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out'||' - Opened.');
1026 End If;
1027
1028 fetch csr_person_data into p_person_id,p_business_group_id,p_legislation_code,p_employee_number,p_applicant_number,p_npw_number,p_person_type_id,
1029 p_date_of_birth,p_town_of_birth,p_cntry_of_birth,p_date_of_death,p_orig_dt_of_hire,p_eff_start_date,
1030 p_eff_end_date,p_sex,p_full_name,p_suffix,p_title,p_last_name,p_first_name,p_middle_names,
1031 p_nationality,p_national_identifier,p_email_address,p_national_id_label;
1032 exit when csr_person_data%notfound;
1033
1034 open csr_address_data(p_person_id,to_date(p_eff_start_date,'YYYY-MM-DD'),to_date(p_eff_end_date,'YYYY-MM-DD'));
1035 fetch csr_address_data bulk collect into p_addr_type;
1036 close csr_address_data;
1037
1038 open csr_phone_data(p_person_id,to_date(p_eff_start_date,'YYYY-MM-DD'),to_date(p_eff_end_date,'YYYY-MM-DD'));
1039 fetch csr_phone_data bulk collect into p_phn_type;
1040 close csr_phone_data;
1041
1042 if p_addr_type.count > 0 and p_phn_type.count > 0 and p_addr_type.count >= p_phn_type.count
1043 then
1044
1045 for k in p_addr_type.first .. p_addr_type.last
1046 loop
1047 if k <= p_phn_type.count
1048 then
1049
1050 UTL_FILE.put_line(l_file_pointer,
1051 p_business_group_id||fnd_global.local_chr(400)||
1052 p_person_id||fnd_global.local_chr(400)||
1053 p_legislation_code||fnd_global.local_chr(400)||
1054 p_employee_number||fnd_global.local_chr(400)||
1055 p_applicant_number||fnd_global.local_chr(400)||
1056 p_npw_number||fnd_global.local_chr(400)||
1057 p_person_type_id||fnd_global.local_chr(400)||
1058 p_date_of_birth||fnd_global.local_chr(400)||
1059 p_town_of_birth||fnd_global.local_chr(400)||
1060 p_cntry_of_birth||fnd_global.local_chr(400)||
1061 p_date_of_death||fnd_global.local_chr(400)||
1062 p_orig_dt_of_hire||fnd_global.local_chr(400)||
1063 p_eff_start_date||fnd_global.local_chr(400)||
1064 p_eff_end_date||fnd_global.local_chr(400)||
1065 p_sex||fnd_global.local_chr(400)||
1066 p_full_name||fnd_global.local_chr(400)||
1067 p_suffix||fnd_global.local_chr(400)||
1068 p_title||fnd_global.local_chr(400)||
1069 p_last_name||fnd_global.local_chr(400)||
1070 p_first_name||fnd_global.local_chr(400)||
1071 p_middle_names||fnd_global.local_chr(400)||
1072 p_nationality||fnd_global.local_chr(400)||
1073 p_national_identifier||fnd_global.local_chr(400)||
1074 p_email_address||fnd_global.local_chr(400)||
1075 p_addr_type(k).p_address_type||fnd_global.local_chr(400)||
1076 p_addr_type(k).p_adr_date_from||fnd_global.local_chr(400)||
1077 p_addr_type(k).p_adr_date_to||fnd_global.local_chr(400)||
1078 p_addr_type(k).p_address_style||fnd_global.local_chr(400)||
1079 p_addr_type(k).p_country||fnd_global.local_chr(400)||
1080 p_addr_type(k).p_addr_line1||fnd_global.local_chr(400)||
1081 p_addr_type(k).p_addr_line2||fnd_global.local_chr(400)||
1082 p_addr_type(k).p_addr_line3||fnd_global.local_chr(400)||
1083 p_addr_type(k).p_twn_or_city||fnd_global.local_chr(400)||
1084 p_addr_type(k).p_tel_number1||fnd_global.local_chr(400)||
1085 p_addr_type(k).p_region1||fnd_global.local_chr(400)||
1086 p_addr_type(k).p_region2||fnd_global.local_chr(400)||
1087 p_addr_type(k).p_postal_code ||fnd_global.local_chr(400)||
1088 p_addr_type(k).p_primary_flag ||fnd_global.local_chr(400)||
1089 p_phn_type(k).p_phn_date_from||fnd_global.local_chr(400)||
1090 p_phn_type(k).p_phn_date_to||fnd_global.local_chr(400)||
1091 p_phn_type(k).p_phone_type ||fnd_global.local_chr(400)||
1092 p_phn_type(k).p_phone_no||fnd_global.local_chr(400)||
1093 p_national_id_label||fnd_global.local_chr(400)||
1094 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
1095 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
1096 fnd_global.local_chr(10)||fnd_global.local_chr(13));
1097
1098
1099 else
1100
1101 UTL_FILE.put_line(l_file_pointer,
1102 p_business_group_id||fnd_global.local_chr(400)||
1103 p_person_id||fnd_global.local_chr(400)||
1104 p_legislation_code||fnd_global.local_chr(400)||
1105 p_employee_number||fnd_global.local_chr(400)||
1106 p_applicant_number||fnd_global.local_chr(400)||
1107 p_npw_number||fnd_global.local_chr(400)||
1108 p_person_type_id||fnd_global.local_chr(400)||
1109 p_date_of_birth||fnd_global.local_chr(400)||
1110 p_town_of_birth||fnd_global.local_chr(400)||
1111 p_cntry_of_birth||fnd_global.local_chr(400)||
1112 p_date_of_death||fnd_global.local_chr(400)||
1113 p_orig_dt_of_hire||fnd_global.local_chr(400)||
1114 p_eff_start_date||fnd_global.local_chr(400)||
1115 p_eff_end_date||fnd_global.local_chr(400)||
1116 p_sex||fnd_global.local_chr(400)||
1117 p_full_name||fnd_global.local_chr(400)||
1118 p_suffix||fnd_global.local_chr(400)||
1119 p_title||fnd_global.local_chr(400)||
1120 p_last_name||fnd_global.local_chr(400)||
1121 p_first_name||fnd_global.local_chr(400)||
1122 p_middle_names||fnd_global.local_chr(400)||
1123 p_nationality||fnd_global.local_chr(400)||
1124 p_national_identifier||fnd_global.local_chr(400)||
1125 p_email_address||fnd_global.local_chr(400)||
1126 p_addr_type(k).p_address_type||fnd_global.local_chr(400)||
1127 p_addr_type(k).p_adr_date_from||fnd_global.local_chr(400)||
1128 p_addr_type(k).p_adr_date_to||fnd_global.local_chr(400)||
1129 p_addr_type(k).p_address_style||fnd_global.local_chr(400)||
1130 p_addr_type(k).p_country||fnd_global.local_chr(400)||
1131 p_addr_type(k).p_addr_line1||fnd_global.local_chr(400)||
1132 p_addr_type(k).p_addr_line2||fnd_global.local_chr(400)||
1133 p_addr_type(k).p_addr_line3||fnd_global.local_chr(400)||
1134 p_addr_type(k).p_twn_or_city||fnd_global.local_chr(400)||
1135 p_addr_type(k).p_tel_number1||fnd_global.local_chr(400)||
1136 p_addr_type(k).p_region1||fnd_global.local_chr(400)||
1137 p_addr_type(k).p_region2||fnd_global.local_chr(400)||
1138 p_addr_type(k).p_postal_code ||fnd_global.local_chr(400)||
1139 p_addr_type(k).p_primary_flag ||fnd_global.local_chr(400)||
1140 fnd_global.local_chr(400)||
1141 fnd_global.local_chr(400)||
1142 fnd_global.local_chr(400)||
1143 fnd_global.local_chr(400)||
1144 p_national_id_label||fnd_global.local_chr(400)||
1145 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
1146 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
1147 fnd_global.local_chr(10)||fnd_global.local_chr(13));
1148
1149
1150 end if;
1151 end loop;
1152 end if;
1153
1154 if p_addr_type.count > 0 and p_phn_type.count > 0 and p_addr_type.count < p_phn_type.count
1155 then
1156
1157 for k in p_phn_type.first .. p_phn_type.last
1158 loop
1159 if k <= p_addr_type.count
1160 then
1161
1162 UTL_FILE.put_line(l_file_pointer,
1163 p_business_group_id||fnd_global.local_chr(400)||
1164 p_person_id||fnd_global.local_chr(400)||
1165 p_legislation_code||fnd_global.local_chr(400)||
1166 p_employee_number||fnd_global.local_chr(400)||
1167 p_applicant_number||fnd_global.local_chr(400)||
1168 p_npw_number||fnd_global.local_chr(400)||
1169 p_person_type_id||fnd_global.local_chr(400)||
1170 p_date_of_birth||fnd_global.local_chr(400)||
1171 p_town_of_birth||fnd_global.local_chr(400)||
1172 p_cntry_of_birth||fnd_global.local_chr(400)||
1173 p_date_of_death||fnd_global.local_chr(400)||
1174 p_orig_dt_of_hire||fnd_global.local_chr(400)||
1175 p_eff_start_date||fnd_global.local_chr(400)||
1176 p_eff_end_date||fnd_global.local_chr(400)||
1177 p_sex||fnd_global.local_chr(400)||
1178 p_full_name||fnd_global.local_chr(400)||
1179 p_suffix||fnd_global.local_chr(400)||
1180 p_title||fnd_global.local_chr(400)||
1181 p_last_name||fnd_global.local_chr(400)||
1182 p_first_name||fnd_global.local_chr(400)||
1183 p_middle_names||fnd_global.local_chr(400)||
1184 p_nationality||fnd_global.local_chr(400)||
1185 p_national_identifier||fnd_global.local_chr(400)||
1186 p_email_address||fnd_global.local_chr(400)||
1187 p_addr_type(k).p_address_type||fnd_global.local_chr(400)||
1188 p_addr_type(k).p_adr_date_from||fnd_global.local_chr(400)||
1189 p_addr_type(k).p_adr_date_to||fnd_global.local_chr(400)||
1190 p_addr_type(k).p_address_style||fnd_global.local_chr(400)||
1191 p_addr_type(k).p_country||fnd_global.local_chr(400)||
1192 p_addr_type(k).p_addr_line1||fnd_global.local_chr(400)||
1193 p_addr_type(k).p_addr_line2||fnd_global.local_chr(400)||
1194 p_addr_type(k).p_addr_line3||fnd_global.local_chr(400)||
1195 p_addr_type(k).p_twn_or_city||fnd_global.local_chr(400)||
1196 p_addr_type(k).p_tel_number1||fnd_global.local_chr(400)||
1197 p_addr_type(k).p_region1||fnd_global.local_chr(400)||
1198 p_addr_type(k).p_region2||fnd_global.local_chr(400)||
1199 p_addr_type(k).p_postal_code ||fnd_global.local_chr(400)||
1200 p_addr_type(k).p_primary_flag ||fnd_global.local_chr(400)||
1201 p_phn_type(k).p_phn_date_from||fnd_global.local_chr(400)||
1202 p_phn_type(k).p_phn_date_to||fnd_global.local_chr(400)||
1203 p_phn_type(k).p_phone_type ||fnd_global.local_chr(400)||
1204 p_phn_type(k).p_phone_no||fnd_global.local_chr(400)||
1205 p_national_id_label||fnd_global.local_chr(400)||
1206 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
1207 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
1208 fnd_global.local_chr(10)||fnd_global.local_chr(13));
1209
1210 else
1211
1212 UTL_FILE.put_line(l_file_pointer,
1213 p_business_group_id||fnd_global.local_chr(400)||
1214 p_person_id||fnd_global.local_chr(400)||
1215 p_legislation_code||fnd_global.local_chr(400)||
1216 p_employee_number||fnd_global.local_chr(400)||
1217 p_applicant_number||fnd_global.local_chr(400)||
1218 p_npw_number||fnd_global.local_chr(400)||
1219 p_person_type_id||fnd_global.local_chr(400)||
1220 p_date_of_birth||fnd_global.local_chr(400)||
1221 p_town_of_birth||fnd_global.local_chr(400)||
1222 p_cntry_of_birth||fnd_global.local_chr(400)||
1223 p_date_of_death||fnd_global.local_chr(400)||
1224 p_orig_dt_of_hire||fnd_global.local_chr(400)||
1225 p_eff_start_date||fnd_global.local_chr(400)||
1226 p_eff_end_date||fnd_global.local_chr(400)||
1227 p_sex||fnd_global.local_chr(400)||
1228 p_full_name||fnd_global.local_chr(400)||
1229 p_suffix||fnd_global.local_chr(400)||
1230 p_title||fnd_global.local_chr(400)||
1231 p_last_name||fnd_global.local_chr(400)||
1232 p_first_name||fnd_global.local_chr(400)||
1233 p_middle_names||fnd_global.local_chr(400)||
1234 p_nationality||fnd_global.local_chr(400)||
1235 p_national_identifier||fnd_global.local_chr(400)||
1236 p_email_address||fnd_global.local_chr(400)||
1237 fnd_global.local_chr(400)||
1238 fnd_global.local_chr(400)||
1239 fnd_global.local_chr(400)||
1240 fnd_global.local_chr(400)||
1241 fnd_global.local_chr(400)||
1242 fnd_global.local_chr(400)||
1243 fnd_global.local_chr(400)||
1244 fnd_global.local_chr(400)||
1245 fnd_global.local_chr(400)||
1246 fnd_global.local_chr(400)||
1247 fnd_global.local_chr(400)||
1248 fnd_global.local_chr(400)||
1249 fnd_global.local_chr(400)||
1250 fnd_global.local_chr(400)||
1251 p_phn_type(k).p_phn_date_from||fnd_global.local_chr(400)||
1252 p_phn_type(k).p_phn_date_to||fnd_global.local_chr(400)||
1253 p_phn_type(k).p_phone_type ||fnd_global.local_chr(400)||
1254 p_phn_type(k).p_phone_no||fnd_global.local_chr(400)||
1255 p_national_id_label||fnd_global.local_chr(400)||
1256 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
1257 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
1258 fnd_global.local_chr(10)||fnd_global.local_chr(13));
1259 end if;
1260 end loop;
1261 end if;
1262
1263 if p_phn_type.count > 0 and p_addr_type.count = 0
1264 then
1265 for k in p_phn_type.first .. p_phn_type.last
1266 loop
1267
1268 UTL_FILE.put_line(l_file_pointer,
1269 p_business_group_id||fnd_global.local_chr(400)||
1270 p_person_id||fnd_global.local_chr(400)||
1271 p_legislation_code||fnd_global.local_chr(400)||
1272 p_employee_number||fnd_global.local_chr(400)||
1273 p_applicant_number||fnd_global.local_chr(400)||
1274 p_npw_number||fnd_global.local_chr(400)||
1275 p_person_type_id||fnd_global.local_chr(400)||
1276 p_date_of_birth||fnd_global.local_chr(400)||
1277 p_town_of_birth||fnd_global.local_chr(400)||
1278 p_cntry_of_birth||fnd_global.local_chr(400)||
1279 p_date_of_death||fnd_global.local_chr(400)||
1280 p_orig_dt_of_hire||fnd_global.local_chr(400)||
1281 p_eff_start_date||fnd_global.local_chr(400)||
1282 p_eff_end_date||fnd_global.local_chr(400)||
1283 p_sex||fnd_global.local_chr(400)||
1284 p_full_name||fnd_global.local_chr(400)||
1285 p_suffix||fnd_global.local_chr(400)||
1286 p_title||fnd_global.local_chr(400)||
1287 p_last_name||fnd_global.local_chr(400)||
1288 p_first_name||fnd_global.local_chr(400)||
1289 p_middle_names||fnd_global.local_chr(400)||
1290 p_nationality||fnd_global.local_chr(400)||
1291 p_national_identifier||fnd_global.local_chr(400)||
1292 p_email_address||fnd_global.local_chr(400)||
1293 fnd_global.local_chr(400)||
1294 fnd_global.local_chr(400)||
1295 fnd_global.local_chr(400)||
1296 fnd_global.local_chr(400)||
1297 fnd_global.local_chr(400)||
1298 fnd_global.local_chr(400)||
1299 fnd_global.local_chr(400)||
1300 fnd_global.local_chr(400)||
1301 fnd_global.local_chr(400)||
1302 fnd_global.local_chr(400)||
1303 fnd_global.local_chr(400)||
1304 fnd_global.local_chr(400)||
1305 fnd_global.local_chr(400)||
1306 fnd_global.local_chr(400)||
1307 p_phn_type(k).p_phn_date_from||fnd_global.local_chr(400)||
1308 p_phn_type(k).p_phn_date_to||fnd_global.local_chr(400)||
1309 p_phn_type(k).p_phone_type ||fnd_global.local_chr(400)||
1310 p_phn_type(k).p_phone_no||fnd_global.local_chr(400)||
1311 p_national_id_label||fnd_global.local_chr(400)||
1312 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
1313 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
1314 fnd_global.local_chr(10)||fnd_global.local_chr(13));
1315
1316 end loop;
1317 end if;
1318
1319 if p_addr_type.count > 0 and p_phn_type.count = 0
1320 then
1321 for k in p_addr_type.first .. p_addr_type.last
1322 loop
1323
1324 UTL_FILE.put_line(l_file_pointer,
1325 p_business_group_id||fnd_global.local_chr(400)||
1326 p_person_id||fnd_global.local_chr(400)||
1327 p_legislation_code||fnd_global.local_chr(400)||
1328 p_employee_number||fnd_global.local_chr(400)||
1329 p_applicant_number||fnd_global.local_chr(400)||
1330 p_npw_number||fnd_global.local_chr(400)||
1331 p_person_type_id||fnd_global.local_chr(400)||
1332 p_date_of_birth||fnd_global.local_chr(400)||
1333 p_town_of_birth||fnd_global.local_chr(400)||
1334 p_cntry_of_birth||fnd_global.local_chr(400)||
1335 p_date_of_death||fnd_global.local_chr(400)||
1336 p_orig_dt_of_hire||fnd_global.local_chr(400)||
1337 p_eff_start_date||fnd_global.local_chr(400)||
1338 p_eff_end_date||fnd_global.local_chr(400)||
1339 p_sex||fnd_global.local_chr(400)||
1340 p_full_name||fnd_global.local_chr(400)||
1341 p_suffix||fnd_global.local_chr(400)||
1342 p_title||fnd_global.local_chr(400)||
1343 p_last_name||fnd_global.local_chr(400)||
1344 p_first_name||fnd_global.local_chr(400)||
1345 p_middle_names||fnd_global.local_chr(400)||
1346 p_nationality||fnd_global.local_chr(400)||
1347 p_national_identifier||fnd_global.local_chr(400)||
1348 p_email_address||fnd_global.local_chr(400)||
1349 p_addr_type(k).p_address_type||fnd_global.local_chr(400)||
1350 p_addr_type(k).p_adr_date_from||fnd_global.local_chr(400)||
1351 p_addr_type(k).p_adr_date_to||fnd_global.local_chr(400)||
1352 p_addr_type(k).p_address_style||fnd_global.local_chr(400)||
1353 p_addr_type(k).p_country||fnd_global.local_chr(400)||
1354 p_addr_type(k).p_addr_line1||fnd_global.local_chr(400)||
1355 p_addr_type(k).p_addr_line2||fnd_global.local_chr(400)||
1356 p_addr_type(k).p_addr_line3||fnd_global.local_chr(400)||
1357 p_addr_type(k).p_twn_or_city||fnd_global.local_chr(400)||
1358 p_addr_type(k).p_tel_number1||fnd_global.local_chr(400)||
1359 p_addr_type(k).p_region1||fnd_global.local_chr(400)||
1360 p_addr_type(k).p_region2||fnd_global.local_chr(400)||
1361 p_addr_type(k).p_postal_code ||fnd_global.local_chr(400)||
1362 p_addr_type(k).p_primary_flag ||fnd_global.local_chr(400)||
1363 fnd_global.local_chr(400)||
1364 fnd_global.local_chr(400)||
1365 fnd_global.local_chr(400)||
1366 fnd_global.local_chr(400)||
1367 p_national_id_label||fnd_global.local_chr(400)||
1368 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
1369 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
1370 fnd_global.local_chr(10)||fnd_global.local_chr(13));
1371
1372 end loop;
1373 end if;
1374
1375 /*Fix for bug 7650158 starts here*/
1376 if p_addr_type.count = 0 and p_phn_type.count = 0
1377 then
1378
1379
1380 UTL_FILE.put_line(l_file_pointer,
1381 p_business_group_id||fnd_global.local_chr(400)||
1382 p_person_id||fnd_global.local_chr(400)||
1383 p_legislation_code||fnd_global.local_chr(400)||
1384 p_employee_number||fnd_global.local_chr(400)||
1385 p_applicant_number||fnd_global.local_chr(400)||
1386 p_npw_number||fnd_global.local_chr(400)||
1387 p_person_type_id||fnd_global.local_chr(400)||
1388 p_date_of_birth||fnd_global.local_chr(400)||
1389 p_town_of_birth||fnd_global.local_chr(400)||
1390 p_cntry_of_birth||fnd_global.local_chr(400)||
1391 p_date_of_death||fnd_global.local_chr(400)||
1392 p_orig_dt_of_hire||fnd_global.local_chr(400)||
1393 p_eff_start_date||fnd_global.local_chr(400)||
1394 p_eff_end_date||fnd_global.local_chr(400)||
1395 p_sex||fnd_global.local_chr(400)||
1396 p_full_name||fnd_global.local_chr(400)||
1397 p_suffix||fnd_global.local_chr(400)||
1398 p_title||fnd_global.local_chr(400)||
1399 p_last_name||fnd_global.local_chr(400)||
1400 p_first_name||fnd_global.local_chr(400)||
1401 p_middle_names||fnd_global.local_chr(400)||
1402 p_nationality||fnd_global.local_chr(400)||
1403 p_national_identifier||fnd_global.local_chr(400)||
1404 p_email_address||fnd_global.local_chr(400)||
1405 fnd_global.local_chr(400)||
1406 fnd_global.local_chr(400)||
1407 fnd_global.local_chr(400)||
1408 fnd_global.local_chr(400)||
1409 fnd_global.local_chr(400)||
1410 fnd_global.local_chr(400)||
1411 fnd_global.local_chr(400)||
1412 fnd_global.local_chr(400)||
1413 fnd_global.local_chr(400)||
1414 fnd_global.local_chr(400)||
1415 fnd_global.local_chr(400)||
1416 fnd_global.local_chr(400)||
1417 fnd_global.local_chr(400)||
1418 fnd_global.local_chr(400)||
1419 fnd_global.local_chr(400)||
1420 fnd_global.local_chr(400)||
1421 fnd_global.local_chr(400)||
1422 fnd_global.local_chr(400)||
1423 p_national_id_label||fnd_global.local_chr(400)||
1424 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
1425 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
1426 fnd_global.local_chr(10)||fnd_global.local_chr(13));
1427
1428
1429 end if;
1430 /*Fix for bug 7650158 ends here*/
1431
1432 if (l_record_counter = ((l_file_counter)*(TRUNC(r/number_of_files))) and l_file_counter <> number_of_files)
1433 Then
1434 If UTL_FILE.IS_OPEN(l_file_pointer)
1435 Then
1436 UTL_FILE.FCLOSE(l_file_pointer);
1437
1438 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out - closed.');
1439 FND_FILE.put_line(fnd_file.log,'Number of records written into '||l_file_name_part||'_'||l_file_counter||'.out = '||(l_record_counter - l_break_count));
1440 l_break_count := l_record_counter;
1441 End If;
1442 l_file_counter := l_file_counter + 1;
1443 End If;
1444 l_record_counter := l_record_counter + 1;
1445
1446 END Loop;
1447 close csr_person_data;
1448
1449 If UTL_FILE.IS_OPEN(l_file_pointer)
1450 Then
1451 UTL_FILE.FCLOSE(l_file_pointer);
1452 FND_FILE.put_line(fnd_file.log,'File - '||l_file_counter||' = '||l_file_name_part||'_'||l_file_counter||'.out - closed.');
1453 FND_FILE.put_line(fnd_file.log,'Number of records written into '||l_file_name_part||'_'||l_file_counter||'.out = '||((l_record_counter - 1) - l_break_count));
1454 End If;
1455 FND_FILE.PUT_LINE(fnd_file.log,'Total Number of records written into '||l_file_counter||' files = '|| (l_record_counter - 1) );
1456 Else
1457 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1458 FND_FILE.put_line(fnd_file.log,'Profile option FND_FILE_OUT has null value. Please specify valid path');
1459 raise l_location_exception;
1460 End If;
1461
1462 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1463 FND_FILE.put_line(fnd_file.log,'Person Initial Load Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1464
1465 EXCEPTION WHEN OTHERS THEN
1466 errbuf := errbuf||SQLERRM;
1467 retcode := '1';
1468 FND_FILE.put_line(fnd_file.log, 'Error in Person Initial Load Extraction: '||SQLCODE);
1469 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1470 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
1471
1472 END HR_PERSON_INITIAL_LOAD;
1473
1474 /*Procedure to extract Person Initial Load Ends*/
1475
1476
1477 /*Common Procedure called from concurrent program begins*/
1478 procedure HR_INITIAL_LOAD (ERRBUF OUT NOCOPY varchar2,
1479 RETCODE OUT NOCOPY number,
1480 p_process_name in varchar2,
1481 p_no_of_files in Number)
1482 is
1483 begin
1484
1485 if p_process_name = 'LOCATION_FULL_SYNCH' then
1486 HR_HRHD_INITIAL_LOAD.HR_LOCATION_INITIAL_LOAD(ERRBUF,RETCODE,p_no_of_files);
1487
1488 elsif p_process_name = 'JOBCODE_FULL_SYNCH' then
1489 HR_HRHD_INITIAL_LOAD.HR_JOBCODE_INITIAL_LOAD(ERRBUF,RETCODE,p_no_of_files);
1490
1491 elsif p_process_name = 'ORGANIZATION_FULL_SYNCH' then
1492 HR_HRHD_INITIAL_LOAD.HR_ORGANIZATION_INITIAL_LOAD(ERRBUF,RETCODE,p_no_of_files);
1493
1494 elsif p_process_name = 'WORKFORCE_FULL_SYNCH' then
1495 HR_HRHD_INITIAL_LOAD.HR_WORKFORCE_INITIAL_LOAD(ERRBUF,RETCODE,p_no_of_files);
1496
1497 elsif p_process_name = 'PERSON_FULL_SYNCH' then
1498 HR_HRHD_INITIAL_LOAD.HR_PERSON_INITIAL_LOAD(ERRBUF,RETCODE,p_no_of_files);
1499 end if;
1500 end HR_INITIAL_LOAD;
1501 /*Common Procedure called from concurrent program ends*/
1502
1503 /* Function for encrypting */
1504
1505 function hr_hrhd_encrypt(p_data VARCHAR2 ) RETURN RAW is
1506 key_bytes_raw RAW (128); -- stores 256-bit encryption key
1507 encryption_type PLS_INTEGER := FND_CRYPTO.DES3_CBC_PKCS5;
1508 --DBMS_CRYPTO.HASH_MD5+ DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5; -- total encryption type
1509 BEGIN
1510
1511 key_bytes_raw := UTL_I18N.STRING_TO_RAW(fnd_vault.get('HRHD','CRYPT_KEY'));
1512
1513 RETURN(FND_CRYPTO.ENCRYPT(plaintext => utl_raw.cast_to_raw (p_data),
1514 crypto_type => encryption_type, key => key_bytes_raw ));
1515
1516 END;
1517
1518 /* Function for decrypting */
1519
1520 function hr_hrhd_decrypt(p_data RAW ) RETURN VARCHAR2 is
1521 key_bytes_raw RAW (128); -- stores 256-bit encryption key
1522 encryption_type PLS_INTEGER := FND_CRYPTO.DES3_CBC_PKCS5 ; -- total encryption type
1523 --DBMS_CRYPTO.HASH_MD5+ DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
1524 BEGIN
1525
1526 key_bytes_raw := UTL_I18N.STRING_TO_RAW(fnd_vault.get('HRHD','CRYPT_KEY'));
1527
1528 RETURN(UTL_RAW.CAST_TO_VARCHAR2(FND_CRYPTO.DECRYPT(cryptext => p_data,
1529 crypto_type => encryption_type, key => key_bytes_raw )));
1530
1531 END;
1532
1533
1534 end HR_HRHD_INITIAL_LOAD;
1535