[Home] [Help]
PACKAGE BODY: APPS.OE_ID_TO_VALUE
Source
1 PACKAGE BODY OE_Id_To_Value AS
2 /* $Header: OEXSIDVB.pls 120.10.12020000.2 2013/01/07 06:48:11 sujithku 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 -- 12874640 Start
5775 ELSIF NVL(p_item_identifier_type, 'INT') = 'CAT' THEN
5776 BEGIN
5777 IF l_debug_level > 0 THEN
5778 oe_debug_pub.add( 'IN OEXSIDVB ITEM IDENTIFIER IS CAT' ) ;
5779 END IF;
5780
5781 SELECT category_concat_segs
5782 ,category_concat_segs
5783 INTO x_ordered_item
5784 ,x_inventory_item
5785 FROM mtl_categories_v
5786 WHERE category_id = p_inventory_item_id;
5787 IF l_debug_level > 0 THEN
5788 oe_debug_pub.add( 'ORDERED_ITEM_DSP: '||X_ORDERED_ITEM ) ;
5789 END IF;
5790 EXCEPTION
5791 WHEN NO_DATA_FOUND THEN
5792 Null;
5793 When too_many_rows then
5794 Null;
5795 When others then
5796 Null;
5797 END;
5798 -- 12874640 End
5799 ELSE
5800 BEGIN
5801 IF l_debug_level > 0 THEN
5802 oe_debug_pub.add( 'IN OEXSIDVB ITEM IDENTIFIER IS GENE' ) ;
5803 END IF;
5804 IF p_ordered_item_id IS NULL THEN
5805 IF l_debug_level > 0 THEN
5806 oe_debug_pub.add( 'ORDERED_ITEM_ID IS NULL ' ) ;
5807 oe_debug_pub.add( 'ORDERED_ITEM: '||P_ORDERED_ITEM ) ;
5808 END IF;
5809 SELECT items.cross_reference
5810 ,sitems.concatenated_segments
5811 INTO x_ordered_item
5812 ,x_inventory_item
5813 FROM mtl_cross_reference_types types
5814 , mtl_cross_references items
5815 , mtl_system_items_vl sitems
5816 WHERE types.cross_reference_type = items.cross_reference_type
5817 AND items.inventory_item_id = sitems.inventory_item_id
5818 AND sitems.organization_id = p_organization_id
5819 AND sitems.inventory_item_id = p_inventory_item_id
5820 AND items.cross_reference_type = p_item_identifier_type
5821 AND items.cross_reference = p_ordered_item;
5822 END IF;
5823
5824 EXCEPTION
5825 WHEN NO_DATA_FOUND THEN
5826 Null;
5827 When too_many_rows then
5828 Null;
5829 When others then
5830 Null;
5831 END;
5832 END IF;
5833 IF l_debug_level > 0 THEN
5834 oe_debug_pub.add( 'ORDERED_ITEM_DSP: '||X_ORDERED_ITEM ) ;
5835 END IF;
5836 EXCEPTION
5837
5838 WHEN NO_DATA_FOUND THEN
5839
5840 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5841 THEN
5842
5843 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5844 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Ordered_Item');
5845 OE_MSG_PUB.Add;
5846
5847 END IF;
5848
5849
5850 WHEN OTHERS THEN
5851
5852 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5853 THEN
5854 OE_MSG_PUB.Add_Exc_Msg
5855 ( G_PKG_NAME
5856 , 'Ordered_Item'
5857 );
5858 END IF;
5859
5860 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5861
5862 END Ordered_Item;
5863
5864 PROCEDURE Item_Identifier
5865 (p_Item_Identifier_type IN VARCHAR2
5866 , x_Item_Identifier OUT NOCOPY VARCHAR2)
5867
5868 IS
5869 --
5870 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5871 l_lookup_type VARCHAR2(80) :='ITEM_IDENTIFIER_TYPE';
5872 --
5873 BEGIN
5874 IF l_debug_level > 0 THEN
5875 oe_debug_pub.add( 'ENTERING ITEM_IDENTIFIER' , 1 ) ;
5876 END IF;
5877 --12874640 IF p_Item_Identifier_type in ('INT','CUST') THEN
5878 IF p_Item_Identifier_type in ('INT','CUST','CAT') THEN --12874640
5879 Select meaning
5880 Into x_Item_Identifier
5881 From oe_lookups
5882 Where lookup_type = l_lookup_type
5883 And lookup_code = p_Item_Identifier_type;
5884 ELSE
5885 x_Item_Identifier := p_Item_Identifier_type;
5886
5887 END IF;
5888
5889 IF l_debug_level > 0 THEN
5890 oe_debug_pub.add( 'ITEM_IDENTIFIER' || X_ITEM_IDENTIFIER , 1 ) ;
5891 END IF;
5892 EXCEPTION
5893
5894 WHEN NO_DATA_FOUND THEN
5895
5896 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5897 THEN
5898
5899 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5900 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Item_Identifier');
5901 OE_MSG_PUB.Add;
5902
5903 END IF;
5904
5905
5906 WHEN OTHERS THEN
5907
5908 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5909 THEN
5910 OE_MSG_PUB.Add_Exc_Msg
5911 ( G_PKG_NAME
5912 , 'Item_Identifier'
5913 );
5914 END IF;
5915
5916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5917
5918 END Item_Identifier;
5919
5920 PROCEDURE Item_Relationship_Type
5921 (p_Item_Relationship_Type IN NUMBER
5922 , x_Item_Relationship_Type_Dsp OUT nocopy VARCHAR2)
5923 IS
5924 --
5925 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5926 l_lookup_type VARCHAR2(80) :='MTL_RELATIONSHIP_TYPES';
5927 --
5928 BEGIN
5929 IF l_debug_level > 0 THEN
5930 oe_debug_pub.add('Entering Item_Relationship_Type',1);
5931 END IF;
5932 IF p_Item_Relationship_Type IS NOT NULL THEN
5933 Select meaning
5934 Into x_Item_Relationship_Type_dsp
5935 From mfg_lookups
5936 Where lookup_type = l_lookup_type
5937 And lookup_code = p_Item_Relationship_Type;
5938 ELSE
5939 x_Item_Relationship_Type_Dsp := null;
5940
5941 IF l_debug_level > 0 THEN
5942 oe_debug_pub.add('in else Item_Relationship_Type' || x_Item_Relationship_Type_Dsp,1);
5943 END IF;
5944 END IF;
5945
5946 IF l_debug_level > 0 THEN
5947 oe_debug_pub.add('Item_Relationship_Type_dsp' || x_Item_Relationship_Type_Dsp,1);
5948 END IF;
5949 EXCEPTION
5950
5951 WHEN NO_DATA_FOUND THEN
5952
5953 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5954 THEN
5955
5956 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
5957 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Item_Relationship_Type');
5958 OE_MSG_PUB.Add;
5959
5960 END IF;
5961
5962
5963 WHEN OTHERS THEN
5964
5965 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5966 THEN
5967 OE_MSG_PUB.Add_Exc_Msg
5968 ( G_PKG_NAME
5969 , 'Item_Relationship_Type'
5970 );
5971 END IF;
5972
5973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5974
5975 END Item_Relationship_Type;
5976
5977 FUNCTION User_Status
5978 ( p_user_status_code IN VARCHAR2
5979 ) RETURN VARCHAR2
5980 IS
5981 l_user_status VARCHAR2(240) := NULL;
5982 --
5983 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5984 l_lookup_type VARCHAR2(80) := 'USER_STATUS';
5985 --
5986 BEGIN
5987
5988 IF l_debug_level > 0 THEN
5989 oe_debug_pub.add( 'IN GET VALUES ' || p_user_status_code ) ;
5990 END IF;
5991 IF p_user_status_code IS NOT NULL THEN
5992
5993 SELECT MEANING
5994 INTO l_user_status
5995 FROM OE_LOOKUPS
5996 WHERE LOOKUP_CODE = p_user_status_code
5997 AND LOOKUP_TYPE = l_lookup_type;
5998
5999
6000 END IF;
6001
6002 IF l_debug_level > 0 THEN
6003 oe_debug_pub.add( 'IN GET VALUES ' || L_user_status ) ;
6004 END IF;
6005 RETURN l_user_status;
6006
6007 EXCEPTION
6008
6009 WHEN NO_DATA_FOUND THEN
6010
6011 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6012 THEN
6013
6014 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6015 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','user_status');
6016 OE_MSG_PUB.Add;
6017
6018 END IF;
6019
6020 RETURN NULL;
6021
6022 WHEN OTHERS THEN
6023
6024 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6025 THEN
6026 OE_MSG_PUB.Add_Exc_Msg
6027 ( G_PKG_NAME
6028 , 'user_status'
6029 );
6030 END IF;
6031
6032 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6033
6034 END User_status;
6035
6036
6037 FUNCTION Transaction_Phase
6038 ( p_Transaction_Phase_code IN VARCHAR2
6039 ) RETURN VARCHAR2
6040 IS
6041 l_Transaction_Phase VARCHAR2(240) := NULL;
6042 --
6043 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6044 l_lookup_type VARCHAR2(80) :='TRANSACTION_PHASE';
6045 --
6046 BEGIN
6047
6048 IF l_debug_level > 0 THEN
6049 oe_debug_pub.add( 'IN GET VALUES ' || p_Transaction_Phase_code ) ;
6050 END IF;
6051 IF p_Transaction_Phase_code IS NOT NULL THEN
6052
6053 SELECT MEANING
6054 INTO l_Transaction_Phase
6055 FROM OE_LOOKUPS
6056 WHERE LOOKUP_CODE = p_Transaction_Phase_code
6057 AND LOOKUP_TYPE = l_lookup_type;
6058
6059
6060 END IF;
6061
6062 IF l_debug_level > 0 THEN
6063 oe_debug_pub.add( 'IN GET VALUES ' || L_Transaction_Phase ) ;
6064 END IF;
6065 RETURN l_Transaction_Phase;
6066
6067 EXCEPTION
6068
6069 WHEN NO_DATA_FOUND THEN
6070
6071 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6072 THEN
6073
6074 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6075 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','transaction_phase');
6076 OE_MSG_PUB.Add;
6077
6078 END IF;
6079
6080 RETURN NULL;
6081
6082 WHEN OTHERS THEN
6083
6084 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6085 THEN
6086 OE_MSG_PUB.Add_Exc_Msg
6087 ( G_PKG_NAME
6088 , 'transaction_phase'
6089 );
6090 END IF;
6091
6092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6093
6094 END Transaction_Phase;
6095 PROCEDURE Customer_Location
6096 (
6097 p_sold_to_site_use_id IN NUMBER
6098 , x_sold_to_location_address1 OUT NOCOPY VARCHAR2
6099 , x_sold_to_location_address2 OUT NOCOPY VARCHAR2
6100 , x_sold_to_location_address3 OUT NOCOPY VARCHAR2
6101 , x_sold_to_location_address4 OUT NOCOPY VARCHAR2
6102 , x_sold_to_location OUT NOCOPY VARCHAR2
6103 , x_sold_to_location_city OUT NOCOPY VARCHAR2
6104 , x_sold_to_location_state OUT NOCOPY VARCHAR2
6105 , x_sold_to_location_postal OUT NOCOPY VARCHAR2
6106 , x_sold_to_location_country OUT NOCOPY VARCHAR2
6107 )
6108 IS
6109 --
6110 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6111 --
6112 BEGIN
6113
6114 IF p_sold_to_site_use_id is NOT NULL THEN
6115
6116 SELECT /* MOAC_SQL_CHANGE */
6117 SITE.LOCATION
6118 , LOC.ADDRESS1
6119 , LOC.ADDRESS2
6120 , LOC.ADDRESS3
6121 , LOC.ADDRESS4
6122 , LOC.CITY
6123 , nvl(LOC.STATE,LOC.PROVINCE) -- 3603600
6124 , LOC.POSTAL_CODE
6125 , LOC.COUNTRY
6126 INTO
6127 x_sold_to_location
6128 , x_sold_to_location_address1
6129 , x_sold_to_location_address2
6130 , x_sold_to_location_address3
6131 , x_sold_to_location_address4
6132 , x_sold_to_location_city
6133 , x_sold_to_location_state
6134 , x_sold_to_location_postal
6135 , x_sold_to_location_country
6136
6137 FROM
6138 HZ_CUST_SITE_USES_All SITE,
6139 HZ_PARTY_SITES PARTY_SITE,
6140 HZ_LOCATIONS LOC,
6141 HZ_CUST_ACCT_SITES ACCT_SITE
6142 WHERE
6143 SITE.SITE_USE_CODE = 'SOLD_TO'
6144 AND SITE.SITE_USE_ID = p_sold_to_site_use_id
6145 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
6146 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
6147 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
6148
6149 ELSE
6150
6151 x_sold_to_location := NULL ;
6152 x_sold_to_location_address1 := NULL ;
6153 x_sold_to_location_address2 := NULL ;
6154 x_sold_to_location_address3 := NULL ;
6155 x_sold_to_location_address4 := NULL ;
6156 x_sold_to_location_city := NULL ;
6157 x_sold_to_location_state := NULL ;
6158 x_sold_to_location_postal := NULL ;
6159 x_sold_to_location_country := NULL ;
6160
6161 END IF;
6162
6163 EXCEPTION
6164
6165 WHEN NO_DATA_FOUND THEN
6166
6167 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6168 THEN
6169
6170 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6171 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Customer_Location');
6172 OE_MSG_PUB.Add;
6173
6174 END IF;
6175
6176
6177 WHEN OTHERS THEN
6178
6179 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6180 THEN
6181 OE_MSG_PUB.Add_Exc_Msg
6182 ( G_PKG_NAME
6183 , 'Customer_Location'
6184 );
6185 END IF;
6186
6187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6188
6189 END Customer_Location;
6190
6191 /*-----------------------------------------------------------------
6192 PROCEDURE Get_Contact_Details
6193
6194 added for pack J enhanced dropship project.
6195 ------------------------------------------------------------------*/
6196 PROCEDURE Get_Contact_Details
6197 ( p_contact_id IN NUMBER
6198 ,x_contact_name OUT NOCOPY VARCHAR2
6199 ,x_phone_line_type OUT NOCOPY VARCHAR2
6200 ,x_phone_number OUT NOCOPY VARCHAR2
6201 ,x_email_address OUT NOCOPY VARCHAR2)
6202 IS
6203 BEGIN
6204
6205 SELECT SUBSTRB(CONTACT_PARTY.PARTY_NAME,1,70) CONTACT_NAME,
6206 DECODE(arl.meaning,NULL, NULL, ' '||arl.meaning) Phone_Type,
6207 DECODE(CONTACT.phone_country_code, NULL, NULL,
6208 CONTACT.phone_country_code || '- ') ||
6209 DECODE(CONTACT.phone_area_code, NULL, NULL, CONTACT.phone_area_code || '-')||
6210 DECODE(CONTACT.phone_number, NULL, NULL, CONTACT.phone_number ) phone,
6211 REL_PARTY.email_address
6212 INTO x_contact_name,
6213 x_phone_line_type,
6214 x_phone_number,
6215 x_email_address
6216 FROM HZ_CONTACT_POINTS CONTACT,
6217 HZ_PARTIES CONTACT_PARTY,
6218 HZ_CUST_ACCOUNT_ROLES ACCT_ROLES,
6219 HZ_CUST_ACCOUNTS CUST_ACCT,
6220 HZ_RELATIONSHIPS PARTY_REL,
6221 HZ_PARTIES REL_PARTY,
6222 AR_LOOKUPS ARL
6223 WHERE CONTACT.owner_table_name(+) = 'HZ_PARTIES'
6224 AND CONTACT.PRIMARY_FLAG (+) = 'Y'
6225 AND CONTACT.contact_point_type (+) = 'PHONE'
6226 AND ACCT_ROLES.PARTY_ID = CONTACT.owner_table_id(+)
6227 AND ACCT_ROLES.cust_account_role_id = p_contact_id
6228 AND PARTY_REL.PARTY_ID = ACCT_ROLES.PARTY_ID
6229 AND PARTY_REL.PARTY_ID = REL_PARTY.PARTY_ID
6230 AND PARTY_REL.SUBJECT_ID = CONTACT_PARTY.PARTY_ID
6231 AND PARTY_REL.OBJECT_ID = CUST_ACCT.PARTY_ID
6232 AND ACCT_ROLES.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
6233 AND CONTACT.status(+) = 'A'
6234 AND ACCT_ROLES.STATUS = 'A'
6235 AND ARL.lookup_type (+) = 'PHONE_LINE_TYPE'
6236 AND ARL.lookup_code(+) = CONTACT.phone_line_type;
6237
6238 EXCEPTION
6239 WHEN others THEN
6240 oe_debug_pub.add('Get_Contact_Details ' || sqlerrm, 1);
6241 END Get_Contact_Details;
6242
6243 --serla begin
6244 FUNCTION payment_collection_event_name
6245 ( p_payment_collection_event IN VARCHAR2
6246 ) RETURN VARCHAR2
6247 IS
6248 l_lookup_type VARCHAR2(80) := 'OE_PAYMENT_COLLECTION_TYPE';
6249 l_payment_collection_event VARCHAR2(80);
6250 BEGIN
6251
6252 IF p_payment_collection_event IS NOT NULL THEN
6253
6254 SELECT MEANING
6255 INTO l_payment_collection_event
6256 FROM OE_LOOKUPS
6257 WHERE LOOKUP_CODE = p_payment_collection_event
6258 AND LOOKUP_TYPE = l_lookup_type;
6259
6260 END IF;
6261
6262 RETURN l_payment_collection_event;
6263
6264 EXCEPTION
6265
6266 WHEN NO_DATA_FOUND THEN
6267
6268 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6269 THEN
6270
6271 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6272 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','payment_collection_event_name');
6273 OE_MSG_PUB.Add;
6274
6275 END IF;
6276
6277
6278 WHEN OTHERS THEN
6279
6280 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6281 THEN
6282 OE_MSG_PUB.Add_Exc_Msg
6283 ( G_PKG_NAME
6284 , 'payment_collection_event_name'
6285 );
6286 END IF;
6287
6288 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6289
6290 END payment_collection_event_name;
6291
6292 FUNCTION Receipt_Method
6293 ( p_receipt_method IN NUMBER
6294 ) RETURN VARCHAR2
6295 IS
6296 l_receipt_method VARCHAR2(80);
6297 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6298 l_err_message VARCHAR2(2000);
6299 BEGIN
6300
6301 IF l_debug_level > 0 THEN
6302 oe_debug_pub.add('Receipt method id....ksurendr'||p_receipt_method);
6303 END IF;
6304
6305 IF p_receipt_method IS NOT NULL AND p_receipt_method <> 0 AND
6306 NOT OE_GLOBALS.Equal(p_receipt_method,FND_API.G_MISS_NUM) THEN
6307 IF l_debug_level > 0 THEN
6308 oe_debug_pub.add('Inside if part....');
6309 END IF;
6310 SELECT NAME
6311 INTO l_receipt_method
6312 FROM AR_RECEIPT_METHODS
6313 WHERE receipt_method_id = p_receipt_method;
6314 --bug 5204358
6315 ELSIF p_receipt_method = 0 THEN
6316 IF l_debug_level > 0 THEN
6317 oe_debug_pub.add('Inside else part....');
6318 END IF;
6319 fnd_message.set_name('ONT','OE_VPM_NO_PAY_METHOD');
6320 OE_MSG_PUB.Add;
6321 RAISE FND_API.G_EXC_ERROR;
6322 END IF;
6323
6324 RETURN l_receipt_method;
6325
6326 EXCEPTION
6327
6328 WHEN NO_DATA_FOUND THEN
6329
6330 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6331 THEN
6332
6333 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6334 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Receipt_Method');
6335 OE_MSG_PUB.Add;
6336
6337 END IF;
6338
6339 WHEN FND_API.G_EXC_ERROR THEN
6340 l_err_message := SQLERRM;
6341 IF l_debug_level > 0 THEN
6342 oe_debug_pub.add('Id to value error for receipt method....exc');
6343 oe_debug_pub.add('Error'||l_err_message);
6344 END IF;
6345
6346 RAISE FND_API.G_EXC_ERROR;
6347
6348 WHEN OTHERS THEN
6349
6350 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6351 THEN
6352 OE_MSG_PUB.Add_Exc_Msg
6353 ( G_PKG_NAME
6354 , 'Receipt_Method'
6355 );
6356 END IF;
6357
6358 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6359
6360 END Receipt_Method;
6361 --serla end
6362
6363 FUNCTION get_sales_group_name
6364 (p_sales_group_id IN NUMBER
6365 )RETURN VARCHAR2 IS
6366
6367 l_sales_group_name VARCHAR2(80);
6368 BEGIN
6369
6370 IF p_sales_group_id IS NOT NULL THEN
6371 Select group_name into l_sales_group_name
6372 From jtf_rs_groups_vl
6373 Where Group_id=p_sales_group_id;
6374 ELSE
6375 Oe_Debug_Pub.add('Input sales group id is null');
6376 END IF;
6377
6378 RETURN l_sales_group_name;
6379
6380 EXCEPTION
6381
6382 WHEN NO_DATA_FOUND THEN
6383 IF p_sales_group_id <> -1 THEN
6384 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6385 THEN
6386
6387 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6388 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sales_group_name');
6389 OE_MSG_PUB.Add;
6390
6391 END IF;
6392 ELSE
6393 oe_debug_pub.add('Sales Group, -1 sales group id');
6394 END IF;
6395
6396
6397 WHEN OTHERS THEN
6398
6399 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6400 THEN
6401 OE_MSG_PUB.Add_Exc_Msg
6402 ( G_PKG_NAME
6403 , 'sales_group_name'
6404 );
6405 END IF;
6406
6407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6408
6409 END get_sales_group_name;
6410
6411 FUNCTION end_customer_Contact
6412 ( p_end_customer_contact_id IN NUMBER
6413 ) RETURN VARCHAR2
6414 IS
6415 l_end_customer_contact VARCHAR2(240) := NULL;
6416 --
6417 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6418 --
6419 BEGIN
6420
6421 IF p_end_customer_contact_id IS NOT NULL THEN
6422
6423 SELECT NAME
6424 INTO l_end_customer_contact
6425 FROM OE_CONTACTS_V
6426 WHERE CONTACT_ID = p_end_customer_contact_id;
6427
6428 END IF;
6429
6430 RETURN l_end_customer_contact;
6431
6432 EXCEPTION
6433
6434 WHEN NO_DATA_FOUND THEN
6435
6436 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6437 THEN
6438
6439 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6440 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_contact');
6441 OE_MSG_PUB.Add;
6442
6443 END IF;
6444
6445 RETURN NULL;
6446
6447 WHEN OTHERS THEN
6448
6449 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6450 THEN
6451 OE_MSG_PUB.Add_Exc_Msg
6452 ( G_PKG_NAME
6453 , 'End_Customer_Contact'
6454 );
6455 END IF;
6456
6457 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6458
6459 END End_Customer_Contact;
6460
6461 PROCEDURE End_Customer
6462 ( p_end_customer_id IN NUMBER ,
6463 x_end_customer_name OUT NOCOPY VARCHAR2 ,
6464 x_end_customer_number OUT NOCOPY VARCHAR2
6465 )
6466 IS
6467 --
6468 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6469 --
6470 BEGIN
6471
6472 IF p_end_customer_id is NOT NULL THEN
6473
6474 IF l_debug_level > 0 THEN
6475 oe_debug_pub.add( 'DOING SELECT FOR END_CUSTOMER_NUMBER' ) ;
6476 END IF;
6477 SELECT O.NAME
6478 , O.CUSTOMER_NUMBER
6479 INTO x_end_customer_name
6480 , x_end_customer_number
6481 FROM OE_SOLD_TO_ORGS_V O
6482 WHERE O.ORGANIZATION_ID = p_end_customer_id;
6483
6484 ELSE
6485
6486 IF l_debug_level > 0 THEN
6487 oe_debug_pub.add( 'INSIDE QUERY FOR END CUSTOMER NUMBER - NO ORG_ID PASSED' ) ;
6488 END IF;
6489 x_end_customer_name := NULL ;
6490 x_end_customer_number := NULL ;
6491
6492 END IF;
6493
6494 EXCEPTION
6495
6496 WHEN NO_DATA_FOUND THEN
6497
6498 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6499 THEN
6500
6501 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6502 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer');
6503 OE_MSG_PUB.Add;
6504
6505 END IF;
6506
6507
6508 WHEN OTHERS THEN
6509
6510 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6511 THEN
6512 OE_MSG_PUB.Add_Exc_Msg
6513 ( G_PKG_NAME
6514 , 'end_customer'
6515 );
6516 END IF;
6517
6518 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6519
6520 END end_customer;
6521
6522 PROCEDURE end_customer_site_use
6523 ( p_end_customer_site_use_id IN NUMBER
6524 , x_end_customer_address1 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6525 , x_end_customer_address2 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6526 , x_end_customer_address3 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6527 , x_end_customer_address4 OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6528 , x_end_customer_location OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6529 , x_end_customer_city OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6530 , x_end_customer_state OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6531 , x_end_customer_postal_code OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6532 , x_end_customer_country OUT NOCOPY /* file.sql.39 change */ VARCHAR2
6533 )
6534 IS
6535 --
6536 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6537 --
6538 BEGIN
6539
6540 IF p_end_customer_site_use_id is NOT NULL THEN
6541
6542 SELECT /* MOAC_SQL_CHANGE */
6543 loc.address1
6544 ,loc.address2
6545 ,loc.address3
6546 ,loc.address4
6547 ,site_use.location
6548 ,loc.city
6549 ,nvl(loc.state,loc.province) -- 3603600
6550 ,loc.postal_code
6551 ,loc.country
6552 INTO
6553 x_end_customer_address1
6554 ,x_end_customer_address2
6555 ,x_end_customer_address3
6556 ,x_end_customer_address4
6557 ,x_end_customer_location
6558 ,x_end_customer_city
6559 ,x_end_customer_state
6560 ,x_end_customer_postal_code
6561 ,x_end_customer_country
6562 FROM
6563 hz_locations loc,
6564 hz_party_sites site,
6565 hz_cust_site_uses_all site_use,
6566 hz_cust_acct_sites acct_site
6567 WHERE
6568 site_use.site_use_id= p_end_customer_site_use_id
6569 and site_use.cust_acct_site_id=acct_site.cust_acct_site_id
6570 and acct_site.party_site_id=site.party_site_id
6571 and site.location_id=loc.location_id;
6572
6573 ELSE
6574 x_end_customer_address1 := NULL;
6575 x_end_customer_address2 := NULL;
6576 x_end_customer_address3 := NULL;
6577 x_end_customer_address4 := NULL;
6578 x_end_customer_location := NULL;
6579 x_end_customer_city := NULL;
6580 x_end_customer_state := NULL;
6581 x_end_customer_postal_code := NULL;
6582 x_end_customer_country := NULL;
6583 END IF;
6584
6585 EXCEPTION
6586
6587 WHEN NO_DATA_FOUND THEN
6588
6589 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6590 THEN
6591
6592 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6593 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_site');
6594 OE_MSG_PUB.Add;
6595
6596 END IF;
6597
6598
6599 WHEN OTHERS THEN
6600
6601 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6602 THEN
6603 OE_MSG_PUB.Add_Exc_Msg
6604 ( G_PKG_NAME
6605 , 'end_customer_site_use'
6606 );
6607 END IF;
6608
6609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6610
6611 END end_customer_site_use;
6612
6613 --Macd
6614
6615 FUNCTION IB_OWNER
6616 ( p_ib_owner IN VARCHAR2
6617 ) RETURN VARCHAR2
6618 IS
6619 l_ib_owner_dsp VARCHAR2(60) := NULL;
6620 l_lookup_type1 VARCHAR2(80) :='ITEM_OWNER';
6621 l_lookup_type2 VARCHAR2(80) :='ONT_INSTALL_BASE';
6622 --
6623 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6624 --
6625 BEGIN
6626
6627 IF p_ib_owner IS NOT NULL THEN
6628
6629 SELECT meaning
6630 INTO l_ib_owner_dsp
6631 FROM OE_LOOKUPS
6632 WHERE lookup_code= p_ib_owner and
6633 (lookup_type=l_lookup_type1 or lookup_type=l_lookup_type2);
6634
6635 END IF;
6636
6637 RETURN l_ib_owner_dsp;
6638
6639 EXCEPTION
6640
6641 WHEN NO_DATA_FOUND THEN
6642
6643 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6644 THEN
6645
6646 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6647 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','IB_OWNER');
6648 OE_MSG_PUB.Add;
6649
6650 END IF;
6651
6652 RETURN NULL;
6653
6654 WHEN OTHERS THEN
6655
6656 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6657 THEN
6658 OE_MSG_PUB.Add_Exc_Msg
6659 ( G_PKG_NAME
6660 , 'IB_OWNER'
6661 );
6662 END IF;
6663
6664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6665
6666 END IB_OWNER;
6667
6668 FUNCTION IB_CURRENT_LOCATION
6669 ( p_ib_current_location IN VARCHAR2
6670 ) RETURN VARCHAR2
6671 IS
6672 l_ib_current_location_dsp VARCHAR2(60) := NULL;
6673 l_lookup_type1 VARCHAR2(80) :='ITEM_CURRENT_LOCATION';
6674 l_lookup_type2 VARCHAR2(80) :='ONT_INSTALL_BASE';
6675 --
6676 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6677 --
6678 BEGIN
6679
6680 IF p_ib_current_location IS NOT NULL THEN
6681
6682 SELECT meaning
6683 INTO l_ib_current_location_dsp
6684 FROM OE_LOOKUPS
6685 WHERE lookup_code= p_ib_current_location and
6686 (lookup_type=l_lookup_type1 or lookup_type=l_lookup_type2);
6687
6688 END IF;
6689
6690 RETURN l_ib_current_location_dsp;
6691
6692 EXCEPTION
6693
6694 WHEN NO_DATA_FOUND THEN
6695
6696 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6697 THEN
6698
6699 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6700 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','IB_CURRENT_LOCATION');
6701 OE_MSG_PUB.Add;
6702
6703 END IF;
6704
6705 RETURN NULL;
6706
6707 WHEN OTHERS THEN
6708
6709 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6710 THEN
6711 OE_MSG_PUB.Add_Exc_Msg
6712 ( G_PKG_NAME
6713 , 'IB_CURRENT_LOCATION'
6714 );
6715 END IF;
6716
6717 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6718
6719 END IB_CURRENT_LOCATION;
6720
6721 FUNCTION IB_INSTALLED_AT_LOCATION
6722 ( p_ib_installed_at_location IN VARCHAR2
6723 ) RETURN VARCHAR2
6724 IS
6725 l_ib_installed_at_location_dsp VARCHAR2(60) := NULL;
6726 l_lookup_type1 VARCHAR2(80) :='ITEM_INSTALL_LOCATION';
6727 l_lookup_type2 VARCHAR2(80) :='ONT_INSTALL_BASE';
6728 --
6729 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6730 --
6731 BEGIN
6732
6733 IF p_ib_installed_at_location IS NOT NULL THEN
6734
6735 SELECT meaning
6736 INTO l_ib_installed_at_location_dsp
6737 FROM OE_LOOKUPS
6738 WHERE lookup_code= p_ib_installed_at_location and
6739 (lookup_type=l_lookup_type1 or lookup_type=l_lookup_type2);
6740
6741 END IF;
6742
6743 RETURN l_ib_installed_at_location_dsp;
6744
6745 EXCEPTION
6746
6747 WHEN NO_DATA_FOUND THEN
6748
6749 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6750 THEN
6751
6752 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6753 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','IB_INSTALLED_AT_LOCATION');
6754 OE_MSG_PUB.Add;
6755
6756 END IF;
6757
6758 RETURN NULL;
6759
6760 WHEN OTHERS THEN
6761
6762 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6763 THEN
6764 OE_MSG_PUB.Add_Exc_Msg
6765 ( G_PKG_NAME
6766 , 'IB_INSTALLED_AT_LOCATION'
6767 );
6768 END IF;
6769
6770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6771
6772 END IB_INSTALLED_AT_LOCATION;
6773 --Macd
6774
6775 --Recurring CHarges
6776 FUNCTION Charge_Periodicity
6777 ( p_charge_periodicity_code IN VARCHAR2
6778 ) RETURN VARCHAR2
6779 IS
6780 l_charge_periodicity_dsp VARCHAR2(60) := NULL;
6781 --l_profile_value VARCHAR2(80) :=Oe_Sys_Parameters.Value('UOM_CLASS_CHARGE_PERIODICITY');
6782 l_profile_value VARCHAR2(80) :=fnd_profile.Value('ONT_UOM_CLASS_CHARGE_PERIODICITY');
6783 --
6784 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6785 --
6786 BEGIN
6787
6788 IF p_charge_periodicity_code IS NOT NULL THEN
6789
6790 SELECT unit_of_measure
6791 INTO l_charge_periodicity_dsp
6792 FROM mtl_units_of_measure_vl
6793 WHERE uom_class=l_profile_value
6794 and uom_code=p_charge_periodicity_code;
6795
6796 END IF;
6797
6798 RETURN l_charge_periodicity_dsp;
6799
6800 EXCEPTION
6801
6802 WHEN NO_DATA_FOUND THEN
6803
6804 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6805 THEN
6806
6807 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6808 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CHARGE_PERIODICITY_CODE');
6809 OE_MSG_PUB.Add;
6810
6811 END IF;
6812
6813 RETURN NULL;
6814
6815 WHEN OTHERS THEN
6816
6817 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6818 THEN
6819 OE_MSG_PUB.Add_Exc_Msg
6820 ( G_PKG_NAME
6821 , 'CHARGE_PERIODICITY'
6822 );
6823 END IF;
6824
6825 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6826
6827 END Charge_Periodicity;
6828 --Recurring Charges
6829
6830
6831 /*3605052*/
6832 FUNCTION SERVICE_PERIOD
6833 ( p_service_period IN VARCHAR2
6834 ,p_inventory_item_id IN NUMBER
6835 ) RETURN VARCHAR2
6836 IS
6837 l_service_period_dsp VARCHAR2(60) := NULL;
6838 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6839 l_temp VARCHAR2(60) := NULL;
6840 BEGIN
6841
6842 IF p_service_period IS NOT NULL THEN
6843
6844 --webroot bug 6826344 start
6845 SELECT service_item_flag
6846 INTO l_temp
6847 FROM MTL_SYSTEM_ITEMS_B
6848 WHERE inventory_item_id = p_inventory_item_id
6849 AND organization_id = OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
6850
6851 --OE_MSG_PUB.ADD_TEXT('l_temp' || l_temp);
6852 IF l_temp = 'Y' THEN
6853 --webroot bug 6826344 end
6854
6855 Select description
6856 INTO l_service_period_dsp
6857 FROM mtl_item_uoms_view
6858 WHERE uom_code =p_service_period
6859 and inventory_item_id = p_inventory_item_id
6860 and organization_id = OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
6861 END IF;
6862
6863 --OE_MSG_PUB.ADD_TEXT('l_service_period_dsp'|| l_service_period_dsp);
6864 END IF;
6865
6866 RETURN l_service_period_dsp;
6867
6868 EXCEPTION
6869
6870 WHEN NO_DATA_FOUND THEN
6871
6872 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6873 THEN
6874
6875 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6876 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','SERVICE_PERIOD');
6877 OE_MSG_PUB.Add;
6878
6879 END IF;
6880
6881 RETURN NULL;
6882
6883 WHEN OTHERS THEN
6884
6885 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6886 THEN
6887 OE_MSG_PUB.Add_Exc_Msg
6888 ( G_PKG_NAME
6889 , 'SERVICE_PERIOD'
6890 );
6891 END IF;
6892
6893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6894
6895 END SERVICE_PERIOD;
6896
6897 -- Added for bug 5701246
6898
6899 FUNCTION SERVICE_REFERENCE_TYPE
6900 ( p_service_reference_type_code IN VARCHAR2
6901 ) RETURN VARCHAR2
6902 IS
6903 l_service_reference_type VARCHAR2(240) := NULL;
6904 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6905 BEGIN
6906
6907 IF p_service_reference_type_code IS NOT NULL THEN
6908
6909 Select meaning
6910 INTO l_service_reference_type
6911 FROM oe_lookups
6912 WHERE lookup_code=p_service_Reference_type_code
6913 and lookup_type = 'SERVICE_REFERENCE_TYPE_CODE';
6914
6915 END IF;
6916
6917 RETURN l_service_reference_type;
6918
6919 EXCEPTION
6920
6921 WHEN NO_DATA_FOUND THEN
6922
6923 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6924 THEN
6925
6926 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6927 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','SERVICE_REFERENCE_TYPE');
6928 OE_MSG_PUB.Add;
6929
6930 END IF;
6931
6932 RETURN NULL;
6933
6934 WHEN OTHERS THEN
6935
6936 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6937 THEN
6938 OE_MSG_PUB.Add_Exc_Msg
6939 ( G_PKG_NAME
6940 , 'SERVICE_REFERENCE_TYPE'
6941 );
6942 END IF;
6943
6944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6945
6946 END SERVICE_REFERENCE_TYPE;
6947
6948 -- end Added for bug 5701246
6949
6950 -- END GEN Id_To_Value
6951
6952 FUNCTION CHANGE_REASON
6953 ( p_change_reason_code IN VARCHAR2
6954 ) RETURN VARCHAR2
6955 IS
6956 l_change_reason VARCHAR2(60) := NULL;
6957 l_lookup_type1 VARCHAR2(80) :='CHANGE_CODE';
6958 l_lookup_type2 VARCHAR2(80) :='CANCEL_CODE';
6959 --
6960 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6961 --
6962 BEGIN
6963
6964 IF p_change_reason_code IS NOT NULL THEN
6965
6966 SELECT meaning
6967 INTO l_change_reason
6968 FROM OE_LOOKUPS
6969 WHERE lookup_code= p_change_reason_code
6970 and (lookup_type=l_lookup_type1 or lookup_type=l_lookup_type2);
6971
6972 END IF;
6973
6974 RETURN l_change_reason;
6975
6976 EXCEPTION
6977
6978 WHEN NO_DATA_FOUND THEN
6979
6980 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
6981 THEN
6982
6983 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
6984 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CHANGE_CODE');
6985 OE_MSG_PUB.Add;
6986
6987 END IF;
6988
6989 RETURN NULL;
6990
6991 WHEN OTHERS THEN
6992
6993 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6994 THEN
6995 OE_MSG_PUB.Add_Exc_Msg
6996 ( G_PKG_NAME
6997 , 'CHANGE_CODE'
6998 );
6999 END IF;
7000
7001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7002
7003 END CHANGE_REASON;
7004
7005 --Customer Acceptance
7006 Procedure Get_Contingency_Attributes
7007 ( p_contingency_id IN NUMBER
7008 , x_contingency_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7009 , x_contingency_description OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7010 , x_expiration_event_attribute OUT NOCOPY /* file.sql.39 change */ VARCHAR2
7011 )
7012 IS
7013 l_expiration_event_code VARCHAR2(30):=NULL;
7014 --
7015 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7016 --
7017 BEGIN
7018
7019 x_contingency_name := NULL;
7020 x_contingency_description := NULL;
7021 x_expiration_event_attribute := NULL;
7022 IF p_contingency_id IS NOT NULL THEN
7023
7024 SELECT contingency_name, description, expiration_event_code
7025 INTO x_contingency_name, x_contingency_description, l_expiration_event_code
7026 FROM AR_DEFERRAL_REASONS
7027 WHERE contingency_id = p_contingency_id;
7028
7029
7030 IF l_expiration_event_code IS NOT NULL THEN
7031 SELECT MEANING
7032 INTO x_expiration_event_attribute
7033 FROM AR_LOOKUPS
7034 WHERE lookup_type='AR_EXPIRATION_EVENTS'
7035 AND lookup_code = l_expiration_event_code;
7036 END IF;
7037
7038 ELSE
7039 x_contingency_name := NULL;
7040 x_contingency_description := NULL;
7041 x_expiration_event_attribute := NULL;
7042
7043 END IF;
7044
7045 EXCEPTION
7046
7047 WHEN NO_DATA_FOUND THEN
7048
7049 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7050 THEN
7051
7052 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7053 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CONTINGENCY_ID');
7054 OE_MSG_PUB.Add;
7055
7056 END IF;
7057
7058 WHEN OTHERS THEN
7059
7060 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7061 THEN
7062 OE_MSG_PUB.Add_Exc_Msg
7063 ( G_PKG_NAME
7064 , 'Get_Contingency_Attributes'
7065 );
7066 END IF;
7067
7068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7069
7070 END Get_Contingency_Attributes;
7071
7072 FUNCTION Revrec_Event(p_revrec_event_code IN VARCHAR2) RETURN VARCHAR2
7073 IS
7074 l_revrec_event VARCHAR2(80);
7075 --
7076 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7077 --
7078 BEGIN
7079
7080 IF p_revrec_event_code IS NOT NULL THEN
7081 SELECT MEANING
7082 INTO l_revrec_event
7083 FROM AR_LOOKUPS
7084 WHERE lookup_type='AR_REVREC_EVENTS'
7085 AND lookup_code = p_revrec_event_code;
7086 ELSE
7087 l_revrec_event := NULL;
7088 END IF;
7089
7090 RETURN l_revrec_event;
7091
7092 EXCEPTION
7093
7094 WHEN NO_DATA_FOUND THEN
7095
7096 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7097 THEN
7098
7099 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7100 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','REVREC_EVENT_CODE');
7101 OE_MSG_PUB.Add;
7102
7103 END IF;
7104
7105 RETURN NULL;
7106
7107 WHEN OTHERS THEN
7108
7109 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7110 THEN
7111 OE_MSG_PUB.Add_Exc_Msg
7112 ( G_PKG_NAME
7113 , 'Revrec_Event'
7114 );
7115 END IF;
7116
7117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7118
7119 END Revrec_Event;
7120
7121 FUNCTION Accepted_By(p_accepted_by IN NUMBER) RETURN VARCHAR2
7122 IS
7123 l_accepted_by_dsp VARCHAR2(100);
7124 --
7125 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
7126 --
7127 BEGIN
7128
7129 IF p_accepted_by IS NOT NULL THEN
7130 SELECT user_name
7131 INTO l_accepted_by_dsp
7132 FROM FND_USER
7133 WHERE user_id=p_accepted_by;
7134 ELSE
7135 l_accepted_by_dsp:= NULL;
7136 END IF;
7137
7138 RETURN l_accepted_by_dsp;
7139
7140 EXCEPTION
7141
7142 WHEN NO_DATA_FOUND THEN
7143
7144 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7145 THEN
7146
7147 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7148 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ACCEPTED_BY');
7149 OE_MSG_PUB.Add;
7150
7151 END IF;
7152
7153 RETURN NULL;
7154
7155 WHEN OTHERS THEN
7156
7157 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7158 THEN
7159 OE_MSG_PUB.Add_Exc_Msg
7160 ( G_PKG_NAME
7161 , 'Accepted_By'
7162 );
7163 END IF;
7164
7165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7166
7167 END Accepted_By;
7168
7169 --sol_ord_er #16014165
7170 Function Billing_Profile (p_service_bill_profile_id IN NUMBER) RETURN VARCHAR2
7171 IS
7172 --
7173 l_billing_profile VARCHAR2(240);
7174 --
7175 BEGIN
7176
7177 IF p_service_bill_profile_id IS NOT NULL THEN
7178 SELECT Profile_number
7179 INTO l_billing_profile
7180 from oks_billing_profiles_v
7181 where Id = p_service_bill_profile_id;
7182 ELSE
7183 l_billing_profile:= NULL;
7184 END IF;
7185 RETURN l_billing_profile;
7186 EXCEPTION
7187 WHEN NO_DATA_FOUND THEN
7188 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7189 THEN
7190 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7191 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','BILLING_PROFILE');
7192 OE_MSG_PUB.Add;
7193 END IF;
7194 RETURN NULL;
7195 WHEN OTHERS THEN
7196 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7197 THEN
7198 OE_MSG_PUB.Add_Exc_Msg
7199 ( G_PKG_NAME
7200 , 'BILLING_PROFILE'
7201 );
7202 END IF;
7203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7204 END Billing_Profile;
7205
7206 Function Billing_Option (p_service_bill_option_code IN VARCHAR2) RETURN VARCHAR2
7207 IS
7208 --
7209 l_Billing_Option VARCHAR2(240);
7210 --
7211 BEGIN
7212 IF p_service_bill_option_code IS NOT NULL THEN
7213 SELECT MEANING
7214 INTO l_Billing_Option FROM OE_LOOKUPS
7215 WHERE lookup_type='OM_BILLING_OPTION_FOR_SERVICES'
7216 and LOOKUP_CODE = p_service_bill_option_code;
7217 ELSE
7218 l_Billing_Option:= NULL;
7219 END IF;
7220 RETURN l_Billing_Option;
7221 EXCEPTION
7222 WHEN NO_DATA_FOUND THEN
7223 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7224 THEN
7225 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7226 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Billing_Option');
7227 OE_MSG_PUB.Add;
7228 END IF;
7229 RETURN NULL;
7230 WHEN OTHERS THEN
7231 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7232 THEN
7233 OE_MSG_PUB.Add_Exc_Msg
7234 ( G_PKG_NAME
7235 , 'Billing_Option'
7236 );
7237 END IF;
7238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7239 END Billing_Option;
7240
7241 Function Subscription_Template (p_service_subs_template_id IN NUMBER) RETURN VARCHAR2
7242 IS
7243 --
7244 l_Subscription_Template VARCHAR2(240);
7245 --
7246 BEGIN
7247 IF p_service_subs_template_id IS NOT NULL THEN
7248 select name
7249 into l_Subscription_Template
7250 from oks_coverage_templts_v
7251 where item_type = 'SUBSCRIPTION'
7252 and id = p_service_subs_template_id ;
7253 ELSE
7254 l_Subscription_Template:= NULL;
7255 END IF;
7256 RETURN l_Subscription_Template;
7257 EXCEPTION
7258 WHEN NO_DATA_FOUND THEN
7259 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7260 THEN
7261 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7262 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Subscription_Template');
7263 OE_MSG_PUB.Add;
7264 END IF;
7265 RETURN NULL;
7266 WHEN OTHERS THEN
7267 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7268 THEN
7269 OE_MSG_PUB.Add_Exc_Msg
7270 ( G_PKG_NAME
7271 , 'Subscription_Template'
7272 );
7273 END IF;
7274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7275 END Subscription_Template;
7276
7277 Function Coverage_Template (p_service_cov_template_id IN NUMBER) RETURN VARCHAR2
7278 IS
7279 --
7280 l_Coverage_Template VARCHAR2(240);
7281 --
7282 BEGIN
7283 IF p_service_cov_template_id IS NOT NULL THEN
7284 select name
7285 into l_Coverage_Template
7286 from oks_coverage_templts_v
7287 where item_type = 'SERVICE'
7288 and id = p_service_cov_template_id ;
7289 ELSE
7290 l_Coverage_Template:= NULL;
7291 END IF;
7292 RETURN l_Coverage_Template;
7293 EXCEPTION
7294 WHEN NO_DATA_FOUND THEN
7295 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7296 THEN
7297 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7298 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Coverage_Template');
7299 OE_MSG_PUB.Add;
7300 END IF;
7301 RETURN NULL;
7302 WHEN OTHERS THEN
7303 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7304 THEN
7305 OE_MSG_PUB.Add_Exc_Msg
7306 ( G_PKG_NAME
7307 , 'Coverage_Template'
7308 );
7309 END IF;
7310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7311 END Coverage_Template;
7312
7313 Function subscription_period (p_service_period IN VARCHAR2) RETURN VARCHAR2
7314 IS
7315 --
7316 l_service_period VARCHAR2(240);
7317 --
7318 BEGIN
7319 If p_service_period is not null THEN
7320 SELECT unit_of_measure_tl description
7321 Into l_service_period
7322 FROM OKX_UNITS_OF_MEASURE_V
7323 WHERE uom_code = p_service_period;
7324 ELSE
7325 l_service_period:= NULL;
7326 END IF;
7327 RETURN l_service_period;
7328 EXCEPTION
7329 WHEN NO_DATA_FOUND THEN
7330 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
7331 THEN
7332 fnd_message.set_name('ONT','OE_ID_TO_VALUE_ERROR');
7333 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Service_Period');
7334 OE_MSG_PUB.Add;
7335 END IF;
7336 RETURN NULL;
7337 WHEN OTHERS THEN
7338 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7339 THEN
7340 OE_MSG_PUB.Add_Exc_Msg
7341 ( G_PKG_NAME
7342 , 'Service_Period'
7343 );
7344 END IF;
7345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
7346 End SUBSCRIPTION_PERIOD;
7347 --sol_ord_er #16014165 End
7348 END OE_Id_To_Value;