[Home] [Help]
PACKAGE BODY: APPS.PER_PCE_BUS
Source
1 Package Body per_pce_bus as
2 /* $Header: pepcerhi.pkb 120.1 2006/10/18 09:19:34 grreddy noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := ' per_pce_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code VARCHAR2(150) DEFAULT NULL;
14 g_cagr_entitlement_id NUMBER DEFAULT NULL;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 PROCEDURE set_security_group_id
21 (p_cagr_entitlement_id IN NUMBER
22 ,p_collective_agreement_id IN per_cagr_entitlements.collective_agreement_ID%TYPE
23 ) IS
24 --
25 -- Declare cursor
26 --
27 CURSOR csr_sec_grp IS
28 SELECT pbg.security_group_id
29 FROM per_business_groups pbg,
30 per_cagr_entitlements pce,
31 per_collective_agreements pca
32 WHERE pce.cagr_entitlement_id = p_cagr_entitlement_id
33 AND pca.collective_agreement_id = p_collective_agreement_id
34 AND pbg.business_group_id = pca.business_group_id;
35
36 --
37 -- Declare local variables
38 --
39 l_security_group_id NUMBER;
40 l_proc VARCHAR2(72) := g_package||'set_security_group_id';
41 --
42 BEGIN
43 --
44 hr_utility.set_location('Entering:'|| l_proc, 10);
45 --
46 -- Ensure that all the mandatory parameter are not NULL
47 --
48 hr_api.mandatory_arg_error
49 (p_api_name => l_proc
50 ,p_argument => 'cagr_entitlement_id'
51 ,p_argument_value => p_cagr_entitlement_id
52 );
53 --
54 OPEN csr_sec_grp;
55 FETCH csr_sec_grp INTO l_security_group_id;
56 --
57 IF csr_sec_grp%notfound THEN
58 --
59 CLOSE csr_sec_grp;
60 --
61 -- The primary key IS invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 fnd_message.raise_error;
65 --
66 END IF;
67 CLOSE csr_sec_grp;
68 --
69 -- Set the security_group_id IN CLIENT_INFO
70 --
71 hr_api.set_security_group_id
72 (p_security_group_id => l_security_group_id
73 );
74 --
75 hr_utility.set_location(' Leaving:'|| l_proc, 20);
76 --
77 END set_security_group_id;
78 --
79 -- ---------------------------------------------------------------------------
80 -- |---------------------< return_legislation_code >-------------------------|
81 -- ---------------------------------------------------------------------------
82 --
83 FUNCTION return_legislation_code
84 (p_cagr_entitlement_id IN NUMBER
85 ,p_collective_agreement_id IN per_cagr_entitlements.collective_agreement_ID%TYPE
86 )
87 RETURN VARCHAR2 IS
88 --
89 -- Declare cursor
90 --
91
92 CURSOR csr_leg_code IS
93 SELECT pbg.legislation_code
94 FROM per_business_groups pbg,
95 per_cagr_entitlements pce,
96 per_collective_agreements pca
97 WHERE pce.cagr_entitlement_id = p_cagr_entitlement_id
98 AND pca.collective_agreement_id = p_collective_agreement_id
99 AND pbg.business_group_id = pca.business_group_id;
100 --
101 -- Declare local variables
102 --
103 l_legislation_code VARCHAR2(150);
104 l_proc VARCHAR2(72) := g_package||'return_legislation_code';
105 --
106 BEGIN
107 --
108 hr_utility.set_location('Entering:'|| l_proc, 10);
109 --
110 -- Ensure that all the mandatory parameter are not NULL
111 --
112 hr_api.mandatory_arg_error
113 (p_api_name => l_proc
114 ,p_argument => 'cagr_entitlement_id'
115 ,p_argument_value => p_cagr_entitlement_id
116 );
117 --
118 IF ( nvl(per_pce_bus.g_cagr_entitlement_id, hr_api.g_number)
119 = p_cagr_entitlement_id) THEN
120 --
121 -- The legislation code has already been found with a previous
122 -- call to this function. Just return the value IN the global
123 -- variable.
124 --
125 l_legislation_code := per_pce_bus.g_legislation_code;
126 hr_utility.set_location(l_proc, 20);
127 else
128 --
129 -- The ID IS different to the last call to this function
130 -- or this IS the first call to this function.
131 --
132 open csr_leg_code;
133 fetch csr_leg_code into l_legislation_code;
134 --
135 if csr_leg_code%notfound then
136 --
137 -- The primary key IS invalid therefore we must error
138 --
139 close csr_leg_code;
140 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
141 fnd_message.raise_error;
142 end if;
143 hr_utility.set_location(l_proc,30);
144 --
145 -- Set the global variables so the values are
146 -- available for the next call to this function.
147 --
148 close csr_leg_code;
149 per_pce_bus.g_cagr_entitlement_id := p_cagr_entitlement_id;
150 per_pce_bus.g_legislation_code := l_legislation_code;
151 END IF;
152 hr_utility.set_location(' Leaving:'|| l_proc, 40);
153 RETURN l_legislation_code;
154 END return_legislation_code;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |-----------------------< chk_non_updateable_args >------------------------|
158 -- ----------------------------------------------------------------------------
159 -- {Start Of Comments}
160 --
161 -- Description:
162 -- This procedure IS used to ensure that non updateable attributes have
163 -- not been updated. If an attribute has been updated an error IS generated.
164 --
165 -- Pre Conditions:
166 -- g_old_rec has been populated with details of the values currently IN
167 -- the database.
168 --
169 -- In Arguments:
170 -- p_rec has been populated with the updated values the user would like the
171 -- record set to.
172 --
173 -- Post Success:
174 -- Processing continues if all the non updateable attributes have not
175 -- changed.
176 --
177 -- Post Failure:
178 -- An application error IS raised if any of the non updatable attributes
179 -- have been altered.
180 --
181 -- {END Of Comments}
182 -- ----------------------------------------------------------------------------
183 PROCEDURE chk_non_updateable_args
184 (p_effective_date IN DATE
185 ,p_rec IN per_pce_shd.g_rec_type
186 ) IS
187 --
188 l_proc VARCHAR2(72) := g_package || 'chk_non_updateable_args';
189 l_error EXCEPTION;
190 l_argument VARCHAR2(30);
191 --
192 BEGIN
193 --
194 -- Only proceed with the validation if a row exists for the current
195 -- record IN the HR Schema.
196 --
197 IF NOT per_pce_shd.api_updating
198 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
199 ,p_object_version_number => p_rec.object_version_number
200 ) THEN
201 --
202 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
203 fnd_message.set_token('PROCEDURE ', l_proc);
204 fnd_message.set_token('STEP ', '5');
205 fnd_message.raise_error;
206 --
207 END IF;
208 --
209 IF nvl(p_rec.collective_agreement_id, hr_api.g_number) <>
210 nvl(per_pce_shd.g_old_rec.collective_agreement_id,hr_api.g_number) THEN
211 --
212 l_argument := 'collective_agreement_id';
213 RAISE l_error;
214 --
215 END IF;
216 --
217 IF nvl(p_rec.cagr_entitlement_item_id, hr_api.g_number) <>
218 nvl(per_pce_shd.g_old_rec.cagr_entitlement_item_id,hr_api.g_number) THEN
219 --
220 l_argument := 'cagr_entitlement_item_id';
221 RAISE l_error;
222 --
223 END IF;
224 --
225 IF nvl(p_rec.formula_criteria, hr_api.g_varchar2) <>
226 nvl(per_pce_shd.g_old_rec.formula_criteria,hr_api.g_varchar2) THEN
227 --
228 l_argument := 'formula_criteria';
229 RAISE l_error;
230 --
231 END IF;
232 --
233 IF nvl(p_rec.cagr_entitlement_item_id, hr_api.g_number) <>
234 nvl(per_pce_shd.g_old_rec.cagr_entitlement_item_id,hr_api.g_number) THEN
235 --
236 l_argument := 'cagr_entitlement_item_id';
237 RAISE l_error;
238 --
239 END IF;
240 --
241 EXCEPTION
242 WHEN l_error THEN
243 hr_api.argument_changed_error
244 (p_api_name => l_proc
245 ,p_argument => l_argument);
246 WHEN OTHERS THEN
247 RAISE;
248 --
249 END chk_non_updateable_args;
250 --
251 -- ---------------------------------------------------------------------------
252 -- |-------------------------------< chk_status >----------------------------|
253 -- ---------------------------------------------------------------------------
254 --
255 -- Desciption :
256 --
257 -- Validate that that the status exists in HR_LOOKUPS for the
258 -- lookup type 'CAGR_STATUS'
259 --
260 -- Pre-conditions :
261 --
262 --
263 -- In Arguments :
264 -- p_cagr_entitlement_id
265 -- p_effective_date
266 -- p_status
267 --
268 -- Post Success :
269 -- Processing continues
270 --
271 -- Post Failure :
272 -- An application error will be raised and processing IS
273 -- terminated
274 --
275 -- Access Status :
276 -- Internal Table Handler Use only.
277 --
278 -- {END of Comments}
279 --
280 -- ---------------------------------------------------------------------------
281 --
282 PROCEDURE chk_status
283 (p_cagr_entitlement_id IN per_cagr_entitlements.cagr_entitlement_id%TYPE
284 ,p_effective_date IN DATE
285 ,p_status IN per_cagr_entitlements.status%TYPE
286 ) IS
287 --
288 -- Declare Local variables
289 --
290 l_proc VARCHAR2(72) := g_package||'chk_status';
291 --
292 BEGIN
293 --
294 hr_utility.set_location('Entering : '||l_proc,10);
295 --
296 -- Check mandatory parameter IS set
297 --
298 hr_api.mandatory_arg_error
299 (p_api_name => l_proc
300 ,p_argument => 'effective_date'
301 ,p_argument_value => p_effective_date
302 );
303 --
304 hr_api.mandatory_arg_error
305 (p_api_name => l_proc
306 ,p_argument => 'status'
307 ,p_argument_value => p_status
308 );
309 --
310 hr_utility.set_location(l_proc,20);
311 --
312 -- Only proceed with validation if :
313 -- a) Inserting or
314 -- b) The value for status has changed
315 --
316 IF ( (p_cagr_entitlement_id IS NULL) OR
317 ((p_cagr_entitlement_id IS NOT NULL) AND
318 (per_pce_shd.g_old_rec.status <> p_status))) THEN
319 --
320 hr_utility.set_location(l_proc,30);
321 --
322 -- Check that the status exists IN HR_LOOKUPS
323 --
324 IF hr_api.not_exists_in_hr_lookups
325 (p_effective_date => p_effective_date
326 ,p_lookup_type => 'CAGR_STATUS'
327 ,p_lookup_code => p_status) THEN
328 --
329 hr_utility.set_location(l_proc, 40);
330 --
331 hr_utility.set_message(800, 'HR_289267_STATUS_INVALID');
332 hr_utility.raise_error;
333 --
334 END IF;
335 --
336 END IF;
337 --
338 hr_utility.set_location('Leaving : '||l_proc, 999);
339 --
340 END chk_status;
341 --
342 -- ---------------------------------------------------------------------------
343 -- |---------------------------< chk_message_level >-------------------------|
344 -- ---------------------------------------------------------------------------
345 --
346 -- Desciption :
347 --
348 -- Validate that that the message_level exists in HR_LOOKUPS for the
349 -- lookup type 'CAGR_MESSAGE_LEVEL'
350 --
351 -- Pre-conditions :
352 --
353 --
354 -- In Arguments :
355 -- p_cagr_entitlement_id
356 -- p_effective_date
357 -- p_message_level
358 --
359 -- Post Success :
360 -- Processing continues
361 --
362 -- Post Failure :
363 -- An application error will be raised and processing IS
364 -- terminated
365 --
366 -- Access Status :
367 -- Internal Table Handler Use only.
368 --
369 -- {END of Comments}
370 --
371 -- ---------------------------------------------------------------------------
372 --
373 PROCEDURE chk_message_level
374 (p_cagr_entitlement_id IN per_cagr_entitlements.cagr_entitlement_id%TYPE
375 ,p_effective_date IN DATE
376 ,p_message_level IN per_cagr_entitlements.message_level%TYPE
377 ) IS
378 --
379 -- Declare Local variables
380 --
381 l_proc VARCHAR2(72) := g_package||'chk_message_level';
382 --
383 BEGIN
384 --
385 hr_utility.set_location('Entering : '||l_proc,10);
386 --
387 IF p_message_level IS NOT NULL THEN
388 --
389 -- Check mandatory parameter IS set
390 --
391 hr_api.mandatory_arg_error
392 (p_api_name => l_proc
393 ,p_argument => 'effective_date'
394 ,p_argument_value => p_effective_date
395 );
396 --
397 hr_utility.set_location(l_proc,20);
398 --
399 --
400 -- Only proceed with validation if :
401 -- a) Inserting or
402 -- b) The value for message level has changed
403 --
404 IF ( (p_cagr_entitlement_id IS NULL) OR
405 ((p_cagr_entitlement_id IS NOT NULL) AND
406 (per_pce_shd.g_old_rec.message_level <> p_message_level))) THEN
407 --
408 hr_utility.set_location(l_proc,30);
409 --
410 -- Check that the unit_of_measure exists IN HR_LOOKUPS
411 --
412 IF hr_api.not_exists_in_hr_lookups
413 (p_effective_date => p_effective_date
414 ,p_lookup_type => 'CAGR_MESSAGE_LEVEL'
415 ,p_lookup_code => p_message_level) THEN
416 --
417 hr_utility.set_location(l_proc, 40);
418 --
419 hr_utility.set_message(800, 'HR_289268_MESSAGE_LEVEL_INV');
420 hr_utility.raise_error;
421 --
422 END IF;
423 --
424 END IF;
425 --
426 END IF;
427 --
428 hr_utility.set_location('Leaving : '||l_proc, 999);
429 --
430 END chk_message_level;
431 --
432 -- ---------------------------------------------------------------------------
433 -- |---------------------< chk_formula_criteria_mismatch >-------------------|
434 -- ---------------------------------------------------------------------------
435 --
436 -- Desciption :
437 --
438 -- Validate that that the formula_criteria, and formula_id match. So
439 -- if the formula_criteria is 'F' then formula_id must be populated and
440 -- if the formula_criteria is 'C' then formula_id must be null.
441 --
442 --
443 -- Pre-conditions :
444 --
445 --
446 -- In Arguments :
447 -- p_formula_id
448 -- p_formula_criteria
449 --
450 -- Post Success :
451 -- Processing continues
452 --
453 -- Post Failure :
454 -- An application error will be raised and processing IS
455 -- terminated
456 --
457 -- Access Status :
458 -- Internal Table Handler Use only.
459 --
460 -- {END of Comments}
461 --
462 -- ---------------------------------------------------------------------------
463 --
464 PROCEDURE chk_formula_criteria_mismatch
465 (p_formula_id IN per_cagr_entitlements.formula_id%TYPE
466 ,p_formula_criteria IN per_cagr_entitlements.formula_criteria%TYPE
467 ) IS
468 --
469 -- Declare Local variables
470 --
471 l_proc VARCHAR2(72) := g_package||'chk_formula_criteria_mismatch';
472 --
473 BEGIN
474 --
475 hr_utility.set_location('Entering : '||l_proc,10);
476 --
477 -- Check that if the formula_id IS populated then the
478 -- formula_criteria value should be 'F'
479 --
480 IF p_formula_criteria = 'F' AND p_formula_id IS NULL THEN
481 --
482 hr_utility.set_location(l_proc, 30);
483 --
484 hr_utility.set_message(800, 'HR_289264_FORMULA_CRITERIA_INV');
485 hr_utility.raise_error;
486 --
487 ELSIF p_formula_criteria = 'C' AND p_formula_id IS NOT NULL THEN
488 --
489 hr_utility.set_location(l_proc, 30);
490 --
491 hr_utility.set_message(800, 'HR_289265_CRITERIA_FORMULA_MIS');
492 hr_utility.raise_error;
493 --
494 END IF;
495 --
496 hr_utility.set_location('Leaving : '||l_proc, 999);
497 --
498 END chk_formula_criteria_mismatch;
499 --
500 -- ---------------------------------------------------------------------------
501 -- |------------------------< chk_formula_criteria >-------------------------|
502 -- ---------------------------------------------------------------------------
503 --
504 -- Desciption :
505 --
506 -- Validate that that the formula_criteria exists in HR_LOOKUPS for the
507 -- lookup_type 'CAGR_CRITERIA_TYPE'
508 --
509 --
510 -- Pre-conditions :
511 --
512 --
513 -- In Arguments :
514 -- p_cagr_entitlement_id
515 -- p_effective_date
516 -- p_formula_id
517 -- p_formula_criteria
518 --
519 -- Post Success :
520 -- Processing continues
521 --
522 -- Post Failure :
523 -- An application error will be raised and processing IS
524 -- terminated
525 --
526 -- Access Status :
527 -- Internal Table Handler Use only.
528 --
529 -- {END of Comments}
530 --
531 -- ---------------------------------------------------------------------------
532 --
533 PROCEDURE chk_formula_criteria
534 (p_cagr_entitlement_id IN per_cagr_entitlements.cagr_entitlement_id%TYPE
535 ,p_effective_date IN DATE
536 ,p_formula_id IN per_cagr_entitlements.formula_id%TYPE
537 ,p_formula_criteria IN per_cagr_entitlements.formula_criteria%TYPE
538 ) IS
539 --
540 -- Declare Local variables
541 --
542 l_proc VARCHAR2(72) := g_package||'chk_formula_criteria';
543 --
544 BEGIN
545 --
546 hr_utility.set_location('Entering : '||l_proc,10);
547 --
548 -- Check mandatory parameter IS set
549 --
550 hr_api.mandatory_arg_error
551 (p_api_name => l_proc
552 ,p_argument => 'effective_date'
553 ,p_argument_value => p_effective_date
554 );
555 --
556 hr_api.mandatory_arg_error
557 (p_api_name => l_proc
558 ,p_argument => 'formula_criteria'
559 ,p_argument_value => p_formula_criteria
560 );
561 --
562 hr_utility.set_location(l_proc,20);
563 --
564 -- Only validate if we are inserting
565 --
566 IF ( (p_cagr_entitlement_id IS NULL) OR
567 ((p_cagr_entitlement_id IS NOT NULL) AND
568 (per_pce_shd.g_old_rec.formula_id <> p_formula_id))) THEN
569 --
570 hr_utility.set_location(l_proc,30);
571 --
572 -- Check that the formula_criteria exists IN HR_LOOKUPS
573 --
574 IF hr_api.not_exists_in_hr_lookups
575 (p_effective_date => p_effective_date
576 ,p_lookup_type => 'CAGR_CRITERIA_TYPE'
577 ,p_lookup_code => p_formula_criteria) THEN
578 --
579 hr_utility.set_location(l_proc, 40);
580 --
581 hr_utility.set_message(800, 'HR_289354_INV_FORM_CRITERIA');
582 hr_utility.raise_error;
583 --
584 END IF;
585 --
586 hr_utility.set_location(l_proc, 50);
587 --
588 END IF;
589 --
590 hr_utility.set_location('Leaving : '||l_proc, 999);
591 --
592 END chk_formula_criteria;
593 --
594 -- ---------------------------------------------------------------------------
595 -- |-----------------------< chk_cagr_entitlement_item_id >------------------|
596 -- ---------------------------------------------------------------------------
597 --
598 -- Desciption :
599 --
600 -- Validates that the cagr_entitlement_item_id exists in
601 -- PER_CAGR_ENTITLEMENT_ITEMS.
602 --
603 --
604 -- Pre-conditions :
605 --
606 --
607 -- In Arguments :
608 -- p_cagr_entitlement_id
609 -- p_cagr_entitlement_item_id
610 --
611 -- Post Success :
612 -- Processing continues
613 --
614 -- Post Failure :
615 -- An application error will be raised and processing IS
616 -- terminated
617 --
618 -- Access Status :
619 -- Internal Table Handler Use only.
620 --
621 -- {END of Comments}
622 --
623 -- ---------------------------------------------------------------------------
624 --
625 PROCEDURE chk_cagr_entitlement_item_id
626 (p_cagr_entitlement_id IN per_cagr_entitlements.cagr_entitlement_id%TYPE
627 ,p_cagr_entitlement_item_id IN NUMBER) IS
628 --
629 -- Declare Cursors
630 --
631 CURSOR chk_id IS
632 SELECT cei.cagr_entitlement_item_id
633 FROM per_cagr_entitlement_items cei
634 WHERE cei.cagr_entitlement_item_id = p_cagr_entitlement_item_id;
635 --
636 -- Declare Local variables
637 --
638 l_proc VARCHAR2(72) := g_package||'chk_cagr_entitlement_item_id';
639 l_item_id per_cagr_entitlements.cagr_entitlement_item_id%TYPE;
640 --
641 BEGIN
642 --
643 hr_utility.set_location('Entering : '||l_proc,10);
644 --
645 -- Only validate if we are inserting a record
646 --
647 IF p_cagr_entitlement_id IS NULL THEN
648 --
649 hr_utility.set_location(l_proc,20);
650 --
651 OPEN chk_id;
652 FETCH chk_id INTO l_item_id;
653 --
654 -- If the entitlement item id does not exists
655 -- then raise an error.
656 --
657 IF chk_id%NOTFOUND THEN
658 --
659 CLOSE chk_id;
660 --
661 hr_utility.set_message(800, 'HR_289353_ITEM_ID_INVALID');
662 hr_utility.raise_error;
663 --
664 ELSE
665 --
666 CLOSE chk_id;
667 --
668 END IF;
669 --
670 END IF;
671 hr_utility.set_location('Leaving : '||l_proc,999);
672 --
673 END chk_cagr_entitlement_item_id;
674 --
675 -- ---------------------------------------------------------------------------
676 -- |-----------------------< chk_collective_agreement_id >-------------------|
677 -- ---------------------------------------------------------------------------
678 --
679 -- Desciption :
680 --
681 -- Validates that the collective_agreement_id exists in the
682 -- PER_COLLECTIVE_AGREEMENTS table.
683 --
684 --
685 -- Pre-conditions :
686 --
687 --
688 -- In Arguments :
689 -- p_cagr_entitlement_id
690 -- p_cagr_collective_agreement_id
691 --
692 -- Post Success :
693 -- Processing continues
694 --
695 -- Post Failure :
696 -- An application error will be raised and processing IS
697 -- terminated
698 --
699 -- Access Status :
700 -- Internal Table Handler Use only.
701 --
702 -- {END of Comments}
703 --
704 -- ---------------------------------------------------------------------------
705 --
706 PROCEDURE chk_collective_agreement_id
707 (p_cagr_entitlement_id IN per_cagr_entitlements.cagr_entitlement_id%TYPE
708 ,p_business_group_id IN NUMBER
709 ,p_collective_agreement_id IN NUMBER) IS
710 --
711 -- Declare Cursors
712 --
713 CURSOR chk_id IS
714 SELECT cag.collective_agreement_id
715 FROM per_collective_agreements cag
716 WHERE cag.business_group_id = p_business_group_id
717 AND cag.collective_agreement_id = p_collective_agreement_id;
718 --
719 -- Declare Local variables
720 --
721 l_proc VARCHAR2(72) := g_package||'chk_collective_agreement_id';
722 l_item_id per_cagr_entitlements.collective_agreement_id%TYPE;
723 --
724 BEGIN
725 --
726 hr_utility.set_location('Entering : '||l_proc,10);
727 --
728 -- Only validate if we are inserting a record
729 --
730 IF p_cagr_entitlement_id IS NULL THEN
731 --
732 hr_utility.set_location(l_proc,20);
733 --
734 OPEN chk_id;
735 FETCH chk_id INTO l_item_id;
736 --
737 -- If the entitlement item id does not exists
738 -- then raise an error.
739 --
740 IF chk_id%NOTFOUND THEN
741 --
742 CLOSE chk_id;
743 --
744 hr_utility.set_message(800, 'PER_52816_COLLECTIVE_AGREEMENT');
745 hr_utility.raise_error;
746 --
747 ELSE
748 --
749 CLOSE chk_id;
750 --
751 END IF;
752 --
753 END IF;
754 hr_utility.set_location('Leaving : '||l_proc,999);
755 --
756 END chk_collective_agreement_id;
757 --
758 -- ---------------------------------------------------------------------------
759 -- |---------------------------< chk_formula_id >----------------------------|
760 -- ---------------------------------------------------------------------------
761 --
762 -- Desciption :
763 --
764 -- Validate that that the formula_id exists IN FF_FORMULAS_F and is of
765 -- the new collective agreement type.
766 --
767 --
768 -- Pre-conditions :
769 --
770 --
771 -- In Arguments :
772 -- p_cagr_entitlement_id
773 -- p_effective_date
774 -- p_formula_id
775 -- p_formula_criteria
776 --
777 -- Post Success :
778 -- Processing continues
779 --
780 -- Post Failure :
781 -- An application error will be raised and processing IS
782 -- terminated
783 --
784 -- Access Status :
785 -- Internal Table Handler Use only.
786 --
787 -- {END of Comments}
788 --
789 -- ---------------------------------------------------------------------------
790 --
791 PROCEDURE chk_formula_id
792 (p_cagr_entitlement_id IN per_cagr_entitlements.cagr_entitlement_id%TYPE
793 ,p_effective_date IN DATE
794 ,p_business_group_id IN NUMBER
795 ,p_formula_id IN per_cagr_entitlements.formula_id%TYPE
796 ,p_formula_criteria IN per_cagr_entitlements.formula_criteria%TYPE
797 ) IS
798 --
799 -- Declare Cursors
800 --
801 CURSOR chk_formula_id IS
802 SELECT ff.formula_id
803 FROM ff_formulas_f ff,
804 ff_formula_types ft,
805 per_business_groups pg
806 WHERE NVL(ff.legislation_code, pg.legislation_code) =
807 pg.legislation_code
808 AND NVL(ff.business_group_id,p_business_group_id) =
809 pg.business_group_id
810 AND pg.business_group_id = p_business_group_id
811 AND ft.formula_type_name = 'CAGR'
812 AND ft.formula_type_id = ff.formula_type_id
813 AND ff.formula_id = p_formula_id
814 AND p_effective_date BETWEEN ff.effective_start_date
815 AND ff.effective_end_date;
816 --
817 -- Declare Local variables
818 --
819 l_proc VARCHAR2(72) := g_package||'chk_formula_id';
820 l_dummy_id per_cagr_entitlements.formula_id%TYPE;
821 --
822 BEGIN
823 --
824 hr_utility.set_location('Entering : '||l_proc,10);
825 --
826 -- Check mandatory parameter IS set
827 --
828 hr_api.mandatory_arg_error
829 (p_api_name => l_proc
830 ,p_argument => 'effective_date'
831 ,p_argument_value => p_effective_date
832 );
833 --
834 hr_api.mandatory_arg_error
835 (p_api_name => l_proc
836 ,p_argument => 'formula_criteria'
837 ,p_argument_value => p_formula_criteria
838 );
839 --
840 hr_utility.set_location(l_proc,20);
841 --
842 -- Only proceed with validation if :
843 -- a) Inserting or
844 -- b) The value for formula id has changed
845 --
846 IF ( (p_cagr_entitlement_id IS NULL) OR
847 ((p_cagr_entitlement_id IS NOT NULL) AND
848 (per_pce_shd.g_old_rec.formula_id <> p_formula_id))) THEN
849 --
850 hr_utility.set_location(l_proc,30);
851 --
852 -- Only check the ff_formula_f table if the
853 -- p_formula_id has a values
854 --
855 IF p_formula_id IS NOT NULL THEN
856 --
857 OPEN chk_formula_id;
858 FETCH chk_formula_id INTO l_dummy_id;
859 --
860 IF chk_formula_id%NOTFOUND THEN
861 --
862 hr_utility.set_location(l_proc, 40);
863 --
864 CLOSE chk_formula_id;
865 --
866 hr_utility.set_message(800, 'HR_289263_FORMULA_ID_INVALID');
867 hr_utility.raise_error;
868 --
869 ELSE
870 --
871 hr_utility.set_location(l_proc, 50);
872 --
873 CLOSE chk_formula_id;
874 --
875 END IF;
876 --
877 END IF;
878 --
879 hr_utility.set_location(l_proc, 60);
880 --
881 -- Check that if the formula_id IS populated then the
882 -- formula_criteria field IS set to 'FORMULA' and that
883 -- if the formula_id IS NULL then the formula_criteria
884 -- field IS set to 'CRITERIA'
885 --
886 chk_formula_criteria_mismatch
887 (p_formula_id => p_formula_id
888 ,p_formula_criteria => p_formula_criteria
889 );
890 --
891 END IF;
892 --
893 hr_utility.set_location('Leaving : '||l_proc, 999);
894 --
895 END chk_formula_id;
896 --
897 -- ---------------------------------------------------------------------------
898 -- |------------------------------< chk_start_date >-------------------------|
899 -- ---------------------------------------------------------------------------
900 --
901 -- Desciption :
902 --
903 -- Validate that that the start_date is not before the start_date of the
904 -- collective agreement and after the end_date for the entitlement record
905 --
906 --
907 -- Pre-conditions :
908 --
909 --
910 -- In Arguments :
911 -- p_cagr_entitlement_id
912 -- p_start_Date
913 -- p_end_Date
914 -- p_collective_agreement_id
915 --
916 -- Post Success :
917 -- Processing continues
918 --
919 -- Post Failure :
920 -- An application error will be raised and processing IS
921 -- terminated
922 --
923 -- Access Status :
924 -- Internal Table Handler Use only.
925 --
926 -- {END of Comments}
927 --
928 -- ---------------------------------------------------------------------------
929 --
930 PROCEDURE chk_start_date
931 (p_cagr_entitlement_id IN per_cagr_entitlements.cagr_entitlement_id%TYPE
932 ,p_start_date IN per_cagr_entitlements.start_date%TYPE
933 ,p_end_date IN per_cagr_entitlements.end_date%TYPE
934 ,p_collective_Agreement_id IN per_cagr_entitlements.collective_agreement_id%TYPE
935 ) IS
936 --
937 -- Define cursors
938 --
939 CURSOR chk_collective_agreement_date IS
940 SELECT pca.start_date
941 FROM per_collective_agreements pca
942 WHERE p_start_date < pca.start_date
943 AND pca.collective_Agreement_id = p_collective_agreement_id;
944 --
945 l_proc VARCHAR2(72) := g_package||'chk_start_date';
946 l_dummy_date per_cagr_entitlements.start_date%TYPE;
947 --
948 BEGIN
949 --
950 hr_utility.set_location('Entering : '||l_proc,10);
951 --
952 -- Check mandatory parameter IS set
953 --
954 hr_api.mandatory_arg_error
955 (p_api_name => l_proc
956 ,p_argument => 'start_date'
957 ,p_argument_value => p_start_date
958 );
959 --
960 hr_utility.set_location(l_proc,20);
961 --
962 -- Check that the start_date IS not before the
963 -- start_date for the collective agreements.
964 --
965 OPEN chk_collective_agreement_date;
966 FETCH chk_collective_agreement_date INTO l_dummy_date;
967 --
968 IF chk_collective_agreement_date%FOUND THEN
969 --
970 CLOSE chk_collective_agreement_date;
971 --
972 hr_utility.set_message(800, 'HR_289261_ST_DATE_BEFORE_CAGR');
973 hr_utility.raise_error;
974 --
975 ELSE
976 --
977 hr_utility.set_location(l_proc,40);
978 --
979 CLOSE chk_collective_agreement_date;
980 --
981 END IF;
982 --
983 -- Check that start_date IS not after the end_date
984 --
985 IF p_start_date > p_end_date THEN
986 --
987 hr_utility.set_message(800, 'HR_289262_ST_DATE_BEFORE_EDATE');
988 hr_utility.raise_error;
989 --
990 END IF;
991 --
992 hr_utility.set_location('Leaving : '||l_proc, 999);
993 --
994 END chk_start_date;
995 --
996 -- ---------------------------------------------------------------------------
997 -- |--------------------------------< chk_end_date >-------------------------|
998 -- ---------------------------------------------------------------------------
999 --
1000 -- Desciption :
1001 --
1002 -- Validate that that the end_date IS not after the end_date of the
1003 -- collective agreement and not before the start_date for the entitlement
1004 --
1005 --
1006 -- Pre-conditions :
1007 --
1008 --
1009 -- In Arguments :
1010 -- p_cagr_entitlement_id
1011 -- p_start_Date
1012 -- p_end_Date
1013 -- p_collective_agreement_id
1014 --
1015 -- Post Success :
1016 -- Processing continues
1017 --
1018 -- Post Failure :
1019 -- An application error will be raised and processing IS
1020 -- terminated
1021 --
1022 -- Access Status :
1023 -- Internal Table Handler Use only.
1024 --
1025 -- {END of Comments}
1026 --
1027 -- ---------------------------------------------------------------------------
1028 --
1029 PROCEDURE chk_end_date
1030 (p_cagr_entitlement_id IN per_cagr_entitlements.cagr_entitlement_id%TYPE
1031 ,p_start_date IN per_cagr_entitlements.start_date%TYPE
1032 ,p_end_date IN per_cagr_entitlements.end_date%TYPE
1033 ,p_effective_date IN DATE
1034 ) IS
1035 --
1036 -- Declare Cursors
1037 --
1038 CURSOR csr_check_line_end_dates IS
1039 SELECT 'X'
1040 FROM per_cagr_entitlement_lines_f pcl
1041 WHERE pcl.cagr_entitlement_id = p_cagr_entitlement_id
1042 AND DECODE(pcl.effective_end_date
1043 ,hr_general.end_of_time,hr_general.start_of_time
1044 ,pcl.effective_end_date) > NVL(p_end_date,hr_general.end_of_time);
1045 --
1046 l_proc VARCHAR2(72) := g_package||'chk_end_date';
1047 l_dummy VARCHAR2(1);
1048 --
1049 BEGIN
1050 --
1051 hr_utility.set_location('Entering : '||p_end_date||'/'||l_proc,10);
1052 --
1053 --
1054 -- Only proceed with validation if :
1055 -- a) Inserting or
1056 -- b) The value for formula id has changed
1057 --
1058 /*
1059 IF ( (p_cagr_entitlement_id IS NULL) OR
1060 ((p_cagr_entitlement_id IS NOT NULL) AND
1061 (per_pce_shd.g_old_rec.end_date <>
1062 NVL(p_end_date,hr_general.end_of_time)))) THEN*/
1063 --
1064 hr_utility.set_location(l_proc,20);
1065 --
1066 -- Check that start_date IS not after the end_date
1067 --
1068 IF NVL(p_end_date,hr_general.end_of_time) < p_start_date THEN
1069 --
1070 hr_utility.set_message(800, 'HR_289271_EDATE_AFTER_ST_DATE');
1071 hr_utility.raise_error;
1072 --
1073 END IF;
1074 --
1075 -- Check that the end_date is not before any end dates for
1076 -- any per_cagr_entitlement_lines_f
1077 --
1078 OPEN csr_check_line_end_dates;
1079 FETCH csr_check_line_end_dates INTO l_dummy;
1080 --
1081 IF csr_check_line_end_dates%FOUND THEN
1082 --
1083 CLOSE csr_check_line_end_dates;
1084 --
1085 hr_utility.set_message(800, 'HR_289393_INV_END_DATE');
1086 hr_utility.raise_error;
1087 --
1088 ELSE
1089 --
1090 CLOSE csr_check_line_end_dates;
1091 --
1092 END IF;
1093 --
1094 -- Check that the end_date is not being set
1095 -- to a date before the effective date
1096 --
1097 IF NVL(p_end_date,hr_general.end_of_time) < p_effective_date THEN
1098 --
1099 hr_utility.set_message(800, 'HR_289394_EDATE_BEFORE_EFF_DAT');
1100 hr_utility.raise_error;
1101 --
1102 END IF;
1103 --
1104 -- END IF;
1105 --
1106 hr_utility.set_location('Leaving : '||l_proc, 999);
1107 --
1108 END chk_end_date;
1109 --
1110 -- ---------------------------------------------------------------------------
1111 -- |-----------------------< chk_entitlement_uniqueness >--------------------|
1112 -- ---------------------------------------------------------------------------
1113 --
1114 -- Desciption :
1115 --
1116 -- Ensures that the entitlement item for the entitlement record has not
1117 -- already been defined for the collective agreement that the entitlement
1118 -- is linked to.
1119 --
1120 --
1121 -- Pre-conditions :
1122 --
1123 --
1124 -- In Arguments :
1125 -- p_cagr_entitlement_id
1126 -- p_cagr_entitlement_item_id
1127 -- p_collective_agreement_id
1128 --
1129 -- Post Success :
1130 -- Processing continues
1131 --
1132 -- Post Failure :
1133 -- An application error will be raised and processing IS
1134 -- terminated
1135 --
1136 -- Access Status :
1137 -- Internal Table Handler Use only.
1138 --
1139 -- {END of Comments}
1140 --
1141 -- ---------------------------------------------------------------------------
1142 --
1143 PROCEDURE chk_entitlement_uniqueness
1144 (p_cagr_entitlement_id IN per_cagr_entitlements.cagr_entitlement_id%TYPE
1145 ,p_cagr_entitlement_item_id IN per_cagr_entitlements.cagr_entitlement_item_id%TYPE
1146 ,p_collective_agreement_id IN per_cagr_entitlements.collective_agreement_id%TYPE
1147 ) IS
1148 --
1149 -- Declare Cursors
1150 --
1151 CURSOR csr_chk_uniqueness IS
1152 SELECT pce.cagr_entitlement_id
1153 FROM per_cagr_entitlements pce
1154 WHERE pce.collective_agreement_id = p_collective_agreement_id
1155 AND pce.cagr_entitlement_item_id = p_cagr_entitlement_item_id;
1156 --
1157 l_proc VARCHAR2(72) := g_package||'chk_end_date';
1158 l_dummy_id per_cagr_entitlements.cagr_entitlement_id%TYPE;
1159 --
1160 BEGIN
1161 --
1162 hr_utility.set_location('Entering : '||l_proc,10);
1163 --
1164 -- Check mandatory parameter IS set
1165 --
1166 hr_api.mandatory_arg_error
1167 (p_api_name => l_proc
1168 ,p_argument => 'collective_agreement_id'
1169 ,p_argument_value => p_collective_agreement_id
1170 );
1171 --
1172 hr_api.mandatory_arg_error
1173 (p_api_name => l_proc
1174 ,p_argument => 'cagr_entitlement_item_id'
1175 ,p_argument_value => p_cagr_entitlement_item_id
1176 );
1177 --
1178 hr_utility.set_location(l_proc,20);
1179 --
1180 -- Validate that the entitlement item has not already
1181 -- been defined within the collective agreement.
1182 --
1183 OPEN csr_chk_uniqueness;
1184 FETCH csr_chk_uniqueness INTO l_dummy_id;
1185 --
1186 IF csr_chk_uniqueness%FOUND THEN
1187 --
1188 CLOSE csr_chk_uniqueness;
1189 --
1190 hr_utility.set_message(800, 'HR_289272_ENTITLEMENT_NOT_UNIQ');
1191 hr_utility.raise_error;
1192 --
1193 ELSE
1194 --
1195 hr_utility.set_location(l_proc,30);
1196 --
1197 CLOSE csr_chk_uniqueness;
1198 --
1199 END IF;
1200 --
1201 hr_utility.set_location('Leaving : '||l_proc, 999);
1202 --
1203 END chk_entitlement_uniqueness;
1204 --
1205 -- ----------------------------------------------------------------------------
1206 -- |---------------------------< insert_validate >----------------------------|
1207 -- ----------------------------------------------------------------------------
1208 PROCEDURE insert_validate
1209 (p_effective_date IN DATE
1210 ,p_rec IN per_pce_shd.g_rec_type
1211 ) IS
1212 --
1213 l_proc VARCHAR2(72) := g_package||'insert_validate';
1214 l_business_group_id NUMBER;
1215 --
1216 BEGIN
1217 --
1218 hr_utility.set_location('Entering:'||l_proc, 5);
1219 --
1220 -- Call procedure that returns the business_group_id
1221 -- that will be used in the chk procedures
1222 --
1223 per_pce_shd.retrieve_cagr_info
1224 (p_collective_agreement_id => p_rec.collective_agreement_id
1225 ,p_business_group_id => l_business_group_id);
1226 --
1227 --
1228 -- Call all supporting business operations
1229 --
1230 /*per_pce_bus.set_security_group_id
1231 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1232 ,p_collective_agreement_id => p_rec.collective_agreement_id
1233 ); */
1234 --
1235 hr_utility.set_location(l_proc,20);
1236 --
1237 per_pce_bus.chk_start_date
1238 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1239 ,p_start_date => p_rec.start_date
1240 ,p_end_date => p_rec.end_date
1241 ,p_collective_Agreement_id => p_rec.collective_agreement_id
1242 );
1243 --
1244 hr_utility.set_location(l_proc,25);
1245 --
1246 per_pce_bus.chk_end_date
1247 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1248 ,p_start_date => p_rec.start_date
1249 ,p_end_date => p_rec.end_date
1250 ,p_effective_date => p_effective_date
1251 );
1252 --
1253 hr_utility.set_location(l_proc,30);
1254 --
1255 per_pce_bus.chk_formula_id
1256 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1257 ,p_effective_date => p_effective_date
1258 ,p_formula_id => p_rec.formula_id
1259 ,p_business_group_id => l_business_group_id
1260 ,p_formula_criteria => p_rec.formula_criteria
1261 );
1262 --
1263 hr_utility.set_location(l_proc,40);
1264 --
1265 per_pce_bus.chk_formula_criteria
1266 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1267 ,p_effective_date => p_effective_date
1268 ,p_formula_id => p_rec.formula_id
1269 ,p_formula_criteria => p_rec.formula_criteria
1270 );
1271 --
1272 hr_utility.set_location(l_proc,50);
1273 --
1274 per_pce_bus.chk_formula_criteria_mismatch
1275 (p_formula_id => p_rec.formula_id
1276 ,p_formula_criteria => p_rec.formula_criteria
1277 );
1278 --
1279 hr_utility.set_location(l_proc,60);
1280 --
1281 per_pce_bus.chk_status
1282 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1283 ,p_effective_date => p_effective_date
1284 ,p_status => p_rec.status
1285 );
1286 --
1287 hr_utility.set_location(l_proc,70);
1288 --
1289 per_pce_bus.chk_message_level
1290 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1291 ,p_effective_date => p_effective_date
1292 ,p_message_level => p_rec.message_level
1293 );
1294 --
1295 hr_utility.set_location(l_proc,80);
1296 --
1297 per_pce_bus.chk_cagr_entitlement_item_id
1298 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1299 ,p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id);
1300 --
1301 hr_utility.set_location(l_proc,90);
1302 --
1303 per_pce_bus. chk_entitlement_uniqueness
1304 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1305 ,p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
1306 ,p_collective_agreement_id => p_rec.collective_agreement_id
1307 );
1308 --
1309 hr_utility.set_location(l_proc,100);
1310 --
1311 per_pce_bus.chk_collective_agreement_id
1312 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1313 ,p_business_group_id => l_business_group_id
1314 ,p_collective_agreement_id => p_rec.collective_agreement_id);
1315 --
1316 hr_utility.set_location(' Leaving:'||l_proc, 900);
1317 --
1318 END insert_validate;
1319 --
1320 -- ----------------------------------------------------------------------------
1321 -- |---------------------------< update_validate >----------------------------|
1322 -- ----------------------------------------------------------------------------
1323 --
1324 PROCEDURE update_validate
1325 (p_effective_date IN DATE
1326 ,p_rec IN per_pce_shd.g_rec_type
1327 ) IS
1328 --
1329 l_proc VARCHAR2(72) := g_package||'update_validate';
1330 l_business_group_id NUMBER;
1331 --
1332 BEGIN
1333 --
1334 hr_utility.set_location('Entering:'||l_proc, 10);
1335 --
1336 -- Call all supporting business operations
1337 --
1338 per_pce_bus.set_security_group_id
1339 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1340 ,p_collective_agreement_id => p_rec.collective_agreement_id
1341 );
1342 --
1343 -- Call procedure that returns the business_group_id
1344 -- that will be used in the chk procedures
1345 --
1346 per_pce_shd.retrieve_cagr_info
1347 (p_collective_agreement_id => p_rec.collective_agreement_id
1348 ,p_business_group_id => l_business_group_id);
1349 --
1350 hr_utility.set_location(l_proc,20);
1351 --
1352 chk_non_updateable_args
1353 (p_effective_date => p_effective_date
1354 ,p_rec => p_rec
1355 );
1356 --
1357 hr_utility.set_location(l_proc,30);
1358 --
1359 per_pce_bus.chk_formula_id
1360 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1361 ,p_effective_date => p_effective_date
1362 ,p_business_group_id => l_business_group_id
1363 ,p_formula_id => p_rec.formula_id
1364 ,p_formula_criteria => p_Rec.formula_criteria
1365 );
1366 --
1367 hr_utility.set_location(l_proc,50);
1368 --
1369 per_pce_bus.chk_formula_criteria
1370 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1371 ,p_effective_date => p_effective_date
1372 ,p_formula_id => p_rec.formula_id
1373 ,p_formula_criteria => p_rec.formula_criteria
1374 );
1375 --
1376 hr_utility.set_location(l_proc,60);
1377 --
1378 per_pce_bus.chk_formula_criteria_mismatch
1379 (p_formula_id => p_rec.formula_id
1380 ,p_formula_criteria => p_rec.formula_criteria
1381 );
1382 --
1383 hr_utility.set_location(l_proc,70);
1384 --
1385 per_pce_bus.chk_status
1386 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1387 ,p_effective_date => p_effective_date
1388 ,p_status => p_rec.status
1389 );
1390 --
1391 hr_utility.set_location(l_proc,80);
1392 --
1393 per_pce_bus.chk_message_level
1394 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
1395 ,p_effective_date => p_effective_date
1396 ,p_message_level => p_rec.message_level
1397 );
1398 --
1399 hr_utility.set_location(' Leaving:'||l_proc, 999);
1400 --
1401 END update_validate;
1402 --
1403 -- ----------------------------------------------------------------------------
1404 -- |---------------------------< delete_validate >----------------------------|
1405 -- ----------------------------------------------------------------------------
1406 PROCEDURE delete_validate
1407 (p_rec IN per_pce_shd.g_rec_type
1408 ) IS
1409 --
1410 l_proc VARCHAR2(72) := g_package||'delete_validate';
1411 --
1412 BEGIN
1413 hr_utility.set_location('Entering:'||l_proc, 5);
1414 --
1415 -- Call all supporting business operations
1416 --
1417 hr_utility.set_location(' Leaving:'||l_proc, 10);
1418 --
1419 END delete_validate;
1420 --
1421 END per_pce_bus;