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