1 PACKAGE BODY OE_CNCL_VALIDATE_LINE AS
2 /* $Header: OEXVCLNB.pls 120.10 2006/08/22 22:01:55 sarsridh 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_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level; -- INVCONV
1138 l_tracking_quantity_ind VARCHAR2(30); -- INVCONV
1139 l_secondary_default_ind VARCHAR2(30); -- INVCONV
1140 l_secondary_uom_code varchar2(3) := NULL; -- INVCONV
1141 l_buffer VARCHAR2(2000); -- INVCONV
1142
1143 CURSOR c_item ( discrete_org_id IN NUMBER -- INVCONV
1144 , discrete_item_id IN NUMBER) IS
1145 SELECT tracking_quantity_ind,
1146 secondary_uom_code,
1147 secondary_default_ind
1148 FROM mtl_system_items
1149 WHERE organization_id = discrete_org_id
1150 AND inventory_item_id = discrete_item_id;
1151
1152 /*OPM 02/JUN/00 BEGIN
1153 ====================*/
1154 --l_item_rec OE_ORDER_CACHE.item_rec_type; -- OPM INVCONV
1155 --l_OPM_UOM VARCHAR2(4); --OPM 06/22
1156 --l_status VARCHAR2(1); --OPM 06/22
1157 l_msg_count NUMBER;
1158 l_msg_data VARCHAR2(2000);
1159 l_return NUMBER := 0;
1160 /*OPM 02/JUN/00 END
1161 ==================*/
1162
1163 -- Added for Enhanced Project Validation
1164 result VARCHAR2(1) := PJM_PROJECT.G_VALIDATE_SUCCESS;
1165 errcode VARCHAR2 (80);
1166 l_order_date_type_code VARCHAR2(10);
1167 p_date DATE;
1168
1169 -- AR System Parameters
1170 l_AR_Sys_Param_Rec AR_SYSTEM_PARAMETERS_ALL%ROWTYPE;
1171 l_sob_id NUMBER;
1172
1173 -- eBTax Changes
1174 l_ship_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
1175 l_ship_to_party_id hz_cust_accounts.party_id%type;
1176 l_ship_to_party_site_id hz_party_sites.party_site_id%type;
1177 l_bill_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
1178 l_bill_to_party_id hz_cust_accounts.party_id%type;
1179 l_bill_to_party_site_id hz_party_sites.party_site_id%type;
1180 l_org_id NUMBER;
1181 -- l_legal_entity_id NUMBER;
1182
1183 cursor partyinfo(p_site_org_id HZ_CUST_SITE_USES_ALL.SITE_USE_ID%type) is
1184 SELECT cust_acct.cust_account_id,
1185 cust_Acct.party_id,
1186 acct_site.party_site_id,
1187 site_use.org_id
1188 FROM
1189 HZ_CUST_SITE_USES_ALL site_use,
1190 HZ_CUST_ACCT_SITES_ALL acct_site,
1191 HZ_CUST_ACCOUNTS_ALL cust_Acct
1192 WHERE site_use.site_use_id = p_site_org_id
1193 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
1194 and acct_site.cust_account_id = cust_acct.cust_account_id;
1195
1196 --bug 4729536
1197 CURSOR cur_customer_relations IS
1198
1199 Select /*MOAC_SQL_NO_CHANGE*/ 'VALID' a
1200 From oe_invoice_to_orgs_v
1201 WHERE site_use_id = p_line_rec.invoice_to_org_id
1202 AND status = 'A'
1203 AND customer_id = p_line_rec.sold_to_org_id
1204 and rownum =1
1205
1206 UNION ALL
1207
1208 SELECT /*MOAC_SQL_NO_CHANGE*/ 'VALID' a
1209 FROM oe_invoice_to_orgs_v oito
1210 WHERE oito.site_use_id = p_line_rec.invoice_to_org_id
1211 AND oito.status = 'A' AND
1212 EXISTS
1213 (
1214 select 1 from HZ_CUST_ACCT_RELATE hcar
1215 where hcar.CUST_ACCOUNT_ID = oito.customer_id
1216 and hcar.RELATED_CUST_ACCOUNT_ID = p_line_rec.sold_to_org_id
1217 /* added the following condition to fix the bug 2002486 */
1218 and hcar.bill_to_flag = 'Y'
1219 )
1220 and rownum = 1 ;
1221
1222 BEGIN
1223
1224 oe_debug_pub.add('Enter OE_CNCL_VALIDATE_LINE.ENTITY',1);
1225
1226
1227 -----------------------------------------------------------
1228 -- Check required attributes.
1229 -----------------------------------------------------------
1230
1231 oe_debug_pub.add('1 '||l_return_status, 1);
1232
1233 oe_debug_pub.add('2 '||l_return_status, 1);
1234 IF p_line_rec.inventory_item_id IS NULL
1235 THEN
1236
1237 l_return_status := FND_API.G_RET_STS_ERROR;
1238
1239 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1240 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1241 OE_Order_UTIL.Get_Attribute_Name('INVENTORY_ITEM_ID'));
1242 OE_MSG_PUB.Add;
1243
1244 END IF;
1245
1246 oe_debug_pub.add('3 '||l_return_status, 1);
1247 IF p_line_rec.line_type_id IS NULL
1248 THEN
1249
1250 l_return_status := FND_API.G_RET_STS_ERROR;
1251
1252 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1253 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1254 OE_Order_UTIL.Get_Attribute_Name('LINE_TYPE_ID'));
1255 OE_MSG_PUB.Add;
1256
1257 ELSIF p_line_rec.line_type_id IS NOT NULL THEN
1258 Validate_line_type(p_line_rec => p_line_rec);
1259
1260 END IF;
1261
1262 -- Return Error if a required attribute is missing.
1263
1264 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1265
1266 RAISE FND_API.G_EXC_ERROR;
1267
1268 END IF;
1269
1270
1271 --------------------------------------------------------------
1272 -- Check conditionally required attributes here.
1273 --------------------------------------------------------------
1274
1275 -- For return lines, Return_Reason_Code is required
1276 oe_debug_pub.add('5 '||l_return_status, 1);
1277 IF p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE
1278 and p_line_rec.return_reason_code is NULL
1279 THEN
1280 l_return_status := FND_API.G_RET_STS_ERROR;
1281
1282 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1283 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1284 OE_Order_UTIL.Get_Attribute_Name('RETURN_REASON_CODE'));
1285 OE_MSG_PUB.Add;
1286
1287 END IF;
1288
1289
1290 oe_debug_pub.add('6 '||l_return_status, 1);
1291
1292 -- subinventory
1293 oe_debug_pub.add('Entity: subinventory - ' || p_line_rec.subinventory);
1294
1295 IF p_line_rec.subinventory is not null THEN
1296 IF p_line_rec.source_type_code = 'INTERNAL' OR
1297 p_line_rec.source_type_code is null THEN
1298 oe_debug_pub.add('Entity Validateion: subinventory', 1);
1299 IF p_line_rec.ship_from_org_id is null THEN
1300 l_return_status := FND_API.G_RET_STS_ERROR;
1301 fnd_message.set_name('ONT', 'OE_ATTRIBUTE_REQUIRED');
1302 fnd_message.set_token('ATTRIBUTE',OE_Order_UTIL.Get_Attribute_Name('SHIP_FROM_ORG_ID'));
1303 OE_MSG_PUB.Add;
1304 END IF;
1305 ELSE -- external
1306 l_return_status := FND_API.G_RET_STS_ERROR;
1307 fnd_message.set_name('ONT', 'OE_SUBINV_EXTERNAL');
1308 OE_MSG_PUB.Add;
1309 END IF;
1310 END IF;
1311
1312 -- end subinventory
1313
1314 oe_debug_pub.add('Entity: done subinv validation', 1);
1315
1316 -- If line is booked, then check for the attributes required on booked lines
1317 -- Fix bug 1277092: this check not required for fully cancelled lines
1318 IF p_line_rec.booked_flag = 'Y'
1319 AND p_line_rec.cancelled_flag <> 'Y' THEN
1320 Check_Book_Reqd_Attributes( p_line_rec => p_line_rec
1321 , x_return_status => l_return_status);
1322
1323 END IF;
1324
1325 -- Return Error if a conditionally required attribute is missing.
1326
1327 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1328
1329 RAISE FND_API.G_EXC_ERROR;
1330
1331 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1332
1333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1334
1335 END IF;
1336
1337
1338 -- OPM 02/JUN/00 START
1339 -- For an item with tracking in Primary and secondary , check qty1/2 both present and sync'd -- INVCONV
1340 -- =====================================================================
1341 oe_debug_pub.add('Primary and Secondary X-VAL start', 1);
1342
1343 OPEN c_item( p_line_rec.ship_from_org_id,
1344 p_line_rec.inventory_item_id
1345 );
1346 FETCH c_item
1347 INTO l_tracking_quantity_ind,
1348 l_secondary_uom_code ,
1349 l_secondary_default_ind
1350 ;
1351
1352
1353 IF c_item%NOTFOUND THEN
1354 l_tracking_quantity_ind := 'P';
1355 l_secondary_uom_code := NULL;
1356 l_secondary_default_ind := null;
1357
1358 END IF;
1359
1360 Close c_item;
1361
1362
1363
1364 /*IF OE_Line_Util.Process_Characteristics
1365 (p_line_rec.inventory_item_id
1366 ,p_line_rec.ship_from_org_id
1367 ,l_item_rec)
1368 THEN
1369 */
1370 IF l_tracking_quantity_ind = 'PS' then
1371 -- IF l_item_rec.dualum_ind in (1,2,3) THEN INVCONV
1372 oe_debug_pub.add('Primary and Secondary X-VAL - tracking_quantity_ind PS', 2);
1373
1374 IF (p_line_rec.ordered_quantity <> FND_API.G_MISS_NUM OR
1375 p_line_rec.ordered_quantity IS NOT NULL) AND
1376 (p_line_rec.ordered_quantity2 = FND_API.G_MISS_NUM OR
1377 p_line_rec.ordered_quantity2 IS NULL) THEN
1378
1379 oe_debug_pub.add('Primary and Secondary X-VAL qty 1 not empty', 2);
1380
1381 l_return_status := FND_API.G_RET_STS_ERROR;
1382 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1383 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Ordered_Quantity2');
1384 OE_MSG_PUB.Add;
1385
1386 ELSIF (p_line_rec.ordered_quantity2 <> FND_API.G_MISS_NUM OR
1387 p_line_rec.ordered_quantity2 IS NOT NULL) AND
1388 (p_line_rec.ordered_quantity IS NULL) THEN
1389
1390 l_return_status := FND_API.G_RET_STS_ERROR;
1391 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
1392 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Ordered_Quantity');
1393 OE_MSG_PUB.Add;
1394 END IF; -- IF (p_line_rec.ordered_quantity <> FND_API.G_MISS_NUM OR
1395 END IF; -- IF l_item_rec.tracking_quantity_ind = 'PS' IF l_item_rec.dualum_ind in (1,2,3) THEN
1396
1397 /* If qty1/qty2 both populated, check tolerances
1398 ================================================*/
1399 oe_debug_pub.add('Primary and Secondary X-VAL - tolerance check', 2);
1400
1401 IF l_secondary_default_ind in ('N','D') then -- INVCONV
1402 -- IF l_item_rec.dualum_ind in (2,3) THEN
1403 IF (p_line_rec.ordered_quantity <> FND_API.G_MISS_NUM AND
1404 p_line_rec.ordered_quantity IS NOT NULL) AND
1405 (p_line_rec.ordered_quantity2 <> FND_API.G_MISS_NUM AND
1406 p_line_rec.ordered_quantity2 IS NOT NULL) THEN
1407
1408 -- check the deviation and error out
1409 l_return := INV_CONVERT.Within_Deviation -- INVCONV
1410 ( p_organization_id =>
1411 p_line_rec.ship_from_org_id
1412 , p_inventory_item_id =>
1413 p_line_rec.inventory_item_id
1414 , p_precision => 5
1415 , p_quantity => p_line_rec.ordered_quantity
1416 , p_uom_code1 => p_line_rec.order_quantity_uom -- INVCONV
1417 , p_quantity2 => p_line_rec.ordered_quantity2
1418 , p_uom_code2 => l_secondary_uom_code );
1419
1420 IF l_return = 0
1421 then
1422 IF l_debug_level > 0 THEN
1423 oe_debug_pub.add('Primary and Secondary X-VAL - tolerance error 1' ,1);
1424 END IF;
1425
1426 l_buffer := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, -- INVCONV
1427 p_encoded => 'F');
1428 oe_msg_pub.add_text(p_message_text => l_buffer);
1429 IF l_debug_level > 0 THEN
1430 oe_debug_pub.add(l_buffer,1);
1431 END IF;
1432 l_return_status := FND_API.G_RET_STS_ERROR;
1433
1434 else
1435 IF l_debug_level > 0 THEN
1436 oe_debug_pub.add('Primary and Secondary X-VAL - No tolerance error ',1);
1437 END IF;
1438 END IF; -- IF l_return = 0
1439
1440
1441 -- OPM BEGIN 06/22
1442 /* Get the OPM equivalent code for order_quantity_uom
1443 ===================================================== INVCONV
1444 GMI_Reservation_Util.Get_OPMUOM_from_AppsUOM
1445 (p_Apps_UOM => p_line_rec.order_quantity_uom
1446 ,x_OPM_UOM => l_OPM_UOM
1447 ,x_return_status => l_status
1448 ,x_msg_count => l_msg_count
1449 ,x_msg_data => l_msg_data);
1450
1451 l_return := GMICVAL.dev_validation(l_item_rec.opm_item_id
1452 ,0
1453 ,p_line_rec.ordered_quantity
1454 ,l_OPM_UOM
1455 ,p_line_rec.ordered_quantity2
1456 ,l_item_rec.opm_item_um2
1457 ,0);
1458 -- OPM END 06/22
1459 IF(l_return = -68) THEN
1460 l_return_status := FND_API.G_RET_STS_ERROR;
1461 FND_MESSAGE.set_name('GMI', 'IC_DEVIATION_HI_ERR');
1462 OE_MSG_PUB.Add;
1463 ELSIF (l_return = -69) THEN
1464 l_return_status := FND_API.G_RET_STS_ERROR;
1465 FND_MESSAGE.set_name('GMI', 'IC_DEVIATION_LO_ERR');
1466 OE_MSG_PUB.Add;
1467 END IF; */
1468
1469
1470 END IF; -- IF (p_line_rec.ordered_quantity <> FND_API.G_MISS_NUM AND
1471
1472
1473 END IF; -- IF l_item_rec.tracking_quantity_ind = 'PS' IF l_item_rec.dualum_ind in (2,3) THEN INVCONV
1474
1475 -- END IF; -- IF OE_Line_Util.Process_Characteristics INVCONV
1476
1477 -- Return Error if a required quantity validation fails
1478 -- ====================================================
1479 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1480 RAISE FND_API.G_EXC_ERROR;
1481 END IF;
1482 -- OPM 02/JUN/00 END
1483 -- ===================
1484
1485
1486 ---------------------------------------------------------------------
1487 -- Validate attribute dependencies here.
1488 ---------------------------------------------------------------------
1489
1490 -- Validate if the warehouse, item combination is valid
1491 IF p_line_rec.inventory_item_id is not null AND
1492 p_line_rec.ship_from_org_id is not null AND
1493 p_line_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
1494 p_line_rec.ship_from_org_id <> FND_API.G_MISS_NUM THEN
1495
1496 /* IF p_line_rec.inventory_item_id <>
1497 nvl(p_old_line_rec.inventory_item_id,0) OR
1498 p_line_rec.ship_from_org_id <> nvl(p_old_line_rec.ship_from_org_id,0)
1499 THEN */
1500
1501
1502
1503 IF p_line_rec.source_type_code = OE_GLOBALS.G_SOURCE_INTERNAL
1504 or p_line_rec.source_type_code is null
1505 THEN
1506 oe_debug_pub.add('Source Type is Internal',1);
1507
1508 IF p_line_rec.line_category_code = 'RETURN' THEN
1509 l_item_type_code := OE_LINE_UTIL.Get_Return_item_type_code(
1510 p_line_rec);
1511 ELSE
1512 l_item_type_code := p_line_rec.item_type_code;
1513 END IF;
1514
1515
1516 IF NOT Validate_Item_Warehouse
1517 (p_line_rec.inventory_item_id,
1518 p_line_rec.ship_from_org_id,
1519 l_item_type_code,
1520 p_line_rec.line_id,
1521 p_line_rec.top_model_line_id)
1522 THEN
1523 l_return_status := FND_API.G_RET_STS_ERROR;
1524 END IF;
1525 ELSE
1526 oe_debug_pub.add('Source Type is External',1);
1527 IF NOT Validate_Receiving_Org
1528 (p_line_rec.inventory_item_id,
1529 p_line_rec.ship_from_org_id)
1530 THEN
1531 l_return_status := FND_API.G_RET_STS_ERROR;
1532 END IF;
1533 END IF;
1534 --END IF;
1535 END IF;
1536
1537 -- subinventory
1538
1539 IF p_line_rec.ship_from_org_id is not null AND
1540 p_line_rec.subinventory is not null AND
1541 p_line_rec.ship_From_org_id <> FND_API.G_MISS_NUM AND
1542 p_line_rec.subinventory <> FND_API.G_MISS_CHAR THEN
1543
1544 /* IF p_line_rec.ship_from_org_id <> nvl(p_old_line_rec.ship_from_org_id, 0) OR
1545 p_line_rec.subinventory <> nvl(p_old_line_rec.subinventory, '0') THEN
1546 */
1547 BEGIN
1548 SELECT 'VALID'
1549 INTO l_dummy
1550 FROM MTL_SUBINVENTORIES_TRK_VAL_V
1551 WHERE organization_id = p_line_rec.ship_from_org_id
1552 AND secondary_inventory_name = p_line_rec.subinventory;
1553 EXCEPTION
1554 WHEN OTHERS THEN
1555 -- not a valid subinventory, show show a msg
1556 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557 END;
1558 --END IF;
1559 END IF;
1560
1561 -- end subinventory
1562
1563 -- start decimal qty validation
1564 IF p_line_rec.inventory_item_id is not null THEN
1565
1566 oe_debug_pub.add('decimal1',2);
1567 IF p_line_rec.order_quantity_uom is not null THEN
1568
1569 -- validate ordered quantity
1570 Validate_Decimal_Quantity
1571 (p_item_id => p_line_rec.inventory_item_id
1572 ,p_item_type_code => p_line_rec.item_type_code
1573 ,p_input_quantity => p_line_rec.ordered_quantity
1574 ,p_uom_code => p_line_rec.order_quantity_uom
1575 ,x_return_status => l_return_status
1576 );
1577
1578 -- validate invoiced_quantity
1579 Validate_Decimal_Quantity
1580 (p_item_id => p_line_rec.inventory_item_id
1581 ,p_item_type_code => p_line_rec.item_type_code
1582 ,p_input_quantity => p_line_rec.invoiced_quantity
1583 ,p_uom_code => p_line_rec.order_quantity_uom
1584 ,x_return_status => l_return_status
1585 );
1586
1587 -- cancelled quantity
1588 Validate_Decimal_Quantity
1589 (p_item_id => p_line_rec.inventory_item_id
1590 ,p_item_type_code => p_line_rec.item_type_code
1591 ,p_input_quantity => p_line_rec.cancelled_quantity
1592 ,p_uom_code => p_line_rec.order_quantity_uom
1593 ,x_return_status => l_return_status
1594 );
1595
1596 -- auto_selected quantity
1597 Validate_Decimal_Quantity
1598 (p_item_id => p_line_rec.inventory_item_id
1599 ,p_item_type_code => p_line_rec.item_type_code
1600 ,p_input_quantity => p_line_rec.auto_selected_quantity
1601 ,p_uom_code => p_line_rec.order_quantity_uom
1602 ,x_return_status => l_return_status
1603 );
1604
1605 -- reserved quantity
1606 Validate_Decimal_Quantity
1607 (p_item_id => p_line_rec.inventory_item_id
1608 ,p_item_type_code => p_line_rec.item_type_code
1609 ,p_input_quantity => p_line_rec.reserved_quantity
1610 ,p_uom_code => p_line_rec.order_quantity_uom
1611 ,x_return_status => l_return_status
1612 );
1613
1614 -- fulfilled quantity, double check with Shashi
1615 Validate_Decimal_Quantity
1616 (p_item_id => p_line_rec.inventory_item_id
1617 ,p_item_type_code => p_line_rec.item_type_code
1618 ,p_input_quantity => p_line_rec.fulfilled_quantity
1619 ,p_uom_code => p_line_rec.order_quantity_uom
1620 ,x_return_status => l_return_status
1621 );
1622
1623 END IF; -- order quantity uom not null
1624
1625 -- validate pricing quantity starts here
1626 -- bug 1391668, don't need to validate pricing quantity
1627 /*
1628 IF (p_line_rec.pricing_quantity_uom is not null AND
1629 p_line_rec.pricing_quantity is not null) THEN
1630
1631 Validate_Decimal_Quantity
1632 (p_item_id => p_line_rec.inventory_item_id
1633 ,p_item_type_code => p_line_rec.item_type_code
1634 ,p_input_quantity => p_line_rec.pricing_quantity
1635 ,p_uom_code => p_line_rec.pricing_quantity_uom
1636 ,x_return_status => l_return_status
1637 );
1638
1639 END IF; -- quantity or uom is null
1640 */
1641 END IF; -- inventory_item_id is null
1642 -- end decimal quantity validation
1643
1644
1645 -- Error if reserved quantity > ordered quantity
1646 /* IF NOT OE_GLOBALS.Equal(p_line_rec.reserved_quantity,p_old_line_rec.reserved_quantity)
1647 THEN
1648
1649 IF (p_line_rec.reserved_quantity > p_line_rec.ordered_quantity) THEN
1650 fnd_message.set_name('ONT','OE_SCH_RES_MORE_ORD_QTY');
1651 OE_MSG_PUB.Add;
1652 l_return_status := FND_API.G_RET_STS_ERROR;
1653 END IF;
1654
1655 END IF;*/
1656
1657
1658 -- Check to see if the user has changed both the Schedule Ship Date
1659 -- and Schedule Arrival Date. This is not allowed. The user can change
1660 -- either one, but not both.
1661
1662 /*
1663 IF (NOT OE_GLOBALS.Equal(p_line_rec.schedule_ship_date,
1664 p_old_line_rec.schedule_ship_date)) AND
1665 (NOT OE_GLOBALS.Equal(p_line_rec.schedule_arrival_date,
1666 p_old_line_rec.schedule_arrival_date)) AND
1667 (OE_ORDER_SCH_UTIL.OESCH_PERFORM_SCHEDULING = 'Y') THEN
1668
1669 -- Config item is created and passed by the CTO team. So this is
1670 -- is the only item type, which when gets created, already has
1671 -- Schedule_Ship_Date and schedule_Arrival_date. We should not
1672 -- error out for this item.
1673
1674 IF p_line_rec.item_type_code <> OE_GLOBALS.G_ITEM_CONFIG THEN
1675 FND_MESSAGE.SET_NAME('ONT','OE_SCH_INVALID_CHANGE');
1676 OE_MSG_PUB.Add;
1677 l_return_status := FND_API.G_RET_STS_ERROR;
1678 END IF;
1679
1680 END IF;
1681 */
1682
1683 Validate_Source_Type
1684 ( p_line_rec => p_line_rec
1685 ,x_return_status => l_return_status);
1686
1687 -- PJM validation.
1688
1689 IF PJM_UNIT_EFF.ENABLED = 'Y' THEN
1690
1691 IF (p_line_rec.project_id IS NOT NULL
1692 AND p_line_rec.ship_from_org_id IS NULL) THEN
1693
1694 l_return_status := FND_API.G_RET_STS_ERROR;
1695 FND_MESSAGE.SET_NAME('ONT', 'OE_SHIP_FROM_REQD');
1696 OE_MSG_PUB.add;
1697 ELSIF (p_line_rec.task_id IS NOT NULL
1698 AND p_line_rec.project_id IS NULL) THEN
1699
1700 l_return_status := FND_API.G_RET_STS_ERROR;
1701 FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_PROJECT_REQD');
1702 OE_MSG_PUB.add;
1703
1704 END IF;
1705
1706 -- Added Code for Enhanced Project Validation and Controls.
1707
1708 l_order_date_type_code := NVL(OE_SCHEDULE_UTIL.Get_Date_Type(
1709 p_line_rec.header_id), 'SHIP');
1710
1711
1712 IF l_order_date_type_code = 'SHIP' THEN
1713 p_date := NVL(p_line_rec.schedule_ship_date,
1714 p_line_rec.request_date);
1715 ELSIF l_order_date_type_code = 'ARRIVAL' THEN
1716 p_date := NVL(p_line_rec.schedule_arrival_date,
1717 p_line_rec.request_date);
1718 END IF;
1719
1720 OE_DEBUG_PUB.Add('Before calling Validate Proj References',1);
1721
1722 result := PJM_PROJECT.VALIDATE_PROJ_REFERENCES
1723 ( X_inventory_org_id => p_line_rec.ship_from_org_id
1724 , X_operating_unit => p_line_rec.org_id
1725 , X_project_id => p_line_rec.project_id
1726 , X_task_id => p_line_rec.task_id
1727 , X_date1 => p_date
1728 , X_date2 => NULL
1729 , X_calling_function =>'OEXVCLNB'
1730 , X_error_code => errcode
1731 );
1732 OE_DEBUG_PUB.Add('Validate Proj References Error:'||
1733 errcode,1);
1734 OE_DEBUG_PUB.Add('Validate Proj References Result:'||
1735 result,1);
1736
1737 IF result <> PJM_PROJECT.G_VALIDATE_SUCCESS THEN
1738 OE_MSG_PUB.Transfer_Msg_Stack;
1739 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
1740 FOR I in 1..l_msg_count loop
1741 l_msg_data := OE_MSG_PUB.Get(I,'F');
1742 OE_DEBUG_PUB.add(l_msg_data,1);
1743 END LOOP;
1744 END IF;
1745
1746 IF result = PJM_PROJECT.G_VALIDATE_FAILURE THEN
1747 l_return_status := FND_API.G_RET_STS_ERROR;
1748 OE_DEBUG_PUB.Add('PJM Validation API returned with Errors',1);
1749 ELSIF result = PJM_PROJECT.G_VALIDATE_WARNING THEN
1750 OE_DEBUG_PUB.Add('PJM Validation API returned with Warnings',1);
1751 END IF;
1752
1753
1754
1755 /* -- Commented Code for Enhanced Project Validation and Controls
1756
1757 ELSIF ( p_line_rec.ship_from_org_id IS NOT NULL AND
1758 p_line_rec.project_id IS NOT NULL) THEN
1759
1760 -- Validate project/warehouse combination.
1761 IF pjm_project.val_proj_idtonum
1762 (p_line_rec.project_id,
1763 p_line_rec.ship_from_org_id) IS NULL THEN
1764
1765 l_return_status := FND_API.G_RET_STS_ERROR;
1766 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SHIP_FROM_PROJ');
1767 OE_MSG_PUB.add;
1768 END IF;
1769
1770 END IF;
1771
1772 IF (p_line_rec.task_id IS NOT NULL
1773 AND p_line_rec.project_id IS NULL) THEN
1774
1775 l_return_status := FND_API.G_RET_STS_ERROR;
1776 FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_PROJECT_REQD');
1777 OE_MSG_PUB.add;
1778
1779 ELSIF (p_line_rec.task_id is NOT NULL
1780 AND p_line_rec.project_id IS NOT NULL) THEN
1781
1782 IF NOT Validate_task(
1783 p_line_rec.project_id,
1784 p_line_rec.task_id) THEN
1785
1786 l_return_status := FND_API.G_RET_STS_ERROR;
1787 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => 'TASK_ID');
1788 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1789 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1790 OE_Order_Util.Get_Attribute_Name('task_id'));
1791 OE_MSG_PUB.Add;
1792 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
1793
1794 END IF;
1795
1796 ELSIF (p_line_rec.task_id is NULL
1797 AND p_line_rec.project_id IS NOT NULL) THEN
1798
1799 IF Validate_task_reqd(
1800 p_line_rec.project_id,
1801 p_line_rec.ship_from_org_id) THEN
1802
1803 l_return_status := FND_API.G_RET_STS_ERROR;
1804 FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_TASK_REQD');
1805 OE_MSG_PUB.ADD;
1806
1807 END IF;
1808
1809 END IF;
1810 */
1811
1812 -- End Item Unit number logic.
1813
1814 oe_debug_pub.add('10 '||l_return_status, 1);
1815 IF (p_line_rec.inventory_item_id IS NOT NULL) AND
1816 (p_line_rec.ship_from_org_id IS NOT NULL) AND
1817 (p_line_rec.end_item_unit_number IS NULL) THEN
1818
1819 IF PJM_UNIT_EFF.UNIT_EFFECTIVE_ITEM
1820 (p_line_rec.inventory_item_id,p_line_rec.ship_from_org_id) = 'Y'
1821 THEN
1822
1823 l_return_status := FND_API.G_RET_STS_ERROR;
1824 fnd_message.set_name('ONT', 'OE_UEFF_NUMBER_REQD');
1825 OE_MSG_PUB.add;
1826 END IF;
1827
1828 END IF;
1829 ELSE -- When project manufacturing is not enabled at the site.
1830
1831 IF (p_line_rec.project_id IS NOT NULL OR
1832 p_line_rec.task_id IS NOT NULL OR
1833 p_line_rec.end_item_unit_number IS NOT NULL) THEN
1834 l_return_status := FND_API.G_RET_STS_ERROR;
1835 fnd_message.set_name('ONT', 'OE_PJM_NOT_INSTALLED');
1836 OE_MSG_PUB.add;
1837
1838 END IF;
1839
1840
1841 END IF; --End if PJM_UNIT_EFF.ENABLED
1842
1843 -- Donot allow to update project and task when a option/class is under ATO
1844 -- Model.
1845
1846 oe_debug_pub.add('11 '||l_return_status, 1);
1847
1848 /*IF p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
1849
1850 IF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
1851 p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS)
1852 --AND p_line_rec.line_id <> p_line_rec.ato_line_id
1853 THEN
1854
1855 FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_PROJ_UPD');
1856 OE_MSG_PUB.add;
1857
1858
1859 END IF;
1860
1861 END IF;
1862 -- End of PJM validation.
1863 */
1864
1865 -- Validate if item, item_identifier_type, inventory_item combination is valid
1866 oe_debug_pub.add('12-1 '||l_return_status, 1);
1867 IF p_line_rec.inventory_item_id IS NOT NULL THEN
1868
1869 IF NOT Validate_Item_Fields
1870 ( p_line_rec.inventory_item_id
1871 , p_line_rec.ordered_item_id
1872 , p_line_rec.item_identifier_type
1873 , p_line_rec.ordered_item
1874 , p_line_rec.sold_to_org_id)
1875 THEN
1876 l_return_status := FND_API.G_RET_STS_ERROR;
1877 fnd_message.set_name('ONT', 'OE_ITEM_VALIDATION_FAILED');
1878 OE_MSG_PUB.add;
1879 END IF;
1880
1881 END IF;
1882
1883 oe_debug_pub.add('12 '||l_return_status, 1);
1884 -- Validate if return item and item on referenced sales order line mismatch
1885 IF (p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE and
1886 p_line_rec.reference_line_id is not null and
1887 p_line_rec.inventory_item_id IS NOT NULL)
1888 THEN
1889 IF NOT Validate_Return_Item_Mismatch
1890 ( p_line_rec.reference_line_id
1891 , p_line_rec.inventory_item_id
1892 )
1893 THEN
1894 l_return_status := FND_API.G_RET_STS_ERROR;
1895 fnd_message.set_name('ONT', 'OE_RETURN_ITEM_MISMATCH_REJECT');
1896 OE_MSG_PUB.add;
1897 END IF;
1898 END IF;
1899
1900 oe_debug_pub.add('13 '||l_return_status, 1);
1901
1902 -- Validate if returning a fulfilled sales order line
1903 IF (p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE and
1904 p_line_rec.reference_line_id is not null)
1905 THEN
1906 IF NOT Validate_Return_Fulfilled_Line
1907 ( p_line_rec.reference_line_id
1908 )
1909 THEN
1910 l_return_status := FND_API.G_RET_STS_ERROR;
1911 fnd_message.set_name('ONT', 'OE_UNFULFILLED_LINE_REJECT');
1912 OE_MSG_PUB.add;
1913 END IF;
1914
1915 END IF;
1916
1917 oe_debug_pub.add('14 '||l_return_status, 1);
1918
1919 -- Validate if item on the Return is Returnable
1920 IF (p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE and
1921 p_line_rec.inventory_item_id IS NOT NULL)
1922 THEN
1923 IF NOT Validate_Return_Item(p_line_rec.inventory_item_id,
1924 p_line_rec.ship_from_org_id)
1925 THEN
1926 l_return_status := FND_API.G_RET_STS_ERROR;
1927 fnd_message.set_name('ONT', 'OE_ITEM_NOT_RETURNABLE');
1928 OE_MSG_PUB.add;
1929 END IF;
1930 END IF;
1931
1932
1933 oe_debug_pub.add('14_1 '||l_return_status, 1);
1934
1935 -- Validate if Reference SO Line is Valid
1936 IF (p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE and
1937 p_line_rec.reference_line_id is not null)
1938 THEN
1939 IF NOT Validate_Return_Reference(p_line_rec.reference_line_id)
1940 THEN
1941 -- Message is populated in the function
1942 l_return_status := FND_API.G_RET_STS_ERROR;
1943 END IF;
1944 END IF;
1945
1946 oe_debug_pub.add('14_2 '||l_return_status, 1);
1947
1948 -- Validate the quantity = 1 on RMA for Serial Number reference
1949 IF (p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE and
1950 p_line_rec.reference_line_id is not null and
1951 p_line_rec.return_context = 'SERIAL' and
1952 NVL(p_line_rec.ordered_quantity,1) <> 1)
1953 THEN
1954 l_return_status := FND_API.G_RET_STS_ERROR;
1955 fnd_message.set_name('ONT','OE_SERIAL_REFERENCED_RMA');
1956 OE_MSG_PUB.Add;
1957 END IF;
1958
1959
1960 oe_debug_pub.add('14_3 '||l_return_status, 1);
1961
1962 -- Validation of Ship To Org Id.
1963 IF p_line_rec.ship_to_org_id IS NOT NULL
1964
1965 THEN
1966
1967 IF NOT Validate_Ship_To_Org(p_line_rec.ship_to_org_id,
1968 p_line_rec.sold_to_org_id
1969 ) THEN
1970 l_return_status := FND_API.G_RET_STS_ERROR;
1971 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
1972 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
1973 OE_Order_Util.Get_Attribute_Name('ship_to_org_id'));
1974 OE_MSG_PUB.Add;
1975 END IF;
1976
1977 END IF;
1978
1979
1980 -- Ship to contact depends on Ship To Org
1981 IF p_line_rec.ship_to_contact_id IS NOT NULL
1982
1983 THEN
1984
1985 BEGIN
1986 oe_debug_pub.add('ship_to_contact_id :'||to_char(p_line_rec.ship_to_contact_id),2);
1987
1988 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
1989 INTO l_dummy
1990 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
1991 , HZ_CUST_ACCT_SITES ACCT_SITE
1992 , HZ_CUST_SITE_USES_ALL SHIP
1993 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_line_rec.ship_to_contact_id
1994 AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
1995 AND ACCT_SITE.CUST_ACCT_SITE_ID = SHIP.CUST_ACCT_SITE_ID
1996 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
1997 AND SHIP.SITE_USE_ID = p_line_rec.ship_to_org_id
1998 AND SHIP.STATUS = 'A'
1999 AND ROWNUM = 1;
2000
2001 /* Replaced ra_contacts , ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440 */
2002
2003
2004 EXCEPTION
2005 WHEN NO_DATA_FOUND THEN
2006 l_return_status := FND_API.G_RET_STS_ERROR;
2007 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2008 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2009 OE_Order_Util.Get_Attribute_Name('ship_to_contact_id'));
2010 OE_MSG_PUB.Add;
2011 WHEN OTHERS THEN
2012 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2013 OE_MSG_PUB.Add_Exc_Msg
2014 ( G_PKG_NAME ,
2015 'Record - Ship To Contact validation '
2016 );
2017 END IF;
2018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2019 END;
2020
2021 END IF;
2022
2023
2024 -- Validation of Deliver To Org Id.
2025 IF p_line_rec.deliver_to_org_id IS NOT NULL
2026
2027 THEN
2028
2029 IF NOT Validate_Deliver_To_Org(p_line_rec.deliver_to_org_id,
2030 p_line_rec.sold_to_org_id
2031 ) THEN
2032 l_return_status := FND_API.G_RET_STS_ERROR;
2033 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
2034 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2035 OE_Order_Util.Get_Attribute_Name('deliver_to_org_id'));
2036 OE_MSG_PUB.Add;
2037 END IF;
2038
2039 END IF;
2040
2041 -- Deliver to contact depends on Deliver To Org
2042 IF p_line_rec.deliver_to_contact_id IS NOT NULL
2043
2044 THEN
2045
2046 BEGIN
2047 oe_debug_pub.add('deliver_to_contact_id :'||to_char(p_line_rec.deliver_to_contact_id),2);
2048
2049 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
2050 INTO l_dummy
2051 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
2052 , HZ_CUST_ACCT_SITES ACCT_SITE
2053 , HZ_CUST_SITE_USES_ALL DELI
2054 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_line_rec.deliver_to_contact_id
2055 AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
2056 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
2057 AND ACCT_SITE.CUST_ACCT_SITE_ID = DELI.CUST_ACCT_SITE_ID
2058 AND DELI.SITE_USE_ID = p_line_rec.deliver_to_org_id
2059 AND DELI.STATUS = 'A'
2060 AND ROWNUM = 1;
2061
2062 /* Replaced ra_contacts , ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440 */
2063
2064
2065 EXCEPTION
2066 WHEN NO_DATA_FOUND THEN
2067 l_return_status := FND_API.G_RET_STS_ERROR;
2068 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2069 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2070 OE_Order_Util.Get_Attribute_Name('deliver_to_contact_id'));
2071 OE_MSG_PUB.Add;
2072 WHEN OTHERS THEN
2073 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2074 OE_MSG_PUB.Add_Exc_Msg
2075 ( G_PKG_NAME ,
2076 'Record - Deliver To Contact validation '
2077 );
2078 END IF;
2079 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2080 END;
2081
2082 END IF;
2083
2084 -- Validation of Invoice To Org Id.
2085 IF p_line_rec.invoice_to_org_id IS NOT NULL
2086
2087 THEN
2088
2089 BEGIN
2090 oe_debug_pub.add('invoice_to_org_id :'||to_char(p_line_rec.invoice_to_org_id),2);
2091 --lcustomer_relations := FND_PROFILE.VALUE('ONT_CUSTOMER_RELATIONSHIPS');
2092
2093 IF nvl(lcustomer_relations,'N') = 'N' THEN
2094
2095 Select 'VALID'
2096 Into l_dummy
2097 From oe_invoice_to_orgs_v
2098 Where customer_id = p_line_rec.sold_to_org_id
2099 And site_use_id = p_line_rec.invoice_to_org_id;
2100
2101 ELSIF lcustomer_relations = 'Y' THEN
2102
2103 /*Select MOAC_SQL_NO_CHANGE 'VALID'
2104 Into l_dummy
2105 From oe_invoice_to_orgs_v
2106 WHERE site_use_id = p_line_rec.invoice_to_org_id
2107 AND status = 'A' AND
2108 customer_id in (
2109 Select p_line_rec.sold_to_org_id from dual
2110 union
2111 select CUST_ACCOUNT_ID from
2112 HZ_CUST_ACCT_RELATE
2113 where RELATED_CUST_ACCOUNT_ID = p_line_rec.sold_to_org_id
2114 /* added the following condition to fix the bug 2002486
2115 and bill_to_flag = 'Y')
2116 and rownum = 1;*/
2117
2118 --bug 4729536
2119 OPEN cur_customer_relations;
2120 FETCH cur_customer_relations INTO l_dummy ;
2121 CLOSE cur_customer_relations;
2122
2123 /* Replaced ra_customer_relationships with HZ Table , to fix the bug 1888440 */
2124
2125
2126 /* added the following ELSIF condition to fix the bug 2002486 */
2127
2128 ELSIF nvl(lcustomer_relations,'N') = 'A' THEN
2129 oe_debug_pub.add
2130 ('Cr: A',2);
2131
2132 SELECT 'VALID'
2133 INTO l_dummy
2134 From oe_invoice_to_orgs_v
2135 WHERE site_use_id = p_line_rec.invoice_to_org_id
2136 AND ROWNUM = 1;
2137
2138
2139 END IF;
2140
2141 EXCEPTION
2142 WHEN NO_DATA_FOUND THEN
2143 l_return_status := FND_API.G_RET_STS_ERROR;
2144 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2145 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2146 OE_Order_Util.Get_Attribute_Name('invoice_to_org_id'));
2147 OE_MSG_PUB.Add;
2148 WHEN OTHERS THEN
2149 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2150 OE_MSG_PUB.Add_Exc_Msg
2151 ( G_PKG_NAME ,
2152 'Record - Invoice To Org validation '
2153 );
2154 END IF;
2155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2156 END;
2157
2158 END IF;
2159
2160 -- Validation of Invoice To Contact Id.
2161 IF p_line_rec.invoice_to_contact_id IS NOT NULL
2162
2163 THEN
2164
2165 BEGIN
2166 oe_debug_pub.add('invoice_to_contact_id :'||to_char(p_line_rec.invoice_to_contact_id),2);
2167
2168 SELECT /* MOAC_SQL_CHANGE */ 'VALID'
2169 INTO l_dummy
2170 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
2171 , HZ_CUST_ACCT_SITES ACCT_SITE
2172 , HZ_CUST_SITE_USES_ALL INV
2173 WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_line_rec.invoice_to_contact_id
2174 AND ACCT_ROLE.CUST_ACCOUNT_ID = ACCT_SITE.CUST_ACCOUNT_ID
2175 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
2176 AND ACCT_SITE.CUST_ACCT_SITE_ID = INV.CUST_ACCT_SITE_ID
2177 AND INV.SITE_USE_ID = p_line_rec.invoice_to_org_id
2178 AND ROWNUM = 1;
2179
2180 /* Replaced ra_contacts , ra_addresses and ra_site_uses with HZ Tables , to fix the bug 1888440 */
2181
2182
2183 EXCEPTION
2184 WHEN NO_DATA_FOUND THEN
2185 l_return_status := FND_API.G_RET_STS_ERROR;
2186 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2187 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2188 OE_Order_Util.Get_Attribute_Name('invoice_to_contact_id'));
2189 OE_MSG_PUB.Add;
2190 WHEN OTHERS THEN
2191 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2192 OE_MSG_PUB.Add_Exc_Msg
2193 ( G_PKG_NAME ,
2194 'Record - Invoice To Contact validation '
2195 );
2196 END IF;
2197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2198 END;
2199
2200 END IF;
2201
2202
2203 /* Added by Manish */
2204
2205 -- Validating Tax Information
2206 IF p_line_rec.tax_code IS NOT NULL AND
2207 p_line_rec.tax_date IS NOT NULL
2208
2209 THEN
2210 BEGIN
2211 -- eBTax changes
2212 /* IF oe_code_control.code_release_level < '110510' THEN
2213 SELECT 'VALID'
2214 INTO l_dummy
2215 FROM AR_VAT_TAX V,
2216 AR_SYSTEM_PARAMETERS P
2217 WHERE V.TAX_CODE = p_line_rec.tax_code
2218 AND V.SET_OF_BOOKS_ID = P.SET_OF_BOOKS_ID
2219 AND NVL(V.TAX_CLASS,'O')='O'
2220 AND NVL(V.DISPLAYED_FLAG,'Y')='Y'
2221 AND ROWNUM = 1;
2222 ELSE
2223 l_AR_Sys_Param_Rec := OE_Sys_Parameters_Pvt.Get_AR_Sys_Params(p_line_rec.org_id);
2224 l_sob_id := l_AR_Sys_Param_Rec.set_of_books_id;
2225 SELECT 'VALID'
2226 INTO l_dummy
2227 FROM AR_VAT_TAX V
2228 WHERE V.TAX_CODE = p_line_rec.tax_code
2229 AND V.SET_OF_BOOKS_ID = l_sob_id
2230 AND NVL(V.TAX_CLASS,'O')='O'
2231 AND NVL(V.DISPLAYED_FLAG,'Y')='Y'
2232 AND ROWNUM = 1;
2233 END IF;*/
2234
2235 SELECT 'VALID'
2236 INTO l_dummy
2237 FROM ZX_OUTPUT_CLASSIFICATIONS_V lk
2238 WHERE lk.lookup_code = p_line_rec.tax_code
2239 --AND lk.lookup_type = 'ZX_OUTPUT_CLASSIFICATIONS'
2240 AND lk.ENABLED_FLAG ='Y'
2241 AND lk.ORG_ID IN (p_line_rec.org_id, -99)
2242 AND TRUNC(p_line_rec.tax_date) BETWEEN TRUNC(lk.START_DATE_ACTIVE)
2243 AND TRUNC(NVL(lk.END_DATE_ACTIVE, p_line_rec.tax_date))
2244 AND ROWNUM = 1;
2245
2246
2247 EXCEPTION
2248
2249 WHEN NO_DATA_FOUND THEN
2250 l_return_status := FND_API.G_RET_STS_ERROR;
2251 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2252 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2253 OE_Order_Util.Get_Attribute_Name('TAX_CODE'));
2254 OE_MSG_PUB.Add;
2255
2256 WHEN OTHERS THEN
2257 IF OE_MSG_PUB.Check_Msg_Level (
2258 OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2259 THEN
2260 OE_MSG_PUB.Add_Exc_Msg
2261 ( G_PKG_NAME ,
2262 'Record - Tax Code validation '
2263 );
2264 END IF;
2265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2266
2267 END; -- BEGIN
2268 END IF;
2269
2270 -- If the Tax handling is "Exempt"
2271
2272 IF p_line_rec.tax_exempt_flag = 'E'
2273 THEN
2274 -- Check for Tax exempt reason
2275 IF p_line_rec.tax_exempt_reason_code IS NULL
2276 THEN
2277 l_return_status := FND_API.G_RET_STS_ERROR;
2278 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_ERROR)
2279 THEN
2280 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
2281 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2282 OE_Order_Util.Get_Attribute_Name('TAX_EXEMPT_REASON_CODE'));
2283 OE_MSG_PUB.Add;
2284 END IF;
2285 END IF;
2286 END IF; -- If Tax handling is exempt
2287
2288 -- If the TAX handling is STANDARD THEN we can not validate for
2289 -- exemption number because it can be a NULL value.
2290
2291
2292 -- If the Tax handling is "Required" then Tax Exempt Number and
2293 -- Tax Exempt Reason should be NULL.
2294
2295 IF p_line_rec.tax_exempt_flag = 'R' AND
2296 (p_line_rec.tax_exempt_number IS NOT NULL OR
2297 p_line_rec.tax_exempt_reason_code IS NOT NULL)
2298 THEN
2299 l_return_status := FND_API.G_RET_STS_ERROR;
2300 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_ERROR)
2301 THEN
2302 fnd_message.set_name('ONT','OE_TAX_EXEMPTION_NOT_ALLOWED');
2303 OE_MSG_PUB.Add;
2304 END IF;
2305
2306 END IF; -- If Tax handling is Required
2307
2308 -- Check for Tax Exempt number/ Tax Exempt reason code depends on
2309 -- following attributes if the Tax_exempt_flag = 'S' (Standard)
2310
2311 IF p_line_rec.tax_exempt_flag IS NOT NULL
2312
2313 THEN
2314
2315 BEGIN
2316 -- eBtax changes
2317 IF p_line_rec.tax_exempt_flag = 'S' and --* recheck (for 'E' ??)
2318 p_line_rec.tax_exempt_number IS NOT NULL and
2319 p_line_rec.tax_exempt_reason_code IS NOT NULL and
2320 p_line_rec.tax_code IS NOT NULL
2321 THEN
2322
2323 /* SELECT 'VALID'
2324 INTO l_dummy
2325 FROM OE_TAX_EXEMPTIONS_QP_V
2326 WHERE TAX_EXEMPT_NUMBER = p_line_rec.tax_exempt_number
2327 AND TAX_EXEMPT_REASON_CODE=p_line_rec.tax_exempt_reason_code
2328 AND SHIP_TO_ORG_ID = nvl(p_line_rec.ship_to_org_id,
2329 p_line_rec.invoice_to_org_id)
2330 AND BILL_TO_CUSTOMER_ID = p_line_rec.sold_to_org_id
2331 AND TAX_CODE = p_line_rec.tax_code
2332 AND STATUS_CODE = 'PRIMARY'
2333 AND ROWNUM = 1;*/
2334
2335
2336 open partyinfo(p_line_rec.invoice_to_org_id);
2337 fetch partyinfo into l_bill_to_cust_Acct_id,
2338 l_bill_to_party_id,
2339 l_bill_to_party_site_id,
2340 l_org_id;
2341 close partyinfo;
2342
2343 if p_line_rec.ship_to_org_id = p_line_rec.invoice_to_org_id then
2344 l_ship_to_cust_Acct_id := l_bill_to_cust_Acct_id;
2345 l_ship_to_party_id := l_bill_to_party_id;
2346 l_ship_to_party_site_id := l_bill_to_party_site_id ;
2347 else
2348 open partyinfo(p_line_rec.ship_to_org_id);
2349 fetch partyinfo into l_ship_to_cust_Acct_id,
2350 l_ship_to_party_id,
2351 l_ship_to_party_site_id,
2352 l_org_id;
2353 close partyinfo;
2354 end if;
2355
2356
2357 SELECT 'VALID'
2358 INTO l_dummy
2359 FROM ZX_EXEMPTIONS_V
2360 WHERE EXEMPT_CERTIFICATE_NUMBER = p_line_rec.tax_exempt_number
2361 AND EXEMPT_REASON_CODE = p_line_rec.tax_exempt_reason_code
2362 AND nvl(site_use_id,nvl(p_line_rec.ship_to_org_id,
2363 p_line_rec.invoice_to_org_id))
2364 = nvl(p_line_rec.ship_to_org_id,
2365 p_line_rec.invoice_to_org_id)
2366 AND nvl(cust_account_id, l_bill_to_cust_acct_id) = l_bill_to_cust_acct_id
2367 AND nvl(PARTY_SITE_ID,nvl(l_ship_to_party_site_id, l_bill_to_party_site_id))=
2368 nvl(l_ship_to_party_site_id, l_bill_to_party_site_id)
2369 AND org_id = l_org_id
2370 AND party_id = l_bill_to_party_id
2371 -- AND nvl(LEGAL_ENTITY_ID,-99) IN (nvl(l_legal_entity_id, legal_entity_id), -99)
2372 AND EXEMPTION_STATUS_CODE = 'PRIMARY'
2373
2374 -- **** Check with OM team whether the join based on date is required or not ****
2375 -- AND TRUNC(NVL(p_line_rec.request_date,sysdate))
2376 -- BETWEEN TRUNC(EFFECTIVE_FROM)
2377 -- AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_line_rec.request_date,sysdate)))
2378 AND ROWNUM = 1;
2379
2380 END IF;
2381
2382 -- Valid Tax Exempt Number.
2383
2384 EXCEPTION
2385
2386 WHEN NO_DATA_FOUND THEN
2387
2388 l_return_status := FND_API.G_RET_STS_ERROR;
2389
2390 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2391 THEN
2392 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
2393 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2394 OE_Order_Util.Get_Attribute_Name('TAX_EXEMPT_NUMBER'));
2395 OE_MSG_PUB.Add;
2396 END IF;
2397
2398 WHEN OTHERS THEN
2399 IF OE_MSG_PUB.Check_Msg_Level (
2400 OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2401 THEN
2402 OE_MSG_PUB.Add_Exc_Msg
2403 ( G_PKG_NAME ,
2404 'Record - Tax Exemptions '
2405 );
2406 END IF;
2407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2408
2409 END; -- BEGIN
2410
2411 END IF; -- Tax exempton info validation.
2412 /* Added by Manish */
2413
2414 -- order_quantity_uom should be primary uom for model/class/option.
2415 IF p_line_rec.order_quantity_uom is not null
2416
2417 THEN
2418
2419 IF ( p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_MODEL OR
2420 p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
2421 p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION ) THEN
2422 BEGIN
2423 SELECT primary_uom_code
2424 INTO l_uom
2425 FROM mtl_system_items
2426 WHERE inventory_item_id = p_line_rec.inventory_item_id
2427 AND organization_id = nvl(p_line_rec.ship_from_org_id,
2428 OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID'));
2429
2430
2431 oe_debug_pub.add('primary uom: '|| l_uom, 1);
2432 oe_debug_pub.add('uom entered: '||p_line_rec.order_quantity_uom , 1);
2433
2434 IF l_uom <> p_line_rec.order_quantity_uom
2435 THEN
2436 oe_debug_pub.add('uom other than primary uom is entered', 1);
2437
2438 fnd_message.set_name('ONT','OE_INVALID_ORDER_QUANTITY_UOM');
2439 fnd_message.set_token('ITEM',p_line_rec.ordered_item );
2440 fnd_message.set_token('UOM', l_uom);
2441 OE_MSG_PUB.Add;
2442 RAISE FND_API.G_EXC_ERROR;
2443 END IF;
2444 EXCEPTION
2445 when no_data_found then
2446 oe_debug_pub.add('OEXLLINB, no_data_found in uom validation', 1);
2447 RAISE FND_API.G_EXC_ERROR;
2448 END;
2449
2450 ELSE -- not ato related, validate item, uom combination
2451 SELECT count(*)
2452 INTO l_uom_count
2453 FROM mtl_item_uoms_view
2454 WHERE inventory_item_id = p_line_rec.inventory_item_id
2455 AND uom_code = p_line_rec.order_quantity_uom
2456 AND organization_id = nvl(p_line_rec.ship_from_org_id,
2457 OE_Sys_Parameters.VALUE_WNPS('MASTER_ORGANIZATION_ID'));
2458
2459
2460 IF l_uom_count = 0 THEN
2461 oe_debug_pub.add('uom/item combination invalid',2);
2462 fnd_message.set_name('ONT', 'OE_INVALID_ITEM_UOM');
2463 OE_MSG_PUB.Add;
2464 RAISE FND_API.G_EXC_ERROR;
2465 END IF;
2466 END IF;
2467 END IF;
2468
2469 If p_line_rec.agreement_id is not null and
2470 NOT OE_GLOBALS.EQUAL(p_line_rec.agreement_id, fnd_api.g_miss_num) then
2471 If not oe_globals.equal(p_line_rec.agreement_id,null) then
2472
2473 -- Check for Agreement +sold_org_id
2474
2475 -- Where cluase added to check start and end date for agreements
2476 -- Geresh
2477
2478 BEGIN
2479 BEGIN
2480 select list_type_code
2481 into l_list_type_code
2482 from qp_list_headers_vl
2483 where list_header_id = p_line_rec.price_list_id;
2484 EXCEPTION WHEN NO_DATA_FOUND THEN
2485 null;
2486 END;
2487
2488 BEGIN
2489 SELECT name ,sold_to_org_id , price_list_id
2490 INTO l_agreement_name,l_sold_to_org,l_price_list_id
2491 FROM oe_agreements_v
2492 WHERE agreement_id = p_line_rec.agreement_id;
2493 EXCEPTION WHEN NO_DATA_FOUND THEN
2494 null;
2495 END;
2496
2497
2498 IF NOT OE_GLOBALS.EQUAL(l_list_type_code,'PRL') THEN
2499 -- any price list with 'PRL' type should be allowed to
2500 -- be associated with any agreement according to bug 1386406.
2501 IF NOT OE_GLOBALS.EQUAL(l_price_list_id, p_line_rec.price_list_id) THEN
2502 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT_PLIST');
2503 fnd_message.set_Token('AGREEMENT_NAME', l_agreement_name);
2504 BEGIN
2505 SELECT name
2506 INTO l_price_list_name
2507 FROM qp_List_headers_vl
2508 WHERE list_header_id = p_line_rec.price_list_id;
2509
2510 Exception when no_data_found then
2511 l_price_list_name := p_line_rec.price_list_id;
2512 END;
2513 fnd_message.set_Token('PRICE_LIST1', l_price_list_name);
2514 BEGIN
2515
2516 SELECT name
2517 INTO l_price_list_name
2518 FROM QP_List_headers_vl
2519 WHERE list_header_id = l_price_list_id;
2520 EXCEPTION
2521 WHEN NO_DATA_FOUND THEN
2522 l_price_list_name := l_price_list_id;
2523 END;
2524 fnd_message.set_Token('PRICE_LIST2', l_price_list_name);
2525 OE_MSG_PUB.Add;
2526 oe_debug_pub.add('Invalid Agreement +price_list_id combination',2);
2527 raise FND_API.G_EXC_ERROR;
2528 END IF;
2529 END IF; -- end of if l_list_type_code <> 'PRL'
2530
2531
2532 -- modified by lkxu, to check for customer relationships.
2533 IF l_sold_to_org IS NOT NULL AND l_sold_to_org <> -1
2534 AND NOT OE_GLOBALS.EQUAL(l_sold_to_org,p_line_rec.sold_to_org_id) THEN
2535 IF nvl(lcustomer_relations,'N') = 'N' THEN
2536 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT');
2537 fnd_message.set_Token('AGREEMENT_ID', p_line_rec.agreement_id);
2538 fnd_message.set_Token('AGREEMENT_NAME', l_agreement_name);
2539 fnd_message.set_Token('CUSTOMER_ID', p_line_rec.sold_to_org_id);
2540 OE_MSG_PUB.Add;
2541 oe_debug_pub.add('Invalid Agreement +sold_org_id combination',2);
2542 RAISE FND_API.G_EXC_ERROR;
2543 ELSIF lcustomer_relations = 'Y' THEN
2544
2545 BEGIN
2546 SELECT 'VALID'
2547 INTO l_dummy
2548 FROM dual
2549 WHERE exists(
2550 select 'x' from
2551 HZ_CUST_ACCT_RELATE
2552 where RELATED_CUST_ACCOUNT_ID = p_line_rec.sold_to_org_id
2553 AND CUST_ACCOUNT_ID = l_sold_to_org
2554
2555 );
2556
2557 oe_debug_pub.add('Linda -- l_dummy is: '||l_dummy,2);
2558 /* Replaced ra_customer_relationships with HZ Table to fix the bug 1888440 */
2559
2560 EXCEPTION
2561 WHEN NO_DATA_FOUND THEN
2562 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT');
2563 fnd_message.set_Token('AGREEMENT_ID', p_line_rec.agreement_id);
2564 fnd_message.set_Token('AGREEMENT_NAME', l_agreement_name);
2565 fnd_message.set_Token('CUSTOMER_ID', p_line_rec.sold_to_org_id);
2566 OE_MSG_PUB.Add;
2567 oe_debug_pub.add('Invalid Agreement +sold_org_id combination',2);
2568 RAISE FND_API.G_EXC_ERROR;
2569 END;
2570 END IF;
2571 END IF;
2572
2573
2574 EXCEPTION
2575 WHEN NO_DATA_FOUND THEN
2576 fnd_message.set_name('ONT', 'OE_INVALID_AGREEMENT');
2577 fnd_message.set_Token('AGREEMENT_ID', p_line_rec.agreement_id);
2578 fnd_message.set_Token('AGREEMENT_NAME', l_agreement_name);
2579 fnd_message.set_Token('CUSTOMER_ID', l_sold_to_org);
2580 OE_MSG_PUB.Add;
2581 oe_debug_pub.add('Invalid Agreement +sold_org_id combination',2);
2582 RAISE FND_API.G_EXC_ERROR;
2583 END;
2584 END IF; -- Agreement has changed
2585
2586 ELSE
2587
2588 /*IF NOT oe_globals.equal(p_line_rec.pricing_date,p_old_line_rec.pricing_date) OR
2589 not oe_globals.equal(p_line_rec.price_list_id,p_old_line_rec.price_list_id) THEN*/
2590
2591
2592 -- Allow only the non agreement price_lists
2593 BEGIN
2594 oe_debug_pub.add('Pricing date is '||p_line_rec.pricing_date,2);
2595 -- modified by lkxu: to select from qp_list_headers_vl instead
2596 -- of from qp_price_lists_v to select only PRL type list headers.
2597
2598 SELECT name
2599 INTO l_price_list_name
2600 FROM qp_list_headers_vl
2601 WHERE list_header_id = p_line_rec.price_list_id
2602 AND list_type_code = 'PRL';
2603
2604 EXCEPTION
2605 WHEN NO_DATA_FOUND THEN
2606 fnd_message.set_name('ONT', 'OE_INVALID_NONAGR_PLIST');
2607 fnd_message.set_Token('PRICE_LIST1', p_line_rec.price_list_id);
2608 fnd_message.set_Token('PRICING_DATE', p_line_rec.pricing_date);
2609 OE_MSG_PUB.Add;
2610 oe_debug_pub.add('Invalid non agreement price list ',2);
2611 RAISE FND_API.G_EXC_ERROR;
2612 END;
2613
2614 --END IF; -- Price list or pricing date has changed
2615 END IF;
2616
2617 oe_debug_pub.add('15 '||l_return_status ,1);
2618
2619 -- Line number validation.
2620 -- Allow line number updates only on Model, Standard, Kit,
2621 --and stand alone service line.
2622
2623 /*IF p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
2624
2625 IF (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_OPTION) OR
2626 (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS) OR
2627 (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT) OR
2628 (p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_SERVICE AND
2629 p_line_rec.service_reference_line_id IS NOT NULL AND
2630 p_line_rec.service_reference_line_id <> FND_API.G_MISS_NUM)
2631
2632 THEN
2633
2634 IF (NOT OE_GLOBALS.EQUAL(p_line_rec.line_number,null)) THEN
2635
2636 l_return_status := FND_API.G_RET_STS_ERROR;
2637 fnd_message.set_name('ONT', 'OE_LINE_NUMBER_UPD');
2638 OE_MSG_PUB.add;
2639
2640 END IF;
2641 END IF;
2642
2643 END IF;
2644 */
2645
2646 oe_debug_pub.add('16 '||l_return_status ,1);
2647
2648 /*IF p_line_rec.top_model_line_id is not null AND
2649 p_line_rec.operation = OE_GLOBALS.G_OPR_UPDATE AND
2650 p_line_rec.ordered_quantity = 0
2651 THEN
2652 oe_debug_pub.add
2653 ('qty of a configuration related line 0'|| p_line_rec.item_type_code, 1);
2654 END IF;
2655 */
2656
2657 oe_debug_pub.add('OEXLLINB, RR:T2',1);
2658 oe_debug_pub.add('17 '||l_return_status ,1);
2659
2660 -- Validate ordered quantity for OTA lines. OTA Lines are
2661 -- identified by item_type_code of training. The ordered
2662 -- quantity cannot be greater than 1 for OTA lines.
2663
2664 l_order_quantity_uom := p_line_rec.order_quantity_uom;
2665 l_is_ota_line := OE_OTA_UTIL.Is_OTA_Line(l_order_quantity_uom);
2666
2667 IF (l_is_ota_line) AND
2668 p_line_rec.ordered_quantity > 1 then
2669
2670 oe_debug_pub.add('Ordered Qty cannot be greater than 1 for OTA lines',
2671 1);
2672 l_return_status := FND_API.G_RET_STS_ERROR;
2673 FND_Message.Set_Name('ONT', 'OE_OTA_INVALID_QTY');
2674 oe_msg_pub.add;
2675 END IF;
2676
2677 /* End of validation for OTA */
2678
2679
2680 -- Fix bug 1162304: issue a warning message if the PO number
2681 -- is being referenced by another order
2682 IF p_line_rec.cust_po_number IS NOT NULL
2683
2684 THEN
2685
2686 IF OE_CNCL_Validate_Header.Is_Duplicate_PO_Number
2687 (p_line_rec.cust_po_number
2688 ,p_line_rec.sold_to_org_id
2689 ,p_line_rec.header_id )
2690 THEN
2691 FND_MESSAGE.SET_NAME('ONT','OE_VAL_DUP_PO_NUMBER');
2692 OE_MSG_PUB.ADD;
2693 END IF;
2694
2695 END IF;
2696 -- End of check for duplicate PO number
2697
2698
2699 -- Fix for bug#1411346:
2700 -- SERVICE end date must be after service start date.
2701
2702 IF (p_line_rec.service_end_date <> FND_API.G_MISS_DATE OR
2703 p_line_rec.service_end_date IS NOT NULL) AND
2704 (p_line_rec.service_start_date <> FND_API.G_MISS_DATE OR
2705 p_line_rec.service_start_date IS NOT NULL) THEN
2706
2707 IF (p_line_rec.service_end_date <= p_line_rec.service_start_date)
2708 THEN
2709 l_return_status := FND_API.G_RET_STS_ERROR;
2710 fnd_message.set_name('ONT','OE_SERV_END_DATE');
2711 OE_MSG_PUB.Add;
2712 END IF;
2713
2714 END IF;
2715
2716 oe_debug_pub.add('18 '||l_return_status ,1);
2717 x_return_status := l_return_status;
2718
2719 -- Done validating entity
2720 oe_debug_pub.add('Exit OE_CNCL_VALIDATE_LINE.ENTITY',1);
2721 EXCEPTION
2722
2723 WHEN FND_API.G_EXC_ERROR THEN
2724
2725 x_return_status := FND_API.G_RET_STS_ERROR;
2726
2727 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2728
2729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2730
2731 WHEN OTHERS THEN
2732
2733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2734
2735 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2736 THEN
2737 OE_MSG_PUB.Add_Exc_Msg
2738 ( G_PKG_NAME
2739 , 'Entity'
2740 );
2741 END IF;
2742
2743 END Entity;
2744
2745 -- Procedure Attributes
2746
2747 PROCEDURE Attributes
2748 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2749 , p_x_line_rec IN OUT NOCOPY OE_Order_PUB.Line_Rec_Type
2750 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2751 )
2752 IS
2753 l_return_status VARCHAR2(1);
2754 l_line_rec OE_Order_PUB.Line_Rec_Type := OE_Order_PUB.G_MISS_LINE_REC;
2755 l_type_code VARCHAR2(30);
2756 BEGIN
2757 oe_debug_pub.add('Enter procedure OE_CNCL_VALIDATE_line.Attributes',1);
2758
2759 x_return_status := FND_API.G_RET_STS_SUCCESS;
2760 l_return_status := FND_API.G_RET_STS_SUCCESS;
2761
2762 -- validate Sales Agreements Attributes
2763 IF p_x_line_rec.blanket_number IS NOT NULL
2764 and p_x_line_rec.blanket_line_number is NOT NULL
2765 THEN
2766 x_return_status := FND_API.G_RET_STS_ERROR;
2767 fnd_message.set_name('ONT', 'OE_BLKT_DISALLOW_CLOSE_REL');
2768 OE_MSG_PUB.add;
2769 END IF;
2770
2771 -- Validate line attributes
2772
2773 IF p_x_line_rec.accounting_rule_id IS NOT NULL
2774 THEN
2775 IF NOT OE_CNCL_Validate.Accounting_Rule(p_x_line_rec.accounting_rule_id) THEN
2776 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2777 p_x_line_rec.accounting_rule_id := NULL;
2778 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2779 p_x_line_rec.accounting_rule_id := FND_API.G_MISS_NUM;
2780 ELSE
2781 x_return_status := FND_API.G_RET_STS_ERROR;
2782 END IF;
2783 END IF;
2784 END IF;
2785
2786 IF p_x_line_rec.agreement_id IS NOT NULL
2787 THEN
2788 IF NOT OE_CNCL_Validate.Agreement(p_x_line_rec.agreement_id) THEN
2789 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2790 p_x_line_rec.agreement_id := NULL;
2791 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2792 p_x_line_rec.agreement_id := FND_API.G_MISS_NUM;
2793 ELSE
2794 x_return_status := FND_API.G_RET_STS_ERROR;
2795 END IF;
2796 END IF;
2797 END IF;
2798
2799
2800 IF p_x_line_rec.deliver_to_contact_id IS NOT NULL
2801 THEN
2802 IF NOT OE_CNCL_Validate.Deliver_To_Contact(p_x_line_rec.deliver_to_contact_id) THEN
2803 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2804 p_x_line_rec.deliver_to_contact_id := NULL;
2805 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2806 p_x_line_rec.deliver_to_contact_id := FND_API.G_MISS_NUM;
2807 ELSE
2808 x_return_status := FND_API.G_RET_STS_ERROR;
2809 END IF;
2810 END IF;
2811 END IF;
2812
2813 IF p_x_line_rec.deliver_to_org_id IS NOT NULL
2814 THEN
2815 IF NOT OE_CNCL_Validate.Deliver_To_Org(p_x_line_rec.deliver_to_org_id) THEN
2816 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2817 p_x_line_rec.deliver_to_org_id := NULL;
2818 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2819 p_x_line_rec.deliver_to_org_id := FND_API.G_MISS_NUM;
2820 ELSE
2821 x_return_status := FND_API.G_RET_STS_ERROR;
2822 END IF;
2823 END IF;
2824 END IF;
2825
2826 IF p_x_line_rec.demand_class_code IS NOT NULL
2827 THEN
2828 IF NOT OE_CNCL_Validate.Demand_Class(p_x_line_rec.demand_class_code) THEN
2829 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2830 p_x_line_rec.demand_class_code := NULL;
2831 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2832 p_x_line_rec.demand_class_code := FND_API.G_MISS_CHAR;
2833 ELSE
2834 x_return_status := FND_API.G_RET_STS_ERROR;
2835 END IF;
2836 END IF;
2837 END IF;
2838
2839 IF p_x_line_rec.dep_plan_required_flag IS NOT NULL
2840 THEN
2841 IF NOT OE_CNCL_Validate.Dep_Plan_Required(p_x_line_rec.dep_plan_required_flag) THEN
2842 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2843 p_x_line_rec.dep_plan_required_flag := NULL;
2844 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2845 p_x_line_rec.dep_plan_required_flag := FND_API.G_MISS_CHAR;
2846 ELSE
2847 x_return_status := FND_API.G_RET_STS_ERROR;
2848 END IF;
2849 END IF;
2850 END IF;
2851
2852 IF p_x_line_rec.end_item_unit_number IS NOT NULL
2853 THEN
2854 IF NOT OE_CNCL_Validate.End_Item_Unit_Number(p_x_line_rec.end_item_unit_number) THEN
2855 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2856 p_x_line_rec.end_item_unit_number := NULL;
2857 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2858 p_x_line_rec.end_item_unit_number := FND_API.G_MISS_CHAR;
2859 ELSE
2860 x_return_status := FND_API.G_RET_STS_ERROR;
2861 END IF;
2862 END IF;
2863 END IF;
2864
2865 IF p_x_line_rec.fob_point_code IS NOT NULL
2866 THEN
2867 IF NOT OE_CNCL_Validate.Fob_Point(p_x_line_rec.fob_point_code) THEN
2868 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2869 p_x_line_rec.fob_point_code := NULL;
2870 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2871 p_x_line_rec.fob_point_code := FND_API.G_MISS_CHAR;
2872 ELSE
2873 x_return_status := FND_API.G_RET_STS_ERROR;
2874 END IF;
2875 END IF;
2876 END IF;
2877
2878 IF p_x_line_rec.freight_terms_code IS NOT NULL
2879 THEN
2880 IF NOT OE_CNCL_Validate.Freight_Terms(p_x_line_rec.freight_terms_code) THEN
2881 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2882 p_x_line_rec.freight_terms_code := NULL;
2883 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2884 p_x_line_rec.freight_terms_code := FND_API.G_MISS_CHAR;
2885 ELSE
2886 x_return_status := FND_API.G_RET_STS_ERROR;
2887 END IF;
2888 END IF;
2889 END IF;
2890
2891 IF p_x_line_rec.invoice_to_contact_id IS NOT NULL
2892 THEN
2893 IF NOT OE_CNCL_Validate.Invoice_To_Contact(p_x_line_rec.invoice_to_contact_id) THEN
2894 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2895 p_x_line_rec.invoice_to_contact_id := NULL;
2896 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2897 p_x_line_rec.invoice_to_contact_id := FND_API.G_MISS_NUM;
2898 ELSE
2899 x_return_status := FND_API.G_RET_STS_ERROR;
2900 END IF;
2901 END IF;
2902 END IF;
2903
2904 IF p_x_line_rec.invoice_to_org_id IS NOT NULL
2905 THEN
2906 IF NOT OE_CNCL_Validate.Invoice_To_Org(p_x_line_rec.invoice_to_org_id) THEN
2907 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2908 p_x_line_rec.invoice_to_org_id := NULL;
2909 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2910 p_x_line_rec.invoice_to_org_id := FND_API.G_MISS_NUM;
2911 ELSE
2912 x_return_status := FND_API.G_RET_STS_ERROR;
2913 END IF;
2914 END IF;
2915 END IF;
2916
2917 IF p_x_line_rec.invoicing_rule_id IS NOT NULL
2918 THEN
2919 IF NOT OE_CNCL_Validate.Invoicing_Rule(p_x_line_rec.invoicing_rule_id) THEN
2920 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2921 p_x_line_rec.invoicing_rule_id := NULL;
2922 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
2923 p_x_line_rec.invoicing_rule_id := FND_API.G_MISS_NUM;
2924 ELSE
2925 x_return_status := FND_API.G_RET_STS_ERROR;
2926 END IF;
2927 END IF;
2928 END IF;
2929
2930 --{added for bug 4240715
2931 IF p_x_line_rec.Ib_owner IS NOT NULL
2932 THEN
2933 IF NOT OE_CNCL_Validate.IB_OWNER(p_x_line_rec.Ib_owner) THEN
2934
2935 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
2936 p_x_line_rec.Ib_owner := NULL;
2937 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
2938 THEN
2939 p_x_line_rec.Ib_Owner := FND_API.G_MISS_CHAR;
2940 ELSE
2941 x_return_status := FND_API.G_RET_STS_ERROR;
2942 END IF;
2943 END IF;
2944 END IF;
2945
2946 IF p_x_line_rec.Ib_installed_at_location IS NOT NULL
2947 THEN
2948 IF NOT OE_CNCL_Validate.IB_INSTALLED_AT_LOCATION(p_x_line_rec.Ib_installed_at_location) THEN
2949 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
2950 THEN
2951 p_x_line_rec.Ib_installed_at_location := NULL;
2952 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
2953 THEN
2954 p_x_line_rec.Ib_installed_at_location := FND_API.G_MISS_CHAR;
2955 ELSE
2956 x_return_status := FND_API.G_RET_STS_ERROR;
2957 END IF;
2958 END IF;
2959
2960 END IF;
2961
2962 IF p_x_line_rec.Ib_current_location IS NOT NULL
2963 THEN
2964
2965 IF NOT OE_CNCL_Validate.IB_CURRENT_LOCATION(p_x_line_rec.ib_current_location) THEN
2966 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
2967 THEN
2968 p_x_line_rec.Ib_current_location := NULL;
2969 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
2970 THEN
2971 p_x_line_rec.Ib_current_location := FND_API.G_MISS_CHAR;
2972 ELSE
2973 x_return_status := FND_API.G_RET_STS_ERROR;
2974 END IF;
2975 END IF;
2976 END IF;
2977
2978
2979 IF p_x_line_rec.End_customer_id IS NOT NULL THEN
2980
2981 IF NOT OE_CNCL_Validate.END_CUSTOMER(p_x_line_rec.End_customer_id) THEN
2982 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
2983 THEN
2984 p_x_line_rec.End_customer_id := NULL;
2985 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
2986 THEN
2987 p_x_line_rec.End_customer_id := FND_API.G_MISS_NUM;
2988 ELSE
2989 x_return_status := FND_API.G_RET_STS_ERROR;
2990 END IF;
2991 END IF;
2992
2993 END IF;
2994
2995
2996
2997 IF p_x_line_rec.End_customer_contact_id IS NOT NULL THEN
2998
2999 IF NOT OE_CNCL_Validate.END_CUSTOMER_CONTACT(p_x_line_rec.End_customer_contact_id) THEN
3000 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
3001 THEN
3002 p_x_line_rec.End_customer_contact_id := NULL;
3003 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
3004 THEN
3005 p_x_line_rec.End_customer_contact_id := FND_API.G_MISS_NUM;
3006 ELSE
3007 x_return_status := FND_API.G_RET_STS_ERROR;
3008 END IF;
3009 END IF;
3010 END IF;
3011
3012 IF p_x_line_rec.End_customer_site_use_id IS NOT NULL
3013 THEN
3014 IF NOT OE_CNCL_Validate.END_CUSTOMER_SITE_USE(p_x_line_rec.End_customer_site_use_id) THEN
3015 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL
3016 THEN
3017 p_x_line_rec.End_customer_site_use_id := NULL;
3018 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF
3019 THEN
3020 p_x_line_rec.End_customer_site_use_id := FND_API.G_MISS_NUM;
3021 ELSE
3022 x_return_status := FND_API.G_RET_STS_ERROR;
3023 END IF;
3024 END IF;
3025 END IF;
3026 -- bug 4240715}
3027
3028 IF p_x_line_rec.item_type_code IS NOT NULL
3029 THEN
3030 IF NOT OE_CNCL_Validate.Item_Type(p_x_line_rec.item_type_code) THEN
3031 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3032 p_x_line_rec.item_type_code := NULL;
3033 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3034 p_x_line_rec.item_type_code := FND_API.G_MISS_CHAR;
3035 ELSE
3036 x_return_status := FND_API.G_RET_STS_ERROR;
3037 END IF;
3038 END IF;
3039 END IF;
3040
3041 IF p_x_line_rec.payment_term_id IS NOT NULL
3042 THEN
3043 IF NOT OE_CNCL_Validate.Payment_Term(p_x_line_rec.payment_term_id) THEN
3044 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3045 p_x_line_rec.payment_term_id := NULL;
3046 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3047 p_x_line_rec.payment_term_id := FND_API.G_MISS_NUM;
3048 ELSE
3049 x_return_status := FND_API.G_RET_STS_ERROR;
3050 END IF;
3051 END IF;
3052 END IF;
3053
3054 IF p_x_line_rec.price_list_id IS NOT NULL
3055 THEN
3056 IF NOT OE_CNCL_Validate.Price_List(p_x_line_rec.price_list_id) THEN
3057 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3058 p_x_line_rec.price_list_id := NULL;
3059 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3060 p_x_line_rec.price_list_id := FND_API.G_MISS_NUM;
3061 ELSE
3062 x_return_status := FND_API.G_RET_STS_ERROR;
3063 END IF;
3064 END IF;
3065 END IF;
3066
3067 IF p_x_line_rec.project_id IS NOT NULL
3068 THEN
3069 IF NOT OE_CNCL_Validate.Project(p_x_line_rec.project_id) THEN
3070 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3071 p_x_line_rec.project_id := NULL;
3072 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3073 p_x_line_rec.project_id := FND_API.G_MISS_NUM;
3074 ELSE
3075 x_return_status := FND_API.G_RET_STS_ERROR;
3076 END IF;
3077 END IF;
3078 END IF;
3079
3080 IF p_x_line_rec.shipment_priority_code IS NOT NULL
3081 THEN
3082 IF NOT OE_CNCL_Validate.Shipment_Priority(p_x_line_rec.shipment_priority_code) THEN
3083 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3084 p_x_line_rec.shipment_priority_code := NULL;
3085 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3086 p_x_line_rec.shipment_priority_code := FND_API.G_MISS_CHAR;
3087 ELSE
3088 x_return_status := FND_API.G_RET_STS_ERROR;
3089 END IF;
3090 END IF;
3091 END IF;
3092
3093 IF p_x_line_rec.shipping_method_code IS NOT NULL
3094 THEN
3095 IF NOT OE_CNCL_Validate.Shipping_Method(p_x_line_rec.shipping_method_code) THEN
3096 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3097 p_x_line_rec.shipping_method_code := NULL;
3098 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3099 p_x_line_rec.shipping_method_code := FND_API.G_MISS_CHAR;
3100 ELSE
3101 x_return_status := FND_API.G_RET_STS_ERROR;
3102 END IF;
3103 END IF;
3104 END IF;
3105
3106 IF p_x_line_rec.ship_from_org_id IS NOT NULL
3107 THEN
3108 IF NOT OE_CNCL_Validate.Ship_From_Org(p_x_line_rec.ship_from_org_id) THEN
3109 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3110 p_x_line_rec.ship_from_org_id := NULL;
3111 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3112 p_x_line_rec.ship_from_org_id := FND_API.G_MISS_NUM;
3113 ELSE
3114 x_return_status := FND_API.G_RET_STS_ERROR;
3115 END IF;
3116 END IF;
3117 END IF;
3118
3119 IF p_x_line_rec.shipping_interfaced_flag IS NOT NULL
3120 THEN
3121 IF NOT OE_CNCL_Validate.Shipping_Interfaced(p_x_line_rec.shipping_interfaced_flag) THEN
3122 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3123 p_x_line_rec.shipping_interfaced_flag := NULL;
3124 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3125 p_x_line_rec.shipping_interfaced_flag := FND_API.G_MISS_CHAR;
3126 ELSE
3127 x_return_status := FND_API.G_RET_STS_ERROR;
3128 END IF;
3129 END IF;
3130 END IF;
3131
3132 IF p_x_line_rec.shippable_flag IS NOT NULL
3133 THEN
3134 IF NOT OE_CNCL_Validate.shippable(p_x_line_rec.shippable_flag) THEN
3135 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3136 p_x_line_rec.shippable_flag := NULL;
3137 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3138 p_x_line_rec.shippable_flag := FND_API.G_MISS_CHAR;
3139 ELSE
3140 x_return_status := FND_API.G_RET_STS_ERROR;
3141 END IF;
3142 END IF;
3143 END IF;
3144
3145 IF p_x_line_rec.ship_to_contact_id IS NOT NULL
3146 THEN
3147 IF NOT OE_CNCL_Validate.Ship_To_Contact(p_x_line_rec.ship_to_contact_id) THEN
3148 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3149 p_x_line_rec.ship_to_contact_id := NULL;
3150 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3151 p_x_line_rec.ship_to_contact_id := FND_API.G_MISS_NUM;
3152 ELSE
3153 x_return_status := FND_API.G_RET_STS_ERROR;
3154 END IF;
3155 END IF;
3156 END IF;
3157
3158 IF p_x_line_rec.ship_to_org_id IS NOT NULL
3159 THEN
3160 IF NOT OE_CNCL_Validate.Ship_To_Org(p_x_line_rec.ship_to_org_id) THEN
3161 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3162 p_x_line_rec.ship_to_org_id := NULL;
3163 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3164 p_x_line_rec.ship_to_org_id := FND_API.G_MISS_NUM;
3165 ELSE
3166 x_return_status := FND_API.G_RET_STS_ERROR;
3167 END IF;
3168 END IF;
3169 END IF;
3170
3171 IF p_x_line_rec.sold_to_org_id IS NOT NULL
3172 THEN
3173 IF NOT OE_CNCL_Validate.Sold_To_Org(p_x_line_rec.sold_to_org_id) THEN
3174 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3175 p_x_line_rec.sold_to_org_id := NULL;
3176 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3177 p_x_line_rec.sold_to_org_id := FND_API.G_MISS_NUM;
3178 ELSE
3179 x_return_status := FND_API.G_RET_STS_ERROR;
3180 END IF;
3181 END IF;
3182 END IF;
3183
3184 IF p_x_line_rec.source_type_code IS NOT NULL
3185 THEN
3186 IF NOT OE_CNCL_Validate.Source_Type(p_x_line_rec.source_type_code) THEN
3187 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3188 p_x_line_rec.source_type_code := NULL;
3189 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3190 p_x_line_rec.source_type_code := FND_API.G_MISS_CHAR;
3191 ELSE
3192 x_return_status := FND_API.G_RET_STS_ERROR;
3193 END IF;
3194 END IF;
3195 END IF;
3196
3197 IF p_x_line_rec.tax_exempt_flag IS NOT NULL
3198 THEN
3199 IF NOT OE_CNCL_Validate.Tax_Exempt(p_x_line_rec.tax_exempt_flag) THEN
3200 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3201 p_x_line_rec.tax_exempt_flag := NULL;
3202 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3203 p_x_line_rec.tax_exempt_flag := FND_API.G_MISS_CHAR;
3204 ELSE
3205 x_return_status := FND_API.G_RET_STS_ERROR;
3206 END IF;
3207 END IF;
3208 END IF;
3209
3210 IF p_x_line_rec.tax_exempt_reason_code IS NOT NULL
3211 THEN
3212 IF NOT OE_CNCL_Validate.Tax_Exempt_Reason(p_x_line_rec.tax_exempt_reason_code) THEN
3213 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3214 p_x_line_rec.tax_exempt_reason_code := NULL;
3215 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3216 p_x_line_rec.tax_exempt_reason_code := FND_API.G_MISS_CHAR;
3217 ELSE
3218 x_return_status := FND_API.G_RET_STS_ERROR;
3219 END IF;
3220 END IF;
3221 END IF;
3222
3223 IF p_x_line_rec.tax_point_code IS NOT NULL
3224 THEN
3225 IF NOT OE_CNCL_Validate.Tax_Point(p_x_line_rec.tax_point_code) THEN
3226 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3227 p_x_line_rec.tax_point_code := NULL;
3228 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3229 p_x_line_rec.tax_point_code := FND_API.G_MISS_CHAR;
3230 ELSE
3231 x_return_status := FND_API.G_RET_STS_ERROR;
3232 END IF;
3233 END IF;
3234 END IF;
3235
3236 IF p_x_line_rec.fulfilled_flag IS NOT NULL
3237 THEN
3238 IF NOT OE_CNCL_Validate.fulfilled(p_x_line_rec.fulfilled_flag) THEN
3239 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3240 p_x_line_rec.fulfilled_flag := NULL;
3241 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3242 p_x_line_rec.fulfilled_flag := FND_API.G_MISS_CHAR;
3243 ELSE
3244 x_return_status := FND_API.G_RET_STS_ERROR;
3245 END IF;
3246 END IF;
3247 END IF;
3248
3249 IF p_x_line_rec.flow_status_code IS NOT NULL
3250 THEN
3251 IF NOT OE_CNCL_Validate.Line_Flow_Status(p_x_line_rec.flow_status_code) THEN
3252 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3253 p_x_line_rec.flow_status_code := NULL;
3254 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3255 p_x_line_rec.flow_status_code := FND_API.G_MISS_CHAR;
3256 ELSE
3257 x_return_status := FND_API.G_RET_STS_ERROR;
3258 END IF;
3259 END IF;
3260 END IF;
3261
3262 oe_debug_pub.add('before flex: '||x_return_status,2);
3263 IF p_x_line_rec.attribute1 IS NOT NULL
3264 OR p_x_line_rec.attribute10 IS NOT NULL
3265 OR p_x_line_rec.attribute11 IS NOT NULL
3266 OR p_x_line_rec.attribute12 IS NOT NULL
3267 OR p_x_line_rec.attribute13 IS NOT NULL
3268 OR p_x_line_rec.attribute14 IS NOT NULL
3269 OR p_x_line_rec.attribute15 IS NOT NULL
3270 OR p_x_line_rec.attribute16 IS NOT NULL --For bug 2184255
3271 OR p_x_line_rec.attribute17 IS NOT NULL
3272 OR p_x_line_rec.attribute18 IS NOT NULL
3273 OR p_x_line_rec.attribute19 IS NOT NULL
3274 OR p_x_line_rec.attribute2 IS NOT NULL
3275 OR p_x_line_rec.attribute20 IS NOT NULL
3276 OR p_x_line_rec.attribute3 IS NOT NULL
3277 OR p_x_line_rec.attribute4 IS NOT NULL
3278 OR p_x_line_rec.attribute5 IS NOT NULL
3279 OR p_x_line_rec.attribute6 IS NOT NULL
3280 OR p_x_line_rec.attribute7 IS NOT NULL
3281 OR p_x_line_rec.attribute8 IS NOT NULL
3282 OR p_x_line_rec.attribute9 IS NOT NULL
3283 OR p_x_line_rec.context IS NOT NULL
3284 THEN
3285
3286 oe_debug_pub.add('Before calling line_desc_flex',2);
3287 IF NOT OE_CNCL_Validate.Line_Desc_Flex
3288 (p_context => p_x_line_rec.context
3289 ,p_attribute1 => p_x_line_rec.attribute1
3290 ,p_attribute2 => p_x_line_rec.attribute2
3291 ,p_attribute3 => p_x_line_rec.attribute3
3292 ,p_attribute4 => p_x_line_rec.attribute4
3293 ,p_attribute5 => p_x_line_rec.attribute5
3294 ,p_attribute6 => p_x_line_rec.attribute6
3295 ,p_attribute7 => p_x_line_rec.attribute7
3296 ,p_attribute8 => p_x_line_rec.attribute8
3297 ,p_attribute9 => p_x_line_rec.attribute9
3298 ,p_attribute10 => p_x_line_rec.attribute10
3299 ,p_attribute11 => p_x_line_rec.attribute11
3300 ,p_attribute12 => p_x_line_rec.attribute12
3301 ,p_attribute13 => p_x_line_rec.attribute13
3302 ,p_attribute14 => p_x_line_rec.attribute14
3303 ,p_attribute15 => p_x_line_rec.attribute15
3304 ,p_attribute16 => p_x_line_rec.attribute16 -- for bug 2184255
3305 ,p_attribute17 => p_x_line_rec.attribute17
3306 ,p_attribute18 => p_x_line_rec.attribute18
3307 ,p_attribute19 => p_x_line_rec.attribute19
3308 ,p_attribute20 => p_x_line_rec.attribute20) THEN
3309
3310 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3311
3312
3313 p_x_line_rec.context := null;
3314 p_x_line_rec.attribute1 := null;
3315 p_x_line_rec.attribute2 := null;
3316 p_x_line_rec.attribute3 := null;
3317 p_x_line_rec.attribute4 := null;
3318 p_x_line_rec.attribute5 := null;
3319 p_x_line_rec.attribute6 := null;
3320 p_x_line_rec.attribute7 := null;
3321 p_x_line_rec.attribute8 := null;
3322 p_x_line_rec.attribute9 := null;
3323 p_x_line_rec.attribute10 := null;
3324 p_x_line_rec.attribute11 := null;
3325 p_x_line_rec.attribute12 := null;
3326 p_x_line_rec.attribute13 := null;
3327 p_x_line_rec.attribute14 := null;
3328 p_x_line_rec.attribute15 := null;
3329 p_x_line_rec.attribute16 := null; -- for bug 2184255
3330 p_x_line_rec.attribute17 := null;
3331 p_x_line_rec.attribute18 := null;
3332 p_x_line_rec.attribute19 := null;
3333 p_x_line_rec.attribute20 := null;
3334
3335 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3336 p_x_line_rec.context := FND_API.G_MISS_CHAR;
3337 p_x_line_rec.attribute1 := FND_API.G_MISS_CHAR;
3338 p_x_line_rec.attribute2 := FND_API.G_MISS_CHAR;
3339 p_x_line_rec.attribute3 := FND_API.G_MISS_CHAR;
3340 p_x_line_rec.attribute4 := FND_API.G_MISS_CHAR;
3341 p_x_line_rec.attribute5 := FND_API.G_MISS_CHAR;
3342 p_x_line_rec.attribute6 := FND_API.G_MISS_CHAR;
3343 p_x_line_rec.attribute7 := FND_API.G_MISS_CHAR;
3344 p_x_line_rec.attribute8 := FND_API.G_MISS_CHAR;
3345 p_x_line_rec.attribute9 := FND_API.G_MISS_CHAR;
3346 p_x_line_rec.attribute10 := FND_API.G_MISS_CHAR;
3347 p_x_line_rec.attribute11 := FND_API.G_MISS_CHAR;
3348 p_x_line_rec.attribute12 := FND_API.G_MISS_CHAR;
3349 p_x_line_rec.attribute13 := FND_API.G_MISS_CHAR;
3350 p_x_line_rec.attribute14 := FND_API.G_MISS_CHAR;
3351 p_x_line_rec.attribute15 := FND_API.G_MISS_CHAR;
3352 p_x_line_rec.attribute16 := FND_API.G_MISS_CHAR; -- for bug 2184255
3353 p_x_line_rec.attribute17 := FND_API.G_MISS_CHAR;
3354 p_x_line_rec.attribute18 := FND_API.G_MISS_CHAR;
3355 p_x_line_rec.attribute19 := FND_API.G_MISS_CHAR;
3356 p_x_line_rec.attribute20 := FND_API.G_MISS_CHAR;
3357 ELSE
3358
3359 x_return_status := FND_API.G_RET_STS_ERROR;
3360 END IF;
3361 END IF;
3362
3363 oe_debug_pub.add('After line_desc_flex ' || x_return_status,2);
3364
3365
3366
3367 END IF;
3368
3369
3370 IF p_x_line_rec.global_attribute1 IS NOT NULL
3371 OR p_x_line_rec.global_attribute10 IS NOT NULL
3372 OR p_x_line_rec.global_attribute11 IS NOT NULL
3373 OR p_x_line_rec.global_attribute12 IS NOT NULL
3374 OR p_x_line_rec.global_attribute13 IS NOT NULL
3375 OR p_x_line_rec.global_attribute14 IS NOT NULL
3376 OR p_x_line_rec.global_attribute15 IS NOT NULL
3377 OR p_x_line_rec.global_attribute16 IS NOT NULL
3378 OR p_x_line_rec.global_attribute17 IS NOT NULL
3379 OR p_x_line_rec.global_attribute18 IS NOT NULL
3380 OR p_x_line_rec.global_attribute19 IS NOT NULL
3381 OR p_x_line_rec.global_attribute2 IS NOT NULL
3382 OR p_x_line_rec.global_attribute20 IS NOT NULL
3383 OR p_x_line_rec.global_attribute3 IS NOT NULL
3384 OR p_x_line_rec.global_attribute4 IS NOT NULL
3385 OR p_x_line_rec.global_attribute5 IS NOT NULL
3386 OR p_x_line_rec.global_attribute6 IS NOT NULL
3387 OR p_x_line_rec.global_attribute7 IS NOT NULL
3388 OR p_x_line_rec.global_attribute8 IS NOT NULL
3389 OR p_x_line_rec.global_attribute9 IS NOT NULL
3390 OR p_x_line_rec.global_attribute_category IS NOT NULL
3391 THEN
3392
3393
3394
3395 OE_DEBUG_PUB.ADD('Before G_line_desc_flex',2);
3396 IF NOT OE_CNCL_Validate.G_Line_Desc_Flex
3397 (p_context => p_x_line_rec.global_attribute_category
3398 ,p_attribute1 => p_x_line_rec.global_attribute1
3399 ,p_attribute2 => p_x_line_rec.global_attribute2
3400 ,p_attribute3 => p_x_line_rec.global_attribute3
3401 ,p_attribute4 => p_x_line_rec.global_attribute4
3402 ,p_attribute5 => p_x_line_rec.global_attribute5
3403 ,p_attribute6 => p_x_line_rec.global_attribute6
3404 ,p_attribute7 => p_x_line_rec.global_attribute7
3405 ,p_attribute8 => p_x_line_rec.global_attribute8
3406 ,p_attribute9 => p_x_line_rec.global_attribute9
3407 ,p_attribute10 => p_x_line_rec.global_attribute10
3408 ,p_attribute11 => p_x_line_rec.global_attribute11
3409 ,p_attribute12 => p_x_line_rec.global_attribute12
3410 ,p_attribute13 => p_x_line_rec.global_attribute13
3411 ,p_attribute14 => p_x_line_rec.global_attribute13
3412 ,p_attribute15 => p_x_line_rec.global_attribute14
3413 ,p_attribute16 => p_x_line_rec.global_attribute16
3414 ,p_attribute17 => p_x_line_rec.global_attribute17
3415 ,p_attribute18 => p_x_line_rec.global_attribute18
3416 ,p_attribute19 => p_x_line_rec.global_attribute19
3417 ,p_attribute20 => p_x_line_rec.global_attribute20) THEN
3418
3419 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3420
3421 p_x_line_rec.global_attribute_category := null;
3422 p_x_line_rec.global_attribute1 := null;
3423 p_x_line_rec.global_attribute2 := null;
3424 p_x_line_rec.global_attribute3 := null;
3425 p_x_line_rec.global_attribute4 := null;
3426 p_x_line_rec.global_attribute5 := null;
3427 p_x_line_rec.global_attribute6 := null;
3428 p_x_line_rec.global_attribute7 := null;
3429 p_x_line_rec.global_attribute8 := null;
3430 p_x_line_rec.global_attribute9 := null;
3431 p_x_line_rec.global_attribute11 := null;
3432 p_x_line_rec.global_attribute12 := null;
3433 p_x_line_rec.global_attribute13 := null;
3434 p_x_line_rec.global_attribute14 := null;
3435 p_x_line_rec.global_attribute15 := null;
3436 p_x_line_rec.global_attribute16 := null;
3437 p_x_line_rec.global_attribute17 := null;
3438 p_x_line_rec.global_attribute18 := null;
3439 p_x_line_rec.global_attribute19 := null;
3440 p_x_line_rec.global_attribute20 := null;
3441
3442 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3443 p_x_line_rec.global_attribute_category := FND_API.G_MISS_CHAR;
3444 p_x_line_rec.global_attribute1 := FND_API.G_MISS_CHAR;
3445 p_x_line_rec.global_attribute2 := FND_API.G_MISS_CHAR;
3446 p_x_line_rec.global_attribute3 := FND_API.G_MISS_CHAR;
3447 p_x_line_rec.global_attribute4 := FND_API.G_MISS_CHAR;
3448 p_x_line_rec.global_attribute5 := FND_API.G_MISS_CHAR;
3449 p_x_line_rec.global_attribute6 := FND_API.G_MISS_CHAR;
3450 p_x_line_rec.global_attribute7 := FND_API.G_MISS_CHAR;
3451 p_x_line_rec.global_attribute8 := FND_API.G_MISS_CHAR;
3452 p_x_line_rec.global_attribute9 := FND_API.G_MISS_CHAR;
3453 p_x_line_rec.global_attribute11 := FND_API.G_MISS_CHAR;
3454 p_x_line_rec.global_attribute12 := FND_API.G_MISS_CHAR;
3455 p_x_line_rec.global_attribute13 := FND_API.G_MISS_CHAR;
3456 p_x_line_rec.global_attribute14 := FND_API.G_MISS_CHAR;
3457 p_x_line_rec.global_attribute15 := FND_API.G_MISS_CHAR;
3458 p_x_line_rec.global_attribute16 := FND_API.G_MISS_CHAR;
3459 p_x_line_rec.global_attribute17 := FND_API.G_MISS_CHAR;
3460 p_x_line_rec.global_attribute18 := FND_API.G_MISS_CHAR;
3461 p_x_line_rec.global_attribute19 := FND_API.G_MISS_CHAR;
3462 p_x_line_rec.global_attribute20 := FND_API.G_MISS_CHAR;
3463
3464 ELSE
3465
3466 x_return_status := FND_API.G_RET_STS_ERROR;
3467 END IF;
3468 END IF;
3469
3470 OE_DEBUG_PUB.ADD('After G_Line_desc_flex ' || x_return_status,2);
3471
3472 END IF;
3473
3474 IF p_x_line_rec.industry_attribute1 IS NOT NULL
3475 OR p_x_line_rec.industry_attribute10 IS NOT NULL
3476 OR p_x_line_rec.industry_attribute11 IS NOT NULL
3477 OR p_x_line_rec.industry_attribute12 IS NOT NULL
3478 OR p_x_line_rec.industry_attribute13 IS NOT NULL
3479 OR p_x_line_rec.industry_attribute14 IS NOT NULL
3480 OR p_x_line_rec.industry_attribute15 IS NOT NULL
3481 OR p_x_line_rec.industry_attribute16 IS NOT NULL
3482 OR p_x_line_rec.industry_attribute17 IS NOT NULL
3483 OR p_x_line_rec.industry_attribute18 IS NOT NULL
3484 OR p_x_line_rec.industry_attribute19 IS NOT NULL
3485 OR p_x_line_rec.industry_attribute2 IS NOT NULL
3486 OR p_x_line_rec.industry_attribute20 IS NOT NULL
3487 OR p_x_line_rec.industry_attribute21 IS NOT NULL
3488 OR p_x_line_rec.industry_attribute22 IS NOT NULL
3489 OR p_x_line_rec.industry_attribute23 IS NOT NULL
3490 OR p_x_line_rec.industry_attribute24 IS NOT NULL
3491 OR p_x_line_rec.industry_attribute25 IS NOT NULL
3492 OR p_x_line_rec.industry_attribute26 IS NOT NULL
3493 OR p_x_line_rec.industry_attribute27 IS NOT NULL
3494 OR p_x_line_rec.industry_attribute28 IS NOT NULL
3495 OR p_x_line_rec.industry_attribute29 IS NOT NULL
3496 OR p_x_line_rec.industry_attribute3 IS NOT NULL
3497 OR p_x_line_rec.industry_attribute30 IS NOT NULL
3498 OR p_x_line_rec.industry_attribute4 IS NOT NULL
3499 OR p_x_line_rec.industry_attribute5 IS NOT NULL
3500 OR p_x_line_rec.industry_attribute6 IS NOT NULL
3501 OR p_x_line_rec.industry_attribute7 IS NOT NULL
3502 OR p_x_line_rec.industry_attribute8 IS NOT NULL
3503 OR p_x_line_rec.industry_attribute9 IS NOT NULL
3504 OR p_x_line_rec.industry_context IS NOT NULL
3505 THEN
3506
3507
3508 IF NOT OE_CNCL_Validate.I_Line_Desc_Flex
3509 (p_context => p_x_line_rec.Industry_context
3510 ,p_attribute1 => p_x_line_rec.Industry_attribute1
3511 ,p_attribute2 => p_x_line_rec.Industry_attribute2
3512 ,p_attribute3 => p_x_line_rec.Industry_attribute3
3513 ,p_attribute4 => p_x_line_rec.Industry_attribute4
3514 ,p_attribute5 => p_x_line_rec.Industry_attribute5
3515 ,p_attribute6 => p_x_line_rec.Industry_attribute6
3516 ,p_attribute7 => p_x_line_rec.Industry_attribute7
3517 ,p_attribute8 => p_x_line_rec.Industry_attribute8
3518 ,p_attribute9 => p_x_line_rec.Industry_attribute9
3519 ,p_attribute10 => p_x_line_rec.Industry_attribute10
3520 ,p_attribute11 => p_x_line_rec.Industry_attribute11
3521 ,p_attribute12 => p_x_line_rec.Industry_attribute12
3522 ,p_attribute13 => p_x_line_rec.Industry_attribute13
3523 ,p_attribute14 => p_x_line_rec.Industry_attribute14
3524 ,p_attribute15 => p_x_line_rec.Industry_attribute15
3525 ,p_attribute16 => p_x_line_rec.Industry_attribute16
3526 ,p_attribute17 => p_x_line_rec.Industry_attribute17
3527 ,p_attribute18 => p_x_line_rec.Industry_attribute18
3528 ,p_attribute19 => p_x_line_rec.Industry_attribute19
3529 ,p_attribute20 => p_x_line_rec.Industry_attribute20
3530 ,p_attribute21 => p_x_line_rec.Industry_attribute21
3531 ,p_attribute22 => p_x_line_rec.Industry_attribute22
3532 ,p_attribute23 => p_x_line_rec.Industry_attribute23
3533 ,p_attribute24 => p_x_line_rec.Industry_attribute24
3534 ,p_attribute25 => p_x_line_rec.Industry_attribute25
3535 ,p_attribute26 => p_x_line_rec.Industry_attribute26
3536 ,p_attribute27 => p_x_line_rec.Industry_attribute27
3537 ,p_attribute28 => p_x_line_rec.Industry_attribute28
3538 ,p_attribute29 => p_x_line_rec.Industry_attribute29
3539 ,p_attribute30 => p_x_line_rec.Industry_attribute30) THEN
3540
3541 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3542
3543
3544 p_x_line_rec.Industry_context := null;
3545 p_x_line_rec.Industry_attribute1 := null;
3546 p_x_line_rec.Industry_attribute2 := null;
3547 p_x_line_rec.Industry_attribute3 := null;
3548 p_x_line_rec.Industry_attribute4 := null;
3549 p_x_line_rec.Industry_attribute5 := null;
3550 p_x_line_rec.Industry_attribute6 := null;
3551 p_x_line_rec.Industry_attribute7 := null;
3552 p_x_line_rec.Industry_attribute8 := null;
3553 p_x_line_rec.Industry_attribute9 := null;
3554 p_x_line_rec.Industry_attribute10 := null;
3555 p_x_line_rec.Industry_attribute11 := null;
3556 p_x_line_rec.Industry_attribute12 := null;
3557 p_x_line_rec.Industry_attribute13 := null;
3558 p_x_line_rec.Industry_attribute14 := null;
3559 p_x_line_rec.Industry_attribute15 := null;
3560 p_x_line_rec.Industry_attribute16 := null;
3561 p_x_line_rec.Industry_attribute17 := null;
3562 p_x_line_rec.Industry_attribute18 := null;
3563 p_x_line_rec.Industry_attribute19 := null;
3564 p_x_line_rec.Industry_attribute20 := null;
3565 p_x_line_rec.Industry_attribute21 := null;
3566 p_x_line_rec.Industry_attribute22 := null;
3567 p_x_line_rec.Industry_attribute23 := null;
3568 p_x_line_rec.Industry_attribute24 := null;
3569 p_x_line_rec.Industry_attribute25 := null;
3570 p_x_line_rec.Industry_attribute26 := null;
3571 p_x_line_rec.Industry_attribute27 := null;
3572 p_x_line_rec.Industry_attribute28 := null;
3573 p_x_line_rec.Industry_attribute29 := null;
3574 p_x_line_rec.Industry_attribute30 := null;
3575
3576 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3577
3578 p_x_line_rec.Industry_context := FND_API.G_MISS_CHAR;
3579 p_x_line_rec.Industry_attribute1 := FND_API.G_MISS_CHAR;
3580 p_x_line_rec.Industry_attribute2 := FND_API.G_MISS_CHAR;
3581 p_x_line_rec.Industry_attribute3 := FND_API.G_MISS_CHAR;
3582 p_x_line_rec.Industry_attribute4 := FND_API.G_MISS_CHAR;
3583 p_x_line_rec.Industry_attribute5 := FND_API.G_MISS_CHAR;
3584 p_x_line_rec.Industry_attribute6 := FND_API.G_MISS_CHAR;
3585 p_x_line_rec.Industry_attribute7 := FND_API.G_MISS_CHAR;
3586 p_x_line_rec.Industry_attribute8 := FND_API.G_MISS_CHAR;
3587 p_x_line_rec.Industry_attribute9 := FND_API.G_MISS_CHAR;
3588 p_x_line_rec.Industry_attribute10 := FND_API.G_MISS_CHAR;
3589 p_x_line_rec.Industry_attribute11 := FND_API.G_MISS_CHAR;
3590 p_x_line_rec.Industry_attribute12 := FND_API.G_MISS_CHAR;
3591 p_x_line_rec.Industry_attribute13 := FND_API.G_MISS_CHAR;
3592 p_x_line_rec.Industry_attribute14 := FND_API.G_MISS_CHAR;
3593 p_x_line_rec.Industry_attribute15 := FND_API.G_MISS_CHAR;
3594 p_x_line_rec.Industry_attribute16 := FND_API.G_MISS_CHAR;
3595 p_x_line_rec.Industry_attribute17 := FND_API.G_MISS_CHAR;
3596 p_x_line_rec.Industry_attribute18 := FND_API.G_MISS_CHAR;
3597 p_x_line_rec.Industry_attribute19 := FND_API.G_MISS_CHAR;
3598 p_x_line_rec.Industry_attribute20 := FND_API.G_MISS_CHAR;
3599 p_x_line_rec.Industry_attribute21 := FND_API.G_MISS_CHAR;
3600 p_x_line_rec.Industry_attribute22 := FND_API.G_MISS_CHAR;
3601 p_x_line_rec.Industry_attribute23 := FND_API.G_MISS_CHAR;
3602 p_x_line_rec.Industry_attribute24 := FND_API.G_MISS_CHAR;
3603 p_x_line_rec.Industry_attribute25 := FND_API.G_MISS_CHAR;
3604 p_x_line_rec.Industry_attribute26 := FND_API.G_MISS_CHAR;
3605 p_x_line_rec.Industry_attribute27 := FND_API.G_MISS_CHAR;
3606 p_x_line_rec.Industry_attribute28 := FND_API.G_MISS_CHAR;
3607 p_x_line_rec.Industry_attribute29 := FND_API.G_MISS_CHAR;
3608 p_x_line_rec.Industry_attribute30 := FND_API.G_MISS_CHAR;
3609 ELSE
3610
3611 x_return_status := FND_API.G_RET_STS_ERROR;
3612 END IF;
3613 END IF;
3614
3615 oe_debug_pub.add('After I_line_desc_flex ' || x_return_status,2);
3616
3617 END IF;
3618
3619 /* Trading Partner Attributes */
3620 IF p_x_line_rec.tp_attribute1 IS NOT NULL
3621 OR p_x_line_rec.tp_attribute2 IS NOT NULL
3622 OR p_x_line_rec.tp_attribute3 IS NOT NULL
3623 OR p_x_line_rec.tp_attribute4 IS NOT NULL
3624 OR p_x_line_rec.tp_attribute5 IS NOT NULL
3625 OR p_x_line_rec.tp_attribute6 IS NOT NULL
3626 OR p_x_line_rec.tp_attribute7 IS NOT NULL
3627 OR p_x_line_rec.tp_attribute8 IS NOT NULL
3628 OR p_x_line_rec.tp_attribute9 IS NOT NULL
3629 OR p_x_line_rec.tp_attribute10 IS NOT NULL
3630 OR p_x_line_rec.tp_attribute11 IS NOT NULL
3631 OR p_x_line_rec.tp_attribute12 IS NOT NULL
3632 OR p_x_line_rec.tp_attribute13 IS NOT NULL
3633 OR p_x_line_rec.tp_attribute14 IS NOT NULL
3634 OR p_x_line_rec.tp_attribute15 IS NOT NULL
3635
3636 THEN
3637
3638
3639 IF NOT OE_CNCL_Validate.TP_Line_Desc_Flex
3640 (p_context => p_x_line_rec.tp_context
3641 ,p_attribute1 => p_x_line_rec.tp_attribute1
3642 ,p_attribute2 => p_x_line_rec.tp_attribute2
3643 ,p_attribute3 => p_x_line_rec.tp_attribute3
3644 ,p_attribute4 => p_x_line_rec.tp_attribute4
3645 ,p_attribute5 => p_x_line_rec.tp_attribute5
3646 ,p_attribute6 => p_x_line_rec.tp_attribute6
3647 ,p_attribute7 => p_x_line_rec.tp_attribute7
3648 ,p_attribute8 => p_x_line_rec.tp_attribute8
3649 ,p_attribute9 => p_x_line_rec.tp_attribute9
3650 ,p_attribute10 => p_x_line_rec.tp_attribute10
3651 ,p_attribute11 => p_x_line_rec.tp_attribute11
3652 ,p_attribute12 => p_x_line_rec.tp_attribute12
3653 ,p_attribute13 => p_x_line_rec.tp_attribute13
3654 ,p_attribute14 => p_x_line_rec.tp_attribute14
3655 ,p_attribute15 => p_x_line_rec.tp_attribute15) THEN
3656
3657 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3658
3659
3660 p_x_line_rec.tp_context := null;
3661 p_x_line_rec.tp_attribute1 := null;
3662 p_x_line_rec.tp_attribute2 := null;
3663 p_x_line_rec.tp_attribute3 := null;
3664 p_x_line_rec.tp_attribute4 := null;
3665 p_x_line_rec.tp_attribute5 := null;
3666 p_x_line_rec.tp_attribute6 := null;
3667 p_x_line_rec.tp_attribute7 := null;
3668 p_x_line_rec.tp_attribute8 := null;
3669 p_x_line_rec.tp_attribute9 := null;
3670 p_x_line_rec.tp_attribute10 := null;
3671 p_x_line_rec.tp_attribute11 := null;
3672 p_x_line_rec.tp_attribute12 := null;
3673 p_x_line_rec.tp_attribute13 := null;
3674 p_x_line_rec.tp_attribute14 := null;
3675 p_x_line_rec.tp_attribute15 := null;
3676
3677 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3678
3679 p_x_line_rec.tp_context := FND_API.G_MISS_CHAR;
3680 p_x_line_rec.tp_attribute1 := FND_API.G_MISS_CHAR;
3681 p_x_line_rec.tp_attribute2 := FND_API.G_MISS_CHAR;
3682 p_x_line_rec.tp_attribute3 := FND_API.G_MISS_CHAR;
3683 p_x_line_rec.tp_attribute4 := FND_API.G_MISS_CHAR;
3684 p_x_line_rec.tp_attribute5 := FND_API.G_MISS_CHAR;
3685 p_x_line_rec.tp_attribute6 := FND_API.G_MISS_CHAR;
3686 p_x_line_rec.tp_attribute7 := FND_API.G_MISS_CHAR;
3687 p_x_line_rec.tp_attribute8 := FND_API.G_MISS_CHAR;
3688 p_x_line_rec.tp_attribute9 := FND_API.G_MISS_CHAR;
3689 p_x_line_rec.tp_attribute10 := FND_API.G_MISS_CHAR;
3690 p_x_line_rec.tp_attribute11 := FND_API.G_MISS_CHAR;
3691 p_x_line_rec.tp_attribute12 := FND_API.G_MISS_CHAR;
3692 p_x_line_rec.tp_attribute13 := FND_API.G_MISS_CHAR;
3693 p_x_line_rec.tp_attribute14 := FND_API.G_MISS_CHAR;
3694 p_x_line_rec.tp_attribute15 := FND_API.G_MISS_CHAR;
3695 ELSE
3696
3697 x_return_status := FND_API.G_RET_STS_ERROR;
3698 END IF;
3699 END IF;
3700
3701 --oe_debug_pub.add('After TP_line_desc_flex ' || x_return_status);
3702
3703 END IF;
3704 /* Trading Partner */
3705
3706
3707 IF p_x_line_rec.return_attribute1 IS NOT NULL
3708 OR p_x_line_rec.return_attribute10 IS NOT NULL
3709 OR p_x_line_rec.return_attribute11 IS NOT NULL
3710 OR p_x_line_rec.return_attribute12 IS NOT NULL
3711 OR p_x_line_rec.return_attribute13 IS NOT NULL
3712 OR p_x_line_rec.return_attribute14 IS NOT NULL
3713 OR p_x_line_rec.return_attribute15 IS NOT NULL
3714 OR p_x_line_rec.return_attribute2 IS NOT NULL
3715 OR p_x_line_rec.return_attribute3 IS NOT NULL
3716 OR p_x_line_rec.return_attribute4 IS NOT NULL
3717 OR p_x_line_rec.return_attribute5 IS NOT NULL
3718 OR p_x_line_rec.return_attribute6 IS NOT NULL
3719 OR p_x_line_rec.return_attribute7 IS NOT NULL
3720 OR p_x_line_rec.return_attribute8 IS NOT NULL
3721 OR p_x_line_rec.return_attribute9 IS NOT NULL
3722 OR p_x_line_rec.return_context IS NOT NULL
3723 THEN
3724
3725
3726 oe_debug_pub.add('Before calling Return line_desc_flex',2);
3727 IF NOT OE_CNCL_Validate.R_Line_Desc_Flex
3728 (p_context => p_x_line_rec.Return_context
3729 ,p_attribute1 => p_x_line_rec.Return_attribute1
3730 ,p_attribute2 => p_x_line_rec.Return_attribute2
3731 ,p_attribute3 => p_x_line_rec.Return_attribute3
3732 ,p_attribute4 => p_x_line_rec.Return_attribute4
3733 ,p_attribute5 => p_x_line_rec.Return_attribute5
3734 ,p_attribute6 => p_x_line_rec.Return_attribute6
3735 ,p_attribute7 => p_x_line_rec.Return_attribute7
3736 ,p_attribute8 => p_x_line_rec.Return_attribute8
3737 ,p_attribute9 => p_x_line_rec.Return_attribute9
3738 ,p_attribute10 => p_x_line_rec.Return_attribute10
3739 ,p_attribute11 => p_x_line_rec.Return_attribute11
3740 ,p_attribute12 => p_x_line_rec.Return_attribute12
3741 ,p_attribute13 => p_x_line_rec.Return_attribute13
3742 ,p_attribute14 => p_x_line_rec.Return_attribute14
3743 ,p_attribute15 => p_x_line_rec.Return_attribute15) THEN
3744
3745 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3746
3747
3748 p_x_line_rec.Return_context := null;
3749 p_x_line_rec.Return_attribute1 := null;
3750 p_x_line_rec.Return_attribute2 := null;
3751 p_x_line_rec.Return_attribute3 := null;
3752 p_x_line_rec.Return_attribute4 := null;
3753 p_x_line_rec.Return_attribute5 := null;
3754 p_x_line_rec.Return_attribute6 := null;
3755 p_x_line_rec.Return_attribute7 := null;
3756 p_x_line_rec.Return_attribute8 := null;
3757 p_x_line_rec.Return_attribute9 := null;
3758 p_x_line_rec.Return_attribute11 := null;
3759 p_x_line_rec.Return_attribute12 := null;
3760 p_x_line_rec.Return_attribute13 := null;
3761 p_x_line_rec.Return_attribute14 := null;
3762 p_x_line_rec.Return_attribute15 := null;
3763 p_x_line_rec.Return_attribute10 := null;
3764
3765 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3766 p_x_line_rec.Return_context := FND_API.G_MISS_CHAR;
3767 p_x_line_rec.Return_attribute1 := FND_API.G_MISS_CHAR;
3768 p_x_line_rec.Return_attribute2 := FND_API.G_MISS_CHAR;
3769 p_x_line_rec.Return_attribute3 := FND_API.G_MISS_CHAR;
3770 p_x_line_rec.Return_attribute4 := FND_API.G_MISS_CHAR;
3771 p_x_line_rec.Return_attribute5 := FND_API.G_MISS_CHAR;
3772 p_x_line_rec.Return_attribute6 := FND_API.G_MISS_CHAR;
3773 p_x_line_rec.Return_attribute7 := FND_API.G_MISS_CHAR;
3774 p_x_line_rec.Return_attribute8 := FND_API.G_MISS_CHAR;
3775 p_x_line_rec.Return_attribute9 := FND_API.G_MISS_CHAR;
3776 p_x_line_rec.Return_attribute11 := FND_API.G_MISS_CHAR;
3777 p_x_line_rec.Return_attribute12 := FND_API.G_MISS_CHAR;
3778 p_x_line_rec.Return_attribute13 := FND_API.G_MISS_CHAR;
3779 p_x_line_rec.Return_attribute14 := FND_API.G_MISS_CHAR;
3780 p_x_line_rec.Return_attribute15 := FND_API.G_MISS_CHAR;
3781 p_x_line_rec.Return_attribute10 := FND_API.G_MISS_CHAR;
3782 ELSE
3783
3784 x_return_status := FND_API.G_RET_STS_ERROR;
3785 END IF;
3786 END IF;
3787
3788 oe_debug_pub.add('After Return line_desc_flex ' || x_return_status,2);
3789
3790
3791
3792 END IF;
3793
3794 -- Done validating attributes
3795
3796 IF p_x_line_rec.salesrep_id IS NOT NULL
3797 THEN
3798 IF NOT OE_CNCL_Validate.salesrep(p_x_line_rec.salesrep_id) THEN
3799 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3800 p_x_line_rec.salesrep_id := NULL;
3801 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3802 p_x_line_rec.salesrep_id := FND_API.G_MISS_NUM;
3803 ELSE
3804 x_return_status := FND_API.G_RET_STS_ERROR;
3805 END IF;
3806 END IF;
3807 END IF;
3808
3809 IF p_x_line_rec.return_reason_code IS NOT NULL
3810 THEN
3811 IF NOT OE_CNCL_Validate.return_reason(p_x_line_rec.return_reason_code) THEN
3812 IF p_validation_level = OE_GLOBALS.G_VALID_LEVEL_PARTIAL THEN
3813 p_x_line_rec.return_reason_code := NULL;
3814 ELSIF p_validation_level = OE_GLOBALS.G_VALID_PARTIAL_WITH_DEF THEN
3815 p_x_line_rec.return_reason_code := FND_API.G_MISS_CHAR;
3816 ELSE
3817 x_return_status := FND_API.G_RET_STS_ERROR;
3818 END IF;
3819 END IF;
3820 END IF;
3821
3822 -- Validate Commitment
3823 IF (p_x_line_rec.commitment_id IS NOT NULL)
3824 THEN
3825 IF NOT OE_CNCL_Validate.commitment(p_x_line_rec.commitment_id) THEN
3826 x_return_status := FND_API.G_RET_STS_ERROR;
3827 END IF;
3828 END IF;
3829 oe_debug_pub.add('Exiting procedure OE_CNCL_VALIDATE_line.Attributes',1);
3830
3831 EXCEPTION
3832
3833 WHEN FND_API.G_EXC_ERROR THEN
3834
3835 x_return_status := FND_API.G_RET_STS_ERROR;
3836
3837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3838
3839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3840
3841 WHEN OTHERS THEN
3842
3843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3844
3845 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3846 THEN
3847 OE_MSG_PUB.Add_Exc_Msg
3848 ( G_PKG_NAME
3849 , 'Attributes'
3850 );
3851 END IF;
3852
3853 END Attributes;
3854
3855 FUNCTION Get_Item_Type(p_line_rec OE_ORDER_PUB.Line_Rec_Type)
3856
3857 RETURN VARCHAR2
3858 IS
3859 l_item_type_code VARCHAR2(30) := NULL;
3860 l_item_rec OE_ORDER_CACHE.item_rec_type;
3861 BEGIN
3862
3863 oe_debug_pub.add('In OEXVCLINB: Function Get_Item_Type',1);
3864 oe_debug_pub.add('The INV Item is'||to_char(p_line_rec.inventory_item_id),1);
3865
3866 IF p_line_rec.line_category_code = OE_GLOBALS.G_RETURN_CATEGORY_CODE THEN
3867 RETURN OE_GLOBALS.G_ITEM_STANDARD;
3868 ELSIF p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CONFIG OR
3869 p_line_rec.item_type_code = OE_GLOBALS.G_ITEM_INCLUDED THEN
3870 RETURN p_line_rec.item_type_code;
3871 END IF;
3872
3873
3874 l_item_rec :=
3875 OE_Order_Cache.Load_Item (p_line_rec.inventory_item_id
3876 ,p_line_rec.ship_from_org_id);
3877
3878 oe_debug_pub.add('Bom Item Type is ' || l_item_rec.bom_item_type);
3879
3880 IF l_item_rec.bom_item_type = 1
3881 -- MODEL items and ato's under pto have bom_item_type = 1
3882 THEN
3883
3884 IF nvl(p_line_rec.top_model_line_ref, 0) <>
3885 nvl(p_line_rec.orig_sys_line_ref, 0)
3886 THEN
3887 oe_debug_pub.add
3888 ('Returning CLASS as the Item Type for ato subconfig',1);
3889 --Procedure to check change in item_type_code
3890 RETURN OE_GLOBALS.G_ITEM_CLASS;
3891 END IF;
3892
3893 oe_debug_pub.add('Returning MODEL as the Item Type',1);
3894 --Procedure to check change in item_type_code
3895 RETURN OE_GLOBALS.G_ITEM_MODEL;
3896
3897 ELSIF l_item_rec.bom_item_type = 2
3898 THEN
3899 oe_debug_pub.add('Returning CLASS as the Item Type',1);
3900 -- Only CLASS items have bom_item_type = 2
3901 --Procedure to check change in item_type_code
3902 RETURN OE_GLOBALS.G_ITEM_CLASS;
3903 ELSIF l_item_rec.bom_item_type = 4 and
3904 l_item_rec.service_item_flag = 'N'
3905 THEN
3906
3907 oe_debug_pub.add('Bom 4 and flag = N');
3908 -- Following 3 items can have bom_item_type = 4 :
3909 -- STANDARD item, OPTION item and a KIT
3910 -- We will distinguish an item to be a kit by seeing if
3911 -- it has a record in bom_bill_of_materials.
3912 -- All options MUST have the top_model_line_ref populated
3913 -- before they come to defaulting. Thus we use it to distinguish
3914 -- between a standard and an option item.
3915 -- ato_item's item_type_code will be standard
3916
3917 oe_debug_pub.add
3918 ('item Org ' || OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID'));
3919 oe_debug_pub.add('inventory_item_id ' || p_line_rec.inventory_item_id);
3920 BEGIN
3921 SELECT OE_GLOBALS.G_ITEM_KIT
3922 INTO l_item_type_code
3923 FROM mtl_system_items
3924 WHERE organization_id
3925 = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
3926 AND inventory_item_id = p_line_rec.inventory_item_id
3927 AND pick_components_flag = 'Y';
3928
3929 oe_debug_pub.add(' Before calling check 1');
3930 --Procedure to check change in item_type_code
3931 RETURN l_item_type_code;
3932 EXCEPTION
3933 WHEN NO_DATA_FOUND THEN
3934 oe_debug_pub.add('get_item_type no data found, bom_item_type : 4', 1);
3935 IF (p_line_rec.top_model_line_ref is not null AND
3936 p_line_rec.top_model_line_ref <> FND_API.G_MISS_CHAR)
3937 OR
3938 (p_line_rec.top_model_line_index is not null AND
3939 p_line_rec.top_model_line_index <> FND_API.G_MISS_NUM)
3940
3941 THEN
3942 oe_debug_pub.add(' Before calling check 2');
3943 RETURN OE_GLOBALS.G_ITEM_OPTION;
3944 ELSE
3945 oe_debug_pub.add(' Before calling check 3');
3946 RETURN OE_GLOBALS.G_ITEM_STANDARD;
3947 END IF;
3948 END;
3949
3950 ELSIF l_item_rec.service_item_flag = 'Y' and
3951 l_item_rec.bom_item_type = 4
3952 THEN
3953 oe_debug_pub.add('Service item flag is: ' || l_item_rec.service_item_flag);
3954 RETURN OE_GLOBALS.G_ITEM_SERVICE;
3955
3956 END IF;
3957
3958 RETURN null;
3959
3960 oe_debug_pub.add('Exiting OEXVCLNB: Function Get_Item_Type');
3961
3962 EXCEPTION
3963
3964 WHEN NO_DATA_FOUND THEN
3965 oe_debug_pub.add(' Before calling check 4');
3966 l_item_type_code := OE_GLOBALS.G_ITEM_STANDARD;
3967 RETURN l_item_type_code;
3968
3969 WHEN OTHERS THEN
3970
3971 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3972 THEN
3973 OE_MSG_PUB.Add_Exc_Msg
3974 ( G_PKG_NAME ,
3975 'Get_Item_Type'
3976 );
3977 END IF;
3978
3979 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3980
3981
3982 END Get_Item_Type;
3983
3984 END OE_CNCL_Validate_Line;
[Home] [Help]