[Home] [Help]
PACKAGE BODY: APPS.HR_TRANSACTION_SWI
Source
1 PACKAGE BODY HR_TRANSACTION_SWI as
2 /* $Header: hrtrnswi.pkb 120.27.12020000.6 2012/11/29 10:11:56 aammoham 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 --Fix for Bug 7712861
599 --xslprocessor.valueof(commitNode,attributeName,l_number);
600 if(xslprocessor.valueof(commitNode,attributeName) is not NULL) then
601 l_number := fnd_number.canonical_to_number(xslprocessor.valueof(commitNode,attributeName));
602 end if;
603 l_element := xmldom.makeElement(commitNode);
604 -- l_isNull := xmldom.getAttribute(l_element, 'null');
605 l_isNull := getAttributeValue (commitNode,attributeName,'null');
606 if l_isNull = 'true' then
607 l_number := NULL;
608 else
609 l_number := NVL(l_number, gmisc_value);
610 end if;
611 hr_utility.set_location(' Exiting :' || l_proc,15);
612 return l_number;
613 End getNumberValue;
614
615 Function get_EO_api_name(
616 p_EO_Name in varchar2)
617 return varchar2 is
618 x_EO_ApiName varchar2(100);
619 Begin
620 x_EO_ApiName :=null;
621
622 if p_EO_Name = g_current_EO_name then
623 x_EO_ApiName := g_current_EO_ApiName;
624 else
625 -- Go into searching the parallel Arrays only when
626 -- g_current_EO_name does not match the p_EO_Name
627 for i in 1..g_EO_Name_map.count loop
628 if g_EO_Name_map(i) = p_EO_Name then
629 -- When the EO Name matches return the API Name from the corresponding
630 -- Parallel Associative Array => g_EO_ApiName_map
631 x_EO_ApiName:= g_EO_ApiName_map(i);
632 -- Store the match in the package level EO Name , EO Api Name varibales
633 -- So that if the next request too is for the same EO we neednot iterate
634 -- through the associative Arrays.
635 g_current_EO_name := p_EO_Name;
636 g_current_EO_ApiName := x_EO_ApiName;
637 exit;
638 end if;
639 end loop;
640 end if ;
641 -- The value in x_EO_ApiName would be returned.
642 return x_EO_ApiName;
643 End get_EO_api_name;
644
645
646
647 Function process_api_internal(
648 p_transaction_id in number,
649 p_root_node in xmldom.DOMNode,
650 p_validate in number default hr_api.g_false_num,
651 p_effective_date in DATE,
652 p_return_status in varchar2)
653 return VARCHAR2 IS
654
655 cursor csr_hat_steps(p_Api_Name in varchar2) is
656 select *
657 from hr_api_transaction_steps
658 where transaction_id=p_transaction_id
659 and API_NAME = p_Api_Name
660 and OBJECT_TYPE is null;
661
662 l_procapi_retstat_out VARCHAR2(1);
663 l_EO_Name varchar2(1000);
664 l_sqlbuf varchar2(1000);
665 l_EO_api_name varchar2(100);
666 l_CEO_Node_Element xmldom.DOMElement;
667 l_EORowNode xmldom.DOMNode;
668 l_child_EO_Node xmldom.DOMNode;
669 l_CEO_NodeList xmldom.DOMNodeList;
670 l_child_EO_NodeList xmldom.DOMNodeList;
671 -- l_CLOB CLOB;
672 x_current_status varchar2(1);
673 l_proc varchar2(72) := g_package || 'process_api_internal';
674 l_CDATANode xmldom.DOMNode;
675
676 Begin
677 hr_utility.set_location(' Entering:' || l_proc,10);
678
679 -- Get the ApiName fromt he EO node
680 l_EO_Name := xmldom.getAttribute(xmldom.makeElement(p_root_node),'Name');
681 --l_EO_api_name := NULL; --g_api_map(l_EO_Name);
682 l_EO_api_name := get_EO_api_name(l_EO_Name);
683 if l_EO_api_name is not NULL then
684 -- From the Root Node Get the EORow Node,which is a sibiling to the CDATA node
685 l_EORowNode := xmldom.getNextSibling(xmldom.getFirstChild(p_root_node));
686 -- Set the Return Status to the default value
687 l_procapi_retstat_out := 'S';
688 -- Set the return value to the return value got from the calling funtion
689 x_current_status := p_return_status;
690
691 -- set the global g_processing_EO_name for error logging
692 g_processing_EO_name :=l_EO_Name;
693 -- From the Root Node Get the EORow Node,which is a sibiling to the CDATA node
694 l_CDATANode := xmldom.getFirstChild(p_root_node);
695 g_processing_EO_cdatavalue := xmldom.getNodeValue(l_CDATANode);
696
697 -- Make a CLOB out of the root_node for binding purposes
698 hr_utility.set_location('Making the CLOB:' || l_proc,15);
699 DBMS_LOB.createTemporary(g_tempClob, FALSE);
700 -- DBMS_LOB.createTemporary(g_tempClob, FALSE);
701 xmldom.writeToClob(l_EORowNode,g_tempClob);
702
703 hr_utility.set_location('Building the Dynamic Procedure call:' || l_proc,20);
704 -- if l_EO_api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API' or l_EO_api_name = 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API' then
705 -- open csr_hat_steps(l_EO_api_name);
706 -- if csr_hat_steps%NOTFOUND then
707 l_sqlbuf:= 'begin ' || l_EO_api_name
708 || ' (p_document => :1 '
709 || ' ,p_return_status => :2 '
710 || ' ,p_validate => :3 '
711 || ' ,p_effective_date => :4); end; ';
712 EXECUTE IMMEDIATE l_sqlbuf using in g_tempClob, out l_procapi_retstat_out, in p_validate, in p_effective_date ;
713 -- else
714 -- l_procapi_retstat_out := 'S';
715 -- end if;
716 -- close csr_hat_steps;
717 -- end if; -- End of checking if EOAPI Name is person/asg
718
719 -- Free the CLOB
720 -- Make the call to set_status to set the current_return Status
721 x_current_status:=set_status(x_current_status,l_procapi_retstat_out);
722
723 DBMS_LOB.freetemporary(g_tempClob);
724
725 IF(l_procapi_retstat_out = 'E') THEN
726 hr_utility.set_location(' Error processing with api call '||l_EO_api_name || l_proc,50);
727 RAISE g_process_api_internal_error;
728 END IF;
729
730 hr_utility.set_location(' Convert RowNode into Element:' || l_proc,20);
731 -- Convert RowNode into Element and get the list of Child EO Nodes if any
732 l_CEO_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(l_EORowNode),'CEO');
733
734 if (xmldom.getLength(l_CEO_NodeList) > 0) then
735 hr_utility.set_location('Child Nodes Exist :' || l_proc,25);
736 l_CEO_Node_Element :=xmldom.makeElement(xmldom.item(l_CEO_NodeList,0));
737 l_child_EO_NodeList :=xmldom.getChildrenByTagName(l_CEO_Node_Element,'EO');
738
739 hr_utility.set_location('Entering For Loop for Child Nodes :' || l_proc,30);
740 for i in 1..xmldom.getLength(l_child_EO_NodeList) loop
741 l_child_EO_Node := xmldom.item(l_child_EO_NodeList,i-1);
742 x_current_status:=process_api_internal(p_transaction_id, l_child_EO_Node,p_validate,p_effective_date,x_current_status);
743 end loop;
744 hr_utility.set_location('End of For Loop :' || l_proc,35);
745 end if;
746 end if; -- if EO API NAME IS NOT NULL
747 hr_utility.set_location('Exiting:' || l_proc,40);
748 return x_current_status;
749 End process_api_internal;
750
751
752
753 Function process_api_call(
754 p_transaction_step_id in NUMBER,
755 p_api_name in VARCHAR2,
756 p_root_node in xmldom.DOMNode,
757 p_validate in number default hr_api.g_false_num,
758 p_effective_date in DATE,
759 p_return_status in varchar2)
760 return VARCHAR2 IS
761 l_procapi_retstat_out VARCHAR2(1);
762 l_sqlbuf varchar2(1000);
763 l_EO_api_name varchar2(100);
764 l_EORowNode xmldom.DOMNode;
765 -- l_CLOB CLOB;
766 x_current_status varchar2(1);
767 l_proc varchar2(72) := g_package || 'process_api_call';
768
769 Begin
770 hr_utility.set_location(' Entering:' || l_proc,10);
771
772 -- Get the ApiName from the parameter
773 l_EO_api_name := p_api_name;
774 if(xmlDOM.isNull(p_root_node)=false) then
775 -- From the Root Node Get the EORow Node,which is a sibiling to the CDATA node
776 l_EORowNode := xmldom.getNextSibling(xmldom.getFirstChild(p_root_node));
777 end if;
778 -- Set the Return Status to the default value
779 l_procapi_retstat_out := 'N';
780 -- Set the return value to the return value got from the calling funtion
781 x_current_status := p_return_status;
782
783
784 -- Make a CLOB out of the root_node for binding purposes
785 if(xmlDOM.isNull(l_EORowNode)=false) then
786 hr_utility.set_location('Making the CLOB:' || l_proc,15);
787 DBMS_LOB.createTemporary(g_tempClob, FALSE);
788 xmldom.writeToClob(l_EORowNode,g_tempClob);
789 end if;
790
791
792 hr_utility.set_location('Building the Dynamic Procedure call:' || l_proc,20);
793 if (xmldom.isNull(p_root_node)=false) then
794 l_sqlbuf:= 'begin ' || l_EO_api_name ||
795 '(p_transaction_step_id => :1
796 ,p_document => :2
797 ,p_return_status => :3
798 ,p_validate => :4
799 ,p_effective_date => :5); end;';
800 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 ;
801 else
802 l_sqlbuf:= 'begin ' || l_EO_api_name ||
803 '(p_transaction_step_id => :1
804 ,p_return_status => :2
805 ,p_validate => :3
806 ,p_effective_date => :4); end;';
807 EXECUTE IMMEDIATE l_sqlbuf using in p_transaction_step_id, out l_procapi_retstat_out, in p_validate, in p_effective_date ;
808 DBMS_LOB.freetemporary(g_tempClob);
809 end if;
810
811 -- Free the CLOB
812 -- Make the call to set_status to set the current_return Status
813 x_current_status:=set_status(x_current_status,l_procapi_retstat_out);
814
815 hr_utility.set_location('Exiting:' || l_proc,25);
816 return x_current_status;
817
818 End process_api_call;
819
820 Function set_status(
821 p_curent_status in VARCHAR2,
822 p_dyn_sql_processapi_sts in VARCHAR2)
823 return VARCHAR2 IS
824 x_return_status varchar2(1);
825 l_proc varchar2(72) := g_package || 'set_status';
826 Begin
827 hr_utility.set_location(' Entering:' || l_proc,10);
828
829 x_return_status:=p_curent_status;
830 if (p_dyn_sql_processapi_sts = 'E') then
831 x_return_status := 'E';
832 elsif (p_dyn_sql_processapi_sts = 'W' and NVL(p_curent_status,'W') <> 'E') then
833 x_return_status := 'W';
834 end if;
835 hr_utility.set_location(' Exiting :' || l_proc,15);
836
837 return x_return_status;
838
839 End set_status;
840
841
842 procedure set_transaction_context(
843 p_transaction_id in number)
844 IS
845 cursor csr_hat is
846 select hat.transaction_id,
847 hat.creator_person_id,
848 hat.status,
849 hat.function_id,
850 hat.transaction_ref_table,
851 hat.transaction_ref_id,
852 hat.transaction_type,
853 hat.assignment_id,
854 hat.selected_person_id,
855 hat.item_type,
856 hat.item_key,
857 hat.transaction_effective_date,
858 hat.process_name,
859 hat.transaction_state,
860 hat.effective_date_option
861 from hr_api_transactions hat
862 where hat.transaction_id =p_transaction_id;
863 step_row csr_hat%rowtype;
864 l_proc varchar2(72) := g_package || 'set_transaction_context';
865 Begin
866
867 hr_utility.set_location(' Entering:' || l_proc,10);
868 hr_utility.set_location(' Opening Cursor:csr_hat' || l_proc,15);
869 g_txn_ctx := null;
870 open csr_hat;
871 fetch csr_hat into step_row;
872 close csr_hat;
873 hr_utility.set_location(' Closing Cursor:csr_hat' || l_proc,20);
874
875 -- Set the Individual fields on the Global Transaction Context Record
876 hr_utility.set_location(' Setting GlobalTxnCtx' || l_proc,25);
877 g_txn_ctx.TRANSACTION_ID :=step_row.TRANSACTION_ID;
878 g_txn_ctx.CREATOR_PERSON_ID :=step_row.CREATOR_PERSON_ID;
879 g_txn_ctx.STATUS :=step_row.STATUS;
880 g_txn_ctx.FUNCTION_ID :=step_row.FUNCTION_ID;
881 g_txn_ctx.TRANSACTION_REF_TABLE :=step_row.TRANSACTION_REF_TABLE;
882 g_txn_ctx.TRANSACTION_REF_ID :=step_row.TRANSACTION_REF_ID;
883 g_txn_ctx.TRANSACTION_TYPE :=step_row.TRANSACTION_TYPE;
884 g_txn_ctx.ASSIGNMENT_ID :=step_row.ASSIGNMENT_ID;
885 g_txn_ctx.SELECTED_PERSON_ID :=step_row.SELECTED_PERSON_ID;
886 g_txn_ctx.ITEM_TYPE :=step_row.ITEM_TYPE;
887 g_txn_ctx.ITEM_KEY :=step_row.ITEM_KEY;
888 g_txn_ctx.PROCESS_NAME :=step_row.PROCESS_NAME;
889 g_txn_ctx.TRANSACTION_STATE :=step_row.TRANSACTION_STATE;
890 g_txn_ctx.EFFECTIVE_DATE_OPTION :=step_row.EFFECTIVE_DATE_OPTION;
891
892 if (step_row.EFFECTIVE_DATE_OPTION = 'A') then
893 g_txn_ctx.EFFECTIVE_DATE := trunc(SYSDATE);
894 else
895 g_txn_ctx.EFFECTIVE_DATE := nvl(step_row.TRANSACTION_EFFECTIVE_DATE, trunc(sysdate));
896 end if;
897 hr_utility.set_location(' Completd Setting GlobalTxnCtx' || l_proc,30);
898 hr_utility.set_location(' Exiting :' || l_proc,35);
899
900
901 end set_transaction_context;
902
903
904 procedure set_person_context(
905 p_selected_person_id in number,
906 p_selected_assignment_id in number,
907 p_effective_date in DATE)
908 IS
909
910 cursor csr_person_details is
911 select ppf.full_name,
912 ppf.person_id,
913 ppf.employee_number,
914 ppf.npw_number,
915 decode(ppf.current_employee_flag, 'Y', 'Y',decode(ppf.current_npw_flag,'Y','Y'),'N') active,
916 paf.assignment_id,
917 paf.assignment_number,
918 paf.assignment_type,
919 paf.primary_flag,
920 paf.supervisor_id,
921 sup.full_name supervisor_name,
922 ppf.business_group_id,
923 paf.organization_id,
924 bustl.name business_group_name,
925 orgtl.name organization_name,
926 paf.job_id,
927 jtl.name job_name,
928 paf.position_id,
929 postl.name position_name,
930 oi.org_information10 currency_code,
931 oi.org_information2 employee_number_generation,
932 oi.org_information3 applicant_number_generation,
933 oi.org_information16 npw_number_generation,
934 oi.org_information9 legislation_code,
935 fs.id_flex_structure_code people_grp_f_struct_code,
936 oi.org_information14 security_group_id,
937 paf.location_id,
938 paf.payroll_id
939
940 from per_all_people_f ppf,
941 per_all_assignments_f paf,
942 hr_all_organization_units_tl bustl,
943 hr_all_organization_units_tl orgtl,
944 per_jobs_tl jtl,
945 hr_all_positions_f_tl postl,
946 per_all_people_f sup,
947 hr_organization_information oi,
948 fnd_id_flex_structures fs
949
950 where ppf.person_id = p_selected_person_id
951 and ppf.person_id = paf.person_id
952 and paf.assignment_id = nvl(p_selected_assignment_id, paf.assignment_id)
953 and paf.assignment_type in ('E','C','A')
954 and paf.primary_flag = decode (nvl(p_selected_assignment_id, -1),-1,'Y', paf.primary_flag)
955 and paf.supervisor_id = sup.person_id(+)
956 and ppf.business_group_id = oi.organization_id
957 and oi.org_information_context = 'Business Group Information'
958 and oi.org_information5 = fs.id_flex_num(+)
959 and fs.id_flex_code(+) = 'GRP'
960 and fs.application_id(+) = 801
961 and ppf.business_group_id = bustl.organization_id
962 and bustl.language = userenv('LANG')
963 and paf.organization_id = orgtl.organization_id
964 and orgtl.language = userenv('LANG')
965 and paf.job_id = jtl.job_id(+)
966 and jtl.language(+) = userenv('LANG')
967 and paf.position_id = postl.position_id(+)
968 and postl.language(+) = userenv('LANG')
969 and p_effective_date between ppf.effective_start_date and ppf.effective_end_date
970 and p_effective_date between paf.effective_start_date and paf.effective_end_date
971 and p_effective_date between sup.effective_start_date(+) and sup.effective_end_date(+);
972
973 step_row csr_person_details%rowtype;
974 l_proc varchar2(72) := g_package || 'set_person_context';
975 l_orgid number;
976 ----------
977 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
978 p_transaction_id number;
979 dummy VARCHAR2(10);
980 ----------
981 Begin
982 hr_utility.set_location(' Entering:' || l_proc,10);
983 hr_utility.set_location(' Opening Cursor:csr_person_details' || l_proc,15);
984
985 --
986 -- Initializing variables
987 p_transaction_id := NULL;
988 dummy := NULL;
989
990 if p_selected_person_id is not null AND p_selected_assignment_id is not null AND p_effective_date is not null then
991 open csr_person_details;
992 fetch csr_person_details into step_row;
993 close csr_person_details;
994 hr_utility.set_location(' Closing Cursor:csr_person_details' || l_proc,20);
995 -- Set the Individual fields on the Global Person Record
996 hr_utility.set_location('Setting the GlobalPersonRecord:' || l_proc,25);
997
998 g_person_ctx.FULL_NAME :=step_row.FULL_NAME;
999 g_person_ctx.PERSON_ID :=step_row.PERSON_ID;
1000 g_person_ctx.EMPLOYEE_NUMBER :=step_row.EMPLOYEE_NUMBER;
1001 g_person_ctx.NPW_NUMBER :=step_row.NPW_NUMBER;
1002 g_person_ctx.ACTIVE :=step_row.ACTIVE;
1003 g_person_ctx.ASSIGNMENT_ID :=step_row.ASSIGNMENT_ID;
1004 g_person_ctx.ASSIGNMENT_NUMBER :=step_row.ASSIGNMENT_NUMBER;
1005 g_person_ctx.ASSIGNMENT_TYPE :=step_row.ASSIGNMENT_TYPE;
1006 g_person_ctx.PRIMARY_FLAG :=step_row.PRIMARY_FLAG;
1007 g_person_ctx.SUPERVISOR_ID :=step_row.SUPERVISOR_ID;
1008 g_person_ctx.SUPERVISOR_NAME :=step_row.SUPERVISOR_NAME;
1009 g_person_ctx.BUSINESS_GROUP_ID :=step_row.BUSINESS_GROUP_ID;
1010 g_person_ctx.ORGANIZATION_ID :=step_row.ORGANIZATION_ID;
1011 g_person_ctx.BUSINESS_GROUP_NAME :=step_row.BUSINESS_GROUP_NAME;
1012 g_person_ctx.ORGANIZATION_NAME :=step_row.ORGANIZATION_NAME;
1013 g_person_ctx.JOB_ID :=step_row.JOB_ID;
1014 g_person_ctx.JOB_NAME :=step_row.JOB_NAME;
1015 g_person_ctx.POSITION_ID :=step_row.POSITION_ID;
1016 g_person_ctx.POSITION_NAME :=step_row.POSITION_NAME;
1017 g_person_ctx.LOCATION_ID :=step_row.LOCATION_ID;
1018 g_person_ctx.CURRENCY_CODE :=step_row.CURRENCY_CODE;
1019 g_person_ctx.EMPLOYEE_NUMBER_GENERATION :=step_row.EMPLOYEE_NUMBER_GENERATION;
1020 g_person_ctx.APPLICANT_NUMBER_GENERATION:=step_row.APPLICANT_NUMBER_GENERATION;
1021 g_person_ctx.NPW_NUMBER_GENERATION :=step_row.NPW_NUMBER_GENERATION;
1022 g_person_ctx.LEGISLATION_CODE :=step_row.LEGISLATION_CODE;
1023 g_person_ctx.PEOPLE_GRP_F_STRUCT_CODE :=step_row.PEOPLE_GRP_F_STRUCT_CODE;
1024 g_person_ctx.SECURITY_GROUP_ID :=step_row.SECURITY_GROUP_ID;
1025 g_person_ctx.PAYROLL_ID :=step_row.PAYROLL_ID;
1026
1027 init_profiles( p_person_id => g_person_ctx.PERSON_ID,
1028 p_assignment_id => g_person_ctx.ASSIGNMENT_ID,
1029 p_business_group_Id => g_person_ctx.BUSINESS_GROUP_ID,
1030 p_organization_Id => g_person_ctx.ORGANIZATION_ID,
1031 p_location_id => g_person_ctx.LOCATION_ID,
1032 p_payroll_id => g_person_ctx.PAYROLL_ID
1033 );
1034
1035 -- HRMS BPO Enhancement changes,for bug 7501793
1036 l_orgid := step_row.business_group_id;
1037 if hr_multi_tenancy_pkg.is_multi_tenant_system then
1038 l_orgid := hr_multi_tenancy_pkg.get_org_id_for_person(step_row.person_id);
1039 end if;
1040
1041 hr_util_misc_ss.set_sys_ctx(step_row.legislation_code, l_orgid);
1042 end if;
1043
1044 ----------
1045 ---Changes for 11776102
1046
1047 p_transaction_id := g_txn_ctx.TRANSACTION_ID;
1048
1049 hr_utility.trace('Inside hr_transaction_swi.set_person_context: p_transaction_id := ' || p_transaction_id);
1050 /*
1051 if(p_transaction_id is not null) then
1052 begin
1053 select * into lr_hr_api_transaction_rec
1054 from hr_api_transactions
1055 where transaction_id= p_transaction_id;
1056
1057 if(lr_hr_api_transaction_rec.item_key IS NOT NULL) THEN
1058 if(wf_engine.getitemattrtext(lr_hr_api_transaction_rec.item_type,
1059 lr_hr_api_transaction_rec.item_key,
1060 'HR_DEFER_COMMIT_ATTR',true)='Y' and fnd_profile.value('HR_CROSS_BUSINESS_GROUP')='N') then
1061
1062 BEGIN
1063
1064 select 'x'
1065 into dummy
1066 from per_people_f
1067 where person_id = p_selected_person_id
1068 and p_effective_date between effective_start_date and effective_end_date;
1069 --
1070 dummy := null;
1071 --
1072 fnd_profile.put('PER_BUSINESS_GROUP_ID', step_row.BUSINESS_GROUP_ID);
1073
1074 EXCEPTION
1075 When OTHERS then
1076 fnd_profile.put('PER_BUSINESS_GROUP_ID', step_row.BUSINESS_GROUP_ID);
1077 null;
1078 end;
1079 end if;
1080 end if;
1081 end;
1082 end if;
1083 */
1084
1085 -- Rewriting above block to handle exception
1086
1087 if(p_transaction_id is not null) then
1088
1089 declare
1090 cursor cur is
1091 select *
1092 from hr_api_transactions
1093 where transaction_id = p_transaction_id;
1094 begin
1095
1096 hr_utility.trace('Entered into anonymous block for fix 11776102');
1097
1098 open cur;
1099 fetch cur into lr_hr_api_transaction_rec;
1100 if cur%NOTFOUND then
1101 lr_hr_api_transaction_rec.item_type := NULL;
1102 lr_hr_api_transaction_rec.item_key := NULL;
1103 hr_utility.trace('Cursor cur fetches no record !');
1104 end if;
1105 if cur%ROWCOUNT > 1 then
1106 hr_utility.trace('Cursor cur fetches more than one record!');
1107 NULL;
1108 end if;
1109 close cur;
1110
1111 if(lr_hr_api_transaction_rec.item_key IS NOT NULL) THEN
1112
1113 if(wf_engine.getitemattrtext(lr_hr_api_transaction_rec.item_type,
1114 lr_hr_api_transaction_rec.item_key,
1115 'HR_DEFER_COMMIT_ATTR',true)='Y' AND
1116 fnd_profile.value('HR_CROSS_BUSINESS_GROUP')='N') THEN
1117
1118 BEGIN
1119
1120 select 'x'
1121 into dummy
1122 from per_people_f
1123 where person_id = p_selected_person_id
1124 and p_effective_date between effective_start_date and effective_end_date;
1125 --
1126 dummy := null;
1127 --
1128 fnd_profile.put('PER_BUSINESS_GROUP_ID', step_row.BUSINESS_GROUP_ID);
1129
1130 EXCEPTION
1131 When OTHERS then
1132 fnd_profile.put('PER_BUSINESS_GROUP_ID', step_row.BUSINESS_GROUP_ID);
1133 NULL;
1134 END;
1135 end if;
1136 end if;
1137 end;
1138 end if;
1139
1140
1141
1142 ----------
1143 hr_utility.set_location('Set values on Global Person Record:' || l_proc,30);
1144 hr_utility.set_location(' Exiting :' || l_proc,35);
1145
1146 end set_person_context;
1147
1148 procedure init_profiles(
1149 p_person_id in number,
1150 p_assignment_id in Number,
1151 p_business_group_Id in Number,
1152 p_organization_Id in Number,
1153 p_location_id in Number,
1154 p_payroll_id in number
1155 )
1156 IS
1157 l_proc varchar2(72) := g_package || 'init_profiles';
1158 Begin
1159
1160 hr_utility.set_location(' Entering:' || l_proc,10);
1161 hr_utility.set_location(' Setting Profile values:' || l_proc,15);
1162
1163 fnd_profile.put('PER_PERSON_ID', p_person_id);
1164 fnd_profile.put('PER_ASSIGNMENT_ID', p_assignment_id);
1165 fnd_profile.put('PER_BUSINESS_GROUP_ID', p_business_group_Id);
1166 fnd_profile.put('PER_ORGANIZATION_ID', p_organization_Id);
1167 fnd_profile.put('PER_LOCATION_ID', p_location_id);
1168 fnd_profile.put('PER_PAYROLL_ID', p_payroll_id);
1169
1170 hr_utility.set_location(' Exiting :' || l_proc,20);
1171
1172 end init_profiles;
1173
1174
1175 procedure delete_transaction(
1176 p_transaction_id in NUMBER,
1177 p_validate in NUMBER default hr_api.g_false_num)
1178 is
1179 l_proc varchar2(72) := g_package || 'delete_transaction';
1180 begin
1181 hr_utility.set_location(' Entering:' || l_proc,10);
1182 hr_utility.set_location(' Calling:hr_transaction_api.rollback_transaction' || l_proc,15);
1183 delete_transaction_children(p_transaction_id, p_validate);
1184 hr_sflutil_ss.closesflnotifications(p_transaction_id,null,null);
1185 hr_transaction_api.rollback_transaction(
1186 p_transaction_id => p_transaction_id,
1187 p_validate => (p_validate=hr_api.g_true_num));
1188 hr_utility.set_location(' Exiting :' || l_proc,20);
1189 end delete_transaction;
1190
1191 function convertCLOBtoXMLElement(
1192 p_document in CLOB)
1193 return xmldom.DOMElement is
1194 x_commitElement xmldom.DOMElement;
1195 l_parser xmlparser.Parser;
1196 l_proc varchar2(72) := g_package || 'convertCLOBtoXMLElement';
1197 Begin
1198 hr_utility.set_location(' Entering:' || l_proc,10);
1199 hr_utility.set_location(' CLOB --> xmldom.DOMElement:' || l_proc,15);
1200 -- CLOB --> xmldom.DOMElement
1201 l_parser := xmlparser.newParser;
1202 xmlparser.ParseCLOB(l_parser,p_document);
1203 x_commitElement := xmldom.getDocumentElement(xmlparser.getDocument(l_parser));
1204 return x_commitElement;
1205 end convertCLOBtoXMLElement;
1206
1207 procedure setTransactionStatus(
1208 p_transaction_id in NUMBER,
1209 p_transaction_ref_table in varchar2,
1210 p_currentTxnStatus in varchar2,
1211 p_proposedTxnStatus in varchar2,
1212 p_propagateMessagePub in number,
1213 p_status out nocopy varchar2)
1214 IS
1215 --
1216 PRAGMA AUTONOMOUS_TRANSACTION;
1217 --
1218 -- local variables
1219 c_proc constant varchar2(30) := 'setTransactionStatus';
1220 c_updateStatus hr_api_transactions.status%type;
1221 ln_notification_id wf_notifications.notification_id%type;
1222 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1223
1224 begin
1225 -- check if debug enabled
1226 if g_debug then
1227 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1228 end if;
1229
1230 -- call the overloaded setTransactionStatus with null comments
1231 setTransactionStatus(p_transaction_id,null,
1232 p_transaction_ref_table,
1233 p_currentTxnStatus,
1234 p_proposedTxnStatus,
1235 p_propagateMessagePub,
1236 p_status);
1237
1238 if g_debug then
1239 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1240 end if;
1241
1242 exception
1243 when others then
1244 -- return error status
1245 p_status := 'E';
1246 end setTransactionStatus;
1247
1248
1249 function isFYINtf(p_ntfId in number)
1250 return boolean IS
1251
1252 l_exists char(1);
1253 begin
1254 l_exists := 'N';
1255 select 'Y' into l_exists
1256 from dual
1257 where exists (select 'e'
1258 from wf_notifications wn, wf_message_attributes mat
1259 where wn.notification_id = p_ntfId
1260 and wn.message_name = mat.message_name
1261 and wn.message_type = mat.message_type
1262 and mat.name = 'RESULT');
1263
1264 return false;
1265 if(l_exists is not null and l_exists='Y') then
1266 return false;
1267 else
1268 return true;
1269 end if;
1270 Exception when others then
1271 return true;
1272 end isFYINtf;
1273
1274 function isEditAllowed(p_transaction_id in number,
1275 p_transaction_status in varchar2,
1276 p_notification_id in number,
1277 p_authenticateNtf in number,
1278 p_loginPersonId in number,
1279 p_loginPersonBgId in number,
1280 p_propagateMessagePub in number)
1281 return varchar2
1282 is
1283 -- local variables
1284 editAllowed varchar2(1);
1285 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1286 lr_per_appraisals_rec per_appraisals%rowtype;
1287 l_authenticateNtf boolean;
1288
1289 CURSOR csr_appr_details (p_appraisal_id IN NUMBER)
1290 IS
1291 SELECT * FROM per_appraisals WHERE appraisal_id = p_appraisal_id;
1292
1293
1294 begin
1295
1296 -- check if we need to propagate the error messages
1297 if(p_propagateMessagePub=hr_api.g_true_num) then
1298 hr_multi_message.enable_message_list;
1299 end if;
1300
1301 -- set the default return satatus
1302 editAllowed :='N';
1303
1304 -- set the default ntf authentication
1305 if((p_authenticateNtf is null) or (p_authenticateNtf=hr_api.g_true_num)) then
1306 l_authenticateNtf := true;
1307 else
1308 l_authenticateNtf := false;
1309 end if;
1310
1311
1312 if(p_notification_id is not null) then
1313 -- check if notification is open and user has access to the notification
1314 if not hr_sflutil_ss.OpenNotificationsExist(p_notification_id) then
1315 -- raise exception ... need to change
1316 if(hr_multi_message.is_message_list_enabled) then
1317 --HRSSA_TRANSACTION_COMPLETE
1318 --This notification is not available as it has already been completed
1319 --and closed.
1320 fnd_message.set_name(800,'HRSSA_TRANSACTION_COMPLETE');
1321 hr_multi_message.add(null,null,null,null,null,'N',hr_multi_message.g_error_msg);
1322 end if;
1323 editAllowed :='E';
1324 return editAllowed;
1325 end if;
1326 -- authenticate login user access to the notifcation id
1327 if(l_authenticateNtf and (wf_advanced_worklist.authenticate(fnd_global.user_name,
1328 p_notification_id,null)
1329 <>fnd_global.user_name)) then
1330
1331 -- no more iterations return false
1332 if(hr_multi_message.is_message_list_enabled) then
1333 hr_multi_message.add(null,null,null,null,null,'N',hr_multi_message.g_error_msg);
1334 end if;
1335 editAllowed :='E';
1336 return editAllowed;
1337 end if;
1338 end if;
1339
1340 if(p_transaction_id is not null) then
1341 -- get the transaction details
1342 begin
1343 select * into lr_hr_api_transaction_rec
1344 from hr_api_transactions
1345 where transaction_id=p_transaction_id;
1346 exception
1347 when others then
1348 editAllowed :='N';
1349 end;
1350
1351 -- 14341883 edit allowed is disabled for all the entries confined to Batch Element Entry
1352 if(lr_hr_api_transaction_rec.process_name='PAY_BATCH_JSP_PRC') then
1353 editAllowed := 'N';
1354 return editAllowed;
1355 end if;
1356 -- 14341883 edit allowed is disabled for all the entries confined to Batch Element Entry
1357
1358 --start - fix for bug 14592885
1359 if(p_transaction_status not in ('Y','YS','RO','ROS')) then
1360 if(p_transaction_status in ('W', 'RI') and (lr_hr_api_transaction_rec.transaction_ref_table='PER_APPRAISALS'))
1361 then
1362 OPEN csr_appr_details (lr_hr_api_transaction_rec.transaction_ref_id);
1363 FETCH csr_appr_details INTO lr_per_appraisals_rec;
1364 CLOSE csr_appr_details;
1365
1366 if(lr_per_appraisals_rec.main_appraiser_id = p_loginPersonId AND (lr_per_appraisals_rec.appraisal_system_status in ('ONGOING', 'RFC')))
1367 then
1368 editAllowed := 'Y';
1369 return editAllowed;
1370 elsif(lr_per_appraisals_rec.appraisee_person_id = p_loginPersonId AND lr_per_appraisals_rec.appraisal_system_status = 'TRANSFER')
1371 then
1372 editAllowed := 'Y';
1373 return editAllowed;
1374 else
1375 editAllowed := 'N';
1376 return editAllowed;
1377 end if;
1378 elsif(isTxnOwner(null,lr_hr_api_transaction_rec.creator_person_id))
1379 then
1380 -- it is the creator editing the transaction
1381 editAllowed := 'Y';
1382 return editAllowed;
1383 end if;
1384 --end - fix for bug 14592885
1385 elsif(p_transaction_status in ('Y','YS','RO','ROS' )) then
1386 -- case where approvers trying to edit
1387
1388 -- check if the login person is the approver
1389 if(fnd_global.user_name=wf_engine.getitemattrtext(lr_hr_api_transaction_rec.item_type,
1390 lr_hr_api_transaction_rec.item_key,
1391 'FORWARD_TO_USERNAME',true)) then
1392 -- check the profile if the system is configured for approvers editing
1393 IF ( nvl(fnd_profile.value('PQH_ALLOW_APPROVER_TO_EDIT_TXN'),'N') = 'Y' ) THEN
1394 -- add specific override logic here
1395 -- case 1: check the programatic overide for the flow
1396 -- wf item attribute HR_RESTRICT_EDIT_ATTR
1397
1398 if(wf_engine.getitemattrtext(lr_hr_api_transaction_rec.item_type,
1399 lr_hr_api_transaction_rec.item_key,
1400 'HR_RESTRICT_EDIT_ATTR',true)='Y') then
1401 editAllowed := 'N';
1402 return editAllowed;
1403 end if;
1404
1405 -- case 2: Functional module layer
1406 -- appraisal specific, not edit allowed for now by approvers
1407 if(lr_hr_api_transaction_rec.transaction_ref_table='PER_APPRAISALS') then
1408 editAllowed := 'N';
1409 -- no more checks return
1410 return editAllowed;
1411 end if;
1412
1413
1414 -- check the if the appover is allowed to edit
1415 pqh_ss_utility.check_edit_privilege (
1416 p_personId => nvl(p_loginPersonId,fnd_global.employee_id)
1417 ,p_businessGroupId => p_loginPersonBgId
1418 ,p_editAllowed => editAllowed);
1419
1420
1421 END IF;-- edit profile check.
1422 end if; -- approver check
1423 end if; -- transaction status check
1424 end if; -- transaction id null check
1425 -- disable the message propagation
1426 IF (p_propagateMessagePub=hr_api.g_true_num) THEN
1427 hr_multi_message.disable_message_list;
1428 END IF;
1429
1430
1431
1432 return editAllowed;
1433
1434 exception
1435 when others then
1436 editAllowed :='N';
1437 return editAllowed;
1438 end isEditAllowed;
1439
1440
1441 function isDeleteAllowed(p_transaction_id in number,
1442 p_transaction_status in varchar2,
1443 p_notification_id in number,
1444 p_authenticateNtf in number,
1445 p_propagateMessagePub in number)
1446 return varchar2
1447 is
1448 -- local variables
1449 deleteAllowed varchar2(1);
1450 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1451 l_authenticateNtf boolean;
1452 begin
1453
1454 -- set default
1455 deleteAllowed :='N';
1456 -- check if we need to propagate the error messages
1457 if(p_propagateMessagePub=hr_api.g_true_num) then
1458 hr_multi_message.enable_message_list;
1459 end if;
1460
1461 -- set the default ntf authentication
1462 if((p_authenticateNtf is null) or (p_authenticateNtf=hr_api.g_true_num)) then
1463 l_authenticateNtf := true;
1464 else
1465 l_authenticateNtf := false;
1466 end if;
1467
1468
1469 if(p_notification_id is not null) then
1470 -- check if notification is open and user has access to the notification
1471 if not hr_sflutil_ss.OpenNotificationsExist(p_notification_id) then
1472 -- raise exception ... need to change
1473 if(hr_multi_message.is_message_list_enabled) then
1474 --HRSSA_TRANSACTION_COMPLETE
1475 --This notification is not available as it has already been completed
1476 --and closed.
1477 fnd_message.set_name(800,'HRSSA_TRANSACTION_COMPLETE');
1478 hr_multi_message.add(null,null,null,null,null,'N',hr_multi_message.g_error_msg);
1479 end if;
1480
1481 deleteAllowed :='E';
1482 return deleteAllowed;
1483 end if;
1484
1485 -- authenticate login user access to the notifcation id
1486 if(l_authenticateNtf and (wf_advanced_worklist.authenticate(fnd_global.user_name,
1487 p_notification_id,null)
1488 <>fnd_global.user_name)) then
1489 -- no more iterations return false
1490 if(hr_multi_message.is_message_list_enabled) then
1491 hr_multi_message.add(null,null,null,null,null,'N',hr_multi_message.g_error_msg);
1492 end if;
1493 deleteAllowed :='E';
1494 return deleteAllowed;
1495 end if;
1496 end if;
1497
1498 if(p_transaction_id is not null) then
1499 -- get the transaction details
1500 begin
1501 select * into lr_hr_api_transaction_rec
1502 from hr_api_transactions
1503 where transaction_id=p_transaction_id;
1504 exception
1505 when others then
1506 deleteAllowed :='N';
1507 end;
1508
1509 if(p_transaction_status not in ('Y','YS','RO','ROS')
1510 and isTxnOwner(null,lr_hr_api_transaction_rec.creator_person_id)) then
1511 -- it is the creator editing the transaction
1512 --- added for 9661713 -- delete always disabled for Appraisal notifications
1513 IF lr_hr_api_transaction_rec.transaction_ref_table = 'PER_APPRAISALS'
1514 and lr_hr_api_transaction_rec.status IN ('C','RI','RIS') THEN
1515 deleteAllowed := 'N';
1516 ELSE
1517 deleteAllowed := 'Y';
1518 END IF;
1519 --- End changes of 9661713
1520 else
1521 deleteAllowed :='N';
1522 end if;
1523
1524 -- disable the message propagation
1525 IF (p_propagateMessagePub=hr_api.g_true_num) THEN
1526 hr_multi_message.disable_message_list;
1527 END IF;
1528 end if;
1529
1530 return deleteAllowed;
1531
1532 exception
1533 when others then
1534 deleteAllowed :='N';
1535 return deleteAllowed;
1536 end isDeleteAllowed;
1537
1538 procedure ownerDeleteAction(p_transaction_id in number,
1539 p_currentTxnStatus in varchar2,
1540 p_transaction_type in varchar2,
1541 p_item_type in varchar2,
1542 p_item_key in varchar2)
1543 is
1544 lv_result varchar2(100);
1545 ln_notification_id wf_notifications.notification_id%type;
1546 ln_activity_id wf_item_activity_statuses.process_activity%type;
1547 begin
1548
1549 -- check on high level if the owner can delete it
1550 -- only case when the pending approvals
1551 if(p_currentTxnStatus in ('Y','YS','RO','ROS')) then
1552 -- raise exception
1553 return;
1554 end if;
1555
1556
1557 if(p_transaction_id is not null) then
1558 -- check if WF based on non-WF based
1559 if(p_item_key is not null) then
1560 -- based on status we need to either abort the wf process
1561 -- and soft delete the txn
1562 -- OR transition the WF process in delete mode
1563 if(p_currentTxnStatus in ('RI','RIS')) then
1564 -- call workflow process in 'DELETE' mode
1565 -- get the notification id and complete it with delete mode
1566
1567 -- get the rfc ntf id
1568 select ias.notification_id,ias.process_activity
1569 into ln_notification_id,ln_activity_id
1570 from wf_item_activity_statuses ias
1571 where ias.item_type = p_item_type
1572 and ias.item_key = p_item_key
1573 and ias.activity_status = 'NOTIFIED'
1574 and notification_id is not null
1575 and rownum < 2;
1576 -- check if the notification id if not throw exception
1577 if(ln_notification_id is not null) then
1578
1579 -- hsundar: Delete any open SFL Notification for this txn
1580 hr_sflutil_ss.closesflnotifications(p_transaction_id,p_item_type,p_item_key);
1581
1582 -- complete ntf with HR_V5_ALL_RESPONSES.del code
1583 /* wf_engine.CompleteActivity(
1584 p_item_Type
1585 , p_item_Key
1586 , wf_engine.getactivitylabel(ln_activity_id)
1587 , 'DEL') ; */
1588 -- fix for bug 5328872
1589 wf_notification.setattrtext(
1590 ln_notification_id
1591 ,'RESULT'
1592 ,'DEL');
1593 wf_notification.respond(
1594 ln_notification_id
1595 ,null
1596 ,fnd_global.user_name
1597 ,null);
1598 else
1599 -- throw exception
1600 null;
1601 end if;
1602 else -- other statuses
1603
1604 -- hsundar: Delete any open SFL Notification for this txn
1605 hr_sflutil_ss.closesflnotifications(p_transaction_id,p_item_type,p_item_key);
1606
1607 hr_transaction_ss.rollback_transaction(p_item_type,
1608 p_item_key,
1609 null,
1610 wf_engine.eng_run,
1611 lv_result);
1612 wf_engine.abortprocess(itemtype => p_item_type
1613 ,itemkey => p_item_key
1614 ,process =>null
1615 ,result => wf_engine.eng_force
1616 ,verify_lock=> true
1617 ,cascade=> true);
1618 end if;
1619 else -- non workflow case
1620 -- soft delete the transaction
1621 hr_transaction_api.update_transaction(
1622 p_transaction_id => p_transaction_id,
1623 p_status => 'D');
1624
1625 -- close all sfl notifications
1626 hr_sflutil_ss.closesflnotifications(p_transaction_id,null,null);
1627 -- do the module specific logic
1628 end if;
1629
1630
1631 else
1632 -- transaction id is null raise error ??
1633 null;
1634 end if;
1635 exception
1636 when others then
1637 raise;
1638 end ownerDeleteAction;
1639
1640 procedure othersDeleteAction(p_transaction_id in number,
1641 p_currentTxnStatus in varchar2,
1642 p_transaction_type in varchar2,
1643 p_item_type in varchar2,
1644 p_item_key in varchar2)
1645 is
1646 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1647 lv_result varchar2(100);
1648 ln_notification_id wf_notifications.notification_id%type;
1649 ln_activity_id wf_item_activity_statuses.process_activity%type;
1650 begin
1651
1652 if(p_currentTxnStatus in ('Y','YS','RO','ROS')) then
1653 -- get the notification activity id and complete
1654 -- the process in reject status
1655 -- get the rfc ntf id
1656 select ias.notification_id,ias.process_activity
1657 into ln_notification_id,ln_activity_id
1658 from wf_item_activity_statuses ias
1659 where ias.item_type = p_item_type
1660 and ias.item_key = p_item_key
1661 and ias.activity_status = 'NOTIFIED'
1662 and notification_id is not null
1663 and rownum < 2;
1664 -- check if we have the notification id
1665 if(ln_notification_id is not null) then
1666
1667 -- hsundar: Delete any open SFL Notification for this txn
1668 hr_sflutil_ss.closesflnotifications(p_transaction_id,p_item_type,p_item_key);
1669
1670 -- complete ntf with HR_V5_ALL_RESPONSES.Reject code
1671 /* wf_engine.CompleteActivity(
1672 p_item_Type
1673 , p_item_Key
1674 , wf_engine.getactivitylabel(ln_activity_id)
1675 , 'REJECTED') ; */
1676 -- fix for bug 5328872
1677 wf_notification.setattrtext(
1678 ln_notification_id
1679 ,'RESULT'
1680 ,'REJECTED');
1681 wf_notification.respond(
1682 ln_notification_id
1683 ,null
1684 ,fnd_global.user_name
1685 ,null);
1686
1687 else
1688 -- throw exception
1689 null;
1690 end if;
1691
1692 else
1693 -- raise exception action cannot be performed
1694 null;
1695 end if;
1696
1697
1698
1699 exception
1700 when others then
1701 raise;
1702 end othersDeleteAction;
1703
1704 procedure deleteAction(p_transaction_id in number)
1705 is
1706 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1707 lv_result varchar2(100);
1708 begin
1709 -- this routine is called from all actions UI
1710 --
1711 -- implementation logic
1712 /*
1713 CurrentStatus Creator Approver
1714 ============ ======= =========
1715 W Soft delete N/A
1716 set status
1717 to 'D'
1718 others no impl Reject txn
1719 in phase I
1720
1721 Phase II we need the txn data rollback and
1722 WF page navigation state reset.
1723
1724 */
1725 if(p_transaction_id is not null) then
1726 begin
1727 select * into lr_hr_api_transaction_rec
1728 from hr_api_transactions
1729 where transaction_id=p_transaction_id;
1730 exception
1731 when others then
1732 raise;
1733 end;
1734
1735 -- check the current owner
1736 if(isTxnOwner(null,lr_hr_api_transaction_rec.creator_person_id)) then
1737 -- do logic specific to creator
1738 ownerDeleteAction(p_transaction_id ,
1739 lr_hr_api_transaction_rec.status,
1740 lr_hr_api_transaction_rec.transaction_type,
1741 lr_hr_api_transaction_rec.item_type,
1742 lr_hr_api_transaction_rec.item_key);
1743 else -- approvers case
1744 othersDeleteAction(p_transaction_id ,
1745 lr_hr_api_transaction_rec.status,
1746 lr_hr_api_transaction_rec.transaction_type,
1747 lr_hr_api_transaction_rec.item_type,
1748 lr_hr_api_transaction_rec.item_key);
1749 end if;-- transaction owner check
1750 end if;-- transaction id check
1751
1752 exception
1753 when others then
1754 null;
1755 end deleteAction;
1756
1757
1758 procedure initiatorDeleteAction(p_transaction_id in number)
1759 is
1760 lv_result varchar2(100);
1761 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1762 begin
1763
1764 if(p_transaction_id is not null) then
1765 -- Read Transaction Details
1766 begin
1767 select * into lr_hr_api_transaction_rec
1768 from hr_api_transactions
1769 where transaction_id=p_transaction_id;
1770 exception
1771 when others then
1772 raise;
1773 end;
1774
1775 -- check if WF based on non-WF based
1776
1777 if(lr_hr_api_transaction_rec.item_type is not null) then
1778 -- WF case.
1779 -- hsundar: Delete any open SFL Notification for this txn
1780 hr_sflutil_ss.closesflnotifications(p_transaction_id
1781 ,lr_hr_api_transaction_rec.item_type
1782 ,lr_hr_api_transaction_rec.item_key);
1783
1784 hr_transaction_ss.rollback_transaction(lr_hr_api_transaction_rec.item_type,
1785 lr_hr_api_transaction_rec.item_key,
1786 null,
1787 wf_engine.eng_run,
1788 lv_result);
1789 wf_engine.abortprocess(itemtype => lr_hr_api_transaction_rec.item_type
1790 ,itemkey => lr_hr_api_transaction_rec.item_key
1791 ,process =>null
1792 ,result => wf_engine.eng_force
1793 ,verify_lock => true
1794 ,cascade => true);
1795 else
1796 -- non WF case
1797 -- soft delete the transaction
1798 hr_transaction_api.update_transaction(
1799 p_transaction_id => p_transaction_id,
1800 p_status => 'D');
1801
1802 -- close all sfl notifications
1803 hr_sflutil_ss.closesflnotifications(p_transaction_id,null,null);
1804 -- do the module specific logic
1805
1806 end if; -- End of 2nd if(lr_hr_api_transaction_rec.item_type is not null)
1807
1808 else
1809 -- transaction id is null raise error ??
1810 null;
1811 end if; -- End of main if(p_transaction_id is not null)
1812
1813 exception
1814 when others then
1815 raise;
1816 end initiatorDeleteAction;
1817
1818
1819
1820
1821 procedure cancelAction(p_transaction_id in number)
1822 is
1823 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1824 begin
1825
1826 if(p_transaction_id is not null) then
1827 begin
1828 select * into lr_hr_api_transaction_rec
1829 from hr_api_transactions
1830 where transaction_id=p_transaction_id;
1831 exception
1832 when others then
1833 null;
1834 end;
1835
1836 -- check the status
1837 if(lr_hr_api_transaction_rec.status='W') then
1838 -- delete the transaction if owner
1839 -- check the current owner
1840 if(isTxnOwner(null,lr_hr_api_transaction_rec.creator_person_id)) then
1841 deleteAction(p_transaction_id);
1842 else
1843 -- raise error ??
1844 -- not a valid call to this action
1845 null;
1846 end if;
1847 else
1848 -- all other status we need to revert the state back to last known good
1849 -- state
1850 hr_trans_history_api.cancel_action(p_transaction_id);
1851 -- Note:
1852 -- This method does not revert the inadvertant wf page flow state
1853 -- the WF activity is handling the transition and based on user action
1854 -- will revert the flow state.
1855
1856 -- call the method to reset the wf pageflow state , if wf is used for page navigation
1857 hr_approval_ss.resetWfPageFlowState(p_transaction_id);
1858
1859 end if;
1860 end if;
1861
1862
1863 exception
1864 when others then
1865 null;
1866 end cancelAction;
1867
1868
1869 function isTxnOwner(p_transaction_id in number,
1870 p_person_id in number) return boolean
1871 is
1872 -- local variables
1873 x_returnStatus boolean;
1874 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
1875 ln_person_id number;
1876 begin
1877
1878 -- set the default value
1879 x_returnStatus := false;
1880 ln_person_id := p_person_id;
1881
1882 if(p_transaction_id is not null) then
1883 -- ignore passed personid
1884 -- derive from the transaction details
1885 select hr_api_transactions.creator_person_id
1886 into ln_person_id
1887 from hr_api_transactions
1888 where transaction_id=p_transaction_id;
1889 end if;
1890
1891 --
1892 if(ln_person_id= fnd_global.employee_id) then
1893 x_returnStatus := true;
1894 else
1895 x_returnStatus :=false;
1896 end if;
1897 return x_returnStatus;
1898 exception
1899 when others then
1900 raise;
1901 end isTxnOwner;
1902
1903 procedure delete_transaction_children(
1904 p_transaction_id in NUMBER,
1905 p_validate in NUMBER default hr_api.g_false_num)
1906 is
1907 cursor csr_trn is
1908 select trn.transaction_id
1909 from hr_api_transactions trn
1910 where trn.parent_transaction_id = p_transaction_id;
1911 begin
1912 for csr_row in csr_trn loop
1913 delete_transaction(csr_row.transaction_id);
1914 end loop;
1915 end delete_transaction_children;
1916
1917 FUNCTION commit_transaction(
1918 p_transaction_id IN NUMBER,
1919 p_validate IN NUMBER DEFAULT hr_api.g_false_num,
1920 p_effective_date IN DATE DEFAULT SYSDATE)
1921 RETURN VARCHAR2 IS
1922 l_proc VARCHAR2(72) := g_package || 'commit_transaction';
1923 x_return_status VARCHAR2(1);
1924 p_error_log CLOB;
1925
1926 BEGIN
1927 hr_utility.set_location(' Entering:' || l_proc,10);
1928 commit_transaction(
1929 p_transaction_id=>p_transaction_id,
1930 p_validate =>p_validate,
1931 p_effective_date=>p_effective_date,
1932 p_process_all_on_error =>hr_api.g_false_num,
1933 p_status=>x_return_status,
1934 p_error_log=>p_error_log);
1935 hr_utility.set_location(' Exiting:' || l_proc,20);
1936
1937 RETURN x_return_status;
1938 EXCEPTION
1939 WHEN others THEN
1940 RAISE;
1941 END commit_transaction;
1942
1943 procedure intializeWFApprovals(p_transaction_id IN NUMBER
1944 ,p_item_key out nocopy number
1945 ,p_status out nocopy varchar2) as
1946
1947
1948 lv_item_type wf_items.item_type%type;
1949 lr_transaction_rec hr_api_transactions%rowtype;
1950 lv_status varchar2(8);
1951 lv_result varchar2(30);
1952 lv_errorActid wf_item_activity_statuses.process_activity%type;
1953 lv_errname VARCHAR2(4000);
1954 l_index binary_integer;
1955 l_temp_item_attribute varchar2(2000);
1956 l_role_name wf_roles.name%type;
1957 l_role_displayname wf_roles.display_name%type;
1958 lt_additional_wf_attributes HR_WF_ATTR_TABLE;
1959 lv_error_message varchar2(4000);
1960 lv_errstack varchar2(4000);
1961 lv_ntfSubMsg fnd_new_messages.message_name%type;
1962 lv_relaunchFunc fnd_form_functions.function_name%type;
1963 lv_param_name fnd_form_functions.parameters%type;
1964 lv_approval_required varchar2(5);
1965 lv_ameTransType varchar2(240);
1966 ln_ameTranAppId number;
1967 lv_xpath varchar2(20000) default 'Transaction/TransCtx';
1968 lv_review_template_rn fnd_form_functions.function_name%type;
1969 lv_Ntf_Attach_Attr wf_item_attribute_values.text_value%type;
1970 lv_approval_comments wf_item_attribute_values.text_value%type;
1971 lv_perz_func wf_item_attribute_values.text_value%type;
1972 lv_perz_leg wf_item_attribute_values.text_value%type;
1973 lv_perz_org wf_item_attribute_values.text_value%type;
1974 begin
1975
1976 begin
1977 -- call the method to create the workflow approval process
1978 if(p_transaction_id is not null) then
1979 -- get the transaction details
1980 select *
1981 into lr_transaction_rec
1982 from hr_api_transactions
1983 where transaction_id=p_transaction_id;
1984
1985 -- derive the fnd function params values from txn
1986 lv_approval_required := hr_xml_util.get_node_value(p_transaction_id,
1987 'pApprovalReqd',
1988 lv_xpath,
1989 NULL,
1990 NULL,
1991 NULL,
1992 NULL,
1993 NULL,
1994 NULL,
1995 NULL,
1996 NULL,
1997 NULL,
1998 NULL,
1999 NULL);
2000 lv_ntfSubMsg := hr_xml_util.get_node_value(p_transaction_id,
2001 'pNtfSubMsg',
2002 lv_xpath,
2003 NULL,
2004 NULL,
2005 NULL,
2006 NULL,
2007 NULL,
2008 NULL,
2009 NULL,
2010 NULL,
2011 NULL,
2012 NULL,
2013 NULL);
2014 lv_ameTransType := hr_xml_util.get_node_value(p_transaction_id,
2015 'pAMETranType',
2016 lv_xpath,
2017 NULL,
2018 NULL,
2019 NULL,
2020 NULL,
2021 NULL,
2022 NULL,
2023 NULL,
2024 NULL,
2025 NULL,
2026 NULL,
2027 NULL);
2028 ln_ameTranAppId:= hr_xml_util.get_node_value(p_transaction_id,
2029 'pAMEAppId',
2030 lv_xpath,
2031 NULL,
2032 NULL,
2033 NULL,
2034 NULL,
2035 NULL,
2036 NULL,
2037 NULL,
2038 NULL,
2039 NULL,
2040 NULL,
2041 NULL);
2042 lv_review_template_rn := hr_xml_util.get_node_value(p_transaction_id,
2043 'ReviewTemplateRNAttr',
2044 lv_xpath,
2045 NULL,
2046 NULL,
2047 NULL,
2048 NULL,
2049 NULL,
2050 NULL,
2051 NULL,
2052 NULL,
2053 NULL,
2054 NULL,
2055 NULL);
2056 lv_Ntf_Attach_Attr:= hr_xml_util.get_node_value(p_transaction_id,
2057 'NtfAttachAttr',
2058 lv_xpath,
2059 NULL,
2060 NULL,
2061 NULL,
2062 NULL,
2063 NULL,
2064 NULL,
2065 NULL,
2066 NULL,
2067 NULL,
2068 NULL,
2069 NULL);
2070 lv_perz_func:= hr_xml_util.get_node_value(p_transaction_id,
2071 'PerzFunctionName',
2072 lv_xpath,
2073 NULL,
2074 NULL,
2075 NULL,
2076 NULL,
2077 NULL,
2078 NULL,
2079 NULL,
2080 NULL,
2081 NULL,
2082 NULL,
2083 NULL);
2084 lv_perz_leg:= hr_xml_util.get_node_value(p_transaction_id,
2085 'PerzLocalizationCode',
2086 lv_xpath,
2087 NULL,
2088 NULL,
2089 NULL,
2090 NULL,
2091 NULL,
2092 NULL,
2093 NULL,
2094 NULL,
2095 NULL,
2096 NULL,
2097 NULL);
2098 lv_perz_org:= hr_xml_util.get_node_value(p_transaction_id,
2099 'PerzOrganizationId',
2100 lv_xpath,
2101 NULL,
2102 NULL,
2103 NULL,
2104 NULL,
2105 NULL,
2106 NULL,
2107 NULL,
2108 NULL,
2109 NULL,
2110 NULL,
2111 NULL);
2112 lt_additional_wf_attributes := HR_WF_ATTR_TABLE(HR_WF_ATTR_TYPE('TRAN_SUBMIT','N',null,null));
2113 hr_approval_ss.startGenericApprovalProcess(p_transaction_id
2114 ,p_item_key
2115 ,lv_ntfSubMsg
2116 ,'HR_RELAUNCH_SS'
2117 ,lt_additional_wf_attributes
2118 ,lv_status
2119 ,lv_error_message
2120 ,lv_errstack
2121 );
2122
2123
2124 -- add check for the error status and raise to bc4j accordingly
2125
2126
2127 -- set additional item attributes
2128 -- HR_OAF_NAVIGATION_ATTR
2129 -- set HR_OAF_EDIT_URL_ATTR
2130 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2131 ,itemkey => p_item_key
2132 ,aname => 'HR_OAF_EDIT_URL_ATTR'
2133 ,text_value=>'HR_RELAUNCH_SS'
2134 ,number_value=>null,
2135 date_value=>null
2136 );
2137 -- set HR_OAF_NAVIGATION_ATTR
2138 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2139 ,itemkey => p_item_key
2140 ,aname => 'HR_OAF_NAVIGATION_ATTR'
2141 ,text_value=>'N'
2142 ,number_value=>null,
2143 date_value=>null
2144 );
2145 -- set HR_REVIEW_TEMPLATE_RN_ATTR
2146 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2147 ,itemkey => p_item_key
2148 ,aname => 'HR_REVIEW_TEMPLATE_RN_ATTR'
2149 ,text_value=>lv_review_template_rn
2150 ,number_value=>null,
2151 date_value=>null
2152 );
2153
2154 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2155 ,itemkey => p_item_key
2156 ,aname => 'HR_NTF_ATTACHMENTS_ATTR'
2157 ,text_value=>lv_Ntf_Attach_Attr
2158 ,number_value=>null,
2159 date_value=>null
2160 );
2161
2162 -- HR_RESTRICT_RFC_ATTR
2163
2164 -- HR_RESTRICT_EDIT_ATTR
2165
2166 -- APPROVAL_GENERIC_URL
2167
2168 -- HR_RUNTIME_APPROVAL_REQ_FLAG
2169 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2170 ,itemkey => p_item_key
2171 ,aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG'
2172 ,text_value=>lv_approval_required
2173 ,number_value=>null,
2174 date_value=>null
2175 );
2176 -- set AME params
2177 -- 'HR_AME_APP_ID_ATTR'
2178 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2179 ,itemkey => p_item_key
2180 ,aname => 'HR_AME_APP_ID_ATTR'
2181 ,text_value=>null
2182 ,number_value=>ln_ameTranAppId
2183 ,date_value=>null
2184 );
2185 -- 'HR_AME_TRAN_TYPE_ATTR'
2186 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2187 ,itemkey => p_item_key
2188 ,aname => 'HR_AME_TRAN_TYPE_ATTR'
2189 ,text_value=>lv_ameTransType
2190 ,number_value=>null,
2191 date_value=>null
2192 );
2193
2194 -- TRANSACTION_ID
2195 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2196 ,itemkey => p_item_key
2197 ,aname => 'TRANSACTION_ID'
2198 ,text_value=>null
2199 ,number_value=>p_transaction_id
2200 ,date_value=>null
2201 );
2202
2203 -- TRAN_SUBMIT
2204 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2205 ,itemkey => p_item_key
2206 ,aname => 'TRAN_SUBMIT'
2207 ,text_value=>'Y'
2208 ,number_value=>null
2209 ,date_value=>null
2210 );
2211 -- PROCESS_DISPLAY_NAME
2212 fnd_message.set_name('PER',lv_ntfSubMsg); -- change the hardcoded
2213
2214 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2215 ,itemkey => p_item_key
2216 ,aname => 'PROCESS_DISPLAY_NAME'
2217 ,text_value=>fnd_message.get
2218 ,number_value=>null
2219 ,date_value=>null
2220 );
2221 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2222 ,itemkey => p_item_key
2223 ,aname => 'HR_PERZ_FUNCTION_NAME_ATTR'
2224 ,text_value=>lv_perz_func
2225 ,number_value=>null
2226 ,date_value=>null
2227 );
2228 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2229 ,itemkey => p_item_key
2230 ,aname => 'HR_PERZ_LOCALIZATION_CODE_ATTR'
2231 ,text_value=>lv_perz_leg
2232 ,number_value=>null
2233 ,date_value=>null
2234 );
2235 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
2236 ,itemkey => p_item_key
2237 ,aname => 'HR_PERZ_ORGANIZATION_ID_ATTR'
2238 ,text_value=>lv_perz_org
2239 ,number_value=>null
2240 ,date_value=>null
2241 );
2242 else
2243 null; -- raise error
2244
2245 end if;
2246 exception
2247 when no_data_found then
2248 raise;
2249 when others then
2250 raise;
2251 end;
2252
2253
2254 end;
2255
2256 procedure setTransactionStatus(
2257 p_transaction_id in NUMBER,
2258 p_approver_comments in varchar2,
2259 p_transaction_ref_table in varchar2,
2260 p_currentTxnStatus in varchar2,
2261 p_proposedTxnStatus in varchar2,
2262 p_propagateMessagePub in number,
2263 p_status out nocopy varchar2)
2264 IS
2265 --
2266 PRAGMA AUTONOMOUS_TRANSACTION;
2267 --
2268 -- local variables
2269 c_proc constant varchar2(30) := 'setTransactionStatus';
2270 c_updateStatus hr_api_transactions.status%type;
2271 ln_notification_id wf_notifications.notification_id%type;
2272 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
2273 lv_item_key wf_items.item_key%type;
2274 lt_additional_wf_attributes HR_WF_ATTR_TABLE;
2275 lv_error_message varchar2(4000);
2276 lv_errstack varchar2(4000);
2277 lv_status varchar2(30); -- revisit on the size
2278 lv_wf_item_attribute HR_WF_ATTR_TYPE;
2279 lv_currentTxnStatus hr_api_transactions.status%type;
2280
2281
2282 begin
2283 -- check if debug enabled
2284 if g_debug then
2285 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2286 end if;
2287 -- check if we need to propagate the error messages
2288 if(p_propagateMessagePub=hr_api.g_true_num) then
2289 hr_multi_message.enable_message_list;
2290 end if;
2291 -- check the proposed status
2292 -- S --> SFL
2293 -- A --> Approval (submit for approval).
2294 if(p_proposedTxnStatus='A')then
2295 -- set the transaction status to 'Y'
2296 c_updateStatus:='Y';
2297 elsif(p_proposedTxnStatus='S')then
2298 begin
2299 select status into lv_currentTxnStatus
2300 from hr_api_transactions
2301 where transaction_id=p_transaction_id;
2302 exception
2303 when others then
2304 null;
2305 end;
2306
2307 c_updateStatus:= hr_sflutil_ss.getSFLStatusForUpdate(
2308 nvl(p_currentTxnStatus,lv_currentTxnStatus),
2309 p_proposedTxnStatus);
2310 else
2311 -- we do not handle other status, return error status
2312 p_status := 'E';
2313 return;
2314 end if;
2315
2316
2317 begin
2318 if(p_proposedTxnStatus='S')then
2319 -- send SFL notification
2320 -- get the transaction record
2321 select *
2322 into lr_hr_api_transaction_rec
2323 from hr_api_transactions
2324 where transaction_id=p_transaction_id;
2325 -- send sfl notification to login user
2326 hr_sflutil_ss.sendsflnotification(p_transaction_id,
2327 p_transaction_ref_table,
2328 fnd_global.user_name,
2329 'HR_RELAUNCH_SS',
2330 null,
2331 ln_notification_id);
2332 -- return success status
2333 p_status := 'S';
2334 -- update the transaction status
2335 hr_transaction_api.update_transaction(
2336 p_transaction_id => p_transaction_id,
2337 p_status => c_updateStatus,
2338 p_transaction_state => null);
2339
2340 -- add the code plugin transfer history
2341 hr_trans_history_api.archive_sfl(p_transaction_id,
2342 ln_notification_id,
2343 fnd_global.user_name);
2344 elsif(p_proposedTxnStatus='A') then
2345
2346
2347 begin
2348 -- check if the wf process is initialized or not
2349 select *
2350 into lr_hr_api_transaction_rec
2351 from hr_api_transactions
2352 where transaction_id=p_transaction_id;
2353
2354 exception
2355 when others then
2356 raise;
2357 end;
2358
2359 if(lr_hr_api_transaction_rec.item_key is not null) then
2360
2361 -- call the code to transition flow in case of approvals
2362 hr_approval_ss.processapprovalsubmit(p_transaction_id,
2363 p_approver_comments);
2364 else
2365
2366 -- intialize the generic approval flow
2367 intializeWFApprovals(p_transaction_id=>p_transaction_id
2368 ,p_item_key =>lv_item_key
2369 ,p_status =>p_status);
2370 if(lv_item_key is null or p_status='E') then
2371 null; -- raise error
2372 else
2373 -- update the transaction with the item key
2374 hr_transaction_api.update_transaction(
2375 p_transaction_id => p_transaction_id,
2376 p_item_key => lv_item_key);
2377
2378 -- complete the wf to send ntf or process commit
2379 hr_approval_ss.processapprovalsubmit(p_transaction_id,
2380 p_approver_comments);
2381
2382 end if;
2383
2384 end if;
2385 else
2386 null;-- do nothing
2387 end if;
2388 exception
2389 when others then
2390 -- return error status
2391 p_status := 'E';
2392 -- propagate the error message
2393
2394 end;
2395 -- disable the message propagation
2396 IF (p_propagateMessagePub=hr_api.g_true_num) THEN
2397 hr_multi_message.disable_message_list;
2398 END IF;
2399
2400 -- finally commit the data
2401 commit;
2402
2403
2404 if g_debug then
2405 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2406 end if;
2407
2408 exception
2409 when others then
2410 -- return error status
2411 p_status := 'E';
2412 end setTransactionStatus;
2413
2414
2415 procedure setTransactionStatus(
2416 p_transaction_id in NUMBER,
2417 p_approver_comments in varchar2,
2418 p_transaction_ref_table in varchar2,
2419 p_currentTxnStatus in varchar2,
2420 p_proposedTxnStatus in varchar2,
2421 p_propagateMessagePub in number,
2422 p_fyidet in varchar2,
2423 p_status out nocopy varchar2)
2424 IS
2425 --
2426 PRAGMA AUTONOMOUS_TRANSACTION;
2427 --
2428 -- local variables
2429 c_proc constant varchar2(30) := 'setTransactionStatus';
2430 c_updateStatus hr_api_transactions.status%type;
2431 ln_notification_id wf_notifications.notification_id%type;
2432 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
2433 lv_item_key wf_items.item_key%type;
2434 lv_item_type wf_items.item_type%type;
2435 lt_additional_wf_attributes HR_WF_ATTR_TABLE;
2436 lv_error_message varchar2(4000);
2437 lv_errstack varchar2(4000);
2438 lv_status varchar2(30); -- revisit on the size
2439 lv_wf_item_attribute HR_WF_ATTR_TYPE;
2440 lv_currentTxnStatus hr_api_transactions.status%type;
2441
2442
2443 begin
2444
2445 -- check if debug enabled
2446 if g_debug then
2447 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2448 end if;
2449 -- check if we need to propagate the error messages
2450 if(p_propagateMessagePub=hr_api.g_true_num) then
2451 hr_multi_message.enable_message_list;
2452 end if;
2453 -- check the proposed status
2454 -- S --> SFL
2455 -- A --> Approval (submit for approval).
2456 if(p_proposedTxnStatus='A')then
2457 -- set the transaction status to 'Y'
2458 c_updateStatus:='Y';
2459 elsif(p_proposedTxnStatus='S')then
2460 begin
2461 select status into lv_currentTxnStatus
2462 from hr_api_transactions
2463 where transaction_id=p_transaction_id;
2464 exception
2465 when others then
2466 null;
2467 end;
2468
2469 c_updateStatus:= hr_sflutil_ss.getSFLStatusForUpdate(
2470 nvl(p_currentTxnStatus,lv_currentTxnStatus),
2471 p_proposedTxnStatus);
2472 else
2473 -- we do not handle other status, return error status
2474 p_status := 'E';
2475 return;
2476 end if;
2477
2478
2479 begin
2480 if(p_proposedTxnStatus='S')then
2481 -- send SFL notification
2482 -- get the transaction record
2483 select *
2484 into lr_hr_api_transaction_rec
2485 from hr_api_transactions
2486 where transaction_id=p_transaction_id;
2487 -- send sfl notification to login user
2488 hr_sflutil_ss.sendsflnotification(p_transaction_id,
2489 p_transaction_ref_table,
2490 fnd_global.user_name,
2491 'HR_RELAUNCH_SS',
2492 null,
2493 ln_notification_id);
2494 -- return success status
2495 p_status := 'S';
2496 -- update the transaction status
2497 hr_transaction_api.update_transaction(
2498 p_transaction_id => p_transaction_id,
2499 p_status => c_updateStatus,
2500 p_transaction_state => null);
2501
2502 -- add the code plugin transfer history
2503 hr_trans_history_api.archive_sfl(p_transaction_id,
2504 ln_notification_id,
2505 fnd_global.user_name);
2506 elsif(p_proposedTxnStatus='A') then
2507
2508
2509 begin
2510 -- check if the wf process is initialized or not
2511 select *
2512 into lr_hr_api_transaction_rec
2513 from hr_api_transactions
2514 where transaction_id=p_transaction_id;
2515
2516 exception
2517 when others then
2518 raise;
2519 end;
2520
2521 if(lr_hr_api_transaction_rec.item_key is not null) then
2522
2523 -- call the code to transition flow in case of approvals
2524 hr_approval_ss.processapprovalsubmit(p_transaction_id,
2525 p_approver_comments);
2526 else
2527
2528 -- intialize the generic approval flow
2529 intializeWFApprovals(p_transaction_id=>p_transaction_id
2530 ,p_item_key =>lv_item_key
2531 ,p_status =>p_status);
2532 if(lv_item_key is null or p_status='E') then
2533 null; -- raise error
2534 else
2535 lv_item_type := lr_hr_api_transaction_rec.item_type;
2536 if(lv_item_type is null) then
2537 null; -- raise error
2538 else
2539 wf_engine.SetItemAttrText(itemtype => lv_item_type
2540 ,itemkey => lv_item_key
2541 ,aname => 'FYI_NTF_DETAILS'
2542 ,avalue => p_fyidet);
2543
2544 -- update the transaction with the item key
2545 hr_transaction_api.update_transaction(
2546 p_transaction_id => p_transaction_id,
2547 p_item_key => lv_item_key);
2548
2549 -- complete the wf to send ntf or process commit
2550 hr_approval_ss.processapprovalsubmit(p_transaction_id,
2551 p_approver_comments);
2552 end if;
2553 end if;
2554 end if;
2555 else
2556 null;-- do nothing
2557 end if;
2558 exception
2559 when others then
2560 -- return error status
2561 p_status := 'E';
2562 -- propagate the error message
2563 end;
2564 -- disable the message propagation
2565 IF (p_propagateMessagePub=hr_api.g_true_num) THEN
2566 hr_multi_message.disable_message_list;
2567 END IF;
2568
2569 -- finally commit the data
2570 commit;
2571
2572 if g_debug then
2573 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2574 end if;
2575
2576 exception
2577 when others then
2578 -- return error status
2579 p_status := 'E';
2580
2581 end setTransactionStatus;
2582
2583 PROCEDURE createEOErrorMSgNode(doc IN OUT nocopy xmldom.DOMDocument,root_node IN OUT nocopy xmldom.DOMNode,itemtype in varchar2
2584 ,itemkey in varchar2)
2585 AS
2586 EO_node xmldom.DOMNode;
2587 message_node xmldom.DOMNode;
2588 item_node xmldom.DOMNode;
2589 msg_count_elmt xmldom.DOMElement;
2590 item_elmt xmldom.DOMElement;
2591 item_text xmldom.DOMText;
2592 msg_count NUMBER;
2593 msg_details VARCHAR2(2000);
2594 p_data VARCHAR2(4000);
2595 p_msg_index_out NUMBER;
2596
2597 BEGIN
2598 hr_utility.set_location('Entering createEOErrorMSgNode : ', 10);
2599 msg_count := fnd_msg_pub.Count_Msg;
2600 p_data := null;
2601
2602 msg_count_elmt :=xmldom.createElement(doc, 'MsgCount');
2603 xmldom.setAttribute(
2604 msg_count_elmt
2605 , 'Value'
2606 , msg_count
2607 );
2608
2609 EO_node := xmldom.appendChild(root_node,xmldom.makeNode(msg_count_elmt));
2610
2611 FOR i IN 1 .. msg_count LOOP
2612 item_elmt := xmldom.createElement(
2613 doc
2614 , 'Msg'
2615 );
2616
2617 message_node := xmldom.appendChild(
2618 EO_node
2619 , xmldom.makeNode(item_elmt)
2620 );
2621 --
2622 item_elmt := xmldom.createElement(
2623 doc
2624 , 'EncodedMessage'
2625 );
2626 item_node := xmldom.appendChild(
2627 message_node
2628 , xmldom.makeNode(item_elmt)
2629 );
2630 -- get the actual message from fnd_msg_pub
2631 msg_details :=fnd_msg_pub.Get_Detail(p_msg_index=>i,p_encoded=>'F');
2632 p_data := p_data || ' %%%% ' || msg_details;
2633 hr_utility.set_location('EO Error : ' || msg_details, 20);
2634 item_text := xmldom.createTextNode(
2635 doc
2636 , msg_details
2637 );
2638 item_node := xmldom.appendChild(
2639 item_node
2640 , xmldom.makeNode(item_text)
2641 );
2642 --
2643 END LOOP;
2644
2645 if(itemkey IS NOT NULL) THEN
2646 p_data := nvl(wf_engine.GetItemAttrText
2647 (itemtype => itemtype
2648 ,itemkey => itemkey
2649 ,aname => 'ERROR_STACK'),' ') || p_data;
2650
2651 wf_engine.setitemattrtext
2652 (itemtype => itemtype
2653 ,itemkey => itemkey
2654 ,aname => 'ERROR_STACK'
2655 ,avalue => substr(p_data,1,1999));
2656 end if;
2657 hr_utility.set_location('Leaving createEOErrorMSgNode : ', 30);
2658 END ;
2659
2660 procedure writeXMLDocToClob(p_error_doc in xmldom.DOMDocument,
2661 p_error_log IN OUT nocopy CLOB)
2662 as
2663 error_log CLOB;
2664 charset VARCHAR2(64);
2665 begin
2666 IF(NOT xmldom.isnull(p_error_doc)) THEN
2667 SELECT v$nls_parameters.value INTO charset FROM v$nls_parameters
2668 WHERE v$nls_parameters.parameter='NLS_CHARACTERSET';
2669 DBMS_LOB.createTemporary(error_log, FALSE);
2670 xmldom.writeToClob(p_error_doc,error_log,charset);
2671 p_error_log:=error_log;
2672 DBMS_LOB.freetemporary(error_log);
2673 xmldom.freeDocument(p_error_doc);
2674 END IF;
2675 end;
2676
2677 PROCEDURE commit_transaction(
2678 p_transaction_id IN NUMBER,
2679 p_validate IN NUMBER DEFAULT hr_api.g_false_num,
2680 p_effective_date IN DATE DEFAULT SYSDATE,
2681 p_process_all_on_error IN NUMBER DEFAULT hr_api.g_false_num,
2682 p_status OUT nocopy VARCHAR2,
2683 p_error_log IN OUT nocopy CLOB)IS
2684 CURSOR csr_trn IS
2685 SELECT transaction_document
2686 FROM hr_api_transactions
2687 WHERE transaction_id = p_transaction_id;
2688
2689 CURSOR csr_trn_item IS
2690 SELECT item_type,item_key
2691 FROM hr_api_transactions
2692 WHERE transaction_id = p_transaction_id;
2693 rootNode xmldom.DOMNode;
2694 l_TXN_Node xmldom.DOMNode;
2695 l_AM_Node xmldom.DOMNode;
2696 l_TransCache_Node xmldom.DOMNode;
2697 l_EO_Node xmldom.DOMNode;
2698 l_CDATA_Node xmldom.DOMNode;
2699 l_EoApiMap_Node xmldom.DOMNode;
2700
2701
2702 l_EoApiMap_NodeList xmldom.DOMNodeList;
2703 l_EO_NodeList xmldom.DOMNodeList;
2704 l_TransCache_NodeList xmldom.DOMNodeList;
2705
2706 l_proc VARCHAR2(72) := g_package || 'commit_transaction';
2707 x_return_status VARCHAR2(1);
2708 l_pwac_return_status VARCHAR2(1);
2709 l_language VARCHAR2(2);
2710 l_EO_Api_Name VARCHAR2(1000);
2711
2712 l_EO_Object_Name VARCHAR2(1000);
2713 l_EO_Id VARCHAR2(1000);
2714 l_CDATA_Name VARCHAR2(1000);
2715 l_CDATA_Length NUMBER;
2716
2717 step_row csr_trn%ROWTYPE;
2718 item_row csr_trn_item%ROWTYPE;
2719
2720 -- error logging
2721 error_doc xmldom.DOMDocument;
2722 error_doc_main_node xmldom.DOMNode;
2723 error_doc_root_elmt xmldom.DOMElement;
2724 error_EO_Node xmldom.DOMNode;
2725 error_temp_Node xmldom.DOMNode;
2726 error_EO_elmt xmldom.DOMElement;
2727 charset VARCHAR2(64);
2728 error_log CLOB;
2729
2730 BEGIN
2731 SAVEPOINT commit_transaction_swi;
2732 hr_utility.set_location(' Entering:' || l_proc,10);
2733 -- Call Set_Transaction_Context
2734 hr_utility.set_location(' Calling set_transaction_context:' || l_proc,15);
2735 set_transaction_context(p_transaction_id);
2736
2737 -- If p_effective_date is not NULL then set it on the g_txn_ctx.EFFECTIVE_DATE
2738 IF ( p_effective_date IS NOT NULL ) THEN
2739 g_txn_ctx.EFFECTIVE_DATE:=p_effective_date;
2740 END IF;
2741 -- Call Set_Person_Context
2742 l_language:='US';
2743 hr_utility.set_location(' Calling set_person_context:' || l_proc,20);
2744 set_person_context( p_selected_person_id => g_txn_ctx.SELECTED_PERSON_ID,
2745 p_selected_assignment_id => g_txn_ctx.ASSIGNMENT_ID,
2746 p_effective_date => g_txn_ctx.EFFECTIVE_DATE);
2747
2748 x_return_status := 'S';
2749 hr_utility.set_location(' Calling :hr_util_misc_ss.seteffectivedate' || l_proc,25);
2750 hr_utility.set_location(' Entering For Loop' || l_proc,35);
2751 -- new code
2752 OPEN csr_trn;
2753 FETCH csr_trn INTO step_row;
2754 -- hsundar: Do the Document processing only when the Txn_document is not null
2755 -- hsundar: If the Document is NULL just return the status as S
2756 IF step_row.transaction_document IS NOT NULL THEN
2757 -- Now get the <Transaction> Node
2758 rootNode := xmldom.makeNode(convertCLOBtoXMLElement(step_row.transaction_document));
2759
2760 -- Now get the <EOApiMap>
2761 l_EoApiMap_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(rootNode),'EoApiMap');
2762 IF (xmldom.getLength(l_EoApiMap_NodeList) > 0) THEN
2763 l_EoApiMap_Node :=xmldom.item(l_EoApiMap_NodeList,0);
2764 l_EO_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(l_EoApiMap_Node),'EO');
2765
2766 -- Put it into a Table
2767 FOR i IN 1..xmldom.getLength(l_EO_NodeList) LOOP
2768 l_EO_Node := xmldom.item(l_EO_NodeList,i-1);
2769 l_EO_Object_Name := xmldom.getAttribute(xmldom.makeElement(l_EO_Node),'Name');
2770 l_EO_Node := xmldom.getFirstChild(l_EO_Node);
2771 l_EO_Api_Name :=xmldom.getNodeValue(l_EO_Node);
2772 --g_api_map(l_EO_Object_Name) := l_EO_Api_Name;
2773 -- Maintain Parallel Arrays
2774 -- 1. Put the EO Name in g_EO_Name_map
2775 g_EO_Name_map(i) := l_EO_Object_Name;
2776 -- 2. Put the EO's API name
2777 g_EO_ApiName_map(i):= l_EO_Api_Name;
2778 END LOOP;
2779
2780
2781 -- Now get the <TransCache> Node
2782 l_TransCache_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(rootNode),'TransCache');
2783 l_TransCache_Node :=xmldom.item(l_TransCache_NodeList,0);
2784 -- Now get the <AM> Node
2785 l_AM_Node :=xmldom.getFirstChild(l_TransCache_Node);
2786 -- Now get the </cd> Node and get its Sibling --> <TXN>
2787 l_TXN_Node :=xmldom.getNextSibling(xmldom.getFirstChild(l_AM_Node));
2788
2789 -- Now get the list of all <EO> Nodes
2790 l_EO_NodeList :=xmldom.getChildrenByTagName(xmldom.makeElement(l_TXN_Node),'EO');
2791 --
2792 IF (xmldom.getLength(l_EO_NodeList) > 0) THEN
2793 -- Loop for it
2794 FOR i IN 1..xmldom.getLength(l_EO_NodeList) LOOP
2795 l_EO_Node := xmldom.item(l_EO_NodeList,i-1);
2796 l_pwac_return_status := 'S';
2797
2798 BEGIN
2799 l_pwac_return_status:=process_api_internal(
2800 p_transaction_id => p_transaction_id,
2801 p_root_node => l_EO_Node,
2802 p_validate => p_validate, -- 5919836
2803 p_effective_date => p_effective_date,
2804 p_return_status => x_return_status
2805 );
2806 EXCEPTION
2807 WHEN g_process_api_internal_error THEN
2808 x_return_status := set_status(x_return_status,'E');
2809 -- read the fnd msg pub for errors and log them to error doc
2810 IF(xmldom.isnull(error_doc)) THEN -- first error condition
2811 -- create the new empty document
2812 error_doc := xmldom.newDOMDocument;
2813 error_doc_main_node := xmldom.makeNode(error_doc);
2814 -- create the root element to hold txn id
2815 -- error_doc_root_elmt
2816 error_doc_root_elmt:= xmldom.createElement(error_doc, 'Transaction');
2817 xmldom.setAttribute(error_doc_root_elmt,'Id', p_transaction_id);
2818 error_doc_main_node :=xmldom.appendChild(error_doc_main_node, xmldom.makeNode(error_doc_root_elmt));
2819 END IF;
2820
2821 -- add the EO node to the error doc
2822 error_EO_elmt := xmldom.createElement(error_doc, 'EO');
2823 -- need the actual nested EO which has errored and its cdata node
2824 xmldom.setAttribute(error_EO_elmt, 'Name', g_processing_EO_name);
2825 xmldom.setAttribute(error_EO_elmt, 'CDATA', g_processing_EO_cdatavalue);
2826 --
2827 error_EO_Node :=xmldom.makeNode(error_EO_elmt);
2828
2829 OPEN csr_trn_item;
2830 FETCH csr_trn_item INTO item_row;
2831 createEOErrorMSgNode(error_doc,error_EO_Node,item_row.item_type,item_row.item_key);
2832 error_temp_Node:=xmldom.appendChild(error_doc_main_node,error_EO_Node);
2833 -- see if we need to progress on the siblings ?
2834 IF p_process_all_on_error = hr_api.g_false_num THEN
2835 RAISE g_process_api_internal_error;
2836 END IF;
2837
2838 END;
2839 x_return_status := set_status(x_return_status,l_pwac_return_status);
2840 END LOOP;
2841 END IF; -- End of if where we check if we have some EO Nodes
2842 END IF; -- End of if where we check if we have EOAPIMAP Nodes
2843 END IF; -- End of if where we check if the txn_doc is null
2844 CLOSE csr_trn;
2845
2846 writeXMLDocToClob(error_doc ,p_error_log );
2847
2848 hr_utility.set_location(' Exiting For Loop:' || l_proc,40);
2849 IF p_validate = hr_api.g_true_num THEN
2850 hr_utility.set_location(' p_validate=TRUE:' || l_proc,45);
2851 RAISE hr_api.validate_enabled;
2852 END IF;
2853 -- Return the status to the calling procedure
2854 -- hsundar: There is no need to commit as Work-Flow takes care of it implicitly
2855 /*if p_validate = hr_api.g_false_num then
2856 hr_utility.set_location('Commiting as p_validate=FALSE:' || l_proc,50);
2857 COMMIT;
2858 END IF; */
2859
2860 hr_utility.set_location(' Exiting:' || l_proc,55);
2861
2862 --return x_return_status;
2863 p_status :=x_return_status;
2864 -- Moved the exception Block out of the for loop
2865
2866 EXCEPTION
2867 WHEN g_process_api_internal_error THEN
2868 p_status :=x_return_status;
2869 writeXMLDocToClob(error_doc ,p_error_log );
2870 WHEN hr_utility.hr_error THEN
2871 --do something here
2872 hr_utility.set_location('Exception:hr_utility.hr_error' || l_proc,555);
2873 ROLLBACK TO commit_transaction_swi;
2874 RAISE;
2875 WHEN hr_api.validate_enabled THEN
2876 -- As the Validate_Enabled exception has been raised
2877 -- we must rollback to the savepoint
2878 hr_utility.set_location('Exception:hr_api.validate_enabled' || l_proc,560);
2879 ROLLBACK TO commit_transaction_swi;
2880 p_status :=x_return_status;
2881 WHEN others THEN
2882 hr_utility.set_location('Exception:others' || l_proc,565);
2883 ROLLBACK TO commit_transaction_swi;
2884 RAISE;
2885
2886 END commit_transaction;
2887
2888
2889
2890 end hr_transaction_swi;
2891