1 PACKAGE PO_RETROACTIVE_PRICING_PVT AS
2 /*$Header: POXRPRIS.pls 120.3 2005/09/14 05:03:52 pchintal noship $*/
3
4 /*===========================================================================
5 PACKAGE NAME: PO_RETROACTIVE_PRICING_PVT
6
7 DESCRIPTION: This package contains server side procedures to
8 update the releases/Std PO against BA/GA when
9 there is a retroactive price update to BA/GA.
10
11 CLIENT/SERVER: Server
12
13 OWNER: pparthas
14
15 FUNCTION/PROCEDURE: MassUpdate_Releases()
16 ===========================================================================*/
17
18 --------------------------------------------------------------------------------
19 --Start of Comments
20 --Name: MassUpdate_Releases
21 --Pre-reqs:
22 -- None.
23 --Modifies:
24 -- None.
25 --Locks:
26 -- None.
27 --Function:
28 -- This API is called from the Approval Window or by the
29 -- Concurrent Program. This procedure will update all
30 -- the releases against Blanket Agreeements or Standard
31 -- POs against Global Agreements that have lines that
32 -- are retroactively changed.
33 --Parameters:
34 --IN:
35 --p_api_version
36 -- Version number of API that caller expects. It
37 -- should match the l_api_version defined in the
38 -- procedure (expected value : 1.0)
39 --p_validation_level
40 -- validation level api uses
41 --p_vendor_id
42 -- Site_id of the Supplier site selected by the user.
43 --p_po_header_id
44 -- Header_id of the Blanket/Global Agreement selected by user.
45 --p_category_struct_id
46 -- Purchasing Category structure Id
47 --p_category_from / p_category_to
48 -- Category Range that user selects to process retroactive changes
49 --p_item_num_from / p_item_num_to
50 -- Item Range that user selects to process retroactive changes
51 --p_date
52 -- All releases or Std PO created on or after this date must be changed.
53 --p_communicate_update
54 -- Communicate Price Updates to Supplier
55 --OUT:
56 --x_return_status
57 -- FND_API.G_RET_STS_SUCCESS if API succeeds
58 -- FND_API.G_RET_STS_ERROR if API fails
59 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
60 --Testing:
61 --
62 --End of Comments
63 --------------------------------------------------------------------------------
64
65 Procedure MassUpdate_Releases ( p_api_version IN NUMBER,
66 p_validation_level IN NUMBER,
67 p_vendor_id IN Number,
68 p_vendor_site_id IN Number,
69 p_po_header_id IN Number,
70 p_category_struct_id IN Number,
71 p_category_from IN Varchar2,
72 p_category_to IN Varchar2,
73 p_item_from IN Varchar2,
74 p_item_to IN Varchar2,
75 p_date IN Date,
76 -- <FPJ Retroactive Price>
77 p_communicate_update IN VARCHAR2 DEFAULT NULL,
78 x_return_status OUT NOCOPY VARCHAR2);
79
80
81
82 /*******************************************************************
83 PROCEDURE NAME: Build_Item_Cursor
84
85 DESCRIPTION : This procedure builds the item cursor statement.
86 This statement needs to be built at run time
87 (dynamic SQL) because of the dynamic nature of the
88 System Item and Category flexfields.
89 Referenced by :
90 parameters : p_cat_structure_id IN Number
91 p_cat_from IN VARCHAR2
92 p_cat_to IN VARCHAR2
93 p_item_from IN Varchar2
94 p_item_to IN Varchar2
95 x_item_cursor IN Varchar2
96
97 CHANGE History: Created 30-Sep-2002 pparthas
98 *******************************************************************/
99 PROCEDURE Build_Item_Cursor
100 ( p_cat_structure_id IN NUMBER
101 , p_cat_from IN VARCHAR2
102 , p_cat_to IN VARCHAR2
103 , p_item_from IN VARCHAR2
104 , p_item_to IN VARCHAR2
105 , x_item_cursor IN OUT NOCOPY VARCHAR2
106 );
107
108
109 /*******************************************************************
110 PROCEDURE NAME: WrapUp_Releases
111
112 DESCRIPTION : This API is called from the main procedure
113 MassUpdate_Releases to wrap up the releases
114 If the Release had any shipment price updated,
115 then document revision had to incremented and
116 approval had to initiated if necessary.
117 Referenced by :
118 parameters : p_old_po_release_id IN Number
119 p_po_release_id IN Number
120 p_po_line_location_id IN Number
121 p_last_release_shipment IN Varchar2(1)
122 x_retroactive_change IN Varchar2(1)
123
124 CHANGE History: Created 30-Sep-2002 pparthas
125 *******************************************************************/
126 PROCEDURE WrapUp_Releases;
127
128
129 /*******************************************************************
130 PROCEDURE NAME: WrapUp_Standard_PO
131
132 DESCRIPTION : This API is called from the main procedure
133 MassUpdate_Releases to process the Standard PO that
134 are created against the Global agreement lines that
135 have been retroactively changed. If the shipments are
136 updated with the new price document revision had to
137 incremented and approval had to initiated if necessary.
138 Referenced by :
139 parameters : p_old_po_header_id IN Number
140 p_po_header_id IN Number
141 p_po_line_location_id IN Number
142 p_last_standard_shipment IN Varchar2(1)
143 x_retroactive_change IN Varchar2(1)
144
145 CHANGE History: Created 30-Sep-2002 pparthas
146 *******************************************************************/
147 PROCEDURE WrapUp_Standard_PO;
148
149
150 /*******************************************************************
151 PROCEDURE NAME: Process_Price_Change
152
153 DESCRIPTION : This API is called from the procedure
154 Process_Releases to update the release with the
155 new price as a result of a retroactive change in
156 the blanket line.
157 Referenced by :
158 parameters : p_po_line_location_id IN Number
159 x_retroactive_change IN Varchar2(1)
160
161 CHANGE History: Created 30-Sep-2002 pparthas
162 *******************************************************************/
163 PROCEDURE Process_Price_Change
164 (p_row_id IN VARCHAR2,
165 p_document_id IN NUMBER,
166 p_po_line_location_id IN NUMBER,
167 p_retroactive_date IN DATE,
168 p_quantity IN NUMBER,
169 p_ship_to_organization_id IN NUMBER,
170 p_ship_to_location_id IN NUMBER,
171 p_po_line_id IN NUMBER,
172 p_old_price_override IN NUMBER,
173 p_need_by_date IN DATE,
174 p_global_agreement_flag IN VARCHAR2,
175 p_authorization_status IN VARCHAR2,
176 p_rev_num IN Number,
177 p_archived_rev_num IN Number,
178 p_contract_id IN NUMBER --<R12 GBPA Adv Pricing >
179 );
180
181
182 /*******************************************************************
183 PROCEDURE NAME: Launch_PO_Approval
184
185 DESCRIPTION : This API calls the Pl/sql API for submission check for
186 PO and if they are are successful calls the
187 procedure PO_RETROACTIVE_PRICING_PVT.
188 Retroactive_Launch_Approval which initiates the
189 Approval Workflow.
190 Referenced by :
191 parameters :
192
193 CHANGE History: Created 18-Oct-2002 pparthas
194 *******************************************************************/
195
196 PROCEDURE Launch_PO_Approval;
197
198 /*******************************************************************
199 PROCEDURE NAME: Launch_REL_Approval
200
201 DESCRIPTION : This API calls the Pl/sql API for submission check for
202 Releases and if they are are successful calls the
203 procedure PO_RETROACTIVE_PRICING_PVT.
204 Retroactive_Launch_Approval which initiates the
205 Approval Workflow.
206 Referenced by :
207 parameters :
208
209 CHANGE History: Created 18-Oct-2002 pparthas
210 *******************************************************************/
211
212 PROCEDURE Launch_REL_Approval;
213
214 /*******************************************************************
215 PROCEDURE NAME: Retroactive_Launch_Approval
216
217 DESCRIPTION : This API is called from the procedure
218 WrapUp_Releases and WrapUp_Standard_PO.
219 This procedure calls the po_reqapproval_init1.
220 start_wf_process after setting the common
221 parameters.
222 Referenced by :
223 parameters : p_po_line_location_id IN Number
224 x_retroactive_change IN Varchar2(1)
225
226 CHANGE History: Created 30-Sep-2002 pparthas
227 *******************************************************************/
228 Procedure Retroactive_Launch_Approval(
229 p_doc_id IN Number,
230 p_doc_type IN Varchar2,
231 p_doc_subtype IN Varchar2);
232
233
234 /*******************************************************************
235 FUNCTION NAME : getDatabaseVersion
236
237 DESCRIPTION : This API is called from the procedure
238 MassUpdate_Releases.
239 It returns the database version number.
240 Referenced by :
241
242 Parameters : l_version NUMBER
243
244 CHANGE History: Created 21-Mar-2003 davidng
245 Deleted 02-Jul-2005 scolvenk
246 *******************************************************************/
247
248
249
250 -- <FPJ Retroactive START>
251 --------------------------------------------------------------------------------
252 --Start of Comments
253 --Name: Get_Retro_mode
254 --Pre-reqs:
255 -- None.
256 --Modifies:
257 -- None.
258 --Locks:
259 -- None.
260 --Function:
261 -- This function returns retroactive pricing mode.
262 --Parameters:
263 --IN:
264 -- None.
265 --RETURN:
266 -- 'NEVER': Not Supported
267 -- 'OPEN_RELEASES': Retroactive Pricing Update on Open Releases
268 -- 'ALL_RELEASES': Retroactive Pricing Update on All Releases
269 --Testing:
270 --
271 --End of Comments
272 -------------------------------------------------------------------------------
273 FUNCTION Get_Retro_Mode RETURN VARCHAR2;
274
275 --------------------------------------------------------------------------------
276 --Start of Comments
277 --Name: Is_Retro_Update
278 --Pre-reqs:
279 -- None.
280 --Modifies:
281 -- None.
282 --Locks:
283 -- None.
284 --Function:
285 -- This function returns retroactive pricing status.
286 --Parameters:
287 --IN:
288 --p_document_id
289 -- The id of the document (po_header_id or po_release_id)
290 --p_document_type
291 -- The type of the document
292 -- PO : Standard PO
293 -- RELEASE : Release
294 --RETURN:
295 -- 'Y': Retroactive Pricing Update
296 -- 'N': Not a Retroactive Pricing Update
297 --Testing:
298 --
299 --End of Comments
300 -------------------------------------------------------------------------------
301 FUNCTION Is_Retro_Update(p_document_id IN NUMBER,
302 p_document_type IN VARCHAR2)
303 RETURN VARCHAR2;
304
305 --------------------------------------------------------------------------------
306 --Start of Comments
307 --Name: Reset_Retro_Update
308 --Pre-reqs:
309 -- None.
310 --Modifies:
311 -- None.
312 --Locks:
313 -- None.
314 --Function:
315 -- This function resets retroactive_date.
316 --Parameters:
317 --IN:
318 --p_document_id
319 -- The id of the document (po_header_id or po_release_id)
320 --p_document_type
321 -- The type of the document
322 -- PO : Standard PO
323 -- RELEASE : Release
324 --Testing:
325 --
326 --End of Comments
327 -------------------------------------------------------------------------------
328 PROCEDURE Reset_Retro_Update(p_document_id IN NUMBER,
329 p_document_type IN VARCHAR2);
330
331 --------------------------------------------------------------------------------
332 --Start of Comments
333 --Name: Retro_Invoice_Release
334 --Pre-reqs:
335 -- None.
336 --Modifies:
337 -- PO_DISTRIBUTIONS_ALL.invoice_adjustment_flag.
338 --Locks:
339 -- None.
340 --Function:
341 -- This procedure updates invoice adjustment flag, and calls Costing
342 -- and Inventory APIs.
343 --Parameters:
344 --IN:
345 --p_api_version
346 -- Version number of API that caller expects. It
347 -- should match the l_api_version defined in the
348 -- procedure (expected value : 1.0)
349 --p_document_id
350 -- The id of the document (po_header_id or po_release_id)
351 --p_document_type
352 -- The type of the document
353 -- PO : Standard PO
354 -- RELEASE : Release
355 --OUT:
356 --x_return_status
357 -- FND_API.G_RET_STS_SUCCESS if API succeeds
358 -- FND_API.G_RET_STS_ERROR if API fails
359 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
360 --x_msg_count
361 -- Number of Error messages
362 --x_msg_data
363 -- Contains error msg in case x_return_status returned
364 -- FND_API.G_RET_STS_ERROR or FND_API.G_RET_STS_UNEXP_ERROR
365 --Testing:
366 --
367 --End of Comments
368 -------------------------------------------------------------------------------
369 PROCEDURE Retro_Invoice_Release(p_api_version IN NUMBER,
370 p_document_id IN NUMBER,
371 p_document_type IN VARCHAR2,
372 x_return_status OUT NOCOPY VARCHAR2,
373 x_msg_count OUT NOCOPY NUMBER,
374 x_msg_data OUT NOCOPY VARCHAR2);
375
376 -- <FPJ Retroactive END>
377
378 --------------------------------------------------------------------------------
379 --Start of Comments :Bug 3231062
380 --Name: Is_Retro_Project_Allowed
381 --Pre-reqs:
382 -- None.
383 --Modifies:
384 -- None.
385 --Locks:
386 -- None.
387 --Function:
388 -- This function returns true if retroactive pricing update allow on line with
389 -- project reference.
390 --Note:
391 -- Removed after 11iX
392 --Parameters:
393 --IN:
394 --p_std_po_price_change
395 --p_po_line_id
396 --p_po_line_loc_id
397 --RETURN:
398 -- 'Y': Retroactive pricing update is allowed
399 -- 'N': Retroactive pricing update is not allowed
400 --Testing:
401 --
402 --End of Comments
403 -------------------------------------------------------------------------------
404 FUNCTION Is_Retro_Project_Allowed(p_std_po_price_change IN VARCHAR2,
405 p_po_line_id IN NUMBER,
406 p_po_line_loc_id IN NUMBER
407 )
408 RETURN VARCHAR2;
409
410 --------------------------------------------------------------------------------
411 --Start of Comments :Bug 3339149
412 --Name: Is_Adjustment_Account_Valid
413 --Pre-reqs:
414 -- None.
415 --Modifies:
416 -- None.
417 --Locks:
418 -- None.
419 --Function:
420 -- This function returns true if the adjustment account exists and is valid
421 --Parameters:
422 --IN:
423 --p_std_po_price_change
424 --p_po_line_id
425 --p_ship_to_organization_id
426 --RETURN:
427 -- 'Y': Adjustment account is valid
428 -- 'N': Adjustment Account does not exist or is not valid
429 --Testing:
430 --
431 --End of Comments
432 -------------------------------------------------------------------------------
433 FUNCTION Is_Adjustment_Account_Valid(p_std_po_price_change IN VARCHAR2,
434 p_po_line_id IN NUMBER,
435 p_po_line_loc_id IN NUMBER
436 )
437 RETURN VARCHAR2;
438
439 END PO_RETROACTIVE_PRICING_PVT;
[Home] [Help]