DBA Data[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;