[Home] [Help]
PACKAGE BODY: APPS.HXC_TK_GRP_QUERY_CRITERIA_API
Source
1 Package body hxc_tk_grp_query_criteria_api as
2 /* $Header: hxctkgqcapi.pkb 120.2 2005/09/23 09:27:04 rchennur noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' hxc_tk_grp_query_criteria_api.';
7
8 g_debug boolean := hr_utility.debug_enabled;
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------< create_tk_grp_query_criteria >----------------|
11 -- ----------------------------------------------------------------------------
12 --
13 -- Description:
14 -- SEE DESCRIPTION IN HEADER
15 --
16 --
17 procedure create_tk_grp_query_criteria
18 (p_validate in boolean default false
19 ,p_tk_group_query_criteria_id in out nocopy number
20 ,p_tk_group_query_id in out nocopy number
21 ,p_object_version_number in out nocopy number
22 ,p_tk_group_id in number
23 ,p_criteria_type in varchar2
24 ,p_criteria_id in number
25 ) is
26 --
27 -- Declare cursors and local variables
28 --
29 l_proc varchar2(72) ;
30 l_object_version_number hxc_tk_group_query_criteria.object_version_number%TYPE;
31 l_tk_group_query_criteria_id hxc_tk_group_query_criteria.tk_group_query_criteria_id%TYPE;
32 l_tk_group_query_id hxc_tk_group_queries.tk_group_query_id%TYPE;
33 --
34 begin
35 g_debug :=hr_utility.debug_enabled;
36 --
37 -- hr_utility.trace_on(trace_mode=>NULL, session_identifier=>'GAZ');
38 --
39 if g_debug then
40 l_proc := g_package||' create_tk_grp_query_criteria';
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 end if;
43 --
44 -- Issue a savepoint if operating in validation only mode
45 --
46 savepoint create_tk_grp_query_criteria;
47 --
48 hxc_tk_grp_query_criteria_api.chk_criteria_type
49 ( p_criteria_type => p_criteria_type );
50
51 hxc_tk_grp_query_criteria_api.chk_criteria_id
52 ( p_criteria_type => p_criteria_type
53 , p_criteria_id => p_criteria_id );
54
55 hxc_tk_group_query_api.maintain_tk_group_query
56 (
57 p_tk_group_query_id => l_tk_group_query_id
58 ,p_tk_group_id => p_tk_group_id
59 );
60 if g_debug then
61 hr_utility.trace('the tk group query id is '||to_char(l_tk_group_query_id));
62 end if;
63
64 hxc_tk_grp_query_criteria_api.chk_criteria_unique (
65 p_tk_group_query_criteria_id => p_tk_group_query_criteria_id
66 , p_tk_group_query_id => l_tk_group_query_id
67 , p_criteria_type => p_criteria_type
68 , p_criteria_id => p_criteria_id );
69
70
71 hxc_tk_grp_query_criteria_api.chk_tk_group_query_id ( p_tk_group_query_id => l_tk_group_query_id );
72
73 if g_debug then
74 hr_utility.set_location(l_proc, 20);
75 end if;
76 --
77 -- Call Before Process User Hook
78 --
79 begin
80 hxc_tk_grp_query_criteria_BK_1.create_tk_grp_query_criteria_b
81 (p_tk_group_query_criteria_id => p_tk_group_query_criteria_id
82 ,p_tk_group_query_id => l_tk_group_query_id
83 ,p_object_version_number => p_object_version_number
84 ,p_criteria_type => p_criteria_type
85 ,p_criteria_id => p_criteria_id );
86
87 exception
88 when hr_api.cannot_find_prog_unit then
89 hr_api.cannot_find_prog_unit_error
90 (p_module_name => 'create_tk_grp_query_criteria'
91 ,p_hook_type => 'BP'
92 );
93 end;
94 --
95 if g_debug then
96 hr_utility.set_location(l_proc, 30);
97 end if;
98 --
99 -- Process Logic
100 --
101 --
102 if g_debug then
103 hr_utility.set_location(l_proc, 40);
104 end if;
105 --
106 -- call row handler
107 --
108 hxc_tkgqc_ins.ins (
109 p_tk_group_query_criteria_id => l_tk_group_query_criteria_id
110 ,p_tk_group_query_id => l_tk_group_query_id
111 ,p_criteria_type => p_criteria_type
112 ,p_criteria_id => p_criteria_id
113 ,p_object_version_number => l_object_version_number );
114 --
115
116 if g_debug then
117 hr_utility.set_location(l_proc, 50);
118 end if;
119 --
120 -- Call After Process User Hook
121 --
122 begin
123 hxc_tk_grp_query_criteria_BK_1.create_tk_grp_query_criteria_a
124 (p_tk_group_query_criteria_id => l_tk_group_query_criteria_id
125 ,p_tk_group_query_id => l_tk_group_query_id
126 ,p_object_version_number => l_object_version_number
127 ,p_criteria_type => p_criteria_type
128 ,p_criteria_id => p_criteria_id
129 );
130 exception
131 when hr_api.cannot_find_prog_unit then
132 hr_api.cannot_find_prog_unit_error
133 (p_module_name => 'create_tk_grp_query_criteria'
134 ,p_hook_type => 'AP'
135 );
136 end;
137 --
138 if g_debug then
139 hr_utility.set_location(l_proc, 60);
140 end if;
141 --
142 -- When in validation only mode raise the Validate_Enabled exception
143 --
144 if p_validate then
145 raise hr_api.validate_enabled;
146 end if;
147 --
148 if g_debug then
149 hr_utility.set_location(' Leaving:'||l_proc, 70);
150 end if;
151 --
152 -- Set all output arguments
153 --
154 p_tk_group_query_criteria_id := l_tk_group_query_criteria_id;
155 p_tk_group_query_id := l_tk_group_query_id;
156 p_object_version_number := l_object_version_number;
157 --
158 exception
159 --
160 when hr_api.validate_enabled then
161 --
162 -- As the Validate_Enabled exception has been raised
163 -- we must rollback to the savepoint
164 --
165 ROLLBACK TO create_tk_grp_query_criteria;
166 --
167 -- Only set output warning arguments
168 -- (Any key or derived arguments must be set to null
169 -- when validation only mode is being used.)
170 --
171 p_tk_group_query_criteria_id := null;
172 p_object_version_number := null;
173 --
174 if g_debug then
175 hr_utility.set_location(' Leaving:'||l_proc, 80);
176 end if;
177 --
178 when others then
179 --
180 -- A validation or unexpected error has occured
181 --
182 if g_debug then
183 hr_utility.trace('In exeception');
184 end if;
185 ROLLBACK TO create_tk_grp_query_criteria;
186 raise;
187 --
188 END create_tk_grp_query_criteria;
189
190
191
192 -- ----------------------------------------------------------------------------
193 -- |-------------------------<update_tk_grp_query_criteria>-------------------|
194 -- ----------------------------------------------------------------------------
195 --
196 --
197 -- Description:
198 -- SEE DESCRIPTION IN HEADER
199 --
200 procedure update_tk_grp_query_criteria
201 (p_validate in boolean default false
202 ,p_tk_group_query_criteria_id in number
203 ,p_tk_group_query_id in number
204 ,p_object_version_number in out nocopy number
205 ,p_criteria_type in varchar2
206 ,p_criteria_id in number )
207 IS
208 --
209 -- Declare cursors and local variables
210 --
211 l_proc varchar2(72) ;
212 l_object_version_number hxc_tk_group_query_criteria.object_version_number%TYPE := p_object_version_number;
213 --
214 begin
215 --
216 g_debug :=hr_utility.debug_enabled;
217 if g_debug then
218 l_proc := g_package||' update_tk_grp_query_criteria';
219 hr_utility.set_location('Entering:'|| l_proc, 10);
220 end if;
221 --
222 -- Issue a savepoint if operating in validation only mode
223 --
224 savepoint update_tk_grp_query_criteria;
225 --
226
227 hr_api.mandatory_arg_error
228 (p_api_name => l_proc
229 ,p_argument => 'TK_GROUP_QUERY_CRITERIA_ID'
230 ,p_argument_value => p_tk_group_query_criteria_id
231 );
232
233 hxc_tk_grp_query_criteria_api.chk_criteria_type
234 ( p_criteria_type => p_criteria_type );
235
236 hxc_tk_grp_query_criteria_api.chk_criteria_id
237 ( p_criteria_type => p_criteria_type
238 , p_criteria_id => p_criteria_id );
239
240 hxc_tk_grp_query_criteria_api.chk_tk_group_query_id ( p_tk_group_query_id => p_tk_group_query_id );
241
242 hxc_tk_grp_query_criteria_api.chk_criteria_unique (
243 p_tk_group_query_criteria_id => p_tk_group_query_criteria_id
244 , p_tk_group_query_id => p_tk_group_query_id
245 , p_criteria_type => p_criteria_type
246 , p_criteria_id => p_criteria_id );
247
248 if g_debug then
249 hr_utility.set_location(l_proc, 20);
250 end if;
251 --
252 -- Call Before Process User Hook
253 --
254 begin
255 hxc_tk_grp_query_criteria_BK_2.update_tk_grp_query_criteria_b
256 (p_tk_group_query_criteria_id => p_tk_group_query_criteria_id
257 ,p_tk_group_query_id => p_tk_group_query_id
258 ,p_object_version_number => p_object_version_number
259 ,p_criteria_type => p_criteria_type
260 ,p_criteria_id => p_criteria_id
261 );
262 exception
263 when hr_api.cannot_find_prog_unit then
264 hr_api.cannot_find_prog_unit_error
265 (p_module_name => 'update_tk_grp_query_criteria'
266 ,p_hook_type => 'BP'
267 );
268 end;
269 --
270 if g_debug then
271 hr_utility.set_location(l_proc, 30);
272 end if;
273 --
274 -- Process Logic
275 --
276 -- call row handler
277 --
278 hxc_tkgqc_upd.upd (
279 p_tk_group_query_criteria_id => p_tk_group_query_criteria_id
280 ,p_tk_group_query_id => p_tk_group_query_id
281 ,p_criteria_type => p_criteria_type
282 ,p_criteria_id => p_criteria_id
283 ,p_object_version_number => l_object_version_number );
284 --
285 --
286 if g_debug then
287 hr_utility.set_location(l_proc, 40);
288 end if;
289 --
290 -- Call After Process User Hook
291 --
292 begin
293 hxc_tk_grp_query_criteria_BK_2.update_tk_grp_query_criteria_a
294 (p_tk_group_query_criteria_id => p_tk_group_query_criteria_id
295 ,p_tk_group_query_id => p_tk_group_query_id
296 ,p_object_version_number => l_object_version_number
297 ,p_criteria_type => p_criteria_type
298 ,p_criteria_id => p_criteria_id
299 );
300 exception
301 when hr_api.cannot_find_prog_unit then
302 hr_api.cannot_find_prog_unit_error
303 (p_module_name => 'update_tk_grp_query_criteria'
304 ,p_hook_type => 'AP'
305 );
306 end;
307 --
308 if g_debug then
309 hr_utility.set_location(l_proc, 50);
310 end if;
311 --
312 -- When in validation only mode raise the Validate_Enabled exception
313 --
314 if p_validate then
315 raise hr_api.validate_enabled;
316 end if;
317 --
318 if g_debug then
319 hr_utility.set_location(' Leaving:'||l_proc, 60);
320 end if;
321 --
322 -- Set all output arguments
323 --
324 p_object_version_number := l_object_version_number;
325 --
326 exception
327 --
328 when hr_api.validate_enabled then
329 --
330 -- As the Validate_Enabled exception has been raised
331 -- we must rollback to the savepoint
332 --
333 ROLLBACK TO update_tk_grp_query_criteria;
334 --
335 -- Only set output warning arguments
336 -- (Any key or derived arguments must be set to null
337 -- when validation only mode is being used.)
338 --
339 p_object_version_number := null;
340 --
341 if g_debug then
342 hr_utility.set_location(' Leaving:'||l_proc, 60);
343 end if;
344 --
345 when others then
346 --
347 -- A validation or unexpected error has occured
348 --
349 if g_debug then
350 hr_utility.trace('In exeception');
351 end if;
352 ROLLBACK TO update_tk_grp_query_criteria;
353 raise;
354
355 end update_tk_grp_query_criteria;
356 --
357 -- ----------------------------------------------------------------------------
358 -- |----------------------< delete_tk_grp_query_criteria >--------------------|
359 -- ----------------------------------------------------------------------------
360 --
361 --
362 -- Description:
363 -- SEE DESCRIPTION IN HEADER
364 --
365 procedure delete_tk_grp_query_criteria
366 (p_validate in boolean default false
367 ,p_tk_group_query_criteria_id in number
368 ,p_object_version_number in number
369 ) is
370 --
371 -- Declare cursors and local variables
372 --
373 l_proc varchar2(72) ;
374 --
375 begin
376 --
377 g_debug :=hr_utility.debug_enabled;
378 -- hr_utility.trace_on(trace_mode=>NULL, session_identifier=>'GAZ');
379 if g_debug then
380 l_proc := g_package||'delete_tk_grp_query_criteria';
381 hr_utility.set_location('Entering:'|| l_proc, 10);
382 end if;
383 --
384 -- Issue a savepoint if operating in validation only mode
385 --
386 savepoint delete_tk_grp_query_criteria;
387 --
388 hxc_tk_grp_query_criteria_api.chk_delete (
389 p_tk_group_query_criteria_id => p_tk_group_query_criteria_id );
390
391 if g_debug then
392 hr_utility.set_location(l_proc, 20);
393 end if;
394 --
395 -- Call Before Process User Hook
396 --
397 begin
398 --
399 hxc_tk_grp_query_criteria_BK_3.delete_tk_grp_query_criteria_b
400 (p_tk_group_query_criteria_id => p_tk_group_query_criteria_id
401 ,p_object_version_number => p_object_version_number
402 );
403 exception
404 when hr_api.cannot_find_prog_unit then
405 hr_api.cannot_find_prog_unit_error
406 (p_module_name => 'delete_tk_grp_query_criteria'
407 ,p_hook_type => 'BP'
408 );
409 end;
410 --
411 if g_debug then
412 hr_utility.set_location(l_proc, 30);
413 end if;
414 --
415 -- Process Logic
416 --
417 hxc_tkgqc_del.del
418 (
419 p_tk_group_query_criteria_id => p_tk_group_query_criteria_id
420 ,p_object_version_number => p_object_version_number
421 );
422 --
423 if g_debug then
424 hr_utility.set_location(l_proc, 40);
425 end if;
426 --
427 -- Call After Process User Hook
428 --
429 begin
430 --
431 hxc_tk_grp_query_criteria_BK_3.delete_tk_grp_query_criteria_a
432 (p_tk_group_query_criteria_id => p_tk_group_query_criteria_id
433 ,p_object_version_number => p_object_version_number
434 );
435 exception
436 when hr_api.cannot_find_prog_unit then
437 hr_api.cannot_find_prog_unit_error
438 (p_module_name => 'delete_tk_grp_query_criteria'
439 ,p_hook_type => 'AP'
440 );
441 end;
442 --
443 -- When in validation only mode raise the Validate_Enabled exception
444 --
445 if p_validate then
446 raise hr_api.validate_enabled;
447 end if;
448 --
449 if g_debug then
450 hr_utility.set_location(' Leaving:'||l_proc, 50);
451 end if;
452 --
453 exception
454 --
455 when hr_api.validate_enabled then
456 --
457 -- As the Validate_Enabled exception has been raised
458 -- we must rollback to the savepoint
459 --
460 ROLLBACK TO delete_tk_grp_query_criteria;
461 --
462 when others then
463 --
464 -- A validation or unexpected error has occured
465 --
466 ROLLBACK TO delete_tk_grp_query_criteria;
467 raise;
468 --
469 end delete_tk_grp_query_criteria;
470 --
471 -- ----------------------------------------------------------------------------
472 -- |-----------------------< chk_criteria_type >------------------------------|
473 -- ----------------------------------------------------------------------------
474 --
475 -- Description:
476 --
477 -- SEE DESCRIPTION IN PACKAGE HEADER
478 --
479 -- Note:
480 -- This procedure is called from the client
481 --
482 -- ----------------------------------------------------------------------------
483 Procedure chk_criteria_type
484 (
485 p_criteria_type in varchar2
486 ) IS
487
488 l_proc varchar2(72) ;
489 --
490 -- cursor to check criteria type valid
491 --
492 CURSOR csr_chk_criteria_type IS
493 SELECT 'error'
494 FROM sys.dual
495 WHERE EXISTS (
496 SELECT 'x'
497 FROM hr_lookups h
498 WHERE h.lookup_type = 'HXC_TK_CRITERIA_TYPES'
499 AND h.lookup_code = p_criteria_type
500 AND h.enabled_flag = 'Y'
501 AND sysdate BETWEEN h.start_date_active AND NVL(h.end_date_active, hr_general.end_of_time) );
502 --
503 l_dummy varchar2(5) := NULL;
504 --
505 BEGIN
506 g_debug :=hr_utility.debug_enabled;
507 if g_debug then
508 l_proc := g_package||'chk_criteria_type';
509 hr_utility.set_location('Entering:'||l_proc, 5);
510 end if;
511
512 hr_api.mandatory_arg_error
513 (p_api_name => l_proc
514 ,p_argument => 'CRITERIA_TYPE'
515 ,p_argument_value => p_criteria_type
516 );
517
518 if g_debug then
519 hr_utility.set_location('Processing:'||l_proc, 10);
520 end if;
521 --
522 -- check that the criteria type is valid
523 --
524 OPEN csr_chk_criteria_type;
525 FETCH csr_chk_criteria_type INTO l_dummy;
526
527 if g_debug then
528 hr_utility.set_location('Processing:'||l_proc, 20);
529 end if;
530
531 IF csr_chk_criteria_type%NOTFOUND
532 THEN
533 if g_debug then
534 hr_utility.set_location('Processing:'||l_proc, 30);
535 end if;
536
537 hr_utility.set_message(809, 'HXC_TEGQC_INV_CRITERIA_TYPE');
538 hr_utility.raise_error;
539 END IF;
540
541 if g_debug then
542 hr_utility.set_location('Processing:'||l_proc, 40);
543 end if;
544
545 CLOSE csr_chk_criteria_type;
546
547 if g_debug then
548 hr_utility.set_location('Leaving:'||l_proc, 50);
549 end if;
550
551 END chk_criteria_type;
552
553 -- ----------------------------------------------------------------------------
554 -- |-----------------------< chk_criteria_id >---------------------------------|
555 -- ----------------------------------------------------------------------------
556
557 Procedure chk_criteria_id
558 (
559 p_criteria_type in varchar2
560 ,p_criteria_id in number
561 ) IS
562
563 l_proc varchar2(32) := 'chk_criteria_id';
564 l_dummy varchar2(1);
565
566 CURSOR csr_chk_assignment_id IS
567 SELECT 'x'
568 FROM dual
569 WHERE EXISTS ( select 'x'
570 FROM per_assignments_f asg
571 WHERE asg.assignment_id = p_criteria_id );
572 -- GAZ - anymore specific ????
573
574 CURSOR csr_chk_person_id IS
575 SELECT 'x'
576 FROM dual
577 WHERE EXISTS ( select 'x'
578 FROM per_people_f p
579 WHERE p.person_id = p_criteria_id );
580 -- GAZ - anymore specific ????
581
582 BEGIN
583
584 IF ( p_criteria_type = 'ASSIGNMENT' )
585 THEN
586
587 OPEN csr_chk_assignment_id;
588 FETCH csr_chk_assignment_id INTO l_dummy;
589
590 IF ( csr_chk_assignment_id%NOTFOUND )
591 THEN
592 hr_utility.set_message(809, 'HXC_TEGQC_INVALID_CRITERIA_ID');
593 hr_utility.raise_error;
594 END IF;
595
596 CLOSE csr_chk_assignment_id;
597
598 ELSIF ( p_criteria_type = 'PERSON' )
599 THEN
600
601 OPEN csr_chk_person_id;
602 FETCH csr_chk_person_id INTO l_dummy;
603
604 IF ( csr_chk_person_id%NOTFOUND )
605 THEN
606 hr_utility.set_message(809, 'HXC_TEGQC_INVALID_CRITERIA_ID');
607 hr_utility.raise_error;
608 END IF;
609
610 CLOSE csr_chk_person_id;
611
612 END IF;
613
614 END chk_criteria_id;
615
616 -- ----------------------------------------------------------------------------
617 -- |-----------------------< chk_tk_group_query_id >--------------------------|
618 -- ----------------------------------------------------------------------------
619 --
620 -- Description:
621 -- SEE DESCRIPTION IN HEADER
622 --
623 -- ----------------------------------------------------------------------------
624 Procedure chk_tk_group_query_id
625 (
626 p_tk_group_query_id in number
627 ) IS
628
629 l_proc varchar2(72) ;
630 l_dummy varchar2(1) := NULL;
631
632 CURSOR csr_chk_tk_group_query_id IS
633 SELECT 'x'
634 FROM dual
635 WHERE EXISTS ( SELECT 'x'
636 FROM hxc_tk_group_queries tkgq
637 WHERE tkgq.tk_group_query_id = p_tk_group_query_id );
638
639 BEGIN
640 g_debug :=hr_utility.debug_enabled;
641 if g_debug then
642 l_proc := 'chk_tk_group_query_id';
643 hr_utility.set_location('Processing:'||l_proc, 5);
644 end if;
645
646 hr_api.mandatory_arg_error
647 (p_api_name => l_proc
648 ,p_argument => 'TK_GROUP_QUERY_ID'
649 ,p_argument_value => p_tk_group_query_id
650 );
651
652 if g_debug then
653 hr_utility.set_location('Processing:'||l_proc, 10);
654 end if;
655
656 OPEN csr_chk_tk_group_query_id;
657 FETCH csr_chk_tk_group_query_id INTO l_dummy;
658
659 if g_debug then
660 hr_utility.set_location('Processing:'||l_proc, 20);
661 end if;
662
663 IF csr_chk_tk_group_query_id%NOTFOUND
664 THEN
665 if g_debug then
666 hr_utility.set_location('Processing:'||l_proc, 30);
667 end if;
668
669 hr_utility.set_message(809, 'HXC_TKGQC_INV_TK_GRP_QUERY_ID');
670 hr_utility.raise_error;
671 END IF;
672
673 CLOSE csr_chk_tk_group_query_id;
674
675 if g_debug then
676 hr_utility.set_location('Processing:'||l_proc, 40);
677 end if;
678
679 END chk_tk_group_query_id;
680
681 -- ----------------------------------------------------------------------------
682 -- |-----------------------< chk_delete >-------------------------------------|
683 -- ----------------------------------------------------------------------------
684 --
685 -- Description:
686 --
687 -- SEE DESCRIPTION IN PACKAGE HEADER
688 --
689 -- ----------------------------------------------------------------------------
690 Procedure chk_delete
691 (
692 p_tk_group_query_criteria_id in number
693 ) IS
694 BEGIN
695 null;
696 END chk_delete;
697 --
698
699 -- ----------------------------------------------------------------------------
700 -- |-------------------------< get_criteria >---------------------------------|
701 -- ----------------------------------------------------------------------------
702 --
703 -- Description:
704 --
705 -- SEE DESCRIPTION IN PACKAGE HEADER
706 --
707 -- ----------------------------------------------------------------------------
708 FUNCTION get_criteria ( p_position number
709 , p_criteria_type varchar2
710 , p_criteria_id number )
711 RETURN varchar2 IS
712
713 l_criteria varchar2(80);
714
715 CURSOR csr_get_assignment_number IS
716 SELECT asg.assignment_number
717 FROM per_assignments asg
718 WHERE asg.assignment_id = p_criteria_id;
719
720 CURSOR csr_get_employee_number IS
721 SELECT p.employee_number
722 FROM per_people p
723 WHERE p.person_id = p_criteria_id;
724
725 CURSOR csr_get_full_name IS
726 SELECT ppf.full_name
727 FROM per_assignments_f asg,fnd_sessions ss,per_people_f ppf
728 WHERE asg.effective_start_date <= ss.effective_date
729 AND asg.effective_end_date >= ss.effective_date
730 AND ss.session_id = USERENV ('sessionid')
731 AND ppf.effective_start_date <= ss.effective_date
732 AND ppf.effective_end_date >= ss.effective_date
733 AND ppf.person_id = asg.person_id
734 AND asg.assignment_id = p_criteria_id;
735
736
737
738 CURSOR csr_get_person_full_name IS
739 SELECT p.full_name
740 FROM per_people p
741 WHERE p.person_id = p_criteria_id;
742
743 BEGIN
744
745 IF ( p_criteria_type = 'ASSIGNMENT' AND p_position = 1 )
746 THEN
747
748 OPEN csr_get_full_name;
749 FETCH csr_get_full_name INTO l_criteria;
750 CLOSE csr_get_full_name;
751
752 ELSIF ( p_criteria_type = 'ASSIGNMENT' AND p_position = 2 )
753 THEN
754
755 OPEN csr_get_assignment_number;
756 FETCH csr_get_assignment_number INTO l_criteria;
757 CLOSE csr_get_assignment_number;
758
759 ELSIF ( p_criteria_type = 'PERSON' AND p_position = 1 )
760 THEN
761
762 OPEN csr_get_person_full_name;
763 FETCH csr_get_person_full_name INTO l_criteria;
764 CLOSE csr_get_person_full_name;
765
766
767 ELSIF ( p_criteria_type = 'PERSON' AND p_position = 2 )
768 THEN
769
770 OPEN csr_get_employee_number;
771 FETCH csr_get_employee_number INTO l_criteria;
772 CLOSE csr_get_employee_number;
773
774 ELSE
775
776 hr_utility.set_message(809, 'HXC_TKGQC_INV_PARAMS_CRITERIA');
777 hr_utility.raise_error;
778
779 END IF;
780
781 RETURN l_criteria;
782
783 END get_criteria;
784
785
786
787 -- ----------------------------------------------------------------------------
788 -- |-----------------------< get_criteria_unique >----------------------------|
789 -- ----------------------------------------------------------------------------
790 --
791 -- Description:
792 --
793 -- SEE DESCRIPTION IN PACKAGE HEADER
794 --
795 -- ----------------------------------------------------------------------------
796
797 PROCEDURE chk_criteria_unique (
798 p_tk_group_query_criteria_id in number
799 , p_tk_group_query_id in number
800 , p_criteria_type varchar2
801 , p_criteria_id number ) IS
802
803 CURSOR csr_chk_unique IS
804 SELECT 'x'
805 FROM sys.dual
806 WHERE EXISTS (
807 SELECT 'x'
808 FROM hxc_tk_group_query_criteria tkgqc
809 WHERE tkgqc.tk_group_query_id = p_tk_group_query_id
810 AND tkgqc.criteria_id = p_criteria_id
811 AND tkgqc.criteria_type = p_criteria_type
812 AND
813 ( tkgqc.tk_group_query_criteria_id <> p_tk_group_query_criteria_id OR
814 p_tk_group_query_criteria_id IS NULL ) );
815
816 l_dummy VARCHAR2(1);
817
818 l_proc varchar2(72) ;
819
820 BEGIN
821 g_debug :=hr_utility.debug_enabled;
822 if g_debug then
823 l_proc := 'chk_criteria_unique';
824 hr_utility.set_location('Entering:'||l_proc, 5);
825 end if;
826
827 OPEN csr_chk_unique;
828 FETCH csr_chk_unique INTO l_dummy;
829
830 if g_debug then
831 hr_utility.set_location('Processing:'||l_proc, 10);
832 end if;
833
834 IF ( csr_chk_unique%FOUND )
835 THEN
836
837 if g_debug then
838 hr_utility.set_location('Processing:'||l_proc, 20);
839 end if;
840
841 hr_utility.set_message(809, 'HXC_TKGQC_DUP_CRITERIA');
842 hr_utility.raise_error;
843
844 END IF;
845
846 if g_debug then
847 hr_utility.set_location('Leaving:'||l_proc, 30);
848 end if;
849
850 END chk_criteria_unique;
851
852 -- ----------------------------------------------------------------------------
853 -- |-----------------------< get_tc_period >----------------------------------|
854 -- ----------------------------------------------------------------------------
855 --
856 -- Description:
857 --
858 -- SEE DESCRIPTION IN PACKAGE HEADER
859 --
860 -- ----------------------------------------------------------------------------
861 FUNCTION get_tc_period ( p_resource_id number )
862 RETURN varchar2 IS
863
864 l_tc_period hxc_recurring_periods.name%TYPE;
865 l_pref_tab hxc_preference_evaluation.t_pref_table;
866 l_recurring_period_id hxc_pref_hierarchies.attribute1%TYPE;
867
868 l_session_date DATE;
869
870 CURSOR csr_get_tc_period ( p_rp_id VARCHAR2 ) IS
871 SELECT rp.name
872 FROM hxc_recurring_periods rp
873 WHERE rp.recurring_period_id = TO_NUMBER(p_rp_id);
874
875 CURSOR csr_get_effective_date IS
876 SELECT effective_date
877 FROM fnd_sessions
878 WHERE session_id = USERENV('sessionid');
879
880 BEGIN
881
882 /*
883
884 -- decided not to get the pref for all time
885 -- get preference as of session date
886
887 hxc_preference_evaluation.resource_preferences(
888 p_resource_id => p_resource_id
889 , p_start_evaluation_date => hr_general.start_of_time
890 , p_end_evaluation_date => hr_general.end_of_time
891 , p_pref_table => l_pref_tab );
892 */
893
894 OPEN csr_get_effective_date;
895 FETCH csr_get_effective_date INTO l_session_date;
896 CLOSE csr_get_effective_date;
897
898 l_recurring_period_id :=
899 hxc_preference_evaluation.resource_preferences(
900 p_resource_id => p_resource_id
901 , p_pref_code => 'TC_W_TCRD_PERIOD'
902 , p_attribute_n => 1
903 , p_evaluation_date => l_session_date );
904
905 OPEN csr_get_tc_period ( l_recurring_period_id );
906 FETCH csr_get_tc_period INTO l_tc_period;
907 CLOSE csr_get_tc_period;
908
909 IF ( l_tc_period IS NULL )
910 THEN
911
912 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
913 fnd_message.set_token('PROCEDURE', 'get_tc_period');
914 fnd_message.set_token('STEP','no Timecard Period Exists for pref value');
915 fnd_message.raise_error;
916
917 END IF;
918
919 RETURN l_tc_period;
920
921 END get_tc_period;
922
923 -- ----------------------------------------------------------------------------
924 -- |-----------------------< check_audit_enabled >----------------------------------|
925 -- ----------------------------------------------------------------------------
926 --
927 -- Description:
928 --
929 -- SEE DESCRIPTION IN PACKAGE HEADER
930 --
931 -- ----------------------------------------------------------------------------
932
933 FUNCTION check_audit_enabled ( p_resource_id number )
934 RETURN VARCHAR2 IS
935
936 l_pref_tab hxc_preference_evaluation.t_pref_table;
937 l_recurring_period_id hxc_pref_hierarchies.attribute1%TYPE;
938 l_session_date DATE;
939
940 l_audit VARCHAR2(150);
941
942 CURSOR csr_get_effective_date IS
943 SELECT effective_date
944 FROM fnd_sessions
945 WHERE session_id = USERENV('SESSIONID');
946
947 BEGIN
948
949 OPEN csr_get_effective_date;
950 FETCH csr_get_effective_date INTO l_session_date;
951 CLOSE csr_get_effective_date;
952
953
954 IF (hxc_preference_evaluation.resource_preferences(
955 p_resource_id => p_resource_id
956 , p_pref_code => 'TS_PER_AUDIT_REQUIREMENTS'
957 , p_attribute_n => 1
958 , p_evaluation_date => l_session_date )IS NOT NULL) THEN
959
960 l_audit := hr_bis.bis_decode_lookup ('YES_NO','Y');
961 ELSE
962 l_audit := hr_bis.bis_decode_lookup ('YES_NO','N');
963 END IF;
964
965 RETURN l_audit;
966
967 EXCEPTION
968
969 WHEN OTHERS THEN
970
971 RETURN NULL;
972
973 END CHECK_AUDIT_ENABLED;
974
975 -- ----------------------------------------------------------------------------
976 -- |-----------------------< tc_period_ok >-----------------------------------|
977 -- ----------------------------------------------------------------------------
978 --
979 -- Description:
980 --
981 -- SEE DESCRIPTION IN PACKAGE HEADER
982 --
983 -- ----------------------------------------------------------------------------
984 FUNCTION tc_period_ok ( p_resource_id number
985 , p_period_type varchar2
986 , p_duration_in_days number )
987 RETURN BOOLEAN IS
988
989 l_recurring_period_id hxc_pref_hierarchies.attribute1%TYPE;
990
991 l_tc_period_ok BOOLEAN := FALSE;
992
993 CURSOR csr_get_tc_period_type ( p_rp_id VARCHAR2 ) IS
994 SELECT rp.period_type
995 , rp.duration_in_days
996 FROM hxc_recurring_periods rp
997 WHERE rp.recurring_period_id = TO_NUMBER(p_rp_id);
998
999 l_period_rec csr_get_tc_period_type%ROWTYPE;
1000
1001
1002 BEGIN
1003
1004 l_recurring_period_id :=
1005 hxc_preference_evaluation.resource_preferences(
1006 p_resource_id => p_resource_id
1007 , p_pref_code => 'TC_W_TCRD_PERIOD'
1008 , p_attribute_n => 1 );
1009
1010 OPEN csr_get_tc_period_type ( l_recurring_period_id );
1011 FETCH csr_get_tc_period_type INTO l_period_rec;
1012 CLOSE csr_get_tc_period_type;
1013
1014 IF ( l_period_rec.period_type IS NOT NULL AND ( l_period_rec.period_type = p_period_type ) )
1015 THEN
1016
1017 l_tc_period_ok := TRUE;
1018
1019 ELSIF ( l_period_rec.period_type IS NULL AND ( l_period_rec.duration_in_days = p_duration_in_days ) AND
1020 l_period_rec.duration_in_days IS NOT NULL )
1021 THEN
1022
1023 l_tc_period_ok := TRUE;
1024
1025 END IF;
1026
1027
1028 RETURN l_tc_period_ok;
1029
1030 END tc_period_ok;
1031
1032 END hxc_tk_grp_query_criteria_api;