1 Package Body pay_uci_bus as
2 /* $Header: pyucirhi.pkb 115.0 2003/09/23 07:31 tvankayl noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_uci_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_user_column_instance_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_user_column_instance_id in number
22 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id
29 from per_business_groups pbg
30 , pay_user_column_instances_f uci
31 where uci.user_column_instance_id = p_user_column_instance_id
32 and pbg.business_group_id = uci.business_group_id;
33 --
34 -- Declare local variables
35 --
36 l_security_group_id number;
37 l_proc varchar2(72) := g_package||'set_security_group_id';
38 --
39 begin
40 --
41 hr_utility.set_location('Entering:'|| l_proc, 10);
42 --
43 -- Ensure that all the mandatory parameter are not null
44 --
45 hr_api.mandatory_arg_error
46 (p_api_name => l_proc
47 ,p_argument => 'user_column_instance_id'
48 ,p_argument_value => p_user_column_instance_id
49 );
50 --
51 open csr_sec_grp;
52 fetch csr_sec_grp into l_security_group_id;
53 --
54 if csr_sec_grp%notfound then
55 --
56 close csr_sec_grp;
57 --
58 -- The primary key is invalid therefore we must error
59 --
60 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
61 hr_multi_message.add
62 (p_associated_column1
63 => nvl(p_associated_column1,'USER_COLUMN_INSTANCE_ID')
64 );
65 --
66 else
67 close csr_sec_grp;
68 --
69 -- Set the security_group_id in CLIENT_INFO
70 --
71 hr_api.set_security_group_id
72 (p_security_group_id => l_security_group_id
73 );
74 end if;
75 --
76 hr_utility.set_location(' Leaving:'|| l_proc, 20);
77 --
78 end set_security_group_id;
79 --
80 -- ---------------------------------------------------------------------------
81 -- |---------------------< return_legislation_code >-------------------------|
82 -- ---------------------------------------------------------------------------
83 --
84 Function return_legislation_code
85 (p_user_column_instance_id in number
86 )
87 Return Varchar2 Is
88 --
89 -- Declare cursor
90 --
91 cursor csr_leg_code is
92 select pbg.legislation_code
93 from per_business_groups pbg
94 , pay_user_column_instances_f uci
95 where uci.user_column_instance_id = p_user_column_instance_id
96 and pbg.business_group_id (+) = uci.business_group_id;
97 --
98 -- Declare local variables
99 --
100 l_legislation_code varchar2(150);
101 l_proc varchar2(72) := g_package||'return_legislation_code';
102 --
103 Begin
104 --
105 hr_utility.set_location('Entering:'|| l_proc, 10);
106 --
107 -- Ensure that all the mandatory parameter are not null
108 --
109 hr_api.mandatory_arg_error
110 (p_api_name => l_proc
111 ,p_argument => 'user_column_instance_id'
112 ,p_argument_value => p_user_column_instance_id
113 );
114 --
115 if ( nvl(pay_uci_bus.g_user_column_instance_id, hr_api.g_number)
116 = p_user_column_instance_id) then
117 --
118 -- The legislation code has already been found with a previous
119 -- call to this function. Just return the value in the global
120 -- variable.
121 --
122 l_legislation_code := pay_uci_bus.g_legislation_code;
123 hr_utility.set_location(l_proc, 20);
124 else
125 --
126 -- The ID is different to the last call to this function
127 -- or this is the first call to this function.
128 --
129 open csr_leg_code;
130 fetch csr_leg_code into l_legislation_code;
131 --
132 if csr_leg_code%notfound then
133 --
134 -- The primary key is invalid therefore we must error
135 --
136 close csr_leg_code;
137 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
138 fnd_message.raise_error;
139 end if;
140 hr_utility.set_location(l_proc,30);
141 --
142 -- Set the global variables so the values are
143 -- available for the next call to this function.
144 --
145 close csr_leg_code;
146 pay_uci_bus.g_user_column_instance_id := p_user_column_instance_id;
147 pay_uci_bus.g_legislation_code := l_legislation_code;
148 end if;
149 hr_utility.set_location(' Leaving:'|| l_proc, 40);
150 return l_legislation_code;
151 end return_legislation_code;
152 --
153 -- ----------------------------------------------------------------------------
154 -- |-----------------------< chk_non_updateable_args >------------------------|
155 -- ----------------------------------------------------------------------------
156 -- {Start Of Comments}
157 --
158 -- Description:
159 -- This procedure is used to ensure that non updateable attributes have
160 -- not been updated. If an attribute has been updated an error is generated.
161 --
162 -- Pre Conditions:
163 -- g_old_rec has been populated with details of the values currently in
164 -- the database.
165 --
166 -- In Arguments:
167 -- p_rec has been populated with the updated values the user would like the
168 -- record set to.
169 --
170 -- Post Success:
171 -- Processing continues if all the non updateable attributes have not
172 -- changed.
173 --
174 -- Post Failure:
175 -- An application error is raised if any of the non updatable attributes
176 -- have been altered.
177 --
178 -- {End Of Comments}
179 -- ----------------------------------------------------------------------------
180 Procedure chk_non_updateable_args
181 (p_effective_date in date
182 ,p_rec in pay_uci_shd.g_rec_type
183 ) IS
184 --
185 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
186 --
187 Begin
188 --
189 hr_utility.set_location('Entering:'|| l_proc, 10);
190 --
191 -- Only proceed with the validation if a row exists for the current
192 -- record in the HR Schema.
193 --
194 IF NOT pay_uci_shd.api_updating
195 (p_user_column_instance_id => p_rec.user_column_instance_id
196 ,p_effective_date => p_effective_date
197 ,p_object_version_number => p_rec.object_version_number
198 ) THEN
199 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
200 fnd_message.set_token('PROCEDURE ', l_proc);
201 fnd_message.set_token('STEP ', '5');
202 fnd_message.raise_error;
203 END IF;
204 --
205 --
206 if nvl(p_rec.user_row_id, hr_api.g_number) <>
207 pay_uci_shd.g_old_rec.user_row_id then
208 hr_api.argument_changed_error
209 (p_api_name => l_proc
210 ,p_argument => 'USER_ROW_ID'
211 ,p_base_table => pay_uci_shd.g_tab_nam
212 );
213 end if;
214 --
215 if nvl(p_rec.user_column_id, hr_api.g_number) <>
216 pay_uci_shd.g_old_rec.user_column_id then
217 hr_api.argument_changed_error
218 (p_api_name => l_proc
219 ,p_argument => 'USER_COLUMN_ID'
220 ,p_base_table => pay_uci_shd.g_tab_nam
221 );
222 end if;
223 --
224 if nvl(p_rec.business_group_id, hr_api.g_number) <>
225 nvl(pay_uci_shd.g_old_rec.business_group_id, hr_api.g_number) then
226 hr_api.argument_changed_error
227 (p_api_name => l_proc
228 ,p_argument => 'BUSINESS_GROUP_ID'
229 ,p_base_table => pay_uci_shd.g_tab_nam
230 );
231 end if;
232 --
233 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
234 nvl(pay_uci_shd.g_old_rec.legislation_code, hr_api.g_varchar2) then
235 hr_api.argument_changed_error
236 (p_api_name => l_proc
237 ,p_argument => 'LEGISLATION_CODE'
238 ,p_base_table => pay_uci_shd.g_tab_nam
239 );
240 end if;
241 --
242 hr_utility.set_location(' Leaving:'|| l_proc, 20);
243 --
244 End chk_non_updateable_args;
245 --
246 -- ----------------------------------------------------------------------------
247 -- |--------------------------< dt_update_validate >--------------------------|
248 -- ----------------------------------------------------------------------------
249 -- {Start Of Comments}
250 --
251 -- Description:
252 -- This procedure is used for referential integrity of datetracked
253 -- parent entities when a datetrack update operation is taking place
254 -- and where there is no cascading of update defined for this entity.
255 --
256 -- Prerequisites:
257 -- This procedure is called from the update_validate.
258 --
259 -- In Parameters:
260 --
261 -- Post Success:
262 -- Processing continues.
263 --
264 -- Post Failure:
265 --
266 -- Developer Implementation Notes:
267 -- This procedure should not need maintenance unless the HR Schema model
268 -- changes.
269 --
270 -- Access Status:
271 -- Internal Row Handler Use Only.
272 --
273 -- {End Of Comments}
274 -- ----------------------------------------------------------------------------
275 Procedure dt_update_validate
276 (p_user_row_id in number default hr_api.g_number
277 ,p_datetrack_mode in varchar2
278 ,p_validation_start_date in date
279 ,p_validation_end_date in date
280 ) Is
281 --
282 l_proc varchar2(72) := g_package||'dt_update_validate';
283 --
284 Begin
285 --
286 -- Ensure that the p_datetrack_mode argument is not null
287 --
288 hr_api.mandatory_arg_error
289 (p_api_name => l_proc
290 ,p_argument => 'datetrack_mode'
291 ,p_argument_value => p_datetrack_mode
292 );
293 --
294 -- Mode will be valid, as this is checked at the start of the upd.
295 --
296 -- Ensure the arguments are not null
297 --
298 hr_api.mandatory_arg_error
299 (p_api_name => l_proc
300 ,p_argument => 'validation_start_date'
301 ,p_argument_value => p_validation_start_date
302 );
303 --
304 hr_api.mandatory_arg_error
305 (p_api_name => l_proc
306 ,p_argument => 'validation_end_date'
307 ,p_argument_value => p_validation_end_date
308 );
309 --
310 If ((nvl(p_user_row_id, hr_api.g_number) <> hr_api.g_number) and
311 NOT (dt_api.check_min_max_dates
312 (p_base_table_name => 'pay_user_rows_f'
313 ,p_base_key_column => 'USER_ROW_ID'
314 ,p_base_key_value => p_user_row_id
315 ,p_from_date => p_validation_start_date
316 ,p_to_date => p_validation_end_date))) Then
317 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
318 fnd_message.set_token('TABLE_NAME','user rows');
319 hr_multi_message.add
320 (p_associated_column1 => pay_uci_shd.g_tab_nam || '.USER_ROW_ID');
321 End If;
322 --
323 Exception
324 When Others Then
325 --
326 -- An unhandled or unexpected error has occurred which
327 -- we must report
328 --
329 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
330 fnd_message.set_token('PROCEDURE', l_proc);
331 fnd_message.set_token('STEP','15');
332 fnd_message.raise_error;
333 End dt_update_validate;
334 --
335 -- ----------------------------------------------------------------------------
336 -- |--------------------------< dt_delete_validate >--------------------------|
337 -- ----------------------------------------------------------------------------
338 -- {Start Of Comments}
339 --
340 -- Description:
341 -- This procedure is used for referential integrity of datetracked
342 -- child entities when either a datetrack DELETE or ZAP is in operation
343 -- and where there is no cascading of delete defined for this entity.
344 -- For the datetrack mode of DELETE or ZAP we must ensure that no
345 -- datetracked child rows exist between the validation start and end
346 -- dates.
347 --
348 -- Prerequisites:
349 -- This procedure is called from the delete_validate.
350 --
351 -- In Parameters:
352 --
353 -- Post Success:
354 -- Processing continues.
355 --
356 -- Post Failure:
357 -- If a row exists by determining the returning Boolean value from the
358 -- generic dt_api.rows_exist function then we must supply an error via
359 -- the use of the local exception handler l_rows_exist.
360 --
361 -- Developer Implementation Notes:
362 -- This procedure should not need maintenance unless the HR Schema model
363 -- changes.
364 --
365 -- Access Status:
366 -- Internal Row Handler Use Only.
367 --
368 -- {End Of Comments}
369 -- ----------------------------------------------------------------------------
370 Procedure dt_delete_validate
371 (p_user_column_instance_id in number
372 ,p_datetrack_mode in varchar2
373 ,p_validation_start_date in date
374 ,p_validation_end_date in date
375 ) Is
376 --
377 l_proc varchar2(72) := g_package||'dt_delete_validate';
378 --
379 Begin
380 --
381 -- Ensure that the p_datetrack_mode argument is not null
382 --
383 hr_api.mandatory_arg_error
384 (p_api_name => l_proc
385 ,p_argument => 'datetrack_mode'
386 ,p_argument_value => p_datetrack_mode
387 );
388 --
389 -- Only perform the validation if the datetrack mode is either
390 -- DELETE or ZAP
391 --
392 If (p_datetrack_mode = hr_api.g_delete or
393 p_datetrack_mode = hr_api.g_zap) then
394 --
395 --
396 -- Ensure the arguments are not null
397 --
398 hr_api.mandatory_arg_error
399 (p_api_name => l_proc
400 ,p_argument => 'validation_start_date'
401 ,p_argument_value => p_validation_start_date
402 );
403 --
404 hr_api.mandatory_arg_error
405 (p_api_name => l_proc
406 ,p_argument => 'validation_end_date'
407 ,p_argument_value => p_validation_end_date
408 );
409 --
410 hr_api.mandatory_arg_error
411 (p_api_name => l_proc
412 ,p_argument => 'user_column_instance_id'
413 ,p_argument_value => p_user_column_instance_id
414 );
415 --
416 --
417 --
418 End If;
419 --
420 Exception
421 When Others Then
422 --
423 -- An unhandled or unexpected error has occurred which
424 -- we must report
425 --
426 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
427 fnd_message.set_token('PROCEDURE', l_proc);
428 fnd_message.set_token('STEP','15');
429 fnd_message.raise_error;
430 --
431 End dt_delete_validate;
432 --
433 -- ----------------------------------------------------------------------------
434 -- |----------------------< chk_startup_action >------------------------------|
435 -- ----------------------------------------------------------------------------
436 --
437 -- Description:
438 -- This procedure will check that the current action is allowed according
439 -- to the current startup mode.
440 --
441 -- ----------------------------------------------------------------------------
442 PROCEDURE chk_startup_action
443 (p_insert IN boolean
444 ,p_business_group_id IN number
445 ,p_legislation_code IN varchar2
446 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
447 --
448 BEGIN
449 --
450 -- Call the supporting procedure to check startup mode
451
452 IF (p_insert) THEN
453
454 if p_business_group_id is not null and p_legislation_code is not null then
455 fnd_message.set_name('PAY', 'PAY_33179_BGLEG_INVALID');
456 fnd_message.raise_error;
457 end if;
458
459 hr_startup_data_api_support.chk_startup_action
460 (p_generic_allowed => TRUE
461 ,p_startup_allowed => TRUE
462 ,p_user_allowed => TRUE
463 ,p_business_group_id => p_business_group_id
464 ,p_legislation_code => p_legislation_code
465 ,p_legislation_subgroup => p_legislation_subgroup
466 );
467 ELSE
468 hr_startup_data_api_support.chk_upd_del_startup_action
469 (p_generic_allowed => TRUE
470 ,p_startup_allowed => TRUE
471 ,p_user_allowed => TRUE
472 ,p_business_group_id => p_business_group_id
473 ,p_legislation_code => p_legislation_code
474 ,p_legislation_subgroup => p_legislation_subgroup
475 );
476 END IF;
477 --
478 END chk_startup_action;
479 --
480 -- ----------------------------------------------------------------------------
481 -- |--------------------------< chk_legislation_code>-------------------------|
482 -- ----------------------------------------------------------------------------
483 --
484 -- Description:
485 -- Validates that the legislation code exists in fnd_territories
486 --
487 -- Pre-Requisites:
488 -- None
489 --
490 -- In Parameters:
491 -- p_legislation_code
492 --
493 -- Post Success:
494 -- Processing continues if the legislation_code is valid.
495 --
496 -- Post Failure:
497 -- An application error is raised and processing is terminated if
498 -- the legislation_code is invalid.
499 --
500 -- Developer/Implementation Notes:
501 -- None
502 --
503 -- Access Status:
504 -- Internal Row Handler Use Only
505 --
506 procedure chk_legislation_code
507 ( p_legislation_code in varchar2 )
508 is
509 --
510 cursor csr_legislation_code is
511 select null
512 from fnd_territories
513 where territory_code = p_legislation_code ;
514 --
515 l_exists varchar2(1);
516 l_proc varchar2(100) := g_package || 'chk_legislation_code';
517 begin
518 --
519 hr_utility.set_location('Entering:'|| l_proc, 10);
520
521 open csr_legislation_code;
522 fetch csr_legislation_code into l_exists ;
523
524 if csr_legislation_code%notfound then
525 close csr_legislation_code;
526 fnd_message.set_name('PAY', 'PAY_33177_LEG_CODE_INVALID');
527 fnd_message.raise_error;
528 end if;
529 close csr_legislation_code;
530
531 hr_utility.set_location(' Leaving:'|| l_proc, 20);
532 --
533 exception
534 when app_exception.application_exception then
535 if hr_multi_message.exception_add
536 (p_associated_column1 => 'PAY_USER_COLUMN_INSTANCES_F.LEGISLATION_CODE'
537 ) then
538 raise;
539 end if;
540 when others then
541 if csr_legislation_code%isopen then
542 close csr_legislation_code;
543 end if;
544 raise;
545 end chk_legislation_code;
546 --
547 -- ----------------------------------------------------------------------------
548 -- |--------------------------< chk_user_row_id >-----------------------------|
549 -- ----------------------------------------------------------------------------
550 --
551 -- Description:
552 -- Validates that the user_row_id exists in pay_user_rows_f for the life span
553 -- of the row being inserted.
554 --
555 -- Pre-Requisites:
556 -- None
557 --
558 -- In Parameters:
559 -- p_user_row_id
560 -- p_legislation_code
561 -- p_business_group_id
562 -- p_validation_start_date
563 -- p_validation_end_date
564 --
565 -- Post Success:
566 -- Processing continues if the user_row_id is valid.
567 --
568 -- Post Failure:
569 -- An application error is raised and processing is terminated if
570 -- the user_row_id is invalid.
571 --
572 -- Developer/Implementation Notes:
573 -- None
574 --
575 -- Access Status:
576 -- Internal Row Handler Use Only
577 --
578 procedure chk_user_row_id
579 (p_user_row_id in number
580 ,p_legislation_code in varchar2
581 ,p_business_group_id in number
582 ,p_validation_start_date in date
583 ,p_validation_end_date in date
584 ) is
585 --
586 cursor csr_user_row_id is
587 select pur.legislation_code , pur.business_group_id , min(effective_start_date) , max(effective_end_date)
588 from pay_user_rows_f pur
589 where pur.user_row_id = p_user_row_id group by pur.business_group_id , pur.legislation_code ;
590
591 --
592 l_busgrpid PAY_USER_ROWS_F.BUSINESS_GROUP_ID%TYPE;
593 l_legcode PAY_USER_ROWS_F.LEGISLATION_CODE%TYPE;
594 l_min_esd PAY_USER_ROWS_F.EFFECTIVE_START_DATE%TYPE;
595 l_max_eed PAY_USER_ROWS_F.EFFECTIVE_END_DATE%TYPE;
596 l_proc varchar2(100) := g_package || 'chk_user_row_id';
597 --
598 begin
599
600 hr_utility.set_location('Entering:'|| l_proc, 10);
601 --
602 -- USER_ROW_ID is mandatory.
603 --
604 hr_api.mandatory_arg_error
605 (p_api_name => l_proc
606 ,p_argument => 'USER_ROW_ID'
607 ,p_argument_value => p_user_row_id
608 );
609 --
610 --
611 open csr_user_row_id;
612 fetch csr_user_row_id into l_legcode, l_busgrpid , l_min_esd , l_max_eed;
613
614 if not (l_min_esd <= p_validation_start_date and l_max_eed >= p_validation_end_date ) then
615 close csr_user_row_id;
616 fnd_message.set_name('PAY', 'PAY_33174_PARENT_ID_INVALID');
617 fnd_message.set_token('PARENT' , 'User Row Id' );
618 fnd_message.raise_error;
619 end if;
620 close csr_user_row_id;
621 --
622 -- Confirm that the parent USER_ROW's startup mode is compatible
623 -- with this row.
624 --
625 if not pay_put_shd.chk_startup_mode_compatible
626 (p_parent_bgid => l_busgrpid
627 ,p_parent_legcode => l_legcode
628 ,p_child_bgid => p_business_group_id
629 ,p_child_legcode => p_legislation_code
630 ) then
631 fnd_message.set_name('PAY', 'PAY_33175_BGLEG_MISMATCH');
632 fnd_message.set_token('CHILD', 'User Value');
633 fnd_message.set_token('PARENT' , 'User Row');
634 fnd_message.raise_error;
635 end if;
636
637 hr_utility.set_location(' Leaving:'|| l_proc, 20);
638
639 exception
640 when app_exception.application_exception then
641 if hr_multi_message.exception_add
642 (p_associated_column1 => 'PAY_USER_COLUMN_INSTANCES_F.USER_ROW_ID'
643 ) then
644 raise;
645 end if;
646 when others then
647 if csr_user_row_id%isopen then
648 close csr_user_row_id;
649 end if;
650 raise;
651
652 end chk_user_row_id;
653 --
654 -- ----------------------------------------------------------------------------
655 -- |--------------------------< chk_user_column_id >--------------------------|
656 -- ----------------------------------------------------------------------------
657 --
658 -- Description:
659 -- Validates that the user_column_id exists in pay_user_columns
660 --
661 -- Pre-Requisites:
662 -- None
663 --
664 -- In Parameters:
665 -- p_user_table_id
666 -- p_legislation_code
667 -- p_business_group_id
668 --
669 -- Post Success:
670 -- Processing continues if the user_column_id is valid.
671 --
672 -- Post Failure:
673 -- An application error is raised and processing is terminated if
674 -- the user_column_id is invalid.
675 --
676 -- Developer/Implementation Notes:
677 -- None
678 --
679 -- Access Status:
680 -- Internal Row Handler Use Only
681 --
682 procedure chk_user_column_id
683 (p_user_column_id in number
684 ,p_legislation_code in varchar2
685 ,p_business_group_id in number
686 ) is
687 --
688 cursor csr_user_column_id is
689 select puc.legislation_code , puc.business_group_id
690 from pay_user_columns puc
691 where puc.user_column_id = p_user_column_id ;
692 --
693 l_busgrpid PAY_USER_COLUMNS.BUSINESS_GROUP_ID%TYPE;
694 l_legcode PAY_USER_COLUMNS.LEGISLATION_CODE%TYPE;
695
696 l_proc varchar2(100) := g_package || 'chk_user_column_id';
697 begin
698
699 hr_utility.set_location('Entering:'|| l_proc, 10);
700 --
701 -- USER_COLUMN_ID is mandatory.
702 --
703 hr_api.mandatory_arg_error
704 (p_api_name => l_proc
705 ,p_argument => 'USER_COLUMN_ID'
706 ,p_argument_value => p_user_column_id
707 );
708 --
709 open csr_user_column_id;
710 fetch csr_user_column_id into l_legcode, l_busgrpid ;
711
712 if csr_user_column_id%notfound then
713 close csr_user_column_id;
714 fnd_message.set_name('PAY', 'PAY_33174_PARENT_ID_INVALID');
715 fnd_message.set_token('PARENT' , 'User Column Id' );
716 fnd_message.raise_error;
717 end if;
718 close csr_user_column_id;
719 --
720 -- Confirm that the parent USER_COLUMN's startup mode is compatible
721 -- with this child row.
722 --
723 if not pay_put_shd.chk_startup_mode_compatible
724 (p_parent_bgid => l_busgrpid
725 ,p_parent_legcode => l_legcode
726 ,p_child_bgid => p_business_group_id
727 ,p_child_legcode => p_legislation_code
728 ) then
729 fnd_message.set_name('PAY', 'PAY_33175_BGLEG_MISMATCH');
730 fnd_message.set_token('CHILD', 'User Value');
731 fnd_message.set_token('PARENT' , 'User Column');
732 fnd_message.raise_error;
733 end if;
734
735 hr_utility.set_location(' Leaving:'|| l_proc, 20);
736
737 exception
738 when app_exception.application_exception then
739 if hr_multi_message.exception_add
740 (p_associated_column1 => 'PAY_USER_COLUMN_INSTANCES_F.USER_COLUMN_ID'
741 ) then
742 raise;
743 end if;
744 when others then
745 if csr_user_column_id%isopen then
746 close csr_user_column_id;
747 end if;
748 raise;
749
750 end chk_user_column_id;
751 --
752 -- ----------------------------------------------------------------------------
753 -- |------------------------< chk_row_column_id >-----------------------------|
754 -- ----------------------------------------------------------------------------
755 --
756 -- Description:
757 -- Validates that there may only one row in PAY_USER_COLUMN_INSTANCES_F
758 -- with the combination of USER_ROW_ID and USER_COLUMN_ID for the life time
759 -- of the row being insert for the specified and in a particular business
760 -- group or legislation.
761 --
762 -- Pre-Requisites:
763 -- User row id and User column id must be validated.
764 --
765 -- In Parameters:
766 -- p_user_row_id
767 -- p_user_column_id
768 -- p_legislation_code
769 -- p_business_group_id
770 -- p_validation_start_date
771 -- p_validation_end_date
772 --
773 -- Post Success:
774 -- Processing continues if the combination is valid.
775 --
776 -- Post Failure:
777 -- An application error is raised and processing is terminated if
778 -- the combination is invalid.
779 --
780 -- Developer/Implementation Notes:
781 -- None
782 --
783 -- Access Status:
784 -- Internal Row Handler Use Only
785 --
786 procedure chk_row_column_id
787 ( p_user_row_id in number
788 ,p_user_column_id in number
789 ,p_business_group_id in number
790 ,p_legislation_code in varchar2
791 ,p_validation_start_date in date
792 ,p_validation_end_date in date
793 ) is
794 --
795 cursor csr_row_column_id is
796 select null
797 from pay_user_column_instances_f uci
798 where uci.user_row_id = p_user_row_id
799 and uci.user_column_id = p_user_column_id
800 and ( p_business_group_id is null
801 or ( p_business_group_id is not null and p_business_group_id = uci.business_group_id )
802 or ( p_business_group_id is not null and
803 uci.legislation_code is null and uci.business_group_id is null )
804 or ( p_business_group_id is not null and
805 uci.legislation_code = hr_api.return_legislation_code(p_business_group_id )))
806 and ( p_legislation_code is null
807 or ( p_legislation_code is not null and p_legislation_code = uci.legislation_code )
808 or ( p_legislation_code is not null and
809 uci.legislation_code is null and uci.business_group_id is null)
810 or ( p_legislation_code is not null and
811 p_legislation_code = hr_api.return_legislation_code(uci.business_group_id )))
812 and (uci.effective_start_date <= p_validation_end_date and
813 uci.effective_end_date >= p_validation_start_date );
814
815 l_proc varchar2(100) := g_package || 'chk_row_column_id';
816 l_exists varchar2(1);
817 --
818 begin
819
820 hr_utility.set_location('Entering:'|| l_proc, 10);
821
822 if hr_multi_message.no_exclusive_error
823 (p_check_column1 => 'PAY_USER_COLUMN_INSTANCES_F.USER_ROW_ID'
824 ,p_check_column2 => 'PAY_USER_COLUMN_INSTANCES_F.USER_COLUMN_ID'
825 ,p_associated_column1 => 'PAY_USER_COLUMN_INSTANCES_F.USER_ROW_ID'
826 ,p_associated_column2 => 'PAY_USER_COLUMN_INSTANCES_F.USER_COLUMN_ID'
827 ) then
828 --
829 open csr_row_column_id;
830 fetch csr_row_column_id into l_exists;
831
832 if csr_row_column_id%found then
833 close csr_row_column_id;
834 fnd_message.set_name('PAY', 'PAY_7038_USERTAB_VALUE_UNIQUE');
835 fnd_message.raise_error;
836 end if;
837
838 close csr_row_column_id;
839
840 end if;
841 hr_utility.set_location(' Leaving:'|| l_proc, 20);
842
843 exception
844
845 when app_exception.application_exception then
846 if hr_multi_message.exception_add
847 (p_associated_column1 => 'PAY_USER_COLUMN_INSTANCES_F.USER_ROW_ID',
848 p_associated_column2 => 'PAY_USER_COLUMN_INSTANCES_F.USER_COLUMN_ID') then
849 raise;
850 end if;
851
852 when others then
853 if csr_row_column_id%isopen then
854 close csr_row_column_id ;
855 end if;
856 raise;
857
858 end chk_row_column_id ;
859 --
860 -- ----------------------------------------------------------------------------
861 -- |---------------------------< chk_value > ---------------------------------|
862 -- ----------------------------------------------------------------------------
863 --
864 -- Description:
865 -- If formula_id on the column identified by user_column_id is not null
866 -- then p_value is validated by calling the formula. This is done for
867 -- multiple date-track versions of the formula if applicable.
868 --
869 -- Pre-Requisites:
870 -- User Column Id must be validate
871 --
872 -- In Parameters:
873 -- p_user_column_instance_id
874 -- p_user_column_id
875 -- p_value
876 -- p_effective_date
877 -- p_object_version_number
878 -- p_business_group_id
879 -- p_validation_start_date
880 -- p_validation_end_date
881 --
882 -- Post Success:
883 -- Processing continues if the p_value is valid.
884 --
885 -- Post Failure:
886 -- An application error is raised and processing is terminated if
887 -- the p_value is invalid.
888 --
889 -- Developer/Implementation Notes:
890 -- None
891 --
892 -- Access Status:
893 -- Internal Row Handler Use Only
894 --
895 Procedure chk_value
896 (p_user_column_instance_id in number
897 ,p_user_column_id in number
898 ,p_value in varchar2
899 ,p_business_group_id in number
900 ,p_validation_start_date in date
901 ,p_validation_end_date in date
902 ,p_object_version_number in number
903 ,p_effective_date in date
904 ) is
905 --
906
907 l_formula_id PAY_USER_COLUMNS.FORMULA_ID%TYPE;
908 l_min_esd PAY_USER_COLUMN_INSTANCES_F.EFFECTIVE_START_DATE%TYPE;
909 l_max_eed PAY_USER_COLUMN_INSTANCES_F.EFFECTIVE_END_DATE%TYPE;
910
911 l_version_esd PAY_USER_COLUMN_INSTANCES_F.EFFECTIVE_START_DATE%TYPE;
912 l_version_eed PAY_USER_COLUMN_INSTANCES_F.EFFECTIVE_END_DATE%TYPE;
913
914 l_proc varchar2(72) := g_package||'chk_value';
915 l_formula_status varchar2(10);
916 l_formula_message fnd_new_messages.message_text%type;
917 l_inputs ff_exec.inputs_t;
918 l_outputs ff_exec.outputs_t;
919
920 cursor csr_formula_id is
921 select puc.formula_id
922 from pay_user_columns puc
923 where puc.user_column_id = p_user_column_id;
924
925 cursor csr_formula_exists is
926 select min(ff.effective_start_date) , max(ff.effective_end_date)
927 from ff_formulas_f ff
928 where ff.formula_id = l_formula_id ;
929
930 cursor csr_formula_versions is
931 select ff.effective_start_date , ff.effective_end_date
932 from ff_formulas_f ff
933 where ff.formula_id = l_formula_id
934 and ff.effective_start_date <= p_validation_end_date
935 and ff.effective_end_date >= p_validation_start_date ;
936
937 --
938
939 Begin
940
941 hr_utility.set_location(' Entering:'||l_proc, 10);
942 --
943
944 if hr_multi_message.no_exclusive_error
945 (p_check_column1 => 'PAY_USER_COLUMN_INSTANCES_F.USER_COLUMN_ID'
946 ,p_associated_column1 => 'PAY_USER_COLUMN_INSTANCES_F.VALUE'
947 ) and (
948 not pay_uci_shd.api_updating
949 (p_user_column_instance_id => p_user_column_instance_id
950 ,p_effective_date => p_effective_date
951 ,p_object_version_number => p_object_version_number
952 ) or
953 nvl(p_value, hr_api.g_varchar2) <>
954 nvl(pay_uci_shd.g_old_rec.value, hr_api.g_varchar2)
955 ) then
956 --
957 open csr_formula_id;
958 fetch csr_formula_id into l_formula_id;
959 close csr_formula_id;
960
961 if l_formula_id is not null then
962
963 open csr_formula_exists;
964 fetch csr_formula_exists into l_min_esd , l_max_eed ;
965 close csr_formula_exists;
966
967 if l_min_esd <= p_validation_start_date and l_max_eed >= p_validation_end_date then
968
969 open csr_formula_versions;
970
971 loop
972
973 fetch csr_formula_versions into l_version_esd , l_version_eed;
974 exit when csr_formula_versions%NOTFOUND;
975
976 ff_exec.init_formula(l_formula_id,
977 l_version_esd,
978 l_inputs,
979 l_outputs );
980
981 if l_inputs.count >= 1 then
982 -- Set up the inputs and contexts to formula.
983
984 for i in l_inputs.first..l_inputs.last loop
985
986 if l_inputs(i).name = 'BUSINESS_GROUP_ID' then
987 -- Set the business_group_id context.
988 l_inputs(i).value := p_business_group_id;
989 elsif l_inputs(i).name = 'ENTRY_VALUE' then
990 -- Set the input to the entry value to be validated.
991 l_inputs(i).value := p_value;
992 else
993 -- No context recognised.
994 close csr_formula_versions;
995 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
996 fnd_message.set_token('PROCEDURE', l_proc);
997 fnd_message.set_token('STEP','20');
998 fnd_message.raise_error;
999 end if;
1000 end loop;
1001 end if;
1002
1003 ff_exec.run_formula(l_inputs, l_outputs);
1004
1005 if l_outputs.count <> 2 then
1006 close csr_formula_versions;
1007 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1008 fnd_message.set_token('PROCEDURE', l_proc);
1009 fnd_message.set_token('STEP','30');
1010 fnd_message.raise_error;
1011 end if;
1012
1013 --
1014
1015 for i in l_outputs.first..l_outputs.last loop
1016
1017 if l_outputs(i).name = 'FORMULA_MESSAGE' then
1018 l_formula_message := l_outputs(i).value;
1019 elsif l_outputs(i).name = 'FORMULA_STATUS' then
1020 l_formula_status := upper(l_outputs(i).value);
1021 else
1022 close csr_formula_versions;
1023 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
1024 fnd_message.set_token('PROCEDURE', l_proc);
1025 fnd_message.set_token('STEP','40');
1026 fnd_message.raise_error;
1027
1028 End if;
1029 End loop;
1030
1031 If l_formula_status <> 'S' then
1032 close csr_formula_versions;
1033 if l_formula_message is null then
1034 -- User not defined an error message.
1035 --
1036 fnd_message.set_name('PAY','PAY_33180_INVALID_USER_VALUE');
1037 fnd_message.raise_error;
1038 Else
1039 -- User has defined message and so we can raise it.
1040 fnd_message.set_name( 'PAY' , 'HR_7998_ALL_EXEMACRO_MESSAGE') ;
1041 fnd_message.set_token( 'MESSAGE' , l_formula_message ) ;
1042 fnd_message.raise_error;
1043 End if;
1044 --
1045 End if;
1046 --
1047 end loop;
1048 close csr_formula_versions;
1049 else
1050 fnd_message.set_name('PAY','PAY_33181_UVAL_FF_NOT_FOUND');
1051 fnd_message.raise_error;
1052 end if;
1053 end if;
1054 end if;
1055 hr_utility.set_location(' Leaving:'||l_proc, 50);
1056
1057 Exception
1058 when app_exception.application_exception then
1059 if hr_multi_message.exception_add
1060 (p_associated_column1 => 'PAY_USER_COLUMN_INSTANCES_F.VALUE') then
1061 raise;
1062 end if;
1063
1064 when others then
1065 if csr_formula_id%isopen then
1066 close csr_formula_id ;
1067 end if;
1068 if csr_formula_exists%isopen then
1069 close csr_formula_exists ;
1070 end if;
1071 if csr_formula_versions%isopen then
1072 close csr_formula_versions ;
1073 end if;
1074 raise;
1075
1076 End chk_value;
1077 --
1078 -- ----------------------------------------------------------------------------
1079 -- |---------------------------< insert_validate >----------------------------|
1080 -- ----------------------------------------------------------------------------
1081 Procedure insert_validate
1082 (p_rec in pay_uci_shd.g_rec_type
1083 ,p_effective_date in date
1084 ,p_datetrack_mode in varchar2
1085 ,p_validation_start_date in date
1086 ,p_validation_end_date in date
1087 ) is
1088 --
1089 l_proc varchar2(72) := g_package||'insert_validate';
1090 --
1091 Begin
1092 hr_utility.set_location('Entering:'||l_proc, 5);
1093 --
1094 -- Call all supporting business operations
1095 --
1096 --
1097 chk_startup_action(true
1098 ,p_rec.business_group_id
1099 ,p_rec.legislation_code
1100 );
1101 IF hr_startup_data_api_support.g_startup_mode
1102 NOT IN ('GENERIC','STARTUP') THEN
1103 --
1104 -- Validate Important Attributes
1105 --
1106 hr_api.validate_bus_grp_id
1107 (p_business_group_id => p_rec.business_group_id
1108 ,p_associated_column1 => pay_uci_shd.g_tab_nam
1109 || '.BUSINESS_GROUP_ID');
1110 --
1111 -- after validating the set of important attributes,
1112 -- if Multiple Message Detection is enabled and at least
1113 -- one error has been found then abort further validation.
1114 --
1115 hr_multi_message.end_validation_set;
1116 END IF;
1117 --
1118
1119 if hr_startup_data_api_support.g_startup_mode not in ('GENERIC','USER') then
1120
1121 --
1122 -- Validate Important Attributes
1123 --
1124 chk_legislation_code(p_legislation_code => p_rec.legislation_code);
1125 --
1126 hr_multi_message.end_validation_set;
1127
1128 end if;
1129 --
1130 --
1131 -- Validate Dependent Attributes
1132 --
1133 --
1134
1135 chk_user_row_id
1136 (p_user_row_id => p_rec.user_row_id
1137 ,p_legislation_code => p_rec.legislation_code
1138 ,p_business_group_id => p_rec.business_group_id
1139 ,p_validation_start_date => p_validation_start_date
1140 ,p_validation_end_date => p_validation_end_date
1141 );
1142
1143 chk_user_column_id
1144 (p_user_column_id => p_rec.user_column_id
1145 ,p_legislation_code => p_rec.legislation_code
1146 ,p_business_group_id =>p_rec.business_group_id
1147 );
1148
1149 chk_row_column_id
1150 (p_user_row_id => p_rec.user_row_id
1151 ,p_user_column_id => p_rec.user_column_id
1152 ,p_business_group_id => p_rec.business_group_id
1153 ,p_legislation_code => p_rec.legislation_code
1154 ,p_validation_start_date => p_validation_start_date
1155 ,p_validation_end_date => p_validation_end_date
1156 );
1157
1158 chk_value
1159 (p_user_column_instance_id => p_rec.user_column_instance_id
1160 ,p_user_column_id => p_rec.user_column_id
1161 ,p_value => p_rec.value
1162 ,p_business_group_id => p_rec.business_group_id
1163 ,p_validation_start_date => p_validation_start_date
1164 ,p_validation_end_date => p_validation_end_date
1165 ,p_object_version_number => p_rec.object_version_number
1166 ,p_effective_date => p_effective_date
1167 );
1168
1169 hr_utility.set_location(' Leaving:'||l_proc, 10);
1170 End insert_validate;
1171 --
1172 -- ----------------------------------------------------------------------------
1173 -- |---------------------------< update_validate >----------------------------|
1174 -- ----------------------------------------------------------------------------
1175 Procedure update_validate
1176 (p_rec in pay_uci_shd.g_rec_type
1177 ,p_effective_date in date
1178 ,p_datetrack_mode in varchar2
1179 ,p_validation_start_date in date
1180 ,p_validation_end_date in date
1181 ) is
1182 --
1183 l_proc varchar2(72) := g_package||'update_validate';
1184 --
1185 Begin
1186 hr_utility.set_location('Entering:'||l_proc, 5);
1187 --
1188 -- Call all supporting business operations
1189 --
1190 --
1191 chk_startup_action(false
1192 ,p_rec.business_group_id
1193 ,p_rec.legislation_code
1194 );
1195 IF hr_startup_data_api_support.g_startup_mode
1196 NOT IN ('GENERIC','STARTUP') THEN
1197 --
1198 -- Validate Important Attributes
1199 --
1200 hr_api.validate_bus_grp_id
1201 (p_business_group_id => p_rec.business_group_id
1202 ,p_associated_column1 => pay_uci_shd.g_tab_nam
1203 || '.BUSINESS_GROUP_ID');
1204 --
1205 -- After validating the set of important attributes,
1206 -- if Multiple Message Detection is enabled and at least
1207 -- one error has been found then abort further validation.
1208 --
1209 hr_multi_message.end_validation_set;
1210 END IF;
1211 --
1212 --
1213 -- Validate Dependent Attributes
1214 --
1215 -- Call the datetrack update integrity operation
1216 --
1217 dt_update_validate
1218 (p_user_row_id => p_rec.user_row_id
1219 ,p_datetrack_mode => p_datetrack_mode
1220 ,p_validation_start_date => p_validation_start_date
1221 ,p_validation_end_date => p_validation_end_date
1222 );
1223 --
1224 chk_non_updateable_args
1225 (p_effective_date => p_effective_date
1226 ,p_rec => p_rec
1227 );
1228 --
1229
1230 chk_value
1231 (p_user_column_instance_id => p_rec.user_column_instance_id
1232 ,p_user_column_id => p_rec.user_column_id
1233 ,p_value => p_rec.value
1234 ,p_business_group_id => p_rec.business_group_id
1235 ,p_validation_start_date => p_validation_start_date
1236 ,p_validation_end_date => p_validation_end_date
1237 ,p_object_version_number => p_rec.object_version_number
1238 ,p_effective_date => p_effective_date
1239 );
1240 --
1241 hr_utility.set_location(' Leaving:'||l_proc, 10);
1242 End update_validate;
1243 --
1244 -- ----------------------------------------------------------------------------
1245 -- |---------------------------< delete_validate >----------------------------|
1246 -- ----------------------------------------------------------------------------
1247 Procedure delete_validate
1248 (p_rec in pay_uci_shd.g_rec_type
1249 ,p_effective_date in date
1250 ,p_datetrack_mode in varchar2
1251 ,p_validation_start_date in date
1252 ,p_validation_end_date in date
1253 ) is
1254 --
1255 l_proc varchar2(72) := g_package||'delete_validate';
1256 --
1257 Begin
1258 hr_utility.set_location('Entering:'||l_proc, 5);
1259 --
1260 --
1261 chk_startup_action(false
1262 ,pay_uci_shd.g_old_rec.business_group_id
1263 ,pay_uci_shd.g_old_rec.legislation_code
1264 );
1265 IF hr_startup_data_api_support.g_startup_mode
1266 NOT IN ('GENERIC','STARTUP') THEN
1267 --
1268 -- Validate Important Attributes
1269 --
1270 --
1271 -- After validating the set of important attributes,
1272 -- if Multiple Message Detection is enabled and at least
1273 -- one error has been found then abort further validation.
1274 --
1275 hr_multi_message.end_validation_set;
1276 END IF;
1277 --
1278 -- Call all supporting business operations
1279 --
1280 dt_delete_validate
1281 (p_datetrack_mode => p_datetrack_mode
1282 ,p_validation_start_date => p_validation_start_date
1283 ,p_validation_end_date => p_validation_end_date
1284 ,p_user_column_instance_id => p_rec.user_column_instance_id
1285 );
1286 --
1287 hr_utility.set_location(' Leaving:'||l_proc, 10);
1288 End delete_validate;
1289 --
1290 end pay_uci_bus;
[Home] [Help]