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