1 PACKAGE PO_CUSTOM_PRICE_PUB AUTHID CURRENT_USER AS
2 /* $Header: POXPCPRS.pls 120.9.12020000.2 2013/02/11 00:13:12 vegajula ship $ */
3 /*#
4 * Provide the ability to perform custom pricing on Oracle Purchasing
5 * documents directly through an API.
6 *
7 * @rep:scope public
8 * @rep:product PO
9 * @rep:displayname Purchase Order Custom Pricing APIs
10 */
11
12
13 /*#
14 * Provides the ability to customize the pricing date through an API.
15 *
16 * The API is called by the Oracle Requisition Pricing API and
17 * PO/Release Pricing API that would use the pricing date to fetch the
18 * price. Prices of products in catalogs may not be static and can
19 * vary with time due to various factors: supply/demand variations,
20 * buying volume, and component price variations.
21 *
22 * The variation of price of a product with time is actually captured
23 * in terms of price and effective periods in the form of price breaks
24 * on blanket purchase agreements and catalog quotations. Price list
25 * lines in Oracle Advanced Pricing can also have effective periods
26 * defined.
27 *
28 * Based on the supplier agreement, the price of the product being
29 * ordered or requested from the catalog may be determined based on the
30 * price that is effective on the pricing date. The pricing date can be
31 * the date that the order is placed, the date that the order is
32 * shipped, or the date that the material is expected to arrive. Rules
33 * around determining the pricing date widely vary from one business to
34 * another and even within a business from one commodity to another.
35 *
36 * Businesses can add PL/SQL code to the custom API that can
37 * accommodate their own special rules to determine the pricing date.
38 * The subsequent pricing call will return the unit price from the
39 * source blanket or quotation that is effective on the pricing date.
40 *
41 * @param p_api_version Null not allowed. Value should match the
42 * current version of the API (currently 1.0). Used by the API to
43 * determine compatibility of API and calling program.
44 * @rep:paraminfo {@rep:required}
45 *
46 * @param p_source_document_header_id Internal ID for source document
47 * header. (i.e. PO_LINES_ALL.po_header_id)
48 * @rep:paraminfo {@rep:required}
49 *
50 * @param p_source_document_line_id Internal ID for source document
51 * line. (i.e. PO_LINES_ALL.po_line_id)
52 * @rep:paraminfo {@rep:required}
53 *
54 * @param p_order_line_id Internal ID for an order line. (i.e.
55 * PO_HEADERS_ALL.po_header_id)
56 * @rep:paraminfo {@rep:required}
57 *
58 * @param p_quantity Quantity
59 * @rep:paraminfo {@rep:required}
60 *
61 * @param p_ship_to_location_id Ship to location ID
62 * @rep:paraminfo {@rep:required}
63 *
64 * @param p_ship_to_organization_id Ship to organization ID
65 * @rep:paraminfo {@rep:required}
66 *
67 * @param p_need_by_date Need by date
68 * @rep:paraminfo {@rep:required}
69 *
70 * @param x_pricing_date New custom pricing date
71 * @rep:paraminfo {@rep:required}
72 *
73 * @param x_return_status Possible Values are:
74 * S = Success - Completed without errors.
75 * E = Error - Parameters are in error.
76 * U = Unexpected error.
77 * Bug5598011 @param p_order_type Adding new parameter p_order_type which will
78 * indicate if the order document is PO or REQUISITION
79
80 * @rep:displayname Get custom price date
81 *
82 * @rep:category BUSINESS_ENTITY PO_STANDARD_PURCHASE_ORDER
83 * @rep:category BUSINESS_ENTITY PO_BLANKET_RELEASE
84 * @rep:category BUSINESS_ENTITY PO_PURCHASE_REQUISITION
85 * @rep:category BUSINESS_ENTITY PO_INTERNAL_REQUISITION
86 */
87 PROCEDURE GET_CUSTOM_PRICE_DATE(p_api_version IN NUMBER,
88 p_source_document_header_id IN NUMBER, -- <FPJ Advanced Price>
89 p_source_document_line_id IN NUMBER,
90 p_order_line_id IN NUMBER, -- <Bug 3754828>
91 p_quantity IN NUMBER,
92 p_ship_to_location_id IN NUMBER,
93 p_ship_to_organization_id IN NUMBER,
94 p_need_by_date IN DATE,
95 x_pricing_date OUT NOCOPY DATE,
96 x_return_status OUT NOCOPY VARCHAR2,
97 p_order_type IN VARCHAR2 DEFAULT NULL); -- <Bug5598011>
98
99
100 /*#
101 * Provides the ability to customize the requisition price through an
102 * API.
103 *
104 * This API is called by the Oracle Requisition Pricing API that could
105 * return a different price than what the Oracle Requisition Pricing
106 * API has returned.
107 *
108 * Often times the price maintained on supplier catalogs are purely
109 * list prices and does not factor in adjustments such as discounts or
110 * surcharges as negotiated between a specific buyer and supplier.
111 *
112 * The Custom Requisition Pricing API enables you to add PL/SQL code to
113 * a new custom price adjustment hook in the standard requisition
114 * pricing API to adjust the price as determined from a source document
115 * (blanket agreement or quotation). This provides a mechanism to
116 * factor in negotiated discounts, surcharges, or other adjustments on
117 * top of the catalog prices.
118 *
119 * @param p_api_version Null not allowed. Value should match the
120 * current version of the API (currently 1.0). Used by the API to
121 * determine compatibility of API and calling program.
122 * @rep:paraminfo {@rep:required}
123 *
124 * @param p_source_document_header_id Internal ID for source document
125 * header. (i.e. PO_LINES_ALL.po_header_id)
126 * @rep:paraminfo {@rep:required}
127 *
128 * @param p_source_document_line_num Line number for source document
129 * line. (i.e. PO_LINES_ALL.po_line_id)
130 * @rep:paraminfo {@rep:required}
131 *
132 * @param p_order_line_id Internal ID for an order line. (i.e.
133 * PO_HEADERS_ALL.po_header_id)
134 * @rep:paraminfo {@rep:required}
135 *
136 * @param p_quantity Quantity
137 * @rep:paraminfo {@rep:required}
138 *
139 * @param p_unit_of_measure Unit of Measure
140 * @rep:paraminfo {@rep:required}
141 *
142 * @param p_deliver_to_location_id Deliver to location ID
143 * @rep:paraminfo {@rep:required}
144 *
145 * @param p_required_currency Required currency
146 * @rep:paraminfo {@rep:required}
147 *
148 * @param p_required_rate_type Required rate type
149 * @rep:paraminfo {@rep:required}
150 *
151 * @param p_need_by_date Need by date
152 * @rep:paraminfo {@rep:required}
153 *
154 * @param p_pricing_date Pricing date
155 * @rep:paraminfo {@rep:required}
156 *
157 * @param p_destination_org_id Destination organization ID
158 * @rep:paraminfo {@rep:required}
159 *
160 * @param p_currency_price Currency price
161 * @rep:paraminfo {@rep:required}
162 *
163 * @param x_new_currency_price New customized price
164 * @rep:paraminfo {@rep:required}
165 *
166 * @param x_return_status Possible Values are:
167 * S = Success - Completed without errors.
168 * E = Error - Parameters are in error.
169 * U = Unexpected error.
170 * @rep:paraminfo {@rep:required}
171 *
172 * @rep:displayname Get custom requisition price
173 *
174 * @rep:category BUSINESS_ENTITY PO_PURCHASE_REQUISITION
175 * @rep:category BUSINESS_ENTITY PO_INTERNAL_REQUISITION
176 */
177 PROCEDURE GET_CUSTOM_REQ_PRICE(p_api_version IN NUMBER,
178 p_source_document_header_id IN NUMBER,
179 p_source_document_line_num IN NUMBER,
180 p_order_line_id IN NUMBER, -- <Bug 3754828>
181 p_quantity IN NUMBER,
182 p_unit_of_measure IN VARCHAR2,
183 p_deliver_to_location_id IN NUMBER,
184 p_required_currency IN VARCHAR2,
185 p_required_rate_type IN VARCHAR2,
186 p_need_by_date IN DATE,
187 p_pricing_date IN DATE,
188 p_destination_org_id IN NUMBER,
189 p_currency_price IN NUMBER,
190 x_new_currency_price OUT NOCOPY NUMBER,
191 x_return_status OUT NOCOPY VARCHAR2);
192
193 /*#
194 * Provides the ability to customize PO/Release price through an API.
195 *
196 * The API is called by the Oracle PO/Release Pricing API that could
197 * return a different price than what the Oracle PO/Release Pricing API
198 * has returned.
199 *
200 * The Custom PO/Release Pricing API enables you to add PL/SQL code to
201 * a custom price adjustment hook in the standard PO/Release Pricing
202 * API that can adjust the unit price as determined from a source
203 * document (blanket agreement or quotation). This provides a mechanism
204 * to factor in negotiated discounts, surcharges, or other adjustments
205 * on top of the catalog prices.
206 *
207 * @param p_api_version Null not allowed. Value should match the
208 * current version of the API (currently 1.0). Used by the API to
209 * determine compatibility of API and calling program.
210 * @rep:paraminfo {@rep:required}
211 *
212 * @param p_order_quantity Order quantity
213 * @rep:paraminfo {@rep:required}
214 *
215 * @param p_ship_to_org Ship to organization
216 * @rep:paraminfo {@rep:required}
217 *
218 * @param p_ship_to_loc Ship to location
219 * @rep:paraminfo {@rep:required}
220 *
221 * @param p_po_line_id Internal ID for PO Line. (i.e.
222 * PO_LINES_ALL.po_line_id)
223 * @rep:paraminfo {@rep:required}
224 *
225 * @param p_cum_flag Cumulative flag
226 * @rep:paraminfo {@rep:required}
227 *
228 * @param p_need_by_date Need by date
229 * @rep:paraminfo {@rep:required}
230 *
231 * @param p_pricing_date Pricing date
232 * @rep:paraminfo {@rep:required}
233 *
234 * @param p_line_location_id Internal ID for PO Shipment. (i.e.
235 * PO_LINE_LOCATIONS_ALL.line_location_id)
236 * @rep:paraminfo {@rep:required}
237 *
238 * @param p_price Calculated price
239 * @rep:paraminfo {@rep:required}
240 *
241 * @param x_new_price New customized price
242 * @rep:paraminfo {@rep:required}
243 *
244 * @param p_order_line_id
245 * The line id of the order document.
246 * @rep:paraminfo {@rep:required}
247 *
248 * @param x_return_status Possible Values are:
249 * S = Success - Completed without errors.
250 * E = Error - Parameters are in error.
251 * U = Unexpected error.
252 * @rep:paraminfo {@rep:required}
253 *
254 * @rep:displayname Get custom purchase order price
255 *
256 * @rep:category BUSINESS_ENTITY PO_STANDARD_PURCHASE_ORDER
257 * @rep:category BUSINESS_ENTITY PO_BLANKET_RELEASE
258 */
259 PROCEDURE GET_CUSTOM_PO_PRICE(p_api_version IN NUMBER,
260 p_order_quantity IN NUMBER,
261 p_ship_to_org IN NUMBER,
262 p_ship_to_loc IN NUMBER,
263 p_po_line_id IN NUMBER,
264 p_cum_flag IN BOOLEAN,
265 p_need_by_date IN DATE,
266 p_pricing_date IN DATE,
267 p_line_location_id IN NUMBER,
268 p_price IN NUMBER,
269 x_new_price OUT NOCOPY NUMBER,
270 x_return_status OUT NOCOPY VARCHAR2,
271 p_req_line_price IN NUMBER DEFAULT NULL, --< Bug 7154646 >
272 p_order_line_id IN NUMBER DEFAULT NULL); -- <Bug 15871591>
273
274 -- <Bug 3794940 START>
275 /* Bug 7154646 Adding the parameter p_req_line_price to retain the Req Line Price, if Required. */
276 /*#
277 * Provides the ability to audit advanced pricing adjustments during pricing
278 * API call
279 *
280 * The API is called by the Oracle PO/Release Pricing API after invoking
281 * advanced pricing engine.
282 *
283 * The information about pricing adjustments is stored in temporary view
284 * QP_LDETS_V. To get the records, user should query this view for the records
285 * that satisfy the following:
286 * qp_ldets_v.line_index = p_line_index AND
287 * qp_ldets_v.automatic_flag = 'Y'
288 *
289 * @param p_api_version Null not allowed. Value should match the
290 * current version of the API (currently 1.0). Used by the API to
291 * determine compatibility of API and calling program.
292 * @rep:paraminfo {@rep:required}
293 *
294 * @param p_order_type Type of the document ('PO' or 'REQUISITION')
295 * @rep:paraminfo {@rep:required}
296 *
297 * @param p_order_line_id Line ID of the document (po line or requisition line)
298 * @rep:paraminfo {@rep:required}
299 *
300 * @param p_line_index The index of pricing adjustments stored in
301 * temporary view QP_LDETS_V.
302 * @rep:paraminfo {@rep:required}
303 *
304 * @param x_return_status Return status of the API. Possible Values are:
305 * S = Success - Completed without errors.
306 * E = Error - Parameters are in error.
307 * U = Unexpected error.
308 * @rep:paraminfo {@rep:required}
309 *
310 * @param x_msg_count Number of messages added to FND_MSG_PUB message stack, if
311 * used
312 * @rep:paraminfo {@rep:required}
313 *
314 * @param x_msg_data If x_msg_count = 1, this output parameter should return
315 * the text of the message
316 * @rep:paraminfo {@rep:required}
317 *
318 * @rep:displayname Audit advanced pricing adjustments
319 *
320 * @rep:category BUSINESS_ENTITY PO_PURCHASE_REQUISITION
321 * @rep:category BUSINESS_ENTITY PO_STANDARD_PURCHASE_ORDER
322 */
323 PROCEDURE audit_qp_price_adjustment(p_api_version IN NUMBER,
324 p_order_type IN VARCHAR2,
325 p_order_line_id IN NUMBER,
326 p_line_index IN NUMBER,
327 x_return_status OUT NOCOPY VARCHAR2,
328 x_msg_count OUT NOCOPY NUMBER,
329 x_msg_data OUT NOCOPY VARCHAR2);
330 -- <Bug 3794940 END>
331 -- < Bug 7430760 START>
332 /*#
333 * This procedure provides for fetching custom unit price for an inventory item
334 * on an internal requisition based on the item, source and destination inventories
335 * and unit of measure. The parameters are
336
337 * IN PARAMETERS
338 *
339 * @param p_api_version p_api_version : Null not allowed. Value should match the
340 * current version of the API (currently 1.0). Used by the API to
341 * determine compatibility of API and calling program.
342 * @rep:paraminfo {@rep:required}
343 *
344 * @param p_item_id : inventory item id of the item for which custom price is fetched
345 * @rep:paraminfo {@rep:required}
346 *
347 * @param p_category_id : category id of the item for which custom price is fetched
348 * @rep:paraminfo {@rep:required}
349 *
350 * @param p_req_header_id : Header id of the requisition
351 * @rep:paraminfo {@rep:required}
352 *
353 * @param p_req_line_id : Line of the requisition line
354 * @rep:paraminfo {@rep:required}
355 *
356 * @param p_src_organization_id : source inventory organization of the requisition line
357 * @rep:paraminfo {@rep:required}
358 *
359 * @param p_src_sub_inventory : Source sub inventory
363 * @rep:paraminfo {@rep:required}
360 * @rep:paraminfo {@rep:required}
361 *
362 * @param p_dest_organization_id : destination inventory organization of the requisition line
364 *
365 * @param p_dest_sub_inventory : destination sub inventory
366 * @rep:paraminfo {@rep:required}
367 *
368 * @param p_deliver_to_location_id : deliver to location in the destination organization
369 * @rep:paraminfo {@rep:required}
370 *
371 * @param p_need_by_date : need by date
372 * @rep:paraminfo {@rep:required}
373 *
374 * @param p_unit_of_measure : unit of measure of the requisition line
375 * @rep:paraminfo {@rep:required}
376 *
377 * @param p_quantity : quantity
378 * @rep:paraminfo {@rep:required}
379 *
380 * @param p_currency_code : currency code of the source organization
381 * @rep:paraminfo {@rep:required}
382 *
383 * @param p_rate : Rate of conversion of p_currency_code to fsp currency code
384 * @rep:paraminfo {@rep:required}
385 *
386 * @param p_rate_type : type of conversion
387 * @rep:paraminfo {@rep:required}
388 *
389 * @param p_rate_date : date of conversion
390 * @rep:paraminfo {@rep:required}
391 *
392 * OUT PARAMETERS
393 *
394 * @param x_return_status
395 * FND_API.G_RET_STS_SUCCESS if API succeeds
396 * FND_API.G_RET_STS_ERROR if API fails
397 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
398 * @rep:paraminfo {@rep:required}
399 *
400 * IN OUT PARAMETERS
401 *
402 * @param x_unit_price
403 * custom price if the custom code is in place
404 * system price if the custom code is not in place
405 * @rep:paraminfo {@rep:required}
406 *
407 * @rep:displayname Get custom Internal Requisition Price
408 *
409 * @rep:category BUSINESS_ENTITY PO_INTERNAL_REQUISITION
410 *
411 */
412
413 PROCEDURE GET_CUST_INTERNAL_REQ_PRICE(p_api_version IN NUMBER,
414 p_item_id IN NUMBER DEFAULT NULL,
415 p_category_id IN NUMBER DEFAULT NULL,
416 p_req_header_id IN NUMBER DEFAULT NULL,
417 p_req_line_id IN NUMBER DEFAULT NULL,
418 p_src_organization_id IN NUMBER DEFAULT NULL,
419 p_src_sub_inventory IN VARCHAR2 DEFAULT NULL,
420 p_dest_organization_id IN NUMBER DEFAULT NULL,
421 p_dest_sub_inventory IN VARCHAR2 DEFAULT NULL,
422 p_deliver_to_location_id IN NUMBER DEFAULT NULL,
423 p_need_by_date IN DATE DEFAULT NULL,
424 p_unit_of_measure IN VARCHAR2 DEFAULT NULL,
425 p_quantity IN NUMBER DEFAULT NULL,
426 p_currency_code IN VARCHAR2 DEFAULT NULL,
427 p_rate IN NUMBER DEFAULT NULL,
428 p_rate_type IN VARCHAR2 DEFAULT NULL,
429 p_rate_date IN DATE DEFAULT NULL,
430 x_return_status OUT NOCOPY VARCHAR2,
431 x_unit_price IN OUT NOCOPY NUMBER
432 );
433
434 -- < Bug 7430760 END>
435 --------------------------------------------------------------------------------
436 --Start of Comments
437 --Name: GET_CUSTOM_INTERNAL_REQ_PRICE
438 --Pre-reqs:
439 -- None.
440 --Modifies:
441 -- None.
442 --Locks:
443 -- None.
444 --Function:
445 -- This function call the procedure GET_CUST_INTERNAL_REQ_PRICE to and returns
446 -- the unit price.This function is called from req import to fetch the unit price.
447 -- This function is used only for internal coding purpose.
448 -- DO NOT CUSTOMIZE THIS FUNCTION.
449 -- CUSTOMIZE THE PROCEDURE GET_CUST_INTERNAL_REQ_PRICE
450 --Parameters:
451 -- IN PARAMETERS
452 --
453 -- p_item_id : inventory item id of the item for which custom price is fetched
454 --
455 -- p_category_id : category id of the category for which custom price is fetched
456 --
457 -- p_req_header_id : Header id of the requisition
458 --
459 -- p_req_line_id : Line of the requisition line
460 --
461 -- p_src_organization_id : source inventory organization of the requisition line
462 --
463 -- p_src_sub_inventory : Source sub inventory
464 --
465 -- p_dest_organization_id : destination inventory organization of the requisition line
466 --
467 -- p_dest_sub_inventory : destination sub inventory
468 --
469 -- p_deliver_to_location_id : deliver to location in the destination organization
470 --
471 -- p_need_by_date : need by date
472 --
473 -- p_unit_of_measure : unit of measure of the requisition line
474 --
475 -- p_quantity : quantity
476 --
477 -- p_currency_code : currency code of the source organization
478 --
479 -- p_rate : Rate of conversion of p_currency_code to fsp currency code
480 --
481 -- p_rate_type : type of conversion
482 --
483 -- p_rate_date : date of conversion
484 --
485 -- p_unit_price : unit price fetched from inventory. This price is returned
486 -- if there is no custom code.
487 --
488 -- OUT PARAMETERS
489
490 -- x_unit_price
491 -- custom price if the custom code is in place
492 -- system price if the custom code is not in place
493 --End of Comments
494 -------------------------------------------------------------------------------
495
496
497
498 FUNCTION GET_CUSTOM_INTERNAL_REQ_PRICE(p_api_version IN NUMBER,
499 p_item_id IN NUMBER DEFAULT NULL,
500 p_category_id IN NUMBER DEFAULT NULL,
501 p_req_header_id IN NUMBER DEFAULT NULL,
502 p_req_line_id IN NUMBER DEFAULT NULL,
503 p_src_organization_id IN NUMBER DEFAULT NULL,
504 p_src_sub_inventory IN VARCHAR2 DEFAULT NULL,
505 p_dest_organization_id IN NUMBER DEFAULT NULL,
506 p_dest_sub_inventory IN VARCHAR2 DEFAULT NULL,
507 p_deliver_to_location_id IN NUMBER DEFAULT NULL,
508 p_need_by_date IN DATE DEFAULT NULL,
509 p_unit_of_measure IN VARCHAR2 DEFAULT NULL,
510 p_quantity IN NUMBER DEFAULT NULL,
511 p_currency_code IN VARCHAR2 DEFAULT NULL,
512 p_rate IN NUMBER DEFAULT NULL,
513 p_rate_type IN VARCHAR2 DEFAULT NULL,
514 p_rate_date IN DATE DEFAULT NULL,
515 p_unit_price IN NUMBER DEFAULT NULL
516 ) RETURN NUMBER;
517
518
519 END PO_CUSTOM_PRICE_PUB; -- Package spec
[Home] [Help]