[Home] [Help]
PACKAGE BODY: APPS.HR_APPROVAL_SS
Source
1 PACKAGE BODY HR_APPROVAL_SS AS
2 /* $Header: hraprvlss.pkb 120.22.12010000.10 2009/01/29 13:47:29 pvelugul ship $ */
3
4 -- Package Variables
5 --
6 g_package constant varchar2(25) := 'HR_APPROVAL_SS.';
7 g_debug boolean ;
8 g_no_transaction_id exception;
9 g_wf_not_initialzed exception;
10 g_wf_error_state exception;
11 g_invalid_responsibility exception;
12 g_transaction_status_invalid exception;
13
14
15 -- cursor determines if an attribute exists
16 cursor csr_wiav (p_item_type in varchar2
17 ,p_item_key in varchar2
18 ,p_name in varchar2)
19 IS
20 select 1
21 from wf_item_attribute_values wiav
22 where wiav.item_type = p_item_type
23 and wiav.item_key = p_item_key
24 and wiav.name = p_name;
25
26
27 procedure create_item_attrib_if_notexist(itemtype in varchar2,
28 itemkey in varchar2,
29 aname in varchar2,
30 text_value in varchar2,
31 number_value in number,
32 date_value in date )is
33 --
34 l_dummy number(1);
35 c_proc constant varchar2(60) := 'create_item_attrib_if_notexist';
36 begin
37 g_debug := hr_utility.debug_enabled;
38 if g_debug then
39 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
40 end if;
41
42
43 if g_debug then
44 hr_utility.set_location('opening cursor csr_wiav with itemtype:'|| itemtype ||' , itemkey: '|| itemkey ||' , aname: '|| aname, 1);
45 end if;
46 -- open the cursor to determine if the a
47 open csr_wiav(itemtype,itemkey,aname);
48 fetch csr_wiav into l_dummy;
49 if csr_wiav%notfound then
50 --
51 -- item attribute does not exist so create it
52 if g_debug then
53 hr_utility.set_location('calling wf_engine.additemattr for aname:'||aname,2);
54 end if;
55 wf_engine.additemattr
56 (itemtype => itemtype
57 ,itemkey => itemkey
58 ,aname => aname
59 ,text_value=>text_value
60 ,number_value=>number_value
61 ,date_value=>date_value);
62 end if;
63
64
65 if csr_wiav%found then
66 -- set the values as per the type
67 if(text_value is not null) then
68 if g_debug then
69 hr_utility.set_location('calling wf_engine.setitemattrtext for text_value:'|| text_value,3);
70 end if;
71 wf_engine.setitemattrtext(itemtype,itemkey,aname,text_value);
72 end if;
73
74 if(number_value is not null) then
75 if g_debug then
76 hr_utility.set_location('calling wf_engine.setitemattrnumber for text_value:'|| number_value,4);
77 end if;
78 wf_engine.setitemattrnumber(itemtype,itemkey,aname,number_value);
79 end if;
80
81 if(date_value is not null) then
82 if g_debug then
83 hr_utility.set_location('calling wf_engine.setitemattrDate for text_value:'|| date_value,5);
84 end if;
85 wf_engine.setitemattrDate(itemtype,itemkey,aname,date_value);
86 end if;
87
88 end if;
89
90 close csr_wiav;
91
92 if (g_debug ) then
93 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
94 end if;
95
96 exception
97 when others then
98 if g_debug then
99 hr_utility.set_location('Error in create_item_attrib_if_notexist SQLERRM' ||' '||to_char(SQLCODE),20);
100 end if;
101 raise;
102
103 end create_item_attrib_if_notexist;
104
105
106 PROCEDURE set_custom_wf_globals
107 (p_itemtype in varchar2
108 ,p_itemkey in varchar2)
109 IS
110 -- Local Variables
111 l_proc constant varchar2(100) := g_package ||'.'|| 'set_custom_wf_globals';
112 BEGIN
113 g_debug := hr_utility.debug_enabled;
114 hr_utility.set_location('Entering: '|| l_proc,1);
115
116 if g_debug then
117 hr_utility.set_location('Setting hr_approval_custom.g_itemtype as :'|| p_itemtype,2);
118 hr_utility.set_location('Setting hr_approval_custom.g_itemkey as :'|| p_itemkey,3);
119 end if;
120
121 hr_approval_custom.g_itemtype := p_itemtype;
122 hr_approval_custom.g_itemkey := p_itemkey;
123 hr_utility.set_location('Leaving: '|| l_proc,10);
124 END set_custom_wf_globals;
125
126 function getOAFPageActId(p_item_type in wf_items.item_type%type,
127 p_item_key in wf_items.item_key%type) return number
128 is
129 -- local variables
130 c_proc constant varchar2(30) := 'storeApproverDetails';
131 ln_activity_id WF_ITEM_ACTIVITY_STATUSES.process_activity%type;
132 begin
133 g_debug := hr_utility.debug_enabled;
134 if g_debug then
135 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
136 end if;
137
138 -- get the blockid value corresponding to the UI page
139 SELECT process_activity
140 into ln_activity_id
141 from
142 (select process_activity
143 FROM WF_ITEM_ACTIVITY_STATUSES IAS
144 WHERE ias.item_type = p_item_type
145 and ias.item_key = p_item_key
146 and ias.activity_status = 'NOTIFIED'
147 and ias.process_activity in (
148 select wpa.instance_id
149 FROM WF_PROCESS_ACTIVITIES WPA,
150 WF_ACTIVITY_ATTRIBUTES WAA,
151 WF_ACTIVITIES WA,
152 WF_ITEMS WI
153 WHERE wpa.process_item_type = ias.item_type
154 and wa.item_type = wpa.process_item_type
155 and wa.name = wpa.activity_name
156 and wi.item_type = ias.item_type
157 and wi.item_key = ias.item_key
158 and wi.begin_date >= wa.begin_date
159 and wi.begin_date < nvl(wa.end_date,wi.begin_date+1)
160 and waa.activity_item_type = wa.item_type
161 and waa.activity_name = wa.name
162 and waa.activity_version = wa.version
163 and waa.type = 'FORM'
164 )
165 order by begin_date desc)
166 where rownum<=1;
167
168 if (g_debug ) then
169 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
170 end if;
171
172 return ln_activity_id;
173
174
175 exception
176 when others then
177 raise;
178
179
180 end getOAFPageActId;
181
182 procedure handleArchive( p_item_type in varchar2,
183 p_item_key in varchar2,
184 p_act_id in number,
185 funmode in varchar2,
186 result out nocopy varchar2 )
187 is
188 -- local variables
189 c_proc constant varchar2(30) := 'handleArchive';
190 -- Variables required for AME API
191 c_application_id integer;
192 c_transaction_id varchar2(25);
193 c_transaction_type varchar2(25);
194 c_next_approvers ame_util.approverstable2;
195 c_approvalprocesscompleteynout ame_util.charType;
196 l_current_forward_to_username wf_users.name%type;
197 lv_result_code WF_ITEM_ACTIVITY_STATUSES.activity_result_code%type;
198 lv_result_display varchar2(250);
199 lv_test_result_code varchar2(250);
200 lv_comments wf_item_attribute_values.text_value%type;
201 lv_recipient varchar2(250);
202 lv_new_transaction varchar2(250);
203 l_cpersonId VARCHAR2(15);
204 begin
205 g_debug := hr_utility.debug_enabled;
206
207 if g_debug then
208 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
209 end if;
210
211 -- get the transaction id of the sshr transaction
212 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
213 itemkey => p_item_key,
214 aname => 'TRANSACTION_ID',
215 ignore_notfound=>true);
216
217 if ( funmode = 'RESPOND' ) then
218 select text_value
219 into lv_result_code
220 from wf_notification_attributes
221 where notification_id=wf_engine.context_nid
222 and name='RESULT';
223
224 begin
225 hr_sflutil_ss.closeopensflnotification(c_transaction_id);
226 exception
227 when others then
228 null;
229 end ;
230
231
232
233 -- WF_NOTE
234 begin
235 select text_value
236 into lv_comments
237 from wf_notification_attributes
238 where notification_id=wf_engine.context_nid
239 and name='WF_NOTE';
240 exception
241 when others then
242 null;
243 end;
244
245 if g_debug then
246 hr_utility.set_location('processing notification response, notification_id:'||wf_engine.context_nid,2);
247 hr_utility.set_location('lv_result_code:'||lv_result_code,3);
248 end if;
249
250 -- possible actions
251 /* APPROVED :Approve
252 DEL :Delete
253 REJECTED :Reject
254 RESUBMIT :Resubmit
255 RETURNEDFORCORRECTION:Return for Correction
256 SFL :Saved For Later
257 START :Start Over
258 TIMEOUT :Timeout/No response
259
260 */
261 -- block update the approval action history
262 begin
263 if(lv_result_code ='APPROVED') then
264 if g_debug then
265 hr_utility.set_location('calling hr_trans_history_api.archive_approve',4);
266 end if;
267 -- use the new routing api's to handle archive
268 hr_trans_history_api.archive_approve(c_transaction_id,
269 wf_engine.context_nid,
270 wf_engine.context_user,
271 lv_comments);
272
273 elsif(lv_result_code='RESUBMIT') then
274 if g_debug then
275 hr_utility.set_location('calling hr_trans_history_api.archive_approve',4);
276 end if;
277 -- use the new routing api's to handle archive
278 hr_trans_history_api.archive_resubmit(c_transaction_id,
279 wf_engine.context_nid,
280 wf_engine.context_user,
281 wf_engine.GetItemAttrText(
282 p_item_type
283 ,p_item_key
284 ,'APPROVAL_COMMENT_COPY'));
285 elsif(lv_result_code='REJECTED') then
286
287 if g_debug then
288 hr_utility.set_location('calling hr_trans_history_api.archive_reject',5);
289 end if;
290 -- archive the action to history
291 hr_trans_history_api.archive_reject(c_transaction_id,
292 wf_engine.context_nid,
293 wf_engine.context_user,
294 lv_comments);
295 elsif(lv_result_code='RETURNEDFORCORRECTION') then
296 if g_debug then
297 hr_utility.set_location('calling hr_trans_history_api.archive_rfc',6);
298 end if;
299 -- archive the action to history
300 hr_trans_history_api.archive_rfc(c_transaction_id,
301 wf_engine.context_nid,
302 wf_engine.context_user,
303 lv_comments);
304 --wf_engine.context_user_comment);
305 elsif(lv_result_code='DEL') then
306 if g_debug then
307 hr_utility.set_location('calling hr_trans_history_api.archive_delete',7);
308 end if;
309 -- archive the action to history
310 hr_trans_history_api.archive_delete(c_transaction_id,
311 wf_engine.context_nid,
312 wf_engine.context_user,
313 lv_comments);
314 end if;
315 exception
316 when others then
317 raise;
318 end;
319 elsif( funmode = 'FORWARD' ) then
320 -- FORWARD - When a notification recipient forwards the notification.
321 hr_trans_history_api.archive_forward(c_transaction_id,
322 wf_engine.context_nid,
323 wf_engine.context_user,
324 wf_engine.context_user_comment);
325 null;
326 elsif( funmode = 'TRANSFER' ) then
327 -- TRANSFER - When a notification recipient transfers the notification.
328 hr_trans_history_api.archive_transfer(c_transaction_id,
329 wf_engine.context_nid,
330 wf_engine.context_user,
331 wf_engine.context_user_comment);
332 -- QUESTION
333 elsif( funmode = 'QUESTION' ) then
334 hr_trans_history_api.archive_req_moreinfo(c_transaction_id,
335 wf_engine.context_nid,
336 wf_engine.context_user,
337 wf_engine.context_user_comment);
338
339 elsif( funmode = 'ANSWER' ) then
340 hr_trans_history_api.archive_answer_moreinfo(c_transaction_id,
341 wf_engine.context_nid,
342 wf_engine.context_user,
343 wf_engine.context_user_comment);
344 elsif( funmode = 'TIMEOUT' ) then
345 if hr_multi_tenancy_pkg.is_multi_tenant_system then
346 l_cpersonId := NVL( wf_engine.getItemAttrText (
347 itemtype => p_item_type
348 ,itemkey => p_item_key
349 ,aname => 'CREATOR_PERSON_ID'),-1);
350
351 hr_multi_tenancy_pkg.set_context_for_person(l_cpersonId);
352 end if;
353 if g_debug then
354 hr_utility.set_location('calling hr_trans_history_api.archive_timeout',7);
355 end if;
356 lv_new_transaction := wf_engine.getitemattrtext(p_item_type,
357 p_item_key,
358 'HR_NEW_TRANSACTION',true);
359
360 if( INSTR(lv_new_transaction,'TIMEOUT') > 0) then
361
362 --Collect the username for whom timeout happened
363 begin
364 select RECIPIENT_ROLE
365 into lv_recipient
366 from wf_notifications
367 where notification_id=wf_engine.context_nid;
368 exception
369 when others then
370 null;
371 end;
372 -- archive the action to history
373 hr_trans_history_api.archive_timeout(c_transaction_id,
374 wf_engine.context_nid,
375 lv_recipient,
376 lv_comments);
377 end if;
378 end if;
379
380 if(lv_result_code is null) then
381 result := wf_engine.eng_null;
382 else
383 result:= 'COMPLETE:'||lv_result_code;
384 end if;
385
386 if g_debug then
387 hr_utility.set_location('returning with the result'||result,8);
388 end if;
389
390
391 if (g_debug ) then
392 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
393 end if;
394 exception
395 when others then
396 hr_utility.set_location('Error:'|| g_package||'.'||c_proc, 10);
397 raise;
398
399 end handleArchive;
400
401 procedure storeApproverDetails(p_item_type in varchar2,
402 p_item_key in varchar2)
403 is
404 -- local variables
405 c_proc constant varchar2(30) := 'storeApproverDetails';
406 l_current_forward_to_id per_people_f.person_id%type;
407 l_current_forward_to_origSys wf_users.orig_system%type;
408 l_current_forward_to_username wf_users.name%type;
409 l_current_forward_to_disp_name wf_users.display_name%type;
410
411 begin
412 g_debug := hr_utility.debug_enabled;
413 if g_debug then
414 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
415 end if;
416 -- get the current forward to approver details
417 l_current_forward_to_id :=
418 nvl(wf_engine.GetItemAttrNumber
419 (itemtype => p_item_type
420 ,itemkey => p_item_key
421 ,aname => 'FORWARD_TO_PERSON_ID'),
422 wf_engine.GetItemAttrNumber
423 (itemtype => p_item_type
424 ,itemkey => p_item_key
425 ,aname => 'CREATOR_PERSON_ID'));
426
427 create_item_attrib_if_notexist(itemtype => p_item_type
428 ,itemkey => p_item_key
429 ,aname => 'FORWARD_FROM_ORIG_SYS_ATTR'
430 ,text_value=>null
431 ,number_value=>null,
432 date_value=>null
433 );
434
435 create_item_attrib_if_notexist(itemtype => p_item_type
436 ,itemkey => p_item_key
437 ,aname => 'FORWARD_TO_ORIG_SYS_ATTR'
438 ,text_value=>null
439 ,number_value=>null,
440 date_value=>null
441 );
442
443 l_current_forward_to_origSys := nvl( wf_engine.GetItemAttrText
444 (itemtype => p_item_type
445 ,itemkey => p_item_key
446 ,aname => 'FORWARD_TO_ORIG_SYS_ATTR'
447 ,ignore_notfound=>true),'PER');
448
449
450
451 if g_debug then
452 hr_utility.set_location('calling wf_directory.GetRoleName for p_orig_system:'|| l_current_forward_to_origSys ||' and p_orig_system_id:'|| l_current_forward_to_id,2);
453 end if;
454
455 wf_directory.GetRoleName--GetUserName
456 (p_orig_system => l_current_forward_to_origSys
457 ,p_orig_system_id => l_current_forward_to_id
458 ,p_name => l_current_forward_to_username
459 ,p_display_name => l_current_forward_to_disp_name);
460
461 -- set the passed approver values to the forward to item attributes
462 wf_engine.SetItemAttrText
463 (itemtype => p_item_type
464 ,itemkey => p_item_key
465 ,aname => 'FORWARD_TO_ORIG_SYS_ATTR'
466 ,avalue =>wf_engine.getItemAttrText
467 (itemtype => p_item_type
468 ,itemkey => p_item_key
469 ,aname =>'HR_APR_ORIG_SYSTEM_ATTR'));
470
471 wf_engine.SetItemAttrNumber
472 (itemtype => p_item_type
473 ,itemkey => p_item_key
474 ,aname => 'FORWARD_TO_PERSON_ID'
475 ,avalue =>wf_engine.getItemAttrNumber
476 (itemtype => p_item_type
477 ,itemkey => p_item_key
478 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR'));
479 --
480 wf_engine.SetItemAttrText
481 (itemtype => p_item_type
482 ,itemkey => p_item_key
483 ,aname => 'FORWARD_TO_USERNAME'
484 ,avalue => wf_engine.getItemAttrText
485 (itemtype => p_item_type
486 ,itemkey => p_item_key
487 ,aname => 'HR_APR_NAME_ATTR'));
488
489 --
490 Wf_engine.SetItemAttrText
491 (itemtype => p_item_type
492 ,itemkey => p_item_key
493 ,aname => 'FORWARD_TO_DISPLAY_NAME'
494 ,avalue => wf_engine.getItemAttrText
495 (itemtype => p_item_type
496 ,itemkey => p_item_key
497 ,aname => 'HR_APR_DISPLAY_NAME_ATTR'));
498 --
499 -- set forward from to old forward to
500 --
501 wf_engine.SetItemAttrNumber
502 (itemtype => p_item_type
503 ,itemkey => p_item_key
504 ,aname => 'FORWARD_FROM_PERSON_ID'
505 ,avalue => l_current_forward_to_id);
506
507 -- FORWARD_FROM_ORIG_SYS_ATTR
508 wf_engine.SetItemAttrText
509 (itemtype => p_item_type
510 ,itemkey => p_item_key
511 ,aname => 'FORWARD_FROM_ORIG_SYS_ATTR'
512 ,avalue => l_current_forward_to_origSys);
513
514
515 --
516 -- Get the username and display name for forward from person
517 -- and save to item attributes
518 --
519 if g_debug then
520 hr_utility.set_location('calling wf_directory.GetRoleName for p_orig_system:'|| l_current_forward_to_origSys ||' and p_orig_system_id:'|| l_current_forward_to_id,2);
521 end if;
522 wf_directory.GetRoleName--GetUserName
523 (p_orig_system => l_current_forward_to_origSys
524 ,p_orig_system_id => l_current_forward_to_id
525 ,p_name => l_current_forward_to_username
526 ,p_display_name => l_current_forward_to_disp_name);
527 --
528 wf_engine.SetItemAttrText
529 (itemtype => p_item_type
530 ,itemkey => p_item_key
531 ,aname => 'FORWARD_FROM_USERNAME'
532 ,avalue => l_current_forward_to_username);
533 --
534 wf_engine.SetItemAttrText
535 (itemtype => p_item_type
536 ,itemkey => p_item_key
537 ,aname => 'FORWARD_FROM_DISPLAY_NAME'
538 ,avalue => l_current_forward_to_disp_name);
539
540 -- store AME specific approver details
541
542 -- FORWARD_TO_ITEM_CLASS_ATTR
543 create_item_attrib_if_notexist(itemtype => p_item_type
544 ,itemkey => p_item_key
545 ,aname => 'FORWARD_TO_ITEM_CLASS_ATTR'
546 ,text_value=>wf_engine.getItemAttrText
547 (itemtype => p_item_type
548 ,itemkey => p_item_key
549 ,aname => 'HR_APR_ITEM_CLASS_ATTR')
550 ,number_value=>null,
551 date_value=>null
552 );
553
554
555 -- FORWARD_TO_ITEM_ID_ATTR
556 create_item_attrib_if_notexist(itemtype => p_item_type
557 ,itemkey => p_item_key
558 ,aname => 'FORWARD_TO_ITEM_ID_ATTR'
559 ,text_value=>wf_engine.getItemAttrText
560 (itemtype => p_item_type
561 ,itemkey => p_item_key
562 ,aname => 'HR_APR_ITEM_ID_ATTR')
563 ,number_value=>null,
564 date_value=>null
565 );
566
567 -- FORWARD_TO_GROUPORCHAINID
568
569 create_item_attrib_if_notexist(itemtype => p_item_type
570 ,itemkey => p_item_key
571 ,aname => 'FORWARD_TO_GROUPORCHAINID'
572 ,text_value=>null
573 ,number_value=>wf_engine.getItemAttrNumber
574 (itemtype => p_item_type
575 ,itemkey => p_item_key
576 ,aname => 'HR_APR_GRPORCHN_ID_ATTR'),
577 date_value=>null
578 );
579
580
581 -- FORWARD_TO_ACTIONTYPEID_ATTR
582 create_item_attrib_if_notexist(itemtype => p_item_type
583 ,itemkey => p_item_key
584 ,aname => 'FORWARD_TO_ACTIONTYPEID_ATTR'
585 ,text_value=>wf_engine.getItemAttrText
586 (itemtype => p_item_type
587 ,itemkey => p_item_key
588 ,aname => 'HR_APR_ACTION_TYPE_ID_ATTR')
589 ,number_value=>null,
590 date_value=>null
591 );
592
593 if (g_debug ) then
594 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
595 end if;
596 exception
597 when others then
598 raise;
599 end storeApproverDetails;
600
601 procedure getNextCustomApprover(p_item_type in varchar2,
602 p_item_key in varchar2,
603 p_approvalprocesscompleteynout out nocopy varchar2,
604 p_approver_rec out nocopy ame_util.approverRecord2)
605 is
606 -- local variables
607 c_proc constant varchar2(30) := 'getNextCustomApprover';
608 l_creator_person_id per_people_f.person_id%type;
609 l_forward_to_person_id per_people_f.person_id%type;
610 l_current_forward_to_id per_people_f.person_id%type;
611 l_current_forward_from_id per_people_f.person_id%type;
612 ln_last_default_approver_id per_people_f.person_id%type;
613 ln_addntl_approvers NUMBER DEFAULT 0;
614 ln_approval_level NUMBER DEFAULT 0;
615 ln_curr_def_appr_index NUMBER DEFAULT 1;
616 ln_current_approver_index NUMBER ;
617 ln_last_def_approver NUMBER;
618 l_dummy VARCHAR2(100);
619 lv_dummy VARCHAR2(20);
620 lv_exists VARCHAR2(10);
621 lv_isvalid VARCHAR2(10);
622 lv_response VARCHAR2(10);
623 lv_item_name VARCHAR2(100);
624
625
626 cv_item_name constant VARCHAR2(20) := 'ADDITIONAL_APPROVER_';
627 cv_notifier_name constant VARCHAR2(9) := 'NOTIFIER_';
628
629
630
631 begin
632 g_debug := hr_utility.debug_enabled;
633 if g_debug then
634 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
635 end if;
636
637 --
638 l_creator_person_id := wf_engine.GetItemAttrNumber
639 (itemtype => p_item_type
640 ,itemkey => p_item_key
641 ,aname => 'CREATOR_PERSON_ID');
642 --
643 l_forward_to_person_id := wf_engine.GetItemAttrNumber
644 (itemtype => p_item_type
645 ,itemkey => p_item_key
646 ,aname =>'FORWARD_TO_PERSON_ID');
647
648 -- get the current forward to person
649 l_current_forward_to_id :=
650 nvl(wf_engine.GetItemAttrNumber
651 (itemtype => p_item_type
652 ,itemkey => p_item_key
653 ,aname => 'FORWARD_TO_PERSON_ID'),
654 wf_engine.GetItemAttrNumber
655 (itemtype => p_item_type
656 ,itemkey => p_item_key
657 ,aname => 'CREATOR_PERSON_ID'));
658
659
660 l_forward_to_person_id := NVL(l_forward_to_person_id,l_current_forward_to_id);
661
662 -- attribute to hold the last_default approver from the heirarchy tree.
663 OPEN csr_wiav(p_item_type,p_item_key,'LAST_DEFAULT_APPROVER');
664 FETCH csr_wiav into l_dummy;
665 IF csr_wiav%notfound THEN
666 -- create new wf_item_attribute_value to hold
667 hr_approval_wf.create_item_attrib_if_notexist
668 (p_item_type => p_item_type
669 ,p_item_key => p_item_key
670 ,p_name => 'LAST_DEFAULT_APPROVER');
671
672 wf_engine.SetItemAttrNumber
673 (itemtype => p_item_type,
674 itemkey => p_item_key,
675 aname => 'LAST_DEFAULT_APPROVER',
676 avalue => NULL);
677
678 END IF;
679 CLOSE csr_wiav;
680
681
682 -- 'LAST_DEFAULT_APPROVER'
683 ln_last_def_approver:= wf_engine.GetItemAttrNumber
684 (itemtype => p_item_type,
685 itemkey => p_item_key,
686 aname => 'LAST_DEFAULT_APPROVER'
687 );
688
689 ln_last_def_approver:= NVL(ln_last_def_approver,l_forward_to_person_id);
690
691 -- check if we have default approvers
692 lv_response := hr_approval_custom.Check_Final_approver
693 (p_forward_to_person_id => ln_last_def_approver
694 ,p_person_id => l_creator_person_id );
695
696 IF lv_response <>'N' THEN
697 p_approvalprocesscompleteynout := ame_util.booleanTrue;
698 return;
699 else
700 p_approvalprocesscompleteynout := ame_util.booleanFalse;
701 END IF;
702
703 -- check if we have reached the max limit on the approvers level
704 -- the level is based on the heirarchy tree.
705 -- get the approval level as conifgured by the HR Rep or Sys Admin
706 OPEN csr_wiav(p_item_type
707 ,p_item_key
708 ,'APPROVAL_LEVEL');
709
710 FETCH csr_wiav into l_dummy;
711 IF csr_wiav%notfound THEN
712 ln_approval_level := 0;
713 ELSE
714 ln_approval_level := wf_engine.GetItemAttrNumber
715 (itemtype => p_item_type,
716 itemkey => p_item_key,
717 aname => 'APPROVAL_LEVEL');
718 END IF; -- for csr_wiav%notfound
719 CLOSE csr_wiav;
720
721 IF ln_approval_level > 0 THEN
722 -- get the current approval level reached
723 -- first check if the attribute exists
724 OPEN csr_wiav(p_item_type
725 ,p_item_key
726 ,'CURRENT_DEF_APPR_INDEX');
727
728 FETCH csr_wiav into l_dummy;
729 IF csr_wiav%notfound THEN
730 NULL;
731 ELSE
732 ln_curr_def_appr_index := wf_engine.GetItemAttrNumber
733 (itemtype => p_item_type,
734 itemkey => p_item_key,
735 aname => 'CURRENT_DEF_APPR_INDEX');
736 END IF;-- for csr_wiav%notfound
737 CLOSE csr_wiav;
738
739 END IF; -- for ln_num_of_add_apprs > 0
740
741
742 -- Fix for the Bug # 1255826
743 IF (ln_approval_level> 0)
744
745 THEN
746 IF( ln_curr_def_appr_index < ln_approval_level)
747 THEN
748
749 p_approvalprocesscompleteynout := ame_util.booleanFalse;
750 ELSE
751 p_approvalprocesscompleteynout := ame_util.booleanTrue;
752 END IF;
753 ELSE
754 lv_response := hr_approval_custom.Check_Final_approver
755 (p_forward_to_person_id => ln_last_def_approver
756 ,p_person_id => l_creator_person_id );
757 if lv_response <>'N' then
758 p_approvalprocesscompleteynout := ame_util.booleanTrue ;
759 else
760 p_approvalprocesscompleteynout := ame_util.booleanFalse;
761 end if;
762 END IF;
763
764 -- get the next approver details
765 if(p_approvalprocesscompleteynout = ame_util.booleanFalse) then
766
767 l_current_forward_from_id:=null;
768 l_current_forward_to_id:=null;
769 ln_addntl_approvers:= null;
770 ln_current_approver_index:=null;
771 -- get the current forward from person
772 l_current_forward_from_id :=
773 nvl(wf_engine.GetItemAttrNumber
774 (itemtype => p_item_type
775 ,itemkey => p_item_key
776 ,aname => 'FORWARD_FROM_PERSON_ID'),
777 wf_engine.GetItemAttrNumber
778 (itemtype => p_item_type
779 ,itemkey => p_item_key
780 ,aname => 'CREATOR_PERSON_ID'));
781 -- get the current forward to person
782 l_current_forward_to_id :=
783 nvl(wf_engine.GetItemAttrNumber
784 (itemtype => p_item_type
785 ,itemkey => p_item_key
786 ,aname => 'FORWARD_TO_PERSON_ID'),
787 wf_engine.GetItemAttrNumber
788 (itemtype => p_item_type
789 ,itemkey => p_item_key
790 ,aname => 'CREATOR_PERSON_ID'));
791
792
793 -- get the total number of additional approvers for this transaction
794 ln_addntl_approvers := NVL(wf_engine.GetItemAttrNumber
795 (itemtype => p_item_type
796 ,itemkey => p_item_key
797 ,aname => 'ADDITIONAL_APPROVERS_NUMBER'),
798 0);
799
800 -- fix for the bug # 1252070
801
802 -- attribute to hold the last_default approver from the heirarchy tree.
803 OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_APPROVER_INDEX');
804 FETCH csr_wiav into lv_dummy;
805 IF csr_wiav%notfound THEN
806 -- create new wf_item_attribute_value to hold
807 hr_approval_wf.create_item_attrib_if_notexist
808 (p_item_type => p_item_type
809 ,p_item_key => p_item_key
810 ,p_name => 'CURRENT_APPROVER_INDEX');
811
812 wf_engine.SetItemAttrNumber
813 (itemtype => p_item_type,
814 itemkey => p_item_key,
815 aname => 'CURRENT_APPROVER_INDEX',
816 avalue => NULL);
817
818 END IF;
819 CLOSE csr_wiav;
820
821
822
823
824
825 -- get the current_approver_index
826 ln_current_approver_index := NVL(wf_engine.GetItemAttrNumber
827 (itemtype => p_item_type
828 ,itemkey => p_item_key
829 ,aname => 'CURRENT_APPROVER_INDEX'),
830 0);
831 -- set the item name
832 lv_item_name := cv_item_name || to_char(ln_current_approver_index + 1);
833
834 -- check if we have additional approver for the next index.
835 -- Fix for the bug # 1255826
836 IF ln_current_approver_index <= ln_addntl_approvers
837 THEN
838 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
839 FETCH csr_wiav into lv_dummy;
840 IF csr_wiav%notfound THEN
841 lv_exists := 'N';
842 ELSE
843 lv_exists := 'Y';
844 lv_isvalid := wf_engine.GetItemAttrText
845 (itemtype => p_item_type,
846 itemkey => p_item_key,
847 aname => lv_item_name);
848 lv_isvalid := NVL(lv_isvalid,' ');
849
850 END IF;
851 CLOSE csr_wiav;
852 ELSE
853 lv_exists := 'N';
854 END IF;
855
856
857 IF lv_exists <>'N' AND lv_isvalid <>'DELETED' THEN
858 l_forward_to_person_id :=
859 wf_engine.GetItemAttrNumber
860 (itemtype => p_item_type,
861 itemkey => p_item_key,
862 aname => lv_item_name
863 );
864
865 ELSE
866 -- get the last default approver index
867
868 ln_last_default_approver_id := wf_engine.GetItemAttrNumber
869 (itemtype => p_item_type,
870 itemkey => p_item_key,
871 aname => 'LAST_DEFAULT_APPROVER');
872
873
874
875 -- get the next approver from the heirarchy tree.
876 -- fix for bug #2087458
877 -- the l_current_forward_to_id resetting was removed for default approver.
878 -- now the from column will show the last approver approved.
879 l_forward_to_person_id :=
880 hr_approval_custom.Get_Next_Approver
881 (p_person_id => NVL(ln_last_default_approver_id,
882 wf_engine.GetItemAttrNumber
883 (itemtype => p_item_type
884 ,itemkey => p_item_key
885 ,aname => 'CREATOR_PERSON_ID')));
886 -- set the last default approver id
887 -- 'LAST_DEFAULT_APPROVER'
888 wf_engine.SetItemAttrNumber
889 (itemtype => p_item_type,
890 itemkey => p_item_key,
891 aname => 'LAST_DEFAULT_APPROVER',
892 avalue => l_forward_to_person_id);
893 -- set cuurent approval levels reached
894 OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_DEF_APPR_INDEX');
895 FETCH csr_wiav into lv_dummy;
896 IF csr_wiav%notfound THEN
897 -- create new wf_item_attribute_value to hold
898 hr_approval_wf.create_item_attrib_if_notexist
899 (p_item_type => p_item_type
900 ,p_item_key => p_item_key
901 ,p_name => 'CURRENT_DEF_APPR_INDEX');
902
903 wf_engine.SetItemAttrNumber
904 (itemtype => p_item_type,
905 itemkey => p_item_key,
906 aname => 'CURRENT_DEF_APPR_INDEX',
907 avalue => 0);
908 ELSE
909 ln_curr_def_appr_index :=
910 wf_engine.GetItemAttrNumber
911 (itemtype => p_item_type,
912 itemkey => p_item_key,
913 aname => 'CURRENT_DEF_APPR_INDEX'
914 );
915 -- increment it and update the item attribute value
916 ln_curr_def_appr_index := ln_curr_def_appr_index + 1;
917 wf_engine.SetItemAttrNumber
918 (itemtype => p_item_type,
919 itemkey => p_item_key,
920 aname => 'CURRENT_DEF_APPR_INDEX',
921 avalue => ln_curr_def_appr_index);
922 END IF;
923 CLOSE csr_wiav;
924
925 END IF;
926
927 -- set the current_approver_index
928 wf_engine.SetItemAttrNumber (itemtype => p_item_type
929 ,itemkey => p_item_key
930 ,aname => 'CURRENT_APPROVER_INDEX'
931 ,avalue => (ln_current_approver_index + 1));
932
933
934 -- derive wf role details for the selected person id
935 if ( l_forward_to_person_id is not null ) then
936 wf_directory.GetRoleName--GetUserName
937 (p_orig_system => 'PER'
938 ,p_orig_system_id => l_forward_to_person_id
939 ,p_name => p_approver_rec.name
940 ,p_display_name => p_approver_rec.display_name);
941
942 p_approver_rec.orig_system:='PER';
943 p_approver_rec.orig_system_id:=l_forward_to_person_id;
944 p_approver_rec.approver_category:=ame_util.approvalApproverCategory;
945 end if;
946
947
948 end if;
949
950 if (g_debug ) then
951 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
952 end if;
953 exception
954 when others then
955 raise;
956 end getNextCustomApprover;
957
958 procedure populateApproverDetails(p_item_type in varchar2,
959 p_item_key in varchar2,
960 p_approverRec in ame_util.approverRecord2)
961 is
962 -- local variables
963 c_proc constant varchar2(60) := 'populateApproverDetails';
964 begin
965 g_debug := hr_utility.debug_enabled;
966
967 if g_debug then
968 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
969 end if;
970 -- create and/or populate approver details to item attributes
971 -- same structer as ame_util.approverRecord2
972 -- HR_APR_NAME_ATTR
973
974 create_item_attrib_if_notexist(itemtype=>p_item_type,
975 itemkey=>p_item_key,
976 aname=>'HR_APR_NAME_ATTR',
977 text_value=>p_approverRec.name,
978 number_value=>null,
979 date_value=>null);
980
981 -- HR_APR_ORIG_SYSTEM_ATTR
982
983 create_item_attrib_if_notexist(itemtype=>p_item_type,
984 itemkey=>p_item_key,
985 aname=>'HR_APR_ORIG_SYSTEM_ATTR',
986 text_value=>p_approverRec.orig_system,
987 number_value=>null,
988 date_value=>null);
989
990 -- HR_APR_ORIG_SYSTEM_ID_ATTR
991
992 create_item_attrib_if_notexist(itemtype=>p_item_type,
993 itemkey=>p_item_key,
994 aname=>'HR_APR_ORIG_SYSTEM_ID_ATTR',
995 text_value=>null,
996 number_value=>p_approverRec.orig_system_id,
997 date_value=>null);
998
999 -- HR_APR_DISPLAY_NAME_ATTR
1000
1001 create_item_attrib_if_notexist(itemtype=>p_item_type,
1002 itemkey=>p_item_key,
1003 aname=>'HR_APR_DISPLAY_NAME_ATTR',
1004 text_value=>p_approverRec.display_name,
1005 number_value=>null,
1006 date_value=>null);
1007
1008 -- HR_APR_CATEGORY_ATTR
1009
1010 create_item_attrib_if_notexist(itemtype=>p_item_type,
1011 itemkey=>p_item_key,
1012 aname=>'HR_APR_CATEGORY_ATTR',
1013 text_value=>p_approverRec.approver_category,
1014 number_value=>null,
1015 date_value=>null);
1016
1017 -- HR_APR_API_INSERTION_ATTR
1018
1019 create_item_attrib_if_notexist(itemtype=>p_item_type,
1020 itemkey=>p_item_key,
1021 aname=>'HR_APR_API_INSERTION_ATTR',
1022 text_value=>p_approverRec.api_insertion,
1023 number_value=>null,
1024 date_value=>null);
1025
1026 -- HR_APR_AUTHORITY_ATTR
1027
1028 create_item_attrib_if_notexist(itemtype=>p_item_type,
1029 itemkey=>p_item_key,
1030 aname=>'HR_APR_AUTHORITY_ATTR',
1031 text_value=>p_approverRec.authority,
1032 number_value=>null,
1033 date_value=>null);
1034
1035 -- HR_APR_APPROVAL_STATUS_ATTR
1036
1037 create_item_attrib_if_notexist(itemtype=>p_item_type,
1038 itemkey=>p_item_key,
1039 aname=>'HR_APR_APPROVAL_STATUS_ATTR',
1040 text_value=>p_approverRec.approval_status,
1041 number_value=>null,
1042 date_value=>null);
1043
1044 -- HR_APR_ACTION_TYPE_ID_ATTR
1045
1046 create_item_attrib_if_notexist(itemtype=>p_item_type,
1047 itemkey=>p_item_key,
1048 aname=>'HR_APR_ACTION_TYPE_ID_ATTR',
1049 text_value=>null,
1050 number_value=>p_approverRec.action_type_id,
1051 date_value=>null);
1052
1053 -- HR_APR_GRPORCHN_ID_ATTR
1054
1055 create_item_attrib_if_notexist(itemtype=>p_item_type,
1056 itemkey=>p_item_key,
1057 aname=>'HR_APR_GRPORCHN_ID_ATTR',
1058 text_value=>null,
1059 number_value=>p_approverRec.group_or_chain_id,
1060 date_value=>null);
1061
1062 -- HR_APR_OCCURRENCE_ATTR
1063
1064 create_item_attrib_if_notexist(itemtype=>p_item_type,
1065 itemkey=>p_item_key,
1066 aname=>'HR_APR_OCCURRENCE_ATTR',
1067 text_value=>null,
1068 number_value=>p_approverRec.occurrence,
1069 date_value=>null);
1070
1071 -- HR_APR_SOURCE_ATTR
1072
1073 create_item_attrib_if_notexist(itemtype=>p_item_type,
1074 itemkey=>p_item_key,
1075 aname=>'HR_APR_SOURCE_ATTR',
1076 text_value=>p_approverRec.source,
1077 number_value=>null,
1078 date_value=>null);
1079
1080 -- HR_APR_ITEM_CLASS_ATTR
1081
1082 create_item_attrib_if_notexist(itemtype=>p_item_type,
1083 itemkey=>p_item_key,
1084 aname=>'HR_APR_ITEM_CLASS_ATTR',
1085 text_value=>p_approverRec.item_class,
1086 number_value=>null,
1087 date_value=>null);
1088
1089 -- HR_APR_ITEM_ID_ATTR
1090
1091 create_item_attrib_if_notexist(itemtype=>p_item_type,
1092 itemkey=>p_item_key,
1093 aname=>'HR_APR_ITEM_ID_ATTR',
1094 text_value=>p_approverRec.item_id,
1095 number_value=>null,
1096 date_value=>null);
1097
1098 -- HR_APR_ITM_CLS_ORD_NUM_ATTR
1099
1100 create_item_attrib_if_notexist(itemtype=>p_item_type,
1101 itemkey=>p_item_key,
1102 aname=>'HR_APR_ITM_CLS_ORD_NUM_ATTR',
1103 text_value=>null,
1104 number_value=>p_approverRec.item_class_order_number,
1105 date_value=>null);
1106
1107 -- HR_APR_ITM_ORD_NUM_ATTR
1108
1109 create_item_attrib_if_notexist(itemtype=>p_item_type,
1110 itemkey=>p_item_key,
1111 aname=>'HR_APR_ITM_ORD_NUM_ATTR',
1112 text_value=>null,
1113 number_value=>p_approverRec.item_order_number,
1114 date_value=>null);
1115
1116 -- HR_APR_SUB_LST_ORD_NUM_ATTR
1117
1118 create_item_attrib_if_notexist(itemtype=>p_item_type,
1119 itemkey=>p_item_key,
1120 aname=>'HR_APR_SUB_LST_ORD_NUM_ATTR',
1121 text_value=>null,
1122 number_value=>p_approverRec.sub_list_order_number,
1123 date_value=>null);
1124
1125 -- HR_APR_ACT_TYP_ORD_NUM_ATTR
1126
1127 create_item_attrib_if_notexist(itemtype=>p_item_type,
1128 itemkey=>p_item_key,
1129 aname=>'HR_APR_ACT_TYP_ORD_NUM_ATTR',
1130 text_value=>null,
1131 number_value=>p_approverRec.action_type_order_number,
1132 date_value=>null);
1133
1134 -- HR_APR_GRPORCHN_ORD_NUM_ATTR
1135
1136 create_item_attrib_if_notexist(itemtype=>p_item_type,
1137 itemkey=>p_item_key,
1138 aname=>'HR_APR_GRPORCHN_ORD_NUM_ATTR',
1139 text_value=>null,
1140 number_value=>p_approverRec.group_or_chain_order_number,
1141 date_value=>null);
1142
1143 -- HR_APR_MEMBER_ORD_NUM_ATTR
1144
1145 create_item_attrib_if_notexist(itemtype=>p_item_type,
1146 itemkey=>p_item_key,
1147 aname=>'HR_APR_MEMBER_ORD_NUM_ATTR',
1148 text_value=>null,
1149 number_value=>p_approverRec.member_order_number,
1150 date_value=>null);
1151
1152 -- HR_APR_ORD_NUM_ATTR
1153
1154 create_item_attrib_if_notexist(itemtype=>p_item_type,
1155 itemkey=>p_item_key,
1156 aname=>'HR_APR_ORD_NUM_ATTR',
1157 text_value=>null,
1158 number_value=>p_approverRec.approver_order_number,
1159 date_value=>null);
1160
1161
1162 if (g_debug ) then
1163 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1164 end if;
1165
1166 exception
1167 when others then
1168 raise;
1169 end populateApproverDetails;
1170
1171 procedure getNextApproverRole(p_item_type in varchar2,
1172 p_item_key in varchar2,
1173 p_act_id in number,
1174 funmode in varchar2,
1175 result out nocopy varchar2 )
1176 is
1177 -- local variables
1178 c_proc constant varchar2(30) := 'getNextApproverRole';
1179 lv_current_approver_category varchar2(4000);
1180
1181 begin
1182 g_debug := hr_utility.debug_enabled;
1183
1184 if g_debug then
1185 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1186 end if;
1187 if ( funmode = wf_engine.eng_run ) then
1188 -- HR_APR_CATEGORY_ATTR
1189 lv_current_approver_category :=wf_engine.GetItemAttrText(itemtype => p_item_type ,
1190 itemkey => p_item_key,
1191 aname => 'HR_APR_CATEGORY_ATTR',
1192 ignore_notfound=>true);
1193 -- default approvals
1194 lv_current_approver_category := nvl(lv_current_approver_category,'A');
1195 if(lv_current_approver_category='A') then
1196 storeApproverDetails(p_item_type,p_item_key);
1197 result := 'COMPLETE:APPROVAL';
1198 else
1199 result := 'COMPLETE:FYI';
1200 end if;
1201 end if;
1202
1203
1204 if (g_debug ) then
1205 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1206 end if;
1207 exception
1208 when others then
1209 raise;
1210 end getNextApproverRole;
1211
1212
1213
1214 function isPreNonAMEFYIComplete( p_item_type in varchar2,
1215 p_item_key in varchar2) return boolean is
1216 lv_result varchar2(50);
1217 c_proc constant varchar2(60) := 'isPreNonAMEFYIComplete';
1218 begin
1219 g_debug := hr_utility.debug_enabled;
1220 if g_debug then
1221 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1222 end if;
1223
1224 -- call the initialize code
1225 --HR_DYNAMIC_APPROVAL_WEB.INITIALIZE_ITEM_ATTRIBUTES(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
1226
1227
1228 -- check if we need to go through this cycle or completed
1229 HR_DYNAMIC_APPROVAL_WEB.CHECK_ONSUBMIT_NOTIFIER(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
1230
1231 if(lv_result='COMPLETE:N') then
1232 HR_DYNAMIC_APPROVAL_WEB.GET_ONSUBMIT_NOTIFIER(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
1233
1234 -- ONSUB_FWD_TO_USERNAME
1235 create_item_attrib_if_notexist(itemtype=>p_item_type,
1236 itemkey=>p_item_key,
1237 aname=>'HR_APR_NAME_ATTR',
1238 text_value=>wf_engine.GetItemAttrText(itemtype => p_item_type ,
1239 itemkey => p_item_key,
1240 aname => 'ONSUB_FWD_TO_USERNAME',
1241 ignore_notfound=>true),
1242 number_value=>null,
1243 date_value=>null);
1244
1245
1246 -- fyiApproverCategory
1247
1248 create_item_attrib_if_notexist(itemtype => p_item_type
1249 ,itemkey => p_item_key
1250 ,aname => 'HR_APR_CATEGORY_ATTR'
1251 ,text_value=>ame_util.fyiApproverCategory
1252 ,number_value=>null,
1253 date_value=>null
1254 );
1255
1256
1257 --HR_APR_SUB_LST_ORD_NUM_ATTR
1258 create_item_attrib_if_notexist(itemtype => p_item_type
1259 ,itemkey => p_item_key
1260 ,aname => 'HR_APR_SUB_LST_ORD_NUM_ATTR'
1261 ,text_value=>null
1262 ,number_value=>1,
1263 date_value=>null
1264 );
1265 create_item_attrib_if_notexist(itemtype => p_item_type
1266 ,itemkey => p_item_key
1267 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR'
1268 ,text_value=>null
1269 ,number_value=>null,
1270 date_value=>null
1271 );
1272 wf_engine.SetItemAttrNumber
1273 (itemtype => p_item_type
1274 ,itemkey => p_item_key
1275 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR'
1276 ,avalue=>null);
1277
1278 if (g_debug ) then
1279 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 11);
1280 end if;
1281 return false;
1282 else
1283 if (g_debug ) then
1284 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 12);
1285 end if;
1286 return true;
1287 end if;
1288 exception
1289 when others then
1290 if g_debug then
1291 hr_utility.set_location('Error in isPreNonAMEFYIComplete SQLERRM' ||' '||to_char(SQLCODE),20);
1292 end if;
1293 raise;
1294 end isPreNonAMEFYIComplete;
1295
1296
1297 function isPostNonAMEFYIComplete( p_item_type in varchar2,
1298 p_item_key in varchar2) return boolean is
1299 lv_result varchar2(50);
1300 c_proc constant varchar2(60) := 'isPostNonAMEFYIComplete';
1301 begin
1302 g_debug := hr_utility.debug_enabled;
1303
1304 if g_debug then
1305 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1306 end if;
1307
1308 -- fyiApproverCategory
1309 HR_DYNAMIC_APPROVAL_WEB.CHECK_ONAPPROVAL_NOTIFIER(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
1310
1311 if(lv_result='COMPLETE:N') then
1312
1313 HR_DYNAMIC_APPROVAL_WEB.GET_ONAPPROVAL_NOTIFIER(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
1314
1315 -- ONAPPR_FWD_TO_USERNAME
1316
1317 create_item_attrib_if_notexist(itemtype=>p_item_type,
1318 itemkey=>p_item_key,
1319 aname=>'HR_APR_NAME_ATTR',
1320 text_value=>wf_engine.GetItemAttrText(itemtype => p_item_type ,
1321 itemkey => p_item_key,
1322 aname => 'ONAPPR_FWD_TO_USERNAME',
1323 ignore_notfound=>true),
1324 number_value=>null,
1325 date_value=>null);
1326
1327
1328 create_item_attrib_if_notexist(itemtype => p_item_type
1329 ,itemkey => p_item_key
1330 ,aname => 'HR_APR_CATEGORY_ATTR'
1331 ,text_value=>ame_util.fyiApproverCategory
1332 ,number_value=>null,
1333 date_value=>null
1334 );
1335
1336
1337 --HR_APR_SUB_LST_ORD_NUM_ATTR
1338 create_item_attrib_if_notexist(itemtype => p_item_type
1339 ,itemkey => p_item_key
1340 ,aname => 'HR_APR_SUB_LST_ORD_NUM_ATTR'
1341 ,text_value=>null
1342 ,number_value=>3,
1343 date_value=>null
1344 );
1345
1346 create_item_attrib_if_notexist(itemtype => p_item_type
1347 ,itemkey => p_item_key
1348 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR'
1349 ,text_value=>null
1350 ,number_value=>null,
1351 date_value=>null
1352 );
1353 wf_engine.SetItemAttrNumber
1354 (itemtype => p_item_type
1355 ,itemkey => p_item_key
1356 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR'
1357 ,avalue=>null);
1358 if (g_debug ) then
1359 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 11);
1360 end if;
1361 return false;
1362 else
1363 if (g_debug ) then
1364 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 12);
1365 end if;
1366 return true;
1367 end if;
1368 exception
1369 when others then
1370 if g_debug then
1371 hr_utility.set_location('Error in isPostNonAMEFYIComplete SQLERRM' ||' '||to_char(SQLCODE),20);
1372 end if;
1373 raise;
1374
1375 end isPostNonAMEFYIComplete;
1376
1377
1378
1379 procedure isFinalApprover( p_item_type in varchar2,
1380 p_item_key in varchar2,
1381 p_act_id in number,
1382 funmode in varchar2,
1383 result out nocopy varchar2 )
1384 AS
1385 -- Local Variables
1386 l_proc constant varchar2(61) := g_package||'isFinalApprover';
1387 c_proc constant varchar2(60) := 'isFinalApprover';
1388 l_name varchar2(60);
1389
1390 -- Variables required for AME API
1391 c_application_id integer;
1392 c_transaction_id varchar2(25);
1393 c_transaction_type varchar2(25);
1394 c_next_approvers ame_util.approverstable2;
1395 c_approvalprocesscompleteynout varchar2(1) ;
1396 c_approver_to_notify_rec ame_util.approverRecord2;
1397 error_message_text varchar2(2000);
1398
1399 --
1400
1401 BEGIN
1402 g_debug := hr_utility.debug_enabled;
1403 c_approvalprocesscompleteynout := ame_util.booleanFalse;
1404
1405 if g_debug then
1406 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1407 end if;
1408
1409 error_message_text := wf_engine.GetItemAttrText(itemtype => p_item_type ,
1410 itemkey => p_item_key,
1411 aname => 'ERROR_MESSAGE_TEXT',
1412 ignore_notfound=>true);
1413
1414
1415 if(error_message_text is not null) then
1416
1417 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_MESSAGE_TEXT',null);
1418 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_ITEM_TYPE',null);
1419 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_ITEM_KEY',null);
1420
1421 end if;
1422
1423 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
1424 itemkey => p_item_key,
1425 aname => 'HR_AME_TRAN_TYPE_ATTR');
1426
1427 if ( funmode = 'RUN' ) then
1428
1429 -- fix for bug 4454439
1430 begin
1431 -- re-intialize the performer roles
1432 hr_approval_ss.reinitperformerroles(p_notification_id=>null
1433 ,p_transaction_id=>null
1434 ,p_item_type=>p_item_type
1435 ,p_item_key=>p_item_key);
1436 exception
1437 when others then
1438 null;
1439 end;
1440
1441 -- -----------------------------------------------------------------------
1442 -- expose the wf control variables to the custom package
1443 -- -----------------------------------------------------------------------
1444 -- Needed for backward compatibility
1445 if g_debug then
1446 hr_utility.set_location('calling set_custom_wf_globals with p_item_type: '|| p_item_type ||' and p_item_key:' || p_item_key,2);
1447 end if;
1448
1449 set_custom_wf_globals
1450 (p_itemtype => p_item_type
1451 ,p_itemkey => p_item_key);
1452
1453 -- check if the pre-notification process for non-AME FYI is completed
1454 if g_debug then
1455 hr_utility.set_location('calling isPreNonAMEFYIComplete with p_item_type: '|| p_item_type ||' and p_item_key:' || p_item_key,3);
1456 end if;
1457
1458 if(NOT isPreNonAMEFYIComplete(p_item_type,p_item_key)) then
1459 result := 'COMPLETE:'||'N';
1460 return;
1461 end if;
1462
1463 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1464 itemkey => p_item_key,
1465 aname => 'HR_AME_APP_ID_ATTR');
1466 c_application_id := nvl(c_application_id,800);
1467 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1468 itemkey => p_item_key,
1469 aname => 'TRANSACTION_ID');
1470 if(c_transaction_type is not null) then
1471 begin
1472 if g_debug then
1473 hr_utility.set_location('calling ame_api2.getNextApprovers4 ',4);
1474 hr_utility.set_location('c_application_id:'||c_application_id,5);
1475 hr_utility.set_location('c_transaction_type:'|| c_transaction_type,6);
1476 hr_utility.set_location('c_transaction_id:'|| c_transaction_id,7);
1477 hr_utility.set_location('flagApproversAsNotifiedIn:'|| ame_util.booleanFalse,8);
1478 end if;
1479 ame_api2.getNextApprovers4
1480 (applicationIdIn => c_application_id
1481 ,transactionTypeIn => c_transaction_type
1482 ,transactionIdIn => c_transaction_id
1483 ,flagApproversAsNotifiedIn=>ame_util.booleanFalse
1484 ,approvalProcessCompleteYNOut => c_approvalprocesscompleteynout
1485 ,nextApproversOut => c_next_approvers);
1486
1487 if g_debug then
1488 hr_utility.set_location('returned from ame_api2.getNextApprovers4, number records fetched:'||c_next_approvers.count,9);
1489 end if;
1490
1491 -- Assumption no parallel approvers,
1492 -- to revisit for parallel approvers case
1493 if(c_approvalprocesscompleteynout<>'Y') then
1494 c_approver_to_notify_rec :=c_next_approvers(1);
1495 end if;
1496
1497 exception
1498 when others then
1499
1500 handleApprovalErrors(p_item_type, p_item_key, sqlerrm);
1501 result := 'COMPLETE:'||'E';
1502 return;
1503 end;
1504
1505 else
1506 -- non AME
1507 begin
1508 hr_approval_ss.getNextCustomApprover
1509 (p_item_type=>p_item_type,
1510 p_item_key =>p_item_key,
1511 p_approvalprocesscompleteynout=>c_approvalprocesscompleteynout,
1512 p_approver_rec=>c_approver_to_notify_rec);
1513 exception
1514 when others then
1515 if g_debug then
1516 hr_utility.set_location('Error in isFinalApprover calling hr_approval_ss.getNextCustomApprover, SQLERRM' ||' '||to_char(SQLCODE),20);
1517 end if;
1518 raise;
1519 end;
1520 end if ; -- check for AME
1521
1522
1523 if g_debug then
1524 hr_utility.set_location('c_approvalprocesscompleteynout:'||c_approvalprocesscompleteynout,12);
1525 end if;
1526
1527 -- check if the approval process is complete
1528 if(c_approvalprocesscompleteynout='Y') then
1529 result := 'COMPLETE:'||'Y';
1530 -- check if the pre-notification process for non-AME FYI is completed
1531 if g_debug then
1532 hr_utility.set_location('calling isPostNonAMEFYIComplete',13);
1533 end if;
1534
1535 if(NOT isPostNonAMEFYIComplete(p_item_type,p_item_key)) then
1536 result := 'COMPLETE:'||'N';
1537 return;
1538 end if;
1539 else
1540 if g_debug then
1541 hr_utility.set_location('calling populateApproverDetails',14);
1542 end if;
1543
1544 populateApproverDetails(p_item_type,p_item_key,c_approver_to_notify_rec);
1545 result := 'COMPLETE:'||'N';
1546 end if;
1547 end if;
1548
1549
1550
1551 if (g_debug ) then
1552 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 50);
1553 end if;
1554
1555
1556 EXCEPTION
1557 WHEN OTHERS THEN
1558 if g_debug then
1559 hr_utility.set_location('Error in isFinalApprover , SQLERRM' ||' '||to_char(SQLCODE),30);
1560 end if;
1561 raise;
1562
1563 END isFinalApprover ;
1564
1565
1566 procedure updateApprovalHistory( p_item_type in varchar2,
1567 p_item_key in varchar2,
1568 p_act_id in number,
1569 funmode in varchar2,
1570 result out nocopy varchar2 )
1571
1572 is
1573 -- local variables
1574 c_proc constant varchar2(30) := 'updateApprovalHistory';
1575 -- Variables required for AME API
1576 c_application_id integer;
1577 c_transaction_id varchar2(25);
1578 c_transaction_type varchar2(25);
1579 c_next_approvers ame_util.approverstable2;
1580 c_approvalprocesscompleteynout ame_util.charType;
1581 l_current_forward_to_username wf_users.name%type;
1582
1583 begin
1584 g_debug := hr_utility.debug_enabled;
1585
1586 if g_debug then
1587 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1588 end if;
1589 if ( funmode = wf_engine.eng_run ) then
1590 -- call PQH_SS_HISTORY.transfer_approval_to_history
1591 if g_debug then
1592 hr_utility.set_location('calling PQH_SS_HISTORY.transfer_approval_to_history ',2);
1593 hr_utility.set_location('p_item_type:'|| p_item_type,3);
1594 hr_utility.set_location('p_item_key:'|| p_item_key,4);
1595 end if;
1596
1597 PQH_SS_HISTORY.transfer_approval_to_history(p_item_type,p_item_key,p_act_id,funmode,result);
1598 -- reset the status to pending approval
1599 if g_debug then
1600 hr_utility.set_location('calling PQH_SS_WORKFLOW.set_txn_approve_status ',5);
1601 hr_utility.set_location('p_item_type:'|| p_item_type,6);
1602 hr_utility.set_location('p_item_key:'|| p_item_key,7);
1603 end if;
1604
1605 PQH_SS_WORKFLOW.set_txn_approve_status(p_item_type,p_item_key,p_act_id,funmode,result);
1606
1607 end if;
1608
1609 if (g_debug ) then
1610 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1611 end if;
1612 exception
1613 when others then
1614 raise;
1615 end updateApprovalHistory;
1616
1617 procedure forwardToRoleReInit(p_item_type in wf_items.item_type%type,
1618 p_item_key in wf_items.item_key%type) is
1619 lv_role_name wf_roles.name%type;
1620 lv_role_disp_name wf_roles.name%type;
1621 lv_role_orig_system wf_roles.orig_system%type;
1622 lv_role_orig_sys_id wf_roles.orig_system_id%type;
1623 begin
1624
1625 -- FORWARD_TO_PERSON_ID
1626 lv_role_orig_sys_id:=wf_engine.getitemattrnumber(p_item_type,p_item_key,'FORWARD_TO_PERSON_ID',true);
1627
1628 if(lv_role_orig_sys_id is not null) then
1629 -- need to revisit with role based support in SSHR transaction
1630 lv_role_orig_system := nvl( wf_engine.GetItemAttrText
1631 (itemtype => p_item_type
1632 ,itemkey => p_item_key
1633 ,aname => 'FORWARD_TO_ORIG_SYS_ATTR'
1634 ,ignore_notfound=>true),'PER');
1635
1636 wf_directory.GetRoleName
1637 (p_orig_system => lv_role_orig_system
1638 ,p_orig_system_id => lv_role_orig_sys_id
1639 ,p_name => lv_role_name
1640 ,p_display_name => lv_role_disp_name);
1641 -- set the concerned item attributes
1642 -- FORWARD_TO_USERNAME
1643 create_item_attrib_if_notexist(itemtype => p_item_type
1644 ,itemkey => p_item_key
1645 ,aname => 'FORWARD_TO_USERNAME'
1646 ,text_value=>lv_role_name
1647 ,number_value=>null,
1648 date_value=>null
1649 );
1650 end if;
1651
1652 end forwardToRoleReInit;
1653
1654
1655 procedure updateApproveStatus( p_item_type in varchar2,
1656 p_item_key in varchar2,
1657 p_act_id in number,
1658 funmode in varchar2,
1659 result out nocopy varchar2 )
1660
1661 is
1662 -- local variables
1663 c_proc constant varchar2(30) := 'updateApproveStatus';
1664 -- Variables required for AME API
1665 c_application_id integer;
1666 c_transaction_id varchar2(25);
1667 c_transaction_type varchar2(25);
1668 c_next_approvers ame_util.approverstable2;
1669 c_approvalprocesscompleteynout ame_util.charType;
1670 l_current_forward_to_username wf_users.name%type;
1671
1672 begin
1673
1674 g_debug := hr_utility.debug_enabled;
1675
1676 if g_debug then
1677 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1678 end if;
1679 if ( funmode = wf_engine.eng_run ) then
1680 -- check if it is AME or custom approvals
1681 c_application_id :=nvl(wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1682 itemkey => p_item_key,
1683 aname => 'HR_AME_APP_ID_ATTR'),800);
1684
1685 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1686 itemkey => p_item_key,
1687 aname => 'TRANSACTION_ID');
1688
1689 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
1690 itemkey => p_item_key,
1691 aname => 'HR_AME_TRAN_TYPE_ATTR');
1692 forwardToRoleReInit(p_item_type,p_item_key);
1693 if(c_transaction_type is not null) then
1694 hr_utility.set_location('In(if ( if(c_transaction_type is not null))): '|| c_proc,2);
1695 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => p_item_type
1696 ,itemkey => p_item_key
1697 ,aname => 'FORWARD_TO_USERNAME');
1698 l_current_forward_to_username := nvl(l_current_forward_to_username,wf_engine.GetItemAttrText(itemtype => p_item_type ,
1699 itemkey => p_item_key,
1700 aname => 'RETURN_TO_USERNAME'));
1701 if g_debug then
1702 hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 3);
1703 hr_utility.set_location('c_application_id:'|| c_application_id, 4);
1704 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 5);
1705 hr_utility.set_location('approvalStatusIn:'|| ame_util.approvedStatus, 6);
1706 hr_utility.set_location('approverNameIn:'|| l_current_forward_to_username, 7);
1707 end if;
1708
1709 begin
1710 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
1711 transactionTypeIn =>c_transaction_type,
1712 transactionIdIn=>c_transaction_id,
1713 approvalStatusIn =>ame_util.approvedStatus,
1714 approverNameIn =>l_current_forward_to_username,
1715 itemClassIn => null,
1716 itemIdIn =>null,
1717 actionTypeIdIn=> null,
1718 groupOrChainIdIn =>null,
1719 occurrenceIn =>null,
1720 forwardeeIn =>ame_util.emptyApproverRecord2,
1721 updateItemIn =>false);
1722 exception
1723 when others then
1724 if g_debug then
1725 hr_utility.set_location('Error in updateApproveStatus SQLERRM' ||' '||to_char(SQLCODE),10);
1726 end if;
1727 hr_utility.trace('ORCL error '||SQLERRM);
1728 result := wf_engine.eng_trans_default;
1729 return;
1730 end;
1731
1732 if g_debug then
1733 hr_utility.set_location('returned from calling ame_api2.updateApprovalStatus2', 8);
1734 end if;
1735
1736 result := wf_engine.eng_trans_default;
1737 else
1738 null;
1739 end if;
1740 pqh_ss_workflow.set_txn_approve_status(p_item_type,p_item_key,p_act_id,funmode,result);
1741 /*
1742 -- transfer the approval action to history
1743 -- call PQH_SS_HISTORY.transfer_approval_to_history
1744 updateApprovalHistory( p_item_type=>p_item_type,
1745 p_item_key=>p_item_key,
1746 p_act_id=>p_act_id,
1747 funmode=>funmode,
1748 result=>result);*/
1749
1750
1751 end if;
1752
1753 if (g_debug ) then
1754 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 20);
1755 end if;
1756 exception
1757 when others then
1758 if g_debug then
1759 hr_utility.set_location('Error in updateApproveStatus SQLERRM' ||' '||to_char(SQLCODE),30);
1760 end if;
1761 raise;
1762 end updateApproveStatus;
1763
1764 procedure approver_category( p_item_type in varchar2,
1765 p_item_key in varchar2,
1766 p_act_id in number,
1767 funmode in varchar2,
1768 result out nocopy varchar2 )
1769 is
1770 --
1771 lv_current_approver_category varchar2(4000);
1772 ln_sublist_order_number number;
1773 c_proc constant varchar2(60) := 'approver_category';
1774 begin
1775 g_debug := hr_utility.debug_enabled;
1776
1777 if g_debug then
1778 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1779 end if;
1780
1781 -- get the current approver category
1782 -- HR_APR_CATEGORY_ATTR
1783 lv_current_approver_category:= wf_engine.GetItemAttrText(itemtype => p_item_type ,
1784 itemkey => p_item_key,
1785 aname => 'HR_APR_CATEGORY_ATTR',
1786 ignore_notfound=>true);
1787 --HR_APR_SUB_LST_ORD_NUM_ATTR
1788 ln_sublist_order_number:=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1789 itemkey => p_item_key,
1790 aname => 'HR_APR_SUB_LST_ORD_NUM_ATTR',
1791 ignore_notfound=>true);
1792 -- default approvals
1793 lv_current_approver_category := nvl(lv_current_approver_category,'A');
1794 -- default FYI approver
1795 ln_sublist_order_number:= nvl(ln_sublist_order_number,2);
1796 if(lv_current_approver_category='A') then
1797 result:= 'COMPLETE:'||'APPROVAL';
1798 elsif(ln_sublist_order_number=1) then
1799 result:= 'COMPLETE:'||'PRE';
1800 elsif(ln_sublist_order_number=2) then
1801 result:= 'COMPLETE:'||'AUTH';
1802 elsif(ln_sublist_order_number=3) then
1803 result:= 'COMPLETE:'||'POST';
1804 end if;
1805
1806 if (g_debug ) then
1807 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1808 end if;
1809
1810 exception
1811 when others then
1812 if g_debug then
1813 hr_utility.set_location('Error in approver_category SQLERRM' ||' '||to_char(SQLCODE),20);
1814 end if;
1815 raise;
1816
1817 end approver_category;
1818
1819
1820 procedure flagApproversAsNotified( p_item_type in varchar2,
1821 p_item_key in varchar2,
1822 p_act_id in number,
1823 funmode in varchar2,
1824 result out nocopy varchar2 )
1825 is
1826 -- local variables
1827 c_proc constant varchar2(30) := 'flagApproversAsNotified';
1828 -- Variables required for AME API
1829 c_application_id integer;
1830 c_transaction_id varchar2(25);
1831 c_transaction_type varchar2(25);
1832 l_current_forward_to_username wf_users.name%type;
1833 l_curr_fwd_to_orig_system_id number;
1834
1835 begin
1836 g_debug := hr_utility.debug_enabled;
1837
1838 if g_debug then
1839 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1840 end if;
1841 if ( funmode = wf_engine.eng_run ) then
1842 -- check if it is AME or custom approvals
1843 c_application_id :=nvl(wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1844 itemkey => p_item_key,
1845 aname => 'HR_AME_APP_ID_ATTR'),800);
1846
1847 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1848 itemkey => p_item_key,
1849 aname => 'TRANSACTION_ID');
1850
1851 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
1852 itemkey => p_item_key,
1853 aname => 'HR_AME_TRAN_TYPE_ATTR');
1854 if(c_transaction_type is not null) then
1855 hr_utility.set_location('In(if ( if(c_transaction_type is not null))): '|| c_proc,15);
1856 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => p_item_type
1857 ,itemkey => p_item_key
1858 ,aname => 'HR_APR_NAME_ATTR');
1859 l_curr_fwd_to_orig_system_id :=wf_engine.getItemAttrNumber
1860 (itemtype => p_item_type
1861 ,itemkey => p_item_key
1862 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR');
1863 if(l_curr_fwd_to_orig_system_id is not null) then
1864
1865 if g_debug then
1866 hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 2);
1867 hr_utility.set_location('c_application_id:'|| c_application_id, 3);
1868 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 4);
1869 hr_utility.set_location('c_transaction_id:'|| c_transaction_id, 5);
1870 hr_utility.set_location('approvalStatusIn:'|| ame_util.notifiedStatus, 6);
1871 hr_utility.set_location('approverNameIn:'|| l_current_forward_to_username, 7);
1872 end if;
1873
1874 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
1875 transactionTypeIn =>c_transaction_type,
1876 transactionIdIn=>c_transaction_id,
1877 approvalStatusIn =>ame_util.notifiedStatus,
1878 approverNameIn =>l_current_forward_to_username,
1879 itemClassIn => wf_engine.GetItemAttrText(itemtype => p_item_type ,
1880 itemkey => p_item_key,
1881 aname => 'HR_APR_ITEM_CLASS_ATTR'),
1882 -- HR_APR_ITEM_ID_ATTR
1883 itemIdIn =>wf_engine.GetItemAttrText(itemtype => p_item_type ,
1884 itemkey => p_item_key,
1885 aname => 'HR_APR_ITEM_ID_ATTR'),
1886 -- HR_APR_ACTION_TYPE_ID_ATTR
1887 actionTypeIdIn=> wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1888 itemkey => p_item_key,
1889 aname => 'HR_APR_ACTION_TYPE_ID_ATTR'),
1890 -- HR_APR_GRPORCHN_ID_ATTR
1891 groupOrChainIdIn =>wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1892 itemkey => p_item_key,
1893 aname => 'HR_APR_GRPORCHN_ID_ATTR'),
1894 occurrenceIn =>null,
1895 forwardeeIn =>ame_util.emptyApproverRecord2,
1896 updateItemIn =>false);
1897 end if;
1898 result := wf_engine.eng_trans_default;
1899 else
1900 null;
1901 end if;
1902 end if;
1903
1904 if (g_debug ) then
1905 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1906 end if;
1907 exception
1908 when others then
1909 if g_debug then
1910 hr_utility.set_location('Error in flagApproversAsNotified SQLERRM' ||' '||to_char(SQLCODE),20);
1911 end if;
1912 raise;
1913 end flagApproversAsNotified;
1914
1915
1916
1917 procedure updateNoResponseStatus( p_item_type in varchar2,
1918 p_item_key in varchar2,
1919 p_act_id in number,
1920 funmode in varchar2,
1921 result out nocopy varchar2 )
1922
1923 is
1924 -- local variables
1925 c_proc constant varchar2(30) := 'updateNoResponseStatus';
1926 -- Variables required for AME API
1927 c_application_id integer;
1928 c_transaction_id varchar2(25);
1929 c_transaction_type varchar2(25);
1930 c_next_approvers ame_util.approverstable2;
1931 c_approvalprocesscompleteynout ame_util.charType;
1932 l_current_forward_to_username wf_users.name%type;
1933
1934 begin
1935 g_debug := hr_utility.debug_enabled;
1936
1937 if g_debug then
1938 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1939 end if;
1940 if ( funmode = wf_engine.eng_run ) then
1941 -- check if it is AME or custom approvals
1942 c_application_id :=nvl(wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1943 itemkey => p_item_key,
1944 aname => 'HR_AME_APP_ID_ATTR'),800);
1945
1946 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1947 itemkey => p_item_key,
1948 aname => 'TRANSACTION_ID');
1949
1950 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
1951 itemkey => p_item_key,
1952 aname => 'HR_AME_TRAN_TYPE_ATTR');
1953 if(c_transaction_type is not null) then
1954 hr_utility.set_location('In(if ( if(c_transaction_type is not null))): '|| c_proc,15);
1955 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => p_item_type
1956 ,itemkey => p_item_key
1957 ,aname => 'FORWARD_TO_USERNAME');
1958 if g_debug then
1959 hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 2);
1960 hr_utility.set_location('c_application_id:'|| c_application_id, 3);
1961 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 4);
1962 hr_utility.set_location('c_transaction_id:'|| c_transaction_id, 5);
1963 hr_utility.set_location('approvalStatusIn:'|| ame_util.noResponseStatus, 6);
1964 hr_utility.set_location('approverNameIn:'|| l_current_forward_to_username, 7);
1965 end if;
1966
1967 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
1968 transactionTypeIn =>c_transaction_type,
1969 transactionIdIn=>c_transaction_id,
1970 approvalStatusIn =>ame_util.noResponseStatus,
1971 approverNameIn =>l_current_forward_to_username,
1972 itemClassIn => null,
1973 itemIdIn =>null,
1974 actionTypeIdIn=> null,
1975 groupOrChainIdIn =>null,
1976 occurrenceIn =>null,
1977 forwardeeIn =>ame_util.emptyApproverRecord2,
1978 updateItemIn =>false);
1979
1980 result := wf_engine.eng_trans_default;
1981 else
1982 result := wf_engine.eng_trans_default;
1983 end if;
1984 end if;
1985
1986 if (g_debug ) then
1987 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1988 end if;
1989 exception
1990 when others then
1991 result := wf_engine.eng_trans_default;
1992 return;
1993 end updateNoResponseStatus;
1994
1995 procedure setRespondedUserContext( p_item_type in varchar2,
1996 p_item_key in varchar2,
1997 p_act_id in number,
1998 funmode in varchar2,
1999 result out nocopy varchar2 )
2000
2001 is
2002 -- local variables
2003 c_proc constant varchar2(30) := 'setRespondedUserContext';
2004
2005 begin
2006 g_debug := hr_utility.debug_enabled;
2007
2008 if g_debug then
2009 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2010 end if;
2011
2012 if ( funmode = 'RESPOND' ) then
2013 create_item_attrib_if_notexist(itemtype=>p_item_type,
2014 itemkey=>p_item_key,
2015 aname=>'HR_CONTEXT_NID_ATTR',
2016 text_value=>null,
2017 number_value=>wf_engine.context_nid,
2018 date_value=>null);
2019 create_item_attrib_if_notexist(itemtype=>p_item_type,
2020 itemkey=>p_item_key,
2021 aname=>'HR_CONTEXT_USER_ATTR',
2022 text_value=>wf_engine.context_user,
2023 number_value=>null,
2024 date_value=>null);
2025 create_item_attrib_if_notexist(itemtype=>p_item_type,
2026 itemkey=>p_item_key,
2027 aname=>'HR_CONTEXT_RECIPIENT_ROLE_ATTR',
2028 text_value=>wf_engine.context_recipient_role,
2029 number_value=>null,
2030 date_value=>null);
2031
2032 create_item_attrib_if_notexist(itemtype=>p_item_type,
2033 itemkey=>p_item_key,
2034 aname=>'HR_CONTEXT_ORIG_RECIPIENT_ATTR',
2035 text_value=>wf_engine.context_original_recipient,
2036 number_value=>null,
2037 date_value=>null);
2038
2039 create_item_attrib_if_notexist(itemtype=>p_item_type,
2040 itemkey=>p_item_key,
2041 aname=>'HR_CONTEXT_FROM_ROLE_ATTR',
2042 text_value=>wf_engine.context_from_role,
2043 number_value=>null,
2044 date_value=>null);
2045 create_item_attrib_if_notexist(itemtype=>p_item_type,
2046 itemkey=>p_item_key,
2047 aname=>'HR_CONTEXT_NEW_ROLE_ATTR',
2048 text_value=>wf_engine.context_new_role,
2049 number_value=>null,
2050 date_value=>null);
2051 create_item_attrib_if_notexist(itemtype=>p_item_type,
2052 itemkey=>p_item_key,
2053 aname=>'HR_CONTEXT_MORE_INFO_ROLE_ATTR',
2054 text_value=>wf_engine.context_more_info_role,
2055 number_value=>null,
2056 date_value=>null);
2057 create_item_attrib_if_notexist(itemtype=>p_item_type,
2058 itemkey=>p_item_key,
2059 aname=>'HR_CONTEXT_USER_KEY_ATTR',
2060 text_value=>wf_engine.context_user_key,
2061 number_value=>null,
2062 date_value=>null);
2063
2064 create_item_attrib_if_notexist(itemtype=>p_item_type,
2065 itemkey=>p_item_key,
2066 aname=>'HR_CONTEXT_PROXY_ATTR',
2067 text_value=>wf_engine.context_proxy,
2068 number_value=>null,
2069 date_value=>null);
2070
2071 result := 'COMPLETE';
2072 return;
2073 end if;
2074
2075 if (g_debug ) then
2076 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2077 end if;
2078
2079
2080
2081 exception
2082 when others then
2083 if g_debug then
2084 hr_utility.set_location('Error in setRespondedUserContext SQLERRM' ||' '||to_char(SQLCODE),20);
2085 end if;
2086 raise;
2087 end setRespondedUserContext;
2088
2089 procedure submit_for_approval( p_item_type in varchar2,
2090 p_item_key in varchar2,
2091 p_act_id in number,
2092 funmode in varchar2,
2093 result out nocopy varchar2 )
2094
2095 is
2096 -- local variables
2097 c_proc constant varchar2(30) := 'submit_for_approval';
2098 -- Variables required for AME API
2099 c_application_id integer;
2100 c_transaction_id varchar2(25);
2101 c_transaction_type varchar2(25);
2102 c_next_approvers ame_util.approverstable2;
2103 c_approvalprocesscompleteynout ame_util.charType;
2104 l_current_forward_to_username wf_users.name%type;
2105 lv_transaction_status varchar2(100);
2106 lv_approval_required varchar2(100);
2107 lv_result varchar2(100);
2108 lv_comments wf_item_attribute_values.text_value%type;
2109
2110 begin
2111 g_debug := hr_utility.debug_enabled;
2112
2113 if g_debug then
2114 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2115 end if;
2116 if ( funmode = wf_engine.eng_run ) then
2117 -- Creating attribute to check the new transaction
2118 create_item_attrib_if_notexist(itemtype => p_item_type
2119 ,itemkey => p_item_key
2120 ,aname => 'HR_NEW_TRANSACTION'
2121 ,text_value=>'TIMEOUT ^ APPROVETWICE' --bug 5414392
2122 ,number_value=>null,
2123 date_value=>null
2124 );
2125 -- check if the transaction needs to be submitted for approval
2126 -- get the transaction status
2127 lv_transaction_status:=wf_engine.getItemAttrText(itemtype=> p_item_type,
2128 itemkey => p_item_key,
2129 aname => 'TRAN_SUBMIT');
2130 if(lv_transaction_status<>'Y') then
2131 -- result := 'COMPLETE:'||'E';
2132 --return;
2133 raise g_transaction_status_invalid;
2134 end if;
2135 if(lv_transaction_status='Y') then
2136 lv_approval_required:= wf_engine.GetItemAttrText(itemtype => p_item_type,
2137 itemkey => p_item_key,
2138 aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG');
2139 lv_approval_required := nvl(lv_approval_required,'N0');
2140 if(lv_approval_required in ('YES_DYNAMIC','YES','Y','YD')) then
2141
2142 -- call the other intialization routines needed for approval
2143 -- notification process
2144 -- PQH_SS_HISTORY.transfer_submit_to_history
2145 if g_debug then
2146 hr_utility.set_location('calling PQH_SS_HISTORY.transfer_submit_to_history', 2);
2147 hr_utility.set_location('p_item_type:'|| p_item_type, 3);
2148 hr_utility.set_location('p_item_key:'|| p_item_key, 4);
2149 end if;
2150 -- PQH_SS_HISTORY.transfer_submit_to_history(p_item_type,p_item_key,p_act_id,funmode,result);
2151 -- use the new history API's
2152 -- add the code plugin transfer history
2153 c_transaction_id := wf_engine.getitemattrnumber(p_item_type,p_item_key,'TRANSACTION_ID');
2154 lv_comments := wf_engine.getitemattrtext(p_item_type,p_item_key,'APPROVAL_COMMENT_COPY');
2155 hr_trans_history_api.archive_submit(c_transaction_id,
2156 null,
2157 fnd_global.user_name,
2158 lv_comments);
2159
2160 -- HR_APPROVAL_WF.INITIALIZE_ITEM_ATTRIBUTES
2161 if g_debug then
2162 hr_utility.set_location('calling HR_APPROVAL_WF.INITIALIZE_ITEM_ATTRIBUTES', 5);
2163 hr_utility.set_location('p_item_type:'|| p_item_type, 6);
2164 hr_utility.set_location('p_item_key:'|| p_item_key, 7);
2165 end if;
2166
2167 HR_DYNAMIC_APPROVAL_WEB.INITIALIZE_ITEM_ATTRIBUTES(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
2168 HR_APPROVAL_WF.INITIALIZE_ITEM_ATTRIBUTES(p_item_type,p_item_key,p_act_id,funmode,result);
2169
2170 -- PQH_SS_WORKFLOW.set_image_source
2171 if g_debug then
2172 hr_utility.set_location('calling PQH_SS_WORKFLOW.set_image_source', 8);
2173 hr_utility.set_location('p_item_type:'|| p_item_type, 9);
2174 hr_utility.set_location('p_item_key:'|| p_item_key, 10);
2175 end if;
2176
2177 PQH_SS_WORKFLOW.set_image_source(p_item_type,p_item_key,p_act_id,funmode,result);
2178 result := 'COMPLETE:'||'Y';
2179 end if;
2180
2181 else
2182 result := 'COMPLETE:'||'N';
2183
2184 end if;
2185
2186 end if;
2187
2188 if (g_debug ) then
2189 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2190 end if;
2191 exception
2192 when others then
2193 if g_debug then
2194 hr_utility.set_location('Error in submit_for_approval SQLERRM' ||' '||to_char(SQLCODE),20);
2195 end if;
2196 raise;
2197 end submit_for_approval;
2198
2199
2200 procedure setSFLResponseContext( p_item_type in varchar2,
2201 p_item_key in varchar2,
2202 p_act_id in number,
2203 funmode in varchar2,
2204 result out nocopy varchar2 )
2205
2206 is
2207 -- local variables
2208 c_proc constant varchar2(30) := 'setSFLResponseContext';
2209 -- Variables required for AME API
2210 c_application_id integer;
2211 c_transaction_id varchar2(25);
2212 c_transaction_type varchar2(25);
2213 c_next_approvers ame_util.approverstable2;
2214 c_approvalprocesscompleteynout ame_util.charType;
2215 l_current_forward_to_username wf_users.name%type;
2216 lv_result_code WF_ITEM_ACTIVITY_STATUSES.activity_result_code%type;
2217 lv_result_display varchar2(250);
2218
2219 begin
2220 g_debug := hr_utility.debug_enabled;
2221
2222 if g_debug then
2223 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2224 end if;
2225
2226 handleArchive(p_item_type,
2227 p_item_key,
2228 p_act_id,
2229 funmode,
2230 result);
2231
2232 if g_debug then
2233 hr_utility.set_location('returning with the result'||result,8);
2234 end if;
2235
2236
2237 if (g_debug ) then
2238 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2239 end if;
2240
2241 exception
2242 when others then
2243 if g_debug then
2244 hr_utility.set_location('Error in setSFLResponseContext SQLERRM' ||' '||to_char(SQLCODE),20);
2245 end if;
2246 raise;
2247 end setSFLResponseContext;
2248
2249 procedure setRFCResponseContext( p_item_type in varchar2,
2250 p_item_key in varchar2,
2251 p_act_id in number,
2252 funmode in varchar2,
2253 result out nocopy varchar2 )
2254
2255 is
2256 -- local variables
2257 c_proc constant varchar2(30) := 'setRFCResponseContext';
2258 -- Variables required for AME API
2259 c_application_id integer;
2260 c_transaction_id varchar2(25);
2261 c_transaction_type varchar2(25);
2262 c_next_approvers ame_util.approverstable2;
2263 c_approvalprocesscompleteynout ame_util.charType;
2264 l_current_forward_to_username wf_users.name%type;
2265 lv_result_code WF_ITEM_ACTIVITY_STATUSES.activity_result_code%type;
2266 lv_result_display varchar2(250);
2267
2268 begin
2269 g_debug := hr_utility.debug_enabled;
2270
2271 if g_debug then
2272 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2273 end if;
2274
2275 --sturlapa coding starts for bug 3866581
2276 begin
2277 select text_value
2278 into lv_result_code
2279 from wf_notification_attributes
2280 where notification_id=wf_engine.context_nid
2281 and name='RESULT';
2282
2283 if(funmode='RESPOND' and lv_result_code='RESUBMIT') Then
2284
2285 WF_ENGINE.SetItemAttrText(p_item_type,p_item_key,'WF_NOTE',wf_engine.GetItemAttrText( p_item_type
2286 ,p_item_key
2287 ,'APPROVAL_COMMENT_COPY'));
2288
2289 end if;
2290
2291 exception
2292 when others then
2293 null;
2294 end;
2295 --sturlapa coding ends for bug 3866581
2296
2297 handleArchive(p_item_type,
2298 p_item_key,
2299 p_act_id,
2300 funmode,
2301 result);
2302 if g_debug then
2303 hr_utility.set_location('returning with the result'||result,8);
2304 end if;
2305
2306
2307
2308
2309 if (g_debug ) then
2310 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2311 end if;
2312
2313 exception
2314 when others then
2315 if g_debug then
2316 hr_utility.set_location('Error in setRFCResponseContext SQLERRM' ||' '||to_char(SQLCODE),20);
2317 end if;
2318 raise;
2319 end setRFCResponseContext;
2320
2321 --bug 5414392
2322
2323 procedure setNtfTransferCtx( p_item_type in varchar2,
2324 p_item_key in varchar2,
2325 p_act_id in number,
2326 funmode in varchar2,
2327 result out nocopy varchar2 )
2328
2329 is
2330 -- local variables
2331 c_proc constant varchar2(30) := 'setNtfTransferCtx';
2332 -- Variables required for AME API
2333 l_forward_to_person_id per_people_f.person_id%type;
2334 c_application_id integer;
2335 c_transaction_id varchar2(25);
2336 c_transaction_type varchar2(25);
2337 c_next_approvers ame_util.approverstable2;
2338 c_approvalprocesscompleteynout ame_util.charType;
2339 l_current_forward_to_username wf_users.name%type;
2340 rec_forwardee ame_util.approverRecord2;
2341
2342 l_new_fwd_person_id per_people_f.person_id%type;
2343 l_new_orig_system varchar2(30);
2344 l_new_fwd_display_name varchar2(360);
2345
2346 cursor get_person_info is
2347 select orig_system,orig_system_id,display_name from wf_roles where name = wf_engine.context_new_role;
2348
2349 /* cursor get_name_info is
2350 select last_name,first_name from per_all_people_f where
2351 person_id = l_new_fwd_person_id;*/
2352
2353 begin
2354
2355 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2356
2357 if ( funmode = 'TRANSFER' ) then
2358 -- TRANSFER - When a notification recipient transfers the notification.
2359 -- check if it is AME or custom approvals
2360 c_application_id :=nvl(wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2361 itemkey => p_item_key,
2362 aname => 'HR_AME_APP_ID_ATTR'),800);
2363
2364 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2365 itemkey => p_item_key,
2366 aname => 'TRANSACTION_ID');
2367
2368 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2369 itemkey => p_item_key,
2370 aname => 'HR_AME_TRAN_TYPE_ATTR');
2371
2372 if(c_transaction_type is not null) then
2373 hr_utility.set_location('In(if ( if(c_transaction_type is not null))): '|| c_proc,2);
2374 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => p_item_type
2375 ,itemkey => p_item_key
2376 ,aname => 'FORWARD_TO_USERNAME');
2377 l_current_forward_to_username := nvl(l_current_forward_to_username,wf_engine.GetItemAttrText(itemtype => p_item_type ,
2378 itemkey => p_item_key,
2379 aname => 'RETURN_TO_USERNAME'));
2380 if g_debug then
2381 hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 3);
2382 hr_utility.set_location('c_application_id:'|| c_application_id, 4);
2383 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 5);
2384 hr_utility.set_location('approvalStatusIn:'|| ame_util.approvedStatus, 6);
2385 hr_utility.set_location('approverNameIn:'|| l_current_forward_to_username, 7);
2386 hr_utility.set_location('new approver:'|| wf_engine.context_new_role, 7);
2387 end if;
2388
2389 begin
2390
2391 open get_person_info;
2392 fetch get_person_info into l_new_orig_system,l_new_fwd_person_id,l_new_fwd_display_name;
2393 CLOSe get_person_info;
2394
2395 rec_forwardee.name:= wf_engine.context_new_role;
2396 rec_forwardee.orig_system :=l_new_orig_system;
2397 rec_forwardee.orig_system_id :=l_new_fwd_person_id;
2398 rec_forwardee.display_name :=l_new_fwd_display_name;
2399 --rec_forwardee.api_insertion:='Y';
2400
2401 hr_utility.set_location('new approver person id:'|| l_new_fwd_person_id, 7);
2402
2403
2404
2405 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
2406 transactionTypeIn =>c_transaction_type,
2407 transactionIdIn=>c_transaction_id,
2408 approvalStatusIn =>ame_util.forwardStatus,
2409 approverNameIn =>l_current_forward_to_username,
2410 itemClassIn => null,
2411 itemIdIn =>null,
2412 actionTypeIdIn=> null,
2413 groupOrChainIdIn =>null,
2414 occurrenceIn =>null,
2415 forwardeeIn =>rec_forwardee,
2416 updateItemIn =>false);
2417
2418 --reset wf attributes
2419
2420 wf_engine.setItemAttrNumber(p_item_type,p_item_key,'FORWARD_TO_PERSON_ID',rec_forwardee.orig_system_id);
2421 wf_engine.setItemAttrText(p_item_type,p_item_key,'FORWARD_TO_USERNAME',wf_engine.context_new_role);
2422 wf_engine.setItemAttrText(p_item_type,p_item_key,'FORWARD_TO_DISPLAY_NAME',wf_directory.GetRoleDisplayName(wf_engine.context_new_role));
2423
2424 if g_debug then
2425 hr_utility.set_location('calling ame_api2.getNextApprovers4 ',4);
2426 hr_utility.set_location('c_application_id:'||c_application_id,5);
2427 hr_utility.set_location('c_transaction_type:'|| c_transaction_type,6);
2428 hr_utility.set_location('c_transaction_id:'|| c_transaction_id,7);
2429 hr_utility.set_location('flagApproversAsNotifiedIn:'|| ame_util.booleanFalse,8);
2430 end if;
2431
2432 ame_api2.getNextApprovers4
2433 (applicationIdIn => c_application_id
2434 ,transactionTypeIn => c_transaction_type
2435 ,transactionIdIn => c_transaction_id
2436 ,flagApproversAsNotifiedIn=>ame_util.booleanFalse
2437 ,approvalProcessCompleteYNOut => c_approvalprocesscompleteynout
2438 ,nextApproversOut => c_next_approvers);
2439
2440
2441 exception
2442 when others then
2443 if g_debug then
2444 hr_utility.set_location('Error in updateApproveStatus SQLERRM' ||' '||to_char(SQLCODE),10);
2445 end if;
2446
2447 result := 'COMPLETE:' || 'ERROR';
2448 return;
2449 end ;
2450
2451 if g_debug then
2452 hr_utility.set_location('returned from calling ame_api2.updateApprovalStatus2', 8);
2453 end if;
2454
2455
2456
2457 result := wf_engine.eng_trans_default;
2458 else
2459 null;
2460 end if;
2461
2462
2463 end if;
2464
2465
2466 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 20);
2467 -- hr_utility.trace_off;
2468 exception
2469 when others then
2470
2471 hr_utility.set_location('Error in setNtfTransferCtx SQLERRM' ||' '||to_char(SQLCODE),30);
2472
2473 raise;
2474 end setNtfTransferCtx;
2475
2476
2477 procedure setApproverResponseContext( p_item_type in varchar2,
2478 p_item_key in varchar2,
2479 p_act_id in number,
2480 funmode in varchar2,
2481 result out nocopy varchar2 )
2482
2483 is
2484 -- local variables
2485 c_proc constant varchar2(30) := 'setApproverResponseContext';
2486 -- Variables required for AME API
2487 c_application_id integer;
2488 c_transaction_id varchar2(25);
2489 c_transaction_type varchar2(25);
2490 c_next_approvers ame_util.approverstable2;
2491 c_approvalprocesscompleteynout ame_util.charType;
2492 l_current_forward_to_username wf_users.name%type;
2493 lv_result_code WF_ITEM_ACTIVITY_STATUSES.activity_result_code%type;
2494 lv_result_display varchar2(250);
2495 lv_test_result_code varchar2(250);
2496 lv_comments wf_item_attribute_values.text_value%type;
2497 lv_new_transaction varchar2(240);
2498
2499 begin
2500 g_debug := hr_utility.debug_enabled;
2501
2502 if g_debug then
2503 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2504 end if;
2505
2506 -- capture the approver login context information
2507 if ( funmode = 'RESPOND' ) then
2508 hr_approval_ss.setrespondedusercontext(p_item_type,p_item_key,p_act_id,funmode,result);
2509 end if;
2510
2511 -- bug 5414392
2512
2513 if ( funmode = 'TRANSFER' ) then
2514 lv_new_transaction := wf_engine.getitemattrtext(p_item_type,
2515 p_item_key,
2516 'HR_NEW_TRANSACTION',true);
2517
2518 if( INSTR(lv_new_transaction,'APPROVETWICE') > 0) then
2519 setNtfTransferCtx(p_item_type,p_item_key,p_act_id,funmode,result);
2520 end if;
2521 end if;
2522 -- archive the user action
2523 handleArchive(p_item_type,
2524 p_item_key,
2525 p_act_id,
2526 funmode,
2527 result);
2528
2529
2530 if (g_debug ) then
2531 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2532 end if;
2533 exception
2534 when others then
2535 if g_debug then
2536 hr_utility.set_location('Error in setRFCResponseContext SQLERRM' ||' '||to_char(SQLCODE),20);
2537 end if;
2538
2539 raise;
2540 end setApproverResponseContext;
2541
2542
2543 procedure reset_ame_to_rfc_state(p_item_type in varchar2,
2544 p_item_key in varchar2)
2545 is
2546 -- Variables required for AME API
2547 c_application_id integer;
2548 c_transaction_id varchar2(25);
2549 c_transaction_type varchar2(25);
2550 c_all_approvers ame_util.approverstable2;--ame_util.approversTable;
2551 c_approvalprocesscompleteynout ame_util.charType;
2552
2553 c_creator_user wf_users.name%Type;
2554 c_return_user wf_users.name%Type;
2555 c_match_found varchar2(1);
2556 c_rfc_initiator varchar2(1);
2557
2558 l_proc constant varchar2(100) := g_package || ' reset_ame_to_rfc_state';
2559 begin
2560
2561 g_debug := hr_utility.debug_enabled;
2562 c_match_found := 'N';
2563
2564 IF g_debug THEN
2565 hr_utility.set_location('Entering:'||l_proc, 1);
2566 END IF;
2567 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2568 itemkey => p_item_key,
2569 aname => 'TRANSACTION_ID',
2570 ignore_notfound=>true);
2571 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2572 itemkey => p_item_key,
2573 aname => 'HR_AME_TRAN_TYPE_ATTR',
2574 ignore_notfound=>true);
2575 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2576 itemkey => p_item_key,
2577 aname => 'HR_AME_APP_ID_ATTR',
2578 ignore_notfound=>true);
2579 c_application_id := nvl(c_application_id,800);
2580
2581 if(c_transaction_type is not null) then
2582 if g_debug then
2583 hr_utility.set_location('calling ame_api2.getAllApprovers7', 2);
2584 hr_utility.set_location('c_application_id:'|| c_application_id, 3);
2585 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 4);
2586 hr_utility.set_location('c_transaction_id:'|| c_transaction_id, 5);
2587 end if;
2588
2589 ame_api2.getAllApprovers7(applicationIdIn =>c_application_id,
2590 transactionTypeIn =>c_transaction_type,
2591 transactionIdIn => c_transaction_id,
2592 approvalProcessCompleteYNOut =>c_approvalprocesscompleteynout,
2593 approversOut =>c_all_approvers);
2594 c_creator_user := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2595 itemkey => p_item_key,
2596 aname => 'CREATOR_PERSON_USERNAME');
2597 c_return_user := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2598 itemkey => p_item_key,
2599 aname => 'RETURN_TO_USERNAME',
2600 ignore_notfound=>true);
2601 IF ( c_return_user IS NULL OR c_creator_user = c_return_user ) THEN
2602 c_rfc_initiator := 'Y';
2603 ELSE
2604 c_rfc_initiator := 'N';
2605 END IF;
2606
2607 for i in 1..c_all_approvers.count loop
2608 IF ( c_rfc_initiator = 'N' AND c_return_user = c_all_approvers(i).name ) THEN
2609 c_match_found := 'Y';
2610 END IF;
2611 IF (c_rfc_initiator = 'Y' OR (c_rfc_initiator = 'N' AND c_match_found = 'Y' ) ) THEN
2612 if g_debug then
2613 hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 2);
2614 hr_utility.set_location('c_application_id:'|| c_application_id, 3);
2615 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 4);
2616 hr_utility.set_location('c_transaction_id:'|| c_transaction_id, 5);
2617 hr_utility.set_location('approvalStatusIn:'|| null, 6);
2618 hr_utility.set_location('approverNameIn:'|| c_all_approvers(i).name, 7);
2619 end if;
2620
2621 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
2622 transactionTypeIn =>c_transaction_type,
2623 transactionIdIn=>c_transaction_id,
2624 approvalStatusIn =>null,
2625 approverNameIn =>c_all_approvers(i).name,
2626 itemClassIn => c_all_approvers(i).ITEM_CLASS,
2627 itemIdIn =>c_all_approvers(i).ITEM_ID,
2628 actionTypeIdIn=> c_all_approvers(i).ACTION_TYPE_ID,
2629 groupOrChainIdIn =>c_all_approvers(i).GROUP_OR_CHAIN_ID,
2630 occurrenceIn =>c_all_approvers(i).OCCURRENCE,
2631 forwardeeIn =>ame_util.emptyApproverRecord2,
2632 updateItemIn =>false);
2633 end if;
2634
2635 end loop;
2636 end if;
2637
2638 IF g_debug THEN
2639 hr_utility.set_location('Leaving:'|| l_proc, 35);
2640 END IF;
2641
2642 EXCEPTION
2643 when others then
2644 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
2645 hr_utility.trace(' exception in '||l_proc||' : ' || sqlerrm);
2646
2647 Wf_Core.Context(g_package, l_proc, p_item_type, p_item_key);
2648 raise;
2649 end reset_ame_to_rfc_state;
2650
2651 procedure reset_approval_rfc_data(p_item_type in varchar2,
2652 p_item_key in varchar2) is
2653
2654 -- local variables
2655 c_proc constant varchar2(60) := 'reset_approval_rfc_data';
2656 c_return_user wf_users.name%Type;
2657 c_creator_user wf_users.name%Type;
2658 l_approvalProcessVersion varchar2(10);
2659 l_curr_approver_role wf_roles.name%type;
2660 c_return_user_role_info_tbl wf_directory.wf_local_roles_tbl_type;
2661 l_curr_approver_role_info_tbl wf_directory.wf_local_roles_tbl_type;
2662
2663 begin
2664 g_debug := hr_utility.debug_enabled;
2665 if g_debug then
2666 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2667 end if;
2668
2669 -- functional logic
2670 -- case : notification sent to M3 for approval
2671 -- M3 RFC to M1 or M0(initiating Manager)
2672 -- All FORWARD_TO should refelect M1 or M0
2673 -- All FORWARD_FROM should reflect M3
2674
2675 -- reset the AME approval state
2676 reset_ame_to_rfc_state(p_item_type,p_item_key);
2677
2678 -- reset the item attrtibutes
2679 c_return_user:= wf_engine.GetItemAttrText(itemtype => p_item_type ,
2680 itemkey => p_item_key,
2681 aname => 'RETURN_TO_USERNAME',
2682 ignore_notfound=>true);
2683 c_creator_user := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2684 itemkey => p_item_key,
2685 aname => 'CREATOR_PERSON_USERNAME');
2686
2687 c_return_user := nvl(c_return_user,c_creator_user);
2688
2689 -- get the return to user orig system and sys id
2690 wf_directory.GetRoleInfo2(c_return_user,c_return_user_role_info_tbl);
2691
2692 l_curr_approver_role :=wf_engine.GetItemAttrText(itemtype => p_item_type ,
2693 itemkey => p_item_key,
2694 aname => 'FORWARD_TO_USERNAME',
2695 ignore_notfound=>true);
2696 ---- get the current approver orig system and sys id
2697
2698 wf_directory.GetRoleInfo2(l_curr_approver_role,l_curr_approver_role_info_tbl);
2699
2700 -- FORWARD_FROM_DISPLAY_NAME
2701 -- set the attribute value to null
2702 create_item_attrib_if_notexist(itemtype => p_item_type
2703 ,itemkey => p_item_key
2704 ,aname => 'FORWARD_FROM_DISPLAY_NAME'
2705 ,text_value=>l_curr_approver_role_info_tbl(1).display_name
2706 ,number_value=>null,
2707 date_value=>null
2708 );
2709
2710 -- FORWARD_FROM_USERNAME
2711 -- set the attribute value to null
2712 create_item_attrib_if_notexist(itemtype => p_item_type
2713 ,itemkey => p_item_key
2714 ,aname => 'FORWARD_FROM_USERNAME'
2715 ,text_value=>l_curr_approver_role
2716 ,number_value=>null,
2717 date_value=>null
2718 );
2719
2720
2721 -- FORWARD_FROM_PERSON_ID
2722 create_item_attrib_if_notexist(itemtype => p_item_type
2723 ,itemkey => p_item_key
2724 ,aname => 'FORWARD_FROM_PERSON_ID'
2725 ,text_value=>null
2726 ,number_value=>l_curr_approver_role_info_tbl(1).orig_system_id,
2727 date_value=>null
2728 );
2729 -- FORWARD_TO_DISPLAY_NAME
2730 create_item_attrib_if_notexist(itemtype => p_item_type
2731 ,itemkey => p_item_key
2732 ,aname => 'FORWARD_TO_DISPLAY_NAME'
2733 ,text_value=>c_return_user_role_info_tbl(1).display_name
2734 ,number_value=>null,
2735 date_value=>null
2736 );
2737
2738 -- FORWARD_TO_USERNAME
2739 create_item_attrib_if_notexist(itemtype => p_item_type
2740 ,itemkey => p_item_key
2741 ,aname => 'FORWARD_TO_USERNAME'
2742 ,text_value=>c_return_user
2743 ,number_value=>null,
2744 date_value=>null
2745 );
2746
2747 -- FORWARD_TO_PERSON_ID
2748 create_item_attrib_if_notexist(itemtype => p_item_type
2749 ,itemkey => p_item_key
2750 ,aname => 'FORWARD_TO_PERSON_ID'
2751 ,text_value=>null
2752 ,number_value=>c_return_user_role_info_tbl(1).orig_system_id,
2753 date_value=>null
2754 );
2755
2756 create_item_attrib_if_notexist(itemtype => p_item_type
2757 ,itemkey => p_item_key
2758 ,aname => 'FORWARD_FROM_ORIG_SYS_ATTR'
2759 ,text_value=>l_curr_approver_role_info_tbl(1).orig_system
2760 ,number_value=>null,
2761 date_value=>null
2762 );
2763
2764 create_item_attrib_if_notexist(itemtype => p_item_type
2765 ,itemkey => p_item_key
2766 ,aname => 'FORWARD_TO_ORIG_SYS_ATTR'
2767 ,text_value=>c_return_user_role_info_tbl(1).orig_system
2768 ,number_value=>null,
2769 date_value=>null
2770 );
2771
2772 /* Bug 2940951: No need to reset current approver index and last default approver in case
2773 * new approval process is used and the non-AME approval is used
2774 * as the two attributes are set when pqh_ss_workflow.return_for_correction is invoked.
2775 * CAUTION: IF this procedure is invoked from somewhere else (apart from RFC) then this needs
2776 * to be checked for that condition too.
2777 */
2778
2779 l_approvalProcessVersion := wf_engine.GetItemAttrText(
2780 itemtype => p_item_Type,
2781 itemkey => p_item_Key,
2782 aname => 'HR_APPROVAL_PRC_VERSION',
2783 ignore_notfound=>true);
2784
2785 IF ( NVL(l_approvalProcessversion,'X') <> 'V5' OR
2786 wf_engine.GetItemAttrText(
2787 itemtype => p_item_Type, itemkey => p_item_Key,
2788 aname => 'HR_AME_TRAN_TYPE_ATTR') IS NOT NULL) THEN
2789
2790 -- CURRENT_APPROVER_INDEX
2791 create_item_attrib_if_notexist(itemtype => p_item_type
2792 ,itemkey => p_item_key
2793 ,aname => 'CURRENT_APPROVER_INDEX'
2794 ,text_value=>null
2795 ,number_value=>null,
2796 date_value=>null
2797 );
2798 -- 'LAST_DEFAULT_APPROVER'
2799 create_item_attrib_if_notexist(itemtype => p_item_type
2800 ,itemkey => p_item_key
2801 ,aname => 'LAST_DEFAULT_APPROVER'
2802 ,text_value=>null
2803 ,number_value=>null,
2804 date_value=>null
2805 );
2806 END IF;
2807 -- CURRENT_DEF_APPR_INDEX
2808 create_item_attrib_if_notexist(itemtype => p_item_type
2809 ,itemkey => p_item_key
2810 ,aname => 'CURRENT_DEF_APPR_INDEX'
2811 ,text_value=>null
2812 ,number_value=>0,
2813 date_value=>null
2814 );
2815
2816 if (g_debug ) then
2817 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2818 end if;
2819
2820 exception
2821 when others then
2822 if g_debug then
2823 hr_utility.set_location('Error in reset_approval_rfc_data SQLERRM' ||' '||to_char(SQLCODE),20);
2824 end if;
2825 raise;
2826 end reset_approval_rfc_data;
2827
2828
2829
2830 procedure processRFC( p_item_type in varchar2,
2831 p_item_key in varchar2,
2832 p_act_id in number,
2833 funmode in varchar2,
2834 result out nocopy varchar2 )
2835
2836 is
2837 -- local variables
2838 c_proc constant varchar2(30) := 'processRFC';
2839 -- Variables required for AME API
2840 c_application_id integer;
2841 c_transaction_id varchar2(25);
2842 c_transaction_type varchar2(25);
2843 c_next_approvers ame_util.approverstable2;
2844 c_approvalprocesscompleteynout ame_util.charType;
2845 l_current_forward_to_username wf_users.name%type;
2846 lv_creatorUserName wf_roles.name%type;
2847 lv_rfcUserName wf_roles.name%type;
2848 lv_customRFC varchar2(5);
2849
2850 lv_dynamicQuery varchar2(4000) ;
2851 lv_queryProcedure varchar2(4000);
2852 begin
2853 g_debug := hr_utility.debug_enabled;
2854 if g_debug then
2855 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2856 end if;
2857 if ( funmode = wf_engine.eng_run ) then
2858
2859 PQH_SS_WORKFLOW.set_txn_rfc_status(p_item_type,p_item_key,p_act_id,funmode,result);
2860 -- fix for bug 4454439
2861 begin
2862 -- re-intialize the performer roles
2863 hr_approval_ss.reinitperformerroles(p_notification_id=>null
2864 ,p_transaction_id=>null
2865 ,p_item_type=>p_item_type
2866 ,p_item_key=>p_item_key);
2867 exception
2868 when others then
2869 null;
2870 end;
2871
2872 -- check if we have product overwrite which does not allow RFC and
2873 -- action would default to delete the transaction
2874 -- HR_CUSTOM_RETURN_FOR_CORR
2875 if g_debug then
2876 hr_utility.set_location('checking product specific custom RFC',2);
2877 end if;
2878 lv_customRFC:=wf_engine.getitemattrtext(p_item_type,p_item_key,'HR_CUSTOM_RETURN_FOR_CORR',true);
2879 lv_customRFC:= nvl(lv_customRFC,'N');
2880 if g_debug then
2881 hr_utility.set_location('lv_customRFC:'||lv_customRFC,3);
2882 end if;
2883
2884 if(lv_customRFC='Y') then
2885 -- no more processing return to delete the transaction
2886 result := wf_engine.eng_completed||':N';
2887 return;
2888 end if;
2889
2890 -- call PQH_SS_HISTORY.transfer_approval_to_history
2891 if g_debug then
2892 hr_utility.set_location('calling PQH_SS_WORKFLOW.set_txn_rfc_status',3);
2893 hr_utility.set_location('p_item_type:'|| p_item_type,4);
2894 hr_utility.set_location('p_item_key:'|| p_item_key,5);
2895 end if;
2896
2897 PQH_SS_WORKFLOW.set_txn_rfc_status(p_item_type,p_item_key,p_act_id,funmode,result);
2898
2899 if g_debug then
2900 hr_utility.set_location('calling reset_approval_rfc_data',6);
2901 hr_utility.set_location('p_item_type:'|| p_item_type,7);
2902 hr_utility.set_location('p_item_key:'|| p_item_key,8);
2903 end if;
2904
2905 begin
2906 reset_approval_rfc_data(p_item_type,p_item_key);
2907 exception
2908 when others then
2909
2910 handleApprovalErrors(p_item_type, p_item_key, sqlerrm);
2911 result := 'COMPLETE:'||'ERROR';
2912 return;
2913
2914 end;
2915
2916
2917
2918 --
2919 begin
2920 -- finally see if we have any module specific call backs
2921 -- 'HR_RFC_CB_ATTR'
2922 lv_queryProcedure := wf_engine.getitemattrtext(p_item_type,p_item_key,'HR_RFC_CB_ATTR',true);
2923
2924 if(lv_queryProcedure is not null or lv_queryProcedure<>'') then
2925
2926 if g_debug then
2927 hr_utility.set_location('Calling queryProcedure: '||lv_queryProcedure, 20);
2928 hr_utility.set_location('p_item_type: '|| p_item_type, 21);
2929 hr_utility.set_location('p_item_key: '|| p_item_key, 22);
2930 hr_utility.set_location('p_act_id: '|| p_act_id, 23);
2931 end if;
2932
2933 lv_dynamicQuery :=
2934 'begin ' ||
2935 lv_queryProcedure ||
2936 '(:itemTypeIn, :itemKeyIn, :actIdIn, :funmodeIn, :resultOut); end;';
2937 execute immediate lv_dynamicQuery
2938 using
2939 in p_item_type,
2940 in p_item_key,
2941 in p_act_id,
2942 in funmode,
2943 in out result;
2944 if g_debug then
2945 hr_utility.set_location('After queryProcedure: '|| lv_queryProcedure, 40);
2946 hr_utility.set_location('result: '|| result, 41);
2947 end if;
2948
2949 end if;
2950 exception
2951 when others then
2952 null; -- raise ???
2953 end;
2954
2955 -- check if the RFC is to Initiator or Approver
2956 -- CREATOR_PERSON_USERNAME
2957 lv_creatorUserName:=wf_engine.getitemattrtext(p_item_type,p_item_key,'CREATOR_PERSON_USERNAME',true);
2958 -- RETURN_TO_USERNAME
2959 lv_rfcUserName:=wf_engine.getitemattrtext(p_item_type,p_item_key,'RETURN_TO_USERNAME',true);
2960 -- Compare
2961
2962 if (lv_creatorUserName is null or lv_rfcUserName is null) then
2963 result := wf_engine.eng_completed||':YES_APPROVER';
2964 elsif (lv_creatorUserName < lv_rfcUserName) then
2965 result := wf_engine.eng_completed||':YES_APPROVER';
2966 elsif (lv_creatorUserName > lv_rfcUserName) then
2967 result := wf_engine.eng_completed||':YES_APPROVER';
2968 elsif (lv_creatorUserName = lv_rfcUserName) then
2969 result := wf_engine.eng_completed||':YES_INIT';
2970 end if;
2971
2972 if g_debug then
2973 hr_utility.set_location('leaving with resultcode :'|| result,9);
2974 end if;
2975
2976
2977 end if;
2978
2979 if (g_debug ) then
2980 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2981 end if;
2982 exception
2983 when others then
2984 if g_debug then
2985 hr_utility.set_location('Error in processRFC SQLERRM' ||' '||to_char(SQLCODE),20);
2986 end if;
2987 raise;
2988 end processRFC;
2989
2990
2991 function getRoleDisplayName(p_user_name in varchar2,
2992 p_orig_system in varchar2,
2993 p_orig_system_id in number)
2994 return varchar2 is
2995 -- local variables
2996 c_proc constant varchar2(30) := 'processRFC';
2997 lv_role_displayName wf_users.display_name%type;
2998 lv_roleName wf_users.name%type;
2999 begin
3000 g_debug := hr_utility.debug_enabled;
3001 if g_debug then
3002 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3003 end if;
3004 if(p_orig_system is null or p_orig_system_id is null) then
3005 lv_role_displayName :=wf_directory.getroledisplayname(p_user_name);
3006 else
3007 wf_directory.GetRoleName
3008 (p_orig_system => p_orig_system
3009 ,p_orig_system_id => p_orig_system_id
3010 ,p_name => lv_roleName
3011 ,p_display_name => lv_role_displayName);
3012 end if;
3013 return lv_role_displayName;
3014 exception
3015 when others then
3016 if g_debug then
3017 hr_utility.set_location('Error in getRoleDisplayName SQLERRM' ||' '||to_char(SQLCODE),20);
3018 end if;
3019
3020 end getRoleDisplayName;
3021
3022
3023 function isApproverEditAllowed(p_transaction_id number default null,
3024 p_user_name in varchar2,
3025 p_orig_system in varchar2,
3026 p_orig_system_id in number)
3027 return varchar2 is
3028 -- local variables
3029 c_proc constant varchar2(30) := 'isApproverEditAllowed';
3030 lv_role_displayName wf_users.display_name%type;
3031 lv_roleName wf_users.name%type;
3032 lv_businessGroupId per_all_people_f.business_group_id%type;
3033 lv_orig_systemId wf_roles.orig_system_id%type;
3034 lv_orig_system wf_roles.orig_system%type;
3035 lv_creator_person_id hr_api_transactions.creator_person_id%type;
3036 begin
3037 g_debug := hr_utility.debug_enabled;
3038 if g_debug then
3039 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3040 end if;
3041 lv_orig_system := p_orig_system;
3042 lv_orig_systemId := p_orig_system_id;
3043
3044 if(p_orig_system_id is null) then
3045 if(p_user_name is not null) then
3046 wf_directory.getroleorigsysinfo(p_user_name,lv_orig_system,lv_orig_systemId);
3047 end if;
3048 end if;
3049
3050 -- need to revisit the functionality once the AME functionality
3051 -- regarding the productions is evaluated.
3052 if(lv_orig_system is not null and lv_orig_system ='PER') then
3053 --
3054 -- check the case for creator person id
3055 begin
3056 select creator_person_id
3057 into lv_creator_person_id
3058 from hr_api_transactions
3059 where transaction_id=p_transaction_id;
3060
3061 if(lv_creator_person_id=lv_orig_systemId) then
3062 return 'Y';
3063 end if;
3064 exception
3065 when others then
3066 null;
3067 end;
3068
3069 begin
3070 select business_group_id
3071 into lv_businessGroupId
3072 from per_all_people_f
3073 where person_id=p_orig_system_id
3074 and sysdate between effective_start_date and effective_end_date;
3075 return pqh_ss_utility.check_edit_privilege(p_orig_system_id,lv_businessGroupId);
3076 exception
3077 when others then
3078 return 'N';
3079 end;
3080 else
3081 return 'N';
3082 end if;
3083
3084 exception
3085 when others then
3086 if g_debug then
3087 hr_utility.set_location('Error in getRoleDisplayName SQLERRM' ||' '||to_char(SQLCODE),20);
3088 end if;
3089
3090 end isApproverEditAllowed;
3091
3092 function getuserOrigSystem(p_user_name in fnd_user.user_name%type,p_notification_id in number default null)
3093 return wf_roles.parent_orig_system%type is
3094 -- local variables
3095 c_proc constant varchar2(30) := 'getuserOrigSystem';
3096 lv_orig_system wf_roles.parent_orig_system%type;
3097 lv_orig_system_id wf_roles.orig_system_id%type;
3098 lv_user_name wf_roles.name%type;
3099 begin
3100 g_debug := hr_utility.debug_enabled;
3101 if g_debug then
3102 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3103 end if;
3104 if(p_notification_id is null) then
3105 wf_directory.getroleorigsysinfo(p_user_name,lv_orig_system,lv_orig_system_id);
3106 else
3107 -- get the original recipient role name
3108 select original_recipient
3109 into lv_user_name
3110 from wf_notifications
3111 where notification_id =p_notification_id;
3112
3113 wf_directory.getroleorigsysinfo(lv_user_name,lv_orig_system,lv_orig_system_id);
3114 end if;
3115
3116 return lv_orig_system;
3117
3118 exception
3119 when others then
3120 if g_debug then
3121 hr_utility.set_location('Error in getRoleDisplayName SQLERRM' ||' '||to_char(SQLCODE),20);
3122 end if;
3123
3124 end getuserOrigSystem;
3125
3126 function getUserOrigSystemId(p_user_name in fnd_user.user_name%type,p_notification_id in number default null)
3127 return wf_roles.orig_system_id%type is
3128 -- local variables
3129 c_proc constant varchar2(30) := 'getUserOrigSystemId';
3130 lv_orig_system wf_roles.parent_orig_system%type;
3131 lv_orig_system_id wf_roles.orig_system_id%type;
3132 lv_user_name wf_roles.name%type;
3133 begin
3134 g_debug := hr_utility.debug_enabled;
3135 if g_debug then
3136 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3137 end if;
3138 if(p_notification_id is null) then
3139 wf_directory.getroleorigsysinfo(p_user_name,lv_orig_system,lv_orig_system_id);
3140 else
3141 -- get the original recipient role name
3142 select original_recipient
3143 into lv_user_name
3144 from wf_notifications
3145 where notification_id =p_notification_id;
3146
3147 wf_directory.getroleorigsysinfo(lv_user_name,lv_orig_system,lv_orig_system_id);
3148 end if;
3149
3150 return lv_orig_system_id;
3151
3152 exception
3153 when others then
3154 if g_debug then
3155 hr_utility.set_location('Error in getUserOrigSystemId SQLERRM' ||' '||to_char(SQLCODE),20);
3156 end if;
3157
3158 end getUserOrigSystemId;
3159
3160
3161 procedure handleRFCAction(p_approval_notification_id in wf_notifications.notification_id%type,
3162 p_transaction_id in hr_api_transactions.transaction_id%type,
3163 p_item_type in wf_items.item_type%type,
3164 p_item_key in wf_items.item_key%type,
3165 p_rfcRoleName in wf_roles.name%type,
3166 p_rfcUserOrigSystem in wf_roles.orig_system%type,
3167 p_rfcUserOrigSystemId in wf_roles.orig_system_id%type,
3168 p_rfc_comments in varchar2,
3169 p_approverIndex in number
3170 ) is
3171 -- local variables
3172 c_proc constant varchar2(30) := 'handleRFCAction';
3173 ln_rfc_notification_id wf_notifications.notification_id%type;
3174 l_lastDefaultApprover NUMBER;
3175 lv_dummy varchar2(10);
3176 lv_role_name wf_roles.name%type;
3177 lv_role_disp_name wf_roles.name%type;
3178 l_return_status varchar2(10);
3179 lv_role_orig_sys_id wf_roles.orig_system_id%type;
3180
3181 -- Cursor to find if the person (selected for RFC) is an additional approver
3182 CURSOR cur_add_appr IS
3183 SELECT 'X'
3184 FROM wf_item_attribute_values
3185 WHERE item_type = p_item_Type
3186 AND item_key = p_item_Key
3187 AND name like 'ADDITIONAL_APPROVER_%'
3188 AND number_value = p_rfcUserOrigSystemId;
3189 --
3190 -- Cursor to fetch the last default approver below the person performing
3191 -- RFC. It is used only in case of NON-AME approvals.
3192 --
3193 CURSOR cur_appr IS
3194 SELECT pth.employee_id
3195 FROM pqh_ss_approval_history pah,
3196 fnd_user pth
3197 WHERE pah.user_name = pth.user_name
3198 AND pah.transaction_history_id = p_transaction_id
3199 AND approval_history_id = (
3200 SELECT MAX(approval_history_id)
3201 FROM pqh_ss_approval_history pah1,
3202 fnd_user pth1
3203 WHERE pah1.user_name = pth1.user_name
3204 AND pah1.transaction_history_id = pah.transaction_history_id
3205 AND pth1.employee_id IN (
3206 SELECT pth2.employee_id --, pth2.user_name, approval_history_id
3207 FROM pqh_ss_approval_history pah2,
3208 fnd_user pth2
3209 WHERE pah2.user_name = pth2.user_name
3210 AND pah2.transaction_history_id = pah.transaction_history_id
3211 AND approval_history_id < (
3212 SELECT MIN(approval_history_id)
3213 FROM pqh_ss_approval_history
3214 WHERE transaction_history_id = pah.transaction_history_id
3215 AND user_name = lv_role_name
3216 AND approval_history_id > 0
3217 )
3218 and approval_history_id > 0
3219 MINUS
3220 SELECT number_value
3221 FROM wf_item_attribute_values
3222 WHERE item_type = p_item_Type
3223 AND item_key = p_item_Key
3224 AND name like 'ADDITIONAL_APPROVER_%'
3225 )
3226 );
3227
3228 begin
3229 g_debug := hr_utility.debug_enabled;
3230 if g_debug then
3231 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3232 end if;
3233
3234
3235
3236 -- fix for bug 4481775
3237 begin
3238 -- re-intialize the performer roles
3239 hr_approval_ss.reinitperformerroles(p_notification_id=>null
3240 ,p_transaction_id=>null
3241 ,p_item_type=>p_item_type
3242 ,p_item_key=>p_item_key);
3243 wf_directory.GetRoleName--GetUserName
3244 (p_orig_system => p_rfcUserOrigSystem
3245 ,p_orig_system_id => p_rfcUserOrigSystemId
3246 ,p_name => lv_role_name
3247 ,p_display_name => lv_role_disp_name);
3248
3249 exception
3250 when others then
3251 null;
3252 end;
3253
3254 -- set the item attributes used for RFC notification
3255 create_item_attrib_if_notexist(itemtype=>p_item_Type,
3256 itemkey =>p_item_Key,
3257 aname =>'RETURN_TO_USERNAME',
3258 text_value =>lv_role_name,
3259 number_value=>null,
3260 date_value =>null );
3261
3262 create_item_attrib_if_notexist(itemtype=>p_item_Type,
3263 itemkey =>p_item_Key,
3264 aname =>'RETURN_TO_USER_ORIG_SYS',
3265 text_value =>p_rfcUserOrigSystem,
3266 number_value=>null,
3267 date_value =>null );
3268 create_item_attrib_if_notexist(itemtype=>p_item_Type,
3269 itemkey =>p_item_Key,
3270 aname =>'RETURN_TO_USER_ORIG_SYS_ID',
3271 text_value =>null,
3272 number_value=>p_rfcUserOrigSystemId,
3273 date_value =>null );
3274 -- 'RETURN_TO_PERSON_DISPLAY_NAME'
3275 create_item_attrib_if_notexist(itemtype=>p_item_Type,
3276 itemkey =>p_item_Key,
3277 aname =>'RETURN_TO_PERSON_DISPLAY_NAME',
3278 text_value => lv_role_disp_name,
3279 number_value=>null,
3280 date_value =>null );
3281
3282 -- Set the notes for RFC notification.
3283 -- NOTE_FROM_APPR
3284 wf_engine.setItemAttrText (
3285 itemtype => p_item_type
3286 ,itemkey => p_item_Key
3287 ,aname => 'NOTE_FROM_APPR'
3288 ,avalue => p_rfc_comments );
3289 wf_engine.setItemAttrText (
3290 itemtype => p_item_type
3291 ,itemkey => p_item_Key
3292 ,aname => 'WF_NOTE'
3293 ,avalue => null );
3294
3295
3296 -- code logic to complete the approval notification with
3297 -- RETURNEDFORCORRECTION result
3298 wf_notification.setattrtext(p_approval_notification_id,
3299 'RESULT',
3300 'RETURNEDFORCORRECTION');
3301 BEGIN
3302 wf_notification.setattrtext(
3303 p_approval_notification_id
3304 ,'WF_NOTE'
3305 ,p_rfc_comments);
3306 EXCEPTION WHEN OTHERS THEN
3307 -- RFC from SFL Other
3308 wf_notification.propagatehistory(
3309 p_item_type
3310 ,p_item_key
3311 ,'APPROVAL_NOTIFICATION'
3312 ,fnd_global.user_name
3313 ,lv_role_name
3314 ,'RETURNEDFORCORRECTION'
3315 ,null
3316 ,p_rfc_comments);
3317 END;
3318
3319 -- now respond to the approval notification
3320 wf_notification.respond(p_approval_notification_id
3321 ,null
3322 ,fnd_global.user_name
3323 ,null);
3324
3325 hr_sflutil_ss.closeopensflnotification(p_transaction_id);
3326
3327 -- Bug 4898974 Fix.
3328 hr_transaction_api.finalize_transaction (
3329 P_TRANSACTION_ID => p_transaction_id
3330 ,P_EVENT => 'RFC'
3331 ,P_RETURN_STATUS => l_return_status
3332 );
3333
3334 -- logic to set the from role for rfc notification
3335 -- Fetch the id for RFC notification.
3336 begin
3337 SELECT ias.notification_id
3338 into ln_rfc_notification_id
3339 FROM WF_ITEM_ACTIVITY_STATUSES IAS
3340 WHERE ias.item_type = p_item_Type
3341 and ias.item_key = p_item_Key
3342 and IAS.ACTIVITY_STATUS = 'NOTIFIED'
3343 and notification_id is not null
3344 and rownum < 2;
3345 exception
3346 when others then
3347 null;
3348 end;
3349
3350 -- Set the from attribute for RFC notification.
3351 wf_notification.setAttrText(
3352 nid => ln_rfc_notification_id
3353 ,aname => '#FROM_ROLE'
3354 ,avalue => fnd_global.user_name );
3355 -- processing logic for non AME
3356 begin
3357 IF (wf_engine.GetItemAttrText(itemtype => p_item_Type ,
3358 itemkey => p_item_key,
3359 aname => 'HR_AME_TRAN_TYPE_ATTR') IS NULL) THEN
3360 --
3361 -- set the attribute value to null
3362 wf_engine.SetItemAttrNumber(
3363 itemtype => p_item_Type ,
3364 itemkey => p_item_key,
3365 aname => 'CURRENT_APPROVER_INDEX',
3366 avalue => p_approverIndex);
3367 --
3368 -- If the selected person (for RFC) is additional approver
3369 -- then fetch the last default approver from history
3370 -- else selected person is the last default approver
3371 IF ( cur_add_appr%ISOPEN ) THEN
3372 CLOSE cur_add_appr;
3373 END IF;
3374 --
3375 OPEN cur_add_appr;
3376 FETCH cur_add_appr INTO lv_dummy;
3377 if cur_add_appr%found then
3378 --
3379 IF ( cur_appr%ISOPEN ) THEN
3380 CLOSE cur_appr;
3381 END IF;
3382 --
3383 OPEN cur_appr;
3384 FETCH cur_appr INTO l_lastDefaultApprover;
3385 CLOSE cur_appr;
3386 --
3387 IF ( l_lastDefaultApprover IS NULL ) THEN
3388
3389 begin
3390 select employee_id into lv_role_orig_sys_id from (
3391 SELECT pth.employee_id
3392 FROM pqh_ss_approval_history pah,
3393 fnd_user pth
3394 WHERE pah.user_name = pth.user_name
3395 AND pah.transaction_history_id = p_transaction_id
3396 and approval_history_id > 0
3397 and approval_history_id = (select min(approval_history_id) from pqh_ss_approval_history where USER_NAME = p_rfcRoleName and transaction_history_id = p_transaction_id)
3398 and pah.last_update_date <(select min(last_update_date) from pqh_ss_approval_history where USER_NAME = p_rfcRoleName and transaction_history_id = p_transaction_id
3399 and approval_history_id = (select min(approval_history_id) from pqh_ss_approval_history where USER_NAME = p_rfcRoleName and transaction_history_id = p_transaction_id))
3400 order by pah.last_update_date desc
3401 )
3402 where rownum = 1;
3403 exception
3404 when others then
3405 null;
3406 end;
3407 l_lastDefaultApprover := lv_role_orig_sys_id;
3408 END IF;
3409 else
3410 l_lastDefaultApprover := p_rfcUserOrigSystemId;
3411 end if;
3412
3413 CLOSE cur_add_appr;
3414
3415 IF ( l_lastDefaultApprover IS NOT NULL ) THEN
3416 wf_engine.SetItemAttrNumber(
3417 itemtype => p_item_Type ,
3418 itemkey => p_item_key,
3419 aname => 'LAST_DEFAULT_APPROVER',
3420 avalue => l_lastDefaultApprover);
3421 END IF;
3422 end if;
3423 exception
3424 when others then
3425 null;
3426 end;
3427
3428 if (g_debug ) then
3429 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
3430 end if;
3431
3432 exception
3433 when others then
3434 if g_debug then
3435 hr_utility.set_location('Error in handleRFCAction SQLERRM' ||' '||to_char(SQLCODE),20);
3436 end if;
3437
3438 end handleRFCAction;
3439
3440 procedure handleRFCAction(p_approval_notification_id in wf_notifications.notification_id%type
3441 ) is
3442 lv_item_type wf_items.item_type%type;
3443 lv_item_key wf_items.item_key%type;
3444 lv_creator_role wf_roles.name%type;
3445 lv_creator_orig_system wf_roles.orig_system%type;
3446 lv_creator_orig_sys_id wf_roles.orig_system_id%type;
3447 begin
3448 hr_workflow_ss.get_item_type_and_key(p_approval_notification_id,lv_item_type,lv_item_key);
3449
3450 -- fix for bug 4481775
3451 begin
3452 -- re-intialize the performer roles
3453 hr_approval_ss.reinitperformerroles(p_notification_id=>null
3454 ,p_transaction_id=>null
3455 ,p_item_type=>lv_item_type
3456 ,p_item_key=>lv_item_key);
3457 exception
3458 when others then
3459 null;
3460 end;
3461
3462
3463 lv_creator_role := wf_engine.getitemattrtext(lv_item_type,
3464 lv_item_key,
3465 'CREATOR_PERSON_USERNAME');
3466 -- get the orig sys info
3467 wf_directory.getroleorigsysinfo(lv_creator_role,
3468 lv_creator_orig_system,
3469 lv_creator_orig_sys_id);
3470
3471 handleRFCAction(p_approval_notification_id =>p_approval_notification_id,
3472 p_transaction_id =>wf_engine.getitemattrnumber
3473 (lv_item_type,
3474 lv_item_key,
3475 'TRANSACTION_ID'),
3476 p_item_type =>lv_item_type,
3477 p_item_key =>lv_item_key,
3478 p_rfcRoleName =>lv_creator_role,
3479 p_rfcUserOrigSystem =>lv_creator_orig_system,
3480 p_rfcUserOrigSystemId =>lv_creator_orig_sys_id,
3481 p_rfc_comments=>null,
3482 p_approverIndex=>0
3483 );
3484
3485 end handleRFCAction;
3486
3487 procedure creatorRoleReInit(p_item_type in wf_items.item_type%type,
3488 p_item_key in wf_items.item_key%type) is
3489 lv_role_name wf_roles.name%type;
3490 lv_role_disp_name wf_roles.name%type;
3491 lv_role_orig_system wf_roles.orig_system%type;
3492 lv_role_orig_sys_id wf_roles.orig_system_id%type;
3493 begin
3494
3495 -- CREATOR_PERSON_ID
3496 lv_role_orig_sys_id:=wf_engine.getitemattrnumber(p_item_type,p_item_key,'CREATOR_PERSON_ID',true);
3497
3498 if(lv_role_orig_sys_id is not null) then
3499 -- need to revisit with role based support in SSHR transaction
3500 lv_role_orig_system := 'PER';
3501
3502 wf_directory.GetRoleName
3503 (p_orig_system => lv_role_orig_system
3504 ,p_orig_system_id => lv_role_orig_sys_id
3505 ,p_name => lv_role_name
3506 ,p_display_name => lv_role_disp_name);
3507
3508 -- set the concerned item attributes
3509 -- CREATOR_PERSON_USERNAME
3510 create_item_attrib_if_notexist(itemtype => p_item_type
3511 ,itemkey => p_item_key
3512 ,aname => 'CREATOR_PERSON_USERNAME'
3513 ,text_value=>lv_role_name
3514 ,number_value=>null,
3515 date_value=>null
3516 );
3517 -- APPROVAL_CREATOR_USERNAME
3518 create_item_attrib_if_notexist(itemtype => p_item_type
3519 ,itemkey => p_item_key
3520 ,aname => 'APPROVAL_CREATOR_USERNAME'
3521 ,text_value=>lv_role_name
3522 ,number_value=>null,
3523 date_value=>null
3524 );
3525
3526 end if;
3527
3528 end creatorRoleReInit;
3529
3530
3531 procedure selPersonRoleReInit(p_item_type in wf_items.item_type%type,
3532 p_item_key in wf_items.item_key%type) is
3533 lv_role_name wf_roles.name%type;
3534 lv_role_disp_name wf_roles.name%type;
3535 lv_role_orig_system wf_roles.orig_system%type;
3536 lv_role_orig_sys_id wf_roles.orig_system_id%type;
3537 begin
3538
3539 -- CURRENT_PERSON_ID
3540 lv_role_orig_sys_id:=wf_engine.getitemattrnumber(p_item_type,p_item_key,'CURRENT_PERSON_ID',true);
3541
3542 if(lv_role_orig_sys_id is not null) then
3543 -- need to revisit with role based support in SSHR transaction
3544 lv_role_orig_system := 'PER';
3545 wf_directory.GetRoleName
3546 (p_orig_system => lv_role_orig_system
3547 ,p_orig_system_id => lv_role_orig_sys_id
3548 ,p_name => lv_role_name
3549 ,p_display_name => lv_role_disp_name);
3550 -- set the concerned item attributes
3551 -- CURRENT_PERSON_USERNAME
3552 create_item_attrib_if_notexist(itemtype => p_item_type
3553 ,itemkey => p_item_key
3554 ,aname => 'CURRENT_PERSON_USERNAME'
3555 ,text_value=>lv_role_name
3556 ,number_value=>null,
3557 date_value=>null
3558 );
3559 end if;
3560
3561 end selPersonRoleReInit;
3562
3563 procedure forwardFromRoleReInit(p_item_type in wf_items.item_type%type,
3564 p_item_key in wf_items.item_key%type) is
3565 lv_role_name wf_roles.name%type;
3566 lv_role_disp_name wf_roles.name%type;
3567 lv_role_orig_system wf_roles.orig_system%type;
3568 lv_role_orig_sys_id wf_roles.orig_system_id%type;
3569 begin
3570
3571 -- FORWARD_FROM_PERSON_ID
3572 lv_role_orig_sys_id:=wf_engine.getitemattrnumber(p_item_type,p_item_key,'FORWARD_FROM_PERSON_ID',true);
3573
3574 if(lv_role_orig_sys_id is not null) then
3575 -- need to revisit with role based support in SSHR transaction
3576 lv_role_orig_system := nvl( wf_engine.GetItemAttrText
3577 (itemtype => p_item_type
3578 ,itemkey => p_item_key
3579 ,aname => 'FORWARD_FROM_ORIG_SYS_ATTR'
3580 ,ignore_notfound=>true),'PER');
3581
3582 wf_directory.GetRoleName
3583 (p_orig_system => lv_role_orig_system
3584 ,p_orig_system_id => lv_role_orig_sys_id
3585 ,p_name => lv_role_name
3586 ,p_display_name => lv_role_disp_name);
3587 -- set the concerned item attributes
3588 -- FORWARD_FROM_USERNAME
3589 create_item_attrib_if_notexist(itemtype => p_item_type
3590 ,itemkey => p_item_key
3591 ,aname => 'FORWARD_FROM_USERNAME'
3592 ,text_value=>lv_role_name
3593 ,number_value=>null,
3594 date_value=>null
3595 );
3596 end if;
3597
3598 end forwardFromRoleReInit;
3599
3600
3601 procedure rfcUserRoleReInit(p_item_type in wf_items.item_type%type,
3602 p_item_key in wf_items.item_key%type) is
3603 lv_role_name wf_roles.name%type;
3604 lv_role_disp_name wf_roles.name%type;
3605 lv_role_orig_system wf_roles.orig_system%type;
3606 lv_role_orig_sys_id wf_roles.orig_system_id%type;
3607 begin
3608
3609 -- RETURN_TO_USER_ORIG_SYS_ID
3610 lv_role_orig_sys_id:=wf_engine.getitemattrnumber(p_item_type,p_item_key,'RETURN_TO_USER_ORIG_SYS_ID',true);
3611
3612 if(lv_role_orig_sys_id is not null) then
3613 -- need to revisit with role based support in SSHR transaction
3614 lv_role_orig_system := wf_engine.GetItemAttrText
3615 (itemtype => p_item_type
3616 ,itemkey => p_item_key
3617 ,aname => 'RETURN_TO_USER_ORIG_SYS'
3618 ,ignore_notfound=>true);
3619
3620 wf_directory.GetRoleName
3621 (p_orig_system => lv_role_orig_system
3622 ,p_orig_system_id => lv_role_orig_sys_id
3623 ,p_name => lv_role_name
3624 ,p_display_name => lv_role_disp_name);
3625 -- set the concerned item attributes
3626 -- RETURN_TO_USERNAME
3627 create_item_attrib_if_notexist(itemtype => p_item_type
3628 ,itemkey => p_item_key
3629 ,aname => 'RETURN_TO_USERNAME'
3630 ,text_value=>lv_role_name
3631 ,number_value=>null,
3632 date_value=>null
3633 );
3634 end if;
3635
3636 end rfcUserRoleReInit;
3637
3638
3639 procedure appraisalRolesReInit(p_item_type in wf_items.item_type%type,
3640 p_item_key in wf_items.item_key%type) is
3641 lv_role_name wf_roles.name%type;
3642 lv_role_disp_name wf_roles.name%type;
3643 lv_role_orig_system wf_roles.orig_system%type;
3644 lv_role_orig_sys_id wf_roles.orig_system_id%type;
3645 l_appraisal_id per_appraisals.appraisal_id%type;
3646 l_main_appraiser_id per_appraisals.main_appraiser_id%type;
3647 l_appraiser_person_id per_appraisals.appraiser_person_id%type;
3648 l_appraisee_person_id per_appraisals.appraisee_person_id%type;
3649 begin
3650
3651 -- get the appraisal id
3652 l_appraisal_id:= wf_engine.GetItemAttrNumber (itemtype => p_item_type ,
3653 itemkey => p_item_key ,
3654 aname => 'APPRAISAL_ID',
3655 ignore_notfound=>true);
3656
3657 -- get the appraisee, main appraiser and appraiser id
3658 if(l_appraisal_id is not null) then
3659 begin
3660
3661 select MAIN_APPRAISER_ID,APPRAISER_PERSON_ID,APPRAISEE_PERSON_ID
3662 into l_main_appraiser_id, l_appraiser_person_id,l_appraisee_person_id
3663 from per_appraisals
3664 where APPRAISAL_ID=l_appraisal_id;
3665
3666 exception
3667 when others then
3668 null;
3669 end;
3670
3671 -- need to revisit with role based support in SSHR transaction
3672 lv_role_orig_system := 'PER';
3673
3674 -- derive the role for HR_APPRAISEE_USER_NAME_ATTR
3675 if((l_appraisee_person_id is not null)and (l_appraisee_person_id<>'')) then
3676 wf_directory.GetRoleName
3677 (p_orig_system => lv_role_orig_system
3678 ,p_orig_system_id => l_appraisee_person_id
3679 ,p_name => lv_role_name
3680 ,p_display_name => lv_role_disp_name);
3681 -- set the concerned item attributes
3682 -- HR_APPRAISEE_USER_NAME_ATTR
3683 create_item_attrib_if_notexist(itemtype => p_item_type
3684 ,itemkey => p_item_key
3685 ,aname => 'HR_APPRAISEE_USER_NAME_ATTR'
3686 ,text_value=>lv_role_name
3687 ,number_value=>null,
3688 date_value=>null
3689 );
3690 end if;
3691
3692 -- derive the role for HR_MAIN_APPRAISER_USERNAME
3693 if(l_main_appraiser_id is not null) then
3694 wf_directory.GetRoleName
3695 (p_orig_system => lv_role_orig_system
3696 ,p_orig_system_id => l_main_appraiser_id
3697 ,p_name => lv_role_name
3698 ,p_display_name => lv_role_disp_name);
3699 -- set the concerned item attributes
3700 -- HR_MAIN_APPRAISER_USERNAME
3701 create_item_attrib_if_notexist(itemtype => p_item_type
3702 ,itemkey => p_item_key
3703 ,aname => 'HR_MAIN_APPRAISER_USERNAME'
3704 ,text_value=>lv_role_name
3705 ,number_value=>null,
3706 date_value=>null
3707 );
3708 end if;
3709
3710 -- derive the role for SUPERVISOR_USERNAME
3711 if(l_appraiser_person_id is not null) then
3712 wf_directory.GetRoleName
3713 (p_orig_system => lv_role_orig_system
3714 ,p_orig_system_id => l_appraiser_person_id
3715 ,p_name => lv_role_name
3716 ,p_display_name => lv_role_disp_name);
3717 -- set the concerned item attributes
3718 -- SUPERVISOR_USERNAME
3719 create_item_attrib_if_notexist(itemtype => p_item_type
3720 ,itemkey => p_item_key
3721 ,aname => 'SUPERVISOR_USERNAME'
3722 ,text_value=>lv_role_name
3723 ,number_value=>null,
3724 date_value=>null
3725 );
3726 end if;
3727 end if;
3728
3729 end appraisalRolesReInit;
3730
3731
3732 procedure reInitPerformerRoles(p_notification_id in wf_notifications.notification_id%type,
3733 p_transaction_id in hr_api_transactions.transaction_id%type,
3734 p_item_type in wf_items.item_type%type,
3735 p_item_key in wf_items.item_key%type) is
3736 c_proc constant varchar2(30) := 'reInitPerformerRoles';
3737 lv_item_type wf_items.item_type%type;
3738 lv_item_key wf_items.item_key%type;
3739
3740 begin
3741 g_debug := hr_utility.debug_enabled;
3742 if g_debug then
3743 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3744 end if;
3745
3746 -- get the item type item key
3747 if(p_notification_id is not null) then
3748 begin
3749 select substr(context,1,instr(context,':',1)-1) itemtype
3750 ,substr(context,instr(context,':')+1, (
3751 instr(context,':',instr(context,':')+1 ) - instr(context,':')-1) ) itemkey
3752 into lv_item_type,lv_item_key
3753 from wf_notifications
3754 where notification_id = p_notification_id;
3755 exception
3756 when others then
3757 null;
3758 end;
3759 elsif(p_transaction_id is not null) then
3760 begin
3761 select item_type,item_key
3762 into lv_item_type,lv_item_key
3763 from hr_api_transactions
3764 where transaction_id=p_transaction_id;
3765 exception
3766 when others then
3767 null;
3768 end;
3769 else
3770 lv_item_type := p_item_type;
3771 lv_item_key := p_item_key;
3772 end if;
3773
3774
3775 if((lv_item_type is not null) and (lv_item_key is not null)) then
3776 -- handle creator role reinit
3777 creatorRoleReInit(lv_item_type,lv_item_key);
3778 -- current or selected person role reinit
3779 selPersonRoleReInit(lv_item_type,lv_item_key);
3780 -- handle forward to user role reinit
3781 forwardToRoleReInit(lv_item_type,lv_item_key);
3782 -- handle forward from user role reinit
3783 forwardFromRoleReInit(lv_item_type,lv_item_key);
3784 -- handle RFC user reinit
3785 rfcUserRoleReInit(lv_item_type,lv_item_key);
3786 -- other module specifics
3787 appraisalRolesReInit(lv_item_type,lv_item_key);
3788 end if;
3789
3790 exception
3791 when others then
3792 if g_debug then
3793 hr_utility.set_location('Error in reInitPerformerRoles SQLERRM' ||' '||to_char(SQLCODE),20);
3794 end if;
3795 raise;
3796
3797 end reInitPerformerRoles;
3798
3799
3800 procedure approvals_block
3801 (itemtype in varchar2
3802 ,itemkey in varchar2
3803 ,actid in number
3804 ,funmode in varchar2
3805 ,result in out nocopy varchar2)
3806 is
3807 --local variables
3808
3809 begin
3810 -- Do nothing in cancel or timeout mode
3811 if (funmode <> wf_engine.eng_run) then
3812 result := wf_engine.eng_null;
3813 return;
3814 end if;
3815 -- set the item attribute value with the current activity id
3816 -- this will be used when the notification is sent.
3817 -- and to complete the blocked thread.
3818 -- HR_APPROVAL_BLOCK_ID_ATTR
3819 create_item_attrib_if_notexist(itemtype => itemtype
3820 ,itemkey => itemkey
3821 ,aname => 'HR_APPROVAL_BLOCK_ID_ATTR'
3822 ,text_value=>null
3823 ,number_value=>actid,
3824 date_value=>null
3825 );
3826 WF_STANDARD.BLOCK(itemtype,itemkey,actid,funmode,result);
3827
3828 --resultout := 'NOTIFIED';
3829
3830 exception
3831 when others then
3832 Wf_Core.Context(g_package, '.approvals_block', itemtype,
3833 itemkey, to_char(actid), funmode);
3834 raise;
3835 end approvals_block;
3836
3837
3838
3839
3840 -- new code for generic approval process
3841 -- without page navigation
3842 -- ----------------------------------------------------------------------------
3843 -- |--------------------< wf_get_runnable_process_name >----------------------|
3844 -- ----------------------------------------------------------------------------
3845 function wf_get_runnable_process_name
3846 (p_item_type in wf_items.item_type%type
3847 ,p_process_name in wf_process_activities.process_name%type)
3848 return wf_runnable_processes_v.display_name%type is
3849 -- cursor determines is the specified process is runnable
3850 cursor csr_wrpv is
3851 select wrpv.display_name
3852 from wf_runnable_processes_v wrpv
3853 where wrpv.item_type = p_item_type
3854 and wrpv.process_name = p_process_name;
3855 --
3856 l_display_name wf_runnable_processes_v.display_name%type;
3857 --
3858 begin
3859 -- Determine if the specified process is runnable
3860 open csr_wrpv;
3861 fetch csr_wrpv into l_display_name;
3862 if csr_wrpv%notfound then
3863 close csr_wrpv;
3864 return(NULL);
3865 end if;
3866 close csr_wrpv;
3867 return(l_display_name);
3868 end wf_get_runnable_process_name;
3869 -- ----------------------------------------------------------------------------
3870 -- |-------------------------< wf_process_runnable >--------------------------|
3871 -- ----------------------------------------------------------------------------
3872 function wf_process_runnable
3873 (p_item_type in wf_items.item_type%type
3874 ,p_process_name in wf_process_activities.process_name%type)
3875 return boolean is
3876 --
3877 begin
3878 if wf_get_runnable_process_name
3879 (p_item_type => p_item_type
3880 ,p_process_name => p_process_name) is NULL then
3881 return(FALSE);
3882 else
3883 return(TRUE);
3884 end if;
3885 end wf_process_runnable;
3886
3887 -- ----------------------------------------------------------------------------
3888 -- |------------------------< create_hr_directory_services >------------------|
3889 -- ----------------------------------------------------------------------------
3890 procedure create_hr_directory_services
3891 (p_item_type in wf_items.item_type%type
3892 ,p_item_key in wf_items.item_key%type
3893 ,p_service_name in varchar2
3894 ,p_service_orig_sys_id in number
3895 ,p_service_orig_sys in varchar2) is
3896 --
3897 l_item_type_attribute_name varchar2(30);
3898 type l_suffix_tab is table of varchar2(30) index by binary_integer;
3899 l_suffix l_suffix_tab;
3900 l_username wf_users.name%type;
3901 l_display_name wf_users.display_name%type;
3902 --
3903 begin
3904 if p_service_orig_sys_id is not null then
3905 l_suffix(1) := 'ID';
3906 l_suffix(2) := 'USERNAME';
3907 l_suffix(3) := 'DISPLAY_NAME';
3908 l_suffix(4) := 'ORIG_SYS';
3909 -- get the USERNAME and DISPLAY_NAME from workflow
3910 begin
3911 wf_directory.getrolename
3912 (p_orig_system => p_service_orig_sys
3913 ,p_orig_system_id => p_service_orig_sys_id
3914 ,p_name => l_username
3915 ,p_display_name => l_display_name);
3916 exception
3917 when others then
3918 null;
3919 end;
3920 for i in 1..4 loop
3921 l_item_type_attribute_name := p_service_name||'_'||l_suffix(i);
3922
3923 -- set the item attribue value
3924 if i = 1 then
3925 -- set the ID value
3926 create_item_attrib_if_notexist(itemtype => p_item_type
3927 ,itemkey => p_item_key
3928 ,aname => l_item_type_attribute_name
3929 ,text_value=>null
3930 ,number_value=>p_service_orig_sys_id,
3931 date_value=>null
3932 );
3933
3934 elsif i = 2 then
3935 -- set the USERNAME value
3936 create_item_attrib_if_notexist(itemtype => p_item_type
3937 ,itemkey => p_item_key
3938 ,aname => l_item_type_attribute_name
3939 ,text_value=>l_username
3940 ,number_value=>null,
3941 date_value=>null
3942 );
3943 elsif i = 3 then
3944 -- set the DISPLAY_NAME value
3945 create_item_attrib_if_notexist(itemtype => p_item_type
3946 ,itemkey => p_item_key
3947 ,aname => l_item_type_attribute_name
3948 ,text_value=>l_display_name
3949 ,number_value=>null,
3950 date_value=>null
3951 );
3952
3953 else
3954 -- set the ORIG_SYS value
3955 create_item_attrib_if_notexist(itemtype => p_item_type
3956 ,itemkey => p_item_key
3957 ,aname => l_item_type_attribute_name
3958 ,text_value=>p_service_orig_sys
3959 ,number_value=>null,
3960 date_value=>null
3961 );
3962
3963 end if;
3964 end loop;
3965 end if;
3966 end create_hr_directory_services;
3967 -- ----------------------------------------------------------------------------
3968 -- getPersonNameFromID --
3969 -- called internally to give the person name for the given user name --
3970 -- ----------------------------------------------------------------------------
3971 --
3972 FUNCTION getPersonNameFromID
3973 (p_person_id per_all_people_f.person_id%type)
3974 return per_all_people_f.full_name%type is
3975 --
3976 cursor csr_full_name
3977 is select full_name
3978 from per_all_people_f papf
3979 where papf.person_id = p_person_id
3980 and trunc(sysdate) between effective_start_date
3981 and effective_end_date;
3982 --
3983 l_employee_name per_all_people_f.full_name%type;
3984 --
3985 BEGIN
3986 --
3987 hr_utility.trace('Finding Person name for person_id :' || p_person_id || ':');
3988 --
3989 open csr_full_name;
3990 fetch csr_full_name into l_employee_name;
3991 --
3992 if csr_full_name%notfound then
3993 l_employee_name := ' ';
3994 end if;
3995 close csr_full_name;
3996 --
3997 hr_utility.trace('Found :' || l_employee_name || ':');
3998 --
3999 return l_employee_name;
4000 --
4001 END getPersonNameFromID;
4002
4003 procedure startGenericApprovalProcess(p_transaction_id in number
4004 ,p_item_key in out nocopy wf_items.item_key%type
4005 ,p_wf_ntf_sub_fnd_msg in fnd_new_messages.message_name%type
4006 ,p_relaunch_function hr_api_transactions.relaunch_function%type
4007 ,p_additional_wf_attributes in HR_WF_ATTR_TABLE
4008 ,p_status out nocopy varchar2
4009 ,p_error_message out nocopy varchar2
4010 ,p_errstack out nocopy varchar2
4011 )
4012 is
4013 c_proc constant varchar2(30) := 'reInitPerformerRoles';
4014 lv_item_type wf_items.item_type%type;
4015 lv_item_key wf_items.item_key%type;
4016 lr_transaction_rec hr_api_transactions%rowtype;
4017 lv_status varchar2(8);
4018 lv_result varchar2(30);
4019 lv_errorActid wf_item_activity_statuses.process_activity%type;
4020 lv_errname VARCHAR2(4000);
4021 l_index binary_integer;
4022 l_temp_item_attribute varchar2(2000);
4023 l_role_name wf_roles.name%type;
4024 l_role_displayname wf_roles.display_name%type;
4025 l_current_person_name per_all_people_f.full_name%type;
4026 l_manager_id per_all_people_f.person_id%type;
4027 begin
4028 g_debug := hr_utility.debug_enabled;
4029 if g_debug then
4030 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4031 end if;
4032 -- check if the transaction id is passed if not raise exception
4033 -- no more iterations
4034 if(p_transaction_id is not null) then
4035 -- block to get the transaction record details
4036 begin
4037 select *
4038 into lr_transaction_rec
4039 from hr_api_transactions
4040 where transaction_id=p_transaction_id;
4041 exception
4042 when no_data_found then
4043 raise;
4044 -- need proper error message propagated
4045 when others then
4046 raise;
4047 end;
4048
4049 -- test value
4050 lr_transaction_rec.item_type:=nvl(lr_transaction_rec.item_type,'HRSSA');
4051 lr_transaction_rec.process_name :=nvl(lr_transaction_rec.process_name,'HR_GENERIC_APPROVAL_PRC');
4052 -- end of test value
4053 -- Determine if the specified process is runnable
4054 if NOT wf_process_runnable
4055 (p_item_type => lr_transaction_rec.item_type
4056 ,p_process_name => lr_transaction_rec.process_name
4057 ) then
4058 -- supply HR error message, p_process_name either does not exist or
4059 -- is NOT a runnable process
4060 hr_utility.set_message(800,'HR_52958_WKF2TSK_INC_PROCESS');
4061 hr_utility.set_message_token('ITEM_TYPE', lr_transaction_rec.item_type);
4062 hr_utility.set_message_token('PROCESS_NAME', lr_transaction_rec.process_name);
4063 hr_utility.raise_error;
4064 end if;
4065
4066 -- now check if we need to derive the item key from db seq
4067 if(p_item_key is null)then
4068 begin
4069 -- Get the next item key from the sequence
4070 select hr_workflow_item_key_s.nextval
4071 into p_item_key
4072 from sys.dual;
4073 exception
4074 when no_data_found then
4075 raise;
4076 -- need to pass proper error message
4077 -- this is a fatal error in getting the sequence value
4078 when others then
4079 raise;
4080 end;
4081 end if;
4082
4083 -- Create the Workflow Process
4084 wf_engine.CreateProcess
4085 (itemtype => lr_transaction_rec.item_type
4086 ,itemkey => p_item_key
4087 ,process => lr_transaction_rec.process_name);
4088
4089 -- check the process status before setting
4090 -- other mandatory attributes
4091 -- check the state of the workflow
4092 -- we need to check if the flow is in error state or not
4093 wf_engine.iteminfo(lr_transaction_rec.item_type,
4094 p_item_key,
4095 p_status,
4096 lv_result,
4097 lv_errorActid,
4098 lv_errname,
4099 p_error_message,
4100 p_errstack);
4101
4102 if(lv_status = 'ERROR') then
4103 raise g_wf_error_state;
4104 end if;
4105
4106 -- Derive the mandatory roles and other details
4107 -- required for wf initalizations
4108
4109 -- add new attribute for deriving the ntf subject
4110 -- HR_NTF_SUB_FND_MSG_ATTR
4111 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
4112 ,itemkey => p_item_key
4113 ,aname => 'HR_NTF_SUB_FND_MSG_ATTR'
4114 ,text_value=>p_wf_ntf_sub_fnd_msg
4115 ,number_value=>null,
4116 date_value=>null
4117 );
4118 --
4119 -- Create the standard set of item attributes
4120 -- CURRENT_PERSON_ID and CREATOR_PERSON_ID
4121 --
4122 create_hr_directory_services
4123 (p_item_type => lr_transaction_rec.item_type
4124 ,p_item_key => p_item_key
4125 ,p_service_name => 'CREATOR_PERSON'
4126 ,p_service_orig_sys_id =>lr_transaction_rec.creator_person_id
4127 ,p_service_orig_sys =>'PER' -- need to revisit for role based support
4128 );
4129 create_hr_directory_services
4130 (p_item_type => lr_transaction_rec.item_type
4131 ,p_item_key => p_item_key
4132 ,p_service_name => 'CURRENT_PERSON'
4133 ,p_service_orig_sys_id =>lr_transaction_rec.selected_person_id
4134 ,p_service_orig_sys =>'PER' -- need to revisit for role based support
4135 );
4136
4137 -- Get Vacancy name and manager/creator name if the transaction is related to vacancy
4138
4139 if lr_transaction_rec.transaction_ref_table ='PER_ALL_VACANCIES' then
4140
4141 l_manager_id := hr_xml_util.get_node_value(lr_transaction_rec.transaction_id,
4142 'ManagerId',
4143 'Transaction/TransCache/AM/TXN/EO/PerRequisitionsEORow/CEO/EO/PerAllVacanciesEORow');
4144
4145 if l_manager_id is not null then
4146 l_current_person_name := getpersonnamefromid(l_manager_id);
4147 else
4148 l_current_person_name := getpersonnamefromid(lr_transaction_rec.creator_person_id);
4149 end if;
4150 l_current_person_name := l_current_person_name||'('||lr_transaction_rec.api_addtnl_info||')';
4151
4152 wf_engine.setItemattrtext(lr_transaction_rec.item_type,p_item_key,'CURRENT_PERSON_DISPLAY_NAME',l_current_person_name);
4153 end if;
4154
4155 -- Get Applicant name if the transaction is related to offers
4156
4157 if lr_transaction_rec.transaction_ref_table ='IRC_OFFERS' then
4158 l_current_person_name := lr_transaction_rec.api_addtnl_info;
4159 wf_engine.setItemattrtext(lr_transaction_rec.item_type,p_item_key,'CURRENT_PERSON_DISPLAY_NAME',l_current_person_name);
4160 end if;
4161 -- Create Item Attributes for those passed in
4162 --
4163 l_index := 1;
4164 --
4165 WHILE l_index <= p_additional_wf_attributes.count LOOP
4166 begin
4167 -- upper the item attribute name
4168 -- if a NO_DATA_FOUND exception occurs, the exception is
4169 -- handled and the item is skipped
4170 l_temp_item_attribute := upper(p_additional_wf_attributes(l_index).name);
4171 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
4172 ,itemkey => p_item_key
4173 ,aname => l_temp_item_attribute
4174 ,text_value=>p_additional_wf_attributes(l_index).text_value
4175 ,number_value=>p_additional_wf_attributes(l_index).number_value,
4176 date_value=>p_additional_wf_attributes(l_index).date_value
4177 );
4178 l_index := l_index + 1;
4179 exception
4180 when NO_DATA_FOUND then
4181 -- The array element at the index position has not been set
4182 -- Ignore, but increment the counter and continue with the LOOP
4183 l_index := l_index + 1;
4184 end;
4185 END LOOP;
4186
4187
4188 --
4189 -- ---------------------------------
4190 -- Get the Role for the Owner
4191 -- ---------------------------------
4192 wf_directory.getRoleName
4193 (p_orig_system => 'PER'
4194 ,p_orig_system_id => lr_transaction_rec.creator_person_id
4195 ,p_name => l_role_name
4196 ,p_display_name => l_role_displayname);
4197
4198 IF l_role_name = '' OR l_role_name IS NULL THEN
4199 RAISE g_invalid_responsibility;
4200 END IF;
4201 -- ---------------------------------------------------
4202 -- Set the Item Owner
4203 -- ---------------------------------------------------
4204 wf_engine.setItemOwner
4205 (itemtype => lr_transaction_rec.item_type
4206 ,itemkey => p_item_key
4207 ,owner => l_role_name);
4208
4209 -- set 'CURRENT_EFFECTIVE_DATE'
4210 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
4211 ,itemkey => p_item_key
4212 ,aname => 'CURRENT_EFFECTIVE_DATE'
4213 ,text_value=>null
4214 ,number_value=>null,
4215 date_value=>lr_transaction_rec.transaction_effective_date
4216 );
4217 -- set 'CURRENT_EFFECTIVE_DATE'
4218 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
4219 ,itemkey => p_item_key
4220 ,aname => 'CURRENT_EFFECTIVE_DATE'
4221 ,text_value=>null
4222 ,number_value=>null,
4223 date_value=>lr_transaction_rec.transaction_effective_date
4224 );
4225 -- set HR_OAF_EDIT_URL_ATTR
4226 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
4227 ,itemkey => p_item_key
4228 ,aname => 'HR_OAF_EDIT_URL_ATTR'
4229 ,text_value=>p_relaunch_function
4230 ,number_value=>null,
4231 date_value=>null
4232 );
4233 -- set HR_OAF_NAVIGATION_ATTR
4234 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
4235 ,itemkey => p_item_key
4236 ,aname => 'HR_OAF_NAVIGATION_ATTR'
4237 ,text_value=>'N'
4238 ,number_value=>null,
4239 date_value=>null
4240 );
4241
4242 -- now start the process
4243 -- Start the WF runtime process
4244 wf_engine.startprocess
4245 (itemtype => lr_transaction_rec.item_type
4246 ,itemkey => p_item_key);
4247
4248 -- check the wf status before returning the status back to caller
4249 -- we need to check if the flow is in error state or not
4250 wf_engine.iteminfo(lr_transaction_rec.item_type,
4251 p_item_key,
4252 p_status,
4253 lv_result,
4254 lv_errorActid,
4255 lv_errname,
4256 p_error_message,
4257 p_errstack);
4258
4259 if(lv_status = 'ERROR') then
4260 raise g_wf_error_state;
4261 end if;
4262
4263
4264 else
4265 raise g_no_transaction_id;
4266 end if;
4267
4268 if (g_debug ) then
4269 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
4270 end if;
4271
4272 exception
4273 WHEN g_invalid_responsibility THEN
4274 fnd_message.set_name('PER','HR_SSA_INVALID_RESPONSIBILITY');
4275 hr_utility.raise_error;
4276 when g_wf_error_state then
4277 -- HR_WF_TRANSACTION_ERROR_SS
4278 fnd_message.set_name('PER','HR_WF_TRANSACTION_ERROR_SS');
4279 fnd_message.set_token('ERRORMSG',p_error_message,true);
4280 fnd_message.set_token('ERRORSTACK',p_errstack,true);
4281 hr_utility.raise_error;
4282 when g_no_transaction_id then
4283 null;
4284 -- handle the proper error message propagation
4285 when others then
4286 raise;
4287 end startGenericApprovalProcess;
4288
4289
4290 function getinitApprovalBlockId(p_transaction_id in number) return number
4291 is
4292
4293 c_proc constant varchar2(30) := 'getinitApprovalBlockId';
4294 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
4295 ln_activity_id number;
4296 lv_loginPersonDispName per_all_people_f.full_name%type;
4297 lv_loginPersonUserName fnd_user.user_name%type;
4298 ln_loginPersonId fnd_user.employee_id%type;
4299 begin
4300 if(p_transaction_id is not null) then
4301 select * into lr_hr_api_transaction_rec
4302 from hr_api_transactions
4303 where transaction_id=p_transaction_id;
4304 if(lr_hr_api_transaction_rec.transaction_ref_table='PER_APPRAISALS') then
4305 -- appraisal specfic
4306
4307 -- set the item attributes specific to appraisals
4308 -- get the wf role info for the login user
4309 wf_directory.getusername
4310 (p_orig_system => 'PER'
4311 ,p_orig_system_id => fnd_global.employee_id
4312 ,p_name => lv_loginPersonUserName
4313 ,p_display_name => lv_loginPersonDispName);
4314 --HR_APPRAISAL_FROM_USER_ATTR
4315 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
4316 ,lr_hr_api_transaction_rec.item_key
4317 ,'HR_APPRAISAL_FROM_USER_ATTR',
4318 fnd_global.user_name);
4319 -- HR_APPRAISAL_FROM_NAME_ATTR
4320 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
4321 ,lr_hr_api_transaction_rec.item_key
4322 ,'HR_APPRAISAL_FROM_NAME_ATTR'
4323 ,lv_loginPersonDispName);
4324
4325 -- APPROVAL_COMMENT_COPY
4326 -- ??? module need to handle in the UI layer.
4327
4328 -- set the blockid value
4329 hr_appraisal_workflow_ss.getapprovalblockid(
4330 lr_hr_api_transaction_rec.item_type,
4331 lr_hr_api_transaction_rec.item_key,
4332 ln_activity_id);
4333 else
4334 -- generic approval process initial block id attribute
4335 --HR_APPROVAL_BLOCK_ID_ATTR
4336 ln_activity_id:=wf_engine.getitemattrnumber(lr_hr_api_transaction_rec.item_type,
4337 lr_hr_api_transaction_rec.item_key,
4338 'HR_APPROVAL_BLOCK_ID_ATTR',true);
4339 end if;
4340 end if;
4341 return ln_activity_id;
4342
4343 end getinitApprovalBlockId;
4344
4345 function getApproverNtfId(p_transaction_id in number) return number
4346 is
4347 c_proc constant varchar2(30) := 'getApproverNtfId';
4348 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
4349 ln_notification_id wf_notifications.notification_id%type;
4350 begin
4351 if(p_transaction_id is not null) then
4352 select * into lr_hr_api_transaction_rec
4353 from hr_api_transactions
4354 where transaction_id=p_transaction_id;
4355
4356 select notification_id
4357 into ln_notification_id
4358 FROM WF_ITEM_ACTIVITY_STATUSES IAS
4359 WHERE ias.item_type = lr_hr_api_transaction_rec.item_type
4360 and ias.item_key = lr_hr_api_transaction_rec.item_key
4361 and ias.activity_status = 'NOTIFIED'
4362 and ias.notification_id is not null
4363 and rownum<=1;
4364 end if;
4365
4366 return ln_notification_id;
4367 exception
4368 when no_data_found then
4369 raise;
4370 when others then
4371 raise;
4372 end getApproverNtfId;
4373
4374 procedure processPageNavWFSubmit(p_transaction_id in number,
4375 p_approval_comments in varchar2)
4376 is
4377 -- local variables
4378 c_proc constant varchar2(30) := 'processPageNavWFSubmit';
4379 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
4380 ln_activity_id wf_item_activity_statuses.process_activity%type;
4381 begin
4382 if g_debug then
4383 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4384 end if;
4385
4386 if(p_transaction_id is not null) then
4387
4388 select * into lr_hr_api_transaction_rec
4389 from hr_api_transactions
4390 where transaction_id=p_transaction_id;
4391
4392 hr_transaction_api.update_transaction(
4393 p_transaction_id => p_transaction_id,
4394 p_status => 'Y',
4395 p_transaction_state => null);
4396
4397 -- re-intialize the performer roles
4398 hr_approval_ss.reinitperformerroles(p_notification_id=>null
4399 ,p_transaction_id=>null
4400 ,p_item_type=>lr_hr_api_transaction_rec.item_type
4401 ,p_item_key=>lr_hr_api_transaction_rec.item_key);
4402
4403
4404 -- default logic
4405 -- get the blockid value corresponding to the UI page
4406 ln_activity_id:=
4407 getOAFPageActId(lr_hr_api_transaction_rec.item_type,lr_hr_api_transaction_rec.item_key);
4408 -- check if the ln_activity_id is null
4409 if(ln_activity_id is null) then
4410 null; -- raise error
4411 end if;
4412 -- set the workflow status TRAN_SUBMIT to Y
4413 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
4414 ,lr_hr_api_transaction_rec.item_key
4415 ,'TRAN_SUBMIT'
4416 ,'Y');
4417
4418
4419 --
4420
4421
4422 -- now transition the workflow to process approval notifications
4423 if(lr_hr_api_transaction_rec.status in('YS','RI','RIS','RO','RIS')) then
4424 -- complete the flow in resubmit mode
4425 wf_engine.CompleteActivity(
4426 lr_hr_api_transaction_rec.item_type
4427 , lr_hr_api_transaction_rec.item_key
4428 , wf_engine.getactivitylabel(ln_activity_id)
4429 , 'RESUBMIT') ;
4430
4431
4432 else
4433 -- else intial submit
4434 wf_engine.CompleteActivity(
4435 lr_hr_api_transaction_rec.item_type
4436 , lr_hr_api_transaction_rec.item_key
4437 , wf_engine.getactivitylabel(ln_activity_id)
4438 , wf_engine.eng_trans_default) ;
4439 end if;
4440 end if;
4441
4442 if (g_debug ) then
4443 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
4444 end if;
4445 exception
4446 when others then
4447 raise;
4448 end processPageNavWFSubmit;
4449
4450
4451 procedure update_comments(
4452 p_ntf_id in number,
4453 p_txn_status in varchar2,
4454 p_approval_comments in varchar2)
4455 is
4456 cursor csr_wf_note_exists is
4457 select 1
4458 from wf_notification_attributes wna
4459 where notification_id = p_ntf_id
4460 and NAME = 'WF_NOTE';
4461 l_dummy number(1);
4462 begin
4463 -- The parameter p_txn_status is currently not used but if this procedure requires
4464 -- modifications in the future it might be needed.
4465
4466 -- Check if comments are present
4467 if p_approval_comments is not null then
4468 -- Check if WF_NOTE is a part of the notification
4469 open csr_wf_note_exists;
4470 fetch csr_wf_note_exists into l_dummy;
4471 if csr_wf_note_exists%found then
4472 -- Attribute exists so write into it
4473 wf_notification.setattrtext( p_ntf_id,'WF_NOTE',p_approval_comments);
4474 else
4475 -- Do we need to create the attribute ?
4476 -- Current implementation is a no-op
4477 null;
4478 end if;
4479 close csr_wf_note_exists;
4480 end if;
4481
4482 end update_comments;
4483
4484
4485
4486 procedure processNonPageNavWFSubmit(p_transaction_id in number,
4487 p_approval_comments in varchar2)
4488 is
4489 -- local variables
4490 c_proc constant varchar2(30) := 'processNonPageNavWFSubmit';
4491 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
4492 ln_activity_id wf_item_activity_statuses.process_activity%type;
4493 ln_notification_id wf_notifications.notification_id%type;
4494 lv_loginPersonDispName per_all_people_f.full_name%type;
4495 lv_loginPersonUserName fnd_user.user_name%type;
4496 ln_loginPersonId fnd_user.employee_id%type;
4497
4498 begin
4499 if g_debug then
4500 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4501 end if;
4502
4503 if(p_transaction_id is not null) then
4504
4505 select * into lr_hr_api_transaction_rec
4506 from hr_api_transactions
4507 where transaction_id=p_transaction_id;
4508
4509 hr_transaction_api.update_transaction(
4510 p_transaction_id => p_transaction_id,
4511 p_status => 'Y',
4512 p_transaction_state => null);
4513
4514
4515 -- re-intialize the performer roles
4516 hr_approval_ss.reinitperformerroles(p_notification_id=>null
4517 ,p_transaction_id=>null
4518 ,p_item_type=>lr_hr_api_transaction_rec.item_type
4519 ,p_item_key=>lr_hr_api_transaction_rec.item_key);
4520
4521
4522 -- get the current transaction status
4523 -- If approver is editing and submitting the possible
4524 -- status are 'Y','YS','RI','RIS','RO','RIS'
4525 -- else it should be the initial creator submit
4526
4527 begin
4528 -- get the current transaction status
4529 if(lr_hr_api_transaction_rec.status in ('Y','YS','RI','RIS','RO','RIS')) then
4530 -- approver edit and submit case
4531 ln_notification_id:= getApproverNtfId(p_transaction_id);
4532 -- check if not null value
4533 if(ln_notification_id is not null) then
4534 -- add the code plugin transfer history
4535 -- need to revisit as this is resubmit not submit action
4536 hr_trans_history_api.archive_resubmit(p_transaction_id,
4537 null,
4538 fnd_global.user_name,
4539 p_approval_comments);
4540 -- complete the notification
4541 wf_notification.setattrtext(
4542 ln_notification_id
4543 ,'RESULT'
4544 ,'RESUBMIT');
4545
4546 -- Fix for bug 4998216 starts
4547 update_comments(ln_notification_id,lr_hr_api_transaction_rec.status,p_approval_comments);
4548 -- Fix for bug 4998216 ends
4549
4550 wf_notification.respond(
4551 ln_notification_id
4552 ,p_approval_comments
4553 ,fnd_global.user_name
4554 ,null);
4555 -- Fix for 5070814
4556 if(lr_hr_api_transaction_rec.status in ('RI','RIS','RO','RIS')) then
4557 -- propagate during RFC only
4558 wf_notification.propagatehistory(
4559 lr_hr_api_transaction_rec.item_type
4560 ,lr_hr_api_transaction_rec.item_key
4561 ,'APPROVAL_NOTIFICATION'
4562 ,fnd_global.user_name
4563 ,'WF_SYSTEM'
4564 --,hr_workflow_ss.getNextApproverForHist(itemtype, itemkey)
4565 ,'RESUBMIT'
4566 ,null
4567 ,p_approval_comments);
4568 end if;
4569 else
4570 -- notification is null, raise exception
4571 null;
4572 end if;
4573 -- get the approver notification id
4574 else
4575 -- intiator submit case
4576 -- get the inital block id
4577 ln_activity_id:= getinitApprovalBlockId(p_transaction_id);
4578
4579 -- update the transaction status before transitioning the flow
4580 hr_transaction_api.update_transaction(
4581 p_transaction_id => p_transaction_id,
4582 p_status => 'Y');
4583
4584
4585
4586
4587 -- set the initial submit comments
4588 -- APPROVAL_COMMENT_COPY
4589 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_hr_api_transaction_rec.item_type
4590 ,itemkey => lr_hr_api_transaction_rec.item_key
4591 ,aname => 'APPROVAL_COMMENT_COPY'
4592 ,text_value=>p_approval_comments
4593 ,number_value=>null,
4594 date_value=>null
4595 );
4596
4597 -- else intial submit
4598 wf_engine.CompleteActivity(
4599 lr_hr_api_transaction_rec.item_type
4600 , lr_hr_api_transaction_rec.item_key
4601 , wf_engine.getactivitylabel(ln_activity_id)
4602 , wf_engine.eng_trans_default) ;
4603
4604 end if;
4605 end;
4606
4607 else
4608 null; -- null transaction id throw error ??
4609 end if;
4610
4611 if (g_debug ) then
4612 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
4613 end if;
4614 exception
4615 when others then
4616 raise;
4617 end processNonPageNavWFSubmit;
4618
4619 procedure processApprovalSubmit(p_transaction_id in number,
4620 p_approval_comments in varchar2)
4621 is
4622 -- local variables
4623 c_proc constant varchar2(30) := 'processApprovalSubmit';
4624 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
4625 ln_activity_id wf_item_activity_statuses.process_activity%type;
4626 lv_loginPersonDispName per_all_people_f.full_name%type;
4627 lv_loginPersonUserName fnd_user.user_name%type;
4628 ln_loginPersonId fnd_user.employee_id%type;
4629 lv_item_type wf_items.item_type%type;
4630 lv_item_key wf_items.item_key%type;
4631 lv_oaf_nav_attr wf_item_attribute_values.text_value%type;
4632 begin
4633 if g_debug then
4634 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4635 end if;
4636
4637 -- code logic
4638
4639 if(p_transaction_id is not null) then
4640
4641 begin
4642 select item_type,item_key
4643 into lv_item_type,lv_item_key
4644 from hr_api_transactions
4645 where transaction_id=p_transaction_id;
4646 exception
4647 when no_data_found then
4648 -- get the fnd message and populate to the fnd message pub
4649 if(hr_multi_message.is_message_list_enabled) then
4650 fnd_message.set_name('PER', 'HR_SS_NO_TXN_DATA');
4651 hr_multi_message.add(p_message_type => hr_multi_message.G_ERROR_MSG );
4652 end if;
4653 hr_utility.raise_error;
4654 when others then
4655 raise;
4656 end; -- finished fetching record
4657
4658 -- do the processing logic
4659 begin
4660 -- check if we have valid item type and key
4661 if(lv_item_type is null or lv_item_key is null) then
4662 raise g_wf_not_initialzed;
4663 end if;
4664 -- check if the flow is used for page navigation
4665 -- HR_OAF_NAVIGATION_ATTR
4666 lv_oaf_nav_attr := wf_engine.getitemattrtext(lv_item_type,lv_item_key,'HR_OAF_NAVIGATION_ATTR',true);
4667 if(lv_oaf_nav_attr='Y') then
4668 -- process page navigation based wf approval submit
4669 processPageNavWFSubmit(p_transaction_id,p_approval_comments );
4670 else
4671 -- process wf approval submit with approvals only
4672 processNonPageNavWFSubmit(p_transaction_id,p_approval_comments );
4673 end if;
4674
4675 exception
4676 when g_wf_not_initialzed then
4677 -- get the fnd message and populate to the fnd message pub
4678 if(hr_multi_message.is_message_list_enabled) then
4679 fnd_message.set_name('PER', 'HR_SS_WF_NOT_INITIALZED');
4680 hr_multi_message.add(p_message_type => hr_multi_message.G_ERROR_MSG );
4681 end if;
4682 hr_utility.raise_error;
4683 when others then
4684 if hr_multi_message.unexpected_error_add(c_proc) then
4685 hr_utility.set_location(' Leaving:' || c_proc,40);
4686 raise;
4687 end if;
4688 end;
4689
4690 -- finally close the sfl open notifications if any
4691 hr_sflutil_ss.closeopensflnotification(p_transaction_id);
4692
4693 else
4694 -- get the fnd message and populate to the fnd message pub
4695 if(hr_multi_message.is_message_list_enabled) then
4696 fnd_message.set_name('PER', 'HR_SS_NULL_TXN_ID');
4697 hr_multi_message.add(p_message_type => hr_multi_message.G_ERROR_MSG );
4698 end if;
4699 hr_utility.raise_error;
4700 end if;
4701
4702
4703
4704 if (g_debug ) then
4705 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
4706 end if;
4707
4708 exception
4709 when others then
4710 raise;
4711 end processApprovalSubmit;
4712
4713 procedure resetWfPageFlowState(p_transaction_id in number)
4714 is
4715 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
4716 ln_activity_id wf_item_attribute_values.number_value%type;
4717 ln_oaf_page_act_id WF_ITEM_ACTIVITY_STATUSES.process_activity%type;
4718 lv_oaf_nav_attr wf_item_attribute_values.text_value%type;
4719 begin
4720
4721 if(p_transaction_id is not null) then
4722 begin
4723 select * into lr_hr_api_transaction_rec
4724 from hr_api_transactions
4725 where transaction_id=p_transaction_id;
4726 exception
4727 when others then
4728 null;
4729 end;
4730
4731 -- check the status
4732 if(lr_hr_api_transaction_rec.status<>'W') then
4733 if(lr_hr_api_transaction_rec.item_key is not null) then
4734 -- check if the flow uses wf for page navigation
4735 -- HR_OAF_NAVIGATION_ATTR
4736 lv_oaf_nav_attr := wf_engine.getitemattrtext(lr_hr_api_transaction_rec.item_type,
4737 lr_hr_api_transaction_rec.item_key,
4738 'HR_OAF_NAVIGATION_ATTR',
4739 true);
4740 if(lv_oaf_nav_attr='Y') then
4741 -- for wf page navigation need to reset the wf state
4742 -- S, RIS,ROS,YS reset the state to saved page actid
4743 -- Y,RO,RI reset to first page activity id
4744 if(lr_hr_api_transaction_rec.status in ('S','RIS','ROS','YS')) then
4745 ln_activity_id := wf_engine.getitemattrnumber(lr_hr_api_transaction_rec.item_type,
4746 lr_hr_api_transaction_rec.item_key,
4747 'SAVED_ACTIVITY_ID',
4748 true);
4749 elsif(lr_hr_api_transaction_rec.status in ('RI','RO','Y')) then
4750 ln_activity_id := wf_engine.getitemattrnumber(lr_hr_api_transaction_rec.item_type,
4751 lr_hr_api_transaction_rec.item_key,
4752 'HR_FIRST_ACTIVITY_ID',
4753 true);
4754
4755 end if;
4756
4757 -- finally call wf engine handle to reset the state
4758 -- need to do only if the current activity id is not
4759 -- same as the ln_activity_id
4760 ln_oaf_page_act_id := hr_approval_ss.getoafpageactid(
4761 lr_hr_api_transaction_rec.item_type,
4762 lr_hr_api_transaction_rec.item_key);
4763 if(ln_activity_id is not null and ln_activity_id<>ln_oaf_page_act_id ) then
4764 WF_ENGINE.handleError(
4765 itemType => lr_hr_api_transaction_rec.item_type
4766 ,itemKey => lr_hr_api_transaction_rec.item_type
4767 ,activity => WF_ENGINE.GetActivityLabel(ln_activity_id)
4768 ,command => 'RETRY' ) ;
4769 end if;
4770 end if;
4771 end if;
4772 end if;
4773 end if;
4774
4775
4776 exception
4777 when others then
4778 null;
4779 end resetWfPageFlowState;
4780
4781
4782 procedure checktransactionState(p_transaction_id IN NUMBER)
4783
4784 is
4785 -- local variables
4786 c_proc constant varchar2(40) := 'checktransactionState';
4787 lv_status hr_api_transactions.status%type;
4788 lv_state hr_api_transactions.transaction_state%type;
4789 begin
4790 if g_debug then
4791 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4792 end if;
4793
4794
4795 begin
4796 select status,transaction_state
4797 into lv_status,lv_state
4798 from hr_api_transactions
4799 where transaction_id=p_transaction_id;
4800
4801 -- check the transaction status
4802 if(lv_status in ('YS','RIS','ROS')) then
4803 -- saved transaction exists set the warning message
4804
4805 if(hr_multi_message.is_message_list_enabled) then
4806 hr_utility.set_message(800,'HR_SS_SAVED_TXN_DATA_EXISTS');
4807 hr_multi_message.add( p_message_type => hr_multi_message.G_WARNING_MSG);
4808 hr_utility.set_warning;
4809 end if;
4810 end if;
4811 if(lv_state is not null) then
4812 -- in advertant saved transaction exists set the warning message
4813
4814 if(hr_multi_message.is_message_list_enabled) then
4815 hr_utility.set_message(800,'HR_SS_INADV_TXN_DATA_EXISTS');
4816 hr_multi_message.add( p_message_type => hr_multi_message.G_WARNING_MSG);
4817 end if;
4818
4819 end if;
4820
4821 exception
4822 when others then
4823 null; -- do nothing
4824 end ;
4825
4826 if (g_debug ) then
4827 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
4828 end if;
4829 exception
4830 when others then
4831 raise;
4832 end checktransactionState;
4833
4834 procedure handleApprovalErrors(p_item_type in wf_items.item_type%type,
4835 p_item_key in wf_items.item_key%type,
4836 error_message_text in varchar2)
4837 is
4838
4839 l_creator_disp_name wf_users.display_name%type;
4840 l_aprv_routing_username varchar2(60);
4841 lv_process_display_name wf_runnable_processes_v.display_name%type;
4842 c_application_id integer;
4843 c_transaction_id varchar2(25);
4844 c_transaction_type varchar2(25);
4845 l_forward_from_display_name wf_users.display_name%type;
4846 l_forward_to_display_name wf_users.display_name%type;
4847 c_approver_to_notify_rec ame_util.approverRecord2;
4848 lv_ntf_sub_msg wf_item_attribute_values.text_value%type;
4849
4850
4851 begin
4852
4853 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
4854 itemkey => p_item_key,
4855 aname => 'HR_AME_APP_ID_ATTR');
4856 c_application_id := nvl(c_application_id,800);
4857 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
4858 itemkey => p_item_key,
4859 aname => 'TRANSACTION_ID');
4860 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
4861 itemkey => p_item_key,
4862 aname => 'HR_AME_TRAN_TYPE_ATTR');
4863
4864 ame_api2.getadminapprover(applicationidin => c_application_id
4865 ,transactiontypein => c_transaction_type
4866 ,adminapproverout => c_approver_to_notify_rec);
4867
4868 l_aprv_routing_username := c_approver_to_notify_rec.name;
4869
4870 if(l_aprv_routing_username is null) then
4871 l_aprv_routing_username := wf_engine.GetItemAttrText(itemtype => p_item_type ,
4872 itemkey => p_item_key,
4873 aname => 'CREATOR_PERSON_USERNAME');
4874 end if;
4875 wf_engine.setitemattrtext(p_item_type,p_item_key,'APPROVAL_ROUTING_USERNAME1',l_aprv_routing_username);
4876
4877 l_forward_from_display_name := wf_engine.GetItemAttrText(itemtype => p_item_type ,
4878 itemkey => p_item_key,
4879 aname => 'FORWARD_FROM_DISPLAY_NAME');
4880 if(l_forward_from_display_name is null) then
4881 l_forward_from_display_name := wf_engine.GetItemAttrText(itemtype => p_item_type ,
4882 itemkey => p_item_key,
4883 aname => 'CREATOR_PERSON_DISPLAY_NAME');
4884 end if;
4885 wf_engine.setitemattrtext(p_item_type,p_item_key,'FORWARD_FROM_DISPLAY_NAME',l_forward_from_display_name);
4886
4887 l_forward_to_display_name := wf_engine.GetItemAttrText(itemtype => p_item_type ,
4888 itemkey => p_item_key,
4889 aname => 'FORWARD_TO_DISPLAY_NAME');
4890 if(l_forward_to_display_name is null) then
4891 l_forward_to_display_name := wf_engine.GetItemAttrText(itemtype => p_item_type ,
4892 itemkey => p_item_key,
4893 aname => 'CREATOR_PERSON_DISPLAY_NAME');
4894 end if;
4895 wf_engine.setitemattrtext(p_item_type,p_item_key,'FORWARD_TO_DISPLAY_NAME',l_forward_to_display_name);
4896
4897
4898 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_MESSAGE_TEXT',error_message_text);
4899 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_ITEM_TYPE',p_item_type);
4900 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_ITEM_KEY',p_item_key);
4901
4902
4903 lv_ntf_sub_msg := wf_engine.GetItemAttrText(itemtype => p_item_type ,
4904 itemkey => p_item_key,
4905 aname => 'HR_NTF_SUB_FND_MSG_ATTR',
4906 ignore_notfound=>true);
4907
4908 if(lv_ntf_sub_msg is null) then
4909 lv_process_display_name := hr_workflow_ss.getProcessDisplayName(p_item_type,p_item_key);
4910 else
4911 fnd_message.set_name('PER',lv_ntf_sub_msg);
4912 lv_process_display_name:= fnd_message.get;
4913 end if;
4914
4915 exception
4916 when others then
4917 if g_debug then
4918 hr_utility.set_location('Error in handleApprovalErrors SQLERRM' ||' '||to_char(SQLCODE),20);
4919 end if;
4920 raise;
4921 end handleApprovalErrors;
4922
4923
4924
4925 END HR_APPROVAL_SS;