1 Package Body per_cei_bus as
2 /* $Header: peceirhi.pkb 120.1 2006/10/18 08:58:46 grreddy noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_cei_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_item_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_cagr_entitlement_item_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , per_cagr_entitlement_items cei
30 where cei.cagr_entitlement_item_id = p_cagr_entitlement_item_id
31 and pbg.business_group_id = cei.business_group_id;
32 --
33 -- Declare local variables
34 --
35 l_security_group_id number;
36 l_proc varchar2(72) := g_package||'set_security_group_id';
37 --
38 begin
39 --
40 hr_utility.set_location('Entering:'|| l_proc, 10);
41 --
42 -- Ensure that all the mandatory parameter are not null
43 --
44 hr_api.mandatory_arg_error
45 (p_api_name => l_proc
46 ,p_argument => 'cagr_entitlement_item_id'
47 ,p_argument_value => p_cagr_entitlement_item_id
48 );
49 --
50 open csr_sec_grp;
51 fetch csr_sec_grp into l_security_group_id;
52 --
53 if csr_sec_grp%notfound then
54 --
55 close csr_sec_grp;
56 --
57 -- The primary key is invalid therefore we must error
58 --
59 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
60 fnd_message.raise_error;
61 --
62 end if;
63 close csr_sec_grp;
64 --
65 -- Set the security_group_id in CLIENT_INFO
66 --
67 hr_api.set_security_group_id
68 (p_security_group_id => l_security_group_id
69 );
70 --
71 hr_utility.set_location(' Leaving:'|| l_proc, 20);
72 --
73 end set_security_group_id;
74 --
75 -- ---------------------------------------------------------------------------
76 -- |---------------------< return_legislation_code >-------------------------|
77 -- ---------------------------------------------------------------------------
78 --
79 Function return_legislation_code
80 (p_cagr_entitlement_item_id in number
81 )
82 Return Varchar2 Is
83 --
84 -- Declare cursor
85 --
86 cursor csr_leg_code is
87 select pbg.legislation_code
88 from per_business_groups pbg
89 , per_cagr_entitlement_items cei
90 where cei.cagr_entitlement_item_id = p_cagr_entitlement_item_id
91 and pbg.business_group_id = cei.business_group_id;
92 --
93 -- Declare local variables
94 --
95 l_legislation_code varchar2(150);
96 l_proc varchar2(72) := g_package||'return_legislation_code';
97 --
98 Begin
99 --
100 hr_utility.set_location('Entering:'|| l_proc, 10);
101 --
102 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_api.mandatory_arg_error
105 (p_api_name => l_proc
106 ,p_argument => 'cagr_entitlement_item_id'
107 ,p_argument_value => p_cagr_entitlement_item_id
108 );
109 --
110 if ( nvl(per_cei_bus.g_cagr_entitlement_item_id, hr_api.g_number)
111 = p_cagr_entitlement_item_id) then
112 --
113 -- The legislation code has already been found with a previous
114 -- call to this function. Just return the value in the global
115 -- variable.
116 --
117 l_legislation_code := per_cei_bus.g_legislation_code;
118 hr_utility.set_location(l_proc, 20);
119 else
120 --
121 -- The ID is different to the last call to this function
122 -- or this is the first call to this function.
123 --
124 open csr_leg_code;
125 fetch csr_leg_code into l_legislation_code;
126 --
127 if csr_leg_code%notfound then
128 --
129 -- The primary key is invalid therefore we must error
130 --
131 close csr_leg_code;
132 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
133 fnd_message.raise_error;
134 end if;
135 hr_utility.set_location(l_proc,30);
136 --
137 -- Set the global variables so the values are
138 -- available for the next call to this function.
139 --
140 close csr_leg_code;
141 per_cei_bus.g_cagr_entitlement_item_id := p_cagr_entitlement_item_id;
142 per_cei_bus.g_legislation_code := l_legislation_code;
143 end if;
144 hr_utility.set_location(' Leaving:'|| l_proc, 40);
145 return l_legislation_code;
146 end return_legislation_code;
147 --
148 -- ----------------------------------------------------------------------------
149 -- |-----------------------< chk_non_updateable_args >------------------------|
150 -- ----------------------------------------------------------------------------
151 -- {Start Of Comments}
152 --
153 -- Description:
154 -- This procedure is used to ensure that non updateable attributes have
155 -- not been updated. If an attribute has been updated an error is generated.
156 --
157 -- Pre Conditions:
158 -- g_old_rec has been populated with details of the values currently in
159 -- the database.
160 --
161 -- In Arguments:
162 -- p_rec has been populated with the updated values the user would like the
163 -- record set to.
164 --
165 -- Post Success:
166 -- Processing continues if all the non updateable attributes have not
167 -- changed.
168 --
169 -- Post Failure:
170 -- An application error is raised if any of the non updatable attributes
171 -- have been altered.
172 --
173 -- {End Of Comments}
174 -- ----------------------------------------------------------------------------
175 Procedure chk_non_updateable_args
176 (p_effective_date in date
177 ,p_rec in per_cei_shd.g_rec_type
178 ) IS
179 --
180 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
181 l_error EXCEPTION;
182 l_argument varchar2(30);
183 l_item_in_use BOOLEAN;
184 --
185 BEGIN
186 --
187 hr_utility.set_location('Entering:'||l_proc, 5);
188 --
189 --
190 -- Only proceed with the validation if a row exists for the current
191 -- record in the HR Schema.
192 --
193 IF NOT per_cei_shd.api_updating
194 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
195 ) THEN
196 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
197 fnd_message.set_token('PROCEDURE ', l_proc);
198 fnd_message.set_token('STEP ', '5');
199 fnd_message.raise_error;
200 END IF;
201 --
202 -- If the item has been seeded by development then
203 -- raise an error as updates are not allowed of
204 -- seeded items.
205 --
206 IF ((p_rec.business_group_id IS NULL) AND
207 (p_rec.legislation_code IS NULL)) OR
208 ((p_rec.business_group_id IS NULL) AND
209 (p_rec.legislation_code IS NOT NULL)) THEN
210 --
211 hr_utility.set_message(800, 'HR_289362_UPD_INV_FOR_SEEDED_I');
212 hr_utility.raise_error;
213 --
214 END IF;
215 --
216 hr_utility.set_location(l_proc, 10);
217 --
218 IF nvl(p_rec.business_group_id, hr_api.g_number) <>
219 nvl(per_cei_shd.g_old_rec.business_group_id,hr_api.g_number) THEN
220 --
221 l_argument := 'business_group_id';
222 RAISE l_error;
223 --
224 END IF;
225 --
226 hr_utility.set_location(l_proc, 20);
227 --
228 IF nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
229 nvl(per_cei_shd.g_old_rec.legislation_code,hr_api.g_varchar2) THEN
230 --
231 l_argument := 'legislation_code';
232 RAISE l_error;
233 --
234 END IF;
235 --
236 hr_utility.set_location(l_proc, 30);
237 --
238 --
239 -- Check to see if the entitlement item has
240 -- been used by any collective agreements
241 --
242 l_item_in_use := per_cei_shd.entitlement_item_in_use
243 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id);
244 --
245 hr_utility.set_location(l_proc, 40);
246 --
247 -- If the Entitlement ITem has been used by Collective Agreement
248 -- then the following fields are now not updatable and so
249 -- should an error should be raised.
250 --
251 IF l_item_in_use THEN
252 --
253 IF nvl(p_rec.mult_entries_allowed_flag , hr_api.g_varchar2) <>
254 nvl(per_cei_shd.g_old_rec.mult_entries_allowed_flag,hr_api.g_varchar2) THEN
255 --
256 l_argument := 'mult_entries_allowed_flag';
257 RAISE l_error;
258 --
259 END IF;
260 --
261 hr_utility.set_location(l_proc||'/'||p_rec.column_type||'/', 50);
262 --
263 IF nvl(p_rec.column_type, hr_api.g_varchar2) <>
264 nvl(per_cei_shd.g_old_rec.column_type,hr_api.g_varchar2) THEN
265 --
266 l_argument := 'column_type';
267 RAISE l_error;
268 --
269 END IF;
270 --
271 hr_utility.set_location(l_proc, 60);
272 --
273 IF nvl(p_rec.flex_value_set_id, hr_api.g_number) <>
274 nvl(per_cei_shd.g_old_rec.flex_value_set_id,hr_api.g_number) THEN
275 --
276 l_argument := 'flex_value_set_id';
277 RAISE l_error;
278 --
279 END IF;
280 --
281 hr_utility.set_location(l_proc, 70);
282 --
283 IF nvl(p_rec.category_name, hr_api.g_varchar2) <>
284 nvl(per_cei_shd.g_old_rec.category_name,hr_api.g_varchar2) THEN
285 --
286 l_argument := 'category_name';
287 RAISE l_error;
288 --
289 END IF;
290 --
291 hr_utility.set_location(l_proc, 80);
292 --
293 IF nvl(p_rec.input_value_id, hr_api.g_number) <>
294 nvl(per_cei_shd.g_old_rec.input_value_id,hr_api.g_number) THEN
295 --
296 l_argument := 'input_value_id';
297 RAISE l_error;
298 --
299 END IF;
300 --
301 hr_utility.set_location(l_proc, 90);
302 --
303 IF nvl(p_rec.element_type_id, hr_api.g_number) <>
304 nvl(per_cei_shd.g_old_rec.element_type_id,hr_api.g_number) THEN
305 --
306 l_argument := 'element_type_id';
307 RAISE l_error;
308 --
309 END IF;
310 --
311 IF nvl(p_rec.cagr_api_id, hr_api.g_number) <>
312 nvl(per_cei_shd.g_old_rec.cagr_api_id,hr_api.g_number) THEN
313 --
314 l_argument := 'cagr_api_id';
315 RAISE l_error;
316 --
317 END IF;
318 --
319 IF nvl(p_rec.cagr_api_param_id, hr_api.g_number) <>
320 nvl(per_cei_shd.g_old_rec.cagr_api_param_id,hr_api.g_number) THEN
321 --
322 l_argument := 'cagr_api_param_id';
323 RAISE l_error;
324 --
325 END IF;
326 --
327 END IF;
328 --
329 hr_utility.set_location('Leaving :'||l_proc, 999);
330 --
331 EXCEPTION
332 WHEN l_error THEN
333 hr_api.argument_changed_error
334 (p_api_name => l_proc
335 ,p_argument => l_argument);
336 WHEN OTHERS THEN
337 RAISE;
338 End chk_non_updateable_args;
339 --
340 -- ---------------------------------------------------------------------------
341 -- |------------------------------< chk_item_name >--------------------------|
342 -- ---------------------------------------------------------------------------
343 --
344 -- Desciption :
345 --
346 -- Validate that that the entitlement item name is unique within the
347 -- category for business group, legislation and global.
348 --
349 --
350 -- Pre-conditions :
351 --
352 --
353 -- In Arguments :
354 -- p_cagr_entitlement_item_id
355 -- p_item_name
356 -- p_category_name
357 --
358 -- Post Success :
359 -- Processing continues
360 --
361 -- Post Failure :
362 -- An application error will be raised and processing is
363 -- terminated
364 --
365 -- Access Status :
366 -- Internal Table Handler Use only.
367 --
368 -- {End of Comments}
369 --
370 -- ---------------------------------------------------------------------------
371 --
372 PROCEDURE chk_item_name
373 (p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
374 ,p_item_name IN per_cagr_entitlement_items.item_name%TYPE
375 ,p_category_name IN per_cagr_entitlement_items.category_name%TYPE
376 ,p_legislation_code IN per_cagr_entitlement_items.legislation_code%TYPE
377 ,p_business_group_id IN per_cagr_entitlement_items.business_group_id%TYPE) IS
378 --
379 -- Declare Cursors
380 --
381 CURSOR chk_for_global_item IS
382 SELECT cei.item_name
383 FROM per_cagr_entitlement_items cei
384 WHERE cei.item_name = p_item_name
385 AND cei.category_name = p_category_name
386 AND cei.business_group_id IS NULL
387 AND cei.legislation_code IS NULL;
388 --
389 CURSOR chk_for_legislation_item IS
390 SELECT cei.item_name
391 FROM per_cagr_entitlement_items cei
392 WHERE cei.item_name = p_item_name
393 AND cei.category_name = p_category_name
394 AND cei.business_group_id IS NULL
395 AND cei.legislation_code = p_legislation_code;
396 --
397 CURSOR chk_for_customer_item IS
398 SELECT cei.item_name
399 FROM per_cagr_entitlement_items cei
400 WHERE cei.item_name = p_item_name
401 AND cei.category_name = p_category_name
402 AND cei.business_group_id = p_business_group_id
403 AND cei.legislation_code = p_legislation_code;
404 --
405 l_proc VARCHAR2(72) := g_package||'chk_item_name';
406 l_item_name per_cagr_entitlement_items.item_name%TYPE;
407 --
408 BEGIN
409 --
410 hr_utility.set_location('Entering:'||l_proc, 10);
411 --
412 IF p_item_name IS NULL THEN
413 --
414 hr_utility.set_message(800,'HR_289220_ITEM_NAME_NULL');
415 hr_utility.raise_error;
416 --
417 END IF;
418 --
419 -- Only proceed with validation if :
420 -- a) Inserting or
421 -- b) The value for item name has changed
422 --
423 IF ( (p_cagr_entitlement_item_id IS NULL) OR
424 ((p_cagr_entitlement_item_id IS NOT NULL) AND
425 (per_cei_shd.g_old_rec.item_name <> p_item_name))) THEN
426 --
427 hr_utility.set_location(l_proc,20);
428 --
429 -- Check to see if a Global Item has already been defined
430 --
431 OPEN chk_for_global_item;
432 FETCH chk_for_global_item INTO l_item_name;
433 --
434 IF chk_for_global_item%FOUND THEN
435 --
436 CLOSE chk_for_global_item;
437 --
438 hr_utility.set_message(800,'HR_289219_ITEM_NAME_INVALID');
439 hr_utility.raise_error;
440 --
441 END IF;
442 --
443 CLOSE chk_for_global_item;
444 --
445 -- Check to see if a Localisation Item has already been defined
446 --
447 OPEN chk_for_legislation_item;
448 FETCH chk_for_legislation_item INTO l_item_name;
449 --
450 IF chk_for_legislation_item%FOUND THEN
451 --
452 CLOSE chk_for_legislation_item;
453 --
454 hr_utility.set_message(800,'HR_289219_ITEM_NAME_INVALID');
455 hr_utility.raise_error;
456 --
457 END IF;
458 --
459 CLOSE chk_for_legislation_item;
460 --
461 -- Check to see if a Customer Item has already been defined
462 --
463 OPEN chk_for_customer_item;
464 FETCH chk_for_customer_item INTO l_item_name;
465 --
466 IF chk_for_customer_item%FOUND THEN
467 --
468 CLOSE chk_for_customer_item;
469 --
470 hr_utility.set_message(800,'HR_289219_ITEM_NAME_INVALID');
471 hr_utility.raise_error;
472 --
473 END IF;
474 --
475 CLOSE chk_for_customer_item;
476 --
477 END IF;
478 hr_utility.set_location(' Leaving:'||l_proc, 100);
479 --
480 END chk_item_name;
481 --
482 -- ---------------------------------------------------------------------------
483 -- |--------------------------< chk_legilsation_code >-----------------------|
484 -- ---------------------------------------------------------------------------
485 --
486 -- Desciption :
487 --
488 -- Validate that that the legislation code exists on the database.
489 --
490 -- Pre-conditions :
491 --
492 --
493 -- In Arguments :
494 -- p_legislation_code
495 --
496 -- Post Success :
497 -- Processing continues
498 --
499 -- Post Failure :
500 -- An application error will be raised and processing is
501 -- terminated
502 --
503 -- Access Status :
504 -- Internal Table Handler Use only.
505 --
506 -- {End of Comments}
507 --
508 -- ---------------------------------------------------------------------------
509 --
510 PROCEDURE chk_legislation_code
511 (p_legislation_code IN per_cagr_entitlement_items.legislation_code%TYPE) IS
512 --
513 -- Local declarations
514 --
515 l_proc VARCHAR2(72) := g_package||'chk_legislation_code';
516 l_territory_code fnd_territories.territory_code%TYPE;
517 --
518 -- Setup cursor for valid legislation code check
519 --
520 CURSOR csr_valid_legislation_code is
521 SELECT territory_code
522 FROM fnd_territories ft
523 WHERE ft.territory_code = p_legislation_code;
524 --
525 BEGIN
526 --
527 hr_utility.set_location('Entering: '||l_proc,10);
528 --
529 -- Only continue if the legislation code has been populated
530 --
531 IF p_legislation_code IS NOT NULL THEN
532 --
533 hr_utility.set_location(l_proc,20);
534 --
535 -- Validate legislation code
536 --
537 OPEN csr_valid_legislation_code;
538 FETCH csr_valid_legislation_code INTO l_territory_code;
539 --
540 IF csr_valid_legislation_code%notfound THEN
541 --
542 hr_utility.set_location(l_proc,30);
543 --
544 CLOSE csr_valid_legislation_code;
545 --
546 hr_utility.set_message(800,'PER_52123_AMD_LEG_CODE_INV');
547 hr_utility.raise_error;
548 --
549 ELSE
550 --
551 hr_utility.set_location(l_proc,40);
552 --
553 CLOSE csr_valid_legislation_code;
554 --
555 END IF;
556 --
557 END IF;
558 --
559 hr_utility.set_location('Leaving: '||l_proc,50);
560 --
561 END chk_legislation_code;
562 --
563 -- ---------------------------------------------------------------------------
564 -- |--------------------------< chk_beneficial_rule >-------------------------|
565 -- ---------------------------------------------------------------------------
566 --
567 -- Desciption :
568 --
569 -- Validate that that the beneficial rule exists in HR_LOOKUPS for the
570 -- lookup type "CAGR_BENEFICIAL_RULE"
571 --
572 -- Pre-conditions :
573 --
574 --
575 -- In Arguments :
576 -- p_effective_date
577 -- p_beneficial_rule
578 --
579 -- Post Success :
580 -- Processing continues
581 --
582 -- Post Failure :
583 -- An application error will be raised and processing is
584 -- terminated
585 --
586 -- Access Status :
587 -- Internal Table Handler Use only.
588 --
589 -- {End of Comments}
590 --
591 -- ---------------------------------------------------------------------------
592 --
593 PROCEDURE chk_beneficial_rule
594 (p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
595 ,p_beneficial_rule IN per_cagr_entitlement_items.beneficial_rule%TYPE
596 ,p_ben_rule_value_set_id IN NUMBER
597 ,p_effective_date IN DATE) IS
598 --
599 -- Local declarations
600 --
601 l_proc VARCHAR2(72) := g_package||'chk_beneficial_rule';
602 --
603 BEGIN
604 --
605 hr_utility.set_location('Entering: '||l_proc,10);
606 --
607 -- Only proceed with validation if :
608 -- a) Inserting or
609 -- b) The value for beneficial rule has changed
610 --
611 IF p_beneficial_rule IS NOT NULL OR
612 NVL(per_cei_shd.g_old_rec.beneficial_rule,hr_api.g_varchar2) <>
613 NVL(p_beneficial_rule, hr_api.g_varchar2) THEN
614 --
615 hr_utility.set_location(l_proc, 20);
616 --
617 -- Check that if a beneficial value set has been entered
618 -- that the beneficial rule has also been entered.
619 --
620 IF p_ben_rule_value_set_id IS NOT NULL AND
621 p_beneficial_rule IS NULL THEN
622 --
623 hr_utility.set_message(800, 'HR_289583_BEN_RULE_NULL');
624 hr_utility.raise_error;
625 --
626 END IF;
627 --
628 -- Check that the category exists in HR_LOOKUPS
629 --
630 IF hr_api.not_exists_in_hr_lookups
631 (p_effective_date => p_effective_date
632 ,p_lookup_type => 'CAGR_BENEFICIAL_RULE'
633 ,p_lookup_code => p_beneficial_rule) THEN
634 --
635 hr_utility.set_location(l_proc, 30);
636 --
637 hr_utility.set_message(800, 'HR_289221_BENEFICIAL_RULE_INV');
638 hr_utility.raise_error;
639 --
640 END IF;
641 --
642 END IF;
643 --
644 hr_utility.set_location('Leaving: '||l_proc,100);
645 --
646 END chk_beneficial_rule;
647 --
648 -- ---------------------------------------------------------------------------
649 -- |----------------------------< chk_column_type >--------------------------|
650 -- ---------------------------------------------------------------------------
651 --
652 -- Desciption :
653 --
654 -- If the entitlement item has been linked to a api parameter then ensure
655 -- that the column_type is the same as the column type for the api
656 -- parameter. Otherwise ensure that the column type exists HR_LOOKUPS
657 -- for the lookup_type of 'CAGR_PARAM_TYPES'
658 --
659 -- Pre-conditions :
660 --
661 --
662 -- In Arguments :
663 -- p_effective_date
664 -- p_entitlement_item_id
665 -- p_column_type
666 -- p_api_param_id
667 --
668 -- Post Success :
669 -- Processing continues
670 --
671 -- Post Failure :
672 -- An application error will be raised and processing is
673 -- terminated
674 --
675 -- Access Status :
676 -- Internal Table Handler Use only.
677 --
678 -- {End of Comments}
679 --
680 -- ---------------------------------------------------------------------------
681 --
682 PROCEDURE chk_column_type
683 (p_cagr_entitlement_item_id IN NUMBER
684 ,p_column_type IN OUT NOCOPY per_cagr_entitlement_items.column_type%TYPE
685 ,p_uom IN per_cagr_entitlement_items.uom%TYPE
686 ,p_cagr_api_param_id IN per_cagr_entitlement_items.cagr_api_param_id%TYPE
687 ,p_input_value_id IN per_cagr_entitlement_items.input_value_id%TYPE
688 ,p_category_name IN per_cagr_entitlement_items.category_name%TYPE
689 ,p_effective_date IN DATE) IS
690 --
691 -- Declare Cursors
692 --
693 CURSOR csr_chk_api_param_type IS
694 SELECT column_type
695 FROM per_cagr_api_parameters p
696 WHERE p.cagr_api_param_id = p_cagr_api_param_id
697 AND p.column_type = p_column_type;
698 --
699 -- Local declarations
700 --
701 l_proc VARCHAR2(72) := g_package||'chk_column_type';
702 l_api_column_type per_cagr_api_parameters.column_type%TYPE;
703 --
704 BEGIN
705 --
706 hr_utility.set_location('Entering: '||l_proc,10);
707 --
708 -- Check mandatory parameter is set
709 --
710 hr_api.mandatory_arg_error
711 (p_api_name => l_proc
712 ,p_argument => 'effective_date'
713 ,p_argument_value => p_effective_date
714 );
715 --
716 -- Only proceed with validation if :
717 -- a) Inserting or
718 -- b) The value for column_type has changed
719 --
720 IF ( (p_cagr_entitlement_item_id IS NULL) OR
721 ((p_cagr_entitlement_item_id IS NOT NULL) AND
722 (per_cei_shd.g_old_rec.column_type <> p_column_type))) THEN
723 --
724 hr_utility.set_location(l_proc, 20);
725 --
726 -- IF the item is a payroll item or a non-denormalised item
727 ---then derive the column typefrom the UOM by calling
728 -- the convert_uom_to_data_type procedure.
729 --
730 IF (p_category_name = 'PAY') OR
731 (p_input_value_id IS NULL AND p_cagr_api_param_id IS NULL) THEN
732 --
733 hr_utility.set_location(l_proc, 30);
734 --
735 p_column_type := per_cagr_utility_pkg.convert_uom_to_data_type
736 (p_uom => p_uom);
737 --
738 ELSE
739 --
740 hr_utility.set_location(l_proc, 40);
741 --
742 -- If the entitlement item has been linked to to a
743 -- api parameter then check to see if the column type matches
744 -- the column type passed into this procedure
745 --
746 IF p_cagr_api_param_id IS NOT NULL THEN
747 --
748 hr_utility.set_location(l_proc, 50);
749 --
750 OPEN csr_chk_api_param_type;
751 FETCH csr_chk_api_param_type INTO l_api_column_type;
752 --
753 -- IF no records have been found then the column types
754 -- do not match so raise an error
755 --
756 IF csr_chk_api_param_type%NOTFOUND THEN
757 --
758 CLOSE csr_chk_api_param_type;
759 --
760 hr_utility.set_message(800, 'HR_289336_COL_TYPE_MISMATCH');
761 hr_utility.raise_error;
762 --
763 END IF;
764 --
765 CLOSE csr_chk_api_param_type;
766 --
767 END IF;
768 --
769 END IF;
770 --
771 hr_utility.set_location(l_proc, 60);
772 --
773 -- Check that the category exists in HR_LOOKUPS
774 --
775 IF hr_api.not_exists_in_hr_lookups
776 (p_effective_date => p_effective_date
777 ,p_lookup_type => 'CAGR_PARAM_TYPES'
778 ,p_lookup_code => p_column_type) THEN
779 --
780 hr_utility.set_location(l_proc, 30);
781 --
782 hr_utility.set_message(800, 'HR_289205_COLUMN_TYPE_INVALID');
783 hr_utility.raise_error;
784 --
785 END IF;
786 --
787 END IF;
788 --
789 hr_utility.set_location('Leaving: '||l_proc,100);
790 --
791 END chk_column_type;
792 --
793 -- ---------------------------------------------------------------------------
794 -- |----------------------------< chk_column_size >--------------------------|
795 -- ---------------------------------------------------------------------------
796 --
797 -- Desciption :
798 --
799 -- If the entitlement item has been linked to a api parameter then ensure
800 -- that the column_size is the same as the column size for the api
801 -- parameter.
802 --
803 -- Pre-conditions :
804 --
805 --
806 -- In Arguments :
807 -- p_entitlement_item_id
808 -- p_column_size
809 -- p_api_param_id
810 --
811 -- Post Success :
812 -- Processing continues
813 --
814 -- Post Failure :
815 -- An application error will be raised and processing is
816 -- terminated
817 --
818 -- Access Status :
819 -- Internal Table Handler Use only.
820 --
821 -- {End of Comments}
822 --
823 -- ---------------------------------------------------------------------------
824 --
825 PROCEDURE chk_column_size
826 (p_cagr_entitlement_item_id IN NUMBER
827 ,p_column_size IN NUMBER
828 ,p_cagr_api_param_id IN NUMBER) IS
829 --
830 -- Declare Cursors
831 --
832 CURSOR csr_chk_api_param_size IS
833 SELECT column_size
834 FROM per_cagr_api_parameters p
835 WHERE p.cagr_api_param_id = p_cagr_api_param_id
836 AND p.column_size = p_column_size;
837 --
838 -- Local declarations
839 --
840 l_proc VARCHAR2(72) := g_package||'chk_column_size';
841 l_api_column_size NUMBER;
842 --
843 BEGIN
844 --
845 hr_utility.set_location('Entering: '||l_proc,10);
846 --
847 -- Check mandatory parameter is set
848 --
849 hr_api.mandatory_arg_error
850 (p_api_name => l_proc
851 ,p_argument => 'column_size'
852 ,p_argument_value => p_column_size
853 );
854 --
855 -- Only proceed with validation if :
856 -- a) Inserting or
857 -- b) The value for column_size has changed
858 --
859 IF ( (p_cagr_entitlement_item_id IS NULL) OR
860 ((p_cagr_entitlement_item_id IS NOT NULL) AND
861 (per_cei_shd.g_old_rec.column_size <> p_column_size))) THEN
862 --
863 hr_utility.set_location(l_proc, 20);
864 --
865 -- If the entitlement item has been linked to to a
866 -- api parameter then check to see if the column size matches
867 -- the column size passed into this procedure
868 --
869 IF p_cagr_api_param_id IS NOT NULL THEN
870 --
871 OPEN csr_chk_api_param_size;
872 FETCH csr_chk_api_param_size INTO l_api_column_size;
873 --
874 -- IF no records have been found then the column types
875 -- do not match so raise an error
876 --
877 IF csr_chk_api_param_size%NOTFOUND THEN
878 --
879 CLOSE csr_chk_api_param_size;
880 --
881 hr_utility.set_message(800, 'HR_289337_COL_SIZE_MISMATCH');
882 hr_utility.raise_error;
883 --
884 END IF;
885 --
886 CLOSE csr_chk_api_param_size;
887 --
888 END IF;
889 --
890 END IF;
891 --
892 hr_utility.set_location('Leaving: '||l_proc,100);
893 --
894 END chk_column_size;
895 --
896 -- ---------------------------------------------------------------------------
897 -- |--------------------------< chk_cagr_api_id >-------------------------|
898 -- ---------------------------------------------------------------------------
899 --
900 -- Desciption :
901 --
902 -- Validate that that the cagr_api_id exists in PER_CAGR_APIS, and that the
903 -- cagr_api_param_id has also been populated. Also ensure that the
904 -- element_type_id is blank.
905 --
906 -- Pre-conditions :
907 --
908 --
909 -- In Arguments :
910 -- p_cagr_api_id
911 -- p_cagr_api_param_id
912 -- p_cagr_entitlement_item_id
913 -- p_element_type_id
914 --
915 -- Post Success :
916 -- Processing continues
917 --
918 -- Post Failure :
919 -- An application error will be raised and processing is
920 -- terminated
921 --
922 -- Access Status :
923 -- Internal Table Handler Use only.
924 --
925 -- {End of Comments}
926 --
927 -- ---------------------------------------------------------------------------
928 --
929 PROCEDURE chk_cagr_api_id
930 (p_cagr_api_id IN per_cagr_entitlement_items.cagr_api_id%TYPE
931 ,p_cagr_api_param_id IN per_cagr_entitlement_items.cagr_api_param_id%TYPE
932 ,p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
933 ,p_element_type_id IN per_cagr_entitlement_items.element_type_id%TYPE) IS
934 --
935 -- Local declarations
936 --
937 l_proc VARCHAR2(72) := g_package||'chk_cagr_api_id';
938 l_cagr_api_id per_cagr_entitlement_items.cagr_api_id%TYPE;
939 --
940 CURSOR csr_cagr_api_id IS
941 SELECT cagr_api_id
942 FROM per_cagr_apis pca
943 WHERE pca.cagr_api_id = p_cagr_api_id;
944 --
945 BEGIN
946 --
947 hr_utility.set_location('Entering: '||l_proc,10);
948 --
949 -- Only proceed with validation if :
950 -- a) Inserting or
951 -- b) The value for cagr api id has changed
952 --
953 IF ( (p_cagr_entitlement_item_id IS NULL) OR
954 ((p_cagr_entitlement_item_id IS NOT NULL) AND
955 (NVL(per_cei_shd.g_old_rec.cagr_api_id, hr_api.g_number) <>
956 NVL(p_cagr_api_id,hr_api.g_number)))) THEN
957 --
958 hr_utility.set_location(l_proc,20);
959 --
960 IF p_cagr_api_id IS NOT NULL THEN
961 --
962 -- Check that the ELEMENT_TYPE_ID has not been populated
963 -- as well as the CAGR_API_ID.
964 --
965 IF p_element_type_id IS NOT NULL THEN
966 --
967 hr_utility.set_message(800, 'HR_289231_CAGR_ELEMENT_COMB_IN');
968 hr_utility.raise_error;
969 --
970 END IF;
971 --
972 -- Check that the CAGR API PARAM ID is populated as well.
973 --
974 IF p_cagr_api_param_id IS NULL THEN
975 --
976 hr_utility.set_message(800, 'HR_289389_API_OR_PARAM_NULL');
977 hr_utility.raise_error;
978 --
979 END IF;
980 --
981 hr_utility.set_location(l_proc,30);
982 --
983 -- Check that the cagr_api_id exists in PER_CAGR_APIS
984 --
985 OPEN csr_cagr_api_id;
986 FETCH csr_cagr_api_id INTO l_cagr_api_id;
987 --
988 IF csr_cagr_api_id%NOTFOUND THEN
989 --
990 CLOSE csr_cagr_api_id;
991 --
992 hr_utility.set_message(800, 'HR_289230_CAGR_API_ID_INVALID');
993 hr_utility.raise_error;
994 --
995 ELSE
996 --
997 hr_utility.set_location(l_proc,40);
998 --
999 CLOSE csr_cagr_api_id;
1000 --
1001 END IF;
1002 --
1003 END IF;
1004 --
1005 END IF;
1006 --
1007 hr_utility.set_location('Leaving: '||l_proc,100);
1008 --
1009 END chk_cagr_api_id;
1010 --
1011 -- ---------------------------------------------------------------------------
1012 -- |------------------------< chk_cagr_api_param_id >------------------------|
1013 -- ---------------------------------------------------------------------------
1014 --
1015 -- Desciption :
1016 --
1017 -- Validate that that the cagr_api_param_id exists in PER_CAGR_API_PARAMS
1018 -- and that the APi Param ID has been populated as well. Also ensure that
1019 -- the Input_Value_ID has not been populated.
1020 --
1021 -- Pre-conditions :
1022 --
1023 --
1024 -- In Arguments :
1025 -- p_cagr_api_param_id
1026 -- p_input_value_id
1027 -- p_cagr_entitlement_item_id
1028 -- p_cagr_api_id
1029 --
1030 -- Post Success :
1031 -- Processing continues
1032 --
1033 -- Post Failure :
1034 -- An application error will be raised and processing is
1035 -- terminated
1036 --
1037 -- Access Status :
1038 -- Internal Table Handler Use only.
1039 --
1040 -- {End of Comments}
1041 --
1042 -- ---------------------------------------------------------------------------
1043 --
1044 PROCEDURE chk_cagr_api_param_id
1045 (p_cagr_api_param_id IN per_cagr_entitlement_items.cagr_api_param_id%TYPE
1046 ,p_input_value_id IN per_cagr_entitlement_items.input_value_id%TYPE
1047 ,p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
1048 ,p_cagr_api_id IN per_cagr_entitlement_items.cagr_api_id%TYPE) IS
1049 --
1050 -- Local declarations
1051 --
1052 l_proc VARCHAR2(72) := g_package||'chk_cagr_api_param_id';
1053 l_cagr_api_param_id per_cagr_entitlement_items.cagr_api_param_id%TYPE;
1054 --
1055 CURSOR csr_cagr_api_param_id IS
1056 SELECT cagr_api_param_id
1057 FROM per_cagr_api_parameters cap
1058 WHERE cap.cagr_api_param_id = p_cagr_api_param_id
1059 AND cap.cagr_api_id = p_cagr_api_id;
1060 --
1061 BEGIN
1062 --
1063 hr_utility.set_location('Entering: '||l_proc,10);
1064 --
1065 hr_utility.set_location(l_proc,20);
1066 --
1067 -- Only proceed with validation if :
1068 -- a) Inserting or
1069 -- b) The value for cagr api param id has changed
1070 --
1071 IF ( (p_cagr_entitlement_item_id IS NULL) OR
1072 ((p_cagr_entitlement_item_id IS NOT NULL) AND
1073 (per_cei_shd.g_old_rec.cagr_api_param_id <> p_cagr_api_param_id))) THEN
1074 --
1075 hr_utility.set_location(l_proc,30);
1076 --
1077 IF p_cagr_api_param_id IS NOT NULL THEN
1078 --
1079 hr_utility.set_location(l_proc,40);
1080 --
1081 -- Check that the INPUT_VALUE has not been populated
1082 -- as well as the CAGR_API_PARAM_ID.
1083 --
1084 IF p_input_value_id IS NOT NULL THEN
1085 --
1086 hr_utility.set_message(800, 'HR_289233_PARAM_INP_VAL_COMB');
1087 hr_utility.raise_error;
1088 --
1089 END IF;
1090 --
1091 -- Check that the CAGR API ID is populated as well.
1092 --
1093 IF p_cagr_api_id IS NULL THEN
1094 --
1095 hr_utility.set_message(800, 'HR_289389_API_OR_PARAM_NULL');
1096 hr_utility.raise_error;
1097 --
1098 END IF;
1099 --
1100 hr_utility.set_location(l_proc,50);
1101 --
1102 -- Check that the cagr_api_param_id exists in PER_CAGR_API_PARAMS
1103 --
1104 OPEN csr_cagr_api_param_id;
1105 FETCH csr_cagr_api_param_id INTO l_cagr_api_param_id;
1106 --
1107 IF csr_cagr_api_param_id%NOTFOUND THEN
1108 --
1109 CLOSE csr_cagr_api_param_id;
1110 --
1111 hr_utility.set_message(800, 'HR_289232_CAGR_API_PARAM_ID_IN');
1112 hr_utility.raise_error;
1113 --
1114 ELSE
1115 --
1116 hr_utility.set_location(l_proc,60);
1117 --
1118 CLOSE csr_cagr_api_param_id;
1119 --
1120 END IF;
1121 --
1122 END IF;
1123 --
1124 END IF;
1125 --
1126 hr_utility.set_location('Leaving: '||l_proc,999);
1127 --
1128 END chk_cagr_api_param_id;
1129 --
1130 -- ---------------------------------------------------------------------------
1131 -- |---------------------------< chk_category_name >-------------------------|
1132 -- ---------------------------------------------------------------------------
1133 --
1134 -- Desciption :
1135 --
1136 -- Validate that that the category_name exists in HR_LOOKUPS.
1137 --
1138 -- Pre-conditions :
1139 --
1140 --
1141 -- In Arguments :
1142 -- p_effective_date
1143 -- p_category_name
1144 --
1145 -- Post Success :
1146 -- Processing continues
1147 --
1148 -- Post Failure :
1149 -- An application error will be raised and processing is
1150 -- terminated
1151 --
1152 -- Access Status :
1153 -- Internal Table Handler Use only.
1154 --
1155 -- {End of Comments}
1156 --
1157 -- ---------------------------------------------------------------------------
1158 --
1159 PROCEDURE chk_category_name
1160 (p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
1161 ,p_category_name IN per_cagr_entitlement_items.category_name%TYPE
1162 ,p_effective_date IN DATE) IS
1163 --
1164 -- Local declarations
1165 --
1166 l_proc VARCHAR2(72) := g_package||'chk_category_name';
1167 --
1168 BEGIN
1169 --
1170 hr_utility.set_location('Entering: '||l_proc,10);
1171 --
1172 -- Check mandatory parameter is set
1173 --
1174 hr_api.mandatory_arg_error
1175 (p_api_name => l_proc
1176 ,p_argument => 'category_name'
1177 ,p_argument_value => p_category_name);
1178 --
1179 -- Only proceed with validation if :
1180 -- a) Inserting or
1181 -- b) The value for category_name has changed
1182 --
1183 IF ( (p_cagr_entitlement_item_id IS NULL) OR
1184 ((p_cagr_entitlement_item_id IS NOT NULL) AND
1185 (per_cei_shd.g_old_rec.category_name <> p_category_name))) THEN
1186 --
1187 hr_utility.set_location(l_proc, 20);
1188 --
1189 -- Check that the category exists in HR_LOOKUPS
1190 --
1191 IF hr_api.not_exists_in_hr_lookups
1192 (p_effective_date => p_effective_date
1193 ,p_lookup_type => 'CAGR_CATEGORIES'
1194 ,p_lookup_code => p_category_name) THEN
1195 --
1196 hr_utility.set_location(l_proc, 30);
1197 --
1198 hr_utility.set_message(800, 'HR_289212_CATEGORY_INVALID');
1199 hr_utility.raise_error;
1200 --
1201 END IF;
1202 --
1203 END IF;
1204 --
1205 hr_utility.set_location('Leaving: '||l_proc,100);
1206 --
1207 END chk_category_name;
1208 --
1209 -- ---------------------------------------------------------------------------
1210 -- |--------------------------------< chk_uom >------------------------------|
1211 -- ---------------------------------------------------------------------------
1212 --
1213 -- Desciption :
1214 --
1215 -- Validate that that the uom exists in either the UNITS lookup type or
1216 -- the uom defined for the api_parameter in the uom_lookup column. The UOM
1217 -- is also mandatory if the the api_parameter has a uom_lookup seeded.
1218 --
1219 -- Pre-conditions :
1220 --
1221 --
1222 -- In Arguments :
1223 -- p_effective_date
1224 -- p_uom
1225 --
1226 -- Post Success :
1227 -- Processing continues
1228 --
1229 -- Post Failure :
1230 -- An application error will be raised and processing is
1231 -- terminated
1232 --
1233 -- Access Status :
1234 -- Internal Table Handler Use only.
1235 --
1236 -- {End of Comments}
1237 --
1238 -- ---------------------------------------------------------------------------
1239 --
1240 PROCEDURE chk_uom
1241 (p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
1242 ,p_uom IN OUT NOCOPY per_cagr_entitlement_items.uom%TYPE
1243 ,p_input_value_id IN per_cagr_entitlement_items.input_value_id%TYPE
1244 ,p_cagr_api_param_id IN per_cagr_entitlement_items.cagr_api_param_id%TYPE
1245 ,p_effective_date IN DATE) IS
1246 --
1247 CURSOR csr_get_param_uom IS
1248 SELECT p.uom_lookup,
1249 p.default_uom
1250 FROM per_cagr_api_parameters p
1251 WHERE p.cagr_api_param_id = p_cagr_api_param_id;
1252 --
1253 CURSOR csr_get_pay_default_uom IS
1254 SELECT piv.uom
1255 FROM pay_input_values_f piv
1256 WHERE piv.input_value_id = p_input_value_id
1257 AND p_effective_date BETWEEN piv.effective_start_date
1258 AND piv.effective_end_date;
1259 --
1260 -- Local declarations
1261 --
1262 l_proc VARCHAR2(72) := g_package||'chk_uom';
1263 l_uom_lookup per_cagr_api_parameters.uom_lookup%TYPE;
1264 l_default_uom per_cagr_api_parameters.default_uom%TYPE;
1265 l_uom pay_input_values_f.uom%TYPE;
1266 --
1267 BEGIN
1268 --
1269 hr_utility.set_location('Entering: '||l_proc,10);
1270 --
1271 -- Only proceed with validation if :
1272 -- a) Inserting or
1273 -- b) The value for uom has changed
1274 --
1275 IF ( (p_cagr_entitlement_item_id IS NULL) OR
1276 ((p_cagr_entitlement_item_id IS NOT NULL) AND
1277 (per_cei_shd.g_old_rec.uom <> NVL(p_uom,hr_api.g_varchar2)))) THEN
1278 --
1279 hr_utility.set_location(l_proc, 20);
1280 --
1281 -- If the item has been defined as a denormalised payroll item
1282 -- then derive the correct UOM from the input value definition.
1283 -- If a UOM has been specified then overright it with the
1284 -- correct uom.
1285 --
1286 IF p_input_value_id IS NOT NULL THEN
1287 --
1288 hr_utility.set_location(l_proc, 30);
1289 --
1290 OPEN csr_get_pay_default_uom;
1291 FETCH csr_get_pay_default_uom INTO p_uom;
1292 --
1293 IF csr_get_pay_default_uom%NOTFOUND THEN
1294 --
1295 CLOSE csr_get_pay_default_uom;
1296 --
1297 hr_utility.set_message(800, 'HR_289253_INPUT_VALUE_INV');
1298 hr_utility.raise_error;
1299 --
1300 END IF;
1301 --
1302 CLOSE csr_get_pay_default_uom;
1303 --
1304 -- If the item has been defined as a non-denormalised item
1305 -- then check that the UOM exists in the UNITS lookup.
1306 --
1307 ELSIF p_input_value_id IS NULL AND
1308 p_cagr_api_param_id IS NULL THEN
1309 --
1310 hr_utility.set_location(l_proc, 40);
1311 --
1312 IF hr_api.not_exists_in_hr_lookups
1313 (p_effective_date => p_effective_date
1314 ,p_lookup_type => NVL(l_uom_lookup,'UNITS')
1315 ,p_lookup_code => p_uom) THEN
1316 --
1317 hr_utility.set_message(800, 'HR_289242_UOM_INVALID');
1318 hr_utility.raise_error;
1319 --
1320 END IF;
1321 --
1322 -- If the item has been defined as a denormalised item
1323 -- then check to see that the UOM has not been set for
1324 -- items that have not been linked to parameters defined
1325 -- with a lookup type. If the parameter does have a
1326 -- lookup defined check that the UOM is present and
1327 -- exists for that lookup.
1328 --
1329 ELSIF p_cagr_api_param_id IS NOT NULL THEN
1330 --
1331 hr_utility.set_location(l_proc, 50);
1332 --
1333 OPEN csr_get_param_uom;
1334 FETCH csr_get_param_uom INTO l_uom_lookup, l_default_uom;
1335 --
1336 -- If the parameter does not exists then an raise error.
1337 --
1338 IF csr_get_param_uom%NOTFOUND THEN
1339 --
1340 CLOSE csr_get_param_uom;
1341 --
1342 hr_utility.set_message(800, 'HR_289232_CAGR_API_PARAM_ID_IN');
1343 hr_utility.raise_error;
1344 --
1345 END IF;
1346 --
1347 CLOSE csr_get_param_uom;
1348 --
1349 hr_utility.set_location(l_proc||'/'||l_uom_lookup||'/'||p_uom, 60);
1350 --
1351 -- If the api parameter has been seeded with a uom_lookup
1352 -- and the uom has no value against it then raise an
1353 -- error as the uom should have a value in this case.
1354 --
1355 IF l_uom_lookup IS NOT NULL AND
1356 p_uom IS NULL THEN
1357 --
1358 hr_utility.set_message(800, 'HR_289335_UOM_MANDATORY');
1359 hr_utility.raise_error;
1360 --
1361 -- If the lookup is null and the uom has been passed into
1362 -- this procedure then raise an error, as the UOM cannot be
1363 -- set if the parameter lookup has also not been set.
1364 --
1365 ELSIF p_uom IS NOT NULL AND
1366 l_uom_lookup IS NULL AND
1367 l_default_UOM IS NULL THEN
1368 --
1369 hr_utility.set_message(800, 'HR_289401_UOM_NOT_NULL');
1370 hr_utility.raise_error;
1371 --
1372 -- If both the UOM and Lookup have been popoulated then
1373 -- check to see that the UOM exists in the Lookup.
1374 --
1375 ELSIF p_uom IS NOT NULL AND
1376 l_uom_lookup IS NOT NULL THEN
1377 --
1378 hr_utility.set_location(l_proc, 70);
1379 --
1380 IF hr_api.not_exists_in_hr_lookups
1381 (p_effective_date => p_effective_date
1382 ,p_lookup_type => l_uom_lookup
1383 ,p_lookup_code => p_uom) THEN
1384 --
1385 hr_utility.set_message(800, 'HR_289242_UOM_INVALID');
1386 hr_utility.raise_error;
1387 --
1388 END IF;
1389 --
1390 -- If the item has got a default_uom and the uom passed
1391 -- in does not match this default uom then raise an error.
1392 --
1393 ELSIF l_default_uom IS NOT NULL AND
1394 p_uom <> l_default_uom THEN
1395 --
1396 hr_utility.set_message(800, 'HR_289401_UOM_NOT_NULL');
1397 hr_utility.raise_error;
1398 --
1399 -- If the item has got a default_uom and the UOM is blank
1400 -- then copy the value of the default_uom to the uom.
1401 --
1402 ELSIF l_default_uom IS NOT NULL AND
1403 p_uom IS NULL THEN
1404 --
1405 p_uom := l_default_uom;
1406 --
1407 END IF;
1408 --
1409 END IF;
1410 --
1411 END IF;
1412 --
1413 hr_utility.set_location('Leaving: '||l_proc,999);
1414 --
1415 END chk_uom;
1416 --
1417 -- ---------------------------------------------------------------------------
1418 -- |-----------------------------< chk_item_use >-----------------------------|
1419 -- ---------------------------------------------------------------------------
1420 --
1421 -- Desciption :
1422 --
1423 -- Validate that on delete that the item is not being referenced
1424 -- by a cagr.
1425 --
1426 --
1427 -- Pre-conditions :
1428 --
1429 --
1430 -- In Arguments :
1431 -- p_entitlement_item_id
1432 --
1433 -- Post Success :
1434 -- Processing continues
1435 --
1436 -- Post Failure :
1437 -- An application error will be raised and processing is
1438 -- terminated
1439 --
1440 -- Access Status :
1441 -- Internal Table Handler Use only.
1442 --
1443 -- {End of Comments}
1444 --
1445 -- ---------------------------------------------------------------------------
1446 --
1447 PROCEDURE chk_item_use
1448 (p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE) IS
1449 --
1450 l_proc varchar2(72) := g_package || 'chk_item_use';
1451 l_item_in_use BOOLEAN;
1452 --
1453 BEGIN
1454 --
1455 hr_utility.set_location('Entering : '||l_proc,10);
1456 --
1457 -- Check mandatory parameter is set
1458 --
1459 hr_api.mandatory_arg_error
1460 (p_api_name => l_proc
1461 ,p_argument => 'cagr_entitlement_item_id'
1462 ,p_argument_value => p_cagr_entitlement_item_id
1463 );
1464 --
1465 l_item_in_use := per_cei_shd.entitlement_item_in_use
1466 (p_cagr_entitlement_item_id => p_cagr_entitlement_item_id);
1467 --
1468 IF l_item_in_use THEN
1469 --
1470 hr_utility.set_location(l_proc,20);
1471 --
1472 hr_utility.set_message(800, 'HR_289234_ENT_ITEM_IN_USE');
1473 hr_utility.raise_error;
1474 --
1475 END IF;
1476 --
1477 hr_utility.set_location('Leaving : '||l_proc,100);
1478 --
1479 END chk_item_use;
1480 --
1481 -- ---------------------------------------------------------------------------
1482 -- |-----------------------< chk_multiple_entries_flag >---------------------|
1483 -- ---------------------------------------------------------------------------
1484 --
1485 -- Desciption :
1486 --
1487 -- Validate that the correct combination of element being allowed for
1488 -- multiple entries and the entitlement item having the correct value set for
1489 -- the multiple entries allowed flag. Correct combinations are:
1490 --
1491 -- Element Allowed Entitlment Items
1492 -- Multiple Entries Multiple Entries Flag Valid
1493 -- ================ ===================== =====
1494 --
1495 -- N Y NO
1496 -- N N NO
1497 -- N NULL YES
1498 --
1499 -- Y Y YES
1500 -- Y N YES
1501 -- Y NULL NO
1502 --
1503 -- Pre-conditions :
1504 --
1505 --
1506 -- In Arguments :
1507 -- p_mult_entries_allowed_flag
1508 -- p_cagr_entitlement_item_id
1509 -- p_category_name
1510 -- p_element_type_id
1511 -- p_business_group_id
1512 -- p_legislation_code
1513 -- p_effective_date
1514 --
1515 -- Post Success :
1516 -- Processing continues
1517 --
1518 -- Post Failure :
1519 -- An application error will be raised and processing is
1520 -- terminated
1521 --
1522 -- Access Status :
1523 -- Internal Table Handler Use only.
1524 --
1525 -- {End of Comments}
1526 --
1527 -- ---------------------------------------------------------------------------
1528 --
1529 PROCEDURE chk_multiple_entries_flag
1530 (p_mult_entries_allowed_flag IN per_cagr_entitlement_items.multiple_entries_allowed_flag%TYPE
1531 ,p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
1532 ,p_category_name IN per_cagr_entitlement_items.category_name%TYPE
1533 ,p_element_type_id IN per_cagr_entitlement_items.element_type_id%TYPE
1534 ,p_business_group_id IN per_cagr_entitlement_items.business_group_id%TYPE
1535 ,p_legislation_code IN per_cagr_entitlement_items.legislation_code%TYPE
1536 ,p_effective_date IN DATE) IS
1537 --
1538 CURSOR csr_get_pay_element IS
1539 SELECT multiple_entries_allowed_flag
1540 FROM pay_element_types_f p --pay_element_types_x p
1541 WHERE ((p.business_group_id = p_business_group_id) OR
1542 (p.business_group_id IS NULL))
1543 AND ((p.legislation_code = p_legislation_code) OR
1544 (p.legislation_code IS NULL))
1545 AND p.element_type_id = p_element_type_id
1546 AND p_effective_date between p.effective_start_date and p.effective_end_date;
1547 --
1548 l_proc VARCHAR2(72) := g_package || 'chk_multiple_entries_flag';
1549 l_multi_flag VARCHAR2(15);
1550 --
1551 BEGIN
1552 --
1553 hr_utility.set_location('Entering : '||l_proc,5);
1554 --
1555 -- Check mandatory parameter is set
1556 --
1557 hr_api.mandatory_arg_error
1558 (p_api_name => l_proc
1559 ,p_argument => 'effective_date'
1560 ,p_argument_value => p_effective_date);
1561 --
1562 -- Only proceed with validation if :
1563 -- a) Inserting or
1564 -- b) The value for item name has changed
1565 --
1566 IF ( (p_cagr_entitlement_item_id IS NULL) OR
1567 ((p_cagr_entitlement_item_id IS NOT NULL) AND
1568 (per_cei_shd.g_old_rec.mult_entries_allowed_flag <>
1569 NVL(p_mult_entries_allowed_flag,hr_api.g_varchar2)))) THEN
1570 --
1571 hr_utility.set_location(l_proc,20);
1572 --
1573 -- Check that the category is correct
1574 --
1575 IF p_category_name <> 'PAY' AND
1576 P_element_type_id IS NOT NULL THEN
1577 --
1578 hr_utility.set_message(800, 'HR_289390_MULTI_CAT_COMB_INV');
1579 hr_utility.raise_error;
1580 --
1581 END IF;
1582 --
1583 -- If the element type id is populated then
1584 -- fetch the multi_entries_allowed_flag
1585 --
1586 IF p_element_type_id IS NOT NULL THEN
1587 --
1588 hr_utility.set_location(l_proc,30);
1589 --
1590 OPEN csr_get_pay_element;
1591 FETCH csr_get_pay_element INTO l_multi_flag;
1592 --
1593 -- Fetch the multiple_entries_allowed_flag for the element.
1594 -- If it does not exist then raise an appropiate error.
1595 --
1596 IF csr_get_pay_element%NOTFOUND THEN
1597 --
1598 CLOSE csr_get_pay_element;
1599 --
1600 hr_utility.set_message(800, 'HR_289252_ELEMENT_TYPE_INV');
1601 hr_utility.raise_error;
1602 --
1603 ELSE
1604 --
1605 CLOSE csr_get_pay_element;
1606 --
1607 END IF;
1608 --
1609 -- If the element type id is not populated then
1610 -- set the multi_entries_allowed_flag to N
1611 --
1612 ELSE
1613 --
1614 hr_utility.set_location(l_proc,40);
1615 --
1616 l_multi_flag := 'N';
1617 --
1618 END IF;
1619 --
1620 hr_utility.set_location(l_proc||'/'||l_multi_flag,45);
1621 --
1622 -- If the Element is allowed multiple entries and the multi
1623 -- entries allowed flag for the entitlement item is not either
1624 -- Y or N then raise an error.
1625 --
1626 IF l_multi_flag = 'Y' AND
1627 (p_mult_entries_allowed_flag NOT IN ('Y','N') OR
1628 p_mult_entries_allowed_flag IS NULL) THEN
1629 --
1630 hr_utility.set_message(800, 'HR_289278_INV_MULT_ENTRY_FLAG');
1631 hr_utility.raise_error;
1632 --
1633 ELSIF l_multi_flag = 'N' AND
1634 p_mult_entries_allowed_flag IS NOT NULL THEN
1635 --
1636 hr_utility.set_message(800, 'HR_289387_MULTI_FLAG_NOT_NULL');
1637 hr_utility.raise_error;
1638 --
1639 END IF;
1640 --
1641 END IF;
1642 --
1643 hr_utility.set_location('Leaving : '||l_proc,999);
1644 --
1645 END chk_multiple_entries_flag;
1646 --
1647 -- ---------------------------------------------------------------------------
1648 -- |---------------------< chk_auto_create_entries_flag >--------------------|
1649 -- ---------------------------------------------------------------------------
1650 --
1651 -- Desciption :
1652 --
1653 -- Validate that a value (Y/N) is supplied for category payroll i.e. when the
1654 -- category name is 'PAY'.
1655 --
1656 -- (Added for CEI Enhancement)
1657 --
1658 --
1659 -- Pre-conditions :
1660 --
1661 --
1662 -- In Arguments :
1663 -- p_auto_create_entries_flag
1664 -- p_category_name
1665 -- p_effective_date
1666 --
1667 -- Post Success :
1668 -- Processing continues
1669 --
1670 -- Post Failure :
1671 -- An application error will be raised and processing is
1672 -- terminated
1673 --
1674 -- Access Status :
1675 -- Internal Table Handler Use only.
1676 --
1677 -- {End of Comments}
1678 --
1679 -- ---------------------------------------------------------------------------
1680 --
1681 PROCEDURE chk_auto_create_entries_flag
1682 (p_auto_create_entries_flag IN OUT NOCOPY per_cagr_entitlement_items.auto_create_entries_flag%TYPE
1683 ,p_category_name IN per_cagr_entitlement_items.category_name%TYPE
1684 ,p_effective_date IN DATE) IS
1685 --
1686 l_proc VARCHAR2(72) := g_package || 'chk_auto_create_entries_flag';
1687 --
1688 BEGIN
1689 --
1690 hr_utility.set_location('Entering : '||l_proc,10);
1691 --
1692 -- Check conditionally mandatory parameter is set else default.
1693 --
1694 IF p_category_name = 'PAY' THEN
1695 --
1696 IF p_auto_create_entries_flag IS NULL THEN
1697 --
1698 hr_utility.set_location(l_proc,20);
1699 --
1700 p_auto_create_entries_flag := 'N';
1701 --
1702 END IF;
1703 --
1704 ELSE -- category is not payroll
1705 --
1706 hr_utility.set_location(l_proc,30);
1707 --
1708 p_auto_create_entries_flag := NULL;
1709 --
1710 END IF;
1711 --
1712 -- Check value is valid, if supplied
1713 --
1714 IF p_auto_create_entries_flag IS NOT NULL THEN
1715 --
1716 hr_utility.set_location(l_proc,40);
1717 --
1718 IF (hr_api.not_exists_in_hr_lookups
1719 (p_effective_date => p_effective_date
1720 ,p_lookup_type => 'YES_NO'
1721 ,p_lookup_code => p_auto_create_entries_flag
1722 )
1723 ) THEN
1724 hr_utility.set_location('Entering : '||l_proc,40);
1725 -- p_auto_create_entries_flag does not exist in lookup, thus error.
1726 hr_utility.set_message(800,'HR_289472_HIDDEN_VALUE_INVALID');
1727 hr_utility.raise_error;
1728 END IF;
1729 --
1730 END IF;
1731 --
1732 hr_utility.set_location('Leaving : '||l_proc,999);
1733 --
1734 END chk_auto_create_entries_flag;
1735 --
1736 -- ---------------------------------------------------------------------------
1737 -- |---------------------------< chk_item_is_unique >-------------------------|
1738 -- ---------------------------------------------------------------------------
1739 --
1740 -- Desciption :
1741 --
1742 -- Validate that on insert and update that the item is unique within the
1743 -- business_group, legilsation and category.
1744 --
1745 --
1746 -- Pre-conditions :
1747 --
1748 --
1749 -- In Arguments :
1750 -- p_entitlement_item_id
1751 --
1752 -- Post Success :
1753 -- Processing continues
1754 --
1755 -- Post Failure :
1756 -- An application error will be raised and processing is
1757 -- terminated
1758 --
1759 -- Access Status :
1760 -- Internal Table Handler Use only.
1761 --
1762 -- {End of Comments}
1763 --
1764 -- ---------------------------------------------------------------------------
1765 --
1766 PROCEDURE chk_item_is_unique
1767 (p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
1768 ,p_category_name IN per_cagr_entitlement_items.category_name%TYPE
1769 ,p_cagr_api_id IN per_cagr_entitlement_items.cagr_api_id%TYPE
1770 ,p_cagr_api_param_id IN per_cagr_entitlement_items.cagr_api_param_id%TYPE
1771 ,p_element_type_id IN per_cagr_entitlement_items.element_type_id%TYPE
1772 ,p_input_value_id IN per_cagr_entitlement_items.input_value_id%TYPE
1773 ,p_legislation_code IN VARCHAR2
1774 ,p_business_group_id IN NUMBER
1775 ) IS
1776 --
1777 l_proc varchar2(72) := g_package || 'chk_item_is unique';
1778 l_dummy per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE;
1779 --
1780 -- Define a cursor that will check that an item as not already
1781 -- been defined for the same API and API Param. The cursor checks
1782 -- at the GLOBAL (NULL BG and LEG), LOCALISATION (NULL BG AND
1783 -- LEG equal to the Legislation Code) and CUSTOMER (BG and LEG
1784 -- both populated).
1785 --
1786 CURSOR csr_chk_api_item IS
1787 SELECT pce.cagr_entitlement_item_id
1788 FROM per_cagr_entitlement_items pce
1789 WHERE pce.category_name = 'ASG'
1790 AND ((p_cagr_entitlement_item_id IS NULL) OR
1791 (pce.cagr_entitlement_item_id <> p_cagr_entitlement_item_id))
1792 AND ((pce.business_group_id IS NULL AND
1793 pce.legislation_code IS NULL) OR
1794 (pce.business_group_id IS NULL AND
1795 pce.legislation_code = p_legislation_code) OR
1796 (pce.business_group_id = p_business_group_id AND
1797 pce.legislation_code = p_legislation_code))
1798 AND pce.cagr_api_id = p_cagr_api_id
1799 AND pce.cagr_api_param_id = p_cagr_api_param_id;
1800 --
1801 CURSOR csr_chk_pay_item IS
1802 SELECT pce.cagr_entitlement_item_id
1803 FROM per_cagr_entitlement_items pce
1804 WHERE ((p_cagr_entitlement_item_id IS NULL) OR
1805 (pce.cagr_entitlement_item_id <> p_cagr_entitlement_item_id))
1806 AND ((pce.business_group_id IS NULL AND
1807 pce.legislation_code IS NULL) OR
1808 (pce.business_group_id IS NULL AND
1809 pce.legislation_code = p_legislation_code) OR
1810 (pce.business_group_id = p_business_group_id AND
1811 pce.legislation_code = p_legislation_code))
1812 AND pce.element_type_id = p_element_type_id
1813 AND pce.input_value_id = p_input_value_id;
1814 --
1815 BEGIN
1816 --
1817 hr_utility.set_location('Entering : '||l_proc,10);
1818 --
1819 -- Check that an item has not been entered
1820 -- for the same api, and parameter name
1821 --
1822 IF p_cagr_api_param_id IS NOT NULL THEN
1823 --
1824 hr_utility.set_location(l_proc,15);
1825 --
1826 -- If the item is being inserted or the api or parameter
1827 -- have been changed (thus updating) then check to see if
1828 -- if the entitlement item is unique.
1829 --
1830 IF ( (p_cagr_entitlement_item_id IS NULL) OR
1831 ((p_cagr_entitlement_item_id IS NOT NULL) AND
1832 ((per_cei_shd.g_old_rec.cagr_api_id <> p_cagr_api_id) OR
1833 (per_cei_shd.g_old_rec.cagr_api_param_id <> p_cagr_api_param_id)
1834 )
1835 )
1836 ) THEN
1837 --
1838 hr_utility.set_location(l_proc,20);
1839 --
1840 OPEN csr_chk_api_item;
1841 FETCH csr_chk_api_item INTO l_dummy;
1842 --
1843 IF csr_chk_api_item%FOUND THEN
1844 --
1845 hr_utility.set_location(l_proc,30);
1846 --
1847 CLOSE csr_chk_api_item;
1848 --
1849 hr_utility.set_message(800, 'HR_289251_ITEM_NOT_UNIQUE');
1850 hr_utility.raise_error;
1851 --
1852 ELSE
1853 --
1854 hr_utility.set_location(l_proc,40);
1855 --
1856 CLOSE csr_chk_api_item;
1857 --
1858 END IF;
1859 --
1860 END IF;
1861 --
1862 -- Check that an item has not been entered with
1863 -- the same element and input values
1864 --
1865 ELSIF p_element_type_id IS NOT NULL THEN
1866 --
1867 hr_utility.set_location(l_proc,50);
1868 --
1869 IF ( (p_cagr_entitlement_item_id IS NULL) OR
1870 ((p_cagr_entitlement_item_id IS NOT NULL) AND
1871 ((per_cei_shd.g_old_rec.element_type_id <> p_element_type_id) OR
1872 (per_cei_shd.g_old_rec.input_value_id <> p_input_value_id)
1873 )
1874 )
1875 ) THEN
1876 --
1877 hr_utility.set_location(l_proc,55);
1878 --
1879 OPEN csr_chk_pay_item;
1880 FETCH csr_chk_pay_item INTO l_dummy;
1881 --
1882 IF csr_chk_pay_item%FOUND THEN
1883 --
1884 hr_utility.set_location(l_proc,60);
1885 --
1886 CLOSE csr_chk_pay_item;
1887 --
1888 hr_utility.set_message(800, 'HR_289251_ITEM_NOT_UNIQUE');
1889 hr_utility.raise_error;
1890 --
1891 ELSE
1892 --
1893 hr_utility.set_location(l_proc,70);
1894 --
1895 CLOSE csr_chk_pay_item;
1896 --
1897 END IF;
1898 --
1899 END IF;
1900 --
1901 END IF;
1902 --
1903 hr_utility.set_location('Leaving : '||l_proc,100);
1904 --
1905 END chk_item_is_unique;
1906 --
1907 -- ---------------------------------------------------------------------------
1908 -- |---------------------------< chk_element_type_id>------------------------|
1909 -- ---------------------------------------------------------------------------
1910 --
1911 -- Desciption :
1912 --
1913 -- Validate that on insert and update that the element type id is valid,
1914 -- that the input_value_id is also populated and the cagr_api_id is blank.
1915 --
1916 -- Pre-conditions :
1917 --
1918 --
1919 -- In Arguments :
1920 -- p_entitlement_item_id
1921 -- p_cagr_api_id
1922 -- p_element_type_id
1923 -- p_input_value_id
1924 -- p_effective_date
1925 -- p_legislation_code
1926 -- p_business_group_id
1927 --
1928 -- Post Success :
1929 -- Processing continues
1930 --
1931 -- Post Failure :
1932 -- An application error will be raised and processing is
1933 -- terminated
1934 --
1935 -- Access Status :
1936 -- Internal Table Handler Use only.
1937 --
1938 -- {End of Comments}
1939 --
1940 -- ---------------------------------------------------------------------------
1941 --
1942 PROCEDURE chk_element_type_id
1943 (p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
1944 ,p_cagr_api_id IN per_cagr_entitlement_items.cagr_api_id%TYPE
1945 ,p_element_type_id IN per_cagr_entitlement_items.element_type_id%TYPE
1946 ,p_input_value_id IN per_cagr_entitlement_items.input_value_id%TYPE
1947 ,p_effective_date IN DATE
1948 ,p_legislation_code IN pay_element_types_f.legislation_code%TYPE
1949 ,p_business_group_id IN pay_element_types_f.business_group_id%TYPE
1950 ) IS
1951 --
1952 l_proc varchar2(72) := g_package || 'chk_element_type_id';
1953 l_dummy pay_element_types_f.element_type_id%TYPE;
1954 --
1955 CURSOR csr_chk_element IS
1956 SELECT pat.element_type_id
1957 FROM pay_element_types_f pat
1958 WHERE pat.element_type_id = p_element_type_id
1959 AND ( ( pat.business_group_id IS NULL AND
1960 pat.legislation_code IS NULL) OR
1961 ( pat.business_group_id IS NULL AND
1962 pat.legislation_code = p_legislation_code) OR
1963 ( pat.business_group_id = p_business_group_id AND
1964 pat.legislation_code = p_legislation_code) OR
1965 ( pat.business_group_id = p_business_group_id AND
1966 pat.legislation_code IS NULL)
1967 )
1968 AND p_effective_date BETWEEN pat.effective_start_date
1969 AND pat.effective_end_date;
1970 --
1971 BEGIN
1972 --
1973 hr_utility.set_location('Entering : '||l_proc,10);
1974 --
1975 -- Check mandatory parameter is set
1976 --
1977 hr_api.mandatory_arg_error
1978 (p_api_name => l_proc
1979 ,p_argument => 'effective_date'
1980 ,p_argument_value => p_effective_date
1981 );
1982 --
1983 hr_utility.set_location(l_proc,20);
1984 --
1985 -- Only proceed with validation if :
1986 -- a) Inserting or
1987 -- b) The value for element_type_id has changed
1988 --
1989 IF ( (p_cagr_entitlement_item_id IS NULL) OR
1990 ((p_cagr_entitlement_item_id IS NOT NULL) AND
1991 (per_cei_shd.g_old_rec.element_type_id <> p_element_type_id))) THEN
1992 --
1993 hr_utility.set_location(l_proc,30);
1994 --
1995 IF p_element_type_id IS NOT NULL THEN
1996 --
1997 hr_utility.set_location(l_proc,40);
1998 --
1999 -- Check that the ELEMENT_TYPE_ID has not been populated
2000 -- as well as the CAGR_API_ID.
2001 --
2002 IF p_cagr_api_id IS NOT NULL THEN
2003 --
2004 hr_utility.set_message(800, 'HR_289231_CAGR_ELEMENT_COMB_IN');
2005 hr_utility.raise_error;
2006 --
2007 END IF;
2008 --
2009 -- Check that a INPUT value is also populated.
2010 --
2011 IF p_input_value_id IS NULL THEN
2012 --
2013 hr_utility.set_message(800, 'HR_289388_EE_OR_IV_NULL');
2014 hr_utility.raise_error;
2015 --
2016 END IF;
2017 --
2018 OPEN csr_chk_element;
2019 FETCH csr_chk_element INTO l_dummy;
2020 --
2021 IF csr_chk_element%NOTFOUND THEN
2022 --
2023 hr_utility.set_location(l_proc,50);
2024 --
2025 CLOSE csr_chk_element;
2026 --
2027 hr_utility.set_message(800, 'HR_289252_ELEMENT_TYPE_INV');
2028 hr_utility.raise_error;
2029 --
2030 ELSE
2031 --
2032 hr_utility.set_location(l_proc,60);
2033 --
2034 CLOSE csr_chk_element;
2035 --
2036 END IF;
2037 --
2038 ELSIF p_element_type_id IS NULL THEN
2039 --
2040 hr_utility.set_location(l_proc,70);
2041 --
2042 -- If the input_value is not null then raise an
2043 -- error as the element type must be populated as well.
2044 --
2045 IF p_input_value_id IS NOT NULL THEN
2046 --
2047 hr_utility.set_message(800, 'HR_289388_EE_OR_IV_NULL');
2048 hr_utility.raise_error;
2049 --
2050 END IF;
2051 --
2052 END IF;
2053 --
2054 END IF;
2055 --
2056 hr_utility.set_location('Leaving : '||l_proc,999);
2057 --
2058 END chk_element_type_id;
2059 --
2060 -- ---------------------------------------------------------------------------
2061 -- |---------------------------< chk_input_value_id>-------------------------|
2062 -- ---------------------------------------------------------------------------
2063 --
2064 -- Desciption :
2065 --
2066 -- Validate that on insert and update that the input value id is valid,
2067 -- the element_type_id contains a value and the car_api_param_id is blank.
2068 --
2069 --
2070 -- Pre-conditions :
2071 --
2072 --
2073 -- In Arguments :
2074 -- p_entitlement_item_id
2075 -- p_input_value_id
2076 -- p_element_type_id
2077 -- p_cagr_api_param_id
2078 -- p_effective_date
2079 --
2080 -- Post Success :
2081 -- Processing continues
2082 --
2083 -- Post Failure :
2084 -- An application error will be raised and processing is
2085 -- terminated
2086 --
2087 -- Access Status :
2088 -- Internal Table Handler Use only.
2089 --
2090 -- {End of Comments}
2091 --
2092 -- ---------------------------------------------------------------------------
2093 --
2094 PROCEDURE chk_input_value_id
2095 (p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
2096 ,p_element_type_id IN per_cagr_entitlement_items.element_type_id%TYPE
2097 ,p_input_value_id IN per_cagr_entitlement_items.input_value_id%TYPE
2098 ,p_cagr_api_param_id IN per_cagr_entitlement_items.cagr_api_param_id%TYPE
2099 ,p_effective_date IN DATE
2100 ) IS
2101 --
2102 l_proc varchar2(72) := g_package || 'chk_input_value_id';
2103 l_dummy pay_input_values_f.input_value_id%TYPE;
2104 --
2105 CURSOR csr_chk_input_value IS
2106 SELECT piv.input_value_id
2107 FROM pay_input_values_f piv
2108 WHERE piv.input_value_id = p_input_value_id
2109 AND piv.element_type_id = p_element_type_id
2110 AND p_effective_date BETWEEN piv.effective_start_date
2111 AND piv.effective_end_date;
2112 --
2113 BEGIN
2114 --
2115 hr_utility.set_location('Entering : '||l_proc,10);
2116 --
2117 -- Check mandatory parameter is set
2118 --
2119 hr_api.mandatory_arg_error
2120 (p_api_name => l_proc
2121 ,p_argument => 'effective_date'
2122 ,p_argument_value => p_effective_date
2123 );
2124 --
2125 hr_utility.set_location(l_proc||'/'||NVL(p_input_value_id,hr_api.g_number),20);
2126 --
2127 -- Only proceed with validation if :
2128 -- a) Inserting or
2129 -- b) The value for input_value_id has changed
2130 --
2131 IF ( (p_cagr_entitlement_item_id IS NULL) OR
2132 ((p_cagr_entitlement_item_id IS NOT NULL) AND
2133 (per_cei_shd.g_old_rec.input_value_id <>
2134 NVL(p_input_value_id,hr_api.g_number)))) THEN
2135 --
2136 hr_utility.set_location(l_proc,30);
2137 --
2138 IF p_input_value_id IS NOT NULL THEN
2139 --
2140 hr_utility.set_location(l_proc,40);
2141 --
2142 -- Check that the INPUT_VALUE has not been populated
2143 -- as well as the CAGR_API_PARAM_ID.
2144 --
2145 IF p_cagr_api_param_id IS NOT NULL THEN
2146 --
2147 hr_utility.set_message(800, 'HR_289233_PARAM_INP_VAL_COMB');
2148 hr_utility.raise_error;
2149 --
2150 END IF;
2151 --
2152 -- Check that the ELEMENT_TYPE has been
2153 -- populated as well as the input value.
2154 --
2155 IF p_element_type_id IS NULL THEN
2156 --
2157 hr_utility.set_message(800, 'HR_289388_EE_OR_IV_NULL');
2158 hr_utility.raise_error;
2159 --
2160 END IF;
2161 --
2162 OPEN csr_chk_input_value;
2163 FETCH csr_chk_input_value INTO l_dummy;
2164 --
2165 IF csr_chk_input_value%NOTFOUND THEN
2166 --
2167 CLOSE csr_chk_input_value;
2168 --
2169 hr_utility.set_message(800, 'HR_289253_INPUT_VALUE_INV');
2170 hr_utility.raise_error;
2171 --
2172 ELSE
2173 --
2174 hr_utility.set_location(l_proc,50);
2175 --
2176 CLOSE csr_chk_input_value;
2177 --
2178 END IF;
2179 --
2180 ELSIF p_input_value_id IS NULL THEN
2181 --
2182 hr_utility.set_location(l_proc,60);
2183 --
2184 -- If the element type is not null then raise an
2185 -- error as the input value must be populated as well.
2186 --
2187 IF p_element_type_id IS NOT NULL THEN
2188 --
2189 hr_utility.set_message(800, 'HR_289388_EE_OR_IV_NULL');
2190 hr_utility.raise_error;
2191 --
2192 END IF;
2193 --
2194 END IF;
2195 --
2196 END IF;
2197 --
2198 hr_utility.set_location('Leaving : '||l_proc,999);
2199 --
2200 END chk_input_value_id;
2201 --
2202 -- ---------------------------------------------------------------------------
2203 -- |-------------------------< chk_flex_value_set_id >-----------------------|
2204 -- ---------------------------------------------------------------------------
2205 --
2206 -- Desciption :
2207 --
2208 -- Validate that that the flex_value_set_id exists in the
2209 -- FND_FLEX_VALUE_SETS_F for records that have a
2210 -- flex_value_set_name that begins with 'CAGR_%' and have a
2211 -- validation_type type of 'F'
2212 --
2213 --
2214 -- Pre-conditions :
2215 --
2216 --
2217 -- In Arguments :
2218 -- p_cagr_entitlement_item_id
2219 -- p_flex_value_set_id
2220 --
2221 -- Post Success :
2222 -- Processing continues
2223 --
2224 -- Post Failure :
2225 -- An application error will be raised and processing is
2226 -- terminated
2227 --
2228 -- Access Status :
2229 -- Internal Table Handler Use only.
2230 --
2231 -- {End of Comments}
2232 --
2233 -- ---------------------------------------------------------------------------
2234 --
2235 PROCEDURE chk_flex_value_set_id
2236 (p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
2237 ,p_flex_value_set_id IN per_cagr_entitlement_items.flex_value_set_id%TYPE) IS
2238 --
2239 CURSOR c_chk_id IS
2240 SELECT flex_value_set_id
2241 FROM fnd_flex_value_sets F
2242 WHERE f.flex_value_set_id = p_flex_value_set_id
2243 AND ((f.flex_value_set_name LIKE 'CAGR%') AND
2244 ((f.flex_value_set_name NOT LIKE 'CAGR_BR_%') AND
2245 (f.flex_value_set_name <> 'CAGR_EMPLOYMENT_CATEGORY')))
2246 AND f.validation_type = 'F';
2247 --
2248 l_proc VARCHAR2(72) := g_package||'chk_flex_value_set_id';
2249 l_dummy_id per_cagr_entitlement_items.flex_value_set_id%TYPE;
2250 --
2251 BEGIN
2252 --
2253 hr_utility.set_location('Entering:'||l_proc, 10);
2254 --
2255 -- Only proceed with validation if :
2256 -- a) Inserting or
2257 -- b) The value for flex_value_set_id has changed
2258 --
2259 IF ( (p_cagr_entitlement_item_id IS NULL) OR
2260 ((p_cagr_entitlement_item_id IS NOT NULL) AND
2261 (NVL(per_cei_shd.g_old_rec.flex_value_set_id, hr_api.g_number) <>
2262 NVL(p_flex_value_set_id, hr_api.g_number)))) THEN
2263 --
2264 hr_utility.set_location(l_proc,20);
2265 --
2266 IF p_flex_value_set_id IS NOT NULL THEN
2267 --
2268 OPEN c_chk_id;
2269 FETCH c_chk_id INTO l_dummy_id;
2270 --
2271 IF c_chk_id%NOTFOUND THEN
2272 --
2273 CLOSE c_chk_id;
2274 --
2275 hr_utility.set_message(800,'HR_289255_VALUE_SET_INV');
2276 hr_utility.raise_error;
2277 --
2278 ELSE
2279 --
2280 hr_utility.set_location(l_proc,30);
2281 --
2282 CLOSE c_chk_id;
2283 --
2284 END IF;
2285 --
2286 END IF;
2287 --
2288 END IF;
2289 --
2290 hr_utility.set_location(' Leaving:'||l_proc, 100);
2291 --
2292 END chk_flex_value_set_id;
2293 --
2294 -- ---------------------------------------------------------------------------
2295 -- |-----------------------< chk_ben_rule_value_set_id >---------------------|
2296 -- ---------------------------------------------------------------------------
2297 --
2298 -- Desciption :
2299 --
2300 -- Validate that that the beneficial_rule_value_set_id exists in the
2301 -- FND_FLEX_VALUE_SETS_F for records that have a
2302 -- flex_value_set_name that begins with 'CAGR_%' and have a
2303 -- validation_type type of 'F'
2304 --
2305 --
2306 -- Pre-conditions :
2307 --
2308 --
2309 -- In Arguments :
2310 -- p_cagr_entitlement_item_id
2311 -- p_beneficial_rule_value_set_id
2312 --
2313 -- Post Success :
2314 -- Processing continues
2315 --
2316 -- Post Failure :
2317 -- An application error will be raised and processing is
2318 -- terminated
2319 --
2320 -- Access Status :
2321 -- Internal Table Handler Use only.
2322 --
2323 -- {End of Comments}
2324 --
2325 -- ---------------------------------------------------------------------------
2326 --
2327 PROCEDURE chk_ben_rule_value_set_id
2328 (p_cagr_entitlement_item_id IN NUMBER
2329 ,p_ben_rule_value_set_id IN NUMBER) IS
2330 --
2331 CURSOR c_chk_id IS
2332 SELECT flex_value_set_id
2333 FROM fnd_flex_value_sets F
2334 WHERE f.flex_value_set_id = p_ben_rule_value_set_id
2335 AND f.flex_value_set_name like 'CAGR_BR_%'
2336 AND f.validation_type = 'F';
2337 --
2338 l_proc VARCHAR2(72) := g_package||'chk_beneficial_rule_value_set_id';
2339 l_dummy_id NUMBER;
2340 --
2341 BEGIN
2342 --
2343 hr_utility.set_location('Entering:'||l_proc, 10);
2344 --
2345 -- Only proceed with validation if :
2346 -- a) Inserting or
2347 -- b) The value for ben rule value set has changed
2348 --
2349 IF ( (p_cagr_entitlement_item_id IS NULL) OR
2350 ((p_cagr_entitlement_item_id IS NOT NULL) AND
2351 (NVL(per_cei_shd.g_old_rec.ben_rule_value_set_id, hr_api.g_number) <>
2352 NVL(p_ben_rule_value_set_id, hr_api.g_number)))) THEN
2353 --
2354 hr_utility.set_location(l_proc,20);
2355 --
2356 IF p_ben_rule_value_set_id IS NOT NULL THEN
2357 --
2358 OPEN c_chk_id;
2359 FETCH c_chk_id INTO l_dummy_id;
2360 --
2361 IF c_chk_id%NOTFOUND THEN
2362 --
2363 CLOSE c_chk_id;
2364 --
2365 hr_utility.set_message(800,'HR_289584_BEN_VALUE_SET_INV');
2366 hr_utility.raise_error;
2367 --
2368 ELSE
2369 --
2370 hr_utility.set_location(l_proc,30);
2371 --
2372 CLOSE c_chk_id;
2373 --
2374 END IF;
2375 --
2376 END IF;
2377 --
2378 END IF;
2379 --
2380 hr_utility.set_location(' Leaving:'||l_proc, 100);
2381 --
2382 END chk_ben_rule_value_set_id;
2383 --
2384 -- ----------------------------------------------------------------------------
2385 -- |---------------------------< insert_validate >----------------------------|
2386 -- ----------------------------------------------------------------------------
2387 Procedure insert_validate
2388 (p_effective_date in date
2389 ,p_rec in out nocopy per_cei_shd.g_rec_type
2390 ) is
2391 --
2392 l_proc varchar2(72) := g_package||'insert_validate';
2393 --
2394 BEGIN
2395 --
2396 hr_utility.set_location('Entering:'||l_proc, 10);
2397 --
2398 -- Call all supporting business operations
2399 --
2400 -- Validate Bus Grp if one has been entered.
2401 --
2402 -- IF p_rec.business_group_id IS NOT NULL THEN
2403 --
2404 hr_api.validate_bus_grp_id(p_rec.business_group_id);
2405 --
2406 -- END IF;
2407 --
2408 hr_utility.set_location(l_proc,20);
2409 --
2410 per_cei_bus.chk_item_name
2411 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2412 ,p_item_name => p_rec.item_name
2413 ,p_category_name => p_rec.category_name
2414 ,p_legislation_code => p_rec.legislation_code
2415 ,p_business_group_id => p_rec.business_group_id);
2416 --
2417 hr_utility.set_location(l_proc,30);
2418 --
2419 per_cei_bus.chk_legislation_code
2420 (p_legislation_code => p_rec.legislation_code);
2421 --
2422 hr_utility.set_location(l_proc,40);
2423 --
2424 per_cei_bus.chk_beneficial_rule
2425 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2426 ,p_beneficial_rule => p_rec.beneficial_rule
2427 ,p_ben_rule_value_set_id => p_rec.ben_rule_value_set_id
2428 ,p_effective_date => p_effective_date);
2429 --
2430 hr_utility.set_location(l_proc,50);
2431 --
2432 per_cei_bus.chk_cagr_api_id
2433 (p_cagr_api_id => p_rec.cagr_api_id
2434 ,p_cagr_api_param_id => p_rec.cagr_api_param_id
2435 ,p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2436 ,p_element_type_id => p_rec.element_type_id);
2437 --
2438 hr_utility.set_location(l_proc,60);
2439 --
2440 per_cei_bus.chk_cagr_api_param_id
2441 (p_cagr_api_param_id => p_rec.cagr_api_param_id
2442 ,p_input_value_id => p_rec.element_type_id
2443 ,p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2444 ,p_cagr_api_id => p_rec.cagr_api_id
2445 );
2446 --
2447 hr_utility.set_location(l_proc,70);
2448 --
2449 per_cei_bus.chk_multiple_entries_flag
2450 (p_mult_entries_allowed_flag => p_rec.mult_entries_allowed_flag
2451 ,p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2452 ,p_category_name => p_rec.category_name
2453 ,p_element_type_id => p_rec.element_type_id
2454 ,p_business_group_id => p_rec.business_group_id
2455 ,p_legislation_code => p_rec.legislation_code
2456 ,p_effective_date => p_effective_date
2457 );
2458 --
2459 hr_utility.set_location(l_proc,80);
2460 --
2461 per_cei_bus.chk_category_name
2462 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2463 ,p_category_name => p_rec.category_name
2464 ,p_effective_date => p_effective_date);
2465 -- -- CEI Enh
2466 hr_utility.set_location(l_proc,85); -- CEI Enh
2467 -- -- CEI Enh
2468 per_cei_bus.chk_auto_create_entries_flag -- CEI Enh
2469 (p_auto_create_entries_flag => p_rec.auto_create_entries_flag -- CEI Enh
2470 ,p_category_name => p_rec.category_name -- CEI Enh
2471 ,p_effective_date => p_effective_date -- CEI Enh
2472 ); -- CEI Enh
2473 --
2474 hr_utility.set_location(l_proc,90);
2475 --
2476 per_cei_bus.chk_uom
2477 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2478 ,p_uom => p_rec.uom
2479 ,p_input_value_id => p_rec.input_value_id
2480 ,p_cagr_api_param_id => p_rec.cagr_api_param_id
2481 ,p_effective_date => p_effective_date);
2482 --
2483 hr_utility.set_location(l_proc,100);
2484 --
2485 per_cei_bus.chk_item_is_unique
2486 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2487 ,p_category_name => p_rec.category_name
2488 ,p_cagr_api_id => p_rec.cagr_api_id
2489 ,p_cagr_api_param_id => p_rec.cagr_api_param_id
2490 ,p_element_type_id => p_rec.element_type_id
2491 ,p_input_value_id => p_rec.input_value_id
2492 ,p_legislation_code => p_rec.legislation_code
2493 ,p_business_group_id => p_rec.business_group_id
2494 );
2495 --
2496 hr_utility.set_location(l_proc,110);
2497 --
2498 per_cei_bus.chk_element_type_id
2499 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2500 ,p_element_type_id => p_rec.element_type_id
2501 ,p_input_value_id => p_rec.input_value_id
2502 ,p_cagr_api_id => p_rec.cagr_api_id
2503 ,p_effective_date => p_effective_date
2504 ,p_legislation_code => p_rec.legislation_code
2505 ,p_business_group_id => p_rec.business_group_id
2506 );
2507 --
2508 hr_utility.set_location(l_proc,120);
2509 --
2510 per_cei_bus.chk_input_value_id
2511 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2512 ,p_element_type_id => p_rec.element_type_id
2513 ,p_input_value_id => p_rec.input_value_id
2514 ,p_cagr_api_param_id => p_rec.cagr_api_param_id
2515 ,p_effective_date => p_effective_date
2516 );
2517 --
2518 hr_utility.set_location(l_proc,130);
2519 --
2520 per_Cei_bus.chk_flex_value_set_id
2521 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2522 ,p_flex_value_set_id => p_rec.flex_value_set_id);
2523 --
2524 hr_utility.set_location(l_proc,140);
2525 --
2526 per_cei_bus.chk_ben_rule_value_set_id
2527 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2528 ,p_ben_rule_value_set_id => p_rec.ben_rule_value_set_id);
2529 --
2530 hr_utility.set_location(l_proc,150);
2531 --
2532 per_cei_bus.chk_column_type
2533 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2534 ,p_column_type => p_rec.column_type
2535 ,p_uom => p_rec.uom
2536 ,p_input_value_id => p_rec.input_value_id
2537 ,p_category_name => p_rec.category_name
2538 ,p_effective_date => p_effective_date
2539 ,p_cagr_api_param_id => p_rec.cagr_api_param_id);
2540 --
2541 hr_utility.set_location(l_proc,160);
2542 --
2543 per_cei_bus.chk_column_size
2544 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2545 ,p_column_size => p_rec.column_size
2546 ,p_cagr_api_param_id => p_rec.cagr_api_param_id);
2547 --
2548 hr_utility.set_location(' Leaving:'||l_proc, 200);
2549 --
2550 END insert_validate;
2551 --
2552 -- ----------------------------------------------------------------------------
2553 -- |---------------------------< update_validate >----------------------------|
2554 -- ----------------------------------------------------------------------------
2555 Procedure update_validate
2556 (p_effective_date in date
2557 ,p_rec in out nocopy per_cei_shd.g_rec_type
2558 ) is
2559 --
2560 l_proc varchar2(72) := g_package||'update_validate';
2561 --
2562 BEGIN
2563 --
2564 hr_utility.set_location('Entering:'||l_proc, 10);
2565 --
2566 -- Call all supporting business operations
2567 -- Validate Bus Grp if one has been entered.
2568 --
2569 IF p_rec.business_group_id IS NOT NULL THEN
2570 --
2571 hr_api.validate_bus_grp_id(p_rec.business_group_id);
2572 --
2573 END IF;
2574 --
2575 hr_utility.set_location(l_proc,20);
2576 --
2577 chk_non_updateable_args
2578 (p_effective_date => p_effective_date
2579 ,p_rec => p_rec);
2580 --
2581 hr_utility.set_location(l_proc,30);
2582 --
2583 per_cei_bus.chk_item_name
2584 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2585 ,p_item_name => p_rec.item_name
2586 ,p_category_name => p_rec.category_name
2587 ,p_legislation_code => p_rec.legislation_code
2588 ,p_business_group_id => p_rec.business_group_id);
2589 --
2590 hr_utility.set_location(l_proc,40);
2591 --
2592 per_cei_bus.chk_legislation_code
2593 (p_legislation_code => p_rec.legislation_code);
2594 --
2595 hr_utility.set_location(l_proc,50);
2596 --
2597 per_cei_bus.chk_beneficial_rule
2598 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2599 ,p_beneficial_rule => p_rec.beneficial_rule
2600 ,p_ben_rule_value_set_id => p_rec.ben_rule_value_set_id
2601 ,p_effective_date => p_effective_date);
2602 --
2603 hr_utility.set_location(l_proc,60);
2604 --
2605 per_cei_bus.chk_cagr_api_id
2606 (p_cagr_api_id => p_rec.cagr_api_id
2607 ,p_cagr_api_param_id => p_rec.cagr_api_param_id
2608 ,p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2609 ,p_element_type_id => p_rec.element_type_id);
2610 --
2611 hr_utility.set_location(l_proc,70);
2612 --
2613 per_cei_bus.chk_cagr_api_param_id
2614 (p_cagr_api_param_id => p_rec.cagr_api_param_id
2615 ,p_input_value_id => p_rec.element_type_id
2616 ,p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2617 ,p_cagr_api_id => p_rec.cagr_api_id
2618 );
2619 --
2620 hr_utility.set_location(l_proc,80);
2621 --
2622 per_cei_bus.chk_category_name
2623 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2624 ,p_category_name => p_rec.category_name
2625 ,p_effective_date => p_effective_date);
2626 -- -- CEI Enh
2627 hr_utility.set_location(l_proc,85); -- CEI Enh
2628 -- -- CEI Enh
2629 per_cei_bus.chk_auto_create_entries_flag -- CEI Enh
2630 (p_auto_create_entries_flag => p_rec.auto_create_entries_flag -- CEI Enh
2631 ,p_category_name => p_rec.category_name -- CEI Enh
2632 ,p_effective_date => p_effective_date -- CEI Enh
2633 ); -- CEI Enh
2634 --
2635 hr_utility.set_location(l_proc,90);
2636 --
2637 per_cei_bus.chk_uom
2638 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2639 ,p_uom => p_rec.uom
2640 ,p_input_value_id => p_rec.input_value_id
2641 ,p_cagr_api_param_id => p_rec.cagr_api_param_id
2642 ,p_effective_date => p_effective_date);
2643 --
2644 hr_utility.set_location(l_proc,100);
2645 --
2646 per_cei_bus.chk_item_is_unique
2647 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2648 ,p_category_name => p_rec.category_name
2649 ,p_cagr_api_id => p_rec.cagr_api_id
2650 ,p_cagr_api_param_id => p_rec.cagr_api_param_id
2651 ,p_element_type_id => p_rec.element_type_id
2652 ,p_input_value_id => p_rec.input_value_id
2653 ,p_legislation_code => p_rec.legislation_code
2654 ,p_business_group_id => p_rec.business_group_id
2655 );
2656 --
2657 hr_utility.set_location(l_proc,110);
2658 --
2659 per_cei_bus.chk_element_type_id
2660 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2661 ,p_element_type_id => p_rec.element_type_id
2662 ,p_input_value_id => p_rec.input_value_id
2663 ,p_cagr_api_id => p_rec.cagr_api_id
2664 ,p_effective_date => p_effective_date
2665 ,p_legislation_code => p_rec.legislation_code
2666 ,p_business_group_id => p_rec.business_group_id
2667 );
2668 --
2669 hr_utility.set_location(l_proc,120);
2670 --
2671 per_cei_bus.chk_input_value_id
2672 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2673 ,p_element_type_id => p_rec.element_type_id
2674 ,p_input_value_id => p_rec.input_value_id
2675 ,p_cagr_api_param_id => p_rec.cagr_api_param_id
2676 ,p_effective_date => p_effective_date
2677 );
2678 --
2679 hr_utility.set_location(l_proc,130);
2680 --
2681 per_cei_bus.chk_flex_value_set_id
2682 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2683 ,p_flex_value_set_id => p_rec.flex_value_set_id);
2684 --
2685 hr_utility.set_location(l_proc,140);
2686 --
2687 per_cei_bus.chk_ben_rule_value_set_id
2688 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2689 ,p_ben_rule_value_set_id => p_rec.ben_rule_value_set_id);
2690 --
2691 hr_utility.set_location(l_proc,150);
2692 --
2693 per_cei_bus.chk_column_type
2694 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2695 ,p_column_type => p_rec.column_type
2696 ,p_uom => p_rec.uom
2697 ,p_input_value_id => p_rec.input_value_id
2698 ,p_category_name => p_rec.category_name
2699 ,p_effective_date => p_effective_date
2700 ,p_cagr_api_param_id => p_rec.cagr_api_param_id);
2701 --
2702 hr_utility.set_location(l_proc,160);
2703 --
2704 per_cei_bus.chk_column_size
2705 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2706 ,p_column_size => p_rec.column_size
2707 ,p_cagr_api_param_id => p_rec.cagr_api_param_id);
2708 --
2709 hr_utility.set_location(l_proc,160);
2710 --
2711 per_cei_bus.chk_multiple_entries_flag
2712 (p_mult_entries_allowed_flag => p_rec.mult_entries_allowed_flag
2713 ,p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id
2714 ,p_category_name => p_rec.category_name
2715 ,p_element_type_id => p_rec.element_type_id
2716 ,p_business_group_id => p_rec.business_group_id
2717 ,p_legislation_code => p_rec.legislation_code
2718 ,p_effective_date => p_effective_date
2719 );
2720 --
2721 hr_utility.set_location(' Leaving:'||l_proc, 999);
2722 --
2723 END update_validate;
2724 --
2725 -- ----------------------------------------------------------------------------
2726 -- |---------------------------< delete_validate >----------------------------|
2727 -- ----------------------------------------------------------------------------
2728 Procedure delete_validate
2729 (p_rec in per_cei_shd.g_rec_type
2730 ) is
2731 --
2732 l_proc varchar2(72) := g_package||'delete_validate';
2733 --
2734 Begin
2735 hr_utility.set_location('Entering:'||l_proc, 5);
2736 --
2737 -- Call all supporting business operations
2738 --
2739 per_cei_bus.chk_item_use
2740 (p_cagr_entitlement_item_id => p_rec.cagr_entitlement_item_id);
2741 --
2742 hr_utility.set_location(' Leaving:'||l_proc, 10);
2743 End delete_validate;
2744 --
2745 end per_cei_bus;
[Home] [Help]