1 Package Body hxc_tcc_bus as
2 /* $Header: hxctccrhi.pkb 120.3 2006/07/07 06:27:47 gsirigin noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_tcc_bus.'; -- Global package name
9
10 g_debug boolean := hr_utility.debug_enabled;
11 --
12 -- ----------------------------------------------------------------------------
13 -- |-----------------------< chk_non_updateable_args >------------------------|
14 -- ----------------------------------------------------------------------------
15 -- {Start Of Comments}
16 --
17 -- Description:
18 -- This procedure is used to ensure that non updateable attributes have
19 -- not been updated. If an attribute has been updated an error is generated.
20 --
21 -- Pre Conditions:
22 -- g_old_rec has been populated with details of the values currently in
23 -- the database.
24 --
25 -- In Arguments:
26 -- p_rec has been populated with the updated values the user would like the
27 -- record set to.
28 --
29 -- Post Success:
30 -- Processing continues if all the non updateable attributes have not
31 -- changed.
32 --
33 -- Post Failure:
34 -- An application error is raised if any of the non updatable attributes
35 -- have been altered.
36 --
37 -- {End Of Comments}
38 -- ----------------------------------------------------------------------------
39 Procedure chk_non_updateable_args
40 (p_rec in hxc_tcc_shd.g_rec_type
41 ) IS
42 --
43 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
44 l_error EXCEPTION;
45 l_argument varchar2(30);
46 --
47 Begin
48 --
49 -- Only proceed with the validation if a row exists for the current
50 -- record in the HR Schema.
51 --
52 IF NOT hxc_tcc_shd.api_updating
53 (p_time_category_comp_id => p_rec.time_category_comp_id
54 ,p_object_version_number => p_rec.object_version_number
55 ) THEN
56 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
57 fnd_message.set_token('PROCEDURE ', l_proc);
58 fnd_message.set_token('STEP ', '5');
59 fnd_message.raise_error;
60 END IF;
61 --
62 -- EDIT_HERE: Add checks to ensure non-updateable args have
63 -- not been updated.
64 --
65 EXCEPTION
66 WHEN l_error THEN
67 hr_api.argument_changed_error
68 (p_api_name => l_proc
69 ,p_argument => l_argument);
70 WHEN OTHERS THEN
71 RAISE;
72 End chk_non_updateable_args;
73
74
75 -- ----------------------------------------------------------------------------
76 -- |------------------------< chk_component_type_id >-------------------------|
77 -- ----------------------------------------------------------------------------
78
79 -- Description:
80 -- This procedure insures a valid mapping component id
81
82 -- Pre Conditions:
83 -- None
84
85 -- In Arguments:
86 -- component_type_id
87
88 -- Post Success:
89 -- Processing continues if the mapping component id business rules
90 -- have not been violated
91
92 -- Post Failure:
93 -- An application error is raised if the mapping component id is not valid
94
95 -- ----------------------------------------------------------------------------
96 Procedure chk_component_type_id
97 (
98 p_time_category_id number
99 ,p_time_category_comp_id number
100 ,p_component_type_id number
101 ) IS
102
103 l_proc varchar2(72);
104
105 -- cursor to check mapping component id is valid
106
107 CURSOR csr_chk_mpc_id IS
108 SELECT 'ok'
109 FROM sys.dual
110 WHERE EXISTS (
111 SELECT 'x'
112 FROM hxc_mapping_components mpc
113 WHERE mpc.mapping_component_id = p_component_type_id );
114
115 l_ok varchar2(2) := NULL;
116
117 BEGIN
118
119 g_debug := hr_utility.debug_enabled;
120
121 if g_debug then
122 l_proc := g_package||'chk_component_type_id';
123 hr_utility.set_location('Entering:'||l_proc, 5);
124 end if;
125
126 IF ( p_component_type_id IS NOT NULL )
127 THEN
128 if g_debug then
129 hr_utility.set_location('Processing:'||l_proc, 10);
130 end if;
131
132 -- check that mapping component id is valid
133
134 OPEN csr_chk_mpc_id;
135 FETCH csr_chk_mpc_id INTO l_ok;
136 CLOSE csr_chk_mpc_id;
137
138 IF l_ok IS NULL
139 THEN
140
141 hr_utility.set_message(809, 'HXC_TCC_MPC_INVALID');
142 hr_utility.raise_error;
143
144 END IF;
145
146 END IF;
147
148 if g_debug then
149 hr_utility.set_location('Leaving:'||l_proc, 20);
150 end if;
151
152 END chk_component_type_id;
153
154 -- ----------------------------------------------------------------------------
155 -- |------------------------< chk_ref_time_category_id >----------------------|
156 -- ----------------------------------------------------------------------------
157
158 -- Description:
159 -- This procedure insures a valid time category id and that it is unique
160 -- within a time category
161 -- Furthermore, we also need to check that this time category code's
162 -- components do not reference or lead back to the Parent Time Category
163
164 -- Pre Conditions:
165 -- None
166
167 -- In Arguments:
168 -- time category code
169
170 -- Post Success:
171 -- Processing continues if the time category code business rules
172 -- have not been violated
173
174 -- Post Failure:
175 -- An application error is raised if the time category code is not valid
176
177 -- ----------------------------------------------------------------------------
178 Procedure chk_time_category_code
179 (
180 p_time_category_id number
181 ,p_time_category_comp_id number
182 ,p_ref_time_category_id number
183 ) IS
184
185 l_proc varchar2(72);
186 l_error varchar2(5);
187 l_ok varchar2(2);
188
189 -- cursor to check time category code is valid
190
191 CURSOR csr_chk_time_category_code IS
192 SELECT 'ok'
193 FROM sys.dual
194 WHERE EXISTS (
195 SELECT 'x'
196 FROM hxc_time_categories htc
197 WHERE htc.time_category_id = p_ref_time_category_id );
198
199 CURSOR csr_chk_code IS
200 SELECT 'error'
201 FROM sys.dual
202 WHERE EXISTS (
203 SELECT 'x'
204 FROM hxc_time_category_comps mpc
205 WHERE mpc.time_category_id = p_time_category_id
206 AND mpc.ref_time_category_id = p_ref_time_category_id
207 AND ( mpc.time_category_comp_id <> p_time_category_comp_id OR
208 p_time_category_comp_id IS NULL ) );
209
210 PROCEDURE chk_for_parent_category ( p_time_category_id NUMBER
211 , p_ref_time_category_id NUMBER ) IS
212
213 CURSOR csr_get_child_comps IS
214 SELECT tcc.ref_time_category_id
215 FROM hxc_time_category_comps tcc
216 , hxc_time_categories htc
217 WHERE htc.time_category_id = p_ref_time_category_id
218 AND htc.time_category_id = tcc.time_category_id
219 AND tcc.ref_time_category_id IS NOT NULL;
220
221 l_ref_comp_time_cat_id hxc_time_category_comps.ref_time_category_id%TYPE;
222
223 l_proc varchar2(72);
224
225 BEGIN
226 g_debug := hr_utility.debug_enabled;
227
228 if g_debug then
229 l_proc := g_package||'chk_for_parent_category';
230 hr_utility.set_location('Processing:'||l_proc, 5);
231 end if;
232
233 -- get any time category components of the child time category code
234 -- and make sure none are equal to the parent time category
235 -- for those which are not equal check that their time category
236 -- components do not point back to the parent.
237
238 OPEN csr_get_child_comps;
239 FETCH csr_get_child_comps INTO l_ref_comp_time_cat_id;
240
241 WHILE csr_get_child_comps%FOUND
242 LOOP
243 if g_debug then
244 hr_utility.set_location('Processing:'||l_proc, 10);
245 end if;
246
247 IF ( l_ref_comp_time_cat_id = p_time_category_id )
248 THEN
249 hr_utility.set_message(809, 'HXC_TCC_TC_CANNOT_REF_PARENT');
250 hr_utility.raise_error;
251 ELSE
252
253 if g_debug then
254 hr_utility.set_location('Processing:'||l_proc, 15);
255 end if;
256
257 chk_for_parent_category ( p_time_category_id => p_time_category_id
258 , p_ref_time_category_id => l_ref_comp_time_cat_id );
259
260 END IF;
261
262 FETCH csr_get_child_comps INTO l_ref_comp_time_cat_id;
263
264 END LOOP;
265
266 CLOSE csr_get_child_comps;
267
268 if g_debug then
269 hr_utility.set_location('Processing:'||l_proc, 20);
270 end if;
271
272 END chk_for_parent_category;
273
274
275
276 BEGIN -- chk_time_category_code
277
278 g_debug := hr_utility.debug_enabled;
279
280 if g_debug then
281 l_proc := g_package||'chk_time_category_code';
282 hr_utility.set_location('Entering:'||l_proc, 10);
283 end if;
284
285 IF ( p_ref_time_category_id IS NOT NULL )
286 THEN
287
288 if g_debug then
289 hr_utility.set_location('Processing:'||l_proc, 20);
290 end if;
291
292 -- check that time category code is valid
293
294 OPEN csr_chk_time_category_code;
295 FETCH csr_chk_time_category_code INTO l_ok;
296 CLOSE csr_chk_time_category_code;
297
298 if g_debug then
299 hr_utility.set_location('Processing:'||l_proc, 30);
300 end if;
301
302 IF l_ok IS NULL
303 THEN
304
305 hr_utility.set_message(809, 'HXC_TCC_TC_INVALID');
306 hr_utility.raise_error;
307
308 END IF;
309
310 if g_debug then
311 hr_utility.set_location('Processing:'||l_proc, 40);
312 end if;
313
314 -- check that time category code is unique
315
316 OPEN csr_chk_code;
317 FETCH csr_chk_code INTO l_error;
318 CLOSE csr_chk_code;
319
320 IF l_error IS NOT NULL
321 THEN
322 if g_debug then
323 hr_utility.set_location('Leaving:'||l_proc, 60);
324 end if;
325
326 hr_utility.set_message(809, 'HXC_TCC_TC_ALREADY_USED');
327 hr_utility.raise_error;
328
329 END IF;
330
331 -- check to see if the time category code components include
332 -- any other time categories and make sure none of these are
333 -- or lead back to the time category code.
334
335 chk_for_parent_category ( p_time_category_id => p_time_category_id
336 , p_ref_time_category_id => p_ref_time_category_id );
337
338 if g_debug then
339 hr_utility.set_location('Leaving:'||l_proc, 70);
340 end if;
341 END IF;
342
343 END chk_time_category_code;
344
345 -- ----------------------------------------------------------------------------
346 -- |-----------------------------< chk_value_id >------------------------------|
347 -- ----------------------------------------------------------------------------
348
349 -- Description:
350 -- This procedure insures a valid value id
351
352 -- Pre Conditions:
353 -- None
354
355 -- In Arguments:
356 -- time category code
357
358 -- Post Success:
359 -- Processing continues if the value id business rules
360 -- have not been violated
361
362 -- Post Failure:
363 -- An application error is raised if the vlaue id is not valid
364
365 -- ----------------------------------------------------------------------------
366 Procedure chk_value_id
367 (
368 p_flex_value_set_id number,
369 p_value_id varchar2
370 ) IS
371
372 l_proc varchar2(72);
373
374 l_description varchar2(150) := NULL;
375
376 BEGIN
377
378 g_debug := hr_utility.debug_enabled;
379
380 IF ( p_value_id IS NOT NULL )
381 THEN
382
383 if g_debug then
384 l_proc := g_package||'chk_value_id';
385 hr_utility.set_location('Entering:'||l_proc, 5);
386 end if;
387
388 l_description := hxc_time_category_utils_pkg.get_flex_value (p_flex_value_set_id, p_value_id );
389
390 IF ( l_description IS NULL )
391 THEN
392 hr_utility.set_message(809, 'HXC_TCC_VALUE_INVALID');
393 hr_utility.raise_error;
394 END IF;
395
396 if g_debug then
397 hr_utility.set_location('Leaving:'||l_proc, 20);
398 end if;
399
400 END IF;
401
402 END chk_value_id;
403
404 -- ----------------------------------------------------------------------------
405 -- |-----------------------< chk_mpc_value_id >------------------------------|
406 -- ----------------------------------------------------------------------------
407
408 -- Description:
409 -- This procedure insures that the mapping component and value combo
410 -- are not duplicated with a category
411
412 -- Pre Conditions:
413 -- None
414
415 -- In Arguments:
416 -- mapping component id
417 -- value id
418 -- flex value set id
419
420 -- Post Success:
421 -- Processing continues if the value id business rules
422 -- have not been violated
423
424 -- Post Failure:
425 -- An application error is raised if the vlaue id is not valid
426
427 -- ----------------------------------------------------------------------------
428 Procedure chk_mpc_value_id
429 (
430 p_time_category_id number,
431 p_time_category_comp_id number,
432 p_flex_value_set_id number,
433 p_value_id varchar2,
434 p_component_type_id number,
435 p_type varchar2
436 ) IS
437
438 --
439 -- cursor to check mapping component is not duplicated
440 --
441 CURSOR csr_chk_mpc IS
442 SELECT 'error'
443 FROM sys.dual
444 WHERE EXISTS (
445 SELECT 'x'
446 FROM hxc_time_category_comps mpc
447 WHERE mpc.time_category_id = p_time_category_id
448 AND mpc.component_type_id = p_component_type_id
449 AND mpc.type = p_type
450 AND (
451 ( mpc.type = 'MC' AND
452 ( mpc.value_id = p_value_id ) OR
453 ( p_value_id IS NULL AND mpc.value_id IS NULL )
454 )
455 OR
456 ( mpc.type = 'MC_VS' AND
457 mpc.flex_value_set_id = p_flex_value_set_id )
458 OR
459 ( mpc.type NOT IN ( 'MC_VS', 'MC' ) )
460 )
461 AND ( mpc.time_category_comp_id <> p_time_category_comp_id OR
462 p_time_category_comp_id IS NULL ) );
463
464 l_error varchar2(5) := NULL;
465
466 l_proc varchar2(72);
467
468 l_description varchar2(150) := NULL;
469
470 BEGIN
471
472 g_debug := hr_utility.debug_enabled;
473
474 -- first of all check to see that mapping component and value combo
475 -- are unique
476
477 OPEN csr_chk_mpc;
478 FETCH csr_chk_mpc INTO l_error;
479 CLOSE csr_chk_mpc;
480
481 if g_debug then
482 l_proc := g_package||'chk_mpc_value_id';
483 hr_utility.set_location('Entering:'||l_proc, 10);
484 end if;
485
486 IF l_error IS NOT NULL
487 THEN
488
489 hr_utility.set_message(809, 'HXC_TCC_MPC_VALUE_ALREADY_USED');
490 hr_utility.raise_error;
491
492 END IF;
493
494 if g_debug then
495 hr_utility.set_location('Entering:'||l_proc, 15);
496 end if;
497
498 END chk_mpc_value_id;
499
500 -- ----------------------------------------------------------------------------
501 -- |-----------------------------< chk_flex_Value_set_id >--------------------|
502 -- ----------------------------------------------------------------------------
503
504 -- Description:
505 -- This procedure insures a valid flex value set id
506
507 -- Pre Conditions:
508 -- None
509
510 -- In Arguments:
511 -- p_flex_value_set_id
512 -- p_value_id
513 -- p_type
514
515 -- Post Success:
516 -- Processing continues if the flex value set id business rules
517 -- have not been violated
518
519 -- Post Failure:
520 -- An application error is raised if the flex vlaue set id is not valid
521
522 -- ----------------------------------------------------------------------------
523 Procedure chk_flex_value_set_id
524 (
525 p_flex_value_set_id number
526 , p_value_id varchar2
527 , p_type varchar2
528 ) IS
529
530 CURSOR csr_chk_flex_value_set_id IS
531 SELECT 'ok'
532 FROM dual
533 WHERE EXISTS (
534 select 1
535 FROM fnd_flex_value_sets
536 WHERE flex_value_set_id = p_flex_value_set_id );
537
538 l_proc varchar2(72);
539
540 l_ok varchar2(2) := NULL;
541
542 BEGIN
543
544 g_debug := hr_utility.debug_enabled;
545
546 IF ( p_flex_value_set_id IS NULL AND p_value_id IS NOT NULL and p_type IN ('MC','MC_VS') )
547 THEN
548
549 hr_utility.set_message(809, 'HXC_TCC_FLEX_VALUE_SET_ID_MAND');
550 hr_utility.raise_error;
551
552 ELSIF ( p_flex_value_set_id IN ( -1,-2 ) )
553 THEN
554
555 -- -1 when ATTRIBUTE_CATEGORY mapping component chosen
556
557 l_ok := 'ok';
558
559 ELSIF ( ( p_type = 'MC' AND p_value_id IS NOT NULL )
560 OR ( p_type = 'MC_VS' ) )
561 THEN
562
563 OPEN csr_chk_flex_value_set_id;
564 FETCH csr_chk_flex_value_set_id INTO l_ok;
565 CLOSE csr_chk_flex_value_set_id;
566
567 ELSE
568
569 l_ok := 'ok';
570
571 END IF;
572
573 IF l_ok IS NULL
574 THEN
575
576 if g_debug then
577 l_proc := g_package||'chk_flex_value_set_id';
578 hr_utility.set_location('Entering:'||l_proc, 5);
579 end if;
580
581 hr_utility.set_message(809, 'HXC_TCC_FLEX_VALUE_SET_ID_INV');
582 hr_utility.raise_error;
583
584 END IF;
585
586 if g_debug then
587 hr_utility.set_location('Leaving:'||l_proc, 20);
588 end if;
589
590 END chk_flex_value_set_id;
591
592 -- ----------------------------------------------------------------------------
593 -- |---------------------------< insert_validate >----------------------------|
594 -- ----------------------------------------------------------------------------
595 Procedure insert_validate
596 (p_rec in hxc_tcc_shd.g_rec_type
597 ) is
598 --
599 l_proc varchar2(72);
600 --
601 Begin
602 g_debug := hr_utility.debug_enabled;
603
604 if g_debug then
605 l_proc := g_package||'insert_validate';
606 hr_utility.set_location('Entering:'||l_proc, 5);
607 end if;
608 --
609 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
610 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS.
611 --
612 -- Call all supporting business operations
613
614 if g_debug then
615 hr_utility.set_location('Processing:'||l_proc, 10);
616 end if;
617
618 IF ( p_rec.component_type_id IS NOT NULL AND p_rec.type = 'MC' )
619 THEN
620 if g_debug then
621 hr_utility.set_location('Processing:'||l_proc, 20);
622 end if;
623
624 chk_component_type_id ( p_component_type_id => p_rec.component_type_id
625 ,p_time_category_id => p_rec.time_category_id
626 ,p_time_category_comp_id => p_rec.time_category_comp_id );
627
628 chk_flex_value_set_id ( p_flex_value_set_id => p_rec.flex_value_set_id
629 , p_value_id => p_rec.value_id
630 , p_type => p_rec.type );
631
632 chk_value_id ( p_flex_value_set_id => p_rec.flex_value_set_id,
633 p_value_id => p_rec.value_id );
634
635 chk_mpc_value_id ( p_time_category_id => p_rec.time_category_id,
636 p_time_category_comp_id => p_rec.time_category_comp_id,
637 p_flex_value_set_id => p_rec.flex_value_set_id,
638 p_value_id => p_rec.value_id,
639 p_component_type_id => p_rec.component_type_id,
640 p_type => p_rec.type );
641
642 ELSIF ( p_rec.type = 'TC' )
643 THEN
644
645 if g_debug then
646 hr_utility.set_location('Processing:'||l_proc, 30);
647 end if;
648
649 chk_time_category_code ( p_ref_time_category_id => p_rec.ref_time_category_id
650 ,p_time_category_id => p_rec.time_category_id
651 ,p_time_category_comp_id => p_rec.time_category_comp_id );
652
653 ELSIF ( p_rec.type = 'AN' ) --Fix for bug 4336172
654 THEN
655
656 if g_debug then
657 hr_utility.set_location('Processing:'||l_proc, 35);
658 end if;
659
660 chk_mpc_value_id ( p_time_category_id => p_rec.time_category_id,
661 p_time_category_comp_id => p_rec.time_category_comp_id,
662 p_flex_value_set_id => p_rec.flex_value_set_id,
663 p_value_id => p_rec.value_id,
664 p_component_type_id => p_rec.component_type_id,
665 p_type => p_rec.type );
666
667 END IF;
668
669 if g_debug then
670 hr_utility.set_location('Processing:'||l_proc, 40);
671 end if;
672
673 End insert_validate;
674 --
675 -- ----------------------------------------------------------------------------
676 -- |---------------------------< update_validate >----------------------------|
677 -- ----------------------------------------------------------------------------
678 Procedure update_validate
679 (p_rec in hxc_tcc_shd.g_rec_type
680 ) is
681 --
682 l_proc varchar2(72);
683 --
684 Begin
685 g_debug := hr_utility.debug_enabled;
686
687 if g_debug then
688 l_proc := g_package||'update_validate';
689 hr_utility.set_location('Entering:'||l_proc, 5);
690 end if;
691
692 -- No business group context. HR_STANDARD_LOOKUPS used for validation.
693 -- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS.
694
695 -- Call all supporting business operations
696
697 if g_debug then
698 hr_utility.set_location('Processing:'||l_proc, 10);
699 end if;
700
701 IF ( p_rec.ref_time_category_id IS NOT NULL )
702 THEN
703 if g_debug then
704 hr_utility.set_location('Processing:'||l_proc, 20);
705 end if;
706
707 chk_time_category_code ( p_ref_time_category_id => p_rec.ref_time_category_id
708 ,p_time_category_id => p_rec.time_category_id
709 ,p_time_category_comp_id => p_rec.time_category_comp_id );
710
711 ELSIF ( p_rec.type = 'MC' )
712 THEN
713
714 if g_debug then
715 hr_utility.set_location('Processing:'||l_proc, 30);
716 end if;
717
718 chk_component_type_id ( p_component_type_id => p_rec.component_type_id
719 ,p_time_category_id => p_rec.time_category_id
720 ,p_time_category_comp_id => p_rec.time_category_comp_id );
721
722 chk_flex_value_set_id ( p_flex_value_set_id => p_rec.flex_value_set_id
723 , p_value_id => p_rec.value_id
724 , p_type => p_rec.type );
725
726 chk_value_id ( p_flex_value_set_id => p_rec.flex_value_set_id,
727 p_value_id => p_rec.value_id );
728
729 chk_mpc_value_id ( p_time_category_id => p_rec.time_category_id,
730 p_time_category_comp_id => p_rec.time_category_comp_id,
731 p_flex_value_set_id => p_rec.flex_value_set_id,
732 p_value_id => p_rec.value_id,
733 p_component_type_id => p_rec.component_type_id,
734 p_type => p_rec.type );
735 END IF;
736
737 if g_debug then
738 hr_utility.set_location('Processing:'||l_proc, 40);
739 end if;
740
741 chk_non_updateable_args
742 (p_rec => p_rec
743 );
744
745 if g_debug then
746 hr_utility.set_location(' Leaving:'||l_proc, 50);
747 end if;
748 End update_validate;
749 --
750 -- ----------------------------------------------------------------------------
751 -- |---------------------------< delete_validate >----------------------------|
752 -- ----------------------------------------------------------------------------
753 Procedure delete_validate
754 (p_rec in hxc_tcc_shd.g_rec_type
755 ) is
756 --
757 l_proc varchar2(72);
758 --
759 Begin
760
761 g_debug := hr_utility.debug_enabled;
762
763 if g_debug then
764 l_proc := g_package||'delete_validate';
765 hr_utility.set_location('Entering:'||l_proc, 5);
766 end if;
767
768 -- Call all supporting business operations
769
770 IF ( p_rec.type IN ( 'AN', 'MC_VS' ) )
771 THEN
772 hxc_time_category_utils_pkg.delete_time_category_comp_sql ( p_rec );
773 END IF;
774
775 if g_debug then
776 hr_utility.set_location(' Leaving:'||l_proc, 10);
777 end if;
778 End delete_validate;
779 --
780 end hxc_tcc_bus;
[Home] [Help]