[Home] [Help]
PACKAGE BODY: APPS.HR_TRANSACTION_SWI
Source
1 PACKAGE BODY HR_TRANSACTION_SWI as
2 /* $Header: hrtrnswi.pkb 120.19.12010000.3 2008/10/30 05:14:54 ubhat ship $ */
3 -- Global variables
4 g_date_format varchar2(10) := 'RRRR/MM/DD';
5 g_package varchar2(33) := 'HR_TRANSACTION_SWI.';
6 g_debug boolean := hr_utility.debug_enabled;
7 g_tempClob CLOB;
8 -- EO Api Map related global Variables
9 g_current_EO_name varchar2(160);
10 g_current_EO_ApiName varchar2(160);
11 -- EO Api Map related Associative Arrays indexed by BINARY_INTEGER for 8i Compatability
12 TYPE eo_map_type IS TABLE of varchar2(160) INDEX BY BINARY_INTEGER;
13 g_EO_Name_map eo_map_type;
14 g_EO_ApiName_map eo_map_type;
15 g_process_api_internal_error EXCEPTION;
16 g_processing_EO_name VARCHAR2(1000);
17 g_processing_EO_cdatavalue VARCHAR2(1000);
18
19
20 --
21 -- ---------------------------------------------------------------------- --
22 -- -----------------------<create_transaction>--------------------------- --
23 -- ---------------------------------------------------------------------- --
24 --
25
26 procedure create_transaction
27 (
28 P_TRANSACTION_ID IN NUMBER
29 ,P_CREATOR_PERSON_ID IN NUMBER
30 ,P_TRANSACTION_PRIVILEGE IN VARCHAR2
31 ,P_PRODUCT_CODE IN VARCHAR2 DEFAULT NULL
32 ,P_URL IN LONG DEFAULT NULL
33 ,P_STATUS IN VARCHAR2 DEFAULT NULL
34 ,P_SECTION_DISPLAY_NAME IN VARCHAR2 DEFAULT NULL
35 ,P_FUNCTION_ID IN NUMBER DEFAULT NULL
36 ,P_TRANSACTION_REF_TABLE IN VARCHAR2 DEFAULT NULL
37 ,P_TRANSACTION_REF_ID IN NUMBER DEFAULT NULL
38 ,P_TRANSACTION_TYPE IN VARCHAR2 DEFAULT NULL
39 ,P_ASSIGNMENT_ID IN NUMBER DEFAULT NULL
40 ,P_API_ADDTNL_INFO IN VARCHAR2 DEFAULT NULL
41 ,P_SELECTED_PERSON_ID IN NUMBER DEFAULT NULL
42 ,P_ITEM_TYPE IN VARCHAR2 DEFAULT NULL
43 ,P_ITEM_KEY IN VARCHAR2 DEFAULT NULL
44 ,P_TRANSACTION_EFFECTIVE_DATE IN DATE DEFAULT NULL
45 ,P_PROCESS_NAME IN VARCHAR2 DEFAULT NULL
46 ,P_TRANSACTION_STATE IN VARCHAR2 DEFAULT NULL
47 ,P_EFFECTIVE_DATE_OPTION IN VARCHAR2 DEFAULT NULL
48 ,P_RPTG_GRP_ID IN NUMBER DEFAULT NULL
49 ,P_PLAN_ID IN NUMBER DEFAULT NULL
50 ,P_CREATOR_ROLE IN VARCHAR2 DEFAULT NULL
51 ,P_LAST_UPDATE_ROLE IN VARCHAR2 DEFAULT NULL
52 ,P_PARENT_TRANSACTION_ID IN NUMBER DEFAULT NULL
53 ,P_RELAUNCH_FUNCTION IN VARCHAR2 DEFAULT NULL
54 ,P_TRANSACTION_GROUP IN VARCHAR2 DEFAULT NULL
55 ,P_TRANSACTION_IDENTIFIER IN VARCHAR2 DEFAULT NULL
56 ,P_TRANSACTION_DOCUMENT IN CLOB DEFAULT NULL
57 ,P_VALIDATE IN NUMBER default hr_api.g_false_num
58 )
59 is
60
61 l_TRANSACTION_ID hr_api_transactions.TRANSACTION_ID%type;
62 l_creator_role hr_api_transactions.creator_role%type;
63 l_last_update_role hr_api_transactions.last_update_role%type;
64
65
66 PRAGMA AUTONOMOUS_TRANSACTION;
67 begin
68 --savepoint create_transaction;
69 l_creator_role := nvl(P_CREATOR_ROLE, 'PER:' || fnd_global.employee_id);
70 l_last_update_role := nvl(P_LAST_UPDATE_ROLE, 'PER:' || fnd_global.employee_id);
71
72
73
74 l_TRANSACTION_ID := P_TRANSACTION_ID;
75 hr_trn_ins.set_base_key_value(l_TRANSACTION_ID);
76 hr_trn_ins.ins(
77 p_validate => false
78 ,p_creator_person_id => p_creator_person_id
79 ,p_transaction_privilege => P_TRANSACTION_PRIVILEGE
80 ,p_transaction_id => l_TRANSACTION_ID
81 ,p_product_code => p_product_code
82 ,p_url=> p_url
83 ,p_status=>P_STATUS
84 ,p_section_display_name=>P_SECTION_DISPLAY_NAME
85 ,p_function_id=>P_FUNCTION_ID
86 ,p_transaction_ref_table=>p_transaction_ref_table
87 ,p_transaction_ref_id=>p_transaction_ref_id
88 ,p_transaction_type=>P_TRANSACTION_TYPE
89 ,p_assignment_id=>P_ASSIGNMENT_ID
90 ,p_selected_person_id=>P_SELECTED_PERSON_ID
91 ,p_item_type=>P_ITEM_TYPE
92 ,p_item_key=>P_ITEM_KEY
93 ,p_transaction_effective_date=>P_TRANSACTION_EFFECTIVE_DATE
94 ,p_process_name=>P_PROCESS_NAME
95 ,p_plan_id=>p_plan_id
96 ,p_rptg_grp_id=>p_rptg_grp_id
97 ,p_effective_date_option=>p_effective_date_option
98 ,p_api_addtnl_info=>p_api_addtnl_info
99 ,p_creator_role =>l_creator_role
100 ,p_last_update_role =>l_last_update_role
101 ,p_parent_transaction_id => p_parent_transaction_id
102 ,p_relaunch_function => p_relaunch_function
103 ,p_transaction_group => p_transaction_group
104 ,p_transaction_identifier => p_transaction_identifier
105 ,p_transaction_document => p_transaction_document
106
107
108
109 );
110 If P_VALIDATE = hr_api.g_false_num Then
111 commit;
112 Else
113 rollback;
114 End If;
115 exception
116 when OTHERS then
117 rollback; -- to create_transaction;
118 end create_transaction;
119
120 --
121 -- ---------------------------------------------------------------------- --
122 -- --------------------<create_transaction_step>------------------------- --
123 -- ---------------------------------------------------------------------- --
124 --
125
126 procedure create_transaction_step
127 (
128 P_API_NAME IN VARCHAR2
129 ,P_API_DISPLAY_NAME IN VARCHAR2 DEFAULT NULL
130 ,P_PROCESSING_ORDER IN NUMBER
131 ,P_ITEM_TYPE IN VARCHAR2 DEFAULT NULL
132 ,P_ITEM_KEY IN VARCHAR2 DEFAULT NULL
133 ,P_ACTIVITY_ID IN NUMBER DEFAULT NULL
134 ,P_CREATOR_PERSON_ID IN NUMBER
135 ,P_UPDATE_PERSON_ID IN NUMBER DEFAULT NULL
136 ,P_OBJECT_TYPE IN VARCHAR2 DEFAULT NULL
137 ,P_OBJECT_NAME IN VARCHAR2 DEFAULT NULL
138 ,P_OBJECT_IDENTIFIER IN VARCHAR2 DEFAULT NULL
139 ,P_OBJECT_STATE IN VARCHAR2 DEFAULT NULL
140 ,P_PK1 IN VARCHAR2 DEFAULT NULL
141 ,P_PK2 IN VARCHAR2 DEFAULT NULL
142 ,P_PK3 IN VARCHAR2 DEFAULT NULL
143 ,P_PK4 IN VARCHAR2 DEFAULT NULL
144 ,P_PK5 IN VARCHAR2 DEFAULT NULL
145 ,P_VALIDATE IN NUMBER DEFAULT hr_api.g_false_num
146 ,P_OBJECT_VERSION_NUMBER IN OUT nocopy NUMBER
147 ,P_TRANSACTION_ID IN NUMBER
148 ,P_TRANSACTION_STEP_ID IN NUMBER
149 ,p_information_category in VARCHAR2 default null
150 ,p_information1 in VARCHAR2 default null
151 ,p_information2 in VARCHAR2 default null
152 ,p_information3 in VARCHAR2 default null
153 ,p_information4 in VARCHAR2 default null
154 ,p_information5 in VARCHAR2 default null
155 ,p_information6 in VARCHAR2 default null
156 ,p_information7 in VARCHAR2 default null
157 ,p_information8 in VARCHAR2 default null
158 ,p_information9 in VARCHAR2 default null
159 ,p_information10 in VARCHAR2 default null
160 ,p_information11 in VARCHAR2 default null
161 ,p_information12 in VARCHAR2 default null
162 ,p_information13 in VARCHAR2 default null
163 ,p_information14 in VARCHAR2 default null
164 ,p_information15 in VARCHAR2 default null
165 ,p_information16 in VARCHAR2 default null
166 ,p_information17 in VARCHAR2 default null
167 ,p_information18 in VARCHAR2 default null
168 ,p_information19 in VARCHAR2 default null
169 ,p_information20 in VARCHAR2 default null
170 ,p_information21 in VARCHAR2 default null
171 ,p_information22 in VARCHAR2 default null
172 ,p_information23 in VARCHAR2 default null
173 ,p_information24 in VARCHAR2 default null
174 ,p_information25 in VARCHAR2 default null
175 ,p_information26 in VARCHAR2 default null
176 ,p_information27 in VARCHAR2 default null
177 ,p_information28 in VARCHAR2 default null
178 ,p_information29 in VARCHAR2 default null
179 ,p_information30 in VARCHAR2 default null
180
181 )
182 is
183 l_proc varchar2(72) := g_package || 'create_transaction_step';
184 l_result varchar2(100);
185 l_trns_object_version_number number;
186 l_transaction_step_id hr_api_transaction_steps.transaction_step_id%type;
187 PRAGMA AUTONOMOUS_TRANSACTION;
188 begin
189 --savepoint create_transaction_step;
190 l_transaction_step_id := P_TRANSACTION_STEP_ID;
191 hr_trs_ins.set_base_key_value(l_transaction_step_id);
192 hr_trs_ins.ins
193 (
194 p_transaction_step_id => l_transaction_step_id,
195 p_transaction_id => p_transaction_id,
196 p_api_name => p_api_name,
197 p_api_display_name => p_api_display_name,
198 p_processing_order => p_processing_order,
199 p_item_type => p_item_type,
200 p_item_key => p_item_key,
201 p_activity_id => p_activity_id,
202 p_creator_person_id => p_creator_person_id,
203 p_update_person_id => p_update_person_id,
204 p_object_version_number => p_object_version_number ,
205 p_OBJECT_TYPE => p_OBJECT_TYPE,
206 p_OBJECT_NAME => p_OBJECT_NAME,
207 p_OBJECT_IDENTIFIER => p_OBJECT_IDENTIFIER,
208 p_OBJECT_STATE => p_OBJECT_STATE,
209 p_PK1 => p_PK1,
210 p_PK2 => p_PK2,
211 p_PK3 => p_PK3,
212 p_PK4 => p_PK4,
213 p_PK5 => p_PK5,
214 p_information_category => p_information_category,
215 p_information1 => p_information1,
216 p_information2 => p_information2,
217 p_information3 => p_information3,
218 p_information4 => p_information4,
219 p_information5 => p_information5,
220 p_information6 => p_information6,
221 p_information7 => p_information7,
222 p_information8 => p_information8,
223 p_information9 => p_information9,
224 p_information10 => p_information10,
225 p_information11 => p_information11,
226 p_information12 => p_information12,
227 p_information13 => p_information13,
228 p_information14 => p_information14,
229 p_information15 => p_information15,
230 p_information16 => p_information16,
231 p_information17 => p_information17,
232 p_information18 => p_information18,
233 p_information19 => p_information19,
234 p_information20 => p_information20,
235 p_information21 => p_information21,
236 p_information22 => p_information22,
237 p_information23 => p_information23,
238 p_information24 => p_information24,
239 p_information25 => p_information25,
240 p_information26 => p_information26,
241 p_information27 => p_information27,
242 p_information28 => p_information28,
243 p_information29 => p_information29,
244 p_information30 => p_information30,
245 p_validate => false
246 );
247 If P_VALIDATE = hr_api.g_false_num Then
248 commit;
249 Else
250 rollback;
251 End If;
252 exception
253 when OTHERS then
254 rollback ;--to create_transaction_step;
255 raise;
256 end create_transaction_step;
257
258 procedure update_transaction
259 (
260 P_TRANSACTION_ID IN NUMBER
261 ,P_CREATOR_PERSON_ID IN NUMBER
262 ,P_TRANSACTION_PRIVILEGE IN VARCHAR2
263 ,P_PRODUCT_CODE IN VARCHAR2 DEFAULT NULL
264 ,P_URL IN LONG DEFAULT NULL
265 ,P_STATUS IN VARCHAR2 DEFAULT NULL
266 ,P_SECTION_DISPLAY_NAME IN VARCHAR2 DEFAULT NULL
267 ,P_FUNCTION_ID IN NUMBER DEFAULT NULL
268 ,P_TRANSACTION_REF_TABLE IN VARCHAR2 DEFAULT NULL
269 ,P_TRANSACTION_REF_ID IN NUMBER DEFAULT NULL
270 ,P_TRANSACTION_TYPE IN VARCHAR2 DEFAULT NULL
271 ,P_ASSIGNMENT_ID IN NUMBER DEFAULT NULL
272 ,P_API_ADDTNL_INFO IN VARCHAR2 DEFAULT NULL
273 ,P_SELECTED_PERSON_ID IN NUMBER DEFAULT NULL
274 ,P_ITEM_TYPE IN VARCHAR2 DEFAULT NULL
275 ,P_ITEM_KEY IN VARCHAR2 DEFAULT NULL
276 ,P_TRANSACTION_EFFECTIVE_DATE IN DATE DEFAULT NULL
277 ,P_PROCESS_NAME IN VARCHAR2 DEFAULT NULL
278 ,P_TRANSACTION_STATE IN VARCHAR2 DEFAULT NULL
279 ,P_EFFECTIVE_DATE_OPTION IN VARCHAR2 DEFAULT NULL
280 ,P_RPTG_GRP_ID IN NUMBER DEFAULT NULL
281 ,P_PLAN_ID IN NUMBER DEFAULT NULL
282 ,P_CREATOR_ROLE IN VARCHAR2 DEFAULT NULL
283 ,P_LAST_UPDATE_ROLE IN VARCHAR2 DEFAULT NULL
284 ,P_PARENT_TRANSACTION_ID IN NUMBER DEFAULT NULL
285 ,P_RELAUNCH_FUNCTION IN VARCHAR2 DEFAULT NULL
286 ,P_TRANSACTION_GROUP IN VARCHAR2 DEFAULT NULL
287 ,P_TRANSACTION_IDENTIFIER IN VARCHAR2 DEFAULT NULL
288 ,P_TRANSACTION_DOCUMENT IN CLOB DEFAULT NULL
289 ,P_VALIDATE IN NUMBER default hr_api.g_false_num
290 )
291 is
292 l_proc varchar2(72) := 'update_transaction';
293 l_last_update_role hr_api_transactions.last_update_role%type;
294 PRAGMA AUTONOMOUS_TRANSACTION;
295 begin
296 --savepoint update_transaction;
297 l_last_update_role := nvl(P_LAST_UPDATE_ROLE, 'PER:' || fnd_global.employee_id);
298 hr_trn_upd.upd(
299 p_validate => false
300 ,p_creator_person_id => p_creator_person_id
301 ,p_transaction_privilege => P_TRANSACTION_PRIVILEGE
302 ,p_transaction_id => p_TRANSACTION_ID
303 ,p_product_code => p_product_code
304 ,p_url=> p_url
305 ,p_status=>P_STATUS
306 ,p_section_display_name=>P_SECTION_DISPLAY_NAME
307 ,p_function_id=>P_FUNCTION_ID
308 ,p_transaction_ref_table=>p_transaction_ref_table
309 ,p_transaction_ref_id=>p_transaction_ref_id
310 ,p_transaction_type=>P_TRANSACTION_TYPE
311 ,p_assignment_id=>P_ASSIGNMENT_ID
312 ,p_selected_person_id=>P_SELECTED_PERSON_ID
313 ,p_item_type=>P_ITEM_TYPE
314 ,p_item_key=>P_ITEM_KEY
315 ,p_transaction_effective_date=>P_TRANSACTION_EFFECTIVE_DATE
316 ,p_process_name=>P_PROCESS_NAME
317 ,p_plan_id=>p_plan_id
318 ,p_rptg_grp_id=>p_rptg_grp_id
319 ,p_effective_date_option=>p_effective_date_option
320 ,p_api_addtnl_info=>p_api_addtnl_info
321 ,p_creator_role =>p_creator_role
322 ,p_last_update_role =>l_last_update_role
323 ,p_parent_transaction_id => p_parent_transaction_id
324 ,p_relaunch_function => p_relaunch_function
325 ,p_transaction_group => p_transaction_group
326 ,p_transaction_identifier => p_transaction_identifier
327 ,p_transaction_document => p_transaction_document
328 ,p_transaction_state => p_transaction_state -- Heena
329 );
330 If P_VALIDATE = hr_api.g_false_num Then
331 commit;
332 Else
333 rollback;
334 End If;
335 exception
336 when OTHERS then
337 rollback ;--to update_transaction;
338 end update_transaction;
339
340 --
341 -- ---------------------------------------------------------------------- --
342 -- --------------------<create_transaction_step>------------------------- --
343 -- ---------------------------------------------------------------------- --
344 --
345
346 procedure update_transaction_step
347 (
348 P_API_NAME IN VARCHAR2
349 ,P_API_DISPLAY_NAME IN VARCHAR2 DEFAULT NULL
350 ,P_PROCESSING_ORDER IN NUMBER
351 ,P_ITEM_TYPE IN VARCHAR2 DEFAULT NULL
352 ,P_ITEM_KEY IN VARCHAR2 DEFAULT NULL
353 ,P_ACTIVITY_ID IN NUMBER DEFAULT NULL
354 ,P_CREATOR_PERSON_ID IN NUMBER
355 ,P_UPDATE_PERSON_ID IN NUMBER DEFAULT NULL
356 ,P_OBJECT_TYPE IN VARCHAR2 DEFAULT NULL
357 ,P_OBJECT_NAME IN VARCHAR2 DEFAULT NULL
358 ,P_OBJECT_IDENTIFIER IN VARCHAR2 DEFAULT NULL
359 ,P_OBJECT_STATE IN VARCHAR2 DEFAULT NULL
360 ,P_PK1 IN VARCHAR2 DEFAULT NULL
361 ,P_PK2 IN VARCHAR2 DEFAULT NULL
362 ,P_PK3 IN VARCHAR2 DEFAULT NULL
363 ,P_PK4 IN VARCHAR2 DEFAULT NULL
364 ,P_PK5 IN VARCHAR2 DEFAULT NULL
365 ,P_VALIDATE IN NUMBER default hr_api.g_false_num
366 ,P_OBJECT_VERSION_NUMBER IN OUT nocopy NUMBER
367 ,P_TRANSACTION_ID IN NUMBER
368 ,P_TRANSACTION_STEP_ID IN NUMBER
369 ,p_information_category in VARCHAR2 default hr_api.g_varchar2
370 ,p_information1 in VARCHAR2 default hr_api.g_varchar2
371 ,p_information2 in VARCHAR2 default hr_api.g_varchar2
372 ,p_information3 in VARCHAR2 default hr_api.g_varchar2
373 ,p_information4 in VARCHAR2 default hr_api.g_varchar2
374 ,p_information5 in VARCHAR2 default hr_api.g_varchar2
375 ,p_information6 in VARCHAR2 default hr_api.g_varchar2
376 ,p_information7 in VARCHAR2 default hr_api.g_varchar2
377 ,p_information8 in VARCHAR2 default hr_api.g_varchar2
378 ,p_information9 in VARCHAR2 default hr_api.g_varchar2
379 ,p_information10 in VARCHAR2 default hr_api.g_varchar2
380 ,p_information11 in VARCHAR2 default hr_api.g_varchar2
381 ,p_information12 in VARCHAR2 default hr_api.g_varchar2
382 ,p_information13 in VARCHAR2 default hr_api.g_varchar2
383 ,p_information14 in VARCHAR2 default hr_api.g_varchar2
384 ,p_information15 in VARCHAR2 default hr_api.g_varchar2
385 ,p_information16 in VARCHAR2 default hr_api.g_varchar2
386 ,p_information17 in VARCHAR2 default hr_api.g_varchar2
387 ,p_information18 in VARCHAR2 default hr_api.g_varchar2
388 ,p_information19 in VARCHAR2 default hr_api.g_varchar2
389 ,p_information20 in VARCHAR2 default hr_api.g_varchar2
390 ,p_information21 in VARCHAR2 default hr_api.g_varchar2
391 ,p_information22 in VARCHAR2 default hr_api.g_varchar2
392 ,p_information23 in VARCHAR2 default hr_api.g_varchar2
393 ,p_information24 in VARCHAR2 default hr_api.g_varchar2
394 ,p_information25 in VARCHAR2 default hr_api.g_varchar2
395 ,p_information26 in VARCHAR2 default hr_api.g_varchar2
396 ,p_information27 in VARCHAR2 default hr_api.g_varchar2
397 ,p_information28 in VARCHAR2 default hr_api.g_varchar2
398 ,p_information29 in VARCHAR2 default hr_api.g_varchar2
399 ,p_information30 in VARCHAR2 default hr_api.g_varchar2
400 )
401 is
402 l_proc varchar2(72) := 'update_transaction_step';
403 l_result varchar2(100);
404 PRAGMA AUTONOMOUS_TRANSACTION;
405 begin
406 --savepoint update_transaction_step;
407 hr_trs_upd.upd
408 (
409 p_transaction_step_id => p_transaction_step_id,
410 p_transaction_id => p_transaction_id,
411 p_api_name => p_api_name,
412 p_api_display_name => p_api_display_name,
413 p_processing_order => p_processing_order,
414 p_item_type => p_item_type,
415 p_item_key => p_item_key,
416 p_activity_id => p_activity_id,
417 p_creator_person_id => p_creator_person_id,
418 p_update_person_id => p_update_person_id,
419 p_object_version_number => p_object_version_number ,
420 p_OBJECT_TYPE => p_OBJECT_TYPE,
421 p_OBJECT_NAME => p_OBJECT_NAME,
422 p_OBJECT_IDENTIFIER => p_OBJECT_IDENTIFIER,
423 p_OBJECT_STATE => p_OBJECT_STATE,
424 p_PK1 => p_PK1,
425 p_PK2 => p_PK2,
426 p_PK3 => p_PK3,
427 p_PK4 => p_PK4,
428 p_PK5 => p_PK5,
429 p_information_category => p_information_category,
430 p_information1 => p_information1,
431 p_information2 => p_information2,
432 p_information3 => p_information3,
433 p_information4 => p_information4,
434 p_information5 => p_information5,
435 p_information6 => p_information6,
436 p_information7 => p_information7,
437 p_information8 => p_information8,
438 p_information9 => p_information9,
439 p_information10 => p_information10,
440 p_information11 => p_information11,
441 p_information12 => p_information12,
442 p_information13 => p_information13,
443 p_information14 => p_information14,
444 p_information15 => p_information15,
445 p_information16 => p_information16,
446 p_information17 => p_information17,
447 p_information18 => p_information18,
448 p_information19 => p_information19,
449 p_information20 => p_information20,
450 p_information21 => p_information21,
451 p_information22 => p_information22,
452 p_information23 => p_information23,
453 p_information24 => p_information24,
454 p_information25 => p_information25,
455 p_information26 => p_information26,
456 p_information27 => p_information27,
457 p_information28 => p_information28,
458 p_information29 => p_information29,
459 p_information30 => p_information30,
460 p_validate => false
461 );
462 If P_VALIDATE = hr_api.g_false_num Then
463 commit;
464 Else
465 rollback;
466 End If;
467 exception
468 when OTHERS then
469 rollback ;--to update_transaction_step;
470 end update_transaction_step;
471
472 procedure delete_transaction_step
473 ( p_transaction_step_id in number
474 ,p_person_id in number
475 ,p_object_version_number in number
476 ,p_validate in number default hr_api.g_false_num
477 ) is
478 --
479 l_proc constant varchar2(100) := g_package || ' delete_transaction_step';
480 PRAGMA AUTONOMOUS_TRANSACTION;
481 begin
482 hr_transaction_api.delete_transaction_step
483 (
484 p_validate => false
485 ,p_transaction_step_id => p_transaction_step_id
486 ,p_person_id => p_person_id
487 ,p_object_version_number => p_object_version_number
488 );
489 If p_validate = hr_api.g_false_num Then
490 commit;
491 Else
492 rollback;
493 End If;
494 exception
495 when OTHERS then
496 rollback ;--to update_transaction_step;
497 raise;
498 end delete_transaction_step;
499
500 Function getAttributeValue(
501 p_commitNode in xmldom.DOMNode,
502 p_tagName in VARCHAR2,
503 p_attributeName in VARCHAR2)
504 return VARCHAR2 IS
505
506 x_isNull VARCHAR2(22);
507 l_tagName_NodeList xmldom.DOMNodeList;
508 l_tagName_Node xmldom.DOMNode;
509 l_proc varchar2(72) := g_package || 'getNumberValue';
510
511 Begin
512 --1. Navigate to the tagName
513 x_isNull := null;
514
515 l_tagName_NodeList := xmldom.getChildrenByTagName(xmldom.makeElement(p_commitNode),p_tagName);
516 if (xmldom.getLength(l_tagName_NodeList) > 0) then
517 --2. See if this tagName has a associated null=true attribute-value pair
518 l_tagName_Node := xmldom.item(l_tagName_NodeList,0);
519 x_isNull := xmldom.getAttribute(xmldom.makeElement(l_tagName_Node), p_attributeName);
520 end if;
521 return x_isNull;
522 exception
523 when OTHERS then
524 return x_isNull;
525
526 end getAttributeValue;
527
528
529
530 Function getDateValue(
531 commitNode in xmldom.DOMNode,
532 attributeName in VARCHAR2,
533 gmisc_value in date default hr_api.g_date)
534 return DATE IS
535 l_date DATE;
536 l_isNull VARCHAR2(10);
537 l_string VARCHAR2(100);
538 l_element xmldom.DOMElement;
539 l_proc varchar2(72) := g_package || 'getDateValue';
540 l_pos number;
541
542 Begin
543 hr_utility.set_location(' Entering:' || l_proc,10);
544
545 xslprocessor.valueof(commitNode,attributeName,l_string);
546 l_pos := instr(l_string, ' ', 1);
547 if(l_pos <> 0) then
548 l_string := substr(l_string,1,l_pos-1);
549 end if;
550 l_date := TO_DATE(l_string,'RRRR-MM-DD');
551 l_element := xmldom.makeElement(commitNode);
552 --l_isNull := xmldom.getAttribute(l_element, 'null');
553 l_isNull := getAttributeValue (commitNode,attributeName,'null');
554 if l_isNull = 'true' then
555 l_date := NULL;
556 else
557 l_date := NVL(l_date, gmisc_value);
558 end if;
559 hr_utility.set_location(' Exiting :' || l_proc,15);
560 return l_date;
561 End getDateValue;
562
563 Function getVarchar2Value(
564 commitNode in xmldom.DOMNode,
565 attributeName in VARCHAR2,
566 gmisc_value in varchar2 default hr_api.g_varchar2)
567 return varchar2 IS
568 l_varchar2 VARCHAR2(4000);
569 l_isNull VARCHAR2(10);
570 l_element xmldom.DOMElement;
571 l_proc varchar2(72) := g_package || 'getVarchar2Value';
572 Begin
573 hr_utility.set_location(' Entering:' || l_proc,10);
574 xslprocessor.valueof(commitNode,attributeName,l_varchar2);
575 l_element := xmldom.makeElement(commitNode);
576 -- l_isNull := xmldom.getAttribute(l_element, 'null');
577 l_isNull := getAttributeValue (commitNode,attributeName,'null');
578 if l_isNull = 'true' then
579 l_varchar2 := NULL;
580 else
581 l_varchar2 := NVL(l_varchar2, gmisc_value);
582 end if;
583 hr_utility.set_location(' Exiting :' || l_proc,15);
584 return l_varchar2;
585 End getVarchar2Value;
586
587 Function getNumberValue(
588 commitNode in xmldom.DOMNode,
589 attributeName in VARCHAR2,
590 gmisc_value in number default hr_api.g_number)
591 return NUMBER IS
592 l_number NUMBER;
593 l_isNull VARCHAR2(22);
594 l_element xmldom.DOMElement;
595 l_proc varchar2(72) := g_package || 'getNumberValue';
596 Begin
597 hr_utility.set_location(' Entering:' || l_proc,10);
598 xslprocessor.valueof(commitNode,attributeName,l_number);
599 l_element := xmldom.makeElement(commitNode);
600 -- l_isNull := xmldom.getAttribute(l_element, 'null');
601 l_isNull := getAttributeValue (commitNode,attributeName,'null');
602 if l_isNull = 'true' then
603 l_number := NULL;
604 else
605 l_number := NVL(l_number, gmisc_value);
606 end if;
607 hr_utility.set_location(' Exiting :' || l_proc,15);
608 return l_number;
609 End getNumberValue;
610
611 Function get_EO_api_name(
612 p_EO_Name in varchar2)
613 return varchar2 is
614 x_EO_ApiName varchar2(100);
615 Begin
616 x_EO_ApiName :=null;
617
618 if p_EO_Name = g_current_EO_name then
619 x_EO_ApiName := g_current_EO_ApiName;
620 else
621 -- Go into searching the parallel Arrays only when
622 -- g_current_EO_name does not match the p_EO_Name
623 for i in 1..g_EO_Name_map.count loop
624 if g_EO_Name_map(i) = p_EO_Name then
625 -- When the EO Name matches return the API Name from the corresponding
626 -- Parallel Associative Array => g_EO_ApiName_map
627 x_EO_ApiName:= g_EO_ApiName_map(i);
628 -- Store the match in the package level EO Name , EO Api Name varibales
629 -- So that if the next request too is for the same EO we neednot iterate
630 -- through the associative Arrays.
631 g_current_EO_name := p_EO_Name;
632 g_current_EO_ApiName := x_EO_ApiName;
633 exit;
634 end if;
635 end loop;
636 end if ;
637 -- The value in x_EO_ApiName would be returned.
638 return x_EO_ApiName;
639 End get_EO_api_name;
640
641
642
643 Function process_api_internal(
644 p_transaction_id in number,
645 p_root_node in xmldom.DOMNode,
646 p_validate in number default hr_api.g_false_num,
647 p_effective_date in DATE,
648 p_return_status in varchar2)
649 return VARCHAR2 IS
650
651 cursor csr_hat_steps(p_Api_Name in varchar2) is
652 select *
653 from hr_api_transaction_steps
654 where transaction_id=p_transaction_id
655 and API_NAME = p_Api_Name
656 and OBJECT_TYPE is null;
657
658 l_procapi_retstat_out VARCHAR2(1);
659 l_EO_Name varchar2(1000);
660 l_sqlbuf varchar2(1000);
661 l_EO_api_name varchar2(100);
662 l_CEO_Node_Element xmldom.DOMElement;
663 l_EORowNode xmldom.DOMNode;
664 l_child_EO_Node xmldom.DOMNode;
665 l_CEO_NodeList xmldom.DOMNodeList;
666 l_child_EO_NodeList xmldom.DOMNodeList;
667 -- l_CLOB CLOB;
668 x_current_status varchar2(1);
669 l_proc varchar2(72) := g_package || 'process_api_internal';
670 l_CDATANode xmldom.DOMNode;
671
672 Begin
673 hr_utility.set_location(' Entering:' || l_proc,10);
674
675 -- Get the ApiName fromt he EO node
676 l_EO_Name := xmldom.getAttribute(xmldom.makeElement(p_root_node),'Name');
677 --l_EO_api_name := NULL; --g_api_map(l_EO_Name);
678 l_EO_api_name := get_EO_api_name(l_EO_Name);
679 if l_EO_api_name is not NULL then
680 -- From the Root Node Get the EORow Node,which is a sibiling to the CDATA node
681 l_EORowNode := xmldom.getNextSibling(xmldom.getFirstChild(p_root_node));
682 -- Set the Return Status to the default value
683 l_procapi_retstat_out := 'S';
684 -- Set the return value to the return value got from the calling funtion
685 x_current_status := p_return_status;
686
687 -- set the global g_processing_EO_name for error logging
688 g_processing_EO_name :=l_EO_Name;
689 -- From the Root Node Get the EORow Node,which is a sibiling to the CDATA node
690 l_CDATANode := xmldom.getFirstChild(p_root_node);
691 g_processing_EO_cdatavalue := xmldom.getNodeValue(l_CDATANode);
692
693 -- Make a CLOB out of the root_node for binding purposes
694 hr_utility.set_location('Making the CLOB:' || l_proc,15);
695 DBMS_LOB.createTemporary(g_tempClob, FALSE);
696 -- DBMS_LOB.createTemporary(g_tempClob, FALSE);
697 xmldom.writeToClob(l_EORowNode,g_tempClob);
698
699 hr_utility.set_location('Building the Dynamic Procedure call:' || l_proc,20);
700 -- if l_EO_api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API' or l_EO_api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API' then
701 -- open csr_hat_steps(l_EO_api_name);
702 -- if csr_hat_steps%NOTFOUND then
703 l_sqlbuf:= 'begin ' || l_EO_api_name
704 || ' (p_document => :1 '
705 || ' ,p_return_status => :2 '
706 || ' ,p_validate => :3 '
707 || ' ,p_effective_date => :4); end; ';
708 EXECUTE IMMEDIATE l_sqlbuf using in g_tempClob, out l_procapi_retstat_out, in p_validate, in p_effective_date ;
709 -- else
710 -- l_procapi_retstat_out := 'S';
711 -- end if;
712 -- close csr_hat_steps;
713 -- end if; -- End of checking if EOAPI Name is person/asg
714
715 -- Free the CLOB
716 -- Make the call to set_status to set the current_return Status
717 x_current_status:=set_status(x_current_status,l_procapi_retstat_out);
718
719 DBMS_LOB.freetemporary(g_tempClob);
720
721 IF(l_procapi_retstat_out = 'E') THEN
722 hr_utility.set_location(' Error processing with api call '||l_EO_api_name || l_proc,50);
723 RAISE g_process_api_internal_error;
724 END IF;
725
726 hr_utility.set_location(' Convert RowNode into Element:' || l_proc,20);
727 -- Convert RowNode into Element and get the list of Child EO Nodes if any
728 l_CEO_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(l_EORowNode),'CEO');
729
730 if (xmldom.getLength(l_CEO_NodeList) > 0) then
731 hr_utility.set_location('Child Nodes Exist :' || l_proc,25);
732 l_CEO_Node_Element :=xmldom.makeElement(xmldom.item(l_CEO_NodeList,0));
733 l_child_EO_NodeList :=xmldom.getChildrenByTagName(l_CEO_Node_Element,'EO');
734
735 hr_utility.set_location('Entering For Loop for Child Nodes :' || l_proc,30);
736 for i in 1..xmldom.getLength(l_child_EO_NodeList) loop
737 l_child_EO_Node := xmldom.item(l_child_EO_NodeList,i-1);
738 x_current_status:=process_api_internal(p_transaction_id, l_child_EO_Node,p_validate,p_effective_date,x_current_status);
739 end loop;
740 hr_utility.set_location('End of For Loop :' || l_proc,35);
741 end if;
742 end if; -- if EO API NAME IS NOT NULL
743 hr_utility.set_location('Exiting:' || l_proc,40);
744 return x_current_status;
745 End process_api_internal;
746
747
748
749 Function process_api_call(
750 p_transaction_step_id in NUMBER,
751 p_api_name in VARCHAR2,
752 p_root_node in xmldom.DOMNode,
753 p_validate in number default hr_api.g_false_num,
754 p_effective_date in DATE,
755 p_return_status in varchar2)
756 return VARCHAR2 IS
757 l_procapi_retstat_out VARCHAR2(1);
758 l_sqlbuf varchar2(1000);
759 l_EO_api_name varchar2(100);
760 l_EORowNode xmldom.DOMNode;
761 -- l_CLOB CLOB;
762 x_current_status varchar2(1);
763 l_proc varchar2(72) := g_package || 'process_api_call';
764
765 Begin
766 hr_utility.set_location(' Entering:' || l_proc,10);
767
768 -- Get the ApiName from the parameter
769 l_EO_api_name := p_api_name;
770 if(xmlDOM.isNull(p_root_node)=false) then
771 -- From the Root Node Get the EORow Node,which is a sibiling to the CDATA node
772 l_EORowNode := xmldom.getNextSibling(xmldom.getFirstChild(p_root_node));
773 end if;
774 -- Set the Return Status to the default value
775 l_procapi_retstat_out := 'N';
776 -- Set the return value to the return value got from the calling funtion
777 x_current_status := p_return_status;
778
779
780 -- Make a CLOB out of the root_node for binding purposes
781 if(xmlDOM.isNull(l_EORowNode)=false) then
782 hr_utility.set_location('Making the CLOB:' || l_proc,15);
783 DBMS_LOB.createTemporary(g_tempClob, FALSE);
784 xmldom.writeToClob(l_EORowNode,g_tempClob);
785 end if;
786
787
788 hr_utility.set_location('Building the Dynamic Procedure call:' || l_proc,20);
789 if (xmldom.isNull(p_root_node)=false) then
790 l_sqlbuf:= 'begin ' || l_EO_api_name ||
791 '(p_transaction_step_id => :1
792 ,p_document => :2
793 ,p_return_status => :3
794 ,p_validate => :4
795 ,p_effective_date => :5); end;';
796 EXECUTE IMMEDIATE l_sqlbuf using in p_transaction_step_id, in g_tempClob, out l_procapi_retstat_out, in p_validate, in p_effective_date ;
797 else
798 l_sqlbuf:= 'begin ' || l_EO_api_name ||
799 '(p_transaction_step_id => :1
800 ,p_return_status => :2
801 ,p_validate => :3
802 ,p_effective_date => :4); end;';
803 EXECUTE IMMEDIATE l_sqlbuf using in p_transaction_step_id, out l_procapi_retstat_out, in p_validate, in p_effective_date ;
804 DBMS_LOB.freetemporary(g_tempClob);
805 end if;
806
807 -- Free the CLOB
808 -- Make the call to set_status to set the current_return Status
809 x_current_status:=set_status(x_current_status,l_procapi_retstat_out);
810
811 hr_utility.set_location('Exiting:' || l_proc,25);
812 return x_current_status;
813
814 End process_api_call;
815
816 Function set_status(
817 p_curent_status in VARCHAR2,
818 p_dyn_sql_processapi_sts in VARCHAR2)
819 return VARCHAR2 IS
820 x_return_status varchar2(1);
821 l_proc varchar2(72) := g_package || 'set_status';
822 Begin
823 hr_utility.set_location(' Entering:' || l_proc,10);
824
825 x_return_status:=p_curent_status;
826 if (p_dyn_sql_processapi_sts = 'E') then
827 x_return_status := 'E';
828 elsif (p_dyn_sql_processapi_sts = 'W' and NVL(p_curent_status,'W') <> 'E') then
829 x_return_status := 'W';
830 end if;
831 hr_utility.set_location(' Exiting :' || l_proc,15);
832
833 return x_return_status;
834
835 End set_status;
836
837
838 procedure set_transaction_context(
839 p_transaction_id in number)
840 IS
841 cursor csr_hat is
842 select hat.transaction_id,
843 hat.creator_person_id,
844 hat.status,
845 hat.function_id,
846 hat.transaction_ref_table,
847 hat.transaction_ref_id,
848 hat.transaction_type,
849 hat.assignment_id,
850 hat.selected_person_id,
851 hat.item_type,
852 hat.item_key,
853 hat.transaction_effective_date,
854 hat.process_name,
855 hat.transaction_state,
856 hat.effective_date_option
857 from hr_api_transactions hat
858 where hat.transaction_id =p_transaction_id;
859 step_row csr_hat%rowtype;
860 l_proc varchar2(72) := g_package || 'set_transaction_context';
861 Begin
862
863 hr_utility.set_location(' Entering:' || l_proc,10);
864 hr_utility.set_location(' Opening Cursor:csr_hat' || l_proc,15);
865 g_txn_ctx := null;
866 open csr_hat;
867 fetch csr_hat into step_row;
868 close csr_hat;
869 hr_utility.set_location(' Closing Cursor:csr_hat' || l_proc,20);
870
871 -- Set the Individual fields on the Global Transaction Context Record
872 hr_utility.set_location(' Setting GlobalTxnCtx' || l_proc,25);
873 g_txn_ctx.TRANSACTION_ID :=step_row.TRANSACTION_ID;
874 g_txn_ctx.CREATOR_PERSON_ID :=step_row.CREATOR_PERSON_ID;
875 g_txn_ctx.STATUS :=step_row.STATUS;
876 g_txn_ctx.FUNCTION_ID :=step_row.FUNCTION_ID;
877 g_txn_ctx.TRANSACTION_REF_TABLE :=step_row.TRANSACTION_REF_TABLE;
878 g_txn_ctx.TRANSACTION_REF_ID :=step_row.TRANSACTION_REF_ID;
879 g_txn_ctx.TRANSACTION_TYPE :=step_row.TRANSACTION_TYPE;
880 g_txn_ctx.ASSIGNMENT_ID :=step_row.ASSIGNMENT_ID;
881 g_txn_ctx.SELECTED_PERSON_ID :=step_row.SELECTED_PERSON_ID;
882 g_txn_ctx.ITEM_TYPE :=step_row.ITEM_TYPE;
883 g_txn_ctx.ITEM_KEY :=step_row.ITEM_KEY;
884 g_txn_ctx.PROCESS_NAME :=step_row.PROCESS_NAME;
885 g_txn_ctx.TRANSACTION_STATE :=step_row.TRANSACTION_STATE;
886 g_txn_ctx.EFFECTIVE_DATE_OPTION :=step_row.EFFECTIVE_DATE_OPTION;
887
888 if (step_row.EFFECTIVE_DATE_OPTION = 'A') then
889 g_txn_ctx.EFFECTIVE_DATE := trunc(SYSDATE);
890 else
891 g_txn_ctx.EFFECTIVE_DATE := nvl(step_row.TRANSACTION_EFFECTIVE_DATE, trunc(sysdate));
892 end if;
893 hr_utility.set_location(' Completd Setting GlobalTxnCtx' || l_proc,30);
894 hr_utility.set_location(' Exiting :' || l_proc,35);
895
896
897 end set_transaction_context;
898
899
900 procedure set_person_context(
901 p_selected_person_id in number,
902 p_selected_assignment_id in number,
903 p_effective_date in DATE)
904 IS
905
906 cursor csr_person_details is
907 select ppf.full_name,
908 ppf.person_id,
909 ppf.employee_number,
910 ppf.npw_number,
911 decode(ppf.current_employee_flag, 'Y', 'Y',decode(ppf.current_npw_flag,'Y','Y'),'N') active,
912 paf.assignment_id,
913 paf.assignment_number,
914 paf.assignment_type,
915 paf.primary_flag,
916 paf.supervisor_id,
917 sup.full_name supervisor_name,
918 ppf.business_group_id,
919 paf.organization_id,
920 bustl.name business_group_name,
921 orgtl.name organization_name,
922 paf.job_id,
923 jtl.name job_name,
924 paf.position_id,
925 postl.name position_name,
926 oi.org_information10 currency_code,
927 oi.org_information2 employee_number_generation,
928 oi.org_information3 applicant_number_generation,
929 oi.org_information16 npw_number_generation,
930 oi.org_information9 legislation_code,
931 fs.id_flex_structure_code people_grp_f_struct_code,
932 oi.org_information14 security_group_id,
933 paf.location_id,
934 paf.payroll_id
935
936 from per_all_people_f ppf,
937 per_all_assignments_f paf,
938 hr_all_organization_units_tl bustl,
939 hr_all_organization_units_tl orgtl,
940 per_jobs_tl jtl,
941 hr_all_positions_f_tl postl,
942 per_all_people_f sup,
943 hr_organization_information oi,
944 fnd_id_flex_structures fs
945
946 where ppf.person_id = p_selected_person_id
947 and ppf.person_id = paf.person_id
948 and paf.assignment_id = nvl(p_selected_assignment_id, paf.assignment_id)
949 and paf.assignment_type in ('E','C','A')
950 and paf.primary_flag = decode (nvl(p_selected_assignment_id, -1),-1,'Y', paf.primary_flag)
951 and paf.supervisor_id = sup.person_id(+)
952 and ppf.business_group_id = oi.organization_id
953 and oi.org_information_context = 'Business Group Information'
954 and oi.org_information5 = fs.id_flex_num(+)
955 and fs.id_flex_code(+) = 'GRP'
956 and fs.application_id(+) = 801
957 and ppf.business_group_id = bustl.organization_id
958 and bustl.language = userenv('LANG')
959 and paf.organization_id = orgtl.organization_id
960 and orgtl.language = userenv('LANG')
961 and paf.job_id = jtl.job_id(+)
962 and jtl.language(+) = userenv('LANG')
963 and paf.position_id = postl.position_id(+)
964 and postl.language(+) = userenv('LANG')
965 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
966 and p_effective_date between paf.effective_start_date and paf.effective_end_date
967 and p_effective_date between sup.effective_start_date(+) and sup.effective_end_date(+);
968
969 step_row csr_person_details%rowtype;
970 l_proc varchar2(72) := g_package || 'set_person_context';
971 l_orgid number;
972 Begin
973 hr_utility.set_location(' Entering:' || l_proc,10);
974 hr_utility.set_location(' Opening Cursor:csr_person_details' || l_proc,15);
975
976 if p_selected_person_id is not null AND p_selected_assignment_id is not null AND p_effective_date is not null then
977 open csr_person_details;
978 fetch csr_person_details into step_row;
979 close csr_person_details;
980 hr_utility.set_location(' Closing Cursor:csr_person_details' || l_proc,20);
981 -- Set the Individual fields on the Global Person Record
982 hr_utility.set_location('Setting the GlobalPersonRecord:' || l_proc,25);
983
984 g_person_ctx.FULL_NAME :=step_row.FULL_NAME;
985 g_person_ctx.PERSON_ID :=step_row.PERSON_ID;
986 g_person_ctx.EMPLOYEE_NUMBER :=step_row.EMPLOYEE_NUMBER;
987 g_person_ctx.NPW_NUMBER :=step_row.NPW_NUMBER;
988 g_person_ctx.ACTIVE :=step_row.ACTIVE;
989 g_person_ctx.ASSIGNMENT_ID :=step_row.ASSIGNMENT_ID;
990 g_person_ctx.ASSIGNMENT_NUMBER :=step_row.ASSIGNMENT_NUMBER;
991 g_person_ctx.ASSIGNMENT_TYPE :=step_row.ASSIGNMENT_TYPE;
992 g_person_ctx.PRIMARY_FLAG :=step_row.PRIMARY_FLAG;
993 g_person_ctx.SUPERVISOR_ID :=step_row.SUPERVISOR_ID;
994 g_person_ctx.SUPERVISOR_NAME :=step_row.SUPERVISOR_NAME;
995 g_person_ctx.BUSINESS_GROUP_ID :=step_row.BUSINESS_GROUP_ID;
996 g_person_ctx.ORGANIZATION_ID :=step_row.ORGANIZATION_ID;
997 g_person_ctx.BUSINESS_GROUP_NAME :=step_row.BUSINESS_GROUP_NAME;
998 g_person_ctx.ORGANIZATION_NAME :=step_row.ORGANIZATION_NAME;
999 g_person_ctx.JOB_ID :=step_row.JOB_ID;
1000 g_person_ctx.JOB_NAME :=step_row.JOB_NAME;
1001 g_person_ctx.POSITION_ID :=step_row.POSITION_ID;
1002 g_person_ctx.POSITION_NAME :=step_row.POSITION_NAME;
1003 g_person_ctx.LOCATION_ID :=step_row.LOCATION_ID;
1004 g_person_ctx.CURRENCY_CODE :=step_row.CURRENCY_CODE;
1005 g_person_ctx.EMPLOYEE_NUMBER_GENERATION :=step_row.EMPLOYEE_NUMBER_GENERATION;
1006 g_person_ctx.APPLICANT_NUMBER_GENERATION:=step_row.APPLICANT_NUMBER_GENERATION;
1007 g_person_ctx.NPW_NUMBER_GENERATION :=step_row.NPW_NUMBER_GENERATION;
1008 g_person_ctx.LEGISLATION_CODE :=step_row.LEGISLATION_CODE;
1009 g_person_ctx.PEOPLE_GRP_F_STRUCT_CODE :=step_row.PEOPLE_GRP_F_STRUCT_CODE;
1010 g_person_ctx.SECURITY_GROUP_ID :=step_row.SECURITY_GROUP_ID;
1011 g_person_ctx.PAYROLL_ID :=step_row.PAYROLL_ID;
1012
1013 init_profiles( p_person_id => g_person_ctx.PERSON_ID,
1014 p_assignment_id => g_person_ctx.ASSIGNMENT_ID,
1015 p_business_group_Id => g_person_ctx.BUSINESS_GROUP_ID,
1016 p_organization_Id => g_person_ctx.ORGANIZATION_ID,
1017 p_location_id => g_person_ctx.LOCATION_ID,
1018 p_payroll_id => g_person_ctx.PAYROLL_ID
1019 );
1020
1021 -- HRMS BPO Enhancement changes,for bug 7501793
1022 l_orgid := step_row.business_group_id;
1023 if hr_multi_tenancy_pkg.is_multi_tenant_system then
1024 l_orgid := hr_multi_tenancy_pkg.get_org_id_for_person(step_row.person_id);
1025 end if;
1026
1027 hr_util_misc_ss.set_sys_ctx(step_row.legislation_code, l_orgid);
1028 end if;
1029
1030 hr_utility.set_location('Set values on Global Person Record:' || l_proc,30);
1031 hr_utility.set_location(' Exiting :' || l_proc,35);
1032
1033 end set_person_context;
1034
1035 procedure init_profiles(
1036 p_person_id in number,
1037 p_assignment_id in Number,
1038 p_business_group_Id in Number,
1039 p_organization_Id in Number,
1040 p_location_id in Number,
1041 p_payroll_id in number
1042 )
1043 IS
1044 l_proc varchar2(72) := g_package || 'init_profiles';
1045 Begin
1046
1047 hr_utility.set_location(' Entering:' || l_proc,10);
1048 hr_utility.set_location(' Setting Profile values:' || l_proc,15);
1049
1050 fnd_profile.put('PER_PERSON_ID', p_person_id);
1051 fnd_profile.put('PER_ASSIGNMENT_ID', p_assignment_id);
1052 fnd_profile.put('PER_BUSINESS_GROUP_ID', p_business_group_Id);
1053 fnd_profile.put('PER_ORGANIZATION_ID', p_organization_Id);
1054 fnd_profile.put('PER_LOCATION_ID', p_location_id);
1055 fnd_profile.put('PER_PAYROLL_ID', p_payroll_id);
1056
1057 hr_utility.set_location(' Exiting :' || l_proc,20);
1058
1059 end init_profiles;
1060
1061
1062 procedure delete_transaction(
1063 p_transaction_id in NUMBER,
1064 p_validate in NUMBER default hr_api.g_false_num)
1065 is
1066 l_proc varchar2(72) := g_package || 'delete_transaction';
1067 begin
1068 hr_utility.set_location(' Entering:' || l_proc,10);
1069 hr_utility.set_location(' Calling:hr_transaction_api.rollback_transaction' || l_proc,15);
1070 delete_transaction_children(p_transaction_id, p_validate);
1071 hr_sflutil_ss.closesflnotifications(p_transaction_id,null,null);
1072 hr_transaction_api.rollback_transaction(
1073 p_transaction_id => p_transaction_id,
1074 p_validate => (p_validate=hr_api.g_true_num));
1075 hr_utility.set_location(' Exiting :' || l_proc,20);
1076 end delete_transaction;
1077
1078 function convertCLOBtoXMLElement(
1079 p_document in CLOB)
1080 return xmldom.DOMElement is
1081 x_commitElement xmldom.DOMElement;
1082 l_parser xmlparser.Parser;
1083 l_proc varchar2(72) := g_package || 'convertCLOBtoXMLElement';
1084 Begin
1085 hr_utility.set_location(' Entering:' || l_proc,10);
1086 hr_utility.set_location(' CLOB --> xmldom.DOMElement:' || l_proc,15);
1087 -- CLOB --> xmldom.DOMElement
1088 l_parser := xmlparser.newParser;
1089 xmlparser.ParseCLOB(l_parser,p_document);
1090 x_commitElement := xmldom.getDocumentElement(xmlparser.getDocument(l_parser));
1091 return x_commitElement;
1092 end convertCLOBtoXMLElement;
1093
1094 procedure setTransactionStatus(
1095 p_transaction_id in NUMBER,
1096 p_transaction_ref_table in varchar2,
1097 p_currentTxnStatus in varchar2,
1098 p_proposedTxnStatus in varchar2,
1099 p_propagateMessagePub in number,
1100 p_status out nocopy varchar2)
1101 IS
1102 --
1103 PRAGMA AUTONOMOUS_TRANSACTION;
1104 --
1105 -- local variables
1106 c_proc constant varchar2(30) := 'setTransactionStatus';
1107 c_updateStatus hr_api_transactions.status%type;
1108 ln_notification_id wf_notifications.notification_id%type;
1109 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1110
1111 begin
1112 -- check if debug enabled
1113 if g_debug then
1114 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1115 end if;
1116
1117 -- call the overloaded setTransactionStatus with null comments
1118 setTransactionStatus(p_transaction_id,null,
1119 p_transaction_ref_table,
1120 p_currentTxnStatus,
1121 p_proposedTxnStatus,
1122 p_propagateMessagePub,
1123 p_status);
1124
1125 if g_debug then
1126 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1127 end if;
1128
1129 exception
1130 when others then
1131 -- return error status
1132 p_status := 'E';
1133 end setTransactionStatus;
1134
1135
1136 function isFYINtf(p_ntfId in number)
1137 return boolean IS
1138
1139 l_exists char(1);
1140 begin
1141 l_exists := 'N';
1142 select 'Y' into l_exists
1143 from dual
1144 where exists (select 'e'
1145 from wf_notifications wn, wf_message_attributes mat
1146 where wn.notification_id = p_ntfId
1147 and wn.message_name = mat.message_name
1148 and wn.message_type = mat.message_type
1149 and mat.name = 'RESULT');
1150
1151 return false;
1152 if(l_exists is not null and l_exists='Y') then
1153 return false;
1154 else
1155 return true;
1156 end if;
1157 Exception when others then
1158 return true;
1159 end isFYINtf;
1160
1161 function isEditAllowed(p_transaction_id in number,
1162 p_transaction_status in varchar2,
1163 p_notification_id in number,
1164 p_authenticateNtf in number,
1165 p_loginPersonId in number,
1166 p_loginPersonBgId in number,
1167 p_propagateMessagePub in number)
1168 return varchar2
1169 is
1170 -- local variables
1171 editAllowed varchar2(1);
1172 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1173 l_authenticateNtf boolean;
1174 begin
1175
1176 -- check if we need to propagate the error messages
1177 if(p_propagateMessagePub=hr_api.g_true_num) then
1178 hr_multi_message.enable_message_list;
1179 end if;
1180
1181 -- set the default return satatus
1182 editAllowed :='N';
1183
1184 -- set the default ntf authentication
1185 if((p_authenticateNtf is null) or (p_authenticateNtf=hr_api.g_true_num)) then
1186 l_authenticateNtf := true;
1187 else
1188 l_authenticateNtf := false;
1189 end if;
1190
1191
1192 if(p_notification_id is not null) then
1193 -- check if notification is open and user has access to the notification
1194 if not hr_sflutil_ss.OpenNotificationsExist(p_notification_id) then
1195 -- raise exception ... need to change
1196 if(hr_multi_message.is_message_list_enabled) then
1197 --HRSSA_TRANSACTION_COMPLETE
1198 --This notification is not available as it has already been completed
1199 --and closed.
1200 fnd_message.set_name(800,'HRSSA_TRANSACTION_COMPLETE');
1201 hr_multi_message.add(null,null,null,null,null,'N',hr_multi_message.g_error_msg);
1202 end if;
1203 editAllowed :='E';
1204 return editAllowed;
1205 end if;
1206 -- authenticate login user access to the notifcation id
1207 if(l_authenticateNtf and (wf_advanced_worklist.authenticate(fnd_global.user_name,
1208 p_notification_id,null)
1209 <>fnd_global.user_name)) then
1210
1211 -- no more iterations return false
1212 if(hr_multi_message.is_message_list_enabled) then
1213 hr_multi_message.add(null,null,null,null,null,'N',hr_multi_message.g_error_msg);
1214 end if;
1215 editAllowed :='E';
1216 return editAllowed;
1217 end if;
1218 end if;
1219
1220 if(p_transaction_id is not null) then
1221 -- get the transaction details
1222 begin
1223 select * into lr_hr_api_transaction_rec
1224 from hr_api_transactions
1225 where transaction_id=p_transaction_id;
1226 exception
1227 when others then
1228 editAllowed :='N';
1229 end;
1230
1231 if(p_transaction_status not in ('Y','YS','RO','ROS')
1232 and isTxnOwner(null,lr_hr_api_transaction_rec.creator_person_id)) then
1233 -- it is the creator editing the transaction
1234 editAllowed := 'Y';
1235 return editAllowed;
1236 elsif(p_transaction_status in ('Y','YS','RO','ROS' )) then
1237 -- case where approvers trying to edit
1238
1239 -- check if the login person is the approver
1240 if(fnd_global.user_name=wf_engine.getitemattrtext(lr_hr_api_transaction_rec.item_type,
1241 lr_hr_api_transaction_rec.item_key,
1242 'FORWARD_TO_USERNAME',true)) then
1243 -- check the profile if the system is configured for approvers editing
1244 IF ( nvl(fnd_profile.value('PQH_ALLOW_APPROVER_TO_EDIT_TXN'),'N') = 'Y' ) THEN
1245 -- add specific override logic here
1246 -- case 1: check the programatic overide for the flow
1247 -- wf item attribute HR_RESTRICT_EDIT_ATTR
1248
1249 if(wf_engine.getitemattrtext(lr_hr_api_transaction_rec.item_type,
1250 lr_hr_api_transaction_rec.item_key,
1251 'HR_RESTRICT_EDIT_ATTR',true)='Y') then
1252 editAllowed := 'N';
1253 return editAllowed;
1254 end if;
1255
1256 -- case 2: Functional module layer
1257 -- appraisal specific, not edit allowed for now by approvers
1258 if(lr_hr_api_transaction_rec.transaction_ref_table='PER_APPRAISALS') then
1259 editAllowed := 'N';
1260 -- no more checks return
1261 return editAllowed;
1262 end if;
1263
1264
1265 -- check the if the appover is allowed to edit
1266 pqh_ss_utility.check_edit_privilege (
1267 p_personId => nvl(p_loginPersonId,fnd_global.employee_id)
1268 ,p_businessGroupId => p_loginPersonBgId
1269 ,p_editAllowed => editAllowed);
1270
1271
1272 END IF;-- edit profile check.
1273 end if; -- approver check
1274 end if; -- transaction status check
1275 end if; -- transaction id null check
1276 -- disable the message propagation
1277 IF (p_propagateMessagePub=hr_api.g_true_num) THEN
1278 hr_multi_message.disable_message_list;
1279 END IF;
1280
1281 return editAllowed;
1282
1283 exception
1284 when others then
1285 editAllowed :='N';
1286 return editAllowed;
1287 end isEditAllowed;
1288
1289
1290 function isDeleteAllowed(p_transaction_id in number,
1291 p_transaction_status in varchar2,
1292 p_notification_id in number,
1293 p_authenticateNtf in number,
1294 p_propagateMessagePub in number)
1295 return varchar2
1296 is
1297 -- local variables
1298 deleteAllowed varchar2(1);
1299 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1300 l_authenticateNtf boolean;
1301 begin
1302
1303 -- set default
1304 deleteAllowed :='N';
1305 -- check if we need to propagate the error messages
1306 if(p_propagateMessagePub=hr_api.g_true_num) then
1307 hr_multi_message.enable_message_list;
1308 end if;
1309
1310 -- set the default ntf authentication
1311 if((p_authenticateNtf is null) or (p_authenticateNtf=hr_api.g_true_num)) then
1312 l_authenticateNtf := true;
1313 else
1314 l_authenticateNtf := false;
1315 end if;
1316
1317
1318 if(p_notification_id is not null) then
1319 -- check if notification is open and user has access to the notification
1320 if not hr_sflutil_ss.OpenNotificationsExist(p_notification_id) then
1321 -- raise exception ... need to change
1322 if(hr_multi_message.is_message_list_enabled) then
1323 --HRSSA_TRANSACTION_COMPLETE
1324 --This notification is not available as it has already been completed
1325 --and closed.
1326 fnd_message.set_name(800,'HRSSA_TRANSACTION_COMPLETE');
1327 hr_multi_message.add(null,null,null,null,null,'N',hr_multi_message.g_error_msg);
1328 end if;
1329
1330 deleteAllowed :='E';
1331 return deleteAllowed;
1332 end if;
1333
1334 -- authenticate login user access to the notifcation id
1335 if(l_authenticateNtf and (wf_advanced_worklist.authenticate(fnd_global.user_name,
1336 p_notification_id,null)
1337 <>fnd_global.user_name)) then
1338 -- no more iterations return false
1339 if(hr_multi_message.is_message_list_enabled) then
1340 hr_multi_message.add(null,null,null,null,null,'N',hr_multi_message.g_error_msg);
1341 end if;
1342 deleteAllowed :='E';
1343 return deleteAllowed;
1344 end if;
1345 end if;
1346
1347 if(p_transaction_id is not null) then
1348 -- get the transaction details
1349 begin
1350 select * into lr_hr_api_transaction_rec
1351 from hr_api_transactions
1352 where transaction_id=p_transaction_id;
1353 exception
1354 when others then
1355 deleteAllowed :='N';
1356 end;
1357
1358 if(p_transaction_status not in ('Y','YS','RO','ROS')
1359 and isTxnOwner(null,lr_hr_api_transaction_rec.creator_person_id)) then
1360 -- it is the creator editing the transaction
1361 deleteAllowed := 'Y';
1362 else
1363 deleteAllowed :='N';
1364 end if;
1365
1366 -- disable the message propagation
1367 IF (p_propagateMessagePub=hr_api.g_true_num) THEN
1368 hr_multi_message.disable_message_list;
1369 END IF;
1370 end if;
1371
1372 return deleteAllowed;
1373
1374 exception
1375 when others then
1376 deleteAllowed :='N';
1377 return deleteAllowed;
1378 end isDeleteAllowed;
1379
1380 procedure ownerDeleteAction(p_transaction_id in number,
1381 p_currentTxnStatus in varchar2,
1382 p_transaction_type in varchar2,
1383 p_item_type in varchar2,
1384 p_item_key in varchar2)
1385 is
1386 lv_result varchar2(100);
1387 ln_notification_id wf_notifications.notification_id%type;
1388 ln_activity_id wf_item_activity_statuses.process_activity%type;
1389 begin
1390
1391 -- check on high level if the owner can delete it
1392 -- only case when the pending approvals
1393 if(p_currentTxnStatus in ('Y','YS','RO','ROS')) then
1394 -- raise exception
1395 return;
1396 end if;
1397
1398
1399 if(p_transaction_id is not null) then
1400 -- check if WF based on non-WF based
1401 if(p_item_key is not null) then
1402 -- based on status we need to either abort the wf process
1403 -- and soft delete the txn
1404 -- OR transition the WF process in delete mode
1405 if(p_currentTxnStatus in ('RI','RIS')) then
1406 -- call workflow process in 'DELETE' mode
1407 -- get the notification id and complete it with delete mode
1408
1409 -- get the rfc ntf id
1410 select ias.notification_id,ias.process_activity
1411 into ln_notification_id,ln_activity_id
1412 from wf_item_activity_statuses ias
1413 where ias.item_type = p_item_type
1414 and ias.item_key = p_item_key
1415 and ias.activity_status = 'NOTIFIED'
1416 and notification_id is not null
1417 and rownum < 2;
1418 -- check if the notification id if not throw exception
1419 if(ln_notification_id is not null) then
1420
1421 -- hsundar: Delete any open SFL Notification for this txn
1422 hr_sflutil_ss.closesflnotifications(p_transaction_id,p_item_type,p_item_key);
1423
1424 -- complete ntf with HR_V5_ALL_RESPONSES.del code
1425 /* wf_engine.CompleteActivity(
1426 p_item_Type
1427 , p_item_Key
1428 , wf_engine.getactivitylabel(ln_activity_id)
1429 , 'DEL') ; */
1430 -- fix for bug 5328872
1431 wf_notification.setattrtext(
1432 ln_notification_id
1433 ,'RESULT'
1434 ,'DEL');
1435 wf_notification.respond(
1436 ln_notification_id
1437 ,null
1438 ,fnd_global.user_name
1439 ,null);
1440 else
1441 -- throw exception
1442 null;
1443 end if;
1444 else -- other statuses
1445
1446 -- hsundar: Delete any open SFL Notification for this txn
1447 hr_sflutil_ss.closesflnotifications(p_transaction_id,p_item_type,p_item_key);
1448
1449 hr_transaction_ss.rollback_transaction(p_item_type,
1450 p_item_key,
1451 null,
1452 wf_engine.eng_run,
1453 lv_result);
1454 wf_engine.abortprocess(itemtype => p_item_type
1455 ,itemkey => p_item_key
1456 ,process =>null
1457 ,result => wf_engine.eng_force
1458 ,verify_lock=> true
1459 ,cascade=> true);
1460 end if;
1461 else -- non workflow case
1462 -- soft delete the transaction
1463 hr_transaction_api.update_transaction(
1464 p_transaction_id => p_transaction_id,
1465 p_status => 'D');
1466
1467 -- close all sfl notifications
1468 hr_sflutil_ss.closesflnotifications(p_transaction_id,null,null);
1469 -- do the module specific logic
1470 end if;
1471
1472
1473 else
1474 -- transaction id is null raise error ??
1475 null;
1476 end if;
1477 exception
1478 when others then
1479 raise;
1480 end ownerDeleteAction;
1481
1482 procedure othersDeleteAction(p_transaction_id in number,
1483 p_currentTxnStatus in varchar2,
1484 p_transaction_type in varchar2,
1485 p_item_type in varchar2,
1486 p_item_key in varchar2)
1487 is
1488 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1489 lv_result varchar2(100);
1490 ln_notification_id wf_notifications.notification_id%type;
1491 ln_activity_id wf_item_activity_statuses.process_activity%type;
1492 begin
1493
1494 if(p_currentTxnStatus in ('Y','YS','RO','ROS')) then
1495 -- get the notification activity id and complete
1496 -- the process in reject status
1497 -- get the rfc ntf id
1498 select ias.notification_id,ias.process_activity
1499 into ln_notification_id,ln_activity_id
1500 from wf_item_activity_statuses ias
1501 where ias.item_type = p_item_type
1502 and ias.item_key = p_item_key
1503 and ias.activity_status = 'NOTIFIED'
1504 and notification_id is not null
1505 and rownum < 2;
1506 -- check if we have the notification id
1507 if(ln_notification_id is not null) then
1508
1509 -- hsundar: Delete any open SFL Notification for this txn
1510 hr_sflutil_ss.closesflnotifications(p_transaction_id,p_item_type,p_item_key);
1511
1512 -- complete ntf with HR_V5_ALL_RESPONSES.Reject code
1513 /* wf_engine.CompleteActivity(
1514 p_item_Type
1515 , p_item_Key
1516 , wf_engine.getactivitylabel(ln_activity_id)
1517 , 'REJECTED') ; */
1518 -- fix for bug 5328872
1519 wf_notification.setattrtext(
1520 ln_notification_id
1521 ,'RESULT'
1522 ,'REJECTED');
1523 wf_notification.respond(
1524 ln_notification_id
1525 ,null
1526 ,fnd_global.user_name
1527 ,null);
1528
1529 else
1530 -- throw exception
1531 null;
1532 end if;
1533
1534 else
1535 -- raise exception action cannot be performed
1536 null;
1537 end if;
1538
1539
1540
1541 exception
1542 when others then
1543 raise;
1544 end othersDeleteAction;
1545
1546 procedure deleteAction(p_transaction_id in number)
1547 is
1548 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1549 lv_result varchar2(100);
1550 begin
1551 -- this routine is called from all actions UI
1552 --
1553 -- implementation logic
1554 /*
1555 CurrentStatus Creator Approver
1556 ============ ======= =========
1557 W Soft delete N/A
1558 set status
1559 to 'D'
1560 others no impl Reject txn
1561 in phase I
1562
1563 Phase II we need the txn data rollback and
1564 WF page navigation state reset.
1565
1566 */
1567 if(p_transaction_id is not null) then
1568 begin
1569 select * into lr_hr_api_transaction_rec
1570 from hr_api_transactions
1571 where transaction_id=p_transaction_id;
1572 exception
1573 when others then
1574 raise;
1575 end;
1576
1577 -- check the current owner
1578 if(isTxnOwner(null,lr_hr_api_transaction_rec.creator_person_id)) then
1579 -- do logic specific to creator
1580 ownerDeleteAction(p_transaction_id ,
1581 lr_hr_api_transaction_rec.status,
1582 lr_hr_api_transaction_rec.transaction_type,
1583 lr_hr_api_transaction_rec.item_type,
1584 lr_hr_api_transaction_rec.item_key);
1585 else -- approvers case
1586 othersDeleteAction(p_transaction_id ,
1587 lr_hr_api_transaction_rec.status,
1588 lr_hr_api_transaction_rec.transaction_type,
1589 lr_hr_api_transaction_rec.item_type,
1590 lr_hr_api_transaction_rec.item_key);
1591 end if;-- transaction owner check
1592 end if;-- transaction id check
1593
1594 exception
1595 when others then
1596 null;
1597 end deleteAction;
1598
1599
1600 procedure initiatorDeleteAction(p_transaction_id in number)
1601 is
1602 lv_result varchar2(100);
1603 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1604 begin
1605
1606 if(p_transaction_id is not null) then
1607 -- Read Transaction Details
1608 begin
1609 select * into lr_hr_api_transaction_rec
1610 from hr_api_transactions
1611 where transaction_id=p_transaction_id;
1612 exception
1613 when others then
1614 raise;
1615 end;
1616
1617 -- check if WF based on non-WF based
1618
1619 if(lr_hr_api_transaction_rec.item_type is not null) then
1620 -- WF case.
1621 -- hsundar: Delete any open SFL Notification for this txn
1622 hr_sflutil_ss.closesflnotifications(p_transaction_id
1623 ,lr_hr_api_transaction_rec.item_type
1624 ,lr_hr_api_transaction_rec.item_key);
1625
1626 hr_transaction_ss.rollback_transaction(lr_hr_api_transaction_rec.item_type,
1627 lr_hr_api_transaction_rec.item_key,
1628 null,
1629 wf_engine.eng_run,
1630 lv_result);
1631 wf_engine.abortprocess(itemtype => lr_hr_api_transaction_rec.item_type
1632 ,itemkey => lr_hr_api_transaction_rec.item_key
1633 ,process =>null
1634 ,result => wf_engine.eng_force
1635 ,verify_lock => true
1636 ,cascade => true);
1637 else
1638 -- non WF case
1639 -- soft delete the transaction
1640 hr_transaction_api.update_transaction(
1641 p_transaction_id => p_transaction_id,
1642 p_status => 'D');
1643
1644 -- close all sfl notifications
1645 hr_sflutil_ss.closesflnotifications(p_transaction_id,null,null);
1646 -- do the module specific logic
1647
1648 end if; -- End of 2nd if(lr_hr_api_transaction_rec.item_type is not null)
1649
1650 else
1651 -- transaction id is null raise error ??
1652 null;
1653 end if; -- End of main if(p_transaction_id is not null)
1654
1655 exception
1656 when others then
1657 raise;
1658 end initiatorDeleteAction;
1659
1660
1661
1662
1663 procedure cancelAction(p_transaction_id in number)
1664 is
1665 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1666 begin
1667
1668 if(p_transaction_id is not null) then
1669 begin
1670 select * into lr_hr_api_transaction_rec
1671 from hr_api_transactions
1672 where transaction_id=p_transaction_id;
1673 exception
1674 when others then
1675 null;
1676 end;
1677
1678 -- check the status
1679 if(lr_hr_api_transaction_rec.status='W') then
1680 -- delete the transaction if owner
1681 -- check the current owner
1682 if(isTxnOwner(null,lr_hr_api_transaction_rec.creator_person_id)) then
1683 deleteAction(p_transaction_id);
1684 else
1685 -- raise error ??
1686 -- not a valid call to this action
1687 null;
1688 end if;
1689 else
1690 -- all other status we need to revert the state back to last known good
1691 -- state
1692 hr_trans_history_api.cancel_action(p_transaction_id);
1693 -- Note:
1694 -- This method does not revert the inadvertant wf page flow state
1695 -- the WF activity is handling the transition and based on user action
1696 -- will revert the flow state.
1697
1698 -- call the method to reset the wf pageflow state , if wf is used for page navigation
1699 hr_approval_ss.resetWfPageFlowState(p_transaction_id);
1700
1701 end if;
1702 end if;
1703
1704
1705 exception
1706 when others then
1707 null;
1708 end cancelAction;
1709
1710
1711 function isTxnOwner(p_transaction_id in number,
1712 p_person_id in number) return boolean
1713 is
1714 -- local variables
1715 x_returnStatus boolean;
1716 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1717 ln_person_id number;
1718 begin
1719
1720 -- set the default value
1721 x_returnStatus := false;
1722 ln_person_id := p_person_id;
1723
1724 if(p_transaction_id is not null) then
1725 -- ignore passed personid
1726 -- derive from the transaction details
1727 select hr_api_transactions.creator_person_id
1728 into ln_person_id
1729 from hr_api_transactions
1730 where transaction_id=p_transaction_id;
1731 end if;
1732
1733 --
1734 if(ln_person_id= fnd_global.employee_id) then
1735 x_returnStatus := true;
1736 else
1737 x_returnStatus :=false;
1738 end if;
1739 return x_returnStatus;
1740 exception
1741 when others then
1742 raise;
1743 end isTxnOwner;
1744
1745 procedure delete_transaction_children(
1746 p_transaction_id in NUMBER,
1747 p_validate in NUMBER default hr_api.g_false_num)
1748 is
1749 cursor csr_trn is
1750 select trn.transaction_id
1751 from hr_api_transactions trn
1752 where trn.parent_transaction_id = p_transaction_id;
1753 begin
1754 for csr_row in csr_trn loop
1755 delete_transaction(csr_row.transaction_id);
1756 end loop;
1757 end delete_transaction_children;
1758
1759 FUNCTION commit_transaction(
1760 p_transaction_id IN NUMBER,
1761 p_validate IN NUMBER DEFAULT hr_api.g_false_num,
1762 p_effective_date IN DATE DEFAULT SYSDATE)
1763 RETURN VARCHAR2 IS
1764 l_proc VARCHAR2(72) := g_package || 'commit_transaction';
1765 x_return_status VARCHAR2(1);
1766 p_error_log CLOB;
1767
1768 BEGIN
1769 hr_utility.set_location(' Entering:' || l_proc,10);
1770 commit_transaction(
1771 p_transaction_id=>p_transaction_id,
1772 p_validate =>p_validate,
1773 p_effective_date=>p_effective_date,
1774 p_process_all_on_error =>hr_api.g_false_num,
1775 p_status=>x_return_status,
1776 p_error_log=>p_error_log);
1777 hr_utility.set_location(' Exiting:' || l_proc,20);
1778
1779 RETURN x_return_status;
1780 EXCEPTION
1781 WHEN others THEN
1782 RAISE;
1783 END commit_transaction;
1784
1785 procedure intializeWFApprovals(p_transaction_id IN NUMBER
1786 ,p_item_key out nocopy number
1787 ,p_status out nocopy varchar2) as
1788
1789
1790 lv_item_type wf_items.item_type%type;
1791 lr_transaction_rec hr_api_transactions%rowtype;
1792 lv_status varchar2(8);
1793 lv_result varchar2(30);
1794 lv_errorActid wf_item_activity_statuses.process_activity%type;
1795 lv_errname VARCHAR2(4000);
1796 l_index binary_integer;
1797 l_temp_item_attribute varchar2(2000);
1798 l_role_name wf_roles.name%type;
1799 l_role_displayname wf_roles.display_name%type;
1800 lt_additional_wf_attributes HR_WF_ATTR_TABLE;
1801 lv_error_message varchar2(4000);
1802 lv_errstack varchar2(4000);
1803 lv_ntfSubMsg fnd_new_messages.message_name%type;
1804 lv_relaunchFunc fnd_form_functions.function_name%type;
1805 lv_param_name fnd_form_functions.parameters%type;
1806 lv_approval_required varchar2(5);
1807 lv_ameTransType varchar2(240);
1808 ln_ameTranAppId number;
1809 lv_xpath varchar2(20000) default 'Transaction/TransCtx';
1810 lv_review_template_rn fnd_form_functions.function_name%type;
1811 lv_Ntf_Attach_Attr wf_item_attribute_values.text_value%type;
1812 lv_approval_comments wf_item_attribute_values.text_value%type;
1813 begin
1814
1815 begin
1816 -- call the method to create the workflow approval process
1817 if(p_transaction_id is not null) then
1818 -- get the transaction details
1819 select *
1820 into lr_transaction_rec
1821 from hr_api_transactions
1822 where transaction_id=p_transaction_id;
1823
1824 -- derive the fnd function params values from txn
1825 lv_approval_required := hr_xml_util.get_node_value(p_transaction_id,
1826 'pApprovalReqd',
1827 lv_xpath,
1828 NULL,
1829 NULL,
1830 NULL,
1831 NULL,
1832 NULL,
1833 NULL,
1834 NULL,
1835 NULL,
1836 NULL,
1837 NULL,
1838 NULL);
1839 lv_ntfSubMsg := hr_xml_util.get_node_value(p_transaction_id,
1840 'pNtfSubMsg',
1841 lv_xpath,
1842 NULL,
1843 NULL,
1844 NULL,
1845 NULL,
1846 NULL,
1847 NULL,
1848 NULL,
1849 NULL,
1850 NULL,
1851 NULL,
1852 NULL);
1853 lv_ameTransType := hr_xml_util.get_node_value(p_transaction_id,
1854 'pAMETranType',
1855 lv_xpath,
1856 NULL,
1857 NULL,
1858 NULL,
1859 NULL,
1860 NULL,
1861 NULL,
1862 NULL,
1863 NULL,
1864 NULL,
1865 NULL,
1866 NULL);
1867 ln_ameTranAppId:= hr_xml_util.get_node_value(p_transaction_id,
1868 'pAMEAppId',
1869 lv_xpath,
1870 NULL,
1871 NULL,
1872 NULL,
1873 NULL,
1874 NULL,
1875 NULL,
1876 NULL,
1877 NULL,
1878 NULL,
1879 NULL,
1880 NULL);
1881 lv_review_template_rn := hr_xml_util.get_node_value(p_transaction_id,
1882 'ReviewTemplateRNAttr',
1883 lv_xpath,
1884 NULL,
1885 NULL,
1886 NULL,
1887 NULL,
1888 NULL,
1889 NULL,
1890 NULL,
1891 NULL,
1892 NULL,
1893 NULL,
1894 NULL);
1895 lv_Ntf_Attach_Attr:= hr_xml_util.get_node_value(p_transaction_id,
1896 'NtfAttachAttr',
1897 lv_xpath,
1898 NULL,
1899 NULL,
1900 NULL,
1901 NULL,
1902 NULL,
1903 NULL,
1904 NULL,
1905 NULL,
1906 NULL,
1907 NULL,
1908 NULL);
1909 lt_additional_wf_attributes := HR_WF_ATTR_TABLE(HR_WF_ATTR_TYPE('TRAN_SUBMIT','N',null,null));
1910 hr_approval_ss.startGenericApprovalProcess(p_transaction_id
1911 ,p_item_key
1912 ,lv_ntfSubMsg
1913 ,'HR_RELAUNCH_SS'
1914 ,lt_additional_wf_attributes
1915 ,lv_status
1916 ,lv_error_message
1917 ,lv_errstack
1918 );
1919
1920
1921 -- add check for the error status and raise to bc4j accordingly
1922
1923
1924 -- set additional item attributes
1925 -- HR_OAF_NAVIGATION_ATTR
1926 -- set HR_OAF_EDIT_URL_ATTR
1927 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
1928 ,itemkey => p_item_key
1929 ,aname => 'HR_OAF_EDIT_URL_ATTR'
1930 ,text_value=>'HR_RELAUNCH_SS'
1931 ,number_value=>null,
1932 date_value=>null
1933 );
1934 -- set HR_OAF_NAVIGATION_ATTR
1935 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
1936 ,itemkey => p_item_key
1937 ,aname => 'HR_OAF_NAVIGATION_ATTR'
1938 ,text_value=>'N'
1939 ,number_value=>null,
1940 date_value=>null
1941 );
1942 -- set HR_REVIEW_TEMPLATE_RN_ATTR
1943 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
1944 ,itemkey => p_item_key
1945 ,aname => 'HR_REVIEW_TEMPLATE_RN_ATTR'
1946 ,text_value=>lv_review_template_rn
1947 ,number_value=>null,
1948 date_value=>null
1949 );
1950
1951 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
1952 ,itemkey => p_item_key
1953 ,aname => 'HR_NTF_ATTACHMENTS_ATTR'
1954 ,text_value=>lv_Ntf_Attach_Attr
1955 ,number_value=>null,
1956 date_value=>null
1957 );
1958
1959 -- HR_RESTRICT_RFC_ATTR
1960
1961 -- HR_RESTRICT_EDIT_ATTR
1962
1963 -- APPROVAL_GENERIC_URL
1964
1965 -- HR_RUNTIME_APPROVAL_REQ_FLAG
1966 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
1967 ,itemkey => p_item_key
1968 ,aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG'
1969 ,text_value=>lv_approval_required
1970 ,number_value=>null,
1971 date_value=>null
1972 );
1973 -- set AME params
1974 -- 'HR_AME_APP_ID_ATTR'
1975 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
1976 ,itemkey => p_item_key
1977 ,aname => 'HR_AME_APP_ID_ATTR'
1978 ,text_value=>null
1979 ,number_value=>ln_ameTranAppId
1980 ,date_value=>null
1981 );
1982 -- 'HR_AME_TRAN_TYPE_ATTR'
1983 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
1984 ,itemkey => p_item_key
1985 ,aname => 'HR_AME_TRAN_TYPE_ATTR'
1986 ,text_value=>lv_ameTransType
1987 ,number_value=>null,
1988 date_value=>null
1989 );
1990
1991 -- TRANSACTION_ID
1992 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
1993 ,itemkey => p_item_key
1994 ,aname => 'TRANSACTION_ID'
1995 ,text_value=>null
1996 ,number_value=>p_transaction_id
1997 ,date_value=>null
1998 );
1999
2000 -- TRAN_SUBMIT
2001 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2002 ,itemkey => p_item_key
2003 ,aname => 'TRAN_SUBMIT'
2004 ,text_value=>'Y'
2005 ,number_value=>null
2006 ,date_value=>null
2007 );
2008 -- PROCESS_DISPLAY_NAME
2009 fnd_message.set_name('PER',lv_ntfSubMsg); -- change the hardcoded
2010
2011 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2012 ,itemkey => p_item_key
2013 ,aname => 'PROCESS_DISPLAY_NAME'
2014 ,text_value=>fnd_message.get
2015 ,number_value=>null
2016 ,date_value=>null
2017 );
2018 else
2019 null; -- raise error
2020
2021 end if;
2022 exception
2023 when no_data_found then
2024 raise;
2025 when others then
2026 raise;
2027 end;
2028
2029
2030 end;
2031
2032 procedure setTransactionStatus(
2033 p_transaction_id in NUMBER,
2034 p_approver_comments in varchar2,
2035 p_transaction_ref_table in varchar2,
2036 p_currentTxnStatus in varchar2,
2037 p_proposedTxnStatus in varchar2,
2038 p_propagateMessagePub in number,
2039 p_status out nocopy varchar2)
2040 IS
2041 --
2042 PRAGMA AUTONOMOUS_TRANSACTION;
2043 --
2044 -- local variables
2045 c_proc constant varchar2(30) := 'setTransactionStatus';
2046 c_updateStatus hr_api_transactions.status%type;
2047 ln_notification_id wf_notifications.notification_id%type;
2048 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
2049 lv_item_key wf_items.item_key%type;
2050 lt_additional_wf_attributes HR_WF_ATTR_TABLE;
2051 lv_error_message varchar2(4000);
2052 lv_errstack varchar2(4000);
2053 lv_status varchar2(30); -- revisit on the size
2054 lv_wf_item_attribute HR_WF_ATTR_TYPE;
2055 lv_currentTxnStatus hr_api_transactions.status%type;
2056
2057
2058 begin
2059 -- check if debug enabled
2060 if g_debug then
2061 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2062 end if;
2063 -- check if we need to propagate the error messages
2064 if(p_propagateMessagePub=hr_api.g_true_num) then
2065 hr_multi_message.enable_message_list;
2066 end if;
2067 -- check the proposed status
2068 -- S --> SFL
2069 -- A --> Approval (submit for approval).
2070 if(p_proposedTxnStatus='A')then
2071 -- set the transaction status to 'Y'
2072 c_updateStatus:='Y';
2073 elsif(p_proposedTxnStatus='S')then
2074 begin
2075 select status into lv_currentTxnStatus
2076 from hr_api_transactions
2077 where transaction_id=p_transaction_id;
2078 exception
2079 when others then
2080 null;
2081 end;
2082
2083 c_updateStatus:= hr_sflutil_ss.getSFLStatusForUpdate(
2084 nvl(p_currentTxnStatus,lv_currentTxnStatus),
2085 p_proposedTxnStatus);
2086 else
2087 -- we do not handle other status, return error status
2088 p_status := 'E';
2089 return;
2090 end if;
2091
2092
2093 begin
2094 if(p_proposedTxnStatus='S')then
2095 -- send SFL notification
2096 -- get the transaction record
2097 select *
2098 into lr_hr_api_transaction_rec
2099 from hr_api_transactions
2100 where transaction_id=p_transaction_id;
2101 -- send sfl notification to login user
2102 hr_sflutil_ss.sendsflnotification(p_transaction_id,
2103 p_transaction_ref_table,
2104 fnd_global.user_name,
2105 'HR_RELAUNCH_SS',
2106 null,
2107 ln_notification_id);
2108 -- return success status
2109 p_status := 'S';
2110 -- update the transaction status
2111 hr_transaction_api.update_transaction(
2112 p_transaction_id => p_transaction_id,
2113 p_status => c_updateStatus,
2114 p_transaction_state => null);
2115
2116 -- add the code plugin transfer history
2117 hr_trans_history_api.archive_sfl(p_transaction_id,
2118 ln_notification_id,
2119 fnd_global.user_name);
2120 elsif(p_proposedTxnStatus='A') then
2121
2122
2123 begin
2124 -- check if the wf process is initialized or not
2125 select *
2126 into lr_hr_api_transaction_rec
2127 from hr_api_transactions
2128 where transaction_id=p_transaction_id;
2129
2130 exception
2131 when others then
2132 raise;
2133 end;
2134
2135 if(lr_hr_api_transaction_rec.item_key is not null) then
2136
2137 -- call the code to transition flow in case of approvals
2138 hr_approval_ss.processapprovalsubmit(p_transaction_id,
2139 p_approver_comments);
2140 else
2141
2142 -- intialize the generic approval flow
2143 intializeWFApprovals(p_transaction_id=>p_transaction_id
2144 ,p_item_key =>lv_item_key
2145 ,p_status =>p_status);
2146 if(lv_item_key is null or p_status='E') then
2147 null; -- raise error
2148 else
2149 -- update the transaction with the item key
2150 hr_transaction_api.update_transaction(
2151 p_transaction_id => p_transaction_id,
2152 p_item_key => lv_item_key);
2153
2154 -- complete the wf to send ntf or process commit
2155 hr_approval_ss.processapprovalsubmit(p_transaction_id,
2156 p_approver_comments);
2157
2158 end if;
2159
2160 end if;
2161 else
2162 null;-- do nothing
2163 end if;
2164 exception
2165 when others then
2166 -- return error status
2167 p_status := 'E';
2168 -- propagate the error message
2169
2170 end;
2171 -- disable the message propagation
2172 IF (p_propagateMessagePub=hr_api.g_true_num) THEN
2173 hr_multi_message.disable_message_list;
2174 END IF;
2175
2176 -- finally commit the data
2177 commit;
2178
2179
2180 if g_debug then
2181 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2182 end if;
2183
2184 exception
2185 when others then
2186 -- return error status
2187 p_status := 'E';
2188 end setTransactionStatus;
2189
2190 PROCEDURE createEOErrorMSgNode(doc IN OUT nocopy xmldom.DOMDocument,root_node IN OUT nocopy xmldom.DOMNode)
2191 AS
2192 EO_node xmldom.DOMNode;
2193 message_node xmldom.DOMNode;
2194 item_node xmldom.DOMNode;
2195 msg_count_elmt xmldom.DOMElement;
2196 item_elmt xmldom.DOMElement;
2197 item_text xmldom.DOMText;
2198 msg_count NUMBER;
2199 msg_details VARCHAR2(2000);
2200 p_data VARCHAR2(4000);
2201 p_msg_index_out NUMBER;
2202
2203 BEGIN
2204 msg_count := fnd_msg_pub.Count_Msg;
2205
2206 msg_count_elmt :=xmldom.createElement(doc, 'MsgCount');
2207 xmldom.setAttribute(
2208 msg_count_elmt
2209 , 'Value'
2210 , msg_count
2211 );
2212
2213 EO_node := xmldom.appendChild(root_node,xmldom.makeNode(msg_count_elmt));
2214
2215 FOR i IN 1 .. msg_count LOOP
2216 item_elmt := xmldom.createElement(
2217 doc
2218 , 'Msg'
2219 );
2220
2221 message_node := xmldom.appendChild(
2222 EO_node
2223 , xmldom.makeNode(item_elmt)
2224 );
2225 --
2226 item_elmt := xmldom.createElement(
2227 doc
2228 , 'EncodedMessage'
2229 );
2230 item_node := xmldom.appendChild(
2231 message_node
2232 , xmldom.makeNode(item_elmt)
2233 );
2234 -- get the actual message from fnd_msg_pub
2235 msg_details :=fnd_msg_pub.Get_Detail(p_msg_index=>i,p_encoded=>'F');
2236 item_text := xmldom.createTextNode(
2237 doc
2238 , msg_details
2239 );
2240 item_node := xmldom.appendChild(
2241 item_node
2242 , xmldom.makeNode(item_text)
2243 );
2244 --
2245 END LOOP;
2246
2247 END ;
2248
2249 procedure writeXMLDocToClob(p_error_doc in xmldom.DOMDocument,
2250 p_error_log IN OUT nocopy CLOB)
2251 as
2252 error_log CLOB;
2253 charset VARCHAR2(64);
2254 begin
2255 IF(NOT xmldom.isnull(p_error_doc)) THEN
2256 SELECT v$nls_parameters.value INTO charset FROM v$nls_parameters
2257 WHERE v$nls_parameters.parameter='NLS_CHARACTERSET';
2258 DBMS_LOB.createTemporary(error_log, FALSE);
2259 xmldom.writeToClob(p_error_doc,error_log,charset);
2260 p_error_log:=error_log;
2261 DBMS_LOB.freetemporary(error_log);
2262 xmldom.freeDocument(p_error_doc);
2263 END IF;
2264 end;
2265
2266 PROCEDURE commit_transaction(
2267 p_transaction_id IN NUMBER,
2268 p_validate IN NUMBER DEFAULT hr_api.g_false_num,
2269 p_effective_date IN DATE DEFAULT SYSDATE,
2270 p_process_all_on_error IN NUMBER DEFAULT hr_api.g_false_num,
2271 p_status OUT nocopy VARCHAR2,
2272 p_error_log IN OUT nocopy CLOB)IS
2273 CURSOR csr_trn IS
2274 SELECT transaction_document
2275 FROM hr_api_transactions
2276 WHERE transaction_id = p_transaction_id;
2277
2278 rootNode xmldom.DOMNode;
2279 l_TXN_Node xmldom.DOMNode;
2280 l_AM_Node xmldom.DOMNode;
2281 l_TransCache_Node xmldom.DOMNode;
2282 l_EO_Node xmldom.DOMNode;
2283 l_CDATA_Node xmldom.DOMNode;
2284 l_EoApiMap_Node xmldom.DOMNode;
2285
2286
2287 l_EoApiMap_NodeList xmldom.DOMNodeList;
2288 l_EO_NodeList xmldom.DOMNodeList;
2289 l_TransCache_NodeList xmldom.DOMNodeList;
2290
2291 l_proc VARCHAR2(72) := g_package || 'commit_transaction';
2292 x_return_status VARCHAR2(1);
2293 l_pwac_return_status VARCHAR2(1);
2294 l_language VARCHAR2(2);
2295 l_EO_Api_Name VARCHAR2(1000);
2296
2297 l_EO_Object_Name VARCHAR2(1000);
2298 l_EO_Id VARCHAR2(1000);
2299 l_CDATA_Name VARCHAR2(1000);
2300 l_CDATA_Length NUMBER;
2301
2302 step_row csr_trn%ROWTYPE;
2303
2304 -- error logging
2305 error_doc xmldom.DOMDocument;
2306 error_doc_main_node xmldom.DOMNode;
2307 error_doc_root_elmt xmldom.DOMElement;
2308 error_EO_Node xmldom.DOMNode;
2309 error_temp_Node xmldom.DOMNode;
2310 error_EO_elmt xmldom.DOMElement;
2311 charset VARCHAR2(64);
2312 error_log CLOB;
2313
2314 BEGIN
2315 SAVEPOINT commit_transaction_swi;
2316 hr_utility.set_location(' Entering:' || l_proc,10);
2317 -- Call Set_Transaction_Context
2318 hr_utility.set_location(' Calling set_transaction_context:' || l_proc,15);
2319 set_transaction_context(p_transaction_id);
2320
2321 -- If p_effective_date is not NULL then set it on the g_txn_ctx.EFFECTIVE_DATE
2322 IF ( p_effective_date IS NOT NULL ) THEN
2323 g_txn_ctx.EFFECTIVE_DATE:=p_effective_date;
2324 END IF;
2325 -- Call Set_Person_Context
2326 l_language:='US';
2327 hr_utility.set_location(' Calling set_person_context:' || l_proc,20);
2328 set_person_context( p_selected_person_id => g_txn_ctx.SELECTED_PERSON_ID,
2329 p_selected_assignment_id => g_txn_ctx.ASSIGNMENT_ID,
2330 p_effective_date => g_txn_ctx.EFFECTIVE_DATE);
2331
2332 x_return_status := 'S';
2333 hr_utility.set_location(' Calling :hr_util_misc_ss.seteffectivedate' || l_proc,25);
2334 hr_utility.set_location(' Entering For Loop' || l_proc,35);
2335 -- new code
2336 OPEN csr_trn;
2337 FETCH csr_trn INTO step_row;
2338 -- hsundar: Do the Document processing only when the Txn_document is not null
2339 -- hsundar: If the Document is NULL just return the status as S
2340 IF step_row.transaction_document IS NOT NULL THEN
2341 -- Now get the <Transaction> Node
2342 rootNode := xmldom.makeNode(convertCLOBtoXMLElement(step_row.transaction_document));
2343
2344 -- Now get the <EOApiMap>
2345 l_EoApiMap_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(rootNode),'EoApiMap');
2346 IF (xmldom.getLength(l_EoApiMap_NodeList) > 0) THEN
2347 l_EoApiMap_Node :=xmldom.item(l_EoApiMap_NodeList,0);
2348 l_EO_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(l_EoApiMap_Node),'EO');
2349
2350 -- Put it into a Table
2351 FOR i IN 1..xmldom.getLength(l_EO_NodeList) LOOP
2352 l_EO_Node := xmldom.item(l_EO_NodeList,i-1);
2353 l_EO_Object_Name := xmldom.getAttribute(xmldom.makeElement(l_EO_Node),'Name');
2354 l_EO_Node := xmldom.getFirstChild(l_EO_Node);
2355 l_EO_Api_Name :=xmldom.getNodeValue(l_EO_Node);
2356 --g_api_map(l_EO_Object_Name) := l_EO_Api_Name;
2357 -- Maintain Parallel Arrays
2358 -- 1. Put the EO Name in g_EO_Name_map
2359 g_EO_Name_map(i) := l_EO_Object_Name;
2360 -- 2. Put the EO's API name
2361 g_EO_ApiName_map(i):= l_EO_Api_Name;
2362 END LOOP;
2363
2364
2365 -- Now get the <TransCache> Node
2366 l_TransCache_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(rootNode),'TransCache');
2367 l_TransCache_Node :=xmldom.item(l_TransCache_NodeList,0);
2368 -- Now get the <AM> Node
2369 l_AM_Node :=xmldom.getFirstChild(l_TransCache_Node);
2370 -- Now get the </cd> Node and get its Sibling --> <TXN>
2371 l_TXN_Node :=xmldom.getNextSibling(xmldom.getFirstChild(l_AM_Node));
2372
2373 -- Now get the list of all <EO> Nodes
2374 l_EO_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(l_TXN_Node),'EO');
2375 --
2376 IF (xmldom.getLength(l_EO_NodeList) > 0) THEN
2377 -- Loop for it
2378 FOR i IN 1..xmldom.getLength(l_EO_NodeList) LOOP
2379 l_EO_Node := xmldom.item(l_EO_NodeList,i-1);
2380 l_pwac_return_status := 'S';
2381
2382 BEGIN
2383 l_pwac_return_status:=process_api_internal(
2384 p_transaction_id => p_transaction_id,
2385 p_root_node => l_EO_Node,
2386 p_validate => p_validate, -- 5919836
2387 p_effective_date => p_effective_date,
2388 p_return_status => x_return_status
2389 );
2390 EXCEPTION
2391 WHEN g_process_api_internal_error THEN
2392 x_return_status := set_status(x_return_status,'E');
2393 -- read the fnd msg pub for errors and log them to error doc
2394 IF(xmldom.isnull(error_doc)) THEN -- first error condition
2395 -- create the new empty document
2396 error_doc := xmldom.newDOMDocument;
2397 error_doc_main_node := xmldom.makeNode(error_doc);
2398 -- create the root element to hold txn id
2399 -- error_doc_root_elmt
2400 error_doc_root_elmt:= xmldom.createElement(error_doc, 'Transaction');
2401 xmldom.setAttribute(error_doc_root_elmt,'Id', p_transaction_id);
2402 error_doc_main_node :=xmldom.appendChild(error_doc_main_node, xmldom.makeNode(error_doc_root_elmt));
2403 END IF;
2404
2405 -- add the EO node to the error doc
2406 error_EO_elmt := xmldom.createElement(error_doc, 'EO');
2407 -- need the actual nested EO which has errored and its cdata node
2408 xmldom.setAttribute(error_EO_elmt, 'Name', g_processing_EO_name);
2409 xmldom.setAttribute(error_EO_elmt, 'CDATA', g_processing_EO_cdatavalue);
2410 --
2411 error_EO_Node :=xmldom.makeNode(error_EO_elmt);
2412 createEOErrorMSgNode(error_doc,error_EO_Node);
2413 error_temp_Node:=xmldom.appendChild(error_doc_main_node,error_EO_Node);
2414 -- see if we need to progress on the siblings ?
2415 IF p_process_all_on_error = hr_api.g_false_num THEN
2416 RAISE g_process_api_internal_error;
2417 END IF;
2418
2419 END;
2420 x_return_status := set_status(x_return_status,l_pwac_return_status);
2421 END LOOP;
2422 END IF; -- End of if where we check if we have some EO Nodes
2423 END IF; -- End of if where we check if we have EOAPIMAP Nodes
2424 END IF; -- End of if where we check if the txn_doc is null
2425 CLOSE csr_trn;
2426
2427 writeXMLDocToClob(error_doc ,p_error_log );
2428
2429 hr_utility.set_location(' Exiting For Loop:' || l_proc,40);
2430 IF p_validate = hr_api.g_true_num THEN
2431 hr_utility.set_location(' p_validate=TRUE:' || l_proc,45);
2432 RAISE hr_api.validate_enabled;
2433 END IF;
2434 -- Return the status to the calling procedure
2435 -- hsundar: There is no need to commit as Work-Flow takes care of it implicitly
2436 /*if p_validate = hr_api.g_false_num then
2437 hr_utility.set_location('Commiting as p_validate=FALSE:' || l_proc,50);
2438 COMMIT;
2439 END IF; */
2440
2441 hr_utility.set_location(' Exiting:' || l_proc,55);
2442
2443 --return x_return_status;
2444 p_status :=x_return_status;
2445 -- Moved the exception Block out of the for loop
2446
2447 EXCEPTION
2448 WHEN g_process_api_internal_error THEN
2449 p_status :=x_return_status;
2450 writeXMLDocToClob(error_doc ,p_error_log );
2451 WHEN hr_utility.hr_error THEN
2452 --do something here
2453 hr_utility.set_location('Exception:hr_utility.hr_error' || l_proc,555);
2454 ROLLBACK TO commit_transaction_swi;
2455 RAISE;
2456 WHEN hr_api.validate_enabled THEN
2457 -- As the Validate_Enabled exception has been raised
2458 -- we must rollback to the savepoint
2459 hr_utility.set_location('Exception:hr_api.validate_enabled' || l_proc,560);
2460 ROLLBACK TO commit_transaction_swi;
2461 p_status :=x_return_status;
2462 WHEN others THEN
2463 hr_utility.set_location('Exception:others' || l_proc,565);
2464 ROLLBACK TO commit_transaction_swi;
2465 RAISE;
2466
2467 END commit_transaction;
2468
2469
2470
2471 end hr_transaction_swi;
2472