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