[Home] [Help]
PACKAGE BODY: APPS.HR_FULL_SYNC_MESSAGES
Source
1 PACKAGE BODY HR_FULL_SYNC_MESSAGES as
2 /* $Header: perhrhdfull.pkb 120.11 2011/05/18 11:54:02 nchinnam noship $ */
3
4 TYPE EMPLIDTYPE IS TABLE OF per_all_people_f.employee_number%type INDEX BY BINARY_INTEGER;
5 TYPE EMPL_RCDTYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 TYPE PROBATION_DTTYPE IS TABLE OF per_all_assignments_f.probation_period%type INDEX BY BINARY_INTEGER;
7 TYPE ORIG_HIRE_DTTYPE IS TABLE OF per_all_people_f.original_date_of_hire%type INDEX BY BINARY_INTEGER;
8 TYPE WEFFDTTYPE IS TABLE OF per_all_assignments_f.effective_start_date%type INDEX BY BINARY_INTEGER;
9 TYPE BUSINESS_UNITTYPE IS TABLE OF per_all_assignments_f.organization_id%type INDEX BY BINARY_INTEGER;
10 TYPE WJOBCODETYPE IS TABLE OF per_all_assignments_f.job_id%type INDEX BY BINARY_INTEGER;
11 TYPE EMPL_STATUSTYPE IS TABLE OF per_all_assignments_f.assignment_status_type_id%type INDEX BY BINARY_INTEGER;
12 TYPE LOCATIONTYPE IS TABLE OF per_all_assignments_f.location_id%type INDEX BY BINARY_INTEGER;
13 TYPE FULL_PART_TIMETYPE IS TABLE OF per_all_assignments_f.employment_category%type INDEX BY BINARY_INTEGER;
14 TYPE COMPANYTYPE IS TABLE OF per_all_assignments_f.business_group_id%type INDEX BY BINARY_INTEGER;
15 TYPE STD_HOURSTYPE IS TABLE OF per_all_assignments_f.normal_hours%type INDEX BY BINARY_INTEGER;
16 TYPE STD_HRS_FREQUENCYTYPE IS TABLE OF per_all_assignments_f.frequency%type INDEX BY BINARY_INTEGER;
17 TYPE GRADETYPE IS TABLE OF per_all_assignments_f.grade_id%type INDEX BY BINARY_INTEGER;
18 TYPE SUPERVISOR_IDTYPE IS TABLE OF per_all_assignments_f.supervisor_id%type INDEX BY BINARY_INTEGER;
19 TYPE ASGN_START_DTTYPE IS TABLE OF per_all_assignments_f.EFFECTIVE_START_DATE%type INDEX BY BINARY_INTEGER;
20 TYPE ASGN_END_DTTYPE IS TABLE OF per_all_assignments_f.EFFECTIVE_END_DATE%type INDEX BY BINARY_INTEGER;
21 TYPE TERMINATION_DTTYPE IS TABLE OF per_periods_of_service.final_process_date%type INDEX BY BINARY_INTEGER;
22 TYPE LAST_DATE_WORKEDTYPE IS TABLE OF per_periods_of_service.ACCEPTED_TERMINATION_DATE%type INDEX BY BINARY_INTEGER;
23 TYPE STEPTYPE IS TABLE OF PER_SPINAL_POINT_PLACEMENTS_F.STEP_ID%type INDEX BY BINARY_INTEGER;
24 TYPE workforce IS REF CURSOR;
25
26 TYPE WorkForceTblType IS RECORD
27 (
28 EMPLID EMPLIDTYPE
29 ,EMPL_RCD EMPL_RCDTYPE
30 ,PROBATION_DT PROBATION_DTTYPE
31 ,ORIG_HIRE_DT ORIG_HIRE_DTTYPE
32 ,EFFDT WEFFDTTYPE
33 ,BUSINESS_UNIT BUSINESS_UNITTYPE
34 ,JOBCODE WJOBCODETYPE
35 ,EMPL_STATUS EMPL_STATUSTYPE
36 ,LOCATION LOCATIONTYPE
37 ,FULL_PART_TIME FULL_PART_TIMETYPE
38 ,COMPANY COMPANYTYPE
39 ,STD_HOURS STD_HOURSTYPE
40 ,STD_HRS_FREQUENCY STD_HRS_FREQUENCYTYPE
41 ,GRADE GRADETYPE
42 ,SUPERVISOR_ID SUPERVISOR_IDTYPE
43 ,ASGN_START_DT ASGN_START_DTTYPE
44 ,ASGN_END_DT ASGN_END_DTTYPE
45 ,TERMINATION_DT TERMINATION_DTTYPE
46 ,LAST_DATE_WORKED LAST_DATE_WORKEDTYPE
47 ,STEP STEPTYPE
48 );
49
50 WorkForceFullType WorkForceTblType;
51 WorkForcedeltaType WorkForceTblType;
52
53
54
55 TYPE setidType IS TABLE OF per_jobs.business_group_id%type INDEX BY BINARY_INTEGER;
56 TYPE jobcodeType IS TABLE OF per_jobs.job_id%type INDEX BY BINARY_INTEGER;
57 TYPE effdtType IS TABLE OF per_jobs.date_from%type INDEX BY BINARY_INTEGER;
58 TYPE effstatusType IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
59 TYPE descrType IS TABLE OF per_jobs.name%type INDEX BY BINARY_INTEGER;
60 TYPE jobcode IS REF CURSOR;
61
62 TYPE JobCodeTblType IS RECORD
63 (
64 SETID setidType,
65 JOBCODE jobcodeType,
66 EFFDT effdtType,
67 EFF_STATUS effstatusType,
68 DESCR descrType);
69
70 Jobcodefulltype JobCodeTblType;
71 Jobcodedeltatype JobCodeTblType;
72
73 /*Common procedure to update the hr_psft_sync_run table begins here*/
74 PROCEDURE update_psft_sync_run
75 (p_status number
76 ,p_process_name varchar2
77 ,p_run_date date
78 ,errbuf OUT NOCOPY VARCHAR2
79 ,retcode OUT NOCOPY VARCHAR2)
80 IS
81 l_status varchar2(10);
82
83 BEGIN
84
85 if p_status = 1 then
86 l_status := 'COMPLETED';
87 elsif p_status = 2 then
88 l_status := 'STARTED';
89 elsif p_status = 3 then
90 l_status := 'ERROR';
91 end if;
92
93 update hr_psft_sync_run
94 set status = l_status
95 where process = p_process_name
96 and run_date = p_run_date;
97 commit;
98
99 FND_FILE.NEW_LINE(FND_FILE.log, 1);
100
101 EXCEPTION WHEN OTHERS THEN
102 errbuf := errbuf||SQLERRM;
103 retcode := '1';
104 FND_FILE.put_line(fnd_file.log,'Error in update_psft_sync_run: '||SQLCODE);
105 FND_FILE.NEW_LINE(FND_FILE.log, 1);
106 FND_FILE.put_line(fnd_file.log,'Error Msg: '||substr(SQLERRM,1,700));
107
108 END update_psft_sync_run;
109 /*Common procedure to update the hr_psft_sync_run table ends here*/
110
111 /*Common procedure to insert into hr_psft_sync_run table begins here*/
112
113 PROCEDURE insert_psft_sync_run
114 (p_status number
115 ,p_process_name varchar2
116 ,errbuf OUT NOCOPY VARCHAR2
117 ,retcode OUT NOCOPY VARCHAR2)
118 IS
119 l_status varchar2(10);
120 BEGIN
121
122 FND_FILE.NEW_LINE(FND_FILE.log, 1);
123
124 if p_status = 1 then
125 l_status := 'COMPLETED';
126 elsif p_status = 2 then
127 l_status := 'STARTED';
128 elsif p_status = 3 then
129 l_status := 'ERROR';
130 end if;
131
132 INSERT INTO hr_psft_sync_run(run_date,status,process)
133 Values(sysdate,l_status,p_process_name);
134 commit;
135
136 FND_FILE.NEW_LINE(FND_FILE.log, 1);
137
138 EXCEPTION WHEN OTHERS THEN
139 errbuf := errbuf||SQLERRM;
140 retcode := '1';
141 FND_FILE.put_line(fnd_file.log,'Error in insert_psft_sync_run: '||SQLCODE);
142 FND_FILE.NEW_LINE(FND_FILE.log, 1);
143 FND_FILE.put_line(fnd_file.log,'Error Msg: '||substr(SQLERRM,1,700));
144
145 END insert_psft_sync_run;
146 /*Common procedure to insert into psft_sync_run table ends here*/
147
148 /*Procedure to extract state data for Full Synch messages begins*/
149 PROCEDURE HR_STATE_FULL_SYNC(errbuf OUT NOCOPY VARCHAR2
150 ,retcode OUT NOCOPY VARCHAR2)
151 is
152
153 p_cntry_code fnd_territories_vl.territory_code%type;
154 p_state_code fnd_common_lookups.lookup_code%type;
155 p_state_desc fnd_common_lookups.meaning%type;
156 p_enabled_flag fnd_common_lookups.enabled_flag%type;
157 p_effective_date date default sysdate;
158
159
160
161 cursor fet_state_sync is
162 select ft.territory_code,fcl.lookup_code,fcl.meaning,fcl.enabled_flag
163 from fnd_common_lookups fcl,fnd_territories_vl ft
164 where fcl.lookup_type = (ft.territory_code ||'_STATE')
165 order by ft.territory_code;
166
167 cursor fet_psft_sync is
168 select count('x')
169 from hr_psft_sync_run
170 where process = 'STATE_FULL_SYNC'
171 and run_date < p_effective_date
172 and status = 'STARTED';
173
174 l_dummy number;
175
176 begin
177
178 open fet_psft_sync;
179 fetch fet_psft_sync into l_dummy;
180 close fet_psft_sync;
181 if l_dummy = 0
182 then
183 FND_FILE.NEW_LINE(FND_FILE.log, 1);
184 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
185 FND_FILE.put_line(fnd_file.log,'State Full synch Data Extraction Begins:'||to_char(p_effective_date,
186 'DD/MM/RRRR HH:MI:SS'));
187 hr_full_sync_messages.insert_psft_sync_run(2,'STATE_FULL_SYNC',errbuf,retcode);
188
189 open fet_state_sync;
190 loop
191 fetch fet_state_sync into p_cntry_code,p_state_code,p_state_desc,p_enabled_flag;
192 exit when fet_state_sync%notfound;
193
194 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_cntry_code||fnd_global.local_chr(400)||p_state_code
195 ||fnd_global.local_chr(400)||p_state_desc||fnd_global.local_chr(400)||p_enabled_flag);
196 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
197 end loop;
198 close fet_state_sync;
199
200 hr_full_sync_messages.update_psft_sync_run(1,'STATE_FULL_SYNC',p_effective_date,errbuf,retcode);
201 FND_FILE.NEW_LINE(FND_FILE.log, 1);
202 FND_FILE.put_line(fnd_file.log,'State Full Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
203 end if;
204
205
206 exception
207 when OTHERS then
208 hr_full_sync_messages.update_psft_sync_run(3,'STATE_FULL_SYNC',p_effective_date,errbuf,retcode);
209 errbuf := errbuf||SQLERRM;
210 retcode := '1';
211 FND_FILE.put_line(fnd_file.log, 'Error in State Data Full Synch Extraction: '||SQLCODE);
212 FND_FILE.NEW_LINE(FND_FILE.log, 1);
213 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
214
215 end HR_STATE_FULL_SYNC;
216 /*Procedure to extract state data for Full Synch messages ends*/
217
218 /*Procedure to extract country data for Full Synch messages begins*/
219
220 PROCEDURE HR_COUNTRY_FULL_SYNC(errbuf OUT NOCOPY VARCHAR2
221 ,retcode OUT NOCOPY VARCHAR2)
222 is
223
224 p_cntry_code fnd_territories_vl.territory_code%type;
225 p_cntry_desc fnd_territories_vl.territory_short_name%type;
226 p_obsolete_flag fnd_territories_vl.obsolete_flag%type;
227 p_effective_date date default sysdate;
228
229 cursor fet_cntry_fsync is
230 select ft.territory_code,
231 ft.territory_short_name ,
232 ft.territory_code,ft.obsolete_flag
233 from fnd_territories_vl ft
234 order by ft.territory_code;
235
236 cursor fet_psft_sync is
237 select count('x')
238 from hr_psft_sync_run
239 where process = 'COUNTRY_FULL_SYNC'
240 and run_date < p_effective_date
241 and status = 'STARTED';
242
243 l_dummy number;
244
245 begin
246
247 open fet_psft_sync;
248 fetch fet_psft_sync into l_dummy;
249 close fet_psft_sync;
250 if l_dummy = 0
251 then
252 FND_FILE.NEW_LINE(FND_FILE.log, 1);
253 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
254 FND_FILE.put_line(fnd_file.log,'Country Full Synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
255 hr_full_sync_messages.insert_psft_sync_run(2,'COUNTRY_FULL_SYNC',errbuf,retcode);
256 open fet_cntry_fsync;
257 loop
258 fetch fet_cntry_fsync into p_cntry_code,p_cntry_desc,p_cntry_code,p_obsolete_flag;
259 exit when fet_cntry_fsync%notfound;
260 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_cntry_code||fnd_global.local_chr(400)||p_cntry_desc||fnd_global.local_chr(400)
261 ||p_cntry_code||fnd_global.local_chr(400)||p_obsolete_flag);
262 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
263 end loop;
264 close fet_cntry_fsync;
265
266
267 hr_full_sync_messages.update_psft_sync_run(1,'COUNTRY_FULL_SYNC',p_effective_date,errbuf,retcode);
268 FND_FILE.NEW_LINE(FND_FILE.log, 1);
269 FND_FILE.put_line(fnd_file.log,'Country Full Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
270
271 end if;
272
273 exception
274 when OTHERS then
275 hr_full_sync_messages.update_psft_sync_run(3,'COUNTRY_FULL_SYNC',p_effective_date,errbuf,retcode);
276 errbuf := errbuf||SQLERRM;
277 retcode := '1';
278 FND_FILE.put_line(fnd_file.log, 'Error in Country Data Full Synch Extraction: '||SQLCODE);
279 FND_FILE.NEW_LINE(FND_FILE.log, 1);
280 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
281
282 end HR_COUNTRY_FULL_SYNC;
283
284 /*Procedure to extract country data for Full Synch messages ends*/
285
286 /*Procedure to extract Location data for Full Synch messages Begins*/
287 PROCEDURE HR_LOCATION_FULL_SYNC(errbuf OUT NOCOPY VARCHAR2
288 ,retcode OUT NOCOPY VARCHAR2)
289 is
290
291 p_bg_id hr_locations_all.business_group_id%type;
292 p_loc_id hr_locations_all.LOCATION_ID%type;
293 p_active_date date;
294 p_effecive_status varchar2(10);
295 p_loc_code hr_locations_all.LOCATION_CODE%type;
296 p_loc_desc hr_locations_all.DESCRIPTION%type;
297 p_loc_style hr_locations_all.STYLE%type;
298 p_add_line_1 hr_locations_all.ADDRESS_LINE_1%type;
299 p_add_line_2 hr_locations_all.ADDRESS_LINE_2%type;
300 p_add_line_3 hr_locations_all.ADDRESS_LINE_3%type;
301 p_town_or_city hr_locations_all.TOWN_OR_CITY%type;
302 p_country hr_locations_all.COUNTRY%type;
303 p_postal_code hr_locations_all.POSTAL_CODE%type;
304 p_region_1 hr_locations_all.REGION_1%type;
305 p_region_2 hr_locations_all.REGION_2%type;
306 p_region_3 hr_locations_all.REGION_3%type;
307 p_tel_no_1 hr_locations_all.TELEPHONE_NUMBER_1%type;
308 p_tel_no_2 hr_locations_all.TELEPHONE_NUMBER_2%type;
309 p_tel_no_3 hr_locations_all.TELEPHONE_NUMBER_3%type;
310 p_loc_info_13 hr_locations_all.LOC_INFORMATION13%type;
311 p_loc_info_14 hr_locations_all.LOC_INFORMATION14%type;
312 p_loc_info_15 hr_locations_all.LOC_INFORMATION15%type;
313 p_loc_info_16 hr_locations_all.LOC_INFORMATION16%type;
314 p_loc_info_17 hr_locations_all.LOC_INFORMATION17%type;
315 p_loc_info_18 hr_locations_all.LOC_INFORMATION18%type;
316 p_loc_info_19 hr_locations_all.LOC_INFORMATION19%type;
317 p_loc_info_20 hr_locations_all.LOC_INFORMATION20%type;
318 p_effective_date date default sysdate;
319
320 cursor fet_loc_sync is
321 select BUSINESS_GROUP_ID,
322 LOCATION_ID,
323 case when inactive_date is not null then inactive_date
324 else CREATION_DATE end,
325 case when inactive_date is not null then 'INACTIVE'
326 else 'ACTIVE' end,
327 LOCATION_CODE ,
328 DESCRIPTION,
329 STYLE,
330 COUNTRY,
331 ADDRESS_LINE_1,
332 ADDRESS_LINE_2,
333 ADDRESS_LINE_3,
334 TOWN_OR_CITY,
335 REGION_1,
336 REGION_2,
337 REGION_3,
338 POSTAL_CODE,
339 TELEPHONE_NUMBER_1,
340 TELEPHONE_NUMBER_2,
341 TELEPHONE_NUMBER_3,
342 LOC_INFORMATION13,
343 LOC_INFORMATION14,
344 LOC_INFORMATION15,
345 LOC_INFORMATION16,
346 LOC_INFORMATION17,
347 LOC_INFORMATION18,
348 LOC_INFORMATION19,
349 LOC_INFORMATION20
350 from
351 hr_locations_all
352 where inactive_date is null;
353
354
355 cursor fet_psft_sync is
356 select count('x')
357 from hr_psft_sync_run
358 where process = 'LOC_FULL_SYNC'
359 and run_date < p_effective_date
360 and status = 'STARTED';
361
362
363
364 l_dummy number;
365
366 begin
367
368 open fet_psft_sync;
369 fetch fet_psft_sync into l_dummy;
370 close fet_psft_sync;
371 if l_dummy = 0
372 then
373 FND_FILE.NEW_LINE(FND_FILE.log, 1);
374 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
375 FND_FILE.put_line(fnd_file.log,'Location Full Synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
376 hr_full_sync_messages.insert_psft_sync_run(2,'LOC_FULL_SYNC',errbuf,retcode);
377
378 open fet_loc_sync;
379 loop
380 fetch fet_loc_sync into p_bg_id,p_loc_id,p_active_date,p_effecive_status,
381 p_loc_code, p_loc_desc, p_loc_style , p_country, p_add_line_1, p_add_line_2, p_add_line_3,
382 p_town_or_city,p_region_1,p_region_2,p_region_3,p_postal_code,p_tel_no_1,p_tel_no_2 ,
383 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,
384 p_loc_info_19,p_loc_info_20;
385 exit when fet_loc_sync%notfound;
386 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_bg_id||fnd_global.local_chr(400)||p_loc_id||
387 fnd_global.local_chr(400)||p_active_date||
388 fnd_global.local_chr(400)||p_effecive_status||fnd_global.local_chr(400)||
389 p_loc_code||fnd_global.local_chr(400)|| p_loc_desc||fnd_global.local_chr(400)||'ADDRESS_START'||
390 fnd_global.local_chr(400)||p_loc_style ||fnd_global.local_chr(400)|| p_add_line_1||
391 fnd_global.local_chr(400)|| p_add_line_2||fnd_global.local_chr(400)|| p_add_line_3||
392 fnd_global.local_chr(400)||p_town_or_city||fnd_global.local_chr(400)||p_country||
393 fnd_global.local_chr(400)||p_postal_code||fnd_global.local_chr(400)||p_region_1||
394 fnd_global.local_chr(400)||p_region_2||fnd_global.local_chr(400)||p_region_3||
395 fnd_global.local_chr(400)||p_tel_no_1||fnd_global.local_chr(400)||p_tel_no_2 ||
396 fnd_global.local_chr(400)||p_tel_no_3||fnd_global.local_chr(400)||p_loc_info_13||
397 fnd_global.local_chr(400)|| p_loc_info_14||fnd_global.local_chr(400)||p_loc_info_15||
398 fnd_global.local_chr(400)||p_loc_info_16||fnd_global.local_chr(400)||p_loc_info_17||
399 fnd_global.local_chr(400)||p_loc_info_18||fnd_global.local_chr(400)||
400 p_loc_info_19||fnd_global.local_chr(400)||p_loc_info_20||fnd_global.local_chr(400)||'ADDRESS_END'||
401 fnd_global.local_chr(400));
402 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
403 end loop;
404 close fet_loc_sync;
405
406 hr_full_sync_messages.update_psft_sync_run(1,'LOC_FULL_SYNC',p_effective_date,errbuf,retcode);
407 FND_FILE.NEW_LINE(FND_FILE.log, 1);
408 FND_FILE.put_line(fnd_file.log,'Location Full Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
409
410 end if;
411
412 exception
413 when OTHERS then
414 hr_full_sync_messages.update_psft_sync_run(3,'LOC_FULL_SYNC',p_effective_date,errbuf,retcode);
415 errbuf := errbuf||SQLERRM;
416 retcode := '1';
417 FND_FILE.put_line(fnd_file.log, 'Error in Location Full Synch Data Extraction: '||SQLCODE);
418 FND_FILE.NEW_LINE(FND_FILE.log, 1);
419 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
420
421 end HR_LOCATION_FULL_SYNC;
422 /*Procedure to extract Location data for Full Synch messages Ends*/
423
424 /*Procedure to extract Person data for Full Synch messages Begins*/
425
426 procedure hr_person_full_sync(errbuf OUT NOCOPY VARCHAR2
427 ,retcode OUT NOCOPY VARCHAR2)
428 is
429
430 L_EMPLOYEE_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%type;
431 L_USER_PERSON_TYPE VARCHAR2(60);
432 L_DATE_OF_BIRTH DATE;
433 L_TOWN_OF_BIRTH PER_ALL_PEOPLE_F.TOWN_OF_BIRTH%type;
434 L_COUNTRY_OF_BIRTH PER_ALL_PEOPLE_F.COUNTRY_OF_BIRTH%type;
435 L_DATE_OF_DEATH DATE;
436 L_ORIGINAL_DATE_OF_HIRE DATE;
437
438 L_EFFECTIVE_START_DATE DATE;
439
440 L_SEX VARCHAR2(30);
441 L_MARITAL_STATUS VARCHAR2(30);
442 L_FULL_NAME PER_ALL_PEOPLE_F.FULL_NAME%type;
443 L_PRE_NAME_ADJUNCT PER_ALL_PEOPLE_F.PRE_NAME_ADJUNCT%type;
444 L_SUFFIX VARCHAR2(30);
445 L_TITLE VARCHAR2(30);
446 L_LAST_NAME PER_ALL_PEOPLE_F.LAST_NAME%type;
447 L_FIRST_NAME PER_ALL_PEOPLE_F.FIRST_NAME%type;
448 L_MIDDLE_NAMES PER_ALL_PEOPLE_F.MIDDLE_NAMES%type;
449
450
451
452 L_ADDRESS_TYPE PER_ADDRESSES.ADDRESS_TYPE%type;
453 L_DATE_FROM DATE;
454 L_COUNTRY PER_ADDRESSES.COUNTRY%type;
455 L_ADDRESS_LINE1 PER_ADDRESSES.ADDRESS_LINE1%type;
456 L_ADDRESS_LINE2 PER_ADDRESSES.ADDRESS_LINE2%type;
457 L_ADDRESS_LINE3 PER_ADDRESSES.ADDRESS_LINE3%type;
458 L_TOWN_OR_CITY PER_ADDRESSES.TOWN_OR_CITY%type;
459 L_TELEPHONE_NUMBER_1 PER_ADDRESSES.TELEPHONE_NUMBER_1%type;
460 L_REGION_1 PER_ADDRESSES.REGION_1%type;
461 L_REGION_2 PER_ADDRESSES.REGION_1%type;
462 L_POSTAL_CODE PER_ADDRESSES.POSTAL_CODE%type;
463
464 L_EMAIL_ADDRESS PER_ALL_PEOPLE_F.EMAIL_ADDRESS%type;
465
466 L_PHONE_TYPE PER_PHONES.PHONE_TYPE%type;
467 L_PHONE_NUMBER PER_PHONES.PHONE_NUMBER%type;
468
469 L_NATIONALITY VARCHAR2(30);
470 L_NATIONAL_IDENTIFIER PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER%type;
471
472 --
473 /*Select state ment modified for the employee number
474 not getting displayed for Ex-Employee*/
475 cursor csr_person_data is
476 SELECT DECODE ( ppf.CURRENT_NPW_FLAG , 'Y', NPW_NUMBER,EMPLOYEE_NUMBER ) EMPLOYEE_NUMBER,
477 HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(SYSDATE , PPF.PERSON_ID) ,
478 DATE_OF_BIRTH,
479 TOWN_OF_BIRTH,
480 COUNTRY_OF_BIRTH,
481 DATE_OF_DEATH,
482 ORIGINAL_DATE_OF_HIRE,
483 EFFECTIVE_START_DATE,
484 HL1.MEANING SEX,
485 HL4.MEANING MARITAL_STATUS,
486 FULL_NAME,
487 PRE_NAME_ADJUNCT,
488 SUFFIX,
489 HL3.MEANING TITLE,
490 LAST_NAME,
491 FIRST_NAME,
492 MIDDLE_NAMES,
493 ADDRESS_TYPE,
494 padr.DATE_FROM,
495 COUNTRY,
496 ADDRESS_LINE1,
497 ADDRESS_LINE2,
498 ADDRESS_LINE3,
499 TOWN_OR_CITY,
500 TELEPHONE_NUMBER_1,
501 REGION_1,
502 REGION_2,
503 POSTAL_CODE,
504 EMAIL_ADDRESS,
505 PHONE_TYPE,
506 PHONE_NUMBER,
507 HL2.MEANING NATIONALITY,
508 NATIONAL_IDENTIFIER
509
510 FROM PER_ALL_PEOPLE_F ppf,
511 PER_ADDRESSES padr ,
512 PER_PHONES ppn ,
513 hr_lookups HL1 ,
514 HR_LOOKUPS HL2 ,
515 HR_LOOKUPS HL3 ,
516 HR_LOOKUPS HL4
517 WHERE ppf.person_id = padr.person_id (+)
518 AND ( padr.person_id is null
519 OR ( padr.person_id is not null
520 AND padr.primary_flag ='Y'
521 AND ppf.person_id = padr.person_id
522 and sysdate between padr.date_from and nvl (padr.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
523 ))
524 AND ppn.PARENT_ID (+) = PPF.PERSON_ID
525 -- Modified for the bug 6895752 starts here
526 /*AND ( ppn.parent_id is null
527 OR ( ppn.parent_id is not null
528 AND PPN.PARENT_TABLE = 'PER_ALL_PEOPLE_F'
529 AND PPN.PHONE_TYPE = 'W1' ))*/
530
531
532
533 AND PPN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
534 AND PPN.PHONE_TYPE (+) = 'W1'
535 -- Modified for the bug 6895752 ends here
536 AND ((ppf.CURRENT_EMPLOYEE_FLAG = 'Y'
537 OR ppf.person_id in -- modified for bug6873563
538 (SELECT nvl(pps.person_id , -100)
539 FROM per_periods_of_service pps
540 WHERE pps.person_id = ppf.person_id
541 AND pps.business_group_id = ppf.business_group_id
542 and ACTUAL_TERMINATION_DATE is not null
543 ))
544 OR ( ppf.CURRENT_NPW_FLAG = 'Y'
545 OR ppf.person_id in -- modified for bug6873563
546 (SELECT nvl(ppp.person_id , -100)
547 FROM per_periods_of_placement ppp
548 WHERE ppp.person_id = ppf.person_id
549 AND ppp.business_group_id = ppf.business_group_id
550 and ACTUAL_TERMINATION_DATE is not null
551 )))
552 AND HL1.LOOKUP_TYPE (+) = 'SEX'
553 AND HL1.LOOKUP_CODE (+) = ppf.SEX
554 AND HL2.LOOKUP_TYPE (+) = 'NATIONALITY'
555 AND HL2.LOOKUP_CODE (+) = Ppf.NATIONALITY
556 AND HL3.LOOKUP_TYPE (+) = 'TITLE'
557 AND HL3.LOOKUP_CODE (+) = PPF.TITLE
558 AND HL4.LOOKUP_TYPE (+) = 'MAR_STATUS'
559 AND HL4.LOOKUP_CODE (+) = PPF.MARITAL_STATUS
560 AND sysdate BETWEEN effective_start_date AND effective_end_date ;
561
562
563
564 cursor csr_psft_sync is
565 select COUNT ('1')
566 from hr_psft_sync_run
567 where process = 'PERSON_FULL_SYNC'
568 and run_date > sysdate
569 and status = 'STARTED';
570
571 l_dummy number;
572 l_current_date date;
573
574 begin
575 open csr_psft_sync;
576 fetch csr_psft_sync into l_dummy;
577 close csr_psft_sync;
578
579 if l_dummy = 0 then
580 l_current_date :=sysdate;
581
582 FND_FILE.NEW_LINE(FND_FILE.log, 1);
583 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
584 FND_FILE.put_line(fnd_file.log,'Person Full Synch Data Extraction Begins:'||to_char(l_current_date, 'DD/MM/RRRR HH:MI:SS'));
585 hr_full_sync_messages.insert_psft_sync_run(2,'PERSON_FULL_SYNC',errbuf,retcode);
586
587 open csr_person_data;
588 loop
589 fetch csr_person_data into L_EMPLOYEE_NUMBER,L_USER_PERSON_TYPE,L_DATE_OF_BIRTH,L_TOWN_OF_BIRTH,L_COUNTRY_OF_BIRTH
590 ,L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE,L_EFFECTIVE_START_DATE
591 , L_SEX,L_MARITAL_STATUS,L_FULL_NAME,L_PRE_NAME_ADJUNCT ,L_SUFFIX
592 ,L_TITLE,L_LAST_NAME,L_FIRST_NAME ,L_MIDDLE_NAMES, L_ADDRESS_TYPE ,L_DATE_FROM ,L_COUNTRY, L_ADDRESS_LINE1,
593 L_ADDRESS_LINE2,L_ADDRESS_LINE3,L_TOWN_OR_CITY ,L_TELEPHONE_NUMBER_1,L_REGION_1 ,L_REGION_2,
594 L_POSTAL_CODE, L_EMAIL_ADDRESS, L_PHONE_TYPE
595 ,L_PHONE_NUMBER,L_NATIONALITY ,L_NATIONAL_IDENTIFIER ;
596
597 exit when csr_person_data%notfound;
598 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,L_EMPLOYEE_NUMBER||fnd_global.local_chr(400)||L_USER_PERSON_TYPE||fnd_global.local_chr(400)||L_DATE_OF_BIRTH
599 ||fnd_global.local_chr(400)||L_TOWN_OF_BIRTH||fnd_global.local_chr(400)||L_COUNTRY_OF_BIRTH||fnd_global.local_chr(400)||L_DATE_OF_DEATH
600 ||fnd_global.local_chr(400)||L_ORIGINAL_DATE_OF_HIRE||fnd_global.local_chr(400)||L_EFFECTIVE_START_DATE||fnd_global.local_chr(400)||L_SEX
601 ||fnd_global.local_chr(400)||L_MARITAL_STATUS||fnd_global.local_chr(400)||L_FULL_NAME||fnd_global.local_chr(400)||L_PRE_NAME_ADJUNCT
602 ||fnd_global.local_chr(400)||L_SUFFIX||fnd_global.local_chr(400)||L_TITLE||fnd_global.local_chr(400)||L_LAST_NAME
603 ||fnd_global.local_chr(400)||L_FIRST_NAME||fnd_global.local_chr(400)||L_MIDDLE_NAMES||fnd_global.local_chr(400)||L_ADDRESS_TYPE
604 ||fnd_global.local_chr(400)||L_DATE_FROM||fnd_global.local_chr(400)||L_COUNTRY||fnd_global.local_chr(400)||L_ADDRESS_LINE1
605 ||fnd_global.local_chr(400)||L_ADDRESS_LINE2||fnd_global.local_chr(400)||L_ADDRESS_LINE3||fnd_global.local_chr(400)||L_TOWN_OR_CITY
606 ||fnd_global.local_chr(400)||L_TELEPHONE_NUMBER_1||fnd_global.local_chr(400)||L_REGION_1||fnd_global.local_chr(400)||L_REGION_2
607 ||fnd_global.local_chr(400)||L_PHONE_NUMBER||fnd_global.local_chr(400)||L_NATIONALITY||fnd_global.local_chr(400)||L_NATIONAL_IDENTIFIER
608 );
609 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
610 end loop;
611 close csr_person_data;
612
613 hr_full_sync_messages.update_psft_sync_run(1,'PERSON_FULL_SYNC',l_current_date,errbuf,retcode);
614 FND_FILE.NEW_LINE(FND_FILE.log, 1);
615 FND_FILE.put_line(fnd_file.log,'Person Full Synch Data Extraction Ends:'||to_char(l_current_date, 'DD/MM/RRRR HH:MI:SS'));
616
617 END if;
618
619 exception
620 when OTHERS then
621 hr_full_sync_messages.update_psft_sync_run(3,'PERSON_FULL_SYNC',l_current_date,errbuf,retcode);
622 errbuf := errbuf||SQLERRM;
623 retcode := '1';
624 FND_FILE.put_line(fnd_file.log, 'Error in Person Full Synch Data Extraction: '||SQLCODE);
625 FND_FILE.NEW_LINE(FND_FILE.log, 1);
626 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
627
628 end hr_person_full_sync;
629 /*Procedure to extract Country data for Full Synch messages Ends*/
630
631 /*Procedure to extract Job data for Full Synch messages Begins*/
632
633 PROCEDURE hr_jobcode_full_sync(errbuf OUT NOCOPY VARCHAR2
634 ,retcode OUT NOCOPY VARCHAR2)
635 IS
636
637 jobcode_full jobcode;
638 p_cnt number := 0 ;
639 p_eff_date DATE default sysdate;
640 l_current_date date default sysdate;
641
642 cursor fet_psft_sync is
643 select count('x')
644 from hr_psft_sync_run
645 where process = 'JOBCODE_FULL_SYNC'
646 and run_date < p_eff_date
647 and status = 'STARTED';
648
649 l_dummy number;
650
651 begin
652
653 open fet_psft_sync;
654 fetch fet_psft_sync into l_dummy;
655 close fet_psft_sync;
656
657 if l_dummy = 0
658 then
659
660 FND_FILE.NEW_LINE(FND_FILE.log, 1);
661 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
662 FND_FILE.put_line(fnd_file.log,'Job Code Full Synch Data Extraction Begins:'||to_char(l_current_date, 'DD/MM/RRRR HH:MI:SS'));
663 hr_full_sync_messages.insert_psft_sync_run(2,'JOBCODE_FULL_SYNC',errbuf,retcode);
664
665 OPEN jobcode_full FOR
666 SELECT BUSINESS_GROUP_ID SETID,
667 JOB_ID JOBCODE,
668 DATE_FROM EFFDT,
669 DECODE(DATE_TO,NULL,'ACTIVE','INACTIVE') EFF_STATUS,
670 NAME DESCR
671 FROM PER_JOBS
672 WHERE last_update_date <= p_eff_date;
673
674 LOOP
675 BEGIN
676 FETCH jobcode_full BULK COLLECT
677 INTO Jobcodefulltype.SETID
678 ,Jobcodefulltype.JOBCODE
679 ,Jobcodefulltype.EFFDT
680 ,Jobcodefulltype.EFF_STATUS
681 ,Jobcodefulltype.DESCR;
682
683
684 END;
685
686 if Jobcodefulltype.jobcode.count <=0 then
687 CLOSE jobcode_full;
688 EXIT;
689 end if;
690
691 p_cnt := p_cnt + Jobcodefulltype.jobcode.count;
692
693 if jobcode_full%NOTFOUND then
694 CLOSE jobcode_full;
695 EXIT;
696 end if;
697
698 END LOOP;
699
700 FOR I IN 1 .. p_cnt Loop
701
702 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
703 Jobcodefulltype.SETID(I)||fnd_global.local_chr(400)||
704 Jobcodefulltype.JOBCODE(I)||fnd_global.local_chr(400)||
705 Jobcodefulltype.EFFDT(I)||fnd_global.local_chr(400)||
706 Jobcodefulltype.EFF_STATUS(I)||fnd_global.local_chr(400)||
707 Jobcodefulltype.DESCR(I)
708 );
709 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
710 END Loop;
711 end if;
712 hr_full_sync_messages.update_psft_sync_run(1,'JOBCODE_FULL_SYNC',l_current_date,errbuf,retcode);
713 FND_FILE.NEW_LINE(FND_FILE.log, 1);
714 FND_FILE.put_line(fnd_file.log,'Job Code Full Synch Data Extraction Ends:'||to_char(l_current_date, 'DD/MM/RRRR HH:MI:SS'));
715
716 EXCEPTION WHEN OTHERS THEN
717 update_psft_sync_run(3,'JOBCODE_FULL_SYNC',l_current_date,errbuf,retcode);
718 errbuf := errbuf||SQLERRM;
719 retcode := '1';
720 FND_FILE.put_line(fnd_file.log, 'Error in jobcode_fullsync: '||SQLCODE);
721 FND_FILE.NEW_LINE(FND_FILE.log, 1);
722 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
723
724 END hr_jobcode_full_sync;
725
726 /*Procedure to extract Job data for Full Synch messages Ends*/
727
728 /*Procedure to extract Workforce data for Full Synch messages Begins*/
729
730 procedure hr_workforce_full_sync(errbuf OUT NOCOPY VARCHAR2
731 ,retcode OUT NOCOPY VARCHAR2)
732 is
733 p_eff_date DATE default sysdate;
734 workforce_full workforce;
735 p_cnt number default 0 ;
736 l_current_date date default sysdate;
737
738 cursor fet_psft_sync is
739 select count('x')
740 from hr_psft_sync_run
741 where process = 'WORKFORCE_FULL_SYNC'
742 and run_date < p_eff_date
743 and status = 'STARTED';
744 l_dummy number;
745
746 begin
747
748 open fet_psft_sync;
749 fetch fet_psft_sync into l_dummy;
750 close fet_psft_sync;
751
752 if l_dummy = 0
753 then
754
755 FND_FILE.NEW_LINE(FND_FILE.log, 1);
756 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
757 FND_FILE.put_line(fnd_file.log,'Workforce Full Synch Data Extraction Begins:'||to_char(l_current_date, 'DD/MM/RRRR HH:MI:SS'));
758 hr_full_sync_messages.insert_psft_sync_run(2,'WORKFORCE_FULL_SYNC',errbuf,retcode);
759
760 OPEN workforce_full FOR
761 SELECT ppf.employee_number,1 AS empl_rcd ,ppf.original_date_of_hire,
762 pas.probation_period,pas.effective_start_date effdt,pas.organization_id,
763 pas.job_id,pas.assignment_status_type_id,pas.location_id,
764 pas.employment_category,pas.business_group_id,pas.normal_hours,
765 pas.frequency,pas.grade_id,pas.supervisor_id,pas.EFFECTIVE_START_DATE,
766 nvl(pas.EFFECTIVE_END_DATE,sysdate) EFFECTIVE_END_DATE,
767 nvl(psf.step_id,0) Step_id
768 ,pos.final_process_date,pos.ACCEPTED_TERMINATION_DATE
769 FROM per_all_people_f ppf,per_all_assignments_f pas,
770 per_periods_of_service pos,PER_SPINAL_POINT_PLACEMENTS_F psf
771 WHERE pas.primary_flag='Y'
772 AND pos.person_id=pas.person_id
773 AND ppf.person_id = pos.person_id
774 AND pas.business_group_id = psf.business_group_id(+)
775 AND pas.assignment_id = psf.assignment_id(+)
776 AND ppf.BUSINESS_GROUP_ID = pas.BUSINESS_GROUP_ID
777 AND pas.effective_start_date BETWEEN ppf.effective_start_date(+) AND
778 ppf.effective_end_date(+)
779 AND pas.last_update_date < = sysdate;
780
781 LOOP
782 BEGIN
783 FETCH workforce_full BULK COLLECT
784 INTO WorkForceFullType.EMPLID
785 ,WorkForceFullType.EMPL_RCD
786 ,WorkForceFullType.ORIG_HIRE_DT
787 ,WorkForceFullType.PROBATION_DT
788 ,WorkForceFullType.EFFDT
789 ,WorkForceFullType.BUSINESS_UNIT
790 ,WorkForceFullType.JOBCODE
791 ,WorkForceFullType.EMPL_STATUS
792 ,WorkForceFullType.LOCATION
793 ,WorkForceFullType.FULL_PART_TIME
794 ,WorkForceFullType.COMPANY
795 ,WorkForceFullType.STD_HOURS
796 ,WorkForceFullType.STD_HRS_FREQUENCY
797 ,WorkForceFullType.GRADE
798 ,WorkForceFullType.SUPERVISOR_ID
799 ,WorkForceFullType.ASGN_START_DT
800 ,WorkForceFullType.ASGN_END_DT
801 ,WorkForceFullType.STEP
802 ,WorkForceFullType.TERMINATION_DT
803 ,WorkForceFullType.LAST_DATE_WORKED;
804
805
806 END;
807
808
809
810 if WorkForceFullType.EMPLID.count <=0 then
811 CLOSE workforce_full;
812 EXIT;
813 end if;
814
815 p_cnt := p_cnt + WorkForceFullType.EMPLID.count;
816
817 if workforce_full%NOTFOUND then
818 CLOSE workforce_full;
819 EXIT;
820 end if;
821
822 END LOOP;
823
824
825 FOR I IN 1 .. p_cnt Loop
826
827 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
828 WorkForceFullType.EMPLID(I)||fnd_global.local_chr(400)||
829 WorkForceFullType.EMPL_RCD(I)||fnd_global.local_chr(400)||
830 WorkForceFullType.ORIG_HIRE_DT(I)||fnd_global.local_chr(400)||
831 WorkForceFullType.PROBATION_DT(I)||fnd_global.local_chr(400)||
832 WorkForceFullType.EFFDT(I)||fnd_global.local_chr(400)||
833 WorkForceFullType.BUSINESS_UNIT(I)||fnd_global.local_chr(400)||
834 WorkForceFullType.JOBCODE(I)||fnd_global.local_chr(400)||
835 WorkForceFullType.EMPL_STATUS(I)||fnd_global.local_chr(400)||
836 WorkForceFullType.LOCATION(I)||fnd_global.local_chr(400)||
837 WorkForceFullType.FULL_PART_TIME(I)||fnd_global.local_chr(400)||
838 WorkForceFullType.COMPANY(I)||fnd_global.local_chr(400)||
839 WorkForceFullType.STD_HOURS(I)||fnd_global.local_chr(400)||
840 WorkForceFullType.STD_HRS_FREQUENCY(I)||fnd_global.local_chr(400)||
841 WorkForceFullType.GRADE(I)||fnd_global.local_chr(400)||
842 WorkForceFullType.SUPERVISOR_ID(I)||fnd_global.local_chr(400)||
843 WorkForceFullType.ASGN_START_DT(I)||fnd_global.local_chr(400)||
844 WorkForceFullType.ASGN_END_DT(I)||fnd_global.local_chr(400)||
845 WorkForceFullType.STEP(I)||fnd_global.local_chr(400)||
846 WorkForceFullType.TERMINATION_DT(I)||fnd_global.local_chr(400)||
847 WorkForceFullType.LAST_DATE_WORKED(I)
848 );
849
850 FND_FILE.NEW_LINE(FND_FILE.output, 1);
851 END Loop;
852
853 hr_full_sync_messages.update_psft_sync_run(1,'WORKFORCE_FULL_SYNC',l_current_date,errbuf,retcode);
854 FND_FILE.NEW_LINE(FND_FILE.log, 1);
855 FND_FILE.put_line(fnd_file.log,'Work Force Full Synch Data Extraction Ends:'||to_char(l_current_date, 'DD/MM/RRRR HH:MI:SS'));
856
857 End if;
858
859 EXCEPTION
860 WHEN OTHERS THEN
861 hr_full_sync_messages.update_psft_sync_run(3,'WORKFORCE_FULL_SYNC',l_current_date,errbuf,retcode);
862 errbuf := errbuf||SQLERRM;
863 retcode := '1';
864 FND_FILE.put_line(fnd_file.log, 'Error in workforce_fullsync: '||SQLCODE);
865 FND_FILE.NEW_LINE(FND_FILE.log, 1);
866 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
867
868 end hr_workforce_full_sync;
869
870 /*Procedure to extract the workforce data for full synch process ends*/
871
872 /*Procedure to extract the organization data for full synch process begins*/
873 procedure hr_organizaton_full_sync(errbuf OUT NOCOPY VARCHAR2
874 ,retcode OUT NOCOPY VARCHAR2)
875 is
876 p_bg_id hr_all_organization_units.business_group_id%type;
877 p_dept_id hr_all_organization_units.organization_id%type;
878 p_eff_date date;
879 p_loc_id hr_all_organization_units.location_id%type;
880 p_person_id per_org_manager_v.person_id%type;
881 p_full_name per_org_manager_v.full_name%type;
882 p_bg_name hr_all_organization_units.name%type;
883 p_eff_status varchar2(10);
884 p_effective_date date default sysdate;
885
886 cursor fet_org_fsync is
887 select org.business_group_id,
888 org.organization_id,
889 case when org.date_to is null then org.date_from
890 else org.date_to end,
891 case when org.date_to is null then 'ACTIVE'
892 else 'INACTIVE' end,
893 org.name,
894 org.location_id,
895 mgr.person_id,
896 mgr.full_name
897 from hr_all_organization_units org
898 ,per_org_manager_v mgr,hr_organization_information hrorg
899 where org.business_group_id = mgr.business_group_id(+)
900 and org.organization_id = mgr.organization_id(+)
901 and hrorg.organization_id = org.organization_id
902 and hrorg.org_information1 = 'HR_ORG'
903 and p_effective_date between org.date_from
904 and nvl(org.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
905 and p_effective_date between mgr.start_date(+) and mgr.end_date(+);
906
907
908
909 cursor fet_psft_sync is
910 select count('x')
911 from hr_psft_sync_run
912 where process = 'ORG_FULL_SYNC'
913 and run_date < p_effective_date
914 and status = 'STARTED';
915
916 l_dummy number;
917
918 begin
919
920 open fet_psft_sync;
921 fetch fet_psft_sync into l_dummy;
922 close fet_psft_sync;
923 if l_dummy = 0
924 then
925 FND_FILE.NEW_LINE(FND_FILE.log, 1);
926 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
927 FND_FILE.put_line(fnd_file.log,'Organization Full Synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
928 hr_full_sync_messages.insert_psft_sync_run(2,'ORG_FULL_SYNC',errbuf,retcode);
929 open fet_org_fsync;
930 loop
931 fetch fet_org_fsync into p_bg_id,p_dept_id,p_eff_date,p_eff_status,p_bg_name,p_loc_id,p_person_id,p_full_name;
932 exit when fet_org_fsync%notfound;
933
934 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_bg_id||fnd_global.local_chr(400)||p_dept_id||fnd_global.local_chr(400)||p_eff_date||
935 fnd_global.local_chr(400)||
936 p_eff_status||fnd_global.local_chr(400)||p_bg_name||fnd_global.local_chr(400)||
937 p_loc_id||fnd_global.local_chr(400)||p_person_id||fnd_global.local_chr(400)||p_full_name);
938 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
939 end loop;
940 close fet_org_fsync;
941
942
943 hr_full_sync_messages.update_psft_sync_run(1,'ORG_FULL_SYNC',p_effective_date,errbuf,retcode);
944 FND_FILE.NEW_LINE(FND_FILE.log, 1);
945 FND_FILE.put_line(fnd_file.log,'Organization Full Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
946
947 end if;
948
949 exception
950 when OTHERS then
951 hr_full_sync_messages.update_psft_sync_run(3,'ORG_FULL_SYNC',p_effective_date,errbuf,retcode);
952 errbuf := errbuf||SQLERRM;
953 retcode := '1';
954 FND_FILE.put_line(fnd_file.log, 'Error in Organization Data Full Synch Extraction: '||SQLCODE);
955 FND_FILE.NEW_LINE(FND_FILE.log, 1);
956 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
957 end hr_organizaton_full_sync;
958 /*Procedure to extract the organization data for full synch process ends*/
959
960 /*Procedure to extract the business group data for full synch process begins*/
961 procedure hr_businessgrp_full_sync(errbuf OUT NOCOPY VARCHAR2
962 ,retcode OUT NOCOPY VARCHAR2)
963 is
964
965 p_bg_id PER_BUSINESS_GROUPS.business_group_id%type;
966 p_bg_name PER_BUSINESS_GROUPS.name%type;
967 p_eff_status varchar2(10);
968 p_eff_date date;
969 p_effective_date date default sysdate;
970
971 cursor fet_bg_fsync is
972 select business_group_id,
973 name,
974 case when date_to is null then date_from
975 else date_to end,
976 case when date_to is null then 'ACTIVE'
977 else 'INACTIVE' end
978 from PER_BUSINESS_GROUPS
979 where p_effective_date between date_from and
980 nvl (date_to, to_date('31-12-4712', 'DD-MM-YYYY'));
981
982
983 cursor fet_psft_sync is
984 select count('x')
985 from hr_psft_sync_run
986 where process = 'BG_FULL_SYNC'
987 and run_date < p_effective_date
988 and status = 'STARTED';
989
990 l_dummy number;
991
992 begin
993
994 open fet_psft_sync;
995 fetch fet_psft_sync into l_dummy;
996 close fet_psft_sync;
997 if l_dummy = 0
998 then
999 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1000 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1001 FND_FILE.put_line(fnd_file.log,'Business Group Full Synch Data Extraction Begins:'
1002 ||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1003 hr_full_sync_messages.insert_psft_sync_run(2,'BG_FULL_SYNC',errbuf,retcode);
1004 open fet_bg_fsync;
1005 loop
1006 fetch fet_bg_fsync into p_bg_id,p_bg_name,p_eff_date,p_eff_status;
1007 exit when fet_bg_fsync%notfound;
1008 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_bg_id||fnd_global.local_chr(400)||p_bg_name||fnd_global.local_chr(400)||
1009 p_eff_date||fnd_global.local_chr(400)||p_eff_status);
1010 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1011 end loop;
1012 close fet_bg_fsync;
1013
1014
1015 hr_full_sync_messages.update_psft_sync_run(1,'BG_FULL_SYNC',p_effective_date,errbuf,retcode);
1016 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1017 FND_FILE.put_line(fnd_file.log,'Business Group Full Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1018
1019 end if;
1020
1021 exception
1022 when OTHERS then
1023 hr_full_sync_messages.update_psft_sync_run(3,'BG_FULL_SYNC',p_effective_date,errbuf,retcode);
1024 errbuf := errbuf||SQLERRM;
1025 retcode := '1';
1026 FND_FILE.put_line(fnd_file.log, 'Error in Business Group Data Full Synch Extraction: '||SQLCODE);
1027 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1028 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
1029 end hr_businessgrp_full_sync;
1030 /*Procedure to extract the business group data for full synch process ends*/
1031
1032 /*Procedure to extract the payroll group data for full synch process begins*/
1033 procedure hr_payroll_full_sync(errbuf OUT NOCOPY VARCHAR2
1034 ,retcode OUT NOCOPY VARCHAR2)
1035 is
1036 p_pyrl_id pay_all_payrolls_f.payroll_id%type;
1037 p_pyrl_name pay_all_payrolls_f.payroll_name%type;
1038 p_bg_id pay_all_payrolls_f.business_group_id%type;
1039 p_eff_date date;
1040 p_eff_status varchar2(10);
1041 p_effective_date date default sysdate;
1042
1043 cursor fet_pyrl_fsync is
1044 select payroll_id,
1045 payroll_name,
1046 business_group_id,
1047 case when p_effective_date > add_months(first_period_end_date,NUMBER_OF_YEARS*12)
1048 then add_months(first_period_end_date,NUMBER_OF_YEARS*12) else (select min(effective_start_date) from
1049 pay_all_payrolls_f pay1
1050 where pay1.payroll_id = pay.payroll_id
1051 and pay1.business_group_id = pay.business_group_id) end,
1052 case when p_effective_date > add_months(first_period_end_date,NUMBER_OF_YEARS*12)
1053 then 'INACTIVE' else 'ACTIVE' end
1054 from pay_all_payrolls_f pay
1055 where p_effective_date between effective_start_date and effective_end_date;
1056
1057
1058 cursor fet_psft_sync is
1059 select count('x')
1060 from hr_psft_sync_run
1061 where process = 'PYRL_FULL_SYNC'
1062 and run_date < p_effective_date
1063 and status = 'STARTED';
1064
1065 l_dummy number;
1066
1067 begin
1068
1069 open fet_psft_sync;
1070 fetch fet_psft_sync into l_dummy;
1071 close fet_psft_sync;
1072 if l_dummy = 0
1073 then
1074 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1075 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1076 FND_FILE.put_line(fnd_file.log,'Payroll Full Synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1077 hr_full_sync_messages.insert_psft_sync_run(2,'PYRL_FULL_SYNC',errbuf,retcode);
1078 open fet_pyrl_fsync;
1079 loop
1080
1081 fetch fet_pyrl_fsync into p_pyrl_id,p_pyrl_name,p_bg_id,p_eff_date,p_eff_status;
1082 exit when fet_pyrl_fsync%notfound;
1083 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_pyrl_id||fnd_global.local_chr(400)||
1084 p_pyrl_name||fnd_global.local_chr(400)||p_bg_id||fnd_global.local_chr(400)||p_eff_date||fnd_global.local_chr(400)||p_eff_status);
1085 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1086 end loop;
1087 close fet_pyrl_fsync;
1088
1089
1090 hr_full_sync_messages.update_psft_sync_run(1,'PYRL_FULL_SYNC',p_effective_date,errbuf,retcode);
1091 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1092 FND_FILE.put_line(fnd_file.log,'Payroll Full Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1093
1094 end if;
1095
1096 exception
1097 when OTHERS then
1098 hr_full_sync_messages.update_psft_sync_run(3,'PYRL_FULL_SYNC',p_effective_date,errbuf,retcode);
1099 errbuf := errbuf||SQLERRM;
1100 retcode := '1';
1101 FND_FILE.put_line(fnd_file.log, 'Error in Payroll Data Full Synch Extraction: '||SQLCODE);
1102 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1103 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
1104 end hr_payroll_full_sync;
1105 /*Procedure to extract the payroll group data for full synch process ends*/
1106
1107 /*Common Procedure called from concurrent program begins*/
1108 procedure hr_full_sync (ERRBUF OUT NOCOPY varchar2,
1109 RETCODE OUT NOCOPY number,
1110 p_process_name in varchar2)
1111 is
1112 begin
1113
1114 if p_process_name = 'STATE_FULL_SYNCH'
1115 then
1116 hr_full_sync_messages.hr_state_full_sync(ERRBUF,RETCODE);
1117 elsif p_process_name = 'COUNTRY_FULL_SYNCH'
1118 then
1119 hr_full_sync_messages.hr_country_full_sync(ERRBUF,RETCODE);
1120 elsif p_process_name = 'LOCATION_FULL_SYNCH'
1121 then
1122 hr_full_sync_messages.hr_location_full_sync(ERRBUF,RETCODE);
1123 elsif p_process_name = 'PERSON_FULL_SYNCH'
1124 then
1125 hr_full_sync_messages.hr_person_full_sync(ERRBUF,RETCODE);
1126 elsif p_process_name = 'WORKFORCE_FULL_SYNCH'
1127 then
1128 hr_full_sync_messages.hr_workforce_full_sync(ERRBUF,RETCODE);
1129 elsif p_process_name = 'JOBCODE_FULL_SYNCH' then
1130 hr_full_sync_messages.hr_jobcode_full_sync(ERRBUF,RETCODE);
1131 elsif p_process_name = 'ORGANIZATION_FULL_SYNCH' then
1132 hr_full_sync_messages.hr_organizaton_full_sync(ERRBUF,RETCODE);
1133 elsif p_process_name = 'BUSINESSGROUP_FULL_SYNCH' then
1134 hr_full_sync_messages.hr_businessgrp_full_sync(ERRBUF,RETCODE);
1135 elsif p_process_name = 'PAYROLL_FULL_SYNCH' then
1136 hr_full_sync_messages.hr_payroll_full_sync(ERRBUF,RETCODE);
1137 end if;
1138 end hr_full_sync;
1139 /*Common Procedure called from concurrent program ends*/
1140
1141 end hr_full_sync_messages;