[Home] [Help]
PACKAGE: APPS.QP_PREQ_PUB
Source
1 PACKAGE QP_PREQ_PUB AUTHID CURRENT_USER AS
2 /* $Header: QPXPPRES.pls 120.5.12020000.1 2012/06/27 21:08:22 appldev ship $ */
3 /*#
4 * This package contains procedures to be called to pass the request information
5 * to the pricing engine.
6 *
7 * @rep:scope public
8 * @rep:product QP
9 * @rep:displayname Price Request
10 * @rep:category BUSINESS_ENTITY QP_PRICE_LIST
11 * @rep:category BUSINESS_ENTITY QP_PRICE_MODIFIER
12 * @rep:category BUSINESS_ENTITY QP_PRICE_QUALIFIER
13 * @rep:category BUSINESS_ENTITY QP_PRICE_FORMULA
14 */
15
16 --For Perforamnce fix bug 7309551 smbalara
17 G_DYNAMIC_SAMPLING_LEVEL CONSTANT VARCHAR2(30) := 'QP_DYNAMIC_SAMPLING_LEVEL';
18 G_ODS NUMBER;
19
20 --replacement constant for FND_API.G_MISS_NUM
21 G_MISS_NUM CONSTANT NUMBER := 9.99E125;
22
23 --FOR DIRECT TEMP TABLE INSERTION
24 G_PRICE_PHASE_FLAG BOOLEAN;
25 G_MIN_PRICING_DATE DATE;
26 G_MAX_PRICING_DATE DATE;
27 G_PRICE_FLAG_INDEX VARCHAR2(1) := 'N';
28 G_CURRENCY_CODE VARCHAR2(30);
29 G_PRICE_LIST_PHASE_ID PLS_INTEGER :=1;
30 G_PRICE_LIST_SEQUENCE PLS_INTEGER :=0;
31 G_GSA_INDICATOR VARCHAR2(1);
32 G_LICENSED_FOR_PRODUCT VARCHAR2(30);
33
34
35 --Processing status
36 G_STATUS_NEW CONSTANT VARCHAR2(30):='N';
37 G_STATUS_DELETED CONSTANT VARCHAR2(30):='D';
38 G_STATUS_UNCHANGED CONSTANT VARCHAR2(30):='X';
39 G_STATUS_TRANSIENT CONSTANT VARCHAR2(30):='T';
40 G_STATUS_GROUPING CONSTANT VARCHAR2(30):='G';
41 G_STATUS_UPDATED CONSTANT VARCHAR2(30):='UPDATED';
42 G_STATUS_INVALID_PRICE_LIST CONSTANT VARCHAR2(30):='IPL';
43 G_STATUS_GSA_VIOLATION CONSTANT VARCHAR2(30):='GSA';
44 G_STS_LHS_NOT_FOUND CONSTANT VARCHAR2(30):='NMS';
45 G_STATUS_FORMULA_ERROR CONSTANT VARCHAR2(30):='FER';
46 G_STATUS_OTHER_ERRORS CONSTANT VARCHAR2(30):='OER';
47 G_STATUS_SYSTEM_GENERATED CONSTANT VARCHAR2(30):='S';
48 G_STATUS_BEST_PRICE_EVAL CONSTANT VARCHAR2(30):= 'B';
49 G_STATUS_INCOMP_LOGIC CONSTANT VARCHAR2(30):= 'I';
50 G_STATUS_CALC_ERROR CONSTANT VARCHAR2(30):='CALC';
51 G_STATUS_UOM_FAILURE CONSTANT VARCHAR2(30):='UOM';
52 G_STATUS_PRIMARY_UOM_FLAG CONSTANT VARCHAR2(30):='P_UOM_FLAG';
53 G_STATUS_OTHER_ITEM_BENEFITS CONSTANT VARCHAR2(30):='OTHER_ITEM_BENEFITS';
54 G_STATUS_INVALID_UOM CONSTANT VARCHAR2(30) := 'INVALID_UOM';
55 G_STATUS_DUP_PRICE_LIST CONSTANT VARCHAR2(30) := 'DUPLICATE_PRICE_LIST
56 ';
57 G_STATUS_INVALID_UOM_CONV CONSTANT VARCHAR2(30) := 'INVALID_UOM_CONV';
58 G_STATUS_INVALID_INCOMP CONSTANT VARCHAR2(30) := 'INVALID_INCOMP';
59 G_STATUS_BEST_PRICE_EVAL_ERROR CONSTANT VARCHAR2(30) := 'INVALID_BEST_PRICE';
60
61 --Processed code
62 G_NO_LIST_PASSED CONSTANT VARCHAR2(30):='NLP';
63 G_STATUS_NOT_IN_MINI_SEARCH CONSTANT VARCHAR2(30) := 'NMS';
64 G_STATUS_MINI_SEARCH_NOT_EXEC CONSTANT VARCHAR2(30) := 'NMSE';
65 G_BY_ENGINE CONSTANT VARCHAR2(30) :='ENGINE';
66
67 --DELETED BY STATUS CODE
68 G_DELETED_PBH CONSTANT VARCHAR2(30):='D_PBH';
69 G_DELETED_GRP CONSTANT VARCHAR2(30):='D_GRP';
70 G_DELETED_EXCLUDER CONSTANT VARCHAR2(30):='D_EXCL';
71 G_DELETED_NULL_PRICE CONSTANT VARCHAR2(30):='D_NULL_PRICE';
72 G_DELETED_CAL_ERROR CONSTANT VARCHAR2(30):='D_CAL_ERROR';
73 G_DELETED_BETWEEN CONSTANT VARCHAR2(30):='D_BETWEEN';
74 G_DELETED_PARENT_FAILS CONSTANT VARCHAR2(30):='D_PBH_PARENT_FAILS';
75
76 --PROCESSED FLAG for internal used only
77 G_NOT_PROCESSED CONSTANT VARCHAR2(30):='N';
78 G_PROCESSED CONSTANT VARCHAR2(30):='Y';
79 G_BY_PBH CONSTANT VARCHAR2(30):='PBH';
80
81 --VALIDATED_CODE
82 G_NOT_VALIDATED CONSTANT VARCHAR2(30):='N';
83 G_VALIDATED CONSTANT VARCHAR2(30):='Y';
84
85 --APPLIED_FLAG
86 G_LIST_APPLIED CONSTANT VARCHAR2(30):='Y';
87 G_LIST_NOT_APPLIED CONSTANT VARCHAR2(30):='N';
88
89 --Line Type Code
90 G_PRICE_BREAK_TYPE CONSTANT VARCHAR2(30):= 'PBH';
91 G_RECURRING_BREAK CONSTANT VARCHAR2(30):= 'RECURRING';
92 G_OTHER_ITEM_DISCOUNT CONSTANT VARCHAR2(30):= 'OID';
93 G_ITEM_UPGRADE CONSTANT VARCHAR2(30) :='IUE';
94 G_TERMS_SUBSTITUTION CONSTANT VARCHAR2(30) := 'TSN';
95 G_COUPON_ISSUE CONSTANT VARCHAR2(30) := 'CIE';
96 G_COUPON CONSTANT VARCHAR2(30) := 'COUPON';
97 G_DISCOUNT CONSTANT VARCHAR2(30) := 'DIS';
98 G_SURCHARGE CONSTANT VARCHAR2(30) := 'SUR';
99 G_PROMO_GOODS_DISCOUNT CONSTANT VARCHAR2(30) := 'PRG';
100 G_FREIGHT_CHARGE CONSTANT VARCHAR2(30) := 'FREIGHT_CHARGE';
101
102 -- Operand Calculation Codes
103 G_PERCENT_DISCOUNT CONSTANT VARCHAR2(30) := '%';
104 G_AMOUNT_DISCOUNT CONSTANT VARCHAR2(30) := 'AMT';
105 G_NEWPRICE_DISCOUNT CONSTANT VARCHAR2(30) := 'NEWPRICE';
106 G_LUMPSUM_DISCOUNT CONSTANT VARCHAR2(30) := 'LUMPSUM';
107
108 -- Price List Types
109 G_UNIT_PRICE CONSTANT VARCHAR2(30) := 'UNIT_PRICE';
110 G_PERCENT_PRICE CONSTANT VARCHAR2(30) := 'PERCENT_PRICE';
111 G_BLOCK_PRICE CONSTANT VARCHAR2(30) := 'BLOCK_PRICE';
112
113 --Attribute type
114 G_QUALIFIER_TYPE CONSTANT VARCHAR2(30):='QUALIFIER';
115 G_PRICING_TYPE CONSTANT VARCHAR2(30):='PRICING';
116 G_PRODUCT_TYPE CONSTANT VARCHAR2(30):='PRODUCT';
117 G_BENEFIT_TYPE CONSTANT VARCHAR2(30):='BENEFIT';
118 G_QUANTITY CONSTANT VARCHAR2(1):='Q';
119 G_AMOUNT CONSTANT VARCHAR2(1):='A';
120 G_ORDER_LINE_TYPE CONSTANT VARCHAR2(30):='ORDER_LINE';
121 G_ADJUSTMENT_LINE_TYPE CONSTANT VARCHAR2(30):='ADJUSTMENT_LINE';
122 G_CHILD_DETAIL_TYPE CONSTANT VARCHAR2(30):='CHILD_DETAIL_LINE';
123 G_PRICE_LIST_TYPE CONSTANT VARCHAR2(30):='PLL';
124
125 --Line level
126 G_LINE_LEVEL CONSTANT VARCHAR2(30):='LINE';
127 G_DETAIL_LEVEL CONSTANT VARCHAR2(30):='DETAIL';
128 G_ORDER_LEVEL CONSTANT VARCHAR2(30):='ORDER';
129
130 G_LINE_GROUP CONSTANT VARCHAR2(30):='LINEGROUP';
131
132 --PROCESSED_CODE
133 G_LINE_GROUP_PROCESSED CONSTANT VARCHAR2(30):='LGP';
134 G_DISCOUNT_MODE CONSTANT VARCHAR2(3):='DIS';
135 G_PRICELIST_MODE CONSTANT VARCHAR2(3):='PLL';
136
137 --COMPARISON OPERATOR TYPE CODE
138 G_OPERATOR_BETWEEN CONSTANT VARCHAR2(30):='BETWEEN';
139
140 --Context for list header and list line as qualifiers
141 G_LIST_HEADER_CONTEXT CONSTANT VARCHAR2(30):= 'MODLIST';
142 G_OLD_LIST_HEADER_CONTEXT CONSTANT VARCHAR2(30):='ORDER';
143 G_LIST_LINE_CONTEXT CONSTANT VARCHAR2(30):= 'LISTLINE';
144 G_PRIC_VOLUME_CONTEXT CONSTANT VARCHAR2(30):= 'VOLUME';
145 G_PRIC_ITEM_CONTEXT CONSTANT VARCHAR2(30):= 'ITEM';
146 G_CUSTOMER_CONTEXT CONSTANT VARCHAR2(30) := 'CUSTOMER';
147
148 -- Attributes
149 G_QUAL_ATTRIBUTE1 CONSTANT VARCHAR2(30) := 'QUALIFIER_ATTRIBUTE1'; -- Promotion
150 G_QUAL_ATTRIBUTE2 CONSTANT VARCHAR2(30) := 'QUALIFIER_ATTRIBUTE2'; -- List Line Id
151 G_QUAL_ATTRIBUTE6 CONSTANT VARCHAR2(30) := 'QUALIFIER_ATTRIBUTE6'; -- Discount Id
152 G_PRIC_ATTRIBUTE1 CONSTANT VARCHAR2(30) := 'PRICING_ATTRIBUTE1';
153 G_PRIC_ATTRIBUTE10 CONSTANT VARCHAR2(30) := 'PRICING_ATTRIBUTE10';
154 G_PRIC_ATTRIBUTE12 CONSTANT VARCHAR2(30) := 'PRICING_ATTRIBUTE12';
155 G_GSA_ATTRIBUTE CONSTANT VARCHAR2(30) :='QUALIFIER_ATTRIBUTE15';
156 G_DISCOUNT_ATTRIBUTE CONSTANT VARCHAR2(30) :='QUALIFIER_ATTRIBUTE6';
157 G_PROMOTION_ATTRIBUTE CONSTANT VARCHAR2(30):='QUALIFIER_ATTRIBUTE1';
158 G_PRICELIST_ATTRIBUTE CONSTANT VARCHAR2(30):='QUALIFIER_ATTRIBUTE4';
159 G_QUANTITY_ATTRIBUTE CONSTANT VARCHAR2(30):='PRICING_ATTRIBUTE10';
160 G_LINE_AMT_ATTRIBUTE CONSTANT VARCHAR2(30):='PRICING_ATTRIBUTE12';
161 G_ORDER_AMOUNT_ATTRIBUTE CONSTANT VARCHAR2(30):='QUALIFIER_ATTRIBUTE10';
162
163 --Need to change! these both may not be same
164 G_LINEGRP_QUANTITY_ATTRIBUTE CONSTANT VARCHAR2(30):='PRICING_ATTRIBUTE12';
165 G_LINEGRP_AMOUNT_ATTRIBUTE CONSTANT VARCHAR2(30):='PRICING_ATTRIBUTE12';
166
167 -- Yes/No/Phase/Debug Flags
168 G_YES CONSTANT VARCHAR2(20) := 'Y';
169 G_NO CONSTANT VARCHAR2(20) := 'N';
170 G_PHASE CONSTANT VARCHAR2(20):='P';
171 G_DONT_WRITE_TO_DEBUG CONSTANT VARCHAR2(20) := 'V';
172 G_ENGINE_TIME_TRACE_ON CONSTANT VARCHAR2(20) := 'T'; --3085171
173 -- Best Price Evaluation Constants
174 G_DISCOUNT_PROCESSING CONSTANT VARCHAR2(30) := 'DISCOUNT';
175 G_PRICELIST_PROCESSING CONSTANT VARCHAR2(30) := 'PRICE_LIST';
176
177 -- Incompatibility Processing
178 G_INCOMP_EXCLUSIVE CONSTANT VARCHAR2(30) := 'EXCL';
179
180 --Incompatibility Resolve Codes
181 G_INCOMP_PRECEDENCE CONSTANT VARCHAR2(30) := 'PRECEDENCE';
182 G_INCOMP_BEST_PRICE CONSTANT VARCHAR2(30) := 'BEST_PRICE';
183
184 -- Header/Line Qualifiers
185 G_HEADER_QUALIFIER CONSTANT VARCHAR2(30) := 'HQ';
186 G_LINE_QUALIFIER CONSTANT VARCHAR2(30) := 'LQ';
187
188 -- Search Flags
189 G_NO_SEARCH CONSTANT VARCHAR2(30):='N';
190 G_YES_SEARCH CONSTANT VARCHAR2(30):='Y';
191
192 --Context for Product ITEN
193 G_ITEM_CONTEXT CONSTANT VARCHAR2(30):='ITEM';
194
195 --INDICATE IF there is a pricing attribute passed in
196 G_PRICING_YES CONSTANT VARCHAR2(30):='Y';
197 G_PRICING_NO CONSTANT VARCHAR2(30):='N';
198
199 --EVENT CONSTANT
200 G_PRICE_LINE_EVENT CONSTANT VARCHAR2(30):='PRICE_LINE';
201 G_PRICE_ORDER_EVENT CONSTANT VARCHAR2(30):='PRICE_ORDER';
202
203 --PRICE BREAK TYPE
204 G_RANGE_BREAK CONSTANT VARCHAR2(30):='RANGE';
205 G_POINT_BREAK CONSTANT VARCHAR2(30):='POINT';
206
207 --G_RELATIONSHIP TYPE CODE
208 G_LINE_TO_LINE CONSTANT VARCHAR2(30):='LINE_TO_LINE';
209 G_LINE_TO_DETAIL CONSTANT VARCHAR2(30):='LINE_TO_DETAIL';
210 G_DETAIL_TO_DETAIL CONSTANT VARCHAR2(30):='DETAIL_TO_DETAIL';
211 G_ORDER_TO_LINE CONSTANT VARCHAR2(30):='ORDER_TO_LINE';
212 G_RELATED_ITEM_PRICE CONSTANT VARCHAR2(30):='RELATED_ITEM_PRICE';
213 G_PBH_LINE CONSTANT VARCHAR2(30):='PBH_LINE';
214 G_SERVICE_LINE CONSTANT VARCHAR2(30):='SERVICE_LINE';
215 G_GENERATED_LINE CONSTANT VARCHAR2(30):='GENERATED_LINE';
216
217 --List Header Type Code
218 G_DISCOUNT_LIST_HEADER CONSTANT VARCHAR2(30):='DLT';
219 G_PRICE_LIST_HEADER CONSTANT VARCHAR2(30):='PRL';
220 G_AGR_LIST_HEADER CONSTANT VARCHAR2(30):='AGR';
221 G_CHARGES_HEADER CONSTANT VARCHAR2(30):='CHARGES';
222
223 --Profile Option Constants
224 --GSA
225 G_GSA_Max_Discount_Enabled CONSTANT VARCHAR2(30) := 'QP_VERIFY_GSA';
226 G_BYPASS_PRICING CONSTANT VARCHAR2(30) := 'QP_BYPASS_PRICING';
227 G_RETURN_MANUAL_DISCOUNTS CONSTANT VARCHAR2(30) := 'QP_RETURN_MANUAL_DISCOUNTS';
228 G_BLIND_DISCOUNT CONSTANT VARCHAR2(30) := 'QP_BLIND_DISCOUNT';
229
230 --DATA_TYPE
231 G_NUMERIC CONSTANT VARCHAR2(1):= 'N';
232 G_VARCHAR CONSTANT VARCHAR2(1):= 'C';
233 G_DATE CONSTANT VARCHAR2(1):= 'D';
234 G_DATE_X CONSTANT VARCHAR2(1):= 'X';
235 G_DATE_Y CONSTANT VARCHAR2(1):= 'Y';
236
237 --CONTROL RECORD constants
238 G_CALCULATE_ONLY CONSTANT VARCHAR2(30):='C';
239 G_SEARCH_ONLY CONSTANT VARCHAR2(30):='N';
240 G_SEARCH_N_CALCULATE CONSTANT VARCHAR2(30):='Y';
241 G_MANUAL_DISCOUNT_FLAG VARCHAR2(1);
242 G_GSA_CHECK_FLAG VARCHAR2(1);
243 G_GSA_DUP_CHECK_FLAG VARCHAR2(1);
244 G_TEMP_TABLE_INSERT_FLAG VARCHAR2(1);
245 G_PUBLIC_API_CALL_FLAG VARCHAR2(1);
246
247
248 G_YES_PROD_HDR_QUAL_IND CONSTANT NUMBER :=6; -- Has Header Qualifiers, Products
249 G_YES_PROD_PRIC_HDR_QUAL_IND CONSTANT NUMBER :=22;-- Has Header Qualifiers, Products and Pricing Attrs
250 G_YES_PROD_LINE_QUAL_IND CONSTANT NUMBER :=12;-- Has Line Qualifiers,Products
251 G_YES_PROD_PRIC_LINE_QUAL_IND CONSTANT NUMBER :=28;-- Has Line Qualifiers,Products and Pricing Attrs
252 G_YES_PROD_HDR_LINE_QUAL_IND CONSTANT NUMBER :=14;-- Has Header, Line Qualifiers,Products
253 G_YES_PRIC_HDR_LINE_QUAL_IND CONSTANT NUMBER :=30;-- Has Header, Line Qualifiers,Products,Pricing Attrs
254 G_YES_PROD_IND CONSTANT NUMBER :=4; -- Has Products
255 G_YES_PROD_PRIC_IND CONSTANT NUMBER :=20;-- Has Products and Pricing Attrs
256 G_YES_HDR_QUAL_IND CONSTANT NUMBER :=2; -- Has (Header Level) Qualifiers
257 G_YES_LINE_QUAL_IND CONSTANT NUMBER :=8; -- Has (Line Level) Qualifiers
258 G_YES_HDR_LINE_QUAL_IND CONSTANT NUMBER :=10;-- Has (Header+Line Level) Qualifiers
259 G_BLIND_DISCOUNT_IND CONSTANT NUMBER :=0; -- Blind Discount
260
261 G_NO_QUAL_IND CONSTANT NUMBER := 2;
262 G_NO_PRIC_IND CONSTANT NUMBER := 4;
263 G_NO_QUAL_PRIC_IND CONSTANT NUMBER := 6;
264
265 G_LINE_DETAIL_INDEX PLS_INTEGER :=1;
266 G_DEBUG_ENGINE VARCHAR2(3);
267
268
269
270
271
272
273 G_BACK_CALCULATION_STS VARCHAR2(30) := 'BACK_CALCULATION_ERROR';
274 G_BACK_CALCULATION_STS_NONE VARCHAR2(30) := 'NONE';
275 G_BACK_CALCULATE VARCHAR2(30) := 'BACK_CALCULATE';
276 --is_ldet_rec used in calculation_cur in VCLNB.pls
277 G_LDET_ORDER_TYPE CONSTANT VARCHAR2(30) := 'Y_ORDER';
278 G_ADJ_ORDER_TYPE CONSTANT VARCHAR2(30) := 'N_ORDER';
279 G_ASO_ORDER_TYPE CONSTANT VARCHAR2(30) := 'X_ORDER';
280 G_LDET_LINE_TYPE CONSTANT VARCHAR2(30) := 'Y_LINE';
281 G_ADJ_LINE_TYPE CONSTANT VARCHAR2(30) := 'N_LINE';
282 G_ASO_LINE_TYPE CONSTANT VARCHAR2(30) := 'X_LINE';
283 --debug profile
284 G_QP_DEBUG VARCHAR2(1);
285
286 -- price book
287 G_CALL_FROM_PRICE_BOOK VARCHAR2(1) := 'N';
288
289 TYPE FRT_CHARGE_REC IS RECORD
290 ( LINE_INDEX NUMBER
291 ,LINE_DETAIL_INDEX NUMBER
292 ,CREATED_FROM_LIST_LINE_ID NUMBER
293 ,ADJUSTMENT_AMOUNT NUMBER
294 ,LEVEL VARCHAR2(30)
295 ,CHARGE_TYPE_CODE VARCHAR2(30)
296 ,CHARGE_SUBTYPE_CODE VARCHAR2(30)
297 ,UPDATED_FLAG VARCHAR2(1)
298 ,DELETED_FLAG VARCHAR2(1));
299
300 TYPE FRT_CHARGE_TBL IS TABLE OF FRT_CHARGE_REC INDEX BY BINARY_INTEGER;
301
302
303
304 TYPE adj_rec_type IS RECORD
305 (
306 created_from_list_line_id number,
307 line_ind number,
308 curr_line_index number,
309 line_id number,
310 line_detail_index number,
311 created_from_list_line_type varchar2(30),
312 created_from_list_header_id number,
313 modifier_level_code varchar(30),
314 applied_flag varchar2(1),
315 amount_changed number,
316 adjusted_unit_price number,
317 priced_quantity number,
318 line_priced_quantity number,
319 updated_adjusted_unit_price number,
320 automatic_flag varchar2(1),
321 override_flag varchar2(1),
322 pricing_group_sequence number,
323 operand_calculation_code varchar2(30),
324 operand_value number,
325 adjustment_amount number,
326 unit_price number,
327 accrual_flag varchar2(1),
328 updated_flag varchar2(1),
329 process_code varchar2(30),
330 pricing_status_code varchar2(30),
331 pricing_status_text varchar2(240),
332 price_break_type_code varchar2(30),
333 charge_type_code varchar2(30),
334 charge_subtype_code varchar2(30),
335 rounding_factor number,
336 pricing_phase_id number,
337 created_from_list_type_code varchar2(30),
338 limit_code varchar2(30),
339 limit_text varchar2(2000),
340 list_line_no varchar2(240),
341 group_quantity number,
342 group_amount number,
343 line_pricing_status_code varchar2(30),
344 is_ldet_rec varchar2(30),
345 line_type_code varchar2(30),
346 price_adjustment_id number,
347 net_amount_flag varchar2(1),
348 calculation_code varchar2(30),
349 ordered_qty number,
350 catchweight_qty number,
351 actual_order_qty number,
352 line_unit_price number,
353 line_category varchar2(30),
354 price_flag varchar2(1)
355 );
356
357 TYPE adj_tbl_type IS TABLE OF adj_rec_type index by BINARY_INTEGER;
358
359 --4900095 service item lumpsum discount
360 --procedure to evaluate the quantity to prorate lumpsum
361 --to include the parent quantity
362
363 PROCEDURE Determine_svc_item_quantity;
364
365 PROCEDURE UPDATE_UNIT_PRICE(x_return_status OUT NOCOPY VARCHAR2,
366 x_return_status_text OUT NOCOPY VARCHAR2);
367
368 /*#
369 * This API allows you to get a base price and to apply price adjustments, other
370 * benefits, and charges to a transaction.
371 *
372 * @param p_line_tbl the input table which contains the elements in the calling
373 * application that require a base and adjusted price
374 * @param p_qual_tbl the input table that contains qualifier information that
375 * helps the pricing engine to determine the pricelist lines
376 * and modifier list lines for which a pricing request is
377 * eligible
378 * @param p_line_attr_tbl the input table that contains pricing attribute
379 * information that helps the pricing engine to determine
380 * the price list lines and modifier list lines for which
381 * a pricing request is eligible
382 * @param p_line_detail_tbl the input table that contains the details of the
383 * derivation of the base and adjusted prices
384 * @param p_line_detail_qual_tbl the input table that contains the details of the
385 * derivation of the qualifier information
386 * @param p_line_detail_attr_tbl the input table that contains the details of the
387 * derivation of the pricing attribute information
388 * @param p_related_lines_tbl the input table that contains relationships between
389 * request lines and request line details
390 * @param p_control_rec the input record that contains parameters which control
391 * the behavior of the pricing engine
392 * @param x_line_tbl the output table which contains the elements in the calling
393 * application that require a base and adjusted price
394 * @param x_line_qual the output table that contains qualifier information
395 * @param x_line_attr_tbl the output table that contains pricing attribute
396 * information
397 * @param x_line_detail_tbl the output table that contains the details of the
398 * derivation of the base and adjusted prices
399 * @param x_line_detail_qual_tbl the output table that contains the details of
400 * the derivation of the qualifier information
401 * @param x_line_detail_attr_tbl the output table that contains the details of
402 * the derivation of the pricing attribute
403 * information
404 * @param x_related_lines_tbl the output table that contains relationships
405 * between request lines and request line details
406 * @param x_return_status the return status of the request
407 * @param x_return_status_text the return status text of the request
408 *
409 * @rep:displayname Price Request
410 */
411 PROCEDURE PRICE_REQUEST
412 (p_line_tbl IN QP_PREQ_GRP.LINE_TBL_TYPE,
413 p_qual_tbl IN QP_PREQ_GRP.QUAL_TBL_TYPE,
414 p_line_attr_tbl IN QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
415 p_line_detail_tbl IN QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
416 p_line_detail_qual_tbl IN QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE,
417 p_line_detail_attr_tbl IN QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE,
418 p_related_lines_tbl IN QP_PREQ_GRP.RELATED_LINES_TBL_TYPE,
419 p_control_rec IN QP_PREQ_GRP.CONTROL_RECORD_TYPE,
420 x_line_tbl OUT NOCOPY QP_PREQ_GRP.LINE_TBL_TYPE,
421 x_line_qual OUT NOCOPY QP_PREQ_GRP.QUAL_TBL_TYPE,
422 x_line_attr_tbl OUT NOCOPY QP_PREQ_GRP.LINE_ATTR_TBL_TYPE,
423 x_line_detail_tbl OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE,
424 x_line_detail_qual_tbl OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE,
425 x_line_detail_attr_tbl OUT NOCOPY QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE,
426 x_related_lines_tbl OUT NOCOPY QP_PREQ_GRP.RELATED_LINES_TBL_TYPE,
427 x_return_status OUT NOCOPY VARCHAR2,
428 x_return_status_text OUT NOCOPY VARCHAR2
429 );
430
431 FUNCTION Raise_GSA_Error
432 ( p_request_type_code IN VARCHAR2
433 , p_inventory_item_id IN NUMBER
434 , p_pricing_date IN DATE
435 , p_unit_price IN NUMBER
436 , p_cust_account_id IN NUMBER
437 )
438 RETURN BOOLEAN;
439
440 --overloaded for applications who insert into temp tables directly
441 PROCEDURE PRICE_REQUEST
442 (p_control_rec IN QP_PREQ_GRP.CONTROL_RECORD_TYPE,
443 x_return_status OUT NOCOPY VARCHAR2,
444 x_return_status_text OUT NOCOPY VARCHAR2
445 );
446
447 PROCEDURE CHECK_GSA_VIOLATION( x_return_status OUT NOCOPY VARCHAR2,
448 x_return_status_text OUT NOCOPY VARCHAR2);
449
450 PROCEDURE Update_Child_Break_Lines(x_return_status OUT NOCOPY VARCHAR2,
451 x_return_status_text OUT NOCOPY VARCHAR2);
452
453 --Procedure to update the line status to 'UPDATED' if there are
454 --any lines with adjustments with process_code 'UPDATED'/'N'
455
456 PROCEDURE Update_Line_Status(x_return_status OUT NOCOPY VARCHAR2,
457 x_return_status_text OUT NOCOPY VARCHAR2);
458
459 PROCEDURE CALCULATE_PRICE
460 (
461 p_request_type_code IN VARCHAR2,
462 p_rounding_flag IN VARCHAR2,
463 p_view_name IN VARCHAR2,
464 p_event_code IN VARCHAR2,
465 p_adj_tbl IN QP_PREQ_PUB.adj_tbl_type,
466 x_return_status OUT NOCOPY VARCHAR2,
467 x_return_status_text OUT NOCOPY VARCHAR2
468 );
469
470 -- 3493716: parameter change from p_list_line_id to p_ldet_index
471 -- 3721860 - reverted the fix done for bug 3493716 and added a new parameter p_line_index
472 FUNCTION Get_Buy_Line_Price_flag(p_list_line_id IN NUMBER, p_line_index IN NUMBER) RETURN VARCHAR2;
473
474 PROCEDURE Update_passed_in_pbh(x_return_status OUT NOCOPY VARCHAR2,
475 x_return_status_text OUT NOCOPY VARCHAR2);
476
477 --for catchwt pricing
478 PROCEDURE GET_ORDERQTY_VALUES(p_ordered_qty IN NUMBER,
479 p_priced_qty IN NUMBER,
480 p_catchweight_qty IN NUMBER,
481 p_actual_order_qty IN NUMBER,
482 p_unit_price IN NUMBER DEFAULT NULL,
483 p_adjusted_unit_price IN NUMBER DEFAULT NULL,
484 p_line_unit_price IN NUMBER DEFAULT NULL,
485 p_operand IN NUMBER DEFAULT NULL,
486 p_adjustment_amt IN NUMBER DEFAULT NULL,
487 p_operand_calculation_code IN VARCHAR2 DEFAULT NULL,
488 p_input_type IN VARCHAR2,
489 x_ordqty_output1 OUT NOCOPY NUMBER,
490 x_ordqty_output2 OUT NOCOPY NUMBER,
491 x_return_status OUT NOCOPY VARCHAR2,
492 x_return_status_text OUT NOCOPY VARCHAR2);
493
494 --prg constants
495 G_FREEGOOD CONSTANT VARCHAR2(30) := 'FREEGOOD';
496 G_FREEGOOD_LINE CONSTANT VARCHAR2(30) := 'F';
497 G_BUYLINE CONSTANT VARCHAR2(30) := 'BUYLINE';
498 G_NOT_VALID CONSTANT VARCHAR2(30) := 'NOT_VALID';
499 G_REQUEST_TYPE_CODE VARCHAR2(30);
500 G_CHECK_CUST_VIEW_FLAG VARCHAR2(30);
501
502 G_BUYLINE_PRICE_FLAG QP_PREQ_GRP.FLAG_TYPE;
503 G_GET_FREIGHT_FLAG VARCHAR2(1);
504
505 --2388011
506 G_PBHVOLATTR_ATTRIBUTE QP_PREQ_GRP.VARCHAR_TYPE;
507
508 --procedure to return price and status code and text -- needed by PO team
509 PROCEDURE get_price_for_line(p_line_index IN NUMBER,
510 p_line_id IN NUMBER,
511 x_line_unit_price OUT NOCOPY number,
512 x_adjusted_unit_price OUT NOCOPY number,
513 x_return_status OUT NOCOPY varchar2,
514 x_pricing_status_code OUT NOCOPY varchar2,
515 x_pricing_status_text OUT NOCOPY varchar2
516 );
517
518 type varchar2000_tbl_type is table of varchar2(2000) index by binary_integer; -- bug 3618464
519 G_LINE_INDEXES_FOR_LINE_ID varchar2000_tbl_type; -- bug 3618464
520 G_BUYLINE_INDEXES_FOR_LINE_ID varchar2000_tbl_type; -- bug 3721860
521
522 --4900095
523 G_PBH_MOD_LEVEL_CODE QP_PREQ_GRP.varchar_type;
524 G_Service_pbh_lg_amt_qty QP_PREQ_GRP.number_type;
525
526 END QP_PREQ_PUB;