[Home] [Help]
PACKAGE BODY: APPS.WMS_OVERPICK
Source
1 PACKAGE BODY wms_overpick AS
2 /* $Header: WMSOPICB.pls 120.1 2005/06/20 05:05:06 appldev ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_OVERPICK';
5
6
7 -- to turn off debugger, comment OUT NOCOPY /* file.sql.39 change */ the line 'dbms_output.put_line(msg);'
8 PROCEDURE mdebug(msg in varchar2)
9 IS
10 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11 BEGIN
12 --dbms_output.put_line('Jef: '||msg);
13 null;
14 END;
15
16 -- This API queries the quantity_tree to find OUT NOCOPY /* file.sql.39 change */ whether there is
17 -- sufficient quantity to be picked in a locator . If suffienct, the API
18 -- returns x_ret=1, otherwise x_ret=0. The API also returns the avail to transact
19 -- qty in the same uom it was called (p_uom)
20
21
22 PROCEDURE validate_overpick
23 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2, -- return status (success/error/unexpected_error)
24 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER, -- number of messages in the message queue
25 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2, -- message text when x_msg_count>0
26 x_ret OUT NOCOPY /* file.sql.39 change */ NUMBER, -- returns 1 if p_qty > quantity from qty_tree
27 -- otherwise returns 0
28 x_att OUT NOCOPY /* file.sql.39 change */ NUMBER, -- quantity that is avail to transact
29 p_temp_id IN NUMBER, -- transaction_temp_id
30 p_qty IN NUMBER, -- quantity requested
31 p_uom IN VARCHAR2 -- unit of measure
32 )
33 IS
34 l_msg_count NUMBER;
35 l_msg_data VARCHAR2(250);
36
37 -- local variables that store values from MMTT
38 l_org_id NUMBER;
39 l_sub VARCHAR2(10);
40 l_loc_id NUMBER;
41 l_item_id NUMBER;
42 l_revision VARCHAR2(3);
43 l_lot VARCHAR2(30);
44 l_primary_uom VARCHAR2(3);
45
46 -- local variables needed to call quantity tree and store values from MSI
47 l_return_status varchar2(1);
48 l_lot_control_code NUMBER;
49 l_revision_qty_control_code NUMBER;
50 l_serial_number_control_code NUMBER;
51 l_is_lot_control BOOLEAN;
52 l_is_revision_control BOOLEAN;
53 l_is_serial_control BOOLEAN;
54 l_qoh NUMBER; -- qty onhand
55 l_rqoh NUMBER; -- qty onhand that is reservable
56 l_qr NUMBER; -- qty reserverd
57 l_qs NUMBER; -- qty suggested
58 l_att NUMBER; -- qty that is avail to transact
59 l_atr NUMBER; -- qty that is avail to reserve (x_atr=x_rqoh - x_qr - x_qs)
60
61
62
63 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
64 BEGIN
65
66 -- Initialize API return status to success
67 x_return_status := FND_API.G_RET_STS_SUCCESS;
68
69 -- Initialize x_ret to zero
70 x_ret := 0;
71
72 -- get input parameters for quantity tree
73 SELECT organization_id, subinventory_code, locator_id, inventory_item_id, revision, lot_number
74 INTO l_org_id, l_sub, l_loc_id, l_item_id, l_revision, l_lot
75 FROM mtl_material_transactions_temp
76 WHERE transaction_temp_id = p_temp_id;
77
78 SELECT lot_control_code, revision_qty_control_code, serial_number_control_code, primary_uom_code
79 INTO l_lot_control_code, l_revision_qty_control_code, l_serial_number_control_code, l_primary_uom
80 from mtl_system_items
81 where inventory_item_id = l_item_id AND organization_id = l_org_id;
82
83 -- check whether item is lot controlled from mtl_system_items
84 IF ( (l_lot_control_code = NULL) OR (l_lot_control_code = 1))
85 THEN l_is_lot_control := FALSE;
86 ELSE l_is_lot_control := TRUE;
87 END IF;
88
89 -- check whether item is revision controlled from mtl_system_items
90 IF ( (l_revision_qty_control_code = NULL) OR (l_revision_qty_control_code = 1))
91 THEN l_is_revision_control := FALSE;
92 ELSE l_is_revision_control := TRUE;
93 END IF;
94
95 -- check whether item is serial controlled from mtl_system_items
96 IF ( (l_serial_number_control_code = NULL) OR (l_serial_number_control_code = 1))
97 THEN l_is_serial_control := FALSE;
98 ELSE l_is_serial_control := TRUE;
99 END IF;
100
101
102 inv_quantity_tree_pub.query_quantities
103 ( p_api_version_number => 1.0
104 , x_return_status => l_return_status
105 , x_msg_count => l_msg_count
106 , x_msg_data => l_msg_data
107 , p_organization_id => l_org_id
108 , p_inventory_item_id => l_item_id
109 , p_tree_mode => 2 -- for transaction mode
110 , p_is_revision_control => l_is_revision_control
111 , p_is_lot_control => l_is_lot_control
112 , p_is_serial_control => l_is_serial_control
113 , p_revision => l_revision
114 , p_lot_number => l_lot
115 , p_subinventory_code => l_sub
116 , p_locator_id => l_loc_id
117 , x_qoh => l_qoh -- quantity onhand
118 , x_rqoh => l_rqoh -- qty onhand that is reservable
119 , x_qr => l_qr -- qty reserved
120 , x_qs => l_qs -- qty suggested
121 , x_att => l_att -- qty that is avail to transact
122 , x_atr => l_atr -- qty that is avail to reserve
123 -- (x_atr=x_rqoh - x_qr - x_qs)
124
125 );
126
127 IF (l_debug = 1) THEN
128 mdebug('In validate_pick, x_att: ' || l_att);
129 END IF;
130 -- convert l_att (quantity that is avail to transact) into the same UOM as
131 -- the input quantity p_qty
132 x_att :=
133 inv_convert.inv_um_convert( item_id => l_item_id,
134 precision => null,
135 from_quantity => l_att,
136 from_unit => l_primary_uom,
137 to_unit => p_uom,
138 from_name => null,
139 to_name => null);
140
141 IF (l_debug = 1) THEN
142 mdebug('In validate_pick, x_converted_att: ' || x_att);
143 END IF;
144 -- if p_qty is greater than the avail to transact qty, return 1,
145 -- otherwise return zero
146 IF (l_debug = 1) THEN
147 mdebug('p_qty: ' || p_qty);
148 END IF;
149 IF p_qty > x_att
150 THEN x_ret := 1;
151 ELSE x_ret := 0;
152 END IF;
153
154 END validate_overpick;
155 END wms_overpick ;