[Home] [Help]
PACKAGE BODY: APPS.HR_WORKFLOW_SS
Source
1 PACKAGE BODY hr_workflow_ss AS
2 /* $Header: hrwkflss.pkb 120.19 2010/06/04 07:02:10 gpurohit ship $ */
3 /*
4 This package contails new (v4.0+)workflow related business logic
5 */
6 --
7 -- Package Variables
8 --
9 g_package varchar2(33) := 'hr_workflow_ss.';
10 g_asg_api_name constant varchar2(80)
11 default 'HR_PROCESS_ASSIGNMENT_SS.PROCESS_API';
12 -- Salary Basis Enhancement Change Begins
13 g_mid_pay_period_change constant varchar2(30) := 'HR_MID_PAY_PERIOD_CHANGE';
14 g_oa_media constant varchar2(100) DEFAULT fnd_web_config.web_server||'OA_MEDIA/';
15 g_oa_html constant varchar2(100) DEFAULT fnd_web_config.jsp_agent;
16
17 g_debug boolean := hr_utility.debug_enabled;
18 gv_item_name VARCHAR2(100) DEFAULT 'ADDITIONAL_APPROVER_';
19 -- cursor determines if an attribute exists
20 cursor csr_wiav (p_item_type in varchar2
21 ,p_item_key in varchar2
22 ,p_name in varchar2)
23 IS
24 select 1
25 from wf_item_attribute_values wiav
26 where wiav.item_type = p_item_type
27 and wiav.item_key = p_item_key
28 and wiav.name = p_name;
29
30 --
31 -- ----------------------------------------------------------------------------
32 -- |-------------------------< branch_on_approval_flag>------------------------|
33 -- ----------------------------------------------------------------------------
34 -- Purpose: This procedure will read the HR_RUNTIME_APPROVAL_REQ_FLAG item level
35 -- attribute value and branch accordingly. This value will be set by the review
36 -- page by reading its attribute level attribute HR_APPROVAL_REQ_FLAG
37 -- (YES/NO/YES_DYNAMIC)
38 -- For
39 -- YES => branch with Yes result
40 -- YES_DYNAMIC => branch with Yes result
41 -- NO => branch with No result
42 -- ----------------------------------------------------------------------------
43 PROCEDURE branch_on_approval_flag
44 (itemtype in varchar2
45 ,itemkey in varchar2
46 ,actid in number
47 ,funcmode in varchar2
48 ,resultout out nocopy varchar2)
49 is
50 l_text_value wf_activity_attr_values.text_value%type;
51 l_trans_ref_table hr_api_transactions.transaction_ref_Table%type;
52 begin
53 --
54 l_text_value := wf_engine.GetItemAttrText(itemtype => itemtype,
55 itemkey => itemkey,
56 aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG');
57 if ( l_text_value in ('YES_DYNAMIC', 'YES','Y','YD')) then
58 -- Approval Process is required
59 resultout := 'COMPLETE:'|| 'Y';
60 /* elsif l_text_value = 'YES' then
61 -- Approval Process is required
62 resultout := 'COMPLETE:'|| 'Y';*/
63 select transaction_ref_table into l_trans_ref_table
64 from hr_api_transactions
65 where item_type=itemtype and item_key=itemkey;
66
67 if l_trans_ref_table = 'IRC_OFFERS' then
68 wf_engine.setItemAttrDate(itemtype => itemtype,
69 itemkey => itemkey,
70 aname => 'CURRENT_EFFECTIVE_DATE',
71 avalue => trunc(sysdate)
72 );
73 end if;
74
75 else
76 -- Approval is not required
77 resultout := 'COMPLETE:'|| 'N';
78 end if;
79 --
80 EXCEPTION
81 WHEN OTHERS THEN
82 WF_CORE.CONTEXT(g_package
83 ,'branch_on_approval_flag'
84 ,itemtype
85 ,itemkey
86 ,to_char(actid)
87 ,funcmode);
88 RAISE;
89 end branch_on_approval_flag;
90 --
91
92 -- ----------------------------------------------------------------------------
93 -- |----------------------< set_rejected_by_payroll > -------------------------|
94 -- ----------------------------------------------------------------------------
95 -- Purpose: This procedure will set the item attribute HR_REJECTED_BY_PAYROLL
96 -- to 'Y'.
97 -- ----------------------------------------------------------------------------
98 PROCEDURE set_rejected_by_payroll
99 (itemtype in varchar2
100 ,itemkey in varchar2
101 ,actid in number
102 ,funcmode in varchar2
103 ,resultout out nocopy varchar2)
104 is
105
106 begin
107 --
108 IF ( funcmode = 'RUN' )
109 THEN
110 wf_engine.SetItemAttrText(itemtype => itemtype
111 ,itemkey => itemkey
112 ,aname => 'HR_REJECTED_BY_PAYROLL'
113 ,avalue => 'Y');
114
115 resultout := 'COMPLETE:';
116 ELSE
117 --
118 NULL;
119 --
120 END IF;
121
122
123 --
124 EXCEPTION
125 WHEN OTHERS THEN
126 WF_CORE.CONTEXT(g_package
127 ,'set_rejected_by_payroll'
128 ,itemtype
129 ,itemkey
130 ,to_char(actid)
131 ,funcmode);
132 RAISE;
133 end set_rejected_by_payroll;
134 --
135
136 -- ----------------------------------------------------------------------------
137 -- |----------------------- < copy_payroll_comment > -------------------------|
138 -- ----------------------------------------------------------------------------
139 -- Purpose: This procedure will populate the wf_note from WF and set the item
140 -- attribute HR_SALBASISCHG_PAYROLL_COMMENT with that value.
141 -- ----------------------------------------------------------------------------
142 PROCEDURE copy_payroll_comment
143 (itemtype in varchar2
144 ,itemkey in varchar2
145 ,actid in number
146 ,funcmode in varchar2
147 ,resultout out nocopy varchar2)
148 IS
149 lv_prev_payroll_comment varchar2(32000) default null;
150
151 BEGIN
152 --
153 IF ( funcmode = 'RUN' )
154 THEN
155 --
156 -- Save the previous comment from Payroll first
157 lv_prev_payroll_comment := wf_engine.GetItemAttrText
158 (itemtype => itemtype,
159 itemkey => itemkey,
160 aname => 'HR_SALBASISCHG_PAYROLL_COMMENT');
161
162
163 IF lv_prev_payroll_comment is NOT NULL
164 THEN
165 lv_prev_payroll_comment := lv_prev_payroll_comment || '<br>';
166 END IF;
167
168 wf_engine.SetItemAttrText
169 (itemtype => itemtype,
170 itemkey => itemkey,
171 aname => 'HR_SALBASISCHG_PAYROLL_COMMENT',
172 avalue => lv_prev_payroll_comment ||
173 wf_engine.GetItemAttrText (itemtype => itemtype,
174 itemkey => itemkey,
175 aname => 'WF_NOTE'));
176 --
177 resultout := 'COMPLETE:';
178 ELSE
179 NULL;
180 END IF;
181 --
182
183 --
184 EXCEPTION
185 WHEN OTHERS THEN
186 WF_CORE.CONTEXT(g_package
187 ,'copy_payroll_comment'
188 ,itemtype
189 ,itemkey
190 ,to_char(actid)
191 ,funcmode);
192 RAISE;
193 end copy_payroll_comment;
194 --
195
196 -------------------------------------------------------------------------------
197 --------- function get_item_type --------------------------------------------
198
199 ---------- private function to get item type for current transaction ---------
200 -------------------------------------------------------------------------------
201 function get_item_type
202 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
203 return varchar2 is
204 c_item_type varchar2(50);
205
206 begin
207
208 begin
209 if g_debug then
210 hr_utility.set_location('querying hr_api_transactions.item_type for p_transaction_id:'||p_transaction_id, 2);
211 end if;
212 select t.item_type
213 into c_item_type
214 from hr_api_transactions t
215 where transaction_id=get_item_type.p_transaction_id;
216 exception
217 when no_data_found then
218 -- get the data from the steps
219 if g_debug then
220 hr_utility.set_location('querying hr_api_transaction_steps.item_type for p_transaction_id:'||p_transaction_id, 2);
221 end if;
222 select ts.item_type
223 into get_item_type.c_item_type
224 from hr_api_transaction_steps ts
225 where ts.transaction_id=get_item_type.p_transaction_id
226 and ts.item_type is not null and rownum <=1;
227 end;
228
229 --return c_item_type;
230 return nvl(c_item_type,'-1');
231 EXCEPTION
232 WHEN OTHERS THEN
233 WF_CORE.CONTEXT(g_package,'.get_item_type',p_transaction_id);
234 RAISE;
235
236 end get_item_type;
237
238
239
240 -------------------------------------------------------------------------------
241 --------- function get_item_key --------------------------------------------
242 ---------- private function to get item key for current transaction ---------
243 -------------------------------------------------------------------------------
244
245 function get_item_key
246 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
247 return varchar2 is
248 c_item_key varchar2(50);
249
250 begin
251
252 begin
253 if g_debug then
254 hr_utility.set_location('querying hr_api_transactions.item_type for p_transaction_id:'||p_transaction_id, 2);
255 end if;
256 select t.item_key
257 into get_item_key.c_item_key
258 from hr_api_transactions t
259 where transaction_id=get_item_key.p_transaction_id;
260 exception
261 when no_data_found then
262 -- get the data from the steps
263 if g_debug then
264 hr_utility.set_location('querying hr_api_transaction_steps.item_type for p_transaction_id:'||p_transaction_id, 2);
265 end if;
266 select ts.item_key
267 into get_item_key.c_item_key
268 from hr_api_transaction_steps ts
269 where ts.transaction_id=get_item_key.p_transaction_id
270 and ts.item_type is not null and rownum <=1;
271 end;
272
273 --return get_item_key.c_item_key;
274 return nvl(get_item_key.c_item_key,'-1');
275 EXCEPTION
276 WHEN OTHERS THEN
277 WF_CORE.CONTEXT(g_package,'.get_item_key',p_transaction_id);
278 RAISE;
279
280 end get_item_key;
281
282 function get_process_name
283 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
284 return varchar2 is
285
286 c_process_name varchar2(100);
287 c_item_type varchar2(50);
288 c_item_key varchar2(100);
289
290 begin
291
292 c_item_type := get_item_type(p_transaction_id);
293 c_item_key := get_item_key(p_transaction_id);
294 c_process_name := wf_engine.GetItemAttrText (itemtype => c_item_type ,
295 itemkey => c_item_key,
296 aname => 'PROCESS_NAME',
297 ignore_notfound => TRUE);
298 return c_process_name;
299 EXCEPTION
300 WHEN OTHERS THEN
301 WF_CORE.CONTEXT(g_package,'.get_process_name',c_item_type,c_item_key);
302 RAISE;
303 end get_process_name ;
304
305 function get_approval_level
306 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
307 return number is
308
309 c_approval_level number;
310 c_item_type varchar2(50);
311 c_item_key varchar2(100);
312
313 begin
314
315 c_item_type := get_item_type(p_transaction_id);
316 c_item_key := get_item_key(p_transaction_id);
317
318 c_approval_level := wf_engine.GetItemAttrNumber (itemtype => c_item_type ,
319 itemkey => c_item_key ,
320 aname => 'APPROVAL_LEVEL');
321 return c_approval_level;
322 EXCEPTION
323 WHEN OTHERS THEN
324 WF_CORE.CONTEXT(g_package,'.get_approval_level',c_item_type,c_item_key);
325 RAISE;
326
327
328 end get_approval_level ;
329
330
331
332 function get_effective_date
333 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
334 return date is
335 c_effective_date date;
336 c_item_type varchar2(50);
337 c_item_key varchar2(100);
338 begin
339
340 c_item_type := get_item_type(p_transaction_id);
341 c_item_key := get_item_key(p_transaction_id);
342
343 c_effective_date := wf_engine.GetItemAttrDate(itemtype => c_item_type ,
344 itemkey => c_item_key,
345 aname => 'CURRENT_EFFECTIVE_DATE');
346
347 return c_effective_date;
348 EXCEPTION
349 WHEN OTHERS THEN
350 WF_CORE.CONTEXT(g_package,'.get_effective_date',c_item_type,c_item_key);
351 RAISE;
352
353
354 end get_effective_date;
355
356 function get_assignment_id
357 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
358 return number is
359 c_assignment_id number;
360 c_item_type varchar2(50);
361 c_item_key varchar2(100);
362
363 begin
364
365 c_item_type := get_item_type(p_transaction_id);
366 c_item_key := get_item_key(p_transaction_id);
367
368 c_assignment_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
369 itemkey => c_item_key,
370 aname => 'CURRENT_ASSIGNMENT_ID');
371 return c_assignment_id;
372 EXCEPTION
373 WHEN OTHERS THEN
374 WF_CORE.CONTEXT(g_package,'.get_assignment_id',c_item_type,c_item_key);
375 RAISE;
376
377
378 end get_assignment_id ;
379
380
381
382 -- ------------------------------------------------------------------------
383 -- |------------------------< Get_next_approver >-------------------------|
384 -- ------------------------------------------------------------------------
385 --
386 -- Description
387 --
388 -- Get the next approver in the chain
389 -- This procedure confirms to the Workflow API specification standards.
390 --
391 --
392 procedure Get_Next_Approver ( itemtype in varchar2,
393 itemkey in varchar2,
394 actid in number,
395 funmode in varchar2,
396 result out nocopy varchar2 )
397 as
398 -- -------------------------------------------------------------------------
399 -- local variables
400 -- -------------------------------------------------------------------------
401 l_creator_person_id per_people_f.person_id%type;
402 l_forward_from_person_id per_people_f.person_id%type;
403 l_forward_from_username wf_users.name%type;
404 l_forward_from_disp_name wf_users.display_name%type;
405 l_forward_to_person_id per_people_f.person_id%type;
406 l_forward_to_username wf_users.name%type;
407 l_forward_to_disp_name wf_users.display_name%type;
408 l_proc_name varchar2(61) := g_package||'get_next_approver';
409 l_current_forward_to_id per_people_f.person_id%type;
410 l_current_forward_from_id per_people_f.person_id%type;
411
412 -- Variables required for AME API
413 c_application_id integer;
414 c_transaction_id varchar2(25);
415 c_transaction_type varchar2(25);
416 c_next_approver_rec ame_util.approverRecord;
417
418 v_approvalprocesscompleteynout varchar2(5);
419 v_next_approver_rec ame_util.approverstable2;
420
421 begin
422
423 --
424
425 if ( funmode = 'RUN' ) then
426
427 -- get the current forward from person
428 l_current_forward_from_id :=
429 nvl(wf_engine.GetItemAttrNumber
430 (itemtype => itemtype
431 ,itemkey => itemkey
432 ,aname => 'FORWARD_FROM_PERSON_ID'),
433 wf_engine.GetItemAttrNumber
434 (itemtype => itemtype
435 ,itemkey => itemkey
436 ,aname => 'CREATOR_PERSON_ID'));
437 -- get the current forward to person
438 l_current_forward_to_id :=
439 nvl(wf_engine.GetItemAttrNumber
440 (itemtype => itemtype
441 ,itemkey => itemkey
442 ,aname => 'FORWARD_TO_PERSON_ID'),
443 wf_engine.GetItemAttrNumber
444 (itemtype => itemtype
445 ,itemkey => itemkey
446 ,aname => 'CREATOR_PERSON_ID'));
447
448
449
450
451
452 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => itemtype ,
453 itemkey => itemkey,
454 aname => 'HR_AME_APP_ID_ATTR');
455
456 c_application_id := nvl(c_application_id,800);
457
458
459
460 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => itemtype ,
461 itemkey => itemkey,
462 aname => 'TRANSACTION_ID');
463
464
465
466 c_transaction_type := wf_engine.GetItemAttrText(itemtype => itemtype ,
467 itemkey => itemkey,
468 aname => 'HR_AME_TRAN_TYPE_ATTR');
469
470
471 /*
472 ame_api.getNextApprover(applicationIdIn =>c_application_id,
473 transactionIdIn =>c_transaction_id,
474 transactionTypeIn =>c_transaction_type,
475 nextApproverOut =>c_next_approver_rec); */
476
477 ame_api2.getNextApprovers4
478 (applicationIdIn => c_application_id
479 ,transactionTypeIn => c_transaction_type
480 ,transactionIdIn => c_transaction_id
481 ,flagApproversAsNotifiedIn=>ame_util.booleanFalse
482 ,approvalProcessCompleteYNOut => v_approvalprocesscompleteynout
483 ,nextApproversOut => v_next_approver_rec);
484 --
485 -- set the next forward to
486 --
487 if(v_approvalprocesscompleteynout<>'Y') then
488 l_forward_to_person_id := v_next_approver_rec(1).orig_system_id;
489 end if;
490
491
492 if(l_forward_to_person_id is null) then
493 result := 'COMPLETE:F';
494
495 else
496 --
497 wf_directory.GetUserName
498 (p_orig_system => 'PER'
499 ,p_orig_system_id => l_forward_to_person_id
500 ,p_name => l_forward_to_username
501 ,p_display_name => l_forward_to_disp_name);
502 --
503 wf_engine.SetItemAttrNumber
504 (itemtype => itemtype
505 ,itemkey => itemkey
506 ,aname => 'FORWARD_TO_PERSON_ID'
507 ,avalue => l_forward_to_person_id);
508 --
509 wf_engine.SetItemAttrText
510 (itemtype => itemtype
511 ,itemkey => itemkey
512 ,aname => 'FORWARD_TO_USERNAME'
513 ,avalue => l_forward_to_username);
514 --
515 Wf_engine.SetItemAttrText
516 (itemtype => itemtype
517 ,itemkey => itemkey
518 ,aname => 'FORWARD_TO_DISPLAY_NAME'
519 ,avalue => l_forward_to_disp_name);
520 --
521 -- set forward from to old forward to
522 --
523
524 wf_engine.SetItemAttrNumber
525 (itemtype => itemtype
526 ,itemkey => itemkey
527 ,aname => 'FORWARD_FROM_PERSON_ID'
528 ,avalue => l_current_forward_to_id);
529 --
530 -- Get the username and display name for forward from person
531 -- and save to item attributes
532 --
533 wf_directory.GetUserName
534 (p_orig_system => 'PER'
535 ,p_orig_system_id => l_current_forward_to_id
536 ,p_name => l_forward_from_username
537 ,p_display_name => l_forward_from_disp_name);
538 --
539 wf_engine.SetItemAttrText
540 (itemtype => itemtype
541 ,itemkey => itemkey
542 ,aname => 'FORWARD_FROM_USERNAME'
543 ,avalue => l_forward_from_username);
544 --
545 wf_engine.SetItemAttrText
546 (itemtype => itemtype
547 ,itemkey => itemkey
548 ,aname => 'FORWARD_FROM_DISPLAY_NAME'
549 ,avalue => l_forward_from_disp_name);
550 --
551
552 result := 'COMPLETE:T';
553
554 end if;
555
556 elsif ( funmode = 'CANCEL' ) then
557 --
558 null;
559 --
560 end if;
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 WF_CORE.CONTEXT(g_package,'.Get_Next_Approver',itemtype,itemkey,funmode);
565 RAISE;
566
567
568 end Get_Next_Approver;
569
570
571 --
572 -- ------------------------------------------------------------------------
573 -- |------------------------< update_approval_status >-------------------------|
574 -- ------------------------------------------------------------------------
575 --
576 -- Description
577 --
578 -- Update the status of the current approvers' approval notification
579 -- This procedure confirms to the Workflow API specification standards.
580 --
581 --
582 procedure update_approval_status ( itemtype in varchar2,
583 itemkey in varchar2,
584 actid in number,
585 funmode in varchar2,
586 result out nocopy varchar2 )
587 as
588
589 l_forward_to_person_id per_people_f.person_id%type;
590
591 -- Variables required for AME API
592 c_application_id integer;
593 c_transaction_id varchar2(25);
594 c_transaction_type varchar2(25);
595 c_next_approver_rec ame_util.approverRecord;
596
597 l_current_forward_to_username wf_users.name%type;
598
599 begin
600
601 --
602
603 if ( funmode = 'RUN' ) then
604
605 c_application_id :=wf_engine.GetItemAttrNumber(itemtype => itemtype ,
606 itemkey => itemkey,
607 aname => 'HR_AME_APP_ID_ATTR');
608
609 c_application_id := nvl(c_application_id,800);
610
611 c_transaction_id := wf_engine.GetItemAttrNumber(itemtype => itemtype ,
612 itemkey => itemkey,
613 aname => 'TRANSACTION_ID');
614
615
616
617 c_transaction_type := wf_engine.GetItemAttrText(itemtype => itemtype ,
618 itemkey => itemkey,
619 aname => 'HR_AME_TRAN_TYPE_ATTR');
620
621
622 l_forward_to_person_id := wf_engine.GetItemAttrNumber
623 (itemtype => itemtype,
624 itemkey => itemkey,
625 aname => 'FORWARD_TO_PERSON_ID');
626 /*
627 ame_api.updateApprovalStatus2(applicationIdIn =>c_application_id,
628 transactionIdIn =>c_transaction_id,
629 approvalStatusIn =>ame_util.approvedStatus,
630 approverPersonIdIn =>l_forward_to_person_id,
631 approverUserIdIn =>null,
632 transactionTypeIn =>c_transaction_type,
633 forwardeeIn =>null); */
634
635
636 l_current_forward_to_username:= Wf_engine.GetItemAttrText(itemtype => itemtype
637 ,itemkey => itemkey
638 ,aname => 'FORWARD_TO_USERNAME');
639
640 l_current_forward_to_username := nvl(l_current_forward_to_username,wf_engine.GetItemAttrText(itemtype => itemtype ,
641 itemkey => itemkey,
642 aname => 'RETURN_TO_USERNAME'));
643
644 ame_api2.updateApprovalStatus2(applicationIdIn=>c_application_id,
645 transactionTypeIn =>c_transaction_type,
646 transactionIdIn=>c_transaction_id,
647 approvalStatusIn =>ame_util.approvedStatus,
648 approverNameIn =>l_current_forward_to_username,
649 itemClassIn => null,
650 itemIdIn =>null,
651 actionTypeIdIn=> null,
652 groupOrChainIdIn =>null,
653 occurrenceIn =>null,
654 forwardeeIn =>ame_util.emptyApproverRecord2,
655 updateItemIn =>false);
656
657 elsif ( funmode = 'CANCEL' ) then
658 --
659 null;
660 --
661 end if;
662 EXCEPTION
663 WHEN OTHERS THEN
664 WF_CORE.CONTEXT(g_package,'.update_approval_status',itemtype,itemkey,funmode);
665 RAISE;
666
667
668
669 end update_approval_status;
670
671 -------------------------------------------------------------------------------
672 --------- function get_final_approver --------------------------------------------
673 ---------- Function to get the final approver from the supervisor chain for current transaction ---------
674 -------------------------------------------------------------------------------
675
676 function get_final_approver
677 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
678 return number
679 is
680 c_item_type varchar2(50);
681 c_item_key number;
682 c_creator_person_id per_all_people_f.person_id%type default null;
683 c_final_appprover_id per_all_people_f.person_id%type default null;
684 c_forward_to_person_id per_all_people_f.person_id%type default null;
685 lv_response varchar2(3);
686
687 begin
688
689 c_item_type := get_item_type(p_transaction_id);
690 c_item_key := get_item_key(p_transaction_id);
691
692 -- bug 4333335 begins
693 hr_approval_custom.g_itemtype := c_item_type;
694 hr_approval_custom.g_itemkey := c_item_key;
695 -- bug 4333335 ends
696
697 /*c_creator_person_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
698 itemkey => c_item_key,
699 aname => 'CREATOR_PERSON_ID');
700 */
701 c_creator_person_id := getApprStartingPointPersonId(p_transaction_id);
702 c_final_appprover_id := c_creator_person_id;
703
704 lv_response := hr_approval_custom.Check_Final_approver(p_forward_to_person_id => c_creator_person_id,
705 p_person_id => c_creator_person_id );
706
707
708 while lv_response='N' loop
709
710 c_forward_to_person_id := hr_approval_custom.Get_Next_Approver(p_person_id =>c_final_appprover_id);
711
712 c_final_appprover_id := c_forward_to_person_id;
713
714 lv_response := hr_approval_custom.Check_Final_approver(p_forward_to_person_id => c_forward_to_person_id,
715 p_person_id => c_creator_person_id );
716
717 end loop;
718
719 return c_final_appprover_id;
720 EXCEPTION
721 WHEN OTHERS THEN
722 WF_CORE.CONTEXT(g_package,'.get_final_approver',c_item_type,c_item_key);
723 RAISE;
724
725
726 end get_final_approver;
727
728
729
730 function allow_requestor_approval
731 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
732 return varchar2 is
733
734 c_item_type varchar2(50);
735 c_item_key varchar2(100);
736 c_final_approver number;
737 c_creator_person_id number;
738
739 begin
740
741 c_item_type := get_item_type(p_transaction_id);
742 c_item_key := get_item_key(p_transaction_id);
743 c_final_approver := get_final_approver(p_transaction_id);
744 /*c_creator_person_id := wf_engine.GetItemAttrNumber(itemtype => c_item_type ,
745 itemkey => c_item_key,
746 aname => 'CREATOR_PERSON_ID');
747 */
748 c_creator_person_id := getApprStartingPointPersonId(p_transaction_id);
749 if(c_final_approver=c_creator_person_id) then
750 return 'true';
751 else return 'false';
752 end if;
753
754
755 EXCEPTION
756 WHEN OTHERS THEN
757 WF_CORE.CONTEXT(g_package,'.allow_requestor_approval',c_item_type,c_item_key);
758 RAISE;
759 end allow_requestor_approval ;
760
761 --
762 -- ------------------------------------------------------------------------
763 -- |------------------ < check_mid_pay_period_change > --------------------|
764 -- ------------------------------------------------------------------------
765 --
766 -- Description
767 --
768 -- Determine if a mid pay period change was performed when a salary basis
769 -- was changed. If yes, we need to set the WF item attribute
770 -- HR_MID_PAY_PERIOD_CHANGE ='Y' so that a notification will be sent to the
771 -- Payroll Contact.
772 --
773 -- This procedure is invoked by the WF HR_CHK_SAL_BASIS_MID_PAY_PERIOD process.
774 --
775 -- ------------------------------------------------------------------------
776 procedure check_mid_pay_period_change
777 (p_item_type in varchar2,
778 p_item_key in varchar2,
779 p_act_id in number,
780 funmode in varchar2,
781 result out nocopy varchar2 ) IS
782
783
784 l_assignment_id per_all_assignments_f.assignment_id%type default null;
785 l_payroll_id per_all_assignments_f.payroll_id%type default null;
786 l_old_pay_basis_id per_all_assignments_f.pay_basis_id%type default null;
787 l_new_pay_basis_id per_all_assignments_f.pay_basis_id%type default null;
788 l_pay_period_start_date date default null;
789 l_pay_period_end_date date default null;
790
791 l_asg_txn_step_id hr_api_transaction_steps.transaction_step_id%type
792 default null;
793 l_effective_date date default null;
794
795
796 CURSOR csr_check_mid_pay_period(p_eff_date_csr in date
797 ,p_payroll_id_csr in number) IS
798 select start_date, end_date
799 from per_time_periods
800 where p_eff_date_csr > start_date
801 and p_eff_date_csr <= end_date
802 and payroll_id = p_payroll_id_csr;
803
804 -- The following cursor is copied from hr_transaction_ss.process_transaction.
805 CURSOR csr_trs is
806 select trs.transaction_step_id
807 ,trs.api_name
808 ,trs.item_type
809 ,trs.item_key
810 ,trs.activity_id
811 ,trs.creator_person_id
812 from hr_api_transaction_steps trs
813 where trs.item_type = p_item_type
814 and trs.item_key = p_item_key
815 order by trs.processing_order
816 ,trs.transaction_step_id ; --#2313279
817 --
818
819 -- Get existing assignment data
820 CURSOR csr_get_old_asg_data IS
821 SELECT pay_basis_id
822 FROM per_all_assignments_f
823 WHERE assignment_id = l_assignment_id
824 AND l_effective_date between effective_start_date
825 and effective_end_date
826 AND assignment_type = 'E';
827
828
829 BEGIN
830 IF ( funmode = 'RUN' )
831 THEN
832
833 -- Get the ASG and Pay Rate transaction step id
834 FOR I in csr_trs
835 LOOP
836 IF I.api_name = g_asg_api_name
837 THEN
838 l_asg_txn_step_id := I.transaction_step_id;
839 EXIT;
840 END IF;
841 END LOOP;
842 IF l_asg_txn_step_id IS NOT NULL
843 THEN
844
845 l_effective_date := to_date(
846 hr_transaction_ss.get_wf_effective_date
847 (p_transaction_step_id => l_asg_txn_step_id),
848 hr_transaction_ss.g_date_format);
849
850 -- Get the pay_basis_id and payroll_id
851 l_new_pay_basis_id := hr_transaction_api.get_number_value
852 (p_transaction_step_id => l_asg_txn_step_id
853 ,p_name => 'P_PAY_BASIS_ID');
854
855 l_payroll_id := hr_transaction_api.get_number_value
856 (p_transaction_step_id => l_asg_txn_step_id
857 ,p_name => 'P_PAYROLL_ID');
858
859 l_assignment_id := hr_transaction_api.get_number_value
860 (p_transaction_step_id => l_asg_txn_step_id
861 ,p_name => 'P_ASSIGNMENT_ID');
862
863 -- Now get the old pay basis id
864 OPEN csr_get_old_asg_data;
865 FETCH csr_get_old_asg_data into l_old_pay_basis_id;
866 IF csr_get_old_asg_data%NOTFOUND
867 THEN
868 -- could be a new hire or applicant hire, there is no asg rec
869
870 CLOSE csr_get_old_asg_data;
871 ELSE
872 CLOSE csr_get_old_asg_data;
873 END IF;
874
875 IF l_old_pay_basis_id IS NOT NULL and
876 l_new_pay_basis_id IS NOT NULL and
877 l_old_pay_basis_id <> l_new_pay_basis_id and
878 l_payroll_id IS NOT NULL
879 THEN
880 -- perform mid pay period check
881 OPEN csr_check_mid_pay_period
882 (p_eff_date_csr => l_effective_date
883 ,p_payroll_id_csr => l_payroll_id);
884 FETCH csr_check_mid_pay_period into l_pay_period_start_date
885 ,l_pay_period_end_date;
886 IF csr_check_mid_pay_period%NOTFOUND
887 THEN
888 -- That means the effective date is not in mid pay period
889 CLOSE csr_check_mid_pay_period;
890 -- Need to set the item attribute to 'N' because this may be
891 -- a Return For Correction and the value of the item attribute
892 -- was set to 'Y' previously.
893 wf_engine.setItemAttrText
894 (itemtype => p_item_type
895 ,itemkey => p_item_key
896 ,aname => g_mid_pay_period_change
897 ,avalue => 'N');
898 ELSE
899 -- Only set the WF Item attribute HR_MID_PAY_PERIOD_CHANGE to
900 -- 'Y' when there is payroll installed and the employee is not a
901 -- new hire (ie. first time salary basis was entered).
902 -- We determine New Hire by looking at the old db assignment rec
903 -- pay_basis_id. If that is null, then this is the first time
904 -- salary basis was entered. We don't need to perform the check
905 -- because there is no element type changed.
906 CLOSE csr_check_mid_pay_period;
907 wf_engine.setItemAttrText
908 (itemtype => p_item_type
909 ,itemkey => p_item_key
910 ,aname => g_mid_pay_period_change
911 ,avalue => 'Y');
912
913 result := 'COMPLETE:'||'Y';
914
915 END IF;
916 END IF;
917 ELSE
918 result := 'COMPLETE:'||'N';
919 END IF; -- asg txn step is not null
920 ELSIF ( funmode = 'CANCEL' ) then
921 --
922 NULL;
923 --
924 END IF;
925 END check_mid_pay_period_change;
926
927
928 --
929 -- ------------------------------------------------------------------------
930 -- |------------------ < apps_initialize > --------------------|
931 -- Method to initialize the session apps context if there is no context already
932 -- set.
933 -- The method checks the current session context userid ,
934 -- If the userid is not same stored in the item attribute HR_USER_ID_ATTR
935 -- then the fnd_global.apps_initialize called with
936 -- user id as stored in the item attribute HR_USER_ID_ATTR.
937 -- ------------------------------------------------------------------------
938
939
940 PROCEDURE apps_initialize
941 ( p_itemtype in varchar2
942 , p_itemkey in varchar2
943 , p_actid in number
944 , p_funcmode in varchar2
945 , p_result in out nocopy varchar2
946 )
947 IS
948 l_user_id NUMBER;
949 l_resp_id NUMBER;
950 l_resp_appl_id NUMBER;
951 l_fnd_user_id fnd_user.user_id%type default null;
952 l_session_user_id varchar2(100);
953 l_session_resp_id NUMBER;
954 BEGIN
955
956 IF (p_funcmode = 'RUN' and wf_engine.GetItemAttrText(itemtype =>p_itemtype,
957 itemkey =>p_itemkey,
958 aname =>'HR_DEFER_COMMIT_ATTR',
959 ignore_notfound=>true)='Y'
960 ) THEN
961 -- Code that compares current session context
962 -- with the work item context required to execute
963 -- the workflow safely
964 l_session_user_id := FND_GLOBAL.USER_ID;
965 l_session_resp_id := FND_GLOBAL.RESP_ID;
966 l_fnd_user_id := wf_engine.GetItemAttrNumber(itemtype =>p_itemtype,
967 itemkey =>p_itemkey,
968 aname =>'HR_USER_ID_ATTR',
969 ignore_notfound=>true);
970
971 l_resp_id :=wf_engine.GetItemAttrNumber(itemtype =>p_itemtype,
972 itemkey =>p_itemkey,
973 aname =>'HR_RESP_ID_ATTR',
974 ignore_notfound=>true);
975
976 IF ((l_session_user_id = l_fnd_user_id) and (l_session_resp_id = l_resp_id)) then
977 -- session already has proper values ignore reset
978 p_result := 'COMPLETE';
979 else
980 -- HR_RESP_ID_ATTR
981 l_resp_id :=wf_engine.GetItemAttrNumber(itemtype =>p_itemtype,
982 itemkey =>p_itemkey,
983 aname =>'HR_RESP_ID_ATTR',
984 ignore_notfound=>true);
985 -- HR_RESP_APPL_ID_ATTR
986 l_resp_appl_id := wf_engine.GetItemAttrNumber(itemtype =>p_itemtype,
987 itemkey =>p_itemkey,
988 aname =>'HR_RESP_APPL_ID_ATTR',
989 ignore_notfound=>true);
990
991 -- set the fnd session context with the last approver user id
992 fnd_global.apps_initialize(user_id =>l_fnd_user_id,
993 resp_id =>l_resp_id,
994 resp_appl_id=> l_resp_appl_id);
995
996
997 p_result := 'COMPLETE';
998 end if;
999 ELSE
1000 p_result := 'COMPLETE';
1001 END IF;
1002
1003 EXCEPTION
1004 WHEN OTHERS THEN NULL;
1005 WF_CORE.Context(g_package, '.apps_initialize',
1006 p_itemtype, p_itemkey, p_actid, p_funcmode);
1007 RAISE;
1008 END apps_initialize;
1009
1010 --
1011 -- ------------------------------------------------------------------------
1012 -- |------------------ < defer_commit > --------------------|
1013 -- Method to read profile value (HR_DEFER_UPDATE)
1014 -- and branch the workflow accordingly.
1015 -- ------------------------------------------------------------------------
1016 procedure defer_commit
1017 (p_item_type in varchar2,
1018 p_item_key in varchar2,
1019 p_act_id in number,
1020 funmode in varchar2,
1021 result in out nocopy varchar2 ) is
1022 -- userid
1023 l_defer_commit fnd_lookups.lookup_code%type;
1024
1025 begin
1026 IF ( funmode = 'RUN' )
1027 THEN
1028 -- get the profile value
1029 -- HR_DEFER_UPDATE
1030 -- Oracle Human Resources
1031 -- HR:Defer Update After Approval
1032 fnd_profile.get(name=>'HR_DEFER_UPDATE',val=>l_defer_commit);
1033 if l_defer_commit = 'N' then
1034 -- Commit immediately
1035 hr_approval_wf.create_item_attrib_if_notexist
1036 (p_item_type => p_item_type
1037 ,p_item_key => p_item_key
1038 ,p_name => 'HR_DEFER_COMMIT_ATTR');
1039
1040 wf_engine.SetItemAttrText(itemtype =>p_item_type,
1041 itemkey =>p_item_key,
1042 aname =>'HR_DEFER_COMMIT_ATTR',
1043 avalue => 'N' );
1044 result := 'COMPLETE:'|| 'N';
1045 else
1046 -- Defer commit.
1047 -- get the current login user id and populate it into item attribute HR_USER_ID_ATTR
1048 --
1049 hr_approval_wf.create_item_attrib_if_notexist
1050 (p_item_type => p_item_type
1051 ,p_item_key => p_item_key
1052 ,p_name => 'HR_USER_ID_ATTR');
1053 wf_engine.SetItemAttrNumber (itemtype => p_item_type,
1054 itemkey => p_item_key,
1055 aname => 'HR_USER_ID_ATTR',
1056 avalue => FND_GLOBAL.USER_ID ) ;
1057 -- get the resp_id, FND_GLOBAL.RESP_ID
1058 hr_approval_wf.create_item_attrib_if_notexist
1059 (p_item_type => p_item_type
1060 ,p_item_key => p_item_key
1061 ,p_name => 'HR_RESP_ID_ATTR');
1062 wf_engine.SetItemAttrNumber (itemtype => p_item_type,
1063 itemkey => p_item_key,
1064 aname => 'HR_RESP_ID_ATTR',
1065 avalue => FND_GLOBAL.RESP_ID ) ;
1066
1067 -- get resp_appl_id , FND_GLOBAL.RESP_APPL_ID
1068 hr_approval_wf.create_item_attrib_if_notexist
1069 (p_item_type => p_item_type
1070 ,p_item_key => p_item_key
1071 ,p_name => 'HR_RESP_APPL_ID_ATTR');
1072 wf_engine.SetItemAttrNumber (itemtype => p_item_type,
1073 itemkey => p_item_key,
1074 aname => 'HR_RESP_APPL_ID_ATTR',
1075 avalue => FND_GLOBAL.RESP_APPL_ID ) ;
1076 hr_approval_wf.create_item_attrib_if_notexist
1077 (p_item_type => p_item_type
1078 ,p_item_key => p_item_key
1079 ,p_name => 'HR_DEFER_COMMIT_ATTR');
1080
1081 wf_engine.SetItemAttrText(itemtype =>p_item_type,
1082 itemkey =>p_item_key,
1083 aname =>'HR_DEFER_COMMIT_ATTR',
1084 avalue => 'Y' );
1085 result := 'COMPLETE:'|| 'Y';
1086 end if;
1087
1088 END IF;
1089
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 WF_CORE.CONTEXT(g_package,'.defer_commit',p_item_type,p_item_key);
1093 RAISE;
1094
1095 END defer_commit;
1096
1097
1098 /*
1099 Methods added to provide java wrapper to WF_ENGINE API's, may be obsoleted
1100 once the Java interface for them is provided. These new procedures are with
1101 new signature adding the boolean parameter ignore_notfound.
1102
1103 */
1104
1105 function GetActivityAttrText(itemtype in varchar2,
1106 itemkey in varchar2,
1107 actid in number,
1108 aname in varchar2,
1109 ignore_notfound in varchar2 default 'FALSE')
1110 return varchar2 is
1111 c_proc varchar2(30) default 'GetActivityAttrText';
1112
1113
1114 begin
1115 g_debug := hr_utility.debug_enabled;
1116
1117 if g_debug then
1118 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1119 end if;
1120
1121
1122 if(ignore_notfound='TRUE') then
1123 if g_debug then
1124 hr_utility.set_location('calling WF_ENGINE.GetActivityAttrText ', 2);
1125 end if;
1126 return WF_ENGINE.GetActivityAttrText(itemtype=>itemtype,
1127 itemkey=>itemkey,
1128 actid=>actid,
1129 aname=>aname,
1130 ignore_notfound=>TRUE);
1131 elsif (ignore_notfound='FALSE') then
1132 if g_debug then
1133 hr_utility.set_location('calling WF_ENGINE.GetActivityAttrText ', 2);
1134 end if;
1135
1136 return WF_ENGINE.GetActivityAttrText(itemtype=>itemtype,
1137 itemkey=>itemkey,
1138 actid=>actid,
1139 aname=>aname,
1140 ignore_notfound=>FALSE);
1141 else
1142 return null;
1143 end if;
1144 exception
1145 when others then
1146 Wf_Core.Context('hr_workflow_ss', 'GetActivityAttrText', itemtype, itemkey,
1147 to_char(actid), aname);
1148 raise;
1149 end GetActivityAttrText;
1150
1151
1152 function GetActivityAttrDate(itemtype in varchar2,
1153 itemkey in varchar2,
1154 actid in number,
1155 aname in varchar2,
1156 ignore_notfound in varchar2 default 'FALSE')
1157 return date is
1158 c_proc varchar2(30) default 'GetActivityAttrDate';
1159
1160 begin
1161 g_debug := hr_utility.debug_enabled;
1162
1163 if g_debug then
1164 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1165 end if;
1166
1167 if(ignore_notfound='TRUE') then
1168 if g_debug then
1169 hr_utility.set_location('calling WF_ENGINE.GetActivityAttrDate ', 2);
1170 end if;
1171 return WF_ENGINE.GetActivityAttrDate(itemtype=>itemtype,
1172 itemkey=>itemkey,
1173 actid=>actid,
1174 aname=>aname,
1175 ignore_notfound=>TRUE);
1176 elsif (ignore_notfound='FALSE') then
1177 if g_debug then
1178 hr_utility.set_location('calling WF_ENGINE.GetActivityAttrDate ', 2);
1179 end if;
1180 return WF_ENGINE.GetActivityAttrDate(itemtype=>itemtype,
1181 itemkey=>itemkey,
1182 actid=>actid,
1183 aname=>aname,
1184 ignore_notfound=>FALSE);
1185 else
1186 return null;
1187 end if;
1188 exception
1189 when others then
1190 Wf_Core.Context('hr_workflow_ss', 'GetActivityAttrDate', itemtype, itemkey,
1191 to_char(actid), aname);
1192 raise;
1193 end GetActivityAttrDate;
1194
1195
1196
1197
1198 function GetActivityAttrNumber(itemtype in varchar2,
1199 itemkey in varchar2,
1200 actid in number,
1201 aname in varchar2,
1202 ignore_notfound in varchar2 default 'FALSE')
1203 return number is
1204 c_proc varchar2(30) default 'GetActivityAttrNumber';
1205
1206 begin
1207 g_debug := hr_utility.debug_enabled;
1208
1209 if g_debug then
1210 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1211 end if;
1212
1213 if(ignore_notfound='TRUE') then
1214 if g_debug then
1215 hr_utility.set_location('calling WF_ENGINE.GetActivityAttrNumber ', 2);
1216 end if;
1217 return WF_ENGINE.GetActivityAttrNumber(itemtype=>itemtype,
1218 itemkey=>itemkey,
1219 actid=>actid,
1220 aname=>aname,
1221 ignore_notfound=>TRUE);
1222 elsif (ignore_notfound='FALSE') then
1223 if g_debug then
1224 hr_utility.set_location('calling WF_ENGINE.GetActivityAttrNumber ', 2);
1225 end if;
1226
1227 return WF_ENGINE.GetActivityAttrNumber(itemtype=>itemtype,
1228 itemkey=>itemkey,
1229 actid=>actid,
1230 aname=>aname,
1231 ignore_notfound=>FALSE);
1232 else
1233 return null;
1234 end if;
1235 exception
1236 when others then
1237 Wf_Core.Context('hr_workflow_ss', 'GetActivityAttrNumber', itemtype, itemkey,
1238 to_char(actid), aname);
1239 raise;
1240 end GetActivityAttrNumber;
1241
1242 PROCEDURE getPageDetails
1243 (p_item_type in varchar2,
1244 p_item_key in varchar2,
1245 p_activityId out nocopy number,
1246 p_page out nocopy varchar2,
1247 p_page_type out nocopy varchar2,
1248 p_page_applicationId out nocopy varchar2,
1249 p_additional_params out nocopy varchar2 )is
1250 -- local variables
1251 lv_procedure_name varchar2(30) default 'getPageDetails';
1252 ln_activityId wf_item_activity_statuses.process_activity%type;
1253 lv_page wf_item_attribute_values.text_value%type;
1254 lv_page_type wf_item_attribute_values.text_value%type;
1255 ln_page_applicationId wf_item_attribute_values.number_value%type;
1256 lv_additional_params wf_item_attribute_values.text_value%type;
1257 BEGIN
1258 hr_utility.set_location(lv_procedure_name,1);
1259 if(hr_utility.debug_enabled) then
1260 -- write debug statements
1261 hr_utility.set_location('Entered'||lv_procedure_name||'with itemtype:'||p_item_type, 2);
1262 hr_utility.set_location('Entered'||lv_procedure_name||'with itemkey:'||p_item_key, 2);
1263 end if;
1264 -- get the activity id for blocked page activity
1265 -- activity used for page should have activity attribute
1266 -- of type 'FORM'
1267 begin
1268 if(hr_utility.debug_enabled) then
1269 -- write debug statements
1270 hr_utility.set_location('Querying WF_ITEM_ACTIVITY_STATUSES for notified activity of type FORM'||lv_procedure_name||'with itemtype:', 3);
1271 end if;
1272
1273 -- Fix for bug 3719338
1274 SELECT process_activity
1275 into ln_activityId
1276 from
1277 (select process_activity
1278 FROM WF_ITEM_ACTIVITY_STATUSES IAS
1279 WHERE ias.item_type = p_item_type
1280 and ias.item_key = p_item_key
1281 and ias.activity_status = 'NOTIFIED'
1282 and ias.process_activity in (
1283 select wpa.instance_id
1284 FROM WF_PROCESS_ACTIVITIES WPA,
1285 WF_ACTIVITY_ATTRIBUTES WAA,
1286 WF_ACTIVITIES WA,
1287 WF_ITEMS WI
1288 WHERE wpa.process_item_type = ias.item_type
1289 and wa.item_type = wpa.process_item_type
1290 and wa.name = wpa.activity_name
1291 and wi.item_type = ias.item_type
1292 and wi.item_key = ias.item_key
1293 and wi.begin_date >= wa.begin_date
1294 and wi.begin_date < nvl(wa.end_date,wi.begin_date+1)
1295 and waa.activity_item_type = wa.item_type
1296 and waa.activity_name = wa.name
1297 and waa.activity_version = wa.version
1298 and waa.type = 'FORM'
1299 )
1300 order by begin_date desc)
1301 where rownum<=1;
1302
1303 exception
1304 when no_data_found then
1305 if(hr_utility.debug_enabled) then
1306 -- write debug statements
1307 hr_utility.set_location('no notified activity found in WF_ITEM_ACTIVITY_STATUSES of type FORM for itemtype:'|| p_item_type||' and item key:'||p_item_key, 4);
1308 end if;
1309 ln_activityId := null;
1310 when others then
1311 ln_activityId := null;
1312 fnd_message.set_name('PER', SQLERRM ||' '||to_char(SQLCODE));
1313 hr_utility.raise_error;
1314 end;
1315
1316 if(ln_activityId is not null) then
1317 -- we have a blocked page activity in the wf root process
1318 -- get the activity attribute values
1319 if(hr_utility.debug_enabled) then
1320 -- write debug statements
1321 hr_utility.set_location('getting activity attributes for activity id:'||ln_activityId ||', itemtype:'|| p_item_type||' and item key:'||p_item_key, 5);
1322 end if;
1323 lv_page := wf_engine.GetActivityAttrText(
1324 itemtype => p_item_type
1325 ,itemkey => p_item_key
1326 ,actid => ln_activityId
1327 ,aname => 'HR_ACTIVITY_TYPE_VALUE' );
1328 lv_page_type := wf_engine.GetActivityAttrText(
1329 itemtype => p_item_type
1330 ,itemkey => p_item_key
1331 ,actid => ln_activityId
1332 ,aname => 'HR_ACTIVITY_TYPE' );
1333 -- 'APPLICATION_ID'
1334 ln_page_applicationId :=nvl( wf_engine.GetActivityAttrText(
1335 itemtype => p_item_type
1336 ,itemkey => p_item_key
1337 ,actid => ln_activityId
1338 ,aname => 'APPLICATION_ID'
1339 ,ignore_notfound=>true),'800');
1340
1341
1342 -- get the additional params
1343 -- 'P_CALLED_FROM' and ???
1344 lv_additional_params := wf_engine.getitemattrtext(
1345 itemtype => p_item_type
1346 ,itemkey => p_item_key
1347 ,aname => 'P_CALLED_FROM' );
1348
1349 p_activityId :=ln_activityId;
1350 p_page :=lv_page;
1351 p_page_type := lv_page_type;
1352 p_page_applicationId :=ln_page_applicationId;
1353 p_additional_params := lv_additional_params;
1354 else
1355 -- there is no blocked page activity, check for additional details
1356 if(hr_utility.debug_enabled) then
1357 -- write debug statements
1358 hr_utility.set_location(' itemtype:'|| p_item_type||' and item key:'||p_item_key, 6);
1359 p_activityId :='0'; -- fix for bug 3823494
1360 end if;
1361 end if;
1362
1363 if(hr_utility.debug_enabled) then
1364 -- write debug statements
1365 hr_utility.set_location('Leaving '||lv_procedure_name||'with itemtype:'||p_item_type, 10);
1366 end if;
1367
1368 EXCEPTION
1369 WHEN OTHERS THEN
1370 fnd_message.set_name('PER', SQLERRM ||' '||to_char(SQLCODE));
1371 hr_utility.raise_error;
1372 END getPageDetails;
1373
1374 function GetItemAttrText(itemtype in varchar2,
1375 itemkey in varchar2,
1376 aname in varchar2,
1377 ignore_notfound in varchar2 default 'FALSE')
1378 return varchar2 is
1379 c_proc varchar2(30) default 'GetItemAttrText';
1380
1381
1382 begin
1383 g_debug := hr_utility.debug_enabled;
1384
1385 if g_debug then
1386 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1387 end if;
1388
1389
1390 if(ignore_notfound='TRUE') then
1391 if g_debug then
1392 hr_utility.set_location('calling WF_ENGINE.GetItemAttrText ', 2);
1393 end if;
1394 return WF_ENGINE.GetItemAttrText(itemtype=>itemtype,
1395 itemkey=>itemkey,
1396 aname=>aname,
1397 ignore_notfound=>TRUE);
1398 elsif (ignore_notfound='FALSE') then
1399 if g_debug then
1400 hr_utility.set_location('calling WF_ENGINE.GetItemAttrText ', 2);
1401 end if;
1402
1403 return WF_ENGINE.GetItemAttrText(itemtype=>itemtype,
1404 itemkey=>itemkey,
1405 aname=>aname,
1406 ignore_notfound=>FALSE);
1407 else
1408 return null;
1409 end if;
1410 exception
1411 when others then
1412 Wf_Core.Context('hr_workflow_ss', 'GetItemAttrText', itemtype, itemkey,
1413 aname);
1414 raise;
1415 end GetItemAttrText;
1416
1417 PROCEDURE get_item_type_and_key (
1418 p_ntfId IN NUMBER
1419 ,p_itemType OUT NOCOPY VARCHAR2
1420 ,p_itemKey OUT NOCOPY VARCHAR2 ) IS
1421 c_proc varchar2(30) default 'get_item_type_and_key';
1422 lv_item_type wf_item_activity_statuses.item_type%type;
1423 lv_item_key wf_item_activity_statuses.item_key%type;
1424 lv_activity_id wf_item_activity_statuses.process_activity%type;
1425 begin
1426 g_debug := hr_utility.debug_enabled;
1427 if g_debug then
1428 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1429 end if;
1430
1431 -- get the itemtype and item key for the notification id
1432 begin
1433 select item_type, item_key, process_activity
1434 into lv_item_type, lv_item_key,lv_activity_id
1435 from wf_item_activity_statuses
1436 where notification_id = p_ntfid;
1437 exception
1438 when no_data_found then
1439 begin
1440 -- try getting from the notification context
1441 select substr(context,1,instr(context,':',1)-1)
1442 ,substr(context,instr(context,':')+1, ( instr(context,':',instr(context,':')+1 ) - instr(context,':')-1) )
1443 into lv_item_type, lv_item_key
1444 from wf_notifications
1445 where notification_id = p_ntfid;
1446 exception
1447 when no_data_found then
1448 hr_utility.set_location('Error in '|| g_package||'.'||c_proc ||SQLERRM ||' '||to_char(SQLCODE), 20);
1449 raise;
1450 when others then
1451 raise;
1452 end;
1453 when others then
1454 raise;
1455
1456 end;
1457 p_itemType := lv_item_type;
1458 p_itemKey := lv_item_key;
1459
1460 exception
1461 when others then
1462 hr_utility.set_location('hr_workflow_ss.get_item_type_and_key errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
1463 Wf_Core.Context('hr_workflow_ss', 'get_item_type_and_key', p_ntfId);
1464 raise;
1465 end get_item_type_and_key;
1466
1467 procedure build_edit_link(document_id IN Varchar2,
1468 display_type IN Varchar2,
1469 document IN OUT NOCOPY varchar2,
1470 document_type IN OUT NOCOPY Varchar2) is
1471 c_proc varchar2(30) default 'GetItemAttrText';
1472 lv_item_type wf_item_activity_statuses.item_type%type;
1473 lv_item_key wf_item_activity_statuses.item_key%type;
1474 lv_checkProfile VARCHAR2(10);
1475 lv_profileValue VARCHAR2(1);
1476 lv_status hr_api_transactions.status%type;
1477 lv_link_label wf_message_attributes_vl.display_name%type;
1478 lv_pageFunc wf_item_attribute_values.text_value%type;
1479 lv_web_html_call fnd_form_functions_vl.web_html_call%type;
1480 lv_params fnd_form_functions_vl.parameters%type;
1481 lv_addtnlParams VARCHAR2(30) ;
1482 lv_restrict_edit_to_owner varchar2(3);
1483 -- fix for bug#3333763
1484 lv_ntf_role WF_NOTIFICATIONS.RECIPIENT_ROLE%type;
1485 lv_ntf_msg_typ WF_NOTIFICATIONS.MESSAGE_TYPE%type;
1486 lv_ntf_msg_name WF_NOTIFICATIONS.MESSAGE_NAME%type;
1487 lv_ntf_prior WF_NOTIFICATIONS.PRIORITY%type;
1488 lv_ntf_due WF_NOTIFICATIONS.DUE_DATE%type;
1489 lv_ntf_status WF_NOTIFICATIONS.STATUS%type;
1490
1491 begin
1492 g_debug := hr_utility.debug_enabled;
1493 if g_debug then
1494 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1495 end if;
1496
1497 -- fix for bug#3333763
1498 -- check the status of the notification before doing any iterations.
1499 -- Get notification recipient and status
1500 Wf_Notification.GetInfo(document_id, lv_ntf_role, lv_ntf_msg_typ, lv_ntf_msg_name, lv_ntf_prior, lv_ntf_due, lv_ntf_status);
1501
1502 if (lv_ntf_status <> 'OPEN') then
1503 -- no more iteration return
1504 document :=null;
1505 document_type:=null;
1506 return;
1507 end if;
1508
1509 -- get the itemtype and item key for the notification id
1510 hr_workflow_ss.get_item_type_and_key(document_id,lv_item_type,lv_item_key);
1511
1512 -- check if the workflow process has been configured to
1513 --restrict edit to transaction owner/creator
1514 -- HR_RESTRICT_EDIT_ATTR
1515 if g_debug then
1516 hr_utility.set_location('querying wf_engine.GetItemAttrText for HR_RESTRICT_EDIT_ATTR with itemtype:itemkey '||lv_item_type||':'||lv_item_key, 2);
1517 end if;
1518
1519 lv_restrict_edit_to_owner :=wf_engine.GetItemAttrText(itemtype=>lv_item_type,
1520 itemkey=>lv_item_key,
1521 aname=>'HR_RESTRICT_EDIT_ATTR',
1522 ignore_notfound=>TRUE);
1523 if g_debug then
1524 hr_utility.set_location('HR_RESTRICT_EDIT_ATTR value:'||lv_restrict_edit_to_owner,3);
1525 end if;
1526
1527 if(lv_restrict_edit_to_owner='Y') then
1528 hr_utility.set_location('process configured to restrict edit to owner/creator, no edit so returning',4);
1529 -- get the current login user name and compare with the creator user name
1530 if(fnd_global.USER_NAME=wf_engine.GetItemAttrText(itemtype=>lv_item_type,
1531 itemkey=>lv_item_key,
1532 aname=>'CREATOR_PERSON_USERNAME',
1533 ignore_notfound=>TRUE))then
1534 null;
1535 else
1536 -- no more iteration return
1537 document :=null;
1538 document_type:=null;
1539 return;
1540 end if;
1541 end if;
1542
1543 -- get the hr_api_transaction.status and profile value
1544 begin
1545 select nvl(status,'N'), nvl(fnd_profile.value('PQH_ALLOW_APPROVER_TO_EDIT_TXN'),'N')
1546 into lv_status, lv_profileValue
1547 from hr_api_transactions
1548 where item_type = lv_item_type
1549 and item_key = lv_item_key;
1550 exception
1551 when no_data_found then
1552 raise;
1553 end;
1554 -- No need to check profile option (i.e. must render edit link)
1555 -- in following cases
1556 IF (INSTR(lv_status,'S') > 0 OR lv_status IN ('RI','N','C','W') ) THEN
1557 lv_checkProfile := 'N';
1558 END IF;
1559
1560 IF (lv_checkProfile = 'N' OR lv_profileValue ='Y' ) THEN
1561 -- get the translated display name for the url link
1562 begin
1563 select wma.display_name
1564 into lv_link_label
1565 from wf_notifications wn, wf_message_attributes_vl wma
1566 where wn.notification_id = document_id
1567 and wn.message_name = wma.message_name
1568 and wma.message_type = lv_item_type
1569 and wma.name = 'EDIT_TXN_URL';
1570 exception
1571 when others then
1572 lv_link_label:= 'EDIT_TXN_URL';
1573 end;
1574
1575 -- build the url link
1576 -- get the link details
1577 -- get the item attribute holding the FND function name corresponding
1578 -- to the MDS document.
1579 lv_pageFunc := nvl(wf_engine.GetItemAttrText(lv_item_type,lv_item_key,'HR_OAF_EDIT_URL_ATTR',TRUE),'PQH_SS_EFFDATE');
1580 -- get the web_html_call value and params for this function
1581 begin
1582 select web_html_call,parameters
1583 into lv_web_html_call,lv_params
1584 from fnd_form_functions_vl
1585 where function_name=lv_pageFunc;
1586 exception
1587 when no_data_found then
1588 hr_utility.set_location('Unable to retrieve function details,web_html_call and parameters for:'||lv_pageFunc||' '|| g_package||'.'||c_proc, 10);
1589 when others then
1590 raise;
1591 end;
1592 -- set the out variables
1593 lv_addtnlParams := '&'||'retainAM=Y'||'&'||'NtfId='||'&'||'#NID';
1594 document := '<tr><td> '||
1595 '<IMG SRC="'||g_oa_media||'afedit.gif"/>'||
1596 '</td><td>'||
1597 '<a class="OraLinkText" href='||g_oa_html||lv_web_html_call||lv_params||lv_addtnlParams||'>'
1598 ||lv_link_label||'</a></td></tr> ';
1599 -- set the document type
1600 document_type := wf_notification.doc_html;
1601
1602 else
1603 document := null;
1604 end if;
1605
1606 if g_debug then
1607 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1608 end if;
1609
1610 exception
1611 when others then
1612 document := null;
1613 document_type :=null;
1614 hr_utility.set_location('hr_workflow_ss.build_edit_link errored : '||SQLERRM ||' '||to_char(SQLCODE), 20);
1615 Wf_Core.Context('hr_workflow_ss', 'build_edit_link', document_id, display_type);
1616 raise;
1617 end build_edit_link;
1618
1619
1620
1621 function GetAttrNumber (nid in number,
1622 aname in varchar2,
1623 ignore_notfound in varchar2 default 'FALSE')
1624 return number is
1625 c_proc varchar2(30) default 'GetAttrNumber';
1626 lvalue wf_notification_attributes.NUMBER_VALUE%type;
1627 begin
1628 g_debug := hr_utility.debug_enabled;
1629
1630 if g_debug then
1631 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1632 end if;
1633 if ((nid is null) or (aname is null)) then
1634 wf_core.token('NID', to_char(nid));
1635 wf_core.token('ANAME', aname);
1636 wf_core.raise('WFSQL_ARGS');
1637 end if;
1638
1639 begin
1640 if g_debug then
1641 hr_utility.set_location('querying wf_notification_attributes.number_value for aname:nid'||aname||':'||nid, 2);
1642 end if;
1643 select WNA.NUMBER_VALUE
1644 into lvalue
1645 from WF_NOTIFICATION_ATTRIBUTES WNA
1646 where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
1647 exception
1648 when no_data_found then
1649 if(ignore_notfound='TRUE') then
1650 return null;
1651 else
1652 wf_core.token('NID', to_char(nid));
1653 wf_core.token('ATTRIBUTE', aname);
1654 wf_core.raise('WFNTF_ATTR');
1655 end if;
1656 end;
1657
1658 return(lvalue);
1659 exception
1660 when others then
1661 wf_core.context('hr_workflow_ss', 'GetAttrNumber', to_char(nid), aname);
1662 hr_utility.set_location('Error querying wf_notification_attributes.NUMBER_VALUE for aname:nid'||aname||':'||nid||'-'||SQLERRM ||' '||to_char(SQLCODE), 10);
1663 raise;
1664 end GetAttrNumber;
1665
1666 function GetAttrText (nid in number,
1667 aname in varchar2,
1668 ignore_notfound in varchar2 default 'FALSE')
1669 return varchar2 is
1670 c_proc varchar2(30) default 'GetAttrText';
1671 lvalue wf_notification_attributes.text_value%type;
1672 begin
1673 g_debug := hr_utility.debug_enabled;
1674
1675 if g_debug then
1676 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1677 end if;
1678 if ((nid is null) or (aname is null)) then
1679 wf_core.token('NID', to_char(nid));
1680 wf_core.token('ANAME', aname);
1681 wf_core.raise('WFSQL_ARGS');
1682 end if;
1683
1684 begin
1685 if g_debug then
1686 hr_utility.set_location('querying wf_notification_attributes.text_value for aname:nid'||aname||':'||nid, 2);
1687 end if;
1688 select WNA.TEXT_VALUE
1689 into lvalue
1690 from WF_NOTIFICATION_ATTRIBUTES WNA
1691 where WNA.NOTIFICATION_ID = nid and WNA.NAME = aname;
1692 exception
1693 when no_data_found then
1694 if(ignore_notfound='TRUE') then
1695 return null;
1696 else
1697 wf_core.token('NID', to_char(nid));
1698 wf_core.token('ATTRIBUTE', aname);
1699 wf_core.raise('WFNTF_ATTR');
1700 end if;
1701 end;
1702
1703 return(lvalue);
1704 exception
1705 when others then
1706 wf_core.context('hr_workflow_ss', 'GetAttrText', to_char(nid), aname);
1707 hr_utility.set_location('Error querying wf_notification_attributes.text_value for aname:nid'||aname||':'||nid||'-'||SQLERRM ||' '||to_char(SQLCODE), 10);
1708 raise;
1709 end GetAttrText;
1710
1711 function getApprStartingPointPersonId
1712 (p_transaction_id IN hr_api_transactions.transaction_id%TYPE)
1713 return number
1714 is
1715 c_item_type varchar2(50);
1716 c_item_key number;
1717 c_creator_person_id per_all_people_f.person_id%type default null;
1718 lv_transaction_ref_table hr_api_transactions.transaction_ref_table%type;
1719 lv_transaction_ref_id hr_api_transactions.transaction_ref_id%type;
1720
1721 begin
1722 -- get the creator person_id from hr_api_transactions
1723 -- this would be the default for all SSHR approvals.
1724 begin
1725 select hr_api_transactions.creator_person_id
1726 into c_creator_person_id
1727 from hr_api_transactions
1728 where hr_api_transactions.transaction_id=getApprStartingPointPersonId.p_transaction_id;
1729 exception
1730 when others then
1731 raise;
1732 end;
1733
1734 -- if the transaction is for appraisal we need go through
1735 -- Main Appraiser chain for approvals.
1736 begin
1737 select hr_api_transactions.transaction_ref_table,hr_api_transactions.transaction_ref_id
1738 into lv_transaction_ref_table,lv_transaction_ref_id
1739 from hr_api_transactions
1740 where hr_api_transactions.transaction_id=getApprStartingPointPersonId.p_transaction_id;
1741
1742 if(lv_transaction_ref_table='PER_APPRAISALS') then
1743 begin
1744 select per_appraisals.main_appraiser_id
1745 into c_creator_person_id
1746 from per_appraisals
1747 where per_appraisals.appraisal_id=getApprStartingPointPersonId.lv_transaction_ref_id;
1748 exception
1749 when others then
1750 -- do not raise, return
1751 null;
1752 end;
1753 end if;
1754 exception
1755 when others then
1756 hr_utility.trace(' exception in checking the hr_api_transactions.transaction_ref_table:'||
1757 'rollback_transaction'||' : ' || sqlerrm);
1758 -- just log the message no need to raise it
1759 end;
1760
1761 return c_creator_person_id;
1762
1763 EXCEPTION
1764 WHEN OTHERS THEN
1765 WF_CORE.CONTEXT(g_package,'.getApprStartingPointPersonId',c_item_type,c_item_key);
1766 RAISE;
1767 end getApprStartingPointPersonId;
1768
1769
1770 procedure updateSFLTransaction (itemtype in varchar2,
1771 itemkey in varchar2,
1772 actid in number,
1773 funmode in varchar2,
1774 result out nocopy varchar2 )
1775 as
1776 -- -------------------------------------------------------------------------
1777 -- local variables
1778 -- -------------------------------------------------------------------------
1779 c_proc varchar2(30) default 'updateSFLTransaction';
1780 dynamicQuery varchar2(4000) default null;
1781 queryProcedure varchar2(4000) default 'pqh_ss_workflow.set_transaction_status';
1782 actionStatus varchar2(3) default 'SFL';
1783 begin
1784 g_debug := hr_utility.debug_enabled;
1785
1786 if g_debug then
1787 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
1788 end if;
1789
1790 -- fix for bug 4454439
1791 begin
1792
1793 -- re-intialize the performer roles
1794 hr_approval_ss.reinitperformerroles(p_notification_id=>null
1795 ,p_transaction_id=>null
1796 ,p_item_type=>itemtype
1797 ,p_item_key=>itemKey);
1798 exception
1799 when others then
1800 null;
1801 end;
1802
1803
1804 if ( funmode = wf_engine.eng_run ) then
1805 -- code to update status and history
1806 if g_debug then
1807 hr_utility.set_location('Calling queryProcedure: '||queryProcedure, 20);
1808 hr_utility.set_location('itemType: '|| itemType, 21);
1809 hr_utility.set_location('itemKey: '|| itemKey, 22);
1810 hr_utility.set_location('actId: '|| actId, 23);
1811 end if;
1812
1813 dynamicQuery :=
1814 'begin ' ||
1815 queryProcedure ||
1816 '(:itemTypeIn, :itemKeyIn, :actIdIn, :actionStatusIn, :resultOut); end;';
1817 execute immediate dynamicQuery
1818 using
1819 in itemType,
1820 in itemKey,
1821 in actId,
1822 in actionStatus,
1823 out result;
1824 if g_debug then
1825 hr_utility.set_location('After queryProcedure: '||queryProcedure, 40);
1826 hr_utility.set_location('result: '|| result, 41);
1827 end if;
1828
1829
1830 --
1831 elsif ( funmode = wf_engine.eng_cancel ) then
1832 --
1833 if g_debug then
1834 hr_utility.set_location(g_package ||'.updateSFLTransaction called in funmode:'||funmode, 50);
1835 hr_utility.set_location('itemType: '|| itemType, 51);
1836 hr_utility.set_location('itemKey: '|| itemKey, 52);
1837 hr_utility.set_location('actId: '|| actId, 53);
1838 end if;
1839 end if;
1840
1841 EXCEPTION
1842 WHEN OTHERS THEN
1843 WF_CORE.CONTEXT(g_package,'.updateSFLTransaction',itemtype,itemkey,funmode);
1844 hr_utility.set_location(''||SQLERRM ||' '||to_char(SQLCODE), 100);
1845 RAISE;
1846
1847
1848 end updateSFLTransaction;
1849
1850 function getProcessDisplayName(itemtype in varchar2,
1851 itemkey in varchar2)
1852 return wf_runnable_processes_v.display_name%type
1853 is
1854 lv_display_name wf_runnable_processes_v.display_name%type;
1855 c_proc varchar2(30) default 'getProcessDisplayName';
1856
1857 lv_ntf_sub_msg wf_item_attribute_values.text_value%type;
1858
1859 begin
1860 if g_debug then
1861 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
1862 end if;
1863 if g_debug then
1864 hr_utility.set_location('Querying wf_runnable_processes_v',11);
1865 hr_utility.set_location('ItemType:'||itemtype,12);
1866 hr_utility.set_location('ItemKey:'||itemkey,13);
1867 end if;
1868
1869 begin
1870 lv_ntf_sub_msg := wf_engine.GetItemAttrText(itemtype => itemtype ,
1871 itemkey => itemkey,
1872 aname => 'HR_NTF_SUB_FND_MSG_ATTR',
1873 ignore_notfound=>true);
1874 if(lv_ntf_sub_msg is null) then
1875 SELECT wrpv.display_name displayName
1876 into getProcessDisplayName.lv_display_name
1877 FROM wf_runnable_processes_v wrpv
1878 WHERE wrpv.item_type = itemtype
1879 AND wrpv.process_name = wf_engine.GetItemAttrText (itemtype,itemkey,'PROCESS_NAME')
1880 AND rownum <=1;
1881 else
1882 fnd_message.set_name('PER',lv_ntf_sub_msg);
1883 getProcessDisplayName.lv_display_name := fnd_message.get;
1884 end if;
1885
1886 exception
1887 when others then
1888 getProcessDisplayName.lv_display_name := itemtype||':'||itemkey;
1889 end;
1890 if g_debug then
1891 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 20);
1892 end if;
1893
1894 return getProcessDisplayName.lv_display_name;
1895 exception
1896 when others then
1897 raise;
1898 end getProcessDisplayName;
1899
1900
1901 procedure getProcessDisplayName(document_id IN Varchar2,
1902 display_type IN Varchar2,
1903 document IN OUT NOCOPY varchar2,
1904 document_type IN OUT NOCOPY Varchar2)
1905 is
1906 c_proc varchar2(30) default 'getProcessDisplayName';
1907 lv_item_type wf_item_activity_statuses.item_type%type;
1908 lv_item_key wf_item_activity_statuses.item_key%type;
1909
1910 begin
1911 g_debug := hr_utility.debug_enabled;
1912 if g_debug then
1913 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
1914 end if;
1915
1916 if g_debug then
1917 hr_utility.set_location('Calling hr_workflow_ss.get_item_type_and_key for NtfId:'||document_id, 11);
1918 end if;
1919 -- get the itemtype and item key for the notification id
1920 hr_workflow_ss.get_item_type_and_key(document_id,lv_item_type,lv_item_key);
1921 -- set the document type
1922 document_type := wf_notification.doc_html;
1923 -- set the document
1924 if g_debug then
1925 hr_utility.set_location('Calling getProcessDisplayName',12);
1926 hr_utility.set_location('ItemType:'||lv_item_type,13);
1927 hr_utility.set_location('ItemKey:'||lv_item_key,14);
1928 end if;
1929 document := hr_workflow_ss.getProcessDisplayName(lv_item_type,lv_item_key);
1930
1931 if g_debug then
1932 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
1933 end if;
1934
1935 exception
1936 when others then
1937 document :=null;
1938 hr_utility.set_location('hr_workflow_ss.getProcessDisplayName errored : '||SQLERRM ||' '||to_char(SQLCODE), 20);
1939 Wf_Core.Context('hr_workflow_ss', 'getProcessDisplayName', document_id, display_type);
1940 raise;
1941 end getProcessDisplayName;
1942
1943
1944 procedure getApprovalMsgSubject(document_id IN Varchar2,
1945 display_type IN Varchar2,
1946 document IN OUT NOCOPY varchar2,
1947 document_type IN OUT NOCOPY Varchar2)
1948 is
1949 c_proc varchar2(30) default 'getApprovalMsgSubject';
1950 lv_item_type wf_item_activity_statuses.item_type%type;
1951 lv_item_key wf_item_activity_statuses.item_key%type;
1952 l_creator_person_id per_people_f.person_id%type;
1953 l_creator_disp_name wf_users.display_name%type;
1954 l_creator_username wf_users.name%type;
1955 l_current_person_id per_people_f.person_id%type;
1956 l_current_disp_name wf_users.display_name%type;
1957 l_current_username wf_users.name%type;
1958 lv_process_display_name wf_runnable_processes_v.display_name%type;
1959 lv_ntf_sub_msg wf_item_attribute_values.text_value%type;
1960 lv_custom_callBack varchar2(60);
1961 l_sqlbuf Varchar2(1000);
1962
1963 begin
1964 g_debug := hr_utility.debug_enabled;
1965 if g_debug then
1966 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 10);
1967 end if;
1968
1969 -- check if we have custom callback for the Approval Ntf subject
1970 --
1971 begin
1972 lv_custom_callBack := hr_xml_util.get_node_value(
1973 p_transaction_id => getattrnumber(document_id,'HR_TRANSACTION_REF_ID_ATTR','TRUE')
1974 ,p_desired_node_value => 'ApprNtfSubCallBack'
1975 ,p_xpath => 'Transaction/TransCtx');
1976 If lv_custom_callBack is not null Then
1977 l_sqlbuf:= 'BEGIN ' || lv_custom_callBack
1978 || ' (document_id => :1 '
1979 || ' ,display_type => :2 '
1980 || ' ,document => :3 '
1981 || ' ,document_type => :4 ); END; ';
1982 EXECUTE IMMEDIATE l_sqlbuf using in document_id,
1983 in display_type,
1984 in out document,
1985 in out document_type;
1986 return;
1987 End If;
1988 exception
1989 when others then
1990 document :=null;
1991 hr_utility.set_location('hr_workflow_ss.getApprovalMsgSubject errored for custom call: '||SQLERRM ||' '||to_char(SQLCODE), 100);
1992 Wf_Core.Context('hr_workflow_ss', 'getApprovalMsgSubject', document_id, display_type);
1993 raise;
1994 end;
1995
1996
1997
1998 -- get the itemtype and item key for the notification id
1999 if g_debug then
2000 hr_utility.set_location('Calling hr_workflow_ss.get_item_type_and_key for NtfId:'||document_id, 11);
2001 end if;
2002 hr_workflow_ss.get_item_type_and_key(document_id,lv_item_type,lv_item_key);
2003
2004 -- get the process display name
2005 if g_debug then
2006 hr_utility.set_location('Calling getProcessDisplayName',12);
2007 hr_utility.set_location('ItemType:'||lv_item_type,13);
2008 hr_utility.set_location('ItemKey:'||lv_item_key,14);
2009 end if;
2010 -- lv_process_display_name := hr_workflow_ss.getProcessDisplayName(lv_item_type,lv_item_key);
2011
2012 lv_ntf_sub_msg := wf_engine.GetItemAttrText(itemtype => lv_item_type ,
2013 itemkey => lv_item_key,
2014 aname => 'HR_NTF_SUB_FND_MSG_ATTR',
2015 ignore_notfound=>true);
2016
2017 if(lv_ntf_sub_msg is null) then
2018 lv_process_display_name := hr_workflow_ss.getProcessDisplayName(lv_item_type,lv_item_key);
2019 else
2020 fnd_message.set_name('PER',lv_ntf_sub_msg);
2021 lv_process_display_name:= fnd_message.get;
2022 end if;
2023
2024
2025 l_creator_person_id:= wf_engine.GetItemAttrNumber
2026 (itemtype => lv_item_type
2027 ,itemkey => lv_item_key
2028 ,aname => 'CREATOR_PERSON_ID');
2029
2030
2031 l_current_person_id:= wf_engine.GetItemAttrNumber
2032 (itemtype => lv_item_type
2033 ,itemkey => lv_item_key
2034 ,aname => 'CURRENT_PERSON_ID');
2035 if g_debug then
2036 hr_utility.set_location('Creator_person_id:'||l_creator_person_id,15);
2037 hr_utility.set_location('Current_person_id:'||l_current_person_id,16);
2038 end if;
2039 if g_debug then
2040 hr_utility.set_location('Building subject for NtfId:'||document_id,17);
2041 end if;
2042 if(l_creator_person_id=l_current_person_id) then
2043 if g_debug then
2044 hr_utility.set_location('calling wf_directory.GetUserName for person_id:'||l_creator_person_id,18);
2045 end if;
2046
2047 -- get creator display name from role
2048 wf_directory.GetUserName
2049 (p_orig_system => 'PER'
2050 ,p_orig_system_id => l_creator_person_id
2051 ,p_name => l_creator_username
2052 ,p_display_name => l_creator_disp_name);
2053
2054 if getOrganizationManagersubject(lv_item_type,lv_item_key) is not null then
2055 l_creator_disp_name := getOrganizationManagersubject(lv_item_type,lv_item_key) || ' (proposed by ' || l_creator_disp_name || ')';
2056 end if;
2057 -- Subject pattern
2058 -- "Change Job for Doe, John "
2059 if g_debug then
2060 hr_utility.set_location('Getting message HR_SS_APPROVER_MSG_SUB_SELF',19);
2061 end if;
2062 fnd_message.set_name('PER','HR_SS_APPROVER_MSG_SUB_SELF');
2063 fnd_message.set_token('PROCESS_DISPLAY_NAME',lv_process_display_name,false);
2064 fnd_message.set_token('CURRENT_PERSON_DISPLAY_NAME',l_creator_disp_name,false);
2065 document := fnd_message.get;
2066
2067 else
2068 -- get creator display name from role
2069 if g_debug then
2070 hr_utility.set_location('calling wf_directory.GetUserName for person_id:'||l_creator_person_id,20);
2071 end if;
2072 wf_directory.GetUserName
2073 (p_orig_system => 'PER'
2074 ,p_orig_system_id => l_creator_person_id
2075 ,p_name => l_creator_username
2076 ,p_display_name => l_creator_disp_name);
2077
2078 -- get current person display name from role
2079 if g_debug then
2080 hr_utility.set_location('calling wf_directory.GetUserName for person_id:'||l_current_person_id,21);
2081 end if;
2082 wf_directory.GetUserName
2083 (p_orig_system => 'PER'
2084 ,p_orig_system_id => l_current_person_id
2085 ,p_name => l_current_username
2086 ,p_display_name => l_current_disp_name);
2087
2088 -- check if the username/wfrole is null or display name is null
2089 if(l_current_username is null OR l_current_disp_name is null) then
2090 -- To support name format, should not rely on the stored person name.
2091 -- Should rely on the person_id to get the name in correct format
2092
2093 begin
2094 if l_current_person_id is not null then
2095 l_current_disp_name := hr_person_name.get_person_name(l_current_person_id,sysdate );
2096 end if;
2097 exception
2098 when others then
2099 l_current_disp_name := null;
2100 end;
2101
2102 if(l_current_disp_name is null ) then
2103 -- cud still be null if person doesnot exist in per_all_people_f as of now.
2104 -- resort to the existing code of fetching from wf item attribute.
2105 l_current_disp_name := wf_engine.GetItemAttrText
2106 (itemtype => lv_item_type
2107 ,itemkey => lv_item_key
2108 ,aname => 'CURRENT_PERSON_DISPLAY_NAME');
2109 end if;
2110
2111 end if;
2112
2113 -- Subject pattern
2114 -- "Change Job for Doe, John (proposed by Bond, James)"
2115 if g_debug then
2116 hr_utility.set_location('Getting message HR_SS_APPROVER_MSG_SUB_REPORTS',22);
2117 end if;
2118
2119 fnd_message.set_name('PER','HR_SS_APPROVER_MSG_SUB_REPORTS');
2120 fnd_message.set_token('PROCESS_DISPLAY_NAME',lv_process_display_name,false);
2121 fnd_message.set_token('CURRENT_PERSON_DISPLAY_NAME',l_current_disp_name,false);
2122 fnd_message.set_token('CREATOR_PERSON_DISPLAY_NAME',l_creator_disp_name,false);
2123 document := fnd_message.get;
2124 end if;
2125
2126
2127 -- set the document type
2128 document_type := wf_notification.doc_html;
2129
2130 if g_debug then
2131 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
2132 end if;
2133
2134 exception
2135 when others then
2136 document :=null;
2137 hr_utility.set_location('hr_workflow_ss.getApprovalMsgSubject errored : '||SQLERRM ||' '||to_char(SQLCODE), 40);
2138 Wf_Core.Context('hr_workflow_ss', 'getApprovalMsgSubject', document_id, display_type);
2139 raise;
2140 end getApprovalMsgSubject;
2141
2142
2143 function getNextApproverForHist(p_item_type in varchar2, p_item_key in varchar2) return varchar2
2144 as
2145 -- local variables
2146 lv_procedure_name varchar2(30) default 'getNextApproverForHist';
2147 ln_transaction_id hr_api_transactions.transaction_id%TYPE;
2148 ln_creator_person_id per_all_people_f.person_id%type default null;
2149 ln_currentApprover_person_id per_people_f.person_id%type;
2150 ln_nextApprover_person_id per_people_f.person_id%type;
2151 ln_nextApprover_userName wf_users.name%type;
2152 ln_nextApprover_dispName wf_users.display_name%type;
2153 lv_last_approver_def VARCHAR2(10) DEFAULT 'Y';
2154 ln_last_default_approver_id per_people_f.person_id%type;
2155 ln_current_approver_index NUMBER ;
2156 ln_curr_def_appr_index NUMBER;
2157 ln_addntl_approvers NUMBER;
2158 lv_exists VARCHAR2(10);
2159 lv_dummy VARCHAR2(20);
2160 lv_isvalid VARCHAR2(10);
2161 lv_item_name VARCHAR2(100) DEFAULT gv_item_name;
2162 -- Variables for AME API
2163 ln_application_id integer;
2164 lv_transaction_id varchar2(25);
2165 lv_transaction_type varchar2(25);
2166 l_next_approver_rec ame_util.approverRecord;
2167 l_default_approvers ame_util.approversTable;
2168 l_foundApprLoc boolean default false;
2169
2170 v_approvalprocesscompleteynout varchar2(5);
2171 v_next_approver_rec ame_util.approverstable2;
2172 v_default_approvers ame_util.approversTable2;
2173
2174 begin
2175 hr_utility.set_location(lv_procedure_name,1);
2176 if(hr_utility.debug_enabled) then
2177 -- write debug statements
2178 hr_utility.set_location('Entered'||lv_procedure_name||'with itemtype:'||p_item_type, 2);
2179 hr_utility.set_location('Entered'||lv_procedure_name||'with itemkey:'||p_item_key, 2);
2180 end if;
2181
2182 -- processing logic
2183 if(p_item_type is null OR p_item_key is null) then
2184 -- no processing return null
2185 return null;
2186 else
2187 -- get the transaction id
2188 ln_transaction_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2189 itemkey => p_item_key,
2190 aname => 'TRANSACTION_ID');
2191 -- check if we have transaction id, if not no more iteration
2192 -- will this condition ever meet ???
2193 if(ln_transaction_id is null) then
2194 -- this is needed as AME depends on hr_api_transactions.transaction_id
2195 -- for processing the ame rules and approvers list
2196 return null;
2197 end if ;
2198
2199 -- get the AME transaction type and app id
2200 ln_application_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type ,
2201 itemkey => p_item_key,
2202 aname => 'HR_AME_APP_ID_ATTR');
2203 lv_transaction_type := wf_engine.GetItemAttrText(itemtype => p_item_type ,
2204 itemkey => p_item_key,
2205 aname => 'HR_AME_TRAN_TYPE_ATTR');
2206 -- check if the flow is using AME
2207 if(lv_transaction_type is null) then
2208 -- flow is using custom package for approvals
2209 -- -----------------------------------------------------------------------
2210 -- expose the wf control variables to the custom package
2211 -- -----------------------------------------------------------------------
2212 hr_approval_custom.g_itemtype := p_item_type;
2213 hr_approval_custom.g_itemkey := p_item_key;
2214
2215 -- process the additional approvers and default approvers
2216 -- get the total number of additional approvers for this transaction
2217 ln_addntl_approvers := NVL(wf_engine.GetItemAttrNumber(itemtype => p_item_type
2218 ,itemkey => p_item_key
2219 ,aname => 'ADDITIONAL_APPROVERS_NUMBER'
2220 ,ignore_notfound=>true), 0);
2221
2222
2223 -- attribute to hold the last_default approver from the heirarchy tree.
2224 OPEN csr_wiav(p_item_type,p_item_key,'CURRENT_APPROVER_INDEX');
2225 FETCH csr_wiav into lv_dummy;
2226 IF csr_wiav%notfound THEN
2227 -- create new wf_item_attribute_value to hold
2228 hr_approval_wf.create_item_attrib_if_notexist(p_item_type => p_item_type
2229 ,p_item_key => p_item_key
2230 ,p_name => 'CURRENT_APPROVER_INDEX');
2231
2232 wf_engine.SetItemAttrNumber (itemtype => p_item_type,
2233 itemkey => p_item_key,
2234 aname => 'CURRENT_APPROVER_INDEX',
2235 avalue => NULL);
2236
2237 END IF;
2238 CLOSE csr_wiav;
2239
2240
2241
2242
2243
2244 -- get the current_approver_index
2245 ln_current_approver_index := NVL(wf_engine.GetItemAttrNumber(itemtype => p_item_type
2246 ,itemkey => p_item_key
2247 ,aname => 'CURRENT_APPROVER_INDEX'
2248 ,ignore_notfound=>true), 0);
2249 -- set the item name
2250 lv_item_name := gv_item_name || to_char(ln_current_approver_index + 1);
2251
2252 -- check if we have additional approver for the next index.
2253 IF ln_current_approver_index <= ln_addntl_approvers THEN
2254 OPEN csr_wiav(p_item_type,p_item_key,lv_item_name);
2255 FETCH csr_wiav into lv_dummy;
2256
2257 IF csr_wiav%notfound THEN
2258 lv_exists := 'N';
2259 ELSE
2260 lv_exists := 'Y';
2261 lv_isvalid := wf_engine.GetItemAttrText(itemtype => p_item_type,
2262 itemkey => p_item_key,
2263 aname => lv_item_name
2264 ,ignore_notfound=>true);
2265 lv_isvalid := NVL(lv_isvalid,' ');
2266
2267 END IF;
2268 CLOSE csr_wiav;
2269 ELSE
2270 lv_exists := 'N';
2271 END IF;
2272
2273
2274 IF lv_exists <>'N' AND lv_isvalid <>'DELETED' THEN
2275 ln_nextApprover_person_id :=wf_engine.GetItemAttrNumber(itemtype => p_item_type,
2276 itemkey => p_item_key,
2277 aname => lv_item_name
2278 );
2279
2280 ELSE
2281 -- get the last default approver index
2282
2283 ln_last_default_approver_id := wf_engine.GetItemAttrNumber(itemtype => p_item_type,
2284 itemkey => p_item_key,
2285 aname => 'LAST_DEFAULT_APPROVER'
2286 ,ignore_notfound=>true);
2287
2288
2289
2290 -- get the next approver from the heirarchy tree.
2291 -- the l_current_forward_to_id resetting was removed for default approver.
2292 -- now the from column will show the last approver approved.
2293 ln_nextApprover_person_id :=
2294 hr_approval_custom.Get_Next_Approver(
2295 p_person_id =>NVL(ln_last_default_approver_id,
2296 wf_engine.GetItemAttrNumber
2297 (itemtype => p_item_type
2298 ,itemkey => p_item_key
2299 ,aname => 'CREATOR_PERSON_ID')));
2300
2301
2302 end if;
2303 else
2304 -- flow is using AME for approvals
2305 -- get the current approver
2306 ln_currentApprover_person_id := wf_engine.GetItemAttrNumber
2307 (p_item_type,
2308 p_item_key,
2309 'FORWARD_TO_PERSON_ID',
2310 true);
2311 -- check if the current approver and creator or same
2312 if(ln_currentApprover_person_id is not null
2313 AND (ln_currentApprover_person_id=getApprStartingPointPersonId(ln_transaction_id))) then
2314 -- call ame getNextApprover method directly as this is intial approval
2315 /*
2316 ame_api.getNextApprover(applicationIdIn =>ln_application_id,
2317 transactionIdIn =>ln_transaction_id,
2318 transactionTypeIn =>lv_transaction_type,
2319 nextApproverOut =>l_next_approver_rec); */
2320
2321 ame_api2.getNextApprovers4
2322 (applicationIdIn => ln_application_id
2323 ,transactionTypeIn => lv_transaction_type
2324 ,transactionIdIn => ln_transaction_id
2325 ,flagApproversAsNotifiedIn=>ame_util.booleanFalse
2326 ,approvalProcessCompleteYNOut => v_approvalprocesscompleteynout
2327 ,nextApproversOut => v_next_approver_rec);
2328
2329 --ln_nextApprover_person_id :=l_next_approver_rec.person_id;
2330
2331 if(v_approvalprocesscompleteynout<>'Y') then
2332 ln_nextApprover_person_id := v_next_approver_rec(1).orig_system_id;
2333 end if;
2334
2335 else
2336 -- get all approvers
2337 -- we need this as AME does not return next approver
2338 -- unless the approval status of current approver is set
2339 /*
2340 ame_api.getAllApprovers(applicationIdIn =>ln_application_id,
2341 transactionIdIn=>ln_transaction_id,
2342 transactionTypeIn =>lv_transaction_type,
2343 approversOut=>l_default_approvers); */
2344
2345 ame_api2.getAllApprovers7(applicationIdIn =>ln_application_id,
2346 transactionTypeIn=>lv_transaction_type,
2347 transactionIdIn =>ln_transaction_id,
2348 approvalProcessCompleteYNOut=>v_approvalProcessCompleteYNOut ,
2349 approversOut=>v_default_approvers );
2350
2351 -- special case AME always returns intiator as first approver
2352 -- so need to eliminate intiator
2353 if (ln_currentApprover_person_id is null) then
2354 ln_nextApprover_person_id :=v_default_approvers(1).orig_system_id;
2355 else
2356 -- loop through the approvers list to get the next approver
2357 -- set the default , creator is default approver
2358 ln_nextApprover_person_id :=getApprStartingPointPersonId(ln_transaction_id);
2359
2360 for i in 1..v_default_approvers.count loop
2361 if(l_foundApprLoc) then
2362 ln_nextApprover_person_id :=v_default_approvers(i).orig_system_id;
2363 exit;
2364 elsif(ln_currentApprover_person_id=v_default_approvers(i).orig_system_id) then
2365 l_foundApprLoc := true;
2366 else
2367 ln_nextApprover_person_id :=v_default_approvers(i).orig_system_id;
2368 end if;
2369 end loop;
2370 end if;
2371
2372 end if;
2373 end if;
2374
2375
2376 -- check if the ln_nextApprover_person_id is null
2377 if(ln_nextApprover_person_id is null) then
2378 return fnd_global.user_name;
2379 else
2380 wf_directory.GetUserName(p_orig_system => 'PER'
2381 ,p_orig_system_id => ln_nextApprover_person_id
2382 ,p_name => ln_nextApprover_userName
2383 ,p_display_name => ln_nextApprover_dispName);
2384
2385 return ln_nextApprover_userName;
2386 end if;
2387 end if;
2388
2389
2390
2391 if(hr_utility.debug_enabled) then
2392 -- write debug statements
2393 hr_utility.set_location('Leaving '||lv_procedure_name||'with itemtype:'||p_item_type, 10);
2394 end if;
2395
2396 EXCEPTION
2397 WHEN OTHERS THEN
2398 fnd_message.set_name('PER', SQLERRM ||' '||to_char(SQLCODE));
2399 hr_utility.raise_error;
2400 return null;
2401 end getNextApproverForHist;
2402
2403 function Authenticate(p_username in varchar2,
2404 p_nid in number,
2405 p_nkey in varchar2)
2406 return varchar2
2407 is
2408 recipient varchar2(320);
2409 orig_recipient varchar2(320);
2410 from_role varchar2(320);
2411 more_info_role varchar2(320);
2412
2413 l_username fnd_user.user_name%TYPE default null;
2414 userRoles Wf_Directory.RoleTable;
2415 matchFound boolean;
2416 begin
2417
2418 l_username := wf_advanced_worklist.Authenticate(p_username, p_nid, p_nkey);
2419
2420 if(l_username = p_username) then
2421 select RECIPIENT_ROLE, ORIGINAL_RECIPIENT, FROM_ROLE, MORE_INFO_ROLE
2422 into recipient, orig_recipient, from_role, more_info_role
2423 from WF_NOTIFICATIONS WN
2424 where WN.NOTIFICATION_ID = p_nid;
2425
2426 Wf_Directory.GetUserRoles(p_username,userRoles);
2427 matchFound := false;
2428
2429 -- loop through the roles and validate if the user role matches
2430 -- to orig_recipient
2431 for i in 1..userRoles.count loop
2432 -- fix for bug 4308800
2433 -- more info case
2434 if(userRoles(i) in (orig_recipient,more_info_role,recipient)) then
2435 matchFound := true;
2436 exit;
2437 end if;
2438 end loop;
2439
2440 if matchFound then
2441 return l_username;
2442 else
2443 return '';
2444 end if;
2445
2446
2447 end if;
2448
2449 exception
2450 when others then
2451 raise;
2452 end Authenticate;
2453
2454
2455
2456 function Authenticate(p_username in varchar2,
2457 p_txn_id in number
2458 )
2459 return varchar2
2460 is
2461 ntfId number;
2462 begin
2463 ntfId := hr_approval_ss.getApproverNtfId(p_txn_id);
2464 if (pqh_ss_workflow.is_notification_closed(ntfId) = 'Y') then
2465 return '';
2466 end if;
2467 return Authenticate(p_username,ntfId,null);
2468 exception
2469 when others then
2470 raise;
2471 end Authenticate;
2472
2473 function getOrganizationManagersubject
2474 (p_item_type IN varchar2,
2475 p_item_key IN varchar2)
2476 return varchar2
2477 is
2478 l_process_name varchar2(500) default null;
2479 l_txn_id number;
2480 l_txn_step_id number;
2481 l_current_person_id number;
2482 l_creator_person_id number;
2483 l_organization_name varchar2(500) default null;
2484 cursor csr_txn_steps
2485 IS
2486 select * from hr_api_transaction_steps where transaction_id = l_txn_id
2487 and api_name = 'HR_CCMGR_SS.PROCESS_API';
2488 type transaction_type is table of hr_api_transaction_steps%rowtype;
2489 txn_steps_rc transaction_type;
2490 l_counter number default 0;
2491
2492 cursor is_term_txn is
2493 select transaction_step_id from hr_api_transaction_steps where transaction_id = l_txn_id
2494 and api_name = 'HR_TERMINATION_SS.PROCESS_API';
2495 l_term_txn number;
2496
2497 begin
2498 l_txn_id := wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'TRANSACTION_ID',true);
2499 l_creator_person_id:= wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'CREATOR_PERSON_ID',true);
2500 l_current_person_id := wf_engine.GetItemAttrNumber(p_item_type,p_item_key,'CURRENT_PERSON_ID',true);
2501 l_process_name := null;
2502
2503 open is_term_txn;
2504 fetch is_term_txn into l_term_txn;
2505 if is_term_txn%found then
2506 close is_term_txn;
2507 return l_process_name;
2508 else
2509 close is_term_txn;
2510 end if;
2511
2512 open csr_txn_steps;
2513 fetch csr_txn_steps bulk collect into txn_steps_rc;
2514 l_counter := txn_steps_rc.count;
2515 if l_counter = 0 then
2516 return l_process_name;
2517 end if;
2518 if l_counter = 1 then
2519 l_organization_name := hr_transaction_api.get_varchar2_value(txn_steps_rc(1).transaction_step_id,'P_ORGANIZATION_NAME');
2520 else
2521 for i in 1 .. txn_steps_rc.count loop
2522 l_txn_step_id := txn_steps_rc(i).transaction_step_id;
2523 if(l_counter = 1) then
2524 l_organization_name := l_organization_name || hr_transaction_api.get_varchar2_value(l_txn_step_id,'P_ORGANIZATION_NAME');
2525 else
2526 l_organization_name := l_organization_name || hr_transaction_api.get_varchar2_value(l_txn_step_id,'P_ORGANIZATION_NAME') || ', ';
2527 end if;
2528
2529 l_counter := l_counter - 1;
2530 end loop;
2531 end if;
2532 close csr_txn_steps;
2533 if( (l_organization_name is not null) and (l_creator_person_id = l_current_person_id) ) then
2534 l_process_name := l_organization_name;
2535 end if;
2536 return l_process_name;
2537 end getOrganizationManagersubject;
2538
2539 PROCEDURE isFyiNtfDet
2540 (itemtype in varchar2
2541 ,itemkey in varchar2
2542 ,actid in number
2543 ,funcmode in varchar2
2544 ,resultout out nocopy varchar2)
2545 IS
2546 l_creator_disp_name wf_users.display_name%type;
2547 l_details varchar2(30) := null;
2548 BEGIN
2549
2550 if getOrganizationManagersubject(itemtype,itemkey) is not null then
2551 l_creator_disp_name := getOrganizationManagersubject(itemtype,itemkey);
2552 wf_engine.SetItemAttrText
2553 (itemtype => itemtype
2554 ,itemkey => itemkey
2555 ,aname => 'CURRENT_PERSON_DISPLAY_NAME'
2556 ,avalue => l_creator_disp_name);
2557 end if;
2558
2559 l_details := wf_engine.getitemattrtext(itemtype,
2560 itemkey,
2561 'FYI_NTF_DETAILS',true);
2562
2563 if l_details = 'Y' then
2564 resultout := 'COMPLETE:'|| 'Y';
2565 else
2566 resultout := 'COMPLETE:'|| 'N';
2567 end if;
2568
2569 EXCEPTION
2570 WHEN OTHERS THEN NULL;
2571 RAISE;
2572 END isFyiNtfDet;
2573
2574
2575 END hr_workflow_ss;