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