1 Package Body pay_grr_bus as
2 /* $Header: pygrrrhi.pkb 115.4 2002/12/10 09:48:17 pkakar ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_grr_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_grade_rule_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |--------------------< chk_grade_or_spinal_point_id >---------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 -- Description:
21 -- Validates that a value for grade_or_spinal_point_id must be entered
22 --
23 -- Validates that if the grade rule is specific to a given grade, the
24 -- relevant grade exists and is date effective on per_grades
25 --
26 -- Validates that if the grade rule is specific to a given spinal point, the
27 -- relevant spinal point exists on per_spinal_points
28 --
29 -- Validates that the value for grade_or_spinal_point_id is unique for the
30 -- combination of business_group_id, rate_id, rate_type and
31 -- grade_or_spinal_point_id between the start and end effective dates for
32 -- the grade rule record
33 --
34 -- Validates that the business group of the grade rule is the same as the
35 -- business group of the spinal point.
36 --
37 -- Validates that the business group of the grade rule is the same as the
38 -- business group of the grade.
39 --
40 --
41 -- Pre-conditions:
42 -- A valid business group
43 --
44 -- In Arguments:
45 -- p_grade_rule_id
46 -- p_grade_or_spinal_point_id
47 -- p_effective_start_date
48 -- p_effective_end_date
49 -- p_effective_date
50 -- p_business_group_id
51 -- p_rate_type
52 -- p_rate_id
53 --
54 -- Post Success:
55 -- If the grade_or_spinal_point_id refers to a grade and the grade exists
56 -- and is date effective on per_grades then processing continues
57 --
58 -- If the grade_or_spinal_point_id refers to spinal point and the spinal
59 -- point exists on per_spinal_points then processing continues
60 --
61 -- If the combination of grade_or_spinal_point_id, business_group_id,
62 -- rate_id and rate_type is unique on pay_grade_rules_f between the start
63 -- and end effective dates then processing continues
64 --
65 -- If the business group of the grade rule is the same as the
66 -- business group of the spinal point.
67 --
68 -- If the business group of the grade rule is the same as the
69 -- business group of the grade.
70 --
71 --
72 -- Post Failure:
73 -- If the grade_or_spinal_point_id refers to a grade and the grade does
74 -- not exist or is not date effective on per_grades then an application
75 -- error is raised and processing is terminated
76 --
77 -- If the business group of the grade rule is not the same as the
78 -- business group of the spinal point.
79 --
80 -- If the business group of the grade rule is not the same as the
81 -- business group of the grade.
82 --
83 --
84 -- Access Status:
85 -- Internal Table Handler Use Only.
86 --
87 -- {End Of Comments}
88 -- ----------------------------------------------------------------------------
89 procedure chk_grade_or_spinal_point_id
90 (p_grade_rule_id in pay_grade_rules_f.grade_rule_id%TYPE
91 ,p_grade_or_spinal_point_id in pay_grade_rules_f.grade_or_spinal_point_id%TYPE
92 ,p_effective_start_date in date
93 ,p_effective_end_date in date
94 ,p_business_group_id in pay_grade_rules_f.business_group_id%TYPE
95 ,p_rate_type in pay_grade_rules_f.rate_type%TYPE
96 ,p_rate_id in pay_grade_rules_f.rate_id%TYPE
97 ,p_effective_date in date
98 ) is
99 --
100 l_exists varchar2(1);
101 l_proc varchar2(72) := g_package||'chk_grade_or_spinal_point_id';
102 l_business_group_id pay_grade_rules_f.business_group_id%type;
103 --
104 cursor csr_valid_grade_id is
105 select business_group_id
106 from per_grades pg
107 where pg.grade_id = p_grade_or_spinal_point_id
108 and p_effective_start_date between pg.date_from
109 and nvl(pg.date_to, hr_api.g_eot)
110 and p_effective_end_date <= nvl(pg.date_to, hr_api.g_eot);
111 --
112 cursor csr_valid_spinal_point is
113 select business_group_id
114 from per_spinal_points psp
115 where psp.spinal_point_id = p_grade_or_spinal_point_id;
116 --
117 cursor csr_unique_rate is
118 select null
119 from pay_grade_rules_f pgr
120 where pgr.rate_id = p_rate_id
121 and pgr.business_group_id = p_business_group_id
122 and pgr.rate_type = p_rate_type
123 and pgr.grade_or_spinal_point_id = p_grade_or_spinal_point_id
124 and p_effective_date between pgr.effective_start_date
125 and pgr.effective_end_date;
126 --
127 begin
128 hr_utility.set_location('Entering:'|| l_proc, 10);
129 --
130 -- Check mandatory parameters have been set
131 --
132 hr_api.mandatory_arg_error
133 (p_api_name => l_proc
134 ,p_argument => 'grade_or_spinal_point_id'
135 ,p_argument_value => p_grade_or_spinal_point_id
136 );
137 --
138 hr_api.mandatory_arg_error
139 (p_api_name => l_proc
140 ,p_argument => 'rate_id'
141 ,p_argument_value => p_rate_id
142 );
143 --
144 hr_api.mandatory_arg_error
145 (p_api_name => l_proc
146 ,p_argument => 'rate_type'
147 ,p_argument_value => p_rate_type
148 );
149 --
150 hr_api.mandatory_arg_error
151 (p_api_name => l_proc
152 ,p_argument => 'effective_start_date'
153 ,p_argument_value => p_effective_start_date
154 );
155 --
156 hr_api.mandatory_arg_error
157 (p_api_name => l_proc
158 ,p_argument => 'effective_end_date'
159 ,p_argument_value => p_effective_end_date
160 );
161 --
162 hr_utility.set_location(l_proc, 20);
163 --
164 -- Check that if the rule is for a grade, this grade
165 -- is a valid grade on per_grades, otherwise the rule
166 -- must be defined for a spinal point in which case
167 -- the spinal point id should exist on per_spinal_points
168 --
169 if p_rate_type = 'G' then
170 hr_utility.set_location(l_proc, 30);
171 open csr_valid_grade_id;
172 fetch csr_valid_grade_id into l_business_group_id;
173 if csr_valid_grade_id%notfound then
174 close csr_valid_grade_id;
175 hr_utility.set_message(801, 'HR_7311_GRR_INVALID_GRADE');
176 hr_utility.raise_error;
177 end if;
178 close csr_valid_grade_id;
179 hr_utility.set_location(l_proc, 35);
180 --
181 -- Check that the grade is in the same business group as the
182 -- grade rule
183 --
184 If p_business_group_id <> l_business_group_id then
185 --
186 hr_utility.set_message(801, 'HR_51299_GRR_INV_G_BG ');
187 hr_utility.raise_error;
188 --
189 end if;
190 hr_utility.set_location(l_proc, 36);
191 elsif p_rate_type = 'SP' then
192 hr_utility.set_location(l_proc, 40);
193 open csr_valid_spinal_point;
194 fetch csr_valid_spinal_point into l_business_group_id;
195 if csr_valid_spinal_point%notfound then
196 close csr_valid_spinal_point;
197 hr_utility.set_message(801, 'HR_7312_GRR_INVALID_SPNL_POINT');
198 hr_utility.raise_error;
199 end if;
200 close csr_valid_spinal_point;
201 hr_utility.set_location(l_proc, 50);
202 --
203 -- Check that the spinal point is in the same business group as the
204 -- grade rule
205 --
206 If p_business_group_id <> l_business_group_id then
207 --
208 hr_utility.set_message(801, 'HR_51298_GRR_INV_SP_BG ');
209 hr_utility.raise_error;
210 --
211 end if;
212 hr_utility.set_location(l_proc, 60);
213 end if;
214 --
215 hr_utility.set_location(l_proc, 3);
216 --
217 -- Checks that the combination of p_grade_or_spinal_point_id,
218 -- p_rate_id, p_rate_type, p_business_group_id is unique
219 -- betwen effective_start_date and effective_end_date on
220 -- per_valid_grades
221 --
222 open csr_unique_rate;
223 fetch csr_unique_rate into l_exists;
224 if csr_unique_rate%found then
225 close csr_unique_rate;
226 hr_utility.set_message(801, 'HR_7313_GRR_GRADE_RULE_COMB_EX');
227 hr_utility.raise_error;
228 end if;
229 close csr_unique_rate;
230 --
231 hr_utility.set_location(' Leaving:'|| l_proc, 4);
232 end chk_grade_or_spinal_point_id;
233 --
234 -- ---------------------------------------------------------------------------
235 -- |---------------------------< chk_rate_id >-------------------------------|
236 -- ---------------------------------------------------------------------------
237 --
238 -- Description:
239 -- Validates that the rate id exists in the table pay_rates
240 --
241 -- Validates that the business group of the grade rule is the same as
242 -- the business group of the rate.
243 --
244 -- Pre-conditions:
245 -- A valid business group
246 --
247 -- In Arguments:
248 -- p_rate_id
249 -- p_business_group_id
250 --
251 -- Post Success:
252 -- If the rate_id refers to a valid rate on the table pay_rates
253 -- then processing continues
254 --
255 -- If the business group of the grade rule is the same as the
256 -- business group of the rate.
257 --
258 -- Post Failure:
259 -- If the rate_id does not exist on the table pay_rates then an
260 -- application error is raised and processing is terminated
261 --
262 -- If the business group of the grade rule is not the same as the
263 -- business group of the rate.
264 --
265 -- Access Status:
266 -- Internal Table Handler Use Only.
267 --
268 -- {End Of Comments}
269 -- ----------------------------------------------------------------------------
270 procedure chk_rate_id
271 (p_rate_id in pay_grade_rules_f.rate_id%TYPE,
272 p_business_group_id pay_grade_rules_f.business_group_id%TYPE
273 )
274 is
275 --
276 l_exists varchar2(1);
277 l_proc varchar2(72) := g_package||'chk_rate_id';
278 l_business_group_id pay_grade_rules_f.business_group_id%TYPE;
279 --
280 cursor csr_rate_exists is
281 select business_group_id
282 from pay_rates pr
283 where pr.rate_id = p_rate_id;
284 --
285 begin
286 hr_utility.set_location('Entering:'|| l_proc, 10);
287 --
288 -- Check mandatory parameters have been set
289 --
290 hr_api.mandatory_arg_error
291 (p_api_name => l_proc
292 ,p_argument => 'rate_id'
293 ,p_argument_value => p_rate_id
294 );
295 --
296 hr_utility.set_location(l_proc, 20);
297 --
298 -- Check that p_rate_id exists on table
299 -- pay_rates
300 --
301 open csr_rate_exists;
302 fetch csr_rate_exists into l_business_group_id;
303 if csr_rate_exists%notfound then
304 close csr_rate_exists;
305 hr_utility.set_message(801, 'HR_7314_GRR_NON_EXIST_PAY_RATE');
306 hr_utility.raise_error;
307 end if;
308 close csr_rate_exists;
309 hr_utility.set_location(l_proc, 25);
310 --
311 -- Check that the rate is in the same business group as the
312 -- grade rule
313 --
314 If p_business_group_id <> l_business_group_id then
315 --
316 hr_utility.set_message(801, 'HR_51301_GRR_INV_RA_BG ');
317 hr_utility.raise_error;
318 --
319 end if;
320 hr_utility.set_location(' Leaving:'|| l_proc, 30);
321 end chk_rate_id;
322 --
323 -- ---------------------------------------------------------------------------
324 -- |--------------------------< chk_rate_type >------------------------------|
325 -- ---------------------------------------------------------------------------
326 --
327 -- Description:
328 -- Validates that the value entered for rate_type exists on
329 -- the table hr_lookups
330 --
331 -- Validates that the combination of the rate type and the
332 -- rate id exists on the table pay_rates
333 --
334 -- Pre-conditions:
335 -- A valid value for p_rate_id
336 --
337 -- In Arguments:
338 -- p_rate_id
339 -- p_rate_type
340 --
341 -- Post Success:
342 -- If the rate type exists on hr_lookups then processing
343 -- continues
344 --
345 -- If the combination of the rate type and the rate id exists
346 -- on the table pay_rates then processing continues
347 --
348 -- Post Failure:
349 -- If the rate type does not exist on hr_lookups, a constraint
350 -- error is raised and processing is terminated
351 --
352 -- If the combination of rate id and rate type does not exist
353 -- on the table pay_rates then an application error is raised
354 -- and processing is terminated
355 --
356 -- Access Status:
357 -- Internal Table Handler Use Only.
358 --
359 -- {End Of Comments}
360 -- ----------------------------------------------------------------------------
361 procedure chk_rate_type
362 (p_rate_id in pay_grade_rules.rate_id%TYPE
363 ,p_rate_type in pay_grade_rules.rate_type%TYPE)
364 is
365 --
366 l_exists varchar2(1);
367 l_proc varchar2(72) := g_package||'chk_rate_type';
368 --
369 cursor csr_valid_comb is
370 select null
371 from pay_rates pr
372 where pr.rate_id = p_rate_id
373 and pr.rate_type = p_rate_type;
374 --
375 begin
376 hr_utility.set_location('Entering:'|| l_proc, 1);
377 --
378 -- Check mandatory parameters have been set
379 --
380 hr_api.mandatory_arg_error
381 (p_api_name => l_proc
382 ,p_argument => 'p_rate_id'
383 ,p_argument_value => p_rate_id
384 );
385 --
386 hr_api.mandatory_arg_error
387 (p_api_name => l_proc
388 ,p_argument => 'p_rate_type'
389 ,p_argument_value => p_rate_type
390 );
391 --
392 hr_utility.set_location(l_proc, 2);
393 --
394 -- Check rate type in 'G' or 'SP'
395 --
396 if p_rate_type not in ('G','SP') then
397 pay_grr_shd.constraint_error(
398 p_constraint_name => 'PAY_GRL_RATE_TYPE_CHK');
399 end if;
400 --
401 hr_utility.set_location(l_proc, 3);
402 --
403 -- Check that the combination of rate id and
404 -- rate_type is valid on the table pay_rates
405 --
406 open csr_valid_comb;
407 fetch csr_valid_comb into l_exists;
408 if csr_valid_comb%notfound then
409 close csr_valid_comb;
410 hr_utility.set_message(801, 'HR_7315_GRR_INVA_PAY_RATE_COMB');
411 hr_utility.raise_error;
412 end if;
413 close csr_valid_comb;
414 hr_utility.set_location(' Leaving:'|| l_proc, 4);
415 end chk_rate_type;
416 --
417 -- ---------------------------------------------------------------------------
418 -- |---------------------------< chk_maximum >-------------------------------|
419 -- ---------------------------------------------------------------------------
420 --
421 -- Description:
422 -- Validates that if the grade rule is defined for a grade, the value
423 -- entered for the column Maximum must be greater than or equal to the value
424 -- for Minimum (if not null) and greater than or equal to the value for
425 -- Value (if not null)
426 --
427 -- Pre-conditions:
428 -- The grade (grade_or_spinal_point_id) must be valid on per_grades
429 --
430 -- In Arguments:
431 -- p_grade_rule_id
432 -- p_rate_type
433 -- p_rate_id
434 -- p_maximum
435 -- p_minimum
436 -- p_value
437 -- p_effective_date
438 -- p_object_version_number
439 --
440 -- Post Success:
441 -- If the value for Maximum is >= Minimum and >= Value then
442 -- processing continues
443 --
444 -- Post Failure:
445 --
446 -- If the value for Maximum is < Minimum and/or < Value then an
447 -- application error is raised and processing is terminated
448 --
449 -- Access Status:
450 -- Internal Table Handler Use Only.
451 --
452 -- {End Of Comments}
453 -- ----------------------------------------------------------------------------
454 procedure chk_maximum
455 (p_grade_rule_id in pay_grade_rules_f.grade_rule_id%TYPE
456 ,p_rate_id in pay_grade_rules_f.rate_id%TYPE
457 ,p_rate_type in pay_grade_rules_f.rate_type%TYPE
458 ,p_maximum in pay_grade_rules_f.maximum%TYPE
459 ,p_minimum in pay_grade_rules_f.minimum%TYPE
460 ,p_value in pay_grade_rules_f.value%TYPE
461 ,p_effective_date in date
462 ,p_object_version_number in pay_grade_rules_f.object_version_number%TYPE)
463 is
464 --
465 l_proc varchar2(72) := g_package||'chk_maximum';
466 l_api_updating boolean;
467 l_value pay_grade_rules_f.value%TYPE;
468 l_maximum pay_grade_rules_f.maximum%TYPE;
469 l_dummy_return_value pay_grade_rules_f.value%TYPE;
470 l_min_max_status varchar2(30);
471 l_uom pay_rates.rate_uom%TYPE;
472 l_currency_code per_business_groups.currency_code%TYPE;
473 --
474 cursor csel1 is
475 select pr.rate_uom
476 , pbg.currency_code
477 from per_business_groups pbg
478 , pay_rates pr
479 where pr.rate_id = p_rate_id
480 and pbg.business_group_id = pr.business_group_id;
481 --
482 begin
483 hr_utility.set_location('Entering:'|| l_proc, 1);
484 --
485 -- p_value and p_maximum reassigned to l_value
486 -- and l_maximum for use within the checkformat
487 -- procedure call as in/out arguments
488 --
489 l_value := p_value;
490 l_maximum := p_maximum;
491 --
492 -- Check mandatory parameters have been set
493 --
494 hr_api.mandatory_arg_error
495 (p_api_name => l_proc
496 ,p_argument => 'rate_type'
497 ,p_argument_value => p_rate_type
498 );
499 --
500 hr_api.mandatory_arg_error
501 (p_api_name => l_proc
502 ,p_argument => 'rate_id'
503 ,p_argument_value => p_rate_id
504 );
505 --
506 hr_api.mandatory_arg_error
507 (p_api_name => l_proc
508 ,p_argument => 'effective_date'
509 ,p_argument_value => p_effective_date
510 );
511 --
512 -- Only proceed with validation if :
513 -- a) The current g_old_rec is current and
514 -- b) The value for address type has changed
515 --
516 l_api_updating := pay_grr_shd.api_updating
517 (p_grade_rule_id => p_grade_rule_id
518 ,p_effective_date => p_effective_date
519 ,p_object_version_number => p_object_version_number);
520 --
521 if ((l_api_updating and
522 nvl(pay_grr_shd.g_old_rec.maximum, hr_api.g_varchar2) <>
523 nvl(l_maximum, hr_api.g_varchar2)) or
524 (NOT l_api_updating)) then
525 hr_utility.set_location(l_proc, 2);
526 --
527 if p_rate_type = 'G' then
528 open csel1;
529 fetch csel1 into l_uom, l_currency_code;
530 if csel1%notfound then
531 close csel1;
532 --
533 -- If no currency exists for a particular
534 -- business group then flag an error
535 --
536 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
537 hr_utility.set_message_token('PROCEDURE', l_proc);
538 hr_utility.set_message_token('STEP', '5');
539 end if;
540 close csel1;
541 --
542 -- Check 1 : maximum > minimum
543 --
544 hr_chkfmt.checkformat
545 (value => l_maximum,
546 format => l_uom,
547 output => l_dummy_return_value,
548 minimum => p_minimum,
549 maximum => l_maximum,
550 nullok => 'Y',
551 rgeflg => l_min_max_status,
552 curcode => l_currency_code);
553 --
554 if (l_min_max_status = 'F') then
555 hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
556 hr_utility.raise_error;
557 end if;
558 --
559 -- Check 2 : value < maximum
560 --
561 hr_chkfmt.checkformat
562 (value => l_value,
563 format => l_uom,
564 output => l_dummy_return_value,
565 minimum => '',
566 maximum => l_maximum,
567 nullok => 'Y',
568 rgeflg => l_min_max_status,
569 curcode => l_currency_code);
570 --
571 if (l_min_max_status = 'F') then
572 hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
573 hr_utility.raise_error;
574 end if;
575 --
576 -- 40.2 change start.
577 --
578 elsif p_rate_type = 'SP' then
579 if l_maximum is not null then
580 hr_utility.set_message(801, 'HR_7851_GRR_INVALID_SP_VAL');
581 hr_utility.raise_error;
582 end if;
583 --
584 -- 40.2 change end.
585 --
586 end if;
587 end if;
588 --
589 hr_utility.set_location(' Leaving:'|| l_proc, 3);
590 end ;
591 --
592 -- ---------------------------------------------------------------------------
593 -- |--------------------------< chk_mid_value >------------------------------|
594 -- ---------------------------------------------------------------------------
595 --
596 -- Description:
597 -- Validates that if the grade rule is defined for a grade, the value
598 -- entered for the column Mid Value must be greater or equal to the value
599 -- for Minimum (if not null) and less than or equal to the value for
600 -- Maximum (if not null).
601 --
602 -- Pre-conditions:
603 -- The grade (grade_or_spinal_point_id) must be valid on per_grades
604 --
605 -- In Arguments:
606 -- p_grade_rule_id
607 -- p_rate_type
608 -- p_rate_id
609 -- p_mid_value
610 -- p_maximum
611 -- p_minimum
612 -- p_effective_date
613 -- p_object_version_number
614 --
615 -- Post Success:
616 -- If the value for Mid Value is >= Minimum and <= Maximum then
617 -- processing continues
618 --
619 -- Post Failure:
620 --
621 -- If the value for Maximum is < Minimum and/or > Maximum then
622 -- an application error is raised and processing is terminated
623 --
624 -- Access Status:
625 -- Internal Table Handler Use Only.
626 --
627 -- {End Of Comments}
628 -- ----------------------------------------------------------------------------
629 procedure chk_mid_value
630 (p_grade_rule_id in pay_grade_rules_f.grade_rule_id%TYPE
631 ,p_rate_type in pay_grade_rules_f.rate_type%TYPE
632 ,p_rate_id in pay_grade_rules_f.rate_id%TYPE
633 ,p_mid_value in pay_grade_rules_f.mid_value%TYPE
634 ,p_maximum in pay_grade_rules_f.maximum%TYPE
635 ,p_minimum in pay_grade_rules_f.minimum%TYPE
636 ,p_effective_date in date
637 ,p_object_version_number in pay_grade_rules_f.object_version_number%TYPE)
638 is
639 --
640 l_proc varchar2(72) := g_package||'chk_mid_value';
641 l_api_updating boolean;
642 l_mid_value pay_grade_rules_f.mid_value%TYPE;
643 l_dummy_return_value pay_grade_rules_f.value%TYPE;
644 l_min_max_status varchar2(30);
645 l_uom pay_rates.rate_uom%TYPE;
646 l_currency_code per_business_groups.currency_code%TYPE;
647 --
648 cursor csel1 is
649 select pr.rate_uom
650 , pbg.currency_code
651 from per_business_groups pbg
652 , pay_rates pr
653 where pr.rate_id = p_rate_id
654 and pbg.business_group_id = pr.business_group_id;
655 --
656 begin
657 hr_utility.set_location('Entering:'|| l_proc, 1);
658 --
659 -- p_mid_value reassigned to l_mid_value for use within
660 -- the checkformat procedure call as in/out argument
661 --
662 l_mid_value := p_mid_value;
663 --
664 -- Check mandatory parameters have been set
665 --
666 hr_api.mandatory_arg_error
667 (p_api_name => l_proc
668 ,p_argument => 'rate_type'
669 ,p_argument_value => p_rate_type
670 );
671 --
672 hr_api.mandatory_arg_error
673 (p_api_name => l_proc
674 ,p_argument => 'rate_id'
675 ,p_argument_value => p_rate_id
676 );
677 --
678 hr_api.mandatory_arg_error
679 (p_api_name => l_proc
680 ,p_argument => 'effective_date'
681 ,p_argument_value => p_effective_date
682 );
683 --
684 -- Only proceed with validation if :
685 -- a) The current g_old_rec is current and
686 -- b) The value for address type has changed
687 --
688 l_api_updating := pay_grr_shd.api_updating
689 (p_grade_rule_id => p_grade_rule_id
690 ,p_effective_date => p_effective_date
691 ,p_object_version_number => p_object_version_number);
692 --
693 if ((l_api_updating and
694 nvl(pay_grr_shd.g_old_rec.mid_value, hr_api.g_varchar2) <>
695 nvl(l_mid_value, hr_api.g_varchar2)) or
696 (NOT l_api_updating)) then
697 hr_utility.set_location(l_proc, 2);
698 --
699 if p_rate_type = 'G' then
700 open csel1;
701 fetch csel1 into l_uom, l_currency_code;
702 if csel1%notfound then
703 close csel1;
704 hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
705 hr_utility.raise_error;
706 end if;
707 close csel1;
708 --
709 -- Check 1 : minimum < mid_value
710 --
711 hr_chkfmt.checkformat
712 (value => l_mid_value,
713 format => l_uom,
714 output => l_dummy_return_value,
715 minimum => p_minimum,
716 maximum => '',
717 nullok => 'Y',
718 rgeflg => l_min_max_status,
719 curcode => l_currency_code);
720 --
721 if (l_min_max_status = 'F') then
722 hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
723 hr_utility.raise_error;
724 end if;
725 --
726 -- Check 2 : mid_value < maximum
727 ---
728 hr_chkfmt.checkformat
729 (value => l_mid_value,
730 format => l_uom,
731 output => l_dummy_return_value,
732 minimum => '',
733 maximum => p_maximum,
734 nullok => 'Y',
735 rgeflg => l_min_max_status,
736 curcode => l_currency_code);
737 --
738 if (l_min_max_status = 'F') then
739 hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
740 hr_utility.raise_error;
741 end if;
742 --
743 -- Added check for not null value of mid_value for rate type
744 -- of SP - AForte 14/6/96
745 elsif p_rate_type = 'SP' then
746 if l_mid_value is not null then
747 hr_utility.set_message(801, 'HR_7851_GRR_INVALID_SP_VAL');
748 hr_utility.raise_error;
749 end if;
750 end if;
751 end if;
752 --
753 hr_utility.set_location(' Leaving:'|| l_proc, 3);
754 end ;
755 --
756 -- ---------------------------------------------------------------------------
757 -- |---------------------------< chk_minimum >-------------------------------|
758 -- ---------------------------------------------------------------------------
759 --
760 -- Description:
761 -- Validates that if the grade rule is defined for a grade, the value
762 -- entered for the column Minimum must be less than or equal to the value
763 -- for Maximum (if not null) and less than or equal to the value for
764 -- Value (if not null)
765 --
766 -- Pre-conditions:
767 -- The grade (grade_or_spinal_point_id) must be valid on per_grades
768 --
769 -- In Arguments:
770 -- p_grade_rule_id
771 -- p_rate_type
772 -- p_rate_id
773 -- p_maximum
774 -- p_minimum
775 -- p_value
776 -- p_effective_date
777 -- p_object_version_number
778 --
779 -- Post Success:
780 -- If the value for Minimum is <= Maximum and Value then
781 -- processing continues
782 --
783 -- Post Failure:
784 --
785 -- If the value for Minimum is > Maximum and/or Value then an
786 -- an application error is raised and processing is terminated
787 --
788 -- Access Status:
789 -- Internal Table Handler Use Only.
790 --
791 -- {End Of Comments}
792 -- ----------------------------------------------------------------------------
793 procedure chk_minimum
794 (p_grade_rule_id in pay_grade_rules_f.grade_rule_id%TYPE
795 ,p_rate_type in pay_grade_rules_f.rate_type%TYPE
796 ,p_rate_id in pay_grade_rules_f.rate_id%TYPE
797 ,p_maximum in pay_grade_rules_f.maximum%TYPE
798 ,p_minimum in pay_grade_rules_f.minimum%TYPE
799 ,p_value in pay_grade_rules_f.value%TYPE
800 ,p_effective_date in date
801 ,p_object_version_number in pay_grade_rules_f.object_version_number%TYPE)
802 is
803 --
804 l_proc varchar2(72) := g_package||'chk_minimum';
805 l_api_updating boolean;
806 l_minimum pay_grade_rules_f.minimum%TYPE;
807 l_value pay_grade_rules_f.value%TYPE;
808 l_dummy_return_value pay_grade_rules_f.value%TYPE;
809 l_min_max_status varchar2(30);
810 l_uom pay_rates.rate_uom%TYPE;
811 l_currency_code per_business_groups.currency_code%TYPE;
812 --
813 cursor csel1 is
814 select pr.rate_uom
815 , pbg.currency_code
816 from per_business_groups pbg
817 , pay_rates pr
818 where pr.rate_id = p_rate_id
819 and pbg.business_group_id = pr.business_group_id;
820 --
821 begin
822 hr_utility.set_location('Entering:'|| l_proc, 1);
823 --
824 -- p_minimum and p_value reassigned to l_minimum
825 -- and l_value for use within the checkformat
826 -- procedure call as in/out arguments
827 --
828 l_minimum := p_minimum;
829 l_value := p_value;
830 --
831 -- Check mandatory parameters have been set
832 --
833 hr_api.mandatory_arg_error
834 (p_api_name => l_proc
835 ,p_argument => 'rate_type'
836 ,p_argument_value => p_rate_type
837 );
838 --
839 hr_api.mandatory_arg_error
840 (p_api_name => l_proc
841 ,p_argument => 'rate_id'
842 ,p_argument_value => p_rate_id
843 );
844 --
845 hr_api.mandatory_arg_error
846 (p_api_name => l_proc
847 ,p_argument => 'effective_date'
848 ,p_argument_value => p_effective_date
849 );
850 --
851 -- Only proceed with validation if :
852 -- a) The current g_old_rec is current and
853 -- b) The value for address type has changed
854 --
855 l_api_updating := pay_grr_shd.api_updating
856 (p_grade_rule_id => p_grade_rule_id
857 ,p_effective_date => p_effective_date
858 ,p_object_version_number => p_object_version_number);
859 --
860 if ((l_api_updating and
861 nvl(pay_grr_shd.g_old_rec.minimum, hr_api.g_varchar2) <>
862 nvl(l_minimum, hr_api.g_varchar2)) or
863 (NOT l_api_updating)) then
864 hr_utility.set_location(l_proc, 2);
865 --
866 if p_rate_type = 'G' then
867 open csel1;
868 fetch csel1 into l_uom, l_currency_code;
869 if csel1%notfound then
870 close csel1;
871 --
872 -- If no currency exists for a particular
873 -- business group then flag an error
874 --
875 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
876 hr_utility.set_message_token('PROCEDURE', l_proc);
877 hr_utility.set_message_token('STEP', '5');
878 end if;
879 close csel1;
880 --
881 -- Check 1 : minimum < maximum
882 --
883 hr_chkfmt.checkformat
884 (value => l_minimum,
885 format => l_uom,
886 output => l_dummy_return_value,
887 minimum => '',
888 maximum => p_maximum,
889 nullok => 'Y',
890 rgeflg => l_min_max_status,
891 curcode => l_currency_code);
892 --
893 if (l_min_max_status = 'F') then
894 hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
895 hr_utility.raise_error;
896 end if;
897 --
898 -- Check 2 : value > minimum
899 --
900 hr_chkfmt.checkformat
901 (value => l_value,
902 format => l_uom,
903 output => l_dummy_return_value,
904 minimum => l_minimum,
905 maximum => '',
906 nullok => 'Y',
907 rgeflg => l_min_max_status,
908 curcode => l_currency_code);
909 --
910 if (l_min_max_status = 'F') then
911 hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
912 hr_utility.raise_error;
913 end if;
914 --
915 -- Added check for not null value of minimum for rate type
916 -- of SP - AForte 14/6/96
917
918 elsif p_rate_type = 'SP' then
919 if l_minimum is not null then
920 hr_utility.set_message(801, 'HR_7851_GRR_INVALID_SP_VAL');
921 hr_utility.raise_error;
922 end if;
923 end if;
924 end if;
925 --
926 hr_utility.set_location(' Leaving:'|| l_proc, 3);
927 end ;
928 --
929 -- ---------------------------------------------------------------------------
930 -- |---------------------------< chk_sequence >------------------------------|
931 -- ---------------------------------------------------------------------------
932 --
933 -- Description:
934 -- Validates that the value for Sequence is consistent with the Grade or
935 -- Spinal Point sequence for which the grade rule is defined
936 --
937 -- Pre-conditions:
938 -- None
939 --
940 -- In Arguments:
941 -- p_sequence
942 -- p_grade_or_spinal_point_id
943 -- p_business_group_id
944 -- p_rate_id
945 -- p_rate_type
946 -- p_effective_start_date
947 -- p_effective_end_date
948 --
949 -- Post Success:
950 -- If the value for sequence corresponds to the value defined for the
951 -- Grade or Spinal Point sequence on the table per_grades or
952 -- per_spinal_points then processing continues
953 --
954 -- Post Failure:
955 -- If the value for sequence does not correspond to the associated value
956 -- for Sequence for the Grade or Spinal Point defined on per_grades or
957 -- per_spinal_points then an application error is raised and
958 -- processing is terminated
959 --
960 -- Access Status:
961 -- Internal Table Handler Use Only.
962 --
963 -- {End Of Comments}
964 -- ----------------------------------------------------------------------------
965 procedure chk_sequence
966 (p_grade_or_spinal_point_id in pay_grade_rules_f.grade_or_spinal_point_id%TYPE
967 ,p_sequence in pay_grade_rules_f.sequence%TYPE
968 ,p_business_group_id in pay_grade_rules_f.business_group_id%TYPE
969 ,p_rate_id in pay_grade_rules_f.rate_id%TYPE
970 ,p_rate_type in pay_grade_rules_f.rate_type%TYPE
971 ,p_effective_start_date in date
972 ,p_effective_end_date in date)
973 is
974 --
975 l_exists varchar2(1);
976 l_proc varchar2(72) := g_package||'chk_sequence';
977 --
978 cursor csr_valid_grade_seq is
979 select null
980 from per_grades pg
981 where pg.grade_id = p_grade_or_spinal_point_id
982 and pg.sequence = p_sequence
983 and p_effective_start_date between pg.date_from
984 and nvl(pg.date_to, hr_api.g_eot)
985 and p_effective_end_date <= nvl(pg.date_to, hr_api.g_eot);
986 --
987 cursor csr_valid_sp_seq is
988 select null
989 from per_spinal_points psp
990 where psp.spinal_point_id = p_grade_or_spinal_point_id
991 and psp.sequence = p_sequence;
992 --
993 begin
994 hr_utility.set_location('Entering:'|| l_proc, 1);
995 --
996 -- Check mandatory parameters have been set
997 --
998 hr_api.mandatory_arg_error
999 (p_api_name => l_proc
1000 ,p_argument => 'grade_or_spinal_point_id'
1001 ,p_argument_value => p_grade_or_spinal_point_id
1002 );
1003 --
1004 hr_api.mandatory_arg_error
1005 (p_api_name => l_proc
1006 ,p_argument => 'rate_id'
1007 ,p_argument_value => p_rate_id
1008 );
1009 --
1010 hr_api.mandatory_arg_error
1011 (p_api_name => l_proc
1012 ,p_argument => 'rate_type'
1013 ,p_argument_value => p_rate_type
1014 );
1015 --
1016 hr_api.mandatory_arg_error
1017 (p_api_name => l_proc
1018 ,p_argument => 'effective_start_date'
1019 ,p_argument_value => p_effective_start_date
1020 );
1021 --
1022 hr_api.mandatory_arg_error
1023 (p_api_name => l_proc
1024 ,p_argument => 'effective_end_date'
1025 ,p_argument_value => p_effective_end_date
1026 );
1027 --
1028 hr_utility.set_location(l_proc, 2);
1029 --
1030 -- Check that combination of Sequence, Business Group,
1031 -- Rate ID and Rate Type is unique on pay_grade_rules_f
1032 --
1033 if p_sequence is not null then
1034 --
1035 -- Check that value for Sequence is valid for the grade
1036 -- on per_grades
1037 --
1038 if p_rate_type = 'G' then
1039 open csr_valid_grade_seq;
1040 fetch csr_valid_grade_seq into l_exists;
1041 if csr_valid_grade_seq%notfound then
1042 close csr_valid_grade_seq;
1043 hr_utility.set_message(801, 'HR_7318_GRR_INV_GRADE_SEQUENCE');
1044 hr_utility.raise_error;
1045 end if;
1046 close csr_valid_grade_seq;
1047 elsif p_rate_type = 'SP' then
1048 open csr_valid_sp_seq;
1049 fetch csr_valid_sp_seq into l_exists;
1050 if csr_valid_sp_seq%notfound then
1051 close csr_valid_sp_seq;
1052 hr_utility.set_message(801, 'HR_7319_GRR_INV_SPIN_POINT_SEQ');
1053 hr_utility.raise_error;
1054 end if;
1055 close csr_valid_sp_seq;
1056 end if;
1057 end if;
1058 --
1059 hr_utility.set_location(' Leaving:'|| l_proc, 3);
1060 end chk_sequence;
1061 --
1062 -- ---------------------------------------------------------------------------
1063 -- |---------------------------< chk_value >---------------------------------|
1064 -- ---------------------------------------------------------------------------
1065 --
1066 -- Description:
1067 -- Validates that if the grade rule is defined for a grade, the value
1068 -- entered for the column Value must be greater than or equal to the value
1069 -- for Minimum (if not null) and less than or equal to the value for
1070 -- Maximum (if not null)
1071 --
1072 -- Pre-conditions:
1073 -- The grade (grade_or_spinal_point_id) must be valid on per_grades
1074 --
1075 -- In Arguments:
1076 -- p_grade_rule_id
1077 -- p_rate_type
1078 -- p_maximum
1079 -- p_minimum
1080 -- p_value
1081 -- p_effective_date
1082 -- p_object_version_number
1083 --
1084 -- Post Success:
1085 -- If the value for Value is >= Minimum and <= Maximum then
1086 -- processing continues
1087 --
1088 -- Post Failure:
1089 --
1090 -- If the value for Value is < Minimum and/or > Maximum then an
1091 -- application error is raised and processing is terminated
1092 --
1093 -- Access Status:
1094 -- Internal Table Handler Use Only.
1095 --
1096 -- {End Of Comments}
1097 -- ----------------------------------------------------------------------------
1098 procedure chk_value
1099 (p_grade_rule_id in pay_grade_rules_f.grade_rule_id%TYPE
1100 ,p_rate_type in pay_grade_rules_f.rate_type%TYPE
1101 ,p_rate_id in pay_grade_rules_f.rate_id%TYPE
1102 ,p_maximum in pay_grade_rules_f.maximum%TYPE
1103 ,p_minimum in pay_grade_rules_f.minimum%TYPE
1104 ,p_value in pay_grade_rules_f.value%TYPE
1105 ,p_effective_date in date
1106 ,p_object_version_number in pay_grade_rules_f.object_version_number%TYPE)
1107 is
1108 --
1109 l_proc varchar2(72) := g_package||'chk_value';
1110 l_api_updating boolean;
1111 l_value pay_grade_rules_f.value%TYPE;
1112 l_dummy_return_value pay_grade_rules_f.value%TYPE;
1113 l_min_max_status varchar2(30);
1114 l_uom pay_rates.rate_uom%TYPE;
1115 l_currency_code per_business_groups.currency_code%TYPE;
1116 --
1117 cursor csel1 is
1118 select pr.rate_uom
1119 , pbg.currency_code
1120 from per_business_groups pbg
1121 , pay_rates pr
1122 where pr.rate_id = p_rate_id
1123 and pbg.business_group_id = pr.business_group_id;
1124 --
1125 begin
1126 hr_utility.set_location('Entering:'|| l_proc, 1);
1127 --
1128 -- p_value reassigned to l_value for use within
1129 -- the checkformat procedure call as in/out argument
1130 --
1131 l_value := p_value;
1132 --
1133 -- Check mandatory parameters have been set
1134 --
1135 hr_api.mandatory_arg_error
1136 (p_api_name => l_proc
1137 ,p_argument => 'rate_type'
1138 ,p_argument_value => p_rate_type
1139 );
1140 --
1141 hr_api.mandatory_arg_error
1142 (p_api_name => l_proc
1143 ,p_argument => 'rate_id'
1144 ,p_argument_value => p_rate_id
1145 );
1146 --
1147 hr_api.mandatory_arg_error
1148 (p_api_name => l_proc
1149 ,p_argument => 'effective_date'
1150 ,p_argument_value => p_effective_date
1151 );
1152 --
1153 -- Only proceed with validation if :
1154 -- a) The current g_old_rec is current and
1155 -- b) The value for address type has changed
1156 --
1157 l_api_updating := pay_grr_shd.api_updating
1158 (p_grade_rule_id => p_grade_rule_id
1159 ,p_effective_date => p_effective_date
1160 ,p_object_version_number => p_object_version_number);
1161 --
1162 if ((l_api_updating and
1163 nvl(pay_grr_shd.g_old_rec.value, hr_api.g_varchar2) <>
1164 nvl(l_value, hr_api.g_varchar2)) or
1165 (NOT l_api_updating)) then
1166 hr_utility.set_location(l_proc, 2);
1167 --
1168 -- Check that value for Value is >= Minimum and <= Maximum
1169 -- (if Minimum, Maximum NOT NULL)
1170 --
1171 if p_rate_type = 'G' then
1172 open csel1;
1173 fetch csel1 into l_uom, l_currency_code;
1174 if csel1%notfound then
1175 close csel1;
1176 --
1177 -- If no currency exists for a particular
1178 -- business group then flag an error
1179 --
1180 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1181 hr_utility.set_message_token('PROCEDURE', l_proc);
1182 hr_utility.set_message_token('STEP', '5');
1183 end if;
1184 close csel1;
1185 --
1186 -- Check 1 : value > minimum
1187 --
1188 hr_chkfmt.checkformat
1189 (value => l_value,
1190 format => l_uom,
1191 output => l_dummy_return_value,
1192 minimum => p_minimum,
1193 maximum => '',
1194 nullok => 'Y',
1195 rgeflg => l_min_max_status,
1196 curcode => l_currency_code);
1197 --
1198 if (l_min_max_status = 'F') then
1199 hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
1200 hr_utility.raise_error;
1201 end if;
1202 --
1203 -- Check 2 : value < maximum
1204 --
1205 hr_chkfmt.checkformat
1206 (value => l_value,
1207 format => l_uom,
1208 output => l_dummy_return_value,
1209 minimum => '',
1210 maximum => p_maximum,
1211 nullok => 'Y',
1212 rgeflg => l_min_max_status,
1213 curcode => l_currency_code);
1214 --
1215 if (l_min_max_status = 'F') then
1216 hr_utility.set_message(801, 'HR_7316_GRR_INVALID_VALUE');
1217 hr_utility.raise_error;
1218 end if;
1219 end if;
1220 end if;
1221 --
1222 hr_utility.set_location(' Leaving:'|| l_proc, 3);
1223 end ;
1224 --
1225 -- ----------------------------------------------------------------------------
1226 -- |----------------------< check_non_updateable_args >-----------------------|
1227 -- ----------------------------------------------------------------------------
1228 -- {Start Of Comments}
1229 --
1230 -- Description:
1231 -- This procedure is used to ensure that non updatetable attributes have
1232 -- not been updated. If an attribute has been updated an error is generated.
1233 --
1234 -- Pre Conditions:
1235 -- g_old_rec has been populated with details of the values currently in
1236 -- the database.
1237 --
1238 -- In Arguments:
1239 -- p_rec has been populated with the updated values the user would like the
1240 --
1241 -- Post Success:
1242 -- Processing continues if all the non updateable attributes have not
1243 -- changed.
1244 --
1245 -- Post Failure:
1246 -- An application error is raised if any of the non updatable attributes
1247 -- (business_group_id, grade_or_spinal_point_id, rate_id, rate_type or
1248 -- sequence) have been altered.
1249 --
1250 -- Access Status:
1251 -- Internal Table Handler Use Only.
1252 --
1253 -- {End Of Comments}
1254 -- ----------------------------------------------------------------------------
1255 Procedure check_non_updateable_args(p_rec in pay_grr_shd.g_rec_type
1256 ,p_effective_date in date) is
1257 --
1258 l_proc varchar2(72) := g_package||'check_non_updateable_args';
1259 l_error exception;
1260 l_argument varchar2(30);
1261 --
1262 Begin
1263 hr_utility.set_location('Entering:'||l_proc, 5);
1264 --
1265 -- Only proceed with validation if a row exists for
1266 -- the current record in the HR Schema
1267 --
1268 if not pay_grr_shd.api_updating
1269 (p_grade_rule_id => p_rec.grade_rule_id
1270 ,p_object_version_number => p_rec.object_version_number
1271 ,p_effective_date => p_effective_date
1272 ) then
1273 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1274 hr_utility.set_message_token('PROCEDURE', l_proc);
1275 hr_utility.set_message_token('STEP', '5');
1276 end if;
1277 --
1278 hr_utility.set_location(l_proc, 6);
1279 --
1280 if nvl(p_rec.business_group_id, hr_api.g_number) <>
1281 nvl(pay_grr_shd.g_old_rec.business_group_id, hr_api.g_number) then
1282 l_argument := 'business_group_id';
1283 raise l_error;
1284 end if;
1285 hr_utility.set_location(l_proc, 7);
1286 --
1287 if p_rec.grade_rule_id <> pay_grr_shd.g_old_rec.grade_rule_id then
1288 l_argument := 'grade_rule_id';
1289 raise l_error;
1290 end if;
1291 hr_utility.set_location(l_proc, 8);
1292 --
1293 if nvl(p_rec.grade_or_spinal_point_id, hr_api.g_number) <>
1294 nvl(pay_grr_shd.g_old_rec.grade_or_spinal_point_id, hr_api.g_number) then
1295 l_argument := 'grade_or_spinal_point_id';
1296 raise l_error;
1297 end if;
1298 hr_utility.set_location(l_proc, 9);
1299 --
1300 if nvl(p_rec.rate_id, hr_api.g_number) <>
1301 nvl(pay_grr_shd.g_old_rec.rate_id, hr_api.g_number) then
1302 l_argument := 'rate_id';
1303 raise l_error;
1304 end if;
1305 hr_utility.set_location(l_proc, 10);
1306 --
1307 if nvl(p_rec.sequence, hr_api.g_number) <>
1308 nvl(pay_grr_shd.g_old_rec.sequence, hr_api.g_number) then
1309 l_argument := 'sequence';
1310 raise l_error;
1311 end if;
1312 hr_utility.set_location(l_proc, 11);
1313 --
1314 if nvl(p_rec.rate_type, hr_api.g_varchar2) <>
1315 nvl(pay_grr_shd.g_old_rec.rate_type, hr_api.g_varchar2) then
1316 l_argument := 'rate_type';
1317 raise l_error;
1318 end if;
1319 hr_utility.set_location(l_proc, 12);
1320 exception
1321 when l_error then
1322 hr_api.argument_changed_error
1323 (p_api_name => l_proc
1324 ,p_argument => l_argument);
1325 when others then
1326 raise;
1327 hr_utility.set_location(' Leaving:'||l_proc, 12);
1328 end check_non_updateable_args;
1329 --
1330 -- ----------------------------------------------------------------------------
1331 -- |--------------------------< dt_update_validate >--------------------------|
1332 -- ----------------------------------------------------------------------------
1333 -- {Start Of Comments}
1334 --
1335 -- Description:
1336 -- This procedure is used for referential integrity of datetracked
1337 -- parent entities when a datetrack update operation is taking place
1338 -- and where there is no cascading of update defined for this entity.
1339 --
1340 -- Pre Conditions:
1341 -- This procedure is called from the update_validate.
1342 --
1343 -- In Arguments:
1344 --
1345 -- Post Success:
1346 -- Processing continues.
1347 --
1348 -- Post Failure:
1349 --
1350 -- Developer Implementation Notes:
1351 -- This procedure should not need maintenance unless the HR Schema model
1352 -- changes.
1353 --
1354 -- Access Status:
1355 -- Internal Table Handler USe Only.
1356 --
1357 -- {End Of Comments}
1358 -- ----------------------------------------------------------------------------
1359 Procedure dt_update_validate
1360 (
1361 p_datetrack_mode in varchar2,
1362 p_validation_start_date in date,
1363 p_validation_end_date in date) Is
1364 --
1365 l_proc varchar2(72) := g_package||'dt_update_validate';
1366 l_integrity_error Exception;
1367 l_table_name all_tables.table_name%TYPE;
1368 --
1369 Begin
1370 hr_utility.set_location('Entering:'||l_proc, 5);
1371 --
1372 -- Ensure that the p_datetrack_mode argument is not null
1373 --
1374 hr_api.mandatory_arg_error
1375 (p_api_name => l_proc,
1376 p_argument => 'datetrack_mode',
1377 p_argument_value => p_datetrack_mode);
1378 --
1379 -- Only perform the validation if the datetrack update mode is valid
1380 --
1381 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1382 --
1383 --
1384 -- Ensure the arguments are not null
1385 --
1386 hr_api.mandatory_arg_error
1387 (p_api_name => l_proc,
1388 p_argument => 'validation_start_date',
1389 p_argument_value => p_validation_start_date);
1390 --
1391 hr_api.mandatory_arg_error
1392 (p_api_name => l_proc,
1393 p_argument => 'validation_end_date',
1394 p_argument_value => p_validation_end_date);
1395 --
1396 --
1397 --
1398 End If;
1399 --
1400 hr_utility.set_location(' Leaving:'||l_proc, 10);
1401 Exception
1402 When l_integrity_error Then
1403 --
1404 -- A referential integrity check was violated therefore
1405 -- we must error
1406 --
1407 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
1408 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1409 hr_utility.raise_error;
1410 When Others Then
1411 --
1412 -- An unhandled or unexpected error has occurred which
1413 -- we must report
1414 --
1415 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1416 hr_utility.set_message_token('PROCEDURE', l_proc);
1417 hr_utility.set_message_token('STEP','15');
1418 hr_utility.raise_error;
1419 End dt_update_validate;
1420 --
1421 -- ----------------------------------------------------------------------------
1422 -- |--------------------------< dt_delete_validate >--------------------------|
1423 -- ----------------------------------------------------------------------------
1424 -- {Start Of Comments}
1425 --
1426 -- Description:
1427 -- This procedure is used for referential integrity of datetracked
1428 -- child entities when either a datetrack DELETE or ZAP is in operation
1429 -- and where there is no cascading of delete defined for this entity.
1430 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1431 -- datetracked child rows exist between the validation start and end
1432 -- dates.
1433 --
1434 -- Pre Conditions:
1435 -- This procedure is called from the delete_validate.
1436 --
1437 -- In Arguments:
1438 --
1439 -- Post Success:
1440 -- Processing continues.
1441 --
1442 -- Post Failure:
1443 -- If a row exists by determining the returning Boolean value from the
1444 -- generic dt_api.rows_exist function then we must supply an error via
1445 -- the use of the local exception handler l_rows_exist.
1446 --
1447 -- Developer Implementation Notes:
1448 -- This procedure should not need maintenance unless the HR Schema model
1449 -- changes.
1450 --
1451 -- Access Status:
1452 -- Internal Table Handler USe Only.
1453 --
1454 -- {End Of Comments}
1455 -- ----------------------------------------------------------------------------
1456 Procedure dt_delete_validate
1457 (p_grade_rule_id in number,
1458 p_datetrack_mode in varchar2,
1459 p_validation_start_date in date,
1460 p_validation_end_date in date) Is
1461 --
1462 l_proc varchar2(72) := g_package||'dt_delete_validate';
1463 l_rows_exist Exception;
1464 l_table_name all_tables.table_name%TYPE;
1465 --
1466 Begin
1467 hr_utility.set_location('Entering:'||l_proc, 5);
1468 --
1469 -- Ensure that the p_datetrack_mode argument is not null
1470 --
1471 hr_api.mandatory_arg_error
1472 (p_api_name => l_proc,
1473 p_argument => 'datetrack_mode',
1474 p_argument_value => p_datetrack_mode);
1475 --
1476 -- Only perform the validation if the datetrack mode is either
1477 -- DELETE or ZAP
1478 --
1479 If (p_datetrack_mode = 'DELETE' or
1480 p_datetrack_mode = 'ZAP') then
1481 --
1482 --
1483 -- Ensure the arguments are not null
1484 --
1485 hr_api.mandatory_arg_error
1486 (p_api_name => l_proc,
1487 p_argument => 'validation_start_date',
1488 p_argument_value => p_validation_start_date);
1489 --
1490 hr_api.mandatory_arg_error
1491 (p_api_name => l_proc,
1492 p_argument => 'validation_end_date',
1493 p_argument_value => p_validation_end_date);
1494 --
1495 hr_api.mandatory_arg_error
1496 (p_api_name => l_proc,
1497 p_argument => 'grade_rule_id',
1498 p_argument_value => p_grade_rule_id);
1499 --
1500 --
1501 --
1502 End If;
1503 --
1504 hr_utility.set_location(' Leaving:'||l_proc, 10);
1505 Exception
1506 When l_rows_exist Then
1507 --
1508 -- A referential integrity check was violated therefore
1509 -- we must error
1510 --
1511 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
1512 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1513 hr_utility.raise_error;
1514 When Others Then
1515 --
1516 -- An unhandled or unexpected error has occurred which
1517 -- we must report
1518 --
1519 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1520 hr_utility.set_message_token('PROCEDURE', l_proc);
1521 hr_utility.set_message_token('STEP','15');
1522 hr_utility.raise_error;
1523 End dt_delete_validate;
1524 --
1525 -- ----------------------------------------------------------------------------
1526 -- |---------------------------< insert_validate >----------------------------|
1527 -- ----------------------------------------------------------------------------
1528 Procedure insert_validate
1529 (p_rec in pay_grr_shd.g_rec_type,
1530 p_effective_date in date,
1531 p_datetrack_mode in varchar2,
1532 p_validation_start_date in date,
1533 p_validation_end_date in date) is
1534 --
1535 l_proc varchar2(72) := g_package||'insert_validate';
1536 --
1537 Begin
1538 hr_utility.set_location('Entering:'||l_proc, 5);
1539 --
1540 -- Call all supporting business operations
1541 --
1542 --
1543 -- Validate business group id
1544 --
1545 -- Business Rule Mapping
1546 -- =====================
1547 -- Rule CHK_BUSINESS_GROUP_ID / a,c
1548 --
1549 hr_api.validate_bus_grp_id(p_rec.business_group_id);
1550 --
1551 hr_utility.set_location(l_proc, 6);
1552 --
1553 -- Validate grade or spinal point id
1554 --
1555 -- Business Rule Mapping
1556 -- =====================
1557 -- Rule CHK_GRADE_OR_SPINAL_POINT_ID / a,b,c,d
1558 --
1559 chk_grade_or_spinal_point_id
1560 (p_grade_rule_id => p_rec.grade_rule_id
1561 ,p_grade_or_spinal_point_id => p_rec.grade_or_spinal_point_id
1562 ,p_effective_start_date => p_validation_start_date
1563 ,p_effective_end_date => p_validation_end_date
1564 ,p_effective_date => p_effective_date
1565 ,p_business_group_id => p_rec.business_group_id
1566 ,p_rate_type => p_rec.rate_type
1567 ,p_rate_id => p_rec.rate_id
1568 );
1569 --
1570 hr_utility.set_location(l_proc, 8);
1571 --
1572 -- Validate rate id
1573 --
1574 chk_rate_id
1575 (p_rate_id => p_rec.rate_id,
1576 p_business_group_id => p_rec.business_group_id
1577 );
1578 --
1579 hr_utility.set_location(l_proc, 9);
1580 --
1581 -- Validate rate type
1582 --
1583 -- Business Rule Mapping
1584 -- =====================
1585 -- Rule CHK_RATE_TYPE / a,b,c
1586 --
1587 chk_rate_type
1588 (p_rate_id => p_rec.rate_id
1589 ,p_rate_type => p_rec.rate_type
1590 );
1591 --
1592 hr_utility.set_location(l_proc, 10);
1593 --
1594 -- Validate maximum
1595 --
1596 -- Business Rule Mapping
1597 -- =====================
1598 -- Rule CHK_MAXIMUM / a,b
1599 --
1600 chk_maximum
1601 (p_grade_rule_id => p_rec.grade_rule_id
1602 ,p_rate_type => p_rec.rate_type
1603 ,p_rate_id => p_rec.rate_id
1604 ,p_maximum => p_rec.maximum
1605 ,p_minimum => p_rec.minimum
1606 ,p_value => p_rec.value
1607 ,p_effective_date => p_effective_date
1608 ,p_object_version_number => p_rec.object_version_number
1609 );
1610 --
1611 hr_utility.set_location(l_proc, 11);
1612 --
1613 -- Validate mid value
1614 --
1615 -- Business Rule Mapping
1616 -- =====================
1617 -- Rule CHK_MID_VALUE / a,b
1618 --
1619 chk_mid_value
1620 (p_grade_rule_id => p_rec.grade_rule_id
1621 ,p_rate_type => p_rec.rate_type
1622 ,p_rate_id => p_rec.rate_id
1623 ,p_mid_value => p_rec.mid_value
1624 ,p_maximum => p_rec.maximum
1625 ,p_minimum => p_rec.minimum
1626 ,p_effective_date => p_effective_date
1627 ,p_object_version_number => p_rec.object_version_number
1628 );
1629 --
1630 hr_utility.set_location(l_proc, 12);
1631 --
1632 -- Validate minimum
1633 --
1634 -- Business Rule Mapping
1635 -- =====================
1636 -- Rule CHK_MINIMUM / a,b
1637 --
1638 chk_minimum
1639 (p_grade_rule_id => p_rec.grade_rule_id
1640 ,p_rate_type => p_rec.rate_type
1641 ,p_rate_id => p_rec.rate_id
1642 ,p_maximum => p_rec.maximum
1643 ,p_minimum => p_rec.minimum
1644 ,p_value => p_rec.value
1645 ,p_effective_date => p_effective_date
1646 ,p_object_version_number => p_rec.object_version_number
1647 );
1648 --
1649 hr_utility.set_location(l_proc, 13);
1650 --
1651 -- Validate sequence
1652 --
1653 -- Business Rule Mapping
1654 -- =====================
1655 -- Rule CHK_SEQUENCE / a,b,c
1656 --
1657 chk_sequence
1658 (p_sequence => p_rec.sequence
1659 ,p_grade_or_spinal_point_id => p_rec.grade_or_spinal_point_id
1660 ,p_business_group_id => p_rec.business_group_id
1661 ,p_rate_id => p_rec.rate_id
1662 ,p_rate_type => p_rec.rate_type
1663 ,p_effective_start_date => p_validation_start_date
1664 ,p_effective_end_date => p_validation_end_date
1665 );
1666 --
1667 hr_utility.set_location(l_proc, 14);
1668 --
1669 -- Validate value
1670 --
1671 -- Business Rule Mapping
1672 -- =====================
1673 -- Rule CHK_VALUE / a,b
1674 --
1675 chk_value
1676 (p_grade_rule_id => p_rec.grade_rule_id
1677 ,p_rate_type => p_rec.rate_type
1678 ,p_rate_id => p_rec.rate_id
1679 ,p_maximum => p_rec.maximum
1680 ,p_minimum => p_rec.minimum
1681 ,p_value => p_rec.value
1682 ,p_effective_date => p_effective_date
1683 ,p_object_version_number => p_rec.object_version_number
1684 );
1685 --
1686 hr_utility.set_location(' Leaving:'||l_proc, 15);
1687 End insert_validate;
1688 --
1689 -- ----------------------------------------------------------------------------
1690 -- |---------------------------< update_validate >----------------------------|
1691 -- ----------------------------------------------------------------------------
1692 Procedure update_validate
1693 (p_rec in pay_grr_shd.g_rec_type,
1694 p_effective_date in date,
1695 p_datetrack_mode in varchar2,
1696 p_validation_start_date in date,
1697 p_validation_end_date in date) is
1698 --
1699 l_proc varchar2(72) := g_package||'update_validate';
1700 --
1701 Begin
1702 hr_utility.set_location('Entering:'||l_proc, 5);
1703 --
1704 -- Call all supporting business operations. Mapping to the
1705 -- appropriate Business Rules in pergrr.bru is provided.
1706 --
1707 -- Check that the columns which cannot be updated
1708 -- have not changed
1709 --
1710 -- Business Rule Mapping
1711 -- =====================
1712 -- CHK_GRADE_RULE_ID / c
1713 -- CHK_BUSINESS_GROUP_ID / b
1714 -- CHK_GRADE_OR_SPINAL_POINT_ID / e
1715 -- CHK_RATE_ID / c
1716 -- CHK_RATE_TYPE / d
1717 --
1718 check_non_updateable_args(p_rec => p_rec
1719 ,p_effective_date => p_effective_date);
1720 --
1721 hr_utility.set_location(l_proc, 6);
1722 -- Validate maximum
1723 --
1724 -- Business Rule Mapping
1725 -- =====================
1726 -- CHK_MAXIMUM / a,b
1727 --
1728 chk_maximum
1729 (p_grade_rule_id => p_rec.grade_rule_id
1730 ,p_rate_type => p_rec.rate_type
1731 ,p_rate_id => p_rec.rate_id
1732 ,p_maximum => p_rec.maximum
1733 ,p_minimum => p_rec.minimum
1734 ,p_value => p_rec.value
1735 ,p_effective_date => p_effective_date
1736 ,p_object_version_number => p_rec.object_version_number
1737 );
1738 --
1739 hr_utility.set_location(l_proc, 7);
1740 --
1741 -- Validate mid value
1742 --
1743 -- Business Rule Mapping
1744 -- =====================
1745 -- CHK_MID_VALUE / a,b
1746 --
1747 chk_mid_value
1748 (p_grade_rule_id => p_rec.grade_rule_id
1749 ,p_rate_type => p_rec.rate_type
1750 ,p_rate_id => p_rec.rate_id
1751 ,p_mid_value => p_rec.mid_value
1752 ,p_maximum => p_rec.maximum
1753 ,p_minimum => p_rec.minimum
1754 ,p_effective_date => p_effective_date
1755 ,p_object_version_number => p_rec.object_version_number
1756 );
1757 --
1758 hr_utility.set_location(l_proc, 8);
1759 --
1760 -- Validate minimum
1761 --
1762 -- Business Rule Mapping
1763 -- =====================
1764 -- CHK_MINIMUM / a,b
1765 --
1766 chk_minimum
1767 (p_grade_rule_id => p_rec.grade_rule_id
1768 ,p_rate_type => p_rec.rate_type
1769 ,p_rate_id => p_rec.rate_id
1770 ,p_maximum => p_rec.maximum
1771 ,p_minimum => p_rec.minimum
1772 ,p_value => p_rec.value
1773 ,p_effective_date => p_effective_date
1774 ,p_object_version_number => p_rec.object_version_number
1775 );
1776 --
1777 hr_utility.set_location(l_proc, 9);
1778 --
1779 -- Validate value
1780 --
1781 -- Business Rule Mapping
1782 -- =====================
1783 -- CHK_VALUE / a,b
1784 --
1785 chk_value
1786 (p_grade_rule_id => p_rec.grade_rule_id
1787 ,p_rate_type => p_rec.rate_type
1788 ,p_rate_id => p_rec.rate_id
1789 ,p_maximum => p_rec.maximum
1790 ,p_minimum => p_rec.minimum
1791 ,p_value => p_rec.value
1792 ,p_effective_date => p_effective_date
1793 ,p_object_version_number => p_rec.object_version_number
1794 );
1795 --
1796 hr_utility.set_location(l_proc, 10);
1797 --
1798 -- Call the datetrack update integrity operation
1799 --
1800 dt_update_validate
1801 (
1802 p_datetrack_mode => p_datetrack_mode,
1803 p_validation_start_date => p_validation_start_date,
1804 p_validation_end_date => p_validation_end_date);
1805 --
1806 hr_utility.set_location(' Leaving:'||l_proc, 11);
1807 End update_validate;
1808 --
1809 -- ----------------------------------------------------------------------------
1810 -- |---------------------------< delete_validate >----------------------------|
1811 -- ----------------------------------------------------------------------------
1812 Procedure delete_validate
1813 (p_rec in pay_grr_shd.g_rec_type,
1814 p_effective_date in date,
1815 p_datetrack_mode in varchar2,
1816 p_validation_start_date in date,
1817 p_validation_end_date in date) is
1818 --
1819 l_proc varchar2(72) := g_package||'delete_validate';
1820 --
1821 Begin
1822 hr_utility.set_location('Entering:'||l_proc, 5);
1823 --
1824 -- Call all supporting business operations
1825 --
1826 dt_delete_validate
1827 (p_datetrack_mode => p_datetrack_mode,
1828 p_validation_start_date => p_validation_start_date,
1829 p_validation_end_date => p_validation_end_date,
1830 p_grade_rule_id => p_rec.grade_rule_id);
1831 --
1832 hr_utility.set_location(' Leaving:'||l_proc, 10);
1833 End delete_validate;
1834 --
1835 --
1836 -- ---------------------------------------------------------------------------
1837 -- |---------------------< return_legislation_code >-------------------------|
1838 -- ---------------------------------------------------------------------------
1839 --
1840 function return_legislation_code
1841 (p_grade_rule_id in number
1842 ) return varchar2 is
1843 --
1844 -- Declare cursor
1845 --
1846 cursor csr_leg_code is
1847 select pbg.legislation_code
1848 from per_business_groups pbg
1849 , pay_grade_rules_f pgr
1850 where pgr.grade_rule_id = p_grade_rule_id
1851 and pbg.business_group_id = pgr.business_group_id
1852 order by pgr.effective_start_date;
1853 --
1854 -- Declare local variables
1855 --
1856 l_legislation_code varchar2(150);
1857 l_proc varchar2(72) := g_package||'return_legislation_code';
1858 begin
1859 hr_utility.set_location('Entering:'|| l_proc, 10);
1860 --
1861 -- Ensure that all the mandatory parameter are not null
1862 --
1863 hr_api.mandatory_arg_error(p_api_name => l_proc,
1864 p_argument => 'grade_rule_id',
1865 p_argument_value => p_grade_rule_id);
1866 --
1867 if nvl(g_grade_rule_id, hr_api.g_number) = p_grade_rule_id then
1868 --
1869 -- The legislation code has already been found with a previous
1870 -- call to this function. Just return the value in the global
1871 -- variable.
1872 --
1873 l_legislation_code := g_legislation_code;
1874 hr_utility.set_location(l_proc, 20);
1875 else
1876 --
1877 -- The ID is different to the last call to this function
1878 -- or this is the first call to this function.
1879 --
1880 open csr_leg_code;
1881 fetch csr_leg_code into l_legislation_code;
1882 if csr_leg_code%notfound then
1883 close csr_leg_code;
1884 --
1885 -- The primary key is invalid therefore we must error
1886 --
1887 hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
1888 hr_utility.raise_error;
1889 end if;
1890 --
1891 -- Set the global variables so the values are
1892 -- available for the next call to this function
1893 --
1894 close csr_leg_code;
1895 g_grade_rule_id := p_grade_rule_id;
1896 g_legislation_code := l_legislation_code;
1897 end if;
1898 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1899 --
1900 return l_legislation_code;
1901 end return_legislation_code;
1902 --
1903 end pay_grr_bus;
[Home] [Help]