1 Package Body per_pcl_bus as
2 /* $Header: pepclrhi.pkb 115.9 2002/12/09 15:33:43 pkakar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' per_pcl_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_line_id number default null;
15 --
16 -- ----------------------------------------------------------------------------
17 -- |--------------------------< check_for_correct_type >-----------------------|
18 -- ----------------------------------------------------------------------------
19 --
20 PROCEDURE chk_dates_are_valid
21 (p_cagr_entitlement_item_id IN per_cagr_entitlements.cagr_entitlement_item_id%TYPE
22 ,p_value IN per_cagr_entitlement_lines_f.value%TYPE
23 ,p_range_from IN per_cagr_entitlement_lines_f.range_from%TYPE
24 ,p_range_to IN per_cagr_entitlement_lines_f.range_to%TYPE
25 ,p_effective_date IN DATE) IS
26 --
27 l_proc VARCHAR2(72) := g_package||'check_dates_are_valid';
28 l_column_type per_cagr_entitlement_items.column_type%TYPE;
29 l_value_date DATE;
30 l_range_to_date DATE;
31 l_range_from_date DATE;
32 --
33 BEGIN
34 --
35 hr_utility.set_location('Entering:'|| l_proc, 10);
36 --
37 l_column_type := per_cagr_utility_pkg.get_column_type
38 (p_cagr_entitlement_item_id => p_cagr_entitlement_item_id
39 ,p_effective_date => p_effective_date);
40 --
41 -- Only check the fields is for entitlement items
42 -- that have been defined as a DATE type.
43 --
44 IF l_column_type = 'DATE' THEN
45 --
46 hr_utility.set_location(l_proc, 20);
47 --
48 -- Convert paramters to date format for comparison
49 --
50 l_value_date := p_value;
51 l_range_from_date := p_range_from;
52 l_range_to_date := p_range_to;
53 --
54 hr_utility.set_location(l_proc||' / '||l_value_Date,25);
55 hr_utility.set_location(l_proc||' / '||l_range_from_Date,26);
56 hr_utility.set_location(l_proc||' / '||l_range_to_Date,27);
57 --
58 -- Check to ensure that the value date is within
59 -- the range_from and range_to dates.
60 --
61 IF l_value_date < l_range_from_date OR
62 l_value_date > l_range_to_date THEN
63 --
64 hr_utility.set_message(800, 'HR_289331_DATE_NOT_IN_RANGE');
65 hr_utility.raise_error;
66 --
67 -- Ensure that the range_from date is
68 -- not later than the range_to date.
69 --
70 ELSIF l_range_from_date > l_range_to_date THEN
71 --
72 hr_utility.set_message(800, 'HR_289332_FROM_DT_AFTER_TO_DT');
73 hr_utility.raise_error;
74 --
75 -- Ensure that the range_to date is
76 -- not before the range_from date
77 --
78 ELSIF l_range_to_date < l_range_from_date THEN
79 --
80 hr_utility.set_message(800, 'HR_289333_TO_DT_BEFORE_FROM_DT');
81 hr_utility.raise_error;
82 --
83 END IF;
84 --
85 END IF;
86 --
87 hr_utility.set_location('Leaving:'|| l_proc, 999);
88 --
89 END chk_dates_are_valid;
90 --
91 -- ----------------------------------------------------------------------------
92 -- |--------------------------< check_for_correct_type >-----------------------|
93 -- ----------------------------------------------------------------------------
94 --
95 PROCEDURE check_for_correct_type
96 (p_cagr_entitlement_item_id IN NUMBER
97 ,p_value IN OUT NOCOPY VARCHAR2
98 ,p_business_group_id IN NUMBER
99 ,p_effective_date IN DATE) IS
100 --
101 -- Delcare Local Variables
102 --
103 l_proc VARCHAR2(72) := g_package||'check_for_correct_type';
104 l_column_type per_cagr_entitlement_items.column_type%TYPE;
105 l_column_size per_cagr_entitlement_items.column_size%TYPE;
106 l_number NUMBER;
107 l_date DATE;
108 l_value VARCHAR2(255);
109 l_output VARCHAR2(255);
110 l_rgeflg VARCHAR2(255);
111 l_ccy_code per_business_groups.currency_code%TYPE;
112 size_error EXCEPTION;
113 --
114 CURSOR csr_get_ccy_code IS
115 SELECT currency_code
116 FROM per_business_groups
117 WHERE business_group_id = p_business_group_id;
118 --
119 CURSOR csr_get_item_size IS
120 SELECT cap.column_size
121 FROM per_cagr_api_parameters cap,
122 per_cagr_entitlement_items cei
123 WHERE cap.cagr_api_param_id = cei.cagr_api_param_id
124 AND cei.cagr_entitlement_item_id = p_cagr_entitlement_item_id;
125 --
126 BEGIN
127 --
128 hr_utility.set_location('Entering:'|| l_proc, 10);
129 --
130 l_column_type := per_cagr_utility_pkg.get_column_type
131 (p_cagr_entitlement_item_id => p_cagr_entitlement_item_id
132 ,p_effective_date => p_effective_date);
133 --
134 IF l_column_type = 'NUM' THEN
135 --
136 hr_utility.set_location(l_proc, 20);
137 --
138 l_number := p_value;
139 --
140 hr_utility.set_location(l_proc, 30);
141 --
142 -- Check that the column size for the number
143 -- fields is not greater than the size seeded
144 -- for that api parameter
145 --
146 BEGIN
147 --
148 OPEN csr_get_item_size;
149 FETCH csr_get_item_size INTO l_column_size;
150 --
151 CLOSE csr_get_item_size;
152 --
153 hr_utility.set_location(l_proc, 40);
154 --
155 IF LENGTH(p_value) > l_column_size THEN
156 --
157 RAISE size_error;
158 --
159 END IF;
160 --
161 EXCEPTION
162 --
163 WHEN size_error THEN
164 RAISE;
165 END;
166 --
167 ELSIF l_column_type = 'DATE' THEN
168 --
169 hr_utility.set_location(l_proc, 50);
170 --
171 l_date := p_value;
172 --
173 ELSIF l_column_type = 'VAR' THEN
174 --
175 hr_utility.set_location(l_proc, 60);
176 --
177 l_value := p_value;
178 --
179 ELSE
180 --
181 hr_utility.set_location(l_proc, 70);
182 --
183 l_value := p_value;
184 --
185 OPEN csr_get_ccy_code;
186 FETCH csr_get_ccy_code INTO l_ccy_code;
187 CLOSE csr_get_ccy_code;
188 --
189 hr_utility.set_location(l_proc, 80);
190 --
191 hr_chkfmt.checkformat
192 (value => l_value
193 ,format => l_column_type
194 ,output => l_output
195 ,minimum => NULL
196 ,maximum => NULL
197 ,nullok => NULL
198 ,rgeflg => l_rgeflg
199 ,curcode => l_ccy_code);
200 --
201 p_value := l_value;
202 --
203 END IF;
204 --
205 hr_utility.set_location('Leaving:'|| l_proc, 999);
206 --
207 EXCEPTION
208 --
209 WHEN size_error THEN
210 --
211 fnd_message.set_name('PER','HR_289588_VALUE_SIZE_TOO_LONG');
212 fnd_message.raise_error;
213 --
214 WHEN OTHERS THEN
215 --
216 IF l_column_type = 'DATE' THEN
217 --
218 fnd_message.set_name('PER','HR_289328_ENT_ITEM_NOT_A_DATE');
219 fnd_message.raise_error;
220 --
221 ELSIF l_column_type = 'NUM' THEN
222 --
223 fnd_message.set_name('PER','HR_289326_ENT_ITEM_NOT_A_NUM');
224 fnd_message.raise_error;
225 --
226 ELSE
227 --
228 fnd_message.set_name('PER','HR_289473_INVALID_VALUE');
229 fnd_message.raise_error;
230 --
231 END IF;
232 --
233 RAISE;
234 --
235 END check_for_correct_type;
236 --
237 -- ---------------------------------------------------------------------------
238 -- |----------------------< set_security_group_id >--------------------------|
239 -- ---------------------------------------------------------------------------
240 --
241 Procedure set_security_group_id
242 (p_cagr_entitlement_line_id in number
243 ) is
244 --
245 -- Declare cursor
246 --
247 cursor csr_sec_grp is
248 select pbg.security_group_id
249 from per_business_groups pbg
250 , per_cagr_entitlement_lines_f pcl
251 , per_cagr_entitlements pce
252 , per_cagr_entitlement_items pci
253
254 where pcl.cagr_entitlement_line_id = p_cagr_entitlement_line_id
255 and pcl.cagr_entitlement_id = pce.cagr_entitlement_id
256 and pce.cagr_entitlement_item_id = pci.cagr_entitlement_item_id
257 and pbg.business_group_id = pci.business_group_id ;
258
259 --
260 -- Declare local variables
261 --
262
263 l_security_group_id number;
264 l_proc varchar2(72) := g_package||'set_security_group_id';
265 --
266 begin
267 --
268 hr_utility.set_location('Entering:'|| l_proc, 10);
269 --
270 -- Ensure that all the mandatory parameter are not null
271 --
272 hr_api.mandatory_arg_error
273 (p_api_name => l_proc
274 ,p_argument => 'cagr_entitlement_line_id'
275 ,p_argument_value => p_cagr_entitlement_line_id
276 );
277 --
278 open csr_sec_grp;
279 fetch csr_sec_grp into l_security_group_id;
280 --
281 if csr_sec_grp%notfound then
282 --
283 close csr_sec_grp;
284 --
285 -- The primary key is invalid therefore we must error
286 --
287 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
288 fnd_message.raise_error;
289 --
290 end if;
291 close csr_sec_grp;
292 --
293 -- Set the security_group_id in CLIENT_INFO
294 --
295 hr_api.set_security_group_id
296 (p_security_group_id => l_security_group_id
297 );
298 --
299 hr_utility.set_location(' Leaving:'|| l_proc, 20);
300 --
301 end set_security_group_id;
302 --
303 -- ---------------------------------------------------------------------------
304 -- |---------------------< return_legislation_code >-------------------------|
305 -- ---------------------------------------------------------------------------
306 --
307 Function return_legislation_code
308 (p_cagr_entitlement_line_id in number
309 )
310 Return Varchar2 Is
311 --
312 -- Declare cursor
313 --
314
315 cursor csr_leg_code is
316 select pbg.legislation_code
317 from per_business_groups pbg
318 , per_cagr_entitlement_lines_f pcl
319 , per_cagr_entitlements pce
320 , per_cagr_entitlement_items pci
321
322 where pcl.cagr_entitlement_line_id = p_cagr_entitlement_line_id
323 and pcl.cagr_entitlement_id = pce.cagr_entitlement_id
324 and pce.cagr_entitlement_item_id = pci.cagr_entitlement_item_id
325 and pbg.business_group_id = pci.business_group_id ;
326
327
328 --
329 -- Declare local variables
330 --
331 l_legislation_code varchar2(150);
332 l_proc varchar2(72) := g_package||'return_legislation_code';
333 --
334 Begin
335 --
336 hr_utility.set_location('Entering:'|| l_proc, 10);
337 --
338 -- Ensure that all the mandatory parameter are not null
339 --
340 hr_api.mandatory_arg_error
341 (p_api_name => l_proc
342 ,p_argument => 'cagr_entitlement_line_id'
343 ,p_argument_value => p_cagr_entitlement_line_id
344 );
345 --
346 if ( nvl(per_pcl_bus.g_cagr_entitlement_line_id, hr_api.g_number)
347 = p_cagr_entitlement_line_id) then
348 --
349 -- The legislation code has already been found with a previous
350 -- call to this function. Just return the value in the global
351 -- variable.
352 --
353 l_legislation_code := per_pcl_bus.g_legislation_code;
354 hr_utility.set_location(l_proc, 20);
355 else
356 --
357 -- The ID is different to the last call to this function
358 -- or this is the first call to this function.
359 --
360 open csr_leg_code;
361 fetch csr_leg_code into l_legislation_code;
362 --
363 if csr_leg_code%notfound then
364 --
365 -- The primary key is invalid therefore we must error
366 --
367 close csr_leg_code;
368 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
369 fnd_message.raise_error;
370 end if;
371 hr_utility.set_location(l_proc,30);
372 --
373 -- Set the global variables so the values are
374 -- available for the next call to this function.
375 --
376 close csr_leg_code;
377 per_pcl_bus.g_cagr_entitlement_line_id := p_cagr_entitlement_line_id;
378 per_pcl_bus.g_legislation_code := l_legislation_code;
379 end if;
380 hr_utility.set_location(' Leaving:'|| l_proc, 40);
381 return l_legislation_code;
382 end return_legislation_code;
383 --
384 -- ----------------------------------------------------------------------------
385 -- |-----------------------< chk_non_updateable_args >------------------------|
386 -- ----------------------------------------------------------------------------
387 -- {Start Of Comments}
388 --
389 -- Description:
390 -- This procedure is used to ensure that non updateable attributes have
391 -- not been updated. If an attribute has been updated an error is generated.
392 --
393 -- Pre Conditions:
394 -- g_old_rec has been populated with details of the values currently in
395 -- the database.
396 --
397 -- In Arguments:
398 -- p_rec has been populated with the updated values the user would like the
399 -- record set to.
400 --
401 -- Post Success:
402 -- Processing continues if all the non updateable attributes have not
403 -- changed.
404 --
405 -- Post Failure:
406 -- An application error is raised if any of the non updatable attributes
407 -- have been altered.
408 --
409 -- {End Of Comments}
410 -- ----------------------------------------------------------------------------
411 Procedure chk_non_updateable_args
415 --
412 (p_effective_date in date
413 ,p_rec in per_pcl_shd.g_rec_type
414 ) IS
416 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
417 l_error EXCEPTION;
418 l_argument varchar2(30);
419 --
420 Begin
421 --
422 -- Only proceed with the validation if a row exists for the current
423 -- record in the HR Schema.
424 --
425 IF NOT per_pcl_shd.api_updating
426 (p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
427 ,p_effective_date => p_effective_date
428 ,p_object_version_number => p_rec.object_version_number
429 ) THEN
430 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
431 fnd_message.set_token('PROCEDURE ', l_proc);
432 fnd_message.set_token('STEP ', '5');
433 fnd_message.raise_error;
434 END IF;
435
436
437 IF nvl(p_rec.cagr_entitlement_id, hr_api.g_number) <>
438 nvl(per_pcl_shd.g_old_rec.cagr_entitlement_id,hr_api.g_number) THEN
439 --
440 l_argument := 'cagr_entitlement_id';
441 RAISE l_error;
442 --
443 END IF;
444
445 EXCEPTION
446 WHEN l_error THEN
447 hr_api.argument_changed_error
448 (p_api_name => l_proc
449 ,p_argument => l_argument);
450 WHEN OTHERS THEN
451 RAISE;
452 End chk_non_updateable_args;
453
454 --
455 -- ---------------------------------------------------------------------------
456 -- |---------------------------< chk_mandatory >-----------------------------|
457 -- ---------------------------------------------------------------------------
458 --
459 -- Desciption :
460 --
461 -- Validate that on insert and update mandatory is not null and that
462 -- it is validated against hr_lookups.
463 --
464 --
465 -- Pre-conditions :
466 --
467 --
468 -- In Arguments :
469 -- p_mandatory
470 -- p_effective_date
471 -- p_cagr_entitlement_line_id
472 --
473 -- Post Success :
474 -- Processing continues
475 --
476 -- Post Failure :
477 -- An application error will be raised and processing is
478 -- terminated
479 --
480 -- Access Status :
481 -- Internal Table Handler Use only.
482 --
483 -- {End of Comments}
484 --
485 -- ---------------------------------------------------------------------------
486 --
487 PROCEDURE chk_mandatory
488 ( p_mandatory IN per_cagr_entitlement_lines_f.mandatory%TYPE
489 ,p_cagr_entitlement_line_id IN per_cagr_entitlement_lines_f.cagr_entitlement_line_id%TYPE
490 ,p_effective_date IN DATE
491 ,p_validation_start_date IN DATE
492 ,p_validation_end_date IN DATE) IS
493
494 --
495 l_proc VARCHAR2(72) := g_package || 'chk_mandatory';
496 l_dummy VARCHAR2(1);
497 --
498
499 BEGIN
500 --
501 hr_utility.set_location('Entering:'||l_proc, 10);
502 --
503 -- Check mandatory is not null
504 --
505 IF p_mandatory IS NULL THEN
506 --
507 hr_utility.set_message(800, 'HR_289281_MANDATORY_NULL');
508 hr_utility.raise_error;
509 --
510 END IF;
511 --
512 -- Only proceed with validation if :
513 -- a) Inserting or
514 -- b) The value for mandatory has changed
515 --
516 IF ( (p_cagr_entitlement_line_id IS NULL) OR
517 ((p_cagr_entitlement_line_id IS NOT NULL) AND
518 (per_pcl_shd.g_old_rec.mandatory <> p_mandatory))) THEN
519 --
520 hr_utility.set_location(l_proc, 20);
521 --
522 -- Check that the type exists in HR_LOOKUPS
523 --
524 IF hr_api.not_exists_in_dt_hr_lookups
525 (p_effective_date => p_effective_date
526 ,p_lookup_type => 'YES_NO'
527 ,p_lookup_code => p_mandatory
528 ,p_validation_start_date => p_validation_start_date
529 ,p_validation_end_date => p_validation_end_date) THEN
530 --
531 hr_utility.set_location(l_proc, 30);
532 --
533 hr_utility.set_message(800, 'HR_289282_MANDATORY_INVALID');
534 hr_utility.raise_error;
535 --
536 END IF;
537 --
538 END IF;
539 --
540 hr_utility.set_location('Leaving '||l_proc, 40);
541 --
542 --
543 END chk_mandatory;
544 --
545 -- ---------------------------------------------------------------------------
546 -- |---------------------------< chk_values >--------------------------------|
547 -- ---------------------------------------------------------------------------
548 --
549 -- Desciption :
550 --
551 -- Validate that value stored in the value field is correct. This is
552 -- achieved by checking the following:
553 --
554 -- 1). If the entitlement item has been defined with a value set
555 -- then the value column will contain an ID. Therefore we must
556 -- check that this ID exists as the primary key to the table
557 -- defined in the value set.
558 --
559 -- 2). Enusre that the value in the value column matches the type
560 -- defined for the entitlement item. For example if the entitlement
564 -- Pre-conditions :
561 -- item has been defined as a NUMBER then the value column
562 -- must be a number.
563 --
565 --
566 --
567 -- In Arguments :
568 -- p_value
569 -- p_range_from
570 -- p_range_to
571 -- p_cagr_entitlement_item_id
572 -- p_cagr_entitlement_line_id
573 --
574 -- Post Success :
575 -- Processing continues
576 --
577 -- Post Failure :
578 -- An application error will be raised and processing is
579 -- terminated
580 --
581 -- Access Status :
582 -- Internal Table Handler Use only.
583 --
584 -- {End of Comments}
585 --
586 -- ---------------------------------------------------------------------------
587 --
588 PROCEDURE chk_value
589 (p_value IN per_cagr_entitlement_lines_f.value%TYPE
590 ,p_cagr_entitlement_line_id IN per_cagr_entitlement_lines_f.cagr_entitlement_line_id%TYPE
591 ,p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
592 ,p_cagr_entitlement_id IN per_cagr_entitlements.cagr_entitlement_id%TYPE
593 ,p_category_name IN VARCHAR2
594 ,p_effective_date IN DATE) IS
595 --
596 -- Declare Local Variables
597 --
598 l_proc VARCHAR2(72) := g_package || 'chk_values';
599 l_flex_value_set_id per_cagr_entitlement_items.flex_value_set_id%TYPE;
600 l_value_desc VARCHAR2(2000);
601 l_non_value_category BOOLEAN;
602 l_value per_cagr_entitlement_lines_f.value%TYPE;
603 l_formatted_value VARCHAR2(2000);
604 --
605 BEGIN
606 --
607 hr_utility.set_location('Entering:'||l_proc, 10);
608 --
609 -- Check mandatory parameters has been set
610 --
611 hr_api.mandatory_arg_error
612 (p_api_name => l_proc
613 ,p_argument => 'CAGR_ENTITLEMENT_ITEM_ID'
614 ,p_argument_value => p_cagr_entitlement_item_id);
615 --
616 -- Function to see if the value field is supported
617 -- by the category name passed in.
618 --
619 l_non_value_category := per_pcl_shd.non_value_category
620 (p_category_name => p_category_name);
621 --
622 -- If the value field has been populated for a category
623 -- that does not use the value field then raise an error.
624 --
625 IF p_value IS NOT NULL AND l_non_value_category THEN
626 --
627 hr_utility.set_message(800, 'HR_289346_VALUE_IS_NOT_NULL');
628 hr_utility.raise_error;
629 --
630 -- Only validate if the value is populated and
631 -- the category is not a category for which the value
632 -- should not be populated (eg. Process).
633 --
634 ELSIF p_value IS NOT NULL AND NOT l_non_value_category THEN
635 --
636 hr_utility.set_location(l_proc, 20);
637 --
638 -- Check value has been populated as it should
639 -- now contain a value.
640 --
641 hr_api.mandatory_arg_error
642 (p_api_name => l_proc
643 ,p_argument => 'VALUE'
644 ,p_argument_value => p_value);
645 --
646 -- Only proceed with validation if :
647 -- a) Inserting or
648 -- b) The value for value has changed
649 --
650 IF ( (p_cagr_entitlement_line_id IS NULL) OR
651 ((p_cagr_entitlement_line_id IS NOT NULL) AND
652 (per_pcl_shd.g_old_rec.value <> p_value))) THEN
653 --
654 hr_utility.set_location(l_proc,30);
655 --
656 -- Fetch the flex_value_set_id if one has
657 -- been set for the entitlement item
658 --
659 l_flex_value_set_id := per_pcl_shd.retrieve_value_set_id
660 (p_cagr_entitlement_item_id => p_cagr_entitlement_item_id);
661 --
662 hr_utility.set_location(l_proc, 40);
663 --
664 -- If the entitlement item does not have
665 -- a value set stored against it then
666 -- just check that the value is of the
667 -- correct type for the entitlement item
668 --
669 IF l_flex_value_set_id IS NULL THEN
670 --
671 hr_utility.set_location(l_proc,50);
672 --
673 l_value := p_value;
674 --
675 -- Check that the value is of the correct
676 -- type for the entitlement item
677 --
678 per_pcl_bus.check_for_correct_type
679 (p_cagr_entitlement_item_id => p_cagr_entitlement_item_id
680 ,p_value => l_value
681 ,p_business_group_id => 0
682 ,p_effective_date => p_effective_date);
683 --
684 -- The entitlement item has a value set stored against it,
685 -- so ensure that the value field (which will contain an
686 -- id) contents are valid for the value set.
687 --
688 ELSIF l_flex_value_set_id IS NOT NULL THEN
689 --
690 hr_utility.set_location(l_proc,60);
691 --
692 -- Fetch the value description from the value set
693 -- for the value content (which will be a fk).
694 --
695 l_value_desc := per_cagr_utility_pkg.get_name_from_value_set
696 (p_cagr_entitlement_id => p_cagr_entitlement_id
697 ,p_value => p_value);
698 --
702 -- the value field is invalid, so raise an error.
699 hr_utility.set_location(l_proc,70);
700 --
701 -- If the value_desc is null then this means that
703 --
704 IF l_value_desc IS NULL THEN
705 --
706 hr_utility.set_message(800, 'HR_289283_VALUE_INVALID');
707 hr_utility.raise_error;
708 --
709 END IF;
710 --
711 END IF;
712 --
713 END IF;
714 --
715 END IF;
716 --
717 hr_utility.set_location('Leaving: '||l_proc,999);
718 --
719 END chk_value;
720 --
721 -- ---------------------------------------------------------------------------
722 -- |---------------------------< chk_range_from >------------------------------|
723 -- ---------------------------------------------------------------------------
724 --
725 -- Desciption :
726 --
727 -- Validate that value stored in the range_to field is correct. This is
728 -- achieved by checking the following:
729 --
730 -- 1). If the entitlement item has been defined with a value set
731 -- then the range_from column will contain an ID. Therefore we must
732 -- check that this ID exists as the primary key to the table
733 -- defined in the value set.
734 --
735 -- 2). Enusre that the value in the range_from column matches the type
736 -- defined for the entitlement item. For example if the entitlement
737 -- item has been defined as a NUMBER then the range_from column
738 -- must be a number.
739 --
740 -- Pre-conditions :
741 --
742 --
743 -- In Arguments :
744 -- p_range_from
745 -- p_cagr_entitlement_item_id
746 --
747 -- Post Success :
748 -- Processing continues
749 --
750 -- Post Failure :
751 -- An application error will be raised and processing is
752 -- terminated
753 --
754 -- Access Status :
755 -- Internal Table Handler Use only.
756 --
757 -- {End of Comments}
758 --
759 -- ---------------------------------------------------------------------------
760 --
761 PROCEDURE chk_range_from
762 (p_range_from IN per_cagr_entitlement_lines_f.range_from%TYPE
763 ,p_cagr_entitlement_line_id IN per_cagr_entitlement_lines_f.cagr_entitlement_line_id%TYPE
764 ,p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
765 ,p_category_name IN VARCHAR2
766 ,p_effective_date IN DATE) IS
767 --
768 -- Declare Local Variables
769 --
770 l_proc VARCHAR2(72) := g_package || 'chk_range_from';
771 l_flex_value_set_id per_cagr_entitlement_items.flex_value_set_id%TYPE;
772 l_range_from_desc VARCHAR2(2000);
773 l_range_from per_cagr_entitlement_lines_f.range_to%TYPE;
774 l_non_value_category BOOLEAN;
775 l_formatted_value VARCHAR2(2000);
776 --
777 BEGIN
778 --
779 hr_utility.set_location('Entering:'||l_proc, 10);
780 --
781 -- Check mandatory parameters has been set
782 --
783 hr_api.mandatory_arg_error
784 (p_api_name => l_proc
785 ,p_argument => 'CAGR_ENTITLEMENT_ITEM_ID'
786 ,p_argument_value => p_cagr_entitlement_item_id);
787 --
788 -- Function to see if the range_from field is supported
789 -- by the category name passed in.
790 --
791 l_non_value_category := per_pcl_shd.non_value_category
792 (p_category_name => p_category_name);
793 --
794 -- If the range_from field has been populated for a category
795 -- that does not use the range_from field then raise an error.
796 --
797 IF p_range_from IS NOT NULL AND l_non_value_category THEN
798 --
799 hr_utility.set_message(800, 'HR_289347_RANGE_FROM_POPULATED');
800 hr_utility.raise_error;
801 --
802 -- Only validate if the range_from is populated and
803 -- the category is not a category for which the range_from
804 -- should not be populated (eg. Process).
805 --
806 ELSIF p_range_from IS NOT NULL AND NOT l_non_value_category THEN
807 --
808 hr_utility.set_location(l_proc, 20);
809 --
810 -- Only proceed with validation if :
811 -- a) Inserting or
812 -- b) The value for range_from has changed
813 --
814 IF ( (p_cagr_entitlement_line_id IS NULL) OR
815 ((p_cagr_entitlement_line_id IS NOT NULL) AND
816 (per_pcl_shd.g_old_rec.range_from <> p_range_from)))THEN
817 --
818 hr_utility.set_location(l_proc,30);
819 --
820 -- Fetch the flex_value_set_id if one has
821 -- been set for the entitlement item
822 --
823 l_flex_value_set_id := per_pcl_shd.retrieve_value_set_id
824 (p_cagr_entitlement_item_id => p_cagr_entitlement_item_id);
825 --
826 hr_utility.set_location(l_proc, 40);
827 --
828 -- If the entitlement item does not have
829 -- a value set stored against it then
830 -- just check that the value is of the
831 -- correct type for the entitlement item
832 --
833 IF l_flex_value_set_id IS NULL THEN
834 --
835 hr_utility.set_location(l_proc,50);
836 --
840 -- type for the entitlement item
837 l_range_from := p_range_from;
838 --
839 -- Check that the value is of the correct
841 --
842 per_pcl_bus.check_for_correct_type
843 (p_cagr_entitlement_item_id => p_cagr_entitlement_item_id
844 ,p_value => l_range_from
845 ,p_business_group_id => 0
846 ,p_effective_date => p_effective_date);
847 --
848 -- The entitlement item has a value set stored against it,
849 -- so ensure that the value field (which will contain an
850 -- id) contents are valid for the value set.
851 --
852 ELSIF l_flex_value_set_id IS NOT NULL THEN
853 --
854 hr_utility.set_location(l_proc,60);
855 --
856 -- Fetch the value description from the value set
857 -- for the value content (which will be a fk).
858 --
859 l_range_from_desc := per_cagr_utility_pkg.get_name_from_value_set
860 (p_cagr_entitlement_id => p_cagr_entitlement_item_id
861 ,p_value => p_range_from);
862 --
863 hr_utility.set_location(l_proc,70);
864 --
865 -- If the value_desc is null then this means that
866 -- the value field is invalid, so raise an error.
867 --
868 IF l_range_from_desc IS NULL THEN
869 --
870 hr_utility.set_message(800, 'HR_289284_RANGE_FROM_INVALID');
871 hr_utility.raise_error;
872 --
873 END IF;
874 --
875 END IF;
876 --
877 END IF;
878 --
879 END IF;
880 --
881 hr_utility.set_location('Leaving: '||l_proc,999);
882 --
883 END chk_range_from;
884 --
885 -- ---------------------------------------------------------------------------
886 -- |---------------------------< chk_range_to >-------------------------------|
887 -- ---------------------------------------------------------------------------
888 --
889 -- Desciption :
890 --
891 -- Validate that value stored in the range_to field is correct. This is
892 -- achieved by checking the following:
893 --
894 -- 1). If the entitlement item has been defined with a value set
895 -- then the range_to column will contain an ID. Therefore we must
896 -- check that this ID exists as the primary key to the table
897 -- defined in the value set.
898 --
899 -- 2). Enusre that the value in the range_to column matches the type
900 -- defined for the entitlement item. For example if the entitlement
901 -- item has been defined as a NUMBER then the range_to column
902 -- must be a number.
903 --
904 -- Pre-conditions :
905 --
906 --
907 -- In Arguments :
908 -- p_range_to
909 -- p_cagr_entitlement_item_id
910 --
911 -- Post Success :
912 -- Processing continues
913 --
914 -- Post Failure :
915 -- An application error will be raised and processing is
916 -- terminated
917 --
918 -- Access Status :
919 -- Internal Table Handler Use only.
920 --
921 -- {End of Comments}
922 --
923 -- ---------------------------------------------------------------------------
924 --
925 PROCEDURE chk_range_to
926 (p_range_to IN per_cagr_entitlement_lines_f.range_to%TYPE
927 ,p_cagr_entitlement_line_id IN per_cagr_entitlement_lines_f.cagr_entitlement_line_id%TYPE
928 ,p_cagr_entitlement_item_id IN per_cagr_entitlement_items.cagr_entitlement_item_id%TYPE
929 ,p_category_name IN VARCHAR2
930 ,p_effective_date IN DATE) IS
931 --
932 -- Declare Local Variables
933 --
934 l_proc VARCHAR2(72) := g_package || 'chk_range_to';
935 l_flex_value_set_id per_cagr_entitlement_items.flex_value_set_id%TYPE;
936 l_range_to_desc VARCHAR2(2000);
937 l_range_to per_cagr_entitlement_lines_f.range_to%TYPE;
938 l_non_value_category BOOLEAN;
939 l_formatted_value VARCHAR2(2000);
940 --
941 BEGIN
942 --
943 hr_utility.set_location('Entering:'||l_proc, 10);
944 --
945 -- Check mandatory parameters has been set
946 --
947 hr_api.mandatory_arg_error
948 (p_api_name => l_proc
949 ,p_argument => 'CAGR_ENTITLEMENT_ITEM_ID'
950 ,p_argument_value => p_cagr_entitlement_item_id);
951 --
952 -- Function to see if the range_to field is supported
953 -- by the category name passed in.
954 --
955 l_non_value_category := per_pcl_shd.non_value_category
956 (p_category_name => p_category_name);
957 --
958 -- If the range_to field has been populated for a category
959 -- that does not use the range_to field then raise an error.
960 --
961 IF p_range_to IS NOT NULL AND l_non_value_category THEN
962 --
963 hr_utility.set_message(800, 'HR_289348_RANGE_TO_POPULATED');
964 hr_utility.raise_error;
965 --
966 -- Only validate if the range_to is populated and
967 -- the category is not a category for which the range_to
968 -- should not be populated (eg. Process).
969 --
970 ELSIF p_range_to IS NOT NULL AND NOT l_non_value_category THEN
971 --
975 --
972 hr_utility.set_location(l_proc, 20);
973 --
974 hr_utility.set_location(l_proc, 20);
976 -- Only proceed with validation if :
977 -- a) Inserting or
978 -- b) The value for range_to has changed
979 --
980 IF ( (p_cagr_entitlement_line_id IS NULL) OR
981 ((p_cagr_entitlement_line_id IS NOT NULL) AND
982 (per_pcl_shd.g_old_rec.range_to <> p_range_to))) THEN
983 --
984 hr_utility.set_location(l_proc,30);
985 --
986 -- Fetch the flex_value_set_id if one has
987 -- been set for the entitlement item
988 --
989 l_flex_value_set_id := per_pcl_shd.retrieve_value_set_id
990 (p_cagr_entitlement_item_id => p_cagr_entitlement_item_id);
991 --
992 hr_utility.set_location(l_proc, 40);
993 --
994 -- If the entitlement item does not have
995 -- a value set stored against it then
996 -- just check that the value is of the
997 -- correct type for the entitlement item
998 --
999 IF l_flex_value_set_id IS NULL THEN
1000 --
1001 hr_utility.set_location(l_proc,50);
1002 --
1003 l_range_to := p_range_to;
1004 --
1005 -- Check that the value is of the correct
1006 -- type for the entitlement item
1007 --
1008 per_pcl_bus.check_for_correct_type
1009 (p_cagr_entitlement_item_id => p_cagr_entitlement_item_id
1010 ,p_value => l_range_to
1011 ,p_business_group_id => 0
1012 ,p_effective_date => p_effective_date);
1013 --
1014 -- The entitlement item has a value set stored against it,
1015 -- so ensure that the value field (which will contain an
1016 -- id) contents are valid for the value set.
1017 --
1018 ELSIF l_flex_value_set_id IS NOT NULL THEN
1019 --
1020 hr_utility.set_location(l_proc,60);
1021 --
1022 -- Fetch the value description from the value set
1023 -- for the value content (which will be a fk).
1024 --
1025 l_range_to_desc := per_cagr_utility_pkg.get_name_from_value_set
1026 (p_cagr_entitlement_id => p_cagr_entitlement_item_id
1027 ,p_value => p_range_to);
1028 --
1029 hr_utility.set_location(l_proc,70);
1030 --
1031 -- If the value_desc is null then this means that
1032 -- the value field is invalid, so raise an error.
1033 --
1034 IF l_range_to_desc IS NULL THEN
1035 --
1036 hr_utility.set_message(800, 'HR_289285_RANGE_TO_INVALID');
1037 hr_utility.raise_error;
1038 --
1039 END IF;
1040 --
1041 END IF;
1042 --
1043 END IF;
1044 --
1045 END IF;
1046 --
1047 hr_utility.set_location('Leaving: '||l_proc,999);
1048 --
1049 END chk_range_to;
1050 --
1051 -- ---------------------------------------------------------------------------
1052 -- |---------------------------< chk_grade_spine_id >------------------------|
1053 -- ---------------------------------------------------------------------------
1054 --
1055 -- Desciption :
1056 --
1057 -- Validate that on insert and update grade_spine_id,parent_spine_id,step_id
1058 -- are not null WHEN category is pay scale.
1059 -- grade_spine_id should be refrenced from per_grade_spines_f.
1060 --
1061 --
1062 -- Pre-conditions :
1063 --
1064 --
1065 -- In Arguments :
1066 -- p_grade_spine_id
1067 -- p_effective_date
1068 -- p_cagr_entitlement_line_id
1069 -- p_cagr_entitlement_id
1070 -- p_validation_start_date
1071 -- p_validation_end_date
1072 --
1073 -- Post Success :
1074 -- Processing continues
1075 --
1076 -- Post Failure :
1077 -- An application error will be raised and processing is
1078 -- terminated
1079 --
1080 -- Access Status :
1081 -- Internal Table Handler Use only.
1082 --
1083 -- {End of Comments}
1084 --
1085 -- ---------------------------------------------------------------------------
1086 --
1087 PROCEDURE chk_grade_spine_id
1088 ( p_grade_spine_id IN per_cagr_entitlement_lines_f.grade_spine_id%TYPE
1089 ,p_cagr_entitlement_line_id IN per_cagr_entitlement_lines_f.cagr_entitlement_line_id%TYPE
1090 ,p_cagr_entitlement_id IN per_cagr_entitlement_lines_f.cagr_entitlement_id%TYPE
1091 ,p_effective_date IN DATE
1092 ,p_validation_start_date IN DATE
1093 ,p_validation_end_date IN DATE) IS
1094
1095 CURSOR csr_chk_category IS
1096 SELECT category_name
1097 FROM per_cagr_entitlement_items pci
1098 ,per_cagr_entitlements pce
1099 WHERE pci.CAGR_ENTITLEMENT_ITEM_ID = pce.CAGR_ENTITLEMENT_ITEM_ID
1100 AND pce.CAGR_ENTITLEMENT_ID = p_CAGR_ENTITLEMENT_ID;
1101
1102
1103 CURSOR csr_chk_grd_fk_start IS
1104 SELECT 'Y'
1105 FROM per_grade_spines_f pgs
1106 WHERE pgs.grade_spine_id = p_grade_spine_id
1107 AND p_validation_start_date between pgs.effective_start_date and pgs.effective_end_date ;
1108
1109
1110 CURSOR csr_chk_grd_fk_end IS
1111 SELECT 'Y'
1112 FROM per_grade_spines_f pgs
1113 WHERE pgs.grade_spine_id = p_grade_spine_id
1117 --
1114 AND p_validation_end_date between pgs.effective_start_date and pgs.effective_end_date ;
1115
1116
1118 l_proc VARCHAR2(72) := g_package || 'chk_grade_spine_id';
1119 l_cat per_cagr_entitlement_items.category_name%TYPE;
1120 l_grd VARCHAR2(1);
1121 --
1122
1123 BEGIN
1124 --
1125 hr_utility.set_location('Entering:'||l_proc, 10);
1126 --
1127 -- Check category is pay scales
1128
1129 OPEN csr_chk_category;
1130 FETCH csr_chk_category INTO l_cat;
1131
1132 hr_utility.set_location(l_proc,20);
1133
1134 IF (l_cat = 'PYS') then
1135
1136 -- Check if parent_spine_id is not null
1137
1138 hr_api.mandatory_arg_error
1139 (p_api_name => l_proc
1140 ,p_argument => 'grade_spine_id'
1141 ,p_argument_value => p_grade_spine_id);
1142
1143 hr_utility.set_location('Leaving:'||l_proc, 30);
1144
1145 -- check while inserting or updating grade_spine_id
1146
1147 IF ((p_cagr_entitlement_line_id IS NULL) OR
1148 ((p_cagr_entitlement_line_id IS NOT NULL) AND
1149 (per_pcl_shd.g_old_rec.grade_spine_id <> p_grade_spine_id))) THEN
1150
1151 hr_utility.set_location('Leaving:'||l_proc, 40);
1152
1153 OPEN csr_chk_grd_fk_start;
1154 FETCH csr_chk_grd_fk_start INTO l_grd;
1155
1156 IF csr_chk_grd_fk_start%NOTFOUND THEN
1157 hr_utility.set_location(l_proc,50);
1158 CLOSE csr_chk_grd_fk_start;
1159 hr_utility.set_message(800, 'HR_289287_GRD_EFFECTIVE_DT_INV');
1160 hr_utility.raise_error;
1161 END IF;
1162
1163 CLOSE csr_chk_grd_fk_start;
1164 hr_utility.set_location(l_proc,60);
1165
1166 OPEN csr_chk_grd_fk_end;
1167 FETCH csr_chk_grd_fk_end INTO l_grd;
1168
1169 IF csr_chk_grd_fk_end%NOTFOUND THEN
1170 hr_utility.set_location(l_proc,70);
1171 CLOSE csr_chk_grd_fk_end;
1172 hr_utility.set_message(800, 'HR_289287_GRD_EFFECTIVE_DT_INV');
1173 hr_utility.raise_error;
1174 END IF;
1175
1176 CLOSE csr_chk_grd_fk_end;
1177 hr_utility.set_location(l_proc,80);
1178
1179 END IF;
1180
1181
1182
1183
1184 END IF;
1185
1186
1187 CLOSE csr_chk_category;
1188 hr_utility.set_location(l_proc,100);
1189 --
1190
1191 END chk_grade_spine_id;
1192
1193
1194
1195 --
1196 -- ---------------------------------------------------------------------------
1197 -- |---------------------------< chk_parent_spine_id >------------------------|
1198 -- ---------------------------------------------------------------------------
1199 --
1200 -- Desciption :
1201 --
1202 -- Validate parent_spine_id is refrenced from per_parent_spines_f.
1203 --
1204 --
1205 -- Pre-conditions :
1206 --
1207 --
1208 -- In Arguments :
1209 -- p_parent_spine_id
1210 -- p_cage_entitlement_line_id
1211 --
1212 -- Post Success :
1213 -- Processing continues
1214 --
1215 -- Post Failure :
1216 -- An application error will be raised and processing is
1217 -- terminated
1218 --
1219 -- Access Status :
1220 -- Internal Table Handler Use only.
1221 --
1222 -- {End of Comments}
1223 --
1224 -- ---------------------------------------------------------------------------
1225 --
1226 PROCEDURE chk_parent_spine_id
1227 ( p_parent_spine_id IN per_cagr_entitlement_lines_f.parent_spine_id%TYPE
1228 ,p_cagr_entitlement_line_id IN
1229 per_cagr_entitlement_lines_f.cagr_entitlement_line_id%TYPE
1230 ,p_cagr_entitlement_id IN per_cagr_entitlement_lines_f.cagr_entitlement_id%TYPE ) IS
1231 --
1232
1233 CURSOR csr_chk_category IS
1234 SELECT category_name
1235 FROM per_cagr_entitlement_items pci
1236 ,per_cagr_entitlements pce
1237 WHERE pci.CAGR_ENTITLEMENT_ITEM_ID = pce.CAGR_ENTITLEMENT_ITEM_ID
1238 AND pce.CAGR_ENTITLEMENT_ID = p_CAGR_ENTITLEMENT_ID;
1239
1240 --
1241 CURSOR csr_chk_parent_spine_fk IS
1242 SELECT 'Y'
1243 FROM per_parent_spines pps
1244 WHERE pps.parent_spine_id = p_parent_spine_id;
1245 --
1246 l_proc VARCHAR2(72) := g_package || 'chk_parent_spine_id';
1247 l_dummy VARCHAR2(1);
1248 l_cat per_cagr_entitlement_items.category_name%TYPE;
1249 --
1250 BEGIN
1251 --
1252 hr_utility.set_location('Entering:'||l_proc, 10);
1253
1254 -- Check category is pay scales
1255
1256 OPEN csr_chk_category;
1257 FETCH csr_chk_category INTO l_cat;
1258 --
1259 hr_utility.set_location(l_proc,20);
1260
1261 IF (l_cat = 'PYS') then
1262
1263 -- Check if parent_spine_id is not null
1264
1265 hr_api.mandatory_arg_error
1266 (p_api_name => l_proc
1267 ,p_argument => 'parent_spine_id'
1268 ,p_argument_value => p_parent_spine_id);
1269
1270 hr_utility.set_location('Leaving:'||l_proc, 30);
1271
1272 --
1273 -- check while inserting or updating parent_spine_id
1274 --
1275
1279
1276 IF ((p_cagr_entitlement_line_id IS NULL) OR
1277 ((p_cagr_entitlement_line_id IS NOT NULL) AND
1278 (per_pcl_shd.g_old_rec.parent_spine_id <> p_parent_spine_id))) THEN
1280
1281 hr_utility.set_location(l_proc,40);
1282 OPEN csr_chk_parent_spine_fk;
1283 FETCH csr_chk_parent_spine_fk INTO l_dummy;
1284 hr_utility.set_location(l_proc,50);
1285
1286 IF csr_chk_parent_spine_fk%NOTFOUND THEN
1287
1288 hr_utility.set_location(l_proc,60);
1289 CLOSE csr_chk_parent_spine_fk;
1290 hr_utility.set_message(800, 'HR_289286_PARENT_SPINE_INVALID');
1291 hr_utility.raise_error;
1292
1293 END IF;
1294
1295 CLOSE csr_chk_parent_spine_fk;
1296 hr_utility.set_location(l_proc,70);
1297
1298 END IF;
1299
1300 END IF;
1301 --
1302 hr_utility.set_location('Leaving: '||l_proc,100);
1303 --
1304 END chk_parent_spine_id;
1305
1306
1307
1308 --
1309 -- ---------------------------------------------------------------------------
1310 -- |---------------------------< chk_status >--------------------------------|
1311 -- ---------------------------------------------------------------------------
1312 --
1313 -- Desciption :
1314 --
1315 -- Validate that on insert status is not null and that
1316 -- it is validated against hr_lookups.
1317 --
1318 --
1319 -- Pre-conditions :
1320 --
1321 --
1322 -- In Arguments :
1323 -- p_status
1324 -- p_effective_date
1325 -- p_cagr_entitlement_line_id
1326 --
1327 -- Post Success :
1328 -- Processing continues
1329 --
1330 -- Post Failure :
1331 -- An application error will be raised and processing is
1332 -- terminated
1333 --
1334 -- Access Status :
1335 -- Internal Table Handler Use only.
1336 --
1337 -- {End of Comments}
1338 --
1339 -- ---------------------------------------------------------------------------
1340 --
1341 PROCEDURE chk_status
1342 (p_status IN per_cagr_entitlement_lines_f.status%TYPE
1343 ,p_cagr_entitlement_line_id IN per_cagr_entitlement_lines_f.cagr_entitlement_line_id%TYPE
1344 ,p_effective_date IN DATE
1345 ,p_validation_start_date IN DATE
1346 ,p_validation_end_date IN DATE) IS
1347
1348 --
1349 l_proc VARCHAR2(72) := g_package || 'chk_status';
1350 --
1351
1352 BEGIN
1353 --
1354 hr_utility.set_location('Entering:'||l_proc, 10);
1355
1356 IF p_status is not null THEN
1357
1358 --
1359 -- Only proceed with validation if :
1360 -- a) Inserting or
1361 -- b) The value for status has changed
1362 --
1363 hr_utility.set_location(l_proc, 20);
1364 IF ((p_cagr_entitlement_line_id IS NULL) OR
1365 ((p_cagr_entitlement_line_id IS NOT NULL) AND
1366 (per_pcl_shd.g_old_rec.status <> p_status))) THEN
1367
1368 hr_utility.set_location(l_proc, 30);
1369 --
1370 -- Check that the type exists in HR_LOOKUPS
1371 --
1372
1373 IF hr_api.not_exists_in_dt_hr_lookups
1374 (p_effective_date => p_effective_date
1375 ,p_lookup_type => 'CAGR_STATUS'
1376 ,p_lookup_code => p_status
1377 ,p_validation_start_date => p_validation_start_date
1378 ,p_validation_end_date => p_validation_end_date) THEN
1379
1380 hr_utility.set_location(l_proc, 40);
1381 hr_utility.set_message(800, 'HR_289267_STATUS_INVALID');
1382 hr_utility.raise_error;
1383
1384 END IF;
1385
1386 END IF;
1387
1388 ELSE
1389
1390 hr_utility.set_location('Leaving '||l_proc, 50);
1391 hr_utility.set_message(800, 'HR_289267_STATUS_INVALID');
1392 hr_utility.raise_error;
1393
1394 END IF;
1395
1396
1397 END chk_status;
1398 --
1399 -- ---------------------------------------------------------------------------
1400 -- |---------------------------< chk_oipl_id >-------------------------------|
1401 -- ---------------------------------------------------------------------------
1402 --
1403 -- Desciption :
1404 --
1405 -- Validate that oipl_id is refrenced from ben_oipl_f.
1406 --
1407 -- Pre-conditions :
1408 --
1409 --
1410 -- In Arguments :
1411 -- p_oipl_id
1412 -- p_cagr_entitlement_line_id
1413 -- p_effective_date
1414 -- p_validation_start_date
1415 -- p_validation_end_date
1416 --
1417 -- Post Success :
1418 -- Processing continues
1419 --
1420 -- Post Failure :
1421 -- An application error will be raised and processing is
1422 -- terminated
1423 --
1424 -- Access Status :
1425 -- Internal Table Handler Use only.
1426 --
1427 -- {End of Comments}
1428 --
1429 -- ---------------------------------------------------------------------------
1430 --
1431 PROCEDURE chk_oipl_id
1432 ( p_oipl_id IN per_cagr_entitlement_lines_f.oipl_id%TYPE
1436 ,p_validation_end_date IN DATE) IS
1433 ,p_cagr_entitlement_line_id IN NUMBER
1434 ,p_effective_date IN DATE
1435 ,p_validation_start_date IN DATE
1437 --
1438 -- Delcare Cursors
1439 --
1440 CURSOR csr_chk_oipl_fk_start IS
1441 SELECT 'Y'
1442 FROM ben_oipl_f bof
1443 WHERE bof.oipl_id = p_oipl_id
1444 AND p_validation_start_date
1445 BETWEEN bof.effective_start_date AND bof.effective_end_date ;
1446 --
1447 CURSOR csr_chk_oipl_fk_end IS
1448 SELECT 'Y'
1449 FROM ben_oipl_f bof
1450 WHERE bof.oipl_id = p_oipl_id
1451 AND p_validation_end_date
1452 BETWEEN bof.effective_start_date AND bof.effective_end_date;
1453 --
1454 -- Delcare Local Variables
1455 --
1456 l_proc VARCHAR2(72) := g_package || 'chk_oipl_id';
1457 l_dummy VARCHAR2(1);
1458 --
1459
1460 BEGIN
1461 --
1462 hr_utility.set_location('Entering:'||l_proc, 10);
1463 --
1464 -- If the oipl_id has not been set to the default
1465 -- value (0) then continue with the validation
1466 --
1467 IF p_oipl_id <> 0 THEN
1468 --
1469 -- Only proceed with validation if :
1470 -- a) Inserting or
1471 -- b) The value for oipl_id has changed
1472 --
1473 IF ( (p_cagr_entitlement_line_id IS NULL) OR
1474 ((p_cagr_entitlement_line_id IS NOT NULL) AND
1475 (per_pcl_shd.g_old_rec.oipl_id <> p_oipl_id))) THEN
1476
1477 hr_utility.set_location('Leaving:'||l_proc, 20);
1478 OPEN csr_chk_oipl_fk_start;
1479 FETCH csr_chk_oipl_fk_start INTO l_dummy;
1480
1481 IF csr_chk_oipl_fk_start%NOTFOUND THEN
1482
1483 hr_utility.set_location(l_proc,30);
1484 CLOSE csr_chk_oipl_fk_start;
1485 hr_utility.set_message(800, 'HR_289288_OIPL_ID_DT_INVALID');
1486 hr_utility.raise_error;
1487
1488 END IF;
1489
1490 CLOSE csr_chk_oipl_fk_start;
1491 hr_utility.set_location(l_proc,40);
1492
1493 OPEN csr_chk_oipl_fk_end;
1494 FETCH csr_chk_oipl_fk_end INTO l_dummy;
1495
1496 IF csr_chk_oipl_fk_end%NOTFOUND THEN
1497
1498 hr_utility.set_location(l_proc,50);
1499 CLOSE csr_chk_oipl_fk_end;
1500 hr_utility.set_message(800, 'HR_289288_OIPL_ID_DT_INVALID');
1501 hr_utility.raise_error;
1502
1503 END IF;
1504
1505 CLOSE csr_chk_oipl_fk_end;
1506 hr_utility.set_location(l_proc,60);
1507
1508 END IF;
1509
1510 END IF;
1511 --
1512 hr_utility.set_location('Leaving: '||l_proc,100);
1513 --
1514 END chk_oipl_id;
1515
1516 --
1517 -- ---------------------------------------------------------------------------
1518 -- |---------------------------< chk_eligy_prfl_id >-------------------------|
1519 -- ---------------------------------------------------------------------------
1520 --
1521 -- Desciption :
1522 --
1523 -- Validates that the eligy_prfl_id exists in ben_eligy_prfl_f. This
1524 -- procedure also ensures that the eligibility profile has only been
1525 -- used once for the entitlement.
1526 --
1527 -- Pre-conditions :
1528 --
1529 --
1530 -- In Arguments :
1531 -- p_eligy_prfl_id
1532 -- p_cagr_entitlement_line_id
1533 -- p_cagr_entilement_id
1534 -- p_grade_spine_id
1535 -- p_category_name
1536 -- p_formula_criteria
1537 -- p_business_group_id
1538 -- p_effective_date
1539 --
1540 -- Post Success :
1541 -- Processing continues
1542 --
1543 -- Post Failure :
1544 -- An application error will be raised and processing is
1545 -- terminated
1546 --
1547 -- Access Status :
1548 -- Internal Table Handler Use only.
1549 --
1550 -- {End of Comments}
1551 --
1552 -- ---------------------------------------------------------------------------
1553 --
1554 PROCEDURE chk_eligy_prfl_id
1555 (p_eligy_prfl_id IN per_cagr_entitlement_lines_f.eligy_prfl_id%TYPE
1556 ,p_grade_spine_id IN NUMBER
1557 ,p_category_name IN VARCHAR2
1558 ,p_cagr_entitlement_line_id IN NUMBER
1559 ,p_cagr_entitlement_id IN per_cagr_entitlement_lines_f.cagr_entitlement_id%TYPE
1560 ,p_business_group_id IN NUMBER
1561 ,p_formula_criteria IN VARCHAR2
1562 ,p_effective_date IN DATE) IS
1563 --
1564 -- Delcare Cursors
1565 --
1566 CURSOR csr_chk_id IS
1567 SELECT bep.eligy_prfl_id
1568 FROM ben_eligy_prfl_f bep
1569 WHERE business_group_id = p_business_group_id
1570 AND bep.eligy_prfl_id = p_eligy_prfl_id
1571 AND p_effective_date BETWEEN bep.effective_start_date AND bep.effective_end_date
1572 AND bep.bnft_cagr_prtn_cd IN ('GLOBAL','CAGR')
1573 --
1574 -- Fix for bug 2491566
1575 --
1576 --AND NOT EXISTS (SELECT 'X'
1577 -- FROM per_cagr_entitlement_lines_f pcl
1578 -- WHERE pcl.eligy_prfl_id = bep.eligy_prfl_id
1579 -- AND pcl.cagr_entitlement_id = p_cagr_entitlement_id
1580 -- AND p_effective_date BETWEEN pcl.effective_start_date AND
1584 FROM DUAL;
1581 -- pcl.effective_end_date)
1582 UNION
1583 SELECT 0
1585 --
1586 -- Fix for bug 2491566
1587 --
1588 --WHERE NOT EXISTS (SELECT 'X'
1589 -- FROM per_cagr_entitlement_lines_f pcl
1590 -- WHERE pcl.eligy_prfl_id = 0
1591 -- AND pcl.cagr_entitlement_id = p_cagr_entitlement_id
1592 -- AND p_effective_date BETWEEN pcl.effective_start_date AND
1593 -- pcl.effective_end_date);
1594 --
1595 CURSOR csr_chk_payscale IS
1596 SELECT bep.eligy_prfl_id
1597 FROM ben_eligy_prfl_f bep
1598 WHERE business_group_id = p_business_group_id
1599 AND bep.eligy_prfl_id = p_eligy_prfl_id
1600 AND p_effective_date BETWEEN bep.effective_start_date
1601 AND bep.effective_end_date
1602 --
1603 -- Fix for bug 2491566
1604 --
1605 --AND NOT EXISTS (SELECT 'X'
1606 -- FROM per_cagr_entitlement_lines_f pcl
1607 -- WHERE pcl.eligy_prfl_id = bep.eligy_prfl_id
1608 -- AND pcl.grade_spine_id = p_grade_spine_id
1609 -- AND pcl.cagr_entitlement_id = p_cagr_entitlement_id
1610 -- AND p_effective_date BETWEEN pcl.effective_start_date
1611 -- AND pcl.effective_end_date)
1612 UNION
1613 SELECT 0
1614 FROM DUAL;
1615 --
1616 -- Fix for bug 2491566
1617 --
1618 --WHERE NOT EXISTS (SELECT 'X'
1619 -- FROM per_cagr_entitlement_lines_f pcl
1620 -- WHERE pcl.eligy_prfl_id = 0
1621 -- AND pcl.grade_spine_id = p_grade_spine_id
1622 -- AND pcl.cagr_entitlement_id = p_cagr_entitlement_id
1623 -- AND p_effective_date BETWEEN pcl.effective_start_date
1624 -- AND pcl.effective_end_date);
1625 --
1626 -- Delcare Local Variables
1627 --
1628 l_proc VARCHAR2(72) := g_package || 'chk_eligy_prfl_id';
1629 l_eligy_prfl_id per_cagr_entitlement_lines_f.oipl_id%TYPE;
1630 --
1631 BEGIN
1632 --
1633 hr_utility.set_location('Entering:'||l_proc, 10);
1634 --
1635 -- Check mandatory parameters has been set
1636 --
1637 hr_api.mandatory_arg_error
1638 (p_api_name => l_proc
1639 ,p_argument => 'ELIGY_PRFL_ID'
1640 ,p_argument_value => p_eligy_prfl_id);
1641 --
1642 hr_api.mandatory_arg_error
1643 (p_api_name => l_proc
1644 ,p_argument => 'CATEGORY_NAME'
1645 ,p_argument_value => p_category_name);
1646 --
1647 -- Only proceed with validation if :
1648 -- a) Inserting or
1649 -- b) The value for eligy_prfl_id has changed
1650 --
1651 IF ( (p_cagr_entitlement_line_id IS NULL) OR
1652 ((p_cagr_entitlement_line_id IS NOT NULL) AND
1653 ((per_pcl_shd.g_old_rec.eligy_prfl_id <> p_eligy_prfl_id) OR
1654 (per_pcl_shd.g_old_rec.grade_spine_id <> p_grade_spine_id))
1655 )
1656 ) THEN
1657 --
1658 hr_utility.set_location(l_proc, 20);
1659 --
1660 -- If the entitlement has been defined with a formula
1661 -- for calculating elibility and cagr values then
1662 -- raise an error.
1663 --
1664 IF p_formula_criteria = 'F' THEN
1665 --
1666 hr_utility.set_message(800, 'HR_289392_ELIG_FOR_FORMULA_ENT');
1667 hr_utility.raise_error;
1668 --
1669 END IF;
1670 --
1671 -- If the category is Payscale then check to see
1672 -- if the eligibility profile and grade combination
1673 -- are unique for this entitlement
1674 --
1675 IF p_category_name = 'PYS' THEN
1676 --
1677 hr_utility.set_location(l_proc, 30);
1678 --
1679 OPEN csr_chk_payscale;
1680 FETCH csr_chk_payscale INTO l_eligy_prfl_id;
1681 --
1682 IF csr_chk_payscale%NOTFOUND THEN
1683 --
1684 CLOSE csr_chk_payscale;
1685 --
1686 hr_utility.set_message(800, 'HR_289344_ELIGY_PRFL_ID_INV');
1687 hr_utility.raise_error;
1688 --
1689 ELSE
1690 --
1691 CLOSE csr_chk_payscale;
1692 --
1693 END IF;
1694 --
1695 -- If the category is not Payscale then check to see
1696 -- if the eligibility profile is unique for this entitlement
1697 --
1698 ELSIF p_category_name <> 'PYS' THEN
1699 --
1700 hr_utility.set_location(l_proc, 40);
1701 --
1702 -- Check that the elig_prfl_id exists and is unique
1703 -- for the collective agreement entitlement.
1704 --
1705 OPEN csr_chk_id;
1706 FETCH csr_chk_id INTO l_eligy_prfl_id;
1707 --
1708 IF csr_chk_id%NOTFOUND THEN
1709 --
1710 CLOSE csr_chk_id;
1711 --
1712 hr_utility.set_message(800, 'HR_289344_ELIGY_PRFL_ID_INV');
1713 hr_utility.raise_error;
1714 --
1715 ELSE
1716 --
1717 CLOSE csr_chk_id;
1718 --
1719 END IF;
1720 --
1721 END IF;
1722 --
1723 END IF;
1724 --
1725 hr_utility.set_location('Leaving: '||l_proc,999);
1726 --
1730 -- |---------------------------< chk_step_id >-------------------------------|
1727 END chk_eligy_prfl_id;
1728 --
1729 -- ---------------------------------------------------------------------------
1731 -- ---------------------------------------------------------------------------
1732 --
1733 -- Desciption :
1734 --
1735 -- Validate that step_id is refrenced from per_spinal_point_steps_f.
1736 --
1737 -- Pre-conditions :
1738 --
1739 --
1740 -- In Arguments :
1741 -- p_step_id
1742 -- p_cagr_entitlement_line_id
1743 -- p_effective_date
1744 -- p_validation_start_date
1745 -- p_validation_end_date
1746 --
1747 -- Post Success :
1748 -- Processing continues
1749 --
1750 -- Post Failure :
1751 -- An application error will be raised and processing is
1752 -- terminated
1753 --
1754 -- Access Status :
1755 -- Internal Table Handler Use only.
1756 --
1757 -- {End of Comments}
1758 --
1759 -- ---------------------------------------------------------------------------
1760 --
1761 PROCEDURE chk_step_id
1762 ( p_step_id IN per_cagr_entitlement_lines_f.step_id%TYPE
1763 ,p_cagr_entitlement_line_id IN NUMBER
1764 ,p_cagr_entitlement_id IN per_cagr_entitlement_lines_f.cagr_entitlement_id%TYPE
1765 ,p_effective_date IN DATE
1766 ,p_validation_start_date IN DATE
1767 ,p_validation_end_date IN DATE) IS
1768 --
1769 --Declare Cursors
1770 --
1771
1772 CURSOR csr_chk_category IS
1773 SELECT category_name
1774 FROM per_cagr_entitlement_items pci
1775 ,per_cagr_entitlements pce
1776 WHERE pci.CAGR_ENTITLEMENT_ITEM_ID = pce.CAGR_ENTITLEMENT_ITEM_ID
1777 AND pce.CAGR_ENTITLEMENT_ID = p_CAGR_ENTITLEMENT_ID;
1778
1779 CURSOR csr_chk_step_fk_start IS
1780 SELECT 'Y'
1781 FROM per_spinal_point_steps_f pspf
1782 WHERE pspf.step_id = p_step_id
1783 AND p_validation_start_date
1784 BETWEEN pspf.effective_start_date AND pspf.effective_end_date ;
1785 --
1786 CURSOR csr_chk_step_fk_end IS
1787 SELECT 'Y'
1788 FROM per_spinal_point_steps_f pspf
1789 WHERE pspf.step_id = p_step_id
1790 AND p_validation_end_date
1791 BETWEEN pspf.effective_start_date AND pspf.effective_end_date ;
1792 --
1793 -- Declare Local Variables
1794 --
1795 l_proc VARCHAR2(72) := g_package || 'chk_step_id';
1796 l_dummy VARCHAR2(1);
1797 l_cat per_cagr_entitlement_items.category_name%TYPE;
1798 --
1799 BEGIN
1800 --
1801 hr_utility.set_location('Entering:'||l_proc, 10);
1802
1803 -- Check category is pay scales
1804
1805 OPEN csr_chk_category;
1806 FETCH csr_chk_category INTO l_cat;
1807 --
1808 hr_utility.set_location(l_proc,20);
1809
1810 IF (l_cat = 'PYS') then
1811
1812 -- Check if step_id is not null
1813 hr_api.mandatory_arg_error
1814 (p_api_name => l_proc
1815 ,p_argument => 'step_id'
1816 ,p_argument_value => p_step_id);
1817
1818 hr_utility.set_location('Leaving:'||l_proc, 30);
1819 --
1820 -- Only proceed with validation if :
1821 -- a) Inserting or
1822 -- b) The value for step_id has changed
1823 --
1824
1825 IF ((p_cagr_entitlement_line_id IS NULL) OR
1826 ((p_cagr_entitlement_line_id IS NOT NULL) AND
1827 (per_pcl_shd.g_old_rec.step_id <> p_step_id))) THEN
1828
1829 hr_utility.set_location(l_proc, 40);
1830 OPEN csr_chk_step_fk_start;
1831 FETCH csr_chk_step_fk_start INTO l_dummy;
1832 hr_utility.set_location(l_proc, 50);
1833
1834 IF csr_chk_step_fk_start%NOTFOUND THEN
1835
1836 hr_utility.set_location(l_proc,60);
1837 CLOSE csr_chk_step_fk_start;
1838 hr_utility.set_message(800, 'HR_289289_STEP_ID_DT_INVALID');
1839 hr_utility.raise_error;
1840
1841 END IF;
1842
1843 CLOSE csr_chk_step_fk_start;
1844 hr_utility.set_location(l_proc,70);
1845 OPEN csr_chk_step_fk_end;
1846 FETCH csr_chk_step_fk_end INTO l_dummy;
1847 hr_utility.set_location(l_proc, 80);
1848
1849 IF csr_chk_step_fk_end%NOTFOUND THEN
1850
1851 hr_utility.set_location(l_proc,90);
1852 CLOSE csr_chk_step_fk_end;
1853 hr_utility.set_message(800, 'HR_289289_STEP_ID_DT_INVALID');
1854 hr_utility.raise_error;
1855
1856 END IF;
1857 --
1858 hr_utility.set_location(l_proc, 100);
1859 --
1860 CLOSE csr_chk_step_fk_end;
1861 --
1862 END IF;
1863 --
1864 END IF;
1865 --
1866 CLOSE csr_chk_category;
1867 hr_utility.set_location('LEaving: '||l_proc,100);
1868 --
1869 END chk_step_id;
1870
1871 --
1872 -- ---------------------------------------------------------------------------
1873 -- |----------------------------< chk_object_version_number >-----------------|
1874 -- ---------------------------------------------------------------------------
1875 --
1876 -- Desciption :
1877 --
1878 -- Checks that the OVN passed is not null on update and delete.
1879 --
1883 -- In Arguments :
1880 -- Pre-conditions :
1881 -- None.
1882 --
1884 -- p_object_version_number
1885 --
1886 -- Post Success :
1887 -- Processing continues
1888 --
1889 -- Post Failure :
1890 -- An application error will be raised and processing is
1891 -- terminated
1892 --
1893 -- Access Status :
1894 -- Internal Table Handler Use only.
1895 --
1896 -- {End of Comments}
1897 --
1898 -- ---------------------------------------------------------------------------
1899 procedure chk_object_version_number
1900 (
1901 p_object_version_number in per_cagr_entitlement_lines_f.object_version_number%TYPE
1902 ) is
1903 --
1904 l_proc varchar2(72) := g_package||'chk_object_version_number';
1905 --
1906 begin
1907 hr_utility.set_location('Entering:'||l_proc, 1);
1908 --
1909 -- Check mandatory parameters have been set
1910 --
1911 hr_api.mandatory_arg_error
1912 (p_api_name => l_proc
1913 ,p_argument => 'object_version_number'
1914 ,p_argument_value => p_object_version_number
1915 );
1916 --
1917 hr_utility.set_location(' Leaving:'||l_proc, 3);
1918 --
1919 end chk_object_version_number;
1920
1921
1922
1923 --
1924 -- ----------------------------------------------------------------------------
1925 -- |--------------------------< dt_update_validate >--------------------------|
1926 -- ----------------------------------------------------------------------------
1927 -- {Start Of Comments}
1928 --
1929 -- Description:
1930 -- This procedure is used for referential integrity of datetracked
1931 -- parent entities when a datetrack update operation is taking place
1932 -- and where there is no cascading of update defined for this entity.
1933 --
1934 -- Prerequisites:
1935 -- This procedure is called from the update_validate.
1936 --
1937 -- In Parameters:
1938 --
1939 -- Post Success:
1940 -- Processing continues.
1941 --
1942 -- Post Failure:
1943 --
1944 -- Developer Implementation Notes:
1945 -- This procedure should not need maintenance unless the HR Schema model
1946 -- changes.
1947 --
1948 -- Access Status:
1949 -- Internal Row Handler Use Only.
1950 --
1951 -- {End Of Comments}
1952 -- ----------------------------------------------------------------------------
1953 Procedure dt_update_validate
1954 (p_grade_spine_id in number default hr_api.g_number
1955 ,p_step_id in number default hr_api.g_number
1956 ,p_from_step_id in number default hr_api.g_number
1957 ,p_to_step_id in number default hr_api.g_number
1958 ,p_datetrack_mode in varchar2
1959 ,p_validation_start_date in date
1960 ,p_validation_end_date in date
1961 ) Is
1962 --
1963 l_proc varchar2(72) := g_package||'dt_update_validate';
1964 l_integrity_error Exception;
1965 l_table_name all_tables.table_name%TYPE;
1966 --
1967 Begin
1968 --
1969 -- Ensure that the p_datetrack_mode argument is not null
1970 --
1971 hr_api.mandatory_arg_error
1972 (p_api_name => l_proc
1973 ,p_argument => 'datetrack_mode'
1974 ,p_argument_value => p_datetrack_mode
1975 );
1976 --
1977 -- Mode will be valid, as this is checked at the start of the upd.
1978 --
1979 -- Ensure the arguments are not null
1980 --
1981 hr_api.mandatory_arg_error
1982 (p_api_name => l_proc
1983 ,p_argument => 'validation_start_date'
1984 ,p_argument_value => p_validation_start_date
1985 );
1986 --
1987 hr_api.mandatory_arg_error
1988 (p_api_name => l_proc
1989 ,p_argument => 'validation_end_date'
1990 ,p_argument_value => p_validation_end_date
1991 );
1992 --
1993 If ((nvl(p_grade_spine_id, hr_api.g_number) <> hr_api.g_number) and
1994 NOT (dt_api.check_min_max_dates
1995 (p_base_table_name => 'per_grade_spines_f'
1996 ,p_base_key_column => 'GRADE_SPINE_ID'
1997 ,p_base_key_value => p_grade_spine_id
1998 ,p_from_date => p_validation_start_date
1999 ,p_to_date => p_validation_end_date))) Then
2000 l_table_name := 'grade spines';
2001 raise l_integrity_error;
2002 End If;
2003 If ((nvl(p_step_id, hr_api.g_number) <> hr_api.g_number) and
2004 NOT (dt_api.check_min_max_dates
2005 (p_base_table_name => 'per_spinal_point_steps_f'
2006 ,p_base_key_column => 'STEP_ID'
2007 ,p_base_key_value => p_step_id
2008 ,p_from_date => p_validation_start_date
2009 ,p_to_date => p_validation_end_date))) Then
2010 l_table_name := 'spinal point steps';
2011 raise l_integrity_error;
2012 End If;
2013 If ((nvl(p_from_step_id, hr_api.g_number) <> hr_api.g_number) and
2014 NOT (dt_api.check_min_max_dates
2015 (p_base_table_name => 'per_spinal_point_steps_f'
2016 ,p_base_key_column => 'STEP_ID'
2017 ,p_base_key_value => p_from_step_id
2018 ,p_from_date => p_validation_start_date
2019 ,p_to_date => p_validation_end_date))) Then
2020 l_table_name := 'spinal point steps';
2024 NOT (dt_api.check_min_max_dates
2021 raise l_integrity_error;
2022 End If;
2023 If ((nvl(p_to_step_id, hr_api.g_number) <> hr_api.g_number) and
2025 (p_base_table_name => 'per_spinal_point_steps_f'
2026 ,p_base_key_column => 'STEP_ID'
2027 ,p_base_key_value => p_to_step_id
2028 ,p_from_date => p_validation_start_date
2029 ,p_to_date => p_validation_end_date))) Then
2030 l_table_name := 'spinal point steps';
2031 raise l_integrity_error;
2032 End If;
2033 --
2034 Exception
2035 When l_integrity_error Then
2036 --
2037 -- A referential integrity check was violated therefore
2038 -- we must error
2039 --
2040 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
2041 fnd_message.set_token('TABLE_NAME', l_table_name);
2042 fnd_message.raise_error;
2043 When Others Then
2044 --
2045 -- An unhandled or unexpected error has occurred which
2046 -- we must report
2047 --
2048 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2049 fnd_message.set_token('PROCEDURE', l_proc);
2050 fnd_message.set_token('STEP','15');
2051 fnd_message.raise_error;
2052 End dt_update_validate;
2053 --
2054 -- ----------------------------------------------------------------------------
2055 -- |--------------------------< dt_delete_validate >--------------------------|
2056 -- ----------------------------------------------------------------------------
2057 -- {Start Of Comments}
2058 --
2059 -- Description:
2060 -- This procedure is used for referential integrity of datetracked
2061 -- child entities when either a datetrack DELETE or ZAP is in operation
2062 -- and where there is no cascading of delete defined for this entity.
2063 -- For the datetrack mode of DELETE or ZAP we must ensure that no
2064 -- datetracked child rows exist between the validation start and end
2065 -- dates.
2066 --
2067 -- Prerequisites:
2068 -- This procedure is called from the delete_validate.
2069 --
2070 -- In Parameters:
2071 --
2072 -- Post Success:
2073 -- Processing continues.
2074 --
2075 -- Post Failure:
2076 -- If a row exists by determining the returning Boolean value from the
2077 -- generic dt_api.rows_exist function then we must supply an error via
2078 -- the use of the local exception handler l_rows_exist.
2079 --
2080 -- Developer Implementation Notes:
2081 -- This procedure should not need maintenance unless the HR Schema model
2082 -- changes.
2083 --
2084 -- Access Status:
2085 -- Internal Row Handler Use Only.
2086 --
2087 -- {End Of Comments}
2088 -- ----------------------------------------------------------------------------
2089 Procedure dt_delete_validate
2090 (p_cagr_entitlement_line_id in number
2091 ,p_datetrack_mode in varchar2
2092 ,p_validation_start_date in date
2093 ,p_validation_end_date in date
2094 ) Is
2095 --
2096 l_proc varchar2(72) := g_package||'dt_delete_validate';
2097 l_rows_exist Exception;
2098 l_table_name all_tables.table_name%TYPE;
2099 --
2100 Begin
2101 --
2102 -- Ensure that the p_datetrack_mode argument is not null
2103 --
2104 hr_api.mandatory_arg_error
2105 (p_api_name => l_proc
2106 ,p_argument => 'datetrack_mode'
2107 ,p_argument_value => p_datetrack_mode
2108 );
2109 --
2110 -- Only perform the validation if the datetrack mode is either
2111 -- DELETE or ZAP
2112 --
2113 If (p_datetrack_mode = hr_api.g_delete or
2114 p_datetrack_mode = hr_api.g_zap) then
2115 --
2116 --
2117 -- Ensure the arguments are not null
2118 --
2119 hr_api.mandatory_arg_error
2120 (p_api_name => l_proc
2121 ,p_argument => 'validation_start_date'
2122 ,p_argument_value => p_validation_start_date
2123 );
2124 --
2125 hr_api.mandatory_arg_error
2126 (p_api_name => l_proc
2127 ,p_argument => 'validation_end_date'
2128 ,p_argument_value => p_validation_end_date
2129 );
2130 --
2131 hr_api.mandatory_arg_error
2132 (p_api_name => l_proc
2133 ,p_argument => 'cagr_entitlement_line_id'
2134 ,p_argument_value => p_cagr_entitlement_line_id
2135 );
2136 --
2137 --
2138 --
2139 End If;
2140 --
2141 Exception
2142 When l_rows_exist Then
2143 --
2144 -- A referential integrity check was violated therefore
2145 -- we must error
2146 --
2147 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
2148 fnd_message.set_token('TABLE_NAME', l_table_name);
2149 fnd_message.raise_error;
2150 When Others Then
2151 --
2152 -- An unhandled or unexpected error has occurred which
2153 -- we must report
2154 --
2155 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2156 fnd_message.set_token('PROCEDURE', l_proc);
2157 fnd_message.set_token('STEP','15');
2158 fnd_message.raise_error;
2159 --
2160 End dt_delete_validate;
2161 --
2162 -- ----------------------------------------------------------------------------
2166 (p_rec in per_pcl_shd.g_rec_type
2163 -- |---------------------------< insert_validate >----------------------------|
2164 -- ----------------------------------------------------------------------------
2165 PROCEDURE insert_validate
2167 ,p_effective_date in date
2168 ,p_datetrack_mode in varchar2
2169 ,p_validation_start_date in date
2170 ,p_validation_end_date in date
2171 ) is
2172 --
2173 l_collective_agreement_id NUMBER;
2174 l_proc VARCHAR2(72) := g_package||'insert_validate';
2175 l_cagr_entitlement_item_id NUMBER;
2176 l_formula_criteria per_cagr_entitlements.formula_criteria%TYPE;
2177 l_business_group_id NUMBER;
2178 l_category_name per_cagr_entitlement_items.category_name%TYPE;
2179 --
2180 BEGIN
2181 --
2182 hr_utility.set_location('Entering:'||l_proc, 10);
2183 --
2184 -- Call procedure that returns the collective agreement id
2185 -- and business_group_id that will be used in the chk procedures
2186 --
2187 per_pcl_shd.retrieve_cagr_info
2188 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2189 ,p_collective_agreement_id => l_collective_agreement_id
2190 ,p_business_group_id => l_business_group_id);
2191 --
2192 hr_utility.set_location(l_proc, 20);
2193 --
2194 -- Call parent cagr_entitlement_item's set_security_group_id function
2195 --
2196 per_pce_bus.set_security_group_id
2197 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2198 ,p_collective_agreement_id => l_collective_agreement_id);
2199 --
2200 -- Retrieve the entitlement_item_id and category name
2201 --
2202 per_pcl_shd.retrieve_entitlement_item_info
2203 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2204 ,p_cagr_entitlement_item_id => l_cagr_entitlement_item_id
2205 ,p_category_name => l_category_name
2206 ,p_formula_criteria => l_formula_criteria);
2207 --
2208 hr_utility.set_location(l_proc, 30);
2209 --
2210 -- Validate mandatory
2211 --
2212 per_pcl_bus.chk_mandatory
2213 ( p_mandatory => p_rec.mandatory
2214 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2215 ,p_effective_date => p_effective_date
2216 ,p_validation_start_date => p_validation_start_date
2217 ,p_validation_end_date => p_validation_end_date);
2218 --
2219 hr_utility.set_location(l_proc, 40);
2220 --
2221 -- Validate value
2222 --
2223 per_pcl_bus.chk_value
2224 (p_value => p_rec.value
2225 ,p_cagr_entitlement_item_id => l_cagr_entitlement_item_id
2226 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2227 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2228 ,p_category_name => l_category_name
2229 ,p_effective_date => p_effective_date);
2230 --
2231 hr_utility.set_location(l_proc, 50);
2232 --
2233 -- Validate Range_From
2234 --
2235 per_pcl_bus.chk_range_from
2236 (p_range_from => p_rec.range_from
2237 ,p_cagr_entitlement_item_id => l_cagr_entitlement_item_id
2238 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2239 ,p_category_name => l_category_name
2240 ,p_effective_date => p_effective_date);
2241 --
2242 hr_utility.set_location(l_proc, 60);
2243 --
2244 -- Validate Range_To
2245 --
2246 per_pcl_bus.chk_range_to
2247 (p_range_to => p_rec.range_to
2248 ,p_cagr_entitlement_item_id => l_cagr_entitlement_item_id
2249 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2250 ,p_category_name => l_category_name
2251 ,p_effective_date => p_effective_date);
2252 --
2253 hr_utility.set_location(l_proc, 65);
2254 --
2255 -- If the entitlement item has been defined as a DATE
2256 -- type then check that all the dates are valid.
2257 --
2258 per_pcl_bus.chk_dates_are_valid
2259 (p_cagr_entitlement_item_id => l_cagr_entitlement_item_id
2260 ,p_value => p_rec.value
2261 ,p_range_from => p_rec.range_from
2262 ,p_range_to => p_rec.range_to
2263 ,p_effective_date => p_effective_date);
2264 --
2265 hr_utility.set_location(l_proc, 70);
2266 --
2267 -- Validate grade_spine_id
2268 --
2269 per_pcl_bus.chk_grade_spine_id
2270 ( p_grade_spine_id => p_rec.grade_spine_id
2271 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2272 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2273 ,p_effective_date => p_effective_date
2274 ,p_validation_start_date => p_validation_start_date
2275 ,p_validation_end_date => p_validation_end_date) ;
2276 --
2277 hr_utility.set_location(l_proc, 80);
2278 --
2279 -- Validate parent_spine_id
2280 --
2281 per_pcl_bus.chk_parent_spine_id
2282 (p_parent_spine_id => p_rec.parent_spine_id
2283 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2284 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id);
2285 --
2286 hr_utility.set_location(l_proc, 90);
2287 --
2288 -- Validate status
2289 --
2290 per_pcl_bus.chk_status
2291 (p_status => p_rec.status
2295 ,p_validation_end_date => p_validation_end_date) ;
2292 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2293 ,p_effective_date => p_effective_date
2294 ,p_validation_start_date => p_validation_start_date
2296 --
2297 hr_utility.set_location(l_proc, 100);
2298 --
2299 -- Validate oipl_id
2300 --
2301 per_pcl_bus.chk_oipl_id
2302 (p_oipl_id => p_rec.oipl_id
2303 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2304 ,p_effective_date => p_effective_date
2305 ,p_validation_start_date => p_validation_start_date
2306 ,p_validation_end_date => p_validation_end_date) ;
2307 --
2308 hr_utility.set_location(l_proc, 110);
2309 --
2310 -- Validate step_id
2311 --
2312 per_pcl_bus.chk_step_id
2313 (p_step_id => p_rec.step_id
2314 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2315 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2316 ,p_effective_date => p_effective_date
2317 ,p_validation_start_date => p_validation_start_date
2318 ,p_validation_end_date => p_validation_end_date);
2319 --
2320 hr_utility.set_location(l_proc, 120);
2321 --
2322 -- Validate eligy_prfl_id
2323 --
2324 per_pcl_bus.chk_eligy_prfl_id
2325 ( p_eligy_prfl_id => p_rec.eligy_prfl_id
2326 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2327 ,p_grade_spine_id => p_rec.grade_spine_id
2328 ,p_category_name => l_category_name
2329 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2330 ,p_business_group_id => l_business_group_id
2331 ,p_effective_date => p_effective_date
2332 ,p_formula_criteria => l_formula_criteria);
2333 --
2334 hr_utility.set_location(' Leaving:'||l_proc, 999);
2335 --
2336 End insert_validate;
2337 --
2338 -- ----------------------------------------------------------------------------
2339 -- |---------------------------< update_validate >----------------------------|
2340 -- ----------------------------------------------------------------------------
2341 Procedure update_validate
2342 (p_rec in per_pcl_shd.g_rec_type
2343 ,p_effective_date in date
2344 ,p_datetrack_mode in varchar2
2345 ,p_validation_start_date in date
2346 ,p_validation_end_date in date
2347 ) is
2348 --
2349 l_proc varchar2(72) := g_package||'update_validate';
2350 l_cagr_entitlement_item_id per_cagr_entitlements.cagr_entitlement_item_id%TYPE;
2351 l_formula_criteria per_cagr_entitlements.formula_criteria%TYPE;
2352 l_collective_agreement_id NUMBER;
2353 l_business_group_id NUMBER;
2354 l_category_name per_cagr_entitlement_items.category_name%TYPE;
2355 --
2356 BEGIN
2357 --
2358 hr_utility.set_location('Entering:'||l_proc, 5);
2359 --
2360 -- Call procedure that returns the collective agreement id
2361 -- and business_group_id that will be used in the chk procedures
2362 --
2363 per_pcl_shd.retrieve_cagr_info
2364 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2365 ,p_collective_agreement_id => l_collective_agreement_id
2366 ,p_business_group_id => l_business_group_id);
2367 --
2368 -- Call parent cagr_entitlement_item's set_security_group_id function
2369 --
2370 per_pce_bus.set_security_group_id
2371 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2372 ,p_collective_agreement_id => l_collective_agreement_id);
2373 --
2374 hr_utility.set_location(l_proc, 10);
2375 --
2376 chk_non_updateable_args
2377 (p_effective_date => p_effective_date
2378 ,p_rec => p_rec);
2379 --
2380 -- Retrieve the entitlement_item_id and category name
2381 --
2382 per_pcl_shd.retrieve_entitlement_item_info
2383 (p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2384 ,p_cagr_entitlement_item_id => l_cagr_entitlement_item_id
2385 ,p_category_name => l_category_name
2386 ,p_formula_criteria => l_formula_criteria);
2387 --
2388 -- Validate mandatory
2389 --
2390 per_pcl_bus.chk_mandatory
2391 (p_mandatory => p_rec.mandatory
2392 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2393 ,p_effective_date => p_effective_date
2394 ,p_validation_start_date => p_validation_start_date
2395 ,p_validation_end_date => p_validation_end_date);
2396 --
2397 hr_utility.set_location(l_proc, 20);
2398
2399 --
2400 hr_utility.set_location(l_proc, 30);
2401 --
2402 -- Validate value
2403 --
2404 per_pcl_bus.chk_value
2405 (p_value => p_rec.value
2406 ,p_cagr_entitlement_item_id => l_cagr_entitlement_item_id
2407 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2408 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2409 ,p_category_name => l_category_name
2410 ,p_effective_date => p_effective_date);
2411 --
2412 hr_utility.set_location(l_proc, 40);
2413 --
2414 -- Validate Range_From
2415 --
2416 per_pcl_bus.chk_range_from
2420 ,p_category_name => l_category_name
2417 (p_range_from => p_rec.range_from
2418 ,p_cagr_entitlement_item_id => l_cagr_entitlement_item_id
2419 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2421 ,p_effective_date => p_effective_date);
2422 --
2423 hr_utility.set_location(l_proc, 50);
2424 --
2425 -- Validate Range_To
2426 --
2427 per_pcl_bus.chk_range_to
2428 (p_range_to => p_rec.range_to
2429 ,p_cagr_entitlement_item_id => l_cagr_entitlement_item_id
2430 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2431 ,p_category_name => l_category_name
2432 ,p_effective_date => p_effective_date);
2433 --
2434 hr_utility.set_location(l_proc, 60);
2435 --
2436 -- If the entitlement item has been defined as a DATE
2437 -- type then check that all the dates are valid.
2438 --
2439 per_pcl_bus.chk_dates_are_valid
2440 (p_cagr_entitlement_item_id => l_cagr_entitlement_item_id
2441 ,p_value => p_rec.value
2442 ,p_range_from => p_rec.range_from
2443 ,p_range_to => p_rec.range_to
2444 ,p_effective_date => p_effective_date);
2445 --
2446 hr_utility.set_location(l_proc, 65);
2447 --
2448 -- Validate grade_spine_id
2449 --
2450 per_pcl_bus.chk_grade_spine_id
2451 (p_grade_spine_id => p_rec.grade_spine_id
2452 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2453 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2454 ,p_effective_date => p_effective_date
2455 ,p_validation_start_date => p_validation_start_date
2456 ,p_validation_end_date => p_validation_end_date) ;
2457 --
2458 hr_utility.set_location(l_proc, 70);
2459 --
2460 -- Validate parent_spine_id
2461 --
2462 per_pcl_bus.chk_parent_spine_id
2463 (p_parent_spine_id => p_rec.parent_spine_id
2464 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2465 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id);
2466 --
2467 hr_utility.set_location(l_proc, 80);
2468 --
2469 -- Validate status
2470 --
2471 per_pcl_bus.chk_status
2472 (p_status => p_rec.status
2473 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2474 ,p_effective_date => p_effective_date
2475 ,p_validation_start_date => p_validation_start_date
2476 ,p_validation_end_date => p_validation_end_date) ;
2477 --
2478 hr_utility.set_location(l_proc, 90);
2479 --
2480 -- Validate oipl_id
2481 --
2482 per_pcl_bus.chk_oipl_id
2483 (p_oipl_id => p_rec.oipl_id
2484 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2485 ,p_effective_date => p_effective_date
2486 ,p_validation_start_date => p_validation_start_date
2487 ,p_validation_end_date => p_validation_end_date) ;
2488 --
2489 hr_utility.set_location(l_proc, 100);
2490 --
2491 -- Validate step_id
2492 --
2493 per_pcl_bus.chk_step_id
2494 (p_step_id => p_rec.step_id
2495 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2496 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2497 ,p_effective_date => p_effective_date
2498 ,p_validation_start_date => p_validation_start_date
2499 ,p_validation_end_date => p_validation_end_date);
2500 --
2501 hr_utility.set_location(l_proc, 110);
2502 --
2503 -- Validate eligy_prfl_id
2504 --
2505 per_pcl_bus.chk_eligy_prfl_id
2506 ( p_eligy_prfl_id => p_rec.eligy_prfl_id
2507 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2508 ,p_grade_spine_id => p_rec.grade_spine_id
2509 ,p_category_name => l_category_name
2510 ,p_cagr_entitlement_id => p_rec.cagr_entitlement_id
2511 ,p_business_group_id => l_business_group_id
2512 ,p_effective_date => p_effective_date
2513 ,p_formula_criteria => l_formula_criteria);
2514 --
2515 -- Validate object_version_number
2516 --
2517 per_pcl_bus.chk_object_version_number
2518 (p_object_version_number => p_rec.object_version_number );
2519 --
2520 hr_utility.set_location(l_proc, 120);
2521 --
2522 -- Call the datetrack update integrity operation
2523 --
2524 dt_update_validate
2525 (p_grade_spine_id => p_rec.grade_spine_id
2526 ,p_step_id => p_rec.step_id
2527 ,p_from_step_id => p_rec.from_step_id
2528 ,p_to_step_id => p_rec.to_step_id
2529 ,p_datetrack_mode => p_datetrack_mode
2530 ,p_validation_start_date => p_validation_start_date
2531 ,p_validation_end_date => p_validation_end_date
2532 );
2533 --
2534 hr_utility.set_location(l_proc, 130);
2535 --
2536 hr_utility.set_location(' Leaving:'||l_proc, 10);
2537 --
2538 End update_validate;
2539 --
2540 -- ----------------------------------------------------------------------------
2541 -- |---------------------------< delete_validate >----------------------------|
2542 -- ----------------------------------------------------------------------------
2543 Procedure delete_validate
2544 (p_rec in per_pcl_shd.g_rec_type
2548 ,p_validation_end_date in date
2545 ,p_effective_date in date
2546 ,p_datetrack_mode in varchar2
2547 ,p_validation_start_date in date
2549 ) is
2550 --
2551 l_proc varchar2(72) := g_package||'delete_validate';
2552 --
2553 Begin
2554 hr_utility.set_location('Entering:'||l_proc, 5);
2555 --
2556 -- Call all supporting business operations
2557 --
2558
2559 per_pcl_bus.chk_object_version_number
2560 (
2561 p_object_version_number => p_rec.object_version_number );
2562
2563 dt_delete_validate
2564 (p_datetrack_mode => p_datetrack_mode
2565 ,p_validation_start_date => p_validation_start_date
2566 ,p_validation_end_date => p_validation_end_date
2567 ,p_cagr_entitlement_line_id => p_rec.cagr_entitlement_line_id
2568 );
2569 --
2570 hr_utility.set_location(' Leaving:'||l_proc, 10);
2571 End delete_validate;
2572 --
2573 end per_pcl_bus;
[Home] [Help]