[Home] [Help]
PACKAGE BODY: APPS.HR_DELTA_SYNC_MESSAGES
Source
1 PACKAGE BODY HR_DELTA_SYNC_MESSAGES as
2 /* $Header: perhrhdrir.pkb 120.13 2008/03/19 09:50:29 sathkris noship $ */
3
4 /*Procedure to update the record into hr_psft_sync_run table begins*/
5 PROCEDURE update_psft_sync_run
6 (p_status number
7 ,p_process_name varchar2
8 ,p_run_date date
9 ,errbuf OUT NOCOPY VARCHAR2
10 ,retcode OUT NOCOPY VARCHAR2)
11 IS
12 l_status varchar2(10);
13
14 BEGIN
15
16 if p_status = 1 then
17 l_status := 'COMPLETED';
18 elsif p_status = 2 then
19 l_status := 'STARTED';
20 elsif p_status = 3 then
21 l_status := 'ERROR';
22 end if;
23
24 update hr_psft_sync_run
25 set status = l_status where process = p_process_name
26 and run_date =p_run_date;
27 commit;
28
29 FND_FILE.NEW_LINE(FND_FILE.log, 1);
30
31 EXCEPTION WHEN OTHERS THEN
32 errbuf := errbuf||SQLERRM;
33 retcode := '1';
34 FND_FILE.put_line(fnd_file.log,'Error in update_psft_sync_run: '||SQLCODE);
35 FND_FILE.NEW_LINE(FND_FILE.log, 1);
36 FND_FILE.put_line(fnd_file.log,'Error Msg: '||substr(SQLERRM,1,700));
37
38 END update_psft_sync_run;
39 /*Procedure to update the record into hr_psft_sync_run table ends*/
40
41 /*Procedure to insert the record into hr_psft_sync_run table begins*/
42 PROCEDURE insert_psft_sync_run
43 (p_status number
44 ,p_process_name varchar2
45 ,errbuf OUT NOCOPY VARCHAR2
46 ,retcode OUT NOCOPY VARCHAR2)
47 IS
48 l_status varchar2(10);
49 BEGIN
50
51 FND_FILE.NEW_LINE(FND_FILE.log, 1);
52
53 if p_status = 1 then
54 l_status := 'COMPLETED';
55 elsif p_status = 2 then
56 l_status := 'STARTED';
57 elsif p_status = 3 then
58 l_status := 'ERROR';
59 end if;
60
61 INSERT INTO hr_psft_sync_run(run_date,status,process)
62 Values(sysdate,l_status,p_process_name);
63 commit;
64
65 FND_FILE.NEW_LINE(FND_FILE.log, 1);
66
67 EXCEPTION WHEN OTHERS THEN
68 errbuf := errbuf||SQLERRM;
69 retcode := '1';
70 FND_FILE.put_line(fnd_file.log,'Error in insert_psft_sync_run: '||SQLCODE);
71 FND_FILE.NEW_LINE(FND_FILE.log, 1);
72 FND_FILE.put_line(fnd_file.log,'Error Msg: '||substr(SQLERRM,1,700));
73
74 END insert_psft_sync_run;
75 /*Procedure to insert the record into hr_psft_sync_run table ends*/
76
77 /*Procedure to extract the delta synch data for country begins here*/
78
79 PROCEDURE hr_country_delta_sync(errbuf OUT NOCOPY VARCHAR2
80 ,retcode OUT NOCOPY VARCHAR2
81 ,p_party_site_id in NUMBER)
82 is
83 p_cntry_code fnd_territories_vl.territory_code%type;
84 p_cntry_desc fnd_territories_vl.territory_short_name%type;
85 p_obs_flag fnd_territories_vl.obsolete_flag%type;
86 p_effective_date date default sysdate;
87 l_params WF_PARAMETER_LIST_T;
88 p_last_update_date date;
89 p_unique_key number;
90 p_row_id rowid;
91 p_gen_msg VARCHAR2(4000);
92 p_gen_status varchar2(10);
93
94
95 cursor fet_cntry_fsync(p_max_run_date date) is
96 select ft.territory_code,
97 ft.territory_short_name ,
98 ft.territory_code,ft.obsolete_flag,ft.row_id,ft.last_update_date
99 from fnd_territories_vl ft
100 where ft.last_update_date > p_max_run_date
101 and (ft.territory_code,ft.row_id) not in (select cntry.country_code,cntry.row_id
102 from hr_country_delta_sync cntry
103 where ft.territory_code = cntry.country_code
104 and ft.row_id = cntry.row_id
105 and ft.last_update_date <= cntry.last_update_date
106 and cntry.status in ('QUEUED','SENT'));
107
108 cursor csr_gen_msg(p_evn_key varchar2)
109 is select generation_status,generation_message
110 from ecx_out_process_v prcs
111 where document_id = p_evn_key;
112
113 cursor fet_delta_status
114 is
115 select country_code,row_id,event_key from
116 hr_country_delta_sync
117 where status = 'QUEUED';
118
119 p_country_code varchar2(5);
120 p_row1_id rowid;
121 p_lstupd_date date;
122
123 cursor fet_cntry_sync(p_country_code varchar2,p_row1_id varchar2)
124 is
125 select ft.territory_code,
126 ft.territory_short_name ,
127 ft.territory_code,ft.obsolete_flag,ft.row_id,ft.last_update_date
128 from fnd_territories_vl ft
129 where territory_code = p_country_code
130 and row_id = p_row1_id;
131
132 p_cntry_delta_sts varchar2(10);
133 p_event_key_gen varchar2(50);
134
135 cursor fet_psft_run_dt is
136 select max(run_date)
137 from hr_psft_sync_run
138 where process = 'COUNTRY_DELTA_SYNC'
139 and run_date < p_effective_date
140 and status = 'COMPLETED';
141
142 cursor fet_psft_run_dt1 is
143 select max(run_date)
144 from hr_psft_sync_run
145 where process = 'COUNTRY_FULL_SYNC'
146 and status = 'COMPLETED';
147
148 cursor fet_psft_sync is
149 select count('x')
150 from hr_psft_sync_run
151 where process = 'COUNTRY_DELTA_SYNC'
152 and run_date < p_effective_date
153 and status = 'STARTED';
154
155 l_dummy number;
156 p_max_run_date date;
157
158 begin
159
160 open fet_psft_sync;
161 fetch fet_psft_sync into l_dummy;
162 close fet_psft_sync;
163 if l_dummy = 0
164 then
165 FND_FILE.NEW_LINE(FND_FILE.log, 1);
166 FND_FILE.put_line(fnd_file.log,'Country Delta synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
167 hr_delta_sync_messages.insert_psft_sync_run(2,'COUNTRY_DELTA_SYNC',errbuf,retcode);
168
169
170
171 open fet_psft_run_dt;
172 fetch fet_psft_run_dt into p_max_run_date;
173 close fet_psft_run_dt;
174
175 if p_max_run_date is null
176 then
177 open fet_psft_run_dt1;
178 fetch fet_psft_run_dt1 into p_max_run_date;
179 close fet_psft_run_dt1;
180 end if;
181
182 open fet_delta_status;
183 loop
184 fetch fet_delta_status into p_country_code,p_row1_id,p_event_key_gen;
185
186 if fet_delta_status%found then
187
188 update hr_country_delta_sync
189 set status = 'SENT'
190 where event_key = p_event_key_gen;
191 commit;
192
193 open fet_cntry_sync(p_country_code,p_row1_id);
194 fetch fet_cntry_sync into p_cntry_code,p_cntry_desc,p_cntry_code,p_obs_flag,p_row_id,p_last_update_date;
195 if fet_cntry_sync%found then
196
197 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
198 insert into hr_country_delta_sync(COUNTRY_CODE,COUNTRY_DESCRIPTION,ROW_ID,
199 COUNTRY_2CHAR,OBSOLETE_FLAG,LAST_UPDATE_DATE,STATUS,EFFECTIVE_STATUS_DATE,EVENT_KEY)
200 values(p_cntry_code,p_cntry_desc,p_row_id,p_cntry_code,p_obs_flag,p_last_update_date,'QUEUED',p_effective_date,
201 p_cntry_code||'-'||to_char(p_unique_key));
202 commit;
203
204 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
205 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'CNTRY',l_params);
206 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
207 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', p_cntry_code||'-'||to_char(p_unique_key), l_params);
208 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
209 p_event_key => p_cntry_code||'-'||to_char(p_unique_key),
210 p_parameters => l_params);
211
212 open csr_gen_msg(p_cntry_code||'-'||to_char(p_unique_key));
213
214 fetch csr_gen_msg into p_gen_status,p_gen_msg;
215 if csr_gen_msg%found then
216 if p_gen_status not in ('0','10') then
217 FND_FILE.NEW_LINE(FND_FILE.log, 1);
218 FND_FILE.put_line(fnd_file.log,'Country Delta Synch Data Extraction Ends for the document id '||p_cntry_code||'-'||to_char(p_unique_key)
219 ||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
220 end if;
221 end if;
222 close csr_gen_msg;
223
224 end if;
225 close fet_cntry_sync;
226 else
227 exit;
228 end if;
229 end loop;
230
231 close fet_delta_status;
232
233 open fet_cntry_fsync(p_max_run_date);
234 loop
235 fetch fet_cntry_fsync into p_cntry_code,p_cntry_desc,p_cntry_code,p_obs_flag,p_row_id,p_last_update_date;
236 if fet_cntry_fsync%found then
237 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
238 insert into hr_country_delta_sync(COUNTRY_CODE,COUNTRY_DESCRIPTION,ROW_ID,
239 COUNTRY_2CHAR,OBSOLETE_FLAG,LAST_UPDATE_DATE,STATUS,EFFECTIVE_STATUS_DATE,EVENT_KEY)
240 values(p_cntry_code,p_cntry_desc,p_row_id,p_cntry_code,p_obs_flag,p_last_update_date,'QUEUED',p_effective_date,
241 p_cntry_code||'-'||to_char(p_unique_key));
242 commit;
243 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
244 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'CNTRY',l_params);
245 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
246 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', p_cntry_code||'-'||to_char(p_unique_key), l_params);
247 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
248 p_event_key => p_cntry_code||'-'||to_char(p_unique_key),
249 p_parameters => l_params);
250
251 open csr_gen_msg(p_cntry_code||'-'||to_char(p_unique_key));
252
253 fetch csr_gen_msg into p_gen_status,p_gen_msg;
254 if csr_gen_msg%found then
255 if p_gen_status not in ('0','10') then
256 FND_FILE.NEW_LINE(FND_FILE.log, 1);
257 FND_FILE.put_line(fnd_file.log,'Country Delta Synch Data Extraction Ends for the document id '||p_cntry_code||'-'||to_char(p_unique_key)
258 ||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
259 end if;
260 end if;
261 close csr_gen_msg;
262 else
263 exit;
264 end if;
265 end loop;
266 close fet_cntry_fsync;
267
268 hr_delta_sync_messages.update_psft_sync_run(1,'COUNTRY_DELTA_SYNC',p_effective_date,errbuf,retcode);
269 FND_FILE.NEW_LINE(FND_FILE.log, 1);
270 FND_FILE.put_line(fnd_file.log,'Country Delta Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
271 end if;
272
273 exception
274 when OTHERS then
275 hr_delta_sync_messages.update_psft_sync_run(3,'COUNTRY_DELTA_SYNC',p_effective_date,errbuf,retcode);
276 errbuf := errbuf||SQLERRM;
277 retcode := '1';
278 FND_FILE.put_line(fnd_file.log, 'Error in Country Delta 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_delta_sync;
283
284 /*Procedure to extract the delta synch data for country ends here*/
285
286 /*Procedure to extract the delta synch data for state begins here*/
287
288 PROCEDURE hr_state_delta_sync (errbuf OUT NOCOPY VARCHAR2
289 ,retcode OUT NOCOPY VARCHAR2
290 ,p_party_site_id in NUMBER)
291 is
292
293 p_cntry_code fnd_territories_vl.territory_code%type;
294 p_state_code fnd_common_lookups.lookup_code%type;
295 p_state_desc fnd_common_lookups.meaning%type;
296 p_enable_flag fnd_common_lookups.enabled_flag%type;
297 p_effective_date date default sysdate;
298 l_params WF_PARAMETER_LIST_T;
299 p_unique_key number;
300 p_last_update_date date;
301 p_gen_msg VARCHAR2(4000);
302 p_gen_status varchar2(10);
303
304 cursor fet_psft_run_dt is
305 select max(run_date)
306 from hr_psft_sync_run
307 where process = 'STATE_DELTA_SYNC'
308 and run_date < p_effective_date
309 and status = 'COMPLETED';
310
311 cursor fet_psft_run_dt1 is
312 select max(run_date)
313 from hr_psft_sync_run
314 where process = 'STATE_FULL_SYNC'
315 and status = 'COMPLETED';
316
317 cursor csr_gen_msg(p_evn_key varchar2)
318 is select generation_status,generation_message
319 from ecx_out_process_v prcs
320 where document_id = p_evn_key;
321
322 cursor fet_state_sync(p_max_run_date date) is
323 select ft.territory_code,fcl.lookup_code,fcl.meaning,fcl.enabled_flag,fcl.last_update_date
324 from fnd_common_lookups fcl,fnd_territories_vl ft
325 where fcl.lookup_type = (ft.territory_code ||'_STATE')
326 and fcl.last_update_date > p_max_run_date
327 and (ft.territory_code ,fcl.lookup_code) not in (select state.country_code,state.state_code
328 from hr_state_delta_sync state
329 where ft.territory_code = state.country_code
330 and fcl.lookup_code = state.state_code
331 and ft.last_update_date <= state.last_update_date
332 and state.status in ('QUEUED','SENT'));
333
334 cursor fet_delta_status
335 is
336 select country_code,state_code,event_key from
337 hr_state_delta_sync
338 where status = 'QUEUED';
339
340 p_country_code varchar2(5);
341 p_stt_code varchar2(30);
342 p_lstupd_date date;
343
344 cursor fet_state_qsync(p_country_code varchar2,p_stt_code varchar2)
345 is
346 select ft.territory_code,fcl.lookup_code,fcl.meaning,fcl.enabled_flag,fcl.last_update_date
347 from fnd_common_lookups fcl,fnd_territories_vl ft
348 where fcl.lookup_type = (ft.territory_code ||'_STATE')
349 and fcl.lookup_type = (p_country_code||'_STATE')
350 and fcl.lookup_code = p_stt_code;
351
352 p_cntry_delta_sts varchar2(10);
353 p_event_key_gen varchar2(50);
354
355
356 cursor fet_psft_sync is
357 select count('x')
358 from hr_psft_sync_run
359 where process = 'STATE_DELTA_SYNC'
360 and run_date < p_effective_date
361 and status = 'STARTED';
362
363 l_dummy number;
364 p_max_run_date date;
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.put_line(fnd_file.log,'State Delta synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
375 hr_delta_sync_messages.insert_psft_sync_run(2,'STATE_DELTA_SYNC',errbuf,retcode);
376
377 open fet_psft_run_dt;
378 fetch fet_psft_run_dt into p_max_run_date;
379 close fet_psft_run_dt;
380
381 if p_max_run_date is null
382 then
383 open fet_psft_run_dt1;
384 fetch fet_psft_run_dt1 into p_max_run_date;
385 close fet_psft_run_dt1;
386 end if;
387
388 open fet_delta_status;
389 loop
390 fetch fet_delta_status into p_country_code,p_stt_code,p_event_key_gen;
391
392 if fet_delta_status%found then
393
394 update hr_state_delta_sync
395 set status = 'SENT'
396 where event_key = p_event_key_gen;
397 commit;
398
399 open fet_state_qsync(p_country_code,p_stt_code);
400 fetch fet_state_qsync into p_cntry_code,p_state_code,p_state_desc,p_enable_flag,p_last_update_date;
401 if fet_state_qsync%found then
402
403 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
404 insert into hr_state_delta_sync(COUNTRY_CODE,STATE_CODE,
405 STATE_DESCRIPTION,ENABLE_FLAG,STATUS,EFFECTIVE_STATUS_DATE,
406 LAST_UPDATE_DATE,EVENT_KEY )
407 values(p_cntry_code,p_state_code,p_state_desc,p_enable_flag,'QUEUED',p_effective_date,p_last_update_date,p_state_code||'-'||to_char(p_unique_key));
408 commit;
409
410 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
411 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'STATE',l_params);
412 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
413 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', p_state_code||'-'||to_char(p_unique_key), l_params);
414 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
415 p_event_key => p_state_code||'-'||to_char(p_unique_key),
416 p_parameters => l_params);
417 end if;
418 close fet_state_qsync;
419
420 open csr_gen_msg(p_state_code||'-'||to_char(p_unique_key));
421
422 fetch csr_gen_msg into p_gen_status,p_gen_msg;
423 if csr_gen_msg%found then
424
425 if p_gen_status not in ('0','10') then
426 FND_FILE.NEW_LINE(FND_FILE.log, 1);
427 FND_FILE.put_line(fnd_file.log,'State Delta Synch Data Extraction Ends for the document id '||p_state_code||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
428 end if;
429 end if;
430 close csr_gen_msg;
431
432 else
433 exit;
434 end if;
435 end loop;
436 close fet_delta_status;
437
438 open fet_state_sync(p_max_run_date);
439 loop
440 fetch fet_state_sync into p_cntry_code,p_state_code,p_state_desc,p_enable_flag,p_last_update_date;
441 if fet_state_sync%found then
442 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
443 insert into hr_state_delta_sync(COUNTRY_CODE,STATE_CODE,
444 STATE_DESCRIPTION,ENABLE_FLAG,STATUS,EFFECTIVE_STATUS_DATE,
445 LAST_UPDATE_DATE,EVENT_KEY ) values(p_cntry_code,p_state_code,p_state_desc,p_enable_flag,'QUEUED',p_effective_date,p_last_update_date,p_state_code||'-'||to_char(p_unique_key));
446 commit;
447
448 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
449 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'STATE',l_params);
450 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
451 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', p_state_code||'-'||to_char(p_unique_key), l_params);
452 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
453 p_event_key => p_state_code||'-'||to_char(p_unique_key),
454 p_parameters => l_params);
455
456 open csr_gen_msg(p_state_code||'-'||to_char(p_unique_key));
457
458 fetch csr_gen_msg into p_gen_status,p_gen_msg;
459 if csr_gen_msg%found then
460
461 if p_gen_status not in ('0','10') then
462 FND_FILE.NEW_LINE(FND_FILE.log, 1);
463 FND_FILE.put_line(fnd_file.log,'State Delta Synch Data Extraction Ends for the document id '||p_state_code||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
464 end if;
465 end if;
466 close csr_gen_msg;
467 else
468 exit;
469 end if;
470 end loop;
471
472 close fet_state_sync;
473
474 hr_delta_sync_messages.update_psft_sync_run(1,'STATE_DELTA_SYNC',p_effective_date,errbuf,retcode);
475 FND_FILE.NEW_LINE(FND_FILE.log, 1);
476 FND_FILE.put_line(fnd_file.log,'State Delta Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
477
478 end if;
479
480
481 exception
482
483
484 when others then
485 hr_delta_sync_messages.update_psft_sync_run(3,'STATE_DELTA_SYNC',p_effective_date,errbuf,retcode);
486 errbuf := errbuf||SQLERRM;
487 retcode := '1';
488 FND_FILE.put_line(fnd_file.log, 'Error in State Delta Synch Extraction: '||SQLCODE);
489 FND_FILE.NEW_LINE(FND_FILE.log, 1);
490 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
491
492 end hr_state_delta_sync;
493
494 /*Procedure to extract the delta synch data for state ends here*/
495
496 /*Procedure to extract the delta synch data for location begins here*/
497 PROCEDURE hr_location_delta_sync (errbuf OUT NOCOPY VARCHAR2
498 ,retcode OUT NOCOPY VARCHAR2
499 ,p_party_site_id in NUMBER)
500
501 is
502
503
504 p_bg_id hr_locations_all.business_group_id%type;
505 p_loc_id hr_locations_all.LOCATION_ID%type;
506 p_active_date date;
507 p_effecive_status varchar2(10);
508 p_loc_code hr_locations_all.LOCATION_CODE%type;
509 p_loc_desc hr_locations_all.DESCRIPTION%type;
510 p_loc_style hr_locations_all.STYLE%type;
511 p_add_line_1 hr_locations_all.ADDRESS_LINE_1%type;
512 p_add_line_2 hr_locations_all.ADDRESS_LINE_2%type;
513 p_add_line_3 hr_locations_all.ADDRESS_LINE_3%type;
514 p_town_or_city hr_locations_all.TOWN_OR_CITY%type;
515 p_country hr_locations_all.COUNTRY%type;
516 p_postal_code hr_locations_all.POSTAL_CODE%type;
517 p_region_1 hr_locations_all.REGION_1%type;
518 p_region_2 hr_locations_all.REGION_2%type;
519 p_region_3 hr_locations_all.REGION_3%type;
520 p_tel_no_1 hr_locations_all.TELEPHONE_NUMBER_1%type;
521 p_tel_no_2 hr_locations_all.TELEPHONE_NUMBER_2%type;
522 p_tel_no_3 hr_locations_all.TELEPHONE_NUMBER_3%type;
523 p_loc_info_13 hr_locations_all.LOC_INFORMATION13%type;
524 p_loc_info_14 hr_locations_all.LOC_INFORMATION14%type;
525 p_loc_info_15 hr_locations_all.LOC_INFORMATION15%type;
526 p_loc_info_16 hr_locations_all.LOC_INFORMATION16%type;
527 p_loc_info_17 hr_locations_all.LOC_INFORMATION17%type;
528 p_loc_info_18 hr_locations_all.LOC_INFORMATION18%type;
529 p_loc_info_19 hr_locations_all.LOC_INFORMATION19%type;
530 p_loc_info_20 hr_locations_all.LOC_INFORMATION20%type;
531 p_effective_date date default sysdate;
532 l_params WF_PARAMETER_LIST_T;
533 p_unique_key number;
534 p_last_update_date date;
535 p_gen_msg VARCHAR2(4000);
536 p_gen_status varchar2(10);
537
538 cursor fet_psft_sync is
539 select count('x')
540 from hr_psft_sync_run
541 where process = 'LOC_DELTA_SYNC'
542 and run_date < p_effective_date
543 and status = 'STARTED';
544
545 cursor fet_psft_run_dt is
546 select max(run_date)
547 from hr_psft_sync_run
548 where process = 'LOC_DELTA_SYNC'
549 and run_date < p_effective_date
550 and status = 'COMPLETED';
551
552
553 cursor fet_psft_run_dt1 is
554 select max(run_date)
555 from hr_psft_sync_run
556 where process = 'LOC_FULL_SYNC'
557 and status = 'COMPLETED';
558
559 l_dummy number;
560 p_max_run_date date;
561
562 cursor csr_gen_msg(p_evn_key varchar2)
563 is select generation_status,generation_message
564 from ecx_out_process_v prcs
565 where document_id = p_evn_key;
566
567 cursor fet_loc_sync(p_max_run_date date) is
568 select BUSINESS_GROUP_ID,
569 LOCATION_ID,
570 case when inactive_date is not null then inactive_date
571 else CREATION_DATE end,
572 case when inactive_date is not null then 'INACTIVE'
573 else 'ACTIVE' end,
574 LOCATION_CODE ,
575 DESCRIPTION,
576 STYLE,
577 COUNTRY,
578 ADDRESS_LINE_1,
579 ADDRESS_LINE_2,
580 ADDRESS_LINE_3,
581 TOWN_OR_CITY,
582 REGION_1,
583 REGION_2,
584 REGION_3,
585 POSTAL_CODE,
586 TELEPHONE_NUMBER_1,
587 TELEPHONE_NUMBER_2,
588 TELEPHONE_NUMBER_3,
589 LOC_INFORMATION13,
590 LOC_INFORMATION14,
591 LOC_INFORMATION15,
592 LOC_INFORMATION16,
593 LOC_INFORMATION17,
594 LOC_INFORMATION18,
595 LOC_INFORMATION19,
596 LOC_INFORMATION20,
597 last_update_date
598
599 from
600 hr_locations_all loc
601 where last_update_date > p_max_run_date
602 and (loc.location_id,loc.business_group_id)not in(
603 select sync.location_id,sync.business_group_id
604 from hr_locn_delta_sync sync
605 where loc.location_id = sync.location_id
606 and loc.business_group_id = sync.business_group_id
607 and loc.last_update_date <= sync.last_update_date
608 and sync.status in ('QUEUED','SENT'));
609
610
611 cursor fet_delta_status
612 is
613 select location_id,business_group_id,event_key from
614 hr_locn_delta_sync
615 where status = 'QUEUED';
616
617 p_location_id number;
618 p_business_group_id number;
619 p_lstupd_date date;
620
621 cursor fet_loc_qsync(p_location_id number,p_business_group_id number)
622 is
623 select BUSINESS_GROUP_ID,
624 LOCATION_ID,
625 case when inactive_date is not null then inactive_date
626 else CREATION_DATE end,
627 case when inactive_date is not null then 'INACTIVE'
628 else 'ACTIVE' end,
629 LOCATION_CODE ,
630 DESCRIPTION,
631 STYLE,
632 COUNTRY,
633 ADDRESS_LINE_1,
634 ADDRESS_LINE_2,
635 ADDRESS_LINE_3,
636 TOWN_OR_CITY,
637 REGION_1,
638 REGION_2,
639 REGION_3,
640 POSTAL_CODE,
641 TELEPHONE_NUMBER_1,
642 TELEPHONE_NUMBER_2,
643 TELEPHONE_NUMBER_3,
644 LOC_INFORMATION13,
645 LOC_INFORMATION14,
646 LOC_INFORMATION15,
647 LOC_INFORMATION16,
648 LOC_INFORMATION17,
649 LOC_INFORMATION18,
650 LOC_INFORMATION19,
651 LOC_INFORMATION20,
652 last_update_date
653
654 from
655 hr_locations_all loc
656 where LOC.location_id = p_location_id
657 and nvl(LOC.business_group_id,0) = nvl(p_business_group_id,0);
658
659 p_cntry_delta_sts varchar2(10);
660 p_event_key_gen varchar2(50);
661
662
663
664 begin
665
666 open fet_psft_sync;
667 fetch fet_psft_sync into l_dummy;
668 close fet_psft_sync;
669
670 if l_dummy = 0
671 then
672
673
674
675 FND_FILE.NEW_LINE(FND_FILE.log, 1);
676 FND_FILE.put_line(fnd_file.log,'Location Delta synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
677 hr_delta_sync_messages.insert_psft_sync_run(2,'LOC_DELTA_SYNC',errbuf,retcode);
678
679 open fet_psft_run_dt;
680 fetch fet_psft_run_dt into p_max_run_date;
681 close fet_psft_run_dt;
682
683 if p_max_run_date is null
684 then
685 open fet_psft_run_dt1;
686 fetch fet_psft_run_dt1 into p_max_run_date;
687 close fet_psft_run_dt1;
688 end if;
689
690 open fet_delta_status;
691 loop
692 fetch fet_delta_status into p_location_id,p_business_group_id,p_event_key_gen;
693
694 if fet_delta_status%found then
695
696 update hr_locn_delta_sync
697 set status = 'SENT'
698 where event_key = p_event_key_gen;
699 commit;
700
701 open fet_loc_qsync(p_location_id,p_business_group_id);
702 fetch fet_loc_qsync into p_bg_id,p_loc_id,p_active_date,p_effecive_status,
703 p_loc_code, p_loc_desc, p_loc_style , p_country, p_add_line_1, p_add_line_2, p_add_line_3,
704 p_town_or_city,p_region_1,p_region_2,p_region_3,p_postal_code,p_tel_no_1,p_tel_no_2 ,
705 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,
706 p_loc_info_19,p_loc_info_20,p_last_update_date;
707 if fet_loc_qsync%found then
708 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
709 insert into hr_locn_delta_sync(BUSINESS_GROUP_ID,
710 LOCATION_ID,
711 EFFECTIVE_DATE,
712 EFFECTIVE_STATUS,
713 LOCATION_CODE,
714 LOCATION_DESCRIPTION,
715 LOCATION_STYLE,
716 COUNTRY,
717 ADDRESS_LINE1,
718 ADDRESS_LINE2,
719 ADDRESS_LINE3,
720 TOWN_OR_CITY,
721 REGION_1,
722 REGION_2,
723 REGION_3,
724 POSTAL_CODE,
725 TELEPHONE_NUMBER_1,
726 TELEPHONE_NUMBER_2,
727 TELEPHONE_NUMBER_3,
728 LOCATION_INFORMATION13,
729 LOCATION_INFORMATION14,
730 LOCATION_INFORMATION15,
731 LOCATION_INFORMATION16,
732 LOCATION_INFORMATION17,
733 LOCATION_INFORMATION18,
734 LOCATION_INFORMATION19,
735 LOCATION_INFORMATION20,
736 STATUS,
737 EFFECTIVE_STATUS_DATE,
738 LAST_UPDATE_DATE,
739 EVENT_KEY
740 )
741 values(p_bg_id,p_loc_id,p_active_date,p_effecive_status,
742 p_loc_code, p_loc_desc, p_loc_style , p_country, p_add_line_1, p_add_line_2, p_add_line_3,
743 p_town_or_city,p_region_1,p_region_2,p_region_3,p_postal_code,p_tel_no_1,p_tel_no_2 ,
744 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,
745 p_loc_info_19,p_loc_info_20,'QUEUED',p_effective_date,p_last_update_date,p_loc_id||'-'||to_char(p_unique_key));
746 commit;
747
748 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
749 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'LOCN',l_params);
750 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
751 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', p_loc_id||'-'||to_char(p_unique_key), l_params);
752 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
753 p_event_key => p_loc_id||'-'||to_char(p_unique_key),
754 p_parameters => l_params);
755
756
757 open csr_gen_msg(p_loc_id||'-'||to_char(p_unique_key));
758
759 fetch csr_gen_msg into p_gen_status,p_gen_msg;
760 if csr_gen_msg%found then
761
762 if p_gen_status not in ('0','10') then
763 FND_FILE.NEW_LINE(FND_FILE.log, 1);
764 FND_FILE.put_line(fnd_file.log,'Location Delta Synch Data Extraction Ends for the document id '||p_loc_id||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
765 end if;
766 end if;
767 close csr_gen_msg;
768
769 end if;
770 close fet_loc_qsync;
771 else
772 exit;
773 end if;
774 end loop;
775 close fet_delta_status;
776
777
778 open fet_loc_sync(p_max_run_date);
779 loop
780 fetch fet_loc_sync into p_bg_id,p_loc_id,p_active_date,p_effecive_status,
781 p_loc_code, p_loc_desc, p_loc_style , p_country, p_add_line_1, p_add_line_2, p_add_line_3,
782 p_town_or_city,p_region_1,p_region_2,p_region_3,p_postal_code,p_tel_no_1,p_tel_no_2 ,
783 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,
784 p_loc_info_19,p_loc_info_20,p_last_update_date;
785 if fet_loc_sync%found then
786 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
787 insert into hr_locn_delta_sync(BUSINESS_GROUP_ID,
788 LOCATION_ID,
789 EFFECTIVE_DATE,
790 EFFECTIVE_STATUS,
791 LOCATION_CODE,
792 LOCATION_DESCRIPTION,
793 LOCATION_STYLE,
794 COUNTRY,
795 ADDRESS_LINE1,
796 ADDRESS_LINE2,
797 ADDRESS_LINE3,
798 TOWN_OR_CITY,
799 REGION_1,
800 REGION_2,
801 REGION_3,
802 POSTAL_CODE,
803 TELEPHONE_NUMBER_1,
804 TELEPHONE_NUMBER_2,
805 TELEPHONE_NUMBER_3,
806 LOCATION_INFORMATION13,
807 LOCATION_INFORMATION14,
808 LOCATION_INFORMATION15,
809 LOCATION_INFORMATION16,
810 LOCATION_INFORMATION17,
811 LOCATION_INFORMATION18,
812 LOCATION_INFORMATION19,
813 LOCATION_INFORMATION20,
814 STATUS,
815 EFFECTIVE_STATUS_DATE,
816 LAST_UPDATE_DATE,
817 EVENT_KEY
818 ) values(p_bg_id,p_loc_id,p_active_date,p_effecive_status,
819 p_loc_code, p_loc_desc, p_loc_style , p_country, p_add_line_1, p_add_line_2, p_add_line_3,
820 p_town_or_city,p_region_1,p_region_2,p_region_3,p_postal_code,p_tel_no_1,p_tel_no_2 ,
821 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,
822 p_loc_info_19,p_loc_info_20,'QUEUED',p_effective_date,p_last_update_date,p_loc_id||'-'||to_char(p_unique_key));
823 commit;
824
825 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
826 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'LOCN',l_params);
827 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
828 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', p_loc_id||'-'||to_char(p_unique_key), l_params);
829 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
830 p_event_key => p_loc_id||'-'||to_char(p_unique_key),
831 p_parameters => l_params);
832
833 open csr_gen_msg(p_loc_id||'-'||to_char(p_unique_key));
834
835 fetch csr_gen_msg into p_gen_status,p_gen_msg;
836 if csr_gen_msg%found then
837
838 if p_gen_status not in ('0','10') then
839 FND_FILE.NEW_LINE(FND_FILE.log, 1);
840 FND_FILE.put_line(fnd_file.log,'Location Delta Synch Data Extraction Ends for the document id '||p_loc_id||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
841 end if;
842 end if;
843 close csr_gen_msg;
844
845
846 else
847 exit;
848 end if;
849 end loop;
850 close fet_loc_sync;
851
852 hr_delta_sync_messages.update_psft_sync_run(1,'LOC_DELTA_SYNC',p_effective_date,errbuf,retcode);
853 FND_FILE.NEW_LINE(FND_FILE.log, 1);
854 FND_FILE.put_line(fnd_file.log,'Location Delta Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
855
856 end if;
857
858 exception
859
860 when OTHERS then
861
862 hr_delta_sync_messages.update_psft_sync_run(3,'LOC_DELTA_SYNC',p_effective_date,errbuf,retcode);
863 errbuf := errbuf||SQLERRM;
864 retcode := '1';
865 FND_FILE.put_line(fnd_file.log, 'Error in Location Delta Synch Extraction: '||SQLCODE);
866 FND_FILE.NEW_LINE(FND_FILE.log, 1);
867 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
868
869 end hr_location_delta_sync;
870
871 /*Procedure to fetch the delta sync data for location ends here*/
872
873 /*Procedure to fetch the delta sync data for person begins here*/
874 procedure hr_person_delta_sync(errbuf OUT NOCOPY VARCHAR2
875 ,retcode OUT NOCOPY VARCHAR2
876 ,p_party_site_id in NUMBER) is
877
878
879 L_EMPLOYEE_NUMBER PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER%type;
880 L_USER_PERSON_TYPE VARCHAR2(60);
881 L_DATE_OF_BIRTH DATE;
882 L_TOWN_OF_BIRTH PER_ALL_PEOPLE_F.TOWN_OF_BIRTH%type;
883 L_COUNTRY_OF_BIRTH PER_ALL_PEOPLE_F.COUNTRY_OF_BIRTH%type;
884 L_DATE_OF_DEATH DATE;
885 L_ORIGINAL_DATE_OF_HIRE DATE;
886 L_EFFECTIVE_START_DATE DATE;
887 L_SEX VARCHAR2(30);
888 L_MARITAL_STATUS VARCHAR2(30);
889 L_FULL_NAME PER_ALL_PEOPLE_F.FULL_NAME%type;
890 L_PRE_NAME_ADJUNCT PER_ALL_PEOPLE_F.PRE_NAME_ADJUNCT%type;
891 L_SUFFIX VARCHAR2(30);
892 L_TITLE VARCHAR2(30);
893 L_LAST_NAME PER_ALL_PEOPLE_F.LAST_NAME%type;
894 L_FIRST_NAME PER_ALL_PEOPLE_F.FIRST_NAME%type;
895 L_MIDDLE_NAMES PER_ALL_PEOPLE_F.MIDDLE_NAMES%type;
896 L_ADDRESS_TYPE PER_ADDRESSES.ADDRESS_TYPE%type;
897 L_DATE_FROM DATE;
898 L_COUNTRY PER_ADDRESSES.COUNTRY%type;
899 L_ADDRESS_LINE1 PER_ADDRESSES.ADDRESS_LINE1%type;
900 L_ADDRESS_LINE2 PER_ADDRESSES.ADDRESS_LINE2%type;
901 L_ADDRESS_LINE3 PER_ADDRESSES.ADDRESS_LINE3%type;
902 L_TOWN_OR_CITY PER_ADDRESSES.TOWN_OR_CITY%type;
903 L_TELEPHONE_NUMBER_1 PER_ADDRESSES.TELEPHONE_NUMBER_1%type;
904 L_REGION_1 PER_ADDRESSES.REGION_1%type;
905 L_REGION_2 PER_ADDRESSES.REGION_1%type;
906 L_POSTAL_CODE PER_ADDRESSES.POSTAL_CODE%type;
907 L_EMAIL_ADDRESS per_all_people_f.email_address%type;
908 L_PHONE_TYPE PER_PHONES.PHONE_TYPE%type;
909 L_PHONE_NUMBER PER_PHONES.PHONE_NUMBER%type;
910 L_NATIONALITY VARCHAR2(30);
911 L_NATIONAL_IDENTIFIER PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER%type;
912 l_business_group_id number(15);
913 p_gen_msg VARCHAR2(4000);
914 p_gen_status varchar2(10);
915 p_event_key_gen varchar2(150);
916 p_last_update_date date;
917
918 /*Select state ment modified for the employee number
919 not getting displayed for Ex-Employee*/
920 cursor csr_person_delta_sync (P_SYNC_DATE DATE) is
921
922 SELECT DECODE ( ppf.CURRENT_NPW_FLAG , 'Y',NPW_NUMBER,EMPLOYEE_NUMBER ) EMPLOYEE_NUMBER,
923 -- HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(P_SYNC_DATE , PPF.PERSON_ID) , bug 6891949
924 HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(ppf.last_update_date , PPF.PERSON_ID) ,
925 DATE_OF_BIRTH,
926 TOWN_OF_BIRTH,
927 COUNTRY_OF_BIRTH,
928 DATE_OF_DEATH,
929 ORIGINAL_DATE_OF_HIRE,
930 EFFECTIVE_START_DATE,
931 HL1.MEANING SEX,
932 HL4.MEANING MARITAL_STATUS,
933 FULL_NAME,
934 PRE_NAME_ADJUNCT,
935 SUFFIX,
936 HL3.MEANING TITLE,
937 LAST_NAME,
938 FIRST_NAME,
939 MIDDLE_NAMES,
940 ADDRESS_TYPE,
941 padr.DATE_FROM,
942 COUNTRY,
943 ADDRESS_LINE1,
944 ADDRESS_LINE2,
945 ADDRESS_LINE3,
946 TOWN_OR_CITY,
947 TELEPHONE_NUMBER_1,
948 REGION_1,
949 REGION_2,
950 POSTAL_CODE,
951 EMAIL_ADDRESS,
952 PHONE_TYPE,
953 PHONE_NUMBER,
954 HL2.MEANING NATIONALITY,
955 NATIONAL_IDENTIFIER,
956 ppf.business_group_id,
957 ppf.LAST_UPDATE_DATE
958
959 FROM PER_ALL_PEOPLE_F ppf,
960 PER_ADDRESSES padr ,
961 PER_PHONES ppn ,
962 hr_lookups HL1 ,
963 HR_LOOKUPS HL2 ,
964 HR_LOOKUPS HL3 ,
965 HR_LOOKUPS HL4
966 WHERE ppf.person_id = padr.person_id (+)
967 AND ( padr.person_id is null
968 OR ( padr.person_id is not null
969 AND padr.primary_flag ='Y'
970 AND ppf.person_id = padr.person_id
971 and sysdate between padr.date_from and nvl (padr.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
972 ))
973 AND ppn.PARENT_ID (+) = PPF.PERSON_ID
974 -- Modified for the bug 6895752 starts here
975 /*AND ( ppn.parent_id is null
976 OR ( ppn.parent_id is not null
977 AND PPN.PARENT_TABLE = 'PER_ALL_PEOPLE_F'
978 AND PPN.PHONE_TYPE = 'W1' ))*/
979
980 AND PPN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
981 AND PPN.PHONE_TYPE (+) = 'W1'
982 -- Modified for the bug 6895752 ends here
983 AND ((ppf.CURRENT_EMPLOYEE_FLAG = 'Y'
984 OR ppf.person_id in -- modified for bug6873563
985 (SELECT nvl(pps.person_id , -100)
986 FROM per_periods_of_service pps
987 WHERE pps.person_id = ppf.person_id
988 AND pps.business_group_id = ppf.business_group_id
989 AND pps.last_update_date > P_SYNC_DATE
990 and ACTUAL_TERMINATION_DATE is not null
991 ))
992 OR ( ppf.CURRENT_NPW_FLAG = 'Y'
993 OR ppf.person_id in -- modified for bug6873563
994 (SELECT nvl(ppp.person_id , -100)
995 FROM per_periods_of_placement ppp
996 WHERE ppp.person_id = ppf.person_id
997 AND ppp.business_group_id = ppf.business_group_id
998 AND ppp.last_update_date > P_SYNC_DATE
999 and ACTUAL_TERMINATION_DATE is not null
1000 )))
1001 AND HL1.LOOKUP_TYPE (+) = 'SEX'
1002 AND HL1.LOOKUP_CODE (+) = ppf.SEX
1003 AND HL2.LOOKUP_TYPE (+) = 'NATIONALITY'
1004 AND HL2.LOOKUP_CODE (+) = Ppf.NATIONALITY
1005 AND HL3.LOOKUP_TYPE (+) = 'TITLE'
1006 AND HL3.LOOKUP_CODE (+) = PPF.TITLE
1007 AND HL4.LOOKUP_TYPE (+) = 'MAR_STATUS'
1008 AND HL4.LOOKUP_CODE (+) = PPF.MARITAL_STATUS
1009 AND ( (ppf.last_update_date > P_SYNC_DATE
1010 AND sysdate BETWEEN effective_start_date AND effective_end_date )
1011 OR (padr.last_update_date > P_SYNC_DATE) )
1012 AND (ppf.employee_number,ppf.business_group_id)
1013 not in (select per.employee_number,per.business_group_id
1014 from hr_person_delta_sync per
1015 where ppf.employee_number = per.employee_number
1016 and ppf.business_group_id = per.business_group_id
1017 and ppf.last_update_date = per.last_update_date
1018 and per.status in ('QUEUED','SENT'));
1019
1020 p_effective_date date default sysdate;
1021
1022 cursor csr_gen_msg(p_evn_key varchar2)
1023 is select generation_status,generation_message
1024 from ecx_out_process_v prcs
1025 where document_id = p_evn_key;
1026
1027 cursor fet_delta_status
1028 is
1029 select employee_number,business_group_id,record_key from
1030 hr_person_delta_sync
1031 where status = 'QUEUED';
1032
1033 p_employee_number1 varchar2(50);
1034 p_business_group_id1 number(15);
1035 cursor csr_person_delta_qsync (p_employee_number varchar2,p_business_group_id number) is
1036
1037 SELECT DECODE ( ppf.CURRENT_NPW_FLAG , 'Y',NPW_NUMBER,EMPLOYEE_NUMBER ) EMPLOYEE_NUMBER,
1038 HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(sysdate , PPF.PERSON_ID) ,
1039 DATE_OF_BIRTH,
1040 TOWN_OF_BIRTH,
1041 COUNTRY_OF_BIRTH,
1042 DATE_OF_DEATH,
1043 ORIGINAL_DATE_OF_HIRE,
1044 EFFECTIVE_START_DATE,
1045 HL1.MEANING SEX,
1046 HL4.MEANING MARITAL_STATUS,
1047 FULL_NAME,
1048 PRE_NAME_ADJUNCT,
1049 SUFFIX,
1050 HL3.MEANING TITLE,
1051 LAST_NAME,
1052 FIRST_NAME,
1053 MIDDLE_NAMES,
1054 ADDRESS_TYPE,
1055 padr.DATE_FROM,
1056 COUNTRY,
1057 ADDRESS_LINE1,
1058 ADDRESS_LINE2,
1059 ADDRESS_LINE3,
1060 TOWN_OR_CITY,
1061 TELEPHONE_NUMBER_1,
1062 REGION_1,
1063 REGION_2,
1064 POSTAL_CODE,
1065 EMAIL_ADDRESS,
1066 PHONE_TYPE,
1067 PHONE_NUMBER,
1068 HL2.MEANING NATIONALITY,
1069 NATIONAL_IDENTIFIER,
1070 ppf.business_group_id,
1071 ppf.LAST_UPDATE_DATE
1072
1073 FROM PER_ALL_PEOPLE_F ppf,
1074 PER_ADDRESSES padr ,
1075 PER_PHONES ppn ,
1076 hr_lookups HL1 ,
1077 HR_LOOKUPS HL2 ,
1078 HR_LOOKUPS HL3 ,
1079 HR_LOOKUPS HL4
1080 WHERE ppf.person_id = padr.person_id (+)
1081 AND ( padr.person_id is null
1082 OR ( padr.person_id is not null
1083 AND padr.primary_flag ='Y'
1084 AND ppf.person_id = padr.person_id
1085 -- and padr.last_update_date > P_SYNC_DATE
1086 and sysdate between padr.date_from and nvl (padr.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
1087 ))
1088 AND ppn.PARENT_ID (+) = PPF.PERSON_ID
1089 -- Modified for the bug 6895752 starts here
1090 /*AND ( ppn.parent_id is null
1091 OR ( ppn.parent_id is not null
1092 AND PPN.PARENT_TABLE = 'PER_ALL_PEOPLE_F'
1093 AND PPN.PHONE_TYPE = 'W1' ))*/
1094
1095 AND PPN.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F'
1096 AND PPN.PHONE_TYPE (+) = 'W1'
1097 -- Modified for the bug 6895752 ends here
1098 AND ((ppf.CURRENT_EMPLOYEE_FLAG = 'Y'
1099 OR ppf.person_id =
1100 (SELECT nvl(pps.person_id , -100)
1101 FROM per_periods_of_service pps
1102 WHERE pps.person_id = ppf.person_id
1103 AND pps.business_group_id = ppf.business_group_id
1104 AND pps.business_group_id = p_business_group_id
1105 --AND pps.last_update_date > P_SYNC_DATE
1106 and ACTUAL_TERMINATION_DATE is not null
1107 ))
1108 OR ( ppf.CURRENT_NPW_FLAG = 'Y'
1109 OR ppf.person_id =
1110 (SELECT nvl(ppp.person_id , -100)
1111 FROM per_periods_of_placement ppp
1112 WHERE ppp.person_id = ppf.person_id
1113 AND ppp.business_group_id = ppf.business_group_id
1114 AND ppp.business_group_id = p_business_group_id
1115 --AND ppp.last_update_date > P_SYNC_DATE
1116 and ACTUAL_TERMINATION_DATE is not null
1117 )))
1118 AND HL1.LOOKUP_TYPE (+) = 'SEX'
1119 AND HL1.LOOKUP_CODE (+) = ppf.SEX
1120 AND HL2.LOOKUP_TYPE (+) = 'NATIONALITY'
1121 AND HL2.LOOKUP_CODE (+) = Ppf.NATIONALITY
1122 AND HL3.LOOKUP_TYPE (+) = 'TITLE'
1123 AND HL3.LOOKUP_CODE (+) = PPF.TITLE
1124 AND HL4.LOOKUP_TYPE (+) = 'MAR_STATUS'
1125 AND HL4.LOOKUP_CODE (+) = PPF.MARITAL_STATUS
1126 AND ppf.employee_number = p_employee_number
1127 --AND ( (ppf.last_update_date > P_SYNC_DATE
1128 AND sysdate BETWEEN effective_start_date AND effective_end_date;
1129
1130
1131
1132
1133
1134 p_cntry_delta_sts varchar2(10);
1135
1136 cursor fet_psft_sync is
1137 select count('x')
1138 from hr_psft_sync_run
1139 where process = 'PERSON_DELTA_SYNC'
1140 and run_date < sysdate
1141 and status = 'STARTED';
1142
1143
1144 cursor csr_psft_sync is
1145 select max (run_date)
1146 from hr_psft_sync_run
1147 where process = 'PERSON_DELTA_SYNC'
1148 and run_date < sysdate
1149 and status = 'COMPLETED';
1150
1151
1152 cursor csr_psft_sync_FULL is
1153 select ruN_date
1154 from hr_psft_sync_run
1155 where process = 'PERSON_FULL_SYNC'
1156 and status = 'COMPLETED';
1157
1158 l_dummy number;
1159 l_sync_date date;
1160 l_current_date date;
1161 p_unique_key number;
1162 l_params WF_PARAMETER_LIST_T;
1163
1164 begin
1165 open fet_psft_sync;
1166 fetch fet_psft_sync into l_dummy;
1167 close fet_psft_sync;
1168
1169 if l_dummy = 0
1170 then
1171
1172
1173
1174 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1175 FND_FILE.put_line(fnd_file.log,'Person Delta synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1176 hr_delta_sync_messages.insert_psft_sync_run(2,'PERSON_DELTA_SYNC',errbuf,retcode);
1177
1178 open csr_psft_sync;
1179 fetch csr_psft_sync into l_sync_date;
1180 close csr_psft_sync;
1181
1182 if l_sync_date is null then
1183 open csr_psft_sync_FULL;
1184 FETCH csr_psft_sync_FULL INTO l_sync_date ;
1185 CLOSE csr_psft_sync_FULL;
1186
1187 end if;
1188
1189 l_current_date :=sysdate;
1190
1191 open fet_delta_status;
1192 loop
1193 fetch fet_delta_status into p_business_group_id1,p_employee_number1,p_event_key_gen;
1194 if fet_delta_status%found then
1195 update hr_person_delta_sync
1196 set status = 'SENT'
1197 where record_key = p_event_key_gen;
1198 commit;
1199
1200 open csr_person_delta_qsync(p_employee_number1,p_business_group_id1);
1201
1202 fetch csr_person_delta_qsync into L_EMPLOYEE_NUMBER,L_USER_PERSON_TYPE,L_DATE_OF_BIRTH,L_TOWN_OF_BIRTH,L_COUNTRY_OF_BIRTH
1203 ,L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE,L_EFFECTIVE_START_DATE
1204 , L_SEX,L_MARITAL_STATUS,L_FULL_NAME,L_PRE_NAME_ADJUNCT ,L_SUFFIX
1205 ,L_TITLE,L_LAST_NAME,L_FIRST_NAME ,L_MIDDLE_NAMES, L_ADDRESS_TYPE ,L_DATE_FROM ,L_COUNTRY, L_ADDRESS_LINE1,
1206 L_ADDRESS_LINE2,L_ADDRESS_LINE3,L_TOWN_OR_CITY ,L_TELEPHONE_NUMBER_1,L_REGION_1 ,L_REGION_2,
1207 L_POSTAL_CODE, L_EMAIL_ADDRESS, L_PHONE_TYPE
1208 ,L_PHONE_NUMBER,L_NATIONALITY ,L_NATIONAL_IDENTIFIER,
1209 l_business_group_id,p_last_update_date ;
1210
1211 if csr_person_delta_qsync%found then
1212
1213 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1214 insert into hr_person_delta_sync
1215 (EMPLOYEE_NUMBER,USER_PERSON_TYPE ,
1216 DATE_OF_BIRTH,TOWN_OF_BIRTH,
1217 COUNTRY_OF_BIRTH,BUSINESS_GROUP_ID,
1218 DATE_OF_DEATH,ORIGINAL_DATE_OF_HIRE,
1219 EFFECTIVE_START_DATE,SEX,MARITAL_STATUS ,
1220 FULL_NAME,PRE_NAME_ADJUNCT,SUFFIX,
1221 TITLE,LAST_NAME,FIRST_NAME ,
1222 MIDDLE_NAMES,ADDRESS_TYPE ,DATE_FROM,
1223 COUNTRY,ADDRESS_LINE1,
1224 ADDRESS_LINE2,ADDRESS_LINE3,TOWN_OR_CITY,
1225 TELEPHONE_NUMBER_1,REGION_1,REGION_2,POSTAL_CODE,
1226 EMAIL_ADDRESS,PHONE_TYPE,PHONE_NUMBER,
1227 NATIONALITY,NATIONAL_IDENTIFIER ,STATUS,
1228 EFFECTIVE_STATUS_DATE,
1229 LAST_UPDATE_DATE,
1230 RECORD_KEY )
1231 values (L_EMPLOYEE_NUMBER,L_USER_PERSON_TYPE,
1232 L_DATE_OF_BIRTH,L_TOWN_OF_BIRTH,L_COUNTRY_OF_BIRTH,l_business_group_id,
1233 L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE,L_EFFECTIVE_START_DATE,
1234 L_SEX,L_MARITAL_STATUS,L_FULL_NAME,L_PRE_NAME_ADJUNCT ,L_SUFFIX,
1235 L_TITLE,L_LAST_NAME,L_FIRST_NAME ,L_MIDDLE_NAMES, L_ADDRESS_TYPE ,L_DATE_FROM ,L_COUNTRY, L_ADDRESS_LINE1,
1236 L_ADDRESS_LINE2,L_ADDRESS_LINE3,L_TOWN_OR_CITY ,L_TELEPHONE_NUMBER_1,L_REGION_1 ,L_REGION_2,
1237 L_POSTAL_CODE, L_EMAIL_ADDRESS, L_PHONE_TYPE,
1238 L_PHONE_NUMBER,L_NATIONALITY ,L_NATIONAL_IDENTIFIER,'QUEUED',l_current_date,
1239 p_last_update_date,L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key));
1240
1241 commit;
1242
1243
1244 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
1245 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'PERSON',l_params);
1246 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
1247 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key), l_params);
1248 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
1249 p_event_key => L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key),
1250 p_parameters => l_params);
1251
1252 open csr_gen_msg(L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key));
1253
1254 fetch csr_gen_msg into p_gen_status,p_gen_msg;
1255 if csr_gen_msg%found then
1256
1257 if p_gen_status not in ('0','10') then
1258 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1259 FND_FILE.put_line(fnd_file.log,'Person Delta Synch Data Extraction Ends for the document id '||L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1260 end if;
1261 end if;
1262 close csr_gen_msg;
1263
1264 end if;
1265 close csr_person_delta_qsync;
1266 else
1267 exit;
1268 end if;
1269 end loop;
1270 close fet_delta_status;
1271 --
1272 open csr_person_delta_sync (l_sync_date);
1273 loop
1274 fetch csr_person_delta_sync into L_EMPLOYEE_NUMBER,L_USER_PERSON_TYPE,L_DATE_OF_BIRTH,L_TOWN_OF_BIRTH,L_COUNTRY_OF_BIRTH
1275 ,L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE,L_EFFECTIVE_START_DATE
1276 , L_SEX,L_MARITAL_STATUS,L_FULL_NAME,L_PRE_NAME_ADJUNCT ,L_SUFFIX
1277 ,L_TITLE,L_LAST_NAME,L_FIRST_NAME ,L_MIDDLE_NAMES, L_ADDRESS_TYPE ,L_DATE_FROM ,L_COUNTRY, L_ADDRESS_LINE1,
1278 L_ADDRESS_LINE2,L_ADDRESS_LINE3,L_TOWN_OR_CITY ,L_TELEPHONE_NUMBER_1,L_REGION_1 ,L_REGION_2,
1279 L_POSTAL_CODE, L_EMAIL_ADDRESS, L_PHONE_TYPE
1280 ,L_PHONE_NUMBER,L_NATIONALITY ,L_NATIONAL_IDENTIFIER,
1281 l_business_group_id,p_last_update_date ;
1282
1283 if csr_person_delta_sync%found then
1284
1285 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1286 insert into hr_person_delta_sync
1287 (EMPLOYEE_NUMBER,USER_PERSON_TYPE ,
1288 DATE_OF_BIRTH,TOWN_OF_BIRTH,
1289 COUNTRY_OF_BIRTH,BUSINESS_GROUP_ID,
1290 DATE_OF_DEATH,ORIGINAL_DATE_OF_HIRE,
1291 EFFECTIVE_START_DATE,SEX,MARITAL_STATUS ,
1292 FULL_NAME,PRE_NAME_ADJUNCT,SUFFIX,
1293 TITLE,LAST_NAME,FIRST_NAME ,
1294 MIDDLE_NAMES,ADDRESS_TYPE ,DATE_FROM,
1295 COUNTRY,ADDRESS_LINE1,
1296 ADDRESS_LINE2,ADDRESS_LINE3,TOWN_OR_CITY,
1297 TELEPHONE_NUMBER_1,REGION_1,REGION_2,POSTAL_CODE,
1298 EMAIL_ADDRESS,PHONE_TYPE,PHONE_NUMBER,
1299 NATIONALITY,NATIONAL_IDENTIFIER ,STATUS,
1300 EFFECTIVE_STATUS_DATE,
1301 LAST_UPDATE_DATE,
1302 RECORD_KEY )
1303 values (L_EMPLOYEE_NUMBER,L_USER_PERSON_TYPE,
1304 L_DATE_OF_BIRTH,L_TOWN_OF_BIRTH,L_COUNTRY_OF_BIRTH,l_business_group_id,
1305 L_DATE_OF_DEATH ,L_ORIGINAL_DATE_OF_HIRE,L_EFFECTIVE_START_DATE,
1306 L_SEX,L_MARITAL_STATUS,L_FULL_NAME,L_PRE_NAME_ADJUNCT ,L_SUFFIX,
1307 L_TITLE,L_LAST_NAME,L_FIRST_NAME ,L_MIDDLE_NAMES, L_ADDRESS_TYPE ,L_DATE_FROM ,L_COUNTRY, L_ADDRESS_LINE1,
1308 L_ADDRESS_LINE2,L_ADDRESS_LINE3,L_TOWN_OR_CITY ,L_TELEPHONE_NUMBER_1,L_REGION_1 ,L_REGION_2,
1309 L_POSTAL_CODE, L_EMAIL_ADDRESS, L_PHONE_TYPE,
1310 L_PHONE_NUMBER,L_NATIONALITY ,L_NATIONAL_IDENTIFIER,'QUEUED',l_current_date,
1311 p_last_update_date,L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key));
1312
1313 commit;
1314
1315 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
1316 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'PERSON',l_params);
1317 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
1318 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key), l_params);
1319 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
1320 p_event_key => L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key),
1321 p_parameters => l_params);
1322
1323 open csr_gen_msg(L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key));
1324
1325 fetch csr_gen_msg into p_gen_status,p_gen_msg;
1326 if csr_gen_msg%found then
1327
1328 if p_gen_status not in ('0','10') then
1329 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1330 FND_FILE.put_line(fnd_file.log,'Person Delta Synch Data Extraction Ends for the document id '||L_EMPLOYEE_NUMBER||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1331 end if;
1332 end if;
1333 close csr_gen_msg;
1334
1335
1336 else
1337 exit ;
1338 end if;
1339
1340 exit when csr_person_delta_sync%notfound;
1341
1342 end loop;
1343 close csr_person_delta_sync;
1344
1345 hr_delta_sync_messages.update_psft_sync_run(1,'PERSON_DELTA_SYNC',p_effective_date,errbuf,retcode);
1346 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1347 FND_FILE.put_line(fnd_file.log,'Person Delta Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1348 end if;
1349
1350 exception
1351
1352 when OTHERS then
1353
1354 hr_delta_sync_messages.update_psft_sync_run(3,'PERSON_DELTA_SYNC',p_effective_date,errbuf,retcode);
1355 errbuf := errbuf||SQLERRM;
1356 retcode := '1';
1357 FND_FILE.put_line(fnd_file.log, 'Error in Person Delta Synch Extraction: '||SQLCODE);
1358 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1359 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
1360
1361
1362
1363 end;
1364 /*Procedure to fetch the delta sync data for person ends here*/
1365
1366 /*Procedure to extract the workforce data for delta synch process begins here*/
1367 procedure hr_workforce_delta_sync(errbuf OUT NOCOPY VARCHAR2
1368 ,retcode OUT NOCOPY VARCHAR2
1369 ,p_party_site_id in NUMBER)
1370 is
1371
1372
1373 TYPE EMPLIDTYPE IS TABLE OF per_all_people_f.employee_number%type INDEX BY BINARY_INTEGER;
1374 TYPE EMPL_RCDTYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1375 TYPE PROBATION_DTTYPE IS TABLE OF per_all_assignments_f.probation_period%type INDEX BY BINARY_INTEGER;
1376 TYPE ORIG_HIRE_DTTYPE IS TABLE OF per_all_people_f.original_date_of_hire%type INDEX BY BINARY_INTEGER;
1377 TYPE WEFFDTTYPE IS TABLE OF per_all_assignments_f.effective_start_date%type INDEX BY BINARY_INTEGER;
1378 TYPE BUSINESS_UNITTYPE IS TABLE OF per_all_assignments_f.organization_id%type INDEX BY BINARY_INTEGER;
1379 TYPE WJOBCODETYPE IS TABLE OF per_all_assignments_f.job_id%type INDEX BY BINARY_INTEGER;
1380 TYPE EMPL_STATUSTYPE IS TABLE OF per_all_assignments_f.assignment_status_type_id%type INDEX BY BINARY_INTEGER;
1381 TYPE LOCATIONTYPE IS TABLE OF per_all_assignments_f.location_id%type INDEX BY BINARY_INTEGER;
1382 TYPE FULL_PART_TIMETYPE IS TABLE OF per_all_assignments_f.employment_category%type INDEX BY BINARY_INTEGER;
1383 TYPE COMPANYTYPE IS TABLE OF per_all_assignments_f.business_group_id%type INDEX BY BINARY_INTEGER;
1384 TYPE STD_HOURSTYPE IS TABLE OF per_all_assignments_f.normal_hours%type INDEX BY BINARY_INTEGER;
1385 TYPE STD_HRS_FREQUENCYTYPE IS TABLE OF per_all_assignments_f.frequency%type INDEX BY BINARY_INTEGER;
1386 TYPE GRADETYPE IS TABLE OF per_all_assignments_f.grade_id%type INDEX BY BINARY_INTEGER;
1387 TYPE SUPERVISOR_IDTYPE IS TABLE OF per_all_assignments_f.supervisor_id%type INDEX BY BINARY_INTEGER;
1388 TYPE ASGN_START_DTTYPE IS TABLE OF per_all_assignments_f.EFFECTIVE_START_DATE%type INDEX BY BINARY_INTEGER;
1389 TYPE ASGN_END_DTTYPE IS TABLE OF per_all_assignments_f.EFFECTIVE_END_DATE%type INDEX BY BINARY_INTEGER;
1390 TYPE TERMINATION_DTTYPE IS TABLE OF per_periods_of_service.final_process_date%type INDEX BY BINARY_INTEGER;
1391 TYPE LAST_DATE_WORKEDTYPE IS TABLE OF per_periods_of_service.ACCEPTED_TERMINATION_DATE%type INDEX BY BINARY_INTEGER;
1392 TYPE STEPTYPE IS TABLE OF PER_SPINAL_POINT_PLACEMENTS_F.STEP_ID%type INDEX BY BINARY_INTEGER;
1393 TYPE LSTUPDDTTYPE IS TABLE OF per_all_assignments_f.last_update_date%type INDEX BY BINARY_INTEGER;
1394 TYPE workforce IS REF CURSOR;
1395
1396 TYPE WorkForceTblType IS RECORD
1397 (
1398 EMPLID EMPLIDTYPE
1399 ,EMPL_RCD EMPL_RCDTYPE
1400 ,PROBATION_DT PROBATION_DTTYPE
1401 ,ORIG_HIRE_DT ORIG_HIRE_DTTYPE
1402 ,EFFDT WEFFDTTYPE
1403 ,BUSINESS_UNIT BUSINESS_UNITTYPE
1404 ,JOBCODE WJOBCODETYPE
1405 ,EMPL_STATUS EMPL_STATUSTYPE
1406 ,LOCATION LOCATIONTYPE
1407 ,FULL_PART_TIME FULL_PART_TIMETYPE
1408 ,COMPANY COMPANYTYPE
1409 ,STD_HOURS STD_HOURSTYPE
1410 ,STD_HRS_FREQUENCY STD_HRS_FREQUENCYTYPE
1411 ,GRADE GRADETYPE
1412 ,SUPERVISOR_ID SUPERVISOR_IDTYPE
1413 ,ASGN_START_DT ASGN_START_DTTYPE
1414 ,ASGN_END_DT ASGN_END_DTTYPE
1415 ,TERMINATION_DT TERMINATION_DTTYPE
1416 ,LAST_DATE_WORKED LAST_DATE_WORKEDTYPE
1417 ,STEP STEPTYPE
1418 ,LAST_UPDATE_DATE LSTUPDDTTYPE
1419 );
1420
1421 WorkForceFullType WorkForceTblType;
1422 WorkForcedeltaType WorkForceTblType;
1423
1424 workforce_delta workforce;
1425 workforce_deltaq workforce;
1426
1427 p_cnt number := 0;
1428 l_params WF_PARAMETER_LIST_T;
1429 p_unique_key number;
1430 p_effective_date date default sysdate;
1431 p_gen_msg VARCHAR2(4000);
1432 p_gen_status varchar2(10);
1433
1434 cursor fet_psft_run_dt is
1435 select max(run_date)
1436 from hr_psft_sync_run
1437 where process = 'WORKFORCE_DELTA_SYNC'
1438 and run_date < p_effective_date
1439 and status = 'COMPLETED';
1440
1441 cursor fet_psft_run_dt1 is
1442 select max(run_date)
1443 from hr_psft_sync_run
1444 where process = 'WORKFORCE_FULL_SYNC'
1445 and status = 'COMPLETED';
1446
1447 cursor fet_psft_sync is
1448 select count('x')
1449 from hr_psft_sync_run
1450 where process = 'WORKFORCE_DELTA_SYNC'
1451 and run_date < p_effective_date
1452 and status = 'STARTED';
1453
1454 cursor csr_gen_msg(p_evn_key varchar2)
1455 is select generation_status,generation_message
1456 from ecx_out_process_v prcs
1457 where document_id = p_evn_key;
1458
1459 cursor fet_delta_status
1460 is
1461 select employee_number,business_group_id,job_id,event_key,last_update_date
1462 from HR_WORKFORCE_DELTA_SYNC
1463 where status = 'QUEUED';
1464
1465 p_emp_num VARCHAR2(30);
1466 p_bg_id number(15);
1467 p_job_id number(15);
1468 p_event_key_gen varchar2(240);
1469 p_lst_upd_date date;
1470
1471 l_dummy number;
1472 run_date date;
1473
1474 begin
1475
1476 open fet_psft_sync;
1477 fetch fet_psft_sync into l_dummy;
1478 close fet_psft_sync;
1479 if l_dummy = 0
1480 then
1481 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1482 FND_FILE.put_line(fnd_file.log,'Work Force Delta synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1483 hr_delta_sync_messages.insert_psft_sync_run(2,'WORKFORCE_DELTA_SYNC',errbuf,retcode);
1484
1485
1486
1487 open fet_psft_run_dt;
1488 fetch fet_psft_run_dt into run_date;
1489 close fet_psft_run_dt;
1490
1491 if run_date is null
1492 then
1493 open fet_psft_run_dt1;
1494 fetch fet_psft_run_dt1 into run_date;
1495 close fet_psft_run_dt1;
1496 end if;
1497
1498
1499 open fet_delta_status;
1500 loop
1501 fetch fet_delta_status into p_emp_num,p_bg_id,p_job_id,p_event_key_gen,p_lst_upd_date;
1502
1503 if fet_delta_status%found then
1504
1505 update HR_WORKFORCE_DELTA_SYNC
1506 set status = 'SENT'
1507 where event_key = p_event_key_gen;
1508 commit;
1509
1510 OPEN workforce_deltaq FOR
1511 SELECT ppf.employee_number,1 AS empl_rcd ,ppf.original_date_of_hire,
1512 pas.probation_period,pas.effective_start_date effdt,pas.organization_id,
1513 pas.job_id,pas.assignment_status_type_id,pas.location_id,
1514 pas.employment_category,pas.business_group_id,pas.normal_hours,
1515 pas.frequency,pas.grade_id,pas.supervisor_id,pas.EFFECTIVE_START_DATE,
1516 nvl(pas.EFFECTIVE_END_DATE,sysdate) EFFECTIVE_END_DATE,
1517 nvl(psf.step_id,0) Step_id
1518 ,pos.final_process_date,pos.ACCEPTED_TERMINATION_DATE,pas.last_update_date
1519 FROM per_all_people_f ppf,per_all_assignments_f pas,
1520 per_periods_of_service pos,PER_SPINAL_POINT_PLACEMENTS_F psf
1521 WHERE pas.primary_flag='Y'
1522 AND pos.person_id=pas.person_id
1523 AND ppf.person_id = pos.person_id
1524 AND pas.business_group_id = psf.business_group_id(+)
1525 AND pas.assignment_id = psf.assignment_id(+)
1526 AND ppf.BUSINESS_GROUP_ID = pas.BUSINESS_GROUP_ID
1527 AND pas.effective_start_date BETWEEN ppf.effective_start_date(+) AND
1528 ppf.effective_end_date(+)
1529 AND ppf.employee_number = p_emp_num
1530 AND pas.business_group_id = p_bg_id
1531 AND pas.job_id = p_job_id
1532 AND pas.last_update_date >= p_lst_upd_date;
1533
1534 FETCH workforce_deltaq
1535 INTO WorkForcedeltaType.EMPLID(1)
1536 ,WorkForcedeltaType.EMPL_RCD(1)
1537 ,WorkForcedeltaType.ORIG_HIRE_DT(1)
1538 ,WorkForcedeltaType.PROBATION_DT(1)
1539 ,WorkForcedeltaType.EFFDT(1)
1540 ,WorkForcedeltaType.BUSINESS_UNIT(1)
1541 ,WorkForcedeltaType.JOBCODE(1)
1542 ,WorkForcedeltaType.EMPL_STATUS(1)
1543 ,WorkForcedeltaType.LOCATION(1)
1544 ,WorkForcedeltaType.FULL_PART_TIME(1)
1545 ,WorkForcedeltaType.COMPANY(1)
1546 ,WorkForcedeltaType.STD_HOURS(1)
1547 ,WorkForcedeltaType.STD_HRS_FREQUENCY(1)
1548 ,WorkForcedeltaType.GRADE(1)
1549 ,WorkForcedeltaType.SUPERVISOR_ID(1)
1550 ,WorkForcedeltaType.ASGN_START_DT(1)
1551 ,WorkForcedeltaType.ASGN_END_DT(1)
1552 ,WorkForcedeltaType.STEP(1)
1553 ,WorkForcedeltaType.TERMINATION_DT(1)
1554 ,WorkForcedeltaType.LAST_DATE_WORKED(1)
1555 ,WorkForcedeltaType.LAST_UPDATE_DATE(1);
1556
1557
1558 if workforce_deltaq%FOUND then
1559
1560
1561 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1562 insert into HR_WORKFORCE_DELTA_SYNC
1563 (EMPLOYEE_NUMBER,
1564 EMPL_RCD ,
1565 ORIGINAL_DATE_OF_HIRE,
1566 PROBATION_PERIOD,
1567 EFFDT,
1568 ORGANIZATION_ID,
1569 JOB_ID,
1570 ASSIGNMENT_STATUS_TYPE_ID,
1571 LOCATION_ID,
1572 EMPLOYMENT_CATEGORY,
1573 BUSINESS_GROUP_ID,
1574 NORMAL_HOURS,
1575 FREQUENCY,
1576 GRADE_ID ,
1577 SUPERVISOR_ID,
1578 EFFECTIVE_START_DATE,
1579 EFFECTIVE_END_DATE,
1580 STEP_ID,
1581 FINAL_PROCESS_DATE,
1582 ACCEPTED_TERMINATION_DATE,
1583 STATUS,
1584 EFFECTIVE_STATUS_DATE,
1585 LAST_UPDATE_DATE,
1586 EVENT_KEY)
1587 values(
1588 WorkForceDeltaType.EMPLID(1)
1589 ,WorkForceDeltaType.EMPL_RCD(1)
1590 ,WorkForceDeltaType.ORIG_HIRE_DT(1)
1591 ,WorkForceDeltaType.PROBATION_DT(1)
1592 ,WorkForceDeltaType.EFFDT(1)
1593 ,WorkForceDeltaType.BUSINESS_UNIT(1)
1594 ,WorkForceDeltaType.JOBCODE(1)
1595 ,WorkForceDeltaType.EMPL_STATUS(1)
1596 ,WorkForceDeltaType.LOCATION(1)
1597 ,WorkForceDeltaType.FULL_PART_TIME(1)
1598 ,WorkForceDeltaType.COMPANY(1)
1599 ,WorkForceDeltaType.STD_HOURS(1)
1600 ,WorkForceDeltaType.STD_HRS_FREQUENCY(1)
1601 ,WorkForceDeltaType.GRADE(1)
1602 ,WorkForceDeltaType.SUPERVISOR_ID(1)
1603 ,WorkForceDeltaType.ASGN_START_DT(1)
1604 ,WorkForceDeltaType.ASGN_END_DT(1)
1605 ,WorkForceDeltaType.STEP(1)
1606 ,WorkForceDeltaType.TERMINATION_DT(1)
1607 ,WorkForceDeltaType.LAST_DATE_WORKED(1)
1608 ,'QUEUED'
1609 ,sysdate
1610 ,WorkForceDeltaType.LAST_UPDATE_DATE(1)
1611 ,WorkForceDeltaType.EMPLID(1)||WorkForceDeltaType.BUSINESS_UNIT(1)||
1612 WorkForceDeltaType.JOBCODE(1)||'-'||to_char(p_unique_key));
1613 commit;
1614
1615 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
1616 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'WORKFORCE',l_params);
1617 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
1618 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', WorkForceDeltaType.EMPLID(1)||WorkForceDeltaType.BUSINESS_UNIT(1)||
1619 WorkForceDeltaType.JOBCODE(1)||'-'||to_char(p_unique_key), l_params);
1620
1621 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
1622 p_event_key => WorkForceDeltaType.EMPLID(1)||WorkForceDeltaType.BUSINESS_UNIT(1)||
1623 WorkForceDeltaType.JOBCODE(1)||'-'||to_char(p_unique_key),
1624 p_parameters => l_params);
1625
1626 open csr_gen_msg(WorkForceDeltaType.EMPLID(1)||WorkForceDeltaType.BUSINESS_UNIT(1)||
1627 WorkForceDeltaType.JOBCODE(1)||'-'||to_char(p_unique_key));
1628
1629 fetch csr_gen_msg into p_gen_status,p_gen_msg;
1630 if csr_gen_msg%found then
1631 if p_gen_status not in ('0','10') then
1632 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1633 FND_FILE.put_line(fnd_file.log,'Workforce Delta Synch Data Extraction Ends for the document id '||WorkForceDeltaType.EMPLID(1)||WorkForceDeltaType.BUSINESS_UNIT(1)||
1634 WorkForceDeltaType.JOBCODE(1)||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1635 end if;
1636 end if;
1637 close csr_gen_msg;
1638
1639 end if;
1640 close workforce_deltaq;
1641 else
1642 exit;
1643 end if;
1644 end loop;
1645
1646 close fet_delta_status;
1647
1648
1649 OPEN workforce_delta FOR
1650 SELECT ppf.employee_number,1 AS empl_rcd ,ppf.original_date_of_hire,
1651 pas.probation_period,pas.effective_start_date effdt,pas.organization_id,
1652 pas.job_id,pas.assignment_status_type_id,pas.location_id,
1653 pas.employment_category,pas.business_group_id,pas.normal_hours,
1654 pas.frequency,pas.grade_id,pas.supervisor_id,pas.EFFECTIVE_START_DATE,
1655 nvl(pas.EFFECTIVE_END_DATE,sysdate) EFFECTIVE_END_DATE,
1656 nvl(psf.step_id,0) Step_id
1657 ,pos.final_process_date,pos.ACCEPTED_TERMINATION_DATE,pas.last_update_date
1658 FROM per_all_people_f ppf,per_all_assignments_f pas,
1659 per_periods_of_service pos,PER_SPINAL_POINT_PLACEMENTS_F psf
1660 WHERE pas.primary_flag='Y'
1661 AND pos.person_id=pas.person_id
1662 AND ppf.person_id = pos.person_id
1663 AND pas.business_group_id = psf.business_group_id(+)
1664 AND pas.assignment_id = psf.assignment_id(+)
1665 AND ppf.BUSINESS_GROUP_ID = pas.BUSINESS_GROUP_ID
1666 AND pas.effective_start_date BETWEEN ppf.effective_start_date(+) AND
1667 ppf.effective_end_date(+)
1668 AND pas.last_update_date >= run_date
1669 AND (ppf.employee_number,pas.business_group_id,pas.job_id) not in (
1670 select wfrc.employee_number,wfrc.business_group_id,wfrc.job_id
1671 from HR_WORKFORCE_DELTA_SYNC wfrc
1672 where wfrc.employee_number = ppf.employee_number
1673 and wfrc.business_group_id = pas.business_group_id
1674 and wfrc.job_id = pas.job_id
1675 and pas.last_update_date <= wfrc.last_update_date
1676 and wfrc.status in ('QUEUED','SENT')) ;
1677
1678 LOOP
1679 BEGIN
1680 FETCH workforce_delta BULK COLLECT
1681 INTO WorkForcedeltaType.EMPLID
1682 ,WorkForcedeltaType.EMPL_RCD
1683 ,WorkForcedeltaType.ORIG_HIRE_DT
1684 ,WorkForcedeltaType.PROBATION_DT
1685 ,WorkForcedeltaType.EFFDT
1686 ,WorkForcedeltaType.BUSINESS_UNIT
1687 ,WorkForcedeltaType.JOBCODE
1688 ,WorkForcedeltaType.EMPL_STATUS
1689 ,WorkForcedeltaType.LOCATION
1690 ,WorkForcedeltaType.FULL_PART_TIME
1691 ,WorkForcedeltaType.COMPANY
1692 ,WorkForcedeltaType.STD_HOURS
1693 ,WorkForcedeltaType.STD_HRS_FREQUENCY
1694 ,WorkForcedeltaType.GRADE
1695 ,WorkForcedeltaType.SUPERVISOR_ID
1696 ,WorkForcedeltaType.ASGN_START_DT
1697 ,WorkForcedeltaType.ASGN_END_DT
1698 ,WorkForcedeltaType.STEP
1699 ,WorkForcedeltaType.TERMINATION_DT
1700 ,WorkForcedeltaType.LAST_DATE_WORKED
1701 ,WorkForcedeltaType.LAST_UPDATE_DATE;
1702
1703
1704 END;
1705
1706 if WorkForcedeltaType.EMPLID.count <=0 then
1707 CLOSE workforce_delta;
1708 EXIT;
1709 end if;
1710
1711 p_cnt := p_cnt + WorkForcedeltaType.EMPLID.count;
1712
1713 if workforce_delta%NOTFOUND then
1714 CLOSE workforce_delta;
1715 EXIT;
1716 end if;
1717
1718 END LOOP;
1719
1720
1721 FOR I IN 1 .. p_cnt Loop
1722 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1723 insert into HR_WORKFORCE_DELTA_SYNC
1724 (EMPLOYEE_NUMBER,
1725 EMPL_RCD ,
1726 ORIGINAL_DATE_OF_HIRE,
1727 PROBATION_PERIOD,
1728 EFFDT,
1729 ORGANIZATION_ID,
1730 JOB_ID,
1731 ASSIGNMENT_STATUS_TYPE_ID,
1732 LOCATION_ID,
1733 EMPLOYMENT_CATEGORY,
1734 BUSINESS_GROUP_ID,
1735 NORMAL_HOURS,
1736 FREQUENCY,
1737 GRADE_ID ,
1738 SUPERVISOR_ID,
1739 EFFECTIVE_START_DATE,
1740 EFFECTIVE_END_DATE,
1741 STEP_ID,
1742 FINAL_PROCESS_DATE,
1743 ACCEPTED_TERMINATION_DATE,
1744 STATUS,
1745 EFFECTIVE_STATUS_DATE,
1746 LAST_UPDATE_DATE,
1747 EVENT_KEY)
1748 values(
1749 WorkForceDeltaType.EMPLID(I)
1750 ,WorkForceDeltaType.EMPL_RCD(I)
1751 ,WorkForceDeltaType.ORIG_HIRE_DT(I)
1752 ,WorkForceDeltaType.PROBATION_DT(I)
1753 ,WorkForceDeltaType.EFFDT(I)
1754 ,WorkForceDeltaType.BUSINESS_UNIT(I)
1755 ,WorkForceDeltaType.JOBCODE(I)
1756 ,WorkForceDeltaType.EMPL_STATUS(I)
1757 ,WorkForceDeltaType.LOCATION(I)
1758 ,WorkForceDeltaType.FULL_PART_TIME(I)
1759 ,WorkForceDeltaType.COMPANY(I)
1760 ,WorkForceDeltaType.STD_HOURS(I)
1761 ,WorkForceDeltaType.STD_HRS_FREQUENCY(I)
1762 ,WorkForceDeltaType.GRADE(I)
1763 ,WorkForceDeltaType.SUPERVISOR_ID(I)
1764 ,WorkForceDeltaType.ASGN_START_DT(I)
1765 ,WorkForceDeltaType.ASGN_END_DT(I)
1766 ,WorkForceDeltaType.STEP(I)
1767 ,WorkForceDeltaType.TERMINATION_DT(I)
1768 ,WorkForceDeltaType.LAST_DATE_WORKED(I)
1769 ,'QUEUED'
1770 ,sysdate
1771 ,WorkForceDeltaType.LAST_UPDATE_DATE(I)
1772 ,WorkForceDeltaType.EMPLID(I)||WorkForceDeltaType.BUSINESS_UNIT(I)||
1773 WorkForceDeltaType.JOBCODE(I)||'-'||to_char(p_unique_key));
1774 commit;
1775
1776 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
1777 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'WORKFORCE',l_params);
1778 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
1779 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', WorkForceDeltaType.EMPLID(I)||WorkForceDeltaType.BUSINESS_UNIT(I)||
1780 WorkForceDeltaType.JOBCODE(I)||'-'||to_char(p_unique_key), l_params);
1781
1782 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
1783 p_event_key => WorkForceDeltaType.EMPLID(I)||WorkForceDeltaType.BUSINESS_UNIT(I)||
1784 WorkForceDeltaType.JOBCODE(I)||'-'||to_char(p_unique_key),
1785 p_parameters => l_params);
1786
1787 open csr_gen_msg(WorkForceDeltaType.EMPLID(I)||WorkForceDeltaType.BUSINESS_UNIT(I)||
1788 WorkForceDeltaType.JOBCODE(I)||'-'||to_char(p_unique_key));
1789
1790 fetch csr_gen_msg into p_gen_status,p_gen_msg;
1791 if csr_gen_msg%found then
1792 if p_gen_status not in ('0','10') then
1793 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1794 FND_FILE.put_line(fnd_file.log,'Workforce Delta Synch Data Extraction Ends for the document id '||WorkForceDeltaType.EMPLID(I)||WorkForceDeltaType.BUSINESS_UNIT(I)||
1795 WorkForceDeltaType.JOBCODE(I)||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1796 end if;
1797 end if;
1798 close csr_gen_msg;
1799
1800
1801 END Loop;
1802 hr_delta_sync_messages.update_psft_sync_run(1,'WORKFORCE_DELTA_SYNC',p_effective_date,errbuf,retcode);
1803 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1804 FND_FILE.put_line(fnd_file.log,'Work Force Delta Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1805
1806 End if;
1807
1808 exception
1809 when OTHERS then
1810 hr_delta_sync_messages.update_psft_sync_run(3,'WORKFORCE_DELTA_SYNC',p_effective_date,errbuf,retcode);
1811 errbuf := errbuf||SQLERRM;
1812 retcode := '1';
1813 FND_FILE.put_line(fnd_file.log, 'Error in Work Force Delta Synch Extraction: '||SQLCODE);
1814 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1815 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
1816
1817 end hr_workforce_delta_sync;
1818 /*Procedure to extract the workforce data for delta synch process ends here*/
1819
1820 /*Procedure to extract the jobcode data for delta synch process begins here*/
1821 procedure hr_jobcode_delta_sync(errbuf OUT NOCOPY VARCHAR2
1822 ,retcode OUT NOCOPY VARCHAR2
1823 ,p_party_site_id in NUMBER)
1824 is
1825
1826 TYPE setidType IS TABLE OF per_jobs.business_group_id%type INDEX BY BINARY_INTEGER;
1827 TYPE jobcodeType IS TABLE OF per_jobs.job_id%type INDEX BY BINARY_INTEGER;
1828 TYPE effdtType IS TABLE OF per_jobs.date_from%type INDEX BY BINARY_INTEGER;
1829 TYPE effstatusType IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
1830 TYPE descrType IS TABLE OF per_jobs.name%type INDEX BY BINARY_INTEGER;
1831 TYPE lstupddtType IS TABLE OF per_jobs.last_update_date%type INDEX BY BINARY_INTEGER;
1832 TYPE jobcode IS REF CURSOR;
1833
1834 TYPE JobCodeTblType IS RECORD
1835 (
1836 SETID setidType,
1837 JOBCODE jobcodeType,
1838 EFFDT effdtType,
1839 EFF_STATUS effstatusType,
1840 DESCR descrType,
1841 LAST_UPD_DATE lstupddtType);
1842
1843 Jobcodedeltatype JobCodeTblType;
1844
1845 jobcode_delta jobcode;
1846 jobcode_deltaq jobcode;
1847
1848 p_cnt number := 0;
1849 l_params WF_PARAMETER_LIST_T;
1850 p_unique_key number;
1851 p_effective_date date default sysdate;
1852
1853 cursor fet_psft_run_dt is
1854 select max(run_date)
1855 from hr_psft_sync_run
1856 where process = 'JOBCODE_DELTA_SYNC'
1857 and run_date < p_effective_date
1858 and status = 'COMPLETED';
1859
1860 cursor fet_psft_run_dt1 is
1861 select max(run_date)
1862 from hr_psft_sync_run
1863 where process = 'JOBCODE_FULL_SYNC'
1864 and status = 'COMPLETED';
1865
1866 cursor fet_psft_sync is
1867 select count('x')
1868 from hr_psft_sync_run
1869 where process = 'JOBCODE_DELTA_SYNC'
1870 and run_date < p_effective_date
1871 and status = 'STARTED';
1872
1873 cursor fet_delta_status
1874 is
1875 select setid,jobcode,event_key,last_update_date from
1876 HR_JOBCODE_DELTA_SYNC
1877 where status = 'QUEUED';
1878
1879 cursor csr_gen_msg(p_evn_key varchar2)
1880 is select generation_status,generation_message
1881 from ecx_out_process_v prcs
1882 where document_id = p_evn_key;
1883
1884
1885
1886 l_dummy number;
1887 run_date date;
1888 p_event_key_gen varchar2(240);
1889 p_set_id number(15);
1890 p_job_id number(15);
1891 p_lst_upddt date;
1892 p_gen_msg VARCHAR2(4000);
1893 p_gen_status varchar2(10);
1894
1895 BEGIN
1896
1897 open fet_psft_sync;
1898 fetch fet_psft_sync into l_dummy;
1899 close fet_psft_sync;
1900 if l_dummy = 0
1901 then
1902
1903 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1904 FND_FILE.put_line(fnd_file.log,'JobCode Delta synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
1905 hr_delta_sync_messages.insert_psft_sync_run(2,'JOBCODE_DELTA_SYNC',errbuf,retcode);
1906 /* Fetching the jobcode data for delta Sync */
1907
1908 open fet_psft_run_dt;
1909 fetch fet_psft_run_dt into run_date;
1910 close fet_psft_run_dt;
1911
1912 if run_date is null
1913 then
1914 open fet_psft_run_dt1;
1915 fetch fet_psft_run_dt1 into run_date;
1916 close fet_psft_run_dt1;
1917 end if;
1918
1919
1920 open fet_delta_status;
1921 loop
1922 fetch fet_delta_status into p_set_id,p_job_id,p_event_key_gen,p_lst_upddt;
1923
1924 if fet_delta_status%found then
1925
1926 update HR_JOBCODE_DELTA_SYNC
1927 set status = 'SENT'
1928 where event_key = p_event_key_gen;
1929 commit;
1930
1931
1932
1933 OPEN jobcode_deltaq FOR
1934 SELECT BUSINESS_GROUP_ID SETID,
1935 JOB_ID JOBCODE,
1936 DATE_FROM EFFDT,
1937 DECODE(DATE_TO,NULL,'ACTIVE','INACTIVE') EFF_STATUS,
1938 NAME DESCR,
1939 LAST_UPDATE_DATE LAST_UPD_DATE
1940 FROM PER_JOBS
1941 WHERE last_update_date >= p_lst_upddt
1942 AND BUSINESS_GROUP_ID = p_set_id
1943 AND JOB_ID = p_job_id;
1944
1945 FETCH jobcode_deltaq
1946 INTO Jobcodedeltatype.SETID(1)
1947 ,Jobcodedeltatype.JOBCODE(1)
1948 ,Jobcodedeltatype.EFFDT(1)
1949 ,Jobcodedeltatype.EFF_STATUS(1)
1950 ,Jobcodedeltatype.DESCR(1)
1951 ,Jobcodedeltatype.LAST_UPD_DATE(1);
1952
1953 IF jobcode_deltaq%found then
1954
1955 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1956
1957 insert into HR_JOBCODE_DELTA_SYNC(
1958 SETID ,
1959 JOBCODE ,
1960 EFFDT,
1961 EFF_STATUS ,
1962 DESCR ,
1963 STATUS ,
1964 EFFECTIVE_STATUS_DATE ,
1965 LAST_UPDATE_DATE ,
1966 EVENT_KEY)
1967 values(
1968 Jobcodedeltatype.SETID(1)
1969 ,Jobcodedeltatype.JOBCODE(1)
1970 ,Jobcodedeltatype.EFFDT(1)
1971 ,Jobcodedeltatype.EFF_STATUS(1)
1972 ,Jobcodedeltatype.DESCR(1)
1973 ,'QUEUED'
1974 ,sysdate
1975 ,Jobcodedeltatype.LAST_UPD_DATE(1)
1976 ,Jobcodedeltatype.SETID(1)||Jobcodedeltatype.JOBCODE(1)||'-'||to_char(p_unique_key));
1977
1978 commit;
1979
1980
1981
1982 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
1983 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'JOBCODE',l_params);
1984 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
1985 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID',Jobcodedeltatype.SETID(1)||Jobcodedeltatype.JOBCODE(1)||'-'||to_char(p_unique_key) , l_params);
1986 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
1987 p_event_key => Jobcodedeltatype.SETID(1)||Jobcodedeltatype.JOBCODE(1)||'-'||to_char(p_unique_key),
1988 p_parameters => l_params);
1989
1990 open csr_gen_msg(Jobcodedeltatype.SETID(1)||Jobcodedeltatype.JOBCODE(1)||'-'||to_char(p_unique_key));
1991
1992 fetch csr_gen_msg into p_gen_status,p_gen_msg;
1993 if csr_gen_msg%found then
1994
1995 if p_gen_status not in ('0','10') then
1996 FND_FILE.NEW_LINE(FND_FILE.log, 1);
1997 FND_FILE.put_line(fnd_file.log,'JobCode Delta Synch Data Extraction Ends for the document id '||Jobcodedeltatype.SETID(1)||
1998 Jobcodedeltatype.JOBCODE(1)||'-'
1999 ||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2000 end if;
2001 end if;
2002 close csr_gen_msg;
2003 end if;
2004 close jobcode_deltaq;
2005 else
2006 exit;
2007 end if;
2008
2009 END loop;
2010 close fet_delta_status;
2011
2012
2013 OPEN jobcode_delta FOR
2014 SELECT job.BUSINESS_GROUP_ID SETID,
2015 job.JOB_ID JOBCODE,
2016 job.DATE_FROM EFFDT,
2017 DECODE(job.DATE_TO,NULL,'ACTIVE','INACTIVE') EFF_STATUS,
2018 job.NAME DESCR,
2019 job.LAST_UPDATE_DATE LAST_UPD_DATE
2020 FROM PER_JOBS job
2021 WHERE job.last_update_date >= run_date
2022 and (job.business_group_id,job.job_id)not in
2023 (select setid,jobcode from HR_JOBCODE_DELTA_SYNC jbcd
2024 where job.BUSINESS_GROUP_ID = jbcd.setid
2025 and job.JOB_ID = jbcd.jobcode
2026 and job.last_update_date <= jbcd.last_update_date
2027 and jbcd.status in ('QUEUED','SENT'));
2028
2029 /* UNION
2030 select SETID,
2031 JOBCODE,
2032 EFFDT,
2033 EFF_STATUS,
2034 DESCR
2035 FROM HR.HR_JOBCODE_DELTA_SYNC
2036 WHERE STATUS = 'QUEUED';*/
2037
2038 LOOP
2039 BEGIN
2040 FETCH jobcode_delta BULK COLLECT
2041 INTO Jobcodedeltatype.SETID
2042 ,Jobcodedeltatype.JOBCODE
2043 ,Jobcodedeltatype.EFFDT
2044 ,Jobcodedeltatype.EFF_STATUS
2045 ,Jobcodedeltatype.DESCR
2046 ,Jobcodedeltatype.LAST_UPD_DATE;
2047
2048 END;
2049
2050 if Jobcodedeltatype.JOBCODE.count <=0 then
2051 CLOSE jobcode_delta;
2052 EXIT;
2053 end if;
2054
2055 p_cnt := p_cnt + Jobcodedeltatype.JOBCODE.count;
2056
2057 if jobcode_delta%NOTFOUND then
2058 CLOSE jobcode_delta;
2059 EXIT;
2060 end if;
2061
2062 END LOOP;
2063
2064
2065 FOR I IN 1 .. p_cnt Loop
2066
2067 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
2068
2069 insert into HR_JOBCODE_DELTA_SYNC(
2070 SETID ,
2071 JOBCODE ,
2072 EFFDT,
2073 EFF_STATUS ,
2074 DESCR ,
2075 STATUS ,
2076 EFFECTIVE_STATUS_DATE ,
2077 LAST_UPDATE_DATE ,
2078 EVENT_KEY)
2079 values(
2080 Jobcodedeltatype.SETID(I)
2081 ,Jobcodedeltatype.JOBCODE(I)
2082 ,Jobcodedeltatype.EFFDT(I)
2083 ,Jobcodedeltatype.EFF_STATUS(I)
2084 ,Jobcodedeltatype.DESCR(I)
2085 ,'QUEUED'
2086 ,sysdate
2087 ,Jobcodedeltatype.LAST_UPD_DATE(I)
2088 ,Jobcodedeltatype.SETID(I)||Jobcodedeltatype.JOBCODE(I)||'-'||to_char(p_unique_key));
2089
2090 commit;
2091
2092
2093
2094 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
2095 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'JOBCODE',l_params);
2096 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
2097 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID',Jobcodedeltatype.SETID(I)||Jobcodedeltatype.JOBCODE(I)||'-'||to_char(p_unique_key) , l_params);
2098 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
2099 p_event_key => Jobcodedeltatype.SETID(I)||Jobcodedeltatype.JOBCODE(I)||'-'||to_char(p_unique_key),
2100 p_parameters => l_params);
2101
2102 open csr_gen_msg(Jobcodedeltatype.SETID(1)||Jobcodedeltatype.JOBCODE(1)||'-'||to_char(p_unique_key));
2103
2104 fetch csr_gen_msg into p_gen_status,p_gen_msg;
2105 if csr_gen_msg%found then
2106
2107 if p_gen_status not in ('0','10') then
2108 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2109 FND_FILE.put_line(fnd_file.log,'JobCode Delta Synch Data Extraction Ends for the document id '||Jobcodedeltatype.SETID(1)||
2110 Jobcodedeltatype.JOBCODE(1)||'-'
2111 ||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2112 end if;
2113 end if;
2114 close csr_gen_msg;
2115
2116 END loop;
2117
2118 hr_delta_sync_messages.update_psft_sync_run(1,'JOBCODE_DELTA_SYNC',p_effective_date,errbuf,retcode);
2119 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2120 FND_FILE.put_line(fnd_file.log,'Jobcode Delta Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2121 end if;
2122
2123 EXCEPTION
2124 WHEN OTHERS THEN
2125 hr_delta_sync_messages.update_psft_sync_run(3,'JOBCODE_DELTA_SYNC',p_effective_date,errbuf,retcode);
2126 errbuf := errbuf||SQLERRM;
2127 retcode := '1';
2128 FND_FILE.put_line(fnd_file.log, 'Error in Jobcode Delta Synch Extraction: '||SQLCODE);
2129 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2130 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
2131
2132 end hr_jobcode_delta_sync;
2133 /*Procedure to extract the jobcode data for delta synch process ends here*/
2134 /*Procedure to extract the organization data for delta synch process begins here*/
2135 procedure hr_organizaton_delta_sync(errbuf OUT NOCOPY VARCHAR2
2136 ,retcode OUT NOCOPY VARCHAR2
2137 ,p_party_site_id in NUMBER)
2138 is
2139 p_bg_id hr_all_organization_units.business_group_id%type;
2140 p_dept_id hr_all_organization_units.organization_id%type;
2141 p_eff_date date;
2142 p_loc_id hr_all_organization_units.location_id%type;
2143 p_person_id per_org_manager_v.person_id%type;
2144 p_full_name per_org_manager_v.full_name%type;
2145 p_bg_name hr_all_organization_units.name%type;
2146 p_eff_status varchar2(10);
2147 p_effective_date date default sysdate;
2148 l_params WF_PARAMETER_LIST_T;
2149 p_last_update_date date;
2150 p_unique_key number;
2151 p_row_id rowid;
2152 p_gen_msg VARCHAR2(4000);
2153 p_gen_status varchar2(10);
2154
2155
2156 cursor fet_orgn_fsync(p_max_run_date date) is
2157 select org.business_group_id,
2158 org.organization_id,
2159 case when org.date_to is null then org.date_from
2160 else org.date_to end,
2161 case when org.date_to is null then 'ACTIVE'
2162 else 'INACTIVE' end,
2163 org.name,
2164 org.location_id,
2165 mgr.person_id,
2166 mgr.full_name,
2167 org.last_update_date
2168 from hr_all_organization_units org
2169 ,per_org_manager_v mgr,hr_organization_information hrorg
2170 where org.business_group_id = mgr.business_group_id(+)
2171 and org.organization_id = mgr.organization_id(+)
2172 and hrorg.organization_id = org.organization_id
2173 and hrorg.org_information1 = 'HR_ORG'
2174 and sysdate between org.date_from
2175 and nvl(org.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
2176 and sysdate between mgr.start_date(+) and mgr.end_date(+)
2177 and org.last_update_date > p_max_run_date
2178 and (org.business_group_id,org.organization_id) not in (select orgn.business_group_id,orgn.organization_id
2179 from hr_organization_delta_sync orgn
2180 where org.business_group_id = orgn.business_group_id
2181 and org.organization_id = orgn.organization_id
2182 and org.last_update_date <= orgn.last_update_date
2183 and orgn.status in ('QUEUED','SENT'));
2184
2185 cursor csr_gen_msg(p_evn_key varchar2)
2186 is select generation_status,generation_message
2187 from ecx_out_process_v prcs
2188 where document_id = p_evn_key;
2189
2190 cursor fet_delta_status
2191 is
2192 select business_group_id,organization_id,event_key,last_update_date from
2193 hr_organization_delta_sync
2194 where status = 'QUEUED';
2195
2196 p_bgrp_id number(15);
2197 p_orgn_id number(15);
2198 p_lstupd_date date;
2199
2200 cursor fet_orgn_sync(p_bgrp_id number,p_orgn_id number,p_lstupd_date date)
2201 is
2202 select org.business_group_id,
2203 org.organization_id,
2204 case when org.date_to is null then org.date_from
2205 else org.date_to end,
2206 case when org.date_to is null then 'ACTIVE'
2207 else 'INACTIVE' end,
2208 org.name,
2209 org.location_id,
2210 mgr.person_id,
2211 mgr.full_name,
2212 org.last_update_date
2213 from hr_all_organization_units org
2214 ,per_org_manager_v mgr,hr_organization_information hrorg
2215 where org.business_group_id = mgr.business_group_id(+)
2216 and org.organization_id = mgr.organization_id(+)
2217 and hrorg.organization_id = org.organization_id
2218 and hrorg.org_information1 = 'HR_ORG'
2219 and sysdate between org.date_from
2220 and nvl(org.date_to, to_date('31-12-4712', 'DD-MM-YYYY'))
2221 and sysdate between mgr.start_date(+) and mgr.end_date(+)
2222 and org.organization_id = p_orgn_id
2223 and org.business_group_id = p_bgrp_id
2224 and org.last_update_date >= p_lstupd_date ;
2225
2226 p_event_key_gen varchar2(50);
2227
2228 cursor fet_psft_run_dt is
2229 select max(run_date)
2230 from hr_psft_sync_run
2231 where process = 'ORG_DELTA_SYNC'
2232 and run_date < p_effective_date
2233 and status = 'COMPLETED';
2234
2235 cursor fet_psft_run_dt1 is
2236 select max(run_date)
2237 from hr_psft_sync_run
2238 where process = 'ORG_FULL_SYNC'
2239 and status = 'COMPLETED';
2240
2241 cursor fet_psft_sync is
2242 select count('x')
2243 from hr_psft_sync_run
2244 where process = 'ORG_DELTA_SYNC'
2245 and run_date < p_effective_date
2246 and status = 'STARTED';
2247
2248 l_dummy number;
2249 p_max_run_date date;
2250
2251 begin
2252
2253 open fet_psft_sync;
2254 fetch fet_psft_sync into l_dummy;
2255 close fet_psft_sync;
2256 if l_dummy = 0
2257 then
2258 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2259 FND_FILE.put_line(fnd_file.log,'Organization Delta synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2260 hr_delta_sync_messages.insert_psft_sync_run(2,'ORG_DELTA_SYNC',errbuf,retcode);
2261
2262
2263
2264 open fet_psft_run_dt;
2265 fetch fet_psft_run_dt into p_max_run_date;
2266 close fet_psft_run_dt;
2267
2268 if p_max_run_date is null
2269 then
2270 open fet_psft_run_dt1;
2271 fetch fet_psft_run_dt1 into p_max_run_date;
2272 close fet_psft_run_dt1;
2273 end if;
2274
2275 open fet_delta_status;
2276 loop
2277 fetch fet_delta_status into p_bgrp_id,p_orgn_id,p_event_key_gen,p_lstupd_date;
2278
2279 if fet_delta_status%found then
2280
2281 update hr_organization_delta_sync
2282 set status = 'SENT'
2283 where event_key = p_event_key_gen;
2284 commit;
2285
2286 open fet_orgn_sync(p_bgrp_id,p_orgn_id,p_lstupd_date);
2287 fetch fet_orgn_sync 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,p_last_update_date;
2288 if fet_orgn_sync%found then
2289
2290 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
2291 insert into hr_organization_delta_sync(BUSINESS_GROUP_ID,
2292 ORGANIZATION_ID,
2293 BUSINESS_GROUP_NAME ,
2294 EFFECTIVE_DATE,
2295 EFFECTIVE_STATUS ,
2296 COMPANY,
2297 SETID_LOCATION,
2298 LOCATION_ID,
2299 MANAGER_ID ,
2300 MANAGER_FULL_NAME,
2301 LAST_UPDATE_DATE ,
2302 STATUS,
2303 EFFECTIVE_STATUS_DATE ,
2304 EVENT_KEY
2305 )
2306 values(p_bg_id,p_dept_id,p_bg_name,p_eff_date,p_eff_status,p_bg_id,p_bg_id,p_loc_id,p_person_id,p_full_name,p_last_update_date,'QUEUED',p_effective_date,
2307 to_char(p_dept_id)||'-'||to_char(p_unique_key));
2308 commit;
2309
2310 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
2311 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'ORGN',l_params);
2312 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
2313 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', to_char(p_dept_id)||'-'||to_char(p_unique_key), l_params);
2314 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
2315 p_event_key => to_char(p_dept_id)||'-'||to_char(p_unique_key),
2316 p_parameters => l_params);
2317
2318 open csr_gen_msg(to_char(p_dept_id)||'-'||to_char(p_unique_key));
2319
2320 fetch csr_gen_msg into p_gen_status,p_gen_msg;
2321 if csr_gen_msg%found then
2322
2323 if p_gen_status not in ('0','10') then
2324 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2325 FND_FILE.put_line(fnd_file.log,'Organization Delta Synch Data Extraction Ends for the document id '||to_char(p_dept_id)||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2326 end if;
2327 end if;
2328 close csr_gen_msg;
2329
2330
2331 end if;
2332 close fet_orgn_sync;
2333 else
2334 exit;
2335 end if;
2336 end loop;
2337
2338 close fet_delta_status;
2339
2340 open fet_orgn_fsync(p_max_run_date);
2341 loop
2342 fetch fet_orgn_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,p_last_update_date;
2343 if fet_orgn_fsync%found then
2344 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
2345 insert into hr_organization_delta_sync(BUSINESS_GROUP_ID,
2346 ORGANIZATION_ID,
2347 BUSINESS_GROUP_NAME ,
2348 EFFECTIVE_DATE,
2349 EFFECTIVE_STATUS ,
2350 COMPANY,
2351 SETID_LOCATION,
2352 LOCATION_ID,
2353 MANAGER_ID ,
2354 MANAGER_FULL_NAME,
2355 LAST_UPDATE_DATE ,
2356 STATUS,
2357 EFFECTIVE_STATUS_DATE ,
2358 EVENT_KEY
2359 )
2360 values(p_bg_id,p_dept_id,p_bg_name,p_eff_date,p_eff_status,p_bg_id,p_bg_id,p_loc_id,p_person_id,p_full_name,p_last_update_date,'QUEUED',p_effective_date,
2361 to_char(p_dept_id)||'-'||to_char(p_unique_key));
2362 commit;
2363 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
2364 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'ORGN',l_params);
2365 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
2366 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', to_char(p_dept_id)||'-'||to_char(p_unique_key), l_params);
2367 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
2368 p_event_key => to_char(p_dept_id)||'-'||to_char(p_unique_key),
2369 p_parameters => l_params);
2370
2371 open csr_gen_msg(to_char(p_dept_id)||'-'||to_char(p_unique_key));
2372
2373 fetch csr_gen_msg into p_gen_status,p_gen_msg;
2374 if csr_gen_msg%found then
2375
2376 if p_gen_status not in ('0','10') then
2377 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2378 FND_FILE.put_line(fnd_file.log,'Organization Delta Synch Data Extraction Ends for the document id '||to_char(p_dept_id)||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2379 end if;
2380 end if;
2381 close csr_gen_msg;
2382 else
2383 exit;
2384 end if;
2385 end loop;
2386 close fet_orgn_fsync;
2387
2388 hr_delta_sync_messages.update_psft_sync_run(1,'ORG_DELTA_SYNC',p_effective_date,errbuf,retcode);
2389 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2390 FND_FILE.put_line(fnd_file.log,'Organization Delta Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2391 end if;
2392
2393 exception
2394 when OTHERS then
2395 hr_delta_sync_messages.update_psft_sync_run(3,'ORG_DELTA_SYNC',p_effective_date,errbuf,retcode);
2396 errbuf := errbuf||SQLERRM;
2397 retcode := '1';
2398 FND_FILE.put_line(fnd_file.log, 'Error in Organization Delta Synch Extraction: '||SQLCODE);
2399 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2400 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
2401 end hr_organizaton_delta_sync;
2402 /*Procedure to extract the organization data for delta synch process begins here*/
2403
2404 /*Procedure to extract the business group data for delta synch process begins here*/
2405 procedure hr_businessgrp_delta_sync(errbuf OUT NOCOPY VARCHAR2
2406 ,retcode OUT NOCOPY VARCHAR2
2407 ,p_party_site_id in NUMBER)
2408 is
2409 p_bg_id PER_BUSINESS_GROUPS.business_group_id%type;
2410 p_bg_name PER_BUSINESS_GROUPS.name%type;
2411 p_eff_status varchar2(10);
2412 p_eff_date date;
2413 p_effective_date date default sysdate;
2414 l_params WF_PARAMETER_LIST_T;
2415 p_last_update_date date;
2416 p_unique_key number;
2417 p_gen_msg VARCHAR2(4000);
2418 p_gen_status varchar2(10);
2419
2420
2421 cursor fet_bg_fsync(p_max_run_date date) is
2422 select business_group_id,
2423 name,
2424 case when date_to is null then date_from
2425 else date_to end,
2426 case when date_to is null then 'ACTIVE'
2427 else 'INACTIVE' end,
2428 last_update_date
2429 from hr_all_organization_units org
2430 where last_update_date > p_max_run_date
2431 and org.organization_id = org.business_group_id
2432 and (business_group_id) not in (select business_group_id
2433 from hr_bgrp_delta_sync bg
2434 where org.business_group_id = bg.business_group_id
2435 and org.last_update_date <= bg.last_update_date
2436 and bg.status in ('QUEUED','SENT'));
2437
2438 cursor fet_delta_status
2439 is
2440 select business_group_id,event_key,last_update_date from
2441 hr_bgrp_delta_sync
2442 where status = 'QUEUED';
2443
2444 p_bgrp_id number(15);
2445 p_lstupd_date date;
2446
2447 cursor fet_bg_sync(p_bgrp_id number,p_lstupd_date date)
2448 is
2449 select business_group_id,
2450 name,
2451 case when date_to is null then date_from
2452 else date_to end,
2453 case when date_to is null then 'ACTIVE'
2454 else 'INACTIVE' end,
2455 last_update_date
2456 from hr_all_organization_units org
2457 where business_group_id = p_bgrp_id
2458 and business_group_id = organization_id
2459 and last_update_date >= p_lstupd_date;
2460
2461 cursor csr_gen_msg(p_evn_key varchar2)
2462 is select generation_status,generation_message
2463 from ecx_out_process_v prcs
2464 where document_id = p_evn_key;
2465
2466 p_event_key_gen varchar2(50);
2467
2468 cursor fet_psft_run_dt is
2469 select max(run_date)
2470 from hr_psft_sync_run
2471 where process = 'BG_DELTA_SYNC'
2472 and run_date < p_effective_date
2473 and status = 'COMPLETED';
2474
2475 cursor fet_psft_run_dt1 is
2476 select max(run_date)
2477 from hr_psft_sync_run
2478 where process = 'BG_FULL_SYNC'
2479 and status = 'COMPLETED';
2480
2481 cursor fet_psft_sync is
2482 select count('x')
2483 from hr_psft_sync_run
2484 where process = 'BG_DELTA_SYNC'
2485 and run_date < p_effective_date
2486 and status = 'STARTED';
2487
2488 l_dummy number;
2489 p_max_run_date date;
2490
2491 begin
2492
2493 open fet_psft_sync;
2494 fetch fet_psft_sync into l_dummy;
2495 close fet_psft_sync;
2496 if l_dummy = 0
2497 then
2498 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2499 FND_FILE.put_line(fnd_file.log,'Business Group Delta synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2500 hr_delta_sync_messages.insert_psft_sync_run(2,'BG_DELTA_SYNC',errbuf,retcode);
2501
2502
2503
2504 open fet_psft_run_dt;
2505 fetch fet_psft_run_dt into p_max_run_date;
2506 close fet_psft_run_dt;
2507
2508 if p_max_run_date is null
2509 then
2510 open fet_psft_run_dt1;
2511 fetch fet_psft_run_dt1 into p_max_run_date;
2512 close fet_psft_run_dt1;
2513 end if;
2514
2515 open fet_delta_status;
2516 loop
2517 fetch fet_delta_status into p_bgrp_id,p_event_key_gen,p_lstupd_date;
2518
2519 if fet_delta_status%found then
2520
2521 update hr_bgrp_delta_sync
2522 set status = 'SENT'
2523 where event_key = p_event_key_gen;
2524 commit;
2525
2526 open fet_bg_sync(p_bgrp_id,p_lstupd_date);
2527 fetch fet_bg_sync into p_bg_id,p_bg_name,p_eff_date,p_eff_status,p_last_update_date;
2528 if fet_bg_sync%found then
2529
2530 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
2531 insert into hr_bgrp_delta_sync(BUSINESS_GROUP_ID,BUSINESS_GROUP_NAME,
2532 EFFECTIVE_DATE,EFFECTIVE_STATUS,LAST_UPDATE_DATE ,STATUS,EFFECTIVE_STATUS_DATE,
2533 EVENT_KEY)
2534 values(p_bg_id,p_bg_name,p_eff_date,p_eff_status,p_last_update_date,'QUEUED',p_effective_date,
2535 to_char(p_bg_id)||'-'||to_char(p_unique_key));
2536 commit;
2537
2538
2539 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
2540 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'BGRP',l_params);
2541 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
2542 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', to_char(p_bg_id)||'-'||to_char(p_unique_key), l_params);
2543 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
2544 p_event_key => to_char(p_bg_id)||'-'||to_char(p_unique_key),
2545 p_parameters => l_params);
2546
2547 open csr_gen_msg(to_char(p_bg_id)||'-'||to_char(p_unique_key));
2548
2549 fetch csr_gen_msg into p_gen_status,p_gen_msg;
2550 if csr_gen_msg%found then
2551
2552 if p_gen_status not in ('0','10') then
2553 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2554 FND_FILE.put_line(fnd_file.log,'Business Group Delta Synch Data Extraction Ends for the document id '||to_char(p_bg_id)||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2555 end if;
2556 end if;
2557 close csr_gen_msg;
2558
2559 end if;
2560 close fet_bg_sync;
2561 else
2562 exit;
2563 end if;
2564 end loop;
2565
2566 close fet_delta_status;
2567
2568 open fet_bg_fsync(p_max_run_date);
2569 loop
2570 fetch fet_bg_fsync into p_bg_id,p_bg_name,p_eff_date,p_eff_status,p_last_update_date;
2571 if fet_bg_fsync%found then
2572 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
2573 insert into hr_bgrp_delta_sync(BUSINESS_GROUP_ID,BUSINESS_GROUP_NAME,
2574 EFFECTIVE_DATE,EFFECTIVE_STATUS,LAST_UPDATE_DATE ,STATUS,EFFECTIVE_STATUS_DATE,
2575 EVENT_KEY)
2576 values(p_bg_id,p_bg_name,p_eff_date,p_eff_status,p_last_update_date,'QUEUED',p_effective_date,
2577 to_char(p_bg_id)||'-'||to_char(p_unique_key));
2578 commit;
2579 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
2580 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'BGRP',l_params);
2581 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
2582 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', to_char(p_bg_id)||'-'||to_char(p_unique_key), l_params);
2583 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
2584 p_event_key => to_char(p_bg_id)||'-'||to_char(p_unique_key),
2585 p_parameters => l_params);
2586
2587 open csr_gen_msg(to_char(p_bg_id)||'-'||to_char(p_unique_key));
2588
2589 fetch csr_gen_msg into p_gen_status,p_gen_msg;
2590 if csr_gen_msg%found then
2591
2592 if p_gen_status not in ('0','10') then
2593 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2594 FND_FILE.put_line(fnd_file.log,'Business Group Delta Synch Data Extraction Ends for the document id '||to_char(p_bg_id)||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2595 end if;
2596 end if;
2597 close csr_gen_msg;
2598 else
2599 exit;
2600 end if;
2601 end loop;
2602 close fet_bg_fsync;
2603
2604 hr_delta_sync_messages.update_psft_sync_run(1,'BG_DELTA_SYNC',p_effective_date,errbuf,retcode);
2605 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2606 FND_FILE.put_line(fnd_file.log,'Business Group Delta Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2607 end if;
2608
2609 exception
2610 when OTHERS then
2611 hr_delta_sync_messages.update_psft_sync_run(3,'BG_DELTA_SYNC',p_effective_date,errbuf,retcode);
2612 errbuf := errbuf||SQLERRM;
2613 retcode := '1';
2614 FND_FILE.put_line(fnd_file.log, 'Error in Business Group Delta Synch Extraction: '||SQLCODE);
2615 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2616 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
2617 end hr_businessgrp_delta_sync;
2618 /*Procedure to extract the business group data for delta synch process ends here*/
2619
2620 /*Procedure to extract the payroll group data for delta synch process begins here*/
2621 procedure hr_payroll_delta_sync(errbuf OUT NOCOPY VARCHAR2
2622 ,retcode OUT NOCOPY VARCHAR2
2623 ,p_party_site_id in NUMBER)
2624 is
2625 p_pyrl_id pay_all_payrolls_f.payroll_id%type;
2626 p_pyrl_name pay_all_payrolls_f.payroll_name%type;
2627 p_bg_id pay_all_payrolls_f.business_group_id%type;
2628 p_eff_date date;
2629 p_eff_status varchar2(10);
2630 p_effective_date date default sysdate;
2631 l_params WF_PARAMETER_LIST_T;
2632 p_last_update_date date;
2633 p_unique_key number;
2634 p_gen_msg VARCHAR2(4000);
2635 p_gen_status varchar2(10);
2636
2637
2638 cursor fet_pyrl_fsync(p_max_run_date date) is
2639 select payroll_id,
2640 payroll_name,
2641 business_group_id,
2642 case when p_effective_date > add_months(first_period_end_date,NUMBER_OF_YEARS*12)
2643 then add_months(first_period_end_date,NUMBER_OF_YEARS*12) else (select min(effective_start_date) from
2644 pay_all_payrolls_f pay1
2645 where pay1.payroll_id = pay.payroll_id
2646 and pay1.business_group_id = pay.business_group_id) end,
2647 case when p_effective_date > add_months(first_period_end_date,NUMBER_OF_YEARS*12)
2648 then 'INACTIVE' else 'ACTIVE' end,
2649 last_update_date
2650 from pay_all_payrolls_f pay
2651 where last_update_date > p_max_run_date
2652 and p_effective_date between effective_start_date and effective_end_date
2653 and (payroll_id,business_group_id) not in (select pyrl.payroll_id,pyrl.business_group_id
2654 from hr_pyrl_delta_sync pyrl
2655 where pay.payroll_id = pyrl.payroll_id
2656 and pay.business_group_id = pyrl.business_group_id
2657 and pay.last_update_date <= pyrl.last_update_date
2658 and pyrl.status in ('QUEUED','SENT'));
2659
2660 cursor csr_gen_msg(p_evn_key varchar2)
2661 is select generation_status,generation_message
2662 from ecx_out_process_v prcs
2663 where document_id = p_evn_key;
2664
2665 cursor fet_delta_status
2666 is
2667 select payroll_id,business_group_id,event_key,last_update_date from
2668 hr_pyrl_delta_sync
2669 where status = 'QUEUED';
2670
2671 p_payroll_id number(9,0);
2672 p_bgrp_id NUMBER(15,0);
2673 p_lstupd_date date;
2674
2675 cursor fet_pyrl_sync(p_payroll_id number,p_bgrp_id number,p_lstupd_date date)
2676 is
2677 select payroll_id,
2678 payroll_name,
2679 business_group_id,
2680 case when p_effective_date > add_months(first_period_end_date,NUMBER_OF_YEARS*12)
2681 then add_months(first_period_end_date,NUMBER_OF_YEARS*12) else (select min(effective_start_date) from
2682 pay_all_payrolls_f pay1
2683 where pay1.payroll_id = pay.payroll_id
2684 and pay1.business_group_id = pay.business_group_id) end,
2685 case when p_effective_date > add_months(first_period_end_date,NUMBER_OF_YEARS*12)
2686 then 'INACTIVE' else 'ACTIVE' end,
2687 last_update_date
2688 from pay_all_payrolls_f pay
2689 where pay.payroll_id = p_payroll_id
2690 and pay.business_group_id = p_bgrp_id
2691 and p_effective_date between effective_start_date and effective_end_date
2692 and last_update_date >= p_lstupd_date;
2693
2694 p_event_key_gen varchar2(50);
2695
2696 cursor fet_psft_run_dt is
2697 select max(run_date)
2698 from hr_psft_sync_run
2699 where process = 'PYRL_DELTA_SYNC'
2700 and run_date < p_effective_date
2701 and status = 'COMPLETED';
2702
2703 cursor fet_psft_run_dt1 is
2704 select max(run_date)
2705 from hr_psft_sync_run
2706 where process = 'PYRL_FULL_SYNC'
2707 and status = 'COMPLETED';
2708
2709 cursor fet_psft_sync is
2710 select count('x')
2711 from hr_psft_sync_run
2712 where process = 'PYRL_DELTA_SYNC'
2713 and run_date < p_effective_date
2714 and status = 'STARTED';
2715
2716 l_dummy number;
2717 p_max_run_date date;
2718
2719 begin
2720
2721 open fet_psft_sync;
2722 fetch fet_psft_sync into l_dummy;
2723 close fet_psft_sync;
2724 if l_dummy = 0
2725 then
2726 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2727 FND_FILE.put_line(fnd_file.log,'Payroll Delta synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2728 hr_delta_sync_messages.insert_psft_sync_run(2,'PYRL_DELTA_SYNC',errbuf,retcode);
2729
2730
2731
2732 open fet_psft_run_dt;
2733 fetch fet_psft_run_dt into p_max_run_date;
2734 close fet_psft_run_dt;
2735
2736 if p_max_run_date is null
2737 then
2738 open fet_psft_run_dt1;
2739 fetch fet_psft_run_dt1 into p_max_run_date;
2740 close fet_psft_run_dt1;
2741 end if;
2742
2743 open fet_delta_status;
2744 loop
2745 fetch fet_delta_status into p_payroll_id,p_bgrp_id,p_event_key_gen,p_lstupd_date;
2746
2747 if fet_delta_status%found then
2748
2749 update hr_pyrl_delta_sync
2750 set status = 'SENT'
2751 where event_key = p_event_key_gen;
2752 commit;
2753
2754 open fet_pyrl_sync(p_payroll_id,p_bgrp_id,p_lstupd_date);
2755 fetch fet_pyrl_sync into p_pyrl_id,p_pyrl_name,p_bg_id,p_eff_date,p_eff_status,p_last_update_date;
2756 if fet_pyrl_sync%found then
2757
2758 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
2759 insert into hr_pyrl_delta_sync(PAYROLL_ID,
2760 PAYROLL_NAME,
2761 BUSINESS_GROUP_ID ,
2762 EFFECTIVE_DATE,
2763 EFFECTIVE_STATUS ,
2764 LAST_UPDATE_DATE,
2765 STATUS ,
2766 EFFECTIVE_STATUS_DATE,
2767 EVENT_KEY
2768 )
2769 values(p_pyrl_id,p_pyrl_name,p_bg_id,p_eff_date,p_eff_status,p_last_update_date,'QUEUED',p_effective_date,
2770 to_char(p_pyrl_id)||'-'||to_char(p_unique_key));
2771 commit;
2772
2773
2774 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
2775 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'PYRL',l_params);
2776 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
2777 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', to_char(p_pyrl_id)||'-'||to_char(p_unique_key), l_params);
2778 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
2779 p_event_key => to_char(p_pyrl_id)||'-'||to_char(p_unique_key),
2780 p_parameters => l_params);
2781
2782 open csr_gen_msg(to_char(p_pyrl_id)||'-'||to_char(p_unique_key));
2783
2784 fetch csr_gen_msg into p_gen_status,p_gen_msg;
2785 if csr_gen_msg%found then
2786
2787 if p_gen_status not in ('0','10') then
2788 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2789 FND_FILE.put_line(fnd_file.log,'Payroll Delta Synch Data Extraction Ends for the document id '||to_char(p_pyrl_id)||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2790 end if;
2791 end if;
2792 close csr_gen_msg;
2793
2794 end if;
2795 close fet_pyrl_sync;
2796 else
2797 exit;
2798 end if;
2799 end loop;
2800
2801 close fet_delta_status;
2802
2803 open fet_pyrl_fsync(p_max_run_date);
2804 loop
2805 fetch fet_pyrl_fsync into p_pyrl_id,p_pyrl_name,p_bg_id,p_eff_date,p_eff_status,p_last_update_date;
2806 if fet_pyrl_fsync%found then
2807 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
2808 insert into hr_pyrl_delta_sync(PAYROLL_ID,
2809 PAYROLL_NAME,
2810 BUSINESS_GROUP_ID ,
2811 EFFECTIVE_DATE,
2812 EFFECTIVE_STATUS ,
2813 LAST_UPDATE_DATE,
2814 STATUS ,
2815 EFFECTIVE_STATUS_DATE,
2816 EVENT_KEY
2817 )
2818 values(p_pyrl_id,p_pyrl_name,p_bg_id,p_eff_date,p_eff_status,p_last_update_date,'QUEUED',p_effective_date,
2819 to_char(p_pyrl_id)||'-'||to_char(p_unique_key));
2820 commit;
2821 WF_EVENT.AddParameterToList('ECX_TRANSACTION_TYPE', 'HRHD', l_params);
2822 WF_EVENT.AddParameterToList('ECX_TRANSACTION_SUBTYPE', 'PYRL',l_params);
2823 WF_EVENT.AddParameterToList('ECX_PARTY_SITE_ID', to_char(p_party_site_id), l_params);
2824 WF_EVENT.AddParameterToList('ECX_DOCUMENT_ID', to_char(p_pyrl_id)||'-'||to_char(p_unique_key), l_params);
2825 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhdrir.xml.out',
2826 p_event_key => to_char(p_pyrl_id)||'-'||to_char(p_unique_key),
2827 p_parameters => l_params);
2828
2829 open csr_gen_msg(to_char(p_pyrl_id)||'-'||to_char(p_unique_key));
2830
2831 fetch csr_gen_msg into p_gen_status,p_gen_msg;
2832 if csr_gen_msg%found then
2833
2834 if p_gen_status not in ('0','10') then
2835 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2836 FND_FILE.put_line(fnd_file.log,'Payroll Delta Synch Data Extraction Ends for the document id '||to_char(p_pyrl_id)||'-'||to_char(p_unique_key)||'due to :'||p_gen_msg||'on'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2837 end if;
2838 end if;
2839 close csr_gen_msg;
2840 else
2841 exit;
2842 end if;
2843 end loop;
2844 close fet_pyrl_fsync;
2845
2846 hr_delta_sync_messages.update_psft_sync_run(1,'PYRL_DELTA_SYNC',p_effective_date,errbuf,retcode);
2847 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2848 FND_FILE.put_line(fnd_file.log,'Payroll Delta Synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2849 end if;
2850
2851 exception
2852 when OTHERS then
2853 hr_delta_sync_messages.update_psft_sync_run(3,'PYRL_DELTA_SYNC',p_effective_date,errbuf,retcode);
2854 errbuf := errbuf||SQLERRM;
2855 retcode := '1';
2856 FND_FILE.put_line(fnd_file.log, 'Error in Payroll Delta Synch Extraction: '||SQLCODE);
2857 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2858 FND_FILE.put_line(fnd_file.log, 'Error Msg: '||substr(SQLERRM,1,700));
2859 end hr_payroll_delta_sync;
2860 /*Procedure to extract the payroll group data for delta synch process ends here*/
2861
2862 /*Common procedure called from concurrent program to extract the data begins here*/
2863 procedure hr_delta_sync (ERRBUF OUT NOCOPY varchar2,
2864 RETCODE OUT NOCOPY number,
2865 p_process_name in varchar2,
2866 p_party_site_id in number)
2867 is
2868 p_effective_date date default sysdate;
2869 begin
2870 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2871 FND_FILE.put_line(fnd_file.log,'Delta synch Data Extraction Begins:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2872 if p_process_name = 'STATE_DELTA_SYNCH'
2873 then
2874 hr_delta_sync_messages.hr_state_delta_sync(ERRBUF,RETCODE,p_party_site_id);
2875 elsif p_process_name = 'COUNTRY_DELTA_SYNCH'
2876 then
2877
2878 hr_delta_sync_messages.hr_country_delta_sync(ERRBUF,RETCODE,p_party_site_id);
2879 elsif p_process_name = 'LOCATION_DELTA_SYNCH'
2880 then
2881 hr_delta_sync_messages.hr_location_delta_sync(ERRBUF,RETCODE,p_party_site_id);
2882 elsif p_process_name = 'PERSON_DELTA_SYNCH'
2883 then
2884 hr_delta_sync_messages.hr_person_delta_sync(ERRBUF,RETCODE,p_party_site_id);
2885 elsif p_process_name = 'WORKFORCE_DELTA_SYNCH'
2886 then
2887 hr_delta_sync_messages.hr_workforce_delta_sync(ERRBUF,RETCODE,p_party_site_id);
2888 elsif p_process_name = 'JOBCODE_DELTA_SYNCH' then
2889 hr_delta_sync_messages.hr_jobcode_delta_sync(ERRBUF,RETCODE,p_party_site_id);
2890 elsif p_process_name = 'ORGANIZATION_DELTA_SYNCH' then
2891 hr_delta_sync_messages.hr_organizaton_delta_sync(ERRBUF,RETCODE,p_party_site_id);
2892 elsif p_process_name = 'BUSINESSGROUP_DELTA_SYNCH' then
2893 hr_delta_sync_messages.hr_businessgrp_delta_sync(ERRBUF,RETCODE,p_party_site_id);
2894 elsif p_process_name = 'PAYROLL_DELTA_SYNCH' then
2895 hr_delta_sync_messages.hr_payroll_delta_sync(ERRBUF,RETCODE,p_party_site_id);
2896 end if;
2897 FND_FILE.NEW_LINE(FND_FILE.log, 1);
2898 FND_FILE.put_line(fnd_file.log,'Delta synch Data Extraction Ends:'||to_char(p_effective_date, 'DD/MM/RRRR HH:MI:SS'));
2899 end hr_delta_sync;
2900 /*Common procedure called from concurrent program to extract the data ends here*/
2901
2902 /*common procedure to update the status of the sync data from message designer starts here*/
2903 PROCEDURE update_delta_msg_status(p_event_key varchar2,
2904 p_process_name varchar2)
2905 is
2906 begin
2907 if p_process_name = 'STATE_DELTA_SYNCH'
2908 then
2909 update hr_state_delta_sync
2910 set status = 'SENT'
2911 where event_key = p_event_key;
2912
2913 elsif p_process_name = 'COUNTRY_DELTA_SYNCH'
2914 then
2915
2916 update hr_country_delta_sync
2917 set status = 'SENT'
2918 where event_key = p_event_key;
2919
2920 elsif p_process_name = 'LOCATION_DELTA_SYNCH'
2921 then
2922
2923 update hr_locn_delta_sync
2924 set status = 'SENT'
2925 where event_key = p_event_key;
2926
2927 elsif p_process_name = 'PERSON_DELTA_SYNCH'
2928 then
2929
2930 update hr_person_delta_sync
2931 set status = 'SENT'
2932 where record_key = p_event_key;
2933
2934 elsif p_process_name = 'WORKFORCE_DELTA_SYNCH'
2935 then
2936
2937 update HR_WORKFORCE_DELTA_SYNC
2938 set status = 'SENT'
2939 where event_key = p_event_key;
2940
2941 elsif p_process_name = 'JOBCODE_DELTA_SYNCH' then
2942
2943 update HR_JOBCODE_DELTA_SYNC
2944 set status = 'SENT'
2945 where event_key = p_event_key;
2946
2947 elsif p_process_name = 'ORGANIZATION_DELTA_SYNCH' then
2948
2949 update hr_organization_delta_sync
2950 set status = 'SENT'
2951 where event_key = p_event_key;
2952
2953 elsif p_process_name = 'BUSINESSGROUP_DELTA_SYNCH' then
2954
2955 update hr_bgrp_delta_sync
2956 set status = 'SENT'
2957 where event_key = p_event_key;
2958
2959 elsif p_process_name = 'PAYROLL_DELTA_SYNCH' then
2960
2961 update hr_pyrl_delta_sync
2962 set status = 'SENT'
2963 where event_key = p_event_key;
2964
2965 end if;
2966 end;
2967
2968 /*common procedure to update the status of the sync data from message designer ends here*/
2969
2970 end hr_delta_sync_messages;
2971