1 PACKAGE BODY INV_TABLE_LOCK_PVT AS
2 /* $Header: INVLOCKB.pls 120.6.12010000.2 2009/05/15 22:35:43 musinha ship $ */
3
4 FUNCTION lock_onhand_records (
5 p_organization_id IN NUMBER
6 , p_inventory_item_id IN NUMBER
7 , p_revision IN VARCHAR2
8 , p_lot IN VARCHAR2
9 , p_subinventory IN VARCHAR2
10 , p_locator IN VARCHAR2
11 , p_issue_receipt IN NUMBER
12 , p_header_id IN NUMBER) RETURN BOOLEAN IS
13 l_lock_handle VARCHAR2(128);
14 l_lock_name VARCHAR2(2000);
15 l_status NUMBER;
16 l_neg_exists NUMBER;
17 l_sessionid NUMBER;
18 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
19 BEGIN
20 l_status := 9;
21 l_lock_name := 'INV_MOQ_' || p_organization_id || '_' ||
22 p_inventory_item_id || '_' ||
23 p_revision || '_' ||
24 p_lot || '_' ||
25 p_subinventory || '_' ||
26 p_locator;
27 if (p_issue_receipt = 0) then
28 -- Receipt 0 Issue 1
29 SELECT count(1) into l_neg_exists
30 FROM mtl_onhand_quantities_detail
31 WHERE organization_id = p_organization_id
32 AND inventory_item_id = p_inventory_item_id
33 AND subinventory_code like p_subinventory
34 AND nvl(lot_number,'@@@') like nvl(p_lot,'@@@')
35 AND nvl(revision,'@@@') like nvl(p_revision,'@@@')
36 AND nvl(locator_id,-999) = nvl(p_locator,-999)
37 AND primary_transaction_quantity < 0
38 AND rownum < 2;
39 end if; -- Receipt part
40
41 if ((p_issue_receipt = 0 AND l_neg_exists > 0)
42 OR p_issue_receipt = 1) then
43 get_lock_handle(p_header_id,l_lock_name,l_lock_handle);
44
45 /* select USERENV('SESSIONID') into l_sessionid from dual;
46 inv_log_util.trace('Session is.. ' ||l_sessionid , 'INV_TABLE_LOCK_PVT', 9); */
47
48 IF (l_debug = 1 ) THEN /* Bug#5401181*/
49 inv_log_util.trace('Lock Name is...:' ||l_lock_name , 'INV_TABLE_LOCK_PVT', 9);
50 inv_log_util.trace('Lock Handle is...:' ||l_lock_handle , 'INV_TABLE_LOCK_PVT', 9);
51 END IF;
52
53 l_status := dbms_lock.request(
54 lockhandle => l_lock_handle
55 ,lockmode => dbms_lock.x_mode
56 ,timeout => dbms_lock.maxwait
57 ,release_on_commit => TRUE);
58 end if;
59 -- l_status = 4.No need to insert into temp table when lock is already
60 -- owned by this session.it will become duplicate entry if we insert
61 -- and release lock may fail in this case for the second time.
62
63 -- moving the insert stmt to the below procedure.So that we can keep the
64 -- lock handle till the end.Otherewise any commit or rollback will remove
65 -- the data in temp table.
66 /* if (l_status = 0) then
67 insert into mtl_onhand_lock_temp(LOCK_HANDLE) values(l_lock_handle);
68 end if; */
69 if (l_status <> 0 AND l_status <> 4) then
70 --Bug 6520517, changed AND to OR to avoid deadlock
71 if (l_status = 1 OR l_status = 2) then
72 RETURN FALSE;
73 end if;
74 end if;
75 return TRUE;
76 EXCEPTION
77 WHEN OTHERS THEN
78 return FALSE;
79 END;
80
81 PROCEDURE get_lock_handle (
82 p_header_id IN NUMBER
83 , p_lock_name IN VARCHAR2
84 , x_lock_handle OUT NOCOPY VARCHAR2) IS
85 PRAGMA AUTONOMOUS_TRANSACTION;
86 BEGIN
87 dbms_lock.allocate_unique(
88 lockname => p_lock_name
89 , lockhandle => x_lock_handle);
90 INSERT INTO mtl_onhand_lock_temp(
91 lock_handle
92 , header_id)
93 VALUES (
94 x_lock_handle
95 , p_header_id);
96 COMMIT;
97 END get_lock_handle;
98
99 -- Bug 6636261: Acquiring lock for a row in MLN
100 PROCEDURE lock_lot_record ( p_organization_id IN NUMBER
101 ,p_inventory_item_id IN NUMBER
102 ,p_lot IN VARCHAR2 ) IS
103
104 x_lock_handle VARCHAR2(128);
105 l_lock_name VARCHAR2(2000);
106 l_status NUMBER;
107 BEGIN
108 l_lock_name := 'INV_MLN_' || p_organization_id || '_' || p_inventory_item_id || '_' || p_lot;
109
110 get_lot_lock_handle( l_lock_name, x_lock_handle);
111
112 l_status := dbms_lock.request(
113 lockhandle => x_lock_handle
114 ,lockmode => dbms_lock.x_mode
115 ,timeout => dbms_lock.maxwait
116 ,release_on_commit => TRUE);
117 END;
118
119 -- Bug 6636261: Acquiring lock handle for a row in MLN
120 PROCEDURE get_lot_lock_handle ( p_lock_name IN VARCHAR2
121 ,x_lock_handle OUT NOCOPY VARCHAR2 ) IS
122 PRAGMA AUTONOMOUS_TRANSACTION;
123
124 BEGIN
125
126 dbms_lock.allocate_unique(
127 lockname => p_lock_name
128 ,lockhandle => x_lock_handle);
129
130 END;
131
132
133
134 PROCEDURE release_locks IS
135 TYPE CHAR_TABLE is TABLE OF VARCHAR2(200);
136 table_handle CHAR_TABLE;
137 l_ret_status NUMBER;
138 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
139 BEGIN
140 IF (l_debug = 1 ) THEN /* Bug#5401181*/
141 inv_log_util.trace('Begin release locks.. ' , 'INV_TABLE_LOCK_PVT', 9);
142 END IF;
143 select lock_handle bulk collect into table_handle from mtl_onhand_lock_temp;
144 if (table_handle IS NULL OR table_handle.COUNT = 0) THEN
145 IF (l_debug = 1 ) THEN/* Bug#5401181*/
146 inv_log_util.trace('No user locks to Release' , 'INV_TABLE_LOCK_PVT', 9);
147 END IF;
148 else
149 for i in table_handle.FIRST .. table_handle.LAST loop
150 l_ret_status := dbms_lock.release(table_handle(i));
151 if l_ret_status = 0 then
152 IF (l_debug = 1 ) THEN /* Bug#5401181*/
153 inv_log_util.trace('Lock released successfully' , 'INV_TABLE_LOCK_PVT', 9);
154 END IF;
155 else
156 IF (l_debug = 1 ) THEN/* Bug#5401181*/
157 inv_log_util.trace('Error in releasing the lock'||l_ret_status, 'INV_TABLE_LOCK_PVT', 9);
158 END IF;
159 end if;
160 end loop;
161 DELETE MTL_ONHAND_LOCK_TEMP;
162 end if;
163 END release_locks;
164
165 --Bug #4338316
166 --If p_commit is 0 (true) then commit, delete the records and user locks
167 --of p_commit 1 (false) then only delete the records.
168 PROCEDURE release_locks(
169 p_header_id IN NUMBER
170 , p_commit IN NUMBER DEFAULT 0) IS
171 TYPE char_table IS TABLE OF VARCHAR2(200);
172 table_handle char_table;
173 l_ret_status NUMBER;
174 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
175 BEGIN
176 IF (l_debug = 1 ) THEN /* Bug#5401181*/
177 inv_log_util.TRACE('Begin release locks for header id.. ' || p_header_id, 'INV_TABLE_LOCK_PVT', 9);
178 END IF;
179
180 IF (p_commit = 1) THEN
181 -- should not delete the temp rows for wip move trx's are this will
182 -- be done BY wip move worker when they call the release_locks api.
183 IF (wip_constants.wip_move_worker <> 'Y') THEN
184 IF (l_debug = 1) /* Bug#5401181*/ THEN
185 inv_log_util.TRACE('In rel locks,wip_move= .. ' || wip_constants.wip_move_worker, 'INV_TABLE_LOCK_PVT', 9);
186 END IF;
187
188 DELETE mtl_onhand_lock_temp
189 WHERE header_id = p_header_id;
190 END IF;
191 ELSE
192 SELECT lock_handle
193 BULK COLLECT INTO table_handle
194 FROM mtl_onhand_lock_temp
195 WHERE header_id = p_header_id;
196
197 IF (table_handle IS NULL OR table_handle.COUNT = 0) THEN
198 IF (l_debug = 1 ) THEN/* Bug#5401181*/
199 inv_log_util.TRACE('No user locks to Release', 'INV_TABLE_LOCK_PVT', 9);
200 END IF;
201 ELSE
202 FOR i IN table_handle.FIRST .. table_handle.LAST LOOP
203 l_ret_status := DBMS_LOCK.release(table_handle(i));
204
205 IF l_ret_status = 0 THEN
206 IF (l_debug = 1 ) THEN/* Bug#5401181*/
207 inv_log_util.TRACE('Lock released successfully', 'INV_TABLE_LOCK_PVT', 9);
208 END IF;
209 ELSE
210 IF (l_debug = 1 ) THEN /* Bug#5401181*/
211 inv_log_util.TRACE('Error in releasing the lock' || l_ret_status, 'INV_TABLE_LOCK_PVT', 9);
212 END IF;
213 END IF;
214 END LOOP;
215
216 DELETE mtl_onhand_lock_temp
217 WHERE header_id = p_header_id;
218 END IF;
219 END IF;
220 END release_locks;
221
222
223 END INV_TABLE_LOCK_PVT;
[Home] [Help]