1 PACKAGE BODY PO_REQAPPROVAL_FINDAPPRV1 AS
2 /* $Header: POXWPA3B.pls 120.5.12010000.2 2008/08/04 08:35:58 rramasam ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7
8 /*=======================================================================+
9 | FILENAME
10 | POXWPA3B.sql
11 |
12 | DESCRIPTION
13 | PL/SQL body for package: PO_REQAPPROVAL_FINDAPPRV1
14 |
15 | NOTES Ben Chihaoui Created 6/15/97
16 | MODIFIED (MM/DD/YY)
17 *=======================================================================*/
18
19
20 -- The following are local/Private procedure that support the workflow APIs:
21
22 FUNCTION IsForwardToProvided(itemtype in varchar2, itemkey in varchar2) RETURN VARCHAR2;
23
24 --
25 -- change CheckForward as public procedure.
26 --
27 --FUNCTION CheckForwardTo( p_username varchar2, x_user_id IN OUT number) RETURN VARCHAR2;
28 --
29 --
30 PROCEDURE GetApprovalPathId(itemtype VARCHAR2, itemkey VARCHAR2);
31
32 --
33 FUNCTION GetForwardMode(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
34
35 --
36 FUNCTION UsePositionFlag RETURN VARCHAR2;
37
38 --
39 FUNCTION GetMgrHRHier(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
40
41 --
42 FUNCTION GetMgrPOHier(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
43
44 --
45 /* Bug# 1496490
46 ** New Procedure to check the owner can approve flag value
47 */
48
49 PROCEDURE CheckOwnerCanApprove (itemtype in VARCHAR2, itemkey in VARCHAR2,
50 CanOwnerApprove out NOCOPY VARCHAR2);
51
52
53 /******************************************************************************/
54
55 --
56 procedure Set_Forward_To_From_App_fwd( itemtype in varchar2,
57 itemkey in varchar2,
58 actid in number,
59 funcmode in varchar2,
60 resultout out NOCOPY varchar2 ) is
61 x_progress varchar2(100);
62
63 l_doc_string varchar2(200);
64 l_preparer_user_name varchar2(100);
65
66 BEGIN
67
68 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Set_Forward_To_From_App_fwd: 01';
69 IF (g_po_wf_debug = 'Y') THEN
70 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
71 END IF;
72
73
74 -- Do nothing in cancel or timeout mode
75 --
76 if (funcmode <> wf_engine.eng_run) then
77
78 resultout := wf_engine.eng_null;
79 return;
80
81 end if;
82
83 /* Bug 2114328: Need to set fnd_context for responder */
84
85 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
86 itemkey => itemkey,
87 aname => 'RESPONDER_USER_ID',
88 avalue => fnd_global.USER_ID);
89
90 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
91 itemkey => itemkey,
92 aname => 'RESPONDER_RESP_ID',
93 avalue => fnd_global.RESP_ID);
94
95 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
96 itemkey => itemkey,
97 aname => 'RESPONDER_APPL_ID',
98 avalue => fnd_global.RESP_APPL_ID);
99
100 -- resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
101
102 resultout := 'COMPLETE' ;
103
104 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Set_Forward_To_From_App_fwd: 02';
105 IF (g_po_wf_debug = 'Y') THEN
106 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
107 END IF;
108
109
110 EXCEPTION
111
112 WHEN OTHERS THEN
113 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
114 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
115 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','Set_Forward_To_From_App_fwd',x_progress);
116 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_FINDAPPRV1.SET_FORWARD_TO_FROM_APP_FWD');
117 raise;
118
119 END Set_Forward_To_From_App_fwd;
120 --
121 --
122 procedure Set_Fwd_To_From_App_timeout( itemtype in varchar2,
123 itemkey in varchar2,
124 actid in number,
125 funcmode in varchar2,
126 resultout out NOCOPY varchar2 ) is
127
128 l_approver_id number;
129 l_approver_username varchar2(100);
130 l_approver_disp_name varchar2(100);
131 l_error_msg varchar2(200);
132 x_progress varchar2(100);
133
134 l_doc_string varchar2(200);
135 l_preparer_user_name varchar2(100);
136
137 BEGIN
138
139 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Set_Fwd_To_From_App_timeout: 01';
140 IF (g_po_wf_debug = 'Y') THEN
141 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
142 END IF;
143
144
145 -- Do nothing in cancel or timeout mode
146 --
147 if (funcmode <> wf_engine.eng_run) then
148
149 resultout := wf_engine.eng_null;
150 return;
151
152 end if;
153
154 /* Bug 2114328: Need to set fnd_context for responder */
155
156 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
157 itemkey => itemkey,
158 aname => 'RESPONDER_USER_ID',
159 avalue => fnd_global.USER_ID);
160
161 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
162 itemkey => itemkey,
163 aname => 'RESPONDER_RESP_ID',
164 avalue => fnd_global.RESP_ID);
165
166 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
167 itemkey => itemkey,
168 aname => 'RESPONDER_APPL_ID',
169 avalue => fnd_global.RESP_APPL_ID);
170
171 /* If the responder chooses APPROVE or the notification times out, then
172 ** Set the Approver to be the old forward-to
173 */
174 l_approver_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
175 itemkey => itemkey,
176 aname => 'FORWARD_TO_ID');
177
178 l_approver_username := wf_engine.GetItemAttrText (itemtype => itemType,
179 itemkey => itemkey,
180 aname => 'FORWARD_TO_USERNAME');
181
182 l_approver_disp_name := wf_engine.GetItemAttrText (itemtype => itemType,
183 itemkey => itemkey,
184 aname => 'FORWARD_TO_DISPLAY_NAME');
185 --
186 wf_engine.SetItemAttrNumber (itemtype => itemtype,
187 itemkey => itemkey,
188 aname => 'APPROVER_EMPID',
189 avalue => l_approver_id);
190
191 wf_engine.SetItemAttrText ( itemtype => itemType,
192 itemkey => itemkey,
193 aname => 'APPROVER_USER_NAME' ,
194 avalue => l_approver_username);
195
196 wf_engine.SetItemAttrText ( itemtype => itemType,
197 itemkey => itemkey,
198 aname => 'APPROVER_DISPLAY_NAME' ,
199 avalue => l_approver_disp_name);
200
201 /*
202 ** Reset the Forward-to and Forward-From.
203 */
204 wf_engine.SetItemAttrNumber (itemtype => itemtype,
205 itemkey => itemkey,
206 aname => 'FORWARD_TO_ID',
207 avalue => NULL);
208 --
209 wf_engine.SetItemAttrText ( itemtype => itemType,
210 itemkey => itemkey,
211 aname => 'FORWARD_TO_USERNAME' ,
212 avalue => NULL);
213
214 wf_engine.SetItemAttrText ( itemtype => itemType,
215 itemkey => itemkey,
216 aname => 'FORWARD_TO_DISPLAY_NAME' ,
217 avalue => NULL);
218 --
219
220 /* Bug# 1325552: kagarwal
221 ** Desc: In Main Requistion approval Process, when the 'Requisition Approval
222 ** Reminder 2' times out the forward from and forward to data is all reset to
223 ** Null.
224 **
225 ** Now when the approval process is unable to Find a new approver it 'Returns
226 ** Requisition to Submitter'. This process sends Notification to the preparer
227 ** and the last approver, which is the forward from person. Since the Forward
228 ** From data is not set the workflow errors out.
229 **
230 ** Hence we need to set the forward from data to the last approver in the
231 ** Set_Fwd_To_From_App_timeout procedure which is the forward to person
232 ** when the timeout occurs.
233 */
234
235 wf_engine.SetItemAttrNumber (itemtype => itemtype,
236 itemkey => itemkey,
237 aname => 'FORWARD_FROM_ID',
238 avalue => l_approver_id);
239 --
240 wf_engine.SetItemAttrText ( itemtype => itemType,
241 itemkey => itemkey,
242 aname => 'FORWARD_FROM_USER_NAME' ,
243 avalue => l_approver_username);
244
245 wf_engine.SetItemAttrText ( itemtype => itemType,
246 itemkey => itemkey,
247 aname => 'FORWARD_FROM_DISP_NAME' ,
248 avalue => l_approver_disp_name);
249
250
251 /* Reset the FORWARD_TO_USERNAME_RESPONSE attribute */
252 wf_engine.SetItemAttrText (itemtype => itemtype,
253 itemkey => itemkey,
254 aname => 'FORWARD_TO_USERNAME_RESPONSE',
255 avalue => NULL);
256
257 /* Set the Subject of the Approval notification to "requires your approval".
258 ** Since the user did not enter a forward-to, then set the
259 ** "Invalid Forward-to" message to NULL.
260 */
261 fnd_message.set_name ('PO','PO_WF_NOTIF_REQUIRES_APPROVAL');
262 l_error_msg := fnd_message.get;
263
264 wf_engine.SetItemAttrText ( itemtype => itemType,
265 itemkey => itemkey,
266 aname => 'REQUIRES_APPROVAL_MSG' ,
267 avalue => l_error_msg);
268
269 wf_engine.SetItemAttrText ( itemtype => itemType,
270 itemkey => itemkey,
271 aname => 'WRONG_FORWARD_TO_MSG' ,
272 avalue => '');
273
274 -- resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
275
276 resultout := 'COMPLETE' ;
277
278 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Set_Fwd_To_From_App_timeout: 02';
279 IF (g_po_wf_debug = 'Y') THEN
280 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
281 END IF;
282
283 EXCEPTION
284
285 WHEN OTHERS THEN
286 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
287 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
288 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','Set_Fwd_To_From_App_timeout',x_progress);
289 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_FINDAPPRV1.SET_FWD_TO_FROM_APP_TIMEOUT');
290 raise;
291
292 END Set_Fwd_To_From_App_timeout;
293 --
294
295 -- Is_Forward_To_Valid
296 -- Is Forward-To userame entered in the Forward-To field in response to the
297 -- the approval notification, a valid username. If not resend the
298 -- notification back to the user.
299 --
300 -- IN
301 -- itemtype -- itemkey -- actid -- funcmode
302 -- OUT
303 -- Resultout
304 -- - Y/N
305 --
306 procedure Is_Forward_To_Valid( itemtype in varchar2,
307 itemkey in varchar2,
308 actid in number,
309 funcmode in varchar2,
310 resultout out NOCOPY varchar2 ) is
311
312 x_user_id number;
313 l_approver_empid number;
314 l_forward_to_username_response varchar2(100);
315 l_forward_to_username varchar2(100);
316 l_forward_to_username_disp varchar2(240);
317 l_forward_to_id number;
318 l_error_msg varchar2(500);
319 x_progress varchar2(200);
320
321 l_doc_string varchar2(200);
322 l_preparer_user_name varchar2(100);
323
324 /* Bug# 1496490 */
325 l_preparer_id number;
326 x_CanOwnerApproveFlag varchar2(1);
327
328 l_orgid number;
329
330 BEGIN
331
332 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_Forward_To_Valid: 01';
333 IF (g_po_wf_debug = 'Y') THEN
334 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
335 END IF;
336
337
338 -- Do nothing in cancel or timeout mode
339 --
340 if (funcmode <> wf_engine.eng_run) then
341
342 resultout := wf_engine.eng_null;
343 return;
344
345 end if;
346
347 /* Bug# 2353153
348 ** Setting application context
349 */
350 -- Context Setting revamp
351 /* PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(itemtype, itemkey); */
352
353
354 /* Bug# 1796605: kagarwal
355 ** Desc: When responding from the E-mail notifications, the forward
356 ** to failed as the org context was not set.
357 */
358
359 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
360 itemkey => itemkey,
361 aname => 'ORG_ID');
362
363 IF l_orgid is NOT NULL THEN
364
365 po_moac_utils_pvt.set_org_context(l_orgid); --<R12 MOAC>
366
367 END IF;
368
369 /* Check that the value entered by responder as the FORWARD-TO user, is actually
370 ** a valid employee (has an employee id).
371 ** If valid, then set the FORWARD-FROM USERNAME and ID from the old FORWARD-TO.
372 ** Then set the Forward-To to the one the user entered in the response.
373 */
374 /* NOTE: We take the value entered by the user and set it to ALL CAPITAL LETTERS!!!
375 */
376 l_forward_to_username_response := wf_engine.GetItemAttrText (itemtype => itemtype,
377 itemkey => itemkey,
378 aname => 'FORWARD_TO_USERNAME_RESPONSE');
379
380 l_forward_to_username_response := UPPER(l_forward_to_username_response);
381
382 x_progress := 'Set_Forward_To_From_App_fwd: 02';
383 x_progress := x_progress || ' Forward-To=' || l_forward_to_username_response;
384 IF (g_po_wf_debug = 'Y') THEN
385 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
386 END IF;
387
388 IF CheckForwardTo(l_forward_to_username_response, x_user_id) = 'Y' THEN
389
390 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_Forward_To_Valid: 010 '||
391 'x_user_id: ' ||x_user_id;
392 IF (g_po_wf_debug = 'Y') THEN
393 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
394 END IF;
395
396 /* The FORWARD-FROM is now the old FORWARD-TO and the NEW FORWARD-TO is set
397 ** to what the user entered in the response
398 */
399
400 l_forward_to_username:= wf_engine.GetItemAttrText (itemtype => itemtype,
401 itemkey => itemkey,
402 aname => 'FORWARD_TO_USERNAME');
403
404 /* Bug# 1352995: kagarwal
405 ** Desc: When the approver takes approve action from the Notification form after
406 ** modifying the PO. The Approver attributes are set but forward-to attributes
407 ** are set to Null. Now if any error is encountered, the Notification is sent to
408 ** the approver and if after the error the approver forwards the PO, the
409 ** is_forward_to_valid function sets the forward-from and approver attributes
410 ** from the forward-to attributes (it has not changed as of now) and then sets
411 ** the forward-to attributes to the the response-forward person but in this case
412 ** the forward-to attributes had been set to null by previous approve action
413 ** hence the approver_username was set to NULL by this function.
414 **
415 ** If the forward-to attributes are null when taking the forward action we
416 ** should use the approver attributes. This will ensure that the approver
417 ** attributes and the forward-from attributes are not set to NULL on
418 ** forwarding the document.
419 */
420
421 IF l_forward_to_username is NOT NULL THEN
422
423 l_forward_to_username_disp:= wf_engine.GetItemAttrText (itemtype => itemtype,
424 itemkey => itemkey,
425 aname => 'FORWARD_TO_DISPLAY_NAME');
426
427 l_forward_to_id:= wf_engine.GetItemAttrNumber (itemtype => itemtype,
428 itemkey => itemkey,
429 aname => 'FORWARD_TO_ID');
430 ELSE /* get the approver name who took this action */
431 l_forward_to_username:= wf_engine.GetItemAttrText (itemtype => itemtype,
432 itemkey => itemkey,
433 aname => 'APPROVER_USER_NAME');
434
435 l_forward_to_username_disp:= wf_engine.GetItemAttrText (itemtype => itemtype,
436 itemkey => itemkey,
437 aname => 'APPROVER_DISPLAY_NAME');
438
439 l_forward_to_id:= wf_engine.GetItemAttrNumber (itemtype => itemtype,
440 itemkey => itemkey,
441 aname => 'APPROVER_EMPID');
442 END IF;
443
444 /* Bug# 1496490: kagarwal
445 ** Desc: If the forward to person is the preparer and the
446 ** owner can approve flag is set to N then return the Notification
447 ** to the responder with Invalid Forward to Message
448 */
449 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_Forward_To_Valid: 015';
450 IF (g_po_wf_debug = 'Y') THEN
451 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
452 END IF;
453
454 l_preparer_id := wf_engine.GetItemAttrText(itemtype => itemtype,
455 itemkey => itemkey,
456 aname => 'PREPARER_ID');
457
458 if (x_user_id = l_preparer_id) then
459 PO_REQAPPROVAL_FINDAPPRV1.CheckOwnerCanApprove(itemtype, itemkey,
460 x_CanOwnerApproveFlag);
461
462 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_Forward_To_Valid: 020 ' ||
463 'x_CanOwnerApproveFlag: '||x_CanOwnerApproveFlag;
464 IF (g_po_wf_debug = 'Y') THEN
465 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
466 END IF;
467
468 if x_CanOwnerApproveFlag = 'N' then
469
470 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_Forward_To_Valid: 025';
471 IF (g_po_wf_debug = 'Y') THEN
472 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
473 END IF;
474
475 fnd_message.set_name('PO', 'PO_WF_NOTIF_INVALID_FORWARD');
476 l_error_msg := fnd_message.get;
477
478 wf_engine.SetItemAttrText (itemtype => itemtype,
479 itemkey => itemkey,
480 aname => 'REQUIRES_APPROVAL_MSG',
481 avalue => '');
482
483 wf_engine.SetItemAttrText (itemtype => itemtype,
484 itemkey => itemkey,
485 aname => 'WRONG_FORWARD_TO_MSG',
486 avalue => l_error_msg);
487
488 resultout := wf_engine.eng_completed || ':' || 'N';
489
490 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_Forward_To_Valid: 050';
491 IF (g_po_wf_debug = 'Y') THEN
492 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
493 END IF;
494
495 return;
496 end if;
497 end if;
498
499 /* end fix Bug# 1496490 */
500
501 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_Forward_To_Valid: 060';
502 IF (g_po_wf_debug = 'Y') THEN
503 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
504 END IF;
505
506
507 /* Set the FORWARD_FROM */
508 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
509 itemkey => itemkey,
510 aname => 'FORWARD_FROM_ID',
511 avalue => l_forward_to_id);
512
513 wf_engine.SetItemAttrText ( itemtype => itemtype,
514 itemkey => itemkey,
515 aname => 'FORWARD_FROM_USER_NAME',
516 avalue => l_forward_to_username);
517
518 wf_engine.SetItemAttrText ( itemtype => itemtype,
519 itemkey => itemkey,
520 aname => 'FORWARD_FROM_DISP_NAME',
521 avalue => l_forward_to_username_disp);
522
523 /* Set the approver to the person who took the action on the notification,
524 ** i.e. the old forward-to person
525 */
526 wf_engine.SetItemAttrNumber ( itemtype => itemtype,
527 itemkey => itemkey,
528 aname => 'APPROVER_EMPID',
529 avalue => l_forward_to_id);
530
531 wf_engine.SetItemAttrText ( itemtype => itemtype,
532 itemkey => itemkey,
533 aname => 'APPROVER_USER_NAME',
534 avalue => l_forward_to_username);
535
536 wf_engine.SetItemAttrText ( itemtype => itemtype,
537 itemkey => itemkey,
538 aname => 'APPROVER_DISPLAY_NAME',
539 avalue => l_forward_to_username_disp);
540
541 /* Set the FORWARD-TO */
542
543 wf_engine.SetItemAttrText ( itemtype => itemtype,
544 itemkey => itemkey,
545 aname => 'FORWARD_TO_USERNAME',
546 avalue => l_forward_to_username_response);
547
548 wf_engine.SetItemAttrNumber ( itemtype => itemType,
549 itemkey => itemkey,
550 aname => 'FORWARD_TO_ID',
551 avalue => x_user_id);
552
553 /* Get the Display name for the user from the WF Directory */
554 wf_engine.SetItemAttrText ( itemtype => itemtype,
555 itemkey => itemkey,
556 aname => 'FORWARD_TO_DISPLAY_NAME',
557 avalue =>
558 wf_directory.GetRoleDisplayName(l_forward_to_username_response));
559
560 /* Reset the FORWARD_TO_USERNAME_RESPONSE attribute */
561 wf_engine.SetItemAttrText (itemtype => itemtype,
562 itemkey => itemkey,
563 aname => 'FORWARD_TO_USERNAME_RESPONSE',
564 avalue => NULL);
565
566 /* Set the Subject of the Approval notification to "requires your approval".
567 ** Since the user entered a valid forward-to, then set the
568 ** "Invalid Forward-to" message to NULL.
569 */
570 fnd_message.set_name ('PO','PO_WF_NOTIF_REQUIRES_APPROVAL');
571 l_error_msg := fnd_message.get;
572
573 wf_engine.SetItemAttrText ( itemtype => itemType,
574 itemkey => itemkey,
575 aname => 'REQUIRES_APPROVAL_MSG' ,
576 avalue => l_error_msg);
577
578 wf_engine.SetItemAttrText ( itemtype => itemType,
579 itemkey => itemkey,
580 aname => 'WRONG_FORWARD_TO_MSG' ,
581 avalue => '');
582
583 --
584 resultout := wf_engine.eng_completed || ':' || 'Y';
585 --
586
587 ELSE
588
589 /* Set the error message that will be shown to the user in the ERROR MESSAGE
590 ** Field in the Notification.
591 */
592
593 /* Set the Subject of the Approval notification to "Invalid forward-to"
594 ** Since the user entered an invalid forward-to, then set the
595 ** "requires your approval" message to NULL.
596 */
597 fnd_message.set_name ('PO','PO_WF_NOTIF_INVALID_FORWARD');
598 l_error_msg := fnd_message.get;
599
600 wf_engine.SetItemAttrText ( itemtype => itemType,
601 itemkey => itemkey,
602 aname => 'REQUIRES_APPROVAL_MSG' ,
603 avalue => '');
604
605 wf_engine.SetItemAttrText ( itemtype => itemType,
606 itemkey => itemkey,
607 aname => 'WRONG_FORWARD_TO_MSG' ,
608 avalue => l_error_msg);
609
610 --
611 resultout := wf_engine.eng_completed || ':' || 'N';
612 --
613
614 END IF;
615
616
617 END Is_Forward_To_Valid ;
618 --
619
620
621 -- Is_forward_to_provided
622 -- Did the submitter or the person responding to the approval notification provide
623 -- a Forward_to.
624 --
625 -- IN
626 -- itemtype, itemkey, actid, funcmode
627 -- OUT
628 -- Resultout
629 -- - Completed - Activity was completed without any errors.
630 --
631 procedure Is_forward_to_provided( itemtype in varchar2,
632 itemkey in varchar2,
633 actid in number,
634 funcmode in varchar2,
635 resultout out NOCOPY varchar2 ) is
636
637 l_forward_to_id number;
638 x_progress varchar2(100);
639
640 l_doc_string varchar2(200);
641 l_preparer_user_name varchar2(100);
642
643 BEGIN
644
645 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_forward_to_provided: 01';
646 IF (g_po_wf_debug = 'Y') THEN
647 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
648 END IF;
649
650
651 -- Do nothing in cancel or timeout mode
652 --
653 if (funcmode <> wf_engine.eng_run) then
654
655 resultout := wf_engine.eng_null;
656 return;
657
658 end if;
659
660
661 l_forward_to_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
662 itemkey => itemkey,
663 aname => 'FORWARD_TO_ID');
664
665 IF l_forward_to_id is NOT NULL THEN
666
667 /* Set the Approver to be the person receiving the approval notification */
668 wf_engine.SetItemAttrNumber (itemtype => itemtype,
669 itemkey => itemkey,
670 aname => 'APPROVER_EMPID',
671 avalue => l_forward_to_id);
672
673 resultout := wf_engine.eng_completed || ':' || 'Y';
674
675 ELSE
676
677 resultout := wf_engine.eng_completed || ':' || 'N';
678
679 END IF;
680
681
682 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_forward_to_provided: 03';
683 IF (g_po_wf_debug = 'Y') THEN
684 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
685 END IF;
686
687 EXCEPTION
688
689 WHEN OTHERS THEN
690 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
691 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
692 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','Is_forward_to_provided',x_progress);
693 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_FINDAPPRV1.IS_FORWARD_TO_PROVIDED');
694 raise;
695
696 END Is_forward_to_provided;
697
698
699 -- Is_Forward_To_User_Name_Valid
700 -- Is the user_name valid for the next approver?
701 --
702 -- IN
703 -- itemtype -- itemkey -- actid -- funcmode
704 -- OUT
705 -- Resultout
706 -- - Y/N
707 procedure Is_Forward_To_User_Name_Valid(itemtype in varchar2,
708 itemkey in varchar2,
709 actid in number,
710 funcmode in varchar2,
711 resultout out NOCOPY varchar2 ) IS
712
713 l_user_name VARCHAR2(100);
714 l_id NUMBER;
715 l_disp_name VARCHAR2(100);
716 x_progress varchar2(100);
717
718 l_doc_string varchar2(200);
719 l_preparer_user_name varchar2(100);
720
721 BEGIN
722
723 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_forward_to_user_name_valid: 01';
724 IF (g_po_wf_debug = 'Y') THEN
725 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
726 END IF;
727
728
729 -- Do nothing in cancel or timeout mode
730 --
731 if (funcmode <> wf_engine.eng_run) then
732
733 resultout := wf_engine.eng_null;
734 return;
735
736 end if;
737
738 l_user_name := wf_engine.GetItemAttrText (itemtype => itemtype,
739 itemkey => itemkey,
740 aname => 'FORWARD_TO_USERNAME');
741
742 IF l_user_name is NOT NULL THEN
743
744 resultout := wf_engine.eng_completed || ':' || 'Y';
745
746 ELSE
747 l_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
748 itemkey => itemkey,
749 aname => 'FORWARD_TO_ID');
750
751 /* Bug# 1312794: kagarwal
752 ** Desc: The SQL below would retun multiple rows for a person_id
753 ** if the person has changed names or status or was rehired.
754 **
755 ** Added addition clause:
756 ** trunc(sysdate) BETWEEN effective_start_date
757 ** AND effective_end_date
758 */
759
760 SELECT full_name
761 INTO l_disp_name
762 FROM PER_ALL_PEOPLE_F -- <BUG 6615913>
763 WHERE person_id = l_id
764 AND trunc(sysdate) BETWEEN effective_start_date
765 AND effective_end_date;
766
767 wf_engine.SetItemAttrText ( itemtype => itemType,
768 itemkey => itemkey,
769 aname => 'APPROVER_DISPLAY_NAME' ,
770 avalue => l_disp_name);
771
772 resultout := wf_engine.eng_completed || ':' || 'N';
773
774 END IF;
775
776 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Is_forward_to_user_name_valid: 02';
777 IF (g_po_wf_debug = 'Y') THEN
778 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
779 END IF;
780
781 EXCEPTION
782
783 WHEN OTHERS THEN
784 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
785 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
786 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','Is_forward_to_user_name_valid',x_progress);
787 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_FINDAPPRV1.IS_FORWARD_TO_USER_NAME_VALID');
788 raise;
789
790 END Is_Forward_To_User_Name_Valid;
791
792
793
794 --
795 -- Get_approval_path_id
796 -- Get the requisition values on the doc header and assigns then to workflow attributes
797 --
798 procedure Get_approval_path_id( itemtype in varchar2,
799 itemkey in varchar2,
800 actid in number,
801 funcmode in varchar2,
802 resultout out NOCOPY varchar2 ) is
803
804 l_document_type varchar2(25);
805 l_document_id number;
806 l_orgid number;
807 l_requisition_header_id NUMBER;
808 x_progress varchar2(100);
809
810 l_doc_string varchar2(200);
811 l_preparer_user_name varchar2(100);
812
813 BEGIN
814
815 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Get_approval_path_id: 01';
816 IF (g_po_wf_debug = 'Y') THEN
817 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
818 END IF;
819
820
821 -- Do nothing in cancel or timeout mode
822 --
823 if (funcmode <> wf_engine.eng_run) then
824
825 resultout := wf_engine.eng_null;
826 return;
827
828 end if;
829
830 -- Set the multi-org context
831 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
832 itemkey => itemkey,
833 aname => 'DOCUMENT_TYPE');
834 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
835 itemkey => itemkey,
836 aname => 'DOCUMENT_ID');
837
838 -- PO_REQAPPROVAL_INIT1.get_multiorg_context (l_document_type, l_document_id, x_orgid);
839 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
840 itemkey => itemkey,
841 aname => 'ORG_ID');
842
843 IF l_orgid is NOT NULL THEN
844
845 po_moac_utils_pvt.set_org_context(l_orgid); --<R12 MOAC>
846
847 END IF;
848
849
850 PO_REQAPPROVAL_FINDAPPRV1.GetApprovalPathId(itemtype,itemkey);
851
852
853 --
854 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
855 --
856 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Get_approval_path_id: 02';
857 IF (g_po_wf_debug = 'Y') THEN
858 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
859 END IF;
860
861
862 EXCEPTION
863
864 WHEN OTHERS THEN
865 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
866 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
867 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','Get_approval_path_id',x_progress);
868 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_FINDAPPRV1.GET_APPROVAL_PATH_ID');
869 raise;
870
871 END Get_approval_path_id;
872
873 -- Get_Forward_mode
874 -- Get the requisition values on the doc header and assigns then to workflow attributes
875 --
876 procedure Get_Forward_mode(itemtype in varchar2,
877 itemkey in varchar2,
878 actid in number,
879 funcmode in varchar2,
880 resultout out NOCOPY varchar2 ) is
881
882 l_document_type varchar2(25);
883 l_document_id number;
884 l_orgid number;
885 l_forward_mode varchar2(25);
886 x_progress varchar2(100);
887
888 l_doc_string varchar2(200);
889 l_preparer_user_name varchar2(100);
890
891 BEGIN
892
893 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Get_Forward_mode: 01';
894 IF (g_po_wf_debug = 'Y') THEN
895 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
896 END IF;
897
898
899 -- Do nothing in cancel or timeout mode
900 --
901 if (funcmode <> wf_engine.eng_run) then
902
903 resultout := wf_engine.eng_null;
904 return;
905
906 end if;
907
908 -- Set the multi-org context
909 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
910 itemkey => itemkey,
911 aname => 'DOCUMENT_TYPE');
912 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
913 itemkey => itemkey,
914 aname => 'DOCUMENT_ID');
915
916 -- PO_REQAPPROVAL_INIT1.get_multiorg_context (l_document_type, l_document_id,x_orgid);
917
918 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
919 itemkey => itemkey,
920 aname => 'ORG_ID');
921
922 IF l_orgid is NOT NULL THEN
923
924 po_moac_utils_pvt.set_org_context(l_orgid); --<R12 MOAC>
925
926 END IF;
927
928
929 l_forward_mode := PO_REQAPPROVAL_FINDAPPRV1.GetForwardMode(itemtype, itemkey);
930
931 IF l_forward_mode = 'DIRECT' THEN
932
933 resultout := wf_engine.eng_completed || ':' || 'DIRECT';
934
935 ELSIF l_forward_mode = 'HIERARCHY' THEN
936
937 resultout := wf_engine.eng_completed || ':' || 'HIERARCHY';
938
939 END IF;
940
941 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Get_Forward_mode: 02';
942 IF (g_po_wf_debug = 'Y') THEN
943 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
944 END IF;
945
946
947 EXCEPTION
948
949 WHEN OTHERS THEN
950 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
951 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
952 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','Get_Forward_mode',x_progress);
953 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_FINDAPPRV1.GET_FORWARD_MODE');
954 raise;
955
956
957 END Get_Forward_mode;
958
959 --
960 procedure Use_Position_flag(itemtype in varchar2,
961 itemkey in varchar2,
962 actid in number,
963 funcmode in varchar2,
964 resultout out NOCOPY varchar2 ) is
965
966 l_document_type varchar2(25);
967 l_document_id number;
968 l_orgid number;
969 l_use_positions_flag varchar2(1);
970 x_progress varchar2(100);
971
972 l_doc_string varchar2(200);
973 l_preparer_user_name varchar2(100);
974
975 BEGIN
976
977 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Use_Position_flag: 01';
978 IF (g_po_wf_debug = 'Y') THEN
979 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
980 END IF;
981
982
983 -- Do nothing in cancel or timeout mode
984 --
985 if (funcmode <> wf_engine.eng_run) then
986
987 resultout := wf_engine.eng_null;
988 return;
989
990 end if;
991
992 -- Set the multi-org context
993 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
994 itemkey => itemkey,
995 aname => 'DOCUMENT_TYPE');
996 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
997 itemkey => itemkey,
998 aname => 'DOCUMENT_ID');
999
1000 -- PO_REQAPPROVAL_INIT1.get_multiorg_context (l_document_type, l_document_id,x_orgid);
1001 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1002 itemkey => itemkey,
1003 aname => 'ORG_ID');
1004
1005 IF l_orgid is NOT NULL THEN
1006
1007 po_moac_utils_pvt.set_org_context(l_orgid); --<R12 MOAC>
1008
1009 END IF;
1010
1011
1012 l_use_positions_flag := PO_REQAPPROVAL_FINDAPPRV1.UsePositionFlag;
1013
1014 IF l_use_positions_flag = 'Y' THEN
1015
1016 resultout := wf_engine.eng_completed || ':' || 'Y';
1017
1018 ELSIF l_use_positions_flag = 'N' THEN
1019
1020 resultout := wf_engine.eng_completed || ':' || 'N';
1021
1022 END IF;
1023
1024 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.Use_Position_flag: 02';
1025 IF (g_po_wf_debug = 'Y') THEN
1026 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1027 END IF;
1028
1029 EXCEPTION
1030
1031 WHEN OTHERS THEN
1032 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1033 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1034 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','Use_Position_flag',x_progress);
1035 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_FINDAPPRV1.USE_POSITION_FLAG');
1036 raise;
1037
1038
1039 END Use_Position_flag;
1040
1041 --
1042 -- GetMgr_hr_hier
1043 -- Get the requisition values on the doc header and assigns then to workflow attributes
1044 --
1045 procedure GetMgr_hr_hier(itemtype in varchar2,
1046 itemkey in varchar2,
1047 actid in number,
1048 funcmode in varchar2,
1049 resultout out NOCOPY varchar2 ) is
1050
1051 l_document_type varchar2(25);
1052 l_document_id number;
1053 l_orgid number;
1054 l_found_manager VARCHAR2(1);
1055 x_progress varchar2(100);
1056
1057 l_doc_string varchar2(200);
1058 l_preparer_user_name varchar2(100);
1059
1060 BEGIN
1061
1062 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.GetMgr_hr_hier: 01';
1063 IF (g_po_wf_debug = 'Y') THEN
1064 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1065 END IF;
1066
1067
1068 -- Do nothing in cancel or timeout mode
1069 --
1070 if (funcmode <> wf_engine.eng_run) then
1071
1072 resultout := wf_engine.eng_null;
1073 return;
1074
1075 end if;
1076
1077 -- Set the multi-org context
1078 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1079 itemkey => itemkey,
1080 aname => 'DOCUMENT_TYPE');
1081 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1082 itemkey => itemkey,
1083 aname => 'DOCUMENT_ID');
1084
1085 -- PO_REQAPPROVAL_INIT1.get_multiorg_context (l_document_type, l_document_id,x_orgid);
1086
1087 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1088 itemkey => itemkey,
1089 aname => 'ORG_ID');
1090
1091 IF l_orgid is NOT NULL THEN
1092
1093 po_moac_utils_pvt.set_org_context(l_orgid); --<R12 MOAC>
1094
1095 END IF;
1096
1097
1098
1099 l_found_manager := PO_REQAPPROVAL_FINDAPPRV1.GetMgrHRHier(itemtype, itemkey);
1100
1101 IF l_found_manager = 'Y' THEN
1102
1103 resultout := wf_engine.eng_completed || ':' || 'Y';
1104 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.GetMgr_hr_hier: RESULT=Y';
1105
1106 ELSIF l_found_manager = 'N' THEN
1107
1108 resultout := wf_engine.eng_completed || ':' || 'N';
1109 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.GetMgr_hr_hier: RESULT=N';
1110
1111 END IF;
1112
1113 IF (g_po_wf_debug = 'Y') THEN
1114 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1115 END IF;
1116
1117 EXCEPTION
1118
1119 WHEN OTHERS THEN
1120 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1121 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1122 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','GetMgr_hr_hier',x_progress);
1123 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_FINDAPPRV1.GETMGR_HR_HIER');
1124 raise;
1125
1126 END GetMgr_hr_hier;
1127
1128 --
1129
1130 --
1131 -- GetMgr_po_hier
1132 -- Get the requisition values on the doc header and assigns then to workflow attributes
1133 --
1134 procedure GetMgr_po_hier(itemtype in varchar2,
1135 itemkey in varchar2,
1136 actid in number,
1137 funcmode in varchar2,
1138 resultout out NOCOPY varchar2 ) is
1139
1140 l_document_type varchar2(25);
1141 l_document_id number;
1142 l_orgid number;
1143 l_found_manager VARCHAR2(1);
1144 x_progress varchar2(100);
1145
1146 l_doc_string varchar2(200);
1147 l_preparer_user_name varchar2(100);
1148
1149 BEGIN
1150
1151 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.GetMgr_po_hier: 01';
1152 IF (g_po_wf_debug = 'Y') THEN
1153 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1154 END IF;
1155
1156
1157 -- Do nothing in cancel or timeout mode
1158 --
1159 if (funcmode <> wf_engine.eng_run) then
1160
1161 resultout := wf_engine.eng_null;
1162 return;
1163
1164 end if;
1165
1166 -- Set the multi-org context
1167 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1168 itemkey => itemkey,
1169 aname => 'DOCUMENT_TYPE');
1170 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1171 itemkey => itemkey,
1172 aname => 'DOCUMENT_ID');
1173
1174 -- PO_REQAPPROVAL_INIT1.get_multiorg_context (l_document_type, l_document_id, x_orgid);
1175 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1176 itemkey => itemkey,
1177 aname => 'ORG_ID');
1178
1179 IF l_orgid is NOT NULL THEN
1180
1181 po_moac_utils_pvt.set_org_context(l_orgid); --<R12 MOAC>
1182
1183 END IF;
1184
1185
1186 l_found_manager := PO_REQAPPROVAL_FINDAPPRV1.GetMgrPOHier(itemtype, itemkey);
1187
1188 IF l_found_manager = 'Y' THEN
1189
1190 resultout := wf_engine.eng_completed || ':' || 'Y';
1191 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.GetMgr_po_hier: RESULT=Y';
1192
1193 ELSIF l_found_manager = 'N' THEN
1194
1195 resultout := wf_engine.eng_completed || ':' || 'N';
1196 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.GetMgr_po_hier: RESULT=N';
1197
1198 END IF;
1199
1200 IF (g_po_wf_debug = 'Y') THEN
1201 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1202 END IF;
1203
1204 EXCEPTION
1205
1206 WHEN OTHERS THEN
1207 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1208 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1209 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','GetMgr_po_hier',x_progress);
1210 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_FINDAPPRV1.GETMGR_PO_HIER');
1211 raise;
1212
1213
1214 END GetMgr_po_hier;
1215
1216 --
1217
1218 /*********************************************************************************
1219 ** The following are the APIs that support the workflow procedures.
1220 *********************************************************************************/
1221
1222
1223 FUNCTION IsForwardToProvided(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
1224
1225 l_forward_to_id NUMBER := NULL;
1226 l_forward_to_id_old NUMBER := NULL;
1227 l_forward_to_username_response varchar2(60):= NULL;
1228
1229 x_progress varchar2(100):='000';
1230 BEGIN
1231
1232 /* DEBUG: This procedure needs to get the employee_id of the username
1233 ** provided by the person responding to the notification.
1234 ** If we are unable to get an employee_id (for example, the
1235 ** user is only a Web user, not in Per_people_F table),
1236 ** then we should raise some kind of execption (e.g. send a notification
1237 ** to the system administrator????).
1238 ** If we get an employee_id, then we need to compare it to the old
1239 ** value in the forward_to_id, to check if the user provided a new
1240 ** forward_to.
1241 */
1242
1243 x_progress :='In IsForwardToProvided. x_progress= 001';
1244 IF (g_po_wf_debug = 'Y') THEN
1245 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1246 END IF;
1247
1248 /* Get_employee_id(), should set the item attribute FORWARD_TO_ID
1249 ** to the id of the username supplied as a forward_to in the notification
1250 ** (It gets the username from item_attribute FORWARD_TO_USERNAME, which is
1251 ** referenced in the message attribute).
1252 ** It should also return that id.
1253 */
1254
1255 l_forward_to_username_response := wf_engine.GetItemAttrText (itemtype => itemtype,
1256 itemkey => itemkey,
1257 aname => 'FORWARD_TO_USERNAME_RESPONSE');
1258
1259 /* If there is no Forward-to provided in the notification, then the forward_to_id
1260 ** comes from the current value of the FORWARD_TO_ID.
1261 ** Otherwise, we get it from the username supplied in the notification.
1262 */
1263 IF l_forward_to_username_response is NOT NULL THEN
1264
1265 x_progress :='In IsForwardToProvided. x_progress= 002';
1266 IF (g_po_wf_debug = 'Y') THEN
1267 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1268 END IF;
1269
1270
1271 /* Set the forward_to username so they would get the notification */
1272 /* DEBUG: Only set the FORWARD_TO_USERNAME, if Get_employee_id() returned
1273 ** a valid employee.
1274 */
1275 PO_REQAPPROVAL_INIT1.Get_employee_id(l_forward_to_username_response,
1276 l_forward_to_id);
1277
1278 IF l_forward_to_id is NOT NULL THEN
1279
1280 x_progress := '003';
1281 x_progress :='In IsForwardToProvided. x_progress= 003';
1282 IF (g_po_wf_debug = 'Y') THEN
1283 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1284 END IF;
1285
1286 wf_engine.SetItemAttrText ( itemtype => itemType,
1287 itemkey => itemkey,
1288 aname => 'FORWARD_TO_USERNAME',
1289 avalue => l_forward_to_username_response);
1290
1291 wf_engine.SetItemAttrNumber ( itemtype => itemType,
1292 itemkey => itemkey,
1293 aname => 'FORWARD_TO_ID',
1294 avalue => l_forward_to_id);
1295
1296 ELSE
1297
1298 x_progress :='In IsForwardToProvided. x_progress= 004';
1299 IF (g_po_wf_debug = 'Y') THEN
1300 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1301 END IF;
1302
1303 RETURN('N'); -- If no valid user provide, then return 'NO FORWARD-TO'
1304
1305 END IF;
1306
1307 ELSE
1308
1309 x_progress :='In IsForwardToProvided. x_progress= 005';
1310 IF (g_po_wf_debug = 'Y') THEN
1311 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1312 END IF;
1313
1314 l_forward_to_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1315 itemkey => itemkey,
1316 aname => 'FORWARD_TO_ID');
1317 END IF;
1318
1319 /* Reset the value of the forward_to RESPONSE attribute */
1320 wf_engine.SetItemAttrText ( itemtype => itemType,
1321 itemkey => itemkey,
1322 aname => 'FORWARD_TO_USERNAME_RESPONSE',
1323 avalue => NULL);
1324
1325 l_forward_to_id_old := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1326 itemkey => itemkey,
1327 aname => 'FORWARD_TO_ID_OLD');
1328
1329 IF ( NVL(l_forward_to_id,0) <> NVL(l_forward_to_id_old,0) ) THEN
1330
1331 -- x_progress:= '006';
1332
1333 /* Set the old value equal to the new value */
1334 wf_engine.SetItemAttrNumber ( itemtype => itemType,
1335 itemkey => itemkey,
1336 aname => 'FORWARD_TO_ID_OLD',
1337 avalue => l_forward_to_id);
1338
1339 RETURN('Y');
1340
1341 ELSE
1342
1343 x_progress :='In IsForwardToProvided. x_progress= 007';
1344 IF (g_po_wf_debug = 'Y') THEN
1345 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1346 END IF;
1347
1348 RETURN('N');
1349 END IF;
1350
1351 EXCEPTION
1352
1353 WHEN OTHERS THEN
1354 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','IsForwardToProvided',x_progress);
1355 raise;
1356
1357 END IsForwardToProvided;
1358
1359 --
1360 FUNCTION CheckForwardTo( p_username varchar2, x_user_id IN OUT NOCOPY number) RETURN VARCHAR2 is
1361
1362 /* Bug# 1646614: kagarwal
1363 ** Desc: We need to remove the check for orig_system_id from wf_users in
1364 ** function CheckForwardTo() in POXWPA3B.pls as it is not required. Also it will
1365 ** improve performance.
1366 */
1367
1368 /*
1369 Cursor C1(username varchar2) is
1370 Select ORIG_SYSTEM_ID
1371 from WF_USERS
1372 where name = username;
1373 */
1374
1375 /* Bug# 1134100: kagarwal
1376 ** Desc: If the forward to user was not an employee the
1377 ** Notification would get forwarded to the user.
1378 ** A check has been added that the user is also a
1379 ** valid employee.
1380 */
1381
1382 /* Bug# 1301432: kagarwal
1383 ** Desc: When validating the forward to username we need to verify that the
1384 ** user is a valid employee and also belongs to fnd_users
1385 **
1386 ** We need to change the Fix of Bug#1134100.
1387 ** The ORIG_SYSTEM_ID in wf_roles returns employee number for username
1388 ** associated with an employee and fnd_user.user_id for username not
1389 ** associated with an employee
1390 **
1391 ** We can do away with the check of wf_roles but it will help us
1392 ** identify if the users have setup issue. Also the check from
1393 ** PER_WORKFORCE_CURRENT_V makes sure that the employee is
1394 ** active on the current date.
1395 */
1396
1397 cursor C2(username varchar2) is
1398 SELECT HR.PERSON_ID
1399 FROM FND_USER FND, PO_WORKFORCE_CURRENT_X HR --<BUG 6615913>
1400 WHERE FND.USER_NAME = username
1401 AND FND.EMPLOYEE_ID = HR.PERSON_ID
1402 AND ROWNUM = 1;
1403
1404 -- check_emp_id number;
1405
1406 x_progress varchar2(3) := '000';
1407
1408 BEGIN
1409
1410 x_progress := '001';
1411
1412 OPEN C2(p_username);
1413
1414 FETCH C2 into x_user_id;
1415
1416 x_progress := '003';
1417
1418 IF C2%FOUND THEN
1419 x_progress := '004';
1420 CLOSE C2;
1421 RETURN('Y');
1422 ELSE
1423 x_progress := '005';
1424 CLOSE C2;
1425 RETURN('N');
1426 END IF;
1427
1428 x_progress := '007';
1429
1430 EXCEPTION
1431
1432 WHEN OTHERS THEN
1433 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','CheckForwardTo',x_progress);
1434 raise;
1435
1436 END CheckForwardTo;
1437
1438 --
1439 PROCEDURE GetApprovalPathId(itemtype VARCHAR2, itemkey VARCHAR2) is
1440
1441 l_approval_path_id NUMBER;
1442 l_document_type_code VARCHAR2(25);
1443 l_document_subtype VARCHAR2(25);
1444
1445 x_progress varchar2(200) := '000';
1446 BEGIN
1447
1448 x_progress := '001';
1449 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1450 itemkey => itemkey,
1451 aname => 'APPROVAL_PATH_ID');
1452
1453 /* If No Approval Path was specified by the user, then get the default */
1454
1455 IF l_approval_path_id is NULL THEN
1456
1457 l_document_type_code := wf_engine.GetItemAttrText (itemtype => itemtype,
1458 itemkey => itemkey,
1459 aname => 'DOCUMENT_TYPE');
1460
1461 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
1462 itemkey => itemkey,
1463 aname => 'DOCUMENT_SUBTYPE');
1464
1465 x_progress := '002';
1466
1467 Select default_approval_path_id into l_approval_path_id
1468 FROM po_document_types podt
1469 WHERE podt.document_type_code = l_document_type_code
1470 AND podt.document_subtype = l_document_subtype;
1471
1472 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1473 itemkey => itemkey,
1474 aname => 'APPROVAL_PATH_ID',
1475 avalue => l_approval_path_id);
1476
1477 END IF;
1478
1479 x_progress := 'Procedure GetApprovalPathId(). PATH_ID= ' || to_char(l_approval_path_id);
1480 IF (g_po_wf_debug = 'Y') THEN
1481 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1482 END IF;
1483
1484 EXCEPTION
1485
1486 WHEN OTHERS THEN
1487 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','GetApprovalPathId',x_progress);
1488 raise;
1489
1490
1491 END GetApprovalPathId;
1492
1493
1494 --
1495 FUNCTION GetForwardMode(itemtype varchar2, itemkey varchar2) RETURN VARCHAR2 is
1496
1497 l_forward_mode VARCHAR2(25);
1498 l_document_subtype VARCHAR2(25);
1499 l_document_type_code VARCHAR2(25);
1500
1501 x_progress varchar2(3) := '000';
1502 BEGIN
1503
1504 x_progress := '001';
1505 l_document_type_code := wf_engine.GetItemAttrText (itemtype => itemtype,
1506 itemkey => itemkey,
1507 aname => 'DOCUMENT_TYPE');
1508
1509 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
1510 itemkey => itemkey,
1511 aname => 'DOCUMENT_SUBTYPE');
1512
1513 SELECT FORWARDING_MODE_CODE into l_forward_mode
1514 from po_document_types
1515 where document_subtype = l_document_subtype
1516 and document_type_code = l_document_type_code;
1517
1518 RETURN(l_forward_mode);
1519
1520 x_progress := '002';
1521 EXCEPTION
1522
1523 WHEN OTHERS THEN
1524 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','GetForwardMode',x_progress);
1525 raise;
1526
1527 END GetForwardMode;
1528
1529 --
1530 FUNCTION UsePositionFlag RETURN VARCHAR2 is
1531
1532 l_use_positions_flag VARCHAR2(1);
1533
1534 x_progress varchar2(3) := '000';
1535 BEGIN
1536
1537 x_progress := '001';
1538 SELECT NVL(use_positions_flag, 'N') into l_use_positions_flag
1539 from financials_system_parameters;
1540
1541 RETURN(l_use_positions_flag);
1542
1543 x_progress := '002';
1544 EXCEPTION
1545
1546 WHEN OTHERS THEN
1547 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','UsePositionFlag',x_progress);
1548 raise;
1549
1550 END UsePositionFlag;
1551
1552 --
1553 FUNCTION GetMgrHRHier(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
1554
1555 /* Bug# 1169107: kagarwal
1556 ** Desc: Added the check that the supervisor is still
1557 ** active on the current date.
1558 **
1559 */
1560
1561 /* Bug# 1350792: kagarwal
1562 ** Desc: In GetMgrHRHier() function the check that the supervisor is still
1563 ** active for bug fix 1169107 should also include that the system_person_type
1564 ** is an 'EMP'.
1565 */
1566
1567 /* Bug#2278152: kagarwal
1568 ** Desc: When we retrieve the supervisor of an employee, we need
1569 ** to check for the person type of EMP_APL as well, in addition to EMP
1570 */
1571
1572 /* Bug# 2479883: kagarwal
1573 ** Desc: When we get the supervisor of an employee, we should be choosing
1574 ** the supervisor from the currently active primary assignment.
1575 **
1576 ** Added condition pera.person_id = p_employee_id to SQL in cursor C1, C2
1577 */
1578
1579 /* Bug# 5556434:
1580 ** Modified the cursors C1 to include the
1581 ** check to select the supervisor if the assignment type E.
1582 ** Also modified the SQLs slightly to ensure that no more inline
1583 ** SQLs to improve performance.
1584 ** Also added CWK assignment type
1585 */
1586
1587 CURSOR c1 (p_empid NUMBER, p_business_group_id NUMBER) IS
1588 SELECT pafe.supervisor_id
1589 FROM Per_All_Assignments_f pafe, -- <BUG 6615913>
1590 Per_All_People_f ppfs, -- <BUG 6615913>
1591 Per_All_Assignments_f pafs, -- <BUG 6615913>
1592 Per_Person_Types ppts
1593 WHERE pafe.business_group_id = p_business_group_id
1594 AND pafe.person_id = p_empid
1595 AND Trunc(SYSDATE) BETWEEN pafe.Effective_Start_Date
1596 AND pafe.Effective_End_Date
1597 AND pafe.Primary_Flag = 'Y'
1598 AND pafe.Assignment_Type IN ('E','C')
1599 AND ppfs.Person_Id = pafe.Supervisor_Id
1600 AND Trunc(SYSDATE) BETWEEN ppfs.Effective_Start_Date
1601 AND ppfs.Effective_End_Date
1602 AND pafs.Person_Id = ppfs.Person_Id
1603 AND Trunc(SYSDATE) BETWEEN pafs.Effective_Start_Date
1604 AND pafs.Effective_End_Date
1605 AND pafs.Primary_Flag = 'Y'
1606 AND pafs.Assignment_Type IN ('E','C')
1607 AND ppts.Person_Type_Id = ppfs.Person_Type_Id
1608 AND ppts.System_Person_Type IN ('EMP','EMP_APL','CWK'); --<R12 CWK Enhancemment>
1609
1610 /* Bug# 5556434:
1611 ** Modified the cursors C2 to include the
1612 ** check to select the supervisor if the assignment type E.
1613 ** Also modified the SQLs slightly to ensure that no more inline
1614 ** SQLs to improve performance.
1615 ** Also added CWK assignment type
1616 */
1617
1618 CURSOR C2 ( p_empid NUMBER ) IS
1619 SELECT Pafe.supervisor_id
1620 FROM Per_All_Assignments_f pafe, -- <BUG 6615913>
1621 Per_All_People_f ppfs, -- <BUG 6615913>
1622 Per_All_Assignments_f pafs, -- <BUG 6615913>
1623 Per_Person_Types ppts
1624 WHERE pafe.person_id = p_empid
1625 AND Trunc(SYSDATE) BETWEEN pafe.Effective_Start_Date
1626 AND pafe.Effective_End_Date
1627 AND pafe.Primary_Flag = 'Y'
1628 AND pafe.Assignment_Type IN ('E','C')
1629 AND ppfs.Person_Id = pafe.Supervisor_Id
1630 AND Trunc(SYSDATE) BETWEEN ppfs.Effective_Start_Date
1631 AND ppfs.Effective_End_Date
1632 AND Pafs.Person_Id = ppfs.Person_Id
1633 AND Trunc(SYSDATE) BETWEEN pafs.Effective_Start_Date
1634 AND pafs.Effective_End_Date
1635 AND pafs.Primary_Flag = 'Y'
1636 AND pafs.Assignment_Type IN ('E','C')
1637 AND ppts.Person_Type_Id = ppfs.Person_Type_Id
1638 AND ppts.System_Person_Type IN ('EMP','EMP_APL','CWK'); --<R12 CWK Enhancemment>
1639
1640 l_superior_id number;
1641 l_empid number;
1642
1643 x_username varchar2(100);
1644 x_user_display_name varchar2(240);
1645
1646 l_forward_from_id number;
1647 l_forward_from_username varchar2(100);
1648 l_forward_from_disp_name varchar2(100);
1649
1650 l_business_group_id NUMBER;
1651
1652 x_progress varchar2(200) := NULL;
1653 x_hr_profile varchar2(1) := hr_general.get_xbg_profile;
1654
1655 BEGIN
1656
1657 /* Get the employee whos manager we need to find. This is always
1658 ** kept in APPROVER_EMPID.
1659 */
1660
1661 l_empid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1662 itemkey => itemkey,
1663 aname => 'APPROVER_EMPID');
1664 SELECT business_group_id
1665 INTO l_business_group_id
1666 FROM FINANCIALS_SYSTEM_PARAMETERS;
1667
1668 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.GetMgrHRHier Business_group_id= ' ||
1669 to_char(l_business_group_id);
1670
1671 if x_hr_profile = 'Y' then
1672 OPEN C2(l_empid);
1673 FETCH C2 into l_superior_id;
1674 else
1675 OPEN C1(l_empid, l_business_group_id);
1676 FETCH C1 into l_superior_id;
1677 end if;
1678
1679 x_progress := x_progress || ' employee_id=' || to_char(l_empid) ||
1680 ' supervisor_id=' || to_char(l_superior_id);
1681
1682 IF (g_po_wf_debug = 'Y') THEN
1683 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1684 END IF;
1685
1686 IF l_superior_id IS NOT NULL THEN
1687
1688 /* Bug #1278794: kagarwal
1689 ** Desc: When a Doc is submitted for approval and the approver does not have
1690 ** the authority to approve, the approval workflow looks for the superior
1691 ** of the approver to forward the document.
1692 **
1693 ** If the superior does not have a valid username, we will return the
1694 ** document to the preparer with Message 'No Approver found'.
1695 **
1696 ** Return 'N', if PO_REQAPPROVAL_INIT1.get_user_name returns
1697 ** x_username as NULL
1698 */
1699
1700 PO_REQAPPROVAL_INIT1.get_user_name(l_superior_id, x_username,
1701 x_user_display_name);
1702
1703 IF x_username IS NULL THEN
1704 if x_hr_profile = 'Y' then
1705 close C2;
1706 else
1707 close C1;
1708 end if;
1709
1710 RETURN('N');
1711
1712 END IF;
1713
1714 /* If we found an approver, then we need a forward-from.
1715 ** If the Forward_from_id is NULL, then we need to set the forward_from_id
1716 ** to be that of the previous approver. This takes care of the following
1717 ** scenario:
1718 ** an approver gets a notification, they respond with APPROVE action.
1719 ** Since the approver did not provide a forward-to, we null out
1720 ** the forward-from and the forward-to (see activity
1721 ** "Set Forward-to/from Approve"). The flow then moves to activity
1722 ** "Verify Authority". If the user does not have authority, then flow
1723 ** moves to "Find Approver". Then we get here.
1724 ** At this point, we find an approver, so we need to set the forward-from
1725 ** to be the last person that took the APPROVE action.
1726 */
1727
1728 l_forward_from_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1729 itemkey => itemkey,
1730 aname => 'FORWARD_FROM_ID');
1731
1732 IF l_forward_from_id is NULL THEN
1733
1734 /* Get the previous approver username and display name */
1735 l_forward_from_username := wf_engine.GetItemAttrText ( itemtype => itemType,
1736 itemkey => itemkey,
1737 aname => 'APPROVER_USER_NAME');
1738
1739 l_forward_from_disp_name := wf_engine.GetItemAttrText ( itemtype => itemType,
1740 itemkey => itemkey,
1741 aname => 'APPROVER_DISPLAY_NAME');
1742
1743 /* Set the forward-from username and display name to that of the previous
1744 ** approver.
1745 */
1746 wf_engine.SetItemAttrText ( itemtype => itemType,
1747 itemkey => itemkey,
1748 aname => 'FORWARD_FROM_ID' ,
1749 avalue => l_empid);
1750
1751 wf_engine.SetItemAttrText ( itemtype => itemType,
1752 itemkey => itemkey,
1753 aname => 'FORWARD_FROM_USER_NAME' ,
1754 avalue => l_forward_from_username);
1755
1756 wf_engine.SetItemAttrText ( itemtype => itemType,
1757 itemkey => itemkey,
1758 aname => 'FORWARD_FROM_DISP_NAME' ,
1759 avalue => l_forward_from_disp_name);
1760
1761 END IF;
1762
1763 /* Set the approver id to that of the superior. We do this in case this manager
1764 ** does not have the authority to approve. Therefore, we would loop back and call
1765 ** this routine again. But this time we want to find the manager of this manager
1766 ** not of the original submitter (Also, note that When we first come into the
1767 ** workflow, we set the APPROVER_EMPID to the preparer id).
1768 **
1769 ** NOTE: Activity "Verify Authority" always uses the APPROVER_EMPID attribute
1770 ** as the approver.
1771 */
1772 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1773 itemkey => itemkey,
1774 aname => 'APPROVER_EMPID',
1775 avalue => l_superior_id);
1776
1777 /* Set the forward-to ID. This is the approver that will get the notification */
1778 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1779 itemkey => itemkey,
1780 aname => 'FORWARD_TO_ID',
1781 avalue => l_superior_id);
1782
1783 /* Set the value of FORWARD_TO_ID_OLD. This is used to determine if the responder
1784 ** to the notification entered a different USERNAME to forward the doc to.
1785 */
1786 wf_engine.SetItemAttrNumber ( itemtype => itemType,
1787 itemkey => itemkey,
1788 aname => 'FORWARD_TO_ID_OLD',
1789 avalue => l_superior_id);
1790
1791 /* Get the username of Forward-to employee. We need to assign it to the
1792 ** performer of the notification */
1793 /* Commented out the call since we have already got the username */
1794 /*PO_REQAPPROVAL_INIT1.get_user_name(l_superior_id, x_username,
1795 x_user_display_name);*/
1796
1797 wf_engine.SetItemAttrText ( itemtype => itemType,
1798 itemkey => itemkey,
1799 aname => 'FORWARD_TO_USERNAME' ,
1800 avalue => x_username);
1801
1802 wf_engine.SetItemAttrText ( itemtype => itemType,
1803 itemkey => itemkey,
1804 aname => 'FORWARD_TO_DISPLAY_NAME' ,
1805 avalue => x_user_display_name);
1806
1807 wf_engine.SetItemAttrText ( itemtype => itemType,
1808 itemkey => itemkey,
1809 aname => 'APPROVER_USER_NAME' ,
1810 avalue => x_username);
1811
1812 wf_engine.SetItemAttrText ( itemtype => itemType,
1813 itemkey => itemkey,
1814 aname => 'APPROVER_DISPLAY_NAME' ,
1815 avalue => x_user_display_name);
1816
1817 if x_hr_profile = 'Y' then
1818 close C2;
1819 else
1820 close C1;
1821 end if;
1822 RETURN('Y');
1823
1824 ELSE
1825
1826 if x_hr_profile = 'Y' then
1827 close C2;
1828 else
1829 close C1;
1830 end if;
1831
1832 RETURN('N');
1833
1834 END IF;
1835
1836
1837 EXCEPTION
1838
1839 WHEN OTHERS THEN
1840 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','GetMgrHRHier',x_progress);
1841 raise;
1842
1843 END GetMgrHRHier;
1844
1845 --
1846 FUNCTION GetMgrPOHier(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
1847
1848 -- Bug 762194: The superior_level needs to be > 0 instead of = 1.
1849
1850 /* Bug 2437175
1851 Added the LEADING(POEH) hint to get better execution plan */
1852
1853 Cursor C1(p_empid number, p_approval_path_id number) is
1854 SELECT /*+ LEADING(POEH) */POEH.superior_id, poeh.superior_level, HREC.full_name
1855 FROM PO_EMPLOYEES_CURRENT_X HREC, -- <BUG 6615913>
1856 PO_EMPLOYEE_HIERARCHIES POEH
1857 WHERE POEH.position_structure_id = p_approval_path_id
1858 AND POEH.employee_id = p_empid
1859 AND HREC.employee_id = POEH.superior_id
1860 AND POEH.superior_level > 0
1861 UNION ALL
1862 SELECT /*+ LEADING(POEH) */ poeh.superior_id, poeh.superior_level, cwk.full_name
1863 FROM PO_WORKFORCE_CURRENT_X cwk, -- <BUG 6615913>
1864 po_employee_hierarchies poeh
1865 WHERE poeh.position_structure_id = p_approval_path_id
1866 AND poeh.employee_id = p_empid
1867 AND cwk.person_id = poeh.superior_id
1868 AND poeh.superior_level > 0
1869 AND nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y'
1870 ORDER BY superior_level, full_name;
1871
1872 l_superior_id NUMBER := NULL;
1873 l_superior_level NUMBER := NULL;
1874 l_full_name VARCHAR2(240) := '000';
1875
1876 l_empid number;
1877 l_approval_path_id number;
1878
1879 x_username varchar2(100);
1880 x_user_display_name varchar2(240);
1881
1882 l_forward_from_id number;
1883 l_forward_from_username varchar2(100);
1884 l_forward_from_disp_name varchar2(100);
1885
1886 x_progress varchar2(200) := NULL;
1887 BEGIN
1888
1889
1890 l_empid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1891 itemkey => itemkey,
1892 aname => 'APPROVER_EMPID');
1893
1894 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1895 itemkey => itemkey,
1896 aname => 'APPROVAL_PATH_ID');
1897
1898 x_progress := '001';
1899
1900 open C1(l_empid, l_approval_path_id);
1901 fetch C1 into l_superior_id, l_superior_level, l_full_name;
1902
1903 x_progress := 'PO_REQAPPROVAL_FINDAPPRV1.GetMgrPOHier: approval_path_id= ' ||
1904 to_char(l_approval_path_id) || ' employee_id=' ||
1905 to_char(l_empid) || ' supervisor_id=' || to_char(l_superior_id);
1906 IF (g_po_wf_debug = 'Y') THEN
1907 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1908 END IF;
1909
1910
1911 IF C1%FOUND THEN
1912
1913 /* Bug #1278794: kagarwal
1914 ** Desc: When a Doc is submitted for approval and the approver does not have
1915 ** the authority to approve, the approval workflow looks for the superior
1916 ** of the approver to forward the document.
1917 **
1918 ** If the superior does not have a valid username, we will return the
1919 ** document to the preparer with Message 'No Approver found'.
1920 **
1921 ** Return 'N', if PO_REQAPPROVAL_INIT1.get_user_name returns
1922 ** x_username as NULL
1923 */
1924
1925 PO_REQAPPROVAL_INIT1.get_user_name(l_superior_id, x_username,
1926 x_user_display_name);
1927
1928 IF x_username IS NULL THEN
1929 close C1;
1930 RETURN('N');
1931
1932 END IF;
1933
1934 /* If we found an approver, then we need a forward-from.
1935 ** If the Forward_from_id is NULL, then we need to set the forward_from_id
1936 ** to be that of the previous approver. This takes care of the following
1937 ** scenario:
1938 ** an approver gets a notification, they respond with APPROVE action.
1939 ** Since the approver did not provide a forward-to, we null out
1940 ** the forward-from and the forward-to (see activity
1941 ** "Set Forward-to/from Approve"). The flow then moves to activity
1942 ** "Verify Authority". If the user does not have authority, then flow
1943 ** moves to "Find Approver". Then we get here.
1944 ** At this point, we find an approver, so we need to set the forward-from
1945 ** to be the last person that took the APPROVE action.
1946 */
1947
1948 l_forward_from_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1949 itemkey => itemkey,
1950 aname => 'FORWARD_FROM_ID');
1951
1952 IF l_forward_from_id is NULL THEN
1953
1954 /* Get the previous approver username and display name */
1955 l_forward_from_username := wf_engine.GetItemAttrText ( itemtype => itemType,
1956 itemkey => itemkey,
1957 aname => 'APPROVER_USER_NAME');
1958
1959 l_forward_from_disp_name := wf_engine.GetItemAttrText ( itemtype => itemType,
1960 itemkey => itemkey,
1961 aname => 'APPROVER_DISPLAY_NAME');
1962
1963 /* Set the forward-from username and display name to that of the previous
1964 ** approver.
1965 */
1966 wf_engine.SetItemAttrText ( itemtype => itemType,
1967 itemkey => itemkey,
1968 aname => 'FORWARD_FROM_ID' ,
1969 avalue => l_empid);
1970
1971 wf_engine.SetItemAttrText ( itemtype => itemType,
1972 itemkey => itemkey,
1973 aname => 'FORWARD_FROM_USER_NAME' ,
1974 avalue => l_forward_from_username);
1975
1976 wf_engine.SetItemAttrText ( itemtype => itemType,
1977 itemkey => itemkey,
1978 aname => 'FORWARD_FROM_DISP_NAME' ,
1979 avalue => l_forward_from_disp_name);
1980
1981 END IF;
1982
1983 /* Set the employee id to the manager. The reason, we do this is that this manager
1984 ** may not have the authority to approve. Therefore, we would loop back and call
1985 ** this routine again. But this time we want to find the manager of this manager
1986 ** not of the original submitter (Also, note that When we first come into the
1987 ** workflow, we set the APPROVER_EMPID to the submitter id).
1988 **
1989 ** NOTE: Activity "Verify Authority" always uses the APPROVER_EMPID attribute
1990 ** as the approver.
1991 */
1992 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1993 itemkey => itemkey,
1994 aname => 'APPROVER_EMPID',
1995 avalue => l_superior_id);
1996
1997 /* Set the forward-to ID. This is the approver that will get the notification */
1998 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1999 itemkey => itemkey,
2000 aname => 'FORWARD_TO_ID',
2001 avalue => l_superior_id);
2002
2003 /* Set the value of FORWARD_TO_ID_OLD. This is used to determine if the responder
2004 ** to the notification entered a different USERNAME to forward the doc to.
2005 */
2006 wf_engine.SetItemAttrNumber ( itemtype => itemType,
2007 itemkey => itemkey,
2008 aname => 'FORWARD_TO_ID_OLD',
2009 avalue => l_superior_id);
2010
2011 /* Get the username of Forward-to employee. We need to assign it to the
2012 ** performer of the notification */
2013 /*PO_REQAPPROVAL_INIT1.get_user_name(l_superior_id, x_username,
2014 x_user_display_name);*/
2015
2016 wf_engine.SetItemAttrText ( itemtype => itemType,
2017 itemkey => itemkey,
2018 aname => 'FORWARD_TO_USERNAME' ,
2019 avalue => x_username);
2020
2021 wf_engine.SetItemAttrText ( itemtype => itemType,
2022 itemkey => itemkey,
2023 aname => 'FORWARD_TO_DISPLAY_NAME' ,
2024 avalue => x_user_display_name);
2025
2026 wf_engine.SetItemAttrText ( itemtype => itemType,
2027 itemkey => itemkey,
2028 aname => 'APPROVER_USER_NAME' ,
2029 avalue => x_username);
2030
2031 wf_engine.SetItemAttrText ( itemtype => itemType,
2032 itemkey => itemkey,
2033 aname => 'APPROVER_DISPLAY_NAME' ,
2034 avalue => x_user_display_name);
2035
2036 close C1;
2037 RETURN('Y');
2038
2039 ELSE
2040
2041 close C1;
2042 RETURN('N');
2043
2044 END IF;
2045
2046
2047 EXCEPTION
2048
2049 WHEN OTHERS THEN
2050 wf_core.context('PO_REQAPPROVAL_FINDAPPRV1','GetMgrPOHier',x_progress);
2051 raise;
2052
2053 END GetMgrPOHier;
2054
2055 --
2056
2057 /* Bug# 1496490
2058 ** New Procedure to check the owner can approve flag value
2059 */
2060
2061 PROCEDURE CheckOwnerCanApprove (itemtype in VARCHAR2, itemkey in VARCHAR2,
2062 CanOwnerApprove out NOCOPY VARCHAR2) is
2063
2064 Cursor C1(p_document_type_code VARCHAR2, p_document_subtype VARCHAR2) is
2065 select NVL(can_preparer_approve_flag,'N')
2066 from po_document_types
2067 where document_type_code = p_document_type_code
2068 and document_subtype = p_document_subtype;
2069
2070 l_document_type_code VARCHAR2(25);
2071 l_document_subtype VARCHAR2(25);
2072 x_progress varchar2(3):= '000';
2073
2074 BEGIN
2075 x_progress := '001';
2076 l_document_type_code := wf_engine.GetItemAttrText (itemtype => itemtype,
2077 itemkey => itemkey,
2078 aname => 'DOCUMENT_TYPE');
2079
2080 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2081 itemkey => itemkey,
2082 aname => 'DOCUMENT_SUBTYPE');
2083
2084 open C1(l_document_type_code, l_document_subtype);
2085 Fetch C1 into CanOwnerApprove;
2086 close C1;
2087
2088 EXCEPTION
2089 WHEN OTHERS THEN
2090 wf_core.context('PO_REQAPPROVAL_INIT1','CheckOwnerCanApprove',x_progress);
2091 raise;
2092 END CheckOwnerCanApprove;
2093
2094 --
2095
2096 end PO_REQAPPROVAL_FINDAPPRV1;
[Home] [Help]