1 PACKAGE BODY OE_BULK_PROCESS_HEADER AS
2 /* $Header: OEBLHDRB.pls 120.8.12010000.6 2009/01/21 13:13:05 smanian 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 IF NOT OE_GLOBALS.EQUAL
1273 (OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).sold_to_org_id
1274 ,p_sold_to_org_id)
1275 THEN
1276
1277 IF OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'N'
1278 OR (OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'Y'
1279 AND NOT Is_Related_Customer(p_sold_to_org_id
1280 ,OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).sold_to_org_id))
1281 THEN
1282
1283 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT');
1284 fnd_message.set_Token('AGREEMENT_ID',p_agreement_id);
1285 fnd_message.set_Token('AGREEMENT_NAME',
1286 OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).name);
1287 fnd_message.set_Token('CUSTOMER_ID',
1288 OE_Bulk_Cache.G_AGREEMENT_TBL(l_c_index).sold_to_org_id);
1289 oe_bulk_msg_pub.Add('Y', 'ERROR');
1290
1291 RETURN FALSE;
1292
1293 END IF;
1294
1295 END IF;
1296
1297 RETURN TRUE;
1298
1299 EXCEPTION
1300 WHEN NO_DATA_FOUND THEN
1301 RETURN FALSE;
1302 END Validate_Agreement;
1303
1304
1305 -- This FUNCTION consists of all price list related validations.
1306
1307 FUNCTION Validate_Price_List
1308 (p_price_list_id IN NUMBER
1309 ,p_curr_code IN VARCHAR2
1310 ,p_pricing_date IN DATE
1311 ,p_calculate_price IN VARCHAR2
1312 )
1313 RETURN BOOLEAN
1314 IS
1315
1316 l_validate_result VARCHAR2(1);
1317 l_c_index NUMBER;
1318
1319 BEGIN
1320
1321 -- For order header, this check is NOT needed if any one line exists
1322 -- with calculate_price_flag of 'N' or 'P' - where to do this?
1323
1324 -- For line, price list validations not needed if calculate price is
1325 -- frozen or partial
1326
1327 IF p_calculate_price IN ('P','N') THEN
1328 RETURN TRUE;
1329 END IF;
1330
1331 -- Cache price list
1332
1333 l_c_index := OE_Bulk_Cache.Load_Price_List(p_price_list_id);
1334
1335 -- Verify that price list is effective for the valid dates
1336
1337 IF NOT trunc(nvl(p_pricing_date,sysdate))
1338 BETWEEN trunc(nvl(OE_Bulk_Cache.G_PRICE_LIST_TBL(l_c_index).start_date_active
1339 ,add_months(sysdate,-10000)))
1340 AND trunc(nvl(OE_Bulk_Cache.G_PRICE_LIST_TBL(l_c_index).end_date_active
1341 ,add_months(sysdate,+10000)))
1342 THEN
1343
1344 fnd_message.set_name('ONT', 'OE_INVALID_NONAGR_PLIST');
1345 fnd_message.set_Token('PRICE_LIST1', p_price_list_id);
1346 fnd_message.set_Token('PRICING_DATE', p_pricing_date);
1347 oe_bulk_msg_pub.Add('Y', 'ERROR');
1348
1349 RETURN FALSE;
1350
1351 END IF;
1352
1353 -- Validate currency against the price list
1354
1355 QP_UTIL_PUB.Validate_Price_list_Curr_code(p_price_list_id
1356 ,p_curr_code
1357 ,p_pricing_date
1358 ,l_validate_result);
1359
1360 IF l_validate_result = 'N' THEN
1361
1362 FND_MESSAGE.SET_NAME('ONT','OE_CURRENCY_MISMATCH');
1363 FND_MESSAGE.SET_TOKEN('LINE_CURR_CODE',
1364 OE_Bulk_Cache.G_PRICE_LIST_TBL(l_c_index).currency_code);
1365 FND_MESSAGE.SET_TOKEN('PRICE_LIST_NAME',
1366 OE_Bulk_Cache.G_PRICE_LIST_TBL(l_c_index).name);
1367 FND_MESSAGE.SET_TOKEN('HEADER_CURR_CODE',p_curr_code);
1368 oe_bulk_msg_pub.Add('Y', 'ERROR');
1369
1370 RETURN FALSE;
1371
1372 END IF;
1373
1374 RETURN TRUE;
1375
1376 EXCEPTION
1377 WHEN NO_DATA_FOUND THEN
1378 oe_debug_pub.add('no data found in validate price list');
1379 RETURN FALSE;
1380 END Validate_Price_List;
1381
1382
1383 FUNCTION Validate_Ship_to(p_sold_to IN NUMBER,
1384 p_Ship_to IN NUMBER)
1385 RETURN BOOLEAN
1386 IS
1387 l_dummy VARCHAR2(10);
1388 l_c_index NUMBER;
1389 BEGIN
1390
1391 --
1392 -- Validations here account for all customer relationships
1393 --
1394 -- If ship_to_org_id is not valid for any customer,
1395 -- then Cache FUNCTION Load_<Entity> will raise no data found
1396 -- so for all relationship values including G_CUST_RELATIONS = 'A'
1397 -- , it will return FALSE.
1398 --
1399 -- If customer relationships = 'N' but customer does not match
1400 -- ship to customer, it will return FALSE.
1401 --
1402 -- If customer relationships = 'Y', it will also check if ship
1403 -- to customer is a related customer and if not, return FALSE.
1404 --
1405
1406 l_c_index := OE_Bulk_Cache.Load_Ship_To
1407 (p_key => p_ship_to);
1408
1409 IF OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).customer_id
1410 = p_sold_to
1411 THEN
1412 RETURN TRUE;
1413 END IF;
1414
1415 IF OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'N' THEN
1416
1417 RETURN FALSE;
1418
1419 ELSIF OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'Y' THEN
1420
1421 SELECT 'VALID'
1422 INTO l_dummy
1423 FROM HZ_CUST_ACCT_RELATE
1424 WHERE RELATED_CUST_ACCOUNT_ID = p_sold_to
1425 AND CUST_ACCOUNT_ID = OE_Bulk_Cache.G_SHIP_TO_TBL(l_c_index).customer_id
1426 AND ship_to_flag = 'Y' AND STATUS='A';
1427
1428 END IF;
1429
1430 RETURN TRUE;
1431
1432 EXCEPTION
1433 WHEN NO_DATA_FOUND THEN
1434 RETURN FALSE;
1435 END Validate_Ship_to;
1436
1437 --abghosh
1438
1439 FUNCTION Validate_Sold_to_site(p_sold_to IN NUMBER,
1440 p_Sold_to_site_use_id IN NUMBER)
1441 RETURN BOOLEAN
1442 IS
1443 l_dummy VARCHAR2(10);
1444 l_c_index NUMBER;
1445 BEGIN
1446
1447 --
1448 --
1449 -- If sold_to_site_use_id is not valid for any customer,
1450 -- then Cache FUNCTION Load_<Entity> will raise no data found
1451 -- , it will return FALSE.
1452 --
1453 --
1454 --
1455
1456 l_c_index := OE_Bulk_Cache.Load_Sold_to_site
1457 (p_key => p_sold_to_site_use_id);
1458
1459 IF OE_Bulk_Cache.G_SOLD_TO_SITE_TBL(l_c_index).customer_id
1460 = p_sold_to
1461 THEN
1462 RETURN TRUE;
1463 ELSE
1464 RETURN FALSE;
1465 END IF;
1466
1467
1468 EXCEPTION
1469 WHEN NO_DATA_FOUND THEN
1470 RETURN FALSE;
1471 END Validate_Sold_to_site;
1472
1473
1474 FUNCTION Validate_Bill_to(p_sold_to IN NUMBER,
1475 p_bill_to IN NUMBER)
1476 RETURN BOOLEAN
1477 IS
1478 l_dummy VARCHAR2(10);
1479 l_c_index NUMBER;
1480 BEGIN
1481
1482 --
1483 -- Validations here account for all customer relationships
1484 --
1485 -- If invoice_to_org_id is not valid for any customer,
1486 -- then Cache FUNCTION Load_<Entity> will raise no data found
1487 -- so for all relationship values including G_CUST_RELATIONS = 'A'
1488 -- , it will return FALSE.
1489 --
1490 -- If customer relationships = 'N' but customer does not match
1491 -- invoice to customer, it will return FALSE.
1492 --
1493 -- If customer relationships = 'Y', it will also check if invoice
1494 -- to customer is a related customer and if not, return FALSE.
1495 --
1496
1497 l_c_index := OE_Bulk_Cache.Load_Invoice_To
1498 (p_key => p_bill_to);
1499
1500 IF OE_Bulk_Cache.G_INVOICE_TO_TBL(l_c_index).customer_id
1501 = p_sold_to
1502 THEN
1503 RETURN TRUE;
1504 END IF;
1505
1506 IF OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'N' THEN
1507
1508 RETURN FALSE;
1509
1510 ELSIF OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'Y' THEN
1511
1512 SELECT 'VALID'
1513 INTO l_dummy
1514 FROM HZ_CUST_ACCT_RELATE
1515 WHERE RELATED_CUST_ACCOUNT_ID = p_sold_to
1516 AND CUST_ACCOUNT_ID = OE_Bulk_Cache.G_INVOICE_TO_TBL(l_c_index).customer_id
1517 AND bill_to_flag = 'Y' AND STATUS='A';
1518
1519 END IF;
1520
1521 RETURN TRUE;
1522
1523 EXCEPTION
1524 WHEN NO_DATA_FOUND THEN
1525 RETURN FALSE;
1526 END Validate_Bill_to;
1527
1528
1529 FUNCTION Validate_Deliver_to(p_sold_to IN NUMBER,
1530 p_deliver_to IN NUMBER)
1531 RETURN BOOLEAN
1532 IS
1533 l_dummy VARCHAR2(10);
1534 --bug 4729536
1535 Cursor cur_customer_relations is
1536 SELECT /*MOAC_SQL_NO_CHANGE */ 'VALID'
1537 FROM oe_deliver_to_orgs_v
1538 WHERE site_use_id = p_deliver_to
1539 AND status = 'A'
1540 AND customer_id = p_sold_to
1541 AND ROWNUM = 1
1542
1543 UNION ALL
1544
1545 SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
1546 FROM oe_deliver_to_orgs_v odto
1547 WHERE site_use_id = p_deliver_to
1548 AND status = 'A'
1549 AND exists
1550 (
1551 SELECT 1
1552 FROM HZ_CUST_ACCT_RELATE hcar
1553 WHERE hcar.CUST_ACCOUNT_ID = odto.customer_id
1554 AND hcar.RELATED_CUST_ACCOUNT_ID = p_sold_to
1555 AND hcar.ship_to_flag = 'Y'
1556 AND hcar.STATUS ='A'
1557 )
1558 AND ROWNUM=1;
1559
1560 BEGIN
1561
1562 IF (OE_Bulk_Order_Pvt.G_CUST_RELATIONS = 'N') THEN
1563
1564 SELECT 'VALID'
1565 INTO l_dummy
1566 FROM oe_deliver_to_orgs_v
1567 WHERE customer_id = p_sold_to
1568 AND site_use_id = p_deliver_to
1569 AND status = 'A';
1570
1571 ELSE
1572
1573 /*SELECT /*MOAC_SQL_NO_CHANGE 'VALID'
1574 INTO l_dummy
1575 FROM oe_deliver_to_orgs_v
1576 WHERE site_use_id = p_deliver_to
1577 AND status = 'A'
1578 AND customer_id IN (
1579 Select p_sold_to from dual
1580 union
1581 SELECT CUST_ACCOUNT_ID
1582 FROM HZ_CUST_ACCT_RELATE
1583 WHERE RELATED_CUST_ACCOUNT_ID = p_sold_to
1584 AND ship_to_flag = 'Y' AND STATUS ='A'
1585 );*/
1586 --bug 4729536
1587 Open Cur_Customer_Relations;
1588 Fetch Cur_Customer_Relations into l_dummy;
1589 Close Cur_Customer_Relations;
1590 --bug 4729536
1591
1592 END IF;
1593
1594 return TRUE;
1595
1596 EXCEPTION
1597 WHEN NO_DATA_FOUND THEN
1598 return FALSE;
1599
1600 END Validate_Deliver_to;
1601
1602
1603 -- Validate that contact is valid for given (site)customer and usage.
1604 -- Validate Active status, since contact does NOT go thru attribute
1605 -- validation.
1606
1607 FUNCTION Validate_Site_Contact
1608 (p_site_use_id IN NUMBER
1609 ,p_contact_id IN NUMBER
1610 )
1611 RETURN BOOLEAN
1612 IS
1613 l_dummy VARCHAR2(10);
1614 BEGIN
1615
1616 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
1617 INTO l_dummy
1618 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
1619 HZ_CUST_SITE_USES_ALL SITE,
1620 HZ_CUST_ACCT_SITES ADDR
1621 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
1622 AND ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID
1623 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1624 AND ADDR.CUST_ACCT_SITE_ID = SITE.CUST_ACCT_SITE_ID
1625 AND SITE.SITE_USE_ID = p_site_use_id
1626 AND SITE.STATUS = 'A'
1627 AND ACCT_ROLE.STATUS = 'A'
1628 AND ADDR.STATUS ='A' ;-- added for bug 2752321
1629
1630 RETURN TRUE;
1631
1632 EXCEPTION
1633 WHEN NO_DATA_FOUND THEN
1634 RETURN FALSE;
1635 END Validate_Site_Contact;
1636
1637
1638 ---------------------------------------------------------------------
1639 -- PROCEDURE Entity
1640 --
1641 -- Main processing procedure used to process headers in a batch.
1642 -- IN parameters -
1643 -- p_header_rec : order headers in this batch
1644 -- p_defaulting_mode : 'Y' if fixed defaulting is needed, 'N' if
1645 -- defaulting is to be completely bypassed
1646 -- OUT NOCOPY /* file.sql.39 change */ parameters -
1647 -- x_header_scredit_rec : sales credits for headers processed
1648 --
1649 -- Processing steps include:
1650 -- 1. Restricted defaulting on p_header_rec if defaulting_mode is 'Y'
1651 -- 2. Populate all internal fields on p_header_rec
1652 -- 3. All entity validations
1653 -- 4. Other misc processing like holds evaluation, sales credits.
1654 ---------------------------------------------------------------------
1655
1656 PROCEDURE Entity
1657 ( p_header_rec IN OUT NOCOPY OE_Bulk_Order_Pvt.HEADER_REC_TYPE
1658 , x_header_scredit_rec IN OUT NOCOPY OE_Bulk_Order_Pvt.SCREDIT_REC_TYPE
1659 , p_defaulting_mode IN VARCHAR2
1660 , p_process_configurations IN VARCHAR2 DEFAULT 'N'
1661 , p_validate_configurations IN VARCHAR2 DEFAULT 'Y'
1662 , p_schedule_configurations IN VARCHAR2 DEFAULT 'N'
1663 , p_validate_desc_flex IN VARCHAR2
1664 )
1665 IS
1666
1667 ctr NUMBER;
1668 error_count NUMBER := 0;
1669 l_dummy VARCHAR2(10);
1670 l_return_status VARCHAR2(30);
1671 l_c_index NUMBER;
1672 l_order_type_id NUMBER;
1673 l_on_generic_hold BOOLEAN;
1674 l_on_booking_hold BOOLEAN;
1675 l_on_scheduling_hold BOOLEAN;
1676 l_scredit_index NUMBER := 1;
1677 l_gapless_sequence VARCHAR2(1) := 'N';
1678 l_hold_ii_flag VARCHAR2(1); -- Added to support out parameter
1679 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1680 l_header_rec_for_hold OE_Order_PUB.Header_Rec_Type; --ER#7479609
1681
1682 BEGIN
1683 ctr := p_header_rec.header_id.COUNT;
1684
1685 FOR i IN 1..ctr LOOP
1686
1687 -- Set Context for messags
1688
1689 oe_bulk_msg_pub.set_msg_context
1690 ( p_entity_code => 'HEADER'
1691 ,p_entity_id => p_header_rec.header_id(i)
1692 ,p_header_id => p_header_rec.header_id(i)
1693 ,p_line_id => null
1694 ,p_orig_sys_document_ref => p_header_rec.orig_sys_document_ref(i)
1695 ,p_orig_sys_document_line_ref => null
1696 ,p_order_source_id => p_header_rec.order_source_id(i)
1697 ,p_source_document_type_id => null
1698 ,p_source_document_id => null
1699 ,p_source_document_line_id => null);
1700
1701 --PIB
1702 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1703 p_header_rec.header_index.extend(1);
1704 p_header_rec.header_index(i) := i;
1705 p_header_rec.event_code.extend(1);
1706 IF p_header_rec.booked_flag(i) = 'Y' THEN
1707 p_header_rec.event_code(i) := 'BATCH,BOOK';
1708 ELSE
1709 p_header_rec.event_code(i) := 'BATCH';
1710 END IF;
1711 If l_debug_level > 0 Then
1712 oe_debug_pub.add('event code : '||p_header_rec.event_code(i));
1713 End If;
1714 END IF;
1715 --PIB
1716
1717 ---------------------------------------------------------
1718 -- CALL THE FIXED DEFAULTING PROCEDURE IF NEEDED
1719 ---------------------------------------------------------
1720
1721 IF p_defaulting_mode = 'Y' THEN
1722
1723 Default_Record
1724 ( p_header_rec => p_header_rec
1725 ,p_index => i
1726 ,x_return_status => l_return_status
1727 );
1728
1729 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1730 p_header_rec.lock_control(i) := -99;
1731 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1732 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1733 END IF;
1734
1735 END IF;
1736
1737 ---------------------------------------------------------
1738 -- POPULATE INTERNAL FIELDS
1739 -- Hardcoded Defaulting From OEXDHDRB.pls
1740 ---------------------------------------------------------
1741
1742 Populate_Internal_Fields
1743 ( p_header_rec => p_header_rec
1744 ,p_index => i
1745 ,x_return_status => l_return_status
1746 );
1747
1748 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1749 p_header_rec.lock_control(i) := -99;
1750 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1752 END IF;
1753
1754
1755 ---------------------------------------------------------
1756 -- START ENTITY VALIDATIONS
1757 ---------------------------------------------------------
1758
1759 -- Validate Required Attributes
1760
1761 -- Order Type is required
1762
1763 IF (p_header_rec.order_type_id(i) IS NOT NULL) THEN
1764
1765 l_order_type_id := p_header_rec.order_type_id(i);
1766
1767 -- Get Order Number - will return FALSE IF
1768 -- 1. Doc Sequence type is 'Manual' and order number not specified
1769 -- 2. If gapless sequence
1770 -- Error messages are populated in Get_Order_Number
1771
1772 IF NOT Get_order_number(p_header_rec.order_type_id(i),
1773 p_header_rec.order_number(i),
1774 l_gapless_sequence ) THEN
1775
1776 IF l_gapless_sequence = 'Y' THEN
1777 p_header_rec.lock_control(i) := -98;
1778 ELSE
1779 p_header_rec.lock_control(i) := -99;
1780 END IF;
1781
1782 -- Order Number is a required column hence put a dummy value
1783 -- for the insert to succeed.
1784 p_header_rec.order_number(i) := -1 * p_header_rec.header_id(i);
1785
1786 END IF;
1787
1788 -- Do other order type related validations
1789
1790 -- Cache order type, if order type is not valid caching will fail
1791 -- with no data found error.
1792 l_c_index := OE_Bulk_Cache.Load_Order_Type
1793 (p_key => l_order_type_id);
1794
1795 -- Check if Order Type is valid for ordered date
1796
1797 IF p_header_rec.ordered_date(i) IS NOT NULL
1798 AND NOT (p_header_rec.ordered_date(i) BETWEEN
1799 nvl(OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_order_type_id).start_date_active,sysdate)
1800 AND nvl(OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_order_type_id).end_date_active,sysdate))
1801 THEN
1802 p_header_rec.lock_control(i) := -99;
1803 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1804 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1805 OE_Order_Util.Get_Attribute_Name('ORDER_TYPE_ID'));
1806 oe_bulk_msg_pub.Add('Y', 'ERROR');
1807 END IF;
1808
1809 -- Validate that Order Type has valid WF assignment
1810
1811 IF NOT OE_BULK_WF_UTIL.Validate_OT_WF_Assignment
1812 (p_header_rec.order_type_id(i)
1813 ,p_header_rec.wf_process_name(i)) THEN
1814 p_header_rec.lock_control(i) := -99;
1815 FND_MESSAGE.SET_NAME('ONT','OE_MISS_FLOW');
1816 oe_bulk_msg_pub.Add('Y', 'ERROR');
1817 END IF;
1818
1819 -- Populate order type name, this is denormalized onto header
1820 -- record as it will be used in insert into mtl_sales_orders
1821
1822 p_header_rec.order_type_name(i) :=
1823 OE_Bulk_Cache.G_ORDER_TYPE_TBL(l_c_index).NAME;
1824
1825 ELSE
1826
1827 p_header_rec.lock_control(i) := -99;
1828 FND_MESSAGE.SET_NAME('ONT','OE_ATTRIBUTE_REQUIRED');
1829 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1830 OE_Order_UTIL.Get_Attribute_Name('ORDER_TYPE_ID'));
1831 oe_bulk_msg_pub.Add('Y', 'ERROR');
1832
1833 -- To avoid Insert failure, populate not null column.
1834 -- This record will be deleted later.
1835
1836 p_header_rec.order_type_id(i) := -99;
1837
1838 END IF; -- Order Type is not null
1839
1840
1841 -- Check that Transactional currency exists
1842 -- If it does validate Price List currency against it.
1843
1844 IF (p_header_rec.transactional_curr_code(i) IS NULL) THEN
1845
1846 p_header_rec.lock_control(i) := -99;
1847
1848 -- To avoid Insert failure, populate not null column.
1849 -- This record will be deleted later.
1850 p_header_rec.transactional_curr_code(i) := '-99';
1851
1852 FND_MESSAGE.SET_NAME('ONT','OE_ATTRIBUTE_REQUIRED');
1853 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1854 OE_Order_UTIL.Get_Attribute_Name('TRANSACTIONAL_CURR_CODE'));
1855 oe_bulk_msg_pub.Add('Y', 'ERROR');
1856
1857 END IF;
1858
1859
1860 -- Price List Validations
1861
1862 IF (p_header_rec.price_list_id(i) IS NOT NULL) THEN
1863
1864 IF NOT Validate_Price_List(p_header_rec.price_list_id(i)
1865 ,p_header_rec.transactional_curr_code(i)
1866 ,p_header_rec.pricing_date(i)
1867 ,'Y'
1868 )
1869 THEN
1870
1871 p_header_rec.lock_control(i) := -99 ;
1872
1873 END IF;
1874
1875 END IF;
1876
1877
1878 -- Conversion rate, date should be null when conversion type is null.
1879
1880 IF (p_header_rec.conversion_type_code(i) IS NULL) THEN
1881 IF p_header_rec.conversion_rate(i) IS NOT NULL
1882 OR p_header_rec.conversion_rate_date(i) IS NOT NULL
1883 THEN
1884 p_header_rec.lock_control(i) := -99;
1885 FND_MESSAGE.SET_NAME('ONT','OE_VAL_CONVERSION_TYPE');
1886 oe_bulk_msg_pub.Add('Y', 'ERROR');
1887 END IF;
1888 END IF;
1889
1890 -- Conversion rate must be Null if coversion type is not User
1891 -- Removed the validation for conversion date Bug 3220059
1892 IF p_header_rec.conversion_type_code(i) <> 'User' AND
1893 p_header_rec.conversion_rate(i) IS NOT NULL
1894 THEN
1895 l_return_status := FND_API.G_RET_STS_ERROR;
1896 FND_MESSAGE.SET_NAME('ONT','OE_VALIDATION_CONV_TYPE');
1897 OE_MSG_PUB.ADD;
1898
1899 END IF; -- END of checks based on conversion type
1900
1901
1902 -- Agreement related validations
1903
1904 IF (p_header_rec.agreement_id(i) IS NOT NULL) THEN
1905
1906 -- Error messages are populated in Validate_Agreement
1907
1908 IF NOT Validate_Agreement(p_header_rec.agreement_id(i)
1909 ,p_header_rec.pricing_date(i)
1910 ,p_header_rec.price_list_id(i)
1911 ,p_header_rec.sold_to_org_id(i)
1912 )
1913 THEN
1914
1915 p_header_rec.lock_control(i) := -99;
1916
1917 END IF;
1918
1919 END IF;
1920
1921
1922 -- BEGIN: Site, Contact Validations
1923
1924 -- Validate Sold-to Contact
1925 -- Validate Active status, since contact does go thru attribute
1926 -- validation.
1927 IF (p_header_rec.sold_to_contact_id(i) IS NOT NULL) THEN
1928 BEGIN
1929
1930 SELECT 'VALID'
1931 INTO l_dummy
1932 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
1933 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_header_rec.sold_to_contact_id(i)
1934 AND ACCT_ROLE.CUST_ACCOUNT_ID = p_header_rec.sold_to_org_id(i)
1935 AND ROWNUM = 1
1936 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1937 AND STATUS = 'A';
1938
1939 EXCEPTION
1940 WHEN NO_DATA_FOUND THEN
1941 p_header_rec.lock_control(i) := -99;
1942 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1943 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1944 OE_Order_Util.Get_Attribute_Name('SOLD_TO_CONTACT_ID'));
1945 oe_bulk_msg_pub.Add('Y', 'ERROR');
1946 END;
1947 END IF; -- End sold to contact validation
1948
1949 -- Validate Bill-to for customer
1950 IF (p_header_rec.invoice_to_org_id(i) IS NOT NULL) THEN
1951
1952 IF NOT Validate_Bill_To(p_header_rec.sold_to_org_id(i),
1953 p_header_rec.invoice_to_org_id(i))
1954 THEN
1955
1956 p_header_rec.lock_control(i) := -99;
1957 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1958 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1959 OE_Order_Util.Get_Attribute_Name('invoice_to_org_id'));
1960 oe_bulk_msg_pub.Add('Y', 'ERROR');
1961
1962 END IF;
1963
1964 END IF; -- Invoice to is not null
1965
1966 -- Validate Ship To for customer
1967 IF (p_header_rec.ship_to_org_id(i) IS NOT NULL) THEN
1968
1969 IF NOT Validate_Ship_To(p_header_rec.sold_to_org_id(i),
1970 p_header_rec.ship_to_org_id(i))
1971 THEN
1972
1973 p_header_rec.lock_control(i) := -99;
1974 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1975 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1976 OE_Order_Util.Get_Attribute_Name('SHIP_TO_ORG_ID'));
1977 oe_bulk_msg_pub.Add('Y', 'ERROR');
1978
1979 END IF;
1980
1981 END IF; -- Ship to is not null
1982
1983 --abghosh
1984 -- Validate sold_to_site_use_id for customer
1985 IF (p_header_rec.sold_to_site_use_id(i) IS NOT NULL) THEN
1986
1987 IF NOT Validate_Sold_to_site(p_header_rec.sold_to_org_id(i),
1988 p_header_rec.sold_to_site_use_id(i))
1989 THEN
1990
1991 p_header_rec.lock_control(i) := -99;
1992 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
1993 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1994 OE_Order_Util.Get_Attribute_Name('SOLD_TO_SITE_USE_ID'));
1995 oe_bulk_msg_pub.Add('Y', 'ERROR');
1996
1997 END IF;
1998
1999 END IF;
2000
2001 --{bug 5054618
2002 -- end customer
2003 IF (p_header_rec.end_Customer_id(i) IS NOT NULL) THEN
2004 IF NOT Validate_End_Customer( p_header_rec.end_Customer_id(i)) THEN
2005 p_header_rec.lock_control(i) := -99;
2006 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2007 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_id'));
2008 oe_bulk_msg_pub.Add('Y', 'ERROR');
2009 END IF;
2010 End IF;
2011
2012 IF (p_header_rec.end_Customer_contact_id(i) IS NOT NULL) THEN
2013 IF NOT Validate_End_Customer_contact( p_header_rec.end_Customer_contact_id(i)) THEN
2014 p_header_rec.lock_control(i) := -99;
2015 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2016 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_contact_id'));
2017 oe_bulk_msg_pub.Add('Y', 'ERROR');
2018 END IF;
2019 END IF;
2020
2021 IF (p_header_rec.end_Customer_site_use_id(i) IS NOT NULL) THEN
2022 IF NOT Validate_End_Customer_site_use(p_header_rec.end_Customer_site_use_id(i),p_header_rec.end_customer_id(i)) THEN
2023 p_header_rec.lock_control(i) := -99;
2024 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2025 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_site_use_id'));
2026 oe_bulk_msg_pub.Add('Y', 'ERROR');
2027 END IF;
2028 END IF;
2029
2030 IF (p_header_rec.IB_owner(i) IS NOT NULL) THEN
2031 IF NOT validate_IB_Owner(p_header_rec.IB_Owner(i)) THEN
2032 p_header_rec.lock_control(i) := -99;
2033 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2034 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('IB_Owner'));
2035 oe_bulk_msg_pub.Add('Y', 'ERROR');
2036 END IF;
2037 END IF;
2038
2039 IF (p_header_rec.IB_current_location(i) IS NOT NULL) THEN
2040 IF NOT Validate_IB_current_location( p_header_rec.IB_current_Location(i)) THEN
2041 p_header_rec.lock_control(i) := -99;
2042 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2043 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('IB_location'));
2044 oe_bulk_msg_pub.Add('Y', 'ERROR');
2045 END IF;
2046 END IF;
2047
2048 IF (p_header_rec.IB_Installed_at_location(i) IS NOT NULL) THEN
2049 IF NOT Validate_IB_Inst_loc( p_header_rec.IB_Installed_at_location(i)) THEN
2050 p_header_rec.lock_control(i) := -99;
2051 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2052 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_site_use_id'));
2053 oe_bulk_msg_pub.Add('Y', 'ERROR');
2054 END IF;
2055 END IF;
2056 --bug 5054618}
2057
2058 -- Validate Deliver-to for customer
2059 IF (p_header_rec.deliver_to_org_id(i) IS NOT NULL) THEN
2060
2061 IF NOT Validate_Deliver_To(p_header_rec.sold_to_org_id(i)
2062 ,p_header_rec.deliver_to_org_id(i))
2063 THEN
2064
2065 p_header_rec.lock_control(i) := -99;
2066 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2067 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2068 OE_Order_Util.Get_Attribute_Name('DELIVER_TO_ORG_ID'));
2069 oe_bulk_msg_pub.Add('Y', 'ERROR');
2070
2071 END IF;
2072
2073 END IF; -- deliver to is not null
2074
2075 -- Validate Bill to contact
2076 IF (p_header_rec.invoice_to_contact_id(i) IS NOT NULL) THEN
2077
2078 IF NOT Validate_Site_Contact(p_header_rec.invoice_to_org_id(i)
2079 ,p_header_rec.invoice_to_contact_id(i)
2080 )
2081 THEN
2082
2083 p_header_rec.lock_control(i) := -99;
2084 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2085 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2086 OE_Order_Util.Get_Attribute_Name('invoice_to_contact_id'));
2087 oe_bulk_msg_pub.Add('Y', 'ERROR');
2088
2089 END IF;
2090
2091 END IF;
2092
2093 -- Validate ship to contact
2094 IF (p_header_rec.ship_to_contact_id(i) IS NOT NULL) THEN
2095
2096 IF NOT Validate_Site_Contact(p_header_rec.ship_to_org_id(i)
2097 ,p_header_rec.ship_to_contact_id(i)
2098 )
2099 THEN
2100
2101 p_header_rec.lock_control(i) := -99;
2102 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2103 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2104 OE_Order_Util.Get_Attribute_Name('SHIP_TO_CONTACT_ID'));
2105 oe_bulk_msg_pub.Add('Y', 'ERROR');
2106
2107 END IF;
2108
2109 END IF;
2110
2111 -- Validate deliver to contact
2112 IF (p_header_rec.deliver_to_contact_id(i) IS NOT NULL) THEN
2113
2114 IF NOT Validate_Site_Contact(p_header_rec.deliver_to_org_id(i)
2115 ,p_header_rec.deliver_to_contact_id(i)
2116 )
2117 THEN
2118
2119 p_header_rec.lock_control(i) := -99;
2120 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2121 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2122 OE_Order_Util.Get_Attribute_Name('DELIVER_TO_CONTACT_ID'));
2123 oe_bulk_msg_pub.Add('Y', 'ERROR');
2124
2125 END IF;
2126
2127 END IF;
2128
2129 -- END: Site, Contact Validations
2130
2131
2132 -- BEGIN: Check for Tax Exemption attributes
2133
2134 IF (p_header_rec.tax_exempt_flag(i) = 'E') THEN
2135
2136 -- Tax exempt reason code is required
2137 IF (p_header_rec.tax_exempt_reason_code(i) IS NULL) THEN
2138
2139 p_header_rec.lock_control(i) := -99;
2140 FND_MESSAGE.SET_NAME('ONT','OE_ATTRIBUTE_REQUIRED');
2141 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2142 OE_Order_UTIL.Get_Attribute_Name('TAX_EXEMPT_REASON_CODE'));
2143 oe_bulk_msg_pub.Add('Y', 'ERROR');
2144
2145 ELSIF NOT Valid_Tax_Exempt_Reason
2146 (p_header_rec.tax_exempt_reason_code(i)) THEN
2147
2148 p_header_rec.lock_control(i) := -99;
2149 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2150 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2151 OE_Order_UTIL.Get_Attribute_Name('TAX_EXEMPT_REASON_CODE'));
2152 oe_bulk_msg_pub.Add('Y', 'ERROR');
2153
2154 END IF;
2155
2156 ELSIF (p_header_rec.tax_exempt_flag(i) = 'R') THEN
2157
2158 IF (p_header_rec.tax_exempt_number(i) IS NOT NULL)
2159 OR (p_header_rec.tax_exempt_reason_code(i) IS NOT NULL)
2160 THEN
2161
2162 p_header_rec.lock_control(i) := -99;
2163 FND_MESSAGE.SET_NAME('ONT','OE_TAX_EXEMPTION_NOT_ALLOWED');
2164 oe_bulk_msg_pub.Add('Y', 'ERROR');
2165
2166 END IF;
2167
2168 -- Validate Tax Exempt # and reason for this customer and site
2169 ELSIF (p_header_rec.tax_exempt_flag(i) = 'S')
2170 AND (p_header_rec.tax_exempt_number(i) IS NOT NULL)
2171 AND(p_header_rec.tax_exempt_reason_code(i) IS NOT NULL)
2172 THEN
2173
2174 --commented for bug 7685103
2175 -- No need to validate execption number irrespective of tax_exempt_flags
2176 /* IF NOT Valid_Tax_Exemptions(p_header_rec.tax_exempt_number(i)
2177 ,p_header_rec.tax_exempt_reason_code(i)
2178 ,p_header_rec.ship_to_org_id(i)
2179 ,p_header_rec.invoice_to_org_id(i)
2180 ,p_header_rec.sold_to_org_id(i)
2181 ,p_header_rec.request_date(i)
2182 )
2183 THEN
2184
2185 p_header_rec.lock_control(i) := -99;
2186 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2187 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2188 OE_Order_Util.Get_Attribute_Name('tax_exempt_number'));
2189 oe_bulk_msg_pub.Add('Y', 'ERROR');
2190
2191 END IF;
2192 */
2193 NULL;
2194 END IF;
2195
2196 -- END: Check for Tax Exemption attributes
2197
2198
2199 -- Duplicate PO Number Validation
2200
2201 IF p_header_rec.cust_po_number(i) IS NOT NULL THEN
2202
2203 IF OE_Validate_Header.Is_Duplicate_PO_Number
2204 (p_header_rec.cust_po_number(i)
2205 ,p_header_rec.sold_to_org_id(i)
2206 ,p_header_rec.header_id(i)
2207 )
2208 THEN
2209 FND_MESSAGE.SET_NAME('ONT','OE_VAL_DUP_PO_NUMBER');
2210 oe_bulk_msg_pub.ADD;
2211 END IF;
2212
2213 END IF;
2214
2215
2216 -- BEGIN: Desc Flex Validation
2217
2218 IF p_validate_desc_flex = 'Y' THEN
2219
2220 IF OE_Bulk_Order_Pvt.G_OE_HEADER_ATTRIBUTES = 'Y' THEN
2221
2222 IF NOT OE_VALIDATE.Header_Desc_Flex
2223 (p_context => p_header_rec.context(i)
2224 ,p_attribute1 => p_header_rec.attribute1(i)
2225 ,p_attribute2 => p_header_rec.attribute2(i)
2226 ,p_attribute3 => p_header_rec.attribute3(i)
2227 ,p_attribute4 => p_header_rec.attribute4(i)
2228 ,p_attribute5 => p_header_rec.attribute5(i)
2229 ,p_attribute6 => p_header_rec.attribute6(i)
2230 ,p_attribute7 => p_header_rec.attribute7(i)
2231 ,p_attribute8 => p_header_rec.attribute8(i)
2232 ,p_attribute9 => p_header_rec.attribute9(i)
2233 ,p_attribute10 => p_header_rec.attribute10(i)
2234 ,p_attribute11 => p_header_rec.attribute11(i)
2235 ,p_attribute12 => p_header_rec.attribute12(i)
2236 ,p_attribute13 => p_header_rec.attribute13(i)
2237 ,p_attribute14 => p_header_rec.attribute14(i)
2238 ,p_attribute15 => p_header_rec.attribute15(i)
2239 ,p_attribute16 => p_header_rec.attribute16(i) -- for bug 2184255
2240 ,p_attribute17 => p_header_rec.attribute17(i)
2241 ,p_attribute18 => p_header_rec.attribute18(i)
2242 ,p_attribute19 => p_header_rec.attribute19(i)
2243 ,p_attribute20 => p_header_rec.attribute20(i))
2244 THEN
2245 p_header_rec.lock_control(i) := -99;
2246 -- Log Error Message
2247 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2248 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2249 'Entity:Flexfield:Header_Desc_Flex');
2250 oe_bulk_msg_pub.Add('Y', 'ERROR');
2251 ELSE -- if the flex validation is successfull
2252 -- For bug 2511313
2253 IF p_header_rec.context(i) IS NULL
2254 OR p_header_rec.context(i) = FND_API.G_MISS_CHAR THEN
2255 p_header_rec.context(i) := oe_validate.g_context;
2256 END IF;
2257
2258 IF p_header_rec.attribute1(i) IS NULL
2259 OR p_header_rec.attribute1(i) = FND_API.G_MISS_CHAR THEN
2260 p_header_rec.attribute1(i) := oe_validate.g_attribute1;
2261 END IF;
2262
2263 IF p_header_rec.attribute2(i) IS NULL
2264 OR p_header_rec.attribute2(i) = FND_API.G_MISS_CHAR THEN
2265 p_header_rec.attribute2(i) := oe_validate.g_attribute2;
2266 END IF;
2267
2268 IF p_header_rec.attribute3(i) IS NULL
2269 OR p_header_rec.attribute3(i) = FND_API.G_MISS_CHAR THEN
2270 p_header_rec.attribute3(i) := oe_validate.g_attribute3;
2271 END IF;
2272
2273 IF p_header_rec.attribute4(i) IS NULL
2274 OR p_header_rec.attribute4(i) = FND_API.G_MISS_CHAR THEN
2275 p_header_rec.attribute4(i) := oe_validate.g_attribute4;
2276 END IF;
2277
2278 IF p_header_rec.attribute5(i) IS NULL
2279 OR p_header_rec.attribute5(i) = FND_API.G_MISS_CHAR THEN
2280 p_header_rec.attribute5(i) := oe_validate.g_attribute5;
2281 END IF;
2282
2283 IF p_header_rec.attribute6(i) IS NULL
2284 OR p_header_rec.attribute6(i) = FND_API.G_MISS_CHAR THEN
2285 p_header_rec.attribute6(i) := oe_validate.g_attribute6;
2286 END IF;
2287
2288 IF p_header_rec.attribute7(i) IS NULL
2289 OR p_header_rec.attribute7(i) = FND_API.G_MISS_CHAR THEN
2290 p_header_rec.attribute7(i) := oe_validate.g_attribute7;
2291 END IF;
2292
2293 IF p_header_rec.attribute8(i) IS NULL
2294 OR p_header_rec.attribute8(i) = FND_API.G_MISS_CHAR THEN
2295 p_header_rec.attribute8(i) := oe_validate.g_attribute8;
2296 END IF;
2297
2298 IF p_header_rec.attribute9(i) IS NULL
2299 OR p_header_rec.attribute9(i) = FND_API.G_MISS_CHAR THEN
2300 p_header_rec.attribute9(i) := oe_validate.g_attribute9;
2301 END IF;
2302
2303 IF p_header_rec.attribute10(i) IS NULL
2304 OR p_header_rec.attribute10(i) = FND_API.G_MISS_CHAR THEN
2305 p_header_rec.attribute10(i) := Oe_validate.G_attribute10;
2306 End IF;
2307
2308 IF p_header_rec.attribute11(i) IS NULL
2309 OR p_header_rec.attribute11(i) = FND_API.G_MISS_CHAR THEN
2310 p_header_rec.attribute11(i) := oe_validate.g_attribute11;
2311 END IF;
2312
2313 IF p_header_rec.attribute12(i) IS NULL
2314 OR p_header_rec.attribute12(i) = FND_API.G_MISS_CHAR THEN
2315 p_header_rec.attribute12(i) := oe_validate.g_attribute12;
2316 END IF;
2317
2318 IF p_header_rec.attribute13(i) IS NULL
2319 OR p_header_rec.attribute13(i) = FND_API.G_MISS_CHAR THEN
2320 p_header_rec.attribute13(i) := oe_validate.g_attribute13;
2321 END IF;
2322
2323 IF p_header_rec.attribute14(i) IS NULL
2324 OR p_header_rec.attribute14(i) = FND_API.G_MISS_CHAR THEN
2325 p_header_rec.attribute14(i) := oe_validate.g_attribute14;
2326 END IF;
2327
2328 IF p_header_rec.attribute15(i) IS NULL
2329 OR p_header_rec.attribute15(i) = FND_API.G_MISS_CHAR THEN
2330 p_header_rec.attribute15(i) := oe_validate.g_attribute15;
2331 END IF;
2332
2333 IF p_header_rec.attribute16(i) IS NULL -- For bug 2184255
2334 OR p_header_rec.attribute16(i) = FND_API.G_MISS_CHAR THEN
2335 p_header_rec.attribute16(i) := oe_validate.g_attribute16;
2336 END IF;
2337
2338 IF p_header_rec.attribute17(i) IS NULL
2339 OR p_header_rec.attribute17(i) = FND_API.G_MISS_CHAR THEN
2340 p_header_rec.attribute17(i) := oe_validate.g_attribute17;
2341 END IF;
2342
2343 IF p_header_rec.attribute18(i) IS NULL
2344 OR p_header_rec.attribute18(i) = FND_API.G_MISS_CHAR THEN
2345 p_header_rec.attribute18(i) := oe_validate.g_attribute18;
2346 END IF;
2347
2348 IF p_header_rec.attribute19(i) IS NULL
2349 OR p_header_rec.attribute19(i) = FND_API.G_MISS_CHAR THEN
2350 p_header_rec.attribute19(i) := oe_validate.g_attribute19;
2351 END IF;
2352
2353 IF p_header_rec.attribute20(i) IS NULL
2354 OR p_header_rec.attribute20(i) = FND_API.G_MISS_CHAR THEN
2355 p_header_rec.attribute20(i) := oe_validate.g_attribute20;
2356 END IF;
2357
2358 -- end of assignments, bug 2511313
2359
2360 END IF;
2361 END IF;
2362
2363 IF OE_Bulk_Order_Pvt.G_OE_HEADER_GLOBAL_ATTRIBUTE = 'Y' THEN
2364
2365 IF NOT OE_VALIDATE.G_Header_Desc_Flex
2366 (p_context => p_header_rec.global_attribute_category(i)
2367 ,p_attribute1 => p_header_rec.global_attribute1(i)
2368 ,p_attribute2 => p_header_rec.global_attribute2(i)
2369 ,p_attribute3 => p_header_rec.global_attribute3(i)
2370 ,p_attribute4 => p_header_rec.global_attribute4(i)
2371 ,p_attribute5 => p_header_rec.global_attribute5(i)
2372 ,p_attribute6 => p_header_rec.global_attribute6(i)
2373 ,p_attribute7 => p_header_rec.global_attribute7(i)
2374 ,p_attribute8 => p_header_rec.global_attribute8(i)
2375 ,p_attribute9 => p_header_rec.global_attribute9(i)
2376 ,p_attribute10 => p_header_rec.global_attribute10(i)
2377 ,p_attribute11 => p_header_rec.global_attribute11(i)
2378 ,p_attribute12 => p_header_rec.global_attribute12(i)
2379 ,p_attribute13 => p_header_rec.global_attribute13(i)
2380 ,p_attribute14 => p_header_rec.global_attribute13(i)
2381 ,p_attribute15 => p_header_rec.global_attribute14(i)
2382 ,p_attribute16 => p_header_rec.global_attribute16(i)
2383 ,p_attribute17 => p_header_rec.global_attribute17(i)
2384 ,p_attribute18 => p_header_rec.global_attribute18(i)
2385 ,p_attribute19 => p_header_rec.global_attribute19(i)
2386 ,p_attribute20 => p_header_rec.global_attribute20(i))
2387 THEN
2388 p_header_rec.lock_control(i) := -99;
2389 -- Log Error Message
2390 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2391 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2392 'Entity:Flexfield:G_Header_Desc_Flex');
2393 oe_bulk_msg_pub.Add('Y', 'ERROR');
2394
2395 ELSE -- for bug 2511313
2396 IF p_header_rec.global_attribute_category(i) IS NULL
2397 OR p_header_rec.global_attribute_category(i) = FND_API.G_MISS_CHAR THEN
2398 p_header_rec.global_attribute_category(i) := oe_validate.g_context;
2399 END IF;
2400
2401 IF p_header_rec.global_attribute1(i) IS NULL
2402 OR p_header_rec.global_attribute1(i) = FND_API.G_MISS_CHAR THEN
2403 p_header_rec.global_attribute1(i) := oe_validate.g_attribute1;
2404 END IF;
2405
2406 IF p_header_rec.global_attribute2(i) IS NULL
2407 OR p_header_rec.global_attribute2(i) = FND_API.G_MISS_CHAR THEN
2408 p_header_rec.global_attribute2(i) := oe_validate.g_attribute2;
2409 END IF;
2410
2411 IF p_header_rec.global_attribute3(i) IS NULL
2412 OR p_header_rec.global_attribute3(i) = FND_API.G_MISS_CHAR THEN
2413 p_header_rec.global_attribute3(i) := oe_validate.g_attribute3;
2414 END IF;
2415
2416 IF p_header_rec.global_attribute4(i) IS NULL
2417 OR p_header_rec.global_attribute4(i) = FND_API.G_MISS_CHAR THEN
2418 p_header_rec.global_attribute4(i) := oe_validate.g_attribute4;
2419 END IF;
2420
2421 IF p_header_rec.global_attribute5(i) IS NULL
2422 OR p_header_rec.global_attribute5(i) = FND_API.G_MISS_CHAR THEN
2423 p_header_rec.global_attribute5(i) := oe_validate.g_attribute5;
2424 END IF;
2425
2426 IF p_header_rec.global_attribute6(i) IS NULL
2427 OR p_header_rec.global_attribute6(i) = FND_API.G_MISS_CHAR THEN
2428 p_header_rec.global_attribute6(i) := oe_validate.g_attribute6;
2429 END IF;
2430
2431 IF p_header_rec.global_attribute7(i) IS NULL
2432 OR p_header_rec.global_attribute7(i) = FND_API.G_MISS_CHAR THEN
2433 p_header_rec.global_attribute7(i) := oe_validate.g_attribute7;
2434 END IF;
2435
2436 IF p_header_rec.global_attribute8(i) IS NULL
2437 OR p_header_rec.global_attribute8(i) = FND_API.G_MISS_CHAR THEN
2438 p_header_rec.global_attribute8(i) := oe_validate.g_attribute8;
2439 END IF;
2440
2441 IF p_header_rec.global_attribute9(i) IS NULL
2442 OR p_header_rec.global_attribute9(i) = FND_API.G_MISS_CHAR THEN
2443 p_header_rec.global_attribute9(i) := oe_validate.g_attribute9;
2444 END IF;
2445
2446 IF p_header_rec.global_attribute10(i) IS NULL
2447 OR p_header_rec.global_attribute10(i) = FND_API.G_MISS_CHAR THEN
2448 p_header_rec.global_attribute10(i) := oe_validate.g_attribute10;
2449 END IF;
2450
2451 IF p_header_rec.global_attribute11(i) IS NULL
2452 OR p_header_rec.global_attribute11(i) = FND_API.G_MISS_CHAR THEN
2453 p_header_rec.global_attribute11(i) := oe_validate.g_attribute11;
2454 END IF;
2455
2456 IF p_header_rec.global_attribute12(i) IS NULL
2457 OR p_header_rec.global_attribute12(i) = FND_API.G_MISS_CHAR THEN
2458 p_header_rec.global_attribute12(i) := oe_validate.g_attribute12;
2459 END IF;
2460
2461 IF p_header_rec.global_attribute13(i) IS NULL
2462 OR p_header_rec.global_attribute13(i) = FND_API.G_MISS_CHAR THEN
2463 p_header_rec.global_attribute13(i) := oe_validate.g_attribute13;
2464 END IF;
2465
2466 IF p_header_rec.global_attribute14(i) IS NULL
2467 OR p_header_rec.global_attribute14(i) = FND_API.G_MISS_CHAR THEN
2468 p_header_rec.global_attribute14(i) := oe_validate.g_attribute14;
2469 END IF;
2470
2471 IF p_header_rec.global_attribute15(i) IS NULL
2472 OR p_header_rec.global_attribute15(i) = FND_API.G_MISS_CHAR THEN
2473 p_header_rec.global_attribute15(i) := oe_validate.g_attribute15;
2474 END IF;
2475
2476 IF p_header_rec.global_attribute16(i) IS NULL
2477 OR p_header_rec.global_attribute16(i) = FND_API.G_MISS_CHAR THEN
2478 p_header_rec.global_attribute16(i) := oe_validate.g_attribute16;
2479 END IF;
2480
2481 IF p_header_rec.global_attribute17(i) IS NULL
2482 OR p_header_rec.global_attribute17(i) = FND_API.G_MISS_CHAR THEN
2483 p_header_rec.global_attribute17(i) := oe_validate.g_attribute17;
2484 END IF;
2485
2486 IF p_header_rec.global_attribute18(i) IS NULL
2487 OR p_header_rec.global_attribute18(i) = FND_API.G_MISS_CHAR THEN
2488 p_header_rec.global_attribute18(i) := oe_validate.g_attribute18;
2489 END IF;
2490
2491 IF p_header_rec.global_attribute19(i) IS NULL
2492 OR p_header_rec.global_attribute19(i) = FND_API.G_MISS_CHAR THEN
2493 p_header_rec.global_attribute19(i) := oe_validate.g_attribute19;
2494 END IF;
2495
2496 IF p_header_rec.global_attribute20(i) IS NULL
2497 OR p_header_rec.global_attribute20(i) = FND_API.G_MISS_CHAR THEN
2498 p_header_rec.global_attribute20(i) := oe_validate.g_attribute20;
2499 END IF;
2500 -- end of bug 2511313
2501
2502 END IF;
2503 END IF;
2504
2505 IF OE_Bulk_Order_Pvt.G_OE_HEADER_TP_ATTRIBUTES = 'Y' THEN
2506
2507 IF NOT OE_VALIDATE.TP_Header_Desc_Flex
2508 (p_context => p_header_rec.tp_context(i)
2509 ,p_attribute1 => p_header_rec.tp_attribute1(i)
2510 ,p_attribute2 => p_header_rec.tp_attribute2(i)
2511 ,p_attribute3 => p_header_rec.tp_attribute3(i)
2512 ,p_attribute4 => p_header_rec.tp_attribute4(i)
2513 ,p_attribute5 => p_header_rec.tp_attribute5(i)
2514 ,p_attribute6 => p_header_rec.tp_attribute6(i)
2515 ,p_attribute7 => p_header_rec.tp_attribute7(i)
2516 ,p_attribute8 => p_header_rec.tp_attribute8(i)
2517 ,p_attribute9 => p_header_rec.tp_attribute9(i)
2518 ,p_attribute10 => p_header_rec.tp_attribute10(i)
2519 ,p_attribute11 => p_header_rec.tp_attribute11(i)
2520 ,p_attribute12 => p_header_rec.tp_attribute12(i)
2521 ,p_attribute13 => p_header_rec.tp_attribute13(i)
2522 ,p_attribute14 => p_header_rec.tp_attribute14(i)
2523 ,p_attribute15 => p_header_rec.tp_attribute15(i))
2524 THEN
2525 p_header_rec.lock_control(i) := -99;
2526 -- Log Error Message
2527 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2528 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2529 'Entity:Flexfield:TP_Header_Desc_Flex');
2530 oe_bulk_msg_pub.Add('Y', 'ERROR');
2531 ELSE -- for bug 2511313
2532
2533 IF p_header_rec.tp_context(i) IS NULL
2534 OR p_header_rec.tp_context(i) = FND_API.G_MISS_CHAR THEN
2535 p_header_rec.tp_context(i) := oe_validate.g_context;
2536 END IF;
2537
2538 IF p_header_rec.tp_attribute1(i) IS NULL
2539 OR p_header_rec.tp_attribute1(i) = FND_API.G_MISS_CHAR THEN
2540 p_header_rec.tp_attribute1(i) := oe_validate.g_attribute1;
2541 END IF;
2542
2543 IF p_header_rec.tp_attribute2(i) IS NULL
2544 OR p_header_rec.tp_attribute2(i) = FND_API.G_MISS_CHAR THEN
2545 p_header_rec.tp_attribute2(i) := oe_validate.g_attribute2;
2546 END IF;
2547
2548 IF p_header_rec.tp_attribute3(i) IS NULL
2549 OR p_header_rec.tp_attribute3(i) = FND_API.G_MISS_CHAR THEN
2550 p_header_rec.tp_attribute3(i) := oe_validate.g_attribute3;
2551 END IF;
2552
2553 IF p_header_rec.tp_attribute4(i) IS NULL
2554 OR p_header_rec.tp_attribute4(i) = FND_API.G_MISS_CHAR THEN
2555 p_header_rec.tp_attribute4(i) := oe_validate.g_attribute4;
2556 END IF;
2557
2558 IF p_header_rec.tp_attribute5(i) IS NULL
2559 OR p_header_rec.tp_attribute5(i) = FND_API.G_MISS_CHAR THEN
2560 p_header_rec.tp_attribute5(i) := oe_validate.g_attribute5;
2561 END IF;
2562
2563 IF p_header_rec.tp_attribute6(i) IS NULL
2564 OR p_header_rec.tp_attribute6(i) = FND_API.G_MISS_CHAR THEN
2565 p_header_rec.tp_attribute6(i) := oe_validate.g_attribute6;
2566 END IF;
2567
2568 IF p_header_rec.tp_attribute7(i) IS NULL
2569 OR p_header_rec.tp_attribute7(i) = FND_API.G_MISS_CHAR THEN
2570 p_header_rec.tp_attribute7(i) := oe_validate.g_attribute7;
2571 END IF;
2572
2573 IF p_header_rec.tp_attribute8(i) IS NULL
2574 OR p_header_rec.tp_attribute8(i) = FND_API.G_MISS_CHAR THEN
2575 p_header_rec.tp_attribute8(i) := oe_validate.g_attribute8;
2576 END IF;
2577
2578 IF p_header_rec.tp_attribute9(i) IS NULL
2579 OR p_header_rec.tp_attribute9(i) = FND_API.G_MISS_CHAR THEN
2580 p_header_rec.tp_attribute9(i) := oe_validate.g_attribute9;
2581 END IF;
2582
2583 IF p_header_rec.tp_attribute10(i) IS NULL
2584 OR p_header_rec.tp_attribute10(i) = FND_API.G_MISS_CHAR THEN
2585 p_header_rec.tp_attribute10(i) := Oe_validate.G_attribute10;
2586 End IF;
2587
2588 IF p_header_rec.tp_attribute11(i) IS NULL
2589 OR p_header_rec.tp_attribute11(i) = FND_API.G_MISS_CHAR THEN
2590 p_header_rec.tp_attribute11(i) := oe_validate.g_attribute11;
2591 END IF;
2592
2593 IF p_header_rec.tp_attribute12(i) IS NULL
2594 OR p_header_rec.tp_attribute12(i) = FND_API.G_MISS_CHAR THEN
2595 p_header_rec.tp_attribute12(i) := oe_validate.g_attribute12;
2596 END IF;
2597
2598 IF p_header_rec.tp_attribute13(i) IS NULL
2599 OR p_header_rec.tp_attribute13(i) = FND_API.G_MISS_CHAR THEN
2600 p_header_rec.tp_attribute13(i) := oe_validate.g_attribute13;
2601 END IF;
2602
2603 IF p_header_rec.tp_attribute14(i) IS NULL
2604 OR p_header_rec.tp_attribute14(i) = FND_API.G_MISS_CHAR THEN
2605 p_header_rec.tp_attribute14(i) := oe_validate.g_attribute14;
2606 END IF;
2607
2608 IF p_header_rec.tp_attribute15(i) IS NULL
2609 OR p_header_rec.tp_attribute15(i) = FND_API.G_MISS_CHAR THEN
2610 p_header_rec.tp_attribute15(i) := oe_validate.g_attribute15;
2611 END IF;
2612
2613 END IF;
2614 END IF;
2615
2616 END IF; -- End if p_validate_desc_flex is 'Y'
2617
2618 -- END: Desc Flex Validations
2619
2620
2621 ---------------------------------------------------------------
2622 -- Add a 100% default sales credit record for this salesperson
2623 ---------------------------------------------------------------
2624
2625 IF p_header_rec.salesrep_id(i) IS NOT NULL THEN
2626
2627 x_header_scredit_rec.header_id.extend(1);
2628 x_header_scredit_rec.salesrep_id.extend(1);
2629 x_header_scredit_rec.sales_credit_type_id.extend(1);
2630
2631 l_c_index := OE_Bulk_Cache.Load_Salesrep
2632 (p_key => p_header_rec.salesrep_id(i));
2633
2634 x_header_scredit_rec.header_id(l_scredit_index)
2635 := p_header_rec.header_id(i);
2636 x_header_scredit_rec.salesrep_id(l_scredit_index)
2637 := p_header_rec.salesrep_id(i);
2638 x_header_scredit_rec.sales_credit_type_id(l_scredit_index)
2639 := OE_Bulk_Cache.G_SALESREP_TBL(l_c_index).sales_credit_type_id;
2640
2641 l_scredit_index := l_scredit_index + 1;
2642
2643 END IF;
2644
2645 ---------------------------------------------------------------
2646 -- Load EDI attributes if sold to customer is an EDI customer
2647 ---------------------------------------------------------------
2648
2649 IF OE_GLOBALS.G_EC_INSTALLED = 'Y'
2650 AND p_header_rec.booked_flag(i) = 'Y'
2651 AND nvl(p_header_rec.lock_control(i),0) <> -99
2652 THEN
2653
2654 l_c_index := OE_Bulk_Cache.Load_Sold_To
2655 (p_key => p_header_rec.sold_to_org_id(i)
2656 ,p_edi_attributes => 'Y'
2657 );
2658
2659 IF OE_Bulk_Cache.G_SOLD_TO_TBL(l_c_index).tp_setup THEN
2660
2661 OE_Bulk_Order_Pvt.G_ACK_NEEDED := 'Y';
2662 p_header_rec.first_ack_code(i) := 'X';
2663
2664 -- Cache EDI attributes as these will be used in creating
2665 -- the acknowledgment records later.
2666
2667 IF p_header_rec.invoice_to_org_id(i) IS NOT NULL THEN
2668 l_c_index := OE_Bulk_Cache.Load_Invoice_To
2669 (p_key => p_header_rec.invoice_to_org_id(i)
2670 ,p_edi_attributes => 'Y'
2671 );
2672 END IF;
2673
2674 IF p_header_rec.ship_to_org_id(i) IS NOT NULL THEN
2675 l_c_index := OE_Bulk_Cache.Load_Ship_To
2676 (p_key => p_header_rec.ship_to_org_id(i)
2677 ,p_edi_attributes => 'Y'
2678 );
2679 END IF;
2680
2681 IF p_header_rec.ship_from_org_id(i) IS NOT NULL THEN
2682 l_c_index := OE_Bulk_Cache.Load_Ship_From
2683 (p_key => p_header_rec.ship_from_org_id(i)
2684 );
2685 END IF;
2686
2687 -- added for end customer changes(bug 5054618)
2688
2689 IF p_header_rec.end_customer_id(i) IS NOT NULL THEN
2690 l_c_index := OE_Bulk_Cache.Load_End_customer
2691 (p_key => p_header_rec.end_customer_id(i)
2692 ,p_edi_attributes => 'Y'
2693 );
2694 END IF;
2695
2696 IF p_header_rec.End_customer_site_use_id(i) IS NOT NULL THEN
2697 l_c_index := OE_Bulk_Cache.Load_end_customer_site
2698 (p_key => p_header_rec.end_customer_site_use_id(i)
2699 ,p_edi_attributes => 'Y'
2700 );
2701 END IF;
2702
2703 END IF;
2704
2705 END IF;
2706
2707 ---------------------------------------------------------------
2708 -- Evaluate Holds for header
2709 ---------------------------------------------------------------
2710
2711 IF p_header_rec.lock_control(i) <> -99 THEN
2712
2713 -- Check Header Level Holds
2714 /*ER#7479609 start
2715 OE_Bulk_Holds_PVT.Evaluate_Holds(
2716 p_header_id => p_header_rec.header_id(i),
2717 p_line_id => NULL,
2718 p_line_number => NULL,
2719 p_sold_to_org_id => p_header_rec.sold_to_org_id(i),
2720 p_inventory_item_id => NULL,
2721 p_ship_from_org_id => NULL,
2722 p_invoice_to_org_id => NULL,
2723 p_ship_to_org_id => NULL,
2724 p_top_model_line_id => NULL,
2725 p_ship_set_name => NULL,
2726 p_arrival_set_name => NULL,
2727 p_on_generic_hold => l_on_generic_hold,
2728 p_on_booking_hold => l_on_booking_hold,
2729 p_on_scheduling_hold => l_on_scheduling_hold
2730 );
2731 ER#7479609 end*/
2732
2733 --ER#7479609 start
2734 l_header_rec_for_hold.header_id := p_header_rec.header_id(i);
2735 l_header_rec_for_hold.sold_to_org_id := p_header_rec.sold_to_org_id(i);
2736 l_header_rec_for_hold.sales_channel_code := p_header_rec.sales_channel_code(i);
2737 l_header_rec_for_hold.payment_type_code := p_header_rec.payment_type_code(i);
2738 l_header_rec_for_hold.order_type_id := p_header_rec.order_type_id(i);
2739 l_header_rec_for_hold.transactional_curr_code := p_header_rec.transactional_curr_code(i);
2740
2741 OE_Bulk_Holds_PVT.Evaluate_Holds(
2742 p_header_rec => l_header_rec_for_hold,
2743 p_line_rec => NULL,
2744 p_on_generic_hold => l_on_generic_hold,
2745 p_on_booking_hold => l_on_booking_hold,
2746 p_on_scheduling_hold => l_on_scheduling_hold
2747 );
2748 --ER#7479609 end
2749
2750 END IF;
2751
2752 ---------------------------------------------------------------
2753 -- BOOKING VALIDATIONS
2754 ---------------------------------------------------------------
2755 IF p_header_rec.booked_flag(i) = 'Y' THEN
2756
2757 -- Do not book the Order if the header is on HOLD.
2758
2759 IF l_on_generic_hold OR l_on_booking_hold THEN
2760
2761 FND_MESSAGE.SET_NAME('ONT','OE_BOOKING_HOLD_EXISTS');
2762 oe_bulk_msg_pub.ADD;
2763 p_header_rec.booked_flag(i) := 'N';
2764 --PIB
2765 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' Then
2766 p_header_rec.event_code(i) := 'BATCH';
2767 If l_debug_level > 0 Then
2768 oe_debug_pub.add('event code1 : '||p_header_rec.event_code(i));
2769 End If;
2770 END IF;
2771 --PIB
2772
2773 ELSE
2774
2775 Check_Book_Reqd_Attributes(p_header_rec => p_header_rec
2776 ,p_index => i
2777 ,x_return_status => l_return_status);
2778
2779 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2780 p_header_rec.booked_flag(i) := 'N';
2781 --PIB
2782 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' Then
2783 p_header_rec.event_code(i) := 'BATCH';
2784 If l_debug_level > 0 Then
2785 oe_debug_pub.add('event code2 : '||p_header_rec.event_code(i));
2786 End If;
2787 END IF;
2788 --PIB
2789 END IF;
2790
2791 END IF;
2792
2793 END IF;
2794
2795 -- Update Global table with pointers to Erroneous record
2796
2797 IF (p_header_rec.lock_control(i) IN (-99, -98, -97) ) THEN
2798
2799 error_count := error_count + 1;
2800
2801 OE_Bulk_Order_Pvt.G_ERROR_REC.order_source_id.EXTEND(1);
2802 OE_Bulk_Order_Pvt.G_ERROR_REC.order_source_id(error_count)
2803 := p_header_rec.order_source_id(i);
2804
2805 OE_Bulk_Order_Pvt.G_ERROR_REC.orig_sys_document_ref.EXTEND(1);
2806 OE_Bulk_Order_Pvt.G_ERROR_REC.orig_sys_document_ref(error_count)
2807 := p_header_rec.orig_sys_document_ref(i);
2808
2809 OE_Bulk_Order_Pvt.G_ERROR_REC.header_id.EXTEND(1);
2810 OE_Bulk_Order_Pvt.G_ERROR_REC.header_id(error_count)
2811 := p_header_rec.header_id(i);
2812 OE_Bulk_Order_PVT.G_ERROR_REC.ineligible_for_hvop.EXTEND(1);
2813 OE_Bulk_Order_PVT.G_ERROR_REC.skip_batch.EXTEND(1);
2814
2815 IF p_header_rec.lock_control(i) = -98 THEN
2816 OE_Bulk_Order_PVT.G_ERROR_REC.ineligible_for_hvop(error_count):=
2817 'Y';
2818 ELSIF p_header_rec.lock_control(i) = -97 THEN
2819 OE_Bulk_Order_PVT.G_ERROR_REC.skip_batch(error_count):= 'Y';
2820 END IF;
2821 END IF;
2822
2823 END LOOP;
2824
2825
2826 EXCEPTION
2827 WHEN OTHERS THEN
2828 IF oe_bulk_msg_pub.check_msg_level(oe_bulk_msg_pub.G_MSG_LVL_UNEXP_ERROR)
2829 THEN
2830 oe_bulk_msg_pub.add_exc_msg
2831 ( G_PKG_NAME
2832 , 'Entity'
2833 );
2834 END IF;
2835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2836 END Entity;
2837
2838
2839 END OE_BULK_PROCESS_HEADER;
[Home] [Help]