[Home] [Help]
PACKAGE BODY: APPS.QP_SOURCING_API_PUB
Source
1 PACKAGE BODY QP_SOURCING_API_PUB AS
2 /* $Header: QPXPSAPB.pls 120.8 2006/03/14 15:32:07 jhkuo ship $ */
3
4 l_debug VARCHAR2(3);
5 G_GSA_INDICATOR_FLAGS QP_PREQ_GRP.FLAG_TYPE;
6
7 Procedure Get_Customer_Info(p_cust_id NUMBER)
8 IS
9
10 --TYPE t_cursor IS REF CURSOR;
11
12 l_account_type_id VARCHAR2(30);
13 v_count NUMBER := 1;
14 --l_acct_type_cursor t_cursor;
15 l_realted_cust_id VARCHAR2(30);
16 --l_related_cust_cursor t_cursor;
17 --
18 CURSOR l_acct_type_cursor (cust_id_in number) is
19 SELECT distinct profile_class_id
20 FROM HZ_CUSTOMER_PROFILES
21 WHERE cust_account_id = cust_id_in;
22 CURSOR l_related_cust_cursor(cust_id_in number) is
23 SELECT RELATED_CUST_ACCOUNT_ID
24 FROM HZ_CUST_ACCT_RELATE
25 WHERE cust_account_id = cust_id_in;
26
27 NUMERIC_OVERFLOW EXCEPTION;
28 NUMERIC_OR_VALUE EXCEPTION;
29 PRAGMA EXCEPTION_INIT(NUMERIC_OVERFLOW, -1426);
30 PRAGMA EXCEPTION_INIT(NUMERIC_OR_VALUE, -6502);
31
32 BEGIN
33
34 if qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_yes then
35
36 G_Customer_Info.customer_id := p_cust_id;
37
38 -- Getting info from HZ_CUST_ACCOUNTS
39 BEGIN
40 SELECT customer_class_code, sales_channel_code
41 INTO G_Customer_Info.customer_class_code, G_Customer_Info.sales_channel_code
42 FROM hz_cust_accounts
43 WHERE cust_account_id = p_cust_id;
44
45 EXCEPTION
46 WHEN no_data_found THEN
47 G_Customer_Info.customer_class_code := null;
48 G_Customer_Info.sales_channel_code := null;
49
50 END;
51
52 -- Getting GSA info
53
54 BEGIN
55 SELECT NVL(gsa_indicator,'N')
56 INTO G_Customer_Info.gsa_indicator
57 FROM hz_cust_site_uses hsu
58 WHERE site_use_id = OE_ORDER_PUB.G_HDR.invoice_to_org_id
59 --added for moac
60 and hsu.org_id = QP_ATTR_MAPPING_PUB.G_ORG_ID;
61 /*
62 AND NVL(hsu.org_id,
63 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1), ' ',NULL,
64 SUBSTRB(USERENV('CLIENT_INFO'), 1,10))),-99)) =
65 NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',NULL,
66 SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99);
67 */
68
69 EXCEPTION
70
71 WHEN NO_DATA_FOUND THEN
72 G_Customer_Info.gsa_indicator := 'N';
73
74 END;
75
76 IF G_Customer_Info.gsa_indicator = 'N' THEN
77
78 BEGIN
79
80 BEGIN
81 G_Customer_Info.gsa_indicator := G_GSA_INDICATOR_FLAGS(p_cust_id);
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 G_Customer_Info.gsa_indicator := null;
85 END;
86
87 IF( G_Customer_Info.gsa_indicator is null) THEN
88 SELECT NVL(gsa_indicator_flag,'N')
89 into G_Customer_Info.gsa_indicator
90 from hz_parties hp,hz_cust_accounts hca
91 where hp.party_id = hca.party_id
92 and hca.cust_account_id = p_cust_id ;
93
94 G_GSA_INDICATOR_FLAGS(p_cust_id) := G_Customer_Info.gsa_indicator;
95 END IF;
96 EXCEPTION
97 WHEN NO_DATA_FOUND THEN
98 G_Customer_Info.gsa_indicator := null;
99 WHEN NUMERIC_OVERFLOW THEN
100 G_Customer_Info.gsa_indicator := null;
101 WHEN NUMERIC_OR_VALUE THEN
102 G_Customer_Info.gsa_indicator := null;
103
104 END;
105
106 END IF;
107
108
109 -- Getting Account Types
110
111 /*
112 OPEN l_acct_type_cursor FOR
113 SELECT distinct customer_profile_class_id
114 FROM AR_CUSTOMER_PROFILES
115 WHERE customer_id = p_cust_id;
116 */
117 OPEN l_acct_type_cursor(p_cust_id);
118 LOOP
119
120 FETCH l_acct_type_cursor INTO l_account_type_id;
121 EXIT WHEN l_acct_type_cursor%NOTFOUND;
122
123 G_Customer_Info.account_types(v_count) := l_account_type_id;
124 v_count := v_count + 1;
125
126 END LOOP;
127
128 CLOSE l_acct_type_cursor;
129
130 -- Get Customer Relationships
131
132 v_count := 1;
133
134 /*
135 OPEN l_related_cust_cursor FOR
136 SELECT RELATED_CUSTOMER_ID
137 FROM RA_CUSTOMER_RELATIONSHIPS
138 WHERE customer_id = p_cust_id;
139 */
140
141 OPEN l_related_cust_cursor(p_cust_id);
142 LOOP
143
144 FETCH l_related_cust_cursor INTO l_realted_cust_id;
145 EXIT WHEN l_related_cust_cursor%NOTFOUND;
146
147 G_Customer_Info.customer_relationships(v_count) := l_realted_cust_id;
148 v_count := v_count + 1;
149
150 END LOOP;
151
152 CLOSE l_related_cust_cursor;
153
154 end if;
155
156 END;
157
158
159 FUNCTION Get_GSA (p_cust_id NUMBER) RETURN VARCHAR2
160 IS
161
162 BEGIN
163
164 get_customer_info(p_cust_id);
165 return g_customer_info.gsa_indicator;
166
167 /*
168 if G_Customer_Info.customer_id = p_cust_id then
169 return G_Customer_Info.gsa_indicator;
170 else
171 Get_Customer_Info(p_cust_id);
172 return G_Customer_Info.gsa_indicator;
173 end if;
174 */
175
176 END;
177
178 FUNCTION Get_Customer_Item_Id (p_item_type VARCHAR2, p_ordered_item_id NUMBER) RETURN NUMBER
179 IS
180
181 BEGIN
182
183 if p_item_type = 'CUST' then
184 return p_ordered_item_id;
185 else
186 return NULL;
187 end if;
188
189 END;
190
191 FUNCTION Get_Sales_Channel (p_cust_id IN NUMBER) RETURN VARCHAR2
192 IS
193
194 BEGIN
195
196 get_customer_info(p_cust_id);
197 return g_customer_info.sales_channel_code;
198
199 /*
200 if G_Customer_Info.customer_id = p_cust_id then
201 return G_Customer_Info.sales_channel_code;
202 else
203 Get_Customer_Info(p_cust_id);
204 return G_Customer_Info.sales_channel_code;
205 end if;
206 */
207
208 END Get_Sales_Channel;
209
210 FUNCTION Get_Site_Use (p_invoice_to_org_id IN NUMBER, p_ship_to_org_id IN NUMBER) RETURN QP_Attr_Mapping_PUB.t_MultiRecord
211 IS
212
213 x_site_use_info QP_Attr_Mapping_PUB.t_MultiRecord;
214
215 BEGIN
216
217 IF p_ship_to_org_id is not null THEN
218 IF p_invoice_to_org_id is not null THEN
219 IF p_ship_to_org_id = p_invoice_to_org_id THEN
220 x_site_use_info(1) := p_ship_to_org_id;
221 ELSE
222 x_site_use_info(1) := p_ship_to_org_id;
223 x_site_use_info(2) := p_invoice_to_org_id;
224 END IF;
225 ELSE
226 x_site_use_info(1) := p_ship_to_org_id;
227 END IF;
228 ELSE IF p_invoice_to_org_id is not null THEN
229 x_site_use_info(1) := p_invoice_to_org_id;
230 END IF;
231 END IF;
232
233 RETURN x_site_use_info;
234
235 END Get_Site_Use;
236
237 FUNCTION Get_Item_Category (p_inventory_item_id IN NUMBER) RETURN QP_Attr_Mapping_PUB.t_MultiRecord
238 IS
239 x_category_ids QP_Attr_Mapping_PUB.t_MultiRecord;
240 BEGIN
241 -- Exploded Category Ids to be picked in case on PLM and Sales n marketing.
242 x_category_ids := QP_CATEGORY_MAPPING_RULE.Get_Item_category(p_inventory_item_id);
243 RETURN(x_category_ids);
244
245 END Get_Item_Category;
246
247 /* Added Sourcing API Get_Agreement_Revisions for Bug 2293711*/
248 FUNCTION Get_Agreement_Revisions (p_agreement_id IN NUMBER) RETURN
249
250 QP_Attr_Mapping_PUB.t_MultiRecord
251 IS
252
253 --TYPE t_cursor IS REF CURSOR;
254
255 x_agreement_ids QP_Attr_Mapping_PUB.t_MultiRecord;
256 l_agreement_id VARCHAR2(30);
257 v_count NUMBER := 1;
258 --l_agreement_cursor t_cursor;
259 l_name varchar2(240);
260 --
261 CURSOR l_agreement_cursor(name_in varchar2) is
262 SELECT agreement_id
263 FROM oe_agreements_vl
264 WHERE name = name_in;
265 --
266 BEGIN
267
268 select name into l_name
269 from oe_agreements_vl
270 WHERE agreement_id = p_agreement_id
271 AND ROWNUM=1;
272
273 /*
274 OPEN l_agreement_cursor FOR
275 SELECT agreement_id
276 FROM oe_agreements_vl
277 WHERE name = l_name;
278 */
279
280 OPEN l_agreement_cursor(l_name);
281 LOOP
282
283 FETCH l_agreement_cursor INTO l_agreement_id;
284 EXIT WHEN l_agreement_cursor%NOTFOUND;
285
286 x_agreement_ids(v_count) := l_agreement_id;
287 v_count := v_count + 1;
288
289 END LOOP;
290
291 CLOSE l_agreement_cursor;
292
293 RETURN x_agreement_ids;
294
295 exception
296 when no_data_found then
297 RETURN x_agreement_ids;
298
299 END Get_Agreement_Revisions;
300
301
302 PROCEDURE Get_Item_Segments_All(p_inventory_item_id IN NUMBER)
303 IS
304
305 l_org_id NUMBER := QP_UTIL.Get_Item_Validation_Org;
306
307 BEGIN
308
309 G_Item_Segments.inventory_item_id := p_inventory_item_id;
310
311 SELECT segment1,segment2,segment3,segment4,segment5,
312 segment6,segment7,segment8,segment9,segment10,
313 segment11,segment12,segment13,segment14,segment15,
314 segment16,segment17,segment18,segment19,segment20
315 INTO G_Item_Segments.segment1,G_Item_Segments.segment2,G_Item_Segments.segment3,
316 G_Item_Segments.segment4,G_Item_Segments.segment5,G_Item_Segments.segment6,
317 G_Item_Segments.segment7,G_Item_Segments.segment8,G_Item_Segments.segment9,
318 G_Item_Segments.segment10,G_Item_Segments.segment11,G_Item_Segments.segment12,
319 G_Item_Segments.segment13,G_Item_Segments.segment14,G_Item_Segments.segment15,
320 G_Item_Segments.segment16,G_Item_Segments.segment17,G_Item_Segments.segment18,
321 G_Item_Segments.segment19,G_Item_Segments.segment20
322 FROM mtl_system_items
323 WHERE inventory_item_id = p_inventory_item_id
324 AND organization_id = l_org_id;
325
326 END;
327
328 FUNCTION Get_Item_Segment(p_inventory_item_id IN NUMBER, p_seg_num NUMBER) RETURN VARCHAR2
329 IS
330
331 l_segment_name VARCHAR2(30);
332
333 BEGIN
334 -- Added for 2623767
335 IF p_inventory_item_id IS NULL THEN
336 RETURN NULL;
337 END IF;
338
339
340 IF (p_inventory_item_id <> G_Item_Segments.inventory_item_id)
341 OR G_Item_Segments.inventory_item_id IS NULL THEN -- Added for 2512883
342
343 Get_Item_Segments_All(p_inventory_item_id);
344 END IF;
345
346 IF p_seg_num = 1 THEN
347 RETURN G_Item_Segments.segment1;
348 ELSIF p_seg_num = 2 THEN
349 RETURN G_Item_Segments.segment2;
350 ELSIF p_seg_num = 3 THEN
351 RETURN G_Item_Segments.segment3;
352 ELSIF p_seg_num = 4 THEN
353 RETURN G_Item_Segments.segment4;
354 ELSIF p_seg_num = 5 THEN
355 RETURN G_Item_Segments.segment5;
356 ELSIF p_seg_num = 6 THEN
357 RETURN G_Item_Segments.segment6;
358 ELSIF p_seg_num = 7 THEN
359 RETURN G_Item_Segments.segment7;
360 ELSIF p_seg_num = 8 THEN
361 RETURN G_Item_Segments.segment8;
362 ELSIF p_seg_num = 9 THEN
363 RETURN G_Item_Segments.segment9;
364 ELSIF p_seg_num = 10 THEN
365 RETURN G_Item_Segments.segment10;
366 ELSIF p_seg_num = 11 THEN
367 RETURN G_Item_Segments.segment11;
368 ELSIF p_seg_num = 12 THEN
369 RETURN G_Item_Segments.segment12;
370 ELSIF p_seg_num = 13 THEN
371 RETURN G_Item_Segments.segment13;
372 ELSIF p_seg_num = 14 THEN
373 RETURN G_Item_Segments.segment14;
374 ELSIF p_seg_num = 15 THEN
375 RETURN G_Item_Segments.segment15;
376 ELSIF p_seg_num = 16 THEN
377 RETURN G_Item_Segments.segment16;
378 ELSIF p_seg_num = 17 THEN
379 RETURN G_Item_Segments.segment17;
380 ELSIF p_seg_num = 18 THEN
381 RETURN G_Item_Segments.segment18;
382 ELSIF p_seg_num = 19 THEN
383 RETURN G_Item_Segments.segment19;
384 ELSIF p_seg_num = 20 THEN
385 RETURN G_Item_Segments.segment20;
386 END IF;
387 END;
388
389
390 FUNCTION Get_Customer_Class(p_cust_id IN NUMBER) RETURN VARCHAR2
391 IS
392
393 BEGIN
394
395 get_customer_info(p_cust_id);
396 return g_customer_info.customer_class_code;
397
398 /*
399 if G_Customer_Info.customer_id = p_cust_id then
400 return G_Customer_Info.customer_class_code;
401 else
402 Get_Customer_Info(p_cust_id);
403 return G_Customer_Info.customer_class_code;
404 end if;
405 */
406
407 END Get_Customer_Class;
408
409 PROCEDURE Get_Order_AMT_and_QTY (p_header_id IN NUMBER)
410 IS
411
412 orders_total_amt NUMBER := 0;
413 orders_total_qty NUMBER := 0;
414 returns_total_amt NUMBER := 0;
415 returns_total_qty NUMBER := 0;
416
417 BEGIN
418
419 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
420 if qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_yes then
421 --Bug 2718722, Added exception handling and replaced pricing_quantity by ordered_quantity
422 If QP_UTIL_PUB.HVOP_Pricing_On = 'Y' Then
423
424 For i in QP_BULK_PREQ_GRP.G_Line_Rec.header_id.first..QP_BULK_PREQ_GRP.G_Line_Rec.header_id.last
425 Loop
426 If QP_BULK_PREQ_GRP.G_Line_Rec.header_id(i) = p_header_id
427 AND
428 NVL(QP_BULK_PREQ_GRP.G_Line_Rec.cancelled_flag(i),'N')='N'
429
430 Then
431 If NVL(QP_BULK_PREQ_GRP.G_Line_Rec.line_category_code(i),'N')<>'RETURN' --order
432 Then
433 orders_total_amt := orders_total_amt + nvl(QP_BULK_PREQ_GRP.G_Line_Rec.ordered_quantity(i),0)*(QP_BULK_PREQ_GRP.G_Line_Rec.unit_list_price(i));
434 orders_total_qty := orders_total_qty + nvl(QP_BULK_PREQ_GRP.G_Line_Rec.ordered_quantity(i),0);
435 Else --return
436 returns_total_amt := returns_total_amt + nvl(QP_BULK_PREQ_GRP.G_Line_Rec.ordered_quantity(i),0)*(QP_BULK_PREQ_GRP.G_Line_Rec.unit_list_price(i));
437 returns_total_qty := returns_total_qty + nvl(QP_BULK_PREQ_GRP.G_Line_Rec.ordered_quantity(i),0);
438 End If; --order/return
439 End If;
440
441 End Loop;
442
443 Else -- Non -HVOP
444
445 /* The next two SQLs clubbed together to fix Bug. 3607932
446 Begin
447 SELECT SUM(nvl(ordered_quantity,0)*(unit_list_price)),
448 SUM(nvl(ordered_quantity,0))
449 INTO orders_total_amt, orders_total_qty
450 FROM oe_order_lines
451 WHERE header_id=p_header_id
452 AND (cancelled_flag='N' OR cancelled_flag IS NULL)
453 AND (line_category_code<>'RETURN' OR line_category_code IS NULL)
454 GROUP BY header_id;
455
456 EXCEPTION
457 WHEN no_data_found THEN
458 IF l_debug = FND_API.G_TRUE THEN
459 OE_debug_pub.add('No ORDER Line Found');
460 END IF;
461
462 End;
463
464 Begin
465 SELECT SUM(nvl(ordered_quantity,0)*(unit_list_price)),
466 SUM(nvl(ordered_quantity,0))
467 INTO returns_total_amt, returns_total_qty
468 FROM oe_order_lines
469 WHERE header_id=p_header_id
470 AND (cancelled_flag='N' OR cancelled_flag IS NULL)
471 AND line_category_code='RETURN'
472 GROUP BY header_id;
473
474 EXCEPTION
475 WHEN no_data_found THEN
476 IF l_debug = FND_API.G_TRUE THEN
477 OE_debug_pub.add('From NO Data Found');
478 END IF;
479 End;
480 */
481
482 Begin
483 SELECT nvl(sum(decode(line_category_code,'RETURN',
484 nvl(ordered_quantity,0)*(unit_list_price),0)),0),
485 nvl(sum(decode(line_category_code,'RETURN',0,
486 nvl(ordered_quantity,0)*(unit_list_price))),0),
487 nvl(sum(decode(line_category_code,'RETURN',
488 nvl(ordered_quantity,0),0)),0),
489 nvl(sum(decode(line_category_code,'RETURN',0,
490 nvl(ordered_quantity,0))),0)
491 INTO returns_total_amt,
492 orders_total_amt,
493 returns_total_qty,
494 orders_total_qty
495 FROM oe_order_lines
496 WHERE header_id=p_header_id
497 AND (cancelled_flag='N' OR cancelled_flag IS NULL)
498 AND charge_periodicity_code is null; -- added for recurring charges Bug 4465168
499
500 EXCEPTION
501 WHEN no_data_found THEN
502 IF l_debug = FND_API.G_TRUE THEN
503 OE_debug_pub.add('From NO Data Found');
504 END IF;
505 End;
506
507 End If; --HVOP_Pricing_On
508 G_Order_Info.header_id := p_header_id;
509 G_Order_Info.order_amount := QP_NUMBER.NUMBER_TO_CANONICAL(NVL(orders_total_amt,0)-NVL(returns_total_amt,0));
510 G_Order_Info.order_quantity := QP_NUMBER.NUMBER_TO_CANONICAL(NVL(orders_total_qty,0)-NVL(returns_total_qty,0));
511
512 end if; --new pricing call
513 END;
514
515 FUNCTION Get_Order_Qty (p_header_id IN NUMBER) RETURN VARCHAR2
516 IS
517
518 BEGIN
519
520 get_order_amt_and_qty(p_header_id);
521 return g_order_info.order_quantity;
522
523 END Get_Order_Qty;
524
525
526 FUNCTION Get_Order_Amount(p_header_id IN NUMBER) RETURN VARCHAR2
527 IS
528
529 BEGIN
530
531 get_order_amt_and_qty(p_header_id);
532 return g_order_info.order_amount;
533
534 END Get_Order_Amount;
535
536
537 FUNCTION Get_Account_Type (p_cust_id IN NUMBER) RETURN QP_Attr_Mapping_PUB.t_MultiRecord
538 IS
539
540 BEGIN
541
542 get_customer_info(p_cust_id);
543 return g_customer_info.account_types;
544
545 END Get_Account_Type;
546
547
548 FUNCTION Get_Agreement_Type (p_agreement_id IN VARCHAR2) RETURN VARCHAR2
549 IS
550
551 BEGIN
552
553 IF p_agreement_id = G_Agreement_Info.agreement_id THEN
554
555 RETURN G_Agreement_Info.agreement_type_code;
556
557 ELSE
558
559 G_Agreement_Info.agreement_id := p_agreement_id;
560 G_Agreement_Info.agreement_type_code := NULL; -- bug#3276930
561
562 SELECT agreement_type_code
563 INTO G_Agreement_Info.agreement_type_code
564 FROM oe_agreements
565 WHERE agreement_id = p_agreement_id;
566
567 RETURN G_Agreement_Info.agreement_type_code;
568
569 END IF;
570
571 END Get_Agreement_Type;
572
573 FUNCTION Get_Model_Id (p_top_model_line_id IN NUMBER) RETURN NUMBER
574 IS
575
576 BEGIN
577 /* Changed the IF condition for bug#2639759 */
578 IF (p_top_model_line_id = G_TOP_MODEL_LINE_ID
579 AND G_MODEL_ID IS NOT NULL) THEN
580
581 RETURN G_MODEL_ID;
582
583 ELSE
584
585 G_MODEL_ID := NULL;
586
587 If QP_UTIL_PUB.HVOP_Pricing_On = 'Y' Then --hvop
588
589 For i in QP_BULK_PREQ_GRP.G_Line_Rec.line_id.first..QP_BULK_PREQ_GRP.G_Line_Rec.line_id.last
590 Loop
591 If QP_BULK_PREQ_GRP.G_Line_Rec.line_id(i) = p_top_model_line_id Then
592 G_MODEL_ID := QP_BULK_PREQ_GRP.G_Line_Rec.inventory_item_id(i);
593 Exit;
594
595 End If;
596 End Loop;
597
598 Else --non hvop
599
600 SELECT inventory_item_id
601 INTO G_MODEL_ID
602 FROM oe_order_lines_all
603 WHERE line_id = p_top_model_line_id;
604
605 End If; --hvop
606
607 if (G_MODEL_ID is not null) then
608 G_TOP_MODEL_LINE_ID := p_top_model_line_id;
609 end if;
610 RETURN G_MODEL_ID;
611
612 END IF;
613
614 END Get_Model_Id;
615
616 FUNCTION Get_Customer_Relationship (p_cust_id IN NUMBER) RETURN QP_Attr_Mapping_PUB.t_MultiRecord
617 IS
618
619 BEGIN
620
621 get_customer_info(p_cust_id);
622 return g_customer_info.customer_relationships;
623
624 END Get_Customer_Relationship;
625
626 FUNCTION Get_Period1_Item_Quantity(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER, p_ordered_uom VARCHAR2) RETURN VARCHAR2
627 IS
628
629 x_item_quantity NUMBER;
630 l_puom VARCHAR2(240);
631 x_Conv_quantity NUMBER;
632 --l_inv_precision NUMBER := FND_PROFILE.Value('QP_INV_DECIMAL_PRECISION');
633 l_inv_precision NUMBER := 40;
634
635 BEGIN
636
637 -- l_ordered_uom := OE_ORDER_PUB.G_LINE.order_quantity_uom;
638 oe_debug_pub.add('Inv Precision: ' || l_inv_precision);
639
640 BEGIN
641
642 SELECT period1_ordered_quantity, primary_uom_code
643 INTO x_item_quantity, l_puom
644 FROM oe_item_cust_vols
645 WHERE sold_to_org_id = p_cust_id
646 AND inventory_item_id = p_inventory_item_id;
647
648 EXCEPTION
649 WHEN no_data_found THEN
650 if ( (p_cust_id is not null) and (p_inventory_item_id is not null) ) THEN
651 return 0;
652 end if;
653 END;
654
655 x_Conv_quantity := QP_NUMBER.NUMBER_TO_CANONICAL(inv_convert.inv_um_convert(p_inventory_item_id,
656 l_inv_precision,
657 x_item_quantity,
658 l_puom,
659 p_ordered_uom,
660 NULL,NULL) );
661
662 If x_Conv_quantity = -99999 then
663 Return Null;
664 Else
665 Return x_Conv_quantity;
666 end if;
667
668 END Get_Period1_Item_Quantity;
669
670 FUNCTION Get_Period1_Item_Quantity(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER) RETURN VARCHAR2
671 IS
672
673 x_return number;
674
675 BEGIN
676
677 x_return := Get_Period1_Item_Quantity(p_cust_id , p_inventory_item_id , OE_ORDER_PUB.G_LINE.order_quantity_uom);
678
679 return x_return;
680
681 END;
682
683 FUNCTION Get_Period2_Item_Quantity(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER, p_ordered_uom VARCHAR2) RETURN VARCHAR2
684 IS
685
686 x_item_quantity NUMBER;
687 l_puom VARCHAR2(240);
688 x_Conv_quantity NUMBER;
689 --l_inv_precision NUMBER := FND_PROFILE.Value('QP_INV_DECIMAL_PRECISION');
690 l_inv_precision NUMBER := 40;
691
692 BEGIN
693
694 BEGIN
695
696 SELECT period2_ordered_quantity, primary_uom_code
697 INTO x_item_quantity, l_puom
698 FROM oe_item_cust_vols
699 WHERE sold_to_org_id = p_cust_id
700 AND inventory_item_id = p_inventory_item_id;
701
702 EXCEPTION
703 WHEN no_data_found THEN
704 if ( (p_cust_id is not null) and (p_inventory_item_id is not null) ) THEN
705 return 0;
706 end if;
707
708 END;
709
710 x_Conv_quantity := QP_NUMBER.NUMBER_TO_CANONICAL(inv_convert.inv_um_convert(p_inventory_item_id,
711 l_inv_precision,
712 x_item_quantity,
713 l_puom,
714 p_ordered_uom,
715 NULL,NULL) );
716 If x_Conv_quantity = -99999 then
717 Return Null;
718 Else
719 Return x_Conv_quantity;
720 end if;
721
722 END Get_Period2_Item_Quantity;
723
724 FUNCTION Get_Period2_Item_Quantity(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER) RETURN VARCHAR2
725 IS
726
727 x_return number;
728
729 BEGIN
730
731 x_return := Get_Period2_Item_Quantity(p_cust_id , p_inventory_item_id , OE_ORDER_PUB.G_LINE.order_quantity_uom);
732
733 return x_return;
734
735 END;
736
737 FUNCTION Get_Period3_Item_Quantity(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER, p_ordered_uom VARCHAR2) RETURN VARCHAR2
738 IS
739
740 x_item_quantity NUMBER;
741 l_puom VARCHAR2(240);
742 x_Conv_quantity NUMBER;
743 --l_inv_precision NUMBER := FND_PROFILE.Value('QP_INV_DECIMAL_PRECISION');
744 l_inv_precision NUMBER := 40;
745
746 BEGIN
747
748 BEGIN
749
750 SELECT period3_ordered_quantity, primary_uom_code
751 INTO x_item_quantity, l_puom
752 FROM oe_item_cust_vols
753 WHERE sold_to_org_id = p_cust_id
754 AND inventory_item_id = p_inventory_item_id;
755
756 EXCEPTION
757 WHEN no_data_found THEN
758 if ( (p_cust_id is not null) and (p_inventory_item_id is not null) ) THEN
759 return 0;
760 end if;
761
762 END;
763
764 x_Conv_quantity := QP_NUMBER.NUMBER_TO_CANONICAL(inv_convert.inv_um_convert(p_inventory_item_id,
765 l_inv_precision,
766 x_item_quantity,
767 l_puom,
768 p_ordered_uom,
769 NULL,NULL) );
770 If x_Conv_quantity = -99999 then
771 Return Null;
772 Else
773 Return x_Conv_quantity;
774 end if;
775
776 END Get_Period3_Item_Quantity;
777
778 FUNCTION Get_Period3_Item_Quantity(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER) RETURN VARCHAR2
779 IS
780
781 x_return number;
782
783 BEGIN
784
785 x_return := Get_Period3_Item_Quantity(p_cust_id , p_inventory_item_id , OE_ORDER_PUB.G_LINE.order_quantity_uom);
786
787 return x_return;
788
789 END;
790
791 FUNCTION Get_Period1_Item_Amount(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER, p_currency_code VARCHAR2, p_conversion_rate_date DATE, p_pricing_date DATE, p_conversion_rate NUMBER, p_conversion_type_code VARCHAR2) RETURN VARCHAR2
792 IS
793
794 x_amount NUMBER;
795 l_sob_id NUMBER;
796 l_coa_id NUMBER;
797 l_sob_name VARCHAR2(240);
798 l_err_buff VARCHAR2(2000);
799 l_sob_currency VARCHAR2(30);
800
801 BEGIN
802
803 BEGIN
804
805 SELECT period1_total_amount
806 INTO x_amount
807 FROM oe_item_cust_vols
808 WHERE sold_to_org_id = p_cust_id
809 AND inventory_item_id = p_inventory_item_id;
810
811 EXCEPTION
812 WHEN no_data_found THEN
813 if ( (p_cust_id is not null) and (p_inventory_item_id is not null) ) THEN
814 return 0;
815 end if;
816 END;
817
818 l_sob_id := FND_PROFILE.Value('OE_SET_OF_BOOKS_ID');
819
820 -- gl_info.gl_get_set_of_books_info(l_sob_id,l_coa_id,l_sob_name,l_sob_currency, l_err_buff);
821 -- Replaced with following select statement per bug 4537515 (sfiresto)
822 BEGIN
823 SELECT name, chart_of_accounts_id, currency_code
824 INTO l_sob_name, l_coa_id, l_sob_currency
825 FROM gl_ledgers_public_v
826 WHERE ledger_id = l_sob_id;
827 EXCEPTION
828 WHEN others THEN
829 l_err_buff := sqlerrm;
830 END;
831
832 --dbms_output.put_line('SOB Currency: ' || l_sob_currency);
833
834 If l_sob_currency <> p_currency_code Then
835
836 x_amount := QP_Cross_Order_Volume_Load.convert_to_base_curr(
837 p_trans_amount => x_amount,
838 p_From_currency => l_sob_currency,
839 p_to_currency => p_currency_code,
840 p_conversion_date => nvl( p_conversion_rate_date, p_pricing_date),
841 p_conversion_rate => p_conversion_rate,
842 p_conversion_type => p_conversion_type_code
843 );
844 End If;
845 /*
846 x_amount := gl_currency_api.convert_amount(l_sob_currency,
847 oe_order_pub.g_hdr.transactional_curr_code,
848 oe_order_pub.g_hdr.conversion_rate_date,
849 oe_order_pub.g_hdr.conversion_type_code,
850 x_amount);
851 */
852
853 RETURN QP_NUMBER.NUMBER_TO_CANONICAL(x_amount);
854
855 END Get_Period1_Item_Amount;
856
857 FUNCTION Get_Period1_Item_Amount(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER) RETURN VARCHAR2
858 IS
859
860 x_return number;
861
862 BEGIN
863
864 x_return := Get_Period1_Item_Amount(p_cust_id,
865 p_inventory_item_id,
866 oe_order_pub.g_hdr.transactional_curr_code,
867 oe_order_pub.g_hdr.conversion_rate_date,
868 oe_order_pub.g_hdr.Pricing_date,
869 oe_order_pub.g_hdr.conversion_rate,
870 oe_order_pub.g_hdr.conversion_type_code);
871
872 return x_return;
873
874 END;
875
876 FUNCTION Get_Period2_Item_Amount(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER, p_currency_code VARCHAR2, p_conversion_rate_date DATE, p_pricing_date DATE, p_conversion_rate NUMBER, p_conversion_type_code VARCHAR2) RETURN VARCHAR2
877 IS
878
879 x_amount NUMBER;
880 l_sob_id NUMBER;
881 l_coa_id NUMBER;
882 l_sob_name VARCHAR2(240);
883 l_err_buff VARCHAR2(2000);
884 l_sob_currency VARCHAR2(30);
885
886 BEGIN
887
888 BEGIN
889
890 SELECT period2_total_amount
891 INTO x_amount
892 FROM oe_item_cust_vols
893 WHERE sold_to_org_id = p_cust_id
894 AND inventory_item_id = p_inventory_item_id;
895
896 EXCEPTION
897 WHEN no_data_found THEN
898 if ( (p_cust_id is not null) and (p_inventory_item_id is not null) ) THEN
899 return 0;
900 end if;
901
902 END;
903
904 l_sob_id := FND_PROFILE.Value('OE_SET_OF_BOOKS_ID');
905
906 -- gl_info.gl_get_set_of_books_info(l_sob_id,l_coa_id,l_sob_name,l_sob_currency,l_err_buff);
907 -- Replaced with following select statement per bug 4537515 (sfiresto)
908 BEGIN
909 SELECT name, chart_of_accounts_id, currency_code
910 INTO l_sob_name, l_coa_id, l_sob_currency
911 FROM gl_ledgers_public_v
912 WHERE ledger_id = l_sob_id;
913 EXCEPTION
914 WHEN others THEN
915 l_err_buff := sqlerrm;
916 END;
917
918 If l_sob_currency <> p_currency_code Then
919
920 x_amount := QP_Cross_Order_Volume_Load.convert_to_base_curr(
921 p_trans_amount => x_amount,
922 p_From_currency => l_sob_currency,
923 p_to_currency => p_currency_code,
924 p_conversion_date => nvl(p_conversion_rate_date, p_pricing_date),
925 p_conversion_rate => p_conversion_rate,
926 p_conversion_type => p_conversion_type_code
927 );
928 End If;
929
930 /*
931 x_amount := gl_currency_api.convert_amount(l_sob_currency,
932 oe_order_pub.g_hdr.transactional_curr_code,
933 oe_order_pub.g_hdr.conversion_rate_date,
934 oe_order_pub.g_hdr.conversion_type_code,
935 x_amount);
936
937 */
938 RETURN QP_NUMBER.NUMBER_TO_CANONICAL(x_amount);
939
940 END Get_Period2_Item_Amount;
941
942 FUNCTION Get_Period2_Item_Amount(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER) RETURN VARCHAR2
943 IS
944
945 x_return number;
946
947 BEGIN
948
949 x_return := Get_Period2_Item_Amount(p_cust_id,
950 p_inventory_item_id,
951 oe_order_pub.g_hdr.transactional_curr_code,
952 oe_order_pub.g_hdr.conversion_rate_date,
953 oe_order_pub.g_hdr.Pricing_date,
954 oe_order_pub.g_hdr.conversion_rate,
955 oe_order_pub.g_hdr.conversion_type_code);
956
957 return x_return;
958
959 END;
960
961 FUNCTION Get_Period3_Item_Amount(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER, p_currency_code VARCHAR2, p_conversion_rate_date DATE, p_pricing_date DATE, p_conversion_rate NUMBER, p_conversion_type_code VARCHAR2) RETURN VARCHAR2
962 IS
963
964 x_amount NUMBER;
965 l_sob_id NUMBER;
966 l_coa_id NUMBER;
967 l_sob_name VARCHAR2(240);
968 l_err_buff VARCHAR2(2000);
969 l_sob_currency VARCHAR2(30);
970
971 BEGIN
972
973 BEGIN
974
975 SELECT period3_total_amount
976 INTO x_amount
977 FROM oe_item_cust_vols
978 WHERE sold_to_org_id = p_cust_id
979 AND inventory_item_id = p_inventory_item_id;
980
981 EXCEPTION
982 WHEN no_data_found THEN
983 if ( (p_cust_id is not null) and (p_inventory_item_id is not null) ) THEN
984 return 0;
985 end if;
986 END;
987
988 l_sob_id := FND_PROFILE.Value('OE_SET_OF_BOOKS_ID');
989
990 -- gl_info.gl_get_set_of_books_info(l_sob_id,l_coa_id,l_sob_name,l_sob_currency, l_err_buff);
991 -- Replaced with following select statement per bug 4537515 (sfiresto)
992 BEGIN
993 SELECT name, chart_of_accounts_id, currency_code
994 INTO l_sob_name, l_coa_id, l_sob_currency
995 FROM gl_ledgers_public_v
996 WHERE ledger_id = l_sob_id;
997 EXCEPTION
998 WHEN others THEN
999 l_err_buff := sqlerrm;
1000 END;
1001
1002 If l_sob_currency <> p_currency_code Then
1003
1004 x_amount := QP_Cross_Order_Volume_Load.convert_to_base_curr(
1005 p_trans_amount => x_amount,
1006 p_From_currency => l_sob_currency,
1007 p_to_currency => p_currency_code,
1008 p_conversion_date => nvl(p_conversion_rate_date, p_pricing_date),
1009 p_conversion_rate => p_conversion_rate,
1010 p_conversion_type => p_conversion_type_code
1011 );
1012 End If;
1013
1014
1015 /* Old Code
1016 If l_sob_currency <> oe_order_pub.g_hdr.transactional_curr_code Then
1017
1018 x_amount := QP_Cross_Order_Volume_Load.convert_to_base_curr(
1019 p_trans_amount => x_amount,
1020 p_From_currency => l_sob_currency,
1021 p_to_currency => oe_order_pub.g_hdr.transactional_curr_code,
1022 p_conversion_date => nvl(oe_order_pub.g_hdr.conversion_rate_date,
1023 oe_order_pub.g_hdr.Pricing_date),
1024 p_conversion_rate => oe_order_pub.g_hdr.conversion_rate,
1025 p_conversion_type => oe_order_pub.g_hdr.conversion_type_code
1026 );
1027 End If;
1028 */
1029
1030 RETURN QP_NUMBER.NUMBER_TO_CANONICAL(x_amount);
1031
1032 END Get_Period3_Item_Amount;
1033
1034 FUNCTION Get_Period3_Item_Amount(p_cust_id IN NUMBER, p_inventory_item_id IN NUMBER) RETURN VARCHAR2
1035 IS
1036
1037 x_return number;
1038
1039 BEGIN
1040
1041 x_return := Get_Period3_Item_Amount(p_cust_id,
1042 p_inventory_item_id,
1043 oe_order_pub.g_hdr.transactional_curr_code,
1044 oe_order_pub.g_hdr.conversion_rate_date,
1045 oe_order_pub.g_hdr.Pricing_date,
1046 oe_order_pub.g_hdr.conversion_rate,
1047 oe_order_pub.g_hdr.conversion_type_code);
1048
1049 return x_return;
1050
1051 END;
1052
1053
1054 FUNCTION Get_Period1_Order_Amount(p_cust_id IN NUMBER, p_currency_code VARCHAR2, p_conversion_rate_date DATE, p_pricing_date DATE, p_conversion_rate NUMBER, p_conversion_type_code VARCHAR2) RETURN VARCHAR2
1055 IS
1056
1057 x_total_amount NUMBER;
1058 l_sob_id NUMBER;
1059 l_coa_id NUMBER;
1060 l_sob_name VARCHAR2(240);
1061 l_err_buff VARCHAR2(2000);
1062 l_sob_currency VARCHAR2(30);
1063
1064 BEGIN
1065
1066 if qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_yes then
1067
1068 BEGIN
1069
1070 SELECT period1_total_amount
1071 INTO x_total_amount
1072 FROM oe_cust_total_amts
1073 WHERE sold_to_org_id = p_cust_id;
1074
1075 EXCEPTION
1076 WHEN no_data_found THEN
1077 if (p_cust_id is not null) THEN
1078 return 0;
1079 end if;
1080 END;
1081
1082 l_sob_id := FND_PROFILE.Value('OE_SET_OF_BOOKS_ID');
1083
1084 -- gl_info.gl_get_set_of_books_info(l_sob_id,l_coa_id,l_sob_name,l_sob_currency,l_err_buff);
1085 -- Replaced with following select statement per bug 4537515 (sfiresto)
1086 BEGIN
1087 SELECT name, chart_of_accounts_id, currency_code
1088 INTO l_sob_name, l_coa_id, l_sob_currency
1089 FROM gl_ledgers_public_v
1090 WHERE ledger_id = l_sob_id;
1091 EXCEPTION
1092 WHEN others THEN
1093 l_err_buff := sqlerrm;
1094 END;
1095
1096 If l_sob_currency <> p_currency_code Then
1097
1098 x_total_amount := QP_Cross_Order_Volume_Load.convert_to_base_curr(
1099 p_trans_amount => x_total_amount,
1100 p_From_currency => l_sob_currency,
1101 p_to_currency => p_currency_code,
1102 p_conversion_date => nvl(p_conversion_rate_date, p_pricing_date),
1103 p_conversion_rate => p_conversion_rate,
1104 p_conversion_type => p_conversion_type_code
1105 );
1106 End If;
1107 /*
1108 x_total_amount := gl_currency_api.convert_amount(l_sob_currency,
1109 oe_order_pub.g_hdr.transactional_curr_code,
1110 oe_order_pub.g_hdr.conversion_rate_date,
1111 oe_order_pub.g_hdr.conversion_type_code,
1112 x_total_amount) ;
1113 --+ G_Order_Info.order_amount;
1114 */
1115
1116 g_order_info.period1_total_amount := qp_number.number_to_canonical(x_total_amount);
1117 end if;
1118
1119 return g_order_info.period1_total_amount;
1120
1121
1122 END Get_Period1_Order_Amount;
1123
1124 FUNCTION Get_Period1_Order_Amount(p_cust_id IN NUMBER) RETURN VARCHAR2
1125 IS
1126
1127 x_return number;
1128
1129 BEGIN
1130
1131 x_return := Get_Period1_Order_Amount(p_cust_id, oe_order_pub.g_hdr.transactional_curr_code, oe_order_pub.g_hdr.conversion_rate_date, oe_order_pub.g_hdr.Pricing_date, oe_order_pub.g_hdr.conversion_rate, oe_order_pub.g_hdr.conversion_type_code);
1132
1133 return x_return;
1134
1135 END;
1136
1137 FUNCTION Get_Period2_Order_Amount(p_cust_id IN NUMBER, p_currency_code VARCHAR2, p_conversion_rate_date DATE, p_pricing_date DATE, p_conversion_rate NUMBER, p_conversion_type_code VARCHAR2) RETURN VARCHAR2
1138 IS
1139
1140 x_total_amount NUMBER;
1141 l_sob_id NUMBER;
1142 l_coa_id NUMBER;
1143 l_sob_name VARCHAR2(240);
1144 l_err_buff VARCHAR2(2000);
1145 l_sob_currency VARCHAR2(30);
1146
1147 BEGIN
1148
1149 if qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_yes then
1150
1151 BEGIN
1152
1153 SELECT period2_total_amount
1154 INTO x_total_amount
1155 FROM oe_cust_total_amts
1156 WHERE sold_to_org_id = p_cust_id;
1157
1158 EXCEPTION
1159 WHEN no_data_found THEN
1160 if (p_cust_id is not null) THEN
1161 return 0;
1162 end if;
1163 END;
1164
1165 l_sob_id := FND_PROFILE.Value('OE_SET_OF_BOOKS_ID');
1166
1167 -- gl_info.gl_get_set_of_books_info(l_sob_id,l_coa_id,l_sob_name,l_sob_currency,l_err_buff);
1168 -- Replaced with following select statement per bug 4537515 (sfiresto)
1169 BEGIN
1170 SELECT name, chart_of_accounts_id, currency_code
1171 INTO l_sob_name, l_coa_id, l_sob_currency
1172 FROM gl_ledgers_public_v
1173 WHERE ledger_id = l_sob_id;
1174 EXCEPTION
1175 WHEN others THEN
1176 l_err_buff := sqlerrm;
1177 END;
1178
1179 If l_sob_currency <> p_currency_code Then
1180
1181 x_total_amount := QP_Cross_Order_Volume_Load.convert_to_base_curr(
1182 p_trans_amount => x_total_amount,
1183 p_From_currency => l_sob_currency,
1184 p_to_currency => p_currency_code,
1185 p_conversion_date => nvl(p_conversion_rate_date, p_pricing_date),
1186 p_conversion_rate => p_conversion_rate,
1187 p_conversion_type => p_conversion_type_code
1188 );
1189 End If;
1190 /*
1191 x_total_amount := gl_currency_api.convert_amount( l_sob_currency,
1192 oe_order_pub.g_hdr.transactional_curr_code,
1193 oe_order_pub.g_hdr.conversion_rate_date,
1194 oe_order_pub.g_hdr.conversion_type_code,
1195 x_total_amount) ;
1196 --+ G_Order_Info.order_amount;
1197 */
1198
1199 g_order_info.period2_total_amount := qp_number.number_to_canonical(x_total_amount);
1200 end if;
1201
1202 return g_order_info.period2_total_amount;
1203
1204 END Get_Period2_Order_Amount;
1205
1206 FUNCTION Get_Period2_Order_Amount(p_cust_id IN NUMBER) RETURN VARCHAR2
1207 IS
1208
1209 x_return number;
1210
1211 BEGIN
1212
1213 x_return := Get_Period2_Order_Amount(p_cust_id, oe_order_pub.g_hdr.transactional_curr_code, oe_order_pub.g_hdr.conversion_rate_date, oe_order_pub.g_hdr.Pricing_date, oe_order_pub.g_hdr.conversion_rate, oe_order_pub.g_hdr.conversion_type_code);
1214
1215 return x_return;
1216
1217 END;
1218
1219 FUNCTION Get_Period3_Order_Amount(p_cust_id IN NUMBER, p_currency_code VARCHAR2, p_conversion_rate_date DATE, p_pricing_date DATE, p_conversion_rate NUMBER, p_conversion_type_code VARCHAR2) RETURN VARCHAR2
1220 IS
1221
1222 x_total_amount NUMBER;
1223 l_sob_id NUMBER;
1224 l_coa_id NUMBER;
1225 l_sob_name VARCHAR2(240);
1226 l_err_buff VARCHAR2(2000);
1227 l_sob_currency VARCHAR2(30);
1228
1229 BEGIN
1230
1231 if qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_yes then
1232
1233 BEGIN
1234
1235 SELECT period3_total_amount
1236 INTO x_total_amount
1237 FROM oe_cust_total_amts
1238 WHERE sold_to_org_id = p_cust_id;
1239
1240 EXCEPTION
1241 WHEN no_data_found THEN
1242 if (p_cust_id is not null) THEN
1243 return 0;
1244 end if;
1245 END;
1246
1247 l_sob_id := FND_PROFILE.Value('OE_SET_OF_BOOKS_ID');
1248
1249 -- gl_info.gl_get_set_of_books_info(l_sob_id,l_coa_id,l_sob_name,l_sob_currency,l_err_buff);
1250 -- Replaced with following select statement per bug 4537515 (sfiresto)
1251 BEGIN
1252 SELECT name, chart_of_accounts_id, currency_code
1253 INTO l_sob_name, l_coa_id, l_sob_currency
1254 FROM gl_ledgers_public_v
1255 WHERE ledger_id = l_sob_id;
1256 EXCEPTION
1257 WHEN others THEN
1258 l_err_buff := sqlerrm;
1259 END;
1260
1261 If l_sob_currency <> p_currency_code Then
1262
1263 x_total_amount := QP_Cross_Order_Volume_Load.convert_to_base_curr(
1264 p_trans_amount => x_total_amount,
1265 p_From_currency => l_sob_currency,
1266 p_to_currency => p_currency_code,
1267 p_conversion_date => nvl(p_conversion_rate_date, p_pricing_date),
1268 p_conversion_rate => p_conversion_rate,
1269 p_conversion_type => p_conversion_type_code
1270 );
1271 End If;
1272 /*
1273 x_total_amount := gl_currency_api.convert_amount(l_sob_currency,
1274 oe_order_pub.g_hdr.transactional_curr_code,
1275 oe_order_pub.g_hdr.conversion_rate_date,
1276 oe_order_pub.g_hdr.conversion_type_code,
1277 x_total_amount) ;
1278 --+ G_Order_Info.order_amount;
1279
1280 */
1281
1282 g_order_info.period3_total_amount := qp_number.number_to_canonical(x_total_amount);
1283 end if;
1284
1285 return g_order_info.period3_total_amount;
1286
1287 END Get_Period3_Order_Amount;
1288
1289 FUNCTION Get_Period3_Order_Amount(p_cust_id IN NUMBER) RETURN VARCHAR2
1290 IS
1291
1292 x_return number;
1293
1294 BEGIN
1295
1296 x_return := Get_Period3_Order_Amount(p_cust_id, oe_order_pub.g_hdr.transactional_curr_code, oe_order_pub.g_hdr.conversion_rate_date, oe_order_pub.g_hdr.Pricing_date, oe_order_pub.g_hdr.conversion_rate, oe_order_pub.g_hdr.conversion_type_code);
1297
1298 return x_return;
1299
1300 END;
1301
1302 /* Begin of getting TCA attributes */
1303
1304 FUNCTION GET_PARTY_ID (p_sold_to_org_id IN NUMBER) RETURN NUMBER IS
1305
1306 l_party_id NUMBER;
1307
1308 CURSOR get_party_id_cur(l_sold_to_org_id NUMBER) IS
1309 SELECT party_id
1310 FROM hz_cust_accounts
1311 WHERE cust_account_id = l_sold_to_org_id;
1312
1313 BEGIN
1314 OPEN get_party_id_cur(p_sold_to_org_id);
1315 FETCH get_party_id_cur INTO l_party_id;
1316 CLOSE get_party_id_cur;
1317 RETURN l_party_id;
1318
1319
1320 EXCEPTION
1321 WHEN OTHERS THEN
1322 RETURN NULL;
1323 END GET_PARTY_ID;
1324
1325
1326 FUNCTION GET_SHIP_TO_PARTY_SITE_ID(p_ship_to_org_id IN NUMBER) RETURN NUMBER IS
1327
1328 l_ship_to_party_site_id NUMBER;
1329
1330 CURSOR get_ship_to_site_id_cur (l_ship_to_org_id NUMBER) IS
1331 SELECT a.party_site_id
1332 FROM hz_cust_acct_sites a,
1333 hz_cust_site_uses b
1334 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1335 AND b.site_use_id = l_ship_to_org_id
1336 AND b.site_use_code = 'SHIP_TO';
1337
1338 BEGIN
1339 OPEN get_ship_to_site_id_cur (p_ship_to_org_id);
1340 FETCH get_ship_to_site_id_cur INTO l_ship_to_party_site_id;
1341 CLOSE get_ship_to_site_id_cur;
1342
1343 RETURN l_ship_to_party_site_id;
1344
1345 EXCEPTION
1346 WHEN OTHERS THEN
1347 RETURN NULL;
1348 END GET_SHIP_TO_PARTY_SITE_ID;
1349
1350
1351 FUNCTION GET_INVOICE_TO_PARTY_SITE_ID(p_invoice_to_org_id IN NUMBER) RETURN NUMBER IS
1352
1353 l_bill_to_party_site_id NUMBER;
1354
1355 CURSOR get_bill_to_site_id_cur (l_bill_to_org_id NUMBER) IS
1356 SELECT a.party_site_id
1357 FROM hz_cust_acct_sites a,
1358 hz_cust_site_uses b
1359 WHERE a.cust_acct_site_id = b.cust_acct_site_id
1360 AND b.site_use_id = l_bill_to_org_id
1361 AND b.site_use_code = 'BILL_TO';
1362
1363 BEGIN
1364 OPEN get_bill_to_site_id_cur (p_invoice_to_org_id);
1365 FETCH get_bill_to_site_id_cur INTO l_bill_to_party_site_id;
1366 CLOSE get_bill_to_site_id_cur;
1367
1368 RETURN l_bill_to_party_site_id;
1369
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 RETURN NULL;
1373 END GET_INVOICE_TO_PARTY_SITE_ID;
1374
1375 /* End of getting TCA attributes */
1376
1377 FUNCTION Get_Line_Weight_Or_Volume
1378 ( p_uom_class IN VARCHAR2,
1379 p_inventory_item_id IN NUMBER,
1380 p_ordered_quantity IN NUMBER,
1381 p_order_quantity_uom IN VARCHAR2
1382 )
1383 RETURN VARCHAR2
1384 IS
1385 l_uom_code VARCHAR2(3);
1386 l_uom_rate NUMBER;
1387 BEGIN
1388
1389 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1390 IF p_uom_class NOT IN ('Weight','Volume')
1391 THEN
1392 IF l_debug = FND_API.G_TRUE THEN
1393 oe_debug_pub.add('Invalide parameter' || p_uom_class);
1394 END IF;
1395 RETURN NULL;
1396 END IF;
1397
1398 IF p_uom_class = 'Weight' THEN
1399 l_uom_code := FND_PROFILE.VALUE('QP_LINE_WEIGHT_UOM_CODE');
1400 ELSE
1401 l_uom_code := FND_PROFILE.VALUE('QP_LINE_VOLUME_UOM_CODE');
1402 END IF;
1403
1404 IF l_uom_code IS NULL THEN
1405 IF l_debug = FND_API.G_TRUE THEN
1406 oe_debug_pub.add('No value set in the Profile Options.');
1407 END IF;
1408 RETURN NULL;
1409 END IF;
1410
1411 INV_CONVERT.INV_UM_CONVERSION(p_order_quantity_uom, l_uom_code, p_inventory_item_id, l_uom_rate);
1412
1413 IF l_uom_rate > 0 THEN
1414 RETURN QP_NUMBER.NUMBER_TO_CANONICAL(TRUNC(l_uom_rate * p_ordered_quantity, 2));
1415 ELSE
1416 IF l_debug = FND_API.G_TRUE THEN
1417 oe_debug_pub.add('No conversion information is available for converting from ' || p_order_quantity_uom || ' TO ' || l_uom_code);
1418 END IF;
1419 RETURN NULL;
1420 END IF;
1421
1422 END Get_Line_Weight_Or_Volume;
1423
1424
1425 FUNCTION Get_Order_Weight_Or_Volume
1426 ( p_uom_class IN VARCHAR2,
1427 p_header_id IN NUMBER
1428 )
1429 RETURN VARCHAR2
1430 IS
1431
1432 -- TYPE t_cursor IS REF CURSOR;
1433 l_uom_code VARCHAR2(3);
1434 l_uom_rate NUMBER;
1435 l_order_total NUMBER := 0;
1436 --l_lines_cursor t_cursor;
1437 l_ordered_quantity NUMBER;
1438 l_ordered_quantity_uom VARCHAR2(30);
1439 l_inventory_item_id NUMBER;
1440 --
1441 CURSOR l_lines_cursor(header_id_in number) is
1442 SELECT ordered_quantity,order_quantity_uom,inventory_item_id
1443 FROM oe_order_lines_all
1444 WHERE header_id = header_id_in;
1445
1446 BEGIN
1447
1448 l_debug := QP_PREQ_GRP.G_DEBUG_ENGINE;
1449 if qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_yes then
1450
1451 IF p_uom_class NOT IN ('Weight','Volume')
1452 THEN
1453 IF l_debug = FND_API.G_TRUE THEN
1454 oe_debug_pub.add('Invalid parameter' || p_uom_class);
1455 END IF;
1456 RETURN NULL;
1457 END IF;
1458
1459 IF p_uom_class = 'Weight' THEN
1460 l_uom_code := FND_PROFILE.VALUE('QP_LINE_WEIGHT_UOM_CODE');
1461 ELSE
1462 l_uom_code := FND_PROFILE.VALUE('QP_LINE_VOLUME_UOM_CODE');
1463 END IF;
1464
1465 IF l_uom_code IS NULL THEN
1466 IF l_debug = FND_API.G_TRUE THEN
1467 oe_debug_pub.add('No value set in the Profile Options.');
1468 END IF;
1469 RETURN NULL;
1470 END IF;
1471
1472 If QP_Util_PUB.HVOP_Pricing_On = 'Y' Then
1473 For i in QP_BULK_PREQ_GRP.G_Line_Rec.header_id.first..QP_BULK_PREQ_GRP.G_Line_Rec.header_id.last
1474 Loop
1475 If QP_BULK_PREQ_GRP.G_Line_Rec.header_id(i) = p_header_id Then
1476 l_ordered_quantity := QP_BULK_PREQ_GRP.G_Line_Rec.ordered_quantity(i);
1477 l_ordered_quantity_uom := QP_BULK_PREQ_GRP.G_Line_Rec.order_quantity_uom(i);
1478 l_inventory_item_id := QP_BULK_PREQ_GRP.G_Line_Rec.inventory_item_id(i);
1479
1480 INV_CONVERT.INV_UM_CONVERSION(l_ordered_quantity_uom, l_uom_code, l_inventory_item_id, l_uom_rate);
1481
1482 IF l_uom_rate > 0 THEN
1483 l_order_total := l_order_total +
1484 QP_NUMBER.NUMBER_TO_CANONICAL(TRUNC(l_uom_rate * l_ordered_quantity, 2));
1485 ELSE
1486 IF l_debug = FND_API.G_TRUE THEN
1487 oe_debug_pub.add('No conversion information is available for converting from ' || l_ordered_quantity_uom || 'TO ' || l_uom_code);
1488 END IF;
1489 RETURN NULL;
1490
1491 END IF;
1492 End If;
1493 END Loop;
1494 Else -- Non HVOP
1495
1496 OPEN l_lines_cursor(p_header_id);
1497
1498 LOOP
1499
1500 FETCH l_lines_cursor INTO
1501 l_ordered_quantity,
1502 l_ordered_quantity_uom,
1503 l_inventory_item_id;
1504 EXIT WHEN l_lines_cursor%NOTFOUND;
1505
1506
1507
1508 INV_CONVERT.INV_UM_CONVERSION(l_ordered_quantity_uom, l_uom_code, l_inventory_item_id, l_uom_rate);
1509
1510 IF l_uom_rate > 0 THEN
1511 l_order_total := (l_order_total + TRUNC(l_uom_rate * l_ordered_quantity, 2));
1512 ELSE
1513 IF l_debug = FND_API.G_TRUE THEN
1514 oe_debug_pub.add('No conversion information is available for converting from ' || l_ordered_quantity_uom || ' TO ' || l_uom_code);
1515 END IF;
1516 --RETURN NULL;
1517 END IF;
1518
1519 END LOOP;
1520 CLOSE l_lines_cursor;
1521 End If; -- HVOP
1522
1523 g_order_info.order_total := l_order_total;
1524 end if; --g_new_pricing_call
1525
1526 return QP_NUMBER.NUMBER_TO_CANONICAL(g_order_info.order_total); -- bug 3191779
1527
1528 END Get_Order_Weight_Or_Volume;
1529
1530 FUNCTION Get_Shippable_Flag(p_header_id IN NUMBER) RETURN VARCHAR2
1531 IS
1532
1533 l_shippable_flag VARCHAR2(1);
1534 x_count NUMBER;
1535
1536 BEGIN
1537
1538 if qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_yes then
1539
1540 l_shippable_flag := 'N';
1541
1542 begin
1543
1544 If QP_Util_PUB.HVOP_Pricing_On = 'Y' Then --HVOP
1545 For i in QP_BULK_PREQ_GRP.G_Line_Rec.line_id.first..QP_BULK_PREQ_GRP.G_Line_Rec.line_id.last
1546 Loop
1547 If QP_BULK_PREQ_GRP.G_Line_Rec.shippable_flag(i) = 'Y' Then
1548 x_count := 1;
1549 Exit;
1550 End If;
1551 End Loop;
1552 Else --Non HVOP
1553 SELECT count(*)
1554 INTO x_count
1555 FROM oe_order_lines_all
1556 WHERE shippable_flag = 'Y'
1557 AND header_id = p_header_id
1558 AND rownum < 2;
1559
1560 End If; --HVOP
1561
1562 IF x_count > 0 THEN
1563 l_shippable_flag := 'Y';
1564 END IF;
1565
1566 EXCEPTION
1567 WHEN no_data_found THEN
1568 l_shippable_flag := 'N';
1569
1570 end;
1571
1572 g_order_info.shippable_flag := l_shippable_flag;
1573 end if;
1574
1575 return g_order_info.shippable_flag;
1576
1577 END Get_Shippable_Flag;
1578
1579 FUNCTION Get_Item_Quantity
1580 ( p_ordered_qty IN NUMBER,
1581 p_pricing_qty IN NUMBER
1582 )
1583 RETURN VARCHAR2
1584 IS
1585
1586 BEGIN
1587
1588 RETURN QP_NUMBER.NUMBER_TO_CANONICAL(NVL(p_pricing_qty,p_ordered_qty));
1589
1590 END Get_Item_Quantity;
1591
1592 FUNCTION Get_Item_Amount
1593 ( p_ordered_qty IN NUMBER,
1594 p_pricing_qty IN NUMBER
1595 )
1596 RETURN VARCHAR2
1597 IS
1598
1599 x_return NUMBER;
1600
1601 BEGIN
1602
1603 x_return := QP_NUMBER.NUMBER_TO_CANONICAL(NVL(p_pricing_qty,p_ordered_qty) * NVL(OE_ORDER_PUB.G_LINE.UNIT_LIST_PRICE_PER_PQTY, OE_ORDER_PUB.G_LINE.UNIT_LIST_PRICE));
1604
1605 IF (OE_ORDER_PUB.G_LINE.UNIT_LIST_PRICE_PER_PQTY IS NULL) AND (OE_ORDER_PUB.G_LINE.UNIT_LIST_PRICE is NULL) THEN
1606 x_return := 0;
1607 END IF;
1608
1609 RETURN x_return;
1610
1611 END Get_Item_Amount;
1612
1613
1614 Procedure Get_Customer_Info(p_cust_id NUMBER, invoice_to_org_id NUMBER)
1615 IS
1616
1617 --TYPE t_cursor IS REF CURSOR;
1618
1619 l_account_type_id VARCHAR2(30);
1620 v_count NUMBER := 1;
1621 --l_acct_type_cursor t_cursor;
1622 l_realted_cust_id VARCHAR2(30);
1623 --l_related_cust_cursor t_cursor;
1624 --
1625 CURSOR l_acct_type_cursor (cust_id_in number) is
1626 SELECT distinct profile_class_id
1627 FROM HZ_CUSTOMER_PROFILES
1628 WHERE cust_account_id = cust_id_in;
1629 CURSOR l_related_cust_cursor(cust_id_in number) is
1630 SELECT RELATED_CUST_ACCOUNT_ID
1631 FROM HZ_CUST_ACCT_RELATE
1632 WHERE cust_account_id = cust_id_in;
1633
1634 BEGIN
1635
1636 if qp_preq_grp.g_new_pricing_call = qp_preq_grp.g_yes then
1637
1638 G_Customer_Info.customer_id := p_cust_id;
1639
1640 -- Getting info from HZ_CUST_ACCOUNTS
1641
1642 BEGIN
1643 SELECT customer_class_code, sales_channel_code
1644 INTO G_Customer_Info.customer_class_code, G_Customer_Info.sales_channel_code
1645 FROM hz_cust_accounts
1646 WHERE cust_account_id = p_cust_id;
1647
1648 EXCEPTION
1649 WHEN no_data_found THEN
1650 G_Customer_Info.customer_class_code := null;
1651 G_Customer_Info.sales_channel_code := null;
1652
1653 END;
1654
1655 -- Getting GSA info
1656
1657 BEGIN
1658 SELECT NVL(gsa_indicator,'N')
1659 INTO G_Customer_Info.gsa_indicator
1660 FROM hz_cust_site_uses hsu
1661 WHERE site_use_id = invoice_to_org_id
1662 --added for moac
1663 and hsu.org_id = QP_ATTR_MAPPING_PUB.G_ORG_ID;
1664 /*
1665 AND NVL(hsu.org_id,
1666 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1), ' ',NULL,
1667 SUBSTRB(USERENV('CLIENT_INFO'), 1,10))),-99)) =
1668 NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',NULL,
1669 SUBSTRB(USERENV('CLIENT_INFO'),1,10))), -99);
1670 */
1671
1672 EXCEPTION
1673
1674 WHEN NO_DATA_FOUND THEN
1675 G_Customer_Info.gsa_indicator := 'N';
1676
1677 END;
1678
1679 IF G_Customer_Info.gsa_indicator = 'N' THEN
1680
1681 BEGIN
1682
1683 SELECT NVL(gsa_indicator_flag,'N')
1684 into G_Customer_Info.gsa_indicator
1685 from hz_parties hp,hz_cust_accounts hca
1686 where hp.party_id = hca.party_id
1687 and hca.cust_account_id = p_cust_id ;
1688
1689 EXCEPTION
1690
1691 WHEN NO_DATA_FOUND THEN
1692 G_Customer_Info.gsa_indicator := null;
1693
1694 END;
1695
1696 END IF;
1697
1698
1699 -- Getting Account Types
1700
1701 /*
1702 OPEN l_acct_type_cursor FOR
1703 SELECT distinct customer_profile_class_id
1704 FROM AR_CUSTOMER_PROFILES
1705 WHERE customer_id = p_cust_id;
1706 */
1707 OPEN l_acct_type_cursor(p_cust_id);
1708 LOOP
1709
1710 FETCH l_acct_type_cursor INTO l_account_type_id;
1711 EXIT WHEN l_acct_type_cursor%NOTFOUND;
1712
1713 G_Customer_Info.account_types(v_count) := l_account_type_id;
1714 v_count := v_count + 1;
1715
1716 END LOOP;
1717
1718 CLOSE l_acct_type_cursor;
1719
1720 -- Get Customer Relationships
1721
1722 v_count := 1;
1723
1724 /*
1725 OPEN l_related_cust_cursor FOR
1726 SELECT RELATED_CUSTOMER_ID
1727 FROM RA_CUSTOMER_RELATIONSHIPS
1728 WHERE customer_id = p_cust_id;
1729 */
1730
1731 OPEN l_related_cust_cursor(p_cust_id);
1732 LOOP
1733
1734 FETCH l_related_cust_cursor INTO l_realted_cust_id;
1735 EXIT WHEN l_related_cust_cursor%NOTFOUND;
1736
1737 G_Customer_Info.customer_relationships(v_count) := l_realted_cust_id;
1738 v_count := v_count + 1;
1739
1740 END LOOP;
1741
1742 CLOSE l_related_cust_cursor;
1743
1744 end if;
1745
1746 END;
1747
1748 FUNCTION Get_Item_Amount
1749 ( p_ordered_qty IN NUMBER,
1750 p_pricing_qty IN NUMBER,
1751 p_UNIT_LIST_PRICE_PER_PQTY IN NUMBER,
1752 p_unit_list_price IN NUMBER
1753 )
1754 RETURN VARCHAR2
1755 IS
1756
1757 x_return NUMBER;
1758
1759 BEGIN
1760
1761 x_return := QP_NUMBER.NUMBER_TO_CANONICAL(NVL(p_pricing_qty,p_ordered_qty) * NVL(p_UNIT_LIST_PRICE_PER_PQTY, p_UNIT_LIST_PRICE));
1762
1763 IF (p_UNIT_LIST_PRICE_PER_PQTY IS NULL) AND (p_UNIT_LIST_PRICE is NULL) THEN
1764 x_return := 0;
1765 END IF;
1766
1767 RETURN x_return;
1768 END;
1769
1770 END QP_SOURCING_API_PUB;