[Home] [Help]
PACKAGE BODY: APPS.OE_CNCL_VALIDATE_HEADER
Source
1 PACKAGE BODY OE_CNCL_Validate_Header AS
2 /* $Header: OEXVCHDB.pls 120.8.12020000.2 2012/11/27 22:21:02 cpati ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_CNCL_Validate_Header';
7
8
9 /* LOCAL PROCEDURES */
10
11 /*-------------------------------------------------------
12 PROCEDURE: Check_Book_Reqd_Attributes
13 Description:
14 --------------------------------------------------------*/
15
16 PROCEDURE Check_Book_Reqd_Attributes
17 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
18 , x_return_status IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
19 )
20 IS
21 l_order_type_rec OE_Order_Cache.Order_Type_Rec_Type;
22 l_set_of_books_rec OE_Order_Cache.Set_Of_Books_Rec_Type;
23 BEGIN
24
25 oe_debug_pub.add('Enter OE_CNCL_VALIDATE_HEADER.CHECK_BOOK_REQD',1);
26
27 -- Check for the following required fields on a booked order:
28 -- Order Number, Sold To Org, Invoice To Org,
29 -- Price List, Tax Exempt Flag, Sales Person, Order Date
30
31 IF p_header_rec.sold_to_org_id IS NULL
32 THEN
33 x_return_status := FND_API.G_RET_STS_ERROR;
34 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
35 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
36 OE_Order_UTIL.Get_Attribute_Name('SOLD_TO_ORG_ID'));
37 OE_MSG_PUB.ADD;
38 END IF;
39
40 IF p_header_rec.salesrep_id IS NULL
41 THEN
42 x_return_status := FND_API.G_RET_STS_ERROR;
43 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
44 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
45 OE_Order_UTIL.Get_Attribute_Name('SALESREP_ID'));
46 OE_MSG_PUB.ADD;
47 END IF;
48
49 IF p_header_rec.ordered_date IS NULL
50 THEN
51 x_return_status := FND_API.G_RET_STS_ERROR;
52 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
53 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
54 OE_Order_UTIL.Get_Attribute_Name('ORDERED_DATE'));
55 OE_MSG_PUB.ADD;
56 END IF;
57
58 IF p_header_rec.invoice_to_org_id IS NULL
59 THEN
60 x_return_status := FND_API.G_RET_STS_ERROR;
61 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
62 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
63 OE_Order_UTIL.Get_Attribute_Name('INVOICE_TO_ORG_ID'));
64 OE_MSG_PUB.ADD;
65 END IF;
66
67 IF p_header_rec.tax_exempt_flag IS NULL
68 THEN
69 x_return_status := FND_API.G_RET_STS_ERROR;
70 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
71 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
72 OE_Order_UTIL.Get_Attribute_Name('TAX_EXEMPT_FLAG'));
73 OE_MSG_PUB.ADD;
74 END IF;
75
76
77 -- Fix bug 1262790
78 -- Ship To Org and Payment Term are required only on regular or
79 -- MIXED orders, NOT on RETURN orders
80
81 oe_debug_pub.add('p_header_rec.ship_to_org_id' || to_char(p_header_rec.ship_to_org_id),2);
82
83 IF p_header_rec.order_category_code <>
84 OE_GLOBALS.G_RETURN_CATEGORY_CODE THEN
85
86 IF p_header_rec.ship_to_org_id IS NULL
87 THEN
88 x_return_status := FND_API.G_RET_STS_ERROR;
89 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
90 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
91 OE_Order_UTIL.Get_Attribute_Name('SHIP_TO_ORG_ID'));
92 OE_MSG_PUB.ADD;
93 END IF;
94
95 IF p_header_rec.payment_term_id IS NULL
96 THEN
97 x_return_status := FND_API.G_RET_STS_ERROR;
98 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
99 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
100 OE_Order_UTIL.Get_Attribute_Name('PAYMENT_TERM_ID'));
101 OE_MSG_PUB.ADD;
102 END IF;
103
104 END IF;
105
106
107 -- Check for additional required fields based on flags set
108 -- at the order type: agreement, customer po number
109
110 l_order_type_rec := OE_Order_Cache.Load_Order_Type
111 (p_header_rec.order_type_id);
112
113 IF ( l_order_type_rec.agreement_required_flag = 'Y' AND
114 p_header_rec.agreement_id IS NULL)
115 THEN
116 x_return_status := FND_API.G_RET_STS_ERROR;
117 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
118 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
119 OE_Order_UTIL.Get_Attribute_Name('AGREEMENT_ID'));
120 OE_MSG_PUB.ADD;
121 END IF;
122
123 IF ( l_order_type_rec.require_po_flag = 'Y' AND
124 p_header_rec.cust_po_number IS NULL)
125 THEN
126 x_return_status := FND_API.G_RET_STS_ERROR;
127 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
128 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
129 OE_Order_UTIL.Get_Attribute_Name('CUST_PO_NUMBER'));
130 OE_MSG_PUB.ADD;
131 END IF;
132
133
134 -- Conversion Type Related Checks
135
136 -- IF SOB currency is dIFferent from order currency,
137 -- conversion type is required
138
139 IF p_header_rec.conversion_type_code IS NULL
140 THEN
141 l_set_of_books_rec := OE_Order_Cache.Load_Set_Of_Books;
142
143 IF ( l_set_of_books_rec.currency_code <>
144 p_header_rec.transactional_curr_code) THEN
145 x_return_status := FND_API.G_RET_STS_ERROR;
146 FND_MESSAGE.SET_NAME('ONT','OE_VAL_REQ_NON_BASE_CURRENCY');
147 FND_MESSAGE.SET_TOKEN
148 ('ORDER_CURRENCY',p_header_rec.transactional_curr_code);
149 FND_MESSAGE.SET_TOKEN('SOB_CURRENCY',l_set_of_books_rec.currency_code);
150 OE_MSG_PUB.ADD;
151 END IF;
152
153 -- IF conversion type is 'User', conversion rate AND conversion rate date
154 -- required.
155
156 ELSIF p_header_rec.conversion_type_code = 'User'
157 THEN
158
159 IF p_header_rec.conversion_rate IS NULL OR
160 p_header_rec.conversion_rate_date IS NULL
161 THEN
162 x_return_status := FND_API.G_RET_STS_ERROR;
163 FND_MESSAGE.SET_NAME('ONT','OE_VAL_USER_CONVERSION_TYPE');
164 OE_MSG_PUB.ADD;
165 END IF;
166
167 END IF; -- END of checks based on conversion type
168
169
170 -- Checks based on payment type attached to the order
171
172 IF p_header_rec.payment_type_code IS NOT NULL THEN
173
174 -- payment amount should be specIFied
175 -- only IF Payment Type is NOT Credit Card
176
177 IF p_header_rec.payment_type_code <> 'CREDIT_CARD' AND
178 p_header_rec.payment_amount IS NULL
179 THEN
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
182 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
183 OE_Order_UTIL.Get_Attribute_Name('PAYMENT_AMOUNT'));
184 OE_MSG_PUB.ADD;
185 END IF;
186
187 -- check number required IF payment type is Check
188
189 IF (p_header_rec.payment_type_code = 'CHECK' AND
190 p_header_rec.check_number IS NULL )
191 THEN
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 FND_MESSAGE.SET_NAME('ONT','OE_VAL_CHECK_NUM_REQD');
194 OE_MSG_PUB.ADD;
195 END IF;
196
197 -- credit card holder name, number AND expiration date
198 -- required for payment type of Credit Card
199
200 /*
201 ** Following Validation Moved to Authorize Credit Card
202 ** Payment Routine. Not required anymore at BOOKING.
203 IF p_header_rec.payment_type_code = 'CREDIT_CARD' THEN
204 IF p_header_rec.credit_card_holder_name IS NULL
205 OR p_header_rec.credit_card_number IS NULL
206 OR p_header_rec.credit_card_expiration_date IS NULL
207 THEN
208 x_return_status := FND_API.G_RET_STS_ERROR;
209 FND_MESSAGE.SET_NAME('ONT','OE_VAL_CREDIT_CARD_REQD');
210 OE_MSG_PUB.ADD;
211 END IF;
212 END IF;
213 */
214
215 END IF; -- END of checks related to payment type
216
217 oe_debug_pub.add('Exiting OE_CNCL_VALIDATE_HEADER.CHECK_BOOK_REQD',1);
218 EXCEPTION
219 WHEN OTHERS THEN
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
221
222 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
223 THEN
224 OE_MSG_PUB.Add_Exc_Msg
225 ( G_PKG_NAME ,
226 'Check_Book_Reqd_Attributes'
227 );
228 END IF;
229
230 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
231 END Check_Book_Reqd_Attributes;
232
233 ---------------------------------------------------------------
234 -- FUNCTION Is_Duplicate_PO_Number
235 -- Added to fix bug 1162304
236 -- Returns TRUE if the PO number is referenced on another order
237 -- for the same customer
238 ---------------------------------------------------------------
239
240 FUNCTION Is_Duplicate_PO_Number
241 ( p_cust_po_number IN VARCHAR2
242 , p_sold_to_org_id IN NUMBER
243 , p_header_id IN NUMBER
244 ) RETURN BOOLEAN
245 IS
246 l_duplicate_exists varchar2(1);
247 BEGIN
248
249 SELECT /* MOAC_SQL_NO_CHANGE */ 'Y'
250 INTO l_duplicate_exists
251 FROM DUAL
252 WHERE EXISTS (SELECT 'Y'
253 FROM OE_ORDER_HEADERS
254 WHERE HEADER_ID <> p_header_id
255 AND SOLD_TO_ORG_ID = p_sold_to_org_id
256 AND CUST_PO_NUMBER = p_cust_po_number )
257 OR EXISTS (SELECT 'Y'
258 FROM OE_ORDER_LINES
259 WHERE HEADER_ID <> p_header_id
260 AND SOLD_TO_ORG_ID = p_sold_to_org_id
261 AND CUST_PO_NUMBER = p_cust_po_number );
262
263 RETURN TRUE;
264
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 RETURN FALSE;
268
269 END Is_Duplicate_PO_Number;
270
271 /*-------------------------------------------------------
272 PROCEDURE: Entity
273 Description:
274 --------------------------------------------------------*/
275
276 PROCEDURE Entity
277 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
278 , p_header_rec IN OE_Order_PUB.Header_Rec_Type
279 )
280 IS
281 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
282 l_dummy VARCHAR2(10);
283 l_price_list_rec OE_Order_Cache.Price_List_Rec_Type;
284 -- l_order_type_rec OE_Order_Cache.Order_Type_Rec_Type;
285 -- L_agreement_rec OE_Order_Cache.Agreement_Rec_Type;
286
287 l_agreement_name varchar2(240);
288 l_sold_to_org number;
289 l_price_list_id number;
290 lcustomer_relations varchar2(1);
291 l_list_type_code VARCHAR2(30);
292 l_validate_result Varchar2(1):='N'; --15878235
293
294 -- eBTax Changes
295 l_ship_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
296 l_ship_to_party_id hz_cust_accounts.party_id%type;
297 l_ship_to_party_site_id hz_party_sites.party_site_id%type;
298 l_bill_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
299 l_bill_to_party_id hz_cust_accounts.party_id%type;
300 l_bill_to_party_site_id hz_party_sites.party_site_id%type;
301 l_org_id NUMBER;
302 -- l_legal_entity_id NUMBER;
303
304 cursor partyinfo(p_site_org_id HZ_CUST_SITE_USES_ALL.SITE_USE_ID%type) is
305 SELECT cust_acct.cust_account_id,
306 cust_Acct.party_id,
307 acct_site.party_site_id,
308 site_use.org_id
309 FROM
310 HZ_CUST_SITE_USES_ALL site_use,
311 HZ_CUST_ACCT_SITES_ALL acct_site,
312 HZ_CUST_ACCOUNTS_ALL cust_Acct
313 WHERE site_use.site_use_id = p_site_org_id
314 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
315 and acct_site.cust_account_id = cust_acct.cust_account_id;
316 -- end eBtax changes
317
318 --bug 4729536
319 Cursor Cur_Customer_Relations IS
320 SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
321 FROM oe_ship_to_orgs_v
322 WHERE site_use_id = p_header_rec.ship_to_org_id
323 AND customer_id = p_header_rec.sold_to_org_id
324 AND ROWNUM = 1
325
326 UNION ALL
327
328 SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
329 FROM oe_ship_to_orgs_v osov
330 WHERE site_use_id = p_header_rec.ship_to_org_id
331 AND EXISTS
332 (SELECT 1 FROM
333 HZ_CUST_ACCT_RELATE hcar
334 WHERE hcar.cust_account_id = osov.customer_id AND
335 hcar.related_cust_account_id = p_header_rec.sold_to_org_id
336 /* added the following condition to fix the bug 2002486 */
337 AND hcar.ship_to_flag = 'Y')
338 AND ROWNUM = 1;
339
340 Cursor Cur_customer_relations_inv IS
341 SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
342 FROM oe_invoice_to_orgs_v
343 WHERE site_use_id = p_header_rec.invoice_to_org_id
344 AND customer_id = p_header_rec.sold_to_org_id
345 AND ROWNUM = 1
346
347 UNION ALL
348
349 SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
350 FROM oe_invoice_to_orgs_v oito
351 WHERE oito.site_use_id = p_header_rec.invoice_to_org_id
352 AND EXISTS(
353 SELECT 1 FROM
354 HZ_CUST_ACCT_RELATE hcar WHERE
355 hcar.cust_account_id = oito.customer_id AND
356 hcar.related_cust_account_id = p_header_rec.sold_to_org_id
357 AND hcar.bill_to_flag = 'Y')
358 AND ROWNUM = 1;
359
360
361 BEGIN
362 oe_debug_pub.add('Enter OE_CNCL_VALIDATE_HEADER.ENTITY',1);
363
364 -- Check required attributes.
365 --lcustomer_relations := FND_PROFILE.VALUE('ONT_CUSTOMER_RELATIONSHIPS');
366 lcustomer_relations := OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG');
367
368
369
370
371 ----------------------------------------------------------
372 -- Check rest of required attributes here.
373 ----------------------------------------------------------
374
375
376 oe_debug_pub.add('p_header_rec.order_type_id' || p_header_rec.order_type_id, 2);
377
378 IF p_header_rec.order_type_id IS NULL
379 THEN
380 l_return_status := FND_API.G_RET_STS_ERROR;
381
382 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_ERROR)
383 THEN
384 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
385 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
386 OE_Order_UTIL.Get_Attribute_Name('ORDER_TYPE_ID'));
387 OE_MSG_PUB.Add;
388 END IF;
389 END IF;
390
391 --add messages
392
393
394 oe_debug_pub.add('p_header_rec.transactional_curr_code' || p_header_rec.transactional_curr_code, 2);
395
396 IF p_header_rec.transactional_curr_code IS NULL
397 THEN
398 l_return_status := FND_API.G_RET_STS_ERROR;
399
400 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_ERROR)
401 THEN
402 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
403 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
404 OE_Order_UTIL.Get_Attribute_Name('TRANSACTIONAL_CURR_CODE'));
405 OE_MSG_PUB.Add;
406 END IF;
407
408 END IF;
409
410
411 -- Return Error IF a required attribute is missing.
412 IF l_return_status = FND_API.G_RET_STS_ERROR
413 THEN
414 RAISE FND_API.G_EXC_ERROR;
415 END IF;
416
417
418 ----------------------------------------------------------
419 -- Check conditionally required attributes here.
420 ----------------------------------------------------------
421
422 -- Check attributes required for booked header
423
424
425 oe_debug_pub.add('p_header_rec.booked_flag' || p_header_rec.booked_flag, 2);
426
427
428 IF p_header_rec.booked_flag = 'Y' THEN
429
430 Check_Book_Reqd_Attributes
431 ( p_header_rec => p_header_rec
432 , x_return_status => l_return_status
433 );
434
435 END IF;
436
437 -- IF the Tax handling is "Exempt"
438
439 oe_debug_pub.add('p_header_rec.tax_exempt_flag' || p_header_rec.tax_exempt_flag, 2);
440
441 IF p_header_rec.tax_exempt_flag = 'E'
442 THEN
443 -- Check for Tax exempt reason
444 IF p_header_rec.tax_exempt_reason_code IS NULL OR
445 p_header_rec.tax_exempt_reason_code = FND_API.G_MISS_CHAR
446 THEN
447 l_return_status := FND_API.G_RET_STS_ERROR;
448
449 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_ERROR)
450 THEN
451 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
452 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
453 OE_Order_UTIL.Get_Attribute_Name('TAX_EXEMPT_REASON_CODE'));
454 OE_MSG_PUB.Add;
455 END IF;
456
457 END IF;
458
459 END IF; -- IF Tax handling is exempt
460
461 -- IF the TAX handling is STANDARD THEN we can not validate the
462 -- Tax Exempt Number as it can be a NULL value.
463
464 -- IF the Tax handling is "Required"
465 IF p_header_rec.tax_exempt_flag = 'R'
466 THEN
467
468 -- Check for Tax exempt number/Tax exempt reason.
469
470 IF (p_header_rec.tax_exempt_number IS NOT NULL AND
471 p_header_rec.tax_exempt_number <> FND_API.G_MISS_CHAR)
472 OR
473 (p_header_rec.tax_exempt_reason_code IS NOT NULL AND
474 p_header_rec.tax_exempt_reason_code <> FND_API.G_MISS_CHAR)
475 THEN
476 l_return_status := FND_API.G_RET_STS_ERROR;
477
478 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_ERROR)
479 THEN
480 fnd_message.set_name('ONT','OE_TAX_EXEMPTION_NOT_ALLOWED');
481 OE_MSG_PUB.Add;
482 END IF;
483 END IF;
484
485 END IF;
486
487 -- Return Error IF a conditionally required attribute is missing.
488
489 IF l_return_status = FND_API.G_RET_STS_ERROR
490 THEN
491 RAISE FND_API.G_EXC_ERROR;
492 END IF;
493
494
495 ----------------------------------------------------------------
496 -- VALIDATE ATTRIBUTE DEPENDENCIES
497 ----------------------------------------------------------------
498
499 oe_debug_pub.add('New price ' || p_header_rec.price_list_id,2);
500 oe_debug_pub.add('New curr ' || p_header_rec.transactional_curr_code,2);
501 -- Validate currency
502
503 l_price_list_rec :=
504 OE_Order_Cache.Load_Price_List ( p_header_rec.price_list_id );
505
506
507 oe_debug_pub.add('p_header_rec.price_list_id' || p_header_rec.price_list_id, 2);
508
509
510 IF p_header_rec.price_list_id IS NOT NULL
511 THEN
512
513 --15878235 start
514 BEGIN
515
516 QP_UTIL_PUB.Validate_Price_list_Curr_code(p_header_rec.price_list_id,
517 p_header_rec.transactional_curr_code,
518 p_header_rec.pricing_date,
519 l_validate_result);
520 EXCEPTION
521 WHEN OTHERS THEN
522
523 oe_debug_pub.add('OEXVCHDB: Error when calling QP_UTIL_PUB.Validate_Price_list_Curr_code:'||SQLERRM);
524
525 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
526 THEN
527
528 OE_MSG_PUB.Add_Exc_Msg
529 ( G_PKG_NAME ,
530 'OE_CNCL_VALIDATE_HEADER-QP_UTIL_PUB'
531 );
532 END IF;
533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
534
535 END;
536
537 oe_debug_pub.add('OEXVCHDB: Currency Validation Result:'||l_validate_result);
538 --15878235 end
539
540 /*15878235 start
541 IF p_header_rec.transactional_curr_code <>
542 l_price_list_rec.currency_code
543 THEN
544 15878235 end*/
545 IF l_validate_result = 'N' THEN --15878235
546 l_return_status := FND_API.G_RET_STS_ERROR;
547 fnd_message.set_name('ONT','OE_VAL_ORD_CURRENCY_MISMATCH');
548 FND_MESSAGE.SET_TOKEN('ORDER_CURRENCY',
549 p_header_rec.transactional_curr_code);
550 FND_MESSAGE.SET_TOKEN('PRICE_LIST_CURRENCY',
551 l_price_list_rec.currency_code);
552 OE_MSG_PUB.Add;
553 END IF; -- Currency Mismatch.
554
555 END IF; -- Price list or currency changed.
556
557 -- Currency_date, currency_rate should be null when type is null.
558
559
560 oe_debug_pub.add('p_header_rec.Conversion_type_code' || p_header_rec.Conversion_type_code, 2);
561
562 IF p_header_rec.Conversion_type_code IS NULL
563 THEN
564 IF Nvl(p_header_rec.conversion_rate, FND_API.G_MISS_NUM)
565 <> FND_API.G_MISS_NUM OR
566 Nvl(p_header_rec.conversion_rate_date, FND_API.G_MISS_DATE)
567 <> FND_API.G_MISS_DATE
568 THEN
569 l_return_status := FND_API.G_RET_STS_ERROR;
570 fnd_message.set_name('ONT','OE_VAL_CONVERSION_TYPE');
571 OE_MSG_PUB.Add;
572 END IF;
573
574 END IF;
575
576 -- made changes to the bug 3220059
577 -- Validation to check that conversion rate are null when type is not User
578
579 IF p_header_rec.conversion_type_code <> 'User' AND
580 p_header_rec.conversion_rate IS NOT NULL
581 THEN
582 l_return_status := FND_API.G_RET_STS_ERROR;
583 FND_MESSAGE.SET_NAME('ONT','OE_VALIDATION_CONV_TYPE');
584 OE_MSG_PUB.ADD;
585
586 END IF; -- END of checks based on conversion type
587
588 oe_debug_pub.add('p_header_rec.ordered_date' || p_header_rec.ordered_date, 2);
589
590 -- Order Type has to be valid
591 BEGIN
592
593 SELECT 'VALID'
594 INTO l_dummy
595 FROM OE_ORDER_TYPES_V
596 WHERE ORDER_TYPE_ID = p_header_rec.order_type_id
597 AND ROWNUM = 1;
598 --
599 -- Commented out when importing CLOSED orders
600 -- AND p_header_rec.ordered_date
601 -- BETWEEN NVL(START_DATE_ACTIVE,p_header_rec.ordered_date)
602 -- AND NVL( END_DATE_ACTIVE,p_header_rec.ordered_date);
603 -- Valid Order Type.
604
605 EXCEPTION
606 WHEN NO_DATA_FOUND THEN
607 l_return_status := FND_API.G_RET_STS_ERROR;
608 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
609 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
610 OE_Order_Util.Get_Attribute_Name('ORDER_TYPE_ID'));
611 OE_MSG_PUB.Add;
612
613 WHEN OTHERS THEN
614
615 IF OE_MSG_PUB.Check_Msg_Level
616 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
617 THEN
618
619 OE_MSG_PUB.Add_Exc_Msg
620 ( G_PKG_NAME ,
621 'Record - Order Type'
622 );
623 END IF;
624
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626 END;
627
628 -- Agreement depends on Order Type AND Sold To Org
629
630
631 oe_debug_pub.add('p_header_rec.agreement_id' || p_header_rec.agreement_id, 2);
632
633 IF p_header_rec.agreement_id IS NOT NULL THEN
634 -- commented by Geresh
635 -- l_agreement_rec :=
636 -- OE_Order_Cache.Load_Agreement (p_header_rec.agreement_id);
637
638 BEGIN
639 BEGIN
640 select list_type_code
641 into l_list_type_code
642 from qp_list_headers_vl
643 where list_header_id = p_header_rec.price_list_id;
644 EXCEPTION WHEN NO_DATA_FOUND THEN
645 null;
646 END;
647
648 IF NOT OE_GLOBALS.EQUAL(l_list_type_code,'PRL') THEN
649 -- any price list with 'PRL' type should be allowed to
650 -- be associated with any agreement according to bug 1386406.
651
652 select name ,sold_to_org_id , price_list_id
653 into l_agreement_name,l_sold_to_org,l_price_list_id
654 from oe_agreements_v
655 where agreement_id = p_header_rec.agreement_id
656 AND ROWNUM = 1;
657 -- Commented out when importing CLOSED orders
658 --
659 -- AND trunc(nvl(p_header_rec.pricing_date,sysdate)) between
660 -- trunc(nvl(START_DATE_ACTIVE,add_months(sysdate,-10000)))
661 -- AND trunc(nvl(END_DATE_ACTIVE,add_months(sysdate,+10000)));
662
663 -- Geresh added
664 IF l_price_list_id <> p_Header_rec.price_list_id
665 THEN
666 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT_PLIST');
667 fnd_message.set_Token
668 ('AGREEMENT_NAME', l_agreement_name || sqlerrm);
669 fnd_message.set_Token('PRICE_LIST1', p_Header_rec.price_list_id);
670 fnd_message.set_Token('PRICE_LIST2', l_price_list_id);
671 OE_MSG_PUB.Add;
672 oe_debug_pub.add('Invalid Agreement +price_list_id combination',2);
673 raise FND_API.G_EXC_ERROR;
674 END IF;
675 END IF;
676
677
678 EXCEPTION
679 WHEN NO_DATA_FOUND THEN
680 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT_PLIST');
681 fnd_message.set_Token('AGREEMENT_NAME', l_agreement_name);
682 fnd_message.set_Token('PRICE_LIST1', p_Header_rec.price_list_id);
683 fnd_message.set_Token('PRICE_LIST2', l_price_list_id || sqlerrm);
684 OE_MSG_PUB.Add;
685 oe_debug_pub.add
686 ('No Data Found Agreement+price_list_id combination',2);
687 raise FND_API.G_EXC_ERROR;
688 END;
689
690 -- l_order_type_rec :=
691 -- OE_Order_Cache.Load_Order_Type (p_header_rec.order_type_id);
692
693 END IF; -- Agreement is not null
694
695 -- Ship to Org id depends on sold to org.
696
697 oe_debug_pub.add('p_header_rec.ship_to_org_id' || p_header_rec.ship_to_org_id, 2);
698
699 IF p_header_rec.ship_to_org_id IS NOT NULL
700 THEN
701
702 BEGIN
703 oe_debug_pub.add
704 ('ship_to_org_id :'||to_char(p_header_rec.ship_to_org_id),2);
705 oe_debug_pub.add
706 ('Customer Relation :'||lcustomer_relations,2);
707
708 --lcustomer_relations := FND_PROFILE.VALUE('ONT_CUSTOMER_RELATIONSHIPS');
709
710 IF nvl(lcustomer_relations,'N') = 'N' THEN
711 oe_debug_pub.add
712 ('Cr: No',2);
713
714 SELECT 'VALID'
715 INTO l_dummy
716 FROM oe_ship_to_orgs_v
717 WHERE customer_id = p_header_rec.sold_to_org_id
718 AND site_use_id = p_header_rec.ship_to_org_id
719 AND ROWNUM = 1;
720 --
721 -- Commented out when importing CLOSED orders
722 --
723 -- AND status = 'A';
724
725 ELSIF lcustomer_relations = 'Y' THEN
726 oe_debug_pub.add
727 ('Cr: Yes',2);
728
729 /*Select /*MOAC_SQL_NO_CHANGE 'VALID'
730 Into l_dummy
731 From oe_ship_to_orgs_v
732 WHERE site_use_id = p_header_rec.ship_to_org_id
733 AND
734 customer_id in (
735 Select p_header_rec.sold_to_org_id from dual
736 union
737 select cust_account_id from
738 HZ_CUST_ACCT_RELATE
739 Where related_cust_account_id = p_header_rec.sold_to_org_id
740 /* added the following condition to fix the bug 2002486
741 and ship_to_flag = 'Y')*/
742
743 /* Replaced ra tables with HZ tables to fix the bug 1888440
744 and rownum = 1;*/
745
746 /* Replaced ra tables with HZ tables to fix the bug 1888440 */
747
748 --bug 4729536
749 OPEN cur_customer_relations;
750 Fetch cur_customer_relations into l_dummy;
751 Close cur_customer_relations;
752 --bug 4729536
753
754 -- Commented out when importing CLOSED orders
755 --
756 -- AND status = 'A'
757
758 /* added the following ELSIF condition to fix the bug 2002486 */
759
760 ELSIF nvl(lcustomer_relations,'N') = 'A' THEN
761 oe_debug_pub.add
762 ('Cr: A',2);
763
764 SELECT 'VALID'
765 INTO l_dummy
766 FROM oe_ship_to_orgs_v
767 WHERE site_use_id = p_header_rec.ship_to_org_id
768 AND ROWNUM = 1;
769
770
771 oe_debug_pub.add
772 ('Cr: Yes- After the select',2);
773
774 END IF;
775
776
777 -- Valid Ship To Org Id.
778
779 EXCEPTION
780
781 WHEN NO_DATA_FOUND THEN
782 oe_debug_pub.add
783 ('In: No data found',2);
784 l_return_status := FND_API.G_RET_STS_ERROR;
785 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
786 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', OE_Order_Util.Get_Attribute_Name
787 ('ship_to_org_id'));
788 OE_MSG_PUB.Add;
789
790 WHEN OTHERS THEN
791
792 IF OE_MSG_PUB.Check_Msg_Level
793 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
794 THEN
795 OE_MSG_PUB.Add_Exc_Msg
796 ( G_PKG_NAME ,
797 'Record - Ship To'
798 );
799 END IF;
800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
801
802 END;
803
804 END IF; -- Ship To needed validation.
805
806 -- Deliver to Org id depends on sold to org.
807 oe_debug_pub.add('p_header_rec.deliver_to_org_id'|| to_char(p_header_rec.deliver_to_org_id),2);
808 IF p_header_rec.deliver_to_org_id IS NOT NULL
809 THEN
810
811 BEGIN
812
813 IF nvl(lcustomer_relations,'N') = 'N' THEN
814
815 SELECT 'VALID'
816 INTO l_dummy
817 FROM oe_deliver_to_orgs_v
818 WHERE customer_id = p_header_rec.sold_to_org_id
819 AND site_use_id = p_header_rec.deliver_to_org_id
820 AND ROWNUM = 1;
821
822 ELSIF lcustomer_relations = 'Y' THEN
823
824 oe_debug_pub.add('Cr: Yes deliver',2);
825
826 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
827 Into l_dummy
828 FROM HZ_CUST_SITE_USES_ALL SITE,
829 HZ_CUST_ACCT_SITES ACCT_SITE
830 WHERE SITE.SITE_USE_ID = p_header_rec.deliver_to_org_id
831 AND SITE.SITE_USE_CODE ='DELIVER_TO'
832 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
833 AND ACCT_SITE.CUST_ACCOUNT_ID in (
834 SELECT p_header_rec.sold_to_org_id FROM DUAL
835 UNION
836 SELECT CUST_ACCOUNT_ID FROM
837 HZ_CUST_ACCT_RELATE_ALL R WHERE
838 R.ORG_ID = ACCT_SITE.ORG_ID
839 AND R.RELATED_CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id
840 and R.ship_to_flag = 'Y')
841 AND ROWNUM = 1;
842 oe_debug_pub.add('Cr: Yes- After the select',2);
843
844 ELSIF lcustomer_relations = 'A' THEN
845
846 SELECT 'VALID'
847 INTO l_dummy
848 FROM HZ_CUST_SITE_USES SITE
849 WHERE SITE.SITE_USE_ID =p_header_rec.deliver_to_org_id;
850
851
852 END IF;
853
854 EXCEPTION
855
856 WHEN NO_DATA_FOUND THEN
857 l_return_status := FND_API.G_RET_STS_ERROR;
858 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
859 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
860 OE_Order_Util.Get_Attribute_Name('deliver_to_org_id'));
861 OE_MSG_PUB.Add;
862
863 WHEN OTHERS THEN
864
865 IF OE_MSG_PUB.Check_Msg_Level
866 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
867 THEN
868
869 OE_MSG_PUB.Add_Exc_Msg
870 ( G_PKG_NAME ,
871 'Record - Deliver To'
872 );
873 END IF;
874
875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
876
877 END;
878
879 END IF; -- Deliver To needed validation.
880
881
882
883 -- Invoice to Org id depends on sold to org.
884
885
886 oe_debug_pub.add
887 ('invoice_to_org_id :'||to_char(p_header_rec.invoice_to_org_id),2);
888
889 IF p_header_rec.invoice_to_org_id IS NOT NULL
890 THEN
891 BEGIN
892 oe_debug_pub.add
893 ('invoice_to_org_id :'||to_char(p_header_rec.invoice_to_org_id),2);
894
895 IF nvl(lcustomer_relations,'N') = 'N' THEN
896
897 Select 'VALID'
898 Into l_dummy
899 From oe_invoice_to_orgs_v
900 Where customer_id = p_header_rec.sold_to_org_id
901 AND site_use_id = p_header_rec.invoice_to_org_id;
902 ELSIF lcustomer_relations = 'Y' THEN
903
904 /*Select /*MOAC_SQL_NO_CHANGE 'VALID'
905 Into l_dummy
906 From oe_invoice_to_orgs_v
907 WHERE site_use_id = p_header_rec.invoice_to_org_id
908 AND
909 customer_id in (
910 Select p_header_rec.sold_to_org_id from dual
911 union
912 select cust_account_id from
913 HZ_CUST_ACCT_RELATE where
914 related_cust_account_id = p_header_rec.sold_to_org_id
915 /* added the following condition to fix the bug 2002486
916 and bill_to_flag = 'Y')
917
918 and rownum = 1;*/
919 --bug 4729536
920 OPEN cur_customer_relations_inv;
921 Fetch cur_customer_relations_inv into l_dummy;
922 Close cur_customer_relations_inv;
923 --bug 4729536
924
925 /* Changed ra_customer_relationships to HZ Table to fix the bug 1888440 */
926
927 --
928 -- Commented out when importing CLOSED orders
929 --
930 -- AND status = 'A' AND
931
932 /* added the following ELSIF condition to fix the bug 2002486 */
933
934 ELSIF nvl(lcustomer_relations,'N') = 'A' THEN
935 oe_debug_pub.add
936 ('Cr: A',2);
937
938 SELECT 'VALID'
939 INTO l_dummy
940 From oe_invoice_to_orgs_v
941 WHERE site_use_id = p_header_rec.invoice_to_org_id
942 AND ROWNUM = 1;
943
944
945 END IF;
946
947
948 EXCEPTION
949
950 WHEN NO_DATA_FOUND THEN
951 l_return_status := FND_API.G_RET_STS_ERROR;
952 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
953 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
954 OE_Order_Util.Get_Attribute_Name('invoice_to_org_id'));
955 OE_MSG_PUB.Add;
956
957 WHEN OTHERS THEN
958
959 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
960 THEN
961 OE_MSG_PUB.Add_Exc_Msg
962 ( G_PKG_NAME ,
963 'Record - Invoice To'
964 );
965 END IF;
966
967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
968 END;
969
970 END IF; -- Invoice to org needed validation.
971
972 -- Customer Location depends on Sold To Org
973 oe_debug_pub.add('p_header_rec.sold_to_site_use_id'|| to_char(p_header_rec.sold_to_site_use_id),2);
974 IF p_header_rec.sold_to_site_use_id IS NOT NULL
975 THEN
976
977 BEGIN
978
979 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
980 INTO l_dummy
981 FROM
982 HZ_CUST_SITE_USES_ALL SITE,
983 HZ_CUST_ACCT_SITES ACCT_SITE
984 WHERE
985 SITE.SITE_USE_ID = p_header_rec.sold_to_site_use_id
986 AND SITE.SITE_USE_CODE = 'SOLD_TO'
987 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
988 AND ACCT_SITE.CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id;
989
990 EXCEPTION
991
992 WHEN NO_DATA_FOUND THEN
993 l_return_status := FND_API.G_RET_STS_ERROR;
994 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
995 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
996 OE_Order_Util.Get_Attribute_Name('SOLD_TO_SITE_USE_ID'));
997 OE_MSG_PUB.Add;
998
999 WHEN OTHERS THEN
1000 IF OE_MSG_PUB.Check_Msg_Level
1001 ( OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1002 THEN
1003 OE_MSG_PUB.Add_Exc_Msg
1004 ( G_PKG_NAME ,
1005 'Record - Customer Location'
1006 );
1007 END IF;
1008
1009 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1010
1011 END; -- BEGIN
1012
1013 END IF; --
1014 -- Sold to contact depends on Sold To Org
1015 oe_debug_pub.add('p_header_rec.sold_to_contact_id'|| to_char(p_header_rec.sold_to_contact_id),2);
1016 IF p_header_rec.sold_to_contact_id IS NOT NULL
1017 THEN
1018
1019 BEGIN
1020
1021 SELECT 'VALID'
1022 INTO l_dummy
1023 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
1024 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.sold_to_contact_id
1025 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1026 AND ACCT_ROLE.CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id
1027 AND ROWNUM = 1;
1028
1029 /* Replaced ra_contacts with HZ Table to fix the bug 1888440 */
1030
1031 -- Valid Sold To Contact
1032
1033 EXCEPTION
1034
1035 WHEN NO_DATA_FOUND THEN
1036 l_return_status := FND_API.G_RET_STS_ERROR;
1037 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1038 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1039 OE_Order_Util.Get_Attribute_Name('SOLD_TO_CONTACT_ID'));
1040 OE_MSG_PUB.Add;
1041
1042 WHEN OTHERS THEN
1043 IF OE_MSG_PUB.Check_Msg_Level
1044 ( OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1045 THEN
1046 OE_MSG_PUB.Add_Exc_Msg
1047 ( G_PKG_NAME ,
1048 'Record - Sold To Contact'
1049 );
1050 END IF;
1051
1052 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1053
1054 END; -- BEGIN
1055
1056 END IF; -- Sold to contact needed validation.
1057
1058
1059 -- Invoice to contact depends on Invoice To Org
1060 oe_debug_pub.add('p_header_rec.invoice_to_contact_id'|| to_char(p_header_rec.invoice_to_contact_id),2);
1061 IF p_header_rec.invoice_to_contact_id IS NOT NULL
1062 THEN
1063 BEGIN
1064 oe_debug_pub.add
1065 ('inv_to_contact :'||to_char(p_header_rec.invoice_to_contact_id),2);
1066
1067 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
1068 INTO l_dummy
1069 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
1070 , HZ_CUST_ACCT_SITES ACCT_SITE
1071 , HZ_CUST_SITE_USES_ALL INV
1072 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.invoice_to_contact_id
1073 AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
1074 AND ACCT_SITE.CUST_ACCT_SITE_ID = INV.CUST_ACCT_SITE_ID
1075 AND INV.SITE_USE_ID = p_header_rec.invoice_to_org_id
1076 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1077 AND ROWNUM = 1;
1078
1079 /* Replaced ra_contacts , ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440 */
1080
1081
1082 EXCEPTION
1083
1084 WHEN NO_DATA_FOUND THEN
1085
1086 l_return_status := FND_API.G_RET_STS_ERROR;
1087 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1088 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1089 OE_Order_Util.Get_Attribute_Name('INVOICE_TO_CONTACT_ID'));
1090 OE_MSG_PUB.Add;
1091
1092 WHEN OTHERS THEN
1093
1094 IF OE_MSG_PUB.Check_Msg_Level
1095 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1096 THEN
1097
1098 OE_MSG_PUB.Add_Exc_Msg
1099 ( G_PKG_NAME ,
1100 'Record - Invoice To Contact'
1101 );
1102 END IF;
1103
1104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1105
1106 END;
1107
1108 END IF; -- Invoice to contact needed validation.
1109
1110
1111 -- Ship to contact depends on Ship To Org
1112 oe_debug_pub.add('p_header_rec.ship_to_contact_id'|| to_char(p_header_rec.ship_to_contact_id),2);
1113 IF p_header_rec.ship_to_contact_id IS NOT NULL
1114 THEN
1115
1116 BEGIN
1117
1118 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
1119 INTO l_dummy
1120 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
1121 , HZ_CUST_ACCT_SITES ACCT_SITE
1122 , HZ_CUST_SITE_USES_ALL SHIP
1123 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.ship_to_contact_id
1124 AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
1125 AND ACCT_SITE.CUST_ACCT_SITE_ID = SHIP.CUST_ACCT_SITE_ID
1126 AND SHIP.SITE_USE_ID = p_header_rec.ship_to_org_id
1127 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1128 AND ROWNUM = 1;
1129
1130 /* Replace RA views with HZ views to fix the bug 1888440 */
1131 --
1132 -- Commented out when importing CLOSED orders
1133 --
1134 -- AND SHIP.STATUS = 'A'
1135
1136 -- Valid Ship To Contact
1137
1138 EXCEPTION
1139
1140 WHEN NO_DATA_FOUND THEN
1141 l_return_status := FND_API.G_RET_STS_ERROR;
1142 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1143 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1144 OE_Order_Util.Get_Attribute_Name('SHIP_TO_CONTACT_ID'));
1145 OE_MSG_PUB.Add;
1146
1147 WHEN OTHERS THEN
1148
1149 IF OE_MSG_PUB.Check_Msg_Level
1150 (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1151 THEN
1152
1153 OE_MSG_PUB.Add_Exc_Msg
1154 ( G_PKG_NAME ,
1155 'Record - Ship To Contact'
1156 );
1157 END IF;
1158
1159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1160
1161 END; -- BEGIN
1162
1163 END IF; -- Ship to contact needed validation.
1164
1165
1166 -- Deliver to contact depends on Deliver To Org
1167 oe_debug_pub.add('p_header_rec.deliver_to_contact_id' || to_char(p_header_rec.deliver_to_contact_id),2);
1168 IF p_header_rec.deliver_to_contact_id IS NOT NULL
1169 THEN
1170
1171 BEGIN
1172
1173 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
1174 INTO l_dummy
1175 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
1176 , HZ_CUST_ACCT_SITES ACCT_SITE
1177 , HZ_CUST_SITE_USES_ALL DELIVER
1178 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.deliver_to_contact_id
1179 AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
1180 AND ACCT_SITE.CUST_ACCT_SITE_ID = DELIVER.CUST_ACCT_SITE_ID
1181 AND DELIVER.SITE_USE_ID = p_header_rec.deliver_to_org_id
1182 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1183 AND ROWNUM = 1;
1184
1185 /* Replaced ra_contacts , ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440 */
1186
1187
1188 -- Valid Deliver To Org.
1189
1190 EXCEPTION
1191
1192 WHEN NO_DATA_FOUND THEN
1193 l_return_status := FND_API.G_RET_STS_ERROR;
1194 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1195 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1196 OE_Order_Util.Get_Attribute_Name('DELIVER_TO_CONTACT_ID'));
1197 OE_MSG_PUB.Add;
1198
1199 WHEN OTHERS THEN
1200
1201 IF OE_MSG_PUB.Check_Msg_Level (
1202 OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1203 THEN
1204 OE_MSG_PUB.Add_Exc_Msg
1205 ( G_PKG_NAME ,
1206 'Record - Deliver To Contact'
1207 );
1208 END IF;
1209
1210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1211
1212 END; -- BEGIN
1213
1214 END IF; -- Deliver to contact needed validation.
1215
1216
1217 -- Check for Tax Exempt number/Tax exempt reason code IF the Tax exempt
1218 -- flag is 'S' (StANDard).
1219 oe_debug_pub.add('p_header_rec.tax_exempt_number'|| p_header_rec.tax_exempt_number,2);
1220 oe_debug_pub.add('p_header_rec.tax_exempt_flag'|| p_header_rec.tax_exempt_flag,2);
1221 IF p_header_rec.tax_exempt_flag IS NOT NULL
1222 THEN
1223
1224 BEGIN
1225
1226 IF p_header_rec.tax_exempt_flag = 'S' AND
1227 p_header_rec.tax_exempt_number IS NOT NULL AND
1228 p_header_rec.tax_exempt_reason_code IS NOT NULL
1229 THEN
1230 -- eBTax changes
1231 /*SELECT 'VALID'
1232 INTO l_dummy
1233 FROM OE_TAX_EXEMPTIONS_QP_V
1234 WHERE TAX_EXEMPT_NUMBER = p_header_rec.tax_exempt_number
1235 AND TAX_EXEMPT_REASON_CODE=p_header_rec.tax_exempt_reason_code
1236 AND SHIP_TO_ORG_ID = nvl(p_header_rec.ship_to_org_id,
1237 p_header_rec.invoice_to_org_id)
1238 AND BILL_TO_CUSTOMER_ID = p_header_rec.sold_to_org_id
1239 AND ROWNUM = 1;
1240
1241 -- Commented out when importing CLOSED orders ?
1242 -- AND STATUS_CODE = 'PRIMARY'
1243 -- AND TRUNC(NVL(p_header_rec.request_date,sysdate)) BETWEEN
1244 -- TRUNC(START_DATE) AND
1245 -- TRUNC(NVL(END_DATE,NVL(p_header_rec.request_date,sysdate)))
1246 */
1247
1248 open partyinfo(p_header_rec.invoice_to_org_id);
1249 fetch partyinfo into l_bill_to_cust_Acct_id,
1250 l_bill_to_party_id,
1251 l_bill_to_party_site_id,
1252 l_org_id;
1253 close partyinfo;
1254
1255 if p_header_rec.ship_to_org_id = p_header_rec.invoice_to_org_id then
1256 l_ship_to_cust_Acct_id := l_bill_to_cust_Acct_id;
1257 l_ship_to_party_id := l_bill_to_party_id;
1258 l_ship_to_party_site_id := l_bill_to_party_site_id ;
1259 else
1260 open partyinfo(p_header_rec.ship_to_org_id);
1261 fetch partyinfo into l_ship_to_cust_Acct_id,
1262 l_ship_to_party_id,
1263 l_ship_to_party_site_id,
1264 l_org_id;
1265 close partyinfo;
1266 end if;
1267
1268
1269 SELECT 'VALID'
1270 INTO l_dummy
1271 FROM ZX_EXEMPTIONS_V
1272 WHERE EXEMPT_CERTIFICATE_NUMBER = p_header_rec.tax_exempt_number
1273 AND EXEMPT_REASON_CODE = p_header_rec.tax_exempt_reason_code
1274 AND nvl(site_use_id,nvl(p_header_rec.ship_to_org_id,
1275 p_header_rec.invoice_to_org_id))
1276 = nvl(p_header_rec.ship_to_org_id,
1277 p_header_rec.invoice_to_org_id)
1278 AND nvl(cust_account_id, l_bill_to_cust_acct_id) = l_bill_to_cust_acct_id
1279 AND nvl(PARTY_SITE_ID,nvl(l_ship_to_party_site_id, l_bill_to_party_site_id))=
1280 nvl(l_ship_to_party_site_id, l_bill_to_party_site_id)
1281 AND org_id = l_org_id
1282 AND party_id = l_bill_to_party_id
1283 -- AND nvl(LEGAL_ENTITY_ID,-99) IN (nvl(l_legal_entity_id, legal_entity_id), -99)
1284 AND EXEMPTION_STATUS_CODE = 'PRIMARY'
1285 -- AND TRUNC(NVL(p_header_rec.request_date,sysdate))
1286 -- BETWEEN TRUNC(EFFECTIVE_FROM)
1287 -- AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_header_rec.request_date,sysdate)))
1288 AND ROWNUM = 1;
1289
1290 END IF;
1291
1292 -- Valid Tax Exempt Number.
1293
1294 EXCEPTION
1295
1296 WHEN NO_DATA_FOUND THEN
1297
1298 l_return_status := FND_API.G_RET_STS_ERROR;
1299 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1300 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1301 OE_Order_Util.Get_Attribute_Name('TAX_EXEMPT_NUMBER'));
1302 OE_MSG_PUB.Add;
1303
1304 WHEN OTHERS THEN
1305
1306 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1307 THEN
1308
1309 OE_MSG_PUB.Add_Exc_Msg
1310 ( G_PKG_NAME ,
1311 'Record - Tax Exempt Number'
1312 );
1313 END IF;
1314
1315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1316
1317 END; -- BEGIN
1318
1319 END IF; -- Tax exempton info validation.
1320
1321
1322 -- Fix bug 1162304: issue a warning message if the PO number
1323 -- is being referenced by another order
1324 IF p_header_rec.cust_po_number IS NOT NULL
1325 THEN
1326
1327 IF OE_CNCL_Validate_Header.Is_Duplicate_PO_Number
1328 (p_header_rec.cust_po_number
1329 ,p_header_rec.sold_to_org_id
1330 ,p_header_rec.header_id )
1331 THEN
1332 FND_MESSAGE.SET_NAME('ONT','OE_VAL_DUP_PO_NUMBER');
1333 OE_MSG_PUB.ADD;
1334 END IF;
1335
1336 END IF;
1337 -- End of check for duplicate PO number
1338
1339
1340 -- Done validating entity
1341 x_return_status := l_return_status;
1342
1343 oe_debug_pub.add('Exit OE_CNCL_VALIDATE_HEADER.ENTITY',1);
1344
1345 EXCEPTION
1346
1347 WHEN FND_API.G_EXC_ERROR THEN
1348 x_return_status := FND_API.G_RET_STS_ERROR;
1349
1350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1351 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1352
1353 WHEN OTHERS THEN
1354 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1355
1356 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1357 THEN
1358 OE_MSG_PUB.Add_Exc_Msg
1359 ( G_PKG_NAME
1360 , 'Entity'
1361 );
1362 END IF;
1363
1364 END Entity;
1365
1366
1367
1368 /*-------------------------------------------------------
1369 PROCEDURE: Attributes
1370 Description:
1371 --------------------------------------------------------*/
1372
1373 PROCEDURE Attributes
1374 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1375 , p_x_header_rec IN OUT NOCOPY OE_Order_PUB.Header_Rec_Type
1376 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1377 )
1378 IS
1379 BEGIN
1380
1381 oe_debug_pub.add('Entering OE_CNCL_VALIDATE_HEADER.ATTRIBUTES',1);
1382
1383 x_return_status := FND_API.G_RET_STS_SUCCESS;
1384 -- validate Sales agreements Attributes
1385 IF p_x_header_rec.Blanket_number IS NOT NULL
1386 THEN
1387 x_return_status := FND_API.G_RET_STS_ERROR;
1388 fnd_message.set_name('ONT', 'OE_BLKT_DISALLOW_CLOSE_REL');
1389 OE_MSG_PUB.add;
1390 END IF;
1391
1392
1393 -- Validate header attributes
1394
1395 IF p_x_header_rec.accounting_rule_id IS NOT NULL
1396 THEN
1397
1398 IF NOT OE_CNCL_Validate.Accounting_Rule(p_x_header_rec.accounting_rule_id)
1399 THEN
1400
1401 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1402 --
1403 p_x_header_rec.accounting_rule_id := NULL;
1404 --
1405 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1406 --
1407 p_x_header_rec.accounting_rule_id := FND_API.G_MISS_NUM;
1408 --
1409 ELSE
1410 --
1411 x_return_status := FND_API.G_RET_STS_ERROR;
1412 --
1413 END IF;
1414
1415 END IF;
1416
1417 END IF;
1418
1419 IF p_x_header_rec.agreement_id IS NOT NULL THEN
1420 --
1421 IF NOT OE_CNCL_Validate.Agreement(p_x_header_rec.agreement_id) THEN
1422 --
1423 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1424 --
1425 p_x_header_rec.agreement_id := NULL;
1426 --
1427 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1428 --
1429 p_x_header_rec.agreement_id := FND_API.G_MISS_NUM;
1430 --
1431 ELSE
1432 --
1433 x_return_status := FND_API.G_RET_STS_ERROR;
1434 --
1435 END IF;
1436 --
1437 END IF;
1438 --
1439 END IF;
1440 --
1441
1442 IF p_x_header_rec.booked_flag IS NOT NULL THEN
1443 --
1444 IF NOT OE_CNCL_Validate.Booked(p_x_header_rec.booked_flag) THEN
1445 --
1446 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1447 --
1448 p_x_header_rec.booked_flag := NULL;
1449 --
1450 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1451 --
1452 p_x_header_rec.booked_flag := FND_API.G_MISS_CHAR;
1453 --
1454 ELSE
1455 --
1456 x_return_status := FND_API.G_RET_STS_ERROR;
1457 --
1458 END IF;
1459 --
1460 END IF;
1461 --
1462 END IF;
1463 --
1464
1465 IF p_x_header_rec.cancelled_flag IS NOT NULL THEN
1466 --
1467 IF NOT OE_CNCL_Validate.Cancelled(p_x_header_rec.cancelled_flag) THEN
1468 --
1469 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1470 --
1471 p_x_header_rec.cancelled_flag := NULL;
1472 --
1473 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1474 --
1475 p_x_header_rec.cancelled_flag := FND_API.G_MISS_CHAR;
1476 --
1477 ELSE
1478 --
1479 x_return_status := FND_API.G_RET_STS_ERROR;
1480 --
1481 END IF;
1482 --
1483 END IF;
1484 --
1485 END IF;
1486 --
1487
1488 IF p_x_header_rec.conversion_type_code IS NOT NULL THEN
1489 --
1490 IF NOT OE_CNCL_Validate.Conversion_Type(p_x_header_rec.conversion_type_code) THEN
1491 --
1492 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1493 --
1494 p_x_header_rec.conversion_type_code := NULL;
1495 --
1496 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1497 --
1498 p_x_header_rec.conversion_type_code := FND_API.G_MISS_CHAR;
1499 --
1500 ELSE
1501 --
1502 x_return_status := FND_API.G_RET_STS_ERROR;
1503 --
1504 END IF;
1505 --
1506 END IF;
1507 --
1508 END IF;
1509 --
1510
1511 IF p_x_header_rec.deliver_to_contact_id IS NOT NULL THEN
1512 --
1513 IF NOT OE_CNCL_Validate.Deliver_To_Contact(p_x_header_rec.deliver_to_contact_id) THEN
1514 --
1515 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1516 --
1517 p_x_header_rec.deliver_to_contact_id := NULL;
1518 --
1519 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1520 --
1521 p_x_header_rec.deliver_to_contact_id := FND_API.G_MISS_NUM;
1522 --
1523 ELSE
1524 --
1525 x_return_status := FND_API.G_RET_STS_ERROR;
1526 --
1527 END IF;
1528 --
1529 END IF;
1530 --
1531 END IF;
1532 --
1533
1534 --
1535 IF p_x_header_rec.deliver_to_org_id IS NOT NULL THEN
1536 --
1537 IF NOT OE_CNCL_Validate.Deliver_To_Org(p_x_header_rec.deliver_to_org_id) THEN
1538 --
1539 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1540 --
1541 p_x_header_rec.deliver_to_org_id := NULL;
1542 --
1543 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1544 --
1545 p_x_header_rec.deliver_to_org_id := FND_API.G_MISS_NUM;
1546 --
1547 ELSE
1548 --
1549 x_return_status := FND_API.G_RET_STS_ERROR;
1550 --
1551 END IF;
1552 --
1553 END IF;
1554 --
1555 END IF;
1556 --
1557
1558 --
1559 IF p_x_header_rec.demand_class_code IS NOT NULL THEN
1560 --
1561 IF NOT OE_CNCL_Validate.Demand_Class(p_x_header_rec.demand_class_code) THEN
1562 --
1563 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1564 --
1565 p_x_header_rec.demAND_class_code := NULL;
1566 --
1567 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1568 --
1569 p_x_header_rec.demAND_class_code := FND_API.G_MISS_CHAR;
1570 --
1571 ELSE
1572 --
1573 x_return_status := FND_API.G_RET_STS_ERROR;
1574 --
1575 END IF;
1576 --
1577 END IF;
1578 --
1579 END IF;
1580 --
1581
1582 --
1583 IF p_x_header_rec.fob_point_code IS NOT NULL THEN
1584 --
1585 IF NOT OE_CNCL_Validate.Fob_Point(p_x_header_rec.fob_point_code) THEN
1586 --
1587 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1588 --
1589 p_x_header_rec.fob_point_code := NULL;
1590 --
1591 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1592 --
1593 p_x_header_rec.fob_point_code := FND_API.G_MISS_CHAR;
1594 --
1595 ELSE
1596 --
1597 x_return_status := FND_API.G_RET_STS_ERROR;
1598 --
1599 END IF;
1600 --
1601 END IF;
1602 --
1603 END IF;
1604 --
1605
1606 --
1607 IF p_x_header_rec.freight_terms_code IS NOT NULL THEN
1608 --
1609 IF NOT OE_CNCL_Validate.Freight_Terms(p_x_header_rec.freight_terms_code) THEN
1610 --
1611 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1612 --
1613 p_x_header_rec.freight_terms_code := NULL;
1614 --
1615 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1616 --
1617 p_x_header_rec.freight_terms_code := FND_API.G_MISS_CHAR;
1618 --
1619 ELSE
1620 --
1621 x_return_status := FND_API.G_RET_STS_ERROR;
1622 --
1623 END IF;
1624 --
1625 END IF;
1626 --
1627 END IF;
1628 --
1629
1630 --
1631 IF p_x_header_rec.invoice_to_contact_id IS NOT NULL THEN
1632 --
1633 IF NOT OE_CNCL_Validate.Invoice_To_Contact(p_x_header_rec.invoice_to_contact_id) THEN
1634 --
1635 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1636 --
1637 p_x_header_rec.invoice_to_contact_id := NULL;
1638 --
1639 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1640 --
1641 p_x_header_rec.invoice_to_contact_id := FND_API.G_MISS_NUM;
1642 --
1643 ELSE
1644 --
1645 x_return_status := FND_API.G_RET_STS_ERROR;
1646 --
1647 END IF;
1648 --
1649 END IF;
1650 --
1651 END IF;
1652 --
1653
1654 --
1655 IF p_x_header_rec.invoice_to_org_id IS NOT NULL THEN
1656 --
1657 IF NOT OE_CNCL_Validate.Invoice_To_Org(p_x_header_rec.invoice_to_org_id) THEN
1658 --
1659 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1660 --
1661 p_x_header_rec.invoice_to_org_id := NULL;
1662 --
1663 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1664 --
1665 p_x_header_rec.invoice_to_org_id := FND_API.G_MISS_NUM;
1666 --
1667 ELSE
1668 --
1669 x_return_status := FND_API.G_RET_STS_ERROR;
1670 --
1671 END IF;
1672 --
1673 END IF;
1674 --
1675 END IF;
1676 --
1677
1678 --
1679 IF p_x_header_rec.invoicing_rule_id IS NOT NULL THEN
1680 --
1681 IF NOT OE_CNCL_Validate.Invoicing_Rule(p_x_header_rec.invoicing_rule_id) THEN
1682 --
1683 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1684 --
1685 p_x_header_rec.invoicing_rule_id := NULL;
1686 --
1687 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1688 --
1689 p_x_header_rec.invoicing_rule_id := FND_API.G_MISS_NUM;
1690 --
1691 ELSE
1692 --
1693 x_return_status := FND_API.G_RET_STS_ERROR;
1694 --
1695 END IF;
1696 --
1697 END IF;
1698 --
1699 END IF;
1700 --
1701
1702 --
1703 IF p_x_header_rec.open_flag IS NOT NULL THEN
1704 --
1705 IF NOT OE_CNCL_Validate.Open(p_x_header_rec.open_flag) THEN
1706 --
1707 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1708 --
1709 p_x_header_rec.open_flag := NULL;
1710 --
1711 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1712 --
1713 p_x_header_rec.open_flag := FND_API.G_MISS_CHAR;
1714 --
1715 ELSE
1716 --
1717 x_return_status := FND_API.G_RET_STS_ERROR;
1718 --
1719 END IF;
1720 --
1721 END IF;
1722 --
1723 END IF;
1724 --
1725
1726 --
1727 IF p_x_header_rec.order_date_type_code IS NOT NULL THEN
1728 --
1729 IF NOT OE_CNCL_Validate.Order_Date_Type_Code(p_x_header_rec.order_date_type_code) THEN
1730 --
1731 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1732 --
1733 p_x_header_rec.order_date_type_code := NULL;
1734 --
1735 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1736 --
1737 p_x_header_rec.order_date_type_code := FND_API.G_MISS_CHAR;
1738 --
1739 ELSE
1740 --
1741 x_return_status := FND_API.G_RET_STS_ERROR;
1742 --
1743 END IF;
1744 --
1745 END IF;
1746 --
1747 END IF;
1748 --
1749
1750 --
1751 IF p_x_header_rec.order_type_id IS NOT NULL THEN
1752 --
1753 IF NOT OE_CNCL_Validate.Order_Type(p_x_header_rec.order_type_id) THEN
1754 --
1755 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1756 --
1757 p_x_header_rec.order_type_id := NULL;
1758 --
1759 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1760 --
1761 p_x_header_rec.order_type_id := FND_API.G_MISS_NUM;
1762 --
1763 ELSE
1764 --
1765 x_return_status := FND_API.G_RET_STS_ERROR;
1766 --
1767 END IF;
1768 --
1769 END IF;
1770 --
1771 END IF;
1772 --
1773
1774 --
1775 IF p_x_header_rec.payment_term_id IS NOT NULL THEN
1776 --
1777 IF NOT OE_CNCL_Validate.Payment_Term(p_x_header_rec.payment_term_id) THEN
1778 --
1779 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1780 --
1781 p_x_header_rec.payment_term_id := NULL;
1782 --
1783 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1784 --
1785 p_x_header_rec.payment_term_id := FND_API.G_MISS_NUM;
1786 --
1787 ELSE
1788 --
1789 x_return_status := FND_API.G_RET_STS_ERROR;
1790 --
1791 END IF;
1792 --
1793 END IF;
1794 --
1795 END IF;
1796 --
1797
1798 --{added for bug 4240715
1799
1800 IF p_x_header_rec.Ib_owner IS NOT NULL THEN
1801
1802 IF NOT OE_CNCL_Validate.IB_OWNER (p_x_header_rec.Ib_owner)
1803 THEN
1804 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1805
1806 p_x_header_rec.Ib_owner := NULL;
1807
1808 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
1809 THEN
1810 p_x_header_rec.Ib_owner := FND_API.G_MISS_CHAR;
1811 ELSE
1812 x_return_status := FND_API.G_RET_STS_ERROR;
1813 END IF;
1814
1815 END IF;
1816
1817 END IF;
1818
1819 IF p_x_header_rec.Ib_installed_at_location IS NOT NULL
1820 THEN
1821
1822 IF NOT OE_CNCL_Validate.IB_INSTALLED_AT_LOCATION (p_x_header_rec.Ib_installed_at_location)
1823 THEN
1824 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
1825 THEN
1826 p_x_header_rec.Ib_installed_at_location := NULL;
1827 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
1828 THEN
1829 p_x_header_rec.Ib_installed_at_location := FND_API.G_MISS_CHAR;
1830 ELSE
1831 x_return_status := FND_API.G_RET_STS_ERROR;
1832 END IF;
1833
1834 END IF;
1835
1836 END IF;
1837
1838 IF p_x_header_rec.Ib_current_location IS NOT NULL
1839 THEN
1840
1841 IF NOT OE_CNCL_Validate.IB_CURRENT_LOCATION (p_x_header_rec.Ib_current_location)
1842 THEN
1843 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
1844 THEN
1845 p_x_header_rec.Ib_current_location := NULL;
1846 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
1847 THEN
1848 p_x_header_rec.Ib_current_location := FND_API.G_MISS_CHAR;
1849 ELSE
1850 x_return_status := FND_API.G_RET_STS_ERROR;
1851 END IF;
1852
1853 END IF;
1854
1855 END IF;
1856
1857 IF p_x_header_rec.End_customer_id IS NOT NULL THEN
1858
1859 IF NOT OE_CNCL_Validate.END_CUSTOMER (p_x_header_rec.End_customer_id)
1860 THEN
1861 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
1862
1863 THEN
1864 p_x_header_rec.End_customer_id := NULL;
1865 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
1866 THEN
1867 p_x_header_rec.End_customer_id := FND_API.G_MISS_NUM;
1868 ELSE
1869 x_return_status := FND_API.G_RET_STS_ERROR;
1870 END IF;
1871
1872 END IF;
1873
1874 END IF;
1875
1876 IF p_x_header_rec.End_customer_contact_id IS NOT NULL
1877 THEN
1878 IF NOT OE_CNCL_Validate.END_CUSTOMER_CONTACT (p_x_header_rec.End_customer_contact_id)
1879 THEN
1880 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
1881 THEN
1882 p_x_header_rec.End_customer_contact_id := NULL;
1883 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
1884 THEN
1885 p_x_header_rec.End_customer_contact_id := FND_API.G_MISS_NUM;
1886 ELSE
1887 x_return_status := FND_API.G_RET_STS_ERROR;
1888 END IF;
1889
1890 END IF;
1891
1892 END IF;
1893
1894 IF p_x_header_rec.End_customer_site_use_id IS NOT NULL
1895 THEN
1896
1897 IF NOT OE_CNCL_Validate.END_CUSTOMER_SITE_USE (p_x_header_rec.End_customer_site_use_id)
1898 THEN
1899 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
1900 THEN
1901 p_x_header_rec.End_customer_site_use_id := NULL;
1902 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
1903 THEN
1904 p_x_header_rec.End_customer_site_use_id := FND_API.G_MISS_NUM;
1905 ELSE
1906 x_return_status := FND_API.G_RET_STS_ERROR;
1907 END IF;
1908
1909 END IF;
1910
1911 END IF;
1912
1913 --bug 4240715}
1914
1915 --
1916 IF p_x_header_rec.price_list_id IS NOT NULL THEN
1917 --
1918 IF NOT OE_CNCL_Validate.Price_List(p_x_header_rec.price_list_id) THEN
1919 --
1920 p_x_header_rec.price_list_id := NULL;
1921 --
1922 ELSE
1923 --
1924 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1925 --
1926 x_return_status := FND_API.G_RET_STS_ERROR;
1927 --
1928 END IF;
1929 --
1930 END IF;
1931 --
1932 END IF;
1933 --
1934
1935 --
1936 IF p_x_header_rec.shipment_priority_code IS NOT NULL THEN
1937 --
1938 IF NOT OE_CNCL_Validate.Shipment_Priority(p_x_header_rec.shipment_priority_code) THEN
1939 --
1940 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1941 --
1942 p_x_header_rec.shipment_priority_code := NULL;
1943 --
1944 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1945 --
1946 p_x_header_rec.shipment_priority_code := FND_API.G_MISS_CHAR;
1947 --
1948 ELSE
1949 --
1950 x_return_status := FND_API.G_RET_STS_ERROR;
1951 --
1952 END IF;
1953 --
1954 END IF;
1955 --
1956 END IF;
1957 --
1958
1959 --
1960 IF p_x_header_rec.shipping_method_code IS NOT NULL THEN
1961 --
1962 IF NOT OE_CNCL_Validate.Shipping_Method(p_x_header_rec.shipping_method_code) THEN
1963 --
1964 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1965 --
1966 p_x_header_rec.shipping_method_code := NULL;
1967 --
1968 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1969 --
1970 p_x_header_rec.shipping_method_code := FND_API.G_MISS_CHAR;
1971 --
1972 ELSE
1973 --
1974 x_return_status := FND_API.G_RET_STS_ERROR;
1975 --
1976 END IF;
1977 --
1978 END IF;
1979 --
1980 END IF;
1981 --
1982
1983 --
1984 IF p_x_header_rec.ship_from_org_id IS NOT NULL THEN
1985 --
1986 IF NOT OE_CNCL_Validate.Ship_From_Org(p_x_header_rec.ship_from_org_id) THEN
1987 --
1988 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
1989 --
1990 p_x_header_rec.ship_from_org_id := NULL;
1991 --
1992 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
1993 --
1994 p_x_header_rec.ship_from_org_id := FND_API.G_MISS_NUM;
1995 --
1996 ELSE
1997 --
1998 x_return_status := FND_API.G_RET_STS_ERROR;
1999 --
2000 END IF;
2001 --
2002 END IF;
2003 --
2004 END IF;
2005 --
2006
2007 --
2008 IF p_x_header_rec.ship_to_contact_id IS NOT NULL THEN
2009 --
2010 IF NOT OE_CNCL_Validate.Ship_To_Contact(p_x_header_rec.ship_to_contact_id) THEN
2011 --
2012 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2013 --
2014 p_x_header_rec.ship_to_contact_id := NULL;
2015 --
2016 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2017 --
2018 p_x_header_rec.ship_to_contact_id := FND_API.G_MISS_NUM;
2019 --
2020 ELSE
2021 --
2022 x_return_status := FND_API.G_RET_STS_ERROR;
2023 --
2024 END IF;
2025 --
2026 END IF;
2027 --
2028 END IF;
2029 --
2030
2031 --
2032 IF p_x_header_rec.ship_to_org_id IS NOT NULL THEN
2033 --
2034 IF NOT OE_CNCL_Validate.Ship_To_Org(p_x_header_rec.ship_to_org_id) THEN
2035 --
2036 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2037 --
2038 p_x_header_rec.ship_to_org_id := NULL;
2039 --
2040 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2041 --
2042 p_x_header_rec.ship_to_org_id := FND_API.G_MISS_NUM;
2043 --
2044 ELSE
2045 --
2046 x_return_status := FND_API.G_RET_STS_ERROR;
2047 --
2048 END IF;
2049 --
2050 END IF;
2051 --
2052 END IF;
2053 --
2054
2055 --
2056 IF p_x_header_rec.sold_to_contact_id IS NOT NULL THEN
2057 --
2058 IF NOT OE_CNCL_Validate.Sold_To_Contact(p_x_header_rec.sold_to_contact_id) THEN
2059 --
2060 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2061 --
2062 p_x_header_rec.sold_to_contact_id := NULL;
2063 --
2064 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2065 --
2066 p_x_header_rec.sold_to_contact_id := FND_API.G_MISS_NUM;
2067 --
2068 ELSE
2069 --
2070 x_return_status := FND_API.G_RET_STS_ERROR;
2071 --
2072 END IF;
2073 --
2074 END IF;
2075 --
2076 END IF;
2077 --
2078
2079
2080 IF p_x_header_rec.sold_to_org_id IS NOT NULL THEN
2081 --
2082 IF NOT OE_CNCL_Validate.Sold_To_Org(p_x_header_rec.sold_to_org_id) THEN
2083 --
2084 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2085 --
2086 p_x_header_rec.sold_to_org_id := NULL;
2087 --
2088 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2089 --
2090 p_x_header_rec.sold_to_org_id := FND_API.G_MISS_NUM;
2091 --
2092 ELSE
2093 --
2094 x_return_status := FND_API.G_RET_STS_ERROR;
2095 --
2096 END IF;
2097 --
2098 END IF;
2099 --
2100 END IF;
2101 --
2102
2103 --
2104 IF p_x_header_rec.source_document_type_id IS NOT NULL THEN
2105 --
2106 IF NOT OE_CNCL_Validate.Source_Document_Type
2107 (p_x_header_rec.source_document_type_id) THEN
2108 --
2109 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2110 --
2111 p_x_header_rec.source_document_type_id := NULL;
2112 --
2113 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2114 --
2115 p_x_header_rec.source_document_type_id := FND_API.G_MISS_NUM;
2116 --
2117 ELSE
2118 --
2119 x_return_status := FND_API.G_RET_STS_ERROR;
2120 --
2121 END IF;
2122 --
2123 END IF;
2124 --
2125 END IF;
2126 --
2127
2128 --
2129 IF p_x_header_rec.tax_exempt_flag IS NOT NULL THEN
2130 --
2131 IF NOT OE_CNCL_Validate.Tax_Exempt(p_x_header_rec.tax_exempt_flag) THEN
2132 --
2133 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2134 --
2135 p_x_header_rec.tax_exempt_flag := NULL;
2136 --
2137 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2138 --
2139 p_x_header_rec.tax_exempt_flag := FND_API.G_MISS_CHAR;
2140 --
2141 ELSE
2142 --
2143 x_return_status := FND_API.G_RET_STS_ERROR;
2144 --
2145 END IF;
2146 --
2147 END IF;
2148 --
2149 END IF;
2150 --
2151
2152 --
2153 IF p_x_header_rec.tax_exempt_reason_code IS NOT NULL THEN
2154 --
2155 IF NOT OE_CNCL_Validate.Tax_Exempt_Reason
2156 (p_x_header_rec.tax_exempt_reason_code) THEN
2157 --
2158 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2159 --
2160 p_x_header_rec.tax_exempt_reason_code := NULL;
2161 --
2162 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2163 --
2164 p_x_header_rec.tax_exempt_reason_code := FND_API.G_MISS_CHAR;
2165 --
2166 ELSE
2167 --
2168 x_return_status := FND_API.G_RET_STS_ERROR;
2169 --
2170 END IF;
2171 --
2172 END IF;
2173 --
2174 END IF;
2175 --
2176
2177 --
2178 IF p_x_header_rec.tax_point_code IS NOT NULL THEN
2179 --
2180 IF NOT OE_CNCL_Validate.Tax_Point(p_x_header_rec.tax_point_code) THEN
2181 --
2182 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2183 --
2184 p_x_header_rec.tax_point_code := NULL;
2185 --
2186 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2187 --
2188 p_x_header_rec.tax_point_code := FND_API.G_MISS_CHAR;
2189 --
2190 ELSE
2191 --
2192 x_return_status := FND_API.G_RET_STS_ERROR;
2193 --
2194 END IF;
2195 --
2196 END IF;
2197 --
2198 END IF;
2199 --
2200
2201 --
2202 IF p_x_header_rec.transactional_curr_code IS NOT NULL THEN
2203 --
2204 IF NOT OE_CNCL_Validate.Transactional_Curr
2205 (p_x_header_rec.transactional_curr_code) THEN
2206 --
2207 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2208 --
2209 p_x_header_rec.transactional_curr_code := NULL;
2210 --
2211 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2212 --
2213 p_x_header_rec.transactional_curr_code := FND_API.G_MISS_CHAR;
2214 --
2215 ELSE
2216 --
2217 x_return_status := FND_API.G_RET_STS_ERROR;
2218 --
2219 END IF;
2220 --
2221 END IF;
2222 --
2223 END IF;
2224 --
2225
2226 --
2227 IF p_x_header_rec.payment_type_code IS NOT NULL THEN
2228 --
2229 IF NOT OE_CNCL_Validate.Payment_Type(p_x_header_rec.payment_type_code) THEN
2230 --
2231 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2232 --
2233 p_x_header_rec.payment_type_code := NULL;
2234 --
2235 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2236 --
2237 p_x_header_rec.payment_type_code := FND_API.G_MISS_CHAR;
2238 --
2239 ELSE
2240 --
2241 x_return_status := FND_API.G_RET_STS_ERROR;
2242 --
2243 END IF;
2244 --
2245 END IF;
2246 --
2247 END IF;
2248 --
2249
2250 --
2251 IF p_x_header_rec.credit_card_code IS NOT NULL THEN
2252 --
2253 IF NOT OE_CNCL_Validate.Credit_Card(p_x_header_rec.credit_card_code) THEN
2254 --
2255 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2256 --
2257 p_x_header_rec.credit_card_code := NULL;
2258 --
2259 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2260 --
2261 p_x_header_rec.credit_card_code := FND_API.G_MISS_CHAR;
2262 --
2263 ELSE
2264 --
2265 x_return_status := FND_API.G_RET_STS_ERROR;
2266 --
2267 END IF;
2268 --
2269 END IF;
2270 --
2271 END IF;
2272 --
2273
2274 --
2275 IF p_x_header_rec.flow_status_code IS NOT NULL THEN
2276 --
2277 IF NOT OE_CNCL_Validate.Flow_Status(p_x_header_rec.flow_status_code) THEN
2278 --
2279 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2280 --
2281 p_x_header_rec.flow_status_code := NULL;
2282 --
2283 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2284 --
2285 p_x_header_rec.flow_status_code := FND_API.G_MISS_CHAR;
2286 --
2287 ELSE
2288 --
2289 x_return_status := FND_API.G_RET_STS_ERROR;
2290 --
2291 END IF;
2292 --
2293 END IF;
2294 --
2295 END IF;
2296 --
2297
2298 --
2299 IF p_x_header_rec.attribute1 IS NOT NULL
2300 OR p_x_header_rec.attribute10 IS NOT NULL
2301 OR p_x_header_rec.attribute11 IS NOT NULL
2302 OR p_x_header_rec.attribute12 IS NOT NULL
2303 OR p_x_header_rec.attribute13 IS NOT NULL
2304 OR p_x_header_rec.attribute14 IS NOT NULL
2305 OR p_x_header_rec.attribute15 IS NOT NULL
2306 OR p_x_header_rec.attribute16 IS NOT NULL --For bug 2184255
2307 OR p_x_header_rec.attribute17 IS NOT NULL
2308 OR p_x_header_rec.attribute18 IS NOT NULL
2309 OR p_x_header_rec.attribute19 IS NOT NULL
2310 OR p_x_header_rec.attribute2 IS NOT NULL
2311 OR p_x_header_rec.attribute20 IS NOT NULL
2312 OR p_x_header_rec.attribute3 IS NOT NULL
2313 OR p_x_header_rec.attribute4 IS NOT NULL
2314 OR p_x_header_rec.attribute5 IS NOT NULL
2315 OR p_x_header_rec.attribute6 IS NOT NULL
2316 OR p_x_header_rec.attribute7 IS NOT NULL
2317 OR p_x_header_rec.attribute8 IS NOT NULL
2318 OR p_x_header_rec.attribute9 IS NOT NULL
2319 OR p_x_header_rec.context IS NOT NULL
2320 THEN
2321 --
2322 oe_debug_pub.add('Before calling header_desc_flex',2);
2323 IF NOT OE_CNCL_VALIDATE.Header_Desc_Flex
2324 (p_context => p_x_header_rec.context
2325 ,p_attribute1 => p_x_header_rec.attribute1
2326 ,p_attribute2 => p_x_header_rec.attribute2
2327 ,p_attribute3 => p_x_header_rec.attribute3
2328 ,p_attribute4 => p_x_header_rec.attribute4
2329 ,p_attribute5 => p_x_header_rec.attribute5
2330 ,p_attribute6 => p_x_header_rec.attribute6
2331 ,p_attribute7 => p_x_header_rec.attribute7
2332 ,p_attribute8 => p_x_header_rec.attribute8
2333 ,p_attribute9 => p_x_header_rec.attribute9
2334 ,p_attribute10 => p_x_header_rec.attribute10
2335 ,p_attribute11 => p_x_header_rec.attribute11
2336 ,p_attribute12 => p_x_header_rec.attribute12
2337 ,p_attribute13 => p_x_header_rec.attribute13
2338 ,p_attribute14 => p_x_header_rec.attribute14
2339 ,p_attribute15 => p_x_header_rec.attribute15
2340 ,p_attribute16 => p_x_header_rec.attribute16 -- for bug 2184255
2341 ,p_attribute17 => p_x_header_rec.attribute17
2342 ,p_attribute18 => p_x_header_rec.attribute18
2343 ,p_attribute19 => p_x_header_rec.attribute19
2344 ,p_attribute20 => p_x_header_rec.attribute20)
2345 THEN
2346
2347 --
2348 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2349 --
2350 p_x_header_rec.context := null;
2351 p_x_header_rec.attribute1 := null;
2352 p_x_header_rec.attribute2 := null;
2353 p_x_header_rec.attribute3 := null;
2354 p_x_header_rec.attribute4 := null;
2355 p_x_header_rec.attribute5 := null;
2356 p_x_header_rec.attribute6 := null;
2357 p_x_header_rec.attribute7 := null;
2358 p_x_header_rec.attribute8 := null;
2359 p_x_header_rec.attribute9 := null;
2360 p_x_header_rec.attribute10 := null;
2361 p_x_header_rec.attribute11 := null;
2362 p_x_header_rec.attribute12 := null;
2363 p_x_header_rec.attribute13 := null;
2364 p_x_header_rec.attribute14 := null;
2365 p_x_header_rec.attribute15 := null;
2366 p_x_header_rec.attribute16 := null; -- for bug 2184255
2367 p_x_header_rec.attribute17 := null;
2368 p_x_header_rec.attribute18 := null;
2369 p_x_header_rec.attribute19 := null;
2370 p_x_header_rec.attribute20 := null;
2371 --
2372 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2373 --
2374 p_x_header_rec.context := FND_API.G_MISS_CHAR;
2375 p_x_header_rec.attribute1 := FND_API.G_MISS_CHAR;
2376 p_x_header_rec.attribute2 := FND_API.G_MISS_CHAR;
2377 p_x_header_rec.attribute3 := FND_API.G_MISS_CHAR;
2378 p_x_header_rec.attribute4 := FND_API.G_MISS_CHAR;
2379 p_x_header_rec.attribute5 := FND_API.G_MISS_CHAR;
2380 p_x_header_rec.attribute6 := FND_API.G_MISS_CHAR;
2381 p_x_header_rec.attribute7 := FND_API.G_MISS_CHAR;
2382 p_x_header_rec.attribute8 := FND_API.G_MISS_CHAR;
2383 p_x_header_rec.attribute9 := FND_API.G_MISS_CHAR;
2384 p_x_header_rec.attribute10 := FND_API.G_MISS_CHAR;
2385 p_x_header_rec.attribute11 := FND_API.G_MISS_CHAR;
2386 p_x_header_rec.attribute12 := FND_API.G_MISS_CHAR;
2387 p_x_header_rec.attribute13 := FND_API.G_MISS_CHAR;
2388 p_x_header_rec.attribute14 := FND_API.G_MISS_CHAR;
2389 p_x_header_rec.attribute15 := FND_API.G_MISS_CHAR;
2390 p_x_header_rec.attribute16 := FND_API.G_MISS_CHAR; -- for bug 2184255
2391 p_x_header_rec.attribute17 := FND_API.G_MISS_CHAR;
2392 p_x_header_rec.attribute18 := FND_API.G_MISS_CHAR;
2393 p_x_header_rec.attribute19 := FND_API.G_MISS_CHAR;
2394 p_x_header_rec.attribute20 := FND_API.G_MISS_CHAR;
2395 --
2396 ELSE
2397 --
2398 x_return_status := FND_API.G_RET_STS_ERROR;
2399 --
2400 END IF;
2401 --
2402 END IF;
2403 --
2404 END IF;
2405 --
2406 oe_debug_pub.add('After header_desc_flex ' || x_return_status,2);
2407 --
2408 IF p_x_header_rec.global_attribute1 IS NOT NULL
2409 OR p_x_header_rec.global_attribute10 IS NOT NULL
2410 OR p_x_header_rec.global_attribute11 IS NOT NULL
2411 OR p_x_header_rec.global_attribute12 IS NOT NULL
2412 OR p_x_header_rec.global_attribute13 IS NOT NULL
2413 OR p_x_header_rec.global_attribute14 IS NOT NULL
2414 OR p_x_header_rec.global_attribute15 IS NOT NULL
2415 OR p_x_header_rec.global_attribute16 IS NOT NULL
2416 OR p_x_header_rec.global_attribute17 IS NOT NULL
2417 OR p_x_header_rec.global_attribute18 IS NOT NULL
2418 OR p_x_header_rec.global_attribute19 IS NOT NULL
2419 OR p_x_header_rec.global_attribute2 IS NOT NULL
2420 OR p_x_header_rec.global_attribute20 IS NOT NULL
2421 OR p_x_header_rec.global_attribute3 IS NOT NULL
2422 OR p_x_header_rec.global_attribute4 IS NOT NULL
2423 OR p_x_header_rec.global_attribute5 IS NOT NULL
2424 OR p_x_header_rec.global_attribute6 IS NOT NULL
2425 OR p_x_header_rec.global_attribute7 IS NOT NULL
2426 OR p_x_header_rec.global_attribute8 IS NOT NULL
2427 OR p_x_header_rec.global_attribute9 IS NOT NULL
2428 OR p_x_header_rec.global_attribute_category IS NOT NULL
2429 THEN
2430
2431 --
2432 OE_DEBUG_PUB.ADD('Before G_header_desc_flex',2);
2433 IF NOT OE_CNCL_VALIDATE.G_Header_Desc_Flex
2434 (p_context => p_x_header_rec.global_attribute_category
2435 ,p_attribute1 => p_x_header_rec.global_attribute1
2436 ,p_attribute2 => p_x_header_rec.global_attribute2
2437 ,p_attribute3 => p_x_header_rec.global_attribute3
2438 ,p_attribute4 => p_x_header_rec.global_attribute4
2439 ,p_attribute5 => p_x_header_rec.global_attribute5
2440 ,p_attribute6 => p_x_header_rec.global_attribute6
2441 ,p_attribute7 => p_x_header_rec.global_attribute7
2442 ,p_attribute8 => p_x_header_rec.global_attribute8
2443 ,p_attribute9 => p_x_header_rec.global_attribute9
2444 ,p_attribute10 => p_x_header_rec.global_attribute10
2445 ,p_attribute11 => p_x_header_rec.global_attribute11
2446 ,p_attribute12 => p_x_header_rec.global_attribute12
2447 ,p_attribute13 => p_x_header_rec.global_attribute13
2448 ,p_attribute14 => p_x_header_rec.global_attribute13
2449 ,p_attribute15 => p_x_header_rec.global_attribute14
2450 ,p_attribute16 => p_x_header_rec.global_attribute16
2451 ,p_attribute17 => p_x_header_rec.global_attribute17
2452 ,p_attribute18 => p_x_header_rec.global_attribute18
2453 ,p_attribute19 => p_x_header_rec.global_attribute19
2454 ,p_attribute20 => p_x_header_rec.global_attribute20)
2455 THEN
2456 --
2457 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2458 --
2459 p_x_header_rec.global_attribute_category := null;
2460 p_x_header_rec.global_attribute1 := null;
2461 p_x_header_rec.global_attribute2 := null;
2462 p_x_header_rec.global_attribute3 := null;
2463 p_x_header_rec.global_attribute4 := null;
2464 p_x_header_rec.global_attribute5 := null;
2465 p_x_header_rec.global_attribute6 := null;
2466 p_x_header_rec.global_attribute7 := null;
2467 p_x_header_rec.global_attribute8 := null;
2468 p_x_header_rec.global_attribute9 := null;
2469 p_x_header_rec.global_attribute11 := null;
2470 p_x_header_rec.global_attribute12 := null;
2471 p_x_header_rec.global_attribute13 := null;
2472 p_x_header_rec.global_attribute14 := null;
2473 p_x_header_rec.global_attribute15 := null;
2474 p_x_header_rec.global_attribute16 := null;
2475 p_x_header_rec.global_attribute17 := null;
2476 p_x_header_rec.global_attribute18 := null;
2477 p_x_header_rec.global_attribute19 := null;
2478 p_x_header_rec.global_attribute20 := null;
2479 --
2480 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2481 --
2482 p_x_header_rec.global_attribute_category
2483 := FND_API.G_MISS_CHAR;
2484 p_x_header_rec.global_attribute1 := FND_API.G_MISS_CHAR;
2485 p_x_header_rec.global_attribute2 := FND_API.G_MISS_CHAR;
2486 p_x_header_rec.global_attribute3 := FND_API.G_MISS_CHAR;
2487 p_x_header_rec.global_attribute4 := FND_API.G_MISS_CHAR;
2488 p_x_header_rec.global_attribute5 := FND_API.G_MISS_CHAR;
2489 p_x_header_rec.global_attribute6 := FND_API.G_MISS_CHAR;
2490 p_x_header_rec.global_attribute7 := FND_API.G_MISS_CHAR;
2491 p_x_header_rec.global_attribute8 := FND_API.G_MISS_CHAR;
2492 p_x_header_rec.global_attribute9 := FND_API.G_MISS_CHAR;
2493 p_x_header_rec.global_attribute11 := FND_API.G_MISS_CHAR;
2494 p_x_header_rec.global_attribute12 := FND_API.G_MISS_CHAR;
2495 p_x_header_rec.global_attribute13 := FND_API.G_MISS_CHAR;
2496 p_x_header_rec.global_attribute14 := FND_API.G_MISS_CHAR;
2497 p_x_header_rec.global_attribute15 := FND_API.G_MISS_CHAR;
2498 p_x_header_rec.global_attribute16 := FND_API.G_MISS_CHAR;
2499 p_x_header_rec.global_attribute17 := FND_API.G_MISS_CHAR;
2500 p_x_header_rec.global_attribute18 := FND_API.G_MISS_CHAR;
2501 p_x_header_rec.global_attribute19 := FND_API.G_MISS_CHAR;
2502 p_x_header_rec.global_attribute20 := FND_API.G_MISS_CHAR;
2503 --
2504 ELSE
2505 --
2506 x_return_status := FND_API.G_RET_STS_ERROR;
2507 --
2508 END IF;
2509 --
2510 END IF;
2511 --
2512 END IF;
2513 --
2514
2515 --
2516 OE_DEBUG_PUB.ADD('After G_header_desc_flex ' || x_return_status,2);
2517 -- Done validating attributes
2518
2519 -- Salesrep_id
2520 IF p_x_header_rec.salesrep_id IS NOT NULL THEN
2521 --
2522 IF NOT OE_CNCL_Validate.salesrep(p_x_header_rec.salesrep_id) THEN
2523 --
2524 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2525 --
2526 p_x_header_rec.salesrep_id := NULL;
2527 --
2528 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2529 --
2530 p_x_header_rec.salesrep_id := FND_API.G_MISS_NUM;
2531 --
2532 ELSE
2533 --
2534 x_return_status := FND_API.G_RET_STS_ERROR;
2535 --
2536 END IF;
2537 --
2538 END IF;
2539 --
2540 END IF;
2541 --
2542
2543 --
2544 IF p_x_header_rec.sales_channel_code IS NOT NULL THEN
2545 --
2546 IF NOT OE_CNCL_Validate.sales_channel(p_x_header_rec.sales_channel_code) THEN
2547 --
2548 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2549 --
2550 p_x_header_rec.sales_channel_code := NULL;
2551 --
2552 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2553 --
2554 p_x_header_rec.sales_channel_code := FND_API.G_MISS_NUM;
2555 --
2556 ELSE
2557 --
2558 x_return_status := FND_API.G_RET_STS_ERROR;
2559 --
2560 END IF;
2561 --
2562 END IF;
2563 --
2564 END IF;
2565 --
2566
2567
2568 -- Return_reason_code
2569 --
2570 IF p_x_header_rec.return_reason_code IS NOT NULL THEN
2571 --
2572 IF NOT OE_CNCL_Validate.return_reason(p_x_header_rec.return_reason_code) THEN
2573 --
2574 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2575 --
2576 p_x_header_rec.return_reason_code := NULL;
2577 --
2578 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2579 --
2580 p_x_header_rec.return_reason_code := FND_API.G_MISS_CHAR;
2581 --
2582 ELSE
2583 --
2584 x_return_status := FND_API.G_RET_STS_ERROR;
2585 --
2586 END IF;
2587 --
2588 END IF;
2589 --
2590 END IF;
2591 --
2592
2593 -- Customer_Location
2594 --
2595 IF p_x_header_rec.sold_to_site_use_id IS NOT NULL THEN
2596 --
2597 IF NOT OE_CNCL_Validate.Customer_Location(p_x_header_rec.sold_to_site_use_id) THEN
2598 --
2599 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2600 --
2601 p_x_header_rec.sold_to_site_use_id := NULL;
2602 --
2603 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2604 --
2605 p_x_header_rec.sold_to_site_use_id := FND_API.G_MISS_CHAR;
2606 --
2607 ELSE
2608 --
2609 x_return_status := FND_API.G_RET_STS_ERROR;
2610 --
2611 END IF;
2612 --
2613 END IF;
2614 --
2615 END IF;
2616 --
2617 oe_debug_pub.add('Exiting OE_CNCL_VALIDATE_HEADER.ATTRIBUTES',1);
2618
2619 EXCEPTION
2620
2621 WHEN FND_API.G_EXC_ERROR THEN
2622 x_return_status := FND_API.G_RET_STS_ERROR;
2623
2624 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2626
2627 WHEN OTHERS THEN
2628 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2629
2630 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2631 THEN
2632 OE_MSG_PUB.Add_Exc_Msg
2633 ( G_PKG_NAME
2634 ,'Attributes'
2635 );
2636 END IF;
2637
2638 END Attributes;
2639
2640
2641
2642 END OE_CNCL_Validate_Header;