1 PACKAGE po_reqapproval_init1 AUTHID CURRENT_USER AS
2 /* $Header: POXWPA1S.pls 120.23 2011/03/02 06:26:39 inagdeo ship $ */
3
4 /*=======================================================================+
5 | FILENAME
6 | POXWPA1S.pls
7 |
8 | DESCRIPTION
9 | PL/SQL spec for package: PO_REQAPPROVAL_INIT1
10 |
11 | NOTES
12 | MODIFIED Ben Chihaoui (06/10/97)
13 | MODIFIED davidng (05/24/02)
14 | Changed parameter PrintFlag to default to 'N' instead of NULL
15 *=====================================================================*/
16
17
18 -- Start_WF_Process
19 -- Generates the itemkey, sets up the Item Attributes,
20 -- then starts the workflow process.
21 --
22 /* RETROACTIVE FPI Change.
23 * Added 2 new parameters MassUpdateReleases and RetroactivePriceChange.
24 * MassUpdateReleases is Y when approval is initiated from the Form and
25 * the user wants to update all the releases against the blanket with
26 * the retroactive price change. RetroactivePriceChange is Y when the
27 * releases are updated with the retroactive price change.
28 */
29
30 PROCEDURE Start_WF_Process ( ItemType VARCHAR2,
31 ItemKey VARCHAR2,
32 WorkflowProcess VARCHAR2,
33 ActionOriginatedFrom VARCHAR2,
34 DocumentID NUMBER,
35 DocumentNumber VARCHAR2,
36 PreparerID NUMBER,
37 DocumentTypeCode VARCHAR2,
38 DocumentSubtype VARCHAR2,
39 SubmitterAction VARCHAR2,
40 forwardToID NUMBER,
41 forwardFromID NUMBER,
42 DefaultApprovalPathID NUMBER,
43 Note VARCHAR2,
44 PrintFlag VARCHAR2 default 'N',
45 FaxFlag VARCHAR2 default 'N',
46 FAXNumber VARCHAR2 default NULL,
47 EmailFlag VARCHAR2 default 'N',
48 EmailAddress VARCHAR2 default NULL,
49 CreateSourcingRule VARCHAR2 default NULL,
50 ReleaseGenMethod VARCHAR2 default NULL,
51 UpdateSourcingRule VARCHAR2 default NULL,
52 MassUpdateReleases VARCHAR2 default 'N', -- Retroactive FPI
53 RetroactivePriceChange VARCHAR2 default 'N', -- Retroactive FPI
54 OrgAssignChange VARCHAR2 default 'N', -- GA FPI
55 CommunicatePriceChange VARCHAR2 default NULL, -- <FPJ Retroactive> -- bug4176111
56 p_Background_Flag VARCHAR2 default 'N', -- <DropShip FPJ>
57 p_Initiator VARCHAR2 default NULL,
58 p_xml_flag VARCHAR2 default NULL, -- Bug 5218538,
59 /* Bug6708182 FPDS-NG ER. */
60 /* Bug 6708182 Start */
61 FpdsngFlag VARCHAR2 default 'N',
62 p_source_type_code VARCHAR2 DEFAULT null
63 /* Bug 6708182 End */
64 ,DraftID NUMBER DEFAULT -1,
65 p_bypass_checks_flag VARCHAR2 DEFAULT 'N');
66 /*Bug 11727653: BYPASSING MULTIPLE SUBMISSION CHECKS IN WORKFLOW*/
67
68
69 -- set_multiorg_context
70 -- Get the org_id and set the context for Mult-org
71 --
72 PROCEDURE get_multiorg_context(document_type varchar2, document_id number,
73 x_orgid IN OUT NOCOPY number);
74
75
76 -- Record variable ReqHdr_rec
77 -- Public record variable used to hold the Requisition_header columns
78 -- Bug#3147435
79 -- Added contractor_requisition_flag and contractor_status to ReqHdrRecord
80 TYPE ReqHdrRecord IS RECORD(
81 REQUISITION_HEADER_ID NUMBER,
82 DESCRIPTION VARCHAR2(240),
83 AUTHORIZATION_STATUS VARCHAR2(25),
84 TYPE_LOOKUP_CODE VARCHAR2(25),
85 PREPARER_ID NUMBER,
86 SEGMENT1 VARCHAR2(20),
87 CLOSED_CODE VARCHAR2(25),
88 EMERGENCY_PO_NUM VARCHAR2(25),
89 CONTRACTOR_REQUISITION_FLAG VARCHAR2(1),
90 CONTRACTOR_STATUS VARCHAR2(25),
91 NOTE_TO_AUTHORIZER VARCHAR2(4000));
92
93 ReqHdr_rec ReqHdrRecord;
94
95 -- Record variable ReqLine_rec
96 -- Public record variable used to hold the Requisition_line columns.
97
98 TYPE ReqLineRecord IS RECORD(
99 LINE_NUM NUMBER,
100 ITEM_DESCRIPTION VARCHAR2(240),
101 UNIT_MEAS_LOOKUP_CODE VARCHAR2(25),
102 UNIT_PRICE NUMBER,
103 QUANTITY NUMBER,
104 NEED_BY_DATE DATE,
105 TO_PERSON_ID NUMBER,
106 DELIVER_TO_LOCATION_ID NUMBER,
107
108 /** PO UTF8 Column Expansion Project 9/23/2002 tpoon **/
109 /** Expanded deliver_to_location from 20 to 60 **/
110 -- DELIVER_TO_LOCATION VARCHAR2(20),
111 DELIVER_TO_LOCATION VARCHAR2(60),
112
113 REQUESTOR_FULL_NAME VARCHAR2(240));
114
115 ReqLine_rec ReqLineRecord;
116
117 -- Cursor GetRecHdr_csr
118 -- Public cursor used to get the Requisition_header columns.
119
120 CURSOR GetRecHdr_csr(p_requisition_header_id NUMBER) RETURN ReqHdrRecord;
121
122 --
123 PROCEDURE get_user_name( p_employee_id IN number, x_username OUT NOCOPY varchar2,
124 x_user_display_name OUT NOCOPY varchar2);
125
126 --
127 PROCEDURE get_employee_id( p_username IN varchar2, x_employee_id OUT NOCOPY number);
128
129 --
130
131 -- SetStartupValues
132 --
133 --
134 -- IN
135 -- itemtype -- itemkey -- actid -- funcmode
136 -- OUT
137 -- Resultout
138 -- - Activity Performed - Activity was completed without any errors.
139 --
140 procedure Set_Startup_Values( itemtype in varchar2,
141 itemkey in varchar2,
142 actid in number,
143 funcmode in varchar2,
144 resultout out NOCOPY varchar2 );
145 --
146 -- Get_ReqAttributes
147 -- Get the requisition attributes. We get the header info and up to 5
148 -- requisition lines.
149 --
150 -- IN
151 -- itemtype -- itemkey -- actid -- funcmode
152 -- OUT
153 -- Resultout
154 -- - Activity Performed - Activity was completed without any errors.
155 --
156 procedure Get_Req_Attributes( itemtype in varchar2,
157 itemkey in varchar2,
158 actid in number,
159 funcmode in varchar2,
160 resultout out NOCOPY varchar2 );
161 --
162 -- Set_req_stat_preapproved
163 -- Added for WR4
164 -- IN
165 -- itemtype -- itemkey -- actid -- funcmode
166 -- OUT
167 -- Resultout
168 -- - Activity Performed - Activity was completed without any errors.
169 --
170 procedure Set_doc_stat_preapproved( itemtype in varchar2,
171 itemkey in varchar2,
172 actid in number,
173 funcmode in varchar2,
174 resultout out NOCOPY varchar2 );
175
176 procedure Set_doc_stat_preapproved1( itemtype in varchar2,
177 itemkey in varchar2,
178 actid in number,
179 funcmode in varchar2,
180 resultout out NOCOPY varchar2 );
181
182 --
183 -- Set_req_stat_inprocess
184 -- Set the requisition status to IN-PROCESS. That way, the users can not bring
185 -- the Requisition up in the entry form and make modifications while it's in
186 -- in the workflow process.
187 --
188 -- IN
189 -- itemtype -- itemkey -- actid -- funcmode
190 -- OUT
191 -- Resultout
192 -- - Activity Performed - Activity was completed without any errors.
193 --
194 procedure Set_doc_stat_inprocess( itemtype in varchar2,
195 itemkey in varchar2,
196 actid in number,
197 funcmode in varchar2,
198 resultout out NOCOPY varchar2 );
199
200 -- set_doc_to_originalstat
201 -- Sets the doc back to it's original status if No Approver was found
202 -- or doc failed STATE VERIFICATION or COMPLETENESS check before APPROVE,
203 -- REJECT or FORWARD.
204
205 --
206 -- IN
207 -- itemtype -- itemkey -- actid -- funcmode
208 -- OUT
209 -- Resultout
210 -- - Activity Performed - Activity was completed without any errors.
211 --
212 procedure set_doc_to_originalstat( itemtype in varchar2,
213 itemkey in varchar2,
214 actid in number,
215 funcmode in varchar2,
216 resultout out NOCOPY varchar2 );
217
218 -- Register_doc_submitted
219 -- IN
220 -- itemtype -- itemkey -- actid -- funcmode
221 -- OUT
222 -- Resultout
223 -- - Activity Performed - Activity was completed without any errors.
224 --
225 procedure Register_doc_submitted( itemtype in varchar2,
226 itemkey in varchar2,
227 actid in number,
228 funcmode in varchar2,
229 resultout out NOCOPY varchar2 );
230
231 --
232 -- Can_Owner_Approve
233 -- Can the owner of a requisition approve it
234 -- IN
235 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
236 -- itemkey - A string generated from the application object's primary key.
237 -- actid - The notification process activity(instance id).
238 -- funcmode - Run/Cancel
239 -- OUT
240 -- Resultout
241 -- Y - Owner can approve requisition
242 -- N - Owner can NOT approve requisition
243 --
244 procedure Can_Owner_Approve( itemtype in varchar2,
245 itemkey in varchar2,
246 actid in number,
247 funcmode in varchar2,
248 resultout out NOCOPY varchar2 );
249
250 --Bug 10013322
251 -- Is_Submitter_Same_As_Preparer
252 -- Is the submitter same as preparer
253 -- IN
254 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
255 -- itemkey - A string generated from the application object's primary key.
256 -- actid - The notification process activity(instance id).
257 -- funcmode - Run/Cancel
258 -- OUT
259 -- Resultout
260 -- Y - Submitter same as preparer
261 -- N - Submitter and preparer are different
262 --
263 procedure Is_Submitter_Same_As_Preparer(itemtype in varchar2,
264 itemkey in varchar2,
265 actid in number,
266 funcmode in varchar2,
267 resultout out NOCOPY varchar2 );
268
269 --
270 -- Is_doc_preapproved
271 -- Is the document status pre-approved
272 -- IN
273 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
274 -- itemkey - A string generated from the application object's primary key.
275 -- actid - The notification process activity(instance id).
276 -- funcmode - Run/Cancel
277 -- OUT
278 -- Resultout
279 -- Y -
280 -- N -
281 --
282 procedure Is_doc_preapproved( itemtype in varchar2,
283 itemkey in varchar2,
284 actid in number,
285 funcmode in varchar2,
286 resultout out NOCOPY varchar2 );
287 --
288
289 --
290 -- Ins_actionhist_submit
291 -- When submitting the document into the workflow, we need to insert
292 -- action SUBMIT into PO_ACTION_HISTORY
293 -- IN
294 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
295 -- itemkey - A string generated from the application object's primary key.
296 -- actid - The notification process activity(instance id).
297 -- funcmode - Run/Cancel
298 -- OUT
299 -- Resultout
300 -- ACTIVITY_PERFORMED
301 --
302 procedure Ins_actionhist_submit( itemtype in varchar2,
303 itemkey in varchar2,
304 actid in number,
305 funcmode in varchar2,
306 resultout out NOCOPY varchar2 );
307 --
308
309 --
310 -- Set_End_VerifyDoc_Failed
311 -- Sets the value of the transition to FAILED_VERIFICATION to match the
312 -- transition value for the VERIFY_REQUISITION Process
313 --
314 -- IN
315 -- itemtype -- itemkey -- actid -- funcmode
316 -- OUT
317 -- Resultout
318 -- - Activity Performed - Activity was completed without any errors.
319 --
320 procedure Set_End_VerifyDoc_Failed( itemtype in varchar2,
321 itemkey in varchar2,
322 actid in number,
323 funcmode in varchar2,
324 resultout out NOCOPY varchar2 );
325
326 --
327 -- Set_End_VerifyDoc_Passed
328 -- Sets the value of the transition to PASSED_VERIFICATION to match the
329 -- transition value for the VERIFY_REQUISITION Process
330 --
331 -- IN
332 -- itemtype -- itemkey -- actid -- funcmode
333 -- OUT
334 -- Resultout
335 -- -
336 --
337 procedure Set_End_VerifyDoc_Passed( itemtype in varchar2,
338 itemkey in varchar2,
339 actid in number,
340 funcmode in varchar2,
341 resultout out NOCOPY varchar2 );
342
343 --
344 -- Set_End_Valid_Action
345 -- Sets the value of the transition to VALID_ACTION to match the
346 -- transition value for the APPROVE_REQUISITION, APPROVE_PO,
347 -- APPROVE_AND_FORWARD_REQUISITION and APPROVE_AND_FORWARD_PO Processes.
348 --
349 -- IN
350 -- itemtype -- itemkey -- actid -- funcmode
351 -- OUT
352 -- Resultout
353 -- - VALID_ACTION
354 --
355 procedure Set_End_Valid_Action( itemtype in varchar2,
356 itemkey in varchar2,
357 actid in number,
358 funcmode in varchar2,
359 resultout out NOCOPY varchar2 );
360
361 --
362 -- Set_End_Invalid_Action
363 -- Sets the value of the transition to VALID_ACTION to match the
364 -- transition value for the APPROVE_REQUISITION, APPROVE_PO,
365 -- APPROVE_AND_FORWARD_REQUISITION and APPROVE_AND_FORWARD_PO Processes.
366 --
367 -- IN
371 -- - VALID_ACTION
368 -- itemtype -- itemkey -- actid -- funcmode
369 -- OUT
370 -- Resultout
372 --
373 procedure Set_End_Invalid_Action( itemtype in varchar2,
374 itemkey in varchar2,
375 actid in number,
376 funcmode in varchar2,
377 resultout out NOCOPY varchar2 );
378
379 --
380 --
381 -- Encumb_on_doc_unreserved
382 -- IN
383 -- itemtype -- itemkey -- actid -- funcmode
384 -- OUT
385 -- Resultout
386 -- - Y/N
387 -- If Encumbrance is ON and Document is NOT reserved, then return Y.
388
389 procedure Encumb_on_doc_unreserved( itemtype in varchar2,
390 itemkey in varchar2,
391 actid in number,
392 funcmode in varchar2,
393 resultout out NOCOPY varchar2 );
394
395
396 --
397 --
398 -- RESERVE_AT_COMPLETION_CHECK
399 -- IN
400 -- itemtype -- itemkey -- actid -- funcmode
401 -- OUT
402 -- Resultout
403 -- - Y/N
404 -- If the reserve at completion flag is checked, then return Y.
405
406 procedure RESERVE_AT_COMPLETION_CHECK( itemtype in varchar2,
407 itemkey in varchar2,
408 actid in number,
409 funcmode in varchar2,
410 resultout out NOCOPY varchar2 );
411 --
412
413 --
414 --
415 -- Is_Interface_ReqImport
416 -- IN
417 -- itemtype -- itemkey -- actid -- funcmode
418 -- OUT
419 -- Resultout
420 -- - Y/N
421 -- If requisition being generated from the requisiton import program
422
423 procedure Is_Interface_ReqImport( itemtype in varchar2,
424 itemkey in varchar2,
425 actid in number,
426 funcmode in varchar2,
427 resultout out NOCOPY varchar2 ) ;
428
429 --
430 -- Remove_reminder_notif
431 -- IN
432 -- itemtype -- itemkey -- actid -- funcmode
433 -- OUT
434 -- Resultout
435 --
436 -- Remove the reminder notifications since this doc is now approved.
437
438 procedure Remove_reminder_notif( itemtype in varchar2,
439 itemkey in varchar2,
440 actid in number,
441 funcmode in varchar2,
442 resultout out NOCOPY varchar2 ) ;
443
444 -- Print_Doc_Yes_No
445 -- IN
446 -- itemtype -- itemkey -- actid -- funcmode
447 -- OUT
448 -- Resultout
449 --
450 -- Does user want to print the document?
451
452 procedure Print_Doc_Yes_No( itemtype in varchar2,
453 itemkey in varchar2,
454 actid in number,
455 funcmode in varchar2,
456 resultout out NOCOPY varchar2 ) ;
457
458
459 -- Fax_Doc_Yes_No
460 -- IN
461 -- itemtype -- itemkey -- actid -- funcmode
462 -- OUT
463 -- Resultout
464 --
465 -- Does user want to fax the document?
466
467 procedure Fax_Doc_Yes_No( itemtype in varchar2,
468 itemkey in varchar2,
469 actid in number,
470 funcmode in varchar2,
471 resultout out NOCOPY varchar2 ) ;
472
473
474
475 -- Send_WS_Notif_Yes_No
476 -- IN
477 -- itemtype -- itemkey -- actid -- funcmode
478 -- OUT
479 -- Resultout
480 --
481 -- Does user want to send the notification ?
482
483 procedure Send_WS_Notif_Yes_No( itemtype in varchar2,
484 itemkey in varchar2,
485 actid in number,
486 funcmode in varchar2,
487 resultout out NOCOPY varchar2 ) ;
488
489
490 -- Send_WS_Fyi_Notif_Yes_No
491 -- IN
492 -- itemtype -- itemkey -- actid -- funcmode
493 -- OUT
494 -- Resultout
495 --
496 -- Does user want to send the FYI notification ?
497
498 procedure Send_WS_Fyi_Notif_Yes_No( itemtype in varchar2,
499 itemkey in varchar2,
500 actid in number,
501 funcmode in varchar2,
502 resultout out NOCOPY varchar2 ) ;
503
504
505 -- Send_WS_Ack_Notif_Yes_No
506 -- IN
507 -- itemtype -- itemkey -- actid -- funcmode
508 -- OUT
512
509 -- Resultout
510 --
511 -- Does user want to send the acknowledgement notification ?
513 procedure Send_WS_Ack_Notif_Yes_No( itemtype in varchar2,
514 itemkey in varchar2,
515 actid in number,
516 funcmode in varchar2,
517 resultout out NOCOPY varchar2 ) ;
518
519
520 -- locate_notifier
521 -- IN
522 -- document_number, document_type
523 -- OUT
524 -- fnd_user (resultout)
525 -- What is the web supplier defined for this particular user (if any)?
526
527 procedure locate_notifier (document_id in varchar2,
528 document_type in varchar2,
529 resultout in out NOCOPY varchar2);
530
531
532 /*******************************************************************
533 < Added this procedure as part of Bug #: 2810150 >
534
535 PROCEDURE NAME: get_user_list_with_resp
536
537 DESCRIPTION :
538 For the given document_id ( ie. po_header_id ), this procedure
539 tries to find out the correct users that need to be sent the
540 notifications.
541
542 Referenced by : Workflow procedures
543 parameters :
544 Input:
545 document_id - the document id
546 document_type - Document type
547 p_notify_only_flag -
548 The values can be 'Y' or 'N'
549 'Y' means: The procedure will return all the users that are supplier users related to the document.
550 Returns the role containing all the users in the "x_resultout" variable
551
552 'N' means: we want users that need to be sent FYI and also the users with resp.
553 x_resultout: will have the role for the users that need to be sent the FYI
554 x_role_with_resp: will have the role for users having the fucntion "POS_ACK_ORDER" assigned to
555 them.
556
557 Output:
558 x_resultout - Role for the users that need to be sent FYI
559 x_role_with_resp - Role for the users who have the ability to acknowledge.
560
561 CHANGE History: Created 27-Feb-2003 jpasala
562 *******************************************************************/
563
564 procedure locate_notifier (p_document_id in varchar2,
565 p_document_type in varchar2,
566 p_notify_only_flag in varchar2,
567 x_resultout in out NOCOPY varchar2,
568 x_role_with_resp in out NOCOPY VARCHAR2) ;
569 -- Email_Doc_Yes_No
570 -- IN
571 -- itemtype -- itemkey -- actid -- funcmode
572 -- OUT
573 -- Resultout
574 --
575 -- Does user want to email the document?
576
577 procedure Email_Doc_Yes_No( itemtype in varchar2,
578 itemkey in varchar2,
579 actid in number,
580 funcmode in varchar2,
581 resultout out NOCOPY varchar2 ) ;
582
583
584 -- Print_Document
585 -- IN
586 -- itemtype -- itemkey -- actid -- funcmode
587 -- OUT
588 -- Resultout
589 --
590 -- Print Document.
591
592 procedure Print_Document( itemtype in varchar2,
593 itemkey in varchar2,
594 actid in number,
595 funcmode in varchar2,
596 resultout out NOCOPY varchar2 ) ;
597
598
599
600 -- Fax_Document
601 -- IN
602 -- itemtype -- itemkey -- actid -- funcmode
603 -- OUT
604 -- Resultout
605 --
606 -- Fax Document.
607
608 procedure Fax_Document( itemtype in varchar2,
609 itemkey in varchar2,
610 actid in number,
611 funcmode in varchar2,
612 resultout out NOCOPY varchar2 ) ;
613
614
615
616 -- Is_document_Approved
617 -- IN
618 -- itemtype -- itemkey -- actid -- funcmode
619 -- OUT
620 -- Resultout
621 --
622 -- Is the document already approved. This may be the case if the document
623 -- was PRE-APPROVED before it goes through the reserve action. The RESERVE
624 -- would then approve the doc after it reserved the funds.
625
626 procedure Is_document_Approved( itemtype in varchar2,
627 itemkey in varchar2,
628 actid in number,
629 funcmode in varchar2,
630 resultout out NOCOPY varchar2 ) ;
631
632 -- Get_Workflow_Approval_Mode
633 -- IN
634 -- itemtype -- itemkey -- actid -- funcmode
635 -- OUT
636 -- Resultout
637 -- On-line
638 -- Background
639
640 procedure Get_Workflow_Approval_Mode( itemtype in varchar2,
641 itemkey in varchar2,
642 actid in number,
643 funcmode in varchar2,
647 -- IN
644 resultout out NOCOPY varchar2 ) ;
645
646 -- Get_Create_PO_Mode
648 -- itemtype -- itemkey -- actid -- funcmode
649 -- OUT
650 -- Resultout
651 -- Activity Performed
652
653 procedure Get_Create_PO_Mode( itemtype in varchar2,
654 itemkey in varchar2,
655 actid in number,
656 funcmode in varchar2,
657 resultout out NOCOPY varchar2 ) ;
658
659 -- Dummy
660 -- IN
661 -- itemtype -- itemkey -- actid -- funcmode
662 -- OUT
663 -- Resultout
664 -- Activity Performed
665 -- Dummy procedure that does nothing (NOOP). Used to set the
666 -- cost above the backgound engine threshold. This causes the
667 -- workflow to execute in the background.
668 procedure Dummy( itemtype in varchar2,
669 itemkey in varchar2,
670 actid in number,
671 funcmode in varchar2,
672 resultout out NOCOPY varchar2 ) ;
673
674 -- Is product source code POR ?
675 -- Determines if the requisition is created
676 -- through web requisition
677 procedure is_apps_source_POR(itemtype in varchar2,
678 itemkey in varchar2,
679 actid in number,
680 funcmode in varchar2,
681 resultout out NOCOPY varchar2);
682
683 -- Bug#3147435
684 -- Is contractor status PENDING?
685 -- Determines if the requisition has contractor_status PENDING at header level
686 procedure is_contractor_status_pending(itemtype in varchar2,
687 itemkey in varchar2,
688 actid in number,
689 funcmode in varchar2,
690 resultout out NOCOPY varchar2);
691
692 -- Is_Submitter_Last_Approver
693 -- IN
694 -- itemtype -- itemkey -- actid -- funcmode
695 -- OUT
696 -- Resultout
697 --
698 -- Bug 823167 kbenjami
699 --
700 -- Is the Submitter the last Approver?
701 -- Checks to see if submitter is also the current
702 -- approver of the doc.
703 -- Prevents two notifications from being sent to the
704 -- same person.
705 --
706 procedure Is_Submitter_Last_Approver(itemtype in varchar2,
707 itemkey in varchar2,
708 actid in number,
709 funcmode in varchar2,
710 resultout out NOCOPY varchar2 ) ;
711
712 -- This function finds out the document type, subtype and number and
713 -- concatenates them together for PLSQL_ERROR_DOC. This is need because
714 -- the document info attribute may be rolled back when error occurs.
715
716 function get_error_doc(itemtype in varchar2,
717 itemkey in varchar2) return varchar2;
718
719 -- This function finds out preparer user name. This is need because
720 -- the document info attribute may be rolled back when error occurs.
721
722 function get_preparer_user_name(itemtype in varchar2,
723 itemkey in varchar2) return varchar2;
724
725 -- This procedure send the 'PLSQL_ERROR_OCCURS' notification to the
726 -- preparer when PL/SQL error occurs during approval workflow
727
728 procedure send_error_notif(itemtype in varchar2,
729 itemkey in varchar2,
730 username in varchar2,
731 doc in varchar2,
732 msg in varchar2,
733 loc in varchar2,
734 document_id in number default NULL); /* Bug 6827401 Added document_id with default value as NULL*/
735
736 /* Bug# 1739194: kagarwal
737 ** Desc: Added new procedure to check the document manager error.
738 */
739 procedure Is_Document_Manager_Error_1_2(itemtype in varchar2,
740 itemkey in varchar2,
741 actid in number,
742 funcmode in varchar2,
743 resultout out NOCOPY varchar2);
744
745 /* bug 1759631 - procedure to check if there is any second email address
746 in the profile */
747
748 procedure PROFILE_VALUE_CHECK(itemtype in varchar2,
749 itemkey in varchar2,
750 actid in number,
751 funcmode in varchar2,
752 resultout out NOCOPY varchar2 ) ;
753
754 procedure Check_Error_Count(itemtype in varchar2,
755 itemkey in varchar2,
756 actid in number,
757 funcmode in varchar2,
758 resultout out NOCOPY varchar2);
759 procedure Initialise_Error(itemtype in varchar2,
760 itemkey in varchar2,
761 actid in number,
765 procedure acceptance_required ( itemtype in varchar2,
762 funcmode in varchar2,
763 resultout out NOCOPY varchar2);
764
766 itemkey in varchar2,
767 actid in number,
768 funcmode in varchar2,
769 result out NOCOPY varchar2 );
770 --
771
772 procedure Register_acceptance ( itemtype in varchar2,
773 itemkey in varchar2,
774 actid in number,
775 funcmode in varchar2,
776 result out NOCOPY varchar2 );
777 --
778
779 procedure Register_rejection ( itemtype in varchar2,
780 itemkey in varchar2,
781 actid in number,
782 funcmode in varchar2,
783 result out NOCOPY varchar2 );
784
785 procedure Create_SR_ASL_Yes_No( itemtype in varchar2,
786 itemkey in varchar2,
787 actid in number,
788 funcmode in varchar2,
789 resultout out NOCOPY varchar2 );
790
791 /* Bug#2353153: kagarwal
792 ** Added new PROCEDURE set_doc_mgr_context as a global procedure as this
793 ** is being used by wf apis present in different packages.
794 */
795
796 PROCEDURE Set_doc_mgr_context (itemtype VARCHAR2,
797 itemkey VARCHAR2);
798
799 /* FPI RETROACTIVE PRICING CHANGE START */
800 /*******************************************************************
801 PROCEDURE NAME: MassUpdate_Releases_Yes_No
802
803 DESCRIPTION : This is the API which determines whether the approval;
804 process should also update the releases against the
805 blanket that is getting approved has to be updated
806 with the new price change. Get the value of
807 the parameter massupdatereleases from start_Wf_process.
808 If this value is Y and if it is a blanket agreeement
809 then we need to mass update the releases with the new
810 price.
811 Referenced by :
812 parameters : Usual workflow attributes.
813
814 CHANGE History: Created 30-Sep-2002 pparthas
815 *******************************************************************/
816
817 procedure MassUpdate_Releases_Yes_No( itemtype in varchar2,
818 itemkey in varchar2,
819 actid in number,
820 funcmode in varchar2,
821 resultout out NOCOPY varchar2 );
822
823 /*******************************************************************
824 PROCEDURE NAME: MassUpdate_Releases_Workflow
825
826 DESCRIPTION : This is the API that is called from PO Approval
827 Workflow if the massupdate checkbox in the
828 Approval Window is set to Yes.
829 Referenced by :
830 parameters : Usual workflow attributes.
831
832 CHANGE History: Created 30-Sep-2002 pparthas
833 *******************************************************************/
834 procedure MassUpdate_Releases_Workflow( itemtype in varchar2,
835 itemkey in varchar2,
836 actid in number,
837 funcmode in varchar2,
838 resultout out NOCOPY varchar2 ) ;
839
840
841 /*******************************************************************
842 PROCEDURE NAME: Send_Supplier_Comm_Yes_No
843
844 DESCRIPTION : This is the API which determines whether supplier
845 Communication has to be sent when the document is
846 is approved. There is a new Change Order Workflow
847 attribute which can be set to Y if we need to
848 send supplier when the release is updated with
849 the new price after the Mass update release program is
850 run. If it is yes, then this procedure will return
851 Yes.
852 Referenced by :
853 parameters : Usual workflow attributes.
854
855 CHANGE History: Created 30-Sep-2002 pparthas
856 *******************************************************************/
857 procedure Send_Supplier_Comm_Yes_No( itemtype in varchar2,
858 itemkey in varchar2,
859 actid in number,
860 funcmode in varchar2,
861 resultout out NOCOPY varchar2 );
862
863 /* RETROACTIVE FPI END */
864
865
866 -- <FPJ Retroactive START>
867 /**
868 * Public Procedure: Retro_Invoice_Release_WF
869 * Requires:
870 * IN PARAMETERS:
871 * Usual workflow attributes.
872 * Modifies: PO_DISTRIBUTIONS_ALL.invoice_adjustment_flag
873 * Effects: This procedure updates invoice adjustment flag, and calls Costing
874 * and Inventory APIs.
875 */
876 procedure Retro_Invoice_Release_WF( itemtype in varchar2,
880 resultout out NOCOPY varchar2 );
877 itemkey in varchar2,
878 actid in number,
879 funcmode in varchar2,
881
882 procedure should_notify_cat_admin(p_item_type in varchar2,
883 p_item_key in varchar2,
884 p_act_id in number,
885 p_func_mode in varchar2,
886 x_result_out out NOCOPY varchar2 );
887
888 -- <FPJ Retroactive END>
889
890 -- <Bug 5059002 Begin>
891
892 /**
893 * Public Procedure: set_is_supplier_context_y
894 * Requires:
895 * IN PARAMETERS:
896 * Usual workflow attributes.
897 * Modifies: Sets the workflow attribute IS_SUPPLIER_CONTEXT to Y
898 */
899
900 -- Commenting this procedure. May not be required after context Setting Fix.
901 /* procedure set_is_supplier_context_y(p_item_type in varchar2,
902 p_item_key in varchar2,
903 p_act_id in number,
904 p_func_mode in varchar2,
905 x_result_out out NOCOPY varchar2); */
906
907 /**
908 * Public Procedure: set_is_supplier_context_n
909 * Requires:
910 * IN PARAMETERS:
911 * Usual workflow attributes.
912 * Modifies: Sets the workflow attribute IS_SUPPLIER_CONTEXT to N
913 */
914 /* procedure set_is_supplier_context_n(p_item_type in varchar2,
915 p_item_key in varchar2,
916 p_act_id in number,
917 p_func_mode in varchar2,
918 x_result_out out NOCOPY varchar2); */
919 -- <Bug 5059002 End>
920
921 -- HTML Orders R12
922 -- function to construct the PO view/update Page URL's
923 Function get_po_url (p_po_header_id IN NUMBER,
924 p_doc_subtype IN VARCHAR2,
925 p_mode IN VARCHAR2)
926 RETURN VARCHAR2;
927
928 --CLM apprvl
929 Function get_mod_url (p_po_header_id IN NUMBER,
930 p_draft_id IN NUMBER,
931 p_doc_subtype IN VARCHAR2,
932 p_mode IN VARCHAR2)
933 RETURN VARCHAR2;
934
935 FUNCTION get_wf_role_for_users(p_list_of_users in varchar2, p_num_users in number) return varchar2;
936
937 -- <Bug 5228261 Begin>
938 procedure GetReqAttributes(p_requisition_header_id in NUMBER,
939 itemtype in varchar2,
940 itemkey in varchar2);
941 -- <Bug 5228261 End>
942
943 -- <Bug 4950854 Begin>
944 procedure update_print_count( p_doc_id in NUMBER,
945 p_doc_type IN VARCHAR2 );
946 -- <Bug 4950854 End>
947
948 -- <BUG 5691965 START>
949 /**
950 ** Public Procedure: Update_Action_History_TimeOut
951 ** Requires:
952 ** IN PARAMETERS:
953 ** Usual workflow attributes.
954 ** Modifies: Action History
955 ** Effects: Actoin History is updated with No Action if the approval
956 ** notification is TimedOut.
957 */
958
959 PROCEDURE Update_Action_History_TimeOut (itemtype IN VARCHAR2,
963 resultout OUT NOCOPY VARCHAR2);
960 itemkey IN VARCHAR2,
961 actid IN NUMBER,
962 funcmode IN VARCHAR2,
964
965 -- <BUG 5691965 END>
966
967 -- <Bug 6144768 Begin>
968 -- When Supplier responds from iSP then the responder should show
969 -- as supplier and also supplier acknowledgement notifications
970 -- should be available in the To-Do Notification full list.
971
972 /**
973 * Public Procedure: set_is_supplier_context_y
974 * Requires:
975 * IN PARAMETERS:
976 * Usual workflow attributes.
977 * Modifies: Sets the workflow attribute IS_SUPPLIER_CONTEXT to Y
978 */
979 procedure set_is_supplier_context_y(p_item_type in varchar2,
980 p_item_key in varchar2,
981 p_act_id in number,
982 p_func_mode in varchar2,
983 x_result_out out NOCOPY varchar2);
984
985 /**
986 * Public Procedure: set_is_supplier_context_n
987 * Requires:
988 * IN PARAMETERS:
989 * Usual workflow attributes.
990 * Modifies: Sets the workflow attribute IS_SUPPLIER_CONTEXT to N
991 */
992 procedure set_is_supplier_context_n(p_item_type in varchar2,
993 p_item_key in varchar2,
994 p_act_id in number,
995 p_func_mode in varchar2,
996 x_result_out out NOCOPY varchar2);
997 -- <Bug 6144768 End>
998
999 --For SGD Project
1000 PROCEDURE SetModUpdateAfterCDGenFlag(p_draft_id IN NUMBER);
1001
1002 -- added for bug 8291565 FP - to avoid sending repeated FYI notifications to iSupplier Users for Purchase orders having same revision number
1003
1004 -- updates the po_headers_all table with the revision number of the Purchase Order for which supplier users are communicated.
1005 PROCEDURE update_supplier_com_rev_num(itemtype IN VARCHAR2,
1006 itemkey IN VARCHAR2,
1007 actid IN VARCHAR2,
1008 funcmode IN VARCHAR2,
1009 resultout OUT NOCOPY VARCHAR2);
1010
1011 -- compares the current revision number of the purchase order with the value stored as last revision number communicated to supplier users.
1012 PROCEDURE check_rev_num_supplier_notif(itemtype IN VARCHAR2,
1013 itemkey IN VARCHAR2,
1014 actid IN VARCHAR2,
1015 funcmode IN VARCHAR2,
1016 resultout OUT NOCOPY VARCHAR2);
1017
1018 -- added for bug 8291565 FP - to avoid sending repeated FYI notifications to iSupplier Users for Purchase orders having same revision number
1019
1020 end PO_REQAPPROVAL_INIT1;
[Home] [Help]