1 PACKAGE BODY OE_BULK_PROCESS_LINE As
2 /* $Header: OEBLLINB.pls 120.14.12010000.8 2009/01/21 13:21:10 smanian ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OE_BULK_PROCESS_LINE';
5
6
7 g_curr_top_index NUMBER;
8 g_curr_ato_index NUMBER;
9 -----------------------------------------------------------------------
10 -- LOCAL PROCEDURES/FUNCTIONS
11 -----------------------------------------------------------------------
12
13 FUNCTION Validate_Line_Type
14 ( p_line_type_id IN NUMBER
15 , p_ordered_date IN DATE
16 )
17 RETURN BOOLEAN
18 IS
19 l_c_index NUMBER;
20 BEGIN
21 oe_debug_pub.add( ' Enter Validate_Line_Type',1);
22 oe_debug_pub.add( ' Order Date :' || to_char(p_ordered_date));
23 oe_debug_pub.add( 'start Date :'|| OE_BULK_CACHE.G_LINE_TYPE_TBL(p_line_type_id).start_date_active);
24 oe_debug_pub.add( 'end Date :'|| OE_BULK_CACHE.G_LINE_TYPE_TBL(p_line_type_id).end_date_active);
25 l_c_index := OE_BULK_CACHE.Load_Line_Type
26 (p_key => p_line_type_id);
27
28 IF p_ordered_date BETWEEN
29 nvl(OE_BULK_CACHE.G_LINE_TYPE_TBL(p_line_type_id).start_date_active,sysdate)
30 AND nvl(OE_BULK_CACHE.G_LINE_TYPE_TBL(p_line_type_id).end_date_active,sysdate)
31 THEN
32 oe_debug_pub.add( 'Line Type Valid');
33 RETURN TRUE;
34 ELSE
35 oe_debug_pub.add( 'Line Type NOT Valid');
36 RETURN FALSE;
37 END IF;
38
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 RETURN FALSE;
42 END Validate_Line_Type;
43
44
45 PROCEDURE Check_Book_Reqd_Attributes
46 ( p_line_rec IN OE_WSH_BULK_GRP.LINE_REC_TYPE
47 , p_index IN NUMBER
48 , x_return_status IN OUT NOCOPY VARCHAR2
49 )
50 IS
51 l_c_index NUMBER;
52 l_rule_type VARCHAR2(10);
53 --
54 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
55 --
56 BEGIN
57
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59
60 -- Check for fields required on a booked order line
61
62 IF p_line_rec.sold_to_org_id(p_index) IS NULL THEN
63 x_return_status := FND_API.G_RET_STS_ERROR;
64 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
65 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
66 OE_Order_UTIL.Get_Attribute_Name('SOLD_TO_ORG_ID'));
67 OE_BULK_MSG_PUB.ADD;
68 END IF;
69
70 IF p_line_rec.invoice_to_org_id(p_index) IS NULL THEN
71 x_return_status := FND_API.G_RET_STS_ERROR;
72 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
73 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
74 OE_Order_UTIL.Get_Attribute_Name('INVOICE_TO_ORG_ID'));
75 OE_BULK_MSG_PUB.ADD;
76 END IF;
77
78 IF p_line_rec.tax_exempt_flag(p_index) IS NULL THEN
79 x_return_status := FND_API.G_RET_STS_ERROR;
80 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
81 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
82 OE_Order_UTIL.Get_Attribute_Name('TAX_EXEMPT_FLAG'));
83 OE_BULK_MSG_PUB.ADD;
84 END IF;
85
86 -- Quantity and UOM Required
87
88 IF p_line_rec.order_quantity_uom(p_index) IS NULL THEN
89 x_return_status := FND_API.G_RET_STS_ERROR;
90 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
91 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
92 OE_Order_UTIL.Get_Attribute_Name('ORDER_QUANTITY_UOM'));
93 OE_BULK_MSG_PUB.ADD;
94 END IF;
95
96 -- Fix bug 1277092: ordered quantity should not be = 0 on a booked line
97 IF p_line_rec.ordered_quantity(p_index) IS NULL
98 OR p_line_rec.ordered_quantity(p_index) = 0 THEN
99 x_return_status := FND_API.G_RET_STS_ERROR;
100 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
101 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
102 OE_Order_UTIL.Get_Attribute_Name('ORDERED_QUANTITY'));
103 OE_BULK_MSG_PUB.ADD;
104 END IF;
105
106 -- Fix bug 1262790
107 -- Ship To and Payment Term required on ORDER lines,
108 -- NOT on RETURN lines
109
110 IF p_line_rec.line_category_code(p_index)
111 <> OE_GLOBALS.G_RETURN_CATEGORY_CODE THEN
112
113 IF p_line_rec.ship_to_org_id(p_index) IS NULL THEN
114 x_return_status := FND_API.G_RET_STS_ERROR;
115 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
116 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
117 OE_Order_UTIL.Get_Attribute_Name('SHIP_TO_ORG_ID'));
118 OE_BULK_MSG_PUB.ADD;
119 END IF;
120
121 IF p_line_rec.payment_term_id(p_index) IS NULL THEN
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
124 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
125 OE_Order_UTIL.Get_Attribute_Name('PAYMENT_TERM_ID'));
126 OE_BULK_MSG_PUB.ADD;
127 END IF;
128 --serla
129 IF OE_CODE_CONTROL.Get_Code_Release_Level >= '110509' THEN
130 IF p_line_rec.item_type_code(p_index) <> 'SERVICE' THEN
131 IF p_line_rec.accounting_rule_id(p_index) IS NOT NULL AND
132 p_line_rec.accounting_rule_id(p_index) <> FND_API.G_MISS_NUM THEN
133 IF l_debug_level > 0 THEN
134 oe_debug_pub.add( 'GETTING ACCOUNTING RULE TYPE' ) ;
135 END IF;
136 SELECT type
137 INTO l_rule_type
138 FROM ra_rules
139 WHERE rule_id = p_line_rec.accounting_rule_id(p_index);
140 IF l_debug_level > 0 THEN
141 oe_debug_pub.add( 'RULE_TYPE IS :'||L_RULE_TYPE||': ACCOUNTING RULE DURATION IS: '||P_LINE_REC.ACCOUNTING_RULE_DURATION ( P_INDEX ) ) ;
142 END IF;
143 IF l_rule_type = 'ACC_DUR' THEN
144 IF p_line_rec.accounting_rule_duration(p_index) IS NULL THEN
145 x_return_status := FND_API.G_RET_STS_ERROR;
146 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
147 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
148 OE_Order_UTIL.Get_Attribute_Name('ACCOUNTING_RULE_DURATION'));
149 OE_BULK_MSG_PUB.ADD;
150 END IF; -- end of accounting_rule_duration null
151 END IF; -- end of variable accounting rule type
152 END IF; -- end of accounting_rule_id not null
153 END IF; -- end of non-service line
154 END IF; -- end of code release level
155 --serla
156 END IF;
157
158 -- ?? Check with Manish ??
159 -- Are checks for tax fields required?
160
161 -- Commenting out as of 1/23/2003 as tax checks not req'd
162 /*
163 IF p_line_rec.tax_date(p_index) IS NULL THEN
164 x_return_status := FND_API.G_RET_STS_ERROR;
165 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
166 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
167 OE_Order_UTIL.Get_Attribute_Name('TAX_DATE'));
168 OE_BULK_MSG_PUB.ADD;
169 END IF;
170 */
171 -- Tax field checks not required, as checks would prevent orders from
172 -- being entered where transaction is taxable and tax calculation
173 -- event is before invoicing.
174
175 -- Pricing attribute checks will be done after the call to Price_Orders
176 -- Bug 2765770 =>
177 -- For calculate price flag of 'N', price list check should be done here
178 -- as such lines are not updated in Price_Orders call.
179
180 IF p_line_rec.calculate_price_flag(p_index) = 'N' THEN
181
182 IF p_line_rec.price_list_id(p_index) IS NULL THEN
183 x_return_status := FND_API.G_RET_STS_ERROR;
184 FND_MESSAGE.SET_NAME('ONT','OE_BOOK_REQD_LINE_ATTRIBUTE');
185 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
186 OE_Order_UTIL.Get_Attribute_Name('PRICE_LIST_ID'));
187 OE_BULK_MSG_PUB.ADD;
188 END IF;
189
190 END IF;
191
192 -- Checks for service fields NOT required
193 -- as BULK does not support creation of service lines.
194
195 -- NOT REQUIRED as BULK does not support RETURN line creation:
196 -- 1. Warehouse and schedule date required on RETURN lines
197 -- 2. Check over return
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 oe_debug_pub.add('Others Error, Line.Check_Book_Reqd_Attributes');
202 oe_debug_pub.add(substr(sqlerrm,1,240));
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
204 IF OE_BULK_MSG_PUB.Check_Msg_Level(OE_BULK_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
205 THEN
206 OE_BULK_MSG_PUB.Add_Exc_Msg
207 ( G_PKG_NAME
208 , 'Check_Book_Reqd_Attributes'
209 );
210 END IF;
211 END Check_Book_Reqd_Attributes;
212
213 PROCEDURE Check_Scheduling_Attributes
214 ( p_line_rec IN OE_WSH_BULK_GRP.LINE_REC_TYPE
215 , p_index IN NUMBER
216 , x_return_status IN OUT NOCOPY VARCHAR2
217 )
218 IS
219 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
220
221 l_c_index NUMBER;
222 l_org_id NUMBER;
223 l_bill_seq_id NUMBER;
224 l_make_buy NUMBER;
225 l_org_code VARCHAR2(30);
226
227 BEGIN
228
229 x_return_status := FND_API.G_RET_STS_SUCCESS;
230
231 -- Check for Required Scheduling Attributes
232
233 IF p_line_rec.ordered_quantity(p_index) IS NULL THEN
234 FND_MESSAGE.SET_NAME('ONT','OE_SCH_MISSING_QUANTITY');
235 OE_BULK_MSG_PUB.Add;
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 END IF;
238
239 -- If the quantity on the line is zero(which is different from
240 -- missing) and if the user is trying to performing scheduling,
241 -- it is an error
242
243 IF p_line_rec.ordered_quantity(p_index) = 0 THEN
244 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ZERO_QTY');
245 OE_BULK_MSG_PUB.Add;
246 x_return_status := FND_API.G_RET_STS_ERROR;
247 END IF;
248
249 -- No need to check for following statuses: cancelled, shipped, reserved
250 -- qty changes as line is being CREATED.
251
252 -- If the order quantity uom on the line is missing or null
253 -- and if the user is trying to performing scheduling,
254 -- it is an error
255
256 IF p_line_rec.order_quantity_uom(p_index) IS NULL THEN
257 FND_MESSAGE.SET_NAME('ONT','OE_SCH_MISSING_UOM');
258 OE_BULK_MSG_PUB.Add;
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 END IF;
261
262 -- Item check not required as it is already validated for creation.
263
264 -- If the request_date on the line is missing or null and
265 -- if the user is trying to performing scheduling,
266 -- it is an error
267
268 IF p_line_rec.request_date(p_index) IS NULL THEN
269 FND_MESSAGE.SET_NAME('ONT','OE_SCH_MISSING_REQUEST_DATE');
270 OE_BULK_MSG_PUB.Add;
271 x_return_status := FND_API.G_RET_STS_ERROR;
272 END IF;
273
274 -- No need to check if line belongs to a set: SETs not supported in BULK mode
275
276 -- Following steps will be post BULK insertion of lines - in OE_BULK_SCHEDULE_UTIL
277 -- 1.Check for Holds
278 -- 2.Check for scheduling levels - whether line should be reserved or not. Or
279 -- should this be done here? If done here, how to mark sch level for each line?
280 -- Solution to 2: Do Not worry - Reservations NOT supported in BULK mode.
281
282 -- No need to do ATO validations: ATOs not supported in BULK mode
283 -- ADDING code
284 l_c_index := OE_BULK_CACHE.Load_Line_Type(p_line_rec.line_type_id(p_index));
285 IF (OE_BULK_CACHE.G_LINE_TYPE_TBL(l_c_index).scheduling_level_code = 'FOUR'
286 OR OE_BULK_CACHE.G_LINE_TYPE_TBL(l_c_index).scheduling_level_code = 'FIVE') AND
287 (p_line_rec.item_type_code(p_index) <> 'STANDARD'
288 OR p_line_rec.ato_line_id(p_index) is not null) THEN
289
290 IF l_debug_level > 0 THEN
291 oe_debug_pub.add( 'CHECKING THAT IT IS A STANDARD ITEM...' , 1 ) ;
292 END IF;
293
294 FND_MESSAGE.SET_NAME('ONT', 'OE_SCH_INACTIVE_STD_ONLY');
295 FND_MESSAGE.SET_TOKEN('LTYPE',
296 nvl(Oe_Schedule_Util.sch_cached_line_type ,'0'));
297 OE_BULK_MSG_PUB.Add;
298
299 IF l_debug_level > 0 THEN
300 oe_debug_pub.add( 'OE_SCH_INACTIVE_STD_ONLY' , 1 ) ;
301 END IF;
302 X_return_status := FND_API.G_RET_STS_ERROR;
303 END IF;
304
305
306 -- ATO checks for config support
307
308 IF p_line_rec.ato_line_id(p_index) is not null AND
309 NOT(p_line_rec.ato_line_id(p_index) = p_line_rec.line_id(p_index) AND
310 p_line_rec.item_type_code(p_index) IN ( OE_GLOBALS.G_ITEM_OPTION,
311 OE_GLOBALS.G_ITEM_STANDARD))
312 THEN
313 IF MSC_ATP_GLOBAL.GET_APS_VERSION <> 10 AND
314 p_line_rec.ship_from_org_id(p_index) is NULL
315 THEN
316 FND_MESSAGE.SET_NAME('ONT','OE_SCH_ATO_WHSE_REQD');
317 OE_BULK_MSG_PUB.Add;
318 IF l_debug_level > 0 THEN
319 oe_debug_pub.add( 'OE_SCH_ATO_WHSE_REQD' , 1 ) ;
320 END IF;
321 x_return_status := FND_API.G_RET_STS_ERROR;
322
323 END IF;
324 END IF; -- Gop code level
325
326 IF p_line_rec.ato_line_id(p_index) = p_line_rec.line_id(p_index) AND
327 p_line_rec.item_type_code(p_index) in ('STANDARD','OPTION') AND
328 fnd_profile.value('INV_CTP') = '5'
329 THEN
330
331 l_org_id := nvl(p_line_rec.ship_from_org_id(p_index), OE_BULK_ORDER_PVT.G_ITEM_ORG);
332 l_c_index := OE_BULK_CACHE.Load_Item
333 (p_key1 => p_line_rec.inventory_item_id(p_index)
334 ,p_key2 => l_org_id
335 ,p_default_attributes => 'Y');
336
337 l_make_buy := OE_BULK_CACHE.G_ITEM_TBL(l_c_index).planning_make_buy_code;
338
339 IF l_debug_level > 0 THEN
340 oe_debug_pub.add( 'L_MAKE_BUY' || L_MAKE_BUY , 2 ) ;
341 END IF;
342
343 IF nvl(l_make_buy,1) <> 2 THEN
344 BEGIN
345 SELECT BILL_SEQUENCE_ID
346 INTO l_bill_seq_id
347 FROM BOM_BILL_OF_MATERIALS
348 WHERE ORGANIZATION_ID = l_org_id
349 AND ASSEMBLY_ITEM_ID = p_line_rec.inventory_item_id(p_index)
350 AND ALTERNATE_BOM_DESIGNATOR IS NULL;
351
352 EXCEPTION
353 WHEN NO_DATA_FOUND THEN
354 IF l_debug_level > 0 THEN
355 oe_debug_pub.add( 'OE_BOM_NO_BILL_IN_SHP_ORG' , 2 ) ;
356 END IF;
357
358 FND_MESSAGE.SET_NAME('ONT','OE_BOM_NO_BILL_IN_SHP_ORG');
359 FND_MESSAGE.SET_TOKEN('ITEM',p_line_rec.ordered_item(p_index));
360
361 Select ORGANIZATION_CODE
362 Into l_org_code
363 From ORG_ORGANIZATION_DEFINITIONS
364 Where ORGANIZATION_ID = l_org_id;
365 IF l_debug_level > 0 THEN
366 oe_debug_pub.add( 'ORGANIZATION CODE:'||L_ORG_CODE , 2 ) ;
367 END IF;
368 FND_MESSAGE.SET_TOKEN('ORG',l_org_code);
369 OE_MSG_PUB.Add;
370 x_return_status := FND_API.G_RET_STS_ERROR;
371
372 WHEN OTHERS THEN
373 Null;
374 END;
375 END IF;
376 END IF;
377
378 END Check_Scheduling_Attributes;
379
380
381 PROCEDURE Get_Line_Number(p_line_number IN OUT NOCOPY OE_WSH_BULK_GRP.T_NUM,
382 p_header_id IN OE_WSH_BULK_GRP.T_NUM)
383 IS
384 l_ctr NUMBER := 1;
385 l_header_id NUMBER := 1;
386 BEGIN
387 l_header_id := p_header_id(1);
388 FOR i IN 1..p_line_number.COUNT LOOP
389 IF p_header_id(i) <> l_header_id THEN
390 l_header_id := p_header_id(i);
391 l_ctr := 1;
392 END IF;
393 p_line_number(i) := l_ctr;
394 l_ctr := l_ctr + 1;
395 END LOOP;
396
397 END Get_Line_Number;
398
399 ----------------------------------------------------------------------
400 -- PROCEDURE Get_Item_Info
401 --
402 -- If inventory_item_id is null on the record, this procedure is called
403 -- to retrieve ID based on value columns passed in interface tables.
404 -- If item identifier type indicates that the item is a CUSTOMER item
405 -- or a generic cross-referenced item, it will also query against
406 -- relevant APIs or cross reference tables to derive the ID.
407 -- NOTE: assumption is that item identifier type is either passed in
408 -- or defaulted prior to this procedure call i.e. it cannot be null.
409 ----------------------------------------------------------------------
410
411 PROCEDURE Get_Item_Info
412 ( p_index IN NUMBER
413 , p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE)
414 IS
415 l_structure fnd_flex_key_api.structure_type;
416 l_flexfield fnd_flex_key_api.flexfield_type;
417 l_segment_array fnd_flex_ext.segmentarray;
418 l_n_segments NUMBER;
419 l_segments FND_FLEX_KEY_API.SEGMENT_LIST;
420 l_id NUMBER;
421 failure_message varchar2(2000);
422 l_inventory_item VARCHAR2(240);
423 l_inventory_item_id_int NUMBER;
424 l_inventory_item_id_cust NUMBER;
425 l_inventory_item_id_gen NUMBER;
426 l_error_code VARCHAR2(9);
427 l_error_flag VARCHAR2(1);
428 l_error_message VARCHAR2(2000);
429 --
430 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
431 --
432 BEGIN
433 IF l_debug_level > 0 THEN
434 oe_debug_pub.add( 'ENTERING OE_BULK_PROCESS_LINE.Get_Item_Info' ) ;
435 END IF;
436
437 IF l_debug_level > 0 THEN
438 oe_debug_pub.add( 'order_source_id: ' ||p_line_rec.order_source_id(p_index));
439 oe_debug_pub.add( 'orig_sys_document_ref: ' || p_line_rec.orig_sys_document_ref(p_index));
440 oe_debug_pub.add( 'orig_sys_line_ref: ' || p_line_rec.orig_sys_line_ref(p_index));
441 oe_debug_pub.add( 'orig_sys_shipment_ref: ' || p_line_rec.orig_sys_shipment_ref(p_index));
442 oe_debug_pub.add( 'org_id: ' || p_line_rec.org_id(p_index));
443 END IF;
444
445 SELECT INVENTORY_ITEM_SEGMENT_1
446 , INVENTORY_ITEM_SEGMENT_2
447 , INVENTORY_ITEM_SEGMENT_3
448 , INVENTORY_ITEM_SEGMENT_4
449 , INVENTORY_ITEM_SEGMENT_5
450 , INVENTORY_ITEM_SEGMENT_6
451 , INVENTORY_ITEM_SEGMENT_7
452 , INVENTORY_ITEM_SEGMENT_8
453 , INVENTORY_ITEM_SEGMENT_9
454 , INVENTORY_ITEM_SEGMENT_10
455 , INVENTORY_ITEM_SEGMENT_11
456 , INVENTORY_ITEM_SEGMENT_12
457 , INVENTORY_ITEM_SEGMENT_13
458 , INVENTORY_ITEM_SEGMENT_14
459 , INVENTORY_ITEM_SEGMENT_15
460 , INVENTORY_ITEM_SEGMENT_16
461 , INVENTORY_ITEM_SEGMENT_17
462 , INVENTORY_ITEM_SEGMENT_18
463 , INVENTORY_ITEM_SEGMENT_19
464 , INVENTORY_ITEM_SEGMENT_20
465 , INVENTORY_ITEM
466 INTO l_segment_array(1)
467 , l_segment_array(2)
468 , l_segment_array(3)
469 , l_segment_array(4)
470 , l_segment_array(5)
471 , l_segment_array(6)
472 , l_segment_array(7)
473 , l_segment_array(8)
474 , l_segment_array(9)
475 , l_segment_array(10)
476 , l_segment_array(11)
477 , l_segment_array(12)
478 , l_segment_array(13)
479 , l_segment_array(14)
480 , l_segment_array(15)
481 , l_segment_array(16)
482 , l_segment_array(17)
483 , l_segment_array(18)
484 , l_segment_array(19)
485 , l_segment_array(20)
486 , l_inventory_item
487 FROM OE_LINES_IFACE_ALL
488 WHERE order_source_id = p_line_rec.order_source_id(p_index)
489 AND orig_sys_document_ref = p_line_rec.orig_sys_document_ref(p_index)
490 AND orig_sys_line_ref = p_line_rec.orig_sys_line_ref(p_index)
491 AND org_id = p_line_rec.org_id(p_index)
492 AND (nvl(orig_sys_shipment_ref,fnd_api.g_miss_char)
493 = nvl(p_line_rec.orig_sys_shipment_ref(p_index),fnd_api.g_miss_char)
494 OR -- added to fix bug 5394064
495 p_line_rec.orig_sys_shipment_ref(p_index) = 'OE_ORDER_LINES_ALL'||p_line_rec.line_id(p_index)||'.'||'1')
496 -- Bug 2764130 : there should be only one row for this doc/line ref
497 -- combination. If there are multiple rows, it will be errored out in
498 -- the duplicate check in procedure Entity.
499 AND rownum = 1;
500
501 oe_debug_pub.add( 'In Get_Item_Info 1' ) ;
502
503 ----------------------------------------------------------------------
504 --(1) Populate p_line_rec.inventory_item_id with ccid if any of the
505 --segments are passed instead of inventory_item_id
506 ----------------------------------------------------------------------
507
508 IF ( (l_segment_array(1) IS NOT NULL) OR
509 (l_segment_array(2) IS NOT NULL) OR
510 (l_segment_array(3) IS NOT NULL) OR
511 (l_segment_array(4) IS NOT NULL) OR
512 (l_segment_array(5) IS NOT NULL) OR
513 (l_segment_array(6) IS NOT NULL) OR
514 (l_segment_array(7) IS NOT NULL) OR
515 (l_segment_array(8) IS NOT NULL) OR
516 (l_segment_array(9) IS NOT NULL) OR
517 (l_segment_array(10) IS NOT NULL) OR
518 (l_segment_array(11) IS NOT NULL) OR
519 (l_segment_array(12) IS NOT NULL) OR
520 (l_segment_array(13) IS NOT NULL) OR
521 (l_segment_array(14) IS NOT NULL) OR
522 (l_segment_array(15) IS NOT NULL) OR
523 (l_segment_array(16) IS NOT NULL) OR
524 (l_segment_array(17) IS NOT NULL) OR
525 (l_segment_array(18) IS NOT NULL) OR
526 (l_segment_array(19) IS NOT NULL) OR
527 (l_segment_array(20) IS NOT NULL)
528 )
529 THEN
530 FND_FLEX_KEY_API.SET_SESSION_MODE('customer_data');
531 l_flexfield := FND_FLEX_KEY_API.FIND_FLEXFIELD('INV', 'MSTK');
532 IF l_debug_level > 0 THEN
533 oe_debug_pub.add( 'AFTER FIND FLEXFIELD' ) ;
534 END IF;
535 l_structure.structure_number := 101;
536 FND_FLEX_KEY_API.GET_SEGMENTS(l_flexfield, l_structure, TRUE, l_n_segments, l_segments);
537 IF l_debug_level > 0 THEN
538 oe_debug_pub.add( 'SEGMENTS ENABLED = '||L_N_SEGMENTS ) ;
539 END IF;
540 IF l_debug_level > 0 THEN
541 oe_debug_pub.add( 'VALIDATION_ORG_ID = '||OE_BULK_ORDER_PVT.G_ITEM_ORG ) ;
542 END IF;
543 IF l_debug_level > 0 THEN
544 oe_debug_pub.add( 'ARRAY1 = '||L_SEGMENT_ARRAY ( 1 ) ) ;
545 END IF;
546 IF FND_FLEX_EXT.GET_COMBINATION_ID('INV', 'MSTK', 101, SYSDATE, l_n_segments, l_segment_array, l_id, OE_BULK_ORDER_PVT.G_ITEM_ORG) THEN
547 p_line_rec.inventory_item_id(p_index) := l_id;
548 IF l_debug_level > 0 THEN
549 oe_debug_pub.add( 'GET CCID = '||P_LINE_REC.INVENTORY_ITEM_ID ( P_INDEX ) ) ;
550 END IF;
551 RETURN;
552 ELSE
553 IF l_debug_level > 0 THEN
554 oe_debug_pub.add( 'ERROR IN GETTING CCID' ) ;
555 END IF;
556 failure_message := fnd_flex_ext.get_message;
557 OE_BULK_MSG_PUB.Add_TEXT(failure_message);
558 RAISE FND_API.G_EXC_ERROR;
559 IF l_debug_level > 0 THEN
560 oe_debug_pub.add( 'FAILURE MESSAGE = ' || SUBSTR ( FAILURE_MESSAGE , 1 , 50 ) ) ;
561 END IF;
562 IF l_debug_level > 0 THEN
563 oe_debug_pub.add( 'FAILURE MESSAGE = ' || SUBSTR ( FAILURE_MESSAGE , 51 , 50 ) ) ;
564 oe_debug_pub.add( 'FAILURE MESSAGE = ' || SUBSTR ( FAILURE_MESSAGE , 101 , 50 ) ) ;
565 END IF;
566 END IF;
567 END IF;
568
569
570 ----------------------------------------------------------------------
571 -- (2) If item value is passed, get ID by matching concatenated
572 -- segments with this value
573 ----------------------------------------------------------------------
574
575 IF l_inventory_item IS NOT NULL THEN
576
577 BEGIN
578 SELECT inventory_item_id
579 INTO l_inventory_item_id_int
580 FROM mtl_system_items_vl
581 WHERE concatenated_segments = l_inventory_item
582 AND customer_order_enabled_flag = 'Y'
583 AND bom_item_type in (1,2,4)
584 AND organization_id = OE_BULK_ORDER_PVT.G_ITEM_ORG;
585 EXCEPTION
586 WHEN NO_DATA_FOUND THEN
587 NULL;
588 END;
589
590 END IF;
591
592
593 ----------------------------------------------------------------------
594 -- (3) For INTERNAL items, return internal item ID
595 ----------------------------------------------------------------------
596
597 IF p_line_rec.item_identifier_type(p_index) = 'INT' THEN
598
599 p_line_rec.inventory_item_id(p_index) := l_inventory_item_id_int;
600
601 ----------------------------------------------------------------------
602 -- (4) For CUSTOMER items, get inv item ID using INV API if ordered item
603 -- or ordered item id fields are passed.
604 ----------------------------------------------------------------------
605
606 ELSIF p_line_rec.item_identifier_type(p_index) = 'CUST' THEN
607
608 IF p_line_rec.ordered_item_id(p_index) IS NULL
609 AND p_line_rec.ordered_item(p_index) IS NULL THEN
610 RETURN;
611 ENd IF;
612
613 IF p_line_rec.sold_to_org_id(p_index) IS NULL THEN
614 IF l_debug_level > 0 THEN
615 oe_debug_pub.add( 'SOLD TO ORG ID IS MISSING , CAN NOT GET CUST ITEM' ) ;
616 END IF;
617 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_CUSTOMER_ID');
618 OE_BULK_MSG_PUB.Add;
619 RAISE FND_API.G_EXC_ERROR;
620 END IF;
621
622 INV_CUSTOMER_ITEM_GRP.CI_Attribute_Value(
623 Z_Customer_Item_Id => p_line_rec.ordered_item_id(p_index)
624 , Z_Customer_Id => p_line_rec.sold_to_org_id(p_index)
625 , Z_Customer_Item_Number => p_line_rec.ordered_item(p_index)
626 , Z_Organization_Id => nvl(p_line_rec.ship_from_org_id(p_index)
627 ,OE_BULK_ORDER_PVT.G_ITEM_ORG)
628 , Z_Inventory_Item_Id => NULL
629 , Attribute_Name => 'INVENTORY_ITEM_ID'
630 , Error_Code => l_error_code
631 , Error_Flag => l_error_flag
632 , Error_Message => l_error_message
633 , Attribute_Value => l_inventory_item_id_cust
634 );
635
636 IF l_error_message IS NOT NULL THEN
637 IF l_debug_level > 0 THEN
638 oe_debug_pub.add( 'INV API CI_ATTR_VAL FOR INV_ITEM_ID RETURNED ERROR' ) ;
639 END IF;
640 FND_MESSAGE.SET_NAME('ONT','OE_INV_CUS_ITEM');
641 FND_MESSAGE.SET_TOKEN('ERROR_CODE', l_error_code);
642 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE', l_error_message);
643 OE_BULK_MSG_PUB.Add;
644 END IF;
645
646 INV_CUSTOMER_ITEM_GRP.CI_Attribute_Value(
647 Z_Customer_Item_Id => p_line_rec.ordered_item_id(p_index)
648 , Z_Customer_Id => p_line_rec.sold_to_org_id(p_index)
649 , Z_Customer_Item_Number => p_line_rec.ordered_item(p_index)
650 , Z_Organization_Id => nvl(p_line_rec.ship_from_org_id(p_index)
651 ,OE_BULK_ORDER_PVT.G_ITEM_ORG)
652 , Z_Inventory_Item_Id => NULL
653 , Attribute_Name => 'CUSTOMER_ITEM_ID'
654 , Error_Code => l_error_code
655 , Error_Flag => l_error_flag
656 , Error_Message => l_error_message
657 , Attribute_Value => p_line_rec.ordered_item_id(p_index)
658 );
659
660 IF l_error_message IS NOT NULL THEN
661 IF l_debug_level > 0 THEN
662 oe_debug_pub.add( 'INV API CI_ATTR_VAL FOR ORDERED_ITEM_ID RETURNED ERROR' ) ;
663 END IF;
664 FND_MESSAGE.SET_NAME('ONT','OE_INV_CUS_ITEM');
665 FND_MESSAGE.SET_TOKEN('ERROR_CODE', l_error_code);
666 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE', l_error_message);
667 OE_BULK_MSG_PUB.Add;
668 END IF;
669
670 IF l_inventory_item_id_int <> l_inventory_item_id_cust THEN
671 IF l_debug_level > 0 THEN
672 oe_debug_pub.add( 'INV ITEM AND CUST ITEM MISMATCH' ) ;
673 END IF;
674 FND_MESSAGE.SET_NAME('ONT','OE_INV_INT_CUS_ITEM_ID');
675 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID',l_inventory_item_id_int);
676 FND_MESSAGE.SET_TOKEN('CUST_ITEM_ID',l_inventory_item_id_cust);
677 OE_BULK_MSG_PUB.Add;
678 ELSIF l_inventory_item_id_int IS NOT NULL
679 THEN
680 p_line_rec.inventory_item_id(p_index):= l_inventory_item_id_int;
681 ELSIF l_inventory_item_id_cust IS NOT NULL
682 THEN
683 p_line_rec.inventory_item_id(p_index):= l_inventory_item_id_cust;
684 END IF;
685
686 ----------------------------------------------------------------------
687 -- (4) For other item cross references, fetch INV item ID from cross
688 -- references table
689 ----------------------------------------------------------------------
690
691 ELSE
692
693 BEGIN
694 SELECT inventory_item_id
695 INTO l_inventory_item_id_gen
696 FROM mtl_cross_references
697 WHERE cross_reference_type = p_line_rec.item_identifier_type(p_index)
698 AND (organization_id = OE_BULK_ORDER_PVT.G_ITEM_ORG
699 OR organization_id IS NULL)
700 AND cross_reference = p_line_rec.ordered_item(p_index)
701 AND (inventory_item_id = l_inventory_item_id_int
702 OR l_inventory_item_id_int IS NULL);
703 EXCEPTION
704 WHEN NO_DATA_FOUND THEN
705 IF l_debug_level > 0 THEN
706 oe_debug_pub.add( 'NO DATA FOUND - GENERIC CROSS REF' ) ;
707 END IF;
708 NULL;
709 WHEN TOO_MANY_ROWS THEN
710 IF l_debug_level > 0 THEN
711 oe_debug_pub.add( 'TOO MANY ROWS ERROR: '||SQLERRM ) ;
712 END IF;
713 FND_MESSAGE.SET_NAME('ONT','OE_NOT_UNIQUE_ITEM');
714 FND_MESSAGE.SET_TOKEN('GENERIC_ITEM', p_line_rec.ordered_item(p_index));
715 OE_BULK_MSG_PUB.Add;
716 END;
717
718 IF l_inventory_item_Id_int <> l_inventory_item_id_gen
719 THEN
720 IF l_debug_level > 0 THEN
721 oe_debug_pub.add( 'WARNING: GENERIC AND INVENTORY ITEM ARE DIFFERENT' ) ;
722 END IF;
723 FND_MESSAGE.SET_NAME('ONT','OE_INV_INT_CUS_ITEM_ID');
724 FND_MESSAGE.SET_TOKEN('INVENTORY_ITEM_ID', l_inventory_item_id_int);
725 FND_MESSAGE.SET_TOKEN('CUST_ITEM_ID', l_inventory_item_id_gen);
726 OE_BULK_MSG_PUB.Add;
727 p_line_rec.inventory_item_id(p_index):= l_inventory_item_id_gen;
728 ELSIF l_inventory_item_id_int IS NOT NULL
729 THEN
730 p_line_rec.inventory_item_id(p_index):= l_inventory_item_id_int;
731 ELSIF l_inventory_item_id_gen IS NOT NULL
732 THEN
733 p_line_rec.inventory_item_id(p_index):= l_inventory_item_id_gen;
734 END IF;
735
736 END IF;
737
738
739
740
741 EXCEPTION
742 WHEN FND_API.G_EXC_ERROR THEN
743 p_line_rec.lock_control(p_index) := -99;
744 WHEN OTHERS THEN
745 IF l_debug_level > 0 THEN
746 oe_debug_pub.add( 'OTHERS ERROR , LINE.GET_ITEM_INFO' ) ;
747 END IF;
748 IF l_debug_level > 0 THEN
749 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
750 END IF;
751 IF OE_BULK_MSG_PUB.Check_Msg_Level(OE_BULK_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
752 THEN
753 OE_BULK_MSG_PUB.Add_Exc_Msg
754 ( G_PKG_NAME
755 , 'Get_Item_Info'
756 );
757 END IF;
758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759 END Get_Item_Info;
760
761 FUNCTION Validate_Subinventory
762 ( p_subinventory IN VARCHAR2
763 , p_inventory_item_id IN NUMBER
764 , p_ship_from_org_id IN NUMBER
765 , p_source_type_code IN VARCHAR2
766 , p_order_source_id IN NUMBER
767 )
768 RETURN BOOLEAN
769 IS
770 l_dummy VARCHAR2(10);
771 --
772 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
773 --
774 BEGIN
775
776 IF p_ship_from_org_id IS NOT NULL THEN
777
778 BEGIN
779 SELECT 'VALID'
780 INTO l_dummy
781 FROM MTL_SUBINVENTORIES_TRK_VAL_V
782 WHERE organization_id = p_ship_from_org_id
783 AND secondary_inventory_name = p_subinventory;
784 EXCEPTION
785 WHEN OTHERS THEN
786 fnd_message.set_name('ONT','OE_SUBINV_INVALID');
787 OE_BULK_MSG_PUB.Add;
788 RETURN FALSE;
789 END;
790
791 END IF;
792
793 IF p_source_type_code = 'INTERNAL' THEN
794
795 IF p_ship_from_org_id is null THEN
796
797 fnd_message.set_name('ONT', 'OE_ATTRIBUTE_REQUIRED');
798 fnd_message.set_token('ATTRIBUTE'
799 ,OE_Order_UTIL.Get_Attribute_Name('SHIP_FROM_ORG_ID'));
800 OE_BULK_MSG_PUB.Add;
801 RETURN FALSE;
802
803 ELSE
804
805 -- validate the subinv is allowed (expense/asset)
806 -- because defaulting can be defaulting an expense sub
807 -- and the INV profile is set to No.
808 IF l_debug_level > 0 THEN
809 oe_debug_pub.add( 'ENTITY: PROFILE EXPENSE_ASSET:' || FND_PROFILE.VALUE ( 'INV:EXPENSE_TO_ASSET_TRANSFER' ) , 5 ) ;
810 END IF;
811 BEGIN
812
813 select 'Y'
814 into l_dummy
815 from mtl_subinventories_trk_val_v sub
816 where sub.organization_id = p_ship_from_org_id
817 and sub.secondary_inventory_name = p_subinventory
818 and (fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER') = 1
819 OR
820 (fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER') <> 1
821 and nvl(p_order_source_id, -1) <> 10
822 )
823 OR
824 (fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER') <> 1
825 and nvl(p_order_source_id, -1) = 10
826 and 'N' = (select inventory_asset_flag
827 from mtl_system_items
828 where inventory_item_id = p_inventory_item_id
829 and organization_id = p_ship_from_org_id
830 )
831 )
832 OR
833 (fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER') <> 1
834 and nvl(p_order_source_id, -1) = 10
835 and 'Y' = (select inventory_asset_flag
836 from mtl_system_items
837 where inventory_item_id = p_inventory_item_id
838 and organization_id = p_ship_from_org_id
839 )
840 and sub.asset_inventory = 1
841 )
842 );
843 EXCEPTION
844 WHEN OTHERS THEN
845 fnd_message.set_name('ONT', 'OE_SUBINV_NON_ASSET');
846 OE_BULK_MSG_PUB.ADD;
847 RETURN FALSE;
848 END;
849
850 END IF; -- end if ship from is null
851
852 END IF; -- end if source type is internal
853
854 RETURN TRUE;
855
856 END Validate_Subinventory;
857
858
859 FUNCTION Validate_Item_Warehouse
860 ( p_inventory_item_id IN NUMBER
861 , p_ship_from_org_id IN NUMBER
862 , p_item_type_code IN VARCHAR2
863 , p_line_id IN NUMBER
864 , p_top_model_line_id IN NUMBER
865 , p_source_document_type_id IN NUMBER
866 , p_line_category_code IN VARCHAR2)
867 RETURN BOOLEAN
868 IS
869 l_dummy VARCHAR2(10);
870 BEGIN
871
872 -- The customer_order_enabled_flag for config item
873 -- is set to 'N'
874
875 /* Bug 1741158 chhung modify BEGIN */
876 IF p_line_category_code ='ORDER' THEN
877
878 /* for Internal Orders */
879 /* Internal Orders only support standard item */
880 IF p_source_document_type_id = 10 THEN
881 --perf bug 5121218, replace org_organization_definitions with
882 --hr_all_organization_units
883
884 /* SELECT null
885 INTO l_dummy
886 FROM mtl_system_items msi,
887 org_organization_definitions org
888 WHERE msi.inventory_item_id = p_inventory_item_id
889 AND org.organization_id= msi.organization_id
890 AND msi.internal_order_enabled_flag = 'Y'
891 AND sysdate <= nvl( org.disable_date, sysdate)
892 AND org.organization_id= p_ship_from_org_id
893 AND rownum=1;
894 */
895
896 SELECT null
897 INTO l_dummy
898 FROM mtl_system_items msi,
899 hr_all_organization_units org
900 WHERE msi.inventory_item_id = p_inventory_item_id
901 AND org.organization_id= msi.organization_id
902 AND msi.internal_order_enabled_flag = 'Y'
903 AND sysdate <= nvl( org.date_to, sysdate)
904 AND org.organization_id= p_ship_from_org_id
905 AND rownum=1;
906
907 ELSE /* other orders except Internal*/
908 IF p_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED OR
909 p_item_type_code = OE_GLOBALS.G_ITEM_CONFIG OR
910 p_item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
911 p_item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
912 (p_item_type_code = OE_GLOBALS.G_ITEM_KIT AND
913 nvl(p_top_model_line_id, -1) <> p_line_id)
914 THEN
915 --perf bug 5121218, replace org_organization_definitions with
916 --hr_all_organization_units
917
918 /* SELECT null
919 INTO l_dummy
920 FROM mtl_system_items msi,
921 org_organization_definitions org
922 WHERE msi.inventory_item_id = p_inventory_item_id
923 AND org.organization_id= msi.organization_id
924 AND sysdate <= nvl( org.disable_date, sysdate)
925 AND org.organization_id= p_ship_from_org_id
926 AND rownum=1;
927 */
928
929 SELECT null
930 INTO l_dummy
931 FROM mtl_system_items msi,
932 hr_all_organization_units org
933 WHERE msi.inventory_item_id = p_inventory_item_id
934 AND org.organization_id= msi.organization_id
935 AND sysdate <= nvl( org.date_to, sysdate)
936 AND org.organization_id= p_ship_from_org_id
937 AND rownum=1;
938
939 ELSE /* item type is MODEL,STANDARD,SERVICE,KIT in top most level*/
940 --perf bug 5121218, replace org_organization_definitions with
941 --hr_all_organization_units
942
943 /* SELECT null
944 INTO l_dummy
945 FROM mtl_system_items msi,
946 org_organization_definitions org
947 WHERE msi.inventory_item_id = p_inventory_item_id
948 AND org.organization_id= msi.organization_id
949 AND msi.customer_order_enabled_flag = 'Y'
950 AND sysdate <= nvl( org.disable_date, sysdate)
951 AND org.organization_id= p_ship_from_org_id
952 AND rownum=1;
953 */
954
955 SELECT null
956 INTO l_dummy
957 FROM mtl_system_items msi,
958 hr_all_organization_units org
959 WHERE msi.inventory_item_id = p_inventory_item_id
960 AND org.organization_id= msi.organization_id
961 AND msi.customer_order_enabled_flag = 'Y'
962 AND sysdate <= nvl( org.date_to, sysdate)
963 AND org.organization_id= p_ship_from_org_id
964 AND rownum=1;
965
966 END IF;
967 END IF;
968
969 ELSE /* p_line_category_code is 'RETURN */
970 -- It's for Return group!!
971 null;
972 END IF;
973
974 RETURN TRUE;
975
976 EXCEPTION
977 WHEN OTHERS THEN
978 RETURN FALSE;
979
980 END Validate_Item_Warehouse;
981
982
983 PROCEDURE Validate_Decimal_Quantity
984 ( p_item_id IN NUMBER
985 , p_item_type_code IN VARCHAR2
986 , p_input_quantity IN NUMBER
987 , p_uom_code IN VARCHAR2
988 , x_return_status IN OUT NOCOPY NUMBER
989 ) IS
990 l_validated_quantity NUMBER;
991 l_primary_quantity NUMBER;
992 l_qty_return_status VARCHAR2(1);
993 --
994 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
995 --
996 BEGIN
997 -- validate input quantity
998 IF (p_input_quantity is not null AND
999 p_input_quantity <> FND_API.G_MISS_NUM) THEN
1000
1001 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1002 IF nvl(p_input_quantity, 0) < 0 -- Process HVOP added check for negative as was missing -NB take out when support for RMAs needed
1003
1004 THEN
1005 FND_MESSAGE.SET_NAME('ONT', 'SO_PR_NEGATIVE_AMOUNT');
1006 OE_BULK_MSG_PUB.Add('Y','ERROR');
1007 x_return_status := -99;
1008 IF l_debug_level > 0 THEN
1009 oe_debug_pub.add ('Validate decimal quantity - quantity negative so error ', 3);
1010 END IF;
1011 RETURN;
1012 END IF; -- IF nvl(p_input_quantity, 0) < 0
1013
1014 END IF; -- IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1015
1016 IF trunc(p_input_quantity) <> p_input_quantity THEN
1017
1018 IF p_item_type_code is not NULL THEN
1019
1020 IF p_item_type_code IN ('MODEL', 'OPTION', 'KIT',
1021 'CLASS','INCLUDED', 'CONFIG')
1022 THEN
1023 IF l_debug_level > 0 THEN
1024 oe_debug_pub.add( 'ITEM IS CONFIG RELATED WITH DECIMAL QTY' , 2 ) ;
1025 END IF;
1026 FND_MESSAGE.SET_NAME('ONT', 'OE_CONFIG_NO_DECIMALS');
1027 OE_BULK_MSG_PUB.Add('Y','ERROR');
1028 x_return_status := -99;
1029
1030 ELSE
1031
1032 inv_decimals_pub.validate_quantity(
1033 p_item_id => p_item_id,
1034 p_organization_id =>
1035 OE_Bulk_Order_PVT.G_ITEM_ORG,
1036 p_input_quantity => p_input_quantity,
1037 p_uom_code => p_uom_code,
1038 x_output_quantity => l_validated_quantity,
1039 x_primary_quantity => l_primary_quantity,
1040 x_return_status => l_qty_return_status);
1041
1042 IF l_qty_return_status = 'W' OR
1043 l_qty_return_status = 'E' THEN
1044 IF l_debug_level > 0 THEN
1045 oe_debug_pub.add( 'INV DECIMAL API RETURN ' || L_QTY_RETURN_STATUS , 2 ) ;
1046 oe_debug_pub.add( 'INPUT_QTY '|| P_INPUT_QUANTITY , 2 ) ;
1047 oe_debug_pub.add( 'L_PRI_QTY '|| L_PRIMARY_QUANTITY , 2 ) ;
1048 oe_debug_pub.add( 'L_VAL_QTY '|| L_VALIDATED_QUANTITY , 2 ) ;
1049 END IF;
1050 /* bug 2926436 */
1051 IF l_qty_return_status = 'W' THEN
1052 fnd_message.set_name('ONT', 'OE_DECIMAL_MAX_PRECISION');
1053 END IF;
1054 --move INV error message to OE message stack
1055 OE_BULK_MSG_PUB.Add('Y','ERROR');
1056 x_return_status := -99;
1057 END IF;
1058
1059 END IF; -- config related item type
1060 END IF; -- item_type_code is null
1061 END IF; -- if not decimal qty
1062 END IF; -- quantity is null
1063
1064 END Validate_Decimal_Quantity;
1065
1066 FUNCTION Validate_Item_Fields
1067 ( p_inventory_item_id IN NUMBER
1068 , p_ordered_item_id IN NUMBER
1069 , p_item_identifier_type IN VARCHAR2
1070 , p_ordered_item IN VARCHAR2
1071 , p_sold_to_org_id IN NUMBER
1072 , p_line_category_code IN VARCHAR2 /*Bug 1678296- chhung adds*/
1073 , p_item_type_code IN VARCHAR2 /*Bug 1741158- chhung adds */
1074 , p_line_id IN NUMBER /*Bug 1741158- chhung adds */
1075 , p_top_model_line_id IN NUMBER /*Bug 1741158- chhung adds */
1076 , p_source_document_type_id IN NUMBER) /*Bug 1741158- chhung adds */
1077 RETURN BOOLEAN
1078 IS
1079 l_c_index NUMBER;
1080 l_dummy VARCHAR2(10);
1081 --
1082 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1083 --
1084 BEGIN
1085
1086 -- If inventory item is not assigned to the item validation org,
1087 -- cache api will raise no_data_found and this validation function
1088 -- will return FALSE.
1089 l_c_index := OE_Bulk_Cache.Load_Item(p_inventory_item_id,null);
1090
1091 IF nvl(p_item_identifier_type, 'INT') = 'INT' THEN
1092
1093 /* Bug 1741158 chhung modify BEGIN */
1094 IF p_line_category_code ='ORDER' THEN
1095
1096 IF p_source_document_type_id = 10
1097 THEN
1098 /* for Internal Orders */
1099 /* Internal Orders only support standard item */
1100 IF OE_Bulk_Cache.G_ITEM_TBL(l_c_index).internal_order_enabled_flag
1101 <> 'Y'
1102 THEN
1103 RETURN FALSE;
1104 END IF;
1105 ELSE /* other orders except Internal*/
1106
1107 IF p_item_type_code = OE_GLOBALS.G_ITEM_INCLUDED OR
1108 p_item_type_code = OE_GLOBALS.G_ITEM_CONFIG OR
1109 p_item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
1110 p_item_type_code = OE_GLOBALS.G_ITEM_OPTION OR
1111 (p_item_type_code = OE_GLOBALS.G_ITEM_KIT AND
1112 nvl(p_top_model_line_id, -1) <> p_line_id)
1113 THEN
1114 RETURN TRUE;
1115 ELSE /* item type is MODEL,STANDARD,SERVICE,KIT in top most level*/
1116 IF OE_Bulk_Cache.G_ITEM_TBL(l_c_index).customer_order_enabled_flag
1117 <> 'Y'
1118 THEN
1119 RETURN FALSE;
1120 END IF;
1121 END IF;
1122
1123 END IF;
1124
1125 /* Bug 1741158 chhung modify END */
1126 ELSE /* p_line_category_code is 'RETURN */
1127 -- It's for Return group!!
1128 null;
1129 END IF;
1130
1131 ELSIF nvl(p_item_identifier_type, 'INT') = 'CUST' THEN
1132 --Bug 1678296 chhung modify BEGIN
1133 IF p_line_category_code ='ORDER' THEN
1134 SELECT 'valid'
1135 INTO l_dummy
1136 FROM mtl_customer_items citems
1137 ,mtl_customer_item_xrefs cxref
1138 ,mtl_system_items_vl sitems
1139 WHERE citems.customer_item_id = cxref.customer_item_id
1140 AND cxref.inventory_item_id = sitems.inventory_item_id
1141 AND sitems.inventory_item_id = p_inventory_item_id
1142 AND sitems.organization_id =
1143 OE_Bulk_Order_PVT.G_ITEM_ORG
1144 AND citems.customer_item_id = p_ordered_item_id
1145 AND citems.customer_id = p_sold_to_org_id
1146 AND citems.inactive_flag = 'N'
1147 AND cxref.inactive_flag = 'N';
1148 ELSE /* line_category_code is 'RETURN'*/
1149 SELECT 'valid'
1150 INTO l_dummy
1151 FROM mtl_customer_items citems
1152 ,mtl_customer_item_xrefs cxref
1153 ,mtl_system_items_vl sitems
1154 WHERE citems.customer_item_id = cxref.customer_item_id
1155 AND cxref.inventory_item_id = sitems.inventory_item_id
1156 AND sitems.inventory_item_id = p_inventory_item_id
1157 AND sitems.organization_id = OE_Bulk_Order_PVT.G_ITEM_ORG
1158 AND citems.customer_item_id = p_ordered_item_id
1159 AND citems.customer_id = p_sold_to_org_id;
1160
1161 END IF;
1162 --Bug 1678296 chhung modify END
1163 ELSE
1164 IF p_ordered_item_id IS NOT NULL THEN
1165 RETURN FALSE;
1166 ELSE
1167 SELECT 'valid'
1168 INTO l_dummy
1169 FROM mtl_cross_reference_types types
1170 , mtl_cross_references items
1171 , mtl_system_items_vl sitems
1172 WHERE types.cross_reference_type = items.cross_reference_type
1173 AND items.inventory_item_id = sitems.inventory_item_id
1174 AND sitems.organization_id = OE_Bulk_Order_PVT.G_ITEM_ORG
1175 AND sitems.inventory_item_id = p_inventory_item_id
1176 AND items.cross_reference_type = p_item_identifier_type
1177 AND items.cross_reference = p_ordered_item
1178 AND (items.organization_id = sitems.organization_id
1179 OR items.org_independent_flag = 'Y'); /*Bug 1636532*/
1180 END IF;
1181 END IF;
1182
1183 RETURN TRUE;
1184
1185 EXCEPTION
1186 WHEN NO_DATA_FOUND THEN
1187 IF l_debug_level > 0 THEN
1188 oe_debug_pub.add( 'VALIDATE_ITEM_FIELDS: NO DATA FOUND' , 1 ) ;
1189 END IF;
1190 IF nvl(p_item_identifier_type, 'INT') = 'INT' THEN
1191 IF l_debug_level > 0 THEN
1192 oe_debug_pub.add( 'INVALID INTERNAL ITEM' ) ;
1193 END IF;
1194 ELSIF nvl(p_item_identifier_type, 'INT') = 'CUST' THEN
1195 IF l_debug_level > 0 THEN
1196 oe_debug_pub.add( 'INVALID CUSTOMER ITEM' ) ;
1197 END IF;
1198 ELSE
1199 IF l_debug_level > 0 THEN
1200 oe_debug_pub.add( 'INVALID GENERIC ITEM' ) ;
1201 END IF;
1202 END IF;
1203 RETURN FALSE;
1204 WHEN OTHERS THEN
1205 IF l_debug_level > 0 THEN
1206 oe_debug_pub.add( 'VALIDATE_ITEM_FIELDS: WHEN OTHERS' , 1 ) ;
1207 END IF;
1208 RETURN FALSE;
1209 END Validate_Item_Fields;
1210
1211 FUNCTION Validate_task
1212 ( p_project_id IN NUMBER
1213 , p_task_id IN NUMBER)
1214 RETURN BOOLEAN
1215 IS
1216 l_dummy VARCHAR2(10);
1217 BEGIN
1218 -- Please add validation here.
1219 RETURN TRUE;
1220 END Validate_task;
1221
1222 FUNCTION Validate_task_reqd
1223 ( p_project_id IN NUMBER
1224 ,p_ship_from_org_id IN NUMBER)
1225 RETURN BOOLEAN
1226 IS
1227 l_project_control_level NUMBER;
1228 BEGIN
1229 -- Please add validation here.
1230 RETURN TRUE;
1231 END Validate_task_reqd;
1232
1233 FUNCTION Validate_User_Item_Description
1234 ( p_user_item_description IN VARCHAR2)
1235
1236 RETURN BOOLEAN
1237 IS
1238 --
1239 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1240 --
1241 BEGIN
1242 IF LENGTHB(p_user_item_description) > 240 THEN
1243 fnd_message.set_name('ONT','ONT_USER_ITEM_DESC_TOO_LONG');
1244 OE_BULK_MSG_PUB.ADD;
1245 IF l_debug_level > 0 THEN
1246 oe_debug_pub.add( 'THE LENGTH OF USER_ITEM_DESCRIPTION SHOULD NOT EXCEED 240 CHARA CTERS FOR DROP SHIP LINES.' , 3 ) ;
1247 END IF;
1248 RETURN FALSE;
1249 END IF;
1250
1251 RETURN TRUE;
1252
1253 EXCEPTION
1254 WHEN OTHERS THEN
1255 IF l_debug_level > 0 THEN
1256 oe_debug_pub.add( 'VALIDATE_USER_ITEM_DESCRIPTION: WHEN OTHERS' , 1 ) ;
1257 END IF;
1258 RETURN FALSE;
1259 END Validate_User_Item_Description;
1260
1261
1262 PROCEDURE Unbook_Order
1263 ( p_header_index IN NUMBER
1264 ,p_last_line_index IN NUMBER
1265 ,p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE)
1266 IS
1267 l_header_id NUMBER;
1268 l_index NUMBER;
1269 l_ii_index NUMBER;
1270 l_last_ii_index NUMBER;
1271 --
1272 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1273 --
1274 BEGIN
1275
1276 l_header_id := OE_Bulk_Order_PVT.g_header_rec.header_id(p_header_index);
1277
1278 IF l_debug_level > 0 THEN
1279 oe_debug_pub.add( 'UNBOOK ORDER , HEADER ID:'||L_HEADER_ID ) ;
1280 oe_debug_pub.add( 'HEADER INDEX :'||P_HEADER_INDEX ) ;
1281 oe_debug_pub.add( 'LAST LINE INDEX :'||P_LAST_LINE_INDEX ) ;
1282 END IF;
1283
1284 -- Unset booking fields on header global
1285
1286 OE_Bulk_Order_PVT.g_header_rec.booked_flag(p_header_index) := 'N';
1287
1288 -- Unset booking fields on line global records
1289
1290 l_index := p_last_line_index;
1291 WHILE p_line_rec.header_id.EXISTS(l_index) LOOP
1292
1293 IF p_line_rec.header_id(l_index) = l_header_id THEN
1294 p_line_rec.booked_flag(l_index) := 'N';
1295 -- Un-set the global on included item records
1296 IF p_line_rec.ii_start_index(l_index) IS NOT NULL THEN
1297 l_ii_index := p_line_rec.ii_start_index(l_index);
1298 l_last_ii_index := l_ii_index +
1299 p_line_rec.ii_count(l_index) - 1;
1300 WHILE l_ii_index <= l_last_ii_index LOOP
1301 p_line_rec.booked_flag(l_ii_index) := 'N';
1302 l_ii_index := l_ii_index + 1;
1303 END LOOP;
1304 END IF;
1305 ELSE
1306 -- No more lines for this header
1307 EXIT;
1308 END IF;
1309
1310 l_index := l_index - 1;
1311
1312 END LOOP;
1313
1314 EXCEPTION
1315 WHEN OTHERS THEN
1316 IF l_debug_level > 0 THEN
1317 oe_debug_pub.add( 'OTHERS ERROR , LINE.UNBOOK_ORDER' ) ;
1318 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
1319 END IF;
1320 IF OE_BULK_MSG_PUB.Check_Msg_Level(OE_BULK_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1321 THEN
1322 OE_BULK_MSG_PUB.Add_Exc_Msg
1323 ( G_PKG_NAME
1324 , 'Unbook_Order'
1325 );
1326 END IF;
1327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1328 END Unbook_Order;
1329
1330 PROCEDURE Default_Record
1331 (p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
1332 ,p_index IN NUMBER
1333 ,p_header_index IN NUMBER
1334 ,x_return_status OUT NOCOPY VARCHAR2
1335 )
1336 IS
1337 l_c_index NUMBER;
1338 --
1339 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1340 --
1341 BEGIN
1342
1343 x_return_status := FND_API.G_RET_STS_SUCCESS;
1344
1345 -- Validation of defaulted attributes - when to do this, within the
1346 -- cache for each source? YES!
1347
1348 -- ASSUMPTION: The hierarchy for defaulting sources for each of the
1349 -- attributes can only be 1.Item 2.Ship To 3.Order Header
1350
1351 -- Populate Ship To first, since it may be used to override Header defaults
1352 IF p_line_rec.org_id(p_index) IS NULL THEN
1353 p_line_rec.org_id(p_index) :=
1354 OE_Bulk_Order_PVT.g_header_rec.org_id(p_header_index);
1355 END IF;
1356
1357
1358
1359 IF p_line_rec.ship_to_org_id(p_index) IS NULL THEN
1360 p_line_rec.ship_to_org_id(p_index) :=
1361 OE_Bulk_Order_PVT.g_header_rec.ship_to_org_id(p_header_index);
1362 END IF;
1363
1364 -- Default attributes that have item as the first source
1365
1366 IF p_line_rec.inventory_item_id(p_index) IS NOT NULL
1367 AND (p_line_rec.accounting_rule_id(p_index) IS NULL
1368 OR p_line_rec.invoicing_rule_id(p_index) IS NULL
1369 OR p_line_rec.ship_tolerance_above(p_index) IS NULL
1370 OR p_line_rec.ship_tolerance_below(p_index) IS NULL
1371 OR p_line_rec.order_quantity_uom(p_index) IS NULL
1372 OR p_line_rec.ship_from_org_id(p_index) IS NULL
1373 )
1374 THEN
1375
1376 BEGIN
1377
1378 l_c_index := OE_BULK_CACHE.Load_Item
1379 (p_key1 => p_line_rec.inventory_item_id(p_index)
1380 ,p_key2 => p_line_rec.ship_from_org_id(p_index)
1381 ,p_default_attributes => 'Y');
1382
1383 p_line_rec.accounting_rule_id(p_index) := nvl(p_line_rec.accounting_rule_id(p_index)
1384 ,OE_BULK_CACHE.G_ITEM_TBL(l_c_index).accounting_rule_id);
1385 p_line_rec.invoicing_rule_id(p_index) := nvl(p_line_rec.invoicing_rule_id(p_index)
1386 ,OE_BULK_CACHE.G_ITEM_TBL(l_c_index).invoicing_rule_id);
1387 p_line_rec.ship_tolerance_above(p_index) := nvl(p_line_rec.ship_tolerance_above(p_index)
1388 ,OE_BULK_CACHE.G_ITEM_TBL(l_c_index).ship_tolerance_above);
1389 p_line_rec.ship_tolerance_below(p_index) := nvl(p_line_rec.ship_tolerance_below(p_index)
1390 ,OE_BULK_CACHE.G_ITEM_TBL(l_c_index).ship_tolerance_below);
1391 p_line_rec.order_quantity_uom(p_index) := nvl(p_line_rec.order_quantity_uom(p_index)
1392 ,OE_BULK_CACHE.G_ITEM_TBL(l_c_index).primary_uom_code);
1393 p_line_rec.ship_from_org_id(p_index) := nvl(p_line_rec.ship_from_org_id(p_index)
1394 ,OE_BULK_CACHE.G_ITEM_TBL(l_c_index).default_shipping_org);
1395
1396 -- Invalid item - error message populated during validation
1397 EXCEPTION
1398 WHEN NO_DATA_FOUND THEN
1399 IF l_debug_level > 0 THEN
1400 oe_debug_pub.add( 'ITEM CACHE RETURNS NO DATA FOUND' ) ;
1401 END IF;
1402 x_return_status := FND_API.G_RET_STS_ERROR;
1403 END;
1404
1405 END IF;
1406
1407 -- Default attributes that have ship to as the first source
1408 -- or is the next source after item.
1409
1410 IF p_line_rec.ship_to_org_id(p_index) IS NOT NULL
1411 AND ( p_line_rec.fob_point_code(p_index) IS NULL
1412 OR p_line_rec.freight_terms_code(p_index) IS NULL
1413 OR p_line_rec.shipping_method_code(p_index) IS NULL
1414 OR p_line_rec.ship_tolerance_above(p_index) IS NULL
1415 OR p_line_rec.ship_tolerance_below(p_index) IS NULL
1416 OR p_line_rec.ship_from_org_id(p_index) IS NULL
1417 OR p_line_rec.item_identifier_type(p_index) IS NULL
1418 OR p_line_rec.demand_class_code(p_index) IS NULL
1419 )
1420 THEN
1421
1422 BEGIN
1423
1424 l_c_index := OE_BULK_CACHE.Load_Ship_To
1425 (p_key => p_line_rec.ship_to_org_id(p_index)
1426 ,p_default_attributes => 'Y');
1427
1428 p_line_rec.fob_point_code(p_index) := nvl(p_line_rec.fob_point_code(p_index)
1429 ,OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).fob_point_code);
1430 p_line_rec.freight_terms_code(p_index) := nvl(p_line_rec.freight_terms_code(p_index)
1431 ,OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).freight_terms_code);
1432 p_line_rec.shipping_method_code(p_index) := nvl(p_line_rec.shipping_method_code(p_index)
1433 ,OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).shipping_method_code);
1434 p_line_rec.ship_tolerance_above(p_index) := nvl(p_line_rec.ship_tolerance_above(p_index)
1435 ,OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).ship_tolerance_above);
1436 p_line_rec.ship_tolerance_below(p_index) := nvl(p_line_rec.ship_tolerance_below(p_index)
1437 ,OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).ship_tolerance_below);
1438 p_line_rec.ship_from_org_id(p_index) := nvl(p_line_rec.ship_from_org_id(p_index)
1439 ,OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).ship_from_org_id);
1440 p_line_rec.item_identifier_type(p_index) := nvl(p_line_rec.item_identifier_type(p_index)
1441 ,OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).item_identifier_type);
1442 p_line_rec.demand_class_code(p_index) := nvl(p_line_rec.demand_class_code(p_index)
1443 ,OE_BULK_CACHE.G_SHIP_TO_TBL(l_c_index).demand_class_code);
1444
1445 -- Invalid ship to - error message populated during validation
1446 EXCEPTION
1447 WHEN NO_DATA_FOUND THEN
1448 IF l_debug_level > 0 THEN
1449 oe_debug_pub.add( 'SHIP TO CACHE RETURNS NO DATA FOUND' ) ;
1450 END IF;
1451 x_return_status := FND_API.G_RET_STS_ERROR;
1452 END;
1453
1454 END IF;
1455
1456 -- Default line type from order type
1457
1458 IF p_line_rec.line_type_id(p_index) IS NULL THEN
1459 l_c_index := OE_Bulk_Cache.Load_Order_Type
1460 (p_key => OE_Bulk_Order_PVT.g_header_rec.order_type_id(p_header_index)
1461 ,p_default_attributes => 'Y'
1462 );
1463 p_line_rec.line_type_id(p_index) :=
1464 OE_BULK_CACHE.G_ORDER_TYPE_TBL(l_c_index).default_outbound_line_type_id;
1465 END IF;
1466
1467 -- Default remaining attributes from Order Header
1468
1469 IF p_line_rec.accounting_rule_id(p_index) IS NULL THEN
1470 p_line_rec.accounting_rule_id(p_index) :=
1471 OE_Bulk_Order_PVT.g_header_rec.accounting_rule_id(p_header_index);
1472 END IF;
1473
1474 IF p_line_rec.demand_class_code(p_index) IS NULL THEN
1475 p_line_rec.demand_class_code(p_index) :=
1476 OE_Bulk_Order_PVT.g_header_rec.demand_class_code(p_header_index);
1477 END IF;
1478
1479 IF p_line_rec.fob_point_code(p_index) IS NULL THEN
1480 p_line_rec.fob_point_code(p_index) :=
1481 OE_Bulk_Order_PVT.g_header_rec.fob_point_code(p_header_index);
1482 END IF;
1483
1484 IF p_line_rec.freight_terms_code(p_index) IS NULL THEN
1485 p_line_rec.freight_terms_code(p_index) :=
1486 OE_Bulk_Order_PVT.g_header_rec.freight_terms_code(p_header_index);
1487 END IF;
1488
1489 IF p_line_rec.invoicing_rule_id(p_index) IS NULL THEN
1490 p_line_rec.invoicing_rule_id(p_index) :=
1491 OE_Bulk_Order_PVT.g_header_rec.invoicing_rule_id(p_header_index);
1492 END IF;
1493
1494 IF p_line_rec.payment_term_id(p_index) IS NULL THEN
1495 p_line_rec.payment_term_id(p_index) :=
1496 OE_Bulk_Order_PVT.g_header_rec.payment_term_id(p_header_index);
1497 END IF;
1498
1499 IF p_line_rec.price_list_id(p_index) IS NULL THEN
1500 p_line_rec.price_list_id(p_index) :=
1501 OE_Bulk_Order_PVT.g_header_rec.price_list_id(p_header_index);
1502 END IF;
1503
1504 IF p_line_rec.salesrep_id(p_index) IS NULL THEN
1505 p_line_rec.salesrep_id(p_index) :=
1506 OE_Bulk_Order_PVT.g_header_rec.salesrep_id(p_header_index);
1507 END IF;
1508
1509 IF p_line_rec.ship_tolerance_above(p_index) IS NULL THEN
1510 p_line_rec.ship_tolerance_above(p_index) :=
1511 OE_Bulk_Order_PVT.g_header_rec.ship_tolerance_above(p_header_index);
1512 END IF;
1513
1514 IF p_line_rec.ship_tolerance_below(p_index) IS NULL THEN
1515 p_line_rec.ship_tolerance_below(p_index) :=
1516 OE_Bulk_Order_PVT.g_header_rec.ship_tolerance_below(p_header_index);
1517 END IF;
1518
1519 IF p_line_rec.shipping_method_code(p_index) IS NULL THEN
1520 p_line_rec.shipping_method_code(p_index) :=
1521 OE_Bulk_Order_PVT.g_header_rec.shipping_method_code(p_header_index);
1522 END IF;
1523
1524 IF p_line_rec.shipment_priority_code(p_index) IS NULL THEN
1525 p_line_rec.shipment_priority_code(p_index) :=
1526 OE_Bulk_Order_PVT.g_header_rec.shipment_priority_code(p_header_index);
1527 END IF;
1528
1529 IF p_line_rec.ship_from_org_id(p_index) IS NULL THEN
1530 p_line_rec.ship_from_org_id(p_index) :=
1531 OE_Bulk_Order_PVT.g_header_rec.ship_from_org_id(p_header_index);
1532 END IF;
1533
1534 IF p_line_rec.tax_exempt_flag(p_index) IS NULL THEN
1535 p_line_rec.tax_exempt_flag(p_index) :=
1536 OE_Bulk_Order_PVT.g_header_rec.tax_exempt_flag(p_header_index);
1537 END IF;
1538
1539
1540 -- Constant Value Defaults
1541
1542 IF p_line_rec.pricing_date(p_index) IS NULL THEN
1543 p_line_rec.pricing_date(p_index) := sysdate;
1544 END IF;
1545
1546 IF p_line_rec.request_date(p_index) IS NULL THEN
1547 p_line_rec.request_date(p_index) := sysdate;
1548 END IF;
1549
1550 IF p_line_rec.tax_date(p_index) IS NULL THEN
1551 p_line_rec.tax_date(p_index) := sysdate;
1552 END IF;
1553
1554 IF p_line_rec.tax_exempt_flag(p_index) IS NULL THEN
1555 p_line_rec.tax_exempt_flag(p_index) := 'S'; -- 'Standard'
1556 END IF;
1557
1558 EXCEPTION
1559 WHEN OTHERS THEN
1560 IF l_debug_level > 0 THEN
1561 oe_debug_pub.add( 'OTHERS ERROR , LINE.DEFAULT_RECORD' ) ;
1562 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
1563 END IF;
1564 IF OE_BULK_MSG_PUB.Check_Msg_Level(OE_BULK_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1565 THEN
1566 OE_BULK_MSG_PUB.Add_Exc_Msg
1567 ( G_PKG_NAME
1568 , 'Default_Record'
1569 );
1570 END IF;
1571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1572 END Default_Record;
1573
1574 PROCEDURE Populate_Internal_Fields
1575 (p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
1576 ,p_index IN NUMBER
1577 ,p_header_index IN NUMBER
1578 ,p_process_tax IN VARCHAR2 DEFAULT 'N'
1579 ,p_process_configurations IN VARCHAR2 DEFAULT 'N'
1580 ,x_unsupported_feature OUT NOCOPY VARCHAR2
1581 ,x_return_status OUT NOCOPY VARCHAR2
1582 )
1583 IS
1584 l_c_index NUMBER;
1585 l_d_index NUMBER;
1586 l_inventory_item_id NUMBER;
1587 l_return_status VARCHAR2(1);
1588 l_msg_count NUMBER;
1589 l_msg_data VARCHAR2(2000);
1590 l_inventory_item_id_cust NUMBER;
1591 --
1592 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1593 l_return_code NUMBER; -- INVCONV
1594 --
1595 BEGIN
1596
1597 -- Line_ID is pre-generated and read from interface tables
1598 -- This is to reduce contention as sequential ids will be
1599 -- assigned to each parallel thread.
1600 -- (Refer OEBVIMNB.pls)
1601
1602 -------------------------------------------------------------------
1603 -- Populate Fields from Header
1604 -------------------------------------------------------------------
1605 oe_debug_pub.add( 'Populate_Internal_Fields 1' ) ;
1606 p_line_rec.header_id(p_index) :=
1607 OE_Bulk_Order_PVT.g_header_rec.header_id(p_header_index);
1608
1609 p_line_rec.booked_flag(p_index) :=
1610 OE_Bulk_Order_PVT.g_header_rec.booked_flag(p_header_index);
1611
1612 IF p_line_rec.sold_to_org_id(p_index) IS NULL THEN
1613 p_line_rec.sold_to_org_id(p_index) :=
1614 OE_Bulk_Order_PVT.g_header_rec.sold_to_org_id(p_header_index);
1615 END IF;
1616
1617 IF p_line_rec.ship_to_org_id(p_index) IS NULL THEN
1618 p_line_rec.ship_to_org_id(p_index) :=
1619 OE_Bulk_Order_PVT.g_header_rec.ship_to_org_id(p_header_index);
1620 END IF;
1621
1622 IF p_line_rec.invoice_to_org_id(p_index) IS NULL THEN
1623 p_line_rec.invoice_to_org_id(p_index) :=
1624 OE_Bulk_Order_PVT.g_header_rec.invoice_to_org_id(p_header_index);
1625 ELSIF p_line_rec.invoice_to_org_id(p_index) <>
1626 OE_Bulk_Order_PVT.g_header_rec.invoice_to_org_id(p_header_index)
1627 THEN
1628 FND_MESSAGE.SET_NAME('ONT','OE_BULK_DIFF_INVOICE_TO');
1629 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
1630 x_return_status := FND_API.G_RET_STS_ERROR;
1631 END IF;
1632
1633
1634 -------------------------------------------------------------------
1635 -- Constant Value Internal Defaults
1636 -------------------------------------------------------------------
1637
1638 IF p_line_rec.calculate_price_flag(p_index) IS NULL THEN
1639 p_line_rec.calculate_price_flag(p_index) := 'Y';
1640 END IF;
1641
1642 --PIB
1643 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1644 IF p_line_rec.pricing_date(p_index) IS NULL THEN
1645 p_line_rec.pricing_date(p_index) := sysdate;
1646 END IF;
1647 END IF;
1648 --PIB
1649
1650 IF p_line_rec.item_identifier_type(p_index) IS NULL THEN
1651 p_line_rec.item_identifier_type(p_index) := 'INT';
1652 END IF;
1653
1654 IF p_line_rec.source_type_code(p_index) IS NULL THEN
1655 p_line_rec.source_type_code(p_index) := 'INTERNAL';
1656 END IF;
1657
1658 IF p_line_rec.cancelled_quantity(p_index) IS NULL THEN
1659 p_line_rec.cancelled_quantity(p_index) := 0;
1660 END IF;
1661
1662 IF p_line_rec.option_flag(p_index) IS NULL THEN
1663 IF p_line_rec.item_type_code(p_index) = 'OPTION' THEN
1664 p_line_rec.option_flag(p_index) := 'Y';
1665 ELSE p_line_rec.option_flag(p_index) := 'N';
1666 END IF;
1667 END IF;
1668
1669 oe_debug_pub.add( 'Populate_Internal_Fields 2' ) ;
1670 IF OE_Bulk_Order_PVT.G_IMPORT_SHIPMENTS = 'YES'
1671 AND p_line_rec.orig_sys_shipment_ref(p_index) IS NULL
1672 AND nvl(p_line_rec.source_document_id(p_index),0) <> 10
1673 THEN
1674 p_line_rec.orig_sys_shipment_ref(p_index)
1675 := 'OE_ORDER_LINES_ALL'||p_line_rec.line_id(p_index)||'.'||'1';
1676 END IF;
1677
1678 IF p_line_rec.ship_from_org_id(p_index) IS NOT NULL THEN
1679 p_line_rec.re_source_flag(p_index) := 'N';
1680 END IF;
1681
1682 -------------------------------------------------------------------
1683 -- Value To ID conversion for Item (includes Item Cross References)
1684 -------------------------------------------------------------------
1685 IF p_line_rec.inventory_item_id(p_index) IS NULL THEN
1686
1687 Get_Item_Info(p_index => p_index
1688 ,p_line_rec => p_line_rec
1689 );
1690
1691
1692 IF l_debug_level > 0 THEN
1693 oe_debug_pub.add( 'INVENTORY_ITEM_ID after Get_Item_Info: ' ||
1694 p_line_rec.inventory_item_id(p_index)) ;
1695 END IF;
1696
1697
1698 END IF;
1699
1700 -- Bug 2411113 - Populate ordered item field
1701
1702 IF p_line_rec.ordered_item(p_index) IS NULL
1703 AND p_line_rec.item_identifier_type(p_index) IN ('INT', 'CUST')
1704 THEN
1705
1706 IF l_debug_level > 0 THEN
1707 oe_debug_pub.add( 'CALL GET_ORDERED_ITEM' ) ;
1708 END IF;
1709
1710 Oe_Oe_Form_Line.Get_Ordered_Item
1711 (x_return_status => l_return_status,
1712 x_msg_count => l_msg_count,
1713 x_msg_data => l_msg_data,
1714 p_item_identifier_type =>p_line_rec.item_identifier_type(p_index),
1715 p_inventory_item_id => p_line_rec.inventory_item_id(p_index),
1716 p_ordered_item_id => p_line_rec.ordered_item_id(p_index),
1717 p_sold_to_org_id => p_line_rec.sold_to_org_id(p_index),
1718 x_ordered_item => p_line_rec.ordered_item(p_index)
1719 );
1720
1721 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1722 x_return_status := FND_API.G_RET_STS_ERROR;
1723 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1725 END IF;
1726
1727 END IF;
1728
1729
1730 -------------------------------------------------------------------
1731 -- Populate Internal Fields derived from Item
1732 -------------------------------------------------------------------
1733 oe_debug_pub.add( 'Populate_Internal_Fields 3' ) ;
1734 IF p_line_rec.inventory_item_id(p_index) IS NOT NULL THEN
1735
1736 l_inventory_item_id := p_line_rec.inventory_item_id(p_index);
1737 l_c_index := OE_BULK_CACHE.Load_Item
1738 (p_key1 => l_inventory_item_id
1739 ,p_key2 => p_line_rec.ship_from_org_id(p_index)
1740 );
1741 oe_debug_pub.add( 'Populate_Internal_Fields 3a' ) ;
1742
1743 -- VAlidate bom item type
1744 IF p_process_configurations = 'Y' THEN
1745 oe_debug_pub.add( 'Populate_Internal_Fields a' ) ;
1746 -- Error if unsupported Item Type for Bulk Mode
1747 IF (OE_BULK_CACHE.G_ITEM_TBL(l_c_index).bom_item_type = 4
1748 AND OE_BULK_CACHE.G_ITEM_TBL(l_c_index).service_item_flag = 'Y')
1749 THEN -- Service item type
1750 oe_debug_pub.add( 'Populate_Internal_Fields ab' ) ;
1751 FND_MESSAGE.SET_NAME('ONT','OE_BULK_NOT_SUPP_ITEM_TYPE');
1752 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
1753 x_return_status := FND_API.G_RET_STS_ERROR;
1754 x_unsupported_feature := 'Y';
1755 p_line_rec.lock_control(p_index) := -98;
1756 ELSE
1757 oe_debug_pub.add( 'Populate_Internal_Fields bc' ) ;
1758 --bug 3798477
1759 IF (OE_BULK_CACHE.G_ITEM_TBL(l_c_index).ont_pricing_qty_source = 'S' AND
1760 OE_BULK_CACHE.G_ITEM_TBL(l_c_index).tracking_quantity_ind = 'P' AND
1761 OE_BULK_CACHE.G_ITEM_TBL(l_c_index).wms_enabled_flag = 'Y' )
1762 OR -- 4282392
1763 (OE_BULK_CACHE.G_ITEM_TBL(l_c_index).tracking_quantity_ind = 'PS' AND
1764 OE_BULK_CACHE.G_ITEM_TBL(l_c_index).ont_pricing_qty_source = 'S')
1765 THEN
1766 OE_BULK_ORDER_PVT.G_CATCHWEIGHT := TRUE;
1767 oe_debug_pub.add( 'Populate_Internal_Fields b' ) ;
1768 END IF;
1769 --bug 3798477
1770 oe_debug_pub.add( 'Populate_Internal_Fields bcc' ) ;
1771 IF l_debug_level > 0 THEN
1772 oe_debug_pub.add('ITEM_TYPE_CODE = '|| p_line_rec.item_type_code(p_index), 4 );
1773 END IF;
1774
1775
1776 IF p_line_rec.item_type_code(p_index) IS NULL THEN
1777
1778 IF OE_BULK_CACHE.G_ITEM_TBL(l_c_index).bom_item_type = 1 AND
1779 nvl(p_line_rec.top_model_line_id(p_index), -1)
1780 = p_line_rec.line_id(p_index)
1781 THEN
1782 p_line_rec.item_type_code(p_index) := 'MODEL';
1783 END IF;
1784
1785 oe_debug_pub.add( 'Populate_Internal_Fields aa' ) ;
1786
1787 IF OE_BULK_CACHE.G_ITEM_TBL(l_c_index).bom_item_type = 1 AND
1788 nvl(OE_BULK_CACHE.G_Item_Tbl(l_c_index).replenish_to_order_flag, 'N') = 'Y' AND
1789 nvl(p_line_rec.top_model_line_id(p_index), -1) <> p_line_rec.line_id(p_index)
1790 THEN
1791
1792 p_line_rec.item_type_code(p_index) := 'CLASS';
1793 END IF;
1794
1795 IF OE_BULK_CACHE.G_ITEM_TBL(l_c_index).bom_item_type = 2 THEN
1796 p_line_rec.item_type_code(p_index) := 'CLASS';
1797 END IF;
1798
1799 IF OE_BULK_CACHE.G_ITEM_TBL(l_c_index).bom_item_type = 4 THEN
1800
1801 IF OE_BULK_CACHE.G_ITEM_TBL(l_c_index).pick_components_flag = 'Y' THEN
1802 p_line_rec.item_type_code(p_index) := 'KIT';
1803 ELSIF p_line_rec.top_model_line_id(p_index) IS NOT NULL THEN
1804 p_line_rec.item_type_code(p_index) := 'OPTION';
1805 oe_debug_pub.add( 'Populate_Internal_Fields bb' ) ;
1806 ELSE
1807 -- Standard Item
1808 p_line_rec.item_type_code(p_index) := OE_GLOBALS.G_ITEM_STANDARD;
1809 END IF;
1810 END IF; --bom_item_type = 4
1811
1812 END IF; -- item_type_code IS NULL
1813
1814 -- Logic before config support change
1815 IF OE_BULK_CACHE.G_ITEM_TBL(l_c_index).bom_item_type = 4 AND
1816 OE_BULK_CACHE.G_ITEM_TBL(l_c_index).pick_components_flag = 'Y'
1817 THEN
1818 p_line_rec.component_code(p_index) := to_char(l_inventory_item_id);
1819 p_line_rec.top_model_line_id(p_index) := p_line_rec.line_id(p_index);
1820 oe_debug_pub.add( 'Populate_Internal_Fields cc' ) ;
1821 --p_line_rec.ship_model_complete_flag(p_index)
1822 -- := OE_BULK_CACHE.G_ITEM_TBL(l_c_index).ship_model_complete_flag;
1823 p_line_rec.ship_tolerance_above(p_index) := 0;
1824 p_line_rec.ship_tolerance_below(p_index) := 0;
1825 END IF; --bom_item_type = 4
1826
1827 -- Set ato_line_id on ato_item
1828 oe_debug_pub.add( 'Populate_Internal_Fields dd' ) ;
1829 IF p_line_rec.item_type_code(p_index) IN ('OPTION', 'STANDARD') AND
1830 nvl(OE_BULK_CACHE.G_Item_Tbl(l_c_index).replenish_to_order_flag, 'N') = 'Y' AND
1831 nvl(OE_BULK_CACHE.G_Item_Tbl(l_c_index).build_in_wip_flag, 'N') = 'Y'
1832 THEN
1833 p_line_rec.ato_line_id(p_index) := p_line_rec.line_id(p_index);
1834 END IF;
1835 oe_debug_pub.add( 'Populate_Internal_Fields ee' ) ;
1836 -- If current line is Top Model Line
1837 IF p_line_rec.item_type_code(p_index) IN ( 'MODEL', 'KIT') THEN
1838 g_curr_top_index := p_index;
1839 p_line_rec.top_model_line_index(p_index) := g_curr_top_index;
1840 END IF;
1841 oe_debug_pub.add( 'Populate_Internal_Fields ff' ) ;
1842 -- Set top_model_line_index on child lines
1843 IF p_line_rec.top_model_line_id(p_index) IS NOT NULL AND
1844 p_line_rec.top_model_line_id(p_index)
1845 <> p_line_rec.line_id(p_index)
1846 THEN
1847 p_line_rec.top_model_line_index(p_index) := g_curr_top_index;
1848 END IF;
1849 oe_debug_pub.add( 'Populate_Internal_Fields gg' ) ;
1850 -- If the current line as an ATO under PTO or ATO model
1851 IF p_line_rec.ato_line_id(p_index) = p_line_rec.line_id(p_index) THEN
1852 g_curr_ato_index := p_index;
1853 p_line_rec.ato_line_index(p_index) := p_index;
1854 END IF;
1855 oe_debug_pub.add( 'Populate_Internal_Fields hh' ) ;
1856 -- Set ato_line_index based on ato_line_id on a line.
1857 IF p_line_rec.ato_line_id(p_index) IS NOT NULL AND
1858 p_line_rec.ato_line_id(p_index) <> p_line_rec.line_id(p_index)
1859 THEN
1860 p_line_rec.ato_line_index(p_index) := g_curr_ato_index;
1861 END IF;
1862 oe_debug_pub.add( 'Populate_Internal_Fields ii' ) ;
1863 -- set ship_model_complete_flag
1864 p_line_rec.ship_model_complete_flag(p_index) :=
1865 OE_BULK_CACHE.G_ITEM_TBL(l_c_index).ship_model_complete_flag;
1866 END IF; -- supported items
1867
1868 ELSE -- p_process_configuration = 'N'
1869 IF l_debug_level > 0 THEN
1870 oe_debug_pub.add( 'p_process_configuration = N in populate_internal_fields ', 5 ) ;
1871 END IF;
1872
1873
1874
1875 -- Error if unsupported Item Type for Bulk Mode
1876
1877 IF -- Model and Class Items
1878 OE_BULK_CACHE.G_ITEM_TBL(l_c_index).bom_item_type IN (1,2)
1879 -- Service Items
1880 OR (OE_BULK_CACHE.G_ITEM_TBL(l_c_index).bom_item_type = 4
1881 AND OE_BULK_CACHE.G_ITEM_TBL(l_c_index).service_item_flag = 'Y')
1882 -- ATO Items
1883 OR (OE_BULK_CACHE.G_ITEM_TBL(l_c_index).replenish_to_order_flag = 'Y')
1884 THEN
1885 oe_debug_pub.add( 'Populate_Internal_Fields 4a' ) ;
1886 FND_MESSAGE.SET_NAME('ONT','OE_BULK_NOT_SUPP_ITEM_TYPE');
1887 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
1888 x_return_status := FND_API.G_RET_STS_ERROR;
1889 x_unsupported_feature := 'Y';
1890 p_line_rec.lock_control(p_index) := -98;
1891
1892 ELSE
1893 oe_debug_pub.add( 'Populate_Internal_Fields 5a' ) ;
1894 --bug 3798477
1895 IF ( OE_BULK_CACHE.G_ITEM_TBL(l_c_index).ont_pricing_qty_source = 'S' AND -- INVCONV
1896 OE_BULK_CACHE.G_ITEM_TBL(l_c_index).tracking_quantity_ind = 'P' AND
1897 OE_BULK_CACHE.G_ITEM_TBL(l_c_index).wms_enabled_flag = 'Y')
1898 OR -- 4282392
1899 (OE_BULK_CACHE.G_ITEM_TBL(l_c_index).tracking_quantity_ind = 'PS' AND
1900 OE_BULK_CACHE.G_ITEM_TBL(l_c_index).ont_pricing_qty_source = 'S' )
1901
1902 THEN
1903 OE_BULK_ORDER_PVT.G_CATCHWEIGHT := TRUE;
1904 END IF;
1905 --bug 3798477
1906 -- Item Type can be STANDARD or KIT in BULK mode
1907
1908 IF OE_BULK_CACHE.G_ITEM_TBL(l_c_index).bom_item_type = 4 THEN
1909 oe_debug_pub.add( 'Populate_Internal_Fields 6a' ) ;
1910 -- KIT Item
1911 IF OE_BULK_CACHE.G_ITEM_TBL(l_c_index).pick_components_flag = 'Y' THEN
1912 oe_debug_pub.add( 'Populate_Internal_Fields 7a' ) ;
1913 p_line_rec.item_type_code(p_index) := OE_GLOBALS.G_ITEM_KIT;
1914 p_line_rec.component_code(p_index) := to_char(l_inventory_item_id);
1915 p_line_rec.top_model_line_id(p_index)
1916 := p_line_rec.line_id(p_index);
1917 p_line_rec.ship_model_complete_flag(p_index)
1918 := OE_BULK_CACHE.G_ITEM_TBL(l_c_index).ship_model_complete_flag;
1919
1920 p_line_rec.ship_tolerance_above(p_index) := 0;
1921 p_line_rec.ship_tolerance_below(p_index) := 0;
1922
1923 -- Standard Item
1924 ELSE
1925 p_line_rec.item_type_code(p_index) := OE_GLOBALS.G_ITEM_STANDARD;
1926 END IF;
1927 END IF;
1928 oe_debug_pub.add( 'Populate_Internal_Fields 8a' ) ;
1929 p_line_rec.shippable_flag(p_index)
1930 := OE_BULK_CACHE.G_ITEM_TBL(l_c_index).shippable_item_flag;
1931 oe_debug_pub.add( 'Populate_Internal_Fields 9a' ) ;
1932 END IF;
1933
1934 oe_debug_pub.add( 'Populate_Internal_Fields 4' ) ;
1935 END IF; -- p_process_configuration = 'Y'
1936 -- Process Characteristics
1937 -- HVOP - checks here for 1. qty2 - call function to default secondary quantity if necessary
1938
1939 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1940 -- INVCONV take out defaulting for grade as normal defaulting takes place.
1941
1942
1943 IF OE_BULK_CACHE.G_ITEM_TBL(l_c_index).tracking_quantity_ind = 'PS' -- INVCONV --
1944 THEN
1945 IF l_debug_level > 0 THEN
1946 oe_debug_pub.add( 'about to call CALL calculate_dual_quantity ' ) ;
1947 END IF;
1948 oe_debug_pub.add( 'Populate_Internal_Fields 5' ) ;
1949 calculate_dual_quantity(
1950 p_line_rec => p_line_rec
1951 ,p_index => p_index
1952 ,p_dualum_ind => OE_BULK_CACHE.G_ITEM_TBL(l_c_index).secondary_default_ind
1953 ,p_x_return_status => l_return_code
1954 );
1955 IF l_debug_level > 0 THEN
1956 oe_debug_pub.add( 'out of calculate_dual_quantity 1 ' ) ;
1957 END IF;
1958 IF l_return_code < 0 THEN
1959 IF l_debug_level > 0 THEN
1960 oe_debug_pub.add( 'error in calculate_dual_quantity 2 ' ) ;
1961 END IF;
1962
1963 FND_MESSAGE.SET_NAME('ONT','OE_BULK_OPM_DUAL_QTY_ERROR'); -- HVOP define better OM or GMI error code
1964 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
1965 x_return_status := FND_API.G_RET_STS_ERROR;
1966 END IF;
1967 oe_debug_pub.add( 'Populate_Internal_Fields 6' ) ;
1968 IF l_debug_level > 0 THEN
1969 oe_debug_pub.add( 'out of calculate_dual_quantity 2 ' ) ;
1970 END IF;
1971 END IF; -- IF OE_BULK_CACHE.G_ITEM_TBL(l_c_index)..tracking_quantity_ind == 'PS' -- INVCONV
1972
1973
1974 END IF; -- IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
1975
1976 END IF;
1977
1978
1979 oe_debug_pub.add( 'Populate_Internal_Fields 7' ) ;
1980 IF l_debug_level > 0 THEN
1981 oe_debug_pub.add( 'out of calculate_dual_quantity 3 ' ) ;
1982 END IF;
1983
1984 -- Checks based on line type
1985 BEGIN
1986
1987 l_c_index := OE_BULK_CACHE.Load_Line_Type
1988 (p_key => p_line_rec.line_type_id(p_index));
1989
1990 l_d_index := OE_BULK_CACHE.Load_Order_Type(OE_Bulk_Order_PVT.g_header_rec.order_type_id(p_header_index));
1991
1992 IF OE_BULK_CACHE.G_LINE_TYPE_TBL(l_c_index).order_category_code
1993 <> 'ORDER'
1994 THEN
1995 FND_MESSAGE.SET_NAME('ONT','OE_BULK_NOT_SUPP_RETURN');
1996 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
1997 x_return_status := FND_API.G_RET_STS_ERROR;
1998 ELSE
1999 p_line_rec.line_category_code(p_index) := 'ORDER';
2000 END IF;
2001
2002 IF l_debug_level > 0 THEN
2003 oe_debug_pub.add( 'tax_calculation_flag = '||
2004 OE_BULK_CACHE.G_LINE_TYPE_TBL(l_c_index).tax_calculation_flag )
2005 ;
2006 oe_debug_pub.add( 'tax_calculation_event = '||
2007 OE_BULK_CACHE.G_ORDER_TYPE_TBL(l_d_index).tax_calculation_event
2008 ) ;
2009 oe_debug_pub.add( 'p_process_tax = '|| p_process_tax ) ;
2010 END IF;
2011
2012
2013 /*--commented for bug 7685103 .We will still import the lines ,tax shall be calculated later from UI
2014
2015 IF OE_BULK_CACHE.G_LINE_TYPE_TBL(l_c_index).tax_calculation_flag = 'Y'
2016 AND ((OE_BULK_CACHE.G_ORDER_TYPE_TBL(l_d_index).tax_calculation_event NOT IN ( 'INVOICING' , 'SHIPPING' ))
2017 OR (OE_BULK_CACHE.G_ORDER_TYPE_TBL(l_d_index).tax_calculation_event IS NULL))
2018 AND NVL(p_process_tax,'N') <> 'Y'
2019 THEN
2020 FND_MESSAGE.SET_NAME('ONT','OE_BULK_NOT_SUPP_TAX_CAL');
2021 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
2022 x_return_status := FND_API.G_RET_STS_ERROR;
2023 END IF;
2024 */
2025
2026 -- Invalid line type - error message populated during validation
2027 EXCEPTION
2028 WHEN NO_DATA_FOUND THEN
2029 -- Set line category for insert to succeed
2030 p_line_rec.line_category_code(p_index) := 'ORDER';
2031 x_return_status := FND_API.G_RET_STS_ERROR;
2032
2033 -- fix bug 5109227
2034 p_line_rec.lock_control(p_index) := -99 ;
2035 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
2036 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2037 OE_Order_UTIL.Get_Attribute_Name('LINE_TYPE_ID'));
2038 OE_BULK_MSG_PUB.Add('Y','ERROR');
2039 -- fix bug 5109227
2040
2041 IF l_debug_level > 0 THEN
2042 oe_debug_pub.add( 'NO_DATA_FOUND in LOAD_LINE_TYPE' ) ;
2043 END IF;
2044
2045 END;
2046
2047 oe_debug_pub.add( 'Populate_Internal_Fields 8' ) ;
2048 -- End of checks based on line type
2049
2050
2051 IF p_line_rec.shipping_method_code(p_index) IS NOT NULL
2052 AND p_line_rec.ship_from_org_id(p_index) IS NOT NULL
2053 THEN
2054 p_line_rec.freight_carrier_code(p_index) :=
2055 OE_BULK_PROCESS_HEADER.Get_Freight_Carrier
2056 (p_shipping_method_code => p_line_rec.shipping_method_code(p_index)
2057 ,p_ship_from_org_id => p_line_rec.ship_from_org_id(p_index)
2058 );
2059 END IF;
2060
2061 IF p_line_rec.pricing_quantity_uom(p_index) IS NOT NULL
2062 AND p_line_rec.pricing_quantity(p_index) IS NOT NULL
2063 THEN
2064 p_line_rec.pricing_quantity(p_index) := OE_Order_Misc_Util.convert_uom(
2065 p_line_rec.inventory_item_id(p_index),
2066 p_line_rec.order_quantity_uom(p_index),
2067 p_line_rec.pricing_quantity_uom(p_index),
2068 p_line_rec.ordered_quantity(p_index));
2069
2070 END IF;
2071
2072 IF p_line_rec.request_date(p_index) IS NOT NULL AND
2073 p_line_rec.latest_acceptable_date(p_index) IS NULL
2074 THEN
2075 p_line_rec.latest_acceptable_date(p_index) :=
2076 p_line_rec.request_date(p_index) +
2077 OE_Bulk_Order_PVT.g_header_rec.latest_schedule_limit(p_header_index);
2078 END IF;
2079
2080 -- Bug 2802876
2081 -- Item type code should not be null even for error records.
2082 -- As further downstream may not be executed for records AFTER
2083 -- the error records e.g. scheduling, WF starts etc.
2084 -- This is because these activities may have loops to start
2085 -- processing when it reaches included item records appended
2086 -- to end of the line tbl e.g. item_type_code <> 'INCLUDED'
2087 -- But for null item types, the loop may end earlier resulting
2088 -- in any record after not being processed.
2089 IF p_line_rec.item_type_code(p_index) IS NULL THEN
2090 p_line_rec.item_type_code(p_index) := 'STANDARD';
2091 END IF;
2092
2093 oe_debug_pub.add( 'Populate_Internal_Fields 9' ) ;
2094 EXCEPTION
2095 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2096 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2097 WHEN OTHERS THEN
2098 IF l_debug_level > 0 THEN
2099 oe_debug_pub.add( 'OTHERS ERROR , LINE.POPULATE_INTERNAL_FIELDS' ) ;
2100 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
2101 END IF;
2102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2103 IF OE_BULK_MSG_PUB.Check_Msg_Level(OE_BULK_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2104 THEN
2105 OE_BULK_MSG_PUB.Add_Exc_Msg
2106 ( G_PKG_NAME
2107 , 'Populate_Internal_Fields'
2108 );
2109 END IF;
2110 END Populate_Internal_Fields;
2111
2112
2113 -----------------------------------------------------------------------
2114 -- PUBLIC PROCEDURES/FUNCTIONS
2115 -----------------------------------------------------------------------
2116 ---------------------------------------------------------------------
2117 -- PROCEDURE Post_Process
2118 --
2119 -- Post_Processing from OEXVIMSB.pls
2120 ---------------------------------------------------------------------
2121 PROCEDURE Post_Process
2122 ( p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
2123 , p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE
2124 , p_line_index IN NUMBER
2125 , p_header_index IN NUMBER
2126 )
2127 IS
2128 l_unit_selling_price NUMBER;
2129 l_payment_term_id NUMBER;
2130 --
2131 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2132 --
2133 BEGIN
2134
2135 -----------------------------------------------------------------
2136 -- Compare price and payment term
2137 -----------------------------------------------------------------
2138
2139 IF p_line_rec.customer_item_net_price(p_line_index) IS NOT NULL
2140 OR p_line_rec.customer_payment_term_id(p_line_index) IS NOT NULL
2141 THEN
2142
2143 -- Select values against DB as pricing calls update values
2144 -- directly on DB.
2145 -- When pricing is BULK enabled, updated values should be
2146 -- on p_line_rec and comparisons should be against the record
2147 -- values then
2148
2149 select unit_selling_price, payment_term_id
2150 into l_unit_selling_price, l_payment_term_id
2151 from oe_order_lines_all
2152 where line_id = p_line_rec.line_id(p_line_index);
2153
2154 IF nvl(p_line_rec.customer_item_net_price(p_line_index)
2155 ,l_unit_selling_price)
2156 <> nvl(l_unit_selling_price,FND_API.G_MISS_NUM)
2157 THEN
2158
2159 FND_MESSAGE.SET_NAME('ONT','OE_OI_PRICE_WARNING');
2160 FND_MESSAGE.SET_TOKEN('CUST_PRICE'
2161 ,p_line_rec.customer_item_net_price(p_line_index));
2162 FND_MESSAGE.SET_TOKEN('SPLR_PRICE',l_unit_selling_price);
2163 OE_BULK_MSG_PUB.Add;
2164
2165 END IF;
2166
2167 IF nvl(p_line_rec.customer_payment_term_id(p_line_index)
2168 ,l_payment_term_id)
2169 <> nvl(l_payment_term_id,FND_API.G_MISS_NUM)
2170 THEN
2171
2172 FND_MESSAGE.SET_NAME('ONT','OE_OI_PAYMENT_TERM_WARNING');
2173 FND_MESSAGE.SET_TOKEN('CUST_TERM'
2174 ,p_line_rec.customer_payment_term_id(p_line_index));
2175 FND_MESSAGE.SET_TOKEN('SPLR_TERM',l_payment_term_id);
2176 OE_BULK_MSG_PUB.Add;
2177
2178 END IF;
2179
2180 END IF;
2181
2182 EXCEPTION
2183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2184 IF l_debug_level > 0 THEN
2185 oe_debug_pub.add( 'UNEXP ERROR , LINE.POST_PROCESS' ) ;
2186 END IF;
2187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2188 WHEN OTHERS THEN
2189 IF l_debug_level > 0 THEN
2190 oe_debug_pub.add( 'OTHERS ERROR , LINE.POST_PROCESS' ) ;
2191 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
2192 END IF;
2193 IF OE_BULK_MSG_PUB.Check_Msg_Level(OE_BULK_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2194 THEN
2195 OE_BULK_MSG_PUB.Add_Exc_Msg
2196 ( G_PKG_NAME
2197 , 'Post_Process'
2198 );
2199 END IF;
2200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2201 END Post_Process;
2202
2203 ---------------------------------------------------------------------
2204 -- PROCEDURE Entity
2205 --
2206 -- Main processing procedure used to process lines in a batch.
2207 -- IN parameters -
2208 -- p_header_rec : order headers in this batch
2209 -- p_line_rec : order lines in this batch
2210 -- p_defaulting_mode : 'Y' if fixed defaulting is needed, 'N' if
2211 -- defaulting is to be completely bypassed
2212 ---------------------------------------------------------------------
2213
2214 PROCEDURE Entity
2215 ( p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
2216 , p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE
2217 , x_line_scredit_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.SCREDIT_REC_TYPE
2218 , p_defaulting_mode IN VARCHAR2
2219 , p_process_configurations IN VARCHAR2 DEFAULT 'N'
2220 , p_validate_configurations IN VARCHAR2 DEFAULT 'Y'
2221 , p_schedule_configurations IN VARCHAR2 DEFAULT 'N'
2222 , p_validate_only IN VARCHAR2 DEFAULT 'N'
2223 , p_validate_desc_flex IN VARCHAR2
2224 , p_process_tax IN VARCHAR2 DEFAULT 'N'
2225 )
2226 IS
2227
2228 l_dummy VARCHAR2(10);
2229 l_count NUMBER;
2230 l_uom VARCHAR2(3);
2231 header_counter binary_integer;
2232 j binary_integer;
2233 l_order_source_id NUMBER := -99; -- Holds info for last errored record
2234 l_orig_sys_document_ref VARCHAR2(50) := '-99'; -- Holds info for last errored record
2235 l_error_count NUMBER := 0;
2236 l_nbr_ctr binary_integer := 1;
2237 l_primary_uom_code VARCHAR2(3);
2238
2239 l_unsupported_feature VARCHAR2(1);
2240 l_return_status VARCHAR2(1);
2241 l_index NUMBER;
2242 l_c_index NUMBER;
2243 l_d_index NUMBER;
2244 l_book_failed BOOLEAN := FALSE;
2245 l_line_count NUMBER := p_line_rec.line_id.COUNT;
2246 l_process_name VARCHAR2(30);
2247
2248 l_on_generic_hold BOOLEAN := FALSE;
2249 l_on_booking_hold BOOLEAN := FALSE;
2250 l_on_scheduling_hold BOOLEAN := FALSE;
2251 l_ii_on_generic_hold BOOLEAN := FALSE;
2252 l_is_ota_line BOOLEAN := FALSE;
2253 l_last_line_index NUMBER;
2254 l_scredit_index NUMBER := 1;
2255 l_ret_status BOOLEAN := TRUE;
2256 l_order_date_type_code VARCHAR2(30);
2257 l_inventory_item_id NUMBER; -- HVOP
2258
2259 -- For AR system parameters
2260 l_AR_Sys_Param_Rec AR_SYSTEM_PARAMETERS_ALL%ROWTYPE;
2261 l_sob_id NUMBER;
2262
2263 -- eBTax Changes
2264 l_ship_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
2265 l_ship_to_party_id hz_cust_accounts.party_id%type;
2266 l_ship_to_party_site_id hz_party_sites.party_site_id%type;
2267 l_bill_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
2268 l_bill_to_party_id hz_cust_accounts.party_id%type;
2269 l_bill_to_party_site_id hz_party_sites.party_site_id%type;
2270 l_org_id NUMBER;
2271
2272 --PP Revenue Recognition
2273 --ER 4893057
2274 l_rule_type VARCHAR2(10);
2275 l_line_rec_for_hold OE_Order_PUB.Line_Rec_Type; --ER#7479609
2276 l_header_rec_for_hold OE_Order_PUB.Header_Rec_Type; --ER#7479609
2277
2278 cursor partyinfo(p_site_org_id HZ_CUST_SITE_USES_ALL.SITE_USE_ID%type) is
2279 SELECT cust_acct.cust_account_id,
2280 cust_Acct.party_id,
2281 acct_site.party_site_id,
2282 site_use.org_id
2283 FROM
2284 HZ_CUST_SITE_USES_ALL site_use,
2285 HZ_CUST_ACCT_SITES_ALL acct_site,
2286 HZ_CUST_ACCOUNTS_ALL cust_Acct
2287 WHERE site_use.site_use_id = p_site_org_id
2288 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
2289 and acct_site.cust_account_id = cust_acct.cust_account_id;
2290
2291
2292 --
2293 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2294 --
2295 BEGIN
2296
2297 IF l_debug_level > 0 THEN
2298 oe_debug_pub.add( 'ENTERING THE ENTITY VALIDATION' ) ;
2299 END IF;
2300 header_counter := 1;
2301
2302 IF l_debug_level > 0 THEN
2303 oe_debug_pub.add( 'THE LINE COUNT IS '||P_LINE_REC.LINE_ID.COUNT ) ;
2304 END IF;
2305 FOR l_index IN 1..l_line_count LOOP
2306
2307 -- Set the message context for errors.
2308 oe_bulk_msg_pub.set_msg_context
2309 ( p_entity_code => 'LINE'
2310 ,p_entity_id => p_line_rec.line_id(l_index)
2311 ,p_header_id => p_line_rec.header_id(l_index)
2312 ,p_line_id => p_line_rec.line_id(l_index)
2313 ,p_orig_sys_document_ref => p_line_rec.orig_sys_document_ref(l_index)
2314 ,p_orig_sys_document_line_ref => p_line_rec.orig_sys_line_ref(l_index)
2315 ,p_source_document_id => NULL
2316 ,p_source_document_line_id => NULL
2317 ,p_order_source_id => p_line_rec.order_source_id(l_index)
2318 ,p_source_document_type_id => NULL );
2319
2320 IF (p_header_rec.order_source_id(header_counter) <>
2321 p_line_rec.order_source_id(l_index) )
2322 OR (p_header_rec.orig_sys_document_ref(header_counter) <>
2323 p_line_rec.orig_sys_document_ref(l_index) )
2324 THEN
2325
2326 IF l_debug_level > 0 THEN
2327 oe_debug_pub.add( 'HEADER COUNTER :'||HEADER_COUNTER ) ;
2328 oe_debug_pub.add( 'LINE ORDER SOURCE:'||P_LINE_REC.ORDER_SOURCE_ID ( L_INDEX ) ) ;
2329 oe_debug_pub.add( 'LINE OSR :'||P_LINE_REC.ORIG_SYS_DOCUMENT_REF ( L_INDEX ) ) ;
2330 END IF;
2331 IF l_book_failed THEN
2332 Unbook_Order(p_header_index => header_counter
2333 ,p_last_line_index => l_last_line_index
2334 ,p_line_rec => p_line_rec
2335 );
2336 l_book_failed := FALSE;
2337 END IF;
2338
2339 j := header_counter;
2340
2341 WHILE j <= p_header_rec.header_id.count
2342 LOOP
2343 IF (p_header_rec.order_source_id(j) =
2344 p_line_rec.order_source_id(l_index) )
2345 AND (p_header_rec.orig_sys_document_ref(j) =
2346 p_line_rec.orig_sys_document_ref(l_index) )
2347 THEN
2348 EXIT;
2349 END IF;
2350 j := j+1;
2351 END LOOP;
2352
2353 header_counter := j;
2354 l_nbr_ctr := 1; -- Reset the line number counter
2355
2356 -- added for HVOP Tax project
2357 -- setting start and end line index for new order bug7685103
2358 OE_BULK_ORDER_PVT.G_HEADER_REC.start_line_index(header_counter) := l_index;
2359 OE_BULK_ORDER_PVT.G_HEADER_REC.end_line_index(header_counter) := l_index;
2360
2361 oe_debug_pub.add('OE_BULK_ORDER_PVT.G_HEADER_REC.start_line_index(header_counter):'||OE_BULK_ORDER_PVT.G_HEADER_REC.start_line_index(header_counter));
2362 oe_debug_pub.add('OE_BULK_ORDER_PVT.G_HEADER_REC.end_line_index(header_counter):'||OE_BULK_ORDER_PVT.G_HEADER_REC.end_line_index(header_counter));
2363
2364
2365 --------------------------------------------------------------
2366 -- Same order: check for duplicate reference, from OEXVIMPB.pls
2367 --------------------------------------------------------------
2368 ELSE
2369
2370 IF l_last_line_index IS NOT NULL THEN
2371
2372 IF OE_Bulk_Order_PVT.G_IMPORT_SHIPMENTS = 'NO' THEN
2373 IF (p_line_rec.orig_sys_line_ref(l_last_line_index)
2374 = p_line_rec.orig_sys_line_ref(l_index))
2375 THEN
2376 FND_MESSAGE.SET_NAME('ONT','OE_OI_DUPLICATE_REF');
2377 FND_MESSAGE.SET_TOKEN('DUPLICATE_REF'
2378 ,'orig_sys_line_ref');
2379 p_line_rec.lock_control(l_index) := -99;
2380 OE_BULK_MSG_PUB.Add;
2381 END IF;
2382 ELSIF OE_Bulk_Order_PVT.G_IMPORT_SHIPMENTS = 'YES' THEN
2383 IF (p_line_rec.orig_sys_line_ref(l_last_line_index)
2384 = p_line_rec.orig_sys_line_ref(l_index))
2385 AND (p_line_rec.orig_sys_shipment_ref(l_last_line_index)
2386 = p_line_rec.orig_sys_shipment_ref(l_index))
2387 THEN
2388 FND_MESSAGE.SET_NAME('ONT','OE_OI_DUPLICATE_REF');
2389 FND_MESSAGE.SET_TOKEN('DUPLICATE_REF'
2390 ,'orig_sys_line_ref and orig_sys_shipment_ref');
2391 p_line_rec.lock_control(l_index) := -99;
2392 OE_BULK_MSG_PUB.Add;
2393 END IF;
2394 END IF;
2395
2396 END IF;
2397
2398 END IF;
2399
2400 -- added for HVOP Tax project
2401 -- setting end line index for this order bug7685103
2402 OE_BULK_ORDER_PVT.G_HEADER_REC.end_line_index(header_counter) := l_index;
2403
2404
2405 p_line_rec.line_number(l_index) := l_nbr_ctr;
2406
2407 --PIB{
2408 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
2409 IF l_debug_level > 0 Then
2410 oe_debug_pub.add('before integration code');
2411 END IF;
2412 -- p_line_rec.line_index.extend(1);
2413 -- p_line_rec.header_index.extend(1);
2414 -- p_line_rec.currency_code.extend(1);
2415 p_line_rec.line_index(l_index) := l_index;
2416 p_line_rec.header_index(l_index) := header_counter;
2417 -- p_line_rec.currency_code(l_index) := p_header_rec.transactional_curr_code(header_counter);
2418 IF l_debug_level > 0 Then
2419 oe_debug_pub.add('after integration code');
2420 END IF;
2421 END IF;
2422 --PIB}
2423
2424 ---------------------------------------------------------
2425 -- CALL THE FIXED DEFAULTING PROCEDURE IF NEEDED
2426 ---------------------------------------------------------
2427
2428 IF p_defaulting_mode = 'Y' THEN
2429
2430 Default_Record
2431 ( p_line_rec => p_line_rec
2432 ,p_index => l_index
2433 ,p_header_index => header_counter
2434 ,x_return_status => l_return_status
2435 );
2436
2437 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2438 p_line_rec.lock_control(l_index) := -99;
2439 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2441 END IF;
2442
2443 END IF;
2444
2445 ---------------------------------------------------------
2446 -- POPULATE INTERNAL FIELDS
2447 -- Hardcoded Defaulting From OEXDLINB.pls
2448 ---------------------------------------------------------
2449
2450 IF l_debug_level > 0 THEN
2451 oe_debug_pub.add( 'PARENT LINE INDEX :'||L_INDEX ) ;
2452 END IF;
2453 Populate_Internal_Fields
2454 ( p_line_rec => p_line_rec
2455 ,p_index => l_index
2456 ,p_header_index => header_counter
2457 ,p_process_tax => p_process_tax
2458 ,p_process_configurations => p_process_configurations
2459 ,x_unsupported_feature => l_unsupported_feature
2460 ,x_return_status => l_return_status
2461 );
2462
2463 oe_debug_pub.add('after Populate_Internal_Fields');
2464 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2465 p_line_rec.lock_control(l_index) := -99;
2466 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2467 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2468 END IF;
2469
2470 -- Populating calculate price flag
2471
2472 --PIB
2473 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
2474 IF l_debug_level > 0 Then
2475 oe_debug_pub.add('before set_price_flag');
2476 END IF;
2477 oe_bulk_priceorder_pvt.set_price_flag(p_line_rec,l_index,header_counter);
2478 IF l_debug_level > 0 Then
2479 oe_debug_pub.add('after set_price_flag');
2480 END IF;
2481 END IF;
2482 --PIB
2483
2484 ---------------------------------------------------------
2485 -- START ENTITY VALIDATIONS
2486 ---------------------------------------------------------
2487
2488 -- Validate Required Attributes
2489
2490 IF (p_line_rec.inventory_item_id(l_index) IS NULL) THEN
2491
2492 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
2493 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2494 OE_Order_UTIL.Get_Attribute_Name('INVENTORY_ITEM_ID'));
2495 OE_BULK_MSG_PUB.Add('Y','ERROR');
2496 p_line_rec.lock_control(l_index) := -99;
2497
2498 -- To avoid Insert failure, populate not null column.
2499 -- This record will be deleted later.
2500 p_line_rec.inventory_item_id(l_index) := -99;
2501 IF l_debug_level > 0 THEN
2502 oe_debug_pub.add( 'IN ERROR 1 ' ) ;
2503 END IF;
2504
2505 END IF;
2506 oe_debug_pub.add('after inventory_item_id');
2507
2508 IF (p_line_rec.line_type_id(l_index) IS NULL) THEN
2509
2510 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
2511 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2512 OE_Order_UTIL.Get_Attribute_Name('LINE_TYPE_ID'));
2513 OE_BULK_MSG_PUB.Add('Y','ERROR');
2514 p_line_rec.lock_control(l_index) := -99;
2515 IF l_debug_level > 0 THEN
2516 oe_debug_pub.add( 'IN ERROR 2 ' ) ;
2517 END IF;
2518
2519 -- To avoid Insert failure, populate not null column.
2520 -- This record will be deleted later.
2521
2522 p_line_rec.line_type_id(l_index) := -99;
2523 IF l_debug_level > 0 THEN
2524 oe_debug_pub.add( 'IN ERROR 3 ' ) ;
2525 END IF;
2526
2527
2528 ELSE -- line_type_id is not null
2529
2530 -- Validate line type for effective dates
2531 IF NOT Validate_Line_Type(p_line_rec.line_type_id(l_index),
2532 p_header_rec.ordered_date(header_counter))
2533 THEN
2534
2535 p_line_rec.lock_control(l_index) := -99 ;
2536 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
2537 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2538 OE_Order_UTIL.Get_Attribute_Name('LINE_TYPE_ID'));
2539 OE_BULK_MSG_PUB.Add('Y','ERROR');
2540
2541 ELSE
2542
2543 -- Validate that Order/Line Type has valid WF assignment
2544 -- Bug 2650317 - Do not validate WF assignment if item type,
2545 -- UOM or order type is null else function may return an
2546 -- unexpected error.
2547 IF p_line_rec.item_type_code(l_index) IS NOT NULL
2548 AND p_line_rec.order_quantity_uom(l_index) IS NOT NULL
2549 AND p_header_rec.order_type_id(header_counter) IS NOT NULL
2550 AND NOT OE_BULK_WF_UTIL.Validate_LT_WF_Assignment(
2551 p_header_rec.order_type_id(header_counter)
2552 ,l_index
2553 ,p_line_rec
2554 ,l_process_name)
2555 THEN
2556 IF l_debug_level > 0 THEN
2557 oe_debug_pub.add( 'FAILURE IN OE_BULK_WF_UTIL.Validate_LT_WF_Assignment ' ) ;
2558 END IF;
2559 p_line_rec.lock_control(l_index) := -99 ;
2560 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
2561 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2562 OE_Order_UTIL.Get_Attribute_Name('LINE_TYPE_ID'));
2563 OE_BULK_MSG_PUB.Add('Y','ERROR');
2564 ELSE
2565 p_line_rec.wf_process_name(l_index) := l_process_name;
2566 END IF;
2567
2568 END IF;
2569
2570 END IF;
2571
2572
2573 oe_debug_pub.add('before checking for tax related attributes');
2574 oe_debug_pub.add(' Process Tax :' || p_process_tax );
2575 -- Check for Tax related attributes
2576
2577 IF (p_line_rec.tax_exempt_flag(l_index) = 'E') THEN
2578
2579 -- Tax exempt reason code is required
2580 IF (p_line_rec.tax_exempt_reason_code(l_index) IS NULL) THEN
2581 p_line_rec.lock_control(l_index) := -99;
2582 fnd_message.set_name('ONT','OE_ATTRIBUTE_REQUIRED');
2583 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Tax Exempt Reason');
2584 OE_BULK_MSG_PUB.Add('Y','ERROR');
2585
2586 ELSIF NOT OE_BULK_PROCESS_HEADER.Valid_Tax_Exempt_Reason
2587 (p_line_rec.tax_exempt_reason_code(l_index)) THEN
2588
2589 p_line_rec.lock_control(l_index) := -99;
2590 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
2591 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2592 OE_Order_UTIL.Get_Attribute_Name('TAX_EXEMPT_REASON_CODE'));
2593 OE_BULK_MSG_PUB.Add('Y','ERROR');
2594
2595 END IF;
2596
2597 END IF;
2598
2599 IF (p_line_rec.tax_exempt_flag(l_index) = 'R') THEN
2600
2601 IF (p_line_rec.tax_exempt_number(l_index) IS NOT NULL)
2602 OR
2603 (p_line_rec.tax_exempt_reason_code(l_index) IS NOT NULL) THEN
2604 p_line_rec.lock_control(l_index) := -99;
2605 IF l_debug_level > 0 THEN
2606 oe_debug_pub.add( 'IN ERROR 6 ' ) ;
2607 END IF;
2608 fnd_message.set_name('ONT','OE_TAX_EXEMPTION_NOT_ALLOWED');
2609 OE_BULK_MSG_PUB.Add('Y','ERROR');
2610 END IF;
2611
2612 END IF;
2613
2614 oe_debug_pub.add('tax related attributes checking completed;');
2615 -- added for HVOP Tax project
2616 IF p_process_tax = 'Y' THEN
2617 OE_Bulk_Process_Line.Load_Cust_Trx_Type_Id(p_line_index => l_index,
2618 p_line_rec => p_line_rec,
2619 p_header_index => header_counter,
2620 p_header_rec => p_header_rec);
2621 END IF;
2622 -- Subinventory Validation
2623 IF p_line_rec.subinventory(l_index) IS NOT NULL THEN
2624 -- Error messages in Validate_Subinventory
2625 IF NOT Validate_Subinventory
2626 (p_line_rec.subinventory(l_index)
2627 ,p_line_rec.inventory_item_id(l_index)
2628 ,p_line_rec.ship_from_org_id(l_index)
2629 ,p_line_rec.source_type_code(l_index)
2630 ,p_line_rec.order_source_id(l_index)
2631 )
2632 THEN
2633 p_line_rec.lock_control(l_index) := -99;
2634 IF l_debug_level > 0 THEN
2635 oe_debug_pub.add( 'SUBINV VALIDATION FAILED' ) ;
2636 END IF;
2637 END IF;
2638 END IF;
2639
2640
2641 -- Item-Warehouse Validation
2642 IF nvl(p_line_rec.inventory_item_id(l_index),-99) <> -99 AND
2643 p_line_rec.ship_from_org_id(l_index) IS NOT NULL
2644 THEN
2645
2646 IF NOT Validate_Item_Warehouse
2647 (p_line_rec.inventory_item_id(l_index),
2648 p_line_rec.ship_from_org_id(l_index),
2649 p_line_rec.item_type_code(l_index),
2650 p_line_rec.line_id(l_index),
2651 p_line_rec.top_model_line_id(l_index),
2652 NULL ,--p_line_rec.source_document_type_id(l_index),
2653 'ORDER')
2654 THEN
2655 p_line_rec.lock_control(l_index) := -99;
2656 IF l_debug_level > 0 THEN
2657 oe_debug_pub.add( 'IN ERROR 7 ' ) ;
2658 END IF;
2659 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ITEM_WHSE');
2660 OE_BULK_MSG_PUB.add('Y','ERROR');
2661 END IF;
2662
2663 END IF;
2664
2665 -- Shipping Method-Warehouse validation
2666 IF (p_line_rec.line_category_code(l_index) <> 'RETURN' AND
2667 p_line_rec.shipping_method_code(l_index) IS NOT NULL AND
2668 p_line_rec.ship_from_org_id(l_index) IS NOT NULL) THEN
2669
2670 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110509' THEN
2671 SELECT count(*)
2672 INTO l_count
2673 FROM wsh_carrier_services wsh,
2674 wsh_org_carrier_services wsh_org
2675 WHERE wsh_org.organization_id = p_line_rec.ship_from_org_id(l_index)
2676 AND wsh.carrier_service_id = wsh_org.carrier_service_id
2677 AND wsh.ship_method_code = p_line_rec.shipping_method_code(l_index)
2678 AND wsh_org.enabled_flag = 'Y';
2679 ELSE
2680
2681 SELECT count(*)
2682 INTO l_count
2683 FROM wsh_carrier_ship_methods
2684 WHERE ship_method_code = p_line_rec.shipping_method_code(l_index)
2685 AND organization_id = p_line_rec.ship_from_org_id(l_index);
2686 END IF;
2687 -- Valid Shipping Method Code.
2688
2689 IF l_count = 0 THEN
2690 p_line_rec.shipping_method_code(l_index) := NULL;
2691
2692 OE_BULK_MSG_PUB.Update_Msg_Context(p_attribute_code => 'SHIPPING_METHOD');
2693 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
2694 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2695 OE_Order_UTIL.Get_Attribute_Name('SHIPPING_METHOD_CODE'));
2696 OE_BULK_MSG_PUB.Add;
2697 OE_BULK_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
2698 END IF;
2699
2700 END IF;
2701
2702 /* -- checks: Warehouse/Process combinations process HVOP
2703 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
2704 IF OE_Bulk_Order_PVT.G_PROCESS_INSTALLED_FLAG = 'Y' THEN
2705
2706 -- first check if warehouse is NULL, do not supply process attributes INVCONV - NOT NEEDED NOW
2707
2708 IF (p_line_rec.ship_from_org_id(l_index)IS NULL) THEN
2709
2710 IF (p_line_rec.ordered_quantity_uom2(l_index)IS NOT NULL
2711 -- AND p_line_rec.context(l_index) = FND_API.G_MISS_CHAR
2712 )
2713 OR
2714 (p_line_rec.ordered_quantity2(l_index) IS NOT NULL)
2715 OR
2716 (p_line_rec.preferred_grade (l_index) IS NOT NULL) THEN
2717
2718 p_line_rec.lock_control(l_index) := -99;
2719 IF l_debug_level > 0 THEN
2720 oe_debug_pub.add( 'error 1 ' ) ;
2721 END IF;
2722 fnd_message.set_name('ONT','OE_BULK_OPM_NOT_PROCESS');
2723 OE_BULK_MSG_PUB.Add('Y','ERROR');
2724 END IF;
2725
2726 END IF; */
2727
2728
2729
2730 -- second process check if cached opm_item_id is NULL OR warehouse is discrete , do not supply process attributes
2731 /* IF p_line_rec.inventory_item_id(l_index) IS NOT NULL THEN
2732
2733 l_inventory_item_id := p_line_rec.inventory_item_id(l_index);
2734 l_c_index := OE_BULK_CACHE.Load_Item
2735 (p_key1 => l_inventory_item_id
2736 ,p_key2 => p_line_rec.ship_from_org_id(l_index)
2737 );
2738 IF ( OE_BULK_CACHE.G_ITEM_TBL(l_c_index).opm_item_id IS NULL
2739 OR OE_BULK_CACHE.G_ITEM_TBL(l_c_index).process_warehouse_flag <> 'Y' )
2740 THEN
2741 IF (p_line_rec.ordered_quantity_uom2(l_index) IS NOT NULL)
2742 OR
2743 (p_line_rec.ordered_quantity2(l_index) IS NOT NULL)
2744 OR
2745 (p_line_rec.preferred_grade (l_index) IS NOT NULL) THEN
2746
2747 p_line_rec.lock_control(l_index) := -99;
2748 IF l_debug_level > 0 THEN
2749 oe_debug_pub.add( 'error 2 ' ) ;
2750 END IF;
2751 fnd_message.set_name('ONT','OE_BULK_OPM_NOT_PROCESS');
2752 OE_BULK_MSG_PUB.Add('Y','ERROR');
2753 END IF;
2754 END IF; -- OE_BULK_CACHE.G_ITEM_TBL(l_c_index).opm_item_id IS NULL THEN
2755
2756 END IF; -- p_line_rec.inventory_item_id(p_index) IS NOT NULL THEN
2757
2758 END IF; -- IF OE_Bulk_Order_PVT.G_PROCESS_INSTALLED_FLAG = 'Y' THEN
2759
2760 END IF; -- IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN */
2761
2762
2763
2764
2765
2766
2767 -- Start decimal qty validation
2768 IF nvl(p_line_rec.inventory_item_id(l_index),-99) <> -99 THEN
2769
2770 IF p_line_rec.order_quantity_uom(l_index) is not null THEN
2771
2772 -- validate ordered quantity
2773 Validate_Decimal_Quantity
2774 (p_item_id => p_line_rec.inventory_item_id(l_index)
2775 ,p_item_type_code => p_line_rec.item_type_code(l_index)
2776 ,p_input_quantity => p_line_rec.ordered_quantity(l_index)
2777 ,p_uom_code => p_line_rec.order_quantity_uom(l_index)
2778 ,x_return_status => p_line_rec.lock_control(l_index)
2779 );
2780
2781 -- Validate UOM
2782 IF ( p_line_rec.item_type_code(l_index) = OE_GLOBALS.G_ITEM_MODEL OR
2783 p_line_rec.item_type_code(l_index) = OE_GLOBALS.G_ITEM_CLASS OR
2784 p_line_rec.item_type_code(l_index) = OE_GLOBALS.G_ITEM_OPTION OR
2785 p_line_rec.item_type_code(l_index) = OE_GLOBALS.G_ITEM_KIT OR
2786 p_line_rec.item_type_code(l_index) = OE_GLOBALS.G_ITEM_INCLUDED OR
2787 p_line_rec.item_type_code(l_index) = OE_GLOBALS.G_ITEM_CONFIG)
2788 THEN
2789
2790 BEGIN
2791 SELECT primary_uom_code
2792 INTO l_uom
2793 FROM mtl_system_items
2794 WHERE inventory_item_id=p_line_rec.inventory_item_id(l_index)
2795 AND organization_id=nvl(p_line_rec.ship_from_org_id(l_index),
2796 OE_Bulk_Order_PVT.G_ITEM_ORG);
2797
2798 IF l_uom <> p_line_rec.order_quantity_uom(l_index) THEN
2799 IF l_debug_level > 0 THEN
2800 oe_debug_pub.add( 'UOM OTHER THAN PRIMARY UOM IS ENTERED' , 1 ) ;
2801 END IF;
2802
2803 fnd_message.set_name('ONT','OE_INVALID_ORDER_QUANTITY_UOM');
2804 fnd_message.set_token('ITEM',p_line_rec.ordered_item(l_index) );
2805 fnd_message.set_token('UOM', l_uom);
2806 OE_BULK_MSG_PUB.Add('Y','ERROR');
2807 --RAISE FND_API.G_EXC_ERROR;
2808 END IF;
2809 EXCEPTION
2810 when no_data_found then
2811 IF l_debug_level > 0 THEN
2812 oe_debug_pub.add( 'OEXLLINB , NO_DATA_FOUND IN UOM VALIDATION' , 1 ) ;
2813 END IF;
2814 p_line_rec.lock_control(l_index) := -99;
2815 fnd_message.set_name('ONT','OE_INVALID_ORDER_QUANTITY_UOM');
2816 fnd_message.set_token('ITEM',p_line_rec.ordered_item(l_index) );
2817 fnd_message.set_token('UOM', l_uom);
2818 OE_BULK_MSG_PUB.Add('Y','ERROR');
2819 END;
2820
2821 ELSE
2822
2823 -- Bug 1544265
2824 -- For other item types, validate uom using inv_convert api
2825 l_ret_status := inv_convert.validate_item_uom
2826 (p_line_rec.order_quantity_uom(l_index)
2827 ,p_line_rec.inventory_item_id(l_index)
2828 ,nvl(p_line_rec.ship_from_org_id(l_index)
2829 ,OE_Bulk_Order_PVT.G_ITEM_ORG )
2830 );
2831 IF NOT l_ret_status THEN
2832 IF l_debug_level > 0 THEN
2833 oe_debug_pub.add( 'UOM/ITEM COMBINATION INVALID' , 2 ) ;
2834 oe_debug_pub.add( 'UOM :'||P_LINE_REC.ORDER_QUANTITY_UOM ( L_INDEX ) ) ;
2835 oe_debug_pub.add( 'ITEM ID :'||P_LINE_REC.INVENTORY_ITEM_ID ( L_INDEX ) ) ;
2836 END IF;
2837 p_line_rec.lock_control(l_index) := -99;
2838 fnd_message.set_name('ONT', 'OE_INVALID_ITEM_UOM');
2839 OE_BULK_MSG_PUB.Add('Y','ERROR');
2840 END IF;
2841
2842 END IF; -- uom validation based on item type
2843
2844 END IF; -- order quantity uom not null
2845
2846 END IF; -- inventory_item_id is null
2847
2848
2849 -- Validate if the source_type, item combination is valid
2850 -- Validate if the source_type, ship_set_id, arrival_set_id is valid
2851 -- Not needed as BULK does not support externally sourced items
2852 -- or lines in sets
2853
2854
2855 -- PJM validation.
2856
2857 IF PJM_UNIT_EFF.ENABLED = 'Y' THEN
2858
2859 IF (p_line_rec.project_id(l_index) IS NOT NULL AND
2860 p_line_rec.ship_from_org_id(l_index) IS NULL)
2861 THEN
2862 FND_MESSAGE.SET_NAME('ONT', 'OE_SHIP_FROM_REQD');
2863 OE_BULK_MSG_PUB.Add('Y','ERROR');
2864 p_line_rec.lock_control(l_index) := -99;
2865 IF l_debug_level > 0 THEN
2866 oe_debug_pub.add( 'IN ERROR 11 ' ) ;
2867 END IF;
2868 END IF;
2869
2870 IF (p_line_rec.project_id(l_index) IS NOT NULL AND
2871 p_line_rec.ship_from_org_id(l_index) IS NOT NULL)
2872 THEN
2873 -- Validate project/warehouse combination.
2874 IF pjm_project.val_proj_idtonum(p_line_rec.project_id(l_index),
2875 p_line_rec.ship_from_org_id(l_index)) IS NULL
2876 THEN
2877 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SHIP_FROM_PROJ');
2878 OE_BULK_MSG_PUB.Add('Y','ERROR');
2879 p_line_rec.lock_control(l_index) := -99;
2880 IF l_debug_level > 0 THEN
2881 oe_debug_pub.add( 'IN ERROR 12 ' ) ;
2882 END IF;
2883 END IF;
2884 END IF;
2885
2886 IF (p_line_rec.task_id(l_index) IS NOT NULL
2887 AND p_line_rec.project_id(l_index) IS NULL) THEN
2888 FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_PROJECT_REQD');
2889 OE_BULK_MSG_PUB.Add('Y','ERROR');
2890 p_line_rec.lock_control(l_index) := -99;
2891 IF l_debug_level > 0 THEN
2892 oe_debug_pub.add( 'IN ERROR 13 ' ) ;
2893 END IF;
2894
2895 ELSIF (p_line_rec.task_id(l_index) is NOT NULL
2896 AND p_line_rec.project_id(l_index) IS NOT NULL) THEN
2897
2898 IF NOT Validate_task(p_line_rec.project_id(l_index),
2899 p_line_rec.task_id(l_index)) THEN
2900 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
2901 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
2902 OE_Order_Util.Get_Attribute_Name('task_id'));
2903 OE_BULK_MSG_PUB.Add('Y','ERROR');
2904 p_line_rec.lock_control(l_index) := -99;
2905 IF l_debug_level > 0 THEN
2906 oe_debug_pub.add( 'IN ERROR 14 ' ) ;
2907 END IF;
2908
2909 END IF;
2910
2911 ELSIF (p_line_rec.task_id(l_index) is NULL
2912 AND p_line_rec.project_id(l_index) IS NOT NULL) THEN
2913
2914 IF Validate_task_reqd(p_line_rec.project_id(l_index),
2915 p_line_rec.ship_from_org_id(l_index)) THEN
2916 FND_MESSAGE.SET_NAME('ONT', 'OE_VAL_TASK_REQD');
2917 OE_BULK_MSG_PUB.Add('Y','ERROR');
2918 p_line_rec.lock_control(l_index) := -99;
2919 IF l_debug_level > 0 THEN
2920 oe_debug_pub.add( 'IN ERROR 15 ' ) ;
2921 END IF;
2922 END IF;
2923 END IF;
2924
2925 IF nvl(p_line_rec.inventory_item_id(l_index),-99) <> -99 AND
2926 (p_line_rec.ship_from_org_id(l_index) IS NOT NULL) AND
2927 (p_line_rec.end_item_unit_number(l_index) IS NULL) THEN
2928
2929 IF PJM_UNIT_EFF.UNIT_EFFECTIVE_ITEM
2930 (p_line_rec.inventory_item_id(l_index),
2931 p_line_rec.ship_from_org_id(l_index)) = 'Y'
2932 THEN
2933 fnd_message.set_name('ONT', 'OE_UEFF_NUMBER_REQD');
2934 OE_BULK_MSG_PUB.Add('Y','ERROR');
2935 p_line_rec.lock_control(l_index) := -99;
2936 IF l_debug_level > 0 THEN
2937 oe_debug_pub.add( 'IN ERROR 16 ' ) ;
2938 END IF;
2939 END IF;
2940 END IF;
2941 ELSE -- When project manufacturing is not enabled at the site.
2942
2943 IF (p_line_rec.project_id(l_index) IS NOT NULL OR
2944 p_line_rec.task_id(l_index) IS NOT NULL OR
2945 p_line_rec.end_item_unit_number(l_index) IS NOT NULL)
2946 THEN
2947 fnd_message.set_name('ONT', 'OE_PJM_NOT_INSTALLED');
2948 OE_BULK_MSG_PUB.Add('Y','ERROR');
2949 p_line_rec.lock_control(l_index) := -99;
2950 IF l_debug_level > 0 THEN
2951 oe_debug_pub.add( 'IN ERROR 17 ' ) ;
2952 END IF;
2953 END IF;
2954
2955 END IF; --End if PJM_UNIT_EFF.ENABLED
2956
2957
2958 -- Validate if item, item_identifier_type, inventory_item combination
2959 -- is valid
2960
2961 IF p_line_rec.inventory_item_id(l_index) IS NOT NULL THEN
2962
2963 IF NOT Validate_Item_Fields
2964 ( p_line_rec.inventory_item_id(l_index)
2965 , p_line_rec.ordered_item_id(l_index)
2966 , p_line_rec.item_identifier_type(l_index)
2967 , p_line_rec.ordered_item(l_index)
2968 , p_line_rec.sold_to_org_id(l_index)
2969 , 'ORDER'
2970 , p_line_rec.item_type_code(l_index)
2971 , p_line_rec.line_id(l_index)
2972 , NULL --p_line_rec.top_model_line_id(l_index)
2973 , NULL --p_line_rec.source_document_type_id(l_index)
2974 )
2975 THEN
2976 p_line_rec.lock_control(l_index) := -99;
2977 fnd_message.set_name('ONT', 'OE_ITEM_VALIDATION_FAILED');
2978 OE_BULK_MSG_PUB.Add('Y','ERROR');
2979 IF l_debug_level > 0 THEN
2980 oe_debug_pub.add( 'IN ERROR 18 ' ) ;
2981 END IF;
2982 END IF;
2983
2984 --Item Orderability
2985 --Validate Item Orderability Rules
2986 IF ( NVL( p_line_rec.item_type_code(l_index),OE_GLOBALS.G_ITEM_STANDARD) = OE_GLOBALS.G_ITEM_STANDARD
2987 OR ( p_line_rec.item_type_code(l_index) = OE_GLOBALS.G_ITEM_MODEL )
2988 ) then
2989
2990 oe_debug_pub.add(' Checking Validate_item_orderability ');
2991 --oe_debug_pub.add(' org id :' || p_line_rec.org_id(l_index));
2992
2993
2994 IF NOT OE_ITORD_UTIL.Validate_item_orderability
2995 (
2996 p_line_rec.org_id(l_index)
2997 , p_line_rec.line_id(l_index)
2998 , p_line_rec.header_id(l_index)
2999 , p_line_rec.inventory_item_id(l_index)
3000 , p_line_rec.sold_to_org_id(l_index)
3001 , p_line_rec.ship_to_org_id(l_index)
3002 , p_line_rec.salesrep_id(l_index)
3003 , p_line_rec.end_customer_id(l_index)
3004 , p_line_rec.invoice_to_org_id(l_index)
3005 , p_line_rec.deliver_to_org_id(l_index)
3006 )
3007 THEN
3008 p_line_rec.lock_control(l_index) := -99;
3009 fnd_message.set_name('ONT', 'OE_ITORD_VALIDATION_FAILED');
3010 fnd_message.set_token('ITEM',OE_ITORD_UTIL.get_item_name(p_line_rec.inventory_item_id(l_index)));
3011 fnd_message.set_token('CATEGORY',OE_ITORD_UTIL.get_item_category_name(p_line_rec.inventory_item_id(l_index)));
3012 OE_BULK_MSG_PUB.Add('Y','ERROR');
3013 IF l_debug_level > 0 THEN
3014 oe_debug_pub.add( 'IN ERROR 19 ' ) ;
3015 END IF;
3016 END IF;
3017 END IF;
3018
3019 END IF;
3020
3021 -- User Item Description related validation,
3022 -- to make sure length not exceed 240 characters for
3023 -- EXTERNAL orders.
3024 IF p_line_rec.user_item_description(l_index) IS NOT NULL
3025 AND p_line_rec.source_type_code(l_index) = 'EXTERNAL'
3026 THEN
3027
3028 IF NOT Validate_User_Item_Description
3029 ( p_line_rec.user_item_description(l_index))
3030 THEN
3031 p_line_rec.lock_control(l_index) := -99;
3032 fnd_message.set_name('ONT', 'ONT_USER_ITEM_DESC_TOO_LONG');
3033 OE_BULK_MSG_PUB.Add('Y','ERROR');
3034 IF l_debug_level > 0 THEN
3035 oe_debug_pub.add( 'LENGTH OF USER ITEM DESC EXCEEDS LIMIT FOR EXTERNAL ORDERS. ' ) ;
3036 END IF;
3037 END IF;
3038
3039 END IF;
3040
3041
3042 -- Agreement related validation
3043 IF p_line_rec.agreement_id(l_index) IS NOT NULL
3044 AND (NOT OE_GLOBALS.Equal(p_line_rec.agreement_id(l_index),
3045 p_header_rec.agreement_id(header_counter))
3046 OR NOT OE_GLOBALS.Equal(p_line_rec.pricing_date(l_index),
3047 p_header_rec.pricing_date(header_counter))
3048 OR NOT OE_GLOBALS.Equal(p_line_rec.price_list_id(l_index),
3049 p_header_rec.price_list_id(header_counter))
3050 )
3051 THEN
3052
3053 -- Error messages are populated in Validate_Agreement
3054
3055 IF NOT OE_BULK_PROCESS_HEADER.Validate_Agreement
3056 (p_line_rec.agreement_id(l_index)
3057 ,p_line_rec.pricing_date(l_index)
3058 ,p_line_rec.price_list_id(l_index)
3059 ,p_line_rec.sold_to_org_id(l_index)
3060 )
3061 THEN
3062
3063 p_line_rec.lock_control(l_index) := -99;
3064
3065 END IF;
3066
3067 END IF; -- If Agreement is NOT NULL
3068
3069 -- Price List related validations
3070
3071 IF p_line_rec.price_list_id(l_index) IS NOT NULL
3072 AND NOT OE_GLOBALS.Equal(p_line_rec.price_list_id(l_index),
3073 p_header_rec.price_list_id(header_counter))
3074 THEN
3075
3076 -- Error messages are populated in Validate_Price_List
3077
3078 IF NOT OE_BULK_PROCESS_HEADER.Validate_Price_List
3079 (p_line_rec.price_list_id(l_index)
3080 ,p_header_rec.transactional_curr_code(header_counter)
3081 ,p_line_rec.pricing_date(l_index)
3082 ,p_line_rec.calculate_price_flag(l_index)
3083 )
3084 THEN
3085
3086 p_line_rec.lock_control(l_index) := -99;
3087
3088 END IF;
3089
3090 END IF;
3091
3092 -- Validate Customer , customer contact and Sites
3093 -- Validate Bill-to for customer
3094 IF p_line_rec.invoice_to_org_id(l_index) IS NOT NULL
3095 AND NOT OE_GLOBALS.Equal(p_line_rec.invoice_to_org_id(l_index),
3096 p_header_rec.invoice_to_org_id(header_counter))
3097 THEN
3098
3099 IF NOT OE_BULK_PROCESS_HEADER.Validate_Bill_To
3100 (p_line_rec.sold_to_org_id(l_index)
3101 ,p_line_rec.invoice_to_org_id(l_index)
3102 )
3103 THEN
3104 p_line_rec.lock_control(l_index) := -99;
3105 IF l_debug_level > 0 THEN
3106 oe_debug_pub.add( 'IN ERROR 22 ' ) ;
3107 END IF;
3108 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3109 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
3110 OE_Order_Util.Get_Attribute_Name('INVOICE_TO_ORG_ID'));
3111 OE_BULK_MSG_PUB.Add('Y','ERROR');
3112 END IF;
3113
3114 END IF; -- Invoice to is not null
3115
3116 -- Validate ship-to for customer
3117 IF p_line_rec.ship_to_org_id(l_index) IS NOT NULL
3118 AND NOT OE_GLOBALS.Equal(p_line_rec.ship_to_org_id(l_index),
3119 p_header_rec.ship_to_org_id(header_counter))
3120 THEN
3121
3122 IF NOT OE_BULK_PROCESS_HEADER.Validate_Ship_To
3123 (p_line_rec.sold_to_org_id(l_index)
3124 ,p_line_rec.ship_to_org_id(l_index)
3125 )
3126 THEN
3127 p_line_rec.lock_control(l_index) := -99;
3128 IF l_debug_level > 0 THEN
3129 oe_debug_pub.add( 'IN ERROR 22 ' ) ;
3130 END IF;
3131 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3132 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
3133 OE_Order_Util.Get_Attribute_Name('ship_to_org_id'));
3134 OE_BULK_MSG_PUB.Add('Y','ERROR');
3135 END IF;
3136
3137 END IF; -- ship to is not null
3138
3139 IF p_line_rec.deliver_to_org_id(l_index) IS NOT NULL
3140 AND NOT OE_GLOBALS.Equal(p_line_rec.deliver_to_org_id(l_index),
3141 p_header_rec.deliver_to_org_id(header_counter))
3142 THEN
3143
3144 IF NOT OE_BULK_PROCESS_HEADER.Validate_Deliver_To
3145 (p_line_rec.sold_to_org_id(l_index)
3146 ,p_line_rec.deliver_to_org_id(l_index)
3147 )
3148 THEN
3149 p_line_rec.lock_control(l_index) := -99;
3150 IF l_debug_level > 0 THEN
3151 oe_debug_pub.add( 'IN ERROR 22 ' ) ;
3152 END IF;
3153 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3154 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
3155 OE_Order_Util.Get_Attribute_Name('DELIVER_TO_ORG_ID'));
3156 OE_BULK_MSG_PUB.Add('Y','ERROR');
3157 END IF;
3158
3159 END IF; -- deliver to is not null
3160
3161 -- Validate Various Site Contacts
3162 -- Cannot put this in above IF, since you may have a site and contact
3163 -- without a customer.??
3164
3165 -- Validate Bill to contact
3166 IF p_line_rec.invoice_to_contact_id(l_index) IS NOT NULL
3167 THEN
3168
3169 IF NOT OE_GLOBALS.EQUAL(p_line_rec.invoice_to_org_id(l_index),
3170 p_header_rec.invoice_to_org_id(
3171 header_counter))
3172 OR
3173 NOT OE_GLOBALS.EQUAL(p_line_rec.invoice_to_contact_id(l_index),
3174 p_header_rec.invoice_to_contact_id(
3175 header_counter))
3176 THEN
3177
3178
3179 IF NOT OE_BULK_PROCESS_HEADER.Validate_Site_Contact(
3180 p_line_rec.invoice_to_org_id(l_index),
3181 p_line_rec.invoice_to_contact_id(l_index)
3182 )
3183 THEN
3184 p_line_rec.lock_control(l_index) := -99;
3185 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3186 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Entity: Bill To Contact ');
3187 OE_BULK_MSG_PUB.Add('Y','ERROR');
3188 END IF;
3189
3190 END IF;
3191
3192 END IF;
3193
3194 -- Validate Ship to contact
3195 IF p_line_rec.Ship_to_contact_id(l_index) IS NOT NULL
3196 THEN
3197
3198 IF NOT OE_GLOBALS.EQUAL(p_line_rec.Ship_to_org_id(l_index),
3199 p_header_rec.Ship_to_org_id(
3200 header_counter)) OR
3201 NOT OE_GLOBALS.EQUAL(p_line_rec.Ship_to_contact_id(l_index),
3202 p_header_rec.Ship_to_contact_id(
3203 header_counter))
3204 THEN
3205
3206 IF NOT OE_BULK_PROCESS_HEADER.Validate_Site_Contact(
3207 p_line_rec.Ship_to_org_id(l_index),
3208 p_line_rec.Ship_to_contact_id(l_index)
3209 )
3210 THEN
3211 p_line_rec.lock_control(l_index) := -99;
3212 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3213 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Entity: SHIP To Contact ');
3214 OE_BULK_MSG_PUB.Add('Y','ERROR');
3215 END IF;
3216
3217 END IF;
3218
3219 END IF;
3220
3221 -- Validate Deliver to contact
3222 IF p_line_rec.Deliver_to_contact_id(l_index) IS NOT NULL
3223 THEN
3224
3225 IF NOT OE_GLOBALS.EQUAL(p_line_rec.Deliver_to_org_id(l_index),
3226 p_header_rec.Deliver_to_org_id(
3227 header_counter)) OR
3228 NOT OE_GLOBALS.EQUAL(p_line_rec.Deliver_to_contact_id(l_index),
3229 p_header_rec.Deliver_to_contact_id(
3230 header_counter))
3231 THEN
3232
3233 IF NOT OE_BULK_PROCESS_HEADER.Validate_Site_Contact(
3234 p_line_rec.Deliver_to_org_id(l_index),
3235 p_line_rec.Deliver_to_contact_id(l_index)
3236 )
3237 THEN
3238 p_line_rec.lock_control(l_index) := -99;
3239 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3240 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Entity: DeliverToContact');
3241 OE_BULK_MSG_PUB.Add('Y','ERROR');
3242 END IF;
3243
3244 END IF;
3245
3246 END IF;
3247
3248 --{Bug 5054618
3249 --End customer changes
3250 IF p_line_rec.end_Customer_id(l_index) IS NOT NULL
3251 AND NOT OE_GLOBALS.Equal(p_line_rec.end_Customer_id(l_index),p_header_rec.end_customer_id(header_counter))THEN
3252 IF NOT OE_BULK_PROCESS_HEADER.Validate_End_Customer(p_line_rec.end_customer_id(l_index)) THEN
3253 p_line_rec.lock_control(l_index) := -99;
3254 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3255 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_id'));
3256 OE_BULK_MSG_PUB.Add('Y','ERROR');
3257 END IF;
3258 END IF;
3259
3260 IF p_line_rec.end_Customer_Contact_id(l_index) IS NOT NULL AND
3261 NOT OE_GLOBALS.Equal(p_line_rec.end_Customer_Contact_id(l_index),p_header_rec.end_customer_Contact_id(header_counter)) THEN
3262 IF NOT OE_BULK_PROCESS_HEADER.Validate_End_Customer_Contact(p_line_rec.end_customer_contact_id(l_index)) THEN
3263 p_line_rec.lock_control(l_index) := -99;
3264 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3265 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_contact_id'));
3266 OE_BULK_MSG_PUB.Add('Y','ERROR');
3267 END IF;
3268 END IF;
3269
3270
3271 IF p_line_rec.end_Customer_site_use_id(l_index) IS NOT NULL AND NOT
3272 OE_GLOBALS.Equal(p_line_rec.end_Customer_site_use_id(l_index),p_header_rec.end_customer_site_use_id(header_counter)) THEN
3273 IF NOT OE_BULK_PROCESS_HEADER.Validate_End_Customer_site_use(p_line_rec.end_customer_site_use_id(l_index),
3274 p_line_rec.end_customer_id(l_index)) THEN
3275 p_line_rec.lock_control(l_index) := -99;
3276 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3277 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('end_customer_site_use_id'));
3278 OE_BULK_MSG_PUB.Add('Y','ERROR');
3279 END IF;
3280 END IF;
3281
3282 IF p_line_rec.IB_owner(l_index) IS NOT NULL AND NOT
3283 OE_GLOBALS.Equal(p_line_rec.IB_owner(l_index),p_header_rec.IB_owner(header_counter))THEN
3284 IF NOT OE_BULK_PROCESS_HEADER.Validate_IB_Owner(p_line_rec.IB_owner(l_index)) THEN
3285 p_line_rec.lock_control(l_index) := -99;
3286 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3287 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('IB_owner'));
3288 OE_BULK_MSG_PUB.Add('Y','ERROR');
3289 END IF;
3290 END IF;
3291
3292 IF p_line_rec.IB_current_location(l_index) IS NOT NULL AND NOT
3293 OE_GLOBALS.Equal(p_line_rec.IB_current_location(l_index),p_header_rec.IB_current_location(header_counter)) THEN
3294 IF NOT OE_BULK_PROCESS_HEADER.Validate_IB_current_Location (p_line_rec.IB_current_location(l_index)) THEN
3295 p_line_rec.lock_control(l_index) := -99;
3296 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3297 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('IB_location'));
3298 OE_BULK_MSG_PUB.Add('Y','ERROR');
3299 END IF;
3300 END IF;
3301
3302 IF p_line_rec.IB_Installed_at_location(l_index) IS NOT NULL AND NOT
3303 OE_GLOBALS.Equal(p_line_rec.IB_Installed_at_location(l_index),p_header_rec.IB_Installed_at_location(header_counter)) THEN
3304 IF NOT OE_BULK_PROCESS_HEADER.Validate_IB_Inst_loc(p_line_rec.IB_Installed_at_location(l_index)) THEN
3305 p_line_rec.lock_control(l_index) := -99;
3306 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3307 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_Util.Get_Attribute_Name('Installed_at_location'));
3308 OE_BULK_MSG_PUB.Add('Y','ERROR');
3309 END IF;
3310 END IF;
3311 --Bug 5054618}
3312
3313 --PP Revenue Recognition
3314 --ER 4893057
3315 --Need to validate whether any order line with items other than
3316 --service items have the partial period accounting rules attached
3317 --to them.
3318 IF p_line_rec.item_type_code(l_index) <> 'SERVICE' THEN
3319 IF p_line_rec.accounting_rule_id(l_index) IS NOT NULL AND
3320 p_line_rec.accounting_rule_id(l_index) <> FND_API.G_MISS_NUM THEN
3321 IF l_debug_level > 0 THEN
3322 oe_debug_pub.add( 'GETTING ACCOUNTING RULE TYPE' ) ;
3323 END IF;
3324 SELECT type
3325 INTO l_rule_type
3326 FROM ra_rules
3327 WHERE rule_id = p_line_rec.accounting_rule_id(l_index);
3328 IF l_debug_level > 0 THEN
3329 oe_debug_pub.add( 'RULE_TYPE IS :'||L_RULE_TYPE) ;
3330 END IF;
3331 IF l_rule_type = 'PP_DR_ALL' OR l_rule_type = 'PP_DR_PP' THEN
3332 p_line_rec.lock_control(l_index) := -99 ;
3333 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3334 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',OE_Order_UTIL.Get_Attribute_Name('ACCOUNTING_RULE_ID'));
3335 OE_BULK_MSG_PUB.Add('Y','ERROR');
3336 END IF; -- end of accounting rule type
3337 END IF; -- end of accounting_rule_id not null
3338 END IF; -- end of non-service line
3339
3340 -- Validate Tax Exempt # and reason for this customer and site
3341
3342 -- --bug7685103 No need to validate tax exemption numbers
3343 /* IF ((p_line_rec.tax_exempt_flag(l_index) = 'S') AND
3344 -- (p_line_rec.tax_code(l_index) IS NOT NULL) AND
3345 (p_line_rec.tax_exempt_number(l_index) IS NOT NULL) AND
3346 (p_line_rec.tax_exempt_reason_code(l_index) IS NOT NULL) AND
3347 (p_line_rec.invoice_to_org_id(l_index) IS NOT NULL) AND
3348 (p_line_rec.ship_to_org_id(l_index) IS NOT NULL)) THEN
3349
3350 BEGIN
3351
3352 -- eBtax changes
3353
3354 open partyinfo(p_line_rec.invoice_to_org_id(l_index));
3355 fetch partyinfo into l_bill_to_cust_Acct_id,
3356 l_bill_to_party_id,
3357 l_bill_to_party_site_id,
3358 l_org_id;
3359 close partyinfo;
3360
3361 if p_line_rec.ship_to_org_id(l_index) = p_line_rec.invoice_to_org_id(l_index) then
3362 l_ship_to_cust_Acct_id := l_bill_to_cust_Acct_id;
3363 l_ship_to_party_id := l_bill_to_party_id;
3364 l_ship_to_party_site_id := l_bill_to_party_site_id ;
3365 else
3366 open partyinfo(p_line_rec.ship_to_org_id(l_index));
3367 fetch partyinfo into l_ship_to_cust_Acct_id,
3368 l_ship_to_party_id,
3369 l_ship_to_party_site_id,
3370 l_org_id;
3371 close partyinfo;
3372 end if;
3373
3374 if l_debug_level>0 then
3375 oe_debug_pub.add('tax_exempt_number '|| p_line_rec.tax_exempt_number(l_index));
3376 oe_debug_pub.add('reason code'||p_line_rec.tax_exempt_reason_code(l_index));
3377 oe_debug_pub.add('ship to org_id'||p_line_rec.ship_to_org_id(l_index));
3378 oe_debug_pub.add('invoice to org_id'||p_line_rec.invoice_to_org_id(l_index));
3379 oe_debug_pub.add('l_shiop to party site id '||l_ship_to_party_site_id);
3380 oe_debug_pub.add('l_bill_to_party site_id '||l_bill_to_party_site_id);
3381 oe_debug_pub.add('l_org_id '||l_org_id);
3382 oe_debug_pub.add('l_bill to party id'||l_bill_to_party_id);
3383 oe_debug_pub.add('request_date'||p_line_rec.request_date(l_index));
3384 end if;
3385 SELECT 'VALID'
3386 INTO l_dummy
3387 FROM ZX_EXEMPTIONS_V
3388 WHERE EXEMPT_CERTIFICATE_NUMBER = p_line_rec.tax_exempt_number(l_index)
3389 AND EXEMPT_REASON_CODE = p_line_rec.tax_exempt_reason_code(l_index)
3390 AND nvl(site_use_id,nvl(p_line_rec.ship_to_org_id(l_index),p_line_rec.invoice_to_org_id(l_index))) =
3391 nvl(p_line_rec.ship_to_org_id(l_index),p_line_rec.invoice_to_org_id(l_index))
3392 AND nvl(cust_account_id, l_bill_to_cust_acct_id) = l_bill_to_cust_acct_id
3393 AND nvl(PARTY_SITE_ID,nvl(l_ship_to_party_site_id, l_bill_to_party_site_id))=
3394 nvl(l_ship_to_party_site_id, l_bill_to_party_site_id)
3395 and org_id = l_org_id
3396 and party_id = l_bill_to_party_id
3397 AND EXEMPTION_STATUS_CODE = 'PRIMARY'
3398 AND TRUNC(NVL(p_line_rec.request_date(l_index),sysdate))
3399 BETWEEN TRUNC(EFFECTIVE_FROM)
3400 AND TRUNC(NVL(EFFECTIVE_TO,NVL(p_line_rec.request_date(l_index),sysdate)))
3401 AND ROWNUM = 1;
3402
3403
3404
3405 SELECT 'VALID'
3406 INTO l_dummy
3407 FROM TAX_EXEMPTIONS_V
3408 WHERE TAX_EXEMPT_NUMBER = p_line_rec.tax_exempt_number(l_index)
3409 AND TAX_EXEMPT_REASON_CODE=p_line_rec.tax_exempt_reason_code(l_index)
3410 AND SHIP_TO_SITE_USE_ID = nvl(p_line_rec.ship_to_org_id(l_index),
3411 p_line_rec.invoice_to_org_id(l_index))
3412 AND BILL_TO_CUSTOMER_ID = p_line_rec.sold_to_org_id(l_index)
3413 AND STATUS_CODE = 'PRIMARY'
3414 AND TAX_CODE = p_line_rec.tax_code(l_index)
3415 AND TRUNC(NVL(p_line_rec.request_date(l_index),sysdate))
3416 BETWEEN TRUNC(START_DATE)
3417 AND TRUNC(NVL(END_DATE,NVL(p_line_rec.request_date(l_index),sysdate)))
3418 AND ROWNUM = 1;
3419
3420
3421 EXCEPTION
3422
3423 WHEN NO_DATA_FOUND THEN
3424 p_line_rec.lock_control(l_index) := -99;
3425 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3426 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Entity: Tax Exemptions');
3427 OE_BULK_MSG_PUB.Add('Y','ERROR');
3428
3429
3430 END;
3431 END IF;
3432 */ --bug7685103 No need to validate the tax exemption number irrespective of tax handling
3433
3434
3435 -- Validating Tax Information
3436 IF p_line_rec.tax_code(l_index) IS NOT NULL AND
3437 p_line_rec.tax_date(l_index) IS NOT NULL
3438 THEN
3439 BEGIN
3440 -- EBTax Changes
3441 /*
3442 IF oe_code_control.code_release_level >= '110510' THEN
3443
3444 l_AR_Sys_Param_Rec := OE_Sys_Parameters_Pvt.Get_AR_Sys_Params(OE_GLOBALS.G_ORG_ID);
3445 l_sob_id := l_AR_Sys_Param_Rec.set_of_books_id;
3446
3447 SELECT 'VALID'
3448 INTO l_dummy
3449 FROM AR_VAT_TAX V
3450 WHERE V.TAX_CODE = p_line_rec.tax_code(l_index)
3451 AND V.SET_OF_BOOKS_ID = l_sob_id
3452 AND NVL(V.ENABLED_FLAG,'Y')='Y'
3453 AND NVL(V.TAX_CLASS,'O')='O'
3454 AND NVL(V.DISPLAYED_FLAG,'Y')='Y'
3455 AND TRUNC(p_line_rec.tax_date(l_index))
3456 BETWEEN TRUNC(V.START_DATE) AND
3457 TRUNC(NVL(V.END_DATE, p_line_rec.tax_date(l_index)))
3458 AND ROWNUM = 1;
3459
3460 ELSE
3461
3462 SELECT 'VALID'
3463 INTO l_dummy
3464 FROM AR_VAT_TAX V,
3465 AR_SYSTEM_PARAMETERS P
3466 WHERE V.TAX_CODE = p_line_rec.tax_code(l_index)
3467 AND V.SET_OF_BOOKS_ID = P.SET_OF_BOOKS_ID
3468 AND NVL(V.ENABLED_FLAG,'Y')='Y'
3469 AND NVL(V.TAX_CLASS,'O')='O'
3470 AND NVL(V.DISPLAYED_FLAG,'Y')='Y'
3471 AND TRUNC(p_line_rec.tax_date(l_index))
3472 BETWEEN TRUNC(V.START_DATE) AND
3473 TRUNC(NVL(V.END_DATE, p_line_rec.tax_date(l_index)))
3474 AND ROWNUM = 1;
3475
3476 END IF;
3477
3478 */
3479 SELECT 'VALID'
3480 INTO l_dummy
3481 FROM ZX_OUTPUT_CLASSIFICATIONS_V
3482 WHERE LOOKUP_CODE = p_line_rec.tax_code(l_index)
3483 -- AND LOOKUP_TYPE = 'ZX_OUTPUT_CLASSIFICATIONS'
3484 AND ENABLED_FLAG ='Y'
3485 AND ORG_ID IN (p_line_rec.org_id(l_index), -99)
3486 AND TRUNC(p_line_rec.tax_date(l_index)) BETWEEN
3487 TRUNC(START_DATE_ACTIVE) AND
3488 TRUNC(NVL(END_DATE_ACTIVE, p_line_rec.tax_date(l_index)))
3489 AND ROWNUM = 1;
3490 EXCEPTION
3491 WHEN OTHERS THEN
3492 p_line_rec.lock_control(l_index) := -99;
3493 IF l_debug_level > 0 THEN
3494 oe_debug_pub.add( 'IN ERROR 29 ' ) ;
3495 END IF;
3496 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3497 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Entity: Tax Code');
3498 OE_BULK_MSG_PUB.Add('Y','ERROR');
3499 END; -- BEGIN
3500 END IF;
3501
3502
3503 -- Validate ordered quantity for OTA lines. OTA Lines are
3504 -- identified by item_type_code of training. The ordered
3505 -- quantity cannot be greater than 1 for OTA lines.
3506
3507 l_is_ota_line := OE_OTA_UTIL.Is_OTA_Line
3508 (p_line_rec.order_quantity_uom(l_index));
3509
3510 IF (l_is_ota_line)
3511 AND p_line_rec.ordered_quantity(l_index) > 1 THEN
3512
3513 p_line_rec.lock_control(l_index) := -99;
3514 FND_Message.Set_Name('ONT', 'OE_OTA_INVALID_QTY');
3515 OE_BULK_MSG_PUB.Add('Y','ERROR');
3516
3517 END IF;
3518
3519 -- issue a warning message if the PO number
3520 -- is being referenced by another order
3521
3522 IF p_line_rec.cust_po_number(l_index) IS NOT NULL
3523 AND p_line_rec.sold_to_org_id(l_index) IS NOT NULL
3524 THEN
3525 IF OE_Validate_Header.Is_Duplicate_PO_Number
3526 (p_line_rec.cust_po_number(l_index)
3527 ,p_line_rec.sold_to_org_id(l_index)
3528 ,p_line_rec.header_id(l_index) )
3529 THEN
3530 FND_MESSAGE.SET_NAME('ONT','OE_VAL_DUP_PO_NUMBER');
3531 OE_BULK_MSG_PUB.Add('Y','ERROR');
3532 END IF;
3533 END IF;
3534 IF p_line_rec.service_end_date(l_index) IS NOT NULL
3535 AND p_line_rec.service_start_date(l_index) IS NOT NULL
3536 THEN
3537 IF p_line_rec.service_start_date(l_index) >=
3538 p_line_rec.service_end_date(l_index)
3539 THEN
3540 fnd_message.set_name('ONT','OE_SERV_END_DATE');
3541 OE_BULK_MSG_PUB.Add('Y','ERROR');
3542 p_line_rec.lock_control(l_index) := -99;
3543 END IF;
3544 END IF;
3545
3546 -- BEGIN: Validate Desc Flex
3547
3548 IF p_validate_desc_flex = 'Y' THEN
3549
3550 IF OE_Bulk_Order_PVT.G_OE_LINE_ATTRIBUTES = 'Y' THEN
3551 IF NOT OE_VALIDATE.Line_Desc_Flex
3552 (p_context => p_line_rec.context(l_index)
3553 ,p_attribute1 => p_line_rec.attribute1(l_index)
3554 ,p_attribute2 => p_line_rec.attribute2(l_index)
3555 ,p_attribute3 => p_line_rec.attribute3(l_index)
3556 ,p_attribute4 => p_line_rec.attribute4(l_index)
3557 ,p_attribute5 => p_line_rec.attribute5(l_index)
3558 ,p_attribute6 => p_line_rec.attribute6(l_index)
3559 ,p_attribute7 => p_line_rec.attribute7(l_index)
3560 ,p_attribute8 => p_line_rec.attribute8(l_index)
3561 ,p_attribute9 => p_line_rec.attribute9(l_index)
3562 ,p_attribute10 => p_line_rec.attribute10(l_index)
3563 ,p_attribute11 => p_line_rec.attribute11(l_index)
3564 ,p_attribute12 => p_line_rec.attribute12(l_index)
3565 ,p_attribute13 => p_line_rec.attribute13(l_index)
3566 ,p_attribute14 => p_line_rec.attribute14(l_index)
3567 ,p_attribute15 => p_line_rec.attribute15(l_index)
3568 ,p_attribute16 => p_line_rec.attribute16(l_index) -- for bug 2184255
3569 ,p_attribute17 => p_line_rec.attribute17(l_index)
3570 ,p_attribute18 => p_line_rec.attribute18(l_index)
3571 ,p_attribute19 => p_line_rec.attribute19(l_index)
3572 ,p_attribute20 => p_line_rec.attribute20(l_index))
3573 THEN
3574 p_line_rec.lock_control(l_index) := -99;
3575 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
3576 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
3577 'Entity:Flexfield:Line_Desc_Flex');
3578 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
3579
3580 ELSE -- if the flex validation is successfull
3581 -- For bug 2511313
3582 IF p_line_rec.context(l_index) IS NULL
3583 OR p_line_rec.context(l_index) = FND_API.G_MISS_CHAR THEN
3584 p_line_rec.context(l_index) := oe_validate.g_context;
3585 END IF;
3586
3587 IF p_line_rec.attribute1(l_index) IS NULL
3588 OR p_line_rec.attribute1(l_index) = FND_API.G_MISS_CHAR THEN
3589 p_line_rec.attribute1(l_index) := oe_validate.g_attribute1;
3590 END IF;
3591
3592 IF p_line_rec.attribute2(l_index) IS NULL
3593 OR p_line_rec.attribute2(l_index) = FND_API.G_MISS_CHAR THEN
3594 p_line_rec.attribute2(l_index) := oe_validate.g_attribute2;
3595 END IF;
3596
3597 IF p_line_rec.attribute3(l_index) IS NULL
3598 OR p_line_rec.attribute3(l_index) = FND_API.G_MISS_CHAR THEN
3599 p_line_rec.attribute3(l_index) := oe_validate.g_attribute3;
3600 END IF;
3601
3602 IF p_line_rec.attribute4(l_index) IS NULL
3603 OR p_line_rec.attribute4(l_index) = FND_API.G_MISS_CHAR THEN
3604 p_line_rec.attribute4(l_index) := oe_validate.g_attribute4;
3605 END IF;
3606
3607 IF p_line_rec.attribute5(l_index) IS NULL
3608 OR p_line_rec.attribute5(l_index) = FND_API.G_MISS_CHAR THEN
3609 p_line_rec.attribute5(l_index) := oe_validate.g_attribute5;
3610 END IF;
3611
3612 IF p_line_rec.attribute6(l_index) IS NULL
3613 OR p_line_rec.attribute6(l_index) = FND_API.G_MISS_CHAR THEN
3614 p_line_rec.attribute6(l_index) := oe_validate.g_attribute6;
3615 END IF;
3616
3617 IF p_line_rec.attribute7(l_index) IS NULL
3618 OR p_line_rec.attribute7(l_index) = FND_API.G_MISS_CHAR THEN
3619 p_line_rec.attribute7(l_index) := oe_validate.g_attribute7;
3620 END IF;
3621
3622 IF p_line_rec.attribute8(l_index) IS NULL
3623 OR p_line_rec.attribute8(l_index) = FND_API.G_MISS_CHAR THEN
3624 p_line_rec.attribute8(l_index) := oe_validate.g_attribute8;
3625 END IF;
3626
3627 IF p_line_rec.attribute9(l_index) IS NULL
3628 OR p_line_rec.attribute9(l_index) = FND_API.G_MISS_CHAR THEN
3629 p_line_rec.attribute9(l_index) := oe_validate.g_attribute9;
3630 END IF;
3631
3632 IF p_line_rec.attribute10(l_index) IS NULL
3633 OR p_line_rec.attribute10(l_index) = FND_API.G_MISS_CHAR THEN
3634 p_line_rec.attribute10(l_index) := Oe_validate.G_attribute10;
3635 End IF;
3636
3637 IF p_line_rec.attribute11(l_index) IS NULL
3638 OR p_line_rec.attribute11(l_index) = FND_API.G_MISS_CHAR THEN
3639 p_line_rec.attribute11(l_index) := oe_validate.g_attribute11;
3640 END IF;
3641
3642 IF p_line_rec.attribute12(l_index) IS NULL
3643 OR p_line_rec.attribute12(l_index) = FND_API.G_MISS_CHAR THEN
3644 p_line_rec.attribute12(l_index) := oe_validate.g_attribute12;
3645 END IF;
3646
3647 IF p_line_rec.attribute13(l_index) IS NULL
3648 OR p_line_rec.attribute13(l_index) = FND_API.G_MISS_CHAR THEN
3649 p_line_rec.attribute13(l_index) := oe_validate.g_attribute13;
3650 END IF;
3651
3652 IF p_line_rec.attribute14(l_index) IS NULL
3653 OR p_line_rec.attribute14(l_index) = FND_API.G_MISS_CHAR THEN
3654 p_line_rec.attribute14(l_index) := oe_validate.g_attribute14;
3655 END IF;
3656
3657 IF p_line_rec.attribute15(l_index) IS NULL
3658 OR p_line_rec.attribute15(l_index) = FND_API.G_MISS_CHAR THEN
3659 p_line_rec.attribute15(l_index) := oe_validate.g_attribute15;
3660 END IF;
3661
3662 IF p_line_rec.attribute16(l_index) IS NULL
3663 OR p_line_rec.attribute16(l_index) = FND_API.G_MISS_CHAR THEN
3664 p_line_rec.attribute16(l_index) := oe_validate.g_attribute16;
3665 END IF;
3666
3667 IF p_line_rec.attribute17(l_index) IS NULL
3668 OR p_line_rec.attribute17(l_index) = FND_API.G_MISS_CHAR THEN
3669 p_line_rec.attribute17(l_index) := oe_validate.g_attribute17;
3670 END IF;
3671
3672 IF p_line_rec.attribute18(l_index) IS NULL
3673 OR p_line_rec.attribute18(l_index) = FND_API.G_MISS_CHAR THEN
3674 p_line_rec.attribute18(l_index) := oe_validate.g_attribute18;
3675 END IF;
3676
3677 IF p_line_rec.attribute19(l_index) IS NULL
3678 OR p_line_rec.attribute19(l_index) = FND_API.G_MISS_CHAR THEN
3679 p_line_rec.attribute19(l_index) := oe_validate.g_attribute19;
3680 END IF;
3681
3682 IF p_line_rec.attribute20(l_index) IS NULL
3683 OR p_line_rec.attribute20(l_index) = FND_API.G_MISS_CHAR THEN
3684 p_line_rec.attribute20(l_index) := oe_validate.g_attribute20;
3685 END IF;
3686 -- end of assignments, bug 2511313
3687
3688 END IF;
3689 END IF;
3690
3691 IF OE_Bulk_Order_PVT.G_OE_LINE_INDUSTRY_ATTRIBUTE = 'Y' THEN
3692 IF NOT OE_VALIDATE.I_Line_Desc_Flex
3693 (p_context => p_line_rec.Industry_context(l_index)
3694 ,p_attribute1 => p_line_rec.Industry_attribute1(l_index)
3695 ,p_attribute2 => p_line_rec.Industry_attribute2(l_index)
3696 ,p_attribute3 => p_line_rec.Industry_attribute3(l_index)
3697 ,p_attribute4 => p_line_rec.Industry_attribute4(l_index)
3698 ,p_attribute5 => p_line_rec.Industry_attribute5(l_index)
3699 ,p_attribute6 => p_line_rec.Industry_attribute6(l_index)
3700 ,p_attribute7 => p_line_rec.Industry_attribute7(l_index)
3701 ,p_attribute8 => p_line_rec.Industry_attribute8(l_index)
3702 ,p_attribute9 => p_line_rec.Industry_attribute9(l_index)
3703 ,p_attribute10 => p_line_rec.Industry_attribute10(l_index)
3704 ,p_attribute11 => p_line_rec.Industry_attribute11(l_index)
3705 ,p_attribute12 => p_line_rec.Industry_attribute12(l_index)
3706 ,p_attribute13 => p_line_rec.Industry_attribute13(l_index)
3707 ,p_attribute14 => p_line_rec.Industry_attribute14(l_index)
3708 ,p_attribute15 => p_line_rec.Industry_attribute15(l_index)
3709 ,p_attribute16 => p_line_rec.Industry_attribute16(l_index)
3710 ,p_attribute17 => p_line_rec.Industry_attribute17(l_index)
3711 ,p_attribute18 => p_line_rec.Industry_attribute18(l_index)
3712 ,p_attribute19 => p_line_rec.Industry_attribute19(l_index)
3713 ,p_attribute20 => p_line_rec.Industry_attribute20(l_index)
3714 ,p_attribute21 => p_line_rec.Industry_attribute21(l_index)
3715 ,p_attribute22 => p_line_rec.Industry_attribute22(l_index)
3716 ,p_attribute23 => p_line_rec.Industry_attribute23(l_index)
3717 ,p_attribute24 => p_line_rec.Industry_attribute24(l_index)
3718 ,p_attribute25 => p_line_rec.Industry_attribute25(l_index)
3719 ,p_attribute26 => p_line_rec.Industry_attribute26(l_index)
3720 ,p_attribute27 => p_line_rec.Industry_attribute27(l_index)
3721 ,p_attribute28 => p_line_rec.Industry_attribute28(l_index)
3722 ,p_attribute29 => p_line_rec.Industry_attribute29(l_index)
3723 ,p_attribute30 => p_line_rec.Industry_attribute30(l_index))
3724 THEN
3725 p_line_rec.lock_control(l_index) := -99;
3726 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
3727 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
3728 'Entity:Flexfield:Industry Line_Desc_Flex');
3729 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
3730
3731 ELSE -- for bug 2511313
3732
3733 IF p_line_rec.industry_context(l_index) IS NULL
3734 OR p_line_rec.industry_context(l_index) = FND_API.G_MISS_CHAR THEN
3735 p_line_rec.industry_context(l_index) := oe_validate.g_context;
3736 END IF;
3737
3738 IF p_line_rec.industry_attribute1(l_index) IS NULL
3739 OR p_line_rec.industry_attribute1(l_index) = FND_API.G_MISS_CHAR THEN
3740 p_line_rec.industry_attribute1(l_index) := oe_validate.g_attribute1;
3741 END IF;
3742
3743 IF p_line_rec.industry_attribute2(l_index) IS NULL
3744 OR p_line_rec.industry_attribute2(l_index) = FND_API.G_MISS_CHAR THEN
3745 p_line_rec.industry_attribute2(l_index) := oe_validate.g_attribute2;
3746 END IF;
3747
3748 IF p_line_rec.industry_attribute3(l_index) IS NULL
3749 OR p_line_rec.industry_attribute3(l_index) = FND_API.G_MISS_CHAR THEN
3750 p_line_rec.industry_attribute3(l_index) := oe_validate.g_attribute3;
3751 END IF;
3752
3753 IF p_line_rec.industry_attribute4(l_index) IS NULL
3754 OR p_line_rec.industry_attribute4(l_index) = FND_API.G_MISS_CHAR THEN
3755 p_line_rec.industry_attribute4(l_index) := oe_validate.g_attribute4;
3756 END IF;
3757
3758 IF p_line_rec.industry_attribute5(l_index) IS NULL
3759 OR p_line_rec.industry_attribute5(l_index) = FND_API.G_MISS_CHAR THEN
3760 p_line_rec.industry_attribute5(l_index) := oe_validate.g_attribute5;
3761 END IF;
3762
3763 IF p_line_rec.industry_attribute6(l_index) IS NULL
3764 OR p_line_rec.industry_attribute6(l_index) = FND_API.G_MISS_CHAR THEN
3765 p_line_rec.industry_attribute6(l_index) := oe_validate.g_attribute6;
3766 END IF;
3767
3768 IF p_line_rec.industry_attribute7(l_index) IS NULL
3769 OR p_line_rec.industry_attribute7(l_index) = FND_API.G_MISS_CHAR THEN
3770 p_line_rec.industry_attribute7(l_index) := oe_validate.g_attribute7;
3771 END IF;
3772
3773 IF p_line_rec.industry_attribute8(l_index) IS NULL
3774 OR p_line_rec.industry_attribute8(l_index) = FND_API.G_MISS_CHAR THEN
3775 p_line_rec.industry_attribute8(l_index) := oe_validate.g_attribute8;
3776 END IF;
3777
3778 IF p_line_rec.industry_attribute9(l_index) IS NULL
3779 OR p_line_rec.industry_attribute9(l_index) = FND_API.G_MISS_CHAR THEN
3780 p_line_rec.industry_attribute9(l_index) := oe_validate.g_attribute9;
3781 END IF;
3782
3783 IF p_line_rec.industry_attribute10(l_index) IS NULL
3784 OR p_line_rec.industry_attribute10(l_index) = FND_API.G_MISS_CHAR THEN
3785 p_line_rec.industry_attribute10(l_index) := oe_validate.g_attribute10;
3786 END IF;
3787
3788 IF p_line_rec.industry_attribute11(l_index) IS NULL
3789 OR p_line_rec.industry_attribute11(l_index) = FND_API.G_MISS_CHAR THEN
3790 p_line_rec.industry_attribute11(l_index) := oe_validate.g_attribute11;
3791 END IF;
3792
3793 IF p_line_rec.industry_attribute12(l_index) IS NULL
3794 OR p_line_rec.industry_attribute12(l_index) = FND_API.G_MISS_CHAR THEN
3795 p_line_rec.industry_attribute12(l_index) := oe_validate.g_attribute12;
3796 END IF;
3797
3798 IF p_line_rec.industry_attribute13(l_index) IS NULL
3799 OR p_line_rec.industry_attribute13(l_index) = FND_API.G_MISS_CHAR THEN
3800 p_line_rec.industry_attribute13(l_index) := oe_validate.g_attribute13;
3801 END IF;
3802
3803 IF p_line_rec.industry_attribute14(l_index) IS NULL
3804 OR p_line_rec.industry_attribute14(l_index) = FND_API.G_MISS_CHAR THEN
3805 p_line_rec.industry_attribute14(l_index) := oe_validate.g_attribute14;
3806 END IF;
3807
3808 IF p_line_rec.industry_attribute15(l_index) IS NULL
3809 OR p_line_rec.industry_attribute15(l_index) = FND_API.G_MISS_CHAR THEN
3810 p_line_rec.industry_attribute15(l_index) := oe_validate.g_attribute15;
3811 END IF;
3812
3813 IF p_line_rec.industry_attribute16(l_index) IS NULL
3814 OR p_line_rec.industry_attribute16(l_index) = FND_API.G_MISS_CHAR THEN
3815 p_line_rec.industry_attribute16(l_index) := oe_validate.g_attribute16;
3816 END IF;
3817
3818 IF p_line_rec.industry_attribute17(l_index) IS NULL
3819 OR p_line_rec.industry_attribute17(l_index) = FND_API.G_MISS_CHAR THEN
3820 p_line_rec.industry_attribute17(l_index) := oe_validate.g_attribute17;
3821 END IF;
3822
3823 IF p_line_rec.industry_attribute18(l_index) IS NULL
3824 OR p_line_rec.industry_attribute18(l_index) = FND_API.G_MISS_CHAR THEN
3825 p_line_rec.industry_attribute18(l_index) := oe_validate.g_attribute18;
3826 END IF;
3827
3828 IF p_line_rec.industry_attribute19(l_index) IS NULL
3829 OR p_line_rec.industry_attribute19(l_index) = FND_API.G_MISS_CHAR THEN
3830 p_line_rec.industry_attribute19(l_index) := oe_validate.g_attribute19;
3831 END IF;
3832
3833 IF p_line_rec.industry_attribute20(l_index) IS NULL
3834 OR p_line_rec.industry_attribute20(l_index) = FND_API.G_MISS_CHAR THEN
3835 p_line_rec.industry_attribute20(l_index) := oe_validate.g_attribute20;
3836 END IF;
3837
3838 IF p_line_rec.industry_attribute21(l_index) IS NULL
3839 OR p_line_rec.industry_attribute21(l_index) = FND_API.G_MISS_CHAR THEN
3840 p_line_rec.industry_attribute21(l_index) := oe_validate.g_attribute21;
3841 END IF;
3842
3843 IF p_line_rec.industry_attribute22(l_index) IS NULL
3844 OR p_line_rec.industry_attribute22(l_index) = FND_API.G_MISS_CHAR THEN
3845 p_line_rec.industry_attribute22(l_index) := oe_validate.g_attribute22;
3846 END IF;
3847
3848 IF p_line_rec.industry_attribute23(l_index) IS NULL
3849 OR p_line_rec.industry_attribute23(l_index) = FND_API.G_MISS_CHAR THEN
3850 p_line_rec.industry_attribute23(l_index) := oe_validate.g_attribute23;
3851 END IF;
3852
3853 IF p_line_rec.industry_attribute24(l_index) IS NULL
3854 OR p_line_rec.industry_attribute24(l_index) = FND_API.G_MISS_CHAR THEN
3855 p_line_rec.industry_attribute24(l_index) := oe_validate.g_attribute24;
3856 END IF;
3857
3858 IF p_line_rec.industry_attribute25(l_index) IS NULL
3859 OR p_line_rec.industry_attribute25(l_index) = FND_API.G_MISS_CHAR THEN
3860 p_line_rec.industry_attribute25(l_index) := oe_validate.g_attribute25;
3861 END IF;
3862
3863 IF p_line_rec.industry_attribute26(l_index) IS NULL
3864 OR p_line_rec.industry_attribute26(l_index) = FND_API.G_MISS_CHAR THEN
3865 p_line_rec.industry_attribute26(l_index) := oe_validate.g_attribute26;
3866 END IF;
3867
3868 IF p_line_rec.industry_attribute27(l_index) IS NULL
3869 OR p_line_rec.industry_attribute27(l_index) = FND_API.G_MISS_CHAR THEN
3870 p_line_rec.industry_attribute27(l_index) := oe_validate.g_attribute27;
3871 END IF;
3872
3873 IF p_line_rec.industry_attribute28(l_index) IS NULL
3874 OR p_line_rec.industry_attribute28(l_index) = FND_API.G_MISS_CHAR THEN
3875 p_line_rec.industry_attribute28(l_index) := oe_validate.g_attribute28;
3876 END IF;
3877
3878 IF p_line_rec.industry_attribute29(l_index) IS NULL
3879 OR p_line_rec.industry_attribute29(l_index) = FND_API.G_MISS_CHAR THEN
3880 p_line_rec.industry_attribute29(l_index) := oe_validate.g_attribute29;
3881 END IF;
3882
3883 IF p_line_rec.industry_attribute30(l_index) IS NULL
3884 OR p_line_rec.industry_attribute30(l_index) = FND_API.G_MISS_CHAR THEN
3885 p_line_rec.industry_attribute30(l_index) := oe_validate.g_attribute30;
3886 END IF;
3887
3888 -- end of bug 2511313
3889 END IF;
3890
3891 END IF;
3892
3893 IF OE_Bulk_Order_PVT.G_OE_LINE_TP_ATTRIBUTES = 'Y' THEN
3894 IF NOT OE_VALIDATE.TP_Line_Desc_Flex
3895 (p_context => p_line_rec.tp_context(l_index)
3896 ,p_attribute1 => p_line_rec.tp_attribute1(l_index)
3897 ,p_attribute2 => p_line_rec.tp_attribute2(l_index)
3898 ,p_attribute3 => p_line_rec.tp_attribute3(l_index)
3899 ,p_attribute4 => p_line_rec.tp_attribute4(l_index)
3900 ,p_attribute5 => p_line_rec.tp_attribute5(l_index)
3901 ,p_attribute6 => p_line_rec.tp_attribute6(l_index)
3902 ,p_attribute7 => p_line_rec.tp_attribute7(l_index)
3903 ,p_attribute8 => p_line_rec.tp_attribute8(l_index)
3904 ,p_attribute9 => p_line_rec.tp_attribute9(l_index)
3905 ,p_attribute10 => p_line_rec.tp_attribute10(l_index)
3906 ,p_attribute11 => p_line_rec.tp_attribute11(l_index)
3907 ,p_attribute12 => p_line_rec.tp_attribute12(l_index)
3908 ,p_attribute13 => p_line_rec.tp_attribute13(l_index)
3909 ,p_attribute14 => p_line_rec.tp_attribute14(l_index)
3910 ,p_attribute15 => p_line_rec.tp_attribute15(l_index))
3911 THEN
3912 p_line_rec.lock_control(l_index) := -99;
3913 FND_MESSAGE.SET_NAME('ONT','OE_INVALID_ATTRIBUTE');
3914 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',
3915 'Entity:Flexfield:TP Line_Desc_Flex');
3916 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
3917
3918 ELSE -- if the flex validation is successfull
3919 -- For bug 2511313
3920 IF p_line_rec.tp_context(l_index) IS NULL
3921 OR p_line_rec.tp_context(l_index) = FND_API.G_MISS_CHAR THEN
3922 p_line_rec.tp_context(l_index) := oe_validate.g_context;
3923 END IF;
3924
3925 IF p_line_rec.tp_attribute1(l_index) IS NULL
3926 OR p_line_rec.tp_attribute1(l_index) = FND_API.G_MISS_CHAR THEN
3927 p_line_rec.tp_attribute1(l_index) := oe_validate.g_attribute1;
3928 END IF;
3929
3930 IF p_line_rec.tp_attribute2(l_index) IS NULL
3931 OR p_line_rec.tp_attribute2(l_index) = FND_API.G_MISS_CHAR THEN
3932 p_line_rec.tp_attribute2(l_index) := oe_validate.g_attribute2;
3933 END IF;
3934
3935 IF p_line_rec.tp_attribute3(l_index) IS NULL
3936 OR p_line_rec.tp_attribute3(l_index) = FND_API.G_MISS_CHAR THEN
3937 p_line_rec.tp_attribute3(l_index) := oe_validate.g_attribute3;
3938 END IF;
3939
3940 IF p_line_rec.tp_attribute4(l_index) IS NULL
3941 OR p_line_rec.tp_attribute4(l_index) = FND_API.G_MISS_CHAR THEN
3942 p_line_rec.tp_attribute4(l_index) := oe_validate.g_attribute4;
3943 END IF;
3944
3945 IF p_line_rec.tp_attribute5(l_index) IS NULL
3946 OR p_line_rec.tp_attribute5(l_index) = FND_API.G_MISS_CHAR THEN
3947 p_line_rec.tp_attribute5(l_index) := oe_validate.g_attribute5;
3948 END IF;
3949
3950 IF p_line_rec.tp_attribute6(l_index) IS NULL
3951 OR p_line_rec.tp_attribute6(l_index) = FND_API.G_MISS_CHAR THEN
3952 p_line_rec.tp_attribute6(l_index) := oe_validate.g_attribute6;
3953 END IF;
3954
3955 IF p_line_rec.tp_attribute7(l_index) IS NULL
3956 OR p_line_rec.tp_attribute7(l_index) = FND_API.G_MISS_CHAR THEN
3957 p_line_rec.tp_attribute7(l_index) := oe_validate.g_attribute7;
3958 END IF;
3959
3960 IF p_line_rec.tp_attribute8(l_index) IS NULL
3961 OR p_line_rec.tp_attribute8(l_index) = FND_API.G_MISS_CHAR THEN
3962 p_line_rec.tp_attribute8(l_index) := oe_validate.g_attribute8;
3963 END IF;
3964
3965 IF p_line_rec.tp_attribute9(l_index) IS NULL
3966 OR p_line_rec.tp_attribute9(l_index) = FND_API.G_MISS_CHAR THEN
3967 p_line_rec.tp_attribute9(l_index) := oe_validate.g_attribute9;
3968 END IF;
3969
3970 IF p_line_rec.tp_attribute10(l_index) IS NULL
3971 OR p_line_rec.tp_attribute10(l_index) = FND_API.G_MISS_CHAR THEN
3972 p_line_rec.tp_attribute10(l_index) := Oe_validate.G_attribute10;
3973 End IF;
3974
3975 IF p_line_rec.tp_attribute11(l_index) IS NULL
3976 OR p_line_rec.tp_attribute11(l_index) = FND_API.G_MISS_CHAR THEN
3977 p_line_rec.tp_attribute11(l_index) := oe_validate.g_attribute11;
3978 END IF;
3979
3980 IF p_line_rec.tp_attribute12(l_index) IS NULL
3981 OR p_line_rec.tp_attribute12(l_index) = FND_API.G_MISS_CHAR THEN
3982 p_line_rec.tp_attribute12(l_index) := oe_validate.g_attribute12;
3983 END IF;
3984
3985 IF p_line_rec.tp_attribute13(l_index) IS NULL
3986 OR p_line_rec.tp_attribute13(l_index) = FND_API.G_MISS_CHAR THEN
3987 p_line_rec.tp_attribute13(l_index) := oe_validate.g_attribute13;
3988 END IF;
3989
3990 IF p_line_rec.tp_attribute14(l_index) IS NULL
3991 OR p_line_rec.tp_attribute14(l_index) = FND_API.G_MISS_CHAR THEN
3992 p_line_rec.tp_attribute14(l_index) := oe_validate.g_attribute14;
3993 END IF;
3994
3995 IF p_line_rec.tp_attribute15(l_index) IS NULL
3996 OR p_line_rec.tp_attribute15(l_index) = FND_API.G_MISS_CHAR THEN
3997 p_line_rec.tp_attribute15(l_index) := oe_validate.g_attribute15;
3998 END IF;
3999 -- end of assignments, bug 2511313
4000
4001 END IF;
4002
4003 END IF;
4004
4005 END IF; --End if p_validate_desc_flex is 'Y'
4006
4007 -- END: Desc Flex Validations
4008
4009
4010 -- Calculate Price Validations from OEXVIMSB.pls
4011
4012 IF p_line_rec.calculate_price_flag(l_index) = 'N' THEN
4013
4014 IF (p_line_rec.unit_list_price(l_index) IS NULL
4015 OR p_line_rec.unit_selling_price(l_index) IS NULL)
4016 THEN
4017 IF l_debug_level > 0 THEN
4018 oe_debug_pub.add( 'LIST PRICE OR SELLING PRICE IS NULL... ' ) ;
4019 END IF;
4020 p_line_rec.lock_control(l_index) := -99;
4021 FND_MESSAGE.SET_NAME('ONT','OE_OI_PRICE');
4022 OE_BULK_MSG_PUB.Add;
4023 ELSIF p_line_rec.pricing_quantity(l_index) IS NULL
4024 THEN
4025 IF l_debug_level > 0 THEN
4026 oe_debug_pub.add( 'PRICING QUANTITY IS NULL...RESETTING' ) ;
4027 END IF;
4028 p_line_rec.pricing_quantity(l_index) :=
4029 p_line_rec.ordered_quantity(l_index);
4030 p_line_rec.pricing_quantity_uom(l_index) :=
4031 p_line_rec.order_quantity_uom(l_index);
4032 END IF;
4033
4034 ELSE
4035
4036 OE_BULK_ORDER_PVT.G_PRICING_NEEDED := 'Y';
4037
4038 END IF;
4039
4040 ---------------------------------------------------------------
4041 -- Add a 100% default sales credit record for this salesperson
4042 -- if different from salesperson on header
4043 ---------------------------------------------------------------
4044 oe_debug_pub.add(' salesrep_id ');
4045
4046 IF p_line_rec.salesrep_id(l_index) IS NOT NULL AND
4047 p_header_rec.salesrep_id(header_counter) IS NOT NULL THEN
4048
4049 --If salesrep on line is different than salesrep on header
4050 IF p_line_rec.salesrep_id(l_index) <>
4051 p_header_rec.salesrep_id(header_counter) THEN
4052
4053 x_line_scredit_rec.header_id.extend(1);
4054 x_line_scredit_rec.line_id.extend(1);
4055 x_line_scredit_rec.salesrep_id.extend(1);
4056 x_line_scredit_rec.sales_credit_type_id.extend(1);
4057
4058 l_c_index := OE_Bulk_Cache.Load_Salesrep
4059 (p_key => p_line_rec.salesrep_id(l_index));
4060
4061 x_line_scredit_rec.header_id(l_scredit_index)
4062 := p_line_rec.header_id(l_index);
4063 x_line_scredit_rec.line_id(l_scredit_index)
4064 := p_line_rec.line_id(l_index);
4065 x_line_scredit_rec.salesrep_id(l_scredit_index)
4066 := p_line_rec.salesrep_id(l_index);
4067 x_line_scredit_rec.sales_credit_type_id(l_scredit_index)
4068 := OE_Bulk_Cache.G_SALESREP_TBL(l_c_index).sales_credit_type_id;
4069
4070 l_scredit_index := l_scredit_index + 1;
4071
4072 END IF;
4073
4074 END IF;
4075
4076 ---------------------------------------------------------------
4077 -- Evaluate Holds For the line
4078 ---------------------------------------------------------------
4079
4080 IF NOT (p_line_rec.lock_control(l_index) = -99 ) THEN
4081 -- Check for holds
4082 /*ER#7479609 start
4083 OE_Bulk_Holds_PVT.Evaluate_Holds(
4084 p_header_id => p_line_rec.header_id(l_index),
4085 p_line_id => p_line_rec.line_id(l_index),
4086 p_line_number => p_line_rec.line_number(l_index),
4087 p_sold_to_org_id => p_line_rec.sold_to_org_id(l_index),
4088 p_inventory_item_id => p_line_rec.inventory_item_id(l_index),
4089 p_ship_from_org_id => p_line_rec.ship_from_org_id(l_index),
4090 p_invoice_to_org_id => p_line_rec.invoice_to_org_id(l_index),
4091 p_ship_to_org_id => p_line_rec.ship_to_org_id(l_index),
4092 p_top_model_line_id => p_line_rec.top_model_line_id(l_index),
4093 p_ship_set_name => NULL,
4094 p_arrival_set_name => NULL,
4095 p_on_generic_hold => l_on_generic_hold,
4096 p_on_booking_hold => l_on_booking_hold,
4097 p_on_scheduling_hold => l_on_scheduling_hold
4098 );
4099 ER#7479609 end*/
4100
4101 --ER#7479609 start
4102 --7671422 l_header_rec_for_hold.order_type_id := p_header_rec.order_type_id(l_index);
4103 l_header_rec_for_hold.order_type_id := p_header_rec.order_type_id(header_counter); --7671422
4104 l_line_rec_for_hold.header_id := p_line_rec.header_id(l_index);
4105 l_line_rec_for_hold.line_id := p_line_rec.line_id(l_index);
4106 l_line_rec_for_hold.line_number := p_line_rec.line_number(l_index);
4107 l_line_rec_for_hold.sold_to_org_id := p_line_rec.sold_to_org_id(l_index);
4108 l_line_rec_for_hold.inventory_item_id := p_line_rec.inventory_item_id(l_index);
4109 l_line_rec_for_hold.ship_from_org_id := p_line_rec.ship_from_org_id(l_index);
4110 l_line_rec_for_hold.invoice_to_org_id := p_line_rec.invoice_to_org_id(l_index);
4111 l_line_rec_for_hold.ship_to_org_id := p_line_rec.ship_to_org_id(l_index);
4112 l_line_rec_for_hold.top_model_line_id := p_line_rec.top_model_line_id(l_index);
4113 l_line_rec_for_hold.price_list_id := p_line_rec.price_list_id(l_index);
4114 l_line_rec_for_hold.creation_date := to_char(sysdate,'DD-MON-RRRR');
4115 l_line_rec_for_hold.shipping_method_code := p_line_rec.shipping_method_code(l_index);
4116 l_line_rec_for_hold.deliver_to_org_id := p_line_rec.deliver_to_org_id(l_index);
4117 l_line_rec_for_hold.source_type_code := p_line_rec.source_type_code(l_index);
4118 l_line_rec_for_hold.line_type_id := p_line_rec.line_type_id(l_index);
4119 l_line_rec_for_hold.payment_term_id := p_line_rec.payment_term_id(l_index);
4120 l_line_rec_for_hold.created_by := NVL(FND_GLOBAL.USER_ID, -1);
4121
4122 oe_debug_pub.add(' Evaluate_Holds ');
4123
4124 OE_Bulk_Holds_PVT.Evaluate_Holds(
4125 p_header_rec => NULL,
4126 p_line_rec => l_line_rec_for_hold,
4127 p_on_generic_hold => l_on_generic_hold,
4128 p_on_booking_hold => l_on_booking_hold,
4129 p_on_scheduling_hold => l_on_scheduling_hold
4130 );
4131 --ER#7479609 end
4132
4133 -- If the line is on Generic/Booking/Scheduling hold, add it to
4134 -- the Global.
4135
4136 IF l_on_generic_hold THEN
4137 OE_Bulk_Holds_PVT.G_Line_Holds_Tbl(l_index).On_Generic_Hold := 'Y';
4138 END IF;
4139
4140 IF l_on_scheduling_hold THEN
4141 OE_Bulk_Holds_PVT.G_Line_Holds_Tbl(l_index).On_Scheduling_Hold := 'Y';
4142 END IF;
4143
4144 END IF;
4145
4146
4147 ---------------------------------------------------------------
4148 -- BOOKING VALIDATIONS
4149 ---------------------------------------------------------------
4150 oe_debug_pub.add('booked_flag ');
4151
4152 IF p_line_rec.booked_flag(l_index) = 'Y' THEN
4153
4154 Check_Book_Reqd_Attributes(p_line_rec => p_line_rec
4155 ,p_index => l_index
4156 ,x_return_status => l_return_status);
4157
4158 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4159 l_book_failed := TRUE;
4160 END IF;
4161
4162 END IF;
4163
4164
4165 ---------------------------------------------------------------
4166 -- SCHEDULING VALIDATIONS
4167 ---------------------------------------------------------------
4168 oe_debug_pub.add(' SCHEDULING :');
4169 l_c_index := OE_BULK_CACHE.Load_Order_Type(p_header_rec.order_type_id(header_counter));
4170
4171 IF (OE_BULK_ORDER_PVT.G_AUTO_SCHEDULE = 'Y'
4172 OR OE_BULK_CACHE.G_ORDER_TYPE_TBL(l_c_index).auto_scheduling_flag = 'Y'
4173 OR p_line_rec.schedule_ship_date(l_index) IS NOT NULL
4174 OR p_line_rec.schedule_arrival_date(l_index) IS NOT NULL)
4175 AND p_line_rec.source_type_code(l_index) = 'INTERNAL'
4176 AND p_line_rec.lock_control(l_index) <> -99
4177 AND nvl(p_line_rec.lock_control(l_index), 0) <> -98
4178 AND nvl(p_line_rec.lock_control(l_index), 0) <> -97
4179 AND NOT ( p_schedule_configurations = 'N' AND
4180 p_line_rec.item_type_code(l_index) IN ('MODEL', 'CLASS',
4181 'OPTION'))
4182 THEN
4183
4184 IF l_debug_level > 0 THEN
4185 oe_debug_pub.add( 'SCHEDULING VALIDATIONS - ato scheduling') ;
4186 END IF;
4187
4188 l_d_index := OE_BULK_CACHE.Load_Line_Type(p_line_rec.line_type_id(l_index));
4189 IF ( OE_BULK_ORDER_PVT.G_SCHEDULE_LINE_ON_HOLD = 'N'
4190 AND l_on_generic_hold )
4191 THEN
4192 -- Add scheduling on hold message
4193 FND_MESSAGE.SET_NAME('ONT','OE_SCH_LINE_ON_HOLD');
4194 OE_BULK_MSG_PUB.Add;
4195 ELSE
4196 IF OE_BULK_CACHE.G_LINE_TYPE_TBL(l_d_index).scheduling_level_code = 'ONE'
4197 OR (OE_BULK_CACHE.G_LINE_TYPE_TBL(l_d_index).scheduling_level_code IS NULL
4198 AND OE_BULK_CACHE.G_ORDER_TYPE_TBL(l_c_index).scheduling_level_code = 'ONE')
4199 THEN
4200 -- Add ATP Only message
4201 FND_MESSAGE.SET_NAME('ONT','OE_ATP_ONLY');
4202 OE_BULK_MSG_PUB.Add;
4203 ELSE
4204 Check_Scheduling_Attributes(p_line_rec => p_line_rec
4205 ,p_index =>l_index
4206 ,x_return_status => l_return_status);
4207
4208 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4209
4210 IF OE_BULK_CACHE.G_LINE_TYPE_TBL(l_d_index).scheduling_level_code = 'FOUR'
4211 OR OE_BULK_CACHE.G_LINE_TYPE_TBL(l_d_index).scheduling_level_code = 'FIVE'
4212 THEN
4213
4214 IF p_line_rec.ship_from_org_id(l_index) IS NOT NULL THEN
4215 IF p_line_rec.schedule_ship_date(l_index) IS NOT NULL THEN
4216 p_line_rec.schedule_arrival_date(l_index) :=
4217 p_line_rec.schedule_ship_date(l_index);
4218 ELSIF p_line_rec.schedule_arrival_date(l_index) IS NOT NULL THEN
4219 p_line_rec.schedule_ship_date(l_index) :=
4220 p_line_rec.schedule_arrival_date(l_index);
4221 ELSE
4222
4223 p_line_rec.schedule_ship_date(l_index) :=
4224 p_line_rec.request_date(l_index);
4225 p_line_rec.schedule_arrival_date(l_index) :=
4226 p_line_rec.request_date(l_index);
4227 END IF;
4228 p_line_rec.schedule_status_code(l_index) := 'SCHEDULED';
4229 p_line_rec.visible_demand_flag(l_index) := 'N';
4230 ELSE
4231 FND_MESSAGE.SET_NAME('ONT','OE_INV_NO_WAREHOUSE');
4232 OE_BULK_MSG_PUB.Add;
4233 IF p_line_rec.schedule_ship_date(l_index) IS NOT NULL
4234 OR p_line_rec.schedule_arrival_date(l_index) IS NOT NULL THEN
4235 p_line_rec.lock_control(l_index) := -99;
4236 END IF;
4237 END IF; -- ship from not null.
4238
4239 ELSE -- four/five
4240 p_line_rec.schedule_status_code(l_index) := 'TO_BE_SCHEDULED';
4241 OE_BULK_ORDER_PVT.G_SCH_COUNT := OE_BULK_ORDER_PVT.G_SCH_COUNT + 1;
4242 END IF;
4243 ELSE
4244 IF p_line_rec.schedule_ship_date(l_index) IS NOT NULL
4245 OR p_line_rec.schedule_arrival_date(l_index) IS NOT NULL THEN
4246 p_line_rec.lock_control(l_index) := -99;
4247 END IF;
4248 END IF; -- return status
4249 END IF;
4250 END IF;
4251 -- Pack J
4252 -- Latest Acceptable date violation check when flag is set to 'Honor'
4253 -- 3940632 : dates truncated before comparison.
4254 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510'
4255 AND OE_SYS_PARAMETERS.value('LATEST_ACCEPTABLE_DATE_FLAG') ='H' THEN
4256 l_order_date_type_code :=
4257 NVL(OE_BULK_SCHEDULE_UTIL.Get_Date_Type(p_line_rec.header_id(l_index)),'SHIP');
4258 IF trunc(NVL(p_line_rec.latest_acceptable_date(l_index),p_line_rec.request_date(l_index)))
4259 < trunc(p_line_rec.request_date(l_index)) THEN -- LAD less than request date
4260 IF l_debug_level > 0 THEN
4261 oe_debug_pub.add( 'Request date exceeds Latest Acceptable Date ',1 ) ;
4262 END IF;
4263 FND_MESSAGE.SET_NAME('ONT','ONT_SCH_REQUEST_EXCEED_LAD');
4264 OE_BULK_MSG_PUB.Add;
4265 ELSIF ((l_order_date_type_code = 'SHIP'
4266 AND trunc(NVL(p_line_rec.schedule_ship_date(l_index),p_line_rec.request_date(l_index)))
4267 > trunc(NVL(p_line_rec.latest_acceptable_date(l_index),p_line_rec.request_date(l_index))))
4268 OR (l_order_date_type_code = 'ARRIVAL'
4269 AND trunc(NVL(p_line_rec.schedule_arrival_date(l_index),p_line_rec.request_date(l_index)))
4270 > trunc(NVL(p_line_rec.latest_acceptable_date(l_index),p_line_rec.request_date(l_index))))) THEN
4271 FND_MESSAGE.SET_NAME('ONT','ONT_SCH_LAD_SCH_FAILED');
4272 OE_BULK_MSG_PUB.Add;
4273 IF l_debug_level > 0 THEN
4274 oe_debug_pub.add( 'SCHEDULE DATE EXCEEDS LAD ',1 ) ;
4275 END IF;
4276
4277 END IF;
4278 END IF;
4279
4280 END IF;
4281
4282
4283 ---------------------------------------------------------------
4284 -- INCLUDED ITEM PROCESSING
4285 ---------------------------------------------------------------
4286 oe_debug_pub.add(' NCLUDED ITEM PROCESSING ');
4287
4288 IF p_line_rec.lock_control(l_index) <> - 99
4289 AND p_line_rec.item_type_code(l_index) = 'KIT'
4290 THEN
4291
4292 OE_BULK_LINE_UTIL.Append_Included_Items
4293 (p_parent_index => l_index
4294 ,p_line_rec => p_line_rec
4295 ,p_header_index => header_counter
4296 ,p_header_rec => p_header_rec
4297 ,x_ii_count => p_line_rec.ii_count(l_index)
4298 ,x_ii_start_index => p_line_rec.ii_start_index(l_index)
4299 ,x_ii_on_generic_hold => l_ii_on_generic_hold
4300 );
4301
4302 -- Logic to mark kits and included item scheduling fields
4303 -- if ii is on hold and schedule lines on hold is 'No'
4304 -- has moved to OEBULINB, Assign_Included_Items
4305
4306 END IF;
4307
4308 ---------------------------------------------------------------
4309 -- Cache EDI attributes if order requires acknowledgments
4310 ---------------------------------------------------------------
4311 IF p_header_rec.first_ack_code(header_counter) = 'X'
4312 AND nvl(p_line_rec.lock_control(l_index),0) <> - 99
4313 THEN
4314
4315 IF p_line_rec.ship_to_org_id(l_index) IS NOT NULL THEN
4316 l_c_index := OE_Bulk_Cache.Load_Ship_To
4317 (p_key => p_line_rec.ship_to_org_id(l_index)
4318 ,p_edi_attributes => 'Y'
4319 );
4320 END IF;
4321
4322 IF p_line_rec.invoice_to_org_id(l_index) IS NOT NULL THEN
4323 l_c_index := OE_Bulk_Cache.Load_Invoice_To
4324 (p_key => p_line_rec.invoice_to_org_id(l_index)
4325 ,p_edi_attributes => 'Y'
4326 );
4327 END IF;
4328
4329 IF p_line_rec.ship_from_org_id(l_index) IS NOT NULL THEN
4330 l_c_index := OE_Bulk_Cache.Load_Ship_From
4331 (p_key => p_line_rec.ship_from_org_id(l_index)
4332 );
4333 END IF;
4334 --{Bug 5054618
4335 -- added for endcustomer changes
4336 IF p_line_rec.end_customer_id(l_index) IS NOT NULL THEN
4337 l_c_index := OE_Bulk_Cache.Load_End_customer
4338 (p_key => p_line_rec.end_customer_id(l_index)
4339 ,p_edi_attributes => 'Y'
4340 );
4341 END IF;
4342
4343 IF p_line_rec.End_customer_site_use_id(l_index) IS NOT NULL THEN
4344 l_c_index := OE_Bulk_Cache.Load_end_customer_site
4345 (p_key => p_line_rec.end_customer_site_use_id(l_index)
4346 ,p_edi_attributes => 'Y'
4347 );
4348 END IF;
4349 --Bug 5054618}
4350
4351 END IF;
4352
4353
4354 ---------------------------------------------------------------
4355 -- LAST STEP: ERROR PROCESSING
4356 ---------------------------------------------------------------
4357 oe_debug_pub.add(' ERROR PROCESSING ');
4358 -- Set Global Error Record If we have had any validation failures
4359
4360 IF (p_line_rec.lock_control(l_index) = -99 ) THEN
4361
4362 -- We update the error table only once for a combination of
4363 -- order_source_id
4364
4365 IF ((p_line_rec.order_source_id(l_index) <> l_order_source_id) OR
4366 (p_line_rec.orig_sys_document_ref(l_index) <> l_orig_sys_document_ref))
4367 THEN
4368
4369 l_error_count := l_error_count + 1;
4370
4371 OE_Bulk_Order_PVT.G_ERROR_REC.order_source_id.EXTEND(1);
4372 OE_Bulk_Order_PVT.G_ERROR_REC.order_source_id(l_error_count)
4373 := p_line_rec.order_source_id(l_index);
4374 l_order_source_id := p_line_rec.order_source_id(l_index);
4375
4376 OE_Bulk_Order_PVT.G_ERROR_REC.orig_sys_document_ref.EXTEND(1);
4377 OE_Bulk_Order_PVT.G_ERROR_REC.orig_sys_document_ref(l_error_count)
4378 := p_line_rec.orig_sys_document_ref(l_index);
4379 l_orig_sys_document_ref := p_line_rec.orig_sys_document_ref(l_index);
4380 OE_Bulk_Order_PVT.G_ERROR_REC.header_id.EXTEND(1);
4381 OE_Bulk_Order_PVT.G_ERROR_REC.header_id(l_error_count)
4382 := p_line_rec.header_id(l_index);
4383
4384 -- Mark Corresponding Header Record as invalid as well
4385 p_header_rec.lock_control(header_counter) := -99;
4386
4387 END IF; -- new order source/orig sys combination
4388
4389
4390 END IF; -- Line has errors
4391
4392 -- Next Line
4393 l_nbr_ctr := l_nbr_ctr + 1;
4394
4395 l_last_line_index := l_index;
4396
4397 END LOOP;
4398
4399 -- Populate header calculate_price_flag
4400
4401 --PIB
4402 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' THEN
4403 Oe_bulk_priceorder_pvt.set_hdr_price_flag(p_header_rec);
4404 END IF;
4405 --PIB
4406
4407 -- Check if booking failed for the last order in the batch
4408 IF l_book_failed THEN
4409 Unbook_Order(p_header_index => header_counter
4410 ,p_last_line_index => l_last_line_index
4411 ,p_line_rec => p_line_rec
4412 );
4413 l_book_failed := FALSE;
4414 END IF;
4415
4416 EXCEPTION
4417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4418 IF l_debug_level > 0 THEN
4419 oe_debug_pub.add( 'UNEXP ERROR , LINE.ENTITY' ) ;
4420 END IF;
4421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4422 WHEN OTHERS THEN
4423 IF l_debug_level > 0 THEN
4424 oe_debug_pub.add( 'OTHERS ERROR , LINE.ENTITY' ) ;
4425 oe_debug_pub.add( SUBSTR ( SQLERRM , 1 , 240 ) ) ;
4426 END IF;
4427 IF OE_BULK_MSG_PUB.Check_Msg_Level(OE_BULK_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4428 THEN
4429 OE_BULK_MSG_PUB.Add_Exc_Msg
4430 ( G_PKG_NAME
4431 , 'Entity'
4432 );
4433 END IF;
4434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4435 END Entity;
4436
4437 -- HVOP below routine is for Dual control items support INVCONV
4438
4439 PROCEDURE calculate_dual_quantity
4440 (
4441 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
4442 ,p_index IN NUMBER
4443 ,p_dualum_ind IN VARCHAR2
4444 ,p_x_return_status OUT NOCOPY NUMBER
4445 )
4446
4447 IS
4448
4449 l_converted_qty NUMBER(19,9); -- INVCONV
4450 l_return NUMBER;
4451 l_status VARCHAR2(1);
4452 l_msg_count NUMBER;
4453 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4454 UOM_CONVERSION_FAILED EXCEPTION;
4455
4456 l_buffer VARCHAR2(2000); -- INVCONV
4457 TOLERANCE_ERROR EXCEPTION; -- INVCONV
4458
4459
4460
4461
4462 BEGIN
4463
4464
4465 IF l_debug_level > 0 THEN
4466 oe_debug_pub.add ('In calculate_dual_quantity', 3);
4467 END IF;
4468
4469 /* If neither quantity is present, then error as only called for a process type 1,2,or 3 ite m */
4470
4471 IF (p_line_rec.ordered_quantity(p_index) IS NULL OR
4472 p_line_rec.ordered_quantity(p_index) = FND_API.G_MISS_NUM ) AND
4473 (p_line_rec.ordered_quantity2(p_index) IS NULL OR
4474 p_line_rec.ordered_quantity2(p_index) = FND_API.G_MISS_NUM ) THEN
4475 p_x_return_status := -1;
4476 IF l_debug_level > 0 THEN
4477 oe_debug_pub.add ('calculate_dual_qty- both quantities empty so error ', 3);
4478 END IF;
4479 RETURN;
4480 END IF;
4481
4482
4483 /* If quantity2 is present and negative, then error */
4484
4485 IF nvl(p_line_rec.ordered_quantity2(p_index), 0) < 0
4486 THEN
4487 FND_MESSAGE.SET_NAME('ONT','SO_PR_NEGATIVE_AMOUNT'); -- HVOP define better OM or GMI error code
4488 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
4489 p_x_return_status := -1;
4490 IF l_debug_level > 0 THEN
4491 oe_debug_pub.add ('calculate_dual_qty- quantity2 negative so error ', 3);
4492 END IF;
4493 RETURN;
4494 END IF;
4495 -- INVCONV check for valid warehouse/item combo PAL
4496 IF nvl(p_line_rec.inventory_item_id(p_index),-99) <> -99 AND
4497 p_line_rec.ship_from_org_id(p_index) IS NOT NULL
4498 THEN
4499
4500 IF NOT Validate_Item_Warehouse
4501 (p_line_rec.inventory_item_id(p_index),
4502 p_line_rec.ship_from_org_id(p_index),
4503 p_line_rec.item_type_code(p_index),
4504 p_line_rec.line_id(p_index),
4505 p_line_rec.top_model_line_id(p_index),
4506 NULL ,--p_line_rec.source_document_type_id(p_index),
4507 'ORDER')
4508 THEN
4509 IF l_debug_level > 0 THEN
4510 oe_debug_pub.add( 'calculate_dual_qty - invalid warehouse/item combo' ) ;
4511 END IF;
4512 p_x_return_status := -1;
4513 FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_ITEM_WHSE');
4514 OE_BULK_MSG_PUB.add('Y','ERROR');
4515 RETURN;
4516 END IF;
4517
4518 END IF;
4519
4520
4521
4522
4523
4524 /*IF l_debug_level > 0 THEN
4525 oe_debug_pub.add('cached dualum_ind is ' || p_dualum_ind ,3 );
4526 oe_debug_pub.add('input qty is ' || p_line_rec.ordered_quantity(p_index) , 3 );
4527 oe_debug_pub.add('input qty2 is ' || p_line_rec.ordered_quantity2(p_index), 3 );
4528 oe_debug_pub.add('input uom is ' || p_line_rec.order_quantity_uom(p_index), 3);
4529 oe_debug_pub.add('input uom2 is ' || p_line_rec.ordered_quantity_uom2(p_index) , 3);
4530 END IF; */
4531
4532
4533 IF p_dualum_ind = 'F' THEN
4534 IF (NVL(p_line_rec.ordered_quantity2(p_index),0) = 0 ) THEN
4535 IF l_debug_level > 0 THEN
4536 oe_debug_pub.add('Calculate_dual_quantity : quantity2 is null and type 1 (D) so calculate it', 3);
4537 END IF;
4538
4539 /*p_line_rec.ordered_quantity2(p_index) := GMI_Reservation_Util.get_opm_converted_qty(
4540 p_apps_item_id => p_line_rec.inventory_item_id(p_index),
4541 p_organization_id => p_line_rec.ship_from_org_id(p_index),
4542 p_apps_from_uom => p_line_rec.order_quantity_uom(p_index) ,
4543 p_apps_to_uom => p_line_rec.ordered_quantity_uom2(p_index) ,
4544 p_original_qty => p_line_rec.ordered_quantity(p_index) ); */
4545
4546 l_converted_qty := INV_CONVERT.INV_UM_CONVERT(p_line_rec.inventory_item_id(p_index)-- INVCONV
4547 ,NULL -- p_lot_number -- INVCONV
4548 ,p_line_rec.ship_from_org_id(p_index) -- INVCONV
4549 ,5 --NULL
4550 ,p_line_rec.ordered_quantity(p_index)
4551 ,p_line_rec.order_quantity_uom(p_index)
4552 ,p_line_rec.ordered_quantity_uom2(p_index)
4553 ,NULL -- From uom name
4554 ,NULL -- To uom name
4555 );
4556 IF l_debug_level > 0 THEN
4557 oe_debug_pub.add('Calculate_dual_quantity : quantity2 '|| l_converted_qty ,3 );
4558 END IF;
4559
4560 p_line_rec.ordered_quantity2(p_index) := l_converted_qty;
4561
4562 IF (p_line_rec.ordered_quantity2(p_index) < 0) THEN
4563 raise UOM_CONVERSION_FAILED;
4564 END IF;
4565
4566
4567 ELSIF (NVL(p_line_rec.ordered_quantity(p_index) ,0) = 0 ) THEN
4568 IF l_debug_level > 0 THEN
4569 oe_debug_pub.add('Calculate_dual_quantity : quantity is null and type 1(F) so calculate it', 3);
4570 END IF;
4571
4572 /* p_line_rec.ordered_quantity(p_index) := GMI_Reservation_Util.get_opm_converted_qty(
4573 p_apps_item_id => p_line_rec.inventory_item_id(p_index),
4574 p_organization_id => p_line_rec.ship_from_org_id(p_index),
4575 p_apps_from_uom => p_line_rec.ordered_quantity_uom2(p_index),
4576 p_apps_to_uom => p_line_rec.order_quantity_uom(p_index),
4577 p_original_qty => p_line_rec.ordered_quantity2(p_index)); */
4578
4579 l_converted_qty := INV_CONVERT.INV_UM_CONVERT(p_line_rec.inventory_item_id(p_index)-- INVCONV
4580 ,NULL -- p_lot_number -- INVCONV
4581 ,p_line_rec.ship_from_org_id(p_index) -- INVCONV
4582 ,5 --NULL
4583 ,p_line_rec.ordered_quantity2(p_index)
4584 ,p_line_rec.ordered_quantity_uom2(p_index)
4585 ,p_line_rec.order_quantity_uom(p_index)
4586 ,NULL -- From uom name
4587 ,NULL -- To uom name
4588 );
4589
4590 p_line_rec.ordered_quantity(p_index) := l_converted_qty;
4591 IF l_debug_level > 0 THEN
4592 oe_debug_pub.add('Calculate_dual_quantity : quantity '||p_line_rec.ordered_quantity(p_index), 3);
4593 END IF;
4594 IF (p_line_rec.ordered_quantity(p_index) < 0) THEN
4595 raise UOM_CONVERSION_FAILED;
4596 END IF;
4597
4598 END IF; --(NVL(p_line_rec.ordered_quantity2(p_index),0) = 0
4599
4600
4601
4602
4603 ELSIF ( p_dualum_ind = 'D') THEN
4604
4605 IF (NVL(p_line_rec.ordered_quantity2(p_index),0) <> 0 )
4606 AND (NVL(p_line_rec.ordered_quantity(p_index),0) <> 0 ) THEN
4607 /* check the deviation and error out */
4608 IF l_debug_level > 0 THEN
4609 oe_debug_pub.add('Calculate_dual_quantity : check the deviation 1 and error out if necc ', 3);
4610 END IF;
4611 l_return := INV_CONVERT.Within_Deviation -- INVCONV
4612 ( p_organization_id =>
4613 p_line_rec.ship_from_org_id(p_index)
4614 , p_inventory_item_id =>
4615 p_line_rec.inventory_item_id(p_index)
4616 , p_lot_number => NULL -- p_lot_number -- INVCONV
4617 , p_precision => 5
4618 , p_quantity => p_line_rec.ordered_quantity(p_index)
4619 , p_uom_code1 => p_line_rec.order_quantity_uom(p_index)
4620 , p_quantity2 => p_line_rec.ordered_quantity2(p_index)
4621 , p_uom_code2 => p_line_rec.ordered_quantity_uom2(p_index)
4622 );
4623
4624 IF l_return = 0
4625 then
4626 IF l_debug_level > 0 THEN
4627 oe_debug_pub.add('Calculate_dual_quantity - tolerance error 3' ,1);
4628 END IF;
4629
4630 l_buffer := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, -- INVCONV
4631 p_encoded => 'F');
4632 p_x_return_status := -1;
4633 oe_msg_pub.add_text(p_message_text => l_buffer);
4634 --fnd_message.set_name('ONT',l_buffer); -- PAL
4635 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
4636 /*IF l_debug_level > 0 THEN
4637 oe_debug_pub.add(l_buffer,1);
4638 END IF; */
4639 RAISE TOLERANCE_ERROR;
4640 else
4641 IF l_debug_level > 0 THEN
4642 oe_debug_pub.add('Calculate_dual_quantity - No tolerance error ',1);
4643 END IF;
4644 END IF; -- IF l_return = 0
4645
4646
4647 /*l_return := GMICVAL.dev_validation(p_opm_item_id
4648 ,0
4649 ,p_line_rec.ordered_quantity(p_index)
4650 ,p_opm_item_um
4651 ,p_line_rec.ordered_quantity2(p_index)
4652 ,p_opm_item_um2
4653 ,0);
4654 IF (l_return = -68 ) THEN
4655 p_x_return_status := -1;
4656 FND_MESSAGE.set_name('GMI','IC_DEVIATION_HI_ERR');
4657 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
4658 ELSIF(l_return = -69 ) THEN
4659 p_x_return_status := -1;
4660 FND_MESSAGE.set_name('GMI','IC_DEVIATION_HI_ERR');
4661 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
4662 END IF; */
4663
4664 END IF; -- (NVL(p_line_rec.ordered_quantity2(p_index),0) <> 0 )
4665
4666 IF (NVL(p_line_rec.ordered_quantity2(p_index),0) = 0 )
4667 AND (NVL(p_line_rec.ordered_quantity(p_index),0) <> 0 ) THEN
4668 IF l_debug_level > 0 THEN
4669 oe_debug_pub.add('Calculate_dual_quantity : quantity2 is null and type 2(D default) so calculate it', 3);
4670 END IF;
4671 l_converted_qty := INV_CONVERT.INV_UM_CONVERT(p_line_rec.inventory_item_id(p_index)-- INVCONV
4672 ,NULL -- p_lot_number -- INVCONV
4673 ,p_line_rec.ship_from_org_id(p_index) -- INVCONV
4674 ,5 --NULL
4675 ,p_line_rec.ordered_quantity(p_index)
4676 ,p_line_rec.order_quantity_uom(p_index)
4677 ,p_line_rec.ordered_quantity_uom2(p_index)
4678 ,NULL -- From uom name
4679 ,NULL -- To uom name
4680 );
4681 IF l_debug_level > 0 THEN
4682 oe_debug_pub.add('Calculate_dual_quantity : quantity2 '|| l_converted_qty ,3 );
4683 END IF;
4684
4685 p_line_rec.ordered_quantity2(p_index) := l_converted_qty;
4686
4687 IF (p_line_rec.ordered_quantity2(p_index) < 0) THEN
4688 raise UOM_CONVERSION_FAILED;
4689 END IF;
4690
4691 /*p_line_rec.ordered_quantity2(p_index) := GMI_Reservation_Util.get_opm_converted_qty(
4692 p_apps_item_id => p_line_rec.inventory_item_id(p_index),
4693 p_organization_id => p_line_rec.ship_from_org_id(p_index),
4694 p_apps_from_uom => p_line_rec.order_quantity_uom(p_index) ,
4695 p_apps_to_uom => p_line_rec.ordered_quantity_uom2(p_index) ,
4696 p_original_qty => p_line_rec.ordered_quantity(p_index) );
4697 IF l_debug_level > 0 THEN
4698 oe_debug_pub.add('OPM Calculate_dual_quantity : quantity2 '||p_line_rec.ordered_quantity2(p_index),3 );
4699 END IF;
4700 IF (p_line_rec.ordered_quantity2(p_index) < 0) THEN
4701 raise UOM_CONVERSION_FAILED;
4702 END IF; */
4703
4704 ELSIF (NVL(p_line_rec.ordered_quantity(p_index) ,0) = 0 ) THEN
4705 IF l_debug_level > 0 THEN
4706 oe_debug_pub.add('Calculate_dual_quantity : quantity is null and type 2 (D) so calculate it', 3);
4707 END IF;
4708
4709
4710 l_converted_qty := INV_CONVERT.INV_UM_CONVERT(p_line_rec.inventory_item_id(p_index)-- INVCONV
4711 ,NULL -- p_lot_number -- INVCONV
4712 ,p_line_rec.ship_from_org_id(p_index) -- INVCONV
4713 ,5 --NULL
4714 ,p_line_rec.ordered_quantity2(p_index)
4715 ,p_line_rec.ordered_quantity_uom2(p_index)
4716 ,p_line_rec.order_quantity_uom(p_index)
4717 ,NULL -- From uom name
4718 ,NULL -- To uom name
4719 );
4720
4721 p_line_rec.ordered_quantity(p_index) := l_converted_qty;
4722 IF l_debug_level > 0 THEN
4723 oe_debug_pub.add('Calculate_dual_quantity : quantity '||p_line_rec.ordered_quantity(p_index), 3);
4724 END IF;
4725 IF (p_line_rec.ordered_quantity(p_index) < 0) THEN
4726 raise UOM_CONVERSION_FAILED;
4727 END IF;
4728
4729 /*p_line_rec.ordered_quantity(p_index) := GMI_Reservation_Util.get_opm_converted_qty(
4730 p_apps_item_id => p_line_rec.inventory_item_id(p_index),
4731 p_organization_id => p_line_rec.ship_from_org_id(p_index),
4732 p_apps_from_uom => p_line_rec.ordered_quantity_uom2(p_index),
4733 p_apps_to_uom => p_line_rec.order_quantity_uom(p_index),
4734 p_original_qty => p_line_rec.ordered_quantity2(p_index));
4735 IF l_debug_level > 0 THEN
4736 oe_debug_pub.add('OPM Calculate_dual_quantity : quantity '||p_line_rec.ordered_quantity(p_index), 3);
4737 END IF;
4738 IF (p_line_rec.ordered_quantity(p_index) < 0) THEN
4739 raise UOM_CONVERSION_FAILED;
4740 END IF; */
4741
4742
4743
4744 END IF; -- (NVL(p_line_rec.ordered_quantity2(p_index),0) = 0 )
4745
4746 -- No default
4747 ELSIF ( p_dualum_ind = 'N') THEN
4748
4749 IF (NVL(p_line_rec.ordered_quantity2(p_index),0) <> 0 )
4750 AND (NVL(p_line_rec.ordered_quantity(p_index),0) <> 0 ) THEN
4751 /* check the deviation and error out */
4752 /*l_return := GMICVAL.dev_validation(p_opm_item_id INVCONV
4753 ,0
4754 ,p_line_rec.ordered_quantity(p_index)
4755 ,p_opm_item_um
4756 ,p_line_rec.ordered_quantity2(p_index)
4757 ,p_opm_item_um2
4758 ,0);
4759 IF (l_return = -68 ) THEN
4760 p_x_return_status := -1;
4761 FND_MESSAGE.set_name('GMI','IC_DEVIATION_HI_ERR');
4762 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
4763 ELSIF(l_return = -69 ) THEN
4764 p_x_return_status := -1;
4765 FND_MESSAGE.set_name('GMI','IC_DEVIATION_HI_ERR');
4766 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
4767 END IF; */
4768
4769 IF l_debug_level > 0 THEN
4770 oe_debug_pub.add('Calculate_dual_quantity : check the deviation 2 and error out if necc ', 3);
4771 END IF;
4772 l_return := INV_CONVERT.Within_Deviation -- INVCONV
4773 ( p_organization_id =>
4774 p_line_rec.ship_from_org_id(p_index)
4775 , p_inventory_item_id =>
4776 p_line_rec.inventory_item_id(p_index)
4777 , p_lot_number => NULL -- p_lot_number -- INVCONV
4778 , p_precision => 5
4779 , p_quantity => p_line_rec.ordered_quantity(p_index)
4780 , p_uom_code1 => p_line_rec.order_quantity_uom(p_index)
4781 , p_quantity2 => p_line_rec.ordered_quantity2(p_index)
4782 , p_uom_code2 => p_line_rec.ordered_quantity_uom2(p_index)
4783 );
4784
4785 IF l_return = 0
4786 then
4787 IF l_debug_level > 0 THEN
4788 oe_debug_pub.add('Calculate_dual_quantity - tolerance error 3' ,1);
4789 END IF;
4790
4791 l_buffer := FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST, -- INVCONV
4792 p_encoded => 'F');
4793 p_x_return_status := -1;
4794 oe_msg_pub.add_text(p_message_text => l_buffer);
4795 --fnd_message.set_name('ONT',l_buffer);
4796 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
4797
4798 IF l_debug_level > 0 THEN
4799 oe_debug_pub.add(l_buffer,1);
4800 END IF;
4801 RAISE TOLERANCE_ERROR;
4802 else
4803 IF l_debug_level > 0 THEN
4804 oe_debug_pub.add('Calculate_dual_quantity - No tolerance error ',1);
4805 END IF;
4806 END IF; -- IF l_return = 0
4807
4808
4809 END IF; -- (NVL(p_line_rec.ordered_quantity2(p_index),0) <> 0 )
4810
4811 IF (NVL(p_line_rec.ordered_quantity2(p_index),0) = 0 )
4812 OR (NVL(p_line_rec.ordered_quantity(p_index),0) = 0 ) THEN
4813 p_x_return_status := -1;
4814 FND_MESSAGE.set_name('ONT','OE_BULK_OPM_NULL_QTY'); --PROCESS HVOP
4815 OE_BULK_MSG_PUB.Add;
4816
4817 END IF;
4818
4819
4820 END IF; -- IF p_dualum_ind = 1
4821
4822 IF l_debug_level > 0 THEN
4823 oe_debug_pub.add ('end of calculate_dual_quantity', 3);
4824 END IF;
4825
4826
4827 EXCEPTION
4828
4829 WHEN UOM_CONVERSION_FAILED THEN
4830 oe_debug_pub.add('Exception handling: UOM_CONVERSION_FAILED in calculate_dual_qauntity', 1);
4831 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR'); -- INVCONV
4832 OE_BULK_MSG_PUB.Add('Y', 'ERROR');
4833
4834 RAISE FND_API.G_EXC_ERROR;
4835
4836 WHEN TOLERANCE_ERROR THEN -- INVCONV
4837 oe_debug_pub.add('Exception handling: TOLERANCE_ERROR in calculate_dual_qty', 1);
4838 p_x_return_status := -1;
4839 --RAISE -- FND_API.G_EXC_ERROR; -- INVCONV
4840
4841
4842 WHEN OTHERS THEN
4843
4844 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4845 THEN
4846 OE_MSG_PUB.Add_Exc_Msg
4847 ( G_PKG_NAME ,
4848 'Calculate_dual_quantity'
4849 );
4850 END IF;
4851 oe_debug_pub.add('Exception handling: others in calculate_dual_qty', 1);
4852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4853
4854
4855
4856 END calculate_dual_quantity;
4857
4858 /*----------------------------------------------------------
4859 FUNCTION Get_Preferred_Grade for HVOP REMOVED FOR INVCONV
4860 -----------------------------------------------------------*/
4861
4862 /*FUNCTION Get_Preferred_Grade
4863 (
4864 p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
4865 ,p_index IN NUMBER
4866 ,p_opm_item_id IN NUMBER
4867 )
4868
4869 RETURN VARCHAR2
4870 IS
4871 l_preferred_grade VARCHAR2(4) := NULL;
4872
4873
4874 CURSOR C_GRADE1 IS
4875 SELECT alot.prefqc_grade
4876 FROM op_alot_prm alot, ic_item_mst item, op_cust_mst cust
4877 WHERE item.item_id = p_opm_item_id
4878 and alot.cust_id = cust.cust_id
4879 and item.alloc_class = alot.alloc_class
4880 and alot.delete_mark = 0
4881 and cust.of_ship_to_site_use_id = p_line_rec.ship_to_org_id(p_index);
4882
4883 CURSOR C_GRADE2 IS
4884 SELECT alot.prefqc_grade
4885 FROM op_alot_prm alot, ic_item_mst item
4886 WHERE item.item_id = p_opm_item_id
4887 and alot.cust_id IS NULL
4888 and item.alloc_class = alot.alloc_class
4889 and alot.delete_mark = 0;
4890 BEGIN
4891
4892 OPEN C_GRADE1;
4893 FETCH C_GRADE1 into l_preferred_grade;
4894 IF (C_GRADE1%NOTFOUND) THEN
4895 CLOSE C_GRADE1;
4896 OPEN C_GRADE2;
4897 FETCH C_GRADE2 into l_preferred_grade;
4898 IF (C_GRADE2%NOTFOUND) THEN
4899 CLOSE C_GRADE2;
4900 RETURN NULL;
4901 END IF;
4902 END IF;
4903
4904 RETURN l_preferred_grade;
4905
4906 EXCEPTION
4907
4908
4909 WHEN OTHERS THEN
4910 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4911 THEN
4912 OE_MSG_PUB.Add_Exc_Msg
4913 ( G_PKG_NAME ,
4914 'Get_Preferred_Grade'
4915 );
4916 END IF;
4917 oe_debug_pub.add('others in get_preferred_grade', 1);
4918 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4919
4920
4921 END Get_Preferred_Grade; */
4922
4923 PROCEDURE Load_Cust_Trx_Type_Id
4924 ( p_line_index IN NUMBER
4925 ,p_line_rec IN OUT NOCOPY OE_WSH_BULK_GRP.LINE_REC_TYPE
4926 ,p_header_index IN NUMBER
4927 ,p_header_rec IN OUT NOCOPY OE_BULK_ORDER_PVT.HEADER_REC_TYPE)
4928 IS
4929 l_index NUMBER;
4930 BEGIN
4931
4932 l_index :=
4933 OE_BULK_CACHE.Load_Line_Type(p_line_rec.line_type_id(p_line_index));
4934
4935 IF OE_BULK_CACHE.G_LINE_TYPE_TBL(l_index).cust_trx_type_id IS NULL THEN
4936 -- Line type is null so get value from order type
4937
4938 l_index := OE_BULK_CACHE.Load_Order_Type(p_header_rec.order_type_id(p_header_index));
4939
4940
4941 IF OE_BULK_CACHE.G_ORDER_TYPE_TBL(l_index).cust_trx_type_id IS NULL THEN
4942 -- Get info from profile value
4943
4944 IF G_INV_TRN_TYPE_ID IS NULL THEN
4945 G_INV_TRN_TYPE_ID := FND_PROFILE.VALUE('OE_INVOICE_TRANSACTION_TYPE_ID');
4946
4947 SELECT tax_calculation_flag
4948 INTO G_INV_TAX_CALC_FLAG
4949 FROM ra_cust_trx_types
4950 WHERE cust_trx_type_id = G_INV_TRN_TYPE_ID;
4951
4952 ELSE
4953 p_line_rec.cust_trx_type_id(p_line_index) := G_INV_TRN_TYPE_ID;
4954 p_line_rec.tax_calculation_flag(p_line_index) := G_INV_TAX_CALC_FLAG;
4955 END IF;
4956 ELSE
4957 p_line_rec.cust_trx_type_id(p_line_index) := OE_BULK_CACHE.G_ORDER_TYPE_TBL(l_index).cust_trx_type_id;
4958 p_line_rec.tax_calculation_flag(p_line_index) := OE_BULK_CACHE.G_ORDER_TYPE_TBL(l_index).tax_calculation_flag;
4959 END IF;
4960
4961 ELSE
4962
4963 -- Take values from line type
4964 p_line_rec.cust_trx_type_id(p_line_index) := OE_BULK_CACHE.G_LINE_TYPE_TBL(l_index).cust_trx_type_id;
4965 p_line_rec.tax_calculation_flag(p_line_index) := OE_BULK_CACHE.G_LINE_TYPE_TBL(l_index).tax_calculation_flag;
4966
4967 END IF;
4968
4969 EXCEPTION
4970
4971
4972 WHEN OTHERS THEN
4973 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4974 THEN
4975 OE_MSG_PUB.Add_Exc_Msg
4976 ( G_PKG_NAME ,
4977 'Load_Cust_Trx_Type_Id'
4978 );
4979 END IF;
4980 oe_debug_pub.add('others in Load_Cust_Trx_type_Id', 1);
4981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4982 END Load_Cust_Trx_Type_Id;
4983
4984 END OE_BULK_PROCESS_LINE;