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