[Home] [Help]
PACKAGE BODY: APPS.HR_HRHD_INITIAL_LOAD
Source
1 PACKAGE BODY HR_HRHD_INITIAL_LOAD as
2 /* $Header: perhdfsyn.pkb 120.3.12010000.9 2008/12/22 08:21:43 sathkris noship $ */
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 is
10
11 p_bg_id hr_locations_all.business_group_id%type;
12 p_loc_id hr_locations_all.LOCATION_ID%type;
13 p_active_date varchar2(10);
14 p_effecive_status varchar2(10);
15 p_lang_code varchar2(10);
16 p_loc_desc hr_locations_all.DESCRIPTION%type;
17 p_loc_style hr_locations_all.STYLE%type;
18 p_add_line_1 hr_locations_all.ADDRESS_LINE_1%type;
19 p_add_line_2 hr_locations_all.ADDRESS_LINE_2%type;
20 p_add_line_3 hr_locations_all.ADDRESS_LINE_3%type;
21 p_town_or_city hr_locations_all.TOWN_OR_CITY%type;
22 p_country hr_locations_all.COUNTRY%type;
23 p_postal_code hr_locations_all.POSTAL_CODE%type;
24 p_region_1 hr_locations_all.REGION_1%type;
25 p_region_2 hr_locations_all.REGION_2%type;
26 p_region_3 hr_locations_all.REGION_3%type;
27 p_tel_no_1 hr_locations_all.TELEPHONE_NUMBER_1%type;
28 p_tel_no_2 hr_locations_all.TELEPHONE_NUMBER_2%type;
29 p_tel_no_3 hr_locations_all.TELEPHONE_NUMBER_3%type;
30 p_loc_info_13 hr_locations_all.LOC_INFORMATION13%type;
31 p_loc_info_14 hr_locations_all.LOC_INFORMATION14%type;
32 p_loc_info_15 hr_locations_all.LOC_INFORMATION15%type;
33 p_loc_info_16 hr_locations_all.LOC_INFORMATION16%type;
34 p_loc_info_17 hr_locations_all.LOC_INFORMATION17%type;
35 p_loc_info_18 hr_locations_all.LOC_INFORMATION18%type;
36 p_loc_info_19 hr_locations_all.LOC_INFORMATION19%type;
37 p_loc_info_20 hr_locations_all.LOC_INFORMATION20%type;
38
39
40 /*CURSOR TO FETCH THE LOCATION DETAILS*/
41 cursor CSR_LOC_INITIAL_LOAD is
42 select hloc.BUSINESS_GROUP_ID,
43 to_char(hloc.CREATION_DATE,'YYYY-MM-DD'),
44 'A' ,
45 hloc.LOCATION_ID,
46 tl.language,
47 tl.DESCRIPTION,
48 STYLE,
49 COUNTRY,
50 ADDRESS_LINE_1,
51 ADDRESS_LINE_2,
52 ADDRESS_LINE_3,
53 TOWN_OR_CITY,
54 REGION_1,
55 REGION_2,
56 REGION_3,
57 POSTAL_CODE,
58 TELEPHONE_NUMBER_1,
59 TELEPHONE_NUMBER_2,
60 TELEPHONE_NUMBER_3,
61 LOC_INFORMATION13,
62 LOC_INFORMATION14,
63 LOC_INFORMATION15,
64 LOC_INFORMATION16,
65 LOC_INFORMATION17,
66 LOC_INFORMATION18,
67 LOC_INFORMATION19,
68 LOC_INFORMATION20
69 from
70 hr_locations_all hloc,hr_locations_all_tl tl
71 where tl.location_id = hloc.location_id
72 and nvl(inactive_date,to_date('31/12/4712','DD/MM/YYYY')) > sysdate
73 union
74 select hloc.BUSINESS_GROUP_ID,
75 to_char(inactive_date,'YYYY-MM-DD'),
76 'I',
77 hloc.LOCATION_ID,
78 tl.language,
79 tl.DESCRIPTION,
80 STYLE,
81 COUNTRY,
82 ADDRESS_LINE_1,
83 ADDRESS_LINE_2,
84 ADDRESS_LINE_3,
85 TOWN_OR_CITY,
86 REGION_1,
87 REGION_2,
88 REGION_3,
89 POSTAL_CODE,
90 TELEPHONE_NUMBER_1,
91 TELEPHONE_NUMBER_2,
92 TELEPHONE_NUMBER_3,
93 LOC_INFORMATION13,
94 LOC_INFORMATION14,
95 LOC_INFORMATION15,
96 LOC_INFORMATION16,
97 LOC_INFORMATION17,
98 LOC_INFORMATION18,
99 LOC_INFORMATION19,
100 LOC_INFORMATION20
101 from
102 hr_locations_all hloc,hr_locations_all_tl tl
103 where tl.location_id = hloc.location_id
104 and inactive_date is not null
105 order by business_group_id,location_id ;
106
107 begin
108
109
110 FND_FILE.NEW_LINE(FND_FILE.log, 1);
111 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
112 FND_FILE.put_line(fnd_file.log,'Location Initial Load Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
113
114 /*Generate the initial load extraction for location and the column delimiter used is to_char(400)*/
115 open CSR_LOC_INITIAL_LOAD;
116 loop
117 fetch CSR_LOC_INITIAL_LOAD into p_bg_id,p_active_date,p_effecive_status,p_loc_id,p_lang_code,
118 p_loc_desc, p_loc_style , p_country, p_add_line_1, p_add_line_2, p_add_line_3,
119 p_town_or_city,p_region_1,p_region_2,p_region_3,p_postal_code,p_tel_no_1,p_tel_no_2 ,
120 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,
121 p_loc_info_19,p_loc_info_20;
122
123 exit when CSR_LOC_INITIAL_LOAD%NOTFOUND;
124
125
126 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_bg_id||
127 fnd_global.local_chr(400)||p_active_date||
128 fnd_global.local_chr(400)||p_effecive_status||
129 fnd_global.local_chr(400)||p_loc_id||
130 fnd_global.local_chr(400)||p_lang_code||
131 fnd_global.local_chr(400)||p_loc_desc||
132 fnd_global.local_chr(400)||p_loc_style ||
133 fnd_global.local_chr(400)|| p_add_line_1||
134 fnd_global.local_chr(400)||p_add_line_2||
135 fnd_global.local_chr(400)|| p_add_line_3||
136 fnd_global.local_chr(400)||p_town_or_city||
137 fnd_global.local_chr(400)||p_country||
138 fnd_global.local_chr(400)||p_postal_code||
139 fnd_global.local_chr(400)||p_region_1||
140 fnd_global.local_chr(400)||p_region_2||
141 fnd_global.local_chr(400)||p_region_3||
142 fnd_global.local_chr(400)||p_tel_no_1||
143 fnd_global.local_chr(400)||p_tel_no_2 ||
144 fnd_global.local_chr(400)||p_tel_no_3||
145 fnd_global.local_chr(400)||p_loc_info_13||
146 fnd_global.local_chr(400)||p_loc_info_14||
147 fnd_global.local_chr(400)||p_loc_info_15||
148 fnd_global.local_chr(400)||p_loc_info_16||
149 fnd_global.local_chr(400)||p_loc_info_17||
150 fnd_global.local_chr(400)||p_loc_info_18||
151 fnd_global.local_chr(400)||p_loc_info_19||
152 fnd_global.local_chr(400)||p_loc_info_20||
153 fnd_global.local_chr(10)||fnd_global.local_chr(13));
154
155
156
157 end loop;
158 close CSR_LOC_INITIAL_LOAD;
159
160
161 FND_FILE.NEW_LINE(FND_FILE.log, 1);
162 FND_FILE.put_line(fnd_file.log,'Location Initial Load Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
163
164
165 exception
166 when OTHERS then
167 errbuf := errbuf||SQLERRM;
168 retcode := '1';
169 FND_FILE.put_line(fnd_file.log, 'Error in Location Initial Load Extraction: '||SQLCODE);
170 FND_FILE.NEW_LINE(FND_FILE.log, 1);
171 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
172
173 END HR_LOCATION_INITIAL_LOAD;
174 /*Procedure to extract Location Initial Load Extraction Ends*/
175
176 /*Procedure to extract Job Initial Load Extraction Begins*/
177
178 PROCEDURE HR_JOBCODE_INITIAL_LOAD(errbuf OUT NOCOPY VARCHAR2
179 ,retcode OUT NOCOPY VARCHAR2)
180 IS
181
182
183 p_job_id per_jobs.job_id%type;
184 p_business_grp_id per_jobs.business_group_id%type;
185 p_eff_date varchar2(10);
186 p_lang_code varchar2(10);
187 p_eff_status varchar2(10);
188 p_job_descr per_jobs_tl.name%type;
189
190 /*Cursor to fetch the job details*/
191
192 cursor csr_job_initial_load is
193 select pj.job_id,
194 business_group_id,
195 tl.language,
196 tl.name,
197 to_char(DATE_FROM,'YYYY-MM-DD') ,
198 'A'
199 from per_jobs pj,per_jobs_tl tl
200 where pj.job_id = tl.job_id
201 and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY')) > sysdate
202 union
203 select pj.job_id,
204 business_group_id,
205 tl.language,
206 tl.name,
207 to_char(DATE_TO,'YYYY-MM-DD') ,
208 'I'
209 from per_jobs pj,per_jobs_tl tl
210 where pj.job_id = tl.job_id
211 and date_to is not null
212 order by business_group_id,job_id;
213
214
215
216 begin
217
218
219 FND_FILE.NEW_LINE(FND_FILE.log, 1);
220 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
221 FND_FILE.put_line(fnd_file.log,'Job Code Initial Load Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
222
223 /*Generate the initial load extraction for job and the column delimiter used is to_char(400)*/
224 OPEN csr_job_initial_load;
225
226 LOOP
227
231
228 FETCH csr_job_initial_load
229 INTO p_job_id,p_business_grp_id,p_lang_code,p_job_descr,p_eff_date,p_eff_status;
230 EXIT WHEN csr_job_initial_load%NOTFOUND;
232
233 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
234 p_business_grp_id||fnd_global.local_chr(400)||
235 p_job_id||fnd_global.local_chr(400)||
236 p_eff_date||fnd_global.local_chr(400)||
237 p_eff_status||fnd_global.local_chr(400)||
238 p_lang_code||fnd_global.local_chr(400)||
239 p_job_descr||
240 fnd_global.local_chr(10)||fnd_global.local_chr(13));
241
242 END Loop;
243 CLOSE csr_job_initial_load;
244
245 FND_FILE.NEW_LINE(FND_FILE.log, 1);
246 FND_FILE.put_line(fnd_file.log,'Job Code Initial Load Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
247
248 EXCEPTION WHEN OTHERS THEN
249 errbuf := errbuf||SQLERRM;
250 retcode := '1';
251 FND_FILE.put_line(fnd_file.log, 'Error in Job Initial Load Extraction: '||SQLCODE);
252 FND_FILE.NEW_LINE(FND_FILE.log, 1);
253 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
254
255 END HR_JOBCODE_INITIAL_LOAD;
256
257 /*Procedure to extract Job Initial Load Ends*/
258
259
260
261 /*Procedure to extract Organization Initial Load Extraction Begins*/
262
263 PROCEDURE HR_ORGANIZATION_INITIAL_LOAD(errbuf OUT NOCOPY VARCHAR2
264 ,retcode OUT NOCOPY VARCHAR2)
265 IS
266
267
268 p_bg_id hr_all_organization_units.business_group_id%type;
269 p_org_id hr_all_organization_units.organization_id%type;
270 p_lang_code varchar2(10);
271 p_bg_name hr_all_organization_units_tl.name%type;
272 p_eff_status varchar2(10);
273 p_loc_id hr_all_organization_units.location_id%type;
274 p_eff_date varchar2(10);
275 p_person_id per_org_manager_v.person_id%type;
276
277
278
279 /*Cursor to fetch the organization details
280 If date_to is in future then
281 two records has to be fetched in the format
282 Date_From 'A'
283 Date_To 'I'
284
285 If date_to is in past then
286 one record has to be fetched as
287 Date_To 'I'*/
288
289 cursor csr_org_initial_load is
290 select ORG.BUSINESS_GROUP_ID,
291 ORG.ORGANIZATION_ID,
292 to_char(DATE_FROM,'YYYY-MM-DD') ,
293 'A' ,
294 TL.LANGUAGE,
295 TL.NAME,
296 ORG.LOCATION_ID,
297 /*Fix for 7576511 - to fetch employee number*/
298 (select employee_number from per_all_people_f ppf,hr_organization_information hrorg1
299 where ppf.person_id = hrorg1.ORG_INFORMATION2
300 and ppf.business_group_id = org.business_group_id
301 and hrorg1.org_information_context = 'Organization Name Alias'
302 and hrorg1.organization_id = org.organization_id
303 and nvl(org.date_to,to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hrorg1.org_information3)
304 and nvl(fnd_date.canonical_to_date(hrorg1.org_information4),to_date('31/12/4712','DD/MM/YYYY'))
305 and fnd_date.canonical_to_date(hrorg1.org_information3) between ppf.effective_start_date and ppf.effective_end_date) MANAGER_ID
306
307 from hr_all_organization_units org,hr_all_organization_units_tl TL
311 and hrorg.org_information1 = 'HR_ORG'
308 ,hr_organization_information hrorg
309 where tl.organization_id = org.organization_id
310 and hrorg.organization_id = org.organization_id
312 and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY')) > sysdate
313 union
314 select ORG.BUSINESS_GROUP_ID,
315 ORG.ORGANIZATION_ID,
316 to_char(DATE_TO,'YYYY-MM-DD') ,
317 'I' ,
318 TL.LANGUAGE,
319 TL.NAME,
320 ORG.LOCATION_ID,
321 /*Fix for 7576511 - to fetch employee number*/
322 (select employee_number from per_all_people_f ppf,hr_organization_information hrorg1
323 where ppf.person_id = hrorg1.ORG_INFORMATION2
324 and ppf.business_group_id = org.business_group_id
325 and hrorg1.org_information_context = 'Organization Name Alias'
326 and hrorg1.organization_id = org.organization_id
327 and nvl(org.date_to,to_date('31/12/4712','DD/MM/YYYY')) between fnd_date.canonical_to_date(hrorg1.org_information3)
331 from hr_all_organization_units org,hr_all_organization_units_tl TL
328 and nvl(fnd_date.canonical_to_date(hrorg1.org_information4),to_date('31/12/4712','DD/MM/YYYY'))
329 and fnd_date.canonical_to_date(hrorg1.org_information3) between ppf.effective_start_date and ppf.effective_end_date) MANAGER_ID
330
332 ,hr_organization_information hrorg
333 where tl.organization_id = org.organization_id
334 and hrorg.organization_id = org.organization_id
335 and hrorg.org_information1 = 'HR_ORG'
336 and date_to is not null
337 order by business_group_id,organization_id;
338
339
340 begin
341
342
343 FND_FILE.NEW_LINE(FND_FILE.log, 1);
344 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
345 FND_FILE.put_line(fnd_file.log,'Organization Initial Load Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
346
347 /*Generate the initial load extraction for organization and the column delimiter used is to_char(400)*/
348 OPEN csr_org_initial_load;
349
350 LOOP
351
352 FETCH csr_org_initial_load
353 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;
354 EXIT WHEN csr_org_initial_load%NOTFOUND;
355
356
357 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
358 p_bg_id||fnd_global.local_chr(400)||
359 p_org_id||fnd_global.local_chr(400)||
360 p_eff_date||fnd_global.local_chr(400)||
361 p_eff_status||fnd_global.local_chr(400)||
362 p_lang_code||fnd_global.local_chr(400)||
363 p_bg_name||fnd_global.local_chr(400)||
364 p_loc_id||fnd_global.local_chr(400)||
365 p_person_id||
366 fnd_global.local_chr(10)||fnd_global.local_chr(13));
367
368 END Loop;
369 CLOSE csr_org_initial_load;
370
371 FND_FILE.NEW_LINE(FND_FILE.log, 1);
372 FND_FILE.put_line(fnd_file.log,'Organization Initial Load Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
373
374 EXCEPTION WHEN OTHERS THEN
375 errbuf := errbuf||SQLERRM;
376 retcode := '1';
377 FND_FILE.put_line(fnd_file.log, 'Error in Organization Initial Load Extraction: '||SQLCODE);
378 FND_FILE.NEW_LINE(FND_FILE.log, 1);
379 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
380
381 END HR_ORGANIZATION_INITIAL_LOAD;
382
383 /*Procedure to extract Organization Initial Load Ends*/
384
385
386 /*Procedure to extract Workforce Initial Load Extraction Begins*/
387
388 PROCEDURE HR_WORKFORCE_INITIAL_LOAD(errbuf OUT NOCOPY VARCHAR2
389 ,retcode OUT NOCOPY VARCHAR2)
390 IS
391
392
393 p_person_id per_all_people_f.person_id%type;
394 p_assignment_id per_all_assignments_f.assignment_id%type;
395 p_assignment_number per_all_assignments_f.assignment_number%type;
396 p_effective_start_date varchar2(10);
397 p_effective_end_date varchar2(10);
398 p_probation_period per_all_assignments_f.probation_period%type;
399 p_probation_units per_all_assignments_f.probation_unit%type;
400 p_organization_id per_all_assignments_f.organization_id%type;
401 p_job_id per_all_assignments_f.job_id%type;
402 p_position_id per_all_assignments_f.position_id%type;
403 p_assignment_status_type_id per_all_assignments_f.assignment_status_type_id%type;
404 p_location_id per_all_assignments_f.location_id%type;
405 p_employment_category per_all_assignments_f.employment_category%type;
406 p_business_group_id per_all_assignments_f.business_group_id%type;
407 p_normal_hours per_all_assignments_f.normal_hours%type;
408 p_frequency per_all_assignments_f.frequency%type;
409 p_grade_id per_all_assignments_f.grade_id%type;
410 p_supervisor_id per_all_assignments_f.supervisor_id%type;
411 p_act_termn_date varchar2(10);
412 p_final_prcs_date varchar2(10);
413
414
415
416 /*Cursor to fetch the workforce details*/
417
418 cursor csr_wkfrc_initial_load is
419 SELECT
420 pas.person_id,
421 pas.assignment_id,
422 pas.assignment_number,
423 to_char(pas.effective_start_date,'YYYY-MM-DD'),
424 to_char(pas.effective_end_date,'YYYY-MM-DD'),
425 pas.probation_period,
426 pas.probation_unit,
427 pas.organization_id,
428 pas.job_id,
429 pas.position_id,
430 pas.assignment_status_type_id,
431 pas.location_id,
432 pas.employment_category,
433 pas.business_group_id,
434 pas.normal_hours,
435 pas.frequency,
436 pas.grade_id,
437 pas.supervisor_id,
438
439 case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date) then
440 to_char(pos.final_process_date,'YYYY-MM-DD')
441 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 ,
445 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
442
443 case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date)
444 then to_char(pos.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD')
446
447 FROM
448 per_all_assignments_f pas,
449 per_periods_of_service pos,
450 per_periods_of_placement pop
451 WHERE pas.person_id = pop.person_id (+)
452 AND pas.person_id = pos.person_id (+)
453 order by pas.business_group_id,pas.assignment_id,pas.effective_start_date;
454
455
456 begin
457
458
459 FND_FILE.NEW_LINE(FND_FILE.log, 1);
460 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
461 FND_FILE.put_line(fnd_file.log,'Workforce Initial Load Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
462
463 /*Generate the initial load extraction for workforce and the column delimiter used is to_char(400)*/
464 OPEN csr_wkfrc_initial_load;
465
466 LOOP
467
468 FETCH csr_wkfrc_initial_load
469 INTO p_person_id,p_assignment_id,p_assignment_number,p_effective_start_date,p_effective_end_date,
470 p_probation_period,p_probation_units,p_organization_id,p_job_id,p_position_id,p_assignment_status_type_id,
471 p_location_id,p_employment_category,p_business_group_id,p_normal_hours,p_frequency,p_grade_id,
472 p_supervisor_id ,p_act_termn_date, p_final_prcs_date;
473 EXIT WHEN csr_wkfrc_initial_load%NOTFOUND;
474
475
476 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
477 p_business_group_id||fnd_global.local_chr(400)||
478 p_person_id||fnd_global.local_chr(400)||
479 p_assignment_id||fnd_global.local_chr(400)||
480 p_assignment_number||fnd_global.local_chr(400)||
481 p_effective_start_date||fnd_global.local_chr(400)||
482 p_effective_end_date||fnd_global.local_chr(400)||
483 p_organization_id||fnd_global.local_chr(400)||
484 p_probation_period||fnd_global.local_chr(400)||
485 p_probation_units||fnd_global.local_chr(400)||
486 p_job_id||fnd_global.local_chr(400)||
487 p_assignment_status_type_id||fnd_global.local_chr(400)||
488 p_location_id||fnd_global.local_chr(400)||
489 p_employment_category||fnd_global.local_chr(400)||
490 p_normal_hours||fnd_global.local_chr(400)||
491 p_frequency||fnd_global.local_chr(400)||
492 p_grade_id||fnd_global.local_chr(400)||
493 p_position_id||fnd_global.local_chr(400)||
494 p_supervisor_id||fnd_global.local_chr(400)||
495 p_act_termn_date||fnd_global.local_chr(400)||
496 p_final_prcs_date||
497 fnd_global.local_chr(10)||fnd_global.local_chr(13));
498
499 END Loop;
500 CLOSE csr_wkfrc_initial_load;
501
502 FND_FILE.NEW_LINE(FND_FILE.log, 1);
503 FND_FILE.put_line(fnd_file.log,'Workforce Initial Load Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
504
505 EXCEPTION WHEN OTHERS THEN
506 errbuf := errbuf||SQLERRM;
507 retcode := '1';
508 FND_FILE.put_line(fnd_file.log, 'Error in Workforce Initial Load Extraction: '||SQLCODE);
509 FND_FILE.NEW_LINE(FND_FILE.log, 1);
510 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
511
512 END HR_WORKFORCE_INITIAL_LOAD;
513
514 /*Procedure to extract Workforce Initial Load Ends*/
515
516
517 /*Procedure to extract Person Initial Load Extraction Begins*/
518
519 PROCEDURE HR_PERSON_INITIAL_LOAD(errbuf OUT NOCOPY VARCHAR2
520 ,retcode OUT NOCOPY VARCHAR2)
521 IS
522
523 p_person_id per_all_people_f.person_id%type;
524 p_business_group_id per_all_people_f.business_group_id%type;
525 p_legislation_code hr_organization_information.org_information9%type;
526 p_employee_number per_all_people_f.employee_number%type;
527 p_applicant_number per_all_people_f.applicant_number%type;
528 p_npw_number per_all_people_f.npw_number%type;
529 p_person_type_id per_all_people_f.person_type_id%type;
530 p_date_of_birth varchar2(10);
531 p_town_of_birth per_all_people_f.town_of_birth%type;
532 p_cntry_of_birth per_all_people_f.country_of_birth%type;
533 p_date_of_death varchar2(10);
534 p_orig_dt_of_hire varchar2(10);
538 p_full_name per_all_people_f.full_name%type;
535 p_eff_start_date varchar2(10);
536 p_eff_end_date varchar2(10);
537 p_sex per_all_people_f.sex%type;
539 p_suffix per_all_people_f.suffix%type;
540 p_title per_all_people_f.title%type;
541 p_last_name per_all_people_f.last_name%type;
542 p_first_name per_all_people_f.first_name%type;
543 p_middle_names per_all_people_f.middle_names%type;
544 p_nationality per_all_people_f.nationality%type;
545 p_national_identifier per_all_people_f.national_identifier%type;
546 p_email_address per_all_people_f.email_address%type;
547 p_national_id_label varchar2(200);
548
549
550 TYPE ADDRESS IS RECORD
551 (
552
553 p_address_type per_addresses.address_type%type,
554 p_address_style per_addresses.style%type,
555 p_adr_date_from varchar2(10),
556 p_adr_date_to varchar2(10),
557 p_country per_addresses.country%type,
558 p_addr_line1 per_addresses.address_line1%type,
559 p_addr_line2 per_addresses.address_line2%type,
560 p_addr_line3 per_addresses.address_line3%type,
561 p_twn_or_city per_addresses.town_or_city%type,
562 p_tel_number1 per_addresses.telephone_number_1%type,
563 p_region1 per_addresses.region_1%type,
564 p_region2 per_addresses.region_2%type,
565 p_postal_code per_addresses.postal_code%type);
566
567 TYPE address_record is table of ADDRESS index by binary_integer;
568
569 TYPE PHONE IS RECORD
570 (
571
572 p_phn_date_from varchar2(10),
573 p_phn_date_to varchar2(10),
574 p_phone_type per_phones.phone_type%type,
575 p_phone_no per_phones.phone_number%type);
576
577 TYPE phone_record is table of PHONE index by binary_integer;
578
579 p_addr_type address_record;
580 p_phn_type phone_record;
581
582
583 /*Cursor to fetch the person details*/
584
585 cursor csr_person_data is
586 SELECT ppf.person_id,
587 ppf.business_group_id,
588 (select org_information9 from
589 hr_organization_information where organization_id = ppf.business_group_id
590 and org_information_context = 'Business Group Information') LEGISLATION_CODE,
591 EMPLOYEE_NUMBER,
592 APPLICANT_NUMBER,
593 NPW_NUMBER,
594 PERSON_TYPE_ID ,
595 to_char(DATE_OF_BIRTH,'YYYY-MM-DD'),
596 TOWN_OF_BIRTH,
597 COUNTRY_OF_BIRTH,
598 to_char(DATE_OF_DEATH,'YYYY-MM-DD'),
599 to_char(ORIGINAL_DATE_OF_HIRE,'YYYY-MM-DD'),
600 to_char(EFFECTIVE_START_DATE,'YYYY-MM-DD'),
601 to_char(EFFECTIVE_END_DATE,'YYYY-MM-DD'),
602 SEX,
603 FULL_NAME,
604 SUFFIX,
605 TITLE,
606 LAST_NAME,
607 FIRST_NAME,
608 MIDDLE_NAMES,
609 NATIONALITY,
610 NATIONAL_IDENTIFIER,
611 EMAIL_ADDRESS,
612 (select message_text from fnd_new_messages where message_name = 'HR_NATIONAL_ID_NUMBER_'|| (select to_char(org_information9) from
613 hr_organization_information where organization_id = ppf.business_group_id
614 and org_information_context = 'Business Group Information')
615 and language_code = USERENV('LANG') )NATIONAL_IDENTIFIER_LABEL
616
617 FROM PER_ALL_PEOPLE_F ppf
618 order by ppf.person_id,ppf.effective_start_date;
619
620 Cursor Csr_Address_Data(P_Person_Id Number,P_Eff_St_Dt Date,P_Eff_End_Dt Date) Is
621 Select
622
623 Address_Type,
624 style,
625 To_Char(Date_From,'YYYY-MM-DD'),
626 To_Char(Date_To,'YYYY-MM-DD'),
627 Country,
628 Address_Line1,
629 Address_Line2,
630 Address_Line3,
631 Town_Or_City,
632 Telephone_Number_1,
633 Region_1,
634 Region_2,
635 Postal_Code
636 FROM per_addresses
637 where person_id = p_person_id
638 and date_from between P_Eff_St_Dt and P_Eff_End_Dt;
639
640 Cursor csr_phone_data(P_Person_Id Number,P_Eff_St_Dt Date,P_Eff_End_Dt Date) Is
641 Select
642
643 to_char(ppn.date_from,'YYYY-MM-DD'),
644 to_char(ppn.date_to,'YYYY-MM-DD'),
645 PHONE_TYPE,
646 PHONE_NUMBER
647 FROM per_phones ppn
648 where ppn.PARENT_ID (+) = P_PERSON_ID
649 AND PPN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
650 AND DATE_FROM between P_Eff_St_Dt and P_Eff_End_Dt;
651
652
653 begin
654
655 FND_FILE.NEW_LINE(FND_FILE.log, 1);
656 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
657 FND_FILE.put_line(fnd_file.log,'Person Initial Load Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
658
662 OPEN csr_person_data;
659 /*Generate the initial load extraction for location and the column delimiter used is to_char(400)*/
660
661 /*Generate the initial load extraction for person and the column delimiter used is to_char(400)*/
663 loop
664 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,
665 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,
666 p_eff_end_date,p_sex,p_full_name,p_suffix,p_title,p_last_name,p_first_name,p_middle_names,
667 p_nationality,p_national_identifier,p_email_address,p_national_id_label;
668 exit when csr_person_data%notfound;
669
670 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'));
671 fetch csr_address_data bulk collect into p_addr_type;
672 close csr_address_data;
673
674 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'));
675 fetch csr_phone_data bulk collect into p_phn_type;
676 close csr_phone_data;
677
678 if p_addr_type.count > 0 and p_phn_type.count > 0 and p_addr_type.count >= p_phn_type.count
679 then
680
681 for k in p_addr_type.first .. p_addr_type.last
682 loop
683 if k <= p_phn_type.count
684 then
685
686 FND_FILE.put_line(FND_FILE.OUTPUT,
687 p_business_group_id||fnd_global.local_chr(400)||
688 p_person_id||fnd_global.local_chr(400)||
689 p_legislation_code||fnd_global.local_chr(400)||
690 p_employee_number||fnd_global.local_chr(400)||
691 p_applicant_number||fnd_global.local_chr(400)||
692 p_npw_number||fnd_global.local_chr(400)||
693 p_person_type_id||fnd_global.local_chr(400)||
694 p_date_of_birth||fnd_global.local_chr(400)||
695 p_town_of_birth||fnd_global.local_chr(400)||
696 p_cntry_of_birth||fnd_global.local_chr(400)||
697 p_date_of_death||fnd_global.local_chr(400)||
698 p_orig_dt_of_hire||fnd_global.local_chr(400)||
699 p_eff_start_date||fnd_global.local_chr(400)||
700 p_eff_end_date||fnd_global.local_chr(400)||
704 p_title||fnd_global.local_chr(400)||
701 p_sex||fnd_global.local_chr(400)||
702 p_full_name||fnd_global.local_chr(400)||
703 p_suffix||fnd_global.local_chr(400)||
705 p_last_name||fnd_global.local_chr(400)||
706 p_first_name||fnd_global.local_chr(400)||
707 p_middle_names||fnd_global.local_chr(400)||
708 p_nationality||fnd_global.local_chr(400)||
709 p_national_identifier||fnd_global.local_chr(400)||
710 p_email_address||fnd_global.local_chr(400)||
711 p_addr_type(k).p_address_type||fnd_global.local_chr(400)||
712 p_addr_type(k).p_adr_date_from||fnd_global.local_chr(400)||
713 p_addr_type(k).p_adr_date_to||fnd_global.local_chr(400)||
714 p_addr_type(k).p_address_style||fnd_global.local_chr(400)||
715 p_addr_type(k).p_country||fnd_global.local_chr(400)||
716 p_addr_type(k).p_addr_line1||fnd_global.local_chr(400)||
717 p_addr_type(k).p_addr_line2||fnd_global.local_chr(400)||
718 p_addr_type(k).p_addr_line3||fnd_global.local_chr(400)||
719 p_addr_type(k).p_twn_or_city||fnd_global.local_chr(400)||
720 p_addr_type(k).p_tel_number1||fnd_global.local_chr(400)||
721 p_addr_type(k).p_region1||fnd_global.local_chr(400)||
722 p_addr_type(k).p_region2||fnd_global.local_chr(400)||
723 p_addr_type(k).p_postal_code ||fnd_global.local_chr(400)||
724 p_phn_type(k).p_phn_date_from||fnd_global.local_chr(400)||
725 p_phn_type(k).p_phn_date_to||fnd_global.local_chr(400)||
726 p_phn_type(k).p_phone_type ||fnd_global.local_chr(400)||
727 p_phn_type(k).p_phone_no||fnd_global.local_chr(400)||
728 p_national_id_label||fnd_global.local_chr(400)||
729 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
730 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
734 else
731 fnd_global.local_chr(10)||fnd_global.local_chr(13));
732
733
735
736 FND_FILE.put_line(FND_FILE.OUTPUT,
737 p_business_group_id||fnd_global.local_chr(400)||
738 p_person_id||fnd_global.local_chr(400)||
739 p_legislation_code||fnd_global.local_chr(400)||
740 p_employee_number||fnd_global.local_chr(400)||
741 p_applicant_number||fnd_global.local_chr(400)||
742 p_npw_number||fnd_global.local_chr(400)||
743 p_person_type_id||fnd_global.local_chr(400)||
744 p_date_of_birth||fnd_global.local_chr(400)||
745 p_town_of_birth||fnd_global.local_chr(400)||
746 p_cntry_of_birth||fnd_global.local_chr(400)||
747 p_date_of_death||fnd_global.local_chr(400)||
748 p_orig_dt_of_hire||fnd_global.local_chr(400)||
749 p_eff_start_date||fnd_global.local_chr(400)||
750 p_eff_end_date||fnd_global.local_chr(400)||
751 p_sex||fnd_global.local_chr(400)||
752 p_full_name||fnd_global.local_chr(400)||
753 p_suffix||fnd_global.local_chr(400)||
754 p_title||fnd_global.local_chr(400)||
755 p_last_name||fnd_global.local_chr(400)||
756 p_first_name||fnd_global.local_chr(400)||
757 p_middle_names||fnd_global.local_chr(400)||
758 p_nationality||fnd_global.local_chr(400)||
759 p_national_identifier||fnd_global.local_chr(400)||
760 p_email_address||fnd_global.local_chr(400)||
761 p_addr_type(k).p_address_type||fnd_global.local_chr(400)||
762 p_addr_type(k).p_adr_date_from||fnd_global.local_chr(400)||
763 p_addr_type(k).p_adr_date_to||fnd_global.local_chr(400)||
764 p_addr_type(k).p_address_style||fnd_global.local_chr(400)||
765 p_addr_type(k).p_country||fnd_global.local_chr(400)||
766 p_addr_type(k).p_addr_line1||fnd_global.local_chr(400)||
767 p_addr_type(k).p_addr_line2||fnd_global.local_chr(400)||
768 p_addr_type(k).p_addr_line3||fnd_global.local_chr(400)||
769 p_addr_type(k).p_twn_or_city||fnd_global.local_chr(400)||
770 p_addr_type(k).p_tel_number1||fnd_global.local_chr(400)||
771 p_addr_type(k).p_region1||fnd_global.local_chr(400)||
772 p_addr_type(k).p_region2||fnd_global.local_chr(400)||
773 p_addr_type(k).p_postal_code ||fnd_global.local_chr(400)||
774 fnd_global.local_chr(400)||
775 fnd_global.local_chr(400)||
776 fnd_global.local_chr(400)||
777 fnd_global.local_chr(400)||
778 p_national_id_label||fnd_global.local_chr(400)||
779 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
780 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
781 fnd_global.local_chr(10)||fnd_global.local_chr(13));
782
783
784 end if;
785 end loop;
786 end if;
787
788 if p_addr_type.count > 0 and p_phn_type.count > 0 and p_addr_type.count < p_phn_type.count
789 then
790
791 for k in p_phn_type.first .. p_phn_type.last
792 loop
793 if k <= p_addr_type.count
794 then
795
796 FND_FILE.put_line(FND_FILE.OUTPUT,
797 p_business_group_id||fnd_global.local_chr(400)||
798 p_person_id||fnd_global.local_chr(400)||
799 p_legislation_code||fnd_global.local_chr(400)||
800 p_employee_number||fnd_global.local_chr(400)||
801 p_applicant_number||fnd_global.local_chr(400)||
802 p_npw_number||fnd_global.local_chr(400)||
803 p_person_type_id||fnd_global.local_chr(400)||
804 p_date_of_birth||fnd_global.local_chr(400)||
805 p_town_of_birth||fnd_global.local_chr(400)||
806 p_cntry_of_birth||fnd_global.local_chr(400)||
807 p_date_of_death||fnd_global.local_chr(400)||
808 p_orig_dt_of_hire||fnd_global.local_chr(400)||
809 p_eff_start_date||fnd_global.local_chr(400)||
810 p_eff_end_date||fnd_global.local_chr(400)||
811 p_sex||fnd_global.local_chr(400)||
812 p_full_name||fnd_global.local_chr(400)||
813 p_suffix||fnd_global.local_chr(400)||
814 p_title||fnd_global.local_chr(400)||
815 p_last_name||fnd_global.local_chr(400)||
816 p_first_name||fnd_global.local_chr(400)||
817 p_middle_names||fnd_global.local_chr(400)||
818 p_nationality||fnd_global.local_chr(400)||
819 p_national_identifier||fnd_global.local_chr(400)||
820 p_email_address||fnd_global.local_chr(400)||
821 p_addr_type(k).p_address_type||fnd_global.local_chr(400)||
822 p_addr_type(k).p_adr_date_from||fnd_global.local_chr(400)||
823 p_addr_type(k).p_adr_date_to||fnd_global.local_chr(400)||
824 p_addr_type(k).p_address_style||fnd_global.local_chr(400)||
828 p_addr_type(k).p_addr_line3||fnd_global.local_chr(400)||
825 p_addr_type(k).p_country||fnd_global.local_chr(400)||
826 p_addr_type(k).p_addr_line1||fnd_global.local_chr(400)||
827 p_addr_type(k).p_addr_line2||fnd_global.local_chr(400)||
829 p_addr_type(k).p_twn_or_city||fnd_global.local_chr(400)||
830 p_addr_type(k).p_tel_number1||fnd_global.local_chr(400)||
831 p_addr_type(k).p_region1||fnd_global.local_chr(400)||
832 p_addr_type(k).p_region2||fnd_global.local_chr(400)||
833 p_addr_type(k).p_postal_code ||fnd_global.local_chr(400)||
834 p_phn_type(k).p_phn_date_from||fnd_global.local_chr(400)||
835 p_phn_type(k).p_phn_date_to||fnd_global.local_chr(400)||
836 p_phn_type(k).p_phone_type ||fnd_global.local_chr(400)||
837 p_phn_type(k).p_phone_no||fnd_global.local_chr(400)||
838 p_national_id_label||fnd_global.local_chr(400)||
839 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
840 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
841 fnd_global.local_chr(10)||fnd_global.local_chr(13));
842
843 else
844
845 FND_FILE.put_line(FND_FILE.OUTPUT,
846 p_business_group_id||fnd_global.local_chr(400)||
847 p_person_id||fnd_global.local_chr(400)||
848 p_legislation_code||fnd_global.local_chr(400)||
849 p_employee_number||fnd_global.local_chr(400)||
850 p_applicant_number||fnd_global.local_chr(400)||
851 p_npw_number||fnd_global.local_chr(400)||
852 p_person_type_id||fnd_global.local_chr(400)||
853 p_date_of_birth||fnd_global.local_chr(400)||
854 p_town_of_birth||fnd_global.local_chr(400)||
855 p_cntry_of_birth||fnd_global.local_chr(400)||
856 p_date_of_death||fnd_global.local_chr(400)||
857 p_orig_dt_of_hire||fnd_global.local_chr(400)||
858 p_eff_start_date||fnd_global.local_chr(400)||
859 p_eff_end_date||fnd_global.local_chr(400)||
860 p_sex||fnd_global.local_chr(400)||
861 p_full_name||fnd_global.local_chr(400)||
862 p_suffix||fnd_global.local_chr(400)||
863 p_title||fnd_global.local_chr(400)||
864 p_last_name||fnd_global.local_chr(400)||
865 p_first_name||fnd_global.local_chr(400)||
866 p_middle_names||fnd_global.local_chr(400)||
867 p_nationality||fnd_global.local_chr(400)||
868 p_national_identifier||fnd_global.local_chr(400)||
869 p_email_address||fnd_global.local_chr(400)||
870 fnd_global.local_chr(400)||
871 fnd_global.local_chr(400)||
872 fnd_global.local_chr(400)||
873 fnd_global.local_chr(400)||
874 fnd_global.local_chr(400)||
875 fnd_global.local_chr(400)||
876 fnd_global.local_chr(400)||
877 fnd_global.local_chr(400)||
878 fnd_global.local_chr(400)||
879 fnd_global.local_chr(400)||
880 fnd_global.local_chr(400)||
881 fnd_global.local_chr(400)||
882 fnd_global.local_chr(400)||
883 p_phn_type(k).p_phn_date_from||fnd_global.local_chr(400)||
884 p_phn_type(k).p_phn_date_to||fnd_global.local_chr(400)||
885 p_phn_type(k).p_phone_type ||fnd_global.local_chr(400)||
886 p_phn_type(k).p_phone_no||fnd_global.local_chr(400)||
887 p_national_id_label||fnd_global.local_chr(400)||
888 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
889 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
890 fnd_global.local_chr(10)||fnd_global.local_chr(13));
891 end if;
892 end loop;
893 end if;
894
895 if p_phn_type.count > 0 and p_addr_type.count = 0
896 then
897 for k in p_phn_type.first .. p_phn_type.last
898 loop
899
900 FND_FILE.put_line(FND_FILE.OUTPUT,
901 p_business_group_id||fnd_global.local_chr(400)||
902 p_person_id||fnd_global.local_chr(400)||
903 p_legislation_code||fnd_global.local_chr(400)||
904 p_employee_number||fnd_global.local_chr(400)||
905 p_applicant_number||fnd_global.local_chr(400)||
906 p_npw_number||fnd_global.local_chr(400)||
907 p_person_type_id||fnd_global.local_chr(400)||
908 p_date_of_birth||fnd_global.local_chr(400)||
909 p_town_of_birth||fnd_global.local_chr(400)||
910 p_cntry_of_birth||fnd_global.local_chr(400)||
911 p_date_of_death||fnd_global.local_chr(400)||
912 p_orig_dt_of_hire||fnd_global.local_chr(400)||
913 p_eff_start_date||fnd_global.local_chr(400)||
914 p_eff_end_date||fnd_global.local_chr(400)||
915 p_sex||fnd_global.local_chr(400)||
916 p_full_name||fnd_global.local_chr(400)||
920 p_first_name||fnd_global.local_chr(400)||
917 p_suffix||fnd_global.local_chr(400)||
918 p_title||fnd_global.local_chr(400)||
919 p_last_name||fnd_global.local_chr(400)||
921 p_middle_names||fnd_global.local_chr(400)||
922 p_nationality||fnd_global.local_chr(400)||
923 p_national_identifier||fnd_global.local_chr(400)||
924 p_email_address||fnd_global.local_chr(400)||
925 fnd_global.local_chr(400)||
926 fnd_global.local_chr(400)||
927 fnd_global.local_chr(400)||
928 fnd_global.local_chr(400)||
929 fnd_global.local_chr(400)||
930 fnd_global.local_chr(400)||
931 fnd_global.local_chr(400)||
932 fnd_global.local_chr(400)||
933 fnd_global.local_chr(400)||
934 fnd_global.local_chr(400)||
935 fnd_global.local_chr(400)||
936 fnd_global.local_chr(400)||
937 fnd_global.local_chr(400)||
938 p_phn_type(k).p_phn_date_from||fnd_global.local_chr(400)||
939 p_phn_type(k).p_phn_date_to||fnd_global.local_chr(400)||
940 p_phn_type(k).p_phone_type ||fnd_global.local_chr(400)||
941 p_phn_type(k).p_phone_no||fnd_global.local_chr(400)||
942 p_national_id_label||fnd_global.local_chr(400)||
943 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
944 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
945 fnd_global.local_chr(10)||fnd_global.local_chr(13));
946
947 end loop;
948 end if;
949
950 if p_addr_type.count > 0 and p_phn_type.count = 0
951 then
952 for k in p_addr_type.first .. p_addr_type.last
953 loop
954
955 FND_FILE.put_line(FND_FILE.OUTPUT,
956 p_business_group_id||fnd_global.local_chr(400)||
957 p_person_id||fnd_global.local_chr(400)||
958 p_legislation_code||fnd_global.local_chr(400)||
959 p_employee_number||fnd_global.local_chr(400)||
960 p_applicant_number||fnd_global.local_chr(400)||
961 p_npw_number||fnd_global.local_chr(400)||
962 p_person_type_id||fnd_global.local_chr(400)||
963 p_date_of_birth||fnd_global.local_chr(400)||
964 p_town_of_birth||fnd_global.local_chr(400)||
965 p_cntry_of_birth||fnd_global.local_chr(400)||
966 p_date_of_death||fnd_global.local_chr(400)||
967 p_orig_dt_of_hire||fnd_global.local_chr(400)||
968 p_eff_start_date||fnd_global.local_chr(400)||
969 p_eff_end_date||fnd_global.local_chr(400)||
970 p_sex||fnd_global.local_chr(400)||
971 p_full_name||fnd_global.local_chr(400)||
972 p_suffix||fnd_global.local_chr(400)||
973 p_title||fnd_global.local_chr(400)||
974 p_last_name||fnd_global.local_chr(400)||
975 p_first_name||fnd_global.local_chr(400)||
976 p_middle_names||fnd_global.local_chr(400)||
977 p_nationality||fnd_global.local_chr(400)||
978 p_national_identifier||fnd_global.local_chr(400)||
979 p_email_address||fnd_global.local_chr(400)||
980 p_addr_type(k).p_address_type||fnd_global.local_chr(400)||
981 p_addr_type(k).p_adr_date_from||fnd_global.local_chr(400)||
982 p_addr_type(k).p_adr_date_to||fnd_global.local_chr(400)||
983 p_addr_type(k).p_address_style||fnd_global.local_chr(400)||
984 p_addr_type(k).p_country||fnd_global.local_chr(400)||
985 p_addr_type(k).p_addr_line1||fnd_global.local_chr(400)||
986 p_addr_type(k).p_addr_line2||fnd_global.local_chr(400)||
987 p_addr_type(k).p_addr_line3||fnd_global.local_chr(400)||
988 p_addr_type(k).p_twn_or_city||fnd_global.local_chr(400)||
989 p_addr_type(k).p_tel_number1||fnd_global.local_chr(400)||
990 p_addr_type(k).p_region1||fnd_global.local_chr(400)||
991 p_addr_type(k).p_region2||fnd_global.local_chr(400)||
992 p_addr_type(k).p_postal_code ||fnd_global.local_chr(400)||
993 fnd_global.local_chr(400)||
994 fnd_global.local_chr(400)||
995 fnd_global.local_chr(400)||
996 fnd_global.local_chr(400)||
997 p_national_id_label||fnd_global.local_chr(400)||
998 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
999 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
1000 fnd_global.local_chr(10)||fnd_global.local_chr(13));
1001
1002 end loop;
1003 end if;
1004
1005 /*Fix for bug 7650158 starts here*/
1006 if p_addr_type.count = 0 and p_phn_type.count = 0
1007 then
1008
1009
1010 FND_FILE.put_line(FND_FILE.OUTPUT,
1011 p_business_group_id||fnd_global.local_chr(400)||
1012 p_person_id||fnd_global.local_chr(400)||
1013 p_legislation_code||fnd_global.local_chr(400)||
1017 p_person_type_id||fnd_global.local_chr(400)||
1014 p_employee_number||fnd_global.local_chr(400)||
1015 p_applicant_number||fnd_global.local_chr(400)||
1016 p_npw_number||fnd_global.local_chr(400)||
1018 p_date_of_birth||fnd_global.local_chr(400)||
1019 p_town_of_birth||fnd_global.local_chr(400)||
1020 p_cntry_of_birth||fnd_global.local_chr(400)||
1021 p_date_of_death||fnd_global.local_chr(400)||
1022 p_orig_dt_of_hire||fnd_global.local_chr(400)||
1023 p_eff_start_date||fnd_global.local_chr(400)||
1024 p_eff_end_date||fnd_global.local_chr(400)||
1025 p_sex||fnd_global.local_chr(400)||
1026 p_full_name||fnd_global.local_chr(400)||
1027 p_suffix||fnd_global.local_chr(400)||
1028 p_title||fnd_global.local_chr(400)||
1029 p_last_name||fnd_global.local_chr(400)||
1030 p_first_name||fnd_global.local_chr(400)||
1031 p_middle_names||fnd_global.local_chr(400)||
1032 p_nationality||fnd_global.local_chr(400)||
1033 p_national_identifier||fnd_global.local_chr(400)||
1034 p_email_address||fnd_global.local_chr(400)||
1035 fnd_global.local_chr(400)||
1036 fnd_global.local_chr(400)||
1037 fnd_global.local_chr(400)||
1038 fnd_global.local_chr(400)||
1039 fnd_global.local_chr(400)||
1040 fnd_global.local_chr(400)||
1041 fnd_global.local_chr(400)||
1042 fnd_global.local_chr(400)||
1043 fnd_global.local_chr(400)||
1044 fnd_global.local_chr(400)||
1045 fnd_global.local_chr(400)||
1046 fnd_global.local_chr(400)||
1047 fnd_global.local_chr(400)||
1048 fnd_global.local_chr(400)||
1049 fnd_global.local_chr(400)||
1050 fnd_global.local_chr(400)||
1051 fnd_global.local_chr(400)||
1052 p_national_id_label||fnd_global.local_chr(400)||
1053 hr_hrhd_initial_load.hr_hrhd_encrypt(p_person_id)||fnd_global.local_chr(400)||
1054 hr_hrhd_initial_load.hr_hrhd_encrypt(p_business_group_id)||
1055 fnd_global.local_chr(10)||fnd_global.local_chr(13));
1056
1057
1058 end if;
1059 /*Fix for bug 7650158 ends here*/
1060
1061 END Loop;
1062 close csr_person_data;
1063
1064
1065 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1066 FND_FILE.put_line(fnd_file.log,'Person Initial Load Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1067
1068 EXCEPTION WHEN OTHERS THEN
1069 errbuf := errbuf||SQLERRM;
1070 retcode := '1';
1071 FND_FILE.put_line(fnd_file.log, 'Error in Person Initial Load Extraction: '||SQLCODE);
1072 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1073 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
1074
1075 END HR_PERSON_INITIAL_LOAD;
1076
1077 /*Procedure to extract Person Initial Load Ends*/
1078
1079
1080 /*Common Procedure called from concurrent program begins*/
1081 procedure HR_INITIAL_LOAD (ERRBUF OUT NOCOPY varchar2,
1082 RETCODE OUT NOCOPY number,
1083 p_process_name in varchar2)
1084 is
1085 begin
1086
1087 if p_process_name = 'LOCATION_FULL_SYNCH' then
1088 HR_HRHD_INITIAL_LOAD.HR_LOCATION_INITIAL_LOAD(ERRBUF,RETCODE);
1089
1090 elsif p_process_name = 'JOBCODE_FULL_SYNCH' then
1091 HR_HRHD_INITIAL_LOAD.HR_JOBCODE_INITIAL_LOAD(ERRBUF,RETCODE);
1092
1093 elsif p_process_name = 'ORGANIZATION_FULL_SYNCH' then
1094 HR_HRHD_INITIAL_LOAD.HR_ORGANIZATION_INITIAL_LOAD(ERRBUF,RETCODE);
1095
1096 elsif p_process_name = 'WORKFORCE_FULL_SYNCH' then
1097 HR_HRHD_INITIAL_LOAD.HR_WORKFORCE_INITIAL_LOAD(ERRBUF,RETCODE);
1098
1099 elsif p_process_name = 'PERSON_FULL_SYNCH' then
1100 HR_HRHD_INITIAL_LOAD.HR_PERSON_INITIAL_LOAD(ERRBUF,RETCODE);
1101 end if;
1102 end HR_INITIAL_LOAD;
1103 /*Common Procedure called from concurrent program ends*/
1104
1105 /* Function for encrypting */
1106
1107 function hr_hrhd_encrypt(p_data VARCHAR2 ) RETURN RAW is
1108 key_bytes_raw RAW (128); -- stores 256-bit encryption key
1109 encryption_type PLS_INTEGER := FND_CRYPTO.DES3_CBC_PKCS5;
1110 --DBMS_CRYPTO.HASH_MD5+ DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5; -- total encryption type
1111 BEGIN
1112
1113 key_bytes_raw := UTL_I18N.STRING_TO_RAW(fnd_vault.get('HRHD','CRYPT_KEY'));
1114
1115 RETURN(FND_CRYPTO.ENCRYPT(plaintext => utl_raw.cast_to_raw (p_data),
1116 crypto_type => encryption_type, key => key_bytes_raw ));
1117
1118 END;
1119
1120 /* Function for decrypting */
1121
1122 function hr_hrhd_decrypt(p_data RAW ) RETURN VARCHAR2 is
1123 key_bytes_raw RAW (128); -- stores 256-bit encryption key
1124 encryption_type PLS_INTEGER := FND_CRYPTO.DES3_CBC_PKCS5 ; -- total encryption type
1125 --DBMS_CRYPTO.HASH_MD5+ DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
1126 BEGIN
1127
1131 crypto_type => encryption_type, key => key_bytes_raw )));
1128 key_bytes_raw := UTL_I18N.STRING_TO_RAW(fnd_vault.get('HRHD','CRYPT_KEY'));
1129
1130 RETURN(UTL_RAW.CAST_TO_VARCHAR2(FND_CRYPTO.DECRYPT(cryptext => p_data,
1132
1133 END;
1134
1135
1136 end HR_HRHD_INITIAL_LOAD;