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