1 Package Body pay_frr_bus as
2 /* $Header: pyfrrrhi.pkb 120.3.12020000.2 2012/07/06 14:00:07 asnell ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_frr_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_formula_result_rule_id number default null;
15 --
16 -- This global variable would be used in the chk_ procedures
17 --
18 g_exists varchar2(1) default null;
19 --
20 -- ---------------------------------------------------------------------------
21 -- |----------------------< set_security_group_id >--------------------------|
22 -- ---------------------------------------------------------------------------
23 --
24 Procedure set_security_group_id
25 (p_formula_result_rule_id in number
26 ,p_associated_column1 in varchar2 default null
27 ) is
28 --
29 -- Declare cursor
30 --
31 cursor csr_sec_grp is
32 select pbg.security_group_id,
33 pbg.legislation_code
34 from per_business_groups_perf pbg
35 , pay_formula_result_rules_f frr
36 where frr.formula_result_rule_id = p_formula_result_rule_id
37 and pbg.business_group_id (+) = frr.business_group_id;
38 --
39 -- Declare local variables
40 --
41 l_security_group_id number;
42 l_proc varchar2(72) := g_package||'set_security_group_id';
43 l_legislation_code varchar2(150);
44 --
45 begin
46 --
47 hr_utility.set_location('Entering:'|| l_proc, 10);
48 --
49 -- Ensure that all the mandatory parameter are not null
50 --
51 hr_api.mandatory_arg_error
52 (p_api_name => l_proc
53 ,p_argument => 'formula_result_rule_id'
54 ,p_argument_value => p_formula_result_rule_id
55 );
56 --
57 open csr_sec_grp;
58 fetch csr_sec_grp into l_security_group_id
59 , l_legislation_code;
60 --
61 if csr_sec_grp%notfound then
62 --
63 close csr_sec_grp;
64 --
65 -- The primary key is invalid therefore we must error
66 --
67 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
68 hr_multi_message.add
69 (p_associated_column1
70 => nvl(p_associated_column1,'FORMULA_RESULT_RULE_ID')
71 );
72 --
73 else
74 close csr_sec_grp;
75 --
76 -- Set the security_group_id in CLIENT_INFO
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 --
82 -- Set the sessions legislation context in HR_SESSION_DATA
83 --
84 hr_api.set_legislation_context(l_legislation_code);
85 end if;
86 --
87 hr_utility.set_location(' Leaving:'|| l_proc, 20);
88 --
89 end set_security_group_id;
90 --
91 -- ---------------------------------------------------------------------------
92 -- |---------------------< return_legislation_code >-------------------------|
93 -- ---------------------------------------------------------------------------
94 --
95 Function return_legislation_code
96 (p_formula_result_rule_id in number
97 )
98 Return Varchar2 Is
99 --
100 -- Declare cursor
101 --
102 cursor csr_leg_code is
103 select pbg.legislation_code
104 from per_business_groups_perf pbg
105 , pay_formula_result_rules_f frr
106 where frr.formula_result_rule_id = p_formula_result_rule_id
107 and pbg.business_group_id (+) = frr.business_group_id;
108 --
109 -- Declare local variables
110 --
111 l_legislation_code varchar2(150);
112 l_proc varchar2(72) := g_package||'return_legislation_code';
113 --
114 Begin
115 --
116 hr_utility.set_location('Entering:'|| l_proc, 10);
117 --
118 -- Ensure that all the mandatory parameter are not null
119 --
120 hr_api.mandatory_arg_error
121 (p_api_name => l_proc
122 ,p_argument => 'formula_result_rule_id'
123 ,p_argument_value => p_formula_result_rule_id
124 );
125 --
126 if ( nvl(pay_frr_bus.g_formula_result_rule_id, hr_api.g_number)
127 = p_formula_result_rule_id) then
128 --
129 -- The legislation code has already been found with a previous
130 -- call to this function. Just return the value in the global
131 -- variable.
132 --
133 l_legislation_code := pay_frr_bus.g_legislation_code;
134 hr_utility.set_location(l_proc, 20);
135 else
136 --
137 -- The ID is different to the last call to this function
138 -- or this is the first call to this function.
139 --
140 open csr_leg_code;
141 fetch csr_leg_code into l_legislation_code;
142 --
143 if csr_leg_code%notfound then
144 --
145 -- The primary key is invalid therefore we must error
146 --
147 close csr_leg_code;
148 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
149 fnd_message.raise_error;
150 end if;
151 hr_utility.set_location(l_proc,30);
152 --
153 -- Set the global variables so the values are
154 -- available for the next call to this function.
155 --
156 close csr_leg_code;
157 pay_frr_bus.g_formula_result_rule_id := p_formula_result_rule_id;
158 pay_frr_bus.g_legislation_code := l_legislation_code;
159 end if;
160 hr_utility.set_location(' Leaving:'|| l_proc, 40);
161 return l_legislation_code;
162 end return_legislation_code;
163 --
164 -- ----------------------------------------------------------------------------
165 -- |-----------------------< chk_non_updateable_args >------------------------|
166 -- ----------------------------------------------------------------------------
167 -- {Start Of Comments}
168 --
169 -- Description:
170 -- This procedure is used to ensure that non updateable attributes have
171 -- not been updated. If an attribute has been updated an error is generated.
172 --
173 -- Pre Conditions:
174 -- g_old_rec has been populated with details of the values currently in
175 -- the database.
176 --
177 -- In Arguments:
178 -- p_rec has been populated with the updated values the user would like the
179 -- record set to.
180 --
181 -- Post Success:
182 -- Processing continues if all the non updateable attributes have not
183 -- changed.
184 --
185 -- Post Failure:
186 -- An application error is raised if any of the non updatable attributes
187 -- have been altered.
188 --
189 -- {End Of Comments}
190 -- ----------------------------------------------------------------------------
191 Procedure chk_non_updateable_args
192 (p_effective_date in date
193 ,p_rec in pay_frr_shd.g_rec_type
194 ) IS
195 --
196 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
197 l_error exception;
198 l_argument varchar2(30);
199 --
200 Begin
201 --
202 -- Only proceed with the validation if a row exists for the current
203 -- record in the HR Schema.
204 --
205 IF NOT pay_frr_shd.api_updating
206 (p_formula_result_rule_id => p_rec.formula_result_rule_id
207 ,p_effective_date => p_effective_date
208 ,p_object_version_number => p_rec.object_version_number
209 ) THEN
210 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
211 fnd_message.set_token('PROCEDURE ', l_proc);
212 fnd_message.set_token('STEP ', '5');
213 fnd_message.raise_error;
214 END IF;
215 --
216 -- Ensure that the following attributes are not updated.
217 --
218 If nvl(p_rec.business_group_id,hr_api.g_number) <>
219 nvl(pay_frr_shd.g_old_rec.business_group_id,hr_api.g_number) then
220 --
221 l_argument := 'business_group_id';
222 raise l_error;
223 --
224 End if;
225 --
226 If nvl(p_rec.legislation_code,hr_api.g_varchar2) <>
227 nvl(pay_frr_shd.g_old_rec.legislation_code,hr_api.g_varchar2) then
228 --
229 l_argument := 'legislation_code';
230 raise l_error;
231 --
232 End if;
233 --
234 If nvl(p_rec.status_processing_rule_id,hr_api.g_number) <>
235 nvl(pay_frr_shd.g_old_rec.status_processing_rule_id,hr_api.g_number) then
236 --
237 l_argument := 'status_processing_rule_id';
238 raise l_error;
239 --
240 End if;
241 --
242 If nvl(p_rec.result_name,hr_api.g_varchar2) <>
243 nvl(pay_frr_shd.g_old_rec.result_name,hr_api.g_varchar2) then
244 --
245 l_argument := 'result_name';
246 raise l_error;
247 --
248 End if;
249 --
250 If nvl(p_rec.legislation_subgroup,hr_api.g_varchar2) <>
251 nvl(pay_frr_shd.g_old_rec.legislation_subgroup,hr_api.g_varchar2) then
252 --
253 l_argument := 'legislation_subgroup';
254 raise l_error;
255 --
256 End if;
257 --
258 EXCEPTION
259 WHEN l_error THEN
260 hr_api.argument_changed_error
261 (p_api_name => l_proc
262 ,p_argument => l_argument);
263 WHEN OTHERS THEN
264 RAISE;
265 End chk_non_updateable_args;
266 --
267 -- ----------------------------------------------------------------------------
268 -- |--------------------------< dt_update_validate >--------------------------|
269 -- ----------------------------------------------------------------------------
270 -- {Start Of Comments}
271 --
272 -- Description:
273 -- This procedure is used for referential integrity of datetracked
274 -- parent entities when a datetrack update operation is taking place
275 -- and where there is no cascading of update defined for this entity.
276 --
277 -- Prerequisites:
278 -- This procedure is called from the update_validate.
279 --
280 -- In Parameters:
281 --
282 -- Post Success:
283 -- Processing continues.
284 --
285 -- Post Failure:
286 --
287 -- Developer Implementation Notes:
288 -- This procedure should not need maintenance unless the HR Schema model
289 -- changes.
290 --
291 -- Access Status:
292 -- Internal Row Handler Use Only.
293 --
294 -- {End Of Comments}
295 -- ----------------------------------------------------------------------------
296 Procedure dt_update_validate
297 (p_datetrack_mode in varchar2
298 ,p_validation_start_date in date
299 ,p_validation_end_date in date
300 ) Is
301 --
302 l_proc varchar2(72) := g_package||'dt_update_validate';
303 --
304 Begin
305 --
306 -- Ensure that the p_datetrack_mode argument is not null
307 --
308 hr_api.mandatory_arg_error
309 (p_api_name => l_proc
310 ,p_argument => 'datetrack_mode'
311 ,p_argument_value => p_datetrack_mode
312 );
313 --
314 -- Mode will be valid, as this is checked at the start of the upd.
315 --
316 -- Ensure the arguments are not null
317 --
318 hr_api.mandatory_arg_error
319 (p_api_name => l_proc
320 ,p_argument => 'validation_start_date'
321 ,p_argument_value => p_validation_start_date
322 );
323 --
324 hr_api.mandatory_arg_error
325 (p_api_name => l_proc
326 ,p_argument => 'validation_end_date'
327 ,p_argument_value => p_validation_end_date
328 );
329 --
330 --
331 --
332 Exception
333 When Others Then
334 --
335 -- An unhandled or unexpected error has occurred which
336 -- we must report
337 --
338 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
339 fnd_message.set_token('PROCEDURE', l_proc);
340 fnd_message.set_token('STEP','15');
341 fnd_message.raise_error;
342 End dt_update_validate;
343 --
344 -- ----------------------------------------------------------------------------
345 -- |--------------------------< dt_delete_validate >--------------------------|
346 -- ----------------------------------------------------------------------------
347 -- {Start Of Comments}
348 --
349 -- Description:
350 -- This procedure is used for referential integrity of datetracked
351 -- child entities when either a datetrack DELETE or ZAP is in operation
352 -- and where there is no cascading of delete defined for this entity.
353 -- For the datetrack mode of DELETE or ZAP we must ensure that no
354 -- datetracked child rows exist between the validation start and end
355 -- dates.
356 --
357 -- Prerequisites:
358 -- This procedure is called from the delete_validate.
359 --
360 -- In Parameters:
361 --
362 -- Post Success:
363 -- Processing continues.
364 --
365 -- Post Failure:
366 -- If a row exists by determining the returning Boolean value from the
367 -- generic dt_api.rows_exist function then we must supply an error via
368 -- the use of the local exception handler l_rows_exist.
369 --
370 -- Developer Implementation Notes:
371 -- This procedure should not need maintenance unless the HR Schema model
372 -- changes.
373 --
374 -- Access Status:
375 -- Internal Row Handler Use Only.
376 --
377 -- {End Of Comments}
378 -- ----------------------------------------------------------------------------
379 Procedure dt_delete_validate
380 (p_formula_result_rule_id in number
381 ,p_datetrack_mode in varchar2
382 ,p_validation_start_date in date
383 ,p_validation_end_date in date
384 ) Is
385 --
386 l_proc varchar2(72) := g_package||'dt_delete_validate';
387 --
388 Begin
389 --
390 -- Ensure that the p_datetrack_mode argument is not null
391 --
392 hr_api.mandatory_arg_error
393 (p_api_name => l_proc
394 ,p_argument => 'datetrack_mode'
395 ,p_argument_value => p_datetrack_mode
396 );
397 --
398 -- Only perform the validation if the datetrack mode is either
399 -- DELETE or ZAP
400 --
401 If (p_datetrack_mode = hr_api.g_delete or
402 p_datetrack_mode = hr_api.g_zap) then
403 --
404 --
405 -- Ensure the arguments are not null
406 --
407 hr_api.mandatory_arg_error
408 (p_api_name => l_proc
409 ,p_argument => 'validation_start_date'
410 ,p_argument_value => p_validation_start_date
411 );
412 --
413 hr_api.mandatory_arg_error
414 (p_api_name => l_proc
415 ,p_argument => 'validation_end_date'
416 ,p_argument_value => p_validation_end_date
417 );
418 --
419 hr_api.mandatory_arg_error
420 (p_api_name => l_proc
421 ,p_argument => 'formula_result_rule_id'
422 ,p_argument_value => p_formula_result_rule_id
423 );
424 --
425 --
426 --
427 End If;
428 --
429 Exception
430 When Others Then
431 --
432 -- An unhandled or unexpected error has occurred which
433 -- we must report
434 --
435 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
436 fnd_message.set_token('PROCEDURE', l_proc);
437 fnd_message.set_token('STEP','15');
438 fnd_message.raise_error;
439 --
440 End dt_delete_validate;
441 --
442 -- ----------------------------------------------------------------------------
443 -- |------------------------< chk_legislation_code >--------------------------|
444 -- ----------------------------------------------------------------------------
445 --
446 -- Description:
447 -- This procedure is used to validate the legislation code against the
448 -- parent table
449 --
450 -- ----------------------------------------------------------------------------
451 Procedure chk_legislation_code
452 (p_legislation_code in varchar2)
453 is
454 --
455 l_proc varchar2(72) := g_package||'chk_legislation_code';
456
457 Cursor c_chk_leg_code
458 is
459 select null
460 from fnd_territories
461 where territory_code = p_legislation_code;
462 --
463 Begin
464 hr_utility.set_location('Entering:'||l_proc, 5);
465 --
466 If p_legislation_code is not null then
467
468 Open c_chk_leg_code;
469 Fetch c_chk_leg_code into g_exists;
470 If c_chk_leg_code%notfound Then
471 --
472 Close c_chk_leg_code;
473 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
474 fnd_message.set_token('COLUMN','LEGISLATION_CODE');
475 fnd_message.set_token('TABLE','FND_TERRITORIES');
476 fnd_message.raise_error;
477 --
478 End If;
479 Close c_chk_leg_code;
480
481 End If;
482 --
483 hr_utility.set_location('Leaving:'||l_proc, 10);
484 End;
485 --
486 -- ----------------------------------------------------------------------------
487 -- |------------------------< chk_element_type_id >---------------------------|
488 -- ----------------------------------------------------------------------------
489 --
490 -- Description:
491 -- This procedure is used to validate the element type id against the
492 -- parent table and to check whether the business group and legislation code
493 -- are consistent with those of the element type.
494 --
495 -- ----------------------------------------------------------------------------
496 Procedure chk_element_type_id
497 (p_effective_date in date
498 ,p_element_type_id in number
499 ,p_business_group_id in number
500 ,p_legislation_code in varchar2
501 ) is
502 --
503 l_proc varchar2(72) := g_package||'chk_element_type_id';
504
505 Cursor c_chk_element_type
506 is
507 select null
508 from pay_element_types_f pet
509 where pet.element_type_id = p_element_type_id
510 and p_effective_date between pet.effective_start_date
511 and pet.effective_end_date
512 and ((p_business_group_id is not null and
513 ((pet.business_group_id = p_business_group_id)
514 or
515 (pet.legislation_code =
516 hr_api.return_legislation_code(p_business_group_id))
517 ))
518 --
519 or
520 (p_legislation_code is not null
521 and pet.legislation_code = p_legislation_code
522 ));
523 --
524 Begin
525 hr_utility.set_location('Entering:'||l_proc, 5);
526 --
527 open c_chk_element_type;
528 fetch c_chk_element_type into g_exists;
529 if c_chk_element_type%notfound then
530 close c_chk_element_type;
531 fnd_message.set_name('PAY','PAY_34171_FRR_INVALID_FK');
532 fnd_message.set_token('1','element type');
533 fnd_message.raise_error;
534 end if;
535 close c_chk_element_type;
536 --
537 hr_utility.set_location('Leaving:'||l_proc, 10);
538 End;
539 --
540 -- ----------------------------------------------------------------------------
541 -- |--------------------< chk_status_processing_rule_id >---------------------|
542 -- ----------------------------------------------------------------------------
543 --
544 -- Description:
545 -- This procedure is used to validate the status_processing_rule_id against
546 -- the parent table and to check whether the business group and legislation
547 -- code are consistent with those of the SPR.
548 --
549 -- ----------------------------------------------------------------------------
550 Procedure chk_status_processing_rule_id
551 (p_effective_date in date
552 ,p_status_processing_rule_id in number
553 ,p_business_group_id in number
554 ,p_legislation_code in varchar2
555 ) is
556 --
557 l_proc varchar2(72) := g_package||'chk_spr_id';
558
559 Cursor c_chk_spr
560 is
561 select null
562 from pay_status_processing_rules_f spr
563 where spr.status_processing_rule_id = p_status_processing_rule_id
564 and p_effective_date between spr.effective_start_date
565 and spr.effective_end_date
566 and ((p_business_group_id is not null and
567 ((spr.business_group_id = p_business_group_id)
568 or
569 (spr.legislation_code =
570 hr_api.return_legislation_code(p_business_group_id))
571 ))
572 --
573 or
574 (p_legislation_code is not null and
575 (spr.legislation_code = p_legislation_code)
576 ));
577 --
578 Begin
579 hr_utility.set_location('Entering:'||l_proc, 5);
580 --
581 open c_chk_spr;
582 fetch c_chk_spr into g_exists;
583 if c_chk_spr%notfound then
584 close c_chk_spr;
585 fnd_message.set_name('PAY','PAY_34171_FRR_INVALID_FK');
586 fnd_message.set_token('1','status processing rule');
587 fnd_message.raise_error;
588 end if;
589 close c_chk_spr;
590 --
591 hr_utility.set_location('Leaving:'||l_proc, 10);
592 End;
593 --
594 -- ----------------------------------------------------------------------------
595 -- |---------------------------< chk_result_name >----------------------------|
596 -- ----------------------------------------------------------------------------
597 --
598 -- Description:
599 -- This procedure is used to check whether the result name is same as the
600 -- item name in formula dictionary and the item is either output or both
601 -- (input and output).
602 --
603 -- ----------------------------------------------------------------------------
604 Procedure chk_result_name
605 (p_effective_date in date
606 ,p_status_processing_rule_id in number
607 ,p_result_name in varchar2
608 ) is
609 --
610 l_proc varchar2(72) := g_package||'chk_result_name';
611 --
612 Cursor c_chk_result_name
613 is
614 select null
615 from ff_fdi_usages_f fdu
616 ,pay_status_processing_rules_f spr
617 where spr.status_processing_rule_id = p_status_processing_rule_id
618 and fdu.formula_id = spr.formula_id
619 and fdu.usage in ('O', 'B')
620 and fdu.item_name = p_result_name
621 and p_effective_date between spr.effective_start_date
622 and spr.effective_end_date
623 and p_effective_date between fdu.effective_start_date
624 and fdu.effective_end_date;
625 --
626 Begin
627 hr_utility.set_location('Entering:'||l_proc, 5);
628 --
629 open c_chk_result_name;
630 fetch c_chk_result_name into g_exists;
631 if c_chk_result_name%notfound then
632 fnd_message.set_name('PAY','PAY_34156_FRR_NAME_INVALID');
633 IF hr_startup_data_api_support.g_startup_mode NOT IN ('STARTUP') THEN
634 close c_chk_result_name;
635 fnd_message.raise_error;
636 ELSE
637 hr_utility.trace('PAY_34156_FRR_NAME_INVALID Warning in startup mode: formula result_name:'
638 ||p_result_name|| ' not found for spr_id:'
639 ||to_char(p_status_processing_rule_id)
640 ||' effective_date:'||to_char(p_effective_date));
641 hr_utility.trace('SQLERRM:'||SQLERRM);
642 END IF;
643 end if;
644 close c_chk_result_name;
645 --
646 hr_utility.set_location('Leaving:'||l_proc, 10);
647 End;
648 --
649 --
650 -- ----------------------------------------------------------------------------
651 -- |-------------------------< chk_common_rules >-----------------------------|
652 -- ----------------------------------------------------------------------------
653 --
654 -- Description:
655 -- This procedure is used to check the dependent attributes of different
656 -- result rule types.
657 --
658 -- ----------------------------------------------------------------------------
659 Procedure chk_common_rules
660 (p_element_type_id in number default null
661 ,p_result_rule_type in varchar2
662 ,p_severity_level in varchar2 default null
663 ,p_input_value_id in number default null
664 ) is
665 --
666 l_proc varchar2(72) := g_package||'chk_common_rules';
667 --
668 Begin
669 hr_utility.set_location('Entering:'||l_proc, 5);
670 --
671 If p_result_rule_type <> 'M' then -- message
672 --
673 -- severity must be entered
674 --
675 If p_severity_level is not null then
676 --
677 fnd_message.set_name('PAY','PAY_34168_FRR_NO_SEVERITY');
678 fnd_message.raise_error;
679 --
680 End If;
681 --
682 If p_result_rule_type in ('I','O','U','S') then -- others
683 --
684 -- element type must be entered
685 --
686 If p_element_type_id is null then
687 --
688 fnd_message.set_name('PAY','PAY_34158_FRR_ELEMENT_REQD');
689 fnd_message.raise_error;
690 --
691 End If;
692 --
693 If p_result_rule_type in ('I','U') then
694 --
695 -- input value must be entered for indirect and update recurring
696 -- rules.
697 --
698 If p_input_value_id is null then
699 --
700 fnd_message.set_name('PAY','PAY_34159_FRR_INPUT_VALUE_REQD');
701 fnd_message.raise_error;
702 --
703 End If;
704 --
705 Else
706 --
707 -- input value must be null for other rules.
708 --
709 If p_input_value_id is not null then
710 --
711 fnd_message.set_name('PAY','PAY_34169_FRR_NO_INPUT_VALUE');
712 fnd_message.raise_error;
713 --
714 End If;
715 --
716 End If;
717 --
718 Elsif p_result_rule_type = 'D' then
719 --
720 -- input value must be entered for direct rule
721 --
722 If p_input_value_id is null then
723 --
724 fnd_message.set_name('PAY','PAY_34159_FRR_INPUT_VALUE_REQD');
725 fnd_message.raise_error;
726 --
727 End If;
728 --
729 End If;
730 --
731 End If;
732 --
733 hr_utility.set_location('Leaving:'||l_proc, 10);
734 End;
735 --
736 -- ----------------------------------------------------------------------------
737 -- |-------------------------< chk_result_rule_type >-------------------------|
738 -- ----------------------------------------------------------------------------
739 --
740 -- Description:
741 -- This procedure is used to validate the result rule type against the
742 -- business rules.
743 -- ----------------------------------------------------------------------------
744 Procedure chk_result_rule_type
745 (p_effective_date in date
746 ,p_element_type_id in number
747 ,p_status_processing_rule_id in number
748 ,p_result_name in varchar2
749 ,p_result_rule_type in varchar2
750 ,p_severity_level in varchar2
751 ,p_input_value_id in number
752 ) is
753 --
754 l_proc varchar2(72) := g_package||'chk_result_rule_type';
755 l_spr_element_id pay_element_types_f.element_type_id%type;
756 --
757 Cursor c_element_dets(p_element_type_id number)
758 is
759 select pet.processing_type
760 ,pet.third_party_pay_only_flag
761 ,pet.processing_priority
762 ,pet.multiple_entries_allowed_flag
763 from pay_element_types_f pet
764 where pet.element_type_id = p_element_type_id
765 and p_effective_date between pet.effective_start_date
766 and pet.effective_end_date;
767 --
768 Cursor c_spr_element
769 is
770 select spr.element_type_id
771 from pay_status_processing_rules_f spr
772 where spr.status_processing_rule_id = p_status_processing_rule_id
773 and p_effective_date between spr.effective_start_date
774 and spr.effective_end_date;
775 --
776 l_element_dets c_element_dets%rowtype;
777 l_spr_element_dets c_element_dets%rowtype;
778 --
779
780 Begin
781 hr_utility.set_location('Entering:'||l_proc, 5);
782 --
783 If hr_api.not_exists_in_hr_lookups
784 (p_effective_date
785 ,'RESULT_RULE_TYPE'
786 ,p_result_rule_type) Then
787 --
788 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
789 fnd_message.set_token('COLUMN','RESULT_RULE_TYPE');
790 fnd_message.set_token('LOOKUP_TYPE','RESULT_RULE_TYPE');
791 fnd_message.raise_error;
792 --
793 End If;
794 --
795 If p_element_type_id is not null then
796 Open c_element_dets (p_element_type_id);
797 Fetch c_element_dets into l_element_dets;
798 Close c_element_dets;
799 End If;
800 --
801 Open c_spr_element;
802 Fetch c_spr_element into l_spr_element_id;
803 Close c_spr_element;
804 --
805 Open c_element_dets (l_spr_element_id);
806 Fetch c_element_dets into l_spr_element_dets;
807 Close c_element_dets;
808 --
809 chk_common_rules
810 (p_element_type_id
811 ,p_result_rule_type
812 ,p_severity_level
813 ,p_input_value_id);
814 --
815 If p_result_rule_type in ('I','O') then -- indirect or order indirect
816 --
817 -- the priority of the non-recurring element providing the input
818 -- value must be same as or lower (ie. same or higher number) than
819 -- that of the element for which this is the formula result rule
820 -- for the lifetime of the formula result rule.
821 --
822 If (l_spr_element_dets.processing_priority
823 > l_element_dets.processing_priority) then
824 --
825 fnd_message.set_name('PAY','PAY_34163_FRR_PRIORITY');
826 fnd_message.raise_error;
827 --
828 End If;
829 --
830 -- the element must be non-recurring and non-third party.
831 --
832 if nvl(l_element_dets.third_party_pay_only_flag,'N') <> 'N' then
833 --
834 fnd_message.set_name('PAY','PAY_34160_FRR_INDIRECT_RULE');
835 fnd_message.raise_error;
836 --
837 End If;
838 If (l_element_dets.processing_type <> 'N') then
839 --
840 IF hr_startup_data_api_support.g_startup_mode NOT IN ('STARTUP') THEN
841 fnd_message.set_name('PAY','PAY_34160_FRR_INDIRECT_RULE');
842 fnd_message.raise_error;
843 ELSE
844 hr_utility.trace('PAY_34160_FRR_INDIRECT_RULE Warning in startup mode: p_element_type_id:'
845 ||p_element_type_id|| ' has processing_type:'
846 ||l_element_dets.processing_type);
847 --
848 End if;
849 End If;
850 --
851 Elsif p_result_rule_type in ('U','S') then -- update recurring or stop entry
852 --
853 -- the element must be recurring
854 --
855 If l_element_dets.processing_type <> 'R' then
856 --
857 fnd_message.set_name('PAY','PAY_34161_FRR_RECURRING');
858 fnd_message.raise_error;
859 --
860 End If;
861 --
862 -- if the element allows multiple entries then it
863 -- can only be the target of such a rule if it is also the source.
864 --
865 If (l_element_dets.multiple_entries_allowed_flag = 'Y'
866 and l_spr_element_id <> p_element_type_id) then
867 --
868 fnd_message.set_name('PAY','PAY_34164_FRR_NO_MULTI_ENTRIES');
869 fnd_message.raise_error;
870 --
871 End If;
872 --
873 Elsif p_result_rule_type = 'M' then -- message
874 --
875 -- severity must be entered
876 --
877 If p_severity_level is null then
878 --
879 fnd_message.set_name('PAY','PAY_34167_FRR_SEVERITY_REQD');
880 fnd_message.raise_error;
881 --
882 End If;
883 --
884 -- no element or input value can be entered
885 --
886 If (p_element_type_id is not null
887 or p_input_value_id is not null) then
888 --
889 fnd_message.set_name('PAY','PAY_34157_FRR_MSG_NO_ELEMENT');
890 fnd_message.raise_error;
891 --
892 End If;
893 --
894 End If;
895 --
896 hr_utility.set_location('Leaving:'||l_proc, 10);
897 End;
898 --
899 -- ----------------------------------------------------------------------------
900 -- |-------------------------< chk_severity_level >---------------------------|
901 -- ----------------------------------------------------------------------------
902 --
903 -- Description:
904 -- This procedure is used to validate the severity level against the
905 -- lookup 'FORMULA_RESULT_MESSAGE_LEVEL'.
906 --
907 -- ----------------------------------------------------------------------------
908 Procedure chk_severity_level
909 (p_effective_date in date
910 ,p_severity_level in varchar2
911 ) is
912 --
913 l_proc varchar2(72) := g_package||'chk_severity_level';
914 --
915 Begin
916 hr_utility.set_location('Entering:'||l_proc, 5);
917 --
918 If hr_api.not_exists_in_hr_lookups
919 (p_effective_date
920 ,'FORMULA_RESULT_MESSAGE_LEVEL'
921 ,p_severity_level) Then
922 --
923 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
924 fnd_message.set_token('COLUMN','SEVERITY_LEVEL');
925 fnd_message.set_token('LOOKUP_TYPE','FORMULA_RESULT_MESSAGE_LEVEL');
926 fnd_message.raise_error;
927 --
928 End If;
929 --
930 hr_utility.set_location('Leaving:'||l_proc, 10);
931 End;
932 --
933 -- ----------------------------------------------------------------------------
934 -- |-------------------------< chk_input_value_id >---------------------------|
935 -- ----------------------------------------------------------------------------
936 --
937 -- Description:
938 -- This procedure is used to check whether the UOM of the input value matches
939 -- the datatype of the formula result.
940 -- ----------------------------------------------------------------------------
941 Procedure chk_input_value_id
942 (p_effective_date in date
943 ,p_element_type_id in number
944 ,p_status_processing_rule_id in number
945 ,p_result_name in varchar2
946 ,p_result_rule_type in varchar2
947 ,p_input_value_id in number
948 ) is
949 --
950 l_proc varchar2(72) := g_package||'chk_input_value_id';
951 l_uom pay_input_values_f.uom%type;
952 l_op_datatype ff_fdi_usages_f.data_type%type;
953 --
954 Cursor c_chk_input_value is
955 select uom
956 from pay_input_values_f piv
957 where piv.input_value_id = p_input_value_id
958 and piv.element_type_id = p_element_type_id
959 and p_effective_date between piv.effective_start_date
960 and piv.effective_end_date;
961 --
962 Cursor c_result_dtype is
963 select fdu.data_type
964 from ff_fdi_usages_f fdu
965 ,pay_status_processing_rules_f spr
966 where spr.status_processing_rule_id = p_status_processing_rule_id
967 and fdu.formula_id = spr.formula_id
968 and fdu.usage in ('O', 'B')
969 and fdu.item_name = p_result_name
970 and p_effective_date between spr.effective_start_date
971 and spr.effective_end_date
972 and p_effective_date between fdu.effective_start_date
973 and fdu.effective_end_date;
974 --
975 Begin
976 hr_utility.set_location('Entering:'||l_proc, 5);
977 --
978 Open c_result_dtype;
979 Fetch c_result_dtype into l_op_datatype;
980 Close c_result_dtype;
981 --
982 If p_input_value_id is not null then
983 --
984 Open c_chk_input_value;
985 Fetch c_chk_input_value into l_uom;
986 If c_chk_input_value%notfound then
987 Close c_chk_input_value;
988 fnd_message.set_name('PAY','PAY_33085_INVALID_FK');
989 fnd_message.set_token('COLUMN','INPUT_VALUE_ID');
990 fnd_message.set_token('TABLE','PAY_INPUT_VALUES_F');
991 fnd_message.raise_error;
992 End If;
993 Close c_chk_input_value;
994 --
995 If l_op_datatype = 'D' then -- date
996 If substr(l_uom,1,1) <> 'D' then
997 fnd_message.set_name('PAY','PAY_34162_FRR_INVALID_UOM');
998 fnd_message.raise_error;
999 End If;
1000 --
1001 Elsif l_op_datatype = 'T' then -- text
1002 If l_uom not in ('C','T') then
1003 fnd_message.set_name('PAY','PAY_34162_FRR_INVALID_UOM');
1004 fnd_message.raise_error;
1005 End If;
1006 --
1007 Elsif l_op_datatype = 'N' then -- numeric
1008 If (substr(l_uom,1,1) not in ('H','I','M','N')) then
1009 fnd_message.set_name('PAY','PAY_34162_FRR_INVALID_UOM');
1010 fnd_message.raise_error;
1011 End If;
1012 --
1013 End If;
1014 --
1015 End If;
1016 --
1017 If (p_result_rule_type = 'O' and l_op_datatype <> 'N') then
1018 --
1019 fnd_message.set_name('PAY','PAY_34170_FRR_DATATYP_MISMATCH');
1020 fnd_message.set_token('RULE','Order Indirect');
1021 fnd_message.set_token('TYPE','Numeric');
1022 fnd_message.raise_error;
1023 --
1024 Elsif (p_result_rule_type = 'M' and l_op_datatype <> 'T') then
1025 --
1026 fnd_message.set_name('PAY','PAY_34170_FRR_DATATYP_MISMATCH');
1027 fnd_message.set_token('RULE','Message');
1028 fnd_message.set_token('TYPE','Text');
1029 fnd_message.raise_error;
1030 --
1031 End If;
1032 --
1033 hr_utility.set_location('Leaving:'||l_proc, 10);
1034 End;
1035 --
1036 -- ----------------------------------------------------------------------------
1037 -- |-------------------------< chk_unique_rules >-----------------------------|
1038 -- ----------------------------------------------------------------------------
1039 --
1040 -- Description:
1041 -- This procedure is used to check whether the formula result rule being
1042 -- created is a duplicate rule.
1043 -- ----------------------------------------------------------------------------
1044 Procedure chk_unique_rules
1045 (p_effective_date in date
1046 ,p_status_processing_rule_id in number
1047 ,p_result_rule_type in varchar2
1048 ,p_result_name in varchar2
1049 ,p_element_type_id in number
1050 ,p_input_value_id in number
1051 ,p_formula_result_rule_id in number default null
1052 ) is
1053 --
1054 l_proc varchar2(72) := g_package||'chk_unique_rules';
1055 --
1056 function message_rule_not_unique
1057 --
1058 -- Returns TRUE if the tested message rule already exists
1059 -- Only one message rule is allowed for each SPR/result name combination
1060 return boolean is
1061 --
1062 v_duplicate_found boolean := FALSE;
1063 --
1064 cursor c_duplicate_rule
1065 is
1066 select '1'
1067 from pay_formula_result_rules_f
1068 where status_processing_rule_id = p_status_processing_rule_id
1069 and result_rule_type = 'M'
1070 and result_name = p_result_name
1071 and p_effective_date between effective_start_date
1072 and effective_end_date
1073 and formula_result_rule_id <> nvl(p_formula_result_rule_id,-1);
1074 --
1075 begin
1076 --
1077 hr_utility.set_location('Entering:'||l_proc, 2);
1078 --
1079 open c_duplicate_rule;
1080 fetch c_duplicate_rule into g_exists;
1081 v_duplicate_found := c_duplicate_rule%found;
1082 close c_duplicate_rule;
1083 --
1084 hr_utility.set_location('Leaving:'||l_proc, 3);
1085 --
1086 return v_duplicate_found;
1087 --
1088 end message_rule_not_unique;
1089 --
1090 function recurring_rule_not_unique
1091 -- Returns TRUE if the tested stop-entry/update-recurring rule already
1092 -- exists.
1093 -- Only one stop-entry/update-recurring rule is allowed for each
1094 -- combination of result name, SPR and element type
1095 return boolean is
1096 --
1097 v_duplicate_found boolean := FALSE;
1098 --
1099 cursor c_duplicate_rule
1100 is
1101 select '1'
1102 from pay_formula_result_rules_f
1103 where status_processing_rule_id = p_status_processing_rule_id
1104 and result_rule_type in ('S','U')
1105 and result_name = p_result_name
1106 and element_type_id = p_element_type_id
1107 and p_effective_date between effective_start_date
1108 and effective_end_date
1109 and formula_result_rule_id <> nvl(p_formula_result_rule_id,-1);
1110 --
1111 begin
1112 --
1113 hr_utility.set_location('Entering:'||l_proc, 4);
1114 --
1115 open c_duplicate_rule;
1116 fetch c_duplicate_rule into g_exists;
1117 v_duplicate_found := c_duplicate_rule%found;
1118 close c_duplicate_rule;
1119 --
1120 hr_utility.set_location('Leaving:'||l_proc, 5);
1121 --
1122 return v_duplicate_found;
1123 --
1124 end recurring_rule_not_unique;
1125 --
1126 function other_rule_type_not_unique
1127 -- Returns TRUE if any duplicate rule/rule-type/input-value is found
1128 -- Only one indirect or direct is allowed for each
1129 -- combination of SPR, result name and input value
1130 return boolean is
1131 --
1132 v_duplicate_found boolean := FALSE;
1133 --
1134 cursor c_duplicate_rule
1135 is
1136 select '1'
1137 from pay_formula_result_rules_f
1138 where status_processing_rule_id = p_status_processing_rule_id
1139 and result_rule_type = p_result_rule_type
1140 and result_name = p_result_name
1141 and input_value_id = p_input_value_id
1142 and p_effective_date between effective_start_date
1143 and effective_end_date
1144 and formula_result_rule_id <> nvl(p_formula_result_rule_id,-1);
1145 --
1146 begin
1147 --
1148 hr_utility.set_location('Entering:'||l_proc, 6);
1149 --
1150 open c_duplicate_rule;
1151 fetch c_duplicate_rule into g_exists;
1152 v_duplicate_found := c_duplicate_rule%found;
1153 close c_duplicate_rule;
1154 --
1155 hr_utility.set_location('Leaving:'||l_proc, 7);
1156 --
1157 return v_duplicate_found;
1158 --
1159 end other_rule_type_not_unique;
1160 --
1161 -- Main procedure starts
1162 --
1163 Begin
1164 hr_utility.set_location('Entering:'||l_proc, 1);
1165 --
1166 If (p_result_rule_type = 'M' and message_rule_not_unique)
1167 or (p_result_rule_type in ('S','U') and recurring_rule_not_unique)
1168 or (p_result_rule_type in ('I','D') and other_rule_type_not_unique) then
1169 --
1170 fnd_message.set_name('PAY','HR_6478_FF_UNI_FRR');
1171 fnd_message.raise_error;
1172 --
1173 End If;
1174 --
1175 hr_utility.set_location('Leaving:'||l_proc, 10);
1176 End;
1177 --
1178 -- ----------------------------------------------------------------------------
1179 -- |------------------------< set_effective_end_date >------------------------|
1180 -- ----------------------------------------------------------------------------
1181 --
1182 -- Description:
1183 -- This procedure is used to set the effective end date of the formula result
1184 -- based on the result rule types.
1185 -- ----------------------------------------------------------------------------
1186 Procedure set_effective_end_date
1187 (p_effective_date in date
1188 ,p_result_rule_type in varchar2
1189 ,p_result_name in varchar2
1190 ,p_status_processing_rule_id in number
1191 ,p_element_type_id in number
1192 ,p_input_value_id in number
1193 ,p_datetrack_mode in varchar2 default null
1194 ,p_formula_result_rule_id in number default null
1195 ,p_validation_end_date in out nocopy date
1196 ) is
1197 --
1198 l_proc varchar2(72) := g_package||'set_eff_end_date';
1199 l_future_rule_end_date date;
1200 l_max_spr_end_date date;
1201 l_max_end_date_of_element date;
1202 l_max_end_date_of_target date;
1203 l_spr_formula_id pay_status_processing_rules_f.formula_id%type;
1204 l_spr_element_type_id pay_status_processing_rules_f.element_type_id%type;
1205 --
1206 Cursor c_spr_element
1207 is
1208 select spr.formula_id,spr.element_type_id
1209 from pay_status_processing_rules_f spr
1210 where spr.status_processing_rule_id = p_status_processing_rule_id
1211 and p_effective_date between spr.effective_start_date
1212 and spr.effective_end_date;
1213 --
1214 Begin
1215 hr_utility.set_location('Entering:'||l_proc, 5);
1216 --
1217 open c_spr_element;
1218 fetch c_spr_element into l_spr_formula_id, l_spr_element_type_id;
1219 close c_spr_element;
1220 --
1221 -- get the maximum end date of the spr
1222 --
1223 l_max_spr_end_date := pay_status_rules_pkg.spr_end_date
1224 (p_status_processing_rule_id
1225 ,l_spr_formula_id);
1226 hr_utility.set_location('l_max_spr_end_date '||to_char(l_max_spr_end_date),6);
1227 --
1228 -- get the maximum end date of the source element type
1229 --
1230 l_max_end_date_of_element := pay_element_types_pkg.element_end_date
1231 (l_spr_element_type_id);
1232 hr_utility.set_location('l_max_end_date_of_element '||to_char(l_max_end_date_of_element),7);
1233 --
1234 -- get the maximum end date of the target element type
1235 --
1236 l_max_end_date_of_target := pay_element_types_pkg.element_end_date
1237 (p_element_type_id);
1238 hr_utility.set_location('l_max_end_date_of_target '||to_char(l_max_end_date_of_target),8);
1239 --
1240 -- get the maximum end date of a similar rule if it exists in the future.
1241 --
1242 l_future_rule_end_date := pay_formula_result_rules_pkg.result_rule_end_date
1243 (p_formula_result_rule_id
1244 ,p_result_rule_type
1245 ,p_result_name
1246 ,p_status_processing_rule_id
1247 ,p_element_type_id
1248 ,p_input_value_id
1249 ,p_effective_date
1250 ,l_max_spr_end_date);
1251 hr_utility.set_location('l_future_rule_end_date '||to_char(l_future_rule_end_date),8);
1252 --
1253 hr_utility.set_location('before set-p_validation_end_date '||to_char(p_validation_end_date),9);
1254 --
1255 if (p_result_rule_type in ('I','U','S'))
1256 or (p_datetrack_mode = hr_api.g_delete_next_change) then
1257 p_validation_end_date := least (l_max_end_date_of_element
1258 ,l_max_spr_end_date
1259 ,l_max_end_date_of_target
1260 ,l_future_rule_end_date);
1261 else
1262 p_validation_end_date := least (l_max_end_date_of_element
1263 ,l_max_spr_end_date
1264 ,l_future_rule_end_date);
1265 end if;
1266 --
1267 hr_utility.set_location('after set-p_validation_end_date '||to_char(p_validation_end_date),10);
1268 --
1269 hr_utility.set_location('Leaving:'||l_proc, 10);
1270 End;
1271 --
1272 -- ----------------------------------------------------------------------------
1273 -- |----------------------< chk_startup_action >------------------------------|
1274 -- ----------------------------------------------------------------------------
1275 --
1276 -- Description:
1277 -- This procedure will check that the current action is allowed according
1278 -- to the current startup mode.
1279 --
1280 -- ----------------------------------------------------------------------------
1281 PROCEDURE chk_startup_action
1282 (p_insert IN boolean
1283 ,p_business_group_id IN number
1284 ,p_legislation_code IN varchar2
1285 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
1286 --
1287 BEGIN
1288 --
1289 -- Call the supporting procedure to check startup mode
1290 --
1291 IF (p_insert) THEN
1292 hr_startup_data_api_support.chk_startup_action
1293 (p_generic_allowed => FALSE
1294 ,p_startup_allowed => TRUE
1295 ,p_user_allowed => TRUE
1296 ,p_business_group_id => p_business_group_id
1297 ,p_legislation_code => p_legislation_code
1298 ,p_legislation_subgroup => p_legislation_subgroup
1299 );
1300 ELSE
1301 hr_startup_data_api_support.chk_upd_del_startup_action
1302 (p_generic_allowed => FALSE
1303 ,p_startup_allowed => TRUE
1304 ,p_user_allowed => TRUE
1305 ,p_business_group_id => p_business_group_id
1306 ,p_legislation_code => p_legislation_code
1307 ,p_legislation_subgroup => p_legislation_subgroup
1308 );
1309 END IF;
1310 --
1311 END chk_startup_action;
1312 --
1313 -- ----------------------------------------------------------------------------
1314 -- |---------------------------< insert_validate >----------------------------|
1315 -- ----------------------------------------------------------------------------
1316 Procedure insert_validate
1317 (p_rec in pay_frr_shd.g_rec_type
1318 ,p_effective_date in date
1319 ,p_datetrack_mode in varchar2
1320 ,p_validation_start_date in date
1321 ,p_validation_end_date in date
1322 ) is
1323 --
1324 l_proc varchar2(72) := g_package||'insert_validate';
1325 --
1326 Begin
1327 hr_utility.set_location('Entering:'||l_proc, 5);
1328 --
1329 -- Call all supporting business operations
1330 --
1331 --
1332 chk_startup_action(true
1333 ,p_rec.business_group_id
1334 ,p_rec.legislation_code
1335 );
1336 IF hr_startup_data_api_support.g_startup_mode
1337 NOT IN ('GENERIC','STARTUP') THEN
1338 --
1339 -- Validate Important Attributes
1340 --
1341 hr_api.validate_bus_grp_id
1342 (p_business_group_id => p_rec.business_group_id
1343 ,p_associated_column1 => pay_frr_shd.g_tab_nam
1344 || '.BUSINESS_GROUP_ID');
1345 --
1346 -- after validating the set of important attributes,
1347 -- if Multiple Message Detection is enabled and at least
1348 -- one error has been found then abort further validation.
1349 --
1350 hr_multi_message.end_validation_set;
1351 END IF;
1352 --
1353 --
1354 -- Validate Dependent Attributes
1355 --
1356 --
1357 If p_rec.legislation_code is not null then
1358 chk_legislation_code
1359 (p_legislation_code => p_rec.legislation_code);
1360 End if;
1361 --
1362 If p_rec.element_type_id is not null then
1363 chk_element_type_id
1364 (p_effective_date => p_effective_date
1365 ,p_element_type_id => p_rec.element_type_id
1366 ,p_business_group_id => p_rec.business_group_id
1367 ,p_legislation_code => p_rec.legislation_code
1368 );
1369 End if;
1370 --
1371 chk_status_processing_rule_id
1372 (p_effective_date => p_effective_date
1373 ,p_status_processing_rule_id => p_rec.status_processing_rule_id
1374 ,p_business_group_id => p_rec.business_group_id
1375 ,p_legislation_code => p_rec.legislation_code
1376 );
1377 --
1378 chk_result_name
1379 (p_effective_date => p_effective_date
1380 ,p_status_processing_rule_id => p_rec.status_processing_rule_id
1381 ,p_result_name => p_rec.result_name
1382 );
1383 --
1384 chk_result_rule_type
1385 (p_effective_date => p_effective_date
1386 ,p_element_type_id => p_rec.element_type_id
1387 ,p_status_processing_rule_id => p_rec.status_processing_rule_id
1388 ,p_result_name => p_rec.result_name
1389 ,p_result_rule_type => p_rec.result_rule_type
1390 ,p_severity_level => p_rec.severity_level
1391 ,p_input_value_id => p_rec.input_value_id
1392 );
1393 --
1394 If p_rec.severity_level is not null then
1395 chk_severity_level
1396 (p_effective_date => p_effective_date
1397 ,p_severity_level => p_rec.severity_level
1398 );
1399 End if;
1400 --
1401 chk_input_value_id
1402 (p_effective_date => p_effective_date
1403 ,p_element_type_id => p_rec.element_type_id
1404 ,p_status_processing_rule_id => p_rec.status_processing_rule_id
1405 ,p_result_name => p_rec.result_name
1406 ,p_result_rule_type => p_rec.result_rule_type
1407 ,p_input_value_id => p_rec.input_value_id
1408 );
1409 --
1410 chk_unique_rules
1411 (p_effective_date => p_effective_date
1412 ,p_element_type_id => p_rec.element_type_id
1413 ,p_status_processing_rule_id => p_rec.status_processing_rule_id
1414 ,p_result_name => p_rec.result_name
1415 ,p_result_rule_type => p_rec.result_rule_type
1416 ,p_input_value_id => p_rec.input_value_id
1417 );
1418 --
1419 --
1420 hr_utility.set_location(' Leaving:'||l_proc, 10);
1421 End insert_validate;
1422 --
1423 -- ----------------------------------------------------------------------------
1424 -- |---------------------------< update_validate >----------------------------|
1425 -- ----------------------------------------------------------------------------
1426 Procedure update_validate
1427 (p_rec in pay_frr_shd.g_rec_type
1428 ,p_effective_date in date
1429 ,p_datetrack_mode in varchar2
1430 ,p_validation_start_date in date
1431 ,p_validation_end_date in date
1432 ) is
1433 --
1434 l_proc varchar2(72) := g_package||'update_validate';
1435 --
1436 Begin
1437 hr_utility.set_location('Entering:'||l_proc, 5);
1438 --
1439 -- Call all supporting business operations
1440 --
1441 --
1442 chk_startup_action(false
1443 ,p_rec.business_group_id
1444 ,p_rec.legislation_code
1445 );
1446 IF hr_startup_data_api_support.g_startup_mode
1447 NOT IN ('GENERIC','STARTUP') THEN
1448 --
1449 -- Validate Important Attributes
1450 --
1451 hr_api.validate_bus_grp_id
1452 (p_business_group_id => p_rec.business_group_id
1453 ,p_associated_column1 => pay_frr_shd.g_tab_nam
1454 || '.BUSINESS_GROUP_ID');
1455 --
1456 -- After validating the set of important attributes,
1457 -- if Multiple Message Detection is enabled and at least
1458 -- one error has been found then abort further validation.
1459 --
1460 hr_multi_message.end_validation_set;
1461 END IF;
1462 --
1463 -- Validate Dependent Attributes
1464 --
1465 If p_rec.element_type_id is not null then
1466 chk_element_type_id
1467 (p_effective_date => p_effective_date
1468 ,p_element_type_id => p_rec.element_type_id
1469 ,p_business_group_id => p_rec.business_group_id
1470 ,p_legislation_code => p_rec.legislation_code
1471 );
1472 End if;
1473 --
1474 chk_result_rule_type
1475 (p_effective_date => p_effective_date
1476 ,p_element_type_id => p_rec.element_type_id
1477 ,p_status_processing_rule_id => p_rec.status_processing_rule_id
1478 ,p_result_name => p_rec.result_name
1479 ,p_result_rule_type => p_rec.result_rule_type
1480 ,p_severity_level => p_rec.severity_level
1481 ,p_input_value_id => p_rec.input_value_id
1482 );
1483 --
1484 If p_rec.severity_level is not null then
1485 chk_severity_level
1486 (p_effective_date => p_effective_date
1487 ,p_severity_level => p_rec.severity_level
1488 );
1489 End if;
1490 --
1491 chk_input_value_id
1492 (p_effective_date => p_effective_date
1493 ,p_element_type_id => p_rec.element_type_id
1494 ,p_status_processing_rule_id => p_rec.status_processing_rule_id
1495 ,p_result_name => p_rec.result_name
1496 ,p_result_rule_type => p_rec.result_rule_type
1497 ,p_input_value_id => p_rec.input_value_id
1498 );
1499 --
1500 chk_unique_rules
1501 (p_effective_date => p_effective_date
1502 ,p_element_type_id => p_rec.element_type_id
1503 ,p_status_processing_rule_id => p_rec.status_processing_rule_id
1504 ,p_result_name => p_rec.result_name
1505 ,p_result_rule_type => p_rec.result_rule_type
1506 ,p_input_value_id => p_rec.input_value_id
1507 ,p_formula_result_rule_id => p_rec.formula_result_rule_id
1508 );
1509 --
1510 -- Call the datetrack update integrity operation
1511 --
1512 dt_update_validate
1513 (p_datetrack_mode => p_datetrack_mode
1514 ,p_validation_start_date => p_validation_start_date
1515 ,p_validation_end_date => p_validation_end_date
1516 );
1517 --
1518 chk_non_updateable_args
1519 (p_effective_date => p_effective_date
1520 ,p_rec => p_rec
1521 );
1522 --
1523 --
1524 hr_utility.set_location(' Leaving:'||l_proc, 10);
1525 End update_validate;
1526 --
1527 -- ----------------------------------------------------------------------------
1528 -- |---------------------------< delete_validate >----------------------------|
1529 -- ----------------------------------------------------------------------------
1530 Procedure delete_validate
1531 (p_rec in pay_frr_shd.g_rec_type
1532 ,p_effective_date in date
1533 ,p_datetrack_mode in varchar2
1534 ,p_validation_start_date in date
1535 ,p_validation_end_date in date
1536 ) is
1537 --
1538 l_proc varchar2(72) := g_package||'delete_validate';
1539 --
1540 Begin
1541 hr_utility.set_location('Entering:'||l_proc, 5);
1542 --
1543 --
1544 chk_startup_action(false
1545 ,pay_frr_shd.g_old_rec.business_group_id
1546 ,pay_frr_shd.g_old_rec.legislation_code
1547 );
1548 IF hr_startup_data_api_support.g_startup_mode
1549 NOT IN ('GENERIC','STARTUP') THEN
1550 --
1551 -- Validate Important Attributes
1552 --
1553 --
1554 -- After validating the set of important attributes,
1555 -- if Multiple Message Detection is enabled and at least
1556 -- one error has been found then abort further validation.
1557 --
1558 hr_multi_message.end_validation_set;
1559 END IF;
1560 --
1561 -- Call all supporting business operations
1562 --
1563 dt_delete_validate
1564 (p_datetrack_mode => p_datetrack_mode
1565 ,p_validation_start_date => p_validation_start_date
1566 ,p_validation_end_date => p_validation_end_date
1567 ,p_formula_result_rule_id => p_rec.formula_result_rule_id
1568 );
1569 --
1570 hr_utility.set_location(' Leaving:'||l_proc, 10);
1571 End delete_validate;
1572 --
1573 end pay_frr_bus;
[Home] [Help]