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