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