[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_CACHE
Source
1 PACKAGE BODY OE_Order_Cache AS
2 /* $Header: OEXUCCHB.pls 120.13.12010000.1 2008/07/25 07:54:59 appldev ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_Order_Cache';
7
8
9 -- Procedures that load cached entities.
10
11 PROCEDURE Enforce_List_Price
12 ( p_header_id number
13 , p_Line_Type_id Number
14 )
15 IS
16 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
17 BEGIN
18
19 if l_debug_level > 0 then
20 oe_debug_pub.add('Entering OE_ORDER_CACHE.Enforce_List_price', 1);
21 end if;
22
23 IF p_Line_Type_id IS NOT NULL THEN
24
25 IF g_Enforce_list_price_rec.Line_Type_id = FND_API.G_MISS_NUM OR
26 g_Enforce_list_price_rec.Line_Type_id <> p_Line_Type_id THEN
27
28 SELECT
29 p_Line_Type_id
30 , nvl(enforce_line_prices_flag,'N')
31 INTO
32 g_Enforce_list_price_rec.Line_Type_id
33 , g_Enforce_list_price_rec.enforce_line_prices_flag
34 from oe_line_types_v
35 WHERE LINE_TYPE_ID = p_Line_Type_id;
36
37 END IF;
38
39 END IF;
40
41 IF p_header_id IS NOT NULL and
42 g_Enforce_list_price_rec.enforce_line_prices_flag <> 'Y' THEN
43
44 IF g_Enforce_list_price_rec.header_id = FND_API.G_MISS_NUM OR
45 g_Enforce_list_price_rec.header_id <> p_header_id
46 THEN
47
48 SELECT /*MOAC_SQL_CHANGES*/
49 p_header_id
50 , nvl(oot.enforce_line_prices_flag,'N')
51 INTO
52 g_Enforce_list_price_rec.header_id
53 , g_Enforce_list_price_rec.enforce_line_prices_flag
54 from oe_Order_types_v oot,oe_order_headers_all ooh
55 where oot.order_type_id= ooh.order_type_id and
56 ooh.header_id = p_header_id and ooh.org_id=oot.org_id;
57
58 END IF;
59
60 END IF;
61
62 if l_debug_level > 0 then
63 oe_debug_pub.add('Exiting OE_ORDER_CACHE.Enforce_List_price', 1);
64 end if;
65
66 EXCEPTION
67
68 When no_data_found then
69 g_Enforce_list_price_rec.enforce_line_prices_flag :='N';
70
71 WHEN OTHERS THEN
72
73 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
74 THEN
75 OE_MSG_PUB.Add_Exc_Msg
76 ( G_PKG_NAME ,
77 'Enforce_List_price'
78 );
79 END IF;
80
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82
83 END Enforce_List_price;
84
85 FUNCTION Load_Order_Type
86 ( p_key IN NUMBER )
87 RETURN Order_Type_Rec_Type
88 IS
89 BEGIN
90 Load_Order_Type(p_key);
91
92 RETURN g_order_type_rec;
93 END Load_Order_Type;
94
95 PROCEDURE Load_Order_Type
96 ( p_key IN NUMBER )
97 IS
98 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
99 BEGIN
100
101 if l_debug_level > 0 then
102 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_ORDER_TYPE', 1);
103 end if;
104
105 IF p_key IS NOT NULL THEN
106
107 IF g_order_type_rec.order_type_id = FND_API.G_MISS_NUM OR
108 g_order_type_rec.order_type_id <> p_key THEN
109
110 oe_debug_pub.add('Loading order Type');
111 SELECT ORDER_TYPE_ID
112 , NAME
113 , INVOICING_RULE_ID
114 , ACCOUNTING_RULE_ID
115 , PRICE_LIST_ID
116 , SHIPMENT_PRIORITY_CODE
117 , fob_point_code FOB_POINT_CODE
118 , FREIGHT_TERMS_CODE
119 , warehouse_id SHIP_FROM_ORG_ID
120 , AGREEMENT_TYPE_CODE
121 , shipping_method_code SHIPPING_METHOD_CODE
122 , AGREEMENT_REQUIRED_FLAG
123 , PO_REQUIRED_FLAG
124 , enforce_line_prices_flag
125 , auto_scheduling_flag
126 -- QUOTING changes
127 , quote_num_as_ord_num_flag
128 , invoice_source_id
129 , non_delivery_invoice_source_id
130 , cust_trx_type_id
131 --added for bug 4200055
132 , start_date_active
133 , end_date_active
134 , tax_calculation_event_code
135 INTO g_order_type_rec.order_type_id
136 , g_order_type_rec.name
137 , g_order_type_rec.invoicing_rule_id
138 , g_order_type_rec.accounting_rule_id
139 , g_order_type_rec.price_list_id
140 , g_order_type_rec.shipment_priority_code
141 , g_order_type_rec.fob_point_code
142 , g_order_type_rec.freight_terms_code
143 , g_order_type_rec.ship_from_org_id
144 , g_order_type_rec.agreement_type_code
145 , g_order_type_rec.shipping_method_code
146 , g_order_type_rec.agreement_required_flag
147 , g_order_type_rec.require_po_flag
148 , g_order_type_rec.enforce_line_prices_flag
149 , g_order_type_rec.auto_scheduling_flag
150 -- QUOTING changes
151 , g_order_type_rec.quote_num_as_ord_num_flag
152 , g_order_type_rec.invoice_source_id
153 , g_order_type_rec.non_delivery_invoice_source_id
154 , g_order_type_rec.cust_trx_type_id
155 -- added for bug 4200055
156 , g_order_type_rec.start_date_active
157 , g_order_type_rec.end_date_active
158 , g_order_type_rec.tax_calculation_event_code
159 FROM OE_ORDER_TYPES_V
160 WHERE ORDER_TYPE_ID = p_key;
161
162 END IF;
163
164 END IF;
165
166 if l_debug_level > 0 then
167 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_ORDER_TYPE', 1);
168 end if;
169
170 EXCEPTION
171
172 WHEN NO_DATA_FOUND THEN
173 RAISE NO_DATA_FOUND ;
174
175
176 WHEN OTHERS THEN
177
178 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
179 THEN
180 OE_MSG_PUB.Add_Exc_Msg
181 ( G_PKG_NAME ,
182 'Load_Order_Type'
183 );
184 END IF;
185
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187
188 END Load_Order_Type;
189
190 ------------------------------------------------------------
191 -- Bug 1929163: overload load_line_type so that it can be accessed
192 -- both as a function and as a procedure
193 ------------------------------------------------------------
194 FUNCTION Load_Line_Type
195 ( p_key IN NUMBER )
196 RETURN Line_Type_Rec_Type
197 IS
198 BEGIN
199
200 Load_Line_Type(p_key);
201
202 RETURN g_line_type_rec;
203
204 END Load_Line_Type;
205
206 PROCEDURE Load_Line_Type
207 ( p_key IN NUMBER )
208 IS
209 l_calculate_tax_flag varchar2(1) := NULL;
210 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
211 BEGIN
212
213 if l_debug_level > 0 then
214 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_LINE_TYPE', 1);
215 end if;
216
217 IF p_key IS NOT NULL THEN
218
219 if l_debug_level > 0 then
220 oe_debug_pub.add('p_key is not null', 1);
221 end if;
222 IF g_line_type_rec.line_type_id = FND_API.G_MISS_NUM OR
223 g_line_type_rec.line_type_id <> p_key THEN
224
225 if l_debug_level > 0 then
226 oe_debug_pub.add('before selecting line_type info : ' || g_line_type_rec.calculate_tax_flag, 1);
227 end if;
228 SELECT LINE_TYPE_ID
229 , NAME
230 , CUST_TRX_TYPE_ID
231 , INVOICING_RULE_ID
232 , ACCOUNTING_RULE_ID
233 , PRICE_LIST_ID
234 , SHIPMENT_PRIORITY_CODE
235 , fob_point_code FOB_POINT_CODE
236 , FREIGHT_TERMS_CODE
237 , warehouse_id SHIP_FROM_ORG_ID
238 , AGREEMENT_TYPE_CODE
239 , shipping_method_code SHIPPING_METHOD_CODE
240 , AGREEMENT_REQUIRED_FLAG
241 , enforce_line_prices_flag
242 , order_category_code
243 , ship_source_type_code
244 , invoice_source_id
245 , non_delivery_invoice_source_id
246 --added for bug 4200055
247 , start_date_active
248 , end_date_active
249 , tax_calculation_event_code
250 INTO g_line_type_rec.line_type_id
251 , g_line_type_rec.name
252 , g_line_type_rec.cust_trx_type_id
253 , g_line_type_rec.invoicing_rule_id
254 , g_line_type_rec.accounting_rule_id
255 , g_line_type_rec.price_list_id
256 , g_line_type_rec.shipment_priority_code
257 , g_line_type_rec.fob_point_code
258 , g_line_type_rec.freight_terms_code
259 , g_line_type_rec.ship_from_org_id
260 , g_line_type_rec.agreement_type_code
261 , g_line_type_rec.shipping_method_code
262 , g_line_type_rec.agreement_required_flag
263 , g_line_type_rec.enforce_line_prices_flag
264 , g_line_type_rec.order_category_code
265 , g_line_type_rec.ship_source_type_code
266 , g_line_type_rec.invoice_source_id
267 , g_line_type_rec.non_delivery_invoice_source_id
268 -- added for bug 4200055
269 , g_line_type_rec.start_date_active
270 , g_line_type_rec.end_date_active
271 , g_line_type_rec.tax_calculation_event_code
272 FROM OE_LINE_TYPES_V
273 WHERE LINE_TYPE_ID = p_key;
274
275 if l_debug_level > 0 then
276 oe_debug_pub.add('after selecting line_type info : ' || g_line_type_rec.calculate_tax_flag, 1);
277 end if;
278 IF g_line_type_rec.cust_trx_type_id IS NOT NULL
279 THEN
280
281 SELECT tax_calculation_flag
282 INTO l_calculate_tax_flag
283 FROM RA_CUST_TRX_TYPES
284 WHERE CUST_TRX_TYPE_ID = g_line_type_rec.cust_trx_type_id;
285
286 if l_debug_level > 0 then
287 oe_debug_pub.add('after selecting tax_flag info : ' || l_calculate_tax_flag, 1);
288 end if;
289 END IF;
290
291 g_line_type_rec.calculate_tax_flag := l_calculate_tax_flag;
292
293 if l_debug_level > 0 then
294 oe_debug_pub.add('tax_flag : ' || g_line_type_rec.calculate_tax_flag, 1);
295 end if;
296
297 END IF;
298
299 END IF;
300
301 if l_debug_level > 0 then
302 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_LINE_TYPE', 1);
303 end if;
304
305 EXCEPTION
306
307 WHEN NO_DATA_FOUND THEN
308
309 RAISE NO_DATA_FOUND ;
310
311 WHEN OTHERS THEN
312
313 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
314 THEN
315 OE_MSG_PUB.Add_Exc_Msg
316 ( G_PKG_NAME ,
317 'Load_Line_Type'
318 );
319 END IF;
320
321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
322 END Load_Line_Type;
323
324
325 FUNCTION Load_Agreement
326 ( p_key IN NUMBER )
327 RETURN Agreement_Rec_Type
328 IS
329 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
330 BEGIN
331
332 if l_debug_level > 0 then
333 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_AGREEMENT', 1);
334 end if;
335
336 IF p_key IS NOT NULL THEN
337
338 IF g_agreement_rec.agreement_id = FND_API.G_MISS_NUM OR
339 g_agreement_rec.agreement_id <> p_key THEN
340
341 SELECT AGREEMENT_ID
342 , INVOICING_RULE_ID
343 , ACCOUNTING_RULE_ID
344 , PRICE_LIST_ID
345 , CUST_PO_NUMBER
346 , PAYMENT_TERM_ID
347 , INVOICE_TO_ORG_ID
348 , INVOICE_TO_CONTACT_ID
349 , AGREEMENT_TYPE_CODE
350 , DECODE(SOLD_TO_ORG_ID,-1,NULL,SOLD_TO_ORG_ID)
351 INTO g_agreement_rec.agreement_id
352 , g_agreement_rec.invoicing_rule_id
353 , g_agreement_rec.accounting_rule_id
354 , g_agreement_rec.price_list_id
355 , g_agreement_rec.cust_po_number
356 , g_agreement_rec.payment_term_id
357 , g_agreement_rec.invoice_to_org_id
358 , g_agreement_rec.invoice_to_contact_id
359 , g_agreement_rec.agreement_type_code
360 , g_agreement_rec.sold_to_org_id
361 FROM OE_AGREEMENTS_V
362 WHERE AGREEMENT_ID = p_key;
363 NULL;
364
365 END IF;
366
367 END IF;
368
369 if l_debug_level > 0 then
370 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_AGREEMENT', 1);
371 end if;
372
373 RETURN g_agreement_rec;
374
375 EXCEPTION
376
377 WHEN OTHERS THEN
378
379 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
380 THEN
381 OE_MSG_PUB.Add_Exc_Msg
382 ( G_PKG_NAME ,
383 'Load_Agreement'
384 );
385 END IF;
386
387 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
388
389 END Load_Agreement;
390
391 FUNCTION Load_Ship_To_Org
392 ( p_key IN NUMBER )
393 RETURN Ship_To_Org_Rec_Type
394 IS
395 l_bill_to_site_use_id NUMBER := NULL;
396 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
397 BEGIN
398
399 if l_debug_level > 0 then
400 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_SHIP_TO_ORG', 1);
401 end if;
402
403 IF p_key IS NOT NULL THEN
404
405 IF g_ship_to_rec.org_id = FND_API.G_MISS_NUM OR
406 g_ship_to_rec.org_id <> p_key THEN
407
408 SELECT ORGANIZATION_ID
409 , PRICE_LIST_ID
410 , FOB_POINT_CODE
411 , FREIGHT_TERMS_CODE
412 , SOLD_FROM_ORG_ID
413 , SHIP_FROM_ORG_ID
414 , CONTACT_ID
415 , SHIP_PARTIAL_ALLOWED
416 , SHIPPING_METHOD_CODE
417 , BILL_TO_SITE_USE_ID
418 INTO g_ship_to_rec.org_id
419 , g_ship_to_rec.price_list_id
420 , g_ship_to_rec.fob_point_code
421 , g_ship_to_rec.freight_terms_code
422 , g_ship_to_rec.sold_from_org_id
423 , g_ship_to_rec.ship_from_org_id
424 , g_ship_to_rec.contact_id
425 , g_ship_to_rec.ship_partial_allowed
426 , g_ship_to_rec.shipping_method_code
427 , l_bill_to_site_use_id
428 FROM OE_SHIP_TO_ORGS_V
429 WHERE ORGANIZATION_ID = p_key;
430
431 -- Fetch Invoice to org id.
432
433 IF l_bill_to_site_use_id IS NOT NULL THEN
434
435 BEGIN
436
437 SELECT ORGANIZATION_ID
438 INTO g_ship_to_rec.invoice_to_org_id
439 FROM OE_INVOICE_TO_ORGS_V
440 WHERE SITE_USE_ID = l_bill_to_site_use_id;
441
442 EXCEPTION
443
444 WHEN NO_DATA_FOUND THEN
445 -- Kris - is this right??
446 g_ship_to_rec.invoice_to_org_id := NULL;
447
448 WHEN OTHERS THEN
449
450 IF OE_MSG_PUB.Check_Msg_Level
451 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
452 THEN
453 OE_MSG_PUB.Add_Exc_Msg
454 ( G_PKG_NAME
455 , 'Load_Ship_To_Org - Fetch Bill to Site'
456 );
457 END IF;
458
459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
460
461 END; -- Fetching Bill To Site
462
463 END IF;
464
465 -- Fetch payment term
466
467 BEGIN
468
469 SELECT STANDARD_TERMS
470 INTO g_ship_to_rec.payment_term_id
471 FROM HZ_CUSTOMER_PROFILES
472 WHERE SITE_USE_ID = p_key;
473
474 EXCEPTION
475
476 WHEN NO_DATA_FOUND THEN
477
478 g_ship_to_rec.payment_term_id := NULL;
479
480 WHEN OTHERS THEN
481
482 IF OE_MSG_PUB.Check_Msg_Level
483 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
484 THEN
485 OE_MSG_PUB.Add_Exc_Msg
486 ( G_PKG_NAME
487 , 'Load_Ship_To_Org - Fetch Payment Terms'
488 );
489 END IF;
490
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492
493 END; -- Begin fetching payment terms.
494
495 END IF;
496
497 END IF;
498
499 if l_debug_level > 0 then
500 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_SHIP_TO_ORG', 1);
501 end if;
502
503 RETURN g_ship_to_rec;
504
505 EXCEPTION
506
507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
508
509 RAISE;
510
511 WHEN OTHERS THEN
512
513 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
514 THEN
515 OE_MSG_PUB.Add_Exc_Msg
516 ( G_PKG_NAME ,
517 'Load_Ship_To_Org'
518 );
519 END IF;
520
521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
522
523 END Load_Ship_To_Org;
524
525 FUNCTION Load_Invoice_To_Org
526 ( p_key IN NUMBER )
527 RETURN Invoice_to_Org_Rec_Type
528 IS
529 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
530 BEGIN
531
532 if l_debug_level > 0 then
533 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_INVOICE_TO_ORG', 1);
534 end if;
535
536 IF p_key IS NOT NULL THEN
537
538 IF g_invoice_to_rec.org_id = FND_API.G_MISS_NUM OR
539 g_invoice_to_rec.org_id <> p_key THEN
540
541 oe_debug_pub.add('Loading Invoice to Org Cache');
542 SELECT ORGANIZATION_ID
543 , PRICE_LIST_ID
544 , FOB_POINT_CODE
545 , FREIGHT_TERMS_CODE
546 , CONTACT_ID
547 , SHIP_PARTIAL_ALLOWED
548 , SHIPPING_METHOD_CODE
549 --added for bug 4200055
550 , STATUS
551 , ADDRESS_STATUS
552 , START_DATE_ACTIVE
553 , END_DATE_ACTIVE
554 INTO g_invoice_to_rec.org_id
555 , g_invoice_to_rec.price_list_id
556 , g_invoice_to_rec.fob_point_code
557 , g_invoice_to_rec.freight_terms_code
558 , g_invoice_to_rec.contact_id
559 , g_invoice_to_rec.ship_partial_allowed
560 , g_invoice_to_rec.shipping_method_code
561 --added for bug 4200055
562 , g_invoice_to_rec.status
563 , g_invoice_to_rec.address_status
564 , g_invoice_to_rec.start_date_active
565 , g_invoice_to_rec.end_date_active
566 FROM OE_INVOICE_TO_ORGS_V
567 WHERE ORGANIZATION_ID = p_key;
568
569 -- Fetch payment term
570
571 BEGIN
572
573 SELECT STANDARD_TERMS
574 INTO g_invoice_to_rec.payment_term_id
575 FROM HZ_CUSTOMER_PROFILES
576 WHERE SITE_USE_ID = p_key;
577
578 EXCEPTION
579
580 WHEN NO_DATA_FOUND THEN
581
582 g_invoice_to_rec.payment_term_id := NULL;
583
584 WHEN OTHERS THEN
585
586 IF OE_MSG_PUB.Check_Msg_Level
587 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
588 THEN
589 OE_MSG_PUB.Add_Exc_Msg
590 ( G_PKG_NAME
591 , 'Load_Invoice_To_Org - Fetch Payment Terms'
592 );
593 END IF;
594
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596
597 END; -- Begin fetching payment terms.
598
599 END IF;
600
601 END IF;
602
603 if l_debug_level > 0 then
604 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_INVOICE_TO_ORG', 1);
605 end if;
606
607 RETURN g_invoice_to_rec;
608
609 EXCEPTION
610 WHEN NO_DATA_FOUND THEN
611
612 RAISE NO_DATA_FOUND ;
613
614
615 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
616
617 RAISE;
618
619 WHEN OTHERS THEN
620
621 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
622 THEN
623 OE_MSG_PUB.Add_Exc_Msg
624 ( G_PKG_NAME ,
625 'Load_Invoice_To_Org'
626 );
627 END IF;
628
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630
631 END Load_Invoice_To_Org;
632
633 FUNCTION Load_Deliver_To_Org
634 ( p_key IN NUMBER )
635 RETURN Deliver_To_Org_Rec_Type
636 IS
637 l_bill_to_site_use_id NUMBER := NULL;
638 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
639 BEGIN
640
641 if l_debug_level > 0 then
642 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_DELIVER_TO_ORG', 1);
643 end if;
644
645 IF p_key IS NOT NULL THEN
646
647 IF g_deliver_to_rec.org_id = FND_API.G_MISS_NUM OR
648 g_deliver_to_rec.org_id <> p_key THEN
649
650 SELECT ORGANIZATION_ID
651 , PRICE_LIST_ID
652 , FOB_POINT_CODE
653 , FREIGHT_TERMS_CODE
654 , SOLD_FROM_ORG_ID
655 , SHIP_FROM_ORG_ID
656 , CONTACT_ID
657 , SHIP_PARTIAL_ALLOWED
658 , SHIPPING_METHOD_CODE
659 , BILL_TO_SITE_USE_ID
660 INTO g_deliver_to_rec.org_id
661 , g_deliver_to_rec.price_list_id
662 , g_deliver_to_rec.fob_point_code
663 , g_deliver_to_rec.freight_terms_code
664 , g_deliver_to_rec.sold_from_org_id
665 , g_deliver_to_rec.ship_from_org_id
666 , g_deliver_to_rec.contact_id
667 , g_deliver_to_rec.ship_partial_allowed
668 , g_deliver_to_rec.shipping_method_code
669 , l_bill_to_site_use_id
670 FROM OE_DELIVER_TO_ORGS_V
671 WHERE ORGANIZATION_ID = p_key;
672
673 -- Fetch Invoice to org id.
674
675 IF l_bill_to_site_use_id IS NOT NULL THEN
676
677 SELECT ORGANIZATION_ID
678 INTO g_deliver_to_rec.invoice_to_org_id
679 FROM OE_INVOICE_TO_ORGS_V
680 WHERE SITE_USE_ID = l_bill_to_site_use_id;
681
682 END IF;
683
684 END IF;
685
686 END IF;
687
688 if l_debug_level > 0 then
689 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_DELIVER_TO_ORG', 1);
690 end if;
691
692 RETURN g_deliver_to_rec;
693
694 EXCEPTION
695
696 WHEN OTHERS THEN
697
698 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
699 THEN
700 OE_MSG_PUB.Add_Exc_Msg
701 ( G_PKG_NAME ,
702 'Load_Deliver_To_Org'
703 );
704 END IF;
705
706 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
707
708 END Load_Deliver_To_Org;
709
710 FUNCTION Load_Sold_To_Org
711 ( p_key IN NUMBER )
712 RETURN Sold_To_Org_Rec_Type
713 IS
714 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
715 l_current_org_id NUMBER ; -- MOAC Changes
716 BEGIN
717
718 if l_debug_level > 0 then
719 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_SOLD_TO_ORG', 1);
720 end if;
721 --MOAC Changes
722 --The Sold_To_Org_Cache was selecting oe_sold_to_orgs_v.organization_id into g_sold_to_rec.org_id.
723 --To have a code consistency added a new column "sold_to_org_id" in "Sold_To_Org_Rec_Type".
724 --Now oe_sold_to_orgs_v.organization_id is selected into g_sold_to_rec.sold_to_org_id and
725 --the OU in g_sold_to_rec.org_id
726
727 l_current_org_id := MO_Global.Get_Current_Org_Id() ;
728
729 IF p_key IS NOT NULL THEN
730
731 IF g_sold_to_rec.sold_to_org_id = FND_API.G_MISS_NUM OR
732 g_sold_to_rec.sold_to_org_id <> p_key OR
733 g_sold_to_rec.org_id <> l_current_org_id THEN
734
735 SELECT ORGANIZATION_ID
736 , PRICE_LIST_ID
737 , FOB_POINT_CODE
738 , FREIGHT_TERMS_CODE
739 , SHIP_PARTIAL_ALLOWED
740 , SHIPPING_METHOD_CODE
741 , ORDER_TYPE_ID
742 INTO g_sold_to_rec.sold_to_org_id --MOAC Changes
743 , g_sold_to_rec.price_list_id
744 , g_sold_to_rec.fob_point_code
745 , g_sold_to_rec.freight_terms_code
746 , g_sold_to_rec.ship_partial_allowed
747 , g_sold_to_rec.shipping_method_code
748 , g_sold_to_rec.order_type_id
749 FROM OE_SOLD_TO_ORGS_V
750 WHERE ORGANIZATION_ID = p_key;
751
752 g_sold_to_rec.org_id := l_current_org_id ; -- MOAC Changes
753
754 -- Fetch Invoice to org.
755
756 BEGIN
757
758 SELECT /*MOAC_SQL_CHANGES*/ INV.ORGANIZATION_ID
759 INTO g_sold_to_rec.invoice_to_org_id
760 FROM OE_INVOICE_TO_ORGS_V INV
761 ,HZ_CUST_ACCT_SITES_ALL ADDR
762 WHERE ADDR.CUST_ACCOUNT_ID = p_key
763 AND ADDR.BILL_TO_FLAG = 'P'
764 AND ADDR.STATUS = 'A'
765 AND INV.ADDRESS_ID = ADDR.CUST_ACCT_SITE_ID
766 AND INV.PRIMARY_FLAG = 'Y'
767 AND INV.STATUS = 'A' and inv.org_id=addr.org_id
768 and INV.address_status='A'; --2752321
769
770 EXCEPTION
771
772 WHEN NO_DATA_FOUND THEN
773
774 g_sold_to_rec.invoice_to_org_id := NULL;
775
776 WHEN OTHERS THEN
777
778 IF OE_MSG_PUB.Check_Msg_Level
779 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
780 THEN
781 OE_MSG_PUB.Add_Exc_Msg
782 ( G_PKG_NAME
783 , 'Load_Sold_To_Org - Fetch Invoice To'
784 );
785 END IF;
786
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
788
789 END; -- Begin fetching Invoice to.
790
791 -- Fetch Ship to org.
792
793 BEGIN
794
795 SELECT /*MOAC_SQL_CHANGES*/ SHIP.ORGANIZATION_ID
796 INTO g_sold_to_rec.ship_to_org_id
797 FROM OE_SHIP_TO_ORGS_V SHIP
798 , HZ_CUST_ACCT_SITES_ALL ADDR
799 WHERE ADDR.CUST_ACCOUNT_ID = p_key
800 AND ADDR.SHIP_TO_FLAG = 'P'
801 AND ADDR.STATUS = 'A'
802 AND SHIP.ADDRESS_ID = ADDR.CUST_ACCT_SITE_ID
803 AND SHIP.PRIMARY_FLAG = 'Y'
804 AND SHIP.STATUS = 'A' and ship.org_id=addr.org_id
805 and ship.address_status='A'; --2752321
806
807 EXCEPTION
808
809 WHEN NO_DATA_FOUND THEN
810
811 g_sold_to_rec.ship_to_org_id := NULL;
812
813 WHEN OTHERS THEN
814
815 IF OE_MSG_PUB.Check_Msg_Level
816 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
817 THEN
818 OE_MSG_PUB.Add_Exc_Msg
819 ( G_PKG_NAME
820 , 'Load_Sold_To_Org - Fetch Ship To'
821 );
822 END IF;
823
824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
825
826 END; -- Begin fetching Ship to.
827
828 -- Fetch Deliver to org
829
830 BEGIN
831
832 SELECT /*MOAC_SQL_CHANGES*/ DEL.ORGANIZATION_ID
833 INTO g_sold_to_rec.deliver_to_org_id
834 FROM OE_DELIVER_TO_ORGS_V DEL
835 ,HZ_CUST_ACCT_SITES_ALL ADDR
836 WHERE ADDR.CUST_ACCOUNT_ID = p_key
837 AND ADDR.SHIP_TO_FLAG = 'P'
838 AND ADDR.STATUS = 'A'
839 AND DEL.ADDRESS_ID = ADDR.CUST_ACCT_SITE_ID
840 AND DEL.PRIMARY_FLAG = 'Y' and del.organization_id=addr.org_id
841 AND DEL.STATUS = 'A'
842 and DEL.address_status='A'; --2752321
843
844 EXCEPTION
845
846 WHEN NO_DATA_FOUND THEN
847
848 g_sold_to_rec.deliver_to_org_id := NULL;
849
850 WHEN OTHERS THEN
851
852 IF OE_MSG_PUB.Check_Msg_Level
853 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
854 THEN
855 OE_MSG_PUB.Add_Exc_Msg
856 ( G_PKG_NAME
857 , 'Load_Sold_To_Org - Fetch Deliver To'
858 );
859 END IF;
860
861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
862
863 END; -- Begin fetching Deliver to.
864
865 -- Fetch payment term
866
867 BEGIN
868
869 SELECT STANDARD_TERMS
870 INTO g_sold_to_rec.payment_term_id
871 FROM HZ_CUSTOMER_PROFILES
872 WHERE CUST_ACCOUNT_ID = p_key
873 AND SITE_USE_ID IS NULL;
874
875 EXCEPTION
876
877 WHEN NO_DATA_FOUND THEN
878
879 g_sold_to_rec.payment_term_id := NULL;
880
881 WHEN OTHERS THEN
882
883 IF OE_MSG_PUB.Check_Msg_Level
884 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
885 THEN
886 OE_MSG_PUB.Add_Exc_Msg
887 ( G_PKG_NAME
888 , 'Load_Sold_To_Org - Fetch Payment Term'
889 );
890 END IF;
891
892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
893
894 END; -- Begin fetching Payment Term.
895
896 END IF;
897
898 END IF;
899
900 if l_debug_level > 0 then
901 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_SOLD_TO_ORG', 1);
902 end if;
903
904 RETURN g_sold_to_rec;
905
906 EXCEPTION
907
908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
909
910 RAISE;
911
912 WHEN OTHERS THEN
913
914 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
915 THEN
916 OE_MSG_PUB.Add_Exc_Msg
917 ( G_PKG_NAME ,
918 'Load_Sold_To_Org'
919 );
920 END IF;
921
922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
923
924 END Load_Sold_To_Org;
925
926 FUNCTION Load_Price_List
927 ( p_key IN NUMBER )
928 RETURN Price_List_Rec_Type
929 IS
930 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
931 BEGIN
932
933 if l_debug_level > 0 then
934 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_PRICE_LIST', 1);
935 end if;
936 IF p_key IS NOT NULL THEN
937
938 IF g_price_list_rec.price_list_id = FND_API.G_MISS_NUM OR
939 g_price_list_rec.price_list_id <> p_key THEN
940
941 SELECT list_header_id
942 -- , PAYMENT_TERM_ID
943 , TERMS_ID
944 , SHIP_METHOD_CODE
945 , FREIGHT_TERMS_CODE
946 , CURRENCY_CODE
947 -- added for bug 4200055
948 , NAME
949 , LIST_TYPE_CODE
950 , ACTIVE_FLAG
951 , START_DATE_ACTIVE
952 , END_DATE_ACTIVE
953 INTO g_price_list_rec.price_list_id
954 , g_price_list_rec.payment_term_id
955 , g_price_list_rec.ship_method_code
956 , g_price_list_rec.freight_terms_code
957 , g_price_list_rec.currency_code
958 --added for bug 4200055
959 , g_price_list_rec.name
960 , g_price_list_rec.list_type_code
961 , g_price_list_rec.active_flag
962 , g_price_list_rec.start_date_active
963 , g_price_list_rec.end_date_active
964 -- FROM qp_list_headers_b
965 FROM qp_list_headers_vl
966 WHERE list_header_id = p_key
967 and list_type_code in ('PRL', 'AGR');
968
969 END IF;
970
971 END IF;
972
973 if l_debug_level > 0 then
974 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_PRICE_LIST', 1);
975 end if;
976
977 RETURN g_price_list_rec;
978
979 EXCEPTION
980 WHEN NO_DATA_FOUND THEN
981
982 RAISE NO_DATA_FOUND ;
983
984 WHEN OTHERS THEN
985
986 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
987 THEN
988 OE_MSG_PUB.Add_Exc_Msg
989 ( G_PKG_NAME ,
990 'Load_Price_List'
991 );
992 END IF;
993
994 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
995
996 END Load_Price_List;
997
998 FUNCTION Load_Set_Of_Books
999 RETURN Set_Of_Books_Rec_Type
1000 IS
1001 l_set_of_books_id NUMBER := NULL;
1002 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1003 BEGIN
1004
1005 if l_debug_level > 0 then
1006 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_SET_OF_BOOKS', 1);
1007 end if;
1008
1009 -- Get set_of_books_id from profile option.
1010
1011 --l_set_of_books_id := FND_PROFILE.VALUE('OE_SET_OF_BOOKS_ID');
1012 l_set_of_books_id := OE_Sys_Parameters.VALUE('SET_OF_BOOKS_ID');
1013
1014
1015 IF l_set_of_books_id IS NOT NULL THEN
1016
1017 -- Fix Bug 1910409: if operating unit changes, l_set_of_books
1018 -- would change therefore compare cached set of books to
1019 -- l_set_of_books and re-set the cache if changed.
1020 IF g_set_of_books_rec.set_of_books_id = FND_API.G_MISS_NUM
1021 OR (l_set_of_books_id <> g_set_of_books_rec.set_of_books_id)
1022 THEN
1023
1024 SELECT SET_OF_BOOKS_ID
1025 , CURRENCY_CODE
1026 INTO g_set_of_books_rec.set_of_books_id
1027 , g_set_of_books_rec.currency_code
1028 FROM OE_GL_SETS_OF_BOOKS_V
1029 WHERE SET_OF_BOOKS_ID = l_set_of_books_id;
1030
1031 END IF;
1032
1033 END IF;
1034
1035 if l_debug_level > 0 then
1036 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_SET_OF_BOOKS', 1);
1037 end if;
1038
1039 RETURN g_set_of_books_rec;
1040
1041 EXCEPTION
1042
1043 WHEN OTHERS THEN
1044
1045 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1046 THEN
1047 OE_MSG_PUB.Add_Exc_Msg
1048 ( G_PKG_NAME ,
1049 'Load_Set_Of_Books'
1050 );
1051 END IF;
1052
1053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1054
1055 END Load_Set_Of_Books;
1056
1057
1058 FUNCTION Load_Item_Cost
1059 ( p_key1 IN NUMBER
1060 , p_key2 IN NUMBER )
1061 RETURN Item_Cost_Rec_Type
1062 IS
1063 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1064 BEGIN
1065
1066 if l_debug_level > 0 then
1067 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_ITEM_COST', 1);
1068 end if;
1069
1070 IF p_key1 IS NOT NULL THEN
1071
1072 IF g_item_cost_rec.inventory_item_id = FND_API.G_MISS_NUM OR
1073 g_item_cost_rec.inventory_item_id <> p_key1 THEN
1074
1075 SELECT INVENTORY_ITEM_ID
1076 , ORGANIZATION_ID
1077 , MATERIAL_COST
1078 , MATERIAL_OVERHEAD_COST
1079 , RESOURCE_COST
1080 , OUTSIDE_PROCESSING_COST
1081 , OVERHEAD_COST
1082 INTO g_item_cost_rec.inventory_item_id
1083 , g_item_cost_rec.organization_id
1084 , g_item_cost_rec.material_cost
1085 , g_item_cost_rec.material_overhead_cost
1086 , g_item_cost_rec.resource_cost
1087 , g_item_cost_rec.outside_processing_cost
1088 , g_item_cost_rec.overhead_cost
1089 FROM CST_ITEM_COSTS
1090 WHERE INVENTORY_ITEM_ID = p_key1
1091 AND ORGANIZATION_ID = p_key2;
1092
1093 END IF;
1094
1095 END IF;
1096
1097 if l_debug_level > 0 then
1098 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_ITEM_COST', 1);
1099 end if;
1100
1101 RETURN g_item_cost_rec;
1102
1103 EXCEPTION
1104
1105 WHEN NO_DATA_FOUND THEN
1106 RETURN g_item_cost_rec;
1107
1108 WHEN OTHERS THEN
1109
1110 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1111 THEN
1112 OE_MSG_PUB.Add_Exc_Msg
1113 ( G_PKG_NAME ,
1114 'Load_Item_Cost'
1115 );
1116 END IF;
1117
1118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1119
1120 END Load_Item_Cost;
1121
1122
1123 ------------------------------------------------------------
1124 -- Bug 1929163: overload load_order_header so that it can
1125 -- be accessed both as a function and as a procedure
1126 ------------------------------------------------------------
1127 FUNCTION Load_Order_Header
1128 ( p_key IN NUMBER )
1129 RETURN OE_Order_PUB.Header_Rec_Type
1130 IS
1131 BEGIN
1132
1133 Load_Order_Header(p_key);
1134
1135 RETURN OE_ORDER_CACHE.g_header_rec;
1136
1137 END Load_Order_Header;
1138
1139 PROCEDURE Load_Order_Header
1140 ( p_key IN NUMBER )
1141 IS
1142 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1143 BEGIN
1144
1145 if l_debug_level > 0 then
1146 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_ORDER_HEADER', 1);
1147 end if;
1148
1149 IF p_key IS NOT NULL THEN
1150
1151 IF g_header_rec.header_id = FND_API.G_MISS_NUM OR
1152 nvl(g_header_rec.header_id,0) <> p_key THEN
1153
1154 OE_HEADER_UTIL.Query_Row(p_header_id => p_key,
1155 x_header_rec => g_header_rec);
1156
1157 END IF;
1158
1159 END IF;
1160
1161 if l_debug_level > 0 then
1162 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_ORDER_HEADER', 1);
1163 end if;
1164
1165 --add bug 4200055
1166 EXCEPTION
1167
1168 WHEN NO_DATA_FOUND THEN
1169
1170 RAISE NO_DATA_FOUND;
1171
1172 WHEN OTHERS THEN
1173
1174 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1175 THEN
1176 OE_MSG_PUB.Add_Exc_Msg
1177 ( G_PKG_NAME
1178 , 'Load_Order_Header'
1179 );
1180 END IF;
1181
1182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1183
1184 END Load_Order_Header;
1185
1186
1187 FUNCTION load_header_discount
1188 ( p_hdr_adj_rec IN oe_order_pub.header_adj_rec_type)
1189 RETURN OE_ORDER_PUB.Header_ADJ_REC_TYPE
1190 IS
1191 l_header_adj_rec OE_ORDER_PUB.header_adj_rec_type;
1192 l_discount_id NUMBER := p_hdr_adj_rec.discount_id;
1193 l_discount_line_id NUMBER := p_hdr_adj_rec.discount_line_id;
1194 l_adj_id NUMBER := p_hdr_adj_rec.price_adjustment_id;
1195 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1196 BEGIN
1197
1198 if l_debug_level > 0 then
1199 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_HEADER_DISCOUNT', 1);
1200 end if;
1201
1202 IF (l_adj_id IS NOT NULL) THEN
1203
1204 IF (g_hdr_discount_rec.price_adjustment_id <> l_adj_id
1205 OR g_hdr_discount_rec.discount_id <> l_discount_id
1206 OR g_hdr_discount_rec.discount_line_id <> l_discount_line_id)
1207 THEN
1208
1209 --oe_debug_pub.add('Load_header_discount. Discount_id = ' ||
1210 -- To_char(l_discount_id) ||
1211 -- ' discount_line_id = ' ||
1212 -- To_char(l_discount_line_id), 2);
1213
1214 -- The discount is uniquely identified by discount_line
1215 IF l_discount_line_id <> -1
1216 THEN
1217
1218 SELECT NVL( Nvl(sodsc.percent, sodls.percent), 0),
1219 sodsc.discount_id,
1220 NVL(sodls.discount_line_id, -1),
1221 sodsc.name,
1222 l_adj_id
1223 INTO g_hdr_discount_rec.percent,
1224 g_hdr_discount_rec.discount_id,
1225 g_hdr_discount_rec.discount_line_id,
1226 g_hdr_discount_rec.adjustment_name,
1227 g_hdr_discount_rec.price_adjustment_id
1228 FROM oe_discounts sodsc,
1229 oe_discount_lines sodls
1230 WHERE sodls.discount_line_id = l_discount_line_id
1231 AND sodls.discount_id = sodsc.discount_id;
1232
1233
1234 -- discount is uniquely identified by discount
1235 ELSE
1236
1237 SELECT Nvl(sodsc.percent, 0),
1238 sodsc.discount_id,
1239 -1,
1240 sodsc.name,
1241 l_adj_id
1242 INTO g_hdr_discount_rec.percent,
1243 g_hdr_discount_rec.discount_id,
1244 g_hdr_discount_rec.discount_line_id,
1245 g_hdr_discount_rec.adjustment_name,
1246 g_hdr_discount_rec.price_adjustment_id
1247 FROM oe_discounts sodsc
1248 WHERE l_discount_id = sodsc.discount_id;
1249
1250 END IF;
1251
1252 END IF;
1253
1254 -- Write the values to the header adj_record
1255 l_header_adj_rec.percent := g_hdr_discount_rec.percent;
1256 l_header_adj_rec.discount_id := g_hdr_discount_rec.discount_id;
1257 l_header_adj_rec.discount_line_id := g_hdr_discount_rec.discount_line_id;
1258 END IF;
1259
1260 if l_debug_level > 0 then
1261 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_HEADER_DISCOUNT', 1);
1262 end if;
1263
1264 RETURN l_header_adj_rec;
1265
1266 EXCEPTION
1267
1268 WHEN OTHERS THEN
1269
1270 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1271 THEN
1272 OE_MSG_PUB.Add_Exc_Msg
1273 ( G_PKG_NAME ,
1274 'Load_header_discount'
1275 );
1276 END IF;
1277
1278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1279
1280 END load_header_discount;
1281
1282
1283
1284 FUNCTION load_line_discount
1285 ( p_line_adj_rec IN oe_order_pub.line_adj_rec_type)
1286 RETURN OE_ORDER_PUB.Line_adj_REC_TYPE
1287 IS
1288 l_line_adj_rec OE_ORDER_PUB.Line_adj_rec_type;
1289 l_discount_id NUMBER := p_line_adj_rec.discount_id;
1290 l_discount_line_id NUMBER := p_line_adj_rec.discount_line_id;
1291 l_line_id NUMBER := p_line_adj_rec.line_id;
1292 l_adj_id NUMBER := p_line_adj_rec.price_adjustment_id;
1293 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1294 BEGIN
1295
1296 if l_debug_level > 0 then
1297 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_LINE_DISCOUNT', 1);
1298 end if;
1299
1300 IF (l_adj_id IS NOT NULL) THEN
1301
1302 IF (g_line_discount_rec.price_adjustment_id <> l_adj_id
1303 OR g_line_discount_rec.discount_line_id <> l_discount_line_id
1304 OR g_line_discount_rec.discount_id <> l_discount_id)
1305 THEN
1306
1307 -- oe_debug_pub.add('OEXUCCHB Before Load_line. Discount_id = ' ||
1308 -- To_char(l_discount_id) ||
1309 -- ' discount_line_id = ' ||
1310 -- To_char(l_discount_line_id) ||
1311 -- ' line_id = ' ||
1312 -- To_char(l_line_id) ||
1313 -- ' percent = ' ||
1314 -- To_char(l_line_adj_rec.percent), 2);
1315
1316 -- The discount is based on a discount_line
1317 IF l_discount_line_id <> -1
1318 THEN
1319
1320 SELECT (nvl(sodsc.amount / oeorln.UNIT_LIST_PRICE * 100,
1321 nvl(sodsc.percent,
1322 nvl((oeorln.UNIT_LIST_PRICE - sodls.price ) /
1323 oeorln.UNIT_LIST_PRICE * 100,
1324 nvl(sodls.amount / oeorln.UNIT_LIST_PRICE * 100,
1325 nvl(sodls.percent,
1326 nvl((oeorln.UNIT_LIST_PRICE - sopbl.price ) /
1327 oeorln.UNIT_LIST_PRICE * 100,
1328 nvl(sopbl.amount / oeorln.UNIT_LIST_PRICE * 100,
1329 nvl( sopbl.percent, 0 ))))))))),
1330 sodsc.discount_id,
1331 Nvl(sodls.discount_line_id, -1),
1332 sodsc.name,
1333 l_adj_id
1334 INTO g_line_discount_rec.percent,
1335 g_line_discount_rec.discount_id,
1336 g_line_discount_rec.discount_line_id,
1337 g_line_discount_rec.adjustment_name,
1338 g_line_discount_rec.price_adjustment_id
1339 FROM oe_discounts sodsc,
1340 oe_discount_lines sodls,
1341 oe_price_break_lines sopbl,
1342 oe_order_lines oeorln
1343 WHERE sodls.discount_line_id = l_discount_line_id
1344 AND sodls.discount_id = sodsc.discount_id
1345 AND sopbl.discount_line_id(+) = sodls.discount_line_id
1346 AND oeorln.line_id = l_line_id;
1347
1348 -- The discount is based on a discount
1349 ELSE
1350
1351 SELECT Nvl(nvl(sodsc.amount / oeorln.UNIT_LIST_PRICE * 100,
1352 sodsc.percent), 0),
1353 sodsc.discount_id,
1354 -1,
1355 sodsc.name,
1356 l_adj_id
1357 INTO g_line_discount_rec.percent,
1358 g_line_discount_rec.discount_id,
1359 g_line_discount_rec.discount_line_id,
1360 g_line_discount_rec.adjustment_name,
1361 g_line_discount_rec.price_adjustment_id
1362 FROM oe_discounts sodsc,
1363 oe_order_lines oeorln
1364 WHERE sodsc.discount_id = l_discount_id
1365 AND oeorln.line_id = l_line_id;
1366
1367 END IF;
1368
1369 END IF;
1370
1371 -- Write the values to the line adj_record
1372 l_line_adj_rec.discount_id := g_line_discount_rec.discount_id;
1373 l_line_adj_rec.discount_line_id := g_line_discount_rec.discount_line_id;
1374 l_line_adj_rec.percent := g_line_discount_rec.percent;
1375
1376 -- oe_debug_pub.add('OEXUCCHB After load_line. Discount_id = ' ||
1377 -- To_char(l_line_adj_rec.discount_id) ||
1378 -- ' discount_line_id = ' ||
1379 -- To_char(l_discount_line_id) ||
1380 -- ' line_id = ' ||
1381 -- To_char(l_line_id) ||
1382 -- ' percent = ' ||
1383 -- To_char(l_line_adj_rec.percent), 2);
1384
1385 END IF;
1386
1387 if l_debug_level > 0 then
1388 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_LINE_DISCOUNT', 1);
1389 end if;
1390 RETURN l_line_adj_rec;
1391
1392 EXCEPTION
1393
1394 WHEN OTHERS THEN
1395
1396 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1397 THEN
1398 OE_MSG_PUB.Add_Exc_Msg
1399 ( G_PKG_NAME ,
1400 'Load_Line_Discount'
1401 );
1402 END IF;
1403
1404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1405
1406 END load_line_discount;
1407
1408 ------------------------------------------------------------
1409 -- Bug 1929163: overload load_top_model_line so that it can be accessed
1410 -- both as a function and as a procedure
1411 ------------------------------------------------------------
1412 FUNCTION Load_Top_Model_Line
1413 ( p_key IN NUMBER )
1414 RETURN OE_ORDER_PUB.Line_Rec_Type
1415 IS
1416 BEGIN
1417
1418 Load_Top_Model_Line(p_key);
1419
1420 RETURN OE_ORDER_CACHE.g_top_model_line_rec;
1421
1422 END Load_Top_Model_Line;
1423
1424 PROCEDURE Load_Top_Model_Line
1425 ( p_key IN NUMBER )
1426 IS
1427 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1428 BEGIN
1429
1430 if l_debug_level > 0 then
1431 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_TOP_MODEL_LINE', 1);
1432 end if;
1433 IF p_key IS NOT NULL THEN
1434
1435 IF nvl(g_top_model_line_rec.line_id,0) <> p_key
1436 THEN
1437 if l_debug_level > 0 then
1438 oe_debug_pub.add('no cached model record', 3);
1439 end if;
1440 OE_Line_Util.Query_Row(p_line_id => p_key,
1441 x_line_rec => g_top_model_line_rec);
1442 ELSE
1443 if l_debug_level > 0 then
1444 oe_debug_pub.add('returning cached model record: '|| p_key, 3);
1445 end if;
1446 END IF;
1447
1448 if l_debug_level > 0 then
1449 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_TOP_MODEL_LINE', 3);
1450 end if;
1451
1452 END IF;
1453
1454 if l_debug_level > 0 then
1455 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_TOP_MODEL_LINE', 1);
1456 end if;
1457
1458 -- add bug 4200055
1459 EXCEPTION
1460
1461 WHEN NO_DATA_FOUND THEN
1462
1463 RAISE NO_DATA_FOUND;
1464
1465 WHEN OTHERS THEN
1466
1467 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1468 THEN
1469 OE_MSG_PUB.Add_Exc_Msg
1470 ( G_PKG_NAME
1471 , 'Load_Top_Model_Line'
1472 );
1473 END IF;
1474
1475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1476
1477 END Load_Top_Model_Line;
1478
1479 ------------------------------------------------------------
1480 -- Bug 1929163: overload load_item so that it can be accessed
1481 -- both as a function and as a procedure
1482 ------------------------------------------------------------
1483 FUNCTION Load_Item
1484 ( p_key1 IN NUMBER
1485 , p_key2 IN NUMBER := FND_API.G_MISS_NUM
1486 , p_key3 IN NUMBER DEFAULT NULL
1487 )
1488 RETURN Item_Rec_Type
1489 IS
1490 BEGIN
1491
1492 Load_Item(p_key1, p_key2,p_key3);
1493
1494 RETURN g_item_rec;
1495
1496 END Load_Item;
1497
1498 PROCEDURE Load_Item
1499 ( p_key1 IN NUMBER
1500 , p_key2 IN NUMBER := FND_API.G_MISS_NUM
1501 , p_key3 IN NUMBER DEFAULT NULL
1502 )
1503 IS
1504 l_key2 NUMBER;
1505 --INVCONV start --OPM 02/JUN/00 BEGIN
1506 --===================
1507 /* CURSOR c_opm_item ( discrete_org_id IN NUMBER
1508 , discrete_item_id IN NUMBER) IS
1509 SELECT item_id
1510 , item_um
1511 , item_um2
1512 , dualum_ind
1513 , grade_ctl
1514 FROM ic_item_mst
1515 WHERE delete_mark = 0
1516 AND item_no in (SELECT segment1
1517 FROM mtl_system_items
1518 WHERE organization_id = discrete_org_id
1519 AND inventory_item_id = discrete_item_id);
1520 --OPM 02/JUN/00 END
1521 --=================
1522 */
1523 --INVCONV end
1524
1525
1526 l_inventory_changed VARCHAR2(1) := 'N';
1527 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1528 BEGIN
1529
1530 if l_debug_level > 0 then
1531 oe_debug_pub.add('Entering OE_ORDER_CACHE.Load_Item'||p_key3, 1);
1532 end if;
1533
1534 -- Always store validation_org in l_key2.
1535 l_key2 := OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',p_key3);
1536
1537 if l_debug_level > 0 then
1538 OE_DEBUG_PUB.ADD('p_key1 is' || p_key1, 3);
1539 OE_DEBUG_PUB.ADD('prev item: ' || g_item_rec.inventory_item_id, 3);
1540 OE_DEBUG_PUB.ADD('l_key2 (Master organizion_id) is' || l_key2, 3);
1541 OE_DEBUG_PUB.ADD('p_key2 (ship_from_org_id) is' || p_key2, 3);
1542 end if;
1543
1544 IF p_key1 IS NOT NULL THEN
1545
1546 IF g_item_rec.inventory_item_id = FND_API.G_MISS_NUM OR
1547 g_item_rec.inventory_item_id <> p_key1 THEN
1548
1549 if l_debug_level > 0 then
1550 OE_DEBUG_PUB.ADD('querying item from mtl_system_items', 3);
1551 end if;
1552
1553 /* Always load values based on the validation org
1554 for the below attributes. In future please add here for the columns
1555 which need to get loaded based on validation org */
1556 /* Shippable_item_flag will be loaded into cache here and
1557 later the same will be reloaded based on the ship_from_org_id.
1558 This is because shippable_flag_item need to be loaded based on
1559 the validation_org if ship_from_org is null*/
1560
1561 SELECT INVENTORY_ITEM_ID
1562 ,ORGANIZATION_ID
1563 ,INVOICING_RULE_ID
1564 ,ACCOUNTING_RULE_ID
1565 ,DEFAULT_SHIPPING_ORG
1566 ,SHIP_MODEL_COMPLETE_FLAG
1567 ,BUILD_IN_WIP_FLAG
1568 ,BOM_ITEM_TYPE
1569 ,REPLENISH_TO_ORDER_FLAG
1570 ,PRIMARY_UOM_CODE
1571 ,PICK_COMPONENTS_FLAG
1572 ,SHIPPABLE_ITEM_FLAG
1573 ,SERVICE_ITEM_FLAG
1574 -- Pack J catchweight
1575 ,ONT_PRICING_QTY_SOURCE -- INVCONV
1576 ,TRACKING_QUANTITY_IND
1577 ,SECONDARY_UOM_CODE
1578 -- bug 4171642 FP
1579 ,ORGANIZATION_ID
1580 ,CUSTOMER_ORDER_ENABLED_FLAG
1581 ,INTERNAL_ORDER_ENABLED_FLAG
1582 ,RETURNABLE_FLAG
1583 ,RESTRICT_SUBINVENTORIES_CODE
1584 -- bug 4171642
1585 -- INVCONV start
1586 ,SECONDARY_DEFAULT_IND
1587 ,LOT_DIVISIBLE_FLAG
1588 ,GRADE_CONTROL_FLAG,
1589 LOT_CONTROL_CODE
1590 INTO g_item_rec.inventory_item_id
1591 ,g_item_rec.organization_id
1592 ,g_item_rec.invoicing_rule_id
1593 ,g_item_rec.accounting_rule_id
1594 ,g_item_rec.default_shipping_org
1595 ,g_item_rec.ship_model_complete_flag
1596 ,g_item_rec.build_in_wip_flag
1597 ,g_item_rec.bom_item_type
1598 ,g_item_rec.replenish_to_order_flag
1599 ,g_item_rec.primary_uom_code
1600 ,g_item_rec.pick_components_flag
1601 ,g_item_rec.shippable_item_flag
1602 ,g_item_rec.service_item_flag
1603 -- Pack J catchweight
1604 ,g_item_rec.ont_pricing_qty_source
1605 ,g_item_rec.tracking_quantity_ind
1606 ,g_item_rec.secondary_uom_code
1607 -- 4171642 FP
1608 ,g_item_rec.master_org_id
1609 ,g_item_rec.customer_order_enabled_flag
1610 ,g_item_rec.internal_order_enabled_flag
1611 ,g_item_rec.returnable_flag
1612 ,g_item_rec.restrict_subinventories_code
1613 -- 4171642
1614 -- INVCONV start
1615 ,g_item_rec.secondary_default_ind
1616 ,g_item_rec.lot_divisible_flag
1617 ,g_item_rec.grade_control_flag
1618 ,g_item_rec.lot_control_code
1619 FROM MTL_SYSTEM_ITEMS
1620 WHERE INVENTORY_ITEM_ID = p_key1
1621 AND ORGANIZATION_ID = l_key2;
1622
1623 -- Since inventory is change, load shippable_item_flag.
1624 l_inventory_changed := 'Y';
1625 -- INVCONV start remove opm
1626 -- OPM 02/JUN/00 - OPM item master characteristics
1627 /*
1628 IF NVL(FND_PROFILE.VALUE('ONT_PROCESS_INSTALLED_FLAG'),'Y')
1629 <> 'N' THEN
1630 IF INV_GMI_RSV_BRANCH.G_PROCESS_INV_INSTALLED = 'I' THEN
1631 OPEN c_opm_item( l_key2
1632 , p_key1);
1633 FETCH c_opm_item
1634 INTO g_item_rec.opm_item_id
1635 ,g_item_rec.opm_item_um
1636 ,g_item_rec.opm_item_um2
1637 ,g_item_rec.dualum_ind
1638 ,g_item_rec.grade_ctl;
1639
1640 IF c_opm_item%NOTFOUND THEN
1641 -- OPM 30/JUN/00 Fully clear the process cache
1642 g_item_rec.opm_item_id := NULL;
1643 g_item_rec.opm_item_um := NULL;
1644 g_item_rec.opm_item_um2 := NULL;
1645 g_item_rec.dualum_ind := NULL;
1646 g_item_rec.grade_ctl := NULL;
1647 END IF;
1648
1649 -- Moved this code from here inside the if INV_GMI_RSV_BRANCH.Is_Org_Process_Org(p_key2) THEN, because it was overriding the
1650 -- value of ont_pricing_qty_source for catchweight item
1651 -- g_item_rec.ont_pricing_qty_source := GML_READ_IC_B.read_price_qty_source(p_key1, l_key2); -- 2044240
1652 -- OE_DEBUG_PUB.ADD('OPM ont_pricing_qty_source after read ic_item_mst_b = ' || g_item_rec.ont_pricing_qty_source, 5);
1653
1654
1655 END IF; -- end of IF NVL(FND_PROFILE.VALUE('ONT_PROCESS_INSTALLED_FLAG'),'Y')
1656 END IF; -- IF INV_GMI_RSV_BRANCH.G_PROCESS_INV_INSTALLED = 'I
1657
1658
1659 */
1660 -- INVCONV end
1661
1662
1663 /* OPM 02/JUN/00 END */
1664
1665 END IF;
1666
1667 /* When p_key2 is not null ie. ship_from_org_id is not null then
1668 load the shippable_item_flag based on the ship_from_org. In future
1669 please add the attributes here that needs to be loaded based on the
1670 ship_from_org_id */
1671
1672 IF (p_key2 IS NOT NULL) AND
1673 (p_key2 <> FND_API.G_MISS_NUM) --AND
1674 -- (p_key2 <> l_key2 ) --added for bug 4171642 removed for bug 6666457
1675 THEN
1676
1677 IF (g_item_rec.organization_id <> p_key2)
1678 OR (l_inventory_changed = 'Y' ) THEN
1679
1680 l_inventory_changed := 'N';
1681
1682 if l_debug_level > 0 then
1683 OE_DEBUG_PUB.ADD('querying based on ship_from_org', 3);
1684 end if;
1685 SELECT shippable_item_flag
1686 ,organization_id
1687 , restrict_subinventories_code -- bug 4171642
1688 ,ONT_PRICING_QTY_SOURCE -- INVCONV
1689 ,TRACKING_QUANTITY_IND
1690 ,SECONDARY_UOM_CODE
1691 ,SECONDARY_DEFAULT_IND
1692 ,LOT_DIVISIBLE_FLAG
1693 ,GRADE_CONTROL_FLAG
1694 ,LOT_CONTROL_CODE
1695 , returnable_flag --5608844
1696 ,PRIMARY_UOM_CODE -- 5608585
1697
1698 INTO g_item_rec.shippable_item_flag
1699 ,g_item_rec.organization_id
1700 ,g_item_rec.restrict_subinventories_code
1701 ,g_item_rec.ont_pricing_qty_source
1702 ,g_item_rec.tracking_quantity_ind
1703 ,g_item_rec.secondary_uom_code
1704 -- INVCONV start
1705 ,g_item_rec.secondary_default_ind
1706 ,g_item_rec.lot_divisible_flag
1707 ,g_item_rec.grade_control_flag
1708 ,g_item_rec.lot_control_code
1709 ,g_item_rec.returnable_flag --5608844
1710 ,g_item_rec.primary_uom_code -- 5608585
1711
1712
1713 FROM MTL_SYSTEM_ITEMS
1714 WHERE INVENTORY_ITEM_ID = p_key1
1715 AND ORGANIZATION_ID = p_key2;
1716 -- Pack J catchweight
1717 --Find out whether the inventory org is WMS enabled from mtl_parameters
1718 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' THEN
1719 SELECT wms_enabled_flag
1720 INTO g_item_rec.wms_enabled_flag
1721 FROM mtl_parameters
1722 WHERE organization_id = p_key2;
1723 END IF;
1724
1725 END IF;
1726 END IF;
1727 END IF;
1728
1729 /* OPM - check for process warehouse */
1730 IF INV_GMI_RSV_BRANCH.Is_Org_Process_Org(p_key2) THEN
1731 g_item_rec.process_warehouse_flag := 'Y';
1732 --g_item_rec.ont_pricing_qty_source := GML_READ_IC_B.read_price_qty_source(p_key1, l_key2); -- INVCONV 2044240
1733 --if l_debug_level > 0 then
1734 --OE_DEBUG_PUB.ADD('OPM ont_pricing_qty_source after read ic_item_mst_b = ' || g_item_rec.ont_pricing_qty_source, 5);
1735 --end if;
1736 ELSE
1737 g_item_rec.process_warehouse_flag := NULL;
1738 END IF;
1739
1740 if l_debug_level > 0 then
1741 oe_debug_pub.add('in OE_ORDER_CACHE.LOAD_ITEM process warehouse flag is ' || g_item_rec.process_warehouse_flag );
1742 /* OPM END */
1743
1744 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_ITEM', 1);
1745 end if;
1746 EXCEPTION
1747 -- this is temporary workaround
1748
1749 WHEN NO_DATA_FOUND THEN
1750
1751 SELECT INVENTORY_ITEM_ID
1752 , ORGANIZATION_ID
1753 , INVOICING_RULE_ID
1754 , ACCOUNTING_RULE_ID
1755 , DEFAULT_SHIPPING_ORG
1756 , SHIP_MODEL_COMPLETE_FLAG
1757 , BUILD_IN_WIP_FLAG
1758 , BOM_ITEM_TYPE
1759 , REPLENISH_TO_ORDER_FLAG
1760 , PRIMARY_UOM_CODE
1761 , PICK_COMPONENTS_FLAG
1762 , SHIPPABLE_ITEM_FLAG
1763 , SERVICE_ITEM_FLAG
1764 -- added for bug 4171642
1765 ,ORGANIZATION_ID
1766 ,CUSTOMER_ORDER_ENABLED_FLAG
1767 ,INTERNAL_ORDER_ENABLED_FLAG
1768 ,RETURNABLE_FLAG
1769 ,RESTRICT_SUBINVENTORIES_CODE
1770
1771 INTO g_item_rec.inventory_item_id
1772 , g_item_rec.organization_id
1773 , g_item_rec.invoicing_rule_id
1774 , g_item_rec.accounting_rule_id
1775 , g_item_rec.default_shipping_org
1776 , g_item_rec.ship_model_complete_flag
1777 , g_item_rec.build_in_wip_flag
1778 , g_item_rec.bom_item_type
1779 , g_item_rec.replenish_to_order_flag
1780 , g_item_rec.primary_uom_code
1781 , g_item_rec.pick_components_flag
1782 , g_item_rec.shippable_item_flag
1783 , g_item_rec.service_item_flag
1784 -- bug 4171642
1785 ,g_item_rec.master_org_id
1786 ,g_item_rec.customer_order_enabled_flag
1787 ,g_item_rec.internal_order_enabled_flag
1788 ,g_item_rec.returnable_flag
1789 ,g_item_rec.restrict_subinventories_code
1790
1791 FROM MTL_SYSTEM_ITEMS
1792 WHERE INVENTORY_ITEM_ID = p_key1
1793 AND ORGANIZATION_ID = OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',p_key3);
1794
1795 if l_debug_level > 0 then
1796 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_ITEM - item doesnt exist in ship_from', 2);
1797 end if;
1798 WHEN OTHERS THEN
1799
1800 if l_debug_level > 0 then
1801 oe_debug_pub.add('exception in load item', 1);
1802 end if;
1803 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1804 THEN
1805 OE_MSG_PUB.Add_Exc_Msg
1806 ( G_PKG_NAME ,
1807 'Load_Item'
1808 );
1809 END IF;
1810
1811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1812
1813 END Load_Item;
1814
1815 FUNCTION Load_Set
1816 ( p_set_id IN NUMBER)
1817 RETURN set_rec_type
1818 IS
1819 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1820 BEGIN
1821
1822 if l_debug_level > 0 then
1823 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_SET', 1);
1824 end if;
1825
1826 IF (p_set_id IS NOT NULL)
1827 THEN
1828 --IF (g_set_rec.set_id <> p_set_id)
1829 -- THEN
1830 BEGIN
1831 SELECT set_id
1832 ,set_name
1833 ,set_type
1834 ,header_Id
1835 ,ship_from_org_id
1836 ,ship_to_org_id
1837 ,schedule_ship_date
1838 ,schedule_arrival_date
1839 ,shipment_priority_code
1840 ,freight_carrier_code
1841 ,shipping_method_code
1842 ,set_status
1843
1844 INTO g_set_rec.set_id
1845 ,g_set_rec.set_name
1846 ,g_set_rec.set_type
1847 ,g_set_rec.Header_Id
1848 ,g_set_rec.Ship_from_org_id
1849 ,g_set_rec.Ship_to_org_id
1850 ,g_set_rec.Schedule_Ship_Date
1851 ,g_set_rec.Schedule_Arrival_Date
1852 ,g_set_rec.Shipment_priority_code
1853 ,g_set_rec.Freight_Carrier_Code
1854 ,g_set_Rec.Shipping_Method_Code
1855 ,g_set_rec.Set_Status
1856
1857 FROM oe_sets
1858 WHERE oe_sets.set_id= p_set_id;
1859
1860 EXCEPTION
1861
1862 WHEN NO_DATA_FOUND THEN
1863 g_set_rec.set_id := NULL;
1864 g_set_rec.set_name := NULL;
1865 g_set_rec.set_type := NULL;
1866 g_set_rec.Header_Id := NULL;
1867 g_set_rec.Ship_from_org_id := NULL;
1868 g_set_rec.Ship_to_org_id := NULL;
1869 g_set_rec.shipment_priority_code := NULL;
1870 g_set_rec.Schedule_Ship_Date:= NULL;
1871 g_set_rec.Schedule_Arrival_Date := NULL;
1872 g_set_rec.Freight_Carrier_Code := NULL;
1873 g_set_Rec.Shipping_Method_Code := NULL;
1874 g_set_rec.Set_Status := NULL;
1875
1876 WHEN OTHERS THEN
1877
1878 IF OE_MSG_PUB.Check_Msg_Level
1879 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1880 THEN
1881 OE_MSG_PUB.Add_Exc_Msg
1882 ( G_PKG_NAME
1883 , 'Load_set '
1884 );
1885 END IF;
1886
1887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1888
1889 END;
1890 --END IF;
1891
1892 if l_debug_level > 0 then
1893 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_SET', 1);
1894 end if;
1895
1896 RETURN g_set_rec;
1897 END IF;
1898
1899 if l_debug_level > 0 then
1900 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_SET', 1);
1901 end if;
1902
1903 RETURN g_set_rec;
1904
1905 EXCEPTION
1906
1907 WHEN OTHERS THEN
1908
1909 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1910 THEN
1911 OE_MSG_PUB.Add_Exc_Msg
1912 ( G_PKG_NAME ,
1913 'Load_Delivery_Set'
1914 );
1915 END IF;
1916
1917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1918
1919 END;
1920
1921 -- added for bug 4200055
1922
1923 FUNCTION Load_Payment_Term
1924 ( p_key IN NUMBER )
1925 RETURN Payment_Term_Rec_Type
1926 IS
1927 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1928 BEGIN
1929
1930 if l_debug_level > 0 then
1931 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_Payment_Term', 1);
1932 end if;
1933
1934 IF p_key IS NOT NULL THEN
1935 IF g_Payment_Term_rec.term_id = FND_API.G_MISS_NUM OR
1936 g_Payment_Term_rec.term_id <> p_key THEN
1937 oe_debug_pub.add('querying oe_ra_terms_v');
1938 SELECT term_id ,
1939 name,
1940 start_date_active,
1941 end_date_active
1942 INTO
1943 g_Payment_Term_rec.term_id,
1944 g_Payment_Term_rec.name,
1945 g_Payment_Term_rec.start_date_active,
1946 g_Payment_Term_rec.end_date_active
1947 FROM OE_RA_TERMS_V
1948 WHERE term_id = p_key ;
1949
1950 END IF ;
1951 END IF ;
1952
1953 if l_debug_level > 0 then
1954 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_Payment_Term', 1);
1955 end if;
1956 RETURN g_Payment_term_rec;
1957 EXCEPTION
1958 WHEN NO_DATA_FOUND THEN
1959 oe_debug_pub.add('No Data Found in OE_Order_Cache.Load_Payment_Term');
1960 RAISE NO_DATA_FOUND ;
1961
1962 -- Returning g_payment_rec could potentially pass the wrong result and
1963 -- if processed could result to data corruption
1964
1965 --return g_payment_term_rec ;
1966 WHEN OTHERS THEN
1967
1968 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1969 THEN
1970 OE_MSG_PUB.Add_Exc_Msg
1971 ( G_PKG_NAME ,
1972 'Load_Payment_Term'
1973 );
1974 END IF;
1975
1976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1977
1978 END Load_Payment_Term;
1979
1980 FUNCTION Load_Salesrep_rec
1981 ( p_key IN NUMBER )
1982 RETURN Salesrep_Rec_Type
1983 IS
1984 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1985 BEGIN
1986
1987 if l_debug_level > 0 then
1988 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_Salesrep_rec', 1);
1989 end if;
1990
1991 IF p_key IS NOT NULL THEN
1992 IF g_Salesrep_rec.salesrep_id = FND_API.G_MISS_NUM OR
1993 g_Salesrep_rec.salesrep_id <> p_key THEN
1994 oe_debug_pub.add('Load Salesrep cache');
1995 SELECT salesrep_id ,
1996 name,
1997 status,
1998 start_date_active,
1999 end_date_active
2000 INTO
2001 g_Salesrep_rec.salesrep_id,
2002 g_Salesrep_rec.name,
2003 g_Salesrep_rec.status,
2004 g_Salesrep_rec.start_date_active,
2005 g_Salesrep_rec.end_date_active
2006 FROM RA_SALESREPS
2007 WHERE salesrep_id = p_key ;
2008
2009 END IF ;
2010 END IF ;
2011
2012 if l_debug_level > 0 then
2013 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_Salesrep', 1);
2014 end if;
2015 RETURN g_Salesrep_rec;
2016 EXCEPTION
2017 WHEN NO_DATA_FOUND THEN
2018 oe_debug_pub.add('No Data Found in OE_Order_Cache.Load_Salesrep_rec');
2019 RAISE NO_DATA_FOUND ;
2020 -- Returing g_salesrep_rec could potentially pass the old information and if processed can result into corruption issue.
2021
2022 -- return g_salesrep_rec ;
2023 WHEN OTHERS THEN
2024
2025 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2026 THEN
2027 OE_MSG_PUB.Add_Exc_Msg
2028 ( G_PKG_NAME ,
2029 'Load_Salesrep_rec'
2030 );
2031 END IF;
2032
2033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2034
2035 END Load_Salesrep_rec;
2036
2037 --end bug 4200055
2038
2039 -- procedures that set the cached records.
2040
2041 PROCEDURE Set_Order_Header
2042 (
2043 p_header_rec IN OE_ORDER_PUB.Header_Rec_Type
2044 ) IS
2045 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2046 BEGIN
2047
2048 if l_debug_level > 0 then
2049 oe_debug_pub.add('Entering OE_ORDER_CACHE.Set_Order_Header', 1);
2050 end if;
2051
2052 IF (p_header_rec.header_id IS NOT NULL) THEN
2053
2054 IF (p_header_rec.header_id = nvl(g_header_rec.header_id,0)) THEN
2055
2056 g_header_rec := p_header_rec;
2057
2058 END IF;
2059
2060 END IF;
2061
2062 if l_debug_level > 0 then
2063 oe_debug_pub.add('Exiting OE_ORDER_CACHE.Set_Order_Header', 1);
2064 end if;
2065
2066 END Set_Order_Header;
2067
2068
2069 -- procedures that clear cached entities.
2070
2071 PROCEDURE Clear_Top_Model_Line(p_key IN NUMBER)
2072 IS
2073 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2074 BEGIN
2075
2076 if l_debug_level > 0 then
2077 oe_debug_pub.add('Entering OE_ORDER_CACHE.Top_Model_Line', 1);
2078 end if;
2079
2080 IF nvl(g_top_model_line_rec.line_id,0) = p_key THEN
2081 if l_debug_level > 0 then
2082 oe_debug_pub.add('in ucchb, clearing top model cache: '|| p_key, 3);
2083 end if;
2084 g_top_model_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
2085 END IF;
2086
2087 if l_debug_level > 0 then
2088 oe_debug_pub.add('Exiting OE_ORDER_CACHE.Top_Model_Line', 1);
2089 end if;
2090
2091 END Clear_Top_Model_Line;
2092
2093
2094 PROCEDURE Clear_Order_Type
2095 IS
2096 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2097 BEGIN
2098
2099 if l_debug_level > 0 then
2100 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_ORDER_TYPE', 1);
2101 end if;
2102 g_order_type_rec := G_MISS_ORDER_TYPE_REC;
2103
2104 if l_debug_level > 0 then
2105 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_ORDER_TYPE', 1);
2106 end if;
2107 END Clear_Order_Type;
2108
2109 PROCEDURE Clear_Agreement
2110 IS
2111 BEGIN
2112
2113 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_AGREEMENT', 1);
2114
2115 g_agreement_rec := G_MISS_AGREEMENT_REC;
2116
2117 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_AGREEMENT', 1);
2118
2119 END Clear_Agreement;
2120
2121 PROCEDURE Clear_Ship_To_Org
2122 IS
2123 BEGIN
2124
2125 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_SHIP_TO_ORG', 1);
2126
2127 g_ship_to_rec := G_MISS_SHIP_TO_REC;
2128
2129 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_SHIP_TO_ORG', 1);
2130
2131 END Clear_Ship_To_Org;
2132
2133 PROCEDURE Clear_Invoice_To_Org
2134 IS
2135 BEGIN
2136
2137 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_INVOICE_TO_ORG', 1);
2138
2139 g_invoice_to_rec := G_MISS_INVOICE_TO_REC;
2140
2141 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_INVOICE_TO_ORG', 1);
2142
2143 END Clear_Invoice_To_Org;
2144
2145 PROCEDURE Clear_Deliver_To_Org
2146 IS
2147 BEGIN
2148
2149 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_DELIVER_TO_ORG', 1);
2150
2151 g_deliver_to_rec := G_MISS_DELIVER_TO_REC;
2152
2153 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_DELIVER_TO_ORG', 1);
2154
2155 END Clear_Deliver_To_Org;
2156
2157 PROCEDURE Clear_Sold_To_Org
2158 IS
2159 BEGIN
2160
2161 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_SOLD_TO_ORG', 1);
2162
2163 g_sold_to_rec := G_MISS_SOLD_TO_REC;
2164
2165 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_SOLD_TO_ORG', 1);
2166
2167 END Clear_Sold_To_Org;
2168
2169 PROCEDURE Clear_Price_List
2170 IS
2171 BEGIN
2172
2173 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_PRICE_LIST', 1);
2174
2175 g_price_list_rec := G_MISS_PRICE_LIST_REC;
2176
2177 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_PRICE_LIST', 1);
2178
2179 END Clear_Price_List;
2180
2181 PROCEDURE Clear_Set_Of_Books
2182 IS
2183 BEGIN
2184
2185 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_SET_OF_BOOKS', 1);
2186
2187 g_set_of_books_rec := G_MISS_SET_OF_BOOKS_REC;
2188
2189 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_SET_OF_BOOKS', 1);
2190
2191 END Clear_Set_Of_Books;
2192
2193 PROCEDURE Clear_item
2194 IS
2195 BEGIN
2196
2197 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_ITEM', 1);
2198
2199 g_item_rec := G_MISS_ITEM_REC;
2200
2201 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_ITEM', 1);
2202
2203 END Clear_item;
2204
2205 PROCEDURE Clear_item_Cost
2206 IS
2207 BEGIN
2208
2209 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_ITEM_COST', 1);
2210
2211 g_item_cost_rec := G_MISS_ITEM_COST_REC;
2212
2213 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_ITEM_COST', 1);
2214
2215 END Clear_item_Cost;
2216
2217 PROCEDURE Clear_Order_Header
2218 IS
2219 BEGIN
2220
2221 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_ORDER_HEADER', 1);
2222
2223 g_header_rec := OE_Order_PUB.G_MISS_HEADER_REC;
2224
2225 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_ORDER_HEADER', 1);
2226
2227 END Clear_Order_Header;
2228
2229
2230 PROCEDURE Clear_Discount
2231 IS
2232 BEGIN
2233
2234 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_DISCOUNT', 1);
2235
2236 g_hdr_discount_rec := oe_order_cache.g_miss_discount_rec;
2237 g_line_discount_rec := oe_order_cache.g_miss_discount_rec;
2238
2239 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_DISCOUNT', 1);
2240
2241 END clear_discount;
2242
2243 --added for bug 4200055
2244 PROCEDURE Clear_Salesrep
2245 IS
2246 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2247
2248 BEGIN
2249 IF l_debug_level > 0 THEN
2250 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_SALESREP', 1);
2251 END IF;
2252
2253 g_salesrep_rec := oe_order_cache.g_miss_salesrep_rec;
2254
2255 IF l_debug_level > 0 THEN
2256 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_SALESREP', 1);
2257 END IF;
2258
2259 END clear_salesrep;
2260
2261
2262 PROCEDURE Clear_Payment_Term
2263 IS
2264 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2265
2266 BEGIN
2267 IF l_debug_level > 0 THEN
2268 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_payment_term', 1);
2269 END IF;
2270
2271 g_payment_term_rec := oe_order_cache.g_miss_payment_term_rec;
2272
2273 IF l_debug_level > 0 THEN
2274 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_payment_term', 1);
2275 END IF;
2276
2277 END clear_payment_term;
2278 -- end bug 4200055
2279
2280 PROCEDURE Clear_All
2281 IS
2282 BEGIN
2283
2284 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_ALL', 1);
2285
2286 Clear_Order_Type;
2287 Clear_Agreement;
2288 Clear_Ship_To_Org;
2289 Clear_Invoice_To_Org;
2290 Clear_Deliver_To_Org;
2291 Clear_Sold_To_Org;
2292 Clear_Price_List;
2293 Clear_Set_Of_Books;
2294 Clear_Item;
2295 Clear_Item_Cost;
2296 Clear_Order_Header;
2297 Clear_Discount;
2298 --added for bug 4200055
2299 Clear_Payment_Term ;
2300 Clear_Salesrep ;
2301 --end
2302
2303 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_ALL', 1);
2304
2305 END Clear_All;
2306
2307 FUNCTION Get_Set_Of_Books
2308 Return Number
2309 IS
2310 l_set_of_books_id Number;
2311 BEGIN
2312 l_set_of_books_id := OE_Sys_Parameters.VALUE('SET_OF_BOOKS_ID');
2313
2314 RETURN l_set_of_books_id;
2315 END Get_Set_Of_Books;
2316
2317
2318 FUNCTION Load_List_Lines
2319 ( p_key IN NUMBER )
2320 RETURN Modifiers_Rec_Type
2321 IS
2322 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2323 BEGIN
2324
2325 if l_debug_level > 0 then
2326 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_list_lines', 1);
2327 end if;
2328
2329 IF p_key IS NOT NULL THEN
2330
2331 IF g_Modifiers_Rec.list_line_id = FND_API.G_MISS_NUM OR
2332 g_Modifiers_Rec.list_line_id <> p_key THEN
2333
2334 SELECT arithmetic_operator
2335 ,automatic_flag
2336 ,base_qty
2337 ,base_uom_code
2338 ,inventory_item_id
2339 ,list_header_id
2340 ,list_line_id
2341 ,list_line_type_code
2342 ,modifier_level_code
2343 ,operand
2344 ,organization_id
2345 ,override_flag
2346 ,percent_price
2347 ,price_break_type_code
2348 ,price_by_formula_id
2349 ,primary_uom_flag
2350 ,print_on_invoice_flag
2351 ,rebate_transaction_type_code
2352 ,related_item_id
2353 ,relationship_type_id
2354 ,substitution_attribute
2355 ,substitution_context
2356 ,substitution_value
2357 ,accrual_flag
2358 ,pricing_group_sequence
2359 ,incompatibility_grp_code
2360 ,list_line_no
2361 ,pricing_phase_id
2362 ,product_precedence
2363 ,expiration_date
2364 ,charge_type_code
2365 ,charge_subtype_code
2366 ,benefit_qty
2367 ,benefit_uom_code
2368 ,accrual_conversion_rate
2369 ,proration_type_code
2370 ,include_on_returns_flag
2371 ,print_on_invoice_flag
2372 ,accrual_flag
2373 INTO
2374 g_Modifiers_Rec.arithmetic_operator
2375 ,g_Modifiers_Rec.automatic_flag
2376 ,g_Modifiers_Rec.base_qty
2377 ,g_Modifiers_Rec.base_uom_code
2378 ,g_Modifiers_Rec.inventory_item_id
2379 ,g_Modifiers_Rec.list_header_id
2380 ,g_Modifiers_Rec.list_line_id
2381 ,g_Modifiers_Rec.list_line_type_code
2382 ,g_Modifiers_Rec.modifier_level_code
2383 ,g_Modifiers_Rec.operand
2384 ,g_Modifiers_Rec.organization_id
2385 ,g_Modifiers_Rec.override_flag
2386 ,g_Modifiers_Rec.percent_price
2387 ,g_Modifiers_Rec.price_break_type_code
2388 ,g_Modifiers_Rec.price_by_formula_id
2389 ,g_Modifiers_Rec.primary_uom_flag
2390 ,g_Modifiers_Rec.print_on_invoice_flag
2391 ,g_Modifiers_Rec.rebate_transaction_type_code
2392 ,g_Modifiers_Rec.related_item_id
2393 ,g_Modifiers_Rec.relationship_type_id
2394 ,g_Modifiers_Rec.substitution_attribute
2395 ,g_Modifiers_Rec.substitution_context
2396 ,g_Modifiers_Rec.substitution_value
2397 ,g_Modifiers_Rec.accrual_flag
2398 ,g_Modifiers_Rec.pricing_group_sequence
2399 ,g_Modifiers_Rec.incompatibility_grp_code
2400 ,g_Modifiers_Rec.list_line_no
2401 ,g_Modifiers_Rec.pricing_phase_id
2402 ,g_Modifiers_Rec.product_precedence
2403 ,g_Modifiers_Rec.expiration_date
2404 ,g_Modifiers_Rec.charge_type_code
2405 ,g_Modifiers_Rec.charge_subtype_code
2406 ,g_Modifiers_Rec.benefit_qty
2407 ,g_Modifiers_Rec.benefit_uom_code
2408 ,g_Modifiers_Rec.accrual_conversion_rate
2409 ,g_Modifiers_Rec.proration_type_code
2410 ,g_Modifiers_Rec.include_on_returns_flag
2411 ,g_Modifiers_Rec.print_on_invoice_flag
2412 ,g_Modifiers_Rec.accrual_flag
2413 FROM qp_list_lines
2414 WHERE list_line_id= p_key;
2415
2416 END IF;
2417
2418 END IF;
2419
2420 if l_debug_level > 0 then
2421 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_list_lines', 1);
2422 end if;
2423
2424 RETURN g_Modifiers_Rec;
2425
2426 EXCEPTION
2427
2428 WHEN OTHERS THEN
2429
2430 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2431 THEN
2432 OE_MSG_PUB.Add_Exc_Msg
2433 ( G_PKG_NAME ,
2434 'Load_list_lines'||sqlerrm
2435 );
2436 END IF;
2437
2438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2439
2440 END Load_List_Lines;
2441
2442 Function Load_Cust_Trx_Type
2443 ( p_key IN NUMBER )
2444 RETURN Cust_Trx_Rec_Type
2445 IS
2446 BEGIN
2447
2448 Load_Cust_Trx_Type(p_key);
2449
2450 RETURN g_cust_trx_rec;
2451
2452 END Load_Cust_Trx_Type;
2453
2454 Procedure Load_Cust_Trx_Type
2455 ( p_key IN NUMBER )
2456 IS
2457 l_calculate_tax_flag varchar2(1) := NULL;
2458 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2459 l_current_org_id NUMBER ; -- MOAC Changes
2460 BEGIN
2461
2462 if l_debug_level > 0 then
2463 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_CUST_TRX_TYPE', 1);
2464 end if;
2465
2466 l_current_org_id := MO_Global.Get_Current_Org_Id() ; --MOAC changes
2467
2468 -- New condition added for bug 2281054
2469 If p_key = 0 then
2470 if l_debug_level > 0 then
2471 oe_debug_pub.add('No Receivable Transaction Type assigned at any of the levels');
2472 end if;
2473 -- bug 2604421, need to initialize this value, otherwise the
2474 -- tax_calculation_flag cached from previous order will remain.
2475 g_cust_trx_rec.tax_calculation_flag := null;
2476 g_cust_trx_rec.cust_trx_type_id := null;
2477 g_cust_trx_rec.org_id := null ; -- MOAC changes
2478
2479 goto THE_END;
2480 end if;
2481
2482 IF p_key IS NOT NULL THEN
2483 /* Modified the If condition for Bug-2113379 */
2484 IF Nvl(g_cust_trx_rec.cust_trx_type_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR
2485 g_cust_trx_rec.cust_trx_type_id <> p_key OR
2486 g_cust_trx_rec.org_id <> l_current_org_id THEN --MOAC changes
2487
2488 SELECT tax_calculation_flag
2489 ,cust_trx_type_id
2490 ,org_id
2491 INTO g_cust_trx_rec.tax_calculation_flag
2492 ,g_cust_trx_rec.cust_trx_type_id
2493 ,g_cust_trx_rec.org_id -- MOAC Changes
2494 FROM RA_CUST_TRX_TYPES_ALL
2495 WHERE CUST_TRX_TYPE_ID = p_key
2496 AND ORG_ID = l_current_org_id ;
2497
2498 END IF;
2499
2500 END IF;
2501 <<THE_END>>
2502
2503 if l_debug_level > 0 then
2504 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_CUST_TRX_TYPE', 1);
2505 end if;
2506
2507 EXCEPTION
2508
2509 WHEN OTHERS THEN
2510
2511 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2512 THEN
2513 OE_MSG_PUB.Add_Exc_Msg
2514 ( G_PKG_NAME ,
2515 'Load_Cust_Trx_Type'
2516 );
2517 END IF;
2518
2519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2520 END Load_Cust_Trx_Type;
2521
2522 FUNCTION get_tax_calculation_flag
2523 ( p_key IN NUMBER,
2524 p_line_rec IN OE_ORDER_PUB.Line_Rec_Type )
2525 RETURN Tax_Calc_Rec_Type
2526 IS
2527 l_calculate_tax_flag varchar2(1) := NULL;
2528 l_tax_rec Tax_Calc_Rec_Type;
2529 l_cust_trx_type_id number;
2530 v_start number;
2531 v_end number;
2532 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2533 BEGIN
2534
2535 --v_start := DBMS_UTILITY.GET_TIME;
2536
2537 if l_debug_level > 0 then
2538 oe_debug_pub.add('Entering OE_ORDER_CACHE.GET_TAX_CALCULATION_FLAG', 1);
2539 end if;
2540
2541 IF p_key IS NOT NULL THEN
2542
2543 IF g_tax_calc_tbl.Exists(p_key) THEN
2544
2545 l_tax_rec.tax_calculation_flag := g_tax_calc_tbl(p_key).tax_calculation_flag;
2546
2547 l_tax_rec.cust_trx_type_id := g_tax_calc_tbl(p_key).cust_trx_type_id;
2548
2549 l_tax_rec.line_type_id := g_tax_calc_tbl(p_key).line_type_id;
2550
2551 if l_debug_level > 0 then
2552 oe_debug_pub.add('ren: flag: cust_trx_type_id: line_type_id: ' || l_tax_rec.tax_calculation_flag ||': ' || l_tax_rec.cust_trx_type_id || ': ' || l_tax_rec.line_type_id || ' .' , 4);
2553 end if;
2554
2555 ELSE
2556
2557
2558 Load_Line_Type(p_key);
2559
2560 l_calculate_tax_flag := g_line_type_rec.calculate_tax_flag;
2561 l_cust_trx_type_id := g_line_type_rec.cust_trx_type_id;
2562
2563 -- made code changes for bug 2604421.
2564 IF ( g_line_type_rec.cust_trx_type_id is null and
2565 g_line_type_rec.calculate_tax_flag is null )
2566 Then
2567
2568 l_cust_trx_type_id :=
2569 OE_INVOICE_PUB.Get_Customer_Transaction_Type(p_line_rec);
2570
2571 load_cust_trx_type(l_cust_trx_type_id);
2572
2573 l_calculate_tax_flag := g_cust_trx_rec.tax_calculation_flag;
2574
2575 l_tax_rec.line_type_id := p_key;
2576 l_tax_rec.tax_calculation_flag := l_calculate_tax_flag;
2577 l_tax_rec.cust_trx_type_id := l_cust_trx_type_id;
2578
2579 ELSE
2580
2581 l_tax_rec.line_type_id := p_key;
2582 l_tax_rec.tax_calculation_flag := l_calculate_tax_flag;
2583 l_tax_rec.cust_trx_type_id := l_cust_trx_type_id;
2584
2585 g_tax_calc_tbl(p_key) := l_tax_rec;
2586 if l_debug_level > 0 then
2587 oe_debug_pub.add('g_tax_calc_tbl flag: cust_trx_type_id: line_type_id: ' || g_tax_calc_tbl(p_key).tax_calculation_flag ||': ' || g_tax_calc_tbl(p_key).cust_trx_type_id || ': ' || g_tax_calc_tbl(p_key).line_type_id || ' .' , 4);
2588 end if;
2589
2590 END IF; /* if cust_trx_type_id is null and
2591 calculate_tax_flag is null */
2592
2593 END IF; /* if g_tax_calc_tbl.Exists(p_key) */
2594
2595 END IF; /* IF p_key is not null */
2596
2597 --v_end := DBMS_UTILITY.GET_TIME;
2598
2599 -- oe_debug_pub.add('ren: Time Of execution for get_tax_calculation_flag '||
2600 -- to_char((v_end-v_start)/100),1);
2601
2602 if l_debug_level > 0 then
2603 oe_debug_pub.add('Exiting OE_ORDER_CACHE.GET_TAX_CALCULATION_FLAG', 1);
2604 end if;
2605
2606 RETURN l_tax_rec;
2607
2608
2609
2610 EXCEPTION
2611
2612 WHEN OTHERS THEN
2613
2614 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2615 THEN
2616 OE_MSG_PUB.Add_Exc_Msg
2617 ( G_PKG_NAME ,
2618 'get_tax_calculation_flag'
2619 );
2620 END IF;
2621
2622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2623 END get_tax_calculation_flag;
2624
2625 FUNCTION IS_FLEX_ENABLED(p_flex_name IN VARCHAR2)
2626 RETURN VARCHAR2
2627 IS
2628 l_flex_name varchar2(240);
2629 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2630 BEGIN
2631 if l_debug_level > 0 then
2632 oe_debug_pub.add('Enter Is Flex Enabled' ,1);
2633 end if;
2634 IF p_flex_name = 'OE_HEADER_ATTRIBUTES' THEN
2635 IF g_hdr_desc_flex is null THEN
2636 g_hdr_desc_flex := Load_flex_enabled_flag(p_flex_name);
2637 END IF;
2638 RETURN g_hdr_desc_flex;
2639 ELSIF p_flex_name = 'OE_HEADER_GLOBAL_ATTRIBUTE' THEN
2640 IF g_hdr_glb_flex is null THEN
2641 g_hdr_glb_flex := Load_flex_enabled_flag(p_flex_name);
2642 END IF;
2643 RETURN g_hdr_glb_flex;
2644 ELSIF p_flex_name = 'OE_HEADER_TP_ATTRIBUTES' THEN
2645 IF g_hdr_tp_flex is null THEN
2646 g_hdr_tp_flex := Load_flex_enabled_flag(p_flex_name);
2647 END IF;
2648 RETURN g_hdr_tp_flex;
2649 ELSIF p_flex_name = 'OE_LINE_ATTRIBUTES' THEN
2650 IF g_line_desc_flex is null THEN
2651 g_line_desc_flex := Load_flex_enabled_flag(p_flex_name);
2652 END IF;
2653 RETURN g_line_desc_flex;
2654 ELSIF p_flex_name = 'OE_LINE_GLOBAL_ATTRIBUTE' THEN
2655 IF g_line_glb_flex is null THEN
2656 g_line_glb_flex := Load_flex_enabled_flag(p_flex_name);
2657 END IF;
2658 RETURN g_line_glb_flex;
2659 ELSIF p_flex_name = 'OE_LINE_PRICING_ATTRIBUTE' THEN
2660 IF g_line_prc_flex is null THEN
2661 g_line_prc_flex := Load_flex_enabled_flag(p_flex_name);
2662 END IF;
2663 RETURN g_line_prc_flex;
2664 ELSIF p_flex_name = 'OE_LINE_TP_ATTRIBUTES' THEN
2665 IF g_line_tp_flex is null THEN
2666 g_line_tp_flex := Load_flex_enabled_flag(p_flex_name);
2667 END IF;
2668 RETURN g_line_tp_flex;
2669 ELSIF p_flex_name = 'OE_LINE_RETURN_ATTRIBUTE' THEN
2670 IF g_line_ret_flex is null THEN
2671 g_line_ret_flex := Load_flex_enabled_flag(p_flex_name);
2672 END IF;
2673 RETURN g_line_ret_flex;
2674 ELSIF p_flex_name = 'OE_LINE_INDUSTRY_ATTRIBUTE' THEN
2675
2676 IF g_line_ind_flex is null THEN
2677 IF OE_GLOBALS.G_RLM_INSTALLED = 'Y' THEN
2678 l_flex_name := 'RLM_SCHEDULE_LINES';
2679 ELSE -- 2684403, 2511313
2680 l_flex_name := p_flex_name;
2681 END IF;
2682 g_line_ind_flex := Load_flex_enabled_flag(l_flex_name);
2683 END IF;
2684 RETURN g_line_ind_flex;
2685 ELSIF p_flex_name = 'OE_BLKT_HEADER_ATTRIBUTES' THEN
2686 IF g_hdr_blkt_desc_flex is null THEN
2687 g_hdr_blkt_desc_flex := Load_flex_enabled_flag(p_flex_name);
2688 END IF;
2689 RETURN g_hdr_blkt_desc_flex;
2690 ELSIF p_flex_name = 'OE_BLKT_LINE_ATTRIBUTES' THEN
2691 IF g_line_blkt_desc_flex is null THEN
2692 g_line_blkt_desc_flex := Load_flex_enabled_flag(p_flex_name);
2693 END IF;
2694 RETURN g_line_blkt_desc_flex;
2695 END IF;
2696 NULL;
2697 if l_debug_level > 0 then
2698 oe_debug_pub.add('Exit Is Flex Enabled' ,1);
2699 end if;
2700
2701 END IS_FLEX_ENABLED;
2702
2703
2704 FUNCTION LOAD_FLEX_ENABLED_FLAG(p_flex_name VARCHAR2)
2705 RETURN VARCHAR2
2706 IS
2707 l_count number;
2708 l_application_id number; --For bug 2684403
2709 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2710 BEGIN
2711 if l_debug_level > 0 then
2712 oe_debug_pub.add('Enter Load Flex Enabled Flag ' ,1);
2713 end if;
2714
2715 l_application_id := 660;
2716
2717 IF p_flex_name = 'RLM_SCHEDULE_LINES' THEN --For bug 2684403
2718 l_application_id := 662;
2719 END IF;
2720
2721 SELECT count(*)
2722 INTO l_count
2723 FROM fnd_descr_flex_column_usages
2724 WHERE APPLICATION_ID = l_application_id
2725 AND DESCRIPTIVE_FLEXFIELD_NAME = p_flex_name
2726 AND ENABLED_FLAG = 'Y'
2727 AND ROWNUM = 1;
2728
2729 IF l_count = 1 THEN
2730 RETURN 'Y';
2731 ELSE
2732 RETURN 'N';
2733 END IF;
2734 if l_debug_level > 0 then
2735 oe_debug_pub.add('Exit Load Flex Enabled Flag ' ,1);
2736 end if;
2737
2738 EXCEPTION
2739 WHEN OTHERS THEN
2740 RETURN 'N';
2741 END LOAD_FLEX_ENABLED_FLAG ;
2742
2743
2744
2745
2746
2747 END OE_Order_Cache;