1 PACKAGE ozf_fund_request_apr_pvt AUTHID CURRENT_USER AS
2 /* $Header: ozfvwfrs.pls 115.4 2003/06/26 05:27:32 mchang ship $ */
3
4 -------------------------------------------------------------------------------------
5 -- Start of Comments
6 --
7 -- NAME
8 -- OZF_WF_Request_Apr_PVT
9 --
10 -- PURPOSE
11 -- This package contains the workflow procedures for
12 -- Fund Request Approvals in Oracle Marketing(Funds and Budgets)
13 --
14 -- HISTORY
15 -- 03/28/2001 MUMU PANDE CREATION
16 -----------------------------------------------------------------------------------------
17
18
19 ---------------------------------------------------------------------
20 -- PROCEDURE
21 -- Create_Fund_Request
22 --
23 --
24 -- PURPOSE
25 -- This Procedure will create the fund request
26 --
27 --
28 -- IN
29 -- p_commit IN VARCHAR2 -- Transaction commit identifier
30 -- p_timeout IN NUMBER -- time out in DAYS specified for the workflow notifications
31 -- p_update_status IN VARCHAR2 -- Fund status update flag
32 -- p_approval_for IN VARCHAR2 -- Object triggering request
33 -- p_approval_for_id IN NUMBER -- Object ID triggering request
34 -- p_requester_id IN NUMBER -- User triggering request
35 -- p_requested_amount IN NUMBER -- amount requested
36 -- p_approval_fm IN VARCHAR2 -- Object towards which the request is directed
37 -- p_approval_fm_id IN NUMBER -- Object ID which the request is directed
38 -- p_workflowprocess IN VARCHAR2 -- Main process in the workflow
39 -- p_item_type IN VARCHAR2 -- Item type in the workflow
40 ----------------------------------------- these 2 parameter added by mpande ---------------------------------
41 --p_transfer_type IN VARCHAR2 -- indicates wether the transfer is release or transfer.Transfer is from 1 fund to another while release is from holdback_amt to
42 -- available amount of the same fund
43 -- p_description IN VARCHAR2 --- indicates note associated with the transfer
44 -- mpande 10/18/2001 added
45 -- p_allocation_flag IN VARCHAR2 := 'N' -- flag to indicate whether it is an allocation or not
46 -- Used By Activities
47 --
48 -- NOTES
49 -- IF x_is_requester_owner is set to 'YES', then this API calls Approve_Request to update the fund
50 -- transfer transactions.
51 -- If x_is_requester_owner then the calling API can trigger the workflow API : Start_Request_Process
52 --
53 -- HISTORY
54 -- 02/16/2000 SHITIJ VATSA CREATION
55 -- 04/16/2000 MPANDE UPDATED
56 -- 10/22/2001 mpande UPDATED
57 -- End of Comments
58
59 PROCEDURE create_fund_request(
60 p_commit IN VARCHAR2 := fnd_api.g_false
61 ,p_update_status IN VARCHAR2 := 'Y'
62 ,p_approval_for IN VARCHAR2 := 'FUND'
63 ,p_approval_for_id IN NUMBER
64 ,p_requester_id IN NUMBER
65 ,p_requested_amount IN NUMBER
66 ,p_approval_fm IN VARCHAR2 := 'FUND'
67 ,p_approval_fm_id IN NUMBER DEFAULT NULL
68 /* yzhao 07/17/2002 fix bug 2410322 - UPG1157:9I:OZF PACKAGE/PACKAGE BODY MISMATCHES
69 ,p_transfer_type IN VARCHAR2 := 'TRANSFER' --- 'REQUEST' OR 'TRANSFER'
70 */
71 ,p_transfer_type IN VARCHAR2 := 'REQUEST' --- 'REQUEST' OR 'TRANSFER'
72 ,p_child_flag IN VARCHAR2 := 'N' -- flag to indicate whether it is a child fund creation
73 ,p_act_budget_id IN NUMBER := NULL -- request_id ( for a child fund it is null)
74 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
75 ,p_justification IN VARCHAR2
76 -- 10/22/2001 mpande Changed code different owner allocation bug
77 ,p_allocation_flag IN VARCHAR2 := 'N' -- flag to indicate whether it is an allocation or not
78 ,x_return_status OUT NOCOPY VARCHAR2
79 ,x_msg_count OUT NOCOPY NUMBER
80 ,x_msg_data OUT NOCOPY VARCHAR2
81 ,x_request_id OUT NOCOPY NUMBER
82 ,x_approver_id OUT NOCOPY NUMBER
83 ,x_is_requester_owner OUT NOCOPY VARCHAR2 -- Use this variable to conditionally trigger the workflow if value ='NO'
84 );
85
86 -- PROCEDURE
87 -- Notify_requestor_FYI
88 --
89 -- PURPOSE
90 -- Generate the Requisition Document for display in messages, either
91 -- text or html
92 -- IN
93 -- document_id - Item Key
94 -- display_type - either 'text/plain' or 'text/html'
95 -- document - document buffer
96 -- document_type - type of document buffer created, either 'text/plain'
97 -- or 'text/html'
98 -- OUT
99 -- USED BY
100 -- - Oracle MArketing Generic Apporval
101 -- HISTORY
102 -- 03/15/2001 MUMU PANDE CREATION
103 -----------------------------------------------------------------
104 PROCEDURE notify_requestor_fyi(
105 document_id IN VARCHAR2
106 ,display_type IN VARCHAR2
107 ,document IN OUT NOCOPY VARCHAR2
108 ,document_type IN OUT NOCOPY VARCHAR2);
109
110 --------------------------------------------------------------------------
111 -- PROCEDURE
112 -- Notify_requestor_of Approval
113 --
114 -- PURPOSE
115 -- Generate the Approval Document for display in messages, either
116 -- text or html
117 -- IN
118 -- document_id - Item Key
119 -- display_type - either 'text/plain' or 'text/html'
120 -- document - document buffer
121 -- document_type - type of document buffer created, either 'text/plain'
122 -- or 'text/html'
123 -- OUT
124 -- USED BY
125 -- - Oracle MArketing Generic Apporval
126 -- HISTORY
127 -- 03/15/2001 MUMU PANDE CREATION
128 ----------------------------------------------------------------------------
129
130 PROCEDURE notify_requestor_of_approval(
131 document_id IN VARCHAR2
132 ,display_type IN VARCHAR2
133 ,document IN OUT NOCOPY VARCHAR2
134 ,document_type IN OUT NOCOPY VARCHAR2);
135
136 --------------------------------------------------------------------------
137 -- PROCEDURE
138 -- Notify_requestor_of rejection
139 --
140 -- PURPOSE
141 -- Generate the Rejection Document for display in messages, either
142 -- text or html
143 -- IN
144 -- document_id - Item Key
145 -- display_type - either 'text/plain' or 'text/html'
146 -- document - document buffer
147 -- document_type - type of document buffer created, either 'text/plain'
148 -- or 'text/html'
149 -- OUT
150 -- USED BY
151 -- - Oracle MArketing Generic Apporval
152 -- HISTORY
153 -- 03/15/2001 MUMU PANDE CREATION
154 -------------------------------------------------------------------------------
155
156 PROCEDURE notify_requestor_of_rejection(
157 document_id IN VARCHAR2
158 ,display_type IN VARCHAR2
159 ,document IN OUT NOCOPY VARCHAR2
160 ,document_type IN OUT NOCOPY VARCHAR2);
161
162 --------------------------------------------------------------------------
163 -- PROCEDURE
164 -- Notify_requestor_of rejection
165 --
166 -- PURPOSE
167 -- Generate the Rejection Document for display in messages, either
168 -- text or html
169 -- IN
170 -- document_id - Item Key
171 -- display_type - either 'text/plain' or 'text/html'
172 -- document - document buffer
173 -- document_type - type of document buffer created, either 'text/plain'
174 -- or 'text/html'
175 -- OUT
176 -- USED BY
177 -- - Oracle MArketing Generic Apporval
178 -- HISTORY
179 -- 03/15/2001 MUMU PANDE CREATION
180
181
182 PROCEDURE notify_approval_required(
183 document_id IN VARCHAR2
184 ,display_type IN VARCHAR2
185 ,document IN OUT NOCOPY VARCHAR2
186 ,document_type IN OUT NOCOPY VARCHAR2);
187
188 --------------------------------------------------------------------------
189 -- PROCEDURE
190 -- notify_appr_req_reminder
191 --
192 -- PURPOSE
193 -- Generate the Rejection Document for display in messages, either
194 -- text or html
195 -- IN
196 -- document_id - Item Key
197 -- display_type - either 'text/plain' or 'text/html'
198 -- document - document buffer
199 -- document_type - type of document buffer created, either 'text/plain'
200 -- or 'text/html'
201 -- OUT
202 -- USED BY
203 -- - Oracle MArketing Generic Apporval
204 -- HISTORY
205 -- 03/15/2001 MUMU PANDE CREATION
206
207
208 PROCEDURE notify_appr_req_reminder(
209 document_id IN VARCHAR2
210 ,display_type IN VARCHAR2
211 ,document IN OUT NOCOPY VARCHAR2
212 ,document_type IN OUT NOCOPY VARCHAR2);
213
214 ---------------------------------------------------------------------
215 -- PROCEDURE
216 -- Set_ParBudget_Activity_details
217 --
218 --
219 -- PURPOSE
220 -- This Procedure will set all the item attribute details
221 --
222 --
223 -- IN
224 --
225 --
226 -- OUT
227 --
228 -- Used By Activities
229 --
230 -- NOTES
231 --
232 --
233 --
234 -- HISTORY
235 -- 02/20/2001 MUMU PANDE CREATION
236 -- End of Comments
237 --------------------------------------------------------------------
238 PROCEDURE set_trans_activity_details(
239 itemtype IN VARCHAR2
240 ,itemkey IN VARCHAR2
241 ,actid IN NUMBER
242 ,funcmode IN VARCHAR2
243 ,resultout OUT NOCOPY VARCHAR2);
244
245 ---------------------------------------------------------------------
246 -- PROCEDURE
247 -- Update_ParBudget_Statas
248 --
249 --
250 -- PURPOSE
251 -- This Procedure will update the status
252 --
253 --
254 -- IN
255 --
256 --
257 -- OUT
258 --
259 -- Used By Activities
260 --
261 -- NOTES
262 --
263 --
264 --
265 -- HISTORY
266 -- 02/20/2001 MUMU PANDE CREATION
267 -- End of Comments
268 --------------------------------------------------------------------
269
270
271 PROCEDURE update_budgettrans_status(
272 itemtype IN VARCHAR2
273 ,itemkey IN VARCHAR2
274 ,actid IN NUMBER
275 ,funcmode IN VARCHAR2
276 ,resultout OUT NOCOPY VARCHAR2);
277 ---------------------------------------------------------------------
278 -- PROCEDURE
279 -- Approve_Holback
280 --
281 --
282 -- PURPOSE
283 -- This Procedure will Update the fund where reserve is done to holdback from
284 -- available amount
285 --
286 -- IN
287 -- p_commit IN VARCHAR2 -- Transaction commit identifier
288 -- p_act_budget_id IN NUMBER -- Fund request identifier having the request details
289 -- p_transac_fund_id IN NUMBER -- transaction fund
290 -- p_requester_id IN NUMBER -- Person initiating the fund release --should always be the owner of the fund
291 -- p_requested_amount IN NUMBER -- Requested amount
292 --
293 -- OUT
294 -- x_return_status OUT VARCHAR2
295 -- x_msg_count OUT NUMBER
296 -- x_msg_data OUT VARCHAR2
297 --
298 -- Used By Activities
299 --
300 -- NOTES
301 --
302 --
303 --
304 -- HISTORY
305 -- 04/06/2001 MPANDE CREATION
306 --
307 -- End of Comments
308
309 PROCEDURE approve_holdback(
310 p_commit IN VARCHAR2 := fnd_api.g_false
311 ,p_act_budget_id IN NUMBER
312 ,p_transfer_type IN VARCHAR2
313 ,p_transac_fund_id IN NUMBER
314 ,p_requester_id IN NUMBER
315 ,p_approver_id IN NUMBER
316 ,p_requested_amount IN NUMBER
317 ,x_return_status OUT NOCOPY VARCHAR2
318 ,x_msg_count OUT NOCOPY NUMBER
319 ,x_msg_data OUT NOCOPY VARCHAR2);
320
321 END ozf_fund_request_apr_pvt;