[Home] [Help]
PACKAGE BODY: APPS.GMI_PICK_WAVE_CONFIRM_PVT
Source
1 PACKAGE BODY GMI_PICK_WAVE_CONFIRM_PVT AS
2 /* $Header: GMIVPWCB.pls 120.1 2005/08/30 08:24:08 nchekuri noship $ */
3 /* +=========================================================================+
4 | Copyright (c) 2000 Oracle Corporation |
5 | TVP, Reading, England |
6 | All rights reserved |
7 +=========================================================================+
8 | FILENAME |
9 | GMIVPWCB.pls |
10 | |
11 | DESCRIPTION |
12 | This package contains Private Routines relating to GMI |
13 | Move Order LINES. |
14 | |
15 | |
16 | |
17 | HISTORY |
18 | 03-MAY-2000 Hverddin Created |
19 | 09/10/01 HW BUG#:1941429 Added code to supoort cross_docking |
20 | 11/22/2002 PKU BUG# 2675737 Secondary qty should not be checked |
21 | for over or under shipment tolerances |
22 | |
23 | Dec. 5th, 2002 HW Added NOCOPY to Pick_confirm procedure |
24 | Jan. 23rd,2004 HAW 3387829. Issue with splitting non-lot and |
25 | non-inv items. Problem is populating the correct |
26 | trans_id during pick confirm which affected |
27 | the split and there was a mismatch between |
28 | shipping lines and transactions in line_detail_id|
29 | Sep. 3rd, 2004 HAW BUG#:3871662. Removed cursor check_wsh |
30 | Sep. 3rd 2004 PKU BUG 3859774 |
31 | ONLY primary allocations should be compared with primary available |
32 +=========================================================================+
33 API Name : GMI_PICK_WAVE_CONFIRM_PVT
34 Type : Global
35 -
36 Pre-reqs : N/A
37 Parameters: Per function
38
39 Current Vers : 1.0
40 */
41 PROCEDURE check_quantity_to_pick(
42 p_order_line_id IN NUMBER,
43 p_quantity_to_pick IN NUMBER,
44 p_quantity2_to_pick IN NUMBER DEFAULT NULL,
45 x_allowed_flag OUT NOCOPY VARCHAR2,
46 x_max_quantity_allowed OUT NOCOPY NUMBER,
47 x_max_quantity2_allowed OUT NOCOPY NUMBER,
48 x_avail_req_quantity OUT NOCOPY NUMBER,
49 x_avail_req_quantity2 OUT NOCOPY NUMBER,
50 x_return_status OUT NOCOPY VARCHAR2
51 );
52
53 PROCEDURE Get_Min_Max_Tolerance_Quantity
54 (
55 p_api_version_number IN NUMBER
56 , p_line_id IN NUMBER
57 , x_min_remaining_quantity OUT NOCOPY NUMBER
58 , x_max_remaining_quantity OUT NOCOPY NUMBER
59 , x_min_remaining_quantity2 OUT NOCOPY NUMBER
60 , x_max_remaining_quantity2 OUT NOCOPY NUMBER
61 , x_return_status OUT NOCOPY VARCHAR2
62 , x_msg_count OUT NOCOPY NUMBER
63 , x_msg_data OUT NOCOPY VARCHAR2
64 );
65
66 PROCEDURE dump_shp_attrb_data
67 (
68 p_shipping_attr IN WSH_INTERFACE.ChangedAttributeTabType
69 );
70
71 -- BEGIN Bug 3776538
72 PROCEDURE truncate_trans_qty
73 (
74 p_line_id IN number,
75 p_delivery_detail_id IN number,
76 p_default_location IN varchar2,
77 is_lot_loct_ctl IN boolean,
78 x_return_status OUT NOCOPY VARCHAR2
79 );
80 -- END Bug 3776538
81
82 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_PICK_WAVE_CONFIRM_PVT';
83
84
85
86
87 -- HW BUG# 2296620
88 G_WARNING_SHIP_SET CONSTANT VARCHAR2(1):='X';
89
90 PROCEDURE CHECK_SHIP_SET (
91 p_ship_set_id IN NUMBER
92 , p_manual_pick IN VARCHAR2
93 , x_return_status OUT NOCOPY VARCHAR2
94 )
95 IS
96
97 l_api_name CONSTANT VARCHAR2 (30) := 'PICK_CONFIRM';
98 l_api_version_number CONSTANT NUMBER := 1.0;
99
100 cursor get_qty IS
101 SELECT quantity,
102 quantity_detailed,
103 line_status
104 FROM IC_TXN_REQUEST_LINES
105 WHERE ship_set_id = p_ship_set_id
106 AND LINE_STATUS = 7;
107
108
109
110 l_warning EXCEPTION;
111 l_manual_warning EXCEPTION;
112 l_ship_set_id NUMBER;
113
114 BEGIN
115
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117
118 l_ship_set_id := p_ship_set_id;
119
120 For mo_line in get_qty LOOP
121
122 IF ( mo_line.quantity > mo_line.quantity_detailed ) THEN
123
124 gmi_reservation_util.println('HELLO from the loop');
125 gmi_reservation_util.println('Value of quantity in chk_ship_set is '||mo_line.quantity );
126 gmi_reservation_util.println('Value of l_qty_detailed is ' ||mo_line.quantity_detailed);
127
128 IF ( p_manual_pick = 'Y' ) THEN
129 gmi_reservation_util.println('Raising manual warning');
130 RAISE l_manual_warning;
131 ELSIF ( p_manual_pick ='N')THEN
132 gmi_reservation_util.println('Ship set is broken. Qty allocated less than requested');
133 RAISE l_warning;
134 END IF; -- of manual or auto allocation
135
136 END IF; -- if qty > qty_detaild
137
138 END LOOP; -- end of loop */
139 gmi_reservation_util.println('HELLO from end of chk-set prc.');
140
141
142 EXCEPTION
143 WHEN FND_API.G_EXC_ERROR THEN
144 x_return_status := fnd_api.g_ret_sts_error;
145
146
147 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
148 x_return_status := fnd_api.g_ret_sts_unexp_error;
149
150 WHEN l_warning THEN
151 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
152
153 WHEN l_manual_warning THEN
154 x_return_status := G_WARNING_SHIP_SET;
155
156 WHEN OTHERS THEN
157 x_return_status := fnd_api.g_ret_sts_unexp_error;
158
159
160 END CHECK_SHIP_SET;
161
162 -- HW end of 2296620
163 /* LG added delivery detail id so it can be performed on a single delivery only */
164 -- HW OPM changes for NOCOPY
165 -- Added NOCOPY to x_mo_line_tbl
166 PROCEDURE PICK_CONFIRM
167 (
168 p_api_version_number IN NUMBER
169 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
170 , p_validation_flag IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
171 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
172 , p_delivery_detail_id IN NUMBER DEFAULT NULL
173 , p_mo_line_tbl IN GMI_Move_Order_Global.MO_LINE_TBL
174 , x_mo_line_tbl OUT NOCOPY GMI_Move_Order_Global.MO_LINE_TBL
175 , x_return_status OUT NOCOPY VARCHAR2
176 , x_msg_count OUT NOCOPY NUMBER
177 , x_msg_data OUT NOCOPY VARCHAR2
178 , p_manual_pick IN VARCHAR2 DEFAULT NULL
179 )
180 IS
181 l_api_name CONSTANT VARCHAR2 (30) := 'PICK_CONFIRM';
182 l_api_version_number CONSTANT NUMBER := 1.0;
183
184 l_msg_count NUMBER :=0;
185 l_msg_data VARCHAR2(2000);
186 l_return_status VARCHAR2(1):=FND_API.G_RET_STS_SUCCESS;
187 l_mo_line_rec GMI_MOVE_ORDER_GLOBAL.mo_line_rec;
188 l_mo_line_tbl GMI_MOVE_ORDER_GLOBAL.mo_line_tbl;
189 l_location IC_TRAN_PND.LOCATION%TYPE;
190 l_ic_item_mst_rec GMI_RESERVATION_UTIL.ic_item_mst_rec;
191 l_shipping_attr WSH_INTERFACE.ChangedAttributeTabType;
192 l_trans_qty NUMBER;
193 l_trans_qty2 NUMBER;
194 l_trans_qty_sts NUMBER;
195 l_trans_qty2_sts NUMBER;
196 l_converted_trans_qty NUMBER;
197 l_sum_trans_qty NUMBER;
198 l_sum_trans_qty2 NUMBER;
199 l_requested_qty NUMBER;
200 l_requested_qty2 NUMBER;
201 l_source_header_id NUMBER;
202 l_source_line_id NUMBER;
203 l_delivery_detail_id NUMBER;
204 l_delivery_qty NUMBER;
205 l_default_transaction NUMBER(1);
206 l_default_exist NUMBER(1) := 1;
207 l_action_flag VARCHAR2(1);
208 l_order_quantity_uom VARCHAR2(3);
209 l_whse_code VARCHAR2(5);
210 l_organization_id NUMBER;
211 l_backorder_qty NUMBER;
212 l_backorder_qty2 NUMBER;
213 l_count NUMBER;
214 l_whse_ctl NUMBER;
215 l_allowed VARCHAR2(1);
216 l_max_qty NUMBER;
217 l_max_qty2 NUMBER;
218 l_status_ctl NUMBER;
219 l_orderable_ind NUMBER;
220 l_shipable_ind NUMBER;
221 l_backorder_del NUMBER;
222 l_transactable NUMBER;
223
224 l_manual_pick VARCHAR2(1);
225 l_no_error VARCHAR2(1) :='1' ;
226 l_warning EXCEPTION;
227 l_tolerance_warning EXCEPTION;
228 l_tolerance_flag NUMBER := 0;
229 l_warning_flag NUMBER := 0 ;
230 -- Begin Bug 3609713
231 l_allowneginv NUMBER;
232 l_onhand1 NUMBER;
233 l_onhand2 NUMBER;
234 l_alloc1 NUMBER;
235 l_alloc2 NUMBER;
236 l_inv_negative NUMBER := 0;
237 -- End Bug 3609713
238
239 l_ALLOW_OPM_TRUNCATE_TXN VARCHAR2(4); -- Bug 3776538
240 /* define Cursors; */
241
242 /* If Item is Lot or Location Controlled */
243
244 CURSOR cur_txn_no_default ( p_line_id NUMBER,
245 p_location VARCHAR2,
246 p_item_id NUMBER)
247 is
248 SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
249 FROM ic_tran_pnd
250 WHERE line_id = p_line_id
251 AND ( lot_id > 0
252 OR location <> p_location )
253 -- AND item_id = p_item_id -- REMOVED for bug 3403418
254 AND doc_type = 'OMSO'
255 AND staged_ind = 0
256 AND completed_ind = 0
257 AND delete_mark = 0
258 AND line_detail_id in
259 (Select delivery_detail_id
260 From wsh_delivery_details
261 Where move_order_line_id = l_mo_line_rec.line_id
262 and released_status in ('R','S'));
263
264 /* If Item is NOT Lot AND NOT Location Controlled */
265
266 CURSOR cur_txn_with_default ( p_line_id NUMBER,
267 p_item_id NUMBER)
268 is
269 SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
270 FROM ic_tran_pnd
271 WHERE line_id = p_line_id
272 -- AND item_id = p_item_id -- REMOVED for bug 3403418
273 AND doc_type = 'OMSO'
274 AND staged_ind = 0
275 AND completed_ind = 0
276 AND delete_mark = 0;
277
278 CURSOR check_default_lot ( p_line_id NUMBER,
279 p_item_id NUMBER)
280 IS
281 SELECT count(*)
282 FROM ic_tran_pnd
283 WHERE line_id = p_line_id
284 -- AND item_id = p_item_id -- REMOVED for bug 3403418
285 AND doc_type = 'OMSO'
286 AND staged_ind = 0
287 AND completed_ind = 0
288 AND delete_mark = 0;
289
290 CURSOR mo_line_txn_c ( p_line_id IN NUMBER,
291 p_location IN VARCHAR2,
292 p_item_id IN NUMBER,
293 p_delivery_detail_id IN NUMBER)
294 IS
295 SELECT tran.trans_id,
296 ABS(tran.trans_qty) trans_qty,
297 ABS(tran.trans_qty2) trans_qty2,
298 tran.qc_grade,
299 tran.location,
300 lots.lot_no,
301 lots.lot_id,
302 lots.sublot_no,
303 loct.INVENTORY_LOCATION_ID locator_id
304 FROM IC_TRAN_PND tran,
305 IC_LOTS_MST lots,
306 IC_LOCT_MST loct
307 WHERE lots.lot_id = tran.lot_id
308 AND lots.item_id = tran.item_id
309 AND lots.delete_mark = 0
310 AND tran.line_id = p_line_id
311 AND ( tran.lot_id > 0
312 OR tran.location <> p_location )
313 -- AND tran.item_id = p_item_id -- REMOVED for bug 3403418
314 AND tran.doc_type = 'OMSO'
315 AND tran.staged_ind = 0
316 AND tran.completed_ind = 0
317 AND tran.delete_mark = 0
318 AND loct.delete_mark(+) = 0
319 AND loct.whse_code (+) = tran.whse_code
320 AND loct.location (+) = tran.location
321 AND tran.line_detail_id = l_delivery_detail_id;
322
323 l_mo_line_txn_rec mo_line_txn_c%ROWTYPE;
324
325 CURSOR get_whse_code IS
326 SELECT whse_code,loct_ctl
327 FROM ic_whse_mst
328 WHERE mtl_organization_id = l_organization_id;
329
330 CURSOR get_delivery IS
331 SELECT delivery_detail_id
332 , source_header_id
333 , source_line_id
334 , requested_quantity
335 , requested_quantity2
336 FROM wsh_delivery_details
337 WHERE move_order_line_id = l_mo_line_rec.line_id
338 AND move_order_line_id IS NOT NULL
339 AND released_status = 'S';
340
341 -- Begin Bug 3609713
342 CURSOR check_onhand_qty(p_item_id IN NUMBER,
343 p_whse_code IN VARCHAR2,
344 p_lot_id IN NUMBER,
345 p_location IN VARCHAR2)
346 IS
347 Select loct_onhand
348 ,nvl(loct_onhand2,0)
349 From ic_loct_inv inv
350 Where inv.item_id = p_item_id
351 AND inv.whse_code = p_whse_code
352 AND inv.lot_id = p_lot_id
353 AND inv.location = p_location;
354
355 CURSOR check_staged(p_item_id IN NUMBER,
356 p_whse_code IN VARCHAR2,
357 p_lot_id IN NUMBER,
358 p_location IN VARCHAR2)
359 IS
360 Select nvl(sum(trans_qty),0)
361 ,nvl(sum(nvl(trans_qty2,0)),0)
362 From ic_tran_pnd pnd
363 Where pnd.completed_ind =0
364 AND pnd.delete_mark = 0
365 AND pnd.staged_ind = 1
366 AND pnd.doc_type= 'OMSO'
367 AND pnd.item_id = p_item_id
368 AND pnd.whse_code = p_whse_code
369 AND pnd.lot_id = p_lot_id
370 AND pnd.location = p_location;
371 -- End Bug 3609713
372
373 CURSOR qty_sum_for_del IS
374 SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
375 FROM ic_tran_pnd
376 WHERE line_id = l_mo_line_rec.txn_source_line_id
377 AND line_detail_id = l_delivery_detail_id
378 AND delete_mark = 0;
379
380 CURSOR check_open_del IS
381 Select count(*)
382 From wsh_delivery_details
383 Where move_order_line_id = l_mo_line_rec.line_id
384 And source_line_id = l_mo_line_rec.txn_source_line_id
385 And released_status = 'S';
386
387 /* bug 2202841, add status ctl, only the shippable can be transacted */
388 CURSOR get_status_ctl(p_item_id NUMBER)
389 is
390 Select status_ctl
391 From ic_item_mst
392 Where item_id=p_item_id;
393
394 CURSOR cur_txn_no_dflt_sts( p_line_id NUMBER,
395 p_item_id NUMBER,
396 p_mo_line_id NUMBER)
397 is
398 SELECT SUM(ABS(nvl(trans_qty,0))),SUM(ABS(nvl(trans_qty2,0)))
399 FROM ic_tran_pnd trans
400 , ic_loct_inv lots
401 , ic_lots_sts sts
402 WHERE trans.line_id = p_line_id
403 AND trans.lot_id > 0
404 -- AND trans.item_id = p_item_id -- REMOVED for bug 3403418
405 AND trans.doc_type = 'OMSO'
406 AND trans.staged_ind = 0
407 AND trans.completed_ind = 0
408 AND trans.delete_mark = 0
409 AND lots.item_id = trans.item_id
410 AND lots.whse_code = trans.whse_code
411 AND lots.lot_id = trans.lot_id
412 AND lots.location = trans.location
413 AND lots.lot_status = sts.lot_status (+)
414 AND NVL(sts.shipping_ind,1) = 1
415 -- AND NVL(sts.order_proc_ind,1)=1 PK Bug 3470116
416 AND NVL(sts.rejected_ind,0) = 0
417 AND trans.line_detail_id in
418 (Select delivery_detail_id
419 From wsh_delivery_details
420 Where move_order_line_id = p_mo_line_id
421 and released_status in ('R','S'))
422 ;
423
424 CURSOR check_lot_sts (p_item_id IN NUMBER,
425 p_whse_code IN VARCHAR2,
426 p_lot_id IN NUMBER,
427 p_location IN VARCHAR2)
428 IS
429 Select order_proc_ind
430 , shipping_ind
431 From ic_lots_sts sts
432 , ic_loct_inv inv
433 Where inv.item_id = p_item_id
434 AND inv.whse_code = p_whse_code
435 AND inv.lot_id = p_lot_id
436 AND inv.location = p_location
437 AND inv.lot_status = sts.lot_status
438 ;
439 CURSOR qty_sum_for_del_sts IS
440 SELECT SUM(ABS(trans_qty)),SUM(ABS(trans_qty2))
441 FROM ic_tran_pnd tran
442 , ic_loct_inv inv -- get status
443 , ic_lots_sts sts -- status check
444 WHERE tran.line_id = l_mo_line_rec.txn_source_line_id
445 AND tran.line_detail_id = l_delivery_detail_id
446 AND tran.delete_mark = 0
447 AND inv.item_id = tran.item_id
448 AND inv.whse_code = tran.whse_code
449 AND inv.lot_id = tran.lot_id
450 AND inv.location = tran.location
451 AND inv.lot_status = sts.lot_status (+)
452 AND NVL(sts.shipping_ind,1) = 1
453 -- AND NVL(sts.order_proc_ind,1)=1 PK Bug 3470116
454 AND NVL(sts.rejected_ind,0) = 0
455 ;
456
457 /* Begin Enhancement 2320442 - Lakshmi Swamy */
458
459 CURSOR New_txn_nonctl_CUR ( p_line_id IN NUMBER,
460 p_item_id IN NUMBER,
461 p_line_detail_id IN NUMBER) IS
462 SELECT trans_id
463 , trans_qty
464 , trans_qty2
465 FROM ic_tran_pnd
466 WHERE line_id = p_line_id
467 -- AND item_id = p_item_id -- REMOVED for bug 3403418
468 AND doc_type = 'OMSO'
469 AND staged_ind = 1
470 AND completed_ind = 0
471 AND delete_mark = 0
472 AND line_detail_id = p_line_detail_id;
473
474 l_trans_id NUMBER;
475 d_trans_id NUMBER;
476 l_validation_level VARCHAR2(4) := FND_API.G_VALID_LEVEL_FULL;
477 l_available_qty NUMBER;
478 l_available_qty2 NUMBER;
479 l_tran_row IC_TRAN_PND%ROWTYPE;
480 l_pick_slip_number NUMBER;
481 OM_G_installed VARCHAR2(30);
482 l_allow_negative_inv NUMBER; /* 2690711 */
483 /* End Enhancement 2320442 - Lakshmi Swamy */
484
485 Cursor check_wsh IS
486 SELECT object_name
487 FROM user_objects
488 WHERE object_name = 'WSH_USA_INV_PVT'
489 AND object_type = 'PACKAGE BODY';
490
491 BEGIN
492 /* Standard Start OF API savepoint */
493 SAVEPOINT PICK_WAVE_CONFIRM;
494 -- Begin Bug 3609713
495 l_allowneginv := NVL(FND_PROFILE.VALUE('IC$ALLOWNEGINV'),0);
496 --End Bug 3609713
497
498 /*DBMS_OUTPUT.PUT_LINE('IN MOVE ORDER LINES'); */
499
500 /* Standard call to check for call compatibility. */
501
502 IF NOT FND_API.Compatible_API_CALL
503 ( l_api_version_number
504 , p_api_version_number
505 , l_api_name
506 , G_PKG_NAME
507 )
508 THEN
509 RAISE FND_API.G_EXC_ERROR;
510 END IF;
511
512 /* Initialize message list if p_int_msg_lst is set TRUE. */
513
514 IF FND_API.to_boolean(p_init_msg_lst)
515 THEN
516 FND_MSG_PUB.Initialize;
517 END IF;
518
519 /* Initialize API return status to sucess */
520 x_return_status := FND_API.G_RET_STS_SUCCESS;
521
522 GMI_Reservation_Util.PrintLn('PICK_WAVE_CONFIRM PVT COUNT=> '||p_mo_line_tbl.COUNT);
523
524 /* Get System Constant For Default Location Value. */
525
526 l_location:= FND_PROFILE.Value_Specific(
527 name => 'IC$DEFAULT_LOCT'
528 , user_id => l_mo_line_rec.last_updated_by
529 );
530
531 FOR I in 1..p_mo_line_TBL.COUNT LOOP
532 l_mo_line_rec := p_mo_line_tbl(I);
533 GMI_RESERVATION_UTIL.PrintLn('Check Missing For Row > '|| I );
534 IF check_required( p_mo_line_rec => l_mo_line_rec) THEN
535 WSH_Util_Core.PrintLn('Check Missing Falied ');
536 FND_MESSAGE.SET_NAME('GMI','GMI_REQUIRED_MISSING');
537 FND_MSG_PUB.ADD;
538 RAISE FND_API.G_EXC_ERROR;
539 END IF;
540
541 -- HW BUG#:2296620 call to check ship_sets
542 l_manual_pick := p_manual_pick;
543
544 gmi_reservation_util.println('Value of p_manual_pick is '||l_manual_pick);
545
546
547 IF ( l_manual_pick = 'N') THEN
548 gmi_reservation_util.println('Value of count is '||p_mo_line_TBL.COUNT);
549
550 gmi_reservation_util.println('Value of ship_Set id is '|| l_mo_line_rec.ship_set_id);
551 gmi_reservation_util.println('Value of l_mo_line_rec.quantity_detailed is '||l_mo_line_rec.quantity_Detailed);
552 IF (l_mo_line_rec.ship_set_id IS NOT NULL ) THEN
553 check_ship_set
554 (
555 p_ship_set_id => l_mo_line_rec.ship_set_id
556 ,p_manual_pick => l_manual_pick
557 ,x_return_status => x_return_status
558 );
559
560 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
561 l_no_error := '0';
562 IF ( x_return_status = WSH_UTIL_CORE.G_RET_STS_WARNING ) THEN
563 RAISE l_warning;
564 ELSE
565 RAISE FND_API.G_EXC_ERROR;
566 END IF;
567
568 ELSE
569 l_no_error :='1';
570 END IF; -- of error checking
571 END IF; -- of ship_Set_id
572
573 ELSE
574 l_no_error := '1';
575 END IF; -- of manual_pick
576
577 -- HW BUG#:2296620
578 IF ( l_no_error = '1' ) THEN
579
580 gmi_reservation_util.println('Going to pick confirm');
581
582
583 /* Need To check The Value Of detailed_qty */
584 /* And quantity_delivered. */
585 /* If they are Equal or Detailed is 0 Nothing needs to be done */
586 /* Should I exit Loop Or Raise an exception */
587
588 /*IF ( l_mo_line_rec.quantity_delivered = l_mo_line_rec.quantity_detailed )
589 OR ( l_mo_line_rec.quantity_detailed = 0) THEN
590 FND_MESSAGE.SET_NAME('INV', 'INV_PICK_QTY_ERROR');
591 FND_MSG_PUB.ADD;
592 raise FND_API.G_EXC_ERROR;
593 END IF; */
594
595 /* Get Process Item details */
596 GMI_RESERVATION_UTIL.Get_OPM_item_from_Apps
597 (
598 p_organization_id => l_mo_line_rec.organization_id
599 , p_inventory_item_id => l_mo_line_rec.inventory_item_id
600 , x_ic_item_mst_rec => l_ic_item_mst_rec
601 , x_return_status => x_return_status
602 , x_msg_count => x_msg_count
603 , x_msg_data => x_msg_data);
604
605 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
606 THEN
607 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
608 FND_MESSAGE.SET_TOKEN('BY_PROC','GMI_Reservation_Util.Get_OPM_item_from_Apps');
609 FND_MESSAGE.SET_TOKEN('WHERE',G_PKG_NAME||'.'||l_api_name);
610 RAISE FND_API.G_EXC_ERROR;
611 END IF;
612
613 GMI_Reservation_Util.PrintLn('Item Found. Now Qty treatment');
614 /* Now Check Item Characteristics */
615 /* HAM Does this logic cover all cases */
616 /* What about loct_ctl=1 and lot_ctl=0 or visa versa. */
617 /* Set quantity Fields to 0; */
618 l_trans_qty := 0;
619 l_trans_qty2 := 0;
620 l_organization_id := l_mo_line_rec.organization_id;
621 Open get_whse_code;
622 Fetch get_whse_code into l_whse_code, l_whse_ctl;
623 Close get_whse_code;
624
625 GMI_Reservation_Util.PrintLn('Item : lot_ctl='||l_ic_item_mst_rec.lot_ctl||', loct_ctl='||l_ic_item_mst_rec.loct_ctl);
626 IF ( l_ic_item_mst_rec.lot_ctl <> 0 ) OR ( l_ic_item_mst_rec.loct_ctl * l_whse_ctl<> 0)
627 THEN
628 l_default_transaction :=0; /* Do not Transact default Lot. */
629 /* get status ctl */
630 Open get_status_ctl(p_item_id => l_ic_item_mst_rec.item_id);
631 Fetch get_status_ctl INTO l_status_ctl;
632 Close get_status_ctl;
633 /* Get TXN Quantities */
634 OPEN cur_txn_no_default
635 ( p_line_id => l_mo_line_rec.txn_source_line_id,
636 p_location => l_location,
637 p_item_id => l_ic_item_mst_rec.item_id
638 );
639
640 FETCH cur_txn_no_default INTO l_trans_qty, l_trans_qty2;
641 IF cur_txn_no_default%NOTFOUND THEN
642 CLOSE cur_txn_no_default;
643 GMI_Reservation_Util.PrintLn('txn_no_default : NOTFOUND ');
644 FND_MESSAGE.SET_NAME('GMI','GMI_REQUIRED_MISSING');
645 RAISE FND_API.G_EXC_ERROR;
646 END IF;
647 CLOSE cur_txn_no_default;
648 l_trans_qty_sts := l_trans_qty;
649 l_trans_qty2_sts := l_trans_qty2;
650 IF nvl(l_status_ctl, 0) <> 0 THEN -- status ctl
651 GMI_Reservation_Util.PrintLn('status controled');
652 OPEN cur_txn_no_dflt_sts
653 ( p_line_id => l_mo_line_rec.txn_source_line_id,
654 p_item_id => l_ic_item_mst_rec.item_id,
655 p_mo_line_id => l_mo_line_rec.line_id
656 );
657
658 FETCH cur_txn_no_dflt_sts INTO l_trans_qty, l_trans_qty2; -- this portion would be transacted
659 IF cur_txn_no_dflt_sts%NOTFOUND THEN
660 CLOSE cur_txn_no_dflt_sts;
661 GMI_Reservation_Util.PrintLn('txn_no_default : NOTFOUND ');
662 FND_MESSAGE.SET_NAME('GMI','GMI_REQUIRED_MISSING');
663 END IF;
664 CLOSE cur_txn_no_dflt_sts;
665 GMI_Reservation_Util.PrintLn('trans_qty '||l_trans_qty ||' trans_qty2 '||l_trans_qty2 );
666 END IF;
667 ELSE /* not lot controled or location controled */
668 l_default_transaction :=1; /* Transact default Lot. */
669 /* Get TXN Quantities */
670 OPEN check_default_lot
671 ( p_line_id => l_mo_line_rec.txn_source_line_id,
672 p_item_id => l_ic_item_mst_rec.item_id
673 );
674 FETCH check_default_lot into l_msg_count;
675 CLOSE check_default_lot;
676 IF l_msg_count = 0 THEN
677 GMI_Reservation_Util.PrintLn('txn_with_default : NOTFOUND ');
678 /* if default is not found at this stage, user should still able to transact */
679 /* the so line with the full qty*/
680 /* this is also true for non inventory controled items*/
681 /* the move order qtys are already in item_um1 and item_um2*/
682 l_trans_qty := l_mo_line_rec.quantity;
683 l_trans_qty2 := l_mo_line_rec.secondary_quantity;
684 /*RAISE FND_API.G_EXC_ERROR; */
685 /* create a default lot to avoid troubles in shipping */
686 GMI_RESERVATION_UTIL.Create_dflt_lot_from_scratch
687 (
688 p_whse_code => l_whse_code
689 , p_line_id => l_mo_line_rec.txn_source_line_id
690 , p_item_id => l_ic_item_mst_rec.item_id
691 , p_qty1 => l_mo_line_rec.quantity
692 , p_qty2 => l_mo_line_rec.secondary_quantity
693 , x_return_status => l_return_status
694 , x_msg_count => x_msg_count
695 , x_msg_data => x_msg_data
696 );
697 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
698 THEN
699 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
700 FND_MESSAGE.SET_TOKEN('BY_PROC','GMI_Reservation_Util.create_dflt_lot_from_scratch');
701 FND_MESSAGE.SET_TOKEN('WHERE',G_PKG_NAME||'.'||l_api_name);
702 RAISE FND_API.G_EXC_ERROR;
703 END IF;
704 ELSE
705 OPEN cur_txn_with_default
706 ( p_line_id => l_mo_line_rec.txn_source_line_id,
707 p_item_id => l_ic_item_mst_rec.item_id
708 );
709 FETCH cur_txn_with_default INTO l_trans_qty, l_trans_qty2;
710 CLOSE cur_txn_with_default;
711 END IF;
712 END IF;
713
714 /* Now Set delivered Quantities */
715 GMI_Reservation_Util.PrintLn('Now Set the delivered Quantities');
716 l_mo_line_rec.quantity_delivered := NVL(l_mo_line_rec.quantity_delivered,0)
717 + NVL(l_trans_qty,0);
718
719 /* Ham May Need To Add Logic To Save As NULL not 0 */
720 /* NOTE NULL + anything = NULL; */
721 l_mo_line_rec.secondary_quantity_delivered := NVL(l_mo_line_rec.secondary_quantity_delivered, 0)
722 + NVL(l_trans_qty2,0);
723
724 -- Bug 1777799, odaboval Added this test for populating detailed_qty:
725 IF (NVL(l_mo_line_rec.quantity_detailed, 0) = 0)
726 THEN
727 l_mo_line_rec.quantity_detailed := l_mo_line_rec.quantity_delivered;
728 l_mo_line_rec.secondary_quantity_detailed := l_mo_line_rec.secondary_quantity_delivered;
729 END IF;
730
731 /* If default Lot is really the actual lot */
732 /* Then unless you pick confirm everything */
733 /* Then throw an ERROR: */
734
735 GMI_Reservation_Util.PrintLn('default_transaction='||l_default_transaction);
736 GMI_Reservation_Util.PrintLn('qty_delivered='||l_mo_line_rec.quantity_delivered||', qty_delivered2='||l_mo_line_rec.secondary_quantity_delivered);
737 GMI_Reservation_Util.PrintLn('qty='||l_mo_line_rec.quantity);
738
739 /* Set Status Of Mo_line_rec To Closed if Fully delivered */
740 /* requsted qty . */
741
742 IF l_mo_line_rec.quantity_delivered >= l_mo_line_rec.quantity THEN
743 /* check over shipment tolerance */
744 -- for non ctl items, no allocation is made but the default trans
745 -- qty is updated by the system, can not be over allocating
746 IF (l_mo_line_rec.quantity_delivered > l_mo_line_rec.quantity )
747 AND (l_default_transaction <> 1) THEN
748 GMI_Pick_Wave_Confirm_PVT.Check_Shipping_Tolerances
749 ( x_return_status => x_return_status,
750 x_msg_count => x_msg_count,
751 x_msg_data => x_msg_data,
752 x_allowed => l_allowed,
753 x_max_quantity => l_max_qty,
754 x_max_quantity2 => l_max_qty2,
755 p_line_id => l_mo_line_rec.line_id,
756 p_quantity => l_trans_qty,
757 p_quantity2 => l_trans_qty2
758 );
759 IF x_return_status <> 'S' THEN
760 fnd_message.set_name('INV', 'INV_CHECK_TOLERANCE_ERROR');
761 RAISE FND_API.G_EXC_ERROR;
762 ELSE
763 IF l_allowed = 'N' THEN
764 GMI_Reservation_Util.PrintLn('ATTENTION :');
765 GMI_Reservation_Util.PrintLn('MOVE ORDER line : line_id ='||l_mo_line_rec.line_id ||
766 ' can not be transacted because picked qty exceeds over shippment tolerance. '||
767 ' The allocated quantity is '|| l_trans_qty ||' but the max allowed quantity is'||
768 l_max_qty || ' Please reduce allocation quantity ');
769
770 /* NC Bug #2557029 */
771 FND_MESSAGE.SET_NAME('GMI','GMI_API_OVER_SHIP_TOLERANCE');
772 FND_MESSAGE.SET_TOKEN('QUANTITY1',to_char(l_trans_qty));
773 FND_MESSAGE.SET_TOKEN('QUANTITY2',to_char(l_max_qty));
774 FND_MESSAGE.SET_TOKEN('LINE_ID',l_mo_line_rec.line_id);
775 GMI_PICK_CONFIRM_PUB.PrintMsg('ERROR: MOVE ORDER line : line_id ='||l_mo_line_rec.line_id ||
776 ' can not be transacted because picked qty exceeds over shippment tolerance. '||
777 'The allocated quantity is '|| l_trans_qty ||' but the max allowed quantity is'||
778 l_max_qty || ' Please reduce allocation quantity ');
779 FND_MSG_PUB.Add;
780 l_tolerance_flag := 1;
781 /* NC End of Bug #2557029 */
782
783 goto next_line;
784 END IF ;
785 END IF;
786 END IF;
787 l_mo_line_rec.line_status :=5;
788 END IF;
789
790 /* Update Move Order Line record */
791 GMI_Reservation_Util.PrintLn('Now, update the MO row');
792 GMI_MOVE_ORDER_LINE_UTIL.Update_ROW(p_mo_line_rec => l_mo_line_rec);
793
794
795 /* Bug 2499153 */
796 /* check to see if WSH G is installed, the file WSHUSAIB.pls is newly introduced in G
797 so checking if the object exists or not would do*/
798
799 OPEN check_wsh;
800 FETCH check_wsh INTO OM_G_installed;
801 IF (check_wsh%NOTFOUND) THEN
802 OM_G_installed := NULL;
803 GMI_Reservation_Util.PrintLn('OM_G_installed not found');
804 END IF;
805 GMI_Reservation_Util.PrintLn('OM_G_installed ' || OM_G_installed);
806 CLOSE check_wsh;
807
808 /* Set Up status Flag */
809 if (l_mo_line_rec.quantity_delivered < l_mo_line_rec.quantity )
810 THEN
811 if l_default_transaction = 0 THEN
812 l_action_flag := 'M';
813 else
814 l_action_flag := 'S'; /* Should Never Happen */
815 end if;
816 ELSE
817 if l_default_transaction = 0 THEN
818 l_action_flag := 'M';
819 else
820 if (l_ic_item_mst_rec.noninv_ind = 1) then
821 l_action_flag := 'U';
822 else
823 if (OM_G_installed is NOT NULL) THEN -- Condition introduced for bug2499153
824 l_action_flag := 'M'; /* Enhancement 2320442 - Changed from U to M */
825 else
826 l_action_flag := 'U';
827 end if;
828 end if;
829 end if;
830 END IF;
831
832 GMI_Reservation_Util.PrintLn('Action Flag is '||l_action_flag);
833 /* Save The SUM Of the transaction QTY's This May Be Used */
834 /* Further In The Code ...... */
835
836 l_sum_trans_qty := l_trans_qty;
837 l_sum_trans_qty2 := l_trans_qty2;
838
839 /* Now Let Build shipping API record */
840 GMI_Reservation_Util.PrintLn('Now, Let us Build shipping API record');
841 /* Maybe i should set context switch to get correct Org. */
842
843 -- Bug 1805216, added NOT NULL in the cursor.
844 /*SELECT a.delivery_detail_id, a.oe_header_id, a.oe_line_id
845 , b.order_quantity_uom
846 INTO l_delivery_detail_id
847 , l_source_header_id
848 , l_source_line_id
849 , l_order_quantity_uom
850 FROM oe_order_lines_all b
851 , wsh_inv_delivery_details_v a
852 WHERE a.move_order_line_id = l_mo_line_rec.line_id
853 AND a.move_order_line_id IS NOT NULL
854 AND a.oe_line_id = b.line_id
855 AND a.released_status = 'S';*/
856 FOR delivery IN get_delivery LOOP /* there could be more than 1 del for the same mo line*/
857 l_delivery_detail_id := delivery.delivery_detail_id;
858 l_source_header_id := delivery.source_header_id;
859 l_source_line_id := delivery.source_line_id;
860 l_requested_qty := delivery.requested_quantity;
861 l_requested_qty2 := delivery.requested_quantity2;
862
863 /* if p_delivery_detail_id is passed, meaning only transact this delivery, Loop would stop*/
864 IF p_delivery_detail_id is not null AND p_delivery_detail_id <> l_delivery_detail_id THEN
865 /* go to next record */
866 goto del_loop_end;
867 END IF;
868
869 --
870 -- Bug 3776538 - Transaction quantities should be truncated to 5 decimals upfront
871 --
872 l_ALLOW_OPM_TRUNCATE_TXN := nvl(fnd_profile.value ('ALLOW_OPM_TRUNCATE_TXN'),'N');
873 GMI_Reservation_Util.PrintLn('(GMIVPWCB)Profile: ALLOW_OPM_TRUNCATE_TXN '||l_ALLOW_OPM_TRUNCATE_TXN);
874 IF (l_ALLOW_OPM_TRUNCATE_TXN = 'Y') THEN
875 GMI_Reservation_Util.PrintLn('Truncating the transaction quantities before TRANSACT');
876 l_return_status := FND_API.G_RET_STS_SUCCESS;
877 truncate_trans_qty (p_line_id => l_mo_line_rec.txn_source_line_id,
878 p_delivery_detail_id => l_delivery_detail_id,
879 p_default_location => l_location,
880 is_lot_loct_ctl => TRUE,
881 x_return_status => l_return_status);
882 --
883 -- Fail the pick confirm process if exception occured during truncation of transactions
884 --
885 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
886 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
887 FND_MESSAGE.SET_TOKEN('BY_PROC','GMI_Reservation_Util.truncate_trans_qty');
888 FND_MESSAGE.SET_TOKEN('WHERE',G_PKG_NAME||'.'||l_api_name);
889 RAISE FND_API.G_EXC_ERROR;
890 END IF;
891 END IF;
892 -- End 3776538
893
894 IF l_default_transaction = 0 THEN /* lot or loct controled */
895 /* get total trans_qtys for this del */
896 Open qty_sum_for_del;
897 Fetch qty_sum_for_del INTO l_sum_trans_qty, l_sum_trans_qty2;
898 Close qty_sum_for_del ;
899 l_trans_qty := l_sum_trans_qty;
900 l_trans_qty2 := l_sum_trans_qty2;
901 l_trans_qty_sts := l_trans_qty;
902 l_trans_qty2_sts := l_trans_qty2;
903 gmi_reservation_util.println('qty_sum_for_del l_trans_qty '||l_trans_qty);
904 gmi_reservation_util.println('qty_sum_for_del l_trans_qty2 '||l_trans_qty2);
905
906 l_backorder_del := 0;
907 IF nvl(l_status_ctl, 0) <> 0 THEN -- status ctl
908 Open qty_sum_for_del_sts;
909 Fetch qty_sum_for_del_sts INTO l_sum_trans_qty, l_sum_trans_qty2;
910 Close qty_sum_for_del_sts ;
911 l_trans_qty := nvl(l_sum_trans_qty,0);
912 l_trans_qty2 := nvl(l_sum_trans_qty2,0);
913 gmi_reservation_util.println('qty_sum_for_del_sts l_trans_qty '||l_trans_qty);
914 gmi_reservation_util.println('qty_sum_for_del_sts l_trans_qty2 '||l_trans_qty2);
915 IF (l_trans_qty_sts > l_trans_qty ) THEN -- lots allocated, but may not be transactable
916 gmi_reservation_util.println('lots allocated, but may not be transactable ');
917 l_backorder_del := 1;
918 END IF;
919 END IF;
920 END IF;
921
922 l_shipping_attr(1).source_header_id := l_source_header_id;
923 l_shipping_attr(1).source_line_id := l_source_line_id;
924 l_shipping_attr(1).subinventory := l_mo_line_rec.to_subinventory_code;
925 l_shipping_attr(1).released_status := 'Y';
926 l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
927
928 GMI_Reservation_Util.PrintLn('Shipping Values : source_header_id='||l_shipping_attr(1).source_header_id);
929 GMI_Reservation_Util.PrintLn('Shipping Values : source_line_id='||l_shipping_attr(1).source_line_id);
930 GMI_Reservation_Util.PrintLn('Shipping Values : delivery_detail_id='||l_shipping_attr(1).delivery_detail_id);
931
932 l_shipping_attr(1).preferred_grade := l_mo_line_rec.qc_grade;
933 l_shipping_attr(1).lot_number := NULL;
934 -- l_shipping_attr(1).sublot_number := NULL; /*Commented for R12. P1 Bug#4561095 */
935 l_shipping_attr(1).locator_id := NULL;
936 l_shipping_attr(1).ship_from_org_id := l_mo_line_rec.organization_id;
937 l_shipping_attr(1).action_flag := l_action_flag;
938
939 IF l_default_transaction = 0 THEN /* lot or loct controled */
940 GMI_Reservation_Util.PrintLn('before getting transaction, line_id='||l_mo_line_rec.txn_source_line_id
941 ||', location='||l_location||', item_id='||l_ic_item_mst_rec.item_id);
942 OPEN mo_line_txn_c ( p_line_id => l_mo_line_rec.txn_source_line_id,
943 p_location => l_location,
944 p_item_id => l_ic_item_mst_rec.item_id,
945 p_delivery_detail_id =>l_delivery_detail_id
946 );
947 GMI_Reservation_Util.PrintLn('After Call to mo_line_txn_c');
948
949
950 /* Bug2621228 - Following assignments made */
951 l_sum_trans_qty := GREATEST (l_sum_trans_qty, l_requested_qty);
952 IF (l_requested_qty > l_sum_trans_qty) THEN
953 l_sum_trans_qty2 := l_requested_qty2;
954 END IF;
955
956 LOOP
957 FETCH mo_line_txn_c into l_mo_line_txn_rec;
958 EXIT when mo_line_txn_c%NOTFOUND;
959
960 GMI_Reservation_Util.PrintLn('a transaction is found : trans_id='||l_mo_line_txn_rec.trans_id);
961
962 GMI_Reservation_Util.PrintLn('Trans id = '||l_mo_line_txn_rec.trans_id);
963 GMI_Reservation_Util.PrintLn('lot_no = '||l_mo_line_txn_rec.lot_no);
964 GMI_Reservation_Util.PrintLn('lot_id = '||l_mo_line_txn_rec.lot_id);
965 GMI_Reservation_Util.PrintLn('sublot_no= '||l_mo_line_txn_rec.sublot_no);
966 GMI_Reservation_Util.PrintLn('Grade = '||l_mo_line_txn_rec.qc_grade);
967 GMI_Reservation_Util.PrintLn('trans_qty= '||l_mo_line_txn_rec.trans_qty);
968 GMI_Reservation_Util.PrintLn('qty2 = '||l_mo_line_txn_rec.trans_qty2);
969 GMI_Reservation_Util.PrintLn('locator = '||l_mo_line_txn_rec.locator_id);
970 /* check the status to see it is transactable */
971 GMI_Reservation_Util.PrintLn('check status = '||l_mo_line_txn_rec.locator_id);
972 /* for non status ctl item, no check */
973 l_orderable_ind := 1;
974 l_shipable_ind := 1;
975 IF nvl(l_status_ctl,0) <> 0 THEN
976 Open check_lot_sts(
977 p_item_id => l_ic_item_mst_rec.item_id,
978 p_whse_code => l_whse_code,
979 p_lot_id => l_mo_line_txn_rec.lot_id,
980 p_location => l_mo_line_txn_rec.location
981 );
982 Fetch check_lot_sts INTO l_orderable_ind, l_shipable_ind;
983 Close check_lot_sts;
984 END IF;
985
986 GMI_Reservation_Util.PrintLn('orderable status ind : '||l_orderable_ind);
987 GMI_Reservation_Util.PrintLn('shipable status ind : '||l_shipable_ind);
988
989 -- Begin Bug 3609713
990 GMI_Reservation_Util.PrintLn(' Lot_ctl '||l_ic_item_mst_rec.lot_ctl);
991 GMI_Reservation_Util.PrintLn(' Allow negative Inventory : '||l_allowneginv);
992
993 -- Bug 3859774
994 -- If inventory is not allowed to go -ve and the lots are shippable!
995 --
996 IF (l_allowneginv <> 1 AND NVL(l_shipable_ind, 0) <> 0) THEN
997 OPEN check_onhand_qty(
998 p_item_id => l_ic_item_mst_rec.item_id,
999 p_whse_code => l_whse_code,
1000 p_lot_id => l_mo_line_txn_rec.lot_id,
1001 p_location => l_mo_line_txn_rec.location
1002 );
1003 FETCH check_onhand_qty INTO l_onhand1, l_onhand2;
1004 IF check_onhand_qty%NOTFOUND THEN
1005 l_onhand1 := 0;
1006 l_onhand2 := 0;
1007 END IF;
1008 CLOSE check_onhand_qty;
1009 GMI_Reservation_Util.PrintLn('Onhand for lot : '||l_onhand1);
1010 GMI_Reservation_Util.PrintLn('Onhand2 for lot : '||l_onhand2);
1011
1012 OPEN check_staged(
1013 p_item_id => l_ic_item_mst_rec.item_id,
1014 p_whse_code => l_whse_code,
1015 p_lot_id => l_mo_line_txn_rec.lot_id,
1016 p_location => l_mo_line_txn_rec.location
1017 );
1018 FETCH check_staged INTO l_alloc1, l_alloc2;
1019 CLOSE check_staged;
1020 GMI_Reservation_Util.PrintLn('allocated for lot : '||l_alloc1);
1021 GMI_Reservation_Util.PrintLn('allocated2 for lot : '||l_alloc2);
1022 -- Bug 3859774
1023 -- ONLY primary allocations should be compared with primary available.
1024 --
1025 IF ((l_onhand1 + l_alloc1) < ABS(l_mo_line_txn_rec.trans_qty)) THEN
1026 GMI_Reservation_Util.PrintLn(' Inventory checked : Going negative !');
1027 l_inv_negative := 1;
1028 ELSE
1029 GMI_Reservation_Util.PrintLn(' Inventory checked : Not going negative ');
1030 l_inv_negative := 0;
1031 END IF;
1032 ELSE -- of (l_allowneginv <> 1 AND nvl(l_shipable_ind, 0) <> 0)
1033 GMI_Reservation_Util.PrintLn(' Item Not lot controlled or Negative inventory Allowed OR ... ');
1034 GMI_Reservation_Util.PrintLn(' ... Negative Inventory not checked since lot is not shipable ');
1035 l_inv_negative := 0;
1036 END IF;
1037
1038 IF (( nvl(l_shipable_ind, 0)) <> 0
1039 AND (l_inv_negative = 0)) THEN -- transact if both orderable and shipable PK Bug 3470116 use only shipping
1040
1041 -- End Bug 3609713
1042
1043 /* NC Added trans_id Bug#1675561 */
1044 l_shipping_attr(1).trans_id := l_mo_line_txn_rec.trans_id;
1045 l_shipping_attr(1).lot_number := l_mo_line_txn_rec.lot_no;
1046 --l_shipping_attr(1).sublot_number := l_mo_line_txn_rec.sublot_no; /* R12 P1 Bug#4561095 */
1047 l_shipping_attr(1).preferred_grade := l_mo_line_txn_rec.qc_grade;
1048 l_shipping_attr(1).locator_id := l_mo_line_txn_rec.locator_id;
1049 l_return_status := '';
1050 l_shipping_attr(1).ordered_quantity := l_mo_line_txn_rec.trans_qty;
1051 l_shipping_attr(1).ordered_quantity2 := l_mo_line_txn_rec.trans_qty2;
1052
1053 GMI_Reservation_Util.PrintLn('total trans qty = '||l_trans_qty);
1054 GMI_Reservation_Util.PrintLn('total requested qty = '||l_requested_qty);
1055
1056
1057 /* overpicking changes */
1058 /* Bug 2621228 - Logic below is changed to populate picked/pending quantity in post G scenario */
1059
1060 IF (l_trans_qty > l_requested_qty AND OM_G_installed IS NULL ) OR -- Pre G with over picking
1061 (OM_G_installed IS NOT NULL) THEN -- Post G code -- always populate the picked and pending qtys
1062 l_shipping_attr(1).picked_quantity := l_mo_line_txn_rec.trans_qty;
1063 l_shipping_attr(1).picked_quantity2 := l_mo_line_txn_rec.trans_qty2;
1064
1065 l_shipping_attr(1).pending_quantity := nvl(l_sum_trans_qty,0) - nvl(l_mo_line_txn_rec.trans_qty,0);
1066 l_shipping_attr(1).pending_quantity2 := nvl(l_sum_trans_qty2,0) - nvl(l_mo_line_txn_rec.trans_qty2,0);
1067
1068 l_sum_trans_qty := l_sum_trans_qty - l_shipping_attr(1).picked_quantity;
1069 l_sum_trans_qty2 := l_sum_trans_qty2 - l_shipping_attr(1).picked_quantity2;
1070
1071 IF l_shipping_attr(1).pending_quantity < 0 THEN
1072 l_shipping_attr(1).pending_quantity := 0;
1073 l_shipping_attr(1).pending_quantity2 := 0;
1074 End IF;
1075 END IF;
1076
1077 /* End bug 2621228 */
1078 /* end of over pick */
1079
1080 GMI_Reservation_Util.PrintLn('Before Calling the Update_Shipping_Attributes trans_id='||l_mo_line_txn_rec.trans_id);
1081
1082 -- Bug 3776538
1083 dump_shp_attrb_data(p_shipping_attr => l_shipping_attr);
1084
1085 WSH_INTERFACE.Update_Shipping_Attributes
1086 (p_source_code => 'INV',
1087 p_changed_attributes => l_shipping_attr,
1088 x_return_status => l_return_status
1089 );
1090 GMI_Reservation_Util.PrintLn('Return Status from [1] Update_Shipping_Attributes Call '||l_return_status);
1091
1092 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1093 THEN
1094 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
1095 FND_MESSAGE.SET_TOKEN('BY_PROC','GMI_Reservation_Util.call_to_WSH_interface');
1096 FND_MESSAGE.SET_TOKEN('WHERE',G_PKG_NAME||'.'||l_api_name);
1097 RAISE FND_API.G_EXC_ERROR;
1098 END IF;
1099
1100 /* Now Update staged_ind in transaction record from 0 to 1 */
1101 /* HAM Will Have to be More Selective */
1102 /* On Update i.e lot_id, qc_grade, locator_id, */
1103 /* NC added delivery_detail_id and staged_ind in the where clause */
1104 /* -- BUG#1675561*/
1105
1106 UPDATE ic_tran_pnd -- NOT NEEDED
1107 SET staged_ind =1
1108 --line_detail_id = l_shipping_attr(1).delivery_detail_id
1109 WHERE trans_id = l_mo_line_txn_rec.trans_id AND
1110 staged_ind <> 1 and delete_mark <> 1;
1111
1112
1113 /* Setting the pick slip number - Bug2455422 */
1114
1115 GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip (l_mo_line_rec.organization_id , l_mo_line_rec.line_id,
1116 x_return_status, x_msg_count, x_msg_data, l_pick_slip_number);
1117 GMI_reservation_Util.PrintLn('pick slip number is '|| l_pick_slip_number);
1118
1119 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1120 THEN
1121 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_lots: Error returned by GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip.', 'pick_lots.log');
1122 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
1123 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip');
1124 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
1125 FND_MSG_PUB.Add;
1126 RAISE FND_API.G_EXC_ERROR;
1127 END IF;
1128
1129 UPDATE ic_tran_pnd
1130 SET pick_slip_number = l_pick_slip_number
1131 WHERE trans_id = l_mo_line_txn_rec.trans_id;
1132
1133 /* End of Setting the pick slip number - Bug2455422 */
1134
1135
1136 GMI_Reservation_Util.PrintLn('Before Loop 1');
1137 -- Begin Bug 3609713
1138 ELSIF (nvl(l_shipable_ind, 0) = 0) THEN
1139 GMI_Reservation_Util.PrintLn('LOTs not transactable for Status ');
1140 l_mo_line_rec.line_status :=7;
1141 GMI_Reservation_Util.PrintLn('Updating mo line status to OPEN- lot status');
1142 GMI_MOVE_ORDER_LINE_UTIL.Update_ROW(p_mo_line_rec => l_mo_line_rec);
1143 ELSE
1144 GMI_Reservation_Util.PrintLn('LOTs not transactable for Negative Inventory ');
1145 l_mo_line_rec.line_status :=7;
1146 l_mo_line_rec.quantity_delivered := NVL(l_mo_line_rec.quantity_delivered,0)
1147 - l_mo_line_txn_rec.trans_qty;
1148 l_mo_line_rec.secondary_quantity_delivered := NVL(l_mo_line_rec.secondary_quantity_delivered, 0)
1149 - NVL(l_mo_line_txn_rec.trans_qty2,0);
1150
1151 GMI_Reservation_Util.PrintLn('Updating mo line status to OPEN- Negative Inventory');
1152 GMI_MOVE_ORDER_LINE_UTIL.Update_ROW(p_mo_line_rec => l_mo_line_rec);
1153 -- End Bug 3609713
1154 END IF;
1155 END LOOP;
1156 CLOSE mo_line_txn_c; /*2423869*/
1157
1158 /* HW BUG#:1941429OPM cross docking -- find out backorder qtys */
1159 --IF (l_mo_line_rec.quantity_delivered < l_mo_line_rec.quantity ) THEN
1160 /* if picked less, back order */
1161 gmi_reservation_util.println('l_trans_qty '||l_trans_qty);
1162 gmi_reservation_util.println('l_requested_qty '||l_requested_qty);
1163
1164 -- Bug 3776538
1165 gmi_reservation_util.println('Remaining qty '|| (l_requested_qty - l_trans_qty));
1166 IF (l_trans_qty < l_requested_qty OR l_backorder_del = 1 ) THEN
1167 gmi_reservation_util.println('Qtys are not same. so need to mark line as a backorder');
1168 gmi_reservation_util.println('Value of l_mo_line_rec.quantity_delivered is '||l_mo_line_rec.quantity_delivered);
1169 gmi_reservation_util.println('Value of l_mo_line_rec.quantity is '||l_mo_line_rec.quantity);
1170 gmi_reservation_util.println('Value of l_mo_line_rec.status is '||l_mo_line_rec.line_status);
1171 /* find out the remaining delivery */
1172 /* Bug2621228 - l_sum_trans_qty replaced by l_trans_qty */
1173 l_backorder_qty := l_requested_qty - l_trans_qty;
1174 l_backorder_qty2 := l_requested_qty2 - l_trans_qty2;
1175 GMI_Reservation_Util.PrintLn('backordering in pickconfirm ');
1176 GMI_Reservation_Util.PrintLn('backorder qty = '|| l_backorder_qty);
1177 GMI_Reservation_Util.PrintLn('backorder qty2 = '|| l_backorder_qty2);
1178 l_shipping_attr(1).preferred_grade := NULL;
1179 l_shipping_attr(1).lot_number := NULL;
1180 --l_shipping_attr(1).sublot_number := NULL; /* R12 P1 Bug#4561095 */
1181 l_shipping_attr(1).locator_id := NULL;
1182 l_shipping_attr(1).subinventory := NULL;
1183 l_shipping_attr(1).action_flag := 'B';
1184 l_shipping_attr(1).cycle_count_quantity := l_backorder_qty;
1185 l_shipping_attr(1).cycle_count_quantity2 := l_backorder_qty2;
1186 l_shipping_attr(1).released_status := '';
1187 l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
1188 GMI_Reservation_Util.PrintLn('backordering Update_Shipping_Attributes delivery_detail_id= '
1189 ||l_delivery_detail_id);
1190
1191 -- Bug 3776538
1192 dump_shp_attrb_data(p_shipping_attr => l_shipping_attr);
1193
1194 WSH_INTERFACE.Update_Shipping_Attributes
1195 (p_source_code => 'INV',
1196 p_changed_attributes => l_shipping_attr,
1197 x_return_status => l_return_status
1198 );
1199 GMI_Reservation_Util.PrintLn('Return Status from [2] Update_Shipping_Attributes Call '||l_return_status);
1200
1201 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1202 THEN
1203 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
1204 FND_MESSAGE.SET_TOKEN('BY_PROC','GMI_Reservation_Util.call_to_WSH_interface');
1205 FND_MESSAGE.SET_TOKEN('WHERE',G_PKG_NAME||'.'||l_api_name);
1206 RAISE FND_API.G_EXC_ERROR;
1207 END IF;
1208 END IF;
1209 /* HW end of bug #:1941429 */
1210 ELSE /* non lot or loct controled */
1211
1212 IF (OM_G_installed is NULL) THEN
1213 GMI_Reservation_Util.PrintLn('Working with Default lot - Pre G scenario');
1214 l_return_status := '';
1215
1216 -- Bug 3776538
1217 dump_shp_attrb_data(p_shipping_attr => l_shipping_attr);
1218
1219 WSH_INTERFACE.Update_Shipping_Attributes
1220 (p_source_code => 'INV',
1221 p_changed_attributes => l_shipping_attr,
1222 x_return_status => l_return_status
1223 );
1224 GMI_Reservation_Util.PrintLn('Return Status from [3] Update_Shipping_Attributes Call '||l_return_status);
1225
1226 ELSE
1227
1228 GMI_Reservation_Util.PrintLn('Working with Default lot - Post G scenario');
1229 /* Just Working with default lot */
1230 /* Begin Enhancement 2320442 - Lakshmi Swamy */
1231
1232 /* Bug 2462993 */
1233
1234 -- HAW 3387829 Commented some unnecessary calls and
1235 -- some code is now shared between non-inventory amd
1236 -- non-lot control items
1237
1238 /* IF (l_ic_item_mst_rec.noninv_ind = 1) THEN
1239 GMI_Reservation_Util.PrintLn('Just Working with noninventory item');
1240 GMI_Reservation_Util.PrintLn('Delivery detail id '||l_shipping_attr(1).delivery_detail_id);
1241 l_return_status := '';
1242 */
1243
1244 /* HAW 3387829 */
1245 -- Since Non-Inv and Non-Lot are executing the same procedure:
1246 --- GMI_PICK_WAVE_CONFIRM_PVT.BALANCE_NONCTL_INV_TRAN, let's call it once for
1247 -- both of them and call New_txn_nonctl_CUR as well.
1248 -- Earlier there was no call to New_txn_nonctl_CUR for non-inventory items
1249 -- and trans_id was never popualated which caused the issue reported in
1250 -- bug 3387829
1251
1252 /* Bug 2901317 - Treating non-inventory as non-controlled item */
1253 gmi_reservation_util.println('Calling GMI_PICK_WAVE_CONFIRM_PVT.BALANCE_NONCTL_INV_TRAN');
1254 GMI_PICK_WAVE_CONFIRM_PVT.BALANCE_NONCTL_INV_TRAN
1255 (
1256 p_mo_line_rec => l_mo_line_rec ,
1257 p_commit => p_commit,
1258 p_item_id => l_ic_item_mst_rec.item_id,
1259 p_whse_code => l_whse_code,
1260 p_requested_qty => l_requested_qty,
1261 p_requested_qty2 => l_requested_qty2,
1262 p_delivery_detail_id => l_delivery_detail_id,
1263 x_available_qty => l_available_qty,
1264 x_available_qty2 => l_available_qty2,
1265 x_tran_row => l_tran_row,
1266 x_return_status => x_return_status,
1267 x_msg_count => x_msg_count,
1268 x_msg_data => x_msg_data
1269 );
1270 /* End bug 2901317 */
1271
1272 OPEN New_txn_nonctl_CUR ( l_mo_line_rec.txn_source_line_id,
1273 l_ic_item_mst_rec.item_id,l_delivery_detail_id);
1274 FETCH New_txn_nonctl_CUR INTO l_trans_id, l_trans_qty, l_trans_qty2;
1275 IF (New_txn_nonctl_CUR%NOTFOUND) THEN
1276 l_trans_qty := 0;
1277 END IF;
1278 CLOSE New_txn_nonctl_CUR;
1279
1280 /* End Bug 2462993 */
1281
1282 IF (l_trans_qty <> 0) THEN
1283 l_shipping_attr(1).preferred_grade := NULL;
1284 l_shipping_attr(1).lot_number := NULL;
1285 -- l_shipping_attr(1).sublot_number := NULL; /* R12 P1 Bug#4561095*/
1286 l_shipping_attr(1).locator_id := NULL;
1287 l_return_status := '';
1288 l_shipping_attr(1).delivery_detail_id := l_delivery_detail_id;
1289 l_shipping_attr(1).released_status := 'Y';
1290 l_shipping_attr(1).trans_id := l_trans_id;
1291 l_shipping_attr(1).ordered_quantity:= (-1) * l_trans_qty;
1292 l_shipping_attr(1).ordered_quantity2:= (-1) * l_trans_qty2;
1293 l_shipping_attr(1).subinventory := l_mo_line_rec.to_subinventory_code;
1294
1295 /* Begin bug 2621228 - picked/pending quantity populated for nonctl items */
1296 l_shipping_attr(1).picked_quantity := (-1) * l_trans_qty;
1297 l_shipping_attr(1).picked_quantity2 := (-1) * l_trans_qty2;
1298 /* l_available_qty below is same as l_trans_qty */
1299 IF (l_available_qty < l_requested_qty ) THEN
1300 l_shipping_attr(1).pending_quantity := l_requested_qty - l_available_qty;
1301 l_shipping_attr(1).pending_quantity2 := l_requested_qty2 - l_available_qty2;
1302 ELSE
1303 l_shipping_attr(1).pending_quantity := 0;
1304 l_shipping_attr(1).pending_quantity2 := 0;
1305 END IF;
1306 /* End Bug 2621228 */
1307
1308 GMI_Reservation_Util.PrintLn('Shiping values Action flag '|| l_shipping_attr(1).action_flag);
1309 GMI_Reservation_Util.PrintLn('Shipping values Ordered qty '||l_shipping_attr(1).ordered_quantity);
1310 GMI_Reservation_Util.PrintLn('l_available_qty1 '|| l_available_qty);
1311 GMI_Reservation_Util.PrintLn('l_requested_qty '||l_requested_qty);
1312
1313 -- Bug 3776538
1314 dump_shp_attrb_data(p_shipping_attr => l_shipping_attr);
1315
1316 WSH_INTERFACE.Update_Shipping_Attributes
1317 (p_source_code => 'INV',
1318 p_changed_attributes => l_shipping_attr,
1319 x_return_status => l_return_status
1320 );
1321
1322 GMI_Reservation_Util.PrintLn('Return Status from [4] Update_Shipping_Attributes Call '||l_return_status);
1323
1324 /* Begin Bug2936797 - Nulling out pick slip number on default Lot */
1325
1326 -- HAW 3387829. There is a difference between non-lot and non-inventory
1327 -- Let's keep the original call to find_default_lot for non-lot items
1328
1329 /* HAW added the if condition to deal with non-lot control items*/
1330 IF (l_ic_item_mst_rec.noninv_ind <> 1)THEN
1331 GMI_Reservation_Util.PrintLn('Working with noncontrolled inventory item - Post G');
1332 GMI_Reservation_Util.PrintLn('Calling GMI_RESERVATION_UTIL.find_default_lot - Post G for l_mo_line_rec.txn_source_line_id'||l_mo_line_rec.txn_source_line_id);
1333 GMI_RESERVATION_UTIL.find_default_lot
1334 ( x_return_status => x_return_status,
1335 x_msg_count => x_msg_count,
1336 x_msg_data => x_msg_data,
1337 x_reservation_id => d_trans_id,
1338 p_line_id => l_mo_line_rec.txn_source_line_id
1339 );
1340 IF x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1341 GMI_RESERVATION_UTIL.println('Error returned by find default lot');
1342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1343 END IF;
1344
1345 IF (d_trans_id is NOT NULL) THEN
1346 UPDATE ic_tran_pnd
1347 SET pick_slip_number = NULL
1348 WHERE trans_id = d_trans_id;
1349 END IF;
1350 /* End Bug2936797 */
1351
1352 END IF;
1353
1354 IF (l_available_qty < l_requested_qty ) THEN
1355 l_shipping_attr(1).trans_id := l_tran_row.trans_id;
1356 l_shipping_attr(1).subinventory := NULL;
1357 l_shipping_attr(1).action_flag := 'B';
1358 l_shipping_attr(1).cycle_count_quantity := l_requested_qty - l_available_qty;
1359 l_shipping_attr(1).cycle_count_quantity2 := l_requested_qty2 - l_available_qty2;
1360 l_shipping_attr(1).released_status := '';
1361 l_return_status := '';
1362
1363 GMI_Reservation_Util.PrintLn('Shipping values cycle count quantity '||l_shipping_attr(1).cycle_count_quantity);
1364
1365 -- Bug 3776538
1366 dump_shp_attrb_data(p_shipping_attr => l_shipping_attr);
1367
1368 WSH_INTERFACE.Update_Shipping_Attributes
1369 (p_source_code => 'INV',
1370 p_changed_attributes => l_shipping_attr,
1371 x_return_status => l_return_status
1372 );
1373 GMI_Reservation_Util.PrintLn('Return Status from [5] Update_Shipping_Attributes Call '||l_return_status);
1374
1375
1376
1377 END IF; -- of l_available_qty < l_requested_qty
1378 END IF; -- of l_trans_qty <> 0
1379 END IF; -- OM_G_installed */
1380 END IF; -- non lot or loct controled
1381 <<del_loop_end>>
1382 null;
1383 END LOOP; /* delivery loop */
1384
1385
1386 /* End Enhancement 2320442 - Lakshmi Swamy */
1387 /* should also close this move order line ,if no more delivery left (no 'S')*/
1388
1389 IF l_mo_line_rec.quantity_delivered < l_mo_line_rec.quantity THEN
1390 Select count(*)
1391 INTO l_count
1392 From wsh_delivery_details
1393 Where move_order_line_id = l_mo_line_rec.line_id
1394 And source_line_id = l_mo_line_rec.txn_source_line_id
1395 And released_status = 'S';
1396 IF l_count = 0 THEN
1397 l_mo_line_rec.line_status :=5;
1398 l_mo_line_rec.quantity := l_mo_line_rec.quantity_detailed;
1399 GMI_Reservation_Util.PrintLn('Now, update the MO row');
1400 GMI_MOVE_ORDER_LINE_UTIL.Update_ROW(p_mo_line_rec => l_mo_line_rec);
1401 END IF;
1402 END IF;
1403
1404 <<NEXT_LINE>>
1405 l_mo_line_tbl(I) := l_mo_line_rec;
1406 x_return_status := l_return_status;
1407
1408 GMI_Reservation_Util.PrintLn('Before Loop 2');
1409 null;
1410 END IF; -- HW of l_no_error =1 for BUG:2296620
1411 END LOOP; /* mo line tbl loop */
1412
1413
1414 /* Load Output table */
1415
1416 x_mo_line_tbl := l_mo_line_tbl;
1417 WSH_Util_Core.PrintLn('Count MOL Table => '|| x_mo_line_tbl.COUNT);
1418 IF( l_warning_flag = 1 ) THEN
1419 RAISE l_warning;
1420 END IF;
1421
1422 IF( l_tolerance_flag = 1 ) THEN
1423 RAISE l_tolerance_warning;
1424 END IF;
1425
1426
1427 EXCEPTION
1428 WHEN FND_API.G_EXC_ERROR THEN
1429 ROLLBACK TO PICK_WAVE_CONFIRM;
1430 -- Bug 3859774
1431 IF (mo_line_txn_c%ISOPEN) THEN
1432 CLOSE mo_line_txn_c;
1433 END IF;
1434 x_return_status := fnd_api.g_ret_sts_error;
1435
1436 /* Get message count and data */
1437 FND_MSG_PUB.count_and_get
1438 ( p_count => x_msg_count
1439 , p_data => x_msg_data
1440 );
1441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1442 ROLLBACK TO PICK_WAVE_CONFIRM;
1443 x_return_status := fnd_api.g_ret_sts_unexp_error;
1444
1445
1446 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1447 ,l_api_name
1448 );
1449
1450 -- HW BUG#:2296620
1451 WHEN l_warning THEN
1452 ROLLBACK TO PICK_WAVE_CONFIRM;
1453 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1454 -- HW end of 2296620
1455
1456 /* NC Bug #2557029 */
1457 WHEN l_tolerance_warning THEN
1458 x_return_status := 'X';
1459 /* NC end of Bug #2557029 */
1460
1461 WHEN OTHERS THEN
1462 ROLLBACK TO PICK_WAVE_CONFIRM;
1463 x_return_status := fnd_api.g_ret_sts_unexp_error;
1464
1465 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1466 ,l_api_name
1467 );
1468
1469
1470 /* Get message count and data */
1471 FND_MSG_PUB.count_and_get
1472 ( p_count => x_msg_count
1473 , p_data => x_msg_data
1474 );
1475
1476
1477
1478 END PICK_CONFIRM;
1479
1480 FUNCTION check_required
1481 (
1482 p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
1483 )
1484 RETURN BOOLEAN
1485 IS
1486 BEGIN
1487
1488 WSH_Util_Core.PrintLn('header id ==> '||p_mo_line_rec.header_id);
1489 WSH_Util_Core.PrintLn('line num ==> '||p_mo_line_rec.line_number);
1490 WSH_Util_Core.PrintLn('org id ==> '||p_mo_line_rec.organization_id);
1491 WSH_Util_Core.PrintLn('Item id ==> '||p_mo_line_rec.inventory_item_id);
1492 WSH_Util_Core.PrintLn('uom code ==> '||p_mo_line_rec.uom_code);
1493 WSH_Util_Core.PrintLn('quantity ==> '||p_mo_line_rec.quantity);
1494 WSH_Util_Core.PrintLn('status ==> '||p_mo_line_rec.line_status);
1495 WSH_Util_Core.PrintLn('trans id ==> '||p_mo_line_rec.transaction_type_id);
1496
1497 IF p_mo_line_rec.header_id is NULL OR
1498 p_mo_line_rec.line_number is NULL OR
1499 p_mo_line_rec.organization_id is NULL OR
1500 p_mo_line_rec.inventory_item_id is NULL OR
1501 p_mo_line_rec.uom_code is NULL OR
1502 p_mo_line_rec.quantity is NULL OR
1503 p_mo_line_rec.line_status is NULL OR
1504 p_mo_line_rec.transaction_type_id is NULL THEN
1505
1506 RETURN TRUE;
1507 WSH_Util_Core.PrintLn('Return True');
1508 ELSE
1509 WSH_Util_Core.PrintLn('Return False');
1510 RETURN FALSE;
1511 END IF;
1512
1513 RETURN TRUE;
1514
1515 EXCEPTION
1516 WHEN OTHERS THEN
1517 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1518 'Check Required'
1519 );
1520
1521 RETURN TRUE;
1522
1523 END CHECK_REQUIRED;
1524
1525 PROCEDURE Get_Opm_converted_qty
1526 (
1527 p_opm_item_id IN NUMBER,
1528 p_apps_from_uom IN VARCHAR2,
1529 p_apps_to_uom IN VARCHAR2,
1530 p_opm_lot_id IN NUMBER,
1531 p_original_qty IN NUMBER,
1532 x_converted_qty OUT NOCOPY NUMBER,
1533 x_return_status OUT NOCOPY VARCHAR2,
1534 x_msg_count OUT NOCOPY NUMBER,
1535 x_msg_data OUT NOCOPY VARCHAR2
1536 )
1537 IS
1538 l_api_name CONSTANT VARCHAR2 (30) := 'Get_OPM_converted_qty';
1539 l_opm_from_uom VARCHAR2(4);
1540 l_opm_to_uom VARCHAR2(4);
1541 l_opm_lot_id NUMBER;
1542 BEGIN
1543
1544 /* Lets Set Return Status to Sucess To Begin With. */
1545
1546 x_return_status := FND_API.G_RET_STS_SUCCESS;
1547
1548 /* Check Input Values */
1549
1550 IF ( p_opm_item_id is NULL
1551 OR p_opm_item_id = 0
1552 OR p_opm_item_id = FND_API.G_MISS_NUM ) THEN
1553 FND_MESSAGE.Set_Name('GMI','MISSING');
1554 FND_MESSAGE.Set_Token('MISSING', 'Opm Item Id');
1555 FND_MSG_PUB.Add;
1556 raise FND_API.G_EXC_ERROR;
1557 END IF;
1558
1559 IF ( p_apps_from_uom is NULL
1560 OR p_apps_from_uom = FND_API.G_MISS_CHAR ) THEN
1561 FND_MESSAGE.Set_Name('GMI','MISSING');
1562 FND_MESSAGE.Set_Token('MISSING', 'apps from uom');
1563 FND_MSG_PUB.Add;
1564 raise FND_API.G_EXC_ERROR;
1565 END IF;
1566
1567 IF ( p_apps_to_uom is NULL
1568 OR p_apps_to_uom = FND_API.G_MISS_CHAR) THEN
1569 FND_MESSAGE.Set_Name('GMI','MISSING');
1570 FND_MESSAGE.Set_Token('MISSING', 'apps to uom');
1571 FND_MSG_PUB.Add;
1572 raise FND_API.G_EXC_ERROR;
1573 END IF;
1574
1575 IF ( p_original_qty is NULL
1576 OR p_apps_to_uom = FND_API.G_MISS_NUM ) THEN
1577 FND_MESSAGE.Set_Name('GMI','MISSING');
1578 FND_MESSAGE.Set_Token('MISSING', 'Original Qty Value');
1579 FND_MSG_PUB.Add;
1580 raise FND_API.G_EXC_ERROR;
1581 END IF;
1582
1583 /* Now Input Values have Been Validated Lets Get OPM */
1584 /* Equaivalent UOM codes. */
1585
1586 /* Lets Get OPM From UOM code from apps from uom code */
1587
1588 GMI_Reservation_Util.Get_OPMUOM_from_AppsUOM
1589 (
1590 p_Apps_UOM => p_apps_from_uom,
1591 x_OPM_UOM => l_opm_from_uom,
1592 x_return_status => x_return_status,
1593 x_msg_count => x_msg_count,
1594 x_msg_data => x_msg_data
1595 );
1596
1597 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1598 FND_MESSAGE.Set_Name('GMI','GMI_OPM_UOM_NOT_FOUND');
1599 FND_MESSAGE.Set_Token('APPS_UOM_CODE', p_apps_from_uom);
1600 FND_MSG_PUB.Add;
1601 RAISE FND_API.G_EXC_ERROR;
1602 END IF;
1603
1604 GMI_reservation_Util.PrintLn('Convert From OPM UOM => ' || l_opm_from_uom);
1605
1606
1607 /* Lets Get OPM to UOM code from apps to uom code */
1608
1609 GMI_Reservation_Util.Get_OPMUOM_from_AppsUOM
1610 (
1611 p_Apps_UOM => p_apps_to_uom,
1612 x_OPM_UOM => l_opm_to_uom,
1613 x_return_status => x_return_status,
1614 x_msg_count => x_msg_count,
1615 x_msg_data => x_msg_data
1616 );
1617
1618 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1619 FND_MESSAGE.Set_Name('GMI','GMI_OPM_UOM_NOT_FOUND');
1620 FND_MESSAGE.Set_Token('APPS_UOM_CODE', p_apps_to_uom);
1621 FND_MSG_PUB.Add;
1622 RAISE FND_API.G_EXC_ERROR;
1623 END IF;
1624
1625 GMI_reservation_Util.PrintLn('Convert To OPM UOM => ' || l_opm_from_uom);
1626
1627
1628
1629 IF ( p_opm_lot_id is NULL
1630 OR p_opm_item_id = FND_API.G_MISS_NUM ) THEN
1631 l_opm_lot_id := 0;
1632 ELSE
1633 l_opm_lot_id := p_opm_lot_id;
1634 END IF;
1635
1636 GMI_reservation_Util.PrintLn('Lot Id => ' || l_opm_lot_id);
1637 GMI_reservation_Util.PrintLn('Original Qty => ' || p_original_qty);
1638
1639 /* OKay We have the values Lets Convert..... */
1640
1641 GMICUOM.icuomcv
1642 (
1643 pitem_id => p_opm_item_id,
1644 plot_id => l_opm_lot_id,
1645 pcur_qty => p_original_qty,
1646 pcur_uom => l_opm_from_uom,
1647 pnew_uom => l_opm_to_uom,
1648 onew_qty => x_converted_qty
1649 );
1650
1651
1652 GMI_reservation_Util.PrintLn('converted Qty => ' || x_converted_qty);
1653
1654
1655 EXCEPTION
1656 WHEN FND_API.G_EXC_ERROR THEN
1657 x_return_status := fnd_api.g_ret_sts_error;
1658
1659 /* Get message count and data */
1660 FND_MSG_PUB.count_and_get
1661 ( p_count => x_msg_count
1662 , p_data => x_msg_data
1663 );
1664
1665 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1666 ROLLBACK TO PICK_WAVE_CONFIRM;
1667 x_return_status := fnd_api.g_ret_sts_unexp_error;
1668
1669 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1670 ,l_api_name
1671 );
1672
1673 WHEN OTHERS THEN
1674 ROLLBACK TO PICK_WAVE_CONFIRM;
1675 x_return_status := fnd_api.g_ret_sts_unexp_error;
1676
1677 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1678 ,l_api_name
1679 );
1680
1681
1682 END get_opm_converted_qty;
1683
1684 --Check_Shipping_Tolerances
1685 --
1686 -- This API checks to make sure that transacting the current allocation
1687 -- does not exceed shipping tolerances.
1688 -- This procedure should only be called for Pick Wave move orders
1689 -- p_line_id : the move order line id.
1690 -- p_quantity: the quantity to be transacted
1691 -- x_allowed: 'Y' if txn is allowed, 'N' otherwise
1692 -- x_max_quantity: the maximum quantity that can be pick confirmed
1693 -- without exceeding shipping tolerances
1694
1695
1696 PROCEDURE Check_Shipping_Tolerances
1697 ( x_return_status OUT NOCOPY VARCHAR2,
1698 x_msg_count OUT NOCOPY NUMBER,
1699 x_msg_data OUT NOCOPY VARCHAR2,
1700 x_allowed OUT NOCOPY VARCHAR2,
1701 x_max_quantity OUT NOCOPY NUMBER,
1702 x_max_quantity2 OUT NOCOPY NUMBER,
1703 p_line_id IN NUMBER,
1704 p_quantity IN NUMBER,
1705 p_quantity2 IN NUMBER
1706 ) IS
1707
1708 l_allowed VARCHAR2(1);
1709 l_return_status VARCHAR2(1);
1710 l_msg_count NUMBER;
1711 l_msg_data VARCHAR2(2000);
1712 l_api_name CONSTANT VARCHAR2(30) := 'Check_Shipping_Tolerances';
1713 l_txn_source_line_id NUMBER;
1714 l_max_quantity NUMBER;
1715 l_avail_req_qty NUMBER;
1716 l_max_quantity2 NUMBER;
1717 l_avail_req_qty2 NUMBER;
1718
1719 CURSOR c_txn_source IS
1720 SELECT txn_source_line_id
1721 FROM ic_txn_request_lines
1722 WHERE line_id = p_line_id;
1723
1724 CURSOR c_source_line IS
1725 SELECT source_line_id
1726 FROM wsh_delivery_details
1727 WHERE move_order_line_id = p_line_id;
1728
1729 BEGIN
1730
1731 -- By default, allow the transaction.
1732 l_allowed := 'Y';
1733 l_return_status := fnd_api.g_ret_sts_success;
1734 l_max_quantity := 1e125;
1735
1736 -- get sales order line id from the move order line
1737 OPEN c_txn_source;
1738 FETCH c_txn_source into l_txn_source_line_id;
1739 IF c_txn_source%NOTFOUND THEN
1740 CLOSE c_txn_source; -- Bug 3859774
1741 RAISE fnd_api.g_exc_error;
1742 END IF;
1743 CLOSE c_txn_source;
1744
1745 -- If for some reason the txn_source_line_id on the move order line is
1746 -- not yet populated, get the order line directly from the delivery
1747 -- details
1748 IF l_txn_source_line_id IS NULL THEN
1749 OPEN c_source_line;
1750 FETCH c_source_line INTO l_txn_source_line_id;
1751 If c_source_line%NOTFOUND Then
1752 l_txn_source_line_id := NULL;
1753 End If;
1754 CLOSE c_source_line; -- Bug 3859774
1755 END IF;
1756
1757 IF l_txn_source_line_id IS NOT NULL THEN
1758 check_quantity_to_pick(
1759 p_order_line_id => l_txn_source_line_id
1760 ,p_quantity_to_pick => p_quantity
1761 ,p_quantity2_to_pick => p_quantity2
1762 ,x_allowed_flag => l_allowed
1763 ,x_max_quantity_allowed => l_max_quantity
1764 ,x_max_quantity2_allowed => l_max_quantity2
1765 ,x_avail_req_quantity => l_avail_req_qty
1766 ,x_avail_req_quantity2 => l_avail_req_qty2
1767 ,x_return_status => l_return_status
1768 );
1769 If l_return_status = FND_API.G_RET_STS_ERROR Then
1770 raise FND_API.G_EXC_ERROR;
1771 Elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR Then
1772 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1773 End If;
1774 END IF;
1775
1776 x_max_quantity := l_max_quantity;
1777 x_allowed := l_allowed;
1778 x_return_status := l_return_status;
1779
1780 EXCEPTION
1781
1782 WHEN fnd_api.g_exc_error THEN
1783 x_return_status := FND_API.G_RET_STS_ERROR;
1784 x_allowed := 'N';
1785 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1786 ,p_data => x_msg_data);
1787
1788 WHEN fnd_api.g_exc_unexpected_error THEN
1789 x_return_status := FND_API.G_RET_STS_ERROR;
1790 x_allowed := 'N';
1791 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count
1792 ,p_data => x_msg_data);
1793
1794 WHEN OTHERS THEN
1795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1796 x_allowed := 'N';
1797 IF fnd_msg_pub.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1798 THEN
1799 FND_MSG_PUB.Add_Exc_Msg
1800 (G_PKG_NAME
1801 ,l_api_name);
1802 END IF;
1803
1804 END Check_Shipping_Tolerances;
1805
1806 -----------------------------------------------------------------------------
1807 --
1808 -- Procedure: check_quantity_to_pick
1809 -- Parameters: p_order_line_id, - order line being picked
1810 -- p_quantity_to_pick - quantity to transact that
1811 -- will be checked
1812 -- x_allowed_flag - 'Y' = allowed, 'N' = not allowed
1813 -- x_max_quantity_allowed - maximum quantity
1814 -- that can be picked
1815 -- x_avail_req_quantity - req quantity not yet staged
1816 -- x_return_status
1817 -- Description: Checks if the quantity to pick is within overshipment
1818 -- tolerance, based on the quantities requested and
1819 -- staged and assignments to deliveries or containers.
1820 -- Also returns the maximum quantity allowed to pick.
1821 -- History: HW Added Qty2 for OPM and changed procedure parameters
1822 -----------------------------------------------------------------------------
1823
1824 PROCEDURE check_quantity_to_pick(
1825 p_order_line_id IN NUMBER,
1826 p_quantity_to_pick IN NUMBER,
1827 p_quantity2_to_pick IN NUMBER DEFAULT NULL,
1828 x_allowed_flag OUT NOCOPY VARCHAR2,
1829 x_max_quantity_allowed OUT NOCOPY NUMBER,
1830 x_max_quantity2_allowed OUT NOCOPY NUMBER,
1831 x_avail_req_quantity OUT NOCOPY NUMBER,
1832 x_avail_req_quantity2 OUT NOCOPY NUMBER,
1833 x_return_status OUT NOCOPY VARCHAR2) IS
1834
1835
1836 -- HW OPM retrieve uom2
1837 CURSOR c_detail_info(x_source_line_id IN NUMBER) IS
1838 SELECT inventory_item_id,
1839 organization_id,
1840 requested_quantity_uom,
1841 requested_quantity_uom2,
1842 ship_tolerance_above
1843 FROM wsh_delivery_details
1844 WHERE source_line_id = x_source_line_id
1845 AND source_code = 'OE'
1846 AND container_flag = 'N'
1847 AND rownum = 1;
1848
1849
1850 -- HW OPM added qty2
1851 CURSOR c_detail_staged_quantities(x_source_line_id IN NUMBER) IS
1852 SELECT NVL(SUM(requested_quantity), 0) net_requested_qty,
1853 NVL(SUM(NVL(picked_quantity, requested_quantity)), 0) net_staged_qty,
1854 NVL(SUM(NVL(requested_quantity2,0)), 0) net_requested_qty2,
1855 NVL(SUM(NVL(picked_quantity2, requested_quantity2)), 0) net_staged_qty2
1856 FROM wsh_delivery_details
1857 WHERE source_line_id = x_source_line_id
1858 AND source_code = 'OE'
1859 AND container_flag = 'N'
1860 AND released_status IN ('X', 'Y', 'C');
1861
1862
1863 CURSOR c_ordered_quantity(x_source_line_id IN NUMBER,
1864 x_item_id IN NUMBER,
1865 x_primary_uom IN VARCHAR2) IS
1866 SELECT WSH_WV_UTILS.CONVERT_UOM(order_quantity_uom,
1867 x_primary_uom,
1868 ordered_quantity,
1869 x_item_id) quantity ,
1870 order_quantity_uom
1871 FROM oe_order_lines_all
1872 WHERE line_id = x_source_line_id;
1873
1874
1875 -- HW OPM cursor for OPM
1876 CURSOR c_ordered_quantity_opm(x_source_line_id IN NUMBER)IS
1877
1878 SELECT ordered_quantity,
1879 order_quantity_uom,
1880 ordered_quantity2,
1881 ordered_quantity_uom2
1882 FROM oe_order_lines_all
1883 WHERE line_id = x_source_line_id;
1884
1885 l_found_flag BOOLEAN;
1886 l_detail_info c_detail_info%ROWTYPE;
1887 l_staged_info c_detail_staged_quantities%ROWTYPE;
1888 l_order_line c_ordered_quantity%ROWTYPE;
1889 --HW OPM variable for OPM cursor
1890 l_order_line_opm c_ordered_quantity_opm%ROWTYPE;
1891 quantity NUMBER;
1892 l_max_quantity2 NUMBER;
1893 l_min_quantity2 NUMBER;
1894 l_max_quantity NUMBER;
1895 l_min_quantity NUMBER;
1896 l_msg_count NUMBER;
1897 l_msg_data VARCHAR2(2000);
1898 l_return_status varchar2(30);
1899
1900 l_apps_uom_ordered_quantity NUMBER := 0; -- Bug 2900072
1901
1902 l_req_qty_left NUMBER;
1903 others EXCEPTION;
1904
1905 -- HW OPM new varibales
1906 l_process_flag VARCHAR2(1) :=FND_API.G_FALSE;
1907 l_req_qty2_left NUMBER;
1908 l_apps_uom VARCHAR2(4);
1909 l_opm_uom VARCHAR2(4);
1910 l_apps_uom2 VARCHAR2(4);
1911 l_opm_uom2 VARCHAR2(4);
1912 l_ic_item_mst_rec GMI_RESERVATION_UTIL.ic_item_mst_rec;
1913
1914
1915 BEGIN
1916
1917 gmi_reservation_util.println('Inside GMI_PICK_WAVE_CONFIRM_PVT.check_quantity_to_pick');
1918 OPEN c_detail_info(p_order_line_id);
1919 FETCH c_detail_info INTO l_detail_info;
1920 l_found_flag := c_detail_info%FOUND;
1921 CLOSE c_detail_info;
1922
1923 -- HW OPM Added qty2
1924 IF NOT l_found_flag THEN
1925 FND_MESSAGE.SET_NAME('WSH','NO_DATA_FOUND');
1926 x_allowed_flag := 'N';
1927 x_max_quantity_allowed := NULL;
1928 x_avail_req_quantity := NULL;
1929 x_max_quantity2_allowed := NULL;
1930 x_avail_req_quantity2 := NULL;
1931
1932 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1933 RETURN;
1934 END IF;
1935
1936 -- HW OPM Need to check the org for forking
1937 IF NOT INV_GMI_RSV_BRANCH.Process_Branch(p_organization_id => l_detail_info.organization_id)
1938 THEN
1939 l_process_flag := FND_API.G_FALSE;
1940 ELSE
1941 l_process_flag := FND_API.G_TRUE;
1942 END IF;
1943
1944
1945 OPEN c_detail_staged_quantities(p_order_line_id);
1946 FETCH c_detail_staged_quantities INTO l_staged_info;
1947 l_found_flag := c_detail_staged_quantities%FOUND;
1948 CLOSE c_detail_staged_quantities;
1949
1950 -- HW OPM Added qty2
1951 IF NOT l_found_flag THEN
1952 FND_MESSAGE.SET_NAME('WSH','NO_DATA_FOUND');
1953 x_allowed_flag := 'N';
1954 x_max_quantity_allowed := NULL;
1955 x_avail_req_quantity := NULL;
1956 x_max_quantity2_allowed := NULL;
1957 x_avail_req_quantity2 := NULL;
1958 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1959 RETURN;
1960 END IF;
1961
1962 -- HW OPM for debugging puproses. Print values
1963 IF ( l_process_flag = FND_API.G_TRUE ) THEN
1964 gmi_reservation_util.println('Value of l_staged_info.net_requested_qty is '||l_staged_info.net_requested_qty);
1965 gmi_reservation_util.println('Value of l_staged_info.net_requested_qty2 is '||l_staged_info.net_requested_qty2);
1966 gmi_reservation_util.println('Value of l_staged_info.net_staged_qty is '||l_staged_info.net_staged_qty);
1967 gmi_reservation_util.println('Value of l_staged_info.net_satged_qty2 is '||l_staged_info.net_staged_qty2);
1968 END IF;
1969
1970 -- HW OPM Need to branch
1971 IF (l_process_flag = FND_API.G_FALSE ) THEN
1972 OPEN c_ordered_quantity(p_order_line_id,
1973 l_detail_info.inventory_item_id,
1974 l_detail_info.requested_quantity_uom);
1975 FETCH c_ordered_quantity INTO l_order_line;
1976 l_found_flag := c_ordered_quantity%FOUND;
1977 CLOSE c_ordered_quantity;
1978 ELSE
1979 OPEN c_ordered_quantity_opm(p_order_line_id);
1980 FETCH c_ordered_quantity_opm INTO l_order_line_opm;
1981 quantity := GMI_Reservation_Util.get_opm_converted_qty(
1982 p_apps_item_id => l_detail_info.inventory_item_id,
1983 p_organization_id => l_detail_info.organization_id,
1984 p_apps_from_uom => l_order_line_opm.order_quantity_uom,
1985 p_apps_to_uom => l_detail_info.requested_quantity_uom,
1986 p_original_qty => l_order_line_opm.ordered_quantity);
1987
1988 l_found_flag := c_ordered_quantity_opm%FOUND;
1989 CLOSE c_ordered_quantity_opm;
1990
1991 END IF; -- of branching
1992
1993 -- HW OPM Added qty2
1994 IF NOT l_found_flag THEN
1995 FND_MESSAGE.SET_NAME('WSH','NO_DATA_FOUND');
1996 x_allowed_flag := 'N';
1997 x_max_quantity_allowed := NULL;
1998 x_avail_req_quantity := NULL;
1999 x_max_quantity2_allowed := NULL;
2000 x_avail_req_quantity2 := NULL;
2001 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2002 RETURN;
2003 END IF;
2004
2005 Get_Min_Max_Tolerance_Quantity
2006 (
2007 p_api_version_number => 1.0
2008 , p_line_id => p_order_line_id
2009 , x_min_remaining_quantity => l_min_quantity
2010 , x_max_remaining_quantity => l_max_quantity
2011 , x_min_remaining_quantity2 => l_min_quantity2
2012 , x_max_remaining_quantity2 => l_max_quantity2
2013 , x_return_status => l_return_status
2014 , x_msg_count => l_msg_count
2015 , x_msg_data => l_msg_data );
2016
2017 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2018 raise others ;
2019 END IF;
2020
2021 -- HW added for debugging purposes
2022 IF ( l_process_flag = FND_API.G_TRUE ) THEN
2023 gmi_reservation_util.println('Value of l_min_quantity is '||l_min_quantity);
2024 gmi_reservation_util.println('Value of l_min_quantity2 is '||l_min_quantity2);
2025 gmi_reservation_util.println('Value of l_max_quantity is '||l_max_quantity);
2026 gmi_reservation_util.println('Value of l_max_quantity2 is '||l_max_quantity2);
2027 END IF;
2028
2029
2030 -- HW Need to branch
2031 IF ( l_process_flag = FND_API.G_FALSE ) THEN
2032 l_max_quantity := WSH_WV_UTILS.CONVERT_UOM(l_order_line.order_quantity_uom,
2033 l_detail_info.requested_quantity_uom,
2034 l_max_quantity,
2035 l_detail_info.inventory_item_id)
2036 - l_staged_info.net_staged_qty;
2037
2038 l_req_qty_left := GREATEST(0,
2039 (l_order_line.quantity
2040 - l_staged_info.net_requested_qty)
2041 );
2042
2043 ELSE
2044 l_max_quantity := GMI_Reservation_Util.get_opm_converted_qty(
2045 p_apps_item_id => l_detail_info.inventory_item_id,
2046 p_organization_id => l_detail_info.organization_id,
2047 p_apps_from_uom => l_order_line_opm.order_quantity_uom,
2048 p_apps_to_uom => l_detail_info.requested_quantity_uom,
2049 p_original_qty => l_max_quantity)
2050 - l_staged_info.net_staged_qty;
2051
2052 gmi_reservation_util.println('l_order_line_opm.ordered_quantity '||l_order_line_opm.ordered_quantity);
2053 gmi_reservation_util.println('l_order_line_opm.order_quantity_uom '||l_order_line_opm.order_quantity_uom);
2054 gmi_reservation_util.println('l_detail_info.requested_quantity_uom '||l_detail_info.requested_quantity_uom);
2055
2056 -- BEGIN Bug 2900072 - If the order UOM is different than item's UOM convert Ordered quantity
2057 IF (l_order_line_opm.order_quantity_uom <> l_detail_info.requested_quantity_uom) THEN
2058 l_apps_uom_ordered_quantity := GMI_Reservation_Util.get_opm_converted_qty(
2059 p_apps_item_id => l_detail_info.inventory_item_id,
2060 p_organization_id => l_detail_info.organization_id,
2061 p_apps_from_uom => l_order_line_opm.order_quantity_uom,
2062 p_apps_to_uom => l_detail_info.requested_quantity_uom,
2063 p_original_qty => l_order_line_opm.ordered_quantity);
2064 ELSE
2065 l_apps_uom_ordered_quantity := l_order_line_opm.ordered_quantity;
2066 END IF;
2067
2068 gmi_reservation_util.println('l_apps_uom_ordered_quantity '||l_apps_uom_ordered_quantity);
2069
2070 l_req_qty_left := GREATEST(0,
2071 (l_apps_uom_ordered_quantity
2072 - l_staged_info.net_requested_qty)
2073 );
2074 -- END Bug 2900072
2075
2076 l_max_quantity2 := nvl(l_max_quantity2,0) - nvl(l_staged_info.net_staged_qty2,0);
2077
2078 l_req_qty2_left := GREATEST(0,
2079 (l_order_line_opm.ordered_quantity2
2080 - l_staged_info.net_requested_qty2)
2081 );
2082
2083 END IF; -- of branching
2084
2085
2086 -- HW added for debugging purposes
2087 IF ( l_process_flag = FND_API.G_TRUE ) THEN
2088 gmi_reservation_util.println('Value of quantity is '||quantity);
2089 gmi_reservation_util.println('Value of l_order_line_opm.quantity2 is '||l_order_line_opm.ordered_quantity2);
2090 gmi_reservation_util.println('Value of l_req_qty_left is '|| l_req_qty_left);
2091 gmi_reservation_util.println('Value of l_req_qty2_left is '|| l_req_qty2_left);
2092 END IF;
2093
2094
2095 IF p_quantity_to_pick < 0 THEN
2096 x_allowed_flag := 'N';
2097 -- HW OPM added a checj for qty2
2098 ELSIF( p_quantity_to_pick > l_max_quantity) THEN
2099 -- Pupakare Begin BUG 2675737
2100 --OR
2101 --nvl(p_quantity2_to_pick,0) > nvl(l_max_quantity2,0) THEN
2102 -- End BUG 2675737
2103 x_allowed_flag := 'N';
2104 ELSE
2105 x_allowed_flag := 'Y';
2106 END IF;
2107
2108 x_max_quantity_allowed := l_max_quantity;
2109 x_avail_req_quantity := l_req_qty_left;
2110 -- HW OPM added qty2
2111 x_max_quantity2_allowed := l_max_quantity2;
2112 x_avail_req_quantity2 := l_req_qty2_left;
2113
2114 -- HW for debugging purposes, print values
2115 IF ( l_process_flag = FND_API.G_TRUE ) THEN
2116 gmi_reservation_util.println('Value of x_max_quantity_allowed is '||x_max_quantity_allowed);
2117 gmi_reservation_util.println('Value of x_max_quantity2_allowed is '||x_max_quantity2_allowed);
2118 gmi_reservation_util.println('Value of x_avail_req_quantity is '||x_avail_req_quantity);
2119 gmi_reservation_util.println('Value of x_avail_req_quantity2 is '||x_avail_req_quantity2);
2120 END IF;
2121
2122 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2123
2124
2125 EXCEPTION
2126 WHEN others THEN
2127 IF c_detail_info%ISOPEN THEN
2128 CLOSE c_detail_info;
2129 END IF;
2130 IF c_detail_staged_quantities%ISOPEN THEN
2131 CLOSE c_detail_staged_quantities;
2132 END IF;
2133 IF c_ordered_quantity%ISOPEN THEN
2134 CLOSE c_ordered_quantity;
2135 END IF;
2136 -- HW closing OPM cursor
2137 IF c_ordered_quantity_opm%ISOPEN THEN
2138 CLOSE c_ordered_quantity_opm;
2139 END IF;
2140
2141 x_allowed_flag := 'N';
2142 x_max_quantity_allowed := NULL;
2143 x_avail_req_quantity := NULL;
2144 -- HW Added for OPM
2145 x_max_quantity2_allowed := NULL;
2146 x_avail_req_quantity2 := NULL;
2147 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2148 wsh_util_core.default_handler('WSH_DETAILS_VALIDATIONS.check_quantity_to_pick');
2149
2150 END check_quantity_to_pick;
2151
2152 PROCEDURE Get_Min_Max_Tolerance_Quantity
2153 (
2154 p_api_version_number IN NUMBER
2155 , p_line_id IN NUMBER
2156 , x_min_remaining_quantity OUT NOCOPY NUMBER
2157 , x_max_remaining_quantity OUT NOCOPY NUMBER
2158 , x_min_remaining_quantity2 OUT NOCOPY NUMBER
2159 , x_max_remaining_quantity2 OUT NOCOPY NUMBER
2160 , x_return_status OUT NOCOPY VARCHAR2
2161 , x_msg_count OUT NOCOPY NUMBER
2162 , x_msg_data OUT NOCOPY VARCHAR2
2163 )
2164
2165 IS
2166
2167 l_api_version_number CONSTANT NUMBER := 1.0;
2168 l_api_name CONSTANT VARCHAR2(30) := 'Get_Min_Max_Tolerance_Quantity';
2169 l_line_set_id NUMBER;
2170 l_ship_tolerance_above NUMBER;
2171 l_ship_tolerance_below NUMBER;
2172 l_tolerance_quantity_below NUMBER;
2173 l_tolerance_quantity_above NUMBER;
2174 l_tolerance_quantity2_below NUMBER;
2175 l_tolerance_quantity2_above NUMBER;
2176
2177 l_ordered_quantity NUMBER;
2178 l_shipped_quantity NUMBER;
2179 l_shipping_quantity NUMBER;
2180 l_min_quantity_remaining NUMBER;
2181 l_max_quantity_remaining NUMBER;
2182 -- HW OPM added qty2 for OPM
2183 l_ordered_quantity2 NUMBER;
2184 l_shipped_quantity2 NUMBER;
2185 l_shipping_quantity2 NUMBER;
2186 l_min_quantity_remaining2 NUMBER;
2187 l_max_quantity_remaining2 NUMBER;
2188
2189 l_top_model_line_id NUMBER;
2190 l_ato_line_id NUMBER;
2191
2192 BEGIN
2193
2194 gmi_reservation_util.println('Entering GMI_PICK_WAVE_CONFIRM_PVT.Get_Min_Max_Tolerance_Quantity');
2195
2196 x_return_status := FND_API.G_RET_STS_SUCCESS;
2197 /*
2198 IF NOT FND_API.Compatible_API_Call
2199 ( l_api_version_number
2200 , p_api_version_number
2201 , l_api_name
2202 , G_PKG_NAME
2203 )
2204 THEN
2205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2206 END IF;
2207 */
2208 -- HW OPM retrieve qty2 for OPM
2209 SELECT ship_tolerance_below,
2210 ship_tolerance_above,
2211 line_set_id,
2212 ordered_quantity,
2213 shipped_quantity,
2214 ordered_quantity2,
2215 shipped_quantity2,
2216 top_model_line_id,
2217 ato_line_id
2218 INTO l_ship_tolerance_below,
2219 l_ship_tolerance_above,
2220 l_line_set_id,
2221 l_ordered_quantity,
2222 l_shipped_quantity,
2223 l_ordered_quantity2,
2224 l_shipped_quantity2,
2225 l_top_model_line_id,
2226 l_ato_line_id
2227 FROM OE_ORDER_LINES_ALL
2228 WHERE line_id = p_line_id;
2229
2230 IF nvl(l_top_model_line_id,-1) = nvl(l_ato_line_id,-1) AND
2231 l_top_model_line_id IS NOT NULL THEN
2232
2233 gmi_reservation_util.println('It is a ATO MODEL ');
2234
2235 SELECT line_set_id
2236 INTO l_line_set_id
2237 FROM OE_ORDER_LINES_ALL
2238 WHERE line_id = l_top_model_line_id;
2239
2240 gmi_reservation_util.println('Line set id : '||l_line_set_id);
2241
2242 END IF;
2243
2244 IF l_line_set_id IS NOT NULL THEN
2245 -- HW Sum qty2 for OPM
2246 gmi_reservation_util.println('Line set id : '||l_line_set_id);
2247 SELECT SUM(ordered_quantity)
2248 , SUM(shipped_quantity)
2249 , SUM(shipping_quantity)
2250 , SUM(nvl(ordered_quantity2,0))
2251 , SUM(nvl(shipped_quantity2,0))
2252 , SUM(nvl(shipping_quantity2,0))
2253 INTO l_ordered_quantity
2254 , l_shipped_quantity
2255 , l_shipping_quantity
2256 , l_ordered_quantity2
2257 , l_shipped_quantity2
2258 , l_shipping_quantity2
2259 FROM oe_order_lines_all
2260 WHERE line_set_id = l_line_set_id;
2261
2262 END IF;
2263
2264 gmi_reservation_util.println('Total ordered quantity : '||to_char(l_ordered_quantity));
2265 gmi_reservation_util.println('Total shipped quantity : '||to_char(l_shipped_quantity));
2266
2267 l_tolerance_quantity_below := nvl(l_ordered_quantity,0)*nvl(l_ship_tolerance_below,0)/100;
2268 l_tolerance_quantity_above := nvl(l_ordered_quantity,0)*nvl(l_ship_tolerance_above,0)/100;
2269 l_tolerance_quantity2_below := nvl(l_ordered_quantity2,0)*nvl(l_ship_tolerance_below,0)/100;
2270 l_tolerance_quantity2_above := nvl(l_ordered_quantity2,0)*nvl(l_ship_tolerance_above,0)/100;
2271
2272 gmi_reservation_util.println('Tolerance quantity below : '||l_tolerance_quantity_below);
2273 gmi_reservation_util.println('Tolerance quantity above : '||l_tolerance_quantity_above);
2274 gmi_reservation_util.println('Tolerance quantity2 below : '||l_tolerance_quantity2_below);
2275 gmi_reservation_util.println('Tolerance quantity2 above : '||l_tolerance_quantity2_above);
2276
2277 l_min_quantity_remaining := l_ordered_quantity - nvl(l_shipped_quantity,0) - l_tolerance_quantity_below;
2278 l_max_quantity_remaining := l_ordered_quantity - nvl(l_shipped_quantity,0) + l_tolerance_quantity_above;
2279
2280 -- HW Get min and max qty2 for OPM
2281 l_min_quantity_remaining2 := nvl(l_ordered_quantity2,0) - nvl(l_shipped_quantity2,0) - l_tolerance_quantity2_below;
2282 l_max_quantity_remaining2 := nvl(l_ordered_quantity2,0) - nvl(l_shipped_quantity2,0) + l_tolerance_quantity2_above;
2283
2284 gmi_reservation_util.println('Min remaining quantity : '||l_min_quantity_remaining);
2285 gmi_reservation_util.println('Max remaining quantity : '||l_max_quantity_remaining);
2286
2287 -- HW Print Qty2 for OPM
2288 gmi_reservation_util.println('Min remaining quantity2 : '||l_min_quantity_remaining2);
2289 gmi_reservation_util.println('Max remaining quantity2 : '||l_max_quantity_remaining2);
2290
2291 IF l_min_quantity_remaining < 0 THEN
2292
2293 l_min_quantity_remaining := 0;
2294
2295 END IF;
2296
2297 IF l_min_quantity_remaining2 < 0 THEN
2298 -- HW reset qty2 for OPM
2299 l_min_quantity_remaining2 := 0;
2300
2301 END IF;
2302
2303 IF l_max_quantity_remaining < 0 THEN
2304
2305 l_max_quantity_remaining := 0;
2306 END IF;
2307
2308 IF l_max_quantity_remaining2 < 0 THEN
2309 -- HW reset qty2 for OPM
2310 l_max_quantity_remaining2 := 0;
2311
2312 END IF;
2313
2314 x_min_remaining_quantity := l_min_quantity_remaining;
2315 x_max_remaining_quantity := l_max_quantity_remaining;
2316
2317 -- HW added qty2 for OPM
2318 x_min_remaining_quantity2 := nvl(l_min_quantity_remaining2,0);
2319 x_max_remaining_quantity2 := nvl(l_max_quantity_remaining2,0);
2320
2321 gmi_reservation_util.println('Return Min remaining quantity : '||x_min_remaining_quantity);
2322 gmi_reservation_util.println('Return Max remaining quantity : '||x_max_remaining_quantity);
2323
2324 -- HW print qty2 for OPM
2325 gmi_reservation_util.println('Return Min remaining quantity2 : '||x_min_remaining_quantity2);
2326 gmi_reservation_util.println('Return Max remaining quantity2 : '||x_max_remaining_quantity2);
2327
2328 gmi_reservation_util.println('Exiting GMI_PICK_WAVE_CONFIRM_PVT.Get_Min_Max_Tolerance_Quantity '||x_return_status);
2329
2330 EXCEPTION
2331
2332 WHEN NO_DATA_FOUND THEN
2333
2334 x_min_remaining_quantity := 0;
2335 x_max_remaining_quantity := 0;
2336
2337 -- HW reset values for qty2 for OPM
2338 x_min_remaining_quantity2 := 0;
2339 x_max_remaining_quantity2 := 0;
2340
2341 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2342 THEN
2343 x_return_status := FND_API.G_RET_STS_ERROR;
2344 END IF;
2345
2346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2347
2348 gmi_reservation_util.println('Unexpected Error : '||sqlerrm);
2349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2350
2351 -- Get message count and data
2352
2353 FND_MSG_PUB.Count_And_Get
2354 ( p_count => x_msg_count
2355 , p_data => x_msg_data
2356 );
2357
2358 WHEN OTHERS THEN
2359
2360 gmi_reservation_util.println('Unexpected Error : '||sqlerrm);
2361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2362
2363 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2364 THEN
2365 FND_MSG_PUB.Add_Exc_Msg
2366 ( G_PKG_NAME
2367 , 'Get_Min_Max_Tolerance_Quantity'
2368 );
2369 END IF;
2370
2371 -- Get message count and data
2372
2373 FND_MSG_PUB.Count_And_Get
2374 ( p_count => x_msg_count
2375 , p_data => x_msg_data
2376 );
2377
2378 END Get_Min_Max_Tolerance_Quantity;
2379
2380
2381 /* Following procedure created for bug2901317*/
2382 PROCEDURE BALANCE_NONINV_TRAN
2383 (
2384 p_dflt_nonctl_tran_rec IN GMI_TRANS_ENGINE_PUB.ictran_rec
2385 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2386 , p_requested_qty IN NUMBER
2387 , p_requested_qty2 IN NUMBER
2388 , p_delivery_detail_id IN NUMBER
2389 , x_tran_row OUT NOCOPY IC_TRAN_PND%ROWTYPE
2390 , x_return_status OUT NOCOPY VARCHAR2
2391 , x_msg_count OUT NOCOPY NUMBER
2392 , x_msg_data OUT NOCOPY VARCHAR2
2393 )
2394 IS
2395
2396 l_dflt_nonctl_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
2397 l_validation_level VARCHAR2(4) := FND_API.G_VALID_LEVEL_FULL;
2398 BEGIN
2399
2400 l_dflt_nonctl_tran_rec := p_dflt_nonctl_tran_rec;
2401
2402 /* Split ic_tran_pnd to have remaining quantity backordered.
2403 set the staged ind on original line to 1 */
2404
2405 l_dflt_nonctl_tran_rec.trans_qty := (-1) *(ABS(l_dflt_nonctl_tran_rec.trans_qty) - p_requested_qty);
2406 l_dflt_nonctl_tran_rec.trans_qty2 := (-1) *(ABS(l_dflt_nonctl_tran_rec.trans_qty2) - p_requested_qty2);
2407
2408 GMI_Reservation_Util.PrintLn('Updating pending txn with qty '||l_dflt_nonctl_tran_rec.trans_qty );
2409
2410 l_dflt_nonctl_tran_rec.staged_ind := 0;
2411 l_dflt_nonctl_tran_rec.line_detail_id := null;
2412
2413
2414 /* Updating the default transaction with backordered quantity */
2415 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
2416 p_api_version => 1.0
2417 ,p_init_msg_list => FND_API.G_FALSE
2418 ,p_commit => p_commit
2419 ,p_validation_level => l_validation_level
2420 ,p_tran_rec => l_dflt_nonctl_tran_rec
2421 ,x_tran_row => x_tran_row
2422 ,x_return_status => x_return_status
2423 ,x_msg_count => x_msg_count
2424 ,x_msg_data => x_msg_data);
2425
2426 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2427 GMI_reservation_Util.PrintLn('(opm_dbg) in PICK_CONFIRM:
2428 Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.' );
2429 GMI_reservation_Util.PrintLn(x_msg_data); -- Bug 3859774
2430 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2431 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
2432 FND_MESSAGE.Set_Token('WHERE', 'PICK_CONFIRM');
2433 FND_MSG_PUB.Add;
2434 RAISE FND_API.G_EXC_ERROR;
2435 END IF;
2436
2437 l_dflt_nonctl_tran_rec.trans_id := NULL;
2438 l_dflt_nonctl_tran_rec.line_detail_id := p_delivery_detail_id;
2439 l_dflt_nonctl_tran_rec.staged_ind := 1;
2440 l_dflt_nonctl_tran_rec.trans_qty := -1 * p_requested_qty;
2441 l_dflt_nonctl_tran_rec.trans_qty2 := -1 * p_requested_qty2;
2442
2443
2444 GMI_Reservation_Util.PrintLn('Creating new pending txn with req qty '||l_dflt_nonctl_tran_rec.trans_qty );
2445
2446 /* Creating a new transaction with the staged quantity */
2447 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
2448 ( p_api_version => 1.0
2449 , p_init_msg_list => FND_API.G_FALSE
2450 , p_commit => FND_API.G_FALSE
2451 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2452 , p_tran_rec => l_dflt_nonctl_tran_rec
2453 , x_tran_row => x_tran_row
2454 , x_return_status => x_return_status
2455 , x_msg_count => x_msg_count
2456 , x_msg_data => x_msg_data
2457 );
2458
2459 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2460 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by Create_Pending_Transaction,
2461 return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
2462 GMI_reservation_Util.PrintLn(x_msg_data); -- Bug 3859774
2463 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
2464 FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
2465 FND_MESSAGE.Set_Token('WHERE','PICK_CONFIRM');
2466 FND_MSG_PUB.Add;
2467 raise FND_API.G_EXC_ERROR;
2468 END IF;
2469
2470 EXCEPTION
2471
2472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2474
2475 FND_MSG_PUB.Count_And_Get
2476 ( p_count => x_msg_count
2477 , p_data => x_msg_data
2478 );
2479 WHEN OTHERS THEN
2480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2481
2482 FND_MSG_PUB.Count_And_Get
2483 ( p_count => x_msg_count
2484 , p_data => x_msg_data
2485 );
2486
2487 END;
2488
2489 /* Following procedure added for Enhancement 2320442 - Lakshmi Swamy */
2490 /* Bug2901317 - April 21st 2003 - Made changes to incorporate non-inventory items */
2491
2492
2493 PROCEDURE BALANCE_NONCTL_INV_TRAN
2494 (
2495 p_mo_line_rec IN GMI_MOVE_ORDER_GLOBAL.mo_line_rec
2496 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2497 , p_item_id IN NUMBER
2498 , p_whse_code IN VARCHAR2
2499 , p_requested_qty IN NUMBER
2500 , p_requested_qty2 IN NUMBER
2501 , p_delivery_detail_id IN NUMBER
2502 , x_available_qty OUT NOCOPY NUMBER
2503 , x_available_qty2 OUT NOCOPY NUMBER
2504 , x_tran_row OUT NOCOPY IC_TRAN_PND%ROWTYPE
2505 , x_return_status OUT NOCOPY VARCHAR2
2506 , x_msg_count OUT NOCOPY NUMBER
2507 , x_msg_data OUT NOCOPY VARCHAR2
2508 )
2509 IS
2510
2511 CURSOR loct_onhand_CUR IS
2512 SELECT loct_onhand , loct_onhand2
2513 FROM ic_loct_inv
2514 WHERE whse_code = p_whse_code
2515 AND item_id = p_item_id
2516 AND lot_id = 0
2517 AND location = GMI_RESERVATION_UTIL.G_DEFAULT_LOCT
2518 AND delete_mark = 0;
2519
2520
2521 CURSOR staged_qty_CUR IS
2522 SELECT NVL(sum(trans_qty),0), NVL(sum(trans_qty2),0)
2523 FROM ic_tran_pnd
2524 WHERE item_id = p_item_id
2525 AND whse_code = p_whse_code
2526 AND doc_type = 'OMSO'
2527 AND staged_ind = 1
2528 AND delete_mark = 0
2529 AND completed_ind = 0;
2530
2531 CURSOR tot_bkordr_qty_CUR IS
2532 SELECT sum(requested_quantity), sum(requested_quantity2)
2533 FROM wsh_delivery_details
2534 WHERE released_status = 'B'
2535 AND source_line_id = p_mo_line_rec.txn_source_line_id;
2536
2537 -- HW BUG# 3871662: Issue:Cursor check_wsh violated the standards.
2538 -- Removed the cursor since it's not being used in this procedure
2539
2540 Cursor item_mst_dtl IS
2541 Select noninv_ind
2542 from ic_item_mst
2543 where item_id = p_item_id;
2544
2545 l_trans_id NUMBER;
2546 l_dflt_nonctl_tran_rec GMI_TRANS_ENGINE_PUB.ictran_rec;
2547 l_dflt_nonctl_tran_rec1 GMI_TRANS_ENGINE_PUB.ictran_rec;
2548 l_validation_level VARCHAR2(4) := FND_API.G_VALID_LEVEL_FULL;
2549 l_tran_row IC_TRAN_PND%ROWTYPE;
2550 l_onhand_qty NUMBER := 0; -- Bug 2910069
2551 l_onhand_qty2 NUMBER := 0; -- Bug 2910069
2552 l_backorder_qty NUMBER := 0;
2553 l_backorder_qty2 NUMBER := 0;
2554 l_commit_qty NUMBER := 0;
2555 l_commit_qty2 NUMBER := 0;
2556 l_pick_slip_number NUMBER := 0;
2557
2558 l_allow_negative_inv NUMBER; /* 2690711 */
2559 l_noninv_ind NUMBER;
2560
2561
2562
2563 BEGIN
2564
2565 GMI_Reservation_Util.PrintLn('Handling Noninventory/Non-controlled Items');
2566
2567 GMI_RESERVATION_UTIL.find_default_lot
2568 ( x_return_status => x_return_status,
2569 x_msg_count => x_msg_count,
2570 x_msg_data => x_msg_data,
2571 x_reservation_id => l_trans_id,
2572 p_line_id => p_mo_line_rec.txn_source_line_id
2573 );
2574
2575 l_dflt_nonctl_tran_rec1.trans_id := l_trans_id;
2576
2577 IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND
2578 (l_dflt_nonctl_tran_rec1, l_dflt_nonctl_tran_rec ) THEN
2579
2580 Open item_mst_dtl;
2581 Fetch item_mst_dtl INTO l_noninv_ind;
2582 Close item_mst_dtl;
2583
2584 IF (l_noninv_ind = 1) THEN
2585 GMI_Reservation_Util.PrintLn('Non Inventory Item');
2586 IF (p_requested_qty < ABS(l_dflt_nonctl_tran_rec.trans_qty)) THEN
2587
2588 GMI_PICK_WAVE_CONFIRM_PVT.BALANCE_NONINV_TRAN
2589 (
2590 p_dflt_nonctl_tran_rec => l_dflt_nonctl_tran_rec,
2591 p_commit => p_commit,
2592 p_requested_qty => p_requested_qty,
2593 p_requested_qty2 => p_requested_qty2,
2594 p_delivery_detail_id => p_delivery_detail_id,
2595 x_tran_row => l_tran_row,
2596 x_return_status => x_return_status,
2597 x_msg_count => x_msg_count,
2598 x_msg_data => x_msg_data
2599 );
2600
2601 RETURN;
2602 END IF;
2603 END IF;
2604
2605
2606 /* Begin Bug 2690711 */
2607
2608 l_allow_negative_inv := FND_PROFILE.VALUE('IC$ALLOWNEGINV');
2609
2610 IF (l_noninv_ind = 1) OR (l_allow_negative_inv = 1) THEN
2611
2612 l_dflt_nonctl_tran_rec.trans_qty := (-1) * p_requested_qty;
2613 l_dflt_nonctl_tran_rec.trans_qty2 := (-1) * p_requested_qty2;
2614 GMI_Reservation_Util.PrintLn('Irrespective of available quantity, transacting all the requested quantity');
2615
2616 l_dflt_nonctl_tran_rec.line_detail_id := p_delivery_detail_id;
2617 l_dflt_nonctl_tran_rec.staged_ind := 1;
2618
2619 /* Updating the default transaction with requested quantity */
2620 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
2621 p_api_version => 1.0
2622 ,p_init_msg_list => FND_API.G_FALSE
2623 ,p_commit => p_commit
2624 ,p_validation_level => l_validation_level
2625 ,p_tran_rec => l_dflt_nonctl_tran_rec
2626 ,x_tran_row => l_tran_row
2627 ,x_return_status => x_return_status
2628 ,x_msg_count => x_msg_count
2629 ,x_msg_data => x_msg_data);
2630
2631 x_available_qty := p_requested_qty;
2632 x_available_qty2 := p_requested_qty2;
2633
2634 x_tran_row := l_tran_row;
2635
2636
2637 /* Begin Bug2936797 - populating pick slip number for non-controlled item */
2638 /* Setting the pick slip number */
2639
2640 IF (l_noninv_ind = 1) THEN
2641 RETURN;
2642 END IF;
2643
2644 GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip
2645 ( p_organization_id => p_mo_line_rec.organization_id
2646 , p_line_id => p_mo_line_rec.line_id
2647 , x_return_status => x_return_status
2648 , x_msg_count => x_msg_count
2649 , x_msg_data => x_msg_data
2650 , x_pick_slip_number => l_pick_slip_number
2651 );
2652
2653 GMI_reservation_Util.PrintLn('pick slip number is '|| l_pick_slip_number);
2654
2655 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
2656 THEN
2657 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_lots: Error returned by GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip.', 'pick_lots.log');
2658 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2659 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip');
2660 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2661 FND_MSG_PUB.Add;
2662 RAISE FND_API.G_EXC_ERROR;
2663 END IF;
2664
2665 UPDATE ic_tran_pnd
2666 SET pick_slip_number = l_pick_slip_number
2667 WHERE trans_id = l_tran_row.trans_id;
2668
2669 /* End Bug2936797 */
2670 RETURN;
2671 END IF;
2672 /* End Bug 2690711 */
2673
2674 OPEN loct_onhand_CUR;
2675 FETCH loct_onhand_CUR INTO l_onhand_qty,l_onhand_qty2;
2676 CLOSE loct_onhand_CUR;
2677
2678 OPEN staged_qty_CUR;
2679 FETCH staged_qty_CUR INTO l_commit_qty,l_commit_qty2;
2680 CLOSE staged_qty_CUR;
2681
2682 GMI_Reservation_Util.PrintLn('l_commit_qty is '||l_commit_qty);
2683 GMI_Reservation_Util.PrintLn('l_onhand_qty is '||l_onhand_qty);
2684
2685 IF (ABS(l_commit_qty) < l_onhand_qty ) THEN
2686 x_available_qty := l_onhand_qty - (-1 * l_commit_qty);
2687 x_available_qty2 := l_onhand_qty2 - (-1 * l_commit_qty2);
2688 ELSE
2689 x_available_qty := 0;
2690 x_available_qty2 := 0;
2691 END IF;
2692
2693 GMI_Reservation_Util.PrintLn('x available qty '||x_available_qty);
2694 GMI_Reservation_Util.PrintLn('p_requested_qty '||p_requested_qty);
2695
2696 IF (x_available_qty <> 0) THEN
2697 IF (x_available_qty < p_requested_qty) THEN
2698 /* Split ic_tran_pnd to have remaining quantity backordered.
2699 set the staged ind on original line to 1 */
2700
2701 l_backorder_qty := p_requested_qty - x_available_qty;
2702 l_backorder_qty2 := p_requested_qty2 - x_available_qty2;
2703 l_dflt_nonctl_tran_rec.trans_qty := -1 * ((-1 * l_dflt_nonctl_tran_rec.trans_qty) - p_requested_qty + l_backorder_qty);
2704 l_dflt_nonctl_tran_rec.trans_qty2 := -1 * ((-1 * l_dflt_nonctl_tran_rec.trans_qty2) - p_requested_qty2 + l_backorder_qty2);
2705
2706 GMI_Reservation_Util.PrintLn('1. Updating pending txn with qty '||l_dflt_nonctl_tran_rec.trans_qty );
2707 l_dflt_nonctl_tran_rec.staged_ind := 0;
2708 l_dflt_nonctl_tran_rec.line_detail_id := null;
2709
2710 /* Updating the default transaction with backordered quantity */
2711 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
2712 p_api_version => 1.0
2713 ,p_init_msg_list => FND_API.G_FALSE
2714 ,p_commit => p_commit
2715 ,p_validation_level => l_validation_level
2716 ,p_tran_rec => l_dflt_nonctl_tran_rec
2717 ,x_tran_row => l_tran_row
2718 ,x_return_status => x_return_status
2719 ,x_msg_count => x_msg_count
2720 ,x_msg_data => x_msg_data);
2721
2722 l_dflt_nonctl_tran_rec.trans_id := NULL;
2723 l_dflt_nonctl_tran_rec.line_detail_id := p_delivery_detail_id;
2724 l_dflt_nonctl_tran_rec.staged_ind := 1;
2725 l_dflt_nonctl_tran_rec.trans_qty := -1 * x_available_qty;
2726 l_dflt_nonctl_tran_rec.trans_qty2 := -1 * x_available_qty2;
2727
2728 GMI_Reservation_Util.PrintLn('1. creating new pending txn with avail qty '||l_dflt_nonctl_tran_rec.trans_qty );
2729
2730 /* Creating a new transaction with the staged quantity */
2731 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
2732 ( p_api_version => 1.0
2733 , p_init_msg_list => FND_API.G_FALSE
2734 , p_commit => FND_API.G_FALSE
2735 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2736 , p_tran_rec => l_dflt_nonctl_tran_rec
2737 , x_tran_row => l_tran_row
2738 , x_return_status => x_return_status
2739 , x_msg_count => x_msg_count
2740 , x_msg_data => x_msg_data
2741 );
2742
2743 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2744 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by Create_Pending_Transaction,
2745 return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
2746 GMI_reservation_Util.PrintLn(x_msg_data); -- Bug 3859774
2747 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
2748 FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
2749 FND_MESSAGE.Set_Token('WHERE','Create_Default_Lot');
2750 FND_MSG_PUB.Add;
2751 raise FND_API.G_EXC_ERROR;
2752 END IF;
2753
2754 ELSIF (p_requested_qty <> ABS(l_dflt_nonctl_tran_rec.trans_qty)) THEN
2755 /* Split ic_tran_pnd to have remaining quantity backordered.
2756 set the staged ind on original line to 1 */
2757
2758 l_dflt_nonctl_tran_rec.trans_qty := (-1) *(ABS(l_dflt_nonctl_tran_rec.trans_qty) - p_requested_qty);
2759 l_dflt_nonctl_tran_rec.trans_qty2 := (-1) *(ABS(l_dflt_nonctl_tran_rec.trans_qty2) - p_requested_qty2);
2760
2761 GMI_Reservation_Util.PrintLn('2. Updating pending txn with qty '||l_dflt_nonctl_tran_rec.trans_qty );
2762
2763 l_dflt_nonctl_tran_rec.staged_ind := 0;
2764 l_dflt_nonctl_tran_rec.line_detail_id := null;
2765
2766 /* Updating the default transaction with backordered quantity */
2767 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
2768 p_api_version => 1.0
2769 ,p_init_msg_list => FND_API.G_FALSE
2770 ,p_commit => p_commit
2771 ,p_validation_level => l_validation_level
2772 ,p_tran_rec => l_dflt_nonctl_tran_rec
2773 ,x_tran_row => l_tran_row
2774 ,x_return_status => x_return_status
2775 ,x_msg_count => x_msg_count
2776 ,x_msg_data => x_msg_data);
2777
2778 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2779 GMI_reservation_Util.PrintLn('(opm_dbg) in PICK_CONFIRM:
2780 Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.' );
2781 GMI_reservation_Util.PrintLn(x_msg_data); -- Bug 3859774
2782 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2783 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
2784 FND_MESSAGE.Set_Token('WHERE', 'PICK_CONFIRM');
2785 FND_MSG_PUB.Add;
2786 RAISE FND_API.G_EXC_ERROR;
2787 END IF;
2788
2789 l_dflt_nonctl_tran_rec.trans_id := NULL;
2790 l_dflt_nonctl_tran_rec.line_detail_id := p_delivery_detail_id;
2791 l_dflt_nonctl_tran_rec.staged_ind := 1;
2792 l_dflt_nonctl_tran_rec.trans_qty := -1 * p_requested_qty;
2793 l_dflt_nonctl_tran_rec.trans_qty2 := -1 * p_requested_qty2;
2794
2795 GMI_Reservation_Util.PrintLn('2. creating new pending txn with req qty '||l_dflt_nonctl_tran_rec.trans_qty );
2796
2797 /* Creating a new transaction with the staged quantity */
2798 GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION
2799 ( p_api_version => 1.0
2800 , p_init_msg_list => FND_API.G_FALSE
2801 , p_commit => FND_API.G_FALSE
2802 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2803 , p_tran_rec => l_dflt_nonctl_tran_rec
2804 , x_tran_row => l_tran_row
2805 , x_return_status => x_return_status
2806 , x_msg_count => x_msg_count
2807 , x_msg_data => x_msg_data
2808 );
2809
2810 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2811 GMI_reservation_Util.PrintLn('(opm_dbg) Error return by Create_Pending_Transaction,
2812 return_status='|| x_return_status||', x_msg_count='|| x_msg_count||'.');
2813 GMI_reservation_Util.PrintLn(x_msg_data); -- Bug 3859774
2814 FND_MESSAGE.Set_Name('GMI','GMI_ERROR');
2815 FND_MESSAGE.Set_Token('BY_PROC','GMI_TRANS_ENGINE_PUB.CREATE_PENDING_TRANSACTION');
2816 FND_MESSAGE.Set_Token('WHERE','PICK_CONFIRM');
2817 FND_MSG_PUB.Add;
2818 raise FND_API.G_EXC_ERROR;
2819 END IF;
2820
2821 ELSE
2822
2823 l_dflt_nonctl_tran_rec.line_detail_id := p_delivery_detail_id;
2824 l_dflt_nonctl_tran_rec.staged_ind := 1;
2825
2826 GMI_Reservation_Util.PrintLn('3. Updating pending txn with staged ind/delivery id');
2827
2828 GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION(
2829 p_api_version => 1.0
2830 ,p_init_msg_list => FND_API.G_FALSE
2831 ,p_commit => p_commit
2832 ,p_validation_level => l_validation_level
2833 ,p_tran_rec => l_dflt_nonctl_tran_rec
2834 ,x_tran_row => l_tran_row
2835 ,x_return_status => x_return_status
2836 ,x_msg_count => x_msg_count
2837 ,x_msg_data => x_msg_data);
2838
2839 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2840 GMI_reservation_Util.PrintLn('(opm_dbg) in PICK_CONFIRM:
2841 Error returned by GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION.' );
2842 GMI_reservation_Util.PrintLn(x_msg_data); -- Bug 3859774
2843 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2844 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_TRANS_ENGINE_PUB.UPDATE_PENDING_TRANSACTION');
2845 FND_MESSAGE.Set_Token('WHERE', 'PICK_CONFIRM');
2846 FND_MSG_PUB.Add;
2847 RAISE FND_API.G_EXC_ERROR;
2848 END IF;
2849
2850 END IF;
2851
2852
2853 /* Setting the pick slip number */
2854
2855 GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip
2856 ( p_organization_id => p_mo_line_rec.organization_id
2857 , p_line_id => p_mo_line_rec.line_id
2858 , x_return_status => x_return_status
2859 , x_msg_count => x_msg_count
2860 , x_msg_data => x_msg_data
2861 , x_pick_slip_number => l_pick_slip_number
2862 );
2863
2864 GMI_reservation_Util.PrintLn('pick slip number is '|| l_pick_slip_number);
2865
2866 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
2867 THEN
2868 GMI_reservation_Util.PrintLn('(opm_dbg) in Set_Pick_lots: Error returned by GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip.', 'pick_lots.log');
2869 FND_MESSAGE.SET_NAME('GMI','GMI_ERROR');
2870 FND_MESSAGE.Set_Token('BY_PROC', 'GMI_Pick_Release_Util.Create_Manual_Alloc_Pickslip');
2871 FND_MESSAGE.Set_Token('WHERE', 'Set_Pick_Lots');
2872 FND_MSG_PUB.Add;
2873 RAISE FND_API.G_EXC_ERROR;
2874 END IF;
2875
2876 GMI_reservation_Util.PrintLn('l_tran_row.trans_id '||l_tran_row.trans_id );
2877 UPDATE ic_tran_pnd
2878 SET pick_slip_number = l_pick_slip_number
2879 WHERE trans_id = l_tran_row.trans_id;
2880
2881 x_tran_row := l_tran_row;
2882 END IF; /* IF (x_available_qty <> 0) */
2883 END IF; /* IF GMI_TRAN_PND_DB_PVT.FETCH_IC_TRAN_PND */
2884
2885 EXCEPTION
2886
2887 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2889
2890 FND_MSG_PUB.Count_And_Get
2891 ( p_count => x_msg_count
2892 , p_data => x_msg_data
2893 );
2894 WHEN OTHERS THEN
2895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2896
2897 FND_MSG_PUB.Count_And_Get
2898 ( p_count => x_msg_count
2899 , p_data => x_msg_data
2900 );
2901
2902 END BALANCE_NONCTL_INV_TRAN;
2903
2904
2905
2906 /* this procedure is called only from ICTOTRX.fmb where the private
2907 * API would be shielded from the form call */
2908 PROCEDURE FORM_PICK_CONFIRM
2909 (
2910 p_api_version_number IN NUMBER
2911 , p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
2912 , p_validation_flag IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
2913 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2914 , p_mo_line_tbl IN GMI_Move_Order_Global.MO_LINE_TBL
2915 , x_mo_line_tbl OUT NOCOPY GMI_Move_Order_Global.MO_LINE_TBL
2916 , x_return_status OUT NOCOPY VARCHAR2
2917 , x_msg_count OUT NOCOPY NUMBER
2918 , x_msg_data OUT NOCOPY VARCHAR2
2919 , p_manual_pick IN VARCHAR2 DEFAULT NULL
2920 )
2921 IS
2922 BEGIN
2923 GMI_Pick_Wave_Confirm_PVT.Pick_Confirm(
2924 p_api_version_number => 1.0,
2925 p_init_msg_lst => FND_API.G_FALSE,
2926 p_validation_flag => FND_API.G_VALID_LEVEL_FULL,
2927 p_commit => p_commit,
2928 p_delivery_detail_id => null,
2929 p_mo_line_tbl => p_mo_line_tbl,
2930 x_mo_line_tbl => x_mo_line_tbl,
2931 x_return_status => x_return_status,
2932 x_msg_data => x_msg_data,
2933 x_msg_count => x_msg_count);
2934
2935 END form_pick_confirm;
2936
2937 -- Added this procedure for bug 3776538
2938 PROCEDURE truncate_trans_qty
2939 (
2940 p_line_id IN number,
2941 p_delivery_detail_id IN number,
2942 p_default_location IN varchar2,
2943 is_lot_loct_ctl IN boolean,
2944 x_return_status OUT NOCOPY VARCHAR2
2945 ) IS
2946
2947 CURSOR lot_loct_ctl_trans
2948 IS
2949 SELECT trans_id, trans_qty, trans_qty2
2950 FROM IC_TRAN_PND tran,
2951 IC_LOTS_MST lots,
2952 IC_LOCT_MST loct
2953 WHERE lots.lot_id = tran.lot_id
2954 AND lots.item_id = tran.item_id
2955 AND lots.delete_mark = 0
2956 AND tran.line_id = p_line_id
2957 AND (tran.lot_id > 0 OR tran.location <> p_default_location )
2958 AND tran.doc_type = 'OMSO'
2959 AND tran.staged_ind = 0
2960 AND tran.completed_ind = 0
2961 AND tran.delete_mark = 0
2962 AND loct.delete_mark(+) = 0
2963 AND loct.whse_code (+) = tran.whse_code
2964 AND loct.location (+) = tran.location
2965 AND tran.line_detail_id = p_delivery_detail_id
2966 FOR UPDATE OF trans_qty, trans_qty2 NOWAIT;
2967
2968 l_TRUNCATE_TO_LENGTH CONSTANT INTEGER := 5;
2969
2970 BEGIN
2971 x_return_status := FND_API.G_RET_STS_SUCCESS;
2972 GMI_Reservation_Util.PrintLn('Inside truncate_trans_qty');
2973 GMI_Reservation_Util.PrintLn('Parameters- p_line_id:'||p_line_id||', p_delivery_detail_id:'||p_delivery_detail_id||', p_default_location:'||p_default_location);
2974 IF (is_lot_loct_ctl = TRUE) THEN
2975 FOR lot_loct_ctl_trans_rec in lot_loct_ctl_trans
2976 LOOP
2977 update ic_tran_pnd
2978 set trans_qty = trunc (trans_qty, l_TRUNCATE_TO_LENGTH),
2979 trans_qty2= trunc (trans_qty2, l_TRUNCATE_TO_LENGTH)
2980 where current of lot_loct_ctl_trans;
2981 GMI_Reservation_Util.PrintLn('Truncated transaction with trans_id: '||lot_loct_ctl_trans_rec.trans_id||' to '||l_TRUNCATE_TO_LENGTH||'th decimal');
2982 END LOOP;
2983 END IF;
2984
2985 EXCEPTION
2986 WHEN OTHERS THEN
2987 gmi_reservation_util.println(SQLERRM);
2988 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2989 END truncate_trans_qty;
2990
2991 PROCEDURE dump_shp_attrb_data (p_shipping_attr IN WSH_INTERFACE.ChangedAttributeTabType) IS
2992 BEGIN
2993 gmi_reservation_util.println('Writing Data from dump_shp_attrb_data');
2994 GMI_Reservation_Util.PrintLn('Delivery Detail ID - '||p_shipping_attr(1).delivery_detail_id);
2995 GMI_Reservation_Util.PrintLn('Line ID - '||p_shipping_attr(1).source_line_id);
2996 GMI_Reservation_Util.PrintLn('Action Flag - '||p_shipping_attr(1).action_flag);
2997 GMI_Reservation_Util.PrintLn('ordered_quantity1 - '||p_shipping_attr(1).ordered_quantity);
2998 GMI_Reservation_Util.PrintLn('ordered_quantity2 - '||p_shipping_attr(1).ordered_quantity2);
2999 GMI_Reservation_Util.PrintLn('Backorder quantity1 - '||p_shipping_attr(1).cycle_count_quantity);
3000 GMI_Reservation_Util.PrintLn('Backorder quantity2 - '||p_shipping_attr(1).cycle_count_quantity2);
3001 GMI_Reservation_Util.PrintLn('Released Status - '||p_shipping_attr(1).released_status);
3002 GMI_Reservation_Util.PrintLn('picked_quantity1 - '||p_shipping_attr(1).picked_quantity);
3003 GMI_Reservation_Util.PrintLn('picked_quantity2 - '||p_shipping_attr(1).picked_quantity2);
3004 GMI_Reservation_Util.PrintLn('pending_quantity1 - '||p_shipping_attr(1).pending_quantity);
3005 GMI_Reservation_Util.PrintLn('pending_quantity2 - '||p_shipping_attr(1).pending_quantity2);
3006 END dump_shp_attrb_data;
3007
3008
3009 END GMI_PICK_WAVE_CONFIRM_PVT;