1 Package body hxc_tk_group_query_api as
2 /* $Header: hxctkgqapi.pkb 120.2 2005/09/23 09:20:33 rchennur noship $ */
3 --
4 -- Package Variables
5 --
6 g_debug boolean := hr_utility.debug_enabled;
7 g_package varchar2(33) := ' hxc_tk_group_query_api.';
8
9 -- ----------------------------------------------------------------------------
10 -- |--------------------------< create_tk_group_query >---------------------|
11 -- ----------------------------------------------------------------------------
12 --
13 --
14 -- Description:
15 --
16 -- This API creates a timekeeper group query with a given name
17 --
18 -- Prerequisites:
19 --
20 -- None
21 --
22 -- In Parameters:
23 -- Name Reqd Type Description
24 --
25 -- p_validate No boolean If TRUE then the database
26 -- remains unchanged. If FALSE
27 -- then a new tk group query
28 -- is created. Default is FALSE.
29 -- p_tk_group_query_id No number Primary Key for timekeeper group query group query
30 -- p_tk_group_id Yes number Foreign Key for timekeeper group query group
31 -- p_object_version_number No number Object Version Number
32 -- p_group_query_name Yes varchar2 tk group Name for the tk_group_query
33 -- p_include_exclude Yes varchar2 Include or Exclude flag
34 -- p_system_user Yes varchar2 System or User flag
35 --
36 -- Post Success:
37 --
38 -- when the tk_group_query has been created successfully the following
39 -- out parameters are set.
40 --
41 -- Name Type Description
42 --
43 -- p_tk_group_query_id Number Primary Key for the new tk group query
44 -- p_object_version_number Number Object version number for the
45 -- new tk group
46 --
47 -- Post Failure:
48 --
49 -- The timekeeper group query will not be inserted and an application error raised
50 --
51 -- Access Status:
52 -- Public.
53 --
54 --
55 procedure create_tk_group_query
56 (p_validate in boolean default false
57 ,p_tk_group_query_id in out nocopy number
58 ,p_tk_group_id in number
59 ,p_object_version_number in out nocopy number
60 ,p_group_query_name in varchar2
61 ,p_include_exclude in varchar2
62 ,p_system_user in varchar2
63 ) is
64 --
65 -- Declare cursors and local variables
66 --
67 l_proc varchar2(72) ;
68 l_object_version_number hxc_tk_group_queries.object_version_number%TYPE;
69 l_tk_group_query_id hxc_tk_group_queries.tk_group_query_id%TYPE;
70 --
71 begin
72 g_debug :=hr_utility.debug_enabled;
73 --
74 --
75 if g_debug then
76 l_proc := g_package||' create_tk_group_query';
77 hr_utility.set_location('Entering:'|| l_proc, 10);
78 end if;
79 --
80 -- Issue a savepoint if operating in validation only mode
81 --
82 savepoint create_tk_group_query;
83 --
84 hxc_tk_group_query_api.chk_name
85 ( p_group_query_name => p_group_query_name
86 , p_tk_group_id => p_tk_group_id
87 , p_tk_group_query_id => p_tk_group_query_id );
88
89 hxc_tk_group_query_api.chk_tk_group_id ( p_tk_group_id => p_tk_group_id );
90
91 if g_debug then
92 hr_utility.set_location(l_proc, 20);
93 end if;
94 --
95 -- Call Before Process User Hook
96 --
97 begin
98 hxc_tk_group_query_BK_1.create_tk_group_query_b
99 (p_tk_group_query_id => p_tk_group_query_id
100 ,p_tk_group_id => p_tk_group_id
101 ,p_object_version_number => p_object_version_number
102 ,p_group_query_name => p_group_query_name
103 ,p_include_exclude => p_include_exclude
104 ,p_system_user => p_system_user
105 );
106 exception
107 when hr_api.cannot_find_prog_unit then
108 hr_api.cannot_find_prog_unit_error
109 (p_module_name => 'create_tk_group_query'
110 ,p_hook_type => 'BP'
111 );
112 end;
113 --
114 if g_debug then
115 hr_utility.set_location(l_proc, 30);
116 end if;
117 --
118 -- Process Logic
119 --
120 --
121 if g_debug then
122 hr_utility.set_location(l_proc, 40);
123 end if;
124 --
125 -- call row handler
126 --
127 hxc_tkgq_ins.ins (
128 p_group_query_name => p_group_query_name
129 ,p_tk_group_query_id => l_tk_group_query_id
130 ,p_tk_group_id => p_tk_group_id
131 ,p_include_exclude => p_include_exclude
132 ,p_system_user => p_system_user
133 ,p_object_version_number => l_object_version_number );
134 --
135 if g_debug then
136 hr_utility.set_location(l_proc, 50);
137 end if;
138 --
139 -- Call After Process User Hook
140 --
141 begin
142 hxc_tk_group_query_BK_1.create_tk_group_query_a
143 (p_tk_group_query_id => l_tk_group_query_id
144 ,p_tk_group_id => p_tk_group_id
145 ,p_object_version_number => l_object_version_number
146 ,p_group_query_name => p_group_query_name
147 ,p_include_exclude => p_include_exclude
148 ,p_system_user => p_system_user
149 );
150 exception
151 when hr_api.cannot_find_prog_unit then
152 hr_api.cannot_find_prog_unit_error
153 (p_module_name => 'create_tk_group_query'
154 ,p_hook_type => 'AP'
155 );
156 end;
157 --
158 if g_debug then
159 hr_utility.set_location(l_proc, 60);
160 end if;
161 --
162 -- When in validation only mode raise the Validate_Enabled exception
163 --
164 if p_validate then
165 raise hr_api.validate_enabled;
166 end if;
167 --
168 if g_debug then
169 hr_utility.set_location(' Leaving:'||l_proc, 70);
170 end if;
171 --
172 -- Set all output arguments
173 --
174 p_tk_group_query_id := l_tk_group_query_id;
175 p_object_version_number := l_object_version_number;
176 --
177 exception
178 --
179 when hr_api.validate_enabled then
180 --
181 -- As the Validate_Enabled exception has been raised
182 -- we must rollback to the savepoint
183 --
184 ROLLBACK TO create_tk_group_query;
185 --
186 -- Only set output warning arguments
187 -- (Any key or derived arguments must be set to null
188 -- when validation only mode is being used.)
189 --
190 p_tk_group_query_id := null;
191 p_object_version_number := null;
192 --
193 if g_debug then
194 hr_utility.set_location(' Leaving:'||l_proc, 80);
195 end if;
196 --
197 when others then
198 --
199 -- A validation or unexpected error has occured
200 --
201 if g_debug then
202 hr_utility.trace('In exeception');
203 end if;
204 ROLLBACK TO create_tk_group_query;
205 raise;
206 --
207 END create_tk_group_query;
208
209
210
211 -- ----------------------------------------------------------------------------
212 -- |-------------------------<update_tk_group_query>------------------------|
213 -- ----------------------------------------------------------------------------
214 --
215 --
216 -- Description:
217 --
218 -- This API updates an existing Tk_Group_Query with a given name
219 --
220 -- Prerequisites:
221 --
222 -- None
223 --
224 -- In Parameters:
225 -- Name Reqd Type Description
226 --
227 -- p_validate No boolean If TRUE then the database
228 -- remains unchanged. If FALSE
229 -- then the tk group query
230 -- is updated. Default is FALSE.
231 -- p_tk_group_id Yes number Primary Key for entity
232 -- p_tk_group_query_id Yes number Foreign Key for entity
233 -- p_object_version_number Yes number Object Version Number
234 -- p_group_query_name Yes varchar2 tk group Name for the timekeeper group query
235 -- p_include_exclude Yes varchar2 Include or Exclude flag
236 -- p_system_user Yes varchar2 System or User flag
237 --
238 -- Post Success:
239 --
240 -- when the timekeeper group query has been updated successfully the following
241 -- out parameters are set.
242 --
243 -- Name Type Description
244 --
245 -- p_object_version_number Number Object version number for the
246 -- updated tk group query
247 --
248 -- Post Failure:
249 --
250 -- The tk_group_query will not be updated and an application error raised
251 --
252 -- Access Status:
253 -- Public.
254 --
255 --
256 procedure update_tk_group_query
257 (p_validate in boolean default false
258 ,p_tk_group_id in number
259 ,p_tk_group_query_id in number
260 ,p_object_version_number in out nocopy number
261 ,p_group_query_name in varchar2
262 ,p_include_exclude in varchar2
263 ,p_system_user in varchar2)
264 IS
265 --
266 -- Declare cursors and local variables
267 --
268 l_proc varchar2(72) ;
269 l_object_version_number hxc_tk_group_queries.object_version_number%TYPE := p_object_version_number;
270 --
271 begin
272 --
273 g_debug :=hr_utility.debug_enabled;
274 if g_debug then
275 l_proc := g_package||' update_tk_group_query';
276 hr_utility.set_location('Entering:'|| l_proc, 10);
277 end if;
278 --
279 -- Issue a savepoint if operating in validation only mode
280 --
281 savepoint update_tk_group_query;
282 --
283 hxc_tk_group_query_api.chk_name
284 ( p_group_query_name => p_group_query_name
285 , p_tk_group_id => p_tk_group_id
286 , p_tk_group_query_id => p_tk_group_query_id );
287
288 hxc_tk_group_query_api.chk_tk_group_id ( p_tk_group_id => p_tk_group_id );
289
290 if g_debug then
291 hr_utility.set_location(l_proc, 20);
292 end if;
293 --
294 -- Call Before Process User Hook
295 --
296 begin
297 hxc_tk_group_query_BK_2.update_tk_group_query_b
298 (p_tk_group_query_id => p_tk_group_query_id
299 ,p_tk_group_id => p_tk_group_id
300 ,p_object_version_number => p_object_version_number
301 ,p_group_query_name => p_group_query_name
302 ,p_include_exclude => p_include_exclude
303 ,p_system_user => p_system_user
304 );
305 exception
306 when hr_api.cannot_find_prog_unit then
307 hr_api.cannot_find_prog_unit_error
308 (p_module_name => 'update_tk_group_query'
309 ,p_hook_type => 'BP'
310 );
311 end;
312 --
313 if g_debug then
314 hr_utility.set_location(l_proc, 30);
315 end if;
316 --
317 -- Process Logic
318 --
319 -- call row handler
320 --
321 hxc_tkgq_upd.upd (
322 p_group_query_name => p_group_query_name
323 ,p_tk_group_query_id => p_tk_group_query_id
324 ,p_tk_group_id => p_tk_group_id
325 ,p_include_exclude => p_include_exclude
326 ,p_system_user => p_system_user
327 ,p_object_version_number => l_object_version_number );
328 --
329 --
330 if g_debug then
331 hr_utility.set_location(l_proc, 40);
332 end if;
333 --
334 -- Call After Process User Hook
335 --
336 begin
337 hxc_tk_group_query_BK_2.update_tk_group_query_a
338 (p_tk_group_query_id => p_tk_group_query_id
339 ,p_tk_group_id => p_tk_group_id
340 ,p_object_version_number => l_object_version_number
341 ,p_group_query_name => p_group_query_name
342 ,p_include_exclude => p_include_exclude
343 ,p_system_user => p_system_user
344 );
345 exception
346 when hr_api.cannot_find_prog_unit then
347 hr_api.cannot_find_prog_unit_error
348 (p_module_name => 'update_tk_group_query'
349 ,p_hook_type => 'AP'
350 );
351 end;
352 --
353 if g_debug then
354 hr_utility.set_location(l_proc, 50);
355 end if;
356 --
357 -- When in validation only mode raise the Validate_Enabled exception
358 --
359 if p_validate then
360 raise hr_api.validate_enabled;
361 end if;
362 --
363 if g_debug then
364 hr_utility.set_location(' Leaving:'||l_proc, 60);
365 end if;
366 --
367 -- Set all output arguments
368 --
369 p_object_version_number := l_object_version_number;
370 --
371 exception
372 --
373 when hr_api.validate_enabled then
374 --
375 -- As the Validate_Enabled exception has been raised
376 -- we must rollback to the savepoint
377 --
378 ROLLBACK TO update_tk_group_query;
379 --
380 -- Only set output warning arguments
381 -- (Any key or derived arguments must be set to null
382 -- when validation only mode is being used.)
383 --
384 p_object_version_number := null;
385 --
386 if g_debug then
387 hr_utility.set_location(' Leaving:'||l_proc, 60);
388 end if;
389 --
390 when others then
391 --
392 -- A validation or unexpected error has occured
393 --
394 if g_debug then
395 hr_utility.trace('In exeception');
396 end if;
397 ROLLBACK TO update_tk_group_query;
398 raise;
399
400 end update_tk_group_query;
401 --
402 -- ----------------------------------------------------------------------------
403 -- |----------------------< delete_tk_group_query >-------------------------|
404 -- ----------------------------------------------------------------------------
405 --
406 --
407 -- Description:
408 --
409 -- This API deletes an existing Tk_Group_Query
410 --
411 -- Prerequisites:
412 --
413 -- None
414 --
415 -- In Parameters:
416 -- Name Reqd Type Description
417 --
418 -- p_validate No boolean If TRUE then the database
419 -- remains unchanged. If FALSE
420 -- then the tk_group_query
421 -- is deleted. Default is FALSE.
422 -- p_tk_group_query_id Yes number Primary Key for entity
423 -- p_object_version_number Yes number Object Version Number
424 --
425 -- Post Success:
426 --
427 -- when the timekeeper group query has been deleted successfully the process
428 -- completes with success.
429 --
430 -- Post Failure:
431 --
432 -- The tk_group_query will not be deleted and an application error raised
433 --
434 -- Access Status:
435 -- Public.
436 --
437 --
438 procedure delete_tk_group_query
439 (p_validate in boolean default false
440 ,p_tk_group_query_id in number
441 ,p_object_version_number in number
442 ) is
443 --
444 -- Declare cursors and local variables
445 --
446 l_proc varchar2(72) ;
447 --
448 begin
449 g_debug :=hr_utility.debug_enabled;
450 --
451
452 if g_debug then
453 l_proc := g_package||'delete_tk_group_query';
454 hr_utility.set_location('Entering:'|| l_proc, 10);
455 end if;
456 --
457 -- Issue a savepoint if operating in validation only mode
458 --
459 savepoint delete_tk_group_query;
460 --
461 hxc_tk_group_query_api.chk_delete (
462 p_tk_group_query_id => p_tk_group_query_id );
463
464 if g_debug then
465 hr_utility.set_location(l_proc, 20);
466 end if;
467 --
468 -- Call Before Process User Hook
469 --
470 begin
471 --
472 hxc_tk_group_query_BK_3.delete_tk_group_query_b
473 (p_tk_group_query_id => p_tk_group_query_id
474 ,p_object_version_number => p_object_version_number
475 );
476 exception
477 when hr_api.cannot_find_prog_unit then
478 hr_api.cannot_find_prog_unit_error
479 (p_module_name => 'delete_tk_group_query'
480 ,p_hook_type => 'BP'
481 );
482 end;
483 --
484 if g_debug then
485 hr_utility.set_location(l_proc, 30);
486 end if;
487 --
488 -- Process Logic
489 --
490 hxc_tkgq_del.del
491 (
492 p_tk_group_query_id => p_tk_group_query_id
493 ,p_object_version_number => p_object_version_number
494 );
495 --
496 if g_debug then
497 hr_utility.set_location(l_proc, 40);
498 end if;
499 --
500 -- Call After Process User Hook
501 --
502 begin
503 --
504 hxc_tk_group_query_BK_3.delete_tk_group_query_a
505 (p_tk_group_query_id => p_tk_group_query_id
506 ,p_object_version_number => p_object_version_number
507 );
508 exception
509 when hr_api.cannot_find_prog_unit then
510 hr_api.cannot_find_prog_unit_error
511 (p_module_name => 'delete_tk_group_query'
512 ,p_hook_type => 'AP'
513 );
514 end;
515 --
516 -- When in validation only mode raise the Validate_Enabled exception
517 --
518 if p_validate then
519 raise hr_api.validate_enabled;
520 end if;
521 --
522 if g_debug then
523 hr_utility.set_location(' Leaving:'||l_proc, 50);
524 end if;
525 --
526 exception
527 --
528 when hr_api.validate_enabled then
529 --
530 -- As the Validate_Enabled exception has been raised
531 -- we must rollback to the savepoint
532 --
533 ROLLBACK TO delete_tk_group_query;
534 --
535 when others then
536 --
537 -- A validation or unexpected error has occured
538 --
539 ROLLBACK TO delete_tk_group_query;
540 raise;
541 --
542 end delete_tk_group_query;
543 --
544 -- ----------------------------------------------------------------------------
545 -- |-----------------------< chk_name >---------------------------------------|
546 -- ----------------------------------------------------------------------------
547 --
548 -- Description:
549 --
550 -- SEE DESCRIPTION IN PACKAGE HEADER
551 --
552 -- Note:
553 -- This procedure is called from the client
554 --
555 -- ----------------------------------------------------------------------------
556 Procedure chk_name
557 (
558 p_group_query_name in varchar2
559 ,p_tk_group_id in number
560 ,p_tk_group_query_id in number
561 ) IS
562
563 l_proc varchar2(72) ;
564 --
565 -- cursor to check name is unique
566 --
567 CURSOR csr_chk_name IS
568 SELECT 'error'
569 FROM sys.dual
570 WHERE EXISTS (
571 SELECT 'x'
572 FROM hxc_tk_group_queries tegq
573 WHERE tegq.group_query_name = p_group_query_name
574 AND tegq.tk_group_id = p_tk_group_id
575 AND
576 ( tegq.tk_group_query_id <> p_tk_group_query_id OR
577 p_tk_group_query_id IS NULL ) );
578 --
579 l_dup_name varchar2(5) := NULL;
580 --
581 BEGIN
582 g_debug :=hr_utility.debug_enabled;
583 if g_debug then
584 l_proc := g_package||'chk_name';
585 hr_utility.trace('Params are: ');
586 hr_utility.trace('tk group id is : '||to_char(p_tk_Group_id));
587 hr_utility.trace('tk group query id is : '||to_char(p_tk_Group_query_id));
588 hr_utility.set_location('Entering:'||l_proc, 5);
589 end if;
590 --
591 -- check that the name has been entered
592 --
593 IF ( p_group_query_name IS NULL )
594 THEN
595 --
596
597 hr_utility.set_message(809, 'HXC_TEGQ_NAME_MAND');
598 hr_utility.raise_error;
599 --
600 END IF;
601 if g_debug then
602 hr_utility.set_location('Processing:'||l_proc, 10);
603 end if;
604 --
605 -- check that the name is unique
606 --
607 OPEN csr_chk_name;
608 FETCH csr_chk_name INTO l_dup_name;
609 CLOSE csr_chk_name;
610 --
611 IF ( l_dup_name IS NOT NULL )
612 THEN
613 --
614 hr_utility.set_message(809, 'HXC_TEGQ_NAME_DUP');
615 hr_utility.raise_error;
616 --
617 END IF;
618 --
619 if g_debug then
620 hr_utility.set_location('Leaving:'||l_proc, 20);
621 end if;
622 --
623 END chk_name;
624 --
625 -- ----------------------------------------------------------------------------
626 -- |-----------------------< chk_tk_group_id >--------------------------------|
627 -- ----------------------------------------------------------------------------
628 --
629 -- Description:
630 -- SEE DESCRIPTION IN HEADER
631 --
632 -- ----------------------------------------------------------------------------
633 Procedure chk_tk_group_id
634 (
635 p_tk_group_id in number
636 ) IS
637
638 l_proc varchar2(72) ;
639 l_dummy varchar2(1) := NULL;
640
641 CURSOR csr_chk_tk_group_id IS
642 SELECT 'x'
643 FROM dual
644 WHERE EXISTS ( SELECT 'x'
645 FROM hxc_tk_groups tkg
646 WHERE tkg.tk_group_id = p_tk_group_id );
647
648 BEGIN
649 g_debug :=hr_utility.debug_enabled;
650 if g_debug then
651 l_proc := 'chk_tk_group_id';
652 hr_utility.set_location('Entering:'||l_proc, 10);
653 end if;
654 hr_api.mandatory_arg_error
655 (p_api_name => l_proc
656 ,p_argument => 'TK_GROUP_ID'
657 ,p_argument_value => p_tk_group_id
658 );
659
660 OPEN csr_chk_tk_group_id;
661 FETCH csr_chk_tk_group_id INTO l_dummy;
662
663 IF csr_chk_tk_group_id%NOTFOUND
664 THEN
665 hr_utility.set_message(809, 'HXC_TKGQ_INVALID_TK_GROUP_ID');
666 hr_utility.raise_error;
667 END IF;
668
669 CLOSE csr_chk_tk_group_id;
670
671 if g_debug then
672 hr_utility.set_location('Leaving:'||l_proc, 20);
673 end if;
674 END chk_tk_group_id;
675
676 -- ----------------------------------------------------------------------------
677 -- |-----------------------< chk_delete >-------------------------------------|
678 -- ----------------------------------------------------------------------------
679 --
680 -- Description:
681 --
682 -- SEE DESCRIPTION IN PACKAGE HEADER
683 --
684 -- ----------------------------------------------------------------------------
685 Procedure chk_delete
686 (
687 p_tk_group_query_id in number
688 ) IS
689 BEGIN
690 null;
691 END chk_delete;
692 --
693 -- ----------------------------------------------------------------------------
694 -- |-----------------------< maintain_tk_group_query >------------------------|
695 -- ----------------------------------------------------------------------------
696 --
697 -- Description:
698 --
699 -- SEE DESCRIPTION IN PACKAGE HEADER
700 --
701 -- GAZ - this will need to change for the full blown solution - namely,
702 -- need to pass in the tk grpup query name
703 --
704 -- ----------------------------------------------------------------------------
705 Procedure maintain_tk_group_query
706 (
707 p_tk_group_query_id in out nocopy number
708 ,p_tk_group_id in number
709 ) IS
710
711
712 l_proc varchar2(72) ;
713
714 l_tk_group_query_id hxc_tk_group_queries.tk_group_query_id%TYPE;
715 l_object_version_number hxc_tk_group_queries.object_version_number%TYPE;
716
717 CURSOR csr_chk_group_query IS
718 SELECT tkgq.tk_group_query_id
719 FROM hxc_tk_group_queries tkgq
720 WHERE tkgq.tk_group_id = p_tk_group_id;
721
722 BEGIN
723 g_debug :=hr_utility.debug_enabled;
724 if g_debug then
725 l_proc := 'maintain_tk_group_query';
726 hr_utility.trace('Params are :');
727 hr_utility.trace('tk group query id is : '||to_char(p_tk_group_query_id));
728 hr_utility.trace('tk group id is : '||to_char(p_tk_group_id));
729 hr_utility.set_location('Entering:'||l_proc, 10);
730 end if;
731 -- chk to see if a tk group query row already exists
732
733 OPEN csr_chk_group_query;
734 FETCH csr_chk_group_query INTO l_tk_group_query_id;
735
736 IF ( csr_chk_group_query%NOTFOUND )
737 THEN
738
739 if g_debug then
740 hr_utility.trace('tk group query not found');
741 end if;
742 -- create tk group query
743
744 hxc_tk_group_query_api.create_tk_group_query
745 (p_validate => FALSE
746 ,p_tk_group_query_id => l_tk_group_query_id
747 ,p_tk_group_id => p_tk_group_id
748 ,p_object_version_number => l_object_version_number
749 ,p_group_query_name => 'System: Included Resources'
750 ,p_include_exclude => 'I'
751 ,p_system_user => 'S' );
752
753 if g_debug then
754 hr_utility.trace('new tk group query id is : '||to_char(l_tk_group_query_id));
755 end if;
756
757 END IF;
758
759 CLOSE csr_chk_group_query;
760
761 if g_debug then
762 hr_utility.trace('tk group query found');
763 hr_utility.trace('old tk group query id is : '||to_char(l_tk_group_query_id));
764 end if;
765 p_tk_group_query_id := l_tk_group_query_id;
766
767 if g_debug then
768 hr_utility.set_location('Leaving:'||l_proc, 20);
769 end if;
770 END maintain_tk_group_query;
771 --
772 END hxc_tk_group_query_api;
[Home] [Help]