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