1 Package Body pay_ecu_bus as
2 /* $Header: pyecurhi.pkb 120.2 2006/02/06 05:37 pgongada noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ecu_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_element_class_usage_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_element_class_usage_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 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , pay_element_class_usages_f ecu
32 where ecu.element_class_usage_id = p_element_class_usage_id
33 and pbg.business_group_id (+) = ecu.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'element_class_usage_id'
50 ,p_argument_value => p_element_class_usage_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'ELEMENT_CLASS_USAGE_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
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_element_class_usage_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 from per_business_groups_perf pbg
101 , pay_element_class_usages_f ecu
102 where ecu.element_class_usage_id = p_element_class_usage_id
103 and pbg.business_group_id (+) = ecu.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'element_class_usage_id'
119 ,p_argument_value => p_element_class_usage_id
120 );
121 --
122 if ( nvl(pay_ecu_bus.g_element_class_usage_id, hr_api.g_number)
123 = p_element_class_usage_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := pay_ecu_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 pay_ecu_bus.g_element_class_usage_id := p_element_class_usage_id;
154 pay_ecu_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 -- ----------------------------------------------------------------------------
160 -- |---------------------------< chk_run_type_id >----------------------------|
161 -- ----------------------------------------------------------------------------
162 -- {Start Of Comments}
163 --
164 -- Description:
165 -- Checks the validity of the run_type_id enterend by carrying out
166 -- the following:
167 -- - check that the run_type_id exists
168 -- - check that the following rules apply:
169 --
170 -- Mode Run Type Available Components Resulting usage
171 -- ------ ----------- ------------------------------- ---------------
172 -- USER USER USER, STARTUP, GENERIC USER
173 -- USER STARTUP USER, STARTUP, GENERIC USER
174 -- USER GENERIC USER, STARTUP, GENERIC USER
175 -- STARTUP USER This mode cannot access USER Error
176 -- run types
177 -- STARTUP STARTUP STARTUP, GENERIC STARTUP
178 -- STARTUP GENERIC STARTUP, GENERIC STARTUP
179 -- GENERIC USER This mode cannot access USER Error
180 -- run types
181 -- GENERIC STARTUP This mode cannot access STARTUP Error
182 -- run types
183 -- GENERIC GENERIC GENERIC GENERIC
184 --
185 -- Pre Conditions:
186 -- g_old_rec has been populated with details of the values currently in
187 -- the database.
188 --
189 -- In Arguments:
190 -- p_rec has been populated with the updated values the user would like the
191 -- record set to.
192 --
193 -- Post Success:
194 --
195 --
196 -- Post Failure:
197 -- An application error is raised
198 --
199 -- {End Of Comments}
200 -- ----------------------------------------------------------------------------
201 Procedure chk_run_type_id
202 (p_effective_date in date
203 ,p_element_class_usage_id in number default null
204 ,p_run_type_id in number
205 ,p_business_group_id in number
206 ,p_legislation_code in varchar2
207 ) IS
208 --
209 CURSOR csr_chk_user_run_type(p_leg_code varchar2) is
210 select 'Y'
211 from pay_run_types_f prt
212 where prt.run_type_id = p_run_type_id
213 and p_effective_date between prt.effective_start_date
214 and prt.effective_end_date
215 and ((prt.business_group_id is not null
216 and prt.business_group_id = p_business_group_id)
217 or (prt.legislation_code is not null
218 and prt.legislation_code = p_leg_code)
219 or (prt.business_group_id is null
220 and prt.legislation_code is null));
221 --
222 CURSOR csr_chk_startup_run_type is
223 select 'Y'
224 from pay_run_types_f prt
225 where prt.run_type_id = p_run_type_id
226 and p_effective_date between prt.effective_start_date
227 and prt.effective_end_date
228 and prt.business_group_id is null
229 and ((p_legislation_code is not null
230 and prt.legislation_code = p_legislation_code)
231 or (prt.legislation_code is null));
232 --
233 CURSOR csr_chk_generic_run_type is
234 select 'Y'
235 from pay_run_types_f prt
236 where prt.run_type_id = p_run_type_id
237 and p_effective_date between prt.effective_start_date
238 and prt.effective_end_date
239 and prt.business_group_id is null
240 and prt.legislation_code is null;
241 --
242 --
243 l_exists varchar2(2);
244 l_legislation_code PAY_ELEMENT_CLASS_USAGES_F.legislation_code%TYPE;
245 l_proc varchar2(72) := g_package||'chk_run_type_id';
246 --
247 Begin
248 hr_api.mandatory_arg_error
249 (p_api_name => l_proc
250 ,p_argument => 'run_type_id'
251 ,p_argument_value => p_run_type_id
252 );
253
254 IF (p_run_type_id is not null) then
255 IF hr_startup_data_api_support.g_startup_mode = 'GENERIC' THEN
256 hr_utility.set_location(l_proc, 15);
257 OPEN csr_chk_generic_run_type;
258 FETCH csr_chk_generic_run_type INTO l_exists;
259 IF csr_chk_generic_run_type%NOTFOUND THEN
260 --
261 CLOSE csr_chk_generic_run_type;
262 hr_utility.set_message(801, 'HR_33587_INVALID_RT_FOR_MODE');
263 hr_utility.raise_error;
264 --
265 END IF;
266 CLOSE csr_chk_generic_run_type;
267 --
268 ELSIF hr_startup_data_api_support.g_startup_mode = 'STARTUP' THEN
269 hr_utility.set_location(l_proc, 20);
270 OPEN csr_chk_startup_run_type;
271 FETCH csr_chk_startup_run_type INTO l_exists;
272 IF csr_chk_startup_run_type%NOTFOUND THEN
273 --
274 CLOSE csr_chk_startup_run_type;
275 hr_utility.set_message(801, 'HR_33587_INVALID_RT_FOR_MODE');
276 hr_utility.raise_error;
277 END IF;
278 CLOSE csr_chk_startup_run_type;
279 --
280 ELSIF hr_startup_data_api_support.g_startup_mode = 'USER' THEN
281 hr_utility.set_location(l_proc, 25);
282 IF (p_element_class_usage_id is not null) then
283 l_legislation_code := return_legislation_code(p_element_class_usage_id);
284 ELSE
285 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
286 END IF;
287 OPEN csr_chk_user_run_type(l_legislation_code);
288 FETCH csr_chk_user_run_type INTO l_exists;
289 IF csr_chk_user_run_type%NOTFOUND THEN
290 --
291 CLOSE csr_chk_user_run_type;
292 hr_utility.set_message(801, 'PAY_33407_ECU_INVALID');
293 hr_utility.raise_error;
294 --
295 END IF;
296 CLOSE csr_chk_user_run_type;
297 END IF;
298 END IF;
299 --
300 exception
301 when app_exception.application_exception then
302 if hr_multi_message.exception_add
303 (p_associated_column1 =>
304 'PAY_ELEMENT_CLASS_USAGES_F.RUN_TYPE_ID') then
305 raise;
306 end if;
307 when others then
308 if csr_chk_generic_run_type%isopen then
309 close csr_chk_generic_run_type;
310 end if;
311 if csr_chk_startup_run_type%isopen then
312 close csr_chk_startup_run_type;
313 end if;
314 if csr_chk_user_run_type%isopen then
315 close csr_chk_user_run_type;
316 end if;
317 raise;
318 --
319 End chk_run_type_id;
320 --
321 -- ----------------------------------------------------------------------------
322 -- |------------------------< chk_classification_id >-------------------------|
323 -- ----------------------------------------------------------------------------
324 -- {Start Of Comments}
325 --
326 -- Description:
327 -- Checks the validity of the classification_id enterend by carrying out
328 -- the following:
329 -- - check that the classification_id exists
330 -- - check that the following rules apply:
331 --
332 -- Mode Run Type Available Components Resulting usage
333 -- ------ ----------- ------------------------------- ---------------
334 -- USER USER USER, STARTUP, GENERIC USER
335 -- USER STARTUP USER, STARTUP, GENERIC USER
336 -- USER GENERIC USER, STARTUP, GENERIC USER
337 -- STARTUP USER This mode cannot access USER Error
338 -- classifications
339 -- STARTUP STARTUP STARTUP, GENERIC STARTUP
340 -- STARTUP GENERIC STARTUP, GENERIC STARTUP
341 -- GENERIC USER This mode cannot access USER Error
342 -- classifications
343 -- GENERIC STARTUP This mode cannot access STARTUP Error
344 -- classifications
345 -- GENERIC GENERIC GENERIC GENERIC
346 --
347 -- Pre Conditions:
348 -- g_old_rec has been populated with details of the values currently in
349 -- the database.
350 --
351 -- In Arguments:
352 -- p_rec has been populated with the updated values the user would like the
353 -- record set to.
354 --
355 -- Post Success:
356 --
357 --
358 -- Post Failure:
359 -- An application error is raised
360 --
361 -- {End Of Comments}
362 -- ----------------------------------------------------------------------------
363 Procedure chk_classification_id
364 (p_effective_date in date
365 ,p_element_class_usage_id in number default null
366 ,p_classification_id in number
367 ,p_business_group_id in number
368 ,p_legislation_code in varchar2
369 ) IS
370 --
371 CURSOR csr_chk_user_ele_class(p_leg_code varchar2) is
372 select 'Y'
373 from pay_element_classifications ecl
374 where ecl.classification_id = p_classification_id
375 and ((ecl.business_group_id is not null
376 and ecl.business_group_id = p_business_group_id)
377 or (ecl.legislation_code is not null
378 and ecl.legislation_code = p_leg_code)
379 or (ecl.business_group_id is null
380 and ecl.legislation_code is null));
381 --
382 CURSOR csr_chk_startup_ele_class is
383 select 'Y'
384 from pay_element_classifications ecl
385 where ecl.classification_id = p_classification_id
386 and ecl.business_group_id is null
387 and ((p_legislation_code is not null
388 and ecl.legislation_code = p_legislation_code)
389 or (ecl.legislation_code is null));
390 --
391 CURSOR csr_chk_generic_ele_class is
392 select 'Y'
393 from pay_element_classifications ecl
394 where ecl.classification_id = p_classification_id
395 and ecl.business_group_id is null
396 and ecl.legislation_code is null;
397 --
398
399
400 l_exists varchar2(2);
401 l_legislation_code PAY_ELEMENT_CLASS_USAGES_F.legislation_code%TYPE;
402 l_proc varchar2(72) := g_package||'chk_classification_id';
403 --
404 Begin
405 hr_api.mandatory_arg_error
406 (p_api_name => l_proc
407 ,p_argument => 'classification_id'
408 ,p_argument_value => p_classification_id
409 );
410 IF (p_classification_id is not null) then
411 IF hr_startup_data_api_support.g_startup_mode = 'GENERIC' THEN
412 hr_utility.set_location(l_proc, 15);
413 OPEN csr_chk_generic_ele_class;
414 FETCH csr_chk_generic_ele_class INTO l_exists;
415 IF csr_chk_generic_ele_class%NOTFOUND THEN
419 hr_utility.raise_error;
416 --
417 CLOSE csr_chk_generic_ele_class;
418 hr_utility.set_message(801, 'PAY_33408_INV_ELE_CLS_FOR_MODE');
420 --
421 END IF;
422 CLOSE csr_chk_generic_ele_class;
423 --
424 ELSIF hr_startup_data_api_support.g_startup_mode = 'STARTUP' THEN
425 hr_utility.set_location(l_proc, 20);
426 OPEN csr_chk_startup_ele_class;
427 FETCH csr_chk_startup_ele_class INTO l_exists;
428 IF csr_chk_startup_ele_class%NOTFOUND THEN
429 --
430 CLOSE csr_chk_startup_ele_class;
431 hr_utility.set_message(801, 'PAY_33408_INV_ELE_CLS_FOR_MODE');
432 hr_utility.raise_error;
433 END IF;
434 CLOSE csr_chk_startup_ele_class;
435 --
436 ELSIF hr_startup_data_api_support.g_startup_mode = 'USER' THEN
437 hr_utility.set_location(l_proc, 25);
438 IF (p_element_class_usage_id is not null) then
439 l_legislation_code := return_legislation_code(p_element_class_usage_id);
440 ELSE
441 l_legislation_code := hr_api.return_legislation_code(p_business_group_id);
442 END IF;
443 OPEN csr_chk_user_ele_class(l_legislation_code);
444 FETCH csr_chk_user_ele_class INTO l_exists;
445 IF csr_chk_user_ele_class%NOTFOUND THEN
446 --
447 CLOSE csr_chk_user_ele_class;
448 hr_utility.set_message(801, 'PAY_33407_ECU_INVALID');
449 hr_utility.raise_error;
450 --
451 END IF;
452 CLOSE csr_chk_user_ele_class;
453 END IF;
454 END IF;
455 --
456 exception
457 when app_exception.application_exception then
458 if hr_multi_message.exception_add
459 (p_associated_column1 =>
460 'PAY_ELEMENT_CLASS_USAGES_F.CLASSIFICATION_ID') then
461 raise;
462 end if;
463 when others then
464 if csr_chk_generic_ele_class%isopen then
465 close csr_chk_generic_ele_class;
466 end if;
467 if csr_chk_startup_ele_class%isopen then
468 close csr_chk_startup_ele_class;
469 end if;
470 if csr_chk_user_ele_class%isopen then
471 close csr_chk_user_ele_class;
472 end if;
473 raise;
474 End chk_classification_id;
475 --
476 --
477 -- ----------------------------------------------------------------------------
478 -- |--------------------------< chk_legislation_code>-------------------------|
479 -- ----------------------------------------------------------------------------
480 --
481 -- Description:
482 -- Validates that the legislation code exists in fnd_territories
483 --
484 -- Pre-Requisites:
485 -- None
486 --
487 -- In Parameters:
488 -- p_legislation_code
489 --
490 -- Post Success:
491 -- Processing continues if the legislation_code is valid.
492 --
493 -- Post Failure:
494 -- An application error is raised and processing is terminated if
495 -- the legislation_code is invalid.
496 --
497 -- Developer/Implementation Notes:
498 -- None
499 --
500 -- Access Status:
501 -- Internal Row Handler Use Only
502 --
503 procedure chk_legislation_code
504 ( p_legislation_code in varchar2 )
505 is
506 --
507 cursor csr_legislation_code is
508 select null
509 from fnd_territories
510 where territory_code = p_legislation_code ;
511 --
512 l_exists varchar2(1);
513 l_proc varchar2(100) := g_package || 'chk_legislation_code';
514 begin
515 --
516 hr_utility.set_location('Entering:'|| l_proc, 10);
517
518 open csr_legislation_code;
519 fetch csr_legislation_code into l_exists ;
520
521 if csr_legislation_code%notfound then
522 close csr_legislation_code;
523 fnd_message.set_name('PAY', 'PAY_33177_LEG_CODE_INVALID');
524 fnd_message.raise_error;
525 end if;
526 close csr_legislation_code;
527
528 hr_utility.set_location(' Leaving:'|| l_proc, 20);
529 --
530 exception
531 when app_exception.application_exception then
532 if hr_multi_message.exception_add
533 (p_associated_column1 => 'PAY_ELEMENT_CLASS_USAGES_F.LEGISLATION_CODE'
534 ) then
535 raise;
536 end if;
537 when others then
538 if csr_legislation_code%isopen then
539 close csr_legislation_code;
540 end if;
541 raise;
542 end chk_legislation_code;
543 --
544 --
545 -- ----------------------------------------------------------------------------
546 -- |-----------------------< chk_non_updateable_args >------------------------|
547 -- ----------------------------------------------------------------------------
548 -- {Start Of Comments}
549 --
550 -- Description:
551 -- This procedure is used to ensure that non updateable attributes have
552 -- not been updated. If an attribute has been updated an error is generated.
553 --
554 -- Pre Conditions:
555 -- g_old_rec has been populated with details of the values currently in
556 -- the database.
557 --
558 -- In Arguments:
559 -- p_rec has been populated with the updated values the user would like the
560 -- record set to.
561 --
562 -- Post Success:
563 -- Processing continues if all the non updateable attributes have not
564 -- changed.
565 --
566 -- Post Failure:
567 -- An application error is raised if any of the non updatable attributes
568 -- have been altered.
569 --
570 -- {End Of Comments}
571 -- ----------------------------------------------------------------------------
572 Procedure chk_non_updateable_args
573 (p_effective_date in date
574 ,p_rec in pay_ecu_shd.g_rec_type
575 ) IS
576 --
577 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
578 --
579 Begin
580 --
584 hr_utility.set_location('Entering : '||l_proc, 5);
581 -- Only proceed with the validation if a row exists for the current
582 -- record in the HR Schema.
583 --
585 IF NOT pay_ecu_shd.api_updating
586 (p_element_class_usage_id => p_rec.element_class_usage_id
587 ,p_effective_date => p_effective_date
588 ,p_object_version_number => p_rec.object_version_number
589 ) THEN
590 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
591 fnd_message.set_token('PROCEDURE ', l_proc);
592 fnd_message.set_token('STEP ', '5');
593 fnd_message.raise_error;
594 END IF;
595 --
596 -- EDIT_HERE: Add checks to ensure non-updateable args have
597 -- not been updated.
598 --
599 if nvl(p_rec.business_group_id, hr_api.g_number) <>
600 nvl(pay_ecu_shd.g_old_rec.business_group_id, hr_api.g_number) then
601 hr_api.argument_changed_error
602 (p_api_name => l_proc
603 ,p_argument => 'BUSINESS_GROUP_ID'
604 ,p_base_table => pay_ecu_shd.g_tab_nam
605 );
606 end if;
607 --
608 if nvl(p_rec.legislation_code, hr_api.g_varchar2) <>
609 nvl(pay_ecu_shd.g_old_rec.legislation_code, hr_api.g_varchar2) then
610 hr_api.argument_changed_error
611 (p_api_name => l_proc
612 ,p_argument => 'LEGISLATION_CODE'
613 ,p_base_table => pay_ecu_shd.g_tab_nam
614 );
615 end if;
616 hr_utility.set_location('Leaving : '||l_proc, 100);
617
618 End chk_non_updateable_args;
619 --
620 -- ----------------------------------------------------------------------------
621 -- |--------------------------< dt_update_validate >--------------------------|
622 -- ----------------------------------------------------------------------------
623 -- {Start Of Comments}
624 --
625 -- Description:
626 -- This procedure is used for referential integrity of datetracked
627 -- parent entities when a datetrack update operation is taking place
628 -- and where there is no cascading of update defined for this entity.
629 --
630 -- Prerequisites:
631 -- This procedure is called from the update_validate.
632 --
633 -- In Parameters:
634 --
635 -- Post Success:
636 -- Processing continues.
637 --
638 -- Post Failure:
639 --
640 -- Developer Implementation Notes:
641 -- This procedure should not need maintenance unless the HR Schema model
642 -- changes.
643 --
644 -- Access Status:
645 -- Internal Row Handler Use Only.
646 --
647 -- {End Of Comments}
648 -- ----------------------------------------------------------------------------
649 Procedure dt_update_validate
650 (p_datetrack_mode in varchar2
651 ,p_validation_start_date in date
652 ,p_validation_end_date in date
653 ) Is
654 --
655 l_proc varchar2(72) := g_package||'dt_update_validate';
656 --
657 Begin
658 --
659 -- Ensure that the p_datetrack_mode argument is not null
660 --
661 hr_api.mandatory_arg_error
662 (p_api_name => l_proc
663 ,p_argument => 'datetrack_mode'
664 ,p_argument_value => p_datetrack_mode
665 );
666 --
667 -- Mode will be valid, as this is checked at the start of the upd.
668 --
669 -- Ensure the arguments are not null
670 --
671 hr_api.mandatory_arg_error
672 (p_api_name => l_proc
673 ,p_argument => 'validation_start_date'
674 ,p_argument_value => p_validation_start_date
675 );
676 --
677 hr_api.mandatory_arg_error
678 (p_api_name => l_proc
679 ,p_argument => 'validation_end_date'
680 ,p_argument_value => p_validation_end_date
681 );
682 --
683 --
684 --
685 Exception
686 When Others Then
687 --
688 -- An unhandled or unexpected error has occurred which
689 -- we must report
690 --
691 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
692 fnd_message.set_token('PROCEDURE', l_proc);
693 fnd_message.set_token('STEP','15');
694 fnd_message.raise_error;
695 End dt_update_validate;
696 --
697 -- ----------------------------------------------------------------------------
698 -- |--------------------------< dt_delete_validate >--------------------------|
699 -- ----------------------------------------------------------------------------
700 -- {Start Of Comments}
701 --
702 -- Description:
703 -- This procedure is used for referential integrity of datetracked
704 -- child entities when either a datetrack DELETE or ZAP is in operation
705 -- and where there is no cascading of delete defined for this entity.
706 -- For the datetrack mode of DELETE or ZAP we must ensure that no
707 -- datetracked child rows exist between the validation start and end
708 -- dates.
709 --
710 -- Prerequisites:
711 -- This procedure is called from the delete_validate.
712 --
713 -- In Parameters:
714 --
715 -- Post Success:
716 -- Processing continues.
717 --
718 -- Post Failure:
719 -- If a row exists by determining the returning Boolean value from the
720 -- generic dt_api.rows_exist function then we must supply an error via
721 -- the use of the local exception handler l_rows_exist.
722 --
723 -- Developer Implementation Notes:
724 -- This procedure should not need maintenance unless the HR Schema model
725 -- changes.
726 --
727 -- Access Status:
728 -- Internal Row Handler Use Only.
729 --
730 -- {End Of Comments}
731 -- ----------------------------------------------------------------------------
732 Procedure dt_delete_validate
736 ,p_validation_end_date in date
733 (p_element_class_usage_id in number
734 ,p_datetrack_mode in varchar2
735 ,p_validation_start_date in date
737 ) Is
738 --
739 l_proc varchar2(72) := g_package||'dt_delete_validate';
740 --
741 Begin
742 --
743 -- Ensure that the p_datetrack_mode argument is not null
744 --
745 hr_api.mandatory_arg_error
746 (p_api_name => l_proc
747 ,p_argument => 'datetrack_mode'
748 ,p_argument_value => p_datetrack_mode
749 );
750 --
751 -- Only perform the validation if the datetrack mode is either
752 -- DELETE or ZAP
753 --
754 If (p_datetrack_mode = hr_api.g_delete or
755 p_datetrack_mode = hr_api.g_zap) then
756 --
757 --
758 -- Ensure the arguments are not null
759 --
760 hr_api.mandatory_arg_error
761 (p_api_name => l_proc
762 ,p_argument => 'validation_start_date'
763 ,p_argument_value => p_validation_start_date
764 );
765 --
766 hr_api.mandatory_arg_error
767 (p_api_name => l_proc
768 ,p_argument => 'validation_end_date'
769 ,p_argument_value => p_validation_end_date
770 );
771 --
772 hr_api.mandatory_arg_error
773 (p_api_name => l_proc
774 ,p_argument => 'element_class_usage_id'
775 ,p_argument_value => p_element_class_usage_id
776 );
777 --
778 --
779 --
780 End If;
781 --
782 Exception
783 When Others Then
784 --
785 -- An unhandled or unexpected error has occurred which
786 -- we must report
787 --
788 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
789 fnd_message.set_token('PROCEDURE', l_proc);
790 fnd_message.set_token('STEP','15');
791 fnd_message.raise_error;
792 --
793 End dt_delete_validate;
794 --
795 -- ----------------------------------------------------------------------------
796 -- |----------------------< chk_startup_action >------------------------------|
797 -- ----------------------------------------------------------------------------
798 --
799 -- Description:
800 -- This procedure will check that the current action is allowed according
801 -- to the current startup mode.
802 --
803 -- ----------------------------------------------------------------------------
804 PROCEDURE chk_startup_action
805 (p_insert IN boolean
806 ,p_business_group_id IN number
807 ,p_legislation_code IN varchar2
808 ,p_legislation_subgroup IN varchar2 DEFAULT NULL) IS
809 --
810 BEGIN
811 --
812 -- Call the supporting procedure to check startup mode
813 -- EDIT_HERE: The following call should be edited if certain types of rows
814 -- are not permitted.
815 IF (p_insert) THEN
816 if p_business_group_id is not null and p_legislation_code is not null then
817 fnd_message.set_name('PAY', 'PAY_33179_BGLEG_INVALID');
818 fnd_message.raise_error;
819 end if;
820 hr_startup_data_api_support.chk_startup_action
821 (p_generic_allowed => TRUE
822 ,p_startup_allowed => TRUE
823 ,p_user_allowed => TRUE
824 ,p_business_group_id => p_business_group_id
825 ,p_legislation_code => p_legislation_code
826 ,p_legislation_subgroup => p_legislation_subgroup
827 );
828 ELSE
829 hr_startup_data_api_support.chk_upd_del_startup_action
830 (p_generic_allowed => TRUE
831 ,p_startup_allowed => TRUE
832 ,p_user_allowed => TRUE
833 ,p_business_group_id => p_business_group_id
834 ,p_legislation_code => p_legislation_code
835 ,p_legislation_subgroup => p_legislation_subgroup
836 );
837 END IF;
838 --
839 END chk_startup_action;
840 --
841 -- ----------------------------------------------------------------------------
842 -- |---------------------------< insert_validate >----------------------------|
843 -- ----------------------------------------------------------------------------
844 Procedure insert_validate
845 (p_rec in pay_ecu_shd.g_rec_type
846 ,p_effective_date in date
847 ,p_datetrack_mode in varchar2
848 ,p_validation_start_date in date
849 ,p_validation_end_date in date
850 ) is
851 --
852 l_proc varchar2(72) := g_package||'insert_validate';
853 l_mode varchar2(10);
854 --
855 Begin
856 hr_utility.set_location('Entering:'||l_proc, 5);
857 --
858 -- Call all supporting business operations
859 --
860 --
861 chk_startup_action(true
862 ,p_rec.business_group_id
863 ,p_rec.legislation_code
864 );
865 l_mode := hr_startup_data_api_support.g_startup_mode;
866 IF l_mode NOT IN ('GENERIC','STARTUP') THEN
867 --
868 -- Validate Important Attributes
869 --
870 If (p_rec.business_group_id is not null) then
871 --
872 hr_api.validate_bus_grp_id
873 (p_business_group_id => p_rec.business_group_id
874 ,p_associated_column1 => pay_ecu_shd.g_tab_nam
875 || '.BUSINESS_GROUP_ID');
876 --
877 end if;
878 -- after validating the set of important attributes,
879 -- if Multiple Message Detection is enabled and at least
880 -- one error has been found then abort further validation.
881 --
882 hr_multi_message.end_validation_set;
883 END IF;
884 --
885 --
886 -- Validate Dependent Attributes
887 hr_utility.set_location('chk_run_type : '||l_proc,10);
891 --
888 if(p_rec.legislation_code is not null) then
889 chk_legislation_code(p_legislation_code => p_rec.legislation_code);
890 end if;
892 hr_utility.set_location('chk_classification_id : '||l_proc, 15);
893 chk_classification_id(p_effective_date => p_effective_date
894 ,p_element_class_usage_id => p_rec.element_class_usage_id
895 ,p_classification_id => p_rec.classification_id
896 ,p_business_group_id => p_rec.business_group_id
897 ,p_legislation_code => p_rec.legislation_code);
898
899
900 hr_utility.set_location('chk_run_type : '||l_proc, 20);
901 chk_run_type_id(p_effective_date => p_effective_date
902 ,p_element_class_usage_id => p_rec.element_class_usage_id
903 ,p_run_type_id => p_rec.run_type_id
904 ,p_business_group_id => p_rec.business_group_id
905 ,p_legislation_code => p_rec.legislation_code);
906
907 --
908 hr_utility.set_location('chk_classification_id : '|| l_proc, 20);
909
910 --
911 hr_utility.set_location(' Leaving:'||l_proc, 100);
912 End insert_validate;
913 --
914 -- ----------------------------------------------------------------------------
915 -- |---------------------------< update_validate >----------------------------|
916 -- ----------------------------------------------------------------------------
917 Procedure update_validate
918 (p_rec in pay_ecu_shd.g_rec_type
919 ,p_effective_date in date
920 ,p_datetrack_mode in varchar2
921 ,p_validation_start_date in date
922 ,p_validation_end_date in date
923 ) is
924 --
925 l_proc varchar2(72) := g_package||'update_validate';
926 l_mode varchar2(10);
927 --
928 Begin
929 hr_utility.set_location('Entering:'||l_proc, 5);
930 --
931 -- Call all supporting business operations
932 --
933 --
934 chk_startup_action(false
935 ,p_rec.business_group_id
936 ,p_rec.legislation_code
937 );
938 l_mode := hr_startup_data_api_support.g_startup_mode;
939 IF l_mode NOT IN ('GENERIC','STARTUP') THEN
940 --
941 -- Validate Important Attributes
942 --
943 If (p_rec.business_group_id is not null) then
944 --
945 hr_api.validate_bus_grp_id
946 (p_business_group_id => p_rec.business_group_id
947 ,p_associated_column1 => pay_ecu_shd.g_tab_nam
948 || '.BUSINESS_GROUP_ID');
949 --
950 end if;
951 -- After validating the set of important attributes,
952 -- if Multiple Message Detection is enabled and at least
953 -- one error has been found then abort further validation.
954 --
955 hr_multi_message.end_validation_set;
956 END IF;
957 --
958 --
959 -- Validate Dependent Attributes
960 --
961 -- Call the datetrack update integrity operation
962 --
963 dt_update_validate
964 (p_datetrack_mode => p_datetrack_mode
965 ,p_validation_start_date => p_validation_start_date
966 ,p_validation_end_date => p_validation_end_date
967 );
968 --
969 chk_non_updateable_args
970 (p_effective_date => p_effective_date
971 ,p_rec => p_rec
972 );
973 --
974 hr_utility.set_location('chk_run_type : '||l_proc,10);
975 if(p_rec.legislation_code is not null) then
976 chk_legislation_code(p_legislation_code => p_rec.legislation_code);
977 end if;
978 --
979 hr_utility.set_location('chk_run_type : '||l_proc,15);
980 chk_run_type_id(p_effective_date => p_effective_date
981 ,p_element_class_usage_id => p_rec.element_class_usage_id
982 ,p_run_type_id => p_rec.run_type_id
983 ,p_business_group_id => p_rec.business_group_id
984 ,p_legislation_code => p_rec.legislation_code);
985
986 --
987 hr_utility.set_location('chk_classification_id : '|| l_proc, 20);
988 chk_classification_id(p_effective_date => p_effective_date
989 ,p_element_class_usage_id => p_rec.element_class_usage_id
990 ,p_classification_id => p_rec.classification_id
991 ,p_business_group_id => p_rec.business_group_id
992 ,p_legislation_code => p_rec.legislation_code);
993
994 --
995 --
996 hr_utility.set_location(' Leaving:'||l_proc, 10);
997 End update_validate;
998 --
999 -- ----------------------------------------------------------------------------
1000 -- |---------------------------< delete_validate >----------------------------|
1001 -- ----------------------------------------------------------------------------
1002 Procedure delete_validate
1003 (p_rec in pay_ecu_shd.g_rec_type
1004 ,p_effective_date in date
1005 ,p_datetrack_mode in varchar2
1006 ,p_validation_start_date in date
1007 ,p_validation_end_date in date
1008 ) is
1009 --
1010 l_proc varchar2(72) := g_package||'delete_validate';
1011 --
1012 Begin
1013 hr_utility.set_location('Entering:'||l_proc, 5);
1014 --
1015 --
1016 chk_startup_action(false
1017 ,pay_ecu_shd.g_old_rec.business_group_id
1018 ,pay_ecu_shd.g_old_rec.legislation_code
1019 );
1020 IF hr_startup_data_api_support.g_startup_mode
1021 NOT IN ('GENERIC','STARTUP') THEN
1022 --
1023 -- Validate Important Attributes
1024 --
1025 --
1026 -- After validating the set of important attributes,
1027 -- if Multiple Message Detection is enabled and at least
1028 -- one error has been found then abort further validation.
1029 --
1030 hr_multi_message.end_validation_set;
1031 END IF;
1032 --
1036 (p_datetrack_mode => p_datetrack_mode
1033 -- Call all supporting business operations
1034 --
1035 dt_delete_validate
1037 ,p_validation_start_date => p_validation_start_date
1038 ,p_validation_end_date => p_validation_end_date
1039 ,p_element_class_usage_id => p_rec.element_class_usage_id
1040 );
1041 --
1042 hr_utility.set_location(' Leaving:'||l_proc, 10);
1043 End delete_validate;
1044 --
1045 end pay_ecu_bus;