1 PACKAGE QP_PRC_UTIL AUTHID CURRENT_USER AS
2 /* $Header: QPXUPRCS.pls 120.1 2005/06/12 23:08:45 appldev $ */
3
4 -- Start of Comments
5 -- Package name: QP_PRC_UTIL
6 --
7 -- Function: Package contains all pricing utilities including:
8 --
9 -- Procedure Calculate_Selling_Price -
10 -- Calculates the selling price or selling percent for an
11 -- item, applying all adjustments.
12 --
13 -- Procedure Price_Item -
14 -- Fetches the best automatic adjustment for an item, sums
15 -- up all the adjustments (manual and automatic, header and
16 -- line) applied to the item, an finally calculates the
17 -- selling price based on the pricing method.
18 --
19 -- End Of Comments
20
21
22 -- Global constants representing pricing method codes
23
24 G_PRC_METHOD_AMOUNT CONSTANT VARCHAR2(10) := 'AMT';
25 G_PRC_METHOD_PERCENT CONSTANT VARCHAR2(10) := '%';
26
27
28 -- Global constants for actions
29
30 G_PRC_ACT_PRICE CONSTANT VARCHAR2(10) := 'PRICE';
31 G_PRC_ACT_CALCULATE CONSTANT VARCHAR2(10) := 'CALCULATE';
32 G_PRC_ACT_APPLY_CHG CONSTANT VARCHAR2(10) := 'APPLY_CHG';
33 G_PRC_ACT_ADJUSTMENT CONSTANT VARCHAR2(10) := 'ADJUSTMENT';
34 G_PRC_ACT_LINES CONSTANT VARCHAR2(10) := 'LINES';
35
36
37 -- Global constants for operations
38
39 G_PRC_OPR_CREATE CONSTANT VARCHAR2(10) := 'CREATE';
40 G_PRC_OPR_DELETE CONSTANT VARCHAR2(10) := 'DELETE';
41 G_PRC_OPR_UPDATE CONSTANT VARCHAR2(10) := 'UPDATE';
42 G_PRC_OPR_LOCK CONSTANT VARCHAR2(10) := 'LOCK';
43
44 -- Global constant defining Item type code 'SERVICE'.
45
46 G_PRC_ITEM_SERVICE VARCHAR2(30) := 'SERVICE';
47
48 -- Global constants for data from oe_entities
49
50 G_ATTR_UNIT NUMBER := 10001;
51 G_ATTR_QUANTITY NUMBER := 10002;
52 G_ATTR_LIST_PRICE NUMBER := 10003;
53 G_ATTR_CUSTOMER NUMBER := 1000;
54 G_ATTR_ITEM NUMBER := 1001;
55 G_ATTR_PO_NUMBER NUMBER := 1004;
56 G_ATTR_AGREEMENT_TYPE NUMBER := 1005;
57 G_ATTR_AGREEMENT NUMBER := 1006;
58 G_ATTR_ORDER_TYPE_ID NUMBER := 1007;
59 G_ATTR_INVOICE_TO NUMBER := 1008;
60 G_ATTR_SHIP_TO NUMBER := 1009;
61 G_ATTR_PRC_ATTRIBUTE1 NUMBER := 1010;
62 G_ATTR_PRC_ATTRIBUTE2 NUMBER := 1011;
63 G_ATTR_PRC_ATTRIBUTE3 NUMBER := 1012;
64 G_ATTR_PRC_ATTRIBUTE4 NUMBER := 1013;
65 G_ATTR_PRC_ATTRIBUTE5 NUMBER := 1014;
66 G_ATTR_PRC_ATTRIBUTE6 NUMBER := 1015;
67 G_ATTR_PRC_ATTRIBUTE7 NUMBER := 1016;
68 G_ATTR_PRC_ATTRIBUTE8 NUMBER := 1017;
69 G_ATTR_PRC_ATTRIBUTE9 NUMBER := 1018;
70 G_ATTR_PRC_ATTRIBUTE10 NUMBER := 1019;
71 G_ATTR_PRC_ATTRIBUTE11 NUMBER := 1040;
72 G_ATTR_PRC_ATTRIBUTE12 NUMBER := 1041;
73 G_ATTR_PRC_ATTRIBUTE13 NUMBER := 1042;
74 G_ATTR_PRC_ATTRIBUTE14 NUMBER := 1043;
75 G_ATTR_PRC_ATTRIBUTE15 NUMBER := 1044;
76 G_ATTR_ITEM_CATEGORY NUMBER := 1045;
77 G_ATTR_CUSTOMER_CLASS NUMBER := 100000;
78
79 -- Type Prc_Item_Rec_Type record
80 -- Usage:
81 -- Description:
82 -- The Prc_Item_Rec_Type record holds the item/price_list information. It
83 -- also holds the discounting attributes affecting the fetch of the
84 -- best discounts.
85 -- Parameters:
86 -- inventory_item_id NUMBER optional
87 -- price_list_id NUMBER required
88 -- If price_list_id is missing, price_item procedure
89 -- returns with all OUT parameters set to NULL
90 -- unit_code VARCHAR2(3) optional
91 -- Discounting attribute from oe_order_lines
92 -- pricing_df_rec VARCHAR2(150) optional
93 -- Discounting attribute from oe_order_lines
94 -- item_category_id NUMBER optional
95 -- Discounting attribute from MTL_ITEM_CATEGORIES
96 -- quantity NUMBER optional
97 -- Discounting attribute from oe_order_lines
98 -- ship_to_site_use_id NUMBER optional
99 -- Discounting attribute from oe_order_lines
100 -- list_price NUMBER required
101 -- If list_price is missing, price_item procedure
102 -- returns with all OUT variables set to NULL
103 -- list_percent NUMBER optional
104 -- Used when price_method_code = PERC (percent)
105 -- base_price NUMBER optional
106 -- Used when price_method_code = PERC. Comes from
107 -- the parent service line list_price in oe_order_lines
108 -- service_duration NUMBER optional
109 -- Used when price_method_code = PERC
110 -- item_type_code VARCHAR2(30) optional
111 -- Used when price_method_code = PERC to check if
112 -- the item is service or not
113 -- price_method_code VARCHAR2(4) required
114 -- price_item procedure supports two types of pricing:
115 -- G_PRC_METHOD_AMOUNT - an amount
116 -- G_PRC_METHOD_PERCENT - a percent
117 -- Value for price_method_code comes from oe_order_lines or
118 -- from fetch_list_price. If price_method_code is
119 -- missing, price_item returns with all out
120 -- parameters set to NULL
121 -- customer_id NUMBER optional
122 -- Discounting attribute from oe_order_headers
123 -- customer_class_code VARCHAR2(30) optional
124 -- Discounting attribute from hz_cust_accounts
125 -- invoice_to_site_use_id NUMBER optional
126 -- Discounting attribute from oe_order_headers
127 -- po_number NUMBER optional
128 -- Discounting attribute from oe_order_headers
129 -- agreement_id NUMBER optional
130 -- Discounting attribute from oe_order_headers
131 -- agreement_type_code VARCHAR(30) optional
132 -- order_type_id NUMBER optional
133 -- Discounting attribute from oe_order_headers
134 -- gsa VARCHAR2(1) optional
135 -- Discounting attribute from oe_order_headers. If value
136 -- is not passed for gsa, the price_item procedure
137 -- derives it from the customer or invoice
138 -- Notes:
139
140 TYPE Prc_Item_Rec_Type is RECORD
141 ( inventory_item_id NUMBER := NULL ,
142 price_list_id NUMBER := NULL ,
143 unit_code VARCHAR2(3) := NULL ,
144 pricing_attribute1 VARCHAR2(150) := NULL ,
145 pricing_attribute2 VARCHAR2(150) := NULL ,
146 pricing_attribute3 VARCHAR2(150) := NULL ,
147 pricing_attribute4 VARCHAR2(150) := NULL ,
148 pricing_attribute5 VARCHAR2(150) := NULL ,
149 pricing_attribute6 VARCHAR2(150) := NULL ,
150 pricing_attribute7 VARCHAR2(150) := NULL ,
151 pricing_attribute8 VARCHAR2(150) := NULL ,
152 pricing_attribute9 VARCHAR2(150) := NULL ,
153 pricing_attribute10 VARCHAR2(150) := NULL ,
154 pricing_attribute11 VARCHAR2(150) := NULL ,
155 pricing_attribute12 VARCHAR2(150) := NULL ,
156 pricing_attribute13 VARCHAR2(150) := NULL ,
157 pricing_attribute14 VARCHAR2(150) := NULL ,
158 pricing_attribute15 VARCHAR2(150) := NULL ,
159 pricing_date DATE := NULL ,
160 item_category_id NUMBER := NULL ,
161 quantity NUMBER := NULL ,
162 ship_to_site_use_id NUMBER := NULL ,
163 list_price NUMBER := NULL ,
164 list_percent NUMBER := NULL ,
165 base_price NUMBER := NULL ,
166 service_duration NUMBER := NULL ,
167 item_type_code VARCHAR2(30) := NULL ,
168 price_method_code VARCHAR2(4) := NULL ,
169 sold_to_org_id NUMBER := NULL ,
170 customer_class_code VARCHAR2(30) := NULL ,
171 /* invoice_to_site_use_id NUMBER := NULL , */
172 invoice_to_org_id NUMBER := NULL ,
173 po_number VARCHAR2(50) := NULL ,
174 agreement_id NUMBER := NULL ,
175 agreement_type_code VARCHAR2(30) := NULL ,
176 order_type_id NUMBER := NULL ,
177 gsa VARCHAR2(1) := NULL
178 );
179
180
181
182 -- Type Adj_Short_Rec_Type record:
183 -- Usage:
184 -- Description:
185 -- The Adj_Short_Rec_Type record holds one fetched adjustment.
186 -- Parameters:
187 -- adjustment_id NUMBER := NULL
188 -- adjustment_id is not fetched by this function.
189 -- It is up to the calling function to get an
190 -- adjustment_id and insert the adjustment
191 -- into OE_PRICE_ADJUSTMENTS
192 -- discount_id NUMBER
193 -- discount_line_id NUMBER
194 -- automatic_flag VARCHAR2(1)
195 -- percent NUMBER
196 -- discount_id, discount_line_id, automatic_flag,
197 -- and percent all returned from oe_price_adjustments
198 -- line_id NUMBER
199 -- header_id NUMBER
200 -- line_tbl_index NUMBER
201 -- line_id, header_id and line_tbl_index are not
202 -- used by this function
203 -- discount_name VARCHAR2(30)
204 -- Name of the discount from oe_discounts
205 -- operation VARCHAR2(10)
206 -- line_tbl_index NUMBER := NULL
207 -- Not currently used
208 -- Notes:
209
210 TYPE Adj_Short_Rec_Type is RECORD
211 ( adjustment_id NUMBER := NULL ,
212 discount_id NUMBER := NULL ,
213 discount_line_id NUMBER := NULL ,
214 automatic_flag VARCHAR2(1) := NULL ,
215 percent NUMBER := NULL ,
216 line_id NUMBER := NULL ,
217 header_id NUMBER := NULL ,
218 discount_name VARCHAR2(30) := NULL ,
219 pricing_date DATE := NULL ,
220 operation VARCHAR2(10) := NULL ,
221 line_tbl_index NUMBER := NULL
222 );
223
224
225
226 -- Type Adj_Short_Tbl_Type table:
227 -- Usage:
228 -- Description:
229 -- The Adj_Short_Tbl_Type table holds fetched adjustments.
230 -- Currently the Price_Item procedure fetches only one automatic adjustment.
231 -- We use the Adj_Short_Tbl_Type table as an OUT parameter to allow for
232 -- future enhancements that may result in the procedure returning more than
233 -- one adjustment. This type is also used in the Price_line API
234 -- to hold a table of line level adjustments.
235 -- Parameters: None
236 -- Notes:
237
238 TYPE Adj_Short_Tbl_Type is TABLE OF Adj_Short_Rec_Type
239 INDEX BY BINARY_INTEGER;
240
241 -- Global variable representing the missing Adj_Short_Tbl
242
243 G_MISS_ADJ_SHORT_TBL Adj_Short_Tbl_Type;
244
245 -- FUNCTION EQUAL
246
247 FUNCTION Equal
248 ( p_attr1 IN NUMBER ,
249 p_attr2 IN NUMBER
250 ) RETURN BOOLEAN;
251
252 FUNCTION Equal
253 ( p_attr1 IN VARCHAR2,
254 p_attr2 IN VARCHAR2
255 ) RETURN BOOLEAN;
256
257 FUNCTION Equal
258 ( p_attr1 IN DATE ,
259 p_attr2 IN DATE
260 ) RETURN BOOLEAN;
261
262
263 -- Function Get_Hdr_Adj_Total
264 -- Usage:
265 -- This function is called from the Price_Line and Price_Order APIs
266 -- Description:
267 -- Get_Hdr_Adj_Total returns the total of all header level adjustments
268 -- from the G_hdr_adj_tbl global table for the specified header header_id
269 -- Parameters:
270 -- IN:
271 -- p_header_id IN NUMBER required
272 -- RETURNS: NUMBER
273 -- Notes:
274
275 FUNCTION Get_Hdr_Adj_Total
276 ( p_header_id IN NUMBER := NULL) RETURN NUMBER;
277
278
279
280 -- Procedure Query_Adjustments
281 -- Usage:
282 -- This procedure is called from Price_Line.
283 -- Description:
284 -- Queries line and header level adjustments.
285 -- The header level adjustments will be stored in
286 -- G_hdr_adj_tbl, while line level adjustments will be
287 -- returned in l_adj_tbl.
288 -- Keep in mind that if G_hdr_adj_tbl has already been
289 -- queried then no extra queries will be performed. This is
290 -- intentional to allow the Price_Header portion of the
291 -- Price_Order API to manipulate header level adjustment and
292 -- have this manipulation reflect on lines selling prices.
293
294 PROCEDURE Query_Adjustments
295 ( p_header_id IN NUMBER := NULL ,
296 p_line_id IN NUMBER := NULL ,
297 p_adj_tbl OUT NOCOPY /* file.sql.39 change */ QP_PRC_UTIL.Adj_Short_Tbl_Type
298 );
299
300 -- FUNCTION Get_Agr_Type : Queries the agreement type code from
301 -- OE_AGREEMENTS.
302
303 FUNCTION Get_Agr_Type
304 ( p_agreement_id IN NUMBER := NULL
305 ) RETURN VARCHAR2;
306
307 -- Procedure Calculate_Selling_Price
308 -- Usage:
309 -- Calculate_Selling_Price is called from the Price_Item procedure
310 -- after Price_Item has fetched the best available automatic
311 -- adjustment and calculated the total adjustment total.
312 -- Calculate_Selling_Price is also called from the Price_Line
313 -- procedure as a full call to Price_Item from the Price_Line API
314 -- is unnecessary in certain situations.
315 -- Description:
316 -- Calculates the selling price or selling percent for an
317 -- item, applying all adjustments.
318 -- Two pricing methods are supported: amount and percent.
319 -- Parameters:
320 -- IN:
321 -- p_adj_total NUMBER required
322 -- Total adjustment percent to apply 0-100
323 -- p_list_price NUMBER required
324 -- Used when price_method_code = AMNT (amount),
325 -- otherwise pass NULL
326 -- p_list_percent NUMBER required
327 -- Used when price_method_code = PERC (percent),
328 -- otherwise pass NULL
329 -- p_price_list_id NUMBER required
330 -- If p_price_list_id is NULL, Calculate_Selling_Price
331 -- returns with all OUT parameters set to NULL
332 -- p_base_price NUMBER required
333 -- Used when price_method_code = PERC. Comes from
334 -- the parent service line list_price in oe_order_lines
335 -- p_service_duration NUMBER required
336 -- Used when price_method_code = PERC,
337 -- otherwise pass NULL
338 -- p_price_method_code VARCHAR2(4) required
339 -- price_item procedure supports two types of pricing:
340 -- G_PRC_METHOD_AMOUNT - an amount
341 -- G_PRC_METHOD_PERCENT - a percent
342 -- Value for price_method_code comes from oe_order_lines or
343 -- from fetch_list_price. If price_method_code is
344 -- missing, price_item returns with all out
345 -- parameters set to NULL
346 --
347 -- OUT:
348 -- p_selling_price NUMBER
349 -- Final rounded selling price
350 -- p_selling_percent NUMBER
351 -- When price_method_code = PERC, this parameter
352 -- holds the selling percent
353 -- p_list_price_out NUMBER
354 -- When price_method_code = PERC, this parameter
355 -- holds the list price
356 --
357 -- Notes:
358
359 PROCEDURE Calculate_Selling_Price
360 ( p_adj_total IN NUMBER ,
361 p_list_price IN NUMBER ,
362 p_list_percent IN NUMBER ,
363 p_price_list_id IN NUMBER ,
364 p_base_price IN NUMBER ,
365 p_service_duration IN NUMBER ,
366 p_pricing_method_code IN VARCHAR2 ,
367 p_selling_price OUT NOCOPY /* file.sql.39 change */ NUMBER ,
368 p_selling_percent OUT NOCOPY /* file.sql.39 change */ NUMBER ,
369 p_list_price_out OUT NOCOPY /* file.sql.39 change */ NUMBER
370 );
371
372
373
374 -- Procedure Price_Item
375 -- Usage:
376 -- Price_Item is called from the Price_Line API.
377 -- Description:
378 -- Fetches the best automatic adjustment for an item, sums
379 -- up all the adjustments (manual and automatic, header and
380 -- line) applied to the item, an finally calculates the
381 -- selling price based on the pricing method.
382 -- Parameters:
383 -- IN:
384 -- p_item_rec Prc_Item_Rec_Type required
385 -- p_item_rec holds the item/price_list information.
386 -- It also holds the discounting attributes affecting
387 -- the fetch of the best discount.
388 -- p_existing_adj_total NUMBER optional
389 -- This parameter holds the total of all manual
390 -- line and header level adjustments. It will be
391 -- used when validating that by applying the new
392 -- adjustment, the total is not going to exceed 100,
393 -- and it will be added to the new adjustment (if any)
394 -- to compute the final adjustment total that will
395 -- be used to calculate the selling price
396 -- OUT:
397 -- p_return_status VARCHAR2(1)
398 -- p_selling_price NUMBER
399 -- Final rounded selling price
400 -- p_selling_percent NUMBER
401 -- When price_method_code = PERC, this parameter
402 -- holds the selling percent
404 -- PL/SQL table that holds the new fetched automatic
405 -- adjustment if found. Currently the Price_Item
406 -- procedure fetches only one automatic adjustment.
407 -- We use the Adj_Short_Tbl_Type table as an OUT
408 -- parameter to allow for future enhancements that
409 -- may result in the procedure returning more than
410 -- one adjustment.
411 -- Notes:
412
413 PROCEDURE Price_Item
414 ( p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
415 p_item_rec IN Prc_Item_Rec_Type ,
416 p_existing_adj_total IN NUMBER := 0 ,
417 p_selling_price OUT NOCOPY /* file.sql.39 change */ NUMBER ,
418 p_selling_percent OUT NOCOPY /* file.sql.39 change */ NUMBER ,
419 p_list_price_out OUT NOCOPY /* file.sql.39 change */ NUMBER ,
420 p_adj_out_table OUT NOCOPY /* file.sql.39 change */ Adj_Short_Tbl_Type
421 );
422
423 FUNCTION Get_item_Category
424 ( p_item_id IN NUMBER
425 ) RETURN NUMBER;
426
427 FUNCTION Get_Cust_Class
428 ( p_sold_to_org_id IN NUMBER := NULL
429 ) RETURN VARCHAR2;
430
431 -- Fix For Bug-1974413
432 -- Function Get_Attribute_Name
433 -- Description : This Function returns Attribute Name corresponding to the
434 -- Attribute Code Passed
435 -- Usage : This Function is called from FND_MESSAGE.SET_TOKEN
436 -- Parameters : IN p_attribute_code
437 -- RETURNS : VARCHAR2
438
439 FUNCTION Get_Attribute_Name
440 ( p_attribute_code IN VARCHAR2
441 ) RETURN VARCHAR2;
442
443
444 END QP_PRC_UTIL;
445
403 -- p_adj_out_tbl Adj_Short_Tbl_Type
[Home] [Help]