[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_CACHE
Source
1 PACKAGE BODY OE_Order_Cache AS
2 /* $Header: OEXUCCHB.pls 120.13.12020000.2 2013/01/07 09:16:27 sujithku 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 ,CONTRACT_ITEM_TYPE_CODE --sol_ord_er #16014165
1591 INTO g_item_rec.inventory_item_id
1592 ,g_item_rec.organization_id
1593 ,g_item_rec.invoicing_rule_id
1594 ,g_item_rec.accounting_rule_id
1595 ,g_item_rec.default_shipping_org
1596 ,g_item_rec.ship_model_complete_flag
1597 ,g_item_rec.build_in_wip_flag
1598 ,g_item_rec.bom_item_type
1599 ,g_item_rec.replenish_to_order_flag
1600 ,g_item_rec.primary_uom_code
1601 ,g_item_rec.pick_components_flag
1602 ,g_item_rec.shippable_item_flag
1603 ,g_item_rec.service_item_flag
1604 -- Pack J catchweight
1605 ,g_item_rec.ont_pricing_qty_source
1606 ,g_item_rec.tracking_quantity_ind
1607 ,g_item_rec.secondary_uom_code
1608 -- 4171642 FP
1609 ,g_item_rec.master_org_id
1610 ,g_item_rec.customer_order_enabled_flag
1611 ,g_item_rec.internal_order_enabled_flag
1612 ,g_item_rec.returnable_flag
1613 ,g_item_rec.restrict_subinventories_code
1614 -- 4171642
1615 -- INVCONV start
1616 ,g_item_rec.secondary_default_ind
1617 ,g_item_rec.lot_divisible_flag
1618 ,g_item_rec.grade_control_flag
1619 ,g_item_rec.lot_control_code
1620 ,g_item_rec.CONTRACT_ITEM_TYPE_CODE --sol_ord_er #16014165
1621 FROM MTL_SYSTEM_ITEMS
1622 WHERE INVENTORY_ITEM_ID = p_key1
1623 AND ORGANIZATION_ID = l_key2;
1624
1625 -- Since inventory is change, load shippable_item_flag.
1626 l_inventory_changed := 'Y';
1627 -- INVCONV start remove opm
1628 -- OPM 02/JUN/00 - OPM item master characteristics
1629 /*
1630 IF NVL(FND_PROFILE.VALUE('ONT_PROCESS_INSTALLED_FLAG'),'Y')
1631 <> 'N' THEN
1632 IF INV_GMI_RSV_BRANCH.G_PROCESS_INV_INSTALLED = 'I' THEN
1633 OPEN c_opm_item( l_key2
1634 , p_key1);
1635 FETCH c_opm_item
1636 INTO g_item_rec.opm_item_id
1637 ,g_item_rec.opm_item_um
1638 ,g_item_rec.opm_item_um2
1639 ,g_item_rec.dualum_ind
1640 ,g_item_rec.grade_ctl;
1641
1642 IF c_opm_item%NOTFOUND THEN
1643 -- OPM 30/JUN/00 Fully clear the process cache
1644 g_item_rec.opm_item_id := NULL;
1645 g_item_rec.opm_item_um := NULL;
1646 g_item_rec.opm_item_um2 := NULL;
1647 g_item_rec.dualum_ind := NULL;
1648 g_item_rec.grade_ctl := NULL;
1649 END IF;
1650
1651 -- Moved this code from here inside the if INV_GMI_RSV_BRANCH.Is_Org_Process_Org(p_key2) THEN, because it was overriding the
1652 -- value of ont_pricing_qty_source for catchweight item
1653 -- g_item_rec.ont_pricing_qty_source := GML_READ_IC_B.read_price_qty_source(p_key1, l_key2); -- 2044240
1654 -- OE_DEBUG_PUB.ADD('OPM ont_pricing_qty_source after read ic_item_mst_b = ' || g_item_rec.ont_pricing_qty_source, 5);
1655
1656
1657 END IF; -- end of IF NVL(FND_PROFILE.VALUE('ONT_PROCESS_INSTALLED_FLAG'),'Y')
1658 END IF; -- IF INV_GMI_RSV_BRANCH.G_PROCESS_INV_INSTALLED = 'I
1659
1660
1661 */
1662 -- INVCONV end
1663
1664
1665 /* OPM 02/JUN/00 END */
1666
1667 END IF;
1668
1669 /* When p_key2 is not null ie. ship_from_org_id is not null then
1670 load the shippable_item_flag based on the ship_from_org. In future
1671 please add the attributes here that needs to be loaded based on the
1672 ship_from_org_id */
1673
1674 IF (p_key2 IS NOT NULL) AND
1675 (p_key2 <> FND_API.G_MISS_NUM) --AND
1676 -- (p_key2 <> l_key2 ) --added for bug 4171642 removed for bug 6666457
1677 THEN
1678
1679 IF (g_item_rec.organization_id <> p_key2)
1680 OR (l_inventory_changed = 'Y' ) THEN
1681
1682 l_inventory_changed := 'N';
1683
1684 if l_debug_level > 0 then
1685 OE_DEBUG_PUB.ADD('querying based on ship_from_org', 3);
1686 end if;
1687 SELECT shippable_item_flag
1688 ,organization_id
1689 , restrict_subinventories_code -- bug 4171642
1690 ,ONT_PRICING_QTY_SOURCE -- INVCONV
1691 ,TRACKING_QUANTITY_IND
1692 ,SECONDARY_UOM_CODE
1693 ,SECONDARY_DEFAULT_IND
1694 ,LOT_DIVISIBLE_FLAG
1695 ,GRADE_CONTROL_FLAG
1696 ,LOT_CONTROL_CODE
1697 , returnable_flag --5608844
1698 ,PRIMARY_UOM_CODE -- 5608585
1699
1700 INTO g_item_rec.shippable_item_flag
1701 ,g_item_rec.organization_id
1702 ,g_item_rec.restrict_subinventories_code
1703 ,g_item_rec.ont_pricing_qty_source
1704 ,g_item_rec.tracking_quantity_ind
1705 ,g_item_rec.secondary_uom_code
1706 -- INVCONV start
1707 ,g_item_rec.secondary_default_ind
1708 ,g_item_rec.lot_divisible_flag
1709 ,g_item_rec.grade_control_flag
1710 ,g_item_rec.lot_control_code
1711 ,g_item_rec.returnable_flag --5608844
1712 ,g_item_rec.primary_uom_code -- 5608585
1713
1714
1715 FROM MTL_SYSTEM_ITEMS
1716 WHERE INVENTORY_ITEM_ID = p_key1
1717 AND ORGANIZATION_ID = p_key2;
1718 -- Pack J catchweight
1719 --Find out whether the inventory org is WMS enabled from mtl_parameters
1720 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110510' THEN
1721 SELECT wms_enabled_flag
1722 INTO g_item_rec.wms_enabled_flag
1723 FROM mtl_parameters
1724 WHERE organization_id = p_key2;
1725 END IF;
1726
1727 END IF;
1728 END IF;
1729 END IF;
1730
1731 /* OPM - check for process warehouse */
1732 IF INV_GMI_RSV_BRANCH.Is_Org_Process_Org(p_key2) THEN
1733 g_item_rec.process_warehouse_flag := 'Y';
1734 --g_item_rec.ont_pricing_qty_source := GML_READ_IC_B.read_price_qty_source(p_key1, l_key2); -- INVCONV 2044240
1735 --if l_debug_level > 0 then
1736 --OE_DEBUG_PUB.ADD('OPM ont_pricing_qty_source after read ic_item_mst_b = ' || g_item_rec.ont_pricing_qty_source, 5);
1737 --end if;
1738 ELSE
1739 g_item_rec.process_warehouse_flag := NULL;
1740 END IF;
1741
1742 if l_debug_level > 0 then
1743 oe_debug_pub.add('in OE_ORDER_CACHE.LOAD_ITEM process warehouse flag is ' || g_item_rec.process_warehouse_flag );
1744 /* OPM END */
1745
1746 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_ITEM', 1);
1747 end if;
1748 EXCEPTION
1749 -- this is temporary workaround
1750
1751 WHEN NO_DATA_FOUND THEN
1752
1753 SELECT INVENTORY_ITEM_ID
1754 , ORGANIZATION_ID
1755 , INVOICING_RULE_ID
1756 , ACCOUNTING_RULE_ID
1757 , DEFAULT_SHIPPING_ORG
1758 , SHIP_MODEL_COMPLETE_FLAG
1759 , BUILD_IN_WIP_FLAG
1760 , BOM_ITEM_TYPE
1761 , REPLENISH_TO_ORDER_FLAG
1762 , PRIMARY_UOM_CODE
1763 , PICK_COMPONENTS_FLAG
1764 , SHIPPABLE_ITEM_FLAG
1765 , SERVICE_ITEM_FLAG
1766 -- added for bug 4171642
1767 ,ORGANIZATION_ID
1768 ,CUSTOMER_ORDER_ENABLED_FLAG
1769 ,INTERNAL_ORDER_ENABLED_FLAG
1770 ,RETURNABLE_FLAG
1771 ,RESTRICT_SUBINVENTORIES_CODE
1772
1773 INTO g_item_rec.inventory_item_id
1774 , g_item_rec.organization_id
1775 , g_item_rec.invoicing_rule_id
1776 , g_item_rec.accounting_rule_id
1777 , g_item_rec.default_shipping_org
1778 , g_item_rec.ship_model_complete_flag
1779 , g_item_rec.build_in_wip_flag
1780 , g_item_rec.bom_item_type
1781 , g_item_rec.replenish_to_order_flag
1782 , g_item_rec.primary_uom_code
1783 , g_item_rec.pick_components_flag
1784 , g_item_rec.shippable_item_flag
1785 , g_item_rec.service_item_flag
1786 -- bug 4171642
1787 ,g_item_rec.master_org_id
1788 ,g_item_rec.customer_order_enabled_flag
1789 ,g_item_rec.internal_order_enabled_flag
1790 ,g_item_rec.returnable_flag
1791 ,g_item_rec.restrict_subinventories_code
1792
1793 FROM MTL_SYSTEM_ITEMS
1794 WHERE INVENTORY_ITEM_ID = p_key1
1795 AND ORGANIZATION_ID = OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID',p_key3);
1796
1797 if l_debug_level > 0 then
1798 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_ITEM - item doesnt exist in ship_from', 2);
1799 end if;
1800 WHEN OTHERS THEN
1801
1802 if l_debug_level > 0 then
1803 oe_debug_pub.add('exception in load item', 1);
1804 end if;
1805 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1806 THEN
1807 OE_MSG_PUB.Add_Exc_Msg
1808 ( G_PKG_NAME ,
1809 'Load_Item'
1810 );
1811 END IF;
1812
1813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1814
1815 END Load_Item;
1816
1817 FUNCTION Load_Set
1818 ( p_set_id IN NUMBER)
1819 RETURN set_rec_type
1820 IS
1821 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1822 BEGIN
1823
1824 if l_debug_level > 0 then
1825 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_SET', 1);
1826 end if;
1827
1828 IF (p_set_id IS NOT NULL)
1829 THEN
1830 --IF (g_set_rec.set_id <> p_set_id)
1831 -- THEN
1832 BEGIN
1833 SELECT set_id
1834 ,set_name
1835 ,set_type
1836 ,header_Id
1837 ,ship_from_org_id
1838 ,ship_to_org_id
1839 ,schedule_ship_date
1840 ,schedule_arrival_date
1841 ,shipment_priority_code
1842 ,freight_carrier_code
1843 ,shipping_method_code
1844 ,set_status
1845
1846 INTO g_set_rec.set_id
1847 ,g_set_rec.set_name
1848 ,g_set_rec.set_type
1849 ,g_set_rec.Header_Id
1850 ,g_set_rec.Ship_from_org_id
1851 ,g_set_rec.Ship_to_org_id
1852 ,g_set_rec.Schedule_Ship_Date
1853 ,g_set_rec.Schedule_Arrival_Date
1854 ,g_set_rec.Shipment_priority_code
1855 ,g_set_rec.Freight_Carrier_Code
1856 ,g_set_Rec.Shipping_Method_Code
1857 ,g_set_rec.Set_Status
1858
1859 FROM oe_sets
1860 WHERE oe_sets.set_id= p_set_id;
1861
1862 EXCEPTION
1863
1864 WHEN NO_DATA_FOUND THEN
1865 g_set_rec.set_id := NULL;
1866 g_set_rec.set_name := NULL;
1867 g_set_rec.set_type := NULL;
1868 g_set_rec.Header_Id := NULL;
1869 g_set_rec.Ship_from_org_id := NULL;
1870 g_set_rec.Ship_to_org_id := NULL;
1871 g_set_rec.shipment_priority_code := NULL;
1872 g_set_rec.Schedule_Ship_Date:= NULL;
1873 g_set_rec.Schedule_Arrival_Date := NULL;
1874 g_set_rec.Freight_Carrier_Code := NULL;
1875 g_set_Rec.Shipping_Method_Code := NULL;
1876 g_set_rec.Set_Status := NULL;
1877
1878 WHEN OTHERS THEN
1879
1880 IF OE_MSG_PUB.Check_Msg_Level
1881 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1882 THEN
1883 OE_MSG_PUB.Add_Exc_Msg
1884 ( G_PKG_NAME
1885 , 'Load_set '
1886 );
1887 END IF;
1888
1889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1890
1891 END;
1892 --END IF;
1893
1894 if l_debug_level > 0 then
1895 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_SET', 1);
1896 end if;
1897
1898 RETURN g_set_rec;
1899 END IF;
1900
1901 if l_debug_level > 0 then
1902 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_SET', 1);
1903 end if;
1904
1905 RETURN g_set_rec;
1906
1907 EXCEPTION
1908
1909 WHEN OTHERS THEN
1910
1911 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1912 THEN
1913 OE_MSG_PUB.Add_Exc_Msg
1914 ( G_PKG_NAME ,
1915 'Load_Delivery_Set'
1916 );
1917 END IF;
1918
1919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1920
1921 END;
1922
1923 -- added for bug 4200055
1924
1925 FUNCTION Load_Payment_Term
1926 ( p_key IN NUMBER )
1927 RETURN Payment_Term_Rec_Type
1928 IS
1929 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1930 BEGIN
1931
1932 if l_debug_level > 0 then
1933 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_Payment_Term', 1);
1934 end if;
1935
1936 IF p_key IS NOT NULL THEN
1937 IF g_Payment_Term_rec.term_id = FND_API.G_MISS_NUM OR
1938 g_Payment_Term_rec.term_id <> p_key THEN
1939 oe_debug_pub.add('querying oe_ra_terms_v');
1940 SELECT term_id ,
1941 name,
1942 start_date_active,
1943 end_date_active
1944 INTO
1945 g_Payment_Term_rec.term_id,
1946 g_Payment_Term_rec.name,
1947 g_Payment_Term_rec.start_date_active,
1948 g_Payment_Term_rec.end_date_active
1949 FROM OE_RA_TERMS_V
1950 WHERE term_id = p_key ;
1951
1952 END IF ;
1953 END IF ;
1954
1955 if l_debug_level > 0 then
1956 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_Payment_Term', 1);
1957 end if;
1958 RETURN g_Payment_term_rec;
1959 EXCEPTION
1960 WHEN NO_DATA_FOUND THEN
1961 oe_debug_pub.add('No Data Found in OE_Order_Cache.Load_Payment_Term');
1962 RAISE NO_DATA_FOUND ;
1963
1964 -- Returning g_payment_rec could potentially pass the wrong result and
1965 -- if processed could result to data corruption
1966
1967 --return g_payment_term_rec ;
1968 WHEN OTHERS THEN
1969
1970 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1971 THEN
1972 OE_MSG_PUB.Add_Exc_Msg
1973 ( G_PKG_NAME ,
1974 'Load_Payment_Term'
1975 );
1976 END IF;
1977
1978 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1979
1980 END Load_Payment_Term;
1981
1982 FUNCTION Load_Salesrep_rec
1983 ( p_key IN NUMBER )
1984 RETURN Salesrep_Rec_Type
1985 IS
1986 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1987 BEGIN
1988
1989 if l_debug_level > 0 then
1990 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_Salesrep_rec', 1);
1991 end if;
1992
1993 IF p_key IS NOT NULL THEN
1994 IF g_Salesrep_rec.salesrep_id = FND_API.G_MISS_NUM OR
1995 g_Salesrep_rec.salesrep_id <> p_key THEN
1996 oe_debug_pub.add('Load Salesrep cache');
1997 SELECT salesrep_id ,
1998 name,
1999 status,
2000 start_date_active,
2001 end_date_active
2002 INTO
2003 g_Salesrep_rec.salesrep_id,
2004 g_Salesrep_rec.name,
2005 g_Salesrep_rec.status,
2006 g_Salesrep_rec.start_date_active,
2007 g_Salesrep_rec.end_date_active
2008 FROM RA_SALESREPS
2009 WHERE salesrep_id = p_key ;
2010
2011 END IF ;
2012 END IF ;
2013
2014 if l_debug_level > 0 then
2015 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_Salesrep', 1);
2016 end if;
2017 RETURN g_Salesrep_rec;
2018 EXCEPTION
2019 WHEN NO_DATA_FOUND THEN
2020 oe_debug_pub.add('No Data Found in OE_Order_Cache.Load_Salesrep_rec');
2021 RAISE NO_DATA_FOUND ;
2022 -- Returing g_salesrep_rec could potentially pass the old information and if processed can result into corruption issue.
2023
2024 -- return g_salesrep_rec ;
2025 WHEN OTHERS THEN
2026
2027 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2028 THEN
2029 OE_MSG_PUB.Add_Exc_Msg
2030 ( G_PKG_NAME ,
2031 'Load_Salesrep_rec'
2032 );
2033 END IF;
2034
2035 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2036
2037 END Load_Salesrep_rec;
2038
2039 --end bug 4200055
2040
2041 -- procedures that set the cached records.
2042
2043 PROCEDURE Set_Order_Header
2044 (
2045 p_header_rec IN OE_ORDER_PUB.Header_Rec_Type
2046 ) IS
2047 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2048 BEGIN
2049
2050 if l_debug_level > 0 then
2051 oe_debug_pub.add('Entering OE_ORDER_CACHE.Set_Order_Header', 1);
2052 end if;
2053
2054 IF (p_header_rec.header_id IS NOT NULL) THEN
2055
2056 IF (p_header_rec.header_id = nvl(g_header_rec.header_id,0)) THEN
2057
2058 g_header_rec := p_header_rec;
2059
2060 END IF;
2061
2062 END IF;
2063
2064 if l_debug_level > 0 then
2065 oe_debug_pub.add('Exiting OE_ORDER_CACHE.Set_Order_Header', 1);
2066 end if;
2067
2068 END Set_Order_Header;
2069
2070
2071 -- procedures that clear cached entities.
2072
2073 PROCEDURE Clear_Top_Model_Line(p_key IN NUMBER)
2074 IS
2075 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2076 BEGIN
2077
2078 if l_debug_level > 0 then
2079 oe_debug_pub.add('Entering OE_ORDER_CACHE.Top_Model_Line', 1);
2080 end if;
2081
2082 IF nvl(g_top_model_line_rec.line_id,0) = p_key THEN
2083 if l_debug_level > 0 then
2084 oe_debug_pub.add('in ucchb, clearing top model cache: '|| p_key, 3);
2085 end if;
2086 g_top_model_line_rec := OE_ORDER_PUB.G_MISS_LINE_REC;
2087 END IF;
2088
2089 if l_debug_level > 0 then
2090 oe_debug_pub.add('Exiting OE_ORDER_CACHE.Top_Model_Line', 1);
2091 end if;
2092
2093 END Clear_Top_Model_Line;
2094
2095
2096 PROCEDURE Clear_Order_Type
2097 IS
2098 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2099 BEGIN
2100
2101 if l_debug_level > 0 then
2102 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_ORDER_TYPE', 1);
2103 end if;
2104 g_order_type_rec := G_MISS_ORDER_TYPE_REC;
2105
2106 if l_debug_level > 0 then
2107 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_ORDER_TYPE', 1);
2108 end if;
2109 END Clear_Order_Type;
2110
2111 PROCEDURE Clear_Agreement
2112 IS
2113 BEGIN
2114
2115 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_AGREEMENT', 1);
2116
2117 g_agreement_rec := G_MISS_AGREEMENT_REC;
2118
2119 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_AGREEMENT', 1);
2120
2121 END Clear_Agreement;
2122
2123 PROCEDURE Clear_Ship_To_Org
2124 IS
2125 BEGIN
2126
2127 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_SHIP_TO_ORG', 1);
2128
2129 g_ship_to_rec := G_MISS_SHIP_TO_REC;
2130
2131 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_SHIP_TO_ORG', 1);
2132
2133 END Clear_Ship_To_Org;
2134
2135 PROCEDURE Clear_Invoice_To_Org
2136 IS
2137 BEGIN
2138
2139 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_INVOICE_TO_ORG', 1);
2140
2141 g_invoice_to_rec := G_MISS_INVOICE_TO_REC;
2142
2143 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_INVOICE_TO_ORG', 1);
2144
2145 END Clear_Invoice_To_Org;
2146
2147 PROCEDURE Clear_Deliver_To_Org
2148 IS
2149 BEGIN
2150
2151 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_DELIVER_TO_ORG', 1);
2152
2153 g_deliver_to_rec := G_MISS_DELIVER_TO_REC;
2154
2155 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_DELIVER_TO_ORG', 1);
2156
2157 END Clear_Deliver_To_Org;
2158
2159 PROCEDURE Clear_Sold_To_Org
2160 IS
2161 BEGIN
2162
2163 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_SOLD_TO_ORG', 1);
2164
2165 g_sold_to_rec := G_MISS_SOLD_TO_REC;
2166
2167 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_SOLD_TO_ORG', 1);
2168
2169 END Clear_Sold_To_Org;
2170
2171 PROCEDURE Clear_Price_List
2172 IS
2173 BEGIN
2174
2175 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_PRICE_LIST', 1);
2176
2177 g_price_list_rec := G_MISS_PRICE_LIST_REC;
2178
2179 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_PRICE_LIST', 1);
2180
2181 END Clear_Price_List;
2182
2183 PROCEDURE Clear_Set_Of_Books
2184 IS
2185 BEGIN
2186
2187 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_SET_OF_BOOKS', 1);
2188
2189 g_set_of_books_rec := G_MISS_SET_OF_BOOKS_REC;
2190
2191 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_SET_OF_BOOKS', 1);
2192
2193 END Clear_Set_Of_Books;
2194
2195 PROCEDURE Clear_item
2196 IS
2197 BEGIN
2198
2199 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_ITEM', 1);
2200
2201 g_item_rec := G_MISS_ITEM_REC;
2202
2203 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_ITEM', 1);
2204
2205 END Clear_item;
2206
2207 PROCEDURE Clear_item_Cost
2208 IS
2209 BEGIN
2210
2211 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_ITEM_COST', 1);
2212
2213 g_item_cost_rec := G_MISS_ITEM_COST_REC;
2214
2215 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_ITEM_COST', 1);
2216
2217 END Clear_item_Cost;
2218
2219 PROCEDURE Clear_Order_Header
2220 IS
2221 BEGIN
2222
2223 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_ORDER_HEADER', 1);
2224
2225 g_header_rec := OE_Order_PUB.G_MISS_HEADER_REC;
2226
2227 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_ORDER_HEADER', 1);
2228
2229 END Clear_Order_Header;
2230
2231
2232 PROCEDURE Clear_Discount
2233 IS
2234 BEGIN
2235
2236 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_DISCOUNT', 1);
2237
2238 g_hdr_discount_rec := oe_order_cache.g_miss_discount_rec;
2239 g_line_discount_rec := oe_order_cache.g_miss_discount_rec;
2240
2241 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_DISCOUNT', 1);
2242
2243 END clear_discount;
2244
2245 --added for bug 4200055
2246 PROCEDURE Clear_Salesrep
2247 IS
2248 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2249
2250 BEGIN
2251 IF l_debug_level > 0 THEN
2252 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_SALESREP', 1);
2253 END IF;
2254
2255 g_salesrep_rec := oe_order_cache.g_miss_salesrep_rec;
2256
2257 IF l_debug_level > 0 THEN
2258 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_SALESREP', 1);
2259 END IF;
2260
2261 END clear_salesrep;
2262
2263
2264 PROCEDURE Clear_Payment_Term
2265 IS
2266 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2267
2268 BEGIN
2269 IF l_debug_level > 0 THEN
2270 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_payment_term', 1);
2271 END IF;
2272
2273 g_payment_term_rec := oe_order_cache.g_miss_payment_term_rec;
2274
2275 IF l_debug_level > 0 THEN
2276 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_payment_term', 1);
2277 END IF;
2278
2279 END clear_payment_term;
2280 -- end bug 4200055
2281
2282 PROCEDURE Clear_All
2283 IS
2284 BEGIN
2285
2286 oe_debug_pub.add('Entering OE_ORDER_CACHE.CLEAR_ALL', 1);
2287
2288 Clear_Order_Type;
2289 Clear_Agreement;
2290 Clear_Ship_To_Org;
2291 Clear_Invoice_To_Org;
2292 Clear_Deliver_To_Org;
2293 Clear_Sold_To_Org;
2294 Clear_Price_List;
2295 Clear_Set_Of_Books;
2296 Clear_Item;
2297 Clear_Item_Cost;
2298 Clear_Order_Header;
2299 Clear_Discount;
2300 --added for bug 4200055
2301 Clear_Payment_Term ;
2302 Clear_Salesrep ;
2303 --end
2304
2305 oe_debug_pub.add('Exiting OE_ORDER_CACHE.CLEAR_ALL', 1);
2306
2307 END Clear_All;
2308
2309 FUNCTION Get_Set_Of_Books
2310 Return Number
2311 IS
2312 l_set_of_books_id Number;
2313 BEGIN
2314 l_set_of_books_id := OE_Sys_Parameters.VALUE('SET_OF_BOOKS_ID');
2315
2316 RETURN l_set_of_books_id;
2317 END Get_Set_Of_Books;
2318
2319
2320 FUNCTION Load_List_Lines
2321 ( p_key IN NUMBER )
2322 RETURN Modifiers_Rec_Type
2323 IS
2324 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2325 BEGIN
2326
2327 if l_debug_level > 0 then
2328 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_list_lines', 1);
2329 end if;
2330
2331 IF p_key IS NOT NULL THEN
2332
2333 IF g_Modifiers_Rec.list_line_id = FND_API.G_MISS_NUM OR
2334 g_Modifiers_Rec.list_line_id <> p_key THEN
2335
2336 SELECT arithmetic_operator
2337 ,automatic_flag
2338 ,base_qty
2339 ,base_uom_code
2340 ,inventory_item_id
2341 ,list_header_id
2342 ,list_line_id
2343 ,list_line_type_code
2344 ,modifier_level_code
2345 ,operand
2346 ,organization_id
2347 ,override_flag
2348 ,percent_price
2349 ,price_break_type_code
2350 ,price_by_formula_id
2351 ,primary_uom_flag
2352 ,print_on_invoice_flag
2353 ,rebate_transaction_type_code
2354 ,related_item_id
2355 ,relationship_type_id
2356 ,substitution_attribute
2357 ,substitution_context
2358 ,substitution_value
2359 ,accrual_flag
2360 ,pricing_group_sequence
2361 ,incompatibility_grp_code
2362 ,list_line_no
2363 ,pricing_phase_id
2364 ,product_precedence
2365 ,expiration_date
2366 ,charge_type_code
2367 ,charge_subtype_code
2368 ,benefit_qty
2369 ,benefit_uom_code
2370 ,accrual_conversion_rate
2371 ,proration_type_code
2372 ,include_on_returns_flag
2373 ,print_on_invoice_flag
2374 ,accrual_flag
2375 INTO
2376 g_Modifiers_Rec.arithmetic_operator
2377 ,g_Modifiers_Rec.automatic_flag
2378 ,g_Modifiers_Rec.base_qty
2379 ,g_Modifiers_Rec.base_uom_code
2380 ,g_Modifiers_Rec.inventory_item_id
2381 ,g_Modifiers_Rec.list_header_id
2382 ,g_Modifiers_Rec.list_line_id
2383 ,g_Modifiers_Rec.list_line_type_code
2384 ,g_Modifiers_Rec.modifier_level_code
2385 ,g_Modifiers_Rec.operand
2386 ,g_Modifiers_Rec.organization_id
2387 ,g_Modifiers_Rec.override_flag
2388 ,g_Modifiers_Rec.percent_price
2389 ,g_Modifiers_Rec.price_break_type_code
2390 ,g_Modifiers_Rec.price_by_formula_id
2391 ,g_Modifiers_Rec.primary_uom_flag
2392 ,g_Modifiers_Rec.print_on_invoice_flag
2393 ,g_Modifiers_Rec.rebate_transaction_type_code
2394 ,g_Modifiers_Rec.related_item_id
2395 ,g_Modifiers_Rec.relationship_type_id
2396 ,g_Modifiers_Rec.substitution_attribute
2397 ,g_Modifiers_Rec.substitution_context
2398 ,g_Modifiers_Rec.substitution_value
2399 ,g_Modifiers_Rec.accrual_flag
2400 ,g_Modifiers_Rec.pricing_group_sequence
2401 ,g_Modifiers_Rec.incompatibility_grp_code
2402 ,g_Modifiers_Rec.list_line_no
2403 ,g_Modifiers_Rec.pricing_phase_id
2404 ,g_Modifiers_Rec.product_precedence
2405 ,g_Modifiers_Rec.expiration_date
2406 ,g_Modifiers_Rec.charge_type_code
2407 ,g_Modifiers_Rec.charge_subtype_code
2408 ,g_Modifiers_Rec.benefit_qty
2409 ,g_Modifiers_Rec.benefit_uom_code
2410 ,g_Modifiers_Rec.accrual_conversion_rate
2411 ,g_Modifiers_Rec.proration_type_code
2412 ,g_Modifiers_Rec.include_on_returns_flag
2413 ,g_Modifiers_Rec.print_on_invoice_flag
2414 ,g_Modifiers_Rec.accrual_flag
2415 FROM qp_list_lines
2416 WHERE list_line_id= p_key;
2417
2418 END IF;
2419
2420 END IF;
2421
2422 if l_debug_level > 0 then
2423 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_list_lines', 1);
2424 end if;
2425
2426 RETURN g_Modifiers_Rec;
2427
2428 EXCEPTION
2429
2430 WHEN OTHERS THEN
2431
2432 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2433 THEN
2434 OE_MSG_PUB.Add_Exc_Msg
2435 ( G_PKG_NAME ,
2436 'Load_list_lines'||sqlerrm
2437 );
2438 END IF;
2439
2440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2441
2442 END Load_List_Lines;
2443
2444 Function Load_Cust_Trx_Type
2445 ( p_key IN NUMBER )
2446 RETURN Cust_Trx_Rec_Type
2447 IS
2448 BEGIN
2449
2450 Load_Cust_Trx_Type(p_key);
2451
2452 RETURN g_cust_trx_rec;
2453
2454 END Load_Cust_Trx_Type;
2455
2456 Procedure Load_Cust_Trx_Type
2457 ( p_key IN NUMBER )
2458 IS
2459 l_calculate_tax_flag varchar2(1) := NULL;
2460 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2461 l_current_org_id NUMBER ; -- MOAC Changes
2462 BEGIN
2463
2464 if l_debug_level > 0 then
2465 oe_debug_pub.add('Entering OE_ORDER_CACHE.LOAD_CUST_TRX_TYPE', 1);
2466 end if;
2467
2468 l_current_org_id := MO_Global.Get_Current_Org_Id() ; --MOAC changes
2469
2470 -- New condition added for bug 2281054
2471 If p_key = 0 then
2472 if l_debug_level > 0 then
2473 oe_debug_pub.add('No Receivable Transaction Type assigned at any of the levels');
2474 end if;
2475 -- bug 2604421, need to initialize this value, otherwise the
2476 -- tax_calculation_flag cached from previous order will remain.
2477 g_cust_trx_rec.tax_calculation_flag := null;
2478 g_cust_trx_rec.cust_trx_type_id := null;
2479 g_cust_trx_rec.org_id := null ; -- MOAC changes
2480
2481 goto THE_END;
2482 end if;
2483
2484 IF p_key IS NOT NULL THEN
2485 /* Modified the If condition for Bug-2113379 */
2486 IF Nvl(g_cust_trx_rec.cust_trx_type_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM OR
2487 g_cust_trx_rec.cust_trx_type_id <> p_key OR
2488 g_cust_trx_rec.org_id <> l_current_org_id THEN --MOAC changes
2489
2490 SELECT tax_calculation_flag
2491 ,cust_trx_type_id
2492 ,org_id
2493 INTO g_cust_trx_rec.tax_calculation_flag
2494 ,g_cust_trx_rec.cust_trx_type_id
2495 ,g_cust_trx_rec.org_id -- MOAC Changes
2496 FROM RA_CUST_TRX_TYPES_ALL
2497 WHERE CUST_TRX_TYPE_ID = p_key
2498 AND ORG_ID = l_current_org_id ;
2499
2500 END IF;
2501
2502 END IF;
2503 <<THE_END>>
2504
2505 if l_debug_level > 0 then
2506 oe_debug_pub.add('Exiting OE_ORDER_CACHE.LOAD_CUST_TRX_TYPE', 1);
2507 end if;
2508
2509 EXCEPTION
2510
2511 WHEN OTHERS THEN
2512
2513 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2514 THEN
2515 OE_MSG_PUB.Add_Exc_Msg
2516 ( G_PKG_NAME ,
2517 'Load_Cust_Trx_Type'
2518 );
2519 END IF;
2520
2521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2522 END Load_Cust_Trx_Type;
2523
2524 FUNCTION get_tax_calculation_flag
2525 ( p_key IN NUMBER,
2526 p_line_rec IN OE_ORDER_PUB.Line_Rec_Type )
2527 RETURN Tax_Calc_Rec_Type
2528 IS
2529 l_calculate_tax_flag varchar2(1) := NULL;
2530 l_tax_rec Tax_Calc_Rec_Type;
2531 l_cust_trx_type_id number;
2532 v_start number;
2533 v_end number;
2534 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2535 BEGIN
2536
2537 --v_start := DBMS_UTILITY.GET_TIME;
2538
2539 if l_debug_level > 0 then
2540 oe_debug_pub.add('Entering OE_ORDER_CACHE.GET_TAX_CALCULATION_FLAG', 1);
2541 end if;
2542
2543 IF p_key IS NOT NULL THEN
2544
2545 IF g_tax_calc_tbl.Exists(p_key) THEN
2546
2547 l_tax_rec.tax_calculation_flag := g_tax_calc_tbl(p_key).tax_calculation_flag;
2548
2549 l_tax_rec.cust_trx_type_id := g_tax_calc_tbl(p_key).cust_trx_type_id;
2550
2551 l_tax_rec.line_type_id := g_tax_calc_tbl(p_key).line_type_id;
2552
2553 if l_debug_level > 0 then
2554 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);
2555 end if;
2556
2557 ELSE
2558
2559
2560 Load_Line_Type(p_key);
2561
2562 l_calculate_tax_flag := g_line_type_rec.calculate_tax_flag;
2563 l_cust_trx_type_id := g_line_type_rec.cust_trx_type_id;
2564
2565 -- made code changes for bug 2604421.
2566 IF ( g_line_type_rec.cust_trx_type_id is null and
2567 g_line_type_rec.calculate_tax_flag is null )
2568 Then
2569
2570 l_cust_trx_type_id :=
2571 OE_INVOICE_PUB.Get_Customer_Transaction_Type(p_line_rec);
2572
2573 load_cust_trx_type(l_cust_trx_type_id);
2574
2575 l_calculate_tax_flag := g_cust_trx_rec.tax_calculation_flag;
2576
2577 l_tax_rec.line_type_id := p_key;
2578 l_tax_rec.tax_calculation_flag := l_calculate_tax_flag;
2579 l_tax_rec.cust_trx_type_id := l_cust_trx_type_id;
2580
2581 ELSE
2582
2583 l_tax_rec.line_type_id := p_key;
2584 l_tax_rec.tax_calculation_flag := l_calculate_tax_flag;
2585 l_tax_rec.cust_trx_type_id := l_cust_trx_type_id;
2586
2587 g_tax_calc_tbl(p_key) := l_tax_rec;
2588 if l_debug_level > 0 then
2589 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);
2590 end if;
2591
2592 END IF; /* if cust_trx_type_id is null and
2593 calculate_tax_flag is null */
2594
2595 END IF; /* if g_tax_calc_tbl.Exists(p_key) */
2596
2597 END IF; /* IF p_key is not null */
2598
2599 --v_end := DBMS_UTILITY.GET_TIME;
2600
2601 -- oe_debug_pub.add('ren: Time Of execution for get_tax_calculation_flag '||
2602 -- to_char((v_end-v_start)/100),1);
2603
2604 if l_debug_level > 0 then
2605 oe_debug_pub.add('Exiting OE_ORDER_CACHE.GET_TAX_CALCULATION_FLAG', 1);
2606 end if;
2607
2608 RETURN l_tax_rec;
2609
2610
2611
2612 EXCEPTION
2613
2614 WHEN OTHERS THEN
2615
2616 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2617 THEN
2618 OE_MSG_PUB.Add_Exc_Msg
2619 ( G_PKG_NAME ,
2620 'get_tax_calculation_flag'
2621 );
2622 END IF;
2623
2624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2625 END get_tax_calculation_flag;
2626
2627 FUNCTION IS_FLEX_ENABLED(p_flex_name IN VARCHAR2)
2628 RETURN VARCHAR2
2629 IS
2630 l_flex_name varchar2(240);
2631 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2632 BEGIN
2633 if l_debug_level > 0 then
2634 oe_debug_pub.add('Enter Is Flex Enabled' ,1);
2635 end if;
2636 IF p_flex_name = 'OE_HEADER_ATTRIBUTES' THEN
2637 IF g_hdr_desc_flex is null THEN
2638 g_hdr_desc_flex := Load_flex_enabled_flag(p_flex_name);
2639 END IF;
2640 RETURN g_hdr_desc_flex;
2641 ELSIF p_flex_name = 'OE_HEADER_GLOBAL_ATTRIBUTE' THEN
2642 IF g_hdr_glb_flex is null THEN
2643 g_hdr_glb_flex := Load_flex_enabled_flag(p_flex_name);
2644 END IF;
2645 RETURN g_hdr_glb_flex;
2646 ELSIF p_flex_name = 'OE_HEADER_TP_ATTRIBUTES' THEN
2647 IF g_hdr_tp_flex is null THEN
2648 g_hdr_tp_flex := Load_flex_enabled_flag(p_flex_name);
2649 END IF;
2650 RETURN g_hdr_tp_flex;
2651 ELSIF p_flex_name = 'OE_LINE_ATTRIBUTES' THEN
2652 IF g_line_desc_flex is null THEN
2653 g_line_desc_flex := Load_flex_enabled_flag(p_flex_name);
2654 END IF;
2655 RETURN g_line_desc_flex;
2656 ELSIF p_flex_name = 'OE_LINE_GLOBAL_ATTRIBUTE' THEN
2657 IF g_line_glb_flex is null THEN
2658 g_line_glb_flex := Load_flex_enabled_flag(p_flex_name);
2659 END IF;
2660 RETURN g_line_glb_flex;
2661 ELSIF p_flex_name = 'OE_LINE_PRICING_ATTRIBUTE' THEN
2662 IF g_line_prc_flex is null THEN
2663 g_line_prc_flex := Load_flex_enabled_flag(p_flex_name);
2664 END IF;
2665 RETURN g_line_prc_flex;
2666 ELSIF p_flex_name = 'OE_LINE_TP_ATTRIBUTES' THEN
2667 IF g_line_tp_flex is null THEN
2668 g_line_tp_flex := Load_flex_enabled_flag(p_flex_name);
2669 END IF;
2670 RETURN g_line_tp_flex;
2671 ELSIF p_flex_name = 'OE_LINE_RETURN_ATTRIBUTE' THEN
2672 IF g_line_ret_flex is null THEN
2673 g_line_ret_flex := Load_flex_enabled_flag(p_flex_name);
2674 END IF;
2675 RETURN g_line_ret_flex;
2676 ELSIF p_flex_name = 'OE_LINE_INDUSTRY_ATTRIBUTE' THEN
2677
2678 IF g_line_ind_flex is null THEN
2679 IF OE_GLOBALS.G_RLM_INSTALLED = 'Y' THEN
2680 l_flex_name := 'RLM_SCHEDULE_LINES';
2681 ELSE -- 2684403, 2511313
2682 l_flex_name := p_flex_name;
2683 END IF;
2684 g_line_ind_flex := Load_flex_enabled_flag(l_flex_name);
2685 END IF;
2686 RETURN g_line_ind_flex;
2687 ELSIF p_flex_name = 'OE_BLKT_HEADER_ATTRIBUTES' THEN
2688 IF g_hdr_blkt_desc_flex is null THEN
2689 g_hdr_blkt_desc_flex := Load_flex_enabled_flag(p_flex_name);
2690 END IF;
2691 RETURN g_hdr_blkt_desc_flex;
2692 ELSIF p_flex_name = 'OE_BLKT_LINE_ATTRIBUTES' THEN
2693 IF g_line_blkt_desc_flex is null THEN
2694 g_line_blkt_desc_flex := Load_flex_enabled_flag(p_flex_name);
2695 END IF;
2696 RETURN g_line_blkt_desc_flex;
2697 END IF;
2698 NULL;
2699 if l_debug_level > 0 then
2700 oe_debug_pub.add('Exit Is Flex Enabled' ,1);
2701 end if;
2702
2703 END IS_FLEX_ENABLED;
2704
2705
2706 FUNCTION LOAD_FLEX_ENABLED_FLAG(p_flex_name VARCHAR2)
2707 RETURN VARCHAR2
2708 IS
2709 l_count number;
2710 l_application_id number; --For bug 2684403
2711 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2712 BEGIN
2713 if l_debug_level > 0 then
2714 oe_debug_pub.add('Enter Load Flex Enabled Flag ' ,1);
2715 end if;
2716
2717 l_application_id := 660;
2718
2719 IF p_flex_name = 'RLM_SCHEDULE_LINES' THEN --For bug 2684403
2720 l_application_id := 662;
2721 END IF;
2722
2723 SELECT count(*)
2724 INTO l_count
2725 FROM fnd_descr_flex_column_usages
2726 WHERE APPLICATION_ID = l_application_id
2727 AND DESCRIPTIVE_FLEXFIELD_NAME = p_flex_name
2728 AND ENABLED_FLAG = 'Y'
2729 AND ROWNUM = 1;
2730
2731 IF l_count = 1 THEN
2732 RETURN 'Y';
2733 ELSE
2734 RETURN 'N';
2735 END IF;
2736 if l_debug_level > 0 then
2737 oe_debug_pub.add('Exit Load Flex Enabled Flag ' ,1);
2738 end if;
2739
2740 EXCEPTION
2741 WHEN OTHERS THEN
2742 RETURN 'N';
2743 END LOAD_FLEX_ENABLED_FLAG ;
2744
2745
2746
2747
2748
2749 END OE_Order_Cache;