[Home] [Help]
PACKAGE BODY: APPS.QP_PRC_UTIL
Source
1 PACKAGE BODY QP_PRC_UTIL AS
2 /* $Header: QPXUPRCB.pls 120.2 2005/10/03 23:26:50 srashmi noship $ */
3
4 -- Global constants holding the package name.
5
6 G_PKG_NAME CONSTANT VARCHAR2(30):='QP_PRC_UTIL';
7
8 -- Global variables and tables to keep track if attribute data
9 -- has been queried yet in the Attribute_Used procedure
10
11 G_ATTRIBUTES_QUERIED BOOLEAN := FALSE;
12
13 TYPE Attribute_Tbl_Type IS TABLE OF BOOLEAN
14 INDEX BY BINARY_INTEGER;
15
16 G_ATTRIBUTE_USED Attribute_Tbl_Type;
17 --QP
18 G_SOLD_TO_ORG_NAME VARCHAR2(30);
19 G_SOLD_TO_ORG_CONTEXT VARCHAR2(30);
20 G_SITE_ORG_NAME VARCHAR2(30);
21 G_SITE_ORG_CONTEXT VARCHAR2(30);
22 G_UNIT_NAME VARCHAR2(30);
23 G_UNIT_CONTEXT VARCHAR2(30);
24 G_CUSTOMER_CLASS_NAME VARCHAR2(30);
25 G_CUSTOMER_CLASS_CONTEXT VARCHAR2(30);
26 G_CUSTOMER_PO_NAME VARCHAR2(30); --1004
27 G_CUSTOMER_PO_CONTEXT VARCHAR2(30);
28 G_ORDER_TYPE_NAME VARCHAR2(30); --1007
29 G_ORDER_TYPE_CONTEXT VARCHAR2(30);
30 G_AGREEMENT_NAME VARCHAR2(30); --1006
31 G_AGREEMENT_CONTEXT VARCHAR2(30);
32 G_AGREEMENT_TYPE_NAME VARCHAR2(30); --1005
33 G_AGREEMENT_TYPE_CONTEXT VARCHAR2(30);
34 G_DOLLARS_NAME VARCHAR2(30);
35 G_DOLLARS_CONTEXT VARCHAR2(30);
36 G_ITEM_CONTEXT VARCHAR2(30);
37 G_ITEM_NAME VARCHAR2(30);
38 G_ITEM_CATEGORY_CONTEXT VARCHAR2(30);
39 G_ITEM_CATEGORY_NAME VARCHAR2(30);
40 G_PRICE_LIST_CONTEXT VARCHAR2(30);
41 G_PRICE_LIST_NAME VARCHAR2(30);
42 --QP END
43
44
45 -- FUNCTION EQUAL
46
47 FUNCTION Equal
48 ( p_attr1 IN NUMBER ,
49 p_attr2 IN NUMBER
50 ) RETURN BOOLEAN
51 IS
52 BEGIN
53
54 oe_debug_pub.add('Entering QP_PRC_UTIL.EQUAL', 1);
55
56 RETURN ( p_attr1 IS NULL AND
57 p_attr2 IS NULL ) OR
58 ( p_attr1 IS NOT NULL AND
59 p_attr2 IS NOT NULL AND
60 p_attr1 = p_attr2 );
61
62 oe_debug_pub.add('Exiting QP_PRC_UTIL.EQUAL', 1);
63
64 END Equal;
65
66 FUNCTION Equal
67 ( p_attr1 IN VARCHAR2,
68 p_attr2 IN VARCHAR2
69 ) RETURN BOOLEAN
70 IS
71 BEGIN
72
73 RETURN ( p_attr1 IS NULL AND
74 p_attr2 IS NULL ) OR
75 ( p_attr1 IS NOT NULL AND
76 p_attr2 IS NOT NULL AND
77 p_attr1 = p_attr2 );
78
79 END Equal;
80
81 FUNCTION Equal
82 ( p_attr1 IN DATE ,
83 p_attr2 IN DATE
84 ) RETURN BOOLEAN
85 IS
86 BEGIN
87
88 RETURN ( p_attr1 IS NULL AND
89 p_attr2 IS NULL ) OR
90 ( p_attr1 IS NOT NULL AND
91 p_attr2 IS NOT NULL AND
92 p_attr1 = p_attr2 );
93
94 END Equal;
95
96
97
98 -- Get_Hdr_Adj_Total queries the total of all header level adjustments
99 -- from DB.
100
101 FUNCTION Get_Hdr_Adj_Total
102 ( p_header_id IN NUMBER := NULL) RETURN NUMBER
103 IS
104 l_adj_total NUMBER := 0;
105 BEGIN
106
107 oe_debug_pub.add('Entering QP_PRC_UTIL.GET_HDR_ADJ_TOTAL', 1);
108
109 -- If p_header_id is NULL, return 0
110
111 IF p_header_id IS NULL THEN
112
113 oe_debug_pub.add('Exiting QP_PRC_UTIL.GET_HDR_ADJ_TOTAL', 1);
114
115 RETURN 0;
116 END IF;
117
118 -- Query total.
119
120 SELECT SUM ( OPERAND ) --QP change percent to OPERAND QP END
121 INTO l_adj_total
122 FROM OE_PRICE_ADJUSTMENTS
123 WHERE HEADER_ID = p_header_id
124 AND LINE_ID IS NULL;
125
126 IF l_adj_total IS NULL THEN
127
128 l_adj_total := 0;
129
130 END IF;
131
132 oe_debug_pub.add('Exiting QP_PRC_UTIL.GET_HDR_ADJ_TOTAL', 1);
133
134 RETURN l_adj_total;
135
136 EXCEPTION
137
138 WHEN OTHERS THEN
139
140 -- Unexpected error
141 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
142
143 OE_MSG_PUB.Add_Exc_Msg
144 ( G_PKG_NAME ,
145 'Price_Utilities - Get_Hdr_Adj_Total'
146 );
147 END IF;
148
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150
151 END Get_Hdr_Adj_Total;
152
153 -- FUNCTION Get_Agr_Type : Queries the agreement type code from
154 -- OE_AGREEMENTS.
155
156 FUNCTION Get_Agr_Type
157 ( p_agreement_id IN NUMBER := NULL
158 ) RETURN VARCHAR2
159 IS
160 l_agr_type_code VARCHAR2(30);
161 BEGIN
162
163 oe_debug_pub.add('Entering QP_PRC_UTIL.GET_AGR_TYPE', 1);
164
165 -- If p_agreement_id is NULL, return NULL
166
167 IF p_agreement_id IS NULL THEN
168
169 oe_debug_pub.add('Exiting QP_PRC_UTIL.GET_AGR_TYPE', 1);
170
171 RETURN NULL;
172 END IF;
173
174 -- Query Agreement type.
175
176 SELECT AGREEMENT_TYPE_CODE
177 INTO l_agr_type_code
178 FROM OE_AGREEMENTS
179 WHERE AGREEMENT_ID = p_agreement_id;
180
181 oe_debug_pub.add('Exiting QP_PRC_UTIL.GET_AGR_TYPE', 1);
182
183 RETURN l_agr_type_code;
184
185 EXCEPTION
186
187 WHEN OTHERS THEN
188
189 -- Unexpected error
190 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
191
192 OE_MSG_PUB.Add_Exc_Msg
193 ( G_PKG_NAME ,
194 'Get_Agr_Type. p_agr_id = '||p_agreement_id
195 );
196 END IF;
197
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199
200 END Get_Agr_Type;
201
202 --Function Get_Item_Category
203 -- Usage:
204 -- This procedure is called from the Price API to fetch the item
205 -- category if missing.
206 -- Description:
207 -- Uses the p_item_id to fetch the item category.
208 -- Notes
209 -- This procedure doesn't perform the fetch because this version of
210 -- the API doesn't support discounting by item category. In future
211 -- releases, the SQL statement should be added to this procedure.
212
213 FUNCTION Get_item_Category
214 ( p_item_id IN NUMBER
215 ) RETURN NUMBER
216 IS
217 l_org_id NUMBER := NULL;
218 l_item_category_id NUMBER := NULL;
219 BEGIN
220
221 oe_debug_pub.add('Entering QP_PRC_UTIL.GET_ITEM_CATEGORY', 1);
222
223 IF p_item_id IS NOT NULL THEN
224
225 -- Fetch validation org.
226
227 l_org_id := FND_PROFILE.VALUE('SO_ORGANIZATION_ID');
228
229 IF l_org_id IS NOT NULL THEN
230
231 SELECT CATEGORY_ID
232 INTO l_item_category_id
233 FROM MTL_ITEM_CATEGORIES CAT
234 , MTL_DEFAULT_CATEGORY_SETS DCS
235 WHERE CAT.INVENTORY_ITEM_ID = p_item_id
236 AND CAT.ORGANIZATION_ID = l_org_id
237 AND CAT.CATEGORY_SET_ID = DCS.CATEGORY_SET_ID
238 AND DCS.FUNCTIONAL_AREA_ID = 7;
239
240 END IF;
241
242 END IF;
243
244 oe_debug_pub.add('Exiting QP_PRC_UTIL.GET_ITEM_CATEGORY', 1);
245
246 RETURN l_item_category_id;
247
248
249 EXCEPTION
250
251 WHEN NO_DATA_FOUND THEN
252
253 RETURN NULL;
254
255 WHEN OTHERS THEN
256
257 -- Unexpected error
258
259 IF OE_MSG_PUB.Check_Msg_Level(
260 OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
261 THEN
262
263 OE_MSG_PUB.Add_Exc_Msg
264 ( G_PKG_NAME ,
265 'Get_Item_Category'
266 );
267 END IF;
268
269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270
271 END Get_Item_Category;
272
273 -- FUNCTION Get_Cust_Class : Queries the customer class code from
274 -- RA_CUSTOMERS.
275
276 FUNCTION Get_Cust_Class
277 ( p_sold_to_org_id IN NUMBER := NULL
278 ) RETURN VARCHAR2
279 IS
280 l_class_code VARCHAR2(30);
281 BEGIN
282
283 oe_debug_pub.add('Entering QP_PRC_UTIL.GET_CUST_CLASS', 1);
284
285 -- If p_customer_id is NULL, return NULL
286
287 IF p_sold_to_org_id IS NULL THEN
288
289 oe_debug_pub.add('Exiting QP_PRC_UTIL.GET_CUST_CLASS', 1);
290
291 RETURN NULL;
292 END IF;
293
294 -- Query Customer Class Code.
295
296 SELECT CUSTOMER_CLASS_CODE
297 INTO l_class_code
298 FROM HZ_CUST_ACCOUNTS
299 WHERE CUST_ACCOUNT_ID = p_sold_to_org_id;
300
301 oe_debug_pub.add('Exiting QP_PRC_UTIL.GET_CUST_CLASS', 1);
302
303 RETURN l_class_code;
304
305 EXCEPTION
306
307 WHEN OTHERS THEN
308
309 -- Unexpected error
310 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
311
312 OE_MSG_PUB.Add_Exc_Msg
313 ( G_PKG_NAME ,
314 'Get_Cust_Class. p_cust_id = '||p_sold_to_org_id
315 );
316 END IF;
317
318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
319
320 END Get_Cust_Class;
321
322 -- Fix For Bug-1974413
323 -- This Function returns Attribute Name corresponding to the
324 -- Attribute Code Passed.
325
326 FUNCTION Get_Attribute_Name
327 (p_attribute_code IN VARCHAR2
328 ) RETURN VARCHAR2
329 IS
330 l_attribute_name VARCHAR2(240);
331 BEGIN
332 SELECT AK.NAME
333 INTO l_attribute_name
334 FROM AK_ATTRIBUTES_VL AK
335 WHERE AK.attribute_code = upper(p_attribute_code)
336 AND AK.attribute_application_id = 661;
337 RETURN(l_attribute_name);
338 END Get_Attribute_Name;
339
340
341 -- Procedure Query_Adjustments
342 -- Usage:
343 -- This procedure is called from Price_Line and Price_Order.
344 -- Description:
345 -- Queries line and/or header level adjustments.
346 --
347
348 PROCEDURE Query_Adjustments
349 ( p_header_id IN NUMBER := NULL ,
350 p_line_id IN NUMBER := NULL ,
351 p_adj_tbl OUT NOCOPY /* file.sql.39 change */ QP_PRC_UTIL.Adj_Short_Tbl_Type
352 )
353 IS
354
355 CURSOR l_line_adj_csr IS
356 SELECT P.PRICE_ADJUSTMENT_ID , -- QP
357 P.LIST_HEADER_ID , -- CHANGE FROM DISCOUNT_ID TO LIST_HEADER_ID
358 P.LIST_LINE_ID , -- CHANGE FROM DISCOUNT_LINE TO LIST_LINE_ID
359 P.AUTOMATIC_FLAG ,
360 P.OPERAND , -- CHANGE PERCENT TO OPERAND
361 P.HEADER_ID , --
362 P.LINE_ID , -- Change OE_DISCOUNT to QP_LIST_HEADERS
363 D.NAME
364 FROM OE_PRICE_ADJUSTMENTS P, QP_LIST_HEADERS D
365 WHERE P.LINE_ID = p_line_id
366 AND P.LIST_HEADER_ID = D.LIST_HEADER_ID;
367 -- QP END
368 CURSOR l_hdr_adj_csr IS
369 SELECT P.PRICE_ADJUSTMENT_ID ,
370 P.LIST_HEADER_ID , --QP CHANGE FROM DISCOUNT_ID TO LIST_HEADER_ID
371 P.LIST_LINE_ID , -- CHANGE FROM DISCOUNT_LINE_ID TO LIST_LINE_ID
372 P.AUTOMATIC_FLAG ,
373 P.OPERAND , -- CHANGE PERCENT TO OPERAN
374 P.HEADER_ID , -- Change OE_DISCOUNT to QP_LIST_HEADERS
375 D.NAME
376 FROM OE_PRICE_ADJUSTMENTS P, QP_LIST_HEADERS D
377 WHERE HEADER_ID = p_header_id
378 AND LINE_ID IS NULL
379 AND P.DISCOUNT_ID = D.LIST_HEADER_ID;
380 -- QP END
381
382 l_adj_rec QP_PRC_UTIL.Adj_Short_Rec_Type;
383 temp_num NUMBER;
384 adj_number NUMBER := 0;
385
386 BEGIN
387
388 oe_debug_pub.add('Entering QP_PRC_UTIL.QUERY_ADJUSTMENTS', 1);
389
390 -- Header level adjustments.
391
392 IF p_header_id IS NOT NULL THEN
393
394 OPEN l_hdr_adj_csr ;
395
396 LOOP
397 -- load l_adj_rec
398
399 FETCH l_hdr_adj_csr INTO
400 l_adj_rec.adjustment_id ,
401 l_adj_rec.discount_id ,
402 l_adj_rec.discount_line_id ,
403 l_adj_rec.automatic_flag ,
404 l_adj_rec.percent ,
405 l_adj_rec.header_id ,
406 l_adj_rec.discount_name;
407
408 EXIT WHEN l_hdr_adj_csr%NOTFOUND;
409
410 -- add adjustment to adjustment table
411
412 adj_number := adj_number + 1;
413 p_adj_tbl(adj_number) := l_adj_rec;
414
415 END LOOP;
416
417 CLOSE l_hdr_adj_csr;
418
419 END IF;
420
421 -- Line level adjustments.
422
423 IF p_line_id IS NOT NULL THEN
424
425 OPEN l_line_adj_csr ;
426
427 LOOP
428 -- load l_adj_rec
429
430 FETCH l_line_adj_csr INTO
431 l_adj_rec.adjustment_id ,
432 l_adj_rec.discount_id ,
433 l_adj_rec.discount_line_id ,
434 l_adj_rec.automatic_flag ,
435 l_adj_rec.percent ,
436 l_adj_rec.header_id ,
437 l_adj_rec.line_id ,
438 l_adj_rec.discount_name;
439
440 EXIT WHEN l_line_adj_csr%NOTFOUND;
441
442 -- add adjustment to adjustment table
443
444 adj_number := adj_number + 1;
445 p_adj_tbl(adj_number) := l_adj_rec;
446
447 END LOOP;
448
449 CLOSE l_line_adj_csr;
450
451 END IF;
452
453 oe_debug_pub.add('Exiting QP_PRC_UTIL.QUERY_ADJUSTMENTS', 1);
454
455 EXCEPTION
456
457 WHEN OTHERS THEN
458
459 -- Unexpected error
460 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
461
462 OE_MSG_PUB.Add_Exc_Msg
463 ( G_PKG_NAME ,
464 'Query Adjustments. p_header_id = '
465 ||p_header_id||' p_line_id = '||p_line_id
466 );
467 END IF;
468
469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
470
471 END Query_Adjustments;
472
473
474
475 -- Function Attribute_Used
476 -- Usage:
477 -- Attribute_Used is called from Fetch_Best_Adjustment as a check to
478 -- see if a call to Get_Adjustment is necessary.
479 -- Description:
480 -- This function is used to enhance performance of pricing when a lot
481 -- of discount lines exist. It checks a profile option
482 -- OE_DISCOUNT_TUNING, and if set attempts to optimize the number of
483 -- fetches it does against OE_DISCOUNT_LINES (which is the number of
484 -- calls to Fetch_Best_Adjustment).
485 --
486 -- The function executes a query against OE_DISCOUNT_LINES to select the
487 -- entites used in discounts. If the attribute_id passed to the function
488 -- exists in OE_DISCOUNT_LINES the function returns TRUE, otherwise it
489 -- returns FALSE.
490 --
491 -- The function caches this information, so in subsequent calls it
492 -- doesn't need to hit the database. It uses a PL/SQL table of BOOLEANs
493 -- G_ATTRIBUTE_USED to store the results.
494
495 FUNCTION Attribute_Used
496 (p_attribute_id IN NUMBER) RETURN BOOLEAN
497 IS
498 l_discount_tuning VARCHAR2(1);
499 l_entity_id VARCHAR2(30); --QP CHANGE FROM NUMBER TO VARCHAR2
500 --QP END
501
502 /* QP replace following SQL with new SQL for QP data model
503 CURSOR used_entities IS
504 SELECT DISTINCT l.entity_id
505 FROM oe_discount_lines l,
506 oe_discounts d
507 WHERE d.automatic_discount_flag = 'Y'
508 AND d.discount_id = l.discount_id;
509 QP END */
510 /*
511 CURSOR used_entities IS
512 SELECT DISTINCT QPPA.PRODUCT_ATTRIBUTE
513 FROM QP_PRICING_ATTRIBUTES QPPA
514 , QP_LIST_LINES QPLL
515 , QP_LIST_HEADERS QPLH
516 WHERE QPLL.LIST_LINE_ID = QPPA.LIST_LINE_ID
517 AND QPLL.LIST_HEADER_ID = QPLH.LIST_HEADER_ID
518 AND QPLH.AUTOMATIC_FLAG = 'Y'
519 AND QPPA.PRODUCT_ATTRIBUTE IN (G_ITEM_NAME
520 ,G_ITEM_CATEGORY_NAME)
521 AND QPPA.PRODUCT_ATTRIBUTE_CONTEXT IN (G_ITEM_CONTEXT
522 ,G_ITEM_CATEGORY_CONTEXT)
523 UNION
524 SELECT DISTINCT QPQ.QUALIFIER_ATTRIBUTE
525 FROM QP_QUALIFIERS QPQ
526 ,QP_LIST_HEADERS QPLH
527 ,QP_LIST_LINES QPLL
528 WHERE QPQ.LIST_HEADER_ID = QPLH.LIST_HEADER_ID
529 AND QPLL.LIST_HEADER_ID = QPLH.LIST_HEADER_ID
530 AND QPLH.AUTOMATIC_FLAG = 'Y'
531 AND QPQ.QUALIFIER_ATTRIBUTE IN (G_CUSTOMER_PO_NAME
532 ,G_AGREEMENT_TYPE_NAME
533 ,G_AGREEMENT_NAME
534 ,G_ORDER_TYPE_NAME)
535 AND QPQ.QUALIFIER_CONTEXT IN (G_CUSTOMER_PO_CONTEXT
536 ,G_AGREEMENT_TYPE_CONTEXT
537 ,G_AGREEMENT_CONTEXT
538 ,G_ORDER_TYPE_CONTEXT);*/
539 --QP END
540
541 BEGIN
542
543 oe_debug_pub.add('Entering QP_PRC_UTIL.ATTRIBUTE_USED', 1);
544
545 IF NOT G_ATTRIBUTES_QUERIED THEN
546
547 -- First time to call this function. Fetch values from DB.
548
549 G_ATTRIBUTES_QUERIED := TRUE;
550
551 l_discount_tuning := FND_PROFILE.VALUE ('OE_DISCOUNT_TUNING');
552
553 IF l_discount_tuning = 'Y' THEN
554
555 -- Set all pricing attributes used flags to TRUE
556
557 G_ATTRIBUTE_USED(G_ATTR_ITEM) := TRUE;
558 G_ATTRIBUTE_USED(G_ATTR_ITEM_CATEGORY) := TRUE;
559 G_ATTRIBUTE_USED(G_ATTR_PO_NUMBER) := TRUE;
560 G_ATTRIBUTE_USED(G_ATTR_AGREEMENT_TYPE) := TRUE;
561 G_ATTRIBUTE_USED(G_ATTR_AGREEMENT) := TRUE;
562 G_ATTRIBUTE_USED(G_ATTR_ORDER_TYPE_ID) := TRUE;
563 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE1) := TRUE;
564 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE2) := TRUE;
565 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE3) := TRUE;
566 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE4) := TRUE;
567 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE5) := TRUE;
568 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE6) := TRUE;
569 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE7) := TRUE;
570 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE8) := TRUE;
571 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE9) := TRUE;
572 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE10) := TRUE;
573 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE11) := TRUE;
574 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE12) := TRUE;
575 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE13) := TRUE;
576 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE14) := TRUE;
577 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE15) := TRUE;
578
579 ELSE
580
581 -- Default all pricing attributes used flags to FALSE
582
583 G_ATTRIBUTE_USED(G_ATTR_ITEM) := TRUE;
584 G_ATTRIBUTE_USED(G_ATTR_ITEM_CATEGORY) := TRUE;
585 G_ATTRIBUTE_USED(G_ATTR_PO_NUMBER) := TRUE;
586 G_ATTRIBUTE_USED(G_ATTR_AGREEMENT_TYPE) := TRUE;
587 G_ATTRIBUTE_USED(G_ATTR_AGREEMENT) := TRUE;
588 G_ATTRIBUTE_USED(G_ATTR_ORDER_TYPE_ID) := TRUE;
589 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE1) := TRUE;
590 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE2) := TRUE;
591 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE3) := TRUE;
592 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE4) := TRUE;
593 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE5) := TRUE;
594 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE6) := TRUE;
595 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE7) := TRUE;
596 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE8) := TRUE;
597 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE9) := TRUE;
598 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE10) := TRUE;
599 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE11) := TRUE;
600 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE12) := TRUE;
601 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE13) := TRUE;
602 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE14) := TRUE;
603 G_ATTRIBUTE_USED(G_ATTR_PRC_ATTRIBUTE15) := TRUE;
604
605 -- Fetch used entity IDs from oe_discount_lines
606 /*
607 OPEN used_entities;
608 LOOP
609 FETCH used_entities INTO l_entity_id;
610 EXIT WHEN used_entities%NOTFOUND; --QP CHANGE TO CONVERT PRICING_ATTRIBUTEX TO NUMBER
611 G_ATTRIBUTE_USED(QP_UTIL.GET_ENTITYVALUE(l_entity_id)) := TRUE;
612 END LOOP; -- CALL QP_UTIL FUNCTION TO DO THAT
613 CLOSE used_entities; -- QP END
614 */
615 END IF;
616
617 END IF;
618
619 oe_debug_pub.add('Exiting QP_PRC_UTIL.ATTRIBUTE_USED', 1);
620
621 RETURN G_ATTRIBUTE_USED(p_attribute_id);
622
623 EXCEPTION
624
625 WHEN OTHERS THEN
626
627 -- Unexpected error
628 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
629
630 OE_MSG_PUB.Add_Exc_Msg
631 ( G_PKG_NAME ,
632 'Price_Item - Attribute_Used'
633 );
634 END IF;
635
636 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
637
638 END; -- Attribute_Used
639
640
641
642 -- Procedure Get_Adjustment
643 -- Usage:
644 -- Get_Adjustment is called multiple times from the
645 -- Fetch_Best_Adjustment procedure, once for each attribute that
646 -- affects pricing.
647 -- Description:
648 -- This procedure executes a query against OE_DISCOUNTS and
649 -- OE_DISCOUNT_LINES to fetch the best discount available.
650 --
651 -- The SQL statment is restricted by percent > l_adj_percent to
652 -- ensure that we only fetch adjustments higher than we already have.
653 --
654 -- The procedure executes one of 2 queries based on the
655 -- p_attribute_id passed to it. If p_attribute_id is null, it only
656 -- queries OE_DISCOUNTS, otherwise it queries against OE_DISCOUNTS and
657 -- OE_DISCOUNT_LINES.
658
659 PROCEDURE Get_Adjustment
660 ( p_best_adj_rec IN Adj_Short_Rec_Type ,
661 p_best_adj_percent IN NUMBER ,
662 p_attribute_id IN NUMBER ,
663 p_attribute_value IN VARCHAR2 ,
664 p_price_list_id IN NUMBER ,
665 p_quantity IN NUMBER ,
666 p_list_price IN NUMBER ,
667 p_sold_to_org_id IN NUMBER ,
668 p_customer_class_code IN VARCHAR2 ,
669 p_gsa IN VARCHAR2 ,
670 p_ship_to_id IN NUMBER ,
671 p_invoice_to_id IN NUMBER ,
672 p_unit_code IN VARCHAR2 ,
673 p_adj_rec OUT NOCOPY /* file.sql.39 change */ Adj_Short_Rec_Type ,
674 p_adj_percent OUT NOCOPY /* file.sql.39 change */ NUMBER
675 )
676
677
678
679 IS
680
681 TYPE discount_cursor IS ref CURSOR;
682 TYPE discount_line_cursor IS ref CURSOR;
683
684 l_discount_csr discount_cursor;
685 l_discount_lines_csr discount_line_cursor;
686
687 l_discounting_type VARCHAR2(30) := NULL;
688 l_pricing_date DATE;
689
690 l_adj_rec Adj_Short_Rec_Type := p_best_adj_rec;
691 l_qp_discount_line_rec QP_PRICING_ENGINE_PVT.l_discount_line_rec;
692
693 BEGIN
694
695 oe_debug_pub.add('Entering QP_PRC_UTIL.GET_ADJUSTMENT', 1);
696
697
698
699 IF p_list_price = 0 THEN
700
701 -- list price is zero. Same behavior as if no adjustments
702 -- were fetched.
703
704 l_adj_rec := p_best_adj_rec ;
705 p_adj_percent := l_adj_rec.percent;
706
707 RETURN;
708 END IF;
709
710
711 l_discounting_type := FND_PROFILE.VALUE ('OE_DISCOUNTING_TYPE');
712
713
714 -- Get pricing_date
715 l_pricing_date := Nvl(l_adj_rec.pricing_date, Sysdate);
716
717
718 IF p_attribute_id IS NULL THEN
719
720 -- Perform fetch without hitting discount lines
721
722 IF l_discounting_type = 'LOW' THEN
723
724
725
726 --DBMS_OUTPUT.PUT_LINE('+================================+');
727 --DBMS_OUTPUT.PUT_LINE('|IN Big select statement --Header| ');
728 --DBMS_OUTPUT.PUT_LINE('+================================+');
729 --DBMS_OUTPUT.PUT_LINE('Discounting type is low--ASC');
730 --DBMS_OUTPUT.PUT_LINE('p_gsa : '||p_gsa);
731 --DBMS_OUTPUT.PUT_LINE('l_pricing_date : '||l_pricing_date);
732 OE_DEBUG_PUB.ADD('+================================+');
733 OE_DEBUG_PUB.ADD('|IN Big select statement --Header| ');
734 OE_DEBUG_PUB.ADD('+================================+');
735 OE_DEBUG_PUB.ADD('Discounting type is low--ASC');
736 OE_DEBUG_PUB.ADD('p_gsa : '||p_gsa);
737 OE_DEBUG_PUB.ADD('l_pricing_date : '||l_pricing_date);
738
739
740
741 OPEN l_discount_csr FOR
742 SELECT QPH.LIST_HEADER_ID, QPL.LIST_LINE_ID, QPH.NAME,
743 DECODE(QPL.ARITHMETIC_OPERATOR,'AMT',NVL(QPL.OPERAND/p_list_price * 100,0),
744 '%',NVL(QPL.OPERAND,0))
745 FROM QP_LIST_HEADERS QPH , QP_LIST_LINES QPL , QP_QUALIFIERS QPQ
746 WHERE QPH.LIST_HEADER_ID = QPL.LIST_HEADER_ID
747 AND QPH.LIST_HEADER_ID = QPQ.LIST_HEADER_ID
748 AND QPQ.QUALIFIER_CONTEXT = G_PRICE_LIST_CONTEXT
749 AND QPQ.QUALIFIER_ATTRIBUTE =G_PRICE_LIST_NAME
750 AND QPQ.QUALIFIER_ATTR_VALUE = p_price_list_id
751 AND QPH.AUTOMATIC_FLAG = 'Y'
752 AND QPH.DISCOUNT_LINES_FLAG = 'N'
753 AND (p_gsa = 'Y'
754 OR NVL(QPH.GSA_INDICATOR,'N') = 'N')
755 AND TRUNC(l_pricing_date) BETWEEN NVL(QPH.START_DATE_ACTIVE, TRUNC(l_pricing_date))
756 AND NVL(QPH.END_DATE_ACTIVE, l_pricing_date)
757 AND DECODE(QPL.ARITHMETIC_OPERATOR,'AMT',NVL(QPL.OPERAND/p_list_price * 100,0),
758 '%', NVL(QPL.OPERAND,0)) < p_best_adj_percent
759 AND ( NOT EXISTS (SELECT NULL
760 FROM QP_QUALIFIERS QPQ
761 WHERE QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID
762 AND QPQ.QUALIFIER_CONTEXT = G_CUSTOMER_CLASS_CONTEXT
763 AND QPQ.QUALIFIER_ATTRIBUTE IN (G_CUSTOMER_CLASS_NAME
764 ,G_SOLD_TO_ORG_NAME
765 ,G_SITE_ORG_NAME))
766 OR
767 ( EXISTS(SELECT NULL
768 FROM QP_QUALIFIERS QPQ
769 WHERE QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID
770 AND (QPQ.QUALIFIER_CONTEXT = G_SOLD_TO_ORG_CONTEXT
771 AND QPQ.QUALIFIER_ATTRIBUTE = G_SOLD_TO_ORG_NAME
772 AND NVL(QPQ.QUALIFIER_ATTR_VALUE,p_sold_to_org_id) = p_sold_to_org_id))
773 OR EXISTS(SELECT NULL
774 FROM QP_QUALIFIERS QPQ
775 WHERE QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID
776 AND (qpq.qualifier_context = G_CUSTOMER_CLASS_CONTEXT
777 AND QPQ.QUALIFIER_ATTRIBUTE = G_CUSTOMER_CLASS_NAME
778 AND NVL(QPQ.QUALIFIER_ATTR_VALUE,p_customer_class_code) =
779 p_customer_class_code))
780 AND EXISTS(SELECT NULL
781 FROM QP_QUALIFIERS QPQ
782 WHERE (QPQ.QUALIFIER_CONTEXT = G_CUSTOMER_CLASS_CONTEXT
783 AND QPQ.QUALIFIER_ATTRIBUTE = G_SITE_ORG_NAME
784 AND (NVL(QPQ.QUALIFIER_ATTR_VALUE,p_ship_to_id) = p_ship_to_id
785 OR NVL(QPQ.QUALIFIER_ATTR_VALUE,p_invoice_to_id) = p_invoice_to_id)))))
786 AND TRUNC(l_pricing_date) BETWEEN NVL(QPH.START_DATE_ACTIVE, TRUNC(l_pricing_date))
787 AND NVL(QPH.END_DATE_ACTIVE, l_pricing_date)
788 ORDER BY DECODE(QPL.ARITHMETIC_OPERATOR,'AMT',nvl(QPL.OPERAND/p_list_price * 100,0),
789 '%', NVL(QPL.OPERAND,0)) ASC;
790 --QP END above is the case to get lowest discount
791
792 ELSE
793
794
795 --NEW SQL
796 --DBMS_OUTPUT.PUT_LINE('+================================+');
797 --DBMS_OUTPUT.PUT_LINE('|IN Big select statement --Header| ');
798 --DBMS_OUTPUT.PUT_LINE('+================================+');
799 --DBMS_OUTPUT.PUT_LINE('Discounting type is low--DESC');
800 --DBMS_OUTPUT.PUT_LINE('p_gsa : '||p_gsa);
801 --DBMS_OUTPUT.PUT_LINE('l_pricing_date : '||l_pricing_date);
802 OE_DEBUG_PUB.ADD('+================================+');
803 OE_DEBUG_PUB.ADD('|IN Big select statement --Header| ');
804 OE_DEBUG_PUB.ADD('+================================+');
805 OE_DEBUG_PUB.ADD('Discounting type is low--DESC');
806 OE_DEBUG_PUB.ADD('p_gsa : '||p_gsa);
807 OE_DEBUG_PUB.ADD('l_pricing_date : '||l_pricing_date);
808
809 OPEN l_discount_csr FOR
810 SELECT QPH.LIST_HEADER_ID,QPL.LIST_LINE_ID, QPH.NAME,
811 DECODE(QPL.ARITHMETIC_OPERATOR,'AMT',NVL(QPL.OPERAND/p_list_price * 100,0),
812 '%',NVL(QPL.OPERAND,0))
813 FROM QP_LIST_HEADERS QPH , QP_LIST_LINES QPL , QP_QUALIFIERS QPQ
814 WHERE QPH.LIST_HEADER_ID = QPL.LIST_HEADER_ID
815 AND QPH.LIST_HEADER_ID = QPQ.LIST_HEADER_ID
816 AND QPQ.QUALIFIER_CONTEXT = G_PRICE_LIST_CONTEXT
817 AND QPQ.QUALIFIER_ATTRIBUTE =G_PRICE_LIST_NAME
818 AND QPQ.QUALIFIER_ATTR_VALUE = p_price_list_id
819 AND QPH.AUTOMATIC_FLAG = 'Y'
820 AND QPH.DISCOUNT_LINES_FLAG = 'N'
821 AND (p_gsa = 'Y'
822 OR NVL(QPH.GSA_INDICATOR,'N') = 'N')
823 AND TRUNC(l_pricing_date) BETWEEN NVL(QPH.START_DATE_ACTIVE, TRUNC(l_pricing_date))
824 AND NVL(QPH.END_DATE_ACTIVE, l_pricing_date)
825 AND DECODE(QPL.ARITHMETIC_OPERATOR,'AMT',NVL(QPL.OPERAND/p_list_price * 100,0),
826 '%', NVL(QPL.OPERAND,0)) > p_best_adj_percent
827 AND ( NOT EXISTS (SELECT NULL
828 FROM QP_QUALIFIERS QPQ
829 WHERE QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID
830 AND QPQ.QUALIFIER_CONTEXT = G_CUSTOMER_CLASS_CONTEXT
831 AND QPQ.QUALIFIER_ATTRIBUTE IN (G_CUSTOMER_CLASS_NAME
832 ,G_SOLD_TO_ORG_NAME
833 ,G_SITE_ORG_NAME))
834 OR
835 ( EXISTS(SELECT NULL
836 FROM QP_QUALIFIERS QPQ
837 WHERE QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID
838 AND (QPQ.QUALIFIER_CONTEXT = G_SOLD_TO_ORG_CONTEXT
839 AND QPQ.QUALIFIER_ATTRIBUTE = G_SOLD_TO_ORG_NAME
840 AND NVL(QPQ.QUALIFIER_ATTR_VALUE,p_sold_to_org_id) = p_sold_to_org_id))
841 AND EXISTS(SELECT NULL
842 FROM QP_QUALIFIERS QPQ
843 WHERE QPQ.LIST_HEADER_ID = QPH.LIST_HEADER_ID
844 AND (qpq.qualifier_context = G_CUSTOMER_CLASS_CONTEXT
845 AND QPQ.QUALIFIER_ATTRIBUTE = G_CUSTOMER_CLASS_NAME
846 AND NVL(QPQ.QUALIFIER_ATTR_VALUE,
847 NVL(p_customer_class_code,'NULL')) =
848 NVL(p_customer_class_code,'NULL') ))
849 AND EXISTS(SELECT NULL
850 FROM QP_QUALIFIERS QPQ
851 WHERE QPQ.QUALIFIER_CONTEXT = G_CUSTOMER_CLASS_CONTEXT
852 AND QPQ.QUALIFIER_ATTRIBUTE = G_SITE_ORG_NAME
853 AND (NVL(QPQ.QUALIFIER_ATTR_VALUE,p_ship_to_id) = p_ship_to_id
854 OR NVL(QPQ.QUALIFIER_ATTR_VALUE,p_invoice_to_id) = p_invoice_to_id
855 )
856 )
857 )
858 )
859
860 AND TRUNC(l_pricing_date) BETWEEN NVL(QPH.START_DATE_ACTIVE, TRUNC(l_pricing_date))
861 AND NVL(QPH.END_DATE_ACTIVE, l_pricing_date)
862 ORDER BY DECODE(QPL.ARITHMETIC_OPERATOR,'AMT',nvl(QPL.OPERAND/p_list_price * 100,0),
863 '%', NVL(QPL.OPERAND,0)) DESC;
864 END IF;
865
866
867 -- Fetch the first discount.
868
869 FETCH l_discount_csr INTO
870
871 l_adj_rec.discount_id ,
872 l_adj_rec.discount_line_id,
873 l_adj_rec.discount_name ,
874 l_adj_rec.percent ;
875
876 IF l_discount_csr%NOTFOUND THEN
877 --DBMS_output.put_line('Got the Discount');
878 p_adj_rec := p_best_adj_rec;
879 p_adj_percent := p_best_adj_percent;
880
881 ELSE
882 --DBMS_output.put_line('Did not get the Discount');
883
884 p_adj_rec := l_adj_rec;
885 p_adj_percent := l_adj_rec.percent;
886
887 END IF;
888
889 CLOSE l_discount_csr;
890
891 ELSE
892
893 -- Perform fetch hitting discount lines
894
895 IF l_discounting_type = 'LOW' THEN
896
897 --QP following SQL is commented out for new datamodel
898 --it is replaced by a sigle procedure call
899 /*
900 OPEN l_discount_lines_csr FOR
901 SELECT
902 ORDERED
903 INDEX(OEDLN OE_DISCOUNT_LINE_N2)
904 INDEX(OEDIS OE_DISCOUNTS_U1)
905 OEDIS.DISCOUNT_ID,
906 OEDIS.NAME,
907 NVL( OEDLN.DISCOUNT_LINE_ID, -1 ),
908 NVL( OEDIS.AMOUNT / p_list_price * 100,
909 NVL( OEDIS.PERCENT,
910 NVL( ( p_list_price - OEDLN.PRICE ) / p_list_price * 100,
911 NVL( OEDLN.AMOUNT / p_list_price * 100,
912 NVL( OEDLN.PERCENT,
913 NVL( ( p_list_price - OEPBL.PRICE ) / p_list_price * 100,
914 NVL( OEPBL.AMOUNT / p_list_price * 100,
915 NVL( OEPBL.PERCENT, 0 ) ) ) ) ) ) ) )
916 FROM OE_DISCOUNT_LINES OEDLN
917 , OE_DISCOUNTS OEDIS
918 , OE_PRICE_BREAK_LINES OEPBL
919 WHERE OEDLN.ENTITY_ID = p_attribute_id
920 AND OEDLN.ENTITY_VALUE = p_attribute_value
921 AND TRUNC(L_PRICING_DATE) BETWEEN
922 NVL( OEDLN.START_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
923 AND NVL( OEDLN.END_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
924 AND OEDIS.DISCOUNT_ID = OEDLN.DISCOUNT_ID
925 AND OEDIS.PRICE_LIST_ID = p_price_list_id
926 AND OEDIS.AUTOMATIC_DISCOUNT_FLAG = 'Y'
927 AND ( p_gsa = 'Y'
928 OR NVL( OEDIS.GSA_INDICATOR, 'N' ) = 'N' )
929 AND TRUNC(L_PRICING_DATE)
930 BETWEEN NVL( OEDIS.START_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
931 AND NVL( OEDIS.END_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
932 AND OEPBL.DISCOUNT_LINE_ID (+) = OEDLN.DISCOUNT_LINE_ID
933 AND TRUNC(L_PRICING_DATE)
934 BETWEEN NVL( OEPBL.START_DATE_ACTIVE (+), TRUNC(L_PRICING_DATE) )
935 AND NVL( OEPBL.END_DATE_ACTIVE (+), TRUNC(L_PRICING_DATE) )
936 AND DECODE( OEPBL.METHOD_TYPE_CODE (+),
937 'UNITS', p_quantity,
938 'DOLLARS', p_quantity * p_list_price,
939 0 )
940 BETWEEN NVL( OEPBL.PRICE_BREAK_LINES_LOW_RANGE (+),
941 DECODE( OEPBL.METHOD_TYPE_CODE (+),
942 'UNITS', p_quantity,
943 'DOLLARS', p_quantity * p_list_price,
944 0 ) )
945 AND NVL( OEPBL.PRICE_BREAK_LINES_HIGH_RANGE (+),
946 DECODE( OEPBL.METHOD_TYPE_CODE (+),
947 'UNITS', p_quantity,
948 'DOLLARS', p_quantity * p_list_price,
949 0 ) )
950 AND NVL(OEPBL.UNIT_CODE(+),NVL(p_unit_code,'NULL'))=
951 NVL(p_unit_code,'NULL')
952 AND NVL( OEDIS.AMOUNT / p_list_price * 100,
953 NVL( OEDIS.PERCENT,
954 NVL( ( p_list_price - OEDLN.PRICE ) / p_list_price * 100,
955 NVL( OEDLN.AMOUNT / p_list_price * 100,
956 NVL( OEDLN.PERCENT,
957 NVL( ( p_list_price - OEPBL.PRICE ) / p_list_price * 100,
958 NVL( OEPBL.AMOUNT / p_list_price * 100,
959 NVL( OEPBL.PERCENT , 0 ) ) ) ) ) ) ) ) < p_best_adj_percent
960 AND ( NOT EXISTS (SELECT NULL
961 FROM OE_DISCOUNT_CUSTOMERS OECST
962 WHERE OECST.DISCOUNT_ID = OEDIS.DISCOUNT_ID )
963 OR EXISTS (SELECT NULL
964 FROM OE_DISCOUNT_CUSTOMERS OECST
965 WHERE OECST.DISCOUNT_ID = OEDIS.DISCOUNT_ID
966 AND NVL( OECST.sold_to_org_id, p_sold_to_org_id ) =
967 p_sold_to_org_id
968 AND NVL( OECST.CUSTOMER_CLASS_CODE,
969 NVL ( p_customer_class_code, 'NULL'
970 ) )=
971 NVL ( p_customer_class_code , 'NULL' )
972 AND ( NVL( OECST.site_org_id, p_ship_to_id) =
973 p_ship_to_id
974 OR
975 NVL( OECST.site_org_id, p_invoice_to_id)
976 = p_invoice_to_id )
977 AND TRUNC(L_PRICING_DATE)
978 BETWEEN NVL( OECST.START_DATE_ACTIVE,
979 TRUNC(L_PRICING_DATE))
980 AND NVL( OECST.END_DATE_ACTIVE,
981 TRUNC(L_PRICING_DATE))))
982 ORDER BY NVL( OEDIS.AMOUNT / p_list_price * 100,
983 NVL( OEDIS.PERCENT,
984 NVL( ( p_list_price - OEDLN.PRICE ) / p_list_price * 100,
985 NVL( OEDLN.AMOUNT / p_list_price * 100,
986 NVL( OEDLN.PERCENT,
987 NVL( ( p_list_price - OEPBL.PRICE ) / p_list_price * 100,
988 NVL( OEPBL.AMOUNT / p_list_price * 100,
989 NVL( OEPBL.PERCENT, 0 ) ) ) ) ) ) ) ) ASC;
990 */
991
992 -- Call QP_PRICING_ENGINE to perform above SQL operation
993 /*DBMS_output.put_line('list '||p_price_list_id||' qty '||p_quantity
994 ||'attr'||p_attribute_id||' valu '||p_attribute_value
995 ||'date '||l_pricing_date);*/
996
997 QP_PRICING_ENGINE_PVT.GET_DISCOUNT_LINES(p_price_list_id
998 ,p_list_price
999 ,p_quantity
1000 ,p_unit_code
1001 ,p_attribute_id
1002 ,p_attribute_value
1003 ,l_pricing_date
1004 ,p_customer_class_code
1005 ,p_sold_to_org_id
1006 ,p_ship_to_id
1007 ,p_invoice_to_id
1008 ,p_best_adj_percent
1009 ,p_gsa
1010 ,'A' --A for ascending
1011 ,l_qp_discount_line_rec);
1012
1013
1014
1015 ELSE
1016
1017 /*
1018 OPEN l_discount_lines_csr FOR
1019 SELECT
1020 ORDERED
1021 INDEX(OEDLN OE_DISCOUNT_LINE_N2)
1022 INDEX(OEDIS OE_DISCOUNTS_U1)
1023 OEDIS.DISCOUNT_ID,
1024 OEDIS.NAME,
1025 NVL( OEDLN.DISCOUNT_LINE_ID, -1 ),
1026 NVL( OEDIS.AMOUNT / p_list_price * 100,
1027 NVL( OEDIS.PERCENT,
1028 NVL( ( p_list_price - OEDLN.PRICE ) / p_list_price * 100,
1029 NVL( OEDLN.AMOUNT / p_list_price * 100,
1030 NVL( OEDLN.PERCENT,
1031 NVL( ( p_list_price - OEPBL.PRICE ) / p_list_price * 100,
1032 NVL( OEPBL.AMOUNT / p_list_price * 100,
1033 NVL( OEPBL.PERCENT, 0 ) ) ) ) ) ) ) )
1034 FROM OE_DISCOUNT_LINES OEDLN
1035 , OE_DISCOUNTS OEDIS
1036 , OE_PRICE_BREAK_LINES OEPBL
1037 WHERE OEDLN.ENTITY_ID = p_attribute_id
1038 AND OEDLN.ENTITY_VALUE = p_attribute_value
1039 AND TRUNC(L_PRICING_DATE) BETWEEN
1040 NVL( OEDLN.START_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
1041 AND NVL( OEDLN.END_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
1042 AND OEDIS.DISCOUNT_ID = OEDLN.DISCOUNT_ID
1043 AND OEDIS.PRICE_LIST_ID = p_price_list_id
1044 AND OEDIS.AUTOMATIC_DISCOUNT_FLAG = 'Y'
1045 AND ( p_gsa = 'Y'
1046 OR NVL( OEDIS.GSA_INDICATOR, 'N' ) = 'N' )
1047 AND TRUNC(L_PRICING_DATE)
1048 BETWEEN NVL( OEDIS.START_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
1049 AND NVL( OEDIS.END_DATE_ACTIVE, TRUNC(L_PRICING_DATE) )
1050 AND OEPBL.DISCOUNT_LINE_ID (+) = OEDLN.DISCOUNT_LINE_ID
1051 AND TRUNC(L_PRICING_DATE)
1052 BETWEEN NVL( OEPBL.START_DATE_ACTIVE (+), TRUNC(L_PRICING_DATE) )
1053 AND NVL( OEPBL.END_DATE_ACTIVE (+), TRUNC(L_PRICING_DATE) )
1054 AND DECODE( OEPBL.METHOD_TYPE_CODE (+),
1055 'UNITS', p_quantity,
1056 'DOLLARS', p_quantity * p_list_price,
1057 0 )
1058 BETWEEN NVL( OEPBL.PRICE_BREAK_LINES_LOW_RANGE (+),
1059 DECODE( OEPBL.METHOD_TYPE_CODE (+),
1060 'UNITS', p_quantity,
1061 'DOLLARS', p_quantity * p_list_price,
1062 0 ) )
1063 AND NVL( OEPBL.PRICE_BREAK_LINES_HIGH_RANGE (+),
1064 DECODE( OEPBL.METHOD_TYPE_CODE (+),
1065 'UNITS', p_quantity,
1066 'DOLLARS', p_quantity * p_list_price,
1067 0 ) )
1068 AND NVL(OEPBL.UNIT_CODE(+),NVL(p_unit_code,'NULL'))=
1069 NVL(p_unit_code,'NULL')
1070 AND NVL( OEDIS.AMOUNT / p_list_price * 100,
1071 NVL( OEDIS.PERCENT,
1072 NVL( ( p_list_price - OEDLN.PRICE ) / p_list_price * 100,
1073 NVL( OEDLN.AMOUNT / p_list_price * 100,
1074 NVL( OEDLN.PERCENT,
1075 NVL( ( p_list_price - OEPBL.PRICE ) / p_list_price * 100,
1076 NVL( OEPBL.AMOUNT / p_list_price * 100,
1077 NVL( OEPBL.PERCENT , 0 ) ) ) ) ) ) ) ) > p_best_adj_percent
1078 AND ( NOT EXISTS (SELECT NULL
1079 FROM OE_DISCOUNT_CUSTOMERS OECST
1080 WHERE OECST.DISCOUNT_ID = OEDIS.DISCOUNT_ID )
1081 OR EXISTS (SELECT NULL
1082 FROM OE_DISCOUNT_CUSTOMERS OECST
1083 WHERE OECST.DISCOUNT_ID = OEDIS.DISCOUNT_ID
1084 AND NVL( OECST.sold_to_org_id, p_sold_to_org_id ) =
1085 p_sold_to_org_id
1086 AND NVL( OECST.CUSTOMER_CLASS_CODE,
1087 NVL ( p_customer_class_code, 'NULL'
1088 ) )=
1089 NVL ( p_customer_class_code , 'NULL' )
1090 AND ( NVL( OECST.site_org_id, p_ship_to_id) =
1091 p_ship_to_id
1092 OR
1093 NVL( OECST.site_org_id, p_invoice_to_id)
1094 = p_invoice_to_id )
1095 AND TRUNC(L_PRICING_DATE)
1096 BETWEEN NVL( OECST.START_DATE_ACTIVE,
1097 TRUNC(L_PRICING_DATE))
1098 AND NVL( OECST.END_DATE_ACTIVE,
1099 TRUNC(L_PRICING_DATE))))
1100 ORDER BY NVL( OEDIS.AMOUNT / p_list_price * 100,
1101 NVL( OEDIS.PERCENT,
1102 NVL( ( p_list_price - OEDLN.PRICE ) / p_list_price * 100,
1103 NVL( OEDLN.AMOUNT / p_list_price * 100,
1104 NVL( OEDLN.PERCENT,
1105 NVL( ( p_list_price - OEPBL.PRICE ) / p_list_price * 100,
1106 NVL( OEPBL.AMOUNT / p_list_price * 100,
1107 NVL( OEPBL.PERCENT, 0 ) ) ) ) ) ) ) ) DESC;
1108 */
1109
1110 --DBMS_output.put_line('IN QPXUPRCB--BEFORE QP_PRICING_ENGINE_PVT');
1111 --DBMS_output.put_line('p_price_list_id: '|| p_price_list_id);
1112 --DBMS_output.put_line('p_quantity: '||p_quantity);
1113 --DBMS_output.put_line('p_unit_code: ' || p_unit_code);
1114 --DBMS_output.put_line('p_attribute_id: '||p_attribute_id);
1115 --DBMS_output.put_line('p_attribute_value: '|| p_attribute_value);
1116 --DBMS_output.put_line('l_pricing_date: '||l_pricing_date);
1117 --DBMS_output.put_line('p_customer_class_code: '||p_customer_class_code);
1118 --DBMS_output.put_line('p_sold_to_org_id: '||p_sold_to_org_id);
1119 --DBMS_output.put_line('p_ship_to_id: '||p_ship_to_id);
1120 --DBMS_output.put_line('p_invoice_to_id: '||p_invoice_to_id);
1121 --DBMS_output.put_line('p_best_adj_percent: '||p_best_adj_percent);
1122
1123 QP_PRICING_ENGINE_PVT.GET_DISCOUNT_LINES(p_price_list_id
1124 ,p_list_price
1125 ,p_quantity
1126 ,p_unit_code
1127 ,p_attribute_id
1128 ,p_attribute_value
1129 ,l_pricing_date
1130 ,p_customer_class_code
1131 ,p_sold_to_org_id
1132 ,p_ship_to_id
1133 ,p_invoice_to_id
1134 ,nvl(p_best_adj_percent,0)
1135 ,'Y'
1136 ,'D' --D for descending
1137 ,l_qp_discount_line_rec);
1138
1139
1140 --DBMS_output.put_line('IN QPXUPRCB--AFTER QP_PRICING_ENGINE_PVT');
1141
1142 END IF;
1143
1144
1145 -- comment out following statement
1146 -- Fetch the first discount line.
1147 /*
1148 FETCH l_discount_lines_csr INTO
1149
1150 l_adj_rec.discount_id ,
1151 l_adj_rec.discount_name ,
1152 l_adj_rec.discount_line_id ,
1153 l_adj_rec.percent ;
1154 */
1155 --above statement replaced by following statements
1156 l_adj_rec.discount_id := l_qp_discount_line_rec.p_discount_id;
1157 l_adj_rec.discount_name := l_qp_discount_line_rec.p_discount_name;
1158 l_adj_rec.discount_line_id := l_qp_discount_line_rec.p_discount_line_id;
1159 l_adj_rec.percent := l_qp_discount_line_rec.p_discount_percent;
1160 --DBMS_output.put_line('New discount_id: ' ||l_qp_discount_line_rec.p_discount_id);
1161 --DBMS_output.put_line('New percent: '||l_qp_discount_line_rec.p_discount_percent);
1162
1163 --QP END
1164
1165 -- Debug info
1166 /*
1167 OE_MSG_PUB.Add_Exc_Msg
1168 ( p_error_text => 'In Get Adjustment - After fetch - '||
1169 ' p_attr_id = '||p_attribute_id||
1170 ' p_attr_value = '||p_attribute_value||
1171 ' p_disc_name = '||l_adj_rec.discount_name||
1172 ' percent = '||l_adj_rec.percent
1173 );
1174 */
1175
1176 --QP
1177 --DBMS_output.put_line('Old discount_id: ' ||p_adj_rec.discount_id);
1178 --DBMS_output.put_line('Old percent: '||p_adj_rec.percent);
1179
1180 IF l_adj_rec.percent IS NOT null THEN
1181 --DBMS_output.put_line('Old kills me: ');
1182 p_adj_rec := l_adj_rec;
1183 p_adj_percent := l_adj_rec.percent;
1184 ELSE
1185 p_adj_rec := p_best_adj_rec;
1186 p_adj_percent := p_best_adj_percent;
1187 END IF;
1188
1189 --QP END
1190
1191
1192 END IF;
1193
1194 oe_debug_pub.add('Exiting QP_PRC_UTIL.GET_ADJUSTMENT', 1);
1195
1196 EXCEPTION
1197
1198 WHEN OTHERS THEN
1199
1200 -- Unexpected error
1201 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1202
1203 OE_MSG_PUB.Add_Exc_Msg
1204 ( G_PKG_NAME ,
1205 'Price_Item - Get_Adjustment, p_attribute_id = '||p_attribute_id
1206 );
1207 END IF;
1208
1209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1210
1211
1212 END; -- Get_Adjustment
1213
1214 -- Function Get_GSA
1215 -- Usage:
1216 -- Get_GSA is called from Fetch_Best_Adjustment if no value for
1217 -- GSA is provided as a parameter to that procedure.
1218 -- Description:
1219 -- Queries the GSA value from the database according to the customer_id
1220 -- and invoice_to_site_use_id parameters.
1221
1222 FUNCTION Get_GSA (p_sold_to_org_id IN NUMBER,
1223 p_invoice_to_id IN NUMBER) RETURN VARCHAR2
1224 IS
1225 l_GSA VARCHAR2(1) := NULL;
1226 l_error_msg VARCHAR2(240);
1227
1228 BEGIN
1229
1230 oe_debug_pub.add('Entering QP_PRC_UTIL.GET_GSA', 1);
1231
1232 -- If the invoice to is NULL, then the order is automatically not GSA
1233
1234 IF p_invoice_to_id IS NULL THEN
1235
1236 oe_debug_pub.add('Exiting QP_PRC_UTIL.GET_GSA', 1);
1237
1238 RETURN 'N';
1239
1240 ELSE
1241 -- Get invoice to GSA indicator
1242
1243 SELECT GSA_INDICATOR
1244 INTO l_GSA
1245 FROM HZ_CUST_SITE_USES
1246 WHERE SITE_USE_ID = p_invoice_to_id;
1247
1248 l_error_msg := 'Get invoice to GSA indicator';
1249 END IF;
1250
1251
1252 -- If invoice to GSA indicator is NULL, then get
1253 -- get the customer GSA indicator
1254
1255 IF l_GSA IS NULL THEN
1256 IF p_sold_to_org_id IS NOT NULL THEN
1257 SELECT NVL( DECODE(party.PARTY_TYPE,'ORGANIZATION',party.GSA_INDICATOR_FLAG, 'N'),'N' )
1258 INTO l_gsa
1259 FROM HZ_CUST_ACCOUNTS cust_acct,HZ_PARTIES party
1260 WHERE cust_acct.PARTY_ID = party.PARTY_ID and
1261 cust_acct.CUST_ACCOUNT_ID = p_sold_to_org_id;
1262 END IF;
1263
1264 l_error_msg := 'Get the customer GSA indicator';
1265 END IF;
1266
1267
1268 -- If the GSA indicator is NULL, then set it to 'N' (No)
1269
1270 IF l_gsa IS NULL THEN
1271
1272 l_gsa := 'N';
1273
1274 END IF;
1275
1276
1277 -- Return the GSA Value
1278
1279 oe_debug_pub.add('Exiting QP_PRC_UTIL.GET_GSA', 1);
1280
1281 RETURN l_gsa;
1282
1283 EXCEPTION
1284
1285 WHEN OTHERS THEN
1286
1287 -- Unexpected error
1288 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1289
1290 OE_MSG_PUB.Add_Exc_Msg
1291 ( G_PKG_NAME ,
1292 'Price_Item - Get_GSA, section '||l_error_msg
1293 );
1294 END IF;
1295
1296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1297
1298 END; -- Get_GSA
1299
1300
1301
1302 -- Procedure Fetch_Best_Adjustment
1303 -- Usage:
1304 -- Called from Price_Item to return the best available price
1305 -- adjustment available based on the item's criteria.
1306 -- Description:
1307 -- The following attributes are the discounting attributes:
1308 -- p_item_id
1309 -- p_pricing_attribute1 through p_pricing_attribute15
1310 -- p_po_number
1311 -- p_agreement_id
1312 -- p_agreement_type_code
1313 -- p_order_type_id
1314 -- If an attribute is not null and is used as a pricing entity, then
1315 -- we execute a fetch using the attribute as the entity. For
1316 -- performance reasons, a fetch against oe_discount_lines is
1317 -- performed for each attribute (in order to hit the
1318 -- appropriate indexes).
1319 -- We also execute an initial fetch for the best adjustment from
1320 -- discounts that have no discount lines.
1321 --
1322 -- The following attributed are used in the fetch query:
1323 -- p_price_list_id required
1324 -- p_list_price required
1325 -- p_quantity optional
1326 -- p_unit_code optional
1327 -- p_ship_to_id optional
1328 -- p_customer_id optional
1329 -- p_customer_class_code optional
1330 -- p_invoice_to_id optional
1331 -- p_gsa optional
1332 --
1333 -- In order to optimize performance and reduce the number of fetches
1334 -- against oe_discount_lines, a check is performed for each attribute
1335 -- to determine whether it is used as a discounting attribute or not.
1336 -- The function Attribute_Used performs this check.
1337 --
1338 -- The query performed in the Get_Adjustment procedure is restricted
1339 -- by a greater-than clause which compares against the previously
1340 -- queried percent and returns an adjustment only if it is higher in
1341 -- value. The initial value for this variable is 0. If Get_Adjustment
1342 -- fetches no rows, it will pass back the best adjustment record and
1343 -- best adjustment percent in the out parameters p_adj_rec
1344 -- and p_adj_percent.
1345
1346 PROCEDURE Fetch_Best_Adjustment
1347 ( p_inventory_item_id IN NUMBER ,
1348 p_price_list_id IN NUMBER ,
1349 p_list_price IN NUMBER ,
1350 p_quantity IN NUMBER ,
1351 p_pricing_attribute1 IN VARCHAR2 := NULL ,
1352 p_pricing_attribute2 IN VARCHAR2 := NULL ,
1353 p_pricing_attribute3 IN VARCHAR2 := NULL ,
1354 p_pricing_attribute4 IN VARCHAR2 := NULL ,
1355 p_pricing_attribute5 IN VARCHAR2 := NULL ,
1356 p_pricing_attribute6 IN VARCHAR2 := NULL ,
1357 p_pricing_attribute7 IN VARCHAR2 := NULL ,
1358 p_pricing_attribute8 IN VARCHAR2 := NULL ,
1359 p_pricing_attribute9 IN VARCHAR2 := NULL ,
1360 p_pricing_attribute10 IN VARCHAR2 := NULL ,
1361 p_pricing_attribute11 IN VARCHAR2 := NULL ,
1362 p_pricing_attribute12 IN VARCHAR2 := NULL ,
1363 p_pricing_attribute13 IN VARCHAR2 := NULL ,
1364 p_pricing_attribute14 IN VARCHAR2 := NULL ,
1365 p_pricing_attribute15 IN VARCHAR2 := NULL ,
1366 p_pricing_date IN DATE := NULL ,
1367 p_unit_code IN VARCHAR2 := NULL ,
1368 p_ship_to_id IN NUMBER := NULL ,
1369 p_item_category_id IN NUMBER := NULL ,
1370 p_sold_to_org_id IN NUMBER := NULL ,
1371 p_customer_class_code IN VARCHAR2 := NULL ,
1372 p_invoice_to_id IN NUMBER := NULL ,
1373 p_po_number IN VARCHAR2 := NULL ,
1374 p_agreement_id IN NUMBER := NULL ,
1375 p_agreement_type_code IN VARCHAR2 := NULL ,
1376 p_order_type_id IN NUMBER := NULL ,
1377 p_gsa IN VARCHAR2 := NULL ,
1378 p_adj_rec OUT NOCOPY /* file.sql.39 change */ Adj_Short_Rec_Type
1379 )
1380 IS
1381 l_adj_percent NUMBER := 0;
1382 l_gsa VARCHAR2(1);
1383 l_adj_rec Adj_Short_Rec_Type;
1384
1385 BEGIN
1386
1387 oe_debug_pub.add('Entering QP_PRC_UTIL.FETCH_BEST_ADJUSTMENT', 1);
1388
1389 -- Price list and list price are required
1390
1391 IF p_price_list_id IS NULL OR
1392 p_list_price IS NULL OR
1393 p_list_price = 0
1394 THEN
1395 return;
1396 END IF;
1397
1398
1399 -- Get GSA flag
1400
1401 IF p_gsa IS NULL THEN
1402 l_gsa := Get_GSA (p_sold_to_org_id, p_invoice_to_id);
1403 ELSE
1404 l_gsa := p_gsa;
1405 END IF;
1406
1407
1408 -- set pricing_date
1409 l_adj_rec.pricing_date := p_pricing_date;
1410
1411
1412 -- Initial fetch (no pricing entities)
1413 OE_DEBUG_PUB.ADD('+==========================================+');
1414 OE_DEBUG_PUB.ADD('|Befor calling header big select statement|');
1415 OE_DEBUG_PUB.ADD('+==========================================+');
1416 OE_DEBUG_PUB.ADD('l_adj_percent '||l_adj_percent);
1417 OE_DEBUG_PUB.ADD('adjustment id '||l_adj_rec.adjustment_id);
1418 OE_DEBUG_PUB.ADD('discount id '||l_adj_rec.discount_id);
1419 OE_DEBUG_PUB.ADD('discount line id '||l_adj_rec.discount_line_id);
1420 OE_DEBUG_PUB.ADD('automatic discount flag '||l_adj_rec.automatic_flag);
1421 OE_DEBUG_PUB.ADD('percent '|| l_adj_rec.percent);
1422 OE_DEBUG_PUB.ADD('line id '||l_adj_rec.line_id);
1423 OE_DEBUG_PUB.ADD('header id' || l_adj_rec.header_id);
1424 OE_DEBUG_PUB.ADD('discount_name '||l_adj_rec.discount_name);
1425 OE_DEBUG_PUB.ADD('pricing_date '||l_adj_rec.pricing_date);
1426 OE_DEBUG_PUB.ADD('operation '||l_adj_rec.operation);
1427 OE_DEBUG_PUB.ADD('line_tbl_index '||l_adj_rec.line_tbl_index);
1428 OE_DEBUG_PUB.ADD('p_price_list_id '||p_price_list_id);
1429 OE_DEBUG_PUB.ADD('p_quantity '||p_quantity);
1430 OE_DEBUG_PUB.ADD('p_list_price '||p_list_price);
1431 OE_DEBUG_PUB.ADD('p_sold_to_org_id '||p_sold_to_org_id);
1432 OE_DEBUG_PUB.ADD('p_customer_class_code '||p_customer_class_code);
1433 OE_DEBUG_PUB.ADD('p_ship_to_id '||p_ship_to_id );
1434 OE_DEBUG_PUB.ADD('p_invoice_to_id '||p_invoice_to_id);
1435 OE_DEBUG_PUB.ADD('p_unit_code '||p_unit_code );
1436
1437
1438 --DBMS_OUTPUT.PUT_LINE('+==========================================+');
1439 --DBMS_OUTPUT.PUT_LINE('|Before calling header big select statement|');
1440 --DBMS_OUTPUT.PUT_LINE('+==========================================+');
1441 --DBMS_OUTPUT.PUT_LINE('l_adj_percent '||l_adj_percent);
1442 --DBMS_OUTPUT.PUT_LINE('adjustment id '||l_adj_rec.adjustment_id);
1443 --DBMS_OUTPUT.PUT_LINE('discount id '||l_adj_rec.discount_id);
1444 --DBMS_OUTPUT.PUT_LINE('discount line id '||l_adj_rec.discount_line_id);
1445 --DBMS_OUTPUT.PUT_LINE('automatic discount flag '||l_adj_rec.automatic_flag);
1446 --DBMS_OUTPUT.PUT_LINE('percent '|| l_adj_rec.percent);
1447 --DBMS_OUTPUT.PUT_LINE('line id '||l_adj_rec.line_id);
1448 --DBMS_OUTPUT.PUT_LINE('header id' || l_adj_rec.header_id);
1449 --DBMS_OUTPUT.PUT_LINE('discount_name '||l_adj_rec.discount_name);
1450 --DBMS_OUTPUT.PUT_LINE('pricing_date '||l_adj_rec.pricing_date);
1451 --DBMS_OUTPUT.PUT_LINE('operation '||l_adj_rec.operation);
1452 --DBMS_OUTPUT.PUT_LINE('line_tbl_index '||l_adj_rec.line_tbl_index);
1453 --DBMS_OUTPUT.PUT_LINE('p_price_list_id '||p_price_list_id);
1454 --DBMS_OUTPUT.PUT_LINE('p_quantity '||p_quantity);
1455 --DBMS_OUTPUT.PUT_LINE('p_list_price '||p_list_price);
1456 --DBMS_OUTPUT.PUT_LINE('p_sold_to_org_id '||p_sold_to_org_id);
1457 --DBMS_OUTPUT.PUT_LINE('p_customer_class_code '||p_customer_class_code);
1458 --DBMS_OUTPUT.PUT_LINE('p_ship_to_id '||p_ship_to_id );
1459 --DBMS_OUTPUT.PUT_LINE('p_invoice_to_id '||p_invoice_to_id);
1460 --DBMS_OUTPUT.PUT_LINE('p_unit_code '||p_unit_code );
1461 --l_adj_rec.pricing_date := TO_DATE('13-OCT-99');
1462 Get_Adjustment
1463 ( p_best_adj_rec => l_adj_rec ,
1464 p_best_adj_percent => l_adj_percent ,
1465 p_attribute_id => NULL ,
1466 p_attribute_value => NULL ,
1467 p_price_list_id => p_price_list_id ,
1468 p_quantity => p_quantity ,
1469 p_list_price => p_list_price ,
1470 p_sold_to_org_id => p_sold_to_org_id ,
1471 p_customer_class_code => p_customer_class_code ,
1472 p_gsa => l_gsa ,
1473 p_ship_to_id => p_ship_to_id ,
1474 p_invoice_to_id => p_invoice_to_id ,
1475 p_unit_code => p_unit_code ,
1476 p_adj_rec => l_adj_rec ,
1477 p_adj_percent => l_adj_percent
1478 );
1479
1480 --DBMS_output.put_line('In fetch best adjustment without attributes : '|| p_adj_rec.percent);
1481
1482 OE_DEBUG_PUB.ADD('+==========================================+');
1483 OE_DEBUG_PUB.ADD('|After calling header big select statement|');
1484 OE_DEBUG_PUB.ADD('+==========================================+');
1485 OE_DEBUG_PUB.ADD('l_adj_percent '||l_adj_percent);
1486 OE_DEBUG_PUB.ADD('adjustment id '||l_adj_rec.adjustment_id);
1487 OE_DEBUG_PUB.ADD('discount id '||l_adj_rec.discount_id);
1488 OE_DEBUG_PUB.ADD('discount line id '||l_adj_rec.discount_line_id);
1489 OE_DEBUG_PUB.ADD('automatic discount flag '||l_adj_rec.automatic_flag);
1490 OE_DEBUG_PUB.ADD('percent '|| l_adj_rec.percent);
1491 OE_DEBUG_PUB.ADD('line id '||l_adj_rec.line_id);
1492 OE_DEBUG_PUB.ADD('header id' || l_adj_rec.header_id);
1493 OE_DEBUG_PUB.ADD('discount_name '||l_adj_rec.discount_name);
1494 OE_DEBUG_PUB.ADD('pricing_date '||l_adj_rec.pricing_date);
1495 OE_DEBUG_PUB.ADD('operation '||l_adj_rec.operation);
1496 OE_DEBUG_PUB.ADD('line_tbl_index '||l_adj_rec.line_tbl_index);
1497
1498 --DBMS_OUTPUT.PUT_LINE('+==========================================+');
1499 --DBMS_OUTPUT.PUT_LINE('|After calling header big select statement|');
1500 --DBMS_OUTPUT.PUT_LINE('+==========================================+');
1501 --DBMS_OUTPUT.PUT_LINE('l_adj_percent '||l_adj_percent);
1502 --DBMS_OUTPUT.PUT_LINE('adjustment id '||l_adj_rec.adjustment_id);
1503 --DBMS_OUTPUT.PUT_LINE('discount id '||l_adj_rec.discount_id);
1504 --DBMS_OUTPUT.PUT_LINE('discount line id '||l_adj_rec.discount_line_id);
1505 --DBMS_OUTPUT.PUT_LINE('automatic discount flag '||l_adj_rec.automatic_flag);
1506 --DBMS_OUTPUT.PUT_LINE('percent '|| l_adj_rec.percent);
1507 --DBMS_OUTPUT.PUT_LINE('line id '||l_adj_rec.line_id);
1508 --DBMS_OUTPUT.PUT_LINE('header id' || l_adj_rec.header_id);
1509 --DBMS_OUTPUT.PUT_LINE('discount_name '||l_adj_rec.discount_name);
1510 --DBMS_OUTPUT.PUT_LINE('pricing_date '||l_adj_rec.pricing_date);
1511 --DBMS_OUTPUT.PUT_LINE('operation '||l_adj_rec.operation);
1512 --DBMS_OUTPUT.PUT_LINE('line_tbl_index '||l_adj_rec.line_tbl_index);
1513
1514
1515 -- Fetches for each discounting attribute
1516
1517 IF p_inventory_item_id IS NOT NULL THEN
1518
1519 IF Attribute_Used(G_ATTR_ITEM) THEN
1520 Get_Adjustment
1521 ( p_best_adj_rec => l_adj_rec ,
1522 p_best_adj_percent=> l_adj_percent ,
1523 p_attribute_id => G_ATTR_ITEM ,
1524 p_attribute_value => TO_CHAR(p_inventory_item_id) ,
1525 p_price_list_id => p_price_list_id ,
1526 p_quantity => p_quantity ,
1527 p_list_price => p_list_price ,
1528 p_sold_to_org_id => p_sold_to_org_id ,
1529 p_customer_class_code => p_customer_class_code ,
1530 p_gsa => l_gsa ,
1531 p_ship_to_id => p_ship_to_id ,
1532 p_invoice_to_id => p_invoice_to_id ,
1533 p_unit_code => p_unit_code ,
1534 p_adj_rec => l_adj_rec ,
1535 p_adj_percent => l_adj_percent
1536 );
1537 --DBMS_output.put_line('In fetch best adjustment in ITEM percent After: '|| p_adj_rec.percent);
1538 OE_DEBUG_PUB.ADD('In fetch best adjustment in ITEM percent After: '|| p_adj_rec.percent);
1539 END IF;
1540
1541 END IF;
1542
1543 IF p_item_category_id IS NOT NULL THEN
1544
1545 IF Attribute_Used(G_ATTR_ITEM_CATEGORY) THEN
1546 --DBMS_output.put_line('In fetch best adjustment G_ATTR_ITEM_CAT: '||G_ATTR_ITEM_CATEGORY );
1547 --DBMS_output.put_line('In fetch best adj --p_attribute_value: '||p_item_category_id);
1548 OE_DEBUG_PUB.ADD('In fetch best adj --p_attribute_value: '||p_item_category_id);
1549 Get_Adjustment
1550 ( p_best_adj_rec => l_adj_rec ,
1551 p_best_adj_percent=> l_adj_percent ,
1552 p_attribute_id => G_ATTR_ITEM_CATEGORY ,
1553 p_attribute_value => TO_CHAR(p_item_category_id) ,
1554 p_price_list_id => p_price_list_id ,
1555 p_quantity => p_quantity ,
1556 p_list_price => p_list_price ,
1557 p_sold_to_org_id => p_sold_to_org_id ,
1558 p_customer_class_code => p_customer_class_code ,
1559 p_gsa => l_gsa ,
1560 p_ship_to_id => p_ship_to_id ,
1561 p_invoice_to_id => p_invoice_to_id ,
1562 p_unit_code => p_unit_code ,
1563 p_adj_rec => l_adj_rec ,
1564 p_adj_percent => l_adj_percent
1565 );
1566 --DBMS_output.put_line('In fetch best adjustment in ITEM_CAT After: '|| p_adj_rec.percent);
1567
1568 END IF;
1569
1570 END IF;
1571
1572 IF p_pricing_attribute1 IS NOT NULL THEN
1573
1574 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE1) THEN
1575 --DBMS_output.put_line('In fetch best adjustment PRC_ATTRIBUTE1: '|| p_adj_rec.percent);
1576 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE1: '|| p_adj_rec.percent);
1577 Get_Adjustment
1578 ( p_best_adj_rec => l_adj_rec ,
1579 p_best_adj_percent=> l_adj_percent ,
1580 p_attribute_id => G_ATTR_PRC_ATTRIBUTE1 ,
1581 p_attribute_value => p_pricing_attribute1 ,
1582 p_price_list_id => p_price_list_id ,
1583 p_quantity => p_quantity ,
1584 p_list_price => p_list_price ,
1585 p_sold_to_org_id => p_sold_to_org_id ,
1586 p_customer_class_code => p_customer_class_code ,
1587 p_gsa => l_gsa ,
1588 p_ship_to_id => p_ship_to_id ,
1589 p_invoice_to_id => p_invoice_to_id ,
1590 p_unit_code => p_unit_code ,
1591 p_adj_rec => l_adj_rec ,
1592 p_adj_percent => l_adj_percent
1593 );
1594
1595 END IF;
1596
1597 END IF;
1598
1599
1600 IF p_pricing_attribute2 IS NOT NULL THEN
1601
1602 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE2) THEN
1603 --DBMS_output.put_line('In fetch best adjustment in PRC_ATTRIB2: '|| p_adj_rec.percent);
1604 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE2: '|| p_adj_rec.percent);
1605 Get_Adjustment
1606 ( p_best_adj_rec => l_adj_rec ,
1607 p_best_adj_percent=> l_adj_percent ,
1608 p_attribute_id => G_ATTR_PRC_ATTRIBUTE2 ,
1609 p_attribute_value => p_pricing_attribute2 ,
1610 p_price_list_id => p_price_list_id ,
1611 p_quantity => p_quantity ,
1612 p_list_price => p_list_price ,
1613 p_sold_to_org_id => p_sold_to_org_id ,
1614 p_customer_class_code => p_customer_class_code ,
1615 p_gsa => l_gsa ,
1616 p_ship_to_id => p_ship_to_id ,
1617 p_invoice_to_id => p_invoice_to_id ,
1618 p_unit_code => p_unit_code ,
1619 p_adj_rec => l_adj_rec ,
1620 p_adj_percent => l_adj_percent
1621 );
1622
1623 END IF;
1624
1625 END IF;
1626
1627
1628 IF p_pricing_attribute3 IS NOT NULL THEN
1629
1630 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE3) THEN
1631 --DBMS_output.put_line('In fetch best adjustment PRC_ATTR3: '|| p_adj_rec.percent);
1632 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE3: '|| p_adj_rec.percent);
1633 Get_Adjustment
1634 ( p_best_adj_rec => l_adj_rec ,
1635 p_best_adj_percent=> l_adj_percent ,
1636 p_attribute_id => G_ATTR_PRC_ATTRIBUTE3 ,
1637 p_attribute_value => p_pricing_attribute3 ,
1638 p_price_list_id => p_price_list_id ,
1639 p_quantity => p_quantity ,
1640 p_list_price => p_list_price ,
1641 p_sold_to_org_id => p_sold_to_org_id ,
1642 p_customer_class_code => p_customer_class_code ,
1643 p_gsa => l_gsa ,
1644 p_ship_to_id => p_ship_to_id ,
1645 p_invoice_to_id => p_invoice_to_id ,
1646 p_unit_code => p_unit_code ,
1647 p_adj_rec => l_adj_rec ,
1648 p_adj_percent => l_adj_percent
1649 );
1650
1651 END IF;
1652
1653 END IF;
1654
1655
1656 IF p_pricing_attribute4 IS NOT NULL THEN
1657
1658 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE4) THEN
1659 --DBMS_output.put_line('In fetch best adjustment PRC_ATTR4: '|| p_adj_rec.percent);
1660 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE4: '|| p_adj_rec.percent);
1661 Get_Adjustment
1662 ( p_best_adj_rec => l_adj_rec ,
1663 p_best_adj_percent=> l_adj_percent ,
1664 p_attribute_id => G_ATTR_PRC_ATTRIBUTE4 ,
1665 p_attribute_value => p_pricing_attribute4 ,
1666 p_price_list_id => p_price_list_id ,
1667 p_quantity => p_quantity ,
1668 p_list_price => p_list_price ,
1669 p_sold_to_org_id => p_sold_to_org_id ,
1670 p_customer_class_code => p_customer_class_code ,
1671 p_gsa => l_gsa ,
1672 p_ship_to_id => p_ship_to_id ,
1673 p_invoice_to_id => p_invoice_to_id ,
1674 p_unit_code => p_unit_code ,
1675 p_adj_rec => l_adj_rec ,
1676 p_adj_percent => l_adj_percent
1677 );
1678
1679 END IF;
1680
1681 END IF;
1682
1683
1684 IF p_pricing_attribute5 IS NOT NULL THEN
1685
1686 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE5) THEN
1687 --DBMS_output.put_line('In fetch best adjustment PRC_ATTR5: '|| p_adj_rec.percent);
1688 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE5: '|| p_adj_rec.percent);
1689 Get_Adjustment
1690 ( p_best_adj_rec => l_adj_rec ,
1691 p_best_adj_percent=> l_adj_percent ,
1692 p_attribute_id => G_ATTR_PRC_ATTRIBUTE5 ,
1693 p_attribute_value => p_pricing_attribute5 ,
1694 p_price_list_id => p_price_list_id ,
1695 p_quantity => p_quantity ,
1696 p_list_price => p_list_price ,
1697 p_sold_to_org_id => p_sold_to_org_id ,
1698 p_customer_class_code => p_customer_class_code ,
1699 p_gsa => l_gsa ,
1700 p_ship_to_id => p_ship_to_id ,
1701 p_invoice_to_id => p_invoice_to_id ,
1702 p_unit_code => p_unit_code ,
1703 p_adj_rec => l_adj_rec ,
1704 p_adj_percent => l_adj_percent
1705 );
1706
1707 END IF;
1708
1709 END IF;
1710
1711
1712 IF p_pricing_attribute6 IS NOT NULL THEN
1713
1714 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE6) THEN
1715 --DBMS_output.put_line('In fetch best adjustment PRC_ATTR6: '|| p_adj_rec.percent);
1716 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE6: '|| p_adj_rec.percent);
1717 Get_Adjustment
1718 ( p_best_adj_rec => l_adj_rec ,
1719 p_best_adj_percent=> l_adj_percent ,
1720 p_attribute_id => G_ATTR_PRC_ATTRIBUTE6 ,
1721 p_attribute_value => p_pricing_attribute6 ,
1722 p_price_list_id => p_price_list_id ,
1723 p_quantity => p_quantity ,
1724 p_list_price => p_list_price ,
1725 p_sold_to_org_id => p_sold_to_org_id ,
1726 p_customer_class_code => p_customer_class_code ,
1727 p_gsa => l_gsa ,
1728 p_ship_to_id => p_ship_to_id ,
1729 p_invoice_to_id => p_invoice_to_id ,
1730 p_unit_code => p_unit_code ,
1731 p_adj_rec => l_adj_rec ,
1732 p_adj_percent => l_adj_percent
1733 );
1734
1735 END IF;
1736
1737 END IF;
1738
1739
1740 IF p_pricing_attribute7 IS NOT NULL THEN
1741
1742 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE7) THEN
1743 --DBMS_output.put_line('In fetch best adjustment PRC_ATTR7: '|| p_adj_rec.percent);
1744 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE7: '|| p_adj_rec.percent);
1745 Get_Adjustment
1746 ( p_best_adj_rec => l_adj_rec ,
1747 p_best_adj_percent=> l_adj_percent ,
1748 p_attribute_id => G_ATTR_PRC_ATTRIBUTE7 ,
1749 p_attribute_value => p_pricing_attribute7 ,
1750 p_price_list_id => p_price_list_id ,
1751 p_quantity => p_quantity ,
1752 p_list_price => p_list_price ,
1753 p_sold_to_org_id => p_sold_to_org_id ,
1754 p_customer_class_code => p_customer_class_code ,
1755 p_gsa => l_gsa ,
1756 p_ship_to_id => p_ship_to_id ,
1757 p_invoice_to_id => p_invoice_to_id ,
1758 p_unit_code => p_unit_code ,
1759 p_adj_rec => l_adj_rec ,
1760 p_adj_percent => l_adj_percent
1761 );
1762
1763 END IF;
1764
1765 END IF;
1766
1767
1768 IF p_pricing_attribute8 IS NOT NULL THEN
1769
1770 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE8) THEN
1771 --DBMS_output.put_line('In fetch best adjustment PRC_ATTR8: '|| p_adj_rec.percent);
1772 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE8: '|| p_adj_rec.percent);
1773 Get_Adjustment
1774 ( p_best_adj_rec => l_adj_rec ,
1775 p_best_adj_percent=> l_adj_percent ,
1776 p_attribute_id => G_ATTR_PRC_ATTRIBUTE8 ,
1777 p_attribute_value => p_pricing_attribute8 ,
1778 p_price_list_id => p_price_list_id ,
1779 p_quantity => p_quantity ,
1780 p_list_price => p_list_price ,
1781 p_sold_to_org_id => p_sold_to_org_id ,
1782 p_customer_class_code => p_customer_class_code ,
1783 p_gsa => l_gsa ,
1784 p_ship_to_id => p_ship_to_id ,
1785 p_invoice_to_id => p_invoice_to_id ,
1786 p_unit_code => p_unit_code ,
1787 p_adj_rec => l_adj_rec ,
1788 p_adj_percent => l_adj_percent
1789 );
1790
1791 END IF;
1792
1793 END IF;
1794
1795
1796 IF p_pricing_attribute9 IS NOT NULL THEN
1797
1798 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE9) THEN
1799 --DBMS_output.put_line('In fetch best adjustment PRC_ATTR9: '|| p_adj_rec.percent);
1800 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE9: '|| p_adj_rec.percent);
1801 Get_Adjustment
1802 ( p_best_adj_rec => l_adj_rec ,
1803 p_best_adj_percent=> l_adj_percent ,
1804 p_attribute_id => G_ATTR_PRC_ATTRIBUTE9 ,
1805 p_attribute_value => p_pricing_attribute9 ,
1806 p_price_list_id => p_price_list_id ,
1807 p_quantity => p_quantity ,
1808 p_list_price => p_list_price ,
1809 p_sold_to_org_id => p_sold_to_org_id ,
1810 p_customer_class_code => p_customer_class_code ,
1811 p_gsa => l_gsa ,
1812 p_ship_to_id => p_ship_to_id ,
1813 p_invoice_to_id => p_invoice_to_id ,
1814 p_unit_code => p_unit_code ,
1815 p_adj_rec => l_adj_rec ,
1816 p_adj_percent => l_adj_percent
1817 );
1818
1819 END IF;
1820
1821 END IF;
1822
1823
1824 IF p_pricing_attribute10 IS NOT NULL THEN
1825
1826 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE10) THEN
1827 --DBMS_output.put_line('In fetch best adjustment PRC_ATTR10: '|| p_adj_rec.percent);
1828 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE10: '|| p_adj_rec.percent);
1829 Get_Adjustment
1830 ( p_best_adj_rec => l_adj_rec ,
1831 p_best_adj_percent=> l_adj_percent ,
1832 p_attribute_id => G_ATTR_PRC_ATTRIBUTE10 ,
1833 p_attribute_value => p_pricing_attribute10 ,
1834 p_price_list_id => p_price_list_id ,
1835 p_quantity => p_quantity ,
1836 p_list_price => p_list_price ,
1837 p_sold_to_org_id => p_sold_to_org_id ,
1838 p_customer_class_code => p_customer_class_code ,
1839 p_gsa => l_gsa ,
1840 p_ship_to_id => p_ship_to_id ,
1841 p_invoice_to_id => p_invoice_to_id ,
1842 p_unit_code => p_unit_code ,
1843 p_adj_rec => l_adj_rec ,
1844 p_adj_percent => l_adj_percent
1845 );
1846
1847 END IF;
1848
1849 END IF;
1850
1851
1852 IF p_pricing_attribute11 IS NOT NULL THEN
1853
1854 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE11) THEN
1855 --DBMS_output.put_line('In fetch best adjustment PRC_ATTR11: '|| p_adj_rec.percent);
1856 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE11: '|| p_adj_rec.percent);
1857 Get_Adjustment
1858 ( p_best_adj_rec => l_adj_rec ,
1859 p_best_adj_percent=> l_adj_percent ,
1860 p_attribute_id => G_ATTR_PRC_ATTRIBUTE11 ,
1861 p_attribute_value => p_pricing_attribute11 ,
1862 p_price_list_id => p_price_list_id ,
1863 p_quantity => p_quantity ,
1864 p_list_price => p_list_price ,
1865 p_sold_to_org_id => p_sold_to_org_id ,
1866 p_customer_class_code => p_customer_class_code ,
1867 p_gsa => l_gsa ,
1868 p_ship_to_id => p_ship_to_id ,
1869 p_invoice_to_id => p_invoice_to_id ,
1870 p_unit_code => p_unit_code ,
1871 p_adj_rec => l_adj_rec ,
1872 p_adj_percent => l_adj_percent
1873 );
1874
1875 END IF;
1876
1877 END IF;
1878
1879
1880 IF p_pricing_attribute12 IS NOT NULL THEN
1881
1882 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE12) THEN
1883 --DBMS_output.put_line('In fetch best adjustment PRC_ATTR12: '|| p_adj_rec.percent);
1884 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE12: '|| p_adj_rec.percent);
1885 Get_Adjustment
1886 ( p_best_adj_rec => l_adj_rec ,
1887 p_best_adj_percent=> l_adj_percent ,
1888 p_attribute_id => G_ATTR_PRC_ATTRIBUTE12 ,
1889 p_attribute_value => p_pricing_attribute12 ,
1890 p_price_list_id => p_price_list_id ,
1891 p_quantity => p_quantity ,
1892 p_list_price => p_list_price ,
1893 p_sold_to_org_id => p_sold_to_org_id ,
1894 p_customer_class_code => p_customer_class_code ,
1895 p_gsa => l_gsa ,
1896 p_ship_to_id => p_ship_to_id ,
1897 p_invoice_to_id => p_invoice_to_id ,
1898 p_unit_code => p_unit_code ,
1899 p_adj_rec => l_adj_rec ,
1900 p_adj_percent => l_adj_percent
1901 );
1902
1903 END IF;
1904
1905 END IF;
1906
1907
1908 IF p_pricing_attribute13 IS NOT NULL THEN
1909
1910 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE13) THEN
1911 --DBMS_OUTPUT.PUT_LINE('In fetch best adjustment PRC_ATTRIBUTE13: '|| p_adj_rec.percent);
1912 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE13: '|| p_adj_rec.percent);
1913 Get_Adjustment
1914 ( p_best_adj_rec => l_adj_rec ,
1915 p_best_adj_percent=> l_adj_percent ,
1916 p_attribute_id => G_ATTR_PRC_ATTRIBUTE13 ,
1917 p_attribute_value => p_pricing_attribute13 ,
1918 p_price_list_id => p_price_list_id ,
1919 p_quantity => p_quantity ,
1920 p_list_price => p_list_price ,
1921 p_sold_to_org_id => p_sold_to_org_id ,
1922 p_customer_class_code => p_customer_class_code ,
1923 p_gsa => l_gsa ,
1924 p_ship_to_id => p_ship_to_id ,
1925 p_invoice_to_id => p_invoice_to_id ,
1926 p_unit_code => p_unit_code ,
1927 p_adj_rec => l_adj_rec ,
1928 p_adj_percent => l_adj_percent
1929 );
1930
1931 END IF;
1932
1933 END IF;
1934
1935
1936 IF p_pricing_attribute14 IS NOT NULL THEN
1937
1938 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE13) THEN
1939 --DBMS_OUTPUT.PUT_LINE('In fetch best adjustment PRC_ATTRIBUTE14: '|| p_adj_rec.percent);
1940 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE14: '|| p_adj_rec.percent);
1941 Get_Adjustment
1942 ( p_best_adj_rec => l_adj_rec ,
1943 p_best_adj_percent=> l_adj_percent ,
1944 p_attribute_id => G_ATTR_PRC_ATTRIBUTE14 ,
1945 p_attribute_value => p_pricing_attribute14 ,
1946 p_price_list_id => p_price_list_id ,
1947 p_quantity => p_quantity ,
1948 p_list_price => p_list_price ,
1949 p_sold_to_org_id => p_sold_to_org_id ,
1950 p_customer_class_code => p_customer_class_code ,
1951 p_gsa => l_gsa ,
1952 p_ship_to_id => p_ship_to_id ,
1953 p_invoice_to_id => p_invoice_to_id ,
1954 p_unit_code => p_unit_code ,
1955 p_adj_rec => l_adj_rec ,
1956 p_adj_percent => l_adj_percent
1957 );
1958
1959 END IF;
1960
1961 END IF;
1962
1963
1964 IF p_pricing_attribute15 IS NOT NULL THEN
1965
1966 IF Attribute_Used(G_ATTR_PRC_ATTRIBUTE15) THEN
1967
1968 Get_Adjustment
1969 ( p_best_adj_rec => l_adj_rec ,
1970 p_best_adj_percent=> l_adj_percent ,
1971 p_attribute_id => G_ATTR_PRC_ATTRIBUTE15 ,
1972 p_attribute_value => p_pricing_attribute15 ,
1973 p_price_list_id => p_price_list_id ,
1974 p_quantity => p_quantity ,
1975 p_list_price => p_list_price ,
1976 p_sold_to_org_id => p_sold_to_org_id ,
1977 p_customer_class_code => p_customer_class_code ,
1978 p_gsa => l_gsa ,
1979 p_ship_to_id => p_ship_to_id ,
1980 p_invoice_to_id => p_invoice_to_id ,
1981 p_unit_code => p_unit_code ,
1982 p_adj_rec => l_adj_rec ,
1983 p_adj_percent => l_adj_percent
1984 );
1985
1986 --DBMS_OUTPUT.PUT_LINE('In fetch best adjustment PRC_ATTRIBUTE15: '|| p_adj_rec.percent);
1987 OE_DEBUG_PUB.ADD('In fetch best adjustment PRC_ATTRIBUTE15: '|| p_adj_rec.percent);
1988
1989 END IF;
1990
1991 END IF;
1992
1993
1994 IF p_po_number IS NOT NULL THEN
1995
1996 IF Attribute_Used(G_ATTR_PO_NUMBER) THEN
1997 --DBMS_output.put_line('In fetch best adjustment PO_NUMBER: '|| p_adj_rec.percent);
1998 Get_Adjustment
1999 ( p_best_adj_rec => l_adj_rec ,
2000 p_best_adj_percent=> l_adj_percent ,
2001 p_attribute_id => G_ATTR_PO_NUMBER ,
2002 p_attribute_value => p_po_number ,
2003 p_price_list_id => p_price_list_id ,
2004 p_quantity => p_quantity ,
2005 p_list_price => p_list_price ,
2006 p_sold_to_org_id => p_sold_to_org_id ,
2007 p_customer_class_code => p_customer_class_code ,
2008 p_gsa => l_gsa ,
2009 p_ship_to_id => p_ship_to_id ,
2010 p_invoice_to_id => p_invoice_to_id ,
2011 p_unit_code => p_unit_code ,
2012 p_adj_rec => l_adj_rec ,
2013 p_adj_percent => l_adj_percent
2014 );
2015
2016 END IF;
2017
2018 END IF;
2019
2020
2021 IF p_agreement_id IS NOT NULL THEN
2022
2023 IF Attribute_Used(G_ATTR_AGREEMENT) THEN
2024 --DBMS_output.put_line('In fetch best adjustment ATTR_AGREEMENT: '|| p_adj_rec.percent);
2025 Get_Adjustment
2026 ( p_best_adj_rec => l_adj_rec ,
2027 p_best_adj_percent=> l_adj_percent ,
2028 p_attribute_id => G_ATTR_AGREEMENT ,
2029 p_attribute_value => TO_CHAR(p_agreement_id) ,
2030 p_price_list_id => p_price_list_id ,
2031 p_quantity => p_quantity ,
2032 p_list_price => p_list_price ,
2033 p_sold_to_org_id => p_sold_to_org_id ,
2034 p_customer_class_code => p_customer_class_code ,
2035 p_gsa => l_gsa ,
2036 p_ship_to_id => p_ship_to_id ,
2037 p_invoice_to_id => p_invoice_to_id ,
2038 p_unit_code => p_unit_code ,
2039 p_adj_rec => l_adj_rec ,
2040 p_adj_percent => l_adj_percent
2041 );
2042
2043 END IF;
2044
2045 END IF;
2046
2047
2048 IF p_agreement_type_code IS NOT NULL THEN
2049
2050 IF Attribute_Used(G_ATTR_AGREEMENT_TYPE) THEN
2051 --DBMS_output.put_line('In fetch best adjustment IN AGREEMENT_TYPE: '|| p_adj_rec.percent);
2052 Get_Adjustment
2053 ( p_best_adj_rec => l_adj_rec ,
2054 p_best_adj_percent=> l_adj_percent ,
2055 p_attribute_id => G_ATTR_AGREEMENT_TYPE ,
2056 p_attribute_value => p_agreement_type_code ,
2057 p_price_list_id => p_price_list_id ,
2058 p_quantity => p_quantity ,
2059 p_list_price => p_list_price ,
2060 p_sold_to_org_id => p_sold_to_org_id ,
2061 p_customer_class_code => p_customer_class_code ,
2062 p_gsa => l_gsa ,
2063 p_ship_to_id => p_ship_to_id ,
2064 p_invoice_to_id => p_invoice_to_id ,
2065 p_unit_code => p_unit_code ,
2066 p_adj_rec => l_adj_rec ,
2067 p_adj_percent => l_adj_percent
2068 );
2069
2070 END IF;
2071
2072 END IF;
2073
2074
2075 IF p_order_type_id IS NOT NULL THEN
2076
2077 IF Attribute_Used(G_ATTR_ORDER_TYPE_ID) THEN
2078 --DBMS_output.put_line('In fetch best adjustment in Order_Type_ID: '|| p_adj_rec.percent);
2079 Get_Adjustment
2080 ( p_best_adj_rec => l_adj_rec ,
2081 p_best_adj_percent=> l_adj_percent ,
2082 p_attribute_id => G_ATTR_ORDER_TYPE_ID ,
2083 p_attribute_value => TO_CHAR(p_order_type_id) ,
2084 p_price_list_id => p_price_list_id ,
2085 p_quantity => p_quantity ,
2086 p_list_price => p_list_price ,
2087 p_sold_to_org_id => p_sold_to_org_id ,
2088 p_customer_class_code => p_customer_class_code ,
2089 p_gsa => l_gsa ,
2090 p_ship_to_id => p_ship_to_id ,
2091 p_invoice_to_id => p_invoice_to_id ,
2092 p_unit_code => p_unit_code ,
2093 p_adj_rec => l_adj_rec ,
2094 p_adj_percent => l_adj_percent
2095 );
2096
2097 END IF;
2098
2099 END IF;
2100
2101
2102 -- Return values collected in l_adj_rec to p_adj_rec out variable
2103
2104 p_adj_rec := l_adj_rec;
2105 --DBMS_output.put_line('In Fetch Best Adjustment percent :'||p_adj_rec.percent);
2106 oe_debug_pub.add('Exiting QP_PRC_UTIL.FETCH_BEST_ADJUSTMENT', 1);
2107
2108 EXCEPTION
2109
2110 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2111
2112 RAISE;
2113
2114 WHEN OTHERS THEN
2115
2116 -- Unexpected error
2117 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2118
2119 OE_MSG_PUB.Add_Exc_Msg
2120 ( G_PKG_NAME ,
2121 'Price_Item - Fetch_Best_Adjustment'
2122 );
2123 END IF;
2124
2125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2126
2127 END; -- Fetch_Best_Adjustment
2128
2129
2130
2131 -- Procedure Calculate_Selling_Price
2132 -- Usage:
2133 -- Calculate_Selling_Price is called from the Price_Item procedure
2134 -- after Price_Item has fetched the best available automatic
2135 -- adjustment and calculated the total adjustment total.
2136 -- Calculate_Selling_Price is also called from the Price_Line
2137 -- procedure as a full call to Price_Item from the Price_Line API
2138 -- is unnecessary in certain situations.
2139 -- Description:
2140 -- Calculates the selling price or selling percent for an
2141 -- item, applying all adjustments.
2142 -- Two pricing methods are supported: amount and percent.
2143 -- Parameters:
2144 -- IN:
2145 -- p_adj_total NUMBER required
2146 -- Total adjustment percent to apply 0-100
2147 -- p_list_price NUMBER required
2148 -- Used when price_method_code = AMNT (amount),
2149 -- otherwise pass NULL
2150 -- p_list_percent NUMBER required
2151 -- Used when price_method_code = PERC (percent),
2152 -- otherwise pass NULL
2153 -- p_price_list_id NUMBER required
2154 -- If p_price_list_id is NULL, Calculate_Selling_Price
2155 -- returns with all OUT parameters set to NULL
2156 -- p_base_price NUMBER required
2157 -- Used when price_method_code = PERC. Comes from
2158 -- the parent service line list_price in oe_order_lines
2159 -- p_service_duration NUMBER required
2160 -- Used when price_method_code = PERC,
2161 -- otherwise pass NULL
2162 -- p_price_method_code VARCHAR2(4) required
2163 -- price_item procedure supports two types of pricing:
2164 -- G_PRC_METHOD_AMOUNT - an amount
2165 -- G_PRC_METHOD_PERCENT - a percent
2166 -- Value for price_method_code comes from oe_order_lines or
2167 -- from fetch_list_price. If price_method_code is
2168 -- missing, price_item returns with all out
2169 -- parameters set to NULL
2170 --
2171 -- OUT:
2172 -- p_selling_price NUMBER
2173 -- Final rounded selling price
2174 -- p_selling_percent NUMBER
2175 -- When price_method_code = PERC, this parameter
2176 -- holds the selling percent
2177 -- p_list_price_out NUMBER
2178 -- When price_method_code = PERC, this parameter
2179 -- holds the list price
2180 --
2181 -- Notes:
2182
2183 PROCEDURE Calculate_Selling_Price
2184 ( p_adj_total IN NUMBER ,
2185 p_list_price IN NUMBER ,
2186 p_list_percent IN NUMBER ,
2187 p_price_list_id IN NUMBER ,
2188 p_base_price IN NUMBER ,
2189 p_service_duration IN NUMBER ,
2190 p_pricing_method_code IN VARCHAR2 ,
2191 p_selling_price OUT NOCOPY /* file.sql.39 change */ NUMBER ,
2192 p_selling_percent OUT NOCOPY /* file.sql.39 change */ NUMBER ,
2193 p_list_price_out OUT NOCOPY /* file.sql.39 change */ NUMBER
2194 )
2195 IS
2196 l_selling_percent NUMBER := NULL;
2197 l_adj_total NUMBER;
2198 l_rounding_factor NUMBER;
2199
2200 BEGIN
2201
2202 oe_debug_pub.add('Entering QP_PRC_UTIL.CALCULATE_SELLING_PRICE', 1);
2203
2204 -- p_price_list_id and p_pricing_method_code are required. If
2205 -- either is null, set all out parameters to NULL and return.
2206 --DBMS_output.put_line('In cal selling price--price_list_id: '||p_price_list_id);
2207 --DBMS_output.put_line('===========================================');
2208 --DBMS_output.put_line('pricing_method_code: '||p_pricing_method_code);
2209
2210 IF p_price_list_id IS NULL
2211 OR p_pricing_method_code IS NULL
2212 THEN
2213 p_selling_price := NULL;
2214 p_selling_percent := NULL;
2215 p_list_price_out := NULL;
2216 RETURN;
2217 END IF;
2218
2219
2220 -- Fetch rounding factor from price list.
2221 --QP
2222 /*
2223 SELECT ROUNDING_FACTOR
2224 INTO l_rounding_factor
2225 FROM oe_price_lists
2226 WHERE price_list_id = p_price_list_id;
2227 */
2228
2229
2230 SELECT ROUNDING_FACTOR
2231 INTO l_rounding_factor
2232 FROM QP_LIST_HEADERS
2233 WHERE LIST_HEADER_ID = p_price_list_id;
2234 --END QP
2235 -- Initialize local variable holding adjustment total
2236
2237 IF p_adj_total IS NULL THEN
2238 l_adj_total := 0 ;
2239 ELSE
2240 l_adj_total := p_adj_total ;
2241 END IF;
2242
2243
2244 IF p_pricing_method_code = G_PRC_METHOD_AMOUNT THEN
2245
2246 -- selling_price = list_price * (100-l_adj_total)/100
2247 --DBMS_output.put_line('p_list_price in cal sell prc: '||p_list_price);
2248 p_selling_price := ROUND ( p_list_price *
2249 ( 100 - l_adj_total ) / 100
2250 , - l_rounding_factor
2251 );
2252
2253 p_selling_percent := NULL;
2254 p_list_price_out := p_list_price;
2255
2256 --DBMS_output.put_line('Sell price '||p_selling_price);
2257
2258 ELSIF p_pricing_method_code = G_PRC_METHOD_PERCENT THEN
2259
2260 -- Init OUT parameters.
2261
2262 p_selling_price := NULL;
2263 p_list_price_out := NULL ;
2264 p_selling_percent := NULL ;
2265
2266 --DBMS_output.put_line('in cal sell--p_list_percent: '||p_list_percent);
2267
2268 -- If pricing method is percent, p_list_percent must be valid
2269
2270 IF p_list_percent IS NULL
2271 THEN
2272 RETURN;
2273 END IF;
2274 --DBMS_output.put_line('in cal sell--p_list_percent '||p_list_percent);
2275
2276 -- Calculate selling percent
2277 -- selling_percent = list_percent * (100 - l_adj_total )/ 100
2278
2279 l_selling_percent := p_list_percent * (100 - l_adj_total)/100;
2280 p_selling_percent := l_selling_percent;
2281
2282
2283 -- p_base_price and p_service_duration are required to
2284 -- calculate p_list_price and p_selling price when the
2285 -- pricing method is percent
2286
2287 IF p_base_price IS NULL OR
2288 p_service_duration IS NULL
2289 THEN
2290 RETURN;
2291 END IF;
2292
2293
2294 -- calculate list_price
2295 -- list_price := base_price*service_duration*list_percent/100
2296
2297 p_list_price_out := ROUND (p_base_price *
2298 p_service_duration *
2299 p_list_percent / 100
2300 , - l_rounding_factor
2301 );
2302
2303 -- calculate selling_price
2304 -- selling_price := base_price*service_duration*selling_percent/100
2305
2306 p_selling_price := ROUND ( p_base_price *
2307 p_service_duration *
2308 l_selling_percent / 100
2309 , - l_rounding_factor
2310 );
2311
2312 ELSE
2313
2314 -- Unexpected error, invalid pricing method
2315
2316 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2317
2318 OE_MSG_PUB.Add_Exc_Msg
2319 ( G_PKG_NAME ,
2320 'Price_Item, Calculate_Selling_Price - invalid pricing method ='||p_pricing_method_code
2321 );
2322 END IF;
2323
2324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2325
2326 END IF;
2327
2328 oe_debug_pub.add('Exiting QP_PRC_UTIL.CALCULATE_SELLING_PRICE', 1);
2329
2330 EXCEPTION
2331
2332 WHEN OTHERS THEN
2333
2334 -- Unexpected error
2335 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2336
2337 OE_MSG_PUB.Add_Exc_Msg
2338 ( G_PKG_NAME ,
2339 'Price_Item - Calculate_Selling_Price'
2340 );
2341 END IF;
2342
2343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2344
2345 END Calculate_Selling_Price;
2346
2347
2348
2349 -- Procedure Price_Item
2350 -- Usage:
2351 -- Price_Item is called from the Price_Line API.
2352 -- Description:
2353 -- Fetches the best automatic adjustment for an item, sums
2354 -- up all the adjustments (manual and automatic, header and
2355 -- line) applied to the item, an finally calculates the
2356 -- selling price based on the pricing method.
2357 -- Parameters:
2358 -- IN:
2359 -- p_item_rec Prc_Item_Rec_Type required
2360 -- p_item_rec holds the item/price_list information.
2361 -- It also holds the discounting attributes affecting
2362 -- the fetch of the best discount.
2363 -- p_existing_adj_total NUMBER optional
2364 -- This parameter holds the total of all manual
2365 -- line and header level adjustments. It will be
2366 -- used when validating that by applying the new
2367 -- adjustment, the total is not going to exceed 100,
2368 -- and it will be added to the new adjustment (if any)
2369 -- to compute the final adjustment total that will
2370 -- be used to calculate the selling price
2371 -- OUT:
2372 -- p_return_status VARCHAR2(1)
2373 -- p_selling_price NUMBER
2374 -- Final rounded selling price
2375 -- p_selling_percent NUMBER
2376 -- When price_method_code = PERC, this parameter
2377 -- holds the selling percent
2378 -- p_adj_out_tbl Adj_Short_Tbl_Type
2379 -- PL/SQL table that holds the new fetched automatic
2380 -- adjustment if found. Currently the Price_Item
2381 -- procedure fetches only one automatic adjustment.
2382 -- We use the Adj_Short_Tbl_Type table as an OUT
2383 -- parameter to allow for future enhancements that
2384 -- may result in the procedure returning more than
2385 -- one adjustment.
2386 -- Notes:
2387
2388 PROCEDURE Price_Item
2389 ( p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 ,
2390 p_item_rec IN Prc_Item_Rec_Type ,
2391 p_existing_adj_total IN NUMBER := 0 ,
2392 p_selling_price OUT NOCOPY /* file.sql.39 change */ NUMBER ,
2393 p_selling_percent OUT NOCOPY /* file.sql.39 change */ NUMBER ,
2394 p_list_price_out OUT NOCOPY /* file.sql.39 change */ NUMBER ,
2395 p_adj_out_table OUT NOCOPY /* file.sql.39 change */ Adj_Short_Tbl_Type
2396 )
2397 IS
2398 l_return_status VARCHAR2(1);
2399 l_validation_error BOOLEAN := FALSE;
2400 l_adj_total NUMBER := 0;
2401 l_adj_rec Adj_Short_Rec_Type;
2402 l_dummy NUMBER;
2403 l_msg_count NUMBER;
2404 l_rounding_factor NUMBER;
2405 l_msg_data VARCHAR2(30);
2406 l_item_rec Prc_Item_Rec_Type;
2407 BEGIN
2408
2409 --DBMS_output.put_line('I am in Price Item');
2410
2411 oe_debug_pub.add('Entering QP_PRC_UTIL.PRICE_ITEM', 1);
2412
2413 -- Initialize p_return_status
2414
2415 p_return_status := FND_API.G_RET_STS_SUCCESS;
2416
2417
2418 -- Initialize OUT parameters
2419
2420 p_selling_price := NULL;
2421 p_selling_percent := NULL;
2422
2423
2424 -- Check required parameters are not null
2425 -- IF NULL try to get them
2426
2427 l_item_rec := p_item_rec;
2428
2429 IF l_item_rec.price_list_id IS NULL
2430 OR l_item_rec.list_price IS NULL
2431 OR l_item_rec.price_method_code IS NULL
2432 THEN
2433 QP_Price_List_PVT.Fetch_List_Price
2434 ( p_api_version_number => 1.0 ,
2435 p_return_status => l_return_status ,
2436 p_msg_count => l_msg_count ,
2437 p_msg_data => l_msg_data ,
2438 p_price_list_id => l_item_rec.price_list_id ,
2439 p_inventory_item_id => l_item_rec.inventory_item_id ,
2440 p_unit_code => l_item_rec.unit_code ,
2441 p_service_duration => l_item_rec.service_duration ,
2442 p_item_type_code => l_item_rec.item_type_code ,
2443 p_prc_method_code => l_item_rec.price_method_code ,
2444 p_pricing_attribute1 => l_item_rec.pricing_attribute1 ,
2445 p_pricing_attribute2 => l_item_rec.pricing_attribute2 ,
2446 p_pricing_attribute3 => l_item_rec.pricing_attribute3 ,
2447 p_pricing_attribute4 => l_item_rec.pricing_attribute4 ,
2448 p_pricing_attribute5 => l_item_rec.pricing_attribute5 ,
2449 p_pricing_attribute6 => l_item_rec.pricing_attribute6 ,
2450 p_pricing_attribute7 => l_item_rec.pricing_attribute7 ,
2451 p_pricing_attribute8 => l_item_rec.pricing_attribute8 ,
2452 p_pricing_attribute9 => l_item_rec.pricing_attribute9 ,
2453 p_pricing_attribute10 => l_item_rec.pricing_attribute10 ,
2454 p_pricing_attribute11 => l_item_rec.pricing_attribute11 ,
2455 p_pricing_attribute12 => l_item_rec.pricing_attribute12 ,
2456 p_pricing_attribute13 => l_item_rec.pricing_attribute13 ,
2457 p_pricing_attribute14 => l_item_rec.pricing_attribute14 ,
2458 p_pricing_attribute15 => l_item_rec.pricing_attribute15 ,
2459 p_base_price => l_item_rec.base_price ,
2460 p_pricing_date => l_item_rec.pricing_date ,
2461 p_price_list_id_out => l_item_rec.price_list_id ,
2462 p_prc_method_code_out => l_item_rec.price_method_code,
2463 p_list_price => l_item_rec.list_price ,
2464 p_list_percent => l_item_rec.list_percent ,
2465 p_rounding_factor => l_rounding_factor
2466 );
2467
2468 END IF;
2469
2470
2471 --If these values are still NULL after the above call, exit
2472 IF l_item_rec.price_list_id IS NULL
2473 OR l_item_rec.list_price IS NULL
2474 OR l_item_rec.price_method_code IS NULL THEN
2475 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2476 return;
2477 END IF;
2478
2479
2480
2481 -- Validate Input
2482
2483 -- Validate input :
2484 -- l_item_rec.price_method_code
2485 -- p_existing_adj_total
2486
2487 -- price method code must be valid
2488 IF NOT Equal ( l_item_rec.price_method_code , G_PRC_METHOD_AMOUNT ) AND
2489 NOT Equal ( l_item_rec.price_method_code , G_PRC_METHOD_PERCENT )
2490 THEN
2491
2492 l_validation_error := TRUE;
2493
2494 FND_MESSAGE.SET_NAME('QP','OE_PRC_INVALID_PRC_METHOD');
2495 FND_MESSAGE.SET_TOKEN('METHOD',l_item_rec.price_method_code);
2496 OE_MSG_PUB.Add;
2497
2498 END IF;
2499
2500 -- If any validation errors occur then return error.
2501
2502 IF l_validation_error THEN
2503 RAISE FND_API.G_EXC_ERROR;
2504 END IF;
2505
2506 -- Set l_adj_total.
2507
2508 IF p_existing_adj_total IS NULL THEN
2509 l_adj_total := 0;
2510 ELSE
2511 l_adj_total := p_existing_adj_total;
2512 END IF;
2513
2514 -- Debug info
2515 /*
2516 OE_MSG_PUB.Add_Exc_Msg
2517 ( p_error_text => 'Before calling fetch_best_adj - '||
2518 ' item = '||l_item_rec.inventory_item_id||
2519 ' price_list = '||l_item_rec.price_list_id||
2520 ' list_rice = '||l_item_rec.list_price||
2521 ' quantity = '||l_item_rec.quantity
2522 );
2523 */
2524
2525 -- Fetch highest automatic discount
2526
2527 OE_DEBUG_PUB.ADD('+============================+');
2528 OE_DEBUG_PUB.ADD('|Before fetch best adjustment|');
2529 OE_DEBUG_PUB.ADD('+============================+');
2530 OE_DEBUG_PUB.ADD('l_item_rec.inventory_item_id '||l_item_rec.inventory_item_id);
2531 OE_DEBUG_PUB.ADD('l_item_rec.price_list_id '||l_item_rec.price_list_id);
2532 OE_DEBUG_PUB.ADD('l_item_rec.list_price '||l_item_rec.list_price);
2533 OE_DEBUG_PUB.ADD('l_item_rec.quantity '||l_item_rec.quantity);
2534 OE_DEBUG_PUB.ADD('l_item_rec.pricing_date '||l_item_rec.pricing_date);
2535 OE_DEBUG_PUB.ADD('l_item_rec.unit_code '||l_item_rec.unit_code);
2536 OE_DEBUG_PUB.ADD('l_item_rec.ship_to_site_use_id '||l_item_rec.ship_to_site_use_id);
2537 OE_DEBUG_PUB.ADD('l_item_rec.item_category_id '||l_item_rec.item_category_id);
2538 OE_DEBUG_PUB.ADD('l_item_rec.sold_to_org_id '||l_item_rec.sold_to_org_id);
2539 OE_DEBUG_PUB.ADD('l_item_rec.customer_class_code '||l_item_rec.customer_class_code);
2540 OE_DEBUG_PUB.ADD('l_item_rec.invoice_to_org_id '||l_item_rec.invoice_to_org_id);
2541 OE_DEBUG_PUB.ADD('l_item_rec.po_number '||l_item_rec.po_number);
2542 OE_DEBUG_PUB.ADD('l_item_rec.agreement_id '||l_item_rec.agreement_id);
2543 OE_DEBUG_PUB.ADD('p_order_type_id '||l_item_rec.order_type_id);
2544 OE_DEBUG_PUB.ADD('p_gsa '||l_item_rec.gsa);
2545 OE_DEBUG_PUB.ADD('l_item_rec.pricing_attribute1'||l_item_rec.pricing_attribute1);
2546
2547 --DBMS_OUTPUT.PUT_LINE('+============================+');
2548 --DBMS_OUTPUT.PUT_LINE('|Before fetch best adjustment|');
2549 --DBMS_OUTPUT.PUT_LINE('+============================+');
2550 --DBMS_OUTPUT.PUT_LINE('l_item_rec.inventory_item_id '||l_item_rec.inventory_item_id);
2551 --DBMS_OUTPUT.PUT_LINE('l_item_rec.price_list_id '||l_item_rec.price_list_id);
2552 --DBMS_OUTPUT.PUT_LINE('l_item_rec.list_price '||l_item_rec.list_price);
2553 --DBMS_OUTPUT.PUT_LINE('l_item_rec.quantity '||l_item_rec.quantity);
2554 --DBMS_OUTPUT.PUT_LINE('l_item_rec.pricing_date '||l_item_rec.pricing_date);
2555 --DBMS_OUTPUT.PUT_LINE('l_item_rec.unit_code '||l_item_rec.unit_code);
2556 --DBMS_OUTPUT.PUT_LINE('l_item_rec.ship_to_site_use_id '||l_item_rec.ship_to_site_use_id);
2557 --DBMS_OUTPUT.PUT_LINE('l_item_rec.item_category_id '||l_item_rec.item_category_id);
2558 --DBMS_OUTPUT.PUT_LINE('l_item_rec.sold_to_org_id '||l_item_rec.sold_to_org_id);
2559 ----DBMS_OUTPUT.PUT_LINE('l_item_rec.customer_class_code '||l_item_rec.customer_class_code);
2560 ----DBMS_OUTPUT.PUT_LINE('l_item_rec.invoice_to_org_id '||l_item_rec.invoice_to_org_id);
2561 ----DBMS_OUTPUT.PUT_LINE('l_item_rec.po_number '||l_item_rec.po_number);
2562 ----DBMS_OUTPUT.PUT_LINE('l_item_rec.agreement_id '||l_item_rec.agreement_id);
2563 ----DBMS_OUTPUT.PUT_LINE('p_order_type_id '||l_item_rec.order_type_id);
2564 ----DBMS_OUTPUT.PUT_LINE('p_gsa '||l_item_rec.gsa);
2565
2566 ----DBMS_output
2567 --QP Debug, see the effect if set p_customer_class_code to other
2568 --l_item_rec.customer_class_code := 'Other';
2569
2570 Fetch_Best_Adjustment
2571 ( p_inventory_item_id => l_item_rec.inventory_item_id ,
2572 p_price_list_id => l_item_rec.price_list_id ,
2573 p_list_price => l_item_rec.list_price ,
2574 p_quantity => l_item_rec.quantity ,
2575 p_pricing_attribute1 => l_item_rec.pricing_attribute1 ,
2576 p_pricing_attribute2 => l_item_rec.pricing_attribute2 ,
2577 p_pricing_attribute3 => l_item_rec.pricing_attribute3 ,
2578 p_pricing_attribute4 => l_item_rec.pricing_attribute4 ,
2579 p_pricing_attribute5 => l_item_rec.pricing_attribute5 ,
2580 p_pricing_attribute6 => l_item_rec.pricing_attribute6 ,
2581 p_pricing_attribute7 => l_item_rec.pricing_attribute7 ,
2582 p_pricing_attribute8 => l_item_rec.pricing_attribute8 ,
2583 p_pricing_attribute9 => l_item_rec.pricing_attribute9 ,
2584 p_pricing_attribute10 => l_item_rec.pricing_attribute10 ,
2585 p_pricing_attribute11 => l_item_rec.pricing_attribute11 ,
2586 p_pricing_attribute12 => l_item_rec.pricing_attribute12 ,
2587 p_pricing_attribute13 => l_item_rec.pricing_attribute13 ,
2588 p_pricing_attribute14 => l_item_rec.pricing_attribute14 ,
2589 p_pricing_attribute15 => l_item_rec.pricing_attribute15 ,
2590 p_pricing_date => l_item_rec.pricing_date ,
2591 p_unit_code => l_item_rec.unit_code ,
2592 p_ship_to_id => l_item_rec.ship_to_site_use_id ,
2593 p_item_category_id => l_item_rec.item_category_id ,
2594 p_sold_to_org_id => l_item_rec.sold_to_org_id ,
2595 p_customer_class_code => l_item_rec.customer_class_code ,
2596 p_invoice_to_id => l_item_rec.invoice_to_org_id,
2597 p_po_number => l_item_rec.po_number ,
2598 p_agreement_id => l_item_rec.agreement_id ,
2599 p_agreement_type_code => l_item_rec.agreement_type_code ,
2600 p_order_type_id => l_item_rec.order_type_id ,
2601 p_gsa => l_item_rec.gsa ,
2602 p_adj_rec => l_adj_rec
2603 );
2604 --DBMS_output.put_line('After fetch best adjustment '||l_item_rec.list_price);
2605 -- Debug info
2606 /*
2607 OE_MSG_PUB.Add_Exc_Msg
2608 ( p_error_text => 'After calling fetch_best_adj - '||
2609 ' adj_id = '||l_adj_rec.adjustment_id||
2610 ' percent = '||l_adj_rec.percent
2611 );
2612 */
2613 -- Add automatic adjustment retrieved by Fetch_Best_Adjustment
2614
2615 IF l_adj_rec.percent IS NOT NULL THEN
2616
2617 l_adj_total := l_adj_total + l_adj_rec.percent;
2618
2619 -- Validate the adjustment total
2620
2621 IF l_adj_total > 100 THEN
2622
2623 -- Not an error condition, we just can't add the new adjustment.
2624 -- Decrement the total with the amount we just added, and add
2625 -- a message describing the failed attempt to add the adjustment.
2626
2627 l_adj_total := l_adj_total - l_adj_rec.percent;
2628
2629
2630 FND_MESSAGE.SET_NAME('QP','OE_PRC_AUTOADJUSTMENT_OVERFLOW');
2631 FND_MESSAGE.SET_TOKEN('DISCOUNT', l_adj_rec.discount_name );
2632 OE_MSG_PUB.Add;
2633
2634
2635 ELSE
2636
2637 -- Add the new adjustment, if any, to the p_adj_out_table
2638
2639 -- Ensure operation is set to create
2640 l_adj_rec.operation := G_PRC_OPR_CREATE;
2641
2642 -- Add adjustment to p_adj_out_table
2643 p_adj_out_table(1) := l_adj_rec;
2644
2645 -- Add a message to the message list to inform the user
2646 -- that an adjustment has been applied to this line.
2647
2648 -- this code should be moved to the part when we process
2649 -- adjustments, but since we don't do actual inserts the
2650 -- code will temporarily reside here.
2651
2652 if l_item_rec.agreement_id is null then
2653 FND_MESSAGE.SET_NAME('QP','OE_PRC_AUTOADJUSTMENT_APPLIED');
2654 FND_MESSAGE.SET_TOKEN('DISCOUNT', l_adj_rec.discount_name );
2655 OE_MSG_PUB.Add;
2656 end if;
2657
2658
2659 END IF;
2660
2661 END IF;
2662
2663
2664 -- Calculate Selling Price
2665
2666 Calculate_Selling_Price
2667 ( p_adj_total => l_adj_total ,
2668 p_list_price => l_item_rec.list_price ,
2669 p_list_percent => l_item_rec.list_percent ,
2670 p_price_list_id => l_item_rec.price_list_id ,
2671 p_base_price => l_item_rec.base_price ,
2672 p_service_duration => l_item_rec.service_duration ,
2673 p_pricing_method_code => l_item_rec.price_method_code ,
2674 p_selling_price => p_selling_price ,
2675 p_selling_percent => p_selling_percent ,
2676 p_list_price_out => p_list_price_out
2677 );
2678
2679 oe_debug_pub.add('Exiting QP_PRC_UTIL.PRICE_ITEM', 1);
2680
2681 EXCEPTION
2682
2683 WHEN FND_API.G_EXC_ERROR THEN
2684
2685 p_return_status := FND_API.G_RET_STS_ERROR;
2686
2687
2688 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2689
2690 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2691
2692
2693 WHEN OTHERS THEN
2694
2695 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2696
2697 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2698 OE_MSG_PUB.Add_Exc_Msg
2699 ( G_PKG_NAME ,
2700 'Price_Item'
2701 );
2702 END IF;
2703
2704 END; -- Price_Item
2705
2706 --QP Initialization
2707 BEGIN
2708 QP_UTIL.GET_CONTEXT_ATTRIBUTE('SOLD_TO_ORG_ID'
2709 ,G_SOLD_TO_ORG_CONTEXT
2710 ,G_SOLD_TO_ORG_NAME);
2711
2712 QP_UTIL.GET_CONTEXT_ATTRIBUTE('SITE_ORG_ID'
2713 ,G_SITE_ORG_CONTEXT
2714 ,G_SITE_ORG_NAME);
2715
2716 QP_UTIL.GET_CONTEXT_ATTRIBUTE('UNITS'
2717 ,G_UNIT_CONTEXT
2718 ,G_UNIT_NAME);
2719
2720 QP_UTIL.GET_CONTEXT_ATTRIBUTE('CUSTOMER_CLASS_CODE'
2721 ,G_CUSTOMER_CLASS_CONTEXT
2722 ,G_CUSTOMER_CLASS_NAME);
2723
2724 QP_UTIL.GET_CONTEXT_ATTRIBUTE('1001'
2725 ,G_ITEM_CONTEXT
2726 ,G_ITEM_NAME);
2727
2728 QP_UTIL.GET_CONTEXT_ATTRIBUTE('1045'
2729 ,G_ITEM_CATEGORY_CONTEXT
2730 ,G_ITEM_CATEGORY_NAME);
2731
2732 QP_UTIL.GET_CONTEXT_ATTRIBUTE('1004'
2733 ,G_CUSTOMER_PO_CONTEXT
2734 ,G_CUSTOMER_PO_NAME);
2735
2736 QP_UTIL.GET_CONTEXT_ATTRIBUTE('1005'
2737 ,G_AGREEMENT_TYPE_CONTEXT
2738 ,G_AGREEMENT_TYPE_NAME);
2739
2740 QP_UTIL.GET_CONTEXT_ATTRIBUTE('1006'
2741 ,G_AGREEMENT_CONTEXT
2742 ,G_AGREEMENT_NAME);
2743
2744 QP_UTIL.GET_CONTEXT_ATTRIBUTE('1007'
2745 ,G_ORDER_TYPE_CONTEXT
2746 ,G_ORDER_TYPE_NAME);
2747
2748 QP_UTIL.GET_CONTEXT_ATTRIBUTE('DOLLARS'
2749 ,G_DOLLARS_CONTEXT
2750 ,G_DOLLARS_NAME);
2751
2752 QP_UTIL.GET_CONTEXT_ATTRIBUTE('PRICE_LIST_ID'
2753 ,G_PRICE_LIST_CONTEXT
2754 ,G_PRICE_LIST_NAME);
2755
2756
2757 -- END QP
2758 END QP_PRC_UTIL;
2759