1 PACKAGE GMI_LOCKS AUTHID CURRENT_USER AS
2 /* $Header: GMIULCKS.pls 115.6 2004/01/08 21:14:28 adeshmuk ship $
3
4 This file contains the specification of a package which
5 locks rows in ic_loct_inv. It is intended to be used by the allocation
6 logic in the OM/OPM integration. At the start of an allocation session
7 for a particular item/whse/lot/location, the procedures in this package
8 prevent users from accessing the inventory simultaneously whilst it is
9 being allocated. This prevents multiple allocation of the same stock and
10 eliminates deadlocks between sessions
11
12 For convenience the lock_inventory procedure is overloaded so the same
13 routine can be called all the time, just by specifying the desired
14 parameter set. Choose from:
15
16 Item
17 Item, Warehouse
18 Item, Warehouse, Lot, Lot Status
19 Item, Warehouse, Location
20 Item, Warehouse, Lot, Lot Status, Location
21
22 These versions return a status value as follows:
23
24 TRUE => Inventory rows locked successfully
25 FALSE => Inventory rows locked by another user, try later
26
27
28 Another variant exists for use in situations where the calling code needs
29 a bit more control over what's happening. The parameter set here is:
30
31 Item, Warehouse, Lot, Lot Status, Location, Retries
32
33 where the 'retries' parameter is the number of times to attempt the locking (which
34 will take place at 1 second intervals) before giving up.
35 For this version of the procedure the return status can be
36
37 TRUE => Inventory rows locked siccessfully
38 FALSE => Inventory rows locked by another user, try later
39 NULL => No rows locked because none matched the parameters
40
41 The final (NULL) return could be significant in some situations. The other versions
42 of the procedure will return TRUE in this case.
43
44
45
46 All calls to this package must specify an item_id at the very least. The other values
47 can be passed as NULL. If a warehouse is passed as NULL, locking could take place across
48 warehouses. If lot_id is NULL but lot_status is not, all lots of the item with this
49 status will be locked, possibly across warehouses if that too is NULL. The possibilities
50 are numerous.
51
52
53 HISTORY
54
55 02-Mar-2000 P.J.Schofield, Oracle UK
56 Package created.
57 28-Oct-2002 J.DiIorio 11.5.1J Bug#2643440 - added nocopy.
58 */
59 PROCEDURE lock_inventory
60 (
61 i_item_id IN ic_loct_inv.item_id%TYPE,
62 o_lock_status OUT NOCOPY BOOLEAN
63 );
64 PROCEDURE lock_inventory
65 (
66 i_item_id IN ic_loct_inv.item_id%TYPE,
67 i_whse_code IN ic_whse_mst.whse_code%TYPE,
68 o_lock_status OUT NOCOPY BOOLEAN
69 );
70 PROCEDURE lock_inventory
71 (
72 i_item_id IN ic_loct_inv.item_id%TYPE,
73 i_whse_code IN ic_loct_inv.whse_code%TYPE,
74 i_lot_id IN ic_loct_inv.lot_id%TYPE,
75 i_lot_status IN ic_loct_inv.lot_status%TYPE,
76 o_lock_status OUT NOCOPY BOOLEAN
77 );
78 /* Bug 3356201 */
79 PROCEDURE lock_inventory
80 (
81 i_item_id IN ic_loct_inv.item_id%TYPE,
82 i_whse_code IN ic_loct_inv.whse_code%TYPE,
83 i_lot_id IN ic_loct_inv.lot_id%TYPE,
84 i_location IN ic_loct_inv.location%TYPE,
85 o_lock_status OUT NOCOPY BOOLEAN
86 );
87 PROCEDURE lock_inventory
88 (
89 i_item_id IN ic_loct_inv.item_id%TYPE,
90 i_whse_code IN ic_loct_inv.whse_code%TYPE,
91 i_location IN ic_loct_inv.location%TYPE,
92 o_lock_status OUT NOCOPY BOOLEAN
93 );
94 PROCEDURE lock_inventory
95 (
96 i_item_id IN ic_loct_inv.item_id%TYPE,
97 i_whse_code IN ic_loct_inv.whse_code%TYPE,
98 i_lot_id IN ic_loct_inv.lot_id%TYPE,
99 i_lot_status IN ic_loct_inv.lot_status%TYPE,
100 i_location IN ic_loct_inv.location%TYPE,
101 o_lock_status OUT NOCOPY BOOLEAN
102 );
103 PROCEDURE lock_inventory
104 (
105 i_item_id IN ic_loct_inv.item_id%TYPE,
106 i_whse_code IN ic_loct_inv.whse_code%TYPE,
107 i_lot_id IN ic_loct_inv.lot_id%TYPE,
108 i_lot_status IN ic_loct_inv.lot_status%TYPE,
109 i_location IN ic_loct_inv.location%TYPE,
110 i_attempts IN NUMBER,
111 o_lock_status OUT NOCOPY BOOLEAN
112 );
113 END GMI_LOCKS;