[Home] [Help]
PACKAGE BODY: APPS.PO_REQAPPROVAL_ACTION
Source
1 PACKAGE BODY PO_REQAPPROVAL_ACTION AS
2 /* $Header: POXWPA4B.pls 120.27.12020000.3 2013/02/10 15:40:21 vegajula ship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6
7 /*=======================================================================+
8 | FILENAME
9 | xxx.sql
10 |
11 | DESCRIPTION
12 | PL/SQL body for package: PO_REQAPPROVAL_ACTION
13 |
14 | NOTES Ben Chihaoui Created 6/15/97
15 | MODIFIED Wlau Support for Kanban Execution 8/28/97
16 |
17 *=======================================================================*/
18
19
20 -- The following are local/Private procedure that support the workflow APIs:
21
22
23 PROCEDURE Invoke_Acknowledge_PO_WF(itemtype in varchar2, itemkey in varchar2);
24 --
25
26 FUNCTION StateCheckApprove(itemtype in varchar2, itemkey in varchar2) RETURN VARCHAR2;
27
28 --
29 FUNCTION StateCheckReject(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
30
31 --
32 FUNCTION DocCompleteCheck(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
33
34 --Bug 12944203
35 /*
36 PROCEDURE InsertHistForOwnerApprove (itemtype VARCHAR2,
37 itemkey VARCHAR2,
38 p_document_id NUMBER,
39 p_document_type VARCHAR2,
40 p_document_subtype VARCHAR2);
41 */
42
43 --
44 FUNCTION ApproveDoc(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
45
46 --
47 FUNCTION ApproveAndForward(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
48
49 --
50 FUNCTION ForwardDocInProcess(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
51
52 --
53 FUNCTION ForwardDocPreApproved(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
54
55 --
56 FUNCTION RejectDoc(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
57
58 --
59 FUNCTION VerifyAuthority(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
60
61 --
62 FUNCTION OpenDocumentState(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2;
63
64 --
65 /* Bug# 2234341 */
66 FUNCTION ReserveDoc(itemtype VARCHAR2, itemkey VARCHAR2,
67 p_override_funds VARCHAR2 default 'N') RETURN VARCHAR2;
68
69 --
70
71 -- <ENCUMBRANCE FPJ START>
72 -- Adding an autonomous call
73
74 PROCEDURE ReserveAutonomous(
75 p_doc_type IN VARCHAR2
76 , p_doc_subtype IN VARCHAR2
77 , p_doc_id IN NUMBER
78 , p_override_funds IN VARCHAR2
79 , p_employee_id IN NUMBER
80 , x_po_return_code OUT NOCOPY VARCHAR2
81 , x_online_report_id OUT NOCOPY NUMBER
82 );
83
84 PROCEDURE po_submission_check_autonomous(
85 p_document_type IN VARCHAR2
86 , p_document_subtype IN VARCHAR2
87 , p_document_id IN NUMBER
88 , p_check_asl IN BOOLEAN
89 , p_draft_id IN NUMBER:= -1 -- CLM Aprvl
90 , x_return_status OUT NOCOPY VARCHAR2
91 , x_sub_check_status OUT NOCOPY VARCHAR2
92 , x_msg_data OUT NOCOPY VARCHAR2
93 , x_online_report_id OUT NOCOPY NUMBER
94 );
95
96 -- <ENCUMBRANCE FPJ END>
97
98 PROCEDURE get_online_report_text(itemtype VARCHAR2, itemkey VARCHAR2, p_online_report_id NUMBER);
99
100 -- Bug 3536831: Added get_advisory_warning(), and created set_report_text_attr()
101 -- Both get_advisory_warning and get_online_report_text will now call set_report_text_attr.
102
103 PROCEDURE get_advisory_warning(
104 itemtype IN VARCHAR2
105 , itemkey IN VARCHAR2
106 , p_online_report_id IN NUMBER
107 , p_warning_header_text IN VARCHAR2
108 );
109
110 PROCEDURE set_report_text_attr(
111 itemtype IN VARCHAR2
112 , itemkey IN VARCHAR2
113 , p_online_report_id IN NUMBER
114 , p_attribute IN VARCHAR2
115 , p_header_text IN VARCHAR2 DEFAULT NULL
116 );
117
118
119 -- <Doc Manager Rewrite 11.5.11 Start>
120
121 PROCEDURE ApproveAutonomous(
122 p_document_id IN NUMBER
123 , p_document_type IN VARCHAR2
124 , p_document_subtype IN VARCHAR2
125 , p_note IN VARCHAR2
126 , p_approval_path_id IN NUMBER
127 , x_return_status OUT NOCOPY VARCHAR2
128 , x_exception_msg OUT NOCOPY VARCHAR2
129 );
130
131 PROCEDURE RejectAutonomous(
132 p_document_id IN NUMBER
133 , p_draft_id IN NUMBER -- CLM Apprvl
134 , p_document_type IN VARCHAR2
135 , p_document_subtype IN VARCHAR2
136 , p_note IN VARCHAR2
137 , p_approval_path_id IN NUMBER
138 , x_return_status OUT NOCOPY VARCHAR2
139 , x_return_code OUT NOCOPY VARCHAR2
140 , x_exception_msg OUT NOCOPY VARCHAR2
141 , x_online_report_id OUT NOCOPY NUMBER
142 );
143
144 PROCEDURE ForwardAutonomous(
145 p_document_id IN NUMBER
146 , p_document_type IN VARCHAR2
147 , p_document_subtype IN VARCHAR2
148 , p_new_doc_status IN VARCHAR2
149 , p_note IN VARCHAR2
150 , p_approval_path_id IN NUMBER
151 , p_forward_to_id IN NUMBER
152 , x_return_status OUT NOCOPY VARCHAR2
153 , x_exception_msg OUT NOCOPY VARCHAR2
154 );
155
156 PROCEDURE AutoUpdateCloseAutonomous(
157 p_document_id IN NUMBER
158 , p_document_type IN VARCHAR2
159 , p_document_subtype IN VARCHAR2
160 , x_return_status OUT NOCOPY VARCHAR2
161 , x_exception_msg OUT NOCOPY VARCHAR2
162 , x_return_code OUT NOCOPY VARCHAR2
163 );
164
165 -- <Doc Manager Rewrite 11.5.11 End>
166
167 --
168 PROCEDURE set_doc_mgr_context(itemtype VARCHAR2, itemkey VARCHAR2);
169 PROCEDURE set_responder_doc_mgr_context(itemtype VARCHAR2, itemkey VARCHAR2);
170
171 /***************************************************************************************/
172
173 PROCEDURE Invoke_Acknowledge_PO_WF(itemtype in varchar2, itemkey in varchar2)
174 is
175 x_orig_system varchar2(12);
176 contact_user_name varchar2(60);
177 contact_display_name varchar2(240);
178 l_ItemType VARCHAR2(100) := 'POSPOACK';
179 l_ItemKey VARCHAR2(240) ;
180 Document_id number;
181 x_document_type varchar2(80);
182 x_doc_revision number;
183 x_vendor_contact_id number;
184 x_vendor_user_id number := NULL;
185 x_contact_user_name varchar2(240);
186 x_contact_display_name varchar2(240);
187 x_acceptance_due_date date;
188 x_acceptance_required_flag varchar2(1);
189 x_minutes_to_acceptance number;
190 x_item_exists number;
191 x_progress varchar2(4) := '000';
192
193 cursor vendor_contacts (supplier_contact_id varchar2) is
194 select USER_ID from fnd_user where supplier_id = supplier_contact_id;
195 begin
196
197 -- check if web suppliers is installed - return FALSE if not.
198
199 Document_Id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
200 itemkey => itemkey,
201 aname => 'DOCUMENT_ID');
202
203 x_Document_type := wf_engine.GetItemAttrText ( itemtype => itemtype,
204 itemkey => itemkey,
205 aname => 'DOCUMENT_TYPE');
206
207 select vendor_contact_id, nvl(acceptance_required_flag, 'N'), acceptance_due_date, nvl(revision_num, 0)
208 into x_vendor_contact_id, x_acceptance_required_flag, x_acceptance_due_date, x_doc_revision
209 from po_headers
210 where po_header_id = document_id;
211
212 if x_vendor_contact_id is not null then
213
214 -- get the vendor contact user name.
215
216 open vendor_contacts(x_vendor_contact_id);
217
218 fetch vendor_contacts into x_vendor_user_id;
219
220 x_orig_system:= 'FND_USR';
221
222 WF_DIRECTORY.GetUserName( x_orig_system,
223 x_vendor_user_id,
224 x_contact_user_name,
225 x_contact_display_name);
226
227 if ( x_contact_user_name is not null ) then
228
229 l_itemkey := 'POS_ACK_' || to_char (document_id) || '_' || to_char(nvl(x_doc_revision, 0));
230
231 -- Check if WF already exists
232
233 select count(*) into x_item_exists
234 from wf_items
235 where item_type = 'POSPOACK'
236 and item_key = l_itemkey;
237
238 if nvl(x_item_exists, 0) <> 0 then
239 begin
240 -- abort if process still active.
241 wf_engine.abortprocess ('POSPOACK', l_itemkey);
242 exception
243 when others then
244 null;
245 end;
246
247 -- purge the workflow
248 wf_purge.items( 'POSPOACK', l_itemkey);
249 end if;
250
251 wf_engine.createProcess ( ItemType => l_ItemType,
252 ItemKey => l_ItemKey,
253 Process => 'MAIN_PROCESS');
254
255 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
256 itemkey => l_itemkey,
257 aname => 'DOCUMENT_ID',
258 avalue => document_id);
259
260 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
261 itemkey => l_itemkey,
262 aname => 'DOCUMENT_TYPE_CODE',
263 avalue => x_document_type);
264
265 wf_engine.SetItemAttrText ( itemtype => l_itemtype,
266 itemkey => l_itemkey,
267 aname => 'SUPPLIER_USER_NAME',
268 avalue => x_contact_user_name);
269
270 wf_engine.SetItemAttrDate ( itemtype => l_itemtype,
271 itemkey => l_itemkey,
272 aname => 'ACCEPTANCE_DUE_DATE',
273 avalue => x_acceptance_due_date);
274
275 -- set item owner.
276 wf_engine.setitemowner ( l_itemtype, l_itemkey, x_contact_user_name);
277
278 begin
279 select (trunc(nvl(x_acceptance_due_date, sysdate)) - trunc(sysdate)) * 60
280 into x_minutes_to_acceptance
281 from sys.dual;
282
283 if x_minutes_to_acceptance is not null then
284
285 wf_engine.SetItemAttrNumber ( itemtype => l_itemtype,
286 itemkey => l_itemkey,
287 aname => 'NUM_MINUTES_TO_ACCEPTANCE',
288 avalue => x_minutes_to_acceptance);
289 end if;
290 exception
291 when others then
292 null;
293 end;
294
295 wf_engine.StartProcess ( ItemType => l_ItemType,
296 ItemKey => l_ItemKey );
297 end if;
298 end if;
299
300 EXCEPTION
301
302 WHEN OTHERS THEN
303 WF_CORE.context('PO_REQAPPROVAL_ACTION' , 'Invoke_Acknowledge_PO_WF', itemtype, itemkey, x_progress);
304 RAISE;
305
306 end;
307
308 /***************************************************************************************/
309
310 procedure State_Check_approve( itemtype in varchar2,
311 itemkey in varchar2,
312 actid in number,
313 funcmode in varchar2,
314 resultout out NOCOPY varchar2 ) is
315
316 x_progress varchar2(100);
317 x_resultout varchar2(30);
318
319 l_doc_mgr_return_val VARCHAR2(1);
320
321 l_doc_string varchar2(200);
322 l_preparer_user_name varchar2(100);
323 doc_manager_exception exception;
324
325 l_doc_type varchar2(30);
326 l_orgid number;
327
328 --Mod Project
329 l_draft_id number;
330 l_is_mod varchar2(1);
331 l_mod_status varchar2(30);
332
333 BEGIN
334
335 x_progress := 'PO_REQAPPROVAL_ACTION.State_Check_approve: 01';
336 IF (g_po_wf_debug = 'Y') THEN
337 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
338 END IF;
339
340
341 -- Do nothing in cancel or timeout mode
342 --
343 if (funcmode <> wf_engine.eng_run) then
344
345 resultout := wf_engine.eng_null;
346 return;
347
348 end if;
349
350 /* Set the Doc manager context */
351 -- Context Fixing revamp
352 --set_doc_mgr_context(itemtype, itemkey);
353
354 /* RG: bug fix 2424044
355 code has been changed from doc mgr call to pl/sql call
356 apps_initialize internally calls set_org_context to org_id
357 that is not necessarily same as the org_id on the document */
358
359 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
360 itemkey => itemkey,
361 aname => 'ORG_ID');
362
363 IF l_orgid is NOT NULL THEN
364 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
365 END IF;
366
367 /* begin code to branch to check document in plsql */
368
369 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
370 itemkey => itemkey,
371 aname => 'DOCUMENT_TYPE');
372
373 -- Mod Project
374 if l_doc_type = 'PA' or l_doc_type = 'PO' then
375 l_draft_id := po_wf_util_pkg. GetItemAttrNumber(itemtype => itemtype,
376 itemkey => itemkey,
377 aname => 'DRAFT_ID');
378 If l_draft_id is Null Then
379 l_draft_id := -1;
380 End If;
381 if l_draft_id <> -1 then
382 select nvl(status, 'DRAFT') into l_mod_status
383 from po_drafts
384 where draft_id = l_draft_id;
385 if (not (l_mod_status = 'DRAFT' or
386 l_mod_status = 'IN PROCESS' or
387 l_mod_status = 'REJECTED' or
388 l_mod_status = 'PRE-APPROVED')) then
389 resultout := wf_engine.eng_completed || ':' || 'N';
390 x_resultout := 'N';
391 else
392 resultout := wf_engine.eng_completed || ':' || 'Y';
393 x_resultout := 'Y';
394 end if;
395 x_progress := 'PO_REQAPPROVAL_ACTION.State_Check_approve: MOD flow. RESULT= ' || x_resultout;
396 IF (g_po_wf_debug = 'Y') THEN
397 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
398 END IF;
399 return;
400 end if;
401 end if;
402 -- Mod Project
403
404 if(l_doc_type = 'PA' or l_doc_type = 'PO' or l_doc_type = 'RELEASE') then
405 x_resultout := PO_APPROVAL_ACTION.po_state_check_approve(itemtype, itemkey, l_doc_type);
406 resultout := wf_engine.eng_completed || ':' || x_resultout;
407 return;
408 elsif (l_doc_type = 'REQUISITION') then
409 x_resultout := PO_APPROVAL_ACTION.req_state_check_approve(itemtype, itemkey);
410 resultout := wf_engine.eng_completed || ':' || x_resultout;
411 return;
412 end if;
413
414 /* end code to branch to check document in plsql */
415
416 l_doc_mgr_return_val := StateCheckApprove(itemtype, itemkey);
417
418 IF l_doc_mgr_return_val = 'Y' THEN
419
420 resultout := wf_engine.eng_completed || ':' || 'Y';
421 x_resultout := 'Y';
422
423 ELSIF l_doc_mgr_return_val = 'N' THEN
424
425 resultout := wf_engine.eng_completed || ':' || 'N';
426 x_resultout := 'N';
427
428 ELSIF l_doc_mgr_return_val = 'F' THEN
429
430 /* This will force the transition in the Workflow to be "Default" */
431 raise doc_manager_exception;
432
433 END IF;
434
435
436 x_progress := 'PO_REQAPPROVAL_ACTION.State_Check_approve: 02. RESULT= ' || x_resultout;
437 IF (g_po_wf_debug = 'Y') THEN
438 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
439 END IF;
440
441
442 EXCEPTION
443 WHEN doc_manager_exception THEN
444 raise;
445 WHEN OTHERS THEN
446 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
447 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
448 WF_CORE.context('PO_REQAPPROVAL_ACTION' , 'state_check_approve', itemtype, itemkey, x_progress);
449 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.STATE_CHECK_APPROVE');
450 RAISE;
451
452
453 END State_Check_approve;
454
455
456 --
457 -- State_Check_reject
458 -- Is the state of the document compatible with the reject action.
459 --
460 procedure State_Check_reject( itemtype in varchar2,
461 itemkey in varchar2,
462 actid in number,
463 funcmode in varchar2,
464 resultout out NOCOPY varchar2 ) is
465 x_progress varchar2(100);
466 x_resultout varchar2(30);
467
468 l_doc_string varchar2(200);
469 l_preparer_user_name varchar2(100);
470
471 l_doc_mgr_return_val VARCHAR2(1);
472 doc_manager_exception exception;
473
474 BEGIN
475
476 x_progress := 'PO_REQAPPROVAL_ACTION.State_Check_reject: 01';
477 IF (g_po_wf_debug = 'Y') THEN
478 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
479 END IF;
480
481
482 -- Do nothing in cancel or timeout mode
483 --
484 if (funcmode <> wf_engine.eng_run) then
485
486 resultout := wf_engine.eng_null;
487 return;
488
489 end if;
490
491 /* Set the Doc manager context */
492 -- Context Setting revamp
493 -- set_doc_mgr_context(itemtype, itemkey);
494
495 l_doc_mgr_return_val := StateCheckReject(itemtype, itemkey);
496
497 IF l_doc_mgr_return_val = 'Y' THEN
498
499 resultout := wf_engine.eng_completed || ':' || 'Y';
500 x_resultout := 'Y';
501
502 ELSIF l_doc_mgr_return_val = 'N' THEN
503
504 resultout := wf_engine.eng_completed || ':' || 'N';
505 x_resultout := 'N';
506
507 ELSIF l_doc_mgr_return_val = 'F' THEN
508
509 /* This will force the transition in the Workflow to be "Default" */
510 x_resultout := 'F';
511 raise doc_manager_exception;
512
513 END IF;
514
515
516 x_progress := 'PO_REQAPPROVAL_ACTION.State_Check_reject: 02. RESULT= ' || x_resultout;
517 IF (g_po_wf_debug = 'Y') THEN
518 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
519 END IF;
520
521
522 EXCEPTION
523
524 WHEN doc_manager_exception THEN
525 raise;
526 WHEN OTHERS THEN
527 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
528 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
529 WF_CORE.context('PO_REQAPPROVAL_ACTION', 'State_Check_reject', itemtype, itemkey, x_progress);
530 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.STATE_CHECK_REJECT');
531 RAISE;
532
533 END State_Check_reject;
534
535 --
536 -- Doc_complete_check
537 -- Is the doc complete (all quantities match, at least one line and one distribution...)
538 --
539 procedure Doc_complete_check(itemtype in varchar2,
540 itemkey in varchar2,
541 actid in number,
542 funcmode in varchar2,
543 resultout out NOCOPY varchar2 ) is
544
545 x_progress varchar2(100);
546 x_resultout varchar2(30);
547 l_doc_mgr_return_val VARCHAR2(1);
548
549 l_doc_string varchar2(200);
550 l_preparer_user_name varchar2(100);
551 doc_manager_exception exception;
552
553 l_doc_type varchar2(30);
554 l_orgid number;
555
556 --<SUBMISSION CHECK FPI>
557 l_sub_check_status varchar2(1);
558
559 BEGIN
560
561 x_progress := 'PO_REQAPPROVAL_ACTION.Doc_complete_check: 01';
562 IF (g_po_wf_debug = 'Y') THEN
563 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
564 END IF;
565
566
567 -- Do nothing in cancel or timeout mode
568 --
569 if (funcmode <> wf_engine.eng_run) then
570
571 resultout := wf_engine.eng_null;
572 return;
573
574 end if;
575
576 /* Set the Doc manager context */
577 -- Context Setting revamp
578 -- set_doc_mgr_context(itemtype, itemkey);
579
580 --<SUBMISSION CHECK FPI START>
581 /* RG: bug fix 2424044
582 code has been changed from doc mgr call to pl/sql call
583 apps_initialize internally calls set_org_context to org_id
584 that is not necessarily same as the org_id on the document */
585
586 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
587 itemkey => itemkey,
588 aname => 'ORG_ID');
589
590 IF l_orgid is NOT NULL THEN
591 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
592 END IF;
593 --<SUBMISSION CHECK FPI END>
594
595
596 /* begin code to branch to check document in plsql */
597 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
598 itemkey => itemkey,
599 aname => 'DOCUMENT_TYPE');
600 if(l_doc_type = 'REQUISITION') then
601 x_resultout := PO_APPROVAL_ACTION.req_complete_check(itemtype, itemkey);
602 resultout := wf_engine.eng_completed || ':' || x_resultout;
603 return;
604 end if;
605 /* end code to branch to check document in plsql */
606
607 -- New call in FPI
608 l_sub_check_status := DocCompleteCheck(itemtype, itemkey);
609
610 IF l_sub_check_status = FND_API.G_RET_STS_SUCCESS THEN
611
612 /*Bug 11727653: BYPASSING MULTIPLE SUBMISSION CHECKS IN WORKFLOW
613 - setting the bypass flag to Y once submission checks are successful*/
614 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
615 itemkey => itemkey,
616 aname => 'BYPASS_CHECKS_FLAG',
617 avalue => 'Y');
618
619 resultout := wf_engine.eng_completed || ':' || 'Y';
620 x_resultout := 'Y';
621
622 ELSIF l_sub_check_status = FND_API.G_RET_STS_ERROR THEN
623
624 resultout := wf_engine.eng_completed || ':' || 'N';
625 x_resultout := 'N';
626
627 ELSIF l_sub_check_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
628
629 raise FND_API.G_EXC_UNEXPECTED_ERROR;
630 END IF;
631 --<SUBMISSION CHECK FPI END>
632
633 x_progress := 'PO_REQAPPROVAL_ACTION.Doc_complete_check: 02. RESULT= ' || x_resultout;
634 IF (g_po_wf_debug = 'Y') THEN
635 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
636 END IF;
637
638
639 EXCEPTION
640
641 WHEN doc_manager_exception THEN
642 raise;
643 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
644 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
645 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
646 WF_CORE.context('PO_REQAPPROVAL_ACTION', 'Doc_complete_check', itemtype, itemkey, x_progress);
647 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.DOC_COMPLETE_CHECK');
648 RAISE;
649
650 WHEN OTHERS THEN
651 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
652 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
653 WF_CORE.context('PO_REQAPPROVAL_ACTION', 'Doc_complete_check', itemtype, itemkey, x_progress);
654 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.DOC_COMPLETE_CHECK');
655 RAISE;
656
657 END Doc_complete_check;
658
659 --
660 -- Approve_doc
661 -- Approve the document
662 --
663 procedure Approve_doc(itemtype in varchar2,
664 itemkey in varchar2,
665 actid in number,
666 funcmode in varchar2,
667 resultout out NOCOPY varchar2 ) is
668
669 x_progress varchar2(100);
670 x_vendor_contact_id number;
671 x_doc_id number;
672 x_resultout varchar2(30);
673
674 l_doc_mgr_return_val VARCHAR2(1);
675
676 l_doc_string varchar2(200);
677 l_preparer_user_name varchar2(100);
678 doc_manager_exception exception;
679
680 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
681 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
682 l_document_id NUMBER;
683 l_approver_id NUMBER;
684 l_preparer_id NUMBER;
685 l_insert_owner_app Boolean := true;
686 l_is_ame_used varchar2(10);
687
688 BEGIN
689
690 x_progress := 'PO_REQAPPROVAL_ACTION.Approve_doc: 01';
691 IF (g_po_wf_debug = 'Y') THEN
692 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
693 END IF;
694
695
696 -- Do nothing in cancel or timeout mode
697 --
698 if (funcmode <> wf_engine.eng_run) then
699
700 resultout := wf_engine.eng_null;
701 return;
702
703 end if;
704
705 l_document_type := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
706 itemkey => itemkey,
707 aname => 'DOCUMENT_TYPE');
708
709 l_document_subtype := po_wf_util_pkg.GetItemAttrText (itemtype => itemtype,
710 itemkey => itemkey,
711 aname => 'DOCUMENT_SUBTYPE');
712
713 l_document_id := po_wf_util_pkg.GetItemAttrNumber (itemtype => itemtype,
714 itemkey => itemkey,
715 aname => 'DOCUMENT_ID');
716 -- Bug8795687: When AME is present with First responder win then
717 -- if the last approver is not the first responder then a NO ACTION
718 -- is recorded in his action_code. Hence Approve action
719 -- for owner should not be captured in this case.
720
721 l_is_ame_used := po_wf_util_pkg.GetItemAttrText(
722 itemtype => itemtype,
723 itemkey => itemkey,
724 aname => 'IS_AME_APPROVAL');
725
726 l_approver_id := po_wf_util_pkg.GetItemAttrNumber(
727 itemtype => itemtype,
728 itemkey => itemkey,
729 aname => 'APPROVER_EMPID');
730
731 l_preparer_id := po_wf_util_pkg.GetItemAttrNumber(
732 itemtype => itemtype,
733 itemkey => itemkey,
734 aname => 'PREPARER_ID');
735
736 IF( l_is_ame_used ='Y' AND l_approver_id <> l_preparer_id) THEN
737 l_insert_owner_app := false;
738
739 END IF;
740 -- Bug8795687: end;
741
742 if (l_document_type = 'REQUISITION' and l_insert_owner_app) then
743
744 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
745 itemkey => itemkey,
746 aname => 'NOTE',
747 avalue => '');
748
749 InsertHistForOwnerApprove(itemtype, itemkey,
750 l_document_id, l_document_type, l_document_subtype);
751 end if;
752
753
754 /* Set the Doc manager context */
755 -- Context Setting revamp
756 -- set_doc_mgr_context(itemtype, itemkey);
757
758 l_doc_mgr_return_val := ApproveDoc(itemtype, itemkey);
759
760 IF l_doc_mgr_return_val = 'Y' THEN
761
762 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
763 x_resultout := 'ACTIVITY_PERFORMED';
764
765 -- check if web suppliers is installed.
766
767 /* Commenting out the call to Invoke Acknowledge PO workflow
768 since this will now be handled as part of the approval workflow.
769 Changes for this workflow have been made in poxwfpoa.wft 115.22 */
770
771 /*if po_core_s.get_product_install_status('POS') = 'I' and
772 itemtype = 'POAPPRV' then
773
774 -- Start Acknowledgement Workflow if supplier contact is provided.
775 Invoke_Acknowledge_PO_WF(itemtype, itemkey);
776
777 end if; */
778
779 ELSE
780 raise doc_manager_exception;
781
782 END IF;
783
784 x_progress := 'PO_REQAPPROVAL_ACTION.Approve_doc: 02. RESULT= ' || x_resultout;
785 IF (g_po_wf_debug = 'Y') THEN
786 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
787 END IF;
788
789
790 EXCEPTION
791
792 WHEN doc_manager_exception THEN
793 raise;
794 WHEN OTHERS THEN
795 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
796 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
797 WF_CORE.context('PO_REQAPPROVAL_ACTION' , 'Approve_doc', itemtype, itemkey, x_progress);
798 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.APPROVE_DOC');
799 RAISE;
800
801
802 END Approve_doc;
803
804 --
805 -- Approve_and_forward_doc
806 -- Approve and forward the doc (i.e. set it status to PRE-APPROVED)
807 --
808 procedure Approve_and_forward_doc(itemtype in varchar2,
809 itemkey in varchar2,
810 actid in number,
811 funcmode in varchar2,
812 resultout out NOCOPY varchar2 ) is
813
814 x_progress varchar2(100);
815 x_resultout varchar2(30);
816
817 l_approver_empid number;
818 l_approver_user_name varchar2(100);
819 l_approver_disp_name varchar2(100);
820 l_doc_mgr_return_val varchar2(1);
821
822 l_doc_string varchar2(200);
823 l_preparer_user_name varchar2(100);
824 doc_manager_exception exception;
825
826
827 BEGIN
828
829 x_progress := 'PO_REQAPPROVAL_ACTION.Approve_and_forward_doc: 01';
830 IF (g_po_wf_debug = 'Y') THEN
831 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
832 END IF;
833
834
835 -- Do nothing in cancel or timeout mode
836 --
837 if (funcmode <> wf_engine.eng_run) then
838
839 resultout := wf_engine.eng_null;
840 return;
841
842 end if;
843
844 /* Set the Doc manager context */
845 -- Context Setting revamp
846 -- set_doc_mgr_context(itemtype, itemkey);
847
848 /* AME Project - setting the bypass flag to N if document is getting forwarded.*/
849 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
850 itemkey => itemkey,
851 aname => 'BYPASS_CHECKS_FLAG',
852 avalue => 'N');
853
854 l_doc_mgr_return_val := ApproveAndForward(itemtype, itemkey);
855
856 IF l_doc_mgr_return_val = 'Y' THEN
857
858 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
859 x_resultout := 'ACTIVITY_PERFORMED';
860
861 /* Set the value of APPROVER_USER_NAME to the Forward-to person, since
862 ** this he/she is going to be the new approver.
863 */
864 l_approver_user_name := wf_engine.GetItemAttrText
865 ( itemtype => itemType,
866 itemkey => itemkey,
867 aname => 'FORWARD_TO_USERNAME');
868
869 l_approver_empid := wf_engine.GetItemAttrNumber
870 ( itemtype => itemType,
871 itemkey => itemkey,
872 aname => 'FORWARD_TO_ID');
873
874 l_approver_disp_name := wf_engine.GetItemAttrText
875 ( itemtype => itemType,
876 itemkey => itemkey,
877 aname => 'FORWARD_TO_DISPLAY_NAME');
878
879 wf_engine.SetItemAttrText ( itemtype => itemType,
880 itemkey => itemkey,
881 aname => 'APPROVER_USER_NAME' ,
882 avalue => l_approver_user_name);
883
884 wf_engine.SetItemAttrNumber (itemtype => itemtype,
885 itemkey => itemkey,
886 aname => 'APPROVER_EMPID',
887 avalue => l_approver_empid);
888
889 wf_engine.SetItemAttrText (itemtype => itemtype,
890 itemkey => itemkey,
891 aname => 'APPROVER_DISPLAY_NAME',
892 avalue => l_approver_disp_name);
893
894 ELSE
895 raise doc_manager_exception;
896
897
898 END IF;
899
900 x_progress := 'PO_REQAPPROVAL_ACTION.Approve_and_forward_doc: 02. RESULT= ' || x_resultout;
901 IF (g_po_wf_debug = 'Y') THEN
902 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
903 END IF;
904
905 EXCEPTION
906
907 WHEN doc_manager_exception THEN
908 raise;
909 WHEN OTHERS THEN
910 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
911 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
912 WF_CORE.context('PO_REQAPPROVAL_ACTION', 'Approve_and_forward_doc', itemtype, itemkey,x_progress);
913 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.APPROVE_AND_FORWARD_DOC');
914 RAISE;
915
916 END Approve_and_forward_doc;
917
918 --
919
920 --
921 -- Forward_doc_inprocess
922 -- If document status is INCOMPLETE, then call cover routine to set the
923 -- status to INPROCESS and forward to the approver.
924 --
925 --
926 procedure Forward_doc_inprocess(itemtype in varchar2,
927 itemkey in varchar2,
928 actid in number,
929 funcmode in varchar2,
930 resultout out NOCOPY varchar2 ) is
931
932 l_found_manager VARCHAR2(1);
933 x_progress varchar2(100);
934 x_resultout varchar2(30);
935
936 l_approver_user_name varchar2(100);
937 l_approver_disp_name varchar2(100);
938 l_approver_empid number;
939 l_doc_mgr_return_val varchar2(1);
940
941 l_doc_string varchar2(200);
942 l_preparer_user_name varchar2(100);
943 doc_manager_exception exception;
944
945 BEGIN
946
947 x_progress := 'PO_REQAPPROVAL_ACTION.Forward_doc_inprocess: 01';
948 IF (g_po_wf_debug = 'Y') THEN
949 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
950 END IF;
951
952
953 -- Do nothing in cancel or timeout mode
954 --
955 if (funcmode <> wf_engine.eng_run) then
956
957 resultout := wf_engine.eng_null;
958 return;
959
960 end if;
961
962 /* Set the Doc manager context */
963
964 -- Context Setting revamp
965 -- set_doc_mgr_context(itemtype, itemkey);
966
967 /* AME Project - setting the bypass flag to N if document is getting forwarded.*/
968 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
969 itemkey => itemkey,
970 aname => 'BYPASS_CHECKS_FLAG',
971 avalue => 'N');
972
973 l_doc_mgr_return_val := ForwardDocInProcess(itemtype, itemkey);
974
975 IF l_doc_mgr_return_val = 'Y' THEN
976
977 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
978 x_resultout := 'ACTIVITY_PERFORMED';
979
980 /* Set the value of APPROVER_USER_NAME to the Forward-to person, since
981 ** this he/she is going to be the new approver.
982 */
983
984 l_approver_user_name := wf_engine.GetItemAttrText
985 ( itemtype => itemType,
986 itemkey => itemkey,
987 aname => 'FORWARD_TO_USERNAME');
988
989 l_approver_empid := wf_engine.GetItemAttrNumber
990 ( itemtype => itemType,
991 itemkey => itemkey,
992 aname => 'FORWARD_TO_ID');
993
994 l_approver_disp_name := wf_engine.GetItemAttrText
995 ( itemtype => itemType,
996 itemkey => itemkey,
997 aname => 'FORWARD_TO_DISPLAY_NAME');
998
999 wf_engine.SetItemAttrText ( itemtype => itemType,
1000 itemkey => itemkey,
1001 aname => 'APPROVER_USER_NAME' ,
1002 avalue => l_approver_user_name);
1003
1004 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1005 itemkey => itemkey,
1006 aname => 'APPROVER_EMPID',
1007 avalue => l_approver_empid);
1008
1009 wf_engine.SetItemAttrText (itemtype => itemtype,
1010 itemkey => itemkey,
1011 aname => 'APPROVER_DISPLAY_NAME',
1012 avalue => l_approver_disp_name);
1013
1014 ELSE
1015
1016 raise doc_manager_exception;
1017
1018 END IF;
1019
1020 x_progress := 'PO_REQAPPROVAL_ACTION.Forward_doc_inprocess: 02. RESULT= ' || x_resultout;
1021 IF (g_po_wf_debug = 'Y') THEN
1022 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1023 END IF;
1024
1025 EXCEPTION
1026 WHEN doc_manager_exception THEN
1027 raise;
1028 WHEN OTHERS THEN
1029 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1030 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1031 WF_CORE.context('PO_REQAPPROVAL_ACTION' , 'Forward_doc_inprocess', itemtype, itemkey,x_progress);
1032 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.FORWARD_DOC_INPROCESS');
1033 RAISE;
1034
1035 END Forward_doc_inprocess;
1036
1037 --
1038
1039 -- Forward_doc_preapproved
1040 -- If document status is PRE-APPROVED then call cover routine to
1041 -- forward the document to the next approver (doc status stays PRE-APPROVED).
1042 --
1043 procedure Forward_doc_preapproved(itemtype in varchar2,
1044 itemkey in varchar2,
1045 actid in number,
1046 funcmode in varchar2,
1047 resultout out NOCOPY varchar2 ) is
1048
1049 x_progress varchar2(100);
1050 x_resultout varchar2(30);
1051
1052 l_approver_user_name varchar2(100);
1053 l_approver_disp_name varchar2(100);
1054 l_approver_empid number;
1055 l_doc_mgr_return_val varchar2(1);
1056
1057 l_doc_string varchar2(200);
1058 l_preparer_user_name varchar2(100);
1059 doc_manager_exception exception;
1060
1061
1062 BEGIN
1063
1064 x_progress := 'PO_REQAPPROVAL_ACTION.Forward_doc_preapproved: 01';
1065 IF (g_po_wf_debug = 'Y') THEN
1066 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1067 END IF;
1068
1069
1070 -- Do nothing in cancel or timeout mode
1071 --
1072 if (funcmode <> wf_engine.eng_run) then
1073
1074 resultout := wf_engine.eng_null;
1075 return;
1076
1077 end if;
1078
1079 /* Set the Doc manager context */
1080 -- Context Setting revamp
1081 -- set_doc_mgr_context(itemtype, itemkey);
1082
1083 /* AME Project - setting the bypass flag to N if document is getting forwarded.*/
1084 po_wf_util_pkg.SetItemAttrText(itemtype => itemtype,
1085 itemkey => itemkey,
1086 aname => 'BYPASS_CHECKS_FLAG',
1087 avalue => 'N');
1088 l_doc_mgr_return_val := ForwardDocPreapproved(itemtype, itemkey);
1089
1090 IF l_doc_mgr_return_val = 'Y' THEN
1091
1092 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1093 x_resultout := 'ACTIVITY_PERFORMED';
1094
1095 /* Set the value of APPROVER_USER_NAME to the Forward-to person, since
1096 ** this he/she is going to be the new approver.
1097 */
1098 l_approver_user_name := wf_engine.GetItemAttrText
1099 ( itemtype => itemType,
1100 itemkey => itemkey,
1101 aname => 'FORWARD_TO_USERNAME');
1102
1103 l_approver_empid := wf_engine.GetItemAttrNumber
1104 ( itemtype => itemType,
1105 itemkey => itemkey,
1106 aname => 'FORWARD_TO_ID');
1107
1108 l_approver_disp_name := wf_engine.GetItemAttrText
1109 ( itemtype => itemType,
1110 itemkey => itemkey,
1111 aname => 'FORWARD_TO_DISPLAY_NAME');
1112
1113 wf_engine.SetItemAttrText ( itemtype => itemType,
1114 itemkey => itemkey,
1115 aname => 'APPROVER_USER_NAME' ,
1116 avalue => l_approver_user_name);
1117
1118 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1119 itemkey => itemkey,
1120 aname => 'APPROVER_EMPID',
1121 avalue => l_approver_empid);
1122
1123 wf_engine.SetItemAttrText (itemtype => itemtype,
1124 itemkey => itemkey,
1125 aname => 'APPROVER_DISPLAY_NAME',
1126 avalue => l_approver_disp_name);
1127
1128 ELSE
1129 raise doc_manager_exception;
1130
1131 END IF;
1132
1133 x_progress := 'PO_REQAPPROVAL_ACTION.Forward_doc_preapproved: 02. RESULT= ' || x_resultout;
1134 IF (g_po_wf_debug = 'Y') THEN
1135 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1136 END IF;
1137
1138 EXCEPTION
1139 WHEN doc_manager_exception THEN
1140 raise;
1141 WHEN OTHERS THEN
1142 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1143 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1144 WF_CORE.context('PO_REQAPPROVAL_ACTION' , 'Forward_doc_preapproved', itemtype, itemkey,x_progress);
1145 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.FORWARD_DOC_PREAPPROVED');
1146 RAISE;
1147
1148
1149 END Forward_doc_preapproved;
1150
1151 -- Reject_DOc
1152 --
1153 procedure Reject_Doc(itemtype in varchar2,
1154 itemkey in varchar2,
1155 actid in number,
1156 funcmode in varchar2,
1157 resultout out NOCOPY varchar2 ) is
1158
1159 x_progress varchar2(100);
1160 x_resultout varchar2(30);
1161 l_doc_mgr_return_val varchar2(1);
1162
1163 l_doc_string varchar2(200);
1164 l_preparer_user_name varchar2(100);
1165 doc_manager_exception exception;
1166
1167 /* Bug 12360278 */
1168 l_note po_action_history.note%TYPE;
1169 l_document_type varchar2(25);
1170 /*Bug 12360278 */
1171
1172
1173 BEGIN
1174
1175 x_progress := 'PO_REQAPPROVAL_ACTION.Reject_Doc: 01';
1176 IF (g_po_wf_debug = 'Y') THEN
1177 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1178 END IF;
1179
1180
1181 -- Do nothing in cancel or timeout mode
1182 --
1183 if (funcmode <> wf_engine.eng_run) then
1184
1185 resultout := wf_engine.eng_null;
1186 return;
1187
1188 end if;
1189
1190 /* Set the Doc manager context */
1191 -- Context Setting revamp
1192 -- set_doc_mgr_context(itemtype, itemkey);
1193
1194 /*Bug 12360278 Reset response note*/
1195 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1196 itemkey => itemkey,
1197 aname => 'DOCUMENT_TYPE');
1198 IF (l_document_type='PO' OR l_document_type='PA' OR l_document_type='RELEASE' ) THEN
1199 l_note := PO_WF_UTIL_PKG.GetItemAttrText(itemtype=>itemtype,
1200 itemkey => itemkey,
1201 aname => 'NOTE_R');
1202 PO_WF_UTIL_PKG.SetItemAttrText(itemtype=>itemtype,
1203 itemkey => itemkey,
1204 aname => 'NOTE',
1205 avalue => l_note);
1206 PO_WF_UTIL_PKG.SetItemAttrText(itemtype=>itemtype,
1207 itemkey => itemkey,
1208 aname => 'NOTE_R',
1209 avalue => null);
1210 END IF;
1211 /*Bug 12360278 */
1212
1213
1214 l_doc_mgr_return_val := RejectDoc(itemtype, itemkey);
1215
1216 IF l_doc_mgr_return_val = 'F' THEN
1217 raise doc_manager_exception;
1218
1219 END IF;
1220 resultout := wf_engine.eng_completed || ':' || l_doc_mgr_return_val;
1221
1222 x_progress := 'PO_REQAPPROVAL_ACTION.Reject_Doc: 02. RESULT= ' || l_doc_mgr_return_val;
1223 IF (g_po_wf_debug = 'Y') THEN
1224 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1225 END IF;
1226
1227 EXCEPTION
1228
1229 WHEN doc_manager_exception THEN
1230 raise;
1231 WHEN OTHERS THEN
1232 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1233 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1234 WF_CORE.context('PO_REQAPPROVAL_ACTION' , 'Reject_doc', itemtype, itemkey,x_progress);
1235 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.REJECT_DOC');
1236 RAISE;
1237
1238
1239 END Reject_Doc;
1240
1241 --
1242 -- Verify_authority
1243 -- Verify the approval authority against the PO setup control rules.
1244 --
1245 procedure Verify_authority(itemtype in varchar2,
1246 itemkey in varchar2,
1247 actid in number,
1248 funcmode in varchar2,
1249 resultout out NOCOPY varchar2 ) is
1250
1251 x_progress varchar2(100);
1252 x_resultout varchar2(30);
1253
1254 l_doc_mgr_return_val varchar2(1);
1255
1256 l_doc_string varchar2(200);
1257 l_preparer_user_name varchar2(100);
1258 doc_manager_exception exception;
1259
1260 BEGIN
1261
1262 x_progress := 'PO_REQAPPROVAL_ACTION.Verify_authority: 01';
1263 IF (g_po_wf_debug = 'Y') THEN
1264 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1265 END IF;
1266
1267
1268 -- Do nothing in cancel or timeout mode
1269 --
1270 if (funcmode <> wf_engine.eng_run) then
1271
1272 resultout := wf_engine.eng_null;
1273 return;
1274
1275 end if;
1276
1277 /* Set the Doc manager context */
1278 -- Context Setting revamp
1279 -- set_doc_mgr_context(itemtype, itemkey);
1280
1281 l_doc_mgr_return_val := VerifyAuthority(itemtype, itemkey);
1282
1283 /* If the return value is 'F', then the transition in the Wokflow will
1284 ** be "Default"
1285 */
1286 If l_doc_mgr_return_val = 'F' then
1287 raise doc_manager_exception;
1288 End if;
1289
1290 resultout := wf_engine.eng_completed || ':' || l_doc_mgr_return_val;
1291 x_resultout := l_doc_mgr_return_val;
1292
1293
1294 x_progress := 'PO_REQAPPROVAL_ACTION.Verify_authority: 02. RESULT= ' || x_resultout;
1295 IF (g_po_wf_debug = 'Y') THEN
1296 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1297 END IF;
1298
1299 EXCEPTION
1300 WHEN doc_manager_exception THEN
1301 raise;
1302 WHEN OTHERS THEN
1303 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1304 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1305 WF_CORE.context('PO_REQAPPROVAL_ACTION' , 'Verify_authority', itemtype, itemkey, x_progress);
1306 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.VERIFY_AUTHORITY');
1307 RAISE;
1308
1309
1310 END Verify_authority;
1311
1312
1313 --
1314 -- Open_Doc_State
1315 --
1316 --
1317 procedure Open_Doc_State( itemtype in varchar2,
1318 itemkey in varchar2,
1319 actid in number,
1320 funcmode in varchar2,
1321 resultout out NOCOPY varchar2 ) is
1322 x_progress varchar2(100);
1323 x_resultout varchar2(30);
1324
1325 l_doc_mgr_return_val varchar2(1);
1326
1327 l_doc_string varchar2(200);
1328 l_preparer_user_name varchar2(100);
1329 doc_manager_exception exception;
1330
1331 BEGIN
1332
1333 x_progress := 'PO_REQAPPROVAL_ACTION.Open_Doc_State: 01';
1334 IF (g_po_wf_debug = 'Y') THEN
1335 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1336 END IF;
1337
1338
1339 -- Do nothing in cancel or timeout mode
1340 --
1341 if (funcmode <> wf_engine.eng_run) then
1342
1343 resultout := wf_engine.eng_null;
1344 return;
1345
1346 end if;
1347
1348 /* Set the Doc manager context */
1349 -- Context Setting revamp
1350 -- set_doc_mgr_context(itemtype, itemkey);
1351
1352 l_doc_mgr_return_val := OpenDocumentState(itemtype, itemkey);
1353
1354 /* If the return value is 'F', then the transition in the Wokflow will
1355 ** be "Default"
1356 */
1357 IF l_doc_mgr_return_val <> 'F' THEN
1358
1359 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1360 x_resultout := 'ACTIVITY_PERFORMED';
1361 ELSE
1362 raise doc_manager_exception;
1363
1364 END IF;
1365
1366 x_progress := 'PO_REQAPPROVAL_ACTION.Open_Doc_State 02. RESULT= ' || x_resultout;
1367 IF (g_po_wf_debug = 'Y') THEN
1368 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1369 END IF;
1370
1371 EXCEPTION
1372
1373 WHEN doc_manager_exception THEN
1374 raise;
1375 WHEN OTHERS THEN
1376 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1377 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1378 WF_CORE.context('PO_REQAPPROVAL_ACTION', 'Open_Doc_State', itemtype, itemkey, x_progress);
1379 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'PO_REQAPPROVAL_ACTION.OPEN_DOC_STATE');
1380 RAISE;
1381
1382 END Open_Doc_State;
1383
1384 --
1385 --
1386 procedure Reserve_Doc( itemtype in varchar2,
1387 itemkey in varchar2,
1388 actid in number,
1389 funcmode in varchar2,
1390 resultout out NOCOPY varchar2 ) is
1391 x_progress varchar2(100);
1392 x_resultout varchar2(30);
1393
1394 l_responder_id number;
1395 l_doc_type varchar2(30);
1396
1397 l_doc_mgr_return_val varchar2(1);
1398
1399 l_doc_string varchar2(200);
1400 l_preparer_user_name varchar2(100);
1401 doc_manager_exception exception;
1402
1403 x_override_funds varchar2(3) := NULL;
1404
1405 l_org_id number; -- Bug 3426272
1406
1407 BEGIN
1408
1409 x_progress := 'PO_REQAPPROVAL_ACTION.Reserve_Doc: 01';
1410 IF (g_po_wf_debug = 'Y') THEN
1411 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1412 END IF;
1413
1414
1415 -- Do nothing in cancel or timeout mode
1416 --
1417 if (funcmode <> wf_engine.eng_run) then
1418
1419 resultout := wf_engine.eng_null;
1420 return;
1421
1422 end if;
1423
1424 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1425 itemkey => itemkey,
1426 aname => 'DOCUMENT_TYPE');
1427
1428
1429 l_responder_id := PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype => itemtype,
1430 itemkey => itemkey,
1431 aname => 'RESPONDER_USER_ID');
1432 -- Context Setting revamp
1433 -- if (l_responder_id is null) then
1434
1435 /* Set the Doc manager context */
1436 -- set_doc_mgr_context(itemtype, itemkey);
1437
1438 --else
1439
1440 /* Set the Doc manager context based on responder */
1441 --set_responder_doc_mgr_context(itemtype, itemkey);
1442
1443 --end if;
1444
1445 /* Bug 3426272: Set the org context to the org context stored in the
1446 * workflow attribute. If the approval workflow is called from another
1447 * application, the above doc_mgr_context calls may reset the
1448 * application context and its sub context, the org context, to that
1449 * of the calling org / application.
1450 *
1451 * Before calling the new FPJ encumbrance code, we must ensure
1452 * that the org context is set to that of the document we are reserving.
1453 * This is conveniently stored at workflow setup time in the
1454 * attribute 'ORG_ID'.
1455 *
1456 */
1457
1458 l_org_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
1459 itemkey => itemkey,
1460 aname => 'ORG_ID');
1461
1462 IF l_org_id is NOT NULL
1463 THEN
1464 PO_MOAC_UTILS_PVT.set_org_context(l_org_id) ; -- <R12.MOAC>
1465 END IF;
1466
1467 /* End Bug 3426272 */
1468
1469
1470 /* Bug# 2234341: kagarwal
1471 ** Desc: Get the value of profile option PO: Override Funds Reservation
1472 ** and pass it to the ReserveDoc function.
1473 */
1474
1475 fnd_profile.get('PO_REQAPPR_OVERRIDE_FUNDS', x_override_funds);
1476
1477 l_doc_mgr_return_val := ReserveDoc(itemtype, itemkey, x_override_funds);
1478
1479 -- <ENCUMBRANCE FPJ START>
1480 -- Commenting the Doc manager Handling
1481
1482 -- If l_doc_mgr_return_val = 'F' then
1483 -- raise doc_manager_exception;
1484 -- End if;
1485
1486 -- <ENCUMBRANCE FPJ END>
1487
1488 resultout := wf_engine.eng_completed || ':' || l_doc_mgr_return_val;
1489 x_resultout := l_doc_mgr_return_val;
1490
1491 x_progress := 'PO_REQAPPROVAL_ACTION.Reserve_Doc 02. RESULT= ' || x_resultout;
1492 IF (g_po_wf_debug = 'Y') THEN
1493 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1494 END IF;
1495
1496 EXCEPTION
1497 -- WHEN doc_manager_exception THEN
1498 -- raise;
1499
1500 WHEN OTHERS THEN
1501 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1502 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(
1503 itemType, itemkey);
1504 WF_CORE.context('PO_REQAPPROVAL_ACTION', 'Reserve_Doc', itemtype, itemkey,
1505 x_progress);
1506 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey,
1507 l_preparer_user_name, l_doc_string, sqlerrm,
1508 'PO_REQAPPROVAL_ACTION.RESERVE_DOC');
1509 RAISE;
1510 END Reserve_Doc;
1511 --
1512
1513 /*********************************************************************************
1514 ** The following are the APIs that support the workflow procedures.
1515 *********************************************************************************/
1516
1517
1518 FUNCTION StateCheckApprove(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
1519
1520 -- <Doc Manager Rewrite 11.5.11 Start>
1521
1522 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
1523 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
1524 l_document_id NUMBER;
1525
1526 l_ret_sts VARCHAR2(1);
1527 l_exc_msg VARCHAR2(2000);
1528 l_ret_code VARCHAR2(25);
1529
1530 -- <Doc Manager Rewrite 11.5.11 End>
1531
1532 x_progress varchar2(200);
1533
1534 BEGIN
1535
1536 -- <Doc Manager Rewrite 11.5.11 Start>
1537
1538 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1539 itemkey => itemkey,
1540 aname => 'DOCUMENT_TYPE');
1541
1542 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
1543 itemkey => itemkey,
1544 aname => 'DOCUMENT_SUBTYPE');
1545
1546 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1547 itemkey => itemkey,
1548 aname => 'DOCUMENT_ID');
1549
1550 x_progress := 'StateCheckApprove: calling action with: ' || 'Doc_type= ' ||
1551 l_document_type || ' Subtype= ' || l_document_subtype ||
1552 ' Doc_id= ' || to_char(l_document_id);
1553
1554 IF (g_po_wf_debug = 'Y') THEN
1555 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1556 END IF;
1557
1558 PO_DOCUMENT_ACTION_PVT.check_doc_status_approve(
1559 p_document_id => l_document_id
1560 , p_document_type => l_document_type
1561 , p_document_subtype => l_document_subtype
1562 , x_return_status => l_ret_sts
1563 , x_return_code => l_ret_code
1564 , x_exception_msg => l_exc_msg
1565 );
1566
1567 -- check_doc_status_approve sets return status to 'S' or 'U' only
1568 IF (l_ret_sts = 'S') THEN
1569
1570 /* If state check passed, then l_ret_code should be null
1571 ** otherwise it should be 'STATE_FAILED'.
1572 */
1573
1574 IF (l_ret_code is NULL) THEN
1575
1576 return('Y');
1577
1578 ELSE
1579
1580 x_progress := 'PO_REQAPPROVAL_ACTION.StateCheckApprove: Returned_code= ' || l_ret_code;
1581 IF (g_po_wf_debug = 'Y') THEN
1582 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1583 END IF;
1584
1585 return('N');
1586
1587 END IF; -- l_ret_code IS NULL
1588
1589 ELSE
1590
1591 x_progress := 'PO_REQAPPROVAL_ACTION.StateCheckApprove: action call returned with: ' || l_ret_sts;
1592 IF (g_po_wf_debug = 'Y') THEN
1593 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1594 END IF;
1595
1596 doc_mgr_err_num := 3;
1597 sysadmin_err_msg := l_exc_msg;
1598
1599 return('F');
1600
1601 END IF; -- l_ret_sts = 'S'
1602
1603 -- <Doc Manager Rewrite 11.5.11 End>
1604
1605 EXCEPTION
1606
1607 WHEN OTHERS THEN
1608 wf_core.context('PO_REQAPPROVAL_ACTION','StateCheckApprove',x_progress);
1609 raise;
1610
1611 END StateCheckApprove;
1612
1613 --
1614
1615 FUNCTION StateCheckReject(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
1616
1617 -- <Doc Manager Rewrite 11.5.11 Start>
1618
1619 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
1620 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
1621 l_document_id NUMBER;
1622
1623 l_ret_sts VARCHAR2(1);
1624 l_exc_msg VARCHAR2(2000);
1625 l_ret_code VARCHAR2(25);
1626
1627 -- <Doc Manager Rewrite 11.5.11 End>
1628
1629 x_progress varchar2(200);
1630
1631 BEGIN
1632
1633 -- <Doc Manager Rewrite 11.5.11 Start>
1634
1635 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
1636 itemkey => itemkey,
1637 aname => 'DOCUMENT_TYPE');
1638
1639 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
1640 itemkey => itemkey,
1641 aname => 'DOCUMENT_SUBTYPE');
1642
1643 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1644 itemkey => itemkey,
1645 aname => 'DOCUMENT_ID');
1646
1647 x_progress := 'StateCheckReject: calling action with: ' || 'Doc_type= ' ||
1648 l_document_type || ' Subtype= ' || l_document_subtype ||
1649 ' Doc_id= ' || to_char(l_document_id);
1650
1651 IF (g_po_wf_debug = 'Y') THEN
1652 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1653 END IF;
1654
1655 PO_DOCUMENT_ACTION_PVT.check_doc_status_reject(
1656 p_document_id => l_document_id
1657 , p_document_type => l_document_type
1658 , p_document_subtype => l_document_subtype
1659 , x_return_status => l_ret_sts
1660 , x_return_code => l_ret_code
1661 , x_exception_msg => l_exc_msg
1662 );
1663
1664 -- check_doc_status_reject returns either 'S' or 'U'
1665 IF (l_ret_sts = 'S') THEN
1666
1667 /* If state check passed, then l_ret_code should be null
1668 ** otherwise it should be 'STATE_FAILED'.
1669 */
1670
1671 IF (l_ret_code is NULL) THEN
1672
1673 return('Y');
1674
1675 ELSE
1676
1677 x_progress := 'PO_REQAPPROVAL_ACTION.StateCheckReject: Returned_code= ' || l_ret_code;
1678 IF (g_po_wf_debug = 'Y') THEN
1679 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1680 END IF;
1681
1682 return('N');
1683
1684 END IF; -- l_ret_code IS NULL
1685
1686 ELSE
1687
1688 x_progress := 'PO_REQAPPROVAL_ACTION.StateCheckReject: action call returned with: ' || l_ret_sts;
1689 IF (g_po_wf_debug = 'Y') THEN
1690 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1691 END IF;
1692
1693 doc_mgr_err_num := 3;
1694 sysadmin_err_msg := l_exc_msg;
1695
1696 return('F');
1697
1698 END IF; -- l_ret_sts = 'S'
1699
1700 -- <Doc Manager Rewrite 11.5.11 End>
1701
1702 EXCEPTION
1703
1704 WHEN OTHERS THEN
1705 wf_core.context('PO_REQAPPROVAL_ACTION','StateCheckReject',x_progress);
1706 raise;
1707
1708 END StateCheckReject;
1709
1710 --
1711 FUNCTION DocCompleteCheck(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
1712
1713 --<SUBMISSION CHECK FPI START>
1714 --L_DM_CALL_REC PO_DOC_MANAGER_PUB.DM_CALL_REC_TYPE;
1715 l_document_type VARCHAR2(25);
1716 l_document_subtype VARCHAR2(25);
1717 l_document_id NUMBER;
1718 x_return_status VARCHAR2(1);
1719 x_sub_check_status VARCHAR2(1);
1720 x_msg_data VARCHAR2(2000);
1721 x_online_report_id NUMBER;
1722 --<SUBMISSION CHECK FPI END>
1723
1724 l_create_sourcing_rule VARCHAR2(1); -- <2757450>
1725 l_check_asl BOOLEAN; -- <2757450>
1726
1727 x_progress varchar2(200);
1728
1729 l_error VARCHAR2(1) := 'W'; --CONTERMS FPJ
1730 l_conterms_yn PO_headers_all.conterms_exist_Flag%Type :='N'; -- CONTERMS FPJ
1731 l_draft_id Number; -- CLM Aprvl
1732
1733 BEGIN
1734
1735 --<SUBMISSION CHECK FPI START>
1736 -- Starting 115.33 the submission check code has been changed from doc
1737 -- mgr call to PL/SQL call as part of SUBMISSION CHECK REWRITE project in FPI
1738
1739 -- New call to pl/sql package PO_DOCUMENT_CHECKS_GRP.po_submission_check()
1740 -- in file POXGDCKB.pls as part of FPI project SUBMISSION CHECK REWRITE
1741
1742 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText
1743 ( itemtype => itemtype,
1744 itemkey => itemkey,
1745 aname => 'DOCUMENT_TYPE'
1746 );
1747 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText
1748 ( itemtype => itemtype,
1749 itemkey => itemkey,
1750 aname => 'DOCUMENT_SUBTYPE'
1751 );
1752 l_document_id := PO_WF_UTIL_PKG.GetItemAttrNumber
1753 ( itemtype => itemtype,
1754 itemkey => itemkey,
1755 aname => 'DOCUMENT_ID'
1756 );
1757 l_create_sourcing_rule := PO_WF_UTIL_PKG.GetItemAttrText -- <2757450>
1758 ( itemtype => itemtype,
1759 itemkey => itemkey,
1760 aname => 'CREATE_SOURCING_RULE'
1761 );
1762
1763 -- CLM Aprvl
1764 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber
1765 ( itemtype => itemtype,
1766 itemkey => itemkey,
1767 aname => 'DRAFT_ID'
1768 );
1769 If l_draft_id is Null Then
1770 l_draft_id := -1;
1771 End If;
1772 -- CLM Aprvl
1773
1774 -- Start of code changes for the bug 16021525
1775 application_id := PO_WF_UTIL_PKG.GetItemAttrNumber
1776 ( itemtype => itemtype,
1777 itemkey => itemkey,
1778 aname => 'APPLICATION_ID'
1779 );
1780 responsibility_id := PO_WF_UTIL_PKG.GetItemAttrNumber
1781 ( itemtype => itemtype,
1782 itemkey => itemkey,
1783 aname => 'RESPONSIBILITY_ID'
1784 );
1785 user_id := PO_WF_UTIL_PKG.GetItemAttrNumber
1786 ( itemtype => itemtype,
1787 itemkey => itemkey,
1788 aname => 'USER_ID'
1789 );
1790
1791 x_progress := 'DocCompleteCheck: calling NEW po_submission_check with: ' || 'Doc_type= ' ||
1792 l_document_Type || ' Subtype= ' || l_document_subtype ||
1793 ' Doc_id= ' || to_char(l_document_Id)||
1794 'Application ID = '||to_char(application_id)||
1795 'responsibility_id = '||to_char(responsibility_id)||
1796 'user_id = '||to_char(user_id);
1797
1798 IF (g_po_wf_debug = 'Y') THEN
1799 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1800 END IF;
1801
1802 -- <2757450 START>: If user chose to Create Sourcing Rule at the time
1803 -- of Approval, do not run the ASL Submission Checks.
1804 --
1805 IF ( l_create_sourcing_rule = 'Y' )
1806 THEN
1807 l_check_asl := FALSE; -- indicates ASL checks should NOT be run
1808 ELSE
1809 l_check_asl := TRUE; -- indicates ASL checks should be run
1810 END IF;
1811 --
1812 -- <2757450 END>
1813
1814 --Call the API to do Submission Checks in PL/SQL
1815 --<ENCUMBRANCE FPJ>
1816 po_submission_check_autonomous(
1817 p_document_type => l_document_type
1818 , p_document_subtype => l_document_subtype
1819 , p_document_id => l_document_id
1820 , p_check_asl => l_check_asl
1821 , p_draft_id => l_draft_id -- CLM Aprvl
1822 , x_return_status => x_return_status
1823 , x_sub_check_status => x_sub_check_status
1824 , x_msg_data => x_msg_data
1825 , x_online_report_id => x_online_report_id
1826 );
1827
1828 application_id := null;
1829 responsibility_id := null;
1830 user_id := null;
1831 -- END of code changes for the bug 16021525
1832
1833 /* If the API executed with no errors
1834 ** x_return_status = G_RET_STS_SUCCESS
1835 ** x_sub_check_status = G_RET_STS_SUCCESS then return G_RET_STS_SUCCESS
1836 ** If the API call went finw while doc has submission check error
1837 ** x_return_status = G_RET_STS_SUCCESS
1838 ** x_sub_check_status = G_RET_STS_ERROR then return G_RET_STS_ERROR
1839 ** Else issue a notification to the system admin that something is wrong with the
1840 ** Submission Check API call.
1841 */
1842 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1843
1844 IF x_sub_check_status = FND_API.G_RET_STS_SUCCESS THEN
1845 return(FND_API.G_RET_STS_SUCCESS);
1846 ELSE
1847
1848 x_progress := 'PO_REQAPPROVAL_ACTION.DocCompleteCheck: x_sub_check_status= ' ||
1849 x_sub_check_status || ' On_Line_Report_id= ' ||
1850 to_char(x_online_report_id);
1851
1852
1853
1854 IF (g_po_wf_debug = 'Y') THEN
1855 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1856 END IF;
1857
1858 --<CONTERMS FPJ START>
1859 l_conterms_yn:= PO_wf_Util_Pkg.GetItemAttrText(
1860 itemtype => itemtype,
1861 itemkey => itemkey,
1862 aname => 'CONTERMS_EXIST_FLAG');
1863
1864 IF (l_conterms_yn = 'Y') then
1865 BEGIN
1866 -- SQL What:Checks for error message type in error table
1867 -- SQL Why :If no errors and only warnings then, success is returned
1868 -- SQL JOIN: NONE
1869 SELECT 'E'
1870 INTO l_error
1871 FROM dual
1872 WHERE EXISTS (SELECT 1
1873 FROM PO_ONLINE_REPORT_TEXT
1874 WHERE online_report_id = x_online_report_id
1875 AND NVL(message_type, 'E') = 'E'); -- Bug 3906870
1876 EXCEPTION
1877 WHEN NO_DATA_FOUND THEN
1878 l_error:='W';
1879 END;
1880 IF (l_error = 'W') then
1881 x_progress := 'PO_REQAPPROVAL_ACTION.DocCompleteCheck: Only Warnings found. Return success';
1882
1883 IF (g_po_wf_debug = 'Y') THEN
1884 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1885 END IF;
1886 return (FND_API.G_RET_STS_SUCCESS);
1887 END IF; --l_error=W
1888 END IF;-- l_conterms_yn
1889
1890 --<CONTERMS FPJ END>
1891 /* Get the online_report_id (to be used to populate the notification */
1892 PO_WF_UTIL_PKG.SetItemAttrNumber ( itemtype => itemType,
1893 itemkey => itemkey,
1894 aname => 'ONLINE_REPORT_ID',
1895 avalue => x_online_report_id);
1896
1897 /* Get the text of the online_report and store in workflow item attribute */
1898 get_online_report_text( itemtype, itemkey, x_online_report_id );
1899
1900 return(FND_API.G_RET_STS_ERROR);
1901
1902 END IF; --API is success
1903
1904 ELSE
1905 /* something went wrong with Submission Check API call. Send notification to Sys Admin.
1906 ** The error message is kept in Item Attribute SYSADMIN_ERROR_MSG */
1907
1908
1909 x_progress := 'PO_REQAPPROVAL_ACTION.DocCompleteCheck: po_submission_check returned with: ' ||
1910 x_return_status ;
1911
1912 IF (g_po_wf_debug = 'Y') THEN
1913 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1914 END IF;
1915
1916 sysadmin_err_msg := x_msg_data;
1917
1918 x_progress := 'PO_REQAPPROVAL_ACTION.DocCompleteCheck: po_submission_check error msg is: ' ||
1919 x_msg_data;
1920
1921 IF (g_po_wf_debug = 'Y') THEN
1922 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1923 END IF;
1924
1925 return(x_return_status);
1926
1927 END IF;
1928 --<SUBMISSION CHECK FPI END>
1929
1930 EXCEPTION
1931
1932 WHEN OTHERS THEN
1933 wf_core.context('PO_REQAPPROVAL_ACTION','DocCompleteCheck',x_progress);
1934 raise;
1935
1936 END DocCompleteCheck;
1937
1938 /*
1939 If the document is self approved, add a new blank row in PO_ACTION_HISTORY
1940 for doc mgr call.
1941 */
1942 PROCEDURE InsertHistForOwnerApprove (itemtype VARCHAR2,
1943 itemkey VARCHAR2,
1944 p_document_id NUMBER,
1945 p_document_type VARCHAR2,
1946 p_document_subtype VARCHAR2) IS
1947 pragma AUTONOMOUS_TRANSACTION;
1948
1949 l_action_code PO_ACTION_HISTORY.ACTION_CODE%TYPE;
1950 l_sequence_num NUMBER;
1951 l_employee_id NUMBER;
1952 l_object_rev_num NUMBER;
1953 l_approval_path_id NUMBER;
1954
1955 x_progress varchar2(200);
1956
1957 BEGIN
1958
1959 x_progress := 'PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove begin';
1960
1961 IF (g_po_wf_debug = 'Y') THEN
1962 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1963 END IF;
1964
1965 -- get data from last entry in po_action_history
1966 SELECT sequence_num,
1967 action_code,
1968 object_revision_num,
1969 approval_path_id
1970 INTO l_sequence_num,
1971 l_action_code,
1972 l_object_rev_num,
1973 l_approval_path_id
1974 FROM po_action_history
1975 WHERE object_id = p_document_id
1976 AND object_type_code = p_document_type
1977 AND sequence_num = (SELECT max(sequence_num)
1978 FROM po_action_history
1979 WHERE object_id = p_document_id
1980 AND Object_Type_Code = P_Document_Type
1981 AND object_sub_type_code = p_document_subtype)
1982 AND Object_Sub_Type_Code = P_Document_Subtype;
1983
1984 x_progress := 'PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove action_code: ' || l_action_code;
1985
1986 IF (g_po_wf_debug = 'Y') THEN
1987 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
1988 END IF;
1989
1990 -- if last entry into po_action_history is submit or reserve, then
1991 -- req is self-approved. Insert approval entry.
1992 --Bug 13444730 Adding Signed action. In case of Signed take employee_id corresponding to Submit.
1993 if (l_action_code in ('SUBMIT', 'RESERVE', 'NO ACTION','SIGNED')) THEN
1994
1995 -- get data from last SUBMIT entry in po_action_history (req submission)
1996
1997 /*Bug 12701382 - In case of Timeout employee id corresponding to last Approve action should be taken.
1998 In case of timeout and document is self approved, employee id corresponding to Submit action should be taken.*/
1999
2000 IF l_action_code = 'NO ACTION' THEN
2001
2002 BEGIN
2003
2004 SELECT employee_id
2005 INTO l_employee_id
2006 FROM po_action_history
2007 WHERE object_id = p_document_id
2008 AND object_type_code = p_document_type
2009 AND sequence_num = (SELECT max(sequence_num)
2010 FROM po_action_history
2011 WHERE object_id = p_document_id
2012 AND action_code = 'APPROVE'
2013 AND Object_Type_Code = P_Document_Type
2014 AND Object_Sub_Type_Code = P_Document_Subtype)
2015 AND object_sub_type_code = p_document_subtype;
2016
2017 EXCEPTION
2018
2019 WHEN No_Data_Found THEN
2020
2021 SELECT employee_id
2022 INTO l_employee_id
2023 FROM po_action_history
2024 WHERE object_id = p_document_id
2025 AND object_type_code = p_document_type
2026 AND sequence_num = (SELECT max(sequence_num)
2027 FROM po_action_history
2028 WHERE object_id = p_document_id
2029 AND action_code = 'SUBMIT'
2030 AND Object_Type_Code = P_Document_Type
2031 AND Object_Sub_Type_Code = P_Document_Subtype)
2032 AND object_sub_type_code = p_document_subtype;
2033
2034 END;
2035
2036 ElSE
2037
2038 SELECT employee_id
2039 INTO l_employee_id
2040 FROM po_action_history
2041 WHERE object_id = p_document_id
2042 AND object_type_code = p_document_type
2043 AND sequence_num = (SELECT max(sequence_num)
2044 FROM Po_Action_History
2045 WHERE object_id = p_document_id
2046 AND action_code = decode(l_action_code, 'SIGNED','SUBMIT',l_action_code) --Bug 13444730
2047 AND Object_Type_Code = P_Document_Type
2048 AND Object_Sub_Type_Code = P_Document_Subtype)
2049 AND object_sub_type_code = p_document_subtype;
2050
2051 END IF;
2052
2053 x_progress := ' PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove employee_id: ' || l_employee_id;
2054
2055 IF (g_po_wf_debug = 'Y') THEN
2056 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, x_progress);
2057 End If;
2058
2059 INSERT into PO_ACTION_HISTORY
2060 (object_id,
2061 object_type_code,
2062 object_sub_type_code,
2063 sequence_num,
2064 last_update_date,
2065 last_updated_by,
2066 creation_date,
2067 created_by,
2068 action_code,
2069 action_date,
2070 employee_id,
2071 note,
2072 object_revision_num,
2073 last_update_login,
2074 request_id,
2075 program_application_id,
2076 program_id,
2077 program_update_date,
2078 approval_path_id,
2079 offline_code)
2080 VALUES
2081 (p_document_id,
2082 p_document_type,
2083 p_document_subtype,
2084 l_sequence_num + 1,
2085 sysdate,
2086 fnd_global.user_id,
2087 sysdate,
2088 fnd_global.user_id,
2089 NULL,
2090 NULL,
2091 l_employee_id,
2092 NULL,
2093 l_object_rev_num,
2094 fnd_global.login_id,
2095 0,
2096 0,
2097 0,
2098 '',
2099 l_approval_path_id,
2100 '' );
2101
2102 commit;
2103
2104 x_progress := 'PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove inserted';
2105
2106 END IF;
2107
2108 x_progress := 'PO_REQAPPROVAL_ACTION.InsertHistForOwnerApprove end';
2109
2110 IF (g_po_wf_debug = 'Y') THEN
2111 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2112 END IF;
2113
2114 EXCEPTION
2115
2116 When Others Then
2117 wf_core.context('PO_REQAPPROVAL_ACTION','ApproveDoc',x_progress || ', ' || sqlerrm);
2118 raise;
2119
2120 END InsertHistForOwnerApprove;
2121
2122 --
2123 FUNCTION ApproveDoc(itemtype varchar2, itemkey varchar2)
2124 RETURN VARCHAR2
2125 IS
2126
2127 -- <Doc Manager Rewrite 11.5.11 Start>
2128 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
2129 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
2130 l_document_id NUMBER;
2131 l_note PO_ACTION_HISTORY.note%TYPE;
2132 l_approval_path_id NUMBER;
2133
2134
2135 l_ret_sts VARCHAR2(1);
2136 l_exc_msg VARCHAR2(2000);
2137 -- <Doc Manager Rewrite 11.5.11 End>
2138
2139
2140 l_kanban_return_status varchar2(10);
2141 x_progress varchar2(200);
2142
2143 BEGIN
2144
2145 -- <Doc Manager Rewrite 11.5.11 Start>
2146
2147 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2148 itemkey => itemkey,
2149 aname => 'DOCUMENT_TYPE');
2150
2151 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2152 itemkey => itemkey,
2153 aname => 'DOCUMENT_SUBTYPE');
2154
2155 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2156 itemkey => itemkey,
2157 aname => 'DOCUMENT_ID');
2158
2159
2160 l_note := wf_engine.GetItemAttrText (itemtype => itemtype,
2161 itemkey => itemkey,
2162 aname => 'NOTE');
2163
2164 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2165 itemkey => itemkey,
2166 aname => 'APPROVAL_PATH_ID');
2167
2168
2169 x_progress := 'ApproveDoc: calling ApproveAutonomous with: '
2170 || 'Doc_type= ' || l_document_type
2171 || ' Subtype= ' || l_document_subtype
2172 || ' Doc_id= ' || to_char(l_document_id);
2173
2174 IF (g_po_wf_debug = 'Y') THEN
2175 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2176 END IF;
2177
2178 ApproveAutonomous(
2179 p_document_id => l_document_id
2180 , p_document_type => l_document_type
2181 , p_document_subtype => l_document_subtype
2182 , p_note => l_note
2183 , p_approval_path_id => l_approval_path_id
2184 , x_return_status => l_ret_sts
2185 , x_exception_msg => l_exc_msg
2186 );
2187
2188 -- Approve returns with only 'S' or 'U'
2189 IF (l_ret_sts = 'S')
2190 THEN
2191
2192 /* Keep the AUTHORIZATION_STATUS in sync with database */
2193 wf_engine.SetItemAttrText ( itemtype => itemType,
2194 itemkey => itemkey,
2195 aname => 'AUTHORIZATION_STATUS',
2196 avalue => 'APPROVED');
2197
2198 IF ((l_document_type IN ('PO','RELEASE') ) OR
2199 (l_document_type = 'REQUISITION' AND l_document_subtype = 'INTERNAL'))
2200 THEN
2201
2202 -- Support for Kanban Execution
2203 -- When document is approved, update Kanban status to 'IN_PROCESS'
2204
2205 PO_KANBAN_SV.Update_Card_Status ('IN_PROCESS',
2206 l_document_type,
2207 l_document_id,
2208 l_kanban_return_status);
2209
2210 END IF;
2211
2212 return('Y');
2213
2214 ELSE
2215
2216 x_progress := 'PO_REQAPPROVAL_ACTION.ApproveDoc: ApproveAutonomous returned with: '
2217 || l_ret_sts;
2218
2219 IF (g_po_wf_debug = 'Y') THEN
2220 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2221 END IF;
2222
2223 doc_mgr_err_num := 3;
2224 sysadmin_err_msg := l_exc_msg;
2225
2226 return('F');
2227
2228 END IF; -- l_ret_sts = 'S'
2229
2230 -- <Doc Manager Rewrite 11.5.11 End>
2231
2232 EXCEPTION
2233
2234 WHEN OTHERS THEN
2235 wf_core.context('PO_REQAPPROVAL_ACTION','ApproveDoc',x_progress);
2236 raise;
2237
2238 END ApproveDoc;
2239
2240 --
2241 FUNCTION ApproveAndForward(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
2242
2243 -- <Doc Manager Rewrite 11.5.11 Start>
2244 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
2245 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
2246 l_document_id NUMBER;
2247 l_note PO_ACTION_HISTORY.note%TYPE;
2248 l_approval_path_id NUMBER;
2249 l_forward_to_id NUMBER;
2250
2251 l_ret_sts VARCHAR2(1);
2252 l_exc_msg VARCHAR2(2000);
2253 -- <Doc Manager Rewrite 11.5.11 End>
2254
2255 x_progress varchar2(200);
2256
2257 BEGIN
2258
2259 -- <Doc Manager Rewrite 11.5.11 Start>
2260
2261 /* The action FORWARD_DOCUMENT creates a new row in PO_ACTION_HISTORY
2262 ** with an action_code that is NULL and it sets the status on the
2263 ** DOCUMENT to 'PRE-APPROVED' (PO_HEADERS, REQs or RELEASES).
2264 */
2265
2266 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2267 itemkey => itemkey,
2268 aname => 'DOCUMENT_TYPE');
2269
2270 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2271 itemkey => itemkey,
2272 aname => 'DOCUMENT_SUBTYPE');
2273
2274 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2275 itemkey => itemkey,
2276 aname => 'DOCUMENT_ID');
2277
2278
2279 l_note := wf_engine.GetItemAttrText (itemtype => itemtype,
2280 itemkey => itemkey,
2281 aname => 'NOTE');
2282
2283 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2284 itemkey => itemkey,
2285 aname => 'APPROVAL_PATH_ID');
2286
2287 l_forward_to_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2288 itemkey => itemkey,
2289 aname => 'FORWARD_TO_ID');
2290
2291
2292 x_progress := 'ApproveAndForwardDoc: calling ForwardAutonomous with: ' || 'Doc_type= ' ||
2293 l_document_type || ' Subtype= ' || l_document_subtype ||
2294 ' Doc_id= ' || to_char(l_document_id);
2295
2296 IF (g_po_wf_debug = 'Y') THEN
2297 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2298 END IF;
2299
2300 ForwardAutonomous(
2301 p_document_id => l_document_id
2302 , p_document_type => l_document_type
2303 , p_document_subtype => l_document_subtype
2304 , p_new_doc_status => PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED
2305 , p_note => l_note
2306 , p_approval_path_id => l_approval_path_id
2307 , p_forward_to_id => l_forward_to_id
2308 , x_return_status => l_ret_sts
2309 , x_exception_msg => l_exc_msg
2310 );
2311
2312
2313 -- Forward returns with only 'S' or 'U'
2314 IF (l_ret_sts = 'S') THEN
2315
2316 /* Keep the AUTHORIZATION_STATUS in sync with database */
2317 wf_engine.SetItemAttrText ( itemtype => itemType,
2318 itemkey => itemkey,
2319 aname => 'AUTHORIZATION_STATUS',
2320 avalue => 'PRE-APPROVED');
2321
2322 return('Y');
2323
2324 ELSE
2325
2326 -- fatal exception, l_ret_sts = 'U'
2327
2328 x_progress := 'PO_REQAPPROVAL_ACTION.ApproveAndForward: ForwardAutonomous returned with: ' || l_ret_sts;
2329 IF (g_po_wf_debug = 'Y') THEN
2330 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2331 END IF;
2332
2333 doc_mgr_err_num := 3;
2334 sysadmin_err_msg := l_exc_msg;
2335
2336 return('F');
2337
2338 END IF;
2339
2340 -- <Doc Manager Rewrite 11.5.11 End>
2341
2342 EXCEPTION
2343
2344 WHEN OTHERS THEN
2345 wf_core.context('PO_REQAPPROVAL_ACTION','ApproveAndForward',x_progress);
2346 raise;
2347
2348 END ApproveAndForward;
2349
2350 --
2351 FUNCTION ForwardDocInProcess(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
2352
2353 -- <Doc Manager Rewrite 11.5.11 Start>
2354
2355 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
2356 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
2357 l_document_id NUMBER;
2358 l_note PO_ACTION_HISTORY.note%TYPE;
2359 l_approval_path_id NUMBER;
2360 l_forward_to_id NUMBER;
2361
2362 l_ret_sts VARCHAR2(1);
2363 l_exc_msg VARCHAR2(2000);
2364
2365 -- <Doc Manager Rewrite 11.5.11 End>
2366
2367 x_progress varchar2(200);
2368 BEGIN
2369
2370 -- <Doc Manager Rewrite 11.5.11 Start>
2371
2372 /* The action FORWARD_DOCUMENT creates a new row in PO_ACTION_HISTORY
2373 ** with an action_code that is NULL and it sets the status on the
2374 ** DOCUMENT to 'IN PROCESS' (PO_HEADERS, REQs or RELEASES).
2375 */
2376
2377 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2378 itemkey => itemkey,
2379 aname => 'DOCUMENT_TYPE');
2380
2381 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2382 itemkey => itemkey,
2383 aname => 'DOCUMENT_SUBTYPE');
2384
2385 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2386 itemkey => itemkey,
2387 aname => 'DOCUMENT_ID');
2388
2389
2390 l_note := wf_engine.GetItemAttrText (itemtype => itemtype,
2391 itemkey => itemkey,
2392 aname => 'NOTE');
2393
2394 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2395 itemkey => itemkey,
2396 aname => 'APPROVAL_PATH_ID');
2397
2398 l_forward_to_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2399 itemkey => itemkey,
2400 aname => 'FORWARD_TO_ID');
2401
2402
2403 x_progress := 'ForwardDocInProcess: calling ForwardAutonomous with: ' || 'Doc_type= ' ||
2404 l_document_type || ' Subtype= ' || l_document_subtype ||
2405 ' Doc_id= ' || to_char(l_document_id);
2406
2407 IF (g_po_wf_debug = 'Y') THEN
2408 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2409 END IF;
2410
2411 ForwardAutonomous(
2412 p_document_id => l_document_id
2413 , p_document_type => l_document_type
2414 , p_document_subtype => l_document_subtype
2415 , p_new_doc_status => PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS
2416 , p_note => l_note
2417 , p_approval_path_id => l_approval_path_id
2418 , p_forward_to_id => l_forward_to_id
2419 , x_return_status => l_ret_sts
2420 , x_exception_msg => l_exc_msg
2421 );
2422
2423 -- Forward returns with only 'S' or 'U'
2424 IF (l_ret_sts = 'S') THEN
2425
2426 /* Keep the AUTHORIZATION_STATUS in sync with database */
2427 wf_engine.SetItemAttrText ( itemtype => itemType,
2428 itemkey => itemkey,
2429 aname => 'AUTHORIZATION_STATUS',
2430 avalue => 'IN PROCESS');
2431
2432 return('Y');
2433
2434 ELSE
2435
2436 -- fatal exception, l_ret_sts = 'U'
2437
2438 x_progress := 'PO_REQAPPROVAL_ACTION.ForwardDocInProcess: ForwardAutonomous returned with: ' || l_ret_sts;
2439 IF (g_po_wf_debug = 'Y') THEN
2440 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2441 END IF;
2442
2443 doc_mgr_err_num := 3;
2444 sysadmin_err_msg := l_exc_msg;
2445
2446 return('F');
2447
2448 END IF; -- l_ret_sts = 'S';
2449
2450 -- <Doc Manager Rewrite 11.5.11 End>
2451
2452 EXCEPTION
2453
2454 WHEN OTHERS THEN
2455 wf_core.context('PO_REQAPPROVAL_ACTION','ForwardDocInProcess',x_progress);
2456 raise;
2457
2458 END ForwardDocInProcess;
2459
2460 --
2461 FUNCTION ForwardDocPreApproved(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
2462
2463 -- <Doc Manager Rewrite 11.5.11 Start>
2464
2465 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
2466 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
2467 l_document_id NUMBER;
2468 l_note PO_ACTION_HISTORY.note%TYPE;
2469 l_approval_path_id NUMBER;
2470 l_forward_to_id NUMBER;
2471
2472 l_ret_sts VARCHAR2(1);
2473 l_exc_msg VARCHAR2(2000);
2474
2475 -- <Doc Manager Rewrite 11.5.11 End>
2476
2477 x_progress varchar2(200);
2478
2479 BEGIN
2480
2481 -- <Doc Manager Rewrite 11.5.11 Start>
2482
2483 /* The action FORWARD_DOCUMENT creates a new row in PO_ACTION_HISTORY
2484 ** with an action_code that is NULL and it sets the status on the
2485 ** DOCUMENT to 'PRE-APPROVED' (PO_HEADERS, REQs or RELEASES).
2486 */
2487
2488 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2489 itemkey => itemkey,
2490 aname => 'DOCUMENT_TYPE');
2491
2492 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2493 itemkey => itemkey,
2494 aname => 'DOCUMENT_SUBTYPE');
2495
2496 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2497 itemkey => itemkey,
2498 aname => 'DOCUMENT_ID');
2499
2500
2501 l_note := wf_engine.GetItemAttrText (itemtype => itemtype,
2502 itemkey => itemkey,
2503 aname => 'NOTE');
2504
2505 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2506 itemkey => itemkey,
2507 aname => 'APPROVAL_PATH_ID');
2508
2509 l_forward_to_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2510 itemkey => itemkey,
2511 aname => 'FORWARD_TO_ID');
2512
2513
2514 x_progress := 'ForwardDocPreapproved: calling ForwardAutonomous with: ' || 'Doc_type= ' ||
2515 l_document_type || ' Subtype= ' || l_document_subtype ||
2516 ' Doc_id= ' || to_char(l_document_id);
2517
2518 IF (g_po_wf_debug = 'Y') THEN
2519 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2520 END IF;
2521
2522 ForwardAutonomous(
2523 p_document_id => l_document_id
2524 , p_document_type => l_document_type
2525 , p_document_subtype => l_document_subtype
2526 , p_new_doc_status => PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED
2527 , p_note => l_note
2528 , p_approval_path_id => l_approval_path_id
2529 , p_forward_to_id => l_forward_to_id
2530 , x_return_status => l_ret_sts
2531 , x_exception_msg => l_exc_msg
2532 );
2533
2534 -- Forward returns with only 'S' or 'U'
2535 IF (l_ret_sts = 'S') THEN
2536
2537 /* Keep the AUTHORIZATION_STATUS in sync with database */
2538 wf_engine.SetItemAttrText ( itemtype => itemType,
2539 itemkey => itemkey,
2540 aname => 'AUTHORIZATION_STATUS',
2541 avalue => 'PRE-APPROVED');
2542
2543 return('Y');
2544
2545 ELSE
2546
2547 -- fatal exception, l_ret_sts = 'U'
2548
2549 x_progress := 'PO_REQAPPROVAL_ACTION.ForwardDocPreApproved: ForwardAutonomous returned with: ' || l_ret_sts;
2550 IF (g_po_wf_debug = 'Y') THEN
2551 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2552 END IF;
2553
2554 doc_mgr_err_num := 3;
2555 sysadmin_err_msg := l_exc_msg;
2556
2557 return('F');
2558
2559 END IF;
2560
2561 -- <Doc Manager Rewrite 11.5.11 End>
2562
2563 EXCEPTION
2564
2565 WHEN OTHERS THEN
2566 wf_core.context('PO_REQAPPROVAL_ACTION','ForwardDocPreApproved',x_progress);
2567 raise;
2568
2569
2570 END ForwardDocPreApproved;
2571
2572 --
2573 FUNCTION RejectDoc(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
2574
2575 -- <Doc Manager Rewrite 11.5.11 Start>
2576 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
2577 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
2578 l_document_id NUMBER;
2579 l_note PO_ACTION_HISTORY.note%TYPE;
2580 l_approval_path_id NUMBER;
2581
2582 l_ret_sts VARCHAR2(1);
2583 l_exc_msg VARCHAR2(2000);
2584 l_ret_code VARCHAR2(25);
2585 l_online_report_id NUMBER;
2586 -- <Doc Manager Rewrite 11.5.11 End>
2587
2588 x_progress varchar2(200);
2589 l_caller varchar2(25); -- for bug 14742082 changing size from 20 to 25
2590 -- CLM Apprvl
2591 l_draft_id NUMBER;
2592
2593 BEGIN
2594
2595 -- <Doc Manager Rewrite 11.5.11 Start>
2596
2597 l_caller := wf_engine.GetItemAttrText (itemtype => itemtype,
2598 itemkey => itemkey,
2599 aname => 'INTERFACE_SOURCE_CODE');
2600
2601
2602 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2603 itemkey => itemkey,
2604 aname => 'DOCUMENT_TYPE');
2605
2606 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2607 itemkey => itemkey,
2608 aname => 'DOCUMENT_SUBTYPE');
2609
2610 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2611 itemkey => itemkey,
2612 aname => 'DOCUMENT_ID');
2613
2614
2615 l_note :=wf_engine.GetItemAttrText (itemtype => itemtype,
2616 itemkey => itemkey,
2617 aname => 'NOTE');
2618
2619 l_approval_path_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2620 itemkey => itemkey,
2621 aname => 'APPROVAL_PATH_ID');
2622
2623 -- CLM Apprvl
2624 l_draft_id := PO_WF_UTIL_PKG.GetItemAttrNumber (itemtype => itemtype,
2625 itemkey => itemkey,
2626 aname => 'DRAFT_ID');
2627 If l_draft_id is Null Then
2628 l_draft_id := -1;
2629 end if;
2630
2631
2632 -- On reject, the Pro*C code was hard coded to set the status
2633 -- of the document to rejected. Hence, the following code was
2634 -- meaningless; commenting it out.
2635 --
2636 -- If the Requisition comes from Web Reqs, then when the approver
2637 -- rejects it, we set the status to 'CANCELLED'. If it comes from
2638 -- any other system, then set it to REJECTED.
2639 --
2640 -- IF l_caller = 'ICX' THEN
2641 --
2642 -- l_reject_status := 'CANCELLED';
2643 --
2644 -- ELSE
2645 --
2646 -- l_reject_status := 'REJECTED';
2647 --
2648 -- END IF;
2649
2650 x_progress := 'RejectDoc: calling RejectAutonomous with: ' || 'Doc_type= ' ||
2651 l_document_type || ' Subtype= ' || l_document_subtype ||
2652 ' Doc_id= ' || to_char(l_document_id);
2653
2654 IF (g_po_wf_debug = 'Y') THEN
2655 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2656 END IF;
2657
2658
2659 RejectAutonomous(
2660 p_document_id => l_document_id
2661 , p_draft_id => l_draft_id -- CLM Apprvl
2662 , p_document_type => l_document_type
2663 , p_document_subtype => l_document_subtype
2664 , p_note => l_note
2665 , p_approval_path_id => l_approval_path_id
2666 , x_return_status => l_ret_sts
2667 , x_return_code => l_ret_code
2668 , x_exception_msg => l_exc_msg
2669 , x_online_report_id => l_online_report_id
2670 );
2671
2672 -- Reject returns with 'S' or 'U'
2673 IF (l_ret_sts = 'S')
2674 THEN
2675
2676 -- If reject succeeded, then l_ret_code is null or 'A' or 'S'
2677
2678 IF ((l_ret_code IS NULL) OR (l_ret_code IN ('A', 'S')))
2679 THEN
2680
2681 -- Reject Succeeded
2682
2683 /* Keep the AUTHORIZATION_STATUS in sync with database */
2684 wf_engine.SetItemAttrText ( itemtype => itemType,
2685 itemkey => itemkey,
2686 aname => 'AUTHORIZATION_STATUS',
2687 avalue => 'REJECTED');
2688
2689 return('Y');
2690
2691 ELSE
2692
2693 -- Reject Failed
2694
2695 x_progress := 'PO_REQAPPROVAL_ACTION.RejectDoc: Returned_code= ' ||
2696 l_ret_code ;
2697 IF (g_po_wf_debug = 'Y') THEN
2698 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2699 END IF;
2700
2701 return('N');
2702
2703 END IF; -- l_ret_code IS NULL
2704
2705 ELSE
2706
2707 -- Fatal Error: l_ret_sts := 'U';
2708
2709 x_progress := 'PO_REQAPPROVAL_ACTION.RejectDoc: RejectAutonomous returned with: ' || l_ret_sts;
2710
2711 IF (g_po_wf_debug = 'Y') THEN
2712 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2713 END IF;
2714
2715
2716 doc_mgr_err_num := 3;
2717 sysadmin_err_msg := l_exc_msg ;
2718
2719 return('F');
2720
2721 END IF; -- If l_ret_sts = 'S'
2722
2723 -- <Doc Manager Rewrite 11.5.11 End>
2724
2725 EXCEPTION
2726
2727 WHEN OTHERS THEN
2728 wf_core.context('PO_REQAPPROVAL_ACTION','RejectDoc',x_progress);
2729 raise;
2730
2731 END RejectDoc;
2732
2733 --
2734 FUNCTION VerifyAuthority(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
2735
2736 -- <Doc Manager Rewrite 11.5.11 Start>
2737
2738 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
2739 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
2740 l_document_id NUMBER;
2741 l_employee_id NUMBER;
2742
2743 l_ret_sts VARCHAR2(1);
2744 l_exc_msg VARCHAR2(2000);
2745 l_fail_msg VARCHAR2(2000);
2746 l_ret_code VARCHAR2(25);
2747
2748 -- <Doc Manager Rewrite 11.5.11 End>
2749
2750 x_progress varchar2(200);
2751 BEGIN
2752
2753 -- <Doc Manager Rewrite 11.5.11 Start>
2754
2755 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2756 itemkey => itemkey,
2757 aname => 'DOCUMENT_TYPE');
2758
2759 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2760 itemkey => itemkey,
2761 aname => 'DOCUMENT_SUBTYPE');
2762
2763 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2764 itemkey => itemkey,
2765 aname => 'DOCUMENT_ID');
2766
2767 l_employee_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2768 itemkey => itemkey,
2769 aname => 'APPROVER_EMPID');
2770
2771 x_progress := 'VerifyAuthority: calling verify_authority with: ' || 'Doc_type= ' ||
2772 l_document_type || ' Subtype= ' || l_document_subtype ||
2773 ' Doc_id= ' || to_char(l_document_id);
2774
2775 IF (g_po_wf_debug = 'Y') THEN
2776 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2777 END IF;
2778
2779
2780 PO_DOCUMENT_ACTION_PVT.verify_authority(
2781 p_document_id => l_document_id
2782 , p_document_type => l_document_type
2783 , p_document_subtype => l_document_subtype
2784 , p_employee_id => l_employee_id
2785 , x_return_status => l_ret_sts
2786 , x_return_code => l_ret_code
2787 , x_exception_msg => l_exc_msg
2788 , x_auth_failed_msg => l_fail_msg
2789 );
2790
2791 -- verify_authority sets return status to 'S' or 'U'
2792 IF (l_ret_sts = 'S')
2793 THEN
2794
2795 /* If authority check passed, then l_ret_code should be null
2796 ** otherwise it should be 'AUTHORIZATION_FAILED'.
2797 */
2798 IF ( l_ret_code is NULL )
2799 THEN
2800 return('Y');
2801 ELSE
2802 return('N');
2803 END IF;
2804
2805 ELSE
2806
2807 -- fatal exceptionl; l_ret_sts := 'U';
2808
2809 x_progress := 'PO_REQAPPROVAL_ACTION.VerifyAuthority: action call returned with: ' || l_ret_sts;
2810 IF (g_po_wf_debug = 'Y') THEN
2811 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2812 END IF;
2813
2814 doc_mgr_err_num := 3;
2815 sysadmin_err_msg := l_exc_msg;
2816
2817 return('F');
2818
2819 END IF; -- l_ret_sts = 'S'
2820
2821 -- <Doc Manager Rewrite 11.5.11 End>
2822
2823 EXCEPTION
2824
2825 WHEN OTHERS THEN
2826 wf_core.context('PO_REQAPPROVAL_ACTION','VerifyAuthority',x_progress);
2827 raise;
2828
2829 END VerifyAuthority;
2830
2831
2832 --
2833 FUNCTION OpenDocumentState(itemtype VARCHAR2, itemkey VARCHAR2) RETURN VARCHAR2 is
2834
2835 -- <Doc Manager Rewrite 11.5.11 Start>
2836 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
2837 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
2838 l_document_id NUMBER;
2839
2840 l_ret_code VARCHAR2(25);
2841 l_ret_sts VARCHAR2(1);
2842 l_exc_msg VARCHAR2(2000);
2843 -- <Doc Manager Rewrite 11.5.11 End>
2844
2845 x_progress varchar2(200);
2846
2847 BEGIN
2848
2849 -- <Doc Manager Rewrite 11.5.11 Start>
2850
2851 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2852 itemkey => itemkey,
2853 aname => 'DOCUMENT_TYPE');
2854
2855 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
2856 itemkey => itemkey,
2857 aname => 'DOCUMENT_SUBTYPE');
2858
2859 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2860 itemkey => itemkey,
2861 aname => 'DOCUMENT_ID');
2862
2863 x_progress := 'OpenDocumentState: calling autoupdatecloseautonomous with: ' || 'Doc_type= ' ||
2864 l_document_type || ' Subtype= ' || l_document_subtype ||
2865 ' Doc_id= ' || to_char(l_document_id);
2866
2867 IF (g_po_wf_debug = 'Y') THEN
2868 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2869 END IF;
2870
2871 AutoUpdateCloseAutonomous(
2872 p_document_id => l_document_id
2873 , p_document_type => l_document_type
2874 , p_document_subtype => l_document_subtype
2875 , x_return_status => l_ret_sts
2876 , x_exception_msg => l_exc_msg
2877 , x_return_code => l_ret_code
2878 );
2879
2880 IF (l_ret_sts = 'S')
2881 THEN
2882
2883 IF (l_ret_code IS NULL)
2884 THEN
2885
2886 return('Y');
2887
2888 ELSE
2889
2890 x_progress := 'PO_REQAPPROVAL_ACTION.OpenDocumentState: Returned_code= ' ||
2891 l_ret_code ;
2892 IF (g_po_wf_debug = 'Y') THEN
2893 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2894 END IF;
2895
2896 return('N');
2897
2898 END IF; -- if l_ret_code IS NULL
2899
2900 ELSE
2901
2902 -- Fatal Error: l_ret_sts := 'U';
2903
2904 /* something went wrong with Doc Action. Send notification to Sys Admin.
2905 ** The error message is kept in Item Attribute SYSADMIN_ERROR_MSG */
2906
2907 x_progress := 'PO_REQAPPROVAL_ACTION.OpenDocumentState: auto_close_update_autonomous returned with: ' ||
2908 l_ret_sts;
2909 IF (g_po_wf_debug = 'Y') THEN
2910 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2911 END IF;
2912
2913 doc_mgr_err_num := 3;
2914 sysadmin_err_msg := l_exc_msg;
2915
2916 return('F');
2917
2918 END IF; -- IF l_ret_sts = 'S'
2919
2920 EXCEPTION
2921
2922 WHEN OTHERS THEN
2923 wf_core.context('PO_REQAPPROVAL_ACTION','OpenDocumentState',x_progress);
2924 raise;
2925
2926 END OpenDocumentState;
2927
2928 --
2929
2930 --
2931 /* Bug# 2234341 */
2932
2933 -- <ENCUMBRANCE FPJ START>
2934 -- Modify the procedure to call FPJ Encumbrance
2935
2936 FUNCTION ReserveDoc(itemtype VARCHAR2, itemkey VARCHAR2,
2937 p_override_funds VARCHAR2 default 'N')
2938 RETURN VARCHAR2 is
2939
2940 -- BUG 6334215
2941 -- Approval workflow was failing in the RESERVE_DOCUMENT
2942 -- process. Approval failed because x_progress which is defined as
2943 -- varchar2(200) was unable to hold the debug message
2944
2945 x_progress varchar2(1000);
2946
2947 l_doc_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
2948 l_doc_subtype PO_DOCUMENT_TYPES.document_subtype%TYPE;
2949 l_doc_id NUMBER;
2950 l_employee_id NUMBER;
2951 l_po_return_code VARCHAR2(10);
2952 l_online_report_id NUMBER;
2953 l_warning_mesg VARCHAR2(2000) := NULL;
2954 l_return VARCHAR2(1) := 'N';
2955 l_is_ame_used varchar2(1) := 'N';
2956 BEGIN
2957
2958 x_progress := 'ReserveDoc 001';
2959 IF (g_po_wf_debug = 'Y') THEN
2960 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2961 END IF;
2962
2963 l_doc_type := wf_engine.GetItemAttrText(
2964 itemtype => itemtype,
2965 itemkey => itemkey,
2966 aname => 'DOCUMENT_TYPE');
2967
2968 l_doc_subtype := wf_engine.GetItemAttrText(
2969 itemtype => itemtype,
2970 itemkey => itemkey,
2971 aname => 'DOCUMENT_SUBTYPE');
2972
2973 x_progress := 'ReserveDoc 010: '|| 'Doc Type = '|| l_doc_type ||
2974 ' Doc Subtype = '|| l_doc_subtype;
2975 IF (g_po_wf_debug = 'Y') THEN
2976 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2977 END IF;
2978
2979 -- If the Document type is Contract Agreement, then don't reserve
2980
2981 IF l_doc_subtype = 'CONTRACT' THEN
2982
2983 return('Y');
2984
2985 END IF;
2986
2987 l_doc_id := wf_engine.GetItemAttrNumber(
2988 itemtype => itemtype,
2989 itemkey => itemkey,
2990 aname => 'DOCUMENT_ID');
2991
2992 x_progress := 'ReserveDoc 020: ' || 'Doc Header Id = ' || to_char(l_doc_id);
2993 IF (g_po_wf_debug = 'Y') THEN
2994 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
2995 END IF;
2996
2997 IF (l_doc_type = 'REQUISITION') THEN
2998
2999 l_is_ame_used := wf_engine.GetItemAttrText(
3000 itemtype => itemtype,
3001 itemkey => itemkey,
3002 aname => 'IS_AME_APPROVAL');
3003
3004
3005
3006 IF(l_is_ame_used ='Y') THEN
3007
3008 l_employee_id := wf_engine.GetItemAttrNumber(
3009 itemtype => itemtype,
3010 itemkey => itemkey,
3011 aname => 'APPROVER_EMPID');
3012
3013 ELSE
3014
3015 l_employee_id := wf_engine.GetItemAttrNumber(
3016 itemtype => itemtype,
3017 itemkey => itemkey,
3018 aname => 'RESPONDER_ID');
3019
3020 END IF;
3021
3022 END IF;
3023
3024 IF (l_employee_id IS NULL) THEN
3025 l_employee_id := wf_engine.GetItemAttrNumber(
3026 itemtype => itemtype,
3027 itemkey => itemkey,
3028 aname => 'APPROVER_EMPID');
3029 END IF;
3030
3031 x_progress := 'ReserveDoc 025: ' || 'l_is_ame_used = ' ||l_is_ame_used || ' l_employee_id ='|| to_char(l_employee_id);
3032 IF (g_po_wf_debug = 'Y') THEN
3033 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3034 END IF;
3035 -- Call the ReserveAutonomous
3036 ReserveAutonomous(
3037 p_doc_type => l_doc_type
3038 , p_doc_subtype => l_doc_subtype
3039 , p_doc_id => l_doc_id
3040 , p_override_funds => p_override_funds
3041 , p_employee_id => l_employee_id
3042 , x_po_return_code => l_po_return_code
3043 , x_online_report_id => l_online_report_id
3044 );
3045
3046 x_progress := 'ReserveDoc 030: ReserveAutonomous return code = ' ||
3047 l_po_return_code;
3048 IF (g_po_wf_debug = 'Y') THEN
3049 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3050 END IF;
3051
3052 IF (l_po_return_code = PO_DOCUMENT_FUNDS_PVT.g_return_SUCCESS) THEN
3053
3054 l_return := 'Y';
3055
3056 ELSIF (l_po_return_code = PO_DOCUMENT_FUNDS_PVT.g_return_WARNING) THEN
3057
3058 -- Also set the online report id for warnings
3059 wf_engine.SetItemAttrNumber(
3060 itemtype => itemType,
3061 itemkey => itemkey,
3062 aname => 'ONLINE_REPORT_ID',
3063 avalue => l_online_report_id);
3064
3065
3066 -- Get warning message off of stack.
3067 -- Bug 3518326: Since we pass l_warning_mesg directly
3068 -- into the 'ADVISORY WARNING' wf attribute below, we
3069 -- have to make sure that we decode it. Hence, pass
3070 -- p_encoded = FND_API.G_FALSE to FND_MSG_PUB.get().
3071
3072 l_warning_mesg := FND_MSG_PUB.get(p_encoded => FND_API.G_FALSE);
3073
3074 -- Set the warning message to workflow attribute ADVISORY_WARNING.
3075 -- If there is no message then it will be set to null
3076 -- Bug 3536831: Call new get_advisory_warning procedure so that the
3077 -- contents of the online report are also shown as part of the warning
3078 -- in the notification to preparer. This is a new procedure.
3079 -- Read note below about why the setting of this attribute was moved here from below.
3080
3081 x_progress := 'ReserveDoc 100 Advisory Message: '||l_warning_mesg;
3082 IF (g_po_wf_debug = 'Y') THEN
3083 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3084 END IF;
3085
3086 get_advisory_warning( itemtype => itemType
3087 , itemkey => itemkey
3088 , p_online_report_id => l_online_report_id
3089 , p_warning_header_text => l_warning_mesg
3090 );
3091
3092 l_return := 'Y';
3093
3094 ELSE
3095
3096 -- Get the online_report_id (to be used to populate the notification
3097 wf_engine.SetItemAttrNumber(
3098 itemtype => itemType,
3099 itemkey => itemkey,
3100 aname => 'ONLINE_REPORT_ID',
3101 avalue => l_online_report_id);
3102
3103 -- Get the text of the online_report and store in workflow item
3104 -- attribute
3105 get_online_report_text( itemtype, itemkey,l_online_report_id);
3106
3107 l_return := 'N';
3108
3109 END IF; -- IF l_po_return_code
3110
3111 -- Bug 3536831: The setting of the advisory_warning attribute is removed
3112 -- from here, and moved above. This attribute is now set only if there
3113 -- actually was a warning. Previously, the attribute would be set to NULL
3114 -- if there wasn't a warning. This should not changed by removing the
3115 -- code here, as now, the attribute is not set, and will default to NULL.
3116 -- The move was made because otherwise, the new implementation would put
3117 -- online report text information into advisory_warning even if there was
3118 -- no warning.
3119
3120 x_progress := 'ReserveDoc 999';
3121 IF (g_po_wf_debug = 'Y') THEN
3122 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3123 END IF;
3124
3125 return(l_return);
3126
3127 EXCEPTION
3128
3129 WHEN OTHERS THEN
3130 wf_core.context('PO_REQAPPROVAL_ACTION','ReserveDoc',x_progress);
3131
3132 /*
3133 Bug 9886447
3134 When exception is raised in function ReserveDoc it does not return proper
3135 value and hence, parent function i.e. Reserve_doc does not get correct value
3136 to return. Hence, putting return in exception block so that Reserve_doc function
3137 will have proper value to return.
3138 If Reserve_doc does not get proper value to return, approval work flow gets stuck.
3139 */
3140
3141 return(l_return);
3142
3143
3144 END ReserveDoc;
3145
3146 -- <ENCUMBRANCE FPJ END>
3147
3148 --
3149
3150
3151 PROCEDURE get_online_report_text(itemtype VARCHAR2, itemkey VARCHAR2, p_online_report_id NUMBER) is
3152
3153 x_progress varchar2(400);
3154
3155 BEGIN
3156
3157 -- Bug 3536831: get_online_report_text is refactored to call new set_report_text_attr.
3158
3159 x_progress := 'PO_REQAPPROVAL_ACTION.get_online_report_text.010.ON_LINE_REPORT_ID= '
3160 || to_char(p_online_report_id);
3161
3162
3163 set_report_text_attr( itemtype => itemtype
3164 , itemkey => itemkey
3165 , p_online_report_id => p_online_report_id
3166 , p_attribute => 'ONLINE_REPORT_TEXT'
3167 );
3168
3169 x_progress := 'PO_REQAPPROVAL_ACTION.get_online_report_text.020.ON_LINE_REPORT_ID= '
3170 || to_char(p_online_report_id);
3171
3172 EXCEPTION
3173 WHEN OTHERS THEN
3174 wf_core.context('PO_REQAPPROVAL_ACTION','get_online_report_text',x_progress);
3175 raise;
3176 END get_online_report_text;
3177
3178
3179 -- <Start Bug 3536831: Added get_advisory_warning and set_report_text_attr>
3180
3181 -------------------------------------------------------------------------------
3182 --Start of Comments
3183 --Name: get_advisory_warning
3184 --Function:
3185 -- Sets the workflow attribute "ADVISORY_WARNING" to the text of the online
3186 -- report provided. It is valid for this attribute to be set to NULL.
3187 --Parameters:
3188 --IN:
3189 --itemtype
3190 -- Workflow itemtype. This should be called only from the PO Approval workflow.
3191 --itemkey
3192 -- Workflow itemkey. The key for the particular PO Approval workflow process
3193 -- that the warning was generated in.
3194 --p_online_report_id
3195 -- The id of the report from which to copy the text. This may be NULL.
3196 --p_warning_header_text
3197 -- A header string that should be appended before the text of the online report.
3198 -- This may be NULL.
3199 --End of Comments
3200 -------------------------------------------------------------------------------
3201 PROCEDURE get_advisory_warning(
3202 itemtype IN VARCHAR2
3203 , itemkey IN VARCHAR2
3204 , p_online_report_id IN NUMBER
3205 , p_warning_header_text IN VARCHAR2
3206 )
3207 IS
3208
3209 x_progress VARCHAR2(400);
3210 l_document_type po_document_types_all.document_type_code%TYPE;--<BUG 7361295>
3211
3212 BEGIN
3213
3214 x_progress := 'PO_REQAPPROVAL_ACTION.get_advisory_warning.010.ON_LINE_REPORT_ID= '
3215 || to_char(p_online_report_id);
3216
3217
3218 set_report_text_attr( itemtype => itemtype
3219 , itemkey => itemkey
3220 , p_online_report_id => p_online_report_id
3221 , p_attribute => 'ADVISORY_WARNING'
3222 , p_header_text => p_warning_header_text
3223 );
3224
3225 -- <BUG 7361295>
3226 l_document_type := wf_engine.GetItemAttrText(itemtype => itemType,
3227 itemkey => itemkey,
3228 aname => 'DOCUMENT_TYPE');
3229
3230
3231 IF (l_document_type = 'REQUISITION') THEN -- <BUG 7361295>
3232 -- Set the workflow attribute Advisory Warning Check to 'Y'
3233 PO_WF_UTIL_PKG.SetItemAttrText(itemtype => itemType,
3234 itemkey => itemkey,
3235 aname => 'ADVISORY_WARNING_CHECK',
3236 avalue => 'Y');
3237 END IF;
3238
3239 x_progress := 'PO_REQAPPROVAL_ACTION.get_advisory_warning.020.ON_LINE_REPORT_ID= '
3240 || to_char(p_online_report_id);
3241
3242 EXCEPTION
3243 WHEN OTHERS THEN
3244 wf_core.context('PO_REQAPPROVAL_ACTION','get_advisory_warning',x_progress);
3245 raise;
3246 END get_advisory_warning;
3247
3248
3249 -------------------------------------------------------------------------------
3250 --Start of Comments
3251 --Name: set_report_text_attr
3252 --Function:
3253 -- Generic procedure to copy the text of an online report into a workflow attribute.
3254 -- Reads each line in the online report, and concatenates them together.
3255 -- The string that is copied into the attribute is truncated to 2000 characters.
3256 --Parameters:
3257 --IN:
3258 --itemtype
3259 -- Workflow itemtype. Make sure that the attribute that the text will be copied into
3260 -- is defined for this particular workflow.
3261 --itemkey
3262 -- Workflow itemkey. The key for the particular workflow process for which the
3263 -- the attribute will be set.
3264 --p_online_report_id
3265 -- The id of the report from which to copy the text. This may be NULL.
3266 --p_attribute
3267 -- The workflow attribute which should be set to the header + report text.
3268 --p_warning_header_text
3269 -- A header string that should be appended before the text of the online report.
3270 -- This may be NULL, and will default to NULL.
3271 -- This paramater should be <= 2000 characters long.
3272 -- Any characters over the 2000 limit will be truncated.
3273 --End of Comments
3274 -------------------------------------------------------------------------------
3275 PROCEDURE set_report_text_attr(
3276 itemtype IN VARCHAR2
3277 , itemkey IN VARCHAR2
3278 , p_online_report_id IN NUMBER
3279 , p_attribute IN VARCHAR2
3280 , p_header_text IN VARCHAR2 DEFAULT NULL
3281 )
3282 IS
3283
3284 TYPE g_report_list_type IS TABLE OF VARCHAR2(2000);
3285
3286 l_report_text_lines g_report_list_type;
3287 l_attribute_text VARCHAR2(4000);
3288 len_att_text NUMBER := 0;
3289 i NUMBER;
3290
3291 x_progress VARCHAR2(400);
3292
3293 BEGIN
3294
3295
3296 x_progress := 'PO_REQAPPROVAL_ACTION.set_report_attr_text.010.ON_LINE_REPORT_ID= '
3297 || to_char(p_online_report_id);
3298
3299
3300 -- Add header text to beginning of attribute string.
3301 IF (p_header_text IS NOT NULL)
3302 THEN
3303 l_attribute_text := substr(p_header_text || ' ', 1, 2000);
3304 len_att_text := length(l_attribute_text);
3305 END IF;
3306
3307 -- Bulk collect text lines for the online report in question.
3308 SELECT substr(text_line, 1, 2000)
3309 BULK COLLECT INTO l_report_text_lines
3310 FROM po_online_report_text
3311 WHERE online_report_id = p_online_report_id
3312 ORDER BY sequence;
3313
3314 x_progress := 'PO_REQAPPROVAL_ACTION.set_report_attr_text.020.ON_LINE_REPORT_ID= '
3315 || to_char(p_online_report_id);
3316
3317 -- Loop through the plsql table, and concatenate each of the lines.
3318 -- Exit the loop if we run out of lines, or the string exceeds 2000 characters.
3319 -- Overflow is avoided since l_attribute_text is 4000 char.
3320 i := l_report_text_lines.FIRST;
3321 WHILE ((i is NOT NULL) and (len_att_text < 2000))
3322 LOOP
3323 l_attribute_text := l_attribute_text || l_report_text_lines(i) || fnd_global.local_chr(10) ; --Bug 10625022
3324 len_att_text := length(l_attribute_text);
3325 i := l_report_text_lines.NEXT(i);
3326 END LOOP;
3327
3328 -- Set the workflow attribute to the derived attribute string.
3329 wf_engine.SetItemAttrText (
3330 itemtype => itemType,
3331 itemkey => itemkey,
3332 aname => p_attribute,
3333 avalue => substr(l_attribute_text, 1, 2000)
3334 );
3335
3336 x_progress := 'PO_REQAPPROVAL_ACTION.set_report_attr_text.030.ON_LINE_REPORT_ID= '
3337 || to_char(p_online_report_id);
3338
3339 IF (g_po_wf_debug = 'Y') THEN
3340 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3341 END IF;
3342
3343 EXCEPTION
3344 WHEN OTHERS THEN
3345 wf_core.context('PO_REQAPPROVAL_ACTION','set_report_attr_text',x_progress);
3346 raise;
3347 END set_report_text_attr;
3348
3349 -- <END Bug 3536831>
3350
3351
3352
3353 --
3354 PROCEDURE set_doc_mgr_context (itemtype VARCHAR2, itemkey VARCHAR2) is
3355
3356 l_user_id number;
3357 l_responsibility_id number;
3358 l_application_id number;
3359
3360 x_progress varchar2(200);
3361
3362 BEGIN
3363
3364 -- Bug 4290541, replaced call to set apps init context with
3365 -- po reqapproval init1 set doc mgr context
3366 --
3367 po_reqapproval_init1.set_doc_mgr_context(itemtype,itemkey);
3368
3369 x_progress := 'PO_REQAPPROVAL_ACTION.set_doc_mgr_context. USER_ID= ' || to_char(l_user_id)
3370 || ' APPLICATION_ID= ' || to_char(l_application_id) ||
3371 'RESPONSIBILITY_ID= ' || to_char(l_responsibility_id);
3372 IF (g_po_wf_debug = 'Y') THEN
3373 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3374 END IF;
3375
3376 EXCEPTION
3377
3378 WHEN OTHERS THEN
3379 wf_core.context('PO_REQAPPROVAL_ACTION','set_doc_mgr_context',x_progress);
3380 raise;
3381
3382
3383 END set_doc_mgr_context;
3384
3385 PROCEDURE set_responder_doc_mgr_context (itemtype VARCHAR2, itemkey VARCHAR2) is
3386
3387 l_user_id number;
3388 l_responsibility_id number;
3389 l_application_id number;
3390
3391 x_progress varchar2(200);
3392
3393 -- Bug 4290541 Start
3394 X_User_Id NUMBER;
3395 X_Responsibility_Id NUMBER;
3396 X_Application_Id NUMBER;
3397 -- Bug 4290541 End
3398
3399 BEGIN
3400
3401 -- Context Setting Revamp
3402 -- Bug 4290541 Start
3403 -- FND_PROFILE.GET('USER_ID',X_USER_ID);
3404 --FND_PROFILE.GET('RESP_ID',X_RESPONSIBILITY_ID);
3405 --FND_PROFILE.GET('RESP_APPL_ID',X_APPLICATION_ID);
3406 -- Bug 4290541 End
3407
3408 X_USER_ID := fnd_global.user_id;
3409 X_RESPONSIBILITY_ID := fnd_global.resp_id;
3410 X_APPLICATION_ID := fnd_global.resp_appl_id;
3411
3412
3413 IF (X_USER_ID = -1) THEN
3414 X_USER_ID := NULL;
3415 END IF;
3416
3417 IF (X_RESPONSIBILITY_ID = -1) THEN
3418 X_RESPONSIBILITY_ID := NULL;
3419 END IF;
3420
3421 IF (X_APPLICATION_ID = -1) THEN
3422 X_APPLICATION_ID := NULL;
3423 END IF;
3424
3425 l_user_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
3426 itemkey => itemkey,
3427 aname => 'RESPONDER_USER_ID');
3428 --
3429 l_application_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
3430 itemkey => itemkey,
3431 aname => 'RESPONDER_APPL_ID');
3432 --
3433 l_responsibility_id := PO_WF_UTIL_PKG.GetItemAttrNumber ( itemtype => itemtype,
3434 itemkey => itemkey,
3435 aname => 'RESPONDER_RESP_ID');
3436
3437 /* Bug# 2626935: kagarwal
3438 ** Desc: If the application context for responder is not set or set to -1
3439 ** then call the set_doc_mgr_context.
3440 */
3441
3442 IF ((l_user_id is NULL) OR (l_user_id = -1) OR
3443 (l_application_id is NULL) OR (l_application_id = -1) OR
3444 (l_responsibility_id is NULL) OR (l_responsibility_id = -1)) THEN
3445
3446 set_doc_mgr_context(itemtype, itemkey);
3447 ELSE
3448 /* Set the context for the doc manager */
3449 -- Bug 4290541 Start
3450 IF X_User_Id IS NOT NULL THEN
3451 FND_GLOBAL.APPS_INITIALIZE (X_User_Id, L_Responsibility_Id, L_Application_Id);
3452 ELSE
3453 FND_GLOBAL.APPS_INITIALIZE (L_User_Id, L_Responsibility_Id, L_Application_Id);
3454 END IF;
3455 -- Bug 4290541 End
3456 END IF;
3457
3458 x_progress := 'PO_REQAPPROVAL_ACTION.set_responder_doc_mgr_context. USER_ID= ' || to_char(l_user_id)
3459 || ' APPLICATION_ID= ' || to_char(l_application_id) ||
3460 'RESPONSIBILITY_ID= ' || to_char(l_responsibility_id);
3461 IF (g_po_wf_debug = 'Y') THEN
3462 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3463 END IF;
3464
3465 EXCEPTION
3466
3467 WHEN OTHERS THEN
3468 wf_core.context('PO_REQAPPROVAL_ACTION','set_responder_doc_mgr_context',x_progress);
3469 raise;
3470
3471
3472 END set_responder_doc_mgr_context;
3473
3474 /* Bug# 2234341: kagarwal
3475 ** Desc: Added a new wf api Reserve_doc_Override(...) for overriding
3476 ** funds reservation, if the approve responds to the 'Reservation failure'
3477 ** Notification with result 'Try Override'
3478 */
3479
3480 procedure Reserve_doc_Override( itemtype in varchar2,
3481 itemkey in varchar2,
3482 actid in number,
3483 funcmode in varchar2,
3484 resultout out NOCOPY varchar2) is
3485 x_progress varchar2(100);
3486 x_resultout varchar2(30);
3487
3488 l_responder_id number;
3489 l_doc_type varchar2(30);
3490
3491 l_doc_mgr_return_val varchar2(1);
3492
3493 l_doc_string varchar2(200);
3494 l_preparer_user_name varchar2(100);
3495 doc_manager_exception exception;
3496
3497 BEGIN
3498
3499 x_progress := 'PO_REQAPPROVAL_ACTION.Reserve_doc_Override: 01';
3500 IF (g_po_wf_debug = 'Y') THEN
3501 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3502 END IF;
3503
3504 -- Do nothing in cancel or timeout mode
3505 --
3506 if (funcmode <> wf_engine.eng_run) then
3507
3508 resultout := wf_engine.eng_null;
3509 return;
3510
3511 end if;
3512
3513 l_doc_type := wf_engine.GetItemAttrText (itemtype => itemtype,
3514 itemkey => itemkey,
3515 aname => 'DOCUMENT_TYPE');
3516
3517
3518 l_responder_id := PO_WF_UTIL_PKG.GetItemAttrNumber(itemtype => itemtype,
3519 itemkey => itemkey,
3520 aname => 'RESPONDER_USER_ID');
3521
3522 -- Context Setting revamp
3523 -- if (l_responder_id is null) then
3524
3525 /* Set the Doc manager context */
3526 -- set_doc_mgr_context(itemtype, itemkey);
3527
3528 -- else
3529
3530 /* Set the Doc manager context based on responder */
3531 -- set_responder_doc_mgr_context(itemtype, itemkey);
3532
3533 -- end if;
3534
3535 /* Bug# 2234341: kagarwal
3536 ** Desc: Always pass override_funds parameter as 'Y'
3537 */
3538
3539 l_doc_mgr_return_val := ReserveDoc(itemtype, itemkey, 'Y');
3540
3541 x_progress := 'PO_REQAPPROVAL_ACTION.Reserve_doc_Override 10. RESULT= ' || x_resultout;
3542 IF (g_po_wf_debug = 'Y') THEN
3543 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3544 END IF;
3545
3546 -- <ENCUMBRANCE FPJ START>
3547 -- Commenting the Doc manager Handling
3548
3549 -- If l_doc_mgr_return_val = 'F' then
3550 -- raise doc_manager_exception;
3551 -- End if;
3552
3553 -- <ENCUMBRANCE FPJ END>
3554
3555 resultout := wf_engine.eng_completed || ':' || l_doc_mgr_return_val;
3556 x_resultout := l_doc_mgr_return_val;
3557
3558 x_progress := 'PO_REQAPPROVAL_ACTION.Reserve_doc_Override 99';
3559 IF (g_po_wf_debug = 'Y') THEN
3560 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
3561 END IF;
3562
3563 EXCEPTION
3564 -- WHEN doc_manager_exception THEN
3565 -- raise;
3566 WHEN OTHERS THEN
3567 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3568 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(
3569 itemType, itemkey);
3570 WF_CORE.context('PO_REQAPPROVAL_ACTION', 'Reserve_doc_Override',
3571 itemtype, itemkey, x_progress);
3572 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey,
3573 l_preparer_user_name, l_doc_string, sqlerrm,
3574 'PO_REQAPPROVAL_ACTION.RESERVE_DOC');
3575 RAISE;
3576 END Reserve_doc_Override;
3577
3578 --
3579
3580 -- <ENCUMBRANCE FPJ START>
3581 ------------------------------------------------------------------------------
3582 --Start of Comments
3583 --Name: ReserveAutonomous
3584 --Pre-reqs:
3585 -- None.
3586 --Modifies:
3587 -- Creates encumbrance entries in the gl_bc_packets table
3588 -- Adds distribution-specific transaction information into the
3589 -- po_online_report_text table
3590 --Locks:
3591 -- None.
3592 --Function:
3593 -- Calls PO_DOCUMENT_FUNDS_PVT.do_reserve as an autonomous call
3594 --Parameters:
3595 --IN:
3596 --p_doc_type
3597 -- Differentiates between the doc being a REQ, PA, PO, or RELEASE,
3598 -- which is used to identify the tables to look at (PO vs. Req)
3599 -- and the join conditions
3600 --p_doc_subtype
3601 -- Differentiates between the subtypes of documents
3602 -- REQ: NULL
3603 -- PO: STANDARD, PLANNED
3604 -- PA: CONTRACT, BLANKET
3605 -- RELEASE: SCHEDULED, BLANKET
3606 --p_doc_id
3607 -- document header id
3608 --p_override_funds
3609 -- Indicates whether funds override capability can be used if needed, to make a
3610 -- transaction succeed.
3611 --p_employee_id
3612 -- Employee Id of the user taking the action
3613 --OUT:
3614 --x_po_return_code
3615 -- Indicates whether PO is classifying this transaction as an
3616 -- Error/Warning/Partial Success/Success
3617 --x_online_report_id
3618 -- Unique id into po_online_report_text rows that store distribution specific
3619 -- reporting information for a specific encumbrance transaction
3620 --Testing:
3621 --
3622 --End of Comments
3623 -------------------------------------------------------------------------------
3624 PROCEDURE ReserveAutonomous(
3625 p_doc_type IN VARCHAR2
3626 , p_doc_subtype IN VARCHAR2
3627 , p_doc_id IN NUMBER
3628 , p_override_funds IN VARCHAR2
3629 , p_employee_id IN NUMBER
3630 , x_po_return_code OUT NOCOPY VARCHAR2
3631 , x_online_report_id OUT NOCOPY NUMBER
3632 ) IS
3633
3634 pragma AUTONOMOUS_TRANSACTION;
3635
3636 l_api_name CONSTANT varchar2(30) := 'ReserveAutonomous';
3637 l_progress VARCHAR2(3);
3638 p_return_status VARCHAR2(1);
3639
3640 BEGIN
3641
3642 SAVEPOINT ReserveAutonomous_SP;
3643
3644 l_progress := '000';
3645
3646 -- Call the do_reserve API
3647 PO_DOCUMENT_FUNDS_PVT.do_reserve(
3648 x_return_status => p_return_status
3649 , p_doc_type => p_doc_type
3650 , p_doc_subtype => p_doc_subtype
3651 , p_doc_level => PO_DOCUMENT_FUNDS_PVT.g_doc_level_HEADER
3652 , p_doc_level_id => p_doc_id
3653 , p_use_enc_gt_flag => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
3654 , p_prevent_partial_flag => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
3655 , p_validate_document => PO_DOCUMENT_FUNDS_PVT.g_parameter_YES
3656 , p_override_funds => p_override_funds
3657 , p_employee_id => p_employee_id
3658 , x_po_return_code => x_po_return_code
3659 , x_online_report_id => x_online_report_id
3660 );
3661
3662 l_progress := '010';
3663
3664 IF p_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3666 END IF;
3667
3668 l_progress := '100';
3669
3670 COMMIT;
3671
3672 EXCEPTION
3673 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3674 ROLLBACK TO ReserveAutonomous_SP;
3675 /* Bug 3293107: removed to_char() around l_progress */
3676 wf_core.context('PO_REQAPPROVAL_ACTION','ReserveAutonomous',
3677 l_progress);
3678 x_po_return_code := PO_DOCUMENT_FUNDS_PVT.g_return_FATAL; /* Bug 9886447 */
3679
3680 WHEN OTHERS THEN
3681 ROLLBACK TO ReserveAutonomous_SP;
3682 /* Bug 3293107: removed to_char() around l_progress */
3683 wf_core.context('PO_REQAPPROVAL_ACTION','ReserveAutonomous',
3684 l_progress);
3685 RAISE;
3686
3687 END ReserveAutonomous;
3688
3689
3690
3691 -- <Doc Manager Rewrite 11.5.11 Start>
3692
3693 PROCEDURE ApproveAutonomous(
3694 p_document_id IN NUMBER
3695 , p_document_type IN VARCHAR2
3696 , p_document_subtype IN VARCHAR2
3697 , p_note IN VARCHAR2
3698 , p_approval_path_id IN NUMBER
3699 , x_return_status OUT NOCOPY VARCHAR2
3700 , x_exception_msg OUT NOCOPY VARCHAR2
3701 )
3702 IS
3703 pragma AUTONOMOUS_TRANSACTION;
3704
3705 d_progress NUMBER;
3706
3707 BEGIN
3708
3709 d_progress := 10;
3710
3711 PO_DOCUMENT_ACTION_PVT.do_approve(
3712 p_document_id => p_document_id
3713 , p_document_type => p_document_type
3714 , p_document_subtype => p_document_subtype
3715 , p_note => p_note
3716 , p_approval_path_id => p_approval_path_id
3717 , x_return_status => x_return_status
3718 , x_exception_msg => x_exception_msg
3719 );
3720
3721 d_progress := 20;
3722
3723 IF (x_return_status = 'S') THEN
3724
3725 COMMIT;
3726
3727 ELSE
3728
3729 ROLLBACK;
3730
3731 END IF;
3732
3733 -- Bug 14277142 : Calling iP rebuild index only for Blanket so that line items becomes serachable in iP store.
3734 IF (p_document_type = 'PA' AND p_document_subtype = 'BLANKET') THEN
3735 -- Rebuild catalog search index.
3736 BEGIN
3737 ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index;
3738 EXCEPTION
3739 WHEN OTHERS THEN
3740 x_exception_msg := 'ApproveAutonomous : Exception for ICX_CAT_INTERMEDIA_INDEX_PVT.rebuild_index and erorr encountered is '
3741 ||SQLCODE||' -ERROR- '||SQLERRM ;
3742 END;
3743 END IF;
3744
3745 EXCEPTION
3746 WHEN others THEN
3747 ROLLBACK;
3748 wf_core.context('PO_REQAPPROVAL_ACTION', 'ApproveAutonomous', to_char(d_progress));
3749 x_return_status := 'U';
3750
3751 END ApproveAutonomous;
3752
3753 PROCEDURE RejectAutonomous(
3754 p_document_id IN NUMBER
3755 , p_draft_id IN NUMBER -- CLM Apprvl
3756 , p_document_type IN VARCHAR2
3757 , p_document_subtype IN VARCHAR2
3758 , p_note IN VARCHAR2
3759 , p_approval_path_id IN NUMBER
3760 , x_return_status OUT NOCOPY VARCHAR2
3761 , x_return_code OUT NOCOPY VARCHAR2
3762 , x_exception_msg OUT NOCOPY VARCHAR2
3763 , x_online_report_id OUT NOCOPY NUMBER
3764 )
3765 IS
3766 pragma AUTONOMOUS_TRANSACTION;
3767
3768 d_progress NUMBER;
3769
3770 BEGIN
3771
3772 d_progress := 10;
3773
3774 PO_DOCUMENT_ACTION_PVT.do_reject(
3775 p_document_id => p_document_id
3776 , p_draft_id => p_draft_id -- CLM Apprvl
3777 , p_document_type => p_document_type
3778 , p_document_subtype => p_document_subtype
3779 , p_note => p_note
3780 , p_approval_path_id => p_approval_path_id
3781 , x_return_status => x_return_status
3782 , x_return_code => x_return_code
3783 , x_exception_msg => x_exception_msg
3784 , x_online_report_id => x_online_report_id
3785 );
3786
3787 d_progress := 20;
3788
3789 IF (x_return_status = 'S') THEN
3790
3791 COMMIT;
3792
3793 ELSE
3794
3795 ROLLBACK;
3796
3797 END IF;
3798
3799 EXCEPTION
3800 WHEN others THEN
3801 ROLLBACK;
3802 wf_core.context('PO_REQAPPROVAL_ACTION', 'RejectAutonomous', to_char(d_progress));
3803 x_return_status := 'U';
3804 END RejectAutonomous;
3805
3806
3807 PROCEDURE ForwardAutonomous(
3808 p_document_id IN NUMBER
3809 , p_document_type IN VARCHAR2
3810 , p_document_subtype IN VARCHAR2
3811 , p_new_doc_status IN VARCHAR2
3812 , p_note IN VARCHAR2
3813 , p_approval_path_id IN NUMBER
3814 , p_forward_to_id IN NUMBER
3815 , x_return_status OUT NOCOPY VARCHAR2
3816 , x_exception_msg OUT NOCOPY VARCHAR2
3817 )
3818 IS
3819 pragma AUTONOMOUS_TRANSACTION;
3820
3821 d_progress NUMBER;
3822
3823 BEGIN
3824
3825 d_progress := 10;
3826
3827 PO_DOCUMENT_ACTION_PVT.do_forward(
3828 p_document_id => p_document_id
3829 , p_document_type => p_document_type
3830 , p_document_subtype => p_document_subtype
3831 , p_new_doc_status => p_new_doc_status
3832 , p_note => p_note
3833 , p_approval_path_id => p_approval_path_id
3834 , p_forward_to_id => p_forward_to_id
3835 , x_return_status => x_return_status
3836 , x_exception_msg => x_exception_msg
3837 );
3838
3839 d_progress := 20;
3840
3841 IF (x_return_status = 'S') THEN
3842
3843 COMMIT;
3844
3845 ELSE
3846
3847 ROLLBACK;
3848
3849 END IF;
3850
3851 EXCEPTION
3852 WHEN others THEN
3853 ROLLBACK;
3854 wf_core.context('PO_REQAPPROVAL_ACTION', 'ForwardAutonomous', to_char(d_progress));
3855 x_return_status := 'U';
3856
3857 END ForwardAutonomous;
3858
3859 PROCEDURE AutoUpdateCloseAutonomous(
3860 p_document_id IN NUMBER
3861 , p_document_type IN VARCHAR2
3862 , p_document_subtype IN VARCHAR2
3863 , x_return_status OUT NOCOPY VARCHAR2
3864 , x_exception_msg OUT NOCOPY VARCHAR2
3865 , x_return_code OUT NOCOPY VARCHAR2
3866 )
3867 IS
3868 pragma AUTONOMOUS_TRANSACTION;
3869
3870 d_progress NUMBER;
3871
3872 BEGIN
3873
3874 d_progress := 10;
3875
3876 PO_DOCUMENT_ACTION_PVT.auto_update_close_state(
3877 p_document_id => p_document_id
3878 , p_document_type => p_document_type
3879 , p_document_subtype => p_document_subtype
3880 , p_line_id => NULL
3881 , p_shipment_id => NULL
3882 , p_calling_mode => 'PO'
3883 , p_called_from_conc => FALSE
3884 , x_return_status => x_return_status
3885 , x_exception_msg => x_exception_msg
3886 , x_return_code => x_return_code
3887 );
3888
3889 d_progress := 20;
3890
3891 IF (x_return_status = 'S') THEN
3892
3893 COMMIT;
3894
3895 ELSE
3896
3897 ROLLBACK;
3898
3899 END IF;
3900
3901 EXCEPTION
3902 WHEN others THEN
3903 ROLLBACK;
3904 wf_core.context('PO_REQAPPROVAL_ACTION', 'AutoUpdateCloseAutonomous', to_char(d_progress));
3905 x_return_status := 'U';
3906
3907 END AutoUpdateCloseAutonomous;
3908 -- <Doc Manager Rewrite 11.5.11 End>
3909
3910 ------------------------------------------------------------------------------
3911 --Start of Comments
3912 --Name: po_submission_check_autonomous
3913 --Function:
3914 -- Calls PO_DOCUMENT_CHECKS_GRP.po_submission_check in an
3915 -- autonomous transaction.
3916 -- The autonomous_transaction is required due to the use of the
3917 -- submission check global temp tables, as submission check is also
3918 -- called later in the workflow as part of doc reservation,
3919 -- which must be an autonomous transaction due to its commit.
3920 -- Without this autonomous transaction, the following error is raised:
3921 -- ORA-14450: attempt to access a transactional temp table already in use
3922 --Notes:
3923 -- See PO_DOCUMENT_CHECKS_GRP.po_submission_check for a description
3924 -- of the parameters.
3925 --Testing:
3926 --
3927 --End of Comments
3928 -------------------------------------------------------------------------------
3929 PROCEDURE po_submission_check_autonomous(
3930 p_document_type IN VARCHAR2
3931 , p_document_subtype IN VARCHAR2
3932 , p_document_id IN NUMBER
3933 , p_check_asl IN BOOLEAN
3934 , p_draft_id IN NUMBER:= -1 -- CLM Aprvl
3935 , x_return_status OUT NOCOPY VARCHAR2
3936 , x_sub_check_status OUT NOCOPY VARCHAR2
3937 , x_msg_data OUT NOCOPY VARCHAR2
3938 , x_online_report_id OUT NOCOPY NUMBER
3939 )
3940 IS
3941 PRAGMA AUTONOMOUS_TRANSACTION;
3942
3943 BEGIN
3944
3945 PO_DOCUMENT_CHECKS_GRP.po_submission_check(
3946 p_api_version => 1.0
3947 , p_action_requested => 'DOC_SUBMISSION_CHECK'
3948 , p_document_type => p_document_type
3949 , p_document_subtype => p_document_subtype
3950 , p_document_id => p_document_id
3951 , p_check_asl => p_check_asl
3952 , p_draft_id => p_draft_id -- CLM Aprvl
3953 , x_return_status => x_return_status
3954 , x_sub_check_status => x_sub_check_status
3955 , x_msg_data => x_msg_data
3956 , x_online_report_id => x_online_report_id
3957 );
3958
3959 -- bug3539651
3960 -- Issue a commit instead of rollback, otherwise we will lose all the data
3961 -- in PO_ONLINE_REPORT_TEXT
3962 COMMIT;
3963
3964
3965 END po_submission_check_autonomous;
3966
3967
3968 -- <ENCUMBRANCE FPJ END>
3969
3970
3971
3972
3973 end PO_REQAPPROVAL_ACTION;