[Home] [Help]
PACKAGE BODY: APPS.OE_DS_PVT
Source
1 PACKAGE BODY OE_DS_PVT AS
2 /* $Header: OEXVDSRB.pls 120.19.12020000.2 2013/03/11 10:25:24 lnammina ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_DS_PVT';
7 G_LINE_LOCATION_ID NUMBER := NULL; --bug 4402566
8
9 FUNCTION Get_Char_of_accts (p_ship_from_org_id IN NUMBER) RETURN NUMBER;
10
11
12 PROCEDURE Decrement_Inventory(
13 p_detail_id IN NUMBER,
14 p_line_rec IN OE_ORDER_PUB.line_rec_type,
15 p_transaction_id IN NUMBER,
16 p_transaction_detail_qty IN NUMBER,
17 p_trans_qty2 IN NUMBER, -- INVCONV
18 p_inventory_item_id IN NUMBER,
19 p_delivery IN NUMBER,
20 p_lot_number IN VARCHAR2,
21 p_revision IN VARCHAR2,
22 p_secondary_inventory IN VARCHAR2,
23 p_locator_id IN NUMBER,
24 p_warehouse_id IN NUMBER,
25 p_chart_of_accts IN NUMBER,
26 p_trx_uom IN VARCHAR2,
27 p_sn_control_code IN NUMBER,
28 p_as_alpha_prefix IN VARCHAR2,
29 p_transaction_header_id IN NUMBER,
30 p_transfer_lpn_id IN NUMBER, -- 3544019
31 x_return_status OUT NOCOPY VARCHAR2);
32
33
34 /* Procedure Decrement_Inventory_for_OPM( remove for INVCONV
35 p_detail_id IN NUMBER,
36 p_line_rec IN OE_ORDER_PUB.line_rec_type,
37 p_transaction_id IN NUMBER,
38 p_trans_qty IN NUMBER,
39 p_trans_qty2 IN NUMBER,
40 p_inventory_item_id IN NUMBER,
41 p_delivery IN NUMBER,
42 p_lot_number IN VARCHAR2,
43 p_sublot_no IN VARCHAR2,
44 p_revision IN VARCHAR2,
45 p_locator_id IN NUMBER,
46 p_warehouse_id IN NUMBER,
47 p_chart_of_accts IN NUMBER,
48 p_trx_uom IN VARCHAR2,
49 p_sn_control_code IN NUMBER,
50 p_as_alpha_prefix IN VARCHAR2,
51 p_transaction_header_id IN NUMBER,
52 x_return_status OUT NOCOPY VARCHAR2); */
53
54
55 PROCEDURE Call_Process_Order
56 (p_orig_shipped IN NUMBER
57 ,p_short_quantity IN NUMBER
58 ,p_transaction_date IN DATE
59 ,p_add_to_shipped IN NUMBER
60 ,p_add_to_shipped2 IN NUMBER
61 ,p_line_rec IN OE_ORDER_PUB.Line_rec_Type
62 ,x_return_status OUT NOCOPY VARCHAR2
63 );
64
65 PROCEDURE Create_reservation
66 (p_qty_to_be_reserved IN NUMBER
67 ,p_qty2_to_be_reserved IN NUMBER default null -- INVCONV
68 ,p_revision IN VARCHAR2
69 ,p_locator_id IN NUMBER
70 ,p_lot IN VARCHAR2
71 ,p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
72 ,x_qty_reserved OUT NOCOPY NUMBER
73 ,x_qty2_reserved OUT NOCOPY NUMBER
74 ,x_rsv_id OUT NOCOPY NUMBER
75 ,x_return_status OUT NOCOPY VARCHAR2
76 ,p_transfer_lpn_id IN NUMBER
77 ,p_prim_reservation_qty IN NUMBER DEFAULT NULL -- 12794393
78 );
79
80
81 /* --------------------------------------------------------------------
82 Procedure Name : DropShipment Receiving
83 Description : This callback function is called from PO receiving function
84 for non-inventory items and INV for inventory items.
85 Fetch the record from RCV_TRANSACTIONS.
86 If there are records in RCV_LOT_TRANSACTIONS, fetch them
87 in a loop.
88 For each record in RCV_LOT_TRANSACTIONS (or for the one
89 record in RCV_TRANSACTIONS):
90 1. If the application is INV, call decrement inventory
91 2. Compute the quantity shipped.
92
93 If the application is INV, call
94 mtl_online_transaction_pub.process_online to decrement
95 inventory.
96
97 Call process order with the shipped quantity update.
98
99
100 ----------------------------------------------------------------------- */
101
102 FUNCTION DropShipReceive( p_rcv_transaction_id IN NUMBER,
103 p_application_short_name IN VARCHAR2,
104 p_mode IN NUMBER DEFAULT 0)
105
106 RETURN BOOLEAN
107 IS
108 l_line_id NUMBER;
109 l_line_rec OE_ORDER_PUB.line_rec_type;
110 l_transaction_id NUMBER := p_rcv_transaction_id;
111 l_application_short_name VARCHAR2(3);
112 l_pr_complete NUMBER; /* Purchase Release complete */
113 l_ordered_quantity NUMBER := 0;
114 l_qty_to_be_reserved NUMBER := 0;
115 l_cancelled_quantity NUMBER := 0;
116 l_shipped_quantity NUMBER := 0;
117 l_orig_short_quantity NUMBER := 0;
118 l_rcv_quantity NUMBER := 0;
119 l_lot_quantity NUMBER := -1;
120 l_short_quantity NUMBER := 0;
121 l_add_to_shipped NUMBER := 0;
122 l_unit_descr VARCHAR2(25);
123 l_order_uom VARCHAR2(3);
124 l_rcv_uom VARCHAR2(3);
125 l_converted_qty NUMBER;
126 l_lot VARCHAR2(80) := null; -- INVCONV 4094197
127 l_subinventory VARCHAR2(10) := null;
128 l_revision VARCHAR2(3) := null;
129 l_transactable VARCHAR2(1);
130 l_sub_reservable NUMBER := 1;
131 l_item_reservable NUMBER := 1;
132 l_organization_id NUMBER;
133 l_item_id NUMBER;
134 l_locator_id NUMBER := 0;
135 l_delivery NUMBER;
136 l_sn_control_code NUMBER;
137 l_as_alpha_prefix VARCHAR2(30);
138 l_transaction_date DATE;
139 l_orig_shipped NUMBER; /* Bug 2312461 */
140
141 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
142 l_quantity_reserved NUMBER;
143 l_qty_to_reserve NUMBER;
144 l_rsv_id NUMBER := 0;
145 l_sales_order_id NUMBER;
146 l_return_status VARCHAR2(1);
147 l_source_code VARCHAR2(40) := fnd_profile.value('ONT_SOURCE_CODE');
148 l_chart_of_accts NUMBER;
149
150 l_lot_set_id NUMBER := null;
151
152 -- For INV's process online API
153 l_outcome BOOLEAN;
154 l_error_code VARCHAR2(10);
155 l_error_explanation VARCHAR2(100);
156
157 l_msg_count NUMBER;
158 l_msg_data VARCHAR2(20000);
159
160
161 -- temporary variable for debugging.
162 l_database VARCHAR2(9);
163 l_file_val VARCHAR2(80);
164
165 -- PROCESS variables INVCONV
166 l_orig_short_quantity2 NUMBER := 0;
167 l_short_quantity2 NUMBER := 0;
168 l_uom2 VARCHAR2(3);
169 l_add_to_shipped2 NUMBER := 0;
170 l_rcv_quantity2 NUMBER := 0;
171 l_lot_quantity2 NUMBER := 0;
172 l_reserve_quantity2 NUMBER := 0;
173 l_unit2 VARCHAR2(25);
174 l_quantity2_reserved NUMBER; -- INVCONV
175 l_qty2_to_be_reserved NUMBER := 0; -- INVCONV
176 -- l_sublot_no VARCHAR2(30); INVCONV
177 -- l_opm_order_uom VARCHAR2(5); INVCONV
178 -- l_opm_rcv_uom VARCHAR2(5); INVCONV
179 -- l_opm_item_id NUMBER; INVCONV
180 l_orig_user_id NUMBER;
181 l_orig_resp_id NUMBER;
182 l_resp_appl_id NUMBER;
183 l_po_header_id NUMBER; -- bug 4402566
184
185 l_so_ou_id NUMBER;
186 l_po_ou_id NUMBER;
187
188 l_transfer_lpn_id NUMBER; -- BUG 3544019
189
190 l_primary_quantity NUMBER := 0; -- 12794393 --Final Receiving qty in prim UOM
191 l_rcv_primary_quantity NUMBER := 0; -- 12794393 --Stores the non lot receiving qty in primary UOM
192
193
194 -- Bug2407918. If more than one order lines are found because of multiple under-receipts, order
195 -- them here, so that first row fetched corresponds to the most recently created line.
196 CURSOR C1 IS
197 SELECT OL.LINE_ID,
198 RT.TRANSACTION_DATE,
199 OL.ORG_ID, -- bug 4402566
200 OD.LINE_LOCATION_ID, -- bug 4402566
201 RT.PO_HEADER_ID, -- bug 4402566
202 nvl(OL.SHIPPED_QUANTITY,0) shp_qty
203 FROM OE_ORDER_LINES_ALL OL,
204 OE_DROP_SHIP_SOURCES OD,
205 RCV_TRANSACTIONS RT
206 WHERE OL.LINE_ID = OD.LINE_ID
207 AND OL.SOURCE_TYPE_CODE = 'EXTERNAL'
208 AND OD.PO_HEADER_ID = RT.PO_HEADER_ID
209 AND OD.PO_LINE_ID = RT.PO_LINE_ID
210 AND OL.OPEN_FLAG = 'Y' --added for bug 7614745
211 AND OL.FLOW_STATUS_CODE = 'AWAITING_RECEIPT'
212 AND OD.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
213 AND RT.TRANSACTION_ID = l_transaction_id
214 ORDER BY 1 desc;
215 --
216 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
217 --
218 -- MOAC
219 l_access_mode VARCHAR2(1);
220 l_current_org_id NUMBER;
221 l_reset_policy BOOLEAN;
222 BEGIN
223
224 IF l_debug_level > 0 THEN
225 oe_debug_pub.add('*** Entering Dropshipreceive() in OE_DS_PVT *** ' , 1 ) ;
226 oe_debug_pub.add('Proces Online Mode:'||p_mode , 1 ) ;
227 oe_debug_pub.add('Transaction ID :' || P_RCV_TRANSACTION_ID , 1 ) ;
228 oe_debug_pub.add('Application Short Name :' || P_APPLICATION_SHORT_NAME , 1 ) ;
229 END IF;
230
231 l_access_mode := mo_global.Get_access_mode(); -- MOAC
232 l_current_org_id := mo_global.get_current_org_id();
233
234 -- Fetch sales order line identifier. If selection fail, it means
235 -- this receiving transaction is not DropShipment associated.
236 -- Return success if selection fail.
237 BEGIN
238 --{ Bug2407918. run the loop only once.
239 FOR i IN C1 LOOP
240 l_line_id := i.line_id;
241 G_LINE_LOCATION_ID := i.line_location_id; --bug 4402566
242 l_po_header_id := i.po_header_id; --bug 4402566
243 l_so_ou_id := i.org_id; --bug 4402566
244 l_transaction_date := i.transaction_date;
245 l_orig_shipped := i.shp_qty;
246 EXIT;
247 END LOOP;
248
249 IF l_line_id IS NULL THEN
250 IF l_debug_level > 0 THEN
251 oe_debug_pub.add( 'Unable to find the sales order identifier for this transaction '||sqlerrm , 1 ) ;
252 END IF;
253 IF l_debug_level > 0 THEN
254 oe_debug_pub.add( 'Dropshipreceive(), This may not be a dropship transaction ' , 1 ) ;
255 END IF;
256 RETURN TRUE;
257 END IF;
258 -- Bug 2407918}
259
260 -- bug 4393738 , moved query_row here to set the msg_context info
261 OE_Line_Util.Query_Row(p_line_id => l_line_id, x_line_rec => l_line_rec);
262 OE_MSG_PUB.set_msg_context(
263 p_entity_code => 'LINE'
264 ,p_entity_id => l_line_rec.line_id
265 ,p_header_id => l_line_rec.header_id
266 ,p_line_id => l_line_rec.line_id
267 ,p_order_source_id => l_line_rec.order_source_id
268 ,p_orig_sys_document_ref => l_line_rec.orig_sys_document_ref
269 ,p_orig_sys_document_line_ref => l_line_rec.orig_sys_line_ref
270 ,p_orig_sys_shipment_ref => l_line_rec.orig_sys_shipment_ref
271 ,p_change_sequence => l_line_rec.change_sequence
272 ,p_source_document_type_id => l_line_rec.source_document_type_id
273 ,p_source_document_id => l_line_rec.source_document_id
274 ,p_source_document_line_id => l_line_rec.source_document_line_id
275 );
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 FND_MESSAGE.SET_NAME('OE','OE_VAL_ORDER_CREDIT');
280 OE_MSG_PUB.Add;
281 IF l_debug_level > 0 THEN
282 oe_debug_pub.add( 'Unable to find the sales order identifier in dropshipreceive()'||sqlerrm , 1 ) ;
283 END IF;
284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
285 END;
286
287 ---------------------------------------
288 -- Changes for Enhanced Drop Shipments
289 ---------------------------------------
290
291 -- Set the Context only for Enhanced Drop Ships.
292 /*
293 SELECT org_id
294 INTO l_so_ou_id
295 FROM oe_order_lines_all
296 WHERE line_id = l_line_id;
297
298
299 SELECT pol.org_id
300 INTO l_po_ou_id
301 FROM po_lines_all pol,
302 oe_drop_ship_sources ds
303 WHERE ds.line_id = l_line_id
304 AND ds.po_header_id = pol.po_header_id
305 AND ds.po_line_id = pol.po_line_id;
306 */
307 -- commented for bug 4402566 and the sql below was added
308 SELECT poh.org_id
309 INTO l_po_ou_id
310 FROM po_headers_all poh
311 WHERE poh.po_header_id = l_po_header_id;
312
313 IF l_debug_level > 0 THEN
314 oe_debug_pub.add('SO Org Id: ' ||l_so_ou_id , 1 ) ;
315 oe_debug_pub.add('PO Org Id: ' ||l_po_ou_id , 1 ) ;
316 END IF;
317
318 IF l_po_ou_id <> l_so_ou_id THEN
319 -- MOAC
320 Mo_Global.Set_Policy_Context (p_access_mode =>'S', p_org_id => l_so_ou_id);
321 l_reset_policy := TRUE;
322 ELSE
323 IF nvl(l_current_org_id,-99) <> l_so_ou_id THEN
324 Mo_Global.Set_Policy_Context (p_access_mode => 'S', p_org_id => l_so_ou_id);
325 l_reset_policy := TRUE;
326 END IF;
327
328 /* Commented for MOAC
329 OE_ORDER_CONTEXT_GRP.Set_Created_By_Context
330 (p_header_id => NULL
331 ,p_line_id => l_line_id
332 ,x_orig_user_id => l_orig_user_id
333 ,x_orig_resp_id => l_orig_resp_id
334 ,x_orig_resp_appl_id => l_resp_appl_id
335 ,x_return_status => l_return_status
336 ,x_msg_count => l_msg_count
337 ,x_msg_data => l_msg_data
338 ); */
339 END IF;
340
341
342 IF l_debug_level > 0 THEN
343 oe_debug_pub.add('Transaction date : ' ||l_transaction_date , 1 ) ;
344 oe_debug_pub.add('Line ID : '||l_line_id||' already received qty => '||TO_CHAR ( l_orig_shipped) , 1 ) ;
345 END IF;
346
347 l_order_uom := l_line_rec.order_quantity_uom;
348 l_orig_short_quantity := nvl(l_line_rec.ordered_quantity,0) - nvl(l_line_rec.shipped_quantity,0);
349
350 /* OPM changes */
351 l_uom2 := l_line_rec.ordered_quantity_uom2;
352 l_orig_short_quantity2 := nvl(l_line_rec.ordered_quantity2,0)
353 - nvl(l_line_rec.shipped_quantity2,0);
354
355 IF l_debug_level > 0 THEN
356 oe_debug_pub.add( 'Open quantity in the sales order => ' || L_ORIG_SHORT_QUANTITY , 1 ) ;
357 END IF;
358 l_short_quantity := l_orig_short_quantity;
359 l_short_quantity2 := l_orig_short_quantity2;
360
361 /*
362 ** Fetch receiving transaction information: lot number, item revision
363 ** subinventory, receiving quantity and transactable flag.
364 */
365 /* Bug2197831:Added a decode statement to check the revision of
366 item-REVISION_QTY_CONTROL_CODE in MTL_SYSTEM_ITEMS and pass the
367 value of the revision or NULL accordingly.
368 */
369 BEGIN
370
371 SELECT rt.quantity,
372 rt.unit_of_measure,
373 rt.uom_code,
374 rt.secondary_quantity, -- OPM
375 rt.secondary_unit_of_measure, -- OPM
376 rt.organization_id,
377 rt.subinventory,
378 NVL(msinv.reservable_type, 2),
379 rt.locator_id,
380 rs.item_id,
381 decode(mi.revision_qty_control_code,2,rs.item_revision,NULL),
382 mi.mtl_transactions_enabled_flag,
383 mi.serial_number_control_code,
384 mi.auto_serial_alpha_prefix,
385 rt.transfer_lpn_id, --bug 3544019
386 nvl(rt.PRIMARY_QUANTITY,0) --12794393
387 INTO l_rcv_quantity,
388 l_unit_descr,
389 l_rcv_uom,
390 l_rcv_quantity2, -- OPM
391 l_unit2, -- OPM
392 l_organization_id,
393 l_subinventory,
394 l_sub_reservable,
395 l_locator_id,
396 l_item_id,
397 l_revision,
398 l_transactable,
399 l_sn_control_code,
400 l_as_alpha_prefix,
401 l_transfer_lpn_id, -- bug 3544019
402 l_rcv_primary_quantity -- 12794393
403 FROM mtl_system_items mi,
404 mtl_secondary_inventories msinv,
405 rcv_shipment_lines rs,
406 rcv_transactions rt
407 WHERE rt.transaction_id = l_transaction_id
408 AND rs.shipment_line_id = rt.shipment_line_id
409 AND mi.organization_id = rt.organization_id
410 AND mi.inventory_item_id = rs.item_id
411 AND msinv.organization_id(+) = rt.organization_id
412 AND msinv.secondary_inventory_name(+) = rt.subinventory;
413
414 IF l_debug_level > 0 THEN
415 oe_debug_pub.add( 'l_revision - '||L_REVISION ) ;
416 END IF;
417
418
419 EXCEPTION
420 WHEN NO_DATA_FOUND THEN
421 IF l_debug_level > 0 THEN
422 oe_debug_pub.add( 'SQL: Fail to retrieve the receiving information '||sqlerrm , 1 ) ;
423 END IF;
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
425 WHEN OTHERS THEN
426 IF l_debug_level > 0 THEN
427 oe_debug_pub.add( 'Error while retrieving receiving information '||sqlerrm , 1 ) ;
428 END IF;
429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
430 END;
431
432 IF l_debug_level > 0 THEN
433 oe_debug_pub.add( 'Ordered uom code => '||L_ORDER_UOM , 5 ) ;
434 END IF;
435 IF l_debug_level > 0 THEN
436 oe_debug_pub.add( 'Received uom code => '||L_RCV_UOM , 5 ) ;
437 END IF;
438
439 IF l_debug_level > 0 THEN
440 oe_debug_pub.add( 'Receiving the items in the subinventory => '||L_SUBINVENTORY , 1 ) ;
441 oe_debug_pub.add( 'Primary Received Qty => '||L_RCV_PRIMARY_QUANTITY , 1 ) ; -- 12794393
442 END IF;
443
444 l_line_rec.subinventory := l_subinventory;
445
446 /*
447 ** There might be mutiple lots associated with one receiving
448 ** transaction. while loop to create a reservation for each lot.
449 */
450
451 DECLARE CURSOR transaction_info IS
452 SELECT rl.lot_num
453 ,NVL( rl.quantity, -1)
454 ,rl.secondary_quantity -- OPM
455 -- ,rl.sublot_num -- OPM
456 ,rt.transaction_id
457 ,Nvl(rl.primary_quantity,0) -- 12794393
458 FROM rcv_lot_transactions rl,
459 rcv_transactions rt
460 WHERE rt.transaction_id = l_transaction_id
461 AND rl.transaction_id (+) = rt.transaction_id;
462
463
464 BEGIN
465
466 OPEN transaction_info;
467
468 FETCH transaction_info INTO
469 l_lot,
470 l_lot_quantity,
471 l_lot_quantity2,
472 -- l_sublot_no, -- INVCONV
473 l_transaction_id,
474 l_primary_quantity; -- 12794393
475
476 IF (l_lot_quantity = -1 ) THEN /* no lot associated */
477 l_lot_quantity := l_rcv_quantity;
478 l_lot_quantity2 := l_rcv_quantity2;
479 l_primary_quantity := l_rcv_primary_quantity; -- 12794393
480 END IF;
481
482
483 IF l_debug_level > 0 THEN
484 oe_debug_pub.add('Open qty available for receipt => ' || L_SHORT_QUANTITY , 1 ) ;
485 END IF;
486
487 LOOP
488
489 /*
490 ** Will use this sequence for inserting all records in
491 ** MTL_INTERFACE tables during decrement inventory
492 */
493
494 SELECT mtl_material_transactions_s.nextval
495 INTO l_lot_set_id
496 FROM dual;
497
498 IF l_lot_quantity <> -1 THEN
499 IF l_debug_level > 0 THEN
500 oe_debug_pub.add(' Lot number => ' || l_lot , 5 ) ;
501 END IF;
502 IF l_debug_level > 0 THEN
503 oe_debug_pub.add(' Lot quantity => ' || l_lot_quantity , 5 ) ;
504 oe_debug_pub.add(' Lot quantity2 => ' || l_lot_quantity2 , 5 ) ; -- INVCONV
505 oe_debug_pub.add(' Lot transaction id => ' || L_TRANSACTION_ID , 1 ) ; -- 12794393
506 oe_debug_pub.add(' Primary receiving qty => ' || l_primary_quantity , 1 ) ; -- 12794393
507 END IF;
508 END IF;
509
510 IF (l_lot_quantity <= 0) THEN
511 goto end_loop;
512 END IF;
513
514
515 /* OPM uom2 can not be changed */
516 l_reserve_quantity2 := nvl(l_lot_quantity2,0);
517
518 /* Reserve the quantities in this lot */
519
520 IF l_order_uom <> l_rcv_uom THEN
521 IF l_debug_level > 0 THEN
522 oe_debug_pub.add( 'Converting the unit of measurement ' , 5 ) ;
523 END IF;
524 --l_converted_qty := OE_ORDER_MISC_UTIL.CONVERT_UOM (l_item_id,l_rcv_uom,l_order_uom,l_lot_quantity);
525 -- Bug 12794393 Added two new IN parameters to handle the lot specific conversion
526
527 l_converted_qty := OE_ORDER_MISC_UTIL.CONVERT_UOM (p_item_id => l_item_id
528 ,p_from_uom_code => l_rcv_uom
529 ,p_to_uom_code => l_order_uom
530 ,p_from_qty => l_lot_quantity
531 ,p_lot_number => l_lot -- 12794393
532 ,p_organization_id => l_organization_id -- 12794393
533 );
534
535 /* -- need forking for OPM UOM conv INVCONV
536
537 IF (INV_GMI_RSV_BRANCH.Process_Branch(l_line_rec.ship_from_org_id)) THEN
538
539 -- This code is commented can we delete this code
540 -- OPM Feb 2003 2683316 - changed the call to GMI
541 -- uom_conversion and Get_OPMUOM_from_AppsUOM above to
542 -- get_opm_converted_qty to resolve rounding issues
543
544
545 l_converted_qty := GMI_Reservation_Util.get_opm_converted_qty(
546 p_apps_item_id => l_line_rec.inventory_item_id,
547 p_organization_id => l_line_rec.ship_from_org_id,
548 p_apps_from_uom => l_rcv_uom,
549 p_apps_to_uom => l_order_uom,
550 p_original_qty => l_lot_quantity);
551
552 GMI_Reservation_Util.Println(' OPM converted qty in proc DropShipReceive '||
553 ' after new get_opm_converted_qty is '|| l_converted_qty);
554 --OPM Feb 2003 2683316 end
555
556 END IF;
557 -- end OPM forking for UOM conv
558 */
559
560 l_short_quantity := l_short_quantity - l_converted_qty;
561 l_add_to_shipped := l_add_to_shipped + l_converted_qty;
562 l_qty_to_be_reserved := l_converted_qty;
563
564 ELSE
565 l_add_to_shipped := l_add_to_shipped + l_lot_quantity;
566 l_short_quantity := l_short_quantity - l_lot_quantity ;
567 l_qty_to_be_reserved := l_lot_quantity;
568 END IF;
569
570 -- dual uom2 can not be changed INVCONV
571
572 l_short_quantity2 := l_short_quantity2 - nvl(l_lot_quantity2,0);
573 l_add_to_shipped2 := l_add_to_shipped2 + nvl(l_lot_quantity2,0);
574 l_qty2_to_be_reserved := l_lot_quantity2; -- INVCONV
575 IF l_debug_level > 0 THEN
576 oe_debug_pub.add( 'Is subinventory reservable ? '||l_sub_reservable , 1 ) ;
577 END IF;
578
579 BEGIN
580 SELECT NVL(reservable_type,2)
581 INTO l_item_reservable
582 FROM mtl_system_items
583 WHERE inventory_item_id = l_line_rec.inventory_item_id
584 AND organization_id = l_line_rec.ship_from_org_id;
585
586 IF l_debug_level > 0 THEN
587 oe_debug_pub.add( 'Is item reservable ? '||l_item_reservable , 1 ) ;
588 END IF;
589
590 EXCEPTION WHEN OTHERS THEN
591 IF l_debug_level > 0 THEN
592 oe_debug_pub.add( 'Error while checking item reservable ? '||sqlerrm , 1 ) ;
593 END IF;
594 l_item_reservable := 2;
595 END;
596
597
598 IF p_application_short_name = 'INV' -- INVCONV AND
599 -- (NOT INV_GMI_RSV_BRANCH.Process_Branch(l_line_rec.ship_from_org_id)) INVCONV
600 AND p_mode = 0 THEN
601 IF l_sub_reservable = 1 AND l_item_reservable = 1 THEN
602 Create_reservation
603 (p_qty_to_be_reserved => l_qty_to_be_reserved
604 ,p_qty2_to_be_reserved => l_qty2_to_be_reserved -- INVCONV
605 ,p_revision => l_revision
606 ,p_locator_id => l_locator_id
607 ,p_lot => l_lot
608 ,p_line_rec => l_line_rec
609 ,x_qty_reserved => l_quantity_reserved
610 ,x_qty2_reserved => l_quantity2_reserved -- INVCONV
611 ,x_rsv_id => l_rsv_id
612 ,x_return_status => l_return_status
613 ,p_transfer_lpn_id => l_transfer_lpn_id
614 ,p_prim_reservation_qty => l_primary_quantity -- 12794393
615 );
616 -- bug 3544019
617
618 IF l_return_status = FND_API.G_RET_STS_ERROR then
619 IF l_debug_level > 0 THEN
620 oe_debug_pub.add('Call to Create Reservation returned expected error '||sqlerrm,1) ;
621 END IF;
622 RAISE FND_API.G_EXC_ERROR;
623 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
624 IF l_debug_level > 0 THEN
625 oe_debug_pub.add('Call to Create Reservation returned unexpected error '||sqlerrm , 1 ) ;
626 END IF;
627 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
628 END IF;
629
630 ELSE
631 IF l_debug_level > 0 THEN
632 oe_debug_pub.add('Item OR Subinventory Non-reservable ' , 1 ) ;
633 END IF;
634 l_quantity_reserved := l_qty_to_be_reserved;
635 l_quantity2_reserved := l_qty2_to_be_reserved; -- INVCONV
636 END IF;
637
638 END IF;
639
640 IF l_debug_level > 0 THEN
641 oe_debug_pub.add( 'After reservation check' , 1 ) ;
642 END IF;
643
644
645 -- Decrement Inventory
646
647 l_chart_of_accts := Get_Char_of_accts (l_line_rec.ship_from_org_id);
648
649 IF p_application_short_name = 'INV' AND
650 p_mode = 0 THEN
651 /* forking code for OPM INVCONV NOT NEEDED NOW
652 IF (INV_GMI_RSV_BRANCH.Process_Branch(l_line_rec.ship_from_org_id))
653 THEN
654 IF l_debug_level > 0 THEN
655 oe_debug_pub.add( 'Calling decrement inventory for opm' , 1 ) ;
656 END IF;
657 Decrement_Inventory_for_OPM(
658 p_detail_id => l_rsv_id,
659 p_line_rec => l_line_rec,
660 p_transaction_id => l_transaction_id,
661 p_trans_qty => l_lot_quantity,
662 p_trans_qty2 => l_lot_quantity2,
663 p_inventory_item_id => l_line_rec.inventory_item_id,
664 p_delivery => null,
665 p_lot_number => l_lot,
666 p_sublot_no => l_sublot_no,
667 p_revision => l_revision,
668 p_locator_id => l_locator_id,
669 p_warehouse_id => l_organization_id,
670 p_chart_of_accts => l_chart_of_accts,
671 p_trx_uom => l_rcv_uom,
672 p_sn_control_code => l_sn_control_code,
673 p_as_alpha_prefix => l_as_alpha_prefix,
674 p_transaction_header_id => l_lot_set_id,
675 x_return_status => l_return_status);
676 IF l_debug_level > 0 THEN
677 oe_debug_pub.add('After calling decrement inventory for opm: ' || l_return_status,1) ;
678 END IF;
679 IF l_return_status = FND_API.G_RET_STS_ERROR then
680 RAISE FND_API.G_EXC_ERROR;
681 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
683 END IF;
684 ElSE */
685 IF l_debug_level > 0 THEN
686 oe_debug_pub.add( 'Calling decrement inventory()' , 1 ) ;
687 END IF;
688 Decrement_Inventory(
689 p_detail_id => l_rsv_id,
690 p_line_rec => l_line_rec,
691 p_transaction_id => l_transaction_id,
692 p_transaction_detail_qty => l_lot_quantity,
693 p_trans_qty2 => l_lot_quantity2, -- INVCONV
694 p_inventory_item_id => l_line_rec.inventory_item_id,
695 p_delivery => null,
696 p_lot_number => l_lot,
697 p_revision => l_revision,
698 p_secondary_inventory => l_subinventory,
699 p_locator_id => l_locator_id,
700 p_warehouse_id => l_organization_id,
701 p_chart_of_accts => l_chart_of_accts,
702 p_trx_uom => l_rcv_uom,
703 p_sn_control_code => l_sn_control_code,
704 p_as_alpha_prefix => l_as_alpha_prefix,
705 p_transaction_header_id => l_lot_set_id,
706 p_transfer_lpn_id => l_transfer_lpn_id, --3544019
707 x_return_status => l_return_status);
708
709 IF l_debug_level > 0 THEN
710 oe_debug_pub.add( 'After calling decrement inventory : ' || l_return_status,1) ;
711 END IF;
712
713 IF l_return_status = FND_API.G_RET_STS_ERROR then
714 IF l_debug_level > 0 THEN
715 oe_debug_pub.add( 'Call to decrementinventory() returned expected error '||sqlerrm,1) ;
716 END IF;
717 RAISE FND_API.G_EXC_ERROR;
718 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
719 IF l_debug_level > 0 THEN
720 oe_debug_pub.add( 'Call to decrementinventory() returned unexpected error '||sqlerrm , 1 ) ;
721 END IF;
722 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
723 END IF; -- IF l_return_status = FND_API.G_RET_STS_ERROR then -- INVCONV
724 --END IF; -- INVCONV
725 END IF;
726
727 FETCH transaction_info INTO
728 l_lot,
729 l_lot_quantity,
730 l_lot_quantity2,
731 --l_sublot_no, -- INVCONV
732 l_transaction_id,
733 l_primary_quantity; -- 12794393
734 EXIT WHEN transaction_info%NOTFOUND;
735 END LOOP;
736 << end_loop >>
737 CLOSE transaction_info;
738
739 IF l_debug_level > 0 THEN
740 oe_debug_pub.add( 'Final open quantity in the sales order => '||l_short_quantity,1) ;
741 END IF;
742
743 END;
744
745 -- Calling Process Order to update Sales Order Lines.
746
747 Call_Process_Order
748 (p_orig_shipped => l_orig_shipped
749 ,p_short_quantity => l_short_quantity
750 ,p_transaction_date => l_transaction_date
751 ,p_add_to_shipped => l_add_to_shipped
752 ,p_add_to_shipped2 => l_add_to_shipped2
753 ,p_line_rec => l_line_rec
754 ,x_return_status => l_return_status
755 );
756
757 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
758 IF l_debug_level > 0 THEN
759 oe_debug_pub.add('Call to process order returned unexpected error '||sqlerrm , 1 ) ;
760 END IF;
761 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
762 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
763 IF l_debug_level > 0 THEN
764 oe_debug_pub.add('Call to process order returned expected error '||sqlerrm , 1 ) ;
765 END IF;
766 RAISE FND_API.G_EXC_ERROR;
767 END IF;
768
769 /* Commented for MOAC
770 IF l_so_ou_id <> l_po_ou_id THEN
771
772 FND_GLOBAL.Apps_Initialize
773 (user_id => l_orig_user_id
774 ,resp_id => l_orig_resp_id
775 ,resp_appl_id => l_resp_appl_id);
776 END IF; */
777
778 -- Reset The Context for Enhanced Dropshipments.
779 IF l_reset_policy THEN -- MOAC
780 Mo_Global.Set_Policy_Context (p_access_mode => l_access_mode, p_org_id => l_current_org_id);
781 END IF;
782
783
784 IF l_debug_level > 0 THEN
785 oe_debug_pub.add('Exiting dropshipreceive() successfully ' , 1 ) ;
786 END IF;
787
788 OE_DEBUG_PUB.dumpdebug;
789 OE_DEBUG_PUB.Debug_Off;
790
791 RETURN TRUE;
792
793 EXCEPTION
794 WHEN FND_API.G_EXC_ERROR THEN
795 IF l_debug_level > 0 THEN
796 oe_debug_pub.add( 'Exiting dropshipreceive with exp. error => '||sqlerrm , 1 ) ;
797 END IF;
798 OE_MSG_PUB.Save_API_Messages(); -- bug 4393738
799 OE_DEBUG_PUB.dumpdebug;
800 OE_DEBUG_PUB.Debug_Off;
801 RETURN FALSE;
802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
803 IF l_debug_level > 0 THEN
804 oe_debug_pub.add( 'Exiting dropshipreceive with unexp. error => '||sqlerrm , 1 ) ;
805 END IF;
806 OE_MSG_PUB.Save_API_Messages(); -- bug 4393738
807 OE_DEBUG_PUB.dumpdebug;
808 OE_DEBUG_PUB.Debug_Off;
809 RETURN FALSE;
810 WHEN OTHERS THEN
811 IF l_debug_level > 0 THEN
812 oe_debug_pub.add( 'Exiting dropshipreceive with others error => '||sqlerrm , 1 ) ;
813 END IF;
814 OE_MSG_PUB.Save_API_Messages(); -- bug 4393738
815 OE_DEBUG_PUB.dumpdebug;
816 OE_DEBUG_PUB.Debug_Off;
817 RETURN FALSE;
818 END DropShipReceive;
819
820 /* --------------------------------------------------------------------
821 Procedure Name : Decrement_Inventory
822 Description : This procedure inserts records in the MTL_TRANSACTION...
823 tables. The inserted records will be later processed by
824 mtl_online_transaction_pub.process_online API (called
825 from the main dropshipreceive function, to decrement
826 inventory in the system.
827 This API is called in a loop from the DropShipReceive
828 function. The receiving transactions are stored in
829 RCV_TRANSACTIONS table and RCV_LOT_TRANSACTIONS (if there
830 are lot level transactions). This API will be called only
831 once (for the record in RCV_TRANSACTIONS) if not lots are
832 associated. If there are multiple lots in the transaction,
833 it will be called for every lot.
834
835 This API will insert records into
836
837 MTL_TRANSACTION_LOTS_INTERFACE: For every receipt in lot
838 (lot controlled items).
839 MTL_SERIAL_NUMBERS_INTERFACE : For every serial number
840 receipt (serial controlled
841 items.)
842 MTL_TRANSACTIONS_INTERFACE : For every transaction
843 (corresponds to qty recd
844 in RCV_TRANSACTIONS)
845
846 IMPORTANT: ALL THE RECORDS INSERTED IN THE ABOVE TABLE FOR A
847 PARTICULAR TRANSACTION HAVE THE SAME TRANSACTION_INTERFACE_ID.
848 We pass the p_transaction_header_id to this API which is
849 value we got from mtl_material_transactions_s sequence.
850 This value is assigned to the TRANSACTION_INTERFACE_ID.
851
852
853 ----------------------------------------------------------------------- */
854
855 Procedure Decrement_Inventory(
856 p_detail_id IN NUMBER,
857 p_line_rec IN OE_ORDER_PUB.line_rec_type,
858 p_transaction_id IN NUMBER,
859 p_transaction_detail_qty IN NUMBER,
860 p_trans_qty2 IN NUMBER, -- INVCONV
861 p_inventory_item_id IN NUMBER,
862 p_delivery IN NUMBER,
863 p_lot_number IN VARCHAR2,
864 p_revision IN VARCHAR2,
865 p_secondary_inventory IN VARCHAR2,
866 p_locator_id IN NUMBER,
867 p_warehouse_id IN NUMBER,
868 p_chart_of_accts IN NUMBER,
869 p_trx_uom IN VARCHAR2,
870 p_sn_control_code IN NUMBER,
871 p_as_alpha_prefix IN VARCHAR2,
872 p_transaction_header_id IN NUMBER,
873 p_transfer_lpn_id IN NUMBER, -- 3544019
874 x_return_status OUT NOCOPY VARCHAR2)
875
876 IS
877 l_source_line_id NUMBER;
878 l_lot_set_id NUMBER;
879 l_trans_acc NUMBER;
880 l_trx_source_type_id NUMBER := 2;
881 l_trx_action_id NUMBER := 1;
882 l_trx_type_code NUMBER := 33;
883 l_ord_num NUMBER;
884 --l_order_type_name VARCHAR2(30) := 'Standard'; bug 4456817
885 l_budget_acct_id NUMBER := -1;
886 l_project_id NUMBER := p_line_rec.project_id;
887 l_task_id NUMBER := p_line_rec.task_id;
888 l_transaction_reference NUMBER := 0;
889 --l_order_number NUMBER; bug 4456817
890 l_line_id NUMBER := p_line_rec.line_id;
891 l_shipment_line_id NUMBER := 0;
892 l_delivery NUMBER := -1;
893 l_dest_subinv VARCHAR2(30) := ' ';
894 l_to_org_id NUMBER := -1;
895 l_location_id NUMBER := 0;
896 l_req_line_id NUMBER := 0;
897 l_unit_price NUMBER;
898 l_concat_segs VARCHAR2(2000);
899 l_concat_ids VARCHAR2(2000);
900 l_concat_descrs VARCHAR2(2000);
901 l_serial_set_id NUMBER;
902 serial_counter NUMBER;
903 l_transaction_date DATE;
904 v_serial_number VARCHAR2(30);
905 v_serial_number_temp VARCHAR2(30);
906 l_msg_count NUMBER;
907 l_msg_data VARCHAR2(2000);
908 --l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
909 l_transaction_interface_id NUMBER := null;
910 l_transaction_header_id NUMBER := p_transaction_header_id;
911 l_transaction_source_id NUMBER ; /* sales_order_id */
912 l_converted_qty NUMBER; -- Bug-2311061
913 l_primary_uom VARCHAR2(3); -- Bug-2908567
914 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
915 BEGIN
916
917 IF l_debug_level > 0 THEN
918 oe_debug_pub.add(' Entering decrement inventory() ' ) ;
919 oe_debug_pub.add(' p_lot_number : ' || p_lot_number , 1 ) ;
920 oe_debug_pub.add(' p_revision : ' || p_revision , 1 ) ;
921 oe_debug_pub.add(' p_secondary_inventory : ' || p_secondary_inventory , 1 ) ;
922 oe_debug_pub.add(' p_inventory_item_id : ' || p_inventory_item_id , 1 ) ;
923 oe_debug_pub.add(' p_trx_uom : ' || p_trx_uom , 1 ) ;
924 oe_debug_pub.add(' p_chart_of_accts : ' || p_chart_of_accts , 1 ) ;
925 oe_debug_pub.add(' p_warehouse_id : ' || p_warehouse_id , 1 ) ;
926 oe_debug_pub.add(' p_as_alpha_prefix : ' || p_as_alpha_prefix , 1 ) ;
927 oe_debug_pub.add(' p_sn_control_code : ' || p_sn_control_code , 1 ) ;
928 oe_debug_pub.add(' p_transaction_id : ' || p_transaction_id , 1 ) ;
929 END IF;
930
931 -- bug 5357879
932 SAVEPOINT DECREMENT_INV;
933 x_return_status := FND_API.G_RET_STS_SUCCESS;
934
935 profile_values.oe_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
936 profile_values.user_id := FND_GLOBAL.USER_ID;
937 profile_values.login_id := FND_GLOBAL.LOGIN_ID;
938 profile_values.request_id := 0;
939 profile_values.application_id := 0;
940 profile_values.program_id := 0;
941
942 -- Bug # 4454055
943 -- Code is commented for Deferred Revenue Project
944 -- The COGS account generator workflow will no longer be called at shipping time, instead,
945 -- Inventory will stamp deferred cogs account on MMT transactions.
946 -- The deferred cogs account can be defined at each inventory org level.
947 -- When revenue is recognized, Costing will get notified and call the OM COGS
948 -- account generator to get the cogs account and recognize cogs in the same period where
949 -- revenue is recognized. Also when an order line is closed without getting invoiced,
950 -- cogs will be recognized at the closing time assuming there would be no future revenue recognition event.
951
952 --
953 -- 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.
954 --
955
956 -- Start Deferred Revenue Project
957
958
959
960 IF OE_FLEX_COGS_PUB.Start_Process (
961 p_api_version_number => 1.0,
962 p_line_id => p_line_rec.line_id,
963 x_return_ccid => l_trans_acc,
964 x_concat_segs => l_concat_segs,
965 x_concat_ids => l_concat_ids,
966 x_concat_descrs => l_concat_descrs,
967 x_msg_count => l_msg_count,
968 x_msg_data => l_msg_data) <> FND_API.G_RET_STS_SUCCESS
969 THEN
970
971 l_trans_acc := NULL;
972 END IF;
973
974
975 IF l_debug_level > 0 THEN
976 oe_debug_pub.add( 'Transaction account id : ' ||l_trans_acc,1 ) ;
977 END IF;
978
979
980
981 -- End Deferred Revenue Project
982
983
984 /* SELECT oe_transactions_iface_s.nextval
985 INTO l_source_line_id
986 FROM dual; */
987
988 -- Change for #2736818
989 l_source_line_id := p_line_rec.line_id; -- Sales Order Line ID
990 l_lot_set_id := p_transaction_header_id;
991 IF l_debug_level > 0 THEN
992 oe_debug_pub.add( 'Source line id => ' || l_source_line_id , 1 ) ;
993 oe_debug_pub.add( 'Selecting unique id for this transaction' , 1 ) ;
994 oe_debug_pub.add( 'Transaction header ID :' || l_lot_set_id, 1 ) ;
995 END IF;
996
997 IF (p_lot_number is not null) THEN
998
999 /* Insert lot transaction interface table */
1000
1001 IF l_debug_level > 0 THEN
1002 oe_debug_pub.add( 'Inserting lots' ) ;
1003 END IF;
1004 INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
1005 (
1006 SOURCE_CODE,
1007 SOURCE_LINE_ID,
1008 TRANSACTION_INTERFACE_ID,
1009 LOT_NUMBER,
1010 TRANSACTION_QUANTITY,
1011 LAST_UPDATE_DATE,
1012 LAST_UPDATED_BY,
1013 CREATION_DATE,
1014 CREATED_BY,
1015 SERIAL_TRANSACTION_TEMP_ID,
1016 ERROR_CODE,
1017 PROCESS_FLAG )
1018 VALUES
1019 (
1020 profile_values.oe_source_code,
1021 l_source_line_id,
1022 l_lot_set_id,
1023 p_lot_number,
1024 p_transaction_detail_qty,
1025 sysdate,
1026 profile_values.user_id,
1027 sysdate,
1028 profile_values.user_id,
1029 null,
1030 null,
1031 'Y');
1032
1033 END IF; /* end insert lot */
1034
1035 /*
1036 Inserting into serial number interface
1037
1038 1. If it is a predefined serial number controlled item
1039 i.e. serial number control code = 2
1040 then when doing an issue transaction ,get the
1041 serial numbers from prior receipt transactions for the
1042 same item,warehouse,locator combination.
1043 2. If it is a serial controlled item with dynamic generation
1044 at receipt time i.e. serial number control code = 5
1045 logic is same as above when doing an issue transaction.
1046 3. If it is a serial controlled item with dynamic generation
1047 at issue time generate the serial numbers with appropriate prefix .
1048 There is 1 serial number per quantity.
1049
1050 */
1051
1052 IF (p_sn_control_code = 2 OR
1053 p_sn_control_code = 5 OR
1054 p_sn_control_code = 6)
1055 THEN
1056
1057 SELECT mtl_material_transactions_s.nextval
1058 INTO l_serial_set_id
1059 FROM dual;
1060
1061 DECLARE
1062 --modified the following cursor for bug 6012741
1063 CURSOR get_received_serial_number IS
1064 SELECT rtrim(ltrim(msn.serial_number))
1065 FROM mtl_serial_numbers msn
1066 WHERE msn.inventory_item_id = p_inventory_item_id
1067 AND msn.current_organization_id = p_warehouse_id
1068 AND nvl(msn.current_subinventory_code,' ') = p_secondary_inventory
1069 AND nvl(msn.current_locator_id,0) = nvl(p_locator_id,0)
1070 AND msn.current_status=3
1071 AND msn.group_mark_id is NULL
1072 AND exists (select 1 from mtl_material_transactions mmt, rcv_transactions rt
1073 where mmt.transaction_id = msn.last_transaction_id
1074 and mmt.transaction_source_type_id = 1
1075 and rt.transaction_id = p_transaction_id
1076 and rt.transaction_id = mmt.rcv_transaction_id
1077 and msn.last_txn_source_id = rt.po_header_id);
1078
1079 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1080 BEGIN
1081
1082 IF l_debug_level > 0 THEN
1083 oe_debug_pub.add( 'Inserting serial numbers after serial numbers fix' , 1 ) ;
1084 END IF;
1085
1086 IF (p_sn_control_code = 2 OR p_sn_control_code = 5) THEN
1087 IF l_debug_level > 0 THEN
1088 oe_debug_pub.add( 'Serial control code = 2 or 5' , 1 ) ;
1089 END IF;
1090 OPEN get_received_serial_number;
1091 ELSIF (p_sn_control_code = 6) THEN
1092 IF l_debug_level > 0 THEN
1093 oe_debug_pub.add( 'Serial control code = 6' , 1 ) ;
1094 END IF;
1095 END IF;
1096
1097 serial_counter := 0;
1098
1099 -- If Order UOM and recieving UOM are not equal convert the
1100 -- quantity to insert serial numbers. Bug-2311061
1101 -- If Recieving UOM and Primary UOM are not same
1102 -- Convert the quantity to insert serial numbers Bug - 2908567
1103
1104 BEGIN
1105 SELECT primary_uom_code
1106 INTO l_primary_uom
1107 FROM mtl_system_items
1108 WHERE inventory_item_id = p_line_rec.inventory_item_id
1109 AND organization_id = p_line_rec.ship_from_org_id;
1110 EXCEPTION
1111 WHEN NO_DATA_FOUND THEN
1112 NULL;
1113 END ;
1114
1115 IF l_primary_uom <> p_trx_uom THEN
1116 IF l_debug_level > 0 THEN
1117 oe_debug_pub.add('Converting the unit of measurement ' , 1 ) ;
1118 oe_debug_pub.add('Primary UOM:'||l_primary_uom , 1 ) ;
1119 oe_debug_pub.add('Transaction UOM:'||p_trx_uom , 1 ) ;
1120 END IF;
1121 l_converted_qty := OE_ORDER_MISC_UTIL.CONVERT_UOM (
1122 p_line_rec.inventory_item_id,
1123 p_trx_uom,
1124 l_primary_uom,
1125 p_transaction_detail_qty);
1126 ELSE
1127 l_converted_qty := p_transaction_detail_qty;
1128 END IF;
1129
1130 while serial_counter < l_converted_qty LOOP
1131
1132 IF (p_sn_control_code = 6) THEN
1133 IF l_debug_level > 0 THEN
1134 oe_debug_pub.add( 'in for loop s.n control code = 6' , 1 ) ;
1135 END IF;
1136 SELECT to_char(oe_mtl_sn_interface_s.nextval)
1137 INTO v_serial_number_temp
1138 FROM dual ;
1139 IF p_as_alpha_prefix is not null THEN
1140 v_serial_number := p_as_alpha_prefix || v_serial_number_temp;
1141 ELSE
1142 v_serial_number := v_serial_number_temp;
1143 END IF;
1144 IF l_debug_level > 0 THEN
1145 oe_debug_pub.add( 'Final serial number is ' || V_SERIAL_NUMBER , 1 ) ;
1146 END IF;
1147 ELSIF (p_sn_control_code = 2 OR p_sn_control_code = 5) THEN
1148 IF l_debug_level > 0 THEN
1149 oe_debug_pub.add( 'Logic for s.n control code = 2 or 5 ' , 1 ) ;
1150 END IF;
1151 FETCH get_received_serial_number INTO v_serial_number;
1152 IF l_debug_level > 0 THEN
1153 oe_debug_pub.add( 'Serial number fetched is => ' || v_serial_number , 1 ) ;
1154 END IF;
1155 END IF;
1156
1157 BEGIN
1158
1159 IF l_debug_level > 0 THEN
1160 oe_debug_pub.add( 'Inserting into mtl_serial_numbers_interface table ' , 1 ) ;
1161 END IF;
1162
1163 insert into mtl_serial_numbers_interface
1164 (transaction_interface_id,
1165 source_code,
1166 source_line_id,
1167 last_update_date,
1168 last_updated_by,
1169 creation_date,
1170 created_by,
1171 last_update_login,
1172 request_id,
1173 program_application_id,
1174 program_id,
1175 program_update_date,
1176 vendor_serial_number,
1177 vendor_lot_number,
1178 fm_serial_number,
1179 to_serial_number,
1180 error_code,
1181 process_flag
1182 )
1183 values
1184 (
1185 l_serial_set_id, /*transaction_interface_id */
1186 profile_values.oe_source_code, /*source_code */
1187 l_source_line_id, /*source_line_id */
1188 sysdate, /*last_update_date*/
1189 profile_values.user_id, /*last_updated_by */
1190 sysdate , /*creation_date */
1191 profile_values.user_id, /*created by */
1192 profile_values.login_id , /*last_update_login */
1193 profile_values.request_id, /*request_id */
1194 profile_values.application_id, /*program_application_id */
1195 profile_values.program_id, /*program_id */
1196 sysdate, /*program_update_date */
1197 null, /* vendor_serial_number */
1198 null, /* vendor_lot_number */
1199 v_serial_number, /* fm_serial_number */
1200 null, /*to_serial_number */
1201 null, /* error_code */
1202 null /* process_flag */
1203 ) ;
1204
1205 EXCEPTION
1206 WHEN OTHERS THEN
1207 IF l_debug_level > 0 THEN
1208 oe_debug_pub.add( 'Failed inserting into mtl_serial_numbers_interface' , 1 ) ;
1209 END IF;
1210 -- bug 5357879
1211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1212 ROLLBACK TO DECREMENT_INV;
1213
1214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1215 END;
1216
1217 /* Update the column group_mark_id with line_id, so that
1218 this serial number will not be used by another transaction
1219 and thus avoiding duplicates */
1220
1221 IF ((p_sn_control_code = 2) OR
1222 (p_sn_control_code = 5))
1223 THEN
1224
1225 BEGIN
1226 UPDATE mtl_serial_numbers
1227 SET GROUP_MARK_ID = p_line_rec.line_id
1228 where inventory_item_id = p_inventory_item_id
1229 and current_organization_id = p_warehouse_id
1230 and nvl(current_locator_id,0) = nvl(p_locator_id,0)
1231 and serial_number = v_serial_number;
1232
1233 IF l_debug_level > 0 THEN
1234 oe_debug_pub.add( 'Serial number updated is :' || v_serial_number , 1 ) ;
1235 END IF;
1236 EXCEPTION
1237 WHEN OTHERS THEN
1238 IF l_debug_level > 0 THEN
1239 oe_debug_pub.add( 'Updating serial numbers failed '||sqlerrm , 1 ) ;
1240 END IF;
1241
1242 -- bug 5357879
1243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1244 ROLLBACK TO DECREMENT_INV;
1245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1246
1247 END;
1248 END IF;
1249 serial_counter := serial_counter + 1;
1250 END LOOP; /* while serial_counter < p_transaction_detail_qty */
1251
1252 END;
1253 END IF; /* end serial number logic */
1254
1255 IF (p_lot_number is not null) AND
1256 (p_sn_control_code = 2 OR
1257 p_sn_control_code = 5 OR
1258 p_sn_control_code = 6)
1259 THEN
1260 IF l_debug_level > 0 THEN
1261 oe_debug_pub.add( 'This is serial+lot controlled item' , 1 ) ;
1262 END IF;
1263
1264 BEGIN
1265
1266 UPDATE MTL_TRANSACTION_LOTS_INTERFACE
1267 SET SERIAL_TRANSACTION_TEMP_ID = l_serial_set_id
1268 WHERE TRANSACTION_INTERFACE_ID = l_lot_set_id;
1269
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 IF l_debug_level > 0 THEN
1273 oe_debug_pub.add( 'Updating serial_transaction_temp_id failed '||sqlerrm , 1 ) ;
1274 END IF;
1275
1276 -- bug 5357879
1277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1278 ROLLBACK TO DECREMENT_INV;
1279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1280 END;
1281
1282 END IF;
1283
1284 /* Determine the Transaction Interface ID for this transaction */
1285
1286 if (p_lot_number is null) AND
1287 (p_sn_control_code = 2 OR p_sn_control_code = 5 OR p_sn_control_code = 6) then
1288 l_transaction_interface_id := l_serial_set_id;
1289 else
1290 l_transaction_interface_id := l_lot_set_id;
1291 end if;
1292
1293 IF l_debug_level > 0 THEN
1294 oe_debug_pub.add( 'Populating transaction_interface_id in mti as '||L_TRANSACTION_INTERFACE_ID , 1 ) ;
1295 END IF;
1296
1297 BEGIN
1298 SELECT RT.TRANSACTION_DATE
1299 INTO l_transaction_date
1300 FROM RCV_TRANSACTIONS RT
1301 WHERE RT.TRANSACTION_ID = p_transaction_id;
1302 EXCEPTION
1303 WHEN NO_DATA_FOUND THEN
1304 l_transaction_date := sysdate;
1305 WHEN OTHERS THEN
1306 l_transaction_date := sysdate;
1307 END;
1308
1309 l_transaction_reference := p_line_rec.header_id;
1310 l_transaction_source_id := get_mtl_sales_order_id(p_line_rec.header_id);
1311 /* commented for bug 4456817
1312 BEGIN
1313 SELECT h.order_number,ot.name
1314 INTO l_order_number,l_order_type_name
1315 FROM oe_order_headers_all h, oe_order_types_v ot
1316 WHERE h.header_id = p_line_rec.header_id AND
1317 ot.order_type_id = h.order_type_id;
1318 EXCEPTION
1319 WHEN NO_DATA_FOUND THEN
1320 l_transaction_date := sysdate;
1321 WHEN OTHERS THEN
1322 l_transaction_date := sysdate;
1323 END; */
1324
1325 IF l_debug_level > 0 THEN
1326 oe_debug_pub.add('Inserting header record' ) ;
1327 oe_debug_pub.add('Source_code : ' || fnd_profile.value ( 'ont_source_code' ) , 1 ) ;
1328 oe_debug_pub.add('Source_line_id : ' || l_source_line_id , 1 ) ;
1329 oe_debug_pub.add('Source_header_id : ' || l_transaction_reference , 1 ) ;
1330 oe_debug_pub.add('Process_flag : ' || 1 , 1 ) ;
1331 oe_debug_pub.add('Transaction_mode : ' || 1 , 1 ) ;
1332 oe_debug_pub.add('Lock_flag : ' || 2 , 1 ) ;
1333 oe_debug_pub.add('Transaction_header_id : ' || l_transaction_header_id , 1 ) ;
1334 oe_debug_pub.add('Inventory_item_id : ' || p_inventory_item_id , 1 ) ;
1335 oe_debug_pub.add('Subinventory_code : ' || p_secondary_inventory , 1 ) ;
1336 oe_debug_pub.add('Transaction_quantity : ' || ( -1 * p_transaction_detail_qty ) , 1 ) ;
1337 oe_debug_pub.add('Transaction_quantity2 : ' || ( -1 * p_trans_qty2 ) , 1 ) ; -- INVCONV
1338 oe_debug_pub.add('Transaction_date : ' || l_transaction_date , 1 ) ;
1339 oe_debug_pub.add('Organization_id : ' || p_warehouse_id , 1 ) ;
1340 oe_debug_pub.add('Transfer_lpn_id : ' || p_transfer_lpn_id,1) ; -- 3544019
1341 oe_debug_pub.add('Acct_period_id : ' || null , 1 ) ;
1342 oe_debug_pub.add('Last_update_date : ' || sysdate , 1 ) ;
1343 oe_debug_pub.add('Last_updated_by : ' || fnd_global.user_id , 1 ) ;
1344 oe_debug_pub.add('Creation_date : ' || sysdate , 1 ) ;
1345 oe_debug_pub.add('Created_by : ' || fnd_global.user_id , 1 ) ;
1346 oe_debug_pub.add('Transaction_source_id : ' || l_transaction_source_id , 1 ) ;
1347 -- oe_debug_pub.add('Dsp_segment1 : ' || l_order_number , 1 ) ; bug 4456817
1348 -- oe_debug_pub.add('Dsp_segment2 : ' || l_order_type_name , 1 ) ;
1349 -- oe_debug_pub.add('Dsp_segment3 : ' || fnd_profile.value ( 'ont_source_code' ) , 1 ) ;
1350 oe_debug_pub.add('Transaction_source_type_id : ' || l_trx_source_type_id , 1 ) ;
1351 oe_debug_pub.add('Transaction_action_id : ' || l_trx_action_id , 1 ) ;
1352 oe_debug_pub.add('Transaction_type_id : ' || l_trx_type_code , 1 ) ;
1353 oe_debug_pub.add('Distribution_account_id : ' || l_trans_acc , 1 ) ;
1354 oe_debug_pub.add('Transaction_reference : ' || l_transaction_reference , 1 ) ;
1355 oe_debug_pub.add('Trx_source_line_id : ' || l_line_id , 1 ) ;
1356 oe_debug_pub.add('Trx_source_delivery_id : ' || l_delivery , 1 ) ;
1357 oe_debug_pub.add('Revision : ' || p_revision , 1 ) ;
1358 oe_debug_pub.add('Locator_id : ' || p_locator_id , 1 ) ;
1359 oe_debug_pub.add('Loc_segment1 : ' || null , 1 ) ;
1360 oe_debug_pub.add('Loc_segment2 : ' || null , 1 ) ;
1361 oe_debug_pub.add('Loc_segment3 : ' || null , 1 ) ;
1362 oe_debug_pub.add('Loc_segment4 : ' || null , 1 ) ;
1363 oe_debug_pub.add('Required_flag : ' || null , 1 ) ;
1364 oe_debug_pub.add('Picking_line_id : ' || l_shipment_line_id , 1 ) ;
1365 oe_debug_pub.add('Transfer_subinventory : ' || l_dest_subinv , 1 ) ;
1366 oe_debug_pub.add('Transfer_organization : ' || l_to_org_id , 1 ) ;
1367 oe_debug_pub.add('Ship_to_location_id : ' || l_location_id , 1 ) ;
1368 oe_debug_pub.add('Requisition_line_id : ' || l_req_line_id , 1 ) ;
1369 oe_debug_pub.add('Transaction_uom : ' || p_trx_uom , 1 ) ;
1370 oe_debug_pub.add('Transaction interface_id : ' || l_transaction_interface_id , 1 ) ;
1371 oe_debug_pub.add('Demand_id : ' || null , 1 ) ;
1372 oe_debug_pub.add('Shipment_number : ' || null , 1 ) ;
1373 oe_debug_pub.add('Currency_code : ' || null , 1 ) ;
1374 oe_debug_pub.add('Currency_conversion_type : ' || null , 1 ) ;
1375 oe_debug_pub.add('Currency_conversion_date : ' || null , 1 ) ;
1376 oe_debug_pub.add('Currency_conversion_rate : ' || null , 1 ) ;
1377 oe_debug_pub.add('Encumbrance_account : ' || l_budget_acct_id , 1 ) ;
1378 oe_debug_pub.add('Encumbrance_amount : ' || l_unit_price * p_transaction_detail_qty , 1 ) ;
1379 oe_debug_pub.add('Project_id : ' || l_project_id , 1 ) ;
1380 oe_debug_pub.add('Task_id : ' || l_task_id , 1 ) ;
1381 oe_debug_pub.add('Before inserting records into mtl interface table..' , 1 ) ;
1382 END IF;
1383
1384 -- 1517431 populate distribution account
1385
1386 DECLARE
1387 l_segment1 varchar2(25) := NULL;
1388 l_segment2 varchar2(25) := NULL;
1389 l_segment3 varchar2(25) := NULL;
1390 l_segment4 varchar2(25) := NULL;
1391 l_segment5 varchar2(25) := NULL;
1392 l_segment6 varchar2(25) := NULL;
1393 l_segment7 varchar2(25) := NULL;
1394 l_segment8 varchar2(25) := NULL;
1395 l_segment9 varchar2(25) := NULL;
1396 l_segment10 varchar2(25) := NULL;
1397 l_segment11 varchar2(25) := NULL;
1398 l_segment12 varchar2(25) := NULL;
1399 l_segment13 varchar2(25) := NULL;
1400 l_segment14 varchar2(25) := NULL;
1401 l_segment15 varchar2(25) := NULL;
1402 l_segment16 varchar2(25) := NULL;
1403 l_segment17 varchar2(25) := NULL;
1404 l_segment18 varchar2(25) := NULL;
1405 l_segment19 varchar2(25) := NULL;
1406 l_segment20 varchar2(25) := NULL;
1407 l_segment21 varchar2(25) := NULL;
1408 l_segment22 varchar2(25) := NULL;
1409 l_segment23 varchar2(25) := NULL;
1410 l_segment24 varchar2(25) := NULL;
1411 l_segment25 varchar2(25) := NULL;
1412 l_segment26 varchar2(25) := NULL;
1413 l_segment27 varchar2(25) := NULL;
1414 l_segment28 varchar2(25) := NULL;
1415 l_segment29 varchar2(25) := NULL;
1416 l_segment30 varchar2(25) := NULL;
1417 CURSOR c_transacc_info IS
1418 SELECT segment1,
1419 segment2,
1420 segment3,
1421 segment4,
1422 segment5,
1423 segment6,
1424 segment7,
1425 segment8,
1426 segment9,
1427 segment10,
1428 segment11,
1429 segment12,
1430 segment13,
1431 segment14,
1432 segment15,
1433 segment16,
1434 segment17,
1435 segment18,
1436 segment19,
1437 segment20,
1438 segment21,
1439 segment22,
1440 segment23,
1441 segment24,
1442 segment25,
1443 segment26,
1444 segment27,
1445 segment28,
1446 segment29,
1447 segment30
1448 FROM GL_CODE_COMBINATIONS
1449 WHERE code_combination_id = l_trans_acc;
1450 BEGIN
1451 OPEN c_transacc_info;
1452 FETCH c_transacc_info INTO
1453 l_segment1,
1454 l_segment2,
1455 l_segment3,
1456 l_segment4,
1457 l_segment5,
1458 l_segment6,
1459 l_segment7,
1460 l_segment8,
1461 l_segment9,
1462 l_segment10,
1463 l_segment11,
1464 l_segment12,
1465 l_segment13,
1466 l_segment14,
1467 l_segment15,
1468 l_segment16,
1469 l_segment17,
1470 l_segment18,
1471 l_segment19,
1472 l_segment20,
1473 l_segment21,
1474 l_segment22,
1475 l_segment23,
1476 l_segment24,
1477 l_segment25,
1478 l_segment26,
1479 l_segment27,
1480 l_segment28,
1481 l_segment29,
1482 l_segment30;
1483 CLOSE c_transacc_info;
1484
1485 INSERT INTO MTL_TRANSACTIONS_INTERFACE
1486 (
1487 SOURCE_CODE,
1488 SOURCE_LINE_ID,
1489 SOURCE_HEADER_ID,
1490 PROCESS_FLAG,
1491 TRANSACTION_MODE,
1492 LOCK_FLAG,
1493 TRANSACTION_HEADER_ID,
1494 INVENTORY_ITEM_ID,
1495 SUBINVENTORY_CODE,
1496 TRANSACTION_QUANTITY,
1497 SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
1498 TRANSACTION_DATE,
1499 ORGANIZATION_ID,
1500 ACCT_PERIOD_ID,
1501 LAST_UPDATE_DATE,
1502 LAST_UPDATED_BY,
1503 CREATION_DATE,
1504 CREATED_BY,
1505 TRANSACTION_SOURCE_ID,
1506 -- DSP_SEGMENT1, bug 4456817
1507 -- DSP_SEGMENT2,
1508 -- DSP_SEGMENT3,
1509 TRANSACTION_SOURCE_TYPE_ID,
1510 TRANSACTION_ACTION_ID,
1511 TRANSACTION_TYPE_ID,
1512 DISTRIBUTION_ACCOUNT_ID,
1513 DST_SEGMENT1,
1514 DST_SEGMENT2,
1515 DST_SEGMENT3,
1516 DST_SEGMENT4,
1517 DST_SEGMENT5,
1518 DST_SEGMENT6,
1519 DST_SEGMENT7,
1520 DST_SEGMENT8,
1521 DST_SEGMENT9,
1522 DST_SEGMENT10,
1523 DST_SEGMENT11,
1524 DST_SEGMENT12,
1525 DST_SEGMENT13,
1526 DST_SEGMENT14,
1527 DST_SEGMENT15,
1528 DST_SEGMENT16,
1529 DST_SEGMENT17,
1530 DST_SEGMENT18,
1531 DST_SEGMENT19,
1532 DST_SEGMENT20,
1533 DST_SEGMENT21,
1534 DST_SEGMENT22,
1535 DST_SEGMENT23,
1536 DST_SEGMENT24,
1537 DST_SEGMENT25,
1538 DST_SEGMENT26,
1539 DST_SEGMENT27,
1540 DST_SEGMENT28,
1541 DST_SEGMENT29,
1542 DST_SEGMENT30,
1543 TRANSACTION_REFERENCE,
1544 TRX_SOURCE_LINE_ID,
1545 TRX_SOURCE_DELIVERY_ID,
1546 REVISION,
1547 LOCATOR_ID,
1548 LOC_SEGMENT1,
1549 LOC_SEGMENT2,
1550 LOC_SEGMENT3,
1551 LOC_SEGMENT4,
1552 REQUIRED_FLAG,
1553 PICKING_LINE_ID,
1554 TRANSFER_SUBINVENTORY,
1555 TRANSFER_ORGANIZATION,
1556 SHIP_TO_LOCATION_ID,
1557 REQUISITION_LINE_ID,
1558 TRANSACTION_UOM,
1559 TRANSACTION_INTERFACE_ID,
1560 DEMAND_ID,
1561 SHIPMENT_NUMBER,
1562 CURRENCY_CODE,
1563 CURRENCY_CONVERSION_TYPE,
1564 CURRENCY_CONVERSION_DATE,
1565 CURRENCY_CONVERSION_RATE,
1566 ENCUMBRANCE_ACCOUNT,
1567 ENCUMBRANCE_AMOUNT,
1568 PROJECT_ID,
1569 TASK_ID,
1570 CONTENT_LPN_ID) -- 3544019
1571 VALUES (
1572 profile_values.oe_source_code,
1573 l_source_line_id,
1574 l_transaction_reference,
1575 1, /* PROCESS_FLAG */
1576 3, /* TRANSACTION_MODE */
1577 2, /* LOCK_FLAG */
1578 l_transaction_header_id,
1579 p_inventory_item_id,
1580 p_secondary_inventory,
1581 (-1 * p_transaction_detail_qty),
1582 (-1 * p_trans_qty2), -- INVCONV
1583 l_transaction_date,
1584 p_warehouse_id,
1585 null,
1586 sysdate,
1587 profile_values.user_id,
1588 sysdate,
1589 profile_values.user_id,
1590 l_transaction_source_id,
1591 -- l_order_number, bug 4456817
1592 -- l_order_type_name,
1593 -- profile_values.oe_source_code,
1594 l_trx_source_type_id,
1595 l_trx_action_id,
1596 l_trx_type_code,
1597 l_trans_acc,
1598 l_segment1,
1599 l_segment2,
1600 l_segment3,
1601 l_segment4,
1602 l_segment5,
1603 l_segment6,
1604 l_segment7,
1605 l_segment8,
1606 l_segment9,
1607 l_segment10,
1608 l_segment11,
1609 l_segment12,
1610 l_segment13,
1611 l_segment14,
1612 l_segment15,
1613 l_segment16,
1614 l_segment17,
1615 l_segment18,
1616 l_segment19,
1617 l_segment20,
1618 l_segment21,
1619 l_segment22,
1620 l_segment23,
1621 l_segment24,
1622 l_segment25,
1623 l_segment26,
1624 l_segment27,
1625 l_segment28,
1626 l_segment29,
1627 l_segment30,
1628 l_transaction_reference,
1629 l_line_id,
1630 decode(l_delivery,-1,null,l_delivery),
1631 p_revision,
1632 decode(p_locator_id,-1,null,p_locator_id),
1633 null,
1634 null,
1635 null,
1636 null,
1637 null,
1638 l_shipment_line_id,
1639 decode(l_dest_subinv,' ',null,l_dest_subinv),
1640 decode(l_to_org_id,-1,null,l_to_org_id),
1641 decode(l_location_id,0,null,l_location_id),
1642 decode(l_req_line_id,0,null,l_req_line_id),
1643 p_trx_uom,
1644 l_transaction_interface_id,
1645 null,
1646 null,
1647 null,
1648 null,
1649 null,
1650 null,
1651 decode(l_budget_acct_id,-1, null, l_budget_acct_id),
1652 decode(l_budget_acct_id,-1, null,
1653 l_unit_price * p_transaction_detail_qty),
1654 decode(l_project_id, 0, null, l_project_id),
1655 decode(l_task_id, 0, null, l_task_id),
1656 p_transfer_lpn_id); -- 3544019
1657
1658 IF l_debug_level > 0 THEN
1659 oe_debug_pub.add( 'Successfully inserted records in mtl interface table ' , 5 ) ;
1660 x_return_status := FND_API.G_RET_STS_SUCCESS; -- bug 5357879
1661 END IF;
1662 EXCEPTION WHEN OTHERS THEN
1663 IF l_debug_level > 0 THEN
1664 oe_debug_pub.add( 'Failed while inserting records in mtl_transactions_interface '||sqlerrm , 1 ) ;
1665 oe_debug_pub.add( 'Sales order issue transaction will not occur ' , 1 ) ;
1666 END IF;
1667
1668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; -- bug 5357879
1669 ROLLBACK TO DECREMENT_INV;
1670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1671 END;
1672
1673 -- Calling this API for immediate decrement of inventory
1674
1675 IF l_debug_level > 0 THEN
1676 oe_debug_pub.add( 'Return from decrementinventory()',5 ) ;
1677 END IF;
1678
1679 END Decrement_Inventory;
1680
1681 /* --------------------------------------------------------------------
1682 Procedure Name : Get_Char_of_accts
1683 Description :
1684 ----------------------------------------------------------------------- */
1685 FUNCTION Get_Char_of_accts (p_ship_from_org_id IN NUMBER)
1686 RETURN NUMBER
1687 IS
1688 l_chart_of_accs NUMBER;
1689 --
1690 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1691 --
1692 BEGIN
1693 -- SQL Performance ID 14882944, replacing org_organization_definitions with relevant base tables
1694 /*
1695 SELECT chart_of_accounts_id
1696 INTO l_chart_of_accs
1697 FROM org_organization_definitions
1698 WHERE org_organization_definitions.organization_id = p_ship_from_org_id; */
1699
1700 SELECT chart_of_accounts_id INTO l_chart_of_accs
1701 FROM gl_sets_of_books gsob, hr_organization_information hoi
1702 WHERE gsob.set_of_books_id = hoi.org_information1
1703 AND upper(hoi.org_information_context) = 'ACCOUNTING INFORMATION'
1704 AND hoi.organization_id = p_ship_from_org_id;
1705
1706 RETURN l_chart_of_accs;
1707 END Get_Char_of_accts;
1708
1709 /*--------------------------------------------------------------------------
1710 Procedure Name : Get_mtl_sales_order_id
1711 Description : This funtion returns the SALES_ORDER_ID (frm mtl_sales_orders)
1712 for a given heeader_id.
1713 Every header in oe_order_headers_all will have a record
1714 in MTL_SALES_ORDERS. The unique key to get the sales_order_id
1715 from mtl_sales_orders is
1716 Order_Number
1717 Order_Type (in base language)
1718 OM:Source Code profile option (stored as ont_source_code).
1719
1720 The above values are stored in a flex in MTL_SALES_ORDERS.
1721 SEGMENT1 : stores the order number
1722 SEGMENT2 : stores the order type
1723 SEGMENT3 : stores the ont_source_code value
1724
1725 -------------------------------------------------------------------------- */
1726 FUNCTION Get_mtl_sales_order_id(p_header_id IN NUMBER)
1727 RETURN NUMBER
1728 IS
1729 l_source_code VARCHAR2(40) := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
1730 l_sales_order_id NUMBER := 0;
1731 l_order_type_name VARCHAR2(80);
1732 l_order_type_id NUMBER;
1733 --
1734 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1735 --
1736 BEGIN
1737
1738 IF l_debug_level > 0 THEN
1739 oe_debug_pub.add( 'ENTERING GET_MTL_SALES_ORDER_ID IN DROPSHIPRECECEIVE ( ) ' , 3 ) ;
1740 END IF;
1741
1742 BEGIN
1743 SELECT order_type_id
1744 INTO l_order_type_id
1745 FROM oe_order_headers
1746 WHERE header_id = p_header_id;
1747 EXCEPTION
1748 WHEN OTHERS THEN
1749 RAISE;
1750 END;
1751
1752 IF l_debug_level > 0 THEN
1753 oe_debug_pub.add( 'ORDER TYPE ID :' || L_ORDER_TYPE_ID , 3 ) ;
1754 END IF;
1755
1756 BEGIN
1757 SELECT NAME
1758 INTO l_order_type_name
1759 FROM OE_TRANSACTION_TYPES_TL
1760 WHERE TRANSACTION_TYPE_ID = l_order_type_id
1761 AND language = (select language_code
1762 from fnd_languages
1763 where installed_flag = 'B');
1764 EXCEPTION
1765 WHEN NO_DATA_FOUND THEN
1766 IF l_debug_level > 0 THEN
1767 oe_debug_pub.add( 'UNABLE TO LOCATE ORDER TYPE ID IN DROPSHIPRECEIVE ( ) '||sqlerrm , 1 ) ;
1768 END IF;
1769 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1770 END;
1771
1772 IF l_debug_level > 0 THEN
1773 oe_debug_pub.add( 'ORDER TYPE: ' || L_ORDER_TYPE_NAME , 2 ) ;
1774 oe_debug_pub.add( 'SOURCE CODE: ' || L_SOURCE_CODE , 2 ) ;
1775 END IF;
1776
1777 SELECT S.SALES_ORDER_ID
1778 INTO l_sales_order_id
1779 FROM MTL_SALES_ORDERS S,
1780 OE_ORDER_HEADERS H
1781 WHERE S.SEGMENT1 = TO_CHAR(H.ORDER_NUMBER)
1782 AND S.SEGMENT2 = l_order_type_name
1783 AND S.SEGMENT3 = l_source_code
1784 AND H.HEADER_ID = p_header_id;
1785
1786 IF l_debug_level > 0 THEN
1787 oe_debug_pub.add( 'EXISTING GET_MTL_SALES_ORDER_ID ( ) WITH SALES ORDER ID => ' || L_SALES_ORDER_ID , 1 ) ;
1788 END IF;
1789
1790 RETURN l_sales_order_id;
1791
1792 EXCEPTION
1793 WHEN NO_DATA_FOUND THEN
1794 IF l_debug_level > 0 THEN
1795 oe_debug_pub.add( '2. L_SALES_ORDER_ID IS 0' , 5 ) ;
1796 END IF;
1797 RETURN 0;
1798 WHEN OTHERS THEN
1799 IF l_debug_level > 0 THEN
1800 oe_debug_pub.add( '2. L_SALES_ORDER_ID IS 0' , 5 ) ;
1801 END IF;
1802 RETURN 0;
1803 END Get_mtl_sales_order_id;
1804
1805 /*-----------------------------------------------------------------
1806 PROCEDURE : Insert_OE_Drop_Ship_Source
1807 DESCRIPTION:
1808 -----------------------------------------------------------------*/
1809
1810 PROCEDURE Insert_OE_Drop_Ship_Source (P_Old_Line_ID IN NUMBER, P_New_Line_ID IN NUMBER) IS
1811 l_Header_ID Number;
1812 l_Org_ID Number;
1813 l_Destination_Organization_ID Number;
1814 l_Requisition_Header_ID Number;
1815 l_Requisition_Line_ID Number;
1816 l_PO_Header_ID Number;
1817 l_PO_Line_ID Number;
1818 l_Line_Location_ID Number;
1819 l_PO_Release_ID Number;
1820 l_old_line_id Number := p_old_line_id;
1821 l_new_line_id Number := p_new_line_id;
1822
1823 CURSOR old_drop_ship_line IS
1824 SELECT Header_id,
1825 Org_id,
1826 Destination_Organization_ID,
1827 Requisition_Header_ID,
1828 Requisition_Line_ID,
1829 PO_Header_ID,
1830 PO_Line_ID,
1831 Line_Location_ID,
1832 PO_Release_ID
1833 FROM OE_DROP_SHIP_SOURCES
1834 WHERE line_id = l_new_line_id
1835 AND line_location_id = G_LINE_LOCATION_ID; -- bug 4402566
1836
1837 --
1838 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1839 --
1840 BEGIN
1841
1842 OPEN old_drop_ship_line;
1843 FETCH old_drop_ship_line
1844 INTO l_header_id,
1845 l_org_id,
1846 l_destination_organization_id,
1847 l_requisition_header_id,
1848 l_requisition_line_id,
1849 l_po_header_id,
1850 l_po_line_id,
1851 l_line_location_id,
1852 l_po_release_id;
1853 CLOSE old_drop_ship_line;
1854
1855 Insert Into OE_Drop_Ship_Sources
1856 (
1857 drop_ship_source_id,
1858 header_id,
1859 line_id,
1860 org_id,
1861 destination_organization_id,
1862 requisition_header_id,
1863 requisition_line_id,
1864 po_header_id,
1865 po_line_id,
1866 line_location_id,
1867 po_release_id,
1868 Creation_Date,
1869 Created_By,
1870 Last_Update_Date,
1871 Last_Updated_By
1872 )
1873 Values
1874 (
1875 oe_drop_ship_source_s.nextval,
1876 l_header_id,
1877 l_old_line_id,
1878 l_org_id,
1879 l_destination_organization_id,
1880 l_requisition_header_id,
1881 l_requisition_line_id,
1882 l_po_header_id,
1883 l_po_line_id,
1884 l_line_location_id,
1885 l_po_release_id,
1886 trunc(Sysdate),
1887 nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1),
1888 trunc(Sysdate),
1889 nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
1890 );
1891
1892 End Insert_OE_Drop_Ship_Source;
1893
1894 /* --------------------------------------------------------------------
1895 Procedure Name : Decrement_Inventory_for_OPM
1896 Description : This procedure first check to see if the order line
1897 has transaction or not. If it does have inv transaction
1898 then, it would do the comparison. If they are the same,
1899 this routine would do nothing. If they are not the same,
1900 the old transactions would be deleted and new ones would be
1901 inserted.Default lot is always checked. A default
1902 transaction would be created if the item is not lot/location
1903 ctl'ed and no transactions exist.
1904
1905 -----------------------------------------------------------------------*/
1906
1907 /*Procedure Decrement_Inventory_for_OPM(
1908 p_detail_id IN NUMBER,
1909 p_line_rec IN OE_ORDER_PUB.line_rec_type,
1910 p_transaction_id IN NUMBER,
1911 p_trans_qty IN NUMBER,
1912 p_trans_qty2 IN NUMBER,
1913 p_inventory_item_id IN NUMBER,
1914 p_delivery IN NUMBER,
1915 p_lot_number IN VARCHAR2,
1916 p_sublot_no IN VARCHAR2,
1917 p_revision IN VARCHAR2,
1918 p_locator_id IN NUMBER,
1919 p_warehouse_id IN NUMBER,
1920 p_chart_of_accts IN NUMBER,
1921 p_trx_uom IN VARCHAR2,
1922 p_sn_control_code IN NUMBER,
1923 p_as_alpha_prefix IN VARCHAR2,
1924 p_transaction_header_id IN NUMBER,
1925 x_return_status OUT NOCOPY VARCHAR2)
1926
1927 IS
1928 l_source_line_id NUMBER;
1929 l_lot_set_id NUMBER;
1930 l_trans_acc NUMBER;
1931 l_trans_qty NUMBER;
1932 l_ord_num NUMBER;
1933 l_order_type_name VARCHAR2(30) := 'Standard';
1934 l_budget_acct_id NUMBER := -1;
1935 l_project_id NUMBER := p_line_rec.project_id;
1936 l_task_id NUMBER := p_line_rec.task_id;
1937 l_transaction_reference NUMBER := 0;
1938 l_order_number NUMBER;
1939 l_line_id NUMBER := p_line_rec.line_id;
1940 l_shipment_line_id NUMBER := 0;
1941 l_delivery NUMBER := -1;
1942 l_to_org_id NUMBER := -1;
1943 l_location_id NUMBER := 0;
1944 l_req_line_id NUMBER := 0;
1945 l_unit_price NUMBER;
1946 l_concat_segs VARCHAR2(2000);
1947 l_concat_ids VARCHAR2(2000);
1948 l_concat_descrs VARCHAR2(2000);
1949 l_transaction_date DATE;
1950 l_msg_count NUMBER;
1951 l_msg_data VARCHAR2(2000);
1952 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1953 l_transaction_interface_id NUMBER := null;
1954 l_transaction_header_id NUMBER := p_transaction_header_id;
1955 l_opm_item_id NUMBER;
1956 l_opm_item_no VARCHAR2(32);
1957 l_opm_item_um VARCHAR2(5);
1958 l_opm_trx_uom VARCHAR2(5);
1959 l_opm_lot_id NUMBER;
1960 l_opm_location VARCHAR2(16);
1961 l_opm_whse VARCHAR2(4);
1962 l_exist NUMBER := 0;
1963
1964 Cursor get_opm_item_no IS
1965 Select distinct segment1
1966 From mtl_system_items
1967 Where inventory_item_id = p_inventory_item_id
1968 and organization_id = p_warehouse_id;
1969
1970 Cursor get_opm_item_id IS
1971 Select item_id, item_um
1972 From ic_item_mst
1973 Where item_no = l_opm_item_no;
1974
1975 Cursor get_opm_lot_id_sublot IS
1976 Select lot_id
1977 From ic_lots_mst
1978 Where lot_no = p_lot_number
1979 And item_id = l_opm_item_id
1980 And lot_id <> 0
1981 And sublot_no = p_sublot_no;
1982
1983 Cursor get_opm_lot_id_no_sublot IS
1984 Select lot_id
1985 From ic_lots_mst
1986 Where lot_no = p_lot_number
1987 And item_id = l_opm_item_id
1988 And lot_id <> 0
1989 And sublot_no is null;
1990
1991 Cursor get_opm_location IS
1992 Select location
1993 From ic_loct_mst
1994 Where inventory_location_id = p_locator_id;
1995
1996 Cursor get_opm_whse IS
1997 Select whse_code
1998 From ic_whse_mst
1999 Where mtl_organization_id = p_warehouse_id;
2000
2001 Cursor check_opm_inv IS
2002 Select count(*)
2003 From ic_tran_pnd
2004 Where item_id = l_opm_item_id
2005 And lot_id = l_opm_lot_id
2006 And line_id = p_line_rec.line_id
2007 And doc_type = 'OMSO'
2008 And delete_mark = 0
2009 And completed_ind = 0;
2010
2011 Cursor check_opm_inv_default IS
2012 Select count(*)
2013 From ic_tran_pnd
2014 Where item_id = l_opm_item_id
2015 And lot_id = 0
2016 And line_id = p_line_rec.line_id
2017 And doc_type = 'OMSO'
2018 And delete_mark = 0
2019 And completed_ind = 0;
2020
2021 --
2022 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2023 --
2024 BEGIN
2025
2026 IF l_debug_level > 0 THEN
2027 oe_debug_pub.add( 'ENTERING DECREMENT INVENTORY FOR OPM' ) ;
2028 oe_debug_pub.add( ' P_LOT_NUMBER : ' || P_LOT_NUMBER , 1 ) ;
2029 oe_debug_pub.add( ' P_INVENTORY_ITEM_ID : ' || P_INVENTORY_ITEM_ID , 1 ) ;
2030 oe_debug_pub.add( ' P_ORGANIZATION_ID : ' || P_WAREHOUSE_ID , 1 ) ;
2031 oe_debug_pub.add( ' P_TRX_UOM : ' || P_TRX_UOM , 1 ) ;
2032 oe_debug_pub.add( ' P_CHART_OF_ACCTS : ' || P_CHART_OF_ACCTS , 1 ) ;
2033 oe_debug_pub.add( ' P_AS_ALPHA_PREFIX : ' || P_AS_ALPHA_PREFIX , 1 ) ;
2034 oe_debug_pub.add( ' P_SN_CONTROL_CODE : ' || P_SN_CONTROL_CODE , 1 ) ;
2035 oe_debug_pub.add( ' P_TRANSACTION_ID : ' || P_TRANSACTION_ID , 1 ) ;
2036 END IF;
2037
2038 profile_values.oe_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
2039
2040 /*
2041 ** Check For The Transaction Account
2042 */
2043
2044 /* IF OE_FLEX_COGS_PUB.Start_Process (
2045 p_api_version_number => 1.0,
2046 p_line_id => p_line_rec.line_id,
2047 x_return_ccid => l_trans_acc,
2048 x_concat_segs => l_concat_segs,
2049 x_concat_ids => l_concat_ids,
2050 x_concat_descrs => l_concat_descrs,
2051 x_msg_count => l_msg_count,
2052 x_msg_data => l_msg_data) <> FND_API.G_RET_STS_SUCCESS
2053 THEN */
2054 /* 1517431, If workflow fails to generate distribution acct,
2055 populate as null */
2056
2057 /* l_trans_acc := NULL;
2058 END IF;
2059
2060 IF l_debug_level > 0 THEN
2061 oe_debug_pub.add( 'TRANSACTION ACCOUNT ID ID : ' || L_TRANS_ACC , 1 ) ;
2062 END IF;
2063
2064 l_trans_qty := p_trans_qty;
2065 /*OPM transactions in ic_tran_pnd
2066
2067 Open get_opm_item_no;
2068 Fetch get_opm_item_no INTO l_opm_item_no;
2069 Close get_opm_item_no;
2070 IF l_debug_level > 0 THEN
2071 oe_debug_pub.add( ' P_OPM_ITEM_NO : ' || L_OPM_ITEM_NO , 1 ) ;
2072 END IF;
2073
2074 Open get_opm_item_id;
2075 Fetch get_opm_item_id INTO l_opm_item_id, l_opm_item_um;
2076 Close get_opm_item_id;
2077 IF l_debug_level > 0 THEN
2078 oe_debug_pub.add( ' P_OPM_ITEM_ID : ' || L_OPM_ITEM_ID , 1 ) ;
2079 END IF;
2080 IF l_debug_level > 0 THEN
2081 oe_debug_pub.add( ' P_OPM_ITEM_UM : ' || L_OPM_ITEM_UM , 1 ) ;
2082 END IF;
2083
2084 Open get_opm_whse;
2085 Fetch get_opm_whse INTO l_opm_whse;
2086 Close get_opm_whse;
2087 IF l_debug_level > 0 THEN
2088 oe_debug_pub.add( ' P_WAREHOUSE_ID : ' || L_OPM_WHSE , 1 ) ;
2089 END IF;
2090
2091 l_opm_location := null;
2092 IF NVL(p_locator_id,0) <>0 THEN
2093 Open get_opm_location;
2094 Fetch get_opm_location INTO l_opm_location;
2095 Close get_opm_location;
2096 END IF;
2097 IF l_debug_level > 0 THEN
2098 oe_debug_pub.add( ' OPM_LOCATION : ' || L_OPM_LOCATION , 1 ) ;
2099 END IF;
2100 l_opm_lot_id := 0;
2101 IF (p_lot_number is not null) AND (p_sublot_no is not null) THEN
2102 Open get_opm_lot_id_sublot;
2103 Fetch get_opm_lot_id_sublot INTO l_opm_lot_id;
2104 Close get_opm_lot_id_sublot;
2105 ELSIF (p_lot_number is not null) THEN
2106 Open get_opm_lot_id_no_sublot;
2107 Fetch get_opm_lot_id_no_sublot INTO l_opm_lot_id;
2108 Close get_opm_lot_id_no_sublot;
2109 END IF;
2110 IF l_debug_level > 0 THEN
2111 oe_debug_pub.add( ' OPM_LOT_ID : ' || L_OPM_LOT_ID , 1 ) ;
2112 END IF;
2113
2114 IF (p_lot_number is not null) THEN
2115 OPEN check_opm_inv ;
2116 Fetch check_opm_inv INTO l_exist;
2117 CLOSE check_opm_inv;
2118 ELSE
2119 OPEN check_opm_inv_default ;
2120 Fetch check_opm_inv_default INTO l_exist;
2121 CLOSE check_opm_inv_default;
2122 END IF;
2123 IF l_debug_level > 0 THEN
2124 oe_debug_pub.add( ' L_EXIST : ' || L_EXIST , 1 ) ;
2125 END IF;
2126
2127 GMI_RESERVATION_UTIL.Get_OPMUOM_from_AppsUOM(
2128 p_Apps_UOM => p_trx_uom
2129 , x_OPM_UOM => l_opm_trx_uom
2130 , x_return_status => l_return_status
2131 , x_msg_count => l_msg_count
2132 , x_msg_data => l_msg_data);
2133 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2134 THEN
2135 FND_MESSAGE.Set_Name('GMI','GMI_OPM_UOM_NOT_FOUND');
2136 FND_MESSAGE.Set_Token('APPS_UOM_CODE', p_trx_uom);
2137 FND_MSG_PUB.Add;
2138 RAISE FND_API.G_EXC_ERROR;
2139 ELSE
2140 IF l_debug_level > 0 THEN
2141 oe_debug_pub.add( ' OPM TRX UOM='||L_OPM_TRX_UOM||'.' , 1 ) ;
2142 END IF;
2143 END IF;
2144 IF l_opm_trx_uom <> l_opm_item_um THEN
2145 GMICUOM.icuomcv(pitem_id => l_opm_item_id,
2146 plot_id => 0,
2147 pcur_qty => p_trans_qty,
2148 pcur_uom => p_trx_uom,
2149 pnew_uom => l_opm_trx_uom,
2150 onew_qty => l_trans_qty);
2151 END IF;
2152
2153 IF nvl(l_exist,0) = 0 THEN
2154 GMI_RESERVATION_UTIL.create_transaction_for_rcv
2155 (
2156 p_whse_code => l_opm_whse
2157 , p_transaction_id=> p_transaction_id
2158 , p_line_id => p_line_rec.line_id
2159 , p_item_id => l_opm_item_id
2160 , p_lot_id => nvl(l_opm_lot_id,0)
2161 , p_location => l_opm_location
2162 , p_qty1 => l_trans_qty
2163 , p_qty2 => p_trans_qty2
2164 , x_return_status => l_return_status
2165 , x_msg_count => l_msg_count
2166 , x_msg_data => l_msg_data
2167 ) ;
2168 END IF;
2169
2170 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2171 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by Create_Pending_Transaction,
2172 return_status='|| x_return_status||', x_msg_count='|| l_msg_count||'.');
2173 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
2174 FND_MESSAGE.Set_Token('BY_PROC','OE_DROPSHIP_RCV.OPM_TRANSACTION');
2175 FND_MESSAGE.Set_Token('WHERE','Create_transaction');
2176 FND_MSG_PUB.Add;
2177 raise FND_API.G_EXC_ERROR;
2178 END IF;
2179
2180 IF l_debug_level > 0 THEN
2181 oe_debug_pub.add( 'EXITING DECREMENT INVENTORY' ) ;
2182 END IF;
2183
2184 END Decrement_Inventory_for_OPM; */
2185
2186 /*--------------------------------------------------------------+
2187 Name : Create_Reservation
2188 Description : This procedure will be called from Drop Ship
2189 Receive. This will call Inventory's Create
2190 reservation API to pu reservations on the order
2191 line and will return the reserved quantity.
2192 Change Record :
2193 +--------------------------------------------------------------*/
2194
2195 PROCEDURE Create_reservation
2196 (p_qty_to_be_reserved IN NUMBER
2197 ,p_qty2_to_be_reserved IN NUMBER DEFAULT NULL --INVVCONV
2198 ,p_revision IN VARCHAR2
2199 ,p_locator_id IN NUMBER
2200 ,p_lot IN VARCHAR2
2201 ,p_line_rec IN OE_ORDER_PUB.Line_Rec_Type
2202 ,x_qty_reserved OUT NOCOPY NUMBER
2203 ,x_qty2_reserved OUT NOCOPY NUMBER -- INVCONV
2204 ,x_rsv_id OUT NOCOPY NUMBER
2205 ,x_return_status OUT NOCOPY VARCHAR2
2206 ,p_transfer_lpn_id IN NUMBER -- 3544019
2207 ,p_prim_reservation_qty IN NUMBER DEFAULT NULL -- 12794393
2208 )IS
2209
2210 l_locator_id NUMBER := 0;
2211 l_reservation_rec inv_reservation_global.mtl_reservation_rec_type;
2212 l_dummy_sn inv_reservation_global.serial_number_tbl_type;
2213 l_quantity_reserved NUMBER;
2214 l_quantity2_reserved NUMBER; -- INVCONV
2215 l_qty2_to_be_reserved NUMBER; --INVCONV
2216 l_rsv_id NUMBER := 0;
2217 l_sales_order_id NUMBER;
2218
2219 l_msg_count NUMBER;
2220 l_msg_data VARCHAR2(20000);
2221
2222 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2223 l_return_status VARCHAR2(1);
2224
2225 BEGIN
2226
2227 IF l_debug_level > 0 THEN
2228 oe_debug_pub.add( 'Populating inventory record before calling reservation ' , 1 ) ;
2229 END IF;
2230
2231 -- Populate inventory record structure before calling reservation
2232
2233 l_reservation_rec.reservation_id := fnd_api.g_miss_num; -- cannot know
2234 l_reservation_rec.requirement_date := p_line_rec.schedule_ship_date;
2235 l_reservation_rec.organization_id := p_line_rec.ship_from_org_id;
2236 l_reservation_rec.inventory_item_id := p_line_rec.inventory_item_id;
2237 l_reservation_rec.demand_source_type_id := INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE;
2238 l_reservation_rec.demand_source_name := NULL;
2239
2240 -- Get demand_source_header_id from mtl_sales_orders
2241
2242 l_sales_order_id := Get_mtl_sales_order_id(p_line_rec.header_id);
2243
2244 IF l_debug_level > 0 THEN
2245 oe_debug_pub.add( 'Sales Order ID '||l_sales_order_id, 1 ) ;
2246 END IF;
2247 IF p_qty2_to_be_reserved = 0 then -- INVCONV
2248 l_qty2_to_be_reserved := NULL;
2249 else
2250 l_qty2_to_be_reserved := p_qty2_to_be_reserved;
2251 END IF;
2252
2253 l_reservation_rec.demand_source_header_id := l_sales_order_id;
2254 l_reservation_rec.demand_source_line_id := p_line_rec.line_id;
2255 l_reservation_rec.demand_source_delivery := NULL;
2256 l_reservation_rec.primary_uom_code := NULL;
2257 l_reservation_rec.primary_uom_id := NULL;
2258 l_reservation_rec.reservation_uom_code := p_line_rec.order_quantity_uom;
2259 l_reservation_rec.reservation_uom_id := NULL;
2260 l_reservation_rec.reservation_quantity := p_qty_to_be_reserved;
2261 l_reservation_rec.secondary_uom_code := p_line_rec.ordered_quantity_uom2; -- INVCONV 4066306
2262 l_reservation_rec.secondary_uom_id := NULL; -- INVCONV 4066306
2263 l_reservation_rec.secondary_reservation_quantity := l_qty2_to_be_reserved; -- INVCONV
2264 l_reservation_rec.primary_reservation_quantity := p_prim_reservation_qty; -- 12794393
2265 l_reservation_rec.autodetail_group_id := NULL;
2266 l_reservation_rec.external_source_code := NULL;
2267 l_reservation_rec.external_source_line_id := NULL;
2268 l_reservation_rec.supply_source_type_id := INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_INV;
2269 l_reservation_rec.supply_source_header_id := NULL;
2270 l_reservation_rec.supply_source_line_id := NULL;
2271 l_reservation_rec.supply_source_name := NULL;
2272 l_reservation_rec.supply_source_line_detail := NULL;
2273 l_reservation_rec.revision := p_revision;
2274 l_reservation_rec.subinventory_code := p_line_rec.subinventory;
2275 l_reservation_rec.subinventory_id := NULL;
2276 l_reservation_rec.locator_id := p_locator_id;
2277 l_reservation_rec.lot_number := p_lot;
2278 l_reservation_rec.lot_number_id := NULL;
2279 l_reservation_rec.pick_slip_number := NULL;
2280 -- for bug 3544019
2281 l_reservation_rec.lpn_id := p_transfer_lpn_id;
2282 l_reservation_rec.attribute_category := NULL;
2283 l_reservation_rec.attribute1 := NULL; -- INVCONV 4066306
2284 l_reservation_rec.attribute2 := NULL; -- INVCONV 4066306
2285 l_reservation_rec.attribute3 := NULL; -- INVCONV 4066306
2286 l_reservation_rec.attribute4 := NULL;
2287 l_reservation_rec.attribute5 := NULL;
2288 l_reservation_rec.attribute6 := NULL;
2289 l_reservation_rec.attribute7 := NULL;
2290 l_reservation_rec.attribute8 := NULL;
2291 l_reservation_rec.attribute9 := NULL;
2292 l_reservation_rec.attribute10 := NULL;
2293 l_reservation_rec.attribute11 := NULL;
2294 l_reservation_rec.attribute12 := NULL;
2295 l_reservation_rec.attribute13 := NULL;
2296 l_reservation_rec.attribute14 := NULL;
2297 l_reservation_rec.attribute15 := NULL;
2298 l_reservation_rec.ship_ready_flag := NULL;
2299
2300 -- Call INV with action = RESERVE
2301
2302 IF l_debug_level > 0 THEN
2303 oe_debug_pub.add( 'Calling clear cache' , 1 ) ;
2304 END IF;
2305
2306 -- Inv_quantity_tree_grp.clear_quantity_cache;
2307 Inv_quantity_tree_pvt.mark_all_for_refresh
2308 ( p_api_version_number => 1.0
2309 , p_init_msg_lst => FND_API.G_TRUE
2310 , x_return_status => l_return_status
2311 , x_msg_count => l_msg_count
2312 , x_msg_data => l_msg_data);
2313
2314 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2315 oe_msg_pub.transfer_msg_stack;
2316 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
2317 for I in 1..l_msg_count loop
2318 l_msg_data := OE_MSG_PUB.Get(I,'F');
2319 IF l_debug_level > 0 THEN
2320 oe_debug_pub.add( L_MSG_DATA , 1 ) ;
2321 END IF;
2322 end loop;
2323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2324 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2325 oe_msg_pub.transfer_msg_stack;
2326 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
2327 for I in 1..l_msg_count loop
2328 l_msg_data := OE_MSG_PUB.Get(I,'F');
2329 IF l_debug_level > 0 THEN
2330 oe_debug_pub.add( L_MSG_DATA , 1 ) ;
2331 END IF;
2332 end loop;
2333 RAISE FND_API.G_EXC_ERROR;
2334 END IF;
2335
2336 --16429262
2337 IF l_debug_level > 0 THEN
2338 oe_debug_pub.add( 'Before calling Create Reservation, Setting G_DS_SOissue_Flag to : ' || G_DS_SOISSUE_FLAG , 1 ) ;
2339 END IF;
2340 G_DS_SOISSUE_FLAG := 'Y';
2341 --16429262
2342
2343 IF l_debug_level > 0 THEN
2344 oe_debug_pub.add( 'Calling inv to reserve quantity : ' || p_qty_to_be_reserved , 1 ) ;
2345 oe_debug_pub.add( 'Calling inv to reserve quantity2 : ' || p_qty2_to_be_reserved , 1 ) ;
2346 END IF;
2347
2348 Inv_reservation_pub.create_reservation
2349 ( p_api_version_number => 1.0
2350 , p_init_msg_lst => FND_API.G_TRUE
2351 , x_return_status => l_return_status
2352 , x_msg_count => l_msg_count
2353 , x_msg_data => l_msg_data
2354 , p_rsv_rec => l_reservation_rec
2355 , p_serial_number => l_dummy_sn
2356 , x_serial_number => l_dummy_sn
2357 , p_partial_reservation_flag => FND_API.G_FALSE
2358 , p_force_reservation_flag => FND_API.G_FALSE
2359 , p_validation_flag => FND_API.G_TRUE
2360 , p_over_reservation_flag => 2 -- bug 4864453
2361 , x_quantity_reserved => l_quantity_reserved
2362 , x_secondary_quantity_reserved => l_quantity2_reserved -- INVCONV
2363 , x_reservation_id => l_rsv_id
2364 );
2365
2366 IF l_debug_level > 0 THEN
2367 oe_debug_pub.add( 'Create reservation returns : ' || l_return_status , 1 ) ;
2368 oe_debug_pub.add( l_msg_data , 1 ) ;
2369 END IF;
2370
2371 --16429262
2372 IF l_debug_level > 0 THEN
2373 oe_debug_pub.add( 'After Returning from Create Reservation, Resetting G_DS_SOissue_Flag back to : ' || G_DS_SOISSUE_FLAG , 1 ) ;
2374 END IF;
2375 G_DS_SOISSUE_FLAG := 'N';
2376 --16429262
2377
2378 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2379 IF l_debug_level > 0 THEN
2380 oe_debug_pub.add( 'Reservation fails with unexpected error in dropshipreceive() : '||sqlerrm , 1 ) ;
2381 oe_debug_pub.add( 'Try receipt after setting sub/item non reservable' , 1 ) ;
2382 END IF;
2383
2384 oe_msg_pub.transfer_msg_stack;
2385 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
2386 for I in 1..l_msg_count loop
2387 l_msg_data := OE_MSG_PUB.Get(I,'F');
2388 IF l_debug_level > 0 THEN
2389 oe_debug_pub.add(l_msg_data,1) ;
2390 END IF;
2391 end loop;
2392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2393
2394 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2395 IF l_debug_level > 0 THEN
2396 oe_debug_pub.add('reservation fails with expected error in dropshipreceive() : '||sqlerrm,1) ;
2397 oe_debug_pub.add( 'Try receipt after setting sub/item non reservable' , 1 ) ;
2398 END IF;
2399 oe_msg_pub.transfer_msg_stack;
2400 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
2401 for I in 1..l_msg_count loop
2402 l_msg_data := OE_MSG_PUB.Get(I,'F');
2403 IF l_debug_level > 0 THEN
2404 oe_debug_pub.add(l_msg_data,1) ;
2405 END IF;
2406 end loop;
2407 RAISE FND_API.G_EXC_ERROR;
2408 END IF;
2409
2410 IF l_debug_level > 0 THEN
2411 oe_debug_pub.add( 'Successfully reserved quantity => '||l_quantity_reserved,1) ;
2412 oe_debug_pub.add( 'Successfully reserved quantity2 => '||l_quantity2_reserved,1) ; -- INVCONV
2413
2414 END IF;
2415
2416 x_qty_reserved := l_quantity_reserved;
2417 x_qty2_reserved := l_quantity2_reserved; -- INVCONV
2418 x_rsv_id := l_rsv_id;
2419
2420 IF l_debug_level > 0 THEN
2421 OE_DEBUG_PUB.Add('Exiting Create_Reservation ...',4);
2422 END IF;
2423 EXCEPTION
2424
2425 WHEN FND_API.G_EXC_ERROR THEN
2426 IF l_debug_level > 0 THEN
2427 OE_DEBUG_PUB.Add('Expected Error in Create Reservation...',4);
2428 END IF;
2429
2430 x_return_status := FND_API.G_RET_STS_ERROR;
2431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2432 IF l_debug_level > 0 THEN
2433 OE_DEBUG_PUB.Add('UnExpected Error in Create Reservation...'||sqlerrm,4);
2434 END IF;
2435
2436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2437 WHEN OTHERS THEN
2438 IF l_debug_level > 0 THEN
2439 OE_DEBUG_PUB.Add('When Others in Create Reservation...'||sqlerrm,4);
2440 END IF;
2441
2442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2443
2444 END Create_Reservation;
2445
2446 -- bug 4393738
2447 /* Dropship to call the Ship_Confirm_New */
2448 /*--------------------------------------------------------------+
2449 Name : Call_Ship_Confirm_New
2450 Description : In R12 it was decided that dropship should call
2451 Ship_Confirm_New instead of the old Ship_Confirm API
2452 +--------------------------------------------------------------*/
2453 PROCEDURE Call_Ship_Confirm_New
2454 (p_short_quantity IN NUMBER
2455 ,p_transaction_date IN DATE
2456 ,p_add_to_shipped IN NUMBER
2457 ,p_add_to_shipped2 IN NUMBER
2458 ,p_line_rec IN OE_ORDER_PUB.Line_rec_Type
2459 ,x_return_status OUT NOCOPY VARCHAR2
2460 ,x_msg_count OUT NOCOPY NUMBER
2461 ,x_msg_data OUT NOCOPY VARCHAR2
2462 )
2463 IS
2464 l_ship_adj_line OE_Ship_Confirmation_Pub.Ship_Adj_Rec_Type;
2465 l_non_bulk_req_line OE_Ship_Confirmation_Pub.Ship_Line_Rec_Type;
2466 l_non_bulk_ship_line OE_Ship_Confirmation_Pub.Ship_Line_Rec_Type;
2467 l_cal_tolerance_tbl OE_Shipping_Integration_PUB.Cal_Tolerance_Tbl_Type;
2468 l_update_tolerance_flag varchar2(1);
2469 l_new_tolerance_below number;
2470 l_ship_beyond_flag varchar2(1);
2471 l_fulfilled_flag varchar2(1);
2472 l_proportion_broken_flag varchar2(1);
2473 l_cal_tolr_return_status varchar2(1);
2474 l_msg_count number;
2475 l_msg_data VARCHAR2(20000);
2476 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2477 l_return_status VARCHAR2(1);
2478 BEGIN
2479 IF l_debug_level > 0 THEN
2480 oe_debug_pub.add('OEXVDSRB.pls: Inside Call_Ship_Confirm_New API',3);
2481 END IF;
2482 -- Extending the l_non_bulk_ship_line
2483 l_non_bulk_ship_line.fulfilled_flag.extend;
2484 l_non_bulk_ship_line.actual_shipment_date.extend;
2485 l_non_bulk_ship_line.shipping_quantity2.extend;
2486 l_non_bulk_ship_line.shipping_quantity.extend;
2487 l_non_bulk_ship_line.shipping_quantity_uom2.extend;
2488 l_non_bulk_ship_line.shipping_quantity_uom.extend;
2489 l_non_bulk_ship_line.line_id.extend;
2490 l_non_bulk_ship_line.header_id.extend;
2491 l_non_bulk_ship_line.top_model_line_id.extend;
2492 l_non_bulk_ship_line.ato_line_id.extend;
2493 l_non_bulk_ship_line.ship_set_id.extend;
2494 l_non_bulk_ship_line.arrival_set_id.extend;
2495 l_non_bulk_ship_line.inventory_item_id.extend;
2496 l_non_bulk_ship_line.ship_from_org_id.extend;
2497 l_non_bulk_ship_line.line_set_id.extend;
2498 l_non_bulk_ship_line.smc_flag.extend;
2499 l_non_bulk_ship_line.over_ship_reason_code.extend;
2500 l_non_bulk_ship_line.requested_quantity.extend;
2501 l_non_bulk_ship_line.requested_quantity2.extend;
2502 l_non_bulk_ship_line.pending_quantity.extend;
2503 l_non_bulk_ship_line.pending_quantity2.extend;
2504 l_non_bulk_ship_line.pending_requested_flag.extend;
2505 l_non_bulk_ship_line.order_quantity_uom.extend;
2506 l_non_bulk_ship_line.order_quantity_uom2.extend;
2507 l_non_bulk_ship_line.model_remnant_flag.extend;
2508 l_non_bulk_ship_line.ordered_quantity.extend;
2509 l_non_bulk_ship_line.ordered_quantity2.extend;
2510 l_non_bulk_ship_line.item_type_code.extend;
2511 l_non_bulk_ship_line.calculate_price_flag.extend;
2512 l_non_bulk_ship_line.source_type_code.extend; -- Added for bug 6877315
2513
2514 IF (p_short_quantity > 0 ) THEN -- Partial receipt
2515
2516 IF ((nvl(p_line_rec.ship_tolerance_above,0) > 0) OR
2517 (nvl(p_line_rec.ship_tolerance_below,0) > 0)) THEN -- tolerances specified
2518
2519 l_cal_tolerance_tbl(1).line_id := p_line_rec.line_id;
2520 l_cal_tolerance_tbl(1).quantity_to_be_shipped := p_add_to_shipped;
2521 l_cal_tolerance_tbl(1).shipping_uom := p_line_rec.order_quantity_uom;
2522 IF l_debug_level > 0 THEN
2523 oe_debug_pub.add('Calling OE_Shipping_Integration_PUB.Get_Tolerance to check the tolerances' ,3);
2524 END IF;
2525
2526 OE_Shipping_Integration_PUB.Get_Tolerance(
2527 p_api_version_number => 1.0,
2528 p_cal_tolerance_tbl => l_cal_tolerance_tbl,
2529 x_update_tolerance_flag => l_update_tolerance_flag,
2530 x_ship_tolerance => l_new_tolerance_below,
2531 x_ship_beyond_tolerance => l_ship_beyond_flag,
2532 x_shipped_within_tolerance => l_fulfilled_flag,
2533 x_config_broken => l_proportion_broken_flag,
2534 x_return_status => l_cal_tolr_return_status,
2535 x_msg_count => l_msg_count,
2536 x_msg_data => l_msg_data);
2537
2538 IF l_cal_tolr_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2539 IF l_debug_level > 0 THEN
2540 oe_debug_pub.add('OE_Shipping_Integration_PUB.Get_Tolerance returned Error', 1 ) ;
2541 END IF;
2542 x_return_status := FND_API.G_RET_STS_ERROR;
2543 return;
2544 END IF;
2545
2546 -- If the qty received > the overship tolerance limit and the operation is
2547 -- to be restriced then the l_ship_beyond_flag = 'T' should be checked.
2548
2549 ELSE -- Tolerance not specified and partial receipt hence line not fulfilled
2550 l_fulfilled_flag:='F';
2551 END IF;
2552 ELSE -- full qty received , hence line fulfilled
2553 l_fulfilled_flag:='T';
2554 END IF;
2555
2556 -- Populate the Non bulk ship line
2557 IF (NVL(l_fulfilled_flag, 'F') = 'T') THEN
2558 l_non_bulk_ship_line.fulfilled_flag(1):= 'Y';
2559 ELSE
2560 l_non_bulk_ship_line.fulfilled_flag(1):= 'N';
2561 END IF;
2562 l_non_bulk_ship_line.actual_shipment_date(1) := p_transaction_date;
2563 l_non_bulk_ship_line.shipping_quantity2(1) := p_add_to_shipped2;
2564 l_non_bulk_ship_line.shipping_quantity(1) := p_add_to_shipped;
2565 l_non_bulk_ship_line.shipping_quantity_uom2(1) := p_line_rec.ordered_quantity_uom2;
2566 l_non_bulk_ship_line.shipping_quantity_uom(1) := p_line_rec.order_quantity_uom;
2567 l_non_bulk_ship_line.line_id(1) := p_line_rec.line_id;
2568 l_non_bulk_ship_line.header_id(1) := p_line_rec.header_id;
2569 l_non_bulk_ship_line.top_model_line_id(1) := p_line_rec.top_model_line_id;
2570 l_non_bulk_ship_line.ato_line_id(1) := p_line_rec.ato_line_id;
2571 l_non_bulk_ship_line.ship_set_id(1) := p_line_rec.ship_set_id;
2572 l_non_bulk_ship_line.arrival_set_id(1) := p_line_rec.arrival_set_id;
2573 l_non_bulk_ship_line.inventory_item_id(1) := p_line_rec.inventory_item_id;
2574 l_non_bulk_ship_line.ship_from_org_id(1) := p_line_rec.ship_from_org_id;
2575 l_non_bulk_ship_line.line_set_id(1) := p_line_rec.line_set_id;
2576 l_non_bulk_ship_line.smc_flag(1) := p_line_rec.ship_model_complete_flag;
2577 l_non_bulk_ship_line.over_ship_reason_code(1) := '0';
2578 l_non_bulk_ship_line.requested_quantity(1) := p_add_to_shipped;
2579 l_non_bulk_ship_line.requested_quantity2(1) := p_add_to_shipped2;
2580 l_non_bulk_ship_line.pending_quantity(1) := NULL;
2581 l_non_bulk_ship_line.pending_quantity2(1) := NULL;
2582 l_non_bulk_ship_line.pending_requested_flag(1) := NULL;
2583 l_non_bulk_ship_line.order_quantity_uom(1) := p_line_rec.order_quantity_uom;
2584 l_non_bulk_ship_line.order_quantity_uom2(1) := p_line_rec.ordered_quantity_uom2;
2585 l_non_bulk_ship_line.model_remnant_flag(1) := p_line_rec.model_remnant_flag;
2586 l_non_bulk_ship_line.ordered_quantity(1) := p_line_rec.ordered_quantity;
2587 l_non_bulk_ship_line.ordered_quantity2(1) := p_line_rec.ordered_quantity2;
2588 l_non_bulk_ship_line.item_type_code(1) := p_line_rec.item_type_code;
2589 l_non_bulk_ship_line.calculate_price_flag(1) := p_line_rec.calculate_price_flag;
2590 l_non_bulk_ship_line.source_type_code(1) := p_line_rec.source_type_code; -- Added for bug 6877315
2591
2592 -- Calling Ship_confirm_new
2593 OE_Ship_Confirmation_Pub.Ship_Confirm_New
2594 ( P_ship_line_rec => l_non_bulk_ship_line,
2595 P_requested_line_rec => l_non_bulk_req_line,
2596 P_line_adj_rec => l_ship_adj_line, -- not used in non_bulk_mode
2597 P_bulk_mode => 'N',
2598 P_start_index => 1, -- not used in non_bulk_mode
2599 P_end_index => 1, -- not used in non_bulk_mode
2600 x_msg_count => l_msg_count,
2601 x_msg_data => l_msg_data,
2602 x_return_status => l_return_status);
2603
2604 IF l_debug_level > 0 THEN
2605 oe_debug_pub.add('OEXVDSRB.pls: OE_Ship_Confirmation_Pub.Ship_Confirm_New return_status='||l_return_status,3);
2606 END IF;
2607
2608 x_return_status := l_return_status;
2609 x_msg_data := l_msg_data;
2610 x_msg_count := l_msg_count;
2611
2612 EXCEPTION
2613 WHEN OTHERS THEN
2614 x_return_status := l_return_status;
2615 x_msg_data := l_msg_data;
2616 x_msg_count := l_msg_count;
2617 END Call_Ship_Confirm_New;
2618
2619 /*--------------------------------------------------------------+
2620 Name : Call_Process_Order
2621 n_bulk_ship_line OE_Ship_Confirmation_Pub.Ship_Line_Rec_Type;
2622 l_non_bulk_req_line OE_Ship_Confirmation_Pub.Ship_Line_Rec_Type;
2623 Description : This procedure will be called from Drop Ship
2624 Receive. This will call process order for
2625 updating shipped quantity on the order line
2626 and complete the ship line activity
2627
2628 Change Record :
2629 +--------------------------------------------------------------*/
2630
2631 PROCEDURE Call_Process_Order
2632 (p_orig_shipped IN NUMBER
2633 ,p_short_quantity IN NUMBER
2634 ,p_transaction_date IN DATE
2635 ,p_add_to_shipped IN NUMBER
2636 ,p_add_to_shipped2 IN NUMBER
2637 ,p_line_rec IN OE_ORDER_PUB.Line_rec_Type
2638 ,x_return_status OUT NOCOPY VARCHAR2
2639 )
2640 IS
2641 -- Process Order arguments
2642 l_msg_count NUMBER;
2643 l_msg_data VARCHAR2(20000);
2644
2645 l_control_rec OE_GLOBALS.control_rec_type;
2646 l_line_tbl OE_ORDER_PUB.line_tbl_type;
2647 l_old_line_tbl OE_ORDER_PUB.line_tbl_type;
2648 l_header_rec OE_Order_PUB.Header_Rec_Type;
2649 l_new_line_rec OE_Order_PUB.Line_Rec_Type;
2650 l_new_line_tbl OE_Order_PUB.Line_Tbl_Type;
2651 l_header_adj_out_tbl OE_Order_PUB.Header_Adj_Tbl_Type;
2652 l_header_scredit_out_tbl OE_Order_PUB.Header_Scredit_Tbl_Type;
2653 l_line_adj_out_tbl OE_Order_PUB.Line_Adj_Tbl_Type;
2654 l_line_scredit_out_tbl OE_Order_PUB.Line_Scredit_Tbl_Type;
2655 l_lot_serial_out_tbl OE_Order_PUB.Lot_Serial_Tbl_Type;
2656 l_action_request_out_tbl OE_Order_PUB.Request_Tbl_Type;
2657 l_Header_Adj_Att_tbl OE_ORDER_PUB.Header_Adj_Att_Tbl_Type;
2658 l_Header_Adj_Assoc_tbl OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type;
2659 l_Header_price_Att_tbl OE_ORDER_PUB.Header_Price_Att_Tbl_Type;
2660 l_Line_Price_Att_tbl OE_ORDER_PUB.Line_Price_Att_Tbl_Type;
2661 l_Line_Adj_Att_tbl OE_ORDER_PUB.Line_Adj_Att_Tbl_Type;
2662 l_Line_Adj_Assoc_tbl OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type;
2663
2664 l_req_qty_tbl OE_Ship_Confirmation_Pub.Req_Quantity_Tbl_Type;
2665
2666 l_new_line_id NUMBER;
2667 l_return_status VARCHAR2(1);
2668 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2669 --serla begin
2670 l_x_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
2671 l_x_Line_Payment_tbl OE_Order_PUB.Line_Payment_Tbl_Type;
2672 --serla end
2673 -- tso
2674 l_top_container_model Varchar2(1);
2675 l_part_of_container Varchar2(1);
2676
2677 BEGIN
2678
2679 -- Bug 2312461: bypass the processing in OM if receiving has already been done once.
2680
2681 IF (p_orig_shipped <> 0) --one receipt already exists
2682 THEN
2683 IF l_debug_level > 0 THEN
2684 oe_debug_pub.add('Receiving has already been done once. no processing in om' ) ;
2685 END IF;
2686 ELSE --first receipt against this line
2687
2688 IF l_debug_level > 0 THEN
2689 oe_debug_pub.add('Calling Call_Ship_Confirm_New API' , 1 ) ;
2690 END IF;
2691
2692 -- Calling this new API, bug 4393738
2693 Call_Ship_Confirm_New
2694 (p_short_quantity => p_short_quantity
2695 ,p_transaction_date => p_transaction_date
2696 ,p_add_to_shipped => p_add_to_shipped
2697 ,p_add_to_shipped2 => p_add_to_shipped2
2698 ,p_line_rec => p_line_rec
2699 ,x_return_status => l_return_status
2700 ,x_msg_count => l_msg_count
2701 ,x_msg_data => l_msg_data);
2702
2703 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2704 IF l_debug_level > 0 THEN
2705 oe_debug_pub.add('OEXVDSRB.pls: Call_Ship_Confirm_New returned unexpected error '||sqlerrm , 1 ) ;
2706 END IF;
2707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2708 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2709 IF l_debug_level > 0 THEN
2710 oe_debug_pub.add('OEXVDSRB.pls: Call_Ship_Confirm_New returned expected error '||sqlerrm , 1 ) ;
2711 END IF;
2712 RAISE FND_API.G_EXC_ERROR;
2713 END IF;
2714
2715 IF l_debug_level > 0 THEN
2716 oe_debug_pub.add('OEXVDSRB.pls: After Call_Ship_Confirm_New, return_status='|| l_return_status,1) ;
2717 END IF;
2718
2719 IF l_debug_level > 0 THEN
2720 oe_debug_pub.add('OEXVDSRB.pls: Calling OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model...' , 5 ) ;
2721 END IF;
2722
2723 -- TSO with Equipment, the non-shippable lines should have NULL shipped_quantity
2724 OE_CONFIG_TSO_PVT.Is_Part_Of_Container_Model
2725 (p_line_id => p_line_rec.line_id,
2726 p_top_model_line_id => p_line_rec.top_model_line_id,
2727 x_top_container_model => l_top_container_model,
2728 x_part_of_container => l_part_of_container);
2729
2730 IF l_part_of_container = 'Y' THEN
2731
2732 UPDATE oe_order_lines_all
2733 SET shipped_quantity = NULL
2734 ,actual_shipment_date = NULL
2735 ,lock_control = lock_control + 1
2736 WHERE top_model_line_id = p_line_rec.top_model_line_id
2737 AND shippable_flag = 'N'
2738 AND shipped_quantity is not NULL;
2739
2740 IF l_debug_level > 0 AND
2741 SQL%FOUND THEN
2742 oe_debug_pub.add('Updated non-shippable lines of TSO ...',5);
2743 END IF;
2744 END IF;
2745 -- TSO with equipment ends
2746
2747 IF ( p_short_quantity > 0 ) THEN
2748 IF l_debug_level > 0 THEN
2749 oe_debug_pub.add('After call from process order api check if split has happened ' , 5 ) ;
2750 END IF;
2751 BEGIN
2752 SELECT max(line_id)
2753 INTO l_new_line_id
2754 FROM oe_order_lines_all
2755 WHERE header_id = p_line_rec.header_id --Bug2489150
2756 AND split_from_line_id = p_line_rec.line_id
2757 AND split_by = 'SYSTEM'; -- Bug-2437391
2758 EXCEPTION
2759 WHEN NO_DATA_FOUND THEN
2760 IF l_debug_level > 0 THEN
2761 oe_debug_pub.add( 'Split line not found '||sqlerrm , 1 ) ;
2762 END IF;
2763 WHEN OTHERS THEN
2764 IF l_debug_level > 0 THEN
2765 oe_debug_pub.add( 'Unexpected: line not found '||sqlerrm , 1 ) ;
2766 END IF;
2767 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2768 END;
2769 -- Bug2344242 added following if condition
2770 IF l_new_line_id is NOT NULL THEN
2771 IF l_debug_level > 0 THEN
2772 oe_debug_pub.add( 'Updating existing dropship record with new line '||l_new_line_id , 1 ) ;
2773 END IF;
2774
2775 update oe_drop_ship_sources
2776 set line_id = l_new_line_id
2777 where line_id = p_line_rec.line_id;
2778
2779 insert_oe_drop_ship_source(p_line_rec.line_id, l_new_line_id);
2780
2781 IF l_debug_level > 0 THEN
2782 oe_debug_pub.add( 'After inserting drop ship source record ' , 5 ) ;
2783 END IF;
2784 END IF;
2785 END IF;
2786
2787 END IF; --one receipt already exists
2788
2789 IF l_debug_level > 0 THEN
2790 OE_DEBUG_PUB.Add('Exiting Call Process Order...',4);
2791 END IF;
2792 EXCEPTION
2793 WHEN FND_API.G_EXC_ERROR THEN
2794 IF l_debug_level > 0 THEN
2795 OE_DEBUG_PUB.Add('Expected Error in Call Process Order...',4);
2796 END IF;
2797
2798 x_return_status := FND_API.G_RET_STS_ERROR;
2799 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2800 IF l_debug_level > 0 THEN
2801 OE_DEBUG_PUB.Add('UnExpected Error in Call Process Order...'||sqlerrm,4);
2802 END IF;
2803
2804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2805 WHEN OTHERS THEN
2806 IF l_debug_level > 0 THEN
2807 OE_DEBUG_PUB.Add('When Others in Call Process Order...'||sqlerrm,4);
2808 END IF;
2809
2810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2811
2812 END Call_Process_Order;
2813
2814 ------------------------------------------------------
2815 -- *** Enhanced Dropshipments ***
2816 ------------------------------------------------------
2817
2818 /*--------------------------------------------------------------+
2819 Name : Check_Req_PO_Cancelled
2820 Description : This procedure will be used to check whether a
2821 Req or PO is cancelled or not. This will called
2822 before logging CMS delayed request. This will
2823 return true if req or PO is cancelled for a
2824 given line.
2825
2826 Change Record :
2827 +--------------------------------------------------------------*/
2828
2829
2830 FUNCTION Check_Req_PO_Cancelled
2831 ( p_line_id IN NUMBER
2832 , p_header_id IN NUMBER
2833 ) RETURN BOOLEAN
2834 IS
2835 l_req_header_id NUMBER;
2836 l_po_header_id NUMBER;
2837 l_req_dsp VARCHAR2(240);
2838 l_req_err VARCHAR2(240);
2839 --l_po_status VARCHAR2(4100);
2840 l_req_status VARCHAR2(4100);
2841 --bug 4411054
2842 l_po_status_rec PO_STATUS_REC_TYPE;
2843 l_return_status VARCHAR2(1);
2844 l_cancel_flag VARCHAR2(1);
2845 l_closed_code VARCHAR2(30);
2846 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2847 l_po_release_id NUMBER; -- bug 5328526
2848
2849 BEGIN
2850
2851 IF l_debug_level > 0 THEN
2852 OE_DEBUG_PUB.Add('Entering Check_Req_PO_Cancelled.. ',1);
2853 END IF;
2854
2855 SELECT requisition_header_id, po_header_id, po_release_id
2856 INTO l_req_header_id,l_po_header_id, l_po_release_id --bug 5328526
2857 FROM oe_drop_ship_sources
2858 WHERE line_id = p_line_id
2859 AND header_id = p_header_id;
2860
2861 IF l_debug_level > 0 THEN
2862 OE_DEBUG_PUB.Add('Requisition:'||l_req_header_id||
2863 ' PO:'||l_po_header_id||
2864 ' PO Release:'||l_po_release_id,1);
2865 END IF;
2866
2867 IF l_req_header_id is not null THEN
2868
2869 l_req_status := PO_RELEASES_SV2.Get_Release_Status
2870 ( x_po_release_id => l_req_header_id
2871 );
2872 IF l_debug_level > 0 THEN
2873 OE_DEBUG_PUB.Add('Requisition Status - '|| l_req_status, 3);
2874 END IF;
2875
2876 IF l_req_status is null THEN
2877 PO_REQS_SV2.Get_Reqs_Auth_Status
2878 (x_req_header_id => l_req_header_id
2879 ,x_req_header_auth_status => l_req_status
2880 ,x_req_header_auth_status_dsp => l_req_dsp
2881 ,x_req_control_error_rc => l_req_err
2882 );
2883
2884 l_req_status := UPPER(l_req_status);
2885 END IF;
2886
2887 IF l_debug_level > 0 THEN
2888 OE_DEBUG_PUB.Add('After Requisition Auth Status - '|| l_req_status, 3);
2889 END IF;
2890
2891 END IF;
2892
2893
2894 IF l_po_header_id is not null THEN
2895
2896 -- comment out for bug 4411054
2897 /*l_po_status := UPPER(PO_HEADERS_SV3.Get_PO_Status
2898 (x_po_header_id => l_po_header_id
2899 ));
2900
2901 IF l_debug_level > 0 THEN
2902 OE_DEBUG_PUB.Add('PO Status : '|| l_po_status, 2);
2903 END IF;
2904 */
2905 PO_DOCUMENT_CHECKS_GRP.po_status_check
2906 (p_api_version => 1.0
2907 , p_header_id => l_po_header_id
2908 , p_release_id => l_po_release_id --bug 5328526
2909 , p_mode => 'GET_STATUS'
2910 , x_po_status_rec => l_po_status_rec
2911 , x_return_status => l_return_status);
2912 IF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2913 l_cancel_flag := l_po_status_rec.cancel_flag(1);
2914 l_closed_code := l_po_status_rec.closed_code(1);
2915 IF l_debug_level > 0 THEN
2916 OE_DEBUG_PUB.Add('Sucess call from PO_DOCUMENT_CHECKS_GRP.po_status_check',2);
2917 OE_DEBUG_PUB.Add('Cancel_flag : '|| l_cancel_flag, 2);
2918 OE_DEBUG_PUB.Add('Closed_code : '|| l_closed_code,2);
2919 END IF;
2920 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2921 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2922 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2923 RAISE FND_API.G_EXC_ERROR;
2924 END IF;
2925
2926 END IF;
2927
2928 IF l_req_header_id is not null OR
2929 l_po_header_id is not null THEN
2930
2931 IF (INSTR(nvl(l_req_status,'z'), 'CANCELLED') > 0 AND
2932 INSTR(nvl(l_req_status,'z'), 'FINALLY CLOSED') > 0) OR
2933 --(INSTR(nvl(l_po_status, 'z'), 'CANCELLED') > 0 AND
2934 -- INSTR(nvl(l_po_status, 'z'), 'FINALLY CLOSED') > 0) THEN
2935 (nvl(l_cancel_flag,'z')='Y' AND
2936 nvl(l_closed_code, 'z')= 'FINALLY CLOSED' ) THEN
2937
2938 IF l_debug_level > 0 THEN
2939 OE_DEBUG_PUB.Add('Requisition or PO is cancelled',1);
2940 END IF;
2941
2942 RETURN TRUE;
2943 ELSE
2944 RETURN FALSE;
2945 END IF;
2946
2947 END IF;
2948
2949 RETURN FALSE;
2950
2951 EXCEPTION
2952 WHEN NO_DATA_FOUND THEN
2953 IF l_debug_level > 0 THEN
2954 OE_DEBUG_PUB.Add('No Data Found in Check_Req_PO_Cancelled', 4);
2955 END IF;
2956 RETURN FALSE;
2957 WHEN OTHERS THEN
2958 IF l_debug_level > 0 THEN
2959 OE_DEBUG_PUB.Add('When Others in Check_Req_PO_Cancelled'|| sqlerrm, 3);
2960 END IF;
2961 RETURN FALSE;
2962 END Check_Req_PO_Cancelled;
2963
2964 /*--------------------------------------------------------------+
2965 Name : Check_PO_Approved
2966 Description : This procedure will be used in constraints
2967 frame work and will be used to check whether a
2968 PO is approved or not. For a given line id
2969 get the po header id and call the PO API to
2970 get the status. If it is approved return
2971 true else false.
2972 Change Record :
2973 +--------------------------------------------------------------*/
2974
2975 Procedure Check_PO_Approved
2976 ( p_application_id IN NUMBER
2977 , p_entity_short_name IN VARCHAR2
2978 , p_validation_entity_short_name IN VARCHAR2
2979 , p_validation_tmplt_short_name IN VARCHAR2
2980 , p_record_set_tmplt_short_name IN VARCHAR2
2981 , p_scope IN VARCHAR2
2982 , p_result OUT NOCOPY /* file.sql.39 change */ NUMBER
2983 )
2984 IS
2985
2986 l_line_id NUMBER := oe_line_security.g_record.line_id;
2987 l_header_id NUMBER := oe_line_security.g_record.header_id;
2988 l_ato_line_id NUMBER := oe_line_security.g_record.ato_line_id;
2989 l_item_type_code VARCHAR2(30) := oe_line_security.g_record.item_type_code;
2990 l_source_type_code VARCHAR2(30) := oe_line_security.g_record.source_type_code;
2991 l_operation VARCHAR2(30) := oe_line_security.g_record.operation;
2992
2993 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2994
2995 l_po_header_id NUMBER;
2996 --bug 4411054
2997 --l_po_status VARCHAR2(4100);
2998 l_po_status_rec PO_STATUS_REC_TYPE;
2999 l_return_status VARCHAR2(1);
3000 l_autorization_status VARCHAR2(30);
3001 l_po_release_id NUMBER; -- bug 5328526
3002
3003 BEGIN
3004
3005 p_result := 0;
3006
3007 IF NVL(l_line_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
3008 RETURN;
3009 END IF;
3010
3011 IF l_source_type_code <> 'EXTERNAL' OR
3012 NVL(l_source_type_code,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
3013 RETURN;
3014 END IF;
3015
3016 IF (l_ato_line_id IS NOT NULL AND
3017 l_ato_line_id <> FND_API.G_MISS_NUM) AND
3018 NOT (l_item_type_code in('OPTION','STANDARD','INCLUDED') AND --9775352
3019 l_ato_line_id = l_line_id ) THEN
3020
3021 IF l_debug_level > 0 THEN
3022 OE_DEBUG_PUB.Add('Line part of a ATO Model: '||l_ato_line_id, 2);
3023 END IF;
3024
3025 SELECT po_header_id, po_release_id
3026 INTO l_po_header_id, l_po_release_id --bug 5328526
3027 FROM oe_drop_ship_sources ds,oe_order_lines l
3028 WHERE ds.header_id = l_header_id
3029 AND l.item_type_code = 'CONFIG'
3030 AND l.line_id = ds.line_id
3031 AND l.ato_line_id = l_ato_line_id;
3032
3033 ELSE
3034
3035 IF (l_operation IS NOT NULL AND
3036 l_operation <> FND_API.G_MISS_CHAR) AND
3037 l_operation <> OE_GLOBALS.G_OPR_CREATE THEN
3038
3039 SELECT po_header_id, po_release_id
3040 INTO l_po_header_id, l_po_release_id --bug 5328526
3041 FROM oe_drop_ship_sources
3042 WHERE line_id = l_line_id
3043 AND header_id = l_header_id;
3044 END IF;
3045
3046 END IF;
3047
3048 IF l_po_header_id is not null THEN
3049
3050 -- comment out for bug 4411054
3051 /*l_po_status := UPPER(PO_HEADERS_SV3.Get_PO_Status
3052 (x_po_header_id => l_po_header_id
3053 ));
3054
3055 IF l_debug_level > 0 THEN
3056 OE_DEBUG_PUB.Add('Check PO Status : '|| l_po_status, 2);
3057 END IF;
3058 */
3059
3060 PO_DOCUMENT_CHECKS_GRP.po_status_check
3061 (p_api_version => 1.0
3062 , p_header_id => l_po_header_id
3063 , p_release_id => l_po_release_id --bug 5328526
3064 , p_mode => 'GET_STATUS'
3065 , x_po_status_rec => l_po_status_rec
3066 , x_return_status => l_return_status);
3067
3068 IF(l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
3069 l_autorization_status := l_po_status_rec.authorization_status(1);
3070
3071 IF l_debug_level > 0 THEN
3072 OE_DEBUG_PUB.Add('Sucess call from PO_DOCUMENT_CHECKS_GRP.po_status_check',2);
3073 OE_DEBUG_PUB.Add('Check PO Status : '|| l_autorization_status, 2);
3074 END IF;
3075 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3077 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3078 RAISE FND_API.G_EXC_ERROR;
3079 END IF;
3080
3081 END IF;
3082
3083 --IF (INSTR(nvl(l_po_status,'z'), 'APPROVED') <> 0 ) THEN
3084 IF(nvl(l_autorization_status,'z')= 'APPROVED') THEN
3085 p_result := 1;
3086 ELSE
3087 p_result := 0;
3088
3089 END IF;
3090
3091
3092
3093 EXCEPTION
3094 WHEN NO_DATA_FOUND THEN
3095 p_result := 0;
3096
3097 IF l_debug_level > 0 THEN
3098 OE_DEBUG_PUB.Add('No Data Found in Check_PO_Approved', 4);
3099 END IF;
3100 WHEN OTHERS THEN
3101 p_result := 1;
3102
3103 IF l_debug_level > 0 THEN
3104 OE_DEBUG_PUB.Add('When Others in Check_PO_Approved', 4);
3105 END IF;
3106
3107 End Check_PO_Approved;
3108
3109 /*--------------------------------------------------------------+
3110 Name : OM_PO_Discrepancy_Exists
3111 Description : This procedure will be used in constraints
3112 frame work and will be used to check whether
3113 there is any existing discrepancy between OM and PO
3114 Change Record :
3115 +--------------------------------------------------------------*/
3116
3117 Procedure OM_PO_Discrepancy_Exists
3118 ( p_application_id IN NUMBER
3119 , p_entity_short_name IN VARCHAR2
3120 , p_validation_entity_short_name IN VARCHAR2
3121 , p_validation_tmplt_short_name IN VARCHAR2
3122 , p_record_set_tmplt_short_name IN VARCHAR2
3123 , p_scope IN VARCHAR2
3124 , p_result OUT NOCOPY /* file.sql.39 change */ NUMBER
3125 )
3126 IS
3127 l_line_id NUMBER := oe_line_security.g_record.line_id;
3128 l_header_id NUMBER := oe_line_security.g_record.header_id;
3129 l_drop_ship_flag VARCHAR2(1);
3130 l_source_type_code VARCHAR2(10) := oe_line_security.g_record.source_type_code;
3131
3132 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3133
3134 BEGIN
3135
3136 p_result := 0;
3137
3138 IF NVL(l_line_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
3139 RETURN;
3140 END IF;
3141
3142 IF l_source_type_code <> 'EXTERNAL' OR
3143 NVL(l_source_type_code,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
3144 RETURN;
3145 END IF;
3146
3147
3148 SELECT drop_ship_flag
3149 INTO l_drop_ship_flag
3150 FROM oe_drop_ship_sources l,po_requisition_lines_all rl
3151 WHERE l.line_id = l_line_id
3152 AND l.header_id = l_header_id
3153 AND l.requisition_line_id = rl.requisition_line_id;
3154
3155 IF NVL(l_drop_ship_flag,'X') = 'X' THEN
3156
3157 p_result := 0;
3158
3159 IF l_debug_level > 0 THEN
3160 OE_DEBUG_PUB.Add('OM PO Discrepancy Exists', 4);
3161 END IF;
3162 ELSE
3163 p_result := 1;
3164
3165 IF l_debug_level > 0 THEN
3166 OE_DEBUG_PUB.Add('No Discrepancy Exisits', 4);
3167 END IF;
3168 END IF;
3169
3170 EXCEPTION
3171 WHEN NO_DATA_FOUND THEN
3172 p_result := 0;
3173
3174 IF l_debug_level > 0 THEN
3175 OE_DEBUG_PUB.Add('No Data Found in OM_PO_Discrepancy_Exists', 4);
3176 END IF;
3177 WHEN OTHERS THEN
3178 p_result := 1;
3179
3180 IF l_debug_level > 0 THEN
3181 OE_DEBUG_PUB.Add('When Others in OM_PO_Discrepancy_Exists', 4);
3182 END IF;
3183 END OM_PO_Discrepancy_Exists;
3184
3185 END OE_DS_PVT;