[Home] [Help]
PACKAGE BODY: APPS.HXC_LOCK_UTIL
Source
1 PACKAGE BODY HXC_LOCK_UTIL AS
2 /* $Header: hxclockutil.pkb 120.3 2005/09/23 05:19:29 nissharm noship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 -- ----------------------------------------------------------------------------
7 -- |---------------------------< check_parameters > ----------------------|
8 -- ----------------------------------------------------------------------------
9 PROCEDURE check_parameters
10 (p_process_locker_type IN VARCHAR2
11 ,p_resource_id IN OUT NOCOPY NUMBER
12 ,p_start_time IN OUT NOCOPY DATE
13 ,p_stop_time IN OUT NOCOPY DATE
14 ,p_time_building_block_id IN NUMBER
15 ,p_time_building_block_ovn IN NUMBER
16 ,p_time_scope IN OUT NOCOPY VARCHAR2
17 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
18 ,p_passed_check OUT NOCOPY BOOLEAN
19 ) IS
20
21 cursor c_timecard_info is
22 select scope, resource_id, stop_time, start_time
23 from hxc_time_building_blocks
24 where time_building_block_id = p_time_building_block_id
25 and object_version_number = p_time_building_block_ovn;
26 --and date_to = hr_general.end_of_time;
27
28 l_tc_scope VARCHAR2(80);
29 l_tc_resource_id NUMBER;
30 l_tc_start_time DATE;
31 l_tc_stop_time DATE;
32
33
34 BEGIN
35
36 p_passed_check := FALSE;
37
38 --dbms_output.put_line('JOEL check_parameters 1 ');
39
40
41 -- check_process_locker_type
42 IF p_process_locker_type is null THEN
43
44 --dbms_output.put_line('JOEL check_parameters 1');
45
46 hxc_timecard_message_helper.addErrorToCollection
47 (p_messages => p_messages
48 ,p_message_name => 'HXC_LOCK_PARAM_RULE_1'
49 ,p_message_level => 'ERROR'
50 ,p_message_field => null
51 ,p_message_tokens => null
52 ,p_application_short_name => 'HXC'
53 ,p_time_building_block_id => null
54 ,p_time_building_block_ovn => null
55 ,p_time_attribute_id => null
56 ,p_time_attribute_ovn => null
57 ,p_message_extent => null
58 );
59 p_passed_check := FALSE;
60 RETURN;
61
62 ELSE
63 -- if it is not null then check that p_process_locker_type
64 -- is a valid value
65 IF p_process_locker_type <> c_ss_timecard_action
66 AND
67 p_process_locker_type <> c_ss_timecard_view
68 AND
69 p_process_locker_type <> c_ss_approval_action
70 AND
71 p_process_locker_type <> c_pui_timekeeper_action
72 AND
73 p_process_locker_type <> c_plsql_pay_retrieval_action
74 AND
75 p_process_locker_type <> c_plsql_pa_retrieval_action
76 AND
77 p_process_locker_type <> c_plsql_eam_retrieval_action
78 AND
79 p_process_locker_type <> c_plsql_po_retrieval_action
80 AND
81 p_process_locker_type <> c_plsql_deposit_action
82 AND
83 p_process_locker_type <> c_plsql_coa_action
84 AND
85 p_process_locker_type <> c_plsql_ar_action
86 THEN
87
88 --dbms_output.put_line('JOEL check_parameters 2'||p_process_locker_type);
89
90 hxc_timecard_message_helper.addErrorToCollection
91 (p_messages => p_messages
92 ,p_message_name => 'HXC_LOCK_PARAM_RULE_2'
93 ,p_message_level => 'ERROR'
94 ,p_message_field => null
95 ,p_message_tokens => null
96 ,p_application_short_name => 'HXC'
97 ,p_time_building_block_id => null
98 ,p_time_building_block_ovn => null
99 ,p_time_attribute_id => null
100 ,p_time_attribute_ovn => null
101 ,p_message_extent => null
102 );
103 p_passed_check := FALSE;
104 RETURN;
105
106 END IF;
107 END IF;
108
109 --dbms_output.put_line('JOEL check_parameters 2 p_resource_id '||p_resource_id);
110 --dbms_output.put_line('JOEL check_parameters 2 p_start_time '||p_start_time);
111 --dbms_output.put_line('JOEL check_parameters 2 p_stop_time '||p_stop_time);
112 --dbms_output.put_line('JOEL check_parameters 2 p_time_building_block_id '||p_time_building_block_id);
113 --dbms_output.put_line('JOEL check_parameters 2 p_time_building_block_ovn '||p_time_building_block_ovn);
114
115
116 -- if all the parameters are null then throw an error
117 IF p_resource_id is null and
118 p_start_time is null and
119 p_stop_time is null and
120 p_time_building_block_id is null and
121 p_time_building_block_ovn is null
122 THEN
123
124 --dbms_output.put_line('JOEL check_parameters 3');
125
126 hxc_timecard_message_helper.addErrorToCollection
127 (p_messages => p_messages
128 ,p_message_name => 'HXC_LOCK_PARAM_RULE_3'
129 ,p_message_level => 'ERROR'
130 ,p_message_field => null
131 ,p_message_tokens => null
132 ,p_application_short_name => 'HXC'
133 ,p_time_building_block_id => null
134 ,p_time_building_block_ovn => null
135 ,p_time_attribute_id => null
136 ,p_time_attribute_ovn => null
137 ,p_message_extent => null
138 );
139 p_passed_check := FALSE;
140 RETURN;
141
142 ELSE
143
144 --dbms_output.put_line('JOEL check_parameters 3');
145
146 -- if time is not null or the resource is not null
147 -- then we are finding the timecard info in the
148 -- case the tbb is not null
149 -- we are checking also that the resource in param
150 -- and the timecard info are compatible
151 IF (p_time_building_block_id is not null
152 and p_time_building_block_ovn is not null)
153 OR
154 (p_resource_id is not null
155 and p_start_time is not null
156 and p_stop_time is not null)
157 THEN
158
159
160 IF (p_time_building_block_id is not null)
161 --and p_time_building_block_ovn is not null)
162 THEN
163 -- find the start_time, stop_time
164 -- resource_id and scope attached to
165 -- the timecard
166
167 --dbms_output.put_line('JOEL check_parameters 4: '||p_time_building_block_id);
168 --dbms_output.put_line('JOEL check_parameters 4: '||p_time_building_block_ovn);
169
170 OPEN c_timecard_info;
171 FETCH c_timecard_info INTO p_time_scope, l_tc_resource_id, l_tc_stop_time, l_tc_start_time;
172
173 IF c_timecard_info%FOUND THEN
174 CLOSE c_timecard_info;
175 IF (p_resource_id is not null and
176 l_tc_resource_id <> p_resource_id)
177 THEN
178 hxc_timecard_message_helper.addErrorToCollection
179 (p_messages => p_messages
180 ,p_message_name => 'HXC_LOCK_PARAM_RULE_4'
181 ,p_message_level => 'ERROR'
182 ,p_message_field => null
183 ,p_message_tokens => null
184 ,p_application_short_name => 'HXC'
185 ,p_time_building_block_id => null
186 ,p_time_building_block_ovn => null
187 ,p_time_attribute_id => null
188 ,p_time_attribute_ovn => null
189 ,p_message_extent => null
190 );
191 p_passed_check := FALSE;
192 RETURN;
193 END IF;
194
195 -- we are making sure that the scope
196 -- of the tbb that we are trying to lock
197 -- is supported.
198 IF l_tc_scope <> 'TIMECARD' and
199 l_tc_scope <> 'TIMECARD_TEMPLATE' and
200 l_tc_scope <> 'DETAIL' and
201 l_tc_scope <> 'APPLICATION_PERIOD' THEN
202
203 --dbms_output.put_line('JOEL check_parameters 5: '||l_tc_scope);
204
205 hxc_timecard_message_helper.addErrorToCollection
206 (p_messages => p_messages
207 ,p_message_name => 'HXC_LOCK_PARAM_RULE_5'
208 ,p_message_level => 'ERROR'
209 ,p_message_field => null
210 ,p_message_tokens => null
211 ,p_application_short_name => 'HXC'
212 ,p_time_building_block_id => null
213 ,p_time_building_block_ovn => null
214 ,p_time_attribute_id => null
215 ,p_time_attribute_ovn => null
216 ,p_message_extent => null
217 );
218 p_passed_check := FALSE;
219 RETURN;
220 END IF;
221
222 -- return the right information
223 p_resource_id := l_tc_resource_id;
224 p_start_time := l_tc_start_time;
225 p_stop_time := l_tc_stop_time;
226
227 ELSE
228
229 --dbms_output.put_line('JOEL check_parameters 6 ');
230
231 CLOSE c_timecard_info;
232 /*
233 hxc_timecard_message_helper.addErrorToCollection
234 (p_messages => p_messages
235 ,p_message_name => 'HXC_LOCK_PARAM_RULE_6'
236 ,p_message_level => 'ERROR'
237 ,p_message_field => null
238 ,p_message_tokens => null
239 ,p_application_short_name => 'HXC'
240 ,p_time_building_block_id => null
241 ,p_time_building_block_ovn => null
242 ,p_time_attribute_id => null
243 ,p_time_attribute_ovn => null
244 ,p_message_extent => null
245 );
246 p_passed_check := FALSE;
247 RETURN;
248 */
249 END IF;
250 ELSIF (p_resource_id is not null
251 and p_start_time is not null
252 and p_stop_time is not null) THEN
253
254 IF p_start_time > p_stop_time THEN
255 hxc_timecard_message_helper.addErrorToCollection
256 (p_messages => p_messages
257 ,p_message_name => 'HXC_LOCK_PARAM_RULE_9'
258 ,p_message_level => 'ERROR'
259 ,p_message_field => null
260 ,p_message_tokens => null
261 ,p_application_short_name => 'HXC'
262 ,p_time_building_block_id => null
263 ,p_time_building_block_ovn => null
264 ,p_time_attribute_id => null
265 ,p_time_attribute_ovn => null
266 ,p_message_extent => null
267 );
268 p_passed_check := FALSE;
269 RETURN;
270
271 END IF;
272
273 END IF;
274
275 ELSE
276
277 --dbms_output.put_line('JOEL check_parameters 7');
278
279 -- in the other case we raise an error.
280 hxc_timecard_message_helper.addErrorToCollection
281 (p_messages => p_messages
282 ,p_message_name => 'HXC_LOCK_PARAM_RULE_10'
283 ,p_message_level => 'ERROR'
284 ,p_message_field => null
285 ,p_message_tokens => null
286 ,p_application_short_name => 'HXC'
287 ,p_time_building_block_id => null
288 ,p_time_building_block_ovn => null
289 ,p_time_attribute_id => null
290 ,p_time_attribute_ovn => null
291 ,p_message_extent => null
292 );
293 p_passed_check := FALSE;
294 RETURN;
295
296 END IF;
297
298 END IF;
299
300 p_passed_check := TRUE;
301
302 END check_parameters;
303
304 -- ----------------------------------------------------------------------------
305 -- |---------------------------< check_grant > ----------------------|
306 -- ----------------------------------------------------------------------------
307 PROCEDURE check_grant(p_locker_type_owner_id IN NUMBER
308 ,p_locker_type_requestor_id IN NUMBER
309 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
310 ,p_grant_lock OUT NOCOPY VARCHAR2)
311 IS
312
313 cursor c_grant_lock (p_locker_type_owner_id NUMBER,
314 p_locker_type_requestor_id NUMBER) is
315 select grant_lock
316 from hxc_locking_rules
317 where locker_type_owner_id = p_locker_type_owner_id
318 and locker_type_requestor_id = p_locker_type_requestor_id;
319
320
321
322 BEGIN
323
324
325 OPEN c_grant_lock(p_locker_type_owner_id,p_locker_type_requestor_id);
326 FETCH c_grant_lock into p_grant_lock;
327 CLOSE c_grant_lock;
328
329
330 END check_grant;
331
332
333 -- ----------------------------------------------------------------------------
334 -- |---------------------------< check_session > ----------------------|
335 -- ----------------------------------------------------------------------------
336 FUNCTION check_session
337 (p_current_session_id IN NUMBER
338 ,p_current_user_id IN NUMBER
339 ,p_current_resource_id IN NUMBER
340 ,p_lock_session_id IN NUMBER
341 ,p_lock_user_id IN NUMBER
342 ,p_lock_resource_id IN NUMBER)
343 RETURN BOOLEAN IS
344
345 l_result BOOLEAN := FALSE;
346
347 BEGIN
348 /*
349 g_debug := hr_utility.debug_enabled;
350
351 if g_debug then
352
353 hr_utility.trace('p_lock_session_id '||p_lock_session_id);
354 hr_utility.trace('p_current_session_id '||p_current_session_id);
355 hr_utility.trace('p_current_user_id '||p_current_user_id);
356 hr_utility.trace('p_lock_user_id '||p_lock_user_id);
357 end if;
358 */
359 IF (p_current_resource_id = p_lock_resource_id and
360 p_current_user_id = p_lock_user_id) THEN
361 l_result := TRUE;
362
363 ELSIF (p_current_session_id = p_lock_session_id and
364 p_current_user_id = p_lock_user_id) THEN
365 l_result := TRUE;
366
367 ELSIF (p_current_session_id <> p_lock_session_id and
368 p_current_user_id = p_lock_user_id) THEN
369 l_result := TRUE;
370 END IF;
371
372 RETURN l_result;
373
374 END check_session;
375
376
377 /*
378 -- ----------------------------------------------------------------------------
382 PROCEDURE validate_lock
379 -- |---------------------------< validate_lock > ----------------------|
380 -- ----------------------------------------------------------------------------
381
383 (p_locker_type_owner_id IN OUT NOCOPY NUMBER
384 ,p_locker_type_requestor_id IN OUT NOCOPY NUMBER
385 ,p_lock_date IN OUT NOCOPY DATE
386 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
387 ,p_valid_lock IN OUT NOCOPY BOOLEAN) IS
388
389 l_grant VARCHAR2(1);
390
391 BEGIN
392
393 p_valid_lock := FALSE;
394
395 -- first we need to check that the lock is not expire
396 -- 10 min
397
398 --dbms_output.put_line('JOEL - validate_lock 1 '||p_lock_date);
399 --dbms_output.put_line('JOEL - validate_lock 1 '||(sysdate-(1/24/60)*10));
400
401 IF p_lock_date > (sysdate-(1/24/60)*10) THEN
402 -- now we need to check following who is locking
403 -- if we can still grant a lock
404 check_grant (p_locker_type_owner_id => p_locker_type_owner_id
405 ,p_locker_type_requestor_id => p_locker_type_requestor_id
406 ,p_messages => p_messages
407 ,p_grant_lock => l_grant
408 );
409
410 --dbms_output.put_line('JOEL - validate_lock 2 '||l_grant);
411
412 IF l_grant = 'N' THEN
413 p_valid_lock := TRUE;
414 END IF;
415
416 END IF;
417
418 END validate_lock;
419 */
420
421 -- ----------------------------------------------------------------------------
422 -- |---------------------------< insert_lock > ----------------------|
423 -- ----------------------------------------------------------------------------
424
425 PROCEDURE insert_lock (p_locker_type_id IN NUMBER
426 ,p_resource_id IN NUMBER
427 ,p_start_time IN DATE
428 ,p_stop_time IN DATE
429 ,p_time_building_block_id IN NUMBER
430 ,p_time_building_block_ovn IN NUMBER
431 ,p_transaction_lock_id IN NUMBER
432 ,p_expiration_time IN NUMBER
433 ,p_row_lock_id IN OUT NOCOPY ROWID) IS
434
435 PRAGMA AUTONOMOUS_TRANSACTION;
436
437 BEGIN
438
439 g_debug := hr_utility.debug_enabled;
440
441 --dbms_output.put_line('JOEL - insert_lock 1 p_start_time '||p_start_time);
442 --dbms_output.put_line('JOEL - insert_lock 1 p_stop_time '||p_stop_time);
443
444 if g_debug then
445 hr_utility.trace('JOEL - insert_lock 1 p_start_time '||p_start_time);
446 end if;
447
448 insert into hxc_locks
449 (LOCKER_TYPE_ID
450 ,RESOURCE_ID
451 ,START_TIME
452 ,STOP_TIME
453 ,TIME_BUILDING_BLOCK_ID
454 ,TIME_BUILDING_BLOCK_OVN
455 ,LOCK_DATE
456 ,PROCESS_ID
457 ,ATTRIBUTE1
458 ,ATTRIBUTE2
459 ,TRANSACTION_LOCK_ID)
460 values
461 (p_locker_type_id
462 ,p_resource_id
463 ,p_start_time
464 ,p_stop_time
465 ,p_time_building_block_id
466 ,p_time_building_block_ovn
467 ,sysdate + (1/24/60)*p_expiration_time
468 ,fnd_global.user_id
469 ,to_char(fnd_global.employee_id)
470 ,to_char(fnd_global.session_id)
471 ,p_transaction_lock_id
472 ) RETURNING ROWID into p_row_lock_id;
473
474 COMMIT;
475
476
477 END insert_lock;
478
482 PROCEDURE delete_lock(p_rowid IN ROWID
479 -- ----------------------------------------------------------------------------
480 -- |---------------------------< delete_lock > ----------------------|
481 -- ----------------------------------------------------------------------------
483 ,p_locker_type_id IN NUMBER
484 ,p_process_locker_type IN VARCHAR2
485 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE) IS
486
487 PRAGMA AUTONOMOUS_TRANSACTION;
488
489 cursor c_rowid_info is
490 select scope , b.time_building_block_id, a.locker_type_id
491 from hxc_locks a ,hxc_time_building_blocks b
492 where a.rowid = p_rowid
493 and b.time_building_block_id = a.time_building_block_id
494 and b.object_version_number = a.time_building_block_ovn ;
495
496
497 cursor c_app_period_detail (p_app_period_id number) is
498 select time_building_block_id, time_building_block_ovn
499 from hxc_ap_detail_links
500 where application_period_id = p_app_period_id;
501
502 l_scope VARCHAR2(80);
503 l_app_period_id NUMBER;
504 l_locker_type_id NUMBER;
505
506 BEGIN
507
508 g_debug := hr_utility.debug_enabled;
509
510 -- we need to check what is the scope of the rowid
511 -- and following the scope then we might need
512 -- to delete some parents
513
514
515
516 if g_debug then
517 hr_utility.trace('JOEL - delete_lock 0 ');
518 end if;
519
520
521 OPEN c_rowid_info;
522 FETCH c_rowid_info INTO l_scope,l_app_period_id,l_locker_type_id;
523 CLOSE c_rowid_info;
524
525 if g_debug then
526 hr_utility.trace('JOEL - delete_lock 1 '||l_app_period_id);
527 hr_utility.trace('JOEL - delete_lock 1 '||l_scope);
528
529 hr_utility.trace('JOEL - delete_lock 1 '||l_app_period_id);
530 end if;
531
532 IF l_scope = 'APPLICATION_PERIOD' THEN
533 -- we need to delete the childs
534 -- we need to lock all the details for this application period
535 IF l_locker_type_id is null THEN
536 IF p_locker_type_id is null THEN
537 l_locker_type_id := hxc_lock_util.get_locker_type_req_id
538 (p_process_locker_type => p_process_locker_type
539 ,p_messages => p_messages);
540 ELSE
541 l_locker_type_id := p_locker_type_id;
542 END IF;
543 END IF;
544
545 if g_debug then
546 hr_utility.trace('JOEL - delete_lock 2 '||l_locker_type_id);
547 end if;
548
549 FOR crs_app_period_detail in c_app_period_detail(l_app_period_id)
550 LOOP
551
552 if g_debug then
553 hr_utility.trace('JOEL - delete_lock 3 '||crs_app_period_detail.time_building_block_id);
554 hr_utility.trace('JOEL - delete_lock 3 '||crs_app_period_detail.time_building_block_ovn);
555 end if;
556
557 -- if we arrive here that means we did not find a detail
558 -- locked and then we can insert a new lock
559 hxc_lock_util.delete_tbb_lock
560 (p_locker_type_id => l_locker_type_id
561 ,p_time_building_block_id => crs_app_period_detail.time_building_block_id
562 ,p_time_building_block_ovn => crs_app_period_detail.time_building_block_ovn);
563 END LOOP;
564
565 END IF;
566
567
568 delete from hxc_locks
569 where rowid = p_rowid;
570
571 COMMIT;
572
573 END delete_lock;
574
575 -- ----------------------------------------------------------------------------
576 -- |------------------------< delete_transaction_lock > ----------------------|
577 -- ----------------------------------------------------------------------------
581 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE)
578 PROCEDURE delete_transaction_lock
579 (p_transaction_lock_id IN NUMBER
580 ,p_process_locker_type IN VARCHAR2
582 IS
583
584 PRAGMA AUTONOMOUS_TRANSACTION;
585
586 l_locker_type_id NUMBER;
587
588 BEGIN
589
590 l_locker_type_id := hxc_lock_util.get_locker_type_req_id
591 (p_process_locker_type => p_process_locker_type
592 ,p_messages => p_messages);
593
594
595 delete from hxc_locks
596 where transaction_lock_id = p_transaction_lock_id
597 and locker_type_id = l_locker_type_id;
598
599 COMMIT;
600
601 END delete_transaction_lock;
602
603
604 -- ----------------------------------------------------------------------------
605 -- |---------------------------< get_locker_type_req_id > ----------------------|
606 -- ----------------------------------------------------------------------------
607 FUNCTION get_locker_type_req_id
608 (p_process_locker_type IN VARCHAR
609 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE)
610 RETURN NUMBER IS
611
612
613 cursor c_locker_type_requestor (p_locker_type VARCHAR2
614 ,p_process_type VARCHAR2) is
615 select locker_type_id
616 from hxc_locker_types
617 where locker_type = p_locker_type
618 and process_type = p_process_type;
619
620 l_locker_type_requestor_id NUMBER := NULL;
621
622 BEGIN
623
624 IF p_process_locker_type = c_ss_timecard_action THEN
625
626 OPEN c_locker_type_requestor (c_self_service,c_timecard_action);
627 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
628 CLOSE c_locker_type_requestor;
629
630 ELSIF p_process_locker_type = c_ss_timecard_view THEN
631
632 OPEN c_locker_type_requestor (c_self_service,c_timecard_view);
633 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
634 CLOSE c_locker_type_requestor;
635
636 ELSIF p_process_locker_type = c_ss_approval_action THEN
637
638 OPEN c_locker_type_requestor (c_self_service,c_approval_action);
639 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
640 CLOSE c_locker_type_requestor;
641
642 ELSIF p_process_locker_type = c_pui_timekeeper_action THEN
643
644 OPEN c_locker_type_requestor (c_pui,c_timekeeper_action);
645 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
646 CLOSE c_locker_type_requestor;
647
648 ELSIF p_process_locker_type = c_plsql_pay_retrieval_action THEN
649
650 OPEN c_locker_type_requestor (c_plsql,c_pay_retrieval_action);
651 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
652 CLOSE c_locker_type_requestor;
653
654 ELSIF p_process_locker_type = c_plsql_pa_retrieval_action THEN
655
656 OPEN c_locker_type_requestor (c_plsql,c_pa_retrieval_action);
657 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
658 CLOSE c_locker_type_requestor;
659
660 ELSIF p_process_locker_type = c_plsql_eam_retrieval_action THEN
661
662 OPEN c_locker_type_requestor (c_plsql,c_eam_retrieval_action);
663 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
664 CLOSE c_locker_type_requestor;
665
666 ELSIF p_process_locker_type = c_plsql_deposit_action THEN
667
668 OPEN c_locker_type_requestor (c_plsql,c_deposit_action);
669 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
670 CLOSE c_locker_type_requestor;
671
672 ELSIF p_process_locker_type = c_plsql_po_retrieval_action THEN
673
674 OPEN c_locker_type_requestor (c_plsql,c_po_retrieval_action);
675 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
676 CLOSE c_locker_type_requestor;
677
678 ELSIF p_process_locker_type = c_plsql_coa_action THEN
679
680 OPEN c_locker_type_requestor (c_plsql,c_coa_action);
681 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
682 CLOSE c_locker_type_requestor;
683
684 ELSIF p_process_locker_type = c_plsql_ar_action THEN
685
686 OPEN c_locker_type_requestor (c_plsql,c_ar_action);
687 FETCH c_locker_type_requestor into l_locker_type_requestor_id;
688 CLOSE c_locker_type_requestor;
689
690 ELSE
691 -- in the other case we raise an error.
692 hxc_timecard_message_helper.addErrorToCollection
693 (p_messages => p_messages
694 ,p_message_name => 'HXC_LOCK_PARAM_RULE'
695 ,p_message_level => 'ERROR'
696 ,p_message_field => null
697 ,p_message_tokens => null
698 ,p_application_short_name => 'HXC'
699 ,p_time_building_block_id => null
700 ,p_time_building_block_ovn => null
701 ,p_time_attribute_id => null
702 ,p_time_attribute_ovn => null
703 ,p_message_extent => null
704 );
705 RETURN NULL;
706
707 END IF;
708
709 RETURN l_locker_type_requestor_id;
710
711 END get_locker_type_req_id;
712
713 -- ----------------------------------------------------------------------------
714 -- |---------------------------< checking_lock > ----------------------|
715 -- ----------------------------------------------------------------------------
716 PROCEDURE check_row_lock
717 (p_locker_type_requestor_id IN NUMBER
718 ,p_process_locker_type IN VARCHAR2
719 ,p_resource_id IN NUMBER
720 ,p_time_building_block_id IN NUMBER
721 ,p_time_building_block_ovn IN NUMBER
722 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
723 ,p_row_locked OUT NOCOPY BOOLEAN)
724 IS
725
726 cursor c_lock (p_tbb_id number, p_tbb_ovn number) is
727 select locker_type_id, lock_date, rowid, resource_id, process_id,attribute2
728 from hxc_locks
729 where time_building_block_id = p_tbb_id
730 and time_building_block_ovn = p_tbb_ovn;
731
732 cursor c_full_name (p_resource_id in number) is
733 select full_name
734 from per_all_people_f
735 where person_id = p_resource_id
736 and sysdate between effective_start_date and effective_end_date;
737
738
739 --l_valid_lock BOOLEAN := FALSE;
740 l_grant VARCHAR2(1);
741
742 l_full_name VARCHAR2(240);
743
744 l_locked_same_user_session BOOLEAN := FALSE;
745
746 BEGIN
747
748 g_debug := hr_utility.debug_enabled;
749
750 --dbms_output.put_line('JOEL - check_row_lock 0: '||p_time_building_block_id);
751 --dbms_output.put_line('JOEL - check_row_lock 0: '||p_time_building_block_ovn);
752
753 if g_debug then
754 hr_utility.trace('JOEL - check_row_lock 0: '||p_time_building_block_id);
755 end if;
756
757 FOR csr_lock in c_lock(p_time_building_block_id,p_time_building_block_ovn) LOOP
758
759 --dbms_output.put_line('JOEL - check_row_lock 1: l_locker_type_owner_id '||csr_lock.locker_type_id);
760 --dbms_output.put_line('JOEL - check_row_lock 1: l_lock_date '||csr_lock.lock_date);
761 --dbms_output.put_line('JOEL - check_row_lock 1: l_lock_rowid '||csr_lock.rowid);
762 -- we found the lock
763 if g_debug then
764 hr_utility.trace('JOEL - check_row_lock 0: '||p_time_building_block_id);
765 hr_utility.trace('JOEL - check_row_lock 0: '||p_time_building_block_ovn);
766 end if;
767
768 --dbms_output.put_line('JOEL - check_row_lock 2 ');
769
770 -- this procedure check if the lock is not expire
771 -- and look in the locking_rules table if the lock
772 -- can be overruled
773 IF csr_lock.lock_date > (sysdate-(1/24/60)*10) THEN
774
775 -- in the case that the lock is a different session
776 -- for the same user_id that would mean
777 -- we have a new browser, therefore we should allowed
778 -- the lock and no check for the grant.
779 l_locked_same_user_session := FALSE;
780
781 IF p_process_locker_type = c_ss_timecard_action
782 --OR p_process_locker_type = c_ss_timecard_view
783 THEN
784 l_locked_same_user_session
785 := check_session
786 (p_current_session_id => fnd_global.session_id
787 ,p_current_user_id => fnd_global.user_id
788 ,p_current_resource_id => p_resource_id
789 ,p_lock_session_id => to_number(csr_lock.attribute2)
790 ,p_lock_user_id => csr_lock.process_id
791 ,p_lock_resource_id => csr_lock.resource_id);
792 ELSE
793 l_locked_same_user_session := FALSE;
794 END IF;
795 /*
796
797 if g_debug then
798 hr_utility.trace('p_current_session_id '||fnd_global.session_id);
799 hr_utility.trace('p_current_user_id '||fnd_global.user_id);
800 hr_utility.trace('p_lock_session_id '||to_number(csr_lock.attribute2));
801 hr_utility.trace('p_lock_user_id '||csr_lock.process_id);
802 end if;
803
804 IF (l_locked_same_user_session) THEN
805 if g_debug then
806 hr_utility.trace('l_locked_same_user_session is true');
807 end if;
808 END IF;
809
810
811 */
812
813 IF not(l_locked_same_user_session) THEN
814
815 -- now we need to check following who is locking
816 -- if we can still grant a lock
817 check_grant (p_locker_type_owner_id => csr_lock.locker_type_id
818 ,p_locker_type_requestor_id => p_locker_type_requestor_id
819 ,p_messages => p_messages
820 ,p_grant_lock => l_grant
821 );
822
823 if g_debug then
824 hr_utility.trace('JOEL - check_row_lock 3 '||l_grant);
825 end if;
826
827 IF l_grant = 'N' THEN
828
829 --dbms_output.put_line('JOEL - check_row_lock 4 ');
830
831 p_row_locked := TRUE;
832
833 OPEN c_full_name (csr_lock.resource_id);
834 FETCH c_full_name into l_full_name;
835 CLOSE c_full_name;
836
837 hxc_timecard_message_helper.addErrorToCollection
838 (p_messages => p_messages
839 ,p_message_name => 'HXC_TIMECARD_LOCKED'
840 ,p_message_level => 'ERROR'
841 ,p_message_field => null
842 ,p_message_tokens => 'FULL_NAME&'||nvl(l_full_name,'unknown')
843 ,p_application_short_name => 'HXC'
844 ,p_time_building_block_id => p_time_building_block_id
845 ,p_time_building_block_ovn => p_time_building_block_ovn
846 ,p_time_attribute_id => null
847 ,p_time_attribute_ovn => null
848 ,p_message_extent => 'BLK_AND_CHILDREN'
849 );
850
851 RETURN;
852 END IF;
853 ELSE
854 -- we need to remove the row just found
855 -- and insert the new lock
856 if g_debug then
857 hr_utility.trace('JOEL - check_row_lock 4 ');
858 hr_utility.trace('JOEL - delete_lock 1 ');
859 end if;
860
861 hxc_lock_util.delete_lock
865 ,p_messages => p_messages);
862 (p_rowid => csr_lock.rowid
863 ,p_locker_type_id => csr_lock.locker_type_id
864 ,p_process_locker_type => null
866
867 END IF;
868 ELSE
869
870 if g_debug then
871 hr_utility.trace('JOEL - check_row_lock 5 ');
872 end if;
873
874 -- we need to remove the row just found
875 -- and insert the new lock
876 if g_debug then
877 hr_utility.trace('JOEL - delete_lock 2 ');
878 end if;
879 hxc_lock_util.delete_lock
880 (p_rowid => csr_lock.rowid
881 ,p_locker_type_id => csr_lock.locker_type_id
882 ,p_process_locker_type => null
883 ,p_messages => p_messages);
884
885 END IF;
886
887 END LOOP;
888
889
890
891 END check_row_lock;
892
893
894 -- ----------------------------------------------------------------------------
895 -- |---------------------------< check_date_lock > ----------------------|
896 -- ----------------------------------------------------------------------------
897 PROCEDURE check_date_lock
898 (p_locker_type_requestor_id IN NUMBER
899 ,p_locker_type_owner_id IN NUMBER
900 ,p_process_locker_type IN VARCHAR2
901 ,p_lock_date IN DATE
902 ,p_lock_start_time IN DATE
903 ,p_lock_stop_time IN DATE
904 ,p_start_time IN DATE
905 ,p_stop_time IN DATE
906 ,p_time_building_block_id IN NUMBER
907 ,p_time_building_block_ovn IN NUMBER
908 ,p_resource_id IN NUMBER
909 ,p_process_id IN NUMBER
910 ,p_attribute2 IN VARCHAR2
911 ,p_rowid IN ROWID
912 ,p_messages IN OUT NOCOPY HXC_MESSAGE_TABLE_TYPE
913 ,p_row_locked OUT NOCOPY BOOLEAN)
914 IS
915
916 cursor c_full_name is
917 select full_name
918 from per_all_people_f
919 where person_id = p_resource_id
920 and sysdate between effective_start_date and effective_end_date;
921
922 --l_valid_lock BOOLEAN := FALSE;
923 l_grant VARCHAR2(1);
924
925 l_full_name VARCHAR2(240);
926
927 l_locked_same_user_session BOOLEAN := FALSE;
928
929 BEGIN
930
931 g_debug := hr_utility.debug_enabled;
932
933 if g_debug then
934 hr_utility.trace('JOEL - check_date_lock 1: p_locker_type_requestor_id '||p_locker_type_requestor_id);
935 hr_utility.trace('JOEL - check_date_lock 1: p_locker_type_owner_id '||p_locker_type_owner_id);
936 hr_utility.trace('JOEL - check_date_lock 1: p_lock_date '||p_lock_date);
937 end if;
938 -- we found the lock
939
940 -- this procedure check if the lock is not expire
941 -- and look in the locking_rules table if the lock
942 -- can be overruled
943 IF p_lock_date > (sysdate-(1/24/60)*10) THEN
944
945 -- in the case that the lock is a different session
946 -- for the same user_id that would mean
947 -- we have a new browser, therefore we should allowed
948 -- the lock and no check for the grant.
949 IF p_process_locker_type = c_ss_timecard_action
950 OR p_process_locker_type = c_ss_timecard_view
951 THEN
952 l_locked_same_user_session
953 := check_session
954 (p_current_session_id => fnd_global.session_id
955 ,p_current_user_id => fnd_global.user_id
956 ,p_current_resource_id => p_resource_id
957 ,p_lock_session_id => to_number(p_attribute2)
958 ,p_lock_user_id => p_process_id
959 ,p_lock_resource_id => null);
960
961 ELSE
962 l_locked_same_user_session := FALSE;
963 END IF;
964 /*
965
966 if g_debug then
967 hr_utility.trace('p_current_session_id '||fnd_global.session_id);
968 hr_utility.trace('p_current_user_id '||fnd_global.user_id);
969 hr_utility.trace('p_lock_session_id '||to_number(p_attribute2));
970 hr_utility.trace('p_lock_user_id '||p_process_id);
971 end if;
972
973 IF (l_locked_same_user_session) THEN
974 if g_debug then
975 hr_utility.trace('l_locked_same_user_session is true');
976 end if;
977 END IF;
978 */
979
980
981
982 IF not(l_locked_same_user_session) THEN
983
984 -- now we need to check following who is locking
985 -- if we can still grant a lock
986 check_grant (p_locker_type_owner_id => p_locker_type_owner_id
987 ,p_locker_type_requestor_id => p_locker_type_requestor_id
988 ,p_messages => p_messages
989 ,p_grant_lock => l_grant
990 );
991
992 if g_debug then
993 hr_utility.trace('JOEL - check_date_lock 3 '||l_grant);
994 end if;
995
996
997 IF l_grant = 'N' THEN
998
999 if g_debug then
1000 hr_utility.trace('JOEL - check_date_lock 4 ');
1001
1002 hr_utility.trace('JOEL - check_date_lock 5: p_lock_start_time '||p_lock_start_time);
1003 hr_utility.trace('JOEL - check_date_lock 5: p_lock_stop_time '||p_lock_stop_time);
1004 hr_utility.trace('JOEL - check_date_lock 5: p_start_time '||p_start_time);
1005 hr_utility.trace('JOEL - check_date_lock 5: p_stop_time '||p_stop_time);
1006 end if;
1007
1008 -- bug 3097592.
1009
1010 /* IF (p_lock_start_time between p_start_time and p_stop_time)
1011 OR (p_lock_stop_time between p_start_time and p_stop_time)
1012 THEN
1013 */
1014 IF (trunc(p_lock_start_time) between p_start_time and p_stop_time)
1015 OR (trunc(p_lock_stop_time) between p_start_time and p_stop_time)
1016 OR (trunc(p_start_time) between p_lock_start_time and p_lock_stop_time)
1017 OR (trunc(p_stop_time) between p_lock_start_time and p_lock_stop_time)
1018 THEN
1019 -- bug 3097592.
1020
1021 if g_debug then
1022 hr_utility.trace('JOEL - check_date_lock 6 ');
1023 end if;
1024
1025 p_row_locked := TRUE;
1026
1027 OPEN c_full_name;
1028 FETCH c_full_name into l_full_name;
1029 CLOSE c_full_name;
1030
1031 hxc_timecard_message_helper.addErrorToCollection
1032 (p_messages => p_messages
1033 ,p_message_name => 'HXC_TIMECARD_LOCKED'
1034 ,p_message_level => 'ERROR'
1035 ,p_message_field => null
1036 ,p_message_tokens => 'FULL_NAME&'||nvl(l_full_name,'unknown')
1037 ,p_application_short_name => 'HXC'
1038 ,p_time_building_block_id => p_time_building_block_id
1039 ,p_time_building_block_ovn => p_time_building_block_ovn
1040 ,p_time_attribute_id => null
1041 ,p_time_attribute_ovn => null
1042 ,p_message_extent => 'BLK_AND_CHILDREN'
1043 );
1044
1045 RETURN;
1046 END IF;
1047
1048 END IF;
1049 ELSE
1050 -- we need to remove the row just found
1051 -- and insert the new lock
1052 if g_debug then
1053 hr_utility.trace('JOEL - delete_lock 4 ');
1054 end if;
1055 hxc_lock_util.delete_lock
1056 (p_rowid => p_rowid
1057 ,p_locker_type_id => p_locker_type_owner_id
1058 ,p_process_locker_type => null
1059 ,p_messages => p_messages);
1060
1061 END IF;
1062
1063
1064 ELSE
1065
1066 --dbms_output.put_line('JOEL - check_date_lock 7 ');
1067
1068 -- we need to remove the row just found
1069 -- and insert the new lock
1070 if g_debug then
1071 hr_utility.trace('JOEL - delete_lock 5 ');
1072 end if;
1073 hxc_lock_util.delete_lock
1074 (p_rowid => p_rowid
1075 ,p_locker_type_id => p_locker_type_owner_id
1076 ,p_process_locker_type => null
1077 ,p_messages => p_messages);
1078
1079 END IF;
1080
1081
1082 END check_date_lock;
1083
1084
1085
1086 -- ----------------------------------------------------------------------------
1087 -- |---------------------------< delete_tbb_lock > ----------------------|
1088 -- ----------------------------------------------------------------------------
1089
1090 PROCEDURE delete_tbb_lock (p_locker_type_id IN NUMBER
1091 ,p_time_building_block_id IN NUMBER
1092 ,p_time_building_block_ovn IN NUMBER) IS
1093
1094 PRAGMA AUTONOMOUS_TRANSACTION;
1095
1096 BEGIN
1097
1098 delete from hxc_locks
1099 where time_building_block_id = p_time_building_block_id
1100 and time_building_block_ovn = p_time_building_block_ovn
1101 and locker_type_id = p_locker_type_id;
1102
1103 COMMIT;
1104
1105 END delete_tbb_lock;
1106
1107 -- ----------------------------------------------------------------------------
1108 -- |---------------------------< delete_period_lock > ----------------------|
1109 -- ----------------------------------------------------------------------------
1110
1111 PROCEDURE delete_period_lock
1112 (p_locker_type_id IN NUMBER
1113 ,p_resource_id IN NUMBER
1114 ,p_start_time IN DATE
1115 ,p_stop_time IN DATE) IS
1116
1117 PRAGMA AUTONOMOUS_TRANSACTION;
1118
1119 BEGIN
1120
1121 --dbms_output.put_line('JOEL - delete_period_lock 1 '||p_locker_type_id);
1122 --dbms_output.put_line('JOEL - delete_period_lock 1 '||p_resource_id);
1123 --dbms_output.put_line('JOEL - delete_period_lock 1 '||p_start_time);
1124 --dbms_output.put_line('JOEL - delete_period_lock 1 '||p_stop_time);
1125
1126 delete from hxc_locks
1127 where resource_id = p_resource_id
1128 and locker_type_id = p_locker_type_id
1129 and start_time = p_start_time
1130 and stop_time = p_stop_time;
1131
1132 COMMIT;
1133
1134 END delete_period_lock;
1135
1136 END HXC_LOCK_UTIL;