1 PACKAGE BODY OE_BULK_PROCESS_HEADER AS
2 /* $Header: OEBLHDRB.pls 120.13 2011/12/20 01:31:23 sujithku ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_BULK_PROCESS_HEADER';
5
6
7 -----------------------------------------------------------------------
8 -- LOCAL PROCEDURES/FUNCTIONS
9 -----------------------------------------------------------------------
10
11 -- Pass sold_to_org_id on the order or line in the related_customer_id
12 -- parameter.
13
14 FUNCTION Is_Related_Customer
15 (p_related_customer_id IN NUMBER
16 ,p_customer_id IN NUMBER
17 )
18 RETURN BOOLEAN
19 IS
20 l_dummy VARCHAR2(1);
21 BEGIN
22
23 SELECT 'VALID'
24 INTO l_dummy
25 FROM hz_cust_acct_relate
26 WHERE RELATED_CUST_ACCOUNT_ID = p_related_customer_id
27 AND CUST_ACCOUNT_ID = p_customer_id AND STATUS='A'
28 AND ROWNUM = 1;
29
30 RETURN TRUE;
31
32 EXCEPTION
33 WHEN OTHERS THEN
34 RETURN FALSE;
35
36 END Is_Related_Customer;
37
38
39 FUNCTION Get_Order_Number(p_order_type_id IN NUMBER,
40 p_order_number IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
41 p_gapless_sequence OUT NOCOPY VARCHAR2)
42 RETURN BOOLEAN
43 IS
44
45 x_result NUMBER;
46 x_doc_sequence_value NUMBER;
47 x_doc_category_code VARCHAR(30):= p_order_type_id;
48 x_doc_sequence_id NUMBER;
49 x_db_sequence_name VARCHAR2(50);
50 x_doc_sequence_type CHAR(1);
51 x_doc_sequence_name VARCHAR2(240);
52 x_Prd_Tbl_Name VARCHAR2(240) ;
53 x_Aud_Tbl_Name VARCHAR2(240);
54 x_Msg_Flag VARCHAR2(240);
55 x_seqassid INTEGER;
56
57 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
58 BEGIN
59
60 -- Check whether order type exists in global table
61
62 IF G_SEQ_INFO_TBL.EXISTS(p_order_type_id) THEN
63
64 x_doc_sequence_type := G_SEQ_INFO_TBL(p_order_type_id);
65
66 ELSE -- Get sequence type from AOL
67
68 x_result := fnd_seqnum.get_seq_info( 660,
69 x_doc_category_code,
70 OE_Bulk_Order_Pvt.G_SOB_ID,
71 null,
72 sysdate,
73 x_doc_sequence_id,
74 x_doc_sequence_type,
75 x_doc_sequence_name,
76 x_db_sequence_name,
77 x_seqassid,
78 x_Prd_Tbl_Name,
79 x_Aud_Tbl_Name,
80 x_Msg_Flag
81 );
82
83
84 IF (x_result <> FND_SEQNUM.SEQSUCC)THEN
85
86 IF (x_result = FND_SEQNUM.NOTUSED) THEN
87 fnd_message.set_name('ONT','OE_MISS_DOC_SEQ');
88 oe_bulk_msg_pub.Add('Y', 'ERROR');
89 RAISE FND_API.G_EXC_ERROR;
90 END IF;
91
92 END IF;
93
94 -- Add entry to global table
95
96 G_SEQ_INFO_TBL(p_order_type_id) := x_doc_sequence_type;
97
98 END IF; -- Order Type exists in Global table
99
100 -- For manual sequences Caller needs to pass the Order Number.
101 IF (x_doc_sequence_type = 'M') THEN
102 IF (p_order_number IS NULL) THEN
103 FND_MESSAGE.SET_NAME('ONT','OE_ATTRIBUTE_REQUIRED');
104 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
105 OE_Order_Util.Get_Attribute_Name('ORDER_NUMBER'));
106 oe_bulk_msg_pub.Add('Y', 'ERROR');
107 return FALSE;
108 ELSE
109 return TRUE;
110 END IF;
111 -- Gapless sequence not supported for BULK
112 ELSIF x_doc_sequence_type = 'G' THEN
113 fnd_message.set_name('ONT','OE_BULK_GAPLESS_DOC_SEQ');
114 oe_bulk_msg_pub.Add('Y', 'ERROR');
115 p_gapless_sequence := 'Y';
116 RETURN FALSE;
117 END IF;
118
119 X_result := fnd_seqnum.get_seq_val(660,
120 x_doc_category_code,
121 OE_Bulk_Order_Pvt.G_SOB_ID,
122 null,
123 sysdate,
124 x_doc_sequence_value,
125 x_doc_sequence_id,
126 'Y',
127 'Y');
128
129 IF (x_result <> 0)THEN
130 RAISE FND_API.G_EXC_ERROR;
131 END IF;
132
133 p_order_number := x_doc_sequence_value;
134 IF (p_order_number IS NULL) THEN
135 FND_MESSAGE.SET_NAME('ONT','OE_ATTRIBUTE_REQUIRED');
136 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
137 OE_Order_Util.Get_Attribute_Name('ORDER_NUMBER'));
138 oe_bulk_msg_pub.Add('Y', 'ERROR');
139 return FALSE;
140 END IF;
141
142 RETURN TRUE;
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 RETURN FALSE;
147 END Get_Order_Number;
148
149
150 --
151 -- This FUNCTION returns the Price List Type Code
152 --
153
154 FUNCTION Get_Price_List_Type
155 ( p_price_list_id IN NUMBER)
156 RETURN VARCHAR2
157 IS
158 l_c_index NUMBER;
159 BEGIN
160
161 l_c_index := OE_Bulk_Cache.Load_Price_List(p_price_list_id);
162
163 RETURN OE_Bulk_Cache.G_PRICE_LIST_TBL(l_c_index).list_type_code;
164
165 END Get_Price_List_Type;
166
167
168 --
169 -- This FUNCTION validates Tax Exempt Reason against the lookup
170 --
171
172 FUNCTION Valid_Tax_Exempt_Reason
173 (p_tax_exempt_reason_code VARCHAR2
174 )
175 RETURN BOOLEAN
176 IS
177 l_dummy VARCHAR2(1);
178 BEGIN
179 -- EBTax Changes
180 SELECT 'Y'
181 INTO l_dummy
182 FROM fnd_lookups l
183 WHERE l.LOOKUP_CODE = p_tax_exempt_reason_code --7782998
184 AND l.LOOKUP_TYPE = 'ZX_EXEMPTION_REASON_CODE'
185 AND l.ENABLED_FLAG = 'Y'
186 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
187 AND NVL(END_DATE_ACTIVE, SYSDATE)
188 ;
189
190 RETURN TRUE;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 RETURN FALSE;
195 END Valid_Tax_Exempt_Reason;
196
197 --
198 -- This FUNCTION validates Tax Exemptions against Customer, Sites
199 --
200
201 FUNCTION Valid_Tax_Exemptions
202 (p_tax_exempt_number VARCHAR2
203 ,p_tax_exempt_reason_code VARCHAR2
204 ,p_ship_to_org_id NUMBER
205 ,p_invoice_to_org_id NUMBER
206 ,p_sold_to_org_id NUMBER
207 ,p_request_date DATE
208 )
209 RETURN BOOLEAN
210 IS
211 l_dummy VARCHAR2(10);
212 -- eBTax Changes
213 l_ship_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
214 l_ship_to_party_id hz_cust_accounts.party_id%type;
215 l_ship_to_party_site_id hz_party_sites.party_site_id%type;
216 l_bill_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
217 l_bill_to_party_id hz_cust_accounts.party_id%type;
218 l_bill_to_party_site_id hz_party_sites.party_site_id%type;
219 l_org_id NUMBER;
220 l_legal_entity_id NUMBER;
221
222 cursor partyinfo(p_site_org_id HZ_CUST_SITE_USES_ALL.SITE_USE_ID%type) is
223 SELECT cust_acct.cust_account_id,
224 cust_Acct.party_id,
225 acct_site.party_site_id,
226 site_use.org_id
227 FROM
228 HZ_CUST_SITE_USES_ALL site_use,
229 HZ_CUST_ACCT_SITES_ALL acct_site,
230 HZ_CUST_ACCOUNTS_ALL cust_Acct
231 WHERE site_use.site_use_id = p_site_org_id
232 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
233 and acct_site.cust_account_id = cust_acct.cust_account_id;
234
235
236 BEGIN
237 -- EBTax Changes
238
239 open partyinfo(p_invoice_to_org_id);
240 fetch partyinfo into l_bill_to_cust_Acct_id,
241 l_bill_to_party_id,
242 l_bill_to_party_site_id,
243 l_org_id;
244 close partyinfo;
245
246 if p_ship_to_org_id = p_invoice_to_org_id then
247 l_ship_to_cust_Acct_id := l_bill_to_cust_Acct_id;
248 l_ship_to_party_id := l_bill_to_party_id;
249 l_ship_to_party_site_id := l_bill_to_party_site_id ;
250 else
251 open partyinfo(p_ship_to_org_id);
252 fetch partyinfo into l_ship_to_cust_Acct_id,
253 l_ship_to_party_id,
254 l_ship_to_party_site_id,
255 l_org_id;
256 close partyinfo;
257 end if;
258
259 SELECT 'VALID'
260 INTO l_dummy
261 FROM ZX_EXEMPTIONS_V
262 WHERE EXEMPT_CERTIFICATE_NUMBER = p_tax_exempt_number
263 AND EXEMPT_REASON_CODE = p_tax_exempt_reason_code
264 AND nvl(site_use_id,nvl(p_ship_to_org_id, p_invoice_to_org_id)) =
265 nvl(p_ship_to_org_id,p_invoice_to_org_id)
266 AND nvl(cust_account_id, l_bill_to_cust_acct_id) = l_bill_to_cust_acct_id
267 AND nvl(PARTY_SITE_ID,nvl(l_ship_to_party_site_id, l_bill_to_party_site_id))=
268 nvl(l_ship_to_party_site_id, l_bill_to_party_site_id)
269 and org_id = l_org_id
270 and party_id = l_bill_to_party_id
271 -- and nvl(LEGAL_ENTITY_ID,-99) IN (nvl(l_legal_entity_id, legal_entity_id), -99)
272 AND EXEMPTION_STATUS_CODE = 'PRIMARY'
273 AND TRUNC(NVL(p_request_date,sysdate))
274 BETWEEN TRUNC(EFFECTIVE_FROM)
275 AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_request_date,sysdate)))
276 AND ROWNUM = 1;
277
278
279 EXCEPTION
280 WHEN NO_DATA_FOUND THEN
281 RETURN FALSE;
282 END Valid_Tax_Exemptions;
283
284 --{ bug 5054618
285 -- End customer changes
286 FUNCTION validate_end_customer(p_end_customer_id IN NUMBER) RETURN BOOLEAN
287 IS
288 l_dummy VARCHAR2(10);
289 BEGIN
290 IF p_end_customer_id IS NULL OR
291 p_end_customer_id = FND_API.G_MISS_NUM THEN
292 RETURN TRUE;
293 END IF;
294
295 SELECT 'VALID' INTO l_dummy
296 FROM OE_SOLD_TO_ORGS_V
297 WHERE ORGANIZATION_ID =p_end_customer_id AND
298 STATUS = 'A' AND
299 SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
300 AND NVL(END_DATE_ACTIVE, SYSDATE);
301 RETURN TRUE;
302 EXCEPTION
303 WHEN NO_DATA_FOUND THEN
304
305 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
306 THEN
307
308 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'END_CUSTOMER_ID');
309
310 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
311 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
312 OE_Order_Util.Get_Attribute_Name('END_CUSTOMER_ID')||':validation:'||to_char(p_end_customer_id));
313 OE_MSG_PUB.Add;
314 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
315
316 END IF;
317
318 RETURN FALSE;
319
320 WHEN OTHERS THEN
321
322 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
323 THEN
324 OE_MSG_PUB.Add_Exc_Msg
325 ( G_PKG_NAME
326 , 'END_CUSOTMER'
327 );
328 END IF;
329
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 null;
332
333 END validate_end_customer;
334
335 FUNCTION validate_end_customer_contact(p_end_customer_contact_id IN NUMBER) RETURN BOOLEAN
336 IS
337 l_dummy VARCHAR2(10);
338 BEGIN
339 IF p_end_customer_contact_id IS NULL OR
340 p_end_customer_contact_id = FND_API.G_MISS_NUM THEN
341 RETURN TRUE;
342 END IF;
343
344 SELECT 'VALID' INTO l_dummy
345 FROM OE_RA_CONTACTS_V CON
346 WHERE CON.CONTACT_ID = p_end_customer_contact_id
347 AND CON.STATUS = 'A';
348 RETURN TRUE;
349 EXCEPTION
350 WHEN NO_DATA_FOUND THEN
351
352 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
353 THEN
354
355 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'END_CUSTOMER_CONTACT_ID');
356
357 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
358 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
359 OE_Order_Util.Get_Attribute_Name('end_customer_contact_id'));
360 OE_MSG_PUB.Add;
361 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
362
363 END IF;
364
365
366 RETURN FALSE;
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 , 'End_Customer_Contact'
375 );
376 END IF;
377
378
379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
380 null;
381 END validate_end_customer_contact;
382
383
384 FUNCTION validate_END_CUSTOMER_SITE_USE (
385 p_end_customer_site_use_id IN NUMBER,
386 p_end_customer_id IN NUMBER)RETURN BOOLEAN
387 IS
388 l_dummy VARCHAR2(10);
389 l_c_index NUMBER;
390 BEGIN
391 IF p_end_customer_site_use_id IS NULL OR p_end_customer_site_use_id = FND_API.G_MISS_NUM THEN
392 RETURN TRUE;
393 END IF;
394
395 /* SELECT 'VALID'
396 INTO
397 l_dummy
398 FROM
399 hz_cust_site_uses site_use,
400 hz_cust_acct_sites acct_site
401 WHERE
402 site_use.site_use_id=p_end_customer_site_use_id
403 and site_use.cust_acct_site_id=acct_site.cust_acct_site_id
404 and acct_site.cust_account_id=p_end_customer_id;
405
406 RETURN TRUE; */
407 l_c_index := OE_Bulk_Cache.Load_End_customer_site
408 (p_key => p_end_customer_site_use_id);
409
410 IF OE_Bulk_Cache.G_END_CUSTOMER_SITE_TBL(l_c_index).customer_id
411 = p_end_customer_id
412 THEN
413 RETURN TRUE;
414 ELSE
415 RETURN FALSE;
416 END IF;
417
418 EXCEPTION
419 WHEN NO_DATA_FOUND THEN
420 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
421 THEN
422
423 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'END_CUSTOMER_SITE_USE_ID');
424
425 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
426 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
427 OE_Order_Util.Get_Attribute_Name('end_customer_site_use_id'));
428 OE_MSG_PUB.Add;
429 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
430
431 END IF;
432
433
434 RETURN FALSE;
435
436 WHEN OTHERS THEN
437
438 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
439 THEN
440 OE_MSG_PUB.Add_Exc_Msg
441 ( G_PKG_NAME
442 , 'END_CUSTOMER_SITE_USE_ID'
443 );
444 END IF;
445
446
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448 null;
449 END validate_END_CUSTOMER_SITE_USE;
450
451 FUNCTION validate_IB_OWNER ( p_ib_owner IN VARCHAR2 )RETURN BOOLEAN
452 IS l_dummy VARCHAR2(10);
453 l_lookup_type1 VARCHAR2(80) :='ITEM_OWNER';
454
455 BEGIN
456 IF p_ib_owner IS NULL OR
457 p_ib_owner = FND_API.G_MISS_CHAR THEN
458 RETURN TRUE;
459 END IF;
460 SELECT 'VALID' INTO
461 l_dummy FROM OE_LOOKUPS
462 WHERE lookup_code = p_ib_owner AND
463 ( lookup_type = l_lookup_type1);
464
465 RETURN TRUE;
466 EXCEPTION
467 WHEN NO_DATA_FOUND THEN
468
469 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
470 THEN
471
472 OE_DEBUG_PUB.ADD('Validation failed for IB_INSTALLED_AT_LOCATION in OEBLHDRB.pls');
473 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'IB_INSTALLED_AT_LOCATION');
474
475 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
476 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
477 OE_Order_Util.Get_Attribute_Name('ib_installed_at_location'));
478 OE_MSG_PUB.Add;
479 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
480
481 END IF;
482
483
484 RETURN FALSE;
485
486 WHEN OTHERS THEN
487
488 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
489 THEN
490 OE_MSG_PUB.Add_Exc_Msg
491 ( G_PKG_NAME
492 , 'IB_INSTALLED_AT_LOCATION'
493 );
494 END IF;
495
496
497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498
499 null;
500
501 END validate_IB_OWNER;
502
503
504 FUNCTION validate_IB_INST_LOC( p_ib_installed_at_location IN VARCHAR2 )RETURN BOOLEAN
505 IS
506 l_dummy VARCHAR2(10);
507 l_lookup_type1 VARCHAR2(80) :='ITEM_INSTALL_LOCATION';
508
509 BEGIN
510 IF p_ib_installed_at_location IS NULL OR p_ib_installed_at_location = FND_API.G_MISS_CHAR THEN
511 RETURN TRUE;
512 END IF;
513 SELECT 'VALID' INTO l_dummy FROM OE_LOOKUPS
514 WHERE lookup_code = p_ib_installed_at_location AND
515 (lookup_type = l_lookup_type1);
516
517 RETURN TRUE;
518 EXCEPTION
519 WHEN NO_DATA_FOUND THEN
520
521 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
522 THEN
523
524 OE_DEBUG_PUB.ADD('Validation failed for IB_INSTALLED_AT_LOCATION in OEBLHDRB.pls');
525 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'IB_INSTALLED_AT_LOCATION');
526
527 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
528 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
529 OE_Order_Util.Get_Attribute_Name('ib_installed_at_location'));
530 OE_MSG_PUB.Add;
531 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
532
533 END IF;
534
535
536 RETURN FALSE;
537
538 WHEN OTHERS THEN
539
540 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
541 THEN
542 OE_MSG_PUB.Add_Exc_Msg
543 ( G_PKG_NAME
544 , 'IB_INSTALLED_AT_LOCATION'
545 );
546 END IF;
547
548
549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
550 null;
551
552 END validate_IB_INST_LOC;
553
554 FUNCTION validate_IB_CURRENT_LOCATION ( p_ib_current_location IN VARCHAR2 )
555 RETURN BOOLEAN
556 IS
557 l_dummy VARCHAR2(10);
558 l_lookup_type1 VARCHAR2(80) :='ITEM_CURRENT_LOCATION';
559
560 BEGIN
561 IF p_ib_current_location IS NULL OR
562 p_ib_current_location = FND_API.G_MISS_CHAR
563 THEN
564
565 RETURN TRUE;
566 END IF;
567
568 SELECT 'VALID'
569 INTO l_dummy
570 FROM OE_LOOKUPS
571 WHERE lookup_code = p_ib_current_location AND
572 (lookup_type = l_lookup_type1 );
573
574 RETURN TRUE;
575 EXCEPTION
576 WHEN NO_DATA_FOUND THEN
577
578 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
579 THEN
580
581 OE_DEBUG_PUB.ADD('Validation failed for IB_CURRENT_LOCATION in OEBLHDRB.pls');
582 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'IB_CURRENT_LOCATION');
583
584 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
585 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
586 OE_Order_Util.Get_Attribute_Name('ib_current_location'));
587 OE_MSG_PUB.Add;
588 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
589
590 END IF;
591
592
593 RETURN FALSE;
594
595 WHEN OTHERS THEN
596
597 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
598 THEN
599 OE_MSG_PUB.Add_Exc_Msg
600 ( G_PKG_NAME
601 , 'IB_CURRENT_LOCATION'
602 );
603 END IF;
604
605
606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
607
608 null;
609 END validate_IB_CURRENT_LOCATION;
610
611 --bug 5054618}
612
613 PROCEDURE Check_Book_Reqd_Attributes
614 ( p_header_rec IN OE_Bulk_Order_Pvt.HEADER_REC_TYPE
615 , p_index IN NUMBER
616 , x_return_status IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
617 )
618 IS
619 l_set_of_books_rec OE_Order_Cache.Set_Of_Books_Rec_Type;
620 l_c_index NUMBER;
621 BEGIN
622
623 oe_debug_pub.add('Enter OE_VALIDATE_HEADER.CHECK_BOOK_REQD',1);
624
625 -- Check for the following required fields on a booked order:
626 -- Order Number, Sold To Org, Invoice To Org,
627 -- Price List, Tax Exempt Flag, Sales Person, Order Date
628
629 IF p_header_rec.sold_to_org_id(p_index) IS NULL
630 THEN
631 x_return_status := FND_API.G_RET_STS_ERROR;
632 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
633 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
634 OE_Order_UTIL.Get_Attribute_Name('SOLD_TO_ORG_ID'));
635 oe_bulk_msg_pub.ADD;
636 END IF;
637
638 IF p_header_rec.salesrep_id(p_index) IS NULL
639 THEN
640 x_return_status := FND_API.G_RET_STS_ERROR;
641 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
642 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
643 OE_Order_UTIL.Get_Attribute_Name('SALESREP_ID'));
644 oe_bulk_msg_pub.ADD;
645 END IF;
646
647 IF p_header_rec.ordered_date(p_index) IS NULL
648 THEN
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
651 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
652 OE_Order_UTIL.Get_Attribute_Name('ORDERED_DATE'));
653 oe_bulk_msg_pub.ADD;
654 END IF;
655
656 IF p_header_rec.invoice_to_org_id(p_index) IS NULL
657 THEN
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
660 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
661 OE_Order_UTIL.Get_Attribute_Name('INVOICE_TO_ORG_ID'));
662 oe_bulk_msg_pub.ADD;
663 END IF;
664
665 IF p_header_rec.tax_exempt_flag(p_index) IS NULL
666 THEN
667 x_return_status := FND_API.G_RET_STS_ERROR;
668 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
669 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
670 OE_Order_UTIL.Get_Attribute_Name('TAX_EXEMPT_FLAG'));
671 oe_bulk_msg_pub.ADD;
672 END IF;
673
674
675 -- Fix bug 1262790
676 -- Ship To Org and Payment Term are required only on regular or
677 -- MIXED orders, NOT on RETURN orders
678
679 IF p_header_rec.order_category_code(p_index) <>
680 OE_GLOBALS.G_RETURN_CATEGORY_CODE THEN
681
682 IF p_header_rec.ship_to_org_id(p_index) IS NULL
683 THEN
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
686 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
687 OE_Order_UTIL.Get_Attribute_Name('SHIP_TO_ORG_ID'));
688 oe_bulk_msg_pub.ADD;
689 END IF;
690
691 IF p_header_rec.payment_term_id(p_index) IS NULL
692 THEN
693 x_return_status := FND_API.G_RET_STS_ERROR;
694 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
695 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
696 OE_Order_UTIL.Get_Attribute_Name('PAYMENT_TERM_ID'));
697 oe_bulk_msg_pub.ADD;
698 END IF;
699
700 END IF;
701
702
703 -- Check for additional required fields based on flags set
704 -- at the order type: agreement, customer po number
705
706 l_c_index := OE_Bulk_Cache.Load_Order_Type(p_header_rec.order_type_id(p_index));
707
708 IF ( OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).agreement_required_flag = 'Y' AND
709 p_header_rec.agreement_id(p_index) IS NULL)
710 THEN
711 x_return_status := FND_API.G_RET_STS_ERROR;
712 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
713 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
714 OE_Order_UTIL.Get_Attribute_Name('AGREEMENT_ID'));
715 oe_bulk_msg_pub.ADD;
716 END IF;
717
718 IF ( OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).require_po_flag = 'Y' AND
719 p_header_rec.cust_po_number(p_index) IS NULL)
720 THEN
721 x_return_status := FND_API.G_RET_STS_ERROR;
722 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
723 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
724 OE_Order_UTIL.Get_Attribute_Name('CUST_PO_NUMBER'));
725 oe_bulk_msg_pub.ADD;
726 END IF;
727
728
729 -- Conversion Type Related Checks
730
731 -- IF SOB currency is dIFferent from order currency,
732 -- conversion type is required
733
734 IF p_header_rec.conversion_type_code(p_index) IS NULL
735 THEN
736 l_set_of_books_rec := OE_Order_Cache.Load_Set_Of_Books;
737
738 IF ( l_set_of_books_rec.currency_code <>
739 p_header_rec.transactional_curr_code(p_index)) THEN
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 FND_MESSAGE.SET_NAME('ONT','OE_VAL_REQ_NON_BASE_CURRENCY');
742 FND_MESSAGE.SET_TOKEN
743 ('ORDER_CURRENCY',p_header_rec.transactional_curr_code(p_index));
744 FND_MESSAGE.SET_TOKEN('SOB_CURRENCY',l_set_of_books_rec.currency_code);
745 oe_bulk_msg_pub.ADD;
746 END IF;
747
748 -- IF conversion type is 'User', conversion rate AND conversion rate date
749 -- required.
750
751 ELSIF p_header_rec.conversion_type_code(p_index) = 'User'
752 THEN
753
754 IF p_header_rec.conversion_rate(p_index) IS NULL OR
755 p_header_rec.conversion_rate_date(p_index) IS NULL
756 THEN
757 x_return_status := FND_API.G_RET_STS_ERROR;
758 FND_MESSAGE.SET_NAME('ONT','OE_VAL_USER_CONVERSION_TYPE');
759 oe_bulk_msg_pub.ADD;
760 END IF;
761
762 END IF; -- END of checks based on conversion type
763
764
765 -- Checks based on payment type attached to the order
766
767 IF p_header_rec.payment_type_code(p_index) IS NOT NULL THEN
768
769 -- payment amount should be specIFied
770 -- only IF Payment Type is NOT Credit Card
771
772 IF p_header_rec.payment_type_code(p_index) <> 'CREDIT_CARD' AND
773 p_header_rec.payment_amount(p_index) IS NULL
774 THEN
775 x_return_status := FND_API.G_RET_STS_ERROR;
776 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQUIRED_ATTRIBUTE');
777 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
778 OE_Order_UTIL.Get_Attribute_Name('PAYMENT_AMOUNT'));
779 oe_bulk_msg_pub.ADD;
780 END IF;
781
782 -- check number required IF payment type is Check
783
784 IF (p_header_rec.payment_type_code(p_index) = 'CHECK' AND
785 p_header_rec.check_number(p_index) IS NULL )
786 THEN
787 x_return_status := FND_API.G_RET_STS_ERROR;
788 FND_MESSAGE.SET_NAME('ONT','OE_VAL_CHECK_NUM_REQD');
789 oe_bulk_msg_pub.ADD;
790 END IF;
791
792 -- credit card holder name, number AND expiration date
793 -- required for payment type of Credit Card
794
795 /*
796 ** Following Validation Moved to Authorize Credit Card
797 ** Payment Routine. Not required anymore at BOOKING.
798 IF p_header_rec.payment_type_code = 'CREDIT_CARD' THEN
799 IF p_header_rec.credit_card_holder_name IS NULL
800 OR p_header_rec.credit_card_number IS NULL
801 OR p_header_rec.credit_card_expiration_date IS NULL
802 THEN
803 x_return_status := FND_API.G_RET_STS_ERROR;
804 FND_MESSAGE.SET_NAME('ONT','OE_VAL_CREDIT_CARD_REQD');
805 oe_bulk_msg_pub.ADD;
806 END IF;
807 END IF;
808 */
809
810 END IF; -- END of checks related to payment type
811
812 oe_debug_pub.add('Exiting OE_VALIDATE_HEADER.CHECK_BOOK_REQD',1);
813 EXCEPTION
814 WHEN OTHERS THEN
815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
816 IF oe_bulk_msg_pub.check_msg_level (oe_bulk_msg_pub.G_MSG_LVL_UNEXP_ERROR)
817 THEN
818 oe_bulk_msg_pub.add_exc_msg
819 ( G_PKG_NAME ,
820 'Check_Book_Reqd_Attributes'
821 );
822 END IF;
823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
824 END Check_Book_Reqd_Attributes;
825
826
827 PROCEDURE Default_Record
828 (p_header_rec IN OUT NOCOPY OE_Bulk_Order_Pvt.HEADER_REC_TYPE
829 ,p_index IN NUMBER
830 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
831 )
832 IS
833 l_c_index NUMBER;
834 BEGIN
835
836 x_return_status := FND_API.G_RET_STS_SUCCESS;
837
838 -- Validation of defaulted attributes - when to do this, within the
839 -- cache for each source? YES!
840
841 -- Default Order Type from 1.Invoice To 2.Ship To
842
843 IF p_header_rec.order_type_id(p_index) IS NULL THEN
844
845 BEGIN
846
847 l_c_index := OE_Bulk_Cache.Load_Invoice_To
848 (p_key => p_header_rec.invoice_to_org_id(p_index)
849 ,p_default_attributes => 'Y');
850 p_header_rec.order_type_id(p_index)
851 := OE_Bulk_Cache.G_INVOICE_TO_TBL(l_c_index).order_type_id;
852 -- Invalid invoice to - error message populated during validation
853 EXCEPTION
854 WHEN NO_DATA_FOUND THEN
855 oe_debug_pub.add('Invoice To cache returns no data found');
856 x_return_status := FND_API.G_RET_STS_ERROR;
857 END;
858
859 END IF;
860
861 IF p_header_rec.order_type_id(p_index) IS NULL THEN
862
863 BEGIN
864
865 l_c_index := OE_Bulk_Cache.Load_Ship_To
866 (p_key => p_header_rec.ship_to_org_id(p_index)
867 ,p_default_attributes => 'Y');
868 p_header_rec.order_type_id(p_index)
869 := OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).order_type_id;
870
871 -- Invalid ship to - error message populated during validation
872 EXCEPTION
873 WHEN NO_DATA_FOUND THEN
874 oe_debug_pub.add('Ship To cache returns no data found');
875 x_return_status := FND_API.G_RET_STS_ERROR;
876 END;
877
878 END IF;
879
880 -- ASSUMPTION: The hierarchy for defaulting sources for each of the
881 -- attributes can only be 1.Agreement 2.Ship To 3.Invoice To 4.Order Type
882
883 -- Default attributes that have agreement as the first source
884
885 IF p_header_rec.agreement_id(p_index) IS NOT NULL
886 AND ( p_header_rec.price_list_id(p_index) IS NULL
887 OR p_header_rec.payment_term_id(p_index) IS NULL
888 OR p_header_rec.accounting_rule_id(p_index) IS NULL
889 OR p_header_rec.invoicing_rule_id(p_index) IS NULL )
890 THEN
891
892 BEGIN
893
894 l_c_index := OE_Bulk_Cache.Load_Agreement
895 (p_key => p_header_rec.agreement_id(p_index)
896 ,p_default_attributes => 'Y');
897
898 p_header_rec.price_list_id(p_index) := nvl(p_header_rec.price_list_id(p_index)
899 ,OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).price_list_id);
900 p_header_rec.payment_term_id(p_index) := nvl(p_header_rec.payment_term_id(p_index)
901 ,OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).payment_term_id);
902 p_header_rec.accounting_rule_id(p_index) := nvl(p_header_rec.accounting_rule_id(p_index)
903 ,OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).accounting_rule_id);
904 p_header_rec.invoicing_rule_id(p_index) := nvl(p_header_rec.invoicing_rule_id(p_index)
905 ,OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).invoicing_rule_id);
906
907 -- Invalid agreement - error message populated during validation
908 EXCEPTION
909 WHEN NO_DATA_FOUND THEN
910 oe_debug_pub.add('Agreement cache returns no data found');
911 x_return_status := FND_API.G_RET_STS_ERROR;
912 END;
913
914 END IF;
915
916 -- Default attributes that have ship to as the first source
917 -- or is the next source after agreement.
918
919 oe_debug_pub.add('load ship to cache');
920 IF p_header_rec.ship_to_org_id(p_index) IS NOT NULL
921 AND ( p_header_rec.fob_point_code(p_index) IS NULL
922 OR p_header_rec.freight_terms_code(p_index) IS NULL
923 OR p_header_rec.demand_class_code(p_index) IS NULL
924 OR p_header_rec.shipping_method_code(p_index) IS NULL
925 OR p_header_rec.ship_tolerance_above(p_index) IS NULL
926 OR p_header_rec.ship_tolerance_below(p_index) IS NULL
927 OR p_header_rec.latest_schedule_limit(p_index) IS NULL
928 OR p_header_rec.order_date_type_code(p_index) IS NULL )
929 THEN
930
931 BEGIN
932
933 l_c_index := OE_Bulk_Cache.Load_Ship_To
934 (p_key => p_header_rec.ship_to_org_id(p_index)
935 ,p_default_attributes => 'Y');
936
937 p_header_rec.fob_point_code(p_index) := nvl(p_header_rec.fob_point_code(p_index)
938 ,OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).fob_point_code);
939 p_header_rec.freight_terms_code(p_index) := nvl(p_header_rec.freight_terms_code(p_index)
940 ,OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).freight_terms_code);
941 p_header_rec.demand_class_code(p_index) := nvl(p_header_rec.demand_class_code(p_index)
942 ,OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).demand_class_code);
943 p_header_rec.shipping_method_code(p_index) := nvl(p_header_rec.shipping_method_code(p_index)
944 ,OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).shipping_method_code);
945 p_header_rec.ship_tolerance_above(p_index) := nvl(p_header_rec.ship_tolerance_above(p_index)
946 ,OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).ship_tolerance_above);
947 p_header_rec.ship_tolerance_below(p_index) := nvl(p_header_rec.ship_tolerance_below(p_index)
948 ,OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).ship_tolerance_below);
949 p_header_rec.latest_schedule_limit(p_index) := nvl(p_header_rec.latest_schedule_limit(p_index)
950 ,OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).latest_schedule_limit);
951 p_header_rec.order_date_type_code(p_index) := nvl(p_header_rec.order_date_type_code(p_index)
952 ,OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).order_date_type_code);
953
954 -- Invalid ship to - error message populated during validation
955 EXCEPTION
956 WHEN NO_DATA_FOUND THEN
957 oe_debug_pub.add('Ship To cache returns no data found');
958 x_return_status := FND_API.G_RET_STS_ERROR;
959 END;
960
961 END IF;
962
963 -- Default attributes that have invoice to as the first source
964 -- or is the next source after agreement, ship to.
965
966 oe_debug_pub.add('load invoice to cache');
967 IF p_header_rec.invoice_to_org_id(p_index) IS NOT NULL
968 AND ( p_header_rec.price_list_id(p_index) IS NULL
969 OR p_header_rec.payment_term_id(p_index) IS NULL )
970 THEN
971
972 BEGIN
973
974 l_c_index := OE_Bulk_Cache.Load_Invoice_To
975 (p_key => p_header_rec.invoice_to_org_id(p_index)
976 ,p_default_attributes => 'Y');
977
978 p_header_rec.price_list_id(p_index) := nvl(p_header_rec.price_list_id(p_index)
979 ,OE_Bulk_Cache.G_INVOICE_TO_TBL(l_c_index).price_list_id);
980 p_header_rec.payment_term_id(p_index) := nvl(p_header_rec.payment_term_id(p_index)
981 ,OE_Bulk_Cache.G_INVOICE_TO_TBL(l_c_index).payment_term_id);
982
983 -- Invalid invoice to - error message populated during validation
984 EXCEPTION
985 WHEN NO_DATA_FOUND THEN
986 oe_debug_pub.add('Invoice to cache returns no data found');
987 x_return_status := FND_API.G_RET_STS_ERROR;
988 END;
989
990 END IF;
991
992 -- Default remaining attributes from order type
993
994 IF p_header_rec.order_type_id(p_index) IS NOT NULL
995 AND (p_header_rec.fob_point_code(p_index) IS NULL
996 OR p_header_rec.freight_terms_code(p_index) IS NULL
997 OR p_header_rec.demand_class_code(p_index) IS NULL
998 OR p_header_rec.shipping_method_code(p_index) IS NULL
999 OR p_header_rec.shipment_priority_code(p_index) IS NULL
1000 OR p_header_rec.accounting_rule_id(p_index) IS NULL
1001 OR p_header_rec.invoicing_rule_id(p_index) IS NULL
1002 OR p_header_rec.conversion_type_code(p_index) IS NULL )
1003 THEN
1004
1005 BEGIN
1006
1007 l_c_index := OE_Bulk_Cache.Load_Order_Type
1008 (p_key => p_header_rec.order_type_id(p_index)
1009 ,p_default_attributes => 'Y');
1010
1011 p_header_rec.accounting_rule_id(p_index) := nvl(p_header_rec.accounting_rule_id(p_index)
1012 ,OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).accounting_rule_id);
1013 p_header_rec.invoicing_rule_id(p_index) := nvl(p_header_rec.invoicing_rule_id(p_index)
1014 ,OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).invoicing_rule_id);
1015 p_header_rec.fob_point_code(p_index) := nvl(p_header_rec.fob_point_code(p_index)
1016 ,OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).fob_point_code);
1017 p_header_rec.freight_terms_code(p_index) := nvl(p_header_rec.freight_terms_code(p_index)
1018 ,OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).freight_terms_code);
1019 p_header_rec.demand_class_code(p_index) := nvl(p_header_rec.demand_class_code(p_index)
1020 ,OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).demand_class_code);
1021 p_header_rec.shipping_method_code(p_index) := nvl(p_header_rec.shipping_method_code(p_index)
1022 ,OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).shipping_method_code);
1023 p_header_rec.shipment_priority_code(p_index) := nvl(p_header_rec.shipment_priority_code(p_index)
1024 ,OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).shipment_priority_code);
1025 p_header_rec.conversion_type_code(p_index) := nvl(p_header_rec.conversion_type_code(p_index)
1026 ,OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).conversion_type_code);
1027
1028 -- Invalid order type - error message populated during validation
1029 EXCEPTION
1030 WHEN NO_DATA_FOUND THEN
1031 oe_debug_pub.add('Order Type cache returns no data found');
1032 x_return_status := FND_API.G_RET_STS_ERROR;
1033 END;
1034
1035 END IF;
1036
1037 -- Constant Value Defaults
1038
1039 IF p_header_rec.pricing_date(p_index) IS NULL THEN
1040 p_header_rec.pricing_date(p_index) := sysdate;
1041 END IF;
1042
1043 IF p_header_rec.ordered_date(p_index) IS NULL THEN
1044 p_header_rec.ordered_date(p_index) := sysdate;
1045 END IF;
1046
1047 IF p_header_rec.request_date(p_index) IS NULL THEN
1048 p_header_rec.request_date(p_index) := sysdate;
1049 END IF;
1050
1051 IF p_header_rec.tax_exempt_flag(p_index) IS NULL THEN
1052 p_header_rec.tax_exempt_flag(p_index) := 'S'; -- 'Standard'
1053 END IF;
1054
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 IF oe_bulk_msg_pub.check_msg_level(oe_bulk_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1058 THEN
1059 oe_bulk_msg_pub.add_exc_msg
1060 ( G_PKG_NAME
1061 , 'Default_Record'
1062 );
1063 END IF;
1064 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1065 END Default_Record;
1066
1067 PROCEDURE Populate_Internal_Fields
1068 (p_header_rec IN OUT NOCOPY OE_Bulk_Order_Pvt.HEADER_REC_TYPE
1069 ,p_index IN NUMBER
1070 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1071 )
1072 IS
1073 l_c_index NUMBER;
1074 l_set_of_books_rec OE_Order_Cache.Set_of_Books_Rec_Type;
1075 l_is_fixed_rate VARCHAR2(1);
1076 BEGIN
1077
1078 -- Header_ID is pre-generated and read from interface tables
1079 -- This is to reduce DB block contention as sequential ids will
1080 -- be assigned to each parallel thread by master import program.
1081 -- (Refer OEBVIMNB.pls)
1082
1083 BEGIN
1084
1085 l_c_index := OE_Bulk_Cache.Load_Order_Type
1086 (p_key => p_header_rec.order_type_id(p_index));
1087
1088 IF OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).order_category_code
1089 = 'RETURN'
1090 THEN
1091 FND_MESSAGE.SET_NAME('ONT','OE_BULK_NOT_SUPP_RETURN');
1092 oe_bulk_msg_pub.Add('Y', 'ERROR');
1093 x_return_status := FND_API.G_RET_STS_ERROR;
1094 ELSE
1095 p_header_rec.order_category_code(p_index) := OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).order_category_code;
1096 END IF;
1097
1098 -- Invalid order type - error message populated during validation
1099 EXCEPTION
1100 WHEN NO_DATA_FOUND THEN
1101 oe_debug_pub.add('Order Type cache returns no data found');
1102 -- Set order category for insert to succeed
1103 p_header_rec.order_category_code(p_index) := 'ORDER';
1104 x_return_status := FND_API.G_RET_STS_ERROR;
1105 END;
1106
1107 IF p_header_rec.shipping_method_code(p_index) IS NOT NULL
1108 AND p_header_rec.ship_from_org_id(p_index) IS NOT NULL
1109 THEN
1110 p_header_rec.freight_carrier_code(p_index) :=
1111 Get_Freight_Carrier
1112 (p_shipping_method_code => p_header_rec.shipping_method_code(p_index)
1113 ,p_ship_from_org_id => p_header_rec.ship_from_org_id(p_index)
1114 );
1115 END IF;
1116
1117 l_set_of_books_rec := OE_Order_Cache.Load_Set_Of_Books;
1118 IF p_header_rec.transactional_curr_code(p_index)
1119 <> l_set_of_books_rec.currency_code
1120 THEN
1121
1122 l_Is_Fixed_Rate :=
1123 GL_CURRENCY_API.IS_FIXED_RATE(
1124 p_header_rec.transactional_curr_code(p_index),
1125 l_set_of_books_rec.currency_code,
1126 nvl(p_header_rec.Ordered_Date(p_index),Sysdate));
1127
1128 IF (L_Is_Fixed_Rate = 'Y') THEN
1129 p_header_rec.Conversion_Type_Code(p_index) := 'EMU FIXED';
1130 END IF;
1131
1132 END IF;
1133
1134 -- QUOTING changes
1135 -- Version number should be initialized to 0 starting 11i10
1136 IF p_header_rec.version_number(p_index) IS NULL THEN
1137 IF OE_Code_Control.Get_Code_Release_Level >= '110510' THEN
1138 p_header_rec.version_number(p_index) := 0;
1139 ELSE
1140 p_header_rec.version_number(p_index) := 1;
1141 END IF;
1142 END IF;
1143
1144 EXCEPTION
1145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1147 WHEN OTHERS THEN
1148 oe_debug_pub.add('others errors, Populate_Internal_Fields');
1149 oe_debug_pub.add(substr(sqlerrm,1,200));
1150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1151 IF oe_bulk_msg_pub.check_msg_level(oe_bulk_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1152 THEN
1153 oe_bulk_msg_pub.add_exc_msg
1154 ( G_PKG_NAME
1155 , 'Populate_Internal_Fields'
1156 );
1157 END IF;
1158 END Populate_Internal_Fields;
1159
1160 -----------------------------------------------------------------------
1161 -- PUBLIC PROCEDURES/FUNCTIONS
1162 -----------------------------------------------------------------------
1163
1164 FUNCTION Get_Freight_Carrier
1165 (p_shipping_method_code IN VARCHAR2
1166 ,p_ship_from_org_id IN VARCHAR2
1167 )
1168 RETURN VARCHAR2
1169 IS
1170 l_freight_code VARCHAR2(80);
1171 BEGIN
1172
1173 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110509' THEN
1174
1175 SELECT freight_code
1176 INTO l_freight_code
1177 FROM wsh_carriers wsh_ca,wsh_carrier_services wsh,
1178 wsh_org_carrier_services wsh_org
1179 WHERE wsh_org.organization_id = p_ship_from_org_id
1180 AND wsh.carrier_service_id = wsh_org.carrier_service_id
1181 AND wsh_ca.carrier_id = wsh.carrier_id
1182 AND wsh.ship_method_code = p_shipping_method_code
1183 AND wsh_org.enabled_flag='Y';
1184 ELSE
1185
1186 SELECT freight_code
1187 INTO l_freight_code
1188 FROM wsh_carrier_ship_methods
1189 WHERE ship_method_code = p_shipping_method_code
1190 AND ORGANIZATION_ID = p_ship_from_org_id;
1191
1192 END IF;
1193
1194 RETURN l_freight_code;
1195
1196 EXCEPTION
1197 WHEN NO_DATA_FOUND THEN
1198 RETURN NULL;
1199 WHEN OTHERS THEN
1200 IF oe_bulk_msg_pub.check_msg_level (oe_bulk_msg_pub.G_MSG_LVL_UNEXP_ERROR)
1201 THEN
1202 oe_bulk_msg_pub.add_exc_msg
1203 ( G_PKG_NAME
1204 ,'Get_Freight_Carrier'
1205 );
1206 END IF;
1207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1208 END Get_Freight_Carrier;
1209
1210 --
1211 -- This FUNCTION is used for all agreement related validations.
1212 --
1213
1214 FUNCTION Validate_Agreement
1215 (p_agreement_id IN NUMBER
1216 ,p_pricing_date IN DATE
1217 ,p_price_list_id IN NUMBER
1218 ,p_sold_to_org_id IN NUMBER
1219 )
1220 RETURN BOOLEAN
1221 IS
1222 l_c_index NUMBER;
1223 BEGIN
1224
1225 -- Load agreement in the cache
1226 l_c_index := OE_Bulk_Cache.Load_Agreement(p_agreement_id);
1227
1228 -- Verify that agreement is effective for the valid dates
1229 IF NOT trunc(nvl(p_pricing_date,sysdate))
1230 BETWEEN trunc(nvl(OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).start_date_active
1231 ,add_months(sysdate,-10000)))
1232 AND trunc(nvl(OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).end_date_active
1233 ,add_months(sysdate,+10000)))
1234 THEN
1235
1236 fnd_message.set_name('ONT', 'ONT_INVALID_AGREEMENT');
1237 fnd_message.set_Token('AGREEMENT_NAME',
1238 OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).name);
1239 fnd_message.set_Token('REVISION',
1240 OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).revision);
1241 oe_bulk_msg_pub.Add('Y', 'ERROR');
1242
1243 RETURN FALSE;
1244
1245 END IF;
1246
1247 -- Verify that price list on header matches price list on agreement
1248 IF p_price_list_id IS NOT NULL
1249 AND Get_Price_List_Type(p_price_list_id) <> 'PRL'
1250 THEN
1251
1252 IF (OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).price_list_id
1253 <> p_price_list_id) THEN
1254
1255 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT_PLIST');
1256 fnd_message.set_Token('AGREEMENT_NAME',
1257 OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).name);
1258 fnd_message.set_Token('PRICE_LIST1',
1259 OE_Bulk_Cache.G_PRICE_LIST_TBL(p_price_list_id).name);
1260 fnd_message.set_Token('PRICE_LIST2',
1261 OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).price_list_id);
1262 oe_bulk_msg_pub.Add('Y', 'ERROR');
1263
1264 RETURN FALSE;
1265
1266 END IF;
1267
1268 END IF;
1269
1270 -- Verify that customer on agreement matches the customer on order or
1271 -- is a related customer if customer relationships is ON.
1272 --Bug 9496609
1273 IF OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).sold_to_org_id is not null
1274 and OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).sold_to_org_id <> -1 then
1275 IF NOT OE_GLOBALS.EQUAL
1276 (OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).sold_to_org_id
1277 ,p_sold_to_org_id)
1278 THEN
1279
1280 IF OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'N'
1281 OR (OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'Y'
1282 AND NOT Is_Related_Customer(p_sold_to_org_id
1283 ,OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).sold_to_org_id))
1284 THEN
1285
1286 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT');
1287 fnd_message.set_Token('AGREEMENT_ID',p_agreement_id);
1288 fnd_message.set_Token('AGREEMENT_NAME',
1289 OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).name);
1290 fnd_message.set_Token('CUSTOMER_ID',
1291 OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).sold_to_org_id);
1292 oe_bulk_msg_pub.Add('Y', 'ERROR');
1293
1294 RETURN FALSE;
1295
1296 END IF;
1297
1298 END IF;
1299
1300 END IF;
1301 --Bug 9496609
1302
1303 RETURN TRUE;
1304
1305 EXCEPTION
1306 WHEN NO_DATA_FOUND THEN
1307 RETURN FALSE;
1308 END Validate_Agreement;
1309
1310
1311 -- This FUNCTION consists of all price list related validations.
1312
1313 FUNCTION Validate_Price_List
1314 (p_price_list_id IN NUMBER
1315 ,p_curr_code IN VARCHAR2
1316 ,p_pricing_date IN DATE
1317 ,p_calculate_price IN VARCHAR2
1318 )
1319 RETURN BOOLEAN
1320 IS
1321
1322 l_validate_result VARCHAR2(1);
1323 l_c_index NUMBER;
1324
1325 BEGIN
1326
1327 -- For order header, this check is NOT needed if any one line exists
1328 -- with calculate_price_flag of 'N' or 'P' - where to do this?
1329
1330 -- For line, price list validations not needed if calculate price is
1331 -- frozen or partial
1332
1333 IF p_calculate_price IN ('P','N') THEN
1334 RETURN TRUE;
1335 END IF;
1336
1337 -- Cache price list
1338
1339 l_c_index := OE_Bulk_Cache.Load_Price_List(p_price_list_id);
1340
1341 -- Verify that price list is effective for the valid dates
1342
1343 IF NOT trunc(nvl(p_pricing_date,sysdate))
1344 BETWEEN trunc(nvl(OE_Bulk_Cache.G_PRICE_LIST_TBL(l_c_index).start_date_active
1345 ,add_months(sysdate,-10000)))
1346 AND trunc(nvl(OE_Bulk_Cache.G_PRICE_LIST_TBL(l_c_index).end_date_active
1347 ,add_months(sysdate,+10000)))
1348 THEN
1349
1350 fnd_message.set_name('ONT', 'OE_INVALID_NONAGR_PLIST');
1351 fnd_message.set_Token('PRICE_LIST1', p_price_list_id);
1352 fnd_message.set_Token('PRICING_DATE', p_pricing_date);
1353 oe_bulk_msg_pub.Add('Y', 'ERROR');
1354
1355 RETURN FALSE;
1356
1357 END IF;
1358
1359 -- Validate currency against the price list
1360
1361 QP_UTIL_PUB.Validate_Price_list_Curr_code(p_price_list_id
1362 ,p_curr_code
1363 ,p_pricing_date
1364 ,l_validate_result);
1365
1366 IF l_validate_result = 'N' THEN
1367
1368 FND_MESSAGE.SET_NAME('ONT','OE_CURRENCY_MISMATCH');
1369 FND_MESSAGE.SET_TOKEN('LINE_CURR_CODE',
1370 OE_Bulk_Cache.G_PRICE_LIST_TBL(l_c_index).currency_code);
1371 FND_MESSAGE.SET_TOKEN('PRICE_LIST_NAME',
1372 OE_Bulk_Cache.G_PRICE_LIST_TBL(l_c_index).name);
1373 FND_MESSAGE.SET_TOKEN('HEADER_CURR_CODE',p_curr_code);
1374 oe_bulk_msg_pub.Add('Y', 'ERROR');
1375
1376 RETURN FALSE;
1377
1378 END IF;
1379
1380 RETURN TRUE;
1381
1382 EXCEPTION
1383 WHEN NO_DATA_FOUND THEN
1384 oe_debug_pub.add('no data found in validate price list');
1385 RETURN FALSE;
1386 END Validate_Price_List;
1387
1388
1389 FUNCTION Validate_Ship_to(p_sold_to IN NUMBER,
1390 p_Ship_to IN NUMBER)
1391 RETURN BOOLEAN
1392 IS
1393 l_dummy VARCHAR2(10);
1394 l_c_index NUMBER;
1395 BEGIN
1396
1397 --
1398 -- Validations here account for all customer relationships
1399 --
1400 -- If ship_to_org_id is not valid for any customer,
1401 -- then Cache FUNCTION Load_<Entity> will raise no data found
1402 -- so for all relationship values including G_CUST_RELATIONS = 'A'
1403 -- , it will return FALSE.
1404 --
1405 -- If customer relationships = 'N' but customer does not match
1406 -- ship to customer, it will return FALSE.
1407 --
1408 -- If customer relationships = 'Y', it will also check if ship
1409 -- to customer is a related customer and if not, return FALSE.
1410 --
1411
1412 l_c_index := OE_Bulk_Cache.Load_Ship_To
1413 (p_key => p_ship_to);
1414
1415 IF OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).customer_id
1416 = p_sold_to
1417 THEN
1418 RETURN TRUE;
1419 END IF;
1420
1421 IF OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'N' THEN
1422
1423 RETURN FALSE;
1424
1425 ELSIF OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'Y' THEN
1426
1427 SELECT 'VALID'
1428 INTO l_dummy
1429 FROM HZ_CUST_ACCT_RELATE
1430 WHERE RELATED_CUST_ACCOUNT_ID = p_sold_to
1431 AND CUST_ACCOUNT_ID = OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).customer_id
1432 AND ship_to_flag = 'Y' AND STATUS='A';
1433
1434 END IF;
1435
1436 RETURN TRUE;
1437
1438 EXCEPTION
1439 WHEN NO_DATA_FOUND THEN
1440 RETURN FALSE;
1441 END Validate_Ship_to;
1442
1443 --abghosh
1444
1445 FUNCTION Validate_Sold_to_site(p_sold_to IN NUMBER,
1446 p_Sold_to_site_use_id IN NUMBER)
1447 RETURN BOOLEAN
1448 IS
1449 l_dummy VARCHAR2(10);
1450 l_c_index NUMBER;
1451 BEGIN
1452
1453 --
1454 --
1455 -- If sold_to_site_use_id is not valid for any customer,
1456 -- then Cache FUNCTION Load_<Entity> will raise no data found
1457 -- , it will return FALSE.
1458 --
1459 --
1460 --
1461
1462 l_c_index := OE_Bulk_Cache.Load_Sold_to_site
1463 (p_key => p_sold_to_site_use_id);
1464
1465 IF OE_Bulk_Cache.G_SOLD_TO_SITE_TBL(l_c_index).customer_id
1466 = p_sold_to
1467 THEN
1468 RETURN TRUE;
1469 ELSE
1470 RETURN FALSE;
1471 END IF;
1472
1473
1474 EXCEPTION
1475 WHEN NO_DATA_FOUND THEN
1476 RETURN FALSE;
1477 END Validate_Sold_to_site;
1478
1479
1480 FUNCTION Validate_Bill_to(p_sold_to IN NUMBER,
1481 p_bill_to IN NUMBER)
1482 RETURN BOOLEAN
1483 IS
1484 l_dummy VARCHAR2(10);
1485 l_c_index NUMBER;
1486 BEGIN
1487
1488 --
1489 -- Validations here account for all customer relationships
1490 --
1491 -- If invoice_to_org_id is not valid for any customer,
1492 -- then Cache FUNCTION Load_<Entity> will raise no data found
1493 -- so for all relationship values including G_CUST_RELATIONS = 'A'
1494 -- , it will return FALSE.
1495 --
1496 -- If customer relationships = 'N' but customer does not match
1497 -- invoice to customer, it will return FALSE.
1498 --
1499 -- If customer relationships = 'Y', it will also check if invoice
1500 -- to customer is a related customer and if not, return FALSE.
1501 --
1502
1503 l_c_index := OE_Bulk_Cache.Load_Invoice_To
1504 (p_key => p_bill_to);
1505
1506 IF OE_Bulk_Cache.G_INVOICE_TO_TBL(l_c_index).customer_id
1507 = p_sold_to
1508 THEN
1509 RETURN TRUE;
1510 END IF;
1511
1512 IF OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'N' THEN
1513
1514 RETURN FALSE;
1515
1516 ELSIF OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'Y' THEN
1517
1518 SELECT 'VALID'
1519 INTO l_dummy
1520 FROM HZ_CUST_ACCT_RELATE
1521 WHERE RELATED_CUST_ACCOUNT_ID = p_sold_to
1522 AND CUST_ACCOUNT_ID = OE_Bulk_Cache.G_INVOICE_TO_TBL(l_c_index).customer_id
1523 AND bill_to_flag = 'Y' AND STATUS='A';
1524
1525 END IF;
1526
1527 RETURN TRUE;
1528
1529 EXCEPTION
1530 WHEN NO_DATA_FOUND THEN
1531 RETURN FALSE;
1532 END Validate_Bill_to;
1533
1534
1535 FUNCTION Validate_Deliver_to(p_sold_to IN NUMBER,
1536 p_deliver_to IN NUMBER)
1537 RETURN BOOLEAN
1538 IS
1539 l_dummy VARCHAR2(10);
1540 --bug 4729536
1541 Cursor cur_customer_relations is
1542 SELECT /*MOAC_SQL_NO_CHANGE */ 'VALID'
1543 FROM oe_deliver_to_orgs_v
1544 WHERE site_use_id = p_deliver_to
1545 AND status = 'A'
1546 AND customer_id = p_sold_to
1547 AND ROWNUM = 1
1548
1549 UNION ALL
1550
1551 SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
1552 FROM oe_deliver_to_orgs_v odto
1553 WHERE site_use_id = p_deliver_to
1554 AND status = 'A'
1555 AND exists
1556 (
1557 SELECT 1
1558 FROM HZ_CUST_ACCT_RELATE hcar
1559 WHERE hcar.CUST_ACCOUNT_ID = odto.customer_id
1560 AND hcar.RELATED_CUST_ACCOUNT_ID = p_sold_to
1561 AND hcar.ship_to_flag = 'Y'
1562 AND hcar.STATUS ='A'
1563 )
1564 AND ROWNUM=1;
1565
1566 BEGIN
1567
1568 IF (OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'N') THEN
1569
1570 SELECT 'VALID'
1571 INTO l_dummy
1572 FROM oe_deliver_to_orgs_v
1573 WHERE customer_id = p_sold_to
1574 AND site_use_id = p_deliver_to
1575 AND status = 'A';
1576
1577 ELSE
1578
1579 /*SELECT /*MOAC_SQL_NO_CHANGE 'VALID'
1580 INTO l_dummy
1581 FROM oe_deliver_to_orgs_v
1582 WHERE site_use_id = p_deliver_to
1583 AND status = 'A'
1584 AND customer_id IN (
1585 Select p_sold_to from dual
1586 union
1587 SELECT CUST_ACCOUNT_ID
1588 FROM HZ_CUST_ACCT_RELATE
1589 WHERE RELATED_CUST_ACCOUNT_ID = p_sold_to
1590 AND ship_to_flag = 'Y' AND STATUS ='A'
1591 );*/
1592 --bug 4729536
1593 Open Cur_Customer_Relations;
1594 Fetch Cur_Customer_Relations into l_dummy;
1595 Close Cur_Customer_Relations;
1596 --bug 4729536
1597
1598 END IF;
1599
1600 return TRUE;
1601
1602 EXCEPTION
1603 WHEN NO_DATA_FOUND THEN
1604 return FALSE;
1605
1606 END Validate_Deliver_to;
1607
1608
1609 -- Validate that contact is valid for given (site)customer and usage.
1610 -- Validate Active status, since contact does NOT go thru attribute
1611 -- validation.
1612
1613 FUNCTION Validate_Site_Contact
1614 (p_site_use_id IN NUMBER
1615 ,p_contact_id IN NUMBER
1616 )
1617 RETURN BOOLEAN
1618 IS
1619 l_dummy VARCHAR2(10);
1620 BEGIN
1621
1622 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
1623 INTO l_dummy
1624 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
1625 HZ_CUST_SITE_USES_ALL SITE,
1626 HZ_CUST_ACCT_SITES ADDR
1627 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
1628 AND ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID
1629 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1630 AND ADDR.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
1631 AND SITE.SITE_USE_ID = p_site_use_id
1632 AND SITE.STATUS = 'A'
1633 AND ACCT_ROLE.STATUS = 'A'
1634 AND ADDR.STATUS ='A' ;-- added for bug 2752321
1635
1636 RETURN TRUE;
1637
1638 EXCEPTION
1639 WHEN NO_DATA_FOUND THEN
1640 RETURN FALSE;
1641 END Validate_Site_Contact;
1642
1643
1644 ---------------------------------------------------------------------
1645 -- PROCEDURE Entity
1646 --
1647 -- Main processing procedure used to process headers in a batch.
1648 -- IN parameters -
1649 -- p_header_rec : order headers in this batch
1650 -- p_defaulting_mode : 'Y' if fixed defaulting is needed, 'N' if
1651 -- defaulting is to be completely bypassed
1652 -- OUT NOCOPY /* file.sql.39 change */ parameters -
1653 -- x_header_scredit_rec : sales credits for headers processed
1654 --
1655 -- Processing steps include:
1656 -- 1. Restricted defaulting on p_header_rec if defaulting_mode is 'Y'
1657 -- 2. Populate all internal fields on p_header_rec
1658 -- 3. All entity validations
1659 -- 4. Other misc processing like holds evaluation, sales credits.
1660 ---------------------------------------------------------------------
1661
1662 PROCEDURE Entity
1663 ( p_header_rec IN OUT NOCOPY OE_Bulk_Order_Pvt.HEADER_REC_TYPE
1664 , x_header_scredit_rec IN OUT NOCOPY OE_Bulk_Order_Pvt.SCREDIT_REC_TYPE
1665 , p_defaulting_mode IN VARCHAR2
1666 , p_process_configurations IN VARCHAR2 DEFAULT 'N'
1667 , p_validate_configurations IN VARCHAR2 DEFAULT 'Y'
1668 , p_schedule_configurations IN VARCHAR2 DEFAULT 'N'
1669 , p_validate_desc_flex IN VARCHAR2
1670 )
1671 IS
1672
1673 ctr NUMBER;
1674 error_count NUMBER := 0;
1675 l_dummy VARCHAR2(10);
1676 l_return_status VARCHAR2(30);
1677 l_c_index NUMBER;
1678 l_order_type_id NUMBER;
1679 l_on_generic_hold BOOLEAN;
1680 l_on_booking_hold BOOLEAN;
1681 l_on_scheduling_hold BOOLEAN;
1682 l_scredit_index NUMBER := 1;
1683 l_gapless_sequence VARCHAR2(1) := 'N';
1684 l_hold_ii_flag VARCHAR2(1); -- Added to support out parameter
1685 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1686 l_header_rec_for_hold OE_Order_PUB.Header_Rec_Type; --ER#7479609
1687
1688 BEGIN
1689 ctr := p_header_rec.header_id.COUNT;
1690
1691 FOR i IN 1..ctr LOOP
1692
1693 -- Set Context for messags
1694
1695 oe_bulk_msg_pub.set_msg_context
1696 ( p_entity_code => 'HEADER'
1697 ,p_entity_id => p_header_rec.header_id(i)
1698 ,p_header_id => p_header_rec.header_id(i)
1699 ,p_line_id => null
1700 ,p_orig_sys_document_ref => p_header_rec.orig_sys_document_ref(i)
1701 ,p_orig_sys_document_line_ref => null
1702 ,p_order_source_id => p_header_rec.order_source_id(i)
1703 ,p_source_document_type_id => null
1704 ,p_source_document_id => null
1705 ,p_source_document_line_id => null);
1706
1707 --PIB
1708 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1709 p_header_rec.header_index.extend(1);
1710 p_header_rec.header_index(i) := i;
1711 p_header_rec.event_code.extend(1);
1712 IF p_header_rec.booked_flag(i) = 'Y' THEN
1713 p_header_rec.event_code(i) := 'BATCH,BOOK';
1714 ELSE
1715 p_header_rec.event_code(i) := 'BATCH';
1716 END IF;
1717 If l_debug_level > 0 Then
1718 oe_debug_pub.add('event code : '||p_header_rec.event_code(i));
1719 End If;
1720 END IF;
1721 --PIB
1722
1723 ---------------------------------------------------------
1724 -- CALL THE FIXED DEFAULTING PROCEDURE IF NEEDED
1725 ---------------------------------------------------------
1726
1727 IF p_defaulting_mode = 'Y' THEN
1728
1729 Default_Record
1730 ( p_header_rec => p_header_rec
1731 ,p_index => i
1732 ,x_return_status => l_return_status
1733 );
1734
1735 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1736 p_header_rec.lock_control(i) := -99;
1737 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1739 END IF;
1740
1741 END IF;
1742
1743 ---------------------------------------------------------
1744 -- POPULATE INTERNAL FIELDS
1745 -- Hardcoded Defaulting From OEXDHDRB.pls
1746 ---------------------------------------------------------
1747
1748 Populate_Internal_Fields
1749 ( p_header_rec => p_header_rec
1750 ,p_index => i
1751 ,x_return_status => l_return_status
1752 );
1753
1754 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1755 p_header_rec.lock_control(i) := -99;
1756 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1757 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1758 END IF;
1759
1760
1761 ---------------------------------------------------------
1762 -- START ENTITY VALIDATIONS
1763 ---------------------------------------------------------
1764
1765 -- Validate Required Attributes
1766
1767 -- Order Type is required
1768 IF (p_header_rec.order_type_id(i) IS NOT NULL) THEN
1769
1770 l_order_type_id := p_header_rec.order_type_id(i);
1771
1772 -- Get Order Number - will return FALSE IF
1773 -- 1. Doc Sequence type is 'Manual' and order number not specified
1774 -- 2. If gapless sequence
1775 -- Error messages are populated in Get_Order_Number
1776
1777 IF NOT Get_order_number(p_header_rec.order_type_id(i),
1778 p_header_rec.order_number(i),
1779 l_gapless_sequence ) THEN
1780
1781 IF l_gapless_sequence = 'Y' THEN
1782 p_header_rec.lock_control(i) := -98;
1783 ELSE
1784 p_header_rec.lock_control(i) := -99;
1785 END IF;
1786
1787 -- Order Number is a required column hence put a dummy value
1788 -- for the insert to succeed.
1789 p_header_rec.order_number(i) := -1 * p_header_rec.header_id(i);
1790
1791 END IF;
1792
1793 -- Do other order type related validations
1794
1795 -- Cache order type, if order type is not valid caching will fail
1796 -- with no data found error.
1797 l_c_index := OE_Bulk_Cache.Load_Order_Type
1798 (p_key => l_order_type_id);
1799
1800 -- Check if Order Type is valid for ordered date
1801
1802 IF p_header_rec.ordered_date(i) IS NOT NULL
1803 AND NOT (p_header_rec.ordered_date(i) BETWEEN
1804 nvl(OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_order_type_id).start_date_active,sysdate)
1805 AND nvl(OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_order_type_id).end_date_active,sysdate))
1806 THEN
1807 p_header_rec.lock_control(i) := -99;
1808 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1809 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1810 OE_Order_Util.Get_Attribute_Name('ORDER_TYPE_ID'));
1811 oe_bulk_msg_pub.Add('Y', 'ERROR');
1812 END IF;
1813
1814 -- Validate that Order Type has valid WF assignment
1815
1816 IF NOT OE_BULK_WF_UTIL.Validate_OT_WF_Assignment
1817 (p_header_rec.order_type_id(i)
1818 ,p_header_rec.wf_process_name(i)) THEN
1819 p_header_rec.lock_control(i) := -99;
1820 FND_MESSAGE.SET_NAME('ONT','OE_MISS_FLOW');
1821 oe_bulk_msg_pub.Add('Y', 'ERROR');
1822 END IF;
1823
1824 -- Populate order type name, this is denormalized onto header
1825 -- record as it will be used in insert into mtl_sales_orders
1826
1827 p_header_rec.order_type_name(i) :=
1828 OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).NAME;
1829
1830 ELSE
1831 p_header_rec.lock_control(i) := -99;
1832 FND_MESSAGE.SET_NAME('ONT','OE_ATTRIBUTE_REQUIRED');
1833 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1834 OE_Order_UTIL.Get_Attribute_Name('ORDER_TYPE_ID'));
1835 oe_bulk_msg_pub.Add('Y', 'ERROR');
1836
1837 -- To avoid Insert failure, populate not null column.
1838 -- This record will be deleted later.
1839
1840 p_header_rec.order_type_id(i) := -99;
1841
1842 END IF; -- Order Type is not null
1843
1844
1845 -- Check that Transactional currency exists
1846 -- If it does validate Price List currency against it.
1847
1848 IF (p_header_rec.transactional_curr_code(i) IS NULL) THEN
1849
1850 p_header_rec.lock_control(i) := -99;
1851
1852 -- To avoid Insert failure, populate not null column.
1853 -- This record will be deleted later.
1854 p_header_rec.transactional_curr_code(i) := '-99';
1855
1856 FND_MESSAGE.SET_NAME('ONT','OE_ATTRIBUTE_REQUIRED');
1857 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1858 OE_Order_UTIL.Get_Attribute_Name('TRANSACTIONAL_CURR_CODE'));
1859 oe_bulk_msg_pub.Add('Y', 'ERROR');
1860
1861 END IF;
1862
1863
1864 -- Price List Validations
1865
1866 IF (p_header_rec.price_list_id(i) IS NOT NULL) THEN
1867
1868 IF NOT Validate_Price_List(p_header_rec.price_list_id(i)
1869 ,p_header_rec.transactional_curr_code(i)
1870 ,p_header_rec.pricing_date(i)
1871 ,'Y'
1872 )
1873 THEN
1874
1875 p_header_rec.lock_control(i) := -99 ;
1876
1877 END IF;
1878
1879 END IF;
1880
1881
1882 -- Conversion rate, date should be null when conversion type is null.
1883
1884 IF (p_header_rec.conversion_type_code(i) IS NULL) THEN
1885 IF p_header_rec.conversion_rate(i) IS NOT NULL
1886 OR p_header_rec.conversion_rate_date(i) IS NOT NULL
1887 THEN
1888 p_header_rec.lock_control(i) := -99;
1889 FND_MESSAGE.SET_NAME('ONT','OE_VAL_CONVERSION_TYPE');
1890 oe_bulk_msg_pub.Add('Y', 'ERROR');
1891 END IF;
1892 END IF;
1893
1894 -- Conversion rate must be Null if coversion type is not User
1895 -- Removed the validation for conversion date Bug 3220059
1896 IF p_header_rec.conversion_type_code(i) <> 'User' AND
1897 p_header_rec.conversion_rate(i) IS NOT NULL
1898 THEN
1899 l_return_status := FND_API.G_RET_STS_ERROR;
1900 FND_MESSAGE.SET_NAME('ONT','OE_VALIDATION_CONV_TYPE');
1901 OE_MSG_PUB.ADD;
1902
1903 END IF; -- END of checks based on conversion type
1904
1905
1906 -- Agreement related validations
1907
1908 IF (p_header_rec.agreement_id(i) IS NOT NULL) THEN
1909
1910 -- Error messages are populated in Validate_Agreement
1911
1912 IF NOT Validate_Agreement(p_header_rec.agreement_id(i)
1913 ,p_header_rec.pricing_date(i)
1914 ,p_header_rec.price_list_id(i)
1915 ,p_header_rec.sold_to_org_id(i)
1916 )
1917 THEN
1918
1919 p_header_rec.lock_control(i) := -99;
1920
1921 END IF;
1922
1923 END IF;
1924
1925
1926 -- BEGIN: Site, Contact Validations
1927
1928 -- Validate Sold-to Contact
1929 -- Validate Active status, since contact does go thru attribute
1930 -- validation.
1931 IF (p_header_rec.sold_to_contact_id(i) IS NOT NULL) THEN
1932 BEGIN
1933
1934 SELECT 'VALID'
1935 INTO l_dummy
1936 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
1937 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.sold_to_contact_id(i)
1938 AND ACCT_ROLE.CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id(i)
1939 AND ROWNUM = 1
1940 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1941 AND STATUS = 'A';
1942
1943 EXCEPTION
1944 WHEN NO_DATA_FOUND THEN
1945 p_header_rec.lock_control(i) := -99;
1946 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1947 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1948 OE_Order_Util.Get_Attribute_Name('SOLD_TO_CONTACT_ID'));
1949 oe_bulk_msg_pub.Add('Y', 'ERROR');
1950 END;
1951 END IF; -- End sold to contact validation
1952
1953 -- Validate Bill-to for customer
1954 IF (p_header_rec.invoice_to_org_id(i) IS NOT NULL) THEN
1955
1956 IF NOT Validate_Bill_To(p_header_rec.sold_to_org_id(i),
1957 p_header_rec.invoice_to_org_id(i))
1958 THEN
1959
1960 p_header_rec.lock_control(i) := -99;
1961 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1962 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1963 OE_Order_Util.Get_Attribute_Name('invoice_to_org_id'));
1964 oe_bulk_msg_pub.Add('Y', 'ERROR');
1965
1966 END IF;
1967
1968 END IF; -- Invoice to is not null
1969
1970 -- Validate Ship To for customer
1971 IF (p_header_rec.ship_to_org_id(i) IS NOT NULL) THEN
1972
1973 IF NOT Validate_Ship_To(p_header_rec.sold_to_org_id(i),
1974 p_header_rec.ship_to_org_id(i))
1975 THEN
1976
1977 p_header_rec.lock_control(i) := -99;
1978 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1979 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1980 OE_Order_Util.Get_Attribute_Name('SHIP_TO_ORG_ID'));
1981 oe_bulk_msg_pub.Add('Y', 'ERROR');
1982
1983 END IF;
1984
1985 END IF; -- Ship to is not null
1986
1987 --abghosh
1988 -- Validate sold_to_site_use_id for customer
1989 IF (p_header_rec.sold_to_site_use_id(i) IS NOT NULL) THEN
1990
1991 IF NOT Validate_Sold_to_site(p_header_rec.sold_to_org_id(i),
1992 p_header_rec.sold_to_site_use_id(i))
1993 THEN
1994
1995 p_header_rec.lock_control(i) := -99;
1996 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1997 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1998 OE_Order_Util.Get_Attribute_Name('SOLD_TO_SITE_USE_ID'));
1999 oe_bulk_msg_pub.Add('Y', 'ERROR');
2000
2001 END IF;
2002
2003 END IF;
2004
2005 --{bug 5054618
2006 -- end customer
2007 IF (p_header_rec.end_Customer_id(i) IS NOT NULL) THEN
2008 IF NOT Validate_End_Customer( p_header_rec.end_Customer_id(i)) THEN
2009 p_header_rec.lock_control(i) := -99;
2010 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2011 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_id'));
2012 oe_bulk_msg_pub.Add('Y', 'ERROR');
2013 END IF;
2014 End IF;
2015
2016 IF (p_header_rec.end_Customer_contact_id(i) IS NOT NULL) THEN
2017 IF NOT Validate_End_Customer_contact( p_header_rec.end_Customer_contact_id(i)) THEN
2018 p_header_rec.lock_control(i) := -99;
2019 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2020 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_contact_id'));
2021 oe_bulk_msg_pub.Add('Y', 'ERROR');
2022 END IF;
2023 END IF;
2024
2025 IF (p_header_rec.end_Customer_site_use_id(i) IS NOT NULL) THEN
2026 IF NOT Validate_End_Customer_site_use(p_header_rec.end_Customer_site_use_id(i),p_header_rec.end_customer_id(i)) THEN
2027 p_header_rec.lock_control(i) := -99;
2028 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2029 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_site_use_id'));
2030 oe_bulk_msg_pub.Add('Y', 'ERROR');
2031 END IF;
2032 END IF;
2033
2034 IF (p_header_rec.IB_owner(i) IS NOT NULL) THEN
2035 IF NOT validate_IB_Owner(p_header_rec.IB_Owner(i)) THEN
2036 p_header_rec.lock_control(i) := -99;
2037 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2038 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('IB_Owner'));
2039 oe_bulk_msg_pub.Add('Y', 'ERROR');
2040 END IF;
2041 END IF;
2042
2043 IF (p_header_rec.IB_current_location(i) IS NOT NULL) THEN
2044 IF NOT Validate_IB_current_location( p_header_rec.IB_current_Location(i)) THEN
2045 p_header_rec.lock_control(i) := -99;
2046 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2047 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('IB_location'));
2048 oe_bulk_msg_pub.Add('Y', 'ERROR');
2049 END IF;
2050 END IF;
2051
2052 IF (p_header_rec.IB_Installed_at_location(i) IS NOT NULL) THEN
2053 IF NOT Validate_IB_Inst_loc( p_header_rec.IB_Installed_at_location(i)) THEN
2054 p_header_rec.lock_control(i) := -99;
2055 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2056 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_site_use_id'));
2057 oe_bulk_msg_pub.Add('Y', 'ERROR');
2058 END IF;
2059 END IF;
2060 --bug 5054618}
2061
2062 -- Validate Deliver-to for customer
2063 IF (p_header_rec.deliver_to_org_id(i) IS NOT NULL) THEN
2064
2065 IF NOT Validate_Deliver_To(p_header_rec.sold_to_org_id(i)
2066 ,p_header_rec.deliver_to_org_id(i))
2067 THEN
2068
2069 p_header_rec.lock_control(i) := -99;
2070 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2071 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2072 OE_Order_Util.Get_Attribute_Name('DELIVER_TO_ORG_ID'));
2073 oe_bulk_msg_pub.Add('Y', 'ERROR');
2074
2075 END IF;
2076
2077 END IF; -- deliver to is not null
2078
2079 -- Validate Bill to contact
2080 IF (p_header_rec.invoice_to_contact_id(i) IS NOT NULL) THEN
2081
2082 IF NOT Validate_Site_Contact(p_header_rec.invoice_to_org_id(i)
2083 ,p_header_rec.invoice_to_contact_id(i)
2084 )
2085 THEN
2086
2087 p_header_rec.lock_control(i) := -99;
2088 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2089 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2090 OE_Order_Util.Get_Attribute_Name('invoice_to_contact_id'));
2091 oe_bulk_msg_pub.Add('Y', 'ERROR');
2092
2093 END IF;
2094
2095 END IF;
2096
2097 -- Validate ship to contact
2098 IF (p_header_rec.ship_to_contact_id(i) IS NOT NULL) THEN
2099
2100 IF NOT Validate_Site_Contact(p_header_rec.ship_to_org_id(i)
2101 ,p_header_rec.ship_to_contact_id(i)
2102 )
2103 THEN
2104
2105 p_header_rec.lock_control(i) := -99;
2106 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2107 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2108 OE_Order_Util.Get_Attribute_Name('SHIP_TO_CONTACT_ID'));
2109 oe_bulk_msg_pub.Add('Y', 'ERROR');
2110
2111 END IF;
2112
2113 END IF;
2114
2115 -- Validate deliver to contact
2116 IF (p_header_rec.deliver_to_contact_id(i) IS NOT NULL) THEN
2117
2118 IF NOT Validate_Site_Contact(p_header_rec.deliver_to_org_id(i)
2119 ,p_header_rec.deliver_to_contact_id(i)
2120 )
2121 THEN
2122
2123 p_header_rec.lock_control(i) := -99;
2124 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2125 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2126 OE_Order_Util.Get_Attribute_Name('DELIVER_TO_CONTACT_ID'));
2127 oe_bulk_msg_pub.Add('Y', 'ERROR');
2128
2129 END IF;
2130
2131 END IF;
2132
2133 -- END: Site, Contact Validations
2134
2135
2136 -- BEGIN: Check for Tax Exemption attributes
2137
2138 IF (p_header_rec.tax_exempt_flag(i) = 'E') THEN
2139
2140 -- Tax exempt reason code is required
2141 IF (p_header_rec.tax_exempt_reason_code(i) IS NULL) THEN
2142
2143 p_header_rec.lock_control(i) := -99;
2144 FND_MESSAGE.SET_NAME('ONT','OE_ATTRIBUTE_REQUIRED');
2145 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2146 OE_Order_UTIL.Get_Attribute_Name('TAX_EXEMPT_REASON_CODE'));
2147 oe_bulk_msg_pub.Add('Y', 'ERROR');
2148
2149 ELSIF NOT Valid_Tax_Exempt_Reason
2150 (p_header_rec.tax_exempt_reason_code(i)) THEN
2151
2152 p_header_rec.lock_control(i) := -99;
2153 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2154 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2155 OE_Order_UTIL.Get_Attribute_Name('TAX_EXEMPT_REASON_CODE'));
2156 oe_bulk_msg_pub.Add('Y', 'ERROR');
2157
2158 END IF;
2159
2160 ELSIF (p_header_rec.tax_exempt_flag(i) = 'R') THEN
2161
2162 IF (p_header_rec.tax_exempt_number(i) IS NOT NULL)
2163 OR (p_header_rec.tax_exempt_reason_code(i) IS NOT NULL)
2164 THEN
2165
2166 p_header_rec.lock_control(i) := -99;
2167 FND_MESSAGE.SET_NAME('ONT','OE_TAX_EXEMPTION_NOT_ALLOWED');
2168 oe_bulk_msg_pub.Add('Y', 'ERROR');
2169
2170 END IF;
2171
2172 -- Validate Tax Exempt # and reason for this customer and site
2173 ELSIF (p_header_rec.tax_exempt_flag(i) = 'S')
2174 AND (p_header_rec.tax_exempt_number(i) IS NOT NULL)
2175 AND(p_header_rec.tax_exempt_reason_code(i) IS NOT NULL)
2176 THEN
2177
2178 --commented for bug 7685103
2179 -- No need to validate execption number irrespective of tax_exempt_flags
2180 /* IF NOT Valid_Tax_Exemptions(p_header_rec.tax_exempt_number(i)
2181 ,p_header_rec.tax_exempt_reason_code(i)
2182 ,p_header_rec.ship_to_org_id(i)
2183 ,p_header_rec.invoice_to_org_id(i)
2184 ,p_header_rec.sold_to_org_id(i)
2185 ,p_header_rec.request_date(i)
2186 )
2187 THEN
2188
2189 p_header_rec.lock_control(i) := -99;
2190 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2191 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2192 OE_Order_Util.Get_Attribute_Name('tax_exempt_number'));
2193 oe_bulk_msg_pub.Add('Y', 'ERROR');
2194
2195 END IF;
2196 */
2197 NULL;
2198 END IF;
2199
2200 -- END: Check for Tax Exemption attributes
2201
2202
2203 -- Duplicate PO Number Validation
2204
2205 IF p_header_rec.cust_po_number(i) IS NOT NULL THEN
2206
2207 IF OE_Validate_Header.Is_Duplicate_PO_Number
2208 (p_header_rec.cust_po_number(i)
2209 ,p_header_rec.sold_to_org_id(i)
2210 ,p_header_rec.header_id(i)
2211 )
2212 THEN
2213 FND_MESSAGE.SET_NAME('ONT','OE_VAL_DUP_PO_NUMBER');
2214 oe_bulk_msg_pub.ADD;
2215 END IF;
2216
2217 END IF;
2218
2219
2220 -- BEGIN: Desc Flex Validation
2221
2222 IF p_validate_desc_flex = 'Y' THEN
2223
2224 IF OE_Bulk_Order_Pvt.G_OE_HEADER_ATTRIBUTES = 'Y' THEN
2225
2226 IF NOT OE_VALIDATE.Header_Desc_Flex
2227 (p_context => p_header_rec.context(i)
2228 ,p_attribute1 => p_header_rec.attribute1(i)
2229 ,p_attribute2 => p_header_rec.attribute2(i)
2230 ,p_attribute3 => p_header_rec.attribute3(i)
2231 ,p_attribute4 => p_header_rec.attribute4(i)
2232 ,p_attribute5 => p_header_rec.attribute5(i)
2233 ,p_attribute6 => p_header_rec.attribute6(i)
2234 ,p_attribute7 => p_header_rec.attribute7(i)
2235 ,p_attribute8 => p_header_rec.attribute8(i)
2236 ,p_attribute9 => p_header_rec.attribute9(i)
2237 ,p_attribute10 => p_header_rec.attribute10(i)
2238 ,p_attribute11 => p_header_rec.attribute11(i)
2239 ,p_attribute12 => p_header_rec.attribute12(i)
2240 ,p_attribute13 => p_header_rec.attribute13(i)
2241 ,p_attribute14 => p_header_rec.attribute14(i)
2242 ,p_attribute15 => p_header_rec.attribute15(i)
2243 ,p_attribute16 => p_header_rec.attribute16(i) -- for bug 2184255
2244 ,p_attribute17 => p_header_rec.attribute17(i)
2245 ,p_attribute18 => p_header_rec.attribute18(i)
2246 ,p_attribute19 => p_header_rec.attribute19(i)
2247 ,p_attribute20 => p_header_rec.attribute20(i))
2248 THEN
2249 p_header_rec.lock_control(i) := -99;
2250 -- Log Error Message
2251 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2252 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2253 'Entity:Flexfield:Header_Desc_Flex');
2254 oe_bulk_msg_pub.Add('Y', 'ERROR');
2255 ELSE -- if the flex validation is successfull
2256 -- For bug 2511313
2257 IF p_header_rec.context(i) IS NULL
2258 OR p_header_rec.context(i) = FND_API.G_MISS_CHAR THEN
2259 p_header_rec.context(i) := oe_validate.g_context;
2260 END IF;
2261
2262 IF p_header_rec.attribute1(i) IS NULL
2263 OR p_header_rec.attribute1(i) = FND_API.G_MISS_CHAR THEN
2264 p_header_rec.attribute1(i) := oe_validate.g_attribute1;
2265 END IF;
2266
2267 IF p_header_rec.attribute2(i) IS NULL
2268 OR p_header_rec.attribute2(i) = FND_API.G_MISS_CHAR THEN
2269 p_header_rec.attribute2(i) := oe_validate.g_attribute2;
2270 END IF;
2271
2272 IF p_header_rec.attribute3(i) IS NULL
2273 OR p_header_rec.attribute3(i) = FND_API.G_MISS_CHAR THEN
2274 p_header_rec.attribute3(i) := oe_validate.g_attribute3;
2275 END IF;
2276
2277 IF p_header_rec.attribute4(i) IS NULL
2278 OR p_header_rec.attribute4(i) = FND_API.G_MISS_CHAR THEN
2279 p_header_rec.attribute4(i) := oe_validate.g_attribute4;
2280 END IF;
2281
2282 IF p_header_rec.attribute5(i) IS NULL
2283 OR p_header_rec.attribute5(i) = FND_API.G_MISS_CHAR THEN
2284 p_header_rec.attribute5(i) := oe_validate.g_attribute5;
2285 END IF;
2286
2287 IF p_header_rec.attribute6(i) IS NULL
2288 OR p_header_rec.attribute6(i) = FND_API.G_MISS_CHAR THEN
2289 p_header_rec.attribute6(i) := oe_validate.g_attribute6;
2290 END IF;
2291
2292 IF p_header_rec.attribute7(i) IS NULL
2293 OR p_header_rec.attribute7(i) = FND_API.G_MISS_CHAR THEN
2294 p_header_rec.attribute7(i) := oe_validate.g_attribute7;
2295 END IF;
2296
2297 IF p_header_rec.attribute8(i) IS NULL
2298 OR p_header_rec.attribute8(i) = FND_API.G_MISS_CHAR THEN
2299 p_header_rec.attribute8(i) := oe_validate.g_attribute8;
2300 END IF;
2301
2302 IF p_header_rec.attribute9(i) IS NULL
2303 OR p_header_rec.attribute9(i) = FND_API.G_MISS_CHAR THEN
2304 p_header_rec.attribute9(i) := oe_validate.g_attribute9;
2305 END IF;
2306
2307 IF p_header_rec.attribute10(i) IS NULL
2308 OR p_header_rec.attribute10(i) = FND_API.G_MISS_CHAR THEN
2309 p_header_rec.attribute10(i) := Oe_validate.G_attribute10;
2310 End IF;
2311
2312 IF p_header_rec.attribute11(i) IS NULL
2313 OR p_header_rec.attribute11(i) = FND_API.G_MISS_CHAR THEN
2314 p_header_rec.attribute11(i) := oe_validate.g_attribute11;
2315 END IF;
2316
2317 IF p_header_rec.attribute12(i) IS NULL
2318 OR p_header_rec.attribute12(i) = FND_API.G_MISS_CHAR THEN
2319 p_header_rec.attribute12(i) := oe_validate.g_attribute12;
2320 END IF;
2321
2322 IF p_header_rec.attribute13(i) IS NULL
2323 OR p_header_rec.attribute13(i) = FND_API.G_MISS_CHAR THEN
2324 p_header_rec.attribute13(i) := oe_validate.g_attribute13;
2325 END IF;
2326
2327 IF p_header_rec.attribute14(i) IS NULL
2328 OR p_header_rec.attribute14(i) = FND_API.G_MISS_CHAR THEN
2329 p_header_rec.attribute14(i) := oe_validate.g_attribute14;
2330 END IF;
2331
2332 IF p_header_rec.attribute15(i) IS NULL
2333 OR p_header_rec.attribute15(i) = FND_API.G_MISS_CHAR THEN
2334 p_header_rec.attribute15(i) := oe_validate.g_attribute15;
2335 END IF;
2336
2337 IF p_header_rec.attribute16(i) IS NULL -- For bug 2184255
2338 OR p_header_rec.attribute16(i) = FND_API.G_MISS_CHAR THEN
2339 p_header_rec.attribute16(i) := oe_validate.g_attribute16;
2340 END IF;
2341
2342 IF p_header_rec.attribute17(i) IS NULL
2343 OR p_header_rec.attribute17(i) = FND_API.G_MISS_CHAR THEN
2344 p_header_rec.attribute17(i) := oe_validate.g_attribute17;
2345 END IF;
2346
2347 IF p_header_rec.attribute18(i) IS NULL
2348 OR p_header_rec.attribute18(i) = FND_API.G_MISS_CHAR THEN
2349 p_header_rec.attribute18(i) := oe_validate.g_attribute18;
2350 END IF;
2351
2352 IF p_header_rec.attribute19(i) IS NULL
2353 OR p_header_rec.attribute19(i) = FND_API.G_MISS_CHAR THEN
2354 p_header_rec.attribute19(i) := oe_validate.g_attribute19;
2355 END IF;
2356
2357 IF p_header_rec.attribute20(i) IS NULL
2358 OR p_header_rec.attribute20(i) = FND_API.G_MISS_CHAR THEN
2359 p_header_rec.attribute20(i) := oe_validate.g_attribute20;
2360 END IF;
2361
2362 -- end of assignments, bug 2511313
2363
2364 END IF;
2365 END IF;
2366
2367 IF OE_Bulk_Order_Pvt.G_OE_HEADER_GLOBAL_ATTRIBUTE = 'Y' THEN
2368
2369 IF NOT OE_VALIDATE.G_Header_Desc_Flex
2370 (p_context => p_header_rec.global_attribute_category(i)
2371 ,p_attribute1 => p_header_rec.global_attribute1(i)
2372 ,p_attribute2 => p_header_rec.global_attribute2(i)
2373 ,p_attribute3 => p_header_rec.global_attribute3(i)
2374 ,p_attribute4 => p_header_rec.global_attribute4(i)
2375 ,p_attribute5 => p_header_rec.global_attribute5(i)
2376 ,p_attribute6 => p_header_rec.global_attribute6(i)
2377 ,p_attribute7 => p_header_rec.global_attribute7(i)
2378 ,p_attribute8 => p_header_rec.global_attribute8(i)
2379 ,p_attribute9 => p_header_rec.global_attribute9(i)
2380 ,p_attribute10 => p_header_rec.global_attribute10(i)
2381 ,p_attribute11 => p_header_rec.global_attribute11(i)
2382 ,p_attribute12 => p_header_rec.global_attribute12(i)
2383 ,p_attribute13 => p_header_rec.global_attribute13(i)
2384 ,p_attribute14 => p_header_rec.global_attribute13(i)
2385 ,p_attribute15 => p_header_rec.global_attribute14(i)
2386 ,p_attribute16 => p_header_rec.global_attribute16(i)
2387 ,p_attribute17 => p_header_rec.global_attribute17(i)
2388 ,p_attribute18 => p_header_rec.global_attribute18(i)
2389 ,p_attribute19 => p_header_rec.global_attribute19(i)
2390 ,p_attribute20 => p_header_rec.global_attribute20(i))
2391 THEN
2392 p_header_rec.lock_control(i) := -99;
2393 -- Log Error Message
2394 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2395 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2396 'Entity:Flexfield:G_Header_Desc_Flex');
2397 oe_bulk_msg_pub.Add('Y', 'ERROR');
2398
2399 ELSE -- for bug 2511313
2400 IF p_header_rec.global_attribute_category(i) IS NULL
2401 OR p_header_rec.global_attribute_category(i) = FND_API.G_MISS_CHAR THEN
2402 p_header_rec.global_attribute_category(i) := oe_validate.g_context;
2403 END IF;
2404
2405 IF p_header_rec.global_attribute1(i) IS NULL
2406 OR p_header_rec.global_attribute1(i) = FND_API.G_MISS_CHAR THEN
2407 p_header_rec.global_attribute1(i) := oe_validate.g_attribute1;
2408 END IF;
2409
2410 IF p_header_rec.global_attribute2(i) IS NULL
2411 OR p_header_rec.global_attribute2(i) = FND_API.G_MISS_CHAR THEN
2412 p_header_rec.global_attribute2(i) := oe_validate.g_attribute2;
2413 END IF;
2414
2415 IF p_header_rec.global_attribute3(i) IS NULL
2416 OR p_header_rec.global_attribute3(i) = FND_API.G_MISS_CHAR THEN
2417 p_header_rec.global_attribute3(i) := oe_validate.g_attribute3;
2418 END IF;
2419
2420 IF p_header_rec.global_attribute4(i) IS NULL
2421 OR p_header_rec.global_attribute4(i) = FND_API.G_MISS_CHAR THEN
2422 p_header_rec.global_attribute4(i) := oe_validate.g_attribute4;
2423 END IF;
2424
2425 IF p_header_rec.global_attribute5(i) IS NULL
2426 OR p_header_rec.global_attribute5(i) = FND_API.G_MISS_CHAR THEN
2427 p_header_rec.global_attribute5(i) := oe_validate.g_attribute5;
2428 END IF;
2429
2430 IF p_header_rec.global_attribute6(i) IS NULL
2431 OR p_header_rec.global_attribute6(i) = FND_API.G_MISS_CHAR THEN
2432 p_header_rec.global_attribute6(i) := oe_validate.g_attribute6;
2433 END IF;
2434
2435 IF p_header_rec.global_attribute7(i) IS NULL
2436 OR p_header_rec.global_attribute7(i) = FND_API.G_MISS_CHAR THEN
2437 p_header_rec.global_attribute7(i) := oe_validate.g_attribute7;
2438 END IF;
2439
2440 IF p_header_rec.global_attribute8(i) IS NULL
2441 OR p_header_rec.global_attribute8(i) = FND_API.G_MISS_CHAR THEN
2442 p_header_rec.global_attribute8(i) := oe_validate.g_attribute8;
2443 END IF;
2444
2445 IF p_header_rec.global_attribute9(i) IS NULL
2446 OR p_header_rec.global_attribute9(i) = FND_API.G_MISS_CHAR THEN
2447 p_header_rec.global_attribute9(i) := oe_validate.g_attribute9;
2448 END IF;
2449
2450 IF p_header_rec.global_attribute10(i) IS NULL
2451 OR p_header_rec.global_attribute10(i) = FND_API.G_MISS_CHAR THEN
2452 p_header_rec.global_attribute10(i) := oe_validate.g_attribute10;
2453 END IF;
2454
2455 IF p_header_rec.global_attribute11(i) IS NULL
2456 OR p_header_rec.global_attribute11(i) = FND_API.G_MISS_CHAR THEN
2457 p_header_rec.global_attribute11(i) := oe_validate.g_attribute11;
2458 END IF;
2459
2460 IF p_header_rec.global_attribute12(i) IS NULL
2461 OR p_header_rec.global_attribute12(i) = FND_API.G_MISS_CHAR THEN
2462 p_header_rec.global_attribute12(i) := oe_validate.g_attribute12;
2463 END IF;
2464
2465 IF p_header_rec.global_attribute13(i) IS NULL
2466 OR p_header_rec.global_attribute13(i) = FND_API.G_MISS_CHAR THEN
2467 p_header_rec.global_attribute13(i) := oe_validate.g_attribute13;
2468 END IF;
2469
2470 IF p_header_rec.global_attribute14(i) IS NULL
2471 OR p_header_rec.global_attribute14(i) = FND_API.G_MISS_CHAR THEN
2472 p_header_rec.global_attribute14(i) := oe_validate.g_attribute14;
2473 END IF;
2474
2475 IF p_header_rec.global_attribute15(i) IS NULL
2476 OR p_header_rec.global_attribute15(i) = FND_API.G_MISS_CHAR THEN
2477 p_header_rec.global_attribute15(i) := oe_validate.g_attribute15;
2478 END IF;
2479
2480 IF p_header_rec.global_attribute16(i) IS NULL
2481 OR p_header_rec.global_attribute16(i) = FND_API.G_MISS_CHAR THEN
2482 p_header_rec.global_attribute16(i) := oe_validate.g_attribute16;
2483 END IF;
2484
2485 IF p_header_rec.global_attribute17(i) IS NULL
2486 OR p_header_rec.global_attribute17(i) = FND_API.G_MISS_CHAR THEN
2487 p_header_rec.global_attribute17(i) := oe_validate.g_attribute17;
2488 END IF;
2489
2490 IF p_header_rec.global_attribute18(i) IS NULL
2491 OR p_header_rec.global_attribute18(i) = FND_API.G_MISS_CHAR THEN
2492 p_header_rec.global_attribute18(i) := oe_validate.g_attribute18;
2493 END IF;
2494
2495 IF p_header_rec.global_attribute19(i) IS NULL
2496 OR p_header_rec.global_attribute19(i) = FND_API.G_MISS_CHAR THEN
2497 p_header_rec.global_attribute19(i) := oe_validate.g_attribute19;
2498 END IF;
2499
2500 IF p_header_rec.global_attribute20(i) IS NULL
2501 OR p_header_rec.global_attribute20(i) = FND_API.G_MISS_CHAR THEN
2502 p_header_rec.global_attribute20(i) := oe_validate.g_attribute20;
2503 END IF;
2504 -- end of bug 2511313
2505
2506 END IF;
2507 END IF;
2508
2509 IF OE_Bulk_Order_Pvt.G_OE_HEADER_TP_ATTRIBUTES = 'Y' THEN
2510
2511 IF NOT OE_VALIDATE.TP_Header_Desc_Flex
2512 (p_context => p_header_rec.tp_context(i)
2513 ,p_attribute1 => p_header_rec.tp_attribute1(i)
2514 ,p_attribute2 => p_header_rec.tp_attribute2(i)
2515 ,p_attribute3 => p_header_rec.tp_attribute3(i)
2516 ,p_attribute4 => p_header_rec.tp_attribute4(i)
2517 ,p_attribute5 => p_header_rec.tp_attribute5(i)
2518 ,p_attribute6 => p_header_rec.tp_attribute6(i)
2519 ,p_attribute7 => p_header_rec.tp_attribute7(i)
2520 ,p_attribute8 => p_header_rec.tp_attribute8(i)
2521 ,p_attribute9 => p_header_rec.tp_attribute9(i)
2522 ,p_attribute10 => p_header_rec.tp_attribute10(i)
2523 ,p_attribute11 => p_header_rec.tp_attribute11(i)
2524 ,p_attribute12 => p_header_rec.tp_attribute12(i)
2525 ,p_attribute13 => p_header_rec.tp_attribute13(i)
2526 ,p_attribute14 => p_header_rec.tp_attribute14(i)
2527 ,p_attribute15 => p_header_rec.tp_attribute15(i))
2528 THEN
2529 p_header_rec.lock_control(i) := -99;
2530 -- Log Error Message
2531 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2532 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2533 'Entity:Flexfield:TP_Header_Desc_Flex');
2534 oe_bulk_msg_pub.Add('Y', 'ERROR');
2535 ELSE -- for bug 2511313
2536
2537 IF p_header_rec.tp_context(i) IS NULL
2538 OR p_header_rec.tp_context(i) = FND_API.G_MISS_CHAR THEN
2539 p_header_rec.tp_context(i) := oe_validate.g_context;
2540 END IF;
2541
2542 IF p_header_rec.tp_attribute1(i) IS NULL
2543 OR p_header_rec.tp_attribute1(i) = FND_API.G_MISS_CHAR THEN
2544 p_header_rec.tp_attribute1(i) := oe_validate.g_attribute1;
2545 END IF;
2546
2547 IF p_header_rec.tp_attribute2(i) IS NULL
2548 OR p_header_rec.tp_attribute2(i) = FND_API.G_MISS_CHAR THEN
2549 p_header_rec.tp_attribute2(i) := oe_validate.g_attribute2;
2550 END IF;
2551
2552 IF p_header_rec.tp_attribute3(i) IS NULL
2553 OR p_header_rec.tp_attribute3(i) = FND_API.G_MISS_CHAR THEN
2554 p_header_rec.tp_attribute3(i) := oe_validate.g_attribute3;
2555 END IF;
2556
2557 IF p_header_rec.tp_attribute4(i) IS NULL
2558 OR p_header_rec.tp_attribute4(i) = FND_API.G_MISS_CHAR THEN
2559 p_header_rec.tp_attribute4(i) := oe_validate.g_attribute4;
2560 END IF;
2561
2562 IF p_header_rec.tp_attribute5(i) IS NULL
2563 OR p_header_rec.tp_attribute5(i) = FND_API.G_MISS_CHAR THEN
2564 p_header_rec.tp_attribute5(i) := oe_validate.g_attribute5;
2565 END IF;
2566
2567 IF p_header_rec.tp_attribute6(i) IS NULL
2568 OR p_header_rec.tp_attribute6(i) = FND_API.G_MISS_CHAR THEN
2569 p_header_rec.tp_attribute6(i) := oe_validate.g_attribute6;
2570 END IF;
2571
2572 IF p_header_rec.tp_attribute7(i) IS NULL
2573 OR p_header_rec.tp_attribute7(i) = FND_API.G_MISS_CHAR THEN
2574 p_header_rec.tp_attribute7(i) := oe_validate.g_attribute7;
2575 END IF;
2576
2577 IF p_header_rec.tp_attribute8(i) IS NULL
2578 OR p_header_rec.tp_attribute8(i) = FND_API.G_MISS_CHAR THEN
2579 p_header_rec.tp_attribute8(i) := oe_validate.g_attribute8;
2580 END IF;
2581
2582 IF p_header_rec.tp_attribute9(i) IS NULL
2583 OR p_header_rec.tp_attribute9(i) = FND_API.G_MISS_CHAR THEN
2584 p_header_rec.tp_attribute9(i) := oe_validate.g_attribute9;
2585 END IF;
2586
2587 IF p_header_rec.tp_attribute10(i) IS NULL
2588 OR p_header_rec.tp_attribute10(i) = FND_API.G_MISS_CHAR THEN
2589 p_header_rec.tp_attribute10(i) := Oe_validate.G_attribute10;
2590 End IF;
2591
2592 IF p_header_rec.tp_attribute11(i) IS NULL
2593 OR p_header_rec.tp_attribute11(i) = FND_API.G_MISS_CHAR THEN
2594 p_header_rec.tp_attribute11(i) := oe_validate.g_attribute11;
2595 END IF;
2596
2597 IF p_header_rec.tp_attribute12(i) IS NULL
2598 OR p_header_rec.tp_attribute12(i) = FND_API.G_MISS_CHAR THEN
2599 p_header_rec.tp_attribute12(i) := oe_validate.g_attribute12;
2600 END IF;
2601
2602 IF p_header_rec.tp_attribute13(i) IS NULL
2603 OR p_header_rec.tp_attribute13(i) = FND_API.G_MISS_CHAR THEN
2604 p_header_rec.tp_attribute13(i) := oe_validate.g_attribute13;
2605 END IF;
2606
2607 IF p_header_rec.tp_attribute14(i) IS NULL
2608 OR p_header_rec.tp_attribute14(i) = FND_API.G_MISS_CHAR THEN
2609 p_header_rec.tp_attribute14(i) := oe_validate.g_attribute14;
2610 END IF;
2611
2612 IF p_header_rec.tp_attribute15(i) IS NULL
2613 OR p_header_rec.tp_attribute15(i) = FND_API.G_MISS_CHAR THEN
2614 p_header_rec.tp_attribute15(i) := oe_validate.g_attribute15;
2615 END IF;
2616
2617 END IF;
2618 END IF;
2619
2620 END IF; -- End if p_validate_desc_flex is 'Y'
2621
2622 -- END: Desc Flex Validations
2623
2624
2625 ---------------------------------------------------------------
2626 -- Add a 100% default sales credit record for this salesperson
2627 ---------------------------------------------------------------
2628
2629 IF p_header_rec.salesrep_id(i) IS NOT NULL THEN
2630
2631 x_header_scredit_rec.header_id.extend(1);
2632 x_header_scredit_rec.salesrep_id.extend(1);
2633 x_header_scredit_rec.sales_credit_type_id.extend(1);
2634
2635 l_c_index := OE_Bulk_Cache.Load_Salesrep
2636 (p_key => p_header_rec.salesrep_id(i));
2637
2638 x_header_scredit_rec.header_id(l_scredit_index)
2639 := p_header_rec.header_id(i);
2640 x_header_scredit_rec.salesrep_id(l_scredit_index)
2641 := p_header_rec.salesrep_id(i);
2642 x_header_scredit_rec.sales_credit_type_id(l_scredit_index)
2643 := OE_Bulk_Cache.G_SALESREP_TBL(l_c_index).sales_credit_type_id;
2644
2645 l_scredit_index := l_scredit_index + 1;
2646
2647 END IF;
2648
2649 ---------------------------------------------------------------
2650 -- Load EDI attributes if sold to customer is an EDI customer
2651 ---------------------------------------------------------------
2652
2653 IF OE_GLOBALS.G_EC_INSTALLED = 'Y'
2654 AND p_header_rec.booked_flag(i) = 'Y'
2655 AND nvl(p_header_rec.lock_control(i),0) <> -99
2656 THEN
2657
2658 l_c_index := OE_Bulk_Cache.Load_Sold_To
2659 (p_key => p_header_rec.sold_to_org_id(i)
2660 ,p_edi_attributes => 'Y'
2661 );
2662
2663 IF OE_Bulk_Cache.G_SOLD_TO_TBL(l_c_index).tp_setup THEN
2664
2665 OE_Bulk_Order_Pvt.G_ACK_NEEDED := 'Y';
2666 p_header_rec.first_ack_code(i) := 'X';
2667
2668 -- Cache EDI attributes as these will be used in creating
2669 -- the acknowledgment records later.
2670
2671 IF p_header_rec.invoice_to_org_id(i) IS NOT NULL THEN
2672 l_c_index := OE_Bulk_Cache.Load_Invoice_To
2673 (p_key => p_header_rec.invoice_to_org_id(i)
2674 ,p_edi_attributes => 'Y'
2675 );
2676 END IF;
2677
2678 IF p_header_rec.ship_to_org_id(i) IS NOT NULL THEN
2679 l_c_index := OE_Bulk_Cache.Load_Ship_To
2680 (p_key => p_header_rec.ship_to_org_id(i)
2681 ,p_edi_attributes => 'Y'
2682 );
2683 END IF;
2684
2685 IF p_header_rec.ship_from_org_id(i) IS NOT NULL THEN
2686 l_c_index := OE_Bulk_Cache.Load_Ship_From
2687 (p_key => p_header_rec.ship_from_org_id(i)
2688 );
2689 END IF;
2690
2691 -- added for end customer changes(bug 5054618)
2692
2693 IF p_header_rec.end_customer_id(i) IS NOT NULL THEN
2694 l_c_index := OE_Bulk_Cache.Load_End_customer
2695 (p_key => p_header_rec.end_customer_id(i)
2696 ,p_edi_attributes => 'Y'
2697 );
2698 END IF;
2699
2700 IF p_header_rec.End_customer_site_use_id(i) IS NOT NULL THEN
2701 l_c_index := OE_Bulk_Cache.Load_end_customer_site
2702 (p_key => p_header_rec.end_customer_site_use_id(i)
2703 ,p_edi_attributes => 'Y'
2704 );
2705 END IF;
2706
2707 END IF;
2708
2709 END IF;
2710
2711 ---------------------------------------------------------------
2712 -- Evaluate Holds for header
2713 ---------------------------------------------------------------
2714
2715 IF p_header_rec.lock_control(i) <> -99 THEN
2716
2717 -- Check Header Level Holds
2718 /*ER#7479609 start
2719 OE_Bulk_Holds_PVT.Evaluate_Holds(
2720 p_header_id => p_header_rec.header_id(i),
2721 p_line_id => NULL,
2722 p_line_number => NULL,
2723 p_sold_to_org_id => p_header_rec.sold_to_org_id(i),
2724 p_inventory_item_id => NULL,
2725 p_ship_from_org_id => NULL,
2726 p_invoice_to_org_id => NULL,
2727 p_ship_to_org_id => NULL,
2728 p_top_model_line_id => NULL,
2729 p_ship_set_name => NULL,
2730 p_arrival_set_name => NULL,
2731 p_on_generic_hold => l_on_generic_hold,
2732 p_on_booking_hold => l_on_booking_hold,
2733 p_on_scheduling_hold => l_on_scheduling_hold
2734 );
2735 ER#7479609 end*/
2736
2737 --ER#7479609 start
2738 l_header_rec_for_hold.header_id := p_header_rec.header_id(i);
2739 l_header_rec_for_hold.sold_to_org_id := p_header_rec.sold_to_org_id(i);
2740 l_header_rec_for_hold.sales_channel_code := p_header_rec.sales_channel_code(i);
2741 l_header_rec_for_hold.payment_type_code := p_header_rec.payment_type_code(i);
2742 l_header_rec_for_hold.order_type_id := p_header_rec.order_type_id(i);
2743 l_header_rec_for_hold.transactional_curr_code := p_header_rec.transactional_curr_code(i);
2744 l_header_rec_for_hold.invoice_to_org_id := p_header_rec.invoice_to_org_id(i); --ER# 3667551
2745
2746 OE_Bulk_Holds_PVT.Evaluate_Holds(
2747 p_header_rec => l_header_rec_for_hold,
2748 p_line_rec => NULL,
2749 p_on_generic_hold => l_on_generic_hold,
2750 p_on_booking_hold => l_on_booking_hold,
2751 p_on_scheduling_hold => l_on_scheduling_hold
2752 );
2753 --ER#7479609 end
2754
2755 END IF;
2756
2757 ---------------------------------------------------------------
2758 -- BOOKING VALIDATIONS
2759 ---------------------------------------------------------------
2760 IF p_header_rec.booked_flag(i) = 'Y' THEN
2761
2762 -- Do not book the Order if the header is on HOLD.
2763
2764 IF l_on_generic_hold OR l_on_booking_hold THEN
2765
2766 FND_MESSAGE.SET_NAME('ONT','OE_BOOKING_HOLD_EXISTS');
2767 oe_bulk_msg_pub.ADD;
2768 p_header_rec.booked_flag(i) := 'N';
2769 --PIB
2770 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' Then
2771 p_header_rec.event_code(i) := 'BATCH';
2772 If l_debug_level > 0 Then
2773 oe_debug_pub.add('event code1 : '||p_header_rec.event_code(i));
2774 End If;
2775 END IF;
2776 --PIB
2777
2778 ELSE
2779
2780 Check_Book_Reqd_Attributes(p_header_rec => p_header_rec
2781 ,p_index => i
2782 ,x_return_status => l_return_status);
2783
2784 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2785 p_header_rec.booked_flag(i) := 'N';
2786 --PIB
2787 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' Then
2788 p_header_rec.event_code(i) := 'BATCH';
2789 If l_debug_level > 0 Then
2790 oe_debug_pub.add('event code2 : '||p_header_rec.event_code(i));
2791 End If;
2792 END IF;
2793 --PIB
2794 END IF;
2795
2796 END IF;
2797
2798 END IF;
2799
2800 -- Update Global table with pointers to Erroneous record
2801
2802 IF (p_header_rec.lock_control(i) IN (-99, -98, -97) ) THEN
2803
2804 --ER 9060917
2805 If NVL (Fnd_Profile.Value('ONT_HVOP_DROP_INVALID_LINES'), 'N')='Y' then
2806
2807 UPDATE oe_headers_iface_all
2808 SET error_flag='Y'
2809 WHERE order_source_id=p_header_rec.order_source_id(i)
2810 and orig_sys_document_ref=p_header_rec.orig_sys_document_ref(i);
2811
2812 end if;
2813 --End of ER 9060917
2814
2815 error_count := error_count + 1;
2816
2817 OE_Bulk_Order_Pvt.G_ERROR_REC.order_source_id.EXTEND(1);
2818 OE_Bulk_Order_Pvt.G_ERROR_REC.order_source_id(error_count)
2819 := p_header_rec.order_source_id(i);
2820
2821 OE_Bulk_Order_Pvt.G_ERROR_REC.orig_sys_document_ref.EXTEND(1);
2822 OE_Bulk_Order_Pvt.G_ERROR_REC.orig_sys_document_ref(error_count)
2823 := p_header_rec.orig_sys_document_ref(i);
2824
2825 OE_Bulk_Order_Pvt.G_ERROR_REC.header_id.EXTEND(1);
2826 OE_Bulk_Order_Pvt.G_ERROR_REC.header_id(error_count)
2827 := p_header_rec.header_id(i);
2828 OE_Bulk_Order_PVT.G_ERROR_REC.ineligible_for_hvop.EXTEND(1);
2829 OE_Bulk_Order_PVT.G_ERROR_REC.skip_batch.EXTEND(1);
2830
2831 IF p_header_rec.lock_control(i) = -98 THEN
2832 OE_Bulk_Order_PVT.G_ERROR_REC.ineligible_for_hvop(error_count):=
2833 'Y';
2834 ELSIF p_header_rec.lock_control(i) = -97 THEN
2835 OE_Bulk_Order_PVT.G_ERROR_REC.skip_batch(error_count):= 'Y';
2836 END IF;
2837 END IF;
2838
2839 END LOOP;
2840
2841
2842 EXCEPTION
2843 WHEN OTHERS THEN
2844 IF oe_bulk_msg_pub.check_msg_level(oe_bulk_msg_pub.G_MSG_LVL_UNEXP_ERROR)
2845 THEN
2846 oe_bulk_msg_pub.add_exc_msg
2847 ( G_PKG_NAME
2848 , 'Entity'
2849 );
2850 END IF;
2851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2852 END Entity;
2853
2854
2855 END OE_BULK_PROCESS_HEADER;
[Home] [Help]