[Home] [Help]
PACKAGE BODY: APPS.HXC_HAC_BUS
Source
1 Package Body hxc_hac_bus as
2 /* $Header: hxchacrhi.pkb 120.4 2006/06/13 08:42:23 gsirigin noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' hxc_hac_bus.'; -- Global package name
9 g_debug boolean := hr_utility.debug_enabled;
10 --
11 -- The following two global variables are only to be
12 -- used by the return_legislation_code function.
13 --
14 g_legislation_code varchar2(150) default null;
15 g_approval_comp_id number default null;
16 --
17 -- ---------------------------------------------------------------------------
18 -- |----------------------< set_security_group_id >--------------------------|
19 -- ---------------------------------------------------------------------------
20 --
21 Procedure set_security_group_id
22 (p_approval_comp_id in number
23 ) is
24 --
25 -- Declare cursor
26 --
27 -- EDIT_HERE In the following cursor statement add join(s) between
28 -- hxc_approval_comps and PER_BUSINESS_GROUPS
29 -- so that the security_group_id for
30 -- the current business group context can be derived.
31 -- Remove this comment when the edit has been completed.
32 cursor csr_sec_grp is
33 select pbg.security_group_id
34 from per_business_groups pbg
35 , hxc_approval_comps hac
36 -- , EDIT_HERE table_name(s) 333
37 where hac.approval_comp_id = p_approval_comp_id;
38 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
39 --
40 -- Declare local variables
41 --
42 l_security_group_id number;
43 l_proc varchar2(72);
44 --
45 begin
46 --
47 g_debug:=hr_utility.debug_enabled;
48 if g_debug then
49 l_proc := g_package||'set_security_group_id';
50 hr_utility.set_location('Entering:'|| l_proc, 10);
51 end if;
52 --
53 -- Ensure that all the mandatory parameter are not null
54 --
55 hr_api.mandatory_arg_error
56 (p_api_name => l_proc
57 ,p_argument => 'approval_comp_id'
58 ,p_argument_value => p_approval_comp_id
59 );
60 --
61 open csr_sec_grp;
62 fetch csr_sec_grp into l_security_group_id;
63 --
64 if csr_sec_grp%notfound then
65 --
66 close csr_sec_grp;
67 --
68 -- The primary key is invalid therefore we must error
69 --
70 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
71 fnd_message.raise_error;
72 --
73 end if;
74 close csr_sec_grp;
75 --
76 -- Set the security_group_id in CLIENT_INFO
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 --
82 if g_debug then
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 end if;
85 --
86 end set_security_group_id;
87 --
88 -- ---------------------------------------------------------------------------
89 -- |---------------------< return_legislation_code >-------------------------|
90 -- ---------------------------------------------------------------------------
91 --
92 Function return_legislation_code
93 (p_approval_comp_id in number
94 )
95 Return Varchar2 Is
96 --
97 -- Declare cursor
98 --
99 -- EDIT_HERE In the following cursor statement add join(s) between
100 -- hxc_approval_comps and PER_BUSINESS_GROUPS
101 -- so that the legislation_code for
102 -- the current business group context can be derived.
103 -- Remove this comment when the edit has been completed.
104 cursor csr_leg_code is
105 select pbg.legislation_code
106 from per_business_groups pbg
107 , hxc_approval_comps hac
108 -- , EDIT_HERE table_name(s) 333
109 where hac.approval_comp_id = p_approval_comp_id;
110 -- and pbg.business_group_id = EDIT_HERE 333.business_group_id;
111 --
112 -- Declare local variables
113 --
114 l_legislation_code varchar2(150);
115 l_proc varchar2(72);
116 --
117 Begin
118 --
119 g_debug:=hr_utility.debug_enabled;
120 if g_debug then
121 l_proc := g_package||'return_legislation_code';
122 hr_utility.set_location('Entering:'|| l_proc, 10);
123 end if;
124 --
125 -- Ensure that all the mandatory parameter are not null
126 --
127 hr_api.mandatory_arg_error
128 (p_api_name => l_proc
129 ,p_argument => 'approval_comp_id'
130 ,p_argument_value => p_approval_comp_id
131 );
132 --
133 if ( nvl(hxc_hac_bus.g_approval_comp_id, hr_api.g_number)
134 = p_approval_comp_id) then
135 --
136 -- The legislation code has already been found with a previous
137 -- call to this function. Just return the value in the global
138 -- variable.
139 --
140 l_legislation_code := hxc_hac_bus.g_legislation_code;
141 if g_debug then
142 hr_utility.set_location(l_proc, 20);
143 end if;
144 else
145 --
146 -- The ID is different to the last call to this function
147 -- or this is the first call to this function.
148 --
149 open csr_leg_code;
150 fetch csr_leg_code into l_legislation_code;
151 --
152 if csr_leg_code%notfound then
153 --
154 -- The primary key is invalid therefore we must error
155 --
156 close csr_leg_code;
157 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
158 fnd_message.raise_error;
159 end if;
160 if g_debug then
161 hr_utility.set_location(l_proc,30);
162 end if;
163 --
164 -- Set the global variables so the values are
165 -- available for the next call to this function.
166 --
167 close csr_leg_code;
168 hxc_hac_bus.g_approval_comp_id := p_approval_comp_id;
169 hxc_hac_bus.g_legislation_code := l_legislation_code;
170 end if;
171 if g_debug then
172 hr_utility.set_location(' Leaving:'|| l_proc, 40);
173 end if;
174 return l_legislation_code;
175 end return_legislation_code;
176 --
177 -- ----------------------------------------------------------------------------
178 -- |-----------------------< chk_non_updateable_args >------------------------|
179 -- ----------------------------------------------------------------------------
180 -- {Start Of Comments}
181 --
182 -- Description:
183 -- This procedure is used to ensure that non updateable attributes have
184 -- not been updated. If an attribute has been updated an error is generated.
185 --
186 -- Pre Conditions:
187 -- g_old_rec has been populated with details of the values currently in
188 -- the database.
189 --
190 -- In Arguments:
191 -- p_rec has been populated with the updated values the user would like the
192 -- record set to.
193 --
194 -- Post Success:
195 -- Processing continues if all the non updateable attributes have not
196 -- changed.
197 --
198 -- Post Failure:
199 -- An application error is raised if any of the non updatable attributes
200 -- have been altered.
201 --
202 -- {End Of Comments}
203 -- ----------------------------------------------------------------------------
204 Procedure chk_non_updateable_args
205 (p_effective_date in date
206 ,p_rec in hxc_hac_shd.g_rec_type
207 ) IS
208 --
209 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
210 l_error EXCEPTION;
211 l_argument varchar2(30);
212 --
213 Begin
214 --
215 -- Only proceed with the validation if a row exists for the current
216 -- record in the HR Schema.
217 --
218 IF NOT hxc_hac_shd.api_updating
219 (p_approval_comp_id => p_rec.approval_comp_id
220 ,p_object_version_number => p_rec.object_version_number
221 ) THEN
222 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
223 fnd_message.set_token('PROCEDURE ', l_proc);
224 fnd_message.set_token('STEP ', '5');
225 fnd_message.raise_error;
226 END IF;
227 --
228 -- EDIT_HERE: Add checks to ensure non-updateable args have
229 -- not been updated.
230 --
231 EXCEPTION
232 WHEN l_error THEN
233 hr_api.argument_changed_error
234 (p_api_name => l_proc
235 ,p_argument => l_argument);
236 WHEN OTHERS THEN
237 RAISE;
238 End chk_non_updateable_args;
239 --
240 -- ----------------------------------------------------------------------------
241 -- |-< chk_approval_comp_dates >----------------------------------------------|
242 -- ----------------------------------------------------------------------------
243 --
244 -- {Start Of Comments}
245 --
246 -- Description:
247 -- This procedure performs basic checks on the assignment dates to ensure
248 -- that they conform with the business rules.
249 -- At the moment the only business rule enforced in this procedure is that
250 -- the end date must be >= the start date and that the start date is not
251 -- null.
252 --
253 -- Prerequisites:
254 -- None.
255 --
256 -- In Parameters:
257 -- p_approval_comp_id
258 -- p_start_date
259 -- p_end_date
260 --
261 -- Post Success:
262 -- Processing continues.
263 --
264 -- Post Failure:
265 -- An exception is raised.
266 --
267 -- Developer Implementation Notes:
268 -- None.
269 --
270 -- Access Status:
271 -- Internal Row Handler Use Only.
272 --
273 -- {End Of Comments}
274 -- ----------------------------------------------------------------------------
275 --
276 PROCEDURE chk_approval_comp_dates
277 (p_approval_comp_id
278 IN hxc_approval_comps.approval_comp_id%TYPE
279 ,p_start_date
280 IN hxc_approval_comps.start_date%TYPE
281 ,p_end_date
282 IN hxc_approval_comps.end_date%TYPE
283 )
284 IS
285 --
286 CURSOR c_get_approval_comp_dates
287 IS
288 SELECT start_date
289 ,end_date
290 FROM hxc_approval_comps
291 WHERE approval_comp_id = p_approval_comp_id;
292 --
293 BEGIN
294 --
295 -- check that the start date is not null
296 --
297 -- IF p_start_date IS NULL THEN
298 --
299 -- hr_utility.set_message
300 -- (809
301 -- ,'HXC_0054_HAC_COMP_ST_DT_NULL'
302 -- );
303 -- hr_utility.raise_error;
304 --
305 -- END IF;
306 --
307 -- check that the start date is not equal to or more than the end date
308 --
309 IF p_start_date > NVL(p_end_date, hr_general.END_OF_TIME) THEN
310 --
311 hr_utility.set_message
312 (809
313 ,'HXC_0055_HAC_COMP_DT_ERR'
314 );
315 hr_utility.raise_error;
316 --
317 END IF;
318 --
319 END chk_approval_comp_dates;
320 --
321 -- ----------------------------------------------------------------------------
322 -- |-< chk_invalid_dates_create >---------------------------------------------|
323 -- ----------------------------------------------------------------------------
324 -- {Start Of Comments}
325 --
326 -- Description:
327 -- This procedure is used to enforce the business rule that the start/end
328 -- dates of new records cannot overlap both the start and the end
329 -- dates of existing records.
330 --
331 -- Prerequisites:
332 -- None.
333 --
334 -- In Parameters:
335 --
336 -- Post Success:
337 -- Processing continues.
338 --
339 -- Post Failure:
340 -- An exception is raised.
341 --
342 -- Developer Implementation Notes:
343 -- None.
344 --
345 -- Access Status:
346 -- Internal Row Handler Use Only.
347 --
348 -- {End Of Comments}
349 -- ----------------------------------------------------------------------------
350 --
351 PROCEDURE chk_invalid_dates_create
352 ( p_approval_style_id
353 IN hxc_approval_comps.approval_style_id%TYPE
354 ,p_time_recipient_id
355 IN hxc_approval_comps.time_recipient_id%TYPE
356 ,p_start_date
357 IN hxc_approval_comps.start_date%TYPE
358 ,p_end_date
359 IN hxc_approval_comps.end_date%TYPE
360 )
361 IS
362 --
363 CURSOR c_chk_invalid_dates_create
364 IS
365 SELECT 'Y'
366 FROM hxc_approval_comps s
367 WHERE s.approval_style_id = p_approval_style_id
368 AND s.time_recipient_id = p_time_recipient_id
369 AND s.start_date >= p_start_date
370 AND NVL(s.end_date, hr_general.END_OF_TIME)
371 <= NVL(p_end_date,hr_general.END_OF_TIME)
372 AND p_time_recipient_id <> -1 ;
373 --
374 l_result VARCHAR2 (1);
375 --
376 BEGIN
377 --
378 OPEN c_chk_invalid_dates_create;
379 --
380 FETCH c_chk_invalid_dates_create INTO l_result;
381 --
382 IF c_chk_invalid_dates_create%FOUND THEN
383 --
384 CLOSE c_chk_invalid_dates_create;
385 --
386 -- record found - raise an exception
387 --
388 hr_utility.set_message
389 (809
390 ,'HXC_0067_HAC_COMP_INVCR_DT_ERR'
391 );
392 hr_utility.raise_error;
393 END IF;
394 --
395 CLOSE c_chk_invalid_dates_create;
396 --
397 END chk_invalid_dates_create;
398 --
399 -- ----------------------------------------------------------------------------
403 --
400 -- |-< chk_invalid_dates_update >---------------------------------------------|
401 -- ----------------------------------------------------------------------------
402 -- {Start Of Comments}
404 -- Description:
405 -- This procedure is used to enforce the business rule that the start/end
406 -- dates of updated records cannot overlap both the start and the end
407 -- dates of existing records.
408 --
409 -- Prerequisites:
410 -- None.
411 --
412 -- In Parameters:
413 --
414 -- Post Success:
415 -- Processing continues.
416 --
417 -- Post Failure:
418 -- An exception is raised.
419 --
420 -- Developer Implementation Notes:
421 -- None.
422 --
423 -- Access Status:
424 -- Internal Row Handler Use Only.
425 --
426 -- {End Of Comments}
427 -- ----------------------------------------------------------------------------
428 --
429 PROCEDURE chk_invalid_dates_update
430 (p_approval_comp_id
431 IN hxc_approval_comps.approval_comp_id%TYPE
432 ,p_approval_style_id
433 IN hxc_approval_comps.approval_style_id%TYPE
434 ,p_time_recipient_id
435 IN hxc_approval_comps.time_recipient_id%TYPE
436 ,p_start_date
437 IN hxc_approval_comps.start_date%TYPE
438 ,p_end_date
439 IN hxc_approval_comps.end_date%TYPE
440 )
441 IS
442 --
443 CURSOR c_chk_invalid_dates_update
444 IS
445 SELECT 'Y'
446 FROM hxc_approval_comps s
447 WHERE s.approval_comp_id <> p_approval_comp_id
448 AND s.approval_style_id = p_approval_style_id
449 AND s.time_recipient_id = p_time_recipient_id
450 AND s.start_date >= p_start_date
451 AND NVL(s.end_date, hr_general.END_OF_TIME)
452 <= NVL(p_end_date,hr_general.END_OF_TIME)
453 AND p_time_recipient_id <> -1 ;
454 --
455 l_result VARCHAR2 (1);
456 --
457 BEGIN
458 --
459 OPEN c_chk_invalid_dates_update;
460 --
461 FETCH c_chk_invalid_dates_update INTO l_result;
462 --
463 IF c_chk_invalid_dates_update%FOUND THEN
464 --
465 CLOSE c_chk_invalid_dates_update;
466 --
467 -- record found - raise an exception
468 --
469 hr_utility.set_message
470 (809
471 ,'HXC_0070_HAC_COMP_INVUP_DT_ERR'
472 );
473 hr_utility.raise_error;
474 END IF;
475 --
476 CLOSE c_chk_invalid_dates_update;
477 --
478 END chk_invalid_dates_update;
479 --
480 -- ----------------------------------------------------------------------------
481 -- |-< chk_overlapping_dates_create >-----------------------------------------|
482 -- ----------------------------------------------------------------------------
483 --
484 -- {Start Of Comments}
485 --
486 -- Description:
487 -- This procedure is used to enforce the business rule that dates of
488 -- records cannot overlap.
489 --
490 -- Prerequisites:
491 -- None.
492 --
493 -- In Parameters:
494 -- p_approval_style_id
495 -- p_start_date
496 -- p_end_date
497 -- p_time_recipient_id
498 --
499 -- Post Success:
500 -- Processing continues.
501 --
502 -- Post Failure:
503 -- An exception is raised.
504 --
505 -- Developer Implementation Notes:
506 -- None.
507 --
508 -- Access Status:
509 -- Internal Row Handler Use Only.
510 --
511 -- {End Of Comments}
512 -- ----------------------------------------------------------------------------
513 --
514 PROCEDURE chk_overlapping_dates_create
515 (p_approval_style_id
516 IN hxc_approval_comps.approval_style_id%TYPE
517 ,p_time_recipient_id
518 IN hxc_approval_comps.time_recipient_id%TYPE
519 ,p_start_date
520 IN hxc_approval_comps.start_date%TYPE
521 ,p_end_date
522 IN hxc_approval_comps.end_date%TYPE
523 )
524 IS
525 --
526 l_id hxc_approval_comps.approval_style_id%TYPE DEFAULT NULL;
527 l_ovn hxc_approval_comps.object_version_number%TYPE DEFAULT NULL;
528 l_start_date hxc_approval_comps.start_date%TYPE DEFAULT NULL;
529 l_end_date hxc_approval_comps.end_date%TYPE DEFAULT NULL;
530 --
531 BEGIN
532 --
533 -- call the other chk_overlapping_dates_create procedure and
534 -- raise the relavent exception
535 -- if it returns anything
536 --
537
538 chk_overlapping_dates_create
539 (p_approval_style_id => p_approval_style_id
540 ,p_time_recipient_id => p_time_recipient_id
541 ,p_start_date => p_start_date
542 ,p_end_date => p_end_date
543 ,p_clashing_id => l_id
544 ,p_clashing_ovn => l_ovn
545 ,p_clashing_start_date => l_start_date
546 ,p_clashing_end_date => l_end_date
547 );
548 --
549
550 IF l_id IS NOT NULL THEN
551 -- we need to work out which exception to raise....
552 IF p_start_date >= l_start_date AND p_start_date <= l_end_date THEN
556 hr_utility.set_message
553 --
554 -- The start date of the inserted record is in error
555 --
557 (809
558 ,'HXC_0071_HAC_COMP_ST_DTCR_ERR'
559 );
560 hr_utility.raise_error;
561 --
562 ELSIF p_end_date >= l_start_date AND p_end_date <= l_end_date THEN
563 --
564 -- The end date of the inserted record is in error
565 --
566 hr_utility.set_message
567 (809
568 ,'HXC_0072_HAC_COMP_END_DTCR_ERR'
569 );
570 hr_utility.raise_error;
571 --
572
573 END IF;
574 END IF;
575 --
576
577 END chk_overlapping_dates_create;
578 --
579 -- ----------------------------------------------------------------------------
580 -- |-< chk_overlapping_dates_create >-----------------------------------------|
581 -- ----------------------------------------------------------------------------
582 --
583 -- {Start Of Comments}
584 --
585 -- Description:
586 -- This procedure is used to enforce the business rule that dates of
587 -- records cannot overlap.
588 --
589 -- Prerequisites:
590 -- None.
591 --
592 -- In Parameters:
593 -- p_approval_style_id
594 -- p_time_recipient_id
595 -- p_start_date
596 -- p_end_date
597 -- p_clashing_id
598 -- p_clashing_ovn
599 -- p_clashing_start_date
600 -- p_clashing_end_date
601 --
602 -- Post Success:
603 -- Processing continues.
604 --
605 -- Post Failure:
606 -- The id of the record which overlaps is returned.
607 --
608 -- Developer Implementation Notes:
609 -- None.
610 --
611 -- Access Status:
612 -- Internal Row Handler Use Only.
613 --
614 -- {End Of Comments}
615 -- ----------------------------------------------------------------------------
616 --
617 PROCEDURE chk_overlapping_dates_create
618 (p_approval_style_id
619 IN hxc_approval_comps.approval_style_id%TYPE
620 ,p_time_recipient_id
621 IN hxc_approval_comps.time_recipient_id%TYPE
622 ,p_start_date
623 IN hxc_approval_comps.start_date%TYPE
624 ,p_end_date
625 IN hxc_approval_comps.end_date%TYPE
626 ,p_clashing_id
627 OUT NOCOPY hxc_approval_comps.approval_style_id%TYPE
628 ,p_clashing_ovn
629 OUT NOCOPY hxc_approval_comps.object_version_number%TYPE
630 ,p_clashing_start_date
631 OUT NOCOPY hxc_approval_comps.start_date%TYPE
632 ,p_clashing_end_date
633 OUT NOCOPY hxc_approval_comps.end_date%TYPE
634 )
635 IS
636 --
637 CURSOR c_chk_overlapping_dates_create
638 IS
639 SELECT s.approval_style_id
640 ,s.object_version_number
641 ,s.start_date
642 ,s.end_date
643 FROM hxc_approval_comps s
644 WHERE s.approval_style_id = p_approval_style_id
645 AND s.time_recipient_id = p_time_recipient_id
646 AND NOT ( (s.start_date < p_start_date
647 AND NVL(s.end_date, hr_general.END_OF_TIME) < p_start_date)
648 OR (s.start_date > p_end_date
649 AND NVL(s.end_date, hr_general.END_OF_TIME)
650 > NVL(p_end_date, hr_general.END_OF_TIME))
651 )
652 AND p_time_recipient_id <> -1;
653 --
654 BEGIN
655 --
656 OPEN c_chk_overlapping_dates_create;
657 --
658 FETCH c_chk_overlapping_dates_create INTO p_clashing_id
659 ,p_clashing_ovn
660 ,p_clashing_start_date
661 ,p_clashing_end_date;
662 --
663 IF c_chk_overlapping_dates_create%NOTFOUND THEN
664 p_clashing_id := NULL;
665 END IF;
666 --
667 CLOSE c_chk_overlapping_dates_create;
668 --
669 END chk_overlapping_dates_create;
670 --
671 -- ----------------------------------------------------------------------------
672 -- |-< chk_overlapping_dates_update >-----------------------------------------|
673 -- ----------------------------------------------------------------------------
674 --
675 -- {Start Of Comments}
676 --
677 -- Description:
678 -- This procedure is used to enforce the business rule that dates of
679 -- records cannot overlap.
680 --
681 -- Prerequisites:
682 -- None.
683 --
684 -- In Parameters:
685 -- p_approval_comp_id
686 -- p_approval_style_id
687 -- p_start_date
688 -- p_end_date
689 -- p_time_recipient_id
690 --
691 -- Post Success:
692 -- Processing continues.
693 --
694 -- Post Failure:
695 -- An exception is raised.
696 --
697 -- Developer Implementation Notes:
698 -- None.
699 --
700 -- Access Status:
701 -- Internal Row Handler Use Only.
702 --
703 -- {End Of Comments}
704 -- ----------------------------------------------------------------------------
705 --
709 ,p_approval_style_id
706 PROCEDURE chk_overlapping_dates_update
707 (p_approval_comp_id
708 IN hxc_approval_comps.approval_comp_id%TYPE
710 IN hxc_approval_comps.approval_style_id%TYPE
711 ,p_time_recipient_id
712 IN hxc_approval_comps.time_recipient_id%TYPE
713 ,p_start_date
714 IN hxc_approval_comps.start_date%TYPE
715 ,p_end_date
716 IN hxc_approval_comps.end_date%TYPE
717 )
718 IS
719 --
720 l_id hxc_approval_comps.approval_style_id%TYPE DEFAULT NULL;
721 l_ovn hxc_approval_comps.object_version_number%TYPE DEFAULT NULL;
722 l_start_date hxc_approval_comps.start_date%TYPE DEFAULT NULL;
723 l_end_date hxc_approval_comps.end_date%TYPE DEFAULT NULL;
724 --
725 BEGIN
726 --
727 -- call the other chk_overlapping_dates_update procedure and
728 -- raise the relavent exception
729 -- if it returns anything
730 --
731
732 chk_overlapping_dates_update
733 (p_approval_comp_id => p_approval_comp_id
734 ,p_approval_style_id => p_approval_style_id
735 ,p_time_recipient_id => p_time_recipient_id
736 ,p_start_date => p_start_date
737 ,p_end_date => p_end_date
738 ,p_clashing_id => l_id
739 ,p_clashing_ovn => l_ovn
740 ,p_clashing_start_date => l_start_date
741 ,p_clashing_end_date => l_end_date
742 );
743 --
744
745 IF l_id IS NOT NULL THEN
746 -- we need to work out which exception to raise....
747 IF p_start_date >= l_start_date AND p_start_date <= l_end_date THEN
748 --
749 -- The start date of the updated record is in error
750 --
751 hr_utility.set_message
752 (809
753 ,'HXC_0073_HAC_COMP_ST_DTUP_ERR'
754 );
755 hr_utility.raise_error;
756 --
757 ELSIF p_end_date >= l_start_date AND p_end_date <= l_end_date THEN
758 --
759 -- The end date of the updated record is in error
760 --
761 hr_utility.set_message
762 (809
763 ,'HXC_0074_HAC_COMP_END_DTUP_ERR'
764 );
765 hr_utility.raise_error;
766 --
767
768 END IF;
769 END IF;
770 --
771
772 END chk_overlapping_dates_update;
773 --
774 -- ----------------------------------------------------------------------------
775 -- |-< chk_overlapping_dates_update >-----------------------------------------|
776 -- ----------------------------------------------------------------------------
777 --
778 -- {Start Of Comments}
779 -- Description:
780 -- This procedure is used to enforce the business rule that dates of
781 -- records cannot overlap.
782 --
783 -- Prerequisites:
784 -- None.
785 --
786 -- In Parameters:
787 -- p_approval_comp_id
788 -- p_approval_style_id
789 -- p_time_recipient_id
790 -- p_start_date
791 -- p_end_date
792 -- p_clashing_id
793 -- p_clashing_ovn
794 -- p_clashing_start_date
795 -- p_clashing_end_date
796 --
797 -- Post Success:
798 -- Processing continues.
799 --
800 -- Post Failure:
801 -- The id of the record which overlaps is returned.
802 --
803 -- Developer Implementation Notes:
804 -- None.
805 --
806 -- Access Status:
807 -- Internal Row Handler Use Only.
808 --
809 -- {End Of Comments}
810 -- ----------------------------------------------------------------------------
811 --
812 PROCEDURE chk_overlapping_dates_update
816 IN hxc_approval_comps.approval_style_id%TYPE
813 (p_approval_comp_id
814 IN hxc_approval_comps.approval_comp_id%TYPE
815 ,p_approval_style_id
817 ,p_time_recipient_id
818 IN hxc_approval_comps.time_recipient_id%TYPE
819 ,p_start_date
820 IN hxc_approval_comps.start_date%TYPE
821 ,p_end_date
822 IN hxc_approval_comps.end_date%TYPE
823 ,p_clashing_id
824 OUT NOCOPY hxc_approval_comps.approval_style_id%TYPE
825 ,p_clashing_ovn
826 OUT NOCOPY hxc_approval_comps.object_version_number%TYPE
827 ,p_clashing_start_date
828 OUT NOCOPY hxc_approval_comps.start_date%TYPE
829 ,p_clashing_end_date
830 OUT NOCOPY hxc_approval_comps.end_date%TYPE
831 )
832 IS
833 --
834 CURSOR c_chk_overlapping_dates_update
835 IS
836 SELECT s.approval_style_id
837 ,s.object_version_number
838 ,s.start_date
839 ,s.end_date
840 FROM hxc_approval_comps s
841 WHERE s.approval_comp_id <> p_approval_comp_id
842 AND s.approval_style_id = p_approval_style_id
843 AND s.time_recipient_id = p_time_recipient_id
844 AND NOT ( (s.start_date < p_start_date
845 AND NVL(s.end_date, hr_general.END_OF_TIME) < p_start_date)
846 OR (s.start_date > p_end_date
847 AND NVL(s.end_date, hr_general.END_OF_TIME)
848 > NVL(p_end_date, hr_general.END_OF_TIME))
849 )
850 AND p_time_recipient_id <> -1 ;
851 --
852 BEGIN
853 --
854 OPEN c_chk_overlapping_dates_update;
855 --
856 FETCH c_chk_overlapping_dates_update INTO p_clashing_id
857 ,p_clashing_ovn
858 ,p_clashing_start_date
859 ,p_clashing_end_date;
860 --
861 IF c_chk_overlapping_dates_update%NOTFOUND THEN
862 p_clashing_id := NULL;
863 END IF;
864 --
865 CLOSE c_chk_overlapping_dates_update;
866
867 --
868 END chk_overlapping_dates_update;
869 --
870 -- ----------------------------------------------------------------------------
871 -- |-< chk_master_detail_rel >-----------------------------------------|
872 -- ----------------------------------------------------------------------------
873 --
874 -- {Start Of Comments}
875 -- Description:
876 -- This procedure is used to enforce the business rule that if the
877 -- parent_comp_id and parent_comp_ovn are not null then a master record
878 -- must exist in the database.
879 --
880 -- Prerequisites:
881 -- None.
882 --
883 -- In Parameters:
884 -- p_parent_comp_id
885 -- p_parent_comp_ovn
886 --
887 -- Post Success:
888 -- Processing continues.
889 --
890 -- Post Failure:
891 -- An exception is raised.
892 --
893 -- Developer Implementation Notes:
894 -- None.
895 --
896 -- Access Status:
897 -- Internal Row Handler Use Only.
898 --
899 -- {End Of Comments}
900 -- ----------------------------------------------------------------------------
901 --
902 PROCEDURE chk_master_detail_rel
903 (
904 p_parent_comp_id IN hxc_approval_comps.parent_comp_id%TYPE
905 ,p_parent_comp_ovn IN hxc_approval_comps.parent_comp_ovn%TYPE )
906
907
908 IS
909 --
910 l_approval_comp_id hxc_approval_comps.approval_comp_id%TYPE;
911 l_object_version_number hxc_approval_comps.object_version_number%TYPE;
912
913 CURSOR c_chk_master_record_exists
914 IS
915 SELECT s.approval_comp_id
916 ,s.object_version_number
917 FROM hxc_approval_comps s
918 WHERE s.approval_comp_id = p_parent_comp_id
919 AND s.object_version_number = p_parent_comp_ovn
920 AND s.approval_mechanism = 'ENTRY_LEVEL_APPROVAL';
921 --
922 BEGIN
923 --
924 IF p_parent_comp_id is not null and p_parent_comp_ovn is not null then
925
926 OPEN c_chk_master_record_exists;
927 FETCH c_chk_master_record_exists into l_approval_comp_id,l_object_version_number;
928
929 IF c_chk_master_record_exists%NOTFOUND then
930
931 CLOSE c_chk_master_record_exists;
932 hr_utility.set_message
933 (809
934 ,'HXC_0402_HAC_COMP_PAR_REC_NF'
935 );
936 hr_utility.raise_error;
937
938 END IF;
939
940 CLOSE c_chk_master_record_exists;
941
942 END IF;
943
944 --
945 END chk_master_detail_rel;
946 --
947
948 --
949 --
950 -- ----------------------------------------------------------------------------
951 -- |-< chk_parent_fields >-----------------------------------------|
952 -- ----------------------------------------------------------------------------
953 --
954 -- {Start Of Comments}
955 -- Description:
956 -- This procedure is used to enforce the business rule that if the
957 -- parent_comp_id is not null then parent comp ovn must also be
958 -- not null and vice versa.
959 --
960 -- Prerequisites:
961 -- None.
962 --
966 --
963 -- In Parameters:
964 -- p_parent_comp_id
965 -- p_parent_comp_ovn
967 -- Post Success:
968 -- Processing continues.
969 --
970 -- Post Failure:
971 -- An exception is raised.
972 --
973 -- Developer Implementation Notes:
974 -- None.
975 --
976 -- Access Status:
977 -- Internal Row Handler Use Only.
978 --
979 -- {End Of Comments}
980 -- ----------------------------------------------------------------------------
981 --
982 PROCEDURE chk_parent_fields
983 (
984 p_parent_comp_id IN hxc_approval_comps.parent_comp_id%TYPE
985 ,p_parent_comp_ovn IN hxc_approval_comps.parent_comp_ovn%TYPE )
986
987 IS
988 --
989 --
990 BEGIN
991 --
992 IF (p_parent_comp_id is not null and p_parent_comp_ovn is null) or
993 (p_parent_comp_id is null and p_parent_comp_ovn is not null) then
994
995 hr_utility.set_message
996 (809
997 ,'HXC_0403_HAC_ELA_PAR_INV'
998 );
999 hr_utility.raise_error;
1000
1001
1002 END IF;
1003
1004 --
1005 END chk_parent_fields;
1006
1007
1008 --
1009 -- ----------------------------------------------------------------------------
1010 -- |-< chk_tim_cat >-----------------------------------------|
1011 -- ----------------------------------------------------------------------------
1012 --
1013 -- {Start Of Comments}
1014 -- Description:
1015 -- This procedure is used to enforce the business rule that if the
1016 -- parent_comp_id and parent_comp_ovn are not null then the
1017 -- time category must be either 0 or belong to the list of
1018 -- time categories in hxc_time_categories table.
1019 -- The time_category_id field must be null if the parent_comp_id and
1020 -- parent_comp_ovn are null.
1021 --
1022 -- Prerequisites:
1023 -- None.
1024 --
1025 -- In Parameters:
1026 -- p_parent_comp_id
1027 -- p_parent_comp_ovn
1028 -- p_time_category_id
1029 --
1030 -- Post Success:
1031 -- Processing continues.
1032 --
1033 -- Post Failure:
1034 -- An exception is raised.
1035 --
1036 -- Developer Implementation Notes:
1037 -- None.
1038 --
1039 -- Access Status:
1040 -- Internal Row Handler Use Only.
1041 --
1042 -- {End Of Comments}
1043 -- ----------------------------------------------------------------------------
1044 --
1045 PROCEDURE chk_tim_cat
1046 ( p_parent_comp_id IN hxc_approval_comps.parent_comp_id%TYPE
1047 ,p_parent_comp_ovn IN hxc_approval_comps.parent_comp_ovn%TYPE
1048 ,p_time_category_id IN hxc_approval_comps.time_category_id%TYPE )
1049
1050 IS
1051 --
1052
1053 CURSOR c_time_category_exists
1054 IS
1055 SELECT 'Y'
1056 FROM hxc_time_categories htc
1057 WHERE htc.time_category_id = p_time_category_id;
1058
1059 l_dummy VARCHAR2(1);
1060 --
1061 BEGIN
1062 --
1063 IF p_parent_comp_id is null and p_parent_comp_ovn is null and
1064 p_time_category_id is not null then
1065 hr_utility.set_message
1066 (809
1067 ,'HXC_0404_HAC_ELA_TIM_CAT_NN');
1068 hr_utility.raise_error;
1069 END IF;
1070
1071
1072 IF p_parent_comp_id is not null and p_parent_comp_ovn is not null then
1073
1074 IF p_time_category_id is null then
1075 hr_utility.set_message
1076 (809
1077 ,'HXC_0405_HAC_ELA_TIM_CAT_NULL'
1078 );
1079 hr_utility.raise_error;
1080 ELSE
1081
1082 IF p_time_category_id <> 0 then
1083
1084 OPEN c_time_category_exists;
1085 FETCH c_time_category_exists into l_dummy;
1086
1087 IF c_time_category_exists%NOTFOUND then
1088 CLOSE c_time_category_exists;
1089 hr_utility.set_message
1090 (809
1091 ,'HXC_0406_HAC_ELA_TIM_CAT_NF'
1092 );
1093 hr_utility.raise_error;
1094
1095 END IF;
1096 CLOSE c_time_category_exists;
1097 END IF;
1098 END IF;
1099 END IF;
1100 --
1101 END chk_tim_cat;
1102
1103 -- ----------------------------------------------------------------------------
1104 -- |-< chk_def_ela_rec_exists >-----------------------------------------|
1105 -- ----------------------------------------------------------------------------
1106 --
1107 -- {Start Of Comments}
1108 -- Description:
1109 -- This procedure is used to enforce the business rule that
1110 -- only one default ELA child record can exist for a parent.
1111 --
1112 -- Prerequisites:
1113 -- None.
1114 --
1115 -- In Parameters:
1116 -- p_approval_comp_id
1117 -- p_time_category_id
1118 -- p_parent_comp_id
1119 -- p_parent_comp_ovn
1120 --
1121 -- Post Success:
1122 -- Processing continues.
1123 --
1124 -- Post Failure:
1125 -- An exception is raised.
1126 --
1127 -- Developer Implementation Notes:
1128 -- None.
1129 --
1130 -- Access Status:
1131 -- Internal Row Handler Use Only.
1132 --
1133 -- {End Of Comments}
1137 (
1134 -- ----------------------------------------------------------------------------
1135 --
1136 PROCEDURE chk_def_ela_rec_exists
1138 p_approval_comp_id IN hxc_approval_comps.approval_comp_id%TYPE
1139 ,p_time_category_id IN hxc_approval_comps.time_category_id%TYPE
1140 ,p_parent_comp_id IN hxc_approval_comps.parent_comp_id%TYPE
1141 ,p_parent_comp_ovn IN hxc_approval_comps.parent_comp_ovn%TYPE )
1142
1143
1144 IS
1145 --
1146 l_approval_comp_id hxc_approval_comps.approval_comp_id%TYPE;
1147 l_object_version_number hxc_approval_comps.object_version_number%TYPE;
1148
1149 CURSOR c_def_ela_rec_exists
1150 IS
1151 SELECT s.approval_comp_id
1152 ,s.object_version_number
1153 FROM hxc_approval_comps s
1154 WHERE s.approval_comp_id <> nvl(p_approval_comp_id,-99)
1155 AND s.parent_comp_id = p_parent_comp_id
1156 AND s.parent_comp_ovn = p_parent_comp_ovn
1157 AND s.time_category_id = p_time_category_id;
1158 --
1159 BEGIN
1160 --
1161 IF p_parent_comp_id is not null and p_parent_comp_ovn is not null and p_time_category_id = 0
1162 then
1163 OPEN c_def_ela_rec_exists;
1164 FETCH c_def_ela_rec_exists into l_approval_comp_id,l_object_version_number;
1165
1166 IF c_def_ela_rec_exists%FOUND then
1167 CLOSE c_def_ela_rec_exists;
1168 hr_utility.set_message
1169 (809
1170 ,'HXC_0407_HAC_ELA_DEF_EXISTS'
1171 );
1172 hr_utility.raise_error;
1173
1174 END IF;
1175 CLOSE c_def_ela_rec_exists;
1176 END IF;
1177 --
1178 END chk_def_ela_rec_exists;
1179 --
1180
1181 --
1182 -- ----------------------------------------------------------------------------
1183 -- |-< chk_tim_rcp >-----------------------------------------|
1184 -- ----------------------------------------------------------------------------
1185 --
1186 -- {Start Of Comments}
1187 -- Description:
1188 -- This procedure is used to enforce the business rule that if the
1189 -- parent_comp_id and parent_comp_ovn are not null then the
1190 -- time recipient must be -1
1191 -- The time_category_id field must belong to the list of time recipients
1192 -- in the hxc_time_recipients table if the parent_comp_id and
1193 -- parent_comp_ovn are not null
1194 --
1195 -- Prerequisites:
1196 -- None.
1197 --
1198 -- In Parameters:
1199 -- p_parent_comp_id
1200 -- p_parent_comp_ovn
1201 -- p_time_recipient_id
1202 --
1203 -- Post Success:
1204 -- Processing continues.
1205 --
1206 -- Post Failure:
1207 -- An exception is raised.
1208 --
1209 -- Developer Implementation Notes:
1210 -- None.
1211 --
1212 -- Access Status:
1213 -- Internal Row Handler Use Only.
1214 --
1215 -- {End Of Comments}
1216 -- ----------------------------------------------------------------------------
1217 --
1218 PROCEDURE chk_tim_rcp
1219 ( p_parent_comp_id IN hxc_approval_comps.parent_comp_id%TYPE
1220 ,p_parent_comp_ovn IN hxc_approval_comps.parent_comp_ovn%TYPE
1221 ,p_time_recipient_id IN hxc_approval_comps.time_recipient_id%TYPE )
1222
1223 IS
1224 --
1225
1226 CURSOR c_time_recipient_exists
1227 IS
1228 SELECT 'Y'
1229 FROM hxc_time_recipients htr
1230 WHERE htr.time_recipient_id = p_time_recipient_id;
1231
1232 l_dummy VARCHAR2(1);
1233 --
1234 BEGIN
1235 --
1236 IF p_parent_comp_id is null and p_parent_comp_ovn is null then
1237
1238 OPEN c_time_recipient_exists;
1239 FETCH c_time_recipient_exists into l_dummy;
1240
1241 IF c_time_recipient_exists%NOTFOUND then
1242 CLOSE c_time_recipient_exists;
1243 hr_utility.set_message
1244 (809
1245 ,'HXC_0408_HAC_COMP_TRP_NF'
1246 );
1247 hr_utility.raise_error;
1248
1249 END IF;
1250 CLOSE c_time_recipient_exists;
1251 END IF;
1252
1253
1254 IF p_parent_comp_id is not null and p_parent_comp_ovn is not null then
1255
1256 IF p_time_recipient_id <> -1 THEN
1257 hr_utility.set_message
1258 (809
1259 ,'HXC_0409_HAC_ELA_TRP'
1260 );
1261
1262 hr_utility.raise_error;
1263
1264 END IF;
1265
1266 END IF;
1267
1268 --
1269 END chk_tim_rcp;
1270 --
1271 --
1272 -- ----------------------------------------------------------------------------
1273 -- |-< chk_tim_cat_dup >-----------------------------------------|
1274 -- ----------------------------------------------------------------------------
1275 --
1276 -- {Start Of Comments}
1277 -- Description:
1278 -- This procedure is used to enforce the business rule that
1279 -- for a time category and a sequence only 1 row can exist.
1280 -- Also for a time category the approval mechanisms must be
1281 -- different but if they are same then the mechanism ids must be
1282 -- different.
1283 -- Prerequisites:
1284 -- None.
1285 --
1286 -- In Parameters:
1287 -- p_approval_comp_id
1288 -- p_time_category_id
1289 -- p_parent_comp_id
1290 -- p_parent_comp_ovn
1291 -- p_approval_mechanism
1295 --
1292 -- p_approval_mechanism_id
1293 -- p_wf_name
1294 -- p_wf_item_type
1296 -- Post Success:
1297 -- Processing continues.
1298 --
1299 -- Post Failure:
1300 -- An exception is raised.
1301 --
1302 -- Developer Implementation Notes:
1303 -- None.
1304 --
1305 -- Access Status:
1306 -- Internal Row Handler Use Only.
1307 --
1308 -- {End Of Comments}
1309 -- ----------------------------------------------------------------------------
1310 --
1311 PROCEDURE chk_tim_cat_dup
1312 (
1313 p_approval_comp_id IN hxc_approval_comps.approval_comp_id%TYPE
1314 ,p_time_category_id IN hxc_approval_comps.time_category_id%TYPE
1315 ,p_approval_order IN hxc_approval_comps.approval_order%TYPE
1316 ,p_parent_comp_id IN hxc_approval_comps.parent_comp_id%TYPE
1317 ,p_parent_comp_ovn IN hxc_approval_comps.parent_comp_ovn%TYPE
1318 ,p_approval_mechanism IN hxc_approval_comps.approval_mechanism%TYPE
1319 ,p_approval_mechanism_id IN hxc_approval_comps.approval_mechanism_id%TYPE
1320 ,p_wf_name IN hxc_approval_comps.wf_name%TYPE
1321 ,p_wf_item_type IN hxc_approval_comps.wf_item_type%TYPE)
1322
1323 IS
1324 --
1325 l_dummy varchar2(1);
1326
1327 CURSOR c_tim_cat_exists
1328 IS
1329 SELECT 'Y'
1330 FROM hxc_approval_comps s
1331 WHERE s.approval_comp_id <> nvl(p_approval_comp_id,-99)
1332 AND s.parent_comp_id = p_parent_comp_id
1333 AND s.parent_comp_ovn = p_parent_comp_ovn
1334 AND s.time_category_id = p_time_category_id
1335 AND p_time_category_id <> 0
1336 AND ((s.approval_order = p_approval_order) or
1337 (s.approval_order is null) or
1338 (p_approval_order is null)
1339 );
1340
1341 CURSOR c_tim_cat_dup
1342 IS
1343 SELECT 'Y'
1344 FROM hxc_approval_comps s
1345 WHERE s.approval_comp_id <> nvl(p_approval_comp_id,-99)
1346 AND s.parent_comp_id = p_parent_comp_id
1347 AND s.parent_comp_ovn = p_parent_comp_ovn
1348 AND s.time_category_id = p_time_category_id
1349 AND p_time_category_id <> 0
1350 AND s.approval_mechanism = p_approval_mechanism
1351 AND nvl(s.approval_mechanism_id,-99) = nvl(p_approval_mechanism_id,-99)
1352 AND nvl(s.wf_name,'0') = nvl (p_wf_name,'0')
1353 AND nvl(s.wf_item_type,'0') = nvl (p_wf_item_type,'0');
1354
1355 --
1356 BEGIN
1357 --
1358 IF p_parent_comp_id is not null and
1359 p_parent_comp_ovn is not null then
1360
1361 OPEN c_tim_cat_exists;
1362 FETCH c_tim_cat_exists into l_dummy;
1363
1364 IF c_tim_cat_exists%FOUND then
1365 CLOSE c_tim_cat_exists;
1366 hr_utility.set_message
1367 (809
1368 ,'HXC_0410_HAC_ELA_TIMCAT_EXISTS'
1369 );
1370 hr_utility.raise_error;
1371
1372 END IF;
1373 CLOSE c_tim_cat_exists;
1374
1375
1376 OPEN c_tim_cat_dup;
1377 FETCH c_tim_cat_dup into l_dummy;
1378
1379 IF c_tim_cat_dup%FOUND then
1380 CLOSE c_tim_cat_dup;
1381
1382 hr_utility.set_message
1383 (809
1384 ,'HXC_0411_HAC_ELA_DUP_TIMCAT'
1385 );
1386 hr_utility.raise_error;
1387
1388 END IF;
1389 CLOSE c_tim_cat_dup;
1390
1391
1392 END IF;
1393 --
1394 END chk_tim_cat_dup;
1395 --
1396 --
1397 -- ----------------------------------------------------------------------------
1398 -- |-< chk_app_mech_for_child >-----------------------------------------|
1399 -- ----------------------------------------------------------------------------
1400 --
1401 -- {Start Of Comments}
1402 -- Description:
1403 -- This procedure is used to enforce the business rule that
1404 -- a child row cannot have the approval mechanism as
1405 -- ENTRY_LEVEL_APPROVAL. Also if the child row is the default
1406 -- row, then the approval mechanism cant be PROJECT_MANAGER.
1407 --
1408 -- Prerequisites:
1409 -- None.
1410 --
1411 -- In Parameters:
1412 -- p_time_category_id
1413 -- p_approval_mechanism
1414 -- p_parent_comp_id
1415 -- p_parent_comp_ovn
1416 --
1417 -- Post Success:
1418 -- Processing continues.
1419 --
1420 -- Post Failure:
1421 -- An exception is raised.
1422 --
1423 -- Developer Implementation Notes:
1424 -- None.
1425 --
1426 -- Access Status:
1427 -- Internal Row Handler Use Only.
1428 --
1429 -- {End Of Comments}
1430 -- ----------------------------------------------------------------------------
1431 --
1432 PROCEDURE chk_app_mech_for_child
1433 (
1434 p_time_category_id IN hxc_approval_comps.time_category_id%TYPE
1435 ,p_approval_mechanism IN hxc_approval_comps.approval_mechanism%TYPE
1436 ,p_parent_comp_id IN hxc_approval_comps.parent_comp_id%TYPE
1437 ,p_parent_comp_ovn IN hxc_approval_comps.parent_comp_ovn%TYPE
1438 )
1439
1440 IS
1441 --
1442 BEGIN
1443 --
1444 IF p_parent_comp_id is not null and
1445 p_parent_comp_ovn is not null then
1446
1447 IF p_time_category_id = 0 then
1448 IF (p_approval_mechanism = 'PROJECT_MANAGER' or
1449 p_approval_mechanism = 'ENTRY_LEVEL_APPROVAL') then
1453 ,'HXC_0412_HAC_DEF_APP_MECH'
1450
1451 hr_utility.set_message
1452 (809
1454 );
1455 hr_utility.raise_error;
1456
1457 END IF;
1458
1459 ELSE
1460 IF (p_approval_mechanism = 'ENTRY_LEVEL_APPROVAL') then
1461
1462 hr_utility.set_message
1463 (809
1464 ,'HXC_0413_HAC_ELA_APP_MECH'
1465 );
1466 hr_utility.raise_error;
1467
1468 END IF;
1469
1470 END IF;
1471
1472 END IF;
1473 --
1474 END chk_app_mech_for_child;
1475
1476 --
1477 -- ----------------------------------------------------------------------------
1478 -- |---------< chk_allowable_extensions >-------------------------------------|
1479 -- ----------------------------------------------------------------------------
1480 --
1481 -- {Start Of Comments}
1482 -- Description:
1483 -- Procedure to check run_recipient_extensions.
1484 --
1485 -- Prerequisites:
1486 -- None.
1487 --
1488 -- In Parameters:
1489 -- p_run_recipient_extensions
1490 -- p_approval_style_id
1491 --
1492 -- Post Success:
1493 -- Processing continues.
1494 --
1495 -- Post Failure:
1496 -- An exception is raised.
1497 --
1498 -- Developer Implementation Notes:
1499 -- None.
1500 --
1501 -- Access Status:
1502 -- Internal Row Handler Use Only.
1503 --
1504 -- {End Of Comments}
1505 -- ----------------------------------------------------------------------------
1506 --
1507
1508
1509 Procedure chk_allowable_extensions
1510 (
1511 p_run_recipient_extensions in hxc_approval_comps.run_recipient_extensions%type
1512 ,p_approval_style_id in hxc_approval_comps.approval_style_id%type
1513 ) is
1514
1515 cursor crs_approval_extensions is select
1516 run_recipient_extensions from
1517 hxc_approval_styles
1518 where
1519 approval_style_id=p_approval_style_id;
1520 approval_style_extensions hxc_approval_styles.run_recipient_extensions%type;
1521 Begin
1522 if not(p_run_recipient_extensions is null or p_run_recipient_extensions in ('Y','N'))
1523 then
1524 hr_utility.set_message(809,'HXC_VALUE_RECIPIENT_EXTENSIONS');
1525 hr_utility.raise_error;
1526 else
1527 open crs_approval_extensions;
1528 fetch crs_approval_extensions into approval_style_extensions;
1529 close crs_approval_extensions;
1530 if (approval_style_extensions is null or approval_style_extensions in ('N'))
1531 then
1532 if not(p_run_recipient_extensions is null or p_run_recipient_extensions ='N')
1533 then
1534 hr_utility.set_message(809,'HXC_VALUE_RECIPIENT_EXTENSIONS');
1535 hr_utility.raise_error;
1536 end if;
1537 end if;
1538 end if;
1539 end chk_allowable_extensions;
1540 --
1541
1542 --
1543 -- ----------------------------------------------------------------------------
1544 -- |---------------------------< insert_validate >----------------------------|
1545 -- ----------------------------------------------------------------------------
1546 Procedure insert_validate
1547 (p_effective_date in date
1548 ,p_rec in hxc_hac_shd.g_rec_type
1549 ) is
1550 --
1551 l_proc varchar2(72);
1552 --
1553 Begin
1554 g_debug:=hr_utility.debug_enabled;
1555 if g_debug then
1556 l_proc := g_package||'insert_validate';
1557 hr_utility.set_location('Entering:'||l_proc, 5);
1558 end if;
1559 --
1560 -- Call all supporting business operations
1561 --
1562 --
1563 -- Do some checks on the date of the approval components
1564 --
1565 chk_approval_comp_dates
1566 (p_approval_comp_id => p_rec.approval_comp_id
1567 ,p_start_date => p_rec.start_date
1568 ,p_end_date => p_rec.end_date
1569 );
1570 --
1571 chk_invalid_dates_create
1572 (p_approval_style_id => p_rec.approval_style_id
1573 ,p_time_recipient_id => p_rec.time_recipient_id
1574 ,p_start_date => p_rec.start_date
1575 ,p_end_date => p_rec.end_date
1576 );
1577 --
1578 chk_overlapping_dates_create
1579 (p_approval_style_id => p_rec.approval_style_id
1580 ,p_time_recipient_id => p_rec.time_recipient_id
1581 ,p_start_date => p_rec.start_date
1582 ,p_end_date => p_rec.end_date
1583 );
1584 --
1585 --
1586 chk_parent_fields
1587 (p_parent_comp_id => p_rec.parent_comp_id
1588 ,p_parent_comp_ovn => p_rec.parent_comp_ovn);
1589
1590 chk_tim_cat
1591 (p_parent_comp_id => p_rec.parent_comp_id
1592 ,p_parent_comp_ovn => p_rec.parent_comp_ovn
1593 ,p_time_category_id => p_rec.time_category_id);
1594
1595 chk_tim_rcp
1596 (p_parent_comp_id => p_rec.parent_comp_id
1597 ,p_parent_comp_ovn => p_rec.parent_comp_ovn
1598 ,p_time_recipient_id => p_rec.time_recipient_id);
1599
1600 chk_def_ela_rec_exists
1601 (p_approval_comp_id => p_rec.approval_comp_id
1602 ,p_time_category_id => p_rec.time_category_id
1603 ,p_parent_comp_id => p_rec.parent_comp_id
1604 ,p_parent_comp_ovn => p_rec.parent_comp_ovn );
1605
1609 ,p_time_category_id => p_rec.time_category_id
1606
1607 chk_tim_cat_dup
1608 ( p_approval_comp_id => p_rec.approval_comp_id
1610 ,p_approval_order => p_rec.approval_order
1611 ,p_parent_comp_id => p_rec.parent_comp_id
1612 ,p_parent_comp_ovn => p_rec.parent_comp_ovn
1613 ,p_approval_mechanism => p_rec.approval_mechanism
1614 ,p_approval_mechanism_id => p_rec.approval_mechanism_id
1615 ,p_wf_name => p_rec.wf_name
1616 ,p_wf_item_type => p_rec.wf_item_type);
1617
1618 chk_app_mech_for_child
1619 ( p_time_category_id => p_rec.time_category_id
1620 ,p_approval_mechanism => p_rec.approval_mechanism
1621 ,p_parent_comp_id => p_rec.parent_comp_id
1622 ,p_parent_comp_ovn => p_rec.parent_comp_ovn
1623 );
1624
1625
1626 --do some checks on the master detail relationship
1627
1628 chk_master_detail_rel
1629 (p_parent_comp_id => p_rec.parent_comp_id
1630 ,p_parent_comp_ovn => p_rec.parent_comp_ovn);
1631
1632 chk_allowable_extensions
1633 (
1634 p_run_recipient_extensions => p_rec.run_recipient_extensions
1635 ,p_approval_style_id => p_rec.approval_style_id
1636 );
1637
1638 -- EDIT_HERE: As this table does not have a mandatory business_group_id
1639 -- column, ensure client_info is populated by calling a suitable
1640 -- ???_???_bus.set_security_group_id procedure, or add one of the following
1641 -- comments:
1642 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
1643 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
1644 --
1645 --
1646 if g_debug then
1647 hr_utility.set_location(' Leaving:'||l_proc, 10);
1648 end if;
1649 End insert_validate;
1650 --
1651 -- ----------------------------------------------------------------------------
1652 -- |---------------------------< update_validate >----------------------------|
1653 -- ----------------------------------------------------------------------------
1654 Procedure update_validate
1655 (p_effective_date in date
1656 ,p_rec in hxc_hac_shd.g_rec_type
1657 ) is
1658 --
1659 l_proc varchar2(72);
1660 --
1661 Begin
1662 g_debug:=hr_utility.debug_enabled;
1663 if g_debug then
1664 l_proc := g_package||'update_validate';
1665 hr_utility.set_location('Entering:'||l_proc, 5);
1666 end if;
1667 --
1668 -- Call all supporting business operations
1669 --
1670 --
1671 -- EDIT_HERE: As this table does not have a mandatory business_group_id
1672 -- column, ensure client_info is populated by calling a suitable
1673 -- ???_???_bus.set_security_group_id procedure, or add one of the following
1674 -- comments:
1675 -- "-- No business group context. HR_STANDARD_LOOKUPS used for validation."
1676 -- "-- CLIENT_INFO not set. No lookup validation or joins to HR_LOOKUPS."
1677 --
1678 chk_non_updateable_args
1679 (p_effective_date => p_effective_date
1680 ,p_rec => p_rec
1681 );
1682 --
1683 -- Do some checks on the date of the approval components
1684 --
1685 chk_approval_comp_dates
1686 (p_approval_comp_id => p_rec.approval_comp_id
1687 ,p_start_date => p_rec.start_date
1688 ,p_end_date => p_rec.end_date
1689 );
1690 --
1691 chk_invalid_dates_update
1692 (p_approval_comp_id => p_rec.approval_comp_id
1693 ,p_approval_style_id => p_rec.approval_style_id
1694 ,p_time_recipient_id => p_rec.time_recipient_id
1695 ,p_start_date => p_rec.start_date
1696 ,p_end_date => p_rec.end_date
1697 );
1698 --
1699 chk_overlapping_dates_update
1700 (p_approval_comp_id => p_rec.approval_comp_id
1701 ,p_approval_style_id => p_rec.approval_style_id
1702 ,p_time_recipient_id => p_rec.time_recipient_id
1703 ,p_start_date => p_rec.start_date
1704 ,p_end_date => p_rec.end_date
1705 );
1706
1707 chk_parent_fields
1708 (p_parent_comp_id => p_rec.parent_comp_id
1709 ,p_parent_comp_ovn => p_rec.parent_comp_ovn);
1710
1711 chk_tim_cat
1712 (p_parent_comp_id => p_rec.parent_comp_id
1713 ,p_parent_comp_ovn => p_rec.parent_comp_ovn
1714 ,p_time_category_id => p_rec.time_category_id);
1715
1716 chk_tim_rcp
1717 (p_parent_comp_id => p_rec.parent_comp_id
1718 ,p_parent_comp_ovn => p_rec.parent_comp_ovn
1719 ,p_time_recipient_id => p_rec.time_recipient_id);
1720
1721 chk_def_ela_rec_exists
1722 (p_approval_comp_id => p_rec.approval_comp_id
1723 ,p_time_category_id => p_rec.time_category_id
1724 ,p_parent_comp_id => p_rec.parent_comp_id
1725 ,p_parent_comp_ovn => p_rec.parent_comp_ovn );
1726
1727 chk_tim_cat_dup
1728 ( p_approval_comp_id => p_rec.approval_comp_id
1729 ,p_time_category_id => p_rec.time_category_id
1730 ,p_approval_order => p_rec.approval_order
1731 ,p_parent_comp_id => p_rec.parent_comp_id
1732 ,p_parent_comp_ovn => p_rec.parent_comp_ovn
1733 ,p_approval_mechanism => p_rec.approval_mechanism
1734 ,p_approval_mechanism_id => p_rec.approval_mechanism_id
1735 ,p_wf_name => p_rec.wf_name
1736 ,p_wf_item_type => p_rec.wf_item_type);
1737
1738 chk_app_mech_for_child
1739 ( p_time_category_id => p_rec.time_category_id
1740 ,p_approval_mechanism => p_rec.approval_mechanism
1741 ,p_parent_comp_id => p_rec.parent_comp_id
1742 ,p_parent_comp_ovn => p_rec.parent_comp_ovn
1743 );
1744 chk_allowable_extensions
1745 (
1746 p_run_recipient_extensions => p_rec.run_recipient_extensions
1747 ,p_approval_style_id => p_rec.approval_style_id
1748 );
1749 --do some checks on the master detail relationship
1750
1751 chk_master_detail_rel
1752 (p_parent_comp_id => p_rec.parent_comp_id
1753 ,p_parent_comp_ovn => p_rec.parent_comp_ovn);
1754 --
1755 if g_debug then
1756 hr_utility.set_location(' Leaving:'||l_proc, 10);
1757 end if;
1758 End update_validate;
1759 --
1760 -- ----------------------------------------------------------------------------
1761 -- |---------------------------< delete_validate >----------------------------|
1762 -- ----------------------------------------------------------------------------
1763 Procedure delete_validate
1764 (p_rec in hxc_hac_shd.g_rec_type
1765 ) is
1766 --
1767 l_proc varchar2(72);
1768 --
1769 Begin
1770 g_debug:=hr_utility.debug_enabled;
1771 if g_debug then
1772 l_proc := g_package||'delete_validate';
1773 hr_utility.set_location('Entering:'||l_proc, 5);
1774 end if;
1775 --
1776 -- Call all supporting business operations
1777 --
1778 if g_debug then
1779 hr_utility.set_location(' Leaving:'||l_proc, 10);
1780 end if;
1781 End delete_validate;
1782 --
1783 end hxc_hac_bus;