[Home] [Help]
PACKAGE BODY: APPS.HR_WIP_LCKS
Source
1 PACKAGE BODY hr_wip_lcks
2 /* $Header: hrwiplck.pkb 115.4 2002/12/10 10:21:23 raranjan ship $ */
3 AS
4 --
5 g_start_state VARCHAR2(5) := 'START';
6 g_query_only_dml_mode VARCHAR2(10) := 'QUERY_ONLY';
7 -- ------------------------------------------------------------------------------
8 -- |-----------------------< chk_lock >-----------------------|
9 -- ------------------------------------------------------------------------------
10 Procedure chk_lock
11 (p_transaction_id IN hr_wip_locks.transaction_id%TYPE
12 ,p_current_user_id IN fnd_user.user_id%TYPE
13 )is
14 l_state hr_wip_transactions.state%TYPE;
15 l_dml_mode hr_wip_transactions.dml_mode%TYPE;
16 cursor csr_state is
17 select state,dml_mode
18 from hr_wip_transactions
19 where transaction_id = p_transaction_id;
20 begin
21 open csr_state;
22 fetch csr_state into l_state,l_dml_mode;
23 If csr_state%notfound
24 then
25 close csr_state;
26 fnd_message.set_name('PER','PER_289671_TXN_INV');
27 fnd_message.raise_error;
28 End if;
29 close csr_state;
30 If l_state <> g_start_state
31 then
32 fnd_message.set_name('PER','PER_289677_LCK_NOT_SAVE');
33 fnd_message.raise_error;
34 End if;
35 --
36 If l_dml_mode = g_query_only_dml_mode
37 then
38 fnd_message.set_name('PER','PER_289678_LCK_NOT_QUERY');
39 fnd_message.raise_error;
40 End if;
41 exception
42 when others then
43 If csr_state%isopen
44 then
45 close csr_state;
46 end if;
47 raise;
48 end chk_lock;
49 --
50 -- ------------------------------------------------------------------------------
51 -- |-----------------------< ins >-----------------------|
52 -- ------------------------------------------------------------------------------
53 Procedure ins
54 (p_transaction_id IN hr_wip_transactions.transaction_id%TYPE
55 ,p_table_name IN hr_wip_locks.table_name%TYPE
56 ,p_primary_key_val1 IN hr_wip_locks.primary_key_val1%TYPE
57 ,p_primary_key_val2 IN hr_wip_locks.primary_key_val2%TYPE
58 ,p_primary_key_val3 IN hr_wip_locks.primary_key_val3%TYPE
59 ,p_primary_key_val4 IN hr_wip_locks.primary_key_val4%TYPE
60 ,p_primary_key_val5 IN hr_wip_locks.primary_key_val5%TYPE
61 ,p_commit IN BOOLEAN
62 ,p_lock_id OUT NOCOPY hr_wip_locks.lock_id%TYPE
63 ,p_locked OUT NOCOPY VARCHAR2
64 ,p_locking_transaction_id OUT NOCOPY hr_wip_transactions.transaction_id%TYPE
65 ,p_locking_lock_id OUT NOCOPY hr_wip_locks.lock_id%TYPE
66 )is
67 l_lock_id hr_wip_locks.lock_id%TYPE;
68 l_err_msg varchar2(240);
69 l_locked varchar2(1);
70 l_locking_id hr_wip_locks.lock_id%TYPE;
71 l_locking_trans_id hr_wip_transactions.transaction_id%TYPE;
72 L_START_TOK_VAL constant number := 12;
73 begin
74 insert into hr_wip_locks
75 (lock_id
76 ,transaction_id
77 ,table_name
78 ,primary_key_val1
79 ,primary_key_val2
80 ,primary_key_val3
81 ,primary_key_val4
82 ,primary_key_val5)
83 VALUES
84 (hr_wip_locks_s.nextval
85 ,p_transaction_id
86 ,p_table_name
87 ,p_primary_key_val1
88 ,p_primary_key_val2
89 ,p_primary_key_val3
90 ,p_primary_key_val4
91 ,p_primary_key_val5)returning lock_id into l_lock_id;
92 If p_commit
93 then
94 p_lock_id := l_lock_id;
95 p_locked := null;
96 p_locking_transaction_id := null;
97 p_locking_lock_id := null;
98 End if;
99 exception
100 when dup_val_on_index then
101 l_err_msg := trim(substr(sqlerrm,L_START_TOK_VAL));
102 If instr(l_err_msg,'HR_WIP_LOCKS_U1') <> 0
103 then
104 fnd_message.set_name('PER','PER_289679_LCK_ID_UNIQUE');
105 fnd_message.raise_error;
106 Elsif instr(l_err_msg,'HR_WIP_LOCKS_U2') <> 0
107 then
108 check_for_lock
109 (p_table_name => p_table_name
110 ,p_primary_key_val1 => p_primary_key_val1
111 ,p_primary_key_val2 => p_primary_key_val2
112 ,p_primary_key_val3 => p_primary_key_val3
113 ,p_primary_key_val4 => p_primary_key_val4
114 ,p_primary_key_val5 => p_primary_key_val5
115 ,p_locked => l_locked
116 ,p_locking_lock_id => l_locking_id
117 ,p_locking_transaction_id => l_locking_trans_id
118 );
119 If l_locked = 'Y'
120 then
121 If p_transaction_id = l_locking_trans_id
122 then
123 p_lock_id := l_locking_id;
124 p_locked := 'N';
125 p_locking_transaction_id := null;
126 p_locking_lock_id := null;
127 Else
128 p_lock_id := null;
129 p_locked := 'Y';
130 p_locking_transaction_id := l_locking_trans_id;
131 p_locking_lock_id := l_locking_id;
132 End if;
133 Else -- if l_locked is N
134 raise;
135 End if;
136 Else -- other than constraint error occurs raise the error
137 raise;
138 End if;
139 end ins;
140 --
141 -- ------------------------------------------------------------------------------
142 -- |-----------------------< create_lock >-----------------------|
143 -- ------------------------------------------------------------------------------
144 Procedure create_lock
145 (p_transaction_id IN hr_wip_locks.transaction_id%TYPE
146 ,p_current_user_id IN fnd_user.user_id%TYPE
147 ,p_table_name IN hr_wip_locks.table_name%TYPE
148 ,p_primary_key_val1 IN hr_wip_locks.primary_key_val1%TYPE
149 ,p_primary_key_val2 IN hr_wip_locks.primary_key_val2%TYPE
150 ,p_primary_key_val3 IN hr_wip_locks.primary_key_val3%TYPE
151 ,p_primary_key_val4 IN hr_wip_locks.primary_key_val4%TYPE
152 ,p_primary_key_val5 IN hr_wip_locks.primary_key_val5%TYPE
153 ,p_lock_id OUT NOCOPY hr_wip_locks.lock_id%TYPE
154 ,p_locked OUT NOCOPY VARCHAR2
155 ,p_locking_transaction_id OUT NOCOPY hr_wip_transactions.transaction_id%TYPE
156 ,p_locking_lock_id OUT NOCOPY hr_wip_locks.lock_id%TYPE
157 )is
158 PRAGMA AUTONOMOUS_TRANSACTION;
159 l_lock_id hr_wip_locks.lock_id%TYPE;
160 l_locked varchar2(1);
161 l_locking_tran_id hr_wip_locks.transaction_id%TYPE;
162 l_locking_lock_id hr_wip_locks.lock_id%TYPE;
163 begin
164 chk_lock
165 (p_transaction_id => p_transaction_id
166 ,p_current_user_id => p_current_user_id
167 );
168 --
169 ins
170 (p_transaction_id => p_transaction_id
171 ,p_table_name => p_table_name
172 ,p_primary_key_val1 => p_primary_key_val1
173 ,p_primary_key_val2 => p_primary_key_val2
174 ,p_primary_key_val3 => p_primary_key_val3
175 ,p_primary_key_val4 => p_primary_key_val4
176 ,p_primary_key_val5 => p_primary_key_val5
177 ,p_commit => true
178 ,p_lock_id => l_lock_id
179 ,p_locked => l_locked
180 ,p_locking_transaction_id => l_locking_tran_id
181 ,p_locking_lock_id => l_locking_lock_id
182 );
183 If l_locked is null
184 then
185 commit;
186 End if;
187 --
188 If l_locked = 'Y'
189 then
190 p_locked := 'Y';
191 p_lock_id := null;
192 p_locking_transaction_id := l_locking_tran_id;
193 p_locking_lock_id := l_locking_lock_id;
194 else
195 p_locked := 'N';
196 p_lock_id := l_lock_id;
197 p_locking_transaction_id := null;
198 p_locking_lock_id := null;
199 End if;
200 end create_lock;
201 --
202 -- ------------------------------------------------------------------------------
203 -- |-----------------------< check_for_lock >-----------------------|
204 -- ------------------------------------------------------------------------------
205 Procedure check_for_lock
206 (p_transaction_id IN hr_wip_locks.transaction_id%TYPE
207 ,p_table_name IN hr_wip_locks.table_name%TYPE
208 ,p_primary_key_val1 IN hr_wip_locks.primary_key_val1%TYPE
209 ,p_primary_key_val2 IN hr_wip_locks.primary_key_val2%TYPE
210 ,p_primary_key_val3 IN hr_wip_locks.primary_key_val3%TYPE
211 ,p_primary_key_val4 IN hr_wip_locks.primary_key_val4%TYPE
212 ,p_primary_key_val5 IN hr_wip_locks.primary_key_val5%TYPE
213 ,p_locked OUT NOCOPY VARCHAR2
214 ,p_locking_lock_id OUT NOCOPY hr_wip_locks.lock_id%TYPE
215 ,p_locking_transaction_id OUT NOCOPY hr_wip_locks.transaction_id%TYPE
216 )is
217 l_trans_id hr_wip_locks.transaction_id%TYPE;
218 l_lock_id hr_wip_locks.lock_id%TYPE;
219 cursor csr_lock is
220 select lck.transaction_id,
221 lck.lock_id
222 from hr_wip_locks lck
223 where lck.transaction_id <> p_transaction_id
224 and lck.table_name = p_table_name
225 and lck.primary_key_val1 = p_primary_key_val1
226 and ((lck.primary_key_val2 = p_primary_key_val2)
227 or (lck.primary_key_val2 is null and p_primary_key_val2 is null))
228 and ((lck.primary_key_val3 = p_primary_key_val3)
229 or (lck.primary_key_val3 is null and p_primary_key_val3 is null))
230 and ((lck.primary_key_val4 = p_primary_key_val4)
231 or (lck.primary_key_val4 is null and p_primary_key_val4 is null))
232 and ((lck.primary_key_val5 = p_primary_key_val5)
233 or (lck.primary_key_val5 is null and p_primary_key_val5 is null));
234 begin
235 open csr_lock;
236 fetch csr_lock into l_trans_id,l_lock_id;
237 If csr_lock%notfound then
238 p_locked := 'N';
239 p_locking_transaction_id := null;
240 p_locking_lock_id := null;
241 Else
242 p_locked := 'Y';
243 p_locking_transaction_id := l_trans_id;
244 p_locking_lock_id := l_lock_id;
245 End if;
246 close csr_lock;
247 exception
248 when others then
249 If csr_lock%isopen
250 then
251 close csr_lock;
252 end if;
253 raise;
254 end check_for_lock;
255 --
256 -- ------------------------------------------------------------------------------
257 -- |-----------------------< check_for_lock >-----------------------|
258 -- ------------------------------------------------------------------------------
259 Procedure check_for_lock
260 (p_table_name IN hr_wip_locks.table_name%TYPE
261 ,p_primary_key_val1 IN hr_wip_locks.primary_key_val1%TYPE
262 ,p_primary_key_val2 IN hr_wip_locks.primary_key_val2%TYPE
263 ,p_primary_key_val3 IN hr_wip_locks.primary_key_val3%TYPE
264 ,p_primary_key_val4 IN hr_wip_locks.primary_key_val4%TYPE
265 ,p_primary_key_val5 IN hr_wip_locks.primary_key_val5%TYPE
266 ,p_locked OUT NOCOPY VARCHAR2
267 ,p_locking_lock_id OUT NOCOPY hr_wip_locks.lock_id%TYPE
268 ,p_locking_transaction_id OUT NOCOPY hr_wip_locks.transaction_id%TYPE
269 )is
270 l_trans_id hr_wip_locks.transaction_id%TYPE;
271 l_lock_id hr_wip_locks.lock_id%TYPE;
272 cursor csr_lock is
273 select lck.transaction_id,
274 lck.lock_id
275 from hr_wip_locks lck
276 where lck.table_name = p_table_name
277 and lck.primary_key_val1 = p_primary_key_val1
278 and ((lck.primary_key_val2 = p_primary_key_val2)
279 or (lck.primary_key_val2 is null and p_primary_key_val2 is null))
280 and ((lck.primary_key_val3 = p_primary_key_val3)
281 or (lck.primary_key_val3 is null and p_primary_key_val3 is null))
282 and ((lck.primary_key_val4 = p_primary_key_val4)
283 or (lck.primary_key_val4 is null and p_primary_key_val4 is null))
284 and ((lck.primary_key_val5 = p_primary_key_val5)
285 or (lck.primary_key_val5 is null and p_primary_key_val5 is null));
286 begin
287 open csr_lock;
288 fetch csr_lock into l_trans_id,l_lock_id;
289 If csr_lock%notfound then
290 p_locked := 'N';
291 p_locking_transaction_id := null;
292 p_locking_lock_id := null;
293 Else
294 p_locked := 'Y';
295 p_locking_transaction_id := l_trans_id;
296 p_locking_lock_id := l_lock_id;
297 End if;
298 close csr_lock;
299 exception
300 when others then
301 If csr_lock%isopen
302 then
303 close csr_lock;
304 end if;
305 raise;
306 end check_for_lock;
307 --
308 END hr_wip_lcks;