1 Package Body pay_pgr_bus as
2 /* $Header: pypgrrhi.pkb 120.5.12010000.2 2008/08/06 08:12:15 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_pgr_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_rate_type >----------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 PROCEDURE chk_rate_type
21 (p_rate_type IN pay_grade_rules_f.rate_type%TYPE
22 ,p_effective_date IN DATE
23 ,p_grade_rule_id IN pay_grade_rules_f.grade_rule_id%TYPE
24 ,p_object_version_number IN pay_grade_rules_f.object_version_number%TYPE
25 ,p_validation_start_date IN DATE
26 ,p_validation_end_date IN DATE) IS
27 --
28 l_api_updating BOOLEAN;
29 l_proc VARCHAR2(72) := g_package||'chk_rate_type';
30 --
31 BEGIN
32 --
33 hr_utility.set_location('Entering : '||l_proc,10);
34 --
35 -- Only proceed with validation if :
36 -- a) The rate_type is changing or new
37 -- b) The value for rate_type is changing and not null
38 --
39 IF ( (p_grade_rule_id IS NULL) OR
40 ((p_grade_rule_id IS NOT NULL) AND
41 (pay_pgr_shd.g_old_rec.rate_type <> p_rate_type))) THEN
42 --
43 hr_utility.set_location(l_proc, 20);
44 --
45 -- Check that the rate type exists in HR_LOOKUPS
46 --
47 IF hr_api.not_exists_in_dt_hr_lookups
48 (p_effective_date => p_effective_date
49 ,p_validation_start_date => p_validation_start_date
50 ,p_validation_end_date => p_validation_end_date
51 ,p_lookup_type => 'RATE_TYPE'
52 ,p_lookup_code => p_rate_type) THEN
53 --
54 hr_utility.set_message(800, 'HR_289589_INV_ASG_RATE_TYPE');
55 hr_utility.raise_error;
56 --
57 END IF;
58 --
59 END IF;
60 --
61 hr_utility.set_location(' Leaving : '||l_proc,999);
62 --
63 EXCEPTION
64 --
65 WHEN app_exception.application_exception THEN
66 --
67 IF hr_multi_message.exception_add
68 (p_associated_column1 => 'PAY_GRADE_RULES_F.RATE_TYPE') THEN
69 --
70 hr_utility.set_location(' Leaving : '||l_proc,998);
71 --
72 RAISE;
73 --
74 END IF;
75 --
76 hr_utility.set_location(' Leaving : '||l_proc,999);
77 --
78 END chk_rate_type;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |-----------------------------< chk_rate_id >-----------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 PROCEDURE chk_rate_id
85 (p_rate_id IN pay_grade_rules_f.rate_id%TYPE
86 ,p_rate_type IN pay_grade_rules_f.rate_type%TYPE
87 ,p_business_group_id IN pay_grade_rules_f.business_group_id%TYPE
88 ,p_grade_or_spinal_point_id IN pay_grade_rules_f.grade_or_spinal_point_id%TYPE
89 ,p_effective_date IN DATE
90 ,p_grade_rule_id IN pay_grade_rules_f.grade_rule_id%TYPE
91 ,p_object_version_number IN pay_grade_rules_f.object_version_number%TYPE) IS
92 --
93 CURSOR csr_chk_rate IS
94 SELECT rate_id
95 FROM pay_rates
96 WHERE rate_id = p_rate_id
97 AND business_group_id = p_business_group_id;
98 --
99 CURSOR csr_duplicate_rate IS
100 SELECT rate_id
101 FROM pay_grade_rules_f pgr
102 WHERE pgr.rate_id = p_rate_id
103 AND pgr.grade_or_spinal_point_id = p_grade_or_spinal_point_id
104 AND ( (p_grade_rule_id IS NULL
105 AND pgr.effective_end_date > p_effective_date)
106 OR (p_grade_rule_id IS NOT NULL
107 AND pgr.grade_rule_id <> p_grade_rule_id));
108 --
109 CURSOR csr_dupl_asg_rate_type IS
110 SELECT pr1.rate_id
111 FROM pay_grade_rules_f pgr
112 ,pay_rates pr1
113 ,pay_rates pr2
114 WHERE pgr.grade_or_spinal_point_id = p_grade_or_spinal_point_id
115 AND ( (p_grade_rule_id IS NULL
116 AND pgr.effective_end_date > p_effective_date)
117 OR (p_grade_rule_id IS NOT NULL
118 AND pgr.grade_rule_id <> p_grade_rule_id
119 AND p_effective_date BETWEEN pgr.effective_start_date
120 AND pgr.effective_end_date))
121 AND pgr.rate_type = 'A'
122 AND pgr.rate_id = pr1.rate_id
123 AND p_rate_type = 'A'
124 AND p_rate_id = pr2.rate_id
125 AND nvl(pr2.asg_rate_type,'X') = nvl(pr1.asg_rate_type,'Y');
126 --
127 l_proc VARCHAR2(72) := g_package||'chk_rate_id';
128 l_dummy_id NUMBER(15);
129 --
130 BEGIN
131 --
132 hr_utility.set_location('Entering : '||l_proc,10);
133 --
134 -- Only proceed with validation if :
135 -- a) The rate_type is changing or new
136 -- b) The value for rate_type is changing and not null
137 --
138 IF ( (p_grade_rule_id IS NULL) OR
139 ((p_grade_rule_id IS NOT NULL) AND
140 (pay_pgr_shd.g_old_rec.rate_id <> p_rate_id))) THEN
141 --
142 hr_utility.set_location(l_proc, 30);
143 --
144 -- Check that the rate exists.
145 --
146 OPEN csr_chk_rate;
147 FETCH csr_chk_rate INTO l_dummy_id;
148 --
149 IF csr_chk_rate%NOTFOUND THEN
150 --
151 CLOSE csr_chk_rate;
152 --
153 hr_utility.set_message(800, 'HR_289683_INVALID_ASG_RATE');
154 hr_utility.raise_error;
155 --
156 ELSE
157 --
158 CLOSE csr_chk_rate;
159 --
160 END IF;
161 --
162 hr_utility.set_location(l_proc, 40);
163 --
164 -- Check that the rate type has not been defined
165 -- more than once for the grade, scale or assignment
166 --
167 OPEN csr_duplicate_rate;
168 FETCH csr_duplicate_rate INTO l_dummy_id;
169 --
170 IF csr_duplicate_rate%FOUND THEN
171 --
172 CLOSE csr_duplicate_rate;
173 --
174 IF p_rate_type = 'A' THEN
175 --
176 hr_utility.set_message(800, 'HR_289684_ASG_RATE_USED');
177 hr_utility.raise_error;
178 --
179 ELSIF p_rate_type = 'SP' THEN
180 --
181 hr_utility.set_message(800, 'HR_289686_PROG_POINT_ALREADY_U');
182 hr_utility.raise_error;
183 --
184 ELSIF p_rate_type = 'G' THEN
185 --
186 hr_utility.set_message(800, 'HR_289685_GRADE_RATE_ALREADY_U');
187 hr_utility.raise_error;
188 --
189 END IF;
190 --
191 ELSE
192 --
193 CLOSE csr_duplicate_rate;
194 --
195 -- Check if the same assignment already has a row of the same asg_rate_type
196 --
197 IF p_rate_type='A' THEN
198 OPEN csr_dupl_asg_rate_type;
199 FETCH csr_dupl_asg_rate_type into l_dummy_id;
200 IF csr_dupl_asg_rate_type%found THEN
201 CLOSE csr_dupl_asg_rate_type;
202 hr_utility.set_message(800, 'HR_449036_ASGRAT_DUP_ASGRAT');
203 hr_utility.raise_error;
204 ELSE
205 CLOSE csr_dupl_asg_rate_type;
206 END IF;
207 END IF;
208 END IF;
209 --
210 END IF;
211 --
212 hr_utility.set_location(' Leaving : '||l_proc,999);
213 --
214 EXCEPTION
215 --
216 WHEN app_exception.application_exception THEN
217 --
218 IF hr_multi_message.exception_add
219 (p_associated_column1 => 'PAY_GRADE_RULES_F.RATE_ID') THEN
220 --
221 hr_utility.set_location(' Leaving : '||l_proc,998);
222 --
223 RAISE;
224 --
225 END IF;
226 --
227 hr_utility.set_location(' Leaving : '||l_proc,997);
228 --
229 END chk_rate_id;
230 --
231 -- ---------------------------------------------------------------------------
232 -- |-------------------< chk_grade_or_spinal_point_id >----------------------|
233 -- ---------------------------------------------------------------------------
234 --
235 PROCEDURE chk_grade_or_spinal_point_id
236 (p_grade_rule_id IN pay_grade_rules_f.grade_rule_id%TYPE
237 ,p_business_group_id IN pay_grade_rules_f.business_group_id%TYPE
238 ,p_effective_date IN DATE
239 ,p_grade_or_spinal_point_id IN pay_grade_rules_f.grade_or_spinal_point_id%TYPE
240 ,p_rate_type IN pay_grade_rules_f.rate_type%TYPE
241 ,p_object_version_number IN pay_grade_rules_f.object_version_number%TYPE) IS
242 --
243 -- Delcare Local Variables
244 --
245 l_proc VARCHAR2(72) := g_package || 'chk_grade_or_spinal_point_id';
246 --l_eligy_prfl_id per_cagr_entitlement_lines_f.oipl_id%TYPE;
247 l_assignment_id per_assignments_f.assignment_id%TYPE;
248 l_grade_id per_grades.grade_id%TYPE;
249 l_spinal_point_id per_spinal_points.spinal_point_id%TYPE;
250 l_dummy_id NUMBER(15);
251 --
252 --
253 -- Delcare Cursors
254 --
255 CURSOR csr_chk_assignment_id IS
256 SELECT paf.assignment_id
257 FROM per_assignments_f PAF
258 WHERE paf.business_group_id = p_business_group_id
259 AND paf.assignment_id = l_assignment_id
260 AND paf.assignment_type = 'C'
261 AND p_effective_date BETWEEN paf.effective_start_date
262 AND paf.effective_end_date;
263 --
264 CURSOR csr_chk_grade_id IS
265 SELECT g.grade_id
266 FROM per_grades g
267 WHERE g.grade_id = l_grade_id
268 AND p_effective_date BETWEEN g.date_from
269 AND nvl(g.date_to, p_effective_date + 1)
270 AND g.business_group_id = p_business_group_id; -- Bug 3640364
271 --
272 CURSOR csr_chk_spinal_point_id IS
273 SELECT spinal_point_id psp
274 FROM per_spinal_points psp
275 WHERE psp.business_group_id = p_business_group_id
276 AND psp.spinal_point_id = l_spinal_point_id;
277 --
278 BEGIN
279 --
280 hr_utility.set_location('Entering:'||l_proc, 10);
281 --
282 hr_api.mandatory_arg_error
283 (p_api_name => l_proc
284 ,p_argument => 'grade_or_spinal_point_id'
285 ,p_argument_value => p_grade_or_spinal_point_id);
286 --
287 hr_utility.set_location(l_proc, 20);
288 --
289 -- Only proceed with validation if :
290 -- a) The grade_or_spinal_point_id is changing or new
291 -- b) The value for grade_or_spinal_point_id is changing and not null
292 --
293 IF ( (p_grade_rule_id IS NULL) OR
294 ((p_grade_rule_id IS NOT NULL) AND
295 (pay_pgr_shd.g_old_rec.grade_or_spinal_point_id <>
296 p_grade_or_spinal_point_id))) THEN
297 --
298 hr_utility.set_location(l_proc, 30);
299 --
300 -- IF the rate type is for Assignment Rates then
301 -- check that p_grade_or_spinal_point_id exists in
302 -- per_assignments_f and that the assignment is
303 -- a CWK assignment.
304 --
305 IF p_rate_type = 'A' THEN
306 hr_utility.set_location(l_proc,40);
307 l_assignment_id := p_grade_or_spinal_point_id;
308 --
309 OPEN csr_chk_assignment_id;
310 FETCH csr_chk_assignment_id INTO l_dummy_id;
311 IF csr_chk_assignment_id%NOTFOUND THEN
312 CLOSE csr_chk_assignment_id;
313 hr_utility.set_message(800, 'HR_289541_PJU_INV_ASG_ID');
314 hr_utility.raise_error;
315 ELSE
316 CLOSE csr_chk_assignment_id;
317 END IF;
318 --
319 -- IF the rate type is for Grade Rates then
320 -- check that p_grade_or_spinal_point_id exists in
321 -- per_grades
322 --
323 ELSIF p_rate_type = 'G' THEN
324 --
325 hr_utility.set_location(l_proc,60);
326 l_grade_id := p_grade_or_spinal_point_id;
327 --
328 OPEN csr_chk_grade_id;
329 FETCH csr_chk_grade_id INTO l_dummy_id;
330 IF csr_chk_grade_id%NOTFOUND THEN
331 CLOSE csr_chk_grade_id;
332 hr_utility.set_message(800, 'HR_PSF_INVALID_GRADE');
333 hr_utility.raise_error;
334 ELSE
335 CLOSE csr_chk_grade_id;
336 END IF;
337 --
338 -- IF the rate type is for Scale Rates then
339 -- check that p_grade_or_spinal_point_id exists in
340 -- per_spinal_points
341 --
342 ELSIF p_rate_type = 'SP' THEN
343 --
344 hr_utility.set_location(l_proc,80);
345 l_spinal_point_id := p_grade_or_spinal_point_id;
346 --
347 OPEN csr_chk_spinal_point_id;
348 FETCH csr_chk_spinal_point_id INTO l_dummy_id;
349 IF csr_chk_spinal_point_id%NOTFOUND THEN
350 CLOSE csr_chk_spinal_point_id;
351 hr_utility.set_message(800, 'HR_289687_SPINAL_POINT_INV');
352 hr_utility.raise_error;
353 ELSE
354 CLOSE csr_chk_spinal_point_id;
355 END IF;
356 END IF;
357 END IF;
358 --
359 hr_utility.set_location('Leaving: '||l_proc,997);
360 --
361 EXCEPTION
362 --
363 WHEN app_exception.application_exception THEN
364 IF hr_multi_message.exception_add
365 (p_associated_column1 => 'PAY_GRADE_RULES_F.GRADE_OR_SPINAL_POINT_ID') THEN
366 hr_utility.set_location(' Leaving: '|| l_proc,998);
367 --
368 RAISE;
369 --
370 END IF;
371 hr_utility.set_location(' Leaving: '||l_proc,999);
372 --
373 END chk_grade_or_spinal_point_id;
374 --
375 -- ---------------------------------------------------------------------------
376 -- |--------------------------< chk_currency_code >--------------------------|
377 -- ---------------------------------------------------------------------------
378 --
379 PROCEDURE chk_currency_code
380 (p_rate_type IN pay_grade_rules_f.rate_type%TYPE
381 ,p_currency_code IN pay_grade_rules_f.currency_code%TYPE
382 ,p_grade_rule_id IN pay_grade_rules_f.grade_rule_id%TYPE
383 ,p_rate_id IN pay_grade_rules_f.rate_id%TYPE
384 ,p_effective_date IN DATE
385 ,p_object_version_number IN pay_grade_rules_f.object_version_number%TYPE) IS
386 --
387 l_proc VARCHAR2(72) := g_package||'chk_currency_code';
388 l_dummy_code fnd_currencies.currency_code%TYPE;
389 l_rate_uom pay_rates.rate_uom%TYPE;
390 --
391 CURSOR csr_chk_currency_code IS
392 SELECT fc.currency_code
393 FROM fnd_currencies fc
394 WHERE fc.currency_code = p_currency_code;
395 --
396 CURSOR csr_get_rate_uom IS
397 SELECT pr.rate_uom
398 FROM pay_rates pr
399 WHERE pr.rate_id = p_rate_id;
400 --
401 BEGIN
402 --
403 hr_utility.set_location('Entering : '||l_proc,10);
404 --
405 -- Only proceed with validation if :
406 -- a) The currency is changing or new
407 -- b) The value for currency is changing and not null
408 --
409 IF ( (p_grade_rule_id IS NULL) OR
410 ((p_grade_rule_id IS NOT NULL) AND
411 (pay_pgr_shd.g_old_rec.currency_code <> p_currency_code))) THEN
412 --
413 hr_utility.set_location(l_proc, 30);
414 --
415 -- If the rate is for assignment rates then check
419 --
416 -- the currency code is not null and exists.
417 --
418 IF p_rate_type = 'A' THEN
420 hr_utility.set_location(l_proc, 40);
421 --
422 -- If the currency code is NULL then raise an error
423 -- as the currency code is mandatory when the rate_type is for
424 -- assignment rates.
425 --
426 IF p_currency_code IS NULL THEN
427 --
428 hr_utility.set_message(800, 'HR_289688_CURRENCY_CODE_NULL');
429 hr_utility.raise_error;
430 --
431 -- If the currency code is not null then check that
432 -- the currency code exists.
433 --
434 ELSE
435 --
436 hr_utility.set_location(l_proc, 50);
437 --
438 OPEN csr_chk_currency_code;
439 FETCH csr_chk_currency_code INTO l_dummy_code;
440 --
441 IF csr_chk_currency_code%NOTFOUND THEN
442 --
443 CLOSE csr_chk_currency_code;
444 --
445 hr_utility.set_message(800, 'HR_289705_INVALID_CURRENCY');
446 hr_utility.raise_error;
447 --
448 ELSE
449 --
450 CLOSE csr_chk_currency_code;
451 --
452 END IF;
453 --
454 END IF;
455 --
456 hr_utility.set_location(l_proc, 60);
457 --
458 -- If the rate is a Grade or Scale Rate and
459 -- the currency code has been populated then
460 -- validate it.
461 --
462 ELSIF p_rate_type <> 'A' THEN
463 --
464 hr_utility.set_location(l_proc, 70);
465 --
466 -- Fetch the rate unit of measure for the pay rate
467 --
468 OPEN csr_get_rate_uom;
469 FETCH csr_get_rate_uom INTO l_rate_uom;
470 --
471 CLOSE csr_get_rate_uom;
472 --
473 -- If the unit of measure has not been set to Money and the
474 -- currency code has been populated then raise an error.
475 --
476 IF l_rate_uom <> 'M' AND p_currency_code IS NOT NULL THEN
477 --
478 hr_utility.set_message(800, 'HR_289689_CCY_CODE_NOT_NULL');
479 hr_utility.raise_error;
480 --
481 -- If the unit of measure has been set to Money and the
482 -- currency code has NOT been populated then raise an error.
483 --
484 ELSIF l_rate_uom = 'M' AND p_currency_code IS NULL THEN
485 --
486 -- Fix for bug 3380687 starts here.
487 -- for rate type G and SP the currency is optional.
488 --
489 IF p_rate_type <> 'G' and p_rate_type <> 'SP' then
490 --
491 hr_utility.set_message(800, 'HR_289688_CURRENCY_CODE_NULL');
492 hr_utility.raise_error;
493 --
494 END IF;
495 --
496 -- Fix for bug 3380687 ends here.
497 --
498 -- If the unit of measure has been set to Money and the
499 -- cuurency has been populated then check that the currency
500 -- code exists.
501 --
502 ELSIF l_rate_uom = 'M' AND p_currency_code IS NOT NULL THEN
503 --
504 hr_utility.set_location(l_proc, 80);
505 --
506 OPEN csr_chk_currency_code;
507 FETCH csr_chk_currency_code INTO l_dummy_code;
508 --
509 IF csr_chk_currency_code%NOTFOUND THEN
510 --
511 CLOSE csr_chk_currency_code;
512 --
513 hr_utility.set_message(800, 'HR_289705_INVALID_CURRENCY');
514 hr_utility.raise_error;
515 --
516 ELSE
517 --
518 CLOSE csr_chk_currency_code;
519 --
520 END IF;
521 --
522 END IF;
523 --
524 END IF;
525 --
526 END IF;
527 --
528 hr_utility.set_location(' Leaving : '||l_proc,999);
529 --
530 EXCEPTION
531 --
532 WHEN app_exception.application_exception THEN
533 --
534 IF hr_multi_message.exception_add
535 (p_associated_column1 => 'PAY_GRADE_RULES_F.CURRENCY_CODE') THEN
536 --
537 hr_utility.set_location(' Leaving : '||l_proc,998);
538 --
539 RAISE;
540 --
541 END IF;
542 --
543 hr_utility.set_location(' Leaving : '||l_proc,999);
544 --
545 END chk_currency_code;
546 --
547 -- ---------------------------------------------------------------------------
548 -- |--------------------------< chk_values_format >--------------------------|
549 -- ---------------------------------------------------------------------------
550 --
551 PROCEDURE chk_values_format(p_rate_id number
552 ,p_rate_type varchar2
553 ,p_value varchar2
554 ,p_minimum varchar2
555 ,p_maximum varchar2
556 ,p_mid_value varchar2
557 ,p_curcode varchar2) IS
558 --
559 l_output varchar2(255);
560 l_rgeflg varchar2(255);
561 l_format varchar2(255);
562 l_input varchar2(255);
563 l_proc VARCHAR2(72) := g_package||'chk_values_format';
564 l_bg_id pay_rates.business_group_id%type; -- added for bug 6016428 (for backward compatibility)
568 CURSOR csr_get_rate_uom IS
565 l_bg_curr_code per_business_groups.currency_code%type; -- added for bug 6016428 (for backward compatibility)
566
567 --
569 SELECT pr.rate_uom
570 FROM pay_rates pr
571 WHERE pr.rate_id = p_rate_id;
572 --
573
574 CURSOR csr_bg_curr_cd(l_bg_id number) IS
575 SELECT org_information10
576 FROM hr_organization_information hoi
577 WHERE hoi.organization_id = l_bg_id
578 AND hoi.org_information_context = 'Business Group Information'
579 AND hoi.org_information2 IS NOT NULL
580 AND EXISTS
581 ( SELECT NULL
582 FROM hr_org_info_types_by_class oitbc,
583 hr_organization_information org_info
584 WHERE org_info.organization_id = hoi.organization_id
585 AND org_info.org_information_context = 'CLASS'
586 AND org_info.org_information2 = 'Y'
587 AND oitbc.org_classification = org_info.org_information1
588 AND oitbc.org_information_type = 'Business Group Information'
589 );
590 --
591
592 BEGIN
593 --
594 hr_utility.set_location('Entering : '||l_proc,10);
595 --
596 open csr_get_rate_uom;
597 fetch csr_get_rate_uom into l_format;
598 close csr_get_rate_uom;
599 --
600 -- Rate UOM is not null column. Therefore no need to check it again.
601 --
602
603 /* changes for bug 6016428 starts */
604 hr_utility.set_location('Getting business Group ID ',11);
605 select business_group_id
606 into l_bg_id
607 from pay_rates
608 where rate_id = p_rate_id;
609 --
610 hr_utility.set_location('Getting default Currency code for business Group ID ',12);
611 OPEN csr_bg_curr_cd(l_bg_id);
612 FETCH csr_bg_curr_cd INTO l_bg_curr_code;
613 CLOSE csr_bg_curr_cd;
614 /* changes for bug 6016428 ends */
615
616 --
617 if p_value is not null then
618 --
619 hr_utility.set_location(l_proc, 15);
620 --for bug 5882341, checkformat require p_value argument in a formated
621 -- string not in a canonical string, so we use the changeformat function
622 -- which change the canonical string into Formated string..
623 -- insted of l_input := p_value; we are using following call
624
625 -- l_input := p_value;
626 hr_chkfmt.changeformat( input =>p_value,
627 output => l_input,
628 format => l_format,
629 curcode => nvl(p_curcode, l_bg_curr_code)); -- Bug 6016428
630 --for bug 5882341
631 hr_chkfmt.checkformat(l_input
632 ,l_format
633 ,l_output
634 ,null
635 ,null
636 ,null
637 ,l_rgeflg
638 ,nvl(p_curcode, l_bg_curr_code)); -- Bug 6016428
639 --
640 end if;
641 --
642 if p_maximum is not null then
643 --
644 hr_utility.set_location(l_proc, 20);
645 --start changes for bug 6346419
646 --l_input := p_maximum;
647 hr_chkfmt.changeformat( input =>p_maximum,
648 output => l_input,
649 format => l_format,
650 curcode => nvl(p_curcode, l_bg_curr_code)); -- Bug 6016428
651 --end changes for bug 6346419
652 hr_chkfmt.checkformat(l_input
653 ,l_format
654 ,l_output
655 ,null
656 ,null
657 ,null
658 ,l_rgeflg
659 ,nvl(p_curcode, l_bg_curr_code)); -- Bug 6016428
660 --
661 end if;
662 --
663 if p_minimum is not null then
664 --
665 hr_utility.set_location(l_proc, 30);
666 --start changes for bug 6346419
667 --l_input := p_minimum;
668 hr_chkfmt.changeformat( input =>p_minimum,
669 output => l_input,
670 format => l_format,
671 curcode => nvl(p_curcode, l_bg_curr_code)); -- Bug 6016428
672 --end changes for bug 6346419
673 hr_chkfmt.checkformat(l_input
674 ,l_format
675 ,l_output
676 ,null
677 ,null
678 ,null
679 ,l_rgeflg
680 ,nvl(p_curcode, l_bg_curr_code)); -- Bug 6016428
681 --
682 end if;
683 --
684 if p_mid_value is not null then
685 --
686 hr_utility.set_location(l_proc, 40);
687 --start changes for bug 6346419
688 --l_input := p_mid_value;
689 hr_chkfmt.changeformat( input =>p_mid_value,
690 output => l_input,
691 format => l_format,
692 curcode => nvl(p_curcode, l_bg_curr_code)); -- Bug 6016428
693 --end changes for bug 6346419
694 hr_chkfmt.checkformat(l_input
695 ,l_format
696 ,l_output
697 ,null
698 ,null
699 ,null
700 ,l_rgeflg
701 ,nvl(p_curcode, l_bg_curr_code)); -- Bug 6016428
702 --
703 end if;
704 --
705 hr_utility.set_location('Leaving : '||l_proc,999);
706 --
707 END chk_values_format;
708 --
712 --
709 -- ---------------------------------------------------------------------------
710 -- |----------------------< chk_assignment_rate_value >----------------------|
711 -- ---------------------------------------------------------------------------
713 PROCEDURE chk_assignment_rate_value
714 (p_value IN pay_grade_rules_f.value%TYPE) IS
715 --
716 l_proc VARCHAR2(72) := g_package||'chk_assignment_rate_value';
717 l_value NUMBER;
718 --
719 BEGIN
720 --
721 hr_utility.set_location('Entering : '||l_proc,10);
722 --
723 -- Copy the value into a numeric variable.
724 -- If p_value contains anything apart from numbers
725 -- then a VALUE_ERROR will be raised and
726 -- trapped in the exception handler.
727 --
728 l_value := p_value;
729 --
730 hr_utility.set_location('Leaving : '||l_proc,999);
731 --
732 EXCEPTION
733 --
734 WHEN VALUE_ERROR THEN
735 --
736 hr_utility.set_message(800, 'HR_289690_ASS_RATE_VAL_NOT_NUM');
737 hr_utility.raise_error;
738 --
739 END chk_assignment_rate_value;
740 --
741 -- ---------------------------------------------------------------------------
742 -- |-------------------------------< chk_value >-----------------------------|
743 -- ---------------------------------------------------------------------------
744 --
745 PROCEDURE chk_value
746 (p_rate_type IN pay_grade_rules_f.rate_type%TYPE
747 ,p_value IN pay_grade_rules_f.value%TYPE
748 ,p_grade_rule_id IN pay_grade_rules_f.grade_rule_id%TYPE
749 ,p_currency_code IN pay_grade_rules_f.currency_code%TYPE
750 ,p_effective_date IN DATE
751 ,p_object_version_number IN pay_grade_rules_f.object_version_number%TYPE) IS
752 --
753 l_proc VARCHAR2(72) := g_package||'chk_value';
754 l_value NUMBER;
755 --
756 BEGIN
757 --
758 hr_utility.set_location('Entering : '||l_proc,10);
759 --
760 -- Only proceed with validation if :
761 -- a) Inserting or
762 -- b) The value for value has changed
763 --
764 IF ( (p_grade_rule_id IS NULL) OR
765 ((p_grade_rule_id IS NOT NULL) AND
766 (pay_pgr_shd.g_old_rec.value <> p_value))) THEN
767 --
768 hr_utility.set_location(l_proc, 30);
769 --
770 -- If the rate is for assignment rates and the
771 -- value field is null then raise an error.
772 --
773 IF p_rate_type = 'A' THEN
774 --
775 hr_utility.set_location(l_proc, 40);
776 --
777 IF p_value IS NULL
778 OR sign(p_value) = -1 THEN
779 --
780 hr_utility.set_message(800, 'HR_289691_ASS_RATE_VALUE_NULL');
781 hr_utility.raise_error;
782 --
783 ELSE
784 --
785 hr_utility.set_location(l_proc, 50);
786 --
787 -- Check that the p_value is in numeric format
788 --
789 chk_assignment_rate_value(p_value => p_value);
790 --
791 END IF;
792 --
793 -- IF the rate is for assignment rates then check
794 -- the currency and value field combination.
795 --
796 ELSIF p_rate_type <> 'A' THEN
797 --
798 hr_utility.set_location(l_proc, 60);
799 --
800 -- If the currency code has been entered then
801 -- check that the value field has also been entered.
802 --
803 -- Fix for bug 3049789 starts here.
804 -- No check needed for the rate type other than A.
805 --
806 null;
807 /*
808 IF p_currency_code IS NOT NULL and p_value IS NULL THEN
809 --
810 hr_utility.set_message(800, 'HR_289706_VALUE_NULL');
811 hr_utility.raise_error;
812 --
813 END IF;
814 */
815 --
816 -- Fix for bug 3049789 ends here.
817 --
818 END IF;
819 --
820 END IF;
821 --
822 hr_utility.set_location(' Leaving : '||l_proc,997);
823 --
824 EXCEPTION
825 --
826 WHEN app_exception.application_exception THEN
827 --
828 IF hr_multi_message.exception_add
829 (p_associated_column1 => 'PAY_GRADE_RULES_F.VALUE') THEN
830 --
831 hr_utility.set_location(' Leaving : '||l_proc,998);
832 --
833 RAISE;
834 --
835 END IF;
836 --
837 hr_utility.set_location(' Leaving : '||l_proc,999);
838 --
839 END chk_value;
840 --
841 -- Fix for bug 3049789 starts here.
842 --
843 -- ---------------------------------------------------------------------------
844 -- |-------------------------< chk_value_min_max_comb >-----------------------|
845 -- ---------------------------------------------------------------------------
846 --
847 PROCEDURE chk_value_min_max_comb
848 (p_rate_type IN pay_grade_rules_f.rate_type%TYPE
849 ,p_grade_rule_id IN pay_grade_rules_f.grade_rule_id%TYPE
850 ,p_value IN pay_grade_rules_f.value%TYPE
851 ,p_minimum IN pay_grade_rules_f.minimum%TYPE
852 ,p_maximum IN pay_grade_rules_f.maximum%TYPE) IS
853 --
854 l_proc VARCHAR2(72) := g_package||'chk_value_min_max_comb';
858 --
855 l_value NUMBER;
856 --
857 BEGIN
859 hr_utility.set_location('Entering : '||l_proc,10);
860 --
861 -- Only proceed with validation if :
862 -- a) Inserting or
863 -- b) The value for value has changed
864 --
865 IF ( (p_grade_rule_id IS NULL) OR
866 ((p_grade_rule_id IS NOT NULL) AND
867 (nvl(pay_pgr_shd.g_old_rec.value,-1) <> nvl(p_value,-1) OR
868 nvl(pay_pgr_shd.g_old_rec.minimum,-1) <> nvl(p_minimum,-1) OR
869 nvl(pay_pgr_shd.g_old_rec.maximum,-1) <> nvl(p_maximum,-1) ))) THEN
870 --
871 hr_utility.set_location(l_proc, 30);
872 --
873 -- If the rate is for Grade rates, either value OR a minimum and Maximum
874 -- should be specified.
875 --
876 IF p_rate_type = 'G' THEN
877 --
878 hr_utility.set_location(l_proc, 40);
879 IF p_value IS NULL and
880 (p_minimum IS NULL or p_maximum IS NULL) THEN
881 --
882 hr_utility.set_message(800, 'PER_449141_VALUE_OR_MIN_MAX');
883 hr_utility.raise_error;
884 --
885 END IF;
886 --
887 END IF;
888 --
889 END IF;
890 --
891 hr_utility.set_location(' Leaving : '||l_proc,997);
892 --
893 EXCEPTION
894 --
895 WHEN app_exception.application_exception THEN
896 --
897 IF hr_multi_message.exception_add
898 (p_associated_column1 => 'PAY_GRADE_RULES_F.VALUE') THEN
899 --
900 hr_utility.set_location(' Leaving : '||l_proc,998);
901 --
902 RAISE;
903 --
904 END IF;
905 --
906 hr_utility.set_location(' Leaving : '||l_proc,999);
907 --
908 END chk_value_min_max_comb;
909 --
910 -- Fix for bug 3049789 ends here.
911 --
912 -- ---------------------------------------------------------------------------
913 -- |----------------------< set_security_group_id >--------------------------|
914 -- ---------------------------------------------------------------------------
915 --
916 Procedure set_security_group_id
917 (p_grade_rule_id in number
918 ,p_associated_column1 in varchar2 default null
919 ) is
920 --
921 -- Declare cursor
922 --
923 cursor csr_sec_grp is
924 select pbg.security_group_id
925 from per_business_groups pbg
926 , pay_grade_rules_f pgr
927 where pgr.grade_rule_id = p_grade_rule_id
928 and pbg.business_group_id = pgr.business_group_id;
929 --
930 -- Declare local variables
931 --
932 l_security_group_id number;
933 l_proc varchar2(72) := g_package||'set_security_group_id';
934 --
935 begin
936 --
937 hr_utility.set_location('Entering:'|| l_proc, 10);
938 --
939 -- Ensure that all the mandatory parameter are not null
940 --
941 hr_api.mandatory_arg_error
942 (p_api_name => l_proc
943 ,p_argument => 'grade_rule_id'
944 ,p_argument_value => p_grade_rule_id
945 );
946 --
947 open csr_sec_grp;
948 fetch csr_sec_grp into l_security_group_id;
949 --
950 if csr_sec_grp%notfound then
951 --
952 close csr_sec_grp;
953 --
954 -- The primary key is invalid therefore we must error
955 --
956 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
957 hr_multi_message.add
958 (p_associated_column1
959 => nvl(p_associated_column1,'GRADE_RULE_ID')
960 );
961 --
962 else
963 close csr_sec_grp;
964 --
965 -- Set the security_group_id in CLIENT_INFO
966 --
967 hr_api.set_security_group_id
968 (p_security_group_id => l_security_group_id
969 );
970 end if;
971 --
972 hr_utility.set_location(' Leaving:'|| l_proc, 20);
973 --
974 end set_security_group_id;
975 --
976 -- ---------------------------------------------------------------------------
977 -- |---------------------< return_legislation_code >-------------------------|
978 -- ---------------------------------------------------------------------------
979 --
980 Function return_legislation_code
981 (p_grade_rule_id in number
982 )
983 Return Varchar2 Is
984 --
985 -- Declare cursor
986 --
987 cursor csr_leg_code is
988 select pbg.legislation_code
989 from per_business_groups pbg
990 , pay_grade_rules_f pgr
991 where pgr.grade_rule_id = p_grade_rule_id
992 and pbg.business_group_id = pgr.business_group_id;
993 --
994 -- Declare local variables
995 --
996 l_legislation_code varchar2(150);
997 l_proc varchar2(72) := g_package||'return_legislation_code';
998 --
999 Begin
1000 --
1001 hr_utility.set_location('Entering:'|| l_proc, 10);
1002 --
1003 -- Ensure that all the mandatory parameter are not null
1004 --
1005 hr_api.mandatory_arg_error
1006 (p_api_name => l_proc
1007 ,p_argument => 'grade_rule_id'
1008 ,p_argument_value => p_grade_rule_id
1012 = p_grade_rule_id) then
1009 );
1010 --
1011 if ( nvl(pay_pgr_bus.g_grade_rule_id, hr_api.g_number)
1013 --
1014 -- The legislation code has already been found with a previous
1015 -- call to this function. Just return the value in the global
1016 -- variable.
1017 --
1018 l_legislation_code := pay_pgr_bus.g_legislation_code;
1019 hr_utility.set_location(l_proc, 20);
1020 else
1021 --
1022 -- The ID is different to the last call to this function
1023 -- or this is the first call to this function.
1024 --
1025 open csr_leg_code;
1026 fetch csr_leg_code into l_legislation_code;
1027 --
1028 if csr_leg_code%notfound then
1029 --
1030 -- The primary key is invalid therefore we must error
1031 --
1032 close csr_leg_code;
1033 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1034 fnd_message.raise_error;
1035 end if;
1036 hr_utility.set_location(l_proc,30);
1037 --
1038 -- Set the global variables so the values are
1039 -- available for the next call to this function.
1040 --
1041 close csr_leg_code;
1042 pay_pgr_bus.g_grade_rule_id := p_grade_rule_id;
1043 pay_pgr_bus.g_legislation_code := l_legislation_code;
1044 end if;
1045 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1046 return l_legislation_code;
1047 end return_legislation_code;
1048 --
1049 -- ----------------------------------------------------------------------------
1050 -- |-----------------------< chk_non_updateable_args >------------------------|
1051 -- ----------------------------------------------------------------------------
1052 -- {Start Of Comments}
1053 --
1054 -- Description:
1055 -- This procedure is used to ensure that non updateable attributes have
1056 -- not been updated. If an attribute has been updated an error is generated.
1057 --
1058 -- Pre Conditions:
1059 -- g_old_rec has been populated with details of the values currently in
1060 -- the database.
1061 --
1062 -- In Arguments:
1063 -- p_rec has been populated with the updated values the user would like the
1064 -- record set to.
1065 --
1066 -- Post Success:
1067 -- Processing continues if all the non updateable attributes have not
1068 -- changed.
1069 --
1070 -- Post Failure:
1071 -- An application error is raised if any of the non updatable attributes
1072 -- have been altered.
1073 --
1074 -- {End Of Comments}
1075 -- ----------------------------------------------------------------------------
1076 Procedure chk_non_updateable_args
1077 (p_effective_date in date
1078 ,p_rec in pay_pgr_shd.g_rec_type
1079 ) IS
1080 --
1081 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
1082 --
1083 Begin
1084 --
1085 -- Only proceed with the validation if a row exists for the current
1086 -- record in the HR Schema.
1087 --
1088 IF NOT pay_pgr_shd.api_updating
1089 (p_grade_rule_id => p_rec.grade_rule_id
1090 ,p_effective_date => p_effective_date
1091 ,p_object_version_number => p_rec.object_version_number
1092 ) THEN
1093 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
1094 fnd_message.set_token('PROCEDURE ', l_proc);
1095 fnd_message.set_token('STEP ', '5');
1096 fnd_message.raise_error;
1097 END IF;
1098 --
1099 End chk_non_updateable_args;
1100 --
1101 -- ----------------------------------------------------------------------------
1102 -- |--------------------------< dt_update_validate >--------------------------|
1103 -- ----------------------------------------------------------------------------
1104 -- {Start Of Comments}
1105 --
1106 -- Description:
1107 -- This procedure is used for referential integrity of datetracked
1108 -- parent entities when a datetrack update operation is taking place
1109 -- and where there is no cascading of update defined for this entity.
1110 --
1111 -- Prerequisites:
1112 -- This procedure is called from the update_validate.
1113 --
1114 -- In Parameters:
1115 --
1116 -- Post Success:
1117 -- Processing continues.
1118 --
1119 -- Post Failure:
1120 --
1121 -- Developer Implementation Notes:
1122 -- This procedure should not need maintenance unless the HR Schema model
1123 -- changes.
1124 --
1125 -- Access Status:
1126 -- Internal Row Handler Use Only.
1127 --
1128 -- {End Of Comments}
1129 -- ----------------------------------------------------------------------------
1130 Procedure dt_update_validate
1131 (p_datetrack_mode in varchar2
1132 ,p_validation_start_date in date
1133 ,p_validation_end_date in date
1134 ) Is
1135 --
1136 l_proc varchar2(72) := g_package||'dt_update_validate';
1137 --
1138 Begin
1139 --
1140 -- Ensure that the p_datetrack_mode argument is not null
1141 --
1142 hr_api.mandatory_arg_error
1143 (p_api_name => l_proc
1144 ,p_argument => 'datetrack_mode'
1145 ,p_argument_value => p_datetrack_mode
1146 );
1147 --
1148 -- Mode will be valid, as this is checked at the start of the upd.
1149 --
1150 -- Ensure the arguments are not null
1151 --
1155 ,p_argument_value => p_validation_start_date
1152 hr_api.mandatory_arg_error
1153 (p_api_name => l_proc
1154 ,p_argument => 'validation_start_date'
1156 );
1157 --
1158 hr_api.mandatory_arg_error
1159 (p_api_name => l_proc
1160 ,p_argument => 'validation_end_date'
1161 ,p_argument_value => p_validation_end_date
1162 );
1163 --
1164 --
1165 --
1166 Exception
1167 When Others Then
1168 --
1169 -- An unhandled or unexpected error has occurred which
1170 -- we must report
1171 --
1172 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1173 fnd_message.set_token('PROCEDURE', l_proc);
1174 fnd_message.set_token('STEP','15');
1175 fnd_message.raise_error;
1176 End dt_update_validate;
1177 --
1178 -- ----------------------------------------------------------------------------
1179 -- |--------------------------< dt_delete_validate >--------------------------|
1180 -- ----------------------------------------------------------------------------
1181 -- {Start Of Comments}
1182 --
1183 -- Description:
1184 -- This procedure is used for referential integrity of datetracked
1185 -- child entities when either a datetrack DELETE or ZAP is in operation
1186 -- and where there is no cascading of delete defined for this entity.
1187 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1188 -- datetracked child rows exist between the validation start and end
1189 -- dates.
1190 --
1191 -- Prerequisites:
1192 -- This procedure is called from the delete_validate.
1193 --
1194 -- In Parameters:
1195 --
1196 -- Post Success:
1197 -- Processing continues.
1198 --
1199 -- Post Failure:
1200 -- If a row exists by determining the returning Boolean value from the
1201 -- generic dt_api.rows_exist function then we must supply an error via
1202 -- the use of the local exception handler l_rows_exist.
1203 --
1204 -- Developer Implementation Notes:
1205 -- This procedure should not need maintenance unless the HR Schema model
1206 -- changes.
1207 --
1208 -- Access Status:
1209 -- Internal Row Handler Use Only.
1210 --
1211 -- {End Of Comments}
1212 -- ----------------------------------------------------------------------------
1213 Procedure dt_delete_validate
1214 (p_grade_rule_id in number
1215 ,p_datetrack_mode in varchar2
1216 ,p_validation_start_date in date
1217 ,p_validation_end_date in date
1218 ) Is
1219 --
1220 l_proc varchar2(72) := g_package||'dt_delete_validate';
1221 --
1222 Begin
1223 --
1224 hr_utility.set_location('Entering:'||l_proc, 10);
1225 --
1226 -- Ensure that the p_datetrack_mode argument is not null
1227 --
1228 hr_api.mandatory_arg_error
1229 (p_api_name => l_proc
1230 ,p_argument => 'datetrack_mode'
1231 ,p_argument_value => p_datetrack_mode
1232 );
1233 --
1234 hr_utility.set_location('Entering:'||l_proc, 20);
1235 --
1236 -- Only perform the validation if the datetrack mode is either
1237 -- DELETE or ZAP
1238 --
1239 If (p_datetrack_mode = hr_api.g_delete or
1240 p_datetrack_mode = hr_api.g_zap) then
1241 --
1242 hr_utility.set_location('Entering:'||l_proc, 30);
1243 --
1244 -- Ensure the arguments are not null
1245 --
1246 hr_api.mandatory_arg_error
1247 (p_api_name => l_proc
1248 ,p_argument => 'validation_start_date'
1249 ,p_argument_value => p_validation_start_date
1250 );
1251 --
1252 hr_utility.set_location('Entering:'||l_proc, 40);
1253 --
1254 hr_api.mandatory_arg_error
1255 (p_api_name => l_proc
1256 ,p_argument => 'validation_end_date'
1257 ,p_argument_value => p_validation_end_date
1258 );
1259 --
1260 hr_api.mandatory_arg_error
1261 (p_api_name => l_proc
1262 ,p_argument => 'grade_rule_id'
1263 ,p_argument_value => p_grade_rule_id
1264 );
1265 --
1266 End If;
1267 --
1268 Exception
1269 When Others Then
1270 --
1271 -- An unhandled or unexpected error has occurred which
1272 -- we must report
1273 --
1274 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1275 fnd_message.set_token('PROCEDURE', l_proc);
1276 fnd_message.set_token('STEP','15');
1277 fnd_message.raise_error;
1278 --
1279 End dt_delete_validate;
1280 --
1281 -- ----------------------------------------------------------------------------
1282 -- |---------------------------< insert_validate >----------------------------|
1283 -- ----------------------------------------------------------------------------
1284 Procedure insert_validate
1285 (p_rec in pay_pgr_shd.g_rec_type
1286 ,p_effective_date in date
1287 ,p_datetrack_mode in varchar2
1288 ,p_validation_start_date in date
1289 ,p_validation_end_date in date
1290 ) is
1291 --
1292 l_proc varchar2(72) := g_package||'insert_validate';
1293 --
1294 Begin
1295 --
1296 hr_utility.set_location('Entering:'||l_proc, 10);
1297 --
1301 (p_business_group_id => p_rec.business_group_id
1298 -- Call all supporting business operations
1299 --
1300 hr_api.validate_bus_grp_id
1302 ,p_associated_column1 => pay_pgr_shd.g_tab_nam
1303 || '.BUSINESS_GROUP_ID');
1304 --
1305 hr_utility.set_location(l_proc, 20);
1306 --
1307 -- After validating the set of important attributes,
1308 -- if Multiple Message detection is enabled and at least
1309 -- one error has been found then abort further validation.
1310 --
1311 hr_multi_message.end_validation_set;
1312 --
1313 hr_utility.set_location(l_proc, 30);
1314 --
1315 --
1316 -- Validate Dependent Attributes
1317 --
1318 pay_pgr_bus.chk_rate_type
1319 (p_rate_type => p_rec.rate_type
1320 ,p_effective_date => p_effective_date
1321 ,p_grade_rule_id => p_rec.grade_rule_id
1322 ,p_object_version_number => p_rec.object_version_number
1323 ,p_validation_start_date => p_validation_start_date
1324 ,p_validation_end_date => p_validation_end_date);
1325 --
1326 hr_utility.set_location(l_proc, 40);
1327 --
1328 pay_pgr_bus.chk_grade_or_spinal_point_id
1329 (p_grade_rule_id => p_rec.grade_rule_id
1330 ,p_business_group_id => p_rec.business_group_id
1331 ,p_effective_date => p_effective_date
1332 ,p_grade_or_spinal_point_id => p_rec.grade_or_spinal_point_id
1333 ,p_rate_type => p_rec.rate_type
1334 ,p_object_version_number => p_rec.object_version_number);
1335 --
1336 hr_utility.set_location(l_proc, 50);
1337 --
1338 pay_pgr_bus.chk_currency_code
1339 (p_rate_type => p_rec.rate_type
1340 ,p_currency_code => p_rec.currency_code
1341 ,p_grade_rule_id => p_rec.grade_rule_id
1342 ,p_rate_id => p_rec.rate_id
1343 ,p_effective_date => p_effective_date
1344 ,p_object_version_number => p_rec.object_version_number);
1345 --
1346 hr_utility.set_location(l_proc, 60);
1347 --
1348 pay_pgr_bus.chk_value
1349 (p_rate_type => p_rec.rate_type
1350 ,p_value => p_rec.value
1351 ,p_grade_rule_id => p_rec.grade_rule_id
1352 ,p_currency_code => p_rec.currency_code
1353 ,p_effective_date => p_effective_date
1354 ,p_object_version_number => p_rec.object_version_number);
1355 --
1356 hr_utility.set_location(l_proc, 70);
1357 --
1358 -- Fix for bug 3049789 starts here.
1359 --
1360 pay_pgr_bus.chk_value_min_max_comb
1361 (p_rate_type => p_rec.rate_type
1362 ,p_grade_rule_id => p_rec.grade_rule_id
1363 ,p_value => p_rec.value
1364 ,p_minimum => p_rec.minimum
1365 ,p_maximum => p_rec.maximum);
1366 --
1367 hr_utility.set_location(l_proc, 80);
1368 --
1369 -- Fix for bug 3049789 ends here.
1370 --
1371 pay_pgr_bus.chk_rate_id
1372 (p_rate_id => p_rec.rate_id
1373 ,p_rate_type => p_rec.rate_type
1374 ,p_business_group_id => p_rec.business_group_id
1375 ,p_grade_or_spinal_point_id => p_rec.grade_or_spinal_point_id
1376 ,p_effective_date => p_effective_date
1377 ,p_grade_rule_id => p_rec.grade_rule_id
1378 ,p_object_version_number => p_rec.object_version_number);
1379 --
1380 hr_utility.set_location(l_proc, 90);
1381 --
1382 pay_pgr_bus.chk_values_format
1383 (p_rate_id => p_rec.rate_id
1384 ,p_rate_type => p_rec.rate_type
1385 ,p_value => p_rec.value
1386 ,p_minimum => p_rec.minimum
1387 ,p_maximum => p_rec.maximum
1388 ,p_mid_value => p_rec.mid_value
1389 ,p_curcode => p_rec.currency_code);
1390 --
1391 hr_utility.set_location(' Leaving:'||l_proc, 999);
1392 --
1393 End insert_validate;
1394 --
1395 -- ----------------------------------------------------------------------------
1396 -- |---------------------------< update_validate >----------------------------|
1397 -- ----------------------------------------------------------------------------
1398 Procedure update_validate
1399 (p_rec in pay_pgr_shd.g_rec_type
1400 ,p_effective_date in date
1401 ,p_datetrack_mode in varchar2
1402 ,p_validation_start_date in date
1403 ,p_validation_end_date in date
1404 ) is
1405 --
1406 l_proc varchar2(72) := g_package||'update_validate';
1407 --
1408 Begin
1409 hr_utility.set_location('Entering:'||l_proc, 10);
1410 --
1411 -- Call all supporting business operations
1412 --
1413 hr_api.validate_bus_grp_id
1414 (p_business_group_id => p_rec.business_group_id
1415 ,p_associated_column1 => pay_pgr_shd.g_tab_nam
1416 || '.BUSINESS_GROUP_ID');
1417 --
1418 hr_utility.set_location(l_proc, 20);
1419 --
1420 --
1421 -- After validating the set of important attributes,
1422 -- if Multiple Message detection is enabled and at least
1423 -- one error has been found then abort further validation.
1424 --
1425 hr_multi_message.end_validation_set;
1426 --
1427 hr_utility.set_location(l_proc, 30);
1428 --
1429 -- Validate Dependent Attributes
1430 --
1431 -- Call the datetrack update integrity operation
1432 --
1433 dt_update_validate
1434 (p_datetrack_mode => p_datetrack_mode
1435 ,p_validation_start_date => p_validation_start_date
1436 ,p_validation_end_date => p_validation_end_date
1437 );
1438 --
1439 hr_utility.set_location(l_proc, 40);
1440 --
1441 chk_non_updateable_args
1442 (p_effective_date => p_effective_date
1443 ,p_rec => p_rec
1444 );
1445 --
1446 hr_utility.set_location(l_proc, 50);
1447 --
1448 -- Validate Dependent Attributes
1449 --
1450 pay_pgr_bus.chk_rate_type
1451 (p_rate_type => p_rec.rate_type
1452 ,p_effective_date => p_effective_date
1453 ,p_grade_rule_id => p_rec.grade_rule_id
1454 ,p_object_version_number => p_rec.object_version_number
1455 ,p_validation_start_date => p_validation_start_date
1456 ,p_validation_end_date => p_validation_end_date);
1457 --
1458 hr_utility.set_location(l_proc, 60);
1459 --
1460 pay_pgr_bus.chk_grade_or_spinal_point_id
1461 (p_grade_rule_id => p_rec.grade_rule_id
1462 ,p_business_group_id => p_rec.business_group_id
1463 ,p_effective_date => p_effective_date
1464 ,p_grade_or_spinal_point_id => p_rec.grade_or_spinal_point_id
1465 ,p_rate_type => p_rec.rate_type
1466 ,p_object_version_number => p_rec.object_version_number);
1467 --
1468 hr_utility.set_location(l_proc, 70);
1469 --
1470 pay_pgr_bus.chk_currency_code
1471 (p_rate_type => p_rec.rate_type
1472 ,p_currency_code => p_rec.currency_code
1473 ,p_grade_rule_id => p_rec.grade_rule_id
1474 ,p_rate_id => p_rec.rate_id
1475 ,p_effective_date => p_effective_date
1476 ,p_object_version_number => p_rec.object_version_number);
1477 --
1478 hr_utility.set_location(l_proc, 80);
1479 --
1480 pay_pgr_bus.chk_value
1481 (p_rate_type => p_rec.rate_type
1482 ,p_value => p_rec.value
1483 ,p_grade_rule_id => p_rec.grade_rule_id
1484 ,p_currency_code => p_rec.currency_code
1485 ,p_effective_date => p_effective_date
1486 ,p_object_version_number => p_rec.object_version_number);
1487 --
1488 hr_utility.set_location(l_proc,90);
1489 --
1490 -- Fix for bug 3049789 starts here.
1491 --
1492 pay_pgr_bus.chk_value_min_max_comb
1493 (p_rate_type => p_rec.rate_type
1494 ,p_grade_rule_id => p_rec.grade_rule_id
1495 ,p_value => p_rec.value
1496 ,p_minimum => p_rec.minimum
1497 ,p_maximum => p_rec.maximum);
1498 --
1499 hr_utility.set_location(l_proc, 100);
1500 --
1501 -- Fix for bug 3049789 ends here.
1502 --
1503 pay_pgr_bus.chk_rate_id
1504 (p_rate_id => p_rec.rate_id
1505 ,p_rate_type => p_rec.rate_type
1506 ,p_business_group_id => p_rec.business_group_id
1507 ,p_grade_or_spinal_point_id => p_rec.grade_or_spinal_point_id
1508 ,p_effective_date => p_effective_date
1509 ,p_grade_rule_id => p_rec.grade_rule_id
1510 ,p_object_version_number => p_rec.object_version_number);
1511 --
1512 hr_utility.set_location(l_proc, 110);
1513 --
1514 pay_pgr_bus.chk_values_format
1515 (p_rate_id => p_rec.rate_id
1516 ,p_rate_type => p_rec.rate_type
1517 ,p_value => p_rec.value
1518 ,p_minimum => p_rec.minimum
1519 ,p_maximum => p_rec.maximum
1520 ,p_mid_value => p_rec.mid_value
1521 ,p_curcode => p_rec.currency_code);
1522 --
1523 hr_utility.set_location(' Leaving:'||l_proc, 999);
1524 --
1525 End update_validate;
1526 --
1527 -- ----------------------------------------------------------------------------
1528 -- |---------------------------< delete_validate >----------------------------|
1529 -- ----------------------------------------------------------------------------
1530 Procedure delete_validate
1531 (p_rec in pay_pgr_shd.g_rec_type
1532 ,p_effective_date in date
1533 ,p_datetrack_mode in varchar2
1534 ,p_validation_start_date in date
1535 ,p_validation_end_date in date
1536 ) is
1537 --
1538 l_proc varchar2(72) := g_package||'delete_validate';
1539 --
1540 Begin
1541 hr_utility.set_location('Entering:'||l_proc, 5);
1542 --
1543 -- Call all supporting business operations
1544 --
1545 dt_delete_validate
1546 (p_datetrack_mode => p_datetrack_mode
1547 ,p_validation_start_date => p_validation_start_date
1548 ,p_validation_end_date => p_validation_end_date
1549 ,p_grade_rule_id => p_rec.grade_rule_id
1550 );
1551 --
1552
1553 hr_utility.set_location(' Leaving:'||l_proc, 20);
1554
1555 End delete_validate;
1556 --
1557 end pay_pgr_bus;
[Home] [Help]