[Home] [Help]
6: -- ----------------------------------------------------------------------------
7: --
8: --
9: g_package varchar2(33) := ' per_per_bus.'; -- Global package name
10: g_debug boolean := hr_utility.debug_enabled;
11: --
12: -- The following two global variables are only to be used by the
13: -- return_legislation_code function.
14: --
46: l_proc varchar2(72) := g_package||'set_security_group_id';
47: --
48: begin
49: if g_debug then
50: hr_utility.set_location('Entering:'|| l_proc, 10);
51: end if;
52: --
53: -- Ensure that all the mandatory parameter are not null
54: --
62: close csr_sec_grp;
63: --
64: -- The primary key is invalid therefore we must error
65: --
66: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
67: hr_multi_message.add(p_associated_column1 =>
68: nvl(p_associated_column1,'PERSON_ID'));
69: else
70: close csr_sec_grp;
80: hr_api.set_legislation_context(l_legislation_code);
81: --
82: end if;
83: if g_debug then
84: hr_utility.set_location(' Leaving:'|| l_proc, 20);
85: end if;
86: --
87: end set_security_group_id;
88: -- ---------------------------------------------------------------------------
128: and p_business_group_id = pet.business_group_id + 0;
129: --
130: begin
131: if g_debug then
132: hr_utility.set_location('Entering:'|| l_proc, 1);
133: end if;
134: --
135: -- Check mandatory parameters have been set
136: --
159: fetch csr_chk_person_type into g_previous_sys_per_type;
160: If csr_chk_person_type%found then
161: close csr_chk_person_type;
162: if g_debug then
163: hr_utility.set_location(l_proc, 3);
164: end if;
165: else
166: --
167: if g_debug then
164: end if;
165: else
166: --
167: if g_debug then
168: hr_utility.set_location(l_proc, 4);
169: end if;
170: --
171: close csr_chk_person_type;
172: -- Error: Invalid person type
169: end if;
170: --
171: close csr_chk_person_type;
172: -- Error: Invalid person type
173: hr_utility.set_message(801,'HR_7513_PER_TYPE_INVALID');
174: hr_utility.raise_error;
175: end if;
176: end if;
177: if g_debug then
170: --
171: close csr_chk_person_type;
172: -- Error: Invalid person type
173: hr_utility.set_message(801,'HR_7513_PER_TYPE_INVALID');
174: hr_utility.raise_error;
175: end if;
176: end if;
177: if g_debug then
178: hr_utility.set_location(' Leaving '||l_proc, 5);
174: hr_utility.raise_error;
175: end if;
176: end if;
177: if g_debug then
178: hr_utility.set_location(' Leaving '||l_proc, 5);
179: end if;
180: return g_previous_sys_per_type;
181: exception
182: when app_exception.application_exception then
183: if hr_multi_message.exception_add
184: (p_associated_column1 => 'PER_ALL_PEOPLE_F.PERSON_TYPE_ID'
185: ) then
186: if g_debug then
187: hr_utility.set_location(' Leaving:'||l_proc, 6);
188: end if;
189: raise;
190: end if;
191: if g_debug then
188: end if;
189: raise;
190: end if;
191: if g_debug then
192: hr_utility.set_location(' Leaving:'||l_proc,7);
193: end if;
194: -- call to stop proceeding further if there is an error in
195: -- system person type
196: --
263: l_system_person_type varchar2(30);
264: --
265: begin
266: if g_debug then
267: hr_utility.set_location('Entering:'|| l_proc, 1);
268: end if;
269: --
270: -- Check mandatory parameters have been set
271: --
301: <> p_person_type_id) or
302: (NOT l_api_updating)) then
303: --
304: if g_debug then
305: hr_utility.set_location(l_proc, 2);
306: end if;
307: --
308: -- Populate l_system_person_type with the value of system person type
309: --
310: l_system_person_type := return_system_person_type(p_person_type_id
311: ,p_business_group_id);
312: --
313: if g_debug then
314: hr_utility.set_location(l_proc, 3);
315: end if;
316: --
317: -- Check if the person is an employee
318: --
318: --
319: if l_system_person_type in ('EMP','EMP_APL') then
320: --
321: if g_debug then
322: hr_utility.set_location(l_proc, 4);
323: end if;
324: --
325: -- Person is an employee so set current employee flag to 'Y'
326: --
327: p_current_employee_flag := 'Y';
328: else
329: --
330: if g_debug then
331: hr_utility.set_location(l_proc, 5);
332: end if;
333: --
334: -- Person is not an employee so set to null
335: --
340: --
341: if l_system_person_type in ('APL','APL_EX_APL','EMP_APL','EX_EMP_APL') then
342: --
343: if g_debug then
344: hr_utility.set_location(l_proc, 6);
345: end if;
346: --
347: -- Person is an applicant so set current applicant flag to 'Y'
348: --
349: p_current_applicant_flag := 'Y';
350: else
351: --
352: if g_debug then
353: hr_utility.set_location(l_proc, 7);
354: end if;
355: --
356: -- Person is not an applicant so set to null
357: --
363: if l_system_person_type in ('EMP','APL','EMP_APL','EX_EMP_APL','APL_EX_APL')
364: then
365: --
366: if g_debug then
367: hr_utility.set_location(l_proc, 8);
368: end if;
369: --
370: -- Person is an employee or applicant so set current emp or apl flag
371: -- to 'Y'
375: --
376: -- Person is not an employee or applicant so set to null
377: --
378: if g_debug then
379: hr_utility.set_location(l_proc, 9);
380: end if;
381: --
382: p_current_emp_or_apl_flag := null;
383: --
382: p_current_emp_or_apl_flag := null;
383: --
384: end if;
385: if g_debug then
386: hr_utility.set_location(l_proc, 10);
387: end if;
388: else
389: if g_debug then
390: hr_utility.set_location(l_proc, 11);
386: hr_utility.set_location(l_proc, 10);
387: end if;
388: else
389: if g_debug then
390: hr_utility.set_location(l_proc, 11);
391: end if;
392: --
393: p_current_employee_flag := per_per_shd.g_old_rec.current_employee_flag;
394: p_current_applicant_flag := per_per_shd.g_old_rec.current_applicant_flag;
394: p_current_applicant_flag := per_per_shd.g_old_rec.current_applicant_flag;
395: p_current_emp_or_apl_flag := per_per_shd.g_old_rec.current_emp_or_apl_flag;
396: end if;
397: if g_debug then
398: hr_utility.set_location(' Leaving:'|| l_proc, 12);
399: end if;
400: end set_current_flags;
401: --
402: -- ---------------------------------------------------------------------------
517: */
518: --
519: begin
520: if g_debug then
521: hr_utility.set_location('Entering:'|| l_proc, 1);
522: end if;
523: --
524: -- Check mandatory parameters have been set
525: --
548: --
549: if p_datetrack_mode = 'UPDATE_OVERRIDE' then
550: --
551: if g_debug then
552: hr_utility.set_location(l_proc, 5);
553: end if;
554: --
555: per_per_bus.chk_system_pers_type
556: (p_person_id => p_person_id
587: l_active_flag;
588: if csr_person_type%notfound then
589: close csr_person_type;
590: -- Error: Invalid person type
591: hr_utility.set_message(801, 'HR_7513_PER_TYPE_INVALID');
592: hr_utility.raise_error;
593: end if;
594: close csr_person_type;
595: if l_business_group_id <> p_business_group_id then
588: if csr_person_type%notfound then
589: close csr_person_type;
590: -- Error: Invalid person type
591: hr_utility.set_message(801, 'HR_7513_PER_TYPE_INVALID');
592: hr_utility.raise_error;
593: end if;
594: close csr_person_type;
595: if l_business_group_id <> p_business_group_id then
596: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
592: hr_utility.raise_error;
593: end if;
594: close csr_person_type;
595: if l_business_group_id <> p_business_group_id then
596: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
597: hr_utility.raise_error;
598: elsif l_active_flag <> 'Y' then
599: hr_utility.set_message(801, 'HR_7973_PER_TYPE_NOT_ACTIVE');
600: hr_utility.raise_error;
593: end if;
594: close csr_person_type;
595: if l_business_group_id <> p_business_group_id then
596: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
597: hr_utility.raise_error;
598: elsif l_active_flag <> 'Y' then
599: hr_utility.set_message(801, 'HR_7973_PER_TYPE_NOT_ACTIVE');
600: hr_utility.raise_error;
601: end if;
595: if l_business_group_id <> p_business_group_id then
596: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
597: hr_utility.raise_error;
598: elsif l_active_flag <> 'Y' then
599: hr_utility.set_message(801, 'HR_7973_PER_TYPE_NOT_ACTIVE');
600: hr_utility.raise_error;
601: end if;
602: --
603: if g_debug then
596: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
597: hr_utility.raise_error;
598: elsif l_active_flag <> 'Y' then
599: hr_utility.set_message(801, 'HR_7973_PER_TYPE_NOT_ACTIVE');
600: hr_utility.raise_error;
601: end if;
602: --
603: if g_debug then
604: hr_utility.set_location(l_proc, 7);
600: hr_utility.raise_error;
601: end if;
602: --
603: if g_debug then
604: hr_utility.set_location(l_proc, 7);
605: end if;
606: --
607: -- If a new person is being inserted, then check that the system person
608: -- type is of a permitted value.
608: -- type is of a permitted value.
609: --
610: if p_datetrack_mode = 'INSERT' then
611: if l_new_system_person_type not in ('EMP', 'APL', 'OTHER') then
612: hr_utility.set_message(801, 'HR_7977_PER_INV_TYPE_FOR_INS');
613: hr_utility.raise_error;
614: end if;
615: end if;
616: --
609: --
610: if p_datetrack_mode = 'INSERT' then
611: if l_new_system_person_type not in ('EMP', 'APL', 'OTHER') then
612: hr_utility.set_message(801, 'HR_7977_PER_INV_TYPE_FOR_INS');
613: hr_utility.raise_error;
614: end if;
615: end if;
616: --
617: -- Only proceed with validation if:
632: l_discard_number,
633: l_discard_varchar2;
634: close csr_person_type;
635: if g_debug then
636: hr_utility.set_location(l_proc, 9);
637: end if;
638: --
639: -- Check if DateTrack mode is valid
640: --
645: -- update_change_insert is not allowed
646: --
647: if p_datetrack_mode = 'UPDATE_CHANGE_INSERT'
648: then
649: hr_utility.set_message(801, 'HR_7724_PER_DT_MODE_SPT_CHANGE');
650: hr_utility.raise_error;
651: end if;
652: --
653: -- DateTrack mode CORRECTION is allowed, provided the system person
646: --
647: if p_datetrack_mode = 'UPDATE_CHANGE_INSERT'
648: then
649: hr_utility.set_message(801, 'HR_7724_PER_DT_MODE_SPT_CHANGE');
650: hr_utility.raise_error;
651: end if;
652: --
653: -- DateTrack mode CORRECTION is allowed, provided the system person
654: -- type hasn't already been changed on the effective start date of
660: fetch csr_system_type
661: into l_original_sys_type;
662: if csr_system_type%notfound then
663: close csr_system_type;
664: hr_utility.set_message(801, 'HR_7984_PER_NO_PREVIOUS_ROW');
665: hr_utility.raise_error;
666: end if;
667: close csr_system_type;
668: if l_original_sys_type <> l_old_system_person_type then
661: into l_original_sys_type;
662: if csr_system_type%notfound then
663: close csr_system_type;
664: hr_utility.set_message(801, 'HR_7984_PER_NO_PREVIOUS_ROW');
665: hr_utility.raise_error;
666: end if;
667: close csr_system_type;
668: if l_original_sys_type <> l_old_system_person_type then
669: hr_utility.set_message(801, 'HR_7978_PER_TYPE_CHANGE');
665: hr_utility.raise_error;
666: end if;
667: close csr_system_type;
668: if l_original_sys_type <> l_old_system_person_type then
669: hr_utility.set_message(801, 'HR_7978_PER_TYPE_CHANGE');
670: hr_utility.raise_error;
671: end if;
672: end if;
673: */
666: end if;
667: close csr_system_type;
668: if l_original_sys_type <> l_old_system_person_type then
669: hr_utility.set_message(801, 'HR_7978_PER_TYPE_CHANGE');
670: hr_utility.raise_error;
671: end if;
672: end if;
673: */
674: end if;
672: end if;
673: */
674: end if;
675: if g_debug then
676: hr_utility.set_location(l_proc, 11);
677: end if;
678: --
679: -- Check that the system person type conversion is valid.
680: --
694: l_new_system_person_type not in ('EMP', 'EX_EMP_APL', 'EX_EMP'))
695: or (l_old_system_person_type = 'EX_EMP_APL' and
696: l_new_system_person_type not in ('EMP_APL', 'EMP', 'EX_EMP_APL','EX_EMP'))
697: then
698: hr_utility.set_message(801, 'HR_7987_PER_INV_TYPE_CHANGE');
699: hr_utility.raise_error;
700: else
701: if g_debug then
702: hr_utility.set_location(l_proc, 12);
695: or (l_old_system_person_type = 'EX_EMP_APL' and
696: l_new_system_person_type not in ('EMP_APL', 'EMP', 'EX_EMP_APL','EX_EMP'))
697: then
698: hr_utility.set_message(801, 'HR_7987_PER_INV_TYPE_CHANGE');
699: hr_utility.raise_error;
700: else
701: if g_debug then
702: hr_utility.set_location(l_proc, 12);
703: end if;
698: hr_utility.set_message(801, 'HR_7987_PER_INV_TYPE_CHANGE');
699: hr_utility.raise_error;
700: else
701: if g_debug then
702: hr_utility.set_location(l_proc, 12);
703: end if;
704: --
705: set_current_flags(p_person_id
706: ,p_business_group_id
717: end if;
718: end if;
719: end if;
720: if g_debug then
721: hr_utility.set_location(' Leaving:'|| l_proc, 13);
722: end if;
723: exception
724: when app_exception.application_exception then
725: if hr_multi_message.exception_add
725: if hr_multi_message.exception_add
726: (p_associated_column1 => 'PER_ALL_PEOPLE_F.PERSON_TYPE_ID'
727: ) then
728: if g_debug then
729: hr_utility.set_location(' Leaving:'||l_proc, 14);
730: end if;
731: raise;
732: end if;
733: if g_debug then
730: end if;
731: raise;
732: end if;
733: if g_debug then
734: hr_utility.set_location(' Leaving:'||l_proc,15);
735: end if;
736: hr_multi_message.end_validation_set;
737: end chk_person_type_id;
738: --
820: and asg.payroll_id is not null;
821: --
822: begin
823: if g_debug then
824: hr_utility.set_location('Entering:'|| l_proc, 1);
825: end if;
826: --
827: -- Check mandatory parameters have been set
828: --
876: hr_api.g_date)
877: <> nvl(p_date_of_birth,hr_api.g_date)) or
878: (NOT l_api_updating)) then
879: if g_debug then
880: hr_utility.set_location(l_proc, 2);
881: end if;
882: --
883: -- Perform Insert/Update checks
884: --
886: --
887: if p_date_of_birth is not null then
888: if p_date_of_birth > p_start_date then
889: -- Error: The Date of Birth is greater than the start date
890: hr_utility.set_message(801, 'HR_6523_PERSON_DOB_GT_START');
891: l_dob_st := true;
892: hr_utility.raise_error;
893: end if;
894: end if;
888: if p_date_of_birth > p_start_date then
889: -- Error: The Date of Birth is greater than the start date
890: hr_utility.set_message(801, 'HR_6523_PERSON_DOB_GT_START');
891: l_dob_st := true;
892: hr_utility.raise_error;
893: end if;
894: end if;
895: if g_debug then
896: hr_utility.set_location(l_proc, 3);
892: hr_utility.raise_error;
893: end if;
894: end if;
895: if g_debug then
896: hr_utility.set_location(l_proc, 3);
897: end if;
898: --
899: -- Calculate persons age
900: --
898: --
899: -- Calculate persons age
900: --
901: if g_debug then
902: hr_utility.set_location(p_effective_date,10);
903: end if;
904: if g_debug then
905: hr_utility.set_location(p_date_of_birth,10);
906: end if;
901: if g_debug then
902: hr_utility.set_location(p_effective_date,10);
903: end if;
904: if g_debug then
905: hr_utility.set_location(p_date_of_birth,10);
906: end if;
907: l_age := trunc(months_between(p_effective_date,p_date_of_birth)/12);
908: --
909: -- Find the minimum and maximum ages allowed for the business group
912: ,l_minimum_age
913: ,l_maximum_age);
914: --
915: if g_debug then
916: hr_utility.set_location(l_proc, 4);
917: end if;
918: --
919: -- Check that the persons age is between the minimum and maximum allowed
920: -- for the business group.
927: if l_age not between nvl(l_minimum_age,l_age) and
928: nvl(l_maximum_age,l_age) and
929: ( l_system_person_type <> 'OTHER' and l_system_person_type <>'%APL') then
930: -- Error: Employees age must be between 'min' and 'max'
931: hr_utility.set_message(801, 'HR_7426_EMP_AGE_ILLEGAL');
932: hr_utility.set_message_token('MIN',to_char(l_minimum_age));
933: hr_utility.set_message_token('MAX',to_char(l_maximum_age));
934: hr_utility.raise_error;
935: end if;
928: nvl(l_maximum_age,l_age) and
929: ( l_system_person_type <> 'OTHER' and l_system_person_type <>'%APL') then
930: -- Error: Employees age must be between 'min' and 'max'
931: hr_utility.set_message(801, 'HR_7426_EMP_AGE_ILLEGAL');
932: hr_utility.set_message_token('MIN',to_char(l_minimum_age));
933: hr_utility.set_message_token('MAX',to_char(l_maximum_age));
934: hr_utility.raise_error;
935: end if;
936: --
929: ( l_system_person_type <> 'OTHER' and l_system_person_type <>'%APL') then
930: -- Error: Employees age must be between 'min' and 'max'
931: hr_utility.set_message(801, 'HR_7426_EMP_AGE_ILLEGAL');
932: hr_utility.set_message_token('MIN',to_char(l_minimum_age));
933: hr_utility.set_message_token('MAX',to_char(l_maximum_age));
934: hr_utility.raise_error;
935: end if;
936: --
937: --Bug# 2273304 End Here
930: -- Error: Employees age must be between 'min' and 'max'
931: hr_utility.set_message(801, 'HR_7426_EMP_AGE_ILLEGAL');
932: hr_utility.set_message_token('MIN',to_char(l_minimum_age));
933: hr_utility.set_message_token('MAX',to_char(l_maximum_age));
934: hr_utility.raise_error;
935: end if;
936: --
937: --Bug# 2273304 End Here
938: --
944: --
945: -- Perform Insert check
946: --
947: if g_debug then
948: hr_utility.set_location(l_proc, 5);
949: end if;
950: --
951: end if;
952: --
957: and return_system_person_type(p_person_type_id, p_business_group_id) in
958: ('EMP','EX_EMP','EMP_APL','EX_EMP_APL')
959: and p_date_of_birth is null then
960: if g_debug then
961: hr_utility.set_location(l_proc, 6);
962: end if;
963: open csr_asg;
964: fetch csr_asg into l_exists;
965: if csr_asg%notfound then
965: if csr_asg%notfound then
966: close csr_asg;
967: p_dob_null_warning := TRUE;
968: if g_debug then
969: hr_utility.set_location(l_proc, 7);
970: end if;
971: else
972: close csr_asg;
973: hr_utility.set_message(801, 'HR_7950_PPM_NULL_DOB');
969: hr_utility.set_location(l_proc, 7);
970: end if;
971: else
972: close csr_asg;
973: hr_utility.set_message(801, 'HR_7950_PPM_NULL_DOB');
974: hr_utility.raise_error;
975: end if;
976: end if;
977: End if;
970: end if;
971: else
972: close csr_asg;
973: hr_utility.set_message(801, 'HR_7950_PPM_NULL_DOB');
974: hr_utility.raise_error;
975: end if;
976: end if;
977: End if;
978: -- Raise warning if date of birth is null and system person type is 'EMP'
983: p_dob_null_warning := TRUE;
984: end if;
985: end if;
986: if g_debug then
987: hr_utility.set_location(' Leaving:'|| l_proc, 8);
988: end if;
989: exception
990: when app_exception.application_exception then
991: If not l_dob_st then
992: if hr_multi_message.exception_add
993: (p_associated_column1 => 'PER_ALL_PEOPLE_F.DATE_OF_BIRTH'
994: ) then
995: if g_debug then
996: hr_utility.set_location(' Leaving:'||l_proc, 9);
997: end if;
998: raise;
999: end if;
1000: Else
1002: (p_associated_column1 => 'PER_ALL_PEOPLE_F.DATE_OF_BIRTH'
1003: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.START_DATE'
1004: ) then
1005: if g_debug then
1006: hr_utility.set_location(' Leaving:'||l_proc, 10);
1007: end if;
1008: raise;
1009: end if;
1010: End if;
1008: raise;
1009: end if;
1010: End if;
1011: if g_debug then
1012: hr_utility.set_location(' Leaving:'||l_proc,11);
1013: end if;
1014: end chk_date_of_birth;
1015: --
1016: -- ---------------------------------------------------------------------------
1066: l_api_updating boolean;
1067: --
1068: begin
1069: if g_debug then
1070: hr_utility.set_location('Entering:'|| l_proc, 10);
1071: end if;
1072: --
1073: -- Check mandatory parameters have been set
1074: --
1089: ,p_argument => 'validation_end_date'
1090: ,p_argument_value => p_validation_end_date
1091: );
1092: if g_debug then
1093: hr_utility.set_location(l_proc, 20);
1094: end if;
1095: --
1096: -- Only proceed with validation if:
1097: -- a) The current g_old_rec is current and
1109: or
1110: (NOT l_api_updating))
1111: then
1112: if g_debug then
1113: hr_utility.set_location(l_proc, 40);
1114: end if;
1115: --
1116: -- Check if marital status is set
1117: --
1130: ,p_lookup_code => p_marital_status
1131: )
1132: then
1133: --
1134: hr_utility.set_message(801, 'HR_7518_PER_M_STATUS_INVALID');
1135: hr_utility.raise_error;
1136: --
1137: end if;
1138: end if;
1131: )
1132: then
1133: --
1134: hr_utility.set_message(801, 'HR_7518_PER_M_STATUS_INVALID');
1135: hr_utility.raise_error;
1136: --
1137: end if;
1138: end if;
1139: if g_debug then
1136: --
1137: end if;
1138: end if;
1139: if g_debug then
1140: hr_utility.set_location(l_proc, 60);
1141: end if;
1142: --
1143: end if;
1144: if g_debug then
1141: end if;
1142: --
1143: end if;
1144: if g_debug then
1145: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1146: end if;
1147: exception
1148: when app_exception.application_exception then
1149: if hr_multi_message.exception_add
1149: if hr_multi_message.exception_add
1150: (p_associated_column1 => 'PER_ALL_PEOPLE_F.MARITAL_STATUS'
1151: ) then
1152: if g_debug then
1153: hr_utility.set_location(' Leaving:'||l_proc, 80);
1154: end if;
1155: raise;
1156: end if;
1157: if g_debug then
1154: end if;
1155: raise;
1156: end if;
1157: if g_debug then
1158: hr_utility.set_location(' Leaving:'||l_proc,90);
1159: end if;
1160: end chk_marital_status;
1161: --
1162: -- ---------------------------------------------------------------------------
1204: where party_id = p_party_id;
1205: --
1206: begin
1207: if g_debug then
1208: hr_utility.set_location('Entering:'|| l_proc, 10);
1209: end if;
1210: --
1211: -- Check mandatory parameters have been set
1212: --
1216: ,p_argument_value => p_effective_date
1217: );
1218: --
1219: if g_debug then
1220: hr_utility.set_location(l_proc, 20);
1221: end if;
1222: --
1223: -- Only proceed with validation if:
1224: -- a) The current g_old_rec is current and
1236: or
1237: (NOT l_api_updating))
1238: then
1239: if g_debug then
1240: hr_utility.set_location(l_proc, 40);
1241: end if;
1242: --
1243: -- Check if party is set
1244: --
1250: fetch c1 into l_exists;
1251: if c1%notfound then
1252: --
1253: close c1;
1254: hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
1255: hr_utility.raise_error;
1256: --
1257: end if;
1258: --
1251: if c1%notfound then
1252: --
1253: close c1;
1254: hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
1255: hr_utility.raise_error;
1256: --
1257: end if;
1258: --
1259: close c1;
1260: --
1261: end if;
1262: --
1263: if g_debug then
1264: hr_utility.set_location(l_proc, 60);
1265: end if;
1266: --
1267: end if;
1268: if g_debug then
1265: end if;
1266: --
1267: end if;
1268: if g_debug then
1269: hr_utility.set_location(' Leaving:'|| l_proc, 70);
1270: end if;
1271: exception
1272: when app_exception.application_exception then
1273: if hr_multi_message.exception_add
1273: if hr_multi_message.exception_add
1274: (p_associated_column1 => 'PER_ALL_PEOPLE_F.PARTY_ID'
1275: ) then
1276: if g_debug then
1277: hr_utility.set_location(' Leaving:'||l_proc, 80);
1278: end if;
1279: raise;
1280: end if;
1281: if g_debug then
1278: end if;
1279: raise;
1280: end if;
1281: if g_debug then
1282: hr_utility.set_location(' Leaving:'||l_proc,90);
1283: end if;
1284: end chk_party_id;
1285: --
1286: -- ---------------------------------------------------------------------------
1337: l_api_updating boolean;
1338: --
1339: begin
1340: if g_debug then
1341: hr_utility.set_location('Entering:'|| l_proc, 10);
1342: end if;
1343: --
1344: -- Check mandatory parameters have been set
1345: --
1376: or
1377: (NOT l_api_updating))
1378: then
1379: if g_debug then
1380: hr_utility.set_location(l_proc, 40);
1381: end if;
1382: --
1383: -- Check if nationality is set
1384: --
1397: ,p_lookup_code => p_nationality
1398: )
1399: then
1400: --
1401: hr_utility.set_message(801, 'HR_7522_PER_NATION_INVALID');
1402: hr_utility.raise_error;
1403: --
1404: end if;
1405: if g_debug then
1398: )
1399: then
1400: --
1401: hr_utility.set_message(801, 'HR_7522_PER_NATION_INVALID');
1402: hr_utility.raise_error;
1403: --
1404: end if;
1405: if g_debug then
1406: hr_utility.set_location(l_proc, 50);
1402: hr_utility.raise_error;
1403: --
1404: end if;
1405: if g_debug then
1406: hr_utility.set_location(l_proc, 50);
1407: end if;
1408: --
1409: end if;
1410: end if;
1408: --
1409: end if;
1410: end if;
1411: if g_debug then
1412: hr_utility.set_location(' Leaving:'|| l_proc, 60);
1413: end if;
1414: exception
1415: when app_exception.application_exception then
1416: if hr_multi_message.exception_add
1416: if hr_multi_message.exception_add
1417: (p_associated_column1 => 'PER_ALL_PEOPLE_F.NATIONALITY'
1418: ) then
1419: if g_debug then
1420: hr_utility.set_location(' Leaving:'||l_proc, 70);
1421: end if;
1422: raise;
1423: end if;
1424: if g_debug then
1421: end if;
1422: raise;
1423: end if;
1424: if g_debug then
1425: hr_utility.set_location(' Leaving:'||l_proc,80);
1426: end if;
1427: end chk_nationality;
1428:
1429: -- start for bug 6241572, a new overloaded procedure is created with
1525: l_prof_val varchar2(50) ; -- #4069243
1526: --
1527: begin
1528: if g_debug then
1529: hr_utility.set_location('Entering:'|| l_proc, 1);
1530: end if;
1531: --
1532: -- Check mandatory parameters have been set
1533: --
1558: <> nvl(p_national_identifier,hr_api.g_varchar2)) or
1559: (NOT l_api_updating)) then
1560: --
1561: if g_debug then
1562: hr_utility.set_location(l_proc, 2);
1563: end if;
1564: --
1565: -- If national identifier is not null then
1566: -- Call process to validate national identifier
1591: --
1592: if fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'NONE' and fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'WARN' then
1593: -- #932657
1594: if l_valid_ni <> p_national_identifier then
1595: hr_utility.set_message(800, 'HR_52765_PER_INV_NI_FORMAT');
1596: hr_utility.set_message_token('P_N_I', p_national_identifier);
1597: hr_utility.set_message_token('RETURNED_VALUE', l_valid_ni); --3807899
1598: hr_utility.raise_error;
1599: end if;
1592: if fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'NONE' and fnd_profile.value('PER_NATIONAL_IDENTIFIER_VALIDATION') <> 'WARN' then
1593: -- #932657
1594: if l_valid_ni <> p_national_identifier then
1595: hr_utility.set_message(800, 'HR_52765_PER_INV_NI_FORMAT');
1596: hr_utility.set_message_token('P_N_I', p_national_identifier);
1597: hr_utility.set_message_token('RETURNED_VALUE', l_valid_ni); --3807899
1598: hr_utility.raise_error;
1599: end if;
1600: end if;
1593: -- #932657
1594: if l_valid_ni <> p_national_identifier then
1595: hr_utility.set_message(800, 'HR_52765_PER_INV_NI_FORMAT');
1596: hr_utility.set_message_token('P_N_I', p_national_identifier);
1597: hr_utility.set_message_token('RETURNED_VALUE', l_valid_ni); --3807899
1598: hr_utility.raise_error;
1599: end if;
1600: end if;
1601: -- 4069243 start
1594: if l_valid_ni <> p_national_identifier then
1595: hr_utility.set_message(800, 'HR_52765_PER_INV_NI_FORMAT');
1596: hr_utility.set_message_token('P_N_I', p_national_identifier);
1597: hr_utility.set_message_token('RETURNED_VALUE', l_valid_ni); --3807899
1598: hr_utility.raise_error;
1599: end if;
1600: end if;
1601: -- 4069243 start
1602: l_prof_val := fnd_profile.value ('PER_NI_UNIQUE_ERROR_WARNING');
1610: end if;
1611: --
1612: end if;
1613: if g_debug then
1614: hr_utility.set_location(' Leaving:'|| l_proc, 3);
1615: end if;
1616: exception
1617: when app_exception.application_exception then
1618: if hr_multi_message.exception_add
1618: if hr_multi_message.exception_add
1619: (p_associated_column1 => 'PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER'
1620: ) then
1621: if g_debug then
1622: hr_utility.set_location(' Leaving:'||l_proc, 4);
1623: end if;
1624: raise;
1625: end if;
1626: if g_debug then
1623: end if;
1624: raise;
1625: end if;
1626: if g_debug then
1627: hr_utility.set_location(' Leaving:'||l_proc,5);
1628: end if;
1629: end chk_national_identifier;
1630: --
1631: -- ---------------------------------------------------------------------------
1758: --
1759: begin
1760: --
1761: if g_debug then
1762: hr_utility.set_location('Entering '||l_proc,10);
1763: end if;
1764: --
1765: -- Assume we have not changed the system_person_type, so set return
1766: -- variable to FALSE.
1771: --
1772: if p_new_person_type_id <> p_old_person_type_id then
1773: --
1774: if g_debug then
1775: hr_utility.set_location(l_proc, 20);
1776: end if;
1777: --
1778: -- Get the system_person_type for the 'new' person_type_id
1779: --
1781: (p_person_type_Id =>p_new_person_type_id
1782: ,p_business_group_id =>p_business_group_id
1783: );
1784: if g_debug then
1785: hr_utility.set_location(l_proc, 30);
1786: end if;
1787: --
1788: -- Get the system_person_type for the 'old' person_type_id
1789: --
1807: l_new_system_person_type = 'EMP')
1808: ) then
1809: --
1810: if g_debug then
1811: hr_utility.set_location(l_proc, 40);
1812: end if;
1813: --
1814: l_return_status := true;
1815: end if;
1813: --
1814: l_return_status := true;
1815: end if;
1816: if g_debug then
1817: hr_utility.set_location(l_proc, 50);
1818: end if;
1819: end if;
1820: if g_debug then
1821: hr_utility.set_location(' Leaving '||l_proc, 60);
1817: hr_utility.set_location(l_proc, 50);
1818: end if;
1819: end if;
1820: if g_debug then
1821: hr_utility.set_location(' Leaving '||l_proc, 60);
1822: end if;
1823: return l_return_status;
1824: end emp_sys_per_type_change;
1825: --
1835: l_proc varchar2(30) := 'emp_ni_change';
1836: --
1837: begin
1838: if g_debug then
1839: hr_utility.set_location('Entering '||l_proc,10);
1840: end if;
1841: --
1842: if p_gen_method = 'N' and
1843: p_new_ni <> p_old_ni then
1841: --
1842: if p_gen_method = 'N' and
1843: p_new_ni <> p_old_ni then
1844: if g_debug then
1845: hr_utility.set_location(l_proc,20);
1846: end if;
1847: l_return_status := TRUE;
1848: end if;
1849: if g_debug then
1846: end if;
1847: l_return_status := TRUE;
1848: end if;
1849: if g_debug then
1850: hr_utility.set_location(' Leaving '||l_proc,30);
1851: end if;
1852: return l_return_status;
1853: end emp_ni_change;
1854: --
1853: end emp_ni_change;
1854: --
1855: begin
1856: if g_debug then
1857: hr_utility.set_location('Entering:'|| l_proc, 10);
1858: end if;
1859: if hr_multi_message.no_exclusive_error
1860: (p_check_column1 => 'PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER'
1861: )
1899: open csr_gen_method;
1900: fetch csr_gen_method into l_gen_method;
1901: close csr_gen_method;
1902: if g_debug then
1903: hr_utility.set_location(l_proc, 20);
1904: end if;
1905: --
1906: -- Need to validate/generate the employee number if the employee number
1907: -- has changed or if the system person type has changed in such a way
1922: or
1923: (NOT l_api_updating)) then
1924: --
1925: if g_debug then
1926: hr_utility.set_location(l_proc, 30);
1927: end if;
1928: --
1929: -- If we are updating and the employee number has actually changed then
1930: -- ensure the number generation method is not automatic.
1939: and (PER_BG_NUMBERING_METHOD_PKG.Get_PersonNumber_Formula
1940: ('EMP',p_effective_date) is not null))
1941: and per_per_shd.g_old_rec.employee_number is not null)
1942: then
1943: hr_utility.set_message(801, 'HR_51239_PER_INV_EMP_UPD');
1944: hr_utility.raise_error;
1945: end if;
1946: if g_debug then
1947: hr_utility.set_location(l_proc, 50);
1940: ('EMP',p_effective_date) is not null))
1941: and per_per_shd.g_old_rec.employee_number is not null)
1942: then
1943: hr_utility.set_message(801, 'HR_51239_PER_INV_EMP_UPD');
1944: hr_utility.raise_error;
1945: end if;
1946: if g_debug then
1947: hr_utility.set_location(l_proc, 50);
1948: end if;
1943: hr_utility.set_message(801, 'HR_51239_PER_INV_EMP_UPD');
1944: hr_utility.raise_error;
1945: end if;
1946: if g_debug then
1947: hr_utility.set_location(l_proc, 50);
1948: end if;
1949: --
1950: -- Perform employee number validation as required by number generation
1951: -- method for the business group, and ensure that it is
1954: if return_system_person_type(p_person_type_id, p_business_group_id) in
1955: ('EMP','EX_EMP', 'EMP_APL','EX_EMP_APL') then
1956: --
1957: if g_debug then
1958: hr_utility.set_location(l_proc, 60);
1959: end if;
1960: --
1961: -- If generation method is 'A' ensure employee number IN is set null
1962: --
1968: and (PER_BG_NUMBERING_METHOD_PKG.Get_PersonNumber_Formula
1969: ('EMP',p_effective_date) is not null)))
1970: then
1971: --
1972: hr_utility.set_message(801,'HR_51240_PER_EMP_NUM_NOT_NULL');
1973: hr_utility.raise_error;
1974: end if;
1975: if g_debug then
1976: hr_utility.set_location(l_proc, 70);
1969: ('EMP',p_effective_date) is not null)))
1970: then
1971: --
1972: hr_utility.set_message(801,'HR_51240_PER_EMP_NUM_NOT_NULL');
1973: hr_utility.raise_error;
1974: end if;
1975: if g_debug then
1976: hr_utility.set_location(l_proc, 70);
1977: end if;
1972: hr_utility.set_message(801,'HR_51240_PER_EMP_NUM_NOT_NULL');
1973: hr_utility.raise_error;
1974: end if;
1975: if g_debug then
1976: hr_utility.set_location(l_proc, 70);
1977: end if;
1978: --
1979: -- If generation method is 'N' ensure employee number IN is set null
1980: -- on insert.
1982: if l_gen_method = 'N' and
1983: NOT l_api_updating and
1984: p_employee_number <> p_national_identifier then
1985: --
1986: hr_utility.set_message(801,'HR_51241_PER_EMP_NUM_NOT_NULL');
1987: l_emp_ni := true;
1988: hr_utility.raise_error;
1989: end if;
1990: if g_debug then
1984: p_employee_number <> p_national_identifier then
1985: --
1986: hr_utility.set_message(801,'HR_51241_PER_EMP_NUM_NOT_NULL');
1987: l_emp_ni := true;
1988: hr_utility.raise_error;
1989: end if;
1990: if g_debug then
1991: hr_utility.set_location(l_proc, 80);
1992: end if;
1987: l_emp_ni := true;
1988: hr_utility.raise_error;
1989: end if;
1990: if g_debug then
1991: hr_utility.set_location(l_proc, 80);
1992: end if;
1993: --
1994: -- If generation method is 'N' ensure national identifier IN is not null
1995: --
1995: --
1996: if l_gen_method = 'N' and
1997: p_national_identifier is null then
1998: --
1999: hr_utility.set_message(801,'HR_51242_PER_NAT_ID_NULL');
2000: l_emp_ni := true;
2001: hr_utility.raise_error;
2002: end if;
2003: if g_debug then
1997: p_national_identifier is null then
1998: --
1999: hr_utility.set_message(801,'HR_51242_PER_NAT_ID_NULL');
2000: l_emp_ni := true;
2001: hr_utility.raise_error;
2002: end if;
2003: if g_debug then
2004: hr_utility.set_location(l_proc, 90);
2005: end if;
2000: l_emp_ni := true;
2001: hr_utility.raise_error;
2002: end if;
2003: if g_debug then
2004: hr_utility.set_location(l_proc, 90);
2005: end if;
2006: --
2007: -- Call number generation routine.
2008: --
2036: ,p_start_date => p_effective_date);
2037: end if;
2038: --
2039: if g_debug then
2040: hr_utility.set_location(l_proc, 100);
2041: end if;
2042: --
2043: -- Check uniqueness of generated number if not generated automatically
2044: --
2044: --
2045: if l_gen_method <> 'A' then
2046: --
2047: if g_debug then
2048: hr_utility.set_location(l_proc, 104);
2049: end if;
2050: --
2051: hr_person.validate_unique_number
2052: (p_person_id => p_person_id
2058: ,p_current_applicant => 'N'
2059: ,p_current_npw => 'N');
2060: --
2061: if g_debug then
2062: hr_utility.set_location(l_proc, 108);
2063: end if;
2064: --
2065: end if;
2066: --
2064: --
2065: end if;
2066: --
2067: if g_debug then
2068: hr_utility.set_location(l_proc, 110);
2069: end if;
2070: --
2071: else
2072: --
2073: -- System person type is not 'EMP','EX_EMP','EMP_APL' or 'EX_EMP_APL'
2074: -- so employee number must be null
2075: --
2076: if g_debug then
2077: hr_utility.set_location(l_proc, 120);
2078: end if;
2079: --
2080: if p_employee_number is not null then
2081: -- Error: You cannot enter an employee number for this person type
2078: end if;
2079: --
2080: if p_employee_number is not null then
2081: -- Error: You cannot enter an employee number for this person type
2082: hr_utility.set_message(801, 'HR_7523_PER_EMP_NO_NOT_NULL');
2083: hr_utility.raise_error;
2084: end if;
2085: --
2086: if g_debug then
2079: --
2080: if p_employee_number is not null then
2081: -- Error: You cannot enter an employee number for this person type
2082: hr_utility.set_message(801, 'HR_7523_PER_EMP_NO_NOT_NULL');
2083: hr_utility.raise_error;
2084: end if;
2085: --
2086: if g_debug then
2087: hr_utility.set_location(l_proc, 130);
2083: hr_utility.raise_error;
2084: end if;
2085: --
2086: if g_debug then
2087: hr_utility.set_location(l_proc, 130);
2088: end if;
2089: --
2090: end if;
2091: end if;
2090: end if;
2091: end if;
2092: end if;
2093: if g_debug then
2094: hr_utility.set_location(' Leaving:'|| l_proc, 140);
2095: end if;
2096: exception
2097: when app_exception.application_exception then
2098: -- if error is not because of national identifier
2101: if hr_multi_message.exception_add
2102: (p_associated_column1 => 'PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER'
2103: ) then
2104: if g_debug then
2105: hr_utility.set_location(' Leaving:'||l_proc, 150);
2106: end if;
2107: raise;
2108: end if;
2109: else
2111: (p_associated_column1 => 'PER_ALL_PEOPLE_F.EMPLOYEE_NUMBER'
2112: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER'
2113: ) then
2114: if g_debug then
2115: hr_utility.set_location(' Leaving:'||l_proc, 160);
2116: end if;
2117: raise;
2118: end if;
2119: end if;
2117: raise;
2118: end if;
2119: end if;
2120: if g_debug then
2121: hr_utility.set_location(' Leaving:'||l_proc,170);
2122: end if;
2123: end chk_employee_number;
2124: --
2125: -- ---------------------------------------------------------------------------
2240: l_proc varchar2(30) := 'npw_ni_change';
2241: --
2242: begin
2243: if g_debug then
2244: hr_utility.set_location('Entering '||l_proc,10);
2245: end if;
2246: --
2247: if p_gen_method = 'N' and
2248: p_new_ni <> p_old_ni then
2246: --
2247: if p_gen_method = 'N' and
2248: p_new_ni <> p_old_ni then
2249: if g_debug then
2250: hr_utility.set_location(l_proc,20);
2251: end if;
2252: l_return_status := TRUE;
2253: end if;
2254: if g_debug then
2251: end if;
2252: l_return_status := TRUE;
2253: end if;
2254: if g_debug then
2255: hr_utility.set_location(' Leaving '||l_proc,30);
2256: end if;
2257: return l_return_status;
2258: end npw_ni_change;
2259: --
2258: end npw_ni_change;
2259: --
2260: begin
2261: if g_debug then
2262: hr_utility.set_location('Entering:'|| l_proc, 10);
2263: end if;
2264: if hr_multi_message.no_exclusive_error
2265: (p_check_column1 => 'PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER'
2266: )
2298: open csr_gen_method;
2299: fetch csr_gen_method into l_gen_method;
2300: close csr_gen_method;
2301: if g_debug then
2302: hr_utility.set_location(l_proc, 20);
2303: end if;
2304: --
2305: --
2306: -- Need to validate/generate the npw number if the npw number
2320: or
2321: (NOT l_api_updating)) then
2322: --
2323: if g_debug then
2324: hr_utility.set_location(l_proc, 30);
2325: end if;
2326: --
2327: -- If we are updating and the npw number has actually changed then
2328: -- ensure the number generation method is not automatic.
2330: if l_api_updating and
2331: nvl(per_per_shd.g_old_rec.npw_number, hr_api.g_varchar2) <>
2332: nvl(p_npw_number, hr_api.g_varchar2) and
2333: l_gen_method = 'A' then
2334: hr_utility.set_message(800, 'HR_289657_PER_INV_CWK_UPD');
2335: hr_utility.raise_error;
2336: end if;
2337: if g_debug then
2338: hr_utility.set_location(l_proc, 50);
2331: nvl(per_per_shd.g_old_rec.npw_number, hr_api.g_varchar2) <>
2332: nvl(p_npw_number, hr_api.g_varchar2) and
2333: l_gen_method = 'A' then
2334: hr_utility.set_message(800, 'HR_289657_PER_INV_CWK_UPD');
2335: hr_utility.raise_error;
2336: end if;
2337: if g_debug then
2338: hr_utility.set_location(l_proc, 50);
2339: end if;
2334: hr_utility.set_message(800, 'HR_289657_PER_INV_CWK_UPD');
2335: hr_utility.raise_error;
2336: end if;
2337: if g_debug then
2338: hr_utility.set_location(l_proc, 50);
2339: end if;
2340: --
2341: -- Perform npw number validation as required by number generation
2342: -- method for the business group, and ensure that it is
2344: --
2345: if p_current_npw_flag = 'Y' then
2346: --
2347: if g_debug then
2348: hr_utility.set_location(l_proc, 60);
2349: end if;
2350: --
2351: -- If generation method is not specified then error: ct. may not have upgraded
2352: -- BG info
2351: -- If generation method is not specified then error: ct. may not have upgraded
2352: -- BG info
2353: if l_gen_method is null then
2354: --
2355: hr_utility.set_message(800,'PER_289634_CWK_GEN_NOT_NULL');
2356: hr_utility.raise_error;
2357: end if;
2358: if g_debug then
2359: hr_utility.set_location(l_proc, 60);
2352: -- BG info
2353: if l_gen_method is null then
2354: --
2355: hr_utility.set_message(800,'PER_289634_CWK_GEN_NOT_NULL');
2356: hr_utility.raise_error;
2357: end if;
2358: if g_debug then
2359: hr_utility.set_location(l_proc, 60);
2360: end if;
2355: hr_utility.set_message(800,'PER_289634_CWK_GEN_NOT_NULL');
2356: hr_utility.raise_error;
2357: end if;
2358: if g_debug then
2359: hr_utility.set_location(l_proc, 60);
2360: end if;
2361: --
2362: -- If generation method is 'A' ensure npw number IN is set null
2363: -- or has not changed from its previous value (fixes 2876274)
2365: if l_gen_method = 'A' and
2366: p_npw_number is not null and
2367: p_npw_number <> nvl(per_per_shd.g_old_rec.npw_number,hr_api.g_varchar2) then
2368: --
2369: hr_utility.set_message(800,'PER_289635_CWK_NUM_NOT_NULL');
2370: hr_utility.raise_error;
2371: end if;
2372: if g_debug then
2373: hr_utility.set_location(l_proc, 70);
2366: p_npw_number is not null and
2367: p_npw_number <> nvl(per_per_shd.g_old_rec.npw_number,hr_api.g_varchar2) then
2368: --
2369: hr_utility.set_message(800,'PER_289635_CWK_NUM_NOT_NULL');
2370: hr_utility.raise_error;
2371: end if;
2372: if g_debug then
2373: hr_utility.set_location(l_proc, 70);
2374: end if;
2369: hr_utility.set_message(800,'PER_289635_CWK_NUM_NOT_NULL');
2370: hr_utility.raise_error;
2371: end if;
2372: if g_debug then
2373: hr_utility.set_location(l_proc, 70);
2374: end if;
2375: --
2376: -- If generation method is 'N' ensure npw number IN is set null
2377: -- on insert.
2379: if l_gen_method = 'N' and
2380: NOT l_api_updating and
2381: p_npw_number <> p_national_identifier then
2382: --
2383: hr_utility.set_message(800,'PER_289636_CWK_NUM_NOT_NULL');
2384: l_npw_ni := true;
2385: hr_utility.raise_error;
2386: end if;
2387: if g_debug then
2381: p_npw_number <> p_national_identifier then
2382: --
2383: hr_utility.set_message(800,'PER_289636_CWK_NUM_NOT_NULL');
2384: l_npw_ni := true;
2385: hr_utility.raise_error;
2386: end if;
2387: if g_debug then
2388: hr_utility.set_location(l_proc, 80);
2389: end if;
2384: l_npw_ni := true;
2385: hr_utility.raise_error;
2386: end if;
2387: if g_debug then
2388: hr_utility.set_location(l_proc, 80);
2389: end if;
2390: --
2391: -- If generation method is 'N' ensure national identifier IN is not null
2392: --
2392: --
2393: if l_gen_method = 'N' and
2394: p_national_identifier is null then
2395: --
2396: hr_utility.set_message(800,'PER_289637_CWK_NAT_ID_NULL');
2397: l_npw_ni := true;
2398: hr_utility.raise_error;
2399: end if;
2400: if g_debug then
2394: p_national_identifier is null then
2395: --
2396: hr_utility.set_message(800,'PER_289637_CWK_NAT_ID_NULL');
2397: l_npw_ni := true;
2398: hr_utility.raise_error;
2399: end if;
2400: if g_debug then
2401: hr_utility.set_location(l_proc, 90);
2402: end if;
2397: l_npw_ni := true;
2398: hr_utility.raise_error;
2399: end if;
2400: if g_debug then
2401: hr_utility.set_location(l_proc, 90);
2402: end if;
2403: --
2404: -- Call number generation routine.
2405: --
2418: ,p_start_date => p_effective_date);
2419:
2420: --
2421: if g_debug then
2422: hr_utility.set_location(l_proc, 100);
2423: end if;
2424: --
2425: -- Check uniqueness of generated number if not generated automatically
2426: --
2426: --
2427: if l_gen_method <> 'A' then
2428: --
2429: if g_debug then
2430: hr_utility.set_location(l_proc, 104);
2431: end if;
2432: --
2433: hr_person.validate_unique_number
2434: (p_person_id => p_person_id
2440: ,p_current_applicant => 'N'
2441: ,p_current_npw => 'Y');
2442: --
2443: if g_debug then
2444: hr_utility.set_location(l_proc, 108);
2445: end if;
2446: --
2447: end if;
2448: --
2446: --
2447: end if;
2448: --
2449: if g_debug then
2450: hr_utility.set_location(l_proc, 110);
2451: end if;
2452: --
2453: else
2454: --
2454: --
2455: -- current_npw_flag is null, so npw number must be null
2456: --
2457: if g_debug then
2458: hr_utility.set_location(l_proc, 120);
2459: end if;
2460: --
2461: if p_npw_number is not null then
2462: -- Error: You cannot enter an npw number for this person type
2459: end if;
2460: --
2461: if p_npw_number is not null then
2462: -- Error: You cannot enter an npw number for this person type
2463: hr_utility.set_message(800, 'PER_289638_CWK_NUM_NOT_NULL');
2464: hr_utility.raise_error;
2465: end if;
2466: --
2467: if g_debug then
2460: --
2461: if p_npw_number is not null then
2462: -- Error: You cannot enter an npw number for this person type
2463: hr_utility.set_message(800, 'PER_289638_CWK_NUM_NOT_NULL');
2464: hr_utility.raise_error;
2465: end if;
2466: --
2467: if g_debug then
2468: hr_utility.set_location(l_proc, 130);
2464: hr_utility.raise_error;
2465: end if;
2466: --
2467: if g_debug then
2468: hr_utility.set_location(l_proc, 130);
2469: end if;
2470: --
2471: end if;
2472: end if;
2471: end if;
2472: end if;
2473: end if;
2474: if g_debug then
2475: hr_utility.set_location(' Leaving:'|| l_proc, 140);
2476: end if;
2477: exception
2478: when app_exception.application_exception then
2479: -- if error is not because of national identifier
2482: if hr_multi_message.exception_add
2483: (p_associated_column1 => 'PER_ALL_PEOPLE_F.NPW_NUMBER'
2484: ) then
2485: if g_debug then
2486: hr_utility.set_location(' Leaving:'||l_proc, 150);
2487: end if;
2488: raise;
2489: end if;
2490: else
2492: (p_associated_column1 => 'PER_ALL_PEOPLE_F.NPW_NUMBER'
2493: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER'
2494: ) then
2495: if g_debug then
2496: hr_utility.set_location(' Leaving:'||l_proc, 160);
2497: end if;
2498: raise;
2499: end if;
2500: end if;
2498: raise;
2499: end if;
2500: end if;
2501: if g_debug then
2502: hr_utility.set_location(' Leaving:'||l_proc,170);
2503: end if;
2504: end chk_npw_number;
2505: --
2506: -- ---------------------------------------------------------------------------
2555: l_api_updating boolean;
2556: --
2557: begin
2558: if g_debug then
2559: hr_utility.set_location('Entering:'|| l_proc, 10);
2560: end if;
2561: --
2562: -- Check mandatory parameters have been set
2563: --
2578: ,p_argument => 'validation_end_date'
2579: ,p_argument_value => p_validation_end_date
2580: );
2581: if g_debug then
2582: hr_utility.set_location(l_proc, 20);
2583: end if;
2584: --
2585: -- Only proceed with validation if:
2586: -- a) The current g_old_rec is current and
2592: ,p_effective_date => p_effective_date
2593: ,p_object_version_number => p_object_version_number
2594: );
2595: if g_debug then
2596: hr_utility.set_location(l_proc, 30);
2597: end if;
2598: --
2599: if ((l_api_updating
2600: and nvl(per_per_shd.g_old_rec.expense_check_send_to_address,
2603: or
2604: (NOT l_api_updating))
2605: then
2606: if g_debug then
2607: hr_utility.set_location(l_proc, 40);
2608: end if;
2609: --
2610: -- Check if expense check send to address is set
2611: --
2624: ,p_lookup_code => p_expense_check_send_to_addres
2625: )
2626: then
2627: --
2628: hr_utility.set_message(801, 'HR_51251_PER_CHECK_SEND_ADR');
2629: hr_utility.raise_error;
2630: --
2631: end if;
2632: if g_debug then
2625: )
2626: then
2627: --
2628: hr_utility.set_message(801, 'HR_51251_PER_CHECK_SEND_ADR');
2629: hr_utility.raise_error;
2630: --
2631: end if;
2632: if g_debug then
2633: hr_utility.set_location(l_proc, 50);
2629: hr_utility.raise_error;
2630: --
2631: end if;
2632: if g_debug then
2633: hr_utility.set_location(l_proc, 50);
2634: end if;
2635: --
2636: end if;
2637: if g_debug then
2634: end if;
2635: --
2636: end if;
2637: if g_debug then
2638: hr_utility.set_location(l_proc, 60);
2639: end if;
2640: --
2641: end if;
2642: if g_debug then
2639: end if;
2640: --
2641: end if;
2642: if g_debug then
2643: hr_utility.set_location(' Leaving:'|| l_proc, 70);
2644: end if;
2645: exception
2646: when app_exception.application_exception then
2647: if hr_multi_message.exception_add
2647: if hr_multi_message.exception_add
2648: (p_associated_column1 => 'PER_ALL_PEOPLE_F.EXPENSE_CHECK_SEND_TO_ADDRESS'
2649: ) then
2650: if g_debug then
2651: hr_utility.set_location(' Leaving:'||l_proc, 80);
2652: end if;
2653: raise;
2654: end if;
2655: if g_debug then
2652: end if;
2653: raise;
2654: end if;
2655: if g_debug then
2656: hr_utility.set_location(' Leaving:'||l_proc,90);
2657: end if;
2658:
2659: end chk_expense_check_send_to_addr;
2660: --
2739: l_lookup_type varchar2(30);
2740: --
2741: begin
2742: if g_debug then
2743: hr_utility.set_location('Entering:'|| l_proc, 10);
2744: end if;
2745: --
2746: -- Check mandatory parameters have been set
2747: --
2762: ,p_argument => 'validation_end_date'
2763: ,p_argument_value => p_validation_end_date
2764: );
2765: if g_debug then
2766: hr_utility.set_location(l_proc, 20);
2767: end if;
2768: --
2769: -- Only proceed with validation if:
2770: -- a) The current g_old_rec is current and
2777: ,p_effective_date => p_effective_date
2778: ,p_object_version_number => p_object_version_number
2779: );
2780: if g_debug then
2781: hr_utility.set_location(l_proc, 30);
2782: end if;
2783: --
2784: if ((l_api_updating
2785: and nvl(per_per_shd.g_old_rec.sex, hr_api.g_varchar2)
2790: or
2791: (NOT l_api_updating))
2792: then
2793: if g_debug then
2794: hr_utility.set_location(l_proc, 40);
2795: end if;
2796: --
2797: -- Check if title is set
2798: --
2811: ,p_lookup_code => p_title
2812: )
2813: then
2814: --
2815: hr_utility.set_message(801, 'HR_7512_PER_TITLE_INVALID');
2816: hr_multi_message.add
2817: (p_associated_column1 => 'PER_ALL_PEOPLE_F.TITLE'
2818: );
2819: --
2818: );
2819: --
2820: end if;
2821: if g_debug then
2822: hr_utility.set_location(l_proc, 50);
2823: end if;
2824: --
2825: end if;
2826: --
2841: ,p_lookup_code => p_sex
2842: )
2843: then
2844: --
2845: hr_utility.set_message(801, 'HR_7511_PER_SEX_INVALID');
2846: hr_multi_message.add
2847: (p_associated_column1 => 'PER_ALL_PEOPLE_F.SEX'
2848: );
2849: --
2848: );
2849: --
2850: end if;
2851: if g_debug then
2852: hr_utility.set_location(l_proc, 40);
2853: end if;
2854: --
2855: -- Check if title is set
2856: --
2860: --
2861: If p_sex = 'M' and
2862: p_title in ('MISS','MRS.','MS.') then
2863: -- Error: Values for Sex and Title are inconsistent.
2864: hr_utility.set_message(801, 'HR_6527_PERSON_SEX_AND_TITLE');
2865: hr_multi_message.add
2866: (p_associated_column1 => 'PER_ALL_PEOPLE_F.TITLE'
2867: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.SEX'
2868: );
2867: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.SEX'
2868: );
2869: end if;
2870: if g_debug then
2871: hr_utility.set_location(l_proc, 70);
2872: end if;
2873: --
2874: If p_sex = 'F' and
2875: p_title = 'MR.' then
2873: --
2874: If p_sex = 'F' and
2875: p_title = 'MR.' then
2876: -- Error: Values for Sex and Title are inconsistent.
2877: hr_utility.set_message(801, 'HR_6527_PERSON_SEX_AND_TITLE');
2878: hr_multi_message.add
2879: (p_associated_column1 => 'PER_ALL_PEOPLE_F.TITLE'
2880: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.SEX'
2881: );
2880: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.SEX'
2881: );
2882: end if;
2883: if g_debug then
2884: hr_utility.set_location(l_proc, 80);
2885: end if;
2886: --
2887: end if;
2888: else
2891: --
2892: if instr(return_system_person_type(p_person_type_id,
2893: p_business_group_id),'EMP') <> 0 then
2894: -- Error: You must enter sex for an employee.
2895: hr_utility.set_message(801, 'HR_6524_EMP_MANDATORY_SEX');
2896: hr_multi_message.add
2897: (p_associated_column1 => 'PER_ALL_PEOPLE_F.SEX'
2898: );
2899: end if;
2897: (p_associated_column1 => 'PER_ALL_PEOPLE_F.SEX'
2898: );
2899: end if;
2900: if g_debug then
2901: hr_utility.set_location(l_proc, 90);
2902: end if;
2903: --
2904: end if;
2905: if g_debug then
2902: end if;
2903: --
2904: end if;
2905: if g_debug then
2906: hr_utility.set_location(' Leaving:'|| l_proc, 130);
2907: end if;
2908: end if;
2909: end chk_sex_title;
2910: --
2961: --
2962: begin
2963: if g_debug then
2964: l_proc := g_package||'chk_town_of_birth';
2965: hr_utility.set_location('Entering:'|| l_proc, 1);
2966: end if;
2967: if p_town_of_birth IS NOT NULL then
2968: --
2969: -- Only proceed with validation if:
2975: ((p_person_id is not null) and
2976: nvl(p_town_of_birth,hr_api.g_varchar2) <> nvl(per_per_shd.g_old_rec.town_of_birth,hr_api.g_varchar2))) then
2977: --
2978: if g_debug then
2979: hr_utility.set_location(l_proc, 2);
2980: end if;
2981: --
2982: open csr_plfi;
2983: fetch csr_plfi into l_lookup_type;
2993: ,p_lookup_code => p_town_of_birth
2994: )
2995: then
2996: -- Error: Invalid town lookup_value has been passed.
2997: hr_utility.set_message_token('TOWN_CODE', p_town_of_birth);
2998: hr_utility.set_message(800, 'PER_52619_TOWN_NOT_FOUND)');
2999: hr_utility.raise_error;
3000: end if;
3001: else
2994: )
2995: then
2996: -- Error: Invalid town lookup_value has been passed.
2997: hr_utility.set_message_token('TOWN_CODE', p_town_of_birth);
2998: hr_utility.set_message(800, 'PER_52619_TOWN_NOT_FOUND)');
2999: hr_utility.raise_error;
3000: end if;
3001: else
3002: close csr_plfi;
2995: then
2996: -- Error: Invalid town lookup_value has been passed.
2997: hr_utility.set_message_token('TOWN_CODE', p_town_of_birth);
2998: hr_utility.set_message(800, 'PER_52619_TOWN_NOT_FOUND)');
2999: hr_utility.raise_error;
3000: end if;
3001: else
3002: close csr_plfi;
3003: end if;
3004: end if;
3005: end if;
3006: --
3007: if g_debug then
3008: hr_utility.set_location(' Leaving:'|| l_proc, 3);
3009: end if;
3010: exception
3011: when app_exception.application_exception then
3012: if hr_multi_message.exception_add
3012: if hr_multi_message.exception_add
3013: (p_associated_column1 => 'PER_ALL_PEOPLE_F.TOWN_OF_BIRTH'
3014: ) then
3015: if g_debug then
3016: hr_utility.set_location(' Leaving:'||l_proc, 4);
3017: end if;
3018: raise;
3019: end if;
3020: if g_debug then
3017: end if;
3018: raise;
3019: end if;
3020: if g_debug then
3021: hr_utility.set_location(' Leaving:'||l_proc,5);
3022: end if;
3023: end chk_town_of_birth;
3024: --
3025: -- ---------------------------------------------------------------------------
3075: --
3076: begin
3077: if g_debug then
3078: l_proc := g_package||'chk_region_of_birth';
3079: hr_utility.set_location('Entering:'|| l_proc, 1);
3080: end if;
3081: if p_region_of_birth IS NOT NULL then
3082: --
3083: -- Only proceed with validation if:
3089: ((p_person_id is not null) and
3090: nvl(p_region_of_birth,hr_api.g_varchar2) <> nvl(per_per_shd.g_old_rec.region_of_birth,hr_api.g_varchar2))) then
3091: --
3092: if g_debug then
3093: hr_utility.set_location(l_proc, 2);
3094: end if;
3095: --
3096: open csr_plfi;
3097: fetch csr_plfi into l_lookup_type;
3107: ,p_lookup_code => p_region_of_birth
3108: )
3109: then
3110: -- Error: Invalid region lookup_value has been passed.
3111: hr_utility.set_message_token('REGION_CODE', p_region_of_birth);
3112: hr_utility.set_message(800, 'PER_52620_REGION_NOT_FOUND)');
3113: hr_utility.raise_error;
3114: end if;
3115: else
3108: )
3109: then
3110: -- Error: Invalid region lookup_value has been passed.
3111: hr_utility.set_message_token('REGION_CODE', p_region_of_birth);
3112: hr_utility.set_message(800, 'PER_52620_REGION_NOT_FOUND)');
3113: hr_utility.raise_error;
3114: end if;
3115: else
3116: close csr_plfi;
3109: then
3110: -- Error: Invalid region lookup_value has been passed.
3111: hr_utility.set_message_token('REGION_CODE', p_region_of_birth);
3112: hr_utility.set_message(800, 'PER_52620_REGION_NOT_FOUND)');
3113: hr_utility.raise_error;
3114: end if;
3115: else
3116: close csr_plfi;
3117: end if;
3118: end if;
3119: end if;
3120: --
3121: if g_debug then
3122: hr_utility.set_location(' Leaving:'|| l_proc, 3);
3123: end if;
3124: exception
3125: when app_exception.application_exception then
3126: if hr_multi_message.exception_add
3126: if hr_multi_message.exception_add
3127: (p_associated_column1 => 'PER_ALL_PEOPLE_F.REGION_OF_BIRTH'
3128: ) then
3129: if g_debug then
3130: hr_utility.set_location(' Leaving:'||l_proc, 4);
3131: end if;
3132: raise;
3133: end if;
3134: if g_debug then
3131: end if;
3132: raise;
3133: end if;
3134: if g_debug then
3135: hr_utility.set_location(' Leaving:'||l_proc,5);
3136: end if;
3137: end chk_region_of_birth;
3138: --
3139: -- ---------------------------------------------------------------------------
3177: --
3178: begin
3179: if g_debug then
3180: l_proc := g_package||'chk_country_of_birth';
3181: hr_utility.set_location('Entering:'|| l_proc, 1);
3182: end if;
3183: if p_country_of_birth IS NOT NULL then
3184: --
3185: -- Only proceed with validation if:
3191: ((p_person_id is not null) and
3192: nvl(p_country_of_birth,hr_api.g_varchar2) <> nvl(per_per_shd.g_old_rec.country_of_birth,hr_api.g_varchar2))) then
3193: --
3194: if g_debug then
3195: hr_utility.set_location(l_proc, 2);
3196: end if;
3197: --
3198: open csr_country;
3199: fetch csr_country into l_exists;
3199: fetch csr_country into l_exists;
3200: if csr_country%notfound then
3201: close csr_country;
3202: -- Error: Invalid country short code
3203: hr_utility.set_message_token('COUNTRY_CODE', p_country_of_birth);
3204: hr_utility.set_message(800, 'PER_52618_COUNTRY_NOT_FOUND');
3205: hr_utility.raise_error;
3206: end if;
3207: close csr_country;
3200: if csr_country%notfound then
3201: close csr_country;
3202: -- Error: Invalid country short code
3203: hr_utility.set_message_token('COUNTRY_CODE', p_country_of_birth);
3204: hr_utility.set_message(800, 'PER_52618_COUNTRY_NOT_FOUND');
3205: hr_utility.raise_error;
3206: end if;
3207: close csr_country;
3208: end if;
3201: close csr_country;
3202: -- Error: Invalid country short code
3203: hr_utility.set_message_token('COUNTRY_CODE', p_country_of_birth);
3204: hr_utility.set_message(800, 'PER_52618_COUNTRY_NOT_FOUND');
3205: hr_utility.raise_error;
3206: end if;
3207: close csr_country;
3208: end if;
3209: end if;
3208: end if;
3209: end if;
3210: --
3211: if g_debug then
3212: hr_utility.set_location(' Leaving:'|| l_proc, 3);
3213: end if;
3214: exception
3215: when app_exception.application_exception then
3216: if hr_multi_message.exception_add
3216: if hr_multi_message.exception_add
3217: (p_associated_column1 => 'PER_ALL_PEOPLE_F.COUNTRY_OF_BIRTH'
3218: ) then
3219: if g_debug then
3220: hr_utility.set_location(' Leaving:'||l_proc, 4);
3221: end if;
3222: raise;
3223: end if;
3224: if g_debug then
3221: end if;
3222: raise;
3223: end if;
3224: if g_debug then
3225: hr_utility.set_location(' Leaving:'||l_proc,5);
3226: end if;
3227: end chk_country_of_birth;
3228: --
3229: -- ---------------------------------------------------------------------------
3279: where p_person_id = person_id;
3280: --
3281: begin
3282: if g_debug then
3283: hr_utility.set_location('Entering:'|| l_proc, 1);
3284: end if;
3285: --
3286: -- Check mandatory parameters have been set
3287: --
3311: --
3312: -- On insert check that start date equals effective date
3313: --
3314: if g_debug then
3315: hr_utility.set_location(l_proc, 2);
3316: end if;
3317: --
3318: if p_start_date <> p_effective_date then
3319: -- Error: Invalid Start Date
3316: end if;
3317: --
3318: if p_start_date <> p_effective_date then
3319: -- Error: Invalid Start Date
3320: hr_utility.set_message(801, 'HR_7514_PER_START_DATE_INVALID');
3321: hr_utility.raise_error;
3322: end if;
3323: elsif(l_api_updating and per_per_shd.g_old_rec.start_date <>
3324: p_start_date) then
3317: --
3318: if p_start_date <> p_effective_date then
3319: -- Error: Invalid Start Date
3320: hr_utility.set_message(801, 'HR_7514_PER_START_DATE_INVALID');
3321: hr_utility.raise_error;
3322: end if;
3323: elsif(l_api_updating and per_per_shd.g_old_rec.start_date <>
3324: p_start_date) then
3325: --
3326: -- On update check if start date is the same as the earliest effective
3327: -- start date
3328: --
3329: if g_debug then
3330: hr_utility.set_location(l_proc, 3);
3331: end if;
3332: --
3333: open csr_chk_min_date;
3334: fetch csr_chk_min_date into l_min_date;
3335: if p_start_date <> l_min_date then
3336: close csr_chk_min_date;
3337: -- Error: Invalid start date
3338:
3339: hr_utility.set_message(801, 'HR_7514_PER_START_DATE_INVALID');
3340: hr_utility.raise_error;
3341: end if;
3342: close csr_chk_min_date;
3343: end if;
3336: close csr_chk_min_date;
3337: -- Error: Invalid start date
3338:
3339: hr_utility.set_message(801, 'HR_7514_PER_START_DATE_INVALID');
3340: hr_utility.raise_error;
3341: end if;
3342: close csr_chk_min_date;
3343: end if;
3344: if g_debug then
3341: end if;
3342: close csr_chk_min_date;
3343: end if;
3344: if g_debug then
3345: hr_utility.set_location(' Leaving:'|| l_proc, 4);
3346: end if;
3347: exception
3348: when app_exception.application_exception then
3349: if hr_multi_message.exception_add
3349: if hr_multi_message.exception_add
3350: (p_associated_column1 => 'PER_ALL_PEOPLE_F.START_DATE'
3351: ) then
3352: if g_debug then
3353: hr_utility.set_location(' Leaving:'||l_proc, 5);
3354: end if;
3355: raise;
3356: end if;
3357: if g_debug then
3354: end if;
3355: raise;
3356: end if;
3357: if g_debug then
3358: hr_utility.set_location(' Leaving:'||l_proc,6);
3359: end if;
3360:
3361: end chk_start_date;
3362: --
3439: p_orig_hire_warning := FALSE;
3440: --
3441: if g_debug then
3442: l_proc := g_package||'chk_orig_and_start_dates';
3443: hr_utility.set_location('Entering:'|| l_proc,5);
3444: end if;
3445: --
3446: if p_original_date_of_hire is NOT NULL then
3447: if (nvl(per_per_shd.g_old_rec.original_date_of_hire,hr_api.g_date)
3451: if l_system_person_type in ('EMP','EMP_APL','EX_EMP','EX_EMP_APL') then
3452: --
3453: if (p_person_id is null and
3454: (nvl(p_original_date_of_hire,hr_api.g_date) > p_start_date)) then
3455: hr_utility.set_message(800,'PER_52474_PER_ORIG_ST_DATE');
3456: hr_multi_message.add
3457: (p_associated_column1 => 'PER_ALL_PEOPLE_F.ORIGINAL_DATE_OF_HIRE'
3458: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.START_DATE'
3459: );
3463: fetch csr_earliest_date into l_earliest_date;
3464: -- added for the bug 5402099
3465: open original_date_of_hire_ppf ;
3466: fetch original_date_of_hire_ppf into l_earliest_date1;
3467: hr_utility.set_location('l_earliest_date1:'|| l_earliest_date1,10);
3468: if l_earliest_date1 is null then
3469: l_earliest_date:=p_original_date_of_hire;
3470: hr_utility.set_location('l_earliest_date:'|| l_earliest_date,15);
3471: close original_date_of_hire_ppf;
3466: fetch original_date_of_hire_ppf into l_earliest_date1;
3467: hr_utility.set_location('l_earliest_date1:'|| l_earliest_date1,10);
3468: if l_earliest_date1 is null then
3469: l_earliest_date:=p_original_date_of_hire;
3470: hr_utility.set_location('l_earliest_date:'|| l_earliest_date,15);
3471: close original_date_of_hire_ppf;
3472: else
3473: close original_date_of_hire_ppf;
3474: end if;
3479: (nvl(p_original_date_of_hire,hr_api.g_date) > l_earliest_date))
3480: or ((l_earliest_date > p_start_date) and
3481: (nvl(p_original_date_of_hire,hr_api.g_date) > p_start_date)) then
3482: */
3483: hr_utility.set_message(800,'PER_52474_PER_ORIG_ST_DATE');
3484: close csr_earliest_date;
3485: hr_multi_message.add
3486: (p_associated_column1 => 'PER_ALL_PEOPLE_F.ORIGINAL_DATE_OF_HIRE'
3487: );
3496: end if;
3497: end if;
3498: --
3499: if g_debug then
3500: hr_utility.set_location(' Leaving:'||l_proc, 10);
3501: end if;
3502: --
3503: end chk_orig_and_start_dates;
3504: --
3637: l_rgeflg varchar2(10);
3638: --
3639: begin
3640: if g_debug then
3641: hr_utility.set_location('Entering:'|| l_proc, 10);
3642: end if;
3643: --
3644: -- Check the mandatory parameters
3645: --
3660: ,p_argument => 'validation_end_date'
3661: ,p_argument_value => p_validation_end_date
3662: );
3663: if g_debug then
3664: hr_utility.set_location(l_proc,20);
3665: end if;
3666: --
3667: -- We know the per_information_category is GB, so check the rest of
3668: -- the per_information fields within this context.
3685: ,p_lookup_code => p_per_information1
3686: )
3687: then
3688: --
3689: hr_utility.set_message(801, 'HR_7524_PER_INFO1_INVALID');
3690: hr_utility.raise_error;
3691: --
3692: end if;
3693: if g_debug then
3686: )
3687: then
3688: --
3689: hr_utility.set_message(801, 'HR_7524_PER_INFO1_INVALID');
3690: hr_utility.raise_error;
3691: --
3692: end if;
3693: if g_debug then
3694: hr_utility.set_location(l_proc,30);
3690: hr_utility.raise_error;
3691: --
3692: end if;
3693: if g_debug then
3694: hr_utility.set_location(l_proc,30);
3695: end if;
3696: --
3697: end if;
3698: if g_debug then
3695: end if;
3696: --
3697: end if;
3698: if g_debug then
3699: hr_utility.set_location(l_proc,40);
3700: end if;
3701: --
3702: -- Check if the per_information2 value exists in hr_lookups
3703: -- where the lookup_type is 'YES_NO'
3717: ,p_lookup_code => p_per_information2
3718: )
3719: then
3720: --
3721: hr_utility.set_message(801, 'HR_7525_PER_INFO2_INVALID');
3722: hr_utility.raise_error;
3723: --
3724: end if;
3725: if g_debug then
3718: )
3719: then
3720: --
3721: hr_utility.set_message(801, 'HR_7525_PER_INFO2_INVALID');
3722: hr_utility.raise_error;
3723: --
3724: end if;
3725: if g_debug then
3726: hr_utility.set_location(l_proc,50);
3722: hr_utility.raise_error;
3723: --
3724: end if;
3725: if g_debug then
3726: hr_utility.set_location(l_proc,50);
3727: end if;
3728: --
3729: end if;
3730: if g_debug then
3727: end if;
3728: --
3729: end if;
3730: if g_debug then
3731: hr_utility.set_location(l_proc,60);
3732: end if;
3733: --
3734: -- Check if the per_information4 value exists in hr_lookups
3735: -- where the lookup_type is 'YES_NO'
3749: ,p_lookup_code => p_per_information4
3750: )
3751: then
3752: --
3753: hr_utility.set_message(801, 'HR_7526_PER_INFO4_INVALID');
3754: hr_utility.raise_error;
3755: --
3756: end if;
3757: if g_debug then
3750: )
3751: then
3752: --
3753: hr_utility.set_message(801, 'HR_7526_PER_INFO4_INVALID');
3754: hr_utility.raise_error;
3755: --
3756: end if;
3757: if g_debug then
3758: hr_utility.set_location(l_proc,70);
3754: hr_utility.raise_error;
3755: --
3756: end if;
3757: if g_debug then
3758: hr_utility.set_location(l_proc,70);
3759: end if;
3760: --
3761: end if;
3762: if g_debug then
3759: end if;
3760: --
3761: end if;
3762: if g_debug then
3763: hr_utility.set_location(l_proc,80);
3764: end if;
3765: --
3766: -- Check if p_per_information5 is greater than 30 characters long
3767: --
3768: if p_per_information5 is not null then
3769: if length(p_per_information5) > 30 then
3770: -- Error: Work Permit (PER_INFORMATION5) cannot be longer than
3771: -- 30 characters
3772: hr_utility.set_message(801, 'HR_7527_PER_INFO5_LENGTH');
3773: hr_utility.raise_error;
3774: end if;
3775: if g_debug then
3776: hr_utility.set_location(l_proc,90);
3769: if length(p_per_information5) > 30 then
3770: -- Error: Work Permit (PER_INFORMATION5) cannot be longer than
3771: -- 30 characters
3772: hr_utility.set_message(801, 'HR_7527_PER_INFO5_LENGTH');
3773: hr_utility.raise_error;
3774: end if;
3775: if g_debug then
3776: hr_utility.set_location(l_proc,90);
3777: end if;
3772: hr_utility.set_message(801, 'HR_7527_PER_INFO5_LENGTH');
3773: hr_utility.raise_error;
3774: end if;
3775: if g_debug then
3776: hr_utility.set_location(l_proc,90);
3777: end if;
3778: --
3779: -- Check if p_per_information5 is not upper case
3780: --
3780: --
3781: --if p_per_information5 <> upper(p_per_information5) then
3782: -- Error: Enter the Work Permit value (PER_INFORMATION5) in
3783: -- upper case
3784: --hr_utility.set_message(801, 'HR_7528_PER_INFO5_CASE');
3785: --hr_utility.raise_error;
3786: --end if;
3787: --if g_debug then
3788: --hr_utility.set_location(l_proc,100);
3781: --if p_per_information5 <> upper(p_per_information5) then
3782: -- Error: Enter the Work Permit value (PER_INFORMATION5) in
3783: -- upper case
3784: --hr_utility.set_message(801, 'HR_7528_PER_INFO5_CASE');
3785: --hr_utility.raise_error;
3786: --end if;
3787: --if g_debug then
3788: --hr_utility.set_location(l_proc,100);
3789: --end if;
3784: --hr_utility.set_message(801, 'HR_7528_PER_INFO5_CASE');
3785: --hr_utility.raise_error;
3786: --end if;
3787: --if g_debug then
3788: --hr_utility.set_location(l_proc,100);
3789: --end if;
3790: end if;
3791: if g_debug then
3792: hr_utility.set_location(l_proc,110);
3788: --hr_utility.set_location(l_proc,100);
3789: --end if;
3790: end if;
3791: if g_debug then
3792: hr_utility.set_location(l_proc,110);
3793: end if;
3794: --
3795: -- Check if p_per_information6 is in the range 0 - 99.
3796: --
3805: ,nullok => 'Y'
3806: ,rgeflg => l_rgeflg
3807: ,curcode => NULL);
3808: if g_debug then
3809: hr_utility.set_location(l_proc,120);
3810: end if;
3811: --
3812: if to_number(l_per_information6) < 0 or
3813: to_number(l_per_information6) > 99 then
3812: if to_number(l_per_information6) < 0 or
3813: to_number(l_per_information6) > 99 then
3814: -- Error: Additional pension years (PER_INFORMATION6) not in the
3815: -- range 0 - 99.
3816: hr_utility.set_message(801, 'HR_51272_PER_INFO6_INVALID');
3817: hr_utility.raise_error;
3818: end if;
3819: if g_debug then
3820: hr_utility.set_location(l_proc,130);
3813: to_number(l_per_information6) > 99 then
3814: -- Error: Additional pension years (PER_INFORMATION6) not in the
3815: -- range 0 - 99.
3816: hr_utility.set_message(801, 'HR_51272_PER_INFO6_INVALID');
3817: hr_utility.raise_error;
3818: end if;
3819: if g_debug then
3820: hr_utility.set_location(l_proc,130);
3821: end if;
3816: hr_utility.set_message(801, 'HR_51272_PER_INFO6_INVALID');
3817: hr_utility.raise_error;
3818: end if;
3819: if g_debug then
3820: hr_utility.set_location(l_proc,130);
3821: end if;
3822: end if;
3823: if g_debug then
3824: hr_utility.set_location(l_proc, 140);
3820: hr_utility.set_location(l_proc,130);
3821: end if;
3822: end if;
3823: if g_debug then
3824: hr_utility.set_location(l_proc, 140);
3825: end if;
3826: --
3827: -- Check if p_per_information7 is in the range 1 - 11.
3828: --
3837: ,nullok => 'Y'
3838: ,rgeflg => l_rgeflg
3839: ,curcode => NULL);
3840: if g_debug then
3841: hr_utility.set_location(l_proc, 150);
3842: end if;
3843: --
3844: if to_number(l_per_information7) < 1 or
3845: to_number(l_per_information7) > 11 then
3844: if to_number(l_per_information7) < 1 or
3845: to_number(l_per_information7) > 11 then
3846: -- Error: Additional pension months (PER_INFORMATION7) not in the
3847: -- range 1 - 11.
3848: hr_utility.set_message(801, 'HR_51273_PER_INFO7_INVALID');
3849: hr_utility.raise_error;
3850: end if;
3851: if g_debug then
3852: hr_utility.set_location(l_proc,160);
3845: to_number(l_per_information7) > 11 then
3846: -- Error: Additional pension months (PER_INFORMATION7) not in the
3847: -- range 1 - 11.
3848: hr_utility.set_message(801, 'HR_51273_PER_INFO7_INVALID');
3849: hr_utility.raise_error;
3850: end if;
3851: if g_debug then
3852: hr_utility.set_location(l_proc,160);
3853: end if;
3848: hr_utility.set_message(801, 'HR_51273_PER_INFO7_INVALID');
3849: hr_utility.raise_error;
3850: end if;
3851: if g_debug then
3852: hr_utility.set_location(l_proc,160);
3853: end if;
3854: end if;
3855: if g_debug then
3856: hr_utility.set_location(l_proc, 170);
3852: hr_utility.set_location(l_proc,160);
3853: end if;
3854: end if;
3855: if g_debug then
3856: hr_utility.set_location(l_proc, 170);
3857: end if;
3858: --
3859: -- Check if p_per_information8 is number.
3860: --
3869: ,nullok => 'Y'
3870: ,rgeflg => l_rgeflg
3871: ,curcode => NULL);
3872: if g_debug then
3873: hr_utility.set_location(l_proc,180);
3874: end if;
3875: end if;
3876: if g_debug then
3877: hr_utility.set_location(l_proc, 190);
3873: hr_utility.set_location(l_proc,180);
3874: end if;
3875: end if;
3876: if g_debug then
3877: hr_utility.set_location(l_proc, 190);
3878: end if;
3879: --
3880: -- Check if the per_information9 value exists in hr_lookups
3881: -- where the lookup_type is 'YES_NO'
3895: ,p_lookup_code => p_per_information9
3896: )
3897: then
3898: --
3899: hr_utility.set_message(801, 'HR_51274_PER_INFO9_INVALID');
3900: hr_utility.raise_error;
3901: --
3902: end if;
3903: if g_debug then
3896: )
3897: then
3898: --
3899: hr_utility.set_message(801, 'HR_51274_PER_INFO9_INVALID');
3900: hr_utility.raise_error;
3901: --
3902: end if;
3903: if g_debug then
3904: hr_utility.set_location(l_proc,200);
3900: hr_utility.raise_error;
3901: --
3902: end if;
3903: if g_debug then
3904: hr_utility.set_location(l_proc,200);
3905: end if;
3906: --
3907: end if;
3908: if g_debug then
3905: end if;
3906: --
3907: end if;
3908: if g_debug then
3909: hr_utility.set_location(l_proc,210);
3910: end if;
3911:
3912: -- ***** Start new code for bug 2236999 **************
3913:
3926: ,p_lookup_code => p_per_information10
3927: )
3928: then
3929: --
3930: hr_utility.set_message(801, 'HR_78105_PER_INFO10_INVALID');
3931: hr_utility.raise_error;
3932: --
3933: end if;
3934:
3927: )
3928: then
3929: --
3930: hr_utility.set_message(801, 'HR_78105_PER_INFO10_INVALID');
3931: hr_utility.raise_error;
3932: --
3933: end if;
3934:
3935: --
4019: l_info_attribute := 30;
4020: raise l_error;
4021: end if;
4022: if g_debug then
4023: hr_utility.set_location(' Leaving:'|| l_proc, 220);
4024: end if;
4025: exception
4026: when l_error then
4027: -- Error: Do not enter PER_INFORMATION99 for this legislation
4024: end if;
4025: exception
4026: when l_error then
4027: -- Error: Do not enter PER_INFORMATION99 for this legislation
4028: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
4029: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
4030: hr_utility.raise_error;
4031: end chk_GB_per_information;
4032: --
4025: exception
4026: when l_error then
4027: -- Error: Do not enter PER_INFORMATION99 for this legislation
4028: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
4029: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
4030: hr_utility.raise_error;
4031: end chk_GB_per_information;
4032: --
4033: -- ---------------------------------------------------------------------------
4026: when l_error then
4027: -- Error: Do not enter PER_INFORMATION99 for this legislation
4028: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
4029: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
4030: hr_utility.raise_error;
4031: end chk_GB_per_information;
4032: --
4033: -- ---------------------------------------------------------------------------
4034: -- |---------------------< chk_US_per_information >-------------------------|
4259: l_rgeflg varchar2(10);
4260: --
4261: begin
4262: if g_debug then
4263: hr_utility.set_location('Entering:'|| l_proc, 10);
4264: end if;
4265: --
4266: -- Check the mandatory parameters
4267: --
4282: ,p_argument => 'validation_end_date'
4283: ,p_argument_value => p_validation_end_date
4284: );
4285: if g_debug then
4286: hr_utility.set_location(l_proc,20);
4287: end if;
4288: --
4289: -- We know the per_information_category is US, so check the rest of
4290: -- the per_information fields within this context.
4312: ,p_lookup_code => p_per_information1
4313: )
4314: then
4315: --
4316: hr_utility.set_message(801, 'HR_7524_PER_INFO1_INVALID');
4317: hr_utility.raise_error;
4318: --
4319: end if;
4320: if g_debug then
4313: )
4314: then
4315: --
4316: hr_utility.set_message(801, 'HR_7524_PER_INFO1_INVALID');
4317: hr_utility.raise_error;
4318: --
4319: end if;
4320: if g_debug then
4321: hr_utility.set_location(l_proc,30);
4317: hr_utility.raise_error;
4318: --
4319: end if;
4320: if g_debug then
4321: hr_utility.set_location(l_proc,30);
4322: end if;
4323: --
4324: end if;
4325: if g_debug then
4322: end if;
4323: --
4324: end if;
4325: if g_debug then
4326: hr_utility.set_location( l_proc, 40);
4327: end if;
4328: --
4329: -- Check if the value for per information2 is set on insert or has
4330: -- changed on update.
4349: ,p_lookup_code => p_per_information2
4350: )
4351: then
4352: --
4353: hr_utility.set_message(801, 'HR_51243_PER_INFO2_INVALID');
4354: hr_utility.raise_error;
4355: --
4356: end if;
4357: if g_debug then
4350: )
4351: then
4352: --
4353: hr_utility.set_message(801, 'HR_51243_PER_INFO2_INVALID');
4354: hr_utility.raise_error;
4355: --
4356: end if;
4357: if g_debug then
4358: hr_utility.set_location(l_proc,50);
4354: hr_utility.raise_error;
4355: --
4356: end if;
4357: if g_debug then
4358: hr_utility.set_location(l_proc,50);
4359: end if;
4360: --
4361: end if;
4362: if g_debug then
4359: end if;
4360: --
4361: end if;
4362: if g_debug then
4363: hr_utility.set_location(l_proc, 60);
4364: end if;
4365: --
4366: -- Check if the value for per information3 is set on insert or has
4367: -- changed on update.
4381: ,output => l_output
4382: ,format => 'D'
4383: ,curcode => NULL);
4384: if g_debug then
4385: hr_utility.set_location(l_proc, 70);
4386: end if;
4387: end if;
4388: if g_debug then
4389: hr_utility.set_location(l_proc, 80);
4385: hr_utility.set_location(l_proc, 70);
4386: end if;
4387: end if;
4388: if g_debug then
4389: hr_utility.set_location(l_proc, 80);
4390: end if;
4391: --
4392: -- Check if the value for per information4 is set on insert or has
4393: -- changed on update.
4412: ,p_lookup_code => p_per_information4
4413: )
4414: then
4415: --
4416: hr_utility.set_message(801, 'HR_51245_PER_INFO4_INVALID');
4417: hr_utility.raise_error;
4418: --
4419: end if;
4420: if g_debug then
4413: )
4414: then
4415: --
4416: hr_utility.set_message(801, 'HR_51245_PER_INFO4_INVALID');
4417: hr_utility.raise_error;
4418: --
4419: end if;
4420: if g_debug then
4421: hr_utility.set_location(l_proc,90);
4417: hr_utility.raise_error;
4418: --
4419: end if;
4420: if g_debug then
4421: hr_utility.set_location(l_proc,90);
4422: end if;
4423: --
4424: end if;
4425: if g_debug then
4422: end if;
4423: --
4424: end if;
4425: if g_debug then
4426: hr_utility.set_location(l_proc, 100);
4427: end if;
4428: --
4429: -- Check if the value for per information5 is set on insert or has
4430: -- changed on update.
4449: ,p_lookup_code => p_per_information5
4450: )
4451: then
4452: --
4453: hr_utility.set_message(801, 'HR_51246_PER_INFO5_INVALID');
4454: hr_utility.raise_error;
4455: --
4456: end if;
4457: if g_debug then
4450: )
4451: then
4452: --
4453: hr_utility.set_message(801, 'HR_51246_PER_INFO5_INVALID');
4454: hr_utility.raise_error;
4455: --
4456: end if;
4457: if g_debug then
4458: hr_utility.set_location(l_proc,110);
4454: hr_utility.raise_error;
4455: --
4456: end if;
4457: if g_debug then
4458: hr_utility.set_location(l_proc,110);
4459: end if;
4460: --
4461: end if;
4462: if g_debug then
4459: end if;
4460: --
4461: end if;
4462: if g_debug then
4463: hr_utility.set_location( l_proc, 120);
4464: end if;
4465: --
4466: -- Check if the value for per information6 is set on insert or has
4467: -- changed on update.
4486: ,p_lookup_code => p_per_information6
4487: )
4488: then
4489: --
4490: hr_utility.set_message(801, 'HR_51247_PER_INFO6_INVALID');
4491: hr_utility.raise_error;
4492: --
4493: end if;
4494: if g_debug then
4487: )
4488: then
4489: --
4490: hr_utility.set_message(801, 'HR_51247_PER_INFO6_INVALID');
4491: hr_utility.raise_error;
4492: --
4493: end if;
4494: if g_debug then
4495: hr_utility.set_location(l_proc, 130);
4491: hr_utility.raise_error;
4492: --
4493: end if;
4494: if g_debug then
4495: hr_utility.set_location(l_proc, 130);
4496: end if;
4497: --
4498: end if;
4499: if g_debug then
4496: end if;
4497: --
4498: end if;
4499: if g_debug then
4500: hr_utility.set_location( l_proc, 140);
4501: end if;
4502: --
4503: -- Check if the value for per information7 is set on insert or has
4504: -- changed on update.
4523: ,p_lookup_code => p_per_information7
4524: )
4525: then
4526: --
4527: hr_utility.set_message(801, 'HR_51285_PER_INFO7_INVALID');
4528: hr_utility.raise_error;
4529: --
4530: end if;
4531: if g_debug then
4524: )
4525: then
4526: --
4527: hr_utility.set_message(801, 'HR_51285_PER_INFO7_INVALID');
4528: hr_utility.raise_error;
4529: --
4530: end if;
4531: if g_debug then
4532: hr_utility.set_location(l_proc,150);
4528: hr_utility.raise_error;
4529: --
4530: end if;
4531: if g_debug then
4532: hr_utility.set_location(l_proc,150);
4533: end if;
4534: --
4535: end if;
4536: if g_debug then
4533: end if;
4534: --
4535: end if;
4536: if g_debug then
4537: hr_utility.set_location( l_proc, 160);
4538: end if;
4539: --
4540: -- Check if the value for per information8 is set on insert or has
4541: -- changed on update.
4553: then
4554: --
4555: -- Error: Field must be null because per_info7 is not 'EXCL'
4556: --
4557: hr_utility.set_message(801, 'HR_51286_PER_INFO8_NOT_NULL');
4558: hr_utility.raise_error;
4559: else
4560: --
4561: -- Check that per information7 exists in hr_lookups for the
4554: --
4555: -- Error: Field must be null because per_info7 is not 'EXCL'
4556: --
4557: hr_utility.set_message(801, 'HR_51286_PER_INFO8_NOT_NULL');
4558: hr_utility.raise_error;
4559: else
4560: --
4561: -- Check that per information7 exists in hr_lookups for the
4562: -- lookup type 'US_NEW_HIRE_EXCEPTIONS' with an enabled flag set to 'Y'
4571: ,p_lookup_code => p_per_information8
4572: )
4573: then
4574: --
4575: hr_utility.set_message(801, 'HR_51287_PER_INFO8_INVALID');
4576: hr_utility.raise_error;
4577: --
4578: end if;
4579: if g_debug then
4572: )
4573: then
4574: --
4575: hr_utility.set_message(801, 'HR_51287_PER_INFO8_INVALID');
4576: hr_utility.raise_error;
4577: --
4578: end if;
4579: if g_debug then
4580: hr_utility.set_location( l_proc, 170);
4576: hr_utility.raise_error;
4577: --
4578: end if;
4579: if g_debug then
4580: hr_utility.set_location( l_proc, 170);
4581: end if;
4582: --
4583: end if;
4584: end if;
4582: --
4583: end if;
4584: end if;
4585: if g_debug then
4586: hr_utility.set_location( l_proc, 180);
4587: end if;
4588: --
4589: -- Check if the value for per information9 is set on insert or has
4590: -- changed on update.
4609: ,p_lookup_code => p_per_information9
4610: )
4611: then
4612: --
4613: hr_utility.set_message(801, 'HR_51288_PER_INFO9_INVALID');
4614: hr_utility.raise_error;
4615: --
4616: end if;
4617: if g_debug then
4610: )
4611: then
4612: --
4613: hr_utility.set_message(801, 'HR_51288_PER_INFO9_INVALID');
4614: hr_utility.raise_error;
4615: --
4616: end if;
4617: if g_debug then
4618: hr_utility.set_location(l_proc, 190);
4614: hr_utility.raise_error;
4615: --
4616: end if;
4617: if g_debug then
4618: hr_utility.set_location(l_proc, 190);
4619: end if;
4620: --
4621: end if;
4622: if g_debug then
4619: end if;
4620: --
4621: end if;
4622: if g_debug then
4623: hr_utility.set_location( l_proc, 200);
4624: end if;
4625: --
4626: if (((nvl(p_per_information10,hr_api.g_varchar2) <>
4627: nvl(per_per_shd.g_old_rec.per_information10,hr_api.g_varchar2)
4643: ,p_lookup_code => p_per_information10
4644: )
4645: then
4646: --
4647: hr_utility.set_message(801, 'PER_52390_PER_INFO10_INVALID');
4648: hr_utility.raise_error;
4649: --
4650: end if;
4651: if g_debug then
4644: )
4645: then
4646: --
4647: hr_utility.set_message(801, 'PER_52390_PER_INFO10_INVALID');
4648: hr_utility.raise_error;
4649: --
4650: end if;
4651: if g_debug then
4652: hr_utility.set_location(l_proc,210);
4648: hr_utility.raise_error;
4649: --
4650: end if;
4651: if g_debug then
4652: hr_utility.set_location(l_proc,210);
4653: end if;
4654: --
4655: end if;
4656: if g_debug then
4653: end if;
4654: --
4655: end if;
4656: if g_debug then
4657: hr_utility.set_location(l_proc,220);
4658: end if;
4659:
4660:
4661: -- Check if any of the remaining per_information parameters are not
4733: l_info_attribute := 30;
4734: raise l_error;
4735: end if;
4736: if g_debug then
4737: hr_utility.set_location('Leaving:'|| l_proc, 230);
4738: end if;
4739: exception
4740: when l_error then
4741: -- Error: Do not enter PER_INFORMATION99 for this legislation
4738: end if;
4739: exception
4740: when l_error then
4741: -- Error: Do not enter PER_INFORMATION99 for this legislation
4742: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
4743: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
4744: hr_utility.raise_error;
4745: end chk_US_per_information;
4746: --
4739: exception
4740: when l_error then
4741: -- Error: Do not enter PER_INFORMATION99 for this legislation
4742: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
4743: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
4744: hr_utility.raise_error;
4745: end chk_US_per_information;
4746: --
4747: -- ----------------------------------------------------------------------------
4740: when l_error then
4741: -- Error: Do not enter PER_INFORMATION99 for this legislation
4742: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
4743: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
4744: hr_utility.raise_error;
4745: end chk_US_per_information;
4746: --
4747: -- ----------------------------------------------------------------------------
4748: -- |------------------------
4792: l_info_attribute number(2);
4793: --
4794: begin
4795: if g_debug then
4796: hr_utility.set_location('Entering:'|| l_proc, 10);
4797: end if;
4798: --
4799: -- Check the mandatory parameters
4800: --
4815: ,p_argument => 'validation_end_date'
4816: ,p_argument_value => p_validation_end_date
4817: );
4818: if g_debug then
4819: hr_utility.set_location(l_proc,20);
4820: end if;
4821: --
4822: -- We know the per_information_category is JP, so check the rest of
4823: -- the per_information fields within this context.
4840: ,p_lookup_code => p_per_information1
4841: )
4842: then
4843: --
4844: hr_utility.set_message(801, 'HR_72022_PER_INFO1_INVALID');
4845: hr_utility.raise_error;
4846: --
4847: end if;
4848: if g_debug then
4841: )
4842: then
4843: --
4844: hr_utility.set_message(801, 'HR_72022_PER_INFO1_INVALID');
4845: hr_utility.raise_error;
4846: --
4847: end if;
4848: if g_debug then
4849: hr_utility.set_location(l_proc,30);
4845: hr_utility.raise_error;
4846: --
4847: end if;
4848: if g_debug then
4849: hr_utility.set_location(l_proc,30);
4850: end if;
4851: --
4852: end if;
4853: if g_debug then
4850: end if;
4851: --
4852: end if;
4853: if g_debug then
4854: hr_utility.set_location(l_proc,40);
4855: end if;
4856: --
4857: -- Check if the per_information2 value exists in hr_lookups
4858: -- where the lookup_type is 'JP_TERM_LOCAL_TAX_PAYMENT_TYPE'
4873: ,p_lookup_code => p_per_information2
4874: )
4875: then
4876: --
4877: hr_utility.set_message(801, 'HR_72023_PER_INFO2_INVALID');
4878: hr_utility.raise_error;
4879: --
4880: end if;
4881: if g_debug then
4874: )
4875: then
4876: --
4877: hr_utility.set_message(801, 'HR_72023_PER_INFO2_INVALID');
4878: hr_utility.raise_error;
4879: --
4880: end if;
4881: if g_debug then
4882: hr_utility.set_location(l_proc,50);
4878: hr_utility.raise_error;
4879: --
4880: end if;
4881: if g_debug then
4882: hr_utility.set_location(l_proc,50);
4883: end if;
4884: --
4885: end if;
4886: if g_debug then
4883: end if;
4884: --
4885: end if;
4886: if g_debug then
4887: hr_utility.set_location(l_proc,60);
4888: end if;
4889: --
4890: --
4891: -- Check if any of the remaining per_information parameters are not
4939: l_info_attribute := 17;
4940: raise l_error;
4941: end if;
4942: if g_debug then
4943: hr_utility.set_location(' Leaving:'|| l_proc, 210);
4944: end if;
4945: exception
4946: when l_error then
4947: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
4943: hr_utility.set_location(' Leaving:'|| l_proc, 210);
4944: end if;
4945: exception
4946: when l_error then
4947: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
4948: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
4949: hr_utility.raise_error;
4950: end chk_JP_per_information;
4951: --
4944: end if;
4945: exception
4946: when l_error then
4947: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
4948: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
4949: hr_utility.raise_error;
4950: end chk_JP_per_information;
4951: --
4952: --
4945: exception
4946: when l_error then
4947: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
4948: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
4949: hr_utility.raise_error;
4950: end chk_JP_per_information;
4951: --
4952: --
4953: -- -----------------------------------------------------------------------------
4962: --
4963: Begin
4964: if g_debug then
4965: l_proc := g_package||'chk_ddf';
4966: hr_utility.set_location('Entering:'||l_proc, 5);
4967: end if;
4968: --
4969: -- Check if the row is being inserted or updated and a
4970: -- value has changed
5104: --
5105: end if;
5106: --
5107: if g_debug then
5108: hr_utility.set_location(' Leaving:'||l_proc, 10);
5109: end if;
5110: end chk_ddf;
5111: -- ---------------------------------------------------------------------------
5112: -- |-----------------------< chk_per_information >--------------------------|
5184: l_info_attribute number(2);
5185: --
5186: begin
5187: if g_debug then
5188: hr_utility.set_location('Entering:'|| l_proc, 10);
5189: end if;
5190: --
5191: -- Check mandatory parameters have been set
5192: --
5207: ,p_argument => 'validation_end_date'
5208: ,p_argument_value => p_validation_end_date
5209: );
5210: if g_debug then
5211: hr_utility.set_location(l_proc, 20);
5212: end if;
5213: --
5214: -- Only proceed with validation if:
5215: -- a) The current g_old_rec is current and
5222: ,p_effective_date => p_effective_date
5223: ,p_object_version_number => p_rec.object_version_number
5224: );
5225: if g_debug then
5226: hr_utility.set_location(l_proc, 30);
5227: end if;
5228: --
5229: if ((l_api_updating and nvl(per_per_shd.g_old_rec.per_information_category,
5230: hr_api.g_varchar2)
5321: <> nvl(p_rec.per_information30,hr_api.g_varchar2)) or
5322: (NOT l_api_updating))
5323: then
5324: if g_debug then
5325: hr_utility.set_location(l_proc, 40);
5326: end if;
5327: --
5328: -- Check if the per_information_category is 'GB' or 'US' calling
5329: -- the appropriate validation routine or generating an error
5371: ,p_effective_date => p_effective_date
5372: ,p_object_version_number => p_rec.object_version_number
5373: );
5374: if g_debug then
5375: hr_utility.set_location(l_proc, 50);
5376: end if;
5377: --
5378: elsif p_rec.per_information_category = 'US' then
5379: --
5418: ,p_object_version_number => p_rec.object_version_number
5419: ,p_api_updating => l_api_updating
5420: );
5421: if g_debug then
5422: hr_utility.set_location(l_proc, 60);
5423: end if;
5424: --
5425: /* elsif p_rec.per_information_category = 'JP' then
5426: --
5464: ,p_effective_date => p_effective_date
5465: ,p_object_version_number => p_rec.object_version_number
5466: );
5467: if g_debug then
5468: hr_utility.set_location(l_proc, 70);
5469: end if;
5470: -- */
5471: else
5472: per_per_bus.chk_ddf(p_rec => p_rec);
5473: end if;
5474: -- end if;
5475: end if;
5476: if g_debug then
5477: hr_utility.set_location(' Leaving:'|| l_proc, 100);
5478: end if;
5479: exception
5480: when l_error then
5481: -- Error: Do not enter PER_INFORMATION99 for this legislation
5478: end if;
5479: exception
5480: when l_error then
5481: -- Error: Do not enter PER_INFORMATION99 for this legislation
5482: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
5483: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
5484: hr_multi_message.add;
5485: -- hr_utility.raise_error;
5486: end chk_per_information;
5479: exception
5480: when l_error then
5481: -- Error: Do not enter PER_INFORMATION99 for this legislation
5482: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
5483: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
5484: hr_multi_message.add;
5485: -- hr_utility.raise_error;
5486: end chk_per_information;
5487: --
5481: -- Error: Do not enter PER_INFORMATION99 for this legislation
5482: hr_utility.set_message(801, 'HR_7529_PER_INFO_NOT_NULL');
5483: hr_utility.set_message_token('NUM',to_char(l_info_attribute));
5484: hr_multi_message.add;
5485: -- hr_utility.raise_error;
5486: end chk_per_information;
5487: --
5488: /* Bug#3613987 - Removed chk_JP_names procedure
5489: -- ---------------------------------------------------------------------------
5537: --
5538: begin
5539: if g_debug then
5540: l_proc := g_package||'chk_JP_names';
5541: hr_utility.set_location('Entering:'|| l_proc, 1);
5542: end if;
5543: --
5544: if hr_multi_message.no_exclusive_error
5545: (p_check_column1 => 'PER_ALL_PEOPLE_F.LAST_NAME'
5556: -- c) a record is being inserted
5557: --
5558: --
5559: if g_debug then
5560: hr_utility.set_location(l_proc, 2);
5561: end if;
5562: --
5563: if ((l_api_updating
5564: and nvl(per_per_shd.g_old_rec.last_name, hr_api.g_varchar2)
5579: ,curcode => NULL);
5580: end if;
5581: --
5582: if g_debug then
5583: hr_utility.set_location(l_proc, 3);
5584: end if;
5585: --
5586: if ((l_api_updating
5587: and nvl(per_per_shd.g_old_rec.first_name, hr_api.g_varchar2)
5603: end if;
5604: end if;
5605: --
5606: if g_debug then
5607: hr_utility.set_location(' Leaving:'|| l_proc, 5);
5608: end if;
5609: exception
5610: when app_exception.application_exception then
5611: if hr_multi_message.exception_add
5612: (p_associated_column1 => 'PER_ALL_PEOPLE_F.LAST_NAME'
5613: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.FIRST_NAME'
5614: ) then
5615: if g_debug then
5616: hr_utility.set_location(' Leaving:'||l_proc, 6);
5617: end if;
5618: raise;
5619: end if;
5620: if g_debug then
5617: end if;
5618: raise;
5619: end if;
5620: if g_debug then
5621: hr_utility.set_location(' Leaving:'||l_proc,7);
5622: end if;
5623: end chk_JP_names;
5624: */
5625: --
5763: l_local_name varchar2(240);
5764: --
5765: begin
5766: if g_debug then
5767: hr_utility.set_location('Entering:'|| l_proc, 10);
5768: end if;
5769: --
5770: -- Check mandatory parameters have been set
5771: --
5784: -- Check if the last name is set.
5785: --
5786: if p_last_name is null then
5787: --
5788: hr_utility.set_message(801, 'PER_52076_PER_NULL_LAST_NAME');
5789: hr_utility.raise_error;
5790: --
5791: end if;
5792: --
5785: --
5786: if p_last_name is null then
5787: --
5788: hr_utility.set_message(801, 'PER_52076_PER_NULL_LAST_NAME');
5789: hr_utility.raise_error;
5790: --
5791: end if;
5792: --
5793: if hr_multi_message.no_all_inclusive_error
6030: <> nvl(p_attribute30,hr_api.g_varchar2)) or
6031: (NOT l_api_updating)) then
6032: --
6033: if g_debug then
6034: hr_utility.set_location(l_proc, 20);
6035: end if;
6036: --
6037: l_duplicate_flag := 'N';
6038: --
6145: --
6146: if l_duplicate_flag = 'Y' then
6147: p_name_combination_warning := TRUE;
6148: if g_debug then
6149: hr_utility.set_location(l_proc,25);
6150: end if;
6151: end if;
6152: --
6153: if g_debug then
6150: end if;
6151: end if;
6152: --
6153: if g_debug then
6154: hr_utility.set_location(l_proc, 30);
6155: end if;
6156: else
6157: --
6158: -- We are not updating the name so we must return the current full_name
6163: p_local_name := per_per_shd.g_old_rec.local_name;
6164: end if;
6165: end if;
6166: if g_debug then
6167: hr_utility.set_location(' Leaving:'|| l_proc, 40);
6168: end if;
6169: exception
6170: when app_exception.application_exception then
6171: if hr_multi_message.exception_add
6171: if hr_multi_message.exception_add
6172: (p_associated_column1 => 'PER_ALL_PEOPLE_F.LAST_NAME'
6173: ) then
6174: if g_debug then
6175: hr_utility.set_location(' Leaving:'||l_proc, 50);
6176: end if;
6177: raise;
6178: end if;
6179: if g_debug then
6176: end if;
6177: raise;
6178: end if;
6179: if g_debug then
6180: hr_utility.set_location(' Leaving:'||l_proc,60);
6181: end if;
6182: end return_full_name;
6183: -- ---------------------------------------------------------------------------
6184: -- |-----------------------< chk_applicant_number >------------------------|
6331: --
6332: begin
6333: --
6334: if g_debug then
6335: hr_utility.set_location('Entering '||l_proc,10);
6336: end if;
6337: --
6338: -- Assume we have not changed the system_person_type, so set return
6339: -- variable to FALSE.
6344: --
6345: if p_new_person_type_id <> p_old_person_type_id then
6346: --
6347: if g_debug then
6348: hr_utility.set_location(l_proc, 20);
6349: end if;
6350: --
6351: -- Get the system_person_type for the 'new' person_type_id
6352: --
6354: (p_person_type_Id =>p_new_person_type_id
6355: ,p_business_group_id =>p_business_group_id
6356: );
6357: if g_debug then
6358: hr_utility.set_location(l_proc, 30);
6359: end if;
6360: --
6361: -- Get the system_person_type for the 'old' person_type_id
6362: --
6376: (l_old_system_person_type = 'EX_EMP' and
6377: l_new_system_person_type = 'EX_EMP_APL')) then
6378: --
6379: if g_debug then
6380: hr_utility.set_location(l_proc, 40);
6381: end if;
6382: --
6383: l_return_status := true;
6384: end if;
6382: --
6383: l_return_status := true;
6384: end if;
6385: if g_debug then
6386: hr_utility.set_location(l_proc, 50);
6387: end if;
6388: end if;
6389: if g_debug then
6390: hr_utility.set_location(' Leaving '||l_proc, 60);
6386: hr_utility.set_location(l_proc, 50);
6387: end if;
6388: end if;
6389: if g_debug then
6390: hr_utility.set_location(' Leaving '||l_proc, 60);
6391: end if;
6392: return l_return_status;
6393: end apl_sys_per_type_change;
6394: --
6393: end apl_sys_per_type_change;
6394: --
6395: begin
6396: if g_debug then
6397: hr_utility.set_location('Entering:'|| l_proc, 1);
6398: end if;
6399: --
6400: -- Check mandatory parameters have been set
6401: --
6422: (p_person_id => p_person_id
6423: ,p_effective_date => p_effective_date
6424: ,p_object_version_number => p_object_version_number);
6425: if g_debug then
6426: hr_utility.set_location(l_proc, 2);
6427: end if;
6428: --
6429: -- Need to validate/generate the applicant number if the applicant number
6430: -- has changed or if the system person type has changed in such a way
6441: or
6442: (NOT l_api_updating)) then
6443: --
6444: if g_debug then
6445: hr_utility.set_location(l_proc, 3);
6446: end if;
6447: --
6448: -- Get the generation method for applicant numbers for the
6449: -- business group
6451: open csr_gen_meth;
6452: fetch csr_gen_meth into l_gen_method;
6453: close csr_gen_meth;
6454: if g_debug then
6455: hr_utility.set_location(l_proc, 4);
6456: end if;
6457: --
6458: -- Get the system person type for the type id
6459: --
6461: (p_person_type_id => p_person_type_id
6462: ,p_business_group_id => p_business_group_id
6463: );
6464: if g_debug then
6465: hr_utility.set_location(l_proc, 5);
6466: end if;
6467: --
6468: -- If system_person_type is OTHER, applicant number must be NULL
6469: --
6468: -- If system_person_type is OTHER, applicant number must be NULL
6469: --
6470: if (l_system_person_type = 'OTHER' and
6471: p_applicant_number is not null) then
6472: hr_utility.set_message(801, 'HR_51199_PER_APP_NOT_NULL');
6473: l_apl_sys := true;
6474: hr_utility.raise_error;
6475: end if;
6476: if g_debug then
6470: if (l_system_person_type = 'OTHER' and
6471: p_applicant_number is not null) then
6472: hr_utility.set_message(801, 'HR_51199_PER_APP_NOT_NULL');
6473: l_apl_sys := true;
6474: hr_utility.raise_error;
6475: end if;
6476: if g_debug then
6477: hr_utility.set_location(l_proc, 6);
6478: end if;
6473: l_apl_sys := true;
6474: hr_utility.raise_error;
6475: end if;
6476: if g_debug then
6477: hr_utility.set_location(l_proc, 6);
6478: end if;
6479: --
6480: -- If system person type is EMP, applicant number must be NULL
6481: -- on insert
6482: --
6483: if (NOT l_api_updating and
6484: l_system_person_type = 'EMP' and
6485: p_applicant_number is not null) then
6486: hr_utility.set_message(801, 'HR_51202_PER_APL_NOT_NULL');
6487: l_apl_sys := true;
6488: hr_utility.raise_error;
6489: end if;
6490: if g_debug then
6484: l_system_person_type = 'EMP' and
6485: p_applicant_number is not null) then
6486: hr_utility.set_message(801, 'HR_51202_PER_APL_NOT_NULL');
6487: l_apl_sys := true;
6488: hr_utility.raise_error;
6489: end if;
6490: if g_debug then
6491: hr_utility.set_location(l_proc, 7);
6492: end if;
6487: l_apl_sys := true;
6488: hr_utility.raise_error;
6489: end if;
6490: if g_debug then
6491: hr_utility.set_location(l_proc, 7);
6492: end if;
6493: --
6494: -- If system person type is EMP or EX_EMP and we are updating then
6495: -- applicant number cannot change to null
6497: if (l_api_updating) then
6498: if (l_system_person_type in ('EMP','EX_EMP') and
6499: per_per_shd.g_old_rec.applicant_number is not null and
6500: p_applicant_number is null) then
6501: hr_utility.set_message(801, 'HR_51200_PER_APL_NOT_NULL_UPD');
6502: l_apl_sys := true;
6503: hr_utility.raise_error;
6504: end if;
6505: if g_debug then
6499: per_per_shd.g_old_rec.applicant_number is not null and
6500: p_applicant_number is null) then
6501: hr_utility.set_message(801, 'HR_51200_PER_APL_NOT_NULL_UPD');
6502: l_apl_sys := true;
6503: hr_utility.raise_error;
6504: end if;
6505: if g_debug then
6506: hr_utility.set_location(l_proc, 8);
6507: end if;
6502: l_apl_sys := true;
6503: hr_utility.raise_error;
6504: end if;
6505: if g_debug then
6506: hr_utility.set_location(l_proc, 8);
6507: end if;
6508: end if;
6509: if g_debug then
6510: hr_utility.set_location(l_proc, 9);
6506: hr_utility.set_location(l_proc, 8);
6507: end if;
6508: end if;
6509: if g_debug then
6510: hr_utility.set_location(l_proc, 9);
6511: end if;
6512: --
6513: -- Check that on update with Automatic number generation
6514: -- the applicant number is not changed.
6516: if (l_api_updating and
6517: nvl(per_per_shd.g_old_rec.applicant_number,hr_api.g_varchar2) <>
6518: nvl(p_applicant_number, hr_api.g_varchar2) and
6519: l_gen_method = 'A') then
6520: hr_utility.set_message(801, 'HR_51201_PER_INV_APL_UPD');
6521: hr_utility.raise_error;
6522: end if;
6523: if g_debug then
6524: hr_utility.set_location(l_proc,11);
6517: nvl(per_per_shd.g_old_rec.applicant_number,hr_api.g_varchar2) <>
6518: nvl(p_applicant_number, hr_api.g_varchar2) and
6519: l_gen_method = 'A') then
6520: hr_utility.set_message(801, 'HR_51201_PER_INV_APL_UPD');
6521: hr_utility.raise_error;
6522: end if;
6523: if g_debug then
6524: hr_utility.set_location(l_proc,11);
6525: end if;
6520: hr_utility.set_message(801, 'HR_51201_PER_INV_APL_UPD');
6521: hr_utility.raise_error;
6522: end if;
6523: if g_debug then
6524: hr_utility.set_location(l_proc,11);
6525: end if;
6526: --
6527: -- On update, If system person type is EMP and no previous sys per
6528: -- type change then applicant number must be null
6534: ,p_person_id => p_person_id
6535: ,p_business_group_id => p_business_group_id
6536: ,p_effective_start_date => p_effective_date) and
6537: p_applicant_number is not null) then
6538: hr_utility.set_message(801, 'HR_51202_PER_APP_NOT_NULL_SPT');
6539: l_apl_sys := true;
6540: hr_utility.raise_error;
6541: end if;
6542: if g_debug then
6536: ,p_effective_start_date => p_effective_date) and
6537: p_applicant_number is not null) then
6538: hr_utility.set_message(801, 'HR_51202_PER_APP_NOT_NULL_SPT');
6539: l_apl_sys := true;
6540: hr_utility.raise_error;
6541: end if;
6542: if g_debug then
6543: hr_utility.set_location(l_proc,12);
6544: end if;
6539: l_apl_sys := true;
6540: hr_utility.raise_error;
6541: end if;
6542: if g_debug then
6543: hr_utility.set_location(l_proc,12);
6544: end if;
6545: --
6546: -- Now call the generate routine. We are either in insert mode
6547: -- or updating a manually generated applicant number
6560: ,p_date_of_birth => p_date_of_birth
6561: ,p_start_date => p_effective_date);
6562:
6563: if g_debug then
6564: hr_utility.set_location(l_proc,13);
6565: end if;
6566: --
6567: -- Check uniqueness of generated number
6568: --
6568: --
6569: if l_gen_method <> 'A' then
6570: --
6571: if g_debug then
6572: hr_utility.set_location(l_proc,14);
6573: end if;
6574: --
6575: hr_person.validate_unique_number
6576: (p_person_id => p_person_id
6582: ,p_current_applicant => p_current_applicant
6583: ,p_current_npw => 'N');
6584: --
6585: if g_debug then
6586: hr_utility.set_location(l_proc,15);
6587: end if;
6588: --
6589: end if;
6590: --
6589: end if;
6590: --
6591: end if;
6592: if g_debug then
6593: hr_utility.set_location(' Leaving:'|| l_proc,16);
6594: end if;
6595: exception
6596: when app_exception.application_exception then
6597: if not l_apl_sys
6599: if hr_multi_message.exception_add
6600: (p_associated_column1 => 'PER_ALL_PEOPLE_F.APPLICANT_NUMBER'
6601: ) then
6602: if g_debug then
6603: hr_utility.set_location(' Leaving:'||l_proc, 17);
6604: end if;
6605: raise;
6606: end if;
6607: else
6609: (p_associated_column1 => 'PER_ALL_PEOPLE_F.APPLICANT_NUMBER'
6610: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.PERSON_TYPE_ID'
6611: ) then
6612: if g_debug then
6613: hr_utility.set_location(' Leaving:'||l_proc, 17);
6614: end if;
6615: raise;
6616: end if;
6617: end if;
6615: raise;
6616: end if;
6617: end if;
6618: if g_debug then
6619: hr_utility.set_location(' Leaving:'||l_proc,18);
6620: end if;
6621: end chk_applicant_number;
6622: --
6623: -- ---------------------------------------------------------------------------
6666: --
6667: begin
6668: if g_debug then
6669: l_proc := g_package||'chk_date_employee_data_verified';
6670: hr_utility.set_location('Entering:'|| l_proc, 1);
6671: end if;
6672: --
6673: -- Check if the person is being updated
6674: --
6676: (p_person_id => p_person_id
6677: ,p_effective_date => p_effective_start_date
6678: ,p_object_version_number => p_object_version_number);
6679: if g_debug then
6680: hr_utility.set_location(l_proc, 2);
6681: end if;
6682: --
6683: if ((l_api_updating and
6684: nvl(per_per_shd.g_old_rec.date_employee_data_verified,
6685: hr_api.g_date) <> nvl(p_date_employee_data_verified,
6686: hr_api.g_date)) or (NOT l_api_updating)) then
6687: --
6688: if g_debug then
6689: hr_utility.set_location(l_proc, 3);
6690: end if;
6691: --
6692: -- Check that date employee data verified is valid
6693: --
6696:
6697: --if p_date_employee_data_verified is not null and
6698: -- p_date_employee_data_verified < p_effective_start_date then
6699: -- Error: Invalid value
6700: -- hr_utility.set_message(801, 'HR_51256_PER_DTE_EMP_DTE_START');
6701: -- hr_utility.raise_error;
6702: --end if;
6703:
6704: --
6697: --if p_date_employee_data_verified is not null and
6698: -- p_date_employee_data_verified < p_effective_start_date then
6699: -- Error: Invalid value
6700: -- hr_utility.set_message(801, 'HR_51256_PER_DTE_EMP_DTE_START');
6701: -- hr_utility.raise_error;
6702: --end if;
6703:
6704: --
6705: if g_debug then
6702: --end if;
6703:
6704: --
6705: if g_debug then
6706: hr_utility.set_location(l_proc, 4);
6707: end if;
6708: end if;
6709: if g_debug then
6710: hr_utility.set_location(' Leaving:'|| l_proc, 5);
6706: hr_utility.set_location(l_proc, 4);
6707: end if;
6708: end if;
6709: if g_debug then
6710: hr_utility.set_location(' Leaving:'|| l_proc, 5);
6711: end if;
6712: exception
6713: when app_exception.application_exception then
6714: if hr_multi_message.exception_add
6715: (p_associated_column1 => 'PER_ALL_PEOPLE_F.DATE_EMPLOYEE_DATA_VERIFIED'
6716: ,p_associated_column2 => 'PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE'
6717: ) then
6718: if g_debug then
6719: hr_utility.set_location(' Leaving:'||l_proc, 6);
6720: end if;
6721: raise;
6722: end if;
6723: if g_debug then
6720: end if;
6721: raise;
6722: end if;
6723: if g_debug then
6724: hr_utility.set_location(' Leaving:'||l_proc,7);
6725: end if;
6726: end chk_date_emp_data_verified;
6727: --
6728: -- ---------------------------------------------------------------------------
6780: --
6781: begin
6782: if g_debug then
6783: l_proc := g_package||'chk_vendor_id';
6784: hr_utility.set_location('Entering:'|| l_proc, 10);
6785: end if;
6786: --
6787: -- Check if the person is being updated
6788: --
6790: (p_person_id => p_person_id
6791: ,p_effective_date => p_effective_date
6792: ,p_object_version_number => p_object_version_number);
6793: if g_debug then
6794: hr_utility.set_location(l_proc, 20);
6795: end if;
6796: --
6797: if ((l_api_updating and
6798: nvl(per_per_shd.g_old_rec.vendor_id, hr_api.g_number) <>
6799: nvl(p_vendor_id, hr_api.g_number)) or
6800: (NOT l_api_updating)) then
6801: --
6802: if g_debug then
6803: hr_utility.set_location(l_proc, 30);
6804: end if;
6805: --
6806: -- Check the system person type and validate as appropriate.
6807: --
6812: -- in the PO_VENDORS table.
6813: --
6814: if p_vendor_id is not null then
6815: if g_debug then
6816: hr_utility.set_location(l_proc, 40);
6817: end if;
6818: open csr_chk_vendor;
6819: fetch csr_chk_vendor into l_exists;
6820: if csr_chk_vendor%notfound then
6821: close csr_chk_vendor;
6822: --
6823: -- Error : Invalid vendor id
6824: --
6825: hr_utility.set_message(801,'HR_51249_PER_INVALID_VENDOR');
6826: hr_utility.raise_error;
6827: end if;
6828: close csr_chk_vendor;
6829: if g_debug then
6822: --
6823: -- Error : Invalid vendor id
6824: --
6825: hr_utility.set_message(801,'HR_51249_PER_INVALID_VENDOR');
6826: hr_utility.raise_error;
6827: end if;
6828: close csr_chk_vendor;
6829: if g_debug then
6830: hr_utility.set_location(l_proc, 50);
6826: hr_utility.raise_error;
6827: end if;
6828: close csr_chk_vendor;
6829: if g_debug then
6830: hr_utility.set_location(l_proc, 50);
6831: end if;
6832: end if;
6833: else
6834: --
6832: end if;
6833: else
6834: --
6835: if g_debug then
6836: hr_utility.set_location(l_proc, 60);
6837: end if;
6838: --
6839: -- Vendor id must be null.
6840: --
6841: if p_vendor_id is not null then
6842: --
6843: -- Error : Vendor must be null
6844: --
6845: hr_utility.set_message(801, 'HR_51250_PER_VENDOR_NOT_NULL');
6846: hr_utility.raise_error;
6847: end if;
6848: if g_debug then
6849: hr_utility.set_location(l_proc, 70);
6842: --
6843: -- Error : Vendor must be null
6844: --
6845: hr_utility.set_message(801, 'HR_51250_PER_VENDOR_NOT_NULL');
6846: hr_utility.raise_error;
6847: end if;
6848: if g_debug then
6849: hr_utility.set_location(l_proc, 70);
6850: end if;
6845: hr_utility.set_message(801, 'HR_51250_PER_VENDOR_NOT_NULL');
6846: hr_utility.raise_error;
6847: end if;
6848: if g_debug then
6849: hr_utility.set_location(l_proc, 70);
6850: end if;
6851: end if;
6852: end if;
6853: --
6851: end if;
6852: end if;
6853: --
6854: if g_debug then
6855: hr_utility.set_location(' Leaving:'|| l_proc, 80);
6856: end if;
6857: exception
6858: when app_exception.application_exception then
6859: if hr_multi_message.exception_add
6859: if hr_multi_message.exception_add
6860: (p_associated_column1 => 'PER_ALL_PEOPLE_F.VENDOR_ID'
6861: ) then
6862: if g_debug then
6863: hr_utility.set_location(' Leaving:'||l_proc, 90);
6864: end if;
6865: raise;
6866: end if;
6867: if g_debug then
6864: end if;
6865: raise;
6866: end if;
6867: if g_debug then
6868: hr_utility.set_location(' Leaving:'||l_proc,100);
6869: end if;
6870: end chk_vendor_id;
6871: --
6872: -- ---------------------------------------------------------------------------
6907: l_api_updating boolean;
6908: --
6909: begin
6910: if g_debug then
6911: hr_utility.set_location('Entering:'|| l_proc, 10);
6912: end if;
6913: --
6914: -- Check mandatory parameters have been set
6915: --
6919: ,p_argument_value => p_effective_date
6920: );
6921: --
6922: if g_debug then
6923: hr_utility.set_location(l_proc, 20);
6924: end if;
6925: --
6926: -- Only proceed with validation if:
6927: -- a) The current g_old_rec is current and
6933: ,p_effective_date => p_effective_date
6934: ,p_object_version_number => p_object_version_number
6935: );
6936: if g_debug then
6937: hr_utility.set_location(l_proc, 30);
6938: end if;
6939: --
6940: if ((l_api_updating
6941: and nvl(per_per_shd.g_old_rec.suffix, hr_api.g_varchar2)
6943: or
6944: (NOT l_api_updating))
6945: then
6946: if g_debug then
6947: hr_utility.set_location(l_proc, 40);
6948: end if;
6949: --
6950: end if;
6951: if g_debug then
6948: end if;
6949: --
6950: end if;
6951: if g_debug then
6952: hr_utility.set_location(' Leaving:'|| l_proc, 70);
6953: end if;
6954: end chk_suffix;
6955: --
6956: -- ---------------------------------------------------------------------------
6989: --
6990: begin
6991: if g_debug then
6992: l_proc := g_package||'chk_work_telephone';
6993: hr_utility.set_location('Entering:'|| l_proc, 10);
6994: end if;
6995:
6996: l_api_updating := per_per_shd.api_updating
6997: (p_person_id => p_person_id
6998: ,p_effective_date => p_effective_date
6999: ,p_object_version_number => p_object_version_number
7000: );
7001: if g_debug then
7002: hr_utility.set_location(l_proc, 30);
7003: end if;
7004: --
7005: if ((l_api_updating
7006: and nvl(per_per_shd.g_old_rec.work_telephone, hr_api.g_varchar2)
7011: --
7012: -- Check parameter is null
7013: --
7014: if g_debug then
7015: hr_utility.set_location('Work Number is:'|| p_work_telephone, 15);
7016: end if;
7017: if p_work_telephone is not null then
7018: hr_utility.set_message(801, 'HR_52217_PER_INVALID_PHONE');
7019: hr_utility.raise_error;
7014: if g_debug then
7015: hr_utility.set_location('Work Number is:'|| p_work_telephone, 15);
7016: end if;
7017: if p_work_telephone is not null then
7018: hr_utility.set_message(801, 'HR_52217_PER_INVALID_PHONE');
7019: hr_utility.raise_error;
7020: end if;
7021: end if;
7022: if g_debug then
7015: hr_utility.set_location('Work Number is:'|| p_work_telephone, 15);
7016: end if;
7017: if p_work_telephone is not null then
7018: hr_utility.set_message(801, 'HR_52217_PER_INVALID_PHONE');
7019: hr_utility.raise_error;
7020: end if;
7021: end if;
7022: if g_debug then
7023: hr_utility.set_location(' Leaving:'|| l_proc, 70);
7019: hr_utility.raise_error;
7020: end if;
7021: end if;
7022: if g_debug then
7023: hr_utility.set_location(' Leaving:'|| l_proc, 70);
7024: end if;
7025: exception
7026: when app_exception.application_exception then
7027: if hr_multi_message.exception_add
7027: if hr_multi_message.exception_add
7028: (p_associated_column1 => 'PER_ALL_PEOPLE_F.WORK_TELEPHONE'
7029: ) then
7030: if g_debug then
7031: hr_utility.set_location(' Leaving:'||l_proc, 80);
7032: end if;
7033: raise;
7034: end if;
7035: if g_debug then
7032: end if;
7033: raise;
7034: end if;
7035: if g_debug then
7036: hr_utility.set_location(' Leaving:'||l_proc,90);
7037: end if;
7038: end chk_work_telephone;
7039: --
7040: -- Fix 3573040
7075: begin
7076: -- Bug fix 38383715. Modified if condition.
7077: if p_per_information_category is not null
7078: and p_per_information_category <> p_legislation_code then
7079: hr_utility.set_message( 800, 'PER_449162_INFO_CATEGORY' );
7080: hr_utility.raise_error;
7081: else
7082: PER_PEOPLE3_PKG.get_ddf_exists
7083: ( p_legislation_code => p_legislation_code
7076: -- Bug fix 38383715. Modified if condition.
7077: if p_per_information_category is not null
7078: and p_per_information_category <> p_legislation_code then
7079: hr_utility.set_message( 800, 'PER_449162_INFO_CATEGORY' );
7080: hr_utility.raise_error;
7081: else
7082: PER_PEOPLE3_PKG.get_ddf_exists
7083: ( p_legislation_code => p_legislation_code
7084: , p_ddf_exists => l_ddf_exists );
7153: );
7154: --
7155: begin
7156: if g_debug then
7157: hr_utility.set_location('Entering:'|| l_proc, 10);
7158: end if;
7159: --
7160: -- Check mandatory parameters have been set
7161: --
7182: ,p_argument => 'datetrack mode'
7183: ,p_argument_value => p_datetrack_mode
7184: );
7185: if g_debug then
7186: hr_utility.set_location(l_proc, 20);
7187: end if;
7188: --
7189: -- If delete mode is DELETE_NEXT_CHANGE or FUTURE_CHANGE
7190: -- check if rows exist in PER_PEOPLE_F where the system person type has
7201: fetch csr_chk_future_changes into l_exists;
7202: if csr_chk_future_changes%found then
7203: close csr_chk_future_changes;
7204: -- Error: Delete not allowed
7205: hr_utility.set_message(801, 'HR_7726_PER_DEL_NOT_ALLOWED');
7206: hr_utility.raise_error;
7207: end if;
7208: close csr_chk_future_changes;
7209: if g_debug then
7202: if csr_chk_future_changes%found then
7203: close csr_chk_future_changes;
7204: -- Error: Delete not allowed
7205: hr_utility.set_message(801, 'HR_7726_PER_DEL_NOT_ALLOWED');
7206: hr_utility.raise_error;
7207: end if;
7208: close csr_chk_future_changes;
7209: if g_debug then
7210: hr_utility.set_location(l_proc, 20);
7206: hr_utility.raise_error;
7207: end if;
7208: close csr_chk_future_changes;
7209: if g_debug then
7210: hr_utility.set_location(l_proc, 20);
7211: end if;
7212: end if;
7213: --
7214: if g_debug then
7211: end if;
7212: end if;
7213: --
7214: if g_debug then
7215: hr_utility.set_location(l_proc, 3);
7216: end if;
7217: --
7218: -- If delete mode in ('ZAP','DELETE','DELETE_NEXT_CHANGE',
7219: -- 'FUTURE_CHANGE') then
7245: --
7246: end if;
7247: --
7248: if g_debug then
7249: hr_utility.set_location(' Leaving:'|| l_proc, 4);
7250: end if;
7251: end chk_delete;
7252: --
7253: -- ----------------------------------------------------------------------------
7286: l_proc varchar2(72) := g_package||'check_non_updateable_args';
7287: --
7288: Begin
7289: if g_debug then
7290: hr_utility.set_location('Entering:'||l_proc, 5);
7291: end if;
7292: --
7293: -- Only proceed with validation if a row exists for
7294: -- the current record in the HR Schema
7296: if not per_per_shd.api_updating
7297: (p_person_id => p_rec.person_id
7298: ,p_effective_date => p_effective_date
7299: ,p_object_version_number => p_rec.object_version_number) then
7300: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7301: hr_utility.set_message_token('PROCEDURE', l_proc);
7302: hr_utility.set_message_token('STEP', '5');
7303: end if;
7304: --
7297: (p_person_id => p_rec.person_id
7298: ,p_effective_date => p_effective_date
7299: ,p_object_version_number => p_rec.object_version_number) then
7300: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7301: hr_utility.set_message_token('PROCEDURE', l_proc);
7302: hr_utility.set_message_token('STEP', '5');
7303: end if;
7304: --
7305: if g_debug then
7298: ,p_effective_date => p_effective_date
7299: ,p_object_version_number => p_rec.object_version_number) then
7300: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7301: hr_utility.set_message_token('PROCEDURE', l_proc);
7302: hr_utility.set_message_token('STEP', '5');
7303: end if;
7304: --
7305: if g_debug then
7306: hr_utility.set_location(l_proc, 6);
7302: hr_utility.set_message_token('STEP', '5');
7303: end if;
7304: --
7305: if g_debug then
7306: hr_utility.set_location(l_proc, 6);
7307: end if;
7308: --
7309: if nvl(p_rec.business_group_id, hr_api.g_number) <>
7310: nvl(per_per_shd.g_old_rec.business_group_id, hr_api.g_number) then
7315: ,p_base_table => per_per_shd.g_tab_nam
7316: );
7317: end if;
7318: if g_debug then
7319: hr_utility.set_location(' Leaving '||l_proc, 7);
7320: end if;
7321: --
7322: end check_non_updateable_args;
7323: --
7361: l_table_name all_tables.table_name%TYPE;
7362: --
7363: Begin
7364: if g_debug then
7365: hr_utility.set_location('Entering:'||l_proc, 5);
7366: end if;
7367: --
7368: -- Ensure that the p_datetrack_mode argument is not null
7369: --
7393: --
7394: End If;
7395: --
7396: if g_debug then
7397: hr_utility.set_location(' Leaving:'||l_proc, 10);
7398: end if;
7399: Exception
7400: /*When l_integrity_error Then
7401: --
7401: --
7402: -- A referential integrity check was violated therefore
7403: -- we must error
7404: --
7405: hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
7406: hr_utility.set_message_token('TABLE_NAME', l_table_name);
7407: hr_utility.raise_error;*/
7408: When Others Then
7409: --
7402: -- A referential integrity check was violated therefore
7403: -- we must error
7404: --
7405: hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
7406: hr_utility.set_message_token('TABLE_NAME', l_table_name);
7407: hr_utility.raise_error;*/
7408: When Others Then
7409: --
7410: -- An unhandled or unexpected error has occurred which
7403: -- we must error
7404: --
7405: hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
7406: hr_utility.set_message_token('TABLE_NAME', l_table_name);
7407: hr_utility.raise_error;*/
7408: When Others Then
7409: --
7410: -- An unhandled or unexpected error has occurred which
7411: -- we must report
7409: --
7410: -- An unhandled or unexpected error has occurred which
7411: -- we must report
7412: --
7413: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7414: hr_utility.set_message_token('PROCEDURE', l_proc);
7415: hr_utility.set_message_token('STEP','15');
7416: hr_utility.raise_error;
7417: End dt_update_validate;
7410: -- An unhandled or unexpected error has occurred which
7411: -- we must report
7412: --
7413: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7414: hr_utility.set_message_token('PROCEDURE', l_proc);
7415: hr_utility.set_message_token('STEP','15');
7416: hr_utility.raise_error;
7417: End dt_update_validate;
7418: --
7411: -- we must report
7412: --
7413: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7414: hr_utility.set_message_token('PROCEDURE', l_proc);
7415: hr_utility.set_message_token('STEP','15');
7416: hr_utility.raise_error;
7417: End dt_update_validate;
7418: --
7419: -- ----------------------------------------------------------------------------
7412: --
7413: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7414: hr_utility.set_message_token('PROCEDURE', l_proc);
7415: hr_utility.set_message_token('STEP','15');
7416: hr_utility.raise_error;
7417: End dt_update_validate;
7418: --
7419: -- ----------------------------------------------------------------------------
7420: -- |--------------------------< dt_delete_validate >--------------------------|
7462: l_table_name all_tables.table_name%TYPE;
7463: --
7464: Begin
7465: if g_debug then
7466: hr_utility.set_location('Entering:'||l_proc, 5);
7467: end if;
7468: --
7469: -- Ensure that the p_datetrack_mode argument is not null
7470: --
7504: p_base_key_value => p_person_id,
7505: p_from_date => p_validation_start_date,
7506: p_to_date => p_validation_end_date)) then
7507: l_table_name := 'contracts';
7508: hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
7509: hr_utility.set_message_token('TABLE_NAME', l_table_name);
7510: hr_multi_message.add;
7511: -- raise l_rows_exist;
7512: end if;
7505: p_from_date => p_validation_start_date,
7506: p_to_date => p_validation_end_date)) then
7507: l_table_name := 'contracts';
7508: hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
7509: hr_utility.set_message_token('TABLE_NAME', l_table_name);
7510: hr_multi_message.add;
7511: -- raise l_rows_exist;
7512: end if;
7513: --
7514: --
7515: End If;
7516: --
7517: if g_debug then
7518: hr_utility.set_location(' Leaving:'||l_proc, 10);
7519: end if;
7520: Exception
7521: /*When l_rows_exist Then
7522: --
7522: --
7523: -- A referential integrity check was violated therefore
7524: -- we must error
7525: --
7526: hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
7527: hr_utility.set_message_token('TABLE_NAME', l_table_name);
7528: hr_utility.raise_error;*/
7529: When Others Then
7530: --
7523: -- A referential integrity check was violated therefore
7524: -- we must error
7525: --
7526: hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
7527: hr_utility.set_message_token('TABLE_NAME', l_table_name);
7528: hr_utility.raise_error;*/
7529: When Others Then
7530: --
7531: -- An unhandled or unexpected error has occurred which
7524: -- we must error
7525: --
7526: hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
7527: hr_utility.set_message_token('TABLE_NAME', l_table_name);
7528: hr_utility.raise_error;*/
7529: When Others Then
7530: --
7531: -- An unhandled or unexpected error has occurred which
7532: -- we must report
7530: --
7531: -- An unhandled or unexpected error has occurred which
7532: -- we must report
7533: --
7534: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7535: hr_utility.set_message_token('PROCEDURE', l_proc);
7536: hr_utility.set_message_token('STEP','15');
7537: hr_utility.raise_error;
7538: End dt_delete_validate;
7531: -- An unhandled or unexpected error has occurred which
7532: -- we must report
7533: --
7534: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7535: hr_utility.set_message_token('PROCEDURE', l_proc);
7536: hr_utility.set_message_token('STEP','15');
7537: hr_utility.raise_error;
7538: End dt_delete_validate;
7539: --
7532: -- we must report
7533: --
7534: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7535: hr_utility.set_message_token('PROCEDURE', l_proc);
7536: hr_utility.set_message_token('STEP','15');
7537: hr_utility.raise_error;
7538: End dt_delete_validate;
7539: --
7540: -- -----------------------------------------------------------------------
7533: --
7534: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
7535: hr_utility.set_message_token('PROCEDURE', l_proc);
7536: hr_utility.set_message_token('STEP','15');
7537: hr_utility.raise_error;
7538: End dt_delete_validate;
7539: --
7540: -- -----------------------------------------------------------------------
7541: -- |------------------------------< chk_df >-----------------------------|
7571: --
7572: begin
7573: if g_debug then
7574: l_proc := g_package||'chk_df';
7575: hr_utility.set_location('Entering:'||l_proc, 10);
7576: end if;
7577: --
7578: if ((p_rec.person_id is not null) and (
7579: nvl(per_per_shd.g_old_rec.attribute_category, hr_api.g_varchar2) <>
7712: );
7713: end if;
7714: --
7715: if g_debug then
7716: hr_utility.set_location(' Leaving:'||l_proc, 20);
7717: end if;
7718:
7719: end chk_df;
7720: --
7757: --
7758: Begin
7759: if g_debug then
7760: l_proc := g_package||'insert_validate';
7761: hr_utility.set_location('Entering:'||l_proc, 10);
7762: end if;
7763: --
7764: -- Set global variable used to improve calling of return_system_status_type
7765: -- when called multiple times on same pass through the validation process.
7790: ,p_associated_column1 => per_per_shd.g_tab_nam || '.BUSINESS_GROUP_ID'
7791: );
7792: hr_multi_message.end_validation_set;
7793: if g_debug then
7794: hr_utility.set_location(l_proc, 20);
7795: end if;
7796: --
7797: -- Validate Correspondence Language
7798: --
7835: ,p_validation_end_date => p_validation_end_date
7836: ,p_object_version_number => p_rec.object_version_number
7837: );
7838: if g_debug then
7839: hr_utility.set_location(l_proc, 30);
7840: end if;
7841: --
7842: -- Validate Blood Type
7843: --
7849: ,p_validation_end_date => p_validation_end_date
7850: ,p_object_version_number => p_rec.object_version_number
7851: );
7852: if g_debug then
7853: hr_utility.set_location(l_proc, 40);
7854: end if;
7855: --
7856: -- Validate Student Status
7857: --
7863: ,p_validation_end_date => p_validation_end_date
7864: ,p_object_version_number => p_rec.object_version_number
7865: );
7866: if g_debug then
7867: hr_utility.set_location(l_proc, 50);
7868: end if;
7869: --
7870: -- Validate Work Schedule
7871: --
7877: ,p_validation_end_date => p_validation_end_date
7878: ,p_object_version_number => p_rec.object_version_number
7879: );
7880: if g_debug then
7881: hr_utility.set_location(l_proc, 60);
7882: end if;
7883: --
7884: -- Validate Rehire Recommendation
7885: --
7891: ,p_validation_end_date => p_validation_end_date
7892: ,p_object_version_number => p_rec.object_version_number
7893: );
7894: if g_debug then
7895: hr_utility.set_location(l_proc, 70);
7896: end if;
7897: --
7898: -- Validate Benefit Group Id
7899: --
7903: ,p_effective_date => p_effective_date
7904: ,p_object_version_number => p_rec.object_version_number
7905: );
7906: if g_debug then
7907: hr_utility.set_location(l_proc, 80);
7908: end if;
7909: --
7910: -- Validate Receipt of Death Certificate date.
7911: --
7916: ,p_object_version_number => p_rec.object_version_number
7917: ,p_date_of_death => p_rec.date_of_death
7918: );
7919: if g_debug then
7920: hr_utility.set_location(l_proc, 90);
7921: end if;
7922: --
7923: -- Validate the dependent's adoption date.
7924: --
7929: ,p_effective_date => p_effective_date
7930: ,p_object_version_number => p_rec.object_version_number
7931: );
7932: if g_debug then
7933: hr_utility.set_location(l_proc, 100);
7934: end if;
7935: --
7936: -- Validate registered disabled flag.
7937: --
7943: ,p_validation_end_date => p_validation_end_date
7944: ,p_object_version_number => p_rec.object_version_number
7945: );
7946: if g_debug then
7947: hr_utility.set_location(l_proc, 110);
7948: end if;
7949: --
7950: -- Validate Date of Death.
7951: --
7956: ,p_effective_date => p_effective_date
7957: ,p_object_version_number => p_rec.object_version_number
7958: );
7959: if g_debug then
7960: hr_utility.set_location(l_proc, 120);
7961: end if;
7962: --
7963: -- Validate uses tobacco flag.
7964: --
7970: ,p_validation_end_date => p_validation_end_date
7971: ,p_object_version_number => p_rec.object_version_number
7972: );
7973: if g_debug then
7974: hr_utility.set_location(l_proc, 125);
7975: end if;
7976:
7977: --
7978: chk_orig_and_start_dates
7985: ,p_object_version_number => p_rec.object_version_number
7986: ,p_orig_hire_warning => p_orig_hire_warning
7987: );
7988: if g_debug then
7989: hr_utility.set_location(l_proc, 130);
7990: end if;
7991: --
7992: -- Validate Person Type ID
7993: --
8004: ,p_object_version_number => p_rec.object_version_number
8005: ,p_datetrack_mode => p_datetrack_mode
8006: );
8007: if g_debug then
8008: hr_utility.set_location(l_proc, 30);
8009: end if;
8010: --
8011: -- Set current flags
8012: --
8020: ,p_effective_date => p_effective_date
8021: ,p_object_version_number => p_rec.object_version_number
8022: );
8023: if g_debug then
8024: hr_utility.set_location(l_proc, 40);
8025: end if;
8026: --
8027: -- Validate National Identifier
8028: --
8047:
8048: );
8049: --
8050: if g_debug then
8051: hr_utility.set_location(l_proc, 50);
8052: end if;
8053: --
8054: -- Validate Employee Number
8055: --
8065: ,p_date_of_birth => p_rec.date_of_birth
8066: ,p_start_date => p_rec.effective_start_date
8067: );
8068: if g_debug then
8069: hr_utility.set_location(l_proc, 60);
8070: end if;
8071: --
8072: chk_npw_number
8073: (p_person_id => p_rec.person_id
8081: ,p_date_of_birth => p_rec.date_of_birth
8082: ,p_start_date => p_rec.effective_start_date
8083: );
8084: if g_debug then
8085: hr_utility.set_location(l_proc, 65);
8086: end if;
8087: --
8088: chk_expense_check_send_to_addr
8089: (p_person_id => p_rec.person_id
8093: ,p_validation_end_date => p_validation_end_date
8094: ,p_object_version_number => p_rec.object_version_number
8095: );
8096: if g_debug then
8097: hr_utility.set_location(l_proc, 70);
8098: end if;
8099: --
8100: -- Validate Start Date
8101: --
8105: ,p_effective_date => p_effective_date
8106: ,p_object_version_number => p_rec.object_version_number
8107: );
8108: if g_debug then
8109: hr_utility.set_location(l_proc, 80);
8110: end if;
8111: --
8112: -- Validate Nationality
8113: --
8119: ,p_validation_end_date => p_validation_end_date
8120: ,p_object_version_number => p_rec.object_version_number
8121: );
8122: if g_debug then
8123: hr_utility.set_location(l_proc, 90);
8124: end if;
8125: --
8126: -- Validate Marital Status
8127: --
8133: ,p_validation_end_date => p_validation_end_date
8134: ,p_object_version_number => p_rec.object_version_number
8135: );
8136: if g_debug then
8137: hr_utility.set_location(l_proc, 100);
8138: end if;
8139: --
8140: chk_party_id
8141: (p_person_id => p_rec.person_id
8156: ,p_validation_end_date => p_validation_end_date
8157: ,p_object_version_number => p_rec.object_version_number
8158: );
8159: if g_debug then
8160: hr_utility.set_location(l_proc, 110);
8161: end if;
8162: --
8163: -- Validate Date of Birth
8164: --
8300: ,p_name_combination_warning => p_name_combination_warning
8301: );
8302:
8303: if g_debug then
8304: hr_utility.set_location(l_proc, 130);
8305: end if;
8306: /* Bug#3613987 - Removed chk_JP_names procedure
8307: --
8308: -- Create full_name for JP legislation
8321: --
8322: end if;
8323: */
8324: if g_debug then
8325: hr_utility.set_location(l_proc, 145);
8326: end if;
8327: --
8328: -- Validate Applicant Number
8329: --
8339: ,p_date_of_birth => p_rec.date_of_birth
8340: ,p_start_date => null
8341: );
8342: if g_debug then
8343: hr_utility.set_location(l_proc, 150);
8344: end if;
8345: --
8346: -- Validate Date Employee Data Verified
8347: --
8351: ,p_effective_start_date => p_validation_start_date
8352: ,p_object_version_number => p_rec.object_version_number
8353: );
8354: if g_debug then
8355: hr_utility.set_location(l_proc, 160);
8356: end if;
8357: --
8358: --
8359: -- Validate Vendor Id
8366: ,p_effective_date => p_effective_date
8367: ,p_object_version_number => p_rec.object_version_number
8368: );
8369: if g_debug then
8370: hr_utility.set_location(l_proc, 180);
8371: end if;
8372: --
8373: chk_suffix
8374: (p_person_id => p_rec.person_id
8376: ,p_effective_date => p_effective_date
8377: ,p_object_version_number => p_rec.object_version_number
8378: );
8379: if g_debug then
8380: hr_utility.set_location(l_proc, 190);
8381: end if;
8382: --
8383: -- Validate work telephone
8384: --
8425: ,p_validation_start_date => p_validation_start_date
8426: ,p_validation_end_date => p_validation_end_date
8427: );
8428: if g_debug then
8429: hr_utility.set_location(l_proc, 200);
8430: end if;
8431: --
8432: -- Validate flex fields.
8433: --
8432: -- Validate flex fields.
8433: --
8434: per_per_bus.chk_df(p_rec => p_rec);
8435: if g_debug then
8436: hr_utility.set_location(l_proc, 210);
8437: end if;
8438: --
8439: if g_debug then
8440: hr_utility.set_location(' Leaving:'||l_proc, 10000);
8436: hr_utility.set_location(l_proc, 210);
8437: end if;
8438: --
8439: if g_debug then
8440: hr_utility.set_location(' Leaving:'||l_proc, 10000);
8441: end if;
8442: End insert_validate;
8443: --
8444: -- ----------------------------------------------------------------------------
8470: --
8471: Begin
8472: if g_debug then
8473: l_proc := g_package||'update_validate';
8474: hr_utility.set_location('Entering:'||l_proc, 10);
8475: end if;
8476: --
8477: -- Set global variable used to improve calling of return_system_status_type
8478: -- when called multiple times on same pass through the validation process.
8528: ,p_validation_end_date => p_validation_end_date
8529: ,p_object_version_number => p_rec.object_version_number
8530: );
8531: if g_debug then
8532: hr_utility.set_location(l_proc, 30);
8533: end if;
8534: --
8535: -- Validate Blood Type
8536: --
8542: ,p_validation_end_date => p_validation_end_date
8543: ,p_object_version_number => p_rec.object_version_number
8544: );
8545: if g_debug then
8546: hr_utility.set_location(l_proc, 40);
8547: end if;
8548: --
8549: -- Validate Student Status
8550: --
8556: ,p_validation_end_date => p_validation_end_date
8557: ,p_object_version_number => p_rec.object_version_number
8558: );
8559: if g_debug then
8560: hr_utility.set_location(l_proc, 50);
8561: end if;
8562: --
8563: -- Validate Work Schedule
8564: --
8570: ,p_validation_end_date => p_validation_end_date
8571: ,p_object_version_number => p_rec.object_version_number
8572: );
8573: if g_debug then
8574: hr_utility.set_location(l_proc, 60);
8575: end if;
8576: --
8577: -- Validate Rehire Recommendation
8578: --
8584: ,p_validation_end_date => p_validation_end_date
8585: ,p_object_version_number => p_rec.object_version_number
8586: );
8587: if g_debug then
8588: hr_utility.set_location(l_proc, 70);
8589: end if;
8590: --
8591: -- Validate Benefit Group Id
8592: --
8596: ,p_effective_date => p_effective_date
8597: ,p_object_version_number => p_rec.object_version_number
8598: );
8599: if g_debug then
8600: hr_utility.set_location(l_proc, 80);
8601: end if;
8602: --
8603: -- Validate Receipt of Death Certificate date.
8604: --
8609: ,p_object_version_number => p_rec.object_version_number
8610: ,p_date_of_death => p_rec.date_of_death
8611: );
8612: if g_debug then
8613: hr_utility.set_location(l_proc, 90);
8614: end if;
8615: --
8616: -- Validate the dependent's adoption date.
8617: --
8622: ,p_effective_date => p_effective_date
8623: ,p_object_version_number => p_rec.object_version_number
8624: );
8625: if g_debug then
8626: hr_utility.set_location(l_proc, 100);
8627: end if;
8628: --
8629: -- Validate registered disabled flag.
8630: --
8636: ,p_validation_end_date => p_validation_end_date
8637: ,p_object_version_number => p_rec.object_version_number
8638: );
8639: if g_debug then
8640: hr_utility.set_location(l_proc, 110);
8641: end if;
8642: --
8643: -- Validate Date of Death.
8644: --
8649: ,p_effective_date => p_effective_date
8650: ,p_object_version_number => p_rec.object_version_number
8651: );
8652: if g_debug then
8653: hr_utility.set_location(l_proc, 120);
8654: end if;
8655: --
8656: -- Validate uses tobacco flag.
8657: --
8663: ,p_validation_end_date => p_validation_end_date
8664: ,p_object_version_number => p_rec.object_version_number
8665: );
8666: if g_debug then
8667: hr_utility.set_location(l_proc, 125);
8668: end if;
8669: --
8670: chk_orig_and_start_dates
8671: (p_person_id => p_rec.person_id
8677: ,p_object_version_number => p_rec.object_version_number
8678: ,p_orig_hire_warning => p_orig_hire_warning
8679: );
8680: if g_debug then
8681: hr_utility.set_location(l_proc, 130);
8682: end if;
8683: --
8684: -- Call all supporting business operations
8685: -- Mapping to the appropriate Business Rules in perper.bru is provided.
8699: ,p_object_version_number => p_rec.object_version_number
8700: ,p_datetrack_mode => p_datetrack_mode
8701: );
8702: if g_debug then
8703: hr_utility.set_location(l_proc, 20);
8704: end if;
8705: --
8706: --
8707: -- Validate National Identifier
8726: ,p_nationality => p_rec.nationality
8727: );
8728: --
8729: if g_debug then
8730: hr_utility.set_location(l_proc, 30);
8731: end if;
8732: --
8733: -- Validate Employee Number
8734: --
8744: ,p_date_of_birth => p_rec.date_of_birth
8745: ,p_start_date => null
8746: );
8747: if g_debug then
8748: hr_utility.set_location(l_proc, 40);
8749: end if;
8750: --
8751: chk_npw_number
8752: (p_person_id => p_rec.person_id
8760: ,p_date_of_birth => p_rec.date_of_birth
8761: ,p_start_date => null
8762: );
8763: if g_debug then
8764: hr_utility.set_location(l_proc, 43);
8765: end if;
8766: --
8767: chk_expense_check_send_to_addr
8768: (p_person_id => p_rec.person_id
8772: ,p_validation_end_date => p_validation_end_date
8773: ,p_object_version_number => p_rec.object_version_number
8774: );
8775: if g_debug then
8776: hr_utility.set_location('Entering:'||l_proc, 45);
8777: end if;
8778: --
8779: -- Validate Start Date
8780: --
8784: ,p_effective_date => p_effective_date
8785: ,p_object_version_number => p_rec.object_version_number
8786: );
8787: if g_debug then
8788: hr_utility.set_location(l_proc, 50);
8789: end if;
8790: --
8791: -- Validate Nationality
8792: --
8798: ,p_validation_end_date => p_validation_end_date
8799: ,p_object_version_number => p_rec.object_version_number
8800: );
8801: if g_debug then
8802: hr_utility.set_location(l_proc, 60);
8803: end if;
8804: --
8805: chk_party_id
8806: (p_person_id => p_rec.person_id
8818: ,p_validation_end_date => p_validation_end_date
8819: ,p_object_version_number => p_rec.object_version_number
8820: );
8821: if g_debug then
8822: hr_utility.set_location(l_proc, 70);
8823: end if;
8824: --
8825: -- Validate Sex and Title
8826: --
8835: ,p_validation_end_date => p_validation_end_date
8836: ,p_object_version_number => p_rec.object_version_number
8837: );
8838: if g_debug then
8839: hr_utility.set_location(l_proc, 80);
8840: end if;
8841: --
8842: -- Validate Date of Birth
8843: --
8853: ,p_validation_end_date => p_validation_end_date
8854: ,p_object_version_number => p_rec.object_version_number
8855: );
8856: if g_debug then
8857: hr_utility.set_location(l_proc, 90);
8858: end if;
8859: --
8860: -- Validate Town of Birth
8861: --
8981: ,p_name_combination_warning => p_name_combination_warning
8982: );
8983:
8984: if g_debug then
8985: hr_utility.set_location(l_proc, 100);
8986: end if;
8987: --
8988: -- Validate benefit medical coverage dates
8989: --
9022: ,p_validation_end_date => p_validation_end_date
9023: );
9024: --
9025: if g_debug then
9026: hr_utility.set_location(l_proc, 110);
9027: end if;
9028: /* Bug#3613987 - Removed chk_JP_names procedure
9029: --
9030: -- Create full_name for JP legislation
9043: --
9044: end if;
9045: */
9046: if g_debug then
9047: hr_utility.set_location(l_proc, 145);
9048: end if;
9049: --
9050: -- Validate Applicant Number
9051: --
9061: ,p_date_of_birth => p_rec.date_of_birth
9062: ,p_start_date => null
9063: );
9064: if g_debug then
9065: hr_utility.set_location(l_proc, 120);
9066: end if;
9067: --
9068: -- Validate Date Employee Data Verified
9069: --
9073: ,p_effective_start_date => p_validation_start_date
9074: ,p_object_version_number => p_rec.object_version_number
9075: );
9076: if g_debug then
9077: hr_utility.set_location(l_proc, 130);
9078: end if;
9079: --
9080: -- Validate Vendor Id
9081: --
9087: ,p_effective_date => p_effective_date
9088: ,p_object_version_number => p_rec.object_version_number
9089: );
9090: if g_debug then
9091: hr_utility.set_location(l_proc, 150);
9092: end if;
9093: --
9094: chk_suffix
9095: (p_person_id => p_rec.person_id
9097: ,p_effective_date => p_effective_date
9098: ,p_object_version_number => p_rec.object_version_number
9099: );
9100: if g_debug then
9101: hr_utility.set_location(l_proc, 160);
9102: end if;
9103: --
9104: -- Validate work telephone
9105: --
9117: p_validation_start_date => p_validation_start_date,
9118: p_validation_end_date => p_validation_end_date
9119: );
9120: if g_debug then
9121: hr_utility.set_location(l_proc, 170);
9122: end if;
9123: --
9124: --
9125: -- Validate flex fields.
9125: -- Validate flex fields.
9126: --
9127: per_per_bus.chk_df(p_rec => p_rec);
9128: if g_debug then
9129: hr_utility.set_location(l_proc, 180);
9130: end if;
9131: --
9132: if g_debug then
9133: hr_utility.set_location(' Leaving:'||l_proc, 10000);
9129: hr_utility.set_location(l_proc, 180);
9130: end if;
9131: --
9132: if g_debug then
9133: hr_utility.set_location(' Leaving:'||l_proc, 10000);
9134: end if;
9135: End update_validate;
9136: --
9137: -- ----------------------------------------------------------------------------
9148: --
9149: Begin
9150: if g_debug then
9151: l_proc := g_package||'delete_validate';
9152: hr_utility.set_location('Entering:'||l_proc, 5);
9153: end if;
9154: --
9155: -- Set global variable used to improve calling of return_system_status_type
9156: -- when called multiple times on same pass through the validation process.
9178: p_validation_end_date => p_validation_end_date,
9179: p_person_id => p_rec.person_id);
9180: --
9181: if g_debug then
9182: hr_utility.set_location(' Leaving:'||l_proc, 10);
9183: end if;
9184: End delete_validate;
9185: --
9186: -- ---------------------------------------------------------------------------|
9215: --
9216: begin
9217: if g_debug then
9218: l_proc := 'chk_person_type';
9219: hr_utility.set_location(' Entering '||l_proc,10);
9220: end if;
9221: --
9222: -- Validate the specified business group
9223: --
9226: ,p_associated_column1 => per_per_shd.g_tab_nam || '.BUSINESS_GROUP_ID'
9227: );
9228: hr_multi_message.end_validation_set;
9229: if g_debug then
9230: hr_utility.set_location(l_proc,20);
9231: end if;
9232: --
9233: -- If the person type hasn't been specified or is set to the system default
9234: -- hr_api.g_number, then fetch the default for
9235: -- the business group and system type.
9236: --
9237: if (p_person_type_id is null or p_person_type_id = hr_api.g_number) then
9238: if g_debug then
9239: hr_utility.set_location(l_proc,30);
9240: end if;
9241: --
9242: -- Get the required default person type
9243: --
9245: fetch csr_get_default
9246: into p_person_type_id;
9247: if csr_get_default%notfound then
9248: close csr_get_default;
9249: hr_utility.set_message(801, 'HR_7972_PER_NO_DEFAULT_TYPE');
9250: hr_utility.raise_error;
9251: end if;
9252: close csr_get_default;
9253: if g_debug then
9246: into p_person_type_id;
9247: if csr_get_default%notfound then
9248: close csr_get_default;
9249: hr_utility.set_message(801, 'HR_7972_PER_NO_DEFAULT_TYPE');
9250: hr_utility.raise_error;
9251: end if;
9252: close csr_get_default;
9253: if g_debug then
9254: hr_utility.set_location(l_proc,40);
9250: hr_utility.raise_error;
9251: end if;
9252: close csr_get_default;
9253: if g_debug then
9254: hr_utility.set_location(l_proc,40);
9255: end if;
9256: else
9257: if g_debug then
9258: hr_utility.set_location(l_proc,50);
9254: hr_utility.set_location(l_proc,40);
9255: end if;
9256: else
9257: if g_debug then
9258: hr_utility.set_location(l_proc,50);
9259: end if;
9260: --
9261: -- Validate the specified person type.
9262: --
9266: l_active_flag,
9267: l_business_group_id;
9268: if csr_person_type%notfound then
9269: close csr_person_type;
9270: hr_utility.set_message(801, 'HR_7513_PER_TYPE_INVALID');
9271: hr_utility.raise_error;
9272: end if;
9273: close csr_person_type;
9274: if g_debug then
9267: l_business_group_id;
9268: if csr_person_type%notfound then
9269: close csr_person_type;
9270: hr_utility.set_message(801, 'HR_7513_PER_TYPE_INVALID');
9271: hr_utility.raise_error;
9272: end if;
9273: close csr_person_type;
9274: if g_debug then
9275: hr_utility.set_location(l_proc,60);
9271: hr_utility.raise_error;
9272: end if;
9273: close csr_person_type;
9274: if g_debug then
9275: hr_utility.set_location(l_proc,60);
9276: end if;
9277: --
9278: if l_active_flag <> 'Y' then
9279: hr_utility.set_message(801, 'HR_7973_PER_TYPE_NOT_ACTIVE');
9275: hr_utility.set_location(l_proc,60);
9276: end if;
9277: --
9278: if l_active_flag <> 'Y' then
9279: hr_utility.set_message(801, 'HR_7973_PER_TYPE_NOT_ACTIVE');
9280: hr_utility.raise_error;
9281: elsif l_business_group_id <> p_business_group_id then
9282: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
9283: hr_utility.raise_error;
9276: end if;
9277: --
9278: if l_active_flag <> 'Y' then
9279: hr_utility.set_message(801, 'HR_7973_PER_TYPE_NOT_ACTIVE');
9280: hr_utility.raise_error;
9281: elsif l_business_group_id <> p_business_group_id then
9282: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
9283: hr_utility.raise_error;
9284: elsif l_system_person_type <> p_expected_sys_type then
9278: if l_active_flag <> 'Y' then
9279: hr_utility.set_message(801, 'HR_7973_PER_TYPE_NOT_ACTIVE');
9280: hr_utility.raise_error;
9281: elsif l_business_group_id <> p_business_group_id then
9282: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
9283: hr_utility.raise_error;
9284: elsif l_system_person_type <> p_expected_sys_type then
9285: hr_utility.set_message(801, 'HR_7970_PER_WRONG_SYS_TYPE');
9286: hr_utility.set_message_token('SYSTYPE', p_expected_sys_type);
9279: hr_utility.set_message(801, 'HR_7973_PER_TYPE_NOT_ACTIVE');
9280: hr_utility.raise_error;
9281: elsif l_business_group_id <> p_business_group_id then
9282: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
9283: hr_utility.raise_error;
9284: elsif l_system_person_type <> p_expected_sys_type then
9285: hr_utility.set_message(801, 'HR_7970_PER_WRONG_SYS_TYPE');
9286: hr_utility.set_message_token('SYSTYPE', p_expected_sys_type);
9287: hr_utility.raise_error;
9281: elsif l_business_group_id <> p_business_group_id then
9282: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
9283: hr_utility.raise_error;
9284: elsif l_system_person_type <> p_expected_sys_type then
9285: hr_utility.set_message(801, 'HR_7970_PER_WRONG_SYS_TYPE');
9286: hr_utility.set_message_token('SYSTYPE', p_expected_sys_type);
9287: hr_utility.raise_error;
9288: end if;
9289: if g_debug then
9282: hr_utility.set_message(801, 'HR_7974_PER_TYPE_INV_BUS_GROUP');
9283: hr_utility.raise_error;
9284: elsif l_system_person_type <> p_expected_sys_type then
9285: hr_utility.set_message(801, 'HR_7970_PER_WRONG_SYS_TYPE');
9286: hr_utility.set_message_token('SYSTYPE', p_expected_sys_type);
9287: hr_utility.raise_error;
9288: end if;
9289: if g_debug then
9290: hr_utility.set_location(l_proc,70);
9283: hr_utility.raise_error;
9284: elsif l_system_person_type <> p_expected_sys_type then
9285: hr_utility.set_message(801, 'HR_7970_PER_WRONG_SYS_TYPE');
9286: hr_utility.set_message_token('SYSTYPE', p_expected_sys_type);
9287: hr_utility.raise_error;
9288: end if;
9289: if g_debug then
9290: hr_utility.set_location(l_proc,70);
9291: end if;
9286: hr_utility.set_message_token('SYSTYPE', p_expected_sys_type);
9287: hr_utility.raise_error;
9288: end if;
9289: if g_debug then
9290: hr_utility.set_location(l_proc,70);
9291: end if;
9292: end if;
9293: end chk_person_type;
9294: --
9316: l_proc varchar2(72) ;
9317: begin
9318: if g_debug then
9319: l_proc := g_package||'return_legislation_code';
9320: hr_utility.set_location('Entering:'|| l_proc, 10);
9321: end if;
9322: --
9323: -- HR/TCA merge allows person_id to be null
9324: --
9325: if p_person_id is null then
9326: g_person_id := p_person_id;
9327: l_legislation_code := NULL;
9328: if g_debug then
9329: hr_utility.set_location(l_proc, 15);
9330: end if;
9331: --
9332: -- Ensure that all the mandatory parameter are not null
9333: --
9343: -- variable.
9344: --
9345: l_legislation_code := g_legislation_code;
9346: if g_debug then
9347: hr_utility.set_location(l_proc, 20);
9348: end if;
9349: else
9350: --
9351: -- The ID is different to the last call to this function
9357: close csr_leg_code;
9358: --
9359: -- The primary key is invalid therefore we must error
9360: --
9361: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
9362: hr_utility.raise_error;
9363: end if;
9364: --
9365: close csr_leg_code;
9358: --
9359: -- The primary key is invalid therefore we must error
9360: --
9361: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
9362: hr_utility.raise_error;
9363: end if;
9364: --
9365: close csr_leg_code;
9366: if g_debug then
9363: end if;
9364: --
9365: close csr_leg_code;
9366: if g_debug then
9367: hr_utility.set_location(' Leaving:'|| l_proc, 20);
9368: end if;
9369: --
9370: g_person_id := p_person_id;
9371: g_legislation_code := l_legislation_code;
9371: g_legislation_code := l_legislation_code;
9372: end if;
9373: end if; -- HR/TCA merge
9374: if g_debug then
9375: hr_utility.set_location(' Leaving:'|| l_proc, 25);
9376: end if;
9377: --
9378: return l_legislation_code;
9379: end return_legislation_code;
9419:
9420: --
9421: begin
9422: if g_debug then
9423: hr_utility.set_location('Entering:'|| l_proc, 1);
9424: end if;
9425: --
9426: -- Check mandatory parameters have been set
9427: --
9443: ,p_argument_value => p_validation_end_date
9444: );
9445: --
9446: if g_debug then
9447: hr_utility.set_location(l_proc, 2);
9448: end if;
9449: --
9450: -- Get current value for system_person_type (i.e. as of the
9451: -- effective date)
9454: fetch csr_get_system_person_type into l_person_type;
9455: close csr_get_system_person_type;
9456: --
9457: if g_debug then
9458: hr_utility.set_location(l_proc, 3);
9459: end if;
9460: --
9461: -- Check that no future changes exist for the person type
9462: --
9463: open csr_chk_future_changes;
9464: fetch csr_chk_future_changes into l_exists;
9465: if csr_chk_future_changes%found then
9466: close csr_chk_future_changes;
9467: hr_utility.set_message(801, 'HR_7979_PER_SYS_PERS_TYP_CHANG');
9468: hr_utility.raise_error;
9469: end if;
9470: close csr_chk_future_changes;
9471: --
9464: fetch csr_chk_future_changes into l_exists;
9465: if csr_chk_future_changes%found then
9466: close csr_chk_future_changes;
9467: hr_utility.set_message(801, 'HR_7979_PER_SYS_PERS_TYP_CHANG');
9468: hr_utility.raise_error;
9469: end if;
9470: close csr_chk_future_changes;
9471: --
9472: if g_debug then
9469: end if;
9470: close csr_chk_future_changes;
9471: --
9472: if g_debug then
9473: hr_utility.set_location(' Leaving:'|| l_proc, 4);
9474: end if;
9475: end chk_system_pers_type;
9476: --
9477: end per_per_bus;