1 PACKAGE BODY GMI_LOCKS AS
2 /* $Header: GMIULCKB.pls 115.9 2004/01/08 21:14:44 adeshmuk ship $ */
3
4 /* ========================================================================
5 * This file contains the body of a package which
6 * locks rows in ic_loct_inv. It is intended to be used by the allocation
7 * logic in the OM/OPM integration. At the start of an allocation session
8 * for a particular item/whse/lot/location, the procedures in this package
9 * prevent users from accessing the inventory simultaneously whilst it is
10 * being allocated. This prevents multiple allocation of the same stock and
11 * eliminates deadlocks between sessions
12 *
13 * For convenience the lock_inventory procedure is overloaded so the same
14 * routine can be called all the time, just by specifying the desired
15 * parameter set. Choose from:
16 *
17 * Item
18 * Item, Warehouse
19 * Item, Warehouse, Lot, Lot Status
20 * Item, Warehouse, Location
21 * Item, Warehouse, Lot, Lot Status, Location
22 *
23 * These versions return a status value as follows:
24 *
25 * TRUE => Inventory rows locked successfully
26 * FALSE => Inventory rows locked by another user, try later
27 *
28 *
29 * Another variant exists for use in situations where the calling code needs
30 * a bit more control over what's happening. The parameter set here is:
31 *
32 * Item, Warehouse, Lot, Lot Status, Location, Retries
33 *
34 * where the 'retries' parameter is the number of times to attempt
35 * the locking (which
36 * will take place at 1 second intervals) before giving up.
37 * For this version of the procedure the return status can be
38 *
39 * TRUE => Inventory rows locked siccessfully
40 * FALSE => Inventory rows locked by another user, try later
41 * NULL => No rows locked because none matched the parameters
42 *
43 * The final (NULL) return could be significant in some situations.
44 * The other versions
45 * of the procedure will return TRUE in this case.
46 *
47 *
48 *
49 * All calls to this package must specify an item_id at the very least.
50 * The other values
51 * can be passed as NULL.
52 * If a warehouse is passed as NULL, locking could take place across
53 * warehouses.
54 * If lot_id is NULL but lot_status is not, all lots of the item with this
55 * status will be locked, possibly across warehouses if that too is NULL.
56 * The possibilities are numerous.
57 *
58 *
59 * HISTORY
60 *
61 * 02-Mar-2000 P.J.Schofield, Oracle UK
62 * Package created.
63 * 28-Oct-2002 J.DiIorio 11.5.1J Bug#2643440 - added nocopy.
64 *
65 * 16-Dec-2003 A. Mundhe Bug 3303486
66 * Modified the code to remove the usage of NVL to help improve performance.
67 *
68 * 07-Jan-2004 A. Mundhe Bug 3356201
69 * Added new overloaded lock_inventory proceudre to handle - Item,
70 * Warehouse, Lot and location parameter set.
71 * ============================================================================*/
72
73 /* Global variables */
74 G_PKG_NAME CONSTANT VARCHAR2(30):='GMI_Locks';
75
76 /* This procedure is private to the package. */
77
78 PROCEDURE lock_rows
79 (
80 i_item_id IN ic_loct_inv.item_id%TYPE,
81 i_whse_code IN ic_loct_inv.whse_code%TYPE,
82 i_lot_id IN ic_loct_inv.lot_id%TYPE,
83 i_lot_status IN ic_loct_inv.lot_status%TYPE,
84 i_location IN ic_loct_inv.location%TYPE,
85 o_lock_status OUT NOCOPY BOOLEAN
86 )
87 IS
88
89 row_count NUMBER;
90 statement VARCHAR2(1000);
91 TYPE ref_cursor_type IS REF CURSOR;
92 cursor_handle ref_cursor_type;
93 l_where VARCHAR2(3000):= NULL;
94 BEGIN
95 -- Bug 3303486
96 -- Removed the usage of NVL and built the where clause dynamically.
97 IF i_whse_code IS NOT NULL THEN
98 l_where := l_where || ' AND :i_whse_code = whse_code ';
99 ELSE
100 l_where := l_where || ' AND :i_whse_code IS NULL ';
101 END IF;
102
103 IF i_location IS NOT NULL THEN
104 l_where := l_where || ' AND :i_location = location ';
105 ELSE
106 l_where := l_where || ' AND :i_location IS NULL ';
107 END IF;
108
109 IF i_lot_id IS NOT NULL THEN
110 l_where := l_where || ' AND :i_lot_id = lot_id ';
111 ELSE
112 l_where := l_where || ' AND :i_lot_id IS NULL ';
113 END IF;
114
115 IF i_lot_status IS NOT NULL THEN
116 l_where := l_where || ' AND :i_lot_status = lot_status ';
117 ELSE
118 l_where := l_where || ' AND :i_lot_status IS NULL ';
119 END IF;
120
121 statement :=
122 'SELECT 1 '
123 || 'FROM ic_loct_inv '
124 || 'WHERE item_id=:i_item_id '
125 || l_where
126 || ' FOR UPDATE NOWAIT ';
127 OPEN cursor_handle FOR statement
128 USING i_item_id, i_whse_code, i_location, i_lot_id, i_lot_status;
129
130 FETCH cursor_handle INTO row_count;
131
132 IF cursor_handle%NOTFOUND
133 THEN
134 o_lock_status := NULL;
135 ELSE
136 o_lock_status := TRUE;
137 END IF;
138
139 CLOSE cursor_handle;
140
141 EXCEPTION
142 WHEN OTHERS
143 THEN
144 /* DBMS_OUTPUT.PUT_LINE('Return Value was '||to_char(SQLCODE)); */
145 o_lock_status := FALSE;
146 END;
147
148 /* All other procedures are public: */
149
150 /* Variant #1 Lock all inventory of the specified item */
151
152 PROCEDURE lock_inventory
153 (
154 i_item_id IN ic_loct_inv.item_id%TYPE,
155 o_lock_status OUT NOCOPY BOOLEAN
156 )
157 IS
158 i_lock_status BOOLEAN;
159 BEGIN
160 GMI_LOCKS.lock_inventory
161 ( i_item_id => i_item_id,
162 i_whse_code => NULL,
163 i_lot_id => NULL,
164 i_lot_status => NULL,
165 i_location => NULL,
166 i_attempts => 5,
167 o_lock_status => i_lock_status);
168
169 o_lock_status := NVL(i_lock_status, TRUE);
170 END;
171
172 /* Variant #2 Lock all inventory of the item in the warehouse specified. */
173
174 PROCEDURE lock_inventory
175 (
176 i_item_id IN ic_loct_inv.item_id%TYPE,
177 i_whse_code IN ic_whse_mst.whse_code%TYPE,
178 o_lock_status OUT NOCOPY BOOLEAN
179 )
180 IS
181 i_lock_status BOOLEAN;
182 BEGIN
183 GMI_LOCKS.lock_inventory
184 ( i_item_id => i_item_id,
185 i_whse_code => i_whse_code,
186 i_lot_id => NULL,
187 i_lot_status => NULL,
188 i_location => NULL,
189 i_attempts => 5,
190 o_lock_status => i_lock_status);
191
192 o_lock_status := NVL(i_lock_status, TRUE);
193 END;
194
195 /* Variant #3 Lock the specified lot(s) of the item in the warehouse. */
196
197 PROCEDURE lock_inventory
198 (
199 i_item_id IN ic_loct_inv.item_id%TYPE,
200 i_whse_code IN ic_loct_inv.whse_code%TYPE,
201 i_lot_id IN ic_loct_inv.lot_id%TYPE,
202 i_lot_status IN ic_loct_inv.lot_status%TYPE,
203 o_lock_status OUT NOCOPY BOOLEAN
204 )
205 IS
206 i_lock_status BOOLEAN;
207 BEGIN
208 GMI_LOCKS.lock_inventory
209 ( i_item_id => i_item_id,
210 i_whse_code => i_whse_code,
211 i_lot_id => i_lot_id,
212 i_lot_status => i_lot_status,
213 i_location => NULL,
214 i_attempts => 5,
215 o_lock_status => i_lock_status);
216
217 o_lock_status := NVL(i_lock_status, TRUE);
218 END;
219
220
221 /* Variant #4 Lock inventory in the warehouse and location specified. */
222
223 PROCEDURE lock_inventory
224 (
225 i_item_id IN ic_loct_inv.item_id%TYPE,
226 i_whse_code IN ic_loct_inv.whse_code%TYPE,
227 i_location IN ic_loct_inv.location%TYPE,
228 o_lock_status OUT NOCOPY BOOLEAN
229 )
230 IS
231 i_lock_status BOOLEAN;
232 BEGIN
233 GMI_LOCKS.lock_inventory
234 ( i_item_id => i_item_id,
235 i_whse_code => i_whse_code,
236 i_lot_id => NULL,
237 i_lot_status => NULL,
238 i_location => i_location,
239 i_attempts => 5,
240 o_lock_status => i_lock_status);
241
242 o_lock_status := NVL(i_lock_status, TRUE);
243 END;
244
245 /* Bug 3356201 */
246 /* Variant #5 Lock inventory in the warehouse, lot and location specified. */
247
248 PROCEDURE lock_inventory
249 (
250 i_item_id IN ic_loct_inv.item_id%TYPE,
251 i_whse_code IN ic_loct_inv.whse_code%TYPE,
252 i_lot_id IN ic_loct_inv.lot_id%TYPE,
253 i_location IN ic_loct_inv.location%TYPE,
254 o_lock_status OUT NOCOPY BOOLEAN
255 )
256 IS
257 i_lock_status BOOLEAN;
258 BEGIN
259 GMI_LOCKS.lock_inventory
260 ( i_item_id => i_item_id,
261 i_whse_code => i_whse_code,
262 i_lot_id => i_lot_id,
263 i_lot_status => NULL,
264 i_location => i_location,
265 i_attempts => 5,
266 o_lock_status => i_lock_status);
267
268 o_lock_status := NVL(i_lock_status, TRUE);
269 END;
270
271 /* Variant #6 - Lock inventory in a specific lot and location in the warehouse specified. */
272
273 PROCEDURE lock_inventory
274 (
275 i_item_id IN ic_loct_inv.item_id%TYPE,
276 i_whse_code IN ic_loct_inv.whse_code%TYPE,
277 i_lot_id IN ic_loct_inv.lot_id%TYPE,
278 i_lot_status IN ic_loct_inv.lot_status%TYPE,
279 i_location IN ic_loct_inv.location%TYPE,
280 o_lock_status OUT NOCOPY BOOLEAN
281 )
282 IS
283 i_lock_status BOOLEAN;
284 BEGIN
285 GMI_LOCKS.lock_inventory
286 ( i_item_id => i_item_id,
287 i_whse_code => i_whse_code,
288 i_lot_id => i_lot_id,
289 i_lot_status => i_lot_status,
290 i_location => i_location,
291 i_attempts => 5,
292 o_lock_status => i_lock_status);
293
294 o_lock_status := NVL(i_lock_status, TRUE);
295 END;
296
297 /* Variant #7 - The main code. All of the above public routines call this one and filter the results. */
298
299 PROCEDURE lock_inventory
300 (
301 i_item_id IN ic_loct_inv.item_id%TYPE,
302 i_whse_code IN ic_loct_inv.whse_code%TYPE,
303 i_lot_id IN ic_loct_inv.lot_id%TYPE,
304 i_lot_status IN ic_loct_inv.lot_status%TYPE,
305 i_location IN ic_loct_inv.location%TYPE,
306 i_attempts IN NUMBER,
307 o_lock_status OUT NOCOPY BOOLEAN
308 )
309 IS
310 retry_count NUMBER(4);
311 BEGIN
312
313 FOR retry_count in 1..i_attempts
314 LOOP
315 GMI_LOCKS.lock_rows
316 ( i_item_id => i_item_id,
317 i_whse_code => i_whse_code,
318 i_lot_id => i_lot_id,
319 i_lot_status => i_lot_status,
320 i_location => i_location,
321 o_lock_status => o_lock_status);
322
323 IF NVL(o_lock_status,TRUE) = TRUE
324 THEN
325 RETURN;
326 ELSE
327 DBMS_LOCK.sleep(1);
328 END IF;
329 END LOOP;
330 END;
331
332 END GMI_LOCKS;
[Home] [Help]