[Home] [Help]
PACKAGE BODY: APPS.HR_HRHD_RIR_WF
Source
1 PACKAGE BODY HR_HRHD_RIR_WF as
2 /* $Header: perhdrsyn.pkb 120.3.12020000.3 2013/02/22 14:20:13 pathota ship $ */
3
4 /* Procedures called inside the workflow process HR RIR Process starts */
5
6 /*procedure called for update job api */
7 procedure update_job(itemtype in varchar2,
8 itemkey in varchar2,
9 actid in number,
10 funcmode in varchar2,
11 resultout in out NOCOPY varchar2)
12 is
13
14 p_event_key varchar2(100);
15 p_event_message WF_EVENT_T;
16 p_event_data clob;
17 p_job_data clob;
18 p_unique_key number;
19 p_job_id number;
20 v_document dbms_xmldom.domdocument;
21 v_nodes dbms_xmldom.DOMNodeList;
22 v_element_x dbms_xmldom.DOMElement;
23 v_node dbms_xmldom.DOMNode;
24 v_node_2 dbms_xmldom.DOMNode;
25 v_tag VARCHAR2(100);
26 p_date_to VARCHAR2(100);
27
28 begin
29
30 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
31 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
32 p_event_data := p_event_message.event_data;
33
34 -- extract the job id from the xml event message
35 v_document := dbms_xmldom.newdomdocument(p_event_data);
36 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'job_id');
37 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
38 v_node := dbms_xmldom.item(v_nodes,0);
39 v_tag := dbms_xmldom.getNodeName(v_node);
40 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
41 p_job_id := dbms_xmldom.getnodevalue(v_node_2);
42
43
44 -- extract the date to from the xml event message
45 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_to');
46 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
47 v_node := dbms_xmldom.item(v_nodes,0);
48 v_tag := dbms_xmldom.getNodeName(v_node);
49 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
50 p_date_to := dbms_xmldom.getnodevalue(v_node_2);
51
52 p_job_data := hr_hrhd_rir_wf.sif_job_data(p_job_id => p_job_id,
53 p_job_op_flag => 'U',
54 p_date_to => FALSE);
55
56
57
58 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
59
60
61
62 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.jobchange',
63 p_event_key => to_char(p_job_id)||'-'||to_char(p_unique_key),
64 p_event_data => p_job_data);
65
66 if p_date_to is not null
67 then
68 p_job_data := hr_hrhd_rir_wf.sif_job_data(p_job_id => p_job_id,
69 p_job_op_flag => 'U',
70 p_date_to => TRUE);
71
72 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
73
74
75
76 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.jobchange',
77 p_event_key => to_char(p_job_id)||'-'||to_char(p_unique_key),
78 p_event_data => p_job_data);
79 end if;
80
81 resultout := 'COMPLETE';
82
83 exception
84 when OTHERS then
85 resultout := 'FAILED';
86 end update_job;
87
88 /*procedure called for create job api */
89 procedure create_job(itemtype in varchar2,
90 itemkey in varchar2,
91 actid in number,
92 funcmode in varchar2,
93 resultout in out NOCOPY varchar2)
94 is
95 p_event_key varchar2(100);
96 p_event_message WF_EVENT_T;
97 p_event_data clob;
98 p_job_data clob;
99 p_unique_key number;
100 p_job_id number;
101 v_document dbms_xmldom.domdocument;
102 v_nodes dbms_xmldom.DOMNodeList;
103 v_element_x dbms_xmldom.DOMElement;
104 v_node dbms_xmldom.DOMNode;
105 v_node_2 dbms_xmldom.DOMNode;
106 v_tag VARCHAR2(100);
107 p_date_to VARCHAR2(100);
108
109 begin
110
111 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
112 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
113 p_event_data := p_event_message.event_data;
114
115 -- extract the job id from the xml event message
116 v_document := dbms_xmldom.newdomdocument(p_event_data);
117 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'job_id');
118 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
119 v_node := dbms_xmldom.item(v_nodes,0);
120 v_tag := dbms_xmldom.getNodeName(v_node);
121 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
122 p_job_id := dbms_xmldom.getnodevalue(v_node_2);
123
124
125 -- extract the date to from the xml event message
126 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_to');
127 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
128 v_node := dbms_xmldom.item(v_nodes,0);
129 v_tag := dbms_xmldom.getNodeName(v_node);
130 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
131 p_date_to := dbms_xmldom.getnodevalue(v_node_2);
132
133
134 p_job_data := hr_hrhd_rir_wf.sif_job_data(p_job_id => p_job_id,
135 p_job_op_flag => 'I',
136 p_date_to => FALSE);
137
138 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
139
140
141 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.jobchange',
142 p_event_key => to_char(p_job_id)||'-'||to_char(p_unique_key),
143 p_event_data => p_job_data);
144
145 if p_date_to is not null
146 then
147 p_job_data := hr_hrhd_rir_wf.sif_job_data(p_job_id => p_job_id,
148 p_job_op_flag => 'U',
149 p_date_to => TRUE);
150
151 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
152
153
154 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.jobchange',
155 p_event_key => to_char(p_job_id)||'-'||to_char(p_unique_key),
156 p_event_data => p_job_data);
157 end if;
158
159 resultout := 'COMPLETE';
160 exception
161 when OTHERS then
162 resultout := 'FAILED';
163 end create_job;
164
165 /*procedure called for delete job api*/
166 procedure delete_job(itemtype in varchar2,
167 itemkey in varchar2,
168 actid in number,
169 funcmode in varchar2,
170 resultout in out NOCOPY varchar2)
171 is
172 p_event_key varchar2(100);
173 p_event_message WF_EVENT_T;
174 p_event_data clob;
175 p_job_data clob;
176 p_unique_key number;
177 p_job_id number;
178 v_document dbms_xmldom.domdocument;
179 v_nodes dbms_xmldom.DOMNodeList;
180 v_element_x dbms_xmldom.DOMElement;
181 v_node dbms_xmldom.DOMNode;
182 v_node_2 dbms_xmldom.DOMNode;
183 v_tag VARCHAR2(100);
184 p_date_to VARCHAR2(100);
185
186 begin
187
188 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
189 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
190 p_event_data := p_event_message.event_data;
191
192 -- extract the job id from the xml event message
193 v_document := dbms_xmldom.newdomdocument(p_event_data);
194 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'job_id');
195 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
196 v_node := dbms_xmldom.item(v_nodes,0);
197 v_tag := dbms_xmldom.getNodeName(v_node);
198 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
199 p_job_id := dbms_xmldom.getnodevalue(v_node_2);
200
201
202 p_job_data := hr_hrhd_rir_wf.sif_job_data(p_job_id => p_job_id,
203 p_job_op_flag => 'D',
204 p_date_to => TRUE);
205
206
207 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
208
209
210
211 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.jobchange',
212 p_event_key => to_char(p_job_id)||'-'||to_char(p_unique_key),
213 p_event_data => p_job_data);
214
215
216 resultout := 'COMPLETE';
217 exception
218 when OTHERS then
219 resultout := 'FAILED';
220 end delete_job;
221
222
223 /*procedure called for create location api*/
224 procedure create_location(itemtype in varchar2,
225 itemkey in varchar2,
226 actid in number,
227 funcmode in varchar2,
228 resultout in out NOCOPY varchar2)
229 is
230
231 p_event_key varchar2(100);
232 p_event_message WF_EVENT_T;
233 p_event_data clob;
234 p_location_data clob;
235 p_unique_key number;
236 p_location_id number;
237 v_document dbms_xmldom.domdocument;
238 v_nodes dbms_xmldom.DOMNodeList;
239 v_element_x dbms_xmldom.DOMElement;
240 v_node dbms_xmldom.DOMNode;
241 v_node_2 dbms_xmldom.DOMNode;
242 v_tag VARCHAR2(100);
243 p_inactive_date VARCHAR2(100);
244
245 begin
246
247 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
248 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
249 p_event_data := p_event_message.event_data;
250
251 -- extract the location id from the xml event message
252 v_document := dbms_xmldom.newdomdocument(p_event_data);
253 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'location_id');
254 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
255 v_node := dbms_xmldom.item(v_nodes,0);
256 v_tag := dbms_xmldom.getNodeName(v_node);
257 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
258 p_location_id := dbms_xmldom.getnodevalue(v_node_2);
259
260
261 -- extract the date to from the xml event message
262 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'inactive_date');
263 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
264 v_node := dbms_xmldom.item(v_nodes,0);
265 v_tag := dbms_xmldom.getNodeName(v_node);
266 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
267 p_inactive_date := dbms_xmldom.getnodevalue(v_node_2);
268
269 p_location_data := hr_hrhd_rir_wf.sif_location_data(p_location_id => p_location_id,
270 p_loc_op_flag => 'I',
271 p_inactive_date => FALSE);
272
273 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
274
275
276
277 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.locchange',
278 p_event_key => to_char(p_location_id)||'-'||to_char(p_unique_key),
279 p_event_data => p_location_data);
280
281
282
283 if p_inactive_date is not null
284 then
285 p_location_data := hr_hrhd_rir_wf.sif_location_data(p_location_id => p_location_id,
286 p_loc_op_flag => 'U',
287 p_inactive_date => TRUE);
288
289 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
290
291
292
293 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.locchange',
294 p_event_key => to_char(p_location_id)||'-'||to_char(p_unique_key),
295 p_event_data => p_location_data);
296 end if;
297
298 resultout := 'COMPLETE';
299
300 exception
301 when OTHERS then
302 resultout := 'FAILED';
303
304 end create_location;
305
306
307 /*procedure called for update location api*/
308
309 procedure update_location(itemtype in varchar2,
310 itemkey in varchar2,
311 actid in number,
312 funcmode in varchar2,
313 resultout in out NOCOPY varchar2)
314 is
315
316 p_event_key varchar2(100);
317 p_event_message WF_EVENT_T;
318 p_event_data clob;
319 p_location_data clob;
320 p_unique_key number;
321 p_location_id number;
322 v_document dbms_xmldom.domdocument;
323 v_nodes dbms_xmldom.DOMNodeList;
324 v_element_x dbms_xmldom.DOMElement;
325 v_node dbms_xmldom.DOMNode;
326 v_node_2 dbms_xmldom.DOMNode;
327 v_tag VARCHAR2(100);
328 p_inactive_date VARCHAR2(100);
329
330 begin
331
332 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
333 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
334 p_event_data := p_event_message.event_data;
335
336 -- extract the location id from the xml event message
337 v_document := dbms_xmldom.newdomdocument(p_event_data);
338 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'location_id');
339 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
340 v_node := dbms_xmldom.item(v_nodes,0);
341 v_tag := dbms_xmldom.getNodeName(v_node);
342 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
343 p_location_id := dbms_xmldom.getnodevalue(v_node_2);
344
345
346 -- extract the date to from the xml event message
347 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'inactive_date');
348 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
349 v_node := dbms_xmldom.item(v_nodes,0);
350 v_tag := dbms_xmldom.getNodeName(v_node);
351 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
352 p_inactive_date := dbms_xmldom.getnodevalue(v_node_2);
353
354 p_location_data := hr_hrhd_rir_wf.sif_location_data(p_location_id => p_location_id,
355 p_loc_op_flag => 'U',
356 p_inactive_date => FALSE);
357
358 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
359
360
361 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.locchange',
362 p_event_key => to_char(p_location_id)||'-'||to_char(p_unique_key),
363 p_event_data => p_location_data);
364
365 if p_inactive_date is not null
366 then
367 p_location_data := hr_hrhd_rir_wf.sif_location_data(p_location_id => p_location_id,
368 p_loc_op_flag => 'U',
369 p_inactive_date => TRUE);
370
371 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
372
373
374
375 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.locchange',
376 p_event_key => to_char(p_location_id)||'-'||to_char(p_unique_key),
377 p_event_data => p_location_data);
378 end if;
379
380 resultout := 'COMPLETE';
381
382 exception
383 when OTHERS then
384 resultout := 'FAILED';
385
386 end update_location;
387
388
389 /*procedure called for delete location api*/
390
391 procedure delete_location(itemtype in varchar2,
392 itemkey in varchar2,
393 actid in number,
394 funcmode in varchar2,
395 resultout in out NOCOPY varchar2)
396 is
397
398 p_event_key varchar2(100);
399 p_event_message WF_EVENT_T;
400 p_event_data clob;
401 p_location_data clob;
402 p_unique_key number;
403 p_location_id number;
404 v_document dbms_xmldom.domdocument;
405 v_nodes dbms_xmldom.DOMNodeList;
406 v_element_x dbms_xmldom.DOMElement;
407 v_node dbms_xmldom.DOMNode;
408 v_node_2 dbms_xmldom.DOMNode;
409 v_tag VARCHAR2(100);
410 p_inactive_date VARCHAR2(100);
411
412 begin
413
414 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
415 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
416 p_event_data := p_event_message.event_data;
417
418 -- extract the location id from the xml event message
419 v_document := dbms_xmldom.newdomdocument(p_event_data);
420 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'location_id');
421 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
422 v_node := dbms_xmldom.item(v_nodes,0);
423 v_tag := dbms_xmldom.getNodeName(v_node);
424 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
425 p_location_id := dbms_xmldom.getnodevalue(v_node_2);
426
427
428 p_location_data := hr_hrhd_rir_wf.sif_location_data(p_location_id => p_location_id,
429 p_loc_op_flag => 'D',
430 p_inactive_date => TRUE);
431
432 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
433
434
435
436 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.locchange',
437 p_event_key => to_char(p_location_id)||'-'||to_char(p_unique_key),
438 p_event_data => p_location_data);
439
440
441 resultout := 'COMPLETE';
442
443 exception
444 when OTHERS then
445 resultout := 'FAILED';
446
447 end delete_location;
448
449 /*procedure called for create Organization api*/
450
451 procedure create_organization(itemtype in varchar2,
452 itemkey in varchar2,
453 actid in number,
454 funcmode in varchar2,
455 resultout in out NOCOPY varchar2)
456 is
457
458 p_event_key varchar2(100);
459 p_event_message WF_EVENT_T;
460 p_event_data clob;
461 p_organization_data clob;
462 p_unique_key number;
463 p_organization_id number;
464 v_document dbms_xmldom.domdocument;
465 v_nodes dbms_xmldom.DOMNodeList;
466 v_element_x dbms_xmldom.DOMElement;
467 v_node dbms_xmldom.DOMNode;
468 v_node_2 dbms_xmldom.DOMNode;
469 v_tag VARCHAR2(100);
470 p_date_to VARCHAR2(100);
471 p_hr_org_chk number;
472 p_date_chk varchar2(11);
473
474 cursor csr_chk_hr_org(p_org_id number) is
475 select org.organization_id,to_char(org.date_to,'DD/MM/YYYY')
476 from hr_all_organization_units org
477 ,hr_organization_information hrorg
478 where hrorg.organization_id = org.organization_id
479 and hrorg.org_information1 = 'HR_ORG'
480 and org.organization_id = p_org_id;
481
482
483 begin
484
485 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
486 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
487 p_event_data := p_event_message.event_data;
488
489 -- extract the organization id from the xml event message
490 v_document := dbms_xmldom.newdomdocument(p_event_data);
491 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'organization_id');
492 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
493 v_node := dbms_xmldom.item(v_nodes,0);
494 v_tag := dbms_xmldom.getNodeName(v_node);
495 v_node_2 := dbms_xmldom.getfirstchild(v_node );
496 p_organization_id := dbms_xmldom.getnodevalue(v_node_2);
497
498
499 -- extract the date to from the xml event message
500 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_to');
501 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
502 v_node := dbms_xmldom.item(v_nodes,0);
503 v_tag := dbms_xmldom.getNodeName(v_node);
504 v_node_2 := dbms_xmldom.getfirstchild(v_node );
505 p_date_to := dbms_xmldom.getnodevalue(v_node_2);
506
507 open csr_chk_hr_org(p_organization_id);
508 fetch csr_chk_hr_org into p_hr_org_chk,p_date_chk;
509 if csr_chk_hr_org%found
510 then
511
512 p_date_to := nvl(p_date_to,p_date_chk);
513
514 p_organization_data := hr_hrhd_rir_wf.sif_organization_data(p_organization_id => p_organization_id,
515 p_org_op_flag => 'I',
516 p_date_to => FALSE);
517
518 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
519
520
521
522 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.orgchange',
523 p_event_key => to_char(p_organization_id)||'-'||to_char(p_unique_key),
524 p_event_data => p_organization_data);
525
526
527
528 if p_date_to is not null
529 then
530 p_organization_data := hr_hrhd_rir_wf.sif_organization_data(p_organization_id => p_organization_id,
531 p_org_op_flag => 'U',
532 p_date_to => TRUE);
533
534 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
535
536
537
538 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.orgchange',
539 p_event_key => to_char(p_organization_id)||'-'||to_char(p_unique_key),
540 p_event_data => p_organization_data);
541 end if;
542
543 end if;
544 close csr_chk_hr_org;
545
546 resultout := 'COMPLETE';
547
548 exception
549 when OTHERS then
550 resultout := 'FAILED';
551
552 end create_organization;
553
554
555 /*procedure called for update Organization api*/
556 procedure update_organization(itemtype in varchar2,
557 itemkey in varchar2,
558 actid in number,
559 funcmode in varchar2,
560 resultout in out NOCOPY varchar2)
561 is
562
563 p_event_key varchar2(100);
564 p_event_message WF_EVENT_T;
565 p_event_data clob;
566 p_organization_data clob;
567 p_unique_key number;
568 p_organization_id number;
569 v_document dbms_xmldom.domdocument;
570 v_nodes dbms_xmldom.DOMNodeList;
571 v_element_x dbms_xmldom.DOMElement;
572 v_node dbms_xmldom.DOMNode;
573 v_node_2 dbms_xmldom.DOMNode;
574 v_tag VARCHAR2(100);
575 p_date_to VARCHAR2(100);
576 p_hr_org_chk varchar2(10);
577
578 cursor csr_chk_hr_org(p_org_id number) is
579 select 'x'
580 from hr_all_organization_units org
581 ,hr_organization_information hrorg
582 where hrorg.organization_id = org.organization_id
583 and hrorg.org_information1 = 'HR_ORG'
584 and org.organization_id = p_org_id;
585
586 begin
587
588 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
589 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
590 p_event_data := p_event_message.event_data;
591
592 -- extract the organization id from the xml event message
593 v_document := dbms_xmldom.newdomdocument(p_event_data);
594 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'organization_id');
595 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
596 v_node := dbms_xmldom.item(v_nodes,0);
597 v_tag := dbms_xmldom.getNodeName(v_node);
598 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
599 p_organization_id := dbms_xmldom.getnodevalue(v_node_2);
600
601
602 -- extract the date to from the xml event message
603 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_to');
604 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
605 v_node := dbms_xmldom.item(v_nodes,0);
606 v_tag := dbms_xmldom.getNodeName(v_node);
607 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
608 p_date_to := dbms_xmldom.getnodevalue(v_node_2);
609
610 open csr_chk_hr_org(p_organization_id);
611 fetch csr_chk_hr_org into p_hr_org_chk;
612 if csr_chk_hr_org%found
613 then
614
615 p_organization_data := hr_hrhd_rir_wf.sif_organization_data(p_organization_id => p_organization_id,
616 p_org_op_flag => 'U',
617 p_date_to => FALSE);
618
619 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
620
621
622
623 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.orgchange',
624 p_event_key => to_char(p_organization_id)||'-'||to_char(p_unique_key),
625 p_event_data => p_organization_data);
626
627 if p_date_to is not null
628 then
629 p_organization_data := hr_hrhd_rir_wf.sif_organization_data(p_organization_id => p_organization_id,
630 p_org_op_flag => 'U',
631 p_date_to => TRUE);
632
633 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
634
635
636 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.orgchange',
637 p_event_key => to_char(p_organization_id)||'-'||to_char(p_unique_key),
638 p_event_data => p_organization_data);
639 end if;
640
641 end if;
642 resultout := 'COMPLETE';
643
644 exception
645 when OTHERS then
646 resultout := 'FAILED';
647
648 end update_organization;
649
650
651 /*procedure called for delete Organization api*/
652
653 procedure delete_organization(itemtype in varchar2,
654 itemkey in varchar2,
655 actid in number,
656 funcmode in varchar2,
657 resultout in out NOCOPY varchar2)
658 is
659
660 p_event_key varchar2(100);
661 p_event_message WF_EVENT_T;
662 p_event_data clob;
663 p_organization_data clob;
664 p_unique_key number;
665 p_organization_id number;
666 v_document dbms_xmldom.domdocument;
667 v_nodes dbms_xmldom.DOMNodeList;
668 v_element_x dbms_xmldom.DOMElement;
669 v_node dbms_xmldom.DOMNode;
670 v_node_2 dbms_xmldom.DOMNode;
671 v_tag VARCHAR2(100);
672 p_date_to VARCHAR2(100);
673 p_hr_org_chk varchar2(10);
674
675 cursor csr_chk_hr_org(p_org_id number) is
676 select 'x'
677 from hr_all_organization_units org
678 ,hr_organization_information hrorg
679 where hrorg.organization_id = org.organization_id
680 and hrorg.org_information1 = 'HR_ORG'
681 and org.organization_id = p_org_id;
682
683 begin
684
685 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
686 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
687 p_event_data := p_event_message.event_data;
688
689 -- extract the organization id from the xml event message
690 v_document := dbms_xmldom.newdomdocument(p_event_data);
691 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'organization_id');
692 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
693 v_node := dbms_xmldom.item(v_nodes,0);
694 v_tag := dbms_xmldom.getNodeName(v_node);
695 v_node_2 := dbms_xmldom.getfirstchild(v_node ); -- can't recall why needed
696 p_organization_id := dbms_xmldom.getnodevalue(v_node_2);
697
698 /* open csr_chk_hr_org(p_organization_id);
699 fetch csr_chk_hr_org into p_hr_org_chk;
700 if csr_chk_hr_org%found
701 then
702 */ -- Fix For Bug # 14587491
703 p_organization_data := hr_hrhd_rir_wf.sif_organization_data(p_organization_id => p_organization_id,
704 p_org_op_flag => 'D',
705 p_date_to => TRUE);
706
707 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
708
709
710
711 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.orgchange',
712 p_event_key => to_char(p_organization_id)||'-'||to_char(p_unique_key),
713 p_event_data => p_organization_data);
714
715 -- end if; --- Fix For Bug # 14587491
716
717 resultout := 'COMPLETE';
718
719 exception
720 when OTHERS then
721 resultout := 'FAILED';
722 end delete_organization;
723
724 /*procedure called for create person api*/
725 procedure create_person(itemtype in varchar2,
726 itemkey in varchar2,
727 actid in number,
728 funcmode in varchar2,
729 resultout in out NOCOPY varchar2)
730
731 is
732 p_event_key varchar2(100);
733 p_event_message WF_EVENT_T;
734 p_event_data clob;
735 p_person_id per_all_people_f.person_id%type;
736 p_person_data clob;
737 p_assignment_data clob;
738 p_unique_key number;
739 p_assignment_id number;
740 v_document dbms_xmldom.domdocument;
741 v_nodes dbms_xmldom.DOMNodeList;
742 v_element_x dbms_xmldom.DOMElement;
743 v_node dbms_xmldom.DOMNode;
744 v_node_2 dbms_xmldom.DOMNode;
745 v_tag VARCHAR2(100);
746 p_eff_date VARCHAR2(100);
747 p_date date;
748 myparameters wf_parameter_list_t;
749
750 l_transaction_id Number;
751 l_transaction_id_canonical Varchar2(30);
752
753 cursor csr_asg_id(p_person_id varchar2,p_date date) is
754 select distinct assignment_id
755 from per_all_assignments_f
756 where person_id = p_person_id
757 and trunc(effective_start_date) = trunc(p_date);
758
759
760 begin
761
762 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
763 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
764 p_event_data := p_event_message.event_data;
765
766 -- extract the person id from the xml event message
767 v_document := dbms_xmldom.newdomdocument(p_event_data);
768 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'person_id');
769 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
770 v_node := dbms_xmldom.item(v_nodes,0);
771 v_tag := dbms_xmldom.getNodeName(v_node);
772 v_node_2 := dbms_xmldom.getfirstchild(v_node );
773 p_person_id := dbms_xmldom.getnodevalue(v_node_2);
774
775 -- extract the assignment id from the xml event message
776
777 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'assignment_id');
778 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
779 v_node := dbms_xmldom.item(v_nodes,0);
780 v_tag := dbms_xmldom.getNodeName(v_node);
781 v_node_2 := dbms_xmldom.getfirstchild(v_node );
782 p_assignment_id := dbms_xmldom.getnodevalue(v_node_2);
783
784 -- extract the effective_start_date from the xml event message
785
786 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'per_effective_start_date');
787 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
788 v_node := dbms_xmldom.item(v_nodes,0);
789 v_tag := dbms_xmldom.getNodeName(v_node);
790 v_node_2 := dbms_xmldom.getfirstchild(v_node );
791 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
792
793 p_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
794
795 if p_date > trunc(sysdate)
796 then
797
798 wf_event.AddParameterToList('person_id',p_person_id,myparameters);
799 wf_event.AddParameterToList('assignment_id',p_assignment_id,myparameters);
800 wf_event.AddParameterToList('eff_date',p_date,myparameters);
801 wf_event.AddParameterToList('person_op_flag','I',myparameters);
802 wf_event.AddParameterToList('asg_op_flag','I',myparameters);
803 hr_hrhd_event_data_handler.hrhd_push_event_data(p_event_data,l_transaction_id);
804 l_transaction_id_canonical := FND_NUMBER.number_to_canonical(l_transaction_id);
805 wf_event.AddParameterToList('transaction_id',l_transaction_id_canonical,myparameters);
806
807 --wf_event.AddParameterToList('event_data',p_event_data,myparameters);
808
809 wf_util.call_me_later(p_callback => 'hr_hrhd_rir_wf.person_callbackable',
810 p_when => p_date,
811 p_parameters => myparameters);
812
813 if p_assignment_id is not null
814 then
815 wf_util.call_me_later(p_callback => 'hr_hrhd_rir_wf.workforce_callbackable',
816 p_when => p_date,
817 p_parameters => myparameters);
818 end if;
819
820 else
821
822 p_person_data := hr_hrhd_rir_wf.sif_person_data(p_person_id =>p_person_id ,
823 p_address_id => null,
824 p_phone_id => null,
825 p_person_op_flag => 'I',
826 p_date => p_date);
827
828
829 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
830
831 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.personchange',
832 p_event_key => to_char(p_person_id)||'-'||to_char(p_unique_key),
833 p_event_data => p_person_data);
834
835 if p_assignment_id is not null
836 then
837 p_assignment_data := hr_hrhd_rir_wf.sif_workforce_data(p_assignment_id =>p_assignment_id ,
838 p_asg_op_flag => 'I',
839 p_date => p_date);
840
841
842 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
843
844 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.asgchange',
845 p_event_key => to_char(p_assignment_id)||'-'||to_char(p_unique_key),
846 p_event_data => p_assignment_data);
847
848 else
849
850 for rec_ass in csr_asg_id(p_person_id,p_date) loop
851
852 if p_date > trunc(sysdate)
853 then
854
855
856 wf_event.AddParameterToList('person_id',p_person_id,myparameters);
857 wf_event.AddParameterToList('assignment_id',rec_ass.assignment_id,myparameters);
858 wf_event.AddParameterToList('p_eff_start_date',p_date ,myparameters);
859 wf_event.AddParameterToList('asg_op_flag','I',myparameters);
860 l_transaction_id := null;
861 hr_hrhd_event_data_handler.hrhd_push_event_data(p_event_data,l_transaction_id);
862 l_transaction_id_canonical := FND_NUMBER.number_to_canonical(l_transaction_id);
863 wf_event.AddParameterToList('transaction_id',l_transaction_id_canonical,myparameters);
864
865 --wf_event.AddParameterToList('event_data',p_event_data,myparameters);
866
867 wf_util.call_me_later(p_callback => 'hr_hrhd_rir_wf.workforce_callbackable',
868 p_when => p_date,
869 p_parameters => myparameters);
870
871 else
872
873 p_assignment_data := hr_hrhd_rir_wf.sif_workforce_data(p_assignment_id =>rec_ass.assignment_id ,
874 p_asg_op_flag => 'I',
875 p_date => p_date);
876
877
878 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
879
880 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.asgchange',
881 p_event_key => to_char(rec_ass.assignment_id)||'-'||to_char(p_unique_key),
882 p_event_data => p_assignment_data);
883
884 end if;
885
886 end loop;
887
888 end if;
889
890 end if;
891
892 resultout := 'COMPLETE';
893
894 exception
895 when OTHERS then
896 resultout := 'FAILED';
897 end create_person;
898
899 /*procedure called for update_person api*/
900 procedure update_person(itemtype in varchar2,
901 itemkey in varchar2,
902 actid in number,
903 funcmode in varchar2,
904 resultout in out NOCOPY varchar2)
905
906 is
907 p_event_key varchar2(100);
908 p_event_message WF_EVENT_T;
909 p_event_data clob;
910 p_person_data clob;
911 p_person_id per_all_people_f.person_id%type;
912 p_unique_key number;
913 v_document dbms_xmldom.domdocument;
914 v_nodes dbms_xmldom.DOMNodeList;
915 v_element_x dbms_xmldom.DOMElement;
916 v_node dbms_xmldom.DOMNode;
917 v_node_2 dbms_xmldom.DOMNode;
918 v_tag VARCHAR2(100);
919 p_eff_date VARCHAR2(100);
920 p_date date;
921 myparameters wf_parameter_list_t;
922 p_prd_service_id varchar2(100);
923 p_at_date varchar2(100);
924 p_act_term_date date;
925 p_event_name varchar2(100);
926
927 p_assignment_id per_all_assignments_f.assignment_id%type;
928 p_eff_start_date per_all_assignments_f.effective_start_date%type;
929 p_assignment_data clob;
930
931 l_transaction_id Number;
932 l_transaction_id_canonical Varchar2(30);
933
934 cursor csr_person_id(p_prd_service_id varchar2,p_date date) is
935 select distinct person_id
936 from per_periods_of_service
937 where period_of_service_id = p_prd_service_id
938 and p_date between date_start and nvl(actual_termination_date,to_date('31/12/4712','DD/MM/YYYY'));
939
940 /*code added for 8424994*/
941
942 cursor csr_asg_id(p_person_id varchar2,p_date date) is
943 select distinct assignment_id
944 from per_all_assignments_f
945 where person_id = p_person_id
946 and p_date between effective_start_date and effective_end_date;
947
948 /*code added for 8424994*/
949
950
951 begin
952
953 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
954 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
955 p_event_data := p_event_message.event_data;
956 p_event_name := p_event_message.event_name;
957
958 -- extract the person id from the xml event message
959 v_document := dbms_xmldom.newdomdocument(p_event_data);
960 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'person_id');
961 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
962 v_node := dbms_xmldom.item(v_nodes,0);
963 v_tag := dbms_xmldom.getNodeName(v_node);
964 v_node_2 := dbms_xmldom.getfirstchild(v_node );
965 p_person_id := dbms_xmldom.getnodevalue(v_node_2);
966
967 -- extract the assignment id from the xml event message
968
969 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'assignment_id');
970 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
971 v_node := dbms_xmldom.item(v_nodes,0);
972 v_tag := dbms_xmldom.getNodeName(v_node);
973 v_node_2 := dbms_xmldom.getfirstchild(v_node );
974 p_assignment_id := dbms_xmldom.getnodevalue(v_node_2);
975
976 -- extract the effective_start_date from the xml event message
977
978 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'effective_start_date');
979 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
980 v_node := dbms_xmldom.item(v_nodes,0);
981 v_tag := dbms_xmldom.getNodeName(v_node);
982 v_node_2 := dbms_xmldom.getfirstchild(v_node );
983 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
984
985 -- extract the period of service id from the xml event message
986
987 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'period_of_service_id');
988 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
989 v_node := dbms_xmldom.item(v_nodes,0);
990 v_tag := dbms_xmldom.getNodeName(v_node);
991 v_node_2 := dbms_xmldom.getfirstchild(v_node );
992 p_prd_service_id := dbms_xmldom.getnodevalue(v_node_2);
993
994 -- extract the actual termination date
995
996 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'actual_termination_date');
997 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
998 v_node := dbms_xmldom.item(v_nodes,0);
999 v_tag := dbms_xmldom.getNodeName(v_node);
1000 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1001 p_at_date := dbms_xmldom.getnodevalue(v_node_2);
1002
1003 /*code added for 8424994*/
1004
1005 p_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
1006
1007 if ((p_event_name = 'oracle.apps.per.api.ex_employee.actual_termination_emp') or (p_event_name = 'oracle.apps.per.api.ex_employee.reverse_terminate_employee')
1008 or (p_event_name = 'oracle.apps.per.api.contingent_worker.reverse_terminate_placement') or (p_event_name = 'oracle.apps.per.api.contingent_worker.terminate_placement') ) then
1009
1010 p_date := to_date(substr(p_at_date,1,10),'YYYY/MM/DD');
1011
1012 end if;
1013
1014 /*code added for 8424994*/
1015
1016 if p_prd_service_id is not null
1017 then
1018 open csr_person_id(p_prd_service_id,p_date);
1019 fetch csr_person_id into p_person_id;
1020 close csr_person_id;
1021 end if;
1022
1023 /*code added for 8424994*/
1024
1025 if p_date > trunc(sysdate)
1026 then
1027
1028 wf_event.AddParameterToList('person_id',p_person_id,myparameters);
1029 wf_event.AddParameterToList('eff_date',p_date,myparameters);
1030 wf_event.AddParameterToList('person_op_flag','U',myparameters);
1031 hr_hrhd_event_data_handler.hrhd_push_event_data(p_event_data,l_transaction_id);
1032 l_transaction_id_canonical := FND_NUMBER.number_to_canonical(l_transaction_id);
1033 wf_event.AddParameterToList('transaction_id',l_transaction_id_canonical,myparameters);
1034
1035 --wf_event.AddParameterToList('event_data',p_event_data,myparameters);
1036
1037 wf_util.call_me_later(p_callback => 'hr_hrhd_rir_wf.person_callbackable',
1038 p_when => p_date,
1039 p_parameters => myparameters);
1040
1041
1042 else
1043
1044 p_person_data := hr_hrhd_rir_wf.sif_person_data(p_person_id =>p_person_id ,
1045 p_address_id => null,
1046 p_phone_id => null,
1047 p_person_op_flag => 'U',
1048 p_date => p_date);
1049
1050
1051 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1052
1053 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.personchange',
1054 p_event_key => to_char(p_person_id)||'-'||to_char(p_unique_key),
1055 p_event_data => p_person_data);
1056
1057
1058 end if;
1059
1060 /*code added for 8424994*/
1061
1062 if ((p_event_name = 'oracle.apps.per.api.ex_employee.actual_termination_emp') or (p_event_name = 'oracle.apps.per.api.ex_employee.reverse_terminate_employee')
1063 or (p_event_name = 'oracle.apps.per.api.contingent_worker.reverse_terminate_placement') or (p_event_name = 'oracle.apps.per.api.contingent_worker.terminate_placement') ) then
1064
1065 for rec_ass in csr_asg_id(p_person_id,p_date) loop
1066
1067 /*code added for 8424994*/
1068
1069 if p_date > trunc(sysdate)
1070 then
1071
1072
1073 wf_event.AddParameterToList('person_id',p_person_id,myparameters);
1074 wf_event.AddParameterToList('assignment_id',rec_ass.assignment_id,myparameters);
1075 wf_event.AddParameterToList('p_eff_start_date',p_date ,myparameters);
1076 wf_event.AddParameterToList('asg_op_flag','U',myparameters);
1077 l_transaction_id := 0;
1078 hr_hrhd_event_data_handler.hrhd_push_event_data(p_event_data,l_transaction_id);
1079 l_transaction_id_canonical := FND_NUMBER.number_to_canonical(l_transaction_id);
1080 wf_event.AddParameterToList('transaction_id',l_transaction_id_canonical,myparameters);
1081
1082 --wf_event.AddParameterToList('event_data',p_event_data,myparameters);
1083
1084 wf_util.call_me_later(p_callback => 'hr_hrhd_rir_wf.workforce_callbackable',
1085 p_when => p_date,
1086 p_parameters => myparameters);
1087
1088 else
1089
1090 p_assignment_data := hr_hrhd_rir_wf.sif_workforce_data(p_assignment_id =>rec_ass.assignment_id ,
1091 p_asg_op_flag => 'U',
1092 p_date => p_date);
1093
1094
1095 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1096
1097 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.asgchange',
1098 p_event_key => to_char(rec_ass.assignment_id)||'-'||to_char(p_unique_key),
1099 p_event_data => p_assignment_data);
1100
1101 end if;
1102
1103 end loop;
1104
1105 end if;
1106
1107 resultout := 'COMPLETE';
1108
1109 exception
1110 when OTHERS then
1111 resultout := 'FAILED';
1112 end update_person;
1113
1114 /*procedure called for create or update address api*/
1115 procedure cre_or_upd_address(itemtype in varchar2,
1116 itemkey in varchar2,
1117 actid in number,
1118 funcmode in varchar2,
1119 resultout in out NOCOPY varchar2)
1120
1121 is
1122 p_event_key varchar2(100);
1123 p_event_message WF_EVENT_T;
1124 p_event_data clob;
1125 p_person_data clob;
1126 p_person_id per_all_people_f.person_id%type;
1127 p_unique_key number;
1128
1129 v_document dbms_xmldom.domdocument;
1130 v_nodes dbms_xmldom.DOMNodeList;
1131 v_element_x dbms_xmldom.DOMElement;
1132 v_node dbms_xmldom.DOMNode;
1133 v_node_2 dbms_xmldom.DOMNode;
1134 v_tag VARCHAR2(100);
1135 p_eff_date VARCHAR2(100);
1136 p_date date;
1137 myparameters wf_parameter_list_t;
1138
1139 p_address_id per_addresses.address_id%type;
1140 p_addr_date_from per_addresses.date_from%type;
1141
1142 l_transaction_id Number :=0;
1143 l_transaction_id_canonical Varchar2(30);
1144
1145 cursor csr_person_id(p_addr_id varchar2,p_eff_date date)
1146 is select person_id from per_addresses
1147 where address_id = p_addr_id
1148 and p_eff_date between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
1149
1150
1151 begin
1152
1153 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
1154 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
1155 p_event_data := p_event_message.event_data;
1156
1157 -- extract the person id from the xml event message
1158 v_document := dbms_xmldom.newdomdocument(p_event_data);
1159 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'person_id');
1160 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1161 v_node := dbms_xmldom.item(v_nodes,0);
1162 v_tag := dbms_xmldom.getNodeName(v_node);
1163 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1164 p_person_id := dbms_xmldom.getnodevalue(v_node_2);
1165
1166 -- extract the assignment id from the xml event message
1167
1168 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'address_id');
1169 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1170 v_node := dbms_xmldom.item(v_nodes,0);
1171 v_tag := dbms_xmldom.getNodeName(v_node);
1172 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1173 p_address_id := dbms_xmldom.getnodevalue(v_node_2);
1174
1175 -- extract the effective_start_date from the xml event message
1176
1177 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_from');
1178 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1179 v_node := dbms_xmldom.item(v_nodes,0);
1180 v_tag := dbms_xmldom.getNodeName(v_node);
1181 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1182 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
1183
1184 p_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
1185
1186
1187 if (p_person_id is null) then
1188 open csr_person_id(p_address_id,p_date);
1189 fetch csr_person_id into p_person_id;
1190 close csr_person_id;
1191 end if;
1192
1193 if p_date > trunc(sysdate)
1194 then
1195
1196 wf_event.AddParameterToList('person_id',p_person_id,myparameters);
1197 wf_event.AddParameterToList('eff_date',p_date,myparameters);
1198 wf_event.AddParameterToList('address_id',p_address_id,myparameters);
1199 hr_hrhd_event_data_handler.hrhd_push_event_data(p_event_data,l_transaction_id);
1200 l_transaction_id_canonical := FND_NUMBER.number_to_canonical(l_transaction_id);
1201 wf_event.AddParameterToList('transaction_id',l_transaction_id_canonical,myparameters);
1202
1203 --wf_event.AddParameterToList('event_data',p_event_data,myparameters);
1204
1205 wf_util.call_me_later(p_callback => 'hr_hrhd_rir_wf.address_callbackable',
1206 p_when => p_date,
1207 p_parameters => myparameters);
1208
1209
1210 else
1211
1212 p_person_data := hr_hrhd_rir_wf.sif_person_data(p_person_id =>p_person_id ,
1213 p_address_id => p_address_id,
1214 p_phone_id => null,
1215 p_person_op_flag => 'U',
1216 p_date => p_date);
1217
1218 hr_utility.set_location('in hr_rir_wf.address '||p_address_id ,51);
1219 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1220
1221 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.personchange',
1222 p_event_key => to_char(p_person_id)||'-'||to_char(p_unique_key),
1223 p_event_data => p_person_data);
1224
1225
1226 end if;
1227
1228 resultout := 'COMPLETE';
1229
1230 exception
1231 when OTHERS then
1232 resultout := 'FAILED';
1233 end cre_or_upd_address;
1234
1235
1236
1237 /*procedure called for create or update phone api*/
1238 procedure cre_or_upd_phone(itemtype in varchar2,
1239 itemkey in varchar2,
1240 actid in number,
1241 funcmode in varchar2,
1242 resultout in out NOCOPY varchar2)
1243
1244 is
1245
1246 p_event_key varchar2(100);
1247 p_event_message WF_EVENT_T;
1248 p_event_data clob;
1249 p_person_data clob;
1250 p_person_id per_all_people_f.person_id%type;
1251 p_unique_key number;
1252
1253 v_document dbms_xmldom.domdocument;
1254 v_nodes dbms_xmldom.DOMNodeList;
1255 v_element_x dbms_xmldom.DOMElement;
1256 v_node dbms_xmldom.DOMNode;
1257 v_node_2 dbms_xmldom.DOMNode;
1258 v_tag VARCHAR2(100);
1259 p_eff_date VARCHAR2(100);
1260 p_date date;
1261 myparameters wf_parameter_list_t;
1262
1263 p_phone_id per_phones.phone_id%type;
1264 p_phn_date_from per_phones.date_from%type;
1265
1266 l_transaction_id Number :=0;
1267 l_transaction_id_canonical Varchar2(30);
1268
1269 cursor csr_person_id(p_phone_id varchar2,p_eff_date date)
1270 is select parent_id from per_phones
1271 where phone_id = p_phone_id
1272 and PARENT_TABLE = 'PER_ALL_PEOPLE_F'
1273 and p_eff_date between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
1274
1275 begin
1276
1277 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
1278 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
1279 p_event_data := p_event_message.event_data;
1280
1281 -- extract the person id from the xml event message
1282 v_document := dbms_xmldom.newdomdocument(p_event_data);
1283 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'parent_id');
1284 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1285 v_node := dbms_xmldom.item(v_nodes,0);
1286 v_tag := dbms_xmldom.getNodeName(v_node);
1287 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1288 p_person_id := dbms_xmldom.getnodevalue(v_node_2);
1289
1290 -- extract the assignment id from the xml event message
1291
1292 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'phone_id');
1293 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1294 v_node := dbms_xmldom.item(v_nodes,0);
1295 v_tag := dbms_xmldom.getNodeName(v_node);
1296 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1297 p_phone_id := dbms_xmldom.getnodevalue(v_node_2);
1298
1299 -- extract the effective_start_date from the xml event message
1300
1301 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_from');
1302 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1303 v_node := dbms_xmldom.item(v_nodes,0);
1304 v_tag := dbms_xmldom.getNodeName(v_node);
1305 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1306 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
1307
1308 p_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
1309
1310 if (p_person_id is null) then
1311 open csr_person_id(p_phone_id,p_date);
1312 fetch csr_person_id into p_person_id;
1313 close csr_person_id;
1314 end if;
1315
1316 if p_date > trunc(sysdate)
1317 then
1318
1319 wf_event.AddParameterToList('person_id',p_person_id,myparameters);
1320 wf_event.AddParameterToList('eff_date',p_date,myparameters);
1321 wf_event.AddParameterToList('phone_id',p_phone_id,myparameters);
1322 hr_hrhd_event_data_handler.hrhd_push_event_data(p_event_data,l_transaction_id);
1323 l_transaction_id_canonical := FND_NUMBER.number_to_canonical(l_transaction_id);
1324 wf_event.AddParameterToList('transaction_id',l_transaction_id_canonical,myparameters);
1325
1326 --wf_event.AddParameterToList('event_data',p_event_data,myparameters);
1327
1328 wf_util.call_me_later(p_callback => 'hr_hrhd_rir_wf.phone_callbackable',
1329 p_when => p_date,
1330 p_parameters => myparameters);
1331
1332
1333 else
1334
1335 p_person_data := hr_hrhd_rir_wf.sif_person_data(p_person_id =>p_person_id ,
1336 p_address_id => null,
1337 p_phone_id => p_phone_id,
1338 p_person_op_flag => 'U',
1339 p_date => p_date);
1340
1341
1342 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1343
1344 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.personchange',
1345 p_event_key => to_char(p_person_id)||'-'||to_char(p_unique_key),
1346 p_event_data => p_person_data);
1347
1348
1349 end if;
1350
1351 resultout := 'COMPLETE';
1352
1353 exception
1354 when OTHERS then
1355 resultout := 'FAILED';
1356
1357 end cre_or_upd_phone;
1358
1359 /*procedure called for create workforce api*/
1360 procedure create_workforce(itemtype in varchar2,
1361 itemkey in varchar2,
1362 actid in number,
1363 funcmode in varchar2,
1364 resultout in out NOCOPY varchar2)
1365
1366 is
1367 p_event_key varchar2(100);
1368 p_event_message WF_EVENT_T;
1369 p_event_data clob;
1370 p_assignment_data clob;
1371 p_unique_key number;
1372 v_document dbms_xmldom.domdocument;
1373 v_nodes dbms_xmldom.DOMNodeList;
1374 v_element_x dbms_xmldom.DOMElement;
1375 v_node dbms_xmldom.DOMNode;
1376 v_node_2 dbms_xmldom.DOMNode;
1377 v_tag VARCHAR2(100);
1378 p_eff_date VARCHAR2(100);
1379 myparameters wf_parameter_list_t;
1380 p_event_name varchar2(100);
1381 p_date date;
1382 -- data required for message
1383 p_person_id per_all_assignments_f.person_id%type;
1384 p_assignment_id per_all_assignments_f.assignment_id%type;
1385 p_eff_start_date per_all_assignments_f.effective_start_date%type;
1386
1387 l_transaction_id Number :=0;
1388 l_transaction_id_canonical Varchar2(30);
1389
1390 begin
1391
1392 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
1393 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
1394 p_event_data := p_event_message.event_data;
1395 v_document := dbms_xmldom.newdomdocument(p_event_data);
1396
1397
1398 -- extract the person id from the message
1399 v_document := dbms_xmldom.newdomdocument(p_event_data);
1400 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'person_id');
1401 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1402 v_node := dbms_xmldom.item(v_nodes,0);
1403 v_tag := dbms_xmldom.getNodeName(v_node);
1404 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1405 p_person_id := dbms_xmldom.getnodevalue(v_node_2);
1406
1407 -- extract the assignment id from the xml event message
1408 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'assignment_id');
1409 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1410 v_node := dbms_xmldom.item(v_nodes,0);
1411 v_tag := dbms_xmldom.getNodeName(v_node);
1412 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1413 p_assignment_id := dbms_xmldom.getnodevalue(v_node_2);
1414
1415 -- extract the effective_date from the xml event message
1416 v_document := dbms_xmldom.newdomdocument(p_event_data);
1417 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'effective_date');
1418 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1419 v_node := dbms_xmldom.item(v_nodes,0);
1420 v_tag := dbms_xmldom.getNodeName(v_node);
1421 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1422 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
1423 p_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
1424
1425 -- extract the effective_start_date from the xml event message
1426 v_document := dbms_xmldom.newdomdocument(p_event_data);
1427 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'effective_start_date');
1428 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1429 v_node := dbms_xmldom.item(v_nodes,0);
1430 v_tag := dbms_xmldom.getNodeName(v_node);
1431 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1432 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
1433 p_eff_start_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
1434
1435
1436
1437
1438 if p_eff_start_date > trunc(sysdate) -- to_date('10/10/2008','DD/MM/YYYY')
1439 then
1440
1441
1442 wf_event.AddParameterToList('person_id',p_person_id,myparameters);
1443 wf_event.AddParameterToList('assignment_id',p_assignment_id,myparameters);
1444 wf_event.AddParameterToList('eff_start_date',p_eff_start_date,myparameters);
1445 wf_event.AddParameterToList('asg_op_flag','I',myparameters);
1446 hr_hrhd_event_data_handler.hrhd_push_event_data(p_event_data,l_transaction_id);
1447 l_transaction_id_canonical := FND_NUMBER.number_to_canonical(l_transaction_id);
1448 wf_event.AddParameterToList('transaction_id',l_transaction_id_canonical,myparameters);
1449
1450 --wf_event.AddParameterToList('event_data',p_event_data,myparameters);
1451
1452 wf_util.call_me_later(p_callback => 'hr_hrhd_rir_wf.workforce_callbackable',
1453 p_when => p_eff_start_date,
1454 p_parameters => myparameters);
1455
1456 else
1457
1458 p_assignment_data := hr_hrhd_rir_wf.sif_workforce_data(p_assignment_id =>p_assignment_id ,
1459 p_asg_op_flag => 'I',
1460 p_date => p_eff_start_date);
1461
1462
1463 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1464
1465 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.asgchange',
1466 p_event_key => to_char(p_assignment_id)||'-'||to_char(p_unique_key),
1467 p_event_data => p_assignment_data);
1468
1469 end if;
1470
1471 resultout := 'COMPLETE';
1472
1473 exception
1474 when OTHERS then
1475 resultout := 'FAILED';
1476 end create_workforce;
1477
1478 /*procedure called for update workforce api*/
1479 procedure update_workforce(itemtype in varchar2,
1480 itemkey in varchar2,
1481 actid in number,
1482 funcmode in varchar2,
1483 resultout in out NOCOPY varchar2)
1484
1485 is
1486 p_event_key varchar2(100);
1487 p_event_message WF_EVENT_T;
1488 p_event_data clob;
1489 p_assignment_data clob;
1490 p_unique_key number;
1491 v_document dbms_xmldom.domdocument;
1492 v_nodes dbms_xmldom.DOMNodeList;
1493 v_element_x dbms_xmldom.DOMElement;
1494 v_node dbms_xmldom.DOMNode;
1495 v_node_2 dbms_xmldom.DOMNode;
1496 v_tag VARCHAR2(100);
1497 p_eff_date VARCHAR2(100);
1498 myparameters wf_parameter_list_t;
1499 p_event_name varchar2(100);
1500 p_date date;
1501 -- data required for message
1502 p_person_id per_all_assignments_f.person_id%type;
1503 p_assignment_id per_all_assignments_f.assignment_id%type;
1504 p_eff_start_date per_all_assignments_f.effective_start_date%type;
1505
1506 l_transaction_id Number :=0;
1507 l_transaction_id_canonical Varchar2(30);
1508
1509 begin
1510
1511 p_event_key := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'EVENT_KEY');
1512 p_event_message := Wf_Engine.GetActivityAttrEvent(itemtype, itemkey, actid, 'EVENT_MSG');
1513 p_event_data := p_event_message.event_data;
1514 v_document := dbms_xmldom.newdomdocument(p_event_data);
1515
1516
1517 -- extract the person id from the message
1518 v_document := dbms_xmldom.newdomdocument(p_event_data);
1519 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'person_id');
1520 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1521 v_node := dbms_xmldom.item(v_nodes,0);
1522 v_tag := dbms_xmldom.getNodeName(v_node);
1523 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1524 p_person_id := dbms_xmldom.getnodevalue(v_node_2);
1525
1526 -- extract the assignment id from the xml event message
1527 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'assignment_id');
1528 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1529 v_node := dbms_xmldom.item(v_nodes,0);
1530 v_tag := dbms_xmldom.getNodeName(v_node);
1531 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1532 p_assignment_id := dbms_xmldom.getnodevalue(v_node_2);
1533
1534 -- extract the effective_date from the xml event message
1535 v_document := dbms_xmldom.newdomdocument(p_event_data);
1536 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'effective_date');
1537 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1538 v_node := dbms_xmldom.item(v_nodes,0);
1539 v_tag := dbms_xmldom.getNodeName(v_node);
1540 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1541 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
1542 p_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
1543
1544 -- extract the effective_start_date from the xml event message
1545 v_document := dbms_xmldom.newdomdocument(p_event_data);
1546 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'effective_start_date');
1547 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
1548 v_node := dbms_xmldom.item(v_nodes,0);
1549 v_tag := dbms_xmldom.getNodeName(v_node);
1550 v_node_2 := dbms_xmldom.getfirstchild(v_node );
1551 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
1552 p_eff_start_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
1553
1554
1555
1556
1557 if p_eff_start_date > trunc(sysdate)
1558 then
1559
1560
1561 wf_event.AddParameterToList('person_id',p_person_id,myparameters);
1562 wf_event.AddParameterToList('assignment_id',p_assignment_id,myparameters);
1563 wf_event.AddParameterToList('eff_start_date',p_eff_start_date,myparameters);
1564 wf_event.AddParameterToList('asg_op_flag','U',myparameters);
1565 hr_hrhd_event_data_handler.hrhd_push_event_data(p_event_data,l_transaction_id);
1566 l_transaction_id_canonical := FND_NUMBER.number_to_canonical(l_transaction_id);
1567 wf_event.AddParameterToList('transaction_id',l_transaction_id_canonical,myparameters);
1568
1569 --wf_event.AddParameterToList('event_data',p_event_data,myparameters);
1570
1571 wf_util.call_me_later(p_callback => 'hr_hrhd_rir_wf.workforce_callbackable',
1572 p_when => p_eff_start_date,
1573 p_parameters => myparameters);
1574
1575 else
1576
1577 p_assignment_data := hr_hrhd_rir_wf.sif_workforce_data(p_assignment_id =>p_assignment_id ,
1578 p_asg_op_flag => 'U',
1579 p_date => p_eff_start_date);
1580
1581
1582 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
1583
1584 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.asgchange',
1585 p_event_key => to_char(p_assignment_id)||'-'||to_char(p_unique_key),
1586 p_event_data => p_assignment_data);
1587
1588 end if;
1589
1590 resultout := 'COMPLETE';
1591
1592 exception
1593 when OTHERS then
1594 resultout := 'FAILED';
1595 end update_workforce;
1596
1597 /* Procedures called inside the workflow process HR RIR Process ends*/
1598
1599 /*Functions to generate the xml data starts*/
1600
1601 /*Function to generate the workforce data*/
1602 FUNCTION sif_workforce_data(p_assignment_id in number,
1603 p_asg_op_flag in varchar2,
1604 p_date in date)
1605 return clob
1606 is
1607 qryctx DBMS_XMLGEN.ctxHandle;
1608 v_xml clob;
1609 v_doc DBMS_XMLDOM.domdocument;
1610 v_root DBMS_XMLDOM.domelement;
1611 v_attr DBMS_XMLDOM.domattr;
1612 v_attstr VARCHAR2 (100);
1613 v_val VARCHAR2 (200);
1614
1615
1616 begin
1617
1618 qryctx := DBMS_XMLGEN.newContext('SELECT :3 as "OPERATION_FLAG",
1619 pas.business_group_id as "BUSINESS_GROUP_ID",
1620 pas.person_id as "PERSON_ID",
1621 pas.assignment_id as "ASSIGNMENT_ID",
1622 pas.assignment_number as "ASSIGNMENT_NUMBER",
1623 to_char(pas.effective_start_date,''YYYY-MM-DD'') as "EFFECTIVE_START_DATE",
1624 to_char(pas.effective_end_date,''YYYY-MM-DD'') as "EFFECTIVE_END_DARE",
1625 pas.organization_id as "ORGANIZATION_ID",
1626 pas.probation_period as "PROBATION_PERIOD",
1627 pas.probation_unit as "PROBATION_UNITS",
1628 pas.job_id as "JOB_ID",
1629 pas.assignment_status_type_id as "ASSIGNMENT_STATUS_TYPE_ID",
1630 pas.location_id as "LOCATION_ID",
1631 pas.employment_category as "EMPLOYMENT_CATEGORY",
1632 pas.normal_hours as "NORMAL_HOURS",
1633 pas.frequency as "FREQUENCY",
1634 pas.grade_id as "GRADE_ID",
1635 pas.position_id as "POSITION_ID",
1636 pas.supervisor_id as "SUPERVISOR_ID",
1637 case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date) then
1638 to_char(pos.final_process_date,''YYYY-MM-DD'')
1639 when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.final_process_date,''YYYY-MM-DD'') end as "FINAL_PROCFESS_DATE",
1640
1641 case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date)
1642 then to_char(pos.ACTUAL_TERMINATION_DATE,''YYYY-MM-DD'')
1643 when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.ACTUAL_TERMINATION_DATE,''YYYY-MM-DD'') end as "ACTUAL_TERMINATION_DATE",
1644
1645 primary_flag as "PRIMARY_FLAG"
1646
1647 FROM per_all_assignments_f pas,
1648 per_periods_of_service pos,
1649 per_periods_of_placement pop
1650 WHERE pas.person_id = pop.person_id (+)
1651 AND pas.person_id = pos.person_id (+)
1652 AND pas.assignment_id = :1
1653 AND :2 between pas.effective_start_date and pas.effective_end_date');
1654
1655 DBMS_XMLGEN.setbindvalue (qryctx, '1', p_assignment_id);
1656 DBMS_XMLGEN.setbindvalue (qryctx, '2', p_date);
1657 DBMS_XMLGEN.setbindvalue (qryctx, '3', p_asg_op_flag);
1658
1659 dbms_xmlgen.setrowsettag(qryctx,'WORKFORCE');
1660 dbms_xmlgen.setrowtag(qryctx,'WORKFORCE_DETAILS');
1661
1662 dbms_xmlgen.setNullHandling(qryctx, 1);
1663
1664
1665
1666
1667 v_xml := replace
1668 ( DBMS_XMLGEN.getXML(qryctx)
1669 , '<WORKFORCE xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">'
1670 , '<WORKFORCE xmlns="urn:oracle.enterprise.crm.data"
1671 xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">'
1672 );
1673
1674
1675
1676
1677
1678 return (v_xml);
1679
1680 end sif_workforce_data;
1681
1682 /*Function to generate the organization xml data*/
1683 FUNCTION sif_organization_data(p_organization_id in number,
1684 p_org_op_flag in varchar2,
1685 p_date_to in boolean)
1686 return clob
1687 is
1688 qryctx DBMS_XMLGEN.ctxHandle;
1689 v_xml clob;
1690 v_doc DBMS_XMLDOM.domdocument;
1691 v_root DBMS_XMLDOM.domelement;
1692 v_attr DBMS_XMLDOM.domattr;
1693 v_attstr VARCHAR2 (100);
1694 v_val VARCHAR2 (200);
1695 begin
1696
1697
1698 if p_org_op_flag = 'D'
1699 then
1700
1701 qryctx := DBMS_XMLGEN.newContext(
1702 'SELECT ''U'' as "OPERATION_FLAG",
1703 null as "BUSINESS_GROUP_ID",
1704 :1 as "ORGANIZATION_ID",
1705 to_char(trunc(sysdate),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
1706 ''I'' as "EFFECTIVE_STATUS" ,
1707 cursor(select null lang_code ,null data from dual) as "ORGANIZATIONNAME",
1708 null as "LOCATION_ID",
1709 null as "MANAGER_ID"
1710 from dual
1711 ');
1712
1713 DBMS_XMLGEN.setbindvalue (qryctx, '1', p_ORGANIZATION_id);
1714
1715 else
1716
1717 if (p_date_to)
1718 then
1719
1720 qryctx := DBMS_XMLGEN.newContext(
1721 'SELECT :2 as "OPERATION_FLAG",
1722 ORG.BUSINESS_GROUP_ID as "BUSINESS_GROUP_ID",
1723 ORG.ORGANIZATION_ID as "ORGANIZATION_ID",
1724 to_char(nvl(date_to,trunc(sysdate)),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
1725 ''I'' as "EFFECTIVE_STATUS",
1726 cursor(select language lang_code,name data from hr_all_organization_units_tl TL where tl.organization_id = org.organization_id) as "ORGANIZATIONNAME",
1727 ORG.LOCATION_ID as "LOCATION_ID",
1728 (select employee_number from per_all_people_f ppf,hr_organization_information hrorg1
1729 where ppf.person_id = hrorg1.ORG_INFORMATION2
1730 and ppf.business_group_id = org.business_group_id
1731 and hrorg1.org_information_context = ''Organization Name Alias''
1732 and hrorg1.organization_id = org.organization_id
1733 and nvl(org.date_to,to_date(''31/12/4712'',''DD/MM/YYYY'')) between fnd_date.canonical_to_date(hrorg1.org_information3)
1734 and nvl(fnd_date.canonical_to_date(hrorg1.org_information4),to_date(''31/12/4712'',''DD/MM/YYYY''))
1735 and fnd_date.canonical_to_date(hrorg1.org_information3) between ppf.effective_start_date and ppf.effective_end_date) as "MANAGER_ID"
1736
1737 from hr_all_organization_units org,
1738 hr_organization_information hrorg
1739 where hrorg.organization_id = org.organization_id
1740 and hrorg.org_information1 = ''HR_ORG''
1741 and org.last_update_date = (select max(last_update_date) from hr_all_organization_units where organization_id = :1)');
1742 else
1743
1744 qryctx := DBMS_XMLGEN.newContext(
1745 'SELECT :2 as "OPERATION_FLAG",
1746 ORG.BUSINESS_GROUP_ID as "BUSINESS_GROUP_ID",
1747 ORG.ORGANIZATION_ID as "ORGANIZATION_ID",
1748 to_char(DATE_FROM,''YYYY-MM-DD'') as "EFFECTIVE_DATE" ,
1749 ''A'' as "EFFECTIVE_STATUS" ,
1750 cursor(select language lang_code,name data from hr_all_organization_units_tl TL where tl.organization_id = org.organization_id) as "ORGANIZATIONNAME",
1751 ORG.LOCATION_ID as "LOCATION_ID",
1752 (select employee_number from per_all_people_f ppf,hr_organization_information hrorg1
1753 where ppf.person_id = hrorg1.ORG_INFORMATION2
1754 and ppf.business_group_id = org.business_group_id
1755 and hrorg1.org_information_context = ''Organization Name Alias''
1756 and hrorg1.organization_id = org.organization_id
1757 and nvl(org.date_to,to_date(''31/12/4712'',''DD/MM/YYYY'')) between fnd_date.canonical_to_date(hrorg1.org_information3)
1758 and nvl(fnd_date.canonical_to_date(hrorg1.org_information4),to_date(''31/12/4712'',''DD/MM/YYYY''))
1759 and fnd_date.canonical_to_date(hrorg1.org_information3) between ppf.effective_start_date and ppf.effective_end_date) as "MANAGER_ID"
1760
1761 from hr_all_organization_units org,
1762 hr_organization_information hrorg
1763 where hrorg.organization_id = org.organization_id
1764 and hrorg.org_information1 = ''HR_ORG''
1765 and org.last_update_date = (select max(last_update_date) from hr_all_organization_units where organization_id = :1)');
1766
1767 end if;
1768
1769 DBMS_XMLGEN.setbindvalue (qryctx, '1', p_ORGANIZATION_id);
1770 DBMS_XMLGEN.setbindvalue (qryctx, '2', p_org_op_flag);
1771
1772 end if;
1773
1774 dbms_xmlgen.setrowsettag(qryctx,'ORGANIZATION');
1775 dbms_xmlgen.setrowtag(qryctx,'ORGANIZATION_DETAILS');
1776
1777 dbms_xmlgen.setNullHandling(qryctx, 1);
1778
1779 v_xml := replace
1780 ( DBMS_XMLGEN.getXML(qryctx)
1781 , 'ORGANIZATIONNAME_ROW>'
1782 , 'LANG>'
1783 );
1784
1785
1786
1787 return ( replace
1788 ( v_xml
1789 , '<ORGANIZATION xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">'
1790 , '<ORGANIZATION xmlns="urn:oracle.enterprise.crm.data"
1791 xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">'
1792 )
1793 );
1794
1795
1796 end sif_organization_data;
1797
1798 /*Function to generate the person xml data*/
1799 FUNCTION sif_person_data(p_person_id in number,
1800 p_address_id in number,
1801 p_phone_id in number,
1802 p_person_op_flag in varchar2,
1803 p_date in date)
1804 return clob
1805 is
1806 qryctx DBMS_XMLGEN.ctxHandle;
1807 v_xml clob;
1808 v_doc DBMS_XMLDOM.domdocument;
1809 v_root DBMS_XMLDOM.domelement;
1810 v_attr DBMS_XMLDOM.domattr;
1811 v_attstr VARCHAR2 (100);
1812 v_val VARCHAR2 (200);
1813
1814
1815 begin
1816
1817 if ((p_address_id is null) and (p_phone_id is null))
1818 then
1819 qryctx := DBMS_XMLGEN.newContext('SELECT :3 as "OPERATION_FLAG",
1820 ppf.business_group_id as "BUSINESS_GROUP_ID",
1821 ppf.person_id as "PERSON_ID",
1822 (select org_information9 from
1823 hr_organization_information where organization_id = ppf.business_group_id
1824 and org_information_context = ''Business Group Information'') as "LEGISLATION_CODE",
1825 EMPLOYEE_NUMBER as "EMPLOYEE_NUMBER",
1826 APPLICANT_NUMBER as "APPLICANT_NUMBER",
1827 NPW_NUMBER as "CWK_NUMBER",
1828 PERSON_TYPE_ID as "PERSON_TYPE_ID",
1829 to_char(DATE_OF_BIRTH,''YYYY-MM-DD'') as "DATE_OF_BIRTH",
1830 TOWN_OF_BIRTH as "TOWN_OF_BIRTH",
1831 COUNTRY_OF_BIRTH as "COUNTRY_OF_BIRTH",
1832 to_char(DATE_OF_DEATH,''YYYY-MM-DD'') as "DATE_OF_DEATH",
1833 to_char(ORIGINAL_DATE_OF_HIRE,''YYYY-MM-DD'') as "ORIGINAL_DATE_OF_HIRE",
1834 to_char(EFFECTIVE_START_DATE,''YYYY-MM-DD'') as "EFFECTIVE_START_DATE",
1835 to_char(EFFECTIVE_END_DATE,''YYYY-MM-DD'') as "EFFECTIVE_END_DATE",
1836 SEX as "SEX",
1837 FULL_NAME as "FULL_NAME",
1838 SUFFIX as "SUFFIX",
1839 TITLE as "TITLE",
1840 LAST_NAME as "LAST_NAME",
1841 FIRST_NAME as "FIRST_NAME",
1842 MIDDLE_NAMES as "MIDDLE_NAMES",
1843 NATIONALITY as "NATIONALITY",
1844 NATIONAL_IDENTIFIER as "NATIONAL_IDENTIFIER",
1845 EMAIL_ADDRESS as "EMAIL_ADDRESS",
1846 null as "ADDRESS_TYPE",
1847 null as "DATE_FROM",
1848 null as "DATE_TO",
1849 null as "ADDRESS_STYLE",
1850 null as "COUNTRY",
1851 null as "ADDRESS_LINE1",
1852 null as "ADDRESS_LINE2",
1853 null as "ADDRESS_LINE3",
1854 null as "TOWN_OR_CITY",
1855 null as "TELEPHONE_NUMBER_1",
1856 null as "REGION_1",
1857 null as "REGION_2",
1858 null as "POSTAL_CODE",
1859 null as "PRIMARY_FLAG",
1860 null as "PHONE_DATE_FROM",
1861 null as "PHONE_DATE_TO",
1862 null as "PHONE_TYPE",
1863 null as "PHONE_NUMBER",
1864 (select message_text from fnd_new_messages where message_name = ''HR_NATIONAL_ID_NUMBER_''||
1865 (select to_char(org_information9) from
1866 hr_organization_information where organization_id = ppf.business_group_id
1867 and org_information_context = ''Business Group Information'')
1868 and language_code = USERENV(''LANG'') ) as "NATIONAL_IDENTIFIER_LABEL",
1869 hr_hrhd_initial_load.hr_hrhd_encrypt(:1) as "ENCRYPTED_PERSON_ID",
1870 hr_hrhd_initial_load.hr_hrhd_encrypt(ppf.business_group_id) as "ENCRYPTED_BUS_GRP_ID"
1871
1872 FROM PER_ALL_PEOPLE_F ppf
1873 where ppf.person_id = :1
1874 AND :2 between ppf.effective_start_date and nvl(ppf.effective_end_date,to_date(''31/12/4712'',''DD/MM/YYYY''))');
1875
1876 DBMS_XMLGEN.setbindvalue (qryctx, '1', p_person_id);
1877 DBMS_XMLGEN.setbindvalue (qryctx, '2', p_date);
1878 DBMS_XMLGEN.setbindvalue (qryctx, '3', p_person_op_flag);
1879
1880 end if;
1881 if (p_address_id is not null) then
1882 qryctx := DBMS_XMLGEN.newContext('SELECT :3 as "OPERATION_FLAG",
1883 ppf.business_group_id as "BUSINESS_GROUP_ID",
1884 ppf.person_id as "PERSON_ID",
1885 (select org_information9 from
1886 hr_organization_information where organization_id = ppf.business_group_id
1887 and org_information_context = ''Business Group Information'') as "LEGISLATION_CODE",
1888 EMPLOYEE_NUMBER as "EMPLOYEE_NUMBER",
1889 APPLICANT_NUMBER as "APPLICANT_NUMBER",
1890 NPW_NUMBER as "CWK_NUMBER",
1891 PERSON_TYPE_ID as "PERSON_TYPE_ID",
1892 to_char(DATE_OF_BIRTH,''YYYY-MM-DD'') as "DATE_OF_BIRTH",
1893 TOWN_OF_BIRTH as "TOWN_OF_BIRTH",
1894 COUNTRY_OF_BIRTH as "COUNTRY_OF_BIRTH",
1895 to_char(DATE_OF_DEATH,''YYYY-MM-DD'') as "DATE_OF_DEATH",
1896 to_char(ORIGINAL_DATE_OF_HIRE,''YYYY-MM-DD'') as "ORIGINAL_DATE_OF_HIRE",
1897 to_char(EFFECTIVE_START_DATE,''YYYY-MM-DD'') as "EFFECTIVE_START_DATE",
1898 to_char(EFFECTIVE_END_DATE,''YYYY-MM-DD'') as "EFFECTIVE_END_DATE",
1899 SEX as "SEX",
1900 FULL_NAME as "FULL_NAME",
1901 SUFFIX as "SUFFIX",
1902 TITLE as "TITLE",
1903 LAST_NAME as "LAST_NAME",
1904 FIRST_NAME as "FIRST_NAME",
1905 MIDDLE_NAMES as "MIDDLE_NAMES",
1906 NATIONALITY as "NATIONALITY",
1907 NATIONAL_IDENTIFIER as "NATIONAL_IDENTIFIER",
1908 EMAIL_ADDRESS as "EMAIL_ADDRESS",
1909 Address_Type as "ADDRESS_TYPE",
1910 To_Char(paddr.Date_From,''YYYY-MM-DD'') as "DATE_FROM",
1911 To_Char(paddr.Date_To,''YYYY-MM-DD'') as "DATE_TO",
1912 Style as "ADDRESS_STYLE",
1913 Country as "COUNTRY",
1914 Address_Line1 as "ADDRESS_LINE1",
1915 Address_Line2 as "ADDRESS_LINE2",
1916 Address_Line3 as "ADDRESS_LINE3",
1917 Town_Or_City as "TOWN_OR_CITY",
1918 Telephone_Number_1 as "TELEPHONE_NUMBER_1",
1919 Region_1 as "REGION_1",
1920 Region_2 as "REGION_2",
1921 Postal_Code as "POSTAL_CODE",
1922 Primary_flag as "PRIMARY_FLAG",
1923 null as "PHONE_DATE_FROM",
1924 null as "PHONE_DATE_TO",
1925 null as "PHONE_TYPE",
1926 null as "PHONE_NUMBER",
1927 (select message_text from fnd_new_messages where message_name = ''HR_NATIONAL_ID_NUMBER_''||
1928 (select to_char(org_information9) from
1929 hr_organization_information where organization_id = ppf.business_group_id
1930 and org_information_context = ''Business Group Information'')
1931 and language_code = USERENV(''LANG'') ) as "NATIONAL_IDENTIFIER_LABEL",
1932 hr_hrhd_initial_load.hr_hrhd_encrypt(:1) as "ENCRYPTED_PERSON_ID",
1933 hr_hrhd_initial_load.hr_hrhd_encrypt(ppf.business_group_id) as "ENCRYPTED_BUS_GRP_ID"
1934
1935 FROM PER_ALL_PEOPLE_F ppf,per_addresses paddr
1936 where ppf.person_id = :1
1937 and ppf.person_id = paddr.person_id
1938 and paddr.address_id = :4
1939 AND :2 between nvl(paddr.date_from,:2) and nvl(paddr.date_to,to_date(''31/12/4712'',''DD/MM/YYYY''))
1940 AND :2 between ppf.effective_start_date and nvl(ppf.effective_end_date,to_date(''31/12/4712'',''DD/MM/YYYY''))');
1941
1942 DBMS_XMLGEN.setbindvalue (qryctx, '1', p_person_id);
1943 DBMS_XMLGEN.setbindvalue (qryctx, '2', p_date);
1944 DBMS_XMLGEN.setbindvalue (qryctx, '3', p_person_op_flag);
1945
1946 DBMS_XMLGEN.setbindvalue (qryctx, '4', p_address_id);
1947 end if;
1948
1949 if (p_phone_id is not null) then
1950 qryctx := DBMS_XMLGEN.newContext('SELECT :3 as "OPERATION_FLAG",
1951 ppf.business_group_id as "BUSINESS_GROUP_ID",
1952 ppf.person_id as "PERSON_ID",
1953 (select org_information9 from
1954 hr_organization_information where organization_id = ppf.business_group_id
1955 and org_information_context = ''Business Group Information'') as "LEGISLATION_CODE",
1956 EMPLOYEE_NUMBER as "EMPLOYEE_NUMBER",
1957 APPLICANT_NUMBER as "APPLICANT_NUMBER",
1958 NPW_NUMBER as "CWK_NUMBER",
1959 PERSON_TYPE_ID as "PERSON_TYPE_ID",
1960 to_char(DATE_OF_BIRTH,''YYYY-MM-DD'') as "DATE_OF_BIRTH",
1961 TOWN_OF_BIRTH as "TOWN_OF_BIRTH",
1962 COUNTRY_OF_BIRTH as "COUNTRY_OF_BIRTH",
1963 to_char(DATE_OF_DEATH,''YYYY-MM-DD'') as "DATE_OF_DEATH",
1964 to_char(ORIGINAL_DATE_OF_HIRE,''YYYY-MM-DD'') as "ORIGINAL_DATE_OF_HIRE",
1965 to_char(EFFECTIVE_START_DATE,''YYYY-MM-DD'') as "EFFECTIVE_START_DATE",
1966 to_char(EFFECTIVE_END_DATE,''YYYY-MM-DD'') as "EFFECTIVE_END_DATE",
1967 SEX as "SEX",
1968 FULL_NAME as "FULL_NAME",
1969 SUFFIX as "SUFFIX",
1970 TITLE as "TITLE",
1971 LAST_NAME as "LAST_NAME",
1972 FIRST_NAME as "FIRST_NAME",
1973 MIDDLE_NAMES as "MIDDLE_NAMES",
1974 NATIONALITY as "NATIONALITY",
1975 NATIONAL_IDENTIFIER as "NATIONAL_IDENTIFIER",
1976 EMAIL_ADDRESS as "EMAIL_ADDRESS",
1977 null as "ADDRESS_TYPE",
1978 null as "DATE_FROM",
1979 null as "DATE_TO",
1980 null as "ADDRESS_STYLE",
1981 null as "COUNTRY",
1982 null as "ADDRESS_LINE1",
1983 null as "ADDRESS_LINE2",
1984 null as "ADDRESS_LINE3",
1985 null as "TOWN_OR_CITY",
1986 null as "TELEPHONE_NUMBER_1",
1987 null as "REGION_1",
1988 null as "REGION_2",
1989 null as "POSTAL_CODE",
1990 null as "PRIMARY_FLAG",
1991 to_char(ppn.date_from,''YYYY-MM-DD'') as "PHONE_DATE_FROM",
1992 to_char(ppn.date_to,''YYYY-MM-DD'') as "PHONE_DATE_TO",
1993 PHONE_TYPE as "PHONE_TYPE",
1994 PHONE_NUMBER as "PHONE_NUMBER",
1995 (select message_text from fnd_new_messages where message_name = ''HR_NATIONAL_ID_NUMBER_''||
1996 (select to_char(org_information9) from
1997 hr_organization_information where organization_id = ppf.business_group_id
1998 and org_information_context = ''Business Group Information'')
1999 and language_code = USERENV(''LANG'') ) as "NATIONAL_IDENTIFIER_LABEL",
2000 hr_hrhd_initial_load.hr_hrhd_encrypt(:1) as "ENCRYPTED_PERSON_ID",
2001 hr_hrhd_initial_load.hr_hrhd_encrypt(ppf.business_group_id) as "ENCRYPTED_BUS_GRP_ID"
2002
2003 FROM PER_ALL_PEOPLE_F ppf,per_phones ppn
2004 where ppf.person_id = :1
2005 and ppf.person_id = ppn.PARENT_ID
2006 and ppn.phone_id = :5
2007 AND PPN.PARENT_TABLE = ''PER_ALL_PEOPLE_F''
2008 AND :2 between nvl(ppn.date_from,:2) and nvl(ppn.date_to,to_date(''31/12/4712'',''DD/MM/YYYY''))
2009 AND :2 between ppf.effective_start_date and nvl(ppf.effective_end_date,to_date(''31/12/4712'',''DD/MM/YYYY''))');
2010
2011 DBMS_XMLGEN.setbindvalue (qryctx, '1', p_person_id);
2012 DBMS_XMLGEN.setbindvalue (qryctx, '2', p_date);
2013 DBMS_XMLGEN.setbindvalue (qryctx, '3', p_person_op_flag);
2014 DBMS_XMLGEN.setbindvalue (qryctx, '5', p_phone_id);
2015 end if;
2016
2017
2018 dbms_xmlgen.setrowsettag(qryctx,'PERSON');
2019 dbms_xmlgen.setrowtag(qryctx,'PERSON_DETAILS');
2020
2021 dbms_xmlgen.setNullHandling(qryctx, 1);
2022
2023
2024
2025 v_xml := replace
2026 ( DBMS_XMLGEN.getXML(qryctx)
2027 , '<PERSON xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">'
2028 , '<PERSON xmlns="urn:oracle.enterprise.crm.data"
2029 xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">'
2030 );
2031
2032
2033
2034
2035 return (v_xml);
2036
2037 end sif_person_data;
2038
2039
2040 /*Function to generate the location data*/
2041 FUNCTION sif_location_data(p_location_id in number,
2042 p_loc_op_flag in varchar2,
2043 p_inactive_date in boolean)
2044 return clob
2045 is
2046 qryctx DBMS_XMLGEN.ctxHandle;
2047 v_xml clob;
2048 v_doc DBMS_XMLDOM.domdocument;
2049 v_root DBMS_XMLDOM.domelement;
2050 v_attr DBMS_XMLDOM.domattr;
2051 v_attstr VARCHAR2 (100);
2052 v_val VARCHAR2 (200);
2053 begin
2054
2055
2056 if p_loc_op_flag = 'D'
2057 then
2058
2059 qryctx := DBMS_XMLGEN.newContext(
2060 'SELECT ''U'' as "OPERATION_FLAG",
2061 ''*'' as "BUSINESS_GROUP_ID",
2062
2063 to_char(trunc(sysdate),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
2064 ''I'' as "EFFECTIVE_STATUS" ,
2065 :1 as "LOCATION_ID",
2066 cursor(select ''*'' lang_code ,''*'' data from dual) as "LOCATIONNAME",
2067 ''*'' as "STYLE",
2068 ''*'' as "COUNTRY",
2069 ''*'' as "ADDRESS_LINE_1",
2070 ''*'' as "ADDRESS_LINE_2",
2071 ''*'' as "ADDRESS_LINE_3",
2072 ''*'' as "TOWN_OR_CITY",
2073 ''*'' as "REGION_1",
2074 ''*'' as "REGION_2",
2075 ''*'' as "REGION_3",
2076 ''*'' as "POSTAL_CODE",
2077 ''*'' as "TELEPHONE_NUMBER_1",
2078 ''*'' as "TELEPHONE_NUMBER_2",
2079 ''*'' as "TELEPHONE_NUMBER_3",
2080 ''*'' as "LOC_INFORMATION13",
2081 ''*'' as "LOC_INFORMATION14",
2082 ''*'' as "LOC_INFORMATION15",
2083 ''*'' as "LOC_INFORMATION16",
2084 ''*'' as "LOC_INFORMATION17",
2085 ''*'' as "LOC_INFORMATION18",
2086 ''*'' as "LOC_INFORMATION19",
2087 ''*'' as "LOC_INFORMATION20"
2088 from dual
2089 ');
2090
2091 DBMS_XMLGEN.setbindvalue (qryctx, '1', p_location_id);
2092
2093 else
2094
2095 if (p_inactive_date)
2096 then
2097
2098 qryctx := DBMS_XMLGEN.newContext(
2099 'SELECT :2 as "OPERATION_FLAG",
2100 hloc.BUSINESS_GROUP_ID as "BUSINESS_GROUP_ID",
2101
2102 to_char(nvl(inactive_date,trunc(sysdate)),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
2103 ''I'' as "EFFECTIVE_STATUS",
2104 hloc.LOCATION_ID as "LOCATION_ID",
2105 cursor(select language lang_code,location_code data from hr_locations_all_tl hltl where hltl.location_id= hloc.location_id) as "LOCATIONNAME",
2106 STYLE as "STYLE",
2107 COUNTRY as "COUNTRY",
2108 ADDRESS_LINE_1 as "ADDRESS_LINE_1",
2109 ADDRESS_LINE_2 as "ADDRESS_LINE_2",
2110 ADDRESS_LINE_3 as "ADDRESS_LINE_3",
2111 TOWN_OR_CITY as "TOWN_OR_CITY",
2112 REGION_1 as "REGION_1",
2113 REGION_2 as "REGION_2",
2114 REGION_3 as "REGION_3",
2115 POSTAL_CODE as "POSTAL_CODE",
2116 TELEPHONE_NUMBER_1 as "TELEPHONE_NUMBER_1",
2117 TELEPHONE_NUMBER_2 as "TELEPHONE_NUMBER_2",
2118 TELEPHONE_NUMBER_3 as "TELEPHONE_NUMBER_3",
2119 LOC_INFORMATION13 as "LOC_INFORMATION13",
2120 LOC_INFORMATION14 as "LOC_INFORMATION14",
2121 LOC_INFORMATION15 as "LOC_INFORMATION15",
2122 LOC_INFORMATION16 as "LOC_INFORMATION16",
2123 LOC_INFORMATION17 as "LOC_INFORMATION17",
2124 LOC_INFORMATION18 as "LOC_INFORMATION18",
2125 LOC_INFORMATION19 as "LOC_INFORMATION19",
2126 LOC_INFORMATION20 as "LOC_INFORMATION20"
2127 from hr_locations_all hloc
2128 where location_id = :1
2129 and last_update_date = (select max(last_update_date) from hr_locations_all where location_id = :1)');
2130 else
2131
2132 qryctx := DBMS_XMLGEN.newContext(
2133 'SELECT :2 as "OPERATION_FLAG",
2134 hloc.BUSINESS_GROUP_ID as "BUSINESS_GROUP_ID",
2135 hloc.LOCATION_ID as "LOCATION_ID",
2136 to_char(hloc.creation_date,''YYYY-MM-DD'') as "EFFECTIVE_DATE",
2137 ''A'' as "EFFECTIVE_STATUS" ,
2138 cursor(select language lang_code,location_code data from hr_locations_all_tl hltl where hltl.location_id= hloc.location_id) as "LOCATIONNAME",
2139 STYLE as "STYLE",
2140 COUNTRY as "COUNTRY",
2141 ADDRESS_LINE_1 as "ADDRESS_LINE_1",
2142 ADDRESS_LINE_2 as "ADDRESS_LINE_2",
2143 ADDRESS_LINE_3 as "ADDRESS_LINE_3",
2144 TOWN_OR_CITY as "TOWN_OR_CITY",
2145 REGION_1 as "REGION_1",
2146 REGION_2 as "REGION_2",
2147 REGION_3 as "REGION_3",
2148 POSTAL_CODE as "POSTAL_CODE",
2149 TELEPHONE_NUMBER_1 as "TELEPHONE_NUMBER_1",
2150 TELEPHONE_NUMBER_2 as "TELEPHONE_NUMBER_2",
2151 TELEPHONE_NUMBER_3 as "TELEPHONE_NUMBER_3",
2152 LOC_INFORMATION13 as "LOC_INFORMATION13",
2153 LOC_INFORMATION14 as "LOC_INFORMATION14",
2154 LOC_INFORMATION15 as "LOC_INFORMATION15",
2155 LOC_INFORMATION16 as "LOC_INFORMATION16",
2156 LOC_INFORMATION17 as "LOC_INFORMATION17",
2157 LOC_INFORMATION18 as "LOC_INFORMATION18",
2158 LOC_INFORMATION19 as "LOC_INFORMATION19",
2159 LOC_INFORMATION20 as "LOC_INFORMATION20"
2160 from hr_locations_all hloc
2161 where location_id = :1
2162 and last_update_date = (select max(last_update_date) from hr_locations_all where location_id = :1)');
2163
2164 end if;
2165
2166 DBMS_XMLGEN.setbindvalue (qryctx, '1', p_location_id);
2167 DBMS_XMLGEN.setbindvalue (qryctx, '2', p_loc_op_flag);
2168
2169 end if;
2170
2171 dbms_xmlgen.setrowsettag(qryctx,'LOCATION');
2172 dbms_xmlgen.setrowtag(qryctx,'LOCATION_DETAILS');
2173 dbms_xmlgen.setNullHandling(qryctx, 1);
2174
2175 v_xml := replace
2176 ( DBMS_XMLGEN.getXML(qryctx)
2177 , 'LOCATIONNAME_ROW>'
2178 , 'LANG>'
2179 );
2180
2181
2182
2183 return ( replace
2184 ( v_xml
2185 , '<LOCATION xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">'
2186 , '<LOCATION xmlns="urn:oracle.enterprise.crm.data"
2187 xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">'
2188 )
2189 );
2190
2191 end sif_location_data;
2192
2193 /*Function to generate the job data*/
2194 FUNCTION sif_job_data(p_job_id in number,
2195 p_job_op_flag in varchar2,
2196 p_date_to in boolean)
2197 return clob
2198 is
2199 qryctx DBMS_XMLGEN.ctxHandle;
2200 v_xml clob;
2201 v_doc DBMS_XMLDOM.domdocument;
2202 v_root DBMS_XMLDOM.domelement;
2203 v_attr DBMS_XMLDOM.domattr;
2204 v_attstr VARCHAR2 (100);
2205 v_val VARCHAR2 (200);
2206
2207
2208 begin
2209
2210 if p_job_op_flag = 'D'
2211 then
2212 qryctx := DBMS_XMLGEN.newContext(
2213 'SELECT ''U'' as "OPERATION_FLAG",
2214 ''*'' as "BUSINESS_GROUP_ID",
2215 :1 as "JOB_ID",
2216 to_char(trunc(sysdate),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
2217 ''I'' as "EFFECTIVE_STATUS",
2218 cursor(select ''*'' lang_code ,''*'' data from dual) as "JOBNAME"
2219 from dual
2220 ');
2221
2222 DBMS_XMLGEN.setbindvalue (qryctx, '1', p_job_id);
2223 else
2224
2225 if (p_date_to)
2226 then
2227
2228 qryctx := DBMS_XMLGEN.newContext(
2229 'SELECT :2 as "OPERATION_FLAG",
2230 pj.Business_group_id as "BUSINESS_GROUP_ID",
2231 pj.job_id as "JOB_ID",
2232 to_char(nvl(pj.date_to,trunc(sysdate)),''YYYY-MM-DD'') as "EFFECTIVE_DATE",
2233 ''I'' as "EFFECTIVE_STATUS",
2234 cursor(select language lang_code ,name data from per_jobs_tl pjtl where pjtl.job_id=pj.job_id) as "JOBNAME"
2235 from per_jobs pj
2236 where job_id = :1
2237 and last_update_date = (select max(last_update_date) from per_jobs where job_id = :1)
2238 ');
2239
2240 else
2241
2242 qryctx := DBMS_XMLGEN.newContext(
2243 'SELECT :2 as "OPERATION_FLAG",
2244 pj.Business_group_id as "BUSINESS_GROUP_ID",
2245 pj.JOB_ID as "JOB_ID",
2246 to_char(pj.date_from,''YYYY-MM-DD'') as "EFFECTIVE_DATE",
2247 ''A'' as "EFFECTIVE_STATUS",
2248 cursor(select language lang_code ,name data from per_jobs_tl pjtl where pjtl.job_id=pj.job_id) as "JOBNAME"
2249 from per_jobs pj
2250 where job_id = :1
2251 and last_update_date = (select max(last_update_date) from per_jobs where job_id = :1)
2252 ');
2253
2254 end if;
2255
2256 DBMS_XMLGEN.setbindvalue (qryctx, '1', p_job_id);
2257 DBMS_XMLGEN.setbindvalue (qryctx, '2', p_job_op_flag);
2258
2259 end if;
2260
2261
2262 dbms_xmlgen.setrowsettag(qryctx,'JOB');
2263 dbms_xmlgen.setrowtag(qryctx,'JOB_DETAILS');
2264
2265 dbms_xmlgen.setNullHandling(qryctx, 1);
2266
2267
2268
2269 v_xml := replace
2270 ( DBMS_XMLGEN.getXML(qryctx)
2271 ,'JOBNAME_ROW>'
2272 ,'LANG>');
2273
2274 return ( replace
2275 ( v_xml
2276 , '<JOB xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">'
2277 , '<JOB xmlns="urn:oracle.enterprise.crm.data"
2278 xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance">'
2279 )
2280 );
2281
2282 end sif_job_data;
2283
2284 /*Functions to generate the xml data ends*/
2285
2286 /* Call backable Procedures definition starts */
2287
2288 procedure workforce_callbackable(my_parms in wf_parameter_list_t)
2289 is
2290
2291
2292 p_assignment_data clob;
2293 p_asg_op_flag varchar2(5);
2294 p_unique_key number;
2295 p_date date;
2296 p_event_data clob;
2297
2298 -- data required for message
2299 p_person_id per_all_assignments_f.person_id%type;
2300 p_assignment_id per_all_assignments_f.assignment_id%type;
2301 p_assignment_number per_all_assignments_f.assignment_number%type;
2302 p_eff_start_date per_all_assignments_f.effective_start_date%type;
2303 p_eff_end_Date per_all_assignments_f.effective_end_date%type;
2304 p_original_date_of_hire per_all_people_f.original_date_of_hire%type;
2305 p_probation_period per_all_assignments_f.probation_period%type;
2306 p_probation_units per_all_assignments_f.probation_unit%type;
2307 p_organization_id per_all_assignments_f.organization_id%type;
2308 p_job_id per_all_assignments_f.job_id%type;
2309 p_assignment_status_type_id per_all_assignments_f.assignment_status_type_id%type;
2310 p_location_id per_all_assignments_f.location_id%type;
2311 p_employment_category per_all_assignments_f.employment_category%type;
2312 p_business_group_id per_all_assignments_f.business_group_id%type;
2313 p_normal_hours per_all_assignments_f.normal_hours%type;
2314 p_frequency per_all_assignments_f.frequency%type;
2315 p_grade_id per_all_assignments_f.grade_id%type;
2316 p_supervisor_id per_all_assignments_f.supervisor_id%type;
2317 p_final_process_date per_periods_of_service.final_process_date%type;
2318 p_accepted_termination_date per_periods_of_service.actual_termination_date%type;
2319 p_primary_flag per_all_assignments_f.primary_flag%type;
2320
2321 -- data required for message from table
2322 p_person_id_t per_all_assignments_f.person_id%type;
2323 p_assignment_id_t per_all_assignments_f.assignment_id%type;
2324 p_assignment_number_t per_all_assignments_f.assignment_number%type;
2325 p_eff_start_date_t per_all_assignments_f.effective_start_date%type;
2326 p_eff_end_Date_t per_all_assignments_f.effective_end_date%type;
2327 p_original_date_of_hire_t per_all_people_f.original_date_of_hire%type;
2328 p_probation_period_t per_all_assignments_f.probation_period%type;
2329 p_probation_units_t per_all_assignments_f.probation_unit%type;
2330 p_organization_id_t per_all_assignments_f.organization_id%type;
2331 p_job_id_t per_all_assignments_f.job_id%type;
2332 p_assignment_status_type_id_t per_all_assignments_f.assignment_status_type_id%type;
2333 p_location_id_t per_all_assignments_f.location_id%type;
2334 p_employment_category_t per_all_assignments_f.employment_category%type;
2335 p_business_group_id_t per_all_assignments_f.business_group_id%type;
2336 p_normal_hours_t per_all_assignments_f.normal_hours%type;
2337 p_frequency_t per_all_assignments_f.frequency%type;
2338 p_grade_id_t per_all_assignments_f.grade_id%type;
2339 p_supervisor_id_t per_all_assignments_f.supervisor_id%type;
2340 p_final_process_date_t per_periods_of_service.final_process_date%type;
2341 p_accepted_termination_date_t per_periods_of_service.actual_termination_date%type;
2342 p_primary_flag_t per_all_assignments_f.primary_flag%type;
2343
2344 l_transaction_id Number;
2345 l_transaction_id_canonical Varchar2(30);
2346
2347 cursor csr_fet_assignment(p_ass_id number,p_effst_date date)
2348 is
2349 SELECT pas.person_id,
2350 pas.assignment_id,
2351 pas.assignment_number,
2352 pas.effective_start_date,
2353 pas.effective_end_date,
2354 ppf.original_date_of_hire,
2355 pas.probation_period,
2356 pas.probation_unit,
2357 pas.organization_id,
2358 pas.job_id,
2359 pas.assignment_status_type_id,
2360 pas.location_id,
2361 pas.employment_category,
2362 pas.business_group_id,
2363 pas.normal_hours,
2364 pas.frequency,
2365 pas.grade_id,
2366 pas.supervisor_id,
2367
2368 case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date) then
2369 to_char(pos.final_process_date,'YYYY-MM-DD')
2370 when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.final_process_date,'YYYY-MM-DD') end ,
2371
2372 case when (pas.person_id = pos.person_id and pas.effective_end_date = pos.actual_termination_date)
2373 then to_char(pos.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD')
2374 when (pas.person_id = pop.person_id and pas.effective_end_date = pop.actual_termination_date) then to_char(pop.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD') end ,
2375
2376 primary_flag
2377
2378 FROM per_all_people_f ppf,
2379 per_all_assignments_f pas,
2380 per_periods_of_service pos,
2381 per_periods_of_placement pop
2382 WHERE pas.assignment_id = p_ass_id
2383 AND pas.person_id = ppf.person_id
2384 AND pas.person_id = pop.person_id (+)
2385 AND pas.person_id = pos.person_id (+)
2386 AND ppf.BUSINESS_GROUP_ID = pas.BUSINESS_GROUP_ID
2387 AND p_effst_date between pas.effective_start_date and pas.effective_end_date
2388 AND pas.effective_start_date BETWEEN ppf.effective_start_date AND
2389 ppf.effective_end_date;
2390
2391 v_document dbms_xmldom.domdocument;
2392 v_nodes dbms_xmldom.DOMNodeList;
2393 v_element_x dbms_xmldom.DOMElement;
2394 v_node dbms_xmldom.DOMNode;
2395 v_node_2 dbms_xmldom.DOMNode;
2396 v_tag VARCHAR2(100);
2397 p_eff_date VARCHAR2(100);
2398
2399
2400 begin
2401
2402
2403 p_person_id := wf_event.getValueForParameter('person_id', my_parms);
2404 p_assignment_id := wf_event.getValueForParameter('assignment_id', my_parms);
2405 p_date := wf_event.getValueForParameter('eff_date', my_parms);
2406 l_transaction_id_canonical := wf_event.getValueForParameter('transaction_id', my_parms);
2407 l_transaction_id := FND_NUMBER.canonical_to_number(l_transaction_id_canonical);
2408 hr_hrhd_event_data_handler.hrhd_retrieve_event_data(l_transaction_id, p_event_data);
2409 --p_event_data := wf_event.getValueForParameter('event_data', my_parms);
2410 p_asg_op_flag := wf_event.getValueForParameter('asg_op_flag', my_parms);
2411
2412 v_document := dbms_xmldom.newdomdocument(p_event_data);
2413
2414 -- extract the effective_start_date from the xml event message
2415 v_document := dbms_xmldom.newdomdocument(p_event_data);
2416 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'effective_start_date');
2417 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2418 v_node := dbms_xmldom.item(v_nodes,0);
2419 v_tag := dbms_xmldom.getNodeName(v_node);
2420 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2421 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
2422 p_eff_start_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
2423
2424 -- extract the effective_start_date from the xml event message
2425 v_document := dbms_xmldom.newdomdocument(p_event_data);
2426 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'assignment_number');
2427 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2428 v_node := dbms_xmldom.item(v_nodes,0);
2429 v_tag := dbms_xmldom.getNodeName(v_node);
2430 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2431 p_assignment_number := dbms_xmldom.getnodevalue(v_node_2);
2432
2433
2434 -- extract the effective_end_date from the xml event message
2435 v_document := dbms_xmldom.newdomdocument(p_event_data);
2436 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'effective_end_date');
2437 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2438 v_node := dbms_xmldom.item(v_nodes,0);
2439 v_tag := dbms_xmldom.getNodeName(v_node);
2440 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2441 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
2442 p_eff_end_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
2443
2444 -- extract the probation_period from the xml event message
2445 v_document := dbms_xmldom.newdomdocument(p_event_data);
2446 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'probation_period');
2447 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2448 v_node := dbms_xmldom.item(v_nodes,0);
2449 v_tag := dbms_xmldom.getNodeName(v_node);
2450 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2451 p_probation_period := dbms_xmldom.getnodevalue(v_node_2);
2452
2453 -- extract the probation_units from the xml event message
2454 v_document := dbms_xmldom.newdomdocument(p_event_data);
2455 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'probation_units');
2456 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2457 v_node := dbms_xmldom.item(v_nodes,0);
2458 v_tag := dbms_xmldom.getNodeName(v_node);
2459 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2460 p_probation_units := dbms_xmldom.getnodevalue(v_node_2);
2461
2462 -- extract the organization_id from the xml event message
2463 v_document := dbms_xmldom.newdomdocument(p_event_data);
2464 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'organization_id');
2465 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2466 v_node := dbms_xmldom.item(v_nodes,0);
2467 v_tag := dbms_xmldom.getNodeName(v_node);
2468 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2469 p_organization_id := dbms_xmldom.getnodevalue(v_node_2);
2470
2471 -- extract the job_id from the xml event message
2472 v_document := dbms_xmldom.newdomdocument(p_event_data);
2473 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'job_id');
2474 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2475 v_node := dbms_xmldom.item(v_nodes,0);
2476 v_tag := dbms_xmldom.getNodeName(v_node);
2477 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2478 p_job_id := dbms_xmldom.getnodevalue(v_node_2);
2479
2480 -- extract the job_id from the xml event message
2481 v_document := dbms_xmldom.newdomdocument(p_event_data);
2482 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'assignment_status_type_id');
2483 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2484 v_node := dbms_xmldom.item(v_nodes,0);
2485 v_tag := dbms_xmldom.getNodeName(v_node);
2486 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2487 p_assignment_status_type_id := dbms_xmldom.getnodevalue(v_node_2);
2488
2489 -- extract the location_id from the xml event message
2490 v_document := dbms_xmldom.newdomdocument(p_event_data);
2491 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'location_id');
2492 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2493 v_node := dbms_xmldom.item(v_nodes,0);
2494 v_tag := dbms_xmldom.getNodeName(v_node);
2495 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2496 p_location_id := dbms_xmldom.getnodevalue(v_node_2);
2497
2498 -- extract the employment_category from the xml event message
2499 v_document := dbms_xmldom.newdomdocument(p_event_data);
2500 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'employment_category');
2501 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2502 v_node := dbms_xmldom.item(v_nodes,0);
2503 v_tag := dbms_xmldom.getNodeName(v_node);
2504 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2505 p_location_id := dbms_xmldom.getnodevalue(v_node_2);
2506
2507 -- extract the business_group_id from the xml event message
2508 v_document := dbms_xmldom.newdomdocument(p_event_data);
2509 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'business_group_id');
2510 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2511 v_node := dbms_xmldom.item(v_nodes,0);
2512 v_tag := dbms_xmldom.getNodeName(v_node);
2513 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2514 p_business_group_id := dbms_xmldom.getnodevalue(v_node_2);
2515
2516 -- extract the normal_hours from the xml event message
2517 v_document := dbms_xmldom.newdomdocument(p_event_data);
2518 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'normal_hours');
2519 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2520 v_node := dbms_xmldom.item(v_nodes,0);
2521 v_tag := dbms_xmldom.getNodeName(v_node);
2522 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2523 p_normal_hours := dbms_xmldom.getnodevalue(v_node_2);
2524
2525 -- extract the frequency from the xml event message
2526 v_document := dbms_xmldom.newdomdocument(p_event_data);
2527 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'frequency');
2528 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2529 v_node := dbms_xmldom.item(v_nodes,0);
2530 v_tag := dbms_xmldom.getNodeName(v_node);
2531 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2532 p_frequency := dbms_xmldom.getnodevalue(v_node_2);
2533
2534 -- extract the grade_id from the xml event message
2535 v_document := dbms_xmldom.newdomdocument(p_event_data);
2536 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'grade_id');
2537 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2538 v_node := dbms_xmldom.item(v_nodes,0);
2539 v_tag := dbms_xmldom.getNodeName(v_node);
2540 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2541 p_grade_id := dbms_xmldom.getnodevalue(v_node_2);
2542
2543 -- extract the supervisor_id from the xml event message
2544 v_document := dbms_xmldom.newdomdocument(p_event_data);
2545 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'supervisor_id');
2546 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2547 v_node := dbms_xmldom.item(v_nodes,0);
2548 v_tag := dbms_xmldom.getNodeName(v_node);
2549 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2550 p_supervisor_id := dbms_xmldom.getnodevalue(v_node_2);
2551
2552 -- extract the final_process_date from the xml event message
2553 v_document := dbms_xmldom.newdomdocument(p_event_data);
2554 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'final_process_date');
2555 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2556 v_node := dbms_xmldom.item(v_nodes,0);
2557 v_tag := dbms_xmldom.getNodeName(v_node);
2558 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2559 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
2560 p_final_process_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
2561
2562 -- extract the actual_termiantion_date from the xml event message
2563 v_document := dbms_xmldom.newdomdocument(p_event_data);
2564 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'actual_termiantion_date');
2565 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2566 v_node := dbms_xmldom.item(v_nodes,0);
2567 v_tag := dbms_xmldom.getNodeName(v_node);
2568 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2569 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
2570 p_accepted_termination_date := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
2571
2572 -- extract the prmary_flag from the xml event message
2573 v_document := dbms_xmldom.newdomdocument(p_event_data);
2574 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'actual_termiantion_date');
2575 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2576 v_node := dbms_xmldom.item(v_nodes,0);
2577 v_tag := dbms_xmldom.getNodeName(v_node);
2578 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2579 p_primary_flag := dbms_xmldom.getnodevalue(v_node_2);
2580
2581
2582
2583
2584
2585 open csr_fet_assignment(p_assignment_id,p_eff_start_date);
2586 fetch csr_fet_assignment into p_person_id_t,p_assignment_id_t,
2587 p_assignment_number_t,p_eff_start_date_t,
2588 p_eff_end_Date_t,p_original_date_of_hire_t,
2589 p_probation_period_t,p_probation_units_t,p_organization_id_t,
2590 p_job_id_t,p_assignment_status_type_id_t,
2591 p_location_id_t,p_employment_category_t,
2592 p_business_group_id_t,p_normal_hours_t,
2593 p_frequency_t,p_grade_id_t,
2594 p_supervisor_id_t,p_final_process_date_t,
2595 p_accepted_termination_date_t,p_primary_flag_t;
2596 close csr_fet_assignment;
2597
2598 if (nvl(p_person_id,p_person_id_t) = p_person_id_t)
2599 and (nvl(p_assignment_id,p_assignment_id_t) = p_assignment_id_t)
2600 and (nvl(p_assignment_number,p_assignment_number_t) = p_assignment_number_t)
2601 and (nvl(p_eff_start_date,p_eff_start_date_t) = p_eff_start_date_t)
2602 and (nvl(p_eff_end_Date,p_eff_end_Date_t) = p_eff_end_Date_t)
2603 and (nvl(p_original_date_of_hire,p_original_date_of_hire_t) = p_original_date_of_hire_t)
2604 and (nvl(p_probation_period,p_probation_period_t) = p_probation_period_t)
2605 and (nvl(p_probation_units,p_probation_units_t) = p_probation_units_t)
2606 and (nvl(p_organization_id,p_organization_id_t) = p_organization_id_t)
2607 and (nvl(p_job_id,p_job_id_t) = p_job_id_t)
2608 and (nvl(p_assignment_status_type_id,p_assignment_status_type_id_t) = p_assignment_status_type_id_t)
2609 and (nvl(p_location_id,p_location_id_t) = p_location_id_t)
2610 and (nvl(p_employment_category,p_employment_category_t) = p_employment_category_t)
2611 and (nvl(p_business_group_id,p_business_group_id_t) = p_business_group_id_t)
2612 and (nvl(p_normal_hours,p_normal_hours_t) = p_normal_hours_t)
2613 and (nvl(p_frequency,p_frequency_t) = p_frequency_t)
2614 and (nvl(p_grade_id,p_grade_id_t) = p_grade_id_t)
2615 and (nvl(p_supervisor_id,p_supervisor_id_t) = p_supervisor_id_t)
2616 and (nvl(p_final_process_date,p_final_process_date_t) = p_final_process_date_t)
2617 and (nvl(p_accepted_termination_date,p_accepted_termination_date_t) = p_accepted_termination_date_t)
2618 and (nvl(p_primary_flag,p_primary_flag_t) = p_primary_flag_t)
2619 then
2620
2621 p_assignment_data := hr_hrhd_rir_wf.sif_workforce_data(p_assignment_id => p_assignment_id,
2622 p_asg_op_flag =>p_asg_op_flag,
2623 p_date => to_date(p_eff_start_date,'DD/MM/YYYY'));
2624
2625 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
2626
2627 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.asgchange',
2628 p_event_key => to_char(p_assignment_id)||'-'||to_char(p_unique_key),
2629 p_event_data => p_assignment_data);
2630
2631 end if;
2632
2633 end workforce_callbackable;
2634
2635 procedure person_callbackable(my_parms in wf_parameter_list_t)
2636 is
2637
2638
2639 p_person_data clob;
2640 p_person_op_flag varchar2(5);
2641 p_unique_key number;
2642 p_date date;
2643 p_event_data clob;
2644
2645 --data required for this
2646 p_person_id per_all_people_f.person_id%type;
2647 p_business_grp_id per_all_people_f.business_group_id%type;
2648 p_legislation_code varchar2(50);
2649 p_person_type_id per_all_people_f.person_type_id%type;
2650 p_emp_number per_all_people_f.employee_number%type;
2651 p_applicant_number per_all_people_f.applicant_number%type;
2652 p_npw_number per_all_people_f.npw_number%type;
2653 p_date_of_birth per_all_people_f.date_of_birth%type;
2654 p_town_of_birth per_all_people_f.town_of_birth%type;
2655 p_cntry_of_birth per_all_people_f.country_of_birth%type;
2656 p_date_of_death per_all_people_f.date_of_death%type;
2657 p_orgnl_dt_of_hire per_all_people_f.original_date_of_hire%type;
2658 p_eff_st_dt per_all_people_f.effective_start_date%type;
2659 p_eff_end_dt per_all_people_f.effective_end_date%type;
2660 p_sex per_all_people_f.sex%type;
2661 p_full_name per_all_people_f.full_name%type;
2662 p_suffix per_all_people_f.suffix%type;
2663 p_title per_all_people_f.title%type;
2664 p_last_name per_all_people_f.last_name%type;
2665 p_first_name per_all_people_f.first_name%type;
2666 p_middle_names per_all_people_f.middle_names%type;
2667 p_nationality per_all_people_f.nationality%type;
2668 p_national_identifier per_all_people_f.national_identifier%type;
2669 p_email_address per_all_people_f.email_address%type;
2670
2671 --data required for this
2672 p_business_grp_id_t per_all_people_f.business_group_id%type;
2673 p_person_type_id_t per_all_people_f.person_type_id%type;
2674 p_emp_number_t per_all_people_f.employee_number%type;
2675 p_applicant_number_t per_all_people_f.applicant_number%type;
2676 p_npw_number_t per_all_people_f.npw_number%type;
2677 p_date_of_birth_t per_all_people_f.date_of_birth%type;
2678 p_town_of_birth_t per_all_people_f.town_of_birth%type;
2679 p_cntry_of_birth_t per_all_people_f.country_of_birth%type;
2680 p_date_of_death_t per_all_people_f.date_of_death%type;
2681 p_orgnl_dt_of_hire_t per_all_people_f.original_date_of_hire%type;
2682 p_eff_st_dt_t per_all_people_f.effective_start_date%type;
2683 p_eff_end_dt_t per_all_people_f.effective_end_date%type;
2684 p_sex_t per_all_people_f.sex%type;
2685 p_full_name_t per_all_people_f.full_name%type;
2686 p_suffix_t per_all_people_f.suffix%type;
2687 p_title_t per_all_people_f.title%type;
2688 p_last_name_t per_all_people_f.last_name%type;
2689 p_first_name_t per_all_people_f.first_name%type;
2690 p_middle_names_t per_all_people_f.middle_names%type;
2691 p_nationality_t per_all_people_f.nationality%type;
2692 p_national_identifier_t per_all_people_f.national_identifier%type;
2693 p_email_address_t per_all_people_f.email_address%type;
2694
2695 l_transaction_id Number;
2696 l_transaction_id_canonical Varchar2(30);
2697
2698 /*Cursor to fetch the person details*/
2699
2700 cursor csr_person_data(p_person_id number,p_eff_st_date date) is
2701 SELECT
2702 ppf.business_group_id,
2703 EMPLOYEE_NUMBER,
2704 APPLICANT_NUMBER,
2705 NPW_NUMBER,
2706 PERSON_TYPE_ID ,
2707 DATE_OF_BIRTH,
2708 TOWN_OF_BIRTH,
2709 COUNTRY_OF_BIRTH,
2710 DATE_OF_DEATH,
2711 ORIGINAL_DATE_OF_HIRE,
2712 EFFECTIVE_START_DATE,
2713 EFFECTIVE_END_DATE,
2714 SEX,
2715 FULL_NAME,
2716 SUFFIX,
2717 TITLE,
2718 LAST_NAME,
2719 FIRST_NAME,
2720 MIDDLE_NAMES,
2721 NATIONALITY,
2722 NATIONAL_IDENTIFIER,
2723 EMAIL_ADDRESS
2724
2725 FROM PER_ALL_PEOPLE_F ppf
2726 where person_id = p_person_id
2727 and p_eff_st_date between effective_start_date and effective_end_date;
2728
2729
2730 v_document dbms_xmldom.domdocument;
2731 v_nodes dbms_xmldom.DOMNodeList;
2732 v_element_x dbms_xmldom.DOMElement;
2733 v_node dbms_xmldom.DOMNode;
2734 v_node_2 dbms_xmldom.DOMNode;
2735 v_tag VARCHAR2(100);
2736 p_eff_date VARCHAR2(100);
2737
2738
2739
2740 begin
2741
2742
2743 p_person_id := wf_event.getValueForParameter('person_id', my_parms);
2744 p_person_op_flag := wf_event.getValueForParameter('person_op_flag', my_parms);
2745 p_date := wf_event.getValueForParameter('eff_date', my_parms);
2746 l_transaction_id_canonical := wf_event.getValueForParameter('transaction_id', my_parms);
2747 l_transaction_id := FND_NUMBER.canonical_to_number(l_transaction_id_canonical);
2748 hr_hrhd_event_data_handler.hrhd_retrieve_event_data(l_transaction_id, p_event_data);
2749 --p_event_data := wf_event.getValueForParameter('event_data', my_parms);
2750 v_document := dbms_xmldom.newdomdocument(p_event_data);
2751 -- extract the effective_end_date from the xml event message
2752
2753 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'per_effective_end_date');
2754 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2755 v_node := dbms_xmldom.item(v_nodes,0);
2756 v_tag := dbms_xmldom.getNodeName(v_node);
2757 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2758 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
2759
2760 p_eff_end_dt := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
2761
2762 -- extract the business_group_id from the xml event message
2763
2764 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'business_group_id');
2765 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2766 v_node := dbms_xmldom.item(v_nodes,0);
2767 v_tag := dbms_xmldom.getNodeName(v_node);
2768 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2769 p_business_grp_id := dbms_xmldom.getnodevalue(v_node_2);
2770
2771 -- extract the person_type_id from the xml event message
2772
2773 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'person_type_id');
2774 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2775 v_node := dbms_xmldom.item(v_nodes,0);
2776 v_tag := dbms_xmldom.getNodeName(v_node);
2777 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2778 p_person_type_id := dbms_xmldom.getnodevalue(v_node_2);
2779
2780 -- extract the employee_number from the xml event message
2781
2782 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'employee_number');
2783 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2784 v_node := dbms_xmldom.item(v_nodes,0);
2785 v_tag := dbms_xmldom.getNodeName(v_node);
2786 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2787 p_emp_number := dbms_xmldom.getnodevalue(v_node_2);
2788
2789 -- extract the applicant_number from the xml event message
2790
2791 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'applicant_number');
2792 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2793 v_node := dbms_xmldom.item(v_nodes,0);
2794 v_tag := dbms_xmldom.getNodeName(v_node);
2795 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2796 p_applicant_number := dbms_xmldom.getnodevalue(v_node_2);
2797
2798 -- extract the npw_number from the xml event message
2799 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'npw_number');
2800 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2801 v_node := dbms_xmldom.item(v_nodes,0);
2802 v_tag := dbms_xmldom.getNodeName(v_node);
2803 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2804 p_npw_number := dbms_xmldom.getnodevalue(v_node_2);
2805
2806 -- extract the npw_number from the xml event message
2807 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'npw_number');
2808 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2809 v_node := dbms_xmldom.item(v_nodes,0);
2810 v_tag := dbms_xmldom.getNodeName(v_node);
2811 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2812 p_npw_number := dbms_xmldom.getnodevalue(v_node_2);
2813
2814
2815 -- extract the date_of_birth from the xml event message
2816 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_of_birth');
2817 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2818 v_node := dbms_xmldom.item(v_nodes,0);
2819 v_tag := dbms_xmldom.getNodeName(v_node);
2820 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2821 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
2822 p_date_of_birth := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
2823
2824 -- extract the town_of_birth from the xml event message
2825 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'town_of_birth');
2826 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2827 v_node := dbms_xmldom.item(v_nodes,0);
2828 v_tag := dbms_xmldom.getNodeName(v_node);
2829 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2830 p_town_of_birth := dbms_xmldom.getnodevalue(v_node_2);
2831
2832 -- extract the cntry_of_birth from the xml event message
2833 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'country_of_birth');
2834 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2835 v_node := dbms_xmldom.item(v_nodes,0);
2836 v_tag := dbms_xmldom.getNodeName(v_node);
2837 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2838 p_cntry_of_birth := dbms_xmldom.getnodevalue(v_node_2);
2839
2840
2841 -- extract the date_of_death from the xml event message
2842 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_of_death');
2843 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2844 v_node := dbms_xmldom.item(v_nodes,0);
2845 v_tag := dbms_xmldom.getNodeName(v_node);
2846 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2847 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
2848 p_date_of_death := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
2849
2850 -- extract the original_date_of_hire from the xml event message
2851 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'original_date_of_hire');
2852 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2853 v_node := dbms_xmldom.item(v_nodes,0);
2854 v_tag := dbms_xmldom.getNodeName(v_node);
2855 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2856 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
2857 p_orgnl_dt_of_hire := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
2858
2859 -- extract the sex from the xml event message
2860 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'sex');
2861 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2862 v_node := dbms_xmldom.item(v_nodes,0);
2863 v_tag := dbms_xmldom.getNodeName(v_node);
2864 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2865 p_sex := dbms_xmldom.getnodevalue(v_node_2);
2866
2867 -- extract the full_name from the xml event message
2868 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'full_name');
2869 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2870 v_node := dbms_xmldom.item(v_nodes,0);
2871 v_tag := dbms_xmldom.getNodeName(v_node);
2872 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2873 p_full_name := dbms_xmldom.getnodevalue(v_node_2);
2874
2875 -- extract the full_name from the xml event message
2876 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'suffix');
2877 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2878 v_node := dbms_xmldom.item(v_nodes,0);
2879 v_tag := dbms_xmldom.getNodeName(v_node);
2880 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2881 p_suffix := dbms_xmldom.getnodevalue(v_node_2);
2882
2883
2884 -- extract the title from the xml event message
2885 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'title');
2886 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2887 v_node := dbms_xmldom.item(v_nodes,0);
2888 v_tag := dbms_xmldom.getNodeName(v_node);
2889 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2890 p_title := dbms_xmldom.getnodevalue(v_node_2);
2891
2892 -- extract the last_name from the xml event message
2893 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'last_name');
2894 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2895 v_node := dbms_xmldom.item(v_nodes,0);
2896 v_tag := dbms_xmldom.getNodeName(v_node);
2897 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2898 p_last_name := dbms_xmldom.getnodevalue(v_node_2);
2899
2900 -- extract the middle_anmes from the xml event message
2901 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'middle_names');
2902 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2903 v_node := dbms_xmldom.item(v_nodes,0);
2904 v_tag := dbms_xmldom.getNodeName(v_node);
2905 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2906 p_middle_names := dbms_xmldom.getnodevalue(v_node_2);
2907
2908 -- extract the nationality from the xml event message
2909 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'nationality');
2910 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2911 v_node := dbms_xmldom.item(v_nodes,0);
2912 v_tag := dbms_xmldom.getNodeName(v_node);
2913 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2914 p_nationality := dbms_xmldom.getnodevalue(v_node_2);
2915
2916
2917 -- extract the national_identifier from the xml event message
2918 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'national_identifier');
2919 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2920 v_node := dbms_xmldom.item(v_nodes,0);
2921 v_tag := dbms_xmldom.getNodeName(v_node);
2922 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2923 p_national_identifier := dbms_xmldom.getnodevalue(v_node_2);
2924
2925 -- extract the email_address from the xml event message
2926 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'email_address');
2927 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
2928 v_node := dbms_xmldom.item(v_nodes,0);
2929 v_tag := dbms_xmldom.getNodeName(v_node);
2930 v_node_2 := dbms_xmldom.getfirstchild(v_node );
2931 p_email_address := dbms_xmldom.getnodevalue(v_node_2);
2932
2933
2934
2935 open csr_person_data(p_person_id,p_date);
2936 fetch csr_person_data into p_business_grp_id_t,p_emp_number_t,
2937 p_applicant_number_t,p_npw_number_t,p_person_type_id_t ,p_date_of_birth_t,p_town_of_birth_t,
2938 p_cntry_of_birth_t,p_date_of_death_t,p_orgnl_dt_of_hire_t,p_eff_st_dt_t,
2939 p_eff_end_dt_t,p_sex_t,p_full_name_t,p_suffix_t,p_title_t,
2940 p_first_name_t,p_last_name_t,p_middle_names_t,p_nationality_t,p_national_identifier_t,p_email_address_t;
2941 close csr_person_data;
2942
2943
2944 if ( (nvl(p_business_grp_id,p_business_grp_id_t) = p_business_grp_id_t)
2945 and (nvl(p_person_type_id,p_person_type_id_t) = p_person_type_id_t)
2946 and (nvl(p_emp_number,p_emp_number_t) = p_emp_number_t)
2947 and (nvl(p_applicant_number,p_applicant_number_t) = p_applicant_number_t)
2948 and (nvl(p_npw_number,p_npw_number_t) = p_npw_number_t)
2949 and (nvl(p_date_of_birth,p_date_of_birth_t) = p_date_of_birth_t)
2950 and (nvl(p_town_of_birth,p_town_of_birth_t) = p_town_of_birth_t)
2951 and (nvl(p_cntry_of_birth,p_cntry_of_birth_t) = p_cntry_of_birth_t)
2952 and (nvl(p_date_of_death,p_date_of_death_t) = p_date_of_death_t)
2953 and (nvl(p_orgnl_dt_of_hire,p_orgnl_dt_of_hire_t) = p_orgnl_dt_of_hire_t)
2954 and (nvl(p_eff_st_dt,p_eff_st_dt_t) = p_eff_st_dt_t)
2955 and (nvl(p_eff_end_dt,p_eff_end_dt_t) = p_eff_end_dt_t)
2956 and (nvl(p_sex,p_sex_t) = p_sex_t)
2957 and (nvl(p_full_name,p_full_name_t) = p_full_name_t)
2958 and (nvl(p_suffix,p_suffix_t) = p_suffix_t)
2959 and (nvl(p_title,p_title_t) = p_title_t)
2960 and (nvl(p_last_name,p_last_name_t) = p_last_name_t)
2961 and (nvl(p_first_name,p_first_name_t) = p_first_name_t)
2962 and (nvl(p_middle_names,p_middle_names_t) = p_middle_names_t)
2963 and (nvl(p_nationality,p_nationality_t) = p_nationality_t)
2964 and (nvl(p_national_identifier,p_national_identifier_t) = p_national_identifier_t)
2965 and (nvl(p_email_address,p_email_address_t) = p_email_address_t) )
2966 then
2967
2968 p_person_data := hr_hrhd_rir_wf.sif_person_data(p_person_id => p_person_id,
2969 p_address_id => null,
2970 p_phone_id => null,
2971 p_person_op_flag =>p_person_op_flag,
2972 p_date => to_date(p_date,'DD/MM/YYYY'));
2973
2974 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
2975
2976
2977 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.personchange',
2978 p_event_key => to_char(p_person_id)||'-'||to_char(p_unique_key),
2979 p_event_data => p_person_data);
2980
2981 end if;
2982
2983 end person_callbackable;
2984
2985
2986 procedure address_callbackable(my_parms in wf_parameter_list_t)
2987 is
2988
2989
2990 p_person_data clob;
2991 p_person_op_flag varchar2(5);
2992 p_unique_key number;
2993 p_date date;
2994 p_event_data clob;
2995 p_person_id per_all_people_f.person_id%type;
2996
2997 p_address_id per_addresses.address_id%type;
2998 p_address_type per_addresses.address_type%type;
2999 p_addr_date_from per_addresses.date_from%type;
3000 p_addr_date_to per_addresses.date_to%type;
3001 p_addr_cntry per_addresses.country%type;
3002 p_Addr_Line1 per_addresses.address_line1%type;
3003 p_Addr_Line2 per_addresses.address_line2%type;
3004 p_Addr_Line3 per_addresses.address_line3%type;
3005 p_Town_Or_City per_addresses.town_or_city%type;
3006 p_Tel_Num_1 per_addresses.telephone_number_1%type;
3007 p_Region_1 per_addresses.region_1%type;
3008 p_Region_2 per_addresses.region_2%type;
3009 p_Postal_Code per_addresses.postal_code%type;
3010 p_Primary_Flag per_addresses.postal_code%type;
3011
3012 p_address_type_t per_addresses.address_type%type;
3013 p_addr_date_from_t per_addresses.date_from%type;
3014 p_addr_date_to_t per_addresses.date_to%type;
3015 p_addr_cntry_t per_addresses.country%type;
3016 p_Addr_Line1_t per_addresses.address_line1%type;
3017 p_Addr_Line2_t per_addresses.address_line2%type;
3018 p_Addr_Line3_t per_addresses.address_line3%type;
3019 p_Town_Or_City_t per_addresses.town_or_city%type;
3020 p_Tel_Num_1_t per_addresses.telephone_number_1%type;
3021 p_Region_1_t per_addresses.region_1%type;
3022 p_Region_2_t per_addresses.region_2%type;
3023 p_Postal_Code_t per_addresses.postal_code%type;
3024 p_Primary_Flag_t per_addresses.postal_code%type;
3025
3026
3027 l_transaction_id_canonical Varchar2(30);
3028 l_transaction_id Number;
3029
3030
3031 Cursor Csr_Address_Data(P_Person_Id Number,P_Address_Id number,P_Eff_St_Dt Date ) Is
3032 Select
3033 Address_Type,
3034 Date_From,
3035 Date_To,
3036 Country,
3037 Address_Line1,
3038 Address_Line2,
3039 Address_Line3,
3040 Town_Or_City,
3041 Telephone_Number_1,
3042 Region_1,
3043 Region_2,
3044 Postal_Code,
3045 Primary_flag
3046 FROM per_addresses
3047 where person_id = p_person_id
3048 and address_id = p_address_id
3049 and P_Eff_St_Dt between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
3050
3051 v_document dbms_xmldom.domdocument;
3052 v_nodes dbms_xmldom.DOMNodeList;
3053 v_element_x dbms_xmldom.DOMElement;
3054 v_node dbms_xmldom.DOMNode;
3055 v_node_2 dbms_xmldom.DOMNode;
3056 v_tag VARCHAR2(100);
3057 p_eff_date VARCHAR2(100);
3058
3059 begin
3060
3061 p_person_id := wf_event.getValueForParameter('person_id', my_parms);
3062 p_date := wf_event.getValueForParameter('eff_date', my_parms);
3063 l_transaction_id_canonical := wf_event.getValueForParameter('transaction_id', my_parms);
3064 l_transaction_id := FND_NUMBER.canonical_to_number(l_transaction_id_canonical);
3065 hr_hrhd_event_data_handler.hrhd_retrieve_event_data(l_transaction_id, p_event_data);
3066 --p_event_data := wf_event.getValueForParameter('event_data', my_parms);
3067 p_address_id := wf_event.getValueForParameter('address_id', my_parms);
3068 v_document := dbms_xmldom.newdomdocument(p_event_data);
3069
3070
3071 -- extract the date_from from the xml event message
3072
3073 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_from');
3074 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3075 v_node := dbms_xmldom.item(v_nodes,0);
3076 v_tag := dbms_xmldom.getNodeName(v_node);
3077 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3078 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
3079
3080 p_addr_date_from := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
3081
3082
3083 -- extract the date_to from the xml event message
3084
3085 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_to');
3086 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3087 v_node := dbms_xmldom.item(v_nodes,0);
3088 v_tag := dbms_xmldom.getNodeName(v_node);
3089 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3090 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
3091
3092 p_addr_date_to := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
3093
3094
3095 -- extract the address_type from the xml event message
3096
3097 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'address_type');
3098 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3099 v_node := dbms_xmldom.item(v_nodes,0);
3100 v_tag := dbms_xmldom.getNodeName(v_node);
3101 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3102 p_address_type := dbms_xmldom.getnodevalue(v_node_2);
3103
3104
3105 -- extract the address_line1 from the xml event message
3106
3107 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'address_line1');
3108 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3109 v_node := dbms_xmldom.item(v_nodes,0);
3110 v_tag := dbms_xmldom.getNodeName(v_node);
3111 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3112 p_Addr_Line1 := dbms_xmldom.getnodevalue(v_node_2);
3113
3114 -- extract the address_line2 from the xml event message
3115
3116 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'address_line2');
3117 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3118 v_node := dbms_xmldom.item(v_nodes,0);
3119 v_tag := dbms_xmldom.getNodeName(v_node);
3120 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3121 p_Addr_Line2 := dbms_xmldom.getnodevalue(v_node_2);
3122
3123
3124 -- extract the address_line2 from the xml event message
3125
3126 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'address_line3');
3127 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3128 v_node := dbms_xmldom.item(v_nodes,0);
3129 v_tag := dbms_xmldom.getNodeName(v_node);
3130 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3131 p_Addr_Line3 := dbms_xmldom.getnodevalue(v_node_2);
3132
3133 -- extract the town_or_city from the xml event message
3134
3135 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'town_or_city');
3136 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3137 v_node := dbms_xmldom.item(v_nodes,0);
3138 v_tag := dbms_xmldom.getNodeName(v_node);
3139 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3140 p_town_or_city := dbms_xmldom.getnodevalue(v_node_2);
3141
3142
3143 -- extract the telephone_number_1 from the xml event message
3144
3145 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'telephone_number_1');
3146 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3147 v_node := dbms_xmldom.item(v_nodes,0);
3148 v_tag := dbms_xmldom.getNodeName(v_node);
3149 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3150 p_Tel_Num_1 := dbms_xmldom.getnodevalue(v_node_2);
3151
3152 -- extract the region_1 from the xml event message
3153
3154 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'region_1');
3155 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3156 v_node := dbms_xmldom.item(v_nodes,0);
3157 v_tag := dbms_xmldom.getNodeName(v_node);
3158 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3159 p_region_1 := dbms_xmldom.getnodevalue(v_node_2);
3160
3161
3162 -- extract the region_2 from the xml event message
3163
3164 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'region_2');
3165 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3166 v_node := dbms_xmldom.item(v_nodes,0);
3167 v_tag := dbms_xmldom.getNodeName(v_node);
3168 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3169 p_region_2 := dbms_xmldom.getnodevalue(v_node_2);
3170
3171 -- extract the postal_code from the xml event message
3172
3173 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'postal_code');
3174 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3175 v_node := dbms_xmldom.item(v_nodes,0);
3176 v_tag := dbms_xmldom.getNodeName(v_node);
3177 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3178 p_postal_code := dbms_xmldom.getnodevalue(v_node_2);
3179
3180 -- extract the primary_flag from the xml event message
3181
3182 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'primary_flag');
3183 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3184 v_node := dbms_xmldom.item(v_nodes,0);
3185 v_tag := dbms_xmldom.getNodeName(v_node);
3186 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3187 p_primary_flag := dbms_xmldom.getnodevalue(v_node_2);
3188
3189 open Csr_Address_Data(p_person_id,p_address_id,p_date);
3190 fetch Csr_Address_Data into
3191 p_address_type_t,p_addr_date_from_t,p_addr_date_to_t,p_addr_cntry_t,p_Addr_Line1_t,
3192 p_Addr_Line2_t,p_Addr_Line3_t,p_Town_Or_City_t,p_Tel_Num_1_t,p_Region_1_t,p_Region_2_t,p_Postal_Code_t
3193 ,p_primary_flag_t;
3194 close Csr_Address_Data;
3195
3196 if ((nvl(p_address_type,p_address_type_t) = p_address_type_t)
3197 and (nvl(p_addr_date_from,p_addr_date_from_t) = p_addr_date_from_t)
3198 and (nvl(p_addr_date_to,p_addr_date_to_t) = p_addr_date_to_t)
3199 and (nvl(p_addr_cntry,p_addr_cntry_t) = p_addr_cntry_t)
3200 and (nvl(p_Addr_Line1,p_Addr_Line1_t) = p_Addr_Line1_t)
3201 and (nvl(p_Addr_Line2,p_Addr_Line2_t) = p_Addr_Line2_t)
3202 and (nvl(p_Town_Or_City,p_Town_Or_City_t) = p_Town_Or_City_t)
3203 and (nvl(p_Tel_Num_1,p_Tel_Num_1_t) = p_Tel_Num_1_t)
3204 and (nvl(p_Region_1,p_Region_1_t) = p_Region_1_t)
3205 and (nvl(p_Region_2,p_Region_2_t) = p_Region_2_t)
3206 and (nvl(p_Postal_Code,p_Postal_Code_t) = p_Postal_Code_t)
3207 and (nvl(p_primary_flag,p_primary_flag_t) = p_primary_flag_t) )
3208 then
3209
3210 p_person_data := hr_hrhd_rir_wf.sif_person_data(p_person_id => p_person_id,
3211 p_address_id => p_address_id,
3212 p_phone_id => null,
3213 p_person_op_flag =>p_person_op_flag,
3214 p_date => to_date(p_date,'DD/MM/YYYY'));
3215
3216 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
3217
3218
3219 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.personchange',
3220 p_event_key => to_char(p_person_id)||'-'||to_char(p_unique_key),
3221 p_event_data => p_person_data);
3222
3223 end if;
3224
3225
3226
3227 end address_callbackable;
3228
3229 procedure phone_callbackable(my_parms in wf_parameter_list_t)
3230 is
3231
3232
3233 p_person_data clob;
3234 p_person_op_flag varchar2(5);
3235 p_unique_key number;
3236 p_date date;
3237 p_event_data clob;
3238 p_person_id per_all_people_f.person_id%type;
3239
3240 p_phone_id per_phones.phone_id%type;
3241 p_phn_date_from per_phones.date_from%type;
3242 p_phn_date_to per_phones.date_to%type;
3243 p_phone_type per_phones.phone_type%type;
3244 p_phone_number per_phones.phone_number%type;
3245
3246 p_phn_date_from_t per_phones.date_from%type;
3247 p_phn_date_to_t per_phones.date_to%type;
3248 p_phone_type_t per_phones.phone_type%type;
3249 p_phone_number_t per_phones.phone_number%type;
3250
3251 l_transaction_id Number :=0 ;
3252 l_transaction_id_canonical Varchar2(30);
3253
3254 Cursor Csr_phone_Data(P_Person_Id Number,P_phone_Id number,P_Eff_St_Dt Date ) Is
3255 Select
3256 ppn.date_from ,
3257 ppn.date_to ,
3258 PHONE_TYPE,
3259 PHONE_NUMBER
3260 FROM per_phones ppn
3261 where ppn.PARENT_ID = P_PERSON_ID
3262 AND PPN.PARENT_TABLE = 'PER_ALL_PEOPLE_F'
3263 and P_Eff_St_Dt between date_from and nvl(date_to,to_date('31/12/4712','DD/MM/YYYY'));
3264
3265 v_document dbms_xmldom.domdocument;
3266 v_nodes dbms_xmldom.DOMNodeList;
3267 v_element_x dbms_xmldom.DOMElement;
3268 v_node dbms_xmldom.DOMNode;
3269 v_node_2 dbms_xmldom.DOMNode;
3270 v_tag VARCHAR2(100);
3271 p_eff_date VARCHAR2(100);
3272
3273 begin
3274
3275 p_person_id := wf_event.getValueForParameter('person_id', my_parms);
3276 p_date := wf_event.getValueForParameter('eff_date', my_parms);
3277 l_transaction_id_canonical := wf_event.getValueForParameter('transaction_id', my_parms);
3278 l_transaction_id := FND_NUMBER.canonical_to_number(l_transaction_id_canonical);
3279 hr_hrhd_event_data_handler.hrhd_retrieve_event_data(l_transaction_id, p_event_data);
3280 --p_event_data := wf_event.getValueForParameter('event_data', my_parms);
3281 p_phone_id := wf_event.getValueForParameter('phone_id', my_parms);
3282 v_document := dbms_xmldom.newdomdocument(p_event_data);
3283
3284
3285 -- extract the date_from from the xml event message
3286
3287 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_from');
3288 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3289 v_node := dbms_xmldom.item(v_nodes,0);
3290 v_tag := dbms_xmldom.getNodeName(v_node);
3291 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3292 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
3293
3294 p_phn_date_from := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
3295
3296
3297 -- extract the date_to from the xml event message
3298
3299 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'date_to');
3300 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3301 v_node := dbms_xmldom.item(v_nodes,0);
3302 v_tag := dbms_xmldom.getNodeName(v_node);
3303 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3304 p_eff_date := dbms_xmldom.getnodevalue(v_node_2);
3305
3306 p_phn_date_to := to_date(substr(p_eff_date,1,10),'YYYY/MM/DD');
3307
3308
3309 -- extract the address_type from the xml event message
3310
3311 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'phone_type');
3312 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3313 v_node := dbms_xmldom.item(v_nodes,0);
3314 v_tag := dbms_xmldom.getNodeName(v_node);
3315 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3316 p_phone_type := dbms_xmldom.getnodevalue(v_node_2);
3317
3318
3319 -- extract the address_line1 from the xml event message
3320
3321 v_nodes := dbms_xmldom.getElementsByTagName(v_document, 'phone_number');
3322 v_element_x := dbms_xmldom.makeelement(xmldom.item(v_nodes,0));
3323 v_node := dbms_xmldom.item(v_nodes,0);
3324 v_tag := dbms_xmldom.getNodeName(v_node);
3325 v_node_2 := dbms_xmldom.getfirstchild(v_node );
3326 p_phone_number := dbms_xmldom.getnodevalue(v_node_2);
3327
3328
3329
3330 open Csr_phone_Data(p_person_id,p_phone_id,p_date);
3331 fetch Csr_phone_Data into
3332 p_phn_date_from_t,p_phn_date_to_t,p_phone_type_t,p_phone_number_t;
3333 close Csr_phone_Data;
3334
3335 if ((nvl(p_phn_date_from,p_phn_date_from_t) = p_phn_date_from_t)
3336 and (nvl(p_phn_date_to,p_phn_date_to_t) = p_phn_date_to_t)
3337 and (nvl(p_phone_type,p_phone_type_t) = p_phone_type_t)
3338 and (nvl(p_phone_number,p_phone_number_t) = p_phone_number_t))
3339 then
3340
3341 p_person_data := hr_hrhd_rir_wf.sif_person_data(p_person_id => p_person_id,
3342 p_address_id => null,
3343 p_phone_id => p_phone_id,
3344 p_person_op_flag =>'U',
3345 p_date => to_date(p_date,'DD/MM/YYYY'));
3346
3347 select hrhd_delta_sync_seq.nextval into p_unique_key from dual;
3348
3349
3350 WF_EVENT.Raise(p_event_name => 'oracle.apps.per.hrhd.personchange',
3351 p_event_key => to_char(p_person_id)||'-'||to_char(p_unique_key),
3352 p_event_data => p_person_data);
3353
3354 end if;
3355
3356
3357
3358 end phone_callbackable;
3359
3360
3361 end HR_HRHD_RIR_WF;
3362