1 Package Body pay_ivl_bus as
2 /* $Header: pyivlrhi.pkb 120.0.12010000.6 2009/07/30 12:03:21 npannamp ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ivl_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_input_value_id number default null;
15
16 cursor csr_row_count is
17 select count(*) from pay_input_values_f
18 where input_value_id = pay_ivl_shd.g_old_rec.input_value_id;
19 --
20 -- ---------------------------------------------------------------------------
21 -- |----------------------< set_security_group_id >--------------------------|
22 -- ---------------------------------------------------------------------------
23 --
24 Procedure set_security_group_id
25 (p_input_value_id in number
26 ,p_associated_column1 in varchar2 default null
27 ) is
28
29 --
30 -- Declare cursor
31 --
32 cursor csr_sec_grp is
33 select pbg.security_group_id
34 from per_business_groups pbg
35 , pay_input_values_f ivl
36 where ivl.input_value_id = p_input_value_id
37 and pbg.business_group_id = ivl.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 --
44 begin
45 --
46 hr_utility.set_location('Entering:'|| l_proc, 10);
47 --
48 -- Ensure that all the mandatory parameter are not null
49 --
50 hr_api.mandatory_arg_error
51 (p_api_name => l_proc
52
53 ,p_argument => 'input_value_id'
54 ,p_argument_value => p_input_value_id
55 );
56 --
57 open csr_sec_grp;
58 fetch csr_sec_grp into l_security_group_id;
59 --
60 if csr_sec_grp%notfound then
61 --
62 close csr_sec_grp;
63 --
64 -- The primary key is invalid therefore we must error
65 --
66 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
67 hr_multi_message.add
68 (p_associated_column1
69 => nvl(p_associated_column1,'INPUT_VALUE_ID')
70 );
71 --
72 else
73 close csr_sec_grp;
74 --
75 -- Set the security_group_id in CLIENT_INFO
76
77 --
78 hr_api.set_security_group_id
79 (p_security_group_id => l_security_group_id
80 );
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_input_value_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100
101 from per_business_groups pbg
102 , pay_input_values_f ivl
103 where ivl.input_value_id = p_input_value_id
104 and pbg.business_group_id (+) = ivl.business_group_id;
105 --
106 -- Declare local variables
107 --
108 l_legislation_code varchar2(150);
109 l_proc varchar2(72) := g_package||'return_legislation_code';
110 --
111 Begin
112 --
113 hr_utility.set_location('Entering:'|| l_proc, 10);
114 --
115 -- Ensure that all the mandatory parameter are not null
116 --
117 hr_api.mandatory_arg_error
118 (p_api_name => l_proc
119 ,p_argument => 'input_value_id'
120 ,p_argument_value => p_input_value_id
121 );
122 --
123 if ( nvl(pay_ivl_bus.g_input_value_id, hr_api.g_number)
124
125 = p_input_value_id) then
126 --
127 -- The legislation code has already been found with a previous
128 -- call to this function. Just return the value in the global
129 -- variable.
130 --
131 l_legislation_code := pay_ivl_bus.g_legislation_code;
132 hr_utility.set_location(l_proc, 20);
133 else
134 --
135 -- The ID is different to the last call to this function
136 -- or this is the first call to this function.
137 --
138 open csr_leg_code;
139 fetch csr_leg_code into l_legislation_code;
140 --
141 if csr_leg_code%notfound then
142 --
143 -- The primary key is invalid therefore we must error
144 --
145 close csr_leg_code;
146 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
147 fnd_message.raise_error;
148
149 end if;
150 hr_utility.set_location(l_proc,30);
151 --
152 -- Set the global variables so the values are
153 -- available for the next call to this function.
154 --
155 close csr_leg_code;
156 pay_ivl_bus.g_input_value_id := p_input_value_id;
157 pay_ivl_bus.g_legislation_code := l_legislation_code;
158 end if;
159 hr_utility.set_location(' Leaving:'|| l_proc, 40);
160 return l_legislation_code;
161 end return_legislation_code;
162 --
163 -- ----------------------------------------------------------------------------
164 -- |-----------------------< chk_default_value_format >-----------------------|
165 -- ----------------------------------------------------------------------------
166 PROCEDURE chk_default_value_format
167 ( p_default_value in varchar2
168 ,p_lookup_type in varchar2
169 ,p_min_value in varchar2
170 ,p_max_value in varchar2
171 ,p_uom in varchar2
172 ,p_input_currency_code in varchar2
173 ,p_warning_or_error in varchar2
174 ,p_default_val_warning out nocopy boolean
175 )
176 IS
177 --
178 l_proc varchar2(72) := g_package||'chk_default_value_format';
179 l_default_value varchar2(100) ;
180 l_range_check varchar2(10) ;
181 --
182 BEGIN
183 --
184 hr_utility.set_location(' Entering:'|| l_proc, 10);
185 --
186 if p_default_value is not null and p_lookup_type is null then
187 --
188 hr_utility.set_location(l_proc, 20);
189 --
190
191 l_default_value := p_default_value ;
192 --
193 hr_chkfmt.checkformat
194 (l_default_value,
195 p_uom,
196 l_default_value,
197 p_min_value,
198 p_max_value,
199 'Y',
200 l_range_check,
201 p_input_currency_code
202 );
203
204 if l_range_check = 'F' then
205 --
206 if p_warning_or_error = 'E' then
207 --
208 hr_utility.set_location(l_proc,30);
209 --
210 fnd_message.set_name('PAY', 'HR_INPVAL_DEFAULT_INVALID');
211 fnd_message.raise_error;
212 --
213 elsif p_warning_or_error = 'W' then
214 --
215 hr_utility.set_location(l_proc,40);
216 --
217 p_default_val_warning := TRUE ;
218 --
219 end if;
220 --
221 end if;
222 --
223 end if ;
224
225 hr_utility.set_location(' Leaving:'|| l_proc, 50);
226
227 END chk_default_value_format;
228 --
229
230 --
231 -- ----------------------------------------------------------------------------
232 -- |-----------------------< chk_non_updateable_args >------------------------|
233 -- ----------------------------------------------------------------------------
234 -- {Start Of Comments}
235 --
236 -- Description:
237 -- This procedure is used to ensure that non updateable attributes have
238 -- not been updated. If an attribute has been updated an error is generated.
239 --
240
241 -- Pre Conditions:
242 -- g_old_rec has been populated with details of the values currently in
243 -- the database.
244 --
245 -- In Arguments:
246 -- p_rec has been populated with the updated values the user would like the
247 -- record set to.
248 --
249 -- Post Success:
250 -- Processing continues if all the non updateable attributes have not
251 -- changed.
252 --
253 -- Post Failure:
254 -- An application error is raised if any of the non updatable attributes
255 -- have been altered.
256 --
257 -- {End Of Comments}
258 -- ----------------------------------------------------------------------------
259 Procedure chk_non_updateable_args
260 (p_effective_date in date
261 ,p_rec in pay_ivl_shd.g_rec_type
262 ) IS
263 --
264
265 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
266 --
267 Begin
268 --
269 -- Only proceed with the validation if a row exists for the current
270 -- record in the HR Schema.
271 --
272 IF NOT pay_ivl_shd.api_updating
273 (p_input_value_id => p_rec.input_value_id
274 ,p_effective_date => p_effective_date
275 ,p_object_version_number => p_rec.object_version_number
276 ) THEN
277 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
278 fnd_message.set_token('PROCEDURE ', l_proc);
279 fnd_message.set_token('STEP ', '5');
280 fnd_message.raise_error;
281 END IF;
282
283 End chk_non_updateable_args;
284 --
285
286 -- ----------------------------------------------------------------------------
287 -- |--------------------------< dt_update_validate >--------------------------|
288 -- ----------------------------------------------------------------------------
289 -- {Start Of Comments}
290 --
291 -- Description:
292 -- This procedure is used for referential integrity of datetracked
293 -- parent entities when a datetrack update operation is taking place
294 -- and where there is no cascading of update defined for this entity.
295 --
296 -- Prerequisites:
297 -- This procedure is called from the update_validate.
298 --
299 -- In Parameters:
300 --
301 -- Post Success:
302 -- Processing continues.
303 --
304 -- Post Failure:
305 --
306 -- Developer Implementation Notes:
307 -- This procedure should not need maintenance unless the HR Schema model
308 -- changes.
309
310 --
311 -- Access Status:
312 -- Internal Row Handler Use Only.
313 --
314 -- {End Of Comments}
315 -- ----------------------------------------------------------------------------
316 Procedure dt_update_validate
317 (p_element_type_id in number default hr_api.g_number
318 ,p_datetrack_mode in varchar2
319 ,p_validation_start_date in date
320 ,p_validation_end_date in date
321 ) Is
322 --
323 l_proc varchar2(72) := g_package||'dt_update_validate';
324 --
325 Begin
326 --
327 -- Ensure that the p_datetrack_mode argument is not null
328 --
329 hr_api.mandatory_arg_error
330 (p_api_name => l_proc
331 ,p_argument => 'datetrack_mode'
332 ,p_argument_value => p_datetrack_mode
333
334 );
335 --
336 -- Mode will be valid, as this is checked at the start of the upd.
337 --
338 -- Ensure the arguments are not null
339 --
340 hr_api.mandatory_arg_error
341 (p_api_name => l_proc
342 ,p_argument => 'validation_start_date'
343 ,p_argument_value => p_validation_start_date
344 );
345 --
346 hr_api.mandatory_arg_error
347 (p_api_name => l_proc
348 ,p_argument => 'validation_end_date'
349 ,p_argument_value => p_validation_end_date
350 );
351 --
352 If ((nvl(p_element_type_id, hr_api.g_number) <> hr_api.g_number) and
353 NOT (dt_api.check_min_max_dates
354 (p_base_table_name => 'pay_element_types_f'
355 ,p_base_key_column => 'ELEMENT_TYPE_ID'
356 ,p_base_key_value => p_element_type_id
357
358 ,p_from_date => p_validation_start_date
359 ,p_to_date => p_validation_end_date))) Then
360 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
361 fnd_message.set_token('TABLE_NAME','element types');
362 hr_multi_message.add
363 (p_associated_column1 => pay_ivl_shd.g_tab_nam || '.ELEMENT_TYPE_ID');
364 End If;
365 --
366 Exception
367 When Others Then
368 --
369 -- An unhandled or unexpected error has occurred which
370 -- we must report
371 --
372 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
373 fnd_message.set_token('PROCEDURE', l_proc);
374 fnd_message.set_token('STEP','15');
375 fnd_message.raise_error;
376 End dt_update_validate;
377 --
378 -- ----------------------------------------------------------------------------
379 -- |--------------------------< dt_delete_validate >--------------------------|
380 -- ----------------------------------------------------------------------------
381
382 -- {Start Of Comments}
383 --
384 -- Description:
385 -- This procedure is used for referential integrity of datetracked
386 -- child entities when either a datetrack DELETE or ZAP is in operation
387 -- and where there is no cascading of delete defined for this entity.
388 -- For the datetrack mode of DELETE or ZAP we must ensure that no
389 -- datetracked child rows exist between the validation start and end
390 -- dates.
391 --
392 -- Prerequisites:
393 -- This procedure is called from the delete_validate.
394 --
395 -- In Parameters:
396 --
397 -- Post Success:
398 -- Processing continues.
399 --
400 -- Post Failure:
401 -- If a row exists by determining the returning Boolean value from the
402 -- generic dt_api.rows_exist function then we must supply an error via
403 -- the use of the local exception handler l_rows_exist.
404 --
405
406 -- Developer Implementation Notes:
407 -- This procedure should not need maintenance unless the HR Schema model
408 -- changes.
409 --
410 -- Access Status:
411 -- Internal Row Handler Use Only.
412 --
413 -- {End Of Comments}
414 -- ----------------------------------------------------------------------------
415 Procedure dt_delete_validate
416 (p_input_value_id in number
417 ,p_datetrack_mode in varchar2
418 ,p_validation_start_date in date
419 ,p_validation_end_date in date
420 ) Is
421 --
422 l_proc varchar2(72) := g_package||'dt_delete_validate';
423 --
424 Begin
425 --
426 -- Ensure that the p_datetrack_mode argument is not null
427 --
428 hr_api.mandatory_arg_error
429
430 (p_api_name => l_proc
431 ,p_argument => 'datetrack_mode'
432 ,p_argument_value => p_datetrack_mode
433 );
434 --
435 -- Only perform the validation if the datetrack mode is either
436 -- DELETE or ZAP
437 --
438 If (p_datetrack_mode = hr_api.g_delete or
439 p_datetrack_mode = hr_api.g_zap) then
440 --
441 --
442 -- Ensure the arguments are not null
443 --
444 hr_api.mandatory_arg_error
445 (p_api_name => l_proc
446 ,p_argument => 'validation_start_date'
447 ,p_argument_value => p_validation_start_date
448 );
449 --
450 hr_api.mandatory_arg_error
451 (p_api_name => l_proc
452 ,p_argument => 'validation_end_date'
453
454 ,p_argument_value => p_validation_end_date
455 );
456 --
457 hr_api.mandatory_arg_error
458 (p_api_name => l_proc
459 ,p_argument => 'input_value_id'
460 ,p_argument_value => p_input_value_id
461 );
462 --
463 If (dt_api.rows_exist
464 (p_base_table_name => 'ben_acty_base_rt_f'
465 ,p_base_key_column => 'input_value_id'
466 ,p_base_key_value => p_input_value_id
467 ,p_from_date => p_validation_start_date
468 ,p_to_date => p_validation_end_date
469 )) Then
470 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
471 fnd_message.set_token('TABLE_NAME','acty base rt');
472 hr_multi_message.add;
473 End If;
474 If (dt_api.rows_exist
475 (p_base_table_name => 'pay_link_input_values_f'
476 ,p_base_key_column => 'input_value_id'
477
478 ,p_base_key_value => p_input_value_id
479 ,p_from_date => p_validation_start_date
480 ,p_to_date => p_validation_end_date
481 )) Then
482 fnd_message.set_name('PAY','HR_7215_DT_CHILD_EXISTS');
483 fnd_message.set_token('TABLE_NAME','link input values');
484 hr_multi_message.add;
485 End If;
486 --
487 End If;
488 --
489 Exception
490 When Others Then
491 --
492 -- An unhandled or unexpected error has occurred which
493 -- we must report
494 --
495 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
496 fnd_message.set_token('PROCEDURE', l_proc);
497 fnd_message.set_token('STEP','15');
498 fnd_message.raise_error;
499 --
500 End dt_delete_validate;
501
502 --
503 -- ----------------------------------------------------------------------------
504 -- |----------------------< chk_startup_action >------------------------------|
505 -- ----------------------------------------------------------------------------
506 --
507 -- Description:
508 -- This procedure will check that the current action is allowed according
509 -- to the current startup mode.
510 --
511 -- ----------------------------------------------------------------------------
512 PROCEDURE chk_startup_action
513 (p_insert IN boolean
514 ,p_business_group_id IN number
515 ,p_legislation_code IN varchar2
516 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
517 --
518 BEGIN
519 --
520 -- Call the supporting procedure to check startup mode
521 IF (p_insert) THEN
522 hr_startup_data_api_support.chk_startup_action
523
524 (p_generic_allowed => TRUE
525 ,p_startup_allowed => TRUE
526 ,p_user_allowed => TRUE
527 ,p_business_group_id => p_business_group_id
528 ,p_legislation_code => p_legislation_code
529 ,p_legislation_subgroup => p_legislation_subgroup
530 );
531 ELSE
532 hr_startup_data_api_support.chk_upd_del_startup_action
533 (p_generic_allowed => TRUE
534 ,p_startup_allowed => TRUE
535 ,p_user_allowed => TRUE
536 ,p_business_group_id => p_business_group_id
537 ,p_legislation_code => p_legislation_code
538 ,p_legislation_subgroup => p_legislation_subgroup
539 );
540 END IF;
541 --
542 END chk_startup_action;
543
544 --
545 -- ----------------------------------------------------------------------------
546 -- |----------------------< chk_lookup_type >------------------------------|
547 -- ----------------------------------------------------------------------------
548 --
549 -- Description:
550 -- This procedure is used to ensure that LOOKUP_TYPE can be entered only
551 -- when Unit Of Measure is 'Character' and is the one present in HR_LOOKUPS,
552 -- enabled and valid as of current date.
553 --
554 -- ----------------------------------------------------------------------------
555 PROCEDURE chk_lookup_type
556 ( p_lookup_type varchar2
557 ,p_uom varchar2
558 ,p_effective_date date
559 ) IS
560 cursor csr_lookup is
561 select 'X' from hr_lookups
562 where upper(lookup_type) = nvl(upper(p_lookup_type),lookup_type)
563 and enabled_flag = 'Y'
564 and p_effective_date between
565 nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);
566
567 l_dummy varchar2(1);
568
569 l_proc varchar2(72) := g_package||'chk_lookup_type';
570
571 BEGIN
572
573 hr_utility.set_location(' Entering:'|| l_proc, 10);
574
575 if p_lookup_type is not null and upper(p_uom) <> 'C' then
576 fnd_message.set_name('PAY','PAY_34117_INVALID_UOM');
577 fnd_message.raise_error;
578 else
579 open csr_lookup;
580 fetch csr_lookup into l_dummy;
581 if csr_lookup%notfound then
582 close csr_lookup;
583 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
584 fnd_message.raise_error;
585 end if;
586 close csr_lookup;
587 end if;
588
589 hr_utility.set_location(' Leaving:'|| l_proc, 10);
590
591 END chk_lookup_type;
592
593 --
594 -- ----------------------------------------------------------------------------
595 -- |----------------------< chk_formula_id >------------------------------|
596 -- ----------------------------------------------------------------------------
597 --
598 -- Description:
599 -- This procedure is used to ensure that FORMULA_ID is present in
600 -- FF_FORMULAS_F as of session date and must be of type
601 -- 'ELEMENT INPUT VALIDATION'
602 --
603 -- ----------------------------------------------------------------------------
604 PROCEDURE chk_formula_id
605 ( p_business_group_id number
606 ,p_legislation_code varchar2
607 ,p_effective_date date
608 ,p_formula_id number
609 ) IS
610
611 cursor csr_formula is
612 select 'X'
613 from ff_formulas_f ff, ff_formula_types ft
614 where nvl(ff.legislation_code,nvl(p_legislation_code,'~~nvl~~'))
615 = nvl(p_legislation_code,'~~nvl~~')
616 and nvl(ff.business_group_id, nvl(p_business_group_id,-1))=nvl(p_business_group_id,-1)
617 and p_effective_date between ff.effective_start_date and ff.effective_end_date
618 and ff.formula_type_id = ft.formula_type_id
619 and upper (ft.formula_type_name) = 'ELEMENT INPUT VALIDATION'
620 and ff.formula_id = p_formula_id;
621
622 l_dummy varchar2(1);
623
624 l_proc varchar2(72) := g_package||'chk_formula_id';
625
626 BEGIN
627
628 hr_utility.set_location(' Entering:'|| l_proc, 10);
629
630 if p_formula_id is not null then
631 open csr_formula;
632 fetch csr_formula into l_dummy;
633 if csr_formula%notfound then
634 close csr_formula;
635 fnd_message.set_name('PAY','PAY_34116_INVALID_FORMULA');
636 fnd_message.raise_error;
637 end if;
638 close csr_formula;
639 end if;
640
641 hr_utility.set_location(' Leaving:'|| l_proc, 10);
642
643 END chk_formula_id;
644 --
645 -- ----------------------------------------------------------------------------
646 -- |------------------------< chk_formula_validation >------------------------|
647 -- ----------------------------------------------------------------------------
648 Procedure chk_formula_validation
649 (p_default_value in varchar2
650 ,p_warning_or_error in varchar2
651 ,p_effective_date in date
652 ,p_input_value_id in number default null
653 ,p_formula_id in number default null
654 ,p_business_group_id in number
655 ,p_default_formula_warning out nocopy boolean
656 ,p_assignment_id_warning out nocopy boolean
657 ,p_formula_message out nocopy varchar2
658 ) is
659 --
660 l_proc varchar2(72) := g_package||'chk_formula_val';
661 l_formula_status varchar2(10);
662 l_formula_message fnd_new_messages.message_text%type;
663 l_formula_id number;
664 l_inputs ff_exec.inputs_t;
665 l_outputs ff_exec.outputs_t;
666 --
667 Cursor C_formula
668 is
669 select formula_id
670 from pay_input_values_f
671 where input_value_id = p_input_value_id
672 and p_effective_date between effective_start_date
673 and effective_end_date;
674 --
675 Begin
676 hr_utility.set_location(' Entering:'||l_proc, 5);
677 --
678 If p_business_group_id is not null then
679 --
680 -- Validation is not done for startup elements
681 --
682 l_formula_id := p_formula_id;
683 p_assignment_id_warning := false;
684
685 hr_utility.set_location(' Step 1:'||l_proc, 6);
686 If (l_formula_id is null) then
687 --
688 Open C_formula;
689 Fetch C_formula into l_formula_id;
690 Close C_formula;
691 --
692 End If;
693 --
694 hr_utility.set_location(' Step 3:'||l_proc, 8);
695 -- We need to call a formula to validate the default value.
696 --For Bug No. 2879170 added if condtion.
697 If l_formula_id is not null then
698 ff_exec.init_formula(l_formula_id,
699 p_effective_date,
700 l_inputs,
701 l_outputs);
702 --
703 -- Check the input count before attempting to
704 -- set the input and context values.
705 --
706 hr_utility.set_location(' Step 4:'||l_proc, 9);
707 If(l_inputs.count >= 1) then
708 -- Set up the inputs and contexts to formula.
709 For i in l_inputs.first..l_inputs.last loop
710 If l_inputs(i).name = 'ASSIGNMENT_ID' then
711 -- We cannot set assignment id at this level, hence
712 -- raise warning and quit
713 p_assignment_id_warning := True;
714 exit;
715 Elsif l_inputs(i).name = 'BUSINESS_GROUP_ID' then
716 -- Set the business_group_id context.
717 l_inputs(i).value := p_business_group_id;
718 Elsif l_inputs(i).name = 'DATE_EARNED' then
719 -- Set the date_earned context.
720 l_inputs(i).value := fnd_date.date_to_canonical(p_effective_date);
721 Elsif l_inputs(i).name = 'ENTRY_VALUE' then
722 -- Set the input to the entry value to be validated.
723 -- Note - need to pass database format to formula.
724 l_inputs(i).value := p_default_value;
725 Else
726 -- No context recognised.
727 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
728 fnd_message.set_token('PROCEDURE', l_proc);
729 fnd_message.set_token('STEP','10');
730 fnd_message.raise_error;
731 End if;
732 End loop;
733 End if;
734 --
735 -- Dont validate if the assignment context exists or if its a start up
736 -- element.
737 --
738 If not p_assignment_id_warning then
739 --
740 ff_exec.run_formula(l_inputs, l_outputs);
741 --
742 -- Now obtain the return values. There should be
743 -- exactly two outputs.
744 If l_outputs.count <> 2 then
745 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
746 fnd_message.set_token('PROCEDURE', l_proc);
747 fnd_message.set_token('STEP','20');
748 fnd_message.raise_error;
749 End if;
750 --
751 For i in l_outputs.first..l_outputs.last loop
752 If l_outputs(i).name = 'FORMULA_MESSAGE' then
753 --
754 hr_utility.set_location(' Step 5:'||l_proc, 10);
755 l_formula_message := l_outputs(i).value;
756 Elsif l_outputs(i).name = 'FORMULA_STATUS' then
757 --
758 hr_utility.set_location(' Step 6:'||l_proc, 11);
759 l_formula_status := upper(l_outputs(i).value);
760 Else
761 --
762 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
763 fnd_message.set_token('PROCEDURE', l_proc);
764 fnd_message.set_token('STEP','30');
765 fnd_message.raise_error;
766 --
767 End if;
768 End loop;
769 --
770 -- Check whether we have raised an error and act appropriately.
771 --
772 hr_utility.set_location(' Step 7:'||l_proc, 11);
773 If l_formula_status <> 'S' and p_warning_or_error = 'E' then
774 -- I.e. the formula validation failed and we need to raise an error.
775 If l_formula_message is null then
776 -- User not defined an error message.
777 --
778 fnd_message.set_name('PAY','PAY_33083_LK_INP_VAL_FORML_ERR');
779 fnd_message.raise_error;
780 Else
781 -- User has defined message and so we can raise it.
782 fnd_message.set_name('PAY','HR_ELE_ENTRY_FORMULA_HINT');
783 fnd_message.set_token('FORMULA_TEXT', l_formula_message, false);
784 fnd_message.raise_error;
785 End if;
786 Elsif l_formula_status <> 'S' and p_warning_or_error = 'W' then
787 -- We have failed validation, but only want to warn.
788 p_default_formula_warning := true;
789 --
790 End if;
791 --
792 End if;
793
794 p_formula_message := l_formula_message;
795 --
796 End If;
797 End If;
798 --
799 hr_utility.set_location(' Leaving:'||l_proc, 10);
800 End;
801 --
802 -- ----------------------------------------------------------------------------
803 -- |---------------------------<chk_value_set_id >----------------------------|
804 -- ----------------------------------------------------------------------------
805 PROCEDURE chk_value_set_id (
806 p_value_set_id number,
807 p_uom varchar2
808 ) is
809 --
810 l_proc varchar2(72) := g_package||'chk_value_set_id';
811 l_validation_type varchar2(1);
812 --
813 cursor csr_value_set is
814 select validation_type
815 from fnd_flex_value_sets
816 where flex_value_set_id = p_value_set_id;
817 --
818 BEGIN
819 --
820 hr_utility.set_location(' Entering:'|| l_proc, 10);
821 --
822 if p_value_set_id is not null then
823 -- Check uom is 'C'
824 if upper(p_uom) <> 'C' then
825 hr_utility.set_location(l_proc, 20);
826 fnd_message.set_name('PAY','PAY_34117_INVALID_UOM');
827 fnd_message.raise_error;
828 else
829 -- Check value set id is valid, i.e. that it exists and that the value
830 -- set is table-validated, i.e. validation_type is 'F'
831 open csr_value_set;
832 fetch csr_value_set into l_validation_type;
833 if csr_value_set%notfound or l_validation_type <> 'F' then
834 close csr_value_set;
835 hr_utility.set_location(l_proc, 30);
836 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
837 fnd_message.set_token('PROCEDURE', l_proc);
838 fnd_message.set_token('STEP','30');
839 fnd_message.raise_error;
840 end if;
841 close csr_value_set;
842 end if;
843 end if;
844 --
845 hr_utility.set_location(' Leaving:'|| l_proc, 40);
846 --
847 END chk_value_set_id;
848
849 -- ----------------------------------------------------------------------------
850 -- |----------------------<chk_upd_display_sequence >-------------------------|
851 -- ----------------------------------------------------------------------------
852 --
853 -- Description:
854 -- This procedure will check that display sequence is not updated
855 -- for an element's input values if there are paylink batch lines for the
856 -- element.
857 --
858 -- ----------------------------------------------------------------------------
859 PROCEDURE chk_upd_display_sequence
860 (p_element_type_id number
861 ,p_name varchar2
862 ,p_display_sequence number) is
863
864 l_proc varchar2(72) := g_package||'chk_upd_display_sequence';
865
866 BEGIN
867
868 hr_utility.set_location(' Entering:'|| l_proc, 10);
869
870 if (p_name <> pay_ivl_shd.g_old_rec.name or p_display_sequence
871 <> pay_ivl_shd.g_old_rec.display_sequence) then
872 pay_element_types_pkg.check_for_paylink_batches
873 (p_element_type_id => p_element_type_id
874 ,p_element_name => p_name);
875 end if;
876
877 hr_utility.set_location(' Leaving:'|| l_proc, 10);
878
879 END chk_upd_display_sequence;
880
881 --
882 -- ----------------------------------------------------------------------------
883 -- |----------------------<chk_upd_generate_db_items_flag >-------------------|
884 -- ----------------------------------------------------------------------------
885 --
886 -- Description:
887 -- This procedure will ensure that if generate_db_items_flag is updated
888 -- and the datetrack mode is not CORRECTION then it will force CORRECTION
889 -- mode.
890 --
891 -- ----------------------------------------------------------------------------
892 PROCEDURE chk_upd_generate_db_items_flag
893 (p_datetrack_mode IN OUT NOCOPY varchar2
894 ,p_generate_db_items_flag IN varchar2)
895 IS
896
897 l_proc varchar2(72) := g_package||'chk_upd_generate_db_items_flag';
898
899 l_count number;
900
901 BEGIN
902
903 hr_utility.set_location(' Entering:'|| l_proc, 10);
904
905 if (p_generate_db_items_flag <> pay_ivl_shd.g_old_rec.generate_db_items_flag)
906 and p_datetrack_mode <> 'CORRECTION' then
907 open csr_row_count;
908 fetch csr_row_count into l_count;
909 close csr_row_count;
910 if l_count > 1 then
911 fnd_message.set_name('PAY', 'PAY_34151_ELE_NO_DATE_UPD');
912 fnd_message.raise_error;
913 else
914 p_datetrack_mode := 'CORRECTION';
915 end if;
916 end if;
917
918 hr_utility.set_location(' Leaving:'|| l_proc, 10);
919
920 END chk_upd_generate_db_items_flag;
921
922 --
923 -- ----------------------------------------------------------------------------
924 -- |----------------------< chk_upd_name >-------------------------------------|
925 -- ----------------------------------------------------------------------------
926 --
927 -- Description:
928 -- This procedure will do the following checks if name is updated
929 -- 1) Ensure that the mode is CORRECTION , if not then force it.
930 -- 2) Check that the name is unique for the element type id
931 -- 3) Name is not updated if its a 'Pay Value' or updated to become a
932 -- 'Pay Value'
933 -- 4) Name is not updated if contributions used for element type is Yes
934 -- and the name is one of these 'Coverage','EE Contr' and 'ER Contr'
935 -- ----------------------------------------------------------------------------
936 PROCEDURE chk_upd_name
937 (p_datetrack_mode IN OUT NOCOPY varchar2
938 ,p_name IN varchar2
939 ,p_effective_date in date
940 ,p_element_type_id IN number)
941
942 IS
943
944 cursor csr_ben_contri is
945 select contributions_used from
946 ben_benefit_classifications
947 where benefit_classification_id in ( select distinct benefit_classification_id
948 from pay_element_types_f
949 where element_type_id = p_element_type_id
950 and p_effective_date between effective_start_date
951 and effective_end_date);
952
953 l_dummy varchar2(1);
954
955 l_proc varchar2(72) := g_package||'chk_upd_name';
956 l_name pay_input_values_f.name%type := p_name;
957 l_boolean boolean;
958 l_chk_name pay_input_values_f.name%type;
959
960 l_count number;
961
962 BEGIN
963
964 hr_utility.set_location(' Entering:'|| l_proc, 10);
965
966
967 if (p_name <> pay_ivl_shd.g_old_rec.name) then
968
969 -- Force Correction mode
970 if p_datetrack_mode <> 'CORRECTION' then
971 open csr_row_count;
972 fetch csr_row_count into l_count;
973 close csr_row_count;
974 if l_count > 1 then
975 fnd_message.set_name('PAY', 'PAY_34151_ELE_NO_DATE_UPD');
976 fnd_message.raise_error;
977 else
978 p_datetrack_mode := 'CORRECTION';
979 end if;
980 end if;
981
982 -- Check if the new name is unique
983 l_boolean := pay_input_values_pkg.name_not_unique
984 (p_element_type_id,
985 null,
986 l_name,
987 p_error_if_true => TRUE);
988
989
990 -- Check if the name is in proper format
991 hr_chkfmt.checkformat
992 (l_name,
993 'PAY_NAME',
994 l_chk_name,
995 null,
996 null,
997 'N',
998 l_chk_name,
999 null);
1000
1001 -- Check that PAY VALUE name is not updated
1002 if upper(p_name) = 'PAY VALUE' then
1003 fnd_message.set_name('PAY', 'PAY_34126_NAME_PAY_VAL_UPD');
1004 fnd_message.raise_error;
1005 end if;
1006
1007 -- Check for the new name
1008 if upper(pay_ivl_shd.g_old_rec.name) = 'PAY VALUE' then
1009 fnd_message.set_name('PAY', 'PAY_34126_NAME_PAY_VAL_UPD');
1010 fnd_message.raise_error;
1011 end if;
1012
1013 -- Name is not updated if contributions used for element type is Yes
1014 -- and the name is one of these 'Coverage','EE Contr' and 'ER Contr'
1015 open csr_ben_contri;
1016 fetch csr_ben_contri into l_dummy;
1017
1018 if l_dummy = 'Y' and upper(pay_ivl_shd.g_old_rec.name)
1019 in ('COVERAGE','EE CONTR','ER CONTR')
1020 then
1021 close csr_ben_contri;
1022 fnd_message.set_name('PAY', 'PAY_33078_LK_INP_VAL_NO_UPD');
1023 fnd_message.raise_error;
1024 end if;
1025 close csr_ben_contri;
1026
1027 end if;
1028
1029 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1030
1031 END chk_upd_name;
1032
1033 --
1034 -- ----------------------------------------------------------------------------
1035 -- |----------------------< chk_upd_uom >-------------------------------------|
1036 -- ----------------------------------------------------------------------------
1037 --
1038 -- Description:
1039 -- This procedure will check that if the UOM is updated, force the datetrack
1040 -- mode to CORRECTION, if not the same.Also check that the UOM is updated
1041 -- only in its class and recreate db items.
1042 --
1043 -- ----------------------------------------------------------------------------
1044 PROCEDURE chk_upd_uom
1045 (p_datetrack_mode IN OUT NOCOPY varchar2
1046 ,p_uom IN varchar2
1047 ,p_input_value_id in number
1048 ,p_effective_date in date)
1049 IS
1050 cursor csr_uom is
1051 select lookup_code from
1052 hr_lookups
1053 where upper(substr(lookup_code,1,2))
1054 = upper(substr(pay_ivl_shd.g_old_rec.uom,1,2))
1055 and lookup_type = 'UNITS'
1056 and enabled_flag = 'Y'
1057 and p_effective_date between
1058 nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);
1059
1060 l_dummy varchar2(1);
1061
1062 l_count number;
1063
1064 l_proc varchar2(72) := g_package||'chk_upd_uom';
1065
1066 BEGIN
1067
1068 hr_utility.set_location(' Entering:'|| l_proc, 10);
1069
1070 -- Check that the UOM if updated to a not null value is
1071 -- within the class as the previous UOM value
1072 if (p_uom <> pay_ivl_shd.g_old_rec.uom and p_uom is not null) then
1073 if p_datetrack_mode <> 'CORRECTION' then
1074 open csr_row_count;
1075 fetch csr_row_count into l_count;
1076 close csr_row_count;
1077 if l_count > 1 then
1078 fnd_message.set_name('PAY', 'PAY_34151_ELE_NO_DATE_UPD');
1079 fnd_message.raise_error;
1080 else
1081 p_datetrack_mode := 'CORRECTION';
1082 end if;
1083 end if;
1084 if pay_ivl_shd.g_old_rec.uom is not null then
1085 for rec in csr_uom
1086 loop
1087 if substr(rec.lookup_code,1,2) <> substr(p_uom,1,2) then
1088 fnd_message.set_name('PAY', 'PAY_34127_UOM_UPD_CLASS');
1089 fnd_message.raise_error;
1090 end if;
1091 end loop;
1092 end if;
1093 end if;
1094
1095 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1096
1097 END chk_upd_uom;
1098
1099 --
1100 -- ----------------------------------------------------------------------------
1101 -- |----------------------< chk_upd_def_value_null >----------------------|
1102 -- ----------------------------------------------------------------------------
1103 --
1104 -- Description:
1105 -- This procedure will ensure that update fails if
1106 -- the input value is used in a pay basis or there exists
1107 -- element entries for this hot defaulted input value
1108 --
1109 -- ----------------------------------------------------------------------------
1110 PROCEDURE chk_upd_def_value_null
1111 (p_input_value_id in number
1112 ,p_effective_date in date
1113 ,p_default_value in varchar2
1114 ,p_pay_basis_warning out nocopy boolean)
1115 IS
1116
1117 cursor csr_pay_basis is
1118 select 1
1119 from per_pay_bases
1120 where input_value_id = p_input_value_id;
1121
1122 cursor csr_entries is
1123 select 1
1124 from pay_element_entry_values_f
1125 where input_value_id = p_input_value_id
1126 and p_effective_date between effective_start_date
1127 and effective_end_date;
1128
1129
1130 l_dummy varchar2(1);
1131
1132
1133 l_proc varchar2(72) := g_package||'chk_upd_def_value_null';
1134
1135 BEGIN
1136
1137 hr_utility.set_location(' Entering:'|| l_proc, 10);
1138
1139 open csr_pay_basis;
1140
1141 -- Check for pay basis
1142
1143 fetch csr_pay_basis into l_dummy;
1144 if csr_pay_basis%found then
1145 close csr_pay_basis;
1146 p_pay_basis_warning := TRUE;
1147 end if;
1148 close csr_pay_basis;
1149
1150
1151 if p_default_value is null then
1152 -- Check for element entries and Hot default flag
1153
1154 open csr_entries;
1155 fetch csr_entries into l_dummy;
1156 if csr_entries%found and pay_ivl_shd.g_old_rec.hot_default_flag = 'Y' then
1157 close csr_entries;
1158 fnd_message.set_name('PAY', 'PAY_34128_DEF_VAL_HOT_DEF');
1159 fnd_message.raise_error;
1160 end if;
1161 close csr_entries;
1162 end if;
1163
1164 hr_utility.set_location(' Entering:'|| l_proc, 10);
1165
1166 END chk_upd_def_value_null;
1167
1168 --
1169 -- ----------------------------------------------------------------------------
1170 -- |----------------------<chk_upd_mand_flag >--------------------------------|
1171 -- ----------------------------------------------------------------------------
1172 --
1173 -- Description:
1174 -- This procedure ensures that the mandatory flag cannot be changed from
1175 -- 'N' to 'Y'.
1176 --
1177 -- ----------------------------------------------------------------------------
1178 PROCEDURE chk_upd_mand_flag
1179 (p_mandatory_flag varchar2
1180 ) IS
1181
1182 l_proc varchar2(72) := g_package||'chk_upd_mand_flag';
1183
1184 BEGIN
1185
1186 hr_utility.set_location(' Entering:'|| l_proc, 10);
1187
1188
1189 if pay_ivl_shd.g_old_rec.mandatory_flag = 'N' and p_mandatory_flag = 'Y' then
1190 fnd_message.set_name('PAY', 'PAY_34125_MAN_FLAG_UPD');
1191 fnd_message.raise_error;
1192 end if;
1193
1194 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1195
1196 END chk_upd_mand_flag;
1197
1198 --
1199 -- ----------------------------------------------------------------------------
1200 -- |----------------------<chk_hot_default_flag >----------------------------|
1201 -- ----------------------------------------------------------------------------
1202 --
1203 -- Description:
1204 -- This procedure will check that hot default flag is not set to 'Y'
1205 -- if mandatory flag is not 'Y'
1206 --
1207 -- ----------------------------------------------------------------------------
1208 PROCEDURE chk_hot_default_flag
1209 (p_mandatory_flag varchar2
1210 ,p_hot_default_flag varchar2
1211 ) IS
1212 l_proc varchar2(72) := g_package||'chk_hot_default_flag';
1213
1214 BEGIN
1215
1216 hr_utility.set_location(' Entering:'|| l_proc, 10);
1217
1218 if p_hot_default_flag = 'Y' and p_mandatory_flag <> 'Y' then
1219 fnd_message.set_name('PAY','PAY_34119_HOT_DEFAULT_FLAG');
1220 fnd_message.raise_error;
1221 end if;
1222
1223 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1224
1225 END chk_hot_default_flag;
1226 --
1227 -- ----------------------------------------------------------------------------
1228 -- |----------------------<chk_name >-----------------------------------------|
1229 -- ----------------------------------------------------------------------------
1230 --
1231 -- Description:
1232 -- This procedure will do the following validations
1233 -- 1) Validate NAME as a valid db item name using hr_fmt.checkformat
1234 -- 2) NAME must be unique for a element
1235 -- 3) The name 'Pay Value' must have a UOM of 'Money' if the element
1236 -- classifications is of PAYMENTS type
1237 --
1238 -- ----------------------------------------------------------------------------
1239 PROCEDURE chk_name
1240 (p_name varchar2
1241 ,p_element_type_id number
1242 ,p_uom varchar2
1243 ) IS
1244
1245 cursor csr_classification is
1246 select non_payments_flag
1247 from pay_element_classifications
1248 where classification_id in ( select distinct classification_id
1249 from pay_element_types_f
1250 where element_type_id = p_element_type_id);
1251
1252 l_boolean boolean;
1253 l_name pay_input_values_f.name%type := p_name;
1254 l_dummy varchar2(100);
1255
1256 l_proc varchar2(72) := g_package||'chk_name';
1257
1258 BEGIN
1259
1260 hr_utility.set_location(' Entering:'|| l_proc, 10);
1261
1262 -- Check that the name is unique
1263
1264 l_boolean := pay_input_values_pkg.name_not_unique
1265 (p_element_type_id,
1266 null,
1267 l_name,
1268 p_error_if_true => TRUE);
1269
1270 -- Check the name for proper format
1271 hr_chkfmt.checkformat
1272 (l_name,
1273 'PAY_NAME',
1274 l_dummy,
1275 null,
1276 null,
1277 'N',
1278 l_dummy,
1279 null);
1280
1281 -- Check that the name 'Pay Value' must have a UOM of 'Money' if the element
1282 -- classifications is of PAYMENTS type
1283
1284 open csr_classification;
1285 fetch csr_classification into l_dummy;
1286 close csr_classification;
1287
1288
1289 if upper(l_name) = 'PAY VALUE' and upper(p_uom) <> 'M' and l_dummy = 'N' then
1290 fnd_message.set_name('PAY', 'PAY_34122_UOM_MONEY_PAYMENTS');
1291 fnd_message.raise_error;
1292 end if;
1293
1294 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1295
1296 END chk_name;
1297
1298 --
1299 -- ----------------------------------------------------------------------------
1300 -- |----------------------<chk_uom >-----------------------------------------|
1301 -- ----------------------------------------------------------------------------
1302 --
1303 -- Description:
1304 -- This procedure will do the following validations
1305 -- 1) The UOM cannot be 'Money' if no currencies have been specified for the
1306 -- element type
1307 -- 2) Validate UOM with HR_LOOKUPS having LOOKUP_TYPE as 'UNITS'
1308 -- and lookup_code not equal to 'M' if element_types'
1309 -- output_currency_code is null
1310 --
1311 -- ----------------------------------------------------------------------------
1312 PROCEDURE chk_uom
1313 (p_element_type_id in number
1314 ,p_uom in varchar2
1315 ,p_effective_date in date
1316 ) IS
1317 cursor csr_currency is
1318
1319 select input_currency_code
1320 from pay_element_types_f
1321 where element_type_id = p_element_type_id;
1322
1323 cursor csr_lookup is
1324 select 'X' from hr_lookups
1325 where upper(lookup_code) = nvl(upper(p_uom),lookup_code)
1326 and lookup_type = 'UNITS'
1327 and enabled_flag = 'Y'
1328 and p_effective_date between
1329 nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);
1330
1331 l_dummy varchar2(1);
1332
1333 l_proc varchar2(72) := g_package||'chk_uom';
1334
1335 BEGIN
1336
1337 hr_utility.set_location(' Entering:'|| l_proc, 10);
1338
1339 for rec in csr_currency
1340 loop
1341 if rec.input_currency_code is null and upper(p_uom) = 'M' then
1342 fnd_message.set_name('PAY', 'PAY_6626_INPVAL_NO_MONEY_UOM');
1343 fnd_message.raise_error;
1344 end if;
1345 end loop;
1346
1347 open csr_lookup;
1348
1349 fetch csr_lookup into l_dummy;
1350 if csr_lookup%notfound then
1351 fnd_message.set_name('PAY', 'PAY_6171_INPVAL_NO_LOOKUP');
1352 fnd_message.raise_error;
1353 end if;
1354
1355 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1356
1357 END chk_uom;
1358
1359 --
1360 -- ----------------------------------------------------------------------------
1361 -- |----------------------<chk_default_value >--------------------------------|
1362 -- ----------------------------------------------------------------------------
1363 --
1364 -- Description:
1365 -- This procedure will do the following validations
1366 -- 1) DEFAULT_VALUE has to be validated against HR_LOOKUPS for lookup_type
1367 -- equal to specified in LOOKUP_TYPE field (active as of current date),
1368 -- if not null
1369 --
1370 -- 2) DEFAULT_VALUE must lie between MIN_VALUE and MAX_VALUE (if LOOKUP_TYPE is
1371 -- null) and if DEFAULT_VALUE does not lie between MIN_VALUE and MAX_VALUE
1372 -- then depending on whether WARNING_OR_ERROR is 'E', an error must be
1373 -- raised else just a warning must be issued
1374 -- ----------------------------------------------------------------------------
1375 PROCEDURE chk_default_value
1376 ( p_element_type_id in number
1377 ,p_default_value in varchar2
1378 ,p_lookup_type in varchar2
1379 ,p_value_set_id in number
1380 ,p_min_value in varchar2
1381 ,p_max_value in varchar2
1382 ,p_uom in varchar2
1383 ,p_warning_or_error in varchar2
1384 ,p_effective_date in date
1385 ,p_default_val_warning out nocopy boolean
1386 )
1387 IS
1388 --
1389 cursor csr_lookup is
1390 select 'X' from hr_lookups
1391 where upper(lookup_code) = nvl(upper(p_default_value),lookup_code)
1392 and lookup_type = p_lookup_type
1393 and enabled_flag = 'Y'
1394 and p_effective_date between
1395 nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);
1396 --
1397 cursor csr_currency(p_element_type_id number) is
1398 select input_currency_code
1399 from pay_element_types_f
1400 where element_type_id = p_element_type_id;
1401 --
1402 l_dummy varchar(1);
1403 l_proc varchar2(72) := g_package||'chk_default_value';
1404 l_input_currency_code varchar2(10) ;
1405 --
1406 BEGIN
1407 --
1408 hr_utility.set_location(' Entering:'|| l_proc, 10);
1409 --
1410 if p_default_value is not null then
1411 --
1412 if p_lookup_type is not null then
1413 --
1414 hr_utility.set_location(l_proc, 20);
1415 --
1416 open csr_lookup;
1417 fetch csr_lookup into l_dummy;
1418 --
1419 if csr_lookup%notfound then
1420 --
1421 hr_utility.set_location(l_proc, 30);
1422 --
1423 close csr_lookup;
1424 --
1425 fnd_message.set_name('PAY', 'PAY_6171_INPVAL_NO_LOOKUP');
1426 fnd_message.raise_error;
1427 --
1428 end if;
1429 --
1430 close csr_lookup;
1431 --
1432 elsif p_value_set_id is not null then
1433 --
1434 hr_utility.set_location(l_proc,40);
1435 if pay_input_values_pkg.decode_vset_value (
1436 p_value_set_id,
1437 p_default_value ) is null then
1438 --
1439 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1440 fnd_message.set_token('PROCEDURE', l_proc);
1441 fnd_message.set_token('STEP','40');
1442 fnd_message.raise_error;
1443 --
1444 end if;
1445 --
1446
1447 -- Bug 6164772
1448
1449 elsif (p_min_value is not null or p_max_value is not null) then
1450 --
1451 hr_utility.set_location(' Leaving:'|| l_proc, 50);
1452 --
1453 open csr_currency(p_element_type_id);
1454 fetch csr_currency into l_input_currency_code ;
1455 close csr_currency;
1456 --
1457 pay_ivl_bus.chk_default_value_format
1458 ( p_default_value
1459 ,p_lookup_type
1460 ,p_min_value
1461 ,p_max_value
1462 ,p_uom
1463 ,l_input_currency_code
1464 ,p_warning_or_error
1465 ,p_default_val_warning
1466 );
1467
1468 end if;
1469 --
1470 end if;
1471 --
1472 hr_utility.set_location(' Leaving:'|| l_proc, 60);
1473 --
1474 END chk_default_value;
1475
1476 --
1477 -- ----------------------------------------------------------------------------
1478 -- |----------------------<chk_upd_default_value >----------------------------|
1479 -- ----------------------------------------------------------------------------
1480 --
1481 -- Description:
1482 -- This procedure will do the following validations
1483 -- 1) DEFAULT_VALUE has to be validated against HR_LOOKUPS for lookup_type
1484 -- equal to specified in LOOKUP_TYPE field (active as of current date),
1485 -- if not null
1486 -- 2) DEFAULT_VALUE must lie between MIN_VALUE and MAX_VALUE (if LOOKUP_TYPE
1487 -- is null) and if DEFAULT_VALUE does not lie between MIN_VALUE and
1488 -- MAX_VALUE then depending on whether WARNING_OR_ERROR is 'E', an
1489 -- error must be raised else just a warning must be issued
1490 -- ----------------------------------------------------------------------------
1491 /* Commented for bug 6164772 - Use chk_default_value in place of chk_upd_default_value
1492 as both do the same validations.
1493
1494 PROCEDURE chk_upd_default_value
1495 ( p_default_value in varchar2
1496 ,p_lookup_type in varchar2
1497 ,p_value_set_id in number
1498 ,p_min_value in varchar2
1499 ,p_max_value in varchar2
1500 ,p_warning_or_error in varchar2
1501 ,p_effective_date in date
1502 ,p_default_val_warning out nocopy boolean
1503 )
1504 IS
1505 --
1506 cursor csr_lookup is
1507 select 'X'
1508 from hr_lookups
1509 where upper(lookup_code) = nvl(upper(p_default_value),lookup_code)
1510 and lookup_type = decode(p_lookup_type,
1511 hr_api.g_varchar2, pay_ivl_shd.g_old_rec.lookup_type,
1512 p_lookup_type)
1513 and enabled_flag = 'Y'
1514 and p_effective_date between nvl(start_date_active,hr_api.g_sot)
1515 and nvl(end_date_active,hr_api.g_eot);
1516 --
1517 l_dummy varchar(1);
1518 l_value_set_id number;
1519 l_proc varchar2(72) := g_package||'chk_upd_default_value';
1520 --
1521 BEGIN
1522 --
1523 hr_utility.set_location(' Entering:'|| l_proc, 10);
1524 --
1525 if p_lookup_type is not null or (
1526 pay_ivl_shd.g_old_rec.lookup_type is not null and
1527 p_lookup_type = hr_api.g_varchar2
1528 ) then
1529 --
1530 open csr_lookup;
1531 fetch csr_lookup into l_dummy;
1532 --
1533 if csr_lookup%notfound then
1534 --
1535 close csr_lookup;
1536 --
1537 fnd_message.set_name('PAY', 'PAY_6171_INPVAL_NO_LOOKUP');
1538 fnd_message.raise_error;
1539 --
1540 end if;
1541 --
1542 close csr_lookup;
1543 --
1544 elsif p_value_set_id is not null and p_default_value is not null and
1545 p_default_value <> hr_api.g_varchar2 and (
1546 p_value_set_id <> hr_api.g_number or (
1547 pay_ivl_shd.g_old_rec.value_set_id is not null and
1548 p_value_set_id = hr_api.g_number
1549 )
1550 ) then
1551 --
1552 if p_value_set_id = hr_api.g_number then
1553 --
1554 -- Value set id is not changing, use the old one
1555 --
1556 l_value_set_id := pay_ivl_shd.g_old_rec.value_set_id;
1557 --
1558 else
1559 --
1560 -- Value set id is changing, use the new one
1561 --
1562 l_value_set_id := p_value_set_id;
1563 --
1564 end if;
1565 --
1566 hr_utility.set_location(l_proc,20);
1567 --
1568 -- Validate the default value using l_value_set_id
1569 --
1570 if pay_input_values_pkg.decode_vset_value (
1571 l_value_set_id,
1572 p_default_value ) is null then
1573 --
1574 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1575 fnd_message.set_token('PROCEDURE', l_proc);
1576 fnd_message.set_token('STEP','20');
1577 fnd_message.raise_error;
1578 --
1579 end if;
1580 --
1581 elsif pay_ivl_shd.g_old_rec.min_value is not null
1582 or pay_ivl_shd.g_old_rec.max_value is not null then
1583 --
1584 if p_min_value <> pay_ivl_shd.g_old_rec.min_value OR p_max_value <> pay_ivl_shd.g_old_rec.max_value then
1585 --
1586 if (p_default_value <= p_min_value or
1587 p_default_value >= p_max_value) then
1588 --
1589 if p_warning_or_error = 'E' then
1590 --
1591 fnd_message.set_name('PAY', 'PAY_6303_INPUT_VALUE_OUT_RANGE');
1592 fnd_message.raise_error;
1593 --
1594 elsif p_warning_or_error = 'W' then
1595 --
1596 p_default_val_warning := TRUE ;
1597 --
1598 end if;
1599 --
1600 end if;
1601 --
1602 else
1603 --
1604 if (p_default_value <= pay_ivl_shd.g_old_rec.min_value or
1605 p_default_value >= pay_ivl_shd.g_old_rec.max_value) then
1606 --
1607 if p_warning_or_error = 'E' then
1608 --
1609 fnd_message.set_name('PAY', 'PAY_6303_INPUT_VALUE_OUT_RANGE');
1610 fnd_message.raise_error;
1611 --
1612 elsif p_warning_or_error = 'W' then
1613 --
1614 p_default_val_warning := TRUE ;
1615 --
1616 end if;
1617 --
1618 end if;
1619 --
1620 end if;
1621 --
1622 end if;
1623 --
1624 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1625 --
1626 END chk_upd_default_value;
1627 */
1628
1629 --
1630 -- ----------------------------------------------------------------------------
1631 -- |----------------------<chk_max_min_value >--------------------------------|
1632 -- ----------------------------------------------------------------------------
1633 --
1634 -- Description:
1635 -- This procedure will check if MIN_VALUE is less than or equal to MAX_VALUE
1636 --
1637 -- ----------------------------------------------------------------------------
1638 PROCEDURE chk_max_min_value
1639 ( p_element_type_id in number
1640 ,p_max_value in varchar2
1641 ,p_min_value in varchar2
1642 ,p_uom in varchar2
1643 ,p_warning_or_error in varchar2
1644 ,p_lookup_type in varchar2
1645 ,p_min_max_warning out nocopy boolean
1646 )
1647 IS
1648 --
1649 cursor csr_currency(p_element_type_id number) is
1650 select input_currency_code
1651 from pay_element_types_f
1652 where element_type_id = p_element_type_id;
1653 --
1654 l_proc varchar2(72) := g_package||'chk_max_min_value';
1655 l_range_check varchar2(10);
1656 l_input_currency_code varchar2(10) ;
1657 l_max_value varchar2(255);
1658 --
1659 BEGIN
1660 --
1661 hr_utility.set_location(' Entering:'|| l_proc, 10);
1662 --
1663 if(p_max_value is not null and p_min_value is not null) then
1664 --
1665 hr_utility.set_location(' Entering:'|| l_proc, 20);
1666 --
1667 open csr_currency(p_element_type_id);
1668 fetch csr_currency into l_input_currency_code ;
1669 close csr_currency;
1670 --
1671 l_max_value := p_max_value;
1672 hr_chkfmt.checkformat
1673 ( l_max_value
1674 ,p_uom
1675 ,l_max_value
1676 ,p_min_value
1677 ,null
1678 ,'Y'
1679 ,l_range_check
1680 ,l_input_currency_code
1681 );
1682 --
1683 if l_range_check = 'F' then
1684 --
1685 if p_warning_or_error = 'E' then
1686 --
1687 fnd_message.set_name('PAY', 'HR_51975_ALL_MAX_MORE_MIN');
1688 fnd_message.raise_error;
1689 --
1690 elsif p_warning_or_error = 'W' then
1691 --
1692 p_min_max_warning := TRUE;
1693 --
1694 end if;
1695 --
1696 end if;
1697 --
1698 end if;
1699 --
1700 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1701 --
1702 END chk_max_min_value;
1703
1704 --
1705 -- ----------------------------------------------------------------------------
1706 -- |----------------------<chk_warning_or_error>------------------------------|
1707 -- ----------------------------------------------------------------------------
1708 --
1709 -- Description:
1710 -- This procedure will do the following validations
1711 -- 1) WARNING_OR_ERROR must be set in case a MIN_VALUE/MAX_VALUE/FORMULA_ID
1712 -- is specified
1713 -- 2) Validate with HR_LOOKUPS having LOOKUP_TYPE as 'WARNING_ERROR'
1714 -- and active as of current date
1715 -- ----------------------------------------------------------------------------
1716 PROCEDURE chk_warning_or_error
1717 (p_warning_or_error in varchar2
1718 ,p_lookup_type in varchar2
1719 ,p_min_value in varchar2
1720 ,p_max_value in varchar2
1721 ,p_formula_id in varchar2
1722 ,p_effective_date in date
1723 )
1724 IS
1725 cursor csr_lookup is
1726 select 'X' from hr_lookups
1727 where upper(lookup_code) = nvl(upper(p_warning_or_error),lookup_code)
1728 and lookup_type = 'WARNING_ERROR'
1729 and enabled_flag = 'Y'
1730 and p_effective_date between
1731 nvl(start_date_active,hr_api.g_sot) and nvl(end_date_active,hr_api.g_eot);
1732
1733 l_dummy varchar2(1);
1734 l_proc varchar2(72) := g_package||'chk_warning_or_error';
1735 BEGIN
1736
1737 hr_utility.set_location(' Entering:'|| l_proc, 10);
1738
1739 if (p_min_value is not null or p_max_value is not null or p_formula_id
1740 is not NULL)
1741 -- or p_lookup_type is not null) -- bug 8675578
1742 and p_warning_or_error is null then
1743 fnd_message.set_name('PAY','PAY_34121_WARN_ERROR_MAND');
1744 fnd_message.raise_error;
1745 elsif (p_min_value is null and p_max_value is null)
1746 and p_formula_id is null
1747 and p_lookup_type is null -- 6164772
1748 and p_warning_or_error is not null then
1749 fnd_message.set_name('PAY','PAY_6908_INPVAL_ERROR_VAL');
1750 fnd_message.raise_error;
1751 end if;
1752
1753 open csr_lookup;
1754 fetch csr_lookup into l_dummy;
1755 if csr_lookup%notfound then
1756 close csr_lookup;
1757 fnd_message.set_name('PAY','HR_52966_INVALID_LOOKUP');
1758 fnd_message.raise_error;
1759 end if;
1760 close csr_lookup;
1761
1762 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1763 END chk_warning_or_error;
1764
1765 --
1766 -- ----------------------------------------------------------------------------
1767 -- |----------------------<chk_other_insert_val>------------------------------|
1768 -- ----------------------------------------------------------------------------
1769 --
1770 -- Description:
1771 -- This procedure will do the following validations
1772 -- 1) Only 15 input values can be created for each element
1773 -- 2) Insert should not be allowed if there exists
1774 -- element entries or pay run results for this element type
1775 -- 3) Any of the following combinations can be entered at a time,
1776 -- 1) FORMULA_ID and DEFAULT_VALUE
1777 -- 2) LOOKUP_TYPE and DEFAULT_VALUE
1778 -- 3) DEFAULT_VALUE, MIN_VALUE and MAX_VALUE
1779 -- 4) VALUE_SET_ID and DEFAULT_VALUE
1780 --
1781 -- ----------------------------------------------------------------------------
1782 PROCEDURE chk_other_insert_val
1783 (p_element_type_id in number
1784 ,p_formula_id in number
1785 ,p_lookup_type in varchar2
1786 ,p_value_set_id in number
1787 ,p_min_value in varchar2
1788 ,p_max_value in varchar2
1789 ,p_start_date in date
1790 ,p_end_date in date
1791 ,p_pay_basis_warning out nocopy boolean
1792 )
1793 IS
1794 cursor csr_run_results is
1795 select 1
1796 from dual
1797 where exists
1798 (select /*+ INDEX(PAYROLL PAY_PAYROLL_ACTIONS_PK)
1799 INDEX(ASSIGN PAY_ASSIGNMENT_ACTIONS_PK) */ 1
1800 from pay_run_results RUN,
1801 pay_payroll_actions PAYROLL,
1802 pay_assignment_actions ASSIGN
1803 where run.element_type_id = p_element_type_id
1804 and assign.assignment_action_id = run.assignment_action_id
1805 and assign.payroll_action_id = payroll.payroll_action_id
1806 and payroll.effective_date between p_start_date
1807 and p_end_date);
1808
1809 cursor csr_pay_basis is
1810 select 1 pay
1811 from per_pay_bases
1812 where input_value_id in ( select input_value_id
1813 from pay_input_values_f
1814 where element_type_id = p_element_type_id);
1815
1816 l_dummy varchar2(1);
1817 l_proc varchar2(72) := g_package||'chk_other_insert_val';
1818
1819 BEGIN
1820
1821 hr_utility.set_location(' Entering:'|| l_proc, 10);
1822 if pay_input_values_pkg.no_of_input_values(p_element_type_id) >= 15 then
1823 fnd_message.set_name('PAY','HR_7124_INPVAL_MAX_ENTRIES');
1824 fnd_message.raise_error;
1825 end if;
1826
1827 open csr_run_results;
1828 fetch csr_run_results into l_dummy;
1829 if csr_run_results%found
1830 or pay_element_types_pkg.element_entries_exist (
1831 p_element_type_id,
1832 p_error_if_true => TRUE
1833 ) then
1834 fnd_message.set_name('PAY','PAY_34123_CANNOT_CR_INP_VAL');
1835 fnd_message.raise_error;
1836 end if;
1837 close csr_run_results;
1838 if p_formula_id is not null and (
1839 p_lookup_type is not null or
1840 p_value_set_id is not null or
1841 p_min_value is not null or
1842 p_max_value is not null
1843 ) then
1844 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1845 fnd_message.raise_error;
1846 elsif p_lookup_type is not null and (
1847 p_formula_id is not null or
1848 p_value_set_id is not null or
1849 p_min_value is not null or
1850 p_max_value is not null
1851 ) then
1852 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1853 fnd_message.raise_error;
1854 elsif p_value_set_id is not null and (
1855 p_formula_id is not null or
1856 p_lookup_type is not null or
1857 p_min_value is not null or
1858 p_max_value is not null
1859 ) then
1860 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1861 fnd_message.raise_error;
1862 elsif (p_min_value is not null or p_max_value is not null) and (
1863 p_formula_id is not null or
1864 p_lookup_type is not null or
1865 p_value_set_id is not null
1866 ) then
1867 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1868 fnd_message.raise_error;
1869 end if;
1870
1871 for rec in csr_pay_basis
1872 loop
1873 if rec.pay = '1' then
1874 p_pay_basis_warning := TRUE;
1875 end if;
1876 end loop;
1877
1878 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1879
1880 END chk_other_insert_val;
1881 --
1882 -- ----------------------------------------------------------------------------
1883 -- |----------------------<chk_other_upd_val>------------------------------|
1884 -- ----------------------------------------------------------------------------
1885 --
1886 -- Description:
1887 -- This procedure will check only one of the following combinations can be
1888 -- entered at a time,
1889 -- 1) FORMULA_ID and DEFAULT_VALUE
1890 -- 2) LOOKUP_TYPE and DEFAULT_VALUE
1891 -- 3) DEFAULT_VALUE, MIN_VALUE and MAX_VALUE
1892 -- 4) VALUE_SET_ID and DEFAULT_VALUE
1893 --
1894 -- ----------------------------------------------------------------------------
1895 PROCEDURE chk_other_upd_val
1896 (p_formula_id in number
1897 ,p_lookup_type in varchar2
1898 ,p_value_set_id in number
1899 ,p_min_value in varchar2
1900 ,p_max_value in varchar2
1901 )
1902 IS
1903
1904 BEGIN
1905
1906 if pay_ivl_shd.g_old_rec.formula_id is not null and
1907 p_formula_id is not null and (
1908 p_lookup_type is not null or
1909 p_value_set_id is not null or
1910 p_min_value is not null or
1911 p_max_value is not null
1912 ) then
1913
1914 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1915 fnd_message.raise_error;
1916
1917 elsif pay_ivl_shd.g_old_rec.lookup_type is not null and
1918 p_lookup_type is not null and (
1919 p_formula_id is not null or
1920 p_value_set_id is not null or
1921 p_min_value is not null or
1922 p_max_value is not null
1923 ) then
1924
1925 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1926 fnd_message.raise_error;
1927
1928 elsif pay_ivl_shd.g_old_rec.value_set_id is not null and
1929 p_value_set_id is not null and (
1930 p_formula_id is not null or
1931 p_lookup_type is not null or
1932 p_min_value is not null or
1933 p_max_value is not null
1934 ) then
1935
1936 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1937 fnd_message.raise_error;
1938
1939 elsif (pay_ivl_shd.g_old_rec.min_value is not null and
1940 p_min_value is not null or
1941 pay_ivl_shd.g_old_rec.max_value is not null and
1942 p_max_value is not null) and (
1943 p_formula_id is not null or
1944 p_lookup_type is not null or
1945 p_value_set_id is not null
1946 ) then
1947
1948 fnd_message.set_name('PAY','PAY_6170_INPVAL_VAL_COMB');
1949 fnd_message.raise_error;
1950
1951 end if;
1952
1953 END chk_other_upd_val;
1954
1955 --
1956 -- ----------------------------------------------------------------------------
1957 -- |----------------------<chk_raise_warning>------------------------------|
1958 -- ----------------------------------------------------------------------------
1959 --
1960 -- Description:
1961 -- This procedure will set p_link_inp_val_warning to TRUE if name or
1962 -- default value or lookup type or min value or max value or warning_or_error
1963 -- is updated
1964 --
1965 -- ----------------------------------------------------------------------------
1966 PROCEDURE chk_raise_warning
1967 (p_lookup_type in varchar2
1968 ,p_name in varchar2
1969 ,p_default_value in varchar2
1970 ,p_min in varchar2
1971 ,p_max in varchar2
1972 ,p_warning_or_error in varchar2
1973 ,p_link_inp_val_warning IN OUT NOCOPY boolean)
1974 IS
1975
1976 l_proc varchar2(72) := g_package||'chk_raise_warning';
1977
1978 BEGIN
1979
1980 hr_utility.set_location(' Entering:'|| l_proc, 10);
1981
1982
1983 if p_lookup_type <> pay_ivl_shd.g_old_rec.lookup_type
1984 OR p_default_value <> pay_ivl_shd.g_old_rec.default_value OR p_min <> pay_ivl_shd.g_old_rec.min_value
1985 OR p_max <> pay_ivl_shd.g_old_rec.max_value OR p_warning_or_error <> pay_ivl_shd.g_old_rec.warning_or_error
1986 then
1987 p_link_inp_val_warning := TRUE;
1988 end if;
1989
1990 hr_utility.set_location(' Leaving:'|| l_proc, 10);
1991
1992 END chk_raise_warning;
1993
1994 --
1995 -- ----------------------------------------------------------------------------
1996 -- |----------------------<chk_delete_allowed>------------------------------|
1997 -- ----------------------------------------------------------------------------
1998 --
1999 -- Description:
2000 -- This procedure will do the following validations
2001 -- a) Not allowed for all delete modes if the element has
2002 -- benefit contributions_used flag as 'Y'
2003 -- and name is 'Coverage' or 'EE Contr' or 'ER Contr', except when
2004 -- the element itself is deleted.
2005 -- b) Not allowed for date effective delete for the following scenarios
2006 -- 1. Formula result rules apply to this input value (either as
2007 -- indirect or update recurring) after the new end date and
2008 -- the rules are not self-referential.
2009 -- 2. An absence exists for a range of time outside of
2010 -- the new date-effective lifetime of the input value.
2011 -- c) Not allowed for ZAP mode for the following scenarios
2012 -- 1. Element entry values for the input value exist.
2013 -- 2. Run result values for the input value exist.
2014 -- 3. Compiled formulae use db items of the input value
2015 -- 4. Formula result rules refer to the input value, and those
2016 -- rules are not self-referential.
2017 -- 5. Absence attendance types exist for this input value.
2018 -- 6. BackPay rules exist for this input value.
2019 -- 7. The input value is PAY_VALUE and links exist for the
2020 -- element type with a costable type of 'Distributed'.
2021 -- 8. The input value is used by a salary basis.
2022 -- 9. The input value is used by an accrual plan.
2023 -- 10. The input value is used by a net calculation rule.
2024 --
2025 -- ----------------------------------------------------------------------------
2026 PROCEDURE chk_delete_allowed
2027 (p_datetrack_mode in varchar2
2028 ,p_input_value_id in number
2029 ,p_element_type_id in number
2030 ,p_effective_date in date
2031 ,p_start_date in date
2032 ,p_end_date in date)
2033 IS
2034 cursor csr_classification is
2035
2036 select contributions_used
2037 from ben_benefit_classifications
2038 where benefit_classification_id in ( select distinct benefit_classification_id
2039 from pay_element_types_f
2040 where element_type_id = p_element_type_id
2041 and p_effective_date between effective_start_date
2042 and effective_end_date);
2043
2044 cursor csr_name is
2045 select 'X'
2046 from pay_input_values_f
2047 where input_value_id = p_input_value_id
2048 and upper(name) in ('COVERAGE','EE CONTR','ER CONTR');
2049
2050 l_dummy varchar2(100);
2051 l_name varchar2(100);
2052
2053 l_proc varchar2(72) := g_package||'chk_delete_allowed';
2054
2055 BEGIN
2056
2057 hr_utility.set_location(' Entering:'|| l_proc, 10);
2058
2059 open csr_classification;
2060 fetch csr_classification into l_dummy;
2061 close csr_classification;
2062
2063 open csr_name;
2064 fetch csr_name into l_name;
2065
2066 if csr_name%found and l_dummy = 'Y' then
2067 close csr_name;
2068 fnd_message.set_name('PAY','PAY_34129_INPVAL_NAME_DEL');
2069 fnd_message.raise_error;
2070 end if;
2071 close csr_name;
2072
2073 if pay_input_values_pkg.deletion_allowed (
2074 p_input_value_id,
2075 p_datetrack_mode ,
2076 p_start_date ,
2077 p_end_date ,
2078 TRUE) then
2079 null;
2080 end if;
2081
2082 hr_utility.set_location(' Leaving:'|| l_proc, 10);
2083
2084 END chk_delete_allowed;
2085
2086 --
2087 -- ----------------------------------------------------------------------------
2088 -- |---------------------------< insert_validate >----------------------------|
2089 -- ----------------------------------------------------------------------------
2090 Procedure insert_validate
2091 (p_rec in pay_ivl_shd.g_rec_type
2092 ,p_effective_date in date
2093 ,p_datetrack_mode in varchar2
2094 ,p_validation_start_date in date
2095 ,p_validation_end_date in date
2096 ,p_default_val_warning out nocopy boolean
2097 ,p_min_max_warning out nocopy boolean
2098 ,p_pay_basis_warning out nocopy boolean
2099 ,p_formula_warning out nocopy boolean
2100 ,p_assignment_id_warning out nocopy boolean
2101 ,p_formula_message out nocopy varchar2
2102 ) is
2103 --
2104 l_proc varchar2(72) := g_package||'insert_validate';
2105 --
2106 Begin
2107 hr_utility.set_location('Entering:'||l_proc, 5);
2108 --
2109 -- Call all supporting business operations
2110 --
2111 --
2112 chk_startup_action(true
2113 ,p_rec.business_group_id
2114 ,p_rec.legislation_code
2115 );
2116 IF hr_startup_data_api_support.g_startup_mode
2117 NOT IN ('GENERIC','STARTUP') THEN
2118 --
2119 -- Validate Important Attributes
2120 --
2121 hr_api.validate_bus_grp_id
2122 (p_business_group_id => p_rec.business_group_id
2123 ,p_associated_column1 => pay_ivl_shd.g_tab_nam
2124 || '.BUSINESS_GROUP_ID');
2125 --
2126 -- after validating the set of important attributes,
2127 -- if Multiple Message Detection is enabled and at least
2128 -- one error has been found then abort further validation.
2129 --
2130 hr_multi_message.end_validation_set;
2131 END IF;
2132 IF hr_startup_data_api_support.g_startup_mode NOT IN ('STARTUP') THEN
2133 pay_ivl_bus.chk_other_insert_val
2134 (p_element_type_id => p_rec.element_type_id
2135 ,p_formula_id => p_rec.formula_id
2136 ,p_lookup_type => p_rec.lookup_type
2137 ,p_value_set_id => p_rec.value_set_id
2138 ,p_min_value => p_rec.min_value
2139 ,p_max_value => p_rec.max_value
2140 ,p_start_date => p_validation_start_date
2141 ,p_end_date => p_validation_end_date
2142 ,p_pay_basis_warning => p_pay_basis_warning
2143 );
2144
2145 pay_ivl_bus.chk_lookup_type
2146 (p_lookup_type => p_rec.lookup_type
2147 ,p_uom => p_rec.uom
2148 ,p_effective_date => p_effective_date);
2149
2150 pay_ivl_bus.chk_formula_id
2151 ( p_business_group_id => p_rec.business_group_id
2152 ,p_legislation_code => p_rec.legislation_code
2153 ,p_effective_date => p_effective_date
2154 ,p_formula_id => p_rec.formula_id
2155 );
2156
2157 pay_ivl_bus.chk_formula_validation
2158 ( p_default_value => p_rec.default_value
2159 ,p_warning_or_error => p_rec.warning_or_error
2160 ,p_effective_date => p_effective_date
2161 ,p_formula_id => p_rec.formula_id
2162 ,p_business_group_id => p_rec.business_group_id
2163 ,p_default_formula_warning => p_formula_warning
2164 ,p_assignment_id_warning => p_assignment_id_warning
2165 ,p_formula_message => p_formula_message
2166 );
2167
2168 pay_ivl_bus.chk_value_set_id (
2169 p_value_set_id => p_rec.value_set_id,
2170 p_uom => p_rec.uom
2171 );
2172
2173 pay_ivl_bus.chk_hot_default_flag
2174 (p_mandatory_flag => p_rec.mandatory_flag
2175 ,p_hot_default_flag => p_rec.hot_default_flag
2176 );
2177
2178 pay_ivl_bus.chk_name
2179 (p_name => p_rec.name
2180 ,p_element_type_id => p_rec.element_type_id
2181 ,p_uom => p_rec.uom
2182 );
2183
2184 pay_ivl_bus.chk_uom
2185 (p_element_type_id => p_rec.element_type_id
2186 ,p_uom => p_rec.uom
2187 ,p_effective_date => p_effective_date );
2188
2189 -- Bug 6164772. Changed the order of call to raise proper error message
2190 -- when check_format() errors from within
2191
2192 pay_ivl_bus.chk_max_min_value
2193 (p_element_type_id => p_rec.element_type_id
2194 ,p_max_value => p_rec.max_value
2195 ,p_min_value => p_rec.min_value
2196 ,p_uom => p_rec.uom
2197 ,p_warning_or_error => p_rec.warning_or_error
2198 ,p_lookup_type => p_rec.lookup_type
2199 ,p_min_max_warning => p_min_max_warning
2200 );
2201
2202 pay_ivl_bus.chk_default_value
2203 ( p_element_type_id => p_rec.element_type_id
2204 ,p_default_value => p_rec.default_value
2205 ,p_lookup_type => p_rec.lookup_type
2206 ,p_value_set_id => p_rec.value_set_id
2207 ,p_min_value => p_rec.min_value
2208 ,p_max_value => p_rec.max_value
2209 ,p_uom => p_rec.uom
2210 ,p_warning_or_error => p_rec.warning_or_error
2211 ,p_effective_date => p_effective_date
2212 ,p_default_val_warning => p_default_val_warning
2213 );
2214
2215 pay_ivl_bus.chk_warning_or_error
2216 (p_warning_or_error => p_rec.warning_or_error
2217 ,p_lookup_type => p_rec.lookup_type
2218 ,p_min_value => p_rec.min_value
2219 ,p_max_value => p_rec.max_value
2220 ,p_formula_id => p_rec.formula_id
2221 ,p_effective_date => p_effective_date
2222 );
2223 END IF;
2224 --
2225 --
2226 -- Validate Dependent Attributes
2227 --
2228 --
2229 hr_utility.set_location(' Leaving:'||l_proc, 10);
2230 --
2231 End insert_validate;
2232 --
2233 -- ----------------------------------------------------------------------------
2234 -- |---------------------------< update_validate >----------------------------|
2235 -- ----------------------------------------------------------------------------
2236 Procedure update_validate
2237 (p_rec in pay_ivl_shd.g_rec_type
2238 ,p_effective_date in date
2239 ,p_datetrack_mode in out nocopy varchar2
2240 ,p_validation_start_date in date
2241 ,p_validation_end_date in date
2242 ,p_default_val_warning out nocopy boolean
2243 ,p_min_max_warning out nocopy boolean
2244 ,p_link_inp_val_warning out nocopy boolean
2245 ,p_pay_basis_warning out nocopy boolean
2246 ,p_formula_warning out nocopy boolean
2247 ,p_assignment_id_warning out nocopy boolean
2248 ,p_formula_message out nocopy varchar2
2249 ) is
2250 --
2251 l_proc varchar2(72) := g_package||'update_validate';
2252 l_default_formula_warning boolean;
2253 --
2254 Begin
2255 hr_utility.set_location('Entering:'||l_proc, 5);
2256 --
2257 -- Call all supporting business operations
2258 --
2259 --
2260 chk_startup_action(false
2261 ,p_rec.business_group_id
2262 ,p_rec.legislation_code
2263 );
2264 IF hr_startup_data_api_support.g_startup_mode
2265 NOT IN ('GENERIC','STARTUP') THEN
2266 --
2267 -- Validate Important Attributes
2268 --
2269
2270 hr_api.validate_bus_grp_id
2271 (p_business_group_id => p_rec.business_group_id
2272 ,p_associated_column1 => pay_ivl_shd.g_tab_nam
2273 || '.BUSINESS_GROUP_ID');
2274 --
2275 -- After validating the set of important attributes,
2276 -- if Multiple Message Detection is enabled and at least
2277 -- one error has been found then abort further validation.
2278 --
2279 hr_multi_message.end_validation_set;
2280
2281 END IF;
2282 IF hr_startup_data_api_support.g_startup_mode NOT IN ('STARTUP') THEN
2283 pay_ivl_bus.chk_other_upd_val
2284 (p_formula_id => p_rec.formula_id
2285 ,p_lookup_type => p_rec.lookup_type
2286 ,p_value_set_id => p_rec.value_set_id
2287 ,p_min_value => p_rec.min_value
2288 ,p_max_value => p_rec.max_value);
2289
2290 pay_ivl_bus.chk_upd_generate_db_items_flag
2291 (p_datetrack_mode => p_datetrack_mode
2292 ,p_generate_db_items_flag => p_rec.generate_db_items_flag);
2293
2294 pay_ivl_bus.chk_upd_name
2295 (p_datetrack_mode => p_datetrack_mode
2296 ,p_name => p_rec.name
2297 ,p_effective_date => p_effective_date
2298 ,p_element_type_id => p_rec.element_type_id);
2299
2300 pay_ivl_bus.chk_upd_uom
2301 (p_datetrack_mode => p_datetrack_mode
2302 ,p_uom => p_rec.uom
2303 ,p_input_value_id => p_rec.input_value_id
2304 ,p_effective_date => p_effective_date);
2305
2306 pay_ivl_bus.chk_upd_def_value_null
2307 (p_input_value_id => p_rec.input_value_id
2308 ,p_effective_date => p_effective_date
2309 ,p_default_value => p_rec.default_value
2310 ,p_pay_basis_warning => p_pay_basis_warning);
2311
2312 pay_ivl_bus.chk_upd_mand_flag
2313 (p_mandatory_flag => p_rec.mandatory_flag
2314 );
2315
2316 pay_ivl_bus.chk_hot_default_flag
2317 (p_mandatory_flag => p_rec.mandatory_flag
2318 ,p_hot_default_flag => p_rec.hot_default_flag);
2319
2320 pay_ivl_bus.chk_lookup_type
2321 (p_lookup_type => p_rec.lookup_type
2322 ,p_uom => p_rec.uom
2323 ,p_effective_date => p_effective_date);
2324
2325 pay_ivl_bus.chk_formula_id
2326 ( p_business_group_id => p_rec.business_group_id
2327 ,p_legislation_code => p_rec.legislation_code
2328 ,p_effective_date => p_effective_date
2329 ,p_formula_id => p_rec.formula_id
2330 );
2331
2332 pay_ivl_bus.chk_formula_validation
2333 ( p_default_value => p_rec.default_value
2334 ,p_warning_or_error => p_rec.warning_or_error
2335 ,p_effective_date => p_effective_date
2336 ,p_formula_id => p_rec.formula_id
2337 ,p_business_group_id => p_rec.business_group_id
2338 ,p_default_formula_warning => p_formula_warning
2339 ,p_assignment_id_warning => p_assignment_id_warning
2340 ,p_formula_message => p_formula_message
2341 );
2342
2343 pay_ivl_bus.chk_value_set_id (
2344 p_value_set_id => p_rec.value_set_id,
2345 p_uom => p_rec.uom
2346 );
2347
2348 pay_ivl_bus.chk_upd_display_sequence
2349 (p_element_type_id => p_rec.element_type_id
2350 ,p_name => p_rec.name
2351 ,p_display_sequence => p_rec.display_sequence );
2352
2353 -- Bug 6164772
2354
2355 pay_ivl_bus.chk_max_min_value
2356 (p_element_type_id => p_rec.element_type_id
2357 ,p_max_value => p_rec.max_value
2358 ,p_min_value => p_rec.min_value
2359 ,p_uom => p_rec.uom
2360 ,p_warning_or_error => p_rec.warning_or_error
2361 ,p_lookup_type => p_rec.lookup_type
2362 ,p_min_max_warning => p_min_max_warning
2363 );
2364
2365 pay_ivl_bus.chk_default_value
2366 ( p_element_type_id => p_rec.element_type_id
2367 ,p_default_value => p_rec.default_value
2368 ,p_lookup_type => p_rec.lookup_type
2369 ,p_value_set_id => p_rec.value_set_id
2370 ,p_min_value => p_rec.min_value
2371 ,p_max_value => p_rec.max_value
2372 ,p_uom => p_rec.uom
2373 ,p_warning_or_error => p_rec.warning_or_error
2374 ,p_effective_date => p_effective_date
2375 ,p_default_val_warning => p_default_val_warning
2376 );
2377
2378 pay_ivl_bus.chk_warning_or_error
2379 (p_warning_or_error => p_rec.warning_or_error
2380 ,p_lookup_type => p_rec.lookup_type
2381 ,p_min_value => p_rec.min_value
2382 ,p_max_value => p_rec.max_value
2383 ,p_formula_id => p_rec.formula_id
2384 ,p_effective_date => p_effective_date
2385 );
2386
2387 pay_ivl_bus.chk_raise_warning
2388 (p_lookup_type => p_rec.lookup_type
2389 ,p_name => p_rec.name
2390 ,p_default_value => p_rec.default_value
2391 ,p_min => p_rec.min_value
2392 ,p_max => p_rec.max_value
2393 ,p_warning_or_error => p_rec.warning_or_error
2394 ,p_link_inp_val_warning => p_link_inp_val_warning);
2395
2396 /* Bug 6164772
2397 if p_rec.default_value <> hr_api.g_varchar2 then
2398 pay_ivl_bus.chk_upd_default_value
2399 ( p_default_value => p_rec.default_value
2400 ,p_lookup_type => p_rec.lookup_type
2401 ,p_value_set_id => p_rec.value_set_id
2402 ,p_min_value => p_rec.min_value
2403 ,p_max_value => p_rec.max_value
2404 ,p_warning_or_error => p_rec.warning_or_error
2405 ,p_effective_date => p_effective_date
2406 ,p_default_val_warning => p_default_val_warning
2407 );
2408 end if;
2409 */
2410 --
2411 --
2412 -- Validate Dependent Attributes
2413 --
2414 -- Call the datetrack update integrity operation
2415 --
2416 dt_update_validate
2417 (p_element_type_id => p_rec.element_type_id
2418 ,p_datetrack_mode => p_datetrack_mode
2419 ,p_validation_start_date => p_validation_start_date
2420 ,p_validation_end_date => p_validation_end_date
2421 );
2422 --
2423 chk_non_updateable_args
2424 (p_effective_date => p_effective_date
2425 ,p_rec => p_rec
2426 );
2427 END IF;
2428 --
2429 --
2430 hr_utility.set_location(' Leaving:'||l_proc, 10);
2431 End update_validate;
2432 --
2433 -- ----------------------------------------------------------------------------
2434 -- |---------------------------< delete_validate >----------------------------|
2435 -- ----------------------------------------------------------------------------
2436 Procedure delete_validate
2437 (p_rec in pay_ivl_shd.g_rec_type
2438 ,p_effective_date in date
2439 ,p_datetrack_mode in varchar2
2440 ,p_validation_start_date in date
2441 ,p_validation_end_date in date
2442
2443 ) is
2444 --
2445 l_proc varchar2(72) := g_package||'delete_validate';
2446 --
2447 Begin
2448 hr_utility.set_location('Entering:'||l_proc, 5);
2449 --
2450 --
2451 chk_startup_action(false
2452 ,pay_ivl_shd.g_old_rec.business_group_id
2453 ,pay_ivl_shd.g_old_rec.legislation_code
2454 );
2455 IF hr_startup_data_api_support.g_startup_mode
2456 NOT IN ('GENERIC','STARTUP') THEN
2457 --
2458 -- Validate Important Attributes
2459 --
2460 --
2461 -- After validating the set of important attributes,
2462 -- if Multiple Message Detection is enabled and at least
2463 -- one error has been found then abort further validation.
2464 --
2465 hr_multi_message.end_validation_set;
2466
2467 END IF;
2468
2469 pay_ivl_bus.chk_delete_allowed
2470 (p_datetrack_mode => p_datetrack_mode
2471 ,p_input_value_id => p_rec.input_value_id
2472 ,p_element_type_id => p_rec.element_type_id
2473 ,p_effective_date => p_effective_date
2474 ,p_start_date => p_validation_start_date
2475 ,p_end_date => p_validation_end_date);
2476
2477
2478 --
2479 -- Call all supporting business operations
2480 --
2481 dt_delete_validate
2482 (p_datetrack_mode => p_datetrack_mode
2483 ,p_validation_start_date => p_validation_start_date
2484 ,p_validation_end_date => p_validation_end_date
2485 ,p_input_value_id => p_rec.input_value_id
2486 );
2487 --
2488 hr_utility.set_location(' Leaving:'||l_proc, 10);
2489 End delete_validate;
2490 --
2491 end pay_ivl_bus;
[Home] [Help]