[Home] [Help]
PACKAGE BODY: APPS.OE_INV_IFACE_PVT
Source
1 PACKAGE BODY OE_Inv_Iface_PVT AS
2 /* $Header: OEXVIIFB.pls 120.14 2012/02/10 03:53:32 rahujain ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Inv_Iface_PVT';
7
8 TYPE Profile_type IS RECORD
9 ( oe_source_code VARCHAR2(240)
10 , user_id NUMBER
11 , login_id NUMBER
12 , request_id NUMBER
13 , application_id NUMBER
14 , program_id NUMBER);
15
16 profile_values Profile_type;
17
18 -- Start of Comments
19 -- API name OE_Inv_Iface_PVT
20 -- Type Private
21 -- Version Current version = 1.0
22 -- Initial version = 1.0
23
24 PROCEDURE Inventory_Interface
25 (
26 p_line_id IN NUMBER
27 , x_return_status OUT NOCOPY VARCHAR2
28
29 , x_result_out OUT NOCOPY VARCHAR2
30
31 )
32 IS
33 l_return_status VARCHAR2(30);
34 l_line_rec OE_Order_Pub.Line_Rec_Type;
35 l_sales_order_id NUMBER;
36 l_msg_count NUMBER;
37 l_msg_data VARCHAR2(4000);
38 l_rsv_rec INV_RESERVATION_GLOBAL.mtl_reservation_rec_type;
39 l_rsv_tbl INV_RESERVATION_GLOBAL.mtl_reservation_tbl_type;
40 l_count NUMBER;
41 l_x_error_code NUMBER;
42 l_lock_records VARCHAR2(1);
43 l_sort_by_req_date NUMBER;
44 l_concat_segs VARCHAR2(2000);
45 l_concat_ids VARCHAR2(2000);
46 l_concat_descrs VARCHAR2(2000);
47 l_trans_acc NUMBER;
48 l_transaction_header_id NUMBER;
49 l_source_line_id NUMBER;
50 l_revision_code NUMBER;
51 l_lot_code NUMBER;
52 l_serial_code NUMBER;
53 l_subinventory VARCHAR2(10);
54 l_order_number NUMBER;
55 l_order_type_name VARCHAR2(200);
56 reservation_flag VARCHAR2(1) := 'N';
57 l_hold_result_out VARCHAR2(30);
58 l_hold_return_status VARCHAR2(30);
59 l_hold_msg_count NUMBER;
60 l_hold_msg_data VARCHAR2(240);
61 l_transaction_reference NUMBER;
62 l_transaction_interface_id NUMBER;
63 l_remained_qty NUMBER;
64 l_remained_qty2 NUMBER; -- INVCONV
65 l_lot VARCHAR2(1);
66 l_revision VARCHAR2(1);
67 l_locator NUMBER;
68 l_stock_locator_control_code NUMBER;
69 l_locator_type NUMBER;
70 l_location_control_code NUMBER;
71 l_transactable_flag VARCHAR2(1);
72 l_index NUMBER;
73 l_ordered_date DATE;
74 -- Process Order arguments
75
76 -- l_control_rec OE_GLOBALS.control_rec_type;
77 l_line_tbl OE_ORDER_PUB.line_tbl_type;
78 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
79 /*
80 l_header_rec OE_Order_PUB.Header_Rec_Type;
81 l_new_line_rec OE_Order_PUB.Line_Rec_Type := OE_Order_Pub.G_MISS_LINE_REC;
82 l_new_line_tbl OE_Order_PUB.Line_Tbl_Type;
83 l_header_adj_out_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
84 l_header_scredit_out_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
85 l_line_adj_out_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
86 l_line_scredit_out_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
87 l_lot_serial_out_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
88 l_action_request_out_tbl OE_Order_PUB.Request_Tbl_Type;
89 l_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
90 l_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
91 l_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
92 l_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
93 l_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
94 l_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
95 */
96
97 /* -- HW OPM BUG#:2536589 added variable to hold item_rec information
98 l_item_rec OE_ORDER_CACHE.item_rec_type;
99 l_process_org NUMBER;
100 opm_msg_count NUMBER;
101 opm_msg_data VARCHAR2(100);
102 opm_lot_id NUMBER ;
103 x_reservation_id NUMBER;
104
105 -- HW end of chanegs for 2536589 */
106 --
107 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
108 l_trx_date_for_inv_iface DATE; --bug5897965
109 --10295156
110 l_qty_to_reserve NUMBER;
111 l_primary_uom VARCHAR2(3);
112
113 --ER 8419373 Start
114 l_serial VARCHAR2(1);
115 l_serial_number_tbl INV_RESERVATION_GLOBAL.RSV_SERIAL_NUMBER_TABLE;
116 l_serial_tbl_count NUMBER;
117 l_input_serial_number_tbl INV_RESERVATION_GLOBAL.RSV_SERIAL_NUMBER_TABLE; --always empty
118 l_serial_transaction_temp_id NUMBER;
119 l_rsv_serial_count NUMBER; --Bug 13690325
120 --ER 8419373 End
121
122 BEGIN
123
124 SAVEPOINT INVENTORY_INTERFACE;
125
126 IF l_debug_level > 0 THEN
127 oe_debug_pub.add( 'INV IFACE: ENTERING INVENTORY INTERFACE' , 1 ) ;
128 END IF;
129 x_return_status := FND_API.G_RET_STS_SUCCESS;
130 x_result_out := OE_GLOBALS.G_WFR_COMPLETE;
131
132 profile_values.oe_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
133 profile_values.user_id := FND_GLOBAL.USER_ID;
134 profile_values.login_id := FND_GLOBAL.LOGIN_ID;
135 profile_values.request_id := 0;
136 profile_values.application_id := 0;
137 profile_values.program_id := 0;
138
139 OE_MSG_PUB.set_msg_context(
140 p_entity_code => 'LINE'
141 ,p_entity_id => p_line_id
142 ,p_line_id => p_line_id);
143
144 /* check for holds */
145 OE_HOLDS_PUB.CHECK_HOLDS(p_api_version => 1.0,
146 p_line_id => p_line_id,
147 p_wf_item => OE_GLOBALS.G_WFI_LIN,
148 p_wf_activity => 'INVENTORY_INTERFACE',
149 x_result_out => l_hold_result_out,
150 x_return_status => l_hold_return_status,
151 x_msg_count => l_hold_msg_count,
152 x_msg_data => l_hold_msg_data);
153
154 IF ( l_hold_return_status = FND_API.G_RET_STS_SUCCESS AND
155 l_hold_result_out = FND_API.G_TRUE ) THEN
156 /* we are reusing the OE_INVOICING_HOLD message here,
157 the message is generic, not invoicing specific */
158
159 FND_MESSAGE.SET_NAME('ONT','OE_INVOICING_HOLD');
160 OE_MSG_PUB.ADD;
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 x_result_out := OE_GLOBALS.G_WFR_ON_HOLD;
163 IF l_debug_level > 0 THEN
164 oe_debug_pub.add( 'INV IFACE: ACTIVITY ON HOLD , EXITING' , 5 ) ;
165 END IF;
166 RETURN;
167 ELSIF l_hold_return_status <> FND_API.G_RET_STS_SUCCESS THEN
168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169 IF l_debug_level > 0 THEN
170 oe_debug_pub.add( 'INV IFACE: CHECK HOLD API ERROR' , 5 ) ;
171 END IF;
172 RETURN;
173 END IF;
174
175 /* Query up the line rec */
176
177 OE_Line_Util.Lock_Row(p_line_id=>p_line_id
178 , p_x_line_rec => l_line_rec
179 , x_return_status => l_return_status
180 );
181 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
182 IF l_debug_level > 0 THEN
183 oe_debug_pub.add( 'INV IFACE: LOCK_ROW FAILED' , 5 ) ;
184 END IF;
185 x_return_status := FND_API.G_RET_STS_ERROR;
186 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
187 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
188 IF l_debug_level > 0 THEN
189 oe_debug_pub.add( 'INV IFACE: LOCK_ROW FAILED' , 5 ) ;
190 END IF;
191 x_return_status := FND_API.G_RET_STS_ERROR;
192 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
193 END IF;
194
195 OE_MSG_PUB.update_msg_context(
196 p_header_id => l_line_rec.header_id
197 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
198 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
199 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
200 ,p_change_sequence => l_line_rec.change_sequence
201 ,p_source_document_id => l_line_rec.source_document_id
202 ,p_source_document_line_id => l_line_rec.source_document_line_id
203 ,p_order_source_id => l_line_rec.order_source_id
204 ,p_source_document_type_id => l_line_rec.source_document_type_id);
205
206 IF l_debug_level > 0 THEN
207 oe_debug_pub.add( 'INV IFACE: LOCK_ROW COMPLETED' , 5 ) ;
208 END IF;
209
210
211 SELECT MTL_TRANSACTIONS_ENABLED_FLAG
212 INTO l_transactable_flag
213 FROM MTL_SYSTEM_ITEMS
214 WHERE inventory_item_id = l_line_rec.inventory_item_id
215 AND organization_id = l_line_rec.ship_from_org_id;
216
217 IF l_line_rec.shippable_flag = 'Y' THEN
218 IF nvl(l_line_rec.source_type_code, OE_GLOBALS.G_SOURCE_EXTERNAL)
219 = OE_GLOBALS.G_SOURCE_EXTERNAL OR
220 l_transactable_flag <> 'Y' THEN
221 x_return_status := FND_API.G_RET_STS_SUCCESS;
222 x_result_out := OE_GLOBALS.G_WFR_NOT_ELIGIBLE;
223 IF l_debug_level > 0 THEN
224 oe_debug_pub.add( 'INV IFACE: INV IFACE NOT ELIGIBLE - EXTERNAL OR NON TRANSACTABLE' , 5 ) ;
225 END IF;
226 RETURN;
227 END IF;
228
229 IF l_line_rec.ship_from_org_id is null THEN
230 IF l_debug_level > 0 THEN
231 oe_debug_pub.add( 'INV IFACE: INV IFACE INCOMPLETE - NO WAREHOUSE' , 5 ) ;
232 END IF;
233 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_NO_WAREHOUSE');
234 OE_MSG_PUB.ADD;
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
237 RETURN;
238 END IF;
239
240 /* get order info */
241 SELECT /* MOAC_SQL_CHANGE */ h.order_number,ot.name,h.ordered_date
242 INTO l_order_number,l_order_type_name,l_ordered_date
243 FROM oe_order_headers_all h, oe_order_types_v ot
244 WHERE h.header_id = l_line_rec.header_id AND
245 ot.order_type_id = h.order_type_id;
246
247 --
248 -- Bug # 4454055
249 -- Code is commented for Deferred Revenue Project
250 -- The COGS account generator workflow will no longer be called at shipping time, instead,
251 -- Inventory will stamp deferred cogs account on MMT transactions.
252 -- The deferred cogs account can be defined at each inventory org level.
253 -- When revenue is recognized, Costing will get notified and call the OM COGS
254 -- account generator to get the cogs account and recognize cogs in the same period where
255 -- revenue is recognized. Also when an order line is closed without getting invoiced,
256 -- cogs will be recognized at the closing time assuming there would be no future revenue recognition event.
257 --
258 -- This code is uncommented as back to get the functionality of 11.5.10 back. This is done in concurrence with the Inv and the Costing team.
259 --
260 -- Start Deferred Revenue Project
261 -- bug5897965, begin
262 -- transaction date for inventory interface will be derived from the system parameter
263 -- New system parameter is - Transaction Date for Inventory Interface Non Ship Process
264 -- It will have the default value of Ordered Date (derived from order header as above)
265 -- Other possible values are Sysdate, Schedule Ship Date from the Order line
266
267 SELECT DECODE(OE_SYS_PARAMETERS.value('TRX_DATE_FOR_INV_IFACE'),
268 'C',SYSDATE,
269 'S',nvl(l_line_rec.schedule_ship_date,SYSDATE),
270 l_ordered_date)
271 INTO l_trx_date_for_inv_iface
272 FROM DUAL;
273
274 IF l_debug_level > 0 THEN
275 OE_DEBUG_PUB.add('Transaction Date derived from system parameter setup is '||l_trx_date_for_inv_iface,1);
276 END IF;
277 --bug5897965, end
278
279 IF OE_FLEX_COGS_PUB.Start_Process (
280 p_api_version_number => 1.0,
281 p_line_id => p_line_id,
282 x_return_ccid => l_trans_acc,
283 x_concat_segs => l_concat_segs,
284 x_concat_ids => l_concat_ids,
285 x_concat_descrs => l_concat_descrs,
286 x_msg_count => l_msg_count,
287 x_msg_data => l_msg_data) <> FND_API.G_RET_STS_SUCCESS
288 THEN
289 -- if COGS workflow fails for some reason,
290 -- we will return INCOMPLETE
291
292 l_trans_acc := NULL;
293 IF l_debug_level > 0 THEN
294 oe_debug_pub.add( 'INV IFACE: COGS FAIL' , 5 ) ;
295 END IF;
296 x_return_status := FND_API.G_RET_STS_ERROR;
297 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
298 RETURN;
299 END IF;
300
301 -- End Deferred Revenue Project
302
303
304 -- if item is under lot/serial/revision control
305 BEGIN
306 SELECT revision_qty_control_code, lot_control_code, serial_number_control_code
307 INTO l_revision_code, l_lot_code, l_serial_code
308 FROM mtl_system_items
309 WHERE inventory_item_id = l_line_rec.inventory_item_id
310 AND organization_id = l_line_rec.ship_from_org_id;
311 EXCEPTION
312 WHEN OTHERS THEN
313 IF l_debug_level > 0 THEN
314 oe_debug_pub.add( 'INV IFACE: REVISION/LOT SELECT FAILURE' , 5 ) ;
315 END IF;
316 x_return_status := FND_API.G_RET_STS_ERROR;
317 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
318 RETURN;
319 END;
320
321 -- After ER 8419373 we support serial numbers
322 IF nvl(l_serial_code, 1) <> 1 THEN
323 IF l_debug_level > 0 THEN
324 oe_debug_pub.add( 'INV IFACE: ITEM UNDER SERIAL CONTORL: ' || l_serial_code , 5 ) ;
325 END IF;
326 /* ER 8419373
327 -- give a message here
328 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_IFACE_SERIAL');
329 OE_MSG_PUB.ADD;
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
332 RETURN;
333 */
334 --Serial items with serial generation Sales Order Issue not supported
335 IF nvl(l_serial_code, 1) = 6 THEN
336 -- give a message here
337 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_IFACE_SER_SO_ISSUE');
338 OE_MSG_PUB.ADD;
339 x_return_status := FND_API.G_RET_STS_ERROR;
340 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
341 RETURN;
342 END IF;
343
344 l_serial := 'Y';
345 END IF;
346
347 IF l_revision_code = 2 THEN
348 IF l_debug_level > 0 THEN
349 oe_debug_pub.add( 'INV IFACE: ITEM UNDER REVISION CONTROL' , 5 ) ;
350 END IF;
351 l_revision := 'Y';
352 END IF;
353
354 IF l_lot_code = 2 THEN
355 IF l_debug_level > 0 THEN
356 oe_debug_pub.add( 'INV IFACE: ITEM UNDER LOT CONTROL' , 5 ) ;
357 END IF;
358 l_lot := 'Y';
359 END IF;
360
361 -- HW OPM BUG#:2536589
362 IF l_lot_code = 1 THEN
363 IF l_debug_level > 0 THEN
364 oe_debug_pub.add( 'INV IFACE: ITEM UNDER NON LOT CONTROL' , 5 ) ;
365 END IF;
366 l_lot := 'N';
367 END IF;
368
369
370 -- we will use this transaction_header_id for all
371 -- interface lines
372 SELECT mtl_material_transactions_s.nextval
373 INTO l_transaction_header_id
374 FROM dual;
375
376 l_transaction_interface_id := l_transaction_header_id;
377
378 l_transaction_reference := l_line_rec.header_id;
379
380
381 /* figure out nocopy reserved_quantity */
382
383 --4504362
384 l_sales_order_id := OE_SCHEDULE_UTIL.Get_mtl_sales_order_id
385 (l_line_rec.header_id);
386
387 -- INVCONV - MERGED CALLS FOR OE_LINE_UTIL.Get_Reserved_Quantity and OE_LINE_UTIL.Get_Reserved_Quantity2
388
389 OE_LINE_UTIL.Get_Reserved_Quantities(p_header_id => l_sales_order_id
390 ,p_line_id => l_line_rec.line_id
391 ,p_org_id => l_line_rec.ship_from_org_id
392 ,x_reserved_quantity => l_line_rec.reserved_quantity
393 ,x_reserved_quantity2 => l_line_rec.reserved_quantity2
394 );
395
396
397
398 /*l_line_rec.reserved_quantity := OE_LINE_UTIL.Get_Reserved_Quantity
399 (p_header_id => l_sales_order_id,
400 p_line_id => l_line_rec.line_id,
401 p_org_id => l_line_rec.ship_from_org_id); */
402 IF l_debug_level > 0 THEN
403 oe_debug_pub.add( 'INV IFACE: RESERVED_QTY = ' || TO_CHAR ( L_LINE_REC.RESERVED_QUANTITY ) , 5 ) ;
404 END IF;
405
406
407 -- INVCONV
408
409 /*l_line_rec.reserved_quantity2 := OE_LINE_UTIL.Get_Reserved_Quantity2
410 (p_header_id => l_sales_order_id,
411 p_line_id => l_line_rec.line_id,
412 p_org_id => l_line_rec.ship_from_org_id); */
413 IF l_debug_level > 0 THEN
414 oe_debug_pub.add( 'INV IFACE: RESERVED_QTY2 = ' || TO_CHAR ( L_LINE_REC.RESERVED_QUANTITY2 ) , 5 ) ;
415 END IF;
416
417
418
419
420 /* --HW OPM BUG#:2536589 Need to initialize variable -- INVCONV NOT NEEDED NOW
421 l_remained_qty := 0;
422
423 -- HW OPM BUG#:2536589 - Check if org is process or discrete
424 IF oe_line_util.Process_Characteristics
425 (l_line_rec.inventory_item_id
426 ,l_line_rec.ship_from_org_id
427 ,l_item_rec) THEN
428 l_process_org := 1;
429 ELSE
430 l_process_org := 0;
431 END IF;
432
433 IF l_debug_level > 0 THEN
434 oe_debug_pub.add( 'VALUE OF L_PROCESS_FLAG IS '||TO_CHAR ( L_PROCESS_ORG ) , 5 ) ;
435 END IF; */
436
437
438 /* -- HW OPM BUG#:2536589 Check if requested_qty < qty_reserved for OPM INVCONV NOT NEEDED NOW
439
440 IF (l_process_org = 1 AND
441 l_line_rec.reserved_quantity > l_line_rec.ordered_quantity ) THEN
442 IF l_debug_level > 0 THEN
443 oe_debug_pub.add( 'INV IFACE FAILED: ORDERED_QTY IS < RESERVED_QTY FOR OPM' , 5 ) ;
444 END IF;
445 x_return_status := FND_API.G_RET_STS_ERROR;
446 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
447 ROLLBACK TO INVENTORY_INTERFACE;
448 RETURN;
449 END IF;
450 -- HW OPM BUG#:2536589 end of changes */
451
452 IF l_line_rec.reserved_quantity > 0 THEN
453 reservation_flag := 'Y';
454 IF l_line_rec.reserved_quantity < l_line_rec.ordered_quantity THEN
455 --10295156
456 -- Check if actually its a partial reservation or the difference is due to the precesion.
457 BEGIN
458 SELECT primary_uom_code
459 INTO l_primary_uom
460 FROM mtl_system_items_b
461 WHERE inventory_item_id = l_line_rec.inventory_item_id
462 AND organization_id = l_line_rec.ship_from_org_id;
463 EXCEPTION
464 WHEN OTHERS THEN
465 IF l_debug_level > 0 THEN
466 OE_DEBUG_PUB.Add('Error in selecting Primary UOM code',1);
467 END IF;
468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
469
470 END;
471 IF NOT OE_GLOBALS.Equal(l_line_rec.order_quantity_uom,
472 l_primary_uom) THEN
473 l_qty_to_reserve := inv_convert.inv_um_convert(
474 item_id => l_line_rec.inventory_item_id
475 , PRECISION => 5
476 , from_quantity => l_line_rec.ordered_quantity - l_line_rec.reserved_quantity
477 , from_unit => l_line_rec.order_quantity_uom
478 , to_unit => l_primary_uom
479 , from_name => NULL -- from uom name
480 , to_name => NULL -- to uom name
481 );
482 IF l_debug_level > 0 THEN
483 OE_DEBUG_PUB.Add('Quantity to reserve after convert '||l_qty_to_reserve,1);
484 END IF;
485 ELSE
486 l_qty_to_reserve := l_line_rec.ordered_quantity - l_line_rec.reserved_quantity;
487 END IF;
488
489 -- partial reservation, we need to interface
490 -- the unreserved qty as well
491 IF l_qty_to_reserve > 0 THEN
492 l_remained_qty := l_line_rec.ordered_quantity - l_line_rec.reserved_quantity;
493 l_remained_qty2 := l_line_rec.ordered_quantity2 - l_line_rec.reserved_quantity2; -- invconv
494 END IF;
495 END IF;
496
497 l_rsv_rec.demand_source_header_id := l_sales_order_id;
498 l_rsv_rec.demand_source_line_id := l_line_rec.line_id;
499 l_rsv_rec.organization_id := l_line_rec.ship_from_org_id;
500
501 IF l_debug_level > 0 THEN
502 oe_debug_pub.add( 'INV IFACE: CALLING INVS QUERY_RESERVATION ' , 1 ) ;
503 END IF;
504 /*ER 8419373
505 inv_reservation_pub.query_reservation
506 ( p_api_version_number => 1.0
507 , p_init_msg_lst => FND_API.G_TRUE
508 , x_return_status => l_return_status
509 , x_msg_count => l_msg_count
510 , x_msg_data => l_msg_data
511 , p_query_input => l_rsv_rec
512 , x_mtl_reservation_tbl => l_rsv_tbl
513 , x_mtl_reservation_tbl_count => l_count
514 , x_error_code => l_x_error_code
515 , p_lock_records => l_lock_records
516 , p_sort_by_req_date => l_sort_by_req_date
517 );*/
518 --ER 8419373, call the overloaded INV API which provides serial number information
519 inv_reservation_pvt.query_reservation (
520 p_api_version_number => 1.0
521 , p_init_msg_lst => FND_API.G_TRUE
522 , x_return_status => l_return_status
523 , x_msg_count => l_msg_count
524 , x_msg_data => l_msg_data
525 , p_query_input => l_rsv_rec
526 , p_lock_records => l_lock_records
527 , p_sort_by_req_date => l_sort_by_req_date
528 --, p_cancel_order_mode IN NUMBER DEFAULT inv_reservation_global.g_cancel_order_no
529 , p_serial_number_table => l_input_serial_number_tbl
530 , x_mtl_reservation_tbl => l_rsv_tbl
531 , x_mtl_reservation_tbl_count => l_count
532 , x_serial_number_table => l_serial_number_tbl
533 , x_serial_number_table_count => l_serial_tbl_count
534 , x_error_code => l_x_error_code
535 );
536
537 IF l_debug_level > 0 THEN
538 oe_debug_pub.add( 'INV IFACE: AFTER CALLING INVS QUERY_RESERVATION: ' || L_RETURN_STATUS , 1 ) ;
539 oe_debug_pub.add( 'RESERVATION COUNT: ' || L_RSV_TBL.COUNT , 1 ) ;
540 oe_debug_pub.add( 'SERIAL TABLE COUNT: ' || l_serial_number_tbl.COUNT , 1 ) ;
541 oe_debug_pub.add( 'SERIAL COUNT: ' || l_serial_tbl_count , 1 ) ;
542 END IF;
543
544 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR OR
545 l_return_status = FND_API.G_RET_STS_ERROR THEN
546 IF l_debug_level > 0 THEN
547 oe_debug_pub.add( 'INV IFACE: QUERY_RESERVATION FAILED' , 5 ) ;
548 END IF;
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
551 RETURN;
552 END IF;
553
554 -- LOOP to insert reservation record to interface table
555
556 FOR I in 1..l_rsv_tbl.count LOOP
557 -- validate the inventory control are being satisfied
558 IF l_revision = 'Y' THEN
559 IF l_rsv_tbl(I).revision is null THEN
560 -- give a message
561 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_IFACE_REVISION');
562 OE_MSG_PUB.ADD;
563 x_return_status := FND_API.G_RET_STS_ERROR;
564 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
565 ROLLBACK TO INVENTORY_INTERFACE;
566 RETURN;
567 END IF;
568 END IF;
569
570 IF l_lot = 'Y' THEN
571
572 -- HW BUG#:2536589 Since OPM doesn't save lot information in the reservation record, we need
573 -- to branch and retrieve lot_id from OPM TRXN table
574 IF ( l_rsv_tbl(I).lot_number is null ) THEN -- INVCONV
575 -- AND l_process_org = 0 ) THEN -- For discrete INVCONV
576 -- give a message
577 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_IFACE_LOT');
578 OE_MSG_PUB.ADD;
579 x_return_status := FND_API.G_RET_STS_ERROR;
580 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
581 ROLLBACK TO INVENTORY_INTERFACE;
582 RETURN;
583 END IF; -- INVCONV
584 END IF; -- of l_lot == 'Y'
585
586 --ER 8419373
587 --if the item is serial controlled then serial number table should not be empty
588 IF l_serial = 'Y' and l_serial_tbl_count = 0 THEN
589 -- give a message
590 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_IFACE_SER');
591 OE_MSG_PUB.ADD;
592 x_return_status := FND_API.G_RET_STS_ERROR;
593 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
594 ROLLBACK TO INVENTORY_INTERFACE;
595 RETURN;
596 END IF; -- of l_serial = 'Y'
597
598 /* -- HW BUG#:2536589, item belongs to OPM and lot control INVCONV
599 ELSIF (l_process_org = 1 AND l_rsv_tbl(I).reservation_quantity <> 0) THEN -- For OPM
600 -- Make sure lot exists and allocated for OPM
601 GMI_RESERVATION_UTIL.FIND_LOT_ID(
602 l_rsv_tbl(I).reservation_id
603 ,l_return_status
604 ,opm_msg_count
605 ,opm_msg_data);
606
607 -- This error is reported if lot_id is not found
608 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
609 IF l_debug_level > 0 THEN
610 oe_debug_pub.add( 'INV IFACE: FAILED TO FETCH LOT_ID INFORMATION FOR OPM TRXN FOR TRANS_ID' || TO_CHAR ( L_RSV_TBL ( I ) .RESERVATION_ID ) , 5 ) ;
611 END IF;
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
614 ROLLBACK TO INVENTORY_INTERFACE;
615 RETURN;
616 END IF; -- of error checking
617 END IF; -- of branching */ --INVCONV
618
619
620
621 /* --HW OPM BUG#:2536589 Item belongs to OPM. This call is for non-inv and INVCONV
622 -- inv opm items
623 IF ( l_process_org = 1 AND
624 l_rsv_tbl(I).reservation_quantity <> 0
625 AND l_remained_qty = 0 ) THEN
626
627 GMI_Reservation_Util.update_opm_trxns(
628 l_rsv_tbl(I).reservation_id
629 ,l_line_rec.inventory_item_id
630 ,l_line_rec.ship_from_org_id
631 ,l_return_status
632 ,opm_msg_count
633 ,opm_msg_data);
634
635 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
636 IF l_debug_level > 0 THEN
637 oe_debug_pub.add( 'INV IFACE: FAILED TO UPDATE OPM TRXNS' , 5 ) ;
638 END IF;
639 x_return_status := FND_API.G_RET_STS_ERROR;
640 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
641 ROLLBACK TO INVENTORY_INTERFACE;
642 RETURN;
643 END IF;
644
645 END IF; -- if process_org */
646 /* handle locator */
647
648 /* -- HW OPM BUG#:2536589 Need to branch since none of the followings are applicable to OPM
649 IF ( l_process_org = 0 ) THEN */-- INVCONV
650
651
652 IF l_rsv_tbl(I).subinventory_code is null THEN
653 -- give a message
654 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_IFACE_RSV_SUB');
655 OE_MSG_PUB.ADD;
656 x_return_status := FND_API.G_RET_STS_ERROR;
657 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
658 ROLLBACK TO INVENTORY_INTERFACE;
659 RETURN;
660 ELSE
661 BEGIN
662 SELECT stock_locator_control_code
663 INTO l_stock_locator_control_code
664 FROM mtl_parameters
665 WHERE organization_id = l_line_rec.ship_from_org_id ;
666
667
668 EXCEPTION
669 WHEN OTHERS THEN
670 IF l_debug_level > 0 THEN
671 oe_debug_pub.add( 'INV IFACE: LOCATOR CONTROL CODE FAILURE' , 5 ) ;
672 END IF;
673 x_return_status := FND_API.G_RET_STS_ERROR;
674 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
675 ROLLBACK TO INVENTORY_INTERFACE;
676 RETURN;
677 END;
678
679 BEGIN
680 SELECT locator_type
681 INTO l_locator_type
682 FROM mtl_secondary_inventories
683 WHERE secondary_inventory_name = l_rsv_tbl(I).subinventory_code
684 AND organization_id = l_line_rec.ship_from_org_id;
685
686 EXCEPTION
687 WHEN OTHERS THEN
688 IF l_debug_level > 0 THEN
689 oe_debug_pub.add( 'INV IFACE: LOCATOR TYPE FAILURE' , 5 ) ;
690 END IF;
691 x_return_status := FND_API.G_RET_STS_ERROR;
692 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
693 ROLLBACK TO INVENTORY_INTERFACE;
694 RETURN;
695 END;
696
697 BEGIN
698 SELECT location_control_code
699 INTO l_location_control_code
700 FROM mtl_system_items
701 WHERE inventory_item_id = l_line_rec.inventory_item_id
702 AND organization_id = l_line_rec.ship_from_org_id;
703 EXCEPTION
704 WHEN OTHERS THEN
705 IF l_debug_level > 0 THEN
706 oe_debug_pub.add( 'INV IFACE: LOCATION CONTROL CODE FAILURE' , 5 ) ;
707 END IF;
708 x_return_status := FND_API.G_RET_STS_ERROR;
709 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
710 ROLLBACK TO INVENTORY_INTERFACE;
711 RETURN;
712 END;
713
714 IF l_debug_level > 0 THEN
715 oe_debug_pub.add( 'INV IFACE: BEFORE LOCATOR_CONTROL API CALL' , 1 ) ;
716 END IF;
717 l_locator := INV_RESERVATION_UTIL_PVT.locator_control(
718 p_org_control => l_stock_locator_control_code
719 ,p_sub_control => l_locator_type
720 ,p_item_control => l_location_control_code);
721 IF l_debug_level > 0 THEN
722 oe_debug_pub.add( 'INV IFACE: AFTER LOCATOR_CONTROL API CALL - ' || TO_CHAR ( L_LOCATOR ) , 1 ) ;
723 END IF;
724
725
726 IF l_locator > 1 THEN -- under locator control
727 IF l_rsv_tbl(I).locator_id is null THEN
728 -- give a message
729 IF l_debug_level > 0 THEN
730 oe_debug_pub.add( 'INV IFACE: ITEM UNDER LOCATOR CONTROL' , 5 ) ;
731 END IF;
732 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_IFACE_LOCATOR');
733 OE_MSG_PUB.ADD;
734 x_return_status := FND_API.G_RET_STS_ERROR;
735 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
736 ROLLBACK TO INVENTORY_INTERFACE;
737 RETURN;
738 END IF;
739 END IF;
740
741 END IF;
742 -- END IF; -- of branch HW BUG#:2535689 INVCONV
743
744 /* -- HW OPM BUG#:2536589 No need to populate these tables for OPM. INVCONV -NOT NEEDED NOW FOR opm iNVENTORY CONVERGENCE
745 -- Added a branch
746 IF ( l_process_org = 0 ) THEN */
747
748 SELECT oe_transactions_iface_s.nextval
749 INTO l_source_line_id
750 FROM dual;
751
752 --ER 8419373 check if lot and serial controlled and get new transaction_interface_id for column SERIAL_TRANSACTION_TEMP_ID
753 IF l_lot = 'Y' AND l_serial = 'Y' THEN
754 SELECT mtl_material_transactions_s.nextval
755 INTO l_serial_transaction_temp_id
756 FROM dual;
757 else
758 l_serial_transaction_temp_id := null;
759 END IF;
760
761 IF l_debug_level > 0 THEN
762 oe_debug_pub.add( 'l_serial_transaction_temp_id: ' || l_serial_transaction_temp_id , 5 ) ;
763 END IF;
764
765 --ER 8419373 END
766
767 IF l_lot = 'Y' THEN
768
769 INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
770 (
771 SOURCE_CODE,
772 SOURCE_LINE_ID,
773 TRANSACTION_INTERFACE_ID,
774 LOT_NUMBER,
775 TRANSACTION_QUANTITY,
776 SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
777 LAST_UPDATE_DATE,
778 LAST_UPDATED_BY,
779 CREATION_DATE,
780 CREATED_BY,
781 SERIAL_TRANSACTION_TEMP_ID,
782 ERROR_CODE,
783 PROCESS_FLAG )
784 VALUES
785 (
786 FND_PROFILE.VALUE('ONT_SOURCE_CODE'),
787 l_source_line_id,
788 l_transaction_interface_id,
789 l_rsv_tbl(I).lot_number,
790 (-1 * l_rsv_tbl(I).reservation_quantity),
791 (-1 * l_rsv_tbl(I).secondary_reservation_quantity), -- INVCONV
792 sysdate,
793 FND_GLOBAL.USER_ID,
794 sysdate,
795 FND_GLOBAL.USER_ID,
796 l_serial_transaction_temp_id, --ER 8419373
797 null,
798 'Y');
799
800 END IF; -- under lot control
801
802 --ER 8419373 START
803 IF l_serial = 'Y' THEN
804
805 l_rsv_serial_count := 0;
806
807 --Get all serial numbers for the current reservation id
808 FOR J IN 1..l_serial_number_tbl.count
809 LOOP
810
811 IF l_rsv_tbl(I).reservation_id = l_serial_number_tbl(J).reservation_id THEN
812
813 l_rsv_serial_count := l_rsv_serial_count + 1;
814
815 INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
816 (
817 SOURCE_CODE,
818 SOURCE_LINE_ID,
819 TRANSACTION_INTERFACE_ID,
820 FM_SERIAL_NUMBER ,
821 --TO_SERIAL_NUMBER ,
822 LAST_UPDATE_DATE,
823 LAST_UPDATED_BY,
824 CREATION_DATE,
825 CREATED_BY,
826 ERROR_CODE,
827 PROCESS_FLAG )
828 VALUES
829 (
830 profile_values.oe_source_code,
831 l_source_line_id,
832 nvl(l_serial_transaction_temp_id,l_transaction_interface_id),
833 l_serial_number_tbl(J).serial_number,
834 --l_serial_number_tbl(J).serial_number,
835 sysdate,
836 FND_GLOBAL.USER_ID,
837 sysdate,
838 FND_GLOBAL.USER_ID,
839 null,
840 1);
841
842 END IF;
843
844 END LOOP;
845
846 IF l_debug_level > 0 THEN
847 oe_debug_pub.add( 'l_rsv_serial_count: ' || l_rsv_serial_count , 5 ) ;
848 oe_debug_pub.add( 'l_rsv_tbl(I).reservation_quantity: ' || l_rsv_tbl(I).reservation_quantity , 5 ) ;
849 END IF;
850
851 --Bug 13690325
852 --check for the reservation quantity if partial serial number have been entered
853 -- i.e. if the reserved quantity is 10 then the serial number entered should also be 10
854 IF l_rsv_tbl(I).reservation_quantity <> l_rsv_serial_count THEN
855 -- give a message
856 FND_MESSAGE.SET_NAME('ONT','OE_QUANTITY_MISMATCH');
857 OE_MSG_PUB.ADD;
858 x_return_status := FND_API.G_RET_STS_ERROR;
859 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
860 ROLLBACK TO INVENTORY_INTERFACE;
861 RETURN;
862 END IF;
863
864 END IF; --under serial control
865 --ER 8419373 END
866
867 IF l_debug_level > 0 THEN
868 oe_debug_pub.add( 'INV IFACE: INSERTING RECORD - 1' , 1 ) ;
869
870 oe_debug_pub.add( 'SOURCE_CODE :' || FND_PROFILE.VALUE ( 'ONT_SOURCE_CODE' ) , 5 ) ;
871
872 oe_debug_pub.add( 'SOURCE_LINE_ID :' || L_SOURCE_LINE_ID , 5 ) ;
873
874 oe_debug_pub.add( 'SOURCE_HEADER_ID :' || L_TRANSACTION_REFERENCE , 5 ) ;
875
876 oe_debug_pub.add( 'PROCESS_FLAG :' || 1 , 5 ) ;
877
878 oe_debug_pub.add( 'TRANSACTION_MODE :' || 1 , 5 ) ;
879
880 oe_debug_pub.add( 'LOCK_FLAG :' || 2 , 5 ) ;
881
882 oe_debug_pub.add( 'TRANSACTION_HEADER_ID :' || L_TRANSACTION_HEADER_ID , 5 ) ;
883
884 oe_debug_pub.add( 'INVENTORY_ITEM_ID :' || L_LINE_REC.INVENTORY_ITEM_ID , 5 ) ;
885
886 oe_debug_pub.add( 'SUBINVENTORY_CODE :' || L_RSV_TBL ( I ) .SUBINVENTORY_CODE , 5 ) ;
887
888 oe_debug_pub.add( 'TRANSACTION_QUANTITY :' || ( -1 * L_RSV_TBL ( I ) .RESERVATION_QUANTITY ) , 5 ) ;
889
890 oe_debug_pub.add( 'SECONDARY TRANSACTION_QUANTITY :' || ( -1 * L_RSV_TBL ( I ) .SECONDARY_RESERVATION_QUANTITY ) , 5 ) ; -- INVCONV
891
892
893 oe_debug_pub.add( 'TRANSACTION_DATE :' || l_trx_date_for_inv_iface, 5 ) ; --bug5897965
894
895 -- bug 5897965 oe_debug_pub.add( 'TRANSACTION_DATE :' || l_ordered_date , 5 ) ;
896
897 oe_debug_pub.add( 'ORGANIZATION_ID :' || L_RSV_TBL ( I ) .ORGANIZATION_ID , 5 ) ;
898
899 oe_debug_pub.add( 'ACCT_PERIOD_ID :' || NULL , 5 ) ;
900
901 oe_debug_pub.add( 'LAST_UPDATE_DATE :' || SYSDATE , 5 ) ;
902
903 oe_debug_pub.add( 'LAST_UPDATED_BY :' || FND_GLOBAL.USER_ID , 5 ) ;
904
905 oe_debug_pub.add( 'CREATION_DATE :' || SYSDATE , 5 ) ;
906
907 oe_debug_pub.add( 'CREATED_BY :' || FND_GLOBAL.USER_ID , 5 ) ;
908
909 oe_debug_pub.add( 'TRANSACTION_SOURCE_ID :' || L_SALES_ORDER_ID , 5 ) ;
910
911 oe_debug_pub.add( 'DSP_SEGMENT1 :' || L_ORDER_NUMBER , 5 ) ;
912
913 oe_debug_pub.add( 'DSP_SEGMENT2 :' || L_ORDER_TYPE_NAME , 5 ) ;
914
915 oe_debug_pub.add( 'DSP_SEGMENT3 :' || FND_PROFILE.VALUE ( 'ONT_SOURCE_CODE' ) , 5 ) ;
916
917 oe_debug_pub.add( 'TRANSACTION_SOURCE_TYPE_ID :' || TO_CHAR ( 2 ) , 5 ) ;
918
919 oe_debug_pub.add( 'TRANSACTION_ACTION_ID :' || TO_CHAR ( 1 ) , 5 ) ;
920
921 oe_debug_pub.add( 'TRANSACTION_TYPE_ID :' || TO_CHAR ( 33 ) , 5 ) ;
922
923 oe_debug_pub.add( 'DISTRIBUTION_ACCOUNT_ID :' || L_TRANS_ACC , 5 ) ;
924
925 oe_debug_pub.add( 'TRANSACTION_REFERENCE :' || L_TRANSACTION_REFERENCE , 5 ) ;
926
927 oe_debug_pub.add( 'TRX_SOURCE_LINE_ID :' || L_LINE_REC.LINE_ID , 5 ) ;
928
929 oe_debug_pub.add( 'TRX_SOURCE_DELIVERY_ID :' || NULL , 5 ) ;
930
931 oe_debug_pub.add( 'REVISION :' || L_RSV_TBL ( I ) .REVISION , 5 ) ;
932
933 oe_debug_pub.add( 'LOCATOR_ID :' || L_RSV_TBL ( I ) .LOCATOR_ID , 5 ) ;
934
935 oe_debug_pub.add( 'LOC_SEGMENT1 :' || NULL , 5 ) ;
936
937 oe_debug_pub.add( 'LOC_SEGMENT2 :' || NULL , 5 ) ;
938
939 oe_debug_pub.add( 'LOC_SEGMENT3 :' || NULL , 5 ) ;
940
941 oe_debug_pub.add( 'LOC_SEGMENT4 :' || NULL , 5 ) ;
942
943 oe_debug_pub.add( 'REQUIRED_FLAG :' || NULL , 5 ) ;
944
945 oe_debug_pub.add( 'PICKING_LINE_ID :' || TO_CHAR ( 0 ) , 5 ) ;
946
947 oe_debug_pub.add( 'TRANSFER_SUBINVENTORY :' || NULL , 5 ) ;
948
949 oe_debug_pub.add( 'TRANSFER_ORGANIZATION :' || NULL , 5 ) ;
950
951 oe_debug_pub.add( 'SHIP_TO_LOCATION_ID :' || NULL , 5 ) ;
952
953 oe_debug_pub.add( 'REQUISITION_LINE_ID :' || NULL , 5 ) ;
954
955 oe_debug_pub.add( 'TRANSACTION_UOM :' || L_RSV_TBL ( I).reservation_uom_code , 5 ); -- 9743423
956
957 oe_debug_pub.add( 'TRANS INTERFACE_ID :' || L_TRANSACTION_INTERFACE_ID , 5 ) ;
958
959 oe_debug_pub.add( 'DEMAND_ID :' || NULL , 5 ) ;
960
961 oe_debug_pub.add( 'SHIPMENT_NUMBER :' || NULL , 5 ) ;
962
963 oe_debug_pub.add( 'CURRENCY_CODE :' || NULL , 5 ) ;
964
965 oe_debug_pub.add( 'CURRENCY_CONVERSION_TYPE :' || NULL , 5 ) ;
966
967 oe_debug_pub.add( 'CURRENCY_CONVERSION_DATE :' || NULL , 5 ) ;
968
969 oe_debug_pub.add( 'CURRENCY_CONVERSION_RATE :' || NULL , 5 ) ;
970
971 oe_debug_pub.add( 'ENCUMBRANCE_ACCOUNT :' || NULL , 5 ) ;
972
973 oe_debug_pub.add( 'ENCUMBRANCE_AMOUNT :' || NULL , 5 ) ;
974
975 oe_debug_pub.add( 'PROJECT_ID :' || L_LINE_REC.PROJECT_ID , 5 ) ;
976
977 oe_debug_pub.add( 'TASK_ID :' || L_LINE_REC.TASK_ID , 5 ) ;
978 END IF;
979
980 INSERT INTO MTL_TRANSACTIONS_INTERFACE
981 (
982 SOURCE_CODE,
983 SOURCE_LINE_ID,
984 SOURCE_HEADER_ID,
985 PROCESS_FLAG,
986 TRANSACTION_MODE,
987 LOCK_FLAG,
988 TRANSACTION_HEADER_ID,
989 INVENTORY_ITEM_ID,
990 SUBINVENTORY_CODE,
991 TRANSACTION_QUANTITY,
992 SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
993 TRANSACTION_DATE,
994 ORGANIZATION_ID,
995 ACCT_PERIOD_ID,
996 LAST_UPDATE_DATE,
997 LAST_UPDATED_BY,
998 CREATION_DATE,
999 CREATED_BY,
1000 TRANSACTION_SOURCE_ID,
1001 DSP_SEGMENT1,
1002 DSP_SEGMENT2,
1003 DSP_SEGMENT3,
1004 TRANSACTION_SOURCE_TYPE_ID,
1005 TRANSACTION_ACTION_ID,
1006 TRANSACTION_TYPE_ID,
1007 DISTRIBUTION_ACCOUNT_ID,
1008 DST_SEGMENT1,
1009 DST_SEGMENT2,
1010 DST_SEGMENT3,
1011 DST_SEGMENT4,
1012 DST_SEGMENT5,
1013 DST_SEGMENT6,
1014 DST_SEGMENT7,
1015 DST_SEGMENT8,
1016 DST_SEGMENT9,
1017 DST_SEGMENT10,
1018 DST_SEGMENT11,
1019 DST_SEGMENT12,
1020 DST_SEGMENT13,
1021 DST_SEGMENT14,
1022 DST_SEGMENT15,
1023 DST_SEGMENT16,
1024 DST_SEGMENT17,
1025 DST_SEGMENT18,
1026 DST_SEGMENT19,
1027 DST_SEGMENT20,
1028 DST_SEGMENT21,
1029 DST_SEGMENT22,
1030 DST_SEGMENT23,
1031 DST_SEGMENT24,
1032 DST_SEGMENT25,
1033 DST_SEGMENT26,
1034 DST_SEGMENT27,
1035 DST_SEGMENT28,
1036 DST_SEGMENT29,
1037 DST_SEGMENT30,
1038 TRANSACTION_REFERENCE,
1039 TRX_SOURCE_LINE_ID,
1040 TRX_SOURCE_DELIVERY_ID,
1041 REVISION,
1042 LOCATOR_ID,
1043 LOC_SEGMENT1,
1044 LOC_SEGMENT2,
1045 LOC_SEGMENT3,
1046 LOC_SEGMENT4,
1047 REQUIRED_FLAG,
1048 PICKING_LINE_ID,
1049 TRANSFER_SUBINVENTORY,
1050 TRANSFER_ORGANIZATION,
1051 SHIP_TO_LOCATION_ID,
1052 REQUISITION_LINE_ID,
1053 TRANSACTION_UOM,
1054 TRANSACTION_INTERFACE_ID,
1055 DEMAND_ID,
1056 SHIPMENT_NUMBER,
1057 CURRENCY_CODE,
1058 CURRENCY_CONVERSION_TYPE,
1059 CURRENCY_CONVERSION_DATE,
1060 CURRENCY_CONVERSION_RATE,
1061 ENCUMBRANCE_ACCOUNT,
1062 ENCUMBRANCE_AMOUNT,
1063 --CONTENT_LPN_ID, -- added for bug 6313351
1064 LPN_ID, --added for bug 8658984
1065 PROJECT_ID,
1066 TASK_ID)
1067 SELECT
1068 profile_values.oe_source_code,
1069 l_source_line_id,
1070 l_transaction_reference,
1071 1, /* PROCESS_FLAG */
1072 3, /* TRANSACTION_MODE */
1073 2, /* LOCK_FLAG */
1074 l_transaction_header_id,
1075 l_line_rec.inventory_item_id,
1076 l_rsv_tbl(I).subinventory_code,
1077 (-1 * l_rsv_tbl(I).reservation_quantity),
1078 (-1 * l_rsv_tbl(I).secondary_reservation_quantity), -- INVCONV
1079 l_trx_date_for_inv_iface/*l_ordered_date*/, --bug5897965 l_ordered_date commented
1080 l_rsv_tbl(I).organization_id,
1081 null,
1082 sysdate,
1083 profile_values.user_id,
1084 sysdate,
1085 profile_values.user_id,
1086 l_sales_order_id, /* transaction_source_id */
1087 l_order_number,
1088 l_order_type_name,
1089 profile_values.oe_source_code,
1090 2,
1091 1,
1092 33,
1093 l_trans_acc,
1094 segment1,
1095 segment2,
1096 segment3,
1097 segment4,
1098 segment5,
1099 segment6,
1100 segment7,
1101 segment8,
1102 segment9,
1103 segment10,
1104 segment11,
1105 segment12,
1106 segment13,
1107 segment14,
1108 segment15,
1109 segment16,
1110 segment17,
1111 segment18,
1112 segment19,
1113 segment20,
1114 segment21,
1115 segment22,
1116 segment23,
1117 segment24,
1118 segment25,
1119 segment26,
1120 segment27,
1121 segment28,
1122 segment29,
1123 segment30,
1124 l_transaction_reference,
1125 l_line_rec.line_id,
1126 null,
1127 l_rsv_tbl(I).revision,
1128 l_rsv_tbl(I).locator_id,
1129 null,
1130 null,
1131 null,
1132 null,
1133 null,
1134 null, /* l_shipment_line_id */
1135 null, /* l_dest_subinv */
1136 null, /* l_to_org_id */
1137 null, /* l_location_id */
1138 null, /* l_req_line_id */
1139 L_RSV_TBL(I).reservation_uom_code , --l_line_rec.order_quantity_uom, --9743423
1140 l_transaction_interface_id, /* interface_id */
1141 null,
1142 null,
1143 null,
1144 null,
1145 null,
1146 null,
1147 null, /* l_budget_acct_id */
1148 null, /* l_unit_price * p_transaction_detail_qty */
1149 l_rsv_tbl(I).lpn_id, -- added for bug 6313351
1150 l_line_rec.project_id,
1151 l_line_rec.task_id
1152 FROM gl_code_combinations
1153 WHERE code_combination_id = l_trans_acc;
1154
1155 SELECT mtl_material_transactions_s.nextval
1156 INTO l_transaction_interface_id
1157 FROM dual;
1158 -- for use when looping or used by the second
1159 -- insert into mtl_transactions_interface
1160 -- interface_id need to be unique for each interface record
1161
1162
1163 /* -- HW OPM end of BUG#:2536589 INVCONV
1164 END IF; --- if discete org */
1165
1166 END LOOP;
1167
1168 END IF; -- with a reservation record
1169
1170 IF l_line_rec.reserved_quantity = 0 OR l_remained_qty > 0 THEN
1171
1172 -- line with no reservation record or
1173 -- line is with partial reservation
1174
1175 -- check if subinventory exists on the line
1176 -- if not error out
1177 -- check if item is under lot/locator/revision control
1178 -- if so error out
1179
1180
1181 /* check if subinventory exists on line */
1182 /* -- HW OPM BUG#:2536589 No need to check subinventory for OPM INVCONV
1183 -- we need to branch.
1184
1185 IF (l_process_org = 0 )THEN */
1186
1187 IF ( l_line_rec.subinventory is null or
1188 l_line_rec.subinventory = FND_API.G_MISS_CHAR ) THEN
1189 IF l_debug_level > 0 THEN
1190 oe_debug_pub.add( 'INV IFACE: SUBINV IS NULL OR MISS_CHAR FOR INSERT - 2' , 5 ) ;
1191 END IF;
1192 -- Give a message here
1193 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_IFACE_SUB');
1194 -- message should say if item is under revision/lot/locator control, use the
1195 -- reservation form to reserve first
1196 OE_MSG_PUB.ADD;
1197 x_return_status := FND_API.G_RET_STS_ERROR;
1198 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
1199 ROLLBACK TO INVENTORY_INTERFACE;
1200 RETURN;
1201 END IF;
1202
1203 /* handle locator */
1204
1205 BEGIN
1206 SELECT stock_locator_control_code
1207 INTO l_stock_locator_control_code
1208 FROM mtl_parameters
1209 WHERE organization_id = l_line_rec.ship_from_org_id;
1210 EXCEPTION
1211 WHEN OTHERS THEN
1212 IF l_debug_level > 0 THEN
1213 oe_debug_pub.add( 'INV IFACE: LOCATOR CONTROL CODE SELECT FAILURE' , 5 ) ;
1214 END IF;
1215 x_return_status := FND_API.G_RET_STS_ERROR;
1216 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
1217 ROLLBACK TO INVENTORY_INTERFACE;
1218 RETURN;
1219 END;
1220
1221 BEGIN
1222 SELECT locator_type
1223 INTO l_locator_type
1224 FROM mtl_secondary_inventories
1225 WHERE secondary_inventory_name = l_line_rec.subinventory
1226 AND organization_id = l_line_rec.ship_from_org_id;
1227
1228 EXCEPTION
1229 WHEN OTHERS THEN
1230 IF l_debug_level > 0 THEN
1231 oe_debug_pub.add( 'INV IFACE: LOCATOR TYPE SELECT FAILURE' , 5 ) ;
1232 END IF;
1233 x_return_status := FND_API.G_RET_STS_ERROR;
1234 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
1235 ROLLBACK TO INVENTORY_INTERFACE;
1236 RETURN;
1237 END;
1238
1239
1240 BEGIN
1241 SELECT location_control_code
1242 INTO l_location_control_code
1243 FROM mtl_system_items
1244 WHERE inventory_item_id = l_line_rec.inventory_item_id
1245 AND organization_id = l_line_rec.ship_from_org_id;
1246 -- ???? warehouse or validation org?
1247 EXCEPTION
1248 WHEN OTHERS THEN
1249 IF l_debug_level > 0 THEN
1250 oe_debug_pub.add( 'INV IFACE: LOCATION CONTROL CODE FAILURE' , 5 ) ;
1251 END IF;
1252 x_return_status := FND_API.G_RET_STS_ERROR;
1253 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
1254 ROLLBACK TO INVENTORY_INTERFACE;
1255 RETURN;
1256 END;
1257
1258 IF l_debug_level > 0 THEN
1259 oe_debug_pub.add( 'INV IFACE: BEFORE LOCATOR_CONTROL API CALL' , 1 ) ;
1260 END IF;
1261
1262 l_locator := INV_RESERVATION_UTIL_PVT.locator_control(
1263 p_org_control => l_stock_locator_control_code
1264 ,p_sub_control => l_locator_type
1265 ,p_item_control => l_location_control_code);
1266 IF l_debug_level > 0 THEN
1267 oe_debug_pub.add( 'INV IFACE: AFTER LOCATOR_CONTROL API CALL - ' || TO_CHAR ( L_LOCATOR ) , 1 ) ;
1268 END IF;
1269
1270 -- END IF; -- of branching HW OPM BUG#:2536589 INVCONV
1271
1272 IF (l_revision_code = 2 OR l_lot_code = 2 OR nvl(l_serial_code, 1) <> 1 OR
1273 l_locator > 1 )THEN
1274 -- 2 == YES
1275 /*ER 8419373
1276 IF nvl(l_serial_code, 1) <> 1 THEN
1277 IF l_debug_level > 0 THEN
1278 oe_debug_pub.add( 'INV IFACE: ITEM UNDER SERIAL CONTORL , ERROR' , 5 ) ;
1279 END IF;
1280 -- give a message here
1281 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_IFACE_SERIAL');
1282 OE_MSG_PUB.ADD;
1283 ELSE*/
1284 IF l_debug_level > 0 THEN
1285 oe_debug_pub.add( 'INV IFACE: ITEM UNDER REVISION/LOT/LOCATOR/SERIAL CONTROL AND NO RESERVATION EXISTS' , 5 ) ;
1286 END IF;
1287 -- Give a message here
1288 FND_MESSAGE.SET_NAME('ONT', 'OE_INV_IFACE_NO_RSV');
1289 OE_MSG_PUB.ADD;
1290 --END IF;
1291 x_return_status := FND_API.G_RET_STS_ERROR;
1292 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
1293 ROLLBACK TO INVENTORY_INTERFACE;
1294 RETURN;
1295
1296 END IF;
1297
1298 /* -- HW OPm BUG#:2536589 Need to check for non-lot,non-inv OPM items. INVCONV
1299 IF ( l_process_org = 1 AND l_lot = 'N' ) THEN
1300 IF l_debug_level > 0 THEN
1301 oe_debug_pub.add( 'INV IFACE: NO DEFAULT TRXN EXISTS FOR NON-LOT , NON-INV OPM ITEM' , 5 ) ;
1302 END IF;
1303 x_return_status := FND_API.G_RET_STS_ERROR;
1304 x_result_out := OE_GLOBALS.G_WFR_INCOMPLETE;
1305 ROLLBACK TO INVENTORY_INTERFACE;
1306 RETURN;
1307 END IF; */
1308
1309 /* -- HW OPM BUG#:2536589 Need to branch since OPM does not need to populate INVCONV
1310 -- mtl_transactions_interface table
1311 IF ( l_process_org = 0 ) THEN */
1312 SELECT oe_transactions_iface_s.nextval
1313 INTO l_source_line_id
1314 FROM dual;
1315
1316 IF l_debug_level > 0 THEN
1317 oe_debug_pub.add( 'INV IFACE: INSERTING RECORD - 2' , 5 ) ;
1318
1319 oe_debug_pub.add( 'SOURCE_CODE :' || FND_PROFILE.VALUE ( 'ONT_SOURCE_CODE' ) , 5 ) ;
1320
1321 oe_debug_pub.add( 'SOURCE_LINE_ID :' || L_SOURCE_LINE_ID , 5 ) ;
1322
1323 oe_debug_pub.add( 'SOURCE_HEADER_ID :' || L_TRANSACTION_REFERENCE , 5 ) ;
1324
1325 oe_debug_pub.add( 'PROCESS_FLAG :' || 1 , 5 ) ;
1326
1327 oe_debug_pub.add( 'TRANSACTION_MODE :' || 1 , 5 ) ;
1328
1329 oe_debug_pub.add( 'LOCK_FLAG :' || 2 , 5 ) ;
1330
1331 oe_debug_pub.add( 'TRANSACTION_HEADER_ID :' || L_TRANSACTION_HEADER_ID , 5 ) ;
1332
1333 oe_debug_pub.add( 'INVENTORY_ITEM_ID :' || L_LINE_REC.INVENTORY_ITEM_ID , 5 ) ;
1334
1335 oe_debug_pub.add( 'SUBINVENTORY_CODE :' || L_LINE_REC.SUBINVENTORY , 5 ) ;
1336
1337 oe_debug_pub.add( 'TRANSACTION_QUANTITY :' || ( -1 * L_LINE_REC.ORDERED_QUANTITY ) || ' OR ' || ( -1 * L_REMAINED_QTY ) , 5 ) ;
1338
1339
1340 -- (-1 * decode(reservation_flag, 'Y', l_remained_qty, l_line_rec.ordered_quantity)),1);
1341
1342
1343 -- bug 5897965 oe_debug_pub.add( 'TRANSACTION_DATE :' || l_ordered_date , 5 ) ;
1344
1345 oe_debug_pub.add( 'TRANSACTION_DATE :' || l_trx_date_for_inv_iface , 5 ) ; --bug5897965
1346
1347 oe_debug_pub.add( 'ORGANIZATION_ID :' || L_LINE_REC.SHIP_FROM_ORG_ID , 5 ) ;
1348
1349 oe_debug_pub.add( 'ACCT_PERIOD_ID :' || NULL , 5 ) ;
1350
1351 oe_debug_pub.add( 'LAST_UPDATE_DATE :' || SYSDATE , 5 ) ;
1352
1353 oe_debug_pub.add( 'LAST_UPDATED_BY :' || FND_GLOBAL.USER_ID , 5 ) ;
1354
1355 oe_debug_pub.add( 'CREATION_DATE :' || SYSDATE , 5 ) ;
1356
1357 oe_debug_pub.add( 'CREATED_BY :' || FND_GLOBAL.USER_ID , 5 ) ;
1358
1359 oe_debug_pub.add( 'TRANSACTION_SOURCE_ID :' || L_SALES_ORDER_ID , 5 ) ;
1360
1361 oe_debug_pub.add( 'DSP_SEGMENT1 :' || L_ORDER_NUMBER , 5 ) ;
1362
1363 oe_debug_pub.add( 'DSP_SEGMENT2 :' || L_ORDER_TYPE_NAME , 5 ) ;
1364
1365 oe_debug_pub.add( 'DSP_SEGMENT3 :' || FND_PROFILE.VALUE ( 'ONT_SOURCE_CODE' ) , 5 ) ;
1366
1367 oe_debug_pub.add( 'TRANSACTION_SOURCE_TYPE_ID :' || TO_CHAR ( 2 ) , 5 ) ;
1368
1369 oe_debug_pub.add( 'TRANSACTION_ACTION_ID :' || TO_CHAR ( 1 ) , 5 ) ;
1370
1371 oe_debug_pub.add( 'TRANSACTION_TYPE_ID :' || TO_CHAR ( 33 ) , 5 ) ;
1372
1373 oe_debug_pub.add( 'DISTRIBUTION_ACCOUNT_ID :' || L_TRANS_ACC , 5 ) ;
1374
1375 oe_debug_pub.add( 'TRANSACTION_REFERENCE :' || L_TRANSACTION_REFERENCE , 5 ) ;
1376
1377 oe_debug_pub.add( 'TRX_SOURCE_LINE_ID :' || L_LINE_REC.LINE_ID , 5 ) ;
1378
1379 oe_debug_pub.add( 'TRX_SOURCE_DELIVERY_ID :' || NULL , 5 ) ;
1380
1381 oe_debug_pub.add( 'REVISION :' || NULL , 5 ) ;
1382
1383 oe_debug_pub.add( 'LOCATOR_ID :' || NULL , 5 ) ;
1384
1385 oe_debug_pub.add( 'LOC_SEGMENT1 :' || NULL , 5 ) ;
1386
1387 oe_debug_pub.add( 'LOC_SEGMENT2 :' || NULL , 5 ) ;
1388
1389 oe_debug_pub.add( 'LOC_SEGMENT3 :' || NULL , 5 ) ;
1390
1391 oe_debug_pub.add( 'LOC_SEGMENT4 :' || NULL , 5 ) ;
1392
1393 oe_debug_pub.add( 'REQUIRED_FLAG :' || NULL , 5 ) ;
1394
1395 oe_debug_pub.add( 'PICKING_LINE_ID :' || NULL , 5 ) ;
1396
1397 oe_debug_pub.add( 'TRANSFER_SUBINVENTORY :' || NULL , 5 ) ;
1398
1399 oe_debug_pub.add( 'TRANSFER_ORGANIZATION :' || NULL , 5 ) ;
1400
1401 oe_debug_pub.add( 'SHIP_TO_LOCATION_ID :' || NULL , 5 ) ;
1402
1403 oe_debug_pub.add( 'REQUISITION_LINE_ID :' || NULL , 5 ) ;
1404
1405 oe_debug_pub.add( 'TRANSACTION_UOM :' || L_LINE_REC.ORDER_QUANTITY_UOM , 5 ) ;
1406
1407 oe_debug_pub.add( 'TRANS INTERFACE_ID :' || L_TRANSACTION_INTERFACE_ID , 5 ) ;
1408
1409 oe_debug_pub.add( 'DEMAND_ID :' || NULL , 5 ) ;
1410
1411 oe_debug_pub.add( 'SHIPMENT_NUMBER :' || NULL , 5 ) ;
1412
1413 oe_debug_pub.add( 'CURRENCY_CODE :' || NULL , 5 ) ;
1414
1415 oe_debug_pub.add( 'CURRENCY_CONVERSION_TYPE :' || NULL , 5 ) ;
1416
1417 oe_debug_pub.add( 'CURRENCY_CONVERSION_DATE :' || NULL , 5 ) ;
1418
1419 oe_debug_pub.add( 'CURRENCY_CONVERSION_RATE :' || NULL , 5 ) ;
1420
1421 oe_debug_pub.add( 'ENCUMBRANCE_ACCOUNT :' || NULL , 5 ) ;
1422
1423 oe_debug_pub.add( 'ENCUMBRANCE_AMOUNT :' || NULL , 5 ) ;
1424
1425 oe_debug_pub.add( 'PROJECT_ID :' || L_LINE_REC.PROJECT_ID , 5 ) ;
1426
1427 oe_debug_pub.add( 'TASK_ID :' || L_LINE_REC.TASK_ID , 5 ) ;
1428 END IF;
1429
1430 INSERT INTO MTL_TRANSACTIONS_INTERFACE
1431 (
1432 SOURCE_CODE,
1433 SOURCE_LINE_ID,
1434 SOURCE_HEADER_ID,
1435 PROCESS_FLAG,
1436 TRANSACTION_MODE,
1437 LOCK_FLAG,
1438 TRANSACTION_HEADER_ID,
1439 INVENTORY_ITEM_ID,
1440 SUBINVENTORY_CODE,
1441 TRANSACTION_QUANTITY,
1442 SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
1443 TRANSACTION_DATE,
1444 ORGANIZATION_ID,
1445 ACCT_PERIOD_ID,
1446 LAST_UPDATE_DATE,
1447 LAST_UPDATED_BY,
1448 CREATION_DATE,
1449 CREATED_BY,
1450 TRANSACTION_SOURCE_ID,
1451 DSP_SEGMENT1,
1452 DSP_SEGMENT2,
1453 DSP_SEGMENT3,
1454 TRANSACTION_SOURCE_TYPE_ID,
1455 TRANSACTION_ACTION_ID,
1456 TRANSACTION_TYPE_ID,
1457 DISTRIBUTION_ACCOUNT_ID,
1458 DST_SEGMENT1,
1459 DST_SEGMENT2,
1460 DST_SEGMENT3,
1461 DST_SEGMENT4,
1462 DST_SEGMENT5,
1463 DST_SEGMENT6,
1464 DST_SEGMENT7,
1465 DST_SEGMENT8,
1466 DST_SEGMENT9,
1467 DST_SEGMENT10,
1468 DST_SEGMENT11,
1469 DST_SEGMENT12,
1470 DST_SEGMENT13,
1471 DST_SEGMENT14,
1472 DST_SEGMENT15,
1473 DST_SEGMENT16,
1474 DST_SEGMENT17,
1475 DST_SEGMENT18,
1476 DST_SEGMENT19,
1477 DST_SEGMENT20,
1478 DST_SEGMENT21,
1479 DST_SEGMENT22,
1480 DST_SEGMENT23,
1481 DST_SEGMENT24,
1482 DST_SEGMENT25,
1483 DST_SEGMENT26,
1484 DST_SEGMENT27,
1485 DST_SEGMENT28,
1486 DST_SEGMENT29,
1487 DST_SEGMENT30,
1488 TRANSACTION_REFERENCE,
1489 TRX_SOURCE_LINE_ID,
1490 TRX_SOURCE_DELIVERY_ID,
1491 REVISION,
1492 LOCATOR_ID,
1493 LOC_SEGMENT1,
1494 LOC_SEGMENT2,
1495 LOC_SEGMENT3,
1496 LOC_SEGMENT4,
1497 REQUIRED_FLAG,
1498 PICKING_LINE_ID,
1499 TRANSFER_SUBINVENTORY,
1500 TRANSFER_ORGANIZATION,
1501 SHIP_TO_LOCATION_ID,
1502 REQUISITION_LINE_ID,
1503 TRANSACTION_UOM,
1504 TRANSACTION_INTERFACE_ID,
1505 DEMAND_ID,
1506 SHIPMENT_NUMBER,
1507 CURRENCY_CODE,
1508 CURRENCY_CONVERSION_TYPE,
1509 CURRENCY_CONVERSION_DATE,
1510 CURRENCY_CONVERSION_RATE,
1511 ENCUMBRANCE_ACCOUNT,
1512 ENCUMBRANCE_AMOUNT,
1513 PROJECT_ID,
1514 TASK_ID)
1515 SELECT
1516 profile_values.oe_source_code,
1517 l_source_line_id,
1518 l_transaction_reference,
1519 1, /* PROCESS_FLAG */
1520 3, /* TRANSACTION_MODE */
1521 2, /* LOCK_FLAG */
1522 l_transaction_header_id,
1523 l_line_rec.inventory_item_id,
1524 l_line_rec.subinventory,
1525 (-1 * decode(reservation_flag, 'Y', l_remained_qty, l_line_rec.ordered_quantity)),
1526 (-1 * decode(reservation_flag, 'Y', l_remained_qty2, l_line_rec.ordered_quantity2)), -- INVCONV
1527 l_trx_date_for_inv_iface/*l_ordered_date*/, --bug5897965 l_ordered_date commented
1528 l_line_rec.ship_from_org_id,
1529 null,
1530 sysdate,
1531 profile_values.user_id,
1532 sysdate,
1533 profile_values.user_id,
1534 l_sales_order_id, /* transaction_source_id */
1535 l_order_number,
1536 l_order_type_name,
1537 profile_values.oe_source_code,
1538 2, /* l_trx_source_type_id */
1539 1, /* l_trx_action_id */
1540 33, /* l_trx_type_code */
1541 l_trans_acc,
1542 segment1,
1543 segment2,
1544 segment3,
1545 segment4,
1546 segment5,
1547 segment6,
1548 segment7,
1549 segment8,
1550 segment9,
1551 segment10,
1552 segment11,
1553 segment12,
1554 segment13,
1555 segment14,
1556 segment15,
1557 segment16,
1558 segment17,
1559 segment18,
1560 segment19,
1561 segment20,
1562 segment21,
1563 segment22,
1564 segment23,
1565 segment24,
1566 segment25,
1567 segment26,
1568 segment27,
1569 segment28,
1570 segment29,
1571 segment30,
1572 l_transaction_reference,
1573 l_line_rec.line_id,
1574 null,
1575 null, /* revision */
1576 null, /* locator */
1577 null,
1578 null,
1579 null,
1580 null,
1581 null,
1582 null, /* l_shipment_line_id */
1583 null, /* l_dest_subinv */
1584 null, /* l_to_org_id */
1585 null, /* l_location_id */
1586 null, /* l_req_line_id */
1587 l_line_rec.order_quantity_uom,
1588 l_transaction_interface_id,
1589 null,
1590 null,
1591 null,
1592 null,
1593 null,
1594 null,
1595 null, /* l_budget_acct_id */
1596 null, /* l_unit_price * p_transaction_detail_qty */
1597 l_line_rec.project_id,
1598 l_line_rec.task_id
1599 FROM gl_code_combinations
1600 WHERE code_combination_id = l_trans_acc;
1601
1602 IF l_debug_level > 0 THEN
1603 oe_debug_pub.add( 'INV IFACE: FINISH INSERTING - 2 , CALLING UPDATE_FLOW_STATUS_CODE' , 1 ) ;
1604 END IF;
1605
1606 /* -- HW OPM BUG#:2536589 -- INVCONV
1607 END IF; -- of branching */
1608
1609 END IF;
1610 /* of partial reservation or no reservation line */
1611
1612 /* update flow_status_code */
1613 OE_ORDER_WF_UTIL.Update_Flow_Status_Code(p_line_id => p_line_id
1614 , p_flow_status_code => 'INVENTORY_INTERFACED'
1615 , x_return_status => l_return_status);
1616 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1617 IF l_debug_level > 0 THEN
1618 oe_debug_pub.add( 'INV IFACE: UPDATE FLOW STATUS CODE FAILED' , 5 ) ;
1619 END IF;
1620 END IF;
1621
1622 END IF; -- If line is shippable
1623
1624 -- do PTO explosion if necessary
1625 -- since we are not going to ship this line, SMC is unimportant here
1626 -- and as long as explosion_date is null, we explode it
1627 IF (l_line_rec.explosion_date IS NULL AND
1628 l_line_rec.top_model_line_id IS NOT NULL AND
1629 l_line_rec.ato_line_id IS NULL) THEN
1630 IF l_debug_level > 0 THEN
1631 oe_debug_pub.add( 'INV IFACE: IT IS A PTO LINE WITHOUT EXPLOSION DATE' , 3 ) ;
1632 END IF;
1633 IF l_line_rec.item_type_code = OE_GLOBALS.G_ITEM_MODEL OR
1634 l_line_rec.item_type_code = OE_GLOBALS.G_ITEM_CLASS OR
1635 l_line_rec.item_type_code = OE_GLOBALS.G_ITEM_KIT THEN
1636 IF l_debug_level > 0 THEN
1637 oe_debug_pub.add( 'INV IFACE: IT IS ITEM TYPE : '|| L_LINE_REC.ITEM_TYPE_CODE , 3 ) ;
1638 END IF;
1639 -- Do the explosion
1640 l_return_status := OE_Config_Util.Process_Included_Items(
1641 p_line_id => l_line_rec.line_id
1642 ,p_freeze => TRUE
1643 ,p_process_requests => TRUE);
1644 IF l_debug_level > 0 THEN
1645 oe_debug_pub.add( 'INV IFACE: AFTER CALLING EXPLOSION : '|| L_RETURN_STATUS , 3 ) ;
1646 END IF;
1647 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1648 IF l_debug_level > 0 THEN
1649 oe_debug_pub.add( 'INV IFACE: FREEZE INCLUDED ITEM FAILED - UNEXP' , 1 ) ;
1650 END IF;
1651 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1652 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1653 IF l_debug_level > 0 THEN
1654 oe_debug_pub.add( 'INV IFACE: FREEZE INCLUDED ITEM FAILED - EXP' , 1 ) ;
1655 END IF;
1656 RAISE FND_API.G_EXC_ERROR;
1657 END IF;
1658 END IF;
1659 END IF;
1660
1661 /* bug 4659103: update of visible_demand_flag code removed */
1662
1663 IF l_debug_level > 0 THEN
1664 oe_debug_pub.add( 'INV IFACE: EXITING INVENTORY_INTERFACE' , 1 ) ;
1665 END IF;
1666
1667 EXCEPTION
1668 WHEN OTHERS THEN
1669 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1670 THEN
1671 OE_MSG_PUB.Add_Exc_Msg
1672 ( G_PKG_NAME
1673 , 'Inventory_Interface'
1674 );
1675 END IF;
1676
1677 IF l_debug_level > 0 THEN
1678 oe_debug_pub.add( 'INV IFACE ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 100 ) , 1 ) ;
1679 END IF;
1680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1681 END Inventory_Interface;
1682
1683 END OE_Inv_Iface_PVT;