[Home] [Help]
PACKAGE BODY: APPS.OE_ID_TO_VALUE
Source
1 PACKAGE BODY OE_Id_To_Value AS
2 /* $Header: OEXSIDVB.pls 120.8.12010000.2 2008/08/04 15:01:34 amallik ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Id_To_Value';
7
8 -- Procedure Get_Attr_Tbl.
9 --
10 -- Used by generator to avoid overriding or duplicating existing
11 -- Id_To_Value functions.
12 --
13 -- DO NOT REMOVE
14
15 PROCEDURE Get_Attr_Tbl
16 IS
17 I NUMBER:=0;
18 --
19 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
20 --
21 BEGIN
22
23 FND_API.g_attr_tbl.DELETE;
24
25 -- START GEN attributes
26
27 -- Generator will append new attributes before end generate comment.
28 I := I + 1;
29 FND_API.g_attr_tbl(I).name := 'conversion_type';
30 I := I + 1;
31 FND_API.g_attr_tbl(I).name := 'deliver_to_contact';
32 I := I + 1;
33 FND_API.g_attr_tbl(I).name := 'deliver_to_org';
34 I := I + 1;
35 FND_API.g_attr_tbl(I).name := 'demand_class';
36 I := I + 1;
37 FND_API.g_attr_tbl(I).name := 'fob_point';
38 I := I + 1;
39 FND_API.g_attr_tbl(I).name := 'freight_carrier';
40 I := I + 1;
41 FND_API.g_attr_tbl(I).name := 'header';
42 I := I + 1;
43 FND_API.g_attr_tbl(I).name := 'intermed_ship_to_contact';
44 I := I + 1;
45 FND_API.g_attr_tbl(I).name := 'intermed_ship_to_org';
46 I := I + 1;
47 FND_API.g_attr_tbl(I).name := 'invoice_to_contact';
48 I := I + 1;
49 FND_API.g_attr_tbl(I).name := 'invoice_to_org';
50 I := I + 1;
51 FND_API.g_attr_tbl(I).name := 'order_source';
52 I := I + 1;
53 FND_API.g_attr_tbl(I).name := 'order_type';
54 I := I + 1;
55 FND_API.g_attr_tbl(I).name := 'org';
56 I := I + 1;
57 FND_API.g_attr_tbl(I).name := 'source_document_type';
58 I := I + 1;
59 FND_API.g_attr_tbl(I).name := 'payment_term';
60 I := I + 1;
61 FND_API.g_attr_tbl(I).name := 'shipment_priority';
62 I := I + 1;
63 FND_API.g_attr_tbl(I).name := 'shipping_method';
64 I := I + 1;
65 FND_API.g_attr_tbl(I).name := 'ship_from_org';
66 I := I + 1;
67 FND_API.g_attr_tbl(I).name := 'ship_to_contact';
68 I := I + 1;
69 FND_API.g_attr_tbl(I).name := 'ship_to_org';
70 I := I + 1;
71 FND_API.g_attr_tbl(I).name := 'sold_to_contact';
72 I := I + 1;
73 FND_API.g_attr_tbl(I).name := 'sold_to_org';
74 I := I + 1;
75 FND_API.g_attr_tbl(I).name := 'tax_exempt';
76 I := I + 1;
77 FND_API.g_attr_tbl(I).name := 'tax_exempt_reason';
78 I := I + 1;
79 FND_API.g_attr_tbl(I).name := 'tax_point';
80 I := I + 1;
81 FND_API.g_attr_tbl(I).name := 'transactional_curr';
82 I := I + 1;
83 FND_API.g_attr_tbl(I).name := 'automatic';
84 I := I + 1;
85 FND_API.g_attr_tbl(I).name := 'line';
86 I := I + 1;
87 FND_API.g_attr_tbl(I).name := 'price_adjustment';
88 I := I + 1;
89 FND_API.g_attr_tbl(I).name := 'dw_update_advice';
90 I := I + 1;
91 FND_API.g_attr_tbl(I).name := 'quota';
92 I := I + 1;
93 FND_API.g_attr_tbl(I).name := 'sales_credit';
94 I := I + 1;
95 FND_API.g_attr_tbl(I).name := 'component';
96 I := I + 1;
97 FND_API.g_attr_tbl(I).name := 'component_sequence';
98 I := I + 1;
99 FND_API.g_attr_tbl(I).name := 'top_model_line';
100 I := I + 1;
101 FND_API.g_attr_tbl(I).name := 'customer_dock';
102 I := I + 1;
103 FND_API.g_attr_tbl(I).name := 'customer_trx_line';
104 I := I + 1;
105 FND_API.g_attr_tbl(I).name := 'demand_bucket_type';
106 I := I + 1;
107 FND_API.g_attr_tbl(I).name := 'dep_plan_required';
108 I := I + 1;
109 FND_API.g_attr_tbl(I).name := 'invoice_complete';
110 I := I + 1;
111 FND_API.g_attr_tbl(I).name := 'item_type';
112 I := I + 1;
113 FND_API.g_attr_tbl(I).name := 'line_category';
114 I := I + 1;
115 FND_API.g_attr_tbl(I).name := 'line_type';
116 I := I + 1;
117 FND_API.g_attr_tbl(I).name := 'link_to_line';
118 I := I + 1;
119 FND_API.g_attr_tbl(I).name := 'option';
120 I := I + 1;
121 FND_API.g_attr_tbl(I).name := 'project';
122 I := I + 1;
123 FND_API.g_attr_tbl(I).name := 'reference_header';
124 I := I + 1;
125 FND_API.g_attr_tbl(I).name := 'reference_line';
126 I := I + 1;
127 FND_API.g_attr_tbl(I).name := 'rla_schedule_type';
128 I := I + 1;
129 FND_API.g_attr_tbl(I).name := 'task';
130 I := I + 1;
131 FND_API.g_attr_tbl(I).name := 'tax';
132 I := I + 1;
133 FND_API.g_attr_tbl(I).name := 'visible_demand';
134
135 /*Pricing Contract */
136
137 I := I + 1;
138 FND_API.g_attr_tbl(I).name := 'agreement';
139 I := I + 1;
140 FND_API.g_attr_tbl(I).name := 'discount';
141 I := I + 1;
142 FND_API.g_attr_tbl(I).name := 'price_list';
143 I := I + 1;
144 FND_API.g_attr_tbl(I).name := 'accounting_rule';
145 I := I + 1;
146 FND_API.g_attr_tbl(I).name := 'agreement_contact';
147 I := I + 1;
148 FND_API.g_attr_tbl(I).name := 'agreement_type';
149 I := I + 1;
150 FND_API.g_attr_tbl(I).name := 'calculate_price_flag';
151 I := I + 1;
152 FND_API.g_attr_tbl(I).name := 'customer';
153 I := I + 1;
154 FND_API.g_attr_tbl(I).name := 'freight_terms';
155 I := I + 1;
156 FND_API.g_attr_tbl(I).name := 'invoice_contact';
157 I := I + 1;
158 FND_API.g_attr_tbl(I).name := 'invoice_to_site_use';
159 I := I + 1;
160 FND_API.g_attr_tbl(I).name := 'invoicing_rule';
161 I := I + 1;
162 FND_API.g_attr_tbl(I).name := 'override_arule';
163 I := I + 1;
164 FND_API.g_attr_tbl(I).name := 'override_irule';
165 I := I + 1;
166 FND_API.g_attr_tbl(I).name := 'revision_reason';
167 I := I + 1;
168 FND_API.g_attr_tbl(I).name := 'salesrep';
169 I := I + 1;
170 FND_API.g_attr_tbl(I).name := 'sales_credit_type';
171 I := I + 1;
172 FND_API.g_attr_tbl(I).name := 'ship_method';
173 I := I + 1;
174 FND_API.g_attr_tbl(I).name := 'term';
175 I := I + 1;
176 FND_API.g_attr_tbl(I).name := 'currency';
177 I := I + 1;
178 FND_API.g_attr_tbl(I).name := 'secondary_price_list';
179 I := I + 1;
180 FND_API.g_attr_tbl(I).name := 'terms';
181 I := I + 1;
182 FND_API.g_attr_tbl(I).name := 'automatic_discount';
183 I := I + 1;
184 FND_API.g_attr_tbl(I).name := 'discount_lines';
185 I := I + 1;
186 FND_API.g_attr_tbl(I).name := 'discount_type';
187 I := I + 1;
188 FND_API.g_attr_tbl(I).name := 'manual_discount';
189 I := I + 1;
190 FND_API.g_attr_tbl(I).name := 'override_allowed';
191 I := I + 1;
192 FND_API.g_attr_tbl(I).name := 'prorate';
193 I := I + 1;
194 FND_API.g_attr_tbl(I).name := 'inventory_item';
195 I := I + 1;
196 FND_API.g_attr_tbl(I).name := 'method';
197 I := I + 1;
198 FND_API.g_attr_tbl(I).name := 'price_list_line';
199 I := I + 1;
200 FND_API.g_attr_tbl(I).name := 'pricing_rule';
201 I := I + 1;
202 FND_API.g_attr_tbl(I).name := 'reprice';
203 I := I + 1;
204 FND_API.g_attr_tbl(I).name := 'unit';
205 I := I + 1;
206 FND_API.g_attr_tbl(I).name := 'customer_class';
207 I := I + 1;
208 FND_API.g_attr_tbl(I).name := 'discount_line';
209 I := I + 1;
210 FND_API.g_attr_tbl(I).name := 'discount_customer';
211 I := I + 1;
212 FND_API.g_attr_tbl(I).name := 'site_use';
213 I := I + 1;
214 FND_API.g_attr_tbl(I).name := 'entity';
215 I := I + 1;
216 FND_API.g_attr_tbl(I).name := 'method_type';
217 I := I + 1;
218 FND_API.g_attr_tbl(I).name := 'lot_serial';
219 I := I + 1;
220 FND_API.g_attr_tbl(I).name := 'appear_on_ack';
221 I := I + 1;
222 FND_API.g_attr_tbl(I).name := 'appear_on_invoice';
223 I := I + 1;
224 FND_API.g_attr_tbl(I).name := 'charge';
225 I := I + 1;
226 FND_API.g_attr_tbl(I).name := 'charge_type';
227 I := I + 1;
228 FND_API.g_attr_tbl(I).name := 'cost_or_charge';
229 I := I + 1;
230 FND_API.g_attr_tbl(I).name := 'delivery';
231 I := I + 1;
232 FND_API.g_attr_tbl(I).name := 'departure';
233 I := I + 1;
234 FND_API.g_attr_tbl(I).name := 'estimated';
235 I := I + 1;
236 FND_API.g_attr_tbl(I).name := 'invoiced';
237 I := I + 1;
238 FND_API.g_attr_tbl(I).name := 'parent_charge';
239 I := I + 1;
240 FND_API.g_attr_tbl(I).name := 'returnable';
241 I := I + 1;
242 FND_API.g_attr_tbl(I).name := 'tax_group';
243 -- END GEN attributes
244
245 END Get_Attr_Tbl;
246
247 -- Prototypes for Id_To_Value functions.
248
249 -- START GEN Id_To_Value
250
251 -- Generator will append new prototypes before end generate comment.
252
253
254 FUNCTION Accounting_Rule
255 ( p_accounting_rule_id IN NUMBER
256 ) RETURN VARCHAR2
257 IS
258 l_accounting_rule VARCHAR2(240) := NULL;
259 --
260 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
261 --
262 BEGIN
263
264 IF p_accounting_rule_id IS NOT NULL THEN
265
266 SELECT NAME
267 INTO l_accounting_rule
268 FROM OE_RA_RULES_V
269 WHERE RULE_ID = p_accounting_rule_id;
270
271 END IF;
272
273 RETURN l_accounting_rule;
274
275 EXCEPTION
276
277 WHEN NO_DATA_FOUND THEN
278
279 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
280 THEN
281
282 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
283 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','accounting_rule');
284 OE_MSG_PUB.Add;
285
286 END IF;
287
288 RETURN NULL;
289
290 WHEN OTHERS THEN
291
292 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
293 THEN
294 OE_MSG_PUB.Add_Exc_Msg
295 ( G_PKG_NAME
296 , 'Accounting_Rule'
297 );
298 END IF;
299
300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
301
302 END Accounting_Rule;
303
304
305 FUNCTION Calculate_Price_Flag
306 ( p_calculate_price_flag IN VARCHAR2
307 ) RETURN VARCHAR2
308 IS
309 l_calculate_price_flag VARCHAR2(240) := NULL;
310 l_lookup_type VARCHAR2(80) :='CALCULATE_PRICE_FLAG';
311 --
312 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
313 --
314 BEGIN
315
316 IF l_debug_level > 0 THEN
317 oe_debug_pub.add( 'IN GET VALUES ' || P_CALCULATE_PRICE_FLAG ) ;
318 END IF;
319 IF p_calculate_price_flag IS NOT NULL THEN
320
321 SELECT MEANING
322 INTO l_calculate_price_flag
323 FROM OE_LOOKUPS
324 WHERE LOOKUP_CODE = p_calculate_price_flag
325 AND LOOKUP_TYPE = l_lookup_type;
326
327
328 END IF;
329
330 IF l_debug_level > 0 THEN
331 oe_debug_pub.add( 'IN GET VALUES ' || L_CALCULATE_PRICE_FLAG ) ;
332 END IF;
333 RETURN l_calculate_price_flag;
334
335 EXCEPTION
336
337 WHEN NO_DATA_FOUND THEN
338
339 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
340 THEN
341
342 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
343 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','calculate_price_flag');
344 OE_MSG_PUB.Add;
345
346 END IF;
347
348 RETURN NULL;
349
350 WHEN OTHERS THEN
351
352 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
353 THEN
354 OE_MSG_PUB.Add_Exc_Msg
355 ( G_PKG_NAME
356 , 'calculate_price_flag'
357 );
358 END IF;
359
360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
361
362 END Calculate_Price_Flag;
363
364
365
366
367
368 FUNCTION Agreement
369 ( p_agreement_id IN NUMBER
370 ) RETURN VARCHAR2
371 IS
372 l_agreement VARCHAR2(240) := NULL;
373 --
374 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
375 --
376 BEGIN
377
378 -- Appended name with revision for Bug 2249065
379
380 IF p_agreement_id IS NOT NULL THEN
381
382 SELECT NAME ||' : '||revision
383 INTO l_agreement
384 FROM OE_AGREEMENTS_VL
385 WHERE AGREEMENT_ID = p_agreement_id;
386
387 -- NULL;
388
389 END IF;
390
391 RETURN l_agreement;
392
393 EXCEPTION
394
395 WHEN NO_DATA_FOUND THEN
396
397 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
398 THEN
399
400 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
401 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','agreement');
402 OE_MSG_PUB.Add;
403
404 END IF;
405
406 RETURN NULL;
407
408 WHEN OTHERS THEN
409
410 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
411 THEN
412 OE_MSG_PUB.Add_Exc_Msg
413 ( G_PKG_NAME
414 , 'Agreement'
415 );
416 END IF;
417
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419
420 END Agreement;
421
422 FUNCTION Conversion_Type
423 ( p_conversion_type_code IN VARCHAR2
424 ) RETURN VARCHAR2
425 IS
426 l_conversion_type VARCHAR2(240) := NULL;
427 --
428 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
429 --
430 BEGIN
431
432 IF p_conversion_type_code IS NOT NULL THEN
433
434 SELECT USER_CONVERSION_TYPE
435 INTO l_conversion_type
436 FROM OE_GL_DAILY_CONVERSION_TYPES_V
437 WHERE CONVERSION_TYPE = p_conversion_type_code;
438
439 END IF;
440
441 RETURN l_conversion_type;
442
443 EXCEPTION
444
445 WHEN NO_DATA_FOUND THEN
446
447 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
448 THEN
449
450 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
451 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','conversion_type');
452 OE_MSG_PUB.Add;
453
454 END IF;
455
456 RETURN NULL;
457
458 WHEN OTHERS THEN
459
460 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
461 THEN
462 OE_MSG_PUB.Add_Exc_Msg
463 ( G_PKG_NAME
464 , 'Conversion_Type'
465 );
466 END IF;
467
468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
469
470 END Conversion_Type;
471
472 FUNCTION Deliver_To_Contact
473 ( p_deliver_to_contact_id IN NUMBER
474 ) RETURN VARCHAR2
475 IS
476 l_deliver_to_contact VARCHAR2(240) := NULL;
477 --
478 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
479 --
480 BEGIN
481
482 IF p_deliver_to_contact_id IS NOT NULL THEN
483
484 SELECT NAME
485 INTO l_deliver_to_contact
486 FROM OE_CONTACTS_V
487 WHERE CONTACT_ID = p_deliver_to_contact_id;
488
489 END IF;
490
491 RETURN l_deliver_to_contact;
492
493 EXCEPTION
494
495 WHEN NO_DATA_FOUND THEN
496
497 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
498 THEN
499
500 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
501 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','deliver_to_contact');
502 OE_MSG_PUB.Add;
503
504 END IF;
505
506 RETURN NULL;
507
508 WHEN OTHERS THEN
509
510 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
511 THEN
512 OE_MSG_PUB.Add_Exc_Msg
513 ( G_PKG_NAME
514 , 'Deliver_To_Contact'
515 );
516 END IF;
517
518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
519
520 END Deliver_To_Contact;
521
522 FUNCTION Inventory_Org
523 ( p_inventory_org_id IN NUMBER
524 ) RETURN VARCHAR2
525 IS
526 l_inventory_org VARCHAR2(240) := NULL;
527 --
528 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
529 --
530 BEGIN
531
532 IF p_inventory_org_id IS NOT NULL THEN
533
534 SELECT ORGANIZATION_NAME
535 INTO l_inventory_org
536 FROM ORG_ORGANIZATION_DEFINITIONS
537 WHERE ORGANIZATION_ID = p_inventory_org_id;
538
539 END IF;
540
541 RETURN l_inventory_org;
542
543 EXCEPTION
544
545 WHEN NO_DATA_FOUND THEN
546
547 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
548 THEN
549
550 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
551 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','inventory_org');
552 OE_MSG_PUB.Add;
553
554 END IF;
555
556 RETURN NULL;
557
558 WHEN OTHERS THEN
559
560 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
561 THEN
562 OE_MSG_PUB.Add_Exc_Msg
563 ( G_PKG_NAME
564 , 'inventory_org'
565 );
566 END IF;
567
568 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
569
570 END inventory_org;
571
572 PROCEDURE Deliver_To_Org
573 ( p_deliver_to_org_id IN NUMBER
574 , x_deliver_to_address1 OUT NOCOPY VARCHAR2
575
576 , x_deliver_to_address2 OUT NOCOPY VARCHAR2
577
578 , x_deliver_to_address3 OUT NOCOPY VARCHAR2
579
580 , x_deliver_to_address4 OUT NOCOPY VARCHAR2
581
582 , x_deliver_to_location OUT NOCOPY VARCHAR2
583
584 , x_deliver_to_org OUT NOCOPY VARCHAR2
585
586 , x_deliver_to_city OUT NOCOPY VARCHAR2
587
588 , x_deliver_to_state OUT NOCOPY VARCHAR2
589
590 , x_deliver_to_postal_code OUT NOCOPY VARCHAR2
591
592 , x_deliver_to_country OUT NOCOPY VARCHAR2
593
594 )
595 IS
596 --
597 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
598 --
599 BEGIN
600
601 IF p_deliver_to_org_id is NOT NULL THEN
602
603 SELECT L.NAME
604 , L.LOCATION_CODE
605 , L.ADDRESS_LINE_1
606 , L.ADDRESS_LINE_2
607 , L.ADDRESS_LINE_3
608 , L.ADDRESS_LINE_4
609 , L.TOWN_OR_CITY
610 , L.STATE
611 , L.POSTAL_CODE
612 , L.COUNTRY
613 INTO x_deliver_to_org
614 , x_deliver_to_location
615 , x_deliver_to_address1
616 , x_deliver_to_address2
617 , x_deliver_to_address3
618 , x_deliver_to_address4
619 , x_deliver_to_city
620 , x_deliver_to_state
621 , x_deliver_to_postal_code
622 , x_deliver_to_country
623 FROM OE_DELIVER_TO_ORGS_V L
624 WHERE L.ORGANIZATION_ID = p_deliver_to_org_id;
625
626 ELSE
627
628 x_deliver_to_org := NULL ;
629 x_deliver_to_location := NULL ;
630 x_deliver_to_address1 := NULL ;
631 x_deliver_to_address2 := NULL ;
632 x_deliver_to_address3 := NULL ;
633 x_deliver_to_address4 := NULL ;
634 x_deliver_to_city := NULL ;
635 x_deliver_to_state := NULL ;
636 x_deliver_to_postal_code := NULL ;
637 x_deliver_to_country := NULL ;
638
639 END IF;
640
641
642 EXCEPTION
643
644 WHEN NO_DATA_FOUND THEN
645
646 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
647 THEN
648
649 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
650 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','deliver_to_org');
651 OE_MSG_PUB.Add;
652
653 END IF;
654
655
656 WHEN OTHERS THEN
657
658 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
659 THEN
660 OE_MSG_PUB.Add_Exc_Msg
661 ( G_PKG_NAME
662 , 'Deliver_To_Org'
663 );
664 END IF;
665
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667
668 END Deliver_To_Org;
669
670 FUNCTION Fob_Point
671 ( p_fob_point_code IN VARCHAR2
672 ) RETURN VARCHAR2
673 IS
674 l_fob_point VARCHAR2(240) := NULL;
675 l_lookup_type VARCHAR2(80) :='FOB';
676 --
677 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
678 --
679 BEGIN
680
681 IF p_fob_point_code IS NOT NULL THEN
682
683 SELECT MEANING
684 INTO l_fob_point
685 FROM OE_AR_LOOKUPS_V
686 WHERE LOOKUP_CODE = p_fob_point_code
687 AND LOOKUP_TYPE = l_lookup_type;
688
689 END IF;
690
691 RETURN l_fob_point;
692
693 EXCEPTION
694
695 WHEN NO_DATA_FOUND THEN
696
697 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
698 THEN
699
700 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
701 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','fob_point');
702 OE_MSG_PUB.Add;
703
704 END IF;
705
706 RETURN NULL;
707
708 WHEN OTHERS THEN
709
710 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
711 THEN
712 OE_MSG_PUB.Add_Exc_Msg
713 ( G_PKG_NAME
714 , 'Fob_Point'
715 );
716 END IF;
717
718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
719
720 END Fob_Point;
721
722 FUNCTION Freight_Terms
723 ( p_freight_terms_code IN VARCHAR2
724 ) RETURN VARCHAR2
725 IS
726 l_freight_terms VARCHAR2(240) := NULL;
727 l_lookup_type VARCHAR2(80) :='FREIGHT_TERMS';
728 --
729 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
730 --
731 BEGIN
732
733 IF p_freight_terms_code IS NOT NULL THEN
734
735 SELECT MEANING
736 INTO l_freight_terms
737 FROM OE_LOOKUPS
738 WHERE LOOKUP_CODE = p_freight_terms_code
739 AND LOOKUP_TYPE = l_lookup_type;
740
741 END IF;
742
743 RETURN l_freight_terms;
744
745 EXCEPTION
746
747 WHEN NO_DATA_FOUND THEN
748
749 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
750 THEN
751
752 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
753 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','freight_terms');
754 OE_MSG_PUB.Add;
755
756 END IF;
757
758 RETURN NULL;
759
760 WHEN OTHERS THEN
761
762 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
763 THEN
764 OE_MSG_PUB.Add_Exc_Msg
765 ( G_PKG_NAME
766 , 'Freight_Terms'
767 );
768 END IF;
769
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771
772 END Freight_Terms;
773
774 -- Intermediate Ship To
775
776 FUNCTION Intermed_Ship_To_Contact
777 ( p_intermed_ship_to_contact_id IN NUMBER
778 ) RETURN VARCHAR2
779 IS
780 l_intermed_ship_to_contact VARCHAR2(240) := NULL;
781 --
782 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
783 --
784 BEGIN
785
786 IF p_intermed_ship_to_contact_id IS NOT NULL THEN
787
788 SELECT NAME
789 INTO l_intermed_ship_to_contact
790 FROM OE_CONTACTS_V
791 WHERE CONTACT_ID = p_intermed_ship_to_contact_id;
792
793 END IF;
794
795 RETURN l_intermed_ship_to_contact;
796
797 EXCEPTION
798
799 WHEN NO_DATA_FOUND THEN
800
801 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
802 THEN
803
804 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
805 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','intermed_ship_to_contact');
806 OE_MSG_PUB.Add;
807
808 END IF;
809
810 WHEN OTHERS THEN
811
812 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
813 THEN
814 OE_MSG_PUB.Add_Exc_Msg
815 ( G_PKG_NAME
816 , 'Intermed_Ship_To_Contact'
817 );
818 END IF;
819
820 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
821
822 END Intermed_Ship_To_Contact;
823
824 PROCEDURE Intermed_Ship_To_Org
825 ( p_intermed_ship_to_org_id IN NUMBER
826 , x_intermed_ship_to_address1 OUT NOCOPY VARCHAR2
827
828 , x_intermed_ship_to_address2 OUT NOCOPY VARCHAR2
829
830 , x_intermed_ship_to_address3 OUT NOCOPY VARCHAR2
831
832 , x_intermed_ship_to_address4 OUT NOCOPY VARCHAR2
833
834 , x_intermed_ship_to_location OUT NOCOPY VARCHAR2
835
836 , x_intermed_ship_to_org OUT NOCOPY VARCHAR2
837
838 , x_intermed_ship_to_city OUT NOCOPY VARCHAR2
839
840 , x_intermed_ship_to_state OUT NOCOPY VARCHAR2
841
842 , x_intermed_ship_to_postal_code OUT NOCOPY VARCHAR2
843
844 , x_intermed_ship_to_country OUT NOCOPY VARCHAR2
845
846 )
847 IS
848 --
849 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
850 --
851 BEGIN
852
853 IF p_intermed_ship_to_org_id is NOT NULL THEN
854
855 SELECT L.NAME
856 , L.LOCATION_CODE
857 , L.ADDRESS_LINE_1
858 , L.ADDRESS_LINE_2
859 , L.ADDRESS_LINE_3
860 , L.ADDRESS_LINE_4
861 , L.TOWN_OR_CITY
862 , L.STATE
863 , L.POSTAL_CODE
864 , L.COUNTRY
865 INTO x_intermed_ship_to_org
866 , x_intermed_ship_to_location
867 , x_intermed_ship_to_address1
868 , x_intermed_ship_to_address2
869 , x_intermed_ship_to_address3
870 , x_intermed_ship_to_address4
871 , x_intermed_ship_to_city
872 , x_intermed_ship_to_state
873 , x_intermed_ship_to_postal_code
874 , x_intermed_ship_to_country
875 FROM OE_SHIP_TO_ORGS_V L
876 WHERE L.ORGANIZATION_ID = p_intermed_ship_to_org_id;
877
878 ELSE
879
880 x_intermed_ship_to_org := NULL ;
881 x_intermed_ship_to_location := NULL ;
882 x_intermed_ship_to_address1 := NULL ;
883 x_intermed_ship_to_address2 := NULL ;
884 x_intermed_ship_to_address3 := NULL ;
885 x_intermed_ship_to_address4 := NULL ;
886 x_intermed_ship_to_city := NULL ;
887 x_intermed_ship_to_state := NULL ;
888 x_intermed_ship_to_postal_code := NULL ;
889 x_intermed_ship_to_country := NULL ;
890
891 END IF;
892
893
894 EXCEPTION
895
896 WHEN NO_DATA_FOUND THEN
897
898 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
899 THEN
900
901 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
902 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','intermed_ship_to_org');
903 OE_MSG_PUB.Add;
904
905 END IF;
906
907
908 WHEN OTHERS THEN
909
910 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
911 THEN
912 OE_MSG_PUB.Add_Exc_Msg
913 ( G_PKG_NAME
914 , 'Intermed_Ship_To_Org'
915 );
916 END IF;
917
918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
919
920 END Intermed_Ship_To_Org;
921
922 FUNCTION Invoice_To_Contact
923 ( p_invoice_to_contact_id IN NUMBER
924 ) RETURN VARCHAR2
925 IS
926 l_invoice_to_contact VARCHAR2(240) := NULL;
927 --
928 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
929 --
930 BEGIN
931
932 IF p_invoice_to_contact_id IS NOT NULL THEN
933
934 SELECT NAME
935 INTO l_invoice_to_contact
936 FROM OE_CONTACTS_V
937 WHERE CONTACT_ID = p_invoice_to_contact_id;
938
939 END IF;
940
941 RETURN l_invoice_to_contact;
942
943 EXCEPTION
944
945 WHEN NO_DATA_FOUND THEN
946
947 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
948 THEN
949
950 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
951 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoice_to_contact');
952 OE_MSG_PUB.Add;
953
954 END IF;
955
956 RETURN NULL;
957
958 WHEN OTHERS THEN
959
960 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
961 THEN
962 OE_MSG_PUB.Add_Exc_Msg
963 ( G_PKG_NAME
964 , 'Invoice_To_Contact'
965 );
966 END IF;
967
968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
969
970 END Invoice_To_Contact;
971
972 PROCEDURE Invoice_To_Org
973 ( p_invoice_to_org_id IN NUMBER
974 , x_invoice_to_address1 OUT NOCOPY VARCHAR2
975
976 , x_invoice_to_address2 OUT NOCOPY VARCHAR2
977
978 , x_invoice_to_address3 OUT NOCOPY VARCHAR2
979
980 , x_invoice_to_address4 OUT NOCOPY VARCHAR2
981
982 , x_invoice_to_location OUT NOCOPY VARCHAR2
983
984 , x_invoice_to_org OUT NOCOPY VARCHAR2
985
986 , x_invoice_to_city OUT NOCOPY VARCHAR2
987
988 , x_invoice_to_state OUT NOCOPY VARCHAR2
989
990 , x_invoice_to_postal_code OUT NOCOPY VARCHAR2
991
992 , x_invoice_to_country OUT NOCOPY VARCHAR2
993
994 )
995 IS
996 --
997 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
998 --
999 BEGIN
1000
1001 IF p_invoice_to_org_id is NOT NULL THEN
1002
1003 SELECT L.NAME
1004 , L.LOCATION_CODE
1005 , L.ADDRESS_LINE_1
1006 , L.ADDRESS_LINE_2
1007 , L.ADDRESS_LINE_3
1008 , L.ADDRESS_LINE_4
1009 , L.TOWN_OR_CITY
1010 , L.STATE
1011 , L.POSTAL_CODE
1012 , L.COUNTRY
1013 INTO x_invoice_to_org
1014 , x_invoice_to_location
1015 , x_invoice_to_address1
1016 , x_invoice_to_address2
1017 , x_invoice_to_address3
1018 , x_invoice_to_address4
1019 , x_invoice_to_city
1020 , x_invoice_to_state
1021 , x_invoice_to_postal_code
1022 , x_invoice_to_country
1023 FROM OE_INVOICE_TO_ORGS_V L
1024 WHERE L.ORGANIZATION_ID = p_invoice_to_org_id;
1025
1026 ELSE
1027
1028 x_invoice_to_org := NULL ;
1029 x_invoice_to_location := NULL ;
1030 x_invoice_to_address1 := NULL ;
1031 x_invoice_to_address2 := NULL ;
1032 x_invoice_to_address3 := NULL ;
1033 x_invoice_to_address4 := NULL ;
1034 x_invoice_to_city := NULL;
1035 x_invoice_to_state := NULL;
1036 x_invoice_to_postal_code := NULL;
1037 x_invoice_to_country := NULL;
1038
1039 END IF;
1040
1041
1042 EXCEPTION
1043
1044 WHEN NO_DATA_FOUND THEN
1045
1046 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1047 THEN
1048
1049 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1050 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoice_to_org');
1051 OE_MSG_PUB.Add;
1052
1053 END IF;
1054
1055
1056 WHEN OTHERS THEN
1057
1058 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1059 THEN
1060 OE_MSG_PUB.Add_Exc_Msg
1061 ( G_PKG_NAME
1062 , 'Invoice_To_Org'
1063 );
1064 END IF;
1065
1066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1067
1068 END Invoice_To_Org;
1069
1070 FUNCTION Invoicing_Rule
1071 ( p_invoicing_rule_id IN NUMBER
1072 ) RETURN VARCHAR2
1073 IS
1074 l_invoicing_rule VARCHAR2(240) := NULL;
1075 --
1076 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1077 --
1078 BEGIN
1079
1080 IF p_invoicing_rule_id IS NOT NULL THEN
1081
1082 SELECT NAME
1083 INTO l_invoicing_rule
1084 FROM OE_RA_RULES_V
1085 WHERE RULE_ID = p_invoicing_rule_id;
1086
1087 END IF;
1088
1089 RETURN l_invoicing_rule;
1090
1091 EXCEPTION
1092
1093 WHEN NO_DATA_FOUND THEN
1094
1095 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1096 THEN
1097
1098 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1099 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoicing_rule');
1100 OE_MSG_PUB.Add;
1101
1102 END IF;
1103
1104 RETURN NULL;
1105
1106 WHEN OTHERS THEN
1107
1108 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1109 THEN
1110 OE_MSG_PUB.Add_Exc_Msg
1111 ( G_PKG_NAME
1112 , 'Invoicing_Rule'
1113 );
1114 END IF;
1115
1116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1117
1118 END Invoicing_Rule;
1119
1120 FUNCTION Order_Source
1121 ( p_order_source_id IN NUMBER
1122 ) RETURN VARCHAR2
1123 IS
1124 l_order_source VARCHAR2(240) := NULL;
1125 --
1126 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1127 --
1128 BEGIN
1129
1130 IF p_order_source_id IS NOT NULL THEN
1131
1132 SELECT NAME
1133 INTO l_order_source
1134 FROM OE_ORDER_SOURCES
1135 WHERE ORDER_SOURCE_ID = p_order_source_id;
1136
1137 END IF;
1138
1139 RETURN l_order_source;
1140
1141 EXCEPTION
1142
1143 WHEN NO_DATA_FOUND THEN
1144
1145 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1146 THEN
1147
1148 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1149 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','order_source');
1150 OE_MSG_PUB.Add;
1151
1152 END IF;
1153
1154 RETURN NULL;
1155
1156 WHEN OTHERS THEN
1157
1158 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1159 THEN
1160 OE_MSG_PUB.Add_Exc_Msg
1161 ( G_PKG_NAME
1162 , 'Order_Source'
1163 );
1164 END IF;
1165
1166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1167
1168 END Order_Source;
1169
1170 FUNCTION Order_Type
1171 ( p_order_type_id IN NUMBER
1172 ) RETURN VARCHAR2
1173 IS
1174 l_order_type VARCHAR2(240) := NULL;
1175 --
1176 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1177 --
1178 BEGIN
1179
1180 IF p_order_type_id IS NOT NULL THEN
1181
1182 SELECT NAME
1183 INTO l_order_type
1184 FROM OE_ORDER_TYPES_v
1185 WHERE ORDER_TYPE_ID = p_order_type_id;
1186
1187 END IF;
1188
1189 RETURN l_order_type;
1190
1191 EXCEPTION
1192
1193 WHEN NO_DATA_FOUND THEN
1194
1195 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1196 THEN
1197
1198 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1199 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','order_type');
1200 OE_MSG_PUB.Add;
1201
1202 END IF;
1203
1204 RETURN NULL;
1205
1206 WHEN OTHERS THEN
1207
1208 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1209 THEN
1210 OE_MSG_PUB.Add_Exc_Msg
1211 ( G_PKG_NAME
1212 , 'Order_Type'
1213 );
1214 END IF;
1215
1216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1217
1218 END Order_Type;
1219
1220 FUNCTION Payment_Term
1221 ( p_payment_term_id IN NUMBER
1222 ) RETURN VARCHAR2
1223 IS
1224 l_payment_term VARCHAR2(240) := NULL;
1225 --
1226 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1227 --
1228 BEGIN
1229
1230 IF p_payment_term_id IS NOT NULL THEN
1231
1232 SELECT NAME
1233 INTO l_payment_term
1234 FROM OE_RA_TERMS_V
1235 WHERE TERM_ID = p_payment_term_id;
1236
1237 END IF;
1238
1239 RETURN l_payment_term;
1240
1241 EXCEPTION
1242
1243 WHEN NO_DATA_FOUND THEN
1244
1245 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1246 THEN
1247
1248 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1249 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','payment_term');
1250 OE_MSG_PUB.Add;
1251
1252 END IF;
1253
1254 RETURN NULL;
1255
1256 WHEN OTHERS THEN
1257
1258 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1259 THEN
1260 OE_MSG_PUB.Add_Exc_Msg
1261 ( G_PKG_NAME
1262 , 'Payment_Term'
1263 );
1264 END IF;
1265
1266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1267
1268 END Payment_Term;
1269
1270 FUNCTION Price_List
1271 ( p_price_list_id IN NUMBER
1272 ) RETURN VARCHAR2
1273 IS
1274 l_price_list VARCHAR2(240) := NULL;
1275 --
1276 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1277 --
1278 BEGIN
1279
1280 IF p_price_list_id IS NOT NULL THEN
1281
1282 SELECT NAME
1283 INTO l_price_list
1284 FROM qp_list_headers_vl
1285 WHERE list_header_id = p_price_list_id and
1286 list_type_code in ('PRL', 'AGR');
1287
1288 END IF;
1289
1290 RETURN l_price_list;
1291
1292 EXCEPTION
1293
1294 WHEN NO_DATA_FOUND THEN
1295
1296 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1297 THEN
1298
1299 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1300 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','price_list');
1301 OE_MSG_PUB.Add;
1302
1303 END IF;
1304
1305 RETURN NULL;
1306
1307 WHEN OTHERS THEN
1308
1309 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1310 THEN
1311 OE_MSG_PUB.Add_Exc_Msg
1312 ( G_PKG_NAME
1313 , 'Price_List'
1314 );
1315 END IF;
1316
1317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1318
1319 END Price_List;
1320
1321 FUNCTION New_Modifier_List
1322 ( p_new_modifier_list_id IN NUMBER
1323 ) RETURN VARCHAR2
1324 IS
1325 l_new_modifier_list VARCHAR2(240) := NULL;
1326 --
1327 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1328 --
1329 BEGIN
1330
1331 IF p_new_modifier_list_id IS NOT NULL THEN
1332
1333 SELECT NAME
1334 INTO l_new_modifier_list
1335 FROM qp_list_headers_vl
1336 WHERE list_header_id = p_new_modifier_list_id ;
1337
1338 END IF;
1339
1340 RETURN l_new_modifier_list;
1341
1342 EXCEPTION
1343
1344 WHEN NO_DATA_FOUND THEN
1345
1346 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1347 THEN
1348
1349 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1350 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','New_Modifier_list');
1351 OE_MSG_PUB.Add;
1352
1353 END IF;
1354
1355 RETURN NULL;
1356
1357 WHEN OTHERS THEN
1358
1359 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1360 THEN
1361 OE_MSG_PUB.Add_Exc_Msg
1362 ( G_PKG_NAME
1363 , 'New_Modifier_List'
1364 );
1365 END IF;
1366
1367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1368
1369 END New_Modifier_List;
1370 FUNCTION Demand_Class
1371 ( p_demand_class_code IN VARCHAR2
1372 ) RETURN VARCHAR2
1373 IS
1374 l_demand_class VARCHAR2(240) := NULL;
1375 l_lookup_type VARCHAR2(80) :='DEMAND_CLASS';
1376 --
1377 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1378 --
1379 BEGIN
1380
1381 IF p_demand_class_code IS NOT NULL THEN
1382
1383 SELECT MEANING
1384 INTO l_demand_class
1385 FROM OE_FND_COMMON_LOOKUPS_V
1386 WHERE LOOKUP_CODE = p_demand_class_code
1387 AND LOOKUP_TYPE = l_lookup_type;
1388
1389 END IF;
1390
1391 RETURN l_demand_class;
1392
1393 EXCEPTION
1394
1395 WHEN NO_DATA_FOUND THEN
1396
1397 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1398 THEN
1399
1400 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1401 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','demand_class');
1402 OE_MSG_PUB.Add;
1403
1404 END IF;
1405
1406 RETURN NULL;
1407
1408 WHEN OTHERS THEN
1409
1410 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1411 THEN
1412 OE_MSG_PUB.Add_Exc_Msg
1413 ( G_PKG_NAME
1414 , 'demand_class'
1415 );
1416 END IF;
1417
1418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1419
1420 END Demand_Class;
1421
1422 FUNCTION Shipment_Priority
1423 ( p_shipment_priority_code IN VARCHAR2
1424 ) RETURN VARCHAR2
1425 IS
1426 l_shipment_priority VARCHAR2(240) := NULL;
1427 l_lookup_type VARCHAR2(80) :='SHIPMENT_PRIORITY';
1428 --
1429 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1430 --
1431 BEGIN
1432
1433 IF p_shipment_priority_code IS NOT NULL THEN
1434
1435 SELECT MEANING
1436 INTO l_shipment_priority
1437 FROM OE_LOOKUPS
1438 WHERE LOOKUP_CODE = p_shipment_priority_code
1439 AND LOOKUP_TYPE = l_lookup_type;
1440
1441 END IF;
1442
1443 RETURN l_shipment_priority;
1444
1445 EXCEPTION
1446
1447 WHEN NO_DATA_FOUND THEN
1448
1449 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1450 THEN
1451
1452 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1453 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','shipment_priority');
1454 OE_MSG_PUB.Add;
1455
1456 END IF;
1457
1458 RETURN NULL;
1459
1460 WHEN OTHERS THEN
1461
1462 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1463 THEN
1464 OE_MSG_PUB.Add_Exc_Msg
1465 ( G_PKG_NAME
1466 , 'Shipment_Priority'
1467 );
1468 END IF;
1469
1470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1471
1472 END Shipment_Priority;
1473
1474 PROCEDURE Ship_From_Org
1475 ( p_ship_from_org_id IN NUMBER
1476 , x_ship_from_address1 OUT NOCOPY VARCHAR2
1477
1478 , x_ship_from_address2 OUT NOCOPY VARCHAR2
1479
1480 , x_ship_from_address3 OUT NOCOPY VARCHAR2
1481
1482 , x_ship_from_address4 OUT NOCOPY VARCHAR2
1483
1484 , x_ship_from_location OUT NOCOPY VARCHAR2
1485
1486 , x_ship_from_org OUT NOCOPY VARCHAR2
1487
1488 )
1489 IS
1490 --
1491 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1492 --
1493 BEGIN
1494
1495 IF p_ship_from_org_id is NOT NULL THEN
1496
1497 SELECT L.Organization_code
1498 , L.LOCATION_CODE
1499 , L.ADDRESS_LINE_1
1500 , L.ADDRESS_LINE_2
1501 , L.ADDRESS_LINE_3
1502 , DECODE(L.TOWN_OR_CITY,NULL,NULL,L.TOWN_OR_CITY||', ')||
1503 DECODE(L.REGION_1, NULL, NULL, L.REGION_1 || ', ')||
1504 DECODE(L.REGION_2, NULL, NULL, L.REGION_2 || ', ')||
1505 DECODE(L.REGION_3, NULL, NULL, L.REGION_3 || ', ')||
1506 DECODE(L.POSTAL_CODE, NULL, NULL, L.POSTAL_CODE || ', ')||
1507 DECODE(L.COUNTRY, NULL, NULL, L.COUNTRY)
1508 INTO x_ship_from_org
1509 , x_ship_from_location
1510 , x_ship_from_address1
1511 , x_ship_from_address2
1512 , x_ship_from_address3
1513 , x_ship_from_address4
1514 FROM OE_SHIP_FROM_ORGS_V L
1515 WHERE L.ORGANIZATION_ID = p_ship_from_org_id;
1516
1517 ELSE
1518
1519 x_ship_from_org := NULL ;
1520 x_ship_from_location := NULL ;
1521 x_ship_from_address1 := NULL ;
1522 x_ship_from_address2 := NULL ;
1523 x_ship_from_address3 := NULL ;
1524 x_ship_from_address4 := NULL ;
1525
1526 END IF;
1527
1528 EXCEPTION
1529
1530 WHEN NO_DATA_FOUND THEN
1531
1532 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1533 THEN
1534
1535 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1536 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_from_org');
1537 OE_MSG_PUB.Add;
1538
1539 END IF;
1540
1541
1542 WHEN OTHERS THEN
1543
1544 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1545 THEN
1546 OE_MSG_PUB.Add_Exc_Msg
1547 ( G_PKG_NAME
1548 , 'Ship_From_Org'
1549 );
1550 END IF;
1551
1552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1553
1554 END Ship_From_Org;
1555
1556 FUNCTION Ship_To_Contact
1557 ( p_ship_to_contact_id IN NUMBER
1558 ) RETURN VARCHAR2
1559 IS
1560 l_ship_to_contact VARCHAR2(240) := NULL;
1561 --
1562 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1563 --
1564 BEGIN
1565
1566 IF p_ship_to_contact_id IS NOT NULL THEN
1567
1568 SELECT NAME
1569 INTO l_ship_to_contact
1570 FROM OE_CONTACTS_V
1571 WHERE CONTACT_ID = p_ship_to_contact_id;
1572
1573 END IF;
1574
1575 RETURN l_ship_to_contact;
1576
1577 EXCEPTION
1578
1579 WHEN NO_DATA_FOUND THEN
1580
1581 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1582 THEN
1583
1584 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1585 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_to_contact');
1586 OE_MSG_PUB.Add;
1587
1588 END IF;
1589
1590 RETURN NULL;
1591
1592 WHEN OTHERS THEN
1593
1594 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1595 THEN
1596 OE_MSG_PUB.Add_Exc_Msg
1597 ( G_PKG_NAME
1598 , 'Ship_To_Contact'
1599 );
1600 END IF;
1601
1602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1603
1604 END Ship_To_Contact;
1605
1606 PROCEDURE Ship_To_Org
1607 ( p_ship_to_org_id IN NUMBER
1608 , x_ship_to_address1 OUT NOCOPY VARCHAR2
1609
1610 , x_ship_to_address2 OUT NOCOPY VARCHAR2
1611
1612 , x_ship_to_address3 OUT NOCOPY VARCHAR2
1613
1614 , x_ship_to_address4 OUT NOCOPY VARCHAR2
1615
1616 , x_ship_to_location OUT NOCOPY VARCHAR2
1617
1618 , x_ship_to_org OUT NOCOPY VARCHAR2
1619
1620 , x_ship_to_city OUT NOCOPY VARCHAR2
1621
1622 , x_ship_to_state OUT NOCOPY VARCHAR2
1623
1624 , x_ship_to_postal_code OUT NOCOPY VARCHAR2
1625
1626 , x_ship_to_country OUT NOCOPY VARCHAR2
1627
1628 )
1629 IS
1630 --
1631 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1632 --
1633 BEGIN
1634
1635 IF p_ship_to_org_id is NOT NULL THEN
1636
1637 SELECT L.NAME
1638 , L.LOCATION_CODE
1639 , L.ADDRESS_LINE_1
1640 , L.ADDRESS_LINE_2
1641 , L.ADDRESS_LINE_3
1642 , L.ADDRESS_LINE_4
1643 , L.TOWN_OR_CITY
1644 , L.STATE
1645 , L.POSTAL_CODE
1646 , L.COUNTRY
1647 INTO x_ship_to_org
1648 , x_ship_to_location
1649 , x_ship_to_address1
1650 , x_ship_to_address2
1651 , x_ship_to_address3
1652 , x_ship_to_address4
1653 , x_ship_to_city
1654 , x_ship_to_state
1655 , x_ship_to_postal_code
1656 , x_ship_to_country
1657 FROM OE_SHIP_TO_ORGS_V L
1658 WHERE L.ORGANIZATION_ID = p_ship_to_org_id;
1659
1660 ELSE
1661
1662 x_ship_to_org := NULL ;
1663 x_ship_to_location := NULL ;
1664 x_ship_to_address1 := NULL ;
1665 x_ship_to_address2 := NULL ;
1666 x_ship_to_address3 := NULL ;
1667 x_ship_to_address4 := NULL ;
1668 x_ship_to_city := NULL ;
1669 x_ship_to_state := NULL ;
1670 x_ship_to_postal_code := NULL ;
1671 x_ship_to_country := NULL ;
1672
1673 END IF;
1674
1675 EXCEPTION
1676
1677 WHEN NO_DATA_FOUND THEN
1678
1679 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1680 THEN
1681
1682 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1683 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_to_org');
1684 OE_MSG_PUB.Add;
1685
1686 END IF;
1687
1688
1689 WHEN OTHERS THEN
1690
1691 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1692 THEN
1693 OE_MSG_PUB.Add_Exc_Msg
1694 ( G_PKG_NAME
1695 , 'Ship_To_Org'
1696 );
1697 END IF;
1698
1699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1700
1701 END Ship_To_Org;
1702
1703 FUNCTION Sold_To_Contact
1704 ( p_sold_to_contact_id IN NUMBER
1705 ) RETURN VARCHAR2
1706 IS
1707 l_sold_to_contact VARCHAR2(240) := NULL;
1708 --
1709 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1710 --
1711 BEGIN
1712
1713 IF p_sold_to_contact_id IS NOT NULL THEN
1714
1715 SELECT NAME
1716 INTO l_sold_to_contact
1717 FROM OE_CONTACTS_V
1718 WHERE CONTACT_ID = p_sold_to_contact_id;
1719
1720 END IF;
1721
1722 RETURN l_sold_to_contact;
1723
1724 EXCEPTION
1725
1726 WHEN NO_DATA_FOUND THEN
1727
1728 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1729 THEN
1730
1731 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1732 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sold_to_contact');
1733 OE_MSG_PUB.Add;
1734
1735 END IF;
1736
1737 RETURN NULL;
1738
1739 WHEN OTHERS THEN
1740
1741 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1742 THEN
1743 OE_MSG_PUB.Add_Exc_Msg
1744 ( G_PKG_NAME
1745 , 'Sold_To_Contact'
1746 );
1747 END IF;
1748
1749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1750
1751 END Sold_To_Contact;
1752
1753 PROCEDURE Sold_To_Org
1754 ( p_sold_to_org_id IN NUMBER ,
1755 x_org OUT NOCOPY VARCHAR2 ,
1756 x_customer_number OUT NOCOPY VARCHAR2
1757 )
1758 IS
1759 --
1760 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1761 --
1762 BEGIN
1763
1764 IF p_sold_to_org_id is NOT NULL THEN
1765
1766 IF l_debug_level > 0 THEN
1767 oe_debug_pub.add( 'DOING SELECT FOR CUSTOMER_NUMBER' ) ;
1768 END IF;
1769 SELECT O.NAME
1770 , O.CUSTOMER_NUMBER
1771 INTO x_org
1772 , x_customer_number
1773 FROM OE_SOLD_TO_ORGS_V O
1774 WHERE O.ORGANIZATION_ID = p_sold_to_org_id;
1775
1776 ELSE
1777
1778 IF l_debug_level > 0 THEN
1779 oe_debug_pub.add( 'INSIDE QUERY FOR CUSTOMER NUMBER - NO ORG_ID PASSED' ) ;
1780 END IF;
1781 x_org := NULL ;
1782 x_customer_number := NULL ;
1783
1784 END IF;
1785
1786 EXCEPTION
1787
1788 WHEN NO_DATA_FOUND THEN
1789
1790 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1791 THEN
1792
1793 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1794 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sold_to_org');
1795 OE_MSG_PUB.Add;
1796
1797 END IF;
1798
1799
1800 WHEN OTHERS THEN
1801
1802 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1803 THEN
1804 OE_MSG_PUB.Add_Exc_Msg
1805 ( G_PKG_NAME
1806 , 'Sold_To_Org'
1807 );
1808 END IF;
1809
1810 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1811
1812 END Sold_To_Org;
1813
1814 --Overloded procedure added for Ac. Desc, Registry ID Project
1815
1816 PROCEDURE Sold_To_Org
1817 (
1818 p_sold_to_org_id IN NUMBER ,
1819 x_org OUT NOCOPY VARCHAR2 ,
1820 x_customer_number OUT NOCOPY VARCHAR2 ,
1821 x_account_description OUT NOCOPY VARCHAR2,
1822 x_registry_id OUT NOCOPY VARCHAR2
1823 ) IS
1824 --
1825 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1826 --
1827 BEGIN
1828
1829 IF p_sold_to_org_id is NOT NULL THEN
1830
1831 IF l_debug_level > 0 THEN
1832 oe_debug_pub.add( 'DOING SELECT FOR CUSTOMER_NUMBER' ) ;
1833 END IF;
1834 SELECT P.PARTY_NAME
1835 , C.ACCOUNT_NUMBER
1836 , C.ACCOUNT_NAME
1837 , P.PARTY_NUMBER
1838 INTO x_org
1839 , x_customer_number
1840 , x_account_description
1841 , x_registry_id
1842 FROM HZ_CUST_ACCOUNTS C
1843 , HZ_PARTIES P
1844 WHERE C.CUST_ACCOUNT_ID = p_sold_to_org_id
1845 AND C.PARTY_ID =P.PARTY_ID;
1846
1847 ELSE
1848
1849 IF l_debug_level > 0 THEN
1850 oe_debug_pub.add( 'INSIDE QUERY FOR CUSTOMER NUMBER - NO ORG_ID PASSED' ) ;
1851 END IF;
1852 x_org := NULL ;
1853 x_customer_number := NULL ;
1854 x_account_description:= NULL ;
1855 x_registry_id := NULL ;
1856
1857 END IF;
1858
1859 EXCEPTION
1860
1861 WHEN NO_DATA_FOUND THEN
1862
1863 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1864 THEN
1865
1866 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1867 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sold_to_org');
1868 OE_MSG_PUB.Add;
1869
1870 END IF;
1871
1872
1873 WHEN OTHERS THEN
1874
1875 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1876 THEN
1877 OE_MSG_PUB.Add_Exc_Msg
1878 ( G_PKG_NAME
1879 , 'Sold_To_Org'
1880 );
1881 END IF;
1882
1883 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1884
1885 END Sold_To_Org;
1886 --added for Ac. Desc, Registry ID Project
1887
1888 FUNCTION Tax_Exempt
1889 ( p_tax_exempt_flag IN VARCHAR2
1890 ) RETURN VARCHAR2
1891 IS
1892 l_tax_exempt VARCHAR2(240) := NULL;
1893
1894 -- eBTax Changes
1895 l_lookup_type VARCHAR2(80) :='ZX_EXEMPTION_CONTROL';
1896 --
1897 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1898 --
1899 BEGIN
1900
1901 IF p_tax_exempt_flag IS NOT NULL THEN
1902 -- eBTax changes
1903 SELECT MEANING
1904 INTO l_tax_exempt
1905 FROM FND_LOOKUPS
1906 WHERE LOOKUP_CODE = p_tax_exempt_flag
1907 AND LOOKUP_TYPE = l_lookup_type;
1908
1909 END IF;
1910
1911 RETURN l_tax_exempt;
1912
1913 EXCEPTION
1914
1915 WHEN NO_DATA_FOUND THEN
1916
1917 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1918 THEN
1919
1920 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1921 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','tax_exempt');
1922 OE_MSG_PUB.Add;
1923
1924 END IF;
1925
1926 RETURN NULL;
1927
1928 WHEN OTHERS THEN
1929
1930 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1931 THEN
1932 OE_MSG_PUB.Add_Exc_Msg
1933 ( G_PKG_NAME
1934 , 'Tax_Exempt'
1935 );
1936 END IF;
1937
1938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1939
1940 END Tax_Exempt;
1941
1942 FUNCTION Tax_Exempt_Reason
1943 ( p_tax_exempt_reason_code IN VARCHAR2
1944 ) RETURN VARCHAR2
1945 IS
1946 l_tax_exempt_reason VARCHAR2(240) := NULL;
1947 -- eBTax changes
1948 l_lookup_type VARCHAR2(80) :='ZX_EXEMPTION_REASON_CODE';
1949 --
1950 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1951 --
1952 BEGIN
1953
1954 IF p_tax_exempt_reason_code IS NOT NULL THEN
1955 -- eBTax changes
1956 SELECT MEANING
1957 INTO l_tax_exempt_reason
1958 FROM FND_LOOKUPS
1959 WHERE LOOKUP_CODE = p_tax_exempt_reason_code
1960 AND LOOKUP_TYPE = l_lookup_type;
1961
1962 END IF;
1963
1964 RETURN l_tax_exempt_reason;
1965
1966 EXCEPTION
1967
1968 WHEN NO_DATA_FOUND THEN
1969
1970 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1971 THEN
1972
1973 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
1974 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','tax_exempt_reason');
1975 OE_MSG_PUB.Add;
1976
1977 END IF;
1978
1979 RETURN NULL;
1980
1981 WHEN OTHERS THEN
1982
1983 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1984 THEN
1985 OE_MSG_PUB.Add_Exc_Msg
1986 ( G_PKG_NAME
1987 , 'Tax_Exempt_Reason'
1988 );
1989 END IF;
1990
1991 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1992
1993 END Tax_Exempt_Reason;
1994
1995 FUNCTION Tax_Point
1996 ( p_tax_point_code IN VARCHAR2
1997 ) RETURN VARCHAR2
1998 IS
1999 l_tax_point VARCHAR2(240) := NULL;
2000 l_lookup_type VARCHAR2(80) :='TAX_POINT_TYPE';
2001 --
2002 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2003 --
2004 BEGIN
2005
2006 IF p_tax_point_code IS NOT NULL THEN
2007
2008 SELECT MEANING
2009 INTO l_tax_point
2010 FROM OE_AR_LOOKUPS_V
2011 WHERE LOOKUP_CODE = p_tax_point_code
2012 AND LOOKUP_TYPE = l_lookup_type;
2013
2014 END IF;
2015
2016 RETURN l_tax_point;
2017
2018 EXCEPTION
2019
2020 WHEN NO_DATA_FOUND THEN
2021
2022 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2023 THEN
2024
2025 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2026 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','tax_point');
2027 OE_MSG_PUB.Add;
2028
2029 END IF;
2030
2031 RETURN NULL;
2032
2033 WHEN OTHERS THEN
2034
2035 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2036 THEN
2037 OE_MSG_PUB.Add_Exc_Msg
2038 ( G_PKG_NAME
2039 , 'Tax_Point'
2040 );
2041 END IF;
2042
2043 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2044
2045 END Tax_Point;
2046
2047 FUNCTION Discount
2048 ( p_discount_id IN NUMBER
2049 ) RETURN VARCHAR2
2050 IS
2051 l_discount VARCHAR2(240) := NULL;
2052 --
2053 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2054 --
2055 BEGIN
2056
2057 IF p_discount_id IS NOT NULL THEN
2058
2059 -- SELECT DISCOUNT
2060 -- INTO l_discount
2061 -- FROM DB_TABLE
2062 -- WHERE DB_COLUMN = p_discount_id;
2063
2064 NULL;
2065
2066 END IF;
2067
2068 RETURN l_discount;
2069
2070 EXCEPTION
2071
2072 WHEN NO_DATA_FOUND THEN
2073
2074 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2075 THEN
2076
2077 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2078 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','discount');
2079 OE_MSG_PUB.Add;
2080
2081 END IF;
2082
2083 RETURN NULL;
2084
2085 WHEN OTHERS THEN
2086
2087 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2088 THEN
2089 OE_MSG_PUB.Add_Exc_Msg
2090 ( G_PKG_NAME
2091 , 'Discount'
2092 );
2093 END IF;
2094
2095 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2096
2097 END Discount;
2098
2099 FUNCTION sales_credit_type
2100 ( p_sales_credit_type_id IN NUMBER
2101 ) RETURN VARCHAR2
2102 IS
2103 l_sales_credit_type VARCHAR2(240) := NULL;
2104 cursor c_sales_credit_type(p_sales_credit_type_id number) is
2105 select name
2106 from oe_sales_credit_types
2107 where sales_credit_type_id = p_sales_credit_type_id;
2108 --
2109 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2110 --
2111 BEGIN
2112
2113 IF p_sales_credit_type_id IS NOT NULL THEN
2114 open c_sales_credit_type(p_sales_credit_type_id);
2115 fetch c_sales_credit_type into l_sales_credit_type;
2116 close c_sales_credit_type;
2117 END IF;
2118
2119 RETURN l_sales_credit_type;
2120
2121 EXCEPTION
2122
2123 WHEN NO_DATA_FOUND THEN
2124
2125 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2126 THEN
2127
2128 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2129 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sales_credit_type');
2130 OE_MSG_PUB.Add;
2131
2132 END IF;
2133
2134 RETURN NULL;
2135
2136 WHEN OTHERS THEN
2137
2138 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2139 THEN
2140 OE_MSG_PUB.Add_Exc_Msg
2141 ( G_PKG_NAME
2142 , 'sales_credit_type'
2143 );
2144 END IF;
2145
2146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2147
2148 END sales_credit_type;
2149
2150 FUNCTION Salesrep
2151 ( p_salesrep_id IN NUMBER
2152 ) RETURN VARCHAR2
2153 IS
2154 l_salesrep VARCHAR2(240) := NULL;
2155 cursor c_salesrep(p_salesrep_id number) is
2156 select name
2157 from ra_salesreps
2158 where salesrep_id = p_salesrep_id;
2159 --
2160 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2161 --
2162 BEGIN
2163
2164 IF p_salesrep_id IS NOT NULL THEN
2165 open c_salesrep(p_salesrep_id);
2166 fetch c_salesrep into l_salesrep;
2167 close c_salesrep;
2168 END IF;
2169
2170 RETURN l_salesrep;
2171
2172 EXCEPTION
2173
2174 WHEN NO_DATA_FOUND THEN
2175
2176 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2177 THEN
2178
2179 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2180 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','salesrep');
2181 OE_MSG_PUB.Add;
2182
2183 END IF;
2184
2185 RETURN NULL;
2186
2187 WHEN OTHERS THEN
2188
2189 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2190 THEN
2191 OE_MSG_PUB.Add_Exc_Msg
2192 ( G_PKG_NAME
2193 , 'Salesrep'
2194 );
2195 END IF;
2196
2197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2198
2199 END Salesrep;
2200
2201 FUNCTION Customer_Item
2202 ( p_customer_item_id IN NUMBER
2203 ) RETURN VARCHAR2
2204 IS
2205 l_customer_item VARCHAR2(240) := NULL;
2206 --
2207 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2208 --
2209 BEGIN
2210
2211 IF p_customer_item_id IS NOT NULL THEN
2212
2213 -- SELECT CUSTOMER_ITEM
2214 -- INTO l_customer_item
2215 -- FROM DB_TABLE
2216 -- WHERE DB_COLUMN = p_customer_item_id;
2217
2218 NULL;
2219
2220 END IF;
2221
2222 RETURN l_customer_item;
2223
2224 EXCEPTION
2225
2226 WHEN NO_DATA_FOUND THEN
2227
2228 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2229 THEN
2230
2231 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2232 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','customer_item');
2233 OE_MSG_PUB.Add;
2234
2235 END IF;
2236
2237 RETURN NULL;
2238
2239 WHEN OTHERS THEN
2240
2241 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2242 THEN
2243 OE_MSG_PUB.Add_Exc_Msg
2244 ( G_PKG_NAME
2245 , 'Customer_Item'
2246 );
2247 END IF;
2248
2249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2250
2251 END Customer_Item;
2252
2253 FUNCTION Demand_Bucket_Type
2254 ( p_demand_bucket_type_code IN VARCHAR2
2255 ) RETURN VARCHAR2
2256 IS
2257 l_demand_bucket_type VARCHAR2(240) := NULL;
2258 --
2259 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2260 --
2261 BEGIN
2262
2263 IF p_demand_bucket_type_code IS NOT NULL THEN
2264
2265 -- SELECT DEMAND_BUCKET_TYPE
2266 -- INTO l_demand_bucket_type
2267 -- FROM DB_TABLE
2268 -- WHERE DB_COLUMN = p_demand_bucket_type_code;
2269
2270 NULL;
2271
2272 END IF;
2273
2274 RETURN l_demand_bucket_type;
2275
2276 EXCEPTION
2277
2278 WHEN NO_DATA_FOUND THEN
2279
2280 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2281 THEN
2282
2283 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2284 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','demand_bucket_type');
2285 OE_MSG_PUB.Add;
2286
2287 END IF;
2288
2289 RETURN NULL;
2290
2291 WHEN OTHERS THEN
2292
2293 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2294 THEN
2295 OE_MSG_PUB.Add_Exc_Msg
2296 ( G_PKG_NAME
2297 , 'Demand_Bucket_Type'
2298 );
2299 END IF;
2300
2301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2302
2303 END Demand_Bucket_Type;
2304
2305 FUNCTION Inventory_Item
2306 ( p_inventory_item_id IN NUMBER
2307 ) RETURN VARCHAR2
2308 IS
2309 l_inventory_item VARCHAR2(240) := NULL;
2310 l_validation_org_id NUMBER := NULL;
2311 --
2312 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2313 --
2314 BEGIN
2315
2316 IF p_inventory_item_id IS NOT NULL THEN
2317
2318 --Kris make a global variable for validation org so we don't have to get it all the time
2319
2320 /*l_validation_org_id := fnd_profile.value('OE_ORGANIZATION_ID');*/
2321 -- This change is required since we are dropping the profile OE_ORGANIZATION -- _ID. Change made by Esha.
2322 l_validation_org_id := OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
2323
2324 SELECT DESCRIPTION
2325 INTO l_inventory_item
2326 FROM MTL_SYSTEM_ITEMS
2327 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
2328 AND ORGANIZATION_ID = l_validation_org_id;
2329
2330
2331 END IF;
2332 RETURN l_inventory_item;
2333
2334 EXCEPTION
2335
2336 WHEN NO_DATA_FOUND THEN
2337
2338 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2339 THEN
2340
2341 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2342 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','inventory_item');
2343 OE_MSG_PUB.Add;
2344
2345 END IF;
2346
2347 RETURN NULL;
2348
2349 WHEN OTHERS THEN
2350
2351 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2352 THEN
2353 OE_MSG_PUB.Add_Exc_Msg
2354 ( G_PKG_NAME
2355 , 'Inventory_Item'
2356 );
2357 END IF;
2358
2359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2360
2361 END Inventory_Item;
2362
2363 FUNCTION Item_Type
2364 ( p_item_type_code IN VARCHAR2
2365 ) RETURN VARCHAR2
2366 IS
2367 l_item_type VARCHAR2(240) := NULL;
2368 --
2369 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2370 --
2371 BEGIN
2372
2373 IF p_item_type_code IS NOT NULL THEN
2374
2375 -- SELECT ITEM_TYPE
2376 -- INTO l_item_type
2377 -- FROM DB_TABLE
2378 -- WHERE DB_COLUMN = p_item_type_code;
2379
2380 NULL;
2381
2382 END IF;
2383
2384 RETURN l_item_type;
2385
2386 EXCEPTION
2387
2388 WHEN NO_DATA_FOUND THEN
2389
2390 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2391 THEN
2392
2393 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2394 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','item_type');
2395 OE_MSG_PUB.Add;
2396
2397 END IF;
2398
2399 RETURN NULL;
2400
2401 WHEN OTHERS THEN
2402
2403 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2404 THEN
2405 OE_MSG_PUB.Add_Exc_Msg
2406 ( G_PKG_NAME
2407 , 'Item_Type'
2408 );
2409 END IF;
2410
2411 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2412
2413 END Item_Type;
2414
2415 FUNCTION Line_Type
2416 ( p_line_type_id IN NUMBER
2417 ) RETURN VARCHAR2
2418 IS
2419 l_line_type VARCHAR2(240) := NULL;
2420 --
2421 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2422 --
2423 BEGIN
2424
2425 IF p_line_type_id IS NOT NULL THEN
2426
2427 SELECT NAME
2428 INTO l_line_type
2429 FROM OE_LINE_TYPES_V
2430 WHERE LINE_TYPE_ID = p_line_type_id;
2431
2432 END IF;
2433
2434 RETURN l_line_type;
2435
2436 EXCEPTION
2437
2438 WHEN NO_DATA_FOUND THEN
2439
2440 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2441 THEN
2442
2443 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2444 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','line_type');
2445 OE_MSG_PUB.Add;
2446
2447 END IF;
2448
2449 RETURN NULL;
2450
2451 WHEN OTHERS THEN
2452
2453 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2454 THEN
2455 OE_MSG_PUB.Add_Exc_Msg
2456 ( G_PKG_NAME
2457 , 'Line_Type'
2458 );
2459 END IF;
2460
2461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2462
2463 END Line_Type;
2464
2465 FUNCTION Project
2466 ( p_project_id IN NUMBER
2467 ) RETURN VARCHAR2
2468 IS
2469 l_project VARCHAR2(240) := NULL;
2470 --
2471 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2472 --
2473 BEGIN
2474 IF p_project_id IS NOT NULL THEN
2475
2476
2477 l_project := pjm_project.all_proj_idtonum(p_project_id);
2478
2479 END IF;
2480
2481 RETURN l_project;
2482
2483 EXCEPTION
2484
2485 WHEN NO_DATA_FOUND THEN
2486
2487 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2488 THEN
2489
2490 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2491 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','project');
2492 OE_MSG_PUB.Add;
2493
2494 END IF;
2495
2496 RETURN NULL;
2497
2498 WHEN OTHERS THEN
2499
2500 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2501 THEN
2502 OE_MSG_PUB.Add_Exc_Msg
2503 ( G_PKG_NAME
2504 , 'Project'
2505 );
2506 END IF;
2507
2508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2509
2510 END Project;
2511
2512 FUNCTION Rla_Schedule_Type
2513 ( p_rla_schedule_type_code IN VARCHAR2
2514 ) RETURN VARCHAR2
2515 IS
2516 l_rla_schedule_type VARCHAR2(240) := NULL;
2517 --
2518 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2519 --
2520 BEGIN
2521
2522 IF p_rla_schedule_type_code IS NOT NULL THEN
2523
2524 -- SELECT RLA_SCHEDULE_TYPE
2525 -- INTO l_rla_schedule_type
2526 -- FROM DB_TABLE
2527 -- WHERE DB_COLUMN = p_rla_schedule_type_code;
2528
2529 NULL;
2530
2531 END IF;
2532
2533 RETURN l_rla_schedule_type;
2534
2535 EXCEPTION
2536
2537 WHEN NO_DATA_FOUND THEN
2538
2539 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2540 THEN
2541
2542 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2543 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','rla_schedule_type');
2544 OE_MSG_PUB.Add;
2545
2546 END IF;
2547
2548 RETURN NULL;
2549
2550 WHEN OTHERS THEN
2551
2552 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2553 THEN
2554 OE_MSG_PUB.Add_Exc_Msg
2555 ( G_PKG_NAME
2556 , 'Rla_Schedule_Type'
2557 );
2558 END IF;
2559
2560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2561
2562 END Rla_Schedule_Type;
2563
2564 FUNCTION Task
2565 ( p_task_id IN NUMBER
2566 ) RETURN VARCHAR2
2567 IS
2568 l_task VARCHAR2(240) := NULL;
2569 --
2570 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2571 --
2572 BEGIN
2573
2574 IF p_task_id IS NOT NULL THEN
2575
2576 l_task := PJM_PROJECT.ALL_TASK_IDTONUM(p_task_id);
2577
2578 END IF;
2579
2580 RETURN l_task;
2581
2582 EXCEPTION
2583
2584 WHEN NO_DATA_FOUND THEN
2585
2586 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2587 THEN
2588
2589 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2590 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','task');
2591 OE_MSG_PUB.Add;
2592
2593 END IF;
2594
2595 RETURN NULL;
2596
2597 WHEN OTHERS THEN
2598
2599 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2600 THEN
2601 OE_MSG_PUB.Add_Exc_Msg
2602 ( G_PKG_NAME
2603 , 'Task'
2604 );
2605 END IF;
2606
2607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2608
2609 END Task;
2610
2611
2612 FUNCTION Over_Ship_Reason
2613 ( p_over_ship_reason_code IN VARCHAR2
2614 ) RETURN VARCHAR2
2615 IS
2616 l_over_ship_reason VARCHAR2(240) := NULL;
2617 --
2618 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2619 --
2620 BEGIN
2621
2622 IF p_over_ship_reason_code IS NOT NULL THEN
2623
2624 -- SELECT RLA_SCHEDULE_TYPE
2625 -- INTO l_rla_schedule_type
2626 -- FROM DB_TABLE
2627 -- WHERE DB_COLUMN = p_rla_schedule_type_code;
2628
2629 NULL;
2630
2631 END IF;
2632
2633 RETURN l_over_ship_reason;
2634
2635 EXCEPTION
2636
2637 WHEN NO_DATA_FOUND THEN
2638
2639 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2640 THEN
2641
2642 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2643 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','over_ship_reason');
2644 OE_MSG_PUB.Add;
2645
2646 END IF;
2647
2648 RETURN NULL;
2649
2650 WHEN OTHERS THEN
2651
2652 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2653 THEN
2654 OE_MSG_PUB.Add_Exc_Msg
2655 ( G_PKG_NAME
2656 , 'Over_Ship_Reason'
2657 );
2658 END IF;
2659
2660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2661
2662 END Over_Ship_Reason;
2663
2664 FUNCTION Source_Type
2665 ( p_source_type_code IN VARCHAR2
2666 ) RETURN VARCHAR2
2667 IS
2668 l_source_type VARCHAR2(240) := NULL;
2669 l_lookup_type VARCHAR2(80) :='SOURCE_TYPE';
2670 --
2671 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2672 --
2673 BEGIN
2674
2675 IF p_source_type_code IS NOT NULL THEN
2676
2677 SELECT MEANING
2678 INTO l_source_type
2679 FROM OE_LOOKUPS
2680 WHERE LOOKUP_CODE = p_source_type_code
2681 AND LOOKUP_TYPE = l_lookup_type;
2682
2683 END IF;
2684
2685 RETURN l_source_type;
2686
2687 EXCEPTION
2688
2689 WHEN NO_DATA_FOUND THEN
2690
2691 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2692 THEN
2693
2694 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2695 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','source_type');
2696 OE_MSG_PUB.Add;
2697
2698 END IF;
2699
2700 RETURN NULL;
2701
2702 WHEN OTHERS THEN
2703
2704 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2705 THEN
2706 OE_MSG_PUB.Add_Exc_Msg
2707 ( G_PKG_NAME
2708 , 'Source_Type'
2709 );
2710 END IF;
2711
2712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2713
2714 END Source_Type;
2715
2716 FUNCTION Order_Date_Type
2717 ( p_order_date_type_code IN VARCHAR2
2718 ) RETURN VARCHAR2
2719 IS
2720 l_order_date_type VARCHAR2(80) := NULL;
2721 l_lookup_type VARCHAR2(80) := 'REQUEST_DATE_TYPE';
2722 --
2723 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2724 --
2725 BEGIN
2726
2727 IF p_order_date_type_code IS NOT NULL THEN
2728
2729 SELECT MEANING
2730 INTO l_order_date_type
2731 FROM OE_LOOKUPS
2732 WHERE LOOKUP_CODE = p_order_date_type_code
2733 AND LOOKUP_TYPE = l_lookup_type;
2734
2735 END IF;
2736
2737 RETURN l_order_date_type;
2738
2739 EXCEPTION
2740
2741 WHEN NO_DATA_FOUND THEN
2742
2743 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2744 THEN
2745
2746 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2747 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','order_date_type');
2748 OE_MSG_PUB.Add;
2749
2750 END IF;
2751
2752 RETURN NULL;
2753
2754 WHEN OTHERS THEN
2755
2756 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2757 THEN
2758 OE_MSG_PUB.Add_Exc_Msg
2759 ( G_PKG_NAME
2760 , 'order_date_type'
2761 );
2762 END IF;
2763
2764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2765
2766 END Order_Date_Type;
2767
2768 FUNCTION Return_Reason
2769 ( p_return_reason_code IN VARCHAR2
2770 ) RETURN VARCHAR2
2771 IS
2772 l_return_reason VARCHAR2(240) := NULL;
2773 l_lookup_type VARCHAR2(80) :='CREDIT_MEMO_REASON';
2774 --
2775 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2776 --
2777 BEGIN
2778
2779 IF p_return_reason_code IS NOT NULL THEN
2780
2781 SELECT MEANING
2782 INTO l_return_reason
2783 FROM OE_AR_LOOKUPS_V
2784 WHERE LOOKUP_CODE = p_return_reason_code
2785 AND LOOKUP_TYPE = l_lookup_type;
2786
2787 END IF;
2788
2789 RETURN l_return_reason;
2790
2791 EXCEPTION
2792
2793 WHEN NO_DATA_FOUND THEN
2794
2795 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2796 THEN
2797
2798 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2799 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','return_reason');
2800 OE_MSG_PUB.Add;
2801
2802 END IF;
2803
2804 RETURN NULL;
2805
2806 WHEN OTHERS THEN
2807
2808 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2809 THEN
2810 OE_MSG_PUB.Add_Exc_Msg
2811 ( G_PKG_NAME
2812 , 'Return_Reason'
2813 );
2814 END IF;
2815
2816 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2817
2818 END Return_Reason;
2819
2820 PROCEDURE Reference_Line
2821 ( p_reference_line_id IN NUMBER
2822 , x_ref_order_number OUT NOCOPY NUMBER
2823 , x_ref_line_number OUT NOCOPY NUMBER
2824 , x_ref_shipment_number OUT NOCOPY NUMBER
2825 , x_ref_option_number OUT NOCOPY NUMBER
2826 , x_ref_component_number OUT NOCOPY NUMBER
2827
2828 )
2829 IS
2830 --
2831 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2832 --
2833 BEGIN
2834
2835 IF p_reference_line_id IS NOT NULL THEN
2836
2837 SELECT /* MOAC_SQL_CHANGE */ H.order_number,
2838 l.line_number,
2839 l.shipment_number,
2840 l.option_number,
2841 l.component_number
2842 INTO x_ref_order_number,
2843 x_ref_line_number,
2844 x_ref_shipment_number,
2845 x_ref_option_number,
2846 x_ref_component_number
2847 FROM oe_order_headers_all h,
2848 oe_order_lines l
2849 WHERE l.line_id=p_reference_line_id
2850 and h.header_id=l.header_id;
2851
2852 ELSE
2853
2854 x_ref_order_number := NULL;
2855 x_ref_line_number := NULL;
2856 x_ref_shipment_number := NULL;
2857 x_ref_option_number := NULL;
2858 x_ref_component_number := NULL;
2859
2860 END IF;
2861
2862 EXCEPTION
2863
2864 WHEN NO_DATA_FOUND THEN
2865
2866 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2867 THEN
2868
2869 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2870 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','reference_line');
2871 OE_MSG_PUB.Add;
2872
2873 END IF;
2874
2875 WHEN OTHERS THEN
2876
2877 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2878 THEN
2879 OE_MSG_PUB.Add_Exc_Msg
2880 ( G_PKG_NAME
2881 , 'Reference_line'
2882 );
2883 END IF;
2884
2885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2886
2887 END Reference_Line;
2888
2889 PROCEDURE Reference_Cust_Trx_Line
2890 ( p_reference_cust_trx_line_id IN NUMBER
2891 , x_ref_invoice_number OUT NOCOPY VARCHAR2
2892
2893 , x_ref_invoice_line_number OUT NOCOPY NUMBER
2894
2895 )
2896 IS
2897 --
2898 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2899 --
2900 BEGIN
2901
2902 IF p_reference_cust_trx_line_id IS NOT NULL THEN
2903
2904 select /* MOAC_SQL_CHANGE */ rct.trx_number,
2905 rctl.line_number
2906 into x_ref_invoice_number,
2907 x_ref_invoice_line_number
2908 from ra_customer_trx rct,
2909 ra_customer_trx_lines_all rctl
2910 where rctl.customer_trx_line_id = p_reference_cust_trx_line_id
2911 and rctl.customer_trx_id = rct.customer_trx_id;
2912
2913 ELSE
2914
2915 x_ref_invoice_number := NULL;
2916 x_ref_invoice_line_number := NULL;
2917
2918 END IF;
2919
2920 EXCEPTION
2921
2922 WHEN NO_DATA_FOUND THEN
2923
2924 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2925 THEN
2926
2927 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2928 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Reference_Customer_Trx_Line');
2929 OE_MSG_PUB.Add;
2930
2931 END IF;
2932
2933 WHEN OTHERS THEN
2934
2935 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2936 THEN
2937 OE_MSG_PUB.Add_Exc_Msg
2938 ( G_PKG_NAME
2939 , 'Reference_Customer_Trx_Line'
2940 );
2941 END IF;
2942
2943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2944
2945 END Reference_Cust_Trx_Line;
2946
2947 FUNCTION Credit_Invoice_Line
2948 ( p_credit_invoice_line_id IN NUMBER
2949 ) RETURN VARCHAR2
2950 IS
2951 l_credit_invoice_number VARCHAR2(240) := NULL;
2952 --
2953 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2954 --
2955 BEGIN
2956
2957 IF p_credit_invoice_line_id IS NOT NULL THEN
2958
2959 select /* MOAC_SQL_CHANGE */ rct.trx_number
2960 into l_credit_invoice_number
2961 from ra_customer_trx rct,
2962 ra_customer_trx_lines_all rctl
2963 where rctl.customer_trx_line_id = p_credit_invoice_line_id
2964 and rctl.customer_trx_id = rct.customer_trx_id;
2965
2966 END IF;
2967
2968 RETURN l_credit_invoice_number;
2969
2970 EXCEPTION
2971
2972 WHEN NO_DATA_FOUND THEN
2973
2974 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2975 THEN
2976
2977 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
2978 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','credit_invoice_line');
2979 OE_MSG_PUB.Add;
2980
2981 END IF;
2982
2983 RETURN NULL;
2984
2985 WHEN OTHERS THEN
2986
2987 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2988 THEN
2989 OE_MSG_PUB.Add_Exc_Msg
2990 ( G_PKG_NAME
2991 , 'credit_invoice_line'
2992 );
2993 END IF;
2994
2995 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2996
2997 END Credit_Invoice_Line;
2998
2999 FUNCTION Veh_Cus_Item_cum_Key
3000 ( p_veh_cus_item_cum_key_id IN NUMBER
3001 ) RETURN VARCHAR2
3002 IS
3003 l_veh_cus_item_cum_key VARCHAR2(240) := NULL;
3004 --
3005 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3006 --
3007 BEGIN
3008
3009 IF p_veh_cus_item_cum_key_id IS NOT NULL THEN
3010
3011 -- SELECT TASK
3012 -- INTO l_task
3013 -- FROM DB_TABLE
3014 -- WHERE DB_COLUMN = p_task_id;
3015
3016 NULL;
3017
3018 END IF;
3019
3020 RETURN l_veh_cus_item_cum_key;
3021
3022 EXCEPTION
3023
3024 WHEN NO_DATA_FOUND THEN
3025
3026 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3027 THEN
3028
3029 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
3030 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','veh_cus_item_cum_key');
3031 OE_MSG_PUB.Add;
3032
3033 END IF;
3034
3035 RETURN NULL;
3036
3037 WHEN OTHERS THEN
3038
3039 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3040 THEN
3041 OE_MSG_PUB.Add_Exc_Msg
3042 ( G_PKG_NAME
3043 , 'Veh_Cus_Item_cum_key'
3044 );
3045 END IF;
3046
3047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3048
3049 END Veh_Cus_Item_cum_key;
3050
3051 FUNCTION Payment_Type
3052 ( p_payment_type_code IN VARCHAR2
3053 ) RETURN VARCHAR2
3054 IS
3055 l_payment_type VARCHAR2(240) := NULL;
3056 l_lookup_type VARCHAR2(80) := 'PAYMENT TYPE';
3057 --
3058 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3059 --
3060 BEGIN
3061
3062 IF p_payment_type_code IS NOT NULL THEN
3063 --serla begin
3064 IF OE_PrePayment_UTIL.IS_MULTIPLE_PAYMENTS_ENABLED THEN
3065 -- skubendr For commitments also the corresponding value has to be returned
3066 IF ( p_payment_type_code = 'COMMITMENT') THEN
3067 l_lookup_type :='OE_PAYMENT_TYPE';
3068 SELECT MEANING
3069 INTO l_payment_type
3070 FROM oe_lookups
3071 WHERE lookup_type=l_lookup_type and lookup_code=p_payment_type_code;
3072 ELSE
3073 SELECT NAME
3074 INTO l_payment_type
3075 FROM oe_payment_types_vl
3076 WHERE payment_type_code = p_payment_type_code;
3077 END IF;
3078 ELSE
3079 SELECT MEANING
3080 INTO l_payment_type
3081 FROM OE_LOOKUPS
3082 WHERE LOOKUP_CODE = p_payment_type_code
3083 AND LOOKUP_TYPE = l_lookup_type;
3084 END IF;
3085 --serla end
3086
3087 END IF;
3088
3089 RETURN l_payment_type;
3090
3091 EXCEPTION
3092
3093 WHEN NO_DATA_FOUND THEN
3094
3095 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3096 THEN
3097
3098 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
3099 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','payment_type');
3100 OE_MSG_PUB.Add;
3101
3102 END IF;
3103
3104 RETURN NULL;
3105
3106 WHEN OTHERS THEN
3107
3108 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3109 THEN
3110 OE_MSG_PUB.Add_Exc_Msg
3111 ( G_PKG_NAME
3112 , 'Payment_Type'
3113 );
3114 END IF;
3115
3116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3117
3118 END Payment_Type;
3119
3120 FUNCTION Credit_Card
3121 ( p_credit_card_code IN VARCHAR2
3122 ) RETURN VARCHAR2
3123 IS
3124 l_credit_card VARCHAR2(240) := NULL;
3125 l_lookup_type VARCHAR2(80) :='CREDIT_CARD';
3126 --
3127 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3128 --
3129 BEGIN
3130
3131 IF p_credit_card_code IS NOT NULL THEN
3132 --R12 CC Encryption
3133 select description into l_credit_card
3134 from iby_creditcard_issuers_v
3135 where CARD_ISSUER_CODE = p_credit_card_code
3136 and rownum = 1;
3137 END IF;
3138
3139 RETURN l_credit_card;
3140
3141 EXCEPTION
3142
3143 WHEN NO_DATA_FOUND THEN
3144
3145 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3146 THEN
3147
3148 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
3149 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','credit_card');
3150 OE_MSG_PUB.Add;
3151
3152 END IF;
3153
3154 RETURN NULL;
3155
3156 WHEN OTHERS THEN
3157
3158 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3159 THEN
3160 OE_MSG_PUB.Add_Exc_Msg
3161 ( G_PKG_NAME
3162 , 'Credit_Card'
3163 );
3164 END IF;
3165
3166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3167
3168 END Credit_Card;
3169
3170
3171 FUNCTION Commitment
3172 ( p_commitment_id IN NUMBER
3173 ) RETURN VARCHAR2
3174 IS
3175 l_commitment VARCHAR2(240) := NULL;
3176 --
3177 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3178 --
3179 BEGIN
3180
3181 IF p_commitment_id IS NOT NULL THEN
3182
3183 SELECT trx_number
3184 INTO l_commitment
3185 FROM RA_CUSTOMER_TRX
3186 WHERE customer_trx_id = p_commitment_id;
3187
3188 END IF;
3189
3190 RETURN l_commitment;
3191
3192 EXCEPTION
3193
3194 WHEN NO_DATA_FOUND THEN
3195
3196 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3197 THEN
3198
3199 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
3200 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','commitment');
3201 OE_MSG_PUB.Add;
3202
3203 END IF;
3204
3205 RETURN NULL;
3206
3207 WHEN OTHERS THEN
3208
3209 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3210 THEN
3211 OE_MSG_PUB.Add_Exc_Msg
3212 ( G_PKG_NAME
3213 , 'Commitment'
3214 );
3215 END IF;
3216
3217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3218
3219 END Commitment;
3220
3221
3222 /* Pricing Contract Functions : Begin */
3223
3224
3225 FUNCTION Agreement_Contact
3226 ( p_agreement_contact_id IN NUMBER
3227 ) RETURN VARCHAR2
3228 IS
3229 l_agreement_contact VARCHAR2(240) := NULL;
3230 --
3231 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3232 --
3233 BEGIN
3234
3235 IF p_agreement_contact_id IS NOT NULL THEN
3236
3237 -- SELECT AGREEMENT_CONTACT
3238 -- INTO l_agreement_contact
3239 -- FROM DB_TABLE
3240 -- WHERE DB_COLUMN = p_agreement_contact_id;
3241
3242 NULL;
3243
3244 END IF;
3245
3246 RETURN l_agreement_contact;
3247
3248 EXCEPTION
3249
3250 WHEN NO_DATA_FOUND THEN
3251
3252 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3253 THEN
3254
3255 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3256 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','agreement_contact');
3257 OE_MSG_PUB.Add;
3258
3259 END IF;
3260
3261 RETURN NULL;
3262
3263 WHEN OTHERS THEN
3264
3265 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3266 THEN
3267 OE_MSG_PUB.Add_Exc_Msg
3268 ( G_PKG_NAME
3269 , 'Agreement_Contact'
3270 );
3271 END IF;
3272
3273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3274
3275 END Agreement_Contact;
3276
3277 FUNCTION Agreement_Type
3278 ( p_agreement_type_code IN VARCHAR2
3279 ) RETURN VARCHAR2
3280 IS
3281 l_agreement_type VARCHAR2(240) := NULL;
3282 --
3283 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3284 --
3285 BEGIN
3286
3287 IF p_agreement_type_code IS NOT NULL THEN
3288
3289 -- SELECT AGREEMENT_TYPE
3290 -- INTO l_agreement_type
3291 -- FROM DB_TABLE
3292 -- WHERE DB_COLUMN = p_agreement_type_code;
3293
3294 NULL;
3295
3296 END IF;
3297
3298 RETURN l_agreement_type;
3299
3300 EXCEPTION
3301
3302 WHEN NO_DATA_FOUND THEN
3303
3304 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3305 THEN
3306
3307 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3308 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','agreement_type');
3309 OE_MSG_PUB.Add;
3310
3311 END IF;
3312
3313 RETURN NULL;
3314
3315 WHEN OTHERS THEN
3316
3317 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3318 THEN
3319 OE_MSG_PUB.Add_Exc_Msg
3320 ( G_PKG_NAME
3321 , 'Agreement_Type'
3322 );
3323 END IF;
3324
3325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3326
3327 END Agreement_Type;
3328
3329 FUNCTION Customer
3330 ( p_sold_to_org_id IN NUMBER
3331 ) RETURN VARCHAR2
3332 IS
3333 l_customer VARCHAR2(240) := NULL;
3334 --
3335 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3336 --
3337 BEGIN
3338
3339 IF p_sold_to_org_id IS NOT NULL THEN
3340
3341 -- SELECT CUSTOMER
3342 -- INTO l_customer
3343 -- FROM DB_TABLE
3344 -- WHERE DB_COLUMN = p_sold_to_org_id;
3345
3346 NULL;
3347
3348 END IF;
3349
3350 RETURN l_customer;
3351
3352 EXCEPTION
3353
3354 WHEN NO_DATA_FOUND THEN
3355
3356 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3357 THEN
3358
3359 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3360 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','customer');
3361 OE_MSG_PUB.Add;
3362
3363 END IF;
3364
3365 RETURN NULL;
3366
3367 WHEN OTHERS THEN
3368
3369 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3370 THEN
3371 OE_MSG_PUB.Add_Exc_Msg
3372 ( G_PKG_NAME
3373 , 'Customer'
3374 );
3375 END IF;
3376
3377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3378
3379 END Customer;
3380
3381 FUNCTION Invoice_Contact
3382 ( p_invoice_contact_id IN NUMBER
3383 ) RETURN VARCHAR2
3384 IS
3385 l_invoice_contact VARCHAR2(240) := NULL;
3386 --
3387 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3388 --
3389 BEGIN
3390
3391 IF p_invoice_contact_id IS NOT NULL THEN
3392
3393 -- SELECT INVOICE_CONTACT
3394 -- INTO l_invoice_contact
3395 -- FROM DB_TABLE
3396 -- WHERE DB_COLUMN = p_invoice_contact_id;
3397
3398 NULL;
3399
3400 END IF;
3401
3402 RETURN l_invoice_contact;
3403
3404 EXCEPTION
3405
3406 WHEN NO_DATA_FOUND THEN
3407
3408 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3409 THEN
3410
3411 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3412 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoice_contact');
3413 OE_MSG_PUB.Add;
3414
3415 END IF;
3416
3417 RETURN NULL;
3418
3419 WHEN OTHERS THEN
3420
3421 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3422 THEN
3423 OE_MSG_PUB.Add_Exc_Msg
3424 ( G_PKG_NAME
3425 , 'Invoice_Contact'
3426 );
3427 END IF;
3428
3429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3430
3431 END Invoice_Contact;
3432
3433 FUNCTION Invoice_To_Site_Use
3434 ( p_invoice_to_org_id IN NUMBER
3435 ) RETURN VARCHAR2
3436 IS
3437 l_invoice_to_site_use VARCHAR2(240) := NULL;
3438 --
3439 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3440 --
3441 BEGIN
3442
3443 IF p_invoice_to_org_id IS NOT NULL THEN
3444
3445 -- SELECT INVOICE_TO_SITE_USE
3446 -- INTO l_invoice_to_site_use
3447 -- FROM DB_TABLE
3448 -- WHERE DB_COLUMN = p_invoice_to_org_id;
3449
3450 NULL;
3451
3452 END IF;
3453
3454 RETURN l_invoice_to_site_use;
3455
3456 EXCEPTION
3457
3458 WHEN NO_DATA_FOUND THEN
3459
3460 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3461 THEN
3462
3463 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3464 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoice_to_site_use');
3465 OE_MSG_PUB.Add;
3466
3467 END IF;
3468
3469 RETURN NULL;
3470
3471 WHEN OTHERS THEN
3472
3473 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3474 THEN
3475 OE_MSG_PUB.Add_Exc_Msg
3476 ( G_PKG_NAME
3477 , 'Invoice_To_Site_Use'
3478 );
3479 END IF;
3480
3481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3482
3483 END Invoice_To_Site_Use;
3484
3485 FUNCTION Override_Arule
3486 ( p_override_arule_flag IN VARCHAR2
3487 ) RETURN VARCHAR2
3488 IS
3489 l_override_arule VARCHAR2(240) := NULL;
3490 --
3491 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3492 --
3493 BEGIN
3494
3495 IF p_override_arule_flag IS NOT NULL THEN
3496
3497 -- SELECT OVERRIDE_ARULE
3498 -- INTO l_override_arule
3499 -- FROM DB_TABLE
3500 -- WHERE DB_COLUMN = p_override_arule_flag;
3501
3502 NULL;
3503
3504 END IF;
3505
3506 RETURN l_override_arule;
3507
3508 EXCEPTION
3509
3510 WHEN NO_DATA_FOUND THEN
3511
3512 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3513 THEN
3514
3515 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3516 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','override_arule');
3517 OE_MSG_PUB.Add;
3518
3519 END IF;
3520
3521 RETURN NULL;
3522
3523 WHEN OTHERS THEN
3524
3525 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3526 THEN
3527 OE_MSG_PUB.Add_Exc_Msg
3528 ( G_PKG_NAME
3529 , 'Override_Arule'
3530 );
3531 END IF;
3532
3533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3534
3535 END Override_Arule;
3536
3537 FUNCTION Override_Irule
3538 ( p_override_irule_flag IN VARCHAR2
3539 ) RETURN VARCHAR2
3540 IS
3541 l_override_irule VARCHAR2(240) := NULL;
3542 --
3543 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3544 --
3545 BEGIN
3546
3547 IF p_override_irule_flag IS NOT NULL THEN
3548
3549 -- SELECT OVERRIDE_IRULE
3550 -- INTO l_override_irule
3551 -- FROM DB_TABLE
3552 -- WHERE DB_COLUMN = p_override_irule_flag;
3553
3554 NULL;
3555
3556 END IF;
3557
3558 RETURN l_override_irule;
3559
3560 EXCEPTION
3561
3562 WHEN NO_DATA_FOUND THEN
3563
3564 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3565 THEN
3566
3567 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3568 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','override_irule');
3569 OE_MSG_PUB.Add;
3570
3571 END IF;
3572
3573 RETURN NULL;
3574
3575 WHEN OTHERS THEN
3576
3577 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3578 THEN
3579 OE_MSG_PUB.Add_Exc_Msg
3580 ( G_PKG_NAME
3581 , 'Override_Irule'
3582 );
3583 END IF;
3584
3585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3586
3587 END Override_Irule;
3588
3589 FUNCTION Revision_Reason
3590 ( p_revision_reason_code IN VARCHAR2
3591 ) RETURN VARCHAR2
3592 IS
3593 l_revision_reason VARCHAR2(240) := NULL;
3594 --
3595 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3596 --
3597 BEGIN
3598
3599 IF p_revision_reason_code IS NOT NULL THEN
3600
3601 -- SELECT REVISION_REASON
3602 -- INTO l_revision_reason
3603 -- FROM DB_TABLE
3604 -- WHERE DB_COLUMN = p_revision_reason_code;
3605
3606 NULL;
3607
3608 END IF;
3609
3610 RETURN l_revision_reason;
3611
3612 EXCEPTION
3613
3614 WHEN NO_DATA_FOUND THEN
3615
3616 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3617 THEN
3618
3619 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3620 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','revision_reason');
3621 OE_MSG_PUB.Add;
3622
3623 END IF;
3624
3625 RETURN NULL;
3626
3627 WHEN OTHERS THEN
3628
3629 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3630 THEN
3631 OE_MSG_PUB.Add_Exc_Msg
3632 ( G_PKG_NAME
3633 , 'Revision_Reason'
3634 );
3635 END IF;
3636
3637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3638
3639 END Revision_Reason;
3640
3641 FUNCTION Ship_Method
3642 ( p_ship_method_code IN VARCHAR2
3643 ) RETURN VARCHAR2
3644 IS
3645 l_ship_method VARCHAR2(240) := NULL;
3646 --
3647 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3648 --
3649 BEGIN
3650
3651 IF p_ship_method_code IS NOT NULL THEN
3652
3653 SELECT meaning
3654 INTO l_ship_method
3655 FROM oe_ship_methods_v
3656 WHERE lookup_code = p_ship_method_code;
3657
3658 NULL;
3659
3660 END IF;
3661
3662 RETURN l_ship_method;
3663
3664 EXCEPTION
3665
3666 WHEN NO_DATA_FOUND THEN
3667
3668 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3669 THEN
3670
3671 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3672 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_method');
3673 OE_MSG_PUB.Add;
3674
3675 END IF;
3676
3677 RETURN NULL;
3678
3679 WHEN OTHERS THEN
3680
3681 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3682 THEN
3683 OE_MSG_PUB.Add_Exc_Msg
3684 ( G_PKG_NAME
3685 , 'Ship_Method'
3686 );
3687 END IF;
3688
3689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3690
3691 END Ship_Method;
3692
3693 FUNCTION Term
3694 ( p_term_id IN NUMBER
3695 ) RETURN VARCHAR2
3696 IS
3697 l_term VARCHAR2(240) := NULL;
3698 --
3699 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3700 --
3701 BEGIN
3702
3703 IF p_term_id IS NOT NULL THEN
3704
3705 -- SELECT TERM
3706 -- INTO l_term
3707 -- FROM DB_TABLE
3708 -- WHERE DB_COLUMN = p_term_id;
3709
3710 NULL;
3711
3712 END IF;
3713
3714 RETURN l_term;
3715
3716 EXCEPTION
3717
3718 WHEN NO_DATA_FOUND THEN
3719
3720 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3721 THEN
3722
3723 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3724 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','term');
3725 OE_MSG_PUB.Add;
3726
3727 END IF;
3728
3729 RETURN NULL;
3730
3731 WHEN OTHERS THEN
3732
3733 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3734 THEN
3735 OE_MSG_PUB.Add_Exc_Msg
3736 ( G_PKG_NAME
3737 , 'Term'
3738 );
3739 END IF;
3740
3741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3742
3743 END Term;
3744
3745 FUNCTION Currency
3746 ( p_currency_code IN VARCHAR2
3747 ) RETURN VARCHAR2
3748 IS
3749 l_currency VARCHAR2(240) := NULL;
3750 --
3751 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3752 --
3753 BEGIN
3754
3755 IF p_currency_code IS NOT NULL THEN
3756
3757 -- SELECT CURRENCY
3758 -- INTO l_currency
3759 -- FROM DB_TABLE
3760 -- WHERE DB_COLUMN = p_currency_code;
3761
3762 NULL;
3763
3764 END IF;
3765
3766 RETURN l_currency;
3767
3768 EXCEPTION
3769
3770 WHEN NO_DATA_FOUND THEN
3771
3772 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3773 THEN
3774
3775 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3776 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','currency');
3777 OE_MSG_PUB.Add;
3778
3779 END IF;
3780
3781 RETURN NULL;
3782
3783 WHEN OTHERS THEN
3784
3785 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3786 THEN
3787 OE_MSG_PUB.Add_Exc_Msg
3788 ( G_PKG_NAME
3789 , 'Currency'
3790 );
3791 END IF;
3792
3793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3794
3795 END Currency;
3796
3797 FUNCTION Secondary_Price_List
3798 ( p_secondary_price_list_id IN NUMBER
3799 ) RETURN VARCHAR2
3800 IS
3801 l_secondary_price_list VARCHAR2(240) := NULL;
3802 --
3803 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3804 --
3805 BEGIN
3806
3807 IF p_secondary_price_list_id IS NOT NULL THEN
3808
3809 -- SELECT SECONDARY_PRICE_LIST
3810 -- INTO l_secondary_price_list
3811 -- FROM DB_TABLE
3812 -- WHERE DB_COLUMN = p_secondary_price_list_id;
3813
3814 NULL;
3815
3816 END IF;
3817
3818 RETURN l_secondary_price_list;
3819
3820 EXCEPTION
3821
3822 WHEN NO_DATA_FOUND THEN
3823
3824 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3825 THEN
3826
3827 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3828 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','secondary_price_list');
3829 OE_MSG_PUB.Add;
3830
3831 END IF;
3832
3833 RETURN NULL;
3834
3835 WHEN OTHERS THEN
3836
3837 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3838 THEN
3839 OE_MSG_PUB.Add_Exc_Msg
3840 ( G_PKG_NAME
3841 , 'Secondary_Price_List'
3842 );
3843 END IF;
3844
3845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3846
3847 END Secondary_Price_List;
3848
3849 FUNCTION Terms
3850 ( p_terms_id IN NUMBER
3851 ) RETURN VARCHAR2
3852 IS
3853 l_terms VARCHAR2(240) := NULL;
3854 --
3855 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3856 --
3857 BEGIN
3858
3859 IF p_terms_id IS NOT NULL THEN
3860
3861 -- SELECT TERMS
3862 -- INTO l_terms
3863 -- FROM DB_TABLE
3864 -- WHERE DB_COLUMN = p_terms_id;
3865
3866 NULL;
3867
3868 END IF;
3869
3870 RETURN l_terms;
3871
3872 EXCEPTION
3873
3874 WHEN NO_DATA_FOUND THEN
3875
3876 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3877 THEN
3878
3879 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3880 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','terms');
3881 OE_MSG_PUB.Add;
3882
3883 END IF;
3884
3885 RETURN NULL;
3886
3887 WHEN OTHERS THEN
3888
3889 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3890 THEN
3891 OE_MSG_PUB.Add_Exc_Msg
3892 ( G_PKG_NAME
3893 , 'Terms'
3894 );
3895 END IF;
3896
3897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3898
3899 END Terms;
3900
3901 FUNCTION Automatic_Discount
3902 ( p_automatic_discount_flag IN VARCHAR2
3903 ) RETURN VARCHAR2
3904 IS
3905 l_automatic_discount VARCHAR2(240) := NULL;
3906 --
3907 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3908 --
3909 BEGIN
3910
3911 IF p_automatic_discount_flag IS NOT NULL THEN
3912
3913 -- SELECT AUTOMATIC_DISCOUNT
3914 -- INTO l_automatic_discount
3915 -- FROM DB_TABLE
3916 -- WHERE DB_COLUMN = p_automatic_discount_flag;
3917
3918 NULL;
3919
3920 END IF;
3921
3922 RETURN l_automatic_discount;
3923
3924 EXCEPTION
3925
3926 WHEN NO_DATA_FOUND THEN
3927
3928 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3929 THEN
3930
3931 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3932 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','automatic_discount');
3933 OE_MSG_PUB.Add;
3934
3935 END IF;
3936
3937 RETURN NULL;
3938
3939 WHEN OTHERS THEN
3940
3941 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3942 THEN
3943 OE_MSG_PUB.Add_Exc_Msg
3944 ( G_PKG_NAME
3945 , 'Automatic_Discount'
3946 );
3947 END IF;
3948
3949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3950
3951 END Automatic_Discount;
3952
3953 FUNCTION Discount_Lines
3954 ( p_discount_lines_flag IN VARCHAR2
3955 ) RETURN VARCHAR2
3956 IS
3957 l_discount_lines VARCHAR2(240) := NULL;
3958 --
3959 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3960 --
3961 BEGIN
3962
3963 IF p_discount_lines_flag IS NOT NULL THEN
3964
3965 -- SELECT DISCOUNT_LINES
3966 -- INTO l_discount_lines
3967 -- FROM DB_TABLE
3968 -- WHERE DB_COLUMN = p_discount_lines_flag;
3969
3970 NULL;
3971
3972 END IF;
3973
3974 RETURN l_discount_lines;
3975
3976 EXCEPTION
3977
3978 WHEN NO_DATA_FOUND THEN
3979
3980 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3981 THEN
3982
3983 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
3984 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','discount_lines');
3985 OE_MSG_PUB.Add;
3986
3987 END IF;
3988
3989 RETURN NULL;
3990
3991 WHEN OTHERS THEN
3992
3993 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3994 THEN
3995 OE_MSG_PUB.Add_Exc_Msg
3996 ( G_PKG_NAME
3997 , 'Discount_Lines'
3998 );
3999 END IF;
4000
4001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4002
4003 END Discount_Lines;
4004
4005 FUNCTION Discount_Type
4006 ( p_discount_type_code IN VARCHAR2
4007 ) RETURN VARCHAR2
4008 IS
4009 l_discount_type VARCHAR2(240) := NULL;
4010 --
4011 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4012 --
4013 BEGIN
4014
4015 IF p_discount_type_code IS NOT NULL THEN
4016
4017 -- SELECT DISCOUNT_TYPE
4018 -- INTO l_discount_type
4019 -- FROM DB_TABLE
4020 -- WHERE DB_COLUMN = p_discount_type_code;
4021
4022 NULL;
4023
4024 END IF;
4025
4026 RETURN l_discount_type;
4027
4028 EXCEPTION
4029
4030 WHEN NO_DATA_FOUND THEN
4031
4032 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4033 THEN
4034
4035 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4036 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','discount_type');
4037 OE_MSG_PUB.Add;
4038
4039 END IF;
4040
4041 RETURN NULL;
4042
4043 WHEN OTHERS THEN
4044
4045 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4046 THEN
4047 OE_MSG_PUB.Add_Exc_Msg
4048 ( G_PKG_NAME
4049 , 'Discount_Type'
4050 );
4051 END IF;
4052
4053 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4054
4055 END Discount_Type;
4056
4057 FUNCTION Manual_Discount
4058 ( p_manual_discount_flag IN VARCHAR2
4059 ) RETURN VARCHAR2
4060 IS
4061 l_manual_discount VARCHAR2(240) := NULL;
4062 --
4063 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4064 --
4065 BEGIN
4066
4067 IF p_manual_discount_flag IS NOT NULL THEN
4068
4069 -- SELECT MANUAL_DISCOUNT
4070 -- INTO l_manual_discount
4071 -- FROM DB_TABLE
4072 -- WHERE DB_COLUMN = p_manual_discount_flag;
4073
4074 NULL;
4075
4076 END IF;
4077
4078 RETURN l_manual_discount;
4079
4080 EXCEPTION
4081
4082 WHEN NO_DATA_FOUND THEN
4083
4084 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4085 THEN
4086
4087 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4088 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','manual_discount');
4089 OE_MSG_PUB.Add;
4090
4091 END IF;
4092
4093 RETURN NULL;
4094
4095 WHEN OTHERS THEN
4096
4097 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4098 THEN
4099 OE_MSG_PUB.Add_Exc_Msg
4100 ( G_PKG_NAME
4101 , 'Manual_Discount'
4102 );
4103 END IF;
4104
4105 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4106
4107 END Manual_Discount;
4108
4109 FUNCTION Override_Allowed
4110 ( p_override_allowed_flag IN VARCHAR2
4111 ) RETURN VARCHAR2
4112 IS
4113 l_override_allowed VARCHAR2(240) := NULL;
4114 --
4115 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4116 --
4117 BEGIN
4118
4119 IF p_override_allowed_flag IS NOT NULL THEN
4120
4121 -- SELECT OVERRIDE_ALLOWED
4122 -- INTO l_override_allowed
4123 -- FROM DB_TABLE
4124 -- WHERE DB_COLUMN = p_override_allowed_flag;
4125
4126 NULL;
4127
4128 END IF;
4129
4130 RETURN l_override_allowed;
4131
4132 EXCEPTION
4133
4134 WHEN NO_DATA_FOUND THEN
4135
4136 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4137 THEN
4138
4139 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4140 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','override_allowed');
4141 OE_MSG_PUB.Add;
4142
4143 END IF;
4144
4145 RETURN NULL;
4146
4147 WHEN OTHERS THEN
4148
4149 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4150 THEN
4151 OE_MSG_PUB.Add_Exc_Msg
4152 ( G_PKG_NAME
4153 , 'Override_Allowed'
4154 );
4155 END IF;
4156
4157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4158
4159 END Override_Allowed;
4160
4161 FUNCTION Prorate
4162 ( p_prorate_flag IN VARCHAR2
4163 ) RETURN VARCHAR2
4164 IS
4165 l_prorate VARCHAR2(240) := NULL;
4166 --
4167 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4168 --
4169 BEGIN
4170
4171 IF p_prorate_flag IS NOT NULL THEN
4172
4173 -- SELECT PRORATE
4174 -- INTO l_prorate
4175 -- FROM DB_TABLE
4176 -- WHERE DB_COLUMN = p_prorate_flag;
4177
4178 NULL;
4179
4180 END IF;
4181
4182 RETURN l_prorate;
4183
4184 EXCEPTION
4185
4186 WHEN NO_DATA_FOUND THEN
4187
4188 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4189 THEN
4190
4191 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4192 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','prorate');
4193 OE_MSG_PUB.Add;
4194
4195 END IF;
4196
4197 RETURN NULL;
4198
4199 WHEN OTHERS THEN
4200
4201 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4202 THEN
4203 OE_MSG_PUB.Add_Exc_Msg
4204 ( G_PKG_NAME
4205 , 'Prorate'
4206 );
4207 END IF;
4208
4209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4210
4211 END Prorate;
4212
4213 FUNCTION Method
4214 ( p_method_code IN VARCHAR2
4215 ) RETURN VARCHAR2
4216 IS
4217 l_method VARCHAR2(240) := NULL;
4218 --
4219 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4220 --
4221 BEGIN
4222
4223 IF p_method_code IS NOT NULL THEN
4224
4225 -- SELECT METHOD
4226 -- INTO l_method
4227 -- FROM DB_TABLE
4228 -- WHERE DB_COLUMN = p_method_code;
4229
4230 NULL;
4231
4232 END IF;
4233
4234 RETURN l_method;
4235
4236 EXCEPTION
4237
4238 WHEN NO_DATA_FOUND THEN
4239
4240 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4241 THEN
4242
4243 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4244 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','method');
4245 OE_MSG_PUB.Add;
4246
4247 END IF;
4248
4249 RETURN NULL;
4250
4251 WHEN OTHERS THEN
4252
4253 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4254 THEN
4255 OE_MSG_PUB.Add_Exc_Msg
4256 ( G_PKG_NAME
4257 , 'Method'
4258 );
4259 END IF;
4260
4261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4262
4263 END Method;
4264
4265 FUNCTION Price_List_Line
4266 ( p_price_list_line_id IN NUMBER
4267 ) RETURN VARCHAR2
4268 IS
4269 l_price_list_line VARCHAR2(240) := NULL;
4270 --
4271 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4272 --
4273 BEGIN
4274
4275 IF p_price_list_line_id IS NOT NULL THEN
4276
4277 -- SELECT PRICE_LIST_LINE
4278 -- INTO l_price_list_line
4279 -- FROM DB_TABLE
4280 -- WHERE DB_COLUMN = p_price_list_line_id;
4281
4282 NULL;
4283
4284 END IF;
4285
4286 RETURN l_price_list_line;
4287
4288 EXCEPTION
4289
4290 WHEN NO_DATA_FOUND THEN
4291
4292 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4293 THEN
4294
4295 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4296 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','price_list_line');
4297 OE_MSG_PUB.Add;
4298
4299 END IF;
4300
4301 RETURN NULL;
4302
4303 WHEN OTHERS THEN
4304
4305 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4306 THEN
4307 OE_MSG_PUB.Add_Exc_Msg
4308 ( G_PKG_NAME
4309 , 'Price_List_Line'
4310 );
4311 END IF;
4312
4313 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4314
4315 END Price_List_Line;
4316
4317 FUNCTION Pricing_Rule
4318 ( p_pricing_rule_id IN NUMBER
4319 ) RETURN VARCHAR2
4320 IS
4321 l_pricing_rule VARCHAR2(240) := NULL;
4322 --
4323 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4324 --
4325 BEGIN
4326
4327 IF p_pricing_rule_id IS NOT NULL THEN
4328
4329 -- SELECT PRICING_RULE
4330 -- INTO l_pricing_rule
4331 -- FROM DB_TABLE
4332 -- WHERE DB_COLUMN = p_pricing_rule_id;
4333
4334 NULL;
4335
4336 END IF;
4337
4338 RETURN l_pricing_rule;
4339
4340 EXCEPTION
4341
4342 WHEN NO_DATA_FOUND THEN
4343
4344 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4345 THEN
4346
4347 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4348 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','pricing_rule');
4349 OE_MSG_PUB.Add;
4350
4351 END IF;
4352
4353 RETURN NULL;
4354
4355 WHEN OTHERS THEN
4356
4357 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4358 THEN
4359 OE_MSG_PUB.Add_Exc_Msg
4360 ( G_PKG_NAME
4361 , 'Pricing_Rule'
4362 );
4363 END IF;
4364
4365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4366
4367 END Pricing_Rule;
4368
4369 FUNCTION Reprice
4370 ( p_reprice_flag IN VARCHAR2
4371 ) RETURN VARCHAR2
4372 IS
4373 l_reprice VARCHAR2(240) := NULL;
4374 --
4375 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4376 --
4377 BEGIN
4378
4379 IF p_reprice_flag IS NOT NULL THEN
4380
4381 -- SELECT REPRICE
4382 -- INTO l_reprice
4383 -- FROM DB_TABLE
4384 -- WHERE DB_COLUMN = p_reprice_flag;
4385
4386 NULL;
4387
4388 END IF;
4389
4390 RETURN l_reprice;
4391
4392 EXCEPTION
4393
4394 WHEN NO_DATA_FOUND THEN
4395
4396 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4397 THEN
4398
4399 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4400 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','reprice');
4401 OE_MSG_PUB.Add;
4402
4403 END IF;
4404
4405 RETURN NULL;
4406
4407 WHEN OTHERS THEN
4408
4409 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4410 THEN
4411 OE_MSG_PUB.Add_Exc_Msg
4412 ( G_PKG_NAME
4413 , 'Reprice'
4414 );
4415 END IF;
4416
4417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4418
4419 END Reprice;
4420
4421 FUNCTION Unit
4422 ( p_unit_code IN VARCHAR2
4423 ) RETURN VARCHAR2
4424 IS
4425 l_unit VARCHAR2(240) := NULL;
4426 --
4427 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4428 --
4429 BEGIN
4430
4431 IF p_unit_code IS NOT NULL THEN
4432
4433 -- SELECT UNIT
4434 -- INTO l_unit
4435 -- FROM DB_TABLE
4436 -- WHERE DB_COLUMN = p_unit_code;
4437
4438 NULL;
4439
4440 END IF;
4441
4442 RETURN l_unit;
4443
4444 EXCEPTION
4445
4446 WHEN NO_DATA_FOUND THEN
4447
4448 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4449 THEN
4450
4451 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4452 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','unit');
4453 OE_MSG_PUB.Add;
4454
4455 END IF;
4456
4457 RETURN NULL;
4458
4459 WHEN OTHERS THEN
4460
4461 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4462 THEN
4463 OE_MSG_PUB.Add_Exc_Msg
4464 ( G_PKG_NAME
4465 , 'Unit'
4466 );
4467 END IF;
4468
4469 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4470
4471 END Unit;
4472
4473 FUNCTION Customer_Class
4474 ( p_customer_class_code IN VARCHAR2
4475 ) RETURN VARCHAR2
4476 IS
4477 l_customer_class VARCHAR2(240) := NULL;
4478 --
4479 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4480 --
4481 BEGIN
4482
4483 IF p_customer_class_code IS NOT NULL THEN
4484
4485 -- SELECT CUSTOMER_CLASS
4486 -- INTO l_customer_class
4487 -- FROM DB_TABLE
4488 -- WHERE DB_COLUMN = p_customer_class_code;
4489
4490 NULL;
4491
4492 END IF;
4493
4494 RETURN l_customer_class;
4495
4496 EXCEPTION
4497
4498 WHEN NO_DATA_FOUND THEN
4499
4500 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4501 THEN
4502
4503 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4504 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','customer_class');
4505 OE_MSG_PUB.Add;
4506
4507 END IF;
4508
4509 RETURN NULL;
4510
4511 WHEN OTHERS THEN
4512
4513 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4514 THEN
4515 OE_MSG_PUB.Add_Exc_Msg
4516 ( G_PKG_NAME
4517 , 'Customer_Class'
4518 );
4519 END IF;
4520
4521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4522
4523 END Customer_Class;
4524
4525 FUNCTION Discount_Customer
4526 ( p_discount_customer_id IN NUMBER
4527 ) RETURN VARCHAR2
4528 IS
4529 l_discount_customer VARCHAR2(240) := NULL;
4530 --
4531 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4532 --
4533 BEGIN
4534
4535 IF p_discount_customer_id IS NOT NULL THEN
4536
4537 -- SELECT DISCOUNT_CUSTOMER
4538 -- INTO l_discount_customer
4539 -- FROM DB_TABLE
4540 -- WHERE DB_COLUMN = p_discount_customer_id;
4541
4542 NULL;
4543
4544 END IF;
4545
4546 RETURN l_discount_customer;
4547
4548 EXCEPTION
4549
4550 WHEN NO_DATA_FOUND THEN
4551
4552 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4553 THEN
4554
4555 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4556 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','discount_customer');
4557 OE_MSG_PUB.Add;
4558
4559 END IF;
4560
4561 RETURN NULL;
4562
4563 WHEN OTHERS THEN
4564
4565 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4566 THEN
4567 OE_MSG_PUB.Add_Exc_Msg
4568 ( G_PKG_NAME
4569 , 'Discount_Customer'
4570 );
4571 END IF;
4572
4573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4574
4575 END Discount_Customer;
4576
4577 FUNCTION Site_Use
4578 ( p_site_use_id IN NUMBER
4579 ) RETURN VARCHAR2
4580 IS
4581 l_site_use VARCHAR2(240) := NULL;
4582 --
4583 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4584 --
4585 BEGIN
4586
4587 IF p_site_use_id IS NOT NULL THEN
4588
4589 -- SELECT SITE_USE
4590 -- INTO l_site_use
4591 -- FROM DB_TABLE
4592 -- WHERE DB_COLUMN = p_site_use_id;
4593
4594 NULL;
4595
4596 END IF;
4597
4598 RETURN l_site_use;
4599
4600 EXCEPTION
4601
4602 WHEN NO_DATA_FOUND THEN
4603
4604 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4605 THEN
4606
4607 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4608 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','site_use');
4609 OE_MSG_PUB.Add;
4610
4611 END IF;
4612
4613 RETURN NULL;
4614
4615 WHEN OTHERS THEN
4616
4617 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4618 THEN
4619 OE_MSG_PUB.Add_Exc_Msg
4620 ( G_PKG_NAME
4621 , 'Site_Use'
4622 );
4623 END IF;
4624
4625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4626
4627 END Site_Use;
4628
4629 FUNCTION Entity
4630 ( p_entity_id IN NUMBER
4631 ) RETURN VARCHAR2
4632 IS
4633 l_entity VARCHAR2(240) := NULL;
4634 --
4635 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4636 --
4637 BEGIN
4638
4639 IF p_entity_id IS NOT NULL THEN
4640
4641 -- SELECT ENTITY
4642 -- INTO l_entity
4643 -- FROM DB_TABLE
4644 -- WHERE DB_COLUMN = p_entity_id;
4645
4646 NULL;
4647
4648 END IF;
4649
4650 RETURN l_entity;
4651
4652 EXCEPTION
4653
4654 WHEN NO_DATA_FOUND THEN
4655
4656 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4657 THEN
4658
4659 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4660 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','entity');
4661 OE_MSG_PUB.Add;
4662
4663 END IF;
4664
4665 RETURN NULL;
4666
4667 WHEN OTHERS THEN
4668
4669 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4670 THEN
4671 OE_MSG_PUB.Add_Exc_Msg
4672 ( G_PKG_NAME
4673 , 'Entity'
4674 );
4675 END IF;
4676
4677 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4678
4679 END Entity;
4680
4681 FUNCTION Method_Type
4682 ( p_method_type_code IN VARCHAR2
4683 ) RETURN VARCHAR2
4684 IS
4685 l_method_type VARCHAR2(240) := NULL;
4686 --
4687 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4688 --
4689 BEGIN
4690
4691 IF p_method_type_code IS NOT NULL THEN
4692
4693 -- SELECT METHOD_TYPE
4694 -- INTO l_method_type
4695 -- FROM DB_TABLE
4696 -- WHERE DB_COLUMN = p_method_type_code;
4697
4698 NULL;
4699
4700 END IF;
4701
4702 RETURN l_method_type;
4703
4704 EXCEPTION
4705
4706 WHEN NO_DATA_FOUND THEN
4707
4708 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4709 THEN
4710
4711 fnd_message.set_name('ONT','OE_ID_TO__VALUE_ERROR');
4712 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','method_type');
4713 OE_MSG_PUB.Add;
4714
4715 END IF;
4716
4717 RETURN NULL;
4718
4719 WHEN OTHERS THEN
4720
4721 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4722 THEN
4723 OE_MSG_PUB.Add_Exc_Msg
4724 ( G_PKG_NAME
4725 , 'Method_Type'
4726 );
4727 END IF;
4728
4729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4730
4731 END Method_Type;
4732
4733 FUNCTION Lot_Serial
4734 ( p_lot_serial_id IN NUMBER
4735 ) RETURN VARCHAR2
4736 IS
4737 l_lot_serial VARCHAR2(240) := NULL;
4738 --
4739 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4740 --
4741 BEGIN
4742
4743 IF p_lot_serial_id IS NOT NULL THEN
4744
4745 -- SELECT LOT_SERIAL
4746 -- INTO l_lot_serial
4747 -- FROM DB_TABLE
4748 -- WHERE DB_COLUMN = p_lot_serial_id;
4749
4750 NULL;
4751
4752 END IF;
4753
4754 RETURN l_lot_serial;
4755
4756 EXCEPTION
4757
4758 WHEN NO_DATA_FOUND THEN
4759
4760 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4761 THEN
4762
4763 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
4764 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','lot_serial');
4765 OE_MSG_PUB.Add;
4766
4767 END IF;
4768
4769 RETURN NULL;
4770
4771 WHEN OTHERS THEN
4772
4773 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4774 THEN
4775 OE_MSG_PUB.Add_Exc_Msg
4776 ( G_PKG_NAME
4777 , 'Lot_Serial'
4778 );
4779 END IF;
4780
4781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4782
4783 END Lot_Serial;
4784
4785 FUNCTION Appear_On_Ack
4786 ( p_appear_on_ack_flag IN VARCHAR2
4787 ) RETURN VARCHAR2
4788 IS
4789 l_appear_on_ack VARCHAR2(240) := NULL;
4790 --
4791 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4792 --
4793 BEGIN
4794
4795 IF p_appear_on_ack_flag IS NOT NULL THEN
4796
4797 -- SELECT APPEAR_ON_ACK
4798 -- INTO l_appear_on_ack
4799 -- FROM DB_TABLE
4800 -- WHERE DB_COLUMN = p_appear_on_ack_flag;
4801
4802 NULL;
4803
4804 END IF;
4805
4806 RETURN l_appear_on_ack;
4807
4808 EXCEPTION
4809
4810 WHEN NO_DATA_FOUND THEN
4811
4812 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4813 THEN
4814
4815 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
4816 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','appear_on_ack');
4817 OE_MSG_PUB.Add;
4818
4819 END IF;
4820
4821 RETURN NULL;
4822
4823 WHEN OTHERS THEN
4824
4825 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4826 THEN
4827 OE_MSG_PUB.Add_Exc_Msg
4828 ( G_PKG_NAME
4829 , 'Appear_On_Ack'
4830 );
4831 END IF;
4832
4833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4834
4835 END Appear_On_Ack;
4836
4837 FUNCTION Appear_On_Invoice
4838 ( p_appear_on_invoice_flag IN VARCHAR2
4839 ) RETURN VARCHAR2
4840 IS
4841 l_appear_on_invoice VARCHAR2(240) := NULL;
4842 --
4843 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4844 --
4845 BEGIN
4846
4847 IF p_appear_on_invoice_flag IS NOT NULL THEN
4848
4849 -- SELECT APPEAR_ON_INVOICE
4850 -- INTO l_appear_on_invoice
4851 -- FROM DB_TABLE
4852 -- WHERE DB_COLUMN = p_appear_on_invoice_flag;
4853
4854 NULL;
4855
4856 END IF;
4857
4858 RETURN l_appear_on_invoice;
4859
4860 EXCEPTION
4861
4862 WHEN NO_DATA_FOUND THEN
4863
4864 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4865 THEN
4866
4867 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
4868 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','appear_on_invoice');
4869 OE_MSG_PUB.Add;
4870
4871 END IF;
4872
4873 RETURN NULL;
4874
4875 WHEN OTHERS THEN
4876
4877 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4878 THEN
4879 OE_MSG_PUB.Add_Exc_Msg
4880 ( G_PKG_NAME
4881 , 'Appear_On_Invoice'
4882 );
4883 END IF;
4884
4885 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4886
4887 END Appear_On_Invoice;
4888
4889 FUNCTION Charge
4890 ( p_charge_id IN NUMBER
4891 ) RETURN VARCHAR2
4892 IS
4893 l_charge VARCHAR2(240) := NULL;
4894 --
4895 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4896 --
4897 BEGIN
4898
4899 IF p_charge_id IS NOT NULL THEN
4900
4901 -- SELECT CHARGE
4902 -- INTO l_charge
4903 -- FROM DB_TABLE
4904 -- WHERE DB_COLUMN = p_charge_id;
4905
4906 NULL;
4907
4908 END IF;
4909
4910 RETURN l_charge;
4911
4912 EXCEPTION
4913
4914 WHEN NO_DATA_FOUND THEN
4915
4916 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4917 THEN
4918
4919 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
4920 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','charge');
4921 OE_MSG_PUB.Add;
4922
4923 END IF;
4924
4925 RETURN NULL;
4926
4927 WHEN OTHERS THEN
4928
4929 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4930 THEN
4931 OE_MSG_PUB.Add_Exc_Msg
4932 ( G_PKG_NAME
4933 , 'Charge'
4934 );
4935 END IF;
4936
4937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4938
4939 END Charge;
4940
4941 FUNCTION Charge_Type
4942 ( p_charge_type_id IN NUMBER
4943 ) RETURN VARCHAR2
4944 IS
4945 l_charge_type VARCHAR2(240) := NULL;
4946 --
4947 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4948 --
4949 BEGIN
4950
4951 IF p_charge_type_id IS NOT NULL THEN
4952
4953 -- SELECT CHARGE_TYPE
4954 -- INTO l_charge_type
4955 -- FROM DB_TABLE
4956 -- WHERE DB_COLUMN = p_charge_type_id;
4957
4958 NULL;
4959
4960 END IF;
4961
4962 RETURN l_charge_type;
4963
4964 EXCEPTION
4965
4966 WHEN NO_DATA_FOUND THEN
4967
4968 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
4969 THEN
4970
4971 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
4972 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','charge_type');
4973 OE_MSG_PUB.Add;
4974
4975 END IF;
4976
4977 RETURN NULL;
4978
4979 WHEN OTHERS THEN
4980
4981 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4982 THEN
4983 OE_MSG_PUB.Add_Exc_Msg
4984 ( G_PKG_NAME
4985 , 'Charge_Type'
4986 );
4987 END IF;
4988
4989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4990
4991 END Charge_Type;
4992
4993 FUNCTION Cost_Or_Charge
4994 ( p_cost_or_charge_flag IN VARCHAR2
4995 ) RETURN VARCHAR2
4996 IS
4997 l_cost_or_charge VARCHAR2(240) := NULL;
4998 --
4999 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5000 --
5001 BEGIN
5002
5003 IF p_cost_or_charge_flag IS NOT NULL THEN
5004
5005 -- SELECT COST_OR_CHARGE
5006 -- INTO l_cost_or_charge
5007 -- FROM DB_TABLE
5008 -- WHERE DB_COLUMN = p_cost_or_charge_flag;
5009
5010 NULL;
5011
5012 END IF;
5013
5014 RETURN l_cost_or_charge;
5015
5016 EXCEPTION
5017
5018 WHEN NO_DATA_FOUND THEN
5019
5020 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5021 THEN
5022
5023 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5024 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','cost_or_charge');
5025 OE_MSG_PUB.Add;
5026
5027 END IF;
5028
5029 RETURN NULL;
5030
5031 WHEN OTHERS THEN
5032
5033 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5034 THEN
5035 OE_MSG_PUB.Add_Exc_Msg
5036 ( G_PKG_NAME
5037 , 'Cost_Or_Charge'
5038 );
5039 END IF;
5040
5041 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5042
5043 END Cost_Or_Charge;
5044
5045 FUNCTION Delivery
5046 ( p_delivery_id IN NUMBER
5047 ) RETURN VARCHAR2
5048 IS
5049 l_delivery VARCHAR2(240) := NULL;
5050 --
5051 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5052 --
5053 BEGIN
5054
5055 IF p_delivery_id IS NOT NULL THEN
5056
5057 -- SELECT DELIVERY
5058 -- INTO l_delivery
5059 -- FROM DB_TABLE
5060 -- WHERE DB_COLUMN = p_delivery_id;
5061
5062 NULL;
5063
5064 END IF;
5065
5066 RETURN l_delivery;
5067
5068 EXCEPTION
5069
5070 WHEN NO_DATA_FOUND THEN
5071
5072 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5073 THEN
5074
5075 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5076 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','delivery');
5077 OE_MSG_PUB.Add;
5078
5079 END IF;
5080
5081 RETURN NULL;
5082
5083 WHEN OTHERS THEN
5084
5085 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5086 THEN
5087 OE_MSG_PUB.Add_Exc_Msg
5088 ( G_PKG_NAME
5089 , 'Delivery'
5090 );
5091 END IF;
5092
5093 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5094
5095 END Delivery;
5096
5097 FUNCTION Departure
5098 ( p_departure_id IN NUMBER
5099 ) RETURN VARCHAR2
5100 IS
5101 l_departure VARCHAR2(240) := NULL;
5102 --
5103 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5104 --
5105 BEGIN
5106
5107 IF p_departure_id IS NOT NULL THEN
5108
5109 -- SELECT DEPARTURE
5110 -- INTO l_departure
5111 -- FROM DB_TABLE
5112 -- WHERE DB_COLUMN = p_departure_id;
5113
5114 NULL;
5115
5116 END IF;
5117
5118 RETURN l_departure;
5119
5120 EXCEPTION
5121
5122 WHEN NO_DATA_FOUND THEN
5123
5124 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5125 THEN
5126
5127 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5128 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','departure');
5129 OE_MSG_PUB.Add;
5130
5131 END IF;
5132
5133 RETURN NULL;
5134
5135 WHEN OTHERS THEN
5136
5137 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5138 THEN
5139 OE_MSG_PUB.Add_Exc_Msg
5140 ( G_PKG_NAME
5141 , 'Departure'
5142 );
5143 END IF;
5144
5145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5146
5147 END Departure;
5148
5149 FUNCTION Estimated
5150 ( p_estimated_flag IN VARCHAR2
5151 ) RETURN VARCHAR2
5152 IS
5153 l_estimated VARCHAR2(240) := NULL;
5154 --
5155 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5156 --
5157 BEGIN
5158
5159 IF p_estimated_flag IS NOT NULL THEN
5160
5161 -- SELECT ESTIMATED
5162 -- INTO l_estimated
5163 -- FROM DB_TABLE
5164 -- WHERE DB_COLUMN = p_estimated_flag;
5165
5166 NULL;
5167
5168 END IF;
5169
5170 RETURN l_estimated;
5171
5172 EXCEPTION
5173
5174 WHEN NO_DATA_FOUND THEN
5175
5176 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5177 THEN
5178
5179 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5180 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','estimated');
5181 OE_MSG_PUB.Add;
5182
5183 END IF;
5184
5185 RETURN NULL;
5186
5187 WHEN OTHERS THEN
5188
5189 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5190 THEN
5191 OE_MSG_PUB.Add_Exc_Msg
5192 ( G_PKG_NAME
5193 , 'Estimated'
5194 );
5195 END IF;
5196
5197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5198
5199 END Estimated;
5200
5201 FUNCTION Invoiced
5202 ( p_invoiced_flag IN VARCHAR2
5203 ) RETURN VARCHAR2
5204 IS
5205 l_invoiced VARCHAR2(240) := NULL;
5206 --
5207 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5208 --
5209 BEGIN
5210
5211 IF p_invoiced_flag IS NOT NULL THEN
5212
5213 -- SELECT INVOICED
5214 -- INTO l_invoiced
5215 -- FROM DB_TABLE
5216 -- WHERE DB_COLUMN = p_invoiced_flag;
5217
5218 NULL;
5219
5220 END IF;
5221
5222 RETURN l_invoiced;
5223
5224 EXCEPTION
5225
5226 WHEN NO_DATA_FOUND THEN
5227
5228 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5229 THEN
5230
5231 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5232 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoiced');
5233 OE_MSG_PUB.Add;
5234
5235 END IF;
5236
5237 RETURN NULL;
5238
5239 WHEN OTHERS THEN
5240
5241 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5242 THEN
5243 OE_MSG_PUB.Add_Exc_Msg
5244 ( G_PKG_NAME
5245 , 'Invoiced'
5246 );
5247 END IF;
5248
5249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5250
5251 END Invoiced;
5252
5253 FUNCTION Parent_Charge
5254 ( p_parent_charge_id IN NUMBER
5255 ) RETURN VARCHAR2
5256 IS
5257 l_parent_charge VARCHAR2(240) := NULL;
5258 --
5259 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5260 --
5261 BEGIN
5262
5263 IF p_parent_charge_id IS NOT NULL THEN
5264
5265 -- SELECT PARENT_CHARGE
5266 -- INTO l_parent_charge
5267 -- FROM DB_TABLE
5268 -- WHERE DB_COLUMN = p_parent_charge_id;
5269
5270 NULL;
5271
5272 END IF;
5273
5274 RETURN l_parent_charge;
5275
5276 EXCEPTION
5277
5278 WHEN NO_DATA_FOUND THEN
5279
5280 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5281 THEN
5282
5283 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5284 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','parent_charge');
5285 OE_MSG_PUB.Add;
5286
5287 END IF;
5288
5289 RETURN NULL;
5290
5291 WHEN OTHERS THEN
5292
5293 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5294 THEN
5295 OE_MSG_PUB.Add_Exc_Msg
5296 ( G_PKG_NAME
5297 , 'Parent_Charge'
5298 );
5299 END IF;
5300
5301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5302
5303 END Parent_Charge;
5304
5305 FUNCTION Returnable
5306 ( p_returnable_flag IN VARCHAR2
5307 ) RETURN VARCHAR2
5308 IS
5309 l_returnable VARCHAR2(240) := NULL;
5310 --
5311 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5312 --
5313 BEGIN
5314
5315 IF p_returnable_flag IS NOT NULL THEN
5316
5317 -- SELECT RETURNABLE
5318 -- INTO l_returnable
5319 -- FROM DB_TABLE
5320 -- WHERE DB_COLUMN = p_returnable_flag;
5321
5322 NULL;
5323
5324 END IF;
5325
5326 RETURN l_returnable;
5327
5328 EXCEPTION
5329
5330 WHEN NO_DATA_FOUND THEN
5331
5332 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5333 THEN
5334
5335 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5336 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','returnable');
5337 OE_MSG_PUB.Add;
5338
5339 END IF;
5340
5341 RETURN NULL;
5342
5343 WHEN OTHERS THEN
5344
5345 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5346 THEN
5347 OE_MSG_PUB.Add_Exc_Msg
5348 ( G_PKG_NAME
5349 , 'Returnable'
5350 );
5351 END IF;
5352
5353 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5354
5355 END Returnable;
5356 /* eBTax changes - the function Tax_Group would no longer be required.
5357 FUNCTION Tax_Group
5358 ( p_tax_code IN VARCHAR2
5359 ) RETURN VARCHAR2
5360 IS
5361 l_tax_group VARCHAR2(1) := NULL;
5362 --
5363 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5364 --
5365 BEGIN
5366
5367 IF p_tax_code IS NOT NULL THEN
5368
5369 SELECT DECODE(TAX_TYPE,'TAX_GROUP','Y',NULL)
5370 INTO l_tax_group
5371 FROM ar_vat_tax
5372 WHERE tax_code = p_tax_code
5373 AND ROWNUM = 1;
5374
5375 END IF;
5376
5377 RETURN l_tax_group;
5378
5379 EXCEPTION
5380
5381 WHEN NO_DATA_FOUND THEN
5382
5383 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5384 THEN
5385
5386 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5387 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','tax_group');
5388 OE_MSG_PUB.Add;
5389
5390 END IF;
5391
5392 RETURN NULL;
5393
5394 WHEN OTHERS THEN
5395
5396 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5397 THEN
5398 OE_MSG_PUB.Add_Exc_Msg
5399 ( G_PKG_NAME
5400 , 'Tax_Group'
5401 );
5402 END IF;
5403
5404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5405
5406 END Tax_Group;
5407 */
5408
5409 FUNCTION Flow_Status
5410 ( p_flow_status_code IN VARCHAR2
5411 ) RETURN VARCHAR2
5412 IS
5413 l_flow_status VARCHAR2(240) := NULL;
5414 l_lookup_type1 VARCHAR2(80) :='FLOW_STATUS';
5415 l_lookup_type2 VARCHAR2(80) :='LINE_FLOW_STATUS';
5416 --
5417 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5418 --
5419 BEGIN
5420
5421 IF p_flow_status_code IS NOT NULL THEN
5422
5423 SELECT distinct MEANING
5424 INTO l_flow_status
5425 FROM OE_LOOKUPS
5426 WHERE LOOKUP_CODE = p_flow_status_code
5427 AND (LOOKUP_TYPE = l_lookup_type1
5428 OR LOOKUP_TYPE = l_lookup_type2);
5429
5430 END IF;
5431
5432 RETURN l_flow_status;
5433
5434 EXCEPTION
5435
5436 WHEN NO_DATA_FOUND THEN
5437
5438 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5439 THEN
5440
5441 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5442 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','flow_status');
5443 OE_MSG_PUB.Add;
5444
5445 END IF;
5446
5447 RETURN NULL;
5448
5449 WHEN OTHERS THEN
5450
5451 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5452 THEN
5453 OE_MSG_PUB.Add_Exc_Msg
5454 ( G_PKG_NAME
5455 , 'Flow_Status'
5456 );
5457 END IF;
5458
5459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5460
5461 END Flow_Status;
5462
5463 FUNCTION Freight_Carrier
5464 ( p_freight_carrier_code IN VARCHAR2
5465 , p_ship_from_org_id IN NUMBER
5466 ) RETURN VARCHAR2
5467 IS
5468 l_freight_carrier VARCHAR2(240) := NULL;
5469 l_ship_from_org_id NUMBER;
5470 --
5471 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5472 --
5473 BEGIN
5474
5475 IF p_ship_from_org_id = FND_API.G_MISS_NUM THEN
5476 l_ship_from_org_id := NULL;
5477 ELSE
5478 l_ship_from_org_id := p_ship_from_org_id;
5479 END IF;
5480
5481 IF p_freight_carrier_code IS NOT NULL THEN
5482
5483 SELECT DESCRIPTION
5484 INTO l_freight_carrier
5485 FROM ORG_FREIGHT
5486 WHERE FREIGHT_CODE = p_freight_carrier_code
5487 AND ORGANIZATION_ID = nvl(l_ship_from_org_id,ORGANIZATION_ID);
5488
5489 END IF;
5490
5491 RETURN l_freight_carrier;
5492
5493 EXCEPTION
5494
5495 WHEN TOO_MANY_ROWS THEN
5496 -- NULL;
5497 RETURN NULL; -- Added for the bug 3554864
5498
5499 WHEN NO_DATA_FOUND THEN
5500
5501 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5502 THEN
5503
5504 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5505 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','freight_carrier');
5506 OE_MSG_PUB.Add;
5507
5508 END IF;
5509
5510 RETURN NULL;
5511
5512 WHEN OTHERS THEN
5513
5514 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5515 THEN
5516 OE_MSG_PUB.Add_Exc_Msg
5517 ( G_PKG_NAME
5518 , 'Freight_Carrier'
5519 );
5520 END IF;
5521
5522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5523
5524 END Freight_Carrier;
5525
5526 FUNCTION Sales_Channel
5527 ( p_sales_channel_code IN VARCHAR2
5528 ) RETURN VARCHAR2
5529 IS
5530 l_sales_channel VARCHAR2(80);
5531 l_lookup_type VARCHAR2(80) := 'SALES_CHANNEL';
5532 --
5533 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5534 --
5535 BEGIN
5536
5537 IF p_sales_channel_code IS NOT NULL THEN
5538
5539 SELECT MEANING
5540 INTO l_sales_channel
5541 FROM OE_LOOKUPS
5542 WHERE LOOKUP_CODE = p_sales_channel_code
5543 AND LOOKUP_TYPE = l_lookup_type;
5544
5545 END IF;
5546
5547 RETURN l_sales_channel;
5548
5549 EXCEPTION
5550
5551 WHEN NO_DATA_FOUND THEN
5552
5553 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5554 THEN
5555
5556 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5557 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sales_channel');
5558 OE_MSG_PUB.Add;
5559
5560 END IF;
5561
5562 RETURN NULL;
5563
5564 WHEN OTHERS THEN
5565
5566 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5567 THEN
5568 OE_MSG_PUB.Add_Exc_Msg
5569 ( G_PKG_NAME
5570 , 'Sales_Channel'
5571 );
5572 END IF;
5573
5574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5575
5576 END Sales_Channel;
5577
5578 PROCEDURE Ship_To_Customer_Name
5579 ( p_ship_to_org_id IN NUMBER
5580 , x_ship_to_customer_name OUT NOCOPY VARCHAR2
5581
5582 )
5583
5584 IS
5585 l_site_use_code VARCHAR2(30);
5586 --
5587 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5588 --
5589 BEGIN
5590
5591 IF p_ship_to_org_id is NOT NULL THEN
5592
5593 l_site_use_code := 'SHIP_TO';
5594
5595 SELECT /* MOAC_SQL_CHANGE */ P.PARTY_NAME
5596 INTO x_ship_to_customer_name
5597 FROM HZ_CUST_SITE_USES_all site,
5598 HZ_CUST_ACCT_SITES cas,
5599 HZ_CUST_ACCOUNTS cust,
5600 HZ_PARTIES p
5601 WHERE site.cust_acct_site_id=cas.cust_acct_site_id
5602 AND cust.cust_account_id=cas.cust_account_id
5603 AND cust.party_id = p.party_id
5604 AND site.site_use_code=l_site_use_code
5605 AND site.site_use_id=p_ship_to_org_id;
5606
5607 ELSE
5608
5609 x_ship_to_customer_name := NULL ;
5610
5611 END IF;
5612
5613 EXCEPTION
5614
5615 WHEN NO_DATA_FOUND THEN
5616
5617 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5618 THEN
5619
5620 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5621 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_to_customer_name');
5622 OE_MSG_PUB.Add;
5623
5624 END IF;
5625
5626
5627 WHEN OTHERS THEN
5628
5629 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5630 THEN
5631 OE_MSG_PUB.Add_Exc_Msg
5632 ( G_PKG_NAME
5633 , 'Ship_To_customer_name'
5634 );
5635 END IF;
5636
5637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5638
5639 END Ship_To_Customer_Name;
5640
5641 PROCEDURE Invoice_To_Customer_Name
5642 ( p_invoice_to_org_id IN NUMBER
5643 , x_invoice_to_customer_name OUT NOCOPY VARCHAR2
5644
5645 )
5646
5647 IS
5648 l_site_use_code VARCHAR2(30);
5649 --
5650 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5651 --
5652 BEGIN
5653
5654 IF p_invoice_to_org_id is NOT NULL THEN
5655
5656 l_site_use_code := 'BILL_TO';
5657
5658 SELECT /* MOAC_SQL_CHANGE */ P.PARTY_NAME
5659 INTO x_invoice_to_customer_name
5660 FROM HZ_CUST_SITE_USES_ALL site,
5661 HZ_CUST_ACCT_SITES cas,
5662 HZ_CUST_ACCOUNTS cust,
5663 HZ_PARTIES p
5664 WHERE site.cust_acct_site_id=cas.cust_acct_site_id
5665 AND cust.cust_account_id=cas.cust_account_id
5666 AND cust.party_id = p.party_id
5667 AND site.site_use_code=l_site_use_code
5668 AND site.site_use_id=p_invoice_to_org_id;
5669
5670 ELSE
5671
5672 x_invoice_to_customer_name := NULL ;
5673
5674 END IF;
5675
5676 EXCEPTION
5677
5678 WHEN NO_DATA_FOUND THEN
5679
5680 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5681 THEN
5682
5683 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5684 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoice_to_customer_name');
5685 OE_MSG_PUB.Add;
5686
5687 END IF;
5688
5689
5690 WHEN OTHERS THEN
5691
5692 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5693 THEN
5694 OE_MSG_PUB.Add_Exc_Msg
5695 ( G_PKG_NAME
5696 , 'Invoice_To_customer_name'
5697 );
5698 END IF;
5699
5700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5701
5702 END Invoice_To_Customer_Name;
5703
5704 PROCEDURE Ordered_Item
5705 ( p_item_identifier_type IN VARCHAR2
5706 , p_inventory_item_id IN NUMBER
5707 , p_organization_id IN NUMBER
5708 , p_ordered_item_id IN NUMBER
5709 , p_sold_to_org_id IN NUMBER
5710 , p_ordered_item IN VARCHAR2
5711 , x_ordered_item OUT NOCOPY VARCHAR2
5712
5713 , x_inventory_item OUT NOCOPY VARCHAR2)
5714
5715 IS
5716 --
5717 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5718 --
5719 BEGIN
5720 IF l_debug_level > 0 THEN
5721 oe_debug_pub.add( 'ENTERING ORDERED_ITEM' , 1 ) ;
5722 END IF;
5723 IF NVL(p_item_identifier_type, 'INT') = 'INT' THEN
5724 BEGIN
5725 IF l_debug_level > 0 THEN
5726 oe_debug_pub.add( 'IN OEXSIDVB ITEM IDENTIFIER IS INT' ) ;
5727 END IF;
5728
5729 SELECT concatenated_segments
5730 ,concatenated_segments
5731 INTO x_ordered_item
5732 ,x_inventory_item
5733 FROM mtl_system_items_vl
5734 WHERE inventory_item_id = p_inventory_item_id
5735 AND organization_id = p_organization_id;
5736 IF l_debug_level > 0 THEN
5737 oe_debug_pub.add( 'ORDERED_ITEM_DSP: '||X_ORDERED_ITEM ) ;
5738 END IF;
5739
5740 EXCEPTION
5741 WHEN NO_DATA_FOUND THEN
5742 Null;
5743 When too_many_rows then
5744 Null;
5745 When others then
5746 Null;
5747 END;
5748 ELSIF NVL(p_item_identifier_type, 'INT') = 'CUST' THEN
5749 BEGIN
5750 IF l_debug_level > 0 THEN
5751 oe_debug_pub.add( 'IN OEXSIDVB ITEM IDENTIFIER IS CUST' ) ;
5752 END IF;
5753 SELECT citems.customer_item_number
5754 ,sitems.concatenated_segments
5755 INTO x_ordered_item
5756 ,x_inventory_item
5757 FROM mtl_customer_items citems
5758 ,mtl_customer_item_xrefs cxref
5759 ,mtl_system_items_vl sitems
5760 WHERE citems.customer_item_id = cxref.customer_item_id
5761 AND cxref.inventory_item_id = sitems.inventory_item_id
5762 AND sitems.inventory_item_id = p_inventory_item_id
5763 AND sitems.organization_id = p_organization_id
5764 AND citems.customer_item_id = p_ordered_item_id
5765 AND citems.customer_id = p_sold_to_org_id;
5766 EXCEPTION
5767 WHEN NO_DATA_FOUND THEN
5768 Null;
5769 When too_many_rows then
5770 Null;
5771 When others then
5772 Null;
5773 END;
5774 ELSE
5775 BEGIN
5776 IF l_debug_level > 0 THEN
5777 oe_debug_pub.add( 'IN OEXSIDVB ITEM IDENTIFIER IS GENE' ) ;
5778 END IF;
5779 IF p_ordered_item_id IS NULL THEN
5780 IF l_debug_level > 0 THEN
5781 oe_debug_pub.add( 'ORDERED_ITEM_ID IS NULL ' ) ;
5782 oe_debug_pub.add( 'ORDERED_ITEM: '||P_ORDERED_ITEM ) ;
5783 END IF;
5784 SELECT items.cross_reference
5785 ,sitems.concatenated_segments
5786 INTO x_ordered_item
5787 ,x_inventory_item
5788 FROM mtl_cross_reference_types types
5789 , mtl_cross_references items
5790 , mtl_system_items_vl sitems
5791 WHERE types.cross_reference_type = items.cross_reference_type
5792 AND items.inventory_item_id = sitems.inventory_item_id
5793 AND sitems.organization_id = p_organization_id
5794 AND sitems.inventory_item_id = p_inventory_item_id
5795 AND items.cross_reference_type = p_item_identifier_type
5796 AND items.cross_reference = p_ordered_item;
5797 END IF;
5798
5799 EXCEPTION
5800 WHEN NO_DATA_FOUND THEN
5801 Null;
5802 When too_many_rows then
5803 Null;
5804 When others then
5805 Null;
5806 END;
5807 END IF;
5808 IF l_debug_level > 0 THEN
5809 oe_debug_pub.add( 'ORDERED_ITEM_DSP: '||X_ORDERED_ITEM ) ;
5810 END IF;
5811 EXCEPTION
5812
5813 WHEN NO_DATA_FOUND THEN
5814
5815 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5816 THEN
5817
5818 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5819 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Ordered_Item');
5820 OE_MSG_PUB.Add;
5821
5822 END IF;
5823
5824
5825 WHEN OTHERS THEN
5826
5827 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5828 THEN
5829 OE_MSG_PUB.Add_Exc_Msg
5830 ( G_PKG_NAME
5831 , 'Ordered_Item'
5832 );
5833 END IF;
5834
5835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5836
5837 END Ordered_Item;
5838
5839 PROCEDURE Item_Identifier
5840 (p_Item_Identifier_type IN VARCHAR2
5841 , x_Item_Identifier OUT NOCOPY VARCHAR2)
5842
5843 IS
5844 --
5845 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5846 l_lookup_type VARCHAR2(80) :='ITEM_IDENTIFIER_TYPE';
5847 --
5848 BEGIN
5849 IF l_debug_level > 0 THEN
5850 oe_debug_pub.add( 'ENTERING ITEM_IDENTIFIER' , 1 ) ;
5851 END IF;
5852 IF p_Item_Identifier_type in ('INT','CUST') THEN
5853 Select meaning
5854 Into x_Item_Identifier
5855 From oe_lookups
5856 Where lookup_type = l_lookup_type
5857 And lookup_code = p_Item_Identifier_type;
5858 ELSE
5859 x_Item_Identifier := p_Item_Identifier_type;
5860
5861 END IF;
5862
5863 IF l_debug_level > 0 THEN
5864 oe_debug_pub.add( 'ITEM_IDENTIFIER' || X_ITEM_IDENTIFIER , 1 ) ;
5865 END IF;
5866 EXCEPTION
5867
5868 WHEN NO_DATA_FOUND THEN
5869
5870 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5871 THEN
5872
5873 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5874 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Item_Identifier');
5875 OE_MSG_PUB.Add;
5876
5877 END IF;
5878
5879
5880 WHEN OTHERS THEN
5881
5882 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5883 THEN
5884 OE_MSG_PUB.Add_Exc_Msg
5885 ( G_PKG_NAME
5886 , 'Item_Identifier'
5887 );
5888 END IF;
5889
5890 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5891
5892 END Item_Identifier;
5893
5894 PROCEDURE Item_Relationship_Type
5895 (p_Item_Relationship_Type IN NUMBER
5896 , x_Item_Relationship_Type_Dsp OUT nocopy VARCHAR2)
5897 IS
5898 --
5899 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5900 l_lookup_type VARCHAR2(80) :='MTL_RELATIONSHIP_TYPES';
5901 --
5902 BEGIN
5903 IF l_debug_level > 0 THEN
5904 oe_debug_pub.add('Entering Item_Relationship_Type',1);
5905 END IF;
5906 IF p_Item_Relationship_Type IS NOT NULL THEN
5907 Select meaning
5908 Into x_Item_Relationship_Type_dsp
5909 From mfg_lookups
5910 Where lookup_type = l_lookup_type
5911 And lookup_code = p_Item_Relationship_Type;
5912 ELSE
5913 x_Item_Relationship_Type_Dsp := null;
5914
5915 IF l_debug_level > 0 THEN
5916 oe_debug_pub.add('in else Item_Relationship_Type' || x_Item_Relationship_Type_Dsp,1);
5917 END IF;
5918 END IF;
5919
5920 IF l_debug_level > 0 THEN
5921 oe_debug_pub.add('Item_Relationship_Type_dsp' || x_Item_Relationship_Type_Dsp,1);
5922 END IF;
5923 EXCEPTION
5924
5925 WHEN NO_DATA_FOUND THEN
5926
5927 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5928 THEN
5929
5930 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5931 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Item_Relationship_Type');
5932 OE_MSG_PUB.Add;
5933
5934 END IF;
5935
5936
5937 WHEN OTHERS THEN
5938
5939 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5940 THEN
5941 OE_MSG_PUB.Add_Exc_Msg
5942 ( G_PKG_NAME
5943 , 'Item_Relationship_Type'
5944 );
5945 END IF;
5946
5947 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5948
5949 END Item_Relationship_Type;
5950
5951 FUNCTION User_Status
5952 ( p_user_status_code IN VARCHAR2
5953 ) RETURN VARCHAR2
5954 IS
5955 l_user_status VARCHAR2(240) := NULL;
5956 --
5957 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5958 l_lookup_type VARCHAR2(80) := 'USER_STATUS';
5959 --
5960 BEGIN
5961
5962 IF l_debug_level > 0 THEN
5963 oe_debug_pub.add( 'IN GET VALUES ' || p_user_status_code ) ;
5964 END IF;
5965 IF p_user_status_code IS NOT NULL THEN
5966
5967 SELECT MEANING
5968 INTO l_user_status
5969 FROM OE_LOOKUPS
5970 WHERE LOOKUP_CODE = p_user_status_code
5971 AND LOOKUP_TYPE = l_lookup_type;
5972
5973
5974 END IF;
5975
5976 IF l_debug_level > 0 THEN
5977 oe_debug_pub.add( 'IN GET VALUES ' || L_user_status ) ;
5978 END IF;
5979 RETURN l_user_status;
5980
5981 EXCEPTION
5982
5983 WHEN NO_DATA_FOUND THEN
5984
5985 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5986 THEN
5987
5988 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5989 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','user_status');
5990 OE_MSG_PUB.Add;
5991
5992 END IF;
5993
5994 RETURN NULL;
5995
5996 WHEN OTHERS THEN
5997
5998 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5999 THEN
6000 OE_MSG_PUB.Add_Exc_Msg
6001 ( G_PKG_NAME
6002 , 'user_status'
6003 );
6004 END IF;
6005
6006 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6007
6008 END User_status;
6009
6010
6011 FUNCTION Transaction_Phase
6012 ( p_Transaction_Phase_code IN VARCHAR2
6013 ) RETURN VARCHAR2
6014 IS
6015 l_Transaction_Phase VARCHAR2(240) := NULL;
6016 --
6017 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6018 l_lookup_type VARCHAR2(80) :='TRANSACTION_PHASE';
6019 --
6020 BEGIN
6021
6022 IF l_debug_level > 0 THEN
6023 oe_debug_pub.add( 'IN GET VALUES ' || p_Transaction_Phase_code ) ;
6024 END IF;
6025 IF p_Transaction_Phase_code IS NOT NULL THEN
6026
6027 SELECT MEANING
6028 INTO l_Transaction_Phase
6029 FROM OE_LOOKUPS
6030 WHERE LOOKUP_CODE = p_Transaction_Phase_code
6031 AND LOOKUP_TYPE = l_lookup_type;
6032
6033
6034 END IF;
6035
6036 IF l_debug_level > 0 THEN
6037 oe_debug_pub.add( 'IN GET VALUES ' || L_Transaction_Phase ) ;
6038 END IF;
6039 RETURN l_Transaction_Phase;
6040
6041 EXCEPTION
6042
6043 WHEN NO_DATA_FOUND THEN
6044
6045 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6046 THEN
6047
6048 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6049 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','transaction_phase');
6050 OE_MSG_PUB.Add;
6051
6052 END IF;
6053
6054 RETURN NULL;
6055
6056 WHEN OTHERS THEN
6057
6058 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6059 THEN
6060 OE_MSG_PUB.Add_Exc_Msg
6061 ( G_PKG_NAME
6062 , 'transaction_phase'
6063 );
6064 END IF;
6065
6066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6067
6068 END Transaction_Phase;
6069 PROCEDURE Customer_Location
6070 (
6071 p_sold_to_site_use_id IN NUMBER
6072 , x_sold_to_location_address1 OUT NOCOPY VARCHAR2
6073 , x_sold_to_location_address2 OUT NOCOPY VARCHAR2
6074 , x_sold_to_location_address3 OUT NOCOPY VARCHAR2
6075 , x_sold_to_location_address4 OUT NOCOPY VARCHAR2
6076 , x_sold_to_location OUT NOCOPY VARCHAR2
6077 , x_sold_to_location_city OUT NOCOPY VARCHAR2
6078 , x_sold_to_location_state OUT NOCOPY VARCHAR2
6079 , x_sold_to_location_postal OUT NOCOPY VARCHAR2
6080 , x_sold_to_location_country OUT NOCOPY VARCHAR2
6081 )
6082 IS
6083 --
6084 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6085 --
6086 BEGIN
6087
6088 IF p_sold_to_site_use_id is NOT NULL THEN
6089
6090 SELECT /* MOAC_SQL_CHANGE */
6091 SITE.LOCATION
6092 , LOC.ADDRESS1
6093 , LOC.ADDRESS2
6094 , LOC.ADDRESS3
6095 , LOC.ADDRESS4
6096 , LOC.CITY
6097 , nvl(LOC.STATE,LOC.PROVINCE) -- 3603600
6098 , LOC.POSTAL_CODE
6099 , LOC.COUNTRY
6100 INTO
6101 x_sold_to_location
6102 , x_sold_to_location_address1
6103 , x_sold_to_location_address2
6104 , x_sold_to_location_address3
6105 , x_sold_to_location_address4
6106 , x_sold_to_location_city
6107 , x_sold_to_location_state
6108 , x_sold_to_location_postal
6109 , x_sold_to_location_country
6110
6111 FROM
6112 HZ_CUST_SITE_USES_All SITE,
6113 HZ_PARTY_SITES PARTY_SITE,
6114 HZ_LOCATIONS LOC,
6115 HZ_CUST_ACCT_SITES ACCT_SITE
6116 WHERE
6117 SITE.SITE_USE_CODE = 'SOLD_TO'
6118 AND SITE.SITE_USE_ID = p_sold_to_site_use_id
6119 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
6120 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
6121 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
6122
6123 ELSE
6124
6125 x_sold_to_location := NULL ;
6126 x_sold_to_location_address1 := NULL ;
6127 x_sold_to_location_address2 := NULL ;
6128 x_sold_to_location_address3 := NULL ;
6129 x_sold_to_location_address4 := NULL ;
6130 x_sold_to_location_city := NULL ;
6131 x_sold_to_location_state := NULL ;
6132 x_sold_to_location_postal := NULL ;
6133 x_sold_to_location_country := NULL ;
6134
6135 END IF;
6136
6137 EXCEPTION
6138
6139 WHEN NO_DATA_FOUND THEN
6140
6141 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6142 THEN
6143
6144 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6145 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Customer_Location');
6146 OE_MSG_PUB.Add;
6147
6148 END IF;
6149
6150
6151 WHEN OTHERS THEN
6152
6153 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6154 THEN
6155 OE_MSG_PUB.Add_Exc_Msg
6156 ( G_PKG_NAME
6157 , 'Customer_Location'
6158 );
6159 END IF;
6160
6161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6162
6163 END Customer_Location;
6164
6165 /*-----------------------------------------------------------------
6166 PROCEDURE Get_Contact_Details
6167
6168 added for pack J enhanced dropship project.
6169 ------------------------------------------------------------------*/
6170 PROCEDURE Get_Contact_Details
6171 ( p_contact_id IN NUMBER
6172 ,x_contact_name OUT NOCOPY VARCHAR2
6173 ,x_phone_line_type OUT NOCOPY VARCHAR2
6174 ,x_phone_number OUT NOCOPY VARCHAR2
6175 ,x_email_address OUT NOCOPY VARCHAR2)
6176 IS
6177 BEGIN
6178
6179 SELECT SUBSTRB(CONTACT_PARTY.PARTY_NAME,1,70) CONTACT_NAME,
6180 DECODE(arl.meaning,NULL, NULL, ' '||arl.meaning) Phone_Type,
6181 DECODE(CONTACT.phone_country_code, NULL, NULL,
6182 CONTACT.phone_country_code || '- ') ||
6183 DECODE(CONTACT.phone_area_code, NULL, NULL, CONTACT.phone_area_code || '-')||
6184 DECODE(CONTACT.phone_number, NULL, NULL, CONTACT.phone_number ) phone,
6185 REL_PARTY.email_address
6186 INTO x_contact_name,
6187 x_phone_line_type,
6188 x_phone_number,
6189 x_email_address
6190 FROM HZ_CONTACT_POINTS CONTACT,
6191 HZ_PARTIES CONTACT_PARTY,
6192 HZ_CUST_ACCOUNT_ROLES ACCT_ROLES,
6193 HZ_CUST_ACCOUNTS CUST_ACCT,
6194 HZ_RELATIONSHIPS PARTY_REL,
6195 HZ_PARTIES REL_PARTY,
6196 AR_LOOKUPS ARL
6197 WHERE CONTACT.owner_table_name(+) = 'HZ_PARTIES'
6198 AND CONTACT.PRIMARY_FLAG (+) = 'Y'
6199 AND CONTACT.contact_point_type (+) = 'PHONE'
6200 AND ACCT_ROLES.PARTY_ID = CONTACT.owner_table_id(+)
6201 AND ACCT_ROLES.cust_account_role_id = p_contact_id
6202 AND PARTY_REL.PARTY_ID = ACCT_ROLES.PARTY_ID
6203 AND PARTY_REL.PARTY_ID = REL_PARTY.PARTY_ID
6204 AND PARTY_REL.SUBJECT_ID = CONTACT_PARTY.PARTY_ID
6205 AND PARTY_REL.OBJECT_ID = CUST_ACCT.PARTY_ID
6206 AND ACCT_ROLES.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
6207 AND CONTACT.status(+) = 'A'
6208 AND ACCT_ROLES.STATUS = 'A'
6209 AND ARL.lookup_type (+) = 'PHONE_LINE_TYPE'
6210 AND ARL.lookup_code(+) = CONTACT.phone_line_type;
6211
6212 EXCEPTION
6213 WHEN others THEN
6214 oe_debug_pub.add('Get_Contact_Details ' || sqlerrm, 1);
6215 END Get_Contact_Details;
6216
6217 --serla begin
6218 FUNCTION payment_collection_event_name
6219 ( p_payment_collection_event IN VARCHAR2
6220 ) RETURN VARCHAR2
6221 IS
6222 l_lookup_type VARCHAR2(80) := 'OE_PAYMENT_COLLECTION_TYPE';
6223 l_payment_collection_event VARCHAR2(80);
6224 BEGIN
6225
6226 IF p_payment_collection_event IS NOT NULL THEN
6227
6228 SELECT MEANING
6229 INTO l_payment_collection_event
6230 FROM OE_LOOKUPS
6231 WHERE LOOKUP_CODE = p_payment_collection_event
6232 AND LOOKUP_TYPE = l_lookup_type;
6233
6234 END IF;
6235
6236 RETURN l_payment_collection_event;
6237
6238 EXCEPTION
6239
6240 WHEN NO_DATA_FOUND THEN
6241
6242 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6243 THEN
6244
6245 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6246 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','payment_collection_event_name');
6247 OE_MSG_PUB.Add;
6248
6249 END IF;
6250
6251
6252 WHEN OTHERS THEN
6253
6254 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6255 THEN
6256 OE_MSG_PUB.Add_Exc_Msg
6257 ( G_PKG_NAME
6258 , 'payment_collection_event_name'
6259 );
6260 END IF;
6261
6262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6263
6264 END payment_collection_event_name;
6265
6266 FUNCTION Receipt_Method
6267 ( p_receipt_method IN NUMBER
6268 ) RETURN VARCHAR2
6269 IS
6270 l_receipt_method VARCHAR2(80);
6271 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6272 l_err_message VARCHAR2(2000);
6273 BEGIN
6274
6275 IF l_debug_level > 0 THEN
6276 oe_debug_pub.add('Receipt method id....ksurendr'||p_receipt_method);
6277 END IF;
6278
6279 IF p_receipt_method IS NOT NULL AND p_receipt_method <> 0 AND
6280 NOT OE_GLOBALS.Equal(p_receipt_method,FND_API.G_MISS_NUM) THEN
6281 IF l_debug_level > 0 THEN
6282 oe_debug_pub.add('Inside if part....');
6283 END IF;
6284 SELECT NAME
6285 INTO l_receipt_method
6286 FROM AR_RECEIPT_METHODS
6287 WHERE receipt_method_id = p_receipt_method;
6288 --bug 5204358
6289 ELSIF p_receipt_method = 0 THEN
6290 IF l_debug_level > 0 THEN
6291 oe_debug_pub.add('Inside else part....');
6292 END IF;
6293 fnd_message.set_name('ONT','OE_VPM_NO_PAY_METHOD');
6294 OE_MSG_PUB.Add;
6295 RAISE FND_API.G_EXC_ERROR;
6296 END IF;
6297
6298 RETURN l_receipt_method;
6299
6300 EXCEPTION
6301
6302 WHEN NO_DATA_FOUND THEN
6303
6304 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6305 THEN
6306
6307 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6308 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Receipt_Method');
6309 OE_MSG_PUB.Add;
6310
6311 END IF;
6312
6313 WHEN FND_API.G_EXC_ERROR THEN
6314 l_err_message := SQLERRM;
6315 IF l_debug_level > 0 THEN
6316 oe_debug_pub.add('Id to value error for receipt method....exc');
6317 oe_debug_pub.add('Error'||l_err_message);
6318 END IF;
6319
6320 RAISE FND_API.G_EXC_ERROR;
6321
6322 WHEN OTHERS THEN
6323
6324 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6325 THEN
6326 OE_MSG_PUB.Add_Exc_Msg
6327 ( G_PKG_NAME
6328 , 'Receipt_Method'
6329 );
6330 END IF;
6331
6332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6333
6334 END Receipt_Method;
6335 --serla end
6336
6337 FUNCTION get_sales_group_name
6338 (p_sales_group_id IN NUMBER
6339 )RETURN VARCHAR2 IS
6340
6341 l_sales_group_name VARCHAR2(80);
6342 BEGIN
6343
6344 IF p_sales_group_id IS NOT NULL THEN
6345 Select group_name into l_sales_group_name
6346 From jtf_rs_groups_vl
6347 Where Group_id=p_sales_group_id;
6348 ELSE
6349 Oe_Debug_Pub.add('Input sales group id is null');
6350 END IF;
6351
6352 RETURN l_sales_group_name;
6353
6354 EXCEPTION
6355
6356 WHEN NO_DATA_FOUND THEN
6357 IF p_sales_group_id <> -1 THEN
6358 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6359 THEN
6360
6361 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6362 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sales_group_name');
6363 OE_MSG_PUB.Add;
6364
6365 END IF;
6366 ELSE
6367 oe_debug_pub.add('Sales Group, -1 sales group id');
6368 END IF;
6369
6370
6371 WHEN OTHERS THEN
6372
6373 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6374 THEN
6375 OE_MSG_PUB.Add_Exc_Msg
6376 ( G_PKG_NAME
6377 , 'sales_group_name'
6378 );
6379 END IF;
6380
6381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6382
6383 END get_sales_group_name;
6384
6385 FUNCTION end_customer_Contact
6386 ( p_end_customer_contact_id IN NUMBER
6387 ) RETURN VARCHAR2
6388 IS
6389 l_end_customer_contact VARCHAR2(240) := NULL;
6390 --
6391 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6392 --
6393 BEGIN
6394
6395 IF p_end_customer_contact_id IS NOT NULL THEN
6396
6397 SELECT NAME
6398 INTO l_end_customer_contact
6399 FROM OE_CONTACTS_V
6400 WHERE CONTACT_ID = p_end_customer_contact_id;
6401
6402 END IF;
6403
6404 RETURN l_end_customer_contact;
6405
6406 EXCEPTION
6407
6408 WHEN NO_DATA_FOUND THEN
6409
6410 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6411 THEN
6412
6413 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6414 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_contact');
6415 OE_MSG_PUB.Add;
6416
6417 END IF;
6418
6419 RETURN NULL;
6420
6421 WHEN OTHERS THEN
6422
6423 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6424 THEN
6425 OE_MSG_PUB.Add_Exc_Msg
6426 ( G_PKG_NAME
6427 , 'End_Customer_Contact'
6428 );
6429 END IF;
6430
6431 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6432
6433 END End_Customer_Contact;
6434
6435 PROCEDURE End_Customer
6436 ( p_end_customer_id IN NUMBER ,
6437 x_end_customer_name OUT NOCOPY VARCHAR2 ,
6438 x_end_customer_number OUT NOCOPY VARCHAR2
6439 )
6440 IS
6441 --
6442 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6443 --
6444 BEGIN
6445
6446 IF p_end_customer_id is NOT NULL THEN
6447
6448 IF l_debug_level > 0 THEN
6449 oe_debug_pub.add( 'DOING SELECT FOR END_CUSTOMER_NUMBER' ) ;
6450 END IF;
6451 SELECT O.NAME
6452 , O.CUSTOMER_NUMBER
6453 INTO x_end_customer_name
6454 , x_end_customer_number
6455 FROM OE_SOLD_TO_ORGS_V O
6456 WHERE O.ORGANIZATION_ID = p_end_customer_id;
6457
6458 ELSE
6459
6460 IF l_debug_level > 0 THEN
6461 oe_debug_pub.add( 'INSIDE QUERY FOR END CUSTOMER NUMBER - NO ORG_ID PASSED' ) ;
6462 END IF;
6463 x_end_customer_name := NULL ;
6464 x_end_customer_number := NULL ;
6465
6466 END IF;
6467
6468 EXCEPTION
6469
6470 WHEN NO_DATA_FOUND THEN
6471
6472 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6473 THEN
6474
6475 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6476 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer');
6477 OE_MSG_PUB.Add;
6478
6479 END IF;
6480
6481
6482 WHEN OTHERS THEN
6483
6484 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6485 THEN
6486 OE_MSG_PUB.Add_Exc_Msg
6487 ( G_PKG_NAME
6488 , 'end_customer'
6489 );
6490 END IF;
6491
6492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6493
6494 END end_customer;
6495
6496 PROCEDURE end_customer_site_use
6497 ( p_end_customer_site_use_id IN NUMBER
6498 , x_end_customer_address1 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6499 , x_end_customer_address2 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6500 , x_end_customer_address3 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6501 , x_end_customer_address4 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6502 , x_end_customer_location OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6503 , x_end_customer_city OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6504 , x_end_customer_state OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6505 , x_end_customer_postal_code OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6506 , x_end_customer_country OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6507 )
6508 IS
6509 --
6510 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6511 --
6512 BEGIN
6513
6514 IF p_end_customer_site_use_id is NOT NULL THEN
6515
6516 SELECT /* MOAC_SQL_CHANGE */
6517 loc.address1
6518 ,loc.address2
6519 ,loc.address3
6520 ,loc.address4
6521 ,site_use.location
6522 ,loc.city
6523 ,nvl(loc.state,loc.province) -- 3603600
6524 ,loc.postal_code
6525 ,loc.country
6526 INTO
6527 x_end_customer_address1
6528 ,x_end_customer_address2
6529 ,x_end_customer_address3
6530 ,x_end_customer_address4
6531 ,x_end_customer_location
6532 ,x_end_customer_city
6533 ,x_end_customer_state
6534 ,x_end_customer_postal_code
6535 ,x_end_customer_country
6536 FROM
6537 hz_locations loc,
6538 hz_party_sites site,
6539 hz_cust_site_uses_all site_use,
6540 hz_cust_acct_sites acct_site
6541 WHERE
6542 site_use.site_use_id= p_end_customer_site_use_id
6543 and site_use.cust_acct_site_id=acct_site.cust_acct_site_id
6544 and acct_site.party_site_id=site.party_site_id
6545 and site.location_id=loc.location_id;
6546
6547 ELSE
6548 x_end_customer_address1 := NULL;
6549 x_end_customer_address2 := NULL;
6550 x_end_customer_address3 := NULL;
6551 x_end_customer_address4 := NULL;
6552 x_end_customer_location := NULL;
6553 x_end_customer_city := NULL;
6554 x_end_customer_state := NULL;
6555 x_end_customer_postal_code := NULL;
6556 x_end_customer_country := NULL;
6557 END IF;
6558
6559 EXCEPTION
6560
6561 WHEN NO_DATA_FOUND THEN
6562
6563 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6564 THEN
6565
6566 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6567 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_site');
6568 OE_MSG_PUB.Add;
6569
6570 END IF;
6571
6572
6573 WHEN OTHERS THEN
6574
6575 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6576 THEN
6577 OE_MSG_PUB.Add_Exc_Msg
6578 ( G_PKG_NAME
6579 , 'end_customer_site_use'
6580 );
6581 END IF;
6582
6583 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6584
6585 END end_customer_site_use;
6586
6587 --Macd
6588
6589 FUNCTION IB_OWNER
6590 ( p_ib_owner IN VARCHAR2
6591 ) RETURN VARCHAR2
6592 IS
6593 l_ib_owner_dsp VARCHAR2(60) := NULL;
6594 l_lookup_type1 VARCHAR2(80) :='ITEM_OWNER';
6595 l_lookup_type2 VARCHAR2(80) :='ONT_INSTALL_BASE';
6596 --
6597 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6598 --
6599 BEGIN
6600
6601 IF p_ib_owner IS NOT NULL THEN
6602
6603 SELECT meaning
6604 INTO l_ib_owner_dsp
6605 FROM OE_LOOKUPS
6606 WHERE lookup_code= p_ib_owner and
6607 (lookup_type=l_lookup_type1 or lookup_type=l_lookup_type2);
6608
6609 END IF;
6610
6611 RETURN l_ib_owner_dsp;
6612
6613 EXCEPTION
6614
6615 WHEN NO_DATA_FOUND THEN
6616
6617 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6618 THEN
6619
6620 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6621 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','IB_OWNER');
6622 OE_MSG_PUB.Add;
6623
6624 END IF;
6625
6626 RETURN NULL;
6627
6628 WHEN OTHERS THEN
6629
6630 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6631 THEN
6632 OE_MSG_PUB.Add_Exc_Msg
6633 ( G_PKG_NAME
6634 , 'IB_OWNER'
6635 );
6636 END IF;
6637
6638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6639
6640 END IB_OWNER;
6641
6642 FUNCTION IB_CURRENT_LOCATION
6643 ( p_ib_current_location IN VARCHAR2
6644 ) RETURN VARCHAR2
6645 IS
6646 l_ib_current_location_dsp VARCHAR2(60) := NULL;
6647 l_lookup_type1 VARCHAR2(80) :='ITEM_CURRENT_LOCATION';
6648 l_lookup_type2 VARCHAR2(80) :='ONT_INSTALL_BASE';
6649 --
6650 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6651 --
6652 BEGIN
6653
6654 IF p_ib_current_location IS NOT NULL THEN
6655
6656 SELECT meaning
6657 INTO l_ib_current_location_dsp
6658 FROM OE_LOOKUPS
6659 WHERE lookup_code= p_ib_current_location and
6660 (lookup_type=l_lookup_type1 or lookup_type=l_lookup_type2);
6661
6662 END IF;
6663
6664 RETURN l_ib_current_location_dsp;
6665
6666 EXCEPTION
6667
6668 WHEN NO_DATA_FOUND THEN
6669
6670 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6671 THEN
6672
6673 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6674 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','IB_CURRENT_LOCATION');
6675 OE_MSG_PUB.Add;
6676
6677 END IF;
6678
6679 RETURN NULL;
6680
6681 WHEN OTHERS THEN
6682
6683 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6684 THEN
6685 OE_MSG_PUB.Add_Exc_Msg
6686 ( G_PKG_NAME
6687 , 'IB_CURRENT_LOCATION'
6688 );
6689 END IF;
6690
6691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6692
6693 END IB_CURRENT_LOCATION;
6694
6695 FUNCTION IB_INSTALLED_AT_LOCATION
6696 ( p_ib_installed_at_location IN VARCHAR2
6697 ) RETURN VARCHAR2
6698 IS
6699 l_ib_installed_at_location_dsp VARCHAR2(60) := NULL;
6700 l_lookup_type1 VARCHAR2(80) :='ITEM_INSTALL_LOCATION';
6701 l_lookup_type2 VARCHAR2(80) :='ONT_INSTALL_BASE';
6702 --
6703 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6704 --
6705 BEGIN
6706
6707 IF p_ib_installed_at_location IS NOT NULL THEN
6708
6709 SELECT meaning
6710 INTO l_ib_installed_at_location_dsp
6711 FROM OE_LOOKUPS
6712 WHERE lookup_code= p_ib_installed_at_location and
6713 (lookup_type=l_lookup_type1 or lookup_type=l_lookup_type2);
6714
6715 END IF;
6716
6717 RETURN l_ib_installed_at_location_dsp;
6718
6719 EXCEPTION
6720
6721 WHEN NO_DATA_FOUND THEN
6722
6723 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6724 THEN
6725
6726 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6727 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','IB_INSTALLED_AT_LOCATION');
6728 OE_MSG_PUB.Add;
6729
6730 END IF;
6731
6732 RETURN NULL;
6733
6734 WHEN OTHERS THEN
6735
6736 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6737 THEN
6738 OE_MSG_PUB.Add_Exc_Msg
6739 ( G_PKG_NAME
6740 , 'IB_INSTALLED_AT_LOCATION'
6741 );
6742 END IF;
6743
6744 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6745
6746 END IB_INSTALLED_AT_LOCATION;
6747 --Macd
6748
6749 --Recurring CHarges
6750 FUNCTION Charge_Periodicity
6751 ( p_charge_periodicity_code IN VARCHAR2
6752 ) RETURN VARCHAR2
6753 IS
6754 l_charge_periodicity_dsp VARCHAR2(60) := NULL;
6755 --l_profile_value VARCHAR2(80) :=Oe_Sys_Parameters.Value('UOM_CLASS_CHARGE_PERIODICITY');
6756 l_profile_value VARCHAR2(80) :=fnd_profile.Value('ONT_UOM_CLASS_CHARGE_PERIODICITY');
6757 --
6758 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6759 --
6760 BEGIN
6761
6762 IF p_charge_periodicity_code IS NOT NULL THEN
6763
6764 SELECT unit_of_measure
6765 INTO l_charge_periodicity_dsp
6766 FROM mtl_units_of_measure_vl
6767 WHERE uom_class=l_profile_value
6768 and uom_code=p_charge_periodicity_code;
6769
6770 END IF;
6771
6772 RETURN l_charge_periodicity_dsp;
6773
6774 EXCEPTION
6775
6776 WHEN NO_DATA_FOUND THEN
6777
6778 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6779 THEN
6780
6781 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6782 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CHARGE_PERIODICITY_CODE');
6783 OE_MSG_PUB.Add;
6784
6785 END IF;
6786
6787 RETURN NULL;
6788
6789 WHEN OTHERS THEN
6790
6791 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6792 THEN
6793 OE_MSG_PUB.Add_Exc_Msg
6794 ( G_PKG_NAME
6795 , 'CHARGE_PERIODICITY'
6796 );
6797 END IF;
6798
6799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6800
6801 END Charge_Periodicity;
6802 --Recurring Charges
6803
6804
6805 /*3605052*/
6806 FUNCTION SERVICE_PERIOD
6807 ( p_service_period IN VARCHAR2
6808 ,p_inventory_item_id IN NUMBER
6809 ) RETURN VARCHAR2
6810 IS
6811 l_service_period_dsp VARCHAR2(60) := NULL;
6812 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6813 l_temp VARCHAR2(60) := NULL;
6814 BEGIN
6815
6816 IF p_service_period IS NOT NULL THEN
6817
6818 --webroot bug 6826344 start
6819 SELECT service_item_flag
6820 INTO l_temp
6821 FROM MTL_SYSTEM_ITEMS_B
6822 WHERE inventory_item_id = p_inventory_item_id
6823 AND organization_id = OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
6824
6825 --OE_MSG_PUB.ADD_TEXT('l_temp' || l_temp);
6826 IF l_temp = 'Y' THEN
6827 --webroot bug 6826344 end
6828
6829 Select description
6830 INTO l_service_period_dsp
6831 FROM mtl_item_uoms_view
6832 WHERE uom_code =p_service_period
6833 and inventory_item_id = p_inventory_item_id
6834 and organization_id = OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
6835 END IF;
6836
6837 --OE_MSG_PUB.ADD_TEXT('l_service_period_dsp'|| l_service_period_dsp);
6838 END IF;
6839
6840 RETURN l_service_period_dsp;
6841
6842 EXCEPTION
6843
6844 WHEN NO_DATA_FOUND THEN
6845
6846 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6847 THEN
6848
6849 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6850 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','SERVICE_PERIOD');
6851 OE_MSG_PUB.Add;
6852
6853 END IF;
6854
6855 RETURN NULL;
6856
6857 WHEN OTHERS THEN
6858
6859 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6860 THEN
6861 OE_MSG_PUB.Add_Exc_Msg
6862 ( G_PKG_NAME
6863 , 'SERVICE_PERIOD'
6864 );
6865 END IF;
6866
6867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6868
6869 END SERVICE_PERIOD;
6870
6871 -- Added for bug 5701246
6872
6873 FUNCTION SERVICE_REFERENCE_TYPE
6874 ( p_service_reference_type_code IN VARCHAR2
6875 ) RETURN VARCHAR2
6876 IS
6877 l_service_reference_type VARCHAR2(240) := NULL;
6878 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6879 BEGIN
6880
6881 IF p_service_reference_type_code IS NOT NULL THEN
6882
6883 Select meaning
6884 INTO l_service_reference_type
6885 FROM oe_lookups
6886 WHERE lookup_code=p_service_Reference_type_code
6887 and lookup_type = 'SERVICE_REFERENCE_TYPE_CODE';
6888
6889 END IF;
6890
6891 RETURN l_service_reference_type;
6892
6893 EXCEPTION
6894
6895 WHEN NO_DATA_FOUND THEN
6896
6897 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6898 THEN
6899
6900 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6901 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','SERVICE_REFERENCE_TYPE');
6902 OE_MSG_PUB.Add;
6903
6904 END IF;
6905
6906 RETURN NULL;
6907
6908 WHEN OTHERS THEN
6909
6910 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6911 THEN
6912 OE_MSG_PUB.Add_Exc_Msg
6913 ( G_PKG_NAME
6914 , 'SERVICE_REFERENCE_TYPE'
6915 );
6916 END IF;
6917
6918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6919
6920 END SERVICE_REFERENCE_TYPE;
6921
6922 -- end Added for bug 5701246
6923
6924 -- END GEN Id_To_Value
6925
6926 FUNCTION CHANGE_REASON
6927 ( p_change_reason_code IN VARCHAR2
6928 ) RETURN VARCHAR2
6929 IS
6930 l_change_reason VARCHAR2(60) := NULL;
6931 l_lookup_type1 VARCHAR2(80) :='CHANGE_CODE';
6932 l_lookup_type2 VARCHAR2(80) :='CANCEL_CODE';
6933 --
6934 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6935 --
6936 BEGIN
6937
6938 IF p_change_reason_code IS NOT NULL THEN
6939
6940 SELECT meaning
6941 INTO l_change_reason
6942 FROM OE_LOOKUPS
6943 WHERE lookup_code= p_change_reason_code
6944 and (lookup_type=l_lookup_type1 or lookup_type=l_lookup_type2);
6945
6946 END IF;
6947
6948 RETURN l_change_reason;
6949
6950 EXCEPTION
6951
6952 WHEN NO_DATA_FOUND THEN
6953
6954 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6955 THEN
6956
6957 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6958 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CHANGE_CODE');
6959 OE_MSG_PUB.Add;
6960
6961 END IF;
6962
6963 RETURN NULL;
6964
6965 WHEN OTHERS THEN
6966
6967 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6968 THEN
6969 OE_MSG_PUB.Add_Exc_Msg
6970 ( G_PKG_NAME
6971 , 'CHANGE_CODE'
6972 );
6973 END IF;
6974
6975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6976
6977 END CHANGE_REASON;
6978
6979 --Customer Acceptance
6980 Procedure Get_Contingency_Attributes
6981 ( p_contingency_id IN NUMBER
6982 , x_contingency_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6983 , x_contingency_description OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6984 , x_expiration_event_attribute OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6985 )
6986 IS
6987 l_expiration_event_code VARCHAR2(30):=NULL;
6988 --
6989 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6990 --
6991 BEGIN
6992
6993 x_contingency_name := NULL;
6994 x_contingency_description := NULL;
6995 x_expiration_event_attribute := NULL;
6996 IF p_contingency_id IS NOT NULL THEN
6997
6998 SELECT contingency_name, description, expiration_event_code
6999 INTO x_contingency_name, x_contingency_description, l_expiration_event_code
7000 FROM AR_DEFERRAL_REASONS
7001 WHERE contingency_id = p_contingency_id;
7002
7003
7004 IF l_expiration_event_code IS NOT NULL THEN
7005 SELECT MEANING
7006 INTO x_expiration_event_attribute
7007 FROM AR_LOOKUPS
7008 WHERE lookup_type='AR_EXPIRATION_EVENTS'
7009 AND lookup_code = l_expiration_event_code;
7010 END IF;
7011
7012 ELSE
7013 x_contingency_name := NULL;
7014 x_contingency_description := NULL;
7015 x_expiration_event_attribute := NULL;
7016
7017 END IF;
7018
7019 EXCEPTION
7020
7021 WHEN NO_DATA_FOUND THEN
7022
7023 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7024 THEN
7025
7026 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7027 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CONTINGENCY_ID');
7028 OE_MSG_PUB.Add;
7029
7030 END IF;
7031
7032 WHEN OTHERS THEN
7033
7034 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7035 THEN
7036 OE_MSG_PUB.Add_Exc_Msg
7037 ( G_PKG_NAME
7038 , 'Get_Contingency_Attributes'
7039 );
7040 END IF;
7041
7042 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7043
7044 END Get_Contingency_Attributes;
7045
7046 FUNCTION Revrec_Event(p_revrec_event_code IN VARCHAR2) RETURN VARCHAR2
7047 IS
7048 l_revrec_event VARCHAR2(80);
7049 --
7050 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7051 --
7052 BEGIN
7053
7054 IF p_revrec_event_code IS NOT NULL THEN
7055 SELECT MEANING
7056 INTO l_revrec_event
7057 FROM AR_LOOKUPS
7058 WHERE lookup_type='AR_REVREC_EVENTS'
7059 AND lookup_code = p_revrec_event_code;
7060 ELSE
7061 l_revrec_event := NULL;
7062 END IF;
7063
7064 RETURN l_revrec_event;
7065
7066 EXCEPTION
7067
7068 WHEN NO_DATA_FOUND THEN
7069
7070 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7071 THEN
7072
7073 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7074 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','REVREC_EVENT_CODE');
7075 OE_MSG_PUB.Add;
7076
7077 END IF;
7078
7079 RETURN NULL;
7080
7081 WHEN OTHERS THEN
7082
7083 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7084 THEN
7085 OE_MSG_PUB.Add_Exc_Msg
7086 ( G_PKG_NAME
7087 , 'Revrec_Event'
7088 );
7089 END IF;
7090
7091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7092
7093 END Revrec_Event;
7094
7095 FUNCTION Accepted_By(p_accepted_by IN NUMBER) RETURN VARCHAR2
7096 IS
7097 l_accepted_by_dsp VARCHAR2(100);
7098 --
7099 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7100 --
7101 BEGIN
7102
7103 IF p_accepted_by IS NOT NULL THEN
7104 SELECT user_name
7105 INTO l_accepted_by_dsp
7106 FROM FND_USER
7107 WHERE user_id=p_accepted_by;
7108 ELSE
7109 l_accepted_by_dsp:= NULL;
7110 END IF;
7111
7112 RETURN l_accepted_by_dsp;
7113
7114 EXCEPTION
7115
7116 WHEN NO_DATA_FOUND THEN
7117
7118 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7119 THEN
7120
7121 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7122 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ACCEPTED_BY');
7123 OE_MSG_PUB.Add;
7124
7125 END IF;
7126
7127 RETURN NULL;
7128
7129 WHEN OTHERS THEN
7130
7131 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7132 THEN
7133 OE_MSG_PUB.Add_Exc_Msg
7134 ( G_PKG_NAME
7135 , 'Accepted_By'
7136 );
7137 END IF;
7138
7139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7140
7141 END Accepted_By;
7142
7143
7144 END OE_Id_To_Value;