[Home] [Help]
PACKAGE BODY: APPS.OE_CNCL_VALUE_TO_ID
Source
1 PACKAGE BODY OE_CNCL_Value_To_Id AS
2 /* $Header: OEXVCIDB.pls 120.4.12000000.2 2007/04/20 12:31:26 smanian ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_CNCL_Value_To_Id';
7
8
9 -- Prototypes for value_to_id Functions.
10
11 -- START GEN value_to_id
12
13 -- Key Flex
14
15 FUNCTION Key_Flex
16 ( p_key_flex_code IN VARCHAR2
17 , p_structure_number IN NUMBER
18 , p_appl_short_name IN VARCHAR2
19 , p_segment_array IN FND_FLEX_EXT.SegmentArray
20 )
21 RETURN NUMBER
22 IS
23 l_id NUMBER;
24 l_segment_array FND_FLEX_EXT.SegmentArray;
25 --
26 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
27 --
28 BEGIN
29
30 IF l_debug_level > 0 THEN
31 oe_debug_pub.add( 'ENTERING OE_CNCL_VALUE_TO_ID.KEY_FLEX' , 1 ) ;
32 END IF;
33 l_segment_array := p_segment_array;
34
35 -- Convert any missing values to NULL
36
37 FOR I IN 1..l_segment_array.COUNT LOOP
38
39 IF l_segment_array(I) = FND_API.G_MISS_CHAR THEN
40 l_segment_array(I) := NULL;
41 END IF;
42
43 END LOOP;
44
45 -- Call Flex conversion routine
46
47 IF NOT FND_FLEX_EXT.get_combination_id
48 ( application_short_name => p_appl_short_name
49 , key_flex_code => p_key_flex_code
50 , structure_number => p_structure_number
51 , validation_date => NULL
52 , n_segments => l_segment_array.COUNT
53 , segments => l_segment_array
54 , combination_id => l_id
55 )
56 THEN
57
58 -- Error getting combination id.
59 -- Function has already pushed a message on the stack. Add to
60 -- the API message list.
61
62 OE_MSG_PUB.Add;
63 l_id := FND_API.G_MISS_NUM;
64
65 END IF;
66
67 RETURN l_id;
68
69 EXCEPTION
70
71 WHEN OTHERS THEN
72
73 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
74 THEN
75 OE_MSG_PUB.Add_Exc_Msg
76 ( G_PKG_NAME
77 , 'Key_Flex'
78 );
79 END IF;
80
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82
83 END Key_Flex;
84
85 -- Generator will append new prototypes before end generate comment.
86
87
88 -- Accounting_Rule (Done aksingh)
89
90 FUNCTION Accounting_Rule
91 ( p_accounting_rule IN VARCHAR2
92 ) RETURN NUMBER
93 IS
94 l_id NUMBER;
95 BEGIN
96
97 IF p_accounting_rule IS NULL
98 THEN
99 RETURN NULL;
100 END IF;
101
102 SELECT RULE_ID
103 INTO l_id
104 FROM OE_RA_RULES_V
105 WHERE NAME = p_accounting_rule;
106
107 RETURN l_id;
108
109 EXCEPTION
110
111 WHEN NO_DATA_FOUND THEN
112
113 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
114 THEN
115
116 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
117 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','accounting_rule_id');
118 OE_MSG_PUB.Add;
119
120 END IF;
121
122 RETURN FND_API.G_MISS_NUM;
123
124 WHEN OTHERS THEN
125
126 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
127 THEN
128 OE_MSG_PUB.Add_Exc_Msg
129 ( G_PKG_NAME
130 , 'Accounting_Rule'
131 );
132 END IF;
133
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135
136 END Accounting_Rule;
137
138 -- Agreement (Done aksingh)
139
140 FUNCTION Agreement
141 ( p_agreement IN VARCHAR2
142 ) RETURN NUMBER
143 IS
144 l_id NUMBER;
145 BEGIN
146
147 IF p_agreement IS NULL
148 THEN
149 RETURN NULL;
150 END IF;
151
152 SELECT AGREEMENT_ID
153 INTO l_id
154 FROM OE_AGREEMENTS_V
155 WHERE NAME = p_agreement;
156
157 RETURN l_id;
158
159 EXCEPTION
160
161 WHEN NO_DATA_FOUND THEN
162
163 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
164 THEN
165
166 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
167 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','agreement_id');
168 OE_MSG_PUB.Add;
169
170 END IF;
171
172 RETURN FND_API.G_MISS_NUM;
173
174 WHEN OTHERS THEN
175
176 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
177 THEN
178 OE_MSG_PUB.Add_Exc_Msg
179 ( G_PKG_NAME
180 , 'Agreement'
181 );
182 END IF;
183
184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
185
186 END Agreement;
187
188 -- Conversion_Type (Done aksingh)
189
190 FUNCTION Conversion_Type
191 ( p_conversion_type IN VARCHAR2
192 ) RETURN VARCHAR2
193 IS
194 l_code VARCHAR2(30);
195 BEGIN
196
197 IF p_conversion_type IS NULL
198 THEN
199 RETURN NULL;
200 END IF;
201
202 SELECT CONVERSION_TYPE
203 INTO l_code
204 FROM OE_GL_DAILY_CONVERSION_TYPES_V
205 WHERE USER_CONVERSION_TYPE = p_conversion_type;
206
207 RETURN l_code;
208
209 EXCEPTION
210
211 WHEN NO_DATA_FOUND THEN
212
213 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
214 THEN
215
216 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
217 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','conversion_type_code');
218 OE_MSG_PUB.Add;
219
220 END IF;
221
222 RETURN FND_API.G_MISS_CHAR;
223
224 WHEN OTHERS THEN
225
226 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
227 THEN
228 OE_MSG_PUB.Add_Exc_Msg
229 ( G_PKG_NAME
230 , 'Conversion_Type'
231 );
232 END IF;
233
234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
235
236 END Conversion_Type;
237
238 -- Deliver_To_Contact (Done aksingh)
239
240 FUNCTION Deliver_To_Contact
241 ( p_deliver_to_contact IN VARCHAR2
242 , p_deliver_to_org_id IN NUMBER
243 ) RETURN NUMBER
244 IS
245 l_id NUMBER;
246 BEGIN
247
248 IF p_deliver_to_contact IS NULL
249 THEN
250 RETURN NULL;
251 END IF;
252
253 SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
254 INTO l_id
255 FROM OE_CONTACTS_V CON
256 , HZ_CUST_ACCT_SITES ACCT_SITE
257 , HZ_CUST_SITE_USES_ALL SITE
258 WHERE CON.NAME = p_deliver_to_contact
259 AND CON.CUSTOMER_ID = ACCT_SITE.CUST_ACCOUNT_ID
260 AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
261 AND SITE.SITE_USE_ID = p_deliver_to_org_id;
262
263 -- Replaced ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440
264
265 RETURN l_id;
266
267 EXCEPTION
268
269 WHEN NO_DATA_FOUND THEN
270
271 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
272 THEN
273
274 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
275 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','deliver_to_contact_id');
276 OE_MSG_PUB.Add;
277
278 END IF;
279
280 RETURN FND_API.G_MISS_NUM;
281
282 WHEN OTHERS THEN
283
284 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
285 THEN
286 OE_MSG_PUB.Add_Exc_Msg
287 ( G_PKG_NAME
288 , 'Deliver_To_Contact'
289 );
290 END IF;
291
292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
293
294 END Deliver_To_Contact;
295
296 -- Deliver_To_Org (Done aksingh)
297
298 FUNCTION Deliver_To_Org
299 ( p_deliver_to_address1 IN VARCHAR2
300 , p_deliver_to_address2 IN VARCHAR2
301 , p_deliver_to_address3 IN VARCHAR2
302 , p_deliver_to_address4 IN VARCHAR2
303 , p_deliver_to_location IN VARCHAR2
304 , p_deliver_to_org IN VARCHAR2
305 , p_sold_to_org_id IN NUMBER
306 , p_deliver_to_city IN VARCHAR2 DEFAULT NULL
307 , p_deliver_to_state IN VARCHAR2 DEFAULT NULL
308 , p_deliver_to_postal_code IN VARCHAR2 DEFAULT NULL
309 , p_deliver_to_country IN VARCHAR2 DEFAULT NULL
310 ) RETURN NUMBER
311 IS
312
313 l_id NUMBER;
314 lcustomer_relations varchar2(1);
315
316 CURSOR c_deliver_to_org_id IS
317 SELECT ORGANIZATION_ID
318 FROM OE_DELIVER_TO_ORGS_V
319 WHERE ADDRESS_LINE_1 = p_deliver_to_address1
320 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
321 nvl( p_deliver_to_address2, fnd_api.g_miss_char)
322 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
323 nvl( p_deliver_to_address3, fnd_api.g_miss_char)
324 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
325 nvl( p_deliver_to_address4, fnd_api.g_miss_char)
326 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
327 nvl( p_deliver_to_city, fnd_api.g_miss_char)
328 AND nvl(STATE,fnd_api.g_miss_char) =
329 nvl( p_deliver_to_state, fnd_api.g_miss_char)
330 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
331 nvl( p_deliver_to_postal_code, fnd_api.g_miss_char)
332 AND nvl(COUNTRY,fnd_api.g_miss_char) =
333 nvl( p_deliver_to_country, fnd_api.g_miss_char)
334 --AND STATUS = 'A'
335 AND CUSTOMER_ID = p_sold_to_org_id;
336
337 CURSOR C1 IS
338 SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
339 FROM OE_DELIVER_TO_ORGS_V
340 WHERE ADDRESS_LINE_1 = p_deliver_to_address1
341 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
342 nvl( p_deliver_to_address2, fnd_api.g_miss_char)
343 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
344 nvl( p_deliver_to_address3,fnd_api.g_miss_char)
345 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
346 nvl( p_deliver_to_address4,fnd_api.g_miss_char)
347 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
348 nvl( p_deliver_to_city, fnd_api.g_miss_char)
349 AND nvl(STATE,fnd_api.g_miss_char) =
350 nvl( p_deliver_to_state, fnd_api.g_miss_char)
351 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
352 nvl( p_deliver_to_postal_code, fnd_api.g_miss_char)
353 AND nvl(COUNTRY,fnd_api.g_miss_char) =
354 nvl( p_deliver_to_country, fnd_api.g_miss_char)
355 --AND STATUS = 'A'
356 AND CUSTOMER_ID IN
357 (
358 SELECT p_sold_to_org_id FROM DUAL
359 UNION
360 SELECT CUST_ACCOUNT_ID FROM
361 HZ_CUST_ACCT_RELATE WHERE
362 RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
363 and ship_to_flag = 'Y');
364 CURSOR C2 IS
365 SELECT ORGANIZATION_ID
366 FROM OE_DELIVER_TO_ORGS_V
367 WHERE ADDRESS_LINE_1 = p_deliver_to_address1
368 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
369 nvl( p_deliver_to_address2, fnd_api.g_miss_char)
370 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
371 nvl( p_deliver_to_address3,fnd_api.g_miss_char)
372 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
373 nvl( p_deliver_to_address4,fnd_api.g_miss_char)
374 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
375 nvl( p_deliver_to_city, fnd_api.g_miss_char)
376 AND nvl(STATE,fnd_api.g_miss_char) =
377 nvl( p_deliver_to_state, fnd_api.g_miss_char)
378 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
379 nvl( p_deliver_to_postal_code, fnd_api.g_miss_char)
380 AND nvl(COUNTRY,fnd_api.g_miss_char) =
381 nvl( p_deliver_to_country, fnd_api.g_miss_char);
382 --AND STATUS = 'A';
383
384 l_org varchar2(100);
385 --
386 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
387 --
388 BEGIN
389
390 IF nvl( p_deliver_to_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
391 AND nvl( p_deliver_to_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
392 AND nvl( p_deliver_to_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
393 AND nvl( p_deliver_to_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
394 AND nvl( p_deliver_to_location,fnd_api.g_miss_char) = fnd_api.g_miss_char
395 AND nvl( p_deliver_to_org, fnd_api.g_miss_char) = fnd_api.g_miss_char
396 THEN
397 RETURN NULL;
398 END IF;
399
400
401 lcustomer_relations := OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG');
402 IF l_debug_level > 0 THEN
403 oe_debug_pub.add( 'CUSTOMER RELATIONS='||LCUSTOMER_RELATIONS ) ;
404 END IF;
405
406 IF lcustomer_relations = 'N' THEN
407
408 OPEN c_deliver_to_org_id;
409 FETCH c_deliver_to_org_id
410 INTO l_id;
411 IF c_deliver_to_org_id%FOUND then
412 CLOSE c_deliver_to_org_id;
413 return l_id;
414 ELSE
415 SELECT ORGANIZATION_ID
416 INTO l_id
417 FROM OE_DELIVER_TO_ORGS_V
418 WHERE ADDRESS_LINE_1 = p_deliver_to_address1
419 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
420 nvl( p_deliver_to_address2, fnd_api.g_miss_char)
421 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
422 nvl( p_deliver_to_address3, fnd_api.g_miss_char)
423 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
424 DECODE(STATE, NULL, NULL, STATE || ', ')||
425 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
426 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
427 nvl( p_deliver_to_address4, fnd_api.g_miss_char)
428 -- AND STATUS = 'A'
429 AND CUSTOMER_ID = p_sold_to_org_id;
430 END IF;
431
432 CLOSE c_deliver_to_org_id;
433
434 RETURN l_id;
435
436 ELSIF lcustomer_relations = 'Y' THEN
437
438 OPEN C1;
439 FETCH C1
440 INTO l_id;
441
442 IF C1%FOUND then
443 IF l_debug_level > 0 THEN
444 oe_debug_pub.add( 'FOUND' ) ;
445 END IF;
446 CLOSE C1;
447 return l_id;
448 ELSE
449 IF l_debug_level > 0 THEN
450 oe_debug_pub.add( 'NOT FOUND' ) ;
451 END IF;
452 -- select userenv('CLIENT_INFO') into l_org from dual;
453 l_org := MO_GLOBAL.Get_Current_Org_Id; --moac
454 IF l_debug_level > 0 THEN
455 oe_debug_pub.add( 'ORG='||L_ORG ) ;
456 END IF;
457
458 SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
459 INTO l_id
460 FROM OE_DELIVER_TO_ORGS_V
464 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
461 WHERE ADDRESS_LINE_1 = p_deliver_to_address1
462 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
463 nvl( p_deliver_to_address2, fnd_api.g_miss_char)
465 nvl( p_deliver_to_address3,fnd_api.g_miss_char)
466 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
467 DECODE(STATE, NULL, NULL, STATE || ', ')||
468 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
469 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
470 NVL( p_deliver_to_address4, fnd_api.g_miss_char)
471 -- AND STATUS = 'A'
472 AND CUSTOMER_ID IN
473 (SELECT p_sold_to_org_id FROM DUAL
474 UNION
475 SELECT CUST_ACCOUNT_ID FROM
476 HZ_CUST_ACCT_RELATE WHERE
477 RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
478 and ship_to_flag = 'Y');
479 IF l_debug_level > 0 THEN
480 oe_debug_pub.add( 'AFTER SELECT FOUND='||L_ID ) ;
481 END IF;
482 END IF;
483
484 CLOSE C1;
485 IF l_debug_level > 0 THEN
486 oe_debug_pub.add( 'RETURNING FROM THE FUNCTION' ) ;
487 END IF;
488 RETURN l_id;
489
490
491 ELSIF lcustomer_relations = 'A' THEN
492
493 OPEN C2;
494 FETCH C2
495 INTO l_id;
496
497 IF C2%FOUND then
498 CLOSE C2;
499 return l_id;
500 ELSE
501 SELECT ORGANIZATION_ID
502 INTO l_id
503 FROM OE_DELIVER_TO_ORGS_V
504 WHERE ADDRESS_LINE_1 = p_deliver_to_address1
505 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
506 nvl( p_deliver_to_address2, fnd_api.g_miss_char)
507 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
508 nvl( p_deliver_to_address3,fnd_api.g_miss_char)
509 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
510 DECODE(STATE, NULL, NULL, STATE || ', ')||
511 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
512 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
513 NVL( p_deliver_to_address4, fnd_api.g_miss_char);
514 -- AND STATUS = 'A';
515 END IF;
516
517 CLOSE C2;
518 RETURN l_id;
519
520 END IF;
521
522 EXCEPTION
523
524 WHEN NO_DATA_FOUND THEN
525
526 IF c_deliver_to_org_id%ISOPEN then
527 CLOSE c_deliver_to_org_id;
528 END IF;
529
530 IF C1%ISOPEN then
531 CLOSE C1;
532 END IF;
533
534 IF C2%ISOPEN then
535 CLOSE C2;
536 END IF;
537
538
539 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
540 THEN
541
542 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
543 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','deliver_to_org_id');
544 OE_MSG_PUB.Add;
545
546 END IF;
547
548 RETURN FND_API.G_MISS_NUM;
549
550 WHEN OTHERS THEN
551
552 IF c_deliver_to_org_id%ISOPEN then
553 CLOSE c_deliver_to_org_id;
554 END IF;
555
556 IF C1%ISOPEN then
557 CLOSE C1;
558 END IF;
559
560 IF C2%ISOPEN then
561 CLOSE C2;
562 END IF;
563
564
565 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
566 THEN
567 OE_MSG_PUB.Add_Exc_Msg
568 ( G_PKG_NAME
569 , 'Deliver_To_Org'
570 );
571 END IF;
572
573 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
574
575 END Deliver_To_Org;
576
577 -- Fob_Point (Done aksingh)
578
579 FUNCTION Fob_Point
580 ( p_fob_point IN VARCHAR2
581 ) RETURN VARCHAR2
582 IS
583 l_lookup_type VARCHAR2(80) :='FOB';
584 l_code VARCHAR2(30);
585 BEGIN
586
587 IF p_fob_point IS NULL
588 THEN
589 RETURN NULL;
590 END IF;
591
592 SELECT LOOKUP_CODE
593 INTO l_code
594 FROM OE_AR_LOOKUPS_V
595 WHERE MEANING = p_fob_point
596 AND LOOKUP_TYPE = l_lookup_type;
597
598 RETURN l_code;
599
600 EXCEPTION
601
602 WHEN NO_DATA_FOUND THEN
603
604 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
605 THEN
606
607 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
608 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','fob_point_code');
609 OE_MSG_PUB.Add;
610
611 END IF;
612
613 RETURN FND_API.G_MISS_CHAR;
614
615 WHEN OTHERS THEN
616
617 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
618 THEN
619 OE_MSG_PUB.Add_Exc_Msg
620 ( G_PKG_NAME
621 , 'Fob_Point'
622 );
623 END IF;
624
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626
630
627 END Fob_Point;
628
629 -- Freight_Terms (Done aksingh)
631 FUNCTION Freight_Terms
632 ( p_freight_terms IN VARCHAR2
633 ) RETURN VARCHAR2
634 IS
635 l_code VARCHAR2(30);
636 l_lookup_type VARCHAR2(80) :='FREIGHT_TERMS';
637 BEGIN
638
639 IF p_freight_terms IS NULL
640 THEN
641 RETURN NULL;
642 END IF;
643
644 SELECT LOOKUP_CODE
645 INTO l_code
646 FROM OE_LOOKUPS
647 WHERE MEANING = p_freight_terms
648 AND LOOKUP_TYPE = l_lookup_type;
649
650 RETURN l_code;
651
652 EXCEPTION
653
654 WHEN NO_DATA_FOUND THEN
655
656 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
657 THEN
658
659 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
660 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','freight_terms_code');
661 OE_MSG_PUB.Add;
662
663 END IF;
664
665 RETURN FND_API.G_MISS_CHAR;
666
667 WHEN OTHERS THEN
668
669 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
670 THEN
671 OE_MSG_PUB.Add_Exc_Msg
672 ( G_PKG_NAME
673 , 'Freight_Terms'
674 );
675 END IF;
676
677 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
678
679 END Freight_Terms;
680
681 -- Intermediate_Ship_To_Contact (Done aksingh)
682
683 FUNCTION Intermed_Ship_To_Contact
684 ( p_intermed_ship_to_contact IN VARCHAR2
685 , p_intermed_ship_to_org_id IN NUMBER
686 ) RETURN NUMBER
687 IS
688 l_id NUMBER;
689 BEGIN
690
691 IF p_intermed_ship_to_contact IS NULL
692 THEN
693 RETURN NULL;
694 END IF;
695
696 -- SELECT XXXX_id
697 -- INTO l_id
698 -- FROM XXXX_table
699 -- WHERE XXXX_val_column = p_intermed_ship_to_contact
700
701 RETURN l_id;
702
703 EXCEPTION
704
705 WHEN NO_DATA_FOUND THEN
706
707 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
708 THEN
709
710 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
711 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','intermed_ship_to_contact_id');
712 OE_MSG_PUB.Add;
713
714 END IF;
715
716 RETURN FND_API.G_MISS_NUM;
717
718
719 WHEN OTHERS THEN
720
721 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
722 THEN
723 OE_MSG_PUB.Add_Exc_Msg
724 ( G_PKG_NAME
725 , 'Intermed_Ship_To_Contact'
726 );
727 END IF;
728
729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
730
731 END Intermed_Ship_To_Contact;
732
733 -- Intermed_Ship_To_Org (Done aksingh)
734
735 FUNCTION Intermed_Ship_To_Org
736 ( p_intermed_ship_to_address1 IN VARCHAR2
737 , p_intermed_ship_to_address2 IN VARCHAR2
738 , p_intermed_ship_to_address3 IN VARCHAR2
739 , p_intermed_ship_to_address4 IN VARCHAR2
740 , p_intermed_ship_to_location IN VARCHAR2
741 , p_intermed_ship_to_org IN VARCHAR2
742 , p_sold_to_org_id IN NUMBER
743 , p_intermed_ship_to_city IN VARCHAR2 DEFAULT NULL
744 , p_intermed_ship_to_state IN VARCHAR2 DEFAULT NULL
745 , p_intermed_ship_to_postal_code IN VARCHAR2 DEFAULT NULL
746 , p_intermed_ship_to_country IN VARCHAR2 DEFAULT NULL
747 ) RETURN NUMBER
748 IS
749 l_id NUMBER;
750 BEGIN
751
752 l_id := Ship_To_Org
753 ( p_ship_to_address1=>p_intermed_ship_to_address1
754 , p_ship_to_address2=>p_intermed_ship_to_address2
755 , p_ship_to_address3=>p_intermed_ship_to_address3
756 , p_ship_to_address4=>p_intermed_ship_to_address4
757 , p_ship_to_location=>p_intermed_ship_to_location
758 , p_ship_to_org=>p_intermed_ship_to_org
759 , p_sold_to_org_id=>p_sold_to_org_id
760 , p_ship_to_city=>p_intermed_ship_to_city
761 , p_ship_to_state=>p_intermed_ship_to_state
762 , p_ship_to_postal_code=>p_intermed_ship_to_postal_code
763 , p_ship_to_country=>p_intermed_ship_to_country
764 );
765
766 RETURN l_id;
767
768 EXCEPTION
769
770 WHEN NO_DATA_FOUND THEN
771
772 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
773 THEN
774
775 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
776 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','intermed_ship_to_org_id');
777 OE_MSG_PUB.Add;
778
779 END IF;
780
781 RETURN FND_API.G_MISS_NUM;
782
786 THEN
783 WHEN OTHERS THEN
784
785 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
787 OE_MSG_PUB.Add_Exc_Msg
788 ( G_PKG_NAME
789 , 'Intermed_Ship_To_Org'
790 );
791 END IF;
792
793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794
795 END Intermed_Ship_To_Org;
796
797 -- Invoice_To_Contact (Done aksingh)
798
799 FUNCTION Invoice_To_Contact
800 ( p_invoice_to_contact IN VARCHAR2
801 , p_invoice_to_org_id IN NUMBER
802 ) RETURN NUMBER
803 IS
804 l_id NUMBER;
805 BEGIN
806
807 IF p_invoice_to_contact IS NULL
808 THEN
809 RETURN NULL;
810 END IF;
811
812 SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
813 INTO l_id
814 FROM OE_CONTACTS_V CON
815 , HZ_CUST_ACCT_SITES ACCT_SITE
816 , HZ_CUST_SITE_USES_ALL SITE
817 WHERE CON.NAME = p_invoice_to_contact
818 AND CON.CUSTOMER_ID = ACCT_SITE.CUST_ACCOUNT_ID
819 AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
820 AND SITE.SITE_USE_ID = p_invoice_to_org_id;
821
822 -- Replaced ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440
823
824 RETURN l_id;
825
826 EXCEPTION
827
828 WHEN NO_DATA_FOUND THEN
829
830 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
831 THEN
832
833 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
834 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoice_to_contact_id');
835 OE_MSG_PUB.Add;
836
837 END IF;
838
839 RETURN FND_API.G_MISS_NUM;
840
841 WHEN OTHERS THEN
842
843 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
844 THEN
845 OE_MSG_PUB.Add_Exc_Msg
846 ( G_PKG_NAME
847 , 'Invoice_To_Contact'
848 );
849 END IF;
850
851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
852
853 END Invoice_To_Contact;
854
855 -- Invoice_To_Org (Done aksingh)
856
857 /* Commented the following definition of the function invoice_to_org and added a new one to fix the bug 2002486
858 FUNCTION Invoice_To_Org
859 ( p_invoice_to_address1 IN VARCHAR2
860 , p_invoice_to_address2 IN VARCHAR2
861 , p_invoice_to_address3 IN VARCHAR2
862 , p_invoice_to_address4 IN VARCHAR2
863 , p_invoice_to_location IN VARCHAR2
864 , p_invoice_to_org IN VARCHAR2
865 , p_sold_to_org_id IN NUMBER
866 , p_invoice_to_city IN VARCHAR2 DEFAULT NULL
867 , p_invoice_to_state IN VARCHAR2 DEFAULT NULL
868 , p_invoice_to_postal_code IN VARCHAR2 DEFAULT NULL
869 , p_invoice_to_country IN VARCHAR2 DEFAULT NULL
870 ) RETURN NUMBER
871 IS
872
873 l_id NUMBER;
874
875 CURSOR c_invoice_to_org_id IS
876 SELECT ORGANIZATION_ID
877 FROM OE_INVOICE_TO_ORGS_V
878 WHERE ADDRESS_LINE_1 = p_invoice_to_address1
879 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
880 nvl( p_invoice_to_address2, fnd_api.g_miss_char)
881 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
882 nvl( p_invoice_to_address3,fnd_api.g_miss_char)
883 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
884 nvl( p_invoice_to_address4,fnd_api.g_miss_char)
885 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
886 nvl( p_invoice_to_city, fnd_api.g_miss_char)
887 AND nvl(STATE,fnd_api.g_miss_char) =
888 nvl( p_invoice_to_state, fnd_api.g_miss_char)
889 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
890 nvl( p_invoice_to_postal_code, fnd_api.g_miss_char)
891 AND nvl(COUNTRY,fnd_api.g_miss_char) =
892 nvl( p_invoice_to_country, fnd_api.g_miss_char)
893 AND STATUS = 'A'
894 AND CUSTOMER_ID = p_sold_to_org_id;
895
896 BEGIN
897
898 IF p_invoice_to_address1 IS NULL
899 OR p_invoice_to_address2 IS NULL
900 OR p_invoice_to_address3 IS NULL
901 OR p_invoice_to_address4 IS NULL
902 OR p_invoice_to_location IS NULL
903 OR p_invoice_to_org IS NULL
904 THEN
905 RETURN NULL;
906 END IF;
907
908
909 OPEN c_invoice_to_org_id;
910 FETCH c_invoice_to_org_id
911 INTO l_id;
912
913 IF c_invoice_to_org_id%FOUND then
914 CLOSE c_invoice_to_org_id;
915 return l_id;
916 ELSE
917 SELECT ORGANIZATION_ID
918 INTO l_id
919 FROM OE_INVOICE_TO_ORGS_V
920 WHERE ADDRESS_LINE_1 = p_invoice_to_address1
921 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
922 nvl( p_invoice_to_address2, fnd_api.g_miss_char)
923 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
924 nvl( p_invoice_to_address3,fnd_api.g_miss_char)
928 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
925 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
926 DECODE(STATE, NULL, NULL, STATE || ', ')||
927 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
929 NVL( p_invoice_to_address4, fnd_api.g_miss_char)
930 AND STATUS = 'A'
931 AND CUSTOMER_ID = p_sold_to_org_id;
932 END IF;
933
934 CLOSE c_invoice_to_org_id;
935
936 RETURN l_id;
937
938 EXCEPTION
939
940 WHEN NO_DATA_FOUND THEN
941
942 IF c_invoice_to_org_id%ISOPEN then
943 CLOSE c_invoice_to_org_id;
944 END IF;
945
946 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
947 THEN
948
949 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
950 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoice_to_org_id');
951 OE_MSG_PUB.Add;
952
953 END IF;
954
955 RETURN FND_API.G_MISS_NUM;
956
957 WHEN OTHERS THEN
958
959 IF c_invoice_to_org_id%ISOPEN then
960 CLOSE c_invoice_to_org_id;
961 END IF;
962
963 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
964 THEN
965 OE_MSG_PUB.Add_Exc_Msg
966 ( G_PKG_NAME
967 , 'Invoice_To_Org'
968 );
969 END IF;
970
971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972
973 END Invoice_To_Org;
974 */
975 /* Added the new definition of the finction invoice_to_org to fix the bug 2002486 */
976 FUNCTION Invoice_To_Org
977 ( p_invoice_to_address1 IN VARCHAR2
978 , p_invoice_to_address2 IN VARCHAR2
979 , p_invoice_to_address3 IN VARCHAR2
980 , p_invoice_to_address4 IN VARCHAR2
981 , p_invoice_to_location IN VARCHAR2
982 , p_invoice_to_org IN VARCHAR2
983 , p_sold_to_org_id IN NUMBER
984 , p_invoice_to_city IN VARCHAR2 DEFAULT NULL
985 , p_invoice_to_state IN VARCHAR2 DEFAULT NULL
986 , p_invoice_to_postal_code IN VARCHAR2 DEFAULT NULL
987 , p_invoice_to_country IN VARCHAR2 DEFAULT NULL
988 ) RETURN NUMBER
989 IS
990
991 l_id NUMBER;
992 lcustomer_relations varchar2(1);
993
994 CURSOR c_invoice_to_org_id IS
995 SELECT ORGANIZATION_ID
996 FROM OE_INVOICE_TO_ORGS_V
997 WHERE ADDRESS_LINE_1 = p_invoice_to_address1
998 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
999 nvl( p_invoice_to_address2, fnd_api.g_miss_char)
1000 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1001 nvl( p_invoice_to_address3,fnd_api.g_miss_char)
1002 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
1003 nvl( p_invoice_to_address4,fnd_api.g_miss_char)
1004 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
1005 nvl( p_invoice_to_city, fnd_api.g_miss_char)
1006 AND nvl(STATE,fnd_api.g_miss_char) =
1007 nvl( p_invoice_to_state, fnd_api.g_miss_char)
1008 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
1009 nvl( p_invoice_to_postal_code, fnd_api.g_miss_char)
1010 AND nvl(COUNTRY,fnd_api.g_miss_char) =
1011 nvl( p_invoice_to_country, fnd_api.g_miss_char)
1012 AND STATUS = 'A'
1013 AND CUSTOMER_ID = p_sold_to_org_id;
1014
1015 CURSOR C1 IS
1016 SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
1017 FROM OE_INVOICE_TO_ORGS_V
1018 WHERE ADDRESS_LINE_1 = p_invoice_to_address1
1019 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1020 nvl( p_invoice_to_address2, fnd_api.g_miss_char)
1021 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1022 nvl( p_invoice_to_address3,fnd_api.g_miss_char)
1023 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
1024 nvl( p_invoice_to_address4,fnd_api.g_miss_char)
1025 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
1026 nvl( p_invoice_to_city, fnd_api.g_miss_char)
1027 AND nvl(STATE,fnd_api.g_miss_char) =
1028 nvl( p_invoice_to_state, fnd_api.g_miss_char)
1029 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
1030 nvl( p_invoice_to_postal_code, fnd_api.g_miss_char)
1031 AND nvl(COUNTRY,fnd_api.g_miss_char) =
1032 nvl( p_invoice_to_country, fnd_api.g_miss_char)
1033 AND STATUS = 'A'
1034 AND CUSTOMER_ID IN
1035 (
1036 SELECT p_sold_to_org_id FROM DUAL
1037 UNION
1038 SELECT CUST_ACCOUNT_ID FROM
1039 HZ_CUST_ACCT_RELATE WHERE
1040 RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
1041 and bill_to_flag = 'Y');
1042 CURSOR C2 IS
1043 SELECT ORGANIZATION_ID
1044 FROM OE_INVOICE_TO_ORGS_V
1045 WHERE ADDRESS_LINE_1 = p_invoice_to_address1
1046 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1047 nvl( p_invoice_to_address2, fnd_api.g_miss_char)
1048 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1049 nvl( p_invoice_to_address3,fnd_api.g_miss_char)
1050 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
1054 AND nvl(STATE,fnd_api.g_miss_char) =
1051 nvl( p_invoice_to_address4,fnd_api.g_miss_char)
1052 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
1053 nvl( p_invoice_to_city, fnd_api.g_miss_char)
1055 nvl( p_invoice_to_state, fnd_api.g_miss_char)
1056 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
1057 nvl( p_invoice_to_postal_code, fnd_api.g_miss_char)
1058 AND nvl(COUNTRY,fnd_api.g_miss_char) =
1059 nvl( p_invoice_to_country, fnd_api.g_miss_char)
1060 AND STATUS = 'A';
1061 --
1062 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1063 --
1064 BEGIN
1065
1066 IF l_debug_level > 0 THEN
1067 oe_debug_pub.add( 'INVOICE_TO_ORG VALUES ARE SOLD_TO_ORG_ID='||P_SOLD_TO_ORG_ID||' ADDRESS1='||P_INVOICE_TO_ADDRESS1||' ADDRESS4='||P_INVOICE_TO_ADDRESS4 ) ;
1068 END IF;
1069 IF p_invoice_to_address1 IS NULL
1070 OR p_invoice_to_address2 IS NULL
1071 OR p_invoice_to_address3 IS NULL
1072 OR p_invoice_to_address4 IS NULL
1073 OR p_invoice_to_location IS NULL
1074 OR p_invoice_to_org IS NULL
1075 THEN
1076 RETURN NULL;
1077 END IF;
1078 lcustomer_relations := OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG');
1079
1080 IF lcustomer_relations = 'N' THEN
1081
1082 OPEN c_invoice_to_org_id;
1083 FETCH c_invoice_to_org_id
1084 INTO l_id;
1085
1086 IF c_invoice_to_org_id%FOUND then
1087 CLOSE c_invoice_to_org_id;
1088 return l_id;
1089 ELSE
1090 SELECT ORGANIZATION_ID
1091 INTO l_id
1092 FROM OE_INVOICE_TO_ORGS_V
1093 WHERE ADDRESS_LINE_1 = p_invoice_to_address1
1094 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1095 nvl( p_invoice_to_address2, fnd_api.g_miss_char)
1096 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1097 nvl( p_invoice_to_address3,fnd_api.g_miss_char)
1098 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
1099 DECODE(STATE, NULL, NULL, STATE || ', ')||
1100 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
1101 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
1102 NVL( p_invoice_to_address4, fnd_api.g_miss_char)
1103 AND STATUS = 'A'
1104 AND CUSTOMER_ID = p_sold_to_org_id;
1105 END IF;
1106
1107 CLOSE c_invoice_to_org_id;
1108 RETURN l_id;
1109
1110 ELSIF lcustomer_relations = 'Y' THEN
1111
1112 OPEN C1;
1113 FETCH C1
1114 INTO l_id;
1115
1116 IF C1%FOUND then
1117 CLOSE C1;
1118 return l_id;
1119 ELSE
1120 SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
1121 INTO l_id
1122 FROM OE_INVOICE_TO_ORGS_V
1123 WHERE ADDRESS_LINE_1 = p_invoice_to_address1
1124 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1125 nvl( p_invoice_to_address2, fnd_api.g_miss_char)
1126 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1127 nvl( p_invoice_to_address3,fnd_api.g_miss_char)
1128 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
1129 DECODE(STATE, NULL, NULL, STATE || ', ')||
1130 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
1131 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
1132 NVL( p_invoice_to_address4, fnd_api.g_miss_char)
1133 AND STATUS = 'A'
1134 AND CUSTOMER_ID IN
1135 (SELECT p_sold_to_org_id FROM DUAL
1136 UNION
1137 SELECT CUST_ACCOUNT_ID FROM
1138 HZ_CUST_ACCT_RELATE WHERE
1139 RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
1140 and bill_to_flag = 'Y');
1141 END IF;
1142
1143 CLOSE C1;
1144 RETURN l_id;
1145
1146 null;
1147
1148 ELSIF lcustomer_relations = 'A' THEN
1149
1150 OPEN C2;
1151 FETCH C2
1152 INTO l_id;
1153
1154 IF C2%FOUND then
1155 CLOSE C2;
1156 return l_id;
1157 ELSE
1158 SELECT ORGANIZATION_ID
1159 INTO l_id
1160 FROM OE_INVOICE_TO_ORGS_V
1161 WHERE ADDRESS_LINE_1 = p_invoice_to_address1
1162 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1163 nvl( p_invoice_to_address2, fnd_api.g_miss_char)
1164 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1165 nvl( p_invoice_to_address3,fnd_api.g_miss_char)
1166 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
1167 DECODE(STATE, NULL, NULL, STATE || ', ')||
1168 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
1169 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
1170 NVL( p_invoice_to_address4, fnd_api.g_miss_char)
1171 AND STATUS = 'A';
1172 END IF;
1173
1174 CLOSE C2;
1175 RETURN l_id;
1176
1177 END IF;
1178
1179 EXCEPTION
1180
1181 WHEN NO_DATA_FOUND THEN
1182
1183 IF c_invoice_to_org_id%ISOPEN then
1184 CLOSE c_invoice_to_org_id;
1185 END IF;
1186
1187 IF C1%ISOPEN then
1188 CLOSE C1;
1192 CLOSE C2;
1189 END IF;
1190
1191 IF C2%ISOPEN then
1193 END IF;
1194
1195 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1196 THEN
1197
1198 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1199 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoice_to_org_id');
1200 OE_MSG_PUB.Add;
1201
1202 END IF;
1203
1204 RETURN FND_API.G_MISS_NUM;
1205
1206 WHEN OTHERS THEN
1207
1208 IF c_invoice_to_org_id%ISOPEN then
1209 CLOSE c_invoice_to_org_id;
1210 END IF;
1211
1212 IF C1%ISOPEN then
1213 CLOSE C1;
1214 END IF;
1215
1216 IF C2%ISOPEN then
1217 CLOSE C2;
1218 END IF;
1219
1220 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1221 THEN
1222 OE_MSG_PUB.Add_Exc_Msg
1223 ( G_PKG_NAME
1224 , 'Invoice_To_Org'
1225 );
1226 END IF;
1227
1228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1229
1230 END Invoice_To_Org;
1231
1232 -- Invoicing_Rule (Done aksingh)
1233
1234 FUNCTION Invoicing_Rule
1235 ( p_invoicing_rule IN VARCHAR2
1236 ) RETURN NUMBER
1237 IS
1238 l_id NUMBER;
1239 BEGIN
1240
1241 IF p_invoicing_rule IS NULL
1242 THEN
1243 RETURN NULL;
1244 END IF;
1245
1246 SELECT RULE_ID
1247 INTO l_id
1248 FROM OE_RA_RULES_V
1249 WHERE NAME = p_invoicing_rule;
1250
1251 RETURN l_id;
1252
1253 EXCEPTION
1254
1255 WHEN NO_DATA_FOUND THEN
1256
1257 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1258 THEN
1259
1260 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1261 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoicing_rule_id');
1262 OE_MSG_PUB.Add;
1263
1264 END IF;
1265
1266 RETURN FND_API.G_MISS_NUM;
1267
1268 WHEN OTHERS THEN
1269
1270 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1271 THEN
1272 OE_MSG_PUB.Add_Exc_Msg
1273 ( G_PKG_NAME
1274 , 'Invoicing_Rule'
1275 );
1276 END IF;
1277
1278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1279
1280 END Invoicing_Rule;
1281
1282 -- Order_Source (Done aksingh)
1283
1284 FUNCTION Order_Source
1285 ( p_order_source IN VARCHAR2
1286 ) RETURN NUMBER
1287 IS
1288 l_id NUMBER;
1289 BEGIN
1290
1291 IF p_order_source IS NULL
1292 THEN
1293 RETURN NULL;
1294 END IF;
1295
1296 SELECT ORDER_SOURCE_ID
1297 INTO l_id
1298 FROM OE_ORDER_SOURCES
1299 WHERE NAME = p_order_source;
1300
1301 -- SELECT XXXX_id
1302 -- INTO l_id
1303 -- FROM XXXX_table
1304 -- WHERE XXXX_val_column = p_order_source
1305
1306 RETURN l_id;
1307
1308 EXCEPTION
1309
1310 WHEN NO_DATA_FOUND THEN
1311
1312 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1313 THEN
1314
1315 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1316 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','order_source_id');
1317 OE_MSG_PUB.Add;
1318
1319 END IF;
1320
1321 RETURN FND_API.G_MISS_NUM;
1322
1323 WHEN OTHERS THEN
1324
1325 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1326 THEN
1327 OE_MSG_PUB.Add_Exc_Msg
1328 ( G_PKG_NAME
1329 , 'Order_Source'
1330 );
1331 END IF;
1332
1333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1334
1335 END Order_Source;
1336
1337 -- Order_Type (Done aksingh)
1338
1339 FUNCTION Order_Type
1340 ( p_order_type IN VARCHAR2
1341 ) RETURN NUMBER
1342 IS
1343 l_id NUMBER;
1344 BEGIN
1345
1346 IF p_order_type IS NULL
1347 THEN
1348 RETURN NULL;
1349 END IF;
1350
1351 SELECT ORDER_TYPE_ID
1352 INTO l_id
1353 FROM OE_ORDER_TYPES_v
1354 WHERE NAME = p_order_type;
1355
1356 RETURN l_id;
1357
1358 EXCEPTION
1359
1360 WHEN NO_DATA_FOUND THEN
1361
1362 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1363 THEN
1364
1365 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1366 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','order_type_id');
1367 OE_MSG_PUB.Add;
1368
1369 END IF;
1370
1371 RETURN FND_API.G_MISS_NUM;
1372
1373 WHEN OTHERS THEN
1374
1378 ( G_PKG_NAME
1375 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1376 THEN
1377 OE_MSG_PUB.Add_Exc_Msg
1379 , 'Order_Type'
1380 );
1381 END IF;
1382
1383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1384
1385 END Order_Type;
1386
1387 -- Payment_Term (Done aksingh)
1388
1389 FUNCTION Payment_Term
1390 ( p_payment_term IN VARCHAR2
1391 ) RETURN NUMBER
1392 IS
1393 l_id NUMBER;
1394 BEGIN
1395
1396 IF p_payment_term IS NULL
1397 THEN
1398 RETURN NULL;
1399 END IF;
1400
1401 SELECT TERM_ID
1402 INTO l_id
1403 FROM OE_RA_TERMS_V
1404 WHERE NAME = p_payment_term;
1405
1406 RETURN l_id;
1407
1408 EXCEPTION
1409
1410 WHEN NO_DATA_FOUND THEN
1411
1412 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1413 THEN
1414
1415 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1416 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','payment_term_id');
1417 OE_MSG_PUB.Add;
1418
1419 END IF;
1420
1421 RETURN FND_API.G_MISS_NUM;
1422
1423 WHEN OTHERS THEN
1424
1425 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1426 THEN
1427 OE_MSG_PUB.Add_Exc_Msg
1428 ( G_PKG_NAME
1429 , 'Payment_Term'
1430 );
1431 END IF;
1432
1433 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1434
1435 END Payment_Term;
1436
1437 -- Price_List (Done aksingh)
1438
1439 FUNCTION Price_List
1440 ( p_price_list IN VARCHAR2
1441 ) RETURN NUMBER
1442 IS
1443 l_id NUMBER;
1444 BEGIN
1445
1446 IF p_price_list IS NULL
1447 THEN
1448 RETURN NULL;
1449 END IF;
1450
1451 SELECT LIST_HEADER_ID
1452 INTO l_id
1453 FROM qp_list_headers_vl
1454 WHERE NAME = p_price_list
1455 AND list_type_code='PRL';
1456
1457 RETURN l_id;
1458
1459 EXCEPTION
1460
1461 WHEN NO_DATA_FOUND THEN
1462
1463 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1464 THEN
1465
1466 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1467 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','price_list_id');
1468 OE_MSG_PUB.Add;
1469
1470 END IF;
1471
1472 RETURN FND_API.G_MISS_NUM;
1473
1474 WHEN OTHERS THEN
1475
1476 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1477 THEN
1478 OE_MSG_PUB.Add_Exc_Msg
1479 ( G_PKG_NAME
1480 , 'Price_List'
1481 );
1482 END IF;
1483
1484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1485
1486 END Price_List;
1487
1488 -- Shipment_Priority (Done aksingh)
1489
1490 FUNCTION Shipment_Priority
1491 ( p_shipment_priority IN VARCHAR2
1492 ) RETURN VARCHAR2
1493 IS
1494 l_code VARCHAR2(30);
1495 l_lookup_type VARCHAR2(80) :='SHIPMENT_PRIORITY';
1496 BEGIN
1497
1498 IF p_shipment_priority IS NULL
1499 THEN
1500 RETURN NULL;
1501 END IF;
1502
1503 SELECT LOOKUP_CODE
1504 INTO l_code
1505 FROM OE_LOOKUPS
1506 WHERE MEANING = p_shipment_priority
1507 AND LOOKUP_TYPE = l_lookup_type;
1508
1509 RETURN l_code;
1510
1511 EXCEPTION
1512
1513 WHEN NO_DATA_FOUND THEN
1514
1515 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1516 THEN
1517
1518 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1519 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','shipment_priority_code');
1520 OE_MSG_PUB.Add;
1521
1522 END IF;
1523
1524 RETURN FND_API.G_MISS_CHAR;
1525
1526 WHEN OTHERS THEN
1527
1528 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1529 THEN
1530 OE_MSG_PUB.Add_Exc_Msg
1531 ( G_PKG_NAME
1532 , 'Shipment_Priority'
1533 );
1534 END IF;
1535
1536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1537
1538 END Shipment_Priority;
1539
1540 -- Ship_From_Org (Done aksingh)
1541
1542 FUNCTION Ship_From_Org
1543 ( p_ship_from_address1 IN VARCHAR2
1544 , p_ship_from_address2 IN VARCHAR2
1545 , p_ship_from_address3 IN VARCHAR2
1546 , p_ship_from_address4 IN VARCHAR2
1547 , p_ship_from_location IN VARCHAR2
1548 , p_ship_from_org IN VARCHAR2
1549 ) RETURN NUMBER
1550 IS
1554 IF p_ship_from_address1 IS NULL
1551 l_id NUMBER;
1552 BEGIN
1553
1555 OR p_ship_from_address2 IS NULL
1556 OR p_ship_from_address3 IS NULL
1557 OR p_ship_from_address4 IS NULL
1558 OR p_ship_from_location IS NULL
1559 OR p_ship_from_org IS NULL
1560 THEN
1561 RETURN NULL;
1562 END IF;
1563
1564 -- SELECT XXXX_id
1565 -- INTO l_id
1566 -- FROM XXXX_table
1567 -- WHERE XXXX_val_column = p_ship_from_address1
1568 -- AND XXXX_val_column = p_ship_from_address2
1569 -- AND XXXX_val_column = p_ship_from_address3
1570 -- AND XXXX_val_column = p_ship_from_address4
1571 -- AND XXXX_val_column = p_ship_from_location
1572 -- AND XXXX_val_column = p_ship_from_org
1573
1574 RETURN l_id;
1575
1576 EXCEPTION
1577
1578 WHEN NO_DATA_FOUND THEN
1579
1580 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1581 THEN
1582
1583 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1584 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_from_org_id');
1585 OE_MSG_PUB.Add;
1586
1587 END IF;
1588
1589 RETURN FND_API.G_MISS_NUM;
1590
1591 WHEN OTHERS THEN
1592
1593 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1594 THEN
1595 OE_MSG_PUB.Add_Exc_Msg
1596 ( G_PKG_NAME
1597 , 'Ship_From_Org'
1598 );
1599 END IF;
1600
1601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1602
1603 END Ship_From_Org;
1604
1605 -- Ship_To_Contact (Done aksingh)
1606
1607 FUNCTION Ship_To_Contact
1608 ( p_ship_to_contact IN VARCHAR2
1609 , p_ship_to_org_id IN NUMBER
1610 ) RETURN NUMBER
1611 IS
1612 l_id NUMBER;
1613 BEGIN
1614
1615 IF p_ship_to_contact IS NULL
1616 THEN
1617 RETURN NULL;
1618 END IF;
1619
1620
1621 SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
1622 INTO l_id
1623 FROM OE_CONTACTS_V CON
1624 , HZ_CUST_ACCT_SITES ACCT_SITE
1625 , HZ_CUST_SITE_USES_ALL SITE
1626 WHERE CON.NAME = p_ship_to_contact
1627 AND CON.CUSTOMER_ID = ACCT_SITE.CUST_ACCOUNT_ID
1628 AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
1629 AND SITE.SITE_USE_ID = p_ship_to_org_id;
1630
1631 -- Replaced ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440
1632
1633 RETURN l_id;
1634
1635 EXCEPTION
1636
1637 WHEN NO_DATA_FOUND THEN
1638
1639 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1640 THEN
1641
1642 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1643 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_to_contact_id');
1644 OE_MSG_PUB.Add;
1645
1646 END IF;
1647
1648 RETURN FND_API.G_MISS_NUM;
1649
1650 WHEN OTHERS THEN
1651
1652 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1653 THEN
1654 OE_MSG_PUB.Add_Exc_Msg
1655 ( G_PKG_NAME
1656 , 'Ship_To_Contact'
1657 );
1658 END IF;
1659
1660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1661
1662 END Ship_To_Contact;
1663
1664 FUNCTION Inventory_Org
1665 ( p_inventory_org IN VARCHAR2
1666 ) RETURN NUMBER
1667 IS
1668 l_id NUMBER;
1669 BEGIN
1670
1671 IF p_inventory_org IS NULL
1672 THEN
1673 RETURN NULL;
1674 END IF;
1675
1676 SELECT organization_id
1677 INTO l_id
1678 FROM org_organization_definitions
1679 WHERE organization_name = p_inventory_org;
1680
1681 RETURN l_id;
1682
1683 EXCEPTION
1684
1685 WHEN NO_DATA_FOUND THEN
1686
1687 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1688 THEN
1689
1690 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1691 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','inventory_org_id');
1692 OE_MSG_PUB.Add;
1693
1694 END IF;
1695
1696 RETURN FND_API.G_MISS_NUM;
1697
1698 WHEN OTHERS THEN
1699
1700 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1701 THEN
1702 OE_MSG_PUB.Add_Exc_Msg
1703 ( G_PKG_NAME
1704 , 'inventory_org'
1705 );
1706 END IF;
1707
1708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1709
1710 END Inventory_Org;
1711
1712 -- Ship_To_Org (Done aksingh)
1713
1714 /* Commented the function ship_to_org and added the new definition of this function to fix the bug 2002486
1715 FUNCTION Ship_To_Org
1716 ( p_ship_to_address1 IN VARCHAR2
1720 , p_ship_to_location IN VARCHAR2
1717 , p_ship_to_address2 IN VARCHAR2
1718 , p_ship_to_address3 IN VARCHAR2
1719 , p_ship_to_address4 IN VARCHAR2
1721 , p_ship_to_org IN VARCHAR2
1722 , p_sold_to_org_id IN NUMBER
1723 , p_ship_to_city IN VARCHAR2 DEFAULT NULL
1724 , p_ship_to_state IN VARCHAR2 DEFAULT NULL
1725 , p_ship_to_postal_code IN VARCHAR2 DEFAULT NULL
1726 , p_ship_to_country IN VARCHAR2 DEFAULT NULL
1727 ) RETURN NUMBER
1728 IS
1729
1730 l_id NUMBER;
1731
1732 CURSOR c_ship_to_org_id IS
1733 SELECT ORGANIZATION_ID
1734 FROM OE_SHIP_TO_ORGS_V
1735 WHERE ADDRESS_LINE_1 = p_ship_to_address1
1736 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1737 nvl( p_ship_to_address2, fnd_api.g_miss_char)
1738 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1739 nvl( p_ship_to_address3, fnd_api.g_miss_char)
1740 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
1741 nvl( p_ship_to_address4, fnd_api.g_miss_char)
1742 AND nvl( town_or_city, fnd_api.g_miss_char) =
1743 nvl( p_ship_to_city, fnd_api.g_miss_char)
1744 AND nvl( state, fnd_api.g_miss_char) =
1745 nvl( p_ship_to_state, fnd_api.g_miss_char)
1746 AND nvl( postal_code, fnd_api.g_miss_char) =
1747 nvl( p_ship_to_postal_code, fnd_api.g_miss_char)
1748 AND nvl( country, fnd_api.g_miss_char) =
1749 nvl( p_ship_to_country, fnd_api.g_miss_char)
1750 AND STATUS = 'A'
1751 AND CUSTOMER_ID = p_sold_to_org_id;
1752
1753 BEGIN
1754
1755 IF p_ship_to_address1 IS NULL
1756 OR p_ship_to_address2 IS NULL
1757 OR p_ship_to_address3 IS NULL
1758 OR p_ship_to_address4 IS NULL
1759 OR p_ship_to_location IS NULL
1760 OR p_ship_to_org IS NULL
1761 THEN
1762 RETURN NULL;
1763 END IF;
1764
1765
1766 OPEN c_ship_to_org_id;
1767 FETCH c_ship_to_org_id
1768 INTO l_id;
1769 IF c_ship_to_org_id%FOUND then
1770 CLOSE c_ship_to_org_id;
1771 return l_id;
1772 ELSE
1773
1774 SELECT ORGANIZATION_ID
1775 INTO l_id
1776 FROM OE_SHIP_TO_ORGS_V
1777 WHERE ADDRESS_LINE_1 = p_ship_to_address1
1778 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1779 nvl( p_ship_to_address2, fnd_api.g_miss_char)
1780 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1781 nvl( p_ship_to_address3, fnd_api.g_miss_char)
1782 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
1783 DECODE(STATE, NULL, NULL, STATE || ', ')||
1784 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
1785 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
1786 nvl( p_ship_to_address4, fnd_api.g_miss_char)
1787 AND STATUS = 'A'
1788 AND CUSTOMER_ID = p_sold_to_org_id;
1789 END IF;
1790 CLOSE c_ship_to_org_id;
1791
1792 RETURN l_id;
1793
1794 EXCEPTION
1795
1796 WHEN NO_DATA_FOUND THEN
1797
1798 IF c_ship_to_org_id%ISOPEN then
1799 CLOSE c_ship_to_org_id;
1800 END IF;
1801
1802 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1803 THEN
1804
1805 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
1806 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_to_org_id');
1807 OE_MSG_PUB.Add;
1808
1809 END IF;
1810
1811 RETURN FND_API.G_MISS_NUM;
1812
1813 WHEN OTHERS THEN
1814
1815 IF c_ship_to_org_id%ISOPEN then
1816 CLOSE c_ship_to_org_id;
1817 END IF;
1818
1819 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1820 THEN
1821 OE_MSG_PUB.Add_Exc_Msg
1822 ( G_PKG_NAME
1823 , 'Ship_To_Org'
1824 );
1825 END IF;
1826
1827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1828
1829 END Ship_To_Org;
1830 */
1831
1832 /* Added the new definition of the function ship_to_org to fix the bug 2002486 */
1833 FUNCTION Ship_To_Org
1834 ( p_ship_to_address1 IN VARCHAR2
1835 , p_ship_to_address2 IN VARCHAR2
1836 , p_ship_to_address3 IN VARCHAR2
1837 , p_ship_to_address4 IN VARCHAR2
1838 , p_ship_to_location IN VARCHAR2
1839 , p_ship_to_org IN VARCHAR2
1840 , p_sold_to_org_id IN NUMBER
1841 , p_ship_to_city IN VARCHAR2 DEFAULT NULL
1842 , p_ship_to_state IN VARCHAR2 DEFAULT NULL
1843 , p_ship_to_postal_code IN VARCHAR2 DEFAULT NULL
1844 , p_ship_to_country IN VARCHAR2 DEFAULT NULL
1845 ) RETURN NUMBER
1846 IS
1847
1848 l_id NUMBER;
1849 lcustomer_relations varchar2(1);
1850
1851
1852 CURSOR c_ship_to_org_id IS
1853 SELECT ORGANIZATION_ID
1854 FROM OE_SHIP_TO_ORGS_V
1858 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1855 WHERE ADDRESS_LINE_1 = p_ship_to_address1
1856 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1857 nvl( p_ship_to_address2, fnd_api.g_miss_char)
1859 nvl( p_ship_to_address3, fnd_api.g_miss_char)
1860 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
1861 nvl( p_ship_to_address4, fnd_api.g_miss_char)
1862 AND nvl( town_or_city, fnd_api.g_miss_char) =
1863 nvl( p_ship_to_city, fnd_api.g_miss_char)
1864 AND nvl( state, fnd_api.g_miss_char) =
1865 nvl( p_ship_to_state, fnd_api.g_miss_char)
1866 AND nvl( postal_code, fnd_api.g_miss_char) =
1867 nvl( p_ship_to_postal_code, fnd_api.g_miss_char)
1868 AND nvl( country, fnd_api.g_miss_char) =
1869 nvl( p_ship_to_country, fnd_api.g_miss_char)
1870 AND STATUS = 'A'
1871 AND CUSTOMER_ID = p_sold_to_org_id;
1872
1873 CURSOR C1 IS
1874 SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
1875 FROM OE_SHIP_TO_ORGS_V
1876 WHERE ADDRESS_LINE_1 = p_ship_to_address1
1877 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1878 nvl( p_ship_to_address2, fnd_api.g_miss_char)
1879 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1880 nvl( p_ship_to_address3, fnd_api.g_miss_char)
1881 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
1882 nvl( p_ship_to_address4, fnd_api.g_miss_char)
1883 AND nvl( town_or_city, fnd_api.g_miss_char) =
1884 nvl( p_ship_to_city, fnd_api.g_miss_char)
1885 AND nvl( state, fnd_api.g_miss_char) =
1886 nvl( p_ship_to_state, fnd_api.g_miss_char)
1887 AND nvl( postal_code, fnd_api.g_miss_char) =
1888 nvl( p_ship_to_postal_code, fnd_api.g_miss_char)
1889 AND nvl( country, fnd_api.g_miss_char) =
1890 nvl( p_ship_to_country, fnd_api.g_miss_char)
1891 AND STATUS = 'A'
1892 AND CUSTOMER_ID in(
1893 SELECT p_sold_to_org_id FROM DUAL
1894 UNION
1895 SELECT CUST_ACCOUNT_ID FROM
1896 HZ_CUST_ACCT_RELATE WHERE
1897 RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
1898 and ship_to_flag = 'Y');
1899
1900 CURSOR C2 IS
1901 SELECT ORGANIZATION_ID
1902 FROM OE_SHIP_TO_ORGS_V
1903 WHERE ADDRESS_LINE_1 = p_ship_to_address1
1904 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1905 nvl( p_ship_to_address2, fnd_api.g_miss_char)
1906 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1907 nvl( p_ship_to_address3, fnd_api.g_miss_char)
1908 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
1909 nvl( p_ship_to_address4, fnd_api.g_miss_char)
1910 AND nvl( town_or_city, fnd_api.g_miss_char) =
1911 nvl( p_ship_to_city, fnd_api.g_miss_char)
1912 AND nvl( state, fnd_api.g_miss_char) =
1913 nvl( p_ship_to_state, fnd_api.g_miss_char)
1914 AND nvl( postal_code, fnd_api.g_miss_char) =
1915 nvl( p_ship_to_postal_code, fnd_api.g_miss_char)
1916 AND nvl( country, fnd_api.g_miss_char) =
1917 nvl( p_ship_to_country, fnd_api.g_miss_char)
1918 AND STATUS = 'A';
1919 BEGIN
1920
1921 IF p_ship_to_address1 IS NULL
1922 OR p_ship_to_address2 IS NULL
1923 OR p_ship_to_address3 IS NULL
1924 OR p_ship_to_address4 IS NULL
1925 OR p_ship_to_location IS NULL
1926 OR p_ship_to_org IS NULL
1927 THEN
1928 RETURN NULL;
1929 END IF;
1930
1931 lcustomer_relations := OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG');
1932
1933 IF lcustomer_relations = 'N' THEN
1934 OPEN c_ship_to_org_id;
1935 FETCH c_ship_to_org_id
1936 INTO l_id;
1937 IF c_ship_to_org_id%FOUND then
1938 CLOSE c_ship_to_org_id;
1939 return l_id;
1940 ELSE
1941
1942 SELECT ORGANIZATION_ID
1943 INTO l_id
1944 FROM OE_SHIP_TO_ORGS_V
1945 WHERE ADDRESS_LINE_1 = p_ship_to_address1
1946 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1947 nvl( p_ship_to_address2, fnd_api.g_miss_char)
1948 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1949 nvl( p_ship_to_address3, fnd_api.g_miss_char)
1950 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
1951 DECODE(STATE, NULL, NULL, STATE || ', ')||
1952 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
1953 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
1954 nvl( p_ship_to_address4, fnd_api.g_miss_char)
1955 AND STATUS = 'A'
1956 AND CUSTOMER_ID = p_sold_to_org_id;
1957 END IF;
1958 CLOSE c_ship_to_org_id;
1959
1960 RETURN l_id;
1961
1962 ELSIF lcustomer_relations = 'Y' THEN
1963 OPEN C1;
1964 FETCH C1
1965 INTO l_id;
1966 IF C1%FOUND then
1967 CLOSE C1;
1968 return l_id;
1969 ELSE
1970
1971 SELECT /*MOAC_SQL_NO_CHANGE*/ ORGANIZATION_ID
1972 INTO l_id
1973 FROM OE_SHIP_TO_ORGS_V
1974 WHERE ADDRESS_LINE_1 = p_ship_to_address1
1978 nvl( p_ship_to_address3, fnd_api.g_miss_char)
1975 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
1976 nvl( p_ship_to_address2, fnd_api.g_miss_char)
1977 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
1979 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
1980 DECODE(STATE, NULL, NULL, STATE || ', ')||
1981 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
1982 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
1983 nvl( p_ship_to_address4, fnd_api.g_miss_char)
1984 AND STATUS = 'A'
1985 AND CUSTOMER_ID
1986 in (
1987 SELECT p_sold_to_org_id FROM DUAL
1988 UNION
1989 SELECT CUST_ACCOUNT_ID FROM
1990 HZ_CUST_ACCT_RELATE WHERE
1991 RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
1992 and ship_to_flag = 'Y');
1993
1994 END IF;
1995 CLOSE C1;
1996
1997 RETURN l_id;
1998
1999 ELSIF lcustomer_relations = 'A' THEN
2000 OPEN C2;
2001 FETCH C2
2002 INTO l_id;
2003 IF C2%FOUND then
2004 CLOSE C2 ;
2005 return l_id;
2006 ELSE
2007
2008 SELECT ORGANIZATION_ID
2009 INTO l_id
2010 FROM OE_SHIP_TO_ORGS_V
2011 WHERE ADDRESS_LINE_1 = p_ship_to_address1
2012 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
2013 nvl( p_ship_to_address2, fnd_api.g_miss_char)
2014 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
2015 nvl( p_ship_to_address3, fnd_api.g_miss_char)
2016 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
2017 DECODE(STATE, NULL, NULL, STATE || ', ')||
2018 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
2019 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
2020 nvl( p_ship_to_address4, fnd_api.g_miss_char)
2021 AND STATUS = 'A';
2022 END IF;
2023 CLOSE C2;
2024
2025 RETURN l_id;
2026 END IF;
2027 EXCEPTION
2028
2029 WHEN NO_DATA_FOUND THEN
2030
2031 IF c_ship_to_org_id%ISOPEN then
2032 CLOSE c_ship_to_org_id;
2033 END IF;
2034 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2035 THEN
2036
2037 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2038 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_to_org_id');
2039 OE_MSG_PUB.Add;
2040
2041 END IF;
2042
2043 RETURN FND_API.G_MISS_NUM;
2044
2045 WHEN OTHERS THEN
2046
2047 IF c_ship_to_org_id%ISOPEN then
2048 CLOSE c_ship_to_org_id;
2049 END IF;
2050
2051 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2052 THEN
2053 OE_MSG_PUB.Add_Exc_Msg
2054 ( G_PKG_NAME
2055 , 'Ship_To_Org'
2056 );
2057 END IF;
2058
2059 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2060
2061 END Ship_To_Org;
2062
2063 -- Sold_To_Contact (Done aksingh)
2064
2065 FUNCTION Sold_To_Contact
2066 ( p_sold_to_contact IN VARCHAR2
2067 , p_sold_to_org_id IN NUMBER
2068 ) RETURN NUMBER
2069 IS
2070 l_id NUMBER;
2071 BEGIN
2072
2073 IF p_sold_to_contact IS NULL
2074 THEN
2075 RETURN NULL;
2076 END IF;
2077
2078
2079 SELECT /* MOAC_SQL_CHANGE */ CON.CONTACT_ID
2080 INTO l_id
2081 FROM OE_CONTACTS_V CON
2082 , HZ_CUST_ACCT_SITES ACCT_SITE
2083 , HZ_CUST_SITE_USES_ALL SITE
2084 WHERE CON.NAME = p_sold_to_contact
2085 AND CON.CUSTOMER_ID = ACCT_SITE.CUST_ACCOUNT_ID
2086 AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
2087 AND SITE.SITE_USE_ID = p_sold_to_org_id;
2088
2089 -- Modified the above query to fix the bug 1888440
2090
2091 RETURN l_id;
2092
2093 EXCEPTION
2094
2095 WHEN NO_DATA_FOUND THEN
2096
2097 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2098 THEN
2099
2100 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2101 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sold_to_contact_id');
2102 OE_MSG_PUB.Add;
2103
2104 END IF;
2105
2106 RETURN FND_API.G_MISS_NUM;
2107
2108 WHEN OTHERS THEN
2109
2110 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2111 THEN
2112 OE_MSG_PUB.Add_Exc_Msg
2113 ( G_PKG_NAME
2114 , 'Sold_To_Contact'
2115 );
2116 END IF;
2117
2118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2119
2120 END Sold_To_Contact;
2121
2122 -- Sold_To_Org (Done aksingh)
2123
2124 FUNCTION Sold_To_Org
2125 ( p_sold_to_org IN VARCHAR2
2126 , p_customer_number IN VARCHAR2
2127 ) RETURN NUMBER
2128 IS
2132 IF nvl(p_sold_to_org,fnd_api.g_miss_char) = fnd_api.g_miss_char
2129 l_id NUMBER;
2130 BEGIN
2131
2133 AND nvl(p_customer_number,fnd_api.g_miss_char) = fnd_api.g_miss_char
2134 THEN
2135 RETURN NULL;
2136 END IF;
2137
2138 IF nvl(p_customer_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
2139
2140 SELECT ORGANIZATION_ID
2141 INTO l_id
2142 FROM OE_SOLD_TO_ORGS_V
2143 WHERE CUSTOMER_NUMBER = p_customer_number;
2144
2145 ELSE
2146
2147 /* SELECT ORGANIZATION_ID
2148 INTO l_id
2149 FROM OE_SOLD_TO_ORGS_V
2150 WHERE NAME = p_sold_to_org;*/
2151
2152 Select Cust_Acct.Cust_account_id into l_id from HZ_CUST_ACCOUNTS Cust_Acct,
2153 HZ_PARTIES Party where Cust_Acct.Party_id = Party.party_id and
2154 Party.Party_name = p_sold_to_org;
2155
2156
2157 END IF;
2158
2159 RETURN l_id;
2160
2161 EXCEPTION
2162
2163 WHEN NO_DATA_FOUND THEN
2164
2165 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2166 THEN
2167
2168 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2169 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sold_to_org_id');
2170 OE_MSG_PUB.Add;
2171
2172 END IF;
2173
2174 RETURN FND_API.G_MISS_NUM;
2175
2176 WHEN OTHERS THEN
2177
2178 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2179 THEN
2180 OE_MSG_PUB.Add_Exc_Msg
2181 ( G_PKG_NAME
2182 , 'Sold_To_Org'
2183 );
2184 END IF;
2185
2186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2187
2188 END Sold_To_Org;
2189
2190 -- Tax_Exempt (Done aksingh)
2191
2192 FUNCTION Tax_Exempt
2193 ( p_tax_exempt IN VARCHAR2
2194 ) RETURN VARCHAR2
2195 IS
2196 l_code VARCHAR2(30);
2197 -- eBTax changes
2198 l_lookup_type VARCHAR2(80) :='ZX_EXEMPTION_CONTROL';
2199 BEGIN
2200
2201 IF p_tax_exempt IS NULL
2202 THEN
2203 RETURN NULL;
2204 END IF;
2205
2206 SELECT LOOKUP_CODE
2207 INTO l_code
2208 FROM fnd_lookups
2209 WHERE MEANING = p_tax_exempt
2210 AND LOOKUP_TYPE = l_lookup_type;
2211
2212 RETURN l_code;
2213
2214 EXCEPTION
2215
2216 WHEN NO_DATA_FOUND THEN
2217
2218 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2219 THEN
2220
2221 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2222 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','tax_exempt_flag');
2223 OE_MSG_PUB.Add;
2224
2225 END IF;
2226
2227 RETURN FND_API.G_MISS_CHAR;
2228
2229 WHEN OTHERS THEN
2230
2231 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2232 THEN
2233 OE_MSG_PUB.Add_Exc_Msg
2234 ( G_PKG_NAME
2235 , 'Tax_Exempt'
2236 );
2237 END IF;
2238
2239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2240
2241 END Tax_Exempt;
2242
2243 -- Tax_Exempt_Reason (Done aksingh)
2244
2245 FUNCTION Tax_Exempt_Reason
2246 ( p_tax_exempt_reason IN VARCHAR2
2247 ) RETURN VARCHAR2
2248 IS
2249 l_code VARCHAR2(30);
2250 -- eBTax changes
2251 l_lookup_type VARCHAR2(80) :='ZX_EXEMPTION_REASON_CODE';
2252 BEGIN
2253
2254 IF p_tax_exempt_reason IS NULL
2255 THEN
2256 RETURN NULL;
2257 END IF;
2258
2259 SELECT LOOKUP_CODE
2260 INTO l_code
2261 FROM fnd_lookups
2262 WHERE MEANING = p_tax_exempt_reason
2263 AND LOOKUP_TYPE = l_lookup_type;
2264
2265 RETURN l_code;
2266
2267 EXCEPTION
2268
2269 WHEN NO_DATA_FOUND THEN
2270
2271 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2272 THEN
2273
2274 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2275 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','tax_exempt_reason_code');
2276 OE_MSG_PUB.Add;
2277
2278 END IF;
2279
2280 RETURN FND_API.G_MISS_CHAR;
2281
2282 WHEN OTHERS THEN
2283
2284 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2285 THEN
2286 OE_MSG_PUB.Add_Exc_Msg
2287 ( G_PKG_NAME
2288 , 'Tax_Exempt_Reason'
2289 );
2290 END IF;
2291
2292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2293
2294 END Tax_Exempt_Reason;
2295
2296 -- Tax_Point (Done aksingh)
2297
2298 FUNCTION Tax_Point
2299 ( p_tax_point IN VARCHAR2
2300 ) RETURN VARCHAR2
2301 IS
2302 l_code VARCHAR2(30);
2306 IF p_tax_point IS NULL
2303 l_lookup_type VARCHAR2(80) :='TAX_POINT_TYPE';
2304 BEGIN
2305
2307 THEN
2308 RETURN NULL;
2309 END IF;
2310
2311 SELECT LOOKUP_CODE
2312 INTO l_code
2313 FROM OE_AR_LOOKUPS_V
2314 WHERE MEANING = p_tax_point
2315 AND LOOKUP_TYPE = l_lookup_type;
2316
2317 RETURN l_code;
2318
2319 EXCEPTION
2320
2321 WHEN NO_DATA_FOUND THEN
2322
2323 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2324 THEN
2325
2326 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2327 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','tax_point_code');
2328 OE_MSG_PUB.Add;
2329
2330 END IF;
2331
2332 RETURN FND_API.G_MISS_CHAR;
2333
2334 WHEN OTHERS THEN
2335
2336 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2337 THEN
2338 OE_MSG_PUB.Add_Exc_Msg
2339 ( G_PKG_NAME
2340 , 'Tax_Point'
2341 );
2342 END IF;
2343
2344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2345
2346 END Tax_Point;
2347
2348 -- Discount (Done aksingh)
2349
2350 FUNCTION Discount
2351 ( p_discount IN VARCHAR2
2352 ) RETURN NUMBER
2353 IS
2354 l_id NUMBER;
2355 BEGIN
2356
2357 IF p_discount IS NULL
2358 THEN
2359 RETURN NULL;
2360 END IF;
2361
2362 -- SELECT XXXX_id
2363 -- INTO l_id
2364 -- FROM XXXX_table
2365 -- WHERE XXXX_val_column = p_discount
2366
2367 RETURN l_id;
2368
2369 EXCEPTION
2370
2371 WHEN NO_DATA_FOUND THEN
2372
2373 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2374 THEN
2375
2376 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2377 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','discount_id');
2378 OE_MSG_PUB.Add;
2379
2380 END IF;
2381
2382 RETURN FND_API.G_MISS_NUM;
2383
2384 WHEN OTHERS THEN
2385
2386 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2387 THEN
2388 OE_MSG_PUB.Add_Exc_Msg
2389 ( G_PKG_NAME
2390 , 'Discount'
2391 );
2392 END IF;
2393
2394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2395
2396 END Discount;
2397
2398 -- Sales_Credit_type (Done aksingh)
2399 FUNCTION sales_credit_type
2400 ( p_sales_credit_type IN VARCHAR2
2401 ) RETURN NUMBER
2402 IS
2403 l_id NUMBER;
2404 BEGIN
2405
2406 IF p_sales_credit_type IS NULL
2407 THEN
2408 RETURN NULL;
2409 END IF;
2410
2411 SELECT sales_credit_type_id
2412 INTO l_id
2413 FROM oe_sales_credit_types
2414 WHERE Name = p_sales_credit_type;
2415
2416 RETURN l_id;
2417
2418 EXCEPTION
2419
2420 WHEN NO_DATA_FOUND THEN
2421
2422 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2423 THEN
2424
2425 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2426 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sales_credit_type');
2427 OE_MSG_PUB.Add;
2428
2429 END IF;
2430
2431 RETURN FND_API.G_MISS_NUM;
2432
2433 WHEN OTHERS THEN
2434
2435 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2436 THEN
2437 OE_MSG_PUB.Add_Exc_Msg
2438 ( G_PKG_NAME
2439 , 'sales_credit_type'
2440 );
2441 END IF;
2442
2443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2444
2445 END sales_credit_type;
2446
2447 -- Salesrep (Done aksingh)
2448
2449 FUNCTION Salesrep
2450 ( p_salesrep IN VARCHAR2
2451 ) RETURN NUMBER
2452 IS
2453 l_id NUMBER;
2454 BEGIN
2455
2456 IF p_salesrep IS NULL
2457 THEN
2458 RETURN NULL;
2459 END IF;
2460
2461 SELECT SALESREP_ID
2462 INTO l_id
2463 FROM RA_SALESREPS
2464 WHERE NAME = p_salesrep;
2465
2466 RETURN l_id;
2467
2468 EXCEPTION
2469
2470 WHEN NO_DATA_FOUND THEN
2471
2472 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2473 THEN
2474
2475 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2476 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','salesrep_id');
2477 OE_MSG_PUB.Add;
2478
2479 END IF;
2480
2481 RETURN FND_API.G_MISS_NUM;
2482
2483 WHEN OTHERS THEN
2484
2485 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2489 , 'Salesrep'
2486 THEN
2487 OE_MSG_PUB.Add_Exc_Msg
2488 ( G_PKG_NAME
2490 );
2491 END IF;
2492
2493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2494
2495 END Salesrep;
2496
2497 -- Demand_Bucket_Type (Done aksingh)
2498
2499 FUNCTION Demand_Bucket_Type
2500 ( p_demand_bucket_type IN VARCHAR2
2501 ) RETURN VARCHAR2
2502 IS
2503 l_code VARCHAR2(30);
2504 BEGIN
2505
2506 IF p_demand_bucket_type IS NULL
2507 THEN
2508 RETURN NULL;
2509 END IF;
2510
2511 -- SELECT XXXX_id
2512 -- INTO l_code
2513 -- FROM XXXX_table
2514 -- WHERE XXXX_val_column = p_demand_bucket_type
2515
2516 RETURN l_code;
2517
2518 EXCEPTION
2519
2520 WHEN NO_DATA_FOUND THEN
2521
2522 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2523 THEN
2524
2525 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2526 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','demand_bucket_type_code');
2527 OE_MSG_PUB.Add;
2528
2529 END IF;
2530
2531 RETURN FND_API.G_MISS_CHAR;
2532
2533 WHEN OTHERS THEN
2534
2535 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2536 THEN
2537 OE_MSG_PUB.Add_Exc_Msg
2538 ( G_PKG_NAME
2539 , 'Demand_Bucket_Type'
2540 );
2541 END IF;
2542
2543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2544
2545 END Demand_Bucket_Type;
2546
2547 -- Inventory_Item
2548
2549 FUNCTION Inventory_Item
2550 ( p_inventory_item IN VARCHAR2
2551 ) RETURN NUMBER
2552 IS
2553 l_id NUMBER;
2554 BEGIN
2555
2556 IF p_inventory_item IS NULL
2557 THEN
2558 RETURN NULL;
2559 END IF;
2560
2561 --bug5603389
2562 SELECT inventory_item_id
2563 INTO l_id
2564 FROM mtl_system_items_vl
2565 WHERE concatenated_segments =p_inventory_item
2566 AND customer_order_enabled_flag = 'Y'
2567 AND bom_item_type in (1,2,4)
2568 AND organization_id = OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID');
2569
2570 RETURN l_id;
2571
2572 EXCEPTION
2573
2574 WHEN NO_DATA_FOUND THEN
2575
2576 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2577 THEN
2578
2579 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2580 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','inventory_item_id');
2581 OE_MSG_PUB.Add;
2582
2583 END IF;
2584
2585 RETURN FND_API.G_MISS_NUM;
2586
2587 WHEN OTHERS THEN
2588
2589 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2590 THEN
2591 OE_MSG_PUB.Add_Exc_Msg
2592 ( G_PKG_NAME
2593 , 'Inventory_Item'
2594 );
2595 END IF;
2596
2597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2598
2599 END Inventory_Item;
2600
2601 -- Item_Type (Done aksingh)
2602
2603 FUNCTION Item_Type
2604 ( p_item_type IN VARCHAR2
2605 ) RETURN VARCHAR2
2606 IS
2607 l_code VARCHAR2(30);
2608 BEGIN
2609
2610 IF p_item_type IS NULL
2611 THEN
2612 RETURN NULL;
2613 END IF;
2614
2615 -- SELECT XXXX_id
2616 -- INTO l_code
2617 -- FROM XXXX_table
2618 -- WHERE XXXX_val_column = p_item_type
2619
2620 RETURN l_code;
2621
2622 EXCEPTION
2623
2624 WHEN NO_DATA_FOUND THEN
2625
2626 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2627 THEN
2628
2629 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2630 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','item_type_code');
2631 OE_MSG_PUB.Add;
2632
2633 END IF;
2634
2635 RETURN FND_API.G_MISS_CHAR;
2636
2637 WHEN OTHERS THEN
2638
2639 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2640 THEN
2641 OE_MSG_PUB.Add_Exc_Msg
2642 ( G_PKG_NAME
2643 , 'Item_Type'
2644 );
2645 END IF;
2646
2647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2648
2649 END Item_Type;
2650
2651 -- Line_Type (Done aksingh)
2652
2653 FUNCTION Line_Type
2654 ( p_line_type IN VARCHAR2
2655 ) RETURN NUMBER
2656 IS
2657 l_id NUMBER;
2658 BEGIN
2659
2660 IF p_line_type IS NULL
2661 THEN
2662 RETURN NULL;
2663 END IF;
2664
2665 SELECT LINE_TYPE_ID
2666 INTO l_id
2667 FROM OE_LINE_TYPES_V
2671
2668 WHERE NAME = p_line_type;
2669
2670 RETURN l_id;
2672 EXCEPTION
2673
2674 WHEN NO_DATA_FOUND THEN
2675
2676 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2677 THEN
2678
2679 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2680 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','line_type_id');
2681 OE_MSG_PUB.Add;
2682
2683 END IF;
2684
2685 RETURN FND_API.G_MISS_NUM;
2686
2687 WHEN OTHERS THEN
2688
2689 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2690 THEN
2691 OE_MSG_PUB.Add_Exc_Msg
2692 ( G_PKG_NAME
2693 , 'Line_Type'
2694 );
2695 END IF;
2696
2697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2698
2699 END Line_Type;
2700
2701 -- Project (Done aksingh -- no code)
2702
2703 FUNCTION Project
2704 ( p_project IN VARCHAR2
2705 ) RETURN NUMBER
2706 IS
2707 l_id NUMBER;
2708 BEGIN
2709
2710 IF p_project IS NULL
2711 THEN
2712 RETURN NULL;
2713 END IF;
2714
2715 -- SELECT XXXX_id
2716 -- INTO l_id
2717 -- FROM XXXX_table
2718 -- WHERE XXXX_val_column = p_project
2719
2720 RETURN l_id;
2721
2722 EXCEPTION
2723
2724 WHEN NO_DATA_FOUND THEN
2725
2726 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2727 THEN
2728
2729 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2730 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','project_id');
2731 OE_MSG_PUB.Add;
2732
2733 END IF;
2734
2735 RETURN FND_API.G_MISS_NUM;
2736
2737 WHEN OTHERS THEN
2738
2739 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2740 THEN
2741 OE_MSG_PUB.Add_Exc_Msg
2742 ( G_PKG_NAME
2743 , 'Project'
2744 );
2745 END IF;
2746
2747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2748
2749 END Project;
2750
2751 -- Rla_Schedule_Type (Done aksingh)
2752
2753 FUNCTION Rla_Schedule_Type
2754 ( p_rla_schedule_type IN VARCHAR2
2755 ) RETURN VARCHAR2
2756 IS
2757 l_code VARCHAR2(30);
2758 BEGIN
2759
2760 IF p_rla_schedule_type IS NULL
2761 THEN
2762 RETURN NULL;
2763 END IF;
2764
2765 -- SELECT XXXX_id
2766 -- INTO l_code
2767 -- FROM XXXX_table
2768 -- WHERE XXXX_val_column = p_rla_schedule_type
2769
2770 RETURN l_code;
2771
2772 EXCEPTION
2773
2774 WHEN NO_DATA_FOUND THEN
2775
2776 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2777 THEN
2778
2779 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2780 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','rla_schedule_type_code');
2781 OE_MSG_PUB.Add;
2782
2783 END IF;
2784
2785 RETURN FND_API.G_MISS_CHAR;
2786
2787 WHEN OTHERS THEN
2788
2789 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2790 THEN
2791 OE_MSG_PUB.Add_Exc_Msg
2792 ( G_PKG_NAME
2793 , 'Rla_Schedule_Type'
2794 );
2795 END IF;
2796
2797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2798
2799 END Rla_Schedule_Type;
2800
2801 -- Task (Done aksingh -- no code)
2802
2803 FUNCTION Task
2804 ( p_task IN VARCHAR2
2805 ) RETURN NUMBER
2806 IS
2807 l_id NUMBER;
2808 BEGIN
2809
2810 IF p_task IS NULL
2811 THEN
2812 RETURN NULL;
2813 END IF;
2814
2815 -- SELECT XXXX_id
2816 -- INTO l_id
2817 -- FROM XXXX_table
2818 -- WHERE XXXX_val_column = p_task
2819
2820 RETURN l_id;
2821
2822 EXCEPTION
2823
2824 WHEN NO_DATA_FOUND THEN
2825
2826 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2827 THEN
2828
2829 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2830 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','task_id');
2831 OE_MSG_PUB.Add;
2832
2833 END IF;
2834
2835 RETURN FND_API.G_MISS_NUM;
2836
2837 WHEN OTHERS THEN
2838
2839 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2840 THEN
2841 OE_MSG_PUB.Add_Exc_Msg
2842 ( G_PKG_NAME
2843 , 'Task'
2844 );
2845 END IF;
2846
2847 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2848
2849 END Task;
2850
2854 ) RETURN VARCHAR2
2851 -- Over_Ship_Reason (Done aksingh)
2852 FUNCTION Over_Ship_Reason
2853 ( p_over_ship_reason IN VARCHAR2
2855 IS
2856 l_code VARCHAR2(15);
2857 BEGIN
2858
2859 IF p_over_ship_reason IS NULL
2860 THEN
2861 RETURN NULL;
2862 END IF;
2863
2864 -- SELECT XXXX_id
2865 -- INTO l_code
2866 -- FROM XXXX_table
2867 -- WHERE XXXX_val_column = p_currency
2868
2869 RETURN l_code;
2870
2871 EXCEPTION
2872
2873 WHEN NO_DATA_FOUND THEN
2874
2875 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2876 THEN
2877
2878 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2879 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','over_ship_reason_code');
2880 OE_MSG_PUB.Add;
2881
2882 END IF;
2883
2884 RETURN FND_API.G_MISS_CHAR;
2885
2886 WHEN OTHERS THEN
2887
2888 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2889 THEN
2890 OE_MSG_PUB.Add_Exc_Msg
2891 ( G_PKG_NAME
2892 , 'Over_Ship_reason'
2893 );
2894 END IF;
2895
2896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2897
2898 END Over_Ship_reason;
2899
2900 -- (Done aksingh)
2901 FUNCTION Return_Reason
2902 ( p_return_reason IN VARCHAR2
2903 ) RETURN VARCHAR2
2904 IS
2905 l_code VARCHAR2(15);
2906 l_lookup_type VARCHAR2(80) :='CREDIT_MEMO_REASON';
2907 BEGIN
2908
2909 IF p_return_reason IS NULL
2910 THEN
2911 RETURN NULL;
2912 END IF;
2913
2914 SELECT LOOKUP_CODE
2915 INTO l_code
2916 FROM OE_AR_LOOKUPS_V
2917 WHERE MEANING = p_return_reason
2918 AND LOOKUP_TYPE = l_lookup_type;
2919
2920 RETURN l_code;
2921
2922 EXCEPTION
2923
2924 WHEN NO_DATA_FOUND THEN
2925
2926 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2927 THEN
2928
2929 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2930 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','return_reason_code');
2931 OE_MSG_PUB.Add;
2932
2933 END IF;
2934
2935 RETURN FND_API.G_MISS_CHAR;
2936
2937 WHEN OTHERS THEN
2938
2939 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2940 THEN
2941 OE_MSG_PUB.Add_Exc_Msg
2942 ( G_PKG_NAME
2943 , 'Return_Reason'
2944 );
2945 END IF;
2946
2947 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2948
2949 END Return_Reason;
2950
2951 -- (Done aksingh)
2952 FUNCTION Veh_Cus_Item_Cum_Key
2953 ( p_veh_cus_item_cum_Key IN VARCHAR2
2954 ) RETURN NUMBER
2955 IS
2956 l_id NUMBER;
2957 BEGIN
2958
2959 IF p_veh_cus_item_cum_Key IS NULL
2960 THEN
2961 RETURN NULL;
2962 END IF;
2963
2964 -- SELECT XXXX_id
2965 -- INTO l_code
2966 -- FROM XXXX_table
2967 -- WHERE XXXX_val_column = p_currency
2968
2969 RETURN l_id;
2970
2971 EXCEPTION
2972
2973 WHEN NO_DATA_FOUND THEN
2974
2975 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2976 THEN
2977
2978 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
2979 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','veh_cus_item_cum_Key_id');
2980 OE_MSG_PUB.Add;
2981
2982 END IF;
2983
2984 RETURN FND_API.G_MISS_NUM;
2985
2986 WHEN OTHERS THEN
2987
2988 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2989 THEN
2990 OE_MSG_PUB.Add_Exc_Msg
2991 ( G_PKG_NAME
2992 , 'Veh_Cus_Item_cum_Key'
2993 );
2994 END IF;
2995
2996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2997
2998 END Veh_Cus_Item_cum_Key;
2999
3000 -- Payment_Type (Done aksingh)
3001
3002 FUNCTION Payment_Type
3003 ( p_payment_type IN VARCHAR2
3004 ) RETURN VARCHAR2
3005 IS
3006 l_code VARCHAR2(30);
3007 l_lookup_type VARCHAR2(80) :='PAYMENT TYPE';
3008 BEGIN
3009
3010 IF p_payment_type IS NULL
3011 THEN
3012 RETURN NULL;
3013 END IF;
3014
3015 SELECT LOOKUP_CODE
3016 INTO l_code
3017 FROM OE_LOOKUPS
3018 WHERE MEANING = p_payment_type
3019 AND LOOKUP_TYPE = l_lookup_type;
3020
3021 RETURN l_code;
3022
3023 EXCEPTION
3024
3025 WHEN NO_DATA_FOUND THEN
3026
3027 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3028 THEN
3032 OE_MSG_PUB.Add;
3029
3030 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3031 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','payment_type_code');
3033
3034 END IF;
3035
3036 RETURN FND_API.G_MISS_CHAR;
3037
3038 WHEN OTHERS THEN
3039
3040 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3041 THEN
3042 OE_MSG_PUB.Add_Exc_Msg
3043 ( G_PKG_NAME
3044 , 'Payment_Type'
3045 );
3046 END IF;
3047
3048 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3049
3050 END Payment_Type;
3051
3052 -- Credit_Card (Done aksingh)
3053
3054 FUNCTION Credit_Card
3055 ( p_credit_card IN VARCHAR2
3056 ) RETURN VARCHAR2
3057 IS
3058 l_code VARCHAR2(30);
3059 l_lookup_type VARCHAR2(80) :='CREDIT_CARD';
3060 BEGIN
3061
3062 IF p_credit_card IS NULL
3063 THEN
3064 RETURN NULL;
3065 END IF;
3066
3067 SELECT LOOKUP_CODE
3068 INTO l_code
3069 FROM OE_LOOKUPS
3070 WHERE MEANING = p_credit_card
3071 AND LOOKUP_TYPE = l_lookup_type;
3072
3073 RETURN l_code;
3074
3075 EXCEPTION
3076
3077 WHEN NO_DATA_FOUND THEN
3078
3079 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3080 THEN
3081
3082 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3083 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','credit_card_code');
3084 OE_MSG_PUB.Add;
3085
3086 END IF;
3087
3088 RETURN FND_API.G_MISS_CHAR;
3089
3090 WHEN OTHERS THEN
3091
3092 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3093 THEN
3094 OE_MSG_PUB.Add_Exc_Msg
3095 ( G_PKG_NAME
3096 , 'Credit_Card'
3097 );
3098 END IF;
3099
3100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3101
3102 END Credit_Card;
3103
3104
3105 FUNCTION Commitment
3106 ( p_commitment IN VARCHAR2
3107 ) RETURN NUMBER
3108 IS
3109 l_code VARCHAR2(30);
3110 BEGIN
3111
3112 RETURN NULL;
3113
3114 END Commitment;
3115
3116
3117 /* Pricing Contract Functions : Begin */
3118
3119 FUNCTION Currency
3120 ( p_currency IN VARCHAR2
3121 ) RETURN VARCHAR2
3122 IS
3123 l_code VARCHAR2(15);
3124 BEGIN
3125
3126 IF p_currency IS NULL
3127 THEN
3128 RETURN NULL;
3129 END IF;
3130
3131 -- SELECT XXXX_id
3132 -- INTO l_code
3133 -- FROM XXXX_table
3134 -- WHERE XXXX_val_column = p_currency
3135
3136 RETURN l_code;
3137
3138 EXCEPTION
3139
3140 WHEN NO_DATA_FOUND THEN
3141
3142 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3143 THEN
3144
3145 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3146 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','currency_code');
3147 OE_MSG_PUB.Add;
3148
3149 END IF;
3150
3151 RETURN FND_API.G_MISS_CHAR;
3152
3153 WHEN OTHERS THEN
3154
3155 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3156 THEN
3157 OE_MSG_PUB.Add_Exc_Msg
3158 ( G_PKG_NAME
3159 , 'Currency'
3160 );
3161 END IF;
3162
3163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3164
3165 END Currency;
3166
3167
3168 -- Agreement_Contact
3169
3170 FUNCTION Agreement_Contact
3171 ( p_Agreement_Contact IN VARCHAR2
3172 ) RETURN VARCHAR2
3173 IS
3174 l_code VARCHAR2(15);
3175 BEGIN
3176
3177 IF p_Agreement_Contact IS NULL
3178 THEN
3179 RETURN NULL;
3180 END IF;
3181
3182 -- SELECT XXXX_id
3183 -- INTO l_code
3184 -- FROM XXXX_table
3185 -- WHERE XXXX_val_column = p_Agreement_Contact
3186
3187 RETURN l_code;
3188
3189 EXCEPTION
3190
3191 WHEN NO_DATA_FOUND THEN
3192
3193 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3194 THEN
3195
3196 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3197 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Agreement_Contact_Id');
3198 OE_MSG_PUB.Add;
3199
3200 END IF;
3201
3202 RETURN FND_API.G_MISS_CHAR;
3203
3204 WHEN OTHERS THEN
3205
3206 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3207 THEN
3208 OE_MSG_PUB.Add_Exc_Msg
3209 ( G_PKG_NAME
3210 , 'Agreement_Contact'
3214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3211 );
3212 END IF;
3213
3215
3216 END Agreement_Contact;
3217
3218 -- Agreement_Type
3219
3220 FUNCTION Agreement_Type
3221 ( p_Agreement_Type IN VARCHAR2
3222 ) RETURN VARCHAR2
3223 IS
3224 l_code VARCHAR2(15);
3225 BEGIN
3226
3227 IF p_Agreement_Type IS NULL
3228 THEN
3229 RETURN NULL;
3230 END IF;
3231
3232 -- SELECT XXXX_id
3233 -- INTO l_code
3234 -- FROM XXXX_table
3235 -- WHERE XXXX_val_column = p_Agreement_Type
3236
3237 RETURN l_code;
3238
3239 EXCEPTION
3240
3241 WHEN NO_DATA_FOUND THEN
3242
3243 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3244 THEN
3245
3246 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3247 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Agreement_Type_Code');
3248 OE_MSG_PUB.Add;
3249
3250 END IF;
3251
3252 RETURN FND_API.G_MISS_CHAR;
3253
3254 WHEN OTHERS THEN
3255
3256 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3257 THEN
3258 OE_MSG_PUB.Add_Exc_Msg
3259 ( G_PKG_NAME
3260 , 'Agreement_Type'
3261 );
3262 END IF;
3263
3264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3265
3266 END Agreement_Type;
3267
3268 -- Customer
3269
3270 FUNCTION Customer
3271 ( p_Customer IN VARCHAR2
3272 ) RETURN VARCHAR2
3273 IS
3274 l_code VARCHAR2(15);
3275 BEGIN
3276
3277 IF p_Customer IS NULL
3278 THEN
3279 RETURN NULL;
3280 END IF;
3281
3282 -- SELECT XXXX_id
3283 -- INTO l_code
3284 -- FROM XXXX_table
3285 -- WHERE XXXX_val_column = p_Customer
3286
3287 RETURN l_code;
3288
3289 EXCEPTION
3290
3291 WHEN NO_DATA_FOUND THEN
3292
3293 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3294 THEN
3295
3296 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3297 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Customer_Id');
3298 OE_MSG_PUB.Add;
3299
3300 END IF;
3301
3302 RETURN FND_API.G_MISS_CHAR;
3303
3304 WHEN OTHERS THEN
3305
3306 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3307 THEN
3308 OE_MSG_PUB.Add_Exc_Msg
3309 ( G_PKG_NAME
3310 , 'Customer'
3311 );
3312 END IF;
3313
3314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3315
3316 END Customer;
3317
3318 -- Invoice_Contact
3319
3320 FUNCTION Invoice_Contact
3321 ( p_Invoice_Contact IN VARCHAR2
3322 ) RETURN VARCHAR2
3323 IS
3324 l_code VARCHAR2(15);
3325 BEGIN
3326
3327 IF p_Invoice_Contact IS NULL
3328 THEN
3329 RETURN NULL;
3330 END IF;
3331
3332 -- SELECT XXXX_id
3333 -- INTO l_code
3334 -- FROM XXXX_table
3335 -- WHERE XXXX_val_column = p_Invoice_Contact
3336
3337 RETURN l_code;
3338
3339 EXCEPTION
3340
3341 WHEN NO_DATA_FOUND THEN
3342
3343 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3344 THEN
3345
3346 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3347 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Invoice_Contact_Id');
3348 OE_MSG_PUB.Add;
3349
3350 END IF;
3351
3352 RETURN FND_API.G_MISS_CHAR;
3353
3354 WHEN OTHERS THEN
3355
3356 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3357 THEN
3358 OE_MSG_PUB.Add_Exc_Msg
3359 ( G_PKG_NAME
3360 , 'Invoice_Contact'
3361 );
3362 END IF;
3363
3364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3365
3366 END Invoice_Contact;
3367
3368 -- Invoice_To_Site_Use
3369
3370 FUNCTION Invoice_To_Site_Use
3371 ( p_Invoice_To_Site_Use IN VARCHAR2
3372 ) RETURN VARCHAR2
3373 IS
3374 l_code VARCHAR2(15);
3375 BEGIN
3376
3377 IF p_Invoice_To_Site_Use IS NULL
3378 THEN
3379 RETURN NULL;
3380 END IF;
3381
3382 -- SELECT XXXX_id
3383 -- INTO l_code
3384 -- FROM XXXX_table
3385 -- WHERE XXXX_val_column = p_Invoice_To_Site_Use
3386
3387 RETURN l_code;
3388
3389 EXCEPTION
3390
3391 WHEN NO_DATA_FOUND THEN
3392
3393 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3394 THEN
3395
3399
3396 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3397 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Invoice_To_Site_Use_Id');
3398 OE_MSG_PUB.Add;
3400 END IF;
3401
3402 RETURN FND_API.G_MISS_CHAR;
3403
3404 WHEN OTHERS THEN
3405
3406 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3407 THEN
3408 OE_MSG_PUB.Add_Exc_Msg
3409 ( G_PKG_NAME
3410 , 'Invoice_To_Site_Use'
3411 );
3412 END IF;
3413
3414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3415
3416 END Invoice_To_Site_Use;
3417
3418 -- Override_Arule
3419
3420 FUNCTION Override_Arule
3421 ( p_Override_Arule IN VARCHAR2
3422 ) RETURN VARCHAR2
3423 IS
3424 l_code VARCHAR2(15);
3425 BEGIN
3426
3427 IF p_Override_Arule IS NULL
3428 THEN
3429 RETURN NULL;
3430 END IF;
3431
3432 -- SELECT XXXX_id
3433 -- INTO l_code
3434 -- FROM XXXX_table
3435 -- WHERE XXXX_val_column = p_Override_Arule
3436
3437 RETURN l_code;
3438
3439 EXCEPTION
3440
3441 WHEN NO_DATA_FOUND THEN
3442
3443 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3444 THEN
3445
3446 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3447 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Override_Arule_Flag');
3448 OE_MSG_PUB.Add;
3449
3450 END IF;
3451
3452 RETURN FND_API.G_MISS_CHAR;
3453
3454 WHEN OTHERS THEN
3455
3456 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3457 THEN
3458 OE_MSG_PUB.Add_Exc_Msg
3459 ( G_PKG_NAME
3460 , 'Override_Arule'
3461 );
3462 END IF;
3463
3464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3465
3466 END Override_Arule;
3467
3468 -- Override_Irule
3469
3470 FUNCTION Override_Irule
3471 ( p_Override_Irule IN VARCHAR2
3472 ) RETURN VARCHAR2
3473 IS
3474 l_code VARCHAR2(15);
3475 BEGIN
3476
3477 IF p_Override_Irule IS NULL
3478 THEN
3479 RETURN NULL;
3480 END IF;
3481
3482 -- SELECT XXXX_id
3483 -- INTO l_code
3484 -- FROM XXXX_table
3485 -- WHERE XXXX_val_column = p_Override_Irule
3486
3487 RETURN l_code;
3488
3489 EXCEPTION
3490
3491 WHEN NO_DATA_FOUND THEN
3492
3493 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3494 THEN
3495
3496 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3497 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Override_Irule_Flag');
3498 OE_MSG_PUB.Add;
3499
3500 END IF;
3501
3502 RETURN FND_API.G_MISS_CHAR;
3503
3504 WHEN OTHERS THEN
3505
3506 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3507 THEN
3508 OE_MSG_PUB.Add_Exc_Msg
3509 ( G_PKG_NAME
3510 , 'Override_Irule'
3511 );
3512 END IF;
3513
3514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3515
3516 END Override_Irule;
3517
3518 -- Revision_Reason
3519
3520 FUNCTION Revision_Reason
3521 ( p_Revision_Reason IN VARCHAR2
3522 ) RETURN VARCHAR2
3523 IS
3524 l_code VARCHAR2(15);
3525 BEGIN
3526
3527 IF p_Revision_Reason IS NULL
3528 THEN
3529 RETURN NULL;
3530 END IF;
3531
3532 -- SELECT XXXX_id
3533 -- INTO l_code
3534 -- FROM XXXX_table
3535 -- WHERE XXXX_val_column = p_Revision_Reason
3536
3537 RETURN l_code;
3538
3539 EXCEPTION
3540
3541 WHEN NO_DATA_FOUND THEN
3542
3543 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3544 THEN
3545
3546 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3547 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Revision_Reason_Code');
3548 OE_MSG_PUB.Add;
3549
3550 END IF;
3551
3552 RETURN FND_API.G_MISS_CHAR;
3553
3554 WHEN OTHERS THEN
3555
3556 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3557 THEN
3558 OE_MSG_PUB.Add_Exc_Msg
3559 ( G_PKG_NAME
3560 , 'Revision_Reason'
3561 );
3562 END IF;
3563
3564 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3565
3566 END Revision_Reason;
3567
3568 -- Ship_Method (Done aksingh)
3569
3570 FUNCTION Ship_Method
3571 ( p_Ship_Method IN VARCHAR2
3572 ) RETURN VARCHAR2
3573 IS
3574 l_code VARCHAR2(80);
3575 BEGIN
3576
3577 IF p_Ship_Method IS NULL
3581
3578 THEN
3579 RETURN NULL;
3580 END IF;
3582 SELECT lookup_code
3583 INTO l_code
3584 FROM oe_ship_methods_v
3585 WHERE meaning = p_Ship_Method and
3586 rownum = 1 ;
3587
3588 RETURN l_code;
3589
3590 EXCEPTION
3591
3592 WHEN NO_DATA_FOUND THEN
3593
3594 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3595 THEN
3596
3597 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3598 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Ship_Method_Code');
3599 OE_MSG_PUB.Add;
3600
3601 END IF;
3602
3603 RETURN FND_API.G_MISS_CHAR;
3604
3605 WHEN OTHERS THEN
3606
3607 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3608 THEN
3609 OE_MSG_PUB.Add_Exc_Msg
3610 ( G_PKG_NAME
3611 , 'Ship_Method'
3612 );
3613 END IF;
3614
3615 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3616
3617 END Ship_Method;
3618
3619 -- Term
3620
3621 FUNCTION Term
3622 ( p_Term IN VARCHAR2
3623 ) RETURN VARCHAR2
3624 IS
3625 l_code VARCHAR2(15);
3626 BEGIN
3627
3628 IF p_Term IS NULL
3629 THEN
3630 RETURN NULL;
3631 END IF;
3632
3633 -- SELECT XXXX_id
3634 -- INTO l_code
3635 -- FROM XXXX_table
3636 -- WHERE XXXX_val_column = p_Term
3637
3638 RETURN l_code;
3639
3640 EXCEPTION
3641
3642 WHEN NO_DATA_FOUND THEN
3643
3644 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3645 THEN
3646
3647 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3648 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Term_Id');
3649 OE_MSG_PUB.Add;
3650
3651 END IF;
3652
3653 RETURN FND_API.G_MISS_CHAR;
3654
3655 WHEN OTHERS THEN
3656
3657 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3658 THEN
3659 OE_MSG_PUB.Add_Exc_Msg
3660 ( G_PKG_NAME
3661 , 'Term'
3662 );
3663 END IF;
3664
3665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3666
3667 END Term;
3668
3669 -- Secondary_Price_List
3670
3671 FUNCTION Secondary_Price_List
3672 ( p_secondary_price_list IN VARCHAR2
3673 ) RETURN NUMBER
3674 IS
3675 l_id NUMBER;
3676 BEGIN
3677
3678 IF p_secondary_price_list IS NULL
3679 THEN
3680 RETURN NULL;
3681 END IF;
3682
3683 -- SELECT XXXX_id
3684 -- INTO l_id
3685 -- FROM XXXX_table
3686 -- WHERE XXXX_val_column = p_secondary_price_list
3687
3688 RETURN l_id;
3689
3690 EXCEPTION
3691
3692 WHEN NO_DATA_FOUND THEN
3693
3694 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3695 THEN
3696
3697 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3698 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','secondary_price_list_id');
3699 OE_MSG_PUB.Add;
3700
3701 END IF;
3702
3703 RETURN FND_API.G_MISS_NUM;
3704
3705 WHEN OTHERS THEN
3706
3707 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3708 THEN
3709 OE_MSG_PUB.Add_Exc_Msg
3710 ( G_PKG_NAME
3711 , 'Secondary_Price_List'
3712 );
3713 END IF;
3714
3715 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3716
3717 END Secondary_Price_List;
3718
3719 -- Terms
3720
3721 FUNCTION Terms
3722 ( p_terms IN VARCHAR2
3723 ) RETURN NUMBER
3724 IS
3725 l_id NUMBER;
3726 BEGIN
3727
3728 IF p_terms IS NULL
3729 THEN
3730 RETURN NULL;
3731 END IF;
3732
3733 -- SELECT XXXX_id
3734 -- INTO l_id
3735 -- FROM XXXX_table
3736 -- WHERE XXXX_val_column = p_terms
3737
3738 RETURN l_id;
3739
3740 EXCEPTION
3741
3742 WHEN NO_DATA_FOUND THEN
3743
3744 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3745 THEN
3746
3747 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3748 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','terms_id');
3749 OE_MSG_PUB.Add;
3750
3751 END IF;
3752
3753 RETURN FND_API.G_MISS_NUM;
3754
3755 WHEN OTHERS THEN
3756
3757 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3758 THEN
3759 OE_MSG_PUB.Add_Exc_Msg
3760 ( G_PKG_NAME
3761 , 'Terms'
3762 );
3763 END IF;
3764
3765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3766
3767 END Terms;
3768
3769 -- Automatic_Discount
3770
3771 FUNCTION Automatic_Discount
3775 l_code VARCHAR2(1);
3772 ( p_automatic_discount IN VARCHAR2
3773 ) RETURN VARCHAR2
3774 IS
3776 BEGIN
3777
3778 IF p_automatic_discount IS NULL
3779 THEN
3780 RETURN NULL;
3781 END IF;
3782
3783 -- SELECT XXXX_id
3784 -- INTO l_code
3785 -- FROM XXXX_table
3786 -- WHERE XXXX_val_column = p_automatic_discount
3787
3788 RETURN l_code;
3789
3790 EXCEPTION
3791
3792 WHEN NO_DATA_FOUND THEN
3793
3794 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3795 THEN
3796
3797 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3798 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','automatic_discount_flag');
3799 OE_MSG_PUB.Add;
3800
3801 END IF;
3802
3803 RETURN FND_API.G_MISS_CHAR;
3804
3805 WHEN OTHERS THEN
3806
3807 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3808 THEN
3809 OE_MSG_PUB.Add_Exc_Msg
3810 ( G_PKG_NAME
3811 , 'Automatic_Discount'
3812 );
3813 END IF;
3814
3815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3816
3817 END Automatic_Discount;
3818
3819 -- Discount_Lines
3820
3821 FUNCTION Discount_Lines
3822 ( p_discount_lines IN VARCHAR2
3823 ) RETURN VARCHAR2
3824 IS
3825 l_code VARCHAR2(1);
3826 BEGIN
3827
3828 IF p_discount_lines IS NULL
3829 THEN
3830 RETURN NULL;
3831 END IF;
3832
3833 -- SELECT XXXX_id
3834 -- INTO l_code
3835 -- FROM XXXX_table
3836 -- WHERE XXXX_val_column = p_discount_lines
3837
3838 RETURN l_code;
3839
3840 EXCEPTION
3841
3842 WHEN NO_DATA_FOUND THEN
3843
3844 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3845 THEN
3846
3847 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3848 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','discount_lines_flag');
3849 OE_MSG_PUB.Add;
3850
3851 END IF;
3852
3853 RETURN FND_API.G_MISS_CHAR;
3854
3855 WHEN OTHERS THEN
3856
3857 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3858 THEN
3859 OE_MSG_PUB.Add_Exc_Msg
3860 ( G_PKG_NAME
3861 , 'Discount_Lines'
3862 );
3863 END IF;
3864
3865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3866
3867 END Discount_Lines;
3868
3869 -- Discount_Type
3870
3871 FUNCTION Discount_Type
3872 ( p_discount_type IN VARCHAR2
3873 ) RETURN VARCHAR2
3874 IS
3875 l_code VARCHAR2(30);
3876 BEGIN
3877
3878 IF p_discount_type IS NULL
3879 THEN
3880 RETURN NULL;
3881 END IF;
3882
3883 -- SELECT XXXX_id
3884 -- INTO l_code
3885 -- FROM XXXX_table
3886 -- WHERE XXXX_val_column = p_discount_type
3887
3888 RETURN l_code;
3889
3890 EXCEPTION
3891
3892 WHEN NO_DATA_FOUND THEN
3893
3894 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3895 THEN
3896
3897 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3898 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','discount_type_code');
3899 OE_MSG_PUB.Add;
3900
3901 END IF;
3902
3903 RETURN FND_API.G_MISS_CHAR;
3904
3905 WHEN OTHERS THEN
3906
3907 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3908 THEN
3909 OE_MSG_PUB.Add_Exc_Msg
3910 ( G_PKG_NAME
3911 , 'Discount_Type'
3912 );
3913 END IF;
3914
3915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3916
3917 END Discount_Type;
3918
3919 -- Manual_Discount
3920
3921 FUNCTION Manual_Discount
3922 ( p_manual_discount IN VARCHAR2
3923 ) RETURN VARCHAR2
3924 IS
3925 l_code VARCHAR2(1);
3926 BEGIN
3927
3928 IF p_manual_discount IS NULL
3929 THEN
3930 RETURN NULL;
3931 END IF;
3932
3933 -- SELECT XXXX_id
3934 -- INTO l_code
3935 -- FROM XXXX_table
3936 -- WHERE XXXX_val_column = p_manual_discount
3937
3938 RETURN l_code;
3939
3940 EXCEPTION
3941
3942 WHEN NO_DATA_FOUND THEN
3943
3944 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3945 THEN
3946
3947 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3948 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','manual_discount_flag');
3949 OE_MSG_PUB.Add;
3950
3951 END IF;
3952
3953 RETURN FND_API.G_MISS_CHAR;
3954
3955 WHEN OTHERS THEN
3956
3960 ( G_PKG_NAME
3957 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3958 THEN
3959 OE_MSG_PUB.Add_Exc_Msg
3961 , 'Manual_Discount'
3962 );
3963 END IF;
3964
3965 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3966
3967 END Manual_Discount;
3968
3969 -- Override_Allowed
3970
3971 FUNCTION Override_Allowed
3972 ( p_override_allowed IN VARCHAR2
3973 ) RETURN VARCHAR2
3974 IS
3975 l_code VARCHAR2(1);
3976 BEGIN
3977
3978 IF p_override_allowed IS NULL
3979 THEN
3980 RETURN NULL;
3981 END IF;
3982
3983 -- SELECT XXXX_id
3984 -- INTO l_code
3985 -- FROM XXXX_table
3986 -- WHERE XXXX_val_column = p_override_allowed
3987
3988 RETURN l_code;
3989
3990 EXCEPTION
3991
3992 WHEN NO_DATA_FOUND THEN
3993
3994 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
3995 THEN
3996
3997 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
3998 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','override_allowed_flag');
3999 OE_MSG_PUB.Add;
4000
4001 END IF;
4002
4003 RETURN FND_API.G_MISS_CHAR;
4004
4005 WHEN OTHERS THEN
4006
4007 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4008 THEN
4009 OE_MSG_PUB.Add_Exc_Msg
4010 ( G_PKG_NAME
4011 , 'Override_Allowed'
4012 );
4013 END IF;
4014
4015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4016
4017 END Override_Allowed;
4018
4019 -- Prorate
4020
4021 FUNCTION Prorate
4022 ( p_prorate IN VARCHAR2
4023 ) RETURN VARCHAR2
4024 IS
4025 l_code VARCHAR2(30);
4026 BEGIN
4027
4028 IF p_prorate IS NULL
4029 THEN
4030 RETURN NULL;
4031 END IF;
4032
4033 -- SELECT XXXX_id
4034 -- INTO l_code
4035 -- FROM XXXX_table
4036 -- WHERE XXXX_val_column = p_prorate
4037
4038 RETURN l_code;
4039
4040 EXCEPTION
4041
4042 WHEN NO_DATA_FOUND THEN
4043
4044 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4045 THEN
4046
4047 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4048 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','prorate_flag');
4049 OE_MSG_PUB.Add;
4050
4051 END IF;
4052
4053 RETURN FND_API.G_MISS_CHAR;
4054
4055 WHEN OTHERS THEN
4056
4057 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4058 THEN
4059 OE_MSG_PUB.Add_Exc_Msg
4060 ( G_PKG_NAME
4061 , 'Prorate'
4062 );
4063 END IF;
4064
4065 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4066
4067 END Prorate;
4068
4069
4070 -- Method
4071
4072 FUNCTION Method
4073 ( p_method IN VARCHAR2
4074 ) RETURN VARCHAR2
4075 IS
4076 l_code VARCHAR2(4);
4077 BEGIN
4078
4079 IF p_method IS NULL
4080 THEN
4081 RETURN NULL;
4082 END IF;
4083
4084 -- SELECT XXXX_id
4085 -- INTO l_code
4086 -- FROM XXXX_table
4087 -- WHERE XXXX_val_column = p_method
4088
4089 RETURN l_code;
4090
4091 EXCEPTION
4092
4093 WHEN NO_DATA_FOUND THEN
4094
4095 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4096 THEN
4097
4098 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4099 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','method_code');
4100 OE_MSG_PUB.Add;
4101
4102 END IF;
4103
4104 RETURN FND_API.G_MISS_CHAR;
4105
4106 WHEN OTHERS THEN
4107
4108 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4109 THEN
4110 OE_MSG_PUB.Add_Exc_Msg
4111 ( G_PKG_NAME
4112 , 'Method'
4113 );
4114 END IF;
4115
4116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4117
4118 END Method;
4119
4120 -- Price_List_Line
4121
4122 FUNCTION Price_List_Line
4123 ( p_price_list_line IN VARCHAR2
4124 ) RETURN NUMBER
4125 IS
4126 l_id NUMBER;
4127 BEGIN
4128
4129 IF p_price_list_line IS NULL
4130 THEN
4131 RETURN NULL;
4132 END IF;
4133
4134 -- SELECT XXXX_id
4135 -- INTO l_id
4136 -- FROM XXXX_table
4137 -- WHERE XXXX_val_column = p_price_list_line
4138
4139 RETURN l_id;
4140
4141 EXCEPTION
4142
4143 WHEN NO_DATA_FOUND THEN
4144
4148 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4145 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4146 THEN
4147
4149 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','price_list_line_id');
4150 OE_MSG_PUB.Add;
4151
4152 END IF;
4153
4154 RETURN FND_API.G_MISS_NUM;
4155
4156 WHEN OTHERS THEN
4157
4158 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4159 THEN
4160 OE_MSG_PUB.Add_Exc_Msg
4161 ( G_PKG_NAME
4162 , 'Price_List_Line'
4163 );
4164 END IF;
4165
4166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4167
4168 END Price_List_Line;
4169
4170 -- Pricing_Rule
4171
4172 FUNCTION Pricing_Rule
4173 ( p_pricing_rule IN VARCHAR2
4174 ) RETURN NUMBER
4175 IS
4176 l_id NUMBER;
4177 BEGIN
4178
4179 IF p_pricing_rule IS NULL
4180 THEN
4181 RETURN NULL;
4182 END IF;
4183
4184 -- SELECT XXXX_id
4185 -- INTO l_id
4186 -- FROM XXXX_table
4187 -- WHERE XXXX_val_column = p_pricing_rule
4188
4189 RETURN l_id;
4190
4191 EXCEPTION
4192
4193 WHEN NO_DATA_FOUND THEN
4194
4195 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4196 THEN
4197
4198 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4199 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','pricing_rule_id');
4200 OE_MSG_PUB.Add;
4201
4202 END IF;
4203
4204 RETURN FND_API.G_MISS_NUM;
4205
4206 WHEN OTHERS THEN
4207
4208 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4209 THEN
4210 OE_MSG_PUB.Add_Exc_Msg
4211 ( G_PKG_NAME
4212 , 'Pricing_Rule'
4213 );
4214 END IF;
4215
4216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4217
4218 END Pricing_Rule;
4219
4220 -- Reprice
4221
4222 FUNCTION Reprice
4223 ( p_reprice IN VARCHAR2
4224 ) RETURN VARCHAR2
4225 IS
4226 l_code VARCHAR2(1);
4227 BEGIN
4228
4229 IF p_reprice IS NULL
4230 THEN
4231 RETURN NULL;
4232 END IF;
4233
4234 -- SELECT XXXX_id
4235 -- INTO l_code
4236 -- FROM XXXX_table
4237 -- WHERE XXXX_val_column = p_reprice
4238
4239 RETURN l_code;
4240
4241 EXCEPTION
4242
4243 WHEN NO_DATA_FOUND THEN
4244
4245 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4246 THEN
4247
4248 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4249 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','reprice_flag');
4250 OE_MSG_PUB.Add;
4251
4252 END IF;
4253
4254 RETURN FND_API.G_MISS_CHAR;
4255
4256 WHEN OTHERS THEN
4257
4258 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4259 THEN
4260 OE_MSG_PUB.Add_Exc_Msg
4261 ( G_PKG_NAME
4262 , 'Reprice'
4263 );
4264 END IF;
4265
4266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4267
4268 END Reprice;
4269
4270 -- Unit
4271
4272 FUNCTION Unit
4273 ( p_unit IN VARCHAR2
4274 ) RETURN VARCHAR2
4275 IS
4276 l_code VARCHAR2(3);
4277 BEGIN
4278
4279 IF p_unit IS NULL
4280 THEN
4281 RETURN NULL;
4282 END IF;
4283
4284 -- SELECT XXXX_id
4285 -- INTO l_code
4286 -- FROM XXXX_table
4287 -- WHERE XXXX_val_column = p_unit
4288
4289 RETURN l_code;
4290
4291 EXCEPTION
4292
4293 WHEN NO_DATA_FOUND THEN
4294
4295 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4296 THEN
4297
4298 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4299 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','unit_code');
4300 OE_MSG_PUB.Add;
4301
4302 END IF;
4303
4304 RETURN FND_API.G_MISS_CHAR;
4305
4306 WHEN OTHERS THEN
4307
4308 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4309 THEN
4310 OE_MSG_PUB.Add_Exc_Msg
4311 ( G_PKG_NAME
4312 , 'Unit'
4313 );
4314 END IF;
4315
4316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4317
4318 END Unit;
4319
4320 -- Customer_Class
4321
4322 FUNCTION Customer_Class
4323 ( p_customer_class IN VARCHAR2
4324 ) RETURN VARCHAR2
4325 IS
4326 l_code VARCHAR2(30);
4327 BEGIN
4328
4332 END IF;
4329 IF p_customer_class IS NULL
4330 THEN
4331 RETURN NULL;
4333
4334 -- SELECT XXXX_id
4335 -- INTO l_code
4336 -- FROM XXXX_table
4337 -- WHERE XXXX_val_column = p_customer_class
4338
4339 RETURN l_code;
4340
4341 EXCEPTION
4342
4343 WHEN NO_DATA_FOUND THEN
4344
4345 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4346 THEN
4347
4348 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4349 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','customer_class_code');
4350 OE_MSG_PUB.Add;
4351
4352 END IF;
4353
4354 RETURN FND_API.G_MISS_CHAR;
4355
4356 WHEN OTHERS THEN
4357
4358 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4359 THEN
4360 OE_MSG_PUB.Add_Exc_Msg
4361 ( G_PKG_NAME
4362 , 'Customer_Class'
4363 );
4364 END IF;
4365
4366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4367
4368 END Customer_Class;
4369
4370 -- Discount_Customer
4371
4372 FUNCTION Discount_Customer
4373 ( p_discount_customer IN VARCHAR2
4374 ) RETURN NUMBER
4375 IS
4376 l_id NUMBER;
4377 BEGIN
4378
4379 IF p_discount_customer IS NULL
4380 THEN
4381 RETURN NULL;
4382 END IF;
4383
4384 -- SELECT XXXX_id
4385 -- INTO l_id
4386 -- FROM XXXX_table
4387 -- WHERE XXXX_val_column = p_discount_customer
4388
4389 RETURN l_id;
4390
4391 EXCEPTION
4392
4393 WHEN NO_DATA_FOUND THEN
4394
4395 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4396 THEN
4397
4398 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4399 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','discount_customer_id');
4400 OE_MSG_PUB.Add;
4401
4402 END IF;
4403
4404 RETURN FND_API.G_MISS_NUM;
4405
4406 WHEN OTHERS THEN
4407
4408 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4409 THEN
4410 OE_MSG_PUB.Add_Exc_Msg
4411 ( G_PKG_NAME
4412 , 'Discount_Customer'
4413 );
4414 END IF;
4415
4416 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4417
4418 END Discount_Customer;
4419
4420 -- Site_Use
4421
4422 FUNCTION Site_Use
4423 ( p_site_use IN VARCHAR2
4424 ) RETURN NUMBER
4425 IS
4426 l_id NUMBER;
4427 BEGIN
4428
4429 IF p_site_use IS NULL
4430 THEN
4431 RETURN NULL;
4432 END IF;
4433
4434 -- SELECT XXXX_id
4435 -- INTO l_id
4436 -- FROM XXXX_table
4437 -- WHERE XXXX_val_column = p_site_use
4438
4439 RETURN l_id;
4440
4441 EXCEPTION
4442
4443 WHEN NO_DATA_FOUND THEN
4444
4445 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4446 THEN
4447
4448 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4449 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','site_use_id');
4450 OE_MSG_PUB.Add;
4451
4452 END IF;
4453
4454 RETURN FND_API.G_MISS_NUM;
4455
4456 WHEN OTHERS THEN
4457
4458 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4459 THEN
4460 OE_MSG_PUB.Add_Exc_Msg
4461 ( G_PKG_NAME
4462 , 'Site_Use'
4463 );
4464 END IF;
4465
4466 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4467
4468 END Site_Use;
4469
4470 -- Entity
4471
4472 FUNCTION Entity
4473 ( p_entity IN VARCHAR2
4474 ) RETURN NUMBER
4475 IS
4476 l_id NUMBER;
4477 BEGIN
4478
4479 IF p_entity IS NULL
4480 THEN
4481 RETURN NULL;
4482 END IF;
4483
4484 -- SELECT XXXX_id
4485 -- INTO l_id
4486 -- FROM XXXX_table
4487 -- WHERE XXXX_val_column = p_entity
4488
4489 RETURN l_id;
4490
4491 EXCEPTION
4492
4493 WHEN NO_DATA_FOUND THEN
4494
4495 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4496 THEN
4497
4498 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4499 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','entity_id');
4500 OE_MSG_PUB.Add;
4501
4502 END IF;
4503
4504 RETURN FND_API.G_MISS_NUM;
4505
4506 WHEN OTHERS THEN
4507
4508 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4509 THEN
4510 OE_MSG_PUB.Add_Exc_Msg
4511 ( G_PKG_NAME
4515
4512 , 'Entity'
4513 );
4514 END IF;
4516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4517
4518 END Entity;
4519
4520 -- Method_Type
4521
4522 FUNCTION Method_Type
4523 ( p_method_type IN VARCHAR2
4524 ) RETURN VARCHAR2
4525 IS
4526 l_code VARCHAR2(30);
4527 BEGIN
4528
4529 IF p_method_type IS NULL
4530 THEN
4531 RETURN NULL;
4532 END IF;
4533
4534 -- SELECT XXXX_id
4535 -- INTO l_code
4536 -- FROM XXXX_table
4537 -- WHERE XXXX_val_column = p_method_type
4538
4539 RETURN l_code;
4540
4541 EXCEPTION
4542
4543 WHEN NO_DATA_FOUND THEN
4544
4545 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4546 THEN
4547
4548 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4549 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','method_type_code');
4550 OE_MSG_PUB.Add;
4551
4552 END IF;
4553
4554 RETURN FND_API.G_MISS_CHAR;
4555
4556 WHEN OTHERS THEN
4557
4558 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4559 THEN
4560 OE_MSG_PUB.Add_Exc_Msg
4561 ( G_PKG_NAME
4562 , 'Method_Type'
4563 );
4564 END IF;
4565
4566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4567
4568 END Method_Type;
4569
4570 /* Pricing Contract Functions : End */
4571
4572 -- Lot_Serial
4573
4574 FUNCTION Lot_Serial
4575 ( p_lot_serial IN VARCHAR2
4576 ) RETURN NUMBER
4577 IS
4578 l_id NUMBER;
4579 BEGIN
4580
4581 IF p_lot_serial IS NULL
4582 THEN
4583 RETURN NULL;
4584 END IF;
4585
4586 -- SELECT XXXX_id
4587 -- INTO l_id
4588 -- FROM XXXX_table
4589 -- WHERE XXXX_val_column = p_lot_serial
4590
4591 RETURN l_id;
4592
4593 EXCEPTION
4594
4595 WHEN NO_DATA_FOUND THEN
4596
4597 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4598 THEN
4599
4600 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4601 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','lot_serial_id');
4602 OE_MSG_PUB.Add;
4603
4604 END IF;
4605
4606 RETURN FND_API.G_MISS_NUM;
4607
4608 WHEN OTHERS THEN
4609
4610 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4611 THEN
4612 OE_MSG_PUB.Add_Exc_Msg
4613 ( G_PKG_NAME
4614 , 'Lot_Serial'
4615 );
4616 END IF;
4617
4618 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4619
4620 END Lot_Serial;
4621
4622 -- Appear_On_Ack
4623
4624 FUNCTION Appear_On_Ack
4625 ( p_appear_on_ack IN VARCHAR2
4626 ) RETURN VARCHAR2
4627 IS
4628 l_code VARCHAR2(1);
4629 BEGIN
4630
4631 IF p_appear_on_ack IS NULL
4632 THEN
4633 RETURN NULL;
4634 END IF;
4635
4636 -- SELECT XXXX_id
4637 -- INTO l_code
4638 -- FROM XXXX_table
4639 -- WHERE XXXX_val_column = p_appear_on_ack
4640
4641 RETURN l_code;
4642
4643 EXCEPTION
4644
4645 WHEN NO_DATA_FOUND THEN
4646
4647 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4648 THEN
4649
4650 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4651 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','appear_on_ack_flag');
4652 OE_MSG_PUB.Add;
4653
4654 END IF;
4655
4656 RETURN FND_API.G_MISS_CHAR;
4657
4658 WHEN OTHERS THEN
4659
4660 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4661 THEN
4662 OE_MSG_PUB.Add_Exc_Msg
4663 ( G_PKG_NAME
4664 , 'Appear_On_Ack'
4665 );
4666 END IF;
4667
4668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4669
4670 END Appear_On_Ack;
4671
4672 -- Appear_On_Invoice
4673
4674 FUNCTION Appear_On_Invoice
4675 ( p_appear_on_invoice IN VARCHAR2
4676 ) RETURN VARCHAR2
4677 IS
4678 l_code VARCHAR2(1);
4679 BEGIN
4680
4681 IF p_appear_on_invoice IS NULL
4682 THEN
4683 RETURN NULL;
4684 END IF;
4685
4686 -- SELECT XXXX_id
4687 -- INTO l_code
4688 -- FROM XXXX_table
4689 -- WHERE XXXX_val_column = p_appear_on_invoice
4690
4691 RETURN l_code;
4692
4693 EXCEPTION
4694
4695 WHEN NO_DATA_FOUND THEN
4696
4697 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4698 THEN
4699
4700 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4701 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','appear_on_invoice_flag');
4705
4702 OE_MSG_PUB.Add;
4703
4704 END IF;
4706 RETURN FND_API.G_MISS_CHAR;
4707
4708 WHEN OTHERS THEN
4709
4710 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4711 THEN
4712 OE_MSG_PUB.Add_Exc_Msg
4713 ( G_PKG_NAME
4714 , 'Appear_On_Invoice'
4715 );
4716 END IF;
4717
4718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4719
4720 END Appear_On_Invoice;
4721
4722 -- Charge
4723
4724 FUNCTION Charge
4725 ( p_charge IN VARCHAR2
4726 ) RETURN NUMBER
4727 IS
4728 l_id NUMBER;
4729 BEGIN
4730
4731 IF p_charge IS NULL
4732 THEN
4733 RETURN NULL;
4734 END IF;
4735
4736 -- SELECT XXXX_id
4737 -- INTO l_id
4738 -- FROM XXXX_table
4739 -- WHERE XXXX_val_column = p_charge
4740
4741 RETURN l_id;
4742
4743 EXCEPTION
4744
4745 WHEN NO_DATA_FOUND THEN
4746
4747 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4748 THEN
4749
4750 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4751 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','charge_id');
4752 OE_MSG_PUB.Add;
4753
4754 END IF;
4755
4756 RETURN FND_API.G_MISS_NUM;
4757
4758 WHEN OTHERS THEN
4759
4760 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4761 THEN
4762 OE_MSG_PUB.Add_Exc_Msg
4763 ( G_PKG_NAME
4764 , 'Charge'
4765 );
4766 END IF;
4767
4768 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4769
4770 END Charge;
4771
4772 -- Charge_Type
4773
4774 FUNCTION Charge_Type
4775 ( p_charge_type IN VARCHAR2
4776 ) RETURN NUMBER
4777 IS
4778 l_id NUMBER;
4779 BEGIN
4780
4781 IF p_charge_type IS NULL
4782 THEN
4783 RETURN NULL;
4784 END IF;
4785
4786 -- SELECT XXXX_id
4787 -- INTO l_id
4788 -- FROM XXXX_table
4789 -- WHERE XXXX_val_column = p_charge_type
4790
4791 RETURN l_id;
4792
4793 EXCEPTION
4794
4795 WHEN NO_DATA_FOUND THEN
4796
4797 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4798 THEN
4799
4800 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4801 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','charge_type_id');
4802 OE_MSG_PUB.Add;
4803
4804 END IF;
4805
4806 RETURN FND_API.G_MISS_NUM;
4807
4808 WHEN OTHERS THEN
4809
4810 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4811 THEN
4812 OE_MSG_PUB.Add_Exc_Msg
4813 ( G_PKG_NAME
4814 , 'Charge_Type'
4815 );
4816 END IF;
4817
4818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4819
4820 END Charge_Type;
4821
4822 -- Cost_Or_Charge
4823
4824 FUNCTION Cost_Or_Charge
4825 ( p_cost_or_charge IN VARCHAR2
4826 ) RETURN VARCHAR2
4827 IS
4828 l_code VARCHAR2(1);
4829 BEGIN
4830
4831 IF p_cost_or_charge IS NULL
4832 THEN
4833 RETURN NULL;
4834 END IF;
4835
4836 -- SELECT XXXX_id
4837 -- INTO l_code
4838 -- FROM XXXX_table
4839 -- WHERE XXXX_val_column = p_cost_or_charge
4840
4841 RETURN l_code;
4842
4843 EXCEPTION
4844
4845 WHEN NO_DATA_FOUND THEN
4846
4847 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4848 THEN
4849
4850 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4851 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','cost_or_charge_flag');
4852 OE_MSG_PUB.Add;
4853
4854 END IF;
4855
4856 RETURN FND_API.G_MISS_CHAR;
4857
4858 WHEN OTHERS THEN
4859
4860 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4861 THEN
4862 OE_MSG_PUB.Add_Exc_Msg
4863 ( G_PKG_NAME
4864 , 'Cost_Or_Charge'
4865 );
4866 END IF;
4867
4868 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4869
4870 END Cost_Or_Charge;
4871
4872 -- Departure
4873
4874 FUNCTION Departure
4875 ( p_departure IN VARCHAR2
4876 ) RETURN NUMBER
4877 IS
4878 l_id NUMBER;
4879 BEGIN
4880
4881 IF p_departure IS NULL
4882 THEN
4883 RETURN NULL;
4884 END IF;
4885
4886 -- SELECT XXXX_id
4887 -- INTO l_id
4888 -- FROM XXXX_table
4889 -- WHERE XXXX_val_column = p_departure
4890
4891 RETURN l_id;
4892
4893 EXCEPTION
4894
4895 WHEN NO_DATA_FOUND THEN
4896
4900 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4897 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4898 THEN
4899
4901 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','departure_id');
4902 OE_MSG_PUB.Add;
4903
4904 END IF;
4905
4906 RETURN FND_API.G_MISS_NUM;
4907
4908 WHEN OTHERS THEN
4909
4910 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4911 THEN
4912 OE_MSG_PUB.Add_Exc_Msg
4913 ( G_PKG_NAME
4914 , 'Departure'
4915 );
4916 END IF;
4917
4918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4919
4920 END Departure;
4921
4922 -- Estimated
4923
4924 FUNCTION Estimated
4925 ( p_estimated IN VARCHAR2
4926 ) RETURN VARCHAR2
4927 IS
4928 l_code VARCHAR2(1);
4929 BEGIN
4930
4931 IF p_estimated IS NULL
4932 THEN
4933 RETURN NULL;
4934 END IF;
4935
4936 -- SELECT XXXX_id
4937 -- INTO l_code
4938 -- FROM XXXX_table
4939 -- WHERE XXXX_val_column = p_estimated
4940
4941 RETURN l_code;
4942
4943 EXCEPTION
4944
4945 WHEN NO_DATA_FOUND THEN
4946
4947 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4948 THEN
4949
4950 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4951 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','estimated_flag');
4952 OE_MSG_PUB.Add;
4953
4954 END IF;
4955
4956 RETURN FND_API.G_MISS_CHAR;
4957
4958 WHEN OTHERS THEN
4959
4960 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4961 THEN
4962 OE_MSG_PUB.Add_Exc_Msg
4963 ( G_PKG_NAME
4964 , 'Estimated'
4965 );
4966 END IF;
4967
4968 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4969
4970 END Estimated;
4971
4972 -- Invoiced
4973
4974 FUNCTION Invoiced
4975 ( p_invoiced IN VARCHAR2
4976 ) RETURN VARCHAR2
4977 IS
4978 l_code VARCHAR2(1);
4979 BEGIN
4980
4981 IF p_invoiced IS NULL
4982 THEN
4983 RETURN NULL;
4984 END IF;
4985
4986 -- SELECT XXXX_id
4987 -- INTO l_code
4988 -- FROM XXXX_table
4989 -- WHERE XXXX_val_column = p_invoiced
4990
4991 RETURN l_code;
4992
4993 EXCEPTION
4994
4995 WHEN NO_DATA_FOUND THEN
4996
4997 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
4998 THEN
4999
5000 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5001 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','invoiced_flag');
5002 OE_MSG_PUB.Add;
5003
5004 END IF;
5005
5006 RETURN FND_API.G_MISS_CHAR;
5007
5008 WHEN OTHERS THEN
5009
5010 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5011 THEN
5012 OE_MSG_PUB.Add_Exc_Msg
5013 ( G_PKG_NAME
5014 , 'Invoiced'
5015 );
5016 END IF;
5017
5018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5019
5020 END Invoiced;
5021
5022 -- Parent_Charge
5023
5024 FUNCTION Parent_Charge
5025 ( p_parent_charge IN VARCHAR2
5026 ) RETURN NUMBER
5027 IS
5028 l_id NUMBER;
5029 BEGIN
5030
5031 IF p_parent_charge IS NULL
5032 THEN
5033 RETURN NULL;
5034 END IF;
5035
5036 -- SELECT XXXX_id
5037 -- INTO l_id
5038 -- FROM XXXX_table
5039 -- WHERE XXXX_val_column = p_parent_charge
5040
5041 RETURN l_id;
5042
5043 EXCEPTION
5044
5045 WHEN NO_DATA_FOUND THEN
5046
5047 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5048 THEN
5049
5050 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5051 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','parent_charge_id');
5052 OE_MSG_PUB.Add;
5053
5054 END IF;
5055
5056 RETURN FND_API.G_MISS_NUM;
5057
5058 WHEN OTHERS THEN
5059
5060 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5061 THEN
5062 OE_MSG_PUB.Add_Exc_Msg
5063 ( G_PKG_NAME
5064 , 'Parent_Charge'
5065 );
5066 END IF;
5067
5068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5069
5070 END Parent_Charge;
5071
5072 -- Returnable
5073
5074 FUNCTION Returnable
5075 ( p_returnable IN VARCHAR2
5076 ) RETURN VARCHAR2
5077 IS
5078 l_code VARCHAR2(1);
5079 BEGIN
5080
5081 IF p_returnable IS NULL
5082 THEN
5083 RETURN NULL;
5084 END IF;
5085
5089 -- WHERE XXXX_val_column = p_returnable
5086 -- SELECT XXXX_id
5087 -- INTO l_code
5088 -- FROM XXXX_table
5090
5091 RETURN l_code;
5092
5093 EXCEPTION
5094
5095 WHEN NO_DATA_FOUND THEN
5096
5097 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5098 THEN
5099
5100 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5101 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','returnable_flag');
5102 OE_MSG_PUB.Add;
5103
5104 END IF;
5105
5106 RETURN FND_API.G_MISS_CHAR;
5107
5108 WHEN OTHERS THEN
5109
5110 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5111 THEN
5112 OE_MSG_PUB.Add_Exc_Msg
5113 ( G_PKG_NAME
5114 , 'Returnable'
5115 );
5116 END IF;
5117
5118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5119
5120 END Returnable;
5121
5122 -- Tax_Group
5123
5124 FUNCTION Tax_Group
5125 ( p_tax_group IN VARCHAR2
5126 ) RETURN VARCHAR2
5127 IS
5128 l_code VARCHAR2(30);
5129 BEGIN
5130
5131 IF p_tax_group IS NULL
5132 THEN
5133 RETURN NULL;
5134 END IF;
5135
5136 -- SELECT XXXX_id
5137 -- INTO l_code
5138 -- FROM XXXX_table
5139 -- WHERE XXXX_val_column = p_tax_group
5140
5141 RETURN l_code;
5142
5143 EXCEPTION
5144
5145 WHEN NO_DATA_FOUND THEN
5146
5147 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5148 THEN
5149
5150 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5151 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','tax_group_code');
5152 OE_MSG_PUB.Add;
5153
5154 END IF;
5155
5156 RETURN FND_API.G_MISS_CHAR;
5157
5158 WHEN OTHERS THEN
5159
5160 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5161 THEN
5162 OE_MSG_PUB.Add_Exc_Msg
5163 ( G_PKG_NAME
5164 , 'Tax_Group'
5165 );
5166 END IF;
5167
5168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5169
5170 END Tax_Group;
5171
5172
5173 FUNCTION Flow_Status
5174 ( p_flow_status IN VARCHAR2
5175 ) RETURN VARCHAR2
5176 IS
5177 l_code VARCHAR2(30);
5178 BEGIN
5179
5180 IF p_flow_status IS NULL
5181 THEN
5182 RETURN NULL;
5183 END IF;
5184
5185 SELECT distinct LOOKUP_CODE
5186 INTO l_code
5187 FROM OE_LOOKUPS
5188 WHERE (lookup_type = 'FLOW_STATUS'
5189 OR lookup_type = 'LINE_FLOW_STATUS')
5190 AND meaning = p_flow_status;
5191
5192 RETURN l_code;
5193
5194 EXCEPTION
5195
5196 WHEN NO_DATA_FOUND THEN
5197
5198 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5199 THEN
5200
5201 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5202 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','flow_status_code');
5203 OE_MSG_PUB.Add;
5204
5205 END IF;
5206
5207 RETURN FND_API.G_MISS_CHAR;
5208
5209 WHEN OTHERS THEN
5210
5211 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5212 THEN
5213 OE_MSG_PUB.Add_Exc_Msg
5214 ( G_PKG_NAME
5215 , 'Flow_Status'
5216 );
5217 END IF;
5218
5219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5220
5221 END Flow_Status;
5222
5223
5224 -- (Done aksingh -- disable_date missing)
5225 FUNCTION freight_Carrier
5226 ( p_freight_carrier IN VARCHAR2
5227 , p_ship_from_org_id IN NUMBER
5228 ) RETURN VARCHAR2
5229 IS
5230 l_code VARCHAR2(30);
5231 l_ship_from_org_id NUMBER;
5232 BEGIN
5233
5234 IF p_freight_carrier IS NULL
5235 THEN
5236 RETURN NULL;
5237 END IF;
5238
5239 IF p_ship_from_org_id = FND_API.G_MISS_NUM THEN
5240 l_ship_from_org_id := NULL;
5241 ELSE
5242 l_ship_from_org_id := p_ship_from_org_id;
5243 END IF;
5244
5245 SELECT freight_code
5246 INTO l_code
5247 FROM ORG_FREIGHT
5248 WHERE DESCRIPTION = p_freight_carrier
5249 AND ORGANIZATION_ID = nvl(l_ship_from_org_id,Organization_id);
5250
5251 RETURN l_code;
5252
5253 EXCEPTION
5254
5255 WHEN NO_DATA_FOUND THEN
5256
5257 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5258 THEN
5259
5260 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5261 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','freight_carrier_code');
5262 OE_MSG_PUB.Add;
5263
5264 END IF;
5265
5266 RETURN FND_API.G_MISS_CHAR;
5267
5271 THEN
5268 WHEN OTHERS THEN
5269
5270 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5272 OE_MSG_PUB.Add_Exc_Msg
5273 ( G_PKG_NAME
5274 , 'Freight_Carrier'
5275 );
5276 END IF;
5277
5278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5279
5280 END Freight_Carrier;
5281
5282 -- (Done aksingh)
5283 FUNCTION Sales_Channel
5284 ( p_sales_channel IN VARCHAR2
5285 ) RETURN VARCHAR2
5286 IS
5287 l_code VARCHAR2(30);
5288 l_lookup_type VARCHAR2(80) :='SALES_CHANNEL';
5289 BEGIN
5290
5291 IF p_sales_channel IS NULL
5292 THEN
5293 RETURN NULL;
5294 END IF;
5295
5296 SELECT LOOKUP_CODE
5297 INTO l_code
5298 FROM OE_LOOKUPS
5299 WHERE LOOKUP_TYPE = l_lookup_type
5300 AND MEANING = p_sales_channel;
5301
5302 RETURN l_code;
5303
5304 EXCEPTION
5305
5306 WHEN NO_DATA_FOUND THEN
5307
5308 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5309 THEN
5310
5311 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5312 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sales_channel_code');
5313 OE_MSG_PUB.Add;
5314
5315 END IF;
5316
5317 RETURN FND_API.G_MISS_CHAR;
5318
5319 WHEN OTHERS THEN
5320
5321 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5322 THEN
5323 OE_MSG_PUB.Add_Exc_Msg
5324 ( G_PKG_NAME
5325 , 'Sales_Channel'
5326 );
5327 END IF;
5328
5329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5330
5331 END Sales_Channel;
5332
5333 FUNCTION Customer_Location
5334 ( p_sold_to_location_address1 IN VARCHAR2
5335 , p_sold_to_location_address2 IN VARCHAR2
5336 , p_sold_to_location_address3 IN VARCHAR2
5337 , p_sold_to_location_address4 IN VARCHAR2
5338 , p_sold_to_location IN VARCHAR2
5339 , p_sold_to_org_id IN NUMBER
5340 , p_sold_to_location_city IN VARCHAR2 DEFAULT NULL
5341 , p_sold_to_location_state IN VARCHAR2 DEFAULT NULL
5342 , p_sold_to_location_postal IN VARCHAR2 DEFAULT NULL
5343 , p_sold_to_location_country IN VARCHAR2 DEFAULT NULL
5344 ) RETURN NUMBER
5345 IS
5346 l_id NUMBER;
5347 --
5348 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5349 --
5350 BEGIN
5351
5352 IF l_debug_level > 0 THEN
5353 oe_debug_pub.add( 'SOLD_TO_LOCATION_ADDRESS1='||P_SOLD_TO_LOCATION_ADDRESS1||' ADDRESS4='||P_SOLD_TO_LOCATION_ADDRESS4||' sold_to_org_id='||p_sold_to_org_id ) ;
5354 oe_debug_pub.add( 'SOLD_TO_LOCATION_ADDRESS2='||P_SOLD_TO_LOCATION_ADDRESS2||' ADDRESS3='||P_SOLD_TO_LOCATION_ADDRESS3);
5355 END IF;
5356
5357 IF nvl( p_sold_to_location_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5358 AND nvl( p_sold_to_location_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5359 AND nvl( p_sold_to_location_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5360 AND nvl( p_sold_to_location_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5361 AND nvl( p_sold_to_org_id,fnd_api.g_miss_num) = fnd_api.g_miss_num
5362 THEN
5363 RETURN NULL;
5364 END IF;
5365
5366 SELECT /* MOAC_SQL_CHANGE */ SITE.SITE_USE_ID
5367 INTO l_id
5368 FROM
5369 HZ_CUST_SITE_USES_ALL SITE,
5370 HZ_PARTY_SITES PARTY_SITE,
5371 HZ_LOCATIONS LOC,
5372 HZ_CUST_ACCT_SITES ACCT_SITE
5373 WHERE
5374 SITE.SITE_USE_CODE = 'SOLD_TO'
5375 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
5376 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
5377 AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
5378 AND SITE.ORG_ID = ACCT_SITE.ORG_ID
5379 AND LOC.ADDRESS1 = p_sold_to_location_address1
5380 AND nvl( LOC.ADDRESS2, fnd_api.g_miss_char) =
5381 nvl( p_sold_to_location_address2, fnd_api.g_miss_char)
5382 AND nvl( LOC.ADDRESS3, fnd_api.g_miss_char) =
5383 nvl( p_sold_to_location_address3, fnd_api.g_miss_char)
5384 AND nvl( LOC.ADDRESS4, fnd_api.g_miss_char) =
5385 nvl( p_sold_to_location_address4, fnd_api.g_miss_char)
5386 AND nvl( LOC.city, fnd_api.g_miss_char) =
5387 nvl( p_sold_to_location_city, fnd_api.g_miss_char)
5388 AND nvl( LOC.state, fnd_api.g_miss_char) =
5389 nvl( p_sold_to_location_state, fnd_api.g_miss_char)
5390 AND nvl( LOC.postal_code, fnd_api.g_miss_char) =
5391 nvl( p_sold_to_location_postal, fnd_api.g_miss_char)
5392 AND nvl( LOC.country, fnd_api.g_miss_char) =
5393 nvl( p_sold_to_location_country, fnd_api.g_miss_char)
5394 AND ACCT_SITE.CUST_ACCOUNT_ID = p_sold_to_org_id ;
5395
5396 RETURN l_id;
5397
5398 EXCEPTION
5399
5403 THEN
5400 WHEN NO_DATA_FOUND THEN
5401
5402 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5404
5405 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5406 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sold_to_site_use_id');
5407 OE_MSG_PUB.Add;
5408
5409 END IF;
5410
5411 RETURN FND_API.G_MISS_NUM;
5412
5413 WHEN OTHERS THEN
5414
5415 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5416 THEN
5417 OE_MSG_PUB.Add_Exc_Msg
5418 ( G_PKG_NAME
5419 , 'Customer_Location'
5420 );
5421 END IF;
5422
5423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5424
5425 END Customer_Location;
5426
5427 FUNCTION END_CUSTOMER
5428 ( p_end_customer IN VARCHAR2
5429 , p_end_customer_number IN VARCHAR2
5430 ) RETURN NUMBER
5431 IS
5432
5433 l_id NUMBER;
5434 --
5435 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5436
5437 BEGIN
5438 oe_Debug_pub.add('entereing end customer value to id in cancel order'||p_end_customer||p_end_customer_number);
5439 IF nvl(p_end_customer,fnd_api.g_miss_char) = fnd_api.g_miss_char
5440 AND nvl(p_end_customer_number,fnd_api.g_miss_char) = fnd_api.g_miss_char
5441 THEN
5442 RETURN NULL;
5443 END IF;
5444
5445 IF nvl(p_end_customer_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
5446 SELECT ORGANIZATION_ID
5447 INTO l_id
5448 FROM OE_SOLD_TO_ORGS_V
5449 WHERE CUSTOMER_NUMBER = p_end_customer_number;
5450 ELSE
5451 Select Cust_Acct.Cust_account_id
5452 into l_id
5453 from HZ_CUST_ACCOUNTS Cust_Acct,
5454 HZ_PARTIES Party
5455 where Cust_Acct.Party_id = Party.party_id
5456 and Party.Party_name = p_end_customer;
5457
5458 END IF;
5459 oe_Debug_pub.add('End custoemr id is'||l_id);
5460
5461 RETURN l_id;
5462
5463 EXCEPTION
5464
5465 WHEN NO_DATA_FOUND THEN
5466
5467 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5468 THEN
5469
5470 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5471 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_id');
5472 OE_MSG_PUB.Add;
5473
5474 END IF;
5475
5476 RETURN FND_API.G_MISS_NUM;
5477
5478 WHEN OTHERS THEN
5479
5480 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5481 THEN
5482 OE_MSG_PUB.Add_Exc_Msg
5483 ( G_PKG_NAME
5484 , 'End_customer'
5485 );
5486 END IF;
5487
5488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5489
5490
5491 END END_CUSTOMER;
5492
5493 FUNCTION END_CUSTOMER_CONTACT
5494 ( p_end_customer_contact IN VARCHAR2
5495 , p_end_customer_id IN NUMBER
5496 ) RETURN NUMBER IS
5497
5498 l_id NUMBER;
5499 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5500 BEGIN
5501
5502 IF p_end_customer_contact IS NULL
5503 THEN
5504 RETURN NULL;
5505 END IF;
5506
5507 SELECT CONTACT_ID
5508 INTO l_id
5509 FROM OE_CONTACTS_V
5510 WHERE NAME = p_end_customer_contact
5511 AND CUSTOMER_ID = p_end_customer_id;
5512
5513 RETURN l_id;
5514
5515 EXCEPTION
5516
5517 WHEN NO_DATA_FOUND THEN
5518
5519 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5520 THEN
5521
5522 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5523 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_contact_id');
5524 OE_MSG_PUB.Add;
5525
5526 END IF;
5527
5528 RETURN FND_API.G_MISS_NUM;
5529
5530 WHEN OTHERS THEN
5531
5532 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5533 THEN
5534 OE_MSG_PUB.Add_Exc_Msg
5535 ( G_PKG_NAME
5536 , 'End_customer_contact'
5537 );
5538 END IF;
5539
5540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5541
5542 END END_CUSTOMER_CONTACT;
5543
5544
5545 FUNCTION END_CUSTOMER_SITE
5546 ( p_end_customer_site_address1 IN VARCHAR2
5547 , p_end_customer_site_address2 IN VARCHAR2
5548 , p_end_customer_site_address3 IN VARCHAR2
5549 , p_end_customer_site_address4 IN VARCHAR2
5550 , p_end_customer_site_location IN VARCHAR2
5551 , p_end_customer_site_org IN VARCHAR2
5552 , p_end_customer_id IN NUMBER
5553 , p_end_customer_site_city IN VARCHAR2 DEFAULT NULL
5554 , p_end_customer_site_state IN VARCHAR2 DEFAULT NULL
5555 , p_end_customer_site_postalcode IN VARCHAR2 DEFAULT NULL
5556 , p_end_customer_site_country IN VARCHAR2 DEFAULT NULL
5557 , p_end_customer_site_use_code IN VARCHAR2 DEFAULT NULL
5558 ) RETURN NUMBER
5559 IS
5560
5561 -- cursor to get the site_id for end_customer
5565 hz_party_sites site,
5562 CURSOR c_site_use_id(in_end_customer_id number,in_end_customer_site_use_code varchar2) IS
5563 SELECT site_use.site_use_id
5564 FROM hz_locations loc,
5566 hz_cust_acct_sites acct_site,
5567 hz_cust_site_uses site_use
5568 WHERE
5569 site_use.cust_acct_site_id=acct_site.cust_acct_site_id
5570 and acct_site.party_site_id=site.party_site_id
5571 and site.location_id=loc.location_id
5572 and site_use.status='A'
5573 and acct_site.status='A' --bug 2752321
5574 and acct_site.cust_account_id=in_end_customer_id
5575 and loc.address1 = p_end_customer_site_address1
5576 and nvl( loc.address2, fnd_api.g_miss_char) =
5577 nvl( p_end_customer_site_address2, fnd_api.g_miss_char)
5578 and nvl( loc.address3, fnd_api.g_miss_char) =
5579 nvl( p_end_customer_site_address3, fnd_api.g_miss_char)
5580 and nvl( loc.address4, fnd_api.g_miss_char) =
5581 nvl( p_end_customer_site_address4, fnd_api.g_miss_char)
5582 and nvl( loc.city, fnd_api.g_miss_char) =
5583 nvl( p_end_customer_site_city, fnd_api.g_miss_char)
5584 and nvl( loc.state, fnd_api.g_miss_char) =
5585 nvl( p_end_customer_site_state, fnd_api.g_miss_char)
5586 and nvl( loc.postal_code, fnd_api.g_miss_char) =
5587 nvl( p_end_customer_site_postalcode, fnd_api.g_miss_char)
5588 and nvl( loc.country, fnd_api.g_miss_char) =
5589 nvl( p_end_customer_site_country, fnd_api.g_miss_char)
5590 and site_use.site_use_code = in_end_customer_site_use_code;
5591
5592 CURSOR c_site_use_id2(in_end_customer_id number,in_end_customer_site_use_code varchar2) IS
5593 SELECT site_use.site_use_id
5594 FROM hz_locations loc,
5595 hz_party_sites site,
5596 hz_cust_acct_sites acct_site,
5597 hz_cust_site_uses site_use
5598 WHERE loc.ADDRESS1 = p_end_customer_site_address1
5599 AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5600 nvl( p_end_customer_site_address2, fnd_api.g_miss_char)
5601 AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5602 nvl( p_end_customer_site_address3, fnd_api.g_miss_char)
5603 AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5604 DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5605 DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
5606 DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
5607 nvl( p_end_customer_site_address4, fnd_api.g_miss_char)
5608 AND site_use.status = 'A'
5609 AND acct_site.status ='A' --bug 2752321
5610 AND acct_site.cust_account_id = p_end_customer_id
5611 and site_use.site_use_code=in_end_customer_site_use_code
5612 and site_use.cust_acct_site_id=acct_site.cust_acct_site_id
5613 and site.party_site_id=acct_site.party_site_id
5614 and site.location_id=loc.location_id;
5615
5616
5617 l_id number;
5618
5619 --
5620 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5621 BEGIN
5622
5623 IF l_debug_level > 0 THEN
5624 oe_debug_pub.add(' end customer site address1: '||p_end_customer_site_address1);
5625 oe_debug_pub.add(' address4: '||p_end_customer_site_address4);
5626 oe_debug_pub.add(' end_customer_id: '||p_end_customer_id );
5627 END IF;
5628
5629 IF nvl( p_end_customer_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5630 AND nvl( p_end_customer_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5631 AND nvl( p_end_customer_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5632 AND nvl( p_end_customer_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5633 AND nvl( p_end_customer_id,fnd_api.g_miss_num) = fnd_api.g_miss_num
5634 THEN
5635 RETURN NULL;
5636 END IF;
5637
5638 -- if no site_use_code passed in
5639 -- try getting sites in the following preference
5640 -- SOLD_TO, SHIP_TO, DELIVER_TO, BILL_TO
5641 IF p_end_customer_site_use_code is null THEN
5642
5643 -- try for SOLD_TO
5644 OPEN c_site_use_id(p_end_customer_id,'SOLD_TO');
5645 FETCH c_site_use_id
5646 INTO l_id;
5647 IF c_site_use_id%FOUND then
5648 CLOSE c_site_use_id;
5649 return l_id;
5650 ELSE
5651 CLOSE c_site_use_id;
5652
5653 OPEN c_site_use_id2(p_end_customer_id,'SOLD_TO');
5654 FETCH c_site_use_id2
5655 INTO l_id;
5656 IF c_site_use_id2%FOUND then
5657 CLOSE c_site_use_id2;
5658 return l_id;
5659 END IF;
5660 CLOSE c_site_use_id2;
5661 END IF;
5662
5663 -- try for SHIP_TO
5664 OPEN c_site_use_id(p_end_customer_id,'SHIP_TO');
5665 FETCH c_site_use_id
5666 INTO l_id;
5667 IF c_site_use_id%FOUND then
5668 CLOSE c_site_use_id;
5669 return l_id;
5670 ELSE
5671 CLOSE c_site_use_id;
5672
5673 OPEN c_site_use_id2(p_end_customer_id,'SHIP_TO');
5674 FETCH c_site_use_id2
5675 INTO l_id;
5676 IF c_site_use_id2%FOUND then
5677 CLOSE c_site_use_id2;
5678 return l_id;
5679 END IF;
5680 CLOSE c_site_use_id2;
5681 END IF;
5682
5683 -- try for DELIVER_TO
5684 OPEN c_site_use_id(p_end_customer_id,'DELIVER_TO');
5685 FETCH c_site_use_id
5686 INTO l_id;
5687 IF c_site_use_id%FOUND then
5688 CLOSE c_site_use_id;
5689 return l_id;
5693 OPEN c_site_use_id2(p_end_customer_id,'DELIVER_TO');
5690 ELSE
5691 CLOSE c_site_use_id;
5692
5694 FETCH c_site_use_id2
5695 INTO l_id;
5696 IF c_site_use_id2%FOUND then
5697 CLOSE c_site_use_id2;
5698 return l_id;
5699 END IF;
5700 CLOSE c_site_use_id2;
5701 END IF;
5702
5703 -- try for BILL_TO
5704 OPEN c_site_use_id(p_end_customer_id,'BILL_TO');
5705 FETCH c_site_use_id
5706 INTO l_id;
5707 IF c_site_use_id%FOUND then
5708 CLOSE c_site_use_id;
5709 return l_id;
5710 ELSE
5711 CLOSE c_site_use_id;
5712
5713 OPEN c_site_use_id2(p_end_customer_id,'BILL_TO');
5714 FETCH c_site_use_id2
5715 INTO l_id;
5716 IF c_site_use_id2%FOUND then
5717 CLOSE c_site_use_id2;
5718 return l_id;
5719 END IF;
5720 CLOSE c_site_use_id2;
5721 END IF;
5722
5723 -- nothing found, raise an error
5724 raise NO_DATA_FOUND;
5725
5726 ELSE
5727 -- site_use_code was passed in
5728
5729 OPEN c_site_use_id(p_end_customer_id,p_end_customer_site_use_code);
5730 FETCH c_site_use_id
5731 INTO l_id;
5732 IF c_site_use_id%FOUND then
5733 CLOSE c_site_use_id;
5734 return l_id;
5735 ELSE
5736 CLOSE c_site_use_id;
5737
5738 OPEN c_site_use_id2(p_end_customer_id,p_end_customer_site_use_code);
5739 FETCH c_site_use_id2
5740 INTO l_id;
5741 IF c_site_use_id2%FOUND then
5742 CLOSE c_site_use_id2;
5743 return l_id;
5744 END IF;
5745 CLOSE c_site_use_id2;
5746 END IF;
5747
5748 -- no data found here, raise an error
5749 raise NO_DATA_FOUND;
5750
5751 END IF;
5752
5753 EXCEPTION
5754
5755 WHEN NO_DATA_FOUND THEN
5756
5757 IF c_site_use_id%ISOPEN then
5758 CLOSE c_site_use_id;
5759 END IF;
5760
5761 IF c_site_use_id2%ISOPEN then
5762 CLOSE c_site_use_id2;
5763 END IF;
5764
5765 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5766 THEN
5767
5768 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5769 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_site_id');
5770 OE_MSG_PUB.Add;
5771
5772 END IF;
5773 RETURN FND_API.G_MISS_NUM;
5774
5775 WHEN OTHERS THEN
5776
5777 IF c_site_use_id%ISOPEN then
5778 CLOSE c_site_use_id;
5779 END IF;
5780
5781 IF c_site_use_id2%ISOPEN then
5782 CLOSE c_site_use_id2;
5783 END IF;
5784
5785 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5786 THEN
5787 OE_MSG_PUB.Add_Exc_Msg
5788 ( G_PKG_NAME
5789 , 'end_cstomer_site_id'
5790 );
5791 END IF;
5792
5793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5794
5795 END END_CUSTOMER_SITE;
5796
5797 FUNCTION IB_Owner
5798 ( p_ib_owner IN VARCHAR2
5799 ) RETURN VARCHAR2
5800 IS
5801 l_code VARCHAR2(30);
5802 l_lookup_type1 VARCHAR2(80) :='ITEM_OWNER';
5803 l_lookup_type2 VARCHAR2(80) :='ONT_INSTALL_BASE';
5804
5805 --
5806 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5807 --
5808 BEGIN
5809 oe_Debug_pub.add('owner');
5810 IF p_ib_owner IS NULL
5811 THEN
5812 RETURN NULL;
5813 END IF;
5814
5815 SELECT LOOKUP_CODE
5816 INTO l_code
5817 FROM OE_LOOKUPS
5818 WHERE MEANING = p_ib_owner
5819 AND LOOKUP_TYPE = l_lookup_type1;-- or LOOKUP_TYPE = l_lookup_type1);
5820 oe_Debug_pub.add('Code value found is'||l_code);
5821 RETURN l_code;
5822
5823 EXCEPTION
5824
5825 WHEN NO_DATA_FOUND THEN
5826
5827 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5828 THEN
5829
5830 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5831 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ib_owner');
5832 OE_MSG_PUB.Add;
5833
5834 END IF;
5835
5836 RETURN FND_API.G_MISS_CHAR;
5837
5838 WHEN OTHERS THEN
5839
5840 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5841 THEN
5842 OE_MSG_PUB.Add_Exc_Msg
5843 ( G_PKG_NAME
5844 , 'IB_Owner'
5845 );
5846 END IF;
5847
5848 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5849
5850 END IB_Owner;
5851
5852 FUNCTION IB_Installed_At_Location
5853 ( p_ib_installed_at_location IN VARCHAR2
5854 ) RETURN VARCHAR2
5855 IS
5856 l_code VARCHAR2(30);
5857 l_lookup_type1 VARCHAR2(80) :='ITEM_INSTALL_LOCATION';
5858 l_lookup_type2 VARCHAR2(80) :='ONT_INSTALL_BASE';
5859
5860 --
5861 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5862 --
5863 BEGIN
5864
5865 IF p_ib_installed_at_location IS NULL
5866 THEN
5867 RETURN NULL;
5868 END IF;
5869
5870 SELECT LOOKUP_CODE
5871 INTO l_code
5872 FROM OE_LOOKUPS
5873 WHERE MEANING = p_ib_installed_at_location
5874 AND LOOKUP_TYPE = l_lookup_type1;-- or LOOKUP_TYPE = l_lookup_type1);
5875
5876 RETURN l_code;
5877
5878 EXCEPTION
5879
5880 WHEN NO_DATA_FOUND THEN
5881
5882 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5883 THEN
5884
5885 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5886 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ib_installed_at_location');
5887 OE_MSG_PUB.Add;
5888
5889 END IF;
5890
5891 RETURN FND_API.G_MISS_CHAR;
5892
5893 WHEN OTHERS THEN
5894
5895 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5896 THEN
5897 OE_MSG_PUB.Add_Exc_Msg
5898 ( G_PKG_NAME
5899 , 'ib_installed_at_location'
5900 );
5901 END IF;
5902
5903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5904
5905 END IB_Installed_At_Location;
5906
5907 FUNCTION IB_Current_Location
5908 ( p_ib_current_location IN VARCHAR2
5909 ) RETURN VARCHAR2
5910 IS
5911 l_code VARCHAR2(30);
5912 l_lookup_type1 VARCHAR2(80) :='ITEM_CURRENT_LOCATION';
5913 l_lookup_type2 VARCHAR2(80) :='ONT_INSTALL_BASE';
5914
5915 --
5916 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5917 --
5918 BEGIN
5919
5920 IF p_ib_current_location IS NULL
5921 THEN
5922 RETURN NULL;
5923 END IF;
5924
5925 SELECT LOOKUP_CODE
5926 INTO l_code
5927 FROM OE_LOOKUPS
5928 WHERE MEANING = p_ib_current_location
5929 AND LOOKUP_TYPE = l_lookup_type1;-- or LOOKUP_TYPE = l_lookup_type1);
5930
5931 RETURN l_code;
5932
5933 EXCEPTION
5934
5935 WHEN NO_DATA_FOUND THEN
5936
5937 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
5938 THEN
5939
5940 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5941 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ib_current_location');
5942 OE_MSG_PUB.Add;
5943
5944 END IF;
5945
5946 RETURN FND_API.G_MISS_CHAR;
5947
5948 WHEN OTHERS THEN
5949
5950 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5951 THEN
5952 OE_MSG_PUB.Add_Exc_Msg
5953 ( G_PKG_NAME
5954 , 'ib_current_location'
5955 );
5956 END IF;
5957
5958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5959
5960 END IB_Current_Location;
5961
5962 END OE_CNCL_Value_To_Id;