[Home] [Help]
PACKAGE BODY: APPS.HR_APPROVAL_SS
Source
1 PACKAGE BODY HR_APPROVAL_SS AS
2 /* $Header: hraprvlss.pkb 120.50.12020000.5 2013/01/30 17:34:10 aammoham 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 /* Wf_engine.SetItemAttrText
500 (itemtype => p_item_type
501 ,itemkey => p_item_key
502 ,aname => 'FORWARD_TO_USER'
503 ,avalue => getToString(p_item_key) || wf_engine.getItemAttrText
504 (itemtype => p_item_type
505 ,itemkey => p_item_key
506 ,aname => 'HR_APR_DISPLAY_NAME_ATTR'));*/
507
508
509 create_item_attrib_if_notexist(itemtype => p_item_type
510 ,itemkey => p_item_key
511 ,aname => 'FORWARD_TO_USER'
512 ,text_value=>getToString(p_item_key) || wf_engine.getItemAttrText
513 (itemtype => p_item_type
514 ,itemkey => p_item_key
515 ,aname => 'HR_APR_DISPLAY_NAME_ATTR')
516 ,number_value=>null,
517 date_value=>null
518 );
519 --
520 -- set forward from to old forward to
521 --
522 wf_engine.SetItemAttrNumber
523 (itemtype => p_item_type
524 ,itemkey => p_item_key
525 ,aname => 'FORWARD_FROM_PERSON_ID'
526 ,avalue => l_current_forward_to_id);
527
528 -- FORWARD_FROM_ORIG_SYS_ATTR
529 wf_engine.SetItemAttrText
530 (itemtype => p_item_type
531 ,itemkey => p_item_key
532 ,aname => 'FORWARD_FROM_ORIG_SYS_ATTR'
533 ,avalue => l_current_forward_to_origSys);
534
535
536 --
537 -- Get the username and display name for forward from person
538 -- and save to item attributes
539 --
540 if g_debug then
541 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);
542 end if;
543 wf_directory.GetRoleName--GetUserName
544 (p_orig_system => l_current_forward_to_origSys
545 ,p_orig_system_id => l_current_forward_to_id
546 ,p_name => l_current_forward_to_username
547 ,p_display_name => l_current_forward_to_disp_name);
548 --
549 wf_engine.SetItemAttrText
550 (itemtype => p_item_type
551 ,itemkey => p_item_key
552 ,aname => 'FORWARD_FROM_USERNAME'
553 ,avalue => l_current_forward_to_username);
554 --
555 wf_engine.SetItemAttrText
556 (itemtype => p_item_type
557 ,itemkey => p_item_key
558 ,aname => 'FORWARD_FROM_DISPLAY_NAME'
559 ,avalue => l_current_forward_to_disp_name);
560
561 -- store AME specific approver details
562
563 -- FORWARD_TO_ITEM_CLASS_ATTR
564 create_item_attrib_if_notexist(itemtype => p_item_type
565 ,itemkey => p_item_key
566 ,aname => 'FORWARD_TO_ITEM_CLASS_ATTR'
567 ,text_value=>wf_engine.getItemAttrText
568 (itemtype => p_item_type
569 ,itemkey => p_item_key
570 ,aname => 'HR_APR_ITEM_CLASS_ATTR')
571 ,number_value=>null,
572 date_value=>null
573 );
574
575
576 -- FORWARD_TO_ITEM_ID_ATTR
577 create_item_attrib_if_notexist(itemtype => p_item_type
578 ,itemkey => p_item_key
579 ,aname => 'FORWARD_TO_ITEM_ID_ATTR'
580 ,text_value=>wf_engine.getItemAttrText
581 (itemtype => p_item_type
582 ,itemkey => p_item_key
583 ,aname => 'HR_APR_ITEM_ID_ATTR')
584 ,number_value=>null,
585 date_value=>null
586 );
587
588 -- FORWARD_TO_GROUPORCHAINID
589
590 create_item_attrib_if_notexist(itemtype => p_item_type
591 ,itemkey => p_item_key
592 ,aname => 'FORWARD_TO_GROUPORCHAINID'
593 ,text_value=>null
594 ,number_value=>wf_engine.getItemAttrNumber
595 (itemtype => p_item_type
596 ,itemkey => p_item_key
597 ,aname => 'HR_APR_GRPORCHN_ID_ATTR'),
598 date_value=>null
599 );
600
601
602 -- FORWARD_TO_ACTIONTYPEID_ATTR
603 create_item_attrib_if_notexist(itemtype => p_item_type
604 ,itemkey => p_item_key
605 ,aname => 'FORWARD_TO_ACTIONTYPEID_ATTR'
606 ,text_value=>wf_engine.getItemAttrText
607 (itemtype => p_item_type
608 ,itemkey => p_item_key
609 ,aname => 'HR_APR_ACTION_TYPE_ID_ATTR')
610 ,number_value=>null,
611 date_value=>null
612 );
613
614 if (g_debug ) then
615 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
616 end if;
617 exception
618 when others then
619 raise;
620 end storeApproverDetails;
621
622 procedure storePrllApprDetails(p_item_type in varchar2,
623 p_item_key in varchar2)
624 is
625 -- local variables
626 c_proc constant varchar2(30) := 'storePrllApprDetails';
627 l_current_forward_to_username wf_users.name%type;
628 l_current_forward_to_disp_name wf_users.display_name%type;
629 l_role_name varchar2(1000) := null;
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 -- set the passed approver values to the forward to item attributes
638
639 l_current_forward_to_username := wf_engine.getItemAttrText
640 (itemtype => p_item_type
641 ,itemkey => p_item_key
642 ,aname => 'FORWARD_FROM_ROLE_NAME'
643 ,ignore_notfound=>true);
644
645 hr_utility.set_location('storeprll l_current_forward_to_username: '|| l_current_forward_to_username, 1);
646
647 if( l_current_forward_to_username is null) then
648
649 hr_utility.set_location('storeprll l_current_forward_to_username is null: '|| l_current_forward_to_username, 2);
650
651 l_current_forward_to_username := fnd_global.user_name;
652
653 hr_utility.set_location('storeprll l_current_forward_to_username: '|| l_current_forward_to_username, 3);
654
655
656 end if;
657
658 select display_name into l_current_forward_to_disp_name from wf_roles where name = l_current_forward_to_username;
659 if(l_current_forward_to_disp_name is null) then
660 l_current_forward_to_disp_name := fnd_global.user_name;
661 end if;
662
663
664
665 wf_engine.SetItemAttrText
666 (itemtype => p_item_type
667 ,itemkey => p_item_key
668 ,aname => 'FORWARD_FROM_USERNAME'
669 ,avalue => l_current_forward_to_username);
670 --
671 wf_engine.SetItemAttrText
672 (itemtype => p_item_type
673 ,itemkey => p_item_key
674 ,aname => 'FORWARD_FROM_DISPLAY_NAME'
675 ,avalue => l_current_forward_to_disp_name);
676
677 wf_engine.SetItemAttrText
678 (itemtype => p_item_type
679 ,itemkey => p_item_key
680 ,aname => 'FORWARD_TO_USERNAME'
681 ,avalue => wf_engine.getItemAttrText
682 (itemtype => p_item_type
683 ,itemkey => p_item_key
684 ,aname => 'HR_APR_NAME_ATTR'));
685
686 Wf_engine.SetItemAttrText
687 (itemtype => p_item_type
688 ,itemkey => p_item_key
689 ,aname => 'FORWARD_TO_DISPLAY_NAME'
690 ,avalue => wf_engine.getItemAttrText
691 (itemtype => p_item_type
692 ,itemkey => p_item_key
693 ,aname => 'HR_APR_DISPLAY_NAME_ATTR'));
694
695 wf_engine.SetItemAttrText
696 (itemtype => p_item_type
697 ,itemkey => p_item_key
698 ,aname => 'FORWARD_TO_USER'
699 ,avalue => null);
700
701
702 if (g_debug ) then
703 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
704 end if;
705 exception
706 when others then
707 raise;
708 end storePrllApprDetails;
709
710 procedure getNextCustomApprover(p_item_type in varchar2,
711 p_item_key in varchar2,
712 p_approvalprocesscompleteynout out nocopy varchar2,
713 p_approver_rec out nocopy ame_util.approverRecord2)
714 is
715 -- local variables
716 c_proc constant varchar2(30) := 'getNextCustomApprover';
717 l_creator_person_id per_people_f.person_id%type;
718 l_forward_to_person_id per_people_f.person_id%type;
719 l_current_forward_to_id per_people_f.person_id%type;
720 l_current_forward_from_id per_people_f.person_id%type;
721 ln_last_default_approver_id per_people_f.person_id%type;
722 ln_addntl_approvers NUMBER DEFAULT 0;
723 ln_approval_level NUMBER DEFAULT 0;
724 ln_curr_def_appr_index NUMBER DEFAULT 1;
725 ln_current_approver_index NUMBER ;
726 ln_last_def_approver NUMBER;
727 l_dummy VARCHAR2(100);
728 lv_dummy VARCHAR2(20);
729 lv_exists VARCHAR2(10);
730 lv_isvalid VARCHAR2(10);
731 lv_response VARCHAR2(10);
732 lv_item_name VARCHAR2(100);
733
734
735 cv_item_name constant VARCHAR2(20) := 'ADDITIONAL_APPROVER_';
736 cv_notifier_name constant VARCHAR2(9) := 'NOTIFIER_';
737
738
739
740 begin
741 g_debug := hr_utility.debug_enabled;
742 if g_debug then
743 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
744 end if;
745
746 --
747 l_creator_person_id := wf_engine.GetItemAttrNumber
748 (itemtype => p_item_type
749 ,itemkey => p_item_key
750 ,aname => 'CREATOR_PERSON_ID');
751 --
752 l_forward_to_person_id := wf_engine.GetItemAttrNumber
753 (itemtype => p_item_type
754 ,itemkey => p_item_key
755 ,aname =>'FORWARD_TO_PERSON_ID');
756
757 -- get the current forward to person
758 l_current_forward_to_id :=
759 nvl(wf_engine.GetItemAttrNumber
760 (itemtype => p_item_type
761 ,itemkey => p_item_key
762 ,aname => 'FORWARD_TO_PERSON_ID'),
763 wf_engine.GetItemAttrNumber
764 (itemtype => p_item_type
765 ,itemkey => p_item_key
766 ,aname => 'CREATOR_PERSON_ID'));
767
768
769 l_forward_to_person_id := NVL(l_forward_to_person_id,l_current_forward_to_id);
770
771 -- attribute to hold the last_default approver from the heirarchy tree.
772 OPEN csr_wiav(p_item_type,p_item_key,'LAST_DEFAULT_APPROVER');
773 FETCH csr_wiav into l_dummy;
774 IF csr_wiav%notfound THEN
775 -- create new wf_item_attribute_value to hold
776 hr_approval_wf.create_item_attrib_if_notexist
777 (p_item_type => p_item_type
778 ,p_item_key => p_item_key
779 ,p_name => 'LAST_DEFAULT_APPROVER');
780
781 wf_engine.SetItemAttrNumber
782 (itemtype => p_item_type,
783 itemkey => p_item_key,
784 aname => 'LAST_DEFAULT_APPROVER',
785 avalue => NULL);
786
787 END IF;
788 CLOSE csr_wiav;
789
790
791 -- 'LAST_DEFAULT_APPROVER'
792 ln_last_def_approver:= wf_engine.GetItemAttrNumber
793 (itemtype => p_item_type,
794 itemkey => p_item_key,
795 aname => 'LAST_DEFAULT_APPROVER'
796 );
797
798 ln_last_def_approver:= NVL(ln_last_def_approver,l_forward_to_person_id);
799
800 -- check if we have default approvers
801 lv_response := hr_approval_custom.Check_Final_approver
802 (p_forward_to_person_id => ln_last_def_approver
803 ,p_person_id => l_creator_person_id );
804
805 IF lv_response <>'N' THEN
806 p_approvalprocesscompleteynout := ame_util.booleanTrue;
807 return;
808 else
809 p_approvalprocesscompleteynout := ame_util.booleanFalse;
810 END IF;
811
812 -- check if we have reached the max limit on the approvers level
813 -- the level is based on the heirarchy tree.
814 -- get the approval level as conifgured by the HR Rep or Sys Admin
815 OPEN csr_wiav(p_item_type
816 ,p_item_key
817 ,'APPROVAL_LEVEL');
818
819 FETCH csr_wiav into l_dummy;
820 IF csr_wiav%notfound THEN
821 ln_approval_level := 0;
822 ELSE
823 ln_approval_level := wf_engine.GetItemAttrNumber
824 (itemtype => p_item_type,
825 itemkey => p_item_key,
826 aname => 'APPROVAL_LEVEL');
827 END IF; -- for csr_wiav%notfound
828 CLOSE csr_wiav;
829
830 IF ln_approval_level > 0 THEN
831 -- get the current approval level reached
832 -- first check if the attribute exists
833 OPEN csr_wiav(p_item_type
834 ,p_item_key
835 ,'CURRENT_DEF_APPR_INDEX');
836
837 FETCH csr_wiav into l_dummy;
838 IF csr_wiav%notfound THEN
839 NULL;
840 ELSE
841 ln_curr_def_appr_index := wf_engine.GetItemAttrNumber
842 (itemtype => p_item_type,
843 itemkey => p_item_key,
844 aname => 'CURRENT_DEF_APPR_INDEX');
845 END IF;-- for csr_wiav%notfound
846 CLOSE csr_wiav;
847
848 END IF; -- for ln_num_of_add_apprs > 0
849
850
851 -- Fix for the Bug # 1255826
852 IF (ln_approval_level> 0)
853
854 THEN
855 IF( ln_curr_def_appr_index < ln_approval_level)
856 THEN
857
858 p_approvalprocesscompleteynout := ame_util.booleanFalse;
859 ELSE
860 p_approvalprocesscompleteynout := ame_util.booleanTrue;
861 END IF;
862 ELSE
863 lv_response := hr_approval_custom.Check_Final_approver
864 (p_forward_to_person_id => ln_last_def_approver
865 ,p_person_id => l_creator_person_id );
866 if lv_response <>'N' then
867 p_approvalprocesscompleteynout := ame_util.booleanTrue ;
868 else
869 p_approvalprocesscompleteynout := ame_util.booleanFalse;
870 end if;
871 END IF;
872
873 -- get the next approver details
874 if(p_approvalprocesscompleteynout = ame_util.booleanFalse) then
875
876 l_current_forward_from_id:=null;
877 l_current_forward_to_id:=null;
878 ln_addntl_approvers:= null;
879 ln_current_approver_index:=null;
880 -- get the current forward from person
881 l_current_forward_from_id :=
882 nvl(wf_engine.GetItemAttrNumber
883 (itemtype => p_item_type
884 ,itemkey => p_item_key
885 ,aname => 'FORWARD_FROM_PERSON_ID'),
886 wf_engine.GetItemAttrNumber
887 (itemtype => p_item_type
888 ,itemkey => p_item_key
889 ,aname => 'CREATOR_PERSON_ID'));
890 -- get the current forward to person
891 l_current_forward_to_id :=
892 nvl(wf_engine.GetItemAttrNumber
893 (itemtype => p_item_type
894 ,itemkey => p_item_key
895 ,aname => 'FORWARD_TO_PERSON_ID'),
896 wf_engine.GetItemAttrNumber
897 (itemtype => p_item_type
898 ,itemkey => p_item_key
899 ,aname => 'CREATOR_PERSON_ID'));
900
901
902 -- get the total number of additional approvers for this transaction
903 ln_addntl_approvers := NVL(wf_engine.GetItemAttrNumber
904 (itemtype => p_item_type
905 ,itemkey => p_item_key
906 ,aname => 'ADDITIONAL_APPROVERS_NUMBER'),
907 0);
908
909 -- fix for the bug # 1252070
910
911 -- attribute to hold the last_default approver from the heirarchy tree.
912 OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_APPROVER_INDEX');
913 FETCH csr_wiav into lv_dummy;
914 IF csr_wiav%notfound THEN
915 -- create new wf_item_attribute_value to hold
916 hr_approval_wf.create_item_attrib_if_notexist
917 (p_item_type => p_item_type
918 ,p_item_key => p_item_key
919 ,p_name => 'CURRENT_APPROVER_INDEX');
920
921 wf_engine.SetItemAttrNumber
922 (itemtype => p_item_type,
923 itemkey => p_item_key,
924 aname => 'CURRENT_APPROVER_INDEX',
925 avalue => NULL);
926
927 END IF;
928 CLOSE csr_wiav;
929
930
931
932
933
934 -- get the current_approver_index
935 ln_current_approver_index := NVL(wf_engine.GetItemAttrNumber
936 (itemtype => p_item_type
937 ,itemkey => p_item_key
938 ,aname => 'CURRENT_APPROVER_INDEX'),
939 0);
940 -- set the item name
941 lv_item_name := cv_item_name || to_char(ln_current_approver_index + 1);
942
943 -- check if we have additional approver for the next index.
944 -- Fix for the bug # 1255826
945 IF ln_current_approver_index <= ln_addntl_approvers
946 THEN
947 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
948 FETCH csr_wiav into lv_dummy;
949 IF csr_wiav%notfound THEN
950 lv_exists := 'N';
951 ELSE
952 lv_exists := 'Y';
953 lv_isvalid := wf_engine.GetItemAttrText
954 (itemtype => p_item_type,
955 itemkey => p_item_key,
956 aname => lv_item_name);
957 lv_isvalid := NVL(lv_isvalid,' ');
958
959 END IF;
960 CLOSE csr_wiav;
961 ELSE
962 lv_exists := 'N';
963 END IF;
964
965
966 IF lv_exists <>'N' AND lv_isvalid <>'DELETED' THEN
967 l_forward_to_person_id :=
968 wf_engine.GetItemAttrNumber
969 (itemtype => p_item_type,
970 itemkey => p_item_key,
971 aname => lv_item_name
972 );
973
974 ELSE
975 -- get the last default approver index
976
977 ln_last_default_approver_id := wf_engine.GetItemAttrNumber
978 (itemtype => p_item_type,
979 itemkey => p_item_key,
980 aname => 'LAST_DEFAULT_APPROVER');
981
982
983
984 -- get the next approver from the heirarchy tree.
985 -- fix for bug #2087458
986 -- the l_current_forward_to_id resetting was removed for default approver.
987 -- now the from column will show the last approver approved.
988 l_forward_to_person_id :=
989 hr_approval_custom.Get_Next_Approver
990 (p_person_id => NVL(ln_last_default_approver_id,
991 wf_engine.GetItemAttrNumber
992 (itemtype => p_item_type
993 ,itemkey => p_item_key
994 ,aname => 'CREATOR_PERSON_ID')));
995 -- set the last default approver id
996 -- 'LAST_DEFAULT_APPROVER'
997 wf_engine.SetItemAttrNumber
998 (itemtype => p_item_type,
999 itemkey => p_item_key,
1000 aname => 'LAST_DEFAULT_APPROVER',
1001 avalue => l_forward_to_person_id);
1002 -- set cuurent approval levels reached
1003 OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_DEF_APPR_INDEX');
1004 FETCH csr_wiav into lv_dummy;
1005 IF csr_wiav%notfound THEN
1006 -- create new wf_item_attribute_value to hold
1007 hr_approval_wf.create_item_attrib_if_notexist
1008 (p_item_type => p_item_type
1009 ,p_item_key => p_item_key
1010 ,p_name => 'CURRENT_DEF_APPR_INDEX');
1011
1012 wf_engine.SetItemAttrNumber
1013 (itemtype => p_item_type,
1014 itemkey => p_item_key,
1015 aname => 'CURRENT_DEF_APPR_INDEX',
1016 avalue => 0);
1017 ELSE
1018 ln_curr_def_appr_index :=
1019 wf_engine.GetItemAttrNumber
1020 (itemtype => p_item_type,
1021 itemkey => p_item_key,
1022 aname => 'CURRENT_DEF_APPR_INDEX'
1023 );
1024 -- increment it and update the item attribute value
1025 ln_curr_def_appr_index := ln_curr_def_appr_index + 1;
1026 wf_engine.SetItemAttrNumber
1027 (itemtype => p_item_type,
1028 itemkey => p_item_key,
1029 aname => 'CURRENT_DEF_APPR_INDEX',
1030 avalue => ln_curr_def_appr_index);
1031 END IF;
1032 CLOSE csr_wiav;
1033
1034 END IF;
1035
1036 -- set the current_approver_index
1037 wf_engine.SetItemAttrNumber (itemtype => p_item_type
1038 ,itemkey => p_item_key
1039 ,aname => 'CURRENT_APPROVER_INDEX'
1040 ,avalue => (ln_current_approver_index + 1));
1041
1042
1043 -- derive wf role details for the selected person id
1044 if ( l_forward_to_person_id is not null ) then
1045 wf_directory.GetRoleName--GetUserName
1046 (p_orig_system => 'PER'
1047 ,p_orig_system_id => l_forward_to_person_id
1048 ,p_name => p_approver_rec.name
1049 ,p_display_name => p_approver_rec.display_name);
1050
1051 p_approver_rec.orig_system:='PER';
1052 p_approver_rec.orig_system_id:=l_forward_to_person_id;
1053 p_approver_rec.approver_category:=ame_util.approvalApproverCategory;
1054 end if;
1055
1056
1057 end if;
1058
1059 if (g_debug ) then
1060 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1061 end if;
1062 exception
1063 when others then
1064 raise;
1065 end getNextCustomApprover;
1066
1067 procedure populateApproverDetails(p_item_type in varchar2,
1068 p_item_key in varchar2,
1069 p_approverRec in ame_util.approverRecord2)
1070 is
1071 -- local variables
1072 c_proc constant varchar2(60) := 'populateApproverDetails';
1073 begin
1074 g_debug := hr_utility.debug_enabled;
1075
1076 if g_debug then
1077 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1078 end if;
1079 -- create and/or populate approver details to item attributes
1080 -- same structer as ame_util.approverRecord2
1081 -- HR_APR_NAME_ATTR
1082
1083 create_item_attrib_if_notexist(itemtype=>p_item_type,
1084 itemkey=>p_item_key,
1085 aname=>'HR_APR_NAME_ATTR',
1086 text_value=>p_approverRec.name,
1087 number_value=>null,
1088 date_value=>null);
1089
1090 -- HR_APR_ORIG_SYSTEM_ATTR
1091
1092 create_item_attrib_if_notexist(itemtype=>p_item_type,
1093 itemkey=>p_item_key,
1094 aname=>'HR_APR_ORIG_SYSTEM_ATTR',
1095 text_value=>p_approverRec.orig_system,
1096 number_value=>null,
1097 date_value=>null);
1098
1099 -- HR_APR_ORIG_SYSTEM_ID_ATTR
1100
1101 create_item_attrib_if_notexist(itemtype=>p_item_type,
1102 itemkey=>p_item_key,
1103 aname=>'HR_APR_ORIG_SYSTEM_ID_ATTR',
1104 text_value=>null,
1105 number_value=>p_approverRec.orig_system_id,
1106 date_value=>null);
1107
1108 -- HR_APR_DISPLAY_NAME_ATTR
1109
1110 create_item_attrib_if_notexist(itemtype=>p_item_type,
1111 itemkey=>p_item_key,
1112 aname=>'HR_APR_DISPLAY_NAME_ATTR',
1113 text_value=>p_approverRec.display_name,
1114 number_value=>null,
1115 date_value=>null);
1116
1117 -- HR_APR_CATEGORY_ATTR
1118
1119 create_item_attrib_if_notexist(itemtype=>p_item_type,
1120 itemkey=>p_item_key,
1121 aname=>'HR_APR_CATEGORY_ATTR',
1122 text_value=>p_approverRec.approver_category,
1123 number_value=>null,
1124 date_value=>null);
1125
1126 -- HR_APR_API_INSERTION_ATTR
1127
1128 create_item_attrib_if_notexist(itemtype=>p_item_type,
1129 itemkey=>p_item_key,
1130 aname=>'HR_APR_API_INSERTION_ATTR',
1131 text_value=>p_approverRec.api_insertion,
1132 number_value=>null,
1133 date_value=>null);
1134
1135 -- HR_APR_AUTHORITY_ATTR
1136
1137 create_item_attrib_if_notexist(itemtype=>p_item_type,
1138 itemkey=>p_item_key,
1139 aname=>'HR_APR_AUTHORITY_ATTR',
1140 text_value=>p_approverRec.authority,
1141 number_value=>null,
1142 date_value=>null);
1143
1144 -- HR_APR_APPROVAL_STATUS_ATTR
1145
1146 create_item_attrib_if_notexist(itemtype=>p_item_type,
1147 itemkey=>p_item_key,
1148 aname=>'HR_APR_APPROVAL_STATUS_ATTR',
1149 text_value=>p_approverRec.approval_status,
1150 number_value=>null,
1151 date_value=>null);
1152
1153 -- HR_APR_ACTION_TYPE_ID_ATTR
1154
1155 create_item_attrib_if_notexist(itemtype=>p_item_type,
1156 itemkey=>p_item_key,
1157 aname=>'HR_APR_ACTION_TYPE_ID_ATTR',
1158 text_value=>null,
1159 number_value=>p_approverRec.action_type_id,
1160 date_value=>null);
1161
1162 -- HR_APR_GRPORCHN_ID_ATTR
1163
1164 create_item_attrib_if_notexist(itemtype=>p_item_type,
1165 itemkey=>p_item_key,
1166 aname=>'HR_APR_GRPORCHN_ID_ATTR',
1167 text_value=>null,
1168 number_value=>p_approverRec.group_or_chain_id,
1169 date_value=>null);
1170
1171 -- HR_APR_OCCURRENCE_ATTR
1172
1173 create_item_attrib_if_notexist(itemtype=>p_item_type,
1174 itemkey=>p_item_key,
1175 aname=>'HR_APR_OCCURRENCE_ATTR',
1176 text_value=>null,
1177 number_value=>p_approverRec.occurrence,
1178 date_value=>null);
1179
1180 -- HR_APR_SOURCE_ATTR
1181
1182 create_item_attrib_if_notexist(itemtype=>p_item_type,
1183 itemkey=>p_item_key,
1184 aname=>'HR_APR_SOURCE_ATTR',
1185 text_value=>p_approverRec.source,
1186 number_value=>null,
1187 date_value=>null);
1188
1189 -- HR_APR_ITEM_CLASS_ATTR
1190
1191 create_item_attrib_if_notexist(itemtype=>p_item_type,
1192 itemkey=>p_item_key,
1193 aname=>'HR_APR_ITEM_CLASS_ATTR',
1194 text_value=>p_approverRec.item_class,
1195 number_value=>null,
1196 date_value=>null);
1197
1198 -- HR_APR_ITEM_ID_ATTR
1199
1200 create_item_attrib_if_notexist(itemtype=>p_item_type,
1201 itemkey=>p_item_key,
1202 aname=>'HR_APR_ITEM_ID_ATTR',
1203 text_value=>p_approverRec.item_id,
1204 number_value=>null,
1205 date_value=>null);
1206
1207 -- HR_APR_ITM_CLS_ORD_NUM_ATTR
1208
1209 create_item_attrib_if_notexist(itemtype=>p_item_type,
1210 itemkey=>p_item_key,
1211 aname=>'HR_APR_ITM_CLS_ORD_NUM_ATTR',
1212 text_value=>null,
1213 number_value=>p_approverRec.item_class_order_number,
1214 date_value=>null);
1215
1216 -- HR_APR_ITM_ORD_NUM_ATTR
1217
1218 create_item_attrib_if_notexist(itemtype=>p_item_type,
1219 itemkey=>p_item_key,
1220 aname=>'HR_APR_ITM_ORD_NUM_ATTR',
1221 text_value=>null,
1222 number_value=>p_approverRec.item_order_number,
1223 date_value=>null);
1224
1225 -- HR_APR_SUB_LST_ORD_NUM_ATTR
1226
1227 create_item_attrib_if_notexist(itemtype=>p_item_type,
1228 itemkey=>p_item_key,
1229 aname=>'HR_APR_SUB_LST_ORD_NUM_ATTR',
1230 text_value=>null,
1231 number_value=>p_approverRec.sub_list_order_number,
1232 date_value=>null);
1233
1234 -- HR_APR_ACT_TYP_ORD_NUM_ATTR
1235
1236 create_item_attrib_if_notexist(itemtype=>p_item_type,
1237 itemkey=>p_item_key,
1238 aname=>'HR_APR_ACT_TYP_ORD_NUM_ATTR',
1239 text_value=>null,
1240 number_value=>p_approverRec.action_type_order_number,
1241 date_value=>null);
1242
1243 -- HR_APR_GRPORCHN_ORD_NUM_ATTR
1244
1245 create_item_attrib_if_notexist(itemtype=>p_item_type,
1246 itemkey=>p_item_key,
1247 aname=>'HR_APR_GRPORCHN_ORD_NUM_ATTR',
1248 text_value=>null,
1249 number_value=>p_approverRec.group_or_chain_order_number,
1250 date_value=>null);
1251
1252 -- HR_APR_MEMBER_ORD_NUM_ATTR
1253
1254 create_item_attrib_if_notexist(itemtype=>p_item_type,
1255 itemkey=>p_item_key,
1256 aname=>'HR_APR_MEMBER_ORD_NUM_ATTR',
1257 text_value=>null,
1258 number_value=>p_approverRec.member_order_number,
1259 date_value=>null);
1260
1261 -- HR_APR_ORD_NUM_ATTR
1262
1263 create_item_attrib_if_notexist(itemtype=>p_item_type,
1264 itemkey=>p_item_key,
1265 aname=>'HR_APR_ORD_NUM_ATTR',
1266 text_value=>null,
1267 number_value=>p_approverRec.approver_order_number,
1268 date_value=>null);
1269
1270
1271 if (g_debug ) then
1272 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1273 end if;
1274
1275 exception
1276 when others then
1277 raise;
1278 end populateApproverDetails;
1279
1280 procedure populatePrllApprDet(p_item_type in varchar2,
1281 p_item_key in varchar2,
1282 p_approverRec in ame_util.approverRecord2)
1283 is
1284 -- local variables
1285 c_proc constant varchar2(60) := 'populateApproverDetails';
1286 begin
1287 g_debug := hr_utility.debug_enabled;
1288
1289 if g_debug then
1290 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1291 end if;
1292 -- create and/or populate approver details to item attributes
1293 -- same structer as ame_util.approverRecord2
1294
1295
1296
1297
1298 -- HR_APR_ORIG_SYSTEM_ATTR
1299
1300 create_item_attrib_if_notexist(itemtype=>p_item_type,
1301 itemkey=>p_item_key,
1302 aname=>'HR_APR_ORIG_SYSTEM_ATTR',
1303 text_value=>p_approverRec.orig_system,
1304 number_value=>null,
1305 date_value=>null);
1306
1307 -- HR_APR_ORIG_SYSTEM_ID_ATTR
1308
1309 create_item_attrib_if_notexist(itemtype=>p_item_type,
1310 itemkey=>p_item_key,
1311 aname=>'HR_APR_ORIG_SYSTEM_ID_ATTR',
1312 text_value=>null,
1313 number_value=>p_approverRec.orig_system_id,
1314 date_value=>null);
1315
1316
1317 -- HR_APR_CATEGORY_ATTR
1318
1319 create_item_attrib_if_notexist(itemtype=>p_item_type,
1320 itemkey=>p_item_key,
1321 aname=>'HR_APR_CATEGORY_ATTR',
1322 text_value=>p_approverRec.approver_category,
1323 number_value=>null,
1324 date_value=>null);
1325
1326 -- HR_APR_API_INSERTION_ATTR
1327
1328 create_item_attrib_if_notexist(itemtype=>p_item_type,
1329 itemkey=>p_item_key,
1330 aname=>'HR_APR_API_INSERTION_ATTR',
1331 text_value=>p_approverRec.api_insertion,
1332 number_value=>null,
1333 date_value=>null);
1334
1335 -- HR_APR_AUTHORITY_ATTR
1336
1337 create_item_attrib_if_notexist(itemtype=>p_item_type,
1338 itemkey=>p_item_key,
1339 aname=>'HR_APR_AUTHORITY_ATTR',
1340 text_value=>p_approverRec.authority,
1341 number_value=>null,
1342 date_value=>null);
1343
1344 -- HR_APR_APPROVAL_STATUS_ATTR
1345
1346 create_item_attrib_if_notexist(itemtype=>p_item_type,
1347 itemkey=>p_item_key,
1348 aname=>'HR_APR_APPROVAL_STATUS_ATTR',
1349 text_value=>p_approverRec.approval_status,
1350 number_value=>null,
1351 date_value=>null);
1352
1353 -- HR_APR_ACTION_TYPE_ID_ATTR
1354
1355 create_item_attrib_if_notexist(itemtype=>p_item_type,
1356 itemkey=>p_item_key,
1357 aname=>'HR_APR_ACTION_TYPE_ID_ATTR',
1358 text_value=>null,
1359 number_value=>p_approverRec.action_type_id,
1360 date_value=>null);
1361
1362 -- HR_APR_GRPORCHN_ID_ATTR
1363
1364 create_item_attrib_if_notexist(itemtype=>p_item_type,
1365 itemkey=>p_item_key,
1366 aname=>'HR_APR_GRPORCHN_ID_ATTR',
1367 text_value=>null,
1368 number_value=>p_approverRec.group_or_chain_id,
1369 date_value=>null);
1370
1371 -- HR_APR_OCCURRENCE_ATTR
1372
1373 create_item_attrib_if_notexist(itemtype=>p_item_type,
1374 itemkey=>p_item_key,
1375 aname=>'HR_APR_OCCURRENCE_ATTR',
1376 text_value=>null,
1377 number_value=>p_approverRec.occurrence,
1378 date_value=>null);
1379
1380 -- HR_APR_SOURCE_ATTR
1381
1382 create_item_attrib_if_notexist(itemtype=>p_item_type,
1383 itemkey=>p_item_key,
1384 aname=>'HR_APR_SOURCE_ATTR',
1385 text_value=>p_approverRec.source,
1386 number_value=>null,
1387 date_value=>null);
1388
1389 -- HR_APR_ITEM_CLASS_ATTR
1390
1391 create_item_attrib_if_notexist(itemtype=>p_item_type,
1392 itemkey=>p_item_key,
1393 aname=>'HR_APR_ITEM_CLASS_ATTR',
1394 text_value=>p_approverRec.item_class,
1395 number_value=>null,
1396 date_value=>null);
1397
1398 -- HR_APR_ITEM_ID_ATTR
1399
1400 create_item_attrib_if_notexist(itemtype=>p_item_type,
1401 itemkey=>p_item_key,
1402 aname=>'HR_APR_ITEM_ID_ATTR',
1403 text_value=>p_approverRec.item_id,
1404 number_value=>null,
1405 date_value=>null);
1406
1407 -- HR_APR_ITM_CLS_ORD_NUM_ATTR
1408
1409 create_item_attrib_if_notexist(itemtype=>p_item_type,
1410 itemkey=>p_item_key,
1411 aname=>'HR_APR_ITM_CLS_ORD_NUM_ATTR',
1412 text_value=>null,
1413 number_value=>p_approverRec.item_class_order_number,
1414 date_value=>null);
1415
1416 -- HR_APR_ITM_ORD_NUM_ATTR
1417
1418 create_item_attrib_if_notexist(itemtype=>p_item_type,
1419 itemkey=>p_item_key,
1420 aname=>'HR_APR_ITM_ORD_NUM_ATTR',
1421 text_value=>null,
1422 number_value=>p_approverRec.item_order_number,
1423 date_value=>null);
1424
1425 -- HR_APR_SUB_LST_ORD_NUM_ATTR
1426
1427 create_item_attrib_if_notexist(itemtype=>p_item_type,
1428 itemkey=>p_item_key,
1429 aname=>'HR_APR_SUB_LST_ORD_NUM_ATTR',
1430 text_value=>null,
1431 number_value=>p_approverRec.sub_list_order_number,
1432 date_value=>null);
1433
1434 -- HR_APR_ACT_TYP_ORD_NUM_ATTR
1435
1436 create_item_attrib_if_notexist(itemtype=>p_item_type,
1437 itemkey=>p_item_key,
1438 aname=>'HR_APR_ACT_TYP_ORD_NUM_ATTR',
1439 text_value=>null,
1440 number_value=>p_approverRec.action_type_order_number,
1441 date_value=>null);
1442
1443 -- HR_APR_GRPORCHN_ORD_NUM_ATTR
1444
1445 create_item_attrib_if_notexist(itemtype=>p_item_type,
1446 itemkey=>p_item_key,
1447 aname=>'HR_APR_GRPORCHN_ORD_NUM_ATTR',
1448 text_value=>null,
1449 number_value=>p_approverRec.group_or_chain_order_number,
1450 date_value=>null);
1451
1452 -- HR_APR_MEMBER_ORD_NUM_ATTR
1453
1454 create_item_attrib_if_notexist(itemtype=>p_item_type,
1455 itemkey=>p_item_key,
1456 aname=>'HR_APR_MEMBER_ORD_NUM_ATTR',
1457 text_value=>null,
1458 number_value=>p_approverRec.member_order_number,
1459 date_value=>null);
1460
1461 -- HR_APR_ORD_NUM_ATTR
1462
1463 create_item_attrib_if_notexist(itemtype=>p_item_type,
1464 itemkey=>p_item_key,
1465 aname=>'HR_APR_ORD_NUM_ATTR',
1466 text_value=>null,
1467 number_value=>p_approverRec.approver_order_number,
1468 date_value=>null);
1469
1470
1471 if (g_debug ) then
1472 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1473 end if;
1474
1475 exception
1476 when others then
1477 raise;
1478 end populatePrllApprDet;
1479
1480 procedure getNextApproverRole(p_item_type in varchar2,
1481 p_item_key in varchar2,
1482 p_act_id in number,
1483 funmode in varchar2,
1484 result out nocopy varchar2 )
1485 is
1486 -- local variables
1487 c_proc constant varchar2(30) := 'getNextApproverRole';
1488 lv_current_approver_category varchar2(4000);
1489 l_current_forward_to_username wf_users.name%type;
1490 l_current_forward_to_disp_name wf_users.display_name%type;
1491 l_is_parallel varchar2(10) := null;
1492
1493 begin
1494 g_debug := hr_utility.debug_enabled;
1495
1496 if g_debug then
1497 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1498 end if;
1499 if ( funmode = wf_engine.eng_run ) then
1500 -- HR_APR_CATEGORY_ATTR
1501 lv_current_approver_category :=wf_engine.GetItemAttrText(itemtype => p_item_type ,
1502 itemkey => p_item_key,
1503 aname => 'HR_APR_CATEGORY_ATTR',
1504 ignore_notfound=>true);
1505 -- default approvals
1506 lv_current_approver_category := nvl(lv_current_approver_category,'A');
1507 if(lv_current_approver_category='A') then
1508 l_is_parallel := wf_engine.GetItemAttrText(itemtype => p_item_type ,
1509 itemkey => p_item_key,
1510 aname => 'HR_PARALLEL_ATTR',
1511 ignore_notfound=>true);
1512 if(l_is_parallel = 'PARALLEL') then
1513
1514 storePrllApprDetails(p_item_type,p_item_key);
1515
1516 else
1517
1518 storeApproverDetails(p_item_type,p_item_key);
1519
1520 end if;
1521
1522 result := 'COMPLETE:APPROVAL';
1523 else
1524
1525 l_current_forward_to_disp_name :=
1526 nvl(wf_engine.GetItemAttrText
1527 (itemtype => p_item_type
1528 ,itemkey => p_item_key
1529 ,aname => 'FORWARD_TO_DISPLAY_NAME'),
1530 wf_engine.GetItemAttrText
1531 (itemtype => p_item_type
1532 ,itemkey => p_item_key
1533 ,aname => 'CREATOR_PERSON_DISPLAY_NAME'));
1534
1535 l_current_forward_to_username :=
1536 nvl(wf_engine.GetItemAttrText
1537 (itemtype => p_item_type
1538 ,itemkey => p_item_key
1539 ,aname => 'FORWARD_TO_USERNAME'),
1540 wf_engine.GetItemAttrText
1541 (itemtype => p_item_type
1542 ,itemkey => p_item_key
1543 ,aname => 'CREATOR_PERSON_USERNAME'));
1544
1545
1546 wf_engine.SetItemAttrText
1547 (itemtype => p_item_type
1548 ,itemkey => p_item_key
1549 ,aname => 'FORWARD_FROM_USERNAME'
1550 ,avalue => l_current_forward_to_username);
1551 --
1552 wf_engine.SetItemAttrText
1553 (itemtype => p_item_type
1554 ,itemkey => p_item_key
1555 ,aname => 'FORWARD_FROM_DISPLAY_NAME'
1556 ,avalue => l_current_forward_to_disp_name);
1557
1558 result := 'COMPLETE:FYI';
1559 end if;
1560 end if;
1561
1562
1563 if (g_debug ) then
1564 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1565 end if;
1566 exception
1567 when others then
1568 raise;
1569 end getNextApproverRole;
1570
1571
1572
1573 function isPreNonAMEFYIComplete( p_item_type in varchar2,
1574 p_item_key in varchar2) return boolean is
1575 lv_result varchar2(50);
1576 c_proc constant varchar2(60) := 'isPreNonAMEFYIComplete';
1577 begin
1578 g_debug := hr_utility.debug_enabled;
1579 if g_debug then
1580 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1581 end if;
1582
1583 -- call the initialize code
1584 --HR_DYNAMIC_APPROVAL_WEB.INITIALIZE_ITEM_ATTRIBUTES(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
1585
1586
1587 -- check if we need to go through this cycle or completed
1588 HR_DYNAMIC_APPROVAL_WEB.CHECK_ONSUBMIT_NOTIFIER(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
1589
1590 if(lv_result='COMPLETE:N') then
1591 HR_DYNAMIC_APPROVAL_WEB.GET_ONSUBMIT_NOTIFIER(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
1592
1593 -- ONSUB_FWD_TO_USERNAME
1594 create_item_attrib_if_notexist(itemtype=>p_item_type,
1595 itemkey=>p_item_key,
1596 aname=>'HR_APR_NAME_ATTR',
1597 text_value=>wf_engine.GetItemAttrText(itemtype => p_item_type ,
1598 itemkey => p_item_key,
1599 aname => 'ONSUB_FWD_TO_USERNAME',
1600 ignore_notfound=>true),
1601 number_value=>null,
1602 date_value=>null);
1603
1604
1605 -- fyiApproverCategory
1606
1607 create_item_attrib_if_notexist(itemtype => p_item_type
1608 ,itemkey => p_item_key
1609 ,aname => 'HR_APR_CATEGORY_ATTR'
1610 ,text_value=>ame_util.fyiApproverCategory
1611 ,number_value=>null,
1612 date_value=>null
1613 );
1614
1615
1616 --HR_APR_SUB_LST_ORD_NUM_ATTR
1617 create_item_attrib_if_notexist(itemtype => p_item_type
1618 ,itemkey => p_item_key
1619 ,aname => 'HR_APR_SUB_LST_ORD_NUM_ATTR'
1620 ,text_value=>null
1621 ,number_value=>1,
1622 date_value=>null
1623 );
1624 create_item_attrib_if_notexist(itemtype => p_item_type
1625 ,itemkey => p_item_key
1626 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR'
1627 ,text_value=>null
1628 ,number_value=>null,
1629 date_value=>null
1630 );
1631 wf_engine.SetItemAttrNumber
1632 (itemtype => p_item_type
1633 ,itemkey => p_item_key
1634 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR'
1635 ,avalue=>null);
1636
1637 if (g_debug ) then
1638 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 11);
1639 end if;
1640 return false;
1641 else
1642 if (g_debug ) then
1643 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 12);
1644 end if;
1645 return true;
1646 end if;
1647 exception
1648 when others then
1649 if g_debug then
1650 hr_utility.set_location('Error in isPreNonAMEFYIComplete SQLERRM' ||' '||to_char(SQLCODE),20);
1651 end if;
1652 raise;
1653 end isPreNonAMEFYIComplete;
1654
1655
1656 function isPostNonAMEFYIComplete( p_item_type in varchar2,
1657 p_item_key in varchar2) return boolean is
1658 lv_result varchar2(50);
1659 c_proc constant varchar2(60) := 'isPostNonAMEFYIComplete';
1660 begin
1661 g_debug := hr_utility.debug_enabled;
1662
1663 if g_debug then
1664 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1665 end if;
1666
1667 -- fyiApproverCategory
1668 HR_DYNAMIC_APPROVAL_WEB.CHECK_ONAPPROVAL_NOTIFIER(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
1669
1670 if(lv_result='COMPLETE:N') then
1671
1672 HR_DYNAMIC_APPROVAL_WEB.GET_ONAPPROVAL_NOTIFIER(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
1673
1674 -- ONAPPR_FWD_TO_USERNAME
1675
1676 create_item_attrib_if_notexist(itemtype=>p_item_type,
1677 itemkey=>p_item_key,
1678 aname=>'HR_APR_NAME_ATTR',
1679 text_value=>wf_engine.GetItemAttrText(itemtype => p_item_type ,
1680 itemkey => p_item_key,
1681 aname => 'ONAPPR_FWD_TO_USERNAME',
1682 ignore_notfound=>true),
1683 number_value=>null,
1684 date_value=>null);
1685
1686
1687 create_item_attrib_if_notexist(itemtype => p_item_type
1688 ,itemkey => p_item_key
1689 ,aname => 'HR_APR_CATEGORY_ATTR'
1690 ,text_value=>ame_util.fyiApproverCategory
1691 ,number_value=>null,
1692 date_value=>null
1693 );
1694
1695
1696 --HR_APR_SUB_LST_ORD_NUM_ATTR
1697 create_item_attrib_if_notexist(itemtype => p_item_type
1698 ,itemkey => p_item_key
1699 ,aname => 'HR_APR_SUB_LST_ORD_NUM_ATTR'
1700 ,text_value=>null
1701 ,number_value=>3,
1702 date_value=>null
1703 );
1704
1705 create_item_attrib_if_notexist(itemtype => p_item_type
1706 ,itemkey => p_item_key
1707 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR'
1708 ,text_value=>null
1709 ,number_value=>null,
1710 date_value=>null
1711 );
1712 wf_engine.SetItemAttrNumber
1713 (itemtype => p_item_type
1714 ,itemkey => p_item_key
1715 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR'
1716 ,avalue=>null);
1717 if (g_debug ) then
1718 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 11);
1719 end if;
1720 return false;
1721 else
1722 if (g_debug ) then
1723 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 12);
1724 end if;
1725 return true;
1726 end if;
1727 exception
1728 when others then
1729 if g_debug then
1730 hr_utility.set_location('Error in isPostNonAMEFYIComplete SQLERRM' ||' '||to_char(SQLCODE),20);
1731 end if;
1732 raise;
1733
1734 end isPostNonAMEFYIComplete;
1735 procedure populatePrllApprDetails(p_item_type in varchar2,
1736 p_item_key in varchar2,
1737 p_role_name in varchar2)
1738 is
1739 -- local variables
1740 c_proc constant varchar2(60) := 'populatePrllApprDetails';
1741 l_users varchar2(1000) := null;
1742 l_approver_role_info_tbl wf_directory.wf_local_roles_tbl_type;
1743
1744 begin
1745 g_debug := hr_utility.debug_enabled;
1746
1747 if g_debug then
1748 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1749 end if;
1750
1751 wf_directory.GetRoleInfo2(p_role_name,l_approver_role_info_tbl);
1752
1753 if g_debug then
1754 hr_utility.set_location('l_approver_role_info_tbl(1).name : '|| l_approver_role_info_tbl(1).name, 999);
1755 hr_utility.set_location('l_approver_role_info_tbl(1).orig_system : '|| l_approver_role_info_tbl(1).orig_system, 999);
1756 hr_utility.set_location('l_approver_role_info_tbl(1).orig_system_id : '|| l_approver_role_info_tbl(1).orig_system_id, 999);
1757 hr_utility.set_location('l_approver_role_info_tbl(1).display_name : '|| l_approver_role_info_tbl(1).display_name, 999);
1758 end if;
1759
1760
1761 -- create and/or populate approver details to item attributes
1762 -- same structer as ame_util.approverRecord2
1763 -- HR_APR_NAME_ATTR
1764
1765 create_item_attrib_if_notexist(itemtype=>p_item_type,
1766 itemkey=>p_item_key,
1767 aname=>'HR_APR_NAME_ATTR',
1768 text_value=>l_approver_role_info_tbl(1).name,
1769 number_value=>null,
1770 date_value=>null);
1771
1772
1773 -- HR_APR_DISPLAY_NAME_ATTR
1774
1775 create_item_attrib_if_notexist(itemtype=>p_item_type,
1776 itemkey=>p_item_key,
1777 aname=>'HR_APR_DISPLAY_NAME_ATTR',
1778 text_value=>l_approver_role_info_tbl(1).display_name,
1779 number_value=>null,
1780 date_value=>null);
1781
1782
1783 if (g_debug ) then
1784 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
1785 end if;
1786
1787 exception
1788 when others then
1789 raise;
1790 end populatePrllApprDetails;
1791
1792
1793
1794 procedure isFinalApprover( p_item_type in varchar2,
1795 p_item_key in varchar2,
1796 p_act_id in number,
1797 funmode in varchar2,
1798 result out nocopy varchar2 )
1799 AS
1800 -- Local Variables
1801 l_proc constant varchar2(61) := g_package||'isFinalApprover';
1802 c_proc constant varchar2(60) := 'isFinalApprover';
1803 l_name varchar2(60);
1804
1805 -- Variables required for AME API
1806 c_application_id integer;
1807 c_transaction_id varchar2(25);
1808 c_transaction_type varchar2(25);
1809 c_next_approvers ame_util.approverstable2;
1810 c_approvalprocesscompleteynout varchar2(1) ;
1811 c_approver_to_notify_rec ame_util.approverRecord2;
1812 error_message_text varchar2(2000);
1813
1814 l_is_parallel varchar2(10) := null;
1815 l_role_name wf_roles.name%TYPE;
1816 l_role_display wf_roles.display_name%TYPE;
1817 l_role_exists varchar2(1);
1818 l_users varchar2(1000) := null;
1819
1820 CURSOR c_chk_wf_role (p_role_name VARCHAR2) IS
1821 SELECT 'Y'
1822 FROM wf_local_roles
1823 WHERE name = p_role_name;
1824
1825 CURSOR c_get_users_from_role (p_role_name VARCHAR2) IS
1826 select user_name
1827 from wf_user_roles
1828 where role_name = p_role_name;
1829
1830 --
1831
1832 BEGIN
1833 g_debug := hr_utility.debug_enabled;
1834 c_approvalprocesscompleteynout := ame_util.booleanFalse;
1835
1836 if g_debug then
1837 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1838 end if;
1839
1840 error_message_text := wf_engine.GetItemAttrText(itemtype => p_item_type ,
1841 itemkey => p_item_key,
1842 aname => 'ERROR_MESSAGE_TEXT',
1843 ignore_notfound=>true);
1844
1845
1846 if(error_message_text is not null) then
1847
1848 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_MESSAGE_TEXT',null);
1849 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_ITEM_TYPE',null);
1850 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_ITEM_KEY',null);
1851
1852 end if;
1853
1854 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
1855 itemkey => p_item_key,
1856 aname => 'HR_AME_TRAN_TYPE_ATTR');
1857
1858 if ( funmode = 'RUN' ) then
1859
1860 -- fix for bug 4454439
1861 begin
1862 -- re-intialize the performer roles
1863 hr_approval_ss.reinitperformerroles(p_notification_id=>null
1864 ,p_transaction_id=>null
1865 ,p_item_type=>p_item_type
1866 ,p_item_key=>p_item_key);
1867 exception
1868 when others then
1869 null;
1870 end;
1871
1872 -- -----------------------------------------------------------------------
1873 -- expose the wf control variables to the custom package
1874 -- -----------------------------------------------------------------------
1875 -- Needed for backward compatibility
1876 if g_debug then
1877 hr_utility.set_location('calling set_custom_wf_globals with p_item_type: '|| p_item_type ||' and p_item_key:' || p_item_key,2);
1878 end if;
1879
1880 set_custom_wf_globals
1881 (p_itemtype => p_item_type
1882 ,p_itemkey => p_item_key);
1883
1884 -- check if the pre-notification process for non-AME FYI is completed
1885 if g_debug then
1886 hr_utility.set_location('calling isPreNonAMEFYIComplete with p_item_type: '|| p_item_type ||' and p_item_key:' || p_item_key,3);
1887 end if;
1888
1889 if(NOT isPreNonAMEFYIComplete(p_item_type,p_item_key)) then
1890 result := 'COMPLETE:'||'N';
1891 return;
1892 end if;
1893
1894 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1895 itemkey => p_item_key,
1896 aname => 'HR_AME_APP_ID_ATTR');
1897 c_application_id := nvl(c_application_id,800);
1898 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
1899 itemkey => p_item_key,
1900 aname => 'TRANSACTION_ID');
1901 if(c_transaction_type is not null) then
1902 begin
1903 if g_debug then
1904 hr_utility.set_location('calling ame_api2.getNextApprovers4 ',4);
1905 hr_utility.set_location('c_application_id:'||c_application_id,5);
1906 hr_utility.set_location('c_transaction_type:'|| c_transaction_type,6);
1907 hr_utility.set_location('c_transaction_id:'|| c_transaction_id,7);
1908 hr_utility.set_location('flagApproversAsNotifiedIn:'|| ame_util.booleanFalse,8);
1909 end if;
1910 ame_api2.getNextApprovers4
1911 (applicationIdIn => c_application_id
1912 ,transactionTypeIn => c_transaction_type
1913 ,transactionIdIn => c_transaction_id
1914 ,flagApproversAsNotifiedIn=>ame_util.booleanFalse
1915 ,approvalProcessCompleteYNOut => c_approvalprocesscompleteynout
1916 ,nextApproversOut => c_next_approvers);
1917
1918 if g_debug then
1919 hr_utility.set_location('returned from ame_api2.getNextApprovers4, number records fetched:'||c_next_approvers.count,9);
1920 end if;
1921
1922 -- Parallel Approvers case
1923 if(c_next_approvers.COUNT >1 ) then
1924
1925 if g_debug then
1926 hr_utility.set_location('Entered into Parallel Approval mode', 1);
1927 end if;
1928
1929 create_item_attrib_if_notexist(itemtype=>p_item_type,
1930 itemkey=>p_item_key,
1931 aname=>'HR_PARALLEL_ATTR',
1932 text_value=>'PARALLEL',
1933 number_value=>null,
1934 date_value=>null);
1935
1936
1937 -- Call the AME API to get the list of ALL approvers
1938
1939 if c_next_approvers.count > 0 then
1940 for l_index in 1 .. c_next_approvers.count
1941 loop
1942 hr_utility.set_location('Approver Name : ' || c_next_approvers(l_index).name,115);
1943 hr_utility.set_location('Approver Orig system : ' || c_next_approvers(l_index).orig_system,115);
1944
1945 if(c_next_approvers(l_index).orig_system = 'PQH_ROLE' OR c_next_approvers(l_index).orig_system = 'POS') then
1946 for c in c_get_users_from_role(c_next_approvers(l_index).name) loop
1947 IF l_users IS NULL THEN
1948 l_users := c.user_name;
1949 ELSE
1950 l_users := l_users ||','||c.user_name;
1951 END IF;
1952 end loop;
1953 else
1954
1955 IF l_users IS NULL THEN
1956 l_users := c_next_approvers(l_index).name;
1957 ELSE
1958 l_users := l_users ||','||c_next_approvers(l_index).name;
1959 END IF;
1960 end if;
1961 end loop;
1962 -- added as fix for Bug 16221443
1963 populatePrllApprDet(p_item_type,p_item_key,c_next_approvers(1));
1964 end if;
1965
1966 hr_utility.set_location('l_users : ' || l_users,111);
1967
1968 l_role_name:='PRLL_APPR_'||c_transaction_id || '_'|| l_users;
1969 l_role_display:='Parallel Approvers Group For '||c_transaction_id || ' '|| l_users;
1970 l_role_exists:='N';
1971
1972 if g_debug then
1973 hr_utility.set_location('l_role_name : ' || l_role_name, 1);
1974 hr_utility.set_location('l_role_display : ' || l_role_display, 1);
1975 end if;
1976
1977 -- Check if the ADHOC role already exists
1978 OPEN c_chk_wf_role(l_role_name);
1979 FETCH c_chk_wf_role INTO l_role_exists;
1980 CLOSE c_chk_wf_role;
1981
1982 -- Check if AME encountered any errors.
1983 IF c_next_approvers.COUNT <> 0 then
1984 -- Check if the ADHOC role already exists
1985 IF l_role_exists = 'Y' THEN
1986 -- If the role exists, then empty the existing role list
1987 wf_directory.RemoveUsersFromAdHocRole
1988 (role_name => l_role_name,
1989 role_users => NULL);
1990
1991 -- Add the users we have identified to the role list.
1992 wf_directory.AddUsersToAdHocRole
1993 (role_name => l_role_name,
1994 role_users => l_users);
1995 ELSE
1996 -- Create an ADHOC role for the approver list and add the
1997 -- users.
1998 wf_directory.CreateAdHocRole
1999 (role_name => l_role_name,
2000 role_display_name => l_role_display,
2001 role_users => l_users,
2002 expiration_date => NULL);
2003
2004 END IF; -- Check ADHOC role exists
2005
2006
2007 END IF; -- approver count
2008 else
2009
2010 if g_debug then
2011 hr_utility.set_location('Entered into Serial Approval mode', 1);
2012 end if;
2013 if(c_approvalprocesscompleteynout<>'Y') then
2014 create_item_attrib_if_notexist(itemtype=>p_item_type,
2015 itemkey=>p_item_key,
2016 aname=>'HR_PARALLEL_ATTR',
2017 text_value=>'SERIAL',
2018 number_value=>null,
2019 date_value=>null);
2020 c_approver_to_notify_rec :=c_next_approvers(1);
2021 end if;
2022 end if;
2023
2024 exception
2025 when others then
2026 if g_debug then
2027 hr_utility.set_location('Error in isFinalApprover, SQLERRM' ||' '||to_char(SQLCODE),20);
2028 end if;
2029 handleApprovalErrors(p_item_type, p_item_key, sqlerrm);
2030 result := 'COMPLETE:'||'E';
2031 return;
2032 end;
2033
2034 else
2035 -- non AME
2036 begin
2037 hr_approval_ss.getNextCustomApprover
2038 (p_item_type=>p_item_type,
2039 p_item_key =>p_item_key,
2040 p_approvalprocesscompleteynout=>c_approvalprocesscompleteynout,
2041 p_approver_rec=>c_approver_to_notify_rec);
2042 exception
2043 when others then
2044 if g_debug then
2045 hr_utility.set_location('Error in isFinalApprover calling hr_approval_ss.getNextCustomApprover, SQLERRM' ||' '||to_char(SQLCODE),20);
2046 end if;
2047 raise;
2048 end;
2049 end if ; -- check for AME
2050
2051
2052 if g_debug then
2053 hr_utility.set_location('c_approvalprocesscompleteynout:'||c_approvalprocesscompleteynout,12);
2054 end if;
2055
2056 -- check if the approval process is complete
2057 if(c_approvalprocesscompleteynout='Y') then
2058 result := 'COMPLETE:'||'Y';
2059 -- check if the pre-notification process for non-AME FYI is completed
2060 if g_debug then
2061 hr_utility.set_location('calling isPostNonAMEFYIComplete',13);
2062 end if;
2063
2064 if(NOT isPostNonAMEFYIComplete(p_item_type,p_item_key)) then
2065 result := 'COMPLETE:'||'N';
2066 return;
2067 end if;
2068 else
2069 l_is_parallel := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2070 itemkey => p_item_key,
2071 aname => 'HR_PARALLEL_ATTR',
2072 ignore_notfound=>true);
2073
2074 if(l_is_parallel = 'PARALLEL') then
2075 populatePrllApprDetails(p_item_type,p_item_key,l_role_name);
2076 else
2077 populateApproverDetails(p_item_type,p_item_key,c_approver_to_notify_rec);
2078 end if;
2079
2080 result := 'COMPLETE:'||'N';
2081 end if;
2082 end if;
2083
2084
2085 if (g_debug ) then
2086 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 50);
2087 end if;
2088
2089
2090 EXCEPTION
2091 WHEN OTHERS THEN
2092 if g_debug then
2093 hr_utility.set_location('Error in isFinalApprover , SQLERRM' ||' '||to_char(SQLCODE),30);
2094 end if;
2095 raise;
2096
2097 END isFinalApprover ;
2098
2099
2100 procedure updateApprovalHistory( p_item_type in varchar2,
2101 p_item_key in varchar2,
2102 p_act_id in number,
2103 funmode in varchar2,
2104 result out nocopy varchar2 )
2105
2106 is
2107 -- local variables
2108 c_proc constant varchar2(30) := 'updateApprovalHistory';
2109 -- Variables required for AME API
2110 c_application_id integer;
2111 c_transaction_id varchar2(25);
2112 c_transaction_type varchar2(25);
2113 c_next_approvers ame_util.approverstable2;
2114 c_approvalprocesscompleteynout ame_util.charType;
2115 l_current_forward_to_username wf_users.name%type;
2116
2117 begin
2118 g_debug := hr_utility.debug_enabled;
2119
2120 if g_debug then
2121 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2122 end if;
2123 if ( funmode = wf_engine.eng_run ) then
2124 -- call PQH_SS_HISTORY.transfer_approval_to_history
2125 if g_debug then
2126 hr_utility.set_location('calling PQH_SS_HISTORY.transfer_approval_to_history ',2);
2127 hr_utility.set_location('p_item_type:'|| p_item_type,3);
2128 hr_utility.set_location('p_item_key:'|| p_item_key,4);
2129 end if;
2130
2131 PQH_SS_HISTORY.transfer_approval_to_history(p_item_type,p_item_key,p_act_id,funmode,result);
2132 -- reset the status to pending approval
2133 if g_debug then
2134 hr_utility.set_location('calling PQH_SS_WORKFLOW.set_txn_approve_status ',5);
2135 hr_utility.set_location('p_item_type:'|| p_item_type,6);
2136 hr_utility.set_location('p_item_key:'|| p_item_key,7);
2137 end if;
2138
2139 PQH_SS_WORKFLOW.set_txn_approve_status(p_item_type,p_item_key,p_act_id,funmode,result);
2140
2141 end if;
2142
2143 if (g_debug ) then
2144 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2145 end if;
2146 exception
2147 when others then
2148 raise;
2149 end updateApprovalHistory;
2150
2151 procedure forwardToRoleReInit(p_item_type in wf_items.item_type%type,
2152 p_item_key in wf_items.item_key%type) is
2153 lv_role_name wf_roles.name%type;
2154 lv_role_disp_name wf_roles.name%type;
2155 lv_role_orig_system wf_roles.orig_system%type;
2156 lv_role_orig_sys_id wf_roles.orig_system_id%type;
2157 begin
2158
2159 -- FORWARD_TO_PERSON_ID
2160 lv_role_orig_sys_id:=wf_engine.getitemattrnumber(p_item_type,p_item_key,'FORWARD_TO_PERSON_ID',true);
2161
2162 if(lv_role_orig_sys_id is not null) then
2163 -- need to revisit with role based support in SSHR transaction
2164 lv_role_orig_system := nvl( wf_engine.GetItemAttrText
2165 (itemtype => p_item_type
2166 ,itemkey => p_item_key
2167 ,aname => 'FORWARD_TO_ORIG_SYS_ATTR'
2168 ,ignore_notfound=>true),'PER');
2169
2170 wf_directory.GetRoleName
2171 (p_orig_system => lv_role_orig_system
2172 ,p_orig_system_id => lv_role_orig_sys_id
2173 ,p_name => lv_role_name
2174 ,p_display_name => lv_role_disp_name);
2175 -- set the concerned item attributes
2176 -- FORWARD_TO_USERNAME
2177 create_item_attrib_if_notexist(itemtype => p_item_type
2178 ,itemkey => p_item_key
2179 ,aname => 'FORWARD_TO_USERNAME'
2180 ,text_value=>lv_role_name
2181 ,number_value=>null,
2182 date_value=>null
2183 );
2184 end if;
2185
2186 end forwardToRoleReInit;
2187
2188
2189 procedure updateApproveStatus( p_item_type in varchar2,
2190 p_item_key in varchar2,
2191 p_act_id in number,
2192 funmode in varchar2,
2193 result out nocopy varchar2 )
2194
2195 is
2196 -- local variables
2197 c_proc constant varchar2(30) := 'updateApproveStatus';
2198 -- Variables required for AME API
2199 c_application_id integer;
2200 c_transaction_id varchar2(25);
2201 c_transaction_type varchar2(25);
2202 c_next_approvers ame_util.approverstable2;
2203 c_approvalprocesscompleteynout ame_util.charType;
2204 l_current_forward_to_username wf_users.name%type;
2205 notification_rec ame_util2.notificationRecord;
2206 l_is_parallel varchar2(10) := null;
2207 original_forward_to_user wf_users.name%type;
2208 l_role_name wf_users.name%type;
2209
2210 ntfidstartpos number := null;
2211 startpos number := null;
2212 endpos number := null;
2213 prll_ntf varchar2(600) := null;
2214
2215 CURSOR c_get_users_from_role (p_role_name VARCHAR2) IS
2216 select *
2217 from wf_user_roles
2218 where role_name = p_role_name;
2219
2220
2221 begin
2222
2223 g_debug := hr_utility.debug_enabled;
2224
2225 if g_debug then
2226 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2227 end if;
2228 if ( funmode = wf_engine.eng_run ) then
2229 -- check if it is AME or custom approvals
2230 c_application_id :=nvl(wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2231 itemkey => p_item_key,
2232 aname => 'HR_AME_APP_ID_ATTR'),800);
2233
2234 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2235 itemkey => p_item_key,
2236 aname => 'TRANSACTION_ID');
2237
2238 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2239 itemkey => p_item_key,
2240 aname => 'HR_AME_TRAN_TYPE_ATTR');
2241 if(c_transaction_type is not null) then
2242 hr_utility.set_location('In(if ( if(c_transaction_type is not null))): '|| c_proc,2);
2243
2244 notification_rec.notification_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2245 itemkey => p_item_key,
2246 aname => 'HR_CONTEXT_NID_ATTR');
2247 notification_rec.user_comments := wf_notification.getattrtext(
2248 notification_rec.notification_id
2249 ,'WF_NOTE');
2250
2251 l_is_parallel := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2252 itemkey => p_item_key,
2253 aname => 'HR_PARALLEL_ATTR',
2254 ignore_notfound=>true);
2255
2256 if(l_is_parallel = 'PARALLEL') then
2257
2258 if g_debug then
2259 hr_utility.set_location('Approval mode is parallel', 1);
2260 end if;
2261
2262 if(notification_rec.notification_id is not null) then
2263
2264 select ORIGINAL_RECIPIENT into original_forward_to_user from wf_notifications where notification_id = notification_rec.notification_id;
2265
2266 prll_ntf := wf_engine.getitemattrtext(p_item_type,
2267 p_item_key,
2268 'PRLL_TRNSFR_DET',true);
2269
2270 if( INSTR(prll_ntf,notification_rec.notification_id) > 0) then
2271
2272 ntfidstartpos := instr(prll_ntf, notification_rec.notification_id,1,1);
2273 startpos := instr(prll_ntf,':',ntfidstartpos,1);
2274 endpos := instr(prll_ntf,'|',ntfidstartpos,1);
2275 original_forward_to_user := substr(prll_ntf,startpos+1, endpos-startpos-1 );
2276
2277 end if;
2278
2279 l_current_forward_to_username := original_forward_to_user;
2280
2281 ame_api2.getNextApprovers4
2282 (applicationIdIn => c_application_id
2283 ,transactionTypeIn => c_transaction_type
2284 ,transactionIdIn => c_transaction_id
2285 ,flagApproversAsNotifiedIn=>ame_util.booleanFalse
2286 ,approvalProcessCompleteYNOut => c_approvalprocesscompleteynout
2287 ,nextApproversOut => c_next_approvers);
2288
2289 for l_index in 1 .. c_next_approvers.count
2290 loop
2291
2292 if(c_next_approvers(l_index).orig_system = 'PQH_ROLE' OR c_next_approvers(l_index).orig_system = 'POS') then
2293 for c in c_get_users_from_role(c_next_approvers(l_index).name) loop
2294 if(c.user_name = original_forward_to_user) then
2295 l_current_forward_to_username := c.role_name;
2296 end if;
2297 end loop;
2298 end if;
2299
2300 end loop;
2301
2302
2303 end if;
2304
2305 else
2306 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => p_item_type
2307 ,itemkey => p_item_key
2308 ,aname => 'FORWARD_TO_USERNAME');
2309 end if;
2310
2311 l_current_forward_to_username := nvl(l_current_forward_to_username,wf_engine.GetItemAttrText(itemtype => p_item_type ,
2312 itemkey => p_item_key,
2313 aname => 'RETURN_TO_USERNAME'));
2314
2315 if g_debug then
2316 hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 3);
2317 hr_utility.set_location('c_application_id:'|| c_application_id, 4);
2318 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 5);
2319 hr_utility.set_location('approvalStatusIn:'|| ame_util.approvedStatus, 6);
2320 hr_utility.set_location('approverNameIn:'|| l_current_forward_to_username, 7);
2321 end if;
2322
2323 hr_utility.set_location('before update AME: ' || l_current_forward_to_username,111);
2324
2325
2326
2327 begin
2328 ame_api6.updateApprovalStatus2(applicationIdIn=>c_application_id,
2329 transactionTypeIn =>c_transaction_type,
2330 transactionIdIn=>c_transaction_id,
2331 approvalStatusIn =>ame_util.approvedStatus,
2332 approverNameIn =>l_current_forward_to_username,
2333 itemClassIn => null,
2334 itemIdIn =>null,
2335 actionTypeIdIn=> null,
2336 groupOrChainIdIn =>null,
2337 occurrenceIn =>null,
2338 notificationIn => notification_rec,
2339 forwardeeIn =>ame_util.emptyApproverRecord2,
2340 updateItemIn =>false);
2341
2342 wf_engine.SetItemAttrText
2343 (itemtype => p_item_type
2344 ,itemkey => p_item_key
2345 ,aname => 'FORWARD_FROM_ROLE_NAME'
2346 ,avalue => l_current_forward_to_username);
2347
2348 l_role_name := wf_engine.getItemAttrText
2349 (itemtype => p_item_type
2350 ,itemkey => p_item_key
2351 ,aname => 'FORWARD_FROM_ROLE_NAME'
2352 ,ignore_notfound=>true);
2353
2354 hr_utility.set_location('l_role_name: ' || l_role_name,111);
2355
2356
2357
2358
2359 exception
2360 when others then
2361 if g_debug then
2362 hr_utility.set_location('Error in updateApproveStatus SQLERRM' ||' '||to_char(SQLCODE),10);
2363 end if;
2364 hr_utility.trace('ORCL error '||SQLERRM);
2365 --start code fix for bug 13610627
2366 --result := wf_engine.eng_trans_default;
2367 handleApprovalErrors(p_item_type, p_item_key, sqlerrm);
2368 result := 'COMPLETE:'||'FAILURE';
2369 --end code fix for bug 13610627
2370 return;
2371 end;
2372
2373 if g_debug then
2374 hr_utility.set_location('returned from calling ame_api2.updateApprovalStatus2', 8);
2375 end if;
2376
2377 --result := wf_engine.eng_trans_default;
2378 result := 'COMPLETE:'||'SUCCESS';
2379 else
2380 null;
2381 end if;
2382 pqh_ss_workflow.set_txn_approve_status(p_item_type,p_item_key,p_act_id,funmode,result);
2383 /*
2384 -- transfer the approval action to history
2385 -- call PQH_SS_HISTORY.transfer_approval_to_history
2386 updateApprovalHistory( p_item_type=>p_item_type,
2387 p_item_key=>p_item_key,
2388 p_act_id=>p_act_id,
2389 funmode=>funmode,
2390 result=>result);*/
2391
2392
2393 result := 'COMPLETE:'||'SUCCESS';
2394 end if;
2395
2396 if (g_debug ) then
2397 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 20);
2398 end if;
2399 exception
2400 when others then
2401 if g_debug then
2402 hr_utility.set_location('Error in updateApproveStatus SQLERRM' ||' '||to_char(SQLCODE),30);
2403 end if;
2404 raise;
2405 end updateApproveStatus;
2406
2407 procedure approver_category( p_item_type in varchar2,
2408 p_item_key in varchar2,
2409 p_act_id in number,
2410 funmode in varchar2,
2411 result out nocopy varchar2 )
2412 is
2413 --
2414 lv_current_approver_category varchar2(4000);
2415 ln_sublist_order_number number;
2416 c_proc constant varchar2(60) := 'approver_category';
2417 begin
2418 g_debug := hr_utility.debug_enabled;
2419
2420 if g_debug then
2421 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2422 end if;
2423
2424 -- get the current approver category
2425 -- HR_APR_CATEGORY_ATTR
2426 lv_current_approver_category:= wf_engine.GetItemAttrText(itemtype => p_item_type ,
2427 itemkey => p_item_key,
2428 aname => 'HR_APR_CATEGORY_ATTR',
2429 ignore_notfound=>true);
2430 --HR_APR_SUB_LST_ORD_NUM_ATTR
2431 ln_sublist_order_number:=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2432 itemkey => p_item_key,
2433 aname => 'HR_APR_SUB_LST_ORD_NUM_ATTR',
2434 ignore_notfound=>true);
2435 -- default approvals
2436 lv_current_approver_category := nvl(lv_current_approver_category,'A');
2437 -- default FYI approver
2438 ln_sublist_order_number:= nvl(ln_sublist_order_number,2);
2439 if(lv_current_approver_category='A') then
2440 result:= 'COMPLETE:'||'APPROVAL';
2441 elsif(ln_sublist_order_number=1) then
2442 result:= 'COMPLETE:'||'PRE';
2443 elsif(ln_sublist_order_number=2) then
2444 result:= 'COMPLETE:'||'AUTH';
2445 elsif(ln_sublist_order_number=3) then
2446 result:= 'COMPLETE:'||'POST';
2447 end if;
2448
2449 if (g_debug ) then
2450 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2451 end if;
2452
2453 exception
2454 when others then
2455 if g_debug then
2456 hr_utility.set_location('Error in approver_category SQLERRM' ||' '||to_char(SQLCODE),20);
2457 end if;
2458 raise;
2459
2460 end approver_category;
2461
2462
2463 procedure flagApproversAsNotified( p_item_type in varchar2,
2464 p_item_key in varchar2,
2465 p_act_id in number,
2466 funmode in varchar2,
2467 result out nocopy varchar2 )
2468 is
2469 -- local variables
2470 c_proc constant varchar2(30) := 'flagApproversAsNotified';
2471 -- Variables required for AME API
2472 c_application_id integer;
2473 c_transaction_id varchar2(25);
2474 c_transaction_type varchar2(25);
2475 l_current_forward_to_username wf_users.name%type;
2476 l_curr_fwd_to_orig_system_id number;
2477 l_is_parallel varchar2(10) := null;
2478 c_next_approvers ame_util.approverstable2;
2479 c_approvalprocesscompleteynout varchar2(1) ;
2480
2481 begin
2482 g_debug := hr_utility.debug_enabled;
2483
2484 if g_debug then
2485 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2486 end if;
2487 if ( funmode = wf_engine.eng_run ) then
2488 -- check if it is AME or custom approvals
2489 c_application_id :=nvl(wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2490 itemkey => p_item_key,
2491 aname => 'HR_AME_APP_ID_ATTR'),800);
2492
2493 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2494 itemkey => p_item_key,
2495 aname => 'TRANSACTION_ID');
2496
2497 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2498 itemkey => p_item_key,
2499 aname => 'HR_AME_TRAN_TYPE_ATTR');
2500 if(c_transaction_type is not null) then
2501 hr_utility.set_location('In(if ( if(c_transaction_type is not null))): '|| c_proc,15);
2502
2503 l_is_parallel := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2504 itemkey => p_item_key,
2505 aname => 'HR_PARALLEL_ATTR',
2506 ignore_notfound=>true);
2507 if(l_is_parallel = 'PARALLEL') then
2508
2509 --------------
2510 ame_api2.getNextApprovers4
2511 (applicationIdIn => c_application_id
2512 ,transactionTypeIn => c_transaction_type
2513 ,transactionIdIn => c_transaction_id
2514 ,flagApproversAsNotifiedIn=>ame_util.booleanFalse
2515 ,approvalProcessCompleteYNOut => c_approvalprocesscompleteynout
2516 ,nextApproversOut => c_next_approvers);
2517
2518
2519 if(c_next_approvers.COUNT >1 ) then
2520
2521
2522 if c_next_approvers.count > 0 then
2523 for l_index in 1 .. c_next_approvers.count
2524 loop
2525 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
2526 transactionTypeIn =>c_transaction_type,
2527 transactionIdIn=>c_transaction_id,
2528 approvalStatusIn =>ame_util.notifiedStatus,
2529 approverNameIn =>c_next_approvers(l_index).name,
2530 itemClassIn => wf_engine.GetItemAttrText(itemtype => p_item_type ,
2531 itemkey => p_item_key,
2532 aname => 'HR_APR_ITEM_CLASS_ATTR'),
2533 -- HR_APR_ITEM_ID_ATTR
2534 itemIdIn =>wf_engine.GetItemAttrText(itemtype => p_item_type ,
2535 itemkey => p_item_key,
2536 aname => 'HR_APR_ITEM_ID_ATTR'),
2537 -- HR_APR_ACTION_TYPE_ID_ATTR
2538 actionTypeIdIn=> wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2539 itemkey => p_item_key,
2540 aname => 'HR_APR_ACTION_TYPE_ID_ATTR'),
2541 -- HR_APR_GRPORCHN_ID_ATTR
2542 groupOrChainIdIn =>wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2543 itemkey => p_item_key,
2544 aname => 'HR_APR_GRPORCHN_ID_ATTR'),
2545 occurrenceIn =>null,
2546 forwardeeIn =>ame_util.emptyApproverRecord2,
2547 updateItemIn =>false);
2548 exit when l_index = 1;
2549 end loop;
2550 end if;
2551 end if;
2552 ----------------
2553
2554
2555 else
2556
2557 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => p_item_type
2558 ,itemkey => p_item_key
2559 ,aname => 'HR_APR_NAME_ATTR');
2560 l_curr_fwd_to_orig_system_id :=wf_engine.getItemAttrNumber
2561 (itemtype => p_item_type
2562 ,itemkey => p_item_key
2563 ,aname => 'HR_APR_ORIG_SYSTEM_ID_ATTR');
2564 if(l_curr_fwd_to_orig_system_id is not null) then
2565
2566 if g_debug then
2567 hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 2);
2568 hr_utility.set_location('c_application_id:'|| c_application_id, 3);
2569 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 4);
2570 hr_utility.set_location('c_transaction_id:'|| c_transaction_id, 5);
2571 hr_utility.set_location('approvalStatusIn:'|| ame_util.notifiedStatus, 6);
2572 hr_utility.set_location('approverNameIn:'|| l_current_forward_to_username, 7);
2573 end if;
2574
2575 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
2576 transactionTypeIn =>c_transaction_type,
2577 transactionIdIn=>c_transaction_id,
2578 approvalStatusIn =>ame_util.notifiedStatus,
2579 approverNameIn =>l_current_forward_to_username,
2580 itemClassIn => wf_engine.GetItemAttrText(itemtype => p_item_type ,
2581 itemkey => p_item_key,
2582 aname => 'HR_APR_ITEM_CLASS_ATTR'),
2583 -- HR_APR_ITEM_ID_ATTR
2584 itemIdIn =>wf_engine.GetItemAttrText(itemtype => p_item_type ,
2585 itemkey => p_item_key,
2586 aname => 'HR_APR_ITEM_ID_ATTR'),
2587 -- HR_APR_ACTION_TYPE_ID_ATTR
2588 actionTypeIdIn=> wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2589 itemkey => p_item_key,
2590 aname => 'HR_APR_ACTION_TYPE_ID_ATTR'),
2591 -- HR_APR_GRPORCHN_ID_ATTR
2592 groupOrChainIdIn =>wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2593 itemkey => p_item_key,
2594 aname => 'HR_APR_GRPORCHN_ID_ATTR'),
2595 occurrenceIn =>null,
2596 forwardeeIn =>ame_util.emptyApproverRecord2,
2597 updateItemIn =>false);
2598 end if;
2599 end if;
2600
2601 result := wf_engine.eng_trans_default;
2602 else
2603 null;
2604 end if;
2605 end if;
2606
2607 if (g_debug ) then
2608 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2609 end if;
2610 exception
2611 when others then
2612 if g_debug then
2613 hr_utility.set_location('Error in flagApproversAsNotified SQLERRM' ||' '||to_char(SQLCODE),20);
2614 end if;
2615 raise;
2616 end flagApproversAsNotified;
2617
2618
2619 procedure updateNoResponseStatus( p_item_type in varchar2,
2620 p_item_key in varchar2,
2621 p_act_id in number,
2622 funmode in varchar2,
2623 result out nocopy varchar2 )
2624
2625 is
2626 -- local variables
2627 c_proc constant varchar2(30) := 'updateNoResponseStatus';
2628 -- Variables required for AME API
2629 c_application_id integer;
2630 c_transaction_id varchar2(25);
2631 c_transaction_type varchar2(25);
2632 c_next_approvers ame_util.approverstable2;
2633 c_approvalprocesscompleteynout ame_util.charType;
2634 l_current_forward_to_username wf_users.name%type;
2635 l_is_parallel varchar2(10) := null;
2636
2637 CURSOR c_get_users_from_role (p_role_name VARCHAR2) IS
2638 select *
2639 from wf_user_roles
2640 where role_name = p_role_name;
2641
2642 begin
2643 g_debug := hr_utility.debug_enabled;
2644
2645 if g_debug then
2646 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2647 end if;
2648 if ( funmode = wf_engine.eng_run ) then
2649 -- check if it is AME or custom approvals
2650 c_application_id :=nvl(wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2651 itemkey => p_item_key,
2652 aname => 'HR_AME_APP_ID_ATTR'),800);
2653
2654 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2655 itemkey => p_item_key,
2656 aname => 'TRANSACTION_ID');
2657
2658 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2659 itemkey => p_item_key,
2660 aname => 'HR_AME_TRAN_TYPE_ATTR');
2661 if(c_transaction_type is not null) then
2662 hr_utility.set_location('In(if ( if(c_transaction_type is not null))): '|| c_proc,15);
2663
2664
2665 l_is_parallel := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2666 itemkey => p_item_key,
2667 aname => 'HR_PARALLEL_ATTR',
2668 ignore_notfound=>true);
2669
2670
2671 if(l_is_parallel = 'PARALLEL') then
2672
2673 ame_api2.getNextApprovers4
2674 (applicationIdIn => c_application_id
2675 ,transactionTypeIn => c_transaction_type
2676 ,transactionIdIn => c_transaction_id
2677 ,flagApproversAsNotifiedIn=>ame_util.booleanFalse
2678 ,approvalProcessCompleteYNOut => c_approvalprocesscompleteynout
2679 ,nextApproversOut => c_next_approvers);
2680
2681 if g_debug then
2682 hr_utility.set_location('returned from ame_api2.getNextApprovers4, number records fetched:'||c_next_approvers.count,9);
2683 end if;
2684
2685 if(c_next_approvers.COUNT >1 ) then
2686
2687
2688 if c_next_approvers.count > 0 then
2689 for l_index in 1 .. c_next_approvers.count
2690 loop
2691 hr_utility.set_location('Approver Name : ' || c_next_approvers(l_index).name,115);
2692 hr_utility.set_location('Approver Orig system : ' || c_next_approvers(l_index).orig_system,115);
2693
2694 if(c_next_approvers(l_index).orig_system = 'PQH_ROLE' OR c_next_approvers(l_index).orig_system = 'POS') then
2695 for c in c_get_users_from_role(c_next_approvers(l_index).name) loop
2696 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
2697 transactionTypeIn =>c_transaction_type,
2698 transactionIdIn=>c_transaction_id,
2699 approvalStatusIn =>ame_util.noResponseStatus,
2700 approverNameIn =>c.user_name,
2701 itemClassIn => null,
2702 itemIdIn =>null,
2703 actionTypeIdIn=> null,
2704 groupOrChainIdIn =>null,
2705 occurrenceIn =>null,
2706 forwardeeIn =>ame_util.emptyApproverRecord2,
2707 updateItemIn =>false);
2708 end loop;
2709 else
2710
2711 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
2712 transactionTypeIn =>c_transaction_type,
2713 transactionIdIn=>c_transaction_id,
2714 approvalStatusIn =>ame_util.noResponseStatus,
2715 approverNameIn =>c_next_approvers(l_index).name,
2716 itemClassIn => null,
2717 itemIdIn =>null,
2718 actionTypeIdIn=> null,
2719 groupOrChainIdIn =>null,
2720 occurrenceIn =>null,
2721 forwardeeIn =>ame_util.emptyApproverRecord2,
2722 updateItemIn =>false);
2723 end if;
2724
2725 end loop;
2726 end if;
2727
2728 end if;
2729
2730
2731 else
2732 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => p_item_type
2733 ,itemkey => p_item_key
2734 ,aname => 'FORWARD_TO_USERNAME');
2735 if g_debug then
2736 hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 2);
2737 hr_utility.set_location('c_application_id:'|| c_application_id, 3);
2738 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 4);
2739 hr_utility.set_location('c_transaction_id:'|| c_transaction_id, 5);
2740 hr_utility.set_location('approvalStatusIn:'|| ame_util.noResponseStatus, 6);
2741 hr_utility.set_location('approverNameIn:'|| l_current_forward_to_username, 7);
2742 end if;
2743
2744 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
2745 transactionTypeIn =>c_transaction_type,
2746 transactionIdIn=>c_transaction_id,
2747 approvalStatusIn =>ame_util.noResponseStatus,
2748 approverNameIn =>l_current_forward_to_username,
2749 itemClassIn => null,
2750 itemIdIn =>null,
2751 actionTypeIdIn=> null,
2752 groupOrChainIdIn =>null,
2753 occurrenceIn =>null,
2754 forwardeeIn =>ame_util.emptyApproverRecord2,
2755 updateItemIn =>false);
2756
2757 end if;
2758
2759
2760 result := wf_engine.eng_trans_default;
2761 else
2762 result := wf_engine.eng_trans_default;
2763 end if;
2764 pqh_ss_workflow.set_txn_approve_status(p_item_type,p_item_key,p_act_id,funmode,result);
2765 end if;
2766
2767 if (g_debug ) then
2768 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2769 end if;
2770
2771
2772 exception
2773 when others then
2774 result := wf_engine.eng_trans_default;
2775 return;
2776 end updateNoResponseStatus;
2777
2778 procedure setRespondedUserContext( p_item_type in varchar2,
2779 p_item_key in varchar2,
2780 p_act_id in number,
2781 funmode in varchar2,
2782 result out nocopy varchar2 )
2783
2784 is
2785 -- local variables
2786 c_proc constant varchar2(30) := 'setRespondedUserContext';
2787
2788 begin
2789
2790
2791 g_debug := hr_utility.debug_enabled;
2792
2793 if g_debug then
2794 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2795 end if;
2796
2797 if ( funmode = 'RESPOND' OR funmode = 'TIMEOUT') then
2798 create_item_attrib_if_notexist(itemtype=>p_item_type,
2799 itemkey=>p_item_key,
2800 aname=>'HR_CONTEXT_NID_ATTR',
2801 text_value=>null,
2802 number_value=>wf_engine.context_nid,
2803 date_value=>null);
2804 create_item_attrib_if_notexist(itemtype=>p_item_type,
2805 itemkey=>p_item_key,
2806 aname=>'HR_CONTEXT_USER_ATTR',
2807 text_value=>wf_engine.context_user,
2808 number_value=>null,
2809 date_value=>null);
2810 create_item_attrib_if_notexist(itemtype=>p_item_type,
2811 itemkey=>p_item_key,
2812 aname=>'HR_CONTEXT_RECIPIENT_ROLE_ATTR',
2813 text_value=>wf_engine.context_recipient_role,
2814 number_value=>null,
2815 date_value=>null);
2816
2817 create_item_attrib_if_notexist(itemtype=>p_item_type,
2818 itemkey=>p_item_key,
2819 aname=>'HR_CONTEXT_ORIG_RECIPIENT_ATTR',
2820 text_value=>wf_engine.context_original_recipient,
2821 number_value=>null,
2822 date_value=>null);
2823
2824 create_item_attrib_if_notexist(itemtype=>p_item_type,
2825 itemkey=>p_item_key,
2826 aname=>'HR_CONTEXT_FROM_ROLE_ATTR',
2827 text_value=>wf_engine.context_from_role,
2828 number_value=>null,
2829 date_value=>null);
2830 create_item_attrib_if_notexist(itemtype=>p_item_type,
2831 itemkey=>p_item_key,
2832 aname=>'HR_CONTEXT_NEW_ROLE_ATTR',
2833 text_value=>wf_engine.context_new_role,
2834 number_value=>null,
2835 date_value=>null);
2836 create_item_attrib_if_notexist(itemtype=>p_item_type,
2837 itemkey=>p_item_key,
2838 aname=>'HR_CONTEXT_MORE_INFO_ROLE_ATTR',
2839 text_value=>wf_engine.context_more_info_role,
2840 number_value=>null,
2841 date_value=>null);
2842 create_item_attrib_if_notexist(itemtype=>p_item_type,
2843 itemkey=>p_item_key,
2844 aname=>'HR_CONTEXT_USER_KEY_ATTR',
2845 text_value=>wf_engine.context_user_key,
2846 number_value=>null,
2847 date_value=>null);
2848
2849 create_item_attrib_if_notexist(itemtype=>p_item_type,
2850 itemkey=>p_item_key,
2851 aname=>'HR_CONTEXT_PROXY_ATTR',
2852 text_value=>wf_engine.context_proxy,
2853 number_value=>null,
2854 date_value=>null);
2855
2856 result := 'COMPLETE';
2857 return;
2858 end if;
2859
2860 if (g_debug ) then
2861 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2862 end if;
2863
2864
2865
2866 exception
2867 when others then
2868 if g_debug then
2869 hr_utility.set_location('Error in setRespondedUserContext SQLERRM' ||' '||to_char(SQLCODE),20);
2870 end if;
2871 raise;
2872 end setRespondedUserContext;
2873
2874 procedure submit_for_approval( p_item_type in varchar2,
2875 p_item_key in varchar2,
2876 p_act_id in number,
2877 funmode in varchar2,
2878 result out nocopy varchar2 )
2879
2880 is
2881 -- local variables
2882 c_proc constant varchar2(30) := 'submit_for_approval';
2883 -- Variables required for AME API
2884 c_application_id integer;
2885 c_transaction_id varchar2(25);
2886 c_transaction_type varchar2(25);
2887 c_next_approvers ame_util.approverstable2;
2888 c_approvalprocesscompleteynout ame_util.charType;
2889 l_current_forward_to_username wf_users.name%type;
2890 lv_transaction_status varchar2(100);
2891 lv_approval_required varchar2(100);
2892 lv_result varchar2(100);
2893 lv_comments wf_item_attribute_values.text_value%type;
2894
2895 begin
2896 g_debug := hr_utility.debug_enabled;
2897
2898 if g_debug then
2899 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
2900 end if;
2901 if ( funmode = wf_engine.eng_run ) then
2902 -- Creating attribute to check the new transaction
2903 create_item_attrib_if_notexist(itemtype => p_item_type
2904 ,itemkey => p_item_key
2905 ,aname => 'HR_NEW_TRANSACTION'
2906 ,text_value=>'TIMEOUT ^ APPROVETWICE' --bug 5414392
2907 ,number_value=>null,
2908 date_value=>null
2909 );
2910
2911 create_item_attrib_if_notexist(itemtype => p_item_type
2912 ,itemkey => p_item_key
2913 ,aname => 'SUBMIT_COMMENTS'
2914 ,text_value=>wf_engine.getitemattrtext(p_item_type,p_item_key,'APPROVAL_COMMENT_COPY',true)
2915 ,number_value=>null,
2916 date_value=>null
2917 );
2918 -- check if the transaction needs to be submitted for approval
2919 -- get the transaction status
2920 lv_transaction_status:=wf_engine.getItemAttrText(itemtype=> p_item_type,
2921 itemkey => p_item_key,
2922 aname => 'TRAN_SUBMIT');
2923 if(lv_transaction_status<>'Y') then
2924 -- result := 'COMPLETE:'||'E';
2925 --return;
2926 raise g_transaction_status_invalid;
2927 end if;
2928 if(lv_transaction_status='Y') then
2929 lv_approval_required:= wf_engine.GetItemAttrText(itemtype => p_item_type,
2930 itemkey => p_item_key,
2931 aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG');
2932 lv_approval_required := nvl(lv_approval_required,'N0');
2933 if(lv_approval_required in ('YES_DYNAMIC','YES','Y','YD')) then
2934
2935 -- call the other intialization routines needed for approval
2936 -- notification process
2937 -- PQH_SS_HISTORY.transfer_submit_to_history
2938 if g_debug then
2939 hr_utility.set_location('calling PQH_SS_HISTORY.transfer_submit_to_history', 2);
2940 hr_utility.set_location('p_item_type:'|| p_item_type, 3);
2941 hr_utility.set_location('p_item_key:'|| p_item_key, 4);
2942 end if;
2943 -- PQH_SS_HISTORY.transfer_submit_to_history(p_item_type,p_item_key,p_act_id,funmode,result);
2944 -- use the new history API's
2945 -- add the code plugin transfer history
2946 c_transaction_id := wf_engine.getitemattrnumber(p_item_type,p_item_key,'TRANSACTION_ID');
2947 lv_comments := wf_engine.getitemattrtext(p_item_type,p_item_key,'APPROVAL_COMMENT_COPY');
2948 hr_trans_history_api.archive_submit(c_transaction_id,
2949 null,
2950 fnd_global.user_name,
2951 lv_comments);
2952
2953 -- HR_APPROVAL_WF.INITIALIZE_ITEM_ATTRIBUTES
2954 if g_debug then
2955 hr_utility.set_location('calling HR_APPROVAL_WF.INITIALIZE_ITEM_ATTRIBUTES', 5);
2956 hr_utility.set_location('p_item_type:'|| p_item_type, 6);
2957 hr_utility.set_location('p_item_key:'|| p_item_key, 7);
2958 end if;
2959
2960 HR_DYNAMIC_APPROVAL_WEB.INITIALIZE_ITEM_ATTRIBUTES(p_item_type,p_item_key,null,wf_engine.eng_run,lv_result);
2961 HR_APPROVAL_WF.INITIALIZE_ITEM_ATTRIBUTES(p_item_type,p_item_key,p_act_id,funmode,result);
2962
2963 -- PQH_SS_WORKFLOW.set_image_source
2964 if g_debug then
2965 hr_utility.set_location('calling PQH_SS_WORKFLOW.set_image_source', 8);
2966 hr_utility.set_location('p_item_type:'|| p_item_type, 9);
2967 hr_utility.set_location('p_item_key:'|| p_item_key, 10);
2968 end if;
2969
2970 PQH_SS_WORKFLOW.set_image_source(p_item_type,p_item_key,p_act_id,funmode,result);
2971 result := 'COMPLETE:'||'Y';
2972 end if;
2973
2974 else
2975 result := 'COMPLETE:'||'N';
2976
2977 end if;
2978
2979 end if;
2980
2981 if (g_debug ) then
2982 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
2983 end if;
2984 exception
2985 when others then
2986 if g_debug then
2987 hr_utility.set_location('Error in submit_for_approval SQLERRM' ||' '||to_char(SQLCODE),20);
2988 end if;
2989 raise;
2990 end submit_for_approval;
2991
2992
2993 procedure setSFLResponseContext( p_item_type in varchar2,
2994 p_item_key in varchar2,
2995 p_act_id in number,
2996 funmode in varchar2,
2997 result out nocopy varchar2 )
2998
2999 is
3000 -- local variables
3001 c_proc constant varchar2(30) := 'setSFLResponseContext';
3002 -- Variables required for AME API
3003 c_application_id integer;
3004 c_transaction_id varchar2(25);
3005 c_transaction_type varchar2(25);
3006 c_next_approvers ame_util.approverstable2;
3007 c_approvalprocesscompleteynout ame_util.charType;
3008 l_current_forward_to_username wf_users.name%type;
3009 lv_result_code WF_ITEM_ACTIVITY_STATUSES.activity_result_code%type;
3010 lv_result_display varchar2(250);
3011
3012 begin
3013 g_debug := hr_utility.debug_enabled;
3014
3015 if g_debug then
3016 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3017 end if;
3018
3019 handleArchive(p_item_type,
3020 p_item_key,
3021 p_act_id,
3022 funmode,
3023 result);
3024
3025 if g_debug then
3026 hr_utility.set_location('returning with the result'||result,8);
3027 end if;
3028
3029
3030 if (g_debug ) then
3031 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
3032 end if;
3033
3034 exception
3035 when others then
3036 if g_debug then
3037 hr_utility.set_location('Error in setSFLResponseContext SQLERRM' ||' '||to_char(SQLCODE),20);
3038 end if;
3039 raise;
3040 end setSFLResponseContext;
3041
3042 procedure setRFCResponseContext( p_item_type in varchar2,
3043 p_item_key in varchar2,
3044 p_act_id in number,
3045 funmode in varchar2,
3046 result out nocopy varchar2 )
3047
3048 is
3049 -- local variables
3050 c_proc constant varchar2(30) := 'setRFCResponseContext';
3051 -- Variables required for AME API
3052 c_application_id integer;
3053 c_transaction_id varchar2(25);
3054 c_transaction_type varchar2(25);
3055 c_next_approvers ame_util.approverstable2;
3056 c_approvalprocesscompleteynout ame_util.charType;
3057 l_current_forward_to_username wf_users.name%type;
3058 lv_result_code WF_ITEM_ACTIVITY_STATUSES.activity_result_code%type;
3059 lv_result_display varchar2(250);
3060
3061 CURSOR csr_txn (p_txn_id IN NUMBER)IS
3062 SELECT transaction_ref_table, transaction_ref_id
3063 FROM hr_api_transactions
3064 WHERE transaction_id = p_txn_id;
3065 l_transaction_ref_table VARCHAR2(50);
3066 l_transaction_ref_id NUMBER;
3067
3068 begin
3069 g_debug := hr_utility.debug_enabled;
3070
3071 if g_debug then
3072 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3073 end if;
3074
3075 --sturlapa coding starts for bug 3866581
3076 begin
3077 select text_value
3078 into lv_result_code
3079 from wf_notification_attributes
3080 where notification_id=wf_engine.context_nid
3081 and name='RESULT';
3082
3083 if(funmode='RESPOND' and lv_result_code='RESUBMIT') Then
3084
3085 WF_ENGINE.SetItemAttrText(p_item_type,p_item_key,'WF_NOTE',wf_engine.GetItemAttrText( p_item_type
3086 ,p_item_key
3087 ,'APPROVAL_COMMENT_COPY'));
3088
3089 end if;
3090
3091 exception
3092 when others then
3093 null;
3094 end;
3095 --sturlapa coding ends for bug 3866581
3096
3097 --- added by KMG
3098 c_transaction_id := wf_engine.getitemattrnumber(p_item_type,p_item_key,'TRANSACTION_ID');
3099
3100 OPEN csr_txn(c_transaction_id);
3101 FETCH csr_txn INTO l_transaction_ref_table,l_transaction_ref_id;
3102 CLOSE csr_txn;
3103 hr_utility.trace('txn_ref_table: '||l_transaction_ref_table);
3104 IF NVL(l_transaction_ref_table,'XXX') = 'PER_APPRAISALS' and lv_result_code = 'DEL' THEN
3105 hr_utility.trace('returning with resubmit as we donot want RFC deletion for appraisal');
3106 result := 'COMPLETE:XYZ'; --so that this takes the default option in the WF
3107 RETURN;
3108 END IF;
3109 ---END Changes by KMG
3110
3111 handleArchive(p_item_type,
3112 p_item_key,
3113 p_act_id,
3114 funmode,
3115 result);
3116 if g_debug then
3117 hr_utility.set_location('returning with the result'||result,8);
3118 end if;
3119
3120
3121
3122
3123 if (g_debug ) then
3124 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
3125 end if;
3126
3127 exception
3128 when others then
3129 if g_debug then
3130 hr_utility.set_location('Error in setRFCResponseContext SQLERRM' ||' '||to_char(SQLCODE),20);
3131 end if;
3132 raise;
3133 end setRFCResponseContext;
3134
3135 --bug 5414392
3136
3137 procedure setNtfTransferCtx( p_item_type in varchar2,
3138 p_item_key in varchar2,
3139 p_act_id in number,
3140 funmode in varchar2,
3141 result out nocopy varchar2 )
3142
3143 is
3144 -- local variables
3145 c_proc constant varchar2(30) := 'setNtfTransferCtx';
3146 -- Variables required for AME API
3147 l_forward_to_person_id per_people_f.person_id%type;
3148 c_application_id integer;
3149 c_transaction_id varchar2(25);
3150 c_transaction_type varchar2(25);
3151 c_next_approvers ame_util.approverstable2;
3152 c_approvalprocesscompleteynout ame_util.charType;
3153 l_current_forward_to_username wf_users.name%type;
3154 rec_forwardee ame_util.approverRecord2;
3155
3156 l_new_fwd_person_id per_people_f.person_id%type;
3157 l_new_orig_system varchar2(30);
3158 l_new_fwd_display_name varchar2(360);
3159 l_is_parallel varchar2(10) := null;
3160
3161 cursor get_person_info is
3162 select orig_system,orig_system_id,display_name from wf_roles where name = wf_engine.context_new_role;
3163
3164 /* cursor get_name_info is
3165 select last_name,first_name from per_all_people_f where
3166 person_id = l_new_fwd_person_id;*/
3167
3168 begin
3169
3170 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3171
3172 if ( funmode = 'TRANSFER' ) then
3173 -- TRANSFER - When a notification recipient transfers the notification.
3174 -- check if it is AME or custom approvals
3175 c_application_id :=nvl(wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
3176 itemkey => p_item_key,
3177 aname => 'HR_AME_APP_ID_ATTR'),800);
3178
3179 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
3180 itemkey => p_item_key,
3181 aname => 'TRANSACTION_ID');
3182
3183 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3184 itemkey => p_item_key,
3185 aname => 'HR_AME_TRAN_TYPE_ATTR');
3186
3187 if(c_transaction_type is not null) then
3188 hr_utility.set_location('In(if ( if(c_transaction_type is not null))): '|| c_proc,2);
3189
3190 /*
3191 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => p_item_type
3192 ,itemkey => p_item_key
3193 ,aname => 'HR_CONTEXT_USER_ATTR');
3194
3195 */
3196
3197 --Start - Fix for Bug 14794335
3198 l_is_parallel := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3199 itemkey => p_item_key,
3200 aname => 'HR_PARALLEL_ATTR',
3201 ignore_notfound=>true);
3202 if(l_is_parallel = 'PARALLEL') then
3203 l_current_forward_to_username := wf_engine.context_user;
3204 else
3205 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => p_item_type
3206 ,itemkey => p_item_key
3207 ,aname => 'FORWARD_TO_USERNAME');
3208 end if;
3209 --End - Fix for Bug 14794335
3210
3211
3212 l_current_forward_to_username := nvl(l_current_forward_to_username,wf_engine.GetItemAttrText(itemtype => p_item_type ,
3213 itemkey => p_item_key,
3214 aname => 'RETURN_TO_USERNAME'));
3215 if g_debug then
3216 hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 3);
3217 hr_utility.set_location('c_application_id:'|| c_application_id, 4);
3218 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 5);
3219 hr_utility.set_location('approvalStatusIn:'|| ame_util.approvedStatus, 6);
3220 hr_utility.set_location('approverNameIn:'|| l_current_forward_to_username, 7);
3221 hr_utility.set_location('new approver:'|| wf_engine.context_new_role, 7);
3222 end if;
3223
3224 begin
3225
3226 open get_person_info;
3227 fetch get_person_info into l_new_orig_system,l_new_fwd_person_id,l_new_fwd_display_name;
3228 CLOSe get_person_info;
3229
3230 rec_forwardee.name:= wf_engine.context_new_role;
3231 rec_forwardee.orig_system :=l_new_orig_system;
3232 rec_forwardee.orig_system_id :=l_new_fwd_person_id;
3233 rec_forwardee.display_name :=l_new_fwd_display_name;
3234 --rec_forwardee.api_insertion:='Y';
3235
3236 hr_utility.set_location('new approver person id:'|| l_new_fwd_person_id, 7);
3237
3238
3239
3240 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
3241 transactionTypeIn =>c_transaction_type,
3242 transactionIdIn=>c_transaction_id,
3243 approvalStatusIn =>ame_util.forwardStatus,
3244 approverNameIn =>l_current_forward_to_username,
3245 itemClassIn => null,
3246 itemIdIn =>null,
3247 actionTypeIdIn=> null,
3248 groupOrChainIdIn =>null,
3249 occurrenceIn =>null,
3250 forwardeeIn =>rec_forwardee,
3251 updateItemIn =>false);
3252
3253 --reset wf attributes
3254
3255 wf_engine.setItemAttrNumber(p_item_type,p_item_key,'FORWARD_TO_PERSON_ID',rec_forwardee.orig_system_id);
3256 wf_engine.setItemAttrText(p_item_type,p_item_key,'FORWARD_TO_USERNAME',wf_engine.context_new_role);
3257 wf_engine.setItemAttrText(p_item_type,p_item_key,'FORWARD_TO_DISPLAY_NAME',wf_directory.GetRoleDisplayName(wf_engine.context_new_role));
3258 wf_engine.setItemAttrText(p_item_type,p_item_key,'FORWARD_TO_USER',getToString(p_item_key) || wf_directory.GetRoleDisplayName(wf_engine.context_new_role));
3259
3260 if g_debug then
3261 hr_utility.set_location('calling ame_api2.getNextApprovers4 ',4);
3262 hr_utility.set_location('c_application_id:'||c_application_id,5);
3263 hr_utility.set_location('c_transaction_type:'|| c_transaction_type,6);
3264 hr_utility.set_location('c_transaction_id:'|| c_transaction_id,7);
3265 hr_utility.set_location('flagApproversAsNotifiedIn:'|| ame_util.booleanFalse,8);
3266 end if;
3267
3268 ame_api2.getNextApprovers4
3269 (applicationIdIn => c_application_id
3270 ,transactionTypeIn => c_transaction_type
3271 ,transactionIdIn => c_transaction_id
3272 ,flagApproversAsNotifiedIn=>ame_util.booleanFalse
3273 ,approvalProcessCompleteYNOut => c_approvalprocesscompleteynout
3274 ,nextApproversOut => c_next_approvers);
3275
3276
3277 exception
3278 when others then
3279 if g_debug then
3280 hr_utility.set_location('Error in updateApproveStatus SQLERRM' ||' '||to_char(SQLCODE),10);
3281 end if;
3282
3283 result := 'COMPLETE:' || 'ERROR';
3284 return;
3285 end ;
3286
3287 if g_debug then
3288 hr_utility.set_location('returned from calling ame_api2.updateApprovalStatus2', 8);
3289 end if;
3290
3291
3292
3293 result := wf_engine.eng_trans_default;
3294 else
3295 null;
3296 end if;
3297
3298
3299 end if;
3300
3301
3302 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 20);
3303 -- hr_utility.trace_off;
3304 exception
3305 when others then
3306
3307 hr_utility.set_location('Error in setNtfTransferCtx SQLERRM' ||' '||to_char(SQLCODE),30);
3308
3309 raise;
3310 end setNtfTransferCtx;
3311
3312
3313 procedure setApproverResponseContext( p_item_type in varchar2,
3314 p_item_key in varchar2,
3315 p_act_id in number,
3316 funmode in varchar2,
3317 result out nocopy varchar2 )
3318
3319 is
3320 -- local variables
3321 c_proc constant varchar2(30) := 'setApproverResponseContext';
3322 -- Variables required for AME API
3323 c_application_id integer;
3324 c_transaction_id varchar2(25);
3325 c_transaction_type varchar2(25);
3326 c_next_approvers ame_util.approverstable2;
3327 c_approvalprocesscompleteynout ame_util.charType;
3328 l_current_forward_to_username wf_users.name%type;
3329 lv_result_code WF_ITEM_ACTIVITY_STATUSES.activity_result_code%type;
3330 lv_result_display varchar2(250);
3331 lv_test_result_code varchar2(250);
3332 lv_comments wf_item_attribute_values.text_value%type;
3333 lv_new_transaction varchar2(240);
3334 l_cpersonId VARCHAR2(30); --added for bug 13485539
3335 l_is_parallel varchar2(10) := null;
3336 prll_ntf varchar2(600) := null;
3337
3338 l_notification_id number := null;
3339
3340
3341 begin
3342
3343 g_debug := hr_utility.debug_enabled;
3344
3345
3346 if g_debug then
3347 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3348 end if;
3349
3350 -- added for bug 13485539
3351 if hr_multi_tenancy_pkg.is_multi_tenant_system then
3352 l_cpersonId := NVL( wf_engine.getItemAttrText (
3353 itemtype => p_item_type
3354 ,itemkey => p_item_key
3355 ,aname => 'CREATOR_PERSON_ID'),-1);
3356
3357 hr_multi_tenancy_pkg.set_context_for_person(l_cpersonId);
3358 -- hr_utility.set_location('After callto multi DBG_'||sa_session.row_label('HR_ENTERPRISE_POLICY')||':Entering',5);
3359 end if;
3360 -- End of addition for bug 13485539
3361 -- capture the approver login context information
3362
3363 if ( funmode = 'RESPOND' OR funmode = 'TIMEOUT') then
3364
3365 hr_approval_ss.setrespondedusercontext(p_item_type,p_item_key,p_act_id,funmode,result);
3366 end if;
3367
3368 -- bug 5414392
3369
3370 if ( funmode = 'TRANSFER' ) then
3371
3372 l_is_parallel := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3373 itemkey => p_item_key,
3374 aname => 'HR_PARALLEL_ATTR',
3375 ignore_notfound=>true);
3376 if(l_is_parallel = 'PARALLEL') then
3377
3378
3379 if g_debug then
3380 hr_utility.set_location('Approval mode is parallel', 1);
3381 end if;
3382
3383 l_notification_id := wf_engine.context_nid;
3384 l_current_forward_to_username:= wf_engine.context_original_recipient;
3385
3386 prll_ntf := wf_engine.getitemattrtext(p_item_type,
3387 p_item_key,
3388 'PRLL_TRNSFR_DET',true);
3389
3390 create_item_attrib_if_notexist(itemtype => p_item_type
3391 ,itemkey => p_item_key
3392 ,aname => 'PRLL_TRNSFR_DET'
3393 ,text_value=> prll_ntf || l_notification_id || ':' || l_current_forward_to_username || '|'
3394 ,number_value=>null,
3395 date_value=>null
3396 );
3397
3398
3399 else
3400 if g_debug then
3401 hr_utility.set_location('Approval mode is serial', 1);
3402 end if;
3403 lv_new_transaction := wf_engine.getitemattrtext(p_item_type,
3404 p_item_key,
3405 'HR_NEW_TRANSACTION',true);
3406
3407
3408
3409 if( INSTR(lv_new_transaction,'APPROVETWICE') > 0) then
3410 setNtfTransferCtx(p_item_type,p_item_key,p_act_id,funmode,result);
3411 end if;
3412
3413
3414 end if;
3415 end if;
3416
3417
3418 -- archive the user action
3419 handleArchive(p_item_type,
3420 p_item_key,
3421 p_act_id,
3422 funmode,
3423 result);
3424
3425
3426
3427 if (g_debug ) then
3428 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
3429 end if;
3430 exception
3431 when others then
3432 if g_debug then
3433 hr_utility.set_location('Error in setApproverResponseContext SQLERRM' ||' '||to_char(SQLCODE),20);
3434 end if;
3435
3436 raise;
3437 end setApproverResponseContext;
3438
3439
3440 procedure reset_ame_to_rfc_state(p_item_type in varchar2,
3441 p_item_key in varchar2)
3442 is
3443 -- Variables required for AME API
3444 c_application_id integer;
3445 c_transaction_id varchar2(25);
3446 c_transaction_type varchar2(25);
3447 c_all_approvers ame_util.approverstable2;--ame_util.approversTable;
3448 c_approvalprocesscompleteynout ame_util.charType;
3449
3450 c_creator_user wf_users.name%Type;
3451 c_return_user wf_users.name%Type;
3452 c_match_found varchar2(1);
3453 c_rfc_initiator varchar2(1);
3454 notification_rec ame_util2.notificationRecord;
3455 c_rfc_user wf_users.name%Type;
3456 l_is_parallel varchar2(10) := null;
3457
3458 l_proc constant varchar2(100) := g_package || ' reset_ame_to_rfc_state';
3459 begin
3460
3461 g_debug := hr_utility.debug_enabled;
3462 c_match_found := 'N';
3463
3464 IF g_debug THEN
3465 hr_utility.set_location('Entering:'||l_proc, 1);
3466 END IF;
3467
3468
3469 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
3470 itemkey => p_item_key,
3471 aname => 'TRANSACTION_ID',
3472 ignore_notfound=>true);
3473 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3474 itemkey => p_item_key,
3475 aname => 'HR_AME_TRAN_TYPE_ATTR',
3476 ignore_notfound=>true);
3477 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
3478 itemkey => p_item_key,
3479 aname => 'HR_AME_APP_ID_ATTR',
3480 ignore_notfound=>true);
3481 c_application_id := nvl(c_application_id,800);
3482
3483 notification_rec.notification_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
3484 itemkey => p_item_key,
3485 aname => 'HR_CONTEXT_NID_ATTR');
3486 notification_rec.user_comments := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3487 itemkey => p_item_key,
3488 aname => 'NOTE_FROM_APPR');
3489
3490 if(c_transaction_type is not null) then
3491 if g_debug then
3492 hr_utility.set_location('calling ame_api2.getAllApprovers7', 2);
3493 hr_utility.set_location('c_application_id:'|| c_application_id, 3);
3494 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 4);
3495 hr_utility.set_location('c_transaction_id:'|| c_transaction_id, 5);
3496 end if;
3497
3498 ame_api2.getAllApprovers7(applicationIdIn =>c_application_id,
3499 transactionTypeIn =>c_transaction_type,
3500 transactionIdIn => c_transaction_id,
3501 approvalProcessCompleteYNOut =>c_approvalprocesscompleteynout,
3502 approversOut =>c_all_approvers);
3503 c_creator_user := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3504 itemkey => p_item_key,
3505 aname => 'CREATOR_PERSON_USERNAME');
3506 c_return_user := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3507 itemkey => p_item_key,
3508 aname => 'RETURN_TO_USERNAME',
3509 ignore_notfound=>true);
3510
3511
3512 l_is_parallel := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3513 itemkey => p_item_key,
3514 aname => 'HR_PARALLEL_ATTR',
3515 ignore_notfound=>true);
3516
3517 if(l_is_parallel = 'PARALLEL') then
3518 c_rfc_user := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3519 itemkey => p_item_key,
3520 aname => 'FORWARD_FROM_ROLE_NAME',
3521 ignore_notfound=>true);
3522 else
3523 c_rfc_user := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3524 itemkey => p_item_key,
3525 aname => 'FORWARD_TO_USERNAME',
3526 ignore_notfound=>true);
3527 end if;
3528
3529
3530
3531
3532 hr_utility.set_location('c_creator_user : ' || c_creator_user,111);
3533 hr_utility.set_location('c_return_user : ' || c_return_user,111);
3534
3535 IF ( c_return_user IS NULL OR c_creator_user = c_return_user ) THEN
3536 c_rfc_initiator := 'Y';
3537 hr_utility.set_location('c_rfc_initiator is Y',111);
3538 ELSE
3539 c_rfc_initiator := 'N';
3540 hr_utility.set_location('c_rfc_initiator is N',111);
3541 END IF;
3542
3543 hr_utility.set_location('Entering For Loop',111);
3544 for i in 1..c_all_approvers.count loop
3545
3546 hr_utility.set_location('c_all_approvers(i).name : ' || c_all_approvers(i).name,111);
3547 hr_utility.set_location('c_rfc_initiator : ' || c_rfc_initiator,111);
3548
3549 IF ( c_rfc_initiator = 'N' AND c_return_user = c_all_approvers(i).name ) THEN
3550 c_match_found := 'Y';
3551 END IF;
3552 hr_utility.set_location('c_match_found :' || c_match_found,111);
3553
3554 IF (c_rfc_initiator = 'Y' OR (c_rfc_initiator = 'N' AND c_match_found = 'Y' ) ) THEN
3555
3556 hr_utility.set_location('calling ame_api2.updateApprovalStatus2', 2);
3557 hr_utility.set_location('c_application_id:'|| c_application_id, 3);
3558 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 4);
3559 hr_utility.set_location('c_transaction_id:'|| c_transaction_id, 5);
3560 hr_utility.set_location('approvalStatusIn:'|| null, 6);
3561 hr_utility.set_location('approverNameIn:'|| c_all_approvers(i).name, 7);
3562 hr_utility.set_location('c_rfc_user:'|| c_rfc_user, 8);
3563
3564
3565 IF (c_rfc_user = c_all_approvers(i).name) THEN
3566
3567 ame_api6.updateApprovalStatus2(applicationIdIn=>c_application_id,
3568 transactionTypeIn =>c_transaction_type,
3569 transactionIdIn=>c_transaction_id,
3570 approvalStatusIn =>null,
3571 approverNameIn =>c_all_approvers(i).name,
3572 itemClassIn => c_all_approvers(i).ITEM_CLASS,
3573 itemIdIn =>c_all_approvers(i).ITEM_ID,
3574 actionTypeIdIn=> c_all_approvers(i).ACTION_TYPE_ID,
3575 groupOrChainIdIn =>c_all_approvers(i).GROUP_OR_CHAIN_ID,
3576 occurrenceIn =>c_all_approvers(i).OCCURRENCE,
3577 notificationIn => notification_rec,
3578 forwardeeIn =>ame_util.emptyApproverRecord2,
3579 updateItemIn =>false);
3580 ELSE
3581
3582 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
3583 transactionTypeIn =>c_transaction_type,
3584 transactionIdIn=>c_transaction_id,
3585 approvalStatusIn =>null,
3586 approverNameIn =>c_all_approvers(i).name,
3587 itemClassIn => c_all_approvers(i).ITEM_CLASS,
3588 itemIdIn =>c_all_approvers(i).ITEM_ID,
3589 actionTypeIdIn=> c_all_approvers(i).ACTION_TYPE_ID,
3590 groupOrChainIdIn =>c_all_approvers(i).GROUP_OR_CHAIN_ID,
3591 occurrenceIn =>c_all_approvers(i).OCCURRENCE,
3592 forwardeeIn =>ame_util.emptyApproverRecord2,
3593 updateItemIn =>false);
3594 END IF;
3595 end if;
3596
3597 end loop;
3598 end if;
3599
3600 IF g_debug THEN
3601 hr_utility.set_location('Leaving:'|| l_proc, 35);
3602 END IF;
3603
3604 EXCEPTION
3605 when others then
3606 hr_utility.set_location('EXCEPTION: '|| l_proc,560);
3607 hr_utility.trace(' exception in '||l_proc||' : ' || sqlerrm);
3608
3609 Wf_Core.Context(g_package, l_proc, p_item_type, p_item_key);
3610 raise;
3611 end reset_ame_to_rfc_state;
3612
3613 procedure reset_approval_rfc_data(p_item_type in varchar2,
3614 p_item_key in varchar2) is
3615
3616 -- local variables
3617 c_proc constant varchar2(60) := 'reset_approval_rfc_data';
3618 c_return_user wf_users.name%Type;
3619 c_creator_user wf_users.name%Type;
3620 l_approvalProcessVersion varchar2(10);
3621 l_curr_approver_role wf_roles.name%type;
3622 c_return_user_role_info_tbl wf_directory.wf_local_roles_tbl_type;
3623 l_curr_approver_role_info_tbl wf_directory.wf_local_roles_tbl_type;
3624 l_is_parallel varchar2(10) := null;
3625
3626 begin
3627 g_debug := hr_utility.debug_enabled;
3628 if g_debug then
3629 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3630 end if;
3631
3632 -- functional logic
3633 -- case : notification sent to M3 for approval
3634 -- M3 RFC to M1 or M0(initiating Manager)
3635 -- All FORWARD_TO should refelect M1 or M0
3636 -- All FORWARD_FROM should reflect M3
3637
3638 -- reset the AME approval state
3639 reset_ame_to_rfc_state(p_item_type,p_item_key);
3640
3641 -- reset the item attrtibutes
3642 c_return_user:= wf_engine.GetItemAttrText(itemtype => p_item_type ,
3643 itemkey => p_item_key,
3644 aname => 'RETURN_TO_USERNAME',
3645 ignore_notfound=>true);
3646 c_creator_user := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3647 itemkey => p_item_key,
3648 aname => 'CREATOR_PERSON_USERNAME');
3649
3650 c_return_user := nvl(c_return_user,c_creator_user);
3651
3652 hr_utility.set_location('c_return_user' || c_return_user,111);
3653 hr_utility.set_location('c_creator_user' || c_creator_user,111);
3654
3655
3656 -- get the return to user orig system and sys id
3657 wf_directory.GetRoleInfo2(c_return_user,c_return_user_role_info_tbl);
3658
3659 l_is_parallel := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3660 itemkey => p_item_key,
3661 aname => 'HR_PARALLEL_ATTR',
3662 ignore_notfound=>true);
3663
3664 if(l_is_parallel = 'PARALLEL') then
3665 l_curr_approver_role := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3666 itemkey => p_item_key,
3667 aname => 'FORWARD_FROM_ROLE_NAME',
3668 ignore_notfound=>true);
3669 wf_engine.SetItemAttrText
3670 (itemtype => p_item_type
3671 ,itemkey => p_item_key
3672 ,aname => 'HR_PARALLEL_ATTR'
3673 ,avalue => 'SERIAL');
3674
3675 else
3676 l_curr_approver_role := wf_engine.GetItemAttrText(itemtype => p_item_type ,
3677 itemkey => p_item_key,
3678 aname => 'FORWARD_TO_USERNAME',
3679 ignore_notfound=>true);
3680 end if;
3681
3682
3683 ---- get the current approver orig system and sys id
3684
3685 hr_utility.set_location('l_curr_approver_role' || l_curr_approver_role,111);
3686
3687 wf_directory.GetRoleInfo2(l_curr_approver_role,l_curr_approver_role_info_tbl);
3688
3689 hr_utility.set_location('l_curr_approver_role_info_tbl(1).display_name' || l_curr_approver_role_info_tbl(1).display_name,111);
3690 hr_utility.set_location('c_return_user_role_info_tbl(1).display_name' || c_return_user_role_info_tbl(1).display_name,111);
3691
3692 -- FORWARD_FROM_DISPLAY_NAME
3693 -- set the attribute value to null
3694 create_item_attrib_if_notexist(itemtype => p_item_type
3695 ,itemkey => p_item_key
3696 ,aname => 'FORWARD_FROM_DISPLAY_NAME'
3697 ,text_value=>l_curr_approver_role_info_tbl(1).display_name
3698 ,number_value=>null,
3699 date_value=>null
3700 );
3701
3702 -- FORWARD_FROM_USERNAME
3703 -- set the attribute value to null
3704 create_item_attrib_if_notexist(itemtype => p_item_type
3705 ,itemkey => p_item_key
3706 ,aname => 'FORWARD_FROM_USERNAME'
3707 ,text_value=>l_curr_approver_role
3708 ,number_value=>null,
3709 date_value=>null
3710 );
3711
3712
3713 -- FORWARD_FROM_PERSON_ID
3714 create_item_attrib_if_notexist(itemtype => p_item_type
3715 ,itemkey => p_item_key
3716 ,aname => 'FORWARD_FROM_PERSON_ID'
3717 ,text_value=>null
3718 ,number_value=>l_curr_approver_role_info_tbl(1).orig_system_id,
3719 date_value=>null
3720 );
3721 -- FORWARD_TO_DISPLAY_NAME
3722 create_item_attrib_if_notexist(itemtype => p_item_type
3723 ,itemkey => p_item_key
3724 ,aname => 'FORWARD_TO_DISPLAY_NAME'
3725 ,text_value=>c_return_user_role_info_tbl(1).display_name
3726 ,number_value=>null,
3727 date_value=>null
3728 );
3729
3730 create_item_attrib_if_notexist(itemtype => p_item_type
3731 ,itemkey => p_item_key
3732 ,aname => 'FORWARD_TO_USER'
3733 ,text_value=> getToString(p_item_key) || c_return_user_role_info_tbl(1).display_name
3734 ,number_value=>null,
3735 date_value=>null
3736 );
3737
3738 -- FORWARD_TO_USERNAME
3739 create_item_attrib_if_notexist(itemtype => p_item_type
3740 ,itemkey => p_item_key
3741 ,aname => 'FORWARD_TO_USERNAME'
3742 ,text_value=>c_return_user
3743 ,number_value=>null,
3744 date_value=>null
3745 );
3746
3747 -- FORWARD_TO_PERSON_ID
3748 create_item_attrib_if_notexist(itemtype => p_item_type
3749 ,itemkey => p_item_key
3750 ,aname => 'FORWARD_TO_PERSON_ID'
3751 ,text_value=>null
3752 ,number_value=>c_return_user_role_info_tbl(1).orig_system_id,
3753 date_value=>null
3754 );
3755
3756 create_item_attrib_if_notexist(itemtype => p_item_type
3757 ,itemkey => p_item_key
3758 ,aname => 'FORWARD_FROM_ORIG_SYS_ATTR'
3759 ,text_value=>l_curr_approver_role_info_tbl(1).orig_system
3760 ,number_value=>null,
3761 date_value=>null
3762 );
3763
3764 create_item_attrib_if_notexist(itemtype => p_item_type
3765 ,itemkey => p_item_key
3766 ,aname => 'FORWARD_TO_ORIG_SYS_ATTR'
3767 ,text_value=>c_return_user_role_info_tbl(1).orig_system
3768 ,number_value=>null,
3769 date_value=>null
3770 );
3771
3772 /* Bug 2940951: No need to reset current approver index and last default approver in case
3773 * new approval process is used and the non-AME approval is used
3774 * as the two attributes are set when pqh_ss_workflow.return_for_correction is invoked.
3775 * CAUTION: IF this procedure is invoked from somewhere else (apart from RFC) then this needs
3776 * to be checked for that condition too.
3777 */
3778
3779 l_approvalProcessVersion := wf_engine.GetItemAttrText(
3780 itemtype => p_item_Type,
3781 itemkey => p_item_Key,
3782 aname => 'HR_APPROVAL_PRC_VERSION',
3783 ignore_notfound=>true);
3784
3785 IF ( NVL(l_approvalProcessversion,'X') <> 'V5' OR
3786 wf_engine.GetItemAttrText(
3787 itemtype => p_item_Type, itemkey => p_item_Key,
3788 aname => 'HR_AME_TRAN_TYPE_ATTR') IS NOT NULL) THEN
3789
3790 -- CURRENT_APPROVER_INDEX
3791 create_item_attrib_if_notexist(itemtype => p_item_type
3792 ,itemkey => p_item_key
3793 ,aname => 'CURRENT_APPROVER_INDEX'
3794 ,text_value=>null
3795 ,number_value=>null,
3796 date_value=>null
3797 );
3798 -- 'LAST_DEFAULT_APPROVER'
3799 create_item_attrib_if_notexist(itemtype => p_item_type
3800 ,itemkey => p_item_key
3801 ,aname => 'LAST_DEFAULT_APPROVER'
3802 ,text_value=>null
3803 ,number_value=>null,
3804 date_value=>null
3805 );
3806 END IF;
3807 -- CURRENT_DEF_APPR_INDEX
3808 create_item_attrib_if_notexist(itemtype => p_item_type
3809 ,itemkey => p_item_key
3810 ,aname => 'CURRENT_DEF_APPR_INDEX'
3811 ,text_value=>null
3812 ,number_value=>0,
3813 date_value=>null
3814 );
3815
3816 if (g_debug ) then
3817 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
3818 end if;
3819
3820 exception
3821 when others then
3822 if g_debug then
3823 hr_utility.set_location('Error in reset_approval_rfc_data SQLERRM' ||' '||to_char(SQLCODE),20);
3824 end if;
3825 raise;
3826 end reset_approval_rfc_data;
3827
3828
3829
3830 procedure processRFC( p_item_type in varchar2,
3831 p_item_key in varchar2,
3832 p_act_id in number,
3833 funmode in varchar2,
3834 result out nocopy varchar2 )
3835
3836 is
3837 -- local variables
3838 c_proc constant varchar2(30) := 'processRFC';
3839 -- Variables required for AME API
3840 c_application_id integer;
3841 c_transaction_id varchar2(25);
3842 c_transaction_type varchar2(25);
3843 c_next_approvers ame_util.approverstable2;
3844 c_approvalprocesscompleteynout ame_util.charType;
3845 l_current_forward_to_username wf_users.name%type;
3846 lv_creatorUserName wf_roles.name%type;
3847 lv_rfcUserName wf_roles.name%type;
3848 lv_customRFC varchar2(5);
3849
3850 lv_dynamicQuery varchar2(4000) ;
3851 lv_queryProcedure varchar2(4000);
3852 begin
3853
3854
3855 g_debug := hr_utility.debug_enabled;
3856 if g_debug then
3857 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3858 end if;
3859 if ( funmode = wf_engine.eng_run ) then
3860
3861 PQH_SS_WORKFLOW.set_txn_rfc_status(p_item_type,p_item_key,p_act_id,funmode,result);
3862 -- fix for bug 4454439
3863 begin
3864 -- re-intialize the performer roles
3865 hr_approval_ss.reinitperformerroles(p_notification_id=>null
3866 ,p_transaction_id=>null
3867 ,p_item_type=>p_item_type
3868 ,p_item_key=>p_item_key);
3869
3870 if hr_workflow_ss.getOrganizationManagersubject(p_item_type,p_item_key) is not null then
3871 wf_engine.setItemAttrText (
3872 itemtype => p_item_type
3873 ,itemkey => p_item_Key
3874 ,aname => 'CURRENT_PERSON_DISPLAY_NAME'
3875 ,avalue => hr_workflow_ss.getOrganizationManagersubject(p_item_type,p_item_key));
3876 end if;
3877 exception
3878 when others then
3879 null;
3880 end;
3881
3882 -- check if we have product overwrite which does not allow RFC and
3883 -- action would default to delete the transaction
3884 -- HR_CUSTOM_RETURN_FOR_CORR
3885 if g_debug then
3886 hr_utility.set_location('checking product specific custom RFC',2);
3887 end if;
3888 lv_customRFC:=wf_engine.getitemattrtext(p_item_type,p_item_key,'HR_CUSTOM_RETURN_FOR_CORR',true);
3889 lv_customRFC:= nvl(lv_customRFC,'N');
3890 if g_debug then
3891 hr_utility.set_location('lv_customRFC:'||lv_customRFC,3);
3892 end if;
3893
3894 if(lv_customRFC='Y') then
3895 -- no more processing return to delete the transaction
3896 result := wf_engine.eng_completed||':N';
3897 return;
3898 end if;
3899
3900 -- call PQH_SS_HISTORY.transfer_approval_to_history
3901 if g_debug then
3902 hr_utility.set_location('calling PQH_SS_WORKFLOW.set_txn_rfc_status',3);
3903 hr_utility.set_location('p_item_type:'|| p_item_type,4);
3904 hr_utility.set_location('p_item_key:'|| p_item_key,5);
3905 end if;
3906
3907 PQH_SS_WORKFLOW.set_txn_rfc_status(p_item_type,p_item_key,p_act_id,funmode,result);
3908
3909 if g_debug then
3910 hr_utility.set_location('calling reset_approval_rfc_data',6);
3911 hr_utility.set_location('p_item_type:'|| p_item_type,7);
3912 hr_utility.set_location('p_item_key:'|| p_item_key,8);
3913 end if;
3914
3915 begin
3916 hr_utility.set_location('Calling reset_approval_rfc_data',111);
3917 reset_approval_rfc_data(p_item_type,p_item_key);
3918 exception
3919 when others then
3920
3921 handleApprovalErrors(p_item_type, p_item_key, sqlerrm);
3922 result := 'COMPLETE:'||'ERROR';
3923 return;
3924
3925 end;
3926
3927
3928
3929 --
3930 begin
3931 -- finally see if we have any module specific call backs
3932 -- 'HR_RFC_CB_ATTR'
3933 lv_queryProcedure := wf_engine.getitemattrtext(p_item_type,p_item_key,'HR_RFC_CB_ATTR',true);
3934
3935 if(lv_queryProcedure is not null or lv_queryProcedure<>'') then
3936
3937 if g_debug then
3938 hr_utility.set_location('Calling queryProcedure: '||lv_queryProcedure, 20);
3939 hr_utility.set_location('p_item_type: '|| p_item_type, 21);
3940 hr_utility.set_location('p_item_key: '|| p_item_key, 22);
3941 hr_utility.set_location('p_act_id: '|| p_act_id, 23);
3942 end if;
3943
3944 lv_dynamicQuery :=
3945 'begin ' ||
3946 lv_queryProcedure ||
3947 '(:itemTypeIn, :itemKeyIn, :actIdIn, :funmodeIn, :resultOut); end;';
3948 execute immediate lv_dynamicQuery
3949 using
3950 in p_item_type,
3951 in p_item_key,
3952 in p_act_id,
3953 in funmode,
3954 in out result;
3955 if g_debug then
3956 hr_utility.set_location('After queryProcedure: '|| lv_queryProcedure, 40);
3957 hr_utility.set_location('result: '|| result, 41);
3958 end if;
3959
3960 end if;
3961 exception
3962 when others then
3963 null; -- raise ???
3964 end;
3965
3966 -- check if the RFC is to Initiator or Approver
3967 -- CREATOR_PERSON_USERNAME
3968 lv_creatorUserName:=wf_engine.getitemattrtext(p_item_type,p_item_key,'CREATOR_PERSON_USERNAME',true);
3969 -- RETURN_TO_USERNAME
3970 lv_rfcUserName:=wf_engine.getitemattrtext(p_item_type,p_item_key,'RETURN_TO_USERNAME',true);
3971 -- Compare
3972
3973 if (lv_creatorUserName is null or lv_rfcUserName is null) then
3974 result := wf_engine.eng_completed||':YES_APPROVER';
3975 elsif (lv_creatorUserName < lv_rfcUserName) then
3976 result := wf_engine.eng_completed||':YES_APPROVER';
3977 elsif (lv_creatorUserName > lv_rfcUserName) then
3978 result := wf_engine.eng_completed||':YES_APPROVER';
3979 elsif (lv_creatorUserName = lv_rfcUserName) then
3980 result := wf_engine.eng_completed||':YES_INIT';
3981 end if;
3982
3983 if g_debug then
3984 hr_utility.set_location('leaving with resultcode :'|| result,9);
3985 end if;
3986
3987
3988 end if;
3989
3990 if (g_debug ) then
3991 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
3992 end if;
3993 exception
3994 when others then
3995 if g_debug then
3996 hr_utility.set_location('Error in processRFC SQLERRM' ||' '||to_char(SQLCODE),20);
3997 end if;
3998 raise;
3999 end processRFC;
4000
4001
4002 function getRoleDisplayName(p_user_name in varchar2,
4003 p_orig_system in varchar2,
4004 p_orig_system_id in number)
4005 return varchar2 is
4006 -- local variables
4007 c_proc constant varchar2(30) := 'processRFC';
4008 lv_role_displayName wf_users.display_name%type;
4009 lv_roleName wf_users.name%type;
4010 begin
4011 g_debug := hr_utility.debug_enabled;
4012 if g_debug then
4013 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4014 end if;
4015 if(p_orig_system is null or p_orig_system_id is null) then
4016 lv_role_displayName :=wf_directory.getroledisplayname(p_user_name);
4017 else
4018 wf_directory.GetRoleName
4019 (p_orig_system => p_orig_system
4020 ,p_orig_system_id => p_orig_system_id
4021 ,p_name => lv_roleName
4022 ,p_display_name => lv_role_displayName);
4023 end if;
4024 return lv_role_displayName;
4025 exception
4026 when others then
4027 if g_debug then
4028 hr_utility.set_location('Error in getRoleDisplayName SQLERRM' ||' '||to_char(SQLCODE),20);
4029 end if;
4030
4031 end getRoleDisplayName;
4032
4033
4034 function isApproverEditAllowed(p_transaction_id number default null,
4035 p_user_name in varchar2,
4036 p_orig_system in varchar2,
4037 p_orig_system_id in number)
4038 return varchar2 is
4039 -- local variables
4040 c_proc constant varchar2(30) := 'isApproverEditAllowed';
4041 lv_role_displayName wf_users.display_name%type;
4042 lv_roleName wf_users.name%type;
4043 lv_businessGroupId per_all_people_f.business_group_id%type;
4044 lv_orig_systemId wf_roles.orig_system_id%type;
4045 lv_orig_system wf_roles.orig_system%type;
4046 lv_creator_person_id hr_api_transactions.creator_person_id%type;
4047 begin
4048 g_debug := hr_utility.debug_enabled;
4049 if g_debug then
4050 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4051 end if;
4052 lv_orig_system := p_orig_system;
4053 lv_orig_systemId := p_orig_system_id;
4054
4055 if(p_orig_system_id is null) then
4056 if(p_user_name is not null) then
4057 wf_directory.getroleorigsysinfo(p_user_name,lv_orig_system,lv_orig_systemId);
4058 end if;
4059 end if;
4060
4061 -- need to revisit the functionality once the AME functionality
4062 -- regarding the productions is evaluated.
4063 if(lv_orig_system is not null and lv_orig_system ='PER') then
4064 --
4065 -- check the case for creator person id
4066 begin
4067 select creator_person_id
4068 into lv_creator_person_id
4069 from hr_api_transactions
4070 where transaction_id=p_transaction_id;
4071
4072 if(lv_creator_person_id=lv_orig_systemId) then
4073 return 'Y';
4074 end if;
4075 exception
4076 when others then
4077 null;
4078 end;
4079
4080 begin
4081 select business_group_id
4082 into lv_businessGroupId
4083 from per_all_people_f
4084 where person_id=p_orig_system_id
4085 and sysdate between effective_start_date and effective_end_date;
4086 return pqh_ss_utility.check_edit_privilege(p_orig_system_id,lv_businessGroupId);
4087 exception
4088 when others then
4089 return 'N';
4090 end;
4091 else
4092 return 'N';
4093 end if;
4094
4095 exception
4096 when others then
4097 if g_debug then
4098 hr_utility.set_location('Error in getRoleDisplayName SQLERRM' ||' '||to_char(SQLCODE),20);
4099 end if;
4100
4101 end isApproverEditAllowed;
4102
4103 function getuserOrigSystem(p_user_name in fnd_user.user_name%type,p_notification_id in number default null)
4104 return wf_roles.parent_orig_system%type is
4105 -- local variables
4106 c_proc constant varchar2(30) := 'getuserOrigSystem';
4107 lv_orig_system wf_roles.parent_orig_system%type;
4108 lv_orig_system_id wf_roles.orig_system_id%type;
4109 lv_user_name wf_roles.name%type;
4110 begin
4111 g_debug := hr_utility.debug_enabled;
4112 if g_debug then
4113 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4114 end if;
4115 if(p_notification_id is null) then
4116 wf_directory.getroleorigsysinfo(p_user_name,lv_orig_system,lv_orig_system_id);
4117 else
4118 -- get the original recipient role name
4119 select original_recipient
4120 into lv_user_name
4121 from wf_notifications
4122 where notification_id =p_notification_id;
4123
4124 wf_directory.getroleorigsysinfo(lv_user_name,lv_orig_system,lv_orig_system_id);
4125 end if;
4126
4127 return lv_orig_system;
4128
4129 exception
4130 when others then
4131 if g_debug then
4132 hr_utility.set_location('Error in getRoleDisplayName SQLERRM' ||' '||to_char(SQLCODE),20);
4133 end if;
4134
4135 end getuserOrigSystem;
4136
4137 function getUserOrigSystemId(p_user_name in fnd_user.user_name%type,p_notification_id in number default null)
4138 return wf_roles.orig_system_id%type is
4139 -- local variables
4140 c_proc constant varchar2(30) := 'getUserOrigSystemId';
4141 lv_orig_system wf_roles.parent_orig_system%type;
4142 lv_orig_system_id wf_roles.orig_system_id%type;
4143 lv_user_name wf_roles.name%type;
4144 begin
4145 g_debug := hr_utility.debug_enabled;
4146 if g_debug then
4147 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4148 end if;
4149 if(p_notification_id is null) then
4150 wf_directory.getroleorigsysinfo(p_user_name,lv_orig_system,lv_orig_system_id);
4151 else
4152 -- get the original recipient role name
4153 select original_recipient
4154 into lv_user_name
4155 from wf_notifications
4156 where notification_id =p_notification_id;
4157
4158 wf_directory.getroleorigsysinfo(lv_user_name,lv_orig_system,lv_orig_system_id);
4159 end if;
4160
4161 return lv_orig_system_id;
4162
4163 exception
4164 when others then
4165 if g_debug then
4166 hr_utility.set_location('Error in getUserOrigSystemId SQLERRM' ||' '||to_char(SQLCODE),20);
4167 end if;
4168
4169 end getUserOrigSystemId;
4170
4171
4172 procedure handleRFCAction(p_approval_notification_id in wf_notifications.notification_id%type,
4173 p_transaction_id in hr_api_transactions.transaction_id%type,
4174 p_item_type in wf_items.item_type%type,
4175 p_item_key in wf_items.item_key%type,
4176 p_rfcRoleName in wf_roles.name%type,
4177 p_rfcUserOrigSystem in wf_roles.orig_system%type,
4178 p_rfcUserOrigSystemId in wf_roles.orig_system_id%type,
4179 p_rfc_comments in varchar2,
4180 p_approverIndex in number
4181 ) is
4182 -- local variables
4183 c_proc constant varchar2(30) := 'handleRFCAction';
4184 ln_rfc_notification_id wf_notifications.notification_id%type;
4185 l_lastDefaultApprover NUMBER;
4186 lv_dummy varchar2(10);
4187 lv_role_name wf_roles.name%type;
4188 lv_role_disp_name wf_roles.name%type;
4189 l_return_status varchar2(10);
4190 lv_role_orig_sys_id wf_roles.orig_system_id%type;
4191
4192 -- Cursor to find if the person (selected for RFC) is an additional approver
4193 CURSOR cur_add_appr IS
4194 SELECT 'X'
4195 FROM wf_item_attribute_values
4196 WHERE item_type = p_item_Type
4197 AND item_key = p_item_Key
4198 AND name like 'ADDITIONAL_APPROVER_%'
4199 AND number_value = p_rfcUserOrigSystemId;
4200 --
4201 -- Cursor to fetch the last default approver below the person performing
4202 -- RFC. It is used only in case of NON-AME approvals.
4203 --
4204 CURSOR cur_appr IS
4205 SELECT pth.employee_id
4206 FROM pqh_ss_approval_history pah,
4207 fnd_user pth
4208 WHERE pah.user_name = pth.user_name
4209 AND pah.transaction_history_id = p_transaction_id
4210 AND approval_history_id = (
4211 SELECT MAX(approval_history_id)
4212 FROM pqh_ss_approval_history pah1,
4213 fnd_user pth1
4214 WHERE pah1.user_name = pth1.user_name
4215 AND pah1.transaction_history_id = pah.transaction_history_id
4216 AND pth1.employee_id IN (
4217 SELECT pth2.employee_id --, pth2.user_name, approval_history_id
4218 FROM pqh_ss_approval_history pah2,
4219 fnd_user pth2
4220 WHERE pah2.user_name = pth2.user_name
4221 AND pah2.transaction_history_id = pah.transaction_history_id
4222 AND approval_history_id < (
4223 SELECT MIN(approval_history_id)
4224 FROM pqh_ss_approval_history
4225 WHERE transaction_history_id = pah.transaction_history_id
4226 AND user_name = lv_role_name
4227 AND approval_history_id > 0
4228 )
4229 and approval_history_id > 0
4230 MINUS
4231 SELECT number_value
4232 FROM wf_item_attribute_values
4233 WHERE item_type = p_item_Type
4234 AND item_key = p_item_Key
4235 AND name like 'ADDITIONAL_APPROVER_%'
4236 )
4237 );
4238
4239 begin
4240 g_debug := hr_utility.debug_enabled;
4241 if g_debug then
4242 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4243 end if;
4244
4245
4246
4247 -- fix for bug 4481775
4248 begin
4249 -- re-intialize the performer roles
4250 hr_approval_ss.reinitperformerroles(p_notification_id=>null
4251 ,p_transaction_id=>null
4252 ,p_item_type=>p_item_type
4253 ,p_item_key=>p_item_key);
4254 wf_directory.GetRoleName--GetUserName
4255 (p_orig_system => p_rfcUserOrigSystem
4256 ,p_orig_system_id => p_rfcUserOrigSystemId
4257 ,p_name => lv_role_name
4258 ,p_display_name => lv_role_disp_name);
4259
4260 exception
4261 when others then
4262 null;
4263 end;
4264
4265 -- set the item attributes used for RFC notification
4266 create_item_attrib_if_notexist(itemtype=>p_item_Type,
4267 itemkey =>p_item_Key,
4268 aname =>'RETURN_TO_USERNAME',
4269 text_value =>lv_role_name,
4270 number_value=>null,
4271 date_value =>null );
4272
4273 create_item_attrib_if_notexist(itemtype=>p_item_Type,
4274 itemkey =>p_item_Key,
4275 aname =>'RETURN_TO_USER_ORIG_SYS',
4276 text_value =>p_rfcUserOrigSystem,
4277 number_value=>null,
4278 date_value =>null );
4279 create_item_attrib_if_notexist(itemtype=>p_item_Type,
4280 itemkey =>p_item_Key,
4281 aname =>'RETURN_TO_USER_ORIG_SYS_ID',
4282 text_value =>null,
4283 number_value=>p_rfcUserOrigSystemId,
4284 date_value =>null );
4285 -- 'RETURN_TO_PERSON_DISPLAY_NAME'
4286 create_item_attrib_if_notexist(itemtype=>p_item_Type,
4287 itemkey =>p_item_Key,
4288 aname =>'RETURN_TO_PERSON_DISPLAY_NAME',
4289 text_value => lv_role_disp_name,
4290 number_value=>null,
4291 date_value =>null );
4292
4293 -- Set the notes for RFC notification.
4294 -- NOTE_FROM_APPR
4295 wf_engine.setItemAttrText (
4296 itemtype => p_item_type
4297 ,itemkey => p_item_Key
4298 ,aname => 'NOTE_FROM_APPR'
4299 ,avalue => p_rfc_comments );
4300 wf_engine.setItemAttrText (
4301 itemtype => p_item_type
4302 ,itemkey => p_item_Key
4303 ,aname => 'WF_NOTE'
4304 ,avalue => null );
4305
4306
4307 -- code logic to complete the approval notification with
4308 -- RETURNEDFORCORRECTION result
4309 wf_notification.setattrtext(p_approval_notification_id,
4310 'RESULT',
4311 'RETURNEDFORCORRECTION');
4312 BEGIN
4313 wf_notification.setattrtext(
4314 p_approval_notification_id
4315 ,'WF_NOTE'
4316 ,p_rfc_comments);
4317 EXCEPTION WHEN OTHERS THEN
4318 -- RFC from SFL Other
4319 wf_notification.propagatehistory(
4320 p_item_type
4321 ,p_item_key
4322 ,'APPROVAL_NOTIFICATION'
4323 ,fnd_global.user_name
4324 ,lv_role_name
4325 ,'RETURNEDFORCORRECTION'
4326 ,null
4327 ,p_rfc_comments);
4328 END;
4329
4330 -- now respond to the approval notification
4331 wf_notification.respond(p_approval_notification_id
4332 ,null
4333 ,fnd_global.user_name
4334 ,null);
4335
4336 hr_sflutil_ss.closeopensflnotification(p_transaction_id);
4337
4338 -- Bug 4898974 Fix.
4339 hr_transaction_api.finalize_transaction (
4340 P_TRANSACTION_ID => p_transaction_id
4341 ,P_EVENT => 'RFC'
4342 ,P_RETURN_STATUS => l_return_status
4343 );
4344
4345 -- logic to set the from role for rfc notification
4346 -- Fetch the id for RFC notification.
4347 begin
4348 SELECT ias.notification_id
4349 into ln_rfc_notification_id
4350 FROM WF_ITEM_ACTIVITY_STATUSES IAS
4351 WHERE ias.item_type = p_item_Type
4352 and ias.item_key = p_item_Key
4353 and IAS.ACTIVITY_STATUS = 'NOTIFIED'
4354 and notification_id is not null
4355 and rownum < 2;
4356 exception
4357 when others then
4358 null;
4359 end;
4360
4361 -- Set the from attribute for RFC notification.
4362 wf_notification.setAttrText(
4363 nid => ln_rfc_notification_id
4364 ,aname => '#FROM_ROLE'
4365 ,avalue => fnd_global.user_name );
4366 -- processing logic for non AME
4367 begin
4368 IF (wf_engine.GetItemAttrText(itemtype => p_item_Type ,
4369 itemkey => p_item_key,
4370 aname => 'HR_AME_TRAN_TYPE_ATTR') IS NULL) THEN
4371 --
4372 -- set the attribute value to null
4373 wf_engine.SetItemAttrNumber(
4374 itemtype => p_item_Type ,
4375 itemkey => p_item_key,
4376 aname => 'CURRENT_APPROVER_INDEX',
4377 avalue => p_approverIndex);
4378 --
4379 -- If the selected person (for RFC) is additional approver
4380 -- then fetch the last default approver from history
4381 -- else selected person is the last default approver
4382 IF ( cur_add_appr%ISOPEN ) THEN
4383 CLOSE cur_add_appr;
4384 END IF;
4385 --
4386 OPEN cur_add_appr;
4387 FETCH cur_add_appr INTO lv_dummy;
4388 if cur_add_appr%found then
4389 --
4390 IF ( cur_appr%ISOPEN ) THEN
4391 CLOSE cur_appr;
4392 END IF;
4393 --
4394 OPEN cur_appr;
4395 FETCH cur_appr INTO l_lastDefaultApprover;
4396 CLOSE cur_appr;
4397 --
4398 IF ( l_lastDefaultApprover IS NULL ) THEN
4399
4400 begin
4401 select employee_id into lv_role_orig_sys_id from (
4402 SELECT pth.employee_id
4403 FROM pqh_ss_approval_history pah,
4404 fnd_user pth
4405 WHERE pah.user_name = pth.user_name
4406 AND pah.transaction_history_id = p_transaction_id
4407 and approval_history_id > 0
4408 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)
4409 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
4410 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))
4411 order by pah.last_update_date desc
4412 )
4413 where rownum = 1;
4414 exception
4415 when others then
4416 null;
4417 end;
4418 l_lastDefaultApprover := lv_role_orig_sys_id;
4419 END IF;
4420 else
4421 l_lastDefaultApprover := p_rfcUserOrigSystemId;
4422 end if;
4423
4424 CLOSE cur_add_appr;
4425
4426 IF ( l_lastDefaultApprover IS NOT NULL ) THEN
4427 wf_engine.SetItemAttrNumber(
4428 itemtype => p_item_Type ,
4429 itemkey => p_item_key,
4430 aname => 'LAST_DEFAULT_APPROVER',
4431 avalue => l_lastDefaultApprover);
4432 END IF;
4433 end if;
4434 exception
4435 when others then
4436 null;
4437 end;
4438
4439 if (g_debug ) then
4440 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
4441 end if;
4442
4443 exception
4444 when others then
4445 if g_debug then
4446 hr_utility.set_location('Error in handleRFCAction SQLERRM' ||' '||to_char(SQLCODE),20);
4447 end if;
4448
4449 end handleRFCAction;
4450
4451 procedure handleRFCAction(p_approval_notification_id in wf_notifications.notification_id%type
4452 ) is
4453 lv_item_type wf_items.item_type%type;
4454 lv_item_key wf_items.item_key%type;
4455 lv_creator_role wf_roles.name%type;
4456 lv_creator_orig_system wf_roles.orig_system%type;
4457 lv_creator_orig_sys_id wf_roles.orig_system_id%type;
4458 begin
4459 hr_workflow_ss.get_item_type_and_key(p_approval_notification_id,lv_item_type,lv_item_key);
4460
4461 -- fix for bug 4481775
4462 begin
4463 -- re-intialize the performer roles
4464 hr_approval_ss.reinitperformerroles(p_notification_id=>null
4465 ,p_transaction_id=>null
4466 ,p_item_type=>lv_item_type
4467 ,p_item_key=>lv_item_key);
4468 exception
4469 when others then
4470 null;
4471 end;
4472
4473
4474 lv_creator_role := wf_engine.getitemattrtext(lv_item_type,
4475 lv_item_key,
4476 'CREATOR_PERSON_USERNAME');
4477 -- get the orig sys info
4478 wf_directory.getroleorigsysinfo(lv_creator_role,
4479 lv_creator_orig_system,
4480 lv_creator_orig_sys_id);
4481
4482 handleRFCAction(p_approval_notification_id =>p_approval_notification_id,
4483 p_transaction_id =>wf_engine.getitemattrnumber
4484 (lv_item_type,
4485 lv_item_key,
4486 'TRANSACTION_ID'),
4487 p_item_type =>lv_item_type,
4488 p_item_key =>lv_item_key,
4489 p_rfcRoleName =>lv_creator_role,
4490 p_rfcUserOrigSystem =>lv_creator_orig_system,
4491 p_rfcUserOrigSystemId =>lv_creator_orig_sys_id,
4492 p_rfc_comments=>null,
4493 p_approverIndex=>0
4494 );
4495
4496 end handleRFCAction;
4497
4498 procedure creatorRoleReInit(p_item_type in wf_items.item_type%type,
4499 p_item_key in wf_items.item_key%type) is
4500 lv_role_name wf_roles.name%type;
4501 lv_role_disp_name wf_roles.name%type;
4502 lv_role_orig_system wf_roles.orig_system%type;
4503 lv_role_orig_sys_id wf_roles.orig_system_id%type;
4504 begin
4505
4506 -- CREATOR_PERSON_ID
4507 lv_role_orig_sys_id:=wf_engine.getitemattrnumber(p_item_type,p_item_key,'CREATOR_PERSON_ID',true);
4508
4509 if(lv_role_orig_sys_id is not null) then
4510 -- need to revisit with role based support in SSHR transaction
4511 lv_role_orig_system := 'PER';
4512
4513 wf_directory.GetRoleName
4514 (p_orig_system => lv_role_orig_system
4515 ,p_orig_system_id => lv_role_orig_sys_id
4516 ,p_name => lv_role_name
4517 ,p_display_name => lv_role_disp_name);
4518
4519 -- set the concerned item attributes
4520 -- CREATOR_PERSON_USERNAME
4521 create_item_attrib_if_notexist(itemtype => p_item_type
4522 ,itemkey => p_item_key
4523 ,aname => 'CREATOR_PERSON_USERNAME'
4524 ,text_value=>lv_role_name
4525 ,number_value=>null,
4526 date_value=>null
4527 );
4528 -- APPROVAL_CREATOR_USERNAME
4529 create_item_attrib_if_notexist(itemtype => p_item_type
4530 ,itemkey => p_item_key
4531 ,aname => 'APPROVAL_CREATOR_USERNAME'
4532 ,text_value=>lv_role_name
4533 ,number_value=>null,
4534 date_value=>null
4535 );
4536
4537 end if;
4538
4539 end creatorRoleReInit;
4540
4541
4542 procedure selPersonRoleReInit(p_item_type in wf_items.item_type%type,
4543 p_item_key in wf_items.item_key%type) is
4544 lv_role_name wf_roles.name%type;
4545 lv_role_disp_name wf_roles.name%type;
4546 lv_role_orig_system wf_roles.orig_system%type;
4547 lv_role_orig_sys_id wf_roles.orig_system_id%type;
4548 begin
4549
4550 -- CURRENT_PERSON_ID
4551 lv_role_orig_sys_id:=wf_engine.getitemattrnumber(p_item_type,p_item_key,'CURRENT_PERSON_ID',true);
4552
4553 if(lv_role_orig_sys_id is not null) then
4554 -- need to revisit with role based support in SSHR transaction
4555 lv_role_orig_system := 'PER';
4556 wf_directory.GetRoleName
4557 (p_orig_system => lv_role_orig_system
4558 ,p_orig_system_id => lv_role_orig_sys_id
4559 ,p_name => lv_role_name
4560 ,p_display_name => lv_role_disp_name);
4561 -- set the concerned item attributes
4562 -- CURRENT_PERSON_USERNAME
4563 create_item_attrib_if_notexist(itemtype => p_item_type
4564 ,itemkey => p_item_key
4565 ,aname => 'CURRENT_PERSON_USERNAME'
4566 ,text_value=>lv_role_name
4567 ,number_value=>null,
4568 date_value=>null
4569 );
4570 create_item_attrib_if_notexist(itemtype => p_item_type
4571 ,itemkey => p_item_key
4572 ,aname => 'CURRENT_PERSON_DISPLAY_NAME'
4573 ,text_value=>lv_role_disp_name
4574 ,number_value=>null,
4575 date_value=>null
4576 );
4577 end if;
4578
4579 end selPersonRoleReInit;
4580
4581 procedure forwardFromRoleReInit(p_item_type in wf_items.item_type%type,
4582 p_item_key in wf_items.item_key%type) is
4583 lv_role_name wf_roles.name%type;
4584 lv_role_disp_name wf_roles.name%type;
4585 lv_role_orig_system wf_roles.orig_system%type;
4586 lv_role_orig_sys_id wf_roles.orig_system_id%type;
4587 begin
4588
4589 -- FORWARD_FROM_PERSON_ID
4590 lv_role_orig_sys_id:=wf_engine.getitemattrnumber(p_item_type,p_item_key,'FORWARD_FROM_PERSON_ID',true);
4591
4592 if(lv_role_orig_sys_id is not null) then
4593 -- need to revisit with role based support in SSHR transaction
4594 lv_role_orig_system := nvl( wf_engine.GetItemAttrText
4595 (itemtype => p_item_type
4596 ,itemkey => p_item_key
4597 ,aname => 'FORWARD_FROM_ORIG_SYS_ATTR'
4598 ,ignore_notfound=>true),'PER');
4599
4600 wf_directory.GetRoleName
4601 (p_orig_system => lv_role_orig_system
4602 ,p_orig_system_id => lv_role_orig_sys_id
4603 ,p_name => lv_role_name
4604 ,p_display_name => lv_role_disp_name);
4605 -- set the concerned item attributes
4606 -- FORWARD_FROM_USERNAME
4607 create_item_attrib_if_notexist(itemtype => p_item_type
4608 ,itemkey => p_item_key
4609 ,aname => 'FORWARD_FROM_USERNAME'
4610 ,text_value=>lv_role_name
4611 ,number_value=>null,
4612 date_value=>null
4613 );
4614 -- FORWARD_FROM_ROLE_NAME
4615 create_item_attrib_if_notexist(itemtype => p_item_type
4616 ,itemkey => p_item_key
4617 ,aname => 'FORWARD_FROM_ROLE_NAME'
4618 ,text_value=>null
4619 ,number_value=>null
4620 ,date_value=>null
4621 );
4622
4623 end if;
4624
4625 end forwardFromRoleReInit;
4626
4627
4628 procedure rfcUserRoleReInit(p_item_type in wf_items.item_type%type,
4629 p_item_key in wf_items.item_key%type) is
4630 lv_role_name wf_roles.name%type;
4631 lv_role_disp_name wf_roles.name%type;
4632 lv_role_orig_system wf_roles.orig_system%type;
4633 lv_role_orig_sys_id wf_roles.orig_system_id%type;
4634 begin
4635
4636 -- RETURN_TO_USER_ORIG_SYS_ID
4637 lv_role_orig_sys_id:=wf_engine.getitemattrnumber(p_item_type,p_item_key,'RETURN_TO_USER_ORIG_SYS_ID',true);
4638
4639 if(lv_role_orig_sys_id is not null) then
4640 -- need to revisit with role based support in SSHR transaction
4641 lv_role_orig_system := wf_engine.GetItemAttrText
4642 (itemtype => p_item_type
4643 ,itemkey => p_item_key
4644 ,aname => 'RETURN_TO_USER_ORIG_SYS'
4645 ,ignore_notfound=>true);
4646
4647 wf_directory.GetRoleName
4648 (p_orig_system => lv_role_orig_system
4649 ,p_orig_system_id => lv_role_orig_sys_id
4650 ,p_name => lv_role_name
4651 ,p_display_name => lv_role_disp_name);
4652 -- set the concerned item attributes
4653 -- RETURN_TO_USERNAME
4654 create_item_attrib_if_notexist(itemtype => p_item_type
4655 ,itemkey => p_item_key
4656 ,aname => 'RETURN_TO_USERNAME'
4657 ,text_value=>lv_role_name
4658 ,number_value=>null,
4659 date_value=>null
4660 );
4661 end if;
4662
4663 end rfcUserRoleReInit;
4664
4665
4666 procedure appraisalRolesReInit(p_item_type in wf_items.item_type%type,
4667 p_item_key in wf_items.item_key%type) is
4668 lv_role_name wf_roles.name%type;
4669 lv_role_disp_name wf_roles.name%type;
4670 lv_role_orig_system wf_roles.orig_system%type;
4671 lv_role_orig_sys_id wf_roles.orig_system_id%type;
4672 l_appraisal_id per_appraisals.appraisal_id%type;
4673 l_main_appraiser_id per_appraisals.main_appraiser_id%type;
4674 l_appraiser_person_id per_appraisals.appraiser_person_id%type;
4675 l_appraisee_person_id per_appraisals.appraisee_person_id%type;
4676 begin
4677
4678 -- get the appraisal id
4679 l_appraisal_id:= wf_engine.GetItemAttrNumber (itemtype => p_item_type ,
4680 itemkey => p_item_key ,
4681 aname => 'APPRAISAL_ID',
4682 ignore_notfound=>true);
4683
4684 -- get the appraisee, main appraiser and appraiser id
4685 if(l_appraisal_id is not null) then
4686 begin
4687
4688 select MAIN_APPRAISER_ID,APPRAISER_PERSON_ID,APPRAISEE_PERSON_ID
4689 into l_main_appraiser_id, l_appraiser_person_id,l_appraisee_person_id
4690 from per_appraisals
4691 where APPRAISAL_ID=l_appraisal_id;
4692
4693 exception
4694 when others then
4695 null;
4696 end;
4697
4698 -- need to revisit with role based support in SSHR transaction
4699 lv_role_orig_system := 'PER';
4700
4701 -- derive the role for HR_APPRAISEE_USER_NAME_ATTR
4702 if((l_appraisee_person_id is not null)and (l_appraisee_person_id<>'')) then
4703 wf_directory.GetRoleName
4704 (p_orig_system => lv_role_orig_system
4705 ,p_orig_system_id => l_appraisee_person_id
4706 ,p_name => lv_role_name
4707 ,p_display_name => lv_role_disp_name);
4708 -- set the concerned item attributes
4709 -- HR_APPRAISEE_USER_NAME_ATTR
4710 create_item_attrib_if_notexist(itemtype => p_item_type
4711 ,itemkey => p_item_key
4712 ,aname => 'HR_APPRAISEE_USER_NAME_ATTR'
4713 ,text_value=>lv_role_name
4714 ,number_value=>null,
4715 date_value=>null
4716 );
4717 end if;
4718
4719 -- derive the role for HR_MAIN_APPRAISER_USERNAME
4720 if(l_main_appraiser_id is not null) then
4721 wf_directory.GetRoleName
4722 (p_orig_system => lv_role_orig_system
4723 ,p_orig_system_id => l_main_appraiser_id
4724 ,p_name => lv_role_name
4725 ,p_display_name => lv_role_disp_name);
4726 -- set the concerned item attributes
4727 -- HR_MAIN_APPRAISER_USERNAME
4728 create_item_attrib_if_notexist(itemtype => p_item_type
4729 ,itemkey => p_item_key
4730 ,aname => 'HR_MAIN_APPRAISER_USERNAME'
4731 ,text_value=>lv_role_name
4732 ,number_value=>null,
4733 date_value=>null
4734 );
4735 end if;
4736
4737 -- derive the role for SUPERVISOR_USERNAME
4738 if(l_appraiser_person_id is not null) then
4739 wf_directory.GetRoleName
4740 (p_orig_system => lv_role_orig_system
4741 ,p_orig_system_id => l_appraiser_person_id
4742 ,p_name => lv_role_name
4743 ,p_display_name => lv_role_disp_name);
4744 -- set the concerned item attributes
4745 -- SUPERVISOR_USERNAME
4746 create_item_attrib_if_notexist(itemtype => p_item_type
4747 ,itemkey => p_item_key
4748 ,aname => 'SUPERVISOR_USERNAME'
4749 ,text_value=>lv_role_name
4750 ,number_value=>null,
4751 date_value=>null
4752 );
4753 end if;
4754 end if;
4755
4756 end appraisalRolesReInit;
4757
4758
4759 procedure reInitPerformerRoles(p_notification_id in wf_notifications.notification_id%type,
4760 p_transaction_id in hr_api_transactions.transaction_id%type,
4761 p_item_type in wf_items.item_type%type,
4762 p_item_key in wf_items.item_key%type) is
4763 c_proc constant varchar2(30) := 'reInitPerformerRoles';
4764 lv_item_type wf_items.item_type%type;
4765 lv_item_key wf_items.item_key%type;
4766
4767 begin
4768 g_debug := hr_utility.debug_enabled;
4769 if g_debug then
4770 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4771 end if;
4772
4773 -- get the item type item key
4774 if(p_notification_id is not null) then
4775 begin
4776 select substr(context,1,instr(context,':',1)-1) itemtype
4777 ,substr(context,instr(context,':')+1, (
4778 instr(context,':',instr(context,':')+1 ) - instr(context,':')-1) ) itemkey
4779 into lv_item_type,lv_item_key
4780 from wf_notifications
4781 where notification_id = p_notification_id;
4782 exception
4783 when others then
4784 null;
4785 end;
4786 elsif(p_transaction_id is not null) then
4787 begin
4788 select item_type,item_key
4789 into lv_item_type,lv_item_key
4790 from hr_api_transactions
4791 where transaction_id=p_transaction_id;
4792 exception
4793 when others then
4794 null;
4795 end;
4796 else
4797 lv_item_type := p_item_type;
4798 lv_item_key := p_item_key;
4799 end if;
4800
4801
4802 if((lv_item_type is not null) and (lv_item_key is not null)) then
4803 -- handle creator role reinit
4804 creatorRoleReInit(lv_item_type,lv_item_key);
4805 -- current or selected person role reinit
4806 selPersonRoleReInit(lv_item_type,lv_item_key);
4807 -- handle forward to user role reinit
4808 forwardToRoleReInit(lv_item_type,lv_item_key);
4809 -- handle forward from user role reinit
4810 forwardFromRoleReInit(lv_item_type,lv_item_key);
4811 -- handle RFC user reinit
4812 rfcUserRoleReInit(lv_item_type,lv_item_key);
4813 -- other module specifics
4814 appraisalRolesReInit(lv_item_type,lv_item_key);
4815 end if;
4816
4817 exception
4818 when others then
4819 if g_debug then
4820 hr_utility.set_location('Error in reInitPerformerRoles SQLERRM' ||' '||to_char(SQLCODE),20);
4821 end if;
4822 raise;
4823
4824 end reInitPerformerRoles;
4825
4826
4827 procedure approvals_block
4828 (itemtype in varchar2
4829 ,itemkey in varchar2
4830 ,actid in number
4831 ,funmode in varchar2
4832 ,result in out nocopy varchar2)
4833 is
4834 --local variables
4835
4836 begin
4837 -- Do nothing in cancel or timeout mode
4838 if (funmode <> wf_engine.eng_run) then
4839 result := wf_engine.eng_null;
4840 return;
4841 end if;
4842 -- set the item attribute value with the current activity id
4843 -- this will be used when the notification is sent.
4844 -- and to complete the blocked thread.
4845 -- HR_APPROVAL_BLOCK_ID_ATTR
4846 create_item_attrib_if_notexist(itemtype => itemtype
4847 ,itemkey => itemkey
4848 ,aname => 'HR_APPROVAL_BLOCK_ID_ATTR'
4849 ,text_value=>null
4850 ,number_value=>actid,
4851 date_value=>null
4852 );
4853 WF_STANDARD.BLOCK(itemtype,itemkey,actid,funmode,result);
4854
4855 --resultout := 'NOTIFIED';
4856
4857 exception
4858 when others then
4859 Wf_Core.Context(g_package, '.approvals_block', itemtype,
4860 itemkey, to_char(actid), funmode);
4861 raise;
4862 end approvals_block;
4863
4864
4865
4866
4867 -- new code for generic approval process
4868 -- without page navigation
4869 -- ----------------------------------------------------------------------------
4870 -- |--------------------< wf_get_runnable_process_name >----------------------|
4871 -- ----------------------------------------------------------------------------
4872 function wf_get_runnable_process_name
4873 (p_item_type in wf_items.item_type%type
4874 ,p_process_name in wf_process_activities.process_name%type)
4875 return wf_runnable_processes_v.display_name%type is
4876 -- cursor determines is the specified process is runnable
4877 cursor csr_wrpv is
4878 select wrpv.display_name
4879 from wf_runnable_processes_v wrpv
4880 where wrpv.item_type = p_item_type
4881 and wrpv.process_name = p_process_name;
4882 --
4883 l_display_name wf_runnable_processes_v.display_name%type;
4884 --
4885 begin
4886 -- Determine if the specified process is runnable
4887 open csr_wrpv;
4888 fetch csr_wrpv into l_display_name;
4889 if csr_wrpv%notfound then
4890 close csr_wrpv;
4891 return(NULL);
4892 end if;
4893 close csr_wrpv;
4894 return(l_display_name);
4895 end wf_get_runnable_process_name;
4896 -- ----------------------------------------------------------------------------
4897 -- |-------------------------< wf_process_runnable >--------------------------|
4898 -- ----------------------------------------------------------------------------
4899 function wf_process_runnable
4900 (p_item_type in wf_items.item_type%type
4901 ,p_process_name in wf_process_activities.process_name%type)
4902 return boolean is
4903 --
4904 begin
4905 if wf_get_runnable_process_name
4906 (p_item_type => p_item_type
4907 ,p_process_name => p_process_name) is NULL then
4908 return(FALSE);
4909 else
4910 return(TRUE);
4911 end if;
4912 end wf_process_runnable;
4913
4914 -- ----------------------------------------------------------------------------
4915 -- |------------------------< create_hr_directory_services >------------------|
4916 -- ----------------------------------------------------------------------------
4917 procedure create_hr_directory_services
4918 (p_item_type in wf_items.item_type%type
4919 ,p_item_key in wf_items.item_key%type
4920 ,p_service_name in varchar2
4921 ,p_service_orig_sys_id in number
4922 ,p_service_orig_sys in varchar2) is
4923 --
4924 l_item_type_attribute_name varchar2(30);
4925 type l_suffix_tab is table of varchar2(30) index by binary_integer;
4926 l_suffix l_suffix_tab;
4927 l_username wf_users.name%type;
4928 l_display_name wf_users.display_name%type;
4929 --
4930 begin
4931 if p_service_orig_sys_id is not null then
4932 l_suffix(1) := 'ID';
4933 l_suffix(2) := 'USERNAME';
4934 l_suffix(3) := 'DISPLAY_NAME';
4935 l_suffix(4) := 'ORIG_SYS';
4936 -- get the USERNAME and DISPLAY_NAME from workflow
4937 begin
4938 wf_directory.getrolename
4939 (p_orig_system => p_service_orig_sys
4940 ,p_orig_system_id => p_service_orig_sys_id
4941 ,p_name => l_username
4942 ,p_display_name => l_display_name);
4943 exception
4944 when others then
4945 null;
4946 end;
4947 for i in 1..4 loop
4948 l_item_type_attribute_name := p_service_name||'_'||l_suffix(i);
4949
4950 -- set the item attribue value
4951 if i = 1 then
4952 -- set the ID value
4953 create_item_attrib_if_notexist(itemtype => p_item_type
4954 ,itemkey => p_item_key
4955 ,aname => l_item_type_attribute_name
4956 ,text_value=>null
4957 ,number_value=>p_service_orig_sys_id,
4958 date_value=>null
4959 );
4960
4961 elsif i = 2 then
4962 -- set the USERNAME value
4963 create_item_attrib_if_notexist(itemtype => p_item_type
4964 ,itemkey => p_item_key
4965 ,aname => l_item_type_attribute_name
4966 ,text_value=>l_username
4967 ,number_value=>null,
4968 date_value=>null
4969 );
4970 elsif i = 3 then
4971 -- set the DISPLAY_NAME value
4972
4973 ------- FOR 13607642
4974 if(l_display_name is null ) then
4975 begin
4976 if p_service_orig_sys_id is not null then
4977 l_display_name := hr_person_name.get_person_name(p_service_orig_sys_id,sysdate );
4978 end if;
4979 exception
4980 when others then
4981 l_display_name := null;
4982 end;
4983 end if;
4984 -----FOR 13607642
4985
4986 create_item_attrib_if_notexist(itemtype => p_item_type
4987 ,itemkey => p_item_key
4988 ,aname => l_item_type_attribute_name
4989 ,text_value=>l_display_name
4990 ,number_value=>null,
4991 date_value=>null
4992 );
4993
4994 else
4995 -- set the ORIG_SYS value
4996 create_item_attrib_if_notexist(itemtype => p_item_type
4997 ,itemkey => p_item_key
4998 ,aname => l_item_type_attribute_name
4999 ,text_value=>p_service_orig_sys
5000 ,number_value=>null,
5001 date_value=>null
5002 );
5003
5004 end if;
5005 end loop;
5006 end if;
5007 end create_hr_directory_services;
5008 -- ----------------------------------------------------------------------------
5009 -- getPersonNameFromID --
5010 -- called internally to give the person name for the given user name --
5011 -- ----------------------------------------------------------------------------
5012 --
5013 FUNCTION getPersonNameFromID
5014 (p_person_id per_all_people_f.person_id%type)
5015 return per_all_people_f.full_name%type is
5016 --
5017 cursor csr_full_name
5018 is select full_name
5019 from per_all_people_f papf
5020 where papf.person_id = p_person_id
5021 and trunc(sysdate) between effective_start_date
5022 and effective_end_date;
5023 --
5024 l_employee_name per_all_people_f.full_name%type;
5025 --
5026 BEGIN
5027 --
5028 hr_utility.trace('Finding Person name for person_id :' || p_person_id || ':');
5029 --
5030 open csr_full_name;
5031 fetch csr_full_name into l_employee_name;
5032 --
5033 if csr_full_name%notfound then
5034 l_employee_name := ' ';
5035 end if;
5036 close csr_full_name;
5037 --
5038 hr_utility.trace('Found :' || l_employee_name || ':');
5039 --
5040 return l_employee_name;
5041 --
5042 END getPersonNameFromID;
5043
5044 procedure startGenericApprovalProcess(p_transaction_id in number
5045 ,p_item_key in out nocopy wf_items.item_key%type
5046 ,p_wf_ntf_sub_fnd_msg in fnd_new_messages.message_name%type
5047 ,p_relaunch_function hr_api_transactions.relaunch_function%type
5048 ,p_additional_wf_attributes in HR_WF_ATTR_TABLE
5049 ,p_status out nocopy varchar2
5050 ,p_error_message out nocopy varchar2
5051 ,p_errstack out nocopy varchar2
5052 )
5053 is
5054 c_proc constant varchar2(30) := 'startGenericApprovalProcess';
5055 lv_item_type wf_items.item_type%type;
5056 lv_item_key wf_items.item_key%type;
5057 lr_transaction_rec hr_api_transactions%rowtype;
5058 lv_status varchar2(8);
5059 lv_result varchar2(30);
5060 lv_errorActid wf_item_activity_statuses.process_activity%type;
5061 lv_errname VARCHAR2(4000);
5062 l_index binary_integer;
5063 l_temp_item_attribute varchar2(2000);
5064 l_role_name wf_roles.name%type;
5065 l_role_displayname wf_roles.display_name%type;
5066 l_current_person_name per_all_people_f.full_name%type;
5067 l_manager_id per_all_people_f.person_id%type;
5068 begin
5069 g_debug := hr_utility.debug_enabled;
5070 if g_debug then
5071 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
5072 end if;
5073 -- check if the transaction id is passed if not raise exception
5074 -- no more iterations
5075 if(p_transaction_id is not null) then
5076 -- block to get the transaction record details
5077 begin
5078 select *
5079 into lr_transaction_rec
5080 from hr_api_transactions
5081 where transaction_id=p_transaction_id;
5082 exception
5083 when no_data_found then
5084 raise;
5085 -- need proper error message propagated
5086 when others then
5087 raise;
5088 end;
5089
5090 -- test value
5091 lr_transaction_rec.item_type:=nvl(lr_transaction_rec.item_type,'HRSSA');
5092 lr_transaction_rec.process_name :=nvl(lr_transaction_rec.process_name,'HR_GENERIC_APPROVAL_PRC');
5093 -- end of test value
5094 -- Determine if the specified process is runnable
5095 if NOT wf_process_runnable
5096 (p_item_type => lr_transaction_rec.item_type
5097 ,p_process_name => lr_transaction_rec.process_name
5098 ) then
5099 -- supply HR error message, p_process_name either does not exist or
5100 -- is NOT a runnable process
5101 hr_utility.set_message(800,'HR_52958_WKF2TSK_INC_PROCESS');
5102 hr_utility.set_message_token('ITEM_TYPE', lr_transaction_rec.item_type);
5103 hr_utility.set_message_token('PROCESS_NAME', lr_transaction_rec.process_name);
5104 hr_utility.raise_error;
5105 end if;
5106
5107 -- now check if we need to derive the item key from db seq
5108 if(p_item_key is null)then
5109 begin
5110 -- Get the next item key from the sequence
5111 select hr_workflow_item_key_s.nextval
5112 into p_item_key
5113 from sys.dual;
5114 exception
5115 when no_data_found then
5116 raise;
5117 -- need to pass proper error message
5118 -- this is a fatal error in getting the sequence value
5119 when others then
5120 raise;
5121 end;
5122 end if;
5123
5124 -- Create the Workflow Process
5125 wf_engine.CreateProcess
5126 (itemtype => lr_transaction_rec.item_type
5127 ,itemkey => p_item_key
5128 ,process => lr_transaction_rec.process_name);
5129
5130 -- check the process status before setting
5131 -- other mandatory attributes
5132 -- check the state of the workflow
5133 -- we need to check if the flow is in error state or not
5134 wf_engine.iteminfo(lr_transaction_rec.item_type,
5135 p_item_key,
5136 p_status,
5137 lv_result,
5138 lv_errorActid,
5139 lv_errname,
5140 p_error_message,
5141 p_errstack);
5142
5143 if(lv_status = 'ERROR') then
5144 raise g_wf_error_state;
5145 end if;
5146
5147 -- Derive the mandatory roles and other details
5148 -- required for wf initalizations
5149
5150 -- add new attribute for deriving the ntf subject
5151 -- HR_NTF_SUB_FND_MSG_ATTR
5152 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
5153 ,itemkey => p_item_key
5154 ,aname => 'HR_NTF_SUB_FND_MSG_ATTR'
5155 ,text_value=>p_wf_ntf_sub_fnd_msg
5156 ,number_value=>null,
5157 date_value=>null
5158 );
5159 --
5160 -- Create the standard set of item attributes
5161 -- CURRENT_PERSON_ID and CREATOR_PERSON_ID
5162 --
5163 create_hr_directory_services
5164 (p_item_type => lr_transaction_rec.item_type
5165 ,p_item_key => p_item_key
5166 ,p_service_name => 'CREATOR_PERSON'
5167 ,p_service_orig_sys_id =>lr_transaction_rec.creator_person_id
5168 ,p_service_orig_sys =>'PER' -- need to revisit for role based support
5169 );
5170 create_hr_directory_services
5171 (p_item_type => lr_transaction_rec.item_type
5172 ,p_item_key => p_item_key
5173 ,p_service_name => 'CURRENT_PERSON'
5174 ,p_service_orig_sys_id =>lr_transaction_rec.selected_person_id
5175 ,p_service_orig_sys =>'PER' -- need to revisit for role based support
5176 );
5177
5178 -- Get Vacancy name and manager/creator name if the transaction is related to vacancy
5179
5180 if lr_transaction_rec.transaction_ref_table ='PER_ALL_VACANCIES' then
5181
5182 l_manager_id := hr_xml_util.get_node_value(lr_transaction_rec.transaction_id,
5183 'ManagerId',
5184 'Transaction/TransCache/AM/TXN/EO/PerRequisitionsEORow/CEO/EO/PerAllVacanciesEORow');
5185
5186 if l_manager_id is not null then
5187 l_current_person_name := getpersonnamefromid(l_manager_id);
5188 else
5189 l_current_person_name := getpersonnamefromid(lr_transaction_rec.creator_person_id);
5190 end if;
5191 l_current_person_name := l_current_person_name||'('||lr_transaction_rec.api_addtnl_info||')';
5192
5193 wf_engine.setItemattrtext(lr_transaction_rec.item_type,p_item_key,'CURRENT_PERSON_DISPLAY_NAME',l_current_person_name);
5194 end if;
5195
5196 -- Get Applicant name if the transaction is related to offers
5197
5198 if lr_transaction_rec.transaction_ref_table ='IRC_OFFERS' then
5199 l_current_person_name := lr_transaction_rec.api_addtnl_info;
5200 wf_engine.setItemattrtext(lr_transaction_rec.item_type,p_item_key,'CURRENT_PERSON_DISPLAY_NAME',l_current_person_name);
5201 end if;
5202 -- Create Item Attributes for those passed in
5203 --
5204 l_index := 1;
5205 --
5206 WHILE l_index <= p_additional_wf_attributes.count LOOP
5207 begin
5208 -- upper the item attribute name
5209 -- if a NO_DATA_FOUND exception occurs, the exception is
5210 -- handled and the item is skipped
5211 l_temp_item_attribute := upper(p_additional_wf_attributes(l_index).name);
5212 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
5213 ,itemkey => p_item_key
5214 ,aname => l_temp_item_attribute
5215 ,text_value=>p_additional_wf_attributes(l_index).text_value
5216 ,number_value=>p_additional_wf_attributes(l_index).number_value,
5217 date_value=>p_additional_wf_attributes(l_index).date_value
5218 );
5219 l_index := l_index + 1;
5220 exception
5221 when NO_DATA_FOUND then
5222 -- The array element at the index position has not been set
5223 -- Ignore, but increment the counter and continue with the LOOP
5224 l_index := l_index + 1;
5225 end;
5226 END LOOP;
5227
5228
5229 --
5230 -- ---------------------------------
5231 -- Get the Role for the Owner
5232 -- ---------------------------------
5233 wf_directory.getRoleName
5234 (p_orig_system => 'PER'
5235 ,p_orig_system_id => lr_transaction_rec.creator_person_id
5236 ,p_name => l_role_name
5237 ,p_display_name => l_role_displayname);
5238
5239 IF l_role_name = '' OR l_role_name IS NULL THEN
5240 RAISE g_invalid_responsibility;
5241 END IF;
5242 -- ---------------------------------------------------
5243 -- Set the Item Owner
5244 -- ---------------------------------------------------
5245 wf_engine.setItemOwner
5246 (itemtype => lr_transaction_rec.item_type
5247 ,itemkey => p_item_key
5248 ,owner => l_role_name);
5249
5250 -- set 'CURRENT_EFFECTIVE_DATE'
5251 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
5252 ,itemkey => p_item_key
5253 ,aname => 'CURRENT_EFFECTIVE_DATE'
5254 ,text_value=>null
5255 ,number_value=>null,
5256 date_value=>lr_transaction_rec.transaction_effective_date
5257 );
5258 -- set 'CURRENT_EFFECTIVE_DATE'
5259 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
5260 ,itemkey => p_item_key
5261 ,aname => 'CURRENT_EFFECTIVE_DATE'
5262 ,text_value=>null
5263 ,number_value=>null,
5264 date_value=>lr_transaction_rec.transaction_effective_date
5265 );
5266 -- set HR_OAF_EDIT_URL_ATTR
5267 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
5268 ,itemkey => p_item_key
5269 ,aname => 'HR_OAF_EDIT_URL_ATTR'
5270 ,text_value=>p_relaunch_function
5271 ,number_value=>null,
5272 date_value=>null
5273 );
5274 -- set HR_OAF_NAVIGATION_ATTR
5275 create_item_attrib_if_notexist(itemtype => lr_transaction_rec.item_type
5276 ,itemkey => p_item_key
5277 ,aname => 'HR_OAF_NAVIGATION_ATTR'
5278 ,text_value=>'N'
5279 ,number_value=>null,
5280 date_value=>null
5281 );
5282
5283 -- now start the process
5284 -- Start the WF runtime process
5285 wf_engine.startprocess
5286 (itemtype => lr_transaction_rec.item_type
5287 ,itemkey => p_item_key);
5288
5289 -- check the wf status before returning the status back to caller
5290 -- we need to check if the flow is in error state or not
5291 wf_engine.iteminfo(lr_transaction_rec.item_type,
5292 p_item_key,
5293 p_status,
5294 lv_result,
5295 lv_errorActid,
5296 lv_errname,
5297 p_error_message,
5298 p_errstack);
5299
5300 if(lv_status = 'ERROR') then
5301 raise g_wf_error_state;
5302 end if;
5303
5304
5305 else
5306 raise g_no_transaction_id;
5307 end if;
5308
5309 if (g_debug ) then
5310 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
5311 end if;
5312
5313 exception
5314 WHEN g_invalid_responsibility THEN
5315 fnd_message.set_name('PER','HR_SSA_INVALID_RESPONSIBILITY');
5316 hr_utility.raise_error;
5317 when g_wf_error_state then
5318 -- HR_WF_TRANSACTION_ERROR_SS
5319 fnd_message.set_name('PER','HR_WF_TRANSACTION_ERROR_SS');
5320 fnd_message.set_token('ERRORMSG',p_error_message,true);
5321 fnd_message.set_token('ERRORSTACK',p_errstack,true);
5322 hr_utility.raise_error;
5323 when g_no_transaction_id then
5324 null;
5325 -- handle the proper error message propagation
5326 when others then
5327 raise;
5328 end startGenericApprovalProcess;
5329
5330
5331 function getinitApprovalBlockId(p_transaction_id in number) return number
5332 is
5333
5334 c_proc constant varchar2(30) := 'getinitApprovalBlockId';
5335 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
5336 ln_activity_id number;
5337 lv_loginPersonDispName per_all_people_f.full_name%type;
5338 lv_loginPersonUserName fnd_user.user_name%type;
5339 ln_loginPersonId fnd_user.employee_id%type;
5340 begin
5341 if(p_transaction_id is not null) then
5342 select * into lr_hr_api_transaction_rec
5343 from hr_api_transactions
5344 where transaction_id=p_transaction_id;
5345 if(lr_hr_api_transaction_rec.transaction_ref_table='PER_APPRAISALS') then
5346 -- appraisal specfic
5347
5348 -- set the item attributes specific to appraisals
5349 -- get the wf role info for the login user
5350 wf_directory.getusername
5351 (p_orig_system => 'PER'
5352 ,p_orig_system_id => fnd_global.employee_id
5353 ,p_name => lv_loginPersonUserName
5354 ,p_display_name => lv_loginPersonDispName);
5355 --HR_APPRAISAL_FROM_USER_ATTR
5356 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
5357 ,lr_hr_api_transaction_rec.item_key
5358 ,'HR_APPRAISAL_FROM_USER_ATTR',
5359 fnd_global.user_name);
5360 -- HR_APPRAISAL_FROM_NAME_ATTR
5361 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
5362 ,lr_hr_api_transaction_rec.item_key
5363 ,'HR_APPRAISAL_FROM_NAME_ATTR'
5364 ,lv_loginPersonDispName);
5365
5366 -- APPROVAL_COMMENT_COPY
5367 -- ??? module need to handle in the UI layer.
5368
5369 -- set the blockid value
5370 hr_appraisal_workflow_ss.getapprovalblockid(
5371 lr_hr_api_transaction_rec.item_type,
5372 lr_hr_api_transaction_rec.item_key,
5373 ln_activity_id);
5374 else
5375 -- generic approval process initial block id attribute
5376 --HR_APPROVAL_BLOCK_ID_ATTR
5377 ln_activity_id:=wf_engine.getitemattrnumber(lr_hr_api_transaction_rec.item_type,
5378 lr_hr_api_transaction_rec.item_key,
5379 'HR_APPROVAL_BLOCK_ID_ATTR',true);
5380 end if;
5381 end if;
5382 return ln_activity_id;
5383
5384 end getinitApprovalBlockId;
5385
5386 function getApproverNtfId(p_transaction_id in number) return number
5387 is
5388 c_proc constant varchar2(30) := 'getApproverNtfId';
5389 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
5390 ln_notification_id wf_notifications.notification_id%type;
5391 begin
5392 if(p_transaction_id is not null) then
5393 select * into lr_hr_api_transaction_rec
5394 from hr_api_transactions
5395 where transaction_id=p_transaction_id;
5396
5397 select notification_id
5398 into ln_notification_id
5399 FROM WF_ITEM_ACTIVITY_STATUSES IAS
5400 WHERE ias.item_type = lr_hr_api_transaction_rec.item_type
5401 and ias.item_key = lr_hr_api_transaction_rec.item_key
5402 and ias.activity_status = 'NOTIFIED'
5403 and ias.notification_id is not null
5404 and rownum<=1;
5405 end if;
5406
5407 return ln_notification_id;
5408 exception
5409 when no_data_found then
5410 raise;
5411 when others then
5412 raise;
5413 end getApproverNtfId;
5414
5415 procedure processPageNavWFSubmit(p_transaction_id in number,
5416 p_approval_comments in varchar2)
5417 is
5418 -- local variables
5419 c_proc constant varchar2(30) := 'processPageNavWFSubmit';
5420 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
5421 ln_activity_id wf_item_activity_statuses.process_activity%type;
5422 begin
5423 if g_debug then
5424 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
5425 end if;
5426
5427 if(p_transaction_id is not null) then
5428
5429 select * into lr_hr_api_transaction_rec
5430 from hr_api_transactions
5431 where transaction_id=p_transaction_id;
5432
5433 hr_transaction_api.update_transaction(
5434 p_transaction_id => p_transaction_id,
5435 p_status => 'Y',
5436 p_transaction_state => null);
5437
5438 -- re-intialize the performer roles
5439 hr_approval_ss.reinitperformerroles(p_notification_id=>null
5440 ,p_transaction_id=>null
5441 ,p_item_type=>lr_hr_api_transaction_rec.item_type
5442 ,p_item_key=>lr_hr_api_transaction_rec.item_key);
5443
5444
5445 -- default logic
5446 -- get the blockid value corresponding to the UI page
5447 ln_activity_id:=
5448 getOAFPageActId(lr_hr_api_transaction_rec.item_type,lr_hr_api_transaction_rec.item_key);
5449 -- check if the ln_activity_id is null
5450 if(ln_activity_id is null) then
5451 null; -- raise error
5452 end if;
5453 -- set the workflow status TRAN_SUBMIT to Y
5454 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type
5455 ,lr_hr_api_transaction_rec.item_key
5456 ,'TRAN_SUBMIT'
5457 ,'Y');
5458
5459
5460 --
5461
5462
5463 -- now transition the workflow to process approval notifications
5464 if(lr_hr_api_transaction_rec.status in('YS','RI','RIS','RO','RIS')) then
5465 -- complete the flow in resubmit mode
5466 wf_engine.CompleteActivity(
5467 lr_hr_api_transaction_rec.item_type
5468 , lr_hr_api_transaction_rec.item_key
5469 , wf_engine.getactivitylabel(ln_activity_id)
5470 , 'RESUBMIT') ;
5471
5472
5473 else
5474 -- else intial submit
5475 wf_engine.CompleteActivity(
5476 lr_hr_api_transaction_rec.item_type
5477 , lr_hr_api_transaction_rec.item_key
5478 , wf_engine.getactivitylabel(ln_activity_id)
5479 , wf_engine.eng_trans_default) ;
5480 end if;
5481 end if;
5482
5483 if (g_debug ) then
5484 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
5485 end if;
5486 exception
5487 when others then
5488 raise;
5489 end processPageNavWFSubmit;
5490
5491
5492 procedure update_comments(
5493 p_ntf_id in number,
5494 p_txn_status in varchar2,
5495 p_approval_comments in varchar2)
5496 is
5497 cursor csr_wf_note_exists is
5498 select 1
5499 from wf_notification_attributes wna
5500 where notification_id = p_ntf_id
5501 and NAME = 'WF_NOTE';
5502 l_dummy number(1);
5503 begin
5504 -- The parameter p_txn_status is currently not used but if this procedure requires
5505 -- modifications in the future it might be needed.
5506
5507 -- Check if comments are present
5508 if p_approval_comments is not null then
5509 -- Check if WF_NOTE is a part of the notification
5510 open csr_wf_note_exists;
5511 fetch csr_wf_note_exists into l_dummy;
5512 if csr_wf_note_exists%found then
5513 -- Attribute exists so write into it
5514 wf_notification.setattrtext( p_ntf_id,'WF_NOTE',p_approval_comments);
5515 else
5516 -- Do we need to create the attribute ?
5517 -- Current implementation is a no-op
5518 null;
5519 end if;
5520 close csr_wf_note_exists;
5521 end if;
5522
5523 end update_comments;
5524
5525
5526
5527 procedure processNonPageNavWFSubmit(p_transaction_id in number,
5528 p_approval_comments in varchar2)
5529 is
5530 -- local variables
5531 c_proc constant varchar2(30) := 'processNonPageNavWFSubmit';
5532 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
5533 ln_activity_id wf_item_activity_statuses.process_activity%type;
5534 ln_notification_id wf_notifications.notification_id%type;
5535 lv_loginPersonDispName per_all_people_f.full_name%type;
5536 lv_loginPersonUserName fnd_user.user_name%type;
5537 ln_loginPersonId fnd_user.employee_id%type;
5538
5539 begin
5540 if g_debug then
5541 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
5542 end if;
5543
5544 if(p_transaction_id is not null) then
5545
5546 select * into lr_hr_api_transaction_rec
5547 from hr_api_transactions
5548 where transaction_id=p_transaction_id;
5549
5550 hr_transaction_api.update_transaction(
5551 p_transaction_id => p_transaction_id,
5552 p_status => 'Y',
5553 p_transaction_state => null);
5554
5555
5556 -- re-intialize the performer roles
5557 hr_approval_ss.reinitperformerroles(p_notification_id=>null
5558 ,p_transaction_id=>null
5559 ,p_item_type=>lr_hr_api_transaction_rec.item_type
5560 ,p_item_key=>lr_hr_api_transaction_rec.item_key);
5561
5562
5563 -- get the current transaction status
5564 -- If approver is editing and submitting the possible
5565 -- status are 'Y','YS','RI','RIS','RO','RIS'
5566 -- else it should be the initial creator submit
5567
5568 begin
5569 -- get the current transaction status
5570 if(lr_hr_api_transaction_rec.status in ('Y','YS','RI','RIS','RO','RIS')) then
5571 -- approver edit and submit case
5572 ln_notification_id:= getApproverNtfId(p_transaction_id);
5573
5574 wf_engine.setitemattrtext(lr_hr_api_transaction_rec.item_type,lr_hr_api_transaction_rec.item_key,
5575 'APPROVAL_COMMENT_COPY',p_approval_comments);
5576
5577 -- check if not null value
5578 if(ln_notification_id is not null) then
5579 -- add the code plugin transfer history
5580 -- need to revisit as this is resubmit not submit action
5581 hr_trans_history_api.archive_resubmit(p_transaction_id,
5582 null,
5583 fnd_global.user_name,
5584 p_approval_comments);
5585 -- complete the notification
5586 wf_notification.setattrtext(
5587 ln_notification_id
5588 ,'RESULT'
5589 ,'RESUBMIT');
5590
5591 -- Fix for bug 4998216 starts
5592 update_comments(ln_notification_id,lr_hr_api_transaction_rec.status,p_approval_comments);
5593 -- Fix for bug 4998216 ends
5594
5595 wf_notification.respond(
5596 ln_notification_id
5597 ,p_approval_comments
5598 ,fnd_global.user_name
5599 ,null);
5600 -- Fix for 5070814
5601 if(lr_hr_api_transaction_rec.status in ('RI','RIS','RO','RIS')) then
5602 -- propagate during RFC only
5603 wf_notification.propagatehistory(
5604 lr_hr_api_transaction_rec.item_type
5605 ,lr_hr_api_transaction_rec.item_key
5606 ,'APPROVAL_NOTIFICATION'
5607 ,fnd_global.user_name
5608 ,'WF_SYSTEM'
5609 --,hr_workflow_ss.getNextApproverForHist(itemtype, itemkey)
5610 ,'RESUBMIT'
5611 ,null
5612 ,p_approval_comments);
5613 end if;
5614 else
5615 -- notification is null, raise exception
5616 null;
5617 end if;
5618 -- get the approver notification id
5619 else
5620 -- intiator submit case
5621 -- get the inital block id
5622 ln_activity_id:= getinitApprovalBlockId(p_transaction_id);
5623
5624 -- update the transaction status before transitioning the flow
5625 hr_transaction_api.update_transaction(
5626 p_transaction_id => p_transaction_id,
5627 p_status => 'Y');
5628
5629
5630
5631
5632 -- set the initial submit comments
5633 -- APPROVAL_COMMENT_COPY
5634 hr_approval_ss.create_item_attrib_if_notexist(itemtype => lr_hr_api_transaction_rec.item_type
5635 ,itemkey => lr_hr_api_transaction_rec.item_key
5636 ,aname => 'APPROVAL_COMMENT_COPY'
5637 ,text_value=>p_approval_comments
5638 ,number_value=>null,
5639 date_value=>null
5640 );
5641
5642 WF_ENGINE.SetItemAttrText(lr_hr_api_transaction_rec.item_type,lr_hr_api_transaction_rec.item_key,
5643 'WF_NOTE',p_approval_comments);
5644
5645 -- else intial submit
5646 wf_engine.CompleteActivity(
5647 lr_hr_api_transaction_rec.item_type
5648 , lr_hr_api_transaction_rec.item_key
5649 , wf_engine.getactivitylabel(ln_activity_id)
5650 , wf_engine.eng_trans_default) ;
5651
5652 end if;
5653 end;
5654
5655 else
5656 null; -- null transaction id throw error ??
5657 end if;
5658
5659 if (g_debug ) then
5660 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
5661 end if;
5662 exception
5663 when others then
5664 raise;
5665 end processNonPageNavWFSubmit;
5666
5667 procedure processApprovalSubmit(p_transaction_id in number,
5668 p_approval_comments in varchar2)
5669 is
5670 -- local variables
5671 c_proc constant varchar2(30) := 'processApprovalSubmit';
5672 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
5673 ln_activity_id wf_item_activity_statuses.process_activity%type;
5674 lv_loginPersonDispName per_all_people_f.full_name%type;
5675 lv_loginPersonUserName fnd_user.user_name%type;
5676 ln_loginPersonId fnd_user.employee_id%type;
5677 lv_item_type wf_items.item_type%type;
5678 lv_item_key wf_items.item_key%type;
5679 lv_oaf_nav_attr wf_item_attribute_values.text_value%type;
5680 begin
5681 if g_debug then
5682 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
5683 end if;
5684
5685 -- code logic
5686
5687 if(p_transaction_id is not null) then
5688
5689 begin
5690 select item_type,item_key
5691 into lv_item_type,lv_item_key
5692 from hr_api_transactions
5693 where transaction_id=p_transaction_id;
5694 exception
5695 when no_data_found then
5696 -- get the fnd message and populate to the fnd message pub
5697 if(hr_multi_message.is_message_list_enabled) then
5698 fnd_message.set_name('PER', 'HR_SS_NO_TXN_DATA');
5699 hr_multi_message.add(p_message_type => hr_multi_message.G_ERROR_MSG );
5700 end if;
5701 hr_utility.raise_error;
5702 when others then
5703 raise;
5704 end; -- finished fetching record
5705
5706 -- do the processing logic
5707 begin
5708 -- check if we have valid item type and key
5709 if(lv_item_type is null or lv_item_key is null) then
5710 raise g_wf_not_initialzed;
5711 end if;
5712 -- check if the flow is used for page navigation
5713 -- HR_OAF_NAVIGATION_ATTR
5714 lv_oaf_nav_attr := wf_engine.getitemattrtext(lv_item_type,lv_item_key,'HR_OAF_NAVIGATION_ATTR',true);
5715 if(lv_oaf_nav_attr='Y') then
5716 -- process page navigation based wf approval submit
5717 processPageNavWFSubmit(p_transaction_id,p_approval_comments );
5718 else
5719 -- process wf approval submit with approvals only
5720 processNonPageNavWFSubmit(p_transaction_id,p_approval_comments );
5721 end if;
5722
5723 exception
5724 when g_wf_not_initialzed then
5725 -- get the fnd message and populate to the fnd message pub
5726 if(hr_multi_message.is_message_list_enabled) then
5727 fnd_message.set_name('PER', 'HR_SS_WF_NOT_INITIALZED');
5728 hr_multi_message.add(p_message_type => hr_multi_message.G_ERROR_MSG );
5729 end if;
5730 hr_utility.raise_error;
5731 when others then
5732 if hr_multi_message.unexpected_error_add(c_proc) then
5733 hr_utility.set_location(' Leaving:' || c_proc,40);
5734 raise;
5735 end if;
5736 end;
5737
5738 -- finally close the sfl open notifications if any
5739 hr_sflutil_ss.closeopensflnotification(p_transaction_id);
5740
5741 else
5742 -- get the fnd message and populate to the fnd message pub
5743 if(hr_multi_message.is_message_list_enabled) then
5744 fnd_message.set_name('PER', 'HR_SS_NULL_TXN_ID');
5745 hr_multi_message.add(p_message_type => hr_multi_message.G_ERROR_MSG );
5746 end if;
5747 hr_utility.raise_error;
5748 end if;
5749
5750
5751
5752 if (g_debug ) then
5753 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
5754 end if;
5755
5756 exception
5757 when others then
5758 raise;
5759 end processApprovalSubmit;
5760
5761 procedure resetWfPageFlowState(p_transaction_id in number)
5762 is
5763 lr_hr_api_transaction_rec hr_api_transactions%rowtype;
5764 ln_activity_id wf_item_attribute_values.number_value%type;
5765 ln_oaf_page_act_id WF_ITEM_ACTIVITY_STATUSES.process_activity%type;
5766 lv_oaf_nav_attr wf_item_attribute_values.text_value%type;
5767 begin
5768
5769 if(p_transaction_id is not null) then
5770 begin
5771 select * into lr_hr_api_transaction_rec
5772 from hr_api_transactions
5773 where transaction_id=p_transaction_id;
5774 exception
5775 when others then
5776 null;
5777 end;
5778
5779 -- check the status
5780 if(lr_hr_api_transaction_rec.status<>'W') then
5781 if(lr_hr_api_transaction_rec.item_key is not null) then
5782 -- check if the flow uses wf for page navigation
5783 -- HR_OAF_NAVIGATION_ATTR
5784 lv_oaf_nav_attr := wf_engine.getitemattrtext(lr_hr_api_transaction_rec.item_type,
5785 lr_hr_api_transaction_rec.item_key,
5786 'HR_OAF_NAVIGATION_ATTR',
5787 true);
5788 if(lv_oaf_nav_attr='Y') then
5789 -- for wf page navigation need to reset the wf state
5790 -- S, RIS,ROS,YS reset the state to saved page actid
5791 -- Y,RO,RI reset to first page activity id
5792 if(lr_hr_api_transaction_rec.status in ('S','RIS','ROS','YS')) then
5793 ln_activity_id := wf_engine.getitemattrnumber(lr_hr_api_transaction_rec.item_type,
5794 lr_hr_api_transaction_rec.item_key,
5795 'SAVED_ACTIVITY_ID',
5796 true);
5797 elsif(lr_hr_api_transaction_rec.status in ('RI','RO','Y')) then
5798 ln_activity_id := wf_engine.getitemattrnumber(lr_hr_api_transaction_rec.item_type,
5799 lr_hr_api_transaction_rec.item_key,
5800 'HR_FIRST_ACTIVITY_ID',
5801 true);
5802
5803 end if;
5804
5805 -- finally call wf engine handle to reset the state
5806 -- need to do only if the current activity id is not
5807 -- same as the ln_activity_id
5808 ln_oaf_page_act_id := hr_approval_ss.getoafpageactid(
5809 lr_hr_api_transaction_rec.item_type,
5810 lr_hr_api_transaction_rec.item_key);
5811 if(ln_activity_id is not null and ln_activity_id<>ln_oaf_page_act_id ) then
5812 WF_ENGINE.handleError(
5813 itemType => lr_hr_api_transaction_rec.item_type
5814 ,itemKey => lr_hr_api_transaction_rec.item_type
5815 ,activity => WF_ENGINE.GetActivityLabel(ln_activity_id)
5816 ,command => 'RETRY' ) ;
5817 end if;
5818 end if;
5819 end if;
5820 end if;
5821 end if;
5822
5823
5824 exception
5825 when others then
5826 null;
5827 end resetWfPageFlowState;
5828
5829
5830 procedure checktransactionState(p_transaction_id IN NUMBER)
5831
5832 is
5833 -- local variables
5834 c_proc constant varchar2(40) := 'checktransactionState';
5835 lv_status hr_api_transactions.status%type;
5836 lv_state hr_api_transactions.transaction_state%type;
5837 begin
5838 if g_debug then
5839 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
5840 end if;
5841
5842
5843 begin
5844 select status,transaction_state
5845 into lv_status,lv_state
5846 from hr_api_transactions
5847 where transaction_id=p_transaction_id;
5848
5849 -- check the transaction status
5850 if(lv_status in ('YS','RIS','ROS')) then
5851 -- saved transaction exists set the warning message
5852
5853 if(hr_multi_message.is_message_list_enabled) then
5854 hr_utility.set_message(800,'HR_SS_SAVED_TXN_DATA_EXISTS');
5855 hr_multi_message.add( p_message_type => hr_multi_message.G_WARNING_MSG);
5856 hr_utility.set_warning;
5857 end if;
5858 end if;
5859 if(lv_state is not null) then
5860 -- in advertant saved transaction exists set the warning message
5861
5862 if(hr_multi_message.is_message_list_enabled) then
5863 hr_utility.set_message(800,'HR_SS_INADV_TXN_DATA_EXISTS');
5864 hr_multi_message.add( p_message_type => hr_multi_message.G_WARNING_MSG);
5865 end if;
5866
5867 end if;
5868
5869 exception
5870 when others then
5871 null; -- do nothing
5872 end ;
5873
5874 if (g_debug ) then
5875 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 10);
5876 end if;
5877 exception
5878 when others then
5879 raise;
5880 end checktransactionState;
5881
5882 procedure handleApprovalErrors(p_item_type in wf_items.item_type%type,
5883 p_item_key in wf_items.item_key%type,
5884 error_message_text in varchar2)
5885 is
5886
5887 l_creator_disp_name wf_users.display_name%type;
5888 l_aprv_routing_username varchar2(60);
5889 lv_process_display_name wf_runnable_processes_v.display_name%type;
5890 c_application_id integer;
5891 c_transaction_id varchar2(25);
5892 c_transaction_type varchar2(25);
5893 l_forward_from_display_name wf_users.display_name%type;
5894 l_forward_to_display_name wf_users.display_name%type;
5895 c_approver_to_notify_rec ame_util.approverRecord2;
5896 lv_ntf_sub_msg wf_item_attribute_values.text_value%type;
5897
5898
5899 begin
5900
5901 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
5902 itemkey => p_item_key,
5903 aname => 'HR_AME_APP_ID_ATTR');
5904 c_application_id := nvl(c_application_id,800);
5905 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
5906 itemkey => p_item_key,
5907 aname => 'TRANSACTION_ID');
5908 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
5909 itemkey => p_item_key,
5910 aname => 'HR_AME_TRAN_TYPE_ATTR');
5911
5912 ame_api2.getadminapprover(applicationidin => c_application_id
5913 ,transactiontypein => c_transaction_type
5914 ,adminapproverout => c_approver_to_notify_rec);
5915
5916 l_aprv_routing_username := c_approver_to_notify_rec.name;
5917
5918 if(l_aprv_routing_username is null) then
5919 l_aprv_routing_username := wf_engine.GetItemAttrText(itemtype => p_item_type ,
5920 itemkey => p_item_key,
5921 aname => 'CREATOR_PERSON_USERNAME');
5922 end if;
5923 wf_engine.setitemattrtext(p_item_type,p_item_key,'APPROVAL_ROUTING_USERNAME1',l_aprv_routing_username);
5924
5925 l_forward_from_display_name := wf_engine.GetItemAttrText(itemtype => p_item_type ,
5926 itemkey => p_item_key,
5927 aname => 'FORWARD_FROM_DISPLAY_NAME');
5928 if(l_forward_from_display_name is null) then
5929 l_forward_from_display_name := wf_engine.GetItemAttrText(itemtype => p_item_type ,
5930 itemkey => p_item_key,
5931 aname => 'CREATOR_PERSON_DISPLAY_NAME');
5932 end if;
5933 wf_engine.setitemattrtext(p_item_type,p_item_key,'FORWARD_FROM_DISPLAY_NAME',l_forward_from_display_name);
5934
5935 l_forward_to_display_name := wf_engine.GetItemAttrText(itemtype => p_item_type ,
5936 itemkey => p_item_key,
5937 aname => 'FORWARD_TO_DISPLAY_NAME');
5938 if(l_forward_to_display_name is null) then
5939 l_forward_to_display_name := wf_engine.GetItemAttrText(itemtype => p_item_type ,
5940 itemkey => p_item_key,
5941 aname => 'CREATOR_PERSON_DISPLAY_NAME');
5942 end if;
5943 wf_engine.setitemattrtext(p_item_type,p_item_key,'FORWARD_TO_DISPLAY_NAME',l_forward_to_display_name);
5944
5945
5946 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_MESSAGE_TEXT',error_message_text);
5947 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_ITEM_TYPE',p_item_type);
5948 wf_engine.setitemattrtext(p_item_type,p_item_key,'ERROR_ITEM_KEY',p_item_key);
5949
5950
5951 lv_ntf_sub_msg := wf_engine.GetItemAttrText(itemtype => p_item_type ,
5952 itemkey => p_item_key,
5953 aname => 'HR_NTF_SUB_FND_MSG_ATTR',
5954 ignore_notfound=>true);
5955
5956 if(lv_ntf_sub_msg is null) then
5957 lv_process_display_name := hr_workflow_ss.getProcessDisplayName(p_item_type,p_item_key);
5958 else
5959 fnd_message.set_name('PER',lv_ntf_sub_msg);
5960 lv_process_display_name:= fnd_message.get;
5961 end if;
5962
5963 exception
5964 when others then
5965 if g_debug then
5966 hr_utility.set_location('Error in handleApprovalErrors SQLERRM' ||' '||to_char(SQLCODE),20);
5967 end if;
5968 raise;
5969 end handleApprovalErrors;
5970
5971 procedure updateRejectStatus( p_item_type in varchar2,
5972 p_item_key in varchar2,
5973 p_act_id in number,
5974 funmode in varchar2,
5975 result out nocopy varchar2)
5976
5977 is
5978 -- local variables
5979 c_proc constant varchar2(30) := 'updateRejectStatus';
5980 -- Variables required for AME API
5981 c_application_id integer;
5982 c_transaction_id varchar2(25);
5983 c_transaction_type varchar2(50);
5984 c_next_approvers ame_util.approverstable2;
5985 c_approvalprocesscompleteynout ame_util.charType;
5986 l_current_forward_to_username wf_users.name%type;
5987 itemClass varchar2(100);
5988 itemId varchar2(100);
5989 actionTypeId number;
5990 groupOrChainId number;
5991 occurrence number;
5992 notification_rec ame_util2.notificationRecord;
5993 l_trans_ref_table hr_api_transactions.transaction_ref_Table%type;
5994 l_current_forward_to_username1 wf_users.name%type;
5995
5996 l_is_parallel varchar2(10) := null;
5997 original_forward_to_user wf_users.name%type;
5998 l_role_name wf_users.name%type;
5999
6000 ntfidstartpos number := null;
6001 startpos number := null;
6002 endpos number := null;
6003 prll_ntf varchar2(600) := null;
6004
6005 CURSOR c_get_users_from_role (p_role_name VARCHAR2) IS
6006 select *
6007 from wf_user_roles
6008 where role_name = p_role_name;
6009
6010
6011 begin
6012
6013 g_debug := hr_utility.debug_enabled;
6014
6015 if g_debug then
6016 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
6017 end if;
6018
6019 begin
6020
6021 select transaction_ref_table into l_trans_ref_table
6022 from hr_api_transactions
6023 where item_type = p_item_type and item_key = p_item_key;
6024
6025 exception
6026 when no_data_found then
6027 l_trans_ref_table := null;
6028
6029 when others then
6030 l_trans_ref_table := null;
6031
6032 end;
6033
6034 if l_trans_ref_table = 'PER_APPRAISALS' then
6035 return;
6036 end if;
6037
6038
6039 if ( funmode = wf_engine.eng_run ) then
6040 -- check if it is AME or custom approvals
6041 c_application_id :=nvl(wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
6042 itemkey => p_item_key,
6043 aname => 'HR_AME_APP_ID_ATTR'),800);
6044
6045 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
6046 itemkey => p_item_key,
6047 aname => 'TRANSACTION_ID');
6048
6049 c_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
6050 itemkey => p_item_key,
6051 aname => 'HR_AME_TRAN_TYPE_ATTR');
6052 if(c_transaction_type is not null) then
6053 hr_utility.set_location('In(if ( if(c_transaction_type is not null))): '|| c_proc,2);
6054
6055 notification_rec.notification_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
6056 itemkey => p_item_key,
6057 aname => 'HR_CONTEXT_NID_ATTR');
6058 notification_rec.user_comments := wf_notification.getattrtext(
6059 notification_rec.notification_id
6060 ,'WF_NOTE');
6061
6062 l_is_parallel := wf_engine.GetItemAttrText(itemtype => p_item_type ,
6063 itemkey => p_item_key,
6064 aname => 'HR_PARALLEL_ATTR',
6065 ignore_notfound=>true);
6066
6067 if(l_is_parallel = 'PARALLEL') then
6068
6069 if g_debug then
6070 hr_utility.set_location('Approval mode is parallel', 1);
6071 end if;
6072 if(notification_rec.notification_id is not null) then
6073
6074 select ORIGINAL_RECIPIENT into original_forward_to_user from wf_notifications where notification_id = notification_rec.notification_id;
6075
6076 prll_ntf := wf_engine.getitemattrtext(p_item_type,
6077 p_item_key,
6078 'PRLL_TRNSFR_DET',true);
6079
6080 if( INSTR(prll_ntf,notification_rec.notification_id) > 0) then
6081
6082 ntfidstartpos := instr(prll_ntf, notification_rec.notification_id,1,1);
6083 startpos := instr(prll_ntf,':',ntfidstartpos,1);
6084 endpos := instr(prll_ntf,'|',ntfidstartpos,1);
6085 original_forward_to_user := substr(prll_ntf,startpos+1, endpos-startpos-1 );
6086
6087 end if;
6088
6089 l_current_forward_to_username := original_forward_to_user;
6090 l_current_forward_to_username1 := original_forward_to_user;
6091
6092 ame_api2.getNextApprovers4
6093 (applicationIdIn => c_application_id
6094 ,transactionTypeIn => c_transaction_type
6095 ,transactionIdIn => c_transaction_id
6096 ,flagApproversAsNotifiedIn=>ame_util.booleanFalse
6097 ,approvalProcessCompleteYNOut => c_approvalprocesscompleteynout
6098 ,nextApproversOut => c_next_approvers);
6099
6100 for l_index in 1 .. c_next_approvers.count
6101 loop
6102
6103 if(c_next_approvers(l_index).orig_system = 'PQH_ROLE' OR c_next_approvers(l_index).orig_system = 'POS') then
6104 for c in c_get_users_from_role(c_next_approvers(l_index).name) loop
6105 if(c.user_name = original_forward_to_user) then
6106 l_current_forward_to_username := c.role_name;
6107 end if;
6108 end loop;
6109 end if;
6110
6111 end loop;
6112
6113
6114 end if;
6115
6116 else
6117 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => p_item_type
6118 ,itemkey => p_item_key
6119 ,aname => 'FORWARD_TO_USERNAME');
6120 end if;
6121
6122 l_current_forward_to_username := nvl(l_current_forward_to_username,wf_engine.GetItemAttrText(itemtype => p_item_type ,
6123 itemkey => p_item_key,
6124 aname => 'RETURN_TO_USERNAME'));
6125
6126
6127 if g_debug then
6128 hr_utility.set_location('calling ame_api6.updateApproveStatus2 with Reject status', 3);
6129 hr_utility.set_location('c_application_id:'|| c_application_id, 4);
6130 hr_utility.set_location('c_transaction_type:'|| c_transaction_type, 5);
6131 hr_utility.set_location('approvalStatusIn:'|| ame_util.approvedStatus, 6);
6132 hr_utility.set_location('approverNameIn:'|| l_current_forward_to_username, 7);
6133 end if;
6134
6135 begin
6136 ame_api6.updateApprovalStatus2(applicationIdIn=>c_application_id,
6137 transactionTypeIn =>c_transaction_type,
6138 transactionIdIn=>c_transaction_id,
6139 approvalStatusIn =>ame_util.rejectStatus,
6140 approverNameIn =>l_current_forward_to_username,
6141 itemClassIn => null,
6142 itemIdIn => null,
6143 actionTypeIdIn=> null,
6144 groupOrChainIdIn => null,
6145 occurrenceIn => null,
6146 notificationIn => notification_rec,
6147 forwardeeIn =>ame_util.emptyApproverRecord2,
6148 updateItemIn =>false);
6149
6150 exception
6151 when others then
6152 if g_debug then
6153 hr_utility.set_location('Error in updateRejectStatus SQLERRM' ||' '||to_char(SQLCODE),10);
6154 end if;
6155 raise;
6156 end ;
6157
6158
6159 if g_debug then
6160 hr_utility.set_location('returned from calling ame_api2.updateApprovalStatus2', 8);
6161 end if;
6162
6163 if(l_is_parallel = 'PARALLEL') then
6164 wf_engine.setItemAttrText(p_item_type,p_item_key,'FORWARD_TO_DISPLAY_NAME',wf_directory.GetRoleDisplayName(l_current_forward_to_username1));
6165 end if;
6166
6167 result := wf_engine.eng_trans_default;
6168 else
6169 null;
6170 end if;
6171 /*
6172 -- transfer the approval action to history
6173 -- call PQH_SS_HISTORY.transfer_approval_to_history
6174 updateApprovalHistory( p_item_type=>p_item_type,
6175 p_item_key=>p_item_key,
6176 p_act_id=>p_act_id,
6177 funmode=>funmode,
6178 result=>result);*/
6179
6180
6181 end if;
6182
6183 if (g_debug ) then
6184 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 20);
6185 end if;
6186 exception
6187 when others then
6188 if g_debug then
6189 hr_utility.set_location('Error in updateRejectStatus SQLERRM' ||' '||to_char(SQLCODE),30);
6190 end if;
6191 raise;
6192 end updateRejectStatus;
6193
6194 function getToString(p_item_key in varchar2)
6195 return varchar2 is
6196 -- local variables
6197 c_proc constant varchar2(30) := 'getToString';
6198 toString varchar2(30);
6199
6200 begin
6201 g_debug := hr_utility.debug_enabled;
6202 if g_debug then
6203 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
6204 end if;
6205
6206 begin
6207 select lower(meaning) into toString from hr_lookups where LOOKUP_TYPE = 'BEN_TO_FROM' and lookup_code = 'TO';
6208 exception
6209 when no_data_found then
6210 toString := 'to ';
6211 end;
6212
6213 return toString || ' ';
6214 exception
6215 when others then
6216 if g_debug then
6217 hr_utility.set_location('Error in getToString SQLERRM' ||' '||to_char(SQLCODE),20);
6218 end if;
6219
6220 end getToString;
6221
6222
6223 END HR_APPROVAL_SS;
6224