[Home] [Help]
PACKAGE BODY: APPS.HR_FULL_SYNC_MESSAGES
Source
1 PACKAGE BODY HR_FULL_SYNC_MESSAGES as
2 /* $Header: perhrhdfull.pkb 120.9 2008/03/19 09:52:12 sathkris 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
353
354 cursor fet_psft_sync is
355 select count('x')
356 from hr_psft_sync_run
357 where process = 'LOC_FULL_SYNC'
358 and run_date < p_effective_date
359 and status = 'STARTED';
360
361
362
363 l_dummy number;
364
365 begin
366
367 open fet_psft_sync;
368 fetch fet_psft_sync into l_dummy;
369 close fet_psft_sync;
370 if l_dummy = 0
371 then
372 FND_FILE.NEW_LINE(FND_FILE.log, 1);
373 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
374 FND_FILE.put_line(fnd_file.log,'Location Full Synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
375 hr_full_sync_messages.insert_psft_sync_run(2,'LOC_FULL_SYNC',errbuf,retcode);
376
377 open fet_loc_sync;
378 loop
379 fetch fet_loc_sync into p_bg_id,p_loc_id,p_active_date,p_effecive_status,
380 p_loc_code, p_loc_desc, p_loc_style , p_country, p_add_line_1, p_add_line_2, p_add_line_3,
381 p_town_or_city,p_region_1,p_region_2,p_region_3,p_postal_code,p_tel_no_1,p_tel_no_2 ,
382 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,
383 p_loc_info_19,p_loc_info_20;
384 exit when fet_loc_sync%notfound;
385 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_bg_id||fnd_global.local_chr(400)||p_loc_id||
386 fnd_global.local_chr(400)||p_active_date||
387 fnd_global.local_chr(400)||p_effecive_status||fnd_global.local_chr(400)||
388 p_loc_code||fnd_global.local_chr(400)|| p_loc_desc||fnd_global.local_chr(400)||'ADDRESS_START'||
389 fnd_global.local_chr(400)||p_loc_style ||fnd_global.local_chr(400)|| p_add_line_1||
390 fnd_global.local_chr(400)|| p_add_line_2||fnd_global.local_chr(400)|| p_add_line_3||
391 fnd_global.local_chr(400)||p_town_or_city||fnd_global.local_chr(400)||p_country||
392 fnd_global.local_chr(400)||p_postal_code||fnd_global.local_chr(400)||p_region_1||
393 fnd_global.local_chr(400)||p_region_2||fnd_global.local_chr(400)||p_region_3||
394 fnd_global.local_chr(400)||p_tel_no_1||fnd_global.local_chr(400)||p_tel_no_2 ||
395 fnd_global.local_chr(400)||p_tel_no_3||fnd_global.local_chr(400)||p_loc_info_13||
396 fnd_global.local_chr(400)|| p_loc_info_14||fnd_global.local_chr(400)||p_loc_info_15||
397 fnd_global.local_chr(400)||p_loc_info_16||fnd_global.local_chr(400)||p_loc_info_17||
398 fnd_global.local_chr(400)||p_loc_info_18||fnd_global.local_chr(400)||
399 p_loc_info_19||fnd_global.local_chr(400)||p_loc_info_20||fnd_global.local_chr(400)||'ADDRESS_END'||
400 fnd_global.local_chr(400));
401 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
402 end loop;
403 close fet_loc_sync;
404
405 hr_full_sync_messages.update_psft_sync_run(1,'LOC_FULL_SYNC',p_effective_date,errbuf,retcode);
406 FND_FILE.NEW_LINE(FND_FILE.log, 1);
407 FND_FILE.put_line(fnd_file.log,'Location Full Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
408
409 end if;
410
411 exception
412 when OTHERS then
413 hr_full_sync_messages.update_psft_sync_run(3,'LOC_FULL_SYNC',p_effective_date,errbuf,retcode);
414 errbuf := errbuf||SQLERRM;
415 retcode := '1';
416 FND_FILE.put_line(fnd_file.log, 'Error in Location Full Synch Data Extraction: '||SQLCODE);
417 FND_FILE.NEW_LINE(FND_FILE.log, 1);
418 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
419
420 end HR_LOCATION_FULL_SYNC;
421 /*Procedure to extract Location data for Full Synch messages Ends*/
422
423 /*Procedure to extract Person data for Full Synch messages Begins*/
424
425 procedure hr_person_full_sync(errbuf OUT NOCOPY VARCHAR2
426 ,retcode OUT NOCOPY VARCHAR2)
427 is
428
429 L_EMPLOYEE_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%type;
430 L_USER_PERSON_TYPE VARCHAR2(60);
431 L_DATE_OF_BIRTH DATE;
432 L_TOWN_OF_BIRTH PER_ALL_PEOPLE_F.TOWN_OF_BIRTH%type;
433 L_COUNTRY_OF_BIRTH PER_ALL_PEOPLE_F.COUNTRY_OF_BIRTH%type;
434 L_DATE_OF_DEATH DATE;
435 L_ORIGINAL_DATE_OF_HIRE DATE;
436
437 L_EFFECTIVE_START_DATE DATE;
438
439 L_SEX VARCHAR2(30);
440 L_MARITAL_STATUS VARCHAR2(30);
441 L_FULL_NAME PER_ALL_PEOPLE_F.FULL_NAME%type;
442 L_PRE_NAME_ADJUNCT PER_ALL_PEOPLE_F.PRE_NAME_ADJUNCT%type;
443 L_SUFFIX VARCHAR2(30);
444 L_TITLE VARCHAR2(30);
445 L_LAST_NAME PER_ALL_PEOPLE_F.LAST_NAME%type;
446 L_FIRST_NAME PER_ALL_PEOPLE_F.FIRST_NAME%type;
447 L_MIDDLE_NAMES PER_ALL_PEOPLE_F.MIDDLE_NAMES%type;
448
449
450
451 L_ADDRESS_TYPE PER_ADDRESSES.ADDRESS_TYPE%type;
452 L_DATE_FROM DATE;
453 L_COUNTRY PER_ADDRESSES.COUNTRY%type;
454 L_ADDRESS_LINE1 PER_ADDRESSES.ADDRESS_LINE1%type;
455 L_ADDRESS_LINE2 PER_ADDRESSES.ADDRESS_LINE2%type;
456 L_ADDRESS_LINE3 PER_ADDRESSES.ADDRESS_LINE3%type;
457 L_TOWN_OR_CITY PER_ADDRESSES.TOWN_OR_CITY%type;
458 L_TELEPHONE_NUMBER_1 PER_ADDRESSES.TELEPHONE_NUMBER_1%type;
459 L_REGION_1 PER_ADDRESSES.REGION_1%type;
460 L_REGION_2 PER_ADDRESSES.REGION_1%type;
461 L_POSTAL_CODE PER_ADDRESSES.POSTAL_CODE%type;
462
463 L_EMAIL_ADDRESS PER_ALL_PEOPLE_F.EMAIL_ADDRESS%type;
464
465 L_PHONE_TYPE PER_PHONES.PHONE_TYPE%type;
466 L_PHONE_NUMBER PER_PHONES.PHONE_NUMBER%type;
467
468 L_NATIONALITY VARCHAR2(30);
469 L_NATIONAL_IDENTIFIER PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER%type;
470
471 --
472 /*Select state ment modified for the employee number
473 not getting displayed for Ex-Employee*/
474 cursor csr_person_data is
475 SELECT DECODE ( ppf.CURRENT_NPW_FLAG , 'Y', NPW_NUMBER,EMPLOYEE_NUMBER ) EMPLOYEE_NUMBER,
476 HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(SYSDATE , PPF.PERSON_ID) ,
477 DATE_OF_BIRTH,
478 TOWN_OF_BIRTH,
479 COUNTRY_OF_BIRTH,
480 DATE_OF_DEATH,
481 ORIGINAL_DATE_OF_HIRE,
482 EFFECTIVE_START_DATE,
483 HL1.MEANING SEX,
484 HL4.MEANING MARITAL_STATUS,
485 FULL_NAME,
486 PRE_NAME_ADJUNCT,
487 SUFFIX,
488 HL3.MEANING TITLE,
489 LAST_NAME,
490 FIRST_NAME,
491 MIDDLE_NAMES,
492 ADDRESS_TYPE,
493 padr.DATE_FROM,
494 COUNTRY,
495 ADDRESS_LINE1,
496 ADDRESS_LINE2,
497 ADDRESS_LINE3,
498 TOWN_OR_CITY,
499 TELEPHONE_NUMBER_1,
500 REGION_1,
501 REGION_2,
502 POSTAL_CODE,
503 EMAIL_ADDRESS,
504 PHONE_TYPE,
505 PHONE_NUMBER,
506 HL2.MEANING NATIONALITY,
507 NATIONAL_IDENTIFIER
508
509 FROM PER_ALL_PEOPLE_F ppf,
510 PER_ADDRESSES padr ,
511 PER_PHONES ppn ,
512 hr_lookups HL1 ,
513 HR_LOOKUPS HL2 ,
514 HR_LOOKUPS HL3 ,
515 HR_LOOKUPS HL4
516 WHERE ppf.person_id = padr.person_id (+)
517 AND ( padr.person_id is null
518 OR ( padr.person_id is not null
519 AND padr.primary_flag ='Y'
520 AND ppf.person_id = padr.person_id
521 and sysdate between padr.date_from and nvl (padr.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
522 ))
523 AND ppn.PARENT_ID (+) = PPF.PERSON_ID
524 -- Modified for the bug 6895752 starts here
525 /*AND ( ppn.parent_id is null
526 OR ( ppn.parent_id is not null
527 AND PPN.PARENT_TABLE = 'PER_ALL_PEOPLE_F'
528 AND PPN.PHONE_TYPE = 'W1' ))*/
529
530
531
532 AND PPN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
533 AND PPN.PHONE_TYPE (+) = 'W1'
534 -- Modified for the bug 6895752 ends here
535 AND ((ppf.CURRENT_EMPLOYEE_FLAG = 'Y'
536 OR ppf.person_id in -- modified for bug6873563
537 (SELECT nvl(pps.person_id , -100)
538 FROM per_periods_of_service pps
539 WHERE pps.person_id = ppf.person_id
540 AND pps.business_group_id = ppf.business_group_id
541 and ACTUAL_TERMINATION_DATE is not null
542 ))
543 OR ( ppf.CURRENT_NPW_FLAG = 'Y'
544 OR ppf.person_id in -- modified for bug6873563
545 (SELECT nvl(ppp.person_id , -100)
546 FROM per_periods_of_placement ppp
547 WHERE ppp.person_id = ppf.person_id
548 AND ppp.business_group_id = ppf.business_group_id
549 and ACTUAL_TERMINATION_DATE is not null
550 )))
551 AND HL1.LOOKUP_TYPE (+) = 'SEX'
552 AND HL1.LOOKUP_CODE (+) = ppf.SEX
553 AND HL2.LOOKUP_TYPE (+) = 'NATIONALITY'
554 AND HL2.LOOKUP_CODE (+) = Ppf.NATIONALITY
555 AND HL3.LOOKUP_TYPE (+) = 'TITLE'
556 AND HL3.LOOKUP_CODE (+) = PPF.TITLE
557 AND HL4.LOOKUP_TYPE (+) = 'MAR_STATUS'
558 AND HL4.LOOKUP_CODE (+) = PPF.MARITAL_STATUS
559 AND sysdate BETWEEN effective_start_date AND effective_end_date ;
560
561
562
563 cursor csr_psft_sync is
564 select COUNT ('1')
565 from hr_psft_sync_run
566 where process = 'PERSON_FULL_SYNC'
567 and run_date > sysdate
568 and status = 'STARTED';
569
570 l_dummy number;
571 l_current_date date;
572
573 begin
574 open csr_psft_sync;
575 fetch csr_psft_sync into l_dummy;
576 close csr_psft_sync;
577
578 if l_dummy = 0 then
579 l_current_date :=sysdate;
580
581 FND_FILE.NEW_LINE(FND_FILE.log, 1);
582 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
583 FND_FILE.put_line(fnd_file.log,'Person Full Synch Data Extraction Begins:'||to_char(l_current_date, 'DD/MM/RRRR HH:MI:SS'));
584 hr_full_sync_messages.insert_psft_sync_run(2,'PERSON_FULL_SYNC',errbuf,retcode);
585
586 open csr_person_data;
587 loop
588 fetch csr_person_data into L_EMPLOYEE_NUMBER,L_USER_PERSON_TYPE,L_DATE_OF_BIRTH,L_TOWN_OF_BIRTH,L_COUNTRY_OF_BIRTH
589 ,L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE,L_EFFECTIVE_START_DATE
590 , L_SEX,L_MARITAL_STATUS,L_FULL_NAME,L_PRE_NAME_ADJUNCT ,L_SUFFIX
591 ,L_TITLE,L_LAST_NAME,L_FIRST_NAME ,L_MIDDLE_NAMES, L_ADDRESS_TYPE ,L_DATE_FROM ,L_COUNTRY, L_ADDRESS_LINE1,
592 L_ADDRESS_LINE2,L_ADDRESS_LINE3,L_TOWN_OR_CITY ,L_TELEPHONE_NUMBER_1,L_REGION_1 ,L_REGION_2,
593 L_POSTAL_CODE, L_EMAIL_ADDRESS, L_PHONE_TYPE
594 ,L_PHONE_NUMBER,L_NATIONALITY ,L_NATIONAL_IDENTIFIER ;
595
596 exit when csr_person_data%notfound;
597 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
598 ||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
599 ||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
600 ||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
601 ||fnd_global.local_chr(400)||L_SUFFIX||fnd_global.local_chr(400)||L_TITLE||fnd_global.local_chr(400)||L_LAST_NAME
602 ||fnd_global.local_chr(400)||L_FIRST_NAME||fnd_global.local_chr(400)||L_MIDDLE_NAMES||fnd_global.local_chr(400)||L_ADDRESS_TYPE
603 ||fnd_global.local_chr(400)||L_DATE_FROM||fnd_global.local_chr(400)||L_COUNTRY||fnd_global.local_chr(400)||L_ADDRESS_LINE1
604 ||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
605 ||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
606 ||fnd_global.local_chr(400)||L_PHONE_NUMBER||fnd_global.local_chr(400)||L_NATIONALITY||fnd_global.local_chr(400)||L_NATIONAL_IDENTIFIER
607 );
608 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
609 end loop;
610 close csr_person_data;
611
612 hr_full_sync_messages.update_psft_sync_run(1,'PERSON_FULL_SYNC',l_current_date,errbuf,retcode);
613 FND_FILE.NEW_LINE(FND_FILE.log, 1);
614 FND_FILE.put_line(fnd_file.log,'Person Full Synch Data Extraction Ends:'||to_char(l_current_date, 'DD/MM/RRRR HH:MI:SS'));
615
616 END if;
617
618 exception
619 when OTHERS then
620 hr_full_sync_messages.update_psft_sync_run(3,'PERSON_FULL_SYNC',l_current_date,errbuf,retcode);
621 errbuf := errbuf||SQLERRM;
622 retcode := '1';
623 FND_FILE.put_line(fnd_file.log, 'Error in Person Full Synch Data Extraction: '||SQLCODE);
624 FND_FILE.NEW_LINE(FND_FILE.log, 1);
625 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
626
627 end hr_person_full_sync;
628 /*Procedure to extract Country data for Full Synch messages Ends*/
629
630 /*Procedure to extract Job data for Full Synch messages Begins*/
631
632 PROCEDURE hr_jobcode_full_sync(errbuf OUT NOCOPY VARCHAR2
633 ,retcode OUT NOCOPY VARCHAR2)
634 IS
635
636 jobcode_full jobcode;
637 p_cnt number := 0 ;
638 p_eff_date DATE default sysdate;
639 l_current_date date default sysdate;
640
641 cursor fet_psft_sync is
642 select count('x')
643 from hr_psft_sync_run
644 where process = 'JOBCODE_FULL_SYNC'
645 and run_date < p_eff_date
646 and status = 'STARTED';
647
648 l_dummy number;
649
650 begin
651
652 open fet_psft_sync;
653 fetch fet_psft_sync into l_dummy;
654 close fet_psft_sync;
655
656 if l_dummy = 0
657 then
658
659 FND_FILE.NEW_LINE(FND_FILE.log, 1);
660 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
661 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'));
662 hr_full_sync_messages.insert_psft_sync_run(2,'JOBCODE_FULL_SYNC',errbuf,retcode);
663
664 OPEN jobcode_full FOR
665 SELECT BUSINESS_GROUP_ID SETID,
666 JOB_ID JOBCODE,
667 DATE_FROM EFFDT,
668 DECODE(DATE_TO,NULL,'ACTIVE','INACTIVE') EFF_STATUS,
669 NAME DESCR
670 FROM PER_JOBS
671 WHERE last_update_date <= p_eff_date;
672
673 LOOP
674 BEGIN
675 FETCH jobcode_full BULK COLLECT
676 INTO Jobcodefulltype.SETID
677 ,Jobcodefulltype.JOBCODE
678 ,Jobcodefulltype.EFFDT
679 ,Jobcodefulltype.EFF_STATUS
680 ,Jobcodefulltype.DESCR;
681
682
683 END;
684
685 if Jobcodefulltype.jobcode.count <=0 then
686 CLOSE jobcode_full;
687 EXIT;
688 end if;
689
690 p_cnt := p_cnt + Jobcodefulltype.jobcode.count;
691
692 if jobcode_full%NOTFOUND then
693 CLOSE jobcode_full;
694 EXIT;
695 end if;
696
697 END LOOP;
698
699 FOR I IN 1 .. p_cnt Loop
700
701 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
702 Jobcodefulltype.SETID(I)||fnd_global.local_chr(400)||
703 Jobcodefulltype.JOBCODE(I)||fnd_global.local_chr(400)||
704 Jobcodefulltype.EFFDT(I)||fnd_global.local_chr(400)||
705 Jobcodefulltype.EFF_STATUS(I)||fnd_global.local_chr(400)||
706 Jobcodefulltype.DESCR(I)
707 );
708 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
709 END Loop;
710 end if;
711 hr_full_sync_messages.update_psft_sync_run(1,'JOBCODE_FULL_SYNC',l_current_date,errbuf,retcode);
712 FND_FILE.NEW_LINE(FND_FILE.log, 1);
713 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'));
714
715 EXCEPTION WHEN OTHERS THEN
716 update_psft_sync_run(3,'JOBCODE_FULL_SYNC',l_current_date,errbuf,retcode);
717 errbuf := errbuf||SQLERRM;
718 retcode := '1';
719 FND_FILE.put_line(fnd_file.log, 'Error in jobcode_fullsync: '||SQLCODE);
720 FND_FILE.NEW_LINE(FND_FILE.log, 1);
721 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
722
723 END hr_jobcode_full_sync;
724
725 /*Procedure to extract Job data for Full Synch messages Ends*/
726
727 /*Procedure to extract Workforce data for Full Synch messages Begins*/
728
729 procedure hr_workforce_full_sync(errbuf OUT NOCOPY VARCHAR2
730 ,retcode OUT NOCOPY VARCHAR2)
731 is
732 p_eff_date DATE default sysdate;
733 workforce_full workforce;
734 p_cnt number default 0 ;
735 l_current_date date default sysdate;
736
737 cursor fet_psft_sync is
738 select count('x')
739 from hr_psft_sync_run
740 where process = 'WORKFORCE_FULL_SYNC'
741 and run_date < p_eff_date
742 and status = 'STARTED';
743 l_dummy number;
744
745 begin
746
747 open fet_psft_sync;
748 fetch fet_psft_sync into l_dummy;
749 close fet_psft_sync;
750
751 if l_dummy = 0
752 then
753
754 FND_FILE.NEW_LINE(FND_FILE.log, 1);
755 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
756 FND_FILE.put_line(fnd_file.log,'Workforce Full Synch Data Extraction Begins:'||to_char(l_current_date, 'DD/MM/RRRR HH:MI:SS'));
757 hr_full_sync_messages.insert_psft_sync_run(2,'WORKFORCE_FULL_SYNC',errbuf,retcode);
758
759 OPEN workforce_full FOR
760 SELECT ppf.employee_number,1 AS empl_rcd ,ppf.original_date_of_hire,
761 pas.probation_period,pas.effective_start_date effdt,pas.organization_id,
762 pas.job_id,pas.assignment_status_type_id,pas.location_id,
763 pas.employment_category,pas.business_group_id,pas.normal_hours,
764 pas.frequency,pas.grade_id,pas.supervisor_id,pas.EFFECTIVE_START_DATE,
765 nvl(pas.EFFECTIVE_END_DATE,sysdate) EFFECTIVE_END_DATE,
766 nvl(psf.step_id,0) Step_id
767 ,pos.final_process_date,pos.ACCEPTED_TERMINATION_DATE
768 FROM per_all_people_f ppf,per_all_assignments_f pas,
769 per_periods_of_service pos,PER_SPINAL_POINT_PLACEMENTS_F psf
770 WHERE pas.primary_flag='Y'
771 AND pos.person_id=pas.person_id
772 AND ppf.person_id = pos.person_id
773 AND pas.business_group_id = psf.business_group_id(+)
774 AND pas.assignment_id = psf.assignment_id(+)
775 AND ppf.BUSINESS_GROUP_ID = pas.BUSINESS_GROUP_ID
776 AND pas.effective_start_date BETWEEN ppf.effective_start_date(+) AND
777 ppf.effective_end_date(+)
778 AND pas.last_update_date < = sysdate;
779
780 LOOP
781 BEGIN
782 FETCH workforce_full BULK COLLECT
783 INTO WorkForceFullType.EMPLID
784 ,WorkForceFullType.EMPL_RCD
785 ,WorkForceFullType.ORIG_HIRE_DT
786 ,WorkForceFullType.PROBATION_DT
787 ,WorkForceFullType.EFFDT
788 ,WorkForceFullType.BUSINESS_UNIT
789 ,WorkForceFullType.JOBCODE
790 ,WorkForceFullType.EMPL_STATUS
791 ,WorkForceFullType.LOCATION
792 ,WorkForceFullType.FULL_PART_TIME
793 ,WorkForceFullType.COMPANY
794 ,WorkForceFullType.STD_HOURS
795 ,WorkForceFullType.STD_HRS_FREQUENCY
796 ,WorkForceFullType.GRADE
797 ,WorkForceFullType.SUPERVISOR_ID
798 ,WorkForceFullType.ASGN_START_DT
799 ,WorkForceFullType.ASGN_END_DT
800 ,WorkForceFullType.STEP
801 ,WorkForceFullType.TERMINATION_DT
802 ,WorkForceFullType.LAST_DATE_WORKED;
803
804
805 END;
806
807
808
809 if WorkForceFullType.EMPLID.count <=0 then
810 CLOSE workforce_full;
811 EXIT;
812 end if;
813
814 p_cnt := p_cnt + WorkForceFullType.EMPLID.count;
815
816 if workforce_full%NOTFOUND then
817 CLOSE workforce_full;
818 EXIT;
819 end if;
820
821 END LOOP;
822
823
824 FOR I IN 1 .. p_cnt Loop
825
826 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
827 WorkForceFullType.EMPLID(I)||fnd_global.local_chr(400)||
828 WorkForceFullType.EMPL_RCD(I)||fnd_global.local_chr(400)||
829 WorkForceFullType.ORIG_HIRE_DT(I)||fnd_global.local_chr(400)||
830 WorkForceFullType.PROBATION_DT(I)||fnd_global.local_chr(400)||
831 WorkForceFullType.EFFDT(I)||fnd_global.local_chr(400)||
832 WorkForceFullType.BUSINESS_UNIT(I)||fnd_global.local_chr(400)||
833 WorkForceFullType.JOBCODE(I)||fnd_global.local_chr(400)||
834 WorkForceFullType.EMPL_STATUS(I)||fnd_global.local_chr(400)||
835 WorkForceFullType.LOCATION(I)||fnd_global.local_chr(400)||
836 WorkForceFullType.FULL_PART_TIME(I)||fnd_global.local_chr(400)||
837 WorkForceFullType.COMPANY(I)||fnd_global.local_chr(400)||
838 WorkForceFullType.STD_HOURS(I)||fnd_global.local_chr(400)||
839 WorkForceFullType.STD_HRS_FREQUENCY(I)||fnd_global.local_chr(400)||
840 WorkForceFullType.GRADE(I)||fnd_global.local_chr(400)||
841 WorkForceFullType.SUPERVISOR_ID(I)||fnd_global.local_chr(400)||
842 WorkForceFullType.ASGN_START_DT(I)||fnd_global.local_chr(400)||
843 WorkForceFullType.ASGN_END_DT(I)||fnd_global.local_chr(400)||
844 WorkForceFullType.STEP(I)||fnd_global.local_chr(400)||
845 WorkForceFullType.TERMINATION_DT(I)||fnd_global.local_chr(400)||
846 WorkForceFullType.LAST_DATE_WORKED(I)
847 );
848
849 FND_FILE.NEW_LINE(FND_FILE.output, 1);
850 END Loop;
851
852 hr_full_sync_messages.update_psft_sync_run(1,'WORKFORCE_FULL_SYNC',l_current_date,errbuf,retcode);
853 FND_FILE.NEW_LINE(FND_FILE.log, 1);
854 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'));
855
856 End if;
857
858 EXCEPTION
859 WHEN OTHERS THEN
860 hr_full_sync_messages.update_psft_sync_run(3,'WORKFORCE_FULL_SYNC',l_current_date,errbuf,retcode);
861 errbuf := errbuf||SQLERRM;
862 retcode := '1';
863 FND_FILE.put_line(fnd_file.log, 'Error in workforce_fullsync: '||SQLCODE);
864 FND_FILE.NEW_LINE(FND_FILE.log, 1);
865 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
866
867 end hr_workforce_full_sync;
868
869 /*Procedure to extract the workforce data for full synch process ends*/
870
871 /*Procedure to extract the organization data for full synch process begins*/
872 procedure hr_organizaton_full_sync(errbuf OUT NOCOPY VARCHAR2
873 ,retcode OUT NOCOPY VARCHAR2)
874 is
875 p_bg_id hr_all_organization_units.business_group_id%type;
876 p_dept_id hr_all_organization_units.organization_id%type;
877 p_eff_date date;
878 p_loc_id hr_all_organization_units.location_id%type;
879 p_person_id per_org_manager_v.person_id%type;
880 p_full_name per_org_manager_v.full_name%type;
881 p_bg_name hr_all_organization_units.name%type;
882 p_eff_status varchar2(10);
883 p_effective_date date default sysdate;
884
885 cursor fet_org_fsync is
886 select org.business_group_id,
887 org.organization_id,
888 case when org.date_to is null then org.date_from
889 else org.date_to end,
890 case when org.date_to is null then 'ACTIVE'
891 else 'INACTIVE' end,
892 org.name,
893 org.location_id,
894 mgr.person_id,
895 mgr.full_name
896 from hr_all_organization_units org
897 ,per_org_manager_v mgr,hr_organization_information hrorg
898 where org.business_group_id = mgr.business_group_id(+)
899 and org.organization_id = mgr.organization_id(+)
900 and hrorg.organization_id = org.organization_id
901 and hrorg.org_information1 = 'HR_ORG'
902 and p_effective_date between org.date_from
903 and nvl(org.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
904 and p_effective_date between mgr.start_date(+) and mgr.end_date(+);
905
906
907
908 cursor fet_psft_sync is
909 select count('x')
910 from hr_psft_sync_run
911 where process = 'ORG_FULL_SYNC'
912 and run_date < p_effective_date
913 and status = 'STARTED';
914
915 l_dummy number;
916
917 begin
918
919 open fet_psft_sync;
920 fetch fet_psft_sync into l_dummy;
921 close fet_psft_sync;
922 if l_dummy = 0
923 then
924 FND_FILE.NEW_LINE(FND_FILE.log, 1);
925 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
926 FND_FILE.put_line(fnd_file.log,'Organization Full Synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
927 hr_full_sync_messages.insert_psft_sync_run(2,'ORG_FULL_SYNC',errbuf,retcode);
928 open fet_org_fsync;
929 loop
930 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;
931 exit when fet_org_fsync%notfound;
932
933 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||
934 fnd_global.local_chr(400)||
935 p_eff_status||fnd_global.local_chr(400)||p_bg_name||fnd_global.local_chr(400)||
936 p_loc_id||fnd_global.local_chr(400)||p_person_id||fnd_global.local_chr(400)||p_full_name);
937 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
938 end loop;
939 close fet_org_fsync;
940
941
942 hr_full_sync_messages.update_psft_sync_run(1,'ORG_FULL_SYNC',p_effective_date,errbuf,retcode);
943 FND_FILE.NEW_LINE(FND_FILE.log, 1);
944 FND_FILE.put_line(fnd_file.log,'Organization Full Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
945
946 end if;
947
948 exception
949 when OTHERS then
950 hr_full_sync_messages.update_psft_sync_run(3,'ORG_FULL_SYNC',p_effective_date,errbuf,retcode);
951 errbuf := errbuf||SQLERRM;
952 retcode := '1';
953 FND_FILE.put_line(fnd_file.log, 'Error in Organization Data Full Synch Extraction: '||SQLCODE);
954 FND_FILE.NEW_LINE(FND_FILE.log, 1);
955 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
956 end hr_organizaton_full_sync;
957 /*Procedure to extract the organization data for full synch process ends*/
958
959 /*Procedure to extract the business group data for full synch process begins*/
960 procedure hr_businessgrp_full_sync(errbuf OUT NOCOPY VARCHAR2
961 ,retcode OUT NOCOPY VARCHAR2)
962 is
963
964 p_bg_id PER_BUSINESS_GROUPS.business_group_id%type;
965 p_bg_name PER_BUSINESS_GROUPS.name%type;
966 p_eff_status varchar2(10);
967 p_eff_date date;
968 p_effective_date date default sysdate;
969
970 cursor fet_bg_fsync is
971 select business_group_id,
972 name,
973 case when date_to is null then date_from
974 else date_to end,
975 case when date_to is null then 'ACTIVE'
976 else 'INACTIVE' end
977 from PER_BUSINESS_GROUPS
978 where p_effective_date between date_from and
979 nvl (date_to, to_date('31-12-4712', 'DD-MM-YYYY'));
980
981
982 cursor fet_psft_sync is
983 select count('x')
984 from hr_psft_sync_run
985 where process = 'BG_FULL_SYNC'
986 and run_date < p_effective_date
987 and status = 'STARTED';
988
989 l_dummy number;
990
991 begin
992
993 open fet_psft_sync;
994 fetch fet_psft_sync into l_dummy;
995 close fet_psft_sync;
996 if l_dummy = 0
997 then
998 FND_FILE.NEW_LINE(FND_FILE.log, 1);
999 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1000 FND_FILE.put_line(fnd_file.log,'Business Group Full Synch Data Extraction Begins:'
1001 ||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1002 hr_full_sync_messages.insert_psft_sync_run(2,'BG_FULL_SYNC',errbuf,retcode);
1003 open fet_bg_fsync;
1004 loop
1005 fetch fet_bg_fsync into p_bg_id,p_bg_name,p_eff_date,p_eff_status;
1006 exit when fet_bg_fsync%notfound;
1007 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_bg_id||fnd_global.local_chr(400)||p_bg_name||fnd_global.local_chr(400)||
1008 p_eff_date||fnd_global.local_chr(400)||p_eff_status);
1009 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1010 end loop;
1011 close fet_bg_fsync;
1012
1013
1014 hr_full_sync_messages.update_psft_sync_run(1,'BG_FULL_SYNC',p_effective_date,errbuf,retcode);
1015 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1016 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'));
1017
1018 end if;
1019
1020 exception
1021 when OTHERS then
1022 hr_full_sync_messages.update_psft_sync_run(3,'BG_FULL_SYNC',p_effective_date,errbuf,retcode);
1023 errbuf := errbuf||SQLERRM;
1024 retcode := '1';
1025 FND_FILE.put_line(fnd_file.log, 'Error in Business Group Data Full Synch Extraction: '||SQLCODE);
1026 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1027 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
1028 end hr_businessgrp_full_sync;
1029 /*Procedure to extract the business group data for full synch process ends*/
1030
1031 /*Procedure to extract the payroll group data for full synch process begins*/
1032 procedure hr_payroll_full_sync(errbuf OUT NOCOPY VARCHAR2
1033 ,retcode OUT NOCOPY VARCHAR2)
1034 is
1035 p_pyrl_id pay_all_payrolls_f.payroll_id%type;
1036 p_pyrl_name pay_all_payrolls_f.payroll_name%type;
1037 p_bg_id pay_all_payrolls_f.business_group_id%type;
1038 p_eff_date date;
1039 p_eff_status varchar2(10);
1040 p_effective_date date default sysdate;
1041
1042 cursor fet_pyrl_fsync is
1043 select payroll_id,
1044 payroll_name,
1045 business_group_id,
1046 case when p_effective_date > add_months(first_period_end_date,NUMBER_OF_YEARS*12)
1047 then add_months(first_period_end_date,NUMBER_OF_YEARS*12) else (select min(effective_start_date) from
1048 pay_all_payrolls_f pay1
1049 where pay1.payroll_id = pay.payroll_id
1050 and pay1.business_group_id = pay.business_group_id) end,
1051 case when p_effective_date > add_months(first_period_end_date,NUMBER_OF_YEARS*12)
1052 then 'INACTIVE' else 'ACTIVE' end
1053 from pay_all_payrolls_f pay
1054 where p_effective_date between effective_start_date and effective_end_date;
1055
1056
1057 cursor fet_psft_sync is
1058 select count('x')
1059 from hr_psft_sync_run
1060 where process = 'PYRL_FULL_SYNC'
1061 and run_date < p_effective_date
1062 and status = 'STARTED';
1063
1064 l_dummy number;
1065
1066 begin
1067
1068 open fet_psft_sync;
1069 fetch fet_psft_sync into l_dummy;
1070 close fet_psft_sync;
1071 if l_dummy = 0
1072 then
1073 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1074 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1075 FND_FILE.put_line(fnd_file.log,'Payroll Full Synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1076 hr_full_sync_messages.insert_psft_sync_run(2,'PYRL_FULL_SYNC',errbuf,retcode);
1077 open fet_pyrl_fsync;
1078 loop
1079
1080 fetch fet_pyrl_fsync into p_pyrl_id,p_pyrl_name,p_bg_id,p_eff_date,p_eff_status;
1081 exit when fet_pyrl_fsync%notfound;
1082 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_pyrl_id||fnd_global.local_chr(400)||
1083 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);
1084 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1085 end loop;
1086 close fet_pyrl_fsync;
1087
1088
1089 hr_full_sync_messages.update_psft_sync_run(1,'PYRL_FULL_SYNC',p_effective_date,errbuf,retcode);
1090 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1091 FND_FILE.put_line(fnd_file.log,'Payroll Full Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1092
1093 end if;
1094
1095 exception
1096 when OTHERS then
1097 hr_full_sync_messages.update_psft_sync_run(3,'PYRL_FULL_SYNC',p_effective_date,errbuf,retcode);
1098 errbuf := errbuf||SQLERRM;
1099 retcode := '1';
1100 FND_FILE.put_line(fnd_file.log, 'Error in Payroll Data Full Synch Extraction: '||SQLCODE);
1101 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1102 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
1103 end hr_payroll_full_sync;
1104 /*Procedure to extract the payroll group data for full synch process ends*/
1105
1106 /*Common Procedure called from concurrent program begins*/
1107 procedure hr_full_sync (ERRBUF OUT NOCOPY varchar2,
1108 RETCODE OUT NOCOPY number,
1109 p_process_name in varchar2)
1110 is
1111 begin
1112
1113 if p_process_name = 'STATE_FULL_SYNCH'
1114 then
1115 hr_full_sync_messages.hr_state_full_sync(ERRBUF,RETCODE);
1116 elsif p_process_name = 'COUNTRY_FULL_SYNCH'
1117 then
1118 hr_full_sync_messages.hr_country_full_sync(ERRBUF,RETCODE);
1119 elsif p_process_name = 'LOCATION_FULL_SYNCH'
1120 then
1121 hr_full_sync_messages.hr_location_full_sync(ERRBUF,RETCODE);
1122 elsif p_process_name = 'PERSON_FULL_SYNCH'
1123 then
1124 hr_full_sync_messages.hr_person_full_sync(ERRBUF,RETCODE);
1125 elsif p_process_name = 'WORKFORCE_FULL_SYNCH'
1126 then
1127 hr_full_sync_messages.hr_workforce_full_sync(ERRBUF,RETCODE);
1128 elsif p_process_name = 'JOBCODE_FULL_SYNCH' then
1129 hr_full_sync_messages.hr_jobcode_full_sync(ERRBUF,RETCODE);
1130 elsif p_process_name = 'ORGANIZATION_FULL_SYNCH' then
1131 hr_full_sync_messages.hr_organizaton_full_sync(ERRBUF,RETCODE);
1132 elsif p_process_name = 'BUSINESSGROUP_FULL_SYNCH' then
1133 hr_full_sync_messages.hr_businessgrp_full_sync(ERRBUF,RETCODE);
1134 elsif p_process_name = 'PAYROLL_FULL_SYNCH' then
1135 hr_full_sync_messages.hr_payroll_full_sync(ERRBUF,RETCODE);
1136 end if;
1137 end hr_full_sync;
1138 /*Common Procedure called from concurrent program ends*/
1139
1140 end hr_full_sync_messages;