1 PACKAGE BODY ASO_SOURCING_PVT as
2 /* $Header: asovsrcb.pls 120.1 2005/08/05 09:32:23 appldev ship $ */
3 -- Start of Comments
4 -- Package name : ASO_SOURCING_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_SOURCING_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asovsrcb.pls';
13
14
15
16
17 FUNCTION Get_Cust_Acct (p_quote_header_id NUMBER)
18 RETURN NUMBER
19 IS
20 x_cust_account_id NUMBER;
21 BEGIN
22
23 SELECT cust_account_id
24 INTO x_cust_account_id
25 FROM aso_quote_headers_all
26 WHERE quote_header_id = p_quote_header_id;
27
28 IF (SQL%NOTFOUND) THEN
29 null;
30 x_cust_account_id := null;
31 END IF;
32
33
34 return x_cust_account_id;
35 EXCEPTION
36 WHEN OTHERS THEN
37 RETURN NULL;
38 END Get_Cust_Acct;
39
40 -- the following four APIs actually create the site use if needed
41 -- this should be changed in the party int
42
43 FUNCTION Get_Ship_to_Site_Use (p_quote_header_id NUMBER)
44 RETURN NUMBER
45 IS
46 x_ship_to_org_id NUMBER := NULL;
47 l_cust_account_id NUMBER;
48 l_ship_party_site_id NUMBER;
49 l_return_status VARCHAR2(1);
50 l_msg_count NUMBER;
51 l_msg_data varchar2(2000);
52 l_ship_to_cust_account_id NUMBER;
53
54 CURSOR C_get_quote_info (l_quote_header_id NUMBER) IS
55 SELECT qh.cust_account_id, qs.ship_to_party_site_id,qs.ship_to_cust_account_id
56 FROM aso_quote_headers_all qh, aso_shipments qs
57 WHERE qh.quote_header_id = qs.quote_header_id
58 AND qh.quote_header_id = l_quote_header_id
59 AND qs.quote_line_id is NULL;
60
61 BEGIN
62
63 OPEN C_get_quote_info(p_quote_header_id);
64 FETCH C_get_quote_info INTO l_cust_account_id, l_ship_party_site_id,l_ship_to_cust_account_id;
65 IF (C_get_quote_info%NOTFOUND) THEN
66 return x_ship_to_org_id;
67 END IF;
68 CLOSE C_get_quote_info;
69
70
71 if l_ship_to_cust_account_id is not null OR l_ship_to_cust_account_id <> fnd_api.G_MISS_NUM then
72 l_cust_account_id := l_ship_to_cust_account_id;
73 end if;
74
75 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
76 aso_debug_pub.add('cust account = ' || l_cust_account_id,1,'N');
77 aso_debug_pub.add('ship party site = ' || l_ship_party_site_id,1,'N');
78 END IF;
79 IF l_cust_account_id is not NULL
80 AND l_ship_party_site_id is not NULL THEN
81
82
83 ASO_MAP_QUOTE_ORDER_INT.GET_ACCT_SITE_USES (
84
85 P_Cust_Account_Id => l_cust_account_id
86 ,P_Party_Site_Id => l_ship_party_site_id
87 ,P_Acct_Site_type => 'SHIP_TO'
88 ,x_return_status => l_return_status
89 ,x_site_use_id => x_ship_to_org_id
90 );
91 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
92 x_ship_to_org_id := NULL;
93 END IF;
94
95 END IF; -- not null
96
97 return x_ship_to_org_id;
98 EXCEPTION
99 WHEN OTHERS THEN
100 RETURN NULL;
101
102
103 END Get_Ship_to_Site_Use;
104
105
106
107 FUNCTION Get_Line_Ship_to_Site_Use (p_quote_line_id NUMBER)
108 RETURN NUMBER
109 IS
110 x_ship_to_org_id NUMBER := NULL;
111 l_cust_account_id NUMBER;
112 l_ship_party_site_id NUMBER;
113 l_return_status VARCHAR2(1);
114 l_msg_count NUMBER;
115 l_msg_data varchar2(2000);
116 l_ship_to_cust_account_id NUMBER;
117 l_quote_header_id number;
118 CURSOR C_get_quote_info (l_quote_line_id NUMBER) IS
119 SELECT qh.cust_account_id, qs.ship_to_party_site_id,
120 qs.ship_to_cust_account_id,qh.quote_header_id
121 FROM aso_quote_headers_all qh, aso_shipments qs, aso_quote_lines_all ql
122 WHERE qh.quote_header_id = qs.quote_header_id
123 AND ql.quote_header_id = qh.quote_header_id
124 AND ql.quote_line_id = l_quote_line_id
125 AND ql.quote_line_id = qs.quote_line_id;
126
127 BEGIN
128
129 OPEN C_get_quote_info(p_quote_line_id);
130 FETCH C_get_quote_info INTO l_cust_account_id, l_ship_party_site_id,
131 l_ship_to_cust_account_id, l_quote_header_id;
132 IF (C_get_quote_info%NOTFOUND) THEN
133 return x_ship_to_org_id;
134 END IF;
135 CLOSE C_get_quote_info;
136
137 if l_ship_to_cust_account_id is not null OR l_ship_to_cust_account_id <> fnd_api.G_MISS_NUM then
138 l_cust_account_id := l_ship_to_cust_account_id;
139 end if;
140 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
141 aso_debug_pub.add('cust account = ' || l_cust_account_id,1,'N');
142 aso_debug_pub.add('party site = ' || l_ship_party_site_id,1,'N');
143 END IF;
144
145 IF l_cust_account_id is not NULL
146 AND l_ship_party_site_id is not NULL THEN
147 ASO_MAP_QUOTE_ORDER_INT.GET_ACCT_SITE_USES (
148
149 P_Cust_Account_Id => l_cust_account_id
150 ,P_Party_Site_Id => l_ship_party_site_id
151 ,P_Acct_Site_type => 'SHIP_TO'
152 ,x_return_status => l_return_status
153 ,x_site_use_id => x_ship_to_org_id
154 );
155
156
157 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
158 x_ship_to_org_id := NULL;
159 END IF;
160 ELSE
161 x_ship_to_org_id := get_ship_to_site_use(l_quote_header_id);
162 END IF; -- not null
163
164 return x_ship_to_org_id;
165 EXCEPTION
166 WHEN OTHERS THEN
167 RETURN NULL;
168
169
170 END Get_Line_Ship_to_Site_Use;
171
172
173
174
175 FUNCTION Get_Invoice_to_Site_Use (p_quote_header_id NUMBER)
176 RETURN NUMBER
177 IS
178 x_invoice_to_org_id NUMBER := NULL;
179 l_cust_account_id NUMBER;
180 l_invoice_party_site_id NUMBER;
181 l_return_status VARCHAR2(1);
182 l_msg_count NUMBER;
183 l_msg_data varchar2(2000);
184 l_invoice_to_cust_account_id NUMBER;
185
186
187 CURSOR C_get_quote_info (l_quote_header_id NUMBER) IS
188 SELECT cust_account_id, invoice_to_party_site_id,invoice_to_cust_account_id
189 FROM aso_quote_headers_all
190 WHERE quote_header_id = l_quote_header_id;
191
192 BEGIN
193
194 OPEN C_get_quote_info(p_quote_header_id);
195 FETCH C_get_quote_info INTO l_cust_account_id, l_invoice_party_site_id,l_invoice_to_cust_account_id;
196 IF (C_get_quote_info%NOTFOUND) THEN
197 return x_invoice_to_org_id;
198 END IF;
199 CLOSE C_get_quote_info;
200
201 -- ----------------dbms_output.put_line(l_cust_account_id || l_invoice_party_site_id);
202
203 if l_invoice_to_cust_account_id is not null OR l_invoice_to_cust_account_id <> fnd_api.G_MISS_NUM then
204 l_cust_account_id := l_invoice_to_cust_account_id;
205 end if;
206
207 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
208 aso_debug_pub.add('cust account = ' || l_cust_account_id,1,'N');
209 aso_debug_pub.add('invoice party site = ' || l_invoice_party_site_id,1,'N');
210 END IF;
211
212
213 IF l_cust_account_id is not NULL
214 AND l_invoice_party_site_id is not NULL THEN
215
216 ASO_MAP_QUOTE_ORDER_INT.GET_ACCT_SITE_USES (
217
218 P_Cust_Account_Id => l_cust_account_id
219 ,P_Party_Site_Id => l_invoice_party_site_id
220 ,P_Acct_Site_type => 'BILL_TO'
221 ,x_return_status => l_return_status
222 ,x_site_use_id => x_invoice_to_org_id
223 );
224
225 -- ----------------dbms_output.put_line(l_return_status || ' '||x_invoice_to_org_id );
226 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
227 x_invoice_to_org_id := NULL;
228 END IF;
229
230 END IF; -- not null
231
232 return x_invoice_to_org_id;
233 EXCEPTION
234 WHEN OTHERS THEN
235 RETURN NULL;
236
237
238 END Get_Invoice_to_Site_Use;
239
240
241
242
243
244 FUNCTION Get_Line_Invoice_Site_Use (p_quote_line_id NUMBER)
245 RETURN NUMBER
246 IS
247 x_invoice_to_org_id NUMBER := NULL;
248 l_cust_account_id NUMBER;
249 l_invoice_party_site_id NUMBER;
250 l_return_status VARCHAR2(1);
251 l_msg_count NUMBER;
252 l_msg_data NUMBER;
253 l_invoice_to_cust_account_id NUMBER;
254 l_quote_header_id number;
255 CURSOR C_get_quote_info (l_quote_line_id NUMBER) IS
256 SELECT qh.cust_account_id, ql.invoice_to_party_site_id,
257 ql.invoice_to_cust_account_id, qh.quote_header_id
258 FROM aso_quote_headers_all qh, aso_quote_lines_all ql
259 WHERE ql.quote_line_id = l_quote_line_id
260 AND ql.quote_header_id = qh.quote_header_id;
261
262 BEGIN
263
264 OPEN C_get_quote_info(p_quote_line_id);
265 FETCH C_get_quote_info INTO l_cust_account_id, l_invoice_party_site_id,
266 l_invoice_to_cust_account_id, l_quote_header_id;
267 IF (C_get_quote_info%NOTFOUND) THEN
268 return x_invoice_to_org_id;
269 END IF;
270 CLOSE C_get_quote_info;
271
272 if l_invoice_to_cust_account_id is not null OR l_invoice_to_cust_account_id <> fnd_api.G_MISS_NUM then
273 l_cust_account_id := l_invoice_to_cust_account_id;
274 end if;
275
276 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
277 aso_debug_pub.add('cust account = ' || l_cust_account_id,1,'N');
278 aso_debug_pub.add('invoice party site = ' || l_invoice_party_site_id,1,'N');
279 END IF;
280
281 IF l_cust_account_id is not NULL
282 AND l_invoice_party_site_id is not NULL THEN
283 ASO_MAP_QUOTE_ORDER_INT.GET_ACCT_SITE_USES (
284
285 P_Cust_Account_Id => l_cust_account_id
286 ,P_Party_Site_Id => l_invoice_party_site_id
287 ,P_Acct_Site_type => 'BILL_TO'
288 ,x_return_status => l_return_status
289 ,x_site_use_id => x_invoice_to_org_id
290 );
291
292 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
293 x_invoice_to_org_id := NULL;
294 END IF;
295 ELSE
296 x_invoice_to_org_id := get_invoice_to_site_use(l_quote_header_id);
297 END IF; -- not null
298
299 return x_invoice_to_org_id;
300 EXCEPTION
301 WHEN OTHERS THEN
302 RETURN NULL;
303
304
305 END Get_Line_Invoice_Site_Use;
306
307
308 FUNCTION Get_Ship_to_Party_Site (p_quote_header_id NUMBER)
309 RETURN NUMBER
310 IS
311 CURSOR C_get_quote_info (l_quote_header_id NUMBER) IS
312 SELECT qs.ship_to_party_site_id
313 FROM aso_shipments qs
314 WHERE qs.quote_header_id = l_quote_header_id
315 AND qs.quote_line_id is NULL;
316
317 x_ship_party_site_id NUMBER := NULL;
318
319 BEGIN
320
321 OPEN C_get_quote_info(p_quote_header_id);
322 FETCH C_get_quote_info INTO x_ship_party_site_id;
323 IF (C_get_quote_info%NOTFOUND) THEN
324 return x_ship_party_site_id;
325 END IF;
326 CLOSE C_get_quote_info;
327
328 return x_ship_party_site_id;
329 EXCEPTION
330 WHEN OTHERS THEN
331 RETURN NULL;
332
333 END Get_Ship_to_Party_Site;
334
335
336
337 FUNCTION Get_Line_Ship_Party_Site (p_quote_line_id NUMBER)
338 RETURN NUMBER
339 IS
340 CURSOR C_get_quote_info (l_quote_line_id NUMBER) IS
341 SELECT qs.ship_to_party_site_id
342 FROM aso_shipments qs
343 WHERE qs.quote_line_id = l_quote_line_id;
344
345 x_ship_party_site_id NUMBER;
346
347 BEGIN
348
349 OPEN C_get_quote_info(p_quote_line_id);
350 FETCH C_get_quote_info INTO x_ship_party_site_id;
351 IF (C_get_quote_info%NOTFOUND) THEN
352 return x_ship_party_site_id;
353 END IF;
354 CLOSE C_get_quote_info;
355
356 return x_ship_party_site_id;
357 EXCEPTION
358 WHEN OTHERS THEN
359 RETURN NULL;
360
361 END Get_Line_Ship_Party_Site;
362
363
364 FUNCTION Get_Invoice_to_Party_Site (p_quote_header_id NUMBER)
365 RETURN NUMBER
366 IS
367 CURSOR C_get_quote_info (l_quote_header_id NUMBER) IS
368 SELECT qs.invoice_to_party_site_id
369 FROM aso_quote_headers_all qs
370 WHERE qs.quote_header_id = l_quote_header_id;
371
372 x_invoice_party_site_id NUMBER := NULL;
373
374 BEGIN
375
376 OPEN C_get_quote_info(p_quote_header_id);
377 FETCH C_get_quote_info INTO x_invoice_party_site_id;
378 IF (C_get_quote_info%NOTFOUND) THEN
379 return x_invoice_party_site_id;
380 END IF;
381 CLOSE C_get_quote_info;
382
383 return x_invoice_party_site_id;
384 EXCEPTION
385 WHEN OTHERS THEN
386 RETURN NULL;
387
388 END Get_Invoice_to_Party_Site;
389
390
391
392 FUNCTION Get_Line_Invoice_Party_Site (p_quote_line_id NUMBER)
393 RETURN NUMBER
394 IS
395 CURSOR C_get_quote_info (l_quote_line_id NUMBER) IS
396 SELECT qs.invoice_to_party_site_id
397 FROM aso_quote_lines_all qs
398 WHERE qs.quote_line_id = l_quote_line_id;
399
400 xl_inv_party_site_id NUMBER;
401
402 BEGIN
403
404 OPEN C_get_quote_info(p_quote_line_id);
405 FETCH C_get_quote_info INTO xl_inv_party_site_id;
406 IF (C_get_quote_info%NOTFOUND) THEN
407 return xl_inv_party_site_id;
408 END IF;
409 CLOSE C_get_quote_info;
410
411 return xl_inv_party_site_id;
412 EXCEPTION
413 WHEN OTHERS THEN
414 RETURN NULL;
415
416 END Get_Line_Invoice_Party_Site;
417
418 /*
419 FUNCTION Get_Party_Id (p_quote_header_id NUMBER)
420 RETURN NUMBER
421 IS
422 x_party_id NUMBER;
423 BEGIN
424
425 SELECT party_id
426 INTO x_party_id
427 FROM aso_quote_headers_all
428 WHERE quote_header_id = p_quote_header_id;
429
430 IF (SQL%NOTFOUND) THEN
431 null;
432 x_party_id := null;
433 END IF;
434
435 return x_party_id;
436
437 END Get_Party_Id;
438
439 */
440
441
442
443 FUNCTION Get_Customer_Class(p_cust_account_id IN NUMBER)
444 RETURN VARCHAR2
445 IS
446 x_class_code VARCHAR2(240);
447 BEGIN
448
449 SELECT customer_class_code
450 INTO x_class_code
451 FROM hz_cust_accounts
452 WHERE cust_account_id = p_cust_account_id;
453
454 RETURN x_class_code;
455 EXCEPTION
456 WHEN OTHERS THEN
457 RETURN NULL;
458
459 END Get_Customer_Class;
460
461 FUNCTION Get_Account_Type (p_cust_account_id IN NUMBER)
462 RETURN QP_Attr_Mapping_PUB.t_MultiRecord
463 IS
464
465 TYPE t_cursor IS REF CURSOR;
466
467 x_account_type_ids QP_Attr_Mapping_PUB.t_MultiRecord;
468 l_account_type_id number;
469 v_count NUMBER := 1;
470 l_acct_type_cursor t_cursor;
471 BEGIN
472 OPEN l_acct_type_cursor FOR
473 SELECT profile_class_id
474 FROM HZ_CUSTOMER_PROFILES
475 WHERE cust_account_id = p_cust_account_id;
476
477 LOOP
478
479 FETCH l_acct_type_cursor INTO l_account_type_id;
480 EXIT WHEN l_acct_type_cursor%NOTFOUND;
481
482 x_account_type_ids(v_count) := l_account_type_id;
483 v_count := v_count + 1;
484
485 END LOOP;
486
487 CLOSE l_acct_type_cursor;
488
489 RETURN x_account_type_ids;
490
491 END Get_Account_Type;
492
493 FUNCTION Get_Sales_Channel (p_cust_account_id IN NUMBER)
494 RETURN VARCHAR2
495 IS
496 x_sales_channel_code VARCHAR2(240);
497 BEGIN
498
499 SELECT sales_channel_code
500 INTO x_sales_channel_code
501 FROM hz_cust_accounts
502 WHERE cust_account_id = p_cust_account_id;
503
504 RETURN x_sales_channel_code;
505 EXCEPTION
506 WHEN OTHERS THEN
507 RETURN NULL;
508
509
510 END Get_Sales_Channel;
511
512
513 FUNCTION Get_GSA (p_cust_account_id NUMBER)
514 RETURN VARCHAR2
515 IS
516 x_gsa VARCHAR2(1);
517 BEGIN
518
519 SELECT gsa_indicator_flag
520 INTO x_gsa
521 FROM hz_cust_accounts accts ,hz_parties party
522 WHERE accts.party_id=party.party_id
523 AND cust_account_id = p_cust_account_id;
524
525 RETURN x_gsa;
526 EXCEPTION
527 WHEN OTHERS THEN
528 RETURN NULL;
529
530 END get_gsa;
531
532 /*
533 FUNCTION Get_Site_Use (p_contact_id IN VARCHAR2)
534 RETURN VARCHAR2
535 IS
536
537 x_site_use_id VARCHAR2(240);
538
539 BEGIN
540
541 SELECT site_use_id
542 INTO x_site_use_id
543 FROM HZ_CUST_SITE_USES_ALL
544 WHERE contact_id = p_contact_id;
545
546 RETURN x_site_use_id;
547
548 END Get_Site_Use;
549 */
550
551
552 FUNCTION Get_quote_Qty (p_qte_header_id IN NUMBER)
553 RETURN VARCHAR2
554 IS
555
556 x_quote_qty varchar2(30);
557 l_quote_qty NUMBER;
558
559 BEGIN
560
561 SELECT SUM(nvl(quantity,0))
562 INTO l_quote_qty
563 FROM aso_quote_lines_all
564 WHERE quote_header_id=p_qte_header_id
565 AND (line_category_code<>'RETURN' OR line_category_code IS NULL)
566 GROUP BY quote_header_id;
567
568
569 IF (SQL%NOTFOUND) THEN
570 l_quote_qty :=0;
571 end if;
572
573 x_quote_qty := FND_NUMBER.NUMBER_TO_CANONICAL(nvl(l_quote_qty, 0));
574 RETURN x_quote_qty;
575 EXCEPTION
576 WHEN OTHERS THEN
577 RETURN NULL;
578
579 END Get_quote_Qty;
580
581
582 FUNCTION Get_quote_Amount(p_qte_header_id IN NUMBER) RETURN VARCHAR2
583 IS
584 x_quote_amount VARCHAR2(30);
585 l_quote_amount NUMBER;
586
587 BEGIN
588
589 SELECT SUM((nvl(quantity,0))*(LINE_LIST_PRICE-LINE_ADJUSTED_AMOUNT))
590 INTO l_quote_amount
591 FROM aso_quote_lines_all
592 WHERE quote_header_id=p_qte_header_id
593 AND (line_category_code<>'RETURN' OR line_category_code IS NULL)
594 GROUP BY quote_header_id;
595
596
597 IF (SQL%NOTFOUND) THEN
598 l_quote_amount :=0;
599 END IF;
600
601 x_quote_amount:=FND_NUMBER.NUMBER_TO_CANONICAL(NVL(l_quote_amount,0));
602
603 RETURN x_quote_amount;
604
605 END Get_quote_Amount;
606
607 -- order context
608
609 FUNCTION Get_shippable_flag(p_qte_line_id NUMBER)
610 RETURN VARCHAR2
611 IS
612 x_shippable_item_flag VARCHAR2(1);
613 BEGIN
614
615 SELECT shippable_item_flag
616 INTO x_shippable_item_flag
617 FROM aso_i_items_v i, aso_quote_lines_all l
618 WHERE l.quote_line_id = p_qte_line_id
619 and l.inventory_item_id = i.inventory_item_id
620 and l.organization_id = i.organization_id;
621
622 RETURN x_shippable_item_flag;
623 EXCEPTION
624 WHEN OTHERS THEN
625 RETURN NULL;
626
627 END get_shippable_flag;
628
629 FUNCTION Get_Cust_Po(
630 p_qte_header_id number
631 ) RETURN VARCHAR2
632 IS
633 Cursor get_po is SELECT payment_ref_number from aso_payments
634 WHERE
635 payment_type_code ='PO' and quote_header_id = p_qte_header_id and quote_line_id is NULL;
636 Customer_PO VARCHAR2(240);
637
638 BEGIN
639 OPEN get_po;
640 fetch get_po into Customer_Po;
641 CLOSE get_po;
642 RETURN Customer_Po;
643 EXCEPTION
644 WHEN OTHERS THEN
645 RETURN NULL;
646
647 END Get_Cust_Po;
648
649 FUNCTION Get_line_Cust_Po(
650 p_qte_line_id number
651 ) RETURN VARCHAR2
652 IS
653 Cursor get_po is SELECT payment_ref_number from aso_payments
654 WHERE
655 payment_type_code ='PO' and quote_line_id = p_qte_line_id;
656 Customer_PO VARCHAR2(240);
657
658 BEGIN
659 OPEN get_po;
660 fetch get_po into Customer_Po;
661 CLOSE get_po;
662 RETURN Customer_Po;
663 EXCEPTION
664 WHEN OTHERS THEN
665 RETURN NULL;
666
667 END Get_line_Cust_Po;
668
669
670
671
672
673 FUNCTION Get_Request_date(
674 p_qte_header_id number
675 ) RETURN DATE
676 IS
677
678 Cursor get_req_date is SELECT request_date from aso_shipments
679 WHERE
680 quote_header_id = p_qte_header_id and quote_line_id is NULL;
681 l_request_date DATE;
682 x_request_date DATE;
683 BEGIN
684 OPEN get_req_date;
685 fetch get_req_date into l_request_date;
686 CLOSE get_req_date;
687
688 x_request_date := FND_DATE.DATE_TO_CANONICAL(l_request_date);
689 RETURN x_request_date;
690 END Get_Request_date;
691
692 FUNCTION Get_Line_Request_date(
693 p_qte_line_id number
694 ) RETURN DATE
695 IS
696
697 Cursor get_req_date is SELECT request_date from aso_shipments
698 WHERE quote_line_id = p_qte_line_id ;
699 l_request_date DATE;
700 x_request_date DATE;
701 BEGIN
702 OPEN get_req_date;
703 fetch get_req_date into l_request_date;
704 CLOSE get_req_date;
705
706 x_request_date := FND_DATE.DATE_TO_CANONICAL(l_request_date);
707 RETURN x_request_date;
708 END Get_line_Request_date;
709
710
711 FUNCTION Get_Freight_term(
712 p_qte_header_id number
713 ) RETURN DATE
714 IS
715
716 Cursor get_frieght is SELECT FREIGHT_TERMS_CODE from aso_shipments
717 WHERE
718 quote_header_id = p_qte_header_id and quote_line_id is NULL;
719 l_freight_terms_code VARCHAR2(30);
720 BEGIN
721 OPEN get_frieght;
722 fetch get_frieght into l_freight_terms_code;
723 CLOSE get_frieght;
724 RETURN l_freight_terms_code;
725 END Get_Freight_term;
726
727 FUNCTION Get_line_Freight_term(
728 p_qte_line_id number
729 ) RETURN VARCHAR2
730 IS
731
732 Cursor get_frieght is SELECT FREIGHT_TERMS_CODE from aso_shipments
733 WHERE
734 quote_line_id = p_qte_line_id;
735 l_freight_terms_code VARCHAR2(30);
736 BEGIN
737 OPEN get_frieght;
738 fetch get_frieght into l_freight_terms_code;
739 CLOSE get_frieght;
740 RETURN l_freight_terms_code;
741 END Get_line_Freight_term;
742
743 FUNCTION Get_Payment_term(
744 p_qte_header_id number
745 ) RETURN NUMBER
746 IS
747
748 Cursor get_pmnt_term is SELECT payment_term_id from aso_payments
749 WHERE
750 quote_header_id = p_qte_header_id and quote_line_id IS null;
751 l_pmnt_term_id NUMBER;
752 BEGIN
753 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
754 aso_debug_pub.add('Inside Get_line_Payment_term',1,'N');
755 aso_debug_pub.add('p_qte_header_id: '||nvl(to_char(p_qte_header_id),'null'),1,'N');
756 END IF;
757 OPEN get_pmnt_term;
758 fetch get_pmnt_term into l_pmnt_term_id;
759 CLOSE get_pmnt_term;
760 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
761 aso_debug_pub.add('Inside Get_line_Payment_term'||l_pmnt_term_id,1,'N');
762 END IF;
763 RETURN l_pmnt_term_id;
764 END Get_Payment_term;
765
766
767 FUNCTION Get_line_Payment_term(
768 p_qte_line_id number,p_qte_header_id number
769 ) RETURN NUMBER
770 IS
771 Cursor get_hdr_pmnt_term is SELECT payment_term_id from aso_payments
772 WHERE
773 quote_header_id = p_qte_header_id and quote_line_id IS null;
774
775 Cursor get_pmnt_term is SELECT payment_term_id from aso_payments
776 WHERE
777 quote_line_id = p_qte_line_id;
778 l_pmnt_term_id NUMBER;
779 BEGIN
780 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
781 aso_debug_pub.add('Inside Get_line_Payment_term',1,'N');
782 aso_debug_pub.add('p_qte_line_id: '||nvl(to_char(p_qte_line_id),'null'),1,'N');
783 aso_debug_pub.add('p_qte_header_id: '||nvl(to_char(p_qte_header_id),'null'),1,'N');
784 END IF;
785 OPEN get_pmnt_term;
786 fetch get_pmnt_term into l_pmnt_term_id;
787 CLOSE get_pmnt_term;
788
789 If l_pmnt_term_id is NULL or l_pmnt_term_id = fnd_api.g_miss_num then
790 OPEN get_hdr_pmnt_term;
791 fetch get_hdr_pmnt_term into l_pmnt_term_id;
792 CLOSE get_hdr_pmnt_term;
793 end if;
794
795 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
796 aso_debug_pub.add('Inside Get_line_Payment_term'||l_pmnt_term_id,1,'N');
797 END IF;
798 RETURN l_pmnt_term_id;
799 END Get_line_Payment_term;
800
801 FUNCTION Get_freight_terms_code(p_qte_line_id NUMBER)
802 RETURN VARCHAR2
803 IS
804 CURSOR C_line_freight_terms_code IS
805 SELECT qs.freight_terms_code
806 FROM aso_shipments qs
807 WHERE qs.quote_line_id = p_qte_line_id
808 and qs.freight_terms_code is not null;
809
810 CURSOR C_hdr_freight_terms_code IS
811 SELECT qs.freight_terms_code
812 FROM aso_shipments qs, aso_quote_lines_all ql
813 WHERE qs.quote_header_id = ql.quote_header_id
814 and ql.quote_line_id = p_qte_line_id
815 and qs.quote_line_id IS NULL;
816
817 x_freight_terms_code VARCHAR2(30);
818
819 BEGIN
820 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
821 aso_debug_pub.add('Get_freight_terms_code - Begin ',1,'Y');
822 aso_debug_pub.add('p_qte_line_id: '||nvl(to_char(p_qte_line_id),'null'),1,'N');
823 END IF;
824
825 OPEN C_line_freight_terms_code;
826 FETCH C_line_freight_terms_code INTO x_freight_terms_code;
827
828 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
829 aso_debug_pub.add('Line Level x_freight_terms_code: '||nvl(x_freight_terms_code,'null'),1,'N');
830 END IF;
831
832 IF (C_line_freight_terms_code%NOTFOUND) THEN
833
834 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
835 aso_debug_pub.add('Inside first IF statement ',1,'N');
836 END IF;
837
838 CLOSE C_line_freight_terms_code;
839
840 OPEN C_hdr_freight_terms_code;
841 FETCH C_hdr_freight_terms_code INTO x_freight_terms_code;
842
843 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
844 aso_debug_pub.add('Header Level x_freight_terms_code: '||nvl(x_freight_terms_code,'null'),1,'N');
845 END IF;
846
847 IF (C_hdr_freight_terms_code%FOUND AND (x_freight_terms_code <> FND_API.G_MISS_CHAR)) THEN
848 CLOSE C_hdr_freight_terms_code;
849 return x_freight_terms_code;
850 ELSE
851 CLOSE C_hdr_freight_terms_code;
852 return null;
853 END IF;
854
855 ELSIF x_freight_terms_code = FND_API.G_MISS_CHAR THEN
856 CLOSE C_line_freight_terms_code;
857 return null;
858 END IF;
859
860 CLOSE C_line_freight_terms_code;
861 return x_freight_terms_code;
862
863 END Get_freight_terms_code ;
864
865
866 FUNCTION Get_shipping_method_code(p_qte_line_id NUMBER)
867 RETURN VARCHAR2
868 IS
869 CURSOR C_line_ship_method_code IS
870 SELECT qs.ship_method_code
871 FROM aso_shipments qs
872 WHERE qs.quote_line_id = p_qte_line_id
873 and qs.ship_method_code is not null;
874
875 CURSOR C_hdr_ship_method_code IS
876 SELECT qs.ship_method_code
877 FROM aso_shipments qs, aso_quote_lines_all ql
878 WHERE qs.quote_header_id = ql.quote_header_id
879 and ql.quote_line_id = p_qte_line_id
880 and qs.quote_line_id IS NULL;
881
882 x_ship_method_code VARCHAR2(30);
883
884 BEGIN
885
886 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
887 aso_debug_pub.add('Get_shipping_method_code - Begin ',1,'Y');
888 aso_debug_pub.add('p_qte_line_id: '||nvl(to_char(p_qte_line_id),'null'),1,'N');
889 END IF;
890
891 OPEN C_line_ship_method_code;
892 FETCH C_line_ship_method_code INTO x_ship_method_code;
893
894 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
895 aso_debug_pub.add('Line Level x_ship_method_code: '||nvl(x_ship_method_code,'null'),1,'N');
896 END IF;
897
898 IF (C_line_ship_method_code%NOTFOUND) THEN
899
900 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
901 aso_debug_pub.add('Inside first IF statement ',1,'N');
902 END IF;
903
904 CLOSE C_line_ship_method_code;
905 OPEN C_hdr_ship_method_code;
906 FETCH C_hdr_ship_method_code INTO x_ship_method_code;
907
908 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
909 aso_debug_pub.add('Header Level x_ship_method_code: '||nvl(x_ship_method_code,'null'),1,'N');
910 END IF;
911
912 IF (C_hdr_ship_method_code%FOUND AND (x_ship_method_code <> FND_API.G_MISS_CHAR)) THEN
913 CLOSE C_hdr_ship_method_code;
914 return x_ship_method_code;
915 ELSE
916 CLOSE C_hdr_ship_method_code;
917 return null;
918 END IF;
919 ELSIF x_ship_method_code = FND_API.G_MISS_CHAR THEN
920 CLOSE C_line_ship_method_code;
921 return null;
922 END IF;
923 CLOSE C_line_ship_method_code;
924 return x_ship_method_code;
925 EXCEPTION
926 WHEN OTHERS THEN
927 RETURN NULL;
928
929
930 END Get_shipping_method_code ;
931
932
933 FUNCTION Get_top_model_item_id(p_qte_line_id NUMBER)
934 RETURN NUMBER
935 IS
936
937
938 x_top_model_line_id NUMBER;
939 lv_quote_line_id NUMBER;
940 x_inventory_item_id NUMBER;
941
942 CURSOR C_top_model_line_id(l_quote_line_id NUMBER) IS
943 select quote_line_id
944 from aso_line_relationships aso_rel
945 where aso_rel.related_quote_line_id = l_quote_line_id;
946
947 CURSOR C_item_id IS
948 select inventory_item_id
949 from aso_quote_lines_all
950 where quote_line_id = x_top_model_line_id;
951
952
953 BEGIN
954 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
955 aso_debug_pub.add('Get_top_model_item_id - Begin ',1,'Y');
956 aso_debug_pub.add('Get_top_model_item_id G_LINE_REC.MODEL_ID '||ASO_PRICING_INT.G_LINE_REC.MODEL_ID,1,'Y');
957 END IF;
958 /* iStore Cataloge is directly setting G_LINE_REC.model_id,Hence we need to return the same value*/
959 IF ASO_PRICING_INT.G_LINE_REC.MODEL_ID IS NOT NULL AND ASO_PRICING_INT.G_LINE_REC.MODEL_ID <> FND_API.G_MISS_NUM THEN
960 Return ASO_PRICING_INT.G_LINE_REC.MODEL_ID;
961 END IF;
962 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
963 aso_debug_pub.add('p_qte_line_id: '||nvl(to_char(p_qte_line_id),'null'),1,'N');
964 END IF;
965
966 x_top_model_line_id := p_qte_line_id;
967
968 OPEN C_top_model_line_id(x_top_model_line_id);
969 FETCH C_top_model_line_id INTO lv_quote_line_id;
970
971 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
972 aso_debug_pub.add('Before loop - lv_quote_line_id: '||nvl(to_char(lv_quote_line_id),'null'),1,'N');
973 END IF;
974
975 IF (C_top_model_line_id%NOTFOUND) THEN
976 CLOSE C_top_model_line_id;
977 return null;
978 END IF;
979 x_top_model_line_id := lv_quote_line_id;
980 CLOSE C_top_model_line_id;
981
982 Loop
983 OPEN C_top_model_line_id(x_top_model_line_id);
984 FETCH C_top_model_line_id INTO lv_quote_line_id;
985
986 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
987 aso_debug_pub.add('Inside Loop - lv_quote_line_id: '||nvl(to_char(lv_quote_line_id),'null'),1,'N');
988 END IF;
989
990 IF (C_top_model_line_id%NOTFOUND) THEN
991 CLOSE C_top_model_line_id;
992 EXIT;
993 END IF;
994
995 CLOSE C_top_model_line_id;
996 x_top_model_line_id := lv_quote_line_id;
997 End Loop;
998
999 open C_item_id;
1000 fetch C_item_id INTO x_inventory_item_id;
1001 IF C_item_id%FOUND AND x_inventory_item_id <> FND_API.G_MISS_NUM THEN
1002 return x_inventory_item_id;
1003 ELSE
1004 return null;
1005 END IF;
1006
1007 EXCEPTION
1008 WHEN OTHERS THEN
1009 RETURN NULL;
1010
1011 END Get_top_model_item_id ;
1012
1013 FUNCTION Get_header_ship_flag(p_qte_header_id NUMBER)
1014 RETURN VARCHAR2
1015 IS
1016 x_shippable_item_flag VARCHAR2(1);
1017 --x_count number;
1018 x_count varchar2(1);
1019
1020 CURSOR C_Is_Item_Shippable IS
1021 SELECT 'x'
1022 FROM aso_quote_lines_all l
1023 WHERE l.quote_header_id = p_qte_header_id
1024 AND EXISTS ( SELECT null
1025 FROM mtl_system_items_b i
1026 WHERE l.inventory_item_id = i.inventory_item_id
1027 AND l.organization_id = i.organization_id
1028 AND i.shippable_item_flag = 'Y' );
1029
1030 BEGIN
1031 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1032 aso_debug_pub.add('Inside Get_header_ship_flag',1,'N');
1033 aso_debug_pub.add('p_qte_header_id: '||nvl(to_char(p_qte_header_id),'null'),1,'N');
1034 END IF;
1035
1036 --SELECT count(*)
1037 -- INTO x_count
1038 --FROM aso_i_items_v i, aso_quote_lines_all l,
1039 --aso_quote_headers_all h
1040 --WHERE l.quote_header_id=h.quote_header_id
1041 --and h.quote_header_id = p_qte_header_id
1042 --and l.inventory_item_id = i.inventory_item_id
1043 --and l.organization_id = i.organization_id
1044 --and i.shippable_item_flag = 'Y';
1045
1046 /****IF x_count > 0 THEN x_shippable_item_flag:='Y';
1047 END IF;
1048 IF x_count = 0 THEN x_shippable_item_flag:='N';
1049 END IF;
1050 *******/
1051
1052 For C_Is_Item_Shippable_Rec in C_Is_Item_Shippable loop
1053 If C_Is_Item_Shippable%found then
1054 x_shippable_item_flag:='Y';
1055 exit;
1056 else
1057 x_shippable_item_flag:='N';
1058 end if;
1059 end loop;
1060 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1061 aso_debug_pub.add('x_shippable_item_flag: '||x_shippable_item_flag,1,'N');
1062 END IF;
1063
1064 RETURN x_shippable_item_flag;
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 RETURN NULL;
1068
1069 -- EXCEPTION
1070 -- WHEN no_data_found THEN
1071 -- x_shippable_item_flag := null;
1072 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1073 aso_debug_pub.add('Inside Get_header_ship_flag '||x_shippable_item_flag,1,'N');
1074 END IF;
1075
1076 END get_header_ship_flag;
1077
1078 FUNCTION Get_Parent_List_price (p_quote_line_id NUMBER)
1079 RETURN NUMBER
1080 IS
1081 x_list_price NUMBER :=NULL;
1082 l_SERVICE_REF_TYPE_CODE varchar2(20);
1083 l_service_ref_line_id NUMBER;
1084
1085 Cursor c_get (l_quote_line_id NUMBER) IS
1086 SELECT SERVICE_REF_TYPE_CODE,service_ref_line_id
1087 FROM aso_quote_line_details qld
1088 WHERE qld.quote_line_id = l_quote_line_id ;
1089
1090 BEGIN
1091 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1092 aso_debug_pub.add('Inside Get_Parent_List_price',1,'N');
1093 aso_debug_pub.add('p_qte_line_id: '||nvl(to_char(p_quote_line_id),'null'),1,'N');
1094 END IF;
1095 OPEN c_get(p_quote_line_id );
1096 FETCH c_get INTO l_SERVICE_REF_TYPE_CODE,l_service_ref_line_id;
1097
1098 IF l_SERVICE_REF_TYPE_CODE = 'QUOTE' Then
1099 select line_list_price
1100 INTO x_list_price
1101 from aso_quote_lines_all ql
1102 where ql.quote_line_id = l_service_ref_line_id;
1103 Elsif l_SERVICE_REF_TYPE_CODE = 'ORDER' then
1104 select unit_list_price
1105 INTO x_list_price
1106 from oe_order_lines_All
1107 where line_id = l_service_ref_line_id;
1108 END IF;
1109 Close c_get;
1110 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1111 aso_debug_pub.add('Inside Get_Parent_List_price'||x_list_price,1,'N');
1112 END IF;
1113 return x_list_price;
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 RETURN NULL;
1117
1118 END Get_Parent_List_price;
1119
1120
1121 FUNCTION Get_Minisite_Id RETURN NUMBER
1122 IS
1123 x_minisite_id NUMBER := NULL;
1124 BEGIN
1125 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1126 aso_debug_pub.add('Start of Get_Minisite_Id...',1,'N');
1127 aso_debug_pub.add('G_HEADER_REC.minisite_id: '||nvl(to_char(ASO_PRICING_INT.G_HEADER_REC.minisite_id),'null'),1,'N');
1128 aso_debug_pub.add('G_LINE_REC.minisite_id: '||nvl(to_char(ASO_PRICING_INT.G_LINE_REC.minisite_id),'null'),1,'N');
1129 END IF;
1130 IF ASO_PRICING_INT.G_LINE_REC.MINISITE_ID IS NOT NULL AND
1131 ASO_PRICING_INT.G_LINE_REC.MINISITE_ID <> FND_API.G_MISS_NUM
1132 THEN
1133 x_minisite_id := ASO_PRICING_INT.G_LINE_REC.MINISITE_ID;
1134 ELSIF ASO_PRICING_INT.G_HEADER_REC.MINISITE_ID IS NOT NULL AND
1135 ASO_PRICING_INT.G_HEADER_REC.MINISITE_ID <> FND_API.G_MISS_NUM
1136 THEN
1137 x_minisite_id := ASO_PRICING_INT.G_HEADER_REC.MINISITE_ID;
1138 END IF;
1139 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1140 aso_debug_pub.add('After Get_Minisite_Id...x_minisite_id :'||x_minisite_id,1,'N');
1141 END IF;
1142 RETURN x_minisite_id;
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 RETURN NULL;
1146 END Get_Minisite_Id;
1147
1148 End ASO_SOURCING_PVT;
[Home] [Help]