1 PACKAGE po_reqchangerequestwf_pvt AUTHID CURRENT_USER AS
2 /* $Header: POXVRCWS.pls 120.8.12020000.3 2013/02/11 00:05:39 vegajula ship $ */
3
4
5 -- Indexes for tolerance values
6 TOL_POTOTAL_IND CONSTANT NUMBER := 1; -- Percent
7 TOL_POTOTAL_AMT_IND CONSTANT NUMBER := 2; -- Value
8 TOL_UNITPRICE_IND CONSTANT NUMBER := 3;
9 TOL_LINEAMT_IND CONSTANT NUMBER := 4; -- Percent
10 TOL_LINEAMT_AMT_IND CONSTANT NUMBER := 5; -- Value
11 TOL_SHIPQTY_IND CONSTANT NUMBER := 6;
12 TOL_SHIPAMT_IND CONSTANT NUMBER := 7; -- Percent
13 TOL_SHIPAMT_AMT_IND CONSTANT NUMBER := 8; -- Value
14 TOL_STARTDATE_IND CONSTANT NUMBER := 9;
15 TOL_ENDDATE_IND CONSTANT NUMBER := 10;
16 TOL_NEEDBY_IND CONSTANT NUMBER := 11;
17 TOL_RCO_ROUTING_IND CONSTANT NUMBER := 12;
18
19 Type ReqLineID_tbl_type is table of NUMBER index by binary_integer;
20
21 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_ReqChangeRequestWF_PVT';
22 g_update_data_exp exception;
23
24 Procedure Process_Cancelled_Req_Lines(
25 p_api_version in number,
26 p_init_msg_list in varchar2:=FND_API.G_FALSE,
27 p_commit in varchar2 :=FND_API.G_FALSE,
28 x_return_status out NOCOPY varchar2,
29 x_msg_count out NOCOPY number,
30 x_msg_data out NOCOPY varchar2,
31 p_CanceledReqLineIDs_tbl in ReqLineID_tbl_type);
32
33 procedure Update_Req_Change_Flag(itemtype in varchar2,
34 itemkey in varchar2,
35 actid in number,
36 funcmode in varchar2,
37 resultout out NOCOPY varchar2 );
38
39 procedure Insert_into_History_CHGsubmit(itemtype in varchar2,
40 itemkey in varchar2,
41 actid in number,
42 funcmode in varchar2,
43 resultout out NOCOPY varchar2 );
44
45 procedure Set_Change_Mgr_Pre_App(itemtype in varchar2,
46 itemkey in varchar2,
47 actid in number,
48 funcmode in varchar2,
49 resultout out NOCOPY varchar2 );
50
51 procedure Set_Change_Mgr_App(itemtype in varchar2,
52 itemkey in varchar2,
53 actid in number,
54 funcmode in varchar2,
55 resultout out NOCOPY varchar2 );
56 procedure Any_Cancellation_Change(itemtype in varchar2,
57 itemkey in varchar2,
58 actid in number,
59 funcmode in varchar2,
60 resultout out NOCOPY varchar2 );
61 procedure Req_Change_Needs_Approval(itemtype in varchar2,
62 itemkey in varchar2,
63 actid in number,
64 funcmode in varchar2,
65 resultout out NOCOPY varchar2 );
66
67 procedure Is_Doc_Approved(itemtype in varchar2,
68 itemkey in varchar2,
69 actid in number,
70 funcmode in varchar2,
71 resultout out NOCOPY varchar2 );
72
73 procedure Set_Doc_In_Process(itemtype in varchar2,
74 itemkey in varchar2,
75 actid in number,
76 funcmode in varchar2,
77 resultout out NOCOPY varchar2 );
78
79 procedure Compare_Revision(itemtype in varchar2,
80 itemkey in varchar2,
81 actid in number,
82 funcmode in varchar2,
83 resultout out NOCOPY varchar2 );
84
85 procedure Record_Buyer_Rejection(itemtype in varchar2,
86 itemkey in varchar2,
87 actid in number,
88 funcmode in varchar2,
89 resultout out NOCOPY varchar2 );
90
91 procedure Record_Buyer_Acceptance(itemtype in varchar2,
92 itemkey in varchar2,
93 actid in number,
94 funcmode in varchar2,
95 resultout out NOCOPY varchar2 );
96
97 procedure Start_Process_Buy_Response_WF(itemtype in varchar2,
98 itemkey in varchar2,
99 actid in number,
100 funcmode in varchar2,
101 resultout out NOCOPY varchar2 );
102
103 procedure Start_ProcessBuyerResponseWF(p_change_request_group_id in number);
104
105 procedure Insert_Buyer_Action_History(itemtype in varchar2,
106 itemkey in varchar2,
107 actid in number,
108 funcmode in varchar2,
109 resultout out NOCOPY varchar2 );
110
111 procedure Process_Buyer_Rejection(itemtype in varchar2,
112 itemkey in varchar2,
113 actid in number,
114 funcmode in varchar2,
115 resultout out NOCOPY varchar2 );
116
117 procedure Process_Cancel_Acceptance(itemtype in varchar2,
118 itemkey in varchar2,
119 actid in number,
120 funcmode in varchar2,
121 resultout out NOCOPY varchar2 );
122
123 procedure Change_Acceptance_Exists(itemtype in varchar2,
124 itemkey in varchar2,
125 actid in number,
126 funcmode in varchar2,
127 resultout out NOCOPY varchar2 );
128
129 procedure Process_Change_Acceptance(itemtype in varchar2,
130 itemkey in varchar2,
131 actid in number,
132 funcmode in varchar2,
133 resultout out NOCOPY varchar2 );
134
135 procedure Req_Change_Responded(itemtype in varchar2,
136 itemkey in varchar2,
137 actid in number,
138 funcmode in varchar2,
139 resultout out NOCOPY varchar2 );
140
141 procedure Reset_Req_Change_Flag(itemtype in varchar2,
142 itemkey in varchar2,
143 actid in number,
144 funcmode in varchar2,
145 resultout out NOCOPY varchar2 );
146
147 procedure New_Po_Change_Exists(itemtype in varchar2,
148 itemkey in varchar2,
149 actid in number,
150 funcmode in varchar2,
151 resultout out NOCOPY varchar2 );
152
153 procedure Record_Po_Approval(itemtype in varchar2,
154 itemkey in varchar2,
155 actid in number,
156 funcmode in varchar2,
157 resultout out NOCOPY varchar2 );
158
159 procedure Record_Po_Rejection(itemtype in varchar2,
160 itemkey in varchar2,
161 actid in number,
162 funcmode in varchar2,
163 resultout out NOCOPY varchar2 );
164
165 procedure Validate_Chg_Against_New_PO(itemtype in varchar2,
166 itemkey in varchar2,
167 actid in number,
168 funcmode in varchar2,
169 resultout out NOCOPY varchar2 );
170
171 procedure Get_Req_Chg_Attributes(itemtype in varchar2,
172 itemkey in varchar2,
173 actid in number,
174 funcmode in varchar2,
175 resultout out NOCOPY varchar2 );
176
177 procedure Start_From_Po_Cancel(itemtype in varchar2,
178 itemkey in varchar2,
179 actid in number,
180 funcmode in varchar2,
181 resultout out NOCOPY varchar2 );
182
183 procedure Change_Request_Mgr_Approved(itemtype in varchar2,
184 itemkey in varchar2,
185 actid in number,
186 funcmode in varchar2,
187 resultout out NOCOPY varchar2 );
188
189 procedure Reset_Change_Flag(itemtype in varchar2,
190 itemkey in varchar2,
191 actid in number,
192 funcmode in varchar2,
193 resultout out NOCOPY varchar2 );
194
195 procedure Convert_Into_Po_Change(itemtype in varchar2,
196 itemkey in varchar2,
197 actid in number,
198 funcmode in varchar2,
199 resultout out NOCOPY varchar2 );
200
201 procedure Convert_Into_So_Change(itemtype in varchar2,
202 itemkey in varchar2,
203 actid in number,
204 funcmode in varchar2,
205 resultout out NOCOPY varchar2 );
206
207 PROCEDURE ConvertIntoSOChange(p_chn_request_group_id IN NUMBER,
208 p_document_header_id IN NUMBER,
209 p_document_line_id IN NUMBER,
210 p_document_num IN NUMBER,
211 p_old_quantity IN NUMBER,
212 p_new_quantity IN NUMBER,
213 p_old_need_by_date IN DATE,
214 p_new_need_by_date IN DATE,
215 p_action_type IN VARCHAR2,
216 p_mode IN VARCHAR2,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_return_msg out NOCOPY varchar2);
219
220 procedure Kickoff_POChange_WF(itemtype in varchar2,
221 itemkey in varchar2,
222 actid in number,
223 funcmode in varchar2,
224 resultout out NOCOPY varchar2 );
225
226 procedure Is_Chg_Mgr_Pre_App(itemtype in varchar2,
227 itemkey in varchar2,
228 actid in number,
229 funcmode in varchar2,
230 resultout out NOCOPY varchar2 );
231
232 procedure Start_Poapprv_WF(itemtype in varchar2,
233 itemkey in varchar2,
234 actid in number,
235 funcmode in varchar2,
236 resultout out NOCOPY varchar2 );
237 procedure Any_Requester_Change(itemtype in varchar2,
238 itemkey in varchar2,
239 actid in number,
240 funcmode in varchar2,
241 resultout out NOCOPY varchar2 );
242 procedure Set_Data_Req_Chn_Evt(itemtype in varchar2,
243 itemkey in varchar2,
244 actid in number,
245 funcmode in varchar2,
246 resultout out NOCOPY varchar2 );
247
248 procedure Get_Change_Attribute(itemtype in varchar2,
249 itemkey in varchar2,
250 actid in number,
251 funcmode in varchar2,
252 resultout out NOCOPY varchar2 );
253
254 procedure Reminder_Need_To_Be_Sent(itemtype in varchar2,
255 itemkey in varchar2,
256 actid in number,
257 funcmode in varchar2,
258 resultout out NOCOPY varchar2 );
259 procedure Set_Change_Rejected(itemtype in varchar2,
260 itemkey in varchar2,
261 actid in number,
262 funcmode in varchar2,
263 resultout out NOCOPY varchar2 );
264 procedure Reset_Reminder_Counter(itemtype in varchar2,
265 itemkey in varchar2,
266 actid in number,
267 funcmode in varchar2,
268 resultout out NOCOPY varchar2 );
269 procedure Update_Action_History_App_Rej(itemtype in varchar2,
270 itemkey in varchar2,
271 actid in number,
272 funcmode in varchar2,
273 resultout out NOCOPY varchar2 );
274
275 procedure Get_Total_Amount_Currency(itemtype in varchar2,
276 itemkey in varchar2,
277 actid in number,
278 funcmode in varchar2,
279 resultout out NOCOPY varchar2 );
280
281 Procedure Submit_Req_Change(p_api_version IN NUMBER,
282 p_commit IN VARCHAR2,
283 p_req_header_id IN NUMBER,
284 p_note_to_approver IN VARCHAR2,
285 p_initiator IN VARCHAR2,
286 x_return_status OUT NOCOPY VARCHAR2);
287
288
289 Procedure Submit_Internal_Req_Change(p_api_version IN NUMBER,
290 p_commit IN VARCHAR2,
291 p_req_header_id IN NUMBER,
292 p_note_to_approver IN VARCHAR2,
293 p_initiator IN VARCHAR2,
294 x_return_status OUT NOCOPY VARCHAR2);
295
296 Procedure Record_Buyer_Response(
300 p_change_request_id IN NUMBER,
297 p_api_version in number,
298 p_commit in varchar2,
299 x_return_status out NOCOPY varchar2,
301 p_acceptance_flag in varchar2,
302 p_responded_by in number,
303 p_response_reason in varchar2);
304
305 Procedure Process_Buyer_Response(
306 p_api_version in number,
307 x_return_status out NOCOPY varchar2,
308 p_change_request_group_id IN NUMBER);
309
310 procedure Get_Change_Total_Attr( itemtype in varchar2,
311 itemkey in varchar2,
312 actid in number,
313 funcmode in varchar2,
314 resultout out NOCOPY varchar2);
315
316 Procedure Set_Buyer_Approval_Notfn_Attr(itemtype in varchar2,
317 itemkey in varchar2,
318 actid in number,
319 funcmode in varchar2,
320 resultout out NOCOPY varchar2);
321
322 PROCEDURE Reject_Supplier_Change( itemtype in varchar2,
323 itemkey in varchar2,
324 actid in number,
325 funcmode in varchar2,
326 resultout out NOCOPY varchar2 );
327
328 PROCEDURE Accept_Supplier_Change( itemtype in varchar2,
329 itemkey in varchar2,
330 actid in number,
331 funcmode in varchar2,
332 resultout out NOCOPY varchar2 );
333
334 PROCEDURE Start_POChange_WF( itemtype IN VARCHAR2,
335 itemkey IN VARCHAR2,
336 actid IN NUMBER,
337 funcmode IN VARCHAR2,
338 resultout OUT NOCOPY VARCHAR2 );
339
340 PROCEDURE Is_Tolerance_Check_Needed( itemtype IN VARCHAR2,
341 itemkey IN VARCHAR2,
342 actid IN NUMBER,
343 funcmode IN VARCHAR2,
344 resultout OUT NOCOPY VARCHAR2 );
345
346 PROCEDURE Changes_Wthn_Buyer_Tol_Values( itemtype IN VARCHAR2,
347 itemkey IN VARCHAR2,
348 actid IN NUMBER,
349 funcmode IN VARCHAR2,
350 resultout OUT NOCOPY VARCHAR2 ) ;
351
352 PROCEDURE More_Po_To_Process( itemtype IN VARCHAR2,
353 itemkey IN VARCHAR2,
354 actid IN NUMBER,
355 funcmode IN VARCHAR2,
356 resultout OUT NOCOPY VARCHAR2 );
357
358 PROCEDURE Accept_Po_Changes( itemtype IN VARCHAR2,
359 itemkey IN VARCHAR2,
360 actid IN NUMBER,
361 funcmode IN VARCHAR2,
362 resultout OUT NOCOPY VARCHAR2 );
363
364 PROCEDURE Set_Buyer_FYI_Notif_Attributes( itemtype IN VARCHAR2,
365 itemkey IN VARCHAR2,
366 actid IN NUMBER,
367 funcmode IN VARCHAR2,
368 resultout OUT NOCOPY VARCHAR2 );
369
370 PROCEDURE More_Req_To_Process( itemtype IN VARCHAR2,
371 itemkey IN VARCHAR2,
372 actid IN NUMBER,
373 funcmode IN VARCHAR2,
374 resultout OUT NOCOPY VARCHAR2 );
375
376 PROCEDURE Start_Notify_Requester_Process( itemtype IN VARCHAR2,
377 itemkey IN VARCHAR2,
378 actid IN NUMBER,
379 funcmode IN VARCHAR2,
380 resultout OUT NOCOPY VARCHAR2 );
381
382 PROCEDURE Start_NotifyRequesterProcess( p_po_change_request_group_id IN NUMBER,
383 p_req_item_type IN VARCHAR2,
384 p_req_item_key IN VARCHAR2 );
385
386 FUNCTION get_formatted_total (l_total IN NUMBER, l_po_currency VARCHAR2) return NUMBER;
387
388
389 PROCEDURE req_line_CANCEL(p_req_line_id IN NUMBER,
390 x_return_status OUT NOCOPY VARCHAR2);
391
392
393 PROCEDURE update_req_line_date_changes(p_req_line_id IN NUMBER,
394 p_need_by_date IN DATE,
395 x_return_status OUT NOCOPY VARCHAR2);
396
397
398 PROCEDURE update_reqline_quan_changes(p_req_line_id IN NUMBER,
399 p_delta_quantity IN NUMBER,
400 p_uom IN VARCHAR2 default null,
401 x_return_status OUT NOCOPY VARCHAR2);
402
403
404 PROCEDURE SEND_INTERNAL_NOTIF(itemtype IN VARCHAR2,
405 itemkey IN VARCHAR2,
406 actid IN NUMBER,
407 funcmode IN VARCHAR2,
408 resultout OUT NOCOPY VARCHAR2 );
409
410
411 PROCEDURE NEXT_INTERNAL_NOTIF(itemtype IN VARCHAR2,
412 itemkey IN VARCHAR2,
413 actid IN NUMBER,
414 funcmode IN VARCHAR2,
415 resultout OUT NOCOPY VARCHAR2 );
416
417
418 FUNCTION get_sales_order_org( p_req_hdr_id IN NUMBER DEFAULT null,
419 p_req_line_id IN NUMBER DEFAULT null
420 ) RETURN NUMBER;
421
422 FUNCTION get_requisition_org( p_req_hdr_id IN NUMBER DEFAULT null,
423 p_req_line_id IN NUMBER DEFAULT null
424 ) RETURN NUMBER;
425
426 -- Bug 9738629
427
428 FUNCTION GET_RATE(po_currency_code in varchar2,
429 req_currency_code in varchar2,
430 po_rate in number,
431 req_rate in number) RETURN number;
432
433
434
435 -- 14227140 changes starts
436 /** This procedure will be called from
437 *1. Req initiated IR ISO change from poreqcha WF
438 *2. Req Rescedule initiated change from CP
439 *3. Fulfillment intiated change.
440 *
441 *The procedure updates the requisition line with changes
442 *of quntity.
443 *It retrives the existing quantity and adds the delta quntity
444 *to compute the new quantity
445 * @param p_req_line_id number holds the req line number
446 * @param p_delta_prim_quantity number changed Prim Qty of SO
447 * @param p_delta_sec_quantity number changed Secondary Qty of SO
448 * @param p_uom number unit of measure.
449 * @param x_return_status returns the tstatus of the api
450 */
451 PROCEDURE update_reqline_quan_changes(p_req_line_id IN NUMBER,
452 p_delta_prim_quantity IN NUMBER,
453 p_delta_sec_quantity IN NUMBER,
454 p_uom IN VARCHAR2 default null,
455 x_return_status OUT NOCOPY VARCHAR2);
456 -- 14227140 changes ends
457
458 -- 15875594 changes starts
459 /** This function will be called for
460 * req_line_changes attachments for Buyer notificaiotn.
461 *
462 This function will gets the requisition line id for given change request id
463 * gets the req line id by using line location id, if line location id is not present
464 * gets the line id by using parent change request id.
465 *
466 *The function gets the po requisition line id for a given
467 * change request id.
468 * @param l_change_request_id number holds the change requset number
469 * @retuns NUMBER req line number
470 */
471 FUNCTION get_req_line_num_chng_grp( l_change_request_id NUMBER)
472 RETURN NUMBER;
473 -- 15875594 changes ends
474
475 end PO_ReqChangeRequestWF_PVT;