1 Package Body pay_ipd_bus as
2 /* $Header: pyipdrhi.pkb 120.4 2011/11/09 12:41:18 rsahai ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ipd_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_paye_details_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_paye_details_id in number
22 ) is
23 --
24 -- Declare cursor
25 --
26 cursor csr_sec_grp is
27 select pbg.security_group_id
28 from per_business_groups pbg
29 , pay_ie_paye_details_f ipd
30 , per_all_assignments_f paa
31 where ipd.paye_details_id = p_paye_details_id
32 and ipd.assignment_id = paa.assignment_id
33 and paa.business_group_id = pbg.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 --
40 begin
41 --
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43 --
44 -- Ensure that all the mandatory parameter are not null
45 --
46 hr_api.mandatory_arg_error
47 (p_api_name => l_proc
48 ,p_argument => 'paye_details_id'
49 ,p_argument_value => p_paye_details_id
50 );
51 --
52 open csr_sec_grp;
53 fetch csr_sec_grp into l_security_group_id;
54 --
55 if csr_sec_grp%notfound then
56 --
57 close csr_sec_grp;
58 --
59 -- The primary key is invalid therefore we must error
60 --
61 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
62 fnd_message.raise_error;
63 --
64 end if;
65 close csr_sec_grp;
66 --
67 -- Set the security_group_id in CLIENT_INFO
68 --
69 hr_api.set_security_group_id
70 (p_security_group_id => l_security_group_id
71 );
72 --
73 hr_utility.set_location(' Leaving:'|| l_proc, 20);
74 --
75 end set_security_group_id;
76 --
77 -- ---------------------------------------------------------------------------
78 -- |---------------------< return_legislation_code >-------------------------|
79 -- ---------------------------------------------------------------------------
80 --
81 Function return_legislation_code
82 (p_paye_details_id in number
83 )
84 Return Varchar2 Is
85 --
86 -- Declare cursor
87 --
88 cursor csr_leg_code is
89 select pbg.legislation_code
90 from per_business_groups pbg
91 , pay_ie_paye_details_f ipd
92 , per_all_assignments_f paa
93 where ipd.paye_details_id = p_paye_details_id
94 and ipd.assignment_id = paa.assignment_id
95 and paa.business_group_id = pbg.business_group_id;
96 --
97 -- Declare local variables
98 --
99 l_legislation_code varchar2(150);
100 l_proc varchar2(72) := g_package||'return_legislation_code';
101 --
102 Begin
106 -- Ensure that all the mandatory parameter are not null
103 --
104 hr_utility.set_location('Entering:'|| l_proc, 10);
105 --
107 --
108 hr_api.mandatory_arg_error
109 (p_api_name => l_proc
110 ,p_argument => 'paye_details_id'
111 ,p_argument_value => p_paye_details_id
112 );
113 --
114 if ( nvl(pay_ipd_bus.g_paye_details_id, hr_api.g_number)
115 = p_paye_details_id) then
116 --
117 -- The legislation code has already been found with a previous
118 -- call to this function. Just return the value in the global
119 -- variable.
120 --
121 l_legislation_code := pay_ipd_bus.g_legislation_code;
122 hr_utility.set_location(l_proc, 20);
123 else
124 --
125 -- The ID is different to the last call to this function
126 -- or this is the first call to this function.
127 --
128 open csr_leg_code;
129 fetch csr_leg_code into l_legislation_code;
130 --
131 if csr_leg_code%notfound then
132 --
133 -- The primary key is invalid therefore we must error
134 --
135 close csr_leg_code;
136 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
137 fnd_message.raise_error;
138 end if;
139 hr_utility.set_location(l_proc,30);
140 --
141 -- Set the global variables so the values are
142 -- available for the next call to this function.
143 --
144 close csr_leg_code;
145 pay_ipd_bus.g_paye_details_id := p_paye_details_id;
146 pay_ipd_bus.g_legislation_code := l_legislation_code;
147 end if;
148 hr_utility.set_location(' Leaving:'|| l_proc, 40);
149 return l_legislation_code;
150 end return_legislation_code;
151 --
152 -- ----------------------------------------------------------------------------
153 -- |-----------------------< chk_non_updateable_args >------------------------|
154 -- ----------------------------------------------------------------------------
155 -- {Start Of Comments}
156 --
157 -- Description:
158 -- This procedure is used to ensure that non updateable attributes have
159 -- not been updated. If an attribute has been updated an error is generated.
160 --
161 -- Pre Conditions:
162 -- g_old_rec has been populated with details of the values currently in
163 -- the database.
164 --
165 -- In Arguments:
166 -- p_rec has been populated with the updated values the user would like the
167 -- record set to.
168 --
169 -- Post Success:
170 -- Processing continues if all the non updateable attributes have not
171 -- changed.
172 --
173 -- Post Failure:
174 -- An application error is raised if any of the non updatable attributes
175 -- have been altered.
176 --
177 -- {End Of Comments}
178 -- ----------------------------------------------------------------------------
179 Procedure chk_non_updateable_args
180 (p_effective_date in date
181 ,p_rec in pay_ipd_shd.g_rec_type
182 ) IS
183 --
184 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
185 l_error EXCEPTION;
186 l_argument varchar2(30);
187 --
188 Begin
189 --
190 -- Only proceed with the validation if a row exists for the current
191 -- record in the HR Schema.
192 --
193 IF NOT pay_ipd_shd.api_updating
194 (p_paye_details_id => p_rec.paye_details_id
195 ,p_effective_date => p_effective_date
196 ,p_object_version_number => p_rec.object_version_number
197 ) THEN
198 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
199 fnd_message.set_token('PROCEDURE ', l_proc);
200 fnd_message.set_token('STEP ', '5');
201 fnd_message.raise_error;
202 END IF;
203 --
204 IF nvl(p_rec.assignment_id, hr_api.g_number) <>
205 nvl(pay_ipd_shd.g_old_rec.assignment_id, hr_api.g_number) THEN
206 l_argument := 'assignment_id';
207 raise l_error;
208 END IF;
209 --
210 IF nvl(p_rec.comm_period_no, hr_api.g_number) <>
211 nvl(pay_ipd_shd.g_old_rec.comm_period_no, hr_api.g_number) THEN
212 l_argument := 'comm_period_no';
213 raise l_error;
214 END IF;
215 --
216 EXCEPTION
217 WHEN l_error THEN
218 hr_api.argument_changed_error
219 (p_api_name => l_proc
220 ,p_argument => l_argument);
221 WHEN OTHERS THEN
222 RAISE;
223 End chk_non_updateable_args;
224 --
225 -- ---------------------------------------------------------------------------
226 -- |---------------------< chk_assignment_id >-------------------------|
227 -- ---------------------------------------------------------------------------
228 -- {Start Of Comments}
229 --
230 -- Description:
231 -- check if assignment already exists and valid as of the effectuve date
232 --
233 -- Prerequisites:
234 --
235 -- In Arguments:
236 -- p_effective_date
237 -- p_assignment_id
238 --
239 --
240 -- Post Success:
241 -- processing continues as no error is raised.
242 --
243 -- Post Failure:
244 -- An error is raised if the assignment does not exist.
245 --
246 -- Access Status:
247 -- Internal Development Use Only.
248 --
249 -- {End Of Comments}
250 -- ---------------------------------------------------------------------------
251 PROCEDURE chk_assignment_id
252 (p_effective_date IN DATE
256 CURSOR csr_assignment IS
253 , p_assignment_id IN NUMBER
254 ) IS
255 -- select payroll_id if assignment id exists
257 SELECT payroll_id
258 FROM per_all_assignments_f
259 WHERE assignment_id = p_assignment_id
260 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
261 --
262 l_payroll_id NUMBER;
263 BEGIN
264 OPEN csr_assignment;
265 FETCH csr_assignment INTO l_payroll_id;
266 IF csr_assignment%NOTFOUND THEN
267 CLOSE csr_assignment;
268 fnd_message.set_name('PAY','HR_IE_ASG_NOT_EXISTS');
269 fnd_message.raise_error;
270 END IF;
271 --
272 IF l_payroll_id IS NULL THEN
273 CLOSE csr_assignment;
274 fnd_message.set_name('PAY','HR_IE_ASG_NOT_IN_PAYROLL');
275 fnd_message.raise_error;
276 END IF;
277 CLOSE csr_assignment;
278 END chk_assignment_id;
279 --
280 --
281 -- ---------------------------------------------------------------------------
282 -- |---------------------< chk_info_source >-------------------------|
283 -- ---------------------------------------------------------------------------
284 -- {Start Of Comments}
285 --
286 -- Description:
287 -- check if info_source already exists in lookup_type IE_PAYE_INFO_SOURCE
288 --
289 -- Prerequisites:
290 --
291 -- In Arguments:
292 -- p_effective_date
293 -- p_info_source
294 --
295 --
296 -- Post Success:
297 -- processing continues as no error is raised.
298 --
299 -- Post Failure:
300 -- An error is raised if the info_source does not exist.
301 --
302 -- Access Status:
303 -- Internal Development Use Only.
304 --
305 -- {End Of Comments}
306 -- ---------------------------------------------------------------------------
307 PROCEDURE chk_info_source
308 (p_effective_date IN DATE
309 , p_info_source IN VARCHAR2
310 ) IS
311 -- select wehre info_source exists
312 CURSOR csr_info_source IS
313 SELECT NULL
314 FROM hr_lookups
315 WHERE lookup_code = p_info_source
316 AND lookup_type = 'IE_PAYE_INFO_SOURCE'
317 AND enabled_flag = 'Y'
318 AND p_effective_date BETWEEN nvl(start_date_active, p_effective_date) AND nvl(end_date_active, p_effective_Date);
319 --
320 l_exists VARCHAR2(1);
321 BEGIN
322 OPEN csr_info_source;
323 FETCH csr_info_source INTO l_exists;
324 IF csr_info_source%NOTFOUND THEN
325 CLOSE csr_info_source;
326 fnd_message.set_name('PAY','HR_IE_INFO_SOURCE_INVALID');
327 fnd_message.set_token('INFO_SOURCE', p_info_source);
328 fnd_message.raise_error;
329 END IF;
330 --
331 CLOSE csr_info_source;
332 END chk_info_source;
333 --
334 --
335 -- ---------------------------------------------------------------------------
336 -- |---------------------< get_comm_period_no >-------------------------|
337 -- ---------------------------------------------------------------------------
338 -- {Start Of Comments}
339 --
340 -- Description:
341 -- Derive value of comm_period_no
342 --
343 -- Prerequisites:
344 --
345 -- In Arguments:
346 -- p_effective_date
347 -- p_assignemnt_id
348 --
349 --
350 -- Post Success:
351 -- returns value of commencement period number.
352 --
353 -- Post Failure:
354 --
355 --
356 -- Access Status:
357 -- Internal Development Use Only.
358 --
359 -- {End Of Comments}
360 -- ---------------------------------------------------------------------------
361 FUNCTION get_comm_period_no
362 ( p_effective_date IN DATE
363 , p_assignment_id IN NUMBER ) RETURN NUMBER IS
364 --
365 l_comm_period_no NUMBER;
366 -- select original hire date of the employee
367 CURSOR csr_orig_hire_date IS
368 SELECT pap.original_date_of_hire, paa.payroll_id
369 FROM per_all_people_f pap, per_all_assignments paa
370 WHERE paa.assignment_id = p_assignment_id
371 AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
372 AND pap.person_id = paa.person_id
373 AND p_effective_Date BETWEEN pap.effective_start_date AND pap.effective_end_date;
374 --
375 orig_hire_date_rec csr_orig_hire_date%ROWTYPE;
376 --
377 CURSOR csr_period_num(l_date IN DATE, l_payroll_id IN NUMBER) IS
378 SELECT ptp.period_num , ptp.start_date
379 FROM per_time_periods ptp, pay_all_payrolls_f pap
380 WHERE ptp.payroll_id = l_payroll_id
381 AND l_date BETWEEN ptp.start_date AND ptp.end_Date
382 AND ptp.period_type = pap.period_type
383 AND pap.payroll_id = l_payroll_id
384 AND p_effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
385 --
386 period_num_rec csr_period_num%ROWTYPE;
387 --
388 BEGIN
389 -- Get original date of hire
390 OPEN csr_orig_hire_date;
391 FETCH csr_orig_hire_date INTO orig_hire_date_rec;
392 CLOSE csr_orig_hire_date;
393 --
394 IF to_date('01-JAN-'||to_char(p_effective_date,'YYYY'),'DD/MM/YYYY') < orig_hire_date_rec.original_date_of_hire THEN
395 -- Get pay period number in which person was hired originaly
396 OPEN csr_period_num(orig_hire_date_rec.original_date_of_hire, orig_hire_date_rec.payroll_id);
397 FETCH csr_period_num INTO period_num_rec;
398 CLOSE csr_period_num;
399 l_comm_period_no := nvl(period_num_rec.period_num,1);
400 ELSE
401 -- Set commencement pay period number to first pay period
402 l_comm_period_no := 1;
403 END IF;
404 --
405 RETURN l_comm_period_no;
406 END get_comm_period_no;
407
408 --
409 -- ---------------------------------------------------------------------------
410 -- |---------------------< chk_comm_period_no >-------------------------|
411 -- ---------------------------------------------------------------------------
412 -- {Start Of Comments}
413 --
414 -- Description:
415 -- check if comm_period_no is valid
416 --
417 -- Prerequisites:
418 --
419 -- In Arguments:
420 -- p_effective_date
421 -- p_comm_period_no
422 -- p_assignment_id
423 --
424 --
425 -- Post Success:
426 -- processing continues as no error is raised.
427 --
428 -- Post Failure:
429 -- An error is raised if the comm_period_no is not valid
430 --
431 -- Access Status:
432 -- Internal Development Use Only.
433 --
434 -- {End Of Comments}
435 -- ---------------------------------------------------------------------------
436 PROCEDURE chk_comm_period_no
437 (p_effective_date IN DATE
438 , p_comm_period_no IN NUMBER
439 , p_assignment_id IN NUMBER
440 ) IS
441 l_comm_period_no NUMBER;
442 BEGIN
443 l_comm_period_no := get_comm_period_no( p_effective_date => p_effective_date,
444 p_assignment_id => p_assignment_id );
445 IF l_comm_period_no <> p_comm_period_no THEN
446 fnd_message.set_name('PAY','HR_IE_COMM_PERIOD_NO_INVALID');
447 fnd_message.raise_error;
448 END IF;
449 --
450 END chk_comm_period_no;
451 --
452 -- ---------------------------------------------------------------------------
453 -- |---------------------< chk_tax_basis >-------------------------|
454 -- ---------------------------------------------------------------------------
455 -- {Start Of Comments}
456 --
457 -- Description:
458 -- check if tax_basis already exists in lookup_type IE_PAYE_TAX_BASIS
459 --
460 -- Prerequisites:
461 --
462 -- In Arguments:
463 -- p_effective_date
464 -- p_tax_basis
465 --
466 --
467 -- Post Success:
468 -- processing continues as no error is raised.
469 --
470 -- Post Failure:
471 -- An error is raised if the info_source does not exist.
472 --
473 -- Access Status:
474 -- Internal Development Use Only.
475 --
476 -- {End Of Comments}
477 -- ---------------------------------------------------------------------------
478 PROCEDURE chk_tax_basis
479 (p_effective_date IN DATE
480 , p_tax_basis IN VARCHAR2
481 ) IS
482 -- select wehre tax_basis exists
483 CURSOR csr_tax_basis IS
484 SELECT NULL
485 FROM hr_lookups
486 WHERE lookup_code = p_tax_basis
487 AND lookup_type = 'IE_PAYE_TAX_BASIS'
488 AND enabled_flag = 'Y'
489 AND p_effective_date BETWEEN nvl(start_date_active, p_effective_date) AND nvl(end_date_active, p_effective_Date);
490 --
491 l_exists VARCHAR2(1);
492 BEGIN
493 OPEN csr_tax_basis;
494 FETCH csr_tax_basis INTO l_exists;
495 IF csr_tax_basis%NOTFOUND THEN
496 CLOSE csr_tax_basis;
497 fnd_message.set_name('PAY','HR_IE_TAX_BASIS_INVALID');
498 fnd_message.set_token('TAX_BASIS', p_tax_basis);
499 fnd_message.raise_error;
500 END IF;
501 --
502 CLOSE csr_tax_basis;
503 END chk_tax_basis;
504 --
505 --
506 -- ---------------------------------------------------------------------------
507 -- |---------------------< chk_tax_assess_basis >-------------------------|
508 -- ---------------------------------------------------------------------------
509 -- {Start Of Comments}
510 --
511 -- Description:
512 -- check if tax_assess_basis already exists in lookup_type IE_PAYE_ASSESS_BASIS
513 --
514 -- Prerequisites:
515 --
516 -- In Arguments:
517 -- p_effective_date
518 -- p_tax_assess_basis
519 --
520 --
521 -- Post Success:
522 -- processing continues as no error is raised.
523 --
524 -- Post Failure:
525 -- An error is raised if the info_source does not exist.
526 --
527 -- Access Status:
528 -- Internal Development Use Only.
529 --
530 -- {End Of Comments}
531 -- ---------------------------------------------------------------------------
532 PROCEDURE chk_tax_assess_basis
533 (p_effective_date IN DATE
534 , p_tax_assess_basis IN VARCHAR2
535 ) IS
536 -- select wehre tax_assess_basis exists
537 CURSOR csr_tax_assess_basis IS
538 SELECT NULL
539 FROM hr_lookups
540 WHERE lookup_code = p_tax_assess_basis
541 AND lookup_type = 'IE_PAYE_ASSESS_BASIS'
542 AND enabled_flag = 'Y'
543 AND p_effective_date BETWEEN nvl(start_date_active, p_effective_date) AND nvl(end_date_active, p_effective_Date);
544 --
545 l_exists VARCHAR2(1);
546 BEGIN
547 OPEN csr_tax_assess_basis;
548 FETCH csr_tax_assess_basis INTO l_exists;
549 IF csr_tax_assess_basis%NOTFOUND THEN
550 CLOSE csr_tax_assess_basis;
551 fnd_message.set_name('PAY','HR_IE_TAX_ASSESS_BASIS_INVALID');
552 fnd_message.set_token('TAX_ASSESS_BASIS', p_tax_assess_basis);
553 fnd_message.raise_error;
554 END IF;
555 --
556 CLOSE csr_tax_assess_basis;
557 END chk_tax_assess_basis;
558
559 --
560 --
561 -- ---------------------------------------------------------------------------
562 -- |---------------------< chk_cert_start_end_dates >-------------------------|
563 -- ---------------------------------------------------------------------------
564 -- {Start Of Comments}
565 --
566 -- Description:
567 -- check if certificate start dates is before or equal to certificate end date
568 --
569 -- Prerequisites:
570 --
571 -- In Arguments:
572 -- p_certificate_start_date
573 -- p_certificate_end_date
574 --
575 --
576 -- Post Success:
577 -- processing continues as no error is raised.
578 --
579 -- Post Failure:
580 -- An error is raised if the certificate_start_date is after
581 -- certificate_end_Date
582 --
583 -- Access Status:
584 -- Internal Development Use Only.
585 --
586 -- {End Of Comments}
587 -- ---------------------------------------------------------------------------
588 PROCEDURE chk_cert_start_end_dates
589 (p_certificate_start_date IN DATE
590 , p_certificate_end_date IN DATE
591 ) IS
592 BEGIN
593 IF p_certificate_start_date > nvl(p_certificate_end_date, p_certificate_start_date+1) THEN
594 fnd_message.set_name('PAY', 'HR_IE_START_END_DATES');
595 fnd_message.set_token('START_DATE', to_char(p_certificate_start_date, 'DD-MON-YYYY'));
596 fnd_message.set_token('END_DATE', to_char(p_certificate_end_date, 'DD-MON-YYYY'));
597 fnd_message.raise_error;
598 END IF;
599 END chk_cert_start_end_Dates;
600 --
601 --
602 -- ---------------------------------------------------------------------------
603 -- |---------------------< chk_duplicate_record >-------------------------|
604 -- ---------------------------------------------------------------------------
605 -- {Start Of Comments}
606 --
607 -- Description:
608 -- check if PAYE record already exists for the assignment
609 --
610 -- Prerequisites:
611 --
612 -- In Arguments:
613 -- p_assignment_id
614 --
615 --
619 --
616 --
617 -- Post Success:
618 -- processing continues as no error is raised.
620 -- Post Failure:
621 -- An error is raised if PAYE record already exists for the assignment
622 --
623 -- Access Status:
624 -- Internal Development Use Only.
625 --
626 -- {End Of Comments}
627 -- ---------------------------------------------------------------------------
628 PROCEDURE chk_duplicate_record
629 ( p_assignment_id IN NUMBER
630 , p_validation_start_date DATE
631 , p_validation_end_date DATE --Bug 4154171
632 ) IS
633 -- Select existing PAYE details
634 CURSOR csr_paye_details IS
635 SELECT min(ipd.effective_start_date) min_start_date
636 FROM pay_ie_paye_details_f ipd
637 WHERE ipd.assignment_id = p_assignment_id
638 and (ipd.effective_start_date between p_validation_start_date and p_validation_end_date
639 OR ipd.effective_end_date between p_validation_start_date and p_validation_end_date
640 OR p_validation_start_date BETWEEN ipd.effective_start_date and ipd.effective_end_date
641 OR p_validation_end_date BETWEEN ipd.effective_start_date and ipd.effective_end_date) ;
642
643
644 --
645 paye_details_rec csr_paye_details%ROWTYPE;
646 BEGIN
647 OPEN csr_paye_details;
648 FETCH csr_paye_details INTO paye_details_rec;
649 --
650 IF paye_details_rec.min_start_date is not null THEN
651 CLOSE csr_paye_details;
652 fnd_message.set_name('PAY', 'HR_IE_PAYE_DETAILS_EXIST');
653 fnd_message.set_token('START_DATE', to_char(paye_details_rec.min_start_date, 'DD-MON-YYYY'));
654 fnd_message.raise_error;
655 END IF;
656 --
657 CLOSE csr_paye_details;
658 END chk_duplicate_record;
659 --
660 --
661 -- ---------------------------------------------------------------------------
662 -- |---------------------< chk_tax_basis_amounts >-------------------------|
663 -- ---------------------------------------------------------------------------
664 -- {Start Of Comments}
665 --
666 -- Description:
667 -- check if amounts are valid for the given tax basis, for 'Emergency'
668 -- tax basis weekly and monthly tax credits ans std rate cut-off amounts must
669 -- be null and for other values of tax basis weekly or monthly amounts
670 -- (depending on payroll frequency) must be not null.
671 --
672 -- Prerequisites:
673 --
674 -- In Arguments:
675 -- p_effective_date
676 -- p_assignment_id
677 -- p_tax_basis
678 -- p_weekly_tax_credit
679 -- p_weekly_std_rate_cut_off
680 -- p_monthly_tax_credit
681 -- p_monthly_std_rate_cut_off
682 --
683 --
684 -- Post Success:
685 -- processing continues as no error is raised.
686 --
687 -- Post Failure:
688 -- An error is raised if amonts are not valid for the given tax basis and payroll
689 -- frequency
690 --
691 -- Access Status:
692 -- Internal Development Use Only.
693 --
694 -- {End Of Comments}
695 -- ---------------------------------------------------------------------------
696 PROCEDURE chk_tax_basis_amounts
697 (p_effective_date IN DATE
698 , p_assignment_id IN NUMBER
699 , p_tax_basis IN VARCHAR2
700 , p_weekly_tax_credit IN NUMBER
701 , p_weekly_std_rate_cut_off IN NUMBER
702 , p_monthly_tax_credit IN NUMBER
703 , p_monthly_std_rate_cut_off IN NUMBER
704 ) IS
705 -- Select Payroll Frequency
706 CURSOR csr_pay_freq IS
707 SELECT pp.period_type
708 FROM pay_payrolls_f pp, per_assignments_f pa
709 WHERE pa.assignment_id = p_assignment_id
710 AND p_effective_date BETWEEN pa.effective_start_date AND pa.effective_end_date
711 AND pp.payroll_id = pa.payroll_id
712 AND p_effective_date BETWEEN pp.effective_start_date AND pp.effective_end_date;
713 --
714 pay_freq_rec csr_pay_freq%ROWTYPE;
715 BEGIN
716 OPEN csr_pay_freq;
717 FETCH csr_pay_freq INTO pay_freq_rec;
718 --
719 IF csr_pay_freq%NOTFOUND THEN
720 CLOSE csr_pay_freq;
721 fnd_message.set_name('PAY', 'HR_IE_ASG_NOT_IN_PAYROLL');
722 fnd_message.raise_error;
723 END IF;
724 --
725 CLOSE csr_pay_freq;
726 --
727 IF p_tax_basis = 'IE_EMERGENCY'
728 AND (p_weekly_tax_credit IS NOT NULL OR p_weekly_std_rate_cut_off IS NOT NULL
729 OR p_monthly_tax_credit IS NOT NULL OR p_monthly_std_rate_cut_off IS NOT NULL ) THEN
730 fnd_message.set_name('PAY', 'HR_IE_TAX_CREDIT_NOT_NULL');
731 fnd_message.raise_error;
732 ELSIF p_tax_basis IN ('IE_CUMULATIVE', 'IE_WEEK1_MONTH1','IE_EXEMPTION','IE_EXEMPT_WEEK_MONTH') THEN -- Bug no 4618981
733 IF pay_freq_rec.period_type IN ('Calendar Month', 'Quarter', 'Bi-Month' ,'Semi-Year','Year') THEN -- bug 13028899
734 IF (p_monthly_tax_credit IS NULL OR p_monthly_std_rate_cut_off IS NULL) THEN
735 fnd_message.set_name('PAY', 'HR_IE_MONTHLY_TAX_CREDIT_NULL');
736 fnd_message.raise_error;
737 END IF;
738 ELSE
739 IF (p_weekly_tax_credit IS NULL OR p_weekly_std_rate_cut_off IS NULL) THEN
740 fnd_message.set_name('PAY', 'HR_IE_WEEKLY_TAX_CREDIT_NULL');
741 fnd_message.raise_error;
742 END IF;
743 END IF;
744 END IF;
745 --
746 END chk_tax_basis_amounts;
747 --
748 --
749 -- ----------------------------------------------------------------------------
750 -- |--------------------------< dt_update_validate >--------------------------|
754 -- Description:
751 -- ----------------------------------------------------------------------------
752 -- {Start Of Comments}
753 --
755 -- This procedure is used for referential integrity of datetracked
756 -- parent entities when a datetrack update operation is taking place
757 -- and where there is no cascading of update defined for this entity.
758 --
759 -- Prerequisites:
760 -- This procedure is called from the update_validate.
761 --
762 -- In Parameters:
763 --
764 -- Post Success:
765 -- Processing continues.
766 --
767 -- Post Failure:
768 --
769 -- Developer Implementation Notes:
770 -- This procedure should not need maintenance unless the HR Schema model
771 -- changes.
772 --
773 -- Access Status:
774 -- Internal Row Handler Use Only.
775 --
776 -- {End Of Comments}
777 -- ----------------------------------------------------------------------------
778 Procedure dt_update_validate
779 (p_assignment_id in number default hr_api.g_number
780 ,p_datetrack_mode in varchar2
781 ,p_validation_start_date in date
782 ,p_validation_end_date in date
783 ) Is
784 --
785 l_proc varchar2(72) := g_package||'dt_update_validate';
786 l_integrity_error Exception;
787 l_table_name all_tables.table_name%TYPE;
788 --
789 Begin
790 --
791 -- Ensure that the p_datetrack_mode argument is not null
792 --
793 hr_api.mandatory_arg_error
794 (p_api_name => l_proc
795 ,p_argument => 'datetrack_mode'
796 ,p_argument_value => p_datetrack_mode
797 );
798 --
799 -- Mode will be valid, as this is checked at the start of the upd.
800 --
801 -- Ensure the arguments are not null
802 --
803 hr_api.mandatory_arg_error
804 (p_api_name => l_proc
805 ,p_argument => 'validation_start_date'
806 ,p_argument_value => p_validation_start_date
807 );
808 --
809 hr_api.mandatory_arg_error
810 (p_api_name => l_proc
811 ,p_argument => 'validation_end_date'
812 ,p_argument_value => p_validation_end_date
813 );
814 --
815 If ((nvl(p_assignment_id, hr_api.g_number) <> hr_api.g_number) and
816 NOT (dt_api.check_min_max_dates
817 (p_base_table_name => 'per_all_assignments_f'
818 ,p_base_key_column => 'ASSIGNMENT_ID'
819 ,p_base_key_value => p_assignment_id
820 ,p_from_date => p_validation_start_date
821 ,p_to_date => p_validation_end_date))) Then
822 l_table_name := 'all assignments';
823 raise l_integrity_error;
824 End If;
825 --
826 Exception
827 When l_integrity_error Then
828 --
829 -- A referential integrity check was violated therefore
830 -- we must error
831 --
832 fnd_message.set_name('PAY', 'HR_7216_DT_UPD_INTEGRITY_ERR');
833 fnd_message.set_token('TABLE_NAME', l_table_name);
834 fnd_message.raise_error;
835 When Others Then
836 --
837 -- An unhandled or unexpected error has occurred which
838 -- we must report
839 --
840 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
841 fnd_message.set_token('PROCEDURE', l_proc);
842 fnd_message.set_token('STEP','15');
843 fnd_message.raise_error;
844 End dt_update_validate;
845 --
846 -- ----------------------------------------------------------------------------
847 -- |--------------------------< dt_delete_validate >--------------------------|
848 -- ----------------------------------------------------------------------------
849 -- {Start Of Comments}
850 --
851 -- Description:
852 -- This procedure is used for referential integrity of datetracked
853 -- child entities when either a datetrack DELETE or ZAP is in operation
854 -- and where there is no cascading of delete defined for this entity.
855 -- For the datetrack mode of DELETE or ZAP we must ensure that no
856 -- datetracked child rows exist between the validation start and end
857 -- dates.
858 --
859 -- Prerequisites:
860 -- This procedure is called from the delete_validate.
861 --
862 -- In Parameters:
863 --
864 -- Post Success:
865 -- Processing continues.
866 --
867 -- Post Failure:
868 -- If a row exists by determining the returning Boolean value from the
869 -- generic dt_api.rows_exist function then we must supply an error via
870 -- the use of the local exception handler l_rows_exist.
871 --
872 -- Developer Implementation Notes:
873 -- This procedure should not need maintenance unless the HR Schema model
874 -- changes.
875 --
876 -- Access Status:
877 -- Internal Row Handler Use Only.
878 --
879 -- {End Of Comments}
880 -- ----------------------------------------------------------------------------
881 Procedure dt_delete_validate
882 (p_paye_details_id in number
883 ,p_datetrack_mode in varchar2
884 ,p_validation_start_date in date
885 ,p_validation_end_date in date
886 ) Is
887 --
888 l_proc varchar2(72) := g_package||'dt_delete_validate';
889 l_rows_exist Exception;
890 l_table_name all_tables.table_name%TYPE;
891 --
892 Begin
893 --
894 -- Ensure that the p_datetrack_mode argument is not null
895 --
899 ,p_argument_value => p_datetrack_mode
896 hr_api.mandatory_arg_error
897 (p_api_name => l_proc
898 ,p_argument => 'datetrack_mode'
900 );
901 --
902 -- Only perform the validation if the datetrack mode is either
903 -- DELETE or ZAP
904 --
905 If (p_datetrack_mode = hr_api.g_delete or
906 p_datetrack_mode = hr_api.g_zap) then
907 --
908 --
909 -- Ensure the arguments are not null
910 --
911 hr_api.mandatory_arg_error
912 (p_api_name => l_proc
913 ,p_argument => 'validation_start_date'
914 ,p_argument_value => p_validation_start_date
915 );
916 --
917 hr_api.mandatory_arg_error
918 (p_api_name => l_proc
919 ,p_argument => 'validation_end_date'
920 ,p_argument_value => p_validation_end_date
921 );
922 --
923 hr_api.mandatory_arg_error
924 (p_api_name => l_proc
925 ,p_argument => 'paye_details_id'
926 ,p_argument_value => p_paye_details_id
927 );
928 --
929 --
930 --
931 End If;
932 --
933 Exception
934 When l_rows_exist Then
935 --
936 -- A referential integrity check was violated therefore
937 -- we must error
938 --
939 fnd_message.set_name('PAY', 'HR_7215_DT_CHILD_EXISTS');
940 fnd_message.set_token('TABLE_NAME', l_table_name);
941 fnd_message.raise_error;
942 When Others Then
943 --
944 -- An unhandled or unexpected error has occurred which
945 -- we must report
946 --
947 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
948 fnd_message.set_token('PROCEDURE', l_proc);
949 fnd_message.set_token('STEP','15');
950 fnd_message.raise_error;
951 --
952 End dt_delete_validate;
953 --
954 -- ----------------------------------------------------------------------------
955 -- |---------------------------< insert_validate >----------------------------|
956 -- ----------------------------------------------------------------------------
957 Procedure insert_validate
958 (p_rec in pay_ipd_shd.g_rec_type
959 ,p_effective_date in date
960 ,p_datetrack_mode in varchar2
961 ,p_validation_start_date in date
962 ,p_validation_end_date in date
963 ) is
964 --
965 l_proc varchar2(72) := g_package||'insert_validate';
966 --
967 Begin
968 hr_utility.set_location('Entering:'||l_proc, 5);
969 --
970 -- Call all supporting business operations
971 --
972 --
973 per_asg_bus1.set_security_group_id(p_rec.assignment_id);
974 --
975 --
976 pay_ipd_bus.chk_assignment_id( p_effective_date => p_effective_date
977 , p_assignment_id => p_rec.assignment_id );
978 --
979 pay_ipd_bus.chk_info_source ( p_effective_date => p_effective_date
980 , p_info_source => p_rec.info_source );
981 --
982 pay_ipd_bus.chk_tax_basis ( p_effective_date => p_effective_date
983 , p_tax_basis => p_rec.tax_basis );
984 --
985 pay_ipd_bus.chk_tax_assess_basis( p_effective_date => p_effective_date
986 , p_tax_assess_basis => p_rec.tax_assess_basis );
987 --
988 pay_ipd_bus.chk_comm_period_no ( p_effective_date => p_effective_date
989 , p_comm_period_no => p_rec.comm_period_no
990 , p_assignment_id => p_rec.assignment_id );
991 --
992 pay_ipd_bus.chk_cert_start_end_dates ( p_certificate_start_date => p_rec.certificate_start_date
993 , p_certificate_end_date => p_rec.certificate_end_date );
994 --
995 pay_ipd_bus.chk_duplicate_record ( p_assignment_id => p_rec.assignment_id
996 ,p_validation_start_date => p_validation_start_date
997 ,p_validation_end_date => p_validation_end_date);
998 --
999 pay_ipd_bus.chk_tax_basis_amounts ( p_effective_date => p_effective_date
1000 , p_assignment_id => p_rec.assignment_id
1001 , p_tax_basis => p_rec.tax_basis
1002 , p_weekly_tax_credit => p_rec.weekly_tax_credit
1003 , p_weekly_std_rate_cut_off => p_rec.weekly_std_rate_cut_off
1004 , p_monthly_tax_credit => p_rec.monthly_tax_credit
1005 , p_monthly_std_rate_cut_off => p_rec.monthly_std_rate_cut_off );
1006 --
1007 hr_utility.set_location(' Leaving:'||l_proc, 10);
1008 End insert_validate;
1009 --
1010 -- ----------------------------------------------------------------------------
1011 -- |---------------------------< update_validate >----------------------------|
1012 -- ----------------------------------------------------------------------------
1013 Procedure update_validate
1014 (p_rec in pay_ipd_shd.g_rec_type
1015 ,p_effective_date in date
1016 ,p_datetrack_mode in varchar2
1017 ,p_validation_start_date in date
1018 ,p_validation_end_date in date
1019 ) is
1020 --
1021 l_proc varchar2(72) := g_package||'update_validate';
1022 --
1023 Begin
1024 hr_utility.set_location('Entering:'||l_proc, 5);
1025 --
1026 -- Call all supporting business operations
1027 --
1028 --
1032 -- Call the datetrack update integrity operation
1029 per_asg_bus1.set_security_group_id(p_rec.assignment_id);
1030 --
1031 --
1033 --
1034 dt_update_validate
1035 (p_assignment_id => p_rec.assignment_id
1036 ,p_datetrack_mode => p_datetrack_mode
1037 ,p_validation_start_date => p_validation_start_date
1038 ,p_validation_end_date => p_validation_end_date
1039 );
1040 --
1041 chk_non_updateable_args
1042 (p_effective_date => p_effective_date
1043 ,p_rec => p_rec
1044 );
1045 --
1046 --
1047 pay_ipd_bus.chk_info_source ( p_effective_date => p_effective_date
1048 , p_info_source => p_rec.info_source );
1049 --
1050 pay_ipd_bus.chk_tax_basis ( p_effective_date => p_effective_date
1051 , p_tax_basis => p_rec.tax_basis );
1052 --
1053 pay_ipd_bus.chk_tax_assess_basis( p_effective_date => p_effective_date
1054 , p_tax_assess_basis => p_rec.tax_assess_basis );
1055 --
1056 pay_ipd_bus.chk_cert_start_end_dates ( p_certificate_start_date => p_rec.certificate_start_date
1057 , p_certificate_end_date => p_rec.certificate_end_date );
1058 --
1059 pay_ipd_bus.chk_tax_basis_amounts ( p_effective_date => p_effective_date
1060 , p_assignment_id => p_rec.assignment_id
1061 , p_tax_basis => p_rec.tax_basis
1062 , p_weekly_tax_credit => p_rec.weekly_tax_credit
1063 , p_weekly_std_rate_cut_off => p_rec.weekly_std_rate_cut_off
1064 , p_monthly_tax_credit => p_rec.monthly_tax_credit
1065 , p_monthly_std_rate_cut_off => p_rec.monthly_std_rate_cut_off );
1066 --
1067 --
1068 hr_utility.set_location(' Leaving:'||l_proc, 10);
1069 End update_validate;
1070 --
1071 -- ----------------------------------------------------------------------------
1072 -- |---------------------------< delete_validate >----------------------------|
1073 -- ----------------------------------------------------------------------------
1074 Procedure delete_validate
1075 (p_rec in pay_ipd_shd.g_rec_type
1076 ,p_effective_date in date
1077 ,p_datetrack_mode in varchar2
1078 ,p_validation_start_date in date
1079 ,p_validation_end_date in date
1080 ) is
1081 --
1082 l_proc varchar2(72) := g_package||'delete_validate';
1083 --
1084 Begin
1085 hr_utility.set_location('Entering:'||l_proc, 5);
1086 --
1087 -- Call all supporting business operations
1088 --
1089 dt_delete_validate
1090 (p_datetrack_mode => p_datetrack_mode
1091 ,p_validation_start_date => p_validation_start_date
1092 ,p_validation_end_date => p_validation_end_date
1093 ,p_paye_details_id => p_rec.paye_details_id
1094 );
1095 --
1096 hr_utility.set_location(' Leaving:'||l_proc, 10);
1097 End delete_validate;
1098 --
1099 end pay_ipd_bus;
[Home] [Help]