1 PACKAGE BODY INV_TABLE_LOCK_PVT AS
2 /* $Header: INVLOCKB.pls 120.6 2007/12/21 23:05:00 yssingh 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 PROCEDURE release_locks IS
100 TYPE CHAR_TABLE is TABLE OF VARCHAR2(200);
101 table_handle CHAR_TABLE;
102 l_ret_status NUMBER;
103 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
104 BEGIN
105 IF (l_debug = 1 ) THEN /* Bug#5401181*/
106 inv_log_util.trace('Begin release locks.. ' , 'INV_TABLE_LOCK_PVT', 9);
107 END IF;
108 select lock_handle bulk collect into table_handle from mtl_onhand_lock_temp;
109 if (table_handle IS NULL OR table_handle.COUNT = 0) THEN
110 IF (l_debug = 1 ) THEN/* Bug#5401181*/
111 inv_log_util.trace('No user locks to Release' , 'INV_TABLE_LOCK_PVT', 9);
112 END IF;
113 else
114 for i in table_handle.FIRST .. table_handle.LAST loop
115 l_ret_status := dbms_lock.release(table_handle(i));
116 if l_ret_status = 0 then
117 IF (l_debug = 1 ) THEN /* Bug#5401181*/
118 inv_log_util.trace('Lock released successfully' , 'INV_TABLE_LOCK_PVT', 9);
119 END IF;
120 else
121 IF (l_debug = 1 ) THEN/* Bug#5401181*/
122 inv_log_util.trace('Error in releasing the lock'||l_ret_status, 'INV_TABLE_LOCK_PVT', 9);
123 END IF;
124 end if;
125 end loop;
126 DELETE MTL_ONHAND_LOCK_TEMP;
127 end if;
128 END release_locks;
129
130 --Bug #4338316
131 --If p_commit is 0 (true) then commit, delete the records and user locks
132 --of p_commit 1 (false) then only delete the records.
133 PROCEDURE release_locks(
134 p_header_id IN NUMBER
135 , p_commit IN NUMBER DEFAULT 0) IS
136 TYPE char_table IS TABLE OF VARCHAR2(200);
137 table_handle char_table;
138 l_ret_status NUMBER;
139 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
140 BEGIN
141 IF (l_debug = 1 ) THEN /* Bug#5401181*/
142 inv_log_util.TRACE('Begin release locks for header id.. ' || p_header_id, 'INV_TABLE_LOCK_PVT', 9);
143 END IF;
144
145 IF (p_commit = 1) THEN
146 -- should not delete the temp rows for wip move trx's are this will
147 -- be done BY wip move worker when they call the release_locks api.
148 IF (wip_constants.wip_move_worker <> 'Y') THEN
149 IF (l_debug = 1) /* Bug#5401181*/ THEN
150 inv_log_util.TRACE('In rel locks,wip_move= .. ' || wip_constants.wip_move_worker, 'INV_TABLE_LOCK_PVT', 9);
151 END IF;
152
153 DELETE mtl_onhand_lock_temp
154 WHERE header_id = p_header_id;
155 END IF;
156 ELSE
157 SELECT lock_handle
158 BULK COLLECT INTO table_handle
159 FROM mtl_onhand_lock_temp
160 WHERE header_id = p_header_id;
161
162 IF (table_handle IS NULL OR table_handle.COUNT = 0) THEN
163 IF (l_debug = 1 ) THEN/* Bug#5401181*/
164 inv_log_util.TRACE('No user locks to Release', 'INV_TABLE_LOCK_PVT', 9);
165 END IF;
166 ELSE
167 FOR i IN table_handle.FIRST .. table_handle.LAST LOOP
168 l_ret_status := DBMS_LOCK.release(table_handle(i));
169
170 IF l_ret_status = 0 THEN
171 IF (l_debug = 1 ) THEN/* Bug#5401181*/
172 inv_log_util.TRACE('Lock released successfully', 'INV_TABLE_LOCK_PVT', 9);
173 END IF;
174 ELSE
175 IF (l_debug = 1 ) THEN /* Bug#5401181*/
176 inv_log_util.TRACE('Error in releasing the lock' || l_ret_status, 'INV_TABLE_LOCK_PVT', 9);
177 END IF;
178 END IF;
179 END LOOP;
180
181 DELETE mtl_onhand_lock_temp
182 WHERE header_id = p_header_id;
183 END IF;
184 END IF;
185 END release_locks;
186
187
188 END INV_TABLE_LOCK_PVT;
[Home] [Help]