[Home] [Help]
PACKAGE BODY: APPS.HXC_LOCK_API
Source
1 PACKAGE BODY HXC_LOCK_API AS
2 /* $Header: hxclockapi.pkb 120.4 2005/09/23 05:29:07 nissharm noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 -- ----------------------------------------------------------------------------
7 -- |---------------------------< request_lock > ----------------------|
8 -- ----------------------------------------------------------------------------
9 -- this request lock is going to be used in SS
10 -- locked success is in varchar.
11 PROCEDURE request_lock
12 (p_process_locker_type IN VARCHAR2
13 ,p_resource_id IN NUMBER DEFAULT NULL
14 ,p_start_time IN DATE DEFAULT NULL
15 ,p_stop_time IN DATE DEFAULT NULL
16 ,p_time_building_block_id IN NUMBER
17 ,p_time_building_block_ovn IN NUMBER
18 ,p_transaction_lock_id IN NUMBER DEFAULT NULL
19 ,p_expiration_time IN NUMBER DEFAULT hxc_lock_util.c_ss_expiration_time
20 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
21 ,p_row_lock_id IN OUT NOCOPY ROWID
22 ,p_locked_success OUT NOCOPY VARCHAR2
23 ) IS
24
25 l_locked_success BOOLEAN := FALSE;
26
27 BEGIN
28
29 request_lock
30 (p_process_locker_type => p_process_locker_type
31 ,p_resource_id => p_resource_id
32 ,p_start_time => p_start_time
33 ,p_stop_time => p_stop_time
34 ,p_time_building_block_id => p_time_building_block_id
35 ,p_time_building_block_ovn => p_time_building_block_ovn
36 ,p_expiration_time => p_expiration_time
37 ,p_row_lock_id => p_row_lock_id
38 ,p_transaction_lock_id => p_transaction_lock_id
39 ,p_messages => p_messages
40 ,p_locked_success => l_locked_success
41 );
42 IF l_locked_success THEN
43 p_locked_success := 'Y';
44 ELSE
45 p_locked_success := 'N';
46 END IF;
47
48
49 END request_lock;
50
51 -- ----------------------------------------------------------------------------
52 -- |---------------------------< request_lock > ----------------------|
53 -- ----------------------------------------------------------------------------
54 -- this request lock is going to be used in timekeeper
55 -- forms since we cannot passed the HXC_MESSAGE_TABLE_TYPE
56 -- as a type
57
58
59 PROCEDURE request_lock
60 (p_process_locker_type IN VARCHAR2
61 ,p_resource_id IN NUMBER DEFAULT NULL
62 ,p_start_time IN DATE DEFAULT NULL
63 ,p_stop_time IN DATE DEFAULT NULL
64 ,p_time_building_block_id IN NUMBER
65 ,p_time_building_block_ovn IN NUMBER
66 ,p_transaction_lock_id IN NUMBER DEFAULT NULL
67 ,p_expiration_time IN NUMBER DEFAULT hxc_lock_util.c_ss_expiration_time
68 ,p_messages IN OUT NOCOPY hxc_self_service_time_deposit.message_table
69 ,p_row_lock_id IN OUT NOCOPY ROWID
70 ,p_locked_success OUT NOCOPY BOOLEAN
71 )IS
72
73 l_messages HXC_MESSAGE_TABLE_TYPE;
74
75 BEGIN
76
77 l_messages := hxc_message_table_type ();
78
79
80 request_lock
81 (p_process_locker_type => p_process_locker_type
82 ,p_resource_id => p_resource_id
83 ,p_start_time => p_start_time
84 ,p_stop_time => p_stop_time
85 ,p_time_building_block_id => p_time_building_block_id
86 ,p_time_building_block_ovn => p_time_building_block_ovn
87 ,p_expiration_time => p_expiration_time
88 ,p_row_lock_id => p_row_lock_id
89 ,p_transaction_lock_id => p_transaction_lock_id
90 ,p_messages => l_messages
91 ,p_locked_success => p_locked_success
92 );
93
94 -- convert the table to the right type
95 hxc_timekeeper_utilities.convert_type_to_message_table (
96 l_messages,
97 p_messages
98 );
99
100
101 END request_lock;
102 -- ----------------------------------------------------------------------------
103 -- |---------------------------< request_lock > ----------------------|
104 -- ----------------------------------------------------------------------------
105 PROCEDURE request_lock
106 (p_process_locker_type IN VARCHAR2
107 ,p_resource_id IN NUMBER DEFAULT NULL
108 ,p_start_time IN DATE DEFAULT NULL
109 ,p_stop_time IN DATE DEFAULT NULL
110 ,p_time_building_block_id IN NUMBER
111 ,p_time_building_block_ovn IN NUMBER
112 ,p_transaction_lock_id IN NUMBER DEFAULT NULL
113 ,p_expiration_time IN NUMBER DEFAULT hxc_lock_util.c_ss_expiration_time
114 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
115 ,p_row_lock_id IN OUT NOCOPY ROWID
116 ,p_locked_success OUT NOCOPY BOOLEAN
117 ) IS
118
119 cursor c_timecard_info (p_tbb_id number, p_tbb_ovn number) is
120 select scope, resource_id, stop_time, start_time, parent_building_Block_id,
121 parent_building_block_ovn
122 from hxc_time_building_blocks
123 where time_building_block_id = p_tbb_id
124 and object_version_number = p_tbb_ovn;
125 --and date_to = hr_general.end_of_time;
126
127 cursor c_app_period_detail (p_app_period_id number) is
128 select time_building_block_id, time_building_block_ovn
129 from hxc_ap_detail_links
130 where application_period_id = p_app_period_id;
131
132
133 --l_tc_resource_id NUMBER;
134 --l_tc_start_time DATE;
135 --l_tc_stop_time DATE;
136
137 l_resource_id NUMBER;
138 l_start_time DATE;
139 l_tc_scope VARCHAR2(80);
140 l_stop_time DATE;
141
142 l_dummy NUMBER;
143
144 l_row_lock_id ROWID;
145
146 l_locker_type_req_id NUMBER;
147 l_timecard_locked BOOLEAN := FALSE;
148
149 l_expiration_time NUMBER;
150
151 l_tbb_id NUMBER;
152 l_tbb_ovn NUMBER;
153
154 l_parent_tbb_id NUMBER;
155 l_parent_tbb_ovn NUMBER;
156
157
158 BEGIN
159
160 g_debug := hr_utility.debug_enabled;
161
162 -- quick check to denormilize the data and make
163 -- sure the passed data are correct
164
165 p_locked_success := FALSE;
166
167 l_resource_id := p_resource_id;
168 l_start_time := p_start_time;
169 l_stop_time := p_stop_time;
170 l_expiration_time := p_expiration_time;
171
172 --IF (p_resource_id = 7976) THEN
173 /*
174
175 if g_debug then
176 hr_utility.trace('JOEL - ----------------------------------');
177 hr_utility.trace('JOEL - request_lock 1 :'||p_resource_id);
178 hr_utility.trace('JOEL - request_lock 1 :'||p_process_locker_type);
179 hr_utility.trace('JOEL - request_lock 1: '||p_start_time);
180 hr_utility.trace('JOEL - request_lock 1: '||p_stop_time);
181 hr_utility.trace('JOEL - request_lock 1: '||p_time_building_block_id);
182 hr_utility.trace('JOEL - request_lock 1: '||p_time_building_block_ovn);
183 hr_utility.trace('JOEL - request_lock 1: '||p_expiration_time);
184 hr_utility.trace('JOEL - request_lock 1: '||p_row_lock_id);
185 end if;
186
187 */
188
189 --END IF;
190
191 IF l_expiration_time is null THEN
192 l_expiration_time := hxc_lock_util.c_ss_expiration_time;
193 END IF;
194
195 --dbms_output.put_line('JOEL - request_lock 1');
196
197 -- for ss we need to check first if the lock exist for this row_id
198 -- we return the same rowid if it does.
199 IF p_process_locker_type = hxc_lock_util.c_ss_timecard_action
200 OR
201 p_process_locker_type = hxc_lock_util.c_ss_timecard_view
202 OR
203 p_process_locker_type = hxc_lock_util.c_ss_approval_action
204 THEN
205
206 --dbms_output.put_line('JOEL - request_lock 2'||p_row_lock_id);
207 IF p_row_lock_id is not null THEN
208
209 p_locked_success :=
210 check_lock
211 (p_row_lock_id => p_row_lock_id
212 ,p_resource_id => p_resource_id
213 ,p_start_time => p_start_time
214 ,p_stop_time => p_stop_time);
215
216 IF p_locked_success THEN
217 --dbms_output.put_line('JOEL - request_lock 3');
218 RETURN;
219 ELSE
220 -- in this case 2 meaning:
221 -- 1 the timecard is not valid anymore so let's
222 -- delete it
223 --
224 -- 2 the row id and the resource_id and
225 -- start_time and stop_time does not match
226 -- then we are releasing the lock and we are
227 -- requesting a new one)
228 if g_debug then
229 hr_utility.trace('JOEL - request_lock 11: ');
230 end if;
231 release_lock (p_row_lock_id => p_row_lock_id);
232 --
233 p_row_lock_id := null;
234
235 END IF;
236
237
238 END IF;
239
240
241 END IF;
242
243 -- In the case of Day scope we will lock the timecard.
244 OPEN c_timecard_info (p_time_building_block_id,p_time_building_block_ovn);
245 FETCH c_timecard_info INTO l_tc_scope,l_resource_id,l_stop_time, l_start_time, l_parent_tbb_id, l_parent_tbb_ovn;
246 CLOSE c_timecard_info;
247
248 IF l_tc_scope = 'DAY' THEN
249 -- we take the timecard id
250 l_tbb_id := l_parent_tbb_id;
251 l_tbb_ovn := l_parent_tbb_ovn;
252 ELSE
253 l_tbb_id := p_time_building_block_id;
254 l_tbb_ovn := p_time_building_block_ovn;
255 END IF;
256
257
258
259 if g_debug then
260 hr_utility.trace('JOEL - request_lock 4');
261 end if;
262
263
264 -- check if there is not a check already.
265 check_lock
266 (p_process_locker_type => p_process_locker_type
267 ,p_resource_id => l_resource_id
268 ,p_start_time => l_start_time
269 ,p_stop_time => l_stop_time
270 ,p_time_building_block_id => l_tbb_id
271 ,p_time_building_block_ovn => l_tbb_ovn
272 ,p_messages => p_messages
273 ,p_timecard_locked => l_timecard_locked
274 ,p_time_building_block_scope => l_tc_scope
275 ,p_process_locker_type_id => l_locker_type_req_id);
276
277 if g_debug then
278 hr_utility.trace('JOEL - request_lock 2');
279 end if;
280
281 --if locked then stop the request_lock process
282 IF l_timecard_locked THEN
283 RETURN;
284 END IF;
285
286 if g_debug then
287 hr_utility.trace('JOEL - request_lock 2-1');
288 end if;
289
290
291
292 IF l_locker_type_req_id is null THEN
293 l_locker_type_req_id := hxc_lock_util.get_locker_type_req_id
294 (p_process_locker_type => p_process_locker_type
295 ,p_messages => p_messages);
296 END IF;
297 -- if it is not locked then now
298 -- we need to insert row in hxc_locks
299 IF l_tbb_id is not null THEN
300
301 if g_debug then
302 hr_utility.trace('JOEL - request_lock 3 :'||l_tc_scope);
303 hr_utility.trace('JOEL - request_lock 3: '||l_start_time);
304 hr_utility.trace('JOEL - request_lock 3: '||l_stop_time);
305 hr_utility.trace('JOEL - request_lock 3: '||l_resource_id);
306 hr_utility.trace('JOEL - request_lock 3: '||l_expiration_time);
307
308 end if;
309
310 IF (l_tc_scope is null or l_resource_id is null
311 or l_start_time is null or l_stop_time is null) THEN
312 OPEN c_timecard_info (l_tbb_id, l_tbb_ovn);
313 FETCH c_timecard_info INTO l_tc_scope,l_resource_id,l_stop_time, l_start_time,l_parent_tbb_id, l_parent_tbb_ovn;
314 CLOSE c_timecard_info;
315 END IF;
316
317 IF l_tc_scope = 'TIMECARD' or
318 l_tc_scope = 'TIMECARD_TEMPLATE' or
319 l_tc_scope = 'DETAIL' THEN
320
321 if g_debug then
322 hr_utility.trace('JOEL - request_lock 3-1: ' || p_time_building_block_id);
323 hr_utility.trace('JOEL - request_lock 3: '||l_start_time);
324 hr_utility.trace('JOEL - request_lock 3: '||l_stop_time);
325 end if;
326
327 hxc_lock_util.insert_lock
328 (p_locker_type_id => l_locker_type_req_id
329 ,p_resource_id => l_resource_id
330 ,p_start_time => l_start_time
331 ,p_stop_time => l_stop_time
332 ,p_time_building_block_id => l_tbb_id
333 ,p_time_building_block_ovn => l_tbb_ovn
334 ,p_transaction_lock_id => p_transaction_lock_id
335 ,p_expiration_time => l_expiration_time
336 ,p_row_lock_id => p_row_lock_id
337 );
338 if g_debug then
339 hr_utility.trace('JOEL - request_lock 3-1: ' || p_row_lock_id);
340 end if;
341
342 p_locked_success := TRUE;
343
344 ELSIF l_tc_scope = 'APPLICATION_PERIOD' THEN
345
346 if g_debug then
347 hr_utility.trace('JOEL - request_lock 4: ' || p_time_building_block_id);
348 end if;
349 -- we need to lock all the details for this application period
350 FOR crs_app_period_detail in c_app_period_detail(l_tbb_id)
351 LOOP
352 if g_debug then
353 hr_utility.trace('JOEL - request_lock 3: '||l_start_time);
354 hr_utility.trace('JOEL - request_lock 3: '||l_stop_time);
355 hr_utility.trace('JOEL - p_time_building_block_id 3: '||p_time_building_block_id);
356 end if;
357
358 -- if we arrive here that means we did not find a detail
359 -- locked and then we can insert a new lock
360 hxc_lock_util.insert_lock
361 (p_locker_type_id => l_locker_type_req_id
362 ,p_resource_id => l_resource_id
363 ,p_start_time => l_start_time
364 ,p_stop_time => l_stop_time
365 ,p_time_building_block_id => crs_app_period_detail.time_building_block_id
366 ,p_time_building_block_ovn => crs_app_period_detail.time_building_block_ovn
367 ,p_transaction_lock_id => p_transaction_lock_id
368 ,p_expiration_time => l_expiration_time
369 ,p_row_lock_id => p_row_lock_id);
370
371
372 END LOOP;
373
374 -- we lock the application period
375 hxc_lock_util.insert_lock
376 (p_locker_type_id => l_locker_type_req_id
377 ,p_resource_id => l_resource_id
378 ,p_start_time => l_start_time
379 ,p_stop_time => l_stop_time
380 ,p_time_building_block_id => l_tbb_id
381 ,p_time_building_block_ovn => l_tbb_ovn
382 ,p_transaction_lock_id => p_transaction_lock_id
383 ,p_expiration_time => l_expiration_time
384 ,p_row_lock_id => p_row_lock_id);
385
386 p_locked_success := TRUE;
387
388
389 END IF;
390
391 ELSE
392
393 if g_debug then
394 hr_utility.trace('JOEL - request_lock 5');
395 end if;
396
397 -- we need to lock a period
398 hxc_lock_util.insert_lock
399 (p_locker_type_id => l_locker_type_req_id
400 ,p_resource_id => p_resource_id
401 ,p_start_time => p_start_time
402 ,p_stop_time => p_stop_time
403 ,p_time_building_block_id => l_dummy
404 ,p_time_building_block_ovn => l_dummy
405 ,p_transaction_lock_id => p_transaction_lock_id
406 ,p_expiration_time => l_expiration_time
407 ,p_row_lock_id => p_row_lock_id
408 );
409 p_locked_success := TRUE;
410
411
412 END IF;
413
417
414 if g_debug then
415 hr_utility.trace('messages :');
416 end if;
418
419 --l_dummy := p_messages.first;
420 --LOOP
421 --EXIT WHEN
422 -- (NOT p_messages.exists(l_dummy));
423
424 --if g_debug then
425 -- hr_utility.trace('message_name :'||p_messages(l_dummy).message_name);
426 -- hr_utility.trace('message_level :'||p_messages(l_dummy).message_level);
427 --end if;
428
429 --l_dummy := p_messages.next(l_dummy);
430
431 --END LOOP;
432
433
434
435
436 END request_lock;
437
438
439 -- ----------------------------------------------------------------------------
440 -- |---------------------------< check_lock > ----------------------|
441 -- ----------------------------------------------------------------------------
442 PROCEDURE check_lock
443 (p_process_locker_type IN VARCHAR2
444 ,p_resource_id IN OUT NOCOPY NUMBER
445 ,p_start_time IN OUT NOCOPY DATE
446 ,p_stop_time IN OUT NOCOPY DATE
447 ,p_time_building_block_id IN NUMBER
448 ,p_time_building_block_ovn IN NUMBER
449 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
450 ,p_timecard_locked OUT NOCOPY BOOLEAN
451 ,p_time_building_block_scope OUT NOCOPY VARCHAR2
452 ,p_process_locker_type_id OUT NOCOPY NUMBER
453 )IS
454
455 cursor c_timecard_detail (p_tbb_id number, p_tbb_ovn number,p_resource_id number) is
456 select detail.time_building_block_id, detail.object_version_number
457 from hxc_time_building_blocks detail,hxc_time_building_blocks day
458 where day.parent_building_block_id = p_tbb_id
459 and day.parent_building_block_ovn = p_tbb_ovn
460 and day.scope = 'DAY'
461 and day.resource_id = p_resource_id
462 and day.time_building_block_id = detail.parent_building_block_id
463 and detail.parent_building_block_ovn =
464 (select max(day2.object_version_number)
465 from hxc_time_building_blocks day2
466 where day2.time_building_block_id = day.time_building_block_id)
467 and detail.scope = 'DETAIL'
468 and detail.resource_id = p_resource_id
469 and detail.object_version_number =
470 (select max(detail2.object_version_number)
471 from hxc_time_building_blocks detail2
472 where detail2.time_building_block_id = detail.time_building_block_id)
473 UNION
474 select day.time_building_block_id, day.object_version_number
475 from hxc_time_building_blocks day
476 where day.parent_building_block_id = p_tbb_id
477 and day.parent_building_block_ovn = p_tbb_ovn
478 and day.scope = 'DAY'
479 and day.resource_id = p_resource_id;
480
481
482 cursor c_detail_timecard (p_tbb_id number, p_tbb_ovn number,p_resource_id number) is
483 select day.parent_building_block_id, day.parent_building_block_ovn
484 from hxc_time_building_blocks detail, hxc_time_building_blocks day
485 where detail.time_building_block_id = p_tbb_id
486 and detail.object_version_number = p_tbb_ovn
487 and detail.scope = 'DETAIL'
488 and detail.resource_id = p_resource_id
489 --and detail.date_to = hr_general.end_of_time
490 and day.scope = 'DAY'
491 --and day.date_to = hr_general.end_of_time
492 and day.resource_id = p_resource_id
493 and day.time_building_block_id = detail.parent_building_block_id
494 and day.object_version_number = detail.parent_building_block_ovn
495 UNION
496 select detail.parent_building_block_id, detail.parent_building_block_ovn
497 from hxc_time_building_blocks detail
498 where detail.time_building_block_id = p_tbb_id
499 and detail.object_version_number = p_tbb_ovn
500 and detail.scope = 'DETAIL'
501 and detail.resource_id = p_resource_id;
502
503
504 cursor c_app_period_detail (p_app_period_id number) is
505 select time_building_block_id, time_building_block_ovn
506 from hxc_ap_detail_links
507 where application_period_id = p_app_period_id;
508
509 cursor c_resource_period_lock (p_resource_id number) is
510 select rowid, start_time, stop_time, lock_date, locker_type_id, process_id, attribute2
511 from hxc_locks
512 where resource_id = p_resource_id;
513
514 cursor c_period_lock (p_resource_id number) is
515 select rowid, start_time, stop_time, lock_date, locker_type_id, process_id, attribute2
516 from hxc_locks
517 where resource_id = p_resource_id
518 and time_building_block_id is null
519 and time_building_block_ovn is null;
520
521
522 --l_time_scope VARCHAR2(80);
523 --l_resource_id NUMBER;
524 --l_start_time DATE;
525 --l_stop_time DATE;
526
527 --l_locker_type_req_id NUMBER;
528
529 l_timecard_id NUMBER;
530 l_timecard_ovn NUMBER;
531
532 l_passed_check BOOLEAN := FALSE;
533
534 l_grant VARCHAR2(1);
535
536 BEGIN
537
538 g_debug := hr_utility.debug_enabled;
539
540 p_timecard_locked := FALSE;
541
542 --l_resource_id := p_resource_id;
543 --l_start_time := p_start_time;
544 --l_stop_time := p_stop_time;
545
546 --dbms_output.put_line('JOEL - check_lock 1');
547
548 -- check the parameteres
549 hxc_lock_util.check_parameters
550 (p_process_locker_type => p_process_locker_type
551 ,p_resource_id => p_resource_id
555 ,p_time_building_block_ovn => p_time_building_block_ovn
552 ,p_start_time => p_start_time
553 ,p_stop_time => p_stop_time
554 ,p_time_building_block_id => p_time_building_block_id
556 ,p_time_scope => p_time_building_block_scope
557 ,p_messages => p_messages
558 ,p_passed_check => l_passed_check);
559
560 --dbms_output.put_line('JOEL - check_lock 2 :'||p_time_building_block_scope);
561
562 -- end the process
563 IF not(l_passed_check) THEN
564 p_timecard_locked := TRUE;
565 RETURN;
566 END IF;
567
568 p_process_locker_type_id := hxc_lock_util.get_locker_type_req_id
569 (p_process_locker_type => p_process_locker_type
570 ,p_messages => p_messages);
571
572
573 --dbms_output.put_line('JOEL - check_lock 3');
574
575 if g_debug then
576 hr_utility.trace('JOEL - check_lock 3');
577 end if;
578
579 IF p_time_building_block_id is not null THEN
580 -- following the scope we need to do different thing.
581 -- but first we need to check if this
582
583 --dbms_output.put_line('JOEL - check_lock 4');
584
585 hxc_lock_util.check_row_lock
586 (p_locker_type_requestor_id => p_process_locker_type_id
587 ,p_process_locker_type => p_process_locker_type
588 ,p_resource_id => p_resource_id
589 ,p_time_building_block_id => p_time_building_block_id
590 ,p_time_building_block_ovn => p_time_building_block_ovn
591 ,p_messages => p_messages
592 ,p_row_locked => p_timecard_locked);
593
594 -- check if we can stop the process
595 IF p_timecard_locked THEN
596 RETURN;
597 END IF;
598
599 -- check if there is not a period locked for a resource
600 FOR crs_period_lock in c_period_lock(p_resource_id) LOOP
601
602 hxc_lock_util.check_date_lock
603 (p_locker_type_requestor_id => p_process_locker_type_id
604 ,p_locker_type_owner_id => crs_period_lock.locker_type_id
605 ,p_process_locker_type => p_process_locker_type
606 ,p_lock_date => crs_period_lock.lock_date
607 ,p_lock_start_time => crs_period_lock.start_time
608 ,p_lock_stop_time => crs_period_lock.stop_time
609 ,p_start_time => p_start_time
610 ,p_stop_time => p_stop_time
611 ,p_time_building_block_id => p_time_building_block_id
612 ,p_time_building_block_ovn => p_time_building_block_ovn
613 ,p_process_id => crs_period_lock.process_id
614 ,p_attribute2 => crs_period_lock.attribute2
615 ,p_resource_id => p_resource_id
616 ,p_rowid => crs_period_lock.rowid
617 ,p_messages => p_messages
618 ,p_row_locked => p_timecard_locked);
619 -- check if we can stop the process
620 IF p_timecard_locked THEN
621 RETURN;
622 END IF;
623
624 END LOOP;
625
626
627 --dbms_output.put_line('JOEL - check_lock 5');
628
629 -- following the scope of the timecard we might need
630 -- to search more
631 IF p_time_building_block_scope= 'TIMECARD' or
632 p_time_building_block_scope = 'TIMECARD_TEMPLATE' THEN
633
634 --dbms_output.put_line('JOEL - check_lock 6');
635
636 -- we need to look if there is not a detail locked
637 FOR crs_timecard_detail in c_timecard_detail(p_time_building_block_id,
638 p_time_building_block_ovn,
639 p_resource_id)
640 LOOP
641
642 --dbms_output.put_line('JOEL - check_lock 7: '||crs_timecard_detail.time_building_block_id);
643
644 hxc_lock_util.check_row_lock
645 (p_locker_type_requestor_id => p_process_locker_type_id
646 ,p_process_locker_type => p_process_locker_type
647 ,p_resource_id => p_resource_id
648 ,p_time_building_block_id => crs_timecard_detail.time_building_block_id
649 ,p_time_building_block_ovn => crs_timecard_detail.object_version_number
650 ,p_messages => p_messages
651 ,p_row_locked => p_timecard_locked);
652
653 -- check if we can stop the process
654 IF p_timecard_locked THEN
655
656 --dbms_output.put_line('JOEL - check_lock 8');
657
658 RETURN;
659 END IF;
660 END LOOP;
661
662 -- if the tbb is a detail we need to check if the timecard is not locked
663 ELSIF p_time_building_block_scope = 'DETAIL' THEN
664
665 --dbms_output.put_line('JOEL - check_lock 9 :'||p_time_building_block_id);
666 --dbms_output.put_line('JOEL - check_lock 9 :'||p_time_building_block_ovn);
667 --dbms_output.put_line('JOEL - check_lock 9 :'||l_resource_id);
668
669 OPEN c_detail_timecard
670 (p_time_building_block_id,
671 p_time_building_block_ovn,
672 p_resource_id);
673 FETCH c_detail_timecard into l_timecard_id, l_timecard_ovn;
674
675 IF c_detail_timecard%FOUND THEN
676 CLOSE c_detail_timecard;
677
678 --dbms_output.put_line('JOEL - check_lock 9 :'||l_timecard_id);
679 --dbms_output.put_line('JOEL - check_lock 9 :'||l_timecard_ovn);
680
684 ,p_process_locker_type => p_process_locker_type
681 -- try to find the lock
682 hxc_lock_util.check_row_lock
683 (p_locker_type_requestor_id => p_process_locker_type_id
685 ,p_resource_id => p_resource_id
686 ,p_time_building_block_id => l_timecard_id
687 ,p_time_building_block_ovn => l_timecard_ovn
688 ,p_messages => p_messages
689 ,p_row_locked => p_timecard_locked);
690
691 -- check if we can stop the process
692 IF p_timecard_locked THEN
693 RETURN;
694 END IF;
695 ELSE
696 hxc_timecard_message_helper.addErrorToCollection
697 (p_messages => p_messages
698 ,p_message_name => 'HXC_LOCK_PARAM_RULE_8'
699 ,p_message_level => 'ERROR'
700 ,p_message_field => null
701 ,p_message_tokens => null
702 ,p_application_short_name => 'HXC'
703 ,p_time_building_block_id => null
704 ,p_time_building_block_ovn => null
705 ,p_time_attribute_id => null
706 ,p_time_attribute_ovn => null
707 ,p_message_extent => null
708 );
709 p_timecard_locked := TRUE;
710 RETURN;
711 END IF;
712
713
714 ELSIF p_time_building_block_scope = 'APPLICATION_PERIOD' THEN
715
716 FOR crs_app_period_detail in c_app_period_detail(p_time_building_block_id)
717 LOOP
718
719 if g_debug then
720 hr_utility.trace('JOEL - check_lock 10 :');
721 end if;
722
723 -- try to find the lock
724 hxc_lock_util.check_row_lock
725 (p_locker_type_requestor_id => p_process_locker_type_id
726 ,p_process_locker_type => p_process_locker_type
727 ,p_resource_id => p_resource_id
728 ,p_time_building_block_id => crs_app_period_detail.time_building_block_id
729 ,p_time_building_block_ovn => crs_app_period_detail.time_building_block_ovn
730 ,p_messages => p_messages
731 ,p_row_locked => p_timecard_locked);
732
733
734 -- check if we can stop the process
735 IF p_timecard_locked THEN
736 RETURN;
737 if g_debug then
738 hr_utility.trace('JOEL - check_lock 11 :');
739 end if;
740 END IF;
741
742 -- for each detail we need to check if the parent is locked
743 OPEN c_detail_timecard
744 (crs_app_period_detail.time_building_block_id,
745 crs_app_period_detail.time_building_block_ovn,
746 p_resource_id);
747 FETCH c_detail_timecard into l_timecard_id, l_timecard_ovn;
748 CLOSE c_detail_timecard;
749
750 --dbms_output.put_line('JOEL - check_lock 11 :');
751 if g_debug then
752 hr_utility.trace('JOEL - check_lock 12 :');
753 end if;
754
755 -- try to find the lock
756 hxc_lock_util.check_row_lock
757 (p_locker_type_requestor_id => p_process_locker_type_id
758 ,p_process_locker_type => p_process_locker_type
759 ,p_resource_id => p_resource_id
760 ,p_time_building_block_id => l_timecard_id
761 ,p_time_building_block_ovn => l_timecard_ovn
762 ,p_messages => p_messages
763 ,p_row_locked => p_timecard_locked);
764
765 -- check if we can stop the process
766 IF p_timecard_locked THEN
767 if g_debug then
768 hr_utility.trace('JOEL - check_lock 13 :');
769 end if;
770 RETURN;
771 END IF;
772
773 END LOOP;
774 END IF;
775
776 ELSE
777 -- we are in the case of a checking a lock for a period for a resource
778 FOR crs_resource_period_lock in c_resource_period_lock(p_resource_id) LOOP
779
780 if g_debug then
781 hr_utility.trace('JOEL - check_lock 12 :');
782 end if;
783
784 hxc_lock_util.check_date_lock
785 (p_locker_type_requestor_id => p_process_locker_type_id
786 ,p_locker_type_owner_id => crs_resource_period_lock.locker_type_id
787 ,p_process_locker_type => p_process_locker_type
788 ,p_lock_date => crs_resource_period_lock.lock_date
789 ,p_lock_start_time => crs_resource_period_lock.start_time
790 ,p_lock_stop_time => crs_resource_period_lock.stop_time
791 ,p_start_time => p_start_time
792 ,p_stop_time => p_stop_time
793 ,p_time_building_block_id => p_time_building_block_id
794 ,p_time_building_block_ovn => p_time_building_block_ovn
795 ,p_process_id => crs_resource_period_lock.process_id
796 ,p_attribute2 => crs_resource_period_lock.attribute2
797 ,p_resource_id => p_resource_id
798 ,p_rowid => crs_resource_period_lock.rowid
799 ,p_messages => p_messages
800 ,p_row_locked => p_timecard_locked);
801
802
803 -- check if we can stop the process
804 IF p_timecard_locked THEN
805 RETURN;
806 END IF;
807
808 END LOOP;
809 END IF;
810
811 END check_lock;
812
816 FUNCTION check_lock (p_row_lock_id IN ROWID)
813 -- ----------------------------------------------------------------------------
814 -- |---------------------------< check_lock > ----------------------|
815 -- ----------------------------------------------------------------------------
817 RETURN BOOLEAN IS
818
819 cursor c_lock is
820 select lock_date from hxc_locks
821 where rowid = p_row_lock_id;
822
823 l_locked BOOLEAN := FALSE;
824 l_lock_date DATE;
825
826 BEGIN
827
828
829 IF p_row_lock_id is not null THEN
830 /*
831
832 g_debug := hr_utility.debug_enabled;
833
834 if g_debug then
835 hr_utility.trace('J a'||trim(p_row_lock_id)||'b');
836 end if;
837
838 */
839 OPEN c_lock;
840 FETCH c_lock into l_lock_date;
841
842 IF c_lock%FOUND THEN
843
844 IF l_lock_date > (sysdate-(1/24/60)*10) THEN
845 --lock still valid
846 l_locked := TRUE;
847 END IF;
848
849 END IF;
850 CLOSE c_lock;
851
852 END IF;
853
854 RETURN l_locked;
855
856 END check_lock;
857
858 -- ----------------------------------------------------------------------------
859 -- |---------------------------< check_lock > ----------------------|
860 -- ----------------------------------------------------------------------------
861 FUNCTION check_lock
862 (p_process_locker_type IN VARCHAR2
863 ,p_transaction_lock_id IN NUMBER
864 ,p_resource_id IN NUMBER)
865 RETURN ROWID IS
866
867 cursor c_lock (p_locker_type_req_id number) is
868 select lock_date, rowid from hxc_locks
869 where transaction_lock_id = p_transaction_lock_id
870 and resource_id = p_resource_id
871 and locker_type_id = p_locker_type_req_id;
872
873 l_locker_type_req_id NUMBER;
874 l_lock_date DATE;
875 l_locked BOOLEAN := FALSE;
876 l_row_id ROWID;
877 l_messages HXC_MESSAGE_TABLE_TYPE;
878
879 BEGIN
880
881 l_locker_type_req_id
882 := hxc_lock_util.get_locker_type_req_id
883 (p_process_locker_type => p_process_locker_type
884 ,p_messages => l_messages);
885
886
887 OPEN c_lock (l_locker_type_req_id);
888 FETCH c_lock into l_lock_date,l_row_id;
889
890 IF c_lock%FOUND THEN
891
892 IF l_lock_date > (sysdate-(1/24/60)*10) THEN
893 --lock still valid
894 l_locked := TRUE;
895 END IF;
896
897 END IF;
898 CLOSE c_lock;
899
900 IF not(l_locked) THEN
901 l_row_id := NULL;
902 END IF;
903
904 RETURN l_row_id;
905
906 END check_lock;
907
908 -- ----------------------------------------------------------------------------
909 -- |---------------------------< check_lock > ----------------------|
910 -- ----------------------------------------------------------------------------
911 FUNCTION check_lock
912 (p_row_lock_id IN ROWID
913 ,p_resource_id IN NUMBER
914 ,p_start_time IN DATE
915 ,p_stop_time IN DATE)
916 RETURN BOOLEAN IS
917
918 cursor c_lock is
919 select lock_date
920 from hxc_locks
921 where rowid = p_row_lock_id
922 and resource_id = p_resource_id
923 and start_time = p_start_time
924 and stop_time = p_stop_time;
925
926 l_lock_date DATE;
927 l_locked BOOLEAN := FALSE;
928
929 BEGIN
930
931
932 OPEN c_lock;
933 FETCH c_lock into l_lock_date;
934
935 IF c_lock%FOUND THEN
936
937 IF l_lock_date > (sysdate-(1/24/60)*10) THEN
938 --lock still valid
939 l_locked := TRUE;
940 END IF;
941
942 END IF;
943 CLOSE c_lock;
944
945
946 RETURN l_locked;
947
948 END check_lock;
949
950
951 -- ----------------------------------------------------------------------------
952 -- |---------------------------< release_lock > ----------------------|
953 -- ----------------------------------------------------------------------------
954 PROCEDURE release_lock
955 (p_row_lock_id IN ROWID)
956 IS
957
958 l_messages HXC_MESSAGE_TABLE_TYPE;
959 l_released_success BOOLEAN;
960
961 BEGIN
962
963
964 release_lock
965 (p_row_lock_id => p_row_lock_id
966 ,p_process_locker_type => hxc_lock_util.c_ss_timecard_action
967 ,p_messages => l_messages
968 ,p_released_success => l_released_success
969 ) ;
970
971
972 END release_lock;
973
974 -- ----------------------------------------------------------------------------
975 -- |---------------------------< release_lock > ----------------------|
976 -- ----------------------------------------------------------------------------
977 PROCEDURE release_lock
978 (p_row_lock_id IN ROWID
979 ,p_process_locker_type IN VARCHAR2
980 ,p_transaction_lock_id IN NUMBER DEFAULT NULL
981 ,p_released_success OUT NOCOPY BOOLEAN
985
982 ) IS
983
984 l_messages HXC_MESSAGE_TABLE_TYPE;
986 BEGIN
987
988
989 release_lock
990 (p_row_lock_id => p_row_lock_id
991 ,p_process_locker_type => p_process_locker_type
992 ,p_transaction_lock_id => p_transaction_lock_id
993 ,p_messages => l_messages
994 ,p_released_success => p_released_success
995 ) ;
996
997 END release_lock;
998
999 -- ----------------------------------------------------------------------------
1000 -- |---------------------------< release_lock > ----------------------|
1001 -- ----------------------------------------------------------------------------
1002 PROCEDURE release_lock
1003 (p_row_lock_id IN ROWID
1004 ,p_process_locker_type IN VARCHAR2
1005 ,p_transaction_lock_id IN NUMBER DEFAULT NULL
1006 ,p_resource_id IN NUMBER DEFAULT NULL
1007 ,p_start_time IN DATE DEFAULT NULL
1008 ,p_stop_time IN DATE DEFAULT NULL
1009 ,p_time_building_block_id IN NUMBER DEFAULT NULL
1010 ,p_time_building_block_ovn IN NUMBER DEFAULT NULL
1011 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
1012 ,p_released_success OUT NOCOPY BOOLEAN
1013 ) IS
1014
1015 cursor c_timecard_info(p_tbb_id number, p_tbb_ovn number) is
1019 where time_building_block_id = p_tbb_id
1016 select scope, resource_id, stop_time, start_time, parent_building_Block_id,
1017 parent_building_block_ovn
1018 from hxc_time_building_blocks
1020 and object_version_number = p_tbb_ovn;
1021 --and date_to = hr_general.end_of_time;
1022
1023 cursor c_app_period_detail (p_app_period_id number) is
1024 select time_building_block_id, time_building_block_ovn
1025 from hxc_ap_detail_links
1026 where application_period_id = p_app_period_id;
1027
1028
1029 l_locker_type_req_id NUMBER;
1030
1031 l_resource_id NUMBER;
1032 l_start_time DATE;
1033 l_stop_time DATE;
1034 l_time_scope VARCHAR2(80);
1035
1036 l_tc_scope VARCHAR2(80);
1037 l_tc_resource_id NUMBER;
1038 l_tc_start_time DATE;
1039 l_tc_stop_time DATE;
1040
1041 l_passed_check BOOLEAN := FALSE;
1042
1043 l_tbb_id NUMBER;
1044 l_tbb_ovn NUMBER;
1045
1046 l_parent_tbb_id NUMBER;
1047 l_parent_tbb_ovn NUMBER;
1048
1049 BEGIN
1050
1051 g_debug := hr_utility.debug_enabled;
1052
1053 p_released_success := FALSE;
1054
1055
1056 if g_debug then
1057 hr_utility.trace('--------------------------------------------');
1058 hr_utility.trace('JOEL - delete_lock 10 '||p_process_locker_type);
1059 hr_utility.trace('JOEL - delete_lock 10 :'||p_resource_id);
1060 hr_utility.trace('JOEL - delete_lock 10 :'||p_process_locker_type);
1061 hr_utility.trace('JOEL - delete_lock 10: '||p_start_time);
1062 hr_utility.trace('JOEL - delete_lock 10: '||p_stop_time);
1063 hr_utility.trace('JOEL - delete_lock 10: '||p_time_building_block_id);
1064 hr_utility.trace('JOEL - delete_lock 10: '||p_time_building_block_ovn);
1065 hr_utility.trace('JOEL - delete_lock 10: '||p_row_lock_id);
1066
1067 end if;
1068
1069 -- if it is not locked then now
1070 -- we need to insert row in hxc_locks
1071
1072 IF p_row_lock_id is not null THEN
1073 if g_debug then
1074 hr_utility.trace('JOEL - delete_lock 10 ');
1075 end if;
1076 hxc_lock_util.delete_lock
1077 (p_rowid => p_row_lock_id
1078 ,p_locker_type_id => null
1079 ,p_process_locker_type => p_process_locker_type
1080 ,p_messages => p_messages);
1081
1082 p_released_success := TRUE;
1083 RETURN;
1084 END IF;
1085
1086 IF p_transaction_lock_id is not null THEN
1087
1088 hxc_lock_util.delete_transaction_lock
1089 (p_transaction_lock_id => p_transaction_lock_id
1090 ,p_process_locker_type => p_process_locker_type
1091 ,p_messages => p_messages);
1092
1093 p_released_success := TRUE;
1094 RETURN;
1095 END IF;
1096
1097
1098 l_resource_id := p_resource_id;
1099 l_start_time := p_start_time;
1100 l_stop_time := p_stop_time;
1101
1102 -- In the case of Day scope we will lock the timecard.
1103 OPEN c_timecard_info (p_time_building_block_id,p_time_building_block_ovn);
1104 FETCH c_timecard_info INTO l_tc_scope,l_resource_id,l_stop_time, l_start_time, l_parent_tbb_id, l_parent_tbb_ovn;
1105 CLOSE c_timecard_info;
1106
1107 IF l_tc_scope = 'DAY' THEN
1108 -- we take the timecard id
1109 l_tbb_id := l_parent_tbb_id;
1110 l_tbb_ovn := l_parent_tbb_ovn;
1111 ELSE
1112 l_tbb_id := p_time_building_block_id;
1113 l_tbb_ovn := p_time_building_block_ovn;
1114 END IF;
1115
1116
1117 --dbms_output.put_line('JOEL - release_lock 2 :');
1118
1119 -- check the parameteres
1120 hxc_lock_util.check_parameters
1121 (p_process_locker_type => p_process_locker_type
1122 ,p_resource_id => l_resource_id
1123 ,p_start_time => l_start_time
1124 ,p_stop_time => l_stop_time
1125 ,p_time_building_block_id => l_tbb_id
1126 ,p_time_building_block_ovn => l_tbb_ovn
1127 ,p_time_scope => l_time_scope
1128 ,p_messages => p_messages
1129 ,p_passed_check => l_passed_check);
1130
1131 -- end the process
1132 IF not(l_passed_check) THEN
1133 RETURN;
1134 END IF;
1135
1136 --dbms_output.put_line('JOEL - release_lock 3:');
1137
1138 l_locker_type_req_id
1139 := hxc_lock_util.get_locker_type_req_id
1140 (p_process_locker_type => p_process_locker_type
1141 ,p_messages => p_messages);
1142
1143
1144 IF l_tbb_id is not null THEN
1145
1146
1147 --dbms_output.put_line('JOEL - release_lock 4 :');
1148
1149
1150 OPEN c_timecard_info(l_tbb_id, l_tbb_ovn);
1151 FETCH c_timecard_info INTO l_tc_scope,l_tc_resource_id,l_tc_stop_time,l_tc_start_time,l_parent_tbb_id, l_parent_tbb_ovn;
1152 CLOSE c_timecard_info;
1153
1154 IF l_tc_scope = 'TIMECARD' or
1155 l_tc_scope = 'TIMECARD_TEMPLATE' or
1156 l_tc_scope = 'DETAIL' THEN
1157
1158 --dbms_output.put_line('JOEL - release_lock 5 :');
1159
1160 hxc_lock_util.delete_tbb_lock
1161 (p_locker_type_id => l_locker_type_req_id
1162 ,p_time_building_block_id => l_tbb_id
1163 ,p_time_building_block_ovn => l_tbb_ovn);
1164
1165
1166 ELSIF l_tc_scope = 'APPLICATION_PERIOD' THEN
1167
1168 -- we need to lock all the details for this application period
1169 FOR crs_app_period_detail in c_app_period_detail(l_tbb_id)
1170 LOOP
1171
1172 --dbms_output.put_line('JOEL - release_lock 6 :');
1173
1174 -- if we arrive here that means we did not find a detail
1175 -- locked and then we can insert a new lock
1176 hxc_lock_util.delete_tbb_lock
1177 (p_locker_type_id => l_locker_type_req_id
1181 END IF;
1178 ,p_time_building_block_id => crs_app_period_detail.time_building_block_id
1179 ,p_time_building_block_ovn => crs_app_period_detail.time_building_block_ovn);
1180 END LOOP;
1182
1183 hxc_lock_util.delete_tbb_lock
1184 (p_locker_type_id => l_locker_type_req_id
1185 ,p_time_building_block_id => l_tbb_id
1186 ,p_time_building_block_ovn => l_tbb_ovn);
1187
1188
1189
1190 ELSE
1191
1192 --dbms_output.put_line('JOEL - release_lock 7 :');
1193
1194 -- we need to delete a period
1195 hxc_lock_util.delete_period_lock
1196 (p_locker_type_id => l_locker_type_req_id
1197 ,p_resource_id => p_resource_id
1198 ,p_start_time => p_start_time
1199 ,p_stop_time => p_stop_time);
1200
1201
1202 END IF;
1203
1204 p_released_success := TRUE;
1205
1206 END release_lock;
1207
1208 END HXC_LOCK_API;