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