1 PACKAGE por_ame_approval_list AUTHID CURRENT_USER AS
2 /* $Header: POXAPL2S.pls 120.15.12020000.2 2013/03/14 06:13:47 rkandima ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'POR_AME_APPROVAL_LIST';
5 G_FILE_NAME CONSTANT VARCHAR2(30) := 'POXAPL2B.pls';
6
7 applicationId number :=201; /* ame is using PO id */
8 /* this variable is deprecated,
9 kept only to avoid compilation dependency
10 in FPI notifcation code POXWPA6B.pls */
11 transactionType varchar2(50) := 'PURCHASE_REQ';
12 fieldDelimiter constant varchar2(1) := ',';
13 quoteChar CONSTANT VARCHAR2(1) := '\';
14
15 -- donot change this, middle tier depends on this value
16 E_SUCCESS CONSTANT NUMBER := 0;
17 E_EXCEPTION_APPROVER_FOUND CONSTANT NUMBER := 1;
18 E_NO_NEXT_APPROVER_FOUND CONSTANT NUMBER := 2;
19 E_INVALID_APPROVER CONSTANT NUMBER := 3;
20 E_NO_AVAILABLE_INSERTION CONSTANT NUMBER := 4;
21 E_OTHER_EXCEPTION CONSTANT NUMBER := 999;
22
23 procedure get_ame_approval_list(pReqHeaderId IN NUMBER,
24 pDefaultFlag IN NUMBER,
25 pApprovalListStr OUT NOCOPY VARCHAR2,
26 pApprovalListCount OUT NOCOPY NUMBER,
27 pQuoteChar OUT NOCOPY VARCHAR2,
28 pFieldDelimiter OUT NOCOPY VARCHAR2,
29 pApprovalAction OUT NOCOPY VARCHAR2);
30
31 procedure get_old_approval_list(pReqHeaderId IN NUMBER,
32 pApprovalListStr OUT NOCOPY VARCHAR2,
33 pApprovalListCount OUT NOCOPY NUMBER,
34 pQuoteChar OUT NOCOPY VARCHAR2,
35 pFieldDelimiter OUT NOCOPY VARCHAR2);
36
37 procedure change_first_approver(pReqHeaderId IN NUMBER,
38 pPersonId IN NUMBER,
39 pApprovalListStr OUT NOCOPY VARCHAR2,
40 pApprovalListCount OUT NOCOPY NUMBER,
41 pQuoteChar OUT NOCOPY VARCHAR2,
42 pFieldDelimiter OUT NOCOPY VARCHAR2);
43
44 procedure insert_approver(pReqHeaderId IN NUMBER,
45 pPersonId IN NUMBER,
46 pAuthority IN VARCHAR2,
47 pApproverCategory IN VARCHAR2,
48 pPosition IN NUMBER,
49 pApproverNumber IN NUMBER,
50 pInsertionType IN VARCHAR2,
51 pApproverName IN VARCHAR2,
52 pApprovalListStr OUT NOCOPY VARCHAR2,
53 pApprovalListCount OUT NOCOPY NUMBER,
54 pQuoteChar OUT NOCOPY VARCHAR2,
55 pFieldDelimiter OUT NOCOPY VARCHAR2);
56
57 procedure delete_approver(pReqHeaderId IN NUMBER,
58 pPersonId IN NUMBER,
59 pOrigSystem IN VARCHAR2,
60 pOrigSystemId IN NUMBER,
61 pRecordName IN VARCHAR2,
62 pAuthority IN VARCHAR2,
63 pApprovalListStr OUT NOCOPY VARCHAR2,
64 pApprovalListCount OUT NOCOPY NUMBER,
65 pQuoteChar OUT NOCOPY VARCHAR2,
66 pFieldDelimiter OUT NOCOPY VARCHAR2);
67
68 function is_ame_reqapprv_workflow (pReqHeaderId IN NUMBER,
69 pIsRcoApproval IN BOOLEAN,
70 xAmeTransactionType OUT NOCOPY VARCHAR2)
71 return varchar2;
72
73
74 procedure is_req_pre_approved(itemtype in varchar2,
75 itemkey in varchar2,
76 actid in number,
77 funcmode in varchar2,
78 resultout out NOCOPY varchar2 );
79
80 procedure get_first_authority_approver(pReqHeaderId IN NUMBER,
81 xPersonId OUT NOCOPY VARCHAR2);
82
83 procedure can_delete_oam_approvers(pReqHeaderId IN NUMBER,
84 xResult OUT NOCOPY VARCHAR2);
85
86 procedure retrieve_approval_info( p_req_header_id in number,
87 p_is_approval_history_flow in varchar2,
88 x_is_ame_approval out NOCOPY varchar2,
89 x_approval_status out NOCOPY varchar2,
90 x_is_rco_approval out NOCOPY varchar2
91 );
92
93 procedure retrieve_approver_info( p_approver_id in number,
94 x_title out NOCOPY varchar2,
95 x_email out NOCOPY varchar2
96 );
97
98 function get_approval_group_name( p_group_id in number ) return varchar2;
99
100 procedure get_ame_approval_list_history( pReqHeaderId IN NUMBER,
101 pCallingPage IN VARCHAR2,
102 pApprovalListStr OUT NOCOPY VARCHAR2,
103 pApprovalListCount OUT NOCOPY NUMBER,
104 pQuoteChar OUT NOCOPY VARCHAR2,
105 pFieldDelimiter OUT NOCOPY VARCHAR2
106 );
107
108 procedure getAmeTransactionType( pReqHeaderId IN NUMBER,
109 pAmeTransactionType OUT NOCOPY VARCHAR2
110 );
111
112 procedure get_next_approvers_info( pReqHeaderId IN NUMBER,
113 x_approverId OUT NOCOPY NUMBER,
114 x_approverName OUT NOCOPY VARCHAR2
115 );
116
117 procedure get_person_info( p_origSystem IN VARCHAR2,
118 p_origSystemId IN NUMBER,
119 p_displayName IN VARCHAR2,
120 p_reqHeaderId IN NUMBER,
121 p_logFlag IN VARCHAR2,
122 x_personId OUT NOCOPY NUMBER,
123 x_fullName OUT NOCOPY VARCHAR2
124 );
125
126 FUNCTION is_req_forward_valid( pReqHeaderId IN NUMBER) RETURN VARCHAR2;
127
128 /* For bug 16064617 :: adding following proc which will be used in a New WF EVENT
129 created for clearing AME approval list when approver rejects the requisition
130 and Reject action gets successful just before sending FYI notification to preparer
131 about rejection of document. */
132
133 procedure Clear_ame_apprv_list_reject(itemtype in varchar2,
134 itemkey in varchar2,
135 actid in number,
136 funcmode in varchar2,
137 resultout out NOCOPY varchar2);
138
139 END;
140
141