1 Package Body pay_psd_bus as
2 /* $Header: pypsdrhi.pkb 120.1 2005/12/08 05:08 ssekhar noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_psd_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_sii_details_id number default null;
15 --
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< set_security_group_id >--------------------------|
18 -- ---------------------------------------------------------------------------
19 --
20 Procedure set_security_group_id
21 (p_sii_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_sii_details_f psd
32 where psd.sii_details_id = p_sii_details_id
33 and pbg.business_group_id = psd.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 => 'sii_details_id'
50 ,p_argument_value => p_sii_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,'SII_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_sii_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_sii_details_f psd
102 where psd.sii_details_id = p_sii_details_id
103 and pbg.business_group_id = psd.business_group_id;
104 --
105 -- Declare local variables
106 --
107 l_legislation_code varchar2(150);
108 l_proc varchar2(72) := g_package||'return_legislation_code';
109 --
110 Begin
111 --
112 hr_utility.set_location('Entering:'|| l_proc, 10);
113 --
114 -- Ensure that all the mandatory parameter are not null
115 --
116 hr_api.mandatory_arg_error
117 (p_api_name => l_proc
118 ,p_argument => 'sii_details_id'
119 ,p_argument_value => p_sii_details_id
120 );
121 --
122 if ( nvl(pay_psd_bus.g_sii_details_id, hr_api.g_number)
123 = p_sii_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_psd_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_psd_bus.g_sii_details_id := p_sii_details_id;
154 pay_psd_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 -- ----------------------------------------------------------------------------
162 --|-------------------------< chk_contract_category >--------------------------|
163 -- ----------------------------------------------------------------------------
164 -- {Start Of Comments}
165 --
166 -- Description:
167 -- This procedure validates the Contract Category.
168 --
169 -- Prerequisites:
170 --
171 --
172 -- In Parameters:
173 -- p_sii_details_id
174 -- p_effective_date
175 -- p_contract_category
176 -- p_validation_start_date
177 -- p_validation_end_date
178 -- p_object_version_number
179 --
180 -- Post Success:
181 -- Processing continues.
182 --
183 -- Post Failure:
184 -- An error is raised if the validation fails.
185 --
186 --
187 -- Access Status:
188 -- Internal Development Use Only.
189 --
190 -- {End Of Comments}
191 -- ----------------------------------------------------------------------------
192 Procedure chk_contract_category
193 (p_sii_details_id in number
194 ,p_effective_date in date
195 ,p_contract_category in varchar2
196 ,p_validation_start_date in date
197 ,p_validation_end_date in date
198 ,p_object_version_number in number
199 ) IS
200
201 l_proc varchar2(72);
202
203 Begin
204 hr_utility.set_location('Entering:'|| l_proc, 10);
205 l_proc := g_package ||'chk_contract_category';
206
207 --
208 -- Check mandatory parameters have been set
209 --
210 hr_api.mandatory_arg_error
211 (p_api_name => l_proc
212 ,p_argument => 'effective date'
213 ,p_argument_value => p_effective_date
214 );
215
216 hr_api.mandatory_arg_error
217 (p_api_name => l_proc
218 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','CONTRACT_CATEGORY')
219 ,p_argument_value => p_contract_category
220 );
221
222 -- If Contract Category is not null then
223 -- Check if the Contract Category value exists in hr_lookups
224 -- where the lookup_type is 'PL_CONTRACT_CATEGORY'
225 --
226 if p_contract_category is not null then
227 if hr_api.not_exists_in_dt_hr_lookups
228 (p_effective_date => p_effective_date
229 ,p_validation_start_date => p_validation_start_date
230 ,p_validation_end_date => p_validation_end_date
231 ,p_lookup_type => 'PL_CONTRACT_CATEGORY'
232 ,p_lookup_code => p_contract_category
233 ) then
234 -- Error: Invalid Contract Category
235 hr_utility.set_message(801,'PAY_375841_CONTRACT_PL_LOOKUP');
236 -- This message will be 'The Contract Category does not exist in the system'
237 hr_utility.raise_error;
238 end if;
239 end if;
240 hr_utility.set_location(' Leaving:'|| l_proc, 20);
241 exception
242 when app_exception.application_exception then
243 if hr_multi_message.exception_add
244 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.CONTRACT_CATEGORY'
245 ) then
246 hr_utility.set_location(' Leaving:'|| l_proc, 30);
247 raise;
248 end if;
249 hr_utility.set_location(' Leaving:'|| l_proc, 40);
250
251 End chk_contract_category;
252
253 --
254 -- ----------------------------------------------------------------------------
255 --|-------------------------< chk_business_group_id >--------------------------|
256 -- ----------------------------------------------------------------------------
257 -- {Start Of Comments}
258 --
259 -- Description:
260 -- This procedure validates the Business Group Id.
261 --
262 -- Prerequisites:
263 --
264 --
265 -- In Parameters:
266 -- p_sii_details_id
267 -- p_effective_date
268 -- p_business_group_id
269 -- p_validation_start_date
270 -- p_validation_end_date
271 -- p_object_version_number
272 --
273 -- Post Success:
274 -- Processing continues.
275 --
276 -- Post Failure:
277 -- An error is raised if the validation fails.
278 --
279 --
280 -- Access Status:
281 -- Internal Development Use Only.
282 --
283 -- {End Of Comments}
284 -- ----------------------------------------------------------------------------
285 Procedure chk_business_group_id
286 (p_sii_details_id in number
287 ,p_effective_date in date
288 ,p_business_group_id in number
289 ,p_validation_start_date in date
290 ,p_validation_end_date in date
291 ,p_object_version_number in number
292 ) IS
293
294 l_proc varchar2(72);
295
296 Begin
297 hr_utility.set_location('Entering:'|| l_proc, 10);
298 l_proc := g_package ||'chk_business_group_id';
299
300 --
301 -- Check mandatory parameters have been set
302 --
303 hr_api.mandatory_arg_error
304 (p_api_name => l_proc
305 ,p_argument => 'effective date'
306 ,p_argument_value => p_effective_date
307 );
308
309 hr_api.mandatory_arg_error
310 (p_api_name => l_proc
311 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','BUSINESS_GROUP')
312 ,p_argument_value => p_business_group_id
313 );
314
315 hr_api.validate_bus_grp_id
316 (p_business_group_id => p_business_group_id
317 ,p_associated_column1 => pay_psd_shd.g_tab_nam||'.BUSINESS_GROUP_ID');
318
319 hr_multi_message.end_validation_set;
320
321 hr_utility.set_location(' Leaving:'|| l_proc, 20);
322 exception
323 when app_exception.application_exception then
324 if hr_multi_message.exception_add
325 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.BUSINESS_GROUP_ID'
326 ) then
327 hr_utility.set_location(' Leaving:'|| l_proc, 30);
328 raise;
329 end if;
330 hr_utility.set_location(' Leaving:'|| l_proc, 40);
331
332 End chk_business_group_id;
333 --
334 --
335 -- ----------------------------------------------------------------------------
336 --|---------------------------< chk_per_asg_id >-------------------------------|
337 -- ----------------------------------------------------------------------------
338 -- {Start Of Comments}
339 --
340 -- Description:
341 -- This procedure validates the Person/Assignment Id.
342 --
343 -- Prerequisites:
344 --
345 --
346 -- In Parameters:
347 -- p_sii_details_id
348 -- p_effective_date
349 -- p_per_or_asg_id
350 -- p_business_group_id
351 -- p_contract_category
352 -- p_object_version_number
353 --
354 -- Post Success:
355 -- Processing continues.
356 --
357 -- Post Failure:
358 -- An error is raised if the validation fails.
359 --
360 --
361 -- Access Status:
362 -- Internal Development Use Only.
363 --
364 -- {End Of Comments}
365 -- ----------------------------------------------------------------------------
366 Procedure chk_per_asg_id
367 (p_effective_date in date
368 ,p_per_or_asg_id in number
369 ,p_contract_category in varchar2
370 ,p_business_group_id in number
371 ,p_object_version_number in number
372 ) IS
373
374 l_proc varchar2(72);
375 l_exists varchar2(1);
376 l_civil_catg hr_soft_coding_keyflex.segment3%TYPE;
377 l_term_catg hr_soft_coding_keyflex.segment3%TYPE;
378
379 l_lump_catg hr_soft_coding_keyflex.segment3%TYPE;
380 l_f_lump_catg hr_soft_coding_keyflex.segment3%TYPE;
381
382 cursor csr_per_id is
383 select null
384 from per_all_people_f papf
385 where papf.person_id = p_per_or_asg_id and
386 papf.business_group_id = p_business_group_id and
387 p_effective_date between papf.effective_start_date and
388 papf.effective_end_date and
389 papf.person_type_id in (select person_type_id from per_person_types
390 where business_group_id = p_business_group_id
391 and system_person_type in ('EMP','EMP_APL'));
392
393 cursor csr_asg_id is
394 select null
395 from per_all_assignments_f paaf, hr_soft_coding_keyflex hrsoft
396 where paaf.assignment_id = p_per_or_asg_id and
397 paaf.business_group_id = p_business_group_id and
398 p_effective_date between paaf.effective_start_date and
399 paaf.effective_end_date and
400 paaf.assignment_status_type_id in (select assignment_status_type_id from
401 per_assignment_status_types where
402 per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN'))
403 and paaf.soft_coding_keyflex_id = hrsoft.soft_coding_keyflex_id and
404 hrsoft.segment3 in (l_civil_catg,l_lump_catg,l_f_lump_catg);
405
406
407 cursor csr_normal_term_id is
408 select null
409 from per_all_assignments_f paaf, hr_soft_coding_keyflex hrsoft
410 where paaf.assignment_id = p_per_or_asg_id and
411 paaf.business_group_id = p_business_group_id and
412 p_effective_date between paaf.effective_start_date and
413 paaf.effective_end_date and
414 paaf.assignment_status_type_id in (select assignment_status_type_id from
415 per_assignment_status_types where
416 per_system_status = 'TERM_ASSIGN')
417 and paaf.soft_coding_keyflex_id = hrsoft.soft_coding_keyflex_id and
418 hrsoft.segment3 = l_term_catg;
419
420 Begin
421 hr_utility.set_location('Entering:'|| l_proc, 10);
422 l_proc := g_package ||'chk_per_asg_id';
423 l_exists := NULL;
424
425 l_civil_catg := 'CIVIL';
426 l_term_catg := 'NORMAL';
427
428 l_lump_catg := 'LUMP';
432 -- Check mandatory parameters have been set
429 l_f_lump_catg := 'F_LUMP';
430
431 --
433 --
434 hr_api.mandatory_arg_error
435 (p_api_name => l_proc
436 ,p_argument => 'effective date'
437 ,p_argument_value => p_effective_date
438 );
439
440 hr_api.mandatory_arg_error
441 (p_api_name => l_proc
442 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','PER_ASG_ID')
443 ,p_argument_value => p_per_or_asg_id
444 );
445
446 if hr_multi_message.no_exclusive_error
447 (p_check_column1 => 'PAY_PL_SII_DETAILS_F.CONTRACT_CATEGORY'
448 ,p_check_column2 => 'PAY_PL_SII_DETAILS_F.BUSINESS_GROUP_ID'
449 ,p_associated_column1 => 'PAY_PL_SII_DETAILS_F.PER_OR_ASG_ID') then
450
451 -- Continue with valiadtion only if the columns
452 -- a) BUSINESS_GROUP_ID and
453 -- b) CONTRACT_CATEGORY are valid.
454
455 if p_contract_category in ('CIVIL','LUMP','F_LUMP') then
456 -- Since Civil SII records are stored at the Assignment level, we open csr_asg_id
457 open csr_asg_id;
458 fetch csr_asg_id into l_exists;
459 if csr_asg_id%NOTFOUND then
460 -- Raise an error message that the record is not in the business group for the date range specified.
461 hr_utility.set_message(801,'PAY_375840_INVALID_PL_ASG_ID');
462 hr_utility.raise_error;
463 end if;
464 close csr_asg_id;
465
466 elsif p_contract_category = 'NORMAL' then
467 -- Since Normal SII records are stored at Person level, we open csr_per_id
468 open csr_per_id;
469 fetch csr_per_id into l_exists;
470 if csr_per_id%NOTFOUND then
471 -- Raise an error message that the records isnot in the business group for the date range specified
472 hr_utility.set_message(801,'PAY_375839_INVALID_PL_PER_ID');
473 hr_utility.raise_error;
474 end if;
475 close csr_per_id;
476
477 elsif p_contract_category = 'TERM_NORMAL' then
478 -- Since Normal Terminated SII records are stored at Assignment level, we open csr_normal_term_id
479 open csr_normal_term_id;
480 fetch csr_normal_term_id into l_exists;
481 if csr_normal_term_id%NOTFOUND then
482 -- Raise an error message that the record is not in the business group for the date range
483 hr_utility.set_message(801,'PAY_375840_INVALID_PL_ASG_ID');
484 hr_utility.raise_error;
485 end if;
486 close csr_normal_term_id;
487
488 end if;
489 end if;
490
491 hr_utility.set_location(' Leaving:'|| l_proc, 20);
492 exception
493 when app_exception.application_exception then
494 if hr_multi_message.exception_add
495 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.PER_OR_ASG_ID'
496 ) then
497 hr_utility.set_location(' Leaving:'|| l_proc, 30);
498 raise;
499 end if;
500 hr_utility.set_location(' Leaving:'|| l_proc, 40);
501
502 End chk_per_asg_id;
503
504 --
505 ---- ----------------------------------------------------------------------------
506 --|---------------------< chk_emp_social_security_info >------------------------|
507 -- ----------------------------------------------------------------------------
508 -- {Start Of Comments}
509 --
510 -- Description:
511 -- This procedure validates the Employee Social Security Information values.
512 --
513 -- Prerequisites:
514 --
515 --
516 -- In Parameters:
517 -- p_sii_details_id
518 -- p_effective_date
519 -- p_emp_social_security_info
520 -- p_object_version_number
521 --
522 --
523 -- Post Success:
524 -- Processing continues.
525 --
526 -- Post Failure:
527 -- An error is raised if the validation fails.
528 --
529 --
530 -- Access Status:
531 -- Internal Development Use Only.
532 --
533 -- {End Of Comments}
534 -- ----------------------------------------------------------------------------
535 Procedure chk_emp_social_security_info
536 (p_sii_details_id in number
537 ,p_effective_date in date
538 ,p_emp_social_security_info in varchar2
539 ,p_object_version_number in number
540 ) IS
541
542 l_proc varchar2(72);
543 l_api_updating boolean;
544 l_exists varchar2(1);
545
546 l_user_table_name pay_user_tables.user_table_name%TYPE;
547 l_legislation_code pay_user_columns.legislation_code%TYPE;
548
549 cursor csr_emp_social_security_info is
550 select null
551 from pay_user_columns puc, pay_user_tables put
552 where puc.user_table_id = put.user_table_id
553 and put.user_table_name = l_user_table_name
554 and put.legislation_code = l_legislation_code
555 and puc.user_column_name = p_emp_social_security_info;
556
557 Begin
558 hr_utility.set_location('Entering:'|| l_proc, 10);
559 l_proc := g_package ||'chk_emp_social_security_info';
560
561 -- Assigning values to local variables
562 l_user_table_name := 'PL_EMPLOYEE_SOCIAL_SECURITY_INFORMATION';
563 l_legislation_code := 'PL';
564
565 --
566 -- Check mandatory parameters have been set
567 --
568 hr_api.mandatory_arg_error
569 (p_api_name => l_proc
570 ,p_argument => 'effective date'
571 ,p_argument_value => p_effective_date
572 );
573
574 hr_api.mandatory_arg_error
575 (p_api_name => l_proc
576 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','EMP_SOCIAL_SECURITY_INFO')
577 ,p_argument_value => p_emp_social_security_info
578 );
579
580
584 -- c) A record is being inserted
581 -- Only proceed with validation if:
582 -- a) The current g_old_rec is current and
583 -- b) Employee Social Security Information has changed
585 --
586 l_api_updating := pay_psd_shd.api_updating
587 (p_sii_details_id => p_sii_details_id
588 ,p_effective_date => p_effective_date
589 ,p_object_version_number => p_object_version_number);
590
591 if ((l_api_updating and nvl(pay_psd_shd.g_old_rec.emp_social_security_info,
592 hr_api.g_varchar2)
593 <> nvl(p_emp_social_security_info,hr_api.g_varchar2)) or
594 (NOT l_api_updating)) then
595
596 --
597 -- If Employee Social Security Information is not null then
598 -- Check if the Employee Social Security Information value exists in User Tables
599 -- where the user table name is 'PL_EMP_SOCIAL_SECURITY_INFO'
600 --
601 if p_emp_social_security_info is not null then
602 open csr_emp_social_security_info;
603 fetch csr_emp_social_security_info into l_exists;
604 if csr_emp_social_security_info%NOTFOUND then
605 hr_utility.set_message(801,'PAY_375842_INVALID_SOCIAL_INFO');
606 hr_utility.raise_error;
607 end if;
608 close csr_emp_social_security_info;
609 end if;
610 end if;
611 hr_utility.set_location(' Leaving:'|| l_proc, 20);
612 exception
613 when app_exception.application_exception then
614 if hr_multi_message.exception_add
615 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.EMP_SOCIAL_SECURITY_INFO'
616 ) then
617 hr_utility.set_location(' Leaving:'|| l_proc, 30);
618 raise;
619 end if;
620 hr_utility.set_location(' Leaving:'|| l_proc, 40);
621
622 End chk_emp_social_security_info;
623 --
624 --
625 -- ----------------------------------------------------------------------------
626 --|---------------------< chk_old_age_contribution >--------------------------|
627 -- ----------------------------------------------------------------------------
628 -- {Start Of Comments}
629 --
630 -- Description:
631 -- This procedure validates the Old Age Contribution values.
632 --
633 -- Prerequisites:
634 --
635 --
636 -- In Parameters:
637 -- p_sii_details_id
638 -- p_effective_date
639 -- p_old_age_contribution
640 -- p_validation_start_date
641 -- p_validation_end_date
642 -- p_object_version_number
643 -- p_contract_category
644 --
645 -- Post Success:
646 -- Processing continues.
647 --
648 -- Post Failure:
649 -- An error is raised if the validation fails.
650 --
651 --
652 -- Access Status:
653 -- Internal Development Use Only.
654 --
655 -- {End Of Comments}
656 -- ----------------------------------------------------------------------------
657 Procedure chk_old_age_contribution
658 (p_sii_details_id in number
659 ,p_effective_date in date
660 ,p_old_age_contribution in varchar2
661 ,p_validation_start_date in date
662 ,p_validation_end_date in date
663 ,p_object_version_number in number
664 ,p_contract_category in varchar2
665 ) IS
666
667 l_proc varchar2(72);
668 l_api_updating boolean;
669
670 Begin
671 hr_utility.set_location('Entering:'|| l_proc, 10);
672 l_proc := g_package ||'chk_old_age_contribution';
673
674 --
675 -- Check mandatory parameters have been set
676 --
677 hr_api.mandatory_arg_error
678 (p_api_name => l_proc
679 ,p_argument => 'effective date'
680 ,p_argument_value => p_effective_date
681 );
682
683 hr_api.mandatory_arg_error
684 (p_api_name => l_proc
685 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','OLD_AGE_CONTRIBUTION')
686 ,p_argument_value => p_old_age_contribution
687 );
688
689
690 -- Only proceed with validation if:
691 -- a) The current g_old_rec is current and
692 -- b) Old Age Contribution value has changed
693 -- c) A record is being inserted
694 --
695 l_api_updating := pay_psd_shd.api_updating
696 (p_sii_details_id => p_sii_details_id
697 ,p_effective_date => p_effective_date
698 ,p_object_version_number => p_object_version_number);
699
700 if ((l_api_updating and nvl(pay_psd_shd.g_old_rec.old_age_contribution,
701 hr_api.g_varchar2)
702 <> nvl(p_old_age_contribution,hr_api.g_varchar2)) or
703 (NOT l_api_updating)) then
704
705 --
706 -- If Old Age Contribution is not null then
707 -- Check if the Old Age Contribution value exists in hr_lookups
708 -- where the lookup_type is 'PL_CONTRIBUTION_TYPE'
709 --
710 if p_old_age_contribution is not null then
711 if hr_api.not_exists_in_dt_hr_lookups
712 (p_effective_date => p_effective_date
713 ,p_validation_start_date => p_validation_start_date
714 ,p_validation_end_date => p_validation_end_date
715 ,p_lookup_type => 'PL_CONTRIBUTION_TYPE'
716 ,p_lookup_code => p_old_age_contribution
717 ) then
718 -- Error: Invalid Contract Category
719 hr_utility.set_message(801,'PAY_375843_INVALID_CONTRI_TYPE');
720 -- This message will be 'The Contribution type does not exist in the system'
721 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','OLD_AGE_CONTRIBUTION'));
722 hr_utility.raise_error;
723 end if;
727 hr_utility.set_message(801,'PAY_375871_INVALID_NORMAL_CONT');
724 -- Raise an error if 'Voluntary' is specified for a 'NORMAL' or 'TERM_NORMAL' category
725 if (p_contract_category in ('NORMAL','TERM_NORMAL') and p_old_age_contribution = 'D') then
726 -- Error: Invalid Contract Category
728 -- This message will be 'The Contribution type does not exist in the system'
729 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','OLD_AGE_CONTRIBUTION'));
730 hr_utility.raise_error;
731 end if;
732 end if;
733 end if;
734 hr_utility.set_location(' Leaving:'|| l_proc, 20);
735 exception
736 when app_exception.application_exception then
737 if hr_multi_message.exception_add
738 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.OLD_AGE_CONTRIBUTION'
739 ) then
740 hr_utility.set_location(' Leaving:'|| l_proc, 30);
741 raise;
742 end if;
743 hr_utility.set_location(' Leaving:'|| l_proc, 40);
744
745 End chk_old_age_contribution;
746 --
747 --
748 -- ----------------------------------------------------------------------------
749 --|---------------------< chk_pension_contribution >--------------------------|
750 -- ----------------------------------------------------------------------------
751 -- {Start Of Comments}
752 --
753 -- Description:
754 -- This procedure validates the Pension Contribution values.
755 --
756 -- Prerequisites:
757 --
758 --
759 -- In Parameters:
760 -- p_sii_details_id
761 -- p_effective_date
762 -- p_pension_contribution
763 -- p_validation_start_date
764 -- p_validation_end_date
765 -- p_object_version_number
766 -- p_contract_category
767 --
768 -- Post Success:
769 -- Processing continues.
770 --
771 -- Post Failure:
772 -- An error is raised if the validation fails.
773 --
774 --
775 -- Access Status:
776 -- Internal Development Use Only.
777 --
778 -- {End Of Comments}
779 -- ----------------------------------------------------------------------------
780 Procedure chk_pension_contribution
781 (p_sii_details_id in number
782 ,p_effective_date in date
783 ,p_pension_contribution in varchar2
784 ,p_validation_start_date in date
785 ,p_validation_end_date in date
786 ,p_object_version_number in number
787 ,p_contract_category in varchar2
788 ) IS
789
790 l_proc varchar2(72);
791 l_api_updating boolean;
792
793 Begin
794 hr_utility.set_location('Entering:'|| l_proc, 10);
795 l_proc := g_package ||'chk_pension_contribution';
796
797 --
798 -- Check mandatory parameters have been set
799 --
800 hr_api.mandatory_arg_error
801 (p_api_name => l_proc
802 ,p_argument => 'effective date'
803 ,p_argument_value => p_effective_date
804 );
805
806 hr_api.mandatory_arg_error
807 (p_api_name => l_proc
808 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','PENSION_CONTRIBUTION')
809 ,p_argument_value => p_pension_contribution
810 );
811
812
813 -- Only proceed with validation if:
814 -- a) The current g_old_rec is current and
815 -- b) Pension Contribution value has changed
816 -- c) A record is being inserted
817 --
818 l_api_updating := pay_psd_shd.api_updating
819 (p_sii_details_id => p_sii_details_id
820 ,p_effective_date => p_effective_date
821 ,p_object_version_number => p_object_version_number);
822
823 if ((l_api_updating and nvl(pay_psd_shd.g_old_rec.pension_contribution,
824 hr_api.g_varchar2)
825 <> nvl(p_pension_contribution,hr_api.g_varchar2)) or
826 (NOT l_api_updating)) then
827
828 --
829 -- If Pension Contribution is not null then
830 -- Check if the Pension Contribution value exists in hr_lookups
831 -- where the lookup_type is 'PL_CONTRIBUTION_TYPE'
832 --
833 if p_pension_contribution is not null then
834 if hr_api.not_exists_in_dt_hr_lookups
835 (p_effective_date => p_effective_date
836 ,p_validation_start_date => p_validation_start_date
837 ,p_validation_end_date => p_validation_end_date
838 ,p_lookup_type => 'PL_CONTRIBUTION_TYPE'
839 ,p_lookup_code => p_pension_contribution
840 ) then
841 -- Error: Invalid Contract Category
842 hr_utility.set_message(801,'PAY_375843_INVALID_CONTRI_TYPE');
843 -- This message will be 'The Contribution type does not exist in the system'
844 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','PENSION_CONTRIBUTION'));
845 hr_utility.raise_error;
846 end if;
847
848 -- Raise an error if 'Voluntary' is specified for a 'NORMAL' or 'TERM_NORMAL' category
849 if (p_contract_category in ('NORMAL','TERM_NORMAL') and p_pension_contribution = 'D') then
850 -- Error: Invalid Contract Category
851 hr_utility.set_message(801,'PAY_375871_INVALID_NORMAL_CONT');
852 -- This message will be 'The Contribution type does not exist in the system'
853 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','PENSION_CONTRIBUTION'));
854 hr_utility.raise_error;
855 end if;
856 end if;
857 end if;
858 hr_utility.set_location(' Leaving:'|| l_proc, 20);
859 exception
860 when app_exception.application_exception then
861 if hr_multi_message.exception_add
865 raise;
862 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.PENSION_CONTRIBUTION'
863 ) then
864 hr_utility.set_location(' Leaving:'|| l_proc, 30);
866 end if;
867 hr_utility.set_location(' Leaving:'|| l_proc, 40);
868
869 End chk_pension_contribution;
870 --
871 --
872 -- ----------------------------------------------------------------------------
873 --|---------------------< chk_sickness_contribution >--------------------------|
874 -- ----------------------------------------------------------------------------
875 -- {Start Of Comments}
876 --
877 -- Description:
878 -- This procedure validates the Sickness Contribution values.
879 --
880 -- Prerequisites:
881 --
882 --
883 -- In Parameters:
884 -- p_sii_details_id
885 -- p_effective_date
886 -- p_sickness_contribution
887 -- p_validation_start_date
888 -- p_validation_end_date
889 -- p_object_version_number
890 -- p_contract_category
891 --
892 -- Post Success:
893 -- Processing continues.
894 --
895 -- Post Failure:
896 -- An error is raised if the validation fails.
897 --
898 --
899 -- Access Status:
900 -- Internal Development Use Only.
901 --
902 -- {End Of Comments}
903 -- ----------------------------------------------------------------------------
904 Procedure chk_sickness_contribution
905 (p_sii_details_id in number
906 ,p_effective_date in date
907 ,p_sickness_contribution in varchar2
908 ,p_validation_start_date in date
909 ,p_validation_end_date in date
910 ,p_object_version_number in number
911 ,p_contract_category in varchar2
912 ) IS
913
914 l_proc varchar2(72);
915 l_api_updating boolean;
916
917 Begin
918 hr_utility.set_location('Entering:'|| l_proc, 10);
919 l_proc := g_package ||'chk_sickness_contribution';
920
921 --
922 -- Check mandatory parameters have been set
923 --
924 hr_api.mandatory_arg_error
925 (p_api_name => l_proc
926 ,p_argument => 'effective date'
927 ,p_argument_value => p_effective_date
928 );
929
930 hr_api.mandatory_arg_error
931 (p_api_name => l_proc
932 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','SICKNESS_CONTRIBUTION')
933 ,p_argument_value => p_sickness_contribution
934 );
935
936
937 -- Only proceed with validation if:
938 -- a) The current g_old_rec is current and
939 -- b) Sickness Contribution value has changed
940 -- c) A record is being inserted
941 --
942 l_api_updating := pay_psd_shd.api_updating
943 (p_sii_details_id => p_sii_details_id
944 ,p_effective_date => p_effective_date
945 ,p_object_version_number => p_object_version_number);
946
947 if ((l_api_updating and nvl(pay_psd_shd.g_old_rec.sickness_contribution,
948 hr_api.g_varchar2)
949 <> nvl(p_sickness_contribution,hr_api.g_varchar2)) or
950 (NOT l_api_updating)) then
951
952 --
953 -- If Sickness Contribution is not null then
954 -- Check if the Sickness Contribution value exists in hr_lookups
955 -- where the lookup_type is 'PL_CONTRIBUTION_TYPE'
956 --
957 if p_sickness_contribution is not null then
958 if hr_api.not_exists_in_dt_hr_lookups
959 (p_effective_date => p_effective_date
960 ,p_validation_start_date => p_validation_start_date
961 ,p_validation_end_date => p_validation_end_date
962 ,p_lookup_type => 'PL_CONTRIBUTION_TYPE'
963 ,p_lookup_code => p_sickness_contribution
964 ) then
965 -- Error: Invalid Contract Category
966 hr_utility.set_message(801,'PAY_375843_INVALID_CONTRI_TYPE');
967 -- This message will be 'The Contribution type does not exist in the system'
968 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','SICKNESS_CONTRIBUTION'));
969 hr_utility.raise_error;
970 end if;
971
972 -- Raise an error if 'Voluntary' is specified for a 'NORMAL' or 'TERM_NORMAL' category
973 if (p_contract_category in ('NORMAL','TERM_NORMAL') and p_sickness_contribution = 'D') then
974 -- Error: Invalid Contract Category
975 hr_utility.set_message(801,'PAY_375871_INVALID_NORMAL_CONT');
976 -- This message will be 'The Contribution type does not exist in the system'
977 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','SICKNESS_CONTRIBUTION'));
978 hr_utility.raise_error;
979 end if;
980 end if;
981 end if;
982 hr_utility.set_location(' Leaving:'|| l_proc, 20);
983 exception
984 when app_exception.application_exception then
985 if hr_multi_message.exception_add
986 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.SICKNESS_CONTRIBUTION'
987 ) then
988 hr_utility.set_location(' Leaving:'|| l_proc, 30);
989 raise;
990 end if;
991 hr_utility.set_location(' Leaving:'|| l_proc, 40);
992
993 End chk_sickness_contribution;
994 --
995 --
996 -- ----------------------------------------------------------------------------
997 --|--------------------< chk_work_injury_contribution >------------------------|
998 -- ----------------------------------------------------------------------------
999 -- {Start Of Comments}
1000 --
1001 -- Description:
1002 -- This procedure validates the Work Injury Contribution values.
1003 --
1004 -- Prerequisites:
1005 --
1006 --
1007 -- In Parameters:
1011 -- p_validation_start_date
1008 -- p_sii_details_id
1009 -- p_effective_date
1010 -- p_work_injury_contribution
1012 -- p_validation_end_date
1013 -- p_object_version_number
1014 -- p_contract_category
1015 --
1016 -- Post Success:
1017 -- Processing continues.
1018 --
1019 -- Post Failure:
1020 -- An error is raised if the validation fails.
1021 --
1022 --
1023 -- Access Status:
1024 -- Internal Development Use Only.
1025 --
1026 -- {End Of Comments}
1027 -- ----------------------------------------------------------------------------
1028 Procedure chk_work_injury_contribution
1029 (p_sii_details_id in number
1030 ,p_effective_date in date
1031 ,p_work_injury_contribution in varchar2
1032 ,p_validation_start_date in date
1033 ,p_validation_end_date in date
1034 ,p_object_version_number in number
1035 ,p_contract_category in varchar2
1036 ) IS
1037
1038 l_proc varchar2(72);
1039 l_api_updating boolean;
1040
1041 Begin
1042 hr_utility.set_location('Entering:'|| l_proc, 10);
1043 l_proc := g_package ||'chk_work_injury_contribution';
1044
1045 --
1046 -- Check mandatory parameters have been set
1047 --
1048 hr_api.mandatory_arg_error
1049 (p_api_name => l_proc
1050 ,p_argument => 'effective date'
1051 ,p_argument_value => p_effective_date
1052 );
1053
1054 hr_api.mandatory_arg_error
1055 (p_api_name => l_proc
1056 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','WORK_INJURY_CONTRIBUTION')
1057 ,p_argument_value => p_work_injury_contribution
1058 );
1059
1060
1061 -- Only proceed with validation if:
1062 -- a) The current g_old_rec is current and
1063 -- b) Work Injury Contribution value has changed
1064 -- c) A record is being inserted
1065 --
1066 l_api_updating := pay_psd_shd.api_updating
1067 (p_sii_details_id => p_sii_details_id
1068 ,p_effective_date => p_effective_date
1069 ,p_object_version_number => p_object_version_number);
1070
1071 if ((l_api_updating and nvl(pay_psd_shd.g_old_rec.work_injury_contribution,
1072 hr_api.g_varchar2)
1073 <> nvl(p_work_injury_contribution,hr_api.g_varchar2)) or
1074 (NOT l_api_updating)) then
1075
1076 --
1077 -- If Work Injury Contribution is not null then
1078 -- Check if the Work Injury Contribution value exists in hr_lookups
1079 -- where the lookup_type is 'PL_CONTRIBUTION_TYPE'
1080 --
1081 if p_work_injury_contribution is not null then
1082 if hr_api.not_exists_in_dt_hr_lookups
1083 (p_effective_date => p_effective_date
1084 ,p_validation_start_date => p_validation_start_date
1085 ,p_validation_end_date => p_validation_end_date
1086 ,p_lookup_type => 'PL_CONTRIBUTION_TYPE'
1087 ,p_lookup_code => p_work_injury_contribution
1088 ) then
1089 -- Error: Invalid Contract Category
1090 hr_utility.set_message(801,'PAY_375843_INVALID_CONTRI_TYPE');
1091 -- This message will be 'The Contribution type does not exist in the system'
1092 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','WORK_INJURY_CONTRIBUTION'));
1093 hr_utility.raise_error;
1094 end if;
1095
1096 -- Raise an error if 'Voluntary' is specified for a 'NORMAL' or 'TERM_NORMAL' category
1097 if (p_contract_category in ('NORMAL','TERM_NORMAL') and p_work_injury_contribution = 'D') then
1098 -- Error: Invalid Contract Category
1099 hr_utility.set_message(801,'PAY_375871_INVALID_NORMAL_CONT');
1100 -- This message will be 'The Contribution type does not exist in the system'
1101 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','WORK_INJURY_CONTRIBUTION'));
1102 hr_utility.raise_error;
1103 end if;
1104 end if;
1105 end if;
1106 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1107 exception
1108 when app_exception.application_exception then
1109 if hr_multi_message.exception_add
1110 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.WORK_INJURY_CONTRIBUTION'
1111 ) then
1112 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1113 raise;
1114 end if;
1115 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1116
1117 End chk_work_injury_contribution;
1118 --
1119 --
1120 -- ----------------------------------------------------------------------------
1121 --|----------------------< chk_labor_contribution >----------------------------|
1122 -- ----------------------------------------------------------------------------
1123 -- {Start Of Comments}
1124 --
1125 -- Description:
1126 -- This procedure validates the Labor Contribution values.
1127 --
1128 -- Prerequisites:
1129 --
1130 --
1131 -- In Parameters:
1132 -- p_sii_details_id
1133 -- p_effective_date
1134 -- p_labor_contribution
1135 -- p_validation_start_date
1136 -- p_validation_end_date
1137 -- p_object_version_number
1138 -- p_contract_category
1139 --
1140 -- Post Success:
1141 -- Processing continues.
1142 --
1143 -- Post Failure:
1144 -- An error is raised if the validation fails.
1145 --
1146 --
1147 -- Access Status:
1148 -- Internal Development Use Only.
1149 --
1150 -- {End Of Comments}
1151 -- ----------------------------------------------------------------------------
1152 Procedure chk_labor_contribution
1153 (p_sii_details_id in number
1154 ,p_effective_date in date
1158 ,p_object_version_number in number
1155 ,p_labor_contribution in varchar2
1156 ,p_validation_start_date in date
1157 ,p_validation_end_date in date
1159 ,p_contract_category in varchar2
1160 ) IS
1161
1162 l_proc varchar2(72);
1163 l_api_updating boolean;
1164
1165 Begin
1166 hr_utility.set_location('Entering:'|| l_proc, 10);
1167 l_proc := g_package ||'chk_labor_contribution';
1168
1169 --
1170 -- Check mandatory parameters have been set
1171 --
1172 hr_api.mandatory_arg_error
1173 (p_api_name => l_proc
1174 ,p_argument => 'effective date'
1175 ,p_argument_value => p_effective_date
1176 );
1177
1178 hr_api.mandatory_arg_error
1179 (p_api_name => l_proc
1180 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','LABOR_CONTRIBUTION')
1181 ,p_argument_value => p_labor_contribution
1182 );
1183
1184
1185 -- Only proceed with validation if:
1186 -- a) The current g_old_rec is current and
1187 -- b) Labor Contribution value has changed
1188 -- c) A record is being inserted
1189 --
1190 l_api_updating := pay_psd_shd.api_updating
1191 (p_sii_details_id => p_sii_details_id
1192 ,p_effective_date => p_effective_date
1193 ,p_object_version_number => p_object_version_number);
1194
1195 if ((l_api_updating and nvl(pay_psd_shd.g_old_rec.labor_contribution,
1196 hr_api.g_varchar2)
1197 <> nvl(p_labor_contribution,hr_api.g_varchar2)) or
1198 (NOT l_api_updating)) then
1199
1200 --
1201 -- If Labor Contribution is not null then
1202 -- Check if the Labor Contribution value exists in hr_lookups
1203 -- where the lookup_type is 'PL_CONTRIBUTION_TYPE'
1204 --
1205 if p_labor_contribution is not null then
1206 if hr_api.not_exists_in_dt_hr_lookups
1207 (p_effective_date => p_effective_date
1208 ,p_validation_start_date => p_validation_start_date
1209 ,p_validation_end_date => p_validation_end_date
1210 ,p_lookup_type => 'PL_CONTRIBUTION_TYPE'
1211 ,p_lookup_code => p_labor_contribution
1212 ) then
1213 -- Error: Invalid Contract Category
1214 hr_utility.set_message(801,'PAY_375843_INVALID_CONTRI_TYPE');
1215 -- This message will be 'The Contribution type does not exist in the system'
1216 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','LABOR_CONTRIBUTION'));
1217 hr_utility.raise_error;
1218 end if;
1219 -- Raise an error if 'Voluntary' is specified for a 'NORMAL' or 'TERM_NORMAL' category
1220 if (p_contract_category in ('NORMAL','TERM_NORMAL') and p_labor_contribution = 'D') then
1221 -- Error: Invalid Contract Category
1222 hr_utility.set_message(801,'PAY_375871_INVALID_NORMAL_CONT');
1223 -- This message will be 'The Contribution type does not exist in the system'
1224 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','LABOR_CONTRIBUTION'));
1225 hr_utility.raise_error;
1226 end if;
1227 end if;
1228 end if;
1229 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1230 exception
1231 when app_exception.application_exception then
1232 if hr_multi_message.exception_add
1233 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.LABOR_CONTRIBUTION'
1234 ) then
1235 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1236 raise;
1237 end if;
1238 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1239
1240 End chk_labor_contribution;
1241 --
1242 --
1243 -- ----------------------------------------------------------------------------
1244 --|---------------------< chk_health_contribution >----------------------------|
1245 -- ----------------------------------------------------------------------------
1246 -- {Start Of Comments}
1247 --
1248 -- Description:
1249 -- This procedure validates the Health Contribution values.
1250 --
1251 -- Prerequisites:
1252 --
1253 --
1254 -- In Parameters:
1255 -- p_sii_details_id
1256 -- p_effective_date
1257 -- p_health_contribution
1258 -- p_validation_start_date
1259 -- p_validation_end_date
1260 -- p_object_version_number
1261 -- p_contract_category
1262 --
1263 -- Post Success:
1264 -- Processing continues.
1265 --
1266 -- Post Failure:
1267 -- An error is raised if the validation fails.
1268 --
1269 --
1270 -- Access Status:
1271 -- Internal Development Use Only.
1272 --
1273 -- {End Of Comments}
1274 -- ----------------------------------------------------------------------------
1275 Procedure chk_health_contribution
1276 (p_sii_details_id in number
1277 ,p_effective_date in date
1278 ,p_health_contribution in varchar2
1279 ,p_validation_start_date in date
1280 ,p_validation_end_date in date
1281 ,p_object_version_number in number
1282 ,p_contract_category in varchar2
1283 ) IS
1284
1285 l_proc varchar2(72);
1286 l_api_updating boolean;
1287
1288 Begin
1289 hr_utility.set_location('Entering:'|| l_proc, 10);
1290 l_proc := g_package ||'chk_health_contribution';
1291
1292 --
1293 -- Check mandatory parameters have been set
1294 --
1295 hr_api.mandatory_arg_error
1296 (p_api_name => l_proc
1297 ,p_argument => 'effective date'
1298 ,p_argument_value => p_effective_date
1299 );
1300
1301 hr_api.mandatory_arg_error
1302 (p_api_name => l_proc
1306
1303 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','HEALTH_CONTRIBUTION')
1304 ,p_argument_value => p_health_contribution
1305 );
1307
1308 -- Only proceed with validation if:
1309 -- a) The current g_old_rec is current and
1310 -- b) Health Contribution value has changed
1311 -- c) A record is being inserted
1312 --
1313 l_api_updating := pay_psd_shd.api_updating
1314 (p_sii_details_id => p_sii_details_id
1315 ,p_effective_date => p_effective_date
1316 ,p_object_version_number => p_object_version_number);
1317
1318 if ((l_api_updating and nvl(pay_psd_shd.g_old_rec.health_contribution,
1319 hr_api.g_varchar2)
1320 <> nvl(p_health_contribution,hr_api.g_varchar2)) or
1321 (NOT l_api_updating)) then
1322
1323 --
1324 -- If Health Contribution is not null then
1325 -- Check if the Health Contribution value exists in hr_lookups
1326 -- where the lookup_type is 'PL_CONTRIBUTION_TYPE'
1327 --
1328 if p_health_contribution is not null then
1329 if hr_api.not_exists_in_dt_hr_lookups
1330 (p_effective_date => p_effective_date
1331 ,p_validation_start_date => p_validation_start_date
1332 ,p_validation_end_date => p_validation_end_date
1333 ,p_lookup_type => 'PL_CONTRIBUTION_TYPE'
1334 ,p_lookup_code => p_health_contribution
1335 ) then
1336 -- Error: Invalid Contract Category
1337 hr_utility.set_message(801,'PAY_375843_INVALID_CONTRI_TYPE');
1338 -- This message will be 'The Contribution type does not exist in the system'
1339 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','HEALTH_CONTRIBUTION'));
1340 hr_utility.raise_error;
1341 end if;
1342
1343 -- Raise an error if 'Voluntary' is specified for a 'NORMAL' or 'TERM_NORMAL' category
1347 -- This message will be 'The Contribution type does not exist in the system'
1344 if (p_contract_category in ('NORMAL','TERM_NORMAL') and p_health_contribution = 'D') then
1345 -- Error: Invalid Contract Category
1346 hr_utility.set_message(801,'PAY_375871_INVALID_NORMAL_CONT');
1348 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','HEALTH_CONTRIBUTION'));
1349 hr_utility.raise_error;
1350 end if;
1351 end if;
1352 end if;
1353 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1354 exception
1355 when app_exception.application_exception then
1356 if hr_multi_message.exception_add
1357 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.HEALTH_CONTRIBUTION'
1358 ) then
1359 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1360 raise;
1361 end if;
1362 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1363
1364 End chk_health_contribution;
1365 --
1366
1367
1368 -- ----------------------------------------------------------------------------
1369 --|------------------< chk_unemployment_contribution >-------------------------|
1370 -- ----------------------------------------------------------------------------
1371 -- {Start Of Comments}
1372 --
1373 -- Description:
1374 -- This procedure validates the Unemployment Contribution values.
1375 --
1376 -- Prerequisites:
1377 --
1378 --
1379 -- In Parameters:
1380 -- p_sii_details_id
1381 -- p_effective_date
1382 -- p_unemployment_contribution
1383 -- p_validation_start_date
1384 -- p_validation_end_date
1385 -- p_object_version_number
1386 -- p_contract_category
1387 --
1388 -- Post Success:
1389 -- Processing continues.
1390 --
1391 -- Post Failure:
1392 -- An error is raised if the validation fails.
1393 --
1394 --
1395 -- Access Status:
1396 -- Internal Development Use Only.
1397 --
1398 -- {End Of Comments}
1399 -- ----------------------------------------------------------------------------
1400 Procedure chk_unemployment_contribution
1401 (p_sii_details_id in number
1402 ,p_effective_date in date
1403 ,p_unemployment_contribution in varchar2
1404 ,p_validation_start_date in date
1405 ,p_validation_end_date in date
1406 ,p_object_version_number in number
1407 ,p_contract_category in varchar2
1408 ) IS
1409
1410 l_proc varchar2(72);
1411 l_api_updating boolean;
1412
1413 Begin
1414 hr_utility.set_location('Entering:'|| l_proc, 10);
1415 l_proc := g_package ||'chk_unemployment_contribution';
1416
1417 --
1418 -- Check mandatory parameters have been set
1419 --
1420 hr_api.mandatory_arg_error
1421 (p_api_name => l_proc
1422 ,p_argument => 'effective date'
1423 ,p_argument_value => p_effective_date
1424 );
1425
1426 hr_api.mandatory_arg_error
1427 (p_api_name => l_proc
1428 ,p_argument => hr_general.decode_lookup('PL_FORM_LABELS','UNEMPLOYMENT_CONTRIBUTION')
1429 ,p_argument_value => p_unemployment_contribution
1430 );
1431
1432
1433 -- Only proceed with validation if:
1434 -- a) The current g_old_rec is current and
1435 -- b) Unemployment Contribution value has changed
1436 -- c) A record is being inserted
1437 --
1438 l_api_updating := pay_psd_shd.api_updating
1439 (p_sii_details_id => p_sii_details_id
1440 ,p_effective_date => p_effective_date
1441 ,p_object_version_number => p_object_version_number);
1442
1443 if ((l_api_updating and nvl(pay_psd_shd.g_old_rec.unemployment_contribution,
1444 hr_api.g_varchar2)
1445 <> nvl(p_unemployment_contribution,hr_api.g_varchar2)) or
1446 (NOT l_api_updating)) then
1447
1448 --
1449 -- If Unemployment Contribution is not null then
1450 -- Check if the Unemployment Contribution value exists in hr_lookups
1451 -- where the lookup_type is 'PL_CONTRIBUTION_TYPE'
1452 --
1453 if p_unemployment_contribution is not null then
1454 if hr_api.not_exists_in_dt_hr_lookups
1455 (p_effective_date => p_effective_date
1456 ,p_validation_start_date => p_validation_start_date
1457 ,p_validation_end_date => p_validation_end_date
1458 ,p_lookup_type => 'PL_CONTRIBUTION_TYPE'
1459 ,p_lookup_code => p_unemployment_contribution
1460 ) then
1461 -- Error: Invalid Contract Category
1462 hr_utility.set_message(801,'PAY_375843_INVALID_CONTRI_TYPE');
1463 -- This message will be 'The Contribution type does not exist in the system'
1464 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','UNEMPLOYMENT_CONTRIBUTION'));
1465 hr_utility.raise_error;
1466 end if;
1467
1468 -- Raise an error if 'Voluntary' is specified for a 'NORMAL' or 'TERM_NORMAL' category
1469 if (p_contract_category in ('NORMAL','TERM_NORMAL') and p_unemployment_contribution = 'D') then
1470 -- Error: Invalid Contract Category
1471 hr_utility.set_message(801,'PAY_375871_INVALID_NORMAL_CONT');
1472 -- This message will be 'The Contribution type does not exist in the system'
1473 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','UNEMPLOYMENT_CONTRIBUTION'));
1474 hr_utility.raise_error;
1475 end if;
1476 end if;
1477 end if;
1478 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1479 exception
1480 when app_exception.application_exception then
1481 if hr_multi_message.exception_add
1485 raise;
1482 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.UNEMPLOYMENT_CONTRIBUTION'
1483 ) then
1484 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1486 end if;
1487 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1488
1489 End chk_unemployment_contribution;
1490 --
1491 --
1492 -- ----------------------------------------------------------------------------
1493 --|--------------------< chk_old_age_cont_end_reason >--------------------------|
1494 -- ----------------------------------------------------------------------------
1495 -- {Start Of Comments}
1496 --
1497 -- Description:
1498 -- This procedure validates the Old Age Contribution End Reason values.
1499 --
1500 -- Prerequisites:
1501 --
1502 --
1503 -- In Parameters:
1504 -- p_sii_details_id
1505 -- p_effective_date
1506 -- p_old_age_contribution
1507 -- p_old_age_cont_end_reason
1508 -- p_validation_start_date
1509 -- p_validation_end_date
1510 -- p_object_version_number
1511 --
1512 -- Post Success:
1513 -- Processing continues.
1514 --
1515 -- Post Failure:
1516 -- An error is raised if the validation fails.
1517 --
1518 --
1519 -- Access Status:
1520 -- Internal Development Use Only.
1521 --
1522 -- {End Of Comments}
1523 -- ----------------------------------------------------------------------------
1524 Procedure chk_old_age_cont_end_reason
1525 (p_sii_details_id in number
1526 ,p_effective_date in date
1527 ,p_old_age_contribution in varchar2
1528 ,p_old_age_cont_end_reason in varchar2
1529 ,p_validation_start_date in date
1530 ,p_validation_end_date in date
1531 ,p_object_version_number in number
1532 ) IS
1533
1534 l_proc varchar2(72);
1535 l_api_updating boolean;
1536
1537
1538
1539 Begin
1540 hr_utility.set_location('Entering:'|| l_proc, 10);
1541 l_proc := g_package ||'chk_old_age_cont_end_reason';
1542
1543 --
1544 -- Check mandatory parameters have been set
1545 --
1546 hr_api.mandatory_arg_error
1547 (p_api_name => l_proc
1548 ,p_argument => 'effective date'
1549 ,p_argument_value => p_effective_date
1550 );
1551
1552 -- Proceed with validation only if the Old Age Contribution Value is not Invalid
1553 if hr_multi_message.no_exclusive_error
1554 (p_check_column1 => 'PAY_PL_SII_DETAILS_F.OLD_AGE_CONTRIBUTION'
1555 ,p_associated_column1 => 'PAY_PL_SII_DETAILS_F.OLD_AGE_CONT_END_REASON') then
1556
1557
1558 -- Only proceed with validation if:
1559 -- a) The current g_old_rec is current and
1560 -- b) Old Age Contribution End Reason value has changed
1561
1562 --
1563 l_api_updating := pay_psd_shd.api_updating
1564 (p_sii_details_id => p_sii_details_id
1565 ,p_effective_date => p_effective_date
1566 ,p_object_version_number => p_object_version_number);
1567
1568 -- If the Contribution type has changed from Mandatory/Voluntary to No Contribution
1569 -- then the Contribution End Reason is mandatory
1570
1571 if p_old_age_contribution = 'N' and
1572 nvl(pay_psd_shd.g_old_rec.old_age_contribution,'N') <> 'N' then
1573 if p_old_age_cont_end_reason is null then
1574 hr_utility.set_message(801,'PAY_375844_CONT_END_REQD');
1575 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','OLD_AGE_CONTRIBUTION'));
1576 hr_utility.raise_error;
1577 end if;
1578 end if;
1579
1580 if (l_api_updating and nvl(pay_psd_shd.g_old_rec.old_age_cont_end_reason,
1581 hr_api.g_varchar2)
1582 <> nvl(p_old_age_cont_end_reason,hr_api.g_varchar2)) then
1583 --
1584 -- If Old Age Contribution End Reason is not null then
1585 -- Check if the Old Age Contribution End Reason value exists in hr_lookups
1586 -- where the lookup_type is 'PL_CONTRIBUTION_END_REASON'
1587 --
1588 if p_old_age_cont_end_reason is not null then
1589 if hr_api.not_exists_in_dt_hr_lookups
1590 (p_effective_date => p_effective_date
1591 ,p_validation_start_date => p_validation_start_date
1592 ,p_validation_end_date => p_validation_end_date
1593 ,p_lookup_type => 'PL_CONTRIBUTION_END_REASON'
1594 ,p_lookup_code => p_old_age_cont_end_reason
1595 ) then
1596 -- Error: Invalid Contract Category
1597 hr_utility.set_message(801,'PAY_375845_INVALID_END_REASON');
1598 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','OLD_AGE_CONTRIBUTION'));
1599 -- This message will be 'The Old Age Contribution End Reason type
1600 -- does not exist in the system'
1601 hr_utility.raise_error;
1602 end if;
1603
1604 -- If the Old Age Contribution End Reason is not null then the value of Old Age
1605 -- Contribution shud be 'No Contribution'.
1606
1607 if p_old_age_contribution <> 'N' then
1608 -- Raise an error that Old Age Contribution should be 'No Contribution'
1609 -- when Old Age Contribution End Reason has been specified
1610 hr_utility.set_message(801,'PAY_375846_CONTRI_TYPE_REQD');
1611 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','OLD_AGE_CONTRIBUTION'));
1612 hr_utility.raise_error;
1613 end if;
1614
1615
1616 -- If the value of the OLD_AGE_CONTRIBUTION has remained unchanged (and its
1617 -- older and new values are 'No Contribution') and if the value of
1618 -- OLD_AGE_CONT_END_REASON has
1619 -- been changed from NULL to a not NULL value, then an error should be raised
1620
1621 if p_old_age_contribution = 'N' and
1625 hr_utility.raise_error;
1622 pay_psd_shd.g_old_rec.old_age_contribution = 'N' then
1623 hr_utility.set_message(801,'PAY_375847_INCORREC_END_REASON');
1624 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','OLD_AGE_CONTRIBUTION'));
1626 end if;
1627
1628
1629 end if;
1630 end if;
1631 end if;
1632 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1633 exception
1634 when app_exception.application_exception then
1635 if hr_multi_message.exception_add
1636 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.OLD_AGE_CONT_END_REASON'
1637 ) then
1638 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1639 raise;
1640 end if;
1641 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1642
1643 End chk_old_age_cont_end_reason;
1644 --
1645 --
1646
1647 -- ----------------------------------------------------------------------------
1648 --|--------------------< chk_pension_cont_end_reason >--------------------------|
1649 -- ----------------------------------------------------------------------------
1650 -- {Start Of Comments}
1651 --
1652 -- Description:
1653 -- This procedure validates the Pension Contribution End Reason values.
1654 --
1655 -- Prerequisites:
1656 --
1657 --
1658 -- In Parameters:
1659 -- p_sii_details_id
1660 -- p_effective_date
1661 -- p_pension_contribution
1662 -- p_pension_cont_end_reason
1663 -- p_validation_start_date
1664 -- p_validation_end_date
1665 -- p_object_version_number
1666 --
1667 -- Post Success:
1668 -- Processing continues.
1669 --
1670 -- Post Failure:
1671 -- An error is raised if the validation fails.
1672 --
1673 --
1674 -- Access Status:
1675 -- Internal Development Use Only.
1676 --
1677 -- {End Of Comments}
1678 -- ----------------------------------------------------------------------------
1679 Procedure chk_pension_cont_end_reason
1680 (p_sii_details_id in number
1681 ,p_effective_date in date
1682 ,p_pension_contribution in varchar2
1683 ,p_pension_cont_end_reason in varchar2
1684 ,p_validation_start_date in date
1685 ,p_validation_end_date in date
1686 ,p_object_version_number in number
1687 ) IS
1688
1689 l_proc varchar2(72);
1690 l_api_updating boolean;
1691
1692
1693
1694 Begin
1695 hr_utility.set_location('Entering:'|| l_proc, 10);
1696 l_proc := g_package ||'chk_pension_cont_end_reason';
1697
1698 --
1699 -- Check mandatory parameters have been set
1700 --
1701 hr_api.mandatory_arg_error
1702 (p_api_name => l_proc
1703 ,p_argument => 'effective date'
1704 ,p_argument_value => p_effective_date
1705 );
1706
1707 -- Proceed with validation only if the Old Age Contribution Value is not Invalid
1708 if hr_multi_message.no_exclusive_error
1709 (p_check_column1 => 'PAY_PL_SII_DETAILS_F.PENSION_CONTRIBUTION'
1710 ,p_associated_column1 => 'PAY_PL_SII_DETAILS_F.OLD_PENSION_END_REASON') then
1711
1712
1713 -- Only proceed with validation if:
1714 -- a) The current g_old_rec is current and
1715 -- b) Pension Contribution End Reason value has changed
1716
1717 --
1718 l_api_updating := pay_psd_shd.api_updating
1719 (p_sii_details_id => p_sii_details_id
1720 ,p_effective_date => p_effective_date
1721 ,p_object_version_number => p_object_version_number);
1722
1723 -- If the Contribution type has changed from Mandatory/Voluntary to No Contribution
1724 -- then the Contribution End Reason is mandatory
1725
1726 if p_pension_contribution = 'N' and
1727 nvl(pay_psd_shd.g_old_rec.pension_contribution,'N') <> 'N' then
1728 if p_pension_cont_end_reason is null then
1729 hr_utility.set_message(801,'PAY_375844_CONT_END_REQD');
1730 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','PENSION_CONTRIBUTION'));
1731 hr_utility.raise_error;
1732 end if;
1733 end if;
1734
1735 if (l_api_updating and nvl(pay_psd_shd.g_old_rec.pension_cont_end_reason,
1736 hr_api.g_varchar2)
1737 <> nvl(p_pension_cont_end_reason,hr_api.g_varchar2)) then
1738 --
1739 -- If Pension Contribution End Reason is not null then
1740 -- Check if the Pension Contribution End Reason value exists in hr_lookups
1741 -- where the lookup_type is 'PL_CONTRIBUTION_END_REASON'
1742 --
1743 if p_pension_cont_end_reason is not null then
1744 if hr_api.not_exists_in_dt_hr_lookups
1745 (p_effective_date => p_effective_date
1746 ,p_validation_start_date => p_validation_start_date
1747 ,p_validation_end_date => p_validation_end_date
1748 ,p_lookup_type => 'PL_CONTRIBUTION_END_REASON'
1749 ,p_lookup_code => p_pension_cont_end_reason
1750 ) then
1751 -- Error: Invalid Contract Category
1752 hr_utility.set_message(801,'PAY_375845_INVALID_END_REASON');
1753 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','PENSION_CONTRIBUTION'));
1754 -- This message will be 'The Pension Contribution End Reason type
1755 -- does not exist in the system'
1756 hr_utility.raise_error;
1757 end if;
1758
1759 -- If the Pension Contribution End Reason is not null then the value of Pension
1760 -- Contribution shud be 'No Contribution'.
1761
1762 if p_pension_contribution <> 'N' then
1763 -- Raise an error that Pension Contribution should be 'No Contribution'
1764 -- when Pension Contribution End Reason has been specified
1768 end if;
1765 hr_utility.set_message(801,'PAY_375846_CONTRI_TYPE_REQD');
1766 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','PENSION_CONTRIBUTION'));
1767 hr_utility.raise_error;
1769
1770
1771 -- If the value of the PENSION_CONTRIBUTION has remained unchanged (and its
1772 -- older and new values are 'No Contribution') and if the value of
1773 -- PENSION_CONT_END_REASON has
1774 -- been changed from NULL to a not NULL value, then an error should be raised
1775
1776 if p_pension_contribution = 'N' and
1777 pay_psd_shd.g_old_rec.pension_contribution = 'N' then
1778 hr_utility.set_message(801,'PAY_375847_INCORREC_END_REASON');
1779 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','PENSION_CONTRIBUTION'));
1780 end if;
1781
1782
1783 end if;
1784 end if;
1785 end if;
1786 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1787 exception
1788 when app_exception.application_exception then
1789 if hr_multi_message.exception_add
1790 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.PENSION_CONT_END_REASON'
1791 ) then
1792 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1793 raise;
1794 end if;
1795 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1796
1797 End chk_pension_cont_end_reason;
1798 --
1799 --
1800 -- ----------------------------------------------------------------------------
1801 --|-------------------< chk_sickness_cont_end_reason >-------------------------|
1802 -- ----------------------------------------------------------------------------
1803 -- {Start Of Comments}
1804 --
1805 -- Description:
1806 -- This procedure validates the Sickness Contribution End Reason values.
1807 --
1808 -- Prerequisites:
1809 --
1810 --
1811 -- In Parameters:
1812 -- p_sii_details_id
1813 -- p_effective_date
1814 -- p_sickness_contribution
1815 -- p_sickness_cont_end_reason
1816 -- p_validation_start_date
1817 -- p_validation_end_date
1818 -- p_object_version_number
1819 --
1820 -- Post Success:
1821 -- Processing continues.
1822 --
1823 -- Post Failure:
1824 -- An error is raised if the validation fails.
1825 --
1826 --
1827 -- Access Status:
1828 -- Internal Development Use Only.
1829 --
1830 -- {End Of Comments}
1831 -- ----------------------------------------------------------------------------
1832 Procedure chk_sickness_cont_end_reason
1833 (p_sii_details_id in number
1834 ,p_effective_date in date
1835 ,p_sickness_contribution in varchar2
1836 ,p_sickness_cont_end_reason in varchar2
1837 ,p_validation_start_date in date
1838 ,p_validation_end_date in date
1839 ,p_object_version_number in number
1840 ) IS
1841
1842 l_proc varchar2(72);
1843 l_api_updating boolean;
1844
1845
1846
1847 Begin
1848 hr_utility.set_location('Entering:'|| l_proc, 10);
1849 l_proc := g_package ||'chk_sickness_cont_end_reason';
1850
1851 --
1852 -- Check mandatory parameters have been set
1853 --
1854 hr_api.mandatory_arg_error
1855 (p_api_name => l_proc
1856 ,p_argument => 'effective date'
1857 ,p_argument_value => p_effective_date
1858 );
1859
1860 -- Proceed with validation only if the Sickness Contribution Value is not Invalid
1861 if hr_multi_message.no_exclusive_error
1862 (p_check_column1 => 'PAY_PL_SII_DETAILS_F.SICKNESS_CONTRIBUTION'
1863 ,p_associated_column1 => 'PAY_PL_SII_DETAILS_F.SICKNESS_CONT_END_REASON') then
1864
1865
1866 -- Only proceed with validation if:
1867 -- a) The current g_old_rec is current and
1868 -- b) Sickness Contribution End Reason value has changed
1869
1870 --
1871 l_api_updating := pay_psd_shd.api_updating
1872 (p_sii_details_id => p_sii_details_id
1873 ,p_effective_date => p_effective_date
1874 ,p_object_version_number => p_object_version_number);
1875
1876 -- If the Contribution type has changed from Mandatory/Voluntary to No Contribution
1877 -- then the Contribution End Reason is mandatory
1878
1879 if p_sickness_contribution = 'N' and
1880 nvl(pay_psd_shd.g_old_rec.sickness_contribution,'N') <> 'N' then
1881 if p_sickness_cont_end_reason is null then
1882 hr_utility.set_message(801,'PAY_375844_CONT_END_REQD');
1883 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','SICKNESS_CONTRIBUTION'));
1884 hr_utility.raise_error;
1885 end if;
1886 end if;
1887
1888 if (l_api_updating and nvl(pay_psd_shd.g_old_rec.sickness_cont_end_reason,
1889 hr_api.g_varchar2)
1890 <> nvl(p_sickness_cont_end_reason,hr_api.g_varchar2)) then
1891 --
1892 -- If Sickness Contribution End Reason is not null then
1893 -- Check if the Sickness Contribution End Reason value exists in hr_lookups
1894 -- where the lookup_type is 'PL_CONTRIBUTION_END_REASON'
1895 --
1896 if p_sickness_cont_end_reason is not null then
1897 if hr_api.not_exists_in_dt_hr_lookups
1898 (p_effective_date => p_effective_date
1899 ,p_validation_start_date => p_validation_start_date
1900 ,p_validation_end_date => p_validation_end_date
1901 ,p_lookup_type => 'PL_CONTRIBUTION_END_REASON'
1902 ,p_lookup_code => p_sickness_cont_end_reason
1903 ) then
1904 -- Error: Invalid Contract Category
1905 hr_utility.set_message(801,'PAY_375845_INVALID_END_REASON');
1906 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','SICKNESS_CONTRIBUTION'));
1910 end if;
1907 -- This message will be 'The Sickness Contribution End Reason type
1908 -- does not exist in the system'
1909 hr_utility.raise_error;
1911
1912 -- If the Sickness Contribution End Reason is not null then the value of Sickness
1913 -- Contribution shud be 'No Contribution'.
1914
1915 if p_sickness_contribution <> 'N' then
1916 -- Raise an error that Sickness Contribution should be 'No Contribution'
1917 -- when Sickness Contribution End Reason has been specified
1918 hr_utility.set_message(801,'PAY_375846_CONTRI_TYPE_REQD');
1919 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','SICKNESS_CONTRIBUTION'));
1920 hr_utility.raise_error;
1921 end if;
1922
1923
1924 -- If the value of the SICKNESS_CONTRIBUTION has remained unchanged (and its
1925 -- older and new values are 'No Contribution') and if the value of
1926 -- SICKNESS_CONT_END_REASON has
1927 -- been changed from NULL to a not NULL value, then an error should be raised
1928
1929 if p_sickness_contribution = 'N' and
1930 pay_psd_shd.g_old_rec.sickness_contribution = 'N' then
1931 hr_utility.set_message(801,'PAY_375847_INCORREC_END_REASON');
1932 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','SICKNESS_CONTRIBUTION'));
1933 end if;
1934
1935
1936 end if;
1937 end if;
1938 end if;
1939 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1940 exception
1941 when app_exception.application_exception then
1942 if hr_multi_message.exception_add
1943 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.SICKNESS_CONT_END_REASON'
1944 ) then
1945 hr_utility.set_location(' Leaving:'|| l_proc, 30);
1946 raise;
1947 end if;
1948 hr_utility.set_location(' Leaving:'|| l_proc, 40);
1949
1950 End chk_sickness_cont_end_reason;
1951 --
1952 --
1953 -- ----------------------------------------------------------------------------
1954 --|--------------------< chk_work_injury_cont_end >---------------------------|
1955 -- ----------------------------------------------------------------------------
1956 -- {Start Of Comments}
1957 --
1958 -- Description:
1959 -- This procedure validates the Work Injury Contribution End Reason values.
1960 --
1961 -- Prerequisites:
1962 --
1963 --
1964 -- In Parameters:
1965 -- p_sii_details_id
1966 -- p_effective_date
1967 -- p_work_injury_contribution
1968 -- p_work_injury_cont_end_reason
1969 -- p_validation_start_date
1970 -- p_validation_end_date
1971 -- p_object_version_number
1972 --
1973 -- Post Success:
1974 -- Processing continues.
1975 --
1976 -- Post Failure:
1977 -- An error is raised if the validation fails.
1978 --
1979 --
1980 -- Access Status:
1981 -- Internal Development Use Only.
1982 --
1983 -- {End Of Comments}
1984 -- ----------------------------------------------------------------------------
1985 Procedure chk_work_injury_cont_end
1986 (p_sii_details_id in number
1987 ,p_effective_date in date
1988 ,p_work_injury_contribution in varchar2
1989 ,p_work_injury_cont_end_reason in varchar2
1990 ,p_validation_start_date in date
1991 ,p_validation_end_date in date
1992 ,p_object_version_number in number
1993 ) IS
1994
1995 l_proc varchar2(72);
1996 l_api_updating boolean;
1997
1998
1999
2000 Begin
2001 hr_utility.set_location('Entering:'|| l_proc, 10);
2002 l_proc := g_package ||'chk_work_injury_cont_end';
2003
2004 --
2005 -- Check mandatory parameters have been set
2006 --
2007 hr_api.mandatory_arg_error
2008 (p_api_name => l_proc
2009 ,p_argument => 'effective date'
2010 ,p_argument_value => p_effective_date
2011 );
2012
2013 -- Proceed with validation only if the Work Injury Contribution Value is not Invalid
2014 if hr_multi_message.no_exclusive_error
2015 (p_check_column1 => 'PAY_PL_SII_DETAILS_F.WORK_INJURY_CONTRIBUTION'
2016 ,p_associated_column1 => 'PAY_PL_SII_DETAILS_F.WORK_INJURY_CONT_END_REASON') then
2017
2018
2019 -- Only proceed with validation if:
2020 -- a) The current g_old_rec is current and
2021 -- b) Work Injury Contribution End Reason value has changed
2022
2023 --
2024 l_api_updating := pay_psd_shd.api_updating
2025 (p_sii_details_id => p_sii_details_id
2026 ,p_effective_date => p_effective_date
2027 ,p_object_version_number => p_object_version_number);
2028
2029 -- If the Contribution type has changed from Mandatory/Voluntary to No Contribution
2030 -- then the Contribution End Reason is mandatory
2031
2032 if p_work_injury_contribution = 'N' and
2033 nvl(pay_psd_shd.g_old_rec.work_injury_contribution,'N') <> 'N' then
2034 if p_work_injury_cont_end_reason is null then
2035 hr_utility.set_message(801,'PAY_375844_CONT_END_REQD');
2036 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','WORK_INJURY_CONTRIBUTION'));
2037 hr_utility.raise_error;
2038 end if;
2039 end if;
2040
2041 if (l_api_updating and nvl(pay_psd_shd.g_old_rec.work_injury_cont_end_reason,
2042 hr_api.g_varchar2)
2043 <> nvl(p_work_injury_cont_end_reason,hr_api.g_varchar2)) then
2044 --
2045 -- If Work Injury Contribution End Reason is not null then
2046 -- Check if the Work Injury Contribution End Reason value exists in hr_lookups
2047 -- where the lookup_type is 'PL_CONTRIBUTION_END_REASON'
2048 --
2049 if p_work_injury_cont_end_reason is not null then
2050 if hr_api.not_exists_in_dt_hr_lookups
2054 ,p_lookup_type => 'PL_CONTRIBUTION_END_REASON'
2051 (p_effective_date => p_effective_date
2052 ,p_validation_start_date => p_validation_start_date
2053 ,p_validation_end_date => p_validation_end_date
2055 ,p_lookup_code => p_work_injury_cont_end_reason
2056 ) then
2057 -- Error: Invalid Contract Category
2058 hr_utility.set_message(801,'PAY_375845_INVALID_END_REASON');
2059 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','WORK_INJURY_CONTRIBUTION'));
2060 -- This message will be 'The Work Injury Contribution End Reason type
2061 -- does not exist in the system'
2062 hr_utility.raise_error;
2063 end if;
2064
2065 -- If the Work Injury Contribution End Reason is not null then the value of Work Injury
2066 -- Contribution shud be 'No Contribution'.
2067
2068 if p_work_injury_contribution <> 'N' then
2069 -- Raise an error that Work Injury Contribution should be 'No Contribution'
2070 -- when Work Injury Contribution End Reason has been specified
2071 hr_utility.set_message(801,'PAY_375846_CONTRI_TYPE_REQD');
2072 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','WORK_INJURY_CONTRIBUTION'));
2073 hr_utility.raise_error;
2074 end if;
2075
2076
2077 -- If the value of the WORK_INJURY_CONTRIBUTION has remained unchanged (and its
2078 -- older and new values are 'No Contribution') and if the value of
2079 -- WORK_INJURY_CONT_END_REASON has
2080 -- been changed from NULL to a not NULL value, then an error should be raised
2081
2082 if p_work_injury_contribution = 'N' and
2083 pay_psd_shd.g_old_rec.work_injury_contribution = 'N' then
2084 hr_utility.set_message(801,'PAY_375847_INCORREC_END_REASON');
2085 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','WORK_INJURY_CONTRIBUTION'));
2086 end if;
2087
2088
2089 end if;
2090 end if;
2091 end if;
2092 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2093 exception
2094 when app_exception.application_exception then
2095 if hr_multi_message.exception_add
2096 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.WORK_INJURY_CONT_END_REASON'
2097 ) then
2098 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2099 raise;
2100 end if;
2101 hr_utility.set_location(' Leaving:'|| l_proc, 40);
2102
2103 End chk_work_injury_cont_end;
2104 --
2105 --
2106 -- ----------------------------------------------------------------------------
2107 --|------------------< chk_labor_fund_cont_end_reason >------------------------|
2108 -- ----------------------------------------------------------------------------
2109 -- {Start Of Comments}
2110 --
2111 -- Description:
2112 -- This procedure validates the Labor fund Contribution End Reason values.
2113 --
2114 -- Prerequisites:
2115 --
2116 --
2117 -- In Parameters:
2118 -- p_sii_details_id
2119 -- p_effective_date
2120 -- p_labor_contribution
2121 -- p_labor_fund_cont_end_reason
2122 -- p_validation_start_date
2123 -- p_validation_end_date
2124 -- p_object_version_number
2125 --
2126 -- Post Success:
2127 -- Processing continues.
2128 --
2129 -- Post Failure:
2130 -- An error is raised if the validation fails.
2131 --
2132 --
2133 -- Access Status:
2134 -- Internal Development Use Only.
2135 --
2136 -- {End Of Comments}
2137 -- ----------------------------------------------------------------------------
2138 Procedure chk_labor_fund_cont_end_reason
2139 (p_sii_details_id in number
2140 ,p_effective_date in date
2141 ,p_labor_contribution in varchar2
2142 ,p_labor_fund_cont_end_reason in varchar2
2143 ,p_validation_start_date in date
2144 ,p_validation_end_date in date
2145 ,p_object_version_number in number
2146 ) IS
2147
2148 l_proc varchar2(72);
2149 l_api_updating boolean;
2150
2151
2152
2153 Begin
2154 hr_utility.set_location('Entering:'|| l_proc, 10);
2155 l_proc := g_package ||'chk_labor_fund_cont_end_reason';
2156
2157 --
2158 -- Check mandatory parameters have been set
2159 --
2160 hr_api.mandatory_arg_error
2161 (p_api_name => l_proc
2162 ,p_argument => 'effective date'
2163 ,p_argument_value => p_effective_date
2164 );
2165
2166 -- Proceed with validation only if the Labor Contribution Value is not Invalid
2167 if hr_multi_message.no_exclusive_error
2168 (p_check_column1 => 'PAY_PL_SII_DETAILS_F.LABOR_CONTRIBUTION'
2169 ,p_associated_column1 => 'PAY_PL_SII_DETAILS_F.LABOR_FUND_CONT_END_REASON') then
2170
2171
2172 -- Only proceed with validation if:
2173 -- a) The current g_old_rec is current and
2174 -- b) Labor Contribution End Reason value has changed
2175
2176 --
2177 l_api_updating := pay_psd_shd.api_updating
2178 (p_sii_details_id => p_sii_details_id
2179 ,p_effective_date => p_effective_date
2180 ,p_object_version_number => p_object_version_number);
2181
2182 -- If the Contribution type has changed from Mandatory/Voluntary to No Contribution
2183 -- then the Contribution End Reason is mandatory
2184
2185 if p_labor_contribution = 'N' and
2186 nvl(pay_psd_shd.g_old_rec.labor_contribution,'N') <> 'N' then
2187 if p_labor_fund_cont_end_reason is null then
2188 hr_utility.set_message(801,'PAY_375844_CONT_END_REQD');
2189 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','LABOR_CONTRIBUTION'));
2190 hr_utility.raise_error;
2191 end if;
2195 hr_api.g_varchar2)
2192 end if;
2193
2194 if (l_api_updating and nvl(pay_psd_shd.g_old_rec.labor_fund_cont_end_reason,
2196 <> nvl(p_labor_fund_cont_end_reason,hr_api.g_varchar2)) then
2197 --
2198 -- If Labor Fund Contribution End Reason is not null then
2199 -- Check if the Labor fund Contribution End Reason value exists in hr_lookups
2200 -- where the lookup_type is 'PL_CONTRIBUTION_END_REASON'
2201 --
2202 if p_labor_fund_cont_end_reason is not null then
2203 if hr_api.not_exists_in_dt_hr_lookups
2204 (p_effective_date => p_effective_date
2205 ,p_validation_start_date => p_validation_start_date
2206 ,p_validation_end_date => p_validation_end_date
2207 ,p_lookup_type => 'PL_CONTRIBUTION_END_REASON'
2208 ,p_lookup_code => p_labor_fund_cont_end_reason
2209 ) then
2210 -- Error: Invalid Contract Category
2211 hr_utility.set_message(801,'PAY_375845_INVALID_END_REASON');
2212 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','LABOR_CONTRIBUTION'));
2213 -- This message will be 'The Labor fund Contribution End Reason type
2214 -- does not exist in the system'
2215 hr_utility.raise_error;
2216 end if;
2217
2218 -- If the Labor fund Contribution End Reason is not null then the value of Labor fund
2219 -- Contribution shud be 'No Contribution'.
2220
2221 if p_labor_contribution <> 'N' then
2222 -- Raise an error that Labor Contribution should be 'No Contribution'
2223 -- when Labor Contribution End Reason has been specified
2224 hr_utility.set_message(801,'PAY_375846_CONTRI_TYPE_REQD');
2225 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','LABOR_CONTRIBUTION'));
2226 hr_utility.raise_error;
2227 end if;
2228
2229
2230 -- If the value of the LABOR_FUND_CONTRIBUTION has remained unchanged (and its
2231 -- older and new values are 'No Contribution') and if the value of
2232 -- LABOR_FUND_CONT_END_REASON has
2233 -- been changed from NULL to a not NULL value, then an error should be raised
2234
2235 if p_labor_contribution = 'N' and
2236 pay_psd_shd.g_old_rec.labor_contribution = 'N' then
2237 hr_utility.set_message(801,'PAY_375847_INCORREC_END_REASON');
2238 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','LABOR_CONTRIBUTION'));
2239 end if;
2240
2241
2242 end if;
2243 end if;
2244 end if;
2245 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2246 exception
2247 when app_exception.application_exception then
2248 if hr_multi_message.exception_add
2249 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.LABOR_FUND_CONT_END_REASON'
2250 ) then
2251 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2252 raise;
2253 end if;
2254 hr_utility.set_location(' Leaving:'|| l_proc, 40);
2255
2256 End chk_labor_fund_cont_end_reason;
2257 --
2258 --
2259 -- ----------------------------------------------------------------------------
2260 --|--------------------< chk_health_cont_end_reason >--------------------------|
2261 -- ----------------------------------------------------------------------------
2262 -- {Start Of Comments}
2263 --
2264 -- Description:
2265 -- This procedure validates the Health Contribution End Reason values.
2266 --
2267 -- Prerequisites:
2268 --
2269 --
2270 -- In Parameters:
2271 -- p_sii_details_id
2272 -- p_effective_date
2273 -- p_health_contribution
2274 -- p_health_cont_end_reason
2275 -- p_validation_start_date
2276 -- p_validation_end_date
2277 -- p_object_version_number
2278 --
2279 -- Post Success:
2280 -- Processing continues.
2281 --
2282 -- Post Failure:
2283 -- An error is raised if the validation fails.
2284 --
2285 --
2286 -- Access Status:
2287 -- Internal Development Use Only.
2288 --
2289 -- {End Of Comments}
2290 -- ----------------------------------------------------------------------------
2291 Procedure chk_health_cont_end_reason
2292 (p_sii_details_id in number
2293 ,p_effective_date in date
2294 ,p_health_contribution in varchar2
2295 ,p_health_cont_end_reason in varchar2
2296 ,p_validation_start_date in date
2297 ,p_validation_end_date in date
2298 ,p_object_version_number in number
2299 ) IS
2300
2301 l_proc varchar2(72);
2302 l_api_updating boolean;
2303
2304
2305
2306 Begin
2307 hr_utility.set_location('Entering:'|| l_proc, 10);
2308 l_proc := g_package ||'chk_health_cont_end_reason';
2309
2310 --
2311 -- Check mandatory parameters have been set
2312 --
2313 hr_api.mandatory_arg_error
2314 (p_api_name => l_proc
2315 ,p_argument => 'effective date'
2316 ,p_argument_value => p_effective_date
2317 );
2318
2319 -- Proceed with validation only if the Health Contribution Value is not Invalid
2320 if hr_multi_message.no_exclusive_error
2321 (p_check_column1 => 'PAY_PL_SII_DETAILS_F.HEALTH_CONTRIBUTION'
2322 ,p_associated_column1 => 'PAY_PL_SII_DETAILS_F.HEALTH_CONT_END_REASON') then
2323
2324
2325 -- Only proceed with validation if:
2326 -- a) The current g_old_rec is current and
2327 -- b) Health Contribution End Reason value has changed
2328
2329 --
2330 l_api_updating := pay_psd_shd.api_updating
2331 (p_sii_details_id => p_sii_details_id
2332 ,p_effective_date => p_effective_date
2333 ,p_object_version_number => p_object_version_number);
2334
2338 if p_health_contribution = 'N' and
2335 -- If the Contribution type has changed from Mandatory/Voluntary to No Contribution
2336 -- then the Contribution End Reason is mandatory
2337
2339 nvl(pay_psd_shd.g_old_rec.health_contribution,'N') <> 'N' then
2340 if p_health_cont_end_reason is null then
2341 hr_utility.set_message(801,'PAY_375844_CONT_END_REQD');
2342 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','HEALTH_CONTRIBUTION'));
2343 hr_utility.raise_error;
2344 end if;
2345 end if;
2346
2347 if (l_api_updating and nvl(pay_psd_shd.g_old_rec.health_cont_end_reason,
2348 hr_api.g_varchar2)
2349 <> nvl(p_health_cont_end_reason,hr_api.g_varchar2)) then
2350 --
2351 -- If Health Contribution End Reason is not null then
2352 -- Check if the Health Contribution End Reason value exists in hr_lookups
2353 -- where the lookup_type is 'PL_CONTRIBUTION_END_REASON'
2354 --
2355 if p_health_cont_end_reason is not null then
2356 if hr_api.not_exists_in_dt_hr_lookups
2357 (p_effective_date => p_effective_date
2358 ,p_validation_start_date => p_validation_start_date
2359 ,p_validation_end_date => p_validation_end_date
2360 ,p_lookup_type => 'PL_CONTRIBUTION_END_REASON'
2361 ,p_lookup_code => p_health_cont_end_reason
2362 ) then
2363 -- Error: Invalid Contract Category
2364 hr_utility.set_message(801,'PAY_375845_INVALID_END_REASON');
2365 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','HEALTH_CONTRIBUTION'));
2366 -- This message will be 'The Health Contribution End Reason type
2367 -- does not exist in the system'
2368 hr_utility.raise_error;
2369 end if;
2370
2371 -- If the Health Contribution End Reason is not null then the value of Health
2372 -- Contribution shud be 'No Contribution'.
2373
2374 if p_health_contribution <> 'N' then
2375 -- Raise an error that Health Contribution should be 'No Contribution'
2376 -- when Health Contribution End Reason has been specified
2377 hr_utility.set_message(801,'PAY_375846_CONTRI_TYPE_REQD');
2378 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','HEALTH_CONTRIBUTION'));
2379 hr_utility.raise_error;
2380 end if;
2381
2382
2383 -- If the value of the HEALTH_CONTRIBUTION has remained unchanged (and its
2384 -- older and new values are 'No Contribution') and if the value of
2385 -- HEALTH_CONT_END_REASON has
2386 -- been changed from NULL to a not NULL value, then an error should be raised
2387
2388 if p_health_contribution = 'N' and
2389 pay_psd_shd.g_old_rec.health_contribution = 'N' then
2390 hr_utility.set_message(801,'PAY_375847_INCORREC_END_REASON');
2391 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','HEALTH_CONTRIBUTION'));
2392 end if;
2393
2394
2395 end if;
2396 end if;
2397 end if;
2398 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2399 exception
2400 when app_exception.application_exception then
2401 if hr_multi_message.exception_add
2402 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.HEALTH_CONT_END_REASON'
2403 ) then
2404 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2405 raise;
2406 end if;
2407 hr_utility.set_location(' Leaving:'|| l_proc, 40);
2408
2409 End chk_health_cont_end_reason;
2410 --
2411 --
2412 -- ----------------------------------------------------------------------------
2413 --|----------------------< chk_unemployment_cont_end >-------------------------|
2414 -- ----------------------------------------------------------------------------
2415 -- {Start Of Comments}
2416 --
2417 -- Description:
2418 -- This procedure validates the Unemployment Contribution End Reason values.
2419 --
2420 -- Prerequisites:
2421 --
2422 --
2423 -- In Parameters:
2424 -- p_sii_details_id
2425 -- p_effective_date
2426 -- p_unemployment_contribution
2427 -- p_unemployment_cont_end_reason
2428 -- p_validation_start_date
2429 -- p_validation_end_date
2430 -- p_object_version_number
2431 --
2432 -- Post Success:
2433 -- Processing continues.
2434 --
2435 -- Post Failure:
2436 -- An error is raised if the validation fails.
2437 --
2438 --
2439 -- Access Status:
2440 -- Internal Development Use Only.
2441 --
2442 -- {End Of Comments}
2443 -- ----------------------------------------------------------------------------
2444 Procedure chk_unemployment_cont_end
2445 (p_sii_details_id in number
2446 ,p_effective_date in date
2447 ,p_unemployment_contribution in varchar2
2448 ,p_unemployment_cont_end_reason in varchar2
2449 ,p_validation_start_date in date
2450 ,p_validation_end_date in date
2451 ,p_object_version_number in number
2452 ) IS
2453
2454 l_proc varchar2(72);
2455 l_api_updating boolean;
2456
2457
2458
2459 Begin
2460 hr_utility.set_location('Entering:'|| l_proc, 10);
2461 l_proc := g_package ||'chk_unemployment_cont_end';
2462
2463 --
2464 -- Check mandatory parameters have been set
2465 --
2466 hr_api.mandatory_arg_error
2467 (p_api_name => l_proc
2468 ,p_argument => 'effective date'
2469 ,p_argument_value => p_effective_date
2470 );
2471
2472 -- Proceed with validation only if the Old Age Contribution Value is not Invalid
2473 if hr_multi_message.no_exclusive_error
2474 (p_check_column1 => 'PAY_PL_SII_DETAILS_F.UNEMPLOYMENT_CONTRIBUTION'
2478 -- Only proceed with validation if:
2475 ,p_associated_column1 => 'PAY_PL_SII_DETAILS_F.UNEMPLOYMENT_CONT_END_REASON') then
2476
2477
2479 -- a) The current g_old_rec is current and
2480 -- b) Unemployment Contribution End Reason value has changed
2481
2482 --
2483 l_api_updating := pay_psd_shd.api_updating
2484 (p_sii_details_id => p_sii_details_id
2485 ,p_effective_date => p_effective_date
2486 ,p_object_version_number => p_object_version_number);
2487
2488 -- If the Contribution type has changed from Mandatory/Voluntary to No Contribution
2489 -- then the Contribution End Reason is mandatory
2490
2491 if p_unemployment_contribution = 'N' and
2492 nvl(pay_psd_shd.g_old_rec.unemployment_contribution,'N') <> 'N' then
2493 if p_unemployment_cont_end_reason is null then
2494 hr_utility.set_message(801,'PAY_375844_CONT_END_REQD');
2495 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','UNEMPLOYMENT_CONTRIBUTION'));
2496 hr_utility.raise_error;
2497 end if;
2498 end if;
2499
2500 if (l_api_updating and nvl(pay_psd_shd.g_old_rec.unemployment_cont_end_reason,
2501 hr_api.g_varchar2)
2502 <> nvl(p_unemployment_cont_end_reason,hr_api.g_varchar2)) then
2503 --
2504 -- If Unemployment Contribution End Reason is not null then
2505 -- Check if the Unemployment Contribution End Reason value exists in hr_lookups
2506 -- where the lookup_type is 'PL_CONTRIBUTION_END_REASON'
2507 --
2508 if p_unemployment_cont_end_reason is not null then
2509 if hr_api.not_exists_in_dt_hr_lookups
2510 (p_effective_date => p_effective_date
2511 ,p_validation_start_date => p_validation_start_date
2512 ,p_validation_end_date => p_validation_end_date
2513 ,p_lookup_type => 'PL_CONTRIBUTION_END_REASON'
2514 ,p_lookup_code => p_unemployment_cont_end_reason
2515 ) then
2516 -- Error: Invalid Contract Category
2517 hr_utility.set_message(801,'PAY_375845_INVALID_END_REASON');
2518 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','UNEMPLOYMENT_CONTRIBUTION'));
2519 -- This message will be 'The Unemployment Contribution End Reason type
2520 -- does not exist in the system'
2521 hr_utility.raise_error;
2522 end if;
2523
2524 -- If the Unemployment Contribution End Reason is not null then the value of Unemployment
2525 -- Contribution shud be 'No Contribution'.
2526
2527 if p_unemployment_contribution <> 'N' then
2528 -- Raise an error that Unemployment Contribution should be 'No Contribution'
2529 -- when Unemployment Contribution End Reason has been specified
2530 hr_utility.set_message(801,'PAY_375846_CONTRI_TYPE_REQD');
2531 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','UNEMPLOYMENT_CONTRIBUTION'));
2532 hr_utility.raise_error;
2533 end if;
2534
2535
2536 -- If the value of the UNEMPLOYMENT_CONTRIBUTION has remained unchanged (and its
2537 -- older and new values are 'No Contribution') and if the value of
2538 -- UNEMPLOYMENT_CONT_END_REASON has
2539 -- been changed from NULL to a not NULL value, then an error should be raised
2540
2541 if p_unemployment_contribution = 'N' and
2542 pay_psd_shd.g_old_rec.unemployment_contribution = 'N' then
2543 hr_utility.set_message(801,'PAY_375847_INCORREC_END_REASON');
2544 hr_utility.set_message_token('TYPE',hr_general.decode_lookup('PL_FORM_LABELS','UNEMPLOYMENT_CONTRIBUTION'));
2545 end if;
2546
2547
2548 end if;
2549 end if;
2550 end if;
2551 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2552 exception
2553 when app_exception.application_exception then
2554 if hr_multi_message.exception_add
2555 (p_associated_column1 => 'PAY_PL_SII_DETAILS_F.UNEMPLOYMENT_CONT_END_REASON'
2556 ) then
2557 hr_utility.set_location(' Leaving:'|| l_proc, 30);
2558 raise;
2559 end if;
2560 hr_utility.set_location(' Leaving:'|| l_proc, 40);
2561
2562 End chk_unemployment_cont_end;
2563 --
2564 --
2565 -- ----------------------------------------------------------------------------
2566 -- |-----------------------< chk_non_updateable_args >------------------------|
2567 -- ----------------------------------------------------------------------------
2568 -- {Start Of Comments}
2569 --
2570 -- Description:
2571 -- This procedure is used to ensure that non updateable attributes have
2572 -- not been updated. If an attribute has been updated an error is generated.
2573 --
2574 -- Pre Conditions:
2575 -- g_old_rec has been populated with details of the values currently in
2576 -- the database.
2577 --
2578 -- In Arguments:
2579 -- p_rec has been populated with the updated values the user would like the
2580 -- record set to.
2581 --
2582 -- Post Success:
2583 -- Processing continues if all the non updateable attributes have not
2584 -- changed.
2585 --
2586 -- Post Failure:
2587 -- An application error is raised if any of the non updatable attributes
2588 -- have been altered.
2589 --
2590 -- {End Of Comments}
2591 -- ----------------------------------------------------------------------------
2592 Procedure chk_non_updateable_args
2593 (p_effective_date in date
2594 ,p_rec in pay_psd_shd.g_rec_type
2595 ) IS
2596 --
2597 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
2598 --
2599 Begin
2600 --
2601 -- Only proceed with the validation if a row exists for the current
2602 -- record in the HR Schema.
2603 --
2604 IF NOT pay_psd_shd.api_updating
2608 ) THEN
2605 (p_sii_details_id => p_rec.sii_details_id
2606 ,p_effective_date => p_effective_date
2607 ,p_object_version_number => p_rec.object_version_number
2609 fnd_message.set_name('PER', 'HR_6153_ALL_PROCEDURE_FAIL');
2610 fnd_message.set_token('PROCEDURE ', l_proc);
2611 fnd_message.set_token('STEP ', '5');
2612 fnd_message.raise_error;
2613 END IF;
2614 --
2615
2616 if nvl(p_rec.business_group_id, hr_api.g_number) <>
2617 nvl(pay_psd_shd.g_old_rec.business_group_id
2618 ,hr_api.g_number
2619 ) then
2620 hr_api.argument_changed_error
2621 (p_api_name => l_proc
2622 ,p_argument => 'BUSINESS_GROUP_ID'
2623 ,p_base_table => pay_psd_shd.g_tab_nam
2624 );
2625 end if;
2626
2627 if nvl(p_rec.contract_category, hr_api.g_varchar2) <>
2628 nvl(pay_psd_shd.g_old_rec.contract_category
2629 ,hr_api.g_varchar2
2630 ) then
2631 hr_api.argument_changed_error
2632 (p_api_name => l_proc
2633 ,p_argument => 'CONTRACT_CATEGORY'
2634 ,p_base_table => pay_psd_shd.g_tab_nam
2635 );
2636 end if;
2637
2638 if nvl(p_rec.per_or_asg_id, hr_api.g_number) <>
2639 nvl(pay_psd_shd.g_old_rec.per_or_asg_id
2640 ,hr_api.g_number
2641 ) then
2642 hr_api.argument_changed_error
2643 (p_api_name => l_proc
2644 ,p_argument => 'PER_OR_ASG_ID'
2645 ,p_base_table => pay_psd_shd.g_tab_nam
2646 );
2647 end if;
2648
2649 --
2650 End chk_non_updateable_args;
2651 --
2652 -- ----------------------------------------------------------------------------
2653 -- |--------------------------< dt_update_validate >--------------------------|
2654 -- ----------------------------------------------------------------------------
2655 -- {Start Of Comments}
2656 --
2657 -- Description:
2658 -- This procedure is used for referential integrity of datetracked
2659 -- parent entities when a datetrack update operation is taking place
2660 -- and where there is no cascading of update defined for this entity.
2661 --
2662 -- Prerequisites:
2663 -- This procedure is called from the update_validate.
2664 --
2665 -- In Parameters:
2666 --
2667 -- Post Success:
2668 -- Processing continues.
2669 --
2670 -- Post Failure:
2671 --
2672 -- Developer Implementation Notes:
2673 -- This procedure should not need maintenance unless the HR Schema model
2674 -- changes.
2675 --
2676 -- Access Status:
2677 -- Internal Row Handler Use Only.
2678 --
2679 -- {End Of Comments}
2680 -- ----------------------------------------------------------------------------
2681 Procedure dt_update_validate
2682 (p_datetrack_mode in varchar2
2683 ,p_validation_start_date in date
2684 ,p_validation_end_date in date
2685 ) Is
2686 --
2687 l_proc varchar2(72) := g_package||'dt_update_validate';
2688 --
2689 Begin
2690 --
2691 -- Ensure that the p_datetrack_mode argument is not null
2692 --
2693 hr_api.mandatory_arg_error
2694 (p_api_name => l_proc
2695 ,p_argument => 'datetrack_mode'
2699 -- Mode will be valid, as this is checked at the start of the upd.
2696 ,p_argument_value => p_datetrack_mode
2697 );
2698 --
2700 --
2701 -- Ensure the arguments are not null
2702 --
2703 hr_api.mandatory_arg_error
2704 (p_api_name => l_proc
2705 ,p_argument => 'validation_start_date'
2706 ,p_argument_value => p_validation_start_date
2707 );
2708 --
2709 hr_api.mandatory_arg_error
2710 (p_api_name => l_proc
2711 ,p_argument => 'validation_end_date'
2712 ,p_argument_value => p_validation_end_date
2713 );
2714 --
2715 --
2716 --
2717 Exception
2718 When Others Then
2719 --
2720 -- An unhandled or unexpected error has occurred which
2721 -- we must report
2722 --
2723 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2724 fnd_message.set_token('PROCEDURE', l_proc);
2725 fnd_message.set_token('STEP','15');
2726 fnd_message.raise_error;
2727 End dt_update_validate;
2728 --
2729 -- ----------------------------------------------------------------------------
2730 -- |--------------------------< dt_delete_validate >--------------------------|
2731 -- ----------------------------------------------------------------------------
2732 -- {Start Of Comments}
2733 --
2734 -- Description:
2735 -- This procedure is used for referential integrity of datetracked
2736 -- child entities when either a datetrack DELETE or ZAP is in operation
2737 -- and where there is no cascading of delete defined for this entity.
2738 -- For the datetrack mode of DELETE or ZAP we must ensure that no
2739 -- datetracked child rows exist between the validation start and end
2740 -- dates.
2741 --
2742 -- Prerequisites:
2743 -- This procedure is called from the delete_validate.
2744 --
2745 -- In Parameters:
2746 --
2747 -- Post Success:
2748 -- Processing continues.
2749 --
2750 -- Post Failure:
2751 -- If a row exists by determining the returning Boolean value from the
2752 -- generic dt_api.rows_exist function then we must supply an error via
2753 -- the use of the local exception handler l_rows_exist.
2754 --
2755 -- Developer Implementation Notes:
2756 -- This procedure should not need maintenance unless the HR Schema model
2757 -- changes.
2758 --
2759 -- Access Status:
2760 -- Internal Row Handler Use Only.
2761 --
2762 -- {End Of Comments}
2763 -- ----------------------------------------------------------------------------
2764 Procedure dt_delete_validate
2765 (p_sii_details_id in number
2766 ,p_datetrack_mode in varchar2
2767 ,p_validation_start_date in date
2771 l_proc varchar2(72) := g_package||'dt_delete_validate';
2768 ,p_validation_end_date in date
2769 ) Is
2770 --
2772 --
2773 Begin
2774 --
2775 -- Ensure that the p_datetrack_mode argument is not null
2776 --
2777 hr_api.mandatory_arg_error
2778 (p_api_name => l_proc
2779 ,p_argument => 'datetrack_mode'
2780 ,p_argument_value => p_datetrack_mode
2781 );
2782 --
2783 -- Only perform the validation if the datetrack mode is either
2784 -- DELETE or ZAP
2785 --
2786 If (p_datetrack_mode = hr_api.g_delete or
2787 p_datetrack_mode = hr_api.g_zap) then
2788 --
2789 --
2790 -- Ensure the arguments are not null
2791 --
2792 hr_api.mandatory_arg_error
2793 (p_api_name => l_proc
2794 ,p_argument => 'validation_start_date'
2795 ,p_argument_value => p_validation_start_date
2796 );
2797 --
2798 hr_api.mandatory_arg_error
2799 (p_api_name => l_proc
2800 ,p_argument => 'validation_end_date'
2801 ,p_argument_value => p_validation_end_date
2802 );
2803 --
2804 hr_api.mandatory_arg_error
2805 (p_api_name => l_proc
2806 ,p_argument => 'sii_details_id'
2807 ,p_argument_value => p_sii_details_id
2808 );
2809 --
2810 --
2811 --
2812 End If;
2813 --
2814 Exception
2815 When Others Then
2816 --
2817 -- An unhandled or unexpected error has occurred which
2818 -- we must report
2819 --
2820 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
2821 fnd_message.set_token('PROCEDURE', l_proc);
2822 fnd_message.set_token('STEP','15');
2823 fnd_message.raise_error;
2824 --
2825 End dt_delete_validate;
2826 --
2827 -- ----------------------------------------------------------------------------
2828 -- |---------------------------< insert_validate >----------------------------|
2829 -- ----------------------------------------------------------------------------
2830 Procedure insert_validate
2831 (p_rec in pay_psd_shd.g_rec_type
2832 ,p_effective_date in date
2833 ,p_datetrack_mode in varchar2
2834 ,p_validation_start_date in date
2835 ,p_validation_end_date in date
2836 ) is
2837 --
2838 l_proc varchar2(72) := g_package||'insert_validate';
2839 --
2840 Begin
2841 hr_utility.set_location('Entering:'||l_proc, 5);
2842 --
2843 -- Call all supporting business operations
2844 --
2845 hr_api.validate_bus_grp_id
2846 (p_business_group_id => p_rec.business_group_id
2847 ,p_associated_column1 => pay_psd_shd.g_tab_nam
2848 || '.BUSINESS_GROUP_ID');
2849 --
2850 -- After validating the set of important attributes,
2851 -- if Multiple Message detection is enabled and at least
2852 -- one error has been found then abort further validation.
2853 --
2854 hr_multi_message.end_validation_set;
2855 --
2856 -- Validate Dependent Attributes
2857 --
2858
2859 -- Validate the Contract Category
2860 pay_psd_bus.chk_contract_category(p_sii_details_id => p_rec.sii_details_id,
2861 p_effective_date => p_effective_date,
2862 p_contract_category => p_rec.contract_category,
2863 p_validation_start_date => p_validation_start_date,
2864 p_validation_end_date => p_validation_end_date,
2865 p_object_version_number => p_rec.object_version_number);
2866
2867
2868
2869 -- Validate the Business Group Id
2870 pay_psd_bus.chk_business_group_id(p_sii_details_id => p_rec.sii_details_id,
2871 p_effective_date => p_effective_date,
2872 p_business_group_id => p_rec.business_group_id,
2873 p_validation_start_date => p_validation_start_date,
2874 p_validation_end_date => p_validation_end_date,
2875 p_object_version_number => p_rec.object_version_number);
2876
2877
2878
2879 -- Validate the Person/Assignment Id
2880 pay_psd_bus.chk_per_asg_id(p_effective_date => p_effective_date,
2881 p_per_or_asg_id => p_rec.per_or_asg_id,
2882 p_contract_category => p_rec.contract_category,
2883 p_business_group_id => p_rec.business_group_id,
2884 p_object_version_number => p_rec.object_version_number);
2885
2886
2887 -- Validate the Old Age Contribution Values
2888 pay_psd_bus.chk_old_age_contribution(p_sii_details_id => p_rec.sii_details_id,
2889 p_effective_date => p_effective_date,
2890 p_old_age_contribution => p_rec.old_age_contribution,
2891 p_validation_start_date => p_validation_start_date,
2892 p_validation_end_date => p_validation_end_date,
2893 p_object_version_number => p_rec.object_version_number,
2894 p_contract_category => p_rec.contract_category);
2895
2896
2897
2898 -- Validate the Pension Contribution Values
2899 pay_psd_bus.chk_pension_contribution(p_sii_details_id => p_rec.sii_details_id,
2900 p_effective_date => p_effective_date,
2901 p_pension_contribution => p_rec.pension_contribution,
2905 p_contract_category => p_rec.contract_category);
2902 p_validation_start_date => p_validation_start_date,
2903 p_validation_end_date => p_validation_end_date,
2904 p_object_version_number => p_rec.object_version_number,
2906
2907
2908 -- Validate the Sickness Contribution Values
2909 pay_psd_bus.chk_sickness_contribution(p_sii_details_id => p_rec.sii_details_id,
2910 p_effective_date => p_effective_date,
2911 p_sickness_contribution => p_rec.sickness_contribution,
2912 p_validation_start_date => p_validation_start_date,
2913 p_validation_end_date => p_validation_end_date,
2914 p_object_version_number => p_rec.object_version_number,
2915 p_contract_category => p_rec.contract_category);
2916
2917
2918
2919 -- Validate the Work Injury Contribution Values
2920 pay_psd_bus.chk_work_injury_contribution(p_sii_details_id => p_rec.sii_details_id,
2921 p_effective_date => p_effective_date,
2922 p_work_injury_contribution => p_rec.work_injury_contribution,
2923 p_validation_start_date => p_validation_start_date,
2924 p_validation_end_date => p_validation_end_date,
2925 p_object_version_number => p_rec.object_version_number,
2926 p_contract_category => p_rec.contract_category);
2927
2928
2929
2930 -- Validate the Labor Contribution Values
2931 pay_psd_bus.chk_labor_contribution(p_sii_details_id => p_rec.sii_details_id,
2932 p_effective_date => p_effective_date,
2933 p_labor_contribution => p_rec.labor_contribution,
2934 p_validation_start_date => p_validation_start_date,
2935 p_validation_end_date => p_validation_end_date,
2936 p_object_version_number => p_rec.object_version_number,
2937 p_contract_category => p_rec.contract_category);
2938
2939
2940
2941 -- Validate the Health Contribution Values
2942 pay_psd_bus.chk_health_contribution(p_sii_details_id => p_rec.sii_details_id,
2943 p_effective_date => p_effective_date,
2944 p_health_contribution => p_rec.health_contribution,
2945 p_validation_start_date => p_validation_start_date,
2946 p_validation_end_date => p_validation_end_date,
2947 p_object_version_number => p_rec.object_version_number,
2948 p_contract_category => p_rec.contract_category);
2949
2950
2951 -- Validate the Unemployment Contribution Values
2952 pay_psd_bus.chk_unemployment_contribution(p_sii_details_id => p_rec.sii_details_id,
2953 p_effective_date => p_effective_date,
2954 p_unemployment_contribution =>
2955 p_rec.unemployment_contribution,
2956 p_validation_start_date => p_validation_start_date,
2957 p_validation_end_date => p_validation_end_date,
2958 p_object_version_number => p_rec.object_version_number,
2959 p_contract_category => p_rec.contract_category);
2960
2961
2962 -- Validate the Old Age Contribution End Reason Values
2963 pay_psd_bus.chk_old_age_cont_end_reason(p_sii_details_id => p_rec.sii_details_id,
2964 p_effective_date => p_effective_date,
2965 p_old_age_contribution => p_rec.old_age_contribution,
2966 p_old_age_cont_end_reason => p_rec.old_age_cont_end_reason,
2967 p_validation_start_date => p_validation_start_date,
2968 p_validation_end_date => p_validation_end_date,
2969 p_object_version_number=> p_rec.object_version_number);
2970
2971
2972 -- Validate the Pension Contribution End Reason Values
2973 pay_psd_bus.chk_pension_cont_end_reason(p_sii_details_id => p_rec.sii_details_id,
2974 p_effective_date => p_effective_date,
2975 p_pension_contribution => p_rec.pension_contribution,
2976 p_pension_cont_end_reason => p_rec.pension_cont_end_reason,
2977 p_validation_start_date => p_validation_start_date,
2978 p_validation_end_date => p_validation_end_date,
2979 p_object_version_number=> p_rec.object_version_number);
2980
2981 -- Validate the Sickness Contribution End Reason Values
2982 pay_psd_bus.chk_sickness_cont_end_reason(p_sii_details_id => p_rec.sii_details_id,
2983 p_effective_date => p_effective_date,
2984 p_sickness_contribution => p_rec.sickness_contribution,
2985 p_sickness_cont_end_reason => p_rec.sickness_cont_end_reason,
2986 p_validation_start_date => p_validation_start_date,
2987 p_validation_end_date => p_validation_end_date,
2988 p_object_version_number=> p_rec.object_version_number);
2989
2990
2991 -- Validate the Work Injury Contribution End Reason Values
2995 p_rec.work_injury_contribution,
2992 pay_psd_bus.chk_work_injury_cont_end(p_sii_details_id => p_rec.sii_details_id,
2993 p_effective_date => p_effective_date,
2994 p_work_injury_contribution =>
2996 p_work_injury_cont_end_reason =>
2997 p_rec.work_injury_cont_end_reason,
2998 p_validation_start_date => p_validation_start_date,
2999 p_validation_end_date => p_validation_end_date,
3000 p_object_version_number=> p_rec.object_version_number);
3001
3002 -- Validate the Labor fund Contribution End Reason Values
3003 pay_psd_bus.chk_labor_fund_cont_end_reason(p_sii_details_id => p_rec.sii_details_id,
3004 p_effective_date => p_effective_date,
3005 p_labor_contribution => p_rec.labor_contribution,
3006 p_labor_fund_cont_end_reason =>
3007 p_rec.labor_fund_cont_end_reason,
3008 p_validation_start_date => p_validation_start_date,
3009 p_validation_end_date => p_validation_end_date,
3010 p_object_version_number=> p_rec.object_version_number);
3011
3012
3013 -- Validate the Health Contribution End Reason Values
3014 pay_psd_bus.chk_health_cont_end_reason(p_sii_details_id => p_rec.sii_details_id,
3015 p_effective_date => p_effective_date,
3016 p_health_contribution => p_rec.health_contribution,
3017 p_health_cont_end_reason => p_rec.health_cont_end_reason,
3018 p_validation_start_date => p_validation_start_date,
3019 p_validation_end_date => p_validation_end_date,
3020 p_object_version_number=> p_rec.object_version_number);
3021
3022
3023 -- Validate the Unemployment Contribution End Reason Values
3024 pay_psd_bus.chk_unemployment_cont_end(p_sii_details_id => p_rec.sii_details_id,
3025 p_effective_date => p_effective_date,
3026 p_unemployment_contribution =>
3027 p_rec.unemployment_contribution,
3028 p_unemployment_cont_end_reason =>
3029 p_rec.unemployment_cont_end_reason,
3030 p_validation_start_date => p_validation_start_date,
3031 p_validation_end_date => p_validation_end_date,
3032 p_object_version_number=> p_rec.object_version_number);
3033
3034
3035
3036
3037
3038 --
3039 hr_utility.set_location(' Leaving:'||l_proc, 10);
3040 End insert_validate;
3041 --
3042 -- ----------------------------------------------------------------------------
3043 -- |---------------------------< update_validate >----------------------------|
3044 -- ----------------------------------------------------------------------------
3045 Procedure update_validate
3046 (p_rec in pay_psd_shd.g_rec_type
3047 ,p_effective_date in date
3048 ,p_datetrack_mode in varchar2
3049 ,p_validation_start_date in date
3050 ,p_validation_end_date in date
3051 ) is
3052 --
3053 l_proc varchar2(72) := g_package||'update_validate';
3054 --
3055 Begin
3056 hr_utility.set_location('Entering:'||l_proc, 5);
3057 --
3058 -- Call all supporting business operations
3059 --
3060 hr_api.validate_bus_grp_id
3061 (p_business_group_id => p_rec.business_group_id
3062 ,p_associated_column1 => pay_psd_shd.g_tab_nam
3063 || '.BUSINESS_GROUP_ID');
3064 --
3065 -- After validating the set of important attributes,
3066 -- if Multiple Message detection is enabled and at least
3067 -- one error has been found then abort further validation.
3068 --
3069 hr_multi_message.end_validation_set;
3070 --
3071 -- Validate Dependent Attributes
3072 --
3073 -- Call the datetrack update integrity operation
3074 --
3075 dt_update_validate
3076 (p_datetrack_mode => p_datetrack_mode
3077 ,p_validation_start_date => p_validation_start_date
3078 ,p_validation_end_date => p_validation_end_date
3079 );
3080 --
3081 chk_non_updateable_args
3082 (p_effective_date => p_effective_date
3083 ,p_rec => p_rec
3084 );
3085 --
3086
3087 --
3088
3089 -- Validate the Employee Social Security Information Values
3090 pay_psd_bus.chk_emp_social_security_info(p_sii_details_id => p_rec.sii_details_id,
3091 p_effective_date => p_effective_date,
3092 p_emp_social_security_info => p_rec.emp_social_security_info,
3093 p_object_version_number => p_rec.object_version_number);
3094
3095
3096 -- Validate the Old Age Contribution Values
3097 pay_psd_bus.chk_old_age_contribution(p_sii_details_id => p_rec.sii_details_id,
3098 p_effective_date => p_effective_date,
3099 p_old_age_contribution => p_rec.old_age_contribution,
3100 p_validation_start_date => p_validation_start_date,
3101 p_validation_end_date => p_validation_end_date,
3105
3102 p_object_version_number => p_rec.object_version_number,
3103 p_contract_category => p_rec.contract_category);
3104
3106
3107 -- Validate the Pension Contribution Values
3108 pay_psd_bus.chk_pension_contribution(p_sii_details_id => p_rec.sii_details_id,
3109 p_effective_date => p_effective_date,
3110 p_pension_contribution => p_rec.pension_contribution,
3111 p_validation_start_date => p_validation_start_date,
3112 p_validation_end_date => p_validation_end_date,
3113 p_object_version_number => p_rec.object_version_number,
3114 p_contract_category => p_rec.contract_category);
3115
3116
3117 -- Validate the Sickness Contribution Values
3118 pay_psd_bus.chk_sickness_contribution(p_sii_details_id => p_rec.sii_details_id,
3119 p_effective_date => p_effective_date,
3120 p_sickness_contribution => p_rec.sickness_contribution,
3121 p_validation_start_date => p_validation_start_date,
3122 p_validation_end_date => p_validation_end_date,
3123 p_object_version_number => p_rec.object_version_number,
3124 p_contract_category => p_rec.contract_category);
3125
3126
3127
3128 -- Validate the Work Injury Contribution Values
3129 pay_psd_bus.chk_work_injury_contribution(p_sii_details_id => p_rec.sii_details_id,
3130 p_effective_date => p_effective_date,
3131 p_work_injury_contribution => p_rec.work_injury_contribution,
3132 p_validation_start_date => p_validation_start_date,
3133 p_validation_end_date => p_validation_end_date,
3134 p_object_version_number => p_rec.object_version_number,
3135 p_contract_category => p_rec.contract_category);
3136
3137
3138
3139 -- Validate the Labor Contribution Values
3140 pay_psd_bus.chk_labor_contribution(p_sii_details_id => p_rec.sii_details_id,
3141 p_effective_date => p_effective_date,
3142 p_labor_contribution => p_rec.labor_contribution,
3143 p_validation_start_date => p_validation_start_date,
3144 p_validation_end_date => p_validation_end_date,
3145 p_object_version_number => p_rec.object_version_number,
3146 p_contract_category => p_rec.contract_category);
3147
3148
3149
3150 -- Validate the Health Contribution Values
3151 pay_psd_bus.chk_health_contribution(p_sii_details_id => p_rec.sii_details_id,
3152 p_effective_date => p_effective_date,
3153 p_health_contribution => p_rec.health_contribution,
3154 p_validation_start_date => p_validation_start_date,
3155 p_validation_end_date => p_validation_end_date,
3156 p_object_version_number => p_rec.object_version_number,
3157 p_contract_category => p_rec.contract_category);
3158
3159
3160 -- Validate the Unemployment Contribution Values
3161 pay_psd_bus.chk_unemployment_contribution(p_sii_details_id => p_rec.sii_details_id,
3162 p_effective_date => p_effective_date,
3163 p_unemployment_contribution =>
3164 p_rec.unemployment_contribution,
3165 p_validation_start_date => p_validation_start_date,
3166 p_validation_end_date => p_validation_end_date,
3167 p_object_version_number => p_rec.object_version_number,
3168 p_contract_category => p_rec.contract_category);
3169
3170 -- Validate the Old Age Contribution End Reason Values
3171 pay_psd_bus.chk_old_age_cont_end_reason(p_sii_details_id => p_rec.sii_details_id,
3172 p_effective_date => p_effective_date,
3173 p_old_age_contribution => p_rec.old_age_contribution,
3174 p_old_age_cont_end_reason => p_rec.old_age_cont_end_reason,
3175 p_validation_start_date => p_validation_start_date,
3176 p_validation_end_date => p_validation_end_date,
3177 p_object_version_number=> p_rec.object_version_number);
3178
3179
3180 -- Validate the Pension Contribution End Reason Values
3181 pay_psd_bus.chk_pension_cont_end_reason(p_sii_details_id => p_rec.sii_details_id,
3182 p_effective_date => p_effective_date,
3183 p_pension_contribution => p_rec.pension_contribution,
3184 p_pension_cont_end_reason => p_rec.pension_cont_end_reason,
3185 p_validation_start_date => p_validation_start_date,
3186 p_validation_end_date => p_validation_end_date,
3187 p_object_version_number=> p_rec.object_version_number);
3188
3189 -- Validate the Sickness Contribution End Reason Values
3190 pay_psd_bus.chk_sickness_cont_end_reason(p_sii_details_id => p_rec.sii_details_id,
3191 p_effective_date => p_effective_date,
3195 p_validation_end_date => p_validation_end_date,
3192 p_sickness_contribution => p_rec.sickness_contribution,
3193 p_sickness_cont_end_reason => p_rec.sickness_cont_end_reason,
3194 p_validation_start_date => p_validation_start_date,
3196 p_object_version_number=> p_rec.object_version_number);
3197
3198
3199 -- Validate the Work Injury Contribution End Reason Values
3200 pay_psd_bus.chk_work_injury_cont_end(p_sii_details_id => p_rec.sii_details_id,
3201 p_effective_date => p_effective_date,
3202 p_work_injury_contribution =>
3203 p_rec.work_injury_contribution,
3204 p_work_injury_cont_end_reason =>
3205 p_rec.work_injury_cont_end_reason,
3206 p_validation_start_date => p_validation_start_date,
3207 p_validation_end_date => p_validation_end_date,
3208 p_object_version_number=> p_rec.object_version_number);
3209
3210 -- Validate the Labor fund Contribution End Reason Values
3211 pay_psd_bus.chk_labor_fund_cont_end_reason(p_sii_details_id => p_rec.sii_details_id,
3212 p_effective_date => p_effective_date,
3213 p_labor_contribution => p_rec.labor_contribution,
3214 p_labor_fund_cont_end_reason =>
3215 p_rec.labor_fund_cont_end_reason,
3216 p_validation_start_date => p_validation_start_date,
3217 p_validation_end_date => p_validation_end_date,
3218 p_object_version_number=> p_rec.object_version_number);
3219
3220
3221 -- Validate the Health Contribution End Reason Values
3222 pay_psd_bus.chk_health_cont_end_reason(p_sii_details_id => p_rec.sii_details_id,
3223 p_effective_date => p_effective_date,
3224 p_health_contribution => p_rec.health_contribution,
3225 p_health_cont_end_reason => p_rec.health_cont_end_reason,
3226 p_validation_start_date => p_validation_start_date,
3227 p_validation_end_date => p_validation_end_date,
3228 p_object_version_number=> p_rec.object_version_number);
3229
3230
3231 -- Validate the Unemployment Contribution End Reason Values
3232 pay_psd_bus.chk_unemployment_cont_end(p_sii_details_id => p_rec.sii_details_id,
3233 p_effective_date => p_effective_date,
3234 p_unemployment_contribution =>
3235 p_rec.unemployment_contribution,
3236 p_unemployment_cont_end_reason =>
3237 p_rec.unemployment_cont_end_reason,
3238 p_validation_start_date => p_validation_start_date,
3239 p_validation_end_date => p_validation_end_date,
3240 p_object_version_number=> p_rec.object_version_number);
3241
3242
3243 --
3244 hr_utility.set_location(' Leaving:'||l_proc, 10);
3245 End update_validate;
3246 --
3247 -- ----------------------------------------------------------------------------
3248 -- |---------------------------< delete_validate >----------------------------|
3249 -- ----------------------------------------------------------------------------
3250 Procedure delete_validate
3251 (p_rec in pay_psd_shd.g_rec_type
3252 ,p_effective_date in date
3253 ,p_datetrack_mode in varchar2
3254 ,p_validation_start_date in date
3255 ,p_validation_end_date in date
3256 ) is
3257 --
3258 l_proc varchar2(72) := g_package||'delete_validate';
3259 --
3260 Begin
3261 hr_utility.set_location('Entering:'||l_proc, 5);
3262 --
3263 -- Call all supporting business operations
3264 --
3265 dt_delete_validate
3266 (p_datetrack_mode => p_datetrack_mode
3267 ,p_validation_start_date => p_validation_start_date
3268 ,p_validation_end_date => p_validation_end_date
3269 ,p_sii_details_id => p_rec.sii_details_id
3270 );
3271 --
3272 hr_utility.set_location(' Leaving:'||l_proc, 10);
3273 End delete_validate;
3274 --
3275 -- ----------------------------------------------------------------------------
3276 --|-----------------------< get_contribution_values >--------------------------|
3277 -- ----------------------------------------------------------------------------
3278 -- {Start Of Comments}
3279 --
3280 -- Description:
3281 -- This procedure derives the various Contribution values when the 'Employee
3282 -- Social Security Information' value is passed in.
3283 --
3284 -- Prerequisites:
3285 --
3286 --
3287 -- In Parameters:
3288 -- p_emp_social_security_info
3289 -- p_effective_date
3290 --
3291 -- In/Out Parameters
3292 -- p_old_age_contribution
3293 -- p_pension_contribution
3294 -- p_sickness_contribution
3295 -- p_work_injury_contribution
3296 -- p_labor_contribution
3297 -- p_health_contribution
3298 -- p_unemployment_contribution
3299 --
3300 --
3301 -- Post Success:
3302 -- Processing continues.
3303 --
3304 -- Post Failure:
3308 -- Access Status:
3305 -- An error is raised if the validation fails.
3306 --
3307 --
3309 -- Internal Development Use Only.
3310 --
3311 -- {End Of Comments}
3312 -- ----------------------------------------------------------------------------
3313 Procedure get_contribution_values
3314 (p_effective_date in date
3315 ,p_emp_social_security_info in varchar2
3316 ,p_old_age_contribution in out nocopy varchar2
3317 ,p_pension_contribution in out nocopy varchar2
3318 ,p_sickness_contribution in out nocopy varchar2
3319 ,p_work_injury_contribution in out nocopy varchar2
3320 ,p_labor_contribution in out nocopy varchar2
3321 ,p_health_contribution in out nocopy varchar2
3322 ,p_unemployment_contribution in out nocopy varchar2) IS
3323
3324 begin
3325
3326
3327
3328
3329
3330 if p_old_age_contribution is null then
3331 select pucif.value
3332 into p_old_age_contribution
3333 from
3334 pay_user_column_instances_f pucif,
3335 pay_user_columns puc,
3336 pay_user_rows_f purf,
3337 pay_user_tables put
3338 where put.user_table_name ='PL_EMPLOYEE_SOCIAL_SECURITY_INFORMATION'
3339 and put.legislation_code = 'PL'
3340 and purf.legislation_code = 'PL'
3341 and purf.user_table_id = put.user_table_id
3342 and p_effective_date between purf.effective_start_date and purf.effective_end_date
3343 and purf.row_low_range_or_name = 'Old Age'
3344 and puc.user_column_name = p_emp_social_security_info
3345 and puc.user_table_id = put.user_table_id
3346 and pucif.user_row_id = purf.user_row_id
3347 and pucif.user_column_id = puc.user_column_id
3348 and p_effective_date between pucif.effective_start_date and pucif.effective_end_date;
3349 end if;
3350
3351
3352
3353 if p_pension_contribution is null then
3354 select pucif.value
3355 into p_pension_contribution
3356 from
3357 pay_user_column_instances_f pucif,
3358 pay_user_columns puc,
3359 pay_user_rows_f purf,
3360 pay_user_tables put
3361 where put.user_table_name ='PL_EMPLOYEE_SOCIAL_SECURITY_INFORMATION'
3362 and put.legislation_code = 'PL'
3363 and purf.legislation_code = 'PL'
3364 and purf.user_table_id = put.user_table_id
3365 and p_effective_date between purf.effective_start_date and purf.effective_end_date
3366 and purf.row_low_range_or_name = 'Pension'
3367 and puc.user_column_name = p_emp_social_security_info
3368 and puc.user_table_id = put.user_table_id
3369 and pucif.user_row_id = purf.user_row_id
3370 and pucif.user_column_id = puc.user_column_id
3371 and p_effective_date between pucif.effective_start_date and pucif.effective_end_date;
3372 end if;
3373
3374
3375 if p_sickness_contribution is null then
3376 select pucif.value
3377 into p_sickness_contribution
3378 from
3379 pay_user_column_instances_f pucif,
3380 pay_user_columns puc,
3381 pay_user_rows_f purf,
3382 pay_user_tables put
3383 where put.user_table_name ='PL_EMPLOYEE_SOCIAL_SECURITY_INFORMATION'
3384 and put.legislation_code = 'PL'
3385 and purf.legislation_code = 'PL'
3386 and purf.user_table_id = put.user_table_id
3387 and p_effective_date between purf.effective_start_date and purf.effective_end_date
3388 and purf.row_low_range_or_name = 'Sickness'
3389 and puc.user_column_name = p_emp_social_security_info
3390 and puc.user_table_id = put.user_table_id
3391 and pucif.user_row_id = purf.user_row_id
3392 and pucif.user_column_id = puc.user_column_id
3393 and p_effective_date between pucif.effective_start_date and pucif.effective_end_date;
3394 end if;
3395
3396 if p_work_injury_contribution is null then
3397 select pucif.value
3398 into p_work_injury_contribution
3399 from
3400 pay_user_column_instances_f pucif,
3401 pay_user_columns puc,
3402 pay_user_rows_f purf,
3403 pay_user_tables put
3404 where put.user_table_name ='PL_EMPLOYEE_SOCIAL_SECURITY_INFORMATION'
3405 and put.legislation_code = 'PL'
3406 and purf.legislation_code = 'PL'
3407 and purf.user_table_id = put.user_table_id
3408 and p_effective_date between purf.effective_start_date and purf.effective_end_date
3409 and purf.row_low_range_or_name = 'Work Injury'
3410 and puc.user_column_name = p_emp_social_security_info
3411 and puc.user_table_id = put.user_table_id
3412 and pucif.user_row_id = purf.user_row_id
3413 and pucif.user_column_id = puc.user_column_id
3414 and p_effective_date between pucif.effective_start_date and pucif.effective_end_date;
3415 end if;
3416
3417 if p_labor_contribution is null then
3418 select pucif.value
3419 into p_labor_contribution
3420 from
3421 pay_user_column_instances_f pucif,
3422 pay_user_columns puc,
3423 pay_user_rows_f purf,
3424 pay_user_tables put
3425 where put.user_table_name ='PL_EMPLOYEE_SOCIAL_SECURITY_INFORMATION'
3426 and put.legislation_code = 'PL'
3427 and purf.legislation_code = 'PL'
3428 and purf.user_table_id = put.user_table_id
3429 and p_effective_date between purf.effective_start_date and purf.effective_end_date
3433 and pucif.user_row_id = purf.user_row_id
3430 and purf.row_low_range_or_name = 'Labor'
3431 and puc.user_column_name = p_emp_social_security_info
3432 and puc.user_table_id = put.user_table_id
3434 and pucif.user_column_id = puc.user_column_id
3435 and p_effective_date between pucif.effective_start_date and pucif.effective_end_date;
3436 end if;
3437
3438
3439 if p_health_contribution is null then
3440 select pucif.value
3441 into p_health_contribution
3442 from
3443 pay_user_column_instances_f pucif,
3444 pay_user_columns puc,
3445 pay_user_rows_f purf,
3446 pay_user_tables put
3447 where put.user_table_name ='PL_EMPLOYEE_SOCIAL_SECURITY_INFORMATION'
3448 and put.legislation_code = 'PL'
3449 and purf.legislation_code = 'PL'
3450 and purf.user_table_id = put.user_table_id
3451 and p_effective_date between purf.effective_start_date and purf.effective_end_date
3452 and purf.row_low_range_or_name = 'Health'
3453 and puc.user_column_name = p_emp_social_security_info
3454 and puc.user_table_id = put.user_table_id
3455 and pucif.user_row_id = purf.user_row_id
3456 and pucif.user_column_id = puc.user_column_id
3457 and p_effective_date between pucif.effective_start_date and pucif.effective_end_date;
3458 end if;
3459
3460
3461 if p_unemployment_contribution is null then
3462 select pucif.value
3463 into p_unemployment_contribution
3464 from
3465 pay_user_column_instances_f pucif,
3466 pay_user_columns puc,
3467 pay_user_rows_f purf,
3468 pay_user_tables put
3469 where put.user_table_name ='PL_EMPLOYEE_SOCIAL_SECURITY_INFORMATION'
3470 and put.legislation_code = 'PL'
3471 and purf.legislation_code = 'PL'
3472 and purf.user_table_id = put.user_table_id
3473 and p_effective_date between purf.effective_start_date and purf.effective_end_date
3474 and purf.row_low_range_or_name = 'Unemployment'
3475 and puc.user_column_name = p_emp_social_security_info
3476 and puc.user_table_id = put.user_table_id
3477 and pucif.user_row_id = purf.user_row_id
3478 and pucif.user_column_id = puc.user_column_id
3479 and p_effective_date between pucif.effective_start_date and pucif.effective_end_date;
3480 end if;
3481
3482 exception
3483 when no_data_found then
3484 hr_utility.set_message(801,'PAY_375858_NO_EMP_SOCIAL_INFO');
3485 hr_utility.raise_error;
3486
3487
3488 end get_contribution_values;
3489
3490 end pay_psd_bus;
[Home] [Help]