[Home] [Help]
PACKAGE BODY: APPS.PAY_PAP_BUS
Source
1 Package Body pay_pap_bus as
2 /* $Header: pypaprhi.pkb 120.0 2005/05/29 07:14:24 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package VARCHAR2(33) := 'pay_pap_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |---------------------------< return_ff_name >-----------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- NAME return_ff_name
15 --
16 -- DESCRIPTION Validates that the Fast Formula exists, is effective and
17 -- visible to the business group and legislation.
18 --
19 -- NOTES The name of the Fast Formula is returned.
20 --
21 FUNCTION return_ff_name
22 (p_effective_date IN DATE
23 ,p_business_group_id IN NUMBER
24 ,p_formula_id IN NUMBER
25 ,p_formula_type_name IN VARCHAR2) RETURN VARCHAR2
26 IS
27
28 l_proc VARCHAR2(72) := g_package||'return_ff_name';
29 l_legislation_code per_business_groups.legislation_code%TYPE;
30 l_formula_name ff_formulas_f.formula_name%TYPE;
31
32 --
33 -- Fetches the legislation code of the business group.
34 --
35 CURSOR csr_get_leg_code IS
36 SELECT pbg.legislation_code
37 FROM per_business_groups pbg
38 WHERE pbg.business_group_id = p_business_group_id;
39
40 --
41 -- Fetches the FF name whilst verifying that the formula type
42 -- matches, the formula is effective and the business group or
43 -- legislation code are visible.
44 --
45 CURSOR csr_chk_and_return_ff IS
46 SELECT ff.formula_name
47 FROM ff_formulas_f ff
48 ,ff_formula_types ft
49 WHERE ff.formula_id = p_formula_id
50 AND ff.formula_type_id = ft.formula_type_id
51 AND ft.formula_type_name = p_formula_type_name
52 AND p_effective_date BETWEEN
53 ff.effective_start_date and ff.effective_end_date
54 AND (ff.business_group_id IS NULL OR
55 (ff.business_group_id IS NOT NULL AND
56 ff.business_group_id = p_business_group_id))
57 AND (ff.legislation_code IS NULL OR
58 (ff.legislation_code IS NOT NULL AND
59 ff.legislation_code = l_legislation_code));
60
61 BEGIN
62
63 hr_utility.set_location('Entering: '||l_proc, 10);
64
65 OPEN csr_get_leg_code;
66 FETCH csr_get_leg_code INTO l_legislation_code;
67 CLOSE csr_get_leg_code;
68
69 OPEN csr_chk_and_return_ff;
70 FETCH csr_chk_and_return_ff INTO l_formula_name;
71 CLOSE csr_chk_and_return_ff;
72
73 hr_utility.set_location('Leaving: '||l_proc, 20);
74
75 --
76 -- If no formula is found, l_formula_name will return NULL. This function
77 -- returns null, instead of erroring, because the calling procedure may
78 -- raise specific (rather than general) error messages.
79 --
80 RETURN l_formula_name;
81
82 END return_ff_name;
83 --
84 -- ----------------------------------------------------------------------------
85 -- |---------------------------< chk_accrual_plan_name >----------------------|
86 -- ----------------------------------------------------------------------------
87 --
88 -- NAME chk_accrual_plan_name
89 --
90 -- DESCRIPTION Validates the accrual plan name. The name must not be
91 -- duplicated within the accrual plans table; the name must
92 -- not cause any clashes with the element type names that the
93 -- plan will create and the name is mandatory.
94 --
95 -- NOTES none
96 --
97 PROCEDURE chk_accrual_plan_name
98 (p_accrual_plan_id IN NUMBER
99 ,p_accrual_plan_name IN VARCHAR2
100 ,p_business_group_id IN NUMBER)
101 IS
102
103 l_proc VARCHAR2(72) := g_package||'chk_accrual_plan_name';
104 l_name_exists VARCHAR2(2) := 'N';
105 l_api_updating BOOLEAN;
106
107 CURSOR csr_check_name IS
108 SELECT 'Y'
109 FROM pay_accrual_plans pap
110 WHERE UPPER(pap.accrual_plan_name) = UPPER(p_accrual_plan_name)
111 AND pap.business_group_id = p_business_group_id
112 AND ((p_accrual_plan_id is null)
113 OR (p_accrual_plan_id is not null and
114 pap.accrual_plan_id <> p_accrual_plan_id));
115
116 BEGIN
117
118 hr_utility.set_location('Entering: '||l_proc, 10);
119
120 hr_api.mandatory_arg_error(p_api_name => l_proc,
121 p_argument => 'p_accrual_plan_name',
122 p_argument_value => p_accrual_plan_name);
123
124 --
125 -- Determine whether the plan name has been duplicated. If a record is found
126 -- then the local variable will be set to 'Y', otherwise it will remain 'N'
127 --
128 OPEN csr_check_name;
129 FETCH csr_check_name INTO l_name_exists;
130 CLOSE csr_check_name;
131
132 --
133 -- Check the value of the local variable.
134 --
135 IF (l_name_exists = 'Y') THEN
136
137 hr_utility.set_location(l_proc, 20);
138 fnd_message.set_name('PAY', 'HR_13163_PTO_DUP_PLAN_NAME');
139 fnd_message.raise_error;
140
141 END IF;
142
143 hr_utility.set_location('Leaving: '||l_proc, 30);
144
145 END chk_accrual_plan_name;
146 --
147 -- ----------------------------------------------------------------------------
148 -- |---------------------------< chk_accrual_category >-----------------------|
149 -- ----------------------------------------------------------------------------
150 --
151 -- NAME chk_accrual_category
152 --
153 -- DESCRIPTION Validates that the accrual category is valid and effective.
154 --
155 -- NOTES none
156 --
157 PROCEDURE chk_accrual_category
158 (p_effective_date IN DATE
159 ,p_accrual_plan_id IN NUMBER
160 ,p_object_version_number IN NUMBER
161 ,p_accrual_category IN VARCHAR2)
162 IS
163
164 l_proc VARCHAR2(72) := g_package||'chk_accrual_category';
165 l_api_updating BOOLEAN;
166
167 BEGIN
168
169 hr_utility.set_location('Entering: '||l_proc, 10);
170
171 --
172 -- Only proceed with validation if:
173 -- a) inserting or
174 -- b) updating and the parameters used within this chk procedure
175 -- have not changed.
176 --
177 l_api_updating := pay_pap_shd.api_updating
178 (p_accrual_plan_id => p_accrual_plan_id
179 ,p_object_version_number => p_object_version_number);
180
181 IF (l_api_updating
182 AND NVL(pay_pap_shd.g_old_rec.accrual_category, hr_api.g_varchar2)
183 = NVL(p_accrual_category, hr_api.g_varchar2)) THEN
184 RETURN;
185 END IF;
186
187 hr_api.mandatory_arg_error(p_api_name => l_proc,
188 p_argument => 'p_accrual_category',
189 p_argument_value => p_accrual_category);
190
191 --
192 -- Bug 1744331: use leg_lookups.
193 --
194 IF hr_api.not_exists_in_leg_lookups
195 (p_effective_date => p_effective_date
196 ,p_lookup_type => 'US_PTO_ACCRUAL'
197 ,p_lookup_code => p_accrual_category) THEN
198
199 --
200 -- The accrual category does not exist in the lookup.
201 --
202 hr_utility.set_location(l_proc, 20);
203 fnd_message.set_name('PER', 'HR_289325_LEG_ACC_NOT_EXISTS');
204 fnd_message.raise_error;
205
206 END IF;
207
208 hr_utility.set_location('Leaving: '||l_proc, 90);
209
210 END chk_accrual_category;
211 --
212 -- ----------------------------------------------------------------------------
213 -- |---------------------------< chk_accrual_start >--------------------------|
214 -- ----------------------------------------------------------------------------
215 --
216 -- NAME chk_accrual_start
217 --
218 -- DESCRIPTION Validates that the accrual start is valid and effective.
219 --
220 -- NOTES none
221 --
222 PROCEDURE chk_accrual_start
223 (p_effective_date IN DATE
224 ,p_accrual_plan_id IN NUMBER
225 ,p_object_version_number IN NUMBER
226 ,p_accrual_start IN VARCHAR2)
227 IS
228
229 l_proc VARCHAR2(72) := g_package||'chk_accrual_start';
230 l_api_updating BOOLEAN;
231
232 BEGIN
233
234 hr_utility.set_location('Entering: '||l_proc, 10);
235
236 --
237 -- Only proceed with validation if:
238 -- a) inserting or
239 -- b) updating and the parameters used within this chk procedure
240 -- have not changed.
241 --
242 l_api_updating := pay_pap_shd.api_updating
243 (p_accrual_plan_id => p_accrual_plan_id
244 ,p_object_version_number => p_object_version_number);
245
246 IF (l_api_updating
247 AND NVL(pay_pap_shd.g_old_rec.accrual_start, hr_api.g_varchar2)
248 = NVL(p_accrual_start, hr_api.g_varchar2)) THEN
249 RETURN;
250 END IF;
251
252 IF p_accrual_start IS NOT NULL THEN
253
254 hr_utility.set_location(l_proc, 20);
255
256 IF hr_api.not_exists_in_hr_lookups
257 (p_effective_date => p_effective_date
258 ,p_lookup_type => 'US_ACCRUAL_START_TYPE'
259 ,p_lookup_code => p_accrual_start) THEN
260
261 --
262 -- The accrual start does not exist in the lookup.
263 --
264 hr_utility.set_location(l_proc, 30);
265 fnd_message.set_name('PER', 'HR_289814_PAP_START_INVALID');
266 fnd_message.raise_error;
267
268 END IF;
269
270 END IF;
271
272 hr_utility.set_location('Leaving: '||l_proc, 90);
273
274 END chk_accrual_start;
275 --
276 -- ----------------------------------------------------------------------------
277 -- |---------------------------< chk_accrual_units_of_measure >---------------|
278 -- ----------------------------------------------------------------------------
279 --
280 -- NAME chk_accrual_units_of_measure
281 --
282 -- DESCRIPTION Validates that the accrual UOM is valid and effective.
283 --
284 -- NOTES none
285 --
286 PROCEDURE chk_accrual_units_of_measure
287 (p_effective_date IN DATE
288 ,p_accrual_plan_id IN NUMBER
289 ,p_object_version_number IN NUMBER
290 ,p_accrual_units_of_measure IN VARCHAR2)
291 IS
292
293 l_proc VARCHAR2(72) := g_package||'chk_accrual_units_of_measure';
294 l_api_updating BOOLEAN;
295
296 BEGIN
297
298 hr_utility.set_location('Entering: '||l_proc, 10);
299
300 hr_api.mandatory_arg_error(p_api_name => l_proc,
301 p_argument => 'p_accrual_units_of_measure',
302 p_argument_value => p_accrual_units_of_measure);
303
304 IF hr_api.not_exists_in_hr_lookups
305 (p_effective_date => p_effective_date
306 ,p_lookup_type => 'HOURS_OR_DAYS'
307 ,p_lookup_code => p_accrual_units_of_measure) THEN
308
309 --
310 -- The accrual UOM does not exist in the lookup.
311 --
312 hr_utility.set_location(l_proc, 20);
313 fnd_message.set_name('PER', 'HR_289815_PAP_UOM_INVALID');
314 fnd_message.raise_error;
315
316 END IF;
317
318 hr_utility.set_location('Leaving: '||l_proc, 90);
319
320 END chk_accrual_units_of_measure;
321 --
322 -- ----------------------------------------------------------------------------
323 -- |---------------------------< chk_accrual_formula_id >---------------------|
324 -- ----------------------------------------------------------------------------
325 --
326 -- NAME chk_accrual_formula_id
327 --
328 -- DESCRIPTION Validates that the accrual formula exists in ff_formulas_f
329 -- globally, for the business group or for the legislation.
330 --
331 -- NOTES none
332 --
333 PROCEDURE chk_accrual_formula_id
334 (p_effective_date IN DATE
335 ,p_accrual_plan_id IN NUMBER
336 ,p_object_version_number IN NUMBER
337 ,p_business_group_id IN NUMBER
338 ,p_accrual_formula_id IN NUMBER)
339 IS
340
341 l_proc VARCHAR2(72) := g_package||'chk_accrual_formula_id';
342 l_accrual_formula ff_formulas_f.formula_name%TYPE;
343 l_api_updating BOOLEAN;
344
345 BEGIN
346
347 hr_utility.set_location('Entering: '||l_proc, 10);
348
349 --
350 -- Only proceed with validation if:
351 -- a) inserting or
352 -- b) updating and the parameters used within this chk procedure
353 -- have not changed.
354 --
355 l_api_updating := pay_pap_shd.api_updating
356 (p_accrual_plan_id => p_accrual_plan_id
357 ,p_object_version_number => p_object_version_number);
358
359 IF (l_api_updating
360 AND NVL(pay_pap_shd.g_old_rec.accrual_formula_id, hr_api.g_number)
361 = NVL(p_accrual_formula_id, hr_api.g_number)) THEN
362 RETURN;
363 END IF;
364
365 hr_api.mandatory_arg_error(p_api_name => l_proc,
366 p_argument => 'p_accrual_formula_id',
367 p_argument_value => p_accrual_formula_id);
368
369 hr_utility.set_location(l_proc, 20);
370
371 l_accrual_formula := return_ff_name
372 (p_effective_date => p_effective_date
373 ,p_business_group_id => p_business_group_id
377 hr_utility.set_location(l_proc, 30);
374 ,p_formula_id => p_accrual_formula_id
375 ,p_formula_type_name => 'Accrual');
376
378
379 IF l_accrual_formula IS NULL THEN
380
381 --
382 -- The formula is not valid so error.
383 --
384 hr_utility.set_location(l_proc, 40);
385 fnd_message.set_name('PER', 'HR_289817_PAP_ACCRUAL_FF');
386 fnd_message.raise_error;
387
388 END IF;
389
390 hr_utility.set_location('Leaving: '||l_proc, 90);
391
392 END chk_accrual_formula_id;
393 --
394 -- ----------------------------------------------------------------------------
395 -- |---------------------------< chk_co_formula_id >--------------------------|
396 -- ----------------------------------------------------------------------------
397 --
398 -- NAME chk_co_formula_id
399 --
400 -- DESCRIPTION Validates that the carry over formula exists in ff_formulas_f
401 -- globally, for the business group or for the legislation.
402 --
403 -- NOTES none
404 --
405 PROCEDURE chk_co_formula_id
406 (p_effective_date IN DATE
407 ,p_accrual_plan_id IN NUMBER
408 ,p_object_version_number IN NUMBER
409 ,p_business_group_id IN NUMBER
410 ,p_accrual_formula_id IN NUMBER
411 ,p_co_formula_id IN NUMBER)
412 IS
413
414 l_proc VARCHAR2(72) := g_package||'chk_co_formula_id';
415 l_accrual_formula ff_formulas_f.formula_name%TYPE;
416 l_co_formula ff_formulas_f.formula_name%TYPE;
417 l_api_updating BOOLEAN;
418
419 BEGIN
420
421 hr_utility.set_location('Entering: '||l_proc, 10);
422
423 --
424 -- Only proceed with validation if:
425 -- a) inserting or
426 -- b) updating and the parameters used within this chk procedure
427 -- have not changed.
428 --
429 l_api_updating := pay_pap_shd.api_updating
430 (p_accrual_plan_id => p_accrual_plan_id
431 ,p_object_version_number => p_object_version_number);
432
433 IF (l_api_updating
434 AND NVL(pay_pap_shd.g_old_rec.accrual_formula_id, hr_api.g_number)
435 = NVL(p_accrual_formula_id, hr_api.g_number)
436 AND NVL(pay_pap_shd.g_old_rec.co_formula_id, hr_api.g_number)
437 = NVL(p_co_formula_id, hr_api.g_number)) THEN
438 RETURN;
439 END IF;
440
441 hr_api.mandatory_arg_error(p_api_name => l_proc,
442 p_argument => 'p_co_formula_id',
443 p_argument_value => p_co_formula_id);
444
445 hr_utility.set_location(l_proc, 20);
446
447 l_co_formula := return_ff_name
448 (p_effective_date => p_effective_date
449 ,p_business_group_id => p_business_group_id
450 ,p_formula_id => p_co_formula_id
451 ,p_formula_type_name => 'Accrual Carryover');
452
453 IF l_co_formula IS NULL THEN
454
455 --
456 -- The formula is not valid so error.
457 --
458 hr_utility.set_location(l_proc, 30);
459 fnd_message.set_name('PER', 'HR_289817_PAP_ACCRUAL_FF');
460 fnd_message.raise_error;
461
462 ELSIF l_co_formula IN ('PTO_PAYROLL_CARRYOVER'
463 ,'PTO_SIMPLE_CARRYOVER'
464 ,'PTO_ROLLING_CARRYOVER'
465 ,'PTO_HD_ANNIVERSARY_CARRYOVER') THEN
466
467 --
468 -- This plan is using a seeded carry over formula,
469 -- get the accrual formula to check compatibility
470 -- (where possible).
471 --
472 hr_utility.set_location(l_proc, 40);
473
474 --
475 -- First get the accrual formula.
476 -- This function call should always return a value because
477 -- the accrual formula has been validated in a previous
478 -- chk procedure.
479 --
480 l_accrual_formula := return_ff_name
481 (p_effective_date => p_effective_date
482 ,p_business_group_id => p_business_group_id
483 ,p_formula_id => p_accrual_formula_id
484 ,p_formula_type_name => 'Accrual');
485
486 hr_utility.set_location(l_proc, 50);
487
488 IF l_accrual_formula IN ('PTO_PAYROLL_CALCULATION'
489 ,'PTO_PAYROLL_BALANCE_CALCULATION'
490 ,'PTO_SIMPLE_MULTIPLIER'
491 ,'PTO_SIMPLE_BALANCE_MULTIPLIER'
492 ,'PTO_HD_ANNIVERSARY_BALANCE'
493 ,'PTO_ROLLING_ACCRUAL') THEN
494
495 hr_utility.set_location(l_proc, 60);
496 --
497 -- Seeded formula are being used for both the accrual and
498 -- carry over formulae. Check that the two are compatible.
499 --
500 IF (l_co_formula = 'PTO_PAYROLL_CARRYOVER' AND
501 l_accrual_formula NOT IN ('PTO_PAYROLL_CALCULATION'
502 ,'PTO_PAYROLL_BALANCE_CALCULATION'))
503 OR (l_co_formula = 'PTO_SIMPLE_CARRYOVER' AND
504 l_accrual_formula NOT IN ('PTO_SIMPLE_MULTIPLIER'
505 ,'PTO_SIMPLE_BALANCE_MULTIPLIER'))
506 OR (l_co_formula = 'PTO_HD_ANNIVERSARY_CARRYOVER' AND
507 l_accrual_formula <> 'PTO_HD_ANNIVERSARY_BALANCE')
511 hr_utility.set_location(l_proc, 70);
508 OR (l_co_formula = 'PTO_ROLLING_CARRYOVER' AND
509 l_accrual_formula <> 'PTO_ROLLING_ACCRUAL') THEN
510
512 --
513 -- The carryover and accrual formulae are incompatible.
514 --
515 fnd_message.set_name('PER','HR_289819_PAP_FF_INCOMPATIBLE');
516 fnd_message.set_token('CO_FF', l_co_formula);
517 fnd_message.set_token('ACCRUAL_FF', l_accrual_formula);
518 fnd_message.raise_error;
519
520 END IF;
521
522 END IF;
523
524 END IF;
525
526 hr_utility.set_location('Leaving: '||l_proc, 90);
527
528 END chk_co_formula_id;
529 --
530 -- ----------------------------------------------------------------------------
531 -- |---------------------------< chk_pto_input_value_id >---------------------|
532 -- ----------------------------------------------------------------------------
533 --
534 -- NAME chk_pto_input_value_id
535 --
536 -- DESCRIPTION Validates that the nominated absence element is a valid
537 -- input value and has a corresponding absence type.
538 --
539 -- NOTES none
540 --
541 PROCEDURE chk_pto_input_value_id
542 (p_accrual_plan_id IN NUMBER
543 ,p_object_version_number IN NUMBER
544 ,p_pto_input_value_id IN NUMBER
545 ,p_business_group_id IN NUMBER
546 ,p_accrual_units_of_measure IN VARCHAR2)
547 IS
548
549 l_proc VARCHAR2(72) := g_package||'chk_pto_input_value_id';
550 l_valid BOOLEAN := TRUE;
551 l_api_updating BOOLEAN;
552 l_dummy NUMBER;
553 l_element_type_id NUMBER;
554 l_legislation_code VARCHAR2(30);
555
556 --
557 -- Checks the format of the input value and fetches the element type.
558 --
559 CURSOR csr_chk_input_value IS
560 SELECT pet.element_type_id
561 FROM pay_element_types_f pet
562 ,pay_input_values_f piv
563 WHERE piv.input_value_id = p_pto_input_value_id
564 AND (piv.business_group_id = p_business_group_id
565 OR (piv.business_group_id is null
566 AND piv.legislation_code = l_legislation_code))
567 AND piv.element_type_id = pet.element_type_id
568 AND ((piv.uom = 'ND' AND
569 p_accrual_units_of_measure = 'D')
570 OR ((piv.uom like 'H_DECIMAL_%' OR
571 piv.uom like 'H_HH%') AND
572 p_accrual_units_of_measure = 'H'));
573
574 --
575 -- Checks that a valid absence type exists or that the DDF is set to Absences.
576 -- The UOM is again validated, but this time against the absence type's UOM.
577 --
578 CURSOR csr_chk_pet_and_paat IS
579 SELECT null
580 FROM pay_element_types_f pet
581 ,pay_element_classifications pec
582 WHERE pet.element_type_id = l_element_type_id
583 AND pet.classification_id = pec.classification_id
584 AND (pet.processing_type = 'N'
585 OR (pet.processing_type = 'R' AND pet.proration_group_id IS NOT NULL))
586 AND (pet.business_group_id = p_business_group_id
587 OR (pet.business_group_id is null
588 AND pet.legislation_code = l_legislation_code))
589 AND ((upper(pec.classification_name) = 'INFORMATION' AND
590 pet.element_information1 = 'ABS' AND
591 p_accrual_units_of_measure = 'H')
592 OR (EXISTS (SELECT null
593 FROM per_absence_attendance_types paat
594 WHERE paat.input_value_id IS NOT NULL
595 AND paat.input_value_id = p_pto_input_value_id
596 AND paat.business_group_id = p_business_group_id
597 AND ((paat.hours_or_days = 'D' AND
598 p_accrual_units_of_measure = 'D')
599 OR (paat.hours_or_days = 'H' AND
600 p_accrual_units_of_measure = 'H')))));
601 -- Defining a cursor to get the legislation_code of the business group
602 --
603 CURSOR csr_get_leg_code IS
604 SELECT pbg.legislation_code
605 FROM per_business_groups pbg
606 WHERE pbg.business_group_id = p_business_group_id;
607 --
608 BEGIN
609
610 hr_utility.set_location('Entering: '||l_proc, 10);
611
612 --
613 -- Only proceed with validation if:
614 -- a) inserting or
615 -- b) updating and the parameters used within this chk procedure
616 -- have not changed.
617 --
618 l_api_updating := pay_pap_shd.api_updating
619 (p_accrual_plan_id => p_accrual_plan_id
620 ,p_object_version_number => p_object_version_number);
621
622 IF (l_api_updating
623 AND NVL(pay_pap_shd.g_old_rec.pto_input_value_id, hr_api.g_number)
624 = NVL(p_pto_input_value_id, hr_api.g_number)
625 AND NVL(pay_pap_shd.g_old_rec.accrual_units_of_measure, hr_api.g_varchar2)
626 = NVL(p_accrual_units_of_measure, hr_api.g_varchar2)) THEN
627 RETURN;
628 END IF;
629
630 hr_api.mandatory_arg_error(p_api_name => l_proc,
631 p_argument => 'p_pto_input_value_id',
632 p_argument_value => p_pto_input_value_id);
633
634 hr_utility.set_location(l_proc, 20);
635
636 --
637 -- Fetch the legislation code
638 OPEN csr_get_leg_code;
639 FETCH csr_get_leg_code INTO l_legislation_code;
640 CLOSE csr_get_leg_code;
641 --
642 -- Check the input value and fetch the element type.
643 --
644 OPEN csr_chk_input_value;
645 FETCH csr_chk_input_value INTO l_element_type_id;
646 CLOSE csr_chk_input_value;
647
648 IF l_element_type_id IS NULL THEN
649
650 hr_utility.set_location(l_proc, 20);
651 l_valid := FALSE;
652
653 ELSE
654
655 hr_utility.set_location(l_proc, 30);
656
657 --
658 -- Check the element type and corresponding absence element.
659 --
660 OPEN csr_chk_pet_and_paat;
661 FETCH csr_chk_pet_and_paat INTO l_dummy;
662
663 IF csr_chk_pet_and_paat%NOTFOUND THEN
664
665 hr_utility.set_location(l_proc, 40);
666 l_valid := FALSE;
667
668 END IF;
669
670 END IF;
671
672 CLOSE csr_chk_pet_and_paat;
673
674 IF NOT l_valid THEN
675
676 fnd_message.set_name('PER','HR_289813_PAP_INVALID_INPT_VAL');
677 fnd_message.raise_error;
678
679 END IF;
680
681 hr_utility.set_location('Leaving: '||l_proc, 90);
682
683 END chk_pto_input_value_id;
684 --
685 -- ----------------------------------------------------------------------------
686 -- |---------------------------< chk_defined_balance_id >---------------------|
687 -- ----------------------------------------------------------------------------
688 --
689 -- NAME chk_defined_balance_id
690 --
691 -- DESCRIPTION Validates that the defined balance is valid and that it is
692 -- compatible with the Accrual formula.
693 --
694 -- NOTES none
695 --
696 PROCEDURE chk_defined_balance_id
697 (p_effective_date IN DATE
698 ,p_accrual_plan_id IN NUMBER
699 ,p_object_version_number IN NUMBER
700 ,p_business_group_id IN NUMBER
701 ,p_accrual_formula_id IN NUMBER
702 ,p_defined_balance_id IN NUMBER
703 ,p_check_accrual_ff OUT NOCOPY BOOLEAN)
704 IS
705
706 l_proc VARCHAR2(72) := g_package||'chk_defined_balance_id';
707 l_accrual_formula ff_formulas_f.formula_name%TYPE;
708 l_api_updating BOOLEAN;
709 l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
710 l_balance_type hr_organization_information.org_information1%TYPE;
711
712 --
713 -- Get the balance dimension given the defined balance.
714 --
715 CURSOR csr_get_dim_name IS
716 SELECT pbd.dimension_name
717 FROM pay_balance_dimensions pbd
718 ,pay_defined_balances pdb
719 WHERE pdb.defined_balance_id = p_defined_balance_id
720 AND pdb.balance_dimension_id = pbd.balance_dimension_id;
721
722 --
723 -- Get the balance type against the BG.
724 --
725 CURSOR csr_get_bg_balance_type IS
726 SELECT hoi.org_information1
727 FROM hr_organization_information hoi
728 WHERE hoi.organization_id = p_business_group_id
729 AND hoi.org_information_context = 'PTO Balance Type';
730
731 --
732 -- Get the balance type against the legislation.
733 --
734 CURSOR csr_get_leg_balance_type IS
735 SELECT plr.rule_mode
736 FROM pay_legislation_rules plr
737 WHERE plr.rule_type = 'PTO_BALANCE_TYPE'
738 AND plr.legislation_code =
739 (SELECT pbg.legislation_code
740 FROM per_business_groups pbg
741 WHERE pbg.business_group_id = p_business_group_id);
742
743 BEGIN
744
745 hr_utility.set_location('Entering: '||l_proc, 10);
746
747 p_check_accrual_ff := FALSE;
748 --
749 -- Only proceed with validation if:
750 -- a) inserting or
751 -- b) updating and the parameters used within this chk procedure
752 -- have not changed.
753 --
754 l_api_updating := pay_pap_shd.api_updating
755 (p_accrual_plan_id => p_accrual_plan_id
756 ,p_object_version_number => p_object_version_number);
757
758 IF (l_api_updating
759 AND NVL(pay_pap_shd.g_old_rec.accrual_formula_id, hr_api.g_number)
760 = NVL(p_accrual_formula_id, hr_api.g_number)
761 AND NVL(pay_pap_shd.g_old_rec.defined_balance_id, hr_api.g_number)
762 = NVL(p_defined_balance_id, hr_api.g_number)) THEN
763 RETURN;
764 END IF;
765
766 hr_utility.set_location(l_proc, 20);
767
768 IF p_defined_balance_id IS NOT NULL THEN
769
770 hr_utility.set_location(l_proc, 30);
771
772 IF l_api_updating
773 AND NVL(pay_pap_shd.g_old_rec.defined_balance_id, hr_api.g_number)
774 <> hr_api.g_number THEN
775
776 --
777 -- The balance dimension has previously been set and is trying
778 -- to be updated. This is not allowed so error.
779 --
780 hr_utility.set_location(l_proc, 40);
781 fnd_message.set_name('PER','HR_289823_PAP_BAL_DIM_UPDATE');
782 fnd_message.raise_error;
783
784
785 END IF;
786
787 hr_utility.set_location(l_proc, 50);
788
789 --
790 -- Fetch the name of the accrual formula for later use. This
791 -- should never error or return null because the accrual formula
792 -- has already been validated prior to this chk procedure.
793 --
794 l_accrual_formula := return_ff_name
795 (p_effective_date => p_effective_date
796 ,p_business_group_id => p_business_group_id
797 ,p_formula_id => p_accrual_formula_id
798 ,p_formula_type_name => 'Accrual');
799
800 hr_utility.set_location(l_proc, 60);
801
802 --
803 -- Fetch the balance dimension name.
804 --
805 OPEN csr_get_dim_name;
806 FETCH csr_get_dim_name INTO l_dimension_name;
807 CLOSE csr_get_dim_name;
808
809 hr_utility.set_location(l_proc, 70);
810
811 IF l_dimension_name IS NULL THEN
812
813 --
814 -- The defined balance does not exist (the defined
815 -- balance has already been validated at this stage
816 -- so this would only occur when there is an internal
817 -- error with creating the defined balance).
818 --
819 hr_utility.set_location(l_proc, 80);
820 fnd_message.set_name('PER','HR_289820_PAP_DEF_BAL_INVALID');
821 fnd_message.raise_error;
822
823 ELSIF l_dimension_name IN ('_ASG_PTO_SM_YTD'
824 ,'_ASG_PTO_DE_SM_YTD'
825 ,'_ASG_PTO_YTD'
826 ,'_ASG_PTO_DE_YTD'
827 ,'_ASG_PTO_HD_YTD'
828 ,'_ASG_PTO_DE_HD_YTD') THEN
829
830 --
831 -- This is a seed dimension name, check it is compatible
832 -- with the accrual formula.
833 --
834 hr_utility.set_location(l_proc, 90);
835
836 IF (l_dimension_name IN ('_ASG_PTO_SM_YTD'
837 ,'_ASG_PTO_DE_SM_YTD') AND
838 l_accrual_formula IN ('PTO_PAYROLL_CALCULATION'
839 ,'PTO_PAYROLL_BALANCE_CALCULATION'
840 ,'PTO_SIMPLE_MULTIPLIER'
841 ,'PTO_HD_ANNIVERSARY_BALANCE'
842 ,'PTO_ROLLING_ACCRUAL'))
843 OR (l_dimension_name IN ('_ASG_PTO_YTD'
844 ,'_ASG_PTO_DE_YTD') AND
845 l_accrual_formula IN ('PTO_PAYROLL_CALCULATION'
846 ,'PTO_SIMPLE_MULTIPLIER'
847 ,'PTO_SIMPLE_BALANCE_MULTIPLIER'
848 ,'PTO_HD_ANNIVERSARY_BALANCE'
849 ,'PTO_ROLLING_ACCRUAL'))
850 OR (l_dimension_name IN ('_ASG_PTO_HD_YTD'
851 ,'_ASG_PTO_DE_HD_YTD') AND
852 l_accrual_formula IN ('PTO_PAYROLL_CALCULATION'
853 ,'PTO_PAYROLL_BALANCE_CALCULATION'
854 ,'PTO_SIMPLE_MULTIPLIER'
855 ,'PTO_SIMPLE_BALANCE_MULTIPLIER'
856 ,'PTO_ROLLING_ACCRUAL')) THEN
857
858 --
859 -- The balance dimension conflicts with the accrual
860 -- formula.
861 --
862 hr_utility.set_location(l_proc, 100);
863 fnd_message.set_name('PER', 'HR_289821_PAP_BAL_DIM_CONFLICT');
864 fnd_message.raise_error;
865
866 END IF;
867
868 --
869 -- Check that the balance dimension matches the balance type.
870 -- First get the balance type.
871 --
872 OPEN csr_get_bg_balance_type;
873 FETCH csr_get_bg_balance_type INTO l_balance_type;
874 CLOSE csr_get_bg_balance_type;
875
876 hr_utility.set_location(l_proc, 110);
877
878 IF l_balance_type IS NULL THEN
879 --
880 -- Check for a balance type at legislative level.
881 --
882 OPEN csr_get_leg_balance_type;
883 FETCH csr_get_leg_balance_type INTO l_balance_type;
884 CLOSE csr_get_leg_balance_type;
885
886 hr_utility.set_location(l_proc, 120);
887
888 END IF;
889
890 IF (l_balance_type = 'DE' AND
891 l_dimension_name NOT IN ('_ASG_PTO_DE_YTD'
892 ,'_ASG_PTO_DE_SM_YTD'
893 ,'_ASG_PTO_DE_HD_YTD'))
894 OR (NVL(l_balance_type, hr_api.g_varchar2) <> 'DE' AND
895 l_dimension_name IN ('_ASG_PTO_DE_YTD'
896 ,'_ASG_PTO_DE_SM_YTD'
897 ,'_ASG_PTO_DE_HD_YTD')) THEN
898
899 hr_utility.set_location(l_proc, 130);
900 fnd_message.set_name('PER', 'HR_289822_PAP_BAL_TYPE_DIM');
901 fnd_message.raise_error;
902
903 END IF;
904
905 END IF;
906
907 --
908 -- If this is not a seeded core Accrual formula then warn.
909 -- A balance dimension is being set for the first time so
910 -- the Accrual formula must support payroll balances.
911 --
912 IF l_accrual_formula NOT IN ('PTO_PAYROLL_CALCULATION'
913 ,'PTO_PAYROLL_BALANCE_CALCULATION'
914 ,'PTO_SIMPLE_MULTIPLIER'
915 ,'PTO_SIMPLE_BALANCE_MULTIPLIER'
916 ,'PTO_HD_ANNIVERSARY_BALANCE'
917 ,'PTO_ROLLING_ACCRUAL') THEN
918
919 p_check_accrual_ff := TRUE;
920
921 END IF;
922
923 END IF;
924
925 hr_utility.set_location('Leaving: '||l_proc, 140);
926
927 END chk_defined_balance_id;
928 --
929 -- ----------------------------------------------------------------------------
930 -- |---------------------------< chk_ineligible_period_type >-----------------|
931 -- ----------------------------------------------------------------------------
932 --
933 -- NAME chk_ineligible_period_type
934 --
935 -- DESCRIPTION Validates that the ineligible period type is valid
936 -- and effective.
937 --
938 -- NOTES none
939 --
940 PROCEDURE chk_ineligible_period_type
941 (p_effective_date IN DATE
942 ,p_accrual_plan_id IN NUMBER
943 ,p_object_version_number IN NUMBER
944 ,p_ineligible_period_type IN VARCHAR2)
945 IS
946
947 l_proc VARCHAR2(72) := g_package||'chk_ineligibile_period_type';
948 l_api_updating BOOLEAN;
949
950 BEGIN
951
955 -- Only proceed with validation if:
952 hr_utility.set_location('Entering: '||l_proc, 10);
953
954 --
956 -- a) inserting or
957 -- b) updating and the parameters used within this chk procedure
958 -- have not changed.
959 --
960 l_api_updating := pay_pap_shd.api_updating
961 (p_accrual_plan_id => p_accrual_plan_id
962 ,p_object_version_number => p_object_version_number);
963
964 IF (l_api_updating
965 AND NVL(pay_pap_shd.g_old_rec.ineligible_period_type, hr_api.g_varchar2)
966 = NVL(p_ineligible_period_type, hr_api.g_varchar2)) THEN
967 RETURN;
968 END IF;
969
970 IF p_ineligible_period_type IS NOT NULL THEN
971
972 hr_utility.set_location(l_proc, 20);
973
974 IF hr_api.not_exists_in_hr_lookups
975 (p_effective_date => p_effective_date
976 ,p_lookup_type => 'PROC_PERIOD_TYPE'
977 ,p_lookup_code => p_ineligible_period_type) THEN
978
979 --
980 -- The ineligible period type does not exist in the lookup.
981 --
982 hr_utility.set_location(l_proc, 30);
983 fnd_message.set_name('PER', 'HR_289816_PAP_INELIG_INVALID');
984 fnd_message.raise_error;
985
986 END IF;
987
988 END IF;
989
990 hr_utility.set_location('Leaving: '||l_proc, 90);
991
992 END chk_ineligible_period_type;
993 --
994 -- ----------------------------------------------------------------------------
995 -- |---------------------------< chk_ineligibility_formula_id >---------------|
996 -- ----------------------------------------------------------------------------
997 --
998 -- NAME chk_ineligibility_formula_id
999 --
1000 -- DESCRIPTION Validates that the ineligibility formula exists in
1001 -- ff_formulas_f globally, for the business group or for the
1002 -- legislation.
1003 --
1004 -- NOTES none
1005 --
1006 PROCEDURE chk_ineligibility_formula_id
1007 (p_effective_date IN DATE
1008 ,p_accrual_plan_id IN NUMBER
1009 ,p_object_version_number IN NUMBER
1010 ,p_business_group_id IN NUMBER
1011 ,p_ineligibility_formula_id IN NUMBER)
1012 IS
1013
1014 l_proc VARCHAR2(72) := g_package||'chk_ineligibility_formula_id';
1015 l_ineligible_formula ff_formulas_f.formula_name%TYPE;
1016 l_api_updating BOOLEAN;
1017
1018 BEGIN
1019
1020 hr_utility.set_location('Entering: '||l_proc, 10);
1021
1022 --
1023 -- Only proceed with validation if:
1024 -- a) inserting or
1025 -- b) updating and the parameters used within this chk procedure
1026 -- have not changed.
1027 --
1028 l_api_updating := pay_pap_shd.api_updating
1029 (p_accrual_plan_id => p_accrual_plan_id
1030 ,p_object_version_number => p_object_version_number);
1031
1032 IF (l_api_updating
1033 AND NVL(pay_pap_shd.g_old_rec.ineligibility_formula_id, hr_api.g_number)
1034 = NVL(p_ineligibility_formula_id, hr_api.g_number)) THEN
1035 RETURN;
1036 END IF;
1037
1038 hr_utility.set_location(l_proc, 20);
1039
1040 IF p_ineligibility_formula_id IS NOT NULL THEN
1041
1042 hr_utility.set_location(l_proc, 30);
1043 l_ineligible_formula := return_ff_name
1044 (p_effective_date => p_effective_date
1045 ,p_business_group_id => p_business_group_id
1046 ,p_formula_id => p_ineligibility_formula_id
1047 ,p_formula_type_name => 'Accrual Ineligibility');
1048
1049 hr_utility.set_location(l_proc, 40);
1050
1051 IF l_ineligible_formula IS NULL THEN
1052
1053 --
1054 -- The formula is not valid so error.
1055 --
1056 hr_utility.set_location(l_proc, 50);
1057 fnd_message.set_name('PER', 'HR_289818_PAP_INELIG_FF');
1058 fnd_message.raise_error;
1059
1060 END IF;
1061
1062 END IF;
1063
1064 hr_utility.set_location('Leaving: '||l_proc, 90);
1065
1066 END chk_ineligibility_formula_id;
1067 --
1068 -- ----------------------------------------------------------------------------
1069 -- |-----------------------------< chk_ddf >----------------------------------|
1070 -- ----------------------------------------------------------------------------
1071 --
1072 -- Description:
1073 -- Validates all the Developer Descriptive Flexfield values.
1074 --
1075 -- Prerequisites:
1076 -- All other columns have been validated. Must be called as the
1077 -- last step from insert_validate and update_validate.
1078 --
1079 -- In Arguments:
1080 -- p_rec
1081 --
1082 -- Post Success:
1083 -- If the Developer Descriptive Flexfield structure column and data values
1084 -- are all valid this procedure will end normally and processing will
1085 -- continue.
1086 --
1087 -- Post Failure:
1088 -- If the Developer Descriptive Flexfield structure column value or any of
1089 -- the data values are invalid then an application error is raised as
1090 -- a PL/SQL exception.
1091 --
1092 -- Access Status:
1093 -- Internal Row Handler Use Only.
1094 --
1095 -- ----------------------------------------------------------------------------
1096 procedure chk_ddf
1100 l_proc varchar2(72) := g_package || 'chk_ddf';
1097 (p_rec in pay_pap_shd.g_rec_type
1098 ) is
1099 --
1101 --
1102 begin
1103 hr_utility.set_location('Entering:'||l_proc,10);
1104 --
1105 if ((p_rec.accrual_plan_id is not null) and (
1106 nvl(pay_pap_shd.g_old_rec.information_category, hr_api.g_varchar2) <>
1107 nvl(p_rec.information_category, hr_api.g_varchar2) or
1108 nvl(pay_pap_shd.g_old_rec.information1, hr_api.g_varchar2) <>
1109 nvl(p_rec.information1, hr_api.g_varchar2) or
1110 nvl(pay_pap_shd.g_old_rec.information2, hr_api.g_varchar2) <>
1111 nvl(p_rec.information2, hr_api.g_varchar2) or
1112 nvl(pay_pap_shd.g_old_rec.information3, hr_api.g_varchar2) <>
1113 nvl(p_rec.information3, hr_api.g_varchar2) or
1114 nvl(pay_pap_shd.g_old_rec.information4, hr_api.g_varchar2) <>
1115 nvl(p_rec.information4, hr_api.g_varchar2) or
1116 nvl(pay_pap_shd.g_old_rec.information5, hr_api.g_varchar2) <>
1117 nvl(p_rec.information5, hr_api.g_varchar2) or
1118 nvl(pay_pap_shd.g_old_rec.information6, hr_api.g_varchar2) <>
1119 nvl(p_rec.information6, hr_api.g_varchar2) or
1120 nvl(pay_pap_shd.g_old_rec.information7, hr_api.g_varchar2) <>
1121 nvl(p_rec.information7, hr_api.g_varchar2) or
1122 nvl(pay_pap_shd.g_old_rec.information8, hr_api.g_varchar2) <>
1123 nvl(p_rec.information8, hr_api.g_varchar2) or
1124 nvl(pay_pap_shd.g_old_rec.information9, hr_api.g_varchar2) <>
1125 nvl(p_rec.information9, hr_api.g_varchar2) or
1126 nvl(pay_pap_shd.g_old_rec.information10, hr_api.g_varchar2) <>
1127 nvl(p_rec.information10, hr_api.g_varchar2) or
1128 nvl(pay_pap_shd.g_old_rec.information11, hr_api.g_varchar2) <>
1129 nvl(p_rec.information11, hr_api.g_varchar2) or
1130 nvl(pay_pap_shd.g_old_rec.information12, hr_api.g_varchar2) <>
1131 nvl(p_rec.information12, hr_api.g_varchar2) or
1132 nvl(pay_pap_shd.g_old_rec.information13, hr_api.g_varchar2) <>
1133 nvl(p_rec.information13, hr_api.g_varchar2) or
1134 nvl(pay_pap_shd.g_old_rec.information14, hr_api.g_varchar2) <>
1135 nvl(p_rec.information14, hr_api.g_varchar2) or
1136 nvl(pay_pap_shd.g_old_rec.information15, hr_api.g_varchar2) <>
1137 nvl(p_rec.information15, hr_api.g_varchar2) or
1138 nvl(pay_pap_shd.g_old_rec.information16, hr_api.g_varchar2) <>
1139 nvl(p_rec.information16, hr_api.g_varchar2) or
1140 nvl(pay_pap_shd.g_old_rec.information17, hr_api.g_varchar2) <>
1141 nvl(p_rec.information17, hr_api.g_varchar2) or
1142 nvl(pay_pap_shd.g_old_rec.information18, hr_api.g_varchar2) <>
1143 nvl(p_rec.information18, hr_api.g_varchar2) or
1144 nvl(pay_pap_shd.g_old_rec.information19, hr_api.g_varchar2) <>
1145 nvl(p_rec.information19, hr_api.g_varchar2) or
1146 nvl(pay_pap_shd.g_old_rec.information20, hr_api.g_varchar2) <>
1147 nvl(p_rec.information20, hr_api.g_varchar2) or
1148 nvl(pay_pap_shd.g_old_rec.information21, hr_api.g_varchar2) <>
1149 nvl(p_rec.information21, hr_api.g_varchar2) or
1150 nvl(pay_pap_shd.g_old_rec.information22, hr_api.g_varchar2) <>
1151 nvl(p_rec.information22, hr_api.g_varchar2) or
1152 nvl(pay_pap_shd.g_old_rec.information23, hr_api.g_varchar2) <>
1153 nvl(p_rec.information23, hr_api.g_varchar2) or
1154 nvl(pay_pap_shd.g_old_rec.information24, hr_api.g_varchar2) <>
1155 nvl(p_rec.information24, hr_api.g_varchar2) or
1156 nvl(pay_pap_shd.g_old_rec.information25, hr_api.g_varchar2) <>
1157 nvl(p_rec.information25, hr_api.g_varchar2) or
1158 nvl(pay_pap_shd.g_old_rec.information26, hr_api.g_varchar2) <>
1159 nvl(p_rec.information26, hr_api.g_varchar2) or
1160 nvl(pay_pap_shd.g_old_rec.information27, hr_api.g_varchar2) <>
1161 nvl(p_rec.information27, hr_api.g_varchar2) or
1162 nvl(pay_pap_shd.g_old_rec.information28, hr_api.g_varchar2) <>
1163 nvl(p_rec.information28, hr_api.g_varchar2) or
1164 nvl(pay_pap_shd.g_old_rec.information29, hr_api.g_varchar2) <>
1165 nvl(p_rec.information29, hr_api.g_varchar2) or
1166 nvl(pay_pap_shd.g_old_rec.information30, hr_api.g_varchar2) <>
1167 nvl(p_rec.information30, hr_api.g_varchar2) ))
1168 or (p_rec.accrual_plan_id is null) then
1169 --
1170 -- Only execute the validation if absolutely necessary:
1171 -- a) During update, the structure column value or any
1172 -- of the attribute values have actually changed.
1173 -- b) During insert.
1174 --
1175 hr_dflex_utility.ins_or_upd_descflex_attribs
1176 (p_appl_short_name => 'PAY'
1177 ,p_descflex_name => 'Accrual Plan Developer DF'
1178 ,p_attribute_category => p_rec.information_category
1179 ,p_attribute1_name => 'INFORMATION1'
1180 ,p_attribute1_value => p_rec.information1
1181 ,p_attribute2_name => 'INFORMATION2'
1182 ,p_attribute2_value => p_rec.information2
1183 ,p_attribute3_name => 'INFORMATION3'
1184 ,p_attribute3_value => p_rec.information3
1185 ,p_attribute4_name => 'INFORMATION4'
1186 ,p_attribute4_value => p_rec.information4
1187 ,p_attribute5_name => 'INFORMATION5'
1188 ,p_attribute5_value => p_rec.information5
1189 ,p_attribute6_name => 'INFORMATION6'
1190 ,p_attribute6_value => p_rec.information6
1191 ,p_attribute7_name => 'INFORMATION7'
1192 ,p_attribute7_value => p_rec.information7
1196 ,p_attribute9_value => p_rec.information9
1193 ,p_attribute8_name => 'INFORMATION8'
1194 ,p_attribute8_value => p_rec.information8
1195 ,p_attribute9_name => 'INFORMATION9'
1197 ,p_attribute10_name => 'INFORMATION10'
1198 ,p_attribute10_value => p_rec.information10
1199 ,p_attribute11_name => 'INFORMATION11'
1200 ,p_attribute11_value => p_rec.information11
1201 ,p_attribute12_name => 'INFORMATION12'
1202 ,p_attribute12_value => p_rec.information12
1203 ,p_attribute13_name => 'INFORMATION13'
1204 ,p_attribute13_value => p_rec.information13
1205 ,p_attribute14_name => 'INFORMATION14'
1206 ,p_attribute14_value => p_rec.information14
1207 ,p_attribute15_name => 'INFORMATION15'
1208 ,p_attribute15_value => p_rec.information15
1209 ,p_attribute16_name => 'INFORMATION16'
1210 ,p_attribute16_value => p_rec.information16
1211 ,p_attribute17_name => 'INFORMATION17'
1212 ,p_attribute17_value => p_rec.information17
1213 ,p_attribute18_name => 'INFORMATION18'
1214 ,p_attribute18_value => p_rec.information18
1215 ,p_attribute19_name => 'INFORMATION19'
1216 ,p_attribute19_value => p_rec.information19
1217 ,p_attribute20_name => 'INFORMATION20'
1218 ,p_attribute21_name => 'INFORMATION21'
1219 ,p_attribute21_value => p_rec.information21
1220 ,p_attribute22_name => 'INFORMATION22'
1221 ,p_attribute22_value => p_rec.information22
1222 ,p_attribute23_name => 'INFORMATION23'
1223 ,p_attribute23_value => p_rec.information23
1224 ,p_attribute24_name => 'INFORMATION24'
1225 ,p_attribute24_value => p_rec.information24
1226 ,p_attribute25_name => 'INFORMATION25'
1227 ,p_attribute25_value => p_rec.information25
1228 ,p_attribute26_name => 'INFORMATION26'
1229 ,p_attribute26_value => p_rec.information26
1230 ,p_attribute27_name => 'INFORMATION27'
1231 ,p_attribute27_value => p_rec.information27
1232 ,p_attribute28_name => 'INFORMATION28'
1233 ,p_attribute28_value => p_rec.information28
1234 ,p_attribute29_name => 'INFORMATION29'
1235 ,p_attribute29_value => p_rec.information29
1236 ,p_attribute30_name => 'INFORMATION30'
1237 );
1238 end if;
1239 --
1240 hr_utility.set_location(' Leaving:'||l_proc,20);
1241 end chk_ddf;
1242
1243 --
1244 -- ----------------------------------------------------------------------------
1245 -- |------< chk_ddf_context >------|
1246 -- ----------------------------------------------------------------------------
1247 --
1248 -- Description
1249 -- This procedure is used to check that the ddf_context passed into the API
1250 -- is equal to the business group_id\222s legislation_code concatenated with \221_\222
1251 -- and the accrual category.
1252 --
1253 -- Pre Conditions
1254 -- None.
1255 --
1256 -- In Parameters
1257 -- accrual_plan_id PK of record being inserted or updated.
1258 -- object_version_number Object version number of record being
1259 -- inserted or updated.
1260 --
1261 -- Post Success
1262 -- Processing continues
1263 --
1264 -- Post Failure
1265 -- Errors handled by the procedure
1266 --
1267 -- Access Status
1268 -- Internal table handler use only.
1269 --
1270 Procedure chk_ddf_context( p_business_group_id in number
1271 ,p_information_category in varchar2
1272 ,p_accrual_category in varchar2
1273 ,p_accrual_plan_id in number
1274 ,p_object_version_number in number) is
1275 --
1276 l_proc varchar2(72) := g_package||'chk_ddf_context';
1277 l_api_updating boolean;
1278 l_legislation_code varchar2(30);
1279 cursor csr_leg_code is select legislation_code
1280 from per_business_Groups
1281 where business_group_id = p_business_Group_id;
1282 l_accrual_meaning varchar2(80);
1283 --
1284 Begin
1285 --
1286 hr_utility.set_location('Entering:'||l_proc, 5);
1287 --
1288 l_api_updating := pay_pap_shd.api_updating
1289 (p_accrual_plan_id => p_accrual_plan_id,
1290 p_object_version_number => p_object_version_number);
1291 --
1292
1293 if (l_api_updating
1294 and (nvl(p_information_category,hr_api.g_varchar2)
1295 <> nvl(pay_pap_shd.g_old_rec.information_category, hr_api.g_varchar2)
1296 )
1297 OR
1298 (nvl(p_accrual_category, hr_api.g_varchar2)
1299 <> nvl(pay_pap_shd.g_old_rec.accrual_category, hr_api.g_varchar2)
1300 ))
1301 OR not l_api_updating
1302 --
1303 -- it is an update with values changing, or it is an insert
1304 --
1305 then
1306 Open csr_leg_code;
1310 -- error if accrual_category is not set (it may be null on insert)
1307 Fetch csr_leg_code into l_legislation_code;
1308 Close csr_leg_code;
1309 --
1311 -- and ddf context is set
1312 -- error if leg_code + accrual_cat <> ddf context
1313
1314 if (not l_api_updating
1315 and (p_accrual_category is null and p_information_category is not null ))
1316 OR
1317 (p_information_category is not null and l_legislation_code ||'_'||
1318 p_accrual_category <> p_information_category)
1319 then
1320 fnd_message.set_name('PER','HR_289740_PAP_BAD_INFO_CONTEXT');
1321 fnd_message.raise_error;
1322 end if;
1323 -- error if accrual category is changing and ddf is already used.
1324 if (l_api_updating and nvl(p_accrual_category, hr_api.g_varchar2)
1325 <> nvl(pay_pap_shd.g_old_rec.accrual_category, hr_api.g_varchar2)
1326 and pay_pap_shd.g_old_rec.information_category is not null )
1327 then
1328 fnd_message.set_name('PER','HR_289741_PAP_CHANGE_CATEGORY');
1329 fnd_message.raise_error;
1330 end if;
1331 end if;
1332 hr_utility.set_location('Leaving:'||l_proc, 100);
1333 --
1334 End chk_ddf_context;
1335 --
1336 -- ----------------------------------------------------------------------------
1337 -- |---------------------------< insert_validate >----------------------------|
1338 -- ----------------------------------------------------------------------------
1339 PROCEDURE insert_validate
1340 (p_effective_date IN DATE
1341 ,p_rec IN pay_pap_shd.g_rec_type
1342 ,p_check_accrual_ff OUT NOCOPY BOOLEAN)
1343 IS
1344
1345 l_proc varchar2(72) := g_package||'insert_validate';
1346
1347 BEGIN
1348
1349 hr_utility.set_location('Entering:'||l_proc, 10);
1350
1351 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1352
1353 --
1354 -- Call all supporting business operations
1355 --
1356 --
1357 -- Check the accrual plan name.
1358 --
1359 chk_accrual_plan_name
1360 (p_accrual_plan_id => p_rec.accrual_plan_id
1361 ,p_accrual_plan_name => p_rec.accrual_plan_name
1362 ,p_business_group_id => p_rec.business_group_id);
1363
1364 hr_utility.set_location(l_proc, 20);
1365
1366 --
1367 -- Check the accrual category.
1368 --
1369 chk_accrual_category
1370 (p_effective_date => p_effective_date
1371 ,p_accrual_plan_id => p_rec.accrual_plan_id
1372 ,p_object_version_number => p_rec.object_version_number
1373 ,p_accrual_category => p_rec.accrual_category);
1374
1375 hr_utility.set_location(l_proc, 30);
1376
1377 --
1378 -- Check the accrual start.
1379 --
1380 chk_accrual_start
1381 (p_effective_date => p_effective_date
1382 ,p_accrual_plan_id => p_rec.accrual_plan_id
1383 ,p_object_version_number => p_rec.object_version_number
1384 ,p_accrual_start => p_rec.accrual_start);
1385
1386 hr_utility.set_location(l_proc, 40);
1387
1388 --
1389 -- Check the accrual UOM.
1390 --
1391 chk_accrual_units_of_measure
1392 (p_effective_date => p_effective_date
1393 ,p_accrual_plan_id => p_rec.accrual_plan_id
1394 ,p_object_version_number => p_rec.object_version_number
1395 ,p_accrual_units_of_measure => p_rec.accrual_units_of_measure);
1396
1397 hr_utility.set_location(l_proc, 50);
1398
1399 --
1400 -- Check the accrual formula.
1401 --
1402 chk_accrual_formula_id
1403 (p_effective_date => p_effective_date
1404 ,p_accrual_plan_id => p_rec.accrual_plan_id
1405 ,p_object_version_number => p_rec.object_version_number
1406 ,p_business_group_id => p_rec.business_group_id
1407 ,p_accrual_formula_id => p_rec.accrual_formula_id);
1408
1409 hr_utility.set_location(l_proc, 60);
1410
1411 --
1412 -- Check the carry over formula.
1413 --
1414 chk_co_formula_id
1415 (p_effective_date => p_effective_date
1416 ,p_accrual_plan_id => p_rec.accrual_plan_id
1417 ,p_object_version_number => p_rec.object_version_number
1418 ,p_business_group_id => p_rec.business_group_id
1419 ,p_accrual_formula_id => p_rec.accrual_formula_id
1420 ,p_co_formula_id => p_rec.co_formula_id);
1421
1422 hr_utility.set_location(l_proc, 70);
1423
1424 --
1425 -- Check the absence element's input value
1426 --
1427 chk_pto_input_value_id
1428 (p_accrual_plan_id => p_rec.accrual_plan_id
1429 ,p_object_version_number => p_rec.object_version_number
1430 ,p_pto_input_value_id => p_rec.pto_input_value_id
1431 ,p_business_group_id => p_rec.business_group_id
1432 ,p_accrual_units_of_measure => p_rec.accrual_units_of_measure);
1433
1434 hr_utility.set_location(l_proc, 80);
1435
1436 --
1437 -- Check the defined balance.
1438 --
1439 chk_defined_balance_id
1440 (p_effective_date => p_effective_date
1444 ,p_accrual_formula_id => p_rec.accrual_formula_id
1441 ,p_accrual_plan_id => p_rec.accrual_plan_id
1442 ,p_object_version_number => p_rec.object_version_number
1443 ,p_business_group_id => p_rec.business_group_id
1445 ,p_defined_balance_id => p_rec.defined_balance_id
1446 ,p_check_accrual_ff => p_check_accrual_ff);
1447
1448 hr_utility.set_location(l_proc, 90);
1449
1450 --
1451 -- Check the ineligible period type.
1452 --
1453 chk_ineligible_period_type
1454 (p_effective_date => p_effective_date
1455 ,p_accrual_plan_id => p_rec.accrual_plan_id
1456 ,p_object_version_number => p_rec.object_version_number
1457 ,p_ineligible_period_type => p_rec.ineligible_period_type);
1458
1459 hr_utility.set_location(l_proc, 100);
1460
1461 --
1462 -- Check the ineligibility formula.
1463 --
1464 chk_ineligibility_formula_id
1465 (p_effective_date => p_effective_date
1466 ,p_accrual_plan_id => p_rec.accrual_plan_id
1467 ,p_object_version_number => p_rec.object_version_number
1468 ,p_business_group_id => p_rec.business_group_id
1469 ,p_ineligibility_formula_id => p_rec.ineligibility_formula_id);
1470
1471 hr_utility.set_location(l_proc, 110);
1472
1473 --
1474 -- Check the flexfields.
1475 --
1476 pay_pap_bus.chk_ddf (p_rec);
1477
1478 hr_utility.set_location(l_proc, 120);
1479
1480 pay_pap_bus.chk_ddf_context
1481 (p_business_group_id => p_rec.business_group_id
1482 ,p_information_category => p_rec.information_category
1483 ,p_accrual_category => p_rec.accrual_category
1484 ,p_accrual_plan_id => p_rec.accrual_plan_id
1485 ,p_object_version_number => p_rec.object_version_number);
1486
1487 hr_utility.set_location(' Leaving:'||l_proc, 130);
1488
1489 END insert_validate;
1490 --
1491 -- ----------------------------------------------------------------------------
1492 -- |---------------------------< update_validate >----------------------------|
1493 -- ----------------------------------------------------------------------------
1494 PROCEDURE update_validate
1495 (p_effective_date IN DATE
1496 ,p_rec IN pay_pap_shd.g_rec_type
1497 ,p_check_accrual_ff OUT NOCOPY BOOLEAN)
1498 IS
1499
1500 l_proc varchar2(72) := g_package||'update_validate';
1501
1502 BEGIN
1503
1504 hr_utility.set_location('Entering:'||l_proc, 10);
1505
1506 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1507
1508 --
1509 -- Call all supporting business operations
1510 --
1511
1512 hr_utility.set_location(l_proc, 20);
1513
1514 --
1515 -- Check the accrual category.
1516 --
1517 chk_accrual_category
1518 (p_effective_date => p_effective_date
1519 ,p_accrual_plan_id => p_rec.accrual_plan_id
1520 ,p_object_version_number => p_rec.object_version_number
1521 ,p_accrual_category => p_rec.accrual_category);
1522
1523 hr_utility.set_location(l_proc, 30);
1524
1525 --
1526 -- Check the accrual start.
1527 --
1528 chk_accrual_start
1529 (p_effective_date => p_effective_date
1530 ,p_accrual_plan_id => p_rec.accrual_plan_id
1531 ,p_object_version_number => p_rec.object_version_number
1532 ,p_accrual_start => p_rec.accrual_start);
1533
1534 hr_utility.set_location(l_proc, 40);
1535
1536 --
1537 -- Check the accrual formula.
1538 --
1539 chk_accrual_formula_id
1540 (p_effective_date => p_effective_date
1541 ,p_accrual_plan_id => p_rec.accrual_plan_id
1542 ,p_object_version_number => p_rec.object_version_number
1543 ,p_business_group_id => p_rec.business_group_id
1544 ,p_accrual_formula_id => p_rec.accrual_formula_id);
1545
1546 hr_utility.set_location(l_proc, 50);
1547
1548 --
1549 -- Check the carry over formula.
1550 --
1551 chk_co_formula_id
1552 (p_effective_date => p_effective_date
1553 ,p_accrual_plan_id => p_rec.accrual_plan_id
1554 ,p_object_version_number => p_rec.object_version_number
1555 ,p_business_group_id => p_rec.business_group_id
1556 ,p_accrual_formula_id => p_rec.accrual_formula_id
1557 ,p_co_formula_id => p_rec.co_formula_id);
1558
1559 hr_utility.set_location(l_proc, 60);
1560
1561 --
1562 -- Check the absence element's input value
1563 --
1564 chk_pto_input_value_id
1565 (p_accrual_plan_id => p_rec.accrual_plan_id
1566 ,p_object_version_number => p_rec.object_version_number
1567 ,p_pto_input_value_id => p_rec.pto_input_value_id
1568 ,p_business_group_id => p_rec.business_group_id
1569 ,p_accrual_units_of_measure => p_rec.accrual_units_of_measure);
1570
1571 hr_utility.set_location(l_proc, 70);
1572
1573 --
1574 -- Check the defined balance.
1575 --
1576 chk_defined_balance_id
1577 (p_effective_date => p_effective_date
1581 ,p_accrual_formula_id => p_rec.accrual_formula_id
1578 ,p_accrual_plan_id => p_rec.accrual_plan_id
1579 ,p_object_version_number => p_rec.object_version_number
1580 ,p_business_group_id => p_rec.business_group_id
1582 ,p_defined_balance_id => p_rec.defined_balance_id
1583 ,p_check_accrual_ff => p_check_accrual_ff);
1584
1585 hr_utility.set_location(l_proc, 80);
1586
1587 --
1588 -- Check the ineligible period type.
1589 --
1590 chk_ineligible_period_type
1591 (p_effective_date => p_effective_date
1592 ,p_accrual_plan_id => p_rec.accrual_plan_id
1593 ,p_object_version_number => p_rec.object_version_number
1594 ,p_ineligible_period_type => p_rec.ineligible_period_type);
1595
1596 hr_utility.set_location(l_proc, 90);
1597
1598 --
1599 -- Check the ineligibility formula.
1600 --
1601 chk_ineligibility_formula_id
1602 (p_effective_date => p_effective_date
1603 ,p_accrual_plan_id => p_rec.accrual_plan_id
1604 ,p_object_version_number => p_rec.object_version_number
1605 ,p_business_group_id => p_rec.business_group_id
1606 ,p_ineligibility_formula_id => p_rec.ineligibility_formula_id);
1607
1608 hr_utility.set_location(l_proc, 100);
1609
1610 --
1611 -- Check the flexfields.
1612 --
1613 pay_pap_bus.chk_ddf (p_rec);
1614
1615 hr_utility.set_location(l_proc, 110);
1616
1617 pay_pap_bus.chk_ddf_context
1618 (p_business_group_id => p_rec.business_group_id
1619 ,p_information_category => p_rec.information_category
1620 ,p_accrual_category => p_rec.accrual_category
1621 ,p_accrual_plan_id => p_rec.accrual_plan_id
1622 ,p_object_version_number => p_rec.object_version_number);
1623
1624 hr_utility.set_location(' Leaving:'||l_proc, 120);
1625
1626 END update_validate;
1627 --
1628 -- ----------------------------------------------------------------------------
1629 -- |---------------------------< delete_validate >----------------------------|
1630 -- ----------------------------------------------------------------------------
1631 Procedure delete_validate(p_rec in pay_pap_shd.g_rec_type) is
1632 --
1633 l_proc varchar2(72) := g_package||'delete_validate';
1634 --
1635 Begin
1636 hr_utility.set_location('Entering:'||l_proc, 5);
1637 --
1638 -- Call all supporting business operations
1639 --
1640 hr_utility.set_location(' Leaving:'||l_proc, 10);
1641 End delete_validate;
1642 --
1643 --
1644 -- ---------------------------------------------------------------------------
1645 -- |---------------------< return_legislation_code >-------------------------|
1646 -- ---------------------------------------------------------------------------
1647 --
1648 function return_legislation_code
1649 (p_accrual_plan_id in number) return varchar2 is
1650 --
1651 -- Declare cursor
1652 --
1653 cursor csr_leg_code is
1654 select a.legislation_code
1658 and a.business_group_id = b.business_group_id;
1655 from per_business_groups a,
1656 pay_accrual_plans b
1657 where b.accrual_plan_id = p_accrual_plan_id
1659 --
1660 -- Declare local variables
1661 --
1662 l_legislation_code varchar2(150);
1663 l_proc varchar2(72) := g_package||'return_legislation_code';
1664 --
1665 begin
1666 --
1667 hr_utility.set_location('Entering:'|| l_proc, 10);
1668 --
1669 -- Ensure that all the mandatory parameter are not null
1670 --
1671 hr_api.mandatory_arg_error(p_api_name => l_proc,
1672 p_argument => 'accrual_plan_id',
1673 p_argument_value => p_accrual_plan_id);
1674 --
1675 open csr_leg_code;
1676 --
1677 fetch csr_leg_code into l_legislation_code;
1678 --
1679 if csr_leg_code%notfound then
1680 --
1681 close csr_leg_code;
1682 --
1683 -- The primary key is invalid therefore we must error
1684 --
1685 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1686 fnd_message.raise_error;
1687 --
1688 end if;
1689 --
1690 close csr_leg_code;
1691 --
1692 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1693 --
1694 return l_legislation_code;
1695 --
1696 end return_legislation_code;
1697 --
1698 -- ----------------------------------------------------------------------------
1699 -- |------------------------< set_security_group_id >-------------------------|
1700 -- ----------------------------------------------------------------------------
1701 --
1702 /*
1703 procedure set_security_group_id
1704 (p_accrual_plan_id in pay_accrual_plans.accrual_plan_id%TYPE
1705 ) is
1706 --
1707 -- Declare cursor
1708 --
1709 cursor csr_sec_grp is
1710 select pbg.security_group_id
1711 from per_business_groups pbg
1712 , pay_accrual_plans pap
1713 where pap.accrual_plan_id = p_accrual_plan_id
1714 and pbg.business_group_id = pap.business_group_id
1715 order by pap.accrual_plan_name;
1716 --
1717 -- Local variables
1718 --
1719 l_security_group_id number;
1720 l_proc varchar2(72) := g_package||'set_security_group_id';
1721 begin
1722 hr_utility.set_location('Entering:'|| l_proc, 10);
1723 --
1724 -- Ensure that all the mandatory parameter are not null
1725 --
1726 hr_api.mandatory_arg_error(p_api_name => l_proc,
1727 p_argument => 'accrual_plan_id',
1728 p_argument_value => p_accrual_plan_id);
1729 --
1730 open csr_sec_grp;
1731 fetch csr_sec_grp into l_security_group_id;
1732 if csr_sec_grp%notfound then
1733 close csr_sec_grp;
1734 --
1735 -- The primary key is invalid therefore we must error
1736 --
1737 fnd_message.set_name('PAY', 'HR_7220_INVALID_PRIMARY_KEY');
1738 fnd_message.raise_error;
1739 end if;
1740 close csr_sec_grp;
1741 --
1742 -- Set the security_group_id in CLIENT_INFO
1743 --
1744 hr_api.set_security_group_id
1745 (p_security_group_id => l_security_group_id
1746 );
1747 --
1748 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1749 end set_security_group_id;
1750 --
1751 */
1752 end pay_pap_bus;