[Home] [Help]
PACKAGE BODY: APPS.PAY_CPT_BUS
Source
1 Package Body pay_cpt_bus as
2 /* $Header: pycprrhi.pkb 120.1.12010000.3 2008/08/06 07:04:33 ubhat ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_cpt_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_assignment_id >------|
12 -- ----------------------------------------------------------------------------
13 Procedure chk_assignment_id
14 (p_emp_province_tax_inf_id in number
15 ,p_assignment_id in
16 pay_ca_emp_prov_tax_info_f.assignment_id%TYPE
17 ,p_business_group_id in
18 pay_ca_emp_prov_tax_info_f.business_group_id%TYPE
19 ,p_effective_date in date
20 ,p_object_version_number in number
21 ) is
22 --
23 l_proc varchar2(72) := g_package||'chk_assignment_id';
24 l_dummy varchar2(1);
25 l_api_updating boolean;
26 l_business_group_id per_assignments_f.business_group_id%TYPE;
27 --
28 cursor c1 is
29 select business_group_id
30 from per_assignments_f asg
31 where asg.assignment_id = p_assignment_id
32 and p_effective_date between asg.effective_start_date
33 and asg.effective_end_date;
34 --
35 cursor c2 is
36 select null
37 from pay_ca_emp_fed_tax_info_f fed
38 where fed.assignment_id = p_assignment_id
39 and p_effective_date between fed.effective_start_date
40 and fed.effective_end_date;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc,5);
45 --
46 -- Check that the mandatory parameters have been set
47 --
48 if p_assignment_id is null then
49 hr_utility.set_message(800, 'HR_74023_ASSIGNMENT_ID_NULL');
50 hr_utility.raise_error;
51 end if;
52 --
53 if p_business_group_id is null then
54 hr_utility.set_message(800, 'HR_74024_BUSINESS_GROUP_ID_NULL');
55 hr_utility.raise_error;
56 end if;
57 --
58 hr_api.mandatory_arg_error
59 (p_api_name => l_proc
60 ,p_argument => 'effective_date'
61 ,p_argument_value => p_effective_date
62 );
63 --
64 l_api_updating := pay_cpt_shd.api_updating
65 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
66 p_effective_date => p_effective_date,
67 p_object_version_number => p_object_version_number);
68 --
69 -- Since assignment_id cannot be updated, the case of
70 -- l_api_updating = TRUE is not considered
71 --
72 if (l_api_updating ) then
73 if p_assignment_id <> pay_cpt_shd.g_old_rec.assignment_id then
74 -- dbms_output.put_line('cannot change assignment_id');
75 hr_utility.set_message(800, 'HR_74027_ASSIGNMENT_ID_CHANGED');
76 hr_utility.raise_error;
77 end if;
78 end if;
79 --
80 -- if (not l_api_updating) then
81 --
82 open c1;
83 --
84 fetch c1 into l_business_group_id;
85 if c1%notfound then
86 --
87 close c1;
88 --
89 -- raise error as assignment_id not found in per_assignments_f
90 -- table.
91 --
92 hr_utility.set_message(800, 'HR_74025_INVALID_ASSIGNMENT_ID');
93 hr_utility.raise_error;
94 --
95 else
96 --
97 close c1;
98 --
99 if p_business_group_id <> l_business_group_id then
100 --
101 hr_utility.set_message(800, 'HR_74026_INVALID_BG_ID');
102 hr_utility.raise_error;
103 --
104 else
105 --
106 open c2;
107 fetch c2 into l_dummy;
108 if c2%notfound then
109 close c2;
110 hr_utility.set_message(800, 'PAY_74029_NO_FED_TAX_INFO');
111 hr_utility.raise_error;
112 end if;
113 close c2;
114 --
115 end if;
116 --
117 end if;
118 --
119 -- end if;
120 --
121 hr_utility.set_location('Leaving:'||l_proc,10);
122 --
123 End chk_assignment_id;
124 -- ----------------------------------------------------------------------------
125 -- |------< chk_emp_province_tax_inf_id >------|
126 -- ----------------------------------------------------------------------------
127 --
128 -- Description
129 -- This procedure is used to check that the primary key for the table
130 -- is created properly. It should be null on insert and
131 -- should not be able to be updated.
132 --
133 -- Pre Conditions
134 -- None.
135 --
136 -- In Parameters
137 -- emp_province_tax_inf_id PK of record being inserted or updated.
138 -- effective_date Effective Date of session
139 -- object_version_number Object version number of record being
140 -- inserted or updated.
141 --
142 -- Post Success
143 -- Processing continues
144 --
145 -- Post Failure
146 -- Errors handled by the procedure
147 --
148 -- Access Status
149 -- Internal table handler use only.
150 --
151 Procedure chk_emp_province_tax_inf_id(p_emp_province_tax_inf_id in number,
152 p_effective_date in date,
153 p_object_version_number in number) is
154 --
155 l_proc varchar2(72) := g_package||'chk_emp_province_tax_inf_id';
156 l_api_updating boolean;
157 --
158 Begin
159 --
160 hr_utility.set_location('Entering:'||l_proc, 5);
161 --
162 l_api_updating := pay_cpt_shd.api_updating
163 (p_effective_date => p_effective_date,
164 p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
165 p_object_version_number => p_object_version_number);
166 --
167 if (l_api_updating
168 and nvl(p_emp_province_tax_inf_id,hr_api.g_number)
169 <> pay_cpt_shd.g_old_rec.emp_province_tax_inf_id) then
170 --
171 -- raise error as PK has changed
172 --
173 pay_cpt_shd.constraint_error('PAY_CA_EMP_PROVIN_TAX_RULES_PK');
174 --
175 elsif not l_api_updating then
176 --
177 -- check if PK is null
178 --
179 if p_emp_province_tax_inf_id is not null then
180 --
181 -- raise error as PK is not null
182 --
183 pay_cpt_shd.constraint_error('PAY_CA_EMP_PROVIN_TAX_RULES_PK');
184 --
185 end if;
186 --
187 end if;
188 --
189 hr_utility.set_location('Leaving:'||l_proc, 10);
190 --
191 End chk_emp_province_tax_inf_id;
192 --
193 -- ----------------------------------------------------------------------------
194 -- |------< chk_wc_exempt_flag >------|
195 -- ----------------------------------------------------------------------------
196 --
197 -- Description
198 -- This procedure is used to check that the lookup value is valid.
199 --
200 -- Pre Conditions
201 -- None.
202 --
203 -- In Parameters
204 -- emp_province_tax_inf_id PK of record being inserted or updated.
205 -- wc_exempt_flag Value of lookup code.
206 -- effective_date effective date
207 -- object_version_number Object version number of record being
208 -- inserted or updated.
209 --
210 -- Post Success
211 -- Processing continues
212 --
213 -- Post Failure
214 -- Error handled by procedure
215 --
216 -- Access Status
217 -- Internal table handler use only.
218 --
219 Procedure chk_wc_exempt_flag(p_emp_province_tax_inf_id in number,
220 p_wc_exempt_flag in varchar2,
221 p_effective_date in date,
222 p_object_version_number in number) is
223 --
224 l_proc varchar2(72) := g_package||'chk_wc_exempt_flag';
225 l_api_updating boolean;
226 --
227 Begin
228 --
229 hr_utility.set_location('Entering:'||l_proc, 5);
230 --
231 l_api_updating := pay_cpt_shd.api_updating
232 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
233 p_effective_date => p_effective_date,
234 p_object_version_number => p_object_version_number);
235 --
236 if (l_api_updating
237 and p_wc_exempt_flag
238 <> nvl(pay_cpt_shd.g_old_rec.wc_exempt_flag,hr_api.g_varchar2)
239 or not l_api_updating)
240 and p_wc_exempt_flag is not null then
241 --
242 -- check if value of lookup falls within lookup type.
243 --
244 if hr_api.not_exists_in_hr_lookups
245 (p_lookup_type => 'YES_NO',
246 p_lookup_code => p_wc_exempt_flag,
247 p_effective_date => p_effective_date) then
248 --
249 -- raise error as does not exist as lookup
250 --
251 hr_utility.set_message(800,'HR_WC_EXEMPT_FLAG_IS_WRONG');
252 hr_utility.raise_error;
253 --
254 end if;
255 --
256 end if;
257 --
258 hr_utility.set_location('Leaving:'||l_proc,10);
259 --
260 end chk_wc_exempt_flag;
261 --
262 -- ----------------------------------------------------------------------------
263 -- |------< chk_pmed_exempt_flag >------|
264 -- ----------------------------------------------------------------------------
265 --
266 -- Description
267 -- This procedure is used to check that the lookup value is valid.
268 --
269 -- Pre Conditions
270 -- None.
271 --
272 -- In Parameters
273 -- emp_province_tax_inf_id PK of record being inserted or updated.
274 -- pmed_exempt_flag Value of lookup code.
275 -- effective_date effective date
276 -- object_version_number Object version number of record being
277 -- inserted or updated.
278 --
279 -- Post Success
280 -- Processing continues
281 --
282 -- Post Failure
283 -- Error handled by procedure
284 --
285 -- Access Status
286 -- Internal table handler use only.
287 --
288 Procedure chk_pmed_exempt_flag(p_emp_province_tax_inf_id in number,
289 p_pmed_exempt_flag in varchar2,
290 p_effective_date in date,
291 p_object_version_number in number) is
292 --
293 l_proc varchar2(72) := g_package||'chk_pmed_exempt_flag';
294 l_api_updating boolean;
295 --
296 Begin
297 --
298 hr_utility.set_location('Entering:'||l_proc, 5);
299 --
300 l_api_updating := pay_cpt_shd.api_updating
301 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
302 p_effective_date => p_effective_date,
303 p_object_version_number => p_object_version_number);
304 --
305 if (l_api_updating
306 and p_pmed_exempt_flag
307 <> nvl(pay_cpt_shd.g_old_rec.pmed_exempt_flag,hr_api.g_varchar2)
308 or not l_api_updating)
309 and p_pmed_exempt_flag is not null then
310 --
311 -- check if value of lookup falls within lookup type.
312 --
313 if hr_api.not_exists_in_hr_lookups
314 (p_lookup_type => 'YES_NO',
315 p_lookup_code => p_pmed_exempt_flag,
316 p_effective_date => p_effective_date) then
317 --
318 -- raise error as does not exist as lookup
319 --
320 hr_utility.set_message(800,'HR_PMED_EXEMPT_FLAG_IS_WRONG');
321 hr_utility.raise_error;
322 --
323 end if;
324 --
325 end if;
326 --
327 hr_utility.set_location('Leaving:'||l_proc,10);
328 --
329 end chk_pmed_exempt_flag;
330 --
331 -- ----------------------------------------------------------------------------
332 -- |------< chk_prov_exempt_flag >------|
333 -- ----------------------------------------------------------------------------
334 --
335 -- Description
336 -- This procedure is used to check that the lookup value is valid.
337 --
338 -- Pre Conditions
339 -- None.
340 --
341 -- In Parameters
342 -- emp_province_tax_inf_id PK of record being inserted or updated.
343 -- prov_exempt_flag Value of lookup code.
344 -- effective_date effective date
345 -- object_version_number Object version number of record being
346 -- inserted or updated.
347 --
348 -- Post Success
349 -- Processing continues
350 --
351 -- Post Failure
352 -- Error handled by procedure
353 --
354 -- Access Status
355 -- Internal table handler use only.
356 --
357 Procedure chk_prov_exempt_flag(p_emp_province_tax_inf_id in number,
358 p_prov_exempt_flag in varchar2,
359 p_effective_date in date,
360 p_object_version_number in number) is
361 --
362 l_proc varchar2(72) := g_package||'chk_prov_exempt_flag';
363 l_api_updating boolean;
364 --
365 Begin
366 --
367 hr_utility.set_location('Entering:'||l_proc, 5);
368 --
369 l_api_updating := pay_cpt_shd.api_updating
370 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
371 p_effective_date => p_effective_date,
372 p_object_version_number => p_object_version_number);
373 --
374 if (l_api_updating
375 and p_prov_exempt_flag
376 <> nvl(pay_cpt_shd.g_old_rec.prov_exempt_flag,hr_api.g_varchar2)
377 or not l_api_updating)
378 and p_prov_exempt_flag is not null then
379 --
380 -- check if value of lookup falls within lookup type.
381 --
382 if hr_api.not_exists_in_hr_lookups
383 (p_lookup_type => 'YES_NO',
384 p_lookup_code => p_prov_exempt_flag,
385 p_effective_date => p_effective_date) then
386 --
387 -- raise error as does not exist as lookup
388 --
389 hr_utility.set_message(800,'HR_PROV_EXEMPT_FLAG_IS_WRONG');
390 hr_utility.raise_error;
391 --
392 end if;
393 --
394 end if;
395 --
396 hr_utility.set_location('Leaving:'||l_proc,10);
397 --
398 end chk_prov_exempt_flag;
399 --
400 -- ----------------------------------------------------------------------------
401 -- |------< chk_basic_exemption_flag >------|
402 -- ----------------------------------------------------------------------------
403 --
404 -- Description
405 -- This procedure is used to check that the lookup value is valid.
406 --
407 -- Pre Conditions
408 -- None.
409 --
410 -- In Parameters
411 -- emp_province_tax_inf_id PK of record being inserted or updated.
412 -- basic_exemption_flag Value of lookup code.
413 -- effective_date effective date
414 -- object_version_number Object version number of record being
415 -- inserted or updated.
416 --
417 -- Post Success
418 -- Processing continues
419 --
420 -- Post Failure
421 -- Error handled by procedure
422 --
423 -- Access Status
424 -- Internal table handler use only.
425 --
426 Procedure chk_basic_exemption_flag(p_emp_province_tax_inf_id in number,
427 p_basic_exemption_flag in varchar2,
428 p_tax_credit_amount in number,
429 p_province_code in varchar2,
430 p_effective_date in date,
431 p_object_version_number in number) is
432 --
433 l_proc varchar2(72) := g_package||'chk_basic_exemption_flag';
434 l_api_updating boolean;
435 --
436 Begin
437 --
438 hr_utility.set_location('Entering:'||l_proc, 5);
442 p_effective_date => p_effective_date,
439 --
440 l_api_updating := pay_cpt_shd.api_updating
441 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
443 p_object_version_number => p_object_version_number);
444 --
445 if (l_api_updating
446 and p_basic_exemption_flag
447 <> nvl(pay_cpt_shd.g_old_rec.basic_exemption_flag,hr_api.g_varchar2)
448 or not l_api_updating)
449 and p_basic_exemption_flag is not null then
450 --
451 -- check if value of lookup falls within lookup type.
452 --
453 if hr_api.not_exists_in_hr_lookups
454 (p_lookup_type => 'YES_NO',
455 p_lookup_code => p_basic_exemption_flag,
456 p_effective_date => p_effective_date) then
457 --
458 -- raise error as does not exist as lookup
459 --
460 hr_utility.set_message(800,'HR_BASIC_EXEMPT_IS_WRONG');
461 hr_utility.raise_error;
462 --
463 end if;
464
465 --
466 end if;
467 if nvl(p_basic_exemption_flag,'N') = 'N'
468 and p_tax_credit_amount is null
469 then
470 hr_utility.set_message(800,'HR_74008_BOTH_NULL');
471 hr_utility.raise_error;
472 --
473 end if;
474
475 if nvl(p_basic_exemption_flag,'N') = 'Y'
476 and p_tax_credit_amount is not null
477 then
478 hr_utility.set_message(800,'HR_74007_BOTH_NOT_NULL');
479 hr_utility.raise_error;
480 --
481 end if;
482 --
483 hr_utility.set_location('Leaving:'||l_proc,10);
484 --
485 end chk_basic_exemption_flag;
486 --
487 -- ----------------------------------------------------------------------------
488 -- |------< chk_marriage_status >------|
489 -- ----------------------------------------------------------------------------
490 --
491 -- Description
492 -- This procedure is used to check that the lookup value is valid.
493 --
494 -- Pre Conditions
495 -- None.
496 --
497 -- In Parameters
498 -- emp_province_tax_inf_id PK of record being inserted or updated.
499 -- marriage_status Value of lookup code.
500 -- effective_date effective date
501 -- object_version_number Object version number of record being
502 -- inserted or updated.
503 --
504 -- Post Success
505 -- Processing continues
506 --
507 -- Post Failure
508 -- Error handled by procedure
509 --
510 -- Access Status
511 -- Internal table handler use only.
512 --
513 Procedure chk_marriage_status(p_emp_province_tax_inf_id in number,
514 p_marriage_status in varchar2,
515 p_effective_date in date,
516 p_object_version_number in number) is
517 --
518 l_proc varchar2(72) := g_package||'chk_marriage_status';
519 l_api_updating boolean;
520 --
521 Begin
522 --
523 hr_utility.set_location('Entering:'||l_proc, 5);
524 --
525 l_api_updating := pay_cpt_shd.api_updating
526 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
527 p_effective_date => p_effective_date,
528 p_object_version_number => p_object_version_number);
529 --
530 if (l_api_updating
531 and p_marriage_status
532 <> nvl(pay_cpt_shd.g_old_rec.marriage_status,hr_api.g_varchar2)
533 or not l_api_updating)
534 and p_marriage_status is not null then
535 --
536 -- check if value of lookup falls within lookup type.
537 --
538 if hr_api.not_exists_in_hr_lookups
539 (p_lookup_type => 'YES_NO',
540 p_lookup_code => p_marriage_status,
541 p_effective_date => p_effective_date) then
542 --
543 -- raise error as does not exist as lookup
544 --
545 hr_utility.set_message(800,'HR_MARRIAGE_STATUS_IS_WRONG');
546 hr_utility.raise_error;
547 --
548 end if;
549 --
550 end if;
551 --
552 hr_utility.set_location('Leaving:'||l_proc,10);
553 --
554 end chk_marriage_status;
555 -- ----------------------------------------------------------------------------
556 -- |------< chk_disability_status >------|
557 -- ----------------------------------------------------------------------------
558 --
559 -- Description
560 -- This procedure is used to check that the lookup value is valid.
561 --
562 -- Pre Conditions
563 -- None.
564 --
565 -- In Parameters
566 -- emp_province_tax_inf_id PK of record being inserted or updated.
567 -- disability_status Value of lookup code.
568 -- effective_date effective date
569 -- object_version_number Object version number of record being
570 -- inserted or updated.
571 --
572 -- Post Success
573 -- Processing continues
574 --
575 -- Post Failure
576 -- Error handled by procedure
577 --
578 -- Access Status
579 -- Internal table handler use only.
580 --
581 Procedure chk_disability_status(p_emp_province_tax_inf_id in number,
582 p_disability_status in varchar2,
586 l_proc varchar2(72) := g_package||'chk_disability_status';
583 p_effective_date in date,
584 p_object_version_number in number) is
585 --
587 l_api_updating boolean;
588 --
589 Begin
590 --
591 hr_utility.set_location('Entering:'||l_proc, 5);
592 --
593 l_api_updating := pay_cpt_shd.api_updating
594 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
595 p_effective_date => p_effective_date,
596 p_object_version_number => p_object_version_number);
597 --
598 if (l_api_updating
599 and p_disability_status
600 <> nvl(pay_cpt_shd.g_old_rec.disability_status,hr_api.g_varchar2)
601 or not l_api_updating)
602 and p_disability_status is not null then
603 --
604 -- check if value of lookup falls within lookup type.
605 --
606 if hr_api.not_exists_in_hr_lookups
607 (p_lookup_type => 'YES_NO',
608 p_lookup_code => p_disability_status,
609 p_effective_date => p_effective_date) then
610 --
611 -- raise error as does not exist as lookup
612 --
613 hr_utility.set_message(800,'HR_DISABILITY_STATUS_IS_WRONG');
614 hr_utility.raise_error;
615 --
616 end if;
617 --
618 end if;
619 --
620 hr_utility.set_location('Leaving:'||l_proc,10);
621 --
622 end chk_disability_status;
623 -- ----------------------------------------------------------------------------
624 -- |------< chk_non_resident_status >------|
625 -- ----------------------------------------------------------------------------
626 --
627 -- Description
628 -- This procedure is used to check that the lookup value is valid.
629 --
630 -- Pre Conditions
631 -- None.
632 --
633 -- In Parameters
634 -- emp_province_tax_inf_id PK of record being inserted or updated.
635 -- non_resident_status Value of lookup code.
636 -- effective_date effective date
637 -- object_version_number Object version number of record being
638 -- inserted or updated.
639 --
640 -- Post Success
641 -- Processing continues
642 --
643 -- Post Failure
644 -- Error handled by procedure
645 --
646 -- Access Status
647 -- Internal table handler use only.
648 --
649 Procedure chk_non_resident_status(p_emp_province_tax_inf_id in number,
650 p_non_resident_status in varchar2,
651 p_effective_date in date,
652 p_object_version_number in number) is
653 --
654 l_proc varchar2(72) := g_package||'chk_non_resident_status';
655 l_api_updating boolean;
656 --
657 Begin
658 --
659 hr_utility.set_location('Entering:'||l_proc, 5);
660 --
661 l_api_updating := pay_cpt_shd.api_updating
662 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
663 p_effective_date => p_effective_date,
664 p_object_version_number => p_object_version_number);
665 --
666 if (l_api_updating
667 and p_non_resident_status
668 <> nvl(pay_cpt_shd.g_old_rec.non_resident_status,hr_api.g_varchar2)
669 or not l_api_updating)
670 and p_non_resident_status is not null then
671 --
672 -- check if value of lookup falls within lookup type.
673 --
674 if hr_api.not_exists_in_hr_lookups
675 (p_lookup_type => 'YES_NO',
676 p_lookup_code => p_non_resident_status,
677 p_effective_date => p_effective_date) then
678 --
679 -- raise error as does not exist as lookup
680 --
681 hr_utility.set_message(800,'HR_NON_RESIDENT_STATUS_WRONG');
682 hr_utility.raise_error;
683 --
684 end if;
685 --
686 end if;
687 --
688 hr_utility.set_location('Leaving:'||l_proc,10);
689 --
690 end chk_non_resident_status;
691 -- ----------------------------------------------------------------------------
692 -- |------< chk_tax_calc_method >------|
693 -- ----------------------------------------------------------------------------
694 --
695 -- Description
696 -- This procedure is used to check that the lookup value is valid.
697 --
698 -- Pre Conditions
699 -- None.
700 --
701 -- In Parameters
702 -- emp_province_tax_inf_id PK of record being inserted or updated.
703 -- tax_calc_method Value of lookup code.
704 -- effective_date effective date
705 -- object_version_number Object version number of record being
706 -- inserted or updated.
707 --
708 -- Post Success
709 -- Processing continues
710 --
711 -- Post Failure
712 -- Error handled by procedure
713 --
714 -- Access Status
715 -- Internal table handler use only.
716 --
717 Procedure chk_tax_calc_method(p_emp_province_tax_inf_id in number,
718 p_tax_calc_method in varchar2,
719 p_effective_date in date,
720 p_object_version_number in number) is
721 --
725 Begin
722 l_proc varchar2(72) := g_package||'chk_tax_calc_method';
723 l_api_updating boolean;
724 --
726 --
727 hr_utility.set_location('Entering:'||l_proc, 5);
728 --
729 l_api_updating := pay_cpt_shd.api_updating
730 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
731 p_effective_date => p_effective_date,
732 p_object_version_number => p_object_version_number);
733 --
734 if (l_api_updating
735 and p_tax_calc_method
736 <> nvl(pay_cpt_shd.g_old_rec.tax_calc_method,hr_api.g_varchar2)
737 or not l_api_updating)
738 and p_tax_calc_method is not null then
739 --
740 -- check if value of lookup falls within lookup type.
741 --
742 if hr_api.not_exists_in_hr_lookups
743 (p_lookup_type => 'QC_TAX_CALC_METHOD',
744 p_lookup_code => p_tax_calc_method,
745 p_effective_date => p_effective_date) then
746 --
747 -- raise error as does not exist as lookup
748 --
749 hr_utility.set_message(800,'HR_QC_TAX_CALC_METHOD');
750 hr_utility.raise_error;
751 --
752 end if;
753 --
754 end if;
755 --
756 hr_utility.set_location('Leaving:'||l_proc,10);
757 --
758 end chk_tax_calc_method;
759 -- ----------------------------------------------------------------------------
760 -- |------< chk_province_code >------|
761 -- ----------------------------------------------------------------------------
762 --
763 -- Description
764 -- This procedure is used to check that the lookup value is valid.
765 --
766 -- Pre Conditions
767 -- None.
768 --
769 -- In Parameters
770 -- emp_province_tax_inf_id PK of record being inserted or updated.
771 -- province_code Value of lookup code.
772 -- effective_date effective date
773 -- object_version_number Object version number of record being
774 -- inserted or updated.
775 --
776 -- Post Success
777 -- Processing continues
778 --
779 -- Post Failure
780 -- Error handled by procedure
781 --
782 -- Access Status
783 -- Internal table handler use only.
784 --
785 Procedure chk_province_code(p_emp_province_tax_inf_id in number,
786 p_province_code in varchar2,
787 p_effective_date in date,
788 p_object_version_number in number) is
789 --
790 l_proc varchar2(72) := g_package||'chk_province_code';
791 l_api_updating boolean;
792 --
793 Begin
794 --
795 hr_utility.set_location('Entering:'||l_proc, 5);
796 --
797 l_api_updating := pay_cpt_shd.api_updating
798 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
799 p_effective_date => p_effective_date,
800 p_object_version_number => p_object_version_number);
801 --
802 if (l_api_updating ) then
803 if p_province_code <>
804 nvl(pay_cpt_shd.g_old_rec.province_code,hr_api.g_varchar2) then
805 hr_utility.set_message(800, 'HR_74030_PROVINCE_CODE_CHANGED');
806 hr_utility.raise_error;
807 end if;
808 end if;
809
810 if (l_api_updating
811 and p_province_code
812 <> nvl(pay_cpt_shd.g_old_rec.province_code,hr_api.g_varchar2)
813 or not l_api_updating)
814 and p_province_code is not null then
815 --
816 -- check if value of lookup falls within lookup type.
817 --
818 if hr_api.not_exists_in_hr_lookups
819 (p_lookup_type => 'CA_PROVINCE',
820 p_lookup_code => p_province_code,
821 p_effective_date => p_effective_date) then
822 --
823 -- raise error as does not exist as lookup
824 --
825 hr_utility.set_message(800,'HR_PROVINCE_CODE_WRONG');
826 hr_utility.raise_error;
827 --
828 end if;
829 --
830 end if;
831 --
832 hr_utility.set_location('Leaving:'||l_proc,10);
833 --
834 end chk_province_code;
835 -- ----------------------------------------------------------------------------
836 -- |------< chk_legislation_code >------|
837 -- ----------------------------------------------------------------------------
838 --
839 -- Description
840 -- This procedure is used to check that the lookup value is valid.
841 --
842 -- Pre Conditions
843 -- None.
844 --
845 -- In Parameters
846 -- emp_province_tax_inf_id PK of record being inserted or updated.
847 -- non_resident_status Value of lookup code.
848 -- effective_date effective date
849 -- object_version_number Object version number of record being
850 -- inserted or updated.
851 --
852 -- Post Success
853 -- Processing continues
854 --
855 -- Post Failure
856 -- Error handled by procedure
857 --
858 -- Access Status
859 -- Internal table handler use only.
860 --
861 Procedure chk_legislation_code(p_emp_province_tax_inf_id in number,
862 p_legislation_code in varchar2,
863 p_effective_date in date,
867 l_api_updating boolean;
864 p_object_version_number in number) is
865 --
866 l_proc varchar2(72) := g_package||'chk_legislation_code';
868 --
869 Begin
870 --
871 hr_utility.set_location('Entering:'||l_proc, 5);
872 --
873 l_api_updating := pay_cpt_shd.api_updating
874 (p_emp_province_tax_inf_id => p_emp_province_tax_inf_id,
875 p_effective_date => p_effective_date,
876 p_object_version_number => p_object_version_number);
877 --
878 if l_api_updating
879 and p_legislation_code
880 <> nvl(pay_cpt_shd.g_old_rec.legislation_code,hr_api.g_varchar2)
881 or not l_api_updating then
882 ---
883 if p_legislation_code is null then
884 -- raise error as legislation_code is NULL
885 --
886 hr_utility.set_message(800,'HR_74028_WRONG_LEGIS_CODE');
887 hr_utility.raise_error;
888 --
889 end if;
890 --
891 -- check if value of lookup falls within lookup type.
892 --
893 if p_legislation_code <> 'CA' then
894 -- raise error as legislation_code is not CA
895 --
896 hr_utility.set_message(800,'HR_74028_WRONG_LEGIS_CODE');
897 hr_utility.raise_error;
898 --
899 end if;
900 --
901 end if;
902 --
903 hr_utility.set_location('Leaving:'||l_proc,10);
904 --
905 end chk_legislation_code;
906 -- ----------------------------------------------------------------------------
907 -- |--------------------------< dt_update_validate >--------------------------|
908 -- ----------------------------------------------------------------------------
909 -- {Start Of Comments}
910 --
911 -- Description:
912 -- This procedure is used for referential integrity of datetracked
913 -- parent entities when a datetrack update operation is taking place
914 -- and where there is no cascading of update defined for this entity.
915 --
916 -- Prerequisites:
917 -- This procedure is called from the update_validate.
918 --
919 -- In Parameters:
920 --
921 -- Post Success:
922 -- Processing continues.
923 --
924 -- Post Failure:
925 --
926 -- Developer Implementation Notes:
927 -- This procedure should not need maintenance unless the HR Schema model
928 -- changes.
929 --
930 -- Access Status:
931 -- Internal Row Handler Use Only.
932 --
933 -- {End Of Comments}
934 -- ----------------------------------------------------------------------------
935 Procedure dt_update_validate
936 (p_assignment_id in number default hr_api.g_number,
937 p_datetrack_mode in varchar2,
938 p_validation_start_date in date,
939 p_validation_end_date in date) Is
940 --
941 l_proc varchar2(72) := g_package||'dt_update_validate';
942 l_integrity_error Exception;
943 l_table_name all_tables.table_name%TYPE;
944 --
945 Begin
946 hr_utility.set_location('Entering:'||l_proc, 5);
947 --
948 -- Ensure that the p_datetrack_mode argument is not null
949 --
950 hr_api.mandatory_arg_error
951 (p_api_name => l_proc,
952 p_argument => 'datetrack_mode',
953 p_argument_value => p_datetrack_mode);
954 --
955 -- Only perform the validation if the datetrack update mode is valid
956 --
957 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
958 --
959 --
960 -- Ensure the arguments are not null
961 --
962 hr_api.mandatory_arg_error
963 (p_api_name => l_proc,
964 p_argument => 'validation_start_date',
965 p_argument_value => p_validation_start_date);
966 --
967 hr_api.mandatory_arg_error
968 (p_api_name => l_proc,
969 p_argument => 'validation_end_date',
970 p_argument_value => p_validation_end_date);
971 --
972 If ((nvl(p_assignment_id, hr_api.g_number) <> hr_api.g_number) and
973 NOT (dt_api.check_min_max_dates
974 (p_base_table_name => 'per_all_assignments_f',
975 p_base_key_column => 'assignment_id',
976 p_base_key_value => p_assignment_id,
977 p_from_date => p_validation_start_date,
978 p_to_date => p_validation_end_date))) Then
979 l_table_name := 'all assignments';
980 Raise l_integrity_error;
981 End If;
982 --
983 End If;
984 --
985 hr_utility.set_location(' Leaving:'||l_proc, 10);
986 Exception
987 When l_integrity_error Then
988 --
989 -- A referential integrity check was violated therefore
990 -- we must error
991 --
992 hr_utility.set_message(800, 'HR_7216_DT_UPD_INTEGRITY_ERR');
993 hr_utility.set_message_token('TABLE_NAME', l_table_name);
994 hr_utility.raise_error;
995 When Others Then
996 --
997 -- An unhandled or unexpected error has occurred which
998 -- we must report
999 --
1000 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
1001 hr_utility.set_message_token('PROCEDURE', l_proc);
1002 hr_utility.set_message_token('STEP','15');
1003 hr_utility.raise_error;
1004 End dt_update_validate;
1008 -- ----------------------------------------------------------------------------
1005 --
1006 -- ----------------------------------------------------------------------------
1007 -- |--------------------------< dt_delete_validate >--------------------------|
1009 -- {Start Of Comments}
1010 --
1011 -- Description:
1012 -- This procedure is used for referential integrity of datetracked
1013 -- child entities when either a datetrack DELETE or ZAP is in operation
1014 -- and where there is no cascading of delete defined for this entity.
1015 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1016 -- datetracked child rows exist between the validation start and end
1017 -- dates.
1018 --
1019 -- Prerequisites:
1020 -- This procedure is called from the delete_validate.
1021 --
1022 -- In Parameters:
1023 --
1024 -- Post Success:
1025 -- Processing continues.
1026 --
1027 -- Post Failure:
1028 -- If a row exists by determining the returning Boolean value from the
1029 -- generic dt_api.rows_exist function then we must supply an error via
1030 -- the use of the local exception handler l_rows_exist.
1031 --
1032 -- Developer Implementation Notes:
1033 -- This procedure should not need maintenance unless the HR Schema model
1034 -- changes.
1035 --
1036 -- Access Status:
1037 -- Internal Row Handler Use Only.
1038 --
1039 -- {End Of Comments}
1040 -- ----------------------------------------------------------------------------
1041 Procedure dt_delete_validate
1042 (p_emp_province_tax_inf_id in number,
1043 p_datetrack_mode in varchar2,
1044 p_validation_start_date in date,
1045 p_validation_end_date in date) Is
1046 --
1047 l_proc varchar2(72) := g_package||'dt_delete_validate';
1048 l_rows_exist Exception;
1049 l_table_name all_tables.table_name%TYPE;
1050 --
1051 Begin
1052 hr_utility.set_location('Entering:'||l_proc, 5);
1053 --
1054 -- Ensure that the p_datetrack_mode argument is not null
1055 --
1056 hr_api.mandatory_arg_error
1057 (p_api_name => l_proc,
1058 p_argument => 'datetrack_mode',
1059 p_argument_value => p_datetrack_mode);
1060 --
1061 -- Only perform the validation if the datetrack mode is either
1062 -- DELETE or ZAP
1063 --
1064 If (p_datetrack_mode = 'DELETE' or
1065 p_datetrack_mode = 'ZAP') then
1066 --
1067 --
1068 -- Ensure the arguments are not null
1069 --
1070 hr_api.mandatory_arg_error
1071 (p_api_name => l_proc,
1072 p_argument => 'validation_start_date',
1073 p_argument_value => p_validation_start_date);
1074 --
1075 hr_api.mandatory_arg_error
1076 (p_api_name => l_proc,
1077 p_argument => 'validation_end_date',
1078 p_argument_value => p_validation_end_date);
1079 --
1080 hr_api.mandatory_arg_error
1081 (p_api_name => l_proc,
1082 p_argument => 'emp_province_tax_inf_id',
1083 p_argument_value => p_emp_province_tax_inf_id);
1084 --
1085 --
1086 --
1087 End If;
1088 --
1089 hr_utility.set_location(' Leaving:'||l_proc, 10);
1090 Exception
1091 When l_rows_exist Then
1092 --
1093 -- A referential integrity check was violated therefore
1094 -- we must error
1095 --
1096 hr_utility.set_message(800, 'HR_7215_DT_CHILD_EXISTS');
1097 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1098 hr_utility.raise_error;
1099 When Others Then
1100 --
1101 -- An unhandled or unexpected error has occurred which
1102 -- we must report
1103 --
1104 hr_utility.set_message(800, 'HR_6153_ALL_PROCEDURE_FAIL');
1105 hr_utility.set_message_token('PROCEDURE', l_proc);
1106 hr_utility.set_message_token('STEP','15');
1107 hr_utility.raise_error;
1108 End dt_delete_validate;
1109 --
1110 -- ----------------------------------------------------------------------------
1111 -- |---------------------------< insert_validate >----------------------------|
1112 -- ----------------------------------------------------------------------------
1113 Procedure insert_validate
1114 (p_rec in pay_cpt_shd.g_rec_type,
1115 p_effective_date in date,
1116 p_datetrack_mode in varchar2,
1117 p_validation_start_date in date,
1118 p_validation_end_date in date) is
1119 --
1120 l_proc varchar2(72) := g_package||'insert_validate';
1121 --
1122 Begin
1123 hr_utility.set_location('Entering:'||l_proc, 5);
1124 --
1125 -- Call all supporting business operations
1126 --
1127 chk_emp_province_tax_inf_id
1128 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1129 p_effective_date => p_effective_date,
1130 p_object_version_number => p_rec.object_version_number);
1131 --
1132 chk_legislation_code
1133 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1134 p_legislation_code => p_rec.legislation_code,
1135 p_effective_date => p_effective_date,
1136 p_object_version_number => p_rec.object_version_number);
1137 --
1138 chk_assignment_id
1139 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id
1140 ,p_assignment_id => p_rec.assignment_id
1141 ,p_business_group_id => p_rec.business_group_id
1145 --
1142 ,p_effective_date => p_effective_date
1143 ,p_object_version_number => p_rec.object_version_number
1144 );
1146 chk_province_code
1147 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1148 p_province_code => p_rec.province_code,
1149 p_effective_date => p_effective_date,
1150 p_object_version_number => p_rec.object_version_number);
1151 --
1152 chk_wc_exempt_flag
1153 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1154 p_wc_exempt_flag => p_rec.wc_exempt_flag,
1155 p_effective_date => p_effective_date,
1156 p_object_version_number => p_rec.object_version_number);
1157 --
1158 chk_pmed_exempt_flag
1159 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1160 p_pmed_exempt_flag => p_rec.pmed_exempt_flag,
1161 p_effective_date => p_effective_date,
1162 p_object_version_number => p_rec.object_version_number);
1163 --
1164 chk_prov_exempt_flag
1165 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1166 p_prov_exempt_flag => p_rec.prov_exempt_flag,
1167 p_effective_date => p_effective_date,
1168 p_object_version_number => p_rec.object_version_number);
1169 --
1170 chk_basic_exemption_flag
1171 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1172 p_basic_exemption_flag => p_rec.basic_exemption_flag,
1173 p_tax_credit_amount => p_rec.tax_credit_amount,
1174 p_province_code => p_rec.province_code,
1175 p_effective_date => p_effective_date,
1176 p_object_version_number => p_rec.object_version_number);
1177 --
1178 chk_marriage_status
1179 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1180 p_marriage_status => p_rec.marriage_status,
1181 p_effective_date => p_effective_date,
1182 p_object_version_number => p_rec.object_version_number);
1183 --
1184 chk_non_resident_status
1185 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1186 p_non_resident_status => p_rec.non_resident_status,
1187 p_effective_date => p_effective_date,
1188 p_object_version_number => p_rec.object_version_number);
1189 --
1190 chk_disability_status
1191 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1192 p_disability_status => p_rec.disability_status,
1193 p_effective_date => p_effective_date,
1194 p_object_version_number => p_rec.object_version_number);
1195 --
1196 chk_tax_calc_method
1197 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1198 p_tax_calc_method => p_rec.tax_calc_method,
1199 p_effective_date => p_effective_date,
1200 p_object_version_number => p_rec.object_version_number);
1201 --
1202 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1203 --
1204 hr_utility.set_location(' Leaving:'||l_proc, 10);
1205 End insert_validate;
1206 --
1207 -- ----------------------------------------------------------------------------
1208 -- |---------------------------< update_validate >----------------------------|
1209 -- ----------------------------------------------------------------------------
1210 Procedure update_validate
1211 (p_rec in pay_cpt_shd.g_rec_type,
1212 p_effective_date in date,
1213 p_datetrack_mode in varchar2,
1214 p_validation_start_date in date,
1215 p_validation_end_date in date) is
1216 --
1217 l_proc varchar2(72) := g_package||'update_validate';
1218 --
1219 Begin
1220 hr_utility.set_location('Entering:'||l_proc, 5);
1221 --
1222 -- Call all supporting business operations
1223 --
1224 chk_emp_province_tax_inf_id
1225 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1226 p_effective_date => p_effective_date,
1227 p_object_version_number => p_rec.object_version_number);
1228 --
1229 chk_legislation_code
1230 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1231 p_legislation_code => p_rec.legislation_code,
1232 p_effective_date => p_effective_date,
1233 p_object_version_number => p_rec.object_version_number);
1234 --
1235 chk_assignment_id
1236 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id
1237 ,p_assignment_id => p_rec.assignment_id
1238 ,p_business_group_id => p_rec.business_group_id
1239 ,p_effective_date => p_effective_date
1240 ,p_object_version_number => p_rec.object_version_number
1241 );
1242 --
1243 chk_province_code
1244 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1245 p_province_code => p_rec.province_code,
1246 p_effective_date => p_effective_date,
1247 p_object_version_number => p_rec.object_version_number);
1248 --
1249 chk_wc_exempt_flag
1250 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1251 p_wc_exempt_flag => p_rec.wc_exempt_flag,
1252 p_effective_date => p_effective_date,
1253 p_object_version_number => p_rec.object_version_number);
1254 --
1255 chk_pmed_exempt_flag
1256 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1257 p_pmed_exempt_flag => p_rec.pmed_exempt_flag,
1258 p_effective_date => p_effective_date,
1262 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1259 p_object_version_number => p_rec.object_version_number);
1260 --
1261 chk_prov_exempt_flag
1263 p_prov_exempt_flag => p_rec.prov_exempt_flag,
1264 p_effective_date => p_effective_date,
1265 p_object_version_number => p_rec.object_version_number);
1266 --
1267 chk_basic_exemption_flag
1268 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1269 p_basic_exemption_flag => p_rec.basic_exemption_flag,
1270 p_tax_credit_amount => p_rec.tax_credit_amount,
1271 p_province_code => p_rec.province_code,
1272 p_effective_date => p_effective_date,
1273 p_object_version_number => p_rec.object_version_number);
1274 --
1275 chk_marriage_status
1276 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1277 p_marriage_status => p_rec.marriage_status,
1278 p_effective_date => p_effective_date,
1279 p_object_version_number => p_rec.object_version_number);
1280 --
1281 chk_non_resident_status
1282 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1283 p_non_resident_status => p_rec.non_resident_status,
1284 p_effective_date => p_effective_date,
1285 p_object_version_number => p_rec.object_version_number);
1286 --
1287 chk_disability_status
1288 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1289 p_disability_status => p_rec.disability_status,
1290 p_effective_date => p_effective_date,
1291 p_object_version_number => p_rec.object_version_number);
1292 --
1293 chk_tax_calc_method
1294 (p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id,
1295 p_tax_calc_method => p_rec.tax_calc_method,
1296 p_effective_date => p_effective_date,
1297 p_object_version_number => p_rec.object_version_number);
1298 --
1299 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1300 --
1301 -- Call the datetrack update integrity operation
1302 --
1303 dt_update_validate
1304 (p_assignment_id => p_rec.assignment_id,
1305 p_datetrack_mode => p_datetrack_mode,
1306 p_validation_start_date => p_validation_start_date,
1307 p_validation_end_date => p_validation_end_date);
1308 --
1309 hr_utility.set_location(' Leaving:'||l_proc, 10);
1310 End update_validate;
1311 --
1312 -- ----------------------------------------------------------------------------
1313 -- |---------------------------< delete_validate >----------------------------|
1314 -- ----------------------------------------------------------------------------
1315 Procedure delete_validate
1316 (p_rec in pay_cpt_shd.g_rec_type,
1317 p_effective_date in date,
1318 p_datetrack_mode in varchar2,
1319 p_validation_start_date in date,
1320 p_validation_end_date in date) is
1321
1322 -- commented csr_assigned cursor definition and redifined below, bug 6059473.
1323 -- while checking whether any PAYROLL is execute, we have to consider
1324 -- that we cannot terminate tax record if Final Process Date is
1325 -- less than DATE EARNED.
1326
1327 /* cursor csr_assigned is
1328 select PA.context_value from
1329 pay_action_contexts PA,
1330 ff_contexts C,
1331 pay_ca_emp_prov_tax_info_f PR
1332 where C.context_id = PA.context_id
1333 and C.context_name = 'JURISDICTION_CODE'
1334 and PA.context_value = PR.province_code
1335 and PA.assignment_id = PR.assignment_id
1336 and PR.emp_province_tax_inf_id = p_rec.emp_province_tax_inf_id; */
1337
1338
1339 cursor csr_assigned(p_csr_tmp_date in date) is
1340 select PA.context_value from
1341 pay_action_contexts PA,
1342 ff_contexts C,
1343 pay_ca_emp_prov_tax_info_f PR,
1344 pay_assignment_actions paa,
1345 per_assignments_f paf
1346 where C.context_id = PA.context_id
1347 and C.context_name = 'JURISDICTION_CODE'
1348 and PA.context_value = PR.province_code
1349 and PA.assignment_id = PR.assignment_id
1350 and PR.emp_province_tax_inf_id = p_rec.emp_province_tax_inf_id
1351 and paf.assignment_id = PR.assignment_id
1352 and paf.assignment_id = paa.assignment_id
1353 and exists (select null
1354 from pay_payroll_actions ppa,
1355 pay_payrolls_f ppf
1356 where ppa.payroll_action_id = paa.payroll_action_id
1357 and ppa.action_type in ('Q','R')
1358 and ppa.date_earned > p_csr_tmp_date
1359 and ppa.payroll_id = ppf.payroll_id
1360 and ppa.effective_date between ppf.effective_start_date
1361 and ppf.effective_end_date
1362 and ppf.payroll_id > 0
1363 and ppf.payroll_id = paf.payroll_id
1364 );
1365
1366 l_proc varchar2(72) := g_package||'delete_validate';
1367 l_prov varchar2(30);
1368
1369 --l_effective_date variable added by sneelapa, bug 6059473
1370 l_effective_date date;
1371
1372 --
1373 Begin
1374 hr_utility.set_location('Entering:'||l_proc, 5);
1375 --
1376 -- Call all supporting business operations
1377 --
1378 l_effective_date := trunc(p_effective_date);
1379
1380 open csr_assigned(l_effective_date);
1381 fetch csr_assigned into l_prov;
1382
1383 if csr_assigned%NOTFOUND then
1384 hr_utility.set_location(l_proc, 6);
1385 dt_delete_validate
1386 (p_datetrack_mode => p_datetrack_mode,
1387 p_validation_start_date => p_validation_start_date,
1388 p_validation_end_date => p_validation_end_date,
1389 p_emp_province_tax_inf_id => p_rec.emp_province_tax_inf_id);
1390 hr_utility.set_location(l_proc, 7);
1391 else
1392 hr_utility.set_location(l_proc, 9);
1393 hr_utility.set_message(800,'HR_74039_CANNOT_PURGE_PROV');
1394 hr_utility.set_message_token('PROVINCECODE', l_prov);
1395 hr_utility.raise_error;
1396 end if;
1397
1398 close csr_assigned;
1399
1400 hr_utility.set_location(' Leaving:'||l_proc, 10);
1401
1402 End delete_validate;
1403 --
1404 --
1405 -- ---------------------------------------------------------------------------
1406 -- |---------------------< return_legislation_code >-------------------------|
1407 -- ---------------------------------------------------------------------------
1408 --
1409 function return_legislation_code
1410 (p_emp_province_tax_inf_id in number) return varchar2 is
1411 --
1412 -- Declare cursor
1413 --
1414 cursor csr_leg_code is
1415 select a.legislation_code
1416 from per_business_groups a,
1417 pay_ca_emp_prov_tax_info_f b
1418 where b.emp_province_tax_inf_id = p_emp_province_tax_inf_id
1419 and a.business_group_id = b.business_group_id;
1420 --
1421 -- Declare local variables
1422 --
1423 l_legislation_code varchar2(150);
1424 l_proc varchar2(72) := g_package||'return_legislation_code';
1425 --
1426 begin
1427 --
1428 hr_utility.set_location('Entering:'|| l_proc, 10);
1429 --
1430 -- Ensure that all the mandatory parameter are not null
1431 --
1432 hr_api.mandatory_arg_error(p_api_name => l_proc,
1433 p_argument => 'emp_province_tax_inf_id',
1434 p_argument_value => p_emp_province_tax_inf_id);
1435 --
1436 open csr_leg_code;
1437 --
1438 fetch csr_leg_code into l_legislation_code;
1439 --
1440 if csr_leg_code%notfound then
1441 --
1442 close csr_leg_code;
1443 --
1444 -- The primary key is invalid therefore we must error
1445 --
1446 hr_utility.set_message(800,'HR_7220_INVALID_PRIMARY_KEY');
1447 hr_utility.raise_error;
1448 --
1449 end if;
1450 --
1451 close csr_leg_code;
1452 --
1453 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1454 --
1455 return l_legislation_code;
1456 --
1457 end return_legislation_code;
1458 --
1459 end pay_cpt_bus;