1 PACKAGE BODY OE_CNCL_VALIDATE_LINE AS
2 /* $Header: OEXVCLNB.pls 120.10.12010000.2 2010/03/03 00:59:51 smusanna ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_CNCL_Validate_Line';
7
8 -- LOCAL PROCEDURES
9
10 -- Check_Book_Reqd_Attributes
11 -- This procedure checks for all the attributes that are required
12 -- on booked order lines.
13
14 PROCEDURE Check_Book_Reqd_Attributes
15 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
16 , x_return_status IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
17 )
18 IS
19 l_proj_ref_enabled NUMBER;
20 l_proj_control_level NUMBER;
21 l_calculate_tax_flag VARCHAR2(1) := 'N';
22 l_line_type_rec OE_Order_Cache.Line_Type_Rec_Type;
23 l_item_type_code VARCHAR2(30);
24 BEGIN
25
26 OE_DEBUG_PUB.Add('Entering OE_CNCL_VALIDATE_LINE.Check_Book_Reqd_Attributes',1);
27 -- Check for fields required on a booked order line
28
29 IF p_line_rec.sold_to_org_id IS NULL THEN
30 x_return_status := FND_API.G_RET_STS_ERROR;
31 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
32 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
33 OE_Order_UTIL.Get_Attribute_Name('SOLD_TO_ORG_ID'));
34 OE_MSG_PUB.ADD;
35 END IF;
36
37 IF p_line_rec.invoice_to_org_id IS NULL THEN
38 x_return_status := FND_API.G_RET_STS_ERROR;
39 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
40 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
41 OE_Order_UTIL.Get_Attribute_Name('INVOICE_TO_ORG_ID'));
42 OE_MSG_PUB.ADD;
43 END IF;
44
45 IF p_line_rec.tax_exempt_flag IS NULL THEN
46 x_return_status := FND_API.G_RET_STS_ERROR;
47 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
48 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
49 OE_Order_UTIL.Get_Attribute_Name('TAX_EXEMPT_FLAG'));
50 OE_MSG_PUB.ADD;
51 END IF;
52
53
54 -- Item, Quantity and UOM Required
55 IF p_line_rec.inventory_item_id IS NULL THEN
56 x_return_status := FND_API.G_RET_STS_ERROR;
57 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
58 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
59 OE_Order_UTIL.Get_Attribute_Name('INVENTORY_ITEM_ID'));
60 OE_MSG_PUB.ADD;
61 END IF;
62
63 IF p_line_rec.order_quantity_uom IS NULL THEN
64 x_return_status := FND_API.G_RET_STS_ERROR;
65 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
66 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
67 OE_Order_UTIL.Get_Attribute_Name('ORDER_QUANTITY_UOM'));
68 OE_MSG_PUB.ADD;
69 END IF;
70
71 -- Fix bug 1277092: ordered quantity should not be = 0 on a booked line
72 IF p_line_rec.ordered_quantity IS NULL
73 OR p_line_rec.ordered_quantity = 0 THEN
74 x_return_status := FND_API.G_RET_STS_ERROR;
75 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
76 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
77 OE_Order_UTIL.Get_Attribute_Name('ORDERED_QUANTITY'));
78 OE_MSG_PUB.ADD;
79 END IF;
80
81 -- For all items that are NOT included items OR config items,
82 -- price list, unit selling price and unit list price are required.
83
84 IF p_line_rec.line_category_code = 'RETURN' THEN
85 l_item_type_code := OE_Line_Util.Get_Return_Item_Type_Code
86 (p_line_rec);
87 ELSE
88 l_item_type_code := p_line_rec.item_type_code;
89 END IF;
90
91 IF (l_item_type_code <> 'INCLUDED'
92 AND l_item_type_code <> 'CONFIG')
93 THEN
94
95 IF p_line_rec.price_list_id IS NULL THEN
96 x_return_status := FND_API.G_RET_STS_ERROR;
97 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
98 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
99 OE_Order_UTIL.Get_Attribute_Name('PRICE_LIST_ID'));
100 OE_MSG_PUB.ADD;
101 END IF;
102
103 IF p_line_rec.unit_list_price IS NULL THEN
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
106 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
107 OE_Order_UTIL.Get_Attribute_Name('UNIT_LIST_PRICE'));
108 OE_MSG_PUB.ADD;
109 END IF;
110
111 IF p_line_rec.unit_selling_price IS NULL THEN
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
114 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
115 OE_Order_UTIL.Get_Attribute_Name('UNIT_SELLING_PRICE'));
116 OE_MSG_PUB.ADD;
117 END IF;
118
119 END IF; -- End of check for pricing attributes.
120
121
122 -- Fix bug 1262790
123 -- Ship To and Payment Term required on ORDER lines,
124 -- NOT on RETURN lines
125
126 IF p_line_rec.line_category_code <> OE_GLOBALS.G_RETURN_CATEGORY_CODE THEN
127
128 IF p_line_rec.ship_to_org_id IS NULL THEN
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
131 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
132 OE_Order_UTIL.Get_Attribute_Name('SHIP_TO_ORG_ID'));
133 OE_MSG_PUB.ADD;
134 END IF;
135
136 IF p_line_rec.payment_term_id IS NULL THEN
137 x_return_status := FND_API.G_RET_STS_ERROR;
138 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
139 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
140 OE_Order_UTIL.Get_Attribute_Name('PAYMENT_TERM_ID'));
141 OE_MSG_PUB.ADD;
142 END IF;
143
144 END IF;
145
146
147 -- Warehouse and schedule date required on RETURN lines
148
149 IF p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE THEN
150
151 IF p_line_rec.ship_from_org_id IS NULL THEN
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_RETURN_ATTRIBUTE');
154 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
155 OE_Order_UTIL.Get_Attribute_Name('SHIP_FROM_ORG_ID'));
156 OE_MSG_PUB.ADD;
157 END IF;
158
159 IF p_line_rec.request_date IS NULL THEN
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_RETURN_ATTRIBUTE');
162 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
163 OE_Order_UTIL.Get_Attribute_Name('REQUEST_DATE'));
164 OE_MSG_PUB.ADD;
165 END IF;
166
167 END IF;
168
169 /* Added by Manish */
170
171 IF p_line_rec.tax_date IS NULL
172 THEN
173 x_return_status := FND_API.G_RET_STS_ERROR;
174 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_RETURN_ATTRIBUTE');
175 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
176 OE_Order_UTIL.Get_Attribute_Name('TAX_DATE'));
177 OE_MSG_PUB.ADD;
178 END IF;
179
180 -- Tax code is required under following conditions.
181 -- 1. The tax hadnling is required at line level.
182 -- (i.e. Tax_exempt_flag = 'R'-Required.)
183 -- 2. The calculate tax flag on customer transaction type for this line
184 -- type is set to Yes.
185
186 oe_debug_pub.add('calc tax flag 2 : ' || l_line_type_rec.calculate_tax_flag );
187
188 l_line_type_rec := OE_Order_Cache.Load_Line_Type(p_line_rec.line_type_id);
189
190 -- fix for bug 1701388 - commented the following code
191 /*
192
193 -- Fix bug#1098412: check for calculate tax flag ONLY if receivable
194 -- transaction type EXISTS on the line type
195 IF l_line_type_rec.cust_trx_type_id IS NOT NULL THEN
196
197 SELECT tax_calculation_flag
198 INTO l_calculate_tax_flag
199 FROM RA_CUST_TRX_TYPES
200 WHERE CUST_TRX_TYPE_ID = l_line_type_rec.cust_trx_type_id;
201
202 END IF;
203
204 */
205
206 -- fix for bug 1701388. changed l_calculate_tax_flag to
207 -- l_line_type_rec.calculate_tax_flag
208
209 -- eBTax changes
210 /* this validation no longer required
211 IF (l_line_type_rec.calculate_tax_flag = 'Y' OR p_line_rec.tax_exempt_flag = 'R')
212 AND p_line_rec.tax_code IS NULL
213 THEN
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 FND_MESSAGE.SET_NAME('ONT','OE_VAL_TAX_CODE_REQD');
216 OE_MSG_PUB.ADD;
217 END IF;*/
218
219 /* Added by Manish */
220
221
222 -- Service Duration is required on SERVICE lines
223 IF l_item_type_code = 'SERVICE' THEN
224 IF p_line_rec.service_duration IS NULL THEN
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_SERVICE_DURATION');
227 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
228 OE_Order_UTIL.Get_Attribute_Name('SERVICE_DURATION'));
229 OE_MSG_PUB.ADD;
230 END IF;
231 END IF;
232 ------------------------------------------------------------------------
233 --Check over return
234 ------------------------------------------------------------------------
235
236 IF p_line_rec.line_category_code = 'RETURN' AND
237 p_line_rec.reference_line_id is not NULL AND
238 p_line_rec.cancelled_flag <> 'Y'
239 THEN
240 IF (OE_LINE_UTIL.Is_Over_Return(p_line_rec)) THEN
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 FND_MESSAGE.Set_Name('ONT', 'OE_RETURN_INVALID_QUANTITY');
243 OE_MSG_PUB.ADD;
244 END IF;
245 END IF;
246
247 OE_DEBUG_PUB.Add('Entering OE_CNCL_VALIDATE_LINE.Check_Book_Reqd_Attributes',1);
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
252 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
253 THEN
254 OE_MSG_PUB.Add_Exc_Msg
255 ( G_PKG_NAME
256 , 'Check_Book_Reqd_Attributes'
257 );
258 END IF;
259 END Check_Book_Reqd_Attributes;
260
261 FUNCTION Get_Date_Type
262 ( p_header_id IN NUMBER)
263 RETURN VARCHAR2
264 IS
265 l_order_date_type_code VARCHAR2(30) := null;
266 BEGIN
267
268 SELECT order_date_type_code
269 INTO l_order_date_type_code
270 FROM oe_order_headers
271 WHERE header_id = p_header_id;
272
273 RETURN l_order_date_type_code;
274
275
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN
278 RETURN NULL;
279 WHEN OTHERS THEN
280 RETURN null;
281 END Get_Date_Type;
282
283 PROCEDURE Validate_Decimal_Quantity
284 ( p_item_id IN NUMBER
285 , p_item_type_code IN VARCHAR2
286 , p_input_quantity IN NUMBER
287 , p_uom_code IN VARCHAR2
288 , x_return_status IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
289 ) IS
290 l_validated_quantity NUMBER;
291 l_primary_quantity NUMBER;
292 l_qty_return_status VARCHAR2(1);
293 BEGIN
294 OE_DEBUG_PUB.Add('Entering OE_CNCL_VALIDATE_LINE.Validate_Decimal_Quantity',1);
295 -- validate input quantity
296 IF (p_input_quantity is not null AND
297 p_input_quantity <> FND_API.G_MISS_NUM) THEN
298
299 IF trunc(p_input_quantity) <> p_input_quantity THEN
300 oe_debug_pub.add('input quantity is decimal',2);
301
302 IF p_item_type_code is not NULL THEN
303
304 IF p_item_type_code IN ('MODEL', 'OPTION', 'KIT',
305 'CLASS','INCLUDED', 'CONFIG') THEN
306 oe_debug_pub.add('item is config related with decimal qty',2);
307 FND_MESSAGE.SET_NAME('ONT', 'OE_CONFIG_NO_DECIMALS');
308 OE_MSG_PUB.Add;
309 x_return_status := FND_API.G_RET_STS_ERROR;
310
311 ELSE
312
313 oe_debug_pub.add('before calling inv decimals api',2);
314 inv_decimals_pub.validate_quantity(
315 p_item_id => p_item_id,
316 p_organization_id =>
317 OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'),
318 p_input_quantity => p_input_quantity,
319 p_uom_code => p_uom_code,
320 x_output_quantity => l_validated_quantity,
321 x_primary_quantity => l_primary_quantity,
322 x_return_status => l_qty_return_status);
323
324 IF l_qty_return_status = 'W' or l_qty_return_status = 'E' THEN
325 oe_debug_pub.add('inv decimal api return ' || l_qty_return_status,2);
326 oe_debug_pub.add('input_qty ' || p_input_quantity,2);
327 oe_debug_pub.add('l_pri_qty ' || l_primary_quantity,2);
328 oe_debug_pub.add('l_val_qty ' || l_validated_quantity,2);
329 /* bug 2926436 */
330 IF l_qty_return_status = 'W' THEN
331 fnd_message.set_name('ONT', 'OE_DECIMAL_MAX_PRECISION');
332 END IF;
333
334 -- move INV error message to OE message stack
335 oe_msg_pub.add;
336 x_return_status := FND_API.G_RET_STS_ERROR;
337 END IF;
338
339 END IF; -- config related item type
340 END IF; -- item_type_code is null
341 END IF; -- if not decimal qty
342 END IF; -- quantity is null
343
344 OE_DEBUG_PUB.Add('Exiting OE_CNCL_VALIDATE_LINE.Validate_Decimal_Quantity',1);
345 END Validate_Decimal_Quantity;
346
347
348 Procedure Validate_Line_Type(p_line_rec IN oe_order_pub.line_rec_type)
349 IS
350 --
351 -- p_old_line_rec IN oe_order_pub.line_rec_type)
352
353 lorder_type_id NUMBER;
354 lexists VARCHAR2(30);
355 lprocessname VARCHAR2(80);
356 l_new_wf_item_type VARCHAR2(30);
357 --l_old_wf_item_type VARCHAR2(30);
358
359 CURSOR find_LineProcessname IS
360 SELECT 'EXISTS'
361 FROM oe_workflow_assignments a
362 WHERE a.line_type_id = p_line_rec.line_type_id
363 AND nvl(a.item_type_code,nvl(l_new_wf_item_type,'-99')) = nvl(l_new_wf_item_type,'-99')
364 AND a.process_name = lprocessname
365 AND a.order_type_id = lorder_type_id
366 ORDER BY a.item_type_code ;
367
368 CURSOR Get_Order_Type IS
369 SELECT order_type_id
370 FROM oe_order_headers
371 WHERE header_id = p_line_rec.header_id ;
372
373
374
375 Cursor find_config_assign is
376 SELECT 'EXISTS'
377 FROM oe_workflow_assignments a
378 WHERE a.line_type_id = p_line_rec.line_type_id
379 AND a.item_type_code = l_new_wf_item_type
380 AND a.order_type_id = lorder_type_id ;
381
382
383 BEGIN
384
385 OE_DEBUG_PUB.Add('Entering OE_CNCL_VALIDATE_LINE.Validate_Line_Type',1);
386
387 IF p_line_rec.ITEM_TYPE_CODE = OE_GLOBALS.G_ITEM_CONFIG THEN
388
389 l_new_wf_item_type := OE_Order_WF_Util.get_wf_item_type(p_line_rec);
390
391 OPEN Get_Order_Type;
392 FETCH Get_Order_Type
393 INTO lorder_type_id;
394 CLOSE Get_Order_Type;
395
396 OPEN find_config_assign;
397 FETCH find_config_assign
398 INTO lexists;
399 CLOSE find_config_assign;
400
401 IF lexists IS NULL THEN
402 oe_debug_pub.add('No explicit assignment exists',2);
403 FND_MESSAGE.SET_NAME('ONT','OE_EXP_ASSIGN_REQ');
404 OE_MSG_PUB.ADD;
405 RAISE FND_API.G_EXC_ERROR;
406 END IF;
407
408 END IF;
409
410 OE_DEBUG_PUB.Add('Exiting OE_CNCL_VALIDATE_LINE.Validate_Line_Type',1);
411 EXCEPTION
412 WHEN NO_DATA_FOUND THEN
413 FND_MESSAGE.SET_NAME('ONT','OE_FLOW_CNT_CHANGE');
414 OE_MSG_PUB.ADD;
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416 WHEN FND_API.G_EXC_ERROR THEN
417 RAISE FND_API.G_EXC_ERROR;
418
419 WHEN OTHERS THEN
420 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
421 THEN
422 OE_MSG_PUB.Add_Exc_Msg
423 ( G_PKG_NAME ,
424 'Validate_Line_Type'
425 );
426 END IF;
427 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
428
429
430 END Validate_line_type;
431
432
433 FUNCTION Validate_Receiving_Org
434 ( p_inventory_item_id IN NUMBER
435 , p_ship_from_org_id IN NUMBER)
436 RETURN BOOLEAN
437 IS
438 l_validate VARCHAR2(1) := 'Y';
439 l_dummy VARCHAR2(10);
440 BEGIN
441 OE_DEBUG_PUB.Add('Entering OE_CNCL_VALIDATE_LINE.Validate_Receiving_Org',1);
442 SELECT null
443 INTO l_dummy
444 FROM mtl_system_items msi,
445 org_organization_definitions org
446 WHERE msi.inventory_item_id = p_inventory_item_id
447 AND org.organization_id= msi.organization_id
448 AND org.organization_id= p_ship_from_org_id
449 AND org.set_of_books_id= ( SELECT fsp.set_of_books_id
450 FROM financials_system_parameters fsp)
451 AND ROWNUM=1 ;
452
453 OE_DEBUG_PUB.Add('Exiting OE_CNCL_VALIDATE_LINE.Validate_Receiving_Org',1);
454 RETURN TRUE;
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ITEM_WHSE');
458 OE_MSG_PUB.add;
459 RETURN FALSE;
460 WHEN OTHERS THEN
461 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ITEM_WHSE');
462 OE_MSG_PUB.add;
463 RETURN FALSE;
464 END Validate_Receiving_Org;
465
466 FUNCTION Validate_Item_Warehouse
467 ( p_inventory_item_id IN NUMBER
468 , p_ship_from_org_id IN NUMBER
469 , p_item_type_code IN VARCHAR2
470 , p_line_id IN NUMBER
471 , p_top_model_line_id IN NUMBER)
472 RETURN BOOLEAN
473 IS
474 l_validate VARCHAR2(1) := 'Y';
475 l_dummy VARCHAR2(10);
476 BEGIN
477 oe_debug_pub.add('Entering Validate_Item_Warehouse',1);
478 -- The customer_order_enabled_flag for config item
479 -- is set to 'N'
480
481 IF p_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED OR
482 p_item_type_code = OE_GLOBALS.G_ITEM_CONFIG OR
483 p_item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
484 p_item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
485 (p_item_type_code = OE_GLOBALS.G_ITEM_KIT AND
486 nvl(p_top_model_line_id, -1) <> p_line_id)
487 THEN
488 SELECT null
489 INTO l_dummy
490 FROM mtl_system_items msi,
491 org_organization_definitions org
492 WHERE msi.inventory_item_id = p_inventory_item_id
493 AND org.organization_id= msi.organization_id
494 AND org.organization_id= p_ship_from_org_id
495 AND rownum=1;
496 ELSE
497 SELECT null
498 INTO l_dummy
499 FROM mtl_system_items msi,
500 org_organization_definitions org
501 WHERE msi.inventory_item_id = p_inventory_item_id
502 AND org.organization_id= msi.organization_id
503 AND org.organization_id= p_ship_from_org_id
504 AND rownum=1;
505 END IF;
506 oe_debug_pub.add('Exiting Validate_Item_Warehouse',1);
507 RETURN TRUE;
508
509 EXCEPTION
510 WHEN NO_DATA_FOUND THEN
511 oe_debug_pub.add('RR: No data found',1);
512
513 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ITEM_WHSE');
514 OE_MSG_PUB.add;
515 RETURN FALSE;
516
517 WHEN OTHERS THEN
518 oe_debug_pub.add('RR: OTHERS',1);
519 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ITEM_WHSE');
520 OE_MSG_PUB.add;
521 RETURN FALSE;
522
523 END Validate_Item_Warehouse;
524
525 FUNCTION Validate_task
526 ( p_project_id IN NUMBER
527 , p_task_id IN NUMBER)
528 RETURN BOOLEAN
529 IS
530 l_dummy VARCHAR2(10);
531 BEGIN
532
533 oe_debug_pub.add('Entering Validate_Task',1);
534 SELECT 'VALID'
535 INTO l_dummy
536 FROM mtl_task_v
537 WHERE project_id = p_project_id
538 AND task_id = p_task_id;
539
540 oe_debug_pub.add('Exiting Validate_Task',1);
541 RETURN TRUE;
542
543 EXCEPTION
544
545 WHEN OTHERS THEN
546 RETURN FALSE;
547
548 END Validate_task;
549
550 FUNCTION Validate_task_reqd
551 ( p_project_id IN NUMBER
552 ,p_ship_from_org_id IN NUMBER)
553 RETURN BOOLEAN
554 IS
555 l_project_control_level NUMBER;
556 BEGIN
557 oe_debug_pub.add('Entering Validate_task_reqd',1);
558
559 -- If project control level in MTL_PARAMETERS for the warehouse
560 -- is set to 'Task', then project references on the order must
561 -- consist of both Project and Task.
562
563 SELECT NVL(PROJECT_CONTROL_LEVEL,0)
564 INTO l_project_control_level
565 FROM MTL_PARAMETERS
566 WHERE ORGANIZATION_ID = p_ship_from_org_id;
567
568 IF l_project_control_level = 2 -- control level is 'Task'
569 THEN
570 oe_debug_pub.add('Exiting Validate_task_reqd',1);
571 RETURN TRUE;
572 ELSE
573 oe_debug_pub.add('Exiting Validate_task_reqd',1);
574 RETURN FALSE;
575 END IF;
576
577 EXCEPTION
578
579 WHEN OTHERS THEN
580 RETURN FALSE;
581
582 END Validate_task_reqd;
583
584 FUNCTION Validate_Item_Fields
585 ( p_inventory_item_id IN NUMBER
586 , p_ordered_item_id IN NUMBER
587 , p_item_identifier_type IN VARCHAR2
588 , p_ordered_item IN VARCHAR2
589 , p_sold_to_org_id IN NUMBER)
590 RETURN BOOLEAN
591 IS
592 l_dummy VARCHAR2(10);
593 BEGIN
594 oe_debug_pub.add('Entering Validate_Item_Fields',1);
595 oe_debug_pub.add('p_inventory_item_id: '||p_inventory_item_id);
596 oe_debug_pub.add('p_ordered_item_id: '||p_ordered_item_id);
597 oe_debug_pub.add('p_item_identifier_type: '||p_item_identifier_type);
598 oe_debug_pub.add('p_ordered_item: '||p_ordered_item);
599 oe_debug_pub.add('p_sold_to_org_id: '||p_sold_to_org_id);
600 IF nvl(p_item_identifier_type, 'INT') = 'INT' THEN
601 SELECT 'valid'
602 INTO l_dummy
603 FROM mtl_system_items_vl
604 WHERE inventory_item_id = p_inventory_item_id
605 AND organization_id = OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID');
606 ELSIF nvl(p_item_identifier_type, 'INT') = 'CUST' THEN
607 SELECT 'valid'
608 INTO l_dummy
609 FROM mtl_customer_items citems
610 ,mtl_customer_item_xrefs cxref
611 ,mtl_system_items_vl sitems
612 WHERE citems.customer_item_id = cxref.customer_item_id
613 AND cxref.inventory_item_id = sitems.inventory_item_id
614 AND sitems.inventory_item_id = p_inventory_item_id
615 AND sitems.organization_id =
616 OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID')
617 AND citems.customer_item_id = p_ordered_item_id
618 AND citems.customer_id = p_sold_to_org_id
619 AND rownum =1;
620 ELSE
621 IF p_ordered_item_id IS NOT NULL THEN
622 RETURN FALSE;
623 ELSE
624 SELECT 'valid'
625 INTO l_dummy
626 FROM mtl_cross_reference_types types
627 , mtl_cross_references items
628 , mtl_system_items_vl sitems
629 WHERE types.cross_reference_type = items.cross_reference_type
630 AND items.inventory_item_id = sitems.inventory_item_id
631 AND sitems.organization_id =
632 OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID')
633 AND sitems.inventory_item_id = p_inventory_item_id
634 AND items.cross_reference_type = p_item_identifier_type
635 AND items.cross_reference = p_ordered_item;
636 END IF;
637 END IF;
638
639 oe_debug_pub.add('Exiting Validate_Item_Fields',1);
640 RETURN TRUE;
641
642 EXCEPTION
643 WHEN NO_DATA_FOUND THEN
644 oe_debug_pub.add('Validate_Item_Fields: No data found',1);
645 IF nvl(p_item_identifier_type, 'INT') = 'INT' THEN
646 oe_debug_pub.add('Invalid internal item');
647 ELSIF nvl(p_item_identifier_type, 'INT') = 'CUST' THEN
648 oe_debug_pub.add('Invalid Customer Item');
649 ELSE
650 oe_debug_pub.add('Invalid Generic Item');
651 END IF;
652 RETURN FALSE;
653 WHEN OTHERS THEN
654 oe_debug_pub.add('Validate_Item_Fields: When Others',1);
655 RETURN FALSE;
656 END Validate_Item_Fields;
657
658 FUNCTION Validate_Return_Item_Mismatch
659 ( p_reference_line_id IN NUMBER
660 , p_inventory_item_id IN NUMBER)
661 RETURN BOOLEAN
662 IS
663 l_ref_inventory_item_id NUMBER;
664 l_profile VARCHAR2(1);
665 BEGIN
666 oe_debug_pub.add('Entering Validate_Return_Item_Mismatch',1);
667
668 IF (p_reference_line_id IS NULL) THEN
669 RETURN TRUE;
670 END IF;
671
672 -- Check Profile Option to see if allow item mismatch
673 l_profile := FND_PROFILE.value('ONT_RETURN_ITEM_MISMATCH_ACTION');
674
675 IF (l_profile is NULL OR l_profile = 'A') THEN
676 RETURN TRUE;
677 ELSE
678
679 SELECT inventory_item_id
680 INTO l_ref_inventory_item_id
681 FROM oe_order_lines
682 WHERE line_id = p_reference_line_id;
683
684 IF (l_ref_inventory_item_id = p_inventory_item_id) THEN
685 RETURN TRUE;
686 ELSIF (l_profile = 'R') THEN
687 RETURN FALSE;
688 ELSE -- warning
689 FND_MESSAGE.SET_NAME('ONT','OE_RETURN_ITEM_MISMATCH_WARNIN');
690 OE_MSG_PUB.ADD;
691 END IF;
692
693 END IF;
694
695 oe_debug_pub.add('Exiting Validate_Return_Item_Mismatch',1);
696 RETURN TRUE;
697
698 EXCEPTION
699 WHEN NO_DATA_FOUND THEN
700 oe_debug_pub.add('Validate_Return_Item_Mismatch: No data found',1);
701 RETURN FALSE;
702 WHEN OTHERS THEN
703 oe_debug_pub.add('Validate_Return_Item_Mismatch: When Others',1);
704 RETURN FALSE;
705 END Validate_Return_Item_Mismatch;
706
707 FUNCTION Validate_Return_Fulfilled_Line
708 (p_reference_line_id IN NUMBER
709 ) RETURN BOOLEAN
710 IS
711 l_ref_fulfilled_quantity NUMBER;
712 l_ref_shippable_flag VARCHAR2(1);
713 l_ref_shipped_quantity NUMBER;
714 l_ref_inv_iface_status VARCHAR2(30);
715 l_profile VARCHAR2(1);
716 BEGIN
717 oe_debug_pub.add('Entering Validate return fulfilled line',1);
718
719 IF (p_reference_line_id IS NULL) THEN
720 RETURN TRUE;
721 END IF;
722
723 -- Check Profile Option to see if allow item mismatch
724 l_profile := FND_PROFILE.value('ONT_RETURN_FULFILLED_LINE_ACTION');
725
726 IF (l_profile is NULL OR l_profile = 'A') THEN
727 RETURN TRUE;
728
729 /*
730 ** As per the fix for Bug # 1541972, modified the following ELSE
731 ** clause to return a success even if Fulfilled Quantity is null
732 ** and some other conditions are met.
733 */
734 ELSE
735
736
737 SELECT nvl(fulfilled_quantity, 0)
738 , nvl(shippable_flag, 'N')
739 , invoice_interface_status_code
740 , nvl(shipped_quantity, 0)
741 INTO l_ref_fulfilled_quantity
742 , l_ref_shippable_flag
743 , l_ref_inv_iface_status
744 , l_ref_shipped_quantity
745 FROM oe_order_lines
746 WHERE line_id = p_reference_line_id;
747
748 IF (l_ref_shippable_flag = 'N' AND l_ref_inv_iface_status = 'NOT_ELIGIBLE') THEN
749 RETURN TRUE;
750 ELSIF l_ref_inv_iface_status in ('YES', 'RFR-PENDING', 'MANUAL-PENDING') THEN
751 RETURN TRUE;
752 ELSIF l_ref_fulfilled_quantity > 0 THEN
753 RETURN TRUE;
754 ELSIF l_ref_shipped_quantity > 0 THEN
755 RETURN TRUE;
756 ELSIF (l_profile = 'R') THEN
757 RETURN FALSE;
758 ELSE -- warning
759 FND_MESSAGE.SET_NAME('ONT','OE_UNFULFILLED_LINE_WARNING');
760 OE_MSG_PUB.ADD;
761 END IF;
762
763 END IF;
764
765 oe_debug_pub.add('Exiting Validate return fulfilled line',1);
766 RETURN TRUE;
767
768 EXCEPTION
769 WHEN NO_DATA_FOUND THEN
770 oe_debug_pub.add('Validate_Return_Fulfilled_Line: No data found',1);
771 RETURN FALSE;
772 WHEN OTHERS THEN
773 oe_debug_pub.add('Validate_Return_Fulfilled_Line: When Others',1);
774 RETURN FALSE;
775 END Validate_Return_Fulfilled_Line;
776
777 FUNCTION Validate_Return_Item
778 (p_inventory_item_id IN NUMBER,
779 p_ship_from_org_id IN NUMBER)
780 RETURN BOOLEAN
781 IS
782 l_returnable_flag Varchar2(1);
783 BEGIN
784 oe_debug_pub.add('Entering Validate_Return_Item',1);
785
786 SELECT nvl(returnable_flag,'Y')
787 INTO l_returnable_flag
788 FROM mtl_system_items
789 WHERE inventory_item_id = p_inventory_item_id
790 and organization_id = nvl(p_ship_from_org_id,
791 oe_sys_parameters.value_wnps('MASTER_ORGANIZATION_ID'));
792
793 IF l_returnable_flag = 'Y' THEN
794 RETURN TRUE;
795 END IF;
796
797 RETURN FALSE;
798
799 EXCEPTION
800 WHEN NO_DATA_FOUND THEN
801 oe_debug_pub.add('Validate_Return_Item: No data found',1);
802 RETURN FALSE;
803 WHEN OTHERS THEN
804 oe_debug_pub.add('Validate_Return_Item: When Others',1);
805 RETURN FALSE;
806 END Validate_Return_Item;
807
808 FUNCTION Validate_Return_Reference
809 (p_reference_line_id IN NUMBER)
810 RETURN BOOLEAN
811 IS
812 l_booked_flag Varchar2(1);
813 BEGIN
814 oe_debug_pub.add('Enter Validate_Return_Reference',1);
815
816 SELECT nvl(booked_flag,'N')
817 INTO l_booked_flag
818 FROM oe_order_lines
819 WHERE line_id = p_reference_line_id
820 and line_category_code = 'ORDER';
821
822 IF l_booked_flag = 'Y' THEN
823 RETURN TRUE;
824 ELSE
825 fnd_message.set_name('ONT', 'OE_RETURN_UNBOOKED_ORDER');
826 OE_MSG_PUB.Add;
827 END IF;
828
829 oe_debug_pub.add('Exit Validate_Return_Reference',1);
830 RETURN FALSE;
831
832 EXCEPTION
833 WHEN NO_DATA_FOUND THEN
834 oe_debug_pub.add('Validate_Return_Reference: No data found',1);
835 fnd_message.set_name('ONT', 'OE_RETURN_INVALID_SO_LINE');
836 OE_MSG_PUB.Add;
837 RETURN FALSE;
838 WHEN OTHERS THEN
839 oe_debug_pub.add('Validate_Return_Reference: When Others',1);
840 fnd_message.set_name('ONT', 'OE_RETURN_INVALID_SO_LINE');
841 OE_MSG_PUB.Add;
842 RETURN FALSE;
843 END Validate_Return_Reference;
844
845
846
847 FUNCTION Validate_Ship_to_Org
848 ( p_ship_to_org_id IN NUMBER
849 , p_sold_to_org_id IN NUMBER)
850 RETURN BOOLEAN
851 IS
852 l_dummy VARCHAR2(10);
853 lcustomer_relations varchar2(1);
854 --bug 4729536
855 Cursor cur_customer_relations IS
856 SELECT 'VALID'
857 FROM oe_ship_to_orgs_v
858 WHERE site_use_id = p_ship_to_org_id
859 AND status = 'A'
860 AND customer_id = p_sold_to_org_id
861 AND ROWNUM = 1
862
863 UNION ALL
864
865 SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID'
866 FROM oe_ship_to_orgs_v osto
867 WHERE site_use_id = p_ship_to_org_id
868 AND status = 'A'
869 AND EXISTS
870 (
871 SELECT 1 FROM
872 HZ_CUST_ACCT_RELATE hcar
873 WHERE hcar.cust_account_id = osto.customer_id AND
874 hcar.RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
875 AND hcar.ship_to_flag = 'Y'
876 )
877 AND ROWNUM = 1;
878
879 BEGIN
880
881 oe_debug_pub.add('Entering Validate_ship_to_org',1);
882 oe_debug_pub.add('ship_to_org_id :'||to_char(p_ship_to_org_id),2);
883
884 --lcustomer_relations := FND_PROFILE.VALUE('ONT_CUSTOMER_RELATIONSHIPS');
885 lcustomer_relations := OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG');
886
887 IF nvl(lcustomer_relations,'N') = 'N' THEN
888
889 Select 'VALID'
890 Into l_dummy
891 From oe_ship_to_orgs_v
892 Where customer_id = p_sold_to_org_id
893 AND site_use_id = p_ship_to_org_id
894 AND status = 'A';
895
896 oe_debug_pub.add('Exiting Validate_ship_to_org',1);
897 RETURN TRUE;
898 ELSIF lcustomer_relations = 'Y' THEN
899
900 /*Select /*MOAC_SQL_NO_CHANGE 'VALID'
901 Into l_dummy
902 From oe_ship_to_orgs_v
903 WHERE site_use_id = p_ship_to_org_id
904 AND status = 'A' AND
905 customer_id in (
906 Select p_sold_to_org_id from dual
907 union
908 select CUST_ACCOUNT_ID from
909 HZ_CUST_ACCT_RELATE
910 where RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
911 /* added the following condition to fix the bug 2002486
912 and ship_to_flag = 'Y')
913
914 and rownum = 1;*/
915 /* Replaced ra_customer_relationships with HZ Table to fix the bug 1888440 */
916
917 --bug 4729536
918 Open cur_customer_relations;
919 Fetch cur_customer_relations into l_dummy;
920 Close cur_customer_relations;
921 --bug 4729536
922
923 RETURN TRUE;
924
925 /* added the following ELSIF condition to fix the bug 2002486 */
926
927 ELSIF nvl(lcustomer_relations,'N') = 'A' THEN
928 oe_debug_pub.add
929 ('Cr: A',2);
930
931 SELECT 'VALID'
932 INTO l_dummy
933 FROM oe_ship_to_orgs_v
934 WHERE site_use_id = p_ship_to_org_id
935 AND ROWNUM = 1;
936
937 END IF;
938 RETURN TRUE;
939
940
941 EXCEPTION
942
943 WHEN OTHERS THEN
944 RETURN FALSE;
945
946 END Validate_Ship_To_Org;
947
948 FUNCTION Validate_Deliver_To_Org
949 ( p_deliver_to_org_id IN NUMBER
950 , p_sold_to_org_id IN NUMBER)
951 RETURN BOOLEAN
952 IS
953 l_dummy VARCHAR2(10);
954 lcustomer_relations varchar2(1);
955
956 BEGIN
957
958 oe_debug_pub.add('Entering OE_CNCL_VALIDATE_LINE.Validate_Deliver_To_Org',1);
959 oe_debug_pub.add('deliver_to_org_id :'||to_char(p_deliver_to_org_id),2);
960 lcustomer_relations := OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG');
961
962 IF nvl(lcustomer_relations,'N') = 'N' THEN
963 SELECT 'VALID'
964 INTO l_dummy
965 FROM oe_deliver_to_orgs_v
966 WHERE customer_id = p_sold_to_org_id
967 AND site_use_id = p_deliver_to_org_id
968 AND status = 'A';
969
970 oe_debug_pub.add('Exiting OE_CNCL_VALIDATE_LINE.Validate_Deliver_To_Org',1);
971 RETURN TRUE;
972
973 ELSIF lcustomer_relations = 'Y' THEN
974 oe_debug_pub.add('Cr: Yes Line Deliver',2);
975
976 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
977 Into l_dummy
978 FROM HZ_CUST_SITE_USES_ALL SITE,
979 HZ_CUST_ACCT_SITES ACCT_SITE
980 WHERE SITE.SITE_USE_ID = p_deliver_to_org_id
981 AND SITE.SITE_USE_CODE ='DELIVER_TO'
982 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
983 AND ACCT_SITE.CUST_ACCOUNT_ID in (
984 SELECT p_sold_to_org_id FROM DUAL
985 UNION
986 SELECT CUST_ACCOUNT_ID FROM
987 HZ_CUST_ACCT_RELATE_ALL R WHERE
988 R.ORG_ID = ACCT_SITE.ORG_ID
989 AND R.RELATED_CUST_ACCOUNT_ID = p_sold_to_org_id
990 and R.ship_to_flag = 'Y')
991 AND ROWNUM = 1;
992
993 oe_debug_pub.add('Exiting OE_CNCL_VALIDATE_LINE.Validate_Deliver_To_Org',1);
994 RETURN TRUE;
995
996 ELSIF lcustomer_relations = 'A' THEN
997
998 SELECT 'VALID'
999 INTO l_dummy
1000 FROM HZ_CUST_SITE_USES SITE
1001 WHERE SITE.SITE_USE_ID =p_deliver_to_org_id;
1002
1003 oe_debug_pub.add('Exiting OE_CNCL_VALIDATE_LINE.Validate_Deliver_To_Org',1);
1004 RETURN TRUE;
1005
1006
1007 END IF;
1008
1009
1010 oe_debug_pub.add('Exiting OE_CNCL_VALIDATE_LINE.Validate_Deliver_To_Org',1);
1011
1012 EXCEPTION
1013
1014 WHEN OTHERS THEN
1015 RETURN FALSE;
1016
1017 END Validate_Deliver_To_Org;
1018
1019
1020 /*-------------------------------------------------------------
1021 PROCEDURE Validate_Source_Type
1022
1023 We use this procedure to add validations related to source_type
1024 = EXTERNAL.
1025 --------------------------------------------------------------*/
1026 PROCEDURE Validate_Source_Type
1027 ( p_line_rec IN OE_Order_PUB.Line_Rec_Type
1028 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1029 IS
1030 l_purchasing_enabled_flag VARCHAR2(1);
1031 BEGIN
1032
1033 oe_debug_pub.add('entering validate_source_type', 3);
1034
1035 IF OE_GLOBALS.Equal(p_line_rec.source_type_code,
1036 OE_GLOBALS.G_SOURCE_EXTERNAL) THEN
1037
1038 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110508'
1039 THEN
1040 IF p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE OR
1041 p_line_rec.ship_model_complete_flag = 'Y'
1042 THEN
1043 oe_debug_pub.add('servie / part of smc model', 4);
1044 FND_MESSAGE.SET_NAME('ONT', 'OE_DS_NOT_VALID_ITEM');
1045 FND_MESSAGE.SET_TOKEN('ITEM', p_line_rec.ordered_item);
1046 OE_MSG_PUB.Add;
1047 x_return_status := FND_API.G_RET_STS_ERROR;
1048 RETURN;
1049 ELSE
1050 oe_debug_pub.add('validate line: pack H new logic DS', 1);
1051 END IF;
1052 ELSE
1053 IF (p_line_rec.item_type_code <> OE_GLOBALS.G_ITEM_STANDARD) THEN
1054 oe_debug_pub.add('Cannot dropship non-standard item',2);
1055 FND_MESSAGE.SET_NAME('ONT', 'OE_DS_NOT_ALLOWED');
1056 OE_MSG_PUB.Add;
1057 x_return_status := FND_API.G_RET_STS_ERROR;
1058 RETURN;
1059 END IF;
1060 END IF;
1061
1062
1063 IF p_line_rec.ship_set_id is not null OR
1064 p_line_rec.arrival_set_id is not null THEN
1065
1066 oe_debug_pub.add('Cannot insert external line to set',2);
1067 FND_MESSAGE.SET_NAME('ONT', 'OE_DS_SET_INS_FAILED');
1068 OE_MSG_PUB.Add;
1069 x_return_status := FND_API.G_RET_STS_ERROR;
1070 RETURN;
1071 END IF;
1072
1073
1074 IF p_line_rec.shippable_flag = 'Y' OR
1075 (p_line_rec.ato_line_id = p_line_rec.line_id AND
1076 p_line_rec.item_type_code in ('MODEL', 'CLASS')) THEN
1077
1078 SELECT purchasing_enabled_flag
1079 INTO l_purchasing_enabled_flag
1080 FROM mtl_system_items msi,
1081 org_organization_definitions org
1082 WHERE msi.inventory_item_id = p_line_rec.inventory_item_id
1083 AND org.organization_id= msi.organization_id
1084 AND sysdate <= nvl( org.disable_date, sysdate)
1085 AND org.organization_id = nvl(p_line_rec.ship_from_org_id,
1086 OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID'))
1087 AND org.set_of_books_id= ( SELECT fsp.set_of_books_id
1088 FROM financials_system_parameters fsp);
1089
1090 IF l_purchasing_enabled_flag = 'N' THEN
1091 FND_MESSAGE.SET_NAME('ONT', 'OE_DS_NOT_ENABLED');
1092 FND_MESSAGE.SET_TOKEN('ITEM', nvl(p_line_rec.ship_from_org_id,
1093 OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID')));
1094 FND_MESSAGE.SET_TOKEN('ORG', p_line_rec.ordered_item);
1095 OE_MSG_PUB.Add;
1096 x_return_status := FND_API.G_RET_STS_ERROR;
1097 END IF;
1098
1099 END IF;
1100
1101 END IF;
1102
1103 oe_debug_pub.add('leaving validate_source_type', 3);
1104
1105 EXCEPTION
1106 WHEN OTHERS THEN
1107 oe_debug_pub.add('error in Validate_Source_Type');
1108 RAISE;
1109 END Validate_Source_Type;
1110
1111
1112 -- PUBLIC PROCEDURES
1113
1114 -- Procedure Entity
1115
1116 PROCEDURE Entity
1117 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1118 , p_line_rec IN OE_Order_PUB.Line_Rec_Type
1119 )
1120 IS
1121 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1122 l_valid_line_number VARCHAR2(1) := 'Y';
1123 l_dummy VARCHAR2(10);
1124 l_uom VARCHAR2(3);
1125 l_uom_count NUMBER;
1126 l_agreement_name VARCHAR2(240);
1127 l_item_type_code VARCHAR2(30);
1128 l_sold_to_org NUMBER;
1129 l_price_list_id NUMBER;
1130 l_price_list_name VARCHAR2(240);
1131 l_option_count NUMBER;
1132 l_is_ota_line BOOLEAN;
1133 l_order_quantity_uom VARCHAR2(3);
1134 lcustomer_relations varchar2(1) := OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG');
1135 l_list_type_code VARCHAR2(30);
1136
1137 l_ret_status BOOLEAN:=TRUE; -- 8993157
1138 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level; -- INVCONV
1139 l_tracking_quantity_ind VARCHAR2(30); -- INVCONV
1140 l_secondary_default_ind VARCHAR2(30); -- INVCONV
1141 l_secondary_uom_code varchar2(3) := NULL; -- INVCONV
1142 l_buffer VARCHAR2(2000); -- INVCONV
1143
1144 CURSOR c_item ( discrete_org_id IN NUMBER -- INVCONV
1145 , discrete_item_id IN NUMBER) IS
1146 SELECT tracking_quantity_ind,
1147 secondary_uom_code,
1148 secondary_default_ind
1149 FROM mtl_system_items
1150 WHERE organization_id = discrete_org_id
1151 AND inventory_item_id = discrete_item_id;
1152
1153 /*OPM 02/JUN/00 BEGIN
1154 ====================*/
1155 --l_item_rec OE_ORDER_CACHE.item_rec_type; -- OPM INVCONV
1156 --l_OPM_UOM VARCHAR2(4); --OPM 06/22
1157 --l_status VARCHAR2(1); --OPM 06/22
1158 l_msg_count NUMBER;
1159 l_msg_data VARCHAR2(2000);
1160 l_return NUMBER := 0;
1161 /*OPM 02/JUN/00 END
1162 ==================*/
1163
1164 -- Added for Enhanced Project Validation
1165 result VARCHAR2(1) := PJM_PROJECT.G_VALIDATE_SUCCESS;
1166 errcode VARCHAR2 (80);
1167 l_order_date_type_code VARCHAR2(10);
1168 p_date DATE;
1169
1170 -- AR System Parameters
1171 l_AR_Sys_Param_Rec AR_SYSTEM_PARAMETERS_ALL%ROWTYPE;
1172 l_sob_id NUMBER;
1173
1174 -- eBTax Changes
1175 l_ship_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
1176 l_ship_to_party_id hz_cust_accounts.party_id%type;
1177 l_ship_to_party_site_id hz_party_sites.party_site_id%type;
1178 l_bill_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
1179 l_bill_to_party_id hz_cust_accounts.party_id%type;
1180 l_bill_to_party_site_id hz_party_sites.party_site_id%type;
1181 l_org_id NUMBER;
1182 -- l_legal_entity_id NUMBER;
1183
1184 cursor partyinfo(p_site_org_id HZ_CUST_SITE_USES_ALL.SITE_USE_ID%type) is
1185 SELECT cust_acct.cust_account_id,
1186 cust_Acct.party_id,
1187 acct_site.party_site_id,
1188 site_use.org_id
1189 FROM
1190 HZ_CUST_SITE_USES_ALL site_use,
1191 HZ_CUST_ACCT_SITES_ALL acct_site,
1192 HZ_CUST_ACCOUNTS_ALL cust_Acct
1193 WHERE site_use.site_use_id = p_site_org_id
1194 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
1195 and acct_site.cust_account_id = cust_acct.cust_account_id;
1196
1197 --bug 4729536
1198 CURSOR cur_customer_relations IS
1199
1200 Select /*MOAC_SQL_NO_CHANGE*/ 'VALID' a
1201 From oe_invoice_to_orgs_v
1202 WHERE site_use_id = p_line_rec.invoice_to_org_id
1203 AND status = 'A'
1204 AND customer_id = p_line_rec.sold_to_org_id
1205 and rownum =1
1206
1207 UNION ALL
1208
1209 SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID' a
1210 FROM oe_invoice_to_orgs_v oito
1211 WHERE oito.site_use_id = p_line_rec.invoice_to_org_id
1212 AND oito.status = 'A' AND
1213 EXISTS
1214 (
1215 select 1 from HZ_CUST_ACCT_RELATE hcar
1216 where hcar.CUST_ACCOUNT_ID = oito.customer_id
1217 and hcar.RELATED_CUST_ACCOUNT_ID = p_line_rec.sold_to_org_id
1218 /* added the following condition to fix the bug 2002486 */
1219 and hcar.bill_to_flag = 'Y'
1220 )
1221 and rownum = 1 ;
1222
1223 BEGIN
1224
1225 oe_debug_pub.add('Enter OE_CNCL_VALIDATE_LINE.ENTITY',1);
1226
1227
1228 -----------------------------------------------------------
1229 -- Check required attributes.
1230 -----------------------------------------------------------
1231
1232 oe_debug_pub.add('1 '||l_return_status, 1);
1233
1234 oe_debug_pub.add('2 '||l_return_status, 1);
1235 IF p_line_rec.inventory_item_id IS NULL
1236 THEN
1237
1238 l_return_status := FND_API.G_RET_STS_ERROR;
1239
1240 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1241 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1242 OE_Order_UTIL.Get_Attribute_Name('INVENTORY_ITEM_ID'));
1243 OE_MSG_PUB.Add;
1244
1245 END IF;
1246
1247 oe_debug_pub.add('3 '||l_return_status, 1);
1248 IF p_line_rec.line_type_id IS NULL
1249 THEN
1250
1251 l_return_status := FND_API.G_RET_STS_ERROR;
1252
1253 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1254 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1255 OE_Order_UTIL.Get_Attribute_Name('LINE_TYPE_ID'));
1256 OE_MSG_PUB.Add;
1257
1258 ELSIF p_line_rec.line_type_id IS NOT NULL THEN
1259 Validate_line_type(p_line_rec => p_line_rec);
1260
1261 END IF;
1262
1263 -- Return Error if a required attribute is missing.
1264
1265 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1266
1267 RAISE FND_API.G_EXC_ERROR;
1268
1269 END IF;
1270
1271
1272 --------------------------------------------------------------
1273 -- Check conditionally required attributes here.
1274 --------------------------------------------------------------
1275
1276 -- For return lines, Return_Reason_Code is required
1277 oe_debug_pub.add('5 '||l_return_status, 1);
1278 IF p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE
1279 and p_line_rec.return_reason_code is NULL
1280 THEN
1281 l_return_status := FND_API.G_RET_STS_ERROR;
1282
1283 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1284 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1285 OE_Order_UTIL.Get_Attribute_Name('RETURN_REASON_CODE'));
1286 OE_MSG_PUB.Add;
1287
1288 END IF;
1289
1290
1291 oe_debug_pub.add('6 '||l_return_status, 1);
1292
1293 -- subinventory
1294 oe_debug_pub.add('Entity: subinventory - ' || p_line_rec.subinventory);
1295
1296 IF p_line_rec.subinventory is not null THEN
1297 IF p_line_rec.source_type_code = 'INTERNAL' OR
1298 p_line_rec.source_type_code is null THEN
1299 oe_debug_pub.add('Entity Validateion: subinventory', 1);
1300 IF p_line_rec.ship_from_org_id is null THEN
1301 l_return_status := FND_API.G_RET_STS_ERROR;
1302 fnd_message.set_name('ONT', 'OE_ATTRIBUTE_REQUIRED');
1303 fnd_message.set_token('ATTRIBUTE',OE_Order_UTIL.Get_Attribute_Name('SHIP_FROM_ORG_ID'));
1304 OE_MSG_PUB.Add;
1305 END IF;
1306 ELSE -- external
1307 l_return_status := FND_API.G_RET_STS_ERROR;
1308 fnd_message.set_name('ONT', 'OE_SUBINV_EXTERNAL');
1309 OE_MSG_PUB.Add;
1310 END IF;
1311 END IF;
1312
1313 -- end subinventory
1314
1315 oe_debug_pub.add('Entity: done subinv validation', 1);
1316
1317 -- If line is booked, then check for the attributes required on booked lines
1318 -- Fix bug 1277092: this check not required for fully cancelled lines
1319 IF p_line_rec.booked_flag = 'Y'
1320 AND p_line_rec.cancelled_flag <> 'Y' THEN
1321 Check_Book_Reqd_Attributes( p_line_rec => p_line_rec
1322 , x_return_status => l_return_status);
1323
1324 END IF;
1325
1326 -- Return Error if a conditionally required attribute is missing.
1327
1328 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1329
1330 RAISE FND_API.G_EXC_ERROR;
1331
1332 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1333
1334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1335
1336 END IF;
1337
1338
1339 -- OPM 02/JUN/00 START
1340 -- For an item with tracking in Primary and secondary , check qty1/2 both present and sync'd -- INVCONV
1341 -- =====================================================================
1342 oe_debug_pub.add('Primary and Secondary X-VAL start', 1);
1343
1344 OPEN c_item( p_line_rec.ship_from_org_id,
1345 p_line_rec.inventory_item_id
1346 );
1347 FETCH c_item
1348 INTO l_tracking_quantity_ind,
1349 l_secondary_uom_code ,
1350 l_secondary_default_ind
1351 ;
1352
1353
1354 IF c_item%NOTFOUND THEN
1355 l_tracking_quantity_ind := 'P';
1356 l_secondary_uom_code := NULL;
1357 l_secondary_default_ind := null;
1358
1359 END IF;
1360
1361 Close c_item;
1362
1363
1364
1365 /*IF OE_Line_Util.Process_Characteristics
1366 (p_line_rec.inventory_item_id
1367 ,p_line_rec.ship_from_org_id
1368 ,l_item_rec)
1369 THEN
1370 */
1371 IF l_tracking_quantity_ind = 'PS' then
1372 -- IF l_item_rec.dualum_ind in (1,2,3) THEN INVCONV
1373 oe_debug_pub.add('Primary and Secondary X-VAL - tracking_quantity_ind PS', 2);
1374
1375 IF (p_line_rec.ordered_quantity <> FND_API.G_MISS_NUM OR
1376 p_line_rec.ordered_quantity IS NOT NULL) AND
1377 (p_line_rec.ordered_quantity2 = FND_API.G_MISS_NUM OR
1378 p_line_rec.ordered_quantity2 IS NULL) THEN
1379
1380 oe_debug_pub.add('Primary and Secondary X-VAL qty 1 not empty', 2);
1381
1382 l_return_status := FND_API.G_RET_STS_ERROR;
1383 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1384 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Ordered_Quantity2');
1385 OE_MSG_PUB.Add;
1386
1387 ELSIF (p_line_rec.ordered_quantity2 <> FND_API.G_MISS_NUM OR
1388 p_line_rec.ordered_quantity2 IS NOT NULL) AND
1389 (p_line_rec.ordered_quantity IS NULL) THEN
1390
1391 l_return_status := FND_API.G_RET_STS_ERROR;
1392 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1393 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Ordered_Quantity');
1394 OE_MSG_PUB.Add;
1395 END IF; -- IF (p_line_rec.ordered_quantity <> FND_API.G_MISS_NUM OR
1396 END IF; -- IF l_item_rec.tracking_quantity_ind = 'PS' IF l_item_rec.dualum_ind in (1,2,3) THEN
1397
1398 /* If qty1/qty2 both populated, check tolerances
1399 ================================================*/
1400 oe_debug_pub.add('Primary and Secondary X-VAL - tolerance check', 2);
1401
1402 IF l_secondary_default_ind in ('N','D') then -- INVCONV
1403 -- IF l_item_rec.dualum_ind in (2,3) THEN
1404 IF (p_line_rec.ordered_quantity <> FND_API.G_MISS_NUM AND
1405 p_line_rec.ordered_quantity IS NOT NULL) AND
1406 (p_line_rec.ordered_quantity2 <> FND_API.G_MISS_NUM AND
1407 p_line_rec.ordered_quantity2 IS NOT NULL) THEN
1408
1409 -- check the deviation and error out
1410 l_return := INV_CONVERT.Within_Deviation -- INVCONV
1411 ( p_organization_id =>
1412 p_line_rec.ship_from_org_id
1413 , p_inventory_item_id =>
1414 p_line_rec.inventory_item_id
1415 , p_precision => 5
1416 , p_quantity => p_line_rec.ordered_quantity
1417 , p_uom_code1 => p_line_rec.order_quantity_uom -- INVCONV
1418 , p_quantity2 => p_line_rec.ordered_quantity2
1419 , p_uom_code2 => l_secondary_uom_code );
1420
1421 IF l_return = 0
1422 then
1423 IF l_debug_level > 0 THEN
1424 oe_debug_pub.add('Primary and Secondary X-VAL - tolerance error 1' ,1);
1425 END IF;
1426
1427 l_buffer := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, -- INVCONV
1428 p_encoded => 'F');
1429 oe_msg_pub.add_text(p_message_text => l_buffer);
1430 IF l_debug_level > 0 THEN
1431 oe_debug_pub.add(l_buffer,1);
1432 END IF;
1433 l_return_status := FND_API.G_RET_STS_ERROR;
1434
1435 else
1436 IF l_debug_level > 0 THEN
1437 oe_debug_pub.add('Primary and Secondary X-VAL - No tolerance error ',1);
1438 END IF;
1439 END IF; -- IF l_return = 0
1440
1441
1442 -- OPM BEGIN 06/22
1443 /* Get the OPM equivalent code for order_quantity_uom
1444 ===================================================== INVCONV
1445 GMI_Reservation_Util.Get_OPMUOM_from_AppsUOM
1446 (p_Apps_UOM => p_line_rec.order_quantity_uom
1447 ,x_OPM_UOM => l_OPM_UOM
1448 ,x_return_status => l_status
1449 ,x_msg_count => l_msg_count
1450 ,x_msg_data => l_msg_data);
1451
1452 l_return := GMICVAL.dev_validation(l_item_rec.opm_item_id
1453 ,0
1454 ,p_line_rec.ordered_quantity
1455 ,l_OPM_UOM
1456 ,p_line_rec.ordered_quantity2
1457 ,l_item_rec.opm_item_um2
1458 ,0);
1459 -- OPM END 06/22
1460 IF(l_return = -68) THEN
1461 l_return_status := FND_API.G_RET_STS_ERROR;
1462 FND_MESSAGE.set_name('GMI', 'IC_DEVIATION_HI_ERR');
1463 OE_MSG_PUB.Add;
1464 ELSIF (l_return = -69) THEN
1465 l_return_status := FND_API.G_RET_STS_ERROR;
1466 FND_MESSAGE.set_name('GMI', 'IC_DEVIATION_LO_ERR');
1467 OE_MSG_PUB.Add;
1468 END IF; */
1469
1470
1471 END IF; -- IF (p_line_rec.ordered_quantity <> FND_API.G_MISS_NUM AND
1472
1473
1474 END IF; -- IF l_item_rec.tracking_quantity_ind = 'PS' IF l_item_rec.dualum_ind in (2,3) THEN INVCONV
1475
1476 -- END IF; -- IF OE_Line_Util.Process_Characteristics INVCONV
1477
1478 -- Return Error if a required quantity validation fails
1479 -- ====================================================
1480 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1481 RAISE FND_API.G_EXC_ERROR;
1482 END IF;
1483 -- OPM 02/JUN/00 END
1484 -- ===================
1485
1486
1487 ---------------------------------------------------------------------
1488 -- Validate attribute dependencies here.
1489 ---------------------------------------------------------------------
1490
1491 -- Validate if the warehouse, item combination is valid
1492 IF p_line_rec.inventory_item_id is not null AND
1493 p_line_rec.ship_from_org_id is not null AND
1494 p_line_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
1495 p_line_rec.ship_from_org_id <> FND_API.G_MISS_NUM THEN
1496
1497 /* IF p_line_rec.inventory_item_id <>
1498 nvl(p_old_line_rec.inventory_item_id,0) OR
1499 p_line_rec.ship_from_org_id <> nvl(p_old_line_rec.ship_from_org_id,0)
1500 THEN */
1501
1502
1503
1504 IF p_line_rec.source_type_code = OE_GLOBALS.G_SOURCE_INTERNAL
1505 or p_line_rec.source_type_code is null
1506 THEN
1507 oe_debug_pub.add('Source Type is Internal',1);
1508
1509 IF p_line_rec.line_category_code = 'RETURN' THEN
1510 l_item_type_code := OE_LINE_UTIL.Get_Return_item_type_code(
1511 p_line_rec);
1512 ELSE
1513 l_item_type_code := p_line_rec.item_type_code;
1514 END IF;
1515
1516
1517 IF NOT Validate_Item_Warehouse
1518 (p_line_rec.inventory_item_id,
1519 p_line_rec.ship_from_org_id,
1520 l_item_type_code,
1521 p_line_rec.line_id,
1522 p_line_rec.top_model_line_id)
1523 THEN
1524 l_return_status := FND_API.G_RET_STS_ERROR;
1525 END IF;
1526 ELSE
1527 oe_debug_pub.add('Source Type is External',1);
1528 IF NOT Validate_Receiving_Org
1529 (p_line_rec.inventory_item_id,
1530 p_line_rec.ship_from_org_id)
1531 THEN
1532 l_return_status := FND_API.G_RET_STS_ERROR;
1533 END IF;
1534 END IF;
1535 --END IF;
1536 END IF;
1537
1538 -- subinventory
1539
1540 IF p_line_rec.ship_from_org_id is not null AND
1541 p_line_rec.subinventory is not null AND
1542 p_line_rec.ship_From_org_id <> FND_API.G_MISS_NUM AND
1543 p_line_rec.subinventory <> FND_API.G_MISS_CHAR THEN
1544
1545 /* IF p_line_rec.ship_from_org_id <> nvl(p_old_line_rec.ship_from_org_id, 0) OR
1546 p_line_rec.subinventory <> nvl(p_old_line_rec.subinventory, '0') THEN
1547 */
1548 BEGIN
1549 SELECT 'VALID'
1550 INTO l_dummy
1551 FROM MTL_SUBINVENTORIES_TRK_VAL_V
1552 WHERE organization_id = p_line_rec.ship_from_org_id
1553 AND secondary_inventory_name = p_line_rec.subinventory;
1554 EXCEPTION
1555 WHEN OTHERS THEN
1556 -- not a valid subinventory, show show a msg
1557 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1558 END;
1559 --END IF;
1560 END IF;
1561
1562 -- end subinventory
1563
1564 -- start decimal qty validation
1565 IF p_line_rec.inventory_item_id is not null THEN
1566
1567 oe_debug_pub.add('decimal1',2);
1568 IF p_line_rec.order_quantity_uom is not null THEN
1569
1570 -- validate ordered quantity
1571 Validate_Decimal_Quantity
1572 (p_item_id => p_line_rec.inventory_item_id
1573 ,p_item_type_code => p_line_rec.item_type_code
1574 ,p_input_quantity => p_line_rec.ordered_quantity
1575 ,p_uom_code => p_line_rec.order_quantity_uom
1576 ,x_return_status => l_return_status
1577 );
1578
1579 -- validate invoiced_quantity
1580 Validate_Decimal_Quantity
1581 (p_item_id => p_line_rec.inventory_item_id
1582 ,p_item_type_code => p_line_rec.item_type_code
1583 ,p_input_quantity => p_line_rec.invoiced_quantity
1584 ,p_uom_code => p_line_rec.order_quantity_uom
1585 ,x_return_status => l_return_status
1586 );
1587
1588 -- cancelled quantity
1589 Validate_Decimal_Quantity
1590 (p_item_id => p_line_rec.inventory_item_id
1591 ,p_item_type_code => p_line_rec.item_type_code
1592 ,p_input_quantity => p_line_rec.cancelled_quantity
1593 ,p_uom_code => p_line_rec.order_quantity_uom
1594 ,x_return_status => l_return_status
1595 );
1596
1597 -- auto_selected quantity
1598 Validate_Decimal_Quantity
1599 (p_item_id => p_line_rec.inventory_item_id
1600 ,p_item_type_code => p_line_rec.item_type_code
1601 ,p_input_quantity => p_line_rec.auto_selected_quantity
1602 ,p_uom_code => p_line_rec.order_quantity_uom
1603 ,x_return_status => l_return_status
1604 );
1605
1606 -- reserved quantity
1607 Validate_Decimal_Quantity
1608 (p_item_id => p_line_rec.inventory_item_id
1609 ,p_item_type_code => p_line_rec.item_type_code
1610 ,p_input_quantity => p_line_rec.reserved_quantity
1611 ,p_uom_code => p_line_rec.order_quantity_uom
1612 ,x_return_status => l_return_status
1613 );
1614
1615 -- fulfilled quantity, double check with Shashi
1616 Validate_Decimal_Quantity
1617 (p_item_id => p_line_rec.inventory_item_id
1618 ,p_item_type_code => p_line_rec.item_type_code
1619 ,p_input_quantity => p_line_rec.fulfilled_quantity
1620 ,p_uom_code => p_line_rec.order_quantity_uom
1621 ,x_return_status => l_return_status
1622 );
1623
1624 END IF; -- order quantity uom not null
1625
1626 -- validate pricing quantity starts here
1627 -- bug 1391668, don't need to validate pricing quantity
1628 /*
1629 IF (p_line_rec.pricing_quantity_uom is not null AND
1630 p_line_rec.pricing_quantity is not null) THEN
1631
1632 Validate_Decimal_Quantity
1633 (p_item_id => p_line_rec.inventory_item_id
1634 ,p_item_type_code => p_line_rec.item_type_code
1635 ,p_input_quantity => p_line_rec.pricing_quantity
1636 ,p_uom_code => p_line_rec.pricing_quantity_uom
1637 ,x_return_status => l_return_status
1638 );
1639
1640 END IF; -- quantity or uom is null
1641 */
1642 END IF; -- inventory_item_id is null
1643 -- end decimal quantity validation
1644
1645
1646 -- Error if reserved quantity > ordered quantity
1647 /* IF NOT OE_GLOBALS.Equal(p_line_rec.reserved_quantity,p_old_line_rec.reserved_quantity)
1648 THEN
1649
1650 IF (p_line_rec.reserved_quantity > p_line_rec.ordered_quantity) THEN
1651 fnd_message.set_name('ONT','OE_SCH_RES_MORE_ORD_QTY');
1652 OE_MSG_PUB.Add;
1653 l_return_status := FND_API.G_RET_STS_ERROR;
1654 END IF;
1655
1656 END IF;*/
1657
1658
1659 -- Check to see if the user has changed both the Schedule Ship Date
1660 -- and Schedule Arrival Date. This is not allowed. The user can change
1661 -- either one, but not both.
1662
1663 /*
1664 IF (NOT OE_GLOBALS.Equal(p_line_rec.schedule_ship_date,
1665 p_old_line_rec.schedule_ship_date)) AND
1666 (NOT OE_GLOBALS.Equal(p_line_rec.schedule_arrival_date,
1667 p_old_line_rec.schedule_arrival_date)) AND
1668 (OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING = 'Y') THEN
1669
1670 -- Config item is created and passed by the CTO team. So this is
1671 -- is the only item type, which when gets created, already has
1672 -- Schedule_Ship_Date and schedule_Arrival_date. We should not
1673 -- error out for this item.
1674
1675 IF p_line_rec.item_type_code <> OE_GLOBALS.G_ITEM_CONFIG THEN
1676 FND_MESSAGE.SET_NAME('ONT','OE_SCH_INVALID_CHANGE');
1677 OE_MSG_PUB.Add;
1678 l_return_status := FND_API.G_RET_STS_ERROR;
1679 END IF;
1680
1681 END IF;
1682 */
1683
1684 Validate_Source_Type
1685 ( p_line_rec => p_line_rec
1686 ,x_return_status => l_return_status);
1687
1688 -- PJM validation.
1689
1690 IF PJM_UNIT_EFF.ENABLED = 'Y' THEN
1691
1692 IF (p_line_rec.project_id IS NOT NULL
1693 AND p_line_rec.ship_from_org_id IS NULL) THEN
1694
1695 l_return_status := FND_API.G_RET_STS_ERROR;
1696 FND_MESSAGE.SET_NAME('ONT', 'OE_SHIP_FROM_REQD');
1697 OE_MSG_PUB.add;
1698 ELSIF (p_line_rec.task_id IS NOT NULL
1699 AND p_line_rec.project_id IS NULL) THEN
1700
1701 l_return_status := FND_API.G_RET_STS_ERROR;
1702 FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_PROJECT_REQD');
1703 OE_MSG_PUB.add;
1704
1705 END IF;
1706
1707 -- Added Code for Enhanced Project Validation and Controls.
1708
1709 l_order_date_type_code := NVL(OE_SCHEDULE_UTIL.Get_Date_Type(
1710 p_line_rec.header_id), 'SHIP');
1711
1712
1713 IF l_order_date_type_code = 'SHIP' THEN
1714 p_date := NVL(p_line_rec.schedule_ship_date,
1715 p_line_rec.request_date);
1716 ELSIF l_order_date_type_code = 'ARRIVAL' THEN
1717 p_date := NVL(p_line_rec.schedule_arrival_date,
1718 p_line_rec.request_date);
1719 END IF;
1720
1721 OE_DEBUG_PUB.Add('Before calling Validate Proj References',1);
1722
1723 result := PJM_PROJECT.VALIDATE_PROJ_REFERENCES
1724 ( X_inventory_org_id => p_line_rec.ship_from_org_id
1725 , X_operating_unit => p_line_rec.org_id
1726 , X_project_id => p_line_rec.project_id
1727 , X_task_id => p_line_rec.task_id
1728 , X_date1 => p_date
1729 , X_date2 => NULL
1730 , X_calling_function =>'OEXVCLNB'
1731 , X_error_code => errcode
1732 );
1733 OE_DEBUG_PUB.Add('Validate Proj References Error:'||
1734 errcode,1);
1735 OE_DEBUG_PUB.Add('Validate Proj References Result:'||
1736 result,1);
1737
1738 IF result <> PJM_PROJECT.G_VALIDATE_SUCCESS THEN
1739 OE_MSG_PUB.Transfer_Msg_Stack;
1740 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
1741 FOR I in 1..l_msg_count loop
1742 l_msg_data := OE_MSG_PUB.Get(I,'F');
1743 OE_DEBUG_PUB.add(l_msg_data,1);
1744 END LOOP;
1745 END IF;
1746
1747 IF result = PJM_PROJECT.G_VALIDATE_FAILURE THEN
1748 l_return_status := FND_API.G_RET_STS_ERROR;
1749 OE_DEBUG_PUB.Add('PJM Validation API returned with Errors',1);
1750 ELSIF result = PJM_PROJECT.G_VALIDATE_WARNING THEN
1751 OE_DEBUG_PUB.Add('PJM Validation API returned with Warnings',1);
1752 END IF;
1753
1754
1755
1756 /* -- Commented Code for Enhanced Project Validation and Controls
1757
1758 ELSIF ( p_line_rec.ship_from_org_id IS NOT NULL AND
1759 p_line_rec.project_id IS NOT NULL) THEN
1760
1761 -- Validate project/warehouse combination.
1762 IF pjm_project.val_proj_idtonum
1763 (p_line_rec.project_id,
1764 p_line_rec.ship_from_org_id) IS NULL THEN
1765
1766 l_return_status := FND_API.G_RET_STS_ERROR;
1767 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SHIP_FROM_PROJ');
1768 OE_MSG_PUB.add;
1769 END IF;
1770
1771 END IF;
1772
1773 IF (p_line_rec.task_id IS NOT NULL
1774 AND p_line_rec.project_id IS NULL) THEN
1775
1776 l_return_status := FND_API.G_RET_STS_ERROR;
1777 FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_PROJECT_REQD');
1778 OE_MSG_PUB.add;
1779
1780 ELSIF (p_line_rec.task_id is NOT NULL
1781 AND p_line_rec.project_id IS NOT NULL) THEN
1782
1783 IF NOT Validate_task(
1784 p_line_rec.project_id,
1785 p_line_rec.task_id) THEN
1786
1787 l_return_status := FND_API.G_RET_STS_ERROR;
1788 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'TASK_ID');
1789 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1790 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1791 OE_Order_Util.Get_Attribute_Name('task_id'));
1792 OE_MSG_PUB.Add;
1793 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1794
1795 END IF;
1796
1797 ELSIF (p_line_rec.task_id is NULL
1798 AND p_line_rec.project_id IS NOT NULL) THEN
1799
1800 IF Validate_task_reqd(
1801 p_line_rec.project_id,
1802 p_line_rec.ship_from_org_id) THEN
1803
1804 l_return_status := FND_API.G_RET_STS_ERROR;
1805 FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_TASK_REQD');
1806 OE_MSG_PUB.ADD;
1807
1808 END IF;
1809
1810 END IF;
1811 */
1812
1813 -- End Item Unit number logic.
1814
1815 oe_debug_pub.add('10 '||l_return_status, 1);
1816 IF (p_line_rec.inventory_item_id IS NOT NULL) AND
1817 (p_line_rec.ship_from_org_id IS NOT NULL) AND
1818 (p_line_rec.end_item_unit_number IS NULL) THEN
1819
1820 IF PJM_UNIT_EFF.UNIT_EFFECTIVE_ITEM
1821 (p_line_rec.inventory_item_id,p_line_rec.ship_from_org_id) = 'Y'
1822 THEN
1823
1824 l_return_status := FND_API.G_RET_STS_ERROR;
1825 fnd_message.set_name('ONT', 'OE_UEFF_NUMBER_REQD');
1826 OE_MSG_PUB.add;
1827 END IF;
1828
1829 END IF;
1830 ELSE -- When project manufacturing is not enabled at the site.
1831
1832 IF (p_line_rec.project_id IS NOT NULL OR
1833 p_line_rec.task_id IS NOT NULL OR
1834 p_line_rec.end_item_unit_number IS NOT NULL) THEN
1835 l_return_status := FND_API.G_RET_STS_ERROR;
1836 fnd_message.set_name('ONT', 'OE_PJM_NOT_INSTALLED');
1837 OE_MSG_PUB.add;
1838
1839 END IF;
1840
1841
1842 END IF; --End if PJM_UNIT_EFF.ENABLED
1843
1844 -- Donot allow to update project and task when a option/class is under ATO
1845 -- Model.
1846
1847 oe_debug_pub.add('11 '||l_return_status, 1);
1848
1849 /*IF p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
1850
1851 IF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
1852 p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS)
1853 --AND p_line_rec.line_id <> p_line_rec.ato_line_id
1854 THEN
1855
1856 FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_PROJ_UPD');
1857 OE_MSG_PUB.add;
1858
1859
1860 END IF;
1861
1862 END IF;
1863 -- End of PJM validation.
1864 */
1865
1866 -- Validate if item, item_identifier_type, inventory_item combination is valid
1867 oe_debug_pub.add('12-1 '||l_return_status, 1);
1868 IF p_line_rec.inventory_item_id IS NOT NULL THEN
1869
1870 IF NOT Validate_Item_Fields
1871 ( p_line_rec.inventory_item_id
1872 , p_line_rec.ordered_item_id
1873 , p_line_rec.item_identifier_type
1874 , p_line_rec.ordered_item
1875 , p_line_rec.sold_to_org_id)
1876 THEN
1877 l_return_status := FND_API.G_RET_STS_ERROR;
1878 fnd_message.set_name('ONT', 'OE_ITEM_VALIDATION_FAILED');
1879 OE_MSG_PUB.add;
1880 END IF;
1881
1882 END IF;
1883
1884 oe_debug_pub.add('12 '||l_return_status, 1);
1885 -- Validate if return item and item on referenced sales order line mismatch
1886 IF (p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE and
1887 p_line_rec.reference_line_id is not null and
1888 p_line_rec.inventory_item_id IS NOT NULL)
1889 THEN
1890 IF NOT Validate_Return_Item_Mismatch
1891 ( p_line_rec.reference_line_id
1892 , p_line_rec.inventory_item_id
1893 )
1894 THEN
1895 l_return_status := FND_API.G_RET_STS_ERROR;
1896 fnd_message.set_name('ONT', 'OE_RETURN_ITEM_MISMATCH_REJECT');
1897 OE_MSG_PUB.add;
1898 END IF;
1899 END IF;
1900
1901 oe_debug_pub.add('13 '||l_return_status, 1);
1902
1903 -- Validate if returning a fulfilled sales order line
1904 IF (p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE and
1905 p_line_rec.reference_line_id is not null)
1906 THEN
1907 IF NOT Validate_Return_Fulfilled_Line
1908 ( p_line_rec.reference_line_id
1909 )
1910 THEN
1911 l_return_status := FND_API.G_RET_STS_ERROR;
1912 fnd_message.set_name('ONT', 'OE_UNFULFILLED_LINE_REJECT');
1913 OE_MSG_PUB.add;
1914 END IF;
1915
1916 END IF;
1917
1918 oe_debug_pub.add('14 '||l_return_status, 1);
1919
1920 -- Validate if item on the Return is Returnable
1921 IF (p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE and
1922 p_line_rec.inventory_item_id IS NOT NULL)
1923 THEN
1924 IF NOT Validate_Return_Item(p_line_rec.inventory_item_id,
1925 p_line_rec.ship_from_org_id)
1926 THEN
1927 l_return_status := FND_API.G_RET_STS_ERROR;
1928 fnd_message.set_name('ONT', 'OE_ITEM_NOT_RETURNABLE');
1929 OE_MSG_PUB.add;
1930 END IF;
1931 END IF;
1932
1933
1934 oe_debug_pub.add('14_1 '||l_return_status, 1);
1935
1936 -- Validate if Reference SO Line is Valid
1937 IF (p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE and
1938 p_line_rec.reference_line_id is not null)
1939 THEN
1940 IF NOT Validate_Return_Reference(p_line_rec.reference_line_id)
1941 THEN
1942 -- Message is populated in the function
1943 l_return_status := FND_API.G_RET_STS_ERROR;
1944 END IF;
1945 END IF;
1946
1947 oe_debug_pub.add('14_2 '||l_return_status, 1);
1948
1949 -- Validate the quantity = 1 on RMA for Serial Number reference
1950 IF (p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE and
1951 p_line_rec.reference_line_id is not null and
1952 p_line_rec.return_context = 'SERIAL' and
1953 NVL(p_line_rec.ordered_quantity,1) <> 1)
1954 THEN
1955 l_return_status := FND_API.G_RET_STS_ERROR;
1956 fnd_message.set_name('ONT','OE_SERIAL_REFERENCED_RMA');
1957 OE_MSG_PUB.Add;
1958 END IF;
1959
1960
1961 oe_debug_pub.add('14_3 '||l_return_status, 1);
1962
1963 -- Validation of Ship To Org Id.
1964 IF p_line_rec.ship_to_org_id IS NOT NULL
1965
1966 THEN
1967
1968 IF NOT Validate_Ship_To_Org(p_line_rec.ship_to_org_id,
1969 p_line_rec.sold_to_org_id
1970 ) THEN
1971 l_return_status := FND_API.G_RET_STS_ERROR;
1972 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1973 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1974 OE_Order_Util.Get_Attribute_Name('ship_to_org_id'));
1975 OE_MSG_PUB.Add;
1976 END IF;
1977
1978 END IF;
1979
1980
1981 -- Ship to contact depends on Ship To Org
1982 IF p_line_rec.ship_to_contact_id IS NOT NULL
1983
1984 THEN
1985
1986 BEGIN
1987 oe_debug_pub.add('ship_to_contact_id :'||to_char(p_line_rec.ship_to_contact_id),2);
1988
1989 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
1990 INTO l_dummy
1991 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
1992 , HZ_CUST_ACCT_SITES ACCT_SITE
1993 , HZ_CUST_SITE_USES_ALL SHIP
1994 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_line_rec.ship_to_contact_id
1995 AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
1996 AND ACCT_SITE.CUST_ACCT_SITE_ID = SHIP.CUST_ACCT_SITE_ID
1997 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1998 AND SHIP.SITE_USE_ID = p_line_rec.ship_to_org_id
1999 AND SHIP.STATUS = 'A'
2000 AND ROWNUM = 1;
2001
2002 /* Replaced ra_contacts , ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440 */
2003
2004
2005 EXCEPTION
2006 WHEN NO_DATA_FOUND THEN
2007 l_return_status := FND_API.G_RET_STS_ERROR;
2008 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2009 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2010 OE_Order_Util.Get_Attribute_Name('ship_to_contact_id'));
2011 OE_MSG_PUB.Add;
2012 WHEN OTHERS THEN
2013 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2014 OE_MSG_PUB.Add_Exc_Msg
2015 ( G_PKG_NAME ,
2016 'Record - Ship To Contact validation '
2017 );
2018 END IF;
2019 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2020 END;
2021
2022 END IF;
2023
2024
2025 -- Validation of Deliver To Org Id.
2026 IF p_line_rec.deliver_to_org_id IS NOT NULL
2027
2028 THEN
2029
2030 IF NOT Validate_Deliver_To_Org(p_line_rec.deliver_to_org_id,
2031 p_line_rec.sold_to_org_id
2032 ) THEN
2033 l_return_status := FND_API.G_RET_STS_ERROR;
2034 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
2035 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2036 OE_Order_Util.Get_Attribute_Name('deliver_to_org_id'));
2037 OE_MSG_PUB.Add;
2038 END IF;
2039
2040 END IF;
2041
2042 -- Deliver to contact depends on Deliver To Org
2043 IF p_line_rec.deliver_to_contact_id IS NOT NULL
2044
2045 THEN
2046
2047 BEGIN
2048 oe_debug_pub.add('deliver_to_contact_id :'||to_char(p_line_rec.deliver_to_contact_id),2);
2049
2050 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
2051 INTO l_dummy
2052 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
2053 , HZ_CUST_ACCT_SITES ACCT_SITE
2054 , HZ_CUST_SITE_USES_ALL DELI
2055 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_line_rec.deliver_to_contact_id
2056 AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
2057 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
2058 AND ACCT_SITE.CUST_ACCT_SITE_ID = DELI.CUST_ACCT_SITE_ID
2059 AND DELI.SITE_USE_ID = p_line_rec.deliver_to_org_id
2060 AND DELI.STATUS = 'A'
2061 AND ROWNUM = 1;
2062
2063 /* Replaced ra_contacts , ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440 */
2064
2065
2066 EXCEPTION
2067 WHEN NO_DATA_FOUND THEN
2068 l_return_status := FND_API.G_RET_STS_ERROR;
2069 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2070 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2071 OE_Order_Util.Get_Attribute_Name('deliver_to_contact_id'));
2072 OE_MSG_PUB.Add;
2073 WHEN OTHERS THEN
2074 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2075 OE_MSG_PUB.Add_Exc_Msg
2076 ( G_PKG_NAME ,
2077 'Record - Deliver To Contact validation '
2078 );
2079 END IF;
2080 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2081 END;
2082
2083 END IF;
2084
2085 -- Validation of Invoice To Org Id.
2086 IF p_line_rec.invoice_to_org_id IS NOT NULL
2087
2088 THEN
2089
2090 BEGIN
2091 oe_debug_pub.add('invoice_to_org_id :'||to_char(p_line_rec.invoice_to_org_id),2);
2092 --lcustomer_relations := FND_PROFILE.VALUE('ONT_CUSTOMER_RELATIONSHIPS');
2093
2094 IF nvl(lcustomer_relations,'N') = 'N' THEN
2095
2096 Select 'VALID'
2097 Into l_dummy
2098 From oe_invoice_to_orgs_v
2099 Where customer_id = p_line_rec.sold_to_org_id
2100 And site_use_id = p_line_rec.invoice_to_org_id;
2101
2102 ELSIF lcustomer_relations = 'Y' THEN
2103
2104 /*Select MOAC_SQL_NO_CHANGE 'VALID'
2105 Into l_dummy
2106 From oe_invoice_to_orgs_v
2107 WHERE site_use_id = p_line_rec.invoice_to_org_id
2108 AND status = 'A' AND
2109 customer_id in (
2110 Select p_line_rec.sold_to_org_id from dual
2111 union
2112 select CUST_ACCOUNT_ID from
2113 HZ_CUST_ACCT_RELATE
2114 where RELATED_CUST_ACCOUNT_ID = p_line_rec.sold_to_org_id
2115 /* added the following condition to fix the bug 2002486
2116 and bill_to_flag = 'Y')
2117 and rownum = 1;*/
2118
2119 --bug 4729536
2120 OPEN cur_customer_relations;
2121 FETCH cur_customer_relations INTO l_dummy ;
2122 CLOSE cur_customer_relations;
2123
2124 /* Replaced ra_customer_relationships with HZ Table , to fix the bug 1888440 */
2125
2126
2127 /* added the following ELSIF condition to fix the bug 2002486 */
2128
2129 ELSIF nvl(lcustomer_relations,'N') = 'A' THEN
2130 oe_debug_pub.add
2131 ('Cr: A',2);
2132
2133 SELECT 'VALID'
2134 INTO l_dummy
2135 From oe_invoice_to_orgs_v
2136 WHERE site_use_id = p_line_rec.invoice_to_org_id
2137 AND ROWNUM = 1;
2138
2139
2140 END IF;
2141
2142 EXCEPTION
2143 WHEN NO_DATA_FOUND THEN
2144 l_return_status := FND_API.G_RET_STS_ERROR;
2145 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2146 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2147 OE_Order_Util.Get_Attribute_Name('invoice_to_org_id'));
2148 OE_MSG_PUB.Add;
2149 WHEN OTHERS THEN
2150 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2151 OE_MSG_PUB.Add_Exc_Msg
2152 ( G_PKG_NAME ,
2153 'Record - Invoice To Org validation '
2154 );
2155 END IF;
2156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2157 END;
2158
2159 END IF;
2160
2161 -- Validation of Invoice To Contact Id.
2162 IF p_line_rec.invoice_to_contact_id IS NOT NULL
2163
2164 THEN
2165
2166 BEGIN
2167 oe_debug_pub.add('invoice_to_contact_id :'||to_char(p_line_rec.invoice_to_contact_id),2);
2168
2169 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
2170 INTO l_dummy
2171 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
2172 , HZ_CUST_ACCT_SITES ACCT_SITE
2173 , HZ_CUST_SITE_USES_ALL INV
2174 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_line_rec.invoice_to_contact_id
2175 AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
2176 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
2177 AND ACCT_SITE.CUST_ACCT_SITE_ID = INV.CUST_ACCT_SITE_ID
2178 AND INV.SITE_USE_ID = p_line_rec.invoice_to_org_id
2179 AND ROWNUM = 1;
2180
2181 /* Replaced ra_contacts , ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440 */
2182
2183
2184 EXCEPTION
2185 WHEN NO_DATA_FOUND THEN
2186 l_return_status := FND_API.G_RET_STS_ERROR;
2187 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2188 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2189 OE_Order_Util.Get_Attribute_Name('invoice_to_contact_id'));
2190 OE_MSG_PUB.Add;
2191 WHEN OTHERS THEN
2192 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2193 OE_MSG_PUB.Add_Exc_Msg
2194 ( G_PKG_NAME ,
2195 'Record - Invoice To Contact validation '
2196 );
2197 END IF;
2198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2199 END;
2200
2201 END IF;
2202
2203
2204 /* Added by Manish */
2205
2206 -- Validating Tax Information
2207 IF p_line_rec.tax_code IS NOT NULL AND
2208 p_line_rec.tax_date IS NOT NULL
2209
2210 THEN
2211 BEGIN
2212 -- eBTax changes
2213 /* IF oe_code_control.code_release_level < '110510' THEN
2214 SELECT 'VALID'
2215 INTO l_dummy
2216 FROM AR_VAT_TAX V,
2217 AR_SYSTEM_PARAMETERS P
2218 WHERE V.TAX_CODE = p_line_rec.tax_code
2219 AND V.SET_OF_BOOKS_ID = P.SET_OF_BOOKS_ID
2220 AND NVL(V.TAX_CLASS,'O')='O'
2221 AND NVL(V.DISPLAYED_FLAG,'Y')='Y'
2222 AND ROWNUM = 1;
2223 ELSE
2224 l_AR_Sys_Param_Rec := OE_Sys_Parameters_Pvt.Get_AR_Sys_Params(p_line_rec.org_id);
2225 l_sob_id := l_AR_Sys_Param_Rec.set_of_books_id;
2226 SELECT 'VALID'
2227 INTO l_dummy
2228 FROM AR_VAT_TAX V
2229 WHERE V.TAX_CODE = p_line_rec.tax_code
2230 AND V.SET_OF_BOOKS_ID = l_sob_id
2231 AND NVL(V.TAX_CLASS,'O')='O'
2232 AND NVL(V.DISPLAYED_FLAG,'Y')='Y'
2233 AND ROWNUM = 1;
2234 END IF;*/
2235
2236 SELECT 'VALID'
2237 INTO l_dummy
2238 FROM ZX_OUTPUT_CLASSIFICATIONS_V lk
2239 WHERE lk.lookup_code = p_line_rec.tax_code
2240 --AND lk.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
2241 AND lk.ENABLED_FLAG ='Y'
2242 AND lk.ORG_ID IN (p_line_rec.org_id, -99)
2243 AND TRUNC(p_line_rec.tax_date) BETWEEN TRUNC(lk.START_DATE_ACTIVE)
2244 AND TRUNC(NVL(lk.END_DATE_ACTIVE, p_line_rec.tax_date))
2245 AND ROWNUM = 1;
2246
2247
2248 EXCEPTION
2249
2250 WHEN NO_DATA_FOUND THEN
2251 l_return_status := FND_API.G_RET_STS_ERROR;
2252 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2253 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2254 OE_Order_Util.Get_Attribute_Name('TAX_CODE'));
2255 OE_MSG_PUB.Add;
2256
2257 WHEN OTHERS THEN
2258 IF OE_MSG_PUB.Check_Msg_Level (
2259 OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2260 THEN
2261 OE_MSG_PUB.Add_Exc_Msg
2262 ( G_PKG_NAME ,
2263 'Record - Tax Code validation '
2264 );
2265 END IF;
2266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2267
2268 END; -- BEGIN
2269 END IF;
2270
2271 -- If the Tax handling is "Exempt"
2272
2273 IF p_line_rec.tax_exempt_flag = 'E'
2274 THEN
2275 -- Check for Tax exempt reason
2276 IF p_line_rec.tax_exempt_reason_code IS NULL
2277 THEN
2278 l_return_status := FND_API.G_RET_STS_ERROR;
2279 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_ERROR)
2280 THEN
2281 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
2282 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2283 OE_Order_Util.Get_Attribute_Name('TAX_EXEMPT_REASON_CODE'));
2284 OE_MSG_PUB.Add;
2285 END IF;
2286 END IF;
2287 END IF; -- If Tax handling is exempt
2288
2289 -- If the TAX handling is STANDARD THEN we can not validate for
2290 -- exemption number because it can be a NULL value.
2291
2292
2293 -- If the Tax handling is "Required" then Tax Exempt Number and
2294 -- Tax Exempt Reason should be NULL.
2295
2296 IF p_line_rec.tax_exempt_flag = 'R' AND
2297 (p_line_rec.tax_exempt_number IS NOT NULL OR
2298 p_line_rec.tax_exempt_reason_code IS NOT NULL)
2299 THEN
2300 l_return_status := FND_API.G_RET_STS_ERROR;
2301 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_ERROR)
2302 THEN
2303 fnd_message.set_name('ONT','OE_TAX_EXEMPTION_NOT_ALLOWED');
2304 OE_MSG_PUB.Add;
2305 END IF;
2306
2307 END IF; -- If Tax handling is Required
2308
2309 -- Check for Tax Exempt number/ Tax Exempt reason code depends on
2310 -- following attributes if the Tax_exempt_flag = 'S' (Standard)
2311
2312 IF p_line_rec.tax_exempt_flag IS NOT NULL
2313
2314 THEN
2315
2316 BEGIN
2317 -- eBtax changes
2318 IF p_line_rec.tax_exempt_flag = 'S' and --* recheck (for 'E' ??)
2319 p_line_rec.tax_exempt_number IS NOT NULL and
2320 p_line_rec.tax_exempt_reason_code IS NOT NULL and
2321 p_line_rec.tax_code IS NOT NULL
2322 THEN
2323
2324 /* SELECT 'VALID'
2325 INTO l_dummy
2326 FROM OE_TAX_EXEMPTIONS_QP_V
2327 WHERE TAX_EXEMPT_NUMBER = p_line_rec.tax_exempt_number
2328 AND TAX_EXEMPT_REASON_CODE=p_line_rec.tax_exempt_reason_code
2329 AND SHIP_TO_ORG_ID = nvl(p_line_rec.ship_to_org_id,
2330 p_line_rec.invoice_to_org_id)
2331 AND BILL_TO_CUSTOMER_ID = p_line_rec.sold_to_org_id
2332 AND TAX_CODE = p_line_rec.tax_code
2333 AND STATUS_CODE = 'PRIMARY'
2334 AND ROWNUM = 1;*/
2335
2336
2337 open partyinfo(p_line_rec.invoice_to_org_id);
2338 fetch partyinfo into l_bill_to_cust_Acct_id,
2339 l_bill_to_party_id,
2340 l_bill_to_party_site_id,
2341 l_org_id;
2342 close partyinfo;
2343
2344 if p_line_rec.ship_to_org_id = p_line_rec.invoice_to_org_id then
2345 l_ship_to_cust_Acct_id := l_bill_to_cust_Acct_id;
2346 l_ship_to_party_id := l_bill_to_party_id;
2347 l_ship_to_party_site_id := l_bill_to_party_site_id ;
2348 else
2349 open partyinfo(p_line_rec.ship_to_org_id);
2350 fetch partyinfo into l_ship_to_cust_Acct_id,
2351 l_ship_to_party_id,
2352 l_ship_to_party_site_id,
2353 l_org_id;
2354 close partyinfo;
2355 end if;
2356
2357
2358 SELECT 'VALID'
2359 INTO l_dummy
2360 FROM ZX_EXEMPTIONS_V
2361 WHERE EXEMPT_CERTIFICATE_NUMBER = p_line_rec.tax_exempt_number
2362 AND EXEMPT_REASON_CODE = p_line_rec.tax_exempt_reason_code
2363 AND nvl(site_use_id,nvl(p_line_rec.ship_to_org_id,
2364 p_line_rec.invoice_to_org_id))
2365 = nvl(p_line_rec.ship_to_org_id,
2366 p_line_rec.invoice_to_org_id)
2367 AND nvl(cust_account_id, l_bill_to_cust_acct_id) = l_bill_to_cust_acct_id
2368 AND nvl(PARTY_SITE_ID,nvl(l_ship_to_party_site_id, l_bill_to_party_site_id))=
2369 nvl(l_ship_to_party_site_id, l_bill_to_party_site_id)
2370 AND org_id = l_org_id
2371 AND party_id = l_bill_to_party_id
2372 -- AND nvl(LEGAL_ENTITY_ID,-99) IN (nvl(l_legal_entity_id, legal_entity_id), -99)
2373 AND EXEMPTION_STATUS_CODE = 'PRIMARY'
2374
2375 -- **** Check with OM team whether the join based on date is required or not ****
2376 -- AND TRUNC(NVL(p_line_rec.request_date,sysdate))
2377 -- BETWEEN TRUNC(EFFECTIVE_FROM)
2378 -- AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_line_rec.request_date,sysdate)))
2379 AND ROWNUM = 1;
2380
2381 END IF;
2382
2383 -- Valid Tax Exempt Number.
2384
2385 EXCEPTION
2386
2387 WHEN NO_DATA_FOUND THEN
2388
2389 l_return_status := FND_API.G_RET_STS_ERROR;
2390
2391 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2392 THEN
2393 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
2394 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2395 OE_Order_Util.Get_Attribute_Name('TAX_EXEMPT_NUMBER'));
2396 OE_MSG_PUB.Add;
2397 END IF;
2398
2399 WHEN OTHERS THEN
2400 IF OE_MSG_PUB.Check_Msg_Level (
2401 OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2402 THEN
2403 OE_MSG_PUB.Add_Exc_Msg
2404 ( G_PKG_NAME ,
2405 'Record - Tax Exemptions '
2406 );
2407 END IF;
2408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2409
2410 END; -- BEGIN
2411
2412 END IF; -- Tax exempton info validation.
2413 /* Added by Manish */
2414
2415 -- order_quantity_uom should be primary uom for model/class/option.
2416 IF p_line_rec.order_quantity_uom is not null
2417
2418 THEN
2419
2420 IF ( p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_MODEL OR
2421 p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
2422 p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION ) THEN
2423 BEGIN
2424 SELECT primary_uom_code
2425 INTO l_uom
2426 FROM mtl_system_items
2427 WHERE inventory_item_id = p_line_rec.inventory_item_id
2428 AND organization_id = nvl(p_line_rec.ship_from_org_id,
2429 OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID'));
2430
2431
2432 oe_debug_pub.add('primary uom: '|| l_uom, 1);
2433 oe_debug_pub.add('uom entered: '||p_line_rec.order_quantity_uom , 1);
2434
2435 IF l_uom <> p_line_rec.order_quantity_uom
2436 THEN
2437 oe_debug_pub.add('uom other than primary uom is entered', 1);
2438
2439 fnd_message.set_name('ONT','OE_INVALID_ORDER_QUANTITY_UOM');
2440 fnd_message.set_token('ITEM',p_line_rec.ordered_item );
2441 fnd_message.set_token('UOM', l_uom);
2442 OE_MSG_PUB.Add;
2443 RAISE FND_API.G_EXC_ERROR;
2444 END IF;
2445 EXCEPTION
2446 when no_data_found then
2447 oe_debug_pub.add('OEXLLINB, no_data_found in uom validation', 1);
2448 RAISE FND_API.G_EXC_ERROR;
2449 END;
2450
2451 ELSE -- not ato related, validate item, uom combination
2452 /* commenting for 8993157
2453 SELECT count(*)
2454 INTO l_uom_count
2455 FROM mtl_item_uoms_view
2456 WHERE inventory_item_id = p_line_rec.inventory_item_id
2457 AND uom_code = p_line_rec.order_quantity_uom
2458 AND organization_id = nvl(p_line_rec.ship_from_org_id,
2459 OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID'));
2460
2461
2462 IF l_uom_count = 0 THEN
2463 oe_debug_pub.add('uom/item combination invalid',2);
2464 fnd_message.set_name('ONT', 'OE_INVALID_ITEM_UOM');
2465 OE_MSG_PUB.Add;
2466 RAISE FND_API.G_EXC_ERROR;
2467 END IF;
2468 */
2469
2470 l_ret_status :=
2471 inv_convert.validate_item_uom(p_line_rec.order_quantity_uom,
2472 p_line_rec.inventory_item_id,
2473 nvl(p_line_rec.ship_from_org_id,
2474 OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID')));
2475 IF NOT l_ret_status THEN
2476 if l_debug_level > 0 then
2477 oe_debug_pub.add('uom/item combination invalid',2);
2478 end if;
2479
2480 fnd_message.set_name('ONT', 'OE_INVALID_ITEM_UOM');
2481 OE_MSG_PUB.Add;
2482 RAISE FND_API.G_EXC_ERROR;
2483 END IF;
2484
2485
2486 END IF;
2487 END IF;
2488
2489 If p_line_rec.agreement_id is not null and
2490 NOT OE_GLOBALS.EQUAL(p_line_rec.agreement_id, fnd_api.g_miss_num) then
2491 If not oe_globals.equal(p_line_rec.agreement_id,null) then
2492
2493 -- Check for Agreement +sold_org_id
2494
2495 -- Where cluase added to check start and end date for agreements
2496 -- Geresh
2497
2498 BEGIN
2499 BEGIN
2500 select list_type_code
2501 into l_list_type_code
2502 from qp_list_headers_vl
2503 where list_header_id = p_line_rec.price_list_id;
2504 EXCEPTION WHEN NO_DATA_FOUND THEN
2505 null;
2506 END;
2507
2508 BEGIN
2509 SELECT name ,sold_to_org_id , price_list_id
2510 INTO l_agreement_name,l_sold_to_org,l_price_list_id
2511 FROM oe_agreements_v
2512 WHERE agreement_id = p_line_rec.agreement_id;
2513 EXCEPTION WHEN NO_DATA_FOUND THEN
2514 null;
2515 END;
2516
2517
2518 IF NOT OE_GLOBALS.EQUAL(l_list_type_code,'PRL') THEN
2519 -- any price list with 'PRL' type should be allowed to
2520 -- be associated with any agreement according to bug 1386406.
2521 IF NOT OE_GLOBALS.EQUAL(l_price_list_id, p_line_rec.price_list_id) THEN
2522 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT_PLIST');
2523 fnd_message.set_Token('AGREEMENT_NAME', l_agreement_name);
2524 BEGIN
2525 SELECT name
2526 INTO l_price_list_name
2527 FROM qp_List_headers_vl
2528 WHERE list_header_id = p_line_rec.price_list_id;
2529
2530 Exception when no_data_found then
2531 l_price_list_name := p_line_rec.price_list_id;
2532 END;
2533 fnd_message.set_Token('PRICE_LIST1', l_price_list_name);
2534 BEGIN
2535
2536 SELECT name
2537 INTO l_price_list_name
2538 FROM QP_List_headers_vl
2539 WHERE list_header_id = l_price_list_id;
2540 EXCEPTION
2541 WHEN NO_DATA_FOUND THEN
2542 l_price_list_name := l_price_list_id;
2543 END;
2544 fnd_message.set_Token('PRICE_LIST2', l_price_list_name);
2545 OE_MSG_PUB.Add;
2546 oe_debug_pub.add('Invalid Agreement +price_list_id combination',2);
2547 raise FND_API.G_EXC_ERROR;
2548 END IF;
2549 END IF; -- end of if l_list_type_code <> 'PRL'
2550
2551
2552 -- modified by lkxu, to check for customer relationships.
2553 IF l_sold_to_org IS NOT NULL AND l_sold_to_org <> -1
2554 AND NOT OE_GLOBALS.EQUAL(l_sold_to_org,p_line_rec.sold_to_org_id) THEN
2555 IF nvl(lcustomer_relations,'N') = 'N' THEN
2556 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT');
2557 fnd_message.set_Token('AGREEMENT_ID', p_line_rec.agreement_id);
2558 fnd_message.set_Token('AGREEMENT_NAME', l_agreement_name);
2559 fnd_message.set_Token('CUSTOMER_ID', p_line_rec.sold_to_org_id);
2560 OE_MSG_PUB.Add;
2561 oe_debug_pub.add('Invalid Agreement +sold_org_id combination',2);
2562 RAISE FND_API.G_EXC_ERROR;
2563 ELSIF lcustomer_relations = 'Y' THEN
2564
2565 BEGIN
2566 SELECT 'VALID'
2567 INTO l_dummy
2568 FROM dual
2569 WHERE exists(
2570 select 'x' from
2571 HZ_CUST_ACCT_RELATE
2572 where RELATED_CUST_ACCOUNT_ID = p_line_rec.sold_to_org_id
2573 AND CUST_ACCOUNT_ID = l_sold_to_org
2574
2575 );
2576
2577 oe_debug_pub.add('Linda -- l_dummy is: '||l_dummy,2);
2578 /* Replaced ra_customer_relationships with HZ Table to fix the bug 1888440 */
2579
2580 EXCEPTION
2581 WHEN NO_DATA_FOUND THEN
2582 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT');
2583 fnd_message.set_Token('AGREEMENT_ID', p_line_rec.agreement_id);
2584 fnd_message.set_Token('AGREEMENT_NAME', l_agreement_name);
2585 fnd_message.set_Token('CUSTOMER_ID', p_line_rec.sold_to_org_id);
2586 OE_MSG_PUB.Add;
2587 oe_debug_pub.add('Invalid Agreement +sold_org_id combination',2);
2588 RAISE FND_API.G_EXC_ERROR;
2589 END;
2590 END IF;
2591 END IF;
2592
2593
2594 EXCEPTION
2595 WHEN NO_DATA_FOUND THEN
2596 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT');
2597 fnd_message.set_Token('AGREEMENT_ID', p_line_rec.agreement_id);
2598 fnd_message.set_Token('AGREEMENT_NAME', l_agreement_name);
2599 fnd_message.set_Token('CUSTOMER_ID', l_sold_to_org);
2600 OE_MSG_PUB.Add;
2601 oe_debug_pub.add('Invalid Agreement +sold_org_id combination',2);
2602 RAISE FND_API.G_EXC_ERROR;
2603 END;
2604 END IF; -- Agreement has changed
2605
2606 ELSE
2607
2608 /*IF NOT oe_globals.equal(p_line_rec.pricing_date,p_old_line_rec.pricing_date) OR
2609 not oe_globals.equal(p_line_rec.price_list_id,p_old_line_rec.price_list_id) THEN*/
2610
2611
2612 -- Allow only the non agreement price_lists
2613 BEGIN
2614 oe_debug_pub.add('Pricing date is '||p_line_rec.pricing_date,2);
2615 -- modified by lkxu: to select from qp_list_headers_vl instead
2616 -- of from qp_price_lists_v to select only PRL type list headers.
2617
2618 SELECT name
2619 INTO l_price_list_name
2620 FROM qp_list_headers_vl
2621 WHERE list_header_id = p_line_rec.price_list_id
2622 AND list_type_code = 'PRL';
2623
2624 EXCEPTION
2625 WHEN NO_DATA_FOUND THEN
2626 fnd_message.set_name('ONT', 'OE_INVALID_NONAGR_PLIST');
2627 fnd_message.set_Token('PRICE_LIST1', p_line_rec.price_list_id);
2628 fnd_message.set_Token('PRICING_DATE', p_line_rec.pricing_date);
2629 OE_MSG_PUB.Add;
2630 oe_debug_pub.add('Invalid non agreement price list ',2);
2631 RAISE FND_API.G_EXC_ERROR;
2632 END;
2633
2634 --END IF; -- Price list or pricing date has changed
2635 END IF;
2636
2637 oe_debug_pub.add('15 '||l_return_status ,1);
2638
2639 -- Line number validation.
2640 -- Allow line number updates only on Model, Standard, Kit,
2641 --and stand alone service line.
2642
2643 /*IF p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
2644
2645 IF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION) OR
2646 (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS) OR
2647 (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT) OR
2648 (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE AND
2649 p_line_rec.service_reference_line_id IS NOT NULL AND
2650 p_line_rec.service_reference_line_id <> FND_API.G_MISS_NUM)
2651
2652 THEN
2653
2654 IF (NOT OE_GLOBALS.EQUAL(p_line_rec.line_number,null)) THEN
2655
2656 l_return_status := FND_API.G_RET_STS_ERROR;
2657 fnd_message.set_name('ONT', 'OE_LINE_NUMBER_UPD');
2658 OE_MSG_PUB.add;
2659
2660 END IF;
2661 END IF;
2662
2663 END IF;
2664 */
2665
2666 oe_debug_pub.add('16 '||l_return_status ,1);
2667
2668 /*IF p_line_rec.top_model_line_id is not null AND
2669 p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
2670 p_line_rec.ordered_quantity = 0
2671 THEN
2672 oe_debug_pub.add
2673 ('qty of a configuration related line 0'|| p_line_rec.item_type_code, 1);
2674 END IF;
2675 */
2676
2677 oe_debug_pub.add('OEXLLINB, RR:T2',1);
2678 oe_debug_pub.add('17 '||l_return_status ,1);
2679
2680 -- Validate ordered quantity for OTA lines. OTA Lines are
2681 -- identified by item_type_code of training. The ordered
2682 -- quantity cannot be greater than 1 for OTA lines.
2683
2684 l_order_quantity_uom := p_line_rec.order_quantity_uom;
2685 l_is_ota_line := OE_OTA_UTIL.Is_OTA_Line(l_order_quantity_uom);
2686
2687 IF (l_is_ota_line) AND
2688 p_line_rec.ordered_quantity > 1 then
2689
2690 oe_debug_pub.add('Ordered Qty cannot be greater than 1 for OTA lines',
2691 1);
2692 l_return_status := FND_API.G_RET_STS_ERROR;
2693 FND_Message.Set_Name('ONT', 'OE_OTA_INVALID_QTY');
2694 oe_msg_pub.add;
2695 END IF;
2696
2697 /* End of validation for OTA */
2698
2699
2700 -- Fix bug 1162304: issue a warning message if the PO number
2701 -- is being referenced by another order
2702 IF p_line_rec.cust_po_number IS NOT NULL
2703
2704 THEN
2705
2706 IF OE_CNCL_Validate_Header.Is_Duplicate_PO_Number
2707 (p_line_rec.cust_po_number
2708 ,p_line_rec.sold_to_org_id
2709 ,p_line_rec.header_id )
2710 THEN
2711 FND_MESSAGE.SET_NAME('ONT','OE_VAL_DUP_PO_NUMBER');
2712 OE_MSG_PUB.ADD;
2713 END IF;
2714
2715 END IF;
2716 -- End of check for duplicate PO number
2717
2718
2719 -- Fix for bug#1411346:
2720 -- SERVICE end date must be after service start date.
2721
2722 IF (p_line_rec.service_end_date <> FND_API.G_MISS_DATE OR
2723 p_line_rec.service_end_date IS NOT NULL) AND
2724 (p_line_rec.service_start_date <> FND_API.G_MISS_DATE OR
2725 p_line_rec.service_start_date IS NOT NULL) THEN
2726
2727 IF (p_line_rec.service_end_date <= p_line_rec.service_start_date)
2728 THEN
2729 l_return_status := FND_API.G_RET_STS_ERROR;
2730 fnd_message.set_name('ONT','OE_SERV_END_DATE');
2731 OE_MSG_PUB.Add;
2732 END IF;
2733
2734 END IF;
2735
2736 oe_debug_pub.add('18 '||l_return_status ,1);
2737 x_return_status := l_return_status;
2738
2739 -- Done validating entity
2740 oe_debug_pub.add('Exit OE_CNCL_VALIDATE_LINE.ENTITY',1);
2741 EXCEPTION
2742
2743 WHEN FND_API.G_EXC_ERROR THEN
2744
2745 x_return_status := FND_API.G_RET_STS_ERROR;
2746
2747 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2748
2749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2750
2751 WHEN OTHERS THEN
2752
2753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2754
2755 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2756 THEN
2757 OE_MSG_PUB.Add_Exc_Msg
2758 ( G_PKG_NAME
2759 , 'Entity'
2760 );
2761 END IF;
2762
2763 END Entity;
2764
2765 -- Procedure Attributes
2766
2767 PROCEDURE Attributes
2768 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2769 , p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
2770 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2771 )
2772 IS
2773 l_return_status VARCHAR2(1);
2774 l_line_rec OE_Order_PUB.Line_Rec_Type := OE_Order_PUB.G_MISS_LINE_REC;
2775 l_type_code VARCHAR2(30);
2776 BEGIN
2777 oe_debug_pub.add('Enter procedure OE_CNCL_VALIDATE_line.Attributes',1);
2778
2779 x_return_status := FND_API.G_RET_STS_SUCCESS;
2780 l_return_status := FND_API.G_RET_STS_SUCCESS;
2781
2782 -- validate Sales Agreements Attributes
2783 IF p_x_line_rec.blanket_number IS NOT NULL
2784 and p_x_line_rec.blanket_line_number is NOT NULL
2785 THEN
2786 x_return_status := FND_API.G_RET_STS_ERROR;
2787 fnd_message.set_name('ONT', 'OE_BLKT_DISALLOW_CLOSE_REL');
2788 OE_MSG_PUB.add;
2789 END IF;
2790
2791 -- Validate line attributes
2792
2793 IF p_x_line_rec.accounting_rule_id IS NOT NULL
2794 THEN
2795 IF NOT OE_CNCL_Validate.Accounting_Rule(p_x_line_rec.accounting_rule_id) THEN
2796 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2797 p_x_line_rec.accounting_rule_id := NULL;
2798 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2799 p_x_line_rec.accounting_rule_id := FND_API.G_MISS_NUM;
2800 ELSE
2801 x_return_status := FND_API.G_RET_STS_ERROR;
2802 END IF;
2803 END IF;
2804 END IF;
2805
2806 IF p_x_line_rec.agreement_id IS NOT NULL
2807 THEN
2808 IF NOT OE_CNCL_Validate.Agreement(p_x_line_rec.agreement_id) THEN
2809 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2810 p_x_line_rec.agreement_id := NULL;
2811 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2812 p_x_line_rec.agreement_id := FND_API.G_MISS_NUM;
2813 ELSE
2814 x_return_status := FND_API.G_RET_STS_ERROR;
2815 END IF;
2816 END IF;
2817 END IF;
2818
2819
2820 IF p_x_line_rec.deliver_to_contact_id IS NOT NULL
2821 THEN
2822 IF NOT OE_CNCL_Validate.Deliver_To_Contact(p_x_line_rec.deliver_to_contact_id) THEN
2823 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2824 p_x_line_rec.deliver_to_contact_id := NULL;
2825 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2826 p_x_line_rec.deliver_to_contact_id := FND_API.G_MISS_NUM;
2827 ELSE
2828 x_return_status := FND_API.G_RET_STS_ERROR;
2829 END IF;
2830 END IF;
2831 END IF;
2832
2833 IF p_x_line_rec.deliver_to_org_id IS NOT NULL
2834 THEN
2835 IF NOT OE_CNCL_Validate.Deliver_To_Org(p_x_line_rec.deliver_to_org_id) THEN
2836 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2837 p_x_line_rec.deliver_to_org_id := NULL;
2838 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2839 p_x_line_rec.deliver_to_org_id := FND_API.G_MISS_NUM;
2840 ELSE
2841 x_return_status := FND_API.G_RET_STS_ERROR;
2842 END IF;
2843 END IF;
2844 END IF;
2845
2846 IF p_x_line_rec.demand_class_code IS NOT NULL
2847 THEN
2848 IF NOT OE_CNCL_Validate.Demand_Class(p_x_line_rec.demand_class_code) THEN
2849 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2850 p_x_line_rec.demand_class_code := NULL;
2851 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2852 p_x_line_rec.demand_class_code := FND_API.G_MISS_CHAR;
2853 ELSE
2854 x_return_status := FND_API.G_RET_STS_ERROR;
2855 END IF;
2856 END IF;
2857 END IF;
2858
2859 IF p_x_line_rec.dep_plan_required_flag IS NOT NULL
2860 THEN
2861 IF NOT OE_CNCL_Validate.Dep_Plan_Required(p_x_line_rec.dep_plan_required_flag) THEN
2862 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2863 p_x_line_rec.dep_plan_required_flag := NULL;
2864 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2865 p_x_line_rec.dep_plan_required_flag := FND_API.G_MISS_CHAR;
2866 ELSE
2867 x_return_status := FND_API.G_RET_STS_ERROR;
2868 END IF;
2869 END IF;
2870 END IF;
2871
2872 IF p_x_line_rec.end_item_unit_number IS NOT NULL
2873 THEN
2874 IF NOT OE_CNCL_Validate.End_Item_Unit_Number(p_x_line_rec.end_item_unit_number) THEN
2875 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2876 p_x_line_rec.end_item_unit_number := NULL;
2877 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2878 p_x_line_rec.end_item_unit_number := FND_API.G_MISS_CHAR;
2879 ELSE
2880 x_return_status := FND_API.G_RET_STS_ERROR;
2881 END IF;
2882 END IF;
2883 END IF;
2884
2885 IF p_x_line_rec.fob_point_code IS NOT NULL
2886 THEN
2887 IF NOT OE_CNCL_Validate.Fob_Point(p_x_line_rec.fob_point_code) THEN
2888 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2889 p_x_line_rec.fob_point_code := NULL;
2890 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2891 p_x_line_rec.fob_point_code := FND_API.G_MISS_CHAR;
2892 ELSE
2893 x_return_status := FND_API.G_RET_STS_ERROR;
2894 END IF;
2895 END IF;
2896 END IF;
2897
2898 IF p_x_line_rec.freight_terms_code IS NOT NULL
2899 THEN
2900 IF NOT OE_CNCL_Validate.Freight_Terms(p_x_line_rec.freight_terms_code) THEN
2901 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2902 p_x_line_rec.freight_terms_code := NULL;
2903 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2904 p_x_line_rec.freight_terms_code := FND_API.G_MISS_CHAR;
2905 ELSE
2906 x_return_status := FND_API.G_RET_STS_ERROR;
2907 END IF;
2908 END IF;
2909 END IF;
2910
2911 IF p_x_line_rec.invoice_to_contact_id IS NOT NULL
2912 THEN
2913 IF NOT OE_CNCL_Validate.Invoice_To_Contact(p_x_line_rec.invoice_to_contact_id) THEN
2914 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2915 p_x_line_rec.invoice_to_contact_id := NULL;
2916 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2917 p_x_line_rec.invoice_to_contact_id := FND_API.G_MISS_NUM;
2918 ELSE
2919 x_return_status := FND_API.G_RET_STS_ERROR;
2920 END IF;
2921 END IF;
2922 END IF;
2923
2924 IF p_x_line_rec.invoice_to_org_id IS NOT NULL
2925 THEN
2926 IF NOT OE_CNCL_Validate.Invoice_To_Org(p_x_line_rec.invoice_to_org_id) THEN
2927 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2928 p_x_line_rec.invoice_to_org_id := NULL;
2929 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2930 p_x_line_rec.invoice_to_org_id := FND_API.G_MISS_NUM;
2931 ELSE
2932 x_return_status := FND_API.G_RET_STS_ERROR;
2933 END IF;
2934 END IF;
2935 END IF;
2936
2937 IF p_x_line_rec.invoicing_rule_id IS NOT NULL
2938 THEN
2939 IF NOT OE_CNCL_Validate.Invoicing_Rule(p_x_line_rec.invoicing_rule_id) THEN
2940 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2941 p_x_line_rec.invoicing_rule_id := NULL;
2942 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2943 p_x_line_rec.invoicing_rule_id := FND_API.G_MISS_NUM;
2944 ELSE
2945 x_return_status := FND_API.G_RET_STS_ERROR;
2946 END IF;
2947 END IF;
2948 END IF;
2949
2950 --{added for bug 4240715
2951 IF p_x_line_rec.Ib_owner IS NOT NULL
2952 THEN
2953 IF NOT OE_CNCL_Validate.IB_OWNER(p_x_line_rec.Ib_owner) THEN
2954
2955 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2956 p_x_line_rec.Ib_owner := NULL;
2957 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
2958 THEN
2959 p_x_line_rec.Ib_Owner := FND_API.G_MISS_CHAR;
2960 ELSE
2961 x_return_status := FND_API.G_RET_STS_ERROR;
2962 END IF;
2963 END IF;
2964 END IF;
2965
2966 IF p_x_line_rec.Ib_installed_at_location IS NOT NULL
2967 THEN
2968 IF NOT OE_CNCL_Validate.IB_INSTALLED_AT_LOCATION(p_x_line_rec.Ib_installed_at_location) THEN
2969 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
2970 THEN
2971 p_x_line_rec.Ib_installed_at_location := NULL;
2972 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
2973 THEN
2974 p_x_line_rec.Ib_installed_at_location := FND_API.G_MISS_CHAR;
2975 ELSE
2976 x_return_status := FND_API.G_RET_STS_ERROR;
2977 END IF;
2978 END IF;
2979
2980 END IF;
2981
2982 IF p_x_line_rec.Ib_current_location IS NOT NULL
2983 THEN
2984
2985 IF NOT OE_CNCL_Validate.IB_CURRENT_LOCATION(p_x_line_rec.ib_current_location) THEN
2986 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
2987 THEN
2988 p_x_line_rec.Ib_current_location := NULL;
2989 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
2990 THEN
2991 p_x_line_rec.Ib_current_location := FND_API.G_MISS_CHAR;
2992 ELSE
2993 x_return_status := FND_API.G_RET_STS_ERROR;
2994 END IF;
2995 END IF;
2996 END IF;
2997
2998
2999 IF p_x_line_rec.End_customer_id IS NOT NULL THEN
3000
3001 IF NOT OE_CNCL_Validate.END_CUSTOMER(p_x_line_rec.End_customer_id) THEN
3002 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
3003 THEN
3004 p_x_line_rec.End_customer_id := NULL;
3005 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
3006 THEN
3007 p_x_line_rec.End_customer_id := FND_API.G_MISS_NUM;
3008 ELSE
3009 x_return_status := FND_API.G_RET_STS_ERROR;
3010 END IF;
3011 END IF;
3012
3013 END IF;
3014
3015
3016
3017 IF p_x_line_rec.End_customer_contact_id IS NOT NULL THEN
3018
3019 IF NOT OE_CNCL_Validate.END_CUSTOMER_CONTACT(p_x_line_rec.End_customer_contact_id) THEN
3020 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
3021 THEN
3022 p_x_line_rec.End_customer_contact_id := NULL;
3023 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
3024 THEN
3025 p_x_line_rec.End_customer_contact_id := FND_API.G_MISS_NUM;
3026 ELSE
3027 x_return_status := FND_API.G_RET_STS_ERROR;
3028 END IF;
3029 END IF;
3030 END IF;
3031
3032 IF p_x_line_rec.End_customer_site_use_id IS NOT NULL
3033 THEN
3034 IF NOT OE_CNCL_Validate.END_CUSTOMER_SITE_USE(p_x_line_rec.End_customer_site_use_id) THEN
3035 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
3036 THEN
3037 p_x_line_rec.End_customer_site_use_id := NULL;
3038 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
3039 THEN
3040 p_x_line_rec.End_customer_site_use_id := FND_API.G_MISS_NUM;
3041 ELSE
3042 x_return_status := FND_API.G_RET_STS_ERROR;
3043 END IF;
3044 END IF;
3045 END IF;
3046 -- bug 4240715}
3047
3048 IF p_x_line_rec.item_type_code IS NOT NULL
3049 THEN
3050 IF NOT OE_CNCL_Validate.Item_Type(p_x_line_rec.item_type_code) THEN
3051 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3052 p_x_line_rec.item_type_code := NULL;
3053 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3054 p_x_line_rec.item_type_code := FND_API.G_MISS_CHAR;
3055 ELSE
3056 x_return_status := FND_API.G_RET_STS_ERROR;
3057 END IF;
3058 END IF;
3059 END IF;
3060
3061 IF p_x_line_rec.payment_term_id IS NOT NULL
3062 THEN
3063 IF NOT OE_CNCL_Validate.Payment_Term(p_x_line_rec.payment_term_id) THEN
3064 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3065 p_x_line_rec.payment_term_id := NULL;
3066 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3067 p_x_line_rec.payment_term_id := FND_API.G_MISS_NUM;
3068 ELSE
3069 x_return_status := FND_API.G_RET_STS_ERROR;
3070 END IF;
3071 END IF;
3072 END IF;
3073
3074 IF p_x_line_rec.price_list_id IS NOT NULL
3075 THEN
3076 IF NOT OE_CNCL_Validate.Price_List(p_x_line_rec.price_list_id) THEN
3077 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3078 p_x_line_rec.price_list_id := NULL;
3079 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3080 p_x_line_rec.price_list_id := FND_API.G_MISS_NUM;
3081 ELSE
3082 x_return_status := FND_API.G_RET_STS_ERROR;
3083 END IF;
3084 END IF;
3085 END IF;
3086
3087 IF p_x_line_rec.project_id IS NOT NULL
3088 THEN
3089 IF NOT OE_CNCL_Validate.Project(p_x_line_rec.project_id) THEN
3090 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3091 p_x_line_rec.project_id := NULL;
3092 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3093 p_x_line_rec.project_id := FND_API.G_MISS_NUM;
3094 ELSE
3095 x_return_status := FND_API.G_RET_STS_ERROR;
3096 END IF;
3097 END IF;
3098 END IF;
3099
3100 IF p_x_line_rec.shipment_priority_code IS NOT NULL
3101 THEN
3102 IF NOT OE_CNCL_Validate.Shipment_Priority(p_x_line_rec.shipment_priority_code) THEN
3103 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3104 p_x_line_rec.shipment_priority_code := NULL;
3105 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3106 p_x_line_rec.shipment_priority_code := FND_API.G_MISS_CHAR;
3107 ELSE
3108 x_return_status := FND_API.G_RET_STS_ERROR;
3109 END IF;
3110 END IF;
3111 END IF;
3112
3113 IF p_x_line_rec.shipping_method_code IS NOT NULL
3114 THEN
3115 IF NOT OE_CNCL_Validate.Shipping_Method(p_x_line_rec.shipping_method_code) THEN
3116 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3117 p_x_line_rec.shipping_method_code := NULL;
3118 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3119 p_x_line_rec.shipping_method_code := FND_API.G_MISS_CHAR;
3120 ELSE
3121 x_return_status := FND_API.G_RET_STS_ERROR;
3122 END IF;
3123 END IF;
3124 END IF;
3125
3126 IF p_x_line_rec.ship_from_org_id IS NOT NULL
3127 THEN
3128 IF NOT OE_CNCL_Validate.Ship_From_Org(p_x_line_rec.ship_from_org_id) THEN
3129 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3130 p_x_line_rec.ship_from_org_id := NULL;
3131 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3132 p_x_line_rec.ship_from_org_id := FND_API.G_MISS_NUM;
3133 ELSE
3134 x_return_status := FND_API.G_RET_STS_ERROR;
3135 END IF;
3136 END IF;
3137 END IF;
3138
3139 IF p_x_line_rec.shipping_interfaced_flag IS NOT NULL
3140 THEN
3141 IF NOT OE_CNCL_Validate.Shipping_Interfaced(p_x_line_rec.shipping_interfaced_flag) THEN
3142 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3143 p_x_line_rec.shipping_interfaced_flag := NULL;
3144 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3145 p_x_line_rec.shipping_interfaced_flag := FND_API.G_MISS_CHAR;
3146 ELSE
3147 x_return_status := FND_API.G_RET_STS_ERROR;
3148 END IF;
3149 END IF;
3150 END IF;
3151
3152 IF p_x_line_rec.shippable_flag IS NOT NULL
3153 THEN
3154 IF NOT OE_CNCL_Validate.shippable(p_x_line_rec.shippable_flag) THEN
3155 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3156 p_x_line_rec.shippable_flag := NULL;
3157 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3158 p_x_line_rec.shippable_flag := FND_API.G_MISS_CHAR;
3159 ELSE
3160 x_return_status := FND_API.G_RET_STS_ERROR;
3161 END IF;
3162 END IF;
3163 END IF;
3164
3165 IF p_x_line_rec.ship_to_contact_id IS NOT NULL
3166 THEN
3167 IF NOT OE_CNCL_Validate.Ship_To_Contact(p_x_line_rec.ship_to_contact_id) THEN
3168 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3169 p_x_line_rec.ship_to_contact_id := NULL;
3170 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3171 p_x_line_rec.ship_to_contact_id := FND_API.G_MISS_NUM;
3172 ELSE
3173 x_return_status := FND_API.G_RET_STS_ERROR;
3174 END IF;
3175 END IF;
3176 END IF;
3177
3178 IF p_x_line_rec.ship_to_org_id IS NOT NULL
3179 THEN
3180 IF NOT OE_CNCL_Validate.Ship_To_Org(p_x_line_rec.ship_to_org_id) THEN
3181 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3182 p_x_line_rec.ship_to_org_id := NULL;
3183 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3184 p_x_line_rec.ship_to_org_id := FND_API.G_MISS_NUM;
3185 ELSE
3186 x_return_status := FND_API.G_RET_STS_ERROR;
3187 END IF;
3188 END IF;
3189 END IF;
3190
3191 IF p_x_line_rec.sold_to_org_id IS NOT NULL
3192 THEN
3193 IF NOT OE_CNCL_Validate.Sold_To_Org(p_x_line_rec.sold_to_org_id) THEN
3194 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3195 p_x_line_rec.sold_to_org_id := NULL;
3196 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3197 p_x_line_rec.sold_to_org_id := FND_API.G_MISS_NUM;
3198 ELSE
3199 x_return_status := FND_API.G_RET_STS_ERROR;
3200 END IF;
3201 END IF;
3202 END IF;
3203
3204 IF p_x_line_rec.source_type_code IS NOT NULL
3205 THEN
3206 IF NOT OE_CNCL_Validate.Source_Type(p_x_line_rec.source_type_code) THEN
3207 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3208 p_x_line_rec.source_type_code := NULL;
3209 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3210 p_x_line_rec.source_type_code := FND_API.G_MISS_CHAR;
3211 ELSE
3212 x_return_status := FND_API.G_RET_STS_ERROR;
3213 END IF;
3214 END IF;
3215 END IF;
3216
3217 IF p_x_line_rec.tax_exempt_flag IS NOT NULL
3218 THEN
3219 IF NOT OE_CNCL_Validate.Tax_Exempt(p_x_line_rec.tax_exempt_flag) THEN
3220 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3221 p_x_line_rec.tax_exempt_flag := NULL;
3222 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3223 p_x_line_rec.tax_exempt_flag := FND_API.G_MISS_CHAR;
3224 ELSE
3225 x_return_status := FND_API.G_RET_STS_ERROR;
3226 END IF;
3227 END IF;
3228 END IF;
3229
3230 IF p_x_line_rec.tax_exempt_reason_code IS NOT NULL
3231 THEN
3232 IF NOT OE_CNCL_Validate.Tax_Exempt_Reason(p_x_line_rec.tax_exempt_reason_code) THEN
3233 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3234 p_x_line_rec.tax_exempt_reason_code := NULL;
3235 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3236 p_x_line_rec.tax_exempt_reason_code := FND_API.G_MISS_CHAR;
3237 ELSE
3238 x_return_status := FND_API.G_RET_STS_ERROR;
3239 END IF;
3240 END IF;
3241 END IF;
3242
3243 IF p_x_line_rec.tax_point_code IS NOT NULL
3244 THEN
3245 IF NOT OE_CNCL_Validate.Tax_Point(p_x_line_rec.tax_point_code) THEN
3246 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3247 p_x_line_rec.tax_point_code := NULL;
3248 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3249 p_x_line_rec.tax_point_code := FND_API.G_MISS_CHAR;
3250 ELSE
3251 x_return_status := FND_API.G_RET_STS_ERROR;
3252 END IF;
3253 END IF;
3254 END IF;
3255
3256 IF p_x_line_rec.fulfilled_flag IS NOT NULL
3257 THEN
3258 IF NOT OE_CNCL_Validate.fulfilled(p_x_line_rec.fulfilled_flag) THEN
3259 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3260 p_x_line_rec.fulfilled_flag := NULL;
3261 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3262 p_x_line_rec.fulfilled_flag := FND_API.G_MISS_CHAR;
3263 ELSE
3264 x_return_status := FND_API.G_RET_STS_ERROR;
3265 END IF;
3266 END IF;
3267 END IF;
3268
3269 IF p_x_line_rec.flow_status_code IS NOT NULL
3270 THEN
3271 IF NOT OE_CNCL_Validate.Line_Flow_Status(p_x_line_rec.flow_status_code) THEN
3272 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3273 p_x_line_rec.flow_status_code := NULL;
3274 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3275 p_x_line_rec.flow_status_code := FND_API.G_MISS_CHAR;
3276 ELSE
3277 x_return_status := FND_API.G_RET_STS_ERROR;
3278 END IF;
3279 END IF;
3280 END IF;
3281
3282 oe_debug_pub.add('before flex: '||x_return_status,2);
3283 IF p_x_line_rec.attribute1 IS NOT NULL
3284 OR p_x_line_rec.attribute10 IS NOT NULL
3285 OR p_x_line_rec.attribute11 IS NOT NULL
3286 OR p_x_line_rec.attribute12 IS NOT NULL
3287 OR p_x_line_rec.attribute13 IS NOT NULL
3288 OR p_x_line_rec.attribute14 IS NOT NULL
3289 OR p_x_line_rec.attribute15 IS NOT NULL
3290 OR p_x_line_rec.attribute16 IS NOT NULL --For bug 2184255
3291 OR p_x_line_rec.attribute17 IS NOT NULL
3292 OR p_x_line_rec.attribute18 IS NOT NULL
3293 OR p_x_line_rec.attribute19 IS NOT NULL
3294 OR p_x_line_rec.attribute2 IS NOT NULL
3295 OR p_x_line_rec.attribute20 IS NOT NULL
3296 OR p_x_line_rec.attribute3 IS NOT NULL
3297 OR p_x_line_rec.attribute4 IS NOT NULL
3298 OR p_x_line_rec.attribute5 IS NOT NULL
3299 OR p_x_line_rec.attribute6 IS NOT NULL
3300 OR p_x_line_rec.attribute7 IS NOT NULL
3301 OR p_x_line_rec.attribute8 IS NOT NULL
3302 OR p_x_line_rec.attribute9 IS NOT NULL
3303 OR p_x_line_rec.context IS NOT NULL
3304 THEN
3305
3306 oe_debug_pub.add('Before calling line_desc_flex',2);
3307 IF NOT OE_CNCL_Validate.Line_Desc_Flex
3308 (p_context => p_x_line_rec.context
3309 ,p_attribute1 => p_x_line_rec.attribute1
3310 ,p_attribute2 => p_x_line_rec.attribute2
3311 ,p_attribute3 => p_x_line_rec.attribute3
3312 ,p_attribute4 => p_x_line_rec.attribute4
3313 ,p_attribute5 => p_x_line_rec.attribute5
3314 ,p_attribute6 => p_x_line_rec.attribute6
3315 ,p_attribute7 => p_x_line_rec.attribute7
3316 ,p_attribute8 => p_x_line_rec.attribute8
3317 ,p_attribute9 => p_x_line_rec.attribute9
3318 ,p_attribute10 => p_x_line_rec.attribute10
3319 ,p_attribute11 => p_x_line_rec.attribute11
3320 ,p_attribute12 => p_x_line_rec.attribute12
3321 ,p_attribute13 => p_x_line_rec.attribute13
3322 ,p_attribute14 => p_x_line_rec.attribute14
3323 ,p_attribute15 => p_x_line_rec.attribute15
3324 ,p_attribute16 => p_x_line_rec.attribute16 -- for bug 2184255
3325 ,p_attribute17 => p_x_line_rec.attribute17
3326 ,p_attribute18 => p_x_line_rec.attribute18
3327 ,p_attribute19 => p_x_line_rec.attribute19
3328 ,p_attribute20 => p_x_line_rec.attribute20) THEN
3329
3330 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3331
3332
3333 p_x_line_rec.context := null;
3334 p_x_line_rec.attribute1 := null;
3335 p_x_line_rec.attribute2 := null;
3336 p_x_line_rec.attribute3 := null;
3337 p_x_line_rec.attribute4 := null;
3338 p_x_line_rec.attribute5 := null;
3339 p_x_line_rec.attribute6 := null;
3340 p_x_line_rec.attribute7 := null;
3341 p_x_line_rec.attribute8 := null;
3342 p_x_line_rec.attribute9 := null;
3343 p_x_line_rec.attribute10 := null;
3344 p_x_line_rec.attribute11 := null;
3345 p_x_line_rec.attribute12 := null;
3346 p_x_line_rec.attribute13 := null;
3347 p_x_line_rec.attribute14 := null;
3348 p_x_line_rec.attribute15 := null;
3349 p_x_line_rec.attribute16 := null; -- for bug 2184255
3350 p_x_line_rec.attribute17 := null;
3351 p_x_line_rec.attribute18 := null;
3352 p_x_line_rec.attribute19 := null;
3353 p_x_line_rec.attribute20 := null;
3354
3355 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3356 p_x_line_rec.context := FND_API.G_MISS_CHAR;
3357 p_x_line_rec.attribute1 := FND_API.G_MISS_CHAR;
3358 p_x_line_rec.attribute2 := FND_API.G_MISS_CHAR;
3359 p_x_line_rec.attribute3 := FND_API.G_MISS_CHAR;
3360 p_x_line_rec.attribute4 := FND_API.G_MISS_CHAR;
3361 p_x_line_rec.attribute5 := FND_API.G_MISS_CHAR;
3362 p_x_line_rec.attribute6 := FND_API.G_MISS_CHAR;
3363 p_x_line_rec.attribute7 := FND_API.G_MISS_CHAR;
3364 p_x_line_rec.attribute8 := FND_API.G_MISS_CHAR;
3365 p_x_line_rec.attribute9 := FND_API.G_MISS_CHAR;
3366 p_x_line_rec.attribute10 := FND_API.G_MISS_CHAR;
3367 p_x_line_rec.attribute11 := FND_API.G_MISS_CHAR;
3368 p_x_line_rec.attribute12 := FND_API.G_MISS_CHAR;
3369 p_x_line_rec.attribute13 := FND_API.G_MISS_CHAR;
3370 p_x_line_rec.attribute14 := FND_API.G_MISS_CHAR;
3371 p_x_line_rec.attribute15 := FND_API.G_MISS_CHAR;
3372 p_x_line_rec.attribute16 := FND_API.G_MISS_CHAR; -- for bug 2184255
3373 p_x_line_rec.attribute17 := FND_API.G_MISS_CHAR;
3374 p_x_line_rec.attribute18 := FND_API.G_MISS_CHAR;
3375 p_x_line_rec.attribute19 := FND_API.G_MISS_CHAR;
3376 p_x_line_rec.attribute20 := FND_API.G_MISS_CHAR;
3377 ELSE
3378
3379 x_return_status := FND_API.G_RET_STS_ERROR;
3380 END IF;
3381 END IF;
3382
3383 oe_debug_pub.add('After line_desc_flex ' || x_return_status,2);
3384
3385
3386
3387 END IF;
3388
3389
3390 IF p_x_line_rec.global_attribute1 IS NOT NULL
3391 OR p_x_line_rec.global_attribute10 IS NOT NULL
3392 OR p_x_line_rec.global_attribute11 IS NOT NULL
3393 OR p_x_line_rec.global_attribute12 IS NOT NULL
3394 OR p_x_line_rec.global_attribute13 IS NOT NULL
3395 OR p_x_line_rec.global_attribute14 IS NOT NULL
3396 OR p_x_line_rec.global_attribute15 IS NOT NULL
3397 OR p_x_line_rec.global_attribute16 IS NOT NULL
3398 OR p_x_line_rec.global_attribute17 IS NOT NULL
3399 OR p_x_line_rec.global_attribute18 IS NOT NULL
3400 OR p_x_line_rec.global_attribute19 IS NOT NULL
3401 OR p_x_line_rec.global_attribute2 IS NOT NULL
3402 OR p_x_line_rec.global_attribute20 IS NOT NULL
3403 OR p_x_line_rec.global_attribute3 IS NOT NULL
3404 OR p_x_line_rec.global_attribute4 IS NOT NULL
3405 OR p_x_line_rec.global_attribute5 IS NOT NULL
3406 OR p_x_line_rec.global_attribute6 IS NOT NULL
3407 OR p_x_line_rec.global_attribute7 IS NOT NULL
3408 OR p_x_line_rec.global_attribute8 IS NOT NULL
3409 OR p_x_line_rec.global_attribute9 IS NOT NULL
3410 OR p_x_line_rec.global_attribute_category IS NOT NULL
3411 THEN
3412
3413
3414
3415 OE_DEBUG_PUB.ADD('Before G_line_desc_flex',2);
3416 IF NOT OE_CNCL_Validate.G_Line_Desc_Flex
3417 (p_context => p_x_line_rec.global_attribute_category
3418 ,p_attribute1 => p_x_line_rec.global_attribute1
3419 ,p_attribute2 => p_x_line_rec.global_attribute2
3420 ,p_attribute3 => p_x_line_rec.global_attribute3
3421 ,p_attribute4 => p_x_line_rec.global_attribute4
3422 ,p_attribute5 => p_x_line_rec.global_attribute5
3423 ,p_attribute6 => p_x_line_rec.global_attribute6
3424 ,p_attribute7 => p_x_line_rec.global_attribute7
3425 ,p_attribute8 => p_x_line_rec.global_attribute8
3426 ,p_attribute9 => p_x_line_rec.global_attribute9
3427 ,p_attribute10 => p_x_line_rec.global_attribute10
3428 ,p_attribute11 => p_x_line_rec.global_attribute11
3429 ,p_attribute12 => p_x_line_rec.global_attribute12
3430 ,p_attribute13 => p_x_line_rec.global_attribute13
3431 ,p_attribute14 => p_x_line_rec.global_attribute13
3432 ,p_attribute15 => p_x_line_rec.global_attribute14
3433 ,p_attribute16 => p_x_line_rec.global_attribute16
3434 ,p_attribute17 => p_x_line_rec.global_attribute17
3435 ,p_attribute18 => p_x_line_rec.global_attribute18
3436 ,p_attribute19 => p_x_line_rec.global_attribute19
3437 ,p_attribute20 => p_x_line_rec.global_attribute20) THEN
3438
3439 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3440
3441 p_x_line_rec.global_attribute_category := null;
3442 p_x_line_rec.global_attribute1 := null;
3443 p_x_line_rec.global_attribute2 := null;
3444 p_x_line_rec.global_attribute3 := null;
3445 p_x_line_rec.global_attribute4 := null;
3446 p_x_line_rec.global_attribute5 := null;
3447 p_x_line_rec.global_attribute6 := null;
3448 p_x_line_rec.global_attribute7 := null;
3449 p_x_line_rec.global_attribute8 := null;
3450 p_x_line_rec.global_attribute9 := null;
3451 p_x_line_rec.global_attribute11 := null;
3452 p_x_line_rec.global_attribute12 := null;
3453 p_x_line_rec.global_attribute13 := null;
3454 p_x_line_rec.global_attribute14 := null;
3455 p_x_line_rec.global_attribute15 := null;
3456 p_x_line_rec.global_attribute16 := null;
3457 p_x_line_rec.global_attribute17 := null;
3458 p_x_line_rec.global_attribute18 := null;
3459 p_x_line_rec.global_attribute19 := null;
3460 p_x_line_rec.global_attribute20 := null;
3461
3462 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3463 p_x_line_rec.global_attribute_category := FND_API.G_MISS_CHAR;
3464 p_x_line_rec.global_attribute1 := FND_API.G_MISS_CHAR;
3465 p_x_line_rec.global_attribute2 := FND_API.G_MISS_CHAR;
3466 p_x_line_rec.global_attribute3 := FND_API.G_MISS_CHAR;
3467 p_x_line_rec.global_attribute4 := FND_API.G_MISS_CHAR;
3468 p_x_line_rec.global_attribute5 := FND_API.G_MISS_CHAR;
3469 p_x_line_rec.global_attribute6 := FND_API.G_MISS_CHAR;
3470 p_x_line_rec.global_attribute7 := FND_API.G_MISS_CHAR;
3471 p_x_line_rec.global_attribute8 := FND_API.G_MISS_CHAR;
3472 p_x_line_rec.global_attribute9 := FND_API.G_MISS_CHAR;
3473 p_x_line_rec.global_attribute11 := FND_API.G_MISS_CHAR;
3474 p_x_line_rec.global_attribute12 := FND_API.G_MISS_CHAR;
3475 p_x_line_rec.global_attribute13 := FND_API.G_MISS_CHAR;
3476 p_x_line_rec.global_attribute14 := FND_API.G_MISS_CHAR;
3477 p_x_line_rec.global_attribute15 := FND_API.G_MISS_CHAR;
3478 p_x_line_rec.global_attribute16 := FND_API.G_MISS_CHAR;
3479 p_x_line_rec.global_attribute17 := FND_API.G_MISS_CHAR;
3480 p_x_line_rec.global_attribute18 := FND_API.G_MISS_CHAR;
3481 p_x_line_rec.global_attribute19 := FND_API.G_MISS_CHAR;
3482 p_x_line_rec.global_attribute20 := FND_API.G_MISS_CHAR;
3483
3484 ELSE
3485
3486 x_return_status := FND_API.G_RET_STS_ERROR;
3487 END IF;
3488 END IF;
3489
3490 OE_DEBUG_PUB.ADD('After G_Line_desc_flex ' || x_return_status,2);
3491
3492 END IF;
3493
3494 IF p_x_line_rec.industry_attribute1 IS NOT NULL
3495 OR p_x_line_rec.industry_attribute10 IS NOT NULL
3496 OR p_x_line_rec.industry_attribute11 IS NOT NULL
3497 OR p_x_line_rec.industry_attribute12 IS NOT NULL
3498 OR p_x_line_rec.industry_attribute13 IS NOT NULL
3499 OR p_x_line_rec.industry_attribute14 IS NOT NULL
3500 OR p_x_line_rec.industry_attribute15 IS NOT NULL
3501 OR p_x_line_rec.industry_attribute16 IS NOT NULL
3502 OR p_x_line_rec.industry_attribute17 IS NOT NULL
3503 OR p_x_line_rec.industry_attribute18 IS NOT NULL
3504 OR p_x_line_rec.industry_attribute19 IS NOT NULL
3505 OR p_x_line_rec.industry_attribute2 IS NOT NULL
3506 OR p_x_line_rec.industry_attribute20 IS NOT NULL
3507 OR p_x_line_rec.industry_attribute21 IS NOT NULL
3508 OR p_x_line_rec.industry_attribute22 IS NOT NULL
3509 OR p_x_line_rec.industry_attribute23 IS NOT NULL
3510 OR p_x_line_rec.industry_attribute24 IS NOT NULL
3511 OR p_x_line_rec.industry_attribute25 IS NOT NULL
3512 OR p_x_line_rec.industry_attribute26 IS NOT NULL
3513 OR p_x_line_rec.industry_attribute27 IS NOT NULL
3514 OR p_x_line_rec.industry_attribute28 IS NOT NULL
3515 OR p_x_line_rec.industry_attribute29 IS NOT NULL
3516 OR p_x_line_rec.industry_attribute3 IS NOT NULL
3517 OR p_x_line_rec.industry_attribute30 IS NOT NULL
3518 OR p_x_line_rec.industry_attribute4 IS NOT NULL
3519 OR p_x_line_rec.industry_attribute5 IS NOT NULL
3520 OR p_x_line_rec.industry_attribute6 IS NOT NULL
3521 OR p_x_line_rec.industry_attribute7 IS NOT NULL
3522 OR p_x_line_rec.industry_attribute8 IS NOT NULL
3523 OR p_x_line_rec.industry_attribute9 IS NOT NULL
3524 OR p_x_line_rec.industry_context IS NOT NULL
3525 THEN
3526
3527
3528 IF NOT OE_CNCL_Validate.I_Line_Desc_Flex
3529 (p_context => p_x_line_rec.Industry_context
3530 ,p_attribute1 => p_x_line_rec.Industry_attribute1
3531 ,p_attribute2 => p_x_line_rec.Industry_attribute2
3532 ,p_attribute3 => p_x_line_rec.Industry_attribute3
3533 ,p_attribute4 => p_x_line_rec.Industry_attribute4
3534 ,p_attribute5 => p_x_line_rec.Industry_attribute5
3535 ,p_attribute6 => p_x_line_rec.Industry_attribute6
3536 ,p_attribute7 => p_x_line_rec.Industry_attribute7
3537 ,p_attribute8 => p_x_line_rec.Industry_attribute8
3538 ,p_attribute9 => p_x_line_rec.Industry_attribute9
3539 ,p_attribute10 => p_x_line_rec.Industry_attribute10
3540 ,p_attribute11 => p_x_line_rec.Industry_attribute11
3541 ,p_attribute12 => p_x_line_rec.Industry_attribute12
3542 ,p_attribute13 => p_x_line_rec.Industry_attribute13
3543 ,p_attribute14 => p_x_line_rec.Industry_attribute14
3544 ,p_attribute15 => p_x_line_rec.Industry_attribute15
3545 ,p_attribute16 => p_x_line_rec.Industry_attribute16
3546 ,p_attribute17 => p_x_line_rec.Industry_attribute17
3547 ,p_attribute18 => p_x_line_rec.Industry_attribute18
3548 ,p_attribute19 => p_x_line_rec.Industry_attribute19
3549 ,p_attribute20 => p_x_line_rec.Industry_attribute20
3550 ,p_attribute21 => p_x_line_rec.Industry_attribute21
3551 ,p_attribute22 => p_x_line_rec.Industry_attribute22
3552 ,p_attribute23 => p_x_line_rec.Industry_attribute23
3553 ,p_attribute24 => p_x_line_rec.Industry_attribute24
3554 ,p_attribute25 => p_x_line_rec.Industry_attribute25
3555 ,p_attribute26 => p_x_line_rec.Industry_attribute26
3556 ,p_attribute27 => p_x_line_rec.Industry_attribute27
3557 ,p_attribute28 => p_x_line_rec.Industry_attribute28
3558 ,p_attribute29 => p_x_line_rec.Industry_attribute29
3559 ,p_attribute30 => p_x_line_rec.Industry_attribute30) THEN
3560
3561 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3562
3563
3564 p_x_line_rec.Industry_context := null;
3565 p_x_line_rec.Industry_attribute1 := null;
3566 p_x_line_rec.Industry_attribute2 := null;
3567 p_x_line_rec.Industry_attribute3 := null;
3568 p_x_line_rec.Industry_attribute4 := null;
3569 p_x_line_rec.Industry_attribute5 := null;
3570 p_x_line_rec.Industry_attribute6 := null;
3571 p_x_line_rec.Industry_attribute7 := null;
3572 p_x_line_rec.Industry_attribute8 := null;
3573 p_x_line_rec.Industry_attribute9 := null;
3574 p_x_line_rec.Industry_attribute10 := null;
3575 p_x_line_rec.Industry_attribute11 := null;
3576 p_x_line_rec.Industry_attribute12 := null;
3577 p_x_line_rec.Industry_attribute13 := null;
3578 p_x_line_rec.Industry_attribute14 := null;
3579 p_x_line_rec.Industry_attribute15 := null;
3580 p_x_line_rec.Industry_attribute16 := null;
3581 p_x_line_rec.Industry_attribute17 := null;
3582 p_x_line_rec.Industry_attribute18 := null;
3583 p_x_line_rec.Industry_attribute19 := null;
3584 p_x_line_rec.Industry_attribute20 := null;
3585 p_x_line_rec.Industry_attribute21 := null;
3586 p_x_line_rec.Industry_attribute22 := null;
3587 p_x_line_rec.Industry_attribute23 := null;
3588 p_x_line_rec.Industry_attribute24 := null;
3589 p_x_line_rec.Industry_attribute25 := null;
3590 p_x_line_rec.Industry_attribute26 := null;
3591 p_x_line_rec.Industry_attribute27 := null;
3592 p_x_line_rec.Industry_attribute28 := null;
3593 p_x_line_rec.Industry_attribute29 := null;
3594 p_x_line_rec.Industry_attribute30 := null;
3595
3596 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3597
3598 p_x_line_rec.Industry_context := FND_API.G_MISS_CHAR;
3599 p_x_line_rec.Industry_attribute1 := FND_API.G_MISS_CHAR;
3600 p_x_line_rec.Industry_attribute2 := FND_API.G_MISS_CHAR;
3601 p_x_line_rec.Industry_attribute3 := FND_API.G_MISS_CHAR;
3602 p_x_line_rec.Industry_attribute4 := FND_API.G_MISS_CHAR;
3603 p_x_line_rec.Industry_attribute5 := FND_API.G_MISS_CHAR;
3604 p_x_line_rec.Industry_attribute6 := FND_API.G_MISS_CHAR;
3605 p_x_line_rec.Industry_attribute7 := FND_API.G_MISS_CHAR;
3606 p_x_line_rec.Industry_attribute8 := FND_API.G_MISS_CHAR;
3607 p_x_line_rec.Industry_attribute9 := FND_API.G_MISS_CHAR;
3608 p_x_line_rec.Industry_attribute10 := FND_API.G_MISS_CHAR;
3609 p_x_line_rec.Industry_attribute11 := FND_API.G_MISS_CHAR;
3610 p_x_line_rec.Industry_attribute12 := FND_API.G_MISS_CHAR;
3611 p_x_line_rec.Industry_attribute13 := FND_API.G_MISS_CHAR;
3612 p_x_line_rec.Industry_attribute14 := FND_API.G_MISS_CHAR;
3613 p_x_line_rec.Industry_attribute15 := FND_API.G_MISS_CHAR;
3614 p_x_line_rec.Industry_attribute16 := FND_API.G_MISS_CHAR;
3615 p_x_line_rec.Industry_attribute17 := FND_API.G_MISS_CHAR;
3616 p_x_line_rec.Industry_attribute18 := FND_API.G_MISS_CHAR;
3617 p_x_line_rec.Industry_attribute19 := FND_API.G_MISS_CHAR;
3618 p_x_line_rec.Industry_attribute20 := FND_API.G_MISS_CHAR;
3619 p_x_line_rec.Industry_attribute21 := FND_API.G_MISS_CHAR;
3620 p_x_line_rec.Industry_attribute22 := FND_API.G_MISS_CHAR;
3621 p_x_line_rec.Industry_attribute23 := FND_API.G_MISS_CHAR;
3622 p_x_line_rec.Industry_attribute24 := FND_API.G_MISS_CHAR;
3623 p_x_line_rec.Industry_attribute25 := FND_API.G_MISS_CHAR;
3624 p_x_line_rec.Industry_attribute26 := FND_API.G_MISS_CHAR;
3625 p_x_line_rec.Industry_attribute27 := FND_API.G_MISS_CHAR;
3626 p_x_line_rec.Industry_attribute28 := FND_API.G_MISS_CHAR;
3627 p_x_line_rec.Industry_attribute29 := FND_API.G_MISS_CHAR;
3628 p_x_line_rec.Industry_attribute30 := FND_API.G_MISS_CHAR;
3629 ELSE
3630
3631 x_return_status := FND_API.G_RET_STS_ERROR;
3632 END IF;
3633 END IF;
3634
3635 oe_debug_pub.add('After I_line_desc_flex ' || x_return_status,2);
3636
3637 END IF;
3638
3639 /* Trading Partner Attributes */
3640 IF p_x_line_rec.tp_attribute1 IS NOT NULL
3641 OR p_x_line_rec.tp_attribute2 IS NOT NULL
3642 OR p_x_line_rec.tp_attribute3 IS NOT NULL
3643 OR p_x_line_rec.tp_attribute4 IS NOT NULL
3644 OR p_x_line_rec.tp_attribute5 IS NOT NULL
3645 OR p_x_line_rec.tp_attribute6 IS NOT NULL
3646 OR p_x_line_rec.tp_attribute7 IS NOT NULL
3647 OR p_x_line_rec.tp_attribute8 IS NOT NULL
3648 OR p_x_line_rec.tp_attribute9 IS NOT NULL
3649 OR p_x_line_rec.tp_attribute10 IS NOT NULL
3650 OR p_x_line_rec.tp_attribute11 IS NOT NULL
3651 OR p_x_line_rec.tp_attribute12 IS NOT NULL
3652 OR p_x_line_rec.tp_attribute13 IS NOT NULL
3653 OR p_x_line_rec.tp_attribute14 IS NOT NULL
3654 OR p_x_line_rec.tp_attribute15 IS NOT NULL
3655
3656 THEN
3657
3658
3659 IF NOT OE_CNCL_Validate.TP_Line_Desc_Flex
3660 (p_context => p_x_line_rec.tp_context
3661 ,p_attribute1 => p_x_line_rec.tp_attribute1
3662 ,p_attribute2 => p_x_line_rec.tp_attribute2
3663 ,p_attribute3 => p_x_line_rec.tp_attribute3
3664 ,p_attribute4 => p_x_line_rec.tp_attribute4
3665 ,p_attribute5 => p_x_line_rec.tp_attribute5
3666 ,p_attribute6 => p_x_line_rec.tp_attribute6
3667 ,p_attribute7 => p_x_line_rec.tp_attribute7
3668 ,p_attribute8 => p_x_line_rec.tp_attribute8
3669 ,p_attribute9 => p_x_line_rec.tp_attribute9
3670 ,p_attribute10 => p_x_line_rec.tp_attribute10
3671 ,p_attribute11 => p_x_line_rec.tp_attribute11
3672 ,p_attribute12 => p_x_line_rec.tp_attribute12
3673 ,p_attribute13 => p_x_line_rec.tp_attribute13
3674 ,p_attribute14 => p_x_line_rec.tp_attribute14
3675 ,p_attribute15 => p_x_line_rec.tp_attribute15) THEN
3676
3677 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3678
3679
3680 p_x_line_rec.tp_context := null;
3681 p_x_line_rec.tp_attribute1 := null;
3682 p_x_line_rec.tp_attribute2 := null;
3683 p_x_line_rec.tp_attribute3 := null;
3684 p_x_line_rec.tp_attribute4 := null;
3685 p_x_line_rec.tp_attribute5 := null;
3686 p_x_line_rec.tp_attribute6 := null;
3687 p_x_line_rec.tp_attribute7 := null;
3688 p_x_line_rec.tp_attribute8 := null;
3689 p_x_line_rec.tp_attribute9 := null;
3690 p_x_line_rec.tp_attribute10 := null;
3691 p_x_line_rec.tp_attribute11 := null;
3692 p_x_line_rec.tp_attribute12 := null;
3693 p_x_line_rec.tp_attribute13 := null;
3694 p_x_line_rec.tp_attribute14 := null;
3695 p_x_line_rec.tp_attribute15 := null;
3696
3697 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3698
3699 p_x_line_rec.tp_context := FND_API.G_MISS_CHAR;
3700 p_x_line_rec.tp_attribute1 := FND_API.G_MISS_CHAR;
3701 p_x_line_rec.tp_attribute2 := FND_API.G_MISS_CHAR;
3702 p_x_line_rec.tp_attribute3 := FND_API.G_MISS_CHAR;
3703 p_x_line_rec.tp_attribute4 := FND_API.G_MISS_CHAR;
3704 p_x_line_rec.tp_attribute5 := FND_API.G_MISS_CHAR;
3705 p_x_line_rec.tp_attribute6 := FND_API.G_MISS_CHAR;
3706 p_x_line_rec.tp_attribute7 := FND_API.G_MISS_CHAR;
3707 p_x_line_rec.tp_attribute8 := FND_API.G_MISS_CHAR;
3708 p_x_line_rec.tp_attribute9 := FND_API.G_MISS_CHAR;
3709 p_x_line_rec.tp_attribute10 := FND_API.G_MISS_CHAR;
3710 p_x_line_rec.tp_attribute11 := FND_API.G_MISS_CHAR;
3711 p_x_line_rec.tp_attribute12 := FND_API.G_MISS_CHAR;
3712 p_x_line_rec.tp_attribute13 := FND_API.G_MISS_CHAR;
3713 p_x_line_rec.tp_attribute14 := FND_API.G_MISS_CHAR;
3714 p_x_line_rec.tp_attribute15 := FND_API.G_MISS_CHAR;
3715 ELSE
3716
3717 x_return_status := FND_API.G_RET_STS_ERROR;
3718 END IF;
3719 END IF;
3720
3721 --oe_debug_pub.add('After TP_line_desc_flex ' || x_return_status);
3722
3723 END IF;
3724 /* Trading Partner */
3725
3726
3727 IF p_x_line_rec.return_attribute1 IS NOT NULL
3728 OR p_x_line_rec.return_attribute10 IS NOT NULL
3729 OR p_x_line_rec.return_attribute11 IS NOT NULL
3730 OR p_x_line_rec.return_attribute12 IS NOT NULL
3731 OR p_x_line_rec.return_attribute13 IS NOT NULL
3732 OR p_x_line_rec.return_attribute14 IS NOT NULL
3733 OR p_x_line_rec.return_attribute15 IS NOT NULL
3734 OR p_x_line_rec.return_attribute2 IS NOT NULL
3735 OR p_x_line_rec.return_attribute3 IS NOT NULL
3736 OR p_x_line_rec.return_attribute4 IS NOT NULL
3737 OR p_x_line_rec.return_attribute5 IS NOT NULL
3738 OR p_x_line_rec.return_attribute6 IS NOT NULL
3739 OR p_x_line_rec.return_attribute7 IS NOT NULL
3740 OR p_x_line_rec.return_attribute8 IS NOT NULL
3741 OR p_x_line_rec.return_attribute9 IS NOT NULL
3742 OR p_x_line_rec.return_context IS NOT NULL
3743 THEN
3744
3745
3746 oe_debug_pub.add('Before calling Return line_desc_flex',2);
3747 IF NOT OE_CNCL_Validate.R_Line_Desc_Flex
3748 (p_context => p_x_line_rec.Return_context
3749 ,p_attribute1 => p_x_line_rec.Return_attribute1
3750 ,p_attribute2 => p_x_line_rec.Return_attribute2
3751 ,p_attribute3 => p_x_line_rec.Return_attribute3
3752 ,p_attribute4 => p_x_line_rec.Return_attribute4
3753 ,p_attribute5 => p_x_line_rec.Return_attribute5
3754 ,p_attribute6 => p_x_line_rec.Return_attribute6
3755 ,p_attribute7 => p_x_line_rec.Return_attribute7
3756 ,p_attribute8 => p_x_line_rec.Return_attribute8
3757 ,p_attribute9 => p_x_line_rec.Return_attribute9
3758 ,p_attribute10 => p_x_line_rec.Return_attribute10
3759 ,p_attribute11 => p_x_line_rec.Return_attribute11
3760 ,p_attribute12 => p_x_line_rec.Return_attribute12
3761 ,p_attribute13 => p_x_line_rec.Return_attribute13
3762 ,p_attribute14 => p_x_line_rec.Return_attribute14
3763 ,p_attribute15 => p_x_line_rec.Return_attribute15) THEN
3764
3765 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3766
3767
3768 p_x_line_rec.Return_context := null;
3769 p_x_line_rec.Return_attribute1 := null;
3770 p_x_line_rec.Return_attribute2 := null;
3771 p_x_line_rec.Return_attribute3 := null;
3772 p_x_line_rec.Return_attribute4 := null;
3773 p_x_line_rec.Return_attribute5 := null;
3774 p_x_line_rec.Return_attribute6 := null;
3775 p_x_line_rec.Return_attribute7 := null;
3776 p_x_line_rec.Return_attribute8 := null;
3777 p_x_line_rec.Return_attribute9 := null;
3778 p_x_line_rec.Return_attribute11 := null;
3779 p_x_line_rec.Return_attribute12 := null;
3780 p_x_line_rec.Return_attribute13 := null;
3781 p_x_line_rec.Return_attribute14 := null;
3782 p_x_line_rec.Return_attribute15 := null;
3783 p_x_line_rec.Return_attribute10 := null;
3784
3785 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3786 p_x_line_rec.Return_context := FND_API.G_MISS_CHAR;
3787 p_x_line_rec.Return_attribute1 := FND_API.G_MISS_CHAR;
3788 p_x_line_rec.Return_attribute2 := FND_API.G_MISS_CHAR;
3789 p_x_line_rec.Return_attribute3 := FND_API.G_MISS_CHAR;
3790 p_x_line_rec.Return_attribute4 := FND_API.G_MISS_CHAR;
3791 p_x_line_rec.Return_attribute5 := FND_API.G_MISS_CHAR;
3792 p_x_line_rec.Return_attribute6 := FND_API.G_MISS_CHAR;
3793 p_x_line_rec.Return_attribute7 := FND_API.G_MISS_CHAR;
3794 p_x_line_rec.Return_attribute8 := FND_API.G_MISS_CHAR;
3795 p_x_line_rec.Return_attribute9 := FND_API.G_MISS_CHAR;
3796 p_x_line_rec.Return_attribute11 := FND_API.G_MISS_CHAR;
3797 p_x_line_rec.Return_attribute12 := FND_API.G_MISS_CHAR;
3798 p_x_line_rec.Return_attribute13 := FND_API.G_MISS_CHAR;
3799 p_x_line_rec.Return_attribute14 := FND_API.G_MISS_CHAR;
3800 p_x_line_rec.Return_attribute15 := FND_API.G_MISS_CHAR;
3801 p_x_line_rec.Return_attribute10 := FND_API.G_MISS_CHAR;
3802 ELSE
3803
3804 x_return_status := FND_API.G_RET_STS_ERROR;
3805 END IF;
3806 END IF;
3807
3808 oe_debug_pub.add('After Return line_desc_flex ' || x_return_status,2);
3809
3810
3811
3812 END IF;
3813
3814 -- Done validating attributes
3815
3816 IF p_x_line_rec.salesrep_id IS NOT NULL
3817 THEN
3818 IF NOT OE_CNCL_Validate.salesrep(p_x_line_rec.salesrep_id) THEN
3819 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3820 p_x_line_rec.salesrep_id := NULL;
3821 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3822 p_x_line_rec.salesrep_id := FND_API.G_MISS_NUM;
3823 ELSE
3824 x_return_status := FND_API.G_RET_STS_ERROR;
3825 END IF;
3826 END IF;
3827 END IF;
3828
3829 IF p_x_line_rec.return_reason_code IS NOT NULL
3830 THEN
3831 IF NOT OE_CNCL_Validate.return_reason(p_x_line_rec.return_reason_code) THEN
3832 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3833 p_x_line_rec.return_reason_code := NULL;
3834 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3835 p_x_line_rec.return_reason_code := FND_API.G_MISS_CHAR;
3836 ELSE
3837 x_return_status := FND_API.G_RET_STS_ERROR;
3838 END IF;
3839 END IF;
3840 END IF;
3841
3842 -- Validate Commitment
3843 IF (p_x_line_rec.commitment_id IS NOT NULL)
3844 THEN
3845 IF NOT OE_CNCL_Validate.commitment(p_x_line_rec.commitment_id) THEN
3846 x_return_status := FND_API.G_RET_STS_ERROR;
3847 END IF;
3848 END IF;
3849 oe_debug_pub.add('Exiting procedure OE_CNCL_VALIDATE_line.Attributes',1);
3850
3851 EXCEPTION
3852
3853 WHEN FND_API.G_EXC_ERROR THEN
3854
3855 x_return_status := FND_API.G_RET_STS_ERROR;
3856
3857 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3858
3859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3860
3861 WHEN OTHERS THEN
3862
3863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3864
3865 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3866 THEN
3867 OE_MSG_PUB.Add_Exc_Msg
3868 ( G_PKG_NAME
3869 , 'Attributes'
3870 );
3871 END IF;
3872
3873 END Attributes;
3874
3875 FUNCTION Get_Item_Type(p_line_rec OE_ORDER_PUB.Line_Rec_Type)
3876
3877 RETURN VARCHAR2
3878 IS
3879 l_item_type_code VARCHAR2(30) := NULL;
3880 l_item_rec OE_ORDER_CACHE.item_rec_type;
3881 BEGIN
3882
3883 oe_debug_pub.add('In OEXVCLINB: Function Get_Item_Type',1);
3884 oe_debug_pub.add('The INV Item is'||to_char(p_line_rec.inventory_item_id),1);
3885
3886 IF p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE THEN
3887 RETURN OE_GLOBALS.G_ITEM_STANDARD;
3888 ELSIF p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CONFIG OR
3889 p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN
3890 RETURN p_line_rec.item_type_code;
3891 END IF;
3892
3893
3894 l_item_rec :=
3895 OE_Order_Cache.Load_Item (p_line_rec.inventory_item_id
3896 ,p_line_rec.ship_from_org_id);
3897
3898 oe_debug_pub.add('Bom Item Type is ' || l_item_rec.bom_item_type);
3899
3900 IF l_item_rec.bom_item_type = 1
3901 -- MODEL items and ato's under pto have bom_item_type = 1
3902 THEN
3903
3904 IF nvl(p_line_rec.top_model_line_ref, 0) <>
3905 nvl(p_line_rec.orig_sys_line_ref, 0)
3906 THEN
3907 oe_debug_pub.add
3908 ('Returning CLASS as the Item Type for ato subconfig',1);
3909 --Procedure to check change in item_type_code
3910 RETURN OE_GLOBALS.G_ITEM_CLASS;
3911 END IF;
3912
3913 oe_debug_pub.add('Returning MODEL as the Item Type',1);
3914 --Procedure to check change in item_type_code
3915 RETURN OE_GLOBALS.G_ITEM_MODEL;
3916
3917 ELSIF l_item_rec.bom_item_type = 2
3918 THEN
3919 oe_debug_pub.add('Returning CLASS as the Item Type',1);
3920 -- Only CLASS items have bom_item_type = 2
3921 --Procedure to check change in item_type_code
3922 RETURN OE_GLOBALS.G_ITEM_CLASS;
3923 ELSIF l_item_rec.bom_item_type = 4 and
3924 l_item_rec.service_item_flag = 'N'
3925 THEN
3926
3927 oe_debug_pub.add('Bom 4 and flag = N');
3928 -- Following 3 items can have bom_item_type = 4 :
3929 -- STANDARD item, OPTION item and a KIT
3930 -- We will distinguish an item to be a kit by seeing if
3931 -- it has a record in bom_bill_of_materials.
3932 -- All options MUST have the top_model_line_ref populated
3933 -- before they come to defaulting. Thus we use it to distinguish
3934 -- between a standard and an option item.
3935 -- ato_item's item_type_code will be standard
3936
3937 oe_debug_pub.add
3938 ('item Org ' || OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'));
3939 oe_debug_pub.add('inventory_item_id ' || p_line_rec.inventory_item_id);
3940 BEGIN
3941 SELECT OE_GLOBALS.G_ITEM_KIT
3942 INTO l_item_type_code
3943 FROM mtl_system_items
3944 WHERE organization_id
3945 = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
3946 AND inventory_item_id = p_line_rec.inventory_item_id
3947 AND pick_components_flag = 'Y';
3948
3949 oe_debug_pub.add(' Before calling check 1');
3950 --Procedure to check change in item_type_code
3951 RETURN l_item_type_code;
3952 EXCEPTION
3953 WHEN NO_DATA_FOUND THEN
3954 oe_debug_pub.add('get_item_type no data found, bom_item_type : 4', 1);
3955 IF (p_line_rec.top_model_line_ref is not null AND
3956 p_line_rec.top_model_line_ref <> FND_API.G_MISS_CHAR)
3957 OR
3958 (p_line_rec.top_model_line_index is not null AND
3959 p_line_rec.top_model_line_index <> FND_API.G_MISS_NUM)
3960
3961 THEN
3962 oe_debug_pub.add(' Before calling check 2');
3963 RETURN OE_GLOBALS.G_ITEM_OPTION;
3964 ELSE
3965 oe_debug_pub.add(' Before calling check 3');
3966 RETURN OE_GLOBALS.G_ITEM_STANDARD;
3967 END IF;
3968 END;
3969
3970 ELSIF l_item_rec.service_item_flag = 'Y' and
3971 l_item_rec.bom_item_type = 4
3972 THEN
3973 oe_debug_pub.add('Service item flag is: ' || l_item_rec.service_item_flag);
3974 RETURN OE_GLOBALS.G_ITEM_SERVICE;
3975
3976 END IF;
3977
3978 RETURN null;
3979
3980 oe_debug_pub.add('Exiting OEXVCLNB: Function Get_Item_Type');
3981
3982 EXCEPTION
3983
3984 WHEN NO_DATA_FOUND THEN
3985 oe_debug_pub.add(' Before calling check 4');
3986 l_item_type_code := OE_GLOBALS.G_ITEM_STANDARD;
3987 RETURN l_item_type_code;
3988
3989 WHEN OTHERS THEN
3990
3991 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3992 THEN
3993 OE_MSG_PUB.Add_Exc_Msg
3994 ( G_PKG_NAME ,
3995 'Get_Item_Type'
3996 );
3997 END IF;
3998
3999 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4000
4001
4002 END Get_Item_Type;
4003
4004 END OE_CNCL_Validate_Line;
[Home] [Help]