[Home] [Help]
PACKAGE BODY: APPS.HXC_LOCK_API
Source
1 PACKAGE BODY HXC_LOCK_API AS
2 /* $Header: hxclockapi.pkb 120.6 2009/07/08 06:41:24 bbayragi ship $ */
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
131
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;
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);
306 hr_utility.trace('JOEL - request_lock 3: '||l_expiration_time);
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);
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
414 if g_debug then
415 hr_utility.trace('messages :');
416 end if;
417
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 -- Bug 8579449
456 -- Forced hints in the below queries to pick up the right path.
457
458 cursor c_timecard_detail (p_tbb_id number, p_tbb_ovn number,p_resource_id number) is
459 select /*+ LEADING(day)
460 INDEX(day HXC_TIME_BUILDING_BLOCKS_FK3) */
461 detail.time_building_block_id, detail.object_version_number
462 from hxc_time_building_blocks detail,hxc_time_building_blocks day
463 where day.parent_building_block_id = p_tbb_id
464 and day.parent_building_block_ovn = p_tbb_ovn
465 and day.scope = 'DAY'
466 and day.resource_id = p_resource_id
467 and day.time_building_block_id = detail.parent_building_block_id
468 and detail.parent_building_block_ovn =
469 (select max(day2.object_version_number)
470 from hxc_time_building_blocks day2
471 where day2.time_building_block_id = day.time_building_block_id)
472 and detail.scope = 'DETAIL'
473 and detail.resource_id = p_resource_id
474 and detail.object_version_number =
475 (select max(detail2.object_version_number)
476 from hxc_time_building_blocks detail2
477 where detail2.time_building_block_id = detail.time_building_block_id)
478 UNION
479 select /*+ INDEX(day HXC_TIME_BUILDING_BLOCKS_FK3)*/
480 day.time_building_block_id, day.object_version_number
481 from hxc_time_building_blocks day
482 where day.parent_building_block_id = p_tbb_id
483 and day.parent_building_block_ovn = p_tbb_ovn
484 and day.scope = 'DAY'
485 and day.resource_id = p_resource_id;
486
487
488 cursor c_detail_timecard (p_tbb_id number, p_tbb_ovn number,p_resource_id number) is
489 select /*+ LEADING(detail)
490 INDEX(detail HXC_TIME_BUILDING_BLOCKS_PK)*/
491 day.parent_building_block_id, day.parent_building_block_ovn
492 from hxc_time_building_blocks detail, hxc_time_building_blocks day
493 where detail.time_building_block_id = p_tbb_id
494 and detail.object_version_number = p_tbb_ovn
495 and detail.scope = 'DETAIL'
496 and detail.resource_id = p_resource_id
497 --and detail.date_to = hr_general.end_of_time
498 and day.scope = 'DAY'
499 --and day.date_to = hr_general.end_of_time
500 and day.resource_id = p_resource_id
501 and day.time_building_block_id = detail.parent_building_block_id
502 and day.object_version_number = detail.parent_building_block_ovn
503 UNION
504 select /*+ INDEX(detail HXC_TIME_BUILDING_BLOCKS_PK) */
505 detail.parent_building_block_id, detail.parent_building_block_ovn
506 from hxc_time_building_blocks detail
507 where detail.time_building_block_id = p_tbb_id
508 and detail.object_version_number = p_tbb_ovn
509 and detail.scope = 'DETAIL'
510 and detail.resource_id = p_resource_id;
511
512
513 cursor c_app_period_detail (p_app_period_id number) is
514 select time_building_block_id, time_building_block_ovn
515 from hxc_ap_detail_links
516 where application_period_id = p_app_period_id;
517
518 cursor c_resource_period_lock (p_resource_id number) is
519 select rowid, start_time, stop_time, lock_date, locker_type_id, process_id, attribute2
520 from hxc_locks
521 where resource_id = p_resource_id;
522
523 -- Bug 8666520
524 -- Added parameters p_start_time and p_stop_time
525 cursor c_period_lock (p_resource_id number,
526 p_start_time date,
527 p_stop_time date ) is
528 select rowid, start_time, stop_time, lock_date, locker_type_id, process_id, attribute2
529 from hxc_locks
530 where resource_id = p_resource_id
534 and time_building_block_ovn is null;
531 and start_time <= p_stop_time
532 and stop_time >= p_start_time
533 and time_building_block_id is null
535
536
537 --l_time_scope VARCHAR2(80);
538 --l_resource_id NUMBER;
539 --l_start_time DATE;
540 --l_stop_time DATE;
541
542 --l_locker_type_req_id NUMBER;
543
544 l_timecard_id NUMBER;
545 l_timecard_ovn NUMBER;
546
547 l_passed_check BOOLEAN := FALSE;
548
549 l_grant VARCHAR2(1);
550
551 BEGIN
552
553 g_debug := hr_utility.debug_enabled;
554
555 p_timecard_locked := FALSE;
556
557 --l_resource_id := p_resource_id;
558 --l_start_time := p_start_time;
559 --l_stop_time := p_stop_time;
560
561 --dbms_output.put_line('JOEL - check_lock 1');
562
563 -- check the parameteres
564 hxc_lock_util.check_parameters
565 (p_process_locker_type => p_process_locker_type
566 ,p_resource_id => p_resource_id
567 ,p_start_time => p_start_time
568 ,p_stop_time => p_stop_time
569 ,p_time_building_block_id => p_time_building_block_id
570 ,p_time_building_block_ovn => p_time_building_block_ovn
571 ,p_time_scope => p_time_building_block_scope
572 ,p_messages => p_messages
573 ,p_passed_check => l_passed_check);
574
575 --dbms_output.put_line('JOEL - check_lock 2 :'||p_time_building_block_scope);
576
577 -- end the process
578 IF not(l_passed_check) THEN
579 p_timecard_locked := TRUE;
580 RETURN;
581 END IF;
582
583 p_process_locker_type_id := hxc_lock_util.get_locker_type_req_id
584 (p_process_locker_type => p_process_locker_type
585 ,p_messages => p_messages);
586
587
588 --dbms_output.put_line('JOEL - check_lock 3');
589
590 if g_debug then
591 hr_utility.trace('JOEL - check_lock 3');
592 end if;
593
594 IF p_time_building_block_id is not null THEN
595 -- following the scope we need to do different thing.
596 -- but first we need to check if this
597
598 --dbms_output.put_line('JOEL - check_lock 4');
599
600 hxc_lock_util.check_row_lock
601 (p_locker_type_requestor_id => p_process_locker_type_id
602 ,p_process_locker_type => p_process_locker_type
603 ,p_resource_id => p_resource_id
604 ,p_time_building_block_id => p_time_building_block_id
605 ,p_time_building_block_ovn => p_time_building_block_ovn
606 ,p_messages => p_messages
607 ,p_row_locked => p_timecard_locked);
608
609 -- check if we can stop the process
610 IF p_timecard_locked THEN
611 RETURN;
612 END IF;
613
614 -- check if there is not a period locked for a resource
615
616 -- Bug 8666520
617 -- Added parameters p_start_time and p_stop_time
618
619 FOR crs_period_lock in c_period_lock(p_resource_id,p_start_time,p_stop_time) LOOP
620
621 hxc_lock_util.check_date_lock
622 (p_locker_type_requestor_id => p_process_locker_type_id
623 ,p_locker_type_owner_id => crs_period_lock.locker_type_id
624 ,p_process_locker_type => p_process_locker_type
625 ,p_lock_date => crs_period_lock.lock_date
626 ,p_lock_start_time => crs_period_lock.start_time
627 ,p_lock_stop_time => crs_period_lock.stop_time
628 ,p_start_time => p_start_time
629 ,p_stop_time => p_stop_time
630 ,p_time_building_block_id => p_time_building_block_id
631 ,p_time_building_block_ovn => p_time_building_block_ovn
632 ,p_process_id => crs_period_lock.process_id
633 ,p_attribute2 => crs_period_lock.attribute2
634 ,p_resource_id => p_resource_id
635 ,p_rowid => crs_period_lock.rowid
636 ,p_messages => p_messages
637 ,p_row_locked => p_timecard_locked);
638 -- check if we can stop the process
639 IF p_timecard_locked THEN
640 RETURN;
641 END IF;
642
643 END LOOP;
644
645
646 --dbms_output.put_line('JOEL - check_lock 5');
647
648 -- following the scope of the timecard we might need
649 -- to search more
650 IF p_time_building_block_scope= 'TIMECARD' or
651 p_time_building_block_scope = 'TIMECARD_TEMPLATE' THEN
652
653 --dbms_output.put_line('JOEL - check_lock 6');
654
655 -- we need to look if there is not a detail locked
656 FOR crs_timecard_detail in c_timecard_detail(p_time_building_block_id,
657 p_time_building_block_ovn,
658 p_resource_id)
659 LOOP
660
661 --dbms_output.put_line('JOEL - check_lock 7: '||crs_timecard_detail.time_building_block_id);
662
663 hxc_lock_util.check_row_lock
664 (p_locker_type_requestor_id => p_process_locker_type_id
665 ,p_process_locker_type => p_process_locker_type
666 ,p_resource_id => p_resource_id
667 ,p_time_building_block_id => crs_timecard_detail.time_building_block_id
668 ,p_time_building_block_ovn => crs_timecard_detail.object_version_number
669 ,p_messages => p_messages
670 ,p_row_locked => p_timecard_locked);
671
672 -- check if we can stop the process
673 IF p_timecard_locked THEN
674
675 --dbms_output.put_line('JOEL - check_lock 8');
676
677 RETURN;
678 END IF;
679 END LOOP;
680
681 -- if the tbb is a detail we need to check if the timecard is not locked
682 ELSIF p_time_building_block_scope = 'DETAIL' THEN
683
684 --dbms_output.put_line('JOEL - check_lock 9 :'||p_time_building_block_id);
685 --dbms_output.put_line('JOEL - check_lock 9 :'||p_time_building_block_ovn);
686 --dbms_output.put_line('JOEL - check_lock 9 :'||l_resource_id);
687
688 OPEN c_detail_timecard
689 (p_time_building_block_id,
690 p_time_building_block_ovn,
691 p_resource_id);
692 FETCH c_detail_timecard into l_timecard_id, l_timecard_ovn;
693
694 IF c_detail_timecard%FOUND THEN
695 CLOSE c_detail_timecard;
696
697 --dbms_output.put_line('JOEL - check_lock 9 :'||l_timecard_id);
698 --dbms_output.put_line('JOEL - check_lock 9 :'||l_timecard_ovn);
699
700 -- try to find the lock
701 hxc_lock_util.check_row_lock
702 (p_locker_type_requestor_id => p_process_locker_type_id
703 ,p_process_locker_type => p_process_locker_type
704 ,p_resource_id => p_resource_id
705 ,p_time_building_block_id => l_timecard_id
706 ,p_time_building_block_ovn => l_timecard_ovn
707 ,p_messages => p_messages
708 ,p_row_locked => p_timecard_locked);
709
710 -- check if we can stop the process
711 IF p_timecard_locked THEN
712 RETURN;
713 END IF;
714 ELSE
715 hxc_timecard_message_helper.addErrorToCollection
716 (p_messages => p_messages
717 ,p_message_name => 'HXC_LOCK_PARAM_RULE_8'
718 ,p_message_level => 'ERROR'
719 ,p_message_field => null
720 ,p_message_tokens => null
721 ,p_application_short_name => 'HXC'
722 ,p_time_building_block_id => null
723 ,p_time_building_block_ovn => null
724 ,p_time_attribute_id => null
725 ,p_time_attribute_ovn => null
726 ,p_message_extent => null
727 );
728 p_timecard_locked := TRUE;
729 RETURN;
730 END IF;
731
732
733 ELSIF p_time_building_block_scope = 'APPLICATION_PERIOD' THEN
734
735 FOR crs_app_period_detail in c_app_period_detail(p_time_building_block_id)
736 LOOP
737
738 if g_debug then
739 hr_utility.trace('JOEL - check_lock 10 :');
740 end if;
741
742 -- try to find the lock
743 hxc_lock_util.check_row_lock
744 (p_locker_type_requestor_id => p_process_locker_type_id
745 ,p_process_locker_type => p_process_locker_type
746 ,p_resource_id => p_resource_id
747 ,p_time_building_block_id => crs_app_period_detail.time_building_block_id
748 ,p_time_building_block_ovn => crs_app_period_detail.time_building_block_ovn
749 ,p_messages => p_messages
750 ,p_row_locked => p_timecard_locked);
751
752
753 -- check if we can stop the process
754 IF p_timecard_locked THEN
755 RETURN;
756 if g_debug then
757 hr_utility.trace('JOEL - check_lock 11 :');
758 end if;
759 END IF;
760
761 -- for each detail we need to check if the parent is locked
762 OPEN c_detail_timecard
763 (crs_app_period_detail.time_building_block_id,
764 crs_app_period_detail.time_building_block_ovn,
765 p_resource_id);
766 FETCH c_detail_timecard into l_timecard_id, l_timecard_ovn;
767 CLOSE c_detail_timecard;
768
769 --dbms_output.put_line('JOEL - check_lock 11 :');
770 if g_debug then
771 hr_utility.trace('JOEL - check_lock 12 :');
772 end if;
773
774 -- try to find the lock
775 hxc_lock_util.check_row_lock
776 (p_locker_type_requestor_id => p_process_locker_type_id
777 ,p_process_locker_type => p_process_locker_type
778 ,p_resource_id => p_resource_id
779 ,p_time_building_block_id => l_timecard_id
780 ,p_time_building_block_ovn => l_timecard_ovn
781 ,p_messages => p_messages
782 ,p_row_locked => p_timecard_locked);
783
784 -- check if we can stop the process
785 IF p_timecard_locked THEN
786 if g_debug then
787 hr_utility.trace('JOEL - check_lock 13 :');
788 end if;
789 RETURN;
790 END IF;
791
792 END LOOP;
793 END IF;
794
795 ELSE
796 -- we are in the case of a checking a lock for a period for a resource
797 FOR crs_resource_period_lock in c_resource_period_lock(p_resource_id) LOOP
798
802
799 if g_debug then
800 hr_utility.trace('JOEL - check_lock 12 :');
801 end if;
803 hxc_lock_util.check_date_lock
804 (p_locker_type_requestor_id => p_process_locker_type_id
805 ,p_locker_type_owner_id => crs_resource_period_lock.locker_type_id
806 ,p_process_locker_type => p_process_locker_type
807 ,p_lock_date => crs_resource_period_lock.lock_date
808 ,p_lock_start_time => crs_resource_period_lock.start_time
809 ,p_lock_stop_time => crs_resource_period_lock.stop_time
810 ,p_start_time => p_start_time
811 ,p_stop_time => p_stop_time
812 ,p_time_building_block_id => p_time_building_block_id
813 ,p_time_building_block_ovn => p_time_building_block_ovn
814 ,p_process_id => crs_resource_period_lock.process_id
815 ,p_attribute2 => crs_resource_period_lock.attribute2
816 ,p_resource_id => p_resource_id
817 ,p_rowid => crs_resource_period_lock.rowid
818 ,p_messages => p_messages
819 ,p_row_locked => p_timecard_locked);
820
821
822 -- check if we can stop the process
823 IF p_timecard_locked THEN
824 RETURN;
825 END IF;
826
827 END LOOP;
828 END IF;
829
830 END check_lock;
831
832 -- ----------------------------------------------------------------------------
833 -- |---------------------------< check_lock > ----------------------|
834 -- ----------------------------------------------------------------------------
835 FUNCTION check_lock (p_row_lock_id IN ROWID)
836 RETURN BOOLEAN IS
837
838 cursor c_lock is
839 select lock_date from hxc_locks
840 where rowid = p_row_lock_id;
841
842 l_locked BOOLEAN := FALSE;
843 l_lock_date DATE;
844
845 BEGIN
846
847
848 IF p_row_lock_id is not null THEN
849 /*
850
851 g_debug := hr_utility.debug_enabled;
852
853 if g_debug then
854 hr_utility.trace('J a'||trim(p_row_lock_id)||'b');
855 end if;
856
857 */
858 OPEN c_lock;
859 FETCH c_lock into l_lock_date;
860
861 IF c_lock%FOUND THEN
862
863 IF l_lock_date > (sysdate-(1/24/60)*10) THEN
864 --lock still valid
865 l_locked := TRUE;
866 END IF;
867
868 END IF;
869 CLOSE c_lock;
870
871 END IF;
872
873 RETURN l_locked;
874
875 END check_lock;
876
877 -- ----------------------------------------------------------------------------
878 -- |---------------------------< check_lock > ----------------------|
879 -- ----------------------------------------------------------------------------
880 FUNCTION check_lock
881 (p_process_locker_type IN VARCHAR2
882 ,p_transaction_lock_id IN NUMBER
883 ,p_resource_id IN NUMBER)
884 RETURN ROWID IS
885
886 cursor c_lock (p_locker_type_req_id number) is
887 select lock_date, rowid from hxc_locks
888 where transaction_lock_id = p_transaction_lock_id
889 and resource_id = p_resource_id
890 and locker_type_id = p_locker_type_req_id;
891
892 l_locker_type_req_id NUMBER;
893 l_lock_date DATE;
894 l_locked BOOLEAN := FALSE;
895 l_row_id ROWID;
896 l_messages HXC_MESSAGE_TABLE_TYPE;
897
898 BEGIN
899
900 l_locker_type_req_id
901 := hxc_lock_util.get_locker_type_req_id
902 (p_process_locker_type => p_process_locker_type
903 ,p_messages => l_messages);
904
905
906 OPEN c_lock (l_locker_type_req_id);
907 FETCH c_lock into l_lock_date,l_row_id;
908
909 IF c_lock%FOUND THEN
910
911 IF l_lock_date > (sysdate-(1/24/60)*10) THEN
912 --lock still valid
913 l_locked := TRUE;
914 END IF;
915
916 END IF;
917 CLOSE c_lock;
918
919 IF not(l_locked) THEN
920 l_row_id := NULL;
921 END IF;
922
923 RETURN l_row_id;
924
925 END check_lock;
926
927 -- ----------------------------------------------------------------------------
928 -- |---------------------------< check_lock > ----------------------|
929 -- ----------------------------------------------------------------------------
930 FUNCTION check_lock
931 (p_row_lock_id IN ROWID
932 ,p_resource_id IN NUMBER
933 ,p_start_time IN DATE
934 ,p_stop_time IN DATE)
935 RETURN BOOLEAN IS
936
937 cursor c_lock is
938 select lock_date
939 from hxc_locks
940 where rowid = p_row_lock_id
941 and resource_id = p_resource_id
942 and start_time = p_start_time
943 and stop_time = p_stop_time;
944
945 l_lock_date DATE;
946 l_locked BOOLEAN := FALSE;
947
948 BEGIN
949
950
951 OPEN c_lock;
952 FETCH c_lock into l_lock_date;
953
954 IF c_lock%FOUND THEN
955
956 IF l_lock_date > (sysdate-(1/24/60)*10) THEN
957 --lock still valid
958 l_locked := TRUE;
959 END IF;
960
961 END IF;
962 CLOSE c_lock;
963
964
965 RETURN l_locked;
966
967 END check_lock;
968
969
970 -- ----------------------------------------------------------------------------
971 -- |---------------------------< release_lock > ----------------------|
972 -- ----------------------------------------------------------------------------
973 PROCEDURE release_lock
974 (p_row_lock_id IN ROWID)
975 IS
976
977 l_messages HXC_MESSAGE_TABLE_TYPE;
978 l_released_success BOOLEAN;
979
980 BEGIN
981
982
983 release_lock
984 (p_row_lock_id => p_row_lock_id
985 ,p_process_locker_type => hxc_lock_util.c_ss_timecard_action
986 ,p_messages => l_messages
987 ,p_released_success => l_released_success
988 ) ;
989
990
991 END release_lock;
992
993 -- ----------------------------------------------------------------------------
994 -- |---------------------------< release_lock > ----------------------|
995 -- ----------------------------------------------------------------------------
996 PROCEDURE release_lock
997 (p_row_lock_id IN ROWID
998 ,p_process_locker_type IN VARCHAR2
999 ,p_transaction_lock_id IN NUMBER DEFAULT NULL
1000 ,p_released_success OUT NOCOPY BOOLEAN
1001 ) IS
1002
1003 l_messages HXC_MESSAGE_TABLE_TYPE;
1004
1005 BEGIN
1006
1007
1008 release_lock
1009 (p_row_lock_id => p_row_lock_id
1010 ,p_process_locker_type => p_process_locker_type
1011 ,p_transaction_lock_id => p_transaction_lock_id
1012 ,p_messages => l_messages
1013 ,p_released_success => p_released_success
1014 ) ;
1015
1016 END release_lock;
1017
1018 -- ----------------------------------------------------------------------------
1019 -- |---------------------------< release_lock > ----------------------|
1020 -- ----------------------------------------------------------------------------
1021 PROCEDURE release_lock
1022 (p_row_lock_id IN ROWID
1023 ,p_process_locker_type IN VARCHAR2
1024 ,p_transaction_lock_id IN NUMBER DEFAULT NULL
1025 ,p_resource_id IN NUMBER DEFAULT NULL
1026 ,p_start_time IN DATE DEFAULT NULL
1027 ,p_stop_time IN DATE DEFAULT NULL
1028 ,p_time_building_block_id IN NUMBER DEFAULT NULL
1029 ,p_time_building_block_ovn IN NUMBER DEFAULT NULL
1030 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
1031 ,p_released_success OUT NOCOPY BOOLEAN
1032 ) IS
1033
1034 cursor c_timecard_info(p_tbb_id number, p_tbb_ovn number) is
1035 select scope, resource_id, stop_time, start_time, parent_building_Block_id,
1036 parent_building_block_ovn
1037 from hxc_time_building_blocks
1038 where time_building_block_id = p_tbb_id
1039 and object_version_number = p_tbb_ovn;
1040 --and date_to = hr_general.end_of_time;
1041
1042 cursor c_app_period_detail (p_app_period_id number) is
1043 select time_building_block_id, time_building_block_ovn
1044 from hxc_ap_detail_links
1045 where application_period_id = p_app_period_id;
1046
1047
1048 l_locker_type_req_id NUMBER;
1049
1050 l_resource_id NUMBER;
1051 l_start_time DATE;
1052 l_stop_time DATE;
1053 l_time_scope VARCHAR2(80);
1054
1055 l_tc_scope VARCHAR2(80);
1056 l_tc_resource_id NUMBER;
1057 l_tc_start_time DATE;
1058 l_tc_stop_time DATE;
1059
1060 l_passed_check BOOLEAN := FALSE;
1061
1062 l_tbb_id NUMBER;
1063 l_tbb_ovn NUMBER;
1064
1065 l_parent_tbb_id NUMBER;
1066 l_parent_tbb_ovn NUMBER;
1067
1068 BEGIN
1069
1070 g_debug := hr_utility.debug_enabled;
1071
1072 p_released_success := FALSE;
1073
1074
1075 if g_debug then
1076 hr_utility.trace('--------------------------------------------');
1077 hr_utility.trace('JOEL - delete_lock 10 '||p_process_locker_type);
1078 hr_utility.trace('JOEL - delete_lock 10 :'||p_resource_id);
1079 hr_utility.trace('JOEL - delete_lock 10 :'||p_process_locker_type);
1080 hr_utility.trace('JOEL - delete_lock 10: '||p_start_time);
1081 hr_utility.trace('JOEL - delete_lock 10: '||p_stop_time);
1082 hr_utility.trace('JOEL - delete_lock 10: '||p_time_building_block_id);
1083 hr_utility.trace('JOEL - delete_lock 10: '||p_time_building_block_ovn);
1084 hr_utility.trace('JOEL - delete_lock 10: '||p_row_lock_id);
1085
1086 end if;
1087
1088 -- if it is not locked then now
1089 -- we need to insert row in hxc_locks
1090
1091 IF p_row_lock_id is not null THEN
1092 if g_debug then
1093 hr_utility.trace('JOEL - delete_lock 10 ');
1094 end if;
1095 hxc_lock_util.delete_lock
1096 (p_rowid => p_row_lock_id
1097 ,p_locker_type_id => null
1098 ,p_process_locker_type => p_process_locker_type
1099 ,p_messages => p_messages);
1100
1101 p_released_success := TRUE;
1102 RETURN;
1103 END IF;
1104
1105 IF p_transaction_lock_id is not null THEN
1106
1107 hxc_lock_util.delete_transaction_lock
1108 (p_transaction_lock_id => p_transaction_lock_id
1109 ,p_process_locker_type => p_process_locker_type
1110 ,p_messages => p_messages);
1111
1112 p_released_success := TRUE;
1113 RETURN;
1114 END IF;
1115
1116
1117 l_resource_id := p_resource_id;
1118 l_start_time := p_start_time;
1119 l_stop_time := p_stop_time;
1120
1121 -- In the case of Day scope we will lock the timecard.
1122 OPEN c_timecard_info (p_time_building_block_id,p_time_building_block_ovn);
1123 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;
1124 CLOSE c_timecard_info;
1125
1126 IF l_tc_scope = 'DAY' THEN
1127 -- we take the timecard id
1128 l_tbb_id := l_parent_tbb_id;
1129 l_tbb_ovn := l_parent_tbb_ovn;
1130 ELSE
1131 l_tbb_id := p_time_building_block_id;
1132 l_tbb_ovn := p_time_building_block_ovn;
1133 END IF;
1134
1135
1136 --dbms_output.put_line('JOEL - release_lock 2 :');
1137
1138 -- check the parameteres
1139 hxc_lock_util.check_parameters
1140 (p_process_locker_type => p_process_locker_type
1141 ,p_resource_id => l_resource_id
1142 ,p_start_time => l_start_time
1143 ,p_stop_time => l_stop_time
1144 ,p_time_building_block_id => l_tbb_id
1145 ,p_time_building_block_ovn => l_tbb_ovn
1146 ,p_time_scope => l_time_scope
1147 ,p_messages => p_messages
1148 ,p_passed_check => l_passed_check);
1149
1150 -- end the process
1151 IF not(l_passed_check) THEN
1152 RETURN;
1153 END IF;
1154
1155 --dbms_output.put_line('JOEL - release_lock 3:');
1156
1157 l_locker_type_req_id
1158 := hxc_lock_util.get_locker_type_req_id
1159 (p_process_locker_type => p_process_locker_type
1160 ,p_messages => p_messages);
1161
1162
1163 IF l_tbb_id is not null THEN
1164
1165
1166 --dbms_output.put_line('JOEL - release_lock 4 :');
1167
1168
1169 OPEN c_timecard_info(l_tbb_id, l_tbb_ovn);
1170 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;
1171 CLOSE c_timecard_info;
1172
1173 IF l_tc_scope = 'TIMECARD' or
1174 l_tc_scope = 'TIMECARD_TEMPLATE' or
1175 l_tc_scope = 'DETAIL' THEN
1176
1177 --dbms_output.put_line('JOEL - release_lock 5 :');
1178
1179 hxc_lock_util.delete_tbb_lock
1180 (p_locker_type_id => l_locker_type_req_id
1181 ,p_time_building_block_id => l_tbb_id
1182 ,p_time_building_block_ovn => l_tbb_ovn);
1183
1184
1185 ELSIF l_tc_scope = 'APPLICATION_PERIOD' THEN
1186
1187 -- we need to lock all the details for this application period
1188 FOR crs_app_period_detail in c_app_period_detail(l_tbb_id)
1189 LOOP
1190
1191 --dbms_output.put_line('JOEL - release_lock 6 :');
1192
1193 -- if we arrive here that means we did not find a detail
1194 -- locked and then we can insert a new lock
1195 hxc_lock_util.delete_tbb_lock
1196 (p_locker_type_id => l_locker_type_req_id
1197 ,p_time_building_block_id => crs_app_period_detail.time_building_block_id
1198 ,p_time_building_block_ovn => crs_app_period_detail.time_building_block_ovn);
1199 END LOOP;
1200 END IF;
1201
1202 hxc_lock_util.delete_tbb_lock
1203 (p_locker_type_id => l_locker_type_req_id
1204 ,p_time_building_block_id => l_tbb_id
1205 ,p_time_building_block_ovn => l_tbb_ovn);
1206
1207
1208
1209 ELSE
1210
1211 --dbms_output.put_line('JOEL - release_lock 7 :');
1212
1213 -- we need to delete a period
1214 hxc_lock_util.delete_period_lock
1215 (p_locker_type_id => l_locker_type_req_id
1216 ,p_resource_id => p_resource_id
1217 ,p_start_time => p_start_time
1218 ,p_stop_time => p_stop_time);
1219
1220
1221 END IF;
1222
1223 p_released_success := TRUE;
1224
1225 END release_lock;
1226
1227 END HXC_LOCK_API;