[Home] [Help]
1: PACKAGE BODY wms_picking_pkg AS
2: /* $Header: WMSPLPDB.pls 120.46.12020000.5 2013/03/29 13:32:49 raminoch ship $ */
3:
4: g_trace_on NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 2);
5: g_group_sequence_number NUMBER := 1;
6: g_max_group_sequence_number NUMBER := -1; -- Bug#5185031
7: g_period_id NUMBER;
8:
6: g_max_group_sequence_number NUMBER := -1; -- Bug#5185031
7: g_period_id NUMBER;
8:
9: --for UCC128, same as in WMSUCCSB.pls
10: g_gtin_cross_ref_type VARCHAR2(25) := fnd_profile.value('INV:GTIN_CROSS_REFERENCE_TYPE');
11: g_gtin_code_length NUMBER := 14;
12:
13: g_cartonization_ids numset_tabType; --Bug: 7254397 Store LPNs for ClusterPickByLabel
14: g_cartonization_ids_inx NUMBER := 0; --Bug: 7254397
21: g_pick_slip_numbers_inx NUMBER := 0;
22: --Added for Case Picking Project end
23:
24: PROCEDURE mydebug(p_msg IN VARCHAR2, p_api_name IN VARCHAR2) IS
25: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
26: BEGIN
27: IF g_trace_on = 1 THEN
28: inv_mobile_helper_functions.tracelog( p_err_msg => p_msg
29: , p_module => 'WMS_PICKING_PKG.' || p_api_name
38: x_msg_count OUT nocopy NUMBER,
39: x_msg_data OUT nocopy VARCHAR2)
40: IS
41: l_api_name VARCHAR2(30) := 'insert_cartonization_id';
42: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
43: BEGIN
44: IF (l_debug = 1) THEN
45: mydebug('Enter:', l_api_name);
46: mydebug('p_lpn_id: ' || TO_CHAR(p_lpn_id), l_api_name);
59: -- Bug: 7254397
60: FUNCTION list_cartonization_id RETURN numset_t PIPELINED
61: IS
62: l_api_name VARCHAR2(30) := 'list_cartonization_id';
63: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
64: l_count NUMBER;
65: BEGIN
66: IF (l_debug = 1) THEN
67: mydebug('Enter:', l_api_name);
84: x_msg_count OUT nocopy NUMBER,
85: x_msg_data OUT nocopy VARCHAR2)
86: IS
87: l_api_name VARCHAR2(30) := 'clear_cartonization_id';
88: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
89: BEGIN
90: IF (l_debug = 1) THEN
91: mydebug('Enter:', l_api_name);
92: END IF;
113: x_msg_count OUT nocopy NUMBER,
114: x_msg_data OUT nocopy VARCHAR2)
115: IS
116: l_api_name VARCHAR2(30) := 'insert_order_numbers';
117: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
118: BEGIN
119: IF (l_debug = 1) THEN
120: mydebug('Enter:', l_api_name);
121: mydebug('p_order_number: ' || TO_CHAR(p_order_number), l_api_name);
133:
134: FUNCTION list_order_numbers RETURN numset_t PIPELINED
135: IS
136: l_api_name VARCHAR2(30) := 'list_order_numbers';
137: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
138: l_count NUMBER;
139: BEGIN
140: IF (l_debug = 1) THEN
141: mydebug('Enter:', l_api_name);
163: x_msg_count OUT nocopy NUMBER,
164: x_msg_data OUT nocopy VARCHAR2)
165: IS
166: l_api_name VARCHAR2(30) := 'clear_order_numbers';
167: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
168: BEGIN
169: IF (l_debug = 1) THEN
170: mydebug('Enter:', l_api_name);
171: END IF;
186: x_msg_count OUT nocopy NUMBER,
187: x_msg_data OUT nocopy VARCHAR2)
188: IS
189: l_api_name VARCHAR2(30) := 'insert_pick_slip_number';
190: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
191: BEGIN
192: IF (l_debug = 1) THEN
193: mydebug('Enter:', l_api_name);
194: mydebug('p_pick_slip_number: ' || TO_CHAR(p_pick_slip_number), l_api_name);
206:
207: FUNCTION list_pick_slip_numbers RETURN numset_t PIPELINED
208: IS
209: l_api_name VARCHAR2(30) := 'list_pick_slip_numbers';
210: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
211: l_count NUMBER;
212: BEGIN
213: IF (l_debug = 1) THEN
214: mydebug('Enter:', l_api_name);
237: x_msg_count OUT nocopy NUMBER,
238: x_msg_data OUT nocopy VARCHAR2)
239: IS
240: l_api_name VARCHAR2(30) := 'clear_pick_slip_number';
241: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
242: BEGIN
243: IF (l_debug = 1) THEN
244: mydebug('Enter:', l_api_name);
245: END IF;
260:
261:
262: PROCEDURE change_task_to_active(p_transaction_temp_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2) IS
263: --PRAGMA AUTONOMOUS_TRANSACTION;
264: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
265: BEGIN
266: x_return_status := fnd_api.g_ret_sts_success;
267:
268: UPDATE wms_dispatched_tasks
354: l_sql VARCHAR2(20000);
355: l_task_cur t_genref;
356: b_is_cluster_pick BOOLEAN := FALSE;
357: l_cartons_list VARCHAR2(4000) := ' (-999) ';
358: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
359:
360: l_same_lot_rec_cnt NUMBER;
361: l_same_lot_pri_qty NUMBER;
362: l_same_lot_tra_qty NUMBER;
1171: END IF;
1172:
1173: ELSIF (l_move_order_type = inv_globals.g_move_order_pick_wave) THEN
1174:
1175: l_check_overpick_passed := NVL(fnd_profile.VALUE('WSH_OVERPICK_ENABLED'), 'N');
1176:
1177: IF l_debug = 1 THEN
1178: mydebug('OVPK: l_check_overpick_passed for PickWave MO = '||l_check_overpick_passed, l_api_name);
1179: END IF;
1555: FROM mtl_material_transactions_temp t2
1556: WHERE t2.parent_line_id = t1.transaction_temp_id
1557: AND t2.organization_id = t1.organization_id
1558: );
1559: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1560: BEGIN
1561: x_return_status := fnd_api.g_ret_sts_success;
1562: -- For each transaction returned by the cursor update the Child Records and delete the Parent.
1563: FOR v_rec IN c_get_bulk_txn(p_organization_id, p_transfer_lpn_id) LOOP
1646: mfg_orig_drop_rec c_get_mfg_drop_details%ROWTYPE;
1647:
1648: b_multiple_drops BOOLEAN := FALSE;
1649:
1650: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1651: BEGIN
1652: -- This API just update transfer_locator of each MMTT record for
1653: -- passed transfer_lpn_id. In case it fails, MMTT will not be updated
1654: -- and pick drop will continue.
2117: l_mmtt_rowcnt NUMBER;
2118: l_wdt_rowcnt NUMBER;
2119: l_undispatched_picks NUMBER;
2120: l_equipment_serial WMS_DISPATCHED_TASKS.EQUIPMENT_INSTANCE%TYPE;
2121: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2122: l_status NUMBER ; --Bug#5157839.
2123:
2124: /*6009436 Begin */
2125: CURSOR c_fm_to_serial_number IS
2841: AND wtf.task_filter_id = wtfd.task_filter_id;
2842:
2843: l_task_status NUMBER; -- bug 4310093
2844:
2845: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2846: l_api_name VARCHAR2(30) := 'WMS_PICKING_PKG.NEXT_TASK';
2847: --
2848: -- Bug 4722574
2849: --
2926: END IF;
2927: --
2928: -- Start Bug 4722574
2929: --
2930: l_ignore_equipment := NVL(fnd_profile.VALUE('WMS_IGNORE_EQUIPMENT'), 1);
2931: IF l_ignore_equipment = 2 THEN
2932: l_equipment_serial := 'NONE';
2933: END IF;
2934: --
3910: qty_tbl wms_Task_mgmt_pub.TASK_QTY_TBL_TYPE;
3911: l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
3912: l_resultant_task_details wms_Task_mgmt_pub.TASK_DETAIL_TBL_TYPE;
3913: l_resultant_tasks wms_Task_mgmt_pub.TASK_TAB_TYPE;
3914: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
3915:
3916: BEGIN
3917:
3918: mydebug('inside split_mmtt_lpn p_transaction_temp_id' || p_transaction_temp_id, 'split_mmtt_lpn');
4022: l_invalid_quantities EXCEPTION;
4023: l_unexpected_error EXCEPTION;
4024: l_query_task_exception EXCEPTION;
4025:
4026: g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
4027: BEGIN
4028: IF g_debug = 1 THEN
4029: mydebug( 'In Split Task ', 'split_task');
4030: END IF;
4333: l_sum_tbl_transaction_qty NUMBER := 0;
4334: l_sum_tbl_primary_qty NUMBER := 0;
4335: l_remaining_primary_qty NUMBER := 0;
4336: l_remaining_transaction_qty NUMBER := 0;
4337: g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
4338: BEGIN
4339: x_return_status := 'E';
4340: IF g_debug = 1 THEN
4341: mydebug( 'Entered','validate_quantities');
4613: x_msg_data OUT NOCOPY VARCHAR2 ,
4614: x_msg_count OUT NOCOPY VARCHAR2 ) IS
4615:
4616: l_sysdate DATE := SYSDATE;
4617: g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
4618: BEGIN
4619: x_return_status := 'E';
4620: IF g_debug = 1 THEN
4621: mydebug( ' Entered ', 'split_mmtt');
5124: x_msg_data OUT NOCOPY VARCHAR2 ,
5125: x_msg_count OUT NOCOPY VARCHAR2 ) IS
5126:
5127: l_sysdate DATE := SYSDATE;
5128: g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5129: BEGIN
5130: x_return_status := 'E';
5131: IF g_debug = 1 THEN
5132: mydebug( ' Entered ','SPLIT_WDT');
5276: l_lot_control_code NUMBER;
5277: l_serial_control_code NUMBER;
5278: x_lot_return_status VARCHAR2(1);
5279:
5280: g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5281: BEGIN
5282: x_return_status := 'E';
5283: IF g_debug = 1 THEN
5284: mydebug( 'Entered.', 'SPLIT_LOT_SERIAL');
5379: ORDER BY fm_serial_number;
5380:
5381: l_transaction_remaining_qty NUMBER;
5382: l_primary_remaining_qty NUMBER;
5383: g_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5384: BEGIN
5385: x_return_status := 'E';
5386: l_transaction_remaining_qty := p_transaction_qty_to_split;
5387: l_primary_remaining_qty := p_primary_qty_to_split;