4 -- ----------------------------------------------------------------------------
1 Package Body pay_ppd_bus as
2 /* $Header: pyppdrhi.pkb 120.2 2005/12/29 01:38 nprasath noship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_ppd_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 ,p_associated_column1 in varchar2 default null
23 ) is
24 --
25 -- Declare cursor
26 --
27 cursor csr_sec_grp is
28 select pbg.security_group_id,
29 pbg.legislation_code
30 from per_business_groups_perf pbg
31 , pay_pl_paye_details_f ppd
32 where ppd.paye_details_id = p_paye_details_id
33 and pbg.business_group_id = ppd.business_group_id;
34 --
35 -- Declare local variables
36 --
37 l_security_group_id number;
38 l_proc varchar2(72) := g_package||'set_security_group_id';
39 l_legislation_code varchar2(150);
40 --
41 begin
42 --
43 hr_utility.set_location('Entering:'|| l_proc, 10);
44 --
45 -- Ensure that all the mandatory parameter are not null
46 --
47 hr_api.mandatory_arg_error
48 (p_api_name => l_proc
49 ,p_argument => 'paye_details_id'
50 ,p_argument_value => p_paye_details_id
51 );
52 --
53 open csr_sec_grp;
54 fetch csr_sec_grp into l_security_group_id
55 , l_legislation_code;
56 --
57 if csr_sec_grp%notfound then
58 --
59 close csr_sec_grp;
60 --
61 -- The primary key is invalid therefore we must error
62 --
63 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
64 hr_multi_message.add
65 (p_associated_column1
66 => nvl(p_associated_column1,'PAYE_DETAILS_ID')
67 );
68 --
69 else
70 close csr_sec_grp;
71 --
72 -- Set the security_group_id in CLIENT_INFO
73 --
74 hr_api.set_security_group_id
75 (p_security_group_id => l_security_group_id
76 );
77 --
78 -- Set the sessions legislation context in HR_SESSION_DATA
79 --
80 hr_api.set_legislation_context(l_legislation_code);
81 end if;
82 --
83 hr_utility.set_location(' Leaving:'|| l_proc, 20);
84 --
85 end set_security_group_id;
86 --
87 -- ---------------------------------------------------------------------------
88 -- |---------------------< return_legislation_code >-------------------------|
89 -- ---------------------------------------------------------------------------
90 --
91 Function return_legislation_code
92 (p_paye_details_id in number
93 )
94 Return Varchar2 Is
95 --
96 -- Declare cursor
97 --
98 cursor csr_leg_code is
99 select pbg.legislation_code
100 from per_business_groups_perf pbg
101 , pay_pl_paye_details_f ppd
102 where ppd.paye_details_id = p_paye_details_id
103 and pbg.business_group_id = ppd.business_group_id;
104 --
105 -- Declare local variables
109 --
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'paye_details_id'
119 ,p_argument_value => p_paye_details_id
120 );
121 --
122 if ( nvl(pay_ppd_bus.g_paye_details_id, hr_api.g_number)
123 = p_paye_details_id) then
124 --
125 -- The legislation code has already been found with a previous
126 -- call to this function. Just return the value in the global
127 -- variable.
128 --
129 l_legislation_code := pay_ppd_bus.g_legislation_code;
130 hr_utility.set_location(l_proc, 20);
131 else
132 --
133 -- The ID is different to the last call to this function
134 -- or this is the first call to this function.
135 --
136 open csr_leg_code;
137 fetch csr_leg_code into l_legislation_code;
138 --
139 if csr_leg_code%notfound then
140 --
141 -- The primary key is invalid therefore we must error
142 --
143 close csr_leg_code;
144 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
145 fnd_message.raise_error;
146 end if;
147 hr_utility.set_location(l_proc,30);
148 --
149 -- Set the global variables so the values are
150 -- available for the next call to this function.
151 --
152 close csr_leg_code;
153 pay_ppd_bus.g_paye_details_id := p_paye_details_id;
154 pay_ppd_bus.g_legislation_code := l_legislation_code;
155 end if;
156 hr_utility.set_location(' Leaving:'|| l_proc, 40);
157 return l_legislation_code;
158 end return_legislation_code;
159 --
160 -- ----------------------------------------------------------------------------
161 -- |-----------------------< chk_non_updateable_args >------------------------|
162 -- ----------------------------------------------------------------------------
163 -- {Start Of Comments}
164 --
165 -- Description:
166 -- This procedure is used to ensure that non updateable attributes have
167 -- not been updated. If an attribute has been updated an error is generated.
168 --
169 -- Pre Conditions:
170 -- g_old_rec has been populated with details of the values currently in
171 -- the database.
172 --
173 -- In Arguments:
174 -- p_rec has been populated with the updated values the user would like the
175 -- record set to.
176 --
177 -- Post Success:
181 -- Post Failure:
178 -- Processing continues if all the non updateable attributes have not
179 -- changed.
180 --
182 -- An application error is raised if any of the non updatable attributes
183 -- have been altered.
184 --
185 -- {End Of Comments}
186 -- ----------------------------------------------------------------------------
187 Procedure chk_non_updateable_args
188 (p_effective_date in date
189 ,p_rec in pay_ppd_shd.g_rec_type
190 ) IS
191 --
192 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
193
194 cursor csr_contract_type(p_per_or_asg_id number,
195 p_effective_date date) is
196 select segment4
197 from hr_soft_coding_keyflex soft, per_all_assignments_f paaf
198 where soft.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
199 and paaf.assignment_id = p_per_or_asg_id
200 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
201
202 l_contract_type hr_soft_coding_keyflex.segment4%TYPE;
203 --
204 Begin
205 --
206 -- Only proceed with the validation if a row exists for the current
207 -- record in the HR Schema.
208 --
209 IF NOT pay_ppd_shd.api_updating
210 (p_paye_details_id => p_rec.paye_details_id
211 ,p_effective_date => p_effective_date
212 ,p_object_version_number => p_rec.object_version_number
213 ) THEN
214 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
215 fnd_message.set_token('PROCEDURE ', l_proc);
216 fnd_message.set_token('STEP ', '5');
217 fnd_message.raise_error;
218 END IF;
219 --
220 if nvl(p_rec.business_group_id, hr_api.g_number) <>
221 nvl(pay_ppd_shd.g_old_rec.business_group_id
222 ,hr_api.g_number
223 ) then
224 hr_api.argument_changed_error
225 (p_api_name => l_proc
226 ,p_argument => 'BUSINESS_GROUP_ID'
227 ,p_base_table => pay_ppd_shd.g_tab_nam
228 );
229 end if;
230
231 if nvl(p_rec.contract_category, hr_api.g_varchar2) <>
232 nvl(pay_ppd_shd.g_old_rec.contract_category
233 ,hr_api.g_varchar2
234 ) then
235 hr_api.argument_changed_error
236 (p_api_name => l_proc
237 ,p_argument => 'CONTRACT_CATEGORY'
238 ,p_base_table => pay_ppd_shd.g_tab_nam
239 );
240 end if;
241
242 if nvl(p_rec.per_or_asg_id, hr_api.g_number) <>
243 nvl(pay_ppd_shd.g_old_rec.per_or_asg_id
244 ,hr_api.g_number
245 ) then
246 hr_api.argument_changed_error
247 (p_api_name => l_proc
248 ,p_argument => 'PER_OR_ASG_ID'
249 ,p_base_table => pay_ppd_shd.g_tab_nam
250 );
251 end if;
252 -- Check to Income Reduction for CIVIL contract its non updateable
253 If p_rec.contract_category in ('CIVIL','LUMP','F_LUMP') then
254 if nvl(p_rec.income_reduction, hr_api.g_varchar2) <>
255 nvl(pay_ppd_shd.g_old_rec.income_reduction
256 ,hr_api.g_varchar2
257 ) then
258 hr_api.argument_changed_error
259 (p_api_name => l_proc
260 ,p_argument => 'INCOME_REDUCTION'
261 ,p_base_table => pay_ppd_shd.g_tab_nam
262 );
263 end if;
264 End if;
265
266 -- Rate of Tax is a constant for Lump Sum Contracts with Contract type
267 -- L01, L02, L03, L04, L09, L10, L11. Also we do not store the Rate of Tax for
268 -- these Contract types in the Tax table. Hence the user should not try to
269 -- update the Rate of Tax for these Contract types when calling the update api
270
271 if p_rec.contract_category = 'LUMP' then
272 open csr_contract_type(p_rec.per_or_asg_id,p_effective_date);
273 fetch csr_contract_type into l_contract_type;
274 close csr_contract_type;
275
276 if l_contract_type in ('L01','L02','L03','L04','L09','L10','L11') then
277 if nvl(p_rec.rate_of_tax, hr_api.g_varchar2) <>
278 nvl(pay_ppd_shd.g_old_rec.rate_of_tax
279 ,hr_api.g_varchar2
280 ) then
281 hr_api.argument_changed_error
282 (p_api_name => l_proc
286 end if;
283 ,p_argument => 'RATE_OF_TAX'
284 ,p_base_table => pay_ppd_shd.g_tab_nam
285 );
287 end if;
288 end if;
289 --
290 End chk_non_updateable_args;
291 --
292 -- ----------------------------------------------------------------------------
293 -- |--------------------------< dt_update_validate >--------------------------|
294 -- ----------------------------------------------------------------------------
295 -- {Start Of Comments}
296 --
297 -- Description:
298 -- This procedure is used for referential integrity of datetracked
299 -- parent entities when a datetrack update operation is taking place
300 -- and where there is no cascading of update defined for this entity.
301 --
302 -- Prerequisites:
303 -- This procedure is called from the update_validate.
304 --
305 -- In Parameters:
306 --
307 -- Post Success:
308 -- Processing continues.
309 --
310 -- Post Failure:
311 --
312 -- Developer Implementation Notes:
313 -- This procedure should not need maintenance unless the HR Schema model
314 -- changes.
315 --
316 -- Access Status:
317 -- Internal Row Handler Use Only.
318 --
319 -- {End Of Comments}
320 -- ----------------------------------------------------------------------------
321 Procedure dt_update_validate
322 (p_datetrack_mode in varchar2
323 ,p_validation_start_date in date
324 ,p_validation_end_date in date
325 ) Is
326 --
327 l_proc varchar2(72) := g_package||'dt_update_validate';
328 --
329 Begin
330 --
331 -- Ensure that the p_datetrack_mode argument is not null
332 --
333 hr_api.mandatory_arg_error
334 (p_api_name => l_proc
335 ,p_argument => 'datetrack_mode'
336 ,p_argument_value => p_datetrack_mode
337 );
338 --
339 -- Mode will be valid, as this is checked at the start of the upd.
340 --
341 -- Ensure the arguments are not null
342 --
343 hr_api.mandatory_arg_error
344 (p_api_name => l_proc
345 ,p_argument => 'validation_start_date'
346 ,p_argument_value => p_validation_start_date
347 );
348 --
349 hr_api.mandatory_arg_error
350 (p_api_name => l_proc
351 ,p_argument => 'validation_end_date'
352 ,p_argument_value => p_validation_end_date
353 );
354 --
355 --
356 --
357 Exception
358 When Others Then
359 --
360 -- An unhandled or unexpected error has occurred which
361 -- we must report
362 --
363 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
364 fnd_message.set_token('PROCEDURE', l_proc);
365 fnd_message.set_token('STEP','15');
366 fnd_message.raise_error;
367 End dt_update_validate;
368 --
369 -- ----------------------------------------------------------------------------
370 -- |--------------------------< dt_delete_validate >--------------------------|
371 -- ----------------------------------------------------------------------------
372 -- {Start Of Comments}
373 --
374 -- Description:
375 -- This procedure is used for referential integrity of datetracked
376 -- child entities when either a datetrack DELETE or ZAP is in operation
377 -- and where there is no cascading of delete defined for this entity.
378 -- For the datetrack mode of DELETE or ZAP we must ensure that no
379 -- datetracked child rows exist between the validation start and end
380 -- dates.
381 --
382 -- Prerequisites:
383 -- This procedure is called from the delete_validate.
384 --
385 -- In Parameters:
386 --
387 -- Post Success:
388 -- Processing continues.
389 --
390 -- Post Failure:
391 -- If a row exists by determining the returning Boolean value from the
392 -- generic dt_api.rows_exist function then we must supply an error via
393 -- the use of the local exception handler l_rows_exist.
394 --
395 -- Developer Implementation Notes:
396 -- This procedure should not need maintenance unless the HR Schema model
397 -- changes.
398 --
399 -- Access Status:
400 -- Internal Row Handler Use Only.
401 --
402 -- {End Of Comments}
403 -- ----------------------------------------------------------------------------
404 Procedure dt_delete_validate
405 (p_paye_details_id in number
406 ,p_datetrack_mode in varchar2
407 ,p_validation_start_date in date
408 ,p_validation_end_date in date
409 ) Is
410 --
411 l_proc varchar2(72) := g_package||'dt_delete_validate';
412 --
413 Begin
414 --
415 -- Ensure that the p_datetrack_mode argument is not null
416 --
417 hr_api.mandatory_arg_error
418 (p_api_name => l_proc
419 ,p_argument => 'datetrack_mode'
420 ,p_argument_value => p_datetrack_mode
421 );
422 --
423 -- Only perform the validation if the datetrack mode is either
424 -- DELETE or ZAP
425 --
426 If (p_datetrack_mode = hr_api.g_delete or
427 p_datetrack_mode = hr_api.g_zap) then
428 --
429 --
430 -- Ensure the arguments are not null
431 --
432 hr_api.mandatory_arg_error
433 (p_api_name => l_proc
434 ,p_argument => 'validation_start_date'
435 ,p_argument_value => p_validation_start_date
436 );
437 --
438 hr_api.mandatory_arg_error
439 (p_api_name => l_proc
443 --
440 ,p_argument => 'validation_end_date'
441 ,p_argument_value => p_validation_end_date
442 );
444 hr_api.mandatory_arg_error
445 (p_api_name => l_proc
446 ,p_argument => 'paye_details_id'
447 ,p_argument_value => p_paye_details_id
448 );
449 --
450 --
451 --
452 End If;
453 --
454 Exception
455 When Others Then
456 --
457 -- An unhandled or unexpected error has occurred which
458 -- we must report
459 --
460 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
461 fnd_message.set_token('PROCEDURE', l_proc);
462 fnd_message.set_token('STEP','15');
463 fnd_message.raise_error;
464 --
465 End dt_delete_validate;
466 --
467 -- ----------------------------------------------------------------------------
468 -- |---------------------------< insert_validate >----------------------------|
469 -- ----------------------------------------------------------------------------
470 Procedure insert_validate
471 (p_rec in pay_ppd_shd.g_rec_type
472 ,p_effective_date in date
473 ,p_datetrack_mode in varchar2
474 ,p_validation_start_date in date
475 ,p_validation_end_date in date
476 ) is
477 --
478 l_proc varchar2(72) := g_package||'insert_validate';
479 --
480 Begin
481 hr_utility.set_location('Entering:'||l_proc, 5);
482 --
483 -- Call all supporting business operations
484 --
485 hr_api.validate_bus_grp_id
486 (p_business_group_id => p_rec.business_group_id
487 ,p_associated_column1 => pay_ppd_shd.g_tab_nam
488 || '.BUSINESS_GROUP_ID');
489 --
490 -- After validating the set of important attributes,
491 -- if Multiple Message detection is enabled and at least
492 -- one error has been found then abort further validation.
493 --
494 hr_multi_message.end_validation_set;
495 --
496 -- Validate Dependent Attributes
497 --
498 --Polish Localization Code has been added here
499
500 --Validation check for Contract Category
501 pay_ppd_bus.chk_contract_category(p_paye_details_id =>p_rec.paye_details_id
502 ,p_effective_date=> p_effective_date
503 ,p_contract_category => p_rec.contract_category
504 ,p_validation_start_date => p_validation_start_date
505 ,p_validation_end_date => p_validation_end_date
506 ,p_object_version_number => p_rec.object_version_number);
507 --
508 --Validation check for Business Group Id
509 pay_ppd_bus.chk_business_group_id(p_paye_details_id => p_rec.paye_details_id,
510 p_effective_date => p_effective_date,
511 p_business_group_id => p_rec.business_group_id,
512 p_validation_start_date => p_validation_start_date,
513 p_validation_end_date => p_validation_end_date,
514 p_object_version_number => p_rec.object_version_number);
515 -- Validation check for per_or_asg_id
516 pay_ppd_bus.chk_per_asg_id(p_effective_date => p_effective_date
517 ,p_per_or_asg_id => p_rec.per_or_asg_id
518 ,p_contract_category=> p_rec.contract_category
519 ,p_business_group_id=> p_rec.business_group_id
520 ,p_object_version_number => p_rec.object_version_number);
521 --
522 -- Validation check for rate_of_tax
523 pay_ppd_bus.chk_rate_of_tax(p_paye_details_id => p_rec.paye_details_id
524 ,p_effective_date => p_effective_date
525 ,p_rate_of_tax => p_rec.rate_of_tax
526 ,p_contract_category => p_rec.contract_category
527 ,p_validation_start_date => p_validation_start_date
528 ,p_validation_end_date => p_validation_end_date
529 ,p_object_version_number => p_rec.object_version_number
530 ,p_per_or_asg_id => p_rec.per_or_asg_id);
531 -- Validation check for Tax Reduction
532
533 If p_rec.contract_category in ('NORMAL','TERM_NORMAL') then
534 pay_ppd_bus.chk_tax_reduction(p_paye_details_id => p_rec.paye_details_id
535 ,p_effective_date => p_effective_date
536 ,p_tax_reduction => p_rec.tax_reduction
537 ,p_validation_start_date => p_validation_start_date
538 ,p_validation_end_date => p_validation_end_date
539 ,p_object_version_number => p_rec.object_version_number);
540 --
541 -- Validation check for tax_calc_with_spouse_child
542 pay_ppd_bus.chk_tax_calc_with_spouse_child(p_paye_details_id => p_rec.paye_details_id
543 ,p_effective_date => p_effective_date
544 ,p_tax_calc_with_spouse_child => p_rec.tax_calc_with_spouse_child
545 ,p_validation_start_date => p_validation_start_date
546 ,p_validation_end_date => p_validation_end_date
547 ,p_object_version_number => p_rec.object_version_number);
548
549 --
550
551 -- Validation check for Income Reduction
552 pay_ppd_bus.chk_income_reduction(p_paye_details_id => p_rec.paye_details_id
553 ,p_effective_date => p_effective_date
554 ,p_income_reduction => p_rec.income_reduction
555 ,p_validation_start_date => p_validation_start_date
556 ,p_validation_end_date => p_validation_end_date
557 ,p_object_version_number => p_rec.object_version_number);
558 --
559 End if;
560 hr_utility.set_location(' Leaving:'||l_proc, 10);
561 End insert_validate;
562 --
563 -- ----------------------------------------------------------------------------
567 (p_rec in pay_ppd_shd.g_rec_type
564 -- |---------------------------< update_validate >----------------------------|
565 -- ----------------------------------------------------------------------------
566 Procedure update_validate
568 ,p_effective_date in date
569 ,p_datetrack_mode in varchar2
570 ,p_validation_start_date in date
571 ,p_validation_end_date in date
572 ) is
573 --
574 l_proc varchar2(72) := g_package||'update_validate';
575 --
576 Begin
577 hr_utility.set_location('Entering:'||l_proc, 5);
578 --
579 -- Call all supporting business operations
580 --
581 hr_api.validate_bus_grp_id
582 (p_business_group_id => p_rec.business_group_id
583 ,p_associated_column1 => pay_ppd_shd.g_tab_nam
584 || '.BUSINESS_GROUP_ID');
585 --
586 -- After validating the set of important attributes,
587 -- if Multiple Message detection is enabled and at least
588 -- one error has been found then abort further validation.
589 --
590 hr_multi_message.end_validation_set;
591 --
592 -- Validate Dependent Attributes
593 --
594 -- Call the datetrack update integrity operation
595 --
596 dt_update_validate
597 (p_datetrack_mode => p_datetrack_mode
598 ,p_validation_start_date => p_validation_start_date
599 ,p_validation_end_date => p_validation_end_date
600 );
601 --
602 chk_non_updateable_args
603 (p_effective_date => p_effective_date
604 ,p_rec => p_rec
605 );
606 --
607 -- Validation check for rate_of_tax
608 pay_ppd_bus.chk_rate_of_tax(p_paye_details_id => p_rec.paye_details_id
609 ,p_effective_date => p_effective_date
610 ,p_rate_of_tax => p_rec.rate_of_tax
611 ,p_contract_category => p_rec.contract_category
612 ,p_validation_start_date => p_validation_start_date
613 ,p_validation_end_date => p_validation_end_date
614 ,p_object_version_number => p_rec.object_version_number
615 ,p_per_or_asg_id => p_rec.per_or_asg_id);
616 -- Validation check for Tax Reduction
617 If p_rec.contract_category in ('NORMAL','TERM_NORMAL') then
618 pay_ppd_bus.chk_tax_reduction(p_paye_details_id => p_rec.paye_details_id
619 ,p_effective_date => p_effective_date
620 ,p_tax_reduction => p_rec.tax_reduction
621 ,p_validation_start_date => p_validation_start_date
622 ,p_validation_end_date => p_validation_end_date
623 ,p_object_version_number => p_rec.object_version_number);
624 --
625 -- Validation check for tax_calc_with_spouse_child
626 pay_ppd_bus.chk_tax_calc_with_spouse_child(p_paye_details_id => p_rec.paye_details_id
627 ,p_effective_date => p_effective_date
628 ,p_tax_calc_with_spouse_child => p_rec.tax_calc_with_spouse_child
629 ,p_validation_start_date => p_validation_start_date
630 ,p_validation_end_date => p_validation_end_date
631 ,p_object_version_number => p_rec.object_version_number);
632
633 --
634
635 -- Validation check for Income Reduction
636 pay_ppd_bus.chk_income_reduction(p_paye_details_id => p_rec.paye_details_id
637 ,p_effective_date => p_effective_date
638 ,p_income_reduction => p_rec.income_reduction
639 ,p_validation_start_date => p_validation_start_date
640 ,p_validation_end_date => p_validation_end_date
641 ,p_object_version_number => p_rec.object_version_number);
642 --
643 End if;
644 --
645 hr_utility.set_location(' Leaving:'||l_proc, 10);
646 End update_validate;
647 --
648 -- ----------------------------------------------------------------------------
649 -- |---------------------------< delete_validate >----------------------------|
650 -- ----------------------------------------------------------------------------
651 Procedure delete_validate
652 (p_rec in pay_ppd_shd.g_rec_type
653 ,p_effective_date in date
654 ,p_datetrack_mode in varchar2
655 ,p_validation_start_date in date
656 ,p_validation_end_date in date
657 ) is
658 --
659 l_proc varchar2(72) := g_package||'delete_validate';
660 --
661 Begin
662 hr_utility.set_location('Entering:'||l_proc, 5);
663 --
664 -- Call all supporting business operations
665 --
666 dt_delete_validate
667 (p_datetrack_mode => p_datetrack_mode
668 ,p_validation_start_date => p_validation_start_date
669 ,p_validation_end_date => p_validation_end_date
670 ,p_paye_details_id => p_rec.paye_details_id
671 );
672 --
673 hr_utility.set_location(' Leaving:'||l_proc, 10);
674 End delete_validate;
675 --
676 -- ----------------------------------------------------------------------------
677 --|-------------------------< chk_contract_category >--------------------------|
678 -- ----------------------------------------------------------------------------
679 Procedure chk_contract_category
680 (p_paye_details_id in number
681 ,p_effective_date in date
682 ,p_contract_category in varchar2
683 ,p_validation_start_date in date
684 ,p_validation_end_date in date
685 ,p_object_version_number in number
686 ) IS
687
688 l_proc varchar2(72);
689
690 Begin
691 hr_utility.set_location('Entering:'|| l_proc, 10);
692 l_proc := g_package ||'chk_contract_category';
693
694 --
698 (p_api_name => l_proc
695 -- Check mandatory parameters have been set
696 --
697 hr_api.mandatory_arg_error
699 ,p_argument => 'effective date'
700 ,p_argument_value => p_effective_date
701 );
702
703 hr_api.mandatory_arg_error
704 (p_api_name => l_proc
705 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_CATEGORY')
706 ,p_argument_value => p_contract_category
707 );
708
709 -- If Contract Category is not null then
710 -- Check if the Contract Category value exists in hr_lookups
711 -- where the lookup_type is 'PL_CONTRACT_CATEGORY'
712 --
713 if p_contract_category is not null then
714 if hr_api.not_exists_in_dt_hr_lookups
715 (p_effective_date => p_effective_date
716 ,p_validation_start_date => p_validation_start_date
717 ,p_validation_end_date => p_validation_end_date
718 ,p_lookup_type => 'PL_CONTRACT_CATEGORY'
719 ,p_lookup_code => p_contract_category
720 ) then
721 -- Error: Invalid Contract Category
722 hr_utility.set_message(801,'PAY_375843_CONTRACT_PL_LOOKUP');
723 -- This message will be 'The Contract Category does not exist in the system'
724 hr_utility.raise_error;
725 end if;
726 end if;
727 hr_utility.set_location(' Leaving:'|| l_proc, 20);
728 exception
729 when app_exception.application_exception then
730 if hr_multi_message.exception_add
731 (p_associated_column1 => 'PAY_PL_PAYE_DETAILS_F.CONTRACT_CATEGORY'
732 ) then
733 hr_utility.set_location(' Leaving:'|| l_proc, 30);
734 raise;
735 end if;
736 hr_utility.set_location(' Leaving:'|| l_proc, 40);
737
738 End chk_contract_category;
739 --
740 -- ----------------------------------------------------------------------------
741 --|---------------------------< chk_per_asg_id >-------------------------------|
742 -- ----------------------------------------------------------------------------
743 Procedure chk_per_asg_id
744 (p_effective_date in date
745 ,p_per_or_asg_id in number
746 ,p_contract_category in varchar2
747 ,p_business_group_id in number
748 ,p_object_version_number in number
749 ) IS
750
751 l_proc varchar2(72);
752 l_exists varchar2(1);
753 l_civil_catg hr_soft_coding_keyflex.segment3%TYPE;
754 l_term_catg hr_soft_coding_keyflex.segment3%TYPE;
755
756 l_lump_catg hr_soft_coding_keyflex.segment3%TYPE;
757 l_f_lump_catg hr_soft_coding_keyflex.segment3%TYPE;
758
759 cursor csr_per_id is
760 select null
761 from per_all_people_f papf
762 where papf.person_id = p_per_or_asg_id and
763 papf.business_group_id = p_business_group_id and
764 p_effective_date between papf.effective_start_date and
765 papf.effective_end_date and
766 papf.person_type_id in (select person_type_id from per_person_types
767 where business_group_id = p_business_group_id
768 and system_person_type in ('EMP','EMP_APL'));
769
770 cursor csr_asg_id is
771 select null
772 from per_all_assignments_f paaf, hr_soft_coding_keyflex hrsoft
773 where paaf.assignment_id = p_per_or_asg_id and
774 paaf.business_group_id = p_business_group_id and
775 p_effective_date between paaf.effective_start_date and
776 paaf.effective_end_date and
777 paaf.assignment_status_type_id in (select assignment_status_type_id from
778 per_assignment_status_types where
779 per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN'))
780 and paaf.soft_coding_keyflex_id = hrsoft.soft_coding_keyflex_id and
781 hrsoft.segment3 in (l_civil_catg,l_lump_catg,l_f_lump_catg);
782
783
784 cursor csr_normal_term_id is
785 select null
786 from per_all_assignments_f paaf, hr_soft_coding_keyflex hrsoft
787 where paaf.assignment_id = p_per_or_asg_id and
788 paaf.business_group_id = p_business_group_id and
789 p_effective_date between paaf.effective_start_date and
790 paaf.effective_end_date and
791 paaf.assignment_status_type_id in (select assignment_status_type_id from
792 per_assignment_status_types where
793 per_system_status = 'TERM_ASSIGN')
794 and paaf.soft_coding_keyflex_id = hrsoft.soft_coding_keyflex_id;
795 -- and hrsoft.segment3 = l_term_catg;
796
797 Begin
798 hr_utility.set_location('Entering:'|| l_proc, 10);
799 l_proc := g_package ||'chk_per_asg_id';
800 l_exists := NULL;
801
802 l_civil_catg := 'CIVIL';
803 l_term_catg := 'TERM_NORMAL';
804
805 l_lump_catg := 'LUMP';
806 l_f_lump_catg := 'F_LUMP';
807
808 --
809 -- Check mandatory parameters have been set
810 --
811 hr_api.mandatory_arg_error
812 (p_api_name => l_proc
813 ,p_argument => 'effective date'
814 ,p_argument_value => p_effective_date
815 );
816
817 hr_api.mandatory_arg_error
818 (p_api_name => l_proc
819 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','PER_ASG_ID')
820 ,p_argument_value => p_per_or_asg_id
821 );
822
826 ,p_associated_column1 => 'PAY_PL_PAYE_DETAILS_F.PER_OR_ASG_ID') then
823 if hr_multi_message.no_exclusive_error
824 (p_check_column1 => 'PAY_PL_PAYE_DETAILS_F.CONTRACT_CATEGORY'
825 ,p_check_column2 => 'PAY_PL_PAYE_DETAILS_F.BUSINESS_GROUP_ID'
827
828 -- Continue with valiadtion only if the columns
829 -- a) BUSINESS_GROUP_ID and
830 -- b) CONTRACT_CATEGORY are valid.
831
832 if p_contract_category in ('CIVIL','LUMP','F_LUMP') then
833 -- Since Civil/Lump Sum/Foreigners Lump Sum PAYE records are stored at the Assignment level,
834 -- we open csr_asg_id
835 open csr_asg_id;
836 fetch csr_asg_id into l_exists;
837 if csr_asg_id%NOTFOUND then
838 -- Raise an error message that the record is not in the business group for the date range specified.
839 hr_utility.set_message(801,'PAY_375840_INVALID_PL_ASG_ID');
840 hr_utility.raise_error;
841 end if;
842 close csr_asg_id;
843
844 elsif p_contract_category = 'NORMAL' then
845 -- Since Normal PAYE records are stored at Person level, we open csr_per_id
846 open csr_per_id;
847 fetch csr_per_id into l_exists;
848 if csr_per_id%NOTFOUND then
849 -- Raise an error message that the records isnot in the business group for the date range specified
850 hr_utility.set_message(801,'PAY_375839_INVALID_PL_PER_ID');
851 hr_utility.raise_error;
852 end if;
853 close csr_per_id;
854
855 elsif p_contract_category = 'TERM_NORMAL' then
856 -- Since Normal Terminated PAYE records are stored at Assignment level, we open csr_normal_term_id
857 open csr_normal_term_id;
858 fetch csr_normal_term_id into l_exists;
859 if csr_normal_term_id%NOTFOUND then
860 -- Raise an error message that the record is not in the business group for the date range
861 hr_utility.set_message(801,'PAY_375857_INVALID_TERM_ID');
862 hr_utility.raise_error;
863 end if;
864 close csr_normal_term_id;
865
866 end if;
867 end if;
868
869 hr_utility.set_location(' Leaving:'|| l_proc, 20);
870 exception
871 when app_exception.application_exception then
872 if hr_multi_message.exception_add
873 (p_associated_column1 => 'PAY_PL_PAYE_DETAILS_F.PER_OR_ASG_ID'
874 ) then
875 hr_utility.set_location(' Leaving:'|| l_proc, 30);
876 raise;
877 end if;
878 hr_utility.set_location(' Leaving:'|| l_proc, 40);
879
880 End chk_per_asg_id;
881
882 --
883 -- ----------------------------------------------------------------------------
884 --|-------------------------< chk_business_group_id >--------------------------|
885 -- ----------------------------------------------------------------------------
886 Procedure chk_business_group_id
887 (p_paye_details_id in number
888 ,p_effective_date in date
889 ,p_business_group_id in number
890 ,p_validation_start_date in date
891 ,p_validation_end_date in date
892 ,p_object_version_number in number
893 ) IS
894
895 l_proc varchar2(72);
896
897 Begin
898 hr_utility.set_location('Entering:'|| l_proc, 10);
899 l_proc := g_package ||'chk_contract_category';
900
901 --
902 -- Check mandatory parameters have been set
903 --
904 hr_api.mandatory_arg_error
905 (p_api_name => l_proc
906 ,p_argument => 'effective date'
907 ,p_argument_value => p_effective_date
908 );
909
910 hr_api.mandatory_arg_error
911 (p_api_name => l_proc
912 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','BUSINESS_GROUP')
913 ,p_argument_value => p_business_group_id
914 );
915
916 hr_api.validate_bus_grp_id
917 (p_business_group_id => p_business_group_id
918 ,p_associated_column1 => pay_ppd_shd.g_tab_nam||'.BUSINESS_GROUP_ID');
919
920 hr_multi_message.end_validation_set;
921
922 hr_utility.set_location(' Leaving:'|| l_proc, 20);
923 exception
924 when app_exception.application_exception then
925 if hr_multi_message.exception_add
926 (p_associated_column1 => 'PAY_PL_PAYE_DETAILS_F.BUSINESS_GROUP_ID'
927 ) then
928 hr_utility.set_location(' Leaving:'|| l_proc, 30);
929 raise;
930 end if;
931 hr_utility.set_location(' Leaving:'|| l_proc, 40);
932
933 End chk_business_group_id;
934 --
935 -- ----------------------------------------------------------------------------
936 --|-------------------------< chk_rate_of_tax >--------------------------------|
937 -- ----------------------------------------------------------------------------
938 Procedure chk_rate_of_tax
939 (p_paye_details_id in number
940 ,p_effective_date in date
941 ,p_rate_of_tax in varchar2
942 ,p_contract_category in varchar2
943 ,p_validation_start_date in date
944 ,p_validation_end_date in date
945 ,p_object_version_number in number
946 ,p_per_or_asg_id in number
947 ) IS
948
949 l_proc varchar2(72);
950 l_api_updating boolean;
951
952 l_contract_type hr_soft_coding_keyflex.segment4%TYPE;
953
954 cursor csr_contract_type(p_per_or_asg_id number,
955 p_effective_date date) is
956 select segment4
960 and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
957 from hr_soft_coding_keyflex soft, per_all_assignments_f paaf
958 where soft.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
959 and paaf.assignment_id = p_per_or_asg_id
961
962
963 Begin
964 hr_utility.set_location('Entering:'|| l_proc, 10);
965 l_proc := g_package ||'chk_rate_of_tax';
966
967 if p_contract_category = 'LUMP' then
968 open csr_contract_type(p_per_or_asg_id,p_effective_date);
969 fetch csr_contract_type into l_contract_type;
970 close csr_contract_type;
971 end if;
972
973 if hr_multi_message.no_exclusive_error
974 (p_check_column1 => 'PAY_PL_PAYE_DETAILS_F.CONTRACT_CATEGORY'
975 ,p_check_column2 => 'PAY_PL_PAYE_DETAILS_F.BUSINESS_GROUP_ID'
976 ,p_associated_column1 => 'PAY_PL_PAYE_DETAILS_F.RATE_OF_TAX') then
977
978 --
979 -- Continue with valiadtion only if the columns
980 -- a) BUSINESS_GROUP_ID and
981 -- b) CONTRACT_CATEGORY are valid.
982
983 -- Check mandatory parameters have been set
984 --
985 hr_api.mandatory_arg_error
986 (p_api_name => l_proc
987 ,p_argument => 'effective date'
988 ,p_argument_value => p_effective_date
989 );
990
991 if p_contract_category in ('NORMAL','TERM_NORMAL','CIVIL','F_LUMP') then
992 hr_api.mandatory_arg_error
993 (p_api_name => l_proc
994 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','RATE_OF_TAX')
995 ,p_argument_value => p_rate_of_tax
996 );
997 end if;
998
999
1000 -- Only proceed with validation if:
1001 -- a) The current g_old_rec is current and
1002 -- b) Rate of Tax value has changed
1003 -- c) A record is being inserted
1004 --
1005 l_api_updating := pay_ppd_shd.api_updating
1006 (p_paye_details_id => p_paye_details_id
1007 ,p_effective_date => p_effective_date
1008 ,p_object_version_number => p_object_version_number);
1009
1010 if ((l_api_updating and nvl(pay_ppd_shd.g_old_rec.rate_of_tax,
1011 hr_api.g_varchar2)
1012 <> nvl(p_rate_of_tax,hr_api.g_varchar2)) or
1013 (NOT l_api_updating)) then
1014
1015 --
1016 -- If Rate of tax is not null then
1017 -- Check if the Rate of Tax value exists in hr_lookups
1018 -- where the lookup_type is 'PL_CIVIL_RATE_OF_TAX' or 'PL_NORMAL_RATE_OF_TAX' based on the contract category
1019 --
1020 if p_rate_of_tax is not null then
1021 if p_contract_category in ('NORMAL','TERM_NORMAL') then
1022 if hr_api.not_exists_in_dt_hr_lookups
1023 (p_effective_date => p_effective_date
1024 ,p_validation_start_date => p_validation_start_date
1025 ,p_validation_end_date => p_validation_end_date
1026 ,p_lookup_type => 'PL_NORMAL_RATE_OF_TAX'
1027 ,p_lookup_code => p_rate_of_tax
1028 ) then
1029 -- Error: Invalid Value for Rate of Tax
1030 hr_utility.set_message(801,'PAY_375848_RATE_OF_TAX');
1031 -- This message will be 'Ensure that you enter a valid tax rate for this employee.'
1032 hr_utility.raise_error;
1033 end if;
1034 elsif p_contract_category = 'CIVIL' then
1035 if hr_api.not_exists_in_dt_hr_lookups
1036 (p_effective_date => p_effective_date
1037 ,p_validation_start_date => p_validation_start_date
1038 ,p_validation_end_date => p_validation_end_date
1039 ,p_lookup_type => 'PL_CIVIL_RATE_OF_TAX'
1040 ,p_lookup_code => p_rate_of_tax
1041 ) then
1042 -- Error: Invalid Value for Rate of Tax
1043 hr_utility.set_message(801,'PAY_375848_RATE_OF_TAX');
1044 -- This message will be 'Ensure that you enter a valid tax rate for this employee.'
1045 hr_utility.raise_error;
1046 end if;
1047 elsif p_contract_category = 'F_LUMP' then
1048 if p_rate_of_tax > 100 or p_rate_of_tax < 0 then
1049 hr_utility.set_message(801,'PAY_375891_F_LUMP_RATE_OF_TAX');
1050 -- This message will be 'Ensure that you enter a valid tax rate for this employee.'
1051 hr_utility.raise_error;
1052 end if;
1053 elsif p_contract_category = 'LUMP' then
1054
1055 -- For Contract types L01, L02, L03, L04, L09, L10, L11 we will not store the Rate of Tax
1056 -- in the table pay_pl_paye_details_f
1057 if l_contract_type not in ('L01','L02','L03','L04','L09','L10','L11') then
1058 if p_rate_of_tax > 100 or p_rate_of_tax < 0 then
1059 hr_utility.set_message(801,'PAY_375891_F_LUMP_RATE_OF_TAX');
1060 -- This message will be 'Ensure that you enter a valid tax rate for this employee.'
1061 hr_utility.raise_error;
1062 end if;
1063 end if;
1064 End if;
1065 end if;
1066 end if;
1067 End if;
1068 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1069 exception
1070 when app_exception.application_exception then
1071 if hr_multi_message.exception_add
1072 (p_associated_column1 => 'PAY_PL_PAYE_DETAILS_F.RATE_OF_TAX'
1073 ) then
1074 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1075 raise;
1076 end if;
1077 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1078
1079 End chk_rate_of_tax;
1080 --
1081 -- ----------------------------------------------------------------------------
1082 --|-------------------------< chk_tax_reduction >------------------------------|
1083 -- ----------------------------------------------------------------------------
1084 Procedure chk_tax_reduction
1085 (p_paye_details_id in number
1086 ,p_effective_date in date
1087 ,p_tax_reduction in varchar2
1088 ,p_validation_start_date in date
1089 ,p_validation_end_date in date
1090 ,p_object_version_number in number
1091 ) IS
1092
1093 l_proc varchar2(72);
1094 l_api_updating boolean;
1095
1096 Begin
1097
1098
1099 hr_utility.set_location('Entering:'|| l_proc, 10);
1100 l_proc := g_package ||'chk_tax_reduction';
1101
1102
1103 --
1104 -- Check mandatory parameters have been set
1105 --
1106 hr_api.mandatory_arg_error
1107 (p_api_name => l_proc
1108 ,p_argument => 'effective date'
1109 ,p_argument_value => p_effective_date
1110 );
1111
1112 hr_api.mandatory_arg_error
1113 (p_api_name => l_proc
1114 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','TAX_REDUCTION')
1115 ,p_argument_value => p_tax_reduction
1116 );
1117
1118
1119 -- Only proceed with validation if:
1120 -- a) The current g_old_rec is current and
1121 -- b) Tax Reduction value has changed
1122 -- c) A record is being inserted
1123 --
1124 l_api_updating := pay_ppd_shd.api_updating
1125 (p_paye_details_id => p_paye_details_id
1126 ,p_effective_date => p_effective_date
1127 ,p_object_version_number => p_object_version_number);
1128
1129 if ((l_api_updating and nvl(pay_ppd_shd.g_old_rec.tax_reduction,
1130 hr_api.g_varchar2)
1131 <> nvl(p_tax_reduction,hr_api.g_varchar2)) or
1132 (NOT l_api_updating)) then
1133
1134 --
1135 -- If Tax Reduction is not null then
1136 -- Check if the Tax Reduction value exists in hr_lookups
1137 -- where the lookup_type is 'PL_TAX_REDUCTION'
1138 --
1139 if p_tax_reduction is not null then
1140 if hr_api.not_exists_in_dt_hr_lookups
1141 (p_effective_date => p_effective_date
1142 ,p_validation_start_date => p_validation_start_date
1143 ,p_validation_end_date => p_validation_end_date
1144 ,p_lookup_type => 'PL_TAX_REDUCTION'
1145 ,p_lookup_code => p_tax_reduction
1146 ) then
1147 -- Error: Invalid value for Tax Reduction
1148 hr_utility.set_message(801,'PAY_375849_TAX_REDUCTION');
1149 -- This message will be 'Ensure that you enter a valid tax reduction percentage for this employee.'
1150 hr_utility.raise_error;
1151 end if;
1152 end if;
1153 end if;
1154
1155 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1156
1157 exception
1158 when app_exception.application_exception then
1159 if hr_multi_message.exception_add
1160 (p_associated_column1 => 'PAY_PL_PAYE_DETAILS_F.TAX_REDUCTION'
1161 ) then
1162 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1163 raise;
1164 end if;
1165 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1166
1167 End chk_tax_reduction;
1168 --
1169 -- ----------------------------------------------------------------------------
1170 --|-------------------------< chk_tax_calc_with_spouse_child >------------------|
1171 -- ----------------------------------------------------------------------------
1172 Procedure chk_tax_calc_with_spouse_child
1173 (p_paye_details_id in number
1174 ,p_effective_date in date
1175 ,p_tax_calc_with_spouse_child in varchar2
1176 ,p_validation_start_date in date
1177 ,p_validation_end_date in date
1178 ,p_object_version_number in number
1179 ) IS
1180
1181 l_proc varchar2(72);
1182 l_api_updating boolean;
1183
1184 Begin
1185 hr_utility.set_location('Entering:'|| l_proc, 10);
1186 l_proc := g_package ||'chk_tax_calc_with_spouse_child';
1187
1188 --
1189 -- Check mandatory parameters have been set
1190 --
1191 hr_api.mandatory_arg_error
1192 (p_api_name => l_proc
1193 ,p_argument => 'effective date'
1194 ,p_argument_value => p_effective_date
1195 );
1196
1197 hr_api.mandatory_arg_error
1198 (p_api_name => l_proc
1199 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','TAX_CALC_WITH_SPOUSE_CHILD')
1200 ,p_argument_value => p_tax_calc_with_spouse_child
1201 );
1202
1203
1204 -- Only proceed with validation if:
1208 --
1205 -- a) The current g_old_rec is current and
1206 -- b) Tax Calculation with Spouse or Child value has changed
1207 -- c) A record is being inserted
1209 l_api_updating := pay_ppd_shd.api_updating
1210 (p_paye_details_id => p_paye_details_id
1211 ,p_effective_date => p_effective_date
1212 ,p_object_version_number => p_object_version_number);
1213
1214 if ((l_api_updating and nvl(pay_ppd_shd.g_old_rec.tax_calc_with_spouse_child,
1215 hr_api.g_varchar2)
1216 <> nvl(p_tax_calc_with_spouse_child,hr_api.g_varchar2)) or
1217 (NOT l_api_updating)) then
1218
1219 --
1220 -- If Tax Reduction is not null then
1221 -- Check if the Tax Calculation with Spouse or Child value exists in hr_lookups
1222 -- where the lookup_type is 'YES_NO'
1223 --
1224 if p_tax_calc_with_spouse_child is not null then
1225 if hr_api.not_exists_in_dt_hr_lookups
1226 (p_effective_date => p_effective_date
1227 ,p_validation_start_date => p_validation_start_date
1228 ,p_validation_end_date => p_validation_end_date
1229 ,p_lookup_type => 'YES_NO'
1230 ,p_lookup_code => p_tax_calc_with_spouse_child
1231 ) then
1232 -- Error: Invalid value for Tax calculation with spouse or child
1233 hr_utility.set_message(801,'PAY_375850_TAX_SPOUSE_CHILD');
1234 -- Error Message is as follows
1235 -- 'Ensure that you specify Y or N to indicate whether or not the application should consider tax calculation with spouse or child.'
1236 hr_utility.raise_error;
1237 end if;
1238 end if;
1239 end if;
1240
1241 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1242 exception
1243 when app_exception.application_exception then
1244 if hr_multi_message.exception_add
1245 (p_associated_column1 => 'PAY_PL_PAYE_DETAILS_F.TAX_CALC_WITH_SPOUSE_CHILD'
1246 ) then
1247 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1248 raise;
1249 end if;
1250 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1251
1252 End chk_tax_calc_with_spouse_child;
1253 --
1254 -- ----------------------------------------------------------------------------
1255 --|-------------------------< chk_income_reduction >------------------------------|
1256 -- ----------------------------------------------------------------------------
1257 Procedure chk_income_reduction
1258 (p_paye_details_id in number
1259 ,p_effective_date in date
1260 ,p_income_reduction in varchar2
1261 ,p_validation_start_date in date
1262 ,p_validation_end_date in date
1263 ,p_object_version_number in number
1264 ) IS
1265
1266 l_proc varchar2(72);
1267 l_api_updating boolean;
1268
1269 Begin
1270 hr_utility.set_location('Entering:'|| l_proc, 10);
1271 l_proc := g_package ||'chk_income_reduction';
1272
1273 --
1274 -- Check mandatory parameters have been set
1275 --
1276 hr_api.mandatory_arg_error
1277 (p_api_name => l_proc
1278 ,p_argument => 'effective date'
1279 ,p_argument_value => p_effective_date
1280 );
1281
1282 hr_api.mandatory_arg_error
1283 (p_api_name => l_proc
1284 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','INCOME_REDUCTION')
1285 ,p_argument_value => p_income_reduction
1286 );
1287
1288
1289 -- Only proceed with validation if:
1290 -- a) The current g_old_rec is current and
1291 -- b) Income Reduction value has changed
1292 -- c) A record is being inserted
1293 --
1294 l_api_updating := pay_ppd_shd.api_updating
1295 (p_paye_details_id => p_paye_details_id
1296 ,p_effective_date => p_effective_date
1297 ,p_object_version_number => p_object_version_number);
1298
1299 if ((l_api_updating and nvl(pay_ppd_shd.g_old_rec.income_reduction,
1300 hr_api.g_varchar2)
1301 <> nvl(p_income_reduction,hr_api.g_varchar2)) or
1302 (NOT l_api_updating)) then
1303
1304 --
1305 -- If Income Reduction is not null then
1306 -- Check if the Income Reduction value exists in hr_lookups
1307 -- where the lookup_type is 'PL_INCOME_REDUCTION'
1308 --
1309 if p_income_reduction is not null then
1310 if hr_api.not_exists_in_dt_hr_lookups
1311 (p_effective_date => p_effective_date
1312 ,p_validation_start_date => p_validation_start_date
1313 ,p_validation_end_date => p_validation_end_date
1314 ,p_lookup_type => 'PL_INCOME_REDUCTION'
1315 ,p_lookup_code => p_income_reduction
1316 ) then
1317 -- Error: Invalid Income Reduction
1318 hr_utility.set_message(801,'PAY_375851_INCOME_REDUCTION');
1319 -- This message will be 'Ensure that you enter a valid income reduction percentage for this employee.'
1320 hr_utility.raise_error;
1321 end if;
1322 end if;
1323 end if;
1324 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1325 exception
1326 when app_exception.application_exception then
1327 if hr_multi_message.exception_add
1328 (p_associated_column1 => 'PAY_PL_PAYE_DETAILS_F.INCOME_REDUCTION'
1329 ) then
1330 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1331 raise;
1332 end if;
1333 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1334
1335 End chk_income_reduction;
1336 --
1337 end pay_ppd_bus;