[Home] [Help]
PACKAGE BODY: APPS.PAY_CFT_BUS
Source
1 Package Body pay_cft_bus as
2 /* $Header: pycatrhi.pkb 120.1 2005/10/05 06:44:36 saurgupt noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_cft_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_emp_fed_tax_inf_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- emp_fed_tax_inf_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_emp_fed_tax_inf_id(p_emp_fed_tax_inf_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_emp_fed_tax_inf_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := pay_cft_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_emp_fed_tax_inf_id => p_emp_fed_tax_inf_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_emp_fed_tax_inf_id,hr_api.g_number)
55 <> pay_cft_shd.g_old_rec.emp_fed_tax_inf_id) then
56 --
57 -- raise error as PK has changed
58 --
59 pay_cft_shd.constraint_error('PAY_CA_EMP_FED_TAX_RULES_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_emp_fed_tax_inf_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 pay_cft_shd.constraint_error('PAY_CA_EMP_FED_TAX_RULES_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_emp_fed_tax_inf_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------< chk_legislation_code >------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the lookup value is valid.
85 --
86 -- Pre Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- emp_fed_tax_inf_id PK of record being inserted or updated.
91 -- legislation_code Value must be 'CA'.
92 -- effective_date effective date
93 -- object_version_number Object version number of record being
94 -- inserted or updated.
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error handled by procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_legislation_code(p_emp_fed_tax_inf_id in number,
106 p_legislation_code in varchar2,
107 p_effective_date in date,
108 p_object_version_number in number) is
109 --
110 l_proc varchar2(72) := g_package||'chk_legislation_code';
111 l_api_updating boolean;
112 --
113 Begin
114 --
115 hr_utility.set_location('Entering:'||l_proc, 5);
116 --
117 l_api_updating := pay_cft_shd.api_updating
118 (p_emp_fed_tax_inf_id => p_emp_fed_tax_inf_id,
119 p_effective_date => p_effective_date,
120 p_object_version_number => p_object_version_number);
121 --
122 if l_api_updating
123 and p_legislation_code
124 <> nvl(pay_cft_shd.g_old_rec.legislation_code,hr_api.g_varchar2)
125 or not l_api_updating then
126 ---
127 if p_legislation_code is null then
128 -- raise error as legislation_code is NULL
129 --
130 hr_utility.set_message(800, 'HR_74028_WRONG_LEGIS_CODE');
131 hr_utility.raise_error;
132 --
133 end if;
134 --
135 -- check if value of lookup falls within lookup type.
136 --
137 if p_legislation_code <> 'CA' then
138 -- raise error as legislation_code is not CA
139 --
140 hr_utility.set_message(800, 'HR_74028_WRONG_LEGIS_CODE');
141 hr_utility.raise_error;
142 --
143 end if;
144 --
145 end if;
146 --
147 hr_utility.set_location('Leaving:'||l_proc,10);
148 --
149 end chk_legislation_code;
150 --
151 -- ----------------------------------------------------------------------------
152 -- |------< chk_assignment_id >------|
153 -- ----------------------------------------------------------------------------
154 Procedure chk_assignment_id
155 (p_emp_fed_tax_inf_id in number
156 ,p_assignment_id in
157 pay_ca_emp_prov_tax_info_f.assignment_id%TYPE
158 ,p_business_group_id in
159 pay_ca_emp_prov_tax_info_f.business_group_id%TYPE
160 ,p_effective_date in date
161 ,p_object_version_number in number
162 ) is
163 --
164 l_proc varchar2(72) := g_package||'chk_assignment_id';
165 l_dummy varchar2(1);
166 l_api_updating boolean;
167 l_business_group_id per_assignments_f.business_group_id%TYPE;
168 l_location_id per_assignments_f.location_id%TYPE;
169 l_country varchar2(30);
170 --
171 cursor c1 is
172 select business_group_id,location_id
173 from per_assignments_f asg
174 where asg.assignment_id = p_assignment_id
175 and p_effective_date between asg.effective_start_date
176 and asg.effective_end_date;
177 --
178 cursor csr_get_location(p_location_id number) is
179 select hrl.country from hr_locations hrl
180 where hrl.location_id = p_location_id;
181 --
182 Begin
183 --
184 hr_utility.set_location('Entering:'||l_proc,5);
185 --
186 -- Check that the mandatory parameters have been set
187 --
188 --dbms_output.put_line('in assignment_id ');
189 if p_assignment_id is null then
190 hr_utility.set_message(800, 'HR_74023_ASSIGNMENT_ID_NULL');
191 hr_utility.raise_error;
192 end if;
193 --
194 --dbms_output.put_line('in assignment_id 2');
195 if p_business_group_id is null then
196 hr_utility.set_message(800, 'HR_74024_BUSINESS_GROUP_ID_NULL');
197 hr_utility.raise_error;
198 end if;
199 --
200 --dbms_output.put_line('in assignment_id 3');
201 hr_api.mandatory_arg_error
202 (p_api_name => l_proc
203 ,p_argument => 'effective_date'
204 ,p_argument_value => p_effective_date
205 );
206 --
207 --dbms_output.put_line('in assignment_id 4');
208 l_api_updating := pay_cft_shd.api_updating
209 (p_emp_fed_tax_inf_id => p_emp_fed_tax_inf_id,
210 p_effective_date => p_effective_date,
211 p_object_version_number => p_object_version_number);
212 --
213 -- Since assignment_id cannot be updated, the case of
214 -- l_api_updating = TRUE is not considered
215 --
216 if (l_api_updating ) then
217 if p_assignment_id <> pay_cft_shd.g_old_rec.assignment_id then
218 --dbms_output.put_line('cannot change assignment_id');
219 hr_utility.set_message(800, 'HR_74027_ASSIGNMENT_ID_CHANGED');
220 hr_utility.raise_error;
221 end if;
222 end if;
223
224 -- if (not l_api_updating) then
225 --
226 open c1;
227 --
228 --dbms_output.put_line('in assignment_id 5');
229 fetch c1 into l_business_group_id,l_location_id;
230 if c1%notfound then
231 --
232 close c1;
233 --
234 -- raise error as assignment_id not found in per_assignments_f
235 -- table.
236 --
237 --dbms_output.put_line('in assignment_id 6');
238 hr_utility.set_message(800, 'HR_74025_INVALID_ASSIGNMENT_ID');
239 hr_utility.raise_error;
240 --
241 else
242 --
243 close c1;
244 --
245 --dbms_output.put_line('in assignment_id 7');
246 if p_business_group_id <> l_business_group_id then
247 --
248 hr_utility.set_message(800, 'HR_74026_INVALID_BG_ID');
249 hr_utility.set_message_token('BG_ID' , l_business_group_id);
250 hr_utility.raise_error;
251 --
252 else
253
254 if l_location_id is NULL then
255 fnd_message.set_name('HR', 'HR_74005_LOCATION_ABSENT');
256 fnd_message.raise_error;
257 else
258 open csr_get_location(l_location_id);
259 fetch csr_get_location into l_country;
260 if l_country is null or l_country <> 'CA' then
261 close csr_get_location;
262 fnd_message.set_name('HR', 'HR_74006_LOCATION_WRONG');
263 fnd_message.raise_error;
264 end if;
265 close csr_get_location;
266 end if;
267 end if;
268 --
269 end if;
270 --
271 -- end if;
272 --
273 --dbms_output.put_line('in assignment_id 6');
274 hr_utility.set_location('Leaving:'||l_proc,10);
275 --
276 End chk_assignment_id;
277 -- ----------------------------------------------------------------------------
278 -- |------< chk_employment_province >------|
279 -- ----------------------------------------------------------------------------
280 --
281 -- Description
282 -- This procedure is used to check that the lookup value is valid.
283 --
284 -- Pre Conditions
285 -- None.
286 --
287 -- In Parameters
288 -- emp_fed_tax_inf_id PK of record being inserted or updated.
289 -- province_code Value of lookup code.
290 -- effective_date effective date
291 -- object_version_number Object version number of record being
292 -- inserted or updated.
293 --
294 -- Post Success
295 -- Processing continues
296 --
297 -- Post Failure
298 -- Error handled by procedure
299 --
300 -- Access Status
301 -- Internal table handler use only.
302 --
303 Procedure chk_employment_province(p_emp_fed_tax_inf_id in number,
304 p_employment_province in varchar2,
305 p_effective_date in date,
306 p_object_version_number in number) is
307 --
308 l_proc varchar2(72) := g_package||'chk_fed_code';
309 l_api_updating boolean;
310 --
311 Begin
312 --
313 hr_utility.set_location('Entering:'||l_proc, 5);
314 --
315 l_api_updating := pay_cft_shd.api_updating
316 (p_emp_fed_tax_inf_id => p_emp_fed_tax_inf_id,
317 p_effective_date => p_effective_date,
318 p_object_version_number => p_object_version_number);
319 --
320 if (l_api_updating
321 and p_employment_province
322 <> nvl(pay_cft_shd.g_old_rec.employment_province,hr_api.g_varchar2)
323 or not l_api_updating)
324 and p_employment_province is not null then
325 --
326 -- check if value of lookup falls within lookup type.
327 --
328 if hr_api.not_exists_in_hr_lookups
329 (p_lookup_type => 'CA_PROVINCE',
330 p_lookup_code => p_employment_province,
331 p_effective_date => p_effective_date) then
332 --
333 -- raise error as does not exist as lookup
334 --
335 hr_utility.set_message(800,'HR_EMPLOYMENT_PROVINCE_WRONG');
336 hr_utility.raise_error;
337 --
338 end if;
339 --
340 end if;
341 --
342 hr_utility.set_location('Leaving:'||l_proc,10);
343 --
344 end chk_employment_province;
345 -- ----------------------------------------------------------------------------
346 -- |------< chk_ei_exempt_flag >------|
347 -- ----------------------------------------------------------------------------
348 --
349 -- Description
350 -- This procedure is used to check that the lookup value is valid.
351 --
352 -- Pre Conditions
353 -- None.
354 --
355 -- In Parameters
356 -- emp_fed_tax_inf_id PK of record being inserted or updated.
357 -- ei_exempt_flag Value of lookup code.
358 -- effective_date effective date
359 -- object_version_number Object version number of record being
360 -- inserted or updated.
361 --
362 -- Post Success
363 -- Processing continues
364 --
365 -- Post Failure
366 -- Error handled by procedure
367 --
368 -- Access Status
369 -- Internal table handler use only.
370 --
371 Procedure chk_ei_exempt_flag(p_emp_fed_tax_inf_id in number,
372 p_ei_exempt_flag in varchar2,
373 p_effective_date in date,
374 p_object_version_number in number) is
375 --
376 l_proc varchar2(72) := g_package||'chk_ei_exempt_flag';
377 l_api_updating boolean;
378 --
379 Begin
380 --
381 hr_utility.set_location('Entering:'||l_proc, 5);
382 --
383 l_api_updating := pay_cft_shd.api_updating
384 (p_emp_fed_tax_inf_id => p_emp_fed_tax_inf_id,
385 p_effective_date => p_effective_date,
386 p_object_version_number => p_object_version_number);
387 --
388 if (l_api_updating
389 and p_ei_exempt_flag
390 <> nvl(pay_cft_shd.g_old_rec.ei_exempt_flag,hr_api.g_varchar2)
391 or not l_api_updating)
392 and p_ei_exempt_flag is not null then
393 --
394 -- check if value of lookup falls within lookup type.
395 --
396 if hr_api.not_exists_in_hr_lookups
397 (p_lookup_type => 'YES_NO',
398 p_lookup_code => p_ei_exempt_flag,
399 p_effective_date => p_effective_date) then
400 --
401 -- raise error as does not exist as lookup
402 --
403 hr_utility.set_message(800,'HR_EI_EXEMPT_FLAG_IS WRONG');
404 hr_utility.raise_error;
405 --
406 end if;
407 --
408 end if;
409 --
410 hr_utility.set_location('Leaving:'||l_proc,10);
411 --
412 end chk_ei_exempt_flag;
413 --
414 -- ----------------------------------------------------------------------------
415 -- |------< chk_fed_exempt_flag >------|
416 -- ----------------------------------------------------------------------------
417 --
418 -- Description
419 -- This procedure is used to check that the lookup value is valid.
420 --
421 -- Pre Conditions
422 -- None.
423 --
424 -- In Parameters
425 -- emp_fed_tax_inf_id PK of record being inserted or updated.
426 -- fed_exempt_flag Value of lookup code.
427 -- effective_date effective date
431 -- Post Success
428 -- object_version_number Object version number of record being
429 -- inserted or updated.
430 --
432 -- Processing continues
433 --
434 -- Post Failure
435 -- Error handled by procedure
436 --
437 -- Access Status
438 -- Internal table handler use only.
439 --
440 Procedure chk_fed_exempt_flag(p_emp_fed_tax_inf_id in number,
441 p_fed_exempt_flag in varchar2,
442 p_effective_date in date,
443 p_object_version_number in number) is
444 --
445 l_proc varchar2(72) := g_package||'chk_fed_exempt_flag';
446 l_api_updating boolean;
447 --
448 Begin
449 --
450 hr_utility.set_location('Entering:'||l_proc, 5);
451 --
452 l_api_updating := pay_cft_shd.api_updating
453 (p_emp_fed_tax_inf_id => p_emp_fed_tax_inf_id,
454 p_effective_date => p_effective_date,
455 p_object_version_number => p_object_version_number);
456 --
457 if (l_api_updating
458 and p_fed_exempt_flag
459 <> nvl(pay_cft_shd.g_old_rec.fed_exempt_flag,hr_api.g_varchar2)
460 or not l_api_updating)
461 and p_fed_exempt_flag is not null then
462 --
463 -- check if value of lookup falls within lookup type.
464 --
465 if hr_api.not_exists_in_hr_lookups
466 (p_lookup_type => 'YES_NO',
467 p_lookup_code => p_fed_exempt_flag,
468 p_effective_date => p_effective_date) then
469 --
470 -- raise error as does not exist as lookup
471 --
472 hr_utility.set_message(800,'HR_FED_EXEMPT_FLAG_IS_WRONG');
473 hr_utility.raise_error;
474 --
475 end if;
476 --
477 end if;
478 --
479 hr_utility.set_location('Leaving:'||l_proc,10);
480 --
481 end chk_fed_exempt_flag;
482 --
483 -- ----------------------------------------------------------------------------
484 -- |------< chk_cpp_qpp_exempt_flag >------|
485 -- ----------------------------------------------------------------------------
486 --
487 -- Description
488 -- This procedure is used to check that the lookup value is valid.
489 --
490 -- Pre Conditions
491 -- None.
492 --
493 -- In Parameters
494 -- emp_fed_tax_inf_id PK of record being inserted or updated.
495 -- cpp_qpp_exempt_flag Value of lookup code.
496 -- effective_date effective date
497 -- object_version_number Object version number of record being
498 -- inserted or updated.
499 --
500 -- Post Success
501 -- Processing continues
502 --
503 -- Post Failure
504 -- Error handled by procedure
505 --
506 -- Access Status
507 -- Internal table handler use only.
508 --
509 Procedure chk_cpp_qpp_exempt_flag(p_emp_fed_tax_inf_id in number,
510 p_cpp_qpp_exempt_flag in varchar2,
511 p_effective_date in date,
512 p_object_version_number in number) is
513 --
514 l_proc varchar2(72) := g_package||'chk_cpp_qpp_exempt_flag';
515 l_api_updating boolean;
516 --
517 Begin
518 --
519 hr_utility.set_location('Entering:'||l_proc, 5);
520 --
521 l_api_updating := pay_cft_shd.api_updating
522 (p_emp_fed_tax_inf_id => p_emp_fed_tax_inf_id,
523 p_effective_date => p_effective_date,
524 p_object_version_number => p_object_version_number);
525 --
526 if (l_api_updating
527 and p_cpp_qpp_exempt_flag
528 <> nvl(pay_cft_shd.g_old_rec.cpp_qpp_exempt_flag,hr_api.g_varchar2)
529 or not l_api_updating)
530 and p_cpp_qpp_exempt_flag is not null then
531 --
532 -- check if value of lookup falls within lookup type.
533 --
534 if hr_api.not_exists_in_hr_lookups
535 (p_lookup_type => 'YES_NO',
536 p_lookup_code => p_cpp_qpp_exempt_flag,
537 p_effective_date => p_effective_date) then
538 --
539 -- raise error as does not exist as lookup
540 --
541 hr_utility.set_message(800,'HR_CPP_QPP_EXEMPT_FLAG_WRONG');
542 hr_utility.raise_error;
543 --
544 end if;
545 --
546 end if;
547 --
548 hr_utility.set_location('Leaving:'||l_proc,10);
549 --
550 end chk_cpp_qpp_exempt_flag;
551 --
552 -- ----------------------------------------------------------------------------
553 -- |------< chk_basic_exemption_flag >------|
554 -- ----------------------------------------------------------------------------
555 --
556 -- Description
557 -- This procedure is used to check that the lookup value is valid.
558 --
559 -- Pre Conditions
560 -- None.
561 --
562 -- In Parameters
563 -- emp_fed_tax_inf_id PK of record being inserted or updated.
564 -- basic_exemption_flag Value of lookup code.
565 -- effective_date effective date
566 -- object_version_number Object version number of record being
567 -- inserted or updated.
568 --
569 -- Post Success
573 -- Error handled by procedure
570 -- Processing continues
571 --
572 -- Post Failure
574 --
575 -- Access Status
576 -- Internal table handler use only.
577 --
578 Procedure chk_basic_exemption_flag(p_emp_fed_tax_inf_id in number,
579 p_basic_exemption_flag in varchar2,
580 p_tax_credit_amount in number,
581 p_effective_date in date,
582 p_object_version_number in number) is
583 --
584 l_proc varchar2(72) := g_package||'chk_basic_exemption_flag';
585 l_api_updating boolean;
586 --
587 Begin
588 --
589 hr_utility.set_location('Entering:'||l_proc, 5);
590 --
591 l_api_updating := pay_cft_shd.api_updating
592 (p_emp_fed_tax_inf_id => p_emp_fed_tax_inf_id,
593 p_effective_date => p_effective_date,
594 p_object_version_number => p_object_version_number);
595 --
596 if (l_api_updating
597 and p_basic_exemption_flag
598 <> nvl(pay_cft_shd.g_old_rec.basic_exemption_flag,hr_api.g_varchar2)
599 or not l_api_updating)
600 and p_basic_exemption_flag is not null then
601 --
602 -- check if value of lookup falls within lookup type.
603 --
604 if hr_api.not_exists_in_hr_lookups
605 (p_lookup_type => 'YES_NO',
606 p_lookup_code => p_basic_exemption_flag,
607 p_effective_date => p_effective_date) then
608 --
609 -- raise error as does not exist as lookup
610 --
611 hr_utility.set_message(800,'HR_BASIC_EXEMPT_IS_WRONG');
612 hr_utility.raise_error;
613 --
614 end if;
615 --
616 end if;
617 --
618 if nvl(p_basic_exemption_flag,'N') = 'N'
619 and p_tax_credit_amount is null
620 then
621 hr_utility.set_message(800,'HR_74008_BOTH_NULL');
622 hr_utility.raise_error;
623 --
624 end if;
625
626 if nvl(p_basic_exemption_flag,'N') = 'Y'
627 and p_tax_credit_amount is not null
628 then
629 hr_utility.set_message(800,'HR_74007_BOTH_NOT_NULL');
630 hr_utility.raise_error;
631 --
632 end if;
633 hr_utility.set_location('Leaving:'||l_proc,10);
634 --
635 end chk_basic_exemption_flag;
636 --
637 -- ----------------------------------------------------------------------------
638 -- |------< chk_tax_calc_method >------|
639 -- ----------------------------------------------------------------------------
640 --
641 -- Description
642 -- This procedure is used to check that the lookup value is valid.
643 --
644 -- Pre Conditions
645 -- None.
646 --
647 -- In Parameters
648 -- emp_fed_tax_inf_id PK of record being inserted or updated.
649 -- tax_calc_method Value of lookup code.
650 -- effective_date effective date
651 -- object_version_number Object version number of record being
652 -- inserted or updated.
653 --
654 -- Post Success
655 -- Processing continues
656 --
657 -- Post Failure
658 -- Error handled by procedure
659 --
660 -- Access Status
661 -- Internal table handler use only.
662 --
663 Procedure chk_tax_calc_method(p_emp_fed_tax_inf_id in number,
664 p_tax_calc_method in varchar2,
665 p_effective_date in date,
666 p_object_version_number in number) is
667 --
668 l_proc varchar2(72) := g_package||'chk_tax_calc_method';
669 l_api_updating boolean;
670 --
671 Begin
672 --
673 hr_utility.set_location('Entering:'||l_proc, 5);
674 --
675 l_api_updating := pay_cft_shd.api_updating
676 (p_emp_fed_tax_inf_id => p_emp_fed_tax_inf_id,
677 p_effective_date => p_effective_date,
678 p_object_version_number => p_object_version_number);
679 --
680 if (l_api_updating
681 and p_tax_calc_method
682 <> nvl(pay_cft_shd.g_old_rec.tax_calc_method,hr_api.g_varchar2)
683 or not l_api_updating)
684 and p_tax_calc_method is not null then
685 --
686 -- check if value of lookup falls within lookup type.
687 --
688 if hr_api.not_exists_in_hr_lookups
689 (p_lookup_type => 'CA_TAX_CALC_METHOD',
690 p_lookup_code => p_tax_calc_method,
691 p_effective_date => p_effective_date) then
692 --
693 -- raise error as does not exist as lookup
694 --
695 hr_utility.set_message(800,'HR_CA_TAX_CALC_METHOD');
696 hr_utility.raise_error;
697 --
698 end if;
699 --
700 end if;
701 --
702 hr_utility.set_location('Leaving:'||l_proc,10);
703 --
704 end chk_tax_calc_method;
705 -- ----------------------------------------------------------------------------
706 -- |--------------------------< dt_update_validate >--------------------------|
707 -- ----------------------------------------------------------------------------
708 -- {Start Of Comments}
709 --
710 -- Description:
714 --
711 -- This procedure is used for referential integrity of datetracked
712 -- parent entities when a datetrack update operation is taking place
713 -- and where there is no cascading of update defined for this entity.
715 -- Prerequisites:
716 -- This procedure is called from the update_validate.
717 --
718 -- In Parameters:
719 --
720 -- Post Success:
721 -- Processing continues.
722 --
723 -- Post Failure:
724 --
725 -- Developer Implementation Notes:
726 -- This procedure should not need maintenance unless the HR Schema model
727 -- changes.
728 --
729 -- Access Status:
730 -- Internal Row Handler Use Only.
731 --
732 -- {End Of Comments}
733 -- ----------------------------------------------------------------------------
734 Procedure dt_update_validate
735 (p_assignment_id in number default hr_api.g_number,
736 p_datetrack_mode in varchar2,
737 p_validation_start_date in date,
738 p_validation_end_date in date) Is
739 --
740 l_proc varchar2(72) := g_package||'dt_update_validate';
741 l_integrity_error Exception;
742 l_table_name all_tables.table_name%TYPE;
743 --
744 Begin
745 hr_utility.set_location('Entering:'||l_proc, 5);
746 --
747 -- Ensure that the p_datetrack_mode argument is not null
748 --
749 hr_api.mandatory_arg_error
750 (p_api_name => l_proc,
751 p_argument => 'datetrack_mode',
752 p_argument_value => p_datetrack_mode);
753 --
754 -- Only perform the validation if the datetrack update mode is valid
755 --
756 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
757 --
758 --
759 -- Ensure the arguments are not null
760 --
761 hr_api.mandatory_arg_error
762 (p_api_name => l_proc,
763 p_argument => 'validation_start_date',
764 p_argument_value => p_validation_start_date);
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 If ((nvl(p_assignment_id, hr_api.g_number) <> hr_api.g_number) and
772 NOT (dt_api.check_min_max_dates
773 (p_base_table_name => 'per_all_assignments_f',
774 p_base_key_column => 'assignment_id',
775 p_base_key_value => p_assignment_id,
776 p_from_date => p_validation_start_date,
777 p_to_date => p_validation_end_date))) Then
778 l_table_name := 'all assignments';
779 Raise l_integrity_error;
780 End If;
781 --
782 End If;
783 --
784 hr_utility.set_location(' Leaving:'||l_proc, 10);
785 Exception
786 When l_integrity_error Then
787 --
788 -- A referential integrity check was violated therefore
789 -- we must error
790 --
791 hr_utility.set_message(800, 'HR_7216_DT_UPD_INTEGRITY_ERR');
792 hr_utility.set_message_token('TABLE_NAME', l_table_name);
793 hr_utility.raise_error;
794 When Others Then
795 --
796 -- An unhandled or unexpected error has occurred which
797 -- we must report
798 --
799 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
800 hr_utility.set_message_token('PROCEDURE', l_proc);
801 hr_utility.set_message_token('STEP','15');
802 hr_utility.raise_error;
803 End dt_update_validate;
804 --
805 -- ----------------------------------------------------------------------------
806 -- |--------------------------< dt_delete_validate >--------------------------|
807 -- ----------------------------------------------------------------------------
808 -- {Start Of Comments}
809 --
810 -- Description:
811 -- This procedure is used for referential integrity of datetracked
812 -- child entities when either a datetrack DELETE or ZAP is in operation
813 -- and where there is no cascading of delete defined for this entity.
814 -- For the datetrack mode of DELETE or ZAP we must ensure that no
815 -- datetracked child rows exist between the validation start and end
816 -- dates.
817 --
818 -- Prerequisites:
819 -- This procedure is called from the delete_validate.
820 --
821 -- In Parameters:
822 --
823 -- Post Success:
824 -- Processing continues.
825 --
826 -- Post Failure:
827 -- If a row exists by determining the returning Boolean value from the
828 -- generic dt_api.rows_exist function then we must supply an error via
829 -- the use of the local exception handler l_rows_exist.
830 --
831 -- Developer Implementation Notes:
832 -- This procedure should not need maintenance unless the HR Schema model
833 -- changes.
834 --
835 -- Access Status:
836 -- Internal Row Handler Use Only.
837 --
838 -- {End Of Comments}
839 -- ----------------------------------------------------------------------------
840 Procedure dt_delete_validate
841 (p_emp_fed_tax_inf_id in number,
842 p_datetrack_mode in varchar2,
843 p_validation_start_date in date,
844 p_validation_end_date in date) Is
845 --
846 l_proc varchar2(72) := g_package||'dt_delete_validate';
850 Begin
847 l_rows_exist Exception;
848 l_table_name all_tables.table_name%TYPE;
849 --
851 hr_utility.set_location('Entering:'||l_proc, 5);
852 --
853 -- Ensure that the p_datetrack_mode argument is not null
854 --
855 hr_api.mandatory_arg_error
856 (p_api_name => l_proc,
857 p_argument => 'datetrack_mode',
858 p_argument_value => p_datetrack_mode);
859 --
860 -- Only perform the validation if the datetrack mode is either
861 -- DELETE or ZAP
862 --
863 If (p_datetrack_mode = 'DELETE' or
864 p_datetrack_mode = 'ZAP') then
865 --
866 --
867 -- Ensure the arguments are not null
868 --
869 hr_api.mandatory_arg_error
870 (p_api_name => l_proc,
871 p_argument => 'validation_start_date',
872 p_argument_value => p_validation_start_date);
873 --
874 hr_api.mandatory_arg_error
875 (p_api_name => l_proc,
876 p_argument => 'validation_end_date',
877 p_argument_value => p_validation_end_date);
878 --
879 hr_api.mandatory_arg_error
880 (p_api_name => l_proc,
881 p_argument => 'emp_fed_tax_inf_id',
882 p_argument_value => p_emp_fed_tax_inf_id);
883 --
884 --
885 --
886 End If;
887 --
888 hr_utility.set_location(' Leaving:'||l_proc, 10);
889 Exception
890 When l_rows_exist Then
891 --
892 -- A referential integrity check was violated therefore
893 -- we must error
894 --
895 hr_utility.set_message(800, 'HR_7215_DT_CHILD_EXISTS');
896 hr_utility.set_message_token('TABLE_NAME', l_table_name);
897 hr_utility.raise_error;
898 When Others Then
899 --
900 -- An unhandled or unexpected error has occurred which
901 -- we must report
902 --
903 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
904 hr_utility.set_message_token('PROCEDURE', l_proc);
905 hr_utility.set_message_token('STEP','15');
906 hr_utility.raise_error;
907 End dt_delete_validate;
908 --
909 -- ----------------------------------------------------------------------------
910 -- |---------------------------< insert_validate >----------------------------|
911 -- ----------------------------------------------------------------------------
912 Procedure insert_validate
913 (p_rec in pay_cft_shd.g_rec_type,
914 p_effective_date in date,
915 p_datetrack_mode in varchar2,
916 p_validation_start_date in date,
917 p_validation_end_date in date) is
918 --
919 l_proc varchar2(72) := g_package||'insert_validate';
920 --
921 Begin
922 hr_utility.set_location('Entering:'||l_proc, 5);
923 --
924 -- Call all supporting business operations
925 --
926 --dbms_output.put_line('before fed_tax_inf_id ');
927 chk_emp_fed_tax_inf_id
928 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
929 p_effective_date => p_effective_date,
930 p_object_version_number => p_rec.object_version_number);
931 --
932 --dbms_output.put_line('before legislation ');
933 chk_legislation_code
934 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
935 p_legislation_code => p_rec.legislation_code,
936 p_effective_date => p_effective_date,
937 p_object_version_number => p_rec.object_version_number);
938
939 --dbms_output.put_line('before assignment_id ');
940 chk_assignment_id
941 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id
942 ,p_assignment_id => p_rec.assignment_id
943 ,p_business_group_id => p_rec.business_group_id
944 ,p_effective_date => p_effective_date
945 ,p_object_version_number => p_rec.object_version_number
946 );
947 --dbms_output.put_line('after assignment_id ');
948 --
949 --dbms_output.put_line('before employment_prvince ');
950 chk_employment_province
951 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id
952 ,p_employment_province => p_rec.employment_province
953 ,p_effective_date => p_effective_date
954 ,p_object_version_number => p_rec.object_version_number
955 );
956 --
957 --dbms_output.put_line('before ei_exempt_flag ');
958 chk_ei_exempt_flag
959 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
960 p_ei_exempt_flag => p_rec.ei_exempt_flag,
961 p_effective_date => p_effective_date,
962 p_object_version_number => p_rec.object_version_number);
963 --
964 --dbms_output.put_line('before fed_exempt ');
965 chk_fed_exempt_flag
966 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
967 p_fed_exempt_flag => p_rec.fed_exempt_flag,
968 p_effective_date => p_effective_date,
969 p_object_version_number => p_rec.object_version_number);
970 --
971 --dbms_output.put_line('before cpp_qpp_exempt ');
972 chk_cpp_qpp_exempt_flag
973 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
974 p_cpp_qpp_exempt_flag => p_rec.cpp_qpp_exempt_flag,
975 p_effective_date => p_effective_date,
976 p_object_version_number => p_rec.object_version_number);
977 --
978 --dbms_output.put_line('before tax_calc_method ');
979 chk_tax_calc_method
980 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
984 --
981 p_tax_calc_method => p_rec.tax_calc_method,
982 p_effective_date => p_effective_date,
983 p_object_version_number => p_rec.object_version_number);
985 --dbms_output.put_line('before basic_exemption ');
986 chk_basic_exemption_flag
987 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
988 p_basic_exemption_flag => p_rec.basic_exemption_flag,
989 p_tax_credit_amount => p_rec.tax_credit_amount,
990 p_effective_date => p_effective_date,
991 p_object_version_number => p_rec.object_version_number);
992 --
993 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
994 --
995 hr_utility.set_location(' Leaving:'||l_proc, 10);
996 End insert_validate;
997 --
998 -- ----------------------------------------------------------------------------
999 -- |---------------------------< update_validate >----------------------------|
1000 -- ----------------------------------------------------------------------------
1001 Procedure update_validate
1002 (p_rec in pay_cft_shd.g_rec_type,
1003 p_effective_date in date,
1004 p_datetrack_mode in varchar2,
1005 p_validation_start_date in date,
1006 p_validation_end_date in date) is
1007 --
1008 l_proc varchar2(72) := g_package||'update_validate';
1009 --
1010 Begin
1011 hr_utility.set_location('Entering:'||l_proc, 5);
1012 --
1013 -- Call all supporting business operations
1014 --
1015 chk_emp_fed_tax_inf_id
1016 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
1017 p_effective_date => p_effective_date,
1018 p_object_version_number => p_rec.object_version_number);
1019 --
1020 chk_legislation_code
1021 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
1022 p_legislation_code => p_rec.legislation_code,
1023 p_effective_date => p_effective_date,
1024 p_object_version_number => p_rec.object_version_number);
1025 --
1026 chk_assignment_id
1027 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id
1028 ,p_assignment_id => p_rec.assignment_id
1029 ,p_business_group_id => p_rec.business_group_id
1030 ,p_effective_date => p_effective_date
1031 ,p_object_version_number => p_rec.object_version_number
1032 );
1033 --
1034 chk_employment_province
1035 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id
1036 ,p_employment_province => p_rec.employment_province
1037 ,p_effective_date => p_effective_date
1038 ,p_object_version_number => p_rec.object_version_number
1039 );
1040 --
1041 chk_ei_exempt_flag
1042 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
1043 p_ei_exempt_flag => p_rec.ei_exempt_flag,
1044 p_effective_date => p_effective_date,
1045 p_object_version_number => p_rec.object_version_number);
1046 --
1047 chk_fed_exempt_flag
1048 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
1049 p_fed_exempt_flag => p_rec.fed_exempt_flag,
1050 p_effective_date => p_effective_date,
1051 p_object_version_number => p_rec.object_version_number);
1052 --
1053 chk_cpp_qpp_exempt_flag
1054 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
1055 p_cpp_qpp_exempt_flag => p_rec.cpp_qpp_exempt_flag,
1056 p_effective_date => p_effective_date,
1057 p_object_version_number => p_rec.object_version_number);
1058 --
1059 chk_tax_calc_method
1060 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
1061 p_tax_calc_method => p_rec.tax_calc_method,
1062 p_effective_date => p_effective_date,
1063 p_object_version_number => p_rec.object_version_number);
1064 --
1065 chk_basic_exemption_flag
1066 (p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id,
1067 p_basic_exemption_flag => p_rec.basic_exemption_flag,
1068 p_tax_credit_amount => p_rec.tax_credit_amount,
1069 p_effective_date => p_effective_date,
1070 p_object_version_number => p_rec.object_version_number);
1071 --
1072 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1073 --
1074 -- Call the datetrack update integrity operation
1075 --
1076 dt_update_validate
1077 (p_assignment_id => p_rec.assignment_id,
1078 p_datetrack_mode => p_datetrack_mode,
1079 p_validation_start_date => p_validation_start_date,
1080 p_validation_end_date => p_validation_end_date);
1081 --
1082 hr_utility.set_location(' Leaving:'||l_proc, 10);
1083 End update_validate;
1084 --
1085 -- ----------------------------------------------------------------------------
1086 -- |---------------------------< delete_validate >----------------------------|
1087 -- ----------------------------------------------------------------------------
1088 Procedure delete_validate
1089 (p_rec in pay_cft_shd.g_rec_type,
1090 p_effective_date in date,
1091 p_datetrack_mode in varchar2,
1092 p_validation_start_date in date,
1093 p_validation_end_date in date) is
1094 --
1095 l_proc varchar2(72) := g_package||'delete_validate';
1096 --
1097 Begin
1098 hr_utility.set_location('Entering:'||l_proc, 5);
1099 --
1100 -- Call all supporting business operations
1101 --
1102 dt_delete_validate
1103 (p_datetrack_mode => p_datetrack_mode,
1104 p_validation_start_date => p_validation_start_date,
1105 p_validation_end_date => p_validation_end_date,
1106 p_emp_fed_tax_inf_id => p_rec.emp_fed_tax_inf_id);
1107 --
1108 hr_utility.set_location(' Leaving:'||l_proc, 10);
1109 End delete_validate;
1110 --
1111 --
1112 -- ---------------------------------------------------------------------------
1113 -- |---------------------< return_legislation_code >-------------------------|
1114 -- ---------------------------------------------------------------------------
1115 --
1116 function return_legislation_code
1117 (p_emp_fed_tax_inf_id in number) return varchar2 is
1118 --
1119 -- Declare cursor
1120 --
1121 cursor csr_leg_code is
1122 select a.legislation_code
1123 from per_business_groups a,
1124 pay_ca_emp_fed_tax_info_f b
1125 where b.emp_fed_tax_inf_id = p_emp_fed_tax_inf_id
1126 and a.business_group_id = b.business_group_id;
1127 --
1128 -- Declare local variables
1129 --
1130 l_legislation_code varchar2(150);
1131 l_proc varchar2(72) := g_package||'return_legislation_code';
1132 --
1133 begin
1134 --
1135 hr_utility.set_location('Entering:'|| l_proc, 10);
1136 --
1137 -- Ensure that all the mandatory parameter are not null
1138 --
1139 hr_api.mandatory_arg_error(p_api_name => l_proc,
1140 p_argument => 'emp_fed_tax_inf_id',
1141 p_argument_value => p_emp_fed_tax_inf_id);
1142 --
1143 open csr_leg_code;
1144 --
1145 fetch csr_leg_code into l_legislation_code;
1146 --
1147 if csr_leg_code%notfound then
1148 --
1149 close csr_leg_code;
1150 --
1151 -- The primary key is invalid therefore we must error
1152 --
1153 hr_utility.set_message(800,'HR_7220_INVALID_PRIMARY_KEY');
1154 hr_utility.raise_error;
1155 --
1156 end if;
1157 --
1158 close csr_leg_code;
1159 --
1160 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1161 --
1162 return l_legislation_code;
1163 --
1164 end return_legislation_code;
1165 --
1166 end pay_cft_bus;