[Home] [Help]
PACKAGE BODY: APPS.PO_APPROVAL_LIST_WF1S
Source
1 PACKAGE BODY PO_APPROVAL_LIST_WF1S AS
2 /* $Header: POXWAL1B.pls 120.2.12000000.2 2007/05/04 00:07:52 lswamy 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 | FILENAME
9 | POXWAL1B.sql
10 |
11 | DESCRIPTION
12 | PL/SQL package: PO_APPROVAL_LIST_WF1S
13 |
14 | NOTES
15 | Created 10/04/98 ecso
16 *=====================================================================*/
17 -- Local procedure
18 -- set context for calls to doc manager
19 --
20 procedure set_doc_mgr_context(itemtype VARCHAR2, itemkey VARCHAR2);
21 PROCEDURE set_doc_mgr_err(itemtype VARCHAR2, itemkey VARCHAR2,
22 p_error_stack PO_APPROVALLIST_S1.ErrorStackType,
23 p_return_code Number);
24
25 procedure Is_Document_Manager_Error_1_2(itemtype in varchar2,
26 itemkey in varchar2,
27 actid in number,
28 funcmode in varchar2,
29 resultout out NOCOPY varchar2) IS
30 l_progress VARCHAR2(100) := '000';
31 l_error_number NUMBER;
32
33 BEGIN
34
35 IF (funcmode='RUN') THEN
36
37 l_progress := 'Is_Document_Manager_Error_1_2: 001';
38 IF (g_po_wf_debug = 'Y') THEN
39 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
40 END IF;
41
42 l_error_number:=
43 wf_engine.GetItemAttrNumber ( itemtype => itemType,
44 itemkey => itemkey,
45 aname => 'DOC_MGR_ERROR_NUM');
46
47 l_progress := 'Is_Document_Manager_Error_1_2: 002 - '||
48 to_char(l_error_number);
49 IF (g_po_wf_debug = 'Y') THEN
50 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
51 END IF;
52
53 IF (l_error_number = 1 or l_error_number = 2) THEN
54 resultout:='COMPLETE:'||'Y';
55 return;
56
57 ELSE
58 resultout:='COMPLETE:'||'N';
59 return;
60
61 END IF;
62
63 END IF; --run mode
64
65 EXCEPTION
66 WHEN OTHERS THEN
67 WF_CORE.context('PO_APPROVAL_LIST_WF1S' , 'Is_Document_Manager_Error_1_2', itemtype, itemkey, l_progress);
68 resultout:='COMPLETE:'||'N';
69
70 END Is_Document_Manager_Error_1_2;
71
72
73 -- Public procedures
74 --
75 -- Does_Approval_list_Exist
76 -- Check if there exists an approval list
77 -- for a requisition
78 --
79 procedure Does_Approval_List_Exist( itemtype in varchar2,
80 itemkey in varchar2,
81 actid in number,
82 funcmode in varchar2,
83 resultout out NOCOPY varchar2) IS
84 l_progress VARCHAR2(100) := '000';
85 l_return_code NUMBER;
86 l_approval_list_header_id NUMBER;
87
88 l_doc_string varchar2(200);
89 l_preparer_user_name varchar2(100);
90
91 l_org_id number;
92
93 BEGIN
94
95 IF (funcmode='RUN') THEN
96
97 l_progress := 'Does_Approval_List_Exist: 001';
98 IF (g_po_wf_debug = 'Y') THEN
99 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
100 END IF;
101
102 l_approval_list_header_id:=
103 wf_engine.GetItemAttrNumber ( itemtype => itemType,
104 itemkey => itemkey,
105 aname => 'APPROVAL_LIST_HEADER_ID');
106
107 l_progress := 'Does_Approval_List_Exist: 002 - '||
108 'l_approval_list_header_id: '||
109 to_char(l_approval_list_header_id);
110 IF (g_po_wf_debug = 'Y') THEN
111 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
112 END IF;
113
114 IF l_approval_list_header_id IS NOT NULL THEN
115 resultout:='COMPLETE:'||'Y';
116 return;
117
118 ELSE
119 resultout:='COMPLETE:'||'N';
120 return;
121
122 END IF;
123
124 END IF; --run mode
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
129 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
130 WF_CORE.context('PO_APPROVAL_LIST_WF1S' , 'Does_Approval_List_Exist', itemtype, itemkey, l_progress);
131 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.DOES_APPROVAL_LIST_EXIST');
132 RAISE;
133
134 END Does_Approval_List_Exist;
135
136 -- Find_Approval_List
137 -- 1. search for an approval list created by preparer
138 -- through web requisition
139 -- 2. if approval list is found,
140 -- record the approval list id on workflow attribute and
141 -- mark approval list with workflow itemtype, itemkey
142 -- by calling update_approval_list_itemkey API
143 --
144 procedure Find_Approval_List ( itemtype in varchar2,
145 itemkey in varchar2,
146 actid in number,
147 funcmode in varchar2,
148 resultout out NOCOPY varchar2) IS
149
150 l_progress VARCHAR2(100) := '000';
151 l_document_id NUMBER;
152 l_document_type VARCHAR2(25):='';
153 l_document_subtype VARCHAR2(25):='';
154 l_return_code NUMBER;
155 l_approval_list_header_id NUMBER;
156 E_FAILURE EXCEPTION;
157
158 l_doc_string varchar2(200);
159 l_preparer_user_name varchar2(100);
160
161 l_org_id number;
162
163 BEGIN
164
165 IF (funcmode='RUN') THEN
166 -- Context Setting revamp
167 --set_doc_mgr_context(itemtype, itemkey);
168
169 l_progress := 'Find_Approval_List: 001';
170 IF (g_po_wf_debug = 'Y') THEN
171 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
172 END IF;
173
174 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
175 itemkey => itemkey,
176 aname => 'DOCUMENT_ID');
177
178 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
179 itemkey => itemkey,
180 aname => 'DOCUMENT_TYPE');
181
182 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
183 itemkey => itemkey,
184 aname => 'DOCUMENT_SUBTYPE');
185
186 -- Set the multi-org context
187
188 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
189 itemkey => itemkey,
190 aname => 'ORG_ID');
191
192 IF l_org_id is NOT NULL THEN
193
194 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
195
196 END IF;
197
198 l_progress := 'Find_Approval_List: 002-'||to_char(l_document_id)||'-'||
199 l_document_type||'-'||l_document_subtype;
200 IF (g_po_wf_debug = 'Y') THEN
201 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
202 END IF;
203
204 /* Pass null for itemtype and itemkey to find
205 ** new approval list.
206 */
207 PO_APPROVALLIST_S1.does_approval_list_exist(
208 p_document_id=>l_document_id,
209 p_document_type=>l_document_type,
210 p_document_subtype=>l_document_subtype,
211 p_itemtype=>NULL,
212 p_itemkey=>NULL,
213 p_return_code=>l_return_code,
214 p_approval_list_header_id=>l_approval_list_header_id);
215
216 l_progress := 'Find_Approval_List: 003- does_approval_list_exist - '||
217 to_char(l_return_code)||
218 ', l_approval_list_header_id: '||
219 to_char(l_approval_list_header_id);
220 IF (g_po_wf_debug = 'Y') THEN
221 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
222 END IF;
223
224 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
225 wf_engine.SetItemAttrNumber ( itemtype => itemType,
226 itemkey => itemkey,
227 aname => 'APPROVAL_LIST_HEADER_ID',
228 avalue => l_approval_list_header_id);
229
230 l_progress := 'Find_Approval_List: 004';
231 IF (g_po_wf_debug = 'Y') THEN
232 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
233 END IF;
234
235 PO_APPROVALLIST_S1.update_approval_list_itemkey(
236 p_approval_list_header_id=>l_approval_list_header_id,
237 p_itemtype=>itemtype,
238 p_itemkey=>itemkey,
239 p_return_code=>l_return_code);
240
241 l_progress := 'Find_Approval_List: 005- update_approval_list_itemkey - '||
242 to_char(l_return_code);
243 IF (g_po_wf_debug = 'Y') THEN
244 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
245 END IF;
246
247 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
248
249 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
250 return;
251 END IF;
252
253 END IF;
254
255 RAISE E_FAILURE;
256
257
258 END IF; -- run mode
259
260 EXCEPTION
261
262 WHEN E_FAILURE THEN
263 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
264 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
265 wf_core.context('PO_APPROVAL_LIST_WF1S',
266 'Find_Approval_list E_FAILURE',
267 l_progress,l_return_code,sqlerrm);
268 -- wf_core.raise('Find_Approval_list E_FAILURE' || l_progress||sqlerrm);
269 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.FIND_APPROVAL_LIST');
270
271 RAISE;
272
273 WHEN OTHERS THEN
274 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
275 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
276 wf_core.context('PO_APPROVAL_LIST_WF1S',
277 'Find_Approval_list',l_progress,l_return_code,sqlerrm);
278
279 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.FIND_APPROVAL_LIST');
280
281 RAISE;
282
283
284 END Find_Approval_List;
285
286 -- Build_Default_Approval_List
287 -- Build default approval list
288 --
289 procedure Build_Default_Approval_list(itemtype in varchar2,
290 itemkey in varchar2,
291 actid in number,
292 funcmode in varchar2,
293 resultout out NOCOPY varchar2) IS
294 l_progress VARCHAR2(300) := '000';
295 l_preparer_id NUMBER;
296 l_first_approver_id NUMBER;
297 l_approval_path_id NUMBER;
298 l_document_id NUMBER;
299 l_document_type VARCHAR2(25);
300 l_document_subtype VARCHAR2(25);
301 l_employee_id NUMBER;
302 l_return_code NUMBER;
303 l_error_stack PO_APPROVALLIST_S1.ErrorStackType;
304 l_approval_list PO_APPROVALLIST_S1.ApprovalListType;
305 l_approval_list_header_id NUMBER:=null;
306 E_APPROVAL_LIST_BUILD_FAIL EXCEPTION;
307 E_APPROVAL_LIST_SAVE_FAIL EXCEPTION;
308
309 l_doc_string varchar2(200);
310 l_preparer_user_name varchar2(100);
311
312 l_org_id number;
313 doc_manager_exception exception;
314
315 l_approver_id NUMBER := null;
316
317 BEGIN
318
319 IF (funcmode='RUN') THEN
320
321 l_progress := 'Build_Default_Approval_list: 001';
322 IF (g_po_wf_debug = 'Y') THEN
323 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
324 END IF;
325
326 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
327 itemkey => itemkey,
328 aname => 'DOCUMENT_ID');
329
330 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
331 itemkey => itemkey,
332 aname => 'DOCUMENT_TYPE');
333
334 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
335 itemkey => itemkey,
336 aname => 'DOCUMENT_SUBTYPE');
337
338 l_progress := 'Build_Default_Approval_list: 002-'||
339 to_char(l_document_id)||'-'||
340 l_document_type||'-'||l_document_subtype;
341 IF (g_po_wf_debug = 'Y') THEN
342 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
343 END IF;
344
345 l_preparer_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
346 itemkey => itemkey,
347 aname => 'PREPARER_ID');
348
349 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
350 itemkey => itemkey,
351 aname => 'APPROVAL_PATH_ID');
352
353 l_first_approver_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
354 itemkey => itemkey,
355 aname => 'FORWARD_TO_ID');
356
357 --Bug 3246530. The approval Authority should be verified for Approver not Preparer.
358 l_approver_id := po_wf_util_pkg.GetItemAttrNumber ( ItemType => itemtype,
359 ItemKey => itemkey,
360 aname => 'APPROVER_EMPID');
361 -- Set the multi-org context
362
363 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
364 itemkey => itemkey,
365 aname => 'ORG_ID');
366
367 IF l_org_id is NOT NULL THEN
368
369 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
370
371 END IF;
372
373 l_progress := 'Build_Default_Approval_list: 003-'||
374 to_char(l_document_id)||'-'||
375 to_char(l_preparer_id)||'-'||
376 to_char(l_approval_path_id)||'-'||
377 to_char(l_first_approver_id);
378 IF (g_po_wf_debug = 'Y') THEN
379 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
380 END IF;
381
382 PO_APPROVALLIST_S1.get_default_approval_list(
383 p_first_approver_id=>l_first_approver_id,
384 p_approval_path_id=>l_approval_path_id,
385 p_document_id=>l_document_id,
386 p_document_type=>l_document_type,
387 p_document_subtype=>l_document_subtype,
388 p_return_code=>l_return_code,
389 p_error_stack=>l_error_stack,
390 p_approval_list=>l_approval_list,
391 p_approver_id=>l_approver_id); -- Bug 3246530
392
393 l_progress := 'Build_Default_Approval_list: 004-get_default_approval_list-'||
394 to_char(l_return_code);
395 IF (g_po_wf_debug = 'Y') THEN
396 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
397 END IF;
398
399 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
400
401 l_progress := 'Build_Default_Approval_list: 006-print_approval_list';
402 IF (g_po_wf_debug = 'Y') THEN
403 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
404 END IF;
405
406 PO_APPROVALLIST_S1.print_approval_list(l_approval_list);
407 PO_APPROVALLIST_S1.save_approval_list(p_document_id=>l_document_id,
408 p_document_type=>l_document_type,
409 p_document_subtype=>l_document_subtype,
410 p_approval_list_header_id=>l_approval_list_header_id,
411 p_first_approver_id=>l_first_approver_id,
412 p_approval_path_id=>l_approval_path_id,
413 p_approval_list=>l_approval_list,
414 p_last_update_date=>null,
415 p_return_code=>l_return_code,
416 p_error_stack=>l_error_stack);
417
418 l_progress := 'Build_Default_Approval_list: 008-save_approval_list-'||
419 to_char(l_return_code);
420 IF (g_po_wf_debug = 'Y') THEN
421 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
422 END IF;
423
424 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
425
426 l_progress := 'Build_Default_Approval_list: 009-'||
427 'l_approval_list_header_id: '||
428 to_char(l_approval_list_header_id);
429 IF (g_po_wf_debug = 'Y') THEN
430 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
431 END IF;
432
433 PO_APPROVALLIST_S1.update_approval_list_itemkey(
434 p_approval_list_header_id=>l_approval_list_header_id,
435 p_itemtype=>itemtype,
436 p_itemkey=>itemkey,
437 p_return_code=>l_return_code);
438
439 l_progress := 'Build_Default_Approval_list: 010 '||
440 '- update_approval_list_itemkey-'||
441 to_char(l_return_code);
442 IF (g_po_wf_debug = 'Y') THEN
443 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
444 END IF;
445
446 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
447 wf_engine.SetItemAttrNumber(itemtype => itemType,
448 itemkey => itemkey,
449 aname => 'APPROVAL_LIST_HEADER_ID',
450 avalue => l_approval_list_header_id);
451 ELSE
452 raise E_APPROVAL_LIST_SAVE_FAIL;
453 END IF;
454 ELSE
455 raise E_APPROVAL_LIST_SAVE_FAIL;
456 END IF;
457
458 l_progress := 'Build_Default_Approval_list: 015';
459 IF (g_po_wf_debug = 'Y') THEN
460 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
461 END IF;
462
463 ELSE
464
465 l_progress := 'Build_Default_Approval_list: 020 ' ||to_char(l_return_code);
466 IF (g_po_wf_debug = 'Y') THEN
467 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
468 END IF;
469
470
471 IF l_return_code in
472 (PO_APPROVALLIST_S1.E_NO_SUPERVISOR_FOUND,
473 PO_APPROVALLIST_S1.E_NO_ONE_HAS_AUTHORITY) THEN
474
475 l_progress := 'Build_Default_Approval_list: 021'||
476 '- E_NO_SUPERVISOR_FOUND OR E_NO_ONE_HAS_AUTHORITY';
477 IF (g_po_wf_debug = 'Y') THEN
478 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
479 END IF;
480
481 resultout:='COMPLETE:'||'FAILURE';
482 return;
483
484 /* Bug# 2378775 */
485
486 ELSIF l_return_code in (PO_APPROVALLIST_S1.E_DOC_MGR_TIMEOUT,
487 PO_APPROVALLIST_S1.E_DOC_MGR_NOMGR,
488 PO_APPROVALLIST_S1.E_DOC_MGR_OTHER) THEN
489
490 set_doc_mgr_err(itemtype, itemkey, l_error_stack, l_return_code);
491 raise doc_manager_exception;
492
493 ELSE
494 l_progress := 'Build_Default_Approval_list: 022-E_APPROVAL_LIST_BUILD_FAIL';
495 IF (g_po_wf_debug = 'Y') THEN
496 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
497 END IF;
498
499 raise E_APPROVAL_LIST_BUILD_FAIL;
500 END IF;
501
502 END IF;
503
504 l_progress := 'Build_Default_Approval_list: 030 - Build_Default_Approval_list'||
505 ' - SUCCESS';
506 IF (g_po_wf_debug = 'Y') THEN
507 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
508 END IF;
509
510 resultout:='COMPLETE:'||'SUCCESS';
511 return;
512
513 END IF; -- run mode
514
515 EXCEPTION
516 WHEN doc_manager_exception THEN
517 raise;
518
519 WHEN OTHERS THEN
520 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
521 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
522 WF_CORE.context('PO_APPROVAL_LIST_WF1S' , 'Build_Default_Approval_list', itemtype, itemkey, l_progress);
523 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.Build_Default_Approval_list');
524 RAISE;
525
526 END Build_Default_Approval_list;
527
528 -- Rebuild_Approval_List
529 -- An approval list will be rebuilt under the following scenario:
530 -- (1) Approver forwards the requisition
531 -- (2) Approver modifies the requisition
532 -- (3) The current approver is not valid
533 --
534 procedure Rebuild_List_Forward(itemtype in varchar2,
535 itemkey in varchar2,
536 actid in number,
537 funcmode in varchar2,
538 resultout out NOCOPY varchar2) IS
539 l_progress VARCHAR2(300) := '000';
540 l_document_id NUMBER;
541 l_document_type VARCHAR2(25):='';
542 l_document_subtype VARCHAR2(25):='';
543 l_return_code NUMBER;
544 l_rebuild_code VARCHAR2(25):='FORWARD_RESPONSE';
545 l_error_stack PO_APPROVALLIST_S1.ErrorStackType;
546 l_approval_list_header_id NUMBER;
547
548 l_doc_string varchar2(200);
549 l_preparer_user_name varchar2(100);
550
551 l_org_id number;
552 doc_manager_exception exception;
553
554 BEGIN
555
556 IF (funcmode = 'RUN') THEN
557
558 l_progress := 'Rebuild_List_Forward: 001';
559 IF (g_po_wf_debug = 'Y') THEN
560 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
561 END IF;
562
563 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
564 itemkey => itemkey,
565 aname => 'DOCUMENT_ID');
566
567 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
568 itemkey => itemkey,
569 aname => 'DOCUMENT_TYPE');
570
571 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
572 itemkey => itemkey,
573 aname => 'DOCUMENT_SUBTYPE');
574
575 -- Set the multi-org context
576
577 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
578 itemkey => itemkey,
579 aname => 'ORG_ID');
580
581 IF l_org_id is NOT NULL THEN
582
583 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
584
585 END IF;
586
587 l_progress := 'Rebuild_List_Forward: 002-'||to_char(l_document_id)||'-'||
588 l_document_type||'-'||l_document_subtype;
589 IF (g_po_wf_debug = 'Y') THEN
590 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
591 END IF;
592
593 PO_APPROVALLIST_S1.rebuild_approval_list(
594 p_document_id=>l_document_id,
595 p_document_type=>l_document_type,
596 p_document_subtype=>l_document_subtype,
597 p_rebuild_code=>l_rebuild_code,
598 p_return_code=>l_return_code,
599 p_error_stack=>l_error_stack,
600 p_approval_list_header_id=>l_approval_list_header_id);
601
602 l_progress := 'Rebuild_List_Forward: 003- rebuild_approval_list - '||
603 l_rebuild_code||'-'||to_char(l_return_code);
604 IF (g_po_wf_debug = 'Y') THEN
605 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
606 END IF;
607
608
609 IF l_return_code=PO_APPROVALLIST_S1.E_SUCCESS THEN
610 wf_engine.SetItemAttrNumber ( itemtype => itemType,
611 itemkey => itemkey,
612 aname => 'APPROVAL_LIST_HEADER_ID',
613 avalue => l_approval_list_header_id);
614
615
616 resultout:='COMPLETE:'||'SUCCESS';
617 return;
618
619 /* Bug# 2378775 */
620
621 ELSIF l_return_code in (PO_APPROVALLIST_S1.E_DOC_MGR_TIMEOUT,
622 PO_APPROVALLIST_S1.E_DOC_MGR_NOMGR,
623 PO_APPROVALLIST_S1.E_DOC_MGR_OTHER) THEN
624
625 set_doc_mgr_err(itemtype, itemkey, l_error_stack, l_return_code);
626 raise doc_manager_exception;
627 ELSE
628 resultout:='COMPLETE:'||'FAILURE';
629 return;
630
631 END IF;
632
633 END IF; -- run mode
634
635
636 EXCEPTION
637 WHEN doc_manager_exception THEN
638 raise;
639
640 WHEN OTHERS THEN
641 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
642 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
643 wf_core.context('PO_APPROVAL_LIST_WF1S','Rebuild_List_Forward',l_progress,sqlerrm);
644 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.REBUILD_LIST_FORWARD');
645 RAISE;
646
647 END Rebuild_List_Forward;
648
649 --
650 procedure Rebuild_List_Doc_Changed(itemtype in varchar2,
651 itemkey in varchar2,
652 actid in number,
653 funcmode in varchar2,
654 resultout out NOCOPY varchar2)IS
655 l_progress VARCHAR2(300) := '000';
656 l_document_id NUMBER;
657 l_document_type VARCHAR2(25):='';
658 l_document_subtype VARCHAR2(25):='';
659 l_return_code NUMBER;
660 l_rebuild_code VARCHAR2(25):='DOCUMENT_CHANGED';
661 l_error_stack PO_APPROVALLIST_S1.ErrorStackType;
662 l_approval_list_header_id NUMBER:='';
663
664 l_doc_string varchar2(200);
665 l_preparer_user_name varchar2(100);
666
667 l_org_id number;
668 doc_manager_exception exception;
669
670 BEGIN
671
672 IF (funcmode = 'RUN') THEN
673
674 l_progress := 'Rebuild_List_Doc_Changed: 001';
675 IF (g_po_wf_debug = 'Y') THEN
676 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
677 END IF;
678
679 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
680 itemkey => itemkey,
681 aname => 'DOCUMENT_ID');
682
683 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
684 itemkey => itemkey,
685 aname => 'DOCUMENT_TYPE');
686
687 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
688 itemkey => itemkey,
689 aname => 'DOCUMENT_SUBTYPE');
690
691 -- Set the multi-org context
692
693 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
694 itemkey => itemkey,
695 aname => 'ORG_ID');
696
697 IF l_org_id is NOT NULL THEN
698
699 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
700
701 END IF;
702
703 l_progress := 'Rebuild_List_Doc_Changed: 002-'||to_char(l_document_id)||'-'||
704 l_document_type||'-'||l_document_subtype;
705 IF (g_po_wf_debug = 'Y') THEN
706 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
707 END IF;
708
709 PO_APPROVALLIST_S1.rebuild_approval_list(
710 p_document_id=>l_document_id,
711 p_document_type=>l_document_type,
712 p_document_subtype=>l_document_subtype,
713 p_rebuild_code=>l_rebuild_code,
714 p_return_code=>l_return_code,
715 p_error_stack=>l_error_stack,
716 p_approval_list_header_id=>l_approval_list_header_id);
717
718 l_progress := 'Rebuild_List_Doc_Changed: 003- rebuild_approval_list - '
719 ||l_rebuild_code||'-'||to_char(l_return_code);
720 IF (g_po_wf_debug = 'Y') THEN
721 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
722 END IF;
723
724 IF l_return_code=PO_APPROVALLIST_S1.E_SUCCESS THEN
725 wf_engine.SetItemAttrNumber ( itemtype => itemType,
726 itemkey => itemkey,
727 aname => 'APPROVAL_LIST_HEADER_ID',
728 avalue => l_approval_list_header_id);
729
730 resultout:='COMPLETE:'||'SUCCESS';
731 return;
732
733 /* Bug# 2378775 */
734
735 ELSIF l_return_code in (PO_APPROVALLIST_S1.E_DOC_MGR_TIMEOUT,
736 PO_APPROVALLIST_S1.E_DOC_MGR_NOMGR,
737 PO_APPROVALLIST_S1.E_DOC_MGR_OTHER) THEN
738
739 set_doc_mgr_err(itemtype, itemkey, l_error_stack, l_return_code);
740 raise doc_manager_exception;
741
742 ELSE
743 resultout:='COMPLETE:'||'FAILURE';
744 return;
745
746 END IF;
747
748 END IF; -- run mode
749
750
751 EXCEPTION
752 WHEN doc_manager_exception THEN
753 raise;
754
755 WHEN OTHERS THEN
756 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
757 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
758 wf_core.context('PO_APPROVAL_LIST_WF1S','Rebuild_List_Doc_Changed',l_progress,sqlerrm);
759 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.REBUILD_LIST_DOC_CHANGED');
760 RAISE;
761
762 END Rebuild_List_Doc_Changed;
763
764 --
765 procedure Rebuild_List_Invalid_Approver(itemtype in varchar2,
766 itemkey in varchar2,
767 actid in number,
768 funcmode in varchar2,
769 resultout out NOCOPY varchar2) IS
770 l_progress VARCHAR2(300) := '000';
771 l_document_id NUMBER;
772 l_document_type VARCHAR2(25):='';
773 l_document_subtype VARCHAR2(25):='';
774 l_return_code NUMBER;
775 l_rebuild_code VARCHAR2(25):='INVALID_APPROVER';
776 l_approval_list_header_id NUMBER:='';
777 l_error_stack PO_APPROVALLIST_S1.ErrorStackType;
778
779 l_doc_string varchar2(200);
780 l_preparer_user_name varchar2(100);
781
782 l_org_id number;
783
784 doc_manager_exception exception;
785
786 BEGIN
787
788 IF (funcmode = 'RUN') THEN
789
790 l_progress := 'Rebuild_List_Invalid_Approver: 001';
791 IF (g_po_wf_debug = 'Y') THEN
792 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
793 END IF;
794
795 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
796 itemkey => itemkey,
797 aname => 'DOCUMENT_ID');
798
799 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
800 itemkey => itemkey,
801 aname => 'DOCUMENT_TYPE');
802
803 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
804 itemkey => itemkey,
805 aname => 'DOCUMENT_SUBTYPE');
806
807 -- Set the multi-org context
808
809 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
810 itemkey => itemkey,
811 aname => 'ORG_ID');
812
813 IF l_org_id is NOT NULL THEN
814
815 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
816
817 END IF;
818
819 l_progress := 'Rebuild_List_Invalid_Approver: 002-'||
820 to_char(l_document_id)||'-'||
821 l_document_type||'-'||l_document_subtype;
822 IF (g_po_wf_debug = 'Y') THEN
823 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
824 END IF;
825
826 PO_APPROVALLIST_S1.rebuild_approval_list(
827 p_document_id=>l_document_id,
828 p_document_type=>l_document_type,
829 p_document_subtype=>l_document_subtype,
830 p_rebuild_code=>l_rebuild_code,
831 p_return_code=>l_return_code,
832 p_error_stack=>l_error_stack,
833 p_approval_list_header_id=>l_approval_list_header_id);
834
835 l_progress := 'Rebuild_List_Invalid_Approver: 003- rebuild_approval_list - '
836 ||l_rebuild_code||'-'||to_char(l_return_code);
837 IF (g_po_wf_debug = 'Y') THEN
838 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
839 END IF;
840
841 IF l_return_code=PO_APPROVALLIST_S1.E_SUCCESS THEN
842 wf_engine.SetItemAttrNumber ( itemtype => itemType,
843 itemkey => itemkey,
844 aname => 'APPROVAL_LIST_HEADER_ID',
845 avalue => l_approval_list_header_id);
846
847 resultout:='COMPLETE:'||'SUCCESS';
848 return;
849
850 /* Bug# 2378775 */
851
852 ELSIF l_return_code in (PO_APPROVALLIST_S1.E_DOC_MGR_TIMEOUT,
853 PO_APPROVALLIST_S1.E_DOC_MGR_NOMGR,
854 PO_APPROVALLIST_S1.E_DOC_MGR_OTHER) THEN
855
856 set_doc_mgr_err(itemtype, itemkey, l_error_stack, l_return_code);
857 raise doc_manager_exception;
858
859 ELSE
860 resultout:='COMPLETE:'||'FAILURE';
861 return;
862
863 END IF;
864
865 END IF; -- run mode
866
867 EXCEPTION
868 WHEN doc_manager_exception THEN
869 raise;
870
871 WHEN OTHERS THEN
872 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
873 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
874 wf_core.context('PO_APPROVAL_LIST_WF1S','Rebuild_List_Invalid_Approver',l_progress,sqlerrm);
875 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.REBUILD_LIST_INVALID_APPROVER');
876 RAISE;
877
878 END Rebuild_List_Invalid_Approver;
879
880 --
881 -- Get_Next_Approver
882 -- get the next approver name from the approval list
883 -- and update workflow attributes.
884 --
885 procedure Get_Next_Approver(itemtype in varchar2,
886 itemkey in varchar2,
887 actid in number,
888 funcmode in varchar2,
889 resultout out NOCOPY varchar2) IS
890 l_progress VARCHAR2(100) := '000';
891 l_document_id NUMBER;
892 l_document_type VARCHAR2(25):='';
893 l_document_subtype VARCHAR2(25):='';
894 l_return_code NUMBER;
895 l_next_approver_id NUMBER;
896 l_next_approver_user_name VARCHAR2(100);
897 l_next_approver_disp_name VARCHAR2(240);
898 l_orig_system VARCHAR2(48):='PER';
899 l_sequence_num NUMBER;
900 l_approver_type VARCHAR2(30);
901 E_FAILURE EXCEPTION;
902
903 l_doc_string varchar2(200);
904 l_preparer_user_name varchar2(100);
905
906 l_org_id number;
907
908 BEGIN
909 IF (funcmode = 'RUN') THEN
910
911 l_progress := 'Get_Next_Approver: 001';
912 IF (g_po_wf_debug = 'Y') THEN
913 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
914 END IF;
915
916 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
917 itemkey => itemkey,
918 aname => 'DOCUMENT_ID');
919
920 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
921 itemkey => itemkey,
922 aname => 'DOCUMENT_TYPE');
923
924 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
925 itemkey => itemkey,
926 aname => 'DOCUMENT_SUBTYPE');
927
928 -- Set the multi-org context
929
930 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
931 itemkey => itemkey,
932 aname => 'ORG_ID');
933
934 IF l_org_id is NOT NULL THEN
935
936 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
937
938 END IF;
939
940 l_progress := 'Get_Next_Approver: 002-'||to_char(l_document_id)||'-'||
941 l_document_type||'-'||l_document_subtype;
942 IF (g_po_wf_debug = 'Y') THEN
943 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
944 END IF;
945
946 PO_APPROVALLIST_S1.get_next_approver(l_document_id,
947 l_document_type,
948 l_document_subtype,
949 l_return_code,
950 l_next_approver_id,
951 l_sequence_num,
952 l_approver_type);
953
954 l_progress := 'Get_Next_Approver: 003- get_next_approver - '||
955 to_char(l_next_approver_id)||'-'||
956 to_char(l_return_code);
957 IF (g_po_wf_debug = 'Y') THEN
958 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
959 END IF;
960
961
962 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
963
964
965 wf_engine.SetItemAttrNumber ( itemtype => itemType,
966 itemkey => itemkey,
967 aname => 'APPROVER_EMPID',
968 avalue => l_next_approver_id);
969
970 wf_engine.SetItemAttrNumber ( itemtype => itemType,
971 itemkey => itemkey,
972 aname => 'FORWARD_TO_ID',
973 avalue => l_next_approver_id);
974
975 l_orig_system:= 'PER';
976
977 WF_DIRECTORY.GetUserName(l_orig_system,
978 l_next_approver_id,
979 l_next_approver_user_name,
980 l_next_approver_disp_name);
981
982 l_progress := 'Get_Next_Approver: 004- GetUserName - '||
983 l_next_approver_user_name;
984 IF (g_po_wf_debug = 'Y') THEN
985 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
986 END IF;
987
988 wf_engine.SetItemAttrText( itemtype => itemType,
989 itemkey => itemkey,
990 aname => 'APPROVER_USER_NAME' ,
991 avalue => l_next_approver_user_name);
992
993 wf_engine.SetItemAttrText( itemtype => itemType,
994 itemkey => itemkey,
995 aname => 'APPROVER_DISPLAY_NAME' ,
996 avalue => l_next_approver_disp_name);
997
998 wf_engine.SetItemAttrText( itemtype => itemType,
999 itemkey => itemkey,
1000 aname => 'FORWARD_TO_USERNAME' ,
1001 avalue => l_next_approver_user_name);
1002
1003 wf_engine.SetItemAttrText( itemtype => itemType,
1004 itemkey => itemkey,
1005 aname => 'FORWARD_TO_DISPLAY_NAME' ,
1006 avalue => l_next_approver_disp_name);
1007
1008 resultout:='COMPLETE:'||'VALID_APPROVER';
1009 return;
1010 ELSIF l_return_code = PO_APPROVALLIST_S1.E_NO_NEXT_APPROVER_FOUND THEN
1011 resultout:='COMPLETE:'||'NO_NEXT_APPROVER';
1012 return;
1013 ELSIF l_return_code = PO_APPROVALLIST_S1.E_INVALID_APPROVER THEN
1014 resultout:='COMPLETE:'||'INVALID_APPROVER';
1015 return;
1016 ELSE
1017 RAISE E_FAILURE;
1018 END IF;
1019 END IF;
1020 EXCEPTION
1021 WHEN E_FAILURE THEN
1022 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1023 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1024 wf_core.context('PO_APPROVAL_LIST_WF1S',
1025 'Get_Next_Approver E_FAILURE',
1026 l_progress,l_return_code,sqlerrm);
1027 -- wf_core.raise('Get_Next_Approver E_FAILURE' || l_progress||sqlerrm);
1028
1029 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.GET_NEXT_APPROVER');
1030
1031 RAISE;
1032 WHEN OTHERS THEN
1033 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1034 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1035 wf_core.context('PO_APPROVAL_LIST_WF1S','Get_Next_Approver',l_progress,sqlerrm);
1036
1037 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.GET_NEXT_APPROVER');
1038
1039 RAISE;
1040
1041 END Get_Next_Approver;
1042
1043
1044 -- Is_Approval_List_Empty
1045 -- at the end of the approval list
1046 -- i.e. list exhausted.
1047 procedure Is_Approval_List_Empty(itemtype in varchar2,
1048 itemkey in varchar2,
1049 actid in number,
1050 funcmode in varchar2,
1051 resultout out NOCOPY varchar2) IS
1052
1053 l_progress VARCHAR2(100) := '000';
1054 l_document_id NUMBER;
1055 l_document_type VARCHAR2(25):='';
1056 l_document_subtype VARCHAR2(25):='';
1057 l_return_code NUMBER;
1058 l_result BOOLEAN:=FALSE;
1059 E_FAILURE EXCEPTION;
1060
1061 l_doc_string varchar2(200);
1062 l_preparer_user_name varchar2(100);
1063
1064 l_org_id number;
1065
1066 BEGIN
1067 l_progress := 'Is_Approval_List_Empty: 001';
1068 IF (g_po_wf_debug = 'Y') THEN
1069 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1070 END IF;
1071
1072 IF (funcmode='RUN') THEN
1073
1074
1075 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1076 itemkey => itemkey,
1077 aname => 'DOCUMENT_ID');
1078
1079 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1080 itemkey => itemkey,
1081 aname => 'DOCUMENT_TYPE');
1082
1083 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
1084 itemkey => itemkey,
1085 aname => 'DOCUMENT_SUBTYPE');
1086
1087 -- Set the multi-org context
1088
1089 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1090 itemkey => itemkey,
1091 aname => 'ORG_ID');
1092
1093 IF l_org_id is NOT NULL THEN
1094
1095 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1096
1097 END IF;
1098
1099 l_progress := 'Is_Approval_List_Empty: 002-'||to_char(l_document_id)||'-'||
1100 l_document_type||'-'||l_document_subtype;
1101 IF (g_po_wf_debug = 'Y') THEN
1102 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1103 END IF;
1104
1105
1106 PO_APPROVALLIST_S1.is_approval_list_exhausted(p_document_id=>l_document_id,
1107 p_document_type=>l_document_type,
1108 p_document_subtype=>l_document_subtype,
1109 p_itemtype=>itemtype,
1110 p_itemkey=>itemkey,
1111 p_return_code=>l_return_code,
1112 p_result=> l_result);
1113
1114 l_progress := 'Is_Approval_List_Empty: 005- is_approval_list_exhausted -'||
1115 to_char(l_return_code);
1116 IF (g_po_wf_debug = 'Y') THEN
1117 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1118 END IF;
1119
1120 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
1121 IF l_result THEN
1122
1123 resultout:='COMPLETE:'||'Y';
1124 return;
1125 ELSE
1126 resultout:='COMPLETE:'||'N';
1127 return;
1128
1129 END IF;
1130
1131 ELSE
1132
1133 RAISE E_FAILURE;
1134
1135 END IF; -- return_code success
1136
1137 END IF; -- run mode
1138
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1142 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1143 wf_core.context('PO_APPROVAL_LIST_WF1S','Is_Approver_List_Empty',l_progress,sqlerrm);
1144 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.IS_APPROVAL_LIST_EMPTY');
1145 RAISE;
1146
1147 END Is_Approval_List_Empty;
1148
1149
1150 procedure Insert_Action_History(itemtype in varchar2,
1151 itemkey in varchar2,
1152 actid in number,
1153 funcmode in varchar2,
1154 resultout out NOCOPY varchar2) IS
1155
1156 l_progress VARCHAR2(100) := '000';
1157 l_action VARCHAR2(30) := 'APPROVE';
1158 l_next_approver_id NUMBER:='';
1159 l_approval_path_id NUMBER:='';
1160 l_req_header_id NUMBER:='';
1161
1162 l_doc_string varchar2(200);
1163 l_preparer_user_name varchar2(100);
1164
1165 l_org_id number;
1166
1167 BEGIN
1168
1169 l_progress := 'Insert_Action_History: 001';
1170 IF (g_po_wf_debug = 'Y') THEN
1171 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1172 END IF;
1173
1174 IF (funcmode='RUN') THEN
1175
1176 l_next_approver_id := wf_engine.GetItemAttrNumber(itemtype=>itemtype,
1177 itemkey=>itemkey,
1178 aname=>'APPROVER_EMPID');
1179
1180 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1181 itemkey => itemkey,
1182 aname => 'APPROVAL_PATH_ID');
1183
1184 l_req_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1185 itemkey => itemkey,
1186 aname => 'DOCUMENT_ID');
1187
1188 -- Set the multi-org context
1189
1190 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1191 itemkey => itemkey,
1192 aname => 'ORG_ID');
1193
1194 IF l_org_id is NOT NULL THEN
1195
1196 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1197
1198 END IF;
1199
1200 /* update po action history */
1201 PO_APPROVAL_LIST_HISTORY_SV.Forward_Action_History(itemtype=>itemtype,
1202 itemkey=>itemkey,
1203 x_forward_to_id=>l_next_approver_id,
1204 x_req_header_id=>l_req_header_id,
1205 x_approval_path_id=>l_approval_path_id);
1206
1207 l_progress := 'Insert_Action_History: 005 - Forward_Action_History';
1208 IF (g_po_wf_debug = 'Y') THEN
1209 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1210 END IF;
1211
1212 /* Reset the FORWARD_TO_USERNAME_RESPONSE attribute */
1213 wf_engine.SetItemAttrText (itemtype => itemtype,
1214 itemkey => itemkey,
1215 aname => 'FORWARD_TO_USERNAME_RESPONSE',
1216 avalue => NULL);
1217
1218 /* Reset the NOTE attribute */
1219 wf_engine.SetItemAttrText (itemtype => itemtype,
1220 itemkey => itemkey,
1221 aname => 'NOTE',
1222 avalue => NULL);
1223
1224 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1225 return;
1226
1227 END IF; -- run mode
1228
1229 l_progress := 'Insert_Action_History: 999';
1230 IF (g_po_wf_debug = 'Y') THEN
1231 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1232 END IF;
1233
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1237 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1238 wf_core.context('PO_APPROVAL_LIST_WF1S','Insert_Action_History',l_progress,sqlerrm);
1239 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.INSERT_ACTION_HISTORY');
1240 RAISE;
1241
1242 END Insert_Action_History;
1243
1244 procedure Update_Action_History_Approve(itemtype in varchar2,
1245 itemkey in varchar2,
1246 actid in number,
1247 funcmode in varchar2,
1248 resultout out NOCOPY varchar2) IS
1249
1250 l_progress VARCHAR2(100) := '000';
1251 l_action VARCHAR2(30) := 'APPROVE';
1252 l_forward_to_id NUMBER:='';
1253 l_document_id NUMBER;
1254 l_document_type VARCHAR2(25):='';
1255 l_document_subtype VARCHAR2(25):='';
1256 l_return_code NUMBER;
1257 l_result BOOLEAN:=FALSE;
1258 l_note VARCHAR2(4000);
1259
1260 l_doc_string varchar2(200);
1261 l_preparer_user_name varchar2(100);
1262
1263 l_org_id number;
1264
1265 BEGIN
1266
1267 l_progress := 'Update_Action_History_Approve: 001';
1268 IF (g_po_wf_debug = 'Y') THEN
1269 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1270 END IF;
1271
1272 IF (funcmode='RUN') THEN
1273
1274 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1275 itemkey => itemkey,
1276 aname => 'DOCUMENT_ID');
1277
1278 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1279 itemkey => itemkey,
1280 aname => 'DOCUMENT_TYPE');
1281
1282 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
1283 itemkey => itemkey,
1284 aname => 'DOCUMENT_SUBTYPE');
1285
1286 l_note := wf_engine.GetItemAttrText(itemtype => itemtype,
1287 itemkey => itemkey,
1288 aname => 'NOTE');
1289
1290 -- Set the multi-org context
1291
1292 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1293 itemkey => itemkey,
1294 aname => 'ORG_ID');
1295
1296 IF l_org_id is NOT NULL THEN
1297
1298 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1299
1300 END IF;
1301
1302 l_progress := 'Update_Action_History_Approve: 002-'||
1303 to_char(l_document_id)||'-'||
1304 l_document_type||'-'||l_document_subtype;
1305 IF (g_po_wf_debug = 'Y') THEN
1306 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1307 END IF;
1308
1309 /* update po action history */
1310 PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
1311 itemkey=>itemkey,
1312 x_action=>l_action,
1313 x_req_header_id=>l_document_id,
1314 x_last_approver=>l_result,
1315 x_note=>l_note);
1316
1317 l_progress := 'Update_Action_History_Approve: 005 - Update_Action_History';
1318 IF (g_po_wf_debug = 'Y') THEN
1319 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1320 END IF;
1321
1322 /* need to release locks for doc mgr */
1323 -- commit;
1324
1325 l_progress := 'Update_Action_History_Approve: 006';
1326 IF (g_po_wf_debug = 'Y') THEN
1327 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1328 END IF;
1329
1330 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1331 return;
1332
1333 END IF; -- run mode
1334
1335 l_progress := 'Update_Action_History_Approve: 999';
1336 IF (g_po_wf_debug = 'Y') THEN
1337 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1338 END IF;
1339
1340
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1344 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1345 wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Approve',l_progress,sqlerrm);
1346 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_APPROVE');
1347 RAISE;
1348
1349 END Update_Action_History_Approve;
1350
1351
1352 procedure Update_Action_History_Timeout(itemtype in varchar2,
1353 itemkey in varchar2,
1354 actid in number,
1355 funcmode in varchar2,
1356 resultout out NOCOPY varchar2) IS
1357
1358 l_progress VARCHAR2(100) := '000';
1359 l_action VARCHAR2(30) := 'NO ACTION';
1360 l_forward_to_id NUMBER:='';
1361 l_document_id NUMBER:='';
1362 l_document_type VARCHAR2(25):='';
1363 l_document_subtype VARCHAR2(25):='';
1364 l_return_code NUMBER;
1365 l_result BOOLEAN:=FALSE;
1366 l_note VARCHAR2(4000);
1367
1368 l_doc_string varchar2(200);
1369 l_preparer_user_name varchar2(100);
1370
1371 l_org_id number;
1372
1373 BEGIN
1374 l_progress := 'Update_Action_History_Timeout: 001';
1375 IF (g_po_wf_debug = 'Y') THEN
1376 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1377 END IF;
1378
1379 IF (funcmode='RUN') THEN
1380
1381 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1382 itemkey => itemkey,
1383 aname => 'DOCUMENT_ID');
1384
1385 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1386 itemkey => itemkey,
1387 aname => 'DOCUMENT_TYPE');
1388
1389 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
1390 itemkey => itemkey,
1391 aname => 'DOCUMENT_SUBTYPE');
1392
1393 l_note := fnd_message.get_string('ICX', 'ICX_POR_NOTIF_TIMEOUT');
1394
1395 -- Set the multi-org context
1396
1397 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1398 itemkey => itemkey,
1399 aname => 'ORG_ID');
1400
1401 IF l_org_id is NOT NULL THEN
1402 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1403 END IF;
1404
1405 l_progress := 'Update_Action_History_Timeout: 002-'||
1406 to_char(l_document_id)||'-'||
1407 l_document_type||'-'||l_document_subtype;
1408
1409 IF (g_po_wf_debug = 'Y') THEN
1410 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1411 END IF;
1412
1413 /* update po action history */
1414 PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
1415 itemkey=>itemkey,
1416 x_action=>l_action,
1417 x_req_header_id=>l_document_id,
1418 x_last_approver=>l_result,
1419 x_note=>l_note);
1420
1421 l_progress := 'Update_Action_History_Timeout: 003- Update_Action_History';
1422
1423 IF (g_po_wf_debug = 'Y') THEN
1424 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1425 END IF;
1426
1427 l_progress := 'Update_Action_History_App_Fwd: 004';
1428
1429 IF (g_po_wf_debug = 'Y') THEN
1430 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1431 END IF;
1432
1433 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1434 return;
1435
1436 END IF; -- run mode
1437
1438 l_progress := 'Update_Action_History_Timeout: 999';
1439 IF (g_po_wf_debug = 'Y') THEN
1440 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1441 END IF;
1442
1443 EXCEPTION
1444 WHEN OTHERS THEN
1445 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1446 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1447 wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Timeout',l_progress,sqlerrm);
1448 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_TIMEOUT');
1449 RAISE;
1450
1451 END Update_Action_History_Timeout;
1452
1453
1454 procedure Update_Action_History_App_Fwd(itemtype in varchar2,
1455 itemkey in varchar2,
1456 actid in number,
1457 funcmode in varchar2,
1458 resultout out NOCOPY varchar2) IS
1459
1460 l_progress VARCHAR2(100) := '000';
1461 l_action VARCHAR2(30) := 'APPROVE_AND_FORWARD';
1462 l_forward_to_id NUMBER:='';
1463 l_document_id NUMBER:='';
1464 l_document_type VARCHAR2(25):='';
1465 l_document_subtype VARCHAR2(25):='';
1466 l_return_code NUMBER;
1467 l_result BOOLEAN:=FALSE;
1468 l_note VARCHAR2(4000);
1469
1470 l_doc_string varchar2(200);
1471 l_preparer_user_name varchar2(100);
1472
1473 l_org_id number;
1474
1475 BEGIN
1476 l_progress := 'Update_Action_History_App_Fwd: 001';
1477 IF (g_po_wf_debug = 'Y') THEN
1478 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1479 END IF;
1480
1481 IF (funcmode='RUN') THEN
1482
1483 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1484 itemkey => itemkey,
1485 aname => 'DOCUMENT_ID');
1486
1487 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1488 itemkey => itemkey,
1489 aname => 'DOCUMENT_TYPE');
1490
1491 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
1492 itemkey => itemkey,
1493 aname => 'DOCUMENT_SUBTYPE');
1494
1495 l_note := wf_engine.GetItemAttrText(itemtype => itemtype,
1496 itemkey => itemkey,
1497 aname => 'NOTE');
1498
1499 -- Set the multi-org context
1500
1501 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1502 itemkey => itemkey,
1503 aname => 'ORG_ID');
1504
1505 IF l_org_id is NOT NULL THEN
1506
1507 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1508
1509 END IF;
1510
1511 l_progress := 'Update_Action_History_App_Fwd: 002-'||
1512 to_char(l_document_id)||'-'||
1513 l_document_type||'-'||l_document_subtype;
1514 IF (g_po_wf_debug = 'Y') THEN
1515 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1516 END IF;
1517
1518 /* update po action history */
1519 PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
1520 itemkey=>itemkey,
1521 x_action=>l_action,
1522 x_req_header_id=>l_document_id,
1523 x_last_approver=>l_result,
1524 x_note=>l_note);
1525
1526 l_progress := 'Update_Action_History_App_Fwd: 005- Update_Action_History';
1527 IF (g_po_wf_debug = 'Y') THEN
1528 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1529 END IF;
1530
1531 /* need to release locks for doc mgr */
1532 -- commit;
1533
1534 l_progress := 'Update_Action_History_App_Fwd: 006';
1535 IF (g_po_wf_debug = 'Y') THEN
1536 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1537 END IF;
1538
1539 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1540 return;
1541
1542 END IF; -- run mode
1543
1544 l_progress := 'Update_Action_History_App_Fwd: 999';
1545 IF (g_po_wf_debug = 'Y') THEN
1546 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1547 END IF;
1548
1549 EXCEPTION
1550 WHEN OTHERS THEN
1551 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1552 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1553 wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_App_Fwd',l_progress,sqlerrm);
1554 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_APP_FWD');
1555 RAISE;
1556
1557 END Update_Action_History_App_Fwd;
1558
1559 procedure Update_Action_History_Forward(itemtype in varchar2,
1560 itemkey in varchar2,
1561 actid in number,
1562 funcmode in varchar2,
1563 resultout out NOCOPY varchar2) IS
1564
1565 l_progress VARCHAR2(100) := '000';
1566 l_action VARCHAR2(30) := 'FORWARD';
1567 l_forward_to_id NUMBER:='';
1568 l_document_id NUMBER:='';
1569 l_document_type VARCHAR2(25):='';
1570 l_document_subtype VARCHAR2(25):='';
1571 l_return_code NUMBER;
1572 l_result BOOLEAN:=FALSE;
1573 l_note VARCHAR2(4000);
1574
1575 l_doc_string varchar2(200);
1576 l_preparer_user_name varchar2(100);
1577
1578 l_org_id number;
1579
1580 BEGIN
1581 l_progress := 'Update_Action_History_Forward: 001';
1582 IF (g_po_wf_debug = 'Y') THEN
1583 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1584 END IF;
1585
1586 IF (funcmode='RUN') THEN
1587
1588 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1589 itemkey => itemkey,
1590 aname => 'DOCUMENT_ID');
1591
1592 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1593 itemkey => itemkey,
1594 aname => 'DOCUMENT_TYPE');
1595
1596 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
1597 itemkey => itemkey,
1598 aname => 'DOCUMENT_SUBTYPE');
1599
1600 l_note := wf_engine.GetItemAttrText(itemtype => itemtype,
1601 itemkey => itemkey,
1602 aname => 'NOTE');
1603
1604 -- Set the multi-org context
1605
1606 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1607 itemkey => itemkey,
1608 aname => 'ORG_ID');
1609
1610 IF l_org_id is NOT NULL THEN
1611
1612 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1613
1614 END IF;
1615
1616 l_progress := 'Update_Action_History_Forward: 002-'||
1617 to_char(l_document_id)||'-'||
1618 l_document_type||'-'||l_document_subtype;
1619 IF (g_po_wf_debug = 'Y') THEN
1620 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1621 END IF;
1622
1623 /* update po action history */
1624 PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
1625 itemkey=>itemkey,
1626 x_action=>l_action,
1627 x_req_header_id=>l_document_id,
1628 x_last_approver=>l_result,
1629 x_note=>l_note);
1630
1631 l_progress := 'Update_Action_History_Forward: 005- Update_Action_History';
1632 IF (g_po_wf_debug = 'Y') THEN
1633 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1634 END IF;
1635
1636 /* need to release locks for doc mgr */
1637 -- commit;
1638
1639 l_progress := 'Update_Action_History_Forward: 006';
1640 IF (g_po_wf_debug = 'Y') THEN
1641 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1642 END IF;
1643
1644 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1645 return;
1646
1647 END IF; -- run mode
1648 l_progress := 'Update_Action_History_Forward: 999';
1649 IF (g_po_wf_debug = 'Y') THEN
1650 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1651 END IF;
1652
1653 EXCEPTION
1654 WHEN OTHERS THEN
1655 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1656 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1657 wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Forward',l_progress,sqlerrm);
1658 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_FORWARD');
1659 RAISE;
1660
1661 END Update_Action_History_Forward;
1662
1663 procedure Update_Action_History_Reject(itemtype in varchar2,
1664 itemkey in varchar2,
1665 actid in number,
1666 funcmode in varchar2,
1667 resultout out NOCOPY varchar2) IS
1668
1669 l_progress VARCHAR2(100) := '000';
1670 l_action VARCHAR2(30) := 'REJECT';
1671 l_forward_to_id NUMBER:='';
1672 l_document_id NUMBER:='';
1673 l_document_type VARCHAR2(25):='';
1674 l_document_subtype VARCHAR2(25):='';
1675 l_return_code NUMBER;
1676 l_result BOOLEAN:=FALSE;
1677 l_note VARCHAR2(4000);
1678
1679 l_doc_string varchar2(200);
1680 l_preparer_user_name varchar2(100);
1681
1682 l_org_id number;
1683
1684 BEGIN
1685 l_progress := 'Update_Action_History_Reject: 001';
1686 IF (g_po_wf_debug = 'Y') THEN
1687 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1688 END IF;
1689
1690 IF (funcmode='RUN') THEN
1691
1692 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1693 itemkey => itemkey,
1694 aname => 'DOCUMENT_ID');
1695
1696 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1697 itemkey => itemkey,
1698 aname => 'DOCUMENT_TYPE');
1699
1700 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
1701 itemkey => itemkey,
1702 aname => 'DOCUMENT_SUBTYPE');
1703
1704 l_note := wf_engine.GetItemAttrText(itemtype => itemtype,
1705 itemkey => itemkey,
1706 aname => 'NOTE');
1707
1708 -- Set the multi-org context
1709
1710 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1711 itemkey => itemkey,
1712 aname => 'ORG_ID');
1713
1714 IF l_org_id is NOT NULL THEN
1715
1716 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
1717
1718 END IF;
1719
1720 l_progress := 'Update_Action_History_Reject: 002-'||
1721 to_char(l_document_id)||'-'||
1722 l_document_type||'-'||l_document_subtype;
1723 IF (g_po_wf_debug = 'Y') THEN
1724 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1725 END IF;
1726
1727 /* update po action history */
1728 PO_APPROVAL_LIST_HISTORY_SV.Update_Action_History(itemtype=>itemtype,
1729 itemkey=>itemkey,
1730 x_action=>l_action,
1731 x_req_header_id=>l_document_id,
1732 x_last_approver=>l_result,
1733 x_note=>l_note);
1734
1735 l_progress := 'Update_Action_History_Reject: 005 - Update_Action_History';
1736 IF (g_po_wf_debug = 'Y') THEN
1737 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1738 END IF;
1739
1740 /* need to release locks for doc mgr */
1741 -- commit;
1742
1743 l_progress := 'Update_Action_History_Reject: 006';
1744 IF (g_po_wf_debug = 'Y') THEN
1745 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1746 END IF;
1747
1748 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
1749 return;
1750
1751 END IF; -- run mode
1752 l_progress := 'Update_Action_History_Reject: 999';
1753 IF (g_po_wf_debug = 'Y') THEN
1754 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1755 END IF;
1756
1757 EXCEPTION
1758 WHEN OTHERS THEN
1759 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1760 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1761 wf_core.context('PO_APPROVAL_LIST_WF1S','Update_Action_History_Reject',l_progress,sqlerrm);
1762 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_ACTION_HISTORY_REJECT');
1763 RAISE;
1764
1765 END Update_Action_History_Reject;
1766
1767 /* Bug# 1712121: kagarwal
1768 ** Desc: We now use the new API: Update_App_List_Resp_Success.
1769 **
1770 ** Also reverted the change of bug# 1394711 in the old API
1771 ** 'Update_Approval_List_Response'. It will now return 'ACTIVITY_PERFORMED'
1772 ** as before the fix in bug# 1394711.
1773 **
1774 ** This is to support reqs submitted for approval before applying this bug
1775 ** fix otherwise their approval will error out due to change in return value.
1776 **
1777 ** For reqs submitted for approval after bug# 1394711 and before this new
1778 ** fix, modified the API 'Update_Approval_List_Response' to check for the
1779 ** expected result type for that req approval process and return
1780 ** 'SUCCESS-FAILURE' instead of 'ACTIVITY_PERFORMED' if the expected result
1781 ** lookup type is 'PO_SUCCESS_FAILURE'.
1782 */
1783
1784 procedure Update_Approval_List_Response(itemtype in varchar2,
1785 itemkey in varchar2,
1786 actid in number,
1787 funcmode in varchar2,
1788 resultout out NOCOPY varchar2) IS
1789 CURSOR c_group_id (p_itemtype VARCHAR2, p_itemkey VARCHAR2, p_activity_name VARCHAR2) IS
1790 SELECT notification_id
1791 FROM wf_item_activity_statuses_v
1792 WHERE item_type = p_itemtype
1793 AND item_key = p_itemkey
1794 AND activity_name = p_activity_name
1795 ORDER BY activity_end_date DESC;
1796
1797 -- bug 1263201
1798 -- We need to get the responder information from the first
1799 -- valid (not timeout/canceled) notification to show the error
1800 -- notification properly.
1801 -- The sequence of notification be checked is
1802 -- PO_REQ_APPROVE, PO_REQ_REMINDER2 then PO_REQ_REMINDER1.
1803
1804 CURSOR c_canceled_notif (notif_id number) IS
1805 SELECT '1'
1806 FROM WF_NOTIFICATIONS
1807 WHERE notification_id = notif_id
1808 AND status = 'CANCELED';
1809
1810 CURSOR c_response(p_group_id number) IS
1811 SELECT recipient_role, attribute_value
1812 FROM wf_notification_attr_resp_v
1813 WHERE group_id = p_group_id
1814 AND attribute_name = 'RESULT';
1815
1816 CURSOR c_response_note(p_group_id number) IS
1817 SELECT attribute_value
1818 FROM wf_notification_attr_resp_v
1819 WHERE group_id = p_group_id
1820 AND attribute_name = 'NOTE';
1821
1822 /* Bug 1578061: remove the join to wf_notifications. This forces the
1823 removal of end_date column
1824
1825 CURSOR c_responder(p_notification_id number) IS
1826 SELECT nvl((wfu.orig_system_id), -9996)
1827 FROM wf_users wfu
1828 WHERE wfu.name = wf_notification.responder(p_notification_id)
1829 AND wfu.orig_system not in ('POS', 'ENG_LIST', 'CUST_CONT');
1830 */
1831
1832 /* bug 1817306 new cursor c_responderid is defined to replace c_responder */
1833 CURSOR c_responderid(p_responder VARCHAR2) IS
1834 SELECT nvl((wfu.orig_system_id), -9996)
1835 FROM wf_users wfu
1836 WHERE wfu.name = p_responder
1837 AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
1838
1839 l_progress VARCHAR2(100) := '000';
1840 l_group_id NUMBER;
1841 l_role VARCHAR2(30);
1842 l_value VARCHAR2(2000);
1843 l_approver_id NUMBER := NULL;
1844 l_responder_id NUMBER := NULL;
1845 l_forward_to_id NUMBER := NULL;
1846 l_document_id NUMBER;
1847 l_document_type VARCHAR2(25):='';
1848 l_document_subtype VARCHAR2(25):='';
1849 l_return_code NUMBER;
1850 l_orgid NUMBER;
1851 l_approval_list_header_id NUMBER:='';
1852 l_error_stack PO_APPROVALLIST_S1.ErrorStackType;
1853 E_UPDATE_RESPONSE_FAIL EXCEPTION;
1854 l_end_date DATE; -- notification end date
1855 l_note VARCHAR2(4000);
1856 l_orig_system VARCHAR2(48);
1857 l_responder_user_name VARCHAR2(100);
1858 l_responder_disp_name VARCHAR2(240);
1859 l_responder VARCHAR2(240);
1860 l_recipient_role VARCHAR2(30);
1861
1862 l_doc_string varchar2(200);
1863 l_preparer_user_name varchar2(100);
1864
1865 l_org_id number;
1866 is_notif_canceled VARCHAR2(2);
1867
1868 /* Bug# 1712121 */
1869 retnew BOOLEAN := FALSE;
1870 exp_result VARCHAR2(30);
1871
1872
1873 BEGIN
1874
1875 l_progress := 'Update_Approval_List_Response: 001- at beginning of function';
1876 IF (g_po_wf_debug = 'Y') THEN
1877 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1878 END IF;
1879
1880 /* Bug# 1431401: kagarwal
1881 ** Desc: We need to set the doc manager context as the response may
1882 ** be coming from the E-mail Notifications otherwise the call to
1883 ** doc manager would fail.
1884 */
1885
1886 IF (funcmode='RUN') THEN
1887 -- Context Setting revamp
1888 -- set_doc_mgr_context(itemtype, itemkey);
1889
1890 /* Bug# 1712121: kagarwal
1891 ** Desc: For reqs submitted for approval after bug# 1394711 and before this new
1892 ** fix, modified the API 'Update_Approval_List_Response' to check for the
1893 ** expected result type for that req approval process and return
1894 ** 'SUCCESS-FAILURE' instead of 'ACTIVITY_PERFORMED' if the expected result
1895 ** lookup type is 'PO_SUCCESS_FAILURE'.
1896 **
1897 ** We can achieve this by running the following sql which returns
1898 ** the expected result type.
1899 */
1900
1901 Begin
1902 select wa.result_type
1903 into exp_result
1904 from wf_activities wa,
1905 wf_process_activities wpa,
1906 wf_items wi
1907 where wpa.instance_id = actid
1908 and wpa.process_item_type = wa.item_type
1909 and wpa.activity_name = wa.name
1910 and wi.item_type = wpa.process_item_type
1911 and wi.item_key = itemkey
1912 and wi.begin_date > wa.begin_date
1913 and wi.begin_date <= nvl(wa.end_date,wi.begin_date);
1914
1915 exception
1916 when others then
1917 null;
1918 end;
1919
1920 l_progress := 'Update_Approval_List_Response : 001-2'||
1921 'exp_result: ' || exp_result || ' actid: ' || to_char(actid);
1922 IF (g_po_wf_debug = 'Y') THEN
1923 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1924 END IF;
1925
1926 if exp_result = 'PO_SUCCESS_FAILURE' then
1927 retnew := TRUE;
1928 end if;
1929
1930 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1931 itemkey => itemkey,
1932 aname => 'ORG_ID');
1933
1934
1935 IF l_orgid is NOT NULL THEN
1936
1937 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
1938
1939 END IF;
1940
1941 l_progress := 'Update_Approval_List_Response: 002';
1942 IF (g_po_wf_debug = 'Y') THEN
1943 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1944 END IF;
1945
1946 OPEN c_group_id(itemtype, itemkey, 'PO_REQ_APPROVE');
1947 FETCH c_group_id INTO l_group_id;
1948 CLOSE c_group_id;
1949
1950 l_progress := 'Update_Approval_List_Response: 003';
1951 IF (g_po_wf_debug = 'Y') THEN
1952 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1953 END IF;
1954
1955 /* start of fix for 1263201 */
1956 OPEN c_canceled_notif (l_group_id);
1957 FETCH c_canceled_notif into is_notif_canceled;
1958
1959 -- check if PO_REQ_APPROVE notification is canceled
1960 IF c_canceled_notif%FOUND THEN
1961
1962 CLOSE c_canceled_notif;
1963
1964 l_progress := 'Update_Approval_List_Response: 0031';
1965 IF (g_po_wf_debug = 'Y') THEN
1966 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1967 END IF;
1968
1969 l_group_id := NULL;
1970
1971 OPEN c_group_id(itemtype, itemkey, 'PO_REQ_REMINDER2');
1972 FETCH c_group_id INTO l_group_id;
1973 CLOSE c_group_id;
1974
1975 l_progress := 'Update_Approval_List_Response: 0032';
1976 IF (g_po_wf_debug = 'Y') THEN
1977 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1978 END IF;
1979
1980 OPEN c_canceled_notif (l_group_id);
1981 FETCH c_canceled_notif into is_notif_canceled;
1982
1983 -- check if PO_REQ_REMINDER2 notification is canceled
1984 IF c_canceled_notif%FOUND THEN
1985
1986 CLOSE c_canceled_notif;
1987
1988 l_progress := 'Update_Approval_List_Response: 0033';
1989 IF (g_po_wf_debug = 'Y') THEN
1990 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
1991 END IF;
1992
1993 l_group_id := NULL;
1994
1995 OPEN c_group_id(itemtype, itemkey, 'PO_REQ_REMINDER1');
1996 FETCH c_group_id INTO l_group_id;
1997 CLOSE c_group_id;
1998
1999 l_progress := 'Update_Approval_List_Response: 0034';
2000 IF (g_po_wf_debug = 'Y') THEN
2001 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2002 END IF;
2003
2004 OPEN c_canceled_notif (l_group_id);
2005 FETCH c_canceled_notif into is_notif_canceled;
2006
2007 l_progress := 'Update_Approval_List_Response: 00341';
2008 IF (g_po_wf_debug = 'Y') THEN
2009 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2010 END IF;
2011
2012 -- check if PO_REQ_REMINDER1 notification is canceled
2013
2014 IF c_canceled_notif%FOUND THEN
2015
2016 CLOSE c_canceled_notif;
2017 l_progress := 'Update_Approval_List_Response: 00342';
2018 l_group_id := NULL;
2019
2020 ELSE -- PO_REQ_REMINDER1 notification is not canceled
2021
2022 CLOSE c_canceled_notif;
2023
2024 END IF; -- check if PO_REQ_REMINDER2 notification is canceled
2025
2026 ELSE -- PO_REQ_REMINDER2 notification is not canceled
2027
2028 CLOSE c_canceled_notif;
2029
2030 END IF; -- check if PO_REQ_REMINDER2 notification is canceled
2031
2032 ELSE -- PO_REQ_APPROVE notifications is not canceled
2033
2034 CLOSE c_canceled_notif;
2035
2036 END IF; -- checked if the PO_REQ_APPROVE notifications is canceled
2037
2038 /* end of fix for 1263201 */
2039
2040 l_progress := 'Update_Approval_List_Response: 0035';
2041 IF (g_po_wf_debug = 'Y') THEN
2042 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2043 END IF;
2044
2045 IF l_group_id is NOT NULL THEN
2046 OPEN c_response(l_group_id);
2047 FETCH c_response INTO l_role, l_value;
2048 CLOSE c_response;
2049
2050 l_progress := 'Update_Approval_List_Response: 004';
2051 IF (g_po_wf_debug = 'Y') THEN
2052 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2053 END IF;
2054
2055 IF l_group_id is NOT NULL THEN
2056
2057 /* Bug 1257763 */
2058 SELECT wfn.responder, wfn.recipient_role, wfn.end_date
2059 INTO l_responder, l_recipient_role, l_end_date
2060 FROM wf_notifications wfn
2061 WHERE wfn.notification_id = l_group_id;
2062
2063 /* csheu bug #1287135 use reponder value in wf_notification to find
2064 its orig_system_id from wf_users. If no matched rows found from
2065 wf_users then we will use l_recipient_role value from wf_notification
2066 to find its orig_system_id from wf_users instead.
2067 */
2068
2069 OPEN c_responderid(l_responder);
2070 FETCH c_responderid INTO l_responder_id;
2071
2072 IF c_responderid%NOTFOUND THEN
2073
2074 CLOSE c_responderid;
2075 OPEN c_responderid(l_recipient_role);
2076 FETCH c_responderid INTO l_responder_id;
2077 CLOSE c_responderid;
2078
2079 END IF;
2080
2081 IF (c_responderid%ISOPEN) THEN
2082 CLOSE c_responderid;
2083 END IF;
2084
2085
2086 l_progress := 'Update_Approval_List_Response: 005';
2087 IF (g_po_wf_debug = 'Y') THEN
2088 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2089 END IF;
2090
2091 wf_engine.SetItemAttrNumber(itemtype => itemType,
2092 itemkey => itemkey,
2093 aname => 'RESPONDER_ID',
2094 avalue => l_responder_id);
2095
2096 l_orig_system:= 'PER';
2097
2098 WF_DIRECTORY.GetUserName(l_orig_system,
2099 l_responder_id,
2100 l_responder_user_name,
2101 l_responder_disp_name);
2102
2103 l_progress := 'Update_Approval_List_Response: 007 -' || l_responder_user_name;
2104 IF (g_po_wf_debug = 'Y') THEN
2105 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2106 END IF;
2107
2108 wf_engine.SetItemAttrText( itemtype => itemType,
2109 itemkey => itemkey,
2110 aname => 'RESPONDER_USER_NAME' ,
2111 avalue => l_responder_user_name);
2112
2113 wf_engine.SetItemAttrText( itemtype => itemType,
2114 itemkey => itemkey,
2115 aname => 'RESPONDER_DISPLAY_NAME' ,
2116 avalue => l_responder_disp_name);
2117
2118 l_progress := 'Update_Approval_List_Response: 008' ;
2119 IF (g_po_wf_debug = 'Y') THEN
2120 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2121 END IF;
2122
2123 IF (INSTR(l_value, 'FORWARD') > 0) THEN
2124 l_forward_to_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2125 itemkey => itemkey,
2126 aname => 'FORWARD_TO_ID');
2127 END IF;
2128
2129 l_progress := 'Update_Approval_List_Response: 009' ;
2130 IF (g_po_wf_debug = 'Y') THEN
2131 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2132 END IF;
2133
2134 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2135 itemkey => itemkey,
2136 aname => 'DOCUMENT_ID');
2137
2138 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2139 itemkey => itemkey,
2140 aname => 'DOCUMENT_TYPE');
2141
2142 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2143 itemkey => itemkey,
2144 aname => 'DOCUMENT_SUBTYPE');
2145
2146 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>itemtype,
2147 itemkey=>itemkey,
2148 aname=>'APPROVER_EMPID');
2149
2150 l_note := wf_engine.GetItemAttrText(itemtype => itemtype,
2151 itemkey => itemkey,
2152 aname => 'NOTE');
2153
2154 l_progress := 'Update_Approval_List_Response: 010 APP'||
2155 to_char(l_approver_id)||
2156 ' RES'||to_char(l_responder_id)||
2157 ' FWD'||to_char(l_forward_to_id);
2158 IF (g_po_wf_debug = 'Y') THEN
2159 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2160 END IF;
2161
2162 PO_APPROVALLIST_S1.update_approval_list_response(
2163 p_document_id=>l_document_id,
2164 p_document_type=>l_document_type,
2165 p_document_subtype=>l_document_subtype,
2166 p_itemtype=>itemtype,
2167 p_itemkey=>itemkey,
2168 p_approver_id=>l_approver_id,
2169 p_responder_id=>l_responder_id,
2170 p_forward_to_id=>l_forward_to_id,
2171 p_response=>l_value,
2172 p_response_date=>l_end_date,
2173 p_comments=>substrb(l_note,1,480), -- bug 3105327
2174 p_return_code=>l_return_code);
2175
2176 l_progress := 'Update_Approval_List_Response: 011'||to_char(l_return_code);
2177 IF (g_po_wf_debug = 'Y') THEN
2178 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2179 END IF;
2180
2181 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
2182
2183 /* rebuild since it is a forward */
2184 IF l_value in ('FORWARD', 'APPROVE_AND_FORWARD') THEN
2185
2186 l_progress := 'Update_Approval_List_Response: 012';
2187 IF (g_po_wf_debug = 'Y') THEN
2188 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2189 END IF;
2190
2191 PO_APPROVALLIST_S1.rebuild_approval_list(
2192 p_document_id=>l_document_id,
2193 p_document_type=>l_document_type,
2194 p_document_subtype=>l_document_subtype,
2195 p_rebuild_code=>'FORWARD_RESPONSE',
2196 p_return_code=>l_return_code,
2197 p_error_stack=>l_error_stack,
2198 p_approval_list_header_id=>l_approval_list_header_id);
2199
2200 l_progress := 'Update_Approval_List_Response : 013'||to_char(l_return_code);
2201 IF (g_po_wf_debug = 'Y') THEN
2202 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2203 END IF;
2204
2205 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
2206 wf_engine.SetItemAttrNumber(itemtype => itemType,
2207 itemkey => itemkey,
2208 aname => 'APPROVAL_LIST_HEADER_ID',
2209 avalue => l_approval_list_header_id);
2210
2211 /* Bug# 1712121 */
2212 if retnew = TRUE then
2213 resultout:='COMPLETE' || ':' || 'SUCCESS';
2214 else
2215 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2216 end if;
2217
2218 RETURN;
2219
2220 /* Bug# 1394711
2221 ** Desc: The Update_Approval_List_Response() procedure raises exception
2222 ** when the rebuild_approval_list() fails and the approval workflow
2223 ** hangs. We need to handle the situation when the rebuild_approval_list()
2224 ** fails because of No approver found in order to return the Requisition
2225 ** to the preparer.
2226 **
2227 ** Changed the procedure Update_Approval_List_Response() to return FAILURE
2228 ** for the above condition or SUCCESS instead of ACTIVITY_PERFORMED.
2229 **
2230 ** The Requsition workflow also has been changed to handle the above.
2231 **
2232 ** Dependency: poxwfrqa.wft
2233 */
2234
2235 ELSIF l_return_code = PO_APPROVALLIST_S1.E_NO_ONE_HAS_AUTHORITY THEN
2236
2237 /* Bug# 1712121 */
2238
2239 if retnew = TRUE then
2240 resultout:='COMPLETE' || ':' || 'FAILURE';
2241 end if;
2242 RETURN;
2243 END IF; --rebuild success
2244
2245 ELSE
2246 /* no need to rebuild for approve or reject actions */
2247 l_progress := 'Update_Approval_List_Response : 100';
2248 IF (g_po_wf_debug = 'Y') THEN
2249 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2250 END IF;
2251
2252 /* Bug# 1712121 */
2253 if retnew = TRUE then
2254 resultout:='COMPLETE' || ':' || 'SUCCESS';
2255 else
2256 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2257 end if;
2258 RETURN;
2259
2260 END IF; -- forward action
2261
2262 END IF; -- update success
2263
2264 END IF;
2265
2266 END IF; -- c_group_id
2267 l_progress := 'Update_Approval_List_Response : 999';
2268 IF (g_po_wf_debug = 'Y') THEN
2269 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2270 END IF;
2271
2272 RAISE E_UPDATE_RESPONSE_FAIL;
2273 END IF; -- run mode
2274
2275 EXCEPTION
2276 WHEN E_UPDATE_RESPONSE_FAIL THEN
2277 IF (c_group_id%ISOPEN) THEN
2278 CLOSE c_group_id;
2279 END IF;
2280 IF (c_response%ISOPEN) THEN
2281 CLOSE c_response;
2282 END IF;
2283
2284 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2285 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2286 wf_core.context('PO_APPROVAL_LIST_WF1S',
2287 'Update_Approval_List_Response E_FAILURE',
2288 l_progress,l_return_code,sqlerrm);
2289 -- wf_core.raise('Find_Approval_list E_FAILURE' || l_progress||sqlerrm);
2290
2291 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APPROVAL_LIST_RESPONSE');
2292
2293 RAISE;
2294
2295 WHEN OTHERS THEN
2296 IF (c_group_id%ISOPEN) THEN
2297 CLOSE c_group_id;
2298 END IF;
2299 IF (c_response%ISOPEN) THEN
2300 CLOSE c_response;
2301 END IF;
2302
2303 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2304 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2305 wf_core.context('PO_APPROVAL_LIST_WF1S',
2306 'Update_Approval_List_Response',l_progress,sqlerrm);
2307
2308 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APPROVAL_LIST_RESPONSE');
2309
2310 RAISE;
2311
2312 END Update_Approval_List_Response;
2313
2314 --------------------------------------------------------------------------------
2315 --Start of Comments
2316 --Name: get_approval_response
2317 --It is migrated from existing code in procedure Update_App_List_Resp_Success.
2318 --It is made a public procedure so that the same logic can be shared by AME approval.
2319 --Pre-reqs:
2320 -- None.
2321 --Modifies:
2322 -- None.
2323 --Locks:
2324 -- None.
2325 --Function:
2326 -- Find the workflow notification's responder.
2327 --Parameters:
2328 --IN:
2329 --itemtype
2330 -- workflow item type
2331 --itemtype
2332 -- workflow item key
2333 --OUT:
2334 --responderId
2335 -- Notification responder ID
2336 --response
2337 -- Notification response
2338 --responseEndDate
2339 -- Notification response end date
2340 --forwardToId
2341 -- Notification forward to person ID
2342 --End of Comments
2343 -------------------------------------------------------------------------------
2344 procedure get_approval_response(itemtype in varchar2,
2345 itemkey in varchar2,
2346 responderId out NOCOPY number,
2347 response out NOCOPY varchar2,
2348 responseEndDate out NOCOPY date,
2349 forwardToId out NOCOPY number) is
2350
2351 CURSOR c_group_id (p_itemtype VARCHAR2, p_itemkey VARCHAR2, p_activity_name VARCHAR2, p_activity_name2 VARCHAR2, p_activity_name3 VARCHAR2, p_activity_name4 VARCHAR2) IS
2352 SELECT notification_id
2353 FROM wf_item_activity_statuses_v
2354 WHERE item_type = p_itemtype
2355 AND item_key = p_itemkey
2356 AND activity_name in ( p_activity_name, p_activity_name2,
2357 p_activity_name3, p_activity_name4)
2358 ORDER BY activity_end_date DESC;
2359
2360 CURSOR c_canceled_notif (notif_id number) IS
2361 SELECT '1'
2362 FROM WF_NOTIFICATIONS
2363 WHERE notification_id = notif_id
2364 AND status = 'CANCELED';
2365
2366 CURSOR c_response(p_group_id number) IS
2367 SELECT recipient_role, attribute_value
2368 FROM wf_notification_attr_resp_v
2369 WHERE group_id = p_group_id
2370 AND attribute_name = 'RESULT';
2371
2372 CURSOR c_response_note(p_group_id number) IS
2373 SELECT attribute_value
2374 FROM wf_notification_attr_resp_v
2375 WHERE group_id = p_group_id
2376 AND attribute_name = 'NOTE';
2377
2378 /* bug 1817306 new cursor c_responderid is defined to replace c_responder */
2379 CURSOR c_responderid(p_responder VARCHAR2) IS
2380 SELECT nvl((wfu.orig_system_id), -9996)
2381 FROM wf_users wfu
2382 WHERE wfu.name = p_responder
2383 AND wfu.orig_system not in ('HZ_PARTY', 'POS', 'ENG_LIST', 'CUST_CONT');
2384
2385 l_responder wf_notifications.responder%TYPE;
2386 l_recipient_role wf_notifications.recipient_role%TYPE;
2387
2388 l_progress VARCHAR2(100) := '000';
2389 l_group_id NUMBER;
2390 l_role wf_notifications.recipient_role%TYPE;
2391 l_approver_id NUMBER := NULL;
2392 l_orig_system wf_users.orig_system%TYPE;
2393 l_responder_user_name wf_users.name%TYPE;
2394 l_responder_disp_name wf_users.display_name%TYPE;
2395
2396 l_org_id number;
2397 is_notif_canceled VARCHAR2(2);
2398 l_doc_string varchar2(200);
2399 l_preparer_user_name wf_users.name%TYPE;
2400
2401 BEGIN
2402
2403 l_progress := 'Update_App_List_Resp_Success: 001- at beginning of function';
2404 IF (g_po_wf_debug = 'Y') THEN
2405 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2406 END IF;
2407
2408 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
2409 itemkey => itemkey,
2410 aname => 'RESPONDER_USER_ID',
2411 avalue => fnd_global.USER_ID);
2412
2413 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
2414 itemkey => itemkey,
2415 aname => 'RESPONDER_RESP_ID',
2416 avalue => fnd_global.RESP_ID);
2417
2418 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
2419 itemkey => itemkey,
2420 aname => 'RESPONDER_APPL_ID',
2421 avalue => fnd_global.RESP_APPL_ID);
2422
2423 l_progress := 'Update_App_List_Resp_Success: 002';
2424 IF (g_po_wf_debug = 'Y') THEN
2425 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2426 END IF;
2427
2428 OPEN c_group_id(itemtype, itemkey, 'PO_REQ_APPROVE', 'PO_REQ_INVALID_FORWARD', 'UNABLE_TO_RESERVE', 'PO_REQ_APPROVE_SIMPLE');
2429
2430 FETCH c_group_id INTO l_group_id;
2431 CLOSE c_group_id;
2432
2433 l_progress := 'Update_App_List_Resp_Success: 003';
2434 IF (g_po_wf_debug = 'Y') THEN
2435 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2436 END IF;
2437
2438 /* start of fix for 1263201 */
2439 OPEN c_canceled_notif (l_group_id);
2440 FETCH c_canceled_notif into is_notif_canceled;
2441
2442
2443 -- check if PO_REQ_APPROVE notification is canceled
2444 IF c_canceled_notif%FOUND THEN
2445
2446 CLOSE c_canceled_notif;
2447
2448 l_progress := 'Update_App_List_Resp_Success: 0031';
2449 IF (g_po_wf_debug = 'Y') THEN
2450 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2451 END IF;
2452
2453 l_group_id := NULL;
2454
2455 OPEN c_group_id(itemtype, itemkey, 'PO_REQ_REMINDER2', 'PO_REQ_INVALID_FORWARD_R1', 'UNABLE_TO_RESERVE', 'PO_REQ_APPROVE_SIMPLE');
2456 FETCH c_group_id INTO l_group_id;
2457 CLOSE c_group_id;
2458
2459 l_progress := 'Update_App_List_Resp_Success: 0032';
2460 IF (g_po_wf_debug = 'Y') THEN
2461 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2462 END IF;
2463
2464 OPEN c_canceled_notif (l_group_id);
2465 FETCH c_canceled_notif into is_notif_canceled;
2466
2467 -- check if PO_REQ_REMINDER2 notification is canceled
2468 IF c_canceled_notif%FOUND THEN
2469
2470 CLOSE c_canceled_notif;
2471
2472 l_progress := 'Update_App_List_Resp_Success: 0033';
2473 IF (g_po_wf_debug = 'Y') THEN
2474 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2475 END IF;
2476 l_group_id := NULL;
2477
2478 OPEN c_group_id(itemtype, itemkey, 'PO_REQ_REMINDER1', 'PO_REQ_INVALID_FORWARD_R2','UNABLE_TO_RESERVE', 'PO_REQ_APPROVE_SIMPLE');
2479 FETCH c_group_id INTO l_group_id;
2480 CLOSE c_group_id;
2481
2482 l_progress := 'Update_App_List_Resp_Success: 0034';
2483 IF (g_po_wf_debug = 'Y') THEN
2484 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2485 END IF;
2486
2487 OPEN c_canceled_notif (l_group_id);
2488 FETCH c_canceled_notif into is_notif_canceled;
2489
2490 l_progress := 'Update_App_List_Resp_Success: 00341';
2491 IF (g_po_wf_debug = 'Y') THEN
2492 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2493 END IF;
2494
2495 -- check if PO_REQ_REMINDER1 notification is canceled
2496
2497 IF c_canceled_notif%FOUND THEN
2498
2499 CLOSE c_canceled_notif;
2500 l_progress := 'Update_App_List_Resp_Success: 00342';
2501 l_group_id := NULL;
2502
2503 ELSE -- PO_REQ_REMINDER1 notification is not canceled
2504
2505 CLOSE c_canceled_notif;
2506
2507 END IF; -- check if PO_REQ_REMINDER2 notification is canceled
2508
2509 ELSE -- PO_REQ_REMINDER2 notification is not canceled
2510
2511 CLOSE c_canceled_notif;
2512
2513 END IF; -- check if PO_REQ_REMINDER2 notification is canceled
2514
2515 ELSE -- PO_REQ_APPROVE notifications is not canceled
2516
2517 CLOSE c_canceled_notif;
2518
2519 END IF; -- checked if the PO_REQ_APPROVE notifications is canceled
2520
2521 /* end of fix for 1263201 */
2522
2523 l_progress := 'Update_App_List_Resp_Success: 0035';
2524 IF (g_po_wf_debug = 'Y') THEN
2525 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2526 END IF;
2527
2528 IF l_group_id is NOT NULL THEN
2529 OPEN c_response(l_group_id);
2530 FETCH c_response INTO l_role, response;
2531 CLOSE c_response;
2532
2533 l_progress := 'Update_App_List_Resp_Success: 004';
2534 IF (g_po_wf_debug = 'Y') THEN
2535 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2536 END IF;
2537
2538 -- IF l_group_id is NOT NULL THEN
2539
2540 /* Bug 1257763 */
2541 SELECT wfn.responder, wfn.recipient_role, wfn.end_date
2542 INTO l_responder, l_recipient_role, responseEndDate
2543 FROM wf_notifications wfn
2544 WHERE wfn.notification_id = l_group_id;
2545
2546 /* csheu bug #1287135 use reponder value in wf_notification to find
2547 its orig_system_id from wf_users. If no matched rows found from
2548 wf_users then we will use l_recipient_role value from wf_notification
2549 to find its orig_system_id from wf_users instead.
2550 */
2551
2552 OPEN c_responderid(l_responder);
2553 FETCH c_responderid INTO responderId;
2554
2555 IF c_responderid%NOTFOUND THEN
2556
2557 CLOSE c_responderid;
2558 OPEN c_responderid(l_recipient_role);
2559 FETCH c_responderid INTO responderId;
2560 CLOSE c_responderid;
2561
2562 END IF;
2563
2564 IF (c_responderid%ISOPEN) THEN
2565 CLOSE c_responderid;
2566 END IF;
2567
2568 l_progress := 'Update_App_List_Resp_Success: 005';
2569 IF (g_po_wf_debug = 'Y') THEN
2570 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2571 END IF;
2572
2573 wf_engine.SetItemAttrNumber(itemtype => itemType,
2574 itemkey => itemkey,
2575 aname => 'RESPONDER_ID',
2576 avalue => responderId);
2577
2578 l_orig_system:= 'PER';
2579
2580 WF_DIRECTORY.GetUserName(l_orig_system,
2581 responderId,
2582 l_responder_user_name,
2583 l_responder_disp_name);
2584
2585 l_progress := 'Update_App_List_Resp_Success: 007 -' || l_responder_user_name;
2586 IF (g_po_wf_debug = 'Y') THEN
2587 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2588 END IF;
2589
2590 wf_engine.SetItemAttrText( itemtype => itemType,
2591 itemkey => itemkey,
2592 aname => 'RESPONDER_USER_NAME' ,
2593 avalue => l_responder_user_name);
2594
2595 wf_engine.SetItemAttrText( itemtype => itemType,
2596 itemkey => itemkey,
2597 aname => 'RESPONDER_DISPLAY_NAME' ,
2598 avalue => l_responder_disp_name);
2599
2600 l_progress := 'Update_App_List_Resp_Success: 008' ;
2601 IF (g_po_wf_debug = 'Y') THEN
2602 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2603 END IF;
2604
2605 IF (INSTR(response, 'FORWARD') > 0) THEN
2606 forwardToId := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2607 itemkey => itemkey,
2608 aname => 'FORWARD_TO_ID');
2609 END IF;
2610
2611 l_progress := 'Update_App_List_Resp_Success: 009' ;
2612 IF (g_po_wf_debug = 'Y') THEN
2613 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2614 END IF;
2615
2616
2617 END IF; -- c_group_id
2618 l_progress := 'Update_App_List_Resp_Success : 999';
2619 IF (g_po_wf_debug = 'Y') THEN
2620 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2621 END IF;
2622
2623 EXCEPTION
2624
2625 WHEN OTHERS THEN
2626 IF (c_group_id%ISOPEN) THEN
2627 CLOSE c_group_id;
2628 END IF;
2629 IF (c_response%ISOPEN) THEN
2630 CLOSE c_response;
2631 END IF;
2632
2633 wf_core.context('PO_APPROVAL_LIST_WF1S',
2634 'Update_App_List_Resp_Success',l_progress,sqlerrm);
2635
2636 RAISE;
2637 end;
2638
2639
2640
2641
2642
2643
2644 /* Bug# 1712121: kagarwal
2645 ** Desc: In bug#1394711 we changed the return type for function
2646 ** Update_Approval_List_Response from 'Activity Performed' to 'SUCCESS/FAILURE'.
2647 ** This changed was made to the API as well as the workflow.
2648 **
2649 ** Now the reqs created after applying this patch would work fine but the
2650 ** requisitions submitted for approval before applying this fix, which are still
2651 ** in process, get stuck when the users try to approve them.
2652 **
2653 ** In scenarios when we have to change the return type in wf, we should
2654 ** create a new API and leave the old one as it is. Now the workflow activity
2655 ** should be calling the new API. With this the new reqs will work fine as the
2656 ** new API will be returning the changed return types as expected by the new
2657 ** workflow definition and also the reqs submitted for approval before the fix
2658 ** will also work fine as the old workflow definition will be calling the old
2659 ** API which still returns the return types as expected by the old definition.
2660 **
2661 ** Created a new API 'Update_App_List_Resp_Success'. This API will return
2662 ** 'SUCCESS-FAILURE'. The workflow activity 'Update Approval List Response'
2663 ** has also been changed to call this new API.
2664 **
2665 ** Also reverted the change of bug# 1394711 in the old API
2666 ** 'Update_Approval_List_Response'. It will now return 'ACTIVITY_PERFORMED'
2667 ** as before the fix in bug# 1394711.
2668 */
2669
2670 procedure Update_App_List_Resp_Success(itemtype in varchar2,
2671 itemkey in varchar2,
2672 actid in number,
2673 funcmode in varchar2,
2674 resultout out NOCOPY varchar2) IS
2675
2676 l_progress VARCHAR2(1000) := '000';
2677 l_approver_id NUMBER := NULL;
2678 l_value VARCHAR2(2000);
2679 l_responder_id NUMBER := NULL;
2680 l_forward_to_id NUMBER := NULL;
2681 l_document_id NUMBER;
2682 l_document_type po_document_types.DOCUMENT_TYPE_CODE%TYPE;
2683 l_document_subtype po_document_types.DOCUMENT_SUBTYPE%TYPE;
2684 l_return_code NUMBER;
2685 l_orgid NUMBER;
2686 l_approval_list_header_id NUMBER:='';
2687 l_error_stack PO_APPROVALLIST_S1.ErrorStackType;
2688 E_UPDATE_RESPONSE_FAIL EXCEPTION;
2689 l_end_date DATE; -- notification end date
2690 l_note VARCHAR2(4000);
2691 l_doc_string varchar2(200);
2692 l_preparer_user_name wf_users.name%TYPE;
2693
2694 doc_manager_exception exception;
2695
2696 BEGIN
2697
2698 l_progress := 'Update_App_List_Resp_Success: 001- at beginning of function';
2699 IF (g_po_wf_debug = 'Y') THEN
2700 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2701 END IF;
2702
2703 IF (funcmode='RUN') THEN
2704
2705 get_approval_response(itemtype => itemtype,
2706 itemkey => itemkey,
2707 responderId => l_responder_id,
2708 response =>l_value,
2709 responseEndDate =>l_end_date,
2710 forwardToId => l_forward_to_id);
2711
2712 -- Context Setting revamp
2713 -- set_doc_mgr_context(itemtype, itemkey);
2714
2715 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2716 itemkey => itemkey,
2717 aname => 'ORG_ID');
2718
2719 IF l_orgid is NOT NULL THEN
2720
2721 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12 MOAC>
2722
2723 END IF;
2724
2725 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2726 itemkey => itemkey,
2727 aname => 'DOCUMENT_ID');
2728
2729 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2730 itemkey => itemkey,
2731 aname => 'DOCUMENT_TYPE');
2732
2733 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2734 itemkey => itemkey,
2735 aname => 'DOCUMENT_SUBTYPE');
2736
2737 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>itemtype,
2738 itemkey=>itemkey,
2739 aname=>'APPROVER_EMPID');
2740
2741 l_note := wf_engine.GetItemAttrText(itemtype => itemtype,
2742 itemkey => itemkey,
2743 aname => 'NOTE');
2744
2745 l_progress := 'Update_App_List_Resp_Success: 010 APP'||
2746 to_char(l_approver_id)||
2747 ' RES'||to_char(l_responder_id)||
2748 ' FWD'||to_char(l_forward_to_id);
2749 IF (g_po_wf_debug = 'Y') THEN
2750 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2751 END IF;
2752
2753 PO_APPROVALLIST_S1.update_approval_list_response(
2754 p_document_id=>l_document_id,
2755 p_document_type=>l_document_type,
2756 p_document_subtype=>l_document_subtype,
2757 p_itemtype=>itemtype,
2758 p_itemkey=>itemkey,
2759 p_approver_id=>l_approver_id,
2760 p_responder_id=>l_responder_id,
2761 p_forward_to_id=>l_forward_to_id,
2762 p_response=>l_value,
2763 p_response_date=>l_end_date,
2764 p_comments=>substrb(l_note,1,480), -- bug 3105327
2765 p_return_code=>l_return_code);
2766
2767 l_progress := 'Update_App_List_Resp_Success: 011'||to_char(l_return_code);
2768 IF (g_po_wf_debug = 'Y') THEN
2769 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2770 END IF;
2771
2772 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
2773
2774 /* rebuild since it is a forward */
2775 IF l_value in ('FORWARD', 'APPROVE_AND_FORWARD') THEN
2776
2777 l_progress := 'Update_App_List_Resp_Success: 012';
2778 IF (g_po_wf_debug = 'Y') THEN
2779 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2780 END IF;
2781 PO_APPROVALLIST_S1.rebuild_approval_list(
2782 p_document_id=>l_document_id,
2783 p_document_type=>l_document_type,
2784 p_document_subtype=>l_document_subtype,
2785 p_rebuild_code=>'FORWARD_RESPONSE',
2786 p_return_code=>l_return_code,
2787 p_error_stack=>l_error_stack,
2788 p_approval_list_header_id=>l_approval_list_header_id);
2789
2790 l_progress := 'Update_App_List_Resp_Success : 013'||to_char(l_return_code);
2791 IF (g_po_wf_debug = 'Y') THEN
2792 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2793 END IF;
2794
2795 IF l_return_code = PO_APPROVALLIST_S1.E_SUCCESS THEN
2796 wf_engine.SetItemAttrNumber(itemtype => itemType,
2797 itemkey => itemkey,
2798 aname => 'APPROVAL_LIST_HEADER_ID',
2799 avalue => l_approval_list_header_id);
2800
2801 resultout:='COMPLETE' || ':' || 'SUCCESS';
2802 RETURN;
2803
2804 /* Bug# 1394711
2805 ** Desc: The Update_Approval_List_Response() procedure raises exception
2806 ** when the rebuild_approval_list() fails and the approval workflow
2807 ** hangs. We need to handle the situation when the rebuild_approval_list()
2808 ** fails because of No approver found in order to return the Requisition
2809 ** to the preparer.
2810 **
2811 ** Changed the procedure Update_Approval_List_Response() to return FAILURE
2812 ** for the above condition or SUCCESS instead of ACTIVITY_PERFORMED.
2813 **
2814 ** The Requsition workflow also has been changed to handle the above.
2815 **
2816 ** Dependency: poxwfrqa.wft
2817 */
2818
2819 ELSIF l_return_code = PO_APPROVALLIST_S1.E_NO_ONE_HAS_AUTHORITY THEN
2820 resultout:='COMPLETE' || ':' || 'FAILURE';
2821 RETURN;
2822
2823 /* Bug# 2378775 */
2824
2825 ELSIF l_return_code in (PO_APPROVALLIST_S1.E_DOC_MGR_TIMEOUT,
2826 PO_APPROVALLIST_S1.E_DOC_MGR_NOMGR,
2827 PO_APPROVALLIST_S1.E_DOC_MGR_OTHER) THEN
2828
2829 set_doc_mgr_err(itemtype, itemkey, l_error_stack, l_return_code);
2830 raise doc_manager_exception;
2831
2832 END IF; --rebuild success
2833
2834 ELSE
2835 /* no need to rebuild for approve or reject actions */
2836 resultout:='COMPLETE' || ':' || 'SUCCESS';
2837 RETURN;
2838
2839 END IF; -- forward action
2840
2841 END IF; -- update success
2842
2843 l_progress := 'Update_App_List_Resp_Success : 999';
2844 IF (g_po_wf_debug = 'Y') THEN
2845 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
2846 END IF;
2847
2848 RAISE E_UPDATE_RESPONSE_FAIL;
2849 END IF; -- run mode
2850
2851 EXCEPTION
2852 WHEN doc_manager_exception THEN
2853 raise;
2854
2855 WHEN E_UPDATE_RESPONSE_FAIL THEN
2856
2857 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2858 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2859 wf_core.context('PO_APPROVAL_LIST_WF1S',
2860 'Update_App_List_Resp_Success E_FAILURE',
2861 l_progress,l_return_code,sqlerrm);
2862 -- wf_core.raise('Find_Approval_list E_FAILURE' || l_progress||sqlerrm);
2863
2864 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string,
2865 sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APP_LIST_RESP_SUCCESS');
2866
2867 RAISE;
2868
2869 WHEN OTHERS THEN
2870
2871 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2872 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2873 wf_core.context('PO_APPROVAL_LIST_WF1S',
2874 'Update_App_List_Resp_Success',l_progress,sqlerrm);
2875
2876 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string,
2877 sqlerrm, 'PO_APPROVAL_LIST_WF1S.UPDATE_APP_LIST_RESP_SUCCESS');
2878
2879 RAISE;
2880
2881 END Update_App_List_Resp_Success;
2882
2883 -- Create Attachment from Information Template
2884 -- This procedure calls por_ift_info_pkg package
2885 -- to create attachments from information template
2886 --
2887 procedure Create_Attach_Info_Temp(itemtype in varchar2,
2888 itemkey in varchar2,
2889 actid in number,
2890 funcmode in varchar2,
2891 resultout out NOCOPY varchar2) IS
2892 l_req_header_id NUMBER:='';
2893 l_progress VARCHAR2(100) := '000';
2894
2895 l_doc_string varchar2(200);
2896 l_preparer_user_name varchar2(100);
2897
2898 l_org_id number;
2899 l_preparer_language varchar2(10);
2900
2901 BEGIN
2902
2903 l_progress := '000';
2904
2905 IF (funcmode='RUN') THEN
2906
2907 -- Set the multi-org context
2908
2909 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2910 itemkey => itemkey,
2911 aname => 'ORG_ID');
2912
2913 IF l_org_id is NOT NULL THEN
2914
2915 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
2916
2917 END IF;
2918
2919 l_req_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2920 itemkey => itemkey,
2921 aname => 'DOCUMENT_ID');
2922
2923
2924 --Bug 3800933. Get the preparer language and pass to info template attachment
2925 l_preparer_language := po_wf_util_pkg.GetItemAttrText ( ItemType => itemtype,
2926 ItemKey => itemkey,
2927 aname => 'PREPARER_LANGUAGE');
2928
2929 l_progress := '001';
2930
2931 por_ift_info_pkg.add_info_template_attachment(l_req_header_id, 33, l_preparer_language);
2932
2933 l_progress := '002';
2934
2935 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
2936 return;
2937
2938 END IF; -- run mode
2939 l_progress := '999';
2940
2941 EXCEPTION
2942 WHEN OTHERS THEN
2943 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2944 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2945 wf_core.context('PO_APPROVAL_LIST_WF1S','Create_Attach_Info_Temp',l_progress,sqlerrm);
2946 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.CREATE_ATTACH_INFO_TEMP');
2947 RAISE;
2948
2949 END Create_Attach_Info_Temp;
2950
2951 --
2952 PROCEDURE set_doc_mgr_context (itemtype VARCHAR2, itemkey VARCHAR2) is
2953
2954 l_user_id number;
2955 l_responsibility_id number;
2956 l_application_id number;
2957
2958 l_progress varchar2(200);
2959
2960 BEGIN
2961
2962 l_user_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
2963 itemkey => itemkey,
2964 aname => 'USER_ID');
2965 --
2966 l_application_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
2967 itemkey => itemkey,
2968 aname => 'APPLICATION_ID');
2969 --
2970 l_responsibility_id := wf_engine.GetItemAttrNumber ( itemtype => itemtype,
2971 itemkey => itemkey,
2972 aname => 'RESPONSIBILITY_ID');
2973
2974 /* Set the context for the doc manager */
2975 -- Bug 4290541, replace apps init with set doc mgr context
2976 -- Context Setting revamp
2977 -- PO_REQAPPROVAL_INIT1.Set_doc_mgr_context(itemtype, itemkey);
2978
2979 l_progress := 'set_doc_mgr_context. USER_ID= ' || to_char(l_user_id)
2980 || ' APPLICATION_ID= ' || to_char(l_application_id) ||
2981 'RESPONSIBILITY_ID= ' || to_char(l_responsibility_id);
2982
2983 EXCEPTION
2984
2985 WHEN OTHERS THEN
2986 wf_core.context('PO_APPROVAL_LIST_WFS1','set_doc_mgr_context',l_progress);
2987 raise;
2988
2989 END set_doc_mgr_context;
2990 --
2991
2992 /* Bug# 2378775: kagarwal
2993 ** Desc: Added new procedure set_doc_mgr_err to initialize the document
2994 ** manager error number and system admin error message for the POERROR
2995 ** workflow.
2996 */
2997
2998 PROCEDURE set_doc_mgr_err(itemtype varchar2,
2999 itemkey varchar2,
3000 p_error_stack PO_APPROVALLIST_S1.ErrorStackType,
3001 p_return_code number) is
3002
3003 l_message_stack PO_APPROVALLIST_S1.MessageStackType;
3004 l_err_code NUMBER;
3005 l_err_index NUMBER;
3006 l_progress varchar2(200);
3007
3008 BEGIN
3009 l_progress := 'set_doc_mgr_err: 001';
3010 IF (g_po_wf_debug = 'Y') THEN
3011 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3012 END IF;
3013
3014 IF p_return_code = PO_APPROVALLIST_S1.E_DOC_MGR_TIMEOUT THEN
3015 PO_REQAPPROVAL_ACTION.doc_mgr_err_num := 1;
3016 ELSIF p_return_code = PO_APPROVALLIST_S1.E_DOC_MGR_NOMGR THEN
3017 PO_REQAPPROVAL_ACTION.doc_mgr_err_num := 2;
3018 ELSIF p_return_code = PO_APPROVALLIST_S1.E_DOC_MGR_OTHER THEN
3019 PO_REQAPPROVAL_ACTION.doc_mgr_err_num := 3;
3020 END IF;
3021
3022 l_progress := 'set_doc_mgr_err: 020: error number = '||
3023 to_char(PO_REQAPPROVAL_ACTION.doc_mgr_err_num);
3024 IF (g_po_wf_debug = 'Y') THEN
3025 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3026 END IF;
3027
3028 IF (p_error_stack.COUNT > 0) THEN
3029 PO_APPROVALLIST_S1.retrieve_messages(p_error_stack,
3030 l_err_code,
3031 l_message_stack);
3032
3033 IF (l_err_code = PO_APPROVALLIST_S1.E_SUCCESS) THEN
3034 l_err_index := p_error_stack.LAST;
3035
3036 If (l_err_index is NOT NULL) THEN
3037 PO_REQAPPROVAL_ACTION.sysadmin_err_msg:= l_message_stack(l_err_index);
3038
3039 l_progress := 'set_doc_mgr_err: 050: error msg = '||
3040 PO_REQAPPROVAL_ACTION.sysadmin_err_msg;
3041 IF (g_po_wf_debug = 'Y') THEN
3042 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3043 END IF;
3044
3045 End If;
3046
3047 END IF;
3048
3049 END IF;
3050
3051 l_progress := 'set_doc_mgr_err: 999';
3052 IF (g_po_wf_debug = 'Y') THEN
3053 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3054 END IF;
3055
3056 EXCEPTION
3057 WHEN OTHERS THEN
3058 wf_core.context('PO_APPROVAL_LIST_WFS1','set_doc_mgr_err',l_progress);
3059 raise;
3060 END;
3061
3062 /* Bug# 2684757: kagarwal
3063 ** Desc: Added new wf api to insert null action before
3064 ** Reserving a Requisition, if the null action does not exists.
3065 ** Otherwise the Reserve action is not recorded.
3066 */
3067 procedure Insert_Res_Action_History(itemtype in varchar2,
3068 itemkey in varchar2,
3069 actid in number,
3070 funcmode in varchar2,
3071 resultout out NOCOPY varchar2) IS
3072
3073 l_progress VARCHAR2(100) := '000';
3074 l_approver_id NUMBER:='';
3075 l_approval_path_id NUMBER:='';
3076 l_req_header_id NUMBER:='';
3077
3078 l_doc_string varchar2(200);
3079 l_preparer_user_name varchar2(100);
3080
3081 l_org_id number;
3082 BEGIN
3083
3084 l_progress := 'Insert_Res_Action_History: 001';
3085 IF (g_po_wf_debug = 'Y') THEN
3086 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3087 END IF;
3088
3089 IF (funcmode='RUN') THEN
3090
3091 l_approver_id := wf_engine.GetItemAttrNumber(itemtype=>itemtype,
3092 itemkey=>itemkey,
3093 aname=>'APPROVER_EMPID');
3094
3095 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3096 itemkey => itemkey,
3097 aname => 'APPROVAL_PATH_ID');
3098
3099 l_req_header_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3100 itemkey => itemkey,
3101 aname => 'DOCUMENT_ID');
3102
3103 -- Set the multi-org context
3104
3105 l_org_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3106 itemkey => itemkey,
3107 aname => 'ORG_ID');
3108
3109 IF l_org_id is NOT NULL THEN
3110
3111 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12 MOAC>
3112
3113 END IF;
3114
3115 PO_APPROVAL_LIST_HISTORY_SV.Reserve_Action_History(
3116 x_req_header_id=>l_req_header_id,
3117 x_approval_path_id=>l_approval_path_id,
3118 x_approver_id =>l_approver_id);
3119
3120 l_progress := 'Insert_Res_Action_History: 005 - Reserve_Action_History';
3121 IF (g_po_wf_debug = 'Y') THEN
3122 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3123 END IF;
3124
3125 resultout:='COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
3126 return;
3127
3128 END IF; -- run mode
3129
3130 l_progress := 'Insert_Res_Action_History: 999';
3131 IF (g_po_wf_debug = 'Y') THEN
3132 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3133 END IF;
3134
3135 EXCEPTION
3136 WHEN OTHERS THEN
3137 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3138 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType,itemkey);
3139 wf_core.context('PO_APPROVAL_LIST_WF1S','Insert_Res_Action_History',
3140 l_progress,sqlerrm);
3141 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name,
3142 l_doc_string, sqlerrm, 'PO_APPROVAL_LIST_WF1S.INSERT_ACTION_HISTORY');
3143 RAISE;
3144
3145 END Insert_Res_Action_History;
3146
3147
3148 END PO_APPROVAL_LIST_WF1S;