[Home] [Help]
71: l_proc varchar2(72) := g_package||'chk_business_group_id';
72: l_api_updating boolean;
73: --
74: begin
75: hr_utility.set_location('Entering:'||l_proc, 5);
76: if p_business_group_id is not null then
77: hr_utility.set_location(l_proc, 10);
78: --
79: hr_api.validate_bus_grp_id(p_business_group_id);
73: --
74: begin
75: hr_utility.set_location('Entering:'||l_proc, 5);
76: if p_business_group_id is not null then
77: hr_utility.set_location(l_proc, 10);
78: --
79: hr_api.validate_bus_grp_id(p_business_group_id);
80: --
81: --
82: --If BUSINESS_GROUP_ID is specified, PERSON_ID must be specified
83: --
84: if p_person_id is null then
85: --
86: hr_utility.set_message(800, 'HR_289945_INV_PERSON_ID');
87: hr_utility.raise_error;
88: --
89: end if;
90: --
83: --
84: if p_person_id is null then
85: --
86: hr_utility.set_message(800, 'HR_289945_INV_PERSON_ID');
87: hr_utility.raise_error;
88: --
89: end if;
90: --
91: end if;
99: if (l_api_updating
100: and nvl(per_add_shd.g_old_rec.business_group_id,hr_api.g_number) <> hr_api.g_number
101: and per_add_shd.g_old_rec.business_group_id <> p_business_group_id ) then
102: --
103: hr_utility.set_message(800, 'HR_289947_INV_UPD_BG_ID');
104: hr_utility.raise_error;
105: --
106:
107: end if;
100: and nvl(per_add_shd.g_old_rec.business_group_id,hr_api.g_number) <> hr_api.g_number
101: and per_add_shd.g_old_rec.business_group_id <> p_business_group_id ) then
102: --
103: hr_utility.set_message(800, 'HR_289947_INV_UPD_BG_ID');
104: hr_utility.raise_error;
105: --
106:
107: end if;
108: --
105: --
106:
107: end if;
108: --
109: hr_utility.set_location('Leaving:'||l_proc, 40);
110: exception
111: when app_exception.application_exception then
112: if hr_multi_message.exception_add
113: (p_associated_column1 => 'PER_PREVIOUS_EMPLOYERS.BUSINESS_GROUP_ID'
111: when app_exception.application_exception then
112: if hr_multi_message.exception_add
113: (p_associated_column1 => 'PER_PREVIOUS_EMPLOYERS.BUSINESS_GROUP_ID'
114: ) then
115: hr_utility.set_location('Leaving:'||l_proc, 50);
116: raise;
117: end if;
118: hr_utility.set_location('Leaving:'||l_proc,60);
119: end chk_business_group_id;
114: ) then
115: hr_utility.set_location('Leaving:'||l_proc, 50);
116: raise;
117: end if;
118: hr_utility.set_location('Leaving:'||l_proc,60);
119: end chk_business_group_id;
120: --
121: -- ---------------------------------------------------------------------------
122: -- |---------------------------< chk_person_id >----------------------------|
165: from per_all_people_f ppf
166: where ppf.person_id = p_person_id;
167: --
168: begin
169: hr_utility.set_location('Entering:'|| l_proc, 10);
170: --
171: -- Check mandatory parameters have been set
172: --
173: hr_api.mandatory_arg_error
180: (p_api_name => l_proc
181: ,p_argument => 'business_group_id'
182: ,p_argument_value => p_business_group_id
183: );
184: hr_utility.set_location(l_proc, 20);
185: --
186: -- Check if inserting or updating with modified values
187: --
188: l_api_updating := per_add_shd.api_updating
193: if ((l_api_updating and per_add_shd.g_old_rec.person_id <> p_person_id)
194: or
195: (NOT l_api_updating))
196: then
197: hr_utility.set_location(l_proc, 30);
198: --
199: -- Check that the Person ID is linked to a
200: -- valid person on PER_PEOPLE_F
201: --
203: fetch csr_valid_pers into l_business_group_id;
204: if csr_valid_pers%notfound then
205: --
206: close csr_valid_pers;
207: hr_utility.set_message(801, 'HR_7298_ADD_PERSON_INVALID');
208: hr_utility.raise_error;
209: --
210: else
211: close csr_valid_pers;
204: if csr_valid_pers%notfound then
205: --
206: close csr_valid_pers;
207: hr_utility.set_message(801, 'HR_7298_ADD_PERSON_INVALID');
208: hr_utility.raise_error;
209: --
210: else
211: close csr_valid_pers;
212: hr_utility.set_location(l_proc, 40);
208: hr_utility.raise_error;
209: --
210: else
211: close csr_valid_pers;
212: hr_utility.set_location(l_proc, 40);
213: --
214: -- Check that the business group of the person is the same as the
215: -- business group of the address
216: --
215: -- business group of the address
216: --
217: if p_business_group_id <> l_business_group_id then
218: --
219: hr_utility.set_message(800, 'PER_52989_ADD_NOMATCH_BGP');
220: hr_utility.raise_error;
221: end if;
222: --
223: end if;
216: --
217: if p_business_group_id <> l_business_group_id then
218: --
219: hr_utility.set_message(800, 'PER_52989_ADD_NOMATCH_BGP');
220: hr_utility.raise_error;
221: end if;
222: --
223: end if;
224: --
223: end if;
224: --
225: end if;
226: --
227: hr_utility.set_location(' Leaving:'|| l_proc, 42);
228: --
229: --UPDATE not allowed unless currently null(U)
230: --
231: if (l_api_updating
232: and nvl(per_add_shd.g_old_rec.person_id,hr_api.g_number) <> hr_api.g_number
233: and per_add_shd.g_old_rec.person_id <> p_person_id
234: ) then
235: --
236: hr_utility.set_message(800, 'HR_289948_INV_UPD_PERSON_ID');
237: hr_utility.raise_error;
238: --
239: end if;
240: hr_utility.set_location(' Leaving:'|| l_proc, 45);
233: and per_add_shd.g_old_rec.person_id <> p_person_id
234: ) then
235: --
236: hr_utility.set_message(800, 'HR_289948_INV_UPD_PERSON_ID');
237: hr_utility.raise_error;
238: --
239: end if;
240: hr_utility.set_location(' Leaving:'|| l_proc, 45);
241: --
236: hr_utility.set_message(800, 'HR_289948_INV_UPD_PERSON_ID');
237: hr_utility.raise_error;
238: --
239: end if;
240: hr_utility.set_location(' Leaving:'|| l_proc, 45);
241: --
242: --If BUSINESS_GROUP_ID is specified, PERSON_ID must be specified
243: --
244: if p_business_group_id is not null and p_person_id is null then
242: --If BUSINESS_GROUP_ID is specified, PERSON_ID must be specified
243: --
244: if p_business_group_id is not null and p_person_id is null then
245: --
246: hr_utility.set_message(800, 'HR_289945_INV_PERSON_ID');
247: hr_utility.raise_error;
248: --
249: end if;
250: --
243: --
244: if p_business_group_id is not null and p_person_id is null then
245: --
246: hr_utility.set_message(800, 'HR_289945_INV_PERSON_ID');
247: hr_utility.raise_error;
248: --
249: end if;
250: --
251: hr_utility.set_location(' Leaving:'|| l_proc, 50);
247: hr_utility.raise_error;
248: --
249: end if;
250: --
251: hr_utility.set_location(' Leaving:'|| l_proc, 50);
252: exception
253: when app_exception.application_exception then
254: if hr_multi_message.exception_add
255: (p_associated_column1 => 'PER_ADDRESSES.PERSON_ID'
253: when app_exception.application_exception then
254: if hr_multi_message.exception_add
255: (p_associated_column1 => 'PER_ADDRESSES.PERSON_ID'
256: ) then
257: hr_utility.set_location(' Leaving:'||l_proc,60);
258: raise;
259: end if;
260: hr_utility.set_location(' Leaving:'||l_proc,70);
261: end chk_person_id;
256: ) then
257: hr_utility.set_location(' Leaving:'||l_proc,60);
258: raise;
259: end if;
260: hr_utility.set_location(' Leaving:'||l_proc,70);
261: end chk_person_id;
262: -- ---------------------------------------------------------------------------
263: -- |---------------------------< chk_party_id >----------------------------|
264: -- ---------------------------------------------------------------------------
313: from hz_parties hzp
314: where hzp.party_id = p_rec.party_id;
315: --
316: begin
317: hr_utility.set_location('Entering:'|| l_proc, 1);
318: --
319: --
320: if p_rec.person_id is not null then
321: if hr_multi_message.no_all_inclusive_error
325: --
326: open csr_get_party_id;
327: fetch csr_get_party_id into l_party_id;
328: close csr_get_party_id;
329: hr_utility.set_location(l_proc,20);
330: if p_rec.party_id is not null then
331: if p_rec.party_id <> nvl(l_party_id,-1) then
332: hr_utility.set_message(800, 'HR_289343_PERSONPARTY_MISMATCH');
333: hr_utility.set_location(l_proc,30);
328: close csr_get_party_id;
329: hr_utility.set_location(l_proc,20);
330: if p_rec.party_id is not null then
331: if p_rec.party_id <> nvl(l_party_id,-1) then
332: hr_utility.set_message(800, 'HR_289343_PERSONPARTY_MISMATCH');
333: hr_utility.set_location(l_proc,30);
334: hr_multi_message.add
335: (p_associated_column1 => 'PER_ADDRESSES.PERSON_ID'
336: ,p_associated_column2 => 'PER_ADDRESSES.DATE_FROM'
329: hr_utility.set_location(l_proc,20);
330: if p_rec.party_id is not null then
331: if p_rec.party_id <> nvl(l_party_id,-1) then
332: hr_utility.set_message(800, 'HR_289343_PERSONPARTY_MISMATCH');
333: hr_utility.set_location(l_proc,30);
334: hr_multi_message.add
335: (p_associated_column1 => 'PER_ADDRESSES.PERSON_ID'
336: ,p_associated_column2 => 'PER_ADDRESSES.DATE_FROM'
337: ,p_associated_column3 => 'PER_ADDRESSES.PARTY_ID'
340: else
341: --
342: -- derive party_id from per_all_people_f using person_id
343: --
344: hr_utility.set_location(l_proc,50);
345: p_rec.party_id := l_party_id;
346: end if;
347: end if;
348: else
346: end if;
347: end if;
348: else
349: if p_rec.party_id is null then
350: hr_utility.set_message(800, 'HR_289341_CHK_PERSON_OR_PARTY');
351: hr_utility.set_location(l_proc,60);
352: hr_multi_message.add
353: ( p_associated_column1 => 'PER_ADDRESSES.PARTY_ID'
354: );
347: end if;
348: else
349: if p_rec.party_id is null then
350: hr_utility.set_message(800, 'HR_289341_CHK_PERSON_OR_PARTY');
351: hr_utility.set_location(l_proc,60);
352: hr_multi_message.add
353: ( p_associated_column1 => 'PER_ADDRESSES.PARTY_ID'
354: );
355: else
356: open csr_valid_party_id;
357: fetch csr_valid_party_id into l_party_id2;
358: if csr_valid_party_id%notfound then
359: close csr_valid_party_id;
360: hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
361: hr_utility.set_location(l_proc,70);
362: hr_multi_message.add
363: (p_associated_column1 => 'PER_ADDRESSES.PARTY_ID'
364: );
357: fetch csr_valid_party_id into l_party_id2;
358: if csr_valid_party_id%notfound then
359: close csr_valid_party_id;
360: hr_utility.set_message(800, 'PER_289342_PARTY_ID_INVALID');
361: hr_utility.set_location(l_proc,70);
362: hr_multi_message.add
363: (p_associated_column1 => 'PER_ADDRESSES.PARTY_ID'
364: );
365: else
369: end if;
370: end if;
371: end if;
372: --
373: hr_utility.set_location(' Leaving:'||l_proc,100);
374: End chk_party_id;
375: --
376: -- ---------------------------------------------------------------------------
377: -- |---------------------------< chk_primary_flag >-------------------------|
408: --
409: l_api_updating boolean;
410: --
411: begin
412: hr_utility.set_location('Entering:'|| l_proc, 10);
413: --
414: -- Check mandatory parameters have been set
415: --
416: hr_api.mandatory_arg_error
417: (p_api_name => l_proc
418: ,p_argument => 'p_primary_flag'
419: ,p_argument_value => p_primary_flag
420: );
421: hr_utility.set_location(l_proc, 20);
422: --
423: -- Check if inserting or updating with modified values
424: --
425: l_api_updating := per_add_shd.api_updating
430: if ((l_api_updating and per_add_shd.g_old_rec.primary_flag <> p_primary_flag)
431: or
432: (NOT l_api_updating))
433: then
434: hr_utility.set_location(l_proc, 30);
435: --
436: -- Check that the value is 'Y' or 'N'
437: --
438: if p_primary_flag not in('Y','N') then
441: (p_constraint_name => 'PER_ADDR_PRIMARY_FLAG_CHK'
442: );
443: --
444: end if;
445: hr_utility.set_location(l_proc, 40);
446: --
447: end if;
448: hr_utility.set_location(' Leaving:'|| l_proc, 50);
449: end chk_primary_flag;
444: end if;
445: hr_utility.set_location(l_proc, 40);
446: --
447: end if;
448: hr_utility.set_location(' Leaving:'|| l_proc, 50);
449: end chk_primary_flag;
450: --
451: -- ---------------------------------------------------------------------------
452: -- |-------------------------< chk_address_type >---------------------------|
492: l_proc varchar2(72) := g_package||'chk_address_type';
493: l_api_updating boolean;
494: --
495: begin
496: hr_utility.set_location('Entering:'|| l_proc, 1);
497: --
498: -- Check mandatory parameters have been set
499: --
500: hr_api.mandatory_arg_error
519: if ((l_api_updating and
520: nvl(per_add_shd.g_old_rec.address_type, hr_api.g_varchar2) <>
521: nvl(p_address_type, hr_api.g_varchar2)) or
522: (NOT l_api_updating)) then
523: hr_utility.set_location(l_proc, 2);
524: --
525: -- Checks that the value for address_type is
526: -- valid and exists on hr_lookups within the
527: -- specified date range
537: ,p_lookup_code => p_address_type
538: ) then
539: --
540: -- Error: Invalid address type.
541: hr_utility.set_message(801, 'HR_7299_ADD_TYPE_INVALID');
542: hr_utility.raise_error;
543: end if;
544: end if;
545: end if;
538: ) then
539: --
540: -- Error: Invalid address type.
541: hr_utility.set_message(801, 'HR_7299_ADD_TYPE_INVALID');
542: hr_utility.raise_error;
543: end if;
544: end if;
545: end if;
546: --
543: end if;
544: end if;
545: end if;
546: --
547: hr_utility.set_location(' Leaving:'|| l_proc, 4);
548: exception
549: when app_exception.application_exception then
550: if hr_multi_message.exception_add
551: (p_associated_column1 => 'PER_ADDRESSES.ADDRESS_TYPE'
549: when app_exception.application_exception then
550: if hr_multi_message.exception_add
551: (p_associated_column1 => 'PER_ADDRESSES.ADDRESS_TYPE'
552: ) then
553: hr_utility.set_location(' Leaving:'||l_proc,5);
554: raise;
555: end if;
556: hr_utility.set_location(' Leaving:'||l_proc,6);
557: --
552: ) then
553: hr_utility.set_location(' Leaving:'||l_proc,5);
554: raise;
555: end if;
556: hr_utility.set_location(' Leaving:'||l_proc,6);
557: --
558: end chk_address_type;
559: --
560: -- ---------------------------------------------------------------------------
602: from fnd_territories ft
603: where ft.territory_code = p_country;
604: --
605: begin
606: hr_utility.set_location('Entering:'|| l_proc, 1);
607: --
608: -- Only proceed with validation if :
609: -- a) The current g_old_rec is current and
610: -- b) The value for country has changed
616: if ((l_api_updating and
617: nvl(per_add_shd.g_old_rec.country, hr_api.g_varchar2) <>
618: nvl(p_country, hr_api.g_varchar2)) or
619: (NOT l_api_updating)) then
620: hr_utility.set_location(l_proc, 2);
621: --
622: -- Checks that value for country is a valid
623: -- country on fnd_territories
624: --
629: open csr_valid_ctry;
630: fetch csr_valid_ctry into l_exists;
631: if csr_valid_ctry%notfound then
632: close csr_valid_ctry;
633: hr_utility.set_message(801, 'HR_7300_ADD_COUNTRY_INVALID');
634: hr_utility.raise_error;
635: end if;
636: close csr_valid_ctry;
637: end if;
630: fetch csr_valid_ctry into l_exists;
631: if csr_valid_ctry%notfound then
632: close csr_valid_ctry;
633: hr_utility.set_message(801, 'HR_7300_ADD_COUNTRY_INVALID');
634: hr_utility.raise_error;
635: end if;
636: close csr_valid_ctry;
637: end if;
638: end if;
636: close csr_valid_ctry;
637: end if;
638: end if;
639: --
640: hr_utility.set_location(' Leaving:'|| l_proc, 3);
641: exception
642: when app_exception.application_exception then
643: if hr_multi_message.exception_add
644: (p_associated_column1 => 'PER_ADDRESSES.COUNTRY'
642: when app_exception.application_exception then
643: if hr_multi_message.exception_add
644: (p_associated_column1 => 'PER_ADDRESSES.COUNTRY'
645: ) then
646: hr_utility.set_location(' Leaving:'||l_proc,4);
647: raise;
648: end if;
649: hr_utility.set_location(' Leaving:'||l_proc,5);
650: --
645: ) then
646: hr_utility.set_location(' Leaving:'||l_proc,4);
647: raise;
648: end if;
649: hr_utility.set_location(' Leaving:'||l_proc,5);
650: --
651: end chk_country;
652: --
653: -- ---------------------------------------------------------------------------
692: l_date_to date;
693: l_api_updating boolean;
694: --
695: begin
696: hr_utility.set_location('Entering:'|| l_proc, 1);
697: --
698: -- Check mandatory parameters have been set
699: --
700: hr_api.mandatory_arg_error
721: nvl(per_add_shd.g_old_rec.date_to, hr_api.g_eot) <>
722: nvl(p_date_to, hr_api.g_eot)) or
723: (NOT l_api_updating)) then
724: --
725: hr_utility.set_location(l_proc, 2);
726: --
727: -- Checks that the value for date_to is greater than or
728: -- equal to the corresponding value for date_from for the
729: -- same record
728: -- equal to the corresponding value for date_from for the
729: -- same record
730: --
731: if nvl(p_date_to, hr_api.g_eot) < p_date_from then
732: hr_utility.set_message(801, 'HR_7301_ADD_DATE_TO_LATER');
733: hr_utility.raise_error;
734: end if;
735: --
736: end if;
729: -- same record
730: --
731: if nvl(p_date_to, hr_api.g_eot) < p_date_from then
732: hr_utility.set_message(801, 'HR_7301_ADD_DATE_TO_LATER');
733: hr_utility.raise_error;
734: end if;
735: --
736: end if;
737: --
735: --
736: end if;
737: --
738: end if;
739: hr_utility.set_location(' Leaving:'|| l_proc, 3);
740: exception
741: when app_exception.application_exception then
742: if hr_multi_message.exception_add
743: (p_same_associated_columns => 'Y'
741: when app_exception.application_exception then
742: if hr_multi_message.exception_add
743: (p_same_associated_columns => 'Y'
744: ) then
745: hr_utility.set_location(' Leaving:'||l_proc,4);
746: raise;
747: end if;
748: hr_utility.set_location(' Leaving:'||l_proc,5);
749: --
744: ) then
745: hr_utility.set_location(' Leaving:'||l_proc,4);
746: raise;
747: end if;
748: hr_utility.set_location(' Leaving:'||l_proc,5);
749: --
750: end chk_date_to;
751: --
752: -- ---------------------------------------------------------------------------
789: l_proc varchar2(72) := g_package||'chk_date_from';
790: l_api_updating boolean;
791: --
792: begin
793: hr_utility.set_location('Entering:'|| l_proc, 1);
794: --
795: -- Check mandatory parameters have been set
796: --
797: hr_api.mandatory_arg_error
809: ,p_object_version_number => p_object_version_number);
810: --
811: if ((l_api_updating and per_add_shd.g_old_rec.date_from <> p_date_from) or
812: (NOT l_api_updating)) then
813: hr_utility.set_location(l_proc, 2);
814: --
815: -- Check that the date_from values is less than
816: -- or equal to the date_to value for the current
817: -- record
816: -- or equal to the date_to value for the current
817: -- record
818: --
819: if p_date_from > nvl(p_date_to, hr_api.g_eot) then
820: hr_utility.set_message(801, 'HR_7303_ADD_DATE_FROM_EARLIER');
821: hr_utility.raise_error;
822: end if;
823: --
824: end if;
817: -- record
818: --
819: if p_date_from > nvl(p_date_to, hr_api.g_eot) then
820: hr_utility.set_message(801, 'HR_7303_ADD_DATE_FROM_EARLIER');
821: hr_utility.raise_error;
822: end if;
823: --
824: end if;
825: --
822: end if;
823: --
824: end if;
825: --
826: hr_utility.set_location(' Leaving:'|| l_proc, 3);
827: exception
828: when app_exception.application_exception then
829: if hr_multi_message.exception_add
830: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
829: if hr_multi_message.exception_add
830: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
831: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
832: ) then
833: hr_utility.set_location(' Leaving:'||l_proc,4);
834: raise;
835: end if;
836: hr_utility.set_location(' Leaving:'||l_proc,5);
837: --
832: ) then
833: hr_utility.set_location(' Leaving:'||l_proc,4);
834: raise;
835: end if;
836: hr_utility.set_location(' Leaving:'||l_proc,5);
837: --
838: end chk_date_from;
839: --
840: -- ---------------------------------------------------------------------------
1035: or (p_address_id is not null
1036: and p_address_id <> pa.address_id));
1037: --
1038: begin
1039: hr_utility.set_location('Entering:'|| l_proc, 10);
1040: --
1041: -- Check mandatory parameters have been set
1042: --
1043: hr_api.mandatory_arg_error
1058: (p_api_name => l_proc
1059: ,p_argument => 'primary_flag'
1060: ,p_argument_value => p_primary_flag
1061: );
1062: hr_utility.set_location(l_proc, 20);
1063: if hr_multi_message.no_all_inclusive_error
1064: (p_check_column1 => 'PER_ADDRESSES.DATE_FROM'
1065: ,p_check_column2 => 'PER_ADDRESSES.DATE_TO'
1066: ,p_check_column3 => 'PER_ADDRESSES.PERSON_ID'
1088: nvl(p_address_type, hr_api.g_varchar2))
1089: or
1090: (NOT l_api_updating))
1091: then
1092: hr_utility.set_location(l_proc, 30);
1093: --
1094: -- For all addresses
1095: -- =================
1096: -- Checks that the date_from, date_to values for a given address
1105: open csr_dup_add_type_exists;
1106: fetch csr_dup_add_type_exists into l_exists;
1107: if csr_dup_add_type_exists%found then
1108: close csr_dup_add_type_exists;
1109: hr_utility.set_message(801, 'HR_51139_ADD_TYPE_ALR_EXIST');
1110: hr_multi_message.add
1111: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1112: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1113: ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1121: end if;
1122: end if;
1123: end if;
1124: --
1125: hr_utility.set_location(l_proc, 4);
1126: --
1127: -- For primary addresses only
1128: -- ==========================
1129: --
1141: open csr_dup_prim_flag;
1142: fetch csr_dup_prim_flag into l_exists;
1143: if csr_dup_prim_flag%found then
1144: close csr_dup_prim_flag;
1145: hr_utility.set_message(801, 'HR_7327_ADD_PRIMARY_ADD_EXISTS');
1146: hr_multi_message.add
1147: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1148: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1149: ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1207: and l_recs_before
1208: and l_recs_after)
1209: or
1210: (l_good_recs < 1)) then
1211: hr_utility.set_message(801, 'HR_51030_ADDR_PRIM_GAP');
1212: hr_multi_message.add
1213: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1214: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1215: ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1234: open csr_invalid_non_prim;
1235: fetch csr_invalid_non_prim into l_exists;
1236: if csr_invalid_non_prim%found then
1237: close csr_invalid_non_prim;
1238: hr_utility.set_message(801, 'HR_7302_ADD_PRIMARY_DATES');
1239: hr_multi_message.add
1240: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1241: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1242: ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1263: open csr_no_primary;
1264: fetch csr_no_primary into l_exists;
1265: if csr_no_primary%notfound then
1266: close csr_no_primary;
1267: hr_utility.set_message(801, 'HR_7302_ADD_PRIMARY_DATES');
1268: hr_multi_message.add
1269: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1270: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1271: ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1298: and address_id <> p_address_id
1299: and l_action_eff_date between date_from
1300: and nvl(date_to,l_action_eff_date);
1301: if l_address_count = 0 then
1302: hr_utility.set_message(800, 'PER_PAYROLL_EXISTS');
1303: hr_multi_message.add();
1304: end if;
1305: end if;
1306: end loop;
1307: end loop;
1308: end if;
1309: end if;
1310: --
1311: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1312: end chk_date_comb;
1313: --
1314: -- ---------------------------------------------------------------------------
1315: -- |-----------------------------< chk_style >------------------------------|
1363: -- 70.2 change c end.
1364: --
1365: --
1366: begin
1367: hr_utility.set_location('Entering:'|| l_proc, 1);
1368: --
1369: -- Check mandatory parameters have been set
1370: --
1371: hr_api.mandatory_arg_error
1372: (p_api_name => l_proc
1373: ,p_argument => 'style'
1374: ,p_argument_value => p_style
1375: );
1376: hr_utility.set_location(l_proc, 2);
1377: --
1378: -- Checks that the flex structure for the style
1379: -- selected exists in fnd_descr_flex_contents
1380: --
1381: open csr_valid_flex_struc;
1382: fetch csr_valid_flex_struc into l_exists;
1383: if csr_valid_flex_struc%notfound then
1384: close csr_valid_flex_struc;
1385: hr_utility.set_message(801, 'HR_7304_ADD_NO_FORMAT');
1386: hr_utility.raise_error;
1387: end if;
1388: close csr_valid_flex_struc;
1389: hr_utility.set_location(l_proc, 3);
1382: fetch csr_valid_flex_struc into l_exists;
1383: if csr_valid_flex_struc%notfound then
1384: close csr_valid_flex_struc;
1385: hr_utility.set_message(801, 'HR_7304_ADD_NO_FORMAT');
1386: hr_utility.raise_error;
1387: end if;
1388: close csr_valid_flex_struc;
1389: hr_utility.set_location(l_proc, 3);
1390: --
1385: hr_utility.set_message(801, 'HR_7304_ADD_NO_FORMAT');
1386: hr_utility.raise_error;
1387: end if;
1388: close csr_valid_flex_struc;
1389: hr_utility.set_location(l_proc, 3);
1390: --
1391: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1392: exception
1393: when app_exception.application_exception then
1387: end if;
1388: close csr_valid_flex_struc;
1389: hr_utility.set_location(l_proc, 3);
1390: --
1391: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1392: exception
1393: when app_exception.application_exception then
1394: if hr_multi_message.exception_add
1395: (p_associated_column1 => 'PER_ADDRESSES.STYLE'
1393: when app_exception.application_exception then
1394: if hr_multi_message.exception_add
1395: (p_associated_column1 => 'PER_ADDRESSES.STYLE'
1396: ) then
1397: hr_utility.set_location(' Leaving:'||l_proc,5);
1398: raise;
1399: end if;
1400: -- Call to raise any errors on multi-message list
1401: -- Taking STYLE as an important parameter.
1399: end if;
1400: -- Call to raise any errors on multi-message list
1401: -- Taking STYLE as an important parameter.
1402: hr_multi_message.end_validation_set;
1403: hr_utility.set_location(' Leaving:'||l_proc,6);
1404: --
1405: end chk_style;
1406: --
1407: -- ---------------------------------------------------------------------------
1451: l_api_updating boolean;
1452: l_proc varchar2(72) := g_package||'chk_style_null_attr';
1453: --
1454: begin
1455: hr_utility.set_location('Entering:'|| l_proc, 1);
1456: --
1457: -- Check 'GB' address style
1458: --
1459: if p_style = 'GB' then
1472: l_token := 'telephone_number_3';
1473: raise l_error;
1474: end if;
1475: --
1476: hr_utility.set_location(l_proc, 2);
1477: --
1478: -- Check 'US' address style
1479: --
1480: elsif p_style = 'US' then
1515: raise l_error;
1516: --
1517: end if;
1518: --
1519: hr_utility.set_location(l_proc, 3);
1520: --
1521: -- Check 'GENERIC' address style
1522: --
1523: elsif p_style = 'GENERIC' then
1528: end if;
1529: --
1530: exception
1531: when l_error then
1532: hr_utility.set_message(801, 'HR_7324_ADD_ADD_ATTRIBUTE_NULL');
1533: hr_utility.set_message_token('ARGUMENT', l_token);
1534: hr_multi_message.add(
1535: p_associated_column1 =>
1536: (per_add_shd.g_tab_nam || '.' || upper(l_token))
1529: --
1530: exception
1531: when l_error then
1532: hr_utility.set_message(801, 'HR_7324_ADD_ADD_ATTRIBUTE_NULL');
1533: hr_utility.set_message_token('ARGUMENT', l_token);
1534: hr_multi_message.add(
1535: p_associated_column1 =>
1536: (per_add_shd.g_tab_nam || '.' || upper(l_token))
1537: );
1537: );
1538: when others then
1539: raise;
1540: --
1541: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1542: end chk_style_null_attr;
1543: --
1544: -- ---------------------------------------------------------------------------
1545: -- |------------------------< chk_address_line1 >----------------------------|
1581: l_proc varchar2(72) := g_package||'chk_address_line1';
1582: l_api_updating boolean;
1583:
1584: begin
1585: hr_utility.set_location('Entering:'|| l_proc, 1);
1586: --
1587: -- Check mandatory parameters have been set
1588: --
1589: hr_api.mandatory_arg_error
1603: if ((l_api_updating and
1604: nvl(per_add_shd.g_old_rec.address_line1, hr_api.g_varchar2) <>
1605: nvl(p_address_line1, hr_api.g_varchar2)) or
1606: (NOT l_api_updating)) then
1607: hr_utility.set_location(l_proc, 2);
1608: --
1609: -- Check that value for address_line1 is not null for 'US' and 'GB'
1610: -- style.
1611: --
1611: --
1612: if p_style = 'GB' or
1613: p_style = 'US' then
1614: --
1615: hr_utility.set_location(l_proc, 3);
1616: --
1617: if p_address_line1 is null then
1618: --
1619: hr_utility.set_message(801, 'HR_51233_ADD_ADD_LINE1_REQ');
1615: hr_utility.set_location(l_proc, 3);
1616: --
1617: if p_address_line1 is null then
1618: --
1619: hr_utility.set_message(801, 'HR_51233_ADD_ADD_LINE1_REQ');
1620: hr_utility.raise_error;
1621: end if;
1622: --
1623: end if;
1616: --
1617: if p_address_line1 is null then
1618: --
1619: hr_utility.set_message(801, 'HR_51233_ADD_ADD_LINE1_REQ');
1620: hr_utility.raise_error;
1621: end if;
1622: --
1623: end if;
1624: --
1623: end if;
1624: --
1625: end if;
1626: --
1627: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1628: exception
1629: when app_exception.application_exception then
1630: if hr_multi_message.exception_add
1631: (p_associated_column1 => 'PER_ADDRESSES.ADDRESS_LINE1'
1629: when app_exception.application_exception then
1630: if hr_multi_message.exception_add
1631: (p_associated_column1 => 'PER_ADDRESSES.ADDRESS_LINE1'
1632: ) then
1633: hr_utility.set_location(' Leaving:'||l_proc,6);
1634: raise;
1635: end if;
1636: hr_utility.set_location(' Leaving:'||l_proc,7);
1637: --
1632: ) then
1633: hr_utility.set_location(' Leaving:'||l_proc,6);
1634: raise;
1635: end if;
1636: hr_utility.set_location(' Leaving:'||l_proc,7);
1637: --
1638: end chk_address_line1;
1639: --
1640: -- ----------------------------------------------------------------------------
1669: from per_business_groups pbg
1670: where pbg.business_group_id = p_business_group_id;
1671:
1672: begin
1673: hr_utility.set_location('Entering:'|| l_proc, 1);
1674: -- Bug 885806
1675: -- dbms_output.put_line('Top of dynamic sql . . .');
1676: hr_utility.trace('Top of dynamic sql . . .');
1677: open csr_bg;
1672: begin
1673: hr_utility.set_location('Entering:'|| l_proc, 1);
1674: -- Bug 885806
1675: -- dbms_output.put_line('Top of dynamic sql . . .');
1676: hr_utility.trace('Top of dynamic sql . . .');
1677: open csr_bg;
1678: fetch csr_bg into l_legislation_code;
1679:
1680: -- Only proceed with validation if :
1697: or
1698: (NOT l_api_updating))
1699: ) then
1700:
1701: hr_utility.set_location(l_proc, 2);
1702: --
1703: -- p_town_or_city(district_code) is not null
1704: --
1705: if p_town_or_city is not NULL then
1702: --
1703: -- p_town_or_city(district_code) is not null
1704: --
1705: if p_town_or_city is not NULL then
1706: hr_utility.set_location(l_proc, 3);
1707:
1708: l_dynamic_sql := 'select t.address_line_1, ' ||
1709: ' t.address_line_1_kana ' ||
1710: 'from per_jp_address_lookups t ' ||
1718: -- 3. Bind dynamic sql variables
1719: -- 4. Define dynamic sql columns
1720: -- 5. Execute and fetch dynamic sql
1721: --
1722: Hr_Utility.Set_Location(l_proc, 6);
1723: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1724: --
1725: Hr_Utility.Set_Location(l_proc, 10);
1726: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1721: --
1722: Hr_Utility.Set_Location(l_proc, 6);
1723: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1724: --
1725: Hr_Utility.Set_Location(l_proc, 10);
1726: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1727: --
1728: Hr_Utility.Set_Location(l_proc, 15);
1729: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1724: --
1725: Hr_Utility.Set_Location(l_proc, 10);
1726: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1727: --
1728: Hr_Utility.Set_Location(l_proc, 15);
1729: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1730: ':p_town_or_city', p_town_or_city);
1731:
1732: Hr_Utility.Set_Location(l_proc, 20);
1728: Hr_Utility.Set_Location(l_proc, 15);
1729: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1730: ':p_town_or_city', p_town_or_city);
1731:
1732: Hr_Utility.Set_Location(l_proc, 20);
1733: dbms_sql.define_column(l_sql_cursor, 1, l_address_line1, 60); -- Step 4
1734: dbms_sql.define_column(l_sql_cursor, 2, l_region_1, 70);
1735: --
1736: Hr_Utility.Set_Location(l_proc, 30);
1732: Hr_Utility.Set_Location(l_proc, 20);
1733: dbms_sql.define_column(l_sql_cursor, 1, l_address_line1, 60); -- Step 4
1734: dbms_sql.define_column(l_sql_cursor, 2, l_region_1, 70);
1735: --
1736: Hr_Utility.Set_Location(l_proc, 30);
1737: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1738:
1739: if l_rows = 0 then
1740: dbms_sql.close_cursor(l_sql_cursor);
1737: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1738:
1739: if l_rows = 0 then
1740: dbms_sql.close_cursor(l_sql_cursor);
1741: hr_utility.set_message(801, 'HR_72028_ADD_INVALID_DIST_CODE');
1742: hr_utility.raise_error;
1743:
1744: elsif l_rows = 1 then
1745: Hr_Utility.Set_Location(l_proc, 35);
1738:
1739: if l_rows = 0 then
1740: dbms_sql.close_cursor(l_sql_cursor);
1741: hr_utility.set_message(801, 'HR_72028_ADD_INVALID_DIST_CODE');
1742: hr_utility.raise_error;
1743:
1744: elsif l_rows = 1 then
1745: Hr_Utility.Set_Location(l_proc, 35);
1746: dbms_sql.column_value(l_sql_cursor, 1, l_address_line1);
1741: hr_utility.set_message(801, 'HR_72028_ADD_INVALID_DIST_CODE');
1742: hr_utility.raise_error;
1743:
1744: elsif l_rows = 1 then
1745: Hr_Utility.Set_Location(l_proc, 35);
1746: dbms_sql.column_value(l_sql_cursor, 1, l_address_line1);
1747: if p_address_line1 is not null and
1748: p_address_line1 <> l_address_line1 then
1749:
1747: if p_address_line1 is not null and
1748: p_address_line1 <> l_address_line1 then
1749:
1750: dbms_sql.close_cursor(l_sql_cursor);
1751: hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1752: hr_utility.raise_error;
1753: end if;
1754: p_address_line1 := l_address_line1;
1755:
1748: p_address_line1 <> l_address_line1 then
1749:
1750: dbms_sql.close_cursor(l_sql_cursor);
1751: hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1752: hr_utility.raise_error;
1753: end if;
1754: p_address_line1 := l_address_line1;
1755:
1756: else
1754: p_address_line1 := l_address_line1;
1755:
1756: else
1757: dbms_sql.close_cursor(l_sql_cursor);
1758: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1759: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1760: hr_utility.raise_error;
1761: end if;
1762:
1755:
1756: else
1757: dbms_sql.close_cursor(l_sql_cursor);
1758: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1759: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1760: hr_utility.raise_error;
1761: end if;
1762:
1763: elsif p_address_line1 is not NULL then
1756: else
1757: dbms_sql.close_cursor(l_sql_cursor);
1758: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1759: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1760: hr_utility.raise_error;
1761: end if;
1762:
1763: elsif p_address_line1 is not NULL then
1764: hr_utility.set_location(l_proc, 4);
1760: hr_utility.raise_error;
1761: end if;
1762:
1763: elsif p_address_line1 is not NULL then
1764: hr_utility.set_location(l_proc, 4);
1765:
1766: l_dynamic_sql :=
1767: 'select t.district_code,' ||
1768: ' t.address_line_1_kana ' ||
1777: -- 3. Bind dynamic sql variables
1778: -- 4. Define dynamic sql columns
1779: -- 5. Execute and fetch dynamic sql
1780: --
1781: Hr_Utility.Set_Location(l_proc, 6);
1782: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1783: --
1784: Hr_Utility.Set_Location(l_proc, 10);
1785: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1780: --
1781: Hr_Utility.Set_Location(l_proc, 6);
1782: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1783: --
1784: Hr_Utility.Set_Location(l_proc, 10);
1785: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1786: --
1787: Hr_Utility.Set_Location(l_proc, 15);
1788: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1783: --
1784: Hr_Utility.Set_Location(l_proc, 10);
1785: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1786: --
1787: Hr_Utility.Set_Location(l_proc, 15);
1788: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1789: ':p_address_line1', p_address_line1);
1790:
1791: Hr_Utility.Set_Location(l_proc, 20);
1787: Hr_Utility.Set_Location(l_proc, 15);
1788: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1789: ':p_address_line1', p_address_line1);
1790:
1791: Hr_Utility.Set_Location(l_proc, 20);
1792: dbms_sql.define_column(l_sql_cursor, 1, l_town_or_city, 50); -- Step 4
1793: dbms_sql.define_column(l_sql_cursor, 2, l_region_1, 70);
1794: --
1795: Hr_Utility.Set_Location(l_proc, 30);
1791: Hr_Utility.Set_Location(l_proc, 20);
1792: dbms_sql.define_column(l_sql_cursor, 1, l_town_or_city, 50); -- Step 4
1793: dbms_sql.define_column(l_sql_cursor, 2, l_region_1, 70);
1794: --
1795: Hr_Utility.Set_Location(l_proc, 30);
1796: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1797:
1798: if l_rows = 0 then
1799: dbms_sql.close_cursor(l_sql_cursor);
1796: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1797:
1798: if l_rows = 0 then
1799: dbms_sql.close_cursor(l_sql_cursor);
1800: hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1801: hr_utility.raise_error;
1802:
1803: elsif l_rows = 1 then
1804: Hr_Utility.Set_Location(l_proc, 35);
1797:
1798: if l_rows = 0 then
1799: dbms_sql.close_cursor(l_sql_cursor);
1800: hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1801: hr_utility.raise_error;
1802:
1803: elsif l_rows = 1 then
1804: Hr_Utility.Set_Location(l_proc, 35);
1805: dbms_sql.column_value(l_sql_cursor, 1, l_town_or_city);
1800: hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1801: hr_utility.raise_error;
1802:
1803: elsif l_rows = 1 then
1804: Hr_Utility.Set_Location(l_proc, 35);
1805: dbms_sql.column_value(l_sql_cursor, 1, l_town_or_city);
1806: p_town_or_city := l_town_or_city;
1807:
1808: else
1806: p_town_or_city := l_town_or_city;
1807:
1808: else
1809: dbms_sql.close_cursor(l_sql_cursor);
1810: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1811: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1812: hr_utility.raise_error;
1813: end if;
1814: --
1807:
1808: else
1809: dbms_sql.close_cursor(l_sql_cursor);
1810: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1811: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1812: hr_utility.raise_error;
1813: end if;
1814: --
1815: --Both p_region_1 and p_address_line1 are null
1808: else
1809: dbms_sql.close_cursor(l_sql_cursor);
1810: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1811: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1812: hr_utility.raise_error;
1813: end if;
1814: --
1815: --Both p_region_1 and p_address_line1 are null
1816: --
1814: --
1815: --Both p_region_1 and p_address_line1 are null
1816: --
1817: else
1818: hr_utility.set_message(801, 'HR_72031_ADD_DIST_LINE1_NULL');
1819: hr_utility.raise_error;
1820: end if;
1821:
1822:
1815: --Both p_region_1 and p_address_line1 are null
1816: --
1817: else
1818: hr_utility.set_message(801, 'HR_72031_ADD_DIST_LINE1_NULL');
1819: hr_utility.raise_error;
1820: end if;
1821:
1822:
1823: dbms_sql.column_value(l_sql_cursor, 2, l_region_1);
1823: dbms_sql.column_value(l_sql_cursor, 2, l_region_1);
1824: if p_region_1 is not null and
1825: p_region_1 <> l_region_1 then
1826: dbms_sql.close_cursor(l_sql_cursor);
1827: hr_utility.set_message(801, 'HR_72032_ADD_INVALID_KANA1');
1828: hr_utility.raise_error;
1829: end if;
1830:
1831: p_region_1 := l_region_1;
1824: if p_region_1 is not null and
1825: p_region_1 <> l_region_1 then
1826: dbms_sql.close_cursor(l_sql_cursor);
1827: hr_utility.set_message(801, 'HR_72032_ADD_INVALID_KANA1');
1828: hr_utility.raise_error;
1829: end if;
1830:
1831: p_region_1 := l_region_1;
1832: dbms_sql.close_cursor(l_sql_cursor);
1830:
1831: p_region_1 := l_region_1;
1832: dbms_sql.close_cursor(l_sql_cursor);
1833: end if;
1834: hr_utility.set_location('Leaving:'|| l_proc, 5);
1835: -- Bug 885806
1836: -- dbms_output.put_line('Bottom of dynamic sql . . .');
1837: hr_utility.trace('Bottom of dynamic sql . . .');
1838: end chk_address1_towncity_comb;
1833: end if;
1834: hr_utility.set_location('Leaving:'|| l_proc, 5);
1835: -- Bug 885806
1836: -- dbms_output.put_line('Bottom of dynamic sql . . .');
1837: hr_utility.trace('Bottom of dynamic sql . . .');
1838: end chk_address1_towncity_comb;
1839: */
1840: --
1841: -- ----------------------------------------------------------------------------
1859: l_rgeflg varchar2(10);
1860: l_region_2 per_addresses.region_2%type;
1861: --
1862: begin
1863: hr_utility.set_location('Entering:'|| l_proc, 1);
1864:
1865: -- Only proceed with validation if :
1866: -- a) The current g_old_rec is current and
1867: -- b) The value for address_line2, or region_2 have changed
1876: nvl(per_add_shd.g_old_rec.region_2, hr_api.g_varchar2) <>
1877: nvl(p_region_2, hr_api.g_varchar2)
1878: )) or (NOT l_api_updating)) then
1879:
1880: hr_utility.set_location(l_proc, 2);
1881:
1882: if p_address_line2 is NULL and p_region_2 is not NULL then
1883: hr_utility.set_message(801, 'HR_72025_ADD_REGION2_NOT_NULL');
1884: hr_utility.raise_error;
1879:
1880: hr_utility.set_location(l_proc, 2);
1881:
1882: if p_address_line2 is NULL and p_region_2 is not NULL then
1883: hr_utility.set_message(801, 'HR_72025_ADD_REGION2_NOT_NULL');
1884: hr_utility.raise_error;
1885: end if;
1886: l_region_2 := p_region_2;
1887: hr_chkfmt.checkformat(value => l_region_2
1880: hr_utility.set_location(l_proc, 2);
1881:
1882: if p_address_line2 is NULL and p_region_2 is not NULL then
1883: hr_utility.set_message(801, 'HR_72025_ADD_REGION2_NOT_NULL');
1884: hr_utility.raise_error;
1885: end if;
1886: l_region_2 := p_region_2;
1887: hr_chkfmt.checkformat(value => l_region_2
1888: ,format => 'KANA'
1891: ,maximum => NULL
1892: ,nullok => 'Y'
1893: ,rgeflg => l_rgeflg
1894: ,curcode => NULL);
1895: hr_utility.set_location(' Calling hr_chkfmt.checkformat2', 1);
1896: end if;
1897:
1898: hr_utility.set_location('Leaving:'|| l_proc, 3);
1899: end chk_address2_region2_comb;
1894: ,curcode => NULL);
1895: hr_utility.set_location(' Calling hr_chkfmt.checkformat2', 1);
1896: end if;
1897:
1898: hr_utility.set_location('Leaving:'|| l_proc, 3);
1899: end chk_address2_region2_comb;
1900: */
1901: --
1902: -- ----------------------------------------------------------------------------
1920: l_rgeflg varchar2(10);
1921: l_region_3 per_addresses.region_3%type;
1922: --
1923: begin
1924: hr_utility.set_location('Entering:'|| l_proc, 1);
1925:
1926: -- Only proceed with validation if :
1927: -- a) The current g_old_rec is current and
1928: -- b) The value for address_line3, or region_3 have changed
1937: nvl(per_add_shd.g_old_rec.region_3, hr_api.g_varchar2) <>
1938: nvl(p_region_3, hr_api.g_varchar2)
1939: )) or (NOT l_api_updating)) then
1940:
1941: hr_utility.set_location(l_proc, 2);
1942:
1943: if p_address_line3 is NULL and p_region_3 is not NULL then
1944: hr_utility.set_message(801, 'HR_72026_ADD_REGION3_NOT_NULL');
1945: hr_utility.raise_error;
1940:
1941: hr_utility.set_location(l_proc, 2);
1942:
1943: if p_address_line3 is NULL and p_region_3 is not NULL then
1944: hr_utility.set_message(801, 'HR_72026_ADD_REGION3_NOT_NULL');
1945: hr_utility.raise_error;
1946: end if;
1947: l_region_3 := p_region_3;
1948: hr_chkfmt.checkformat(value => l_region_3
1941: hr_utility.set_location(l_proc, 2);
1942:
1943: if p_address_line3 is NULL and p_region_3 is not NULL then
1944: hr_utility.set_message(801, 'HR_72026_ADD_REGION3_NOT_NULL');
1945: hr_utility.raise_error;
1946: end if;
1947: l_region_3 := p_region_3;
1948: hr_chkfmt.checkformat(value => l_region_3
1949: ,format => 'KANA'
1952: ,maximum => NULL
1953: ,nullok => 'Y'
1954: ,rgeflg => l_rgeflg
1955: ,curcode => NULL);
1956: hr_utility.set_location(' Calling hr_chkfmt.checkformat3', 1);
1957: end if;
1958:
1959: hr_utility.set_location('Leaving:'|| l_proc, 1);
1960: end chk_address3_region3_comb;
1955: ,curcode => NULL);
1956: hr_utility.set_location(' Calling hr_chkfmt.checkformat3', 1);
1957: end if;
1958:
1959: hr_utility.set_location('Leaving:'|| l_proc, 1);
1960: end chk_address3_region3_comb;
1961: */
1962: --
1963: -- ----------------------------------------------------------------------------
1970: l_dynamic_sql varchar2(2000); -- Dynamic sql text
1971: l_rows integer; -- Num of rows returned
1972: l_dummy varchar2(1);
1973: begin
1974: hr_utility.set_location('Entering:'|| l_proc, 10);
1975: if p_postal_code is not null then
1976: --
1977: l_dynamic_sql := 'select null ' ||
1978: 'from per_jp_postal_codes p ' ||
1985: -- 3. Bind dynamic sql variables
1986: -- 4. Define dynamic sql columns
1987: -- 5. Execute and fetch dynamic sql
1988: --
1989: hr_utility.set_location(l_proc, 15);
1990: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1991: --
1992: hr_utility.set_location(l_proc, 20);
1993: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.native); -- Step 2
1988: --
1989: hr_utility.set_location(l_proc, 15);
1990: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1991: --
1992: hr_utility.set_location(l_proc, 20);
1993: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.native); -- Step 2
1994: --
1995: hr_utility.set_location(l_proc, 25);
1996: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1991: --
1992: hr_utility.set_location(l_proc, 20);
1993: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.native); -- Step 2
1994: --
1995: hr_utility.set_location(l_proc, 25);
1996: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1997: ':p_postal_code', p_postal_code);
1998:
1999: hr_utility.set_location(l_proc, 30);
1995: hr_utility.set_location(l_proc, 25);
1996: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1997: ':p_postal_code', p_postal_code);
1998:
1999: hr_utility.set_location(l_proc, 30);
2000: dbms_sql.define_column(l_sql_cursor, 1, l_dummy, 1); -- Step 4
2001: --
2002: hr_utility.set_location(l_proc, 35);
2003: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1998:
1999: hr_utility.set_location(l_proc, 30);
2000: dbms_sql.define_column(l_sql_cursor, 1, l_dummy, 1); -- Step 4
2001: --
2002: hr_utility.set_location(l_proc, 35);
2003: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
2004:
2005: if l_rows = 0 then
2006: dbms_sql.close_cursor(l_sql_cursor);
2003: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
2004:
2005: if l_rows = 0 then
2006: dbms_sql.close_cursor(l_sql_cursor);
2007: hr_utility.set_message(801, 'HR_72027_ADD_INVALID_POST_CODE');
2008: hr_utility.raise_error;
2009: end if;
2010: dbms_sql.close_cursor(l_sql_cursor);
2011: end if;
2004:
2005: if l_rows = 0 then
2006: dbms_sql.close_cursor(l_sql_cursor);
2007: hr_utility.set_message(801, 'HR_72027_ADD_INVALID_POST_CODE');
2008: hr_utility.raise_error;
2009: end if;
2010: dbms_sql.close_cursor(l_sql_cursor);
2011: end if;
2012: hr_utility.set_location(' Leaving:'|| l_proc, 100);
2008: hr_utility.raise_error;
2009: end if;
2010: dbms_sql.close_cursor(l_sql_cursor);
2011: end if;
2012: hr_utility.set_location(' Leaving:'|| l_proc, 100);
2013: end chk_jp_postal_code;
2014: */
2015: --
2016: -- ---------------------------------------------------------------------------
2099: l_count number(10);
2100: l_city_address_data get_city_address%ROWTYPE;
2101: --
2102: begin
2103: hr_utility.set_location('Entering:'|| l_proc, 1);
2104: --
2105: -- Check mandatory parameters have been set
2106: --
2107: hr_api.mandatory_arg_error
2122: and nvl(per_add_shd.g_old_rec.postal_code, hr_api.g_varchar2) <>
2123: nvl(p_postal_code, hr_api.g_varchar2)) or
2124: (NOT l_api_updating))
2125: then
2126: hr_utility.set_location(l_proc, 2);
2127: --
2128: -- Check if GEOCODES is installed for a US legislation
2129: --
2130: if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y' then
2132: -- Check that the zip code is set
2133: --
2134: if p_postal_code is null then
2135: --
2136: hr_utility.set_message(800, 'PER_52991_ADD_NO_ZIP_SET');
2137: hr_utility.raise_error;
2138: --
2139: end if;
2140: /*----------------------- Changes start for 5367066 ---------------------*/
2133: --
2134: if p_postal_code is null then
2135: --
2136: hr_utility.set_message(800, 'PER_52991_ADD_NO_ZIP_SET');
2137: hr_utility.raise_error;
2138: --
2139: end if;
2140: /*----------------------- Changes start for 5367066 ---------------------*/
2141:
2139: end if;
2140: /*----------------------- Changes start for 5367066 ---------------------*/
2141:
2142: begin
2143: hr_utility.set_location('Postal code ='||substr(p_postal_code,1,5), 21);
2144: hr_utility.set_location('Town city ='||p_town_or_city, 22);
2145: open get_city_address;
2146: loop
2147: fetch get_city_address into l_city_address_data;
2140: /*----------------------- Changes start for 5367066 ---------------------*/
2141:
2142: begin
2143: hr_utility.set_location('Postal code ='||substr(p_postal_code,1,5), 21);
2144: hr_utility.set_location('Town city ='||p_town_or_city, 22);
2145: open get_city_address;
2146: loop
2147: fetch get_city_address into l_city_address_data;
2148: l_count := get_city_address%ROWCOUNT;
2145: open get_city_address;
2146: loop
2147: fetch get_city_address into l_city_address_data;
2148: l_count := get_city_address%ROWCOUNT;
2149: hr_utility.set_location('NO of rows returned from cursor = '||l_count, 23);
2150: exit when get_city_address%NOTFOUND;
2151: if (substr(p_postal_code,1,5) between l_city_address_data.zip_start and l_city_address_data.zip_end) then
2152: null;
2153: else
2150: exit when get_city_address%NOTFOUND;
2151: if (substr(p_postal_code,1,5) between l_city_address_data.zip_start and l_city_address_data.zip_end) then
2152: null;
2153: else
2154: hr_utility.set_message(800, 'HR_7786_ADDR_US_ZIP_OOR');
2155: hr_utility.set_message_token('ZIP_START',l_city_address_data.zip_start);
2156: hr_utility.set_message_token('ZIP_END',l_city_address_data.zip_end);
2157: hr_utility.raise_error;
2158: end if;
2151: if (substr(p_postal_code,1,5) between l_city_address_data.zip_start and l_city_address_data.zip_end) then
2152: null;
2153: else
2154: hr_utility.set_message(800, 'HR_7786_ADDR_US_ZIP_OOR');
2155: hr_utility.set_message_token('ZIP_START',l_city_address_data.zip_start);
2156: hr_utility.set_message_token('ZIP_END',l_city_address_data.zip_end);
2157: hr_utility.raise_error;
2158: end if;
2159: end loop;
2152: null;
2153: else
2154: hr_utility.set_message(800, 'HR_7786_ADDR_US_ZIP_OOR');
2155: hr_utility.set_message_token('ZIP_START',l_city_address_data.zip_start);
2156: hr_utility.set_message_token('ZIP_END',l_city_address_data.zip_end);
2157: hr_utility.raise_error;
2158: end if;
2159: end loop;
2160: --
2153: else
2154: hr_utility.set_message(800, 'HR_7786_ADDR_US_ZIP_OOR');
2155: hr_utility.set_message_token('ZIP_START',l_city_address_data.zip_start);
2156: hr_utility.set_message_token('ZIP_END',l_city_address_data.zip_end);
2157: hr_utility.raise_error;
2158: end if;
2159: end loop;
2160: --
2161: close get_city_address;
2159: end loop;
2160: --
2161: close get_city_address;
2162: if l_count = 0 then
2163: hr_utility.set_message(800, 'HR_51195_ADD_INVALID_ZIP_CODE');
2164: hr_utility.raise_error;
2165: end if;
2166: hr_utility.set_location('Leaveing the Cursor =', 24);
2167: end;
2160: --
2161: close get_city_address;
2162: if l_count = 0 then
2163: hr_utility.set_message(800, 'HR_51195_ADD_INVALID_ZIP_CODE');
2164: hr_utility.raise_error;
2165: end if;
2166: hr_utility.set_location('Leaveing the Cursor =', 24);
2167: end;
2168:
2162: if l_count = 0 then
2163: hr_utility.set_message(800, 'HR_51195_ADD_INVALID_ZIP_CODE');
2164: hr_utility.raise_error;
2165: end if;
2166: hr_utility.set_location('Leaveing the Cursor =', 24);
2167: end;
2168:
2169: begin
2170: --
2168:
2169: begin
2170: --
2171: if length(p_postal_code) = 5 then
2172: hr_utility.set_location(l_proc, 4);
2173: --
2174: -- Check if zip code is all numbers
2175: --
2176: for i in 1..5 loop
2175: --
2176: for i in 1..5 loop
2177: if(substr(p_postal_code,i,1)
2178: not between '0' and '9') then
2179: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2180: hr_utility.raise_error;
2181: end if;
2182: end loop;
2183: --
2176: for i in 1..5 loop
2177: if(substr(p_postal_code,i,1)
2178: not between '0' and '9') then
2179: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2180: hr_utility.raise_error;
2181: end if;
2182: end loop;
2183: --
2184: elsif length(p_postal_code) = 10 then
2181: end if;
2182: end loop;
2183: --
2184: elsif length(p_postal_code) = 10 then
2185: hr_utility.set_location(l_proc, 5);
2186: --
2187: -- Parse zip code to validate for correct format.
2188: --
2189: l_postal_code_1 := substr(p_postal_code,1,5);
2194: --
2195: for i in 1..5 loop
2196: if(substr(l_postal_code_1,i,1)
2197: not between '0' and '9') then
2198: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2199: hr_utility.raise_error;
2200: end if;
2201: end loop;
2202: hr_utility.set_location(l_proc, 6);
2195: for i in 1..5 loop
2196: if(substr(l_postal_code_1,i,1)
2197: not between '0' and '9') then
2198: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2199: hr_utility.raise_error;
2200: end if;
2201: end loop;
2202: hr_utility.set_location(l_proc, 6);
2203: --
2198: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2199: hr_utility.raise_error;
2200: end if;
2201: end loop;
2202: hr_utility.set_location(l_proc, 6);
2203: --
2204: -- Validate last 4 characters are numbers
2205: --
2206: for i in 1..4 loop
2205: --
2206: for i in 1..4 loop
2207: if(substr(l_postal_code_3,i,1)
2208: not between '0' and '9') then
2209: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2210: hr_utility.raise_error;
2211: end if;
2212: end loop;
2213: hr_utility.set_location(l_proc, 7);
2206: for i in 1..4 loop
2207: if(substr(l_postal_code_3,i,1)
2208: not between '0' and '9') then
2209: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2210: hr_utility.raise_error;
2211: end if;
2212: end loop;
2213: hr_utility.set_location(l_proc, 7);
2214: --
2209: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2210: hr_utility.raise_error;
2211: end if;
2212: end loop;
2213: hr_utility.set_location(l_proc, 7);
2214: --
2215: -- Validate last sixth characters is '-'
2216: --
2217: if l_postal_code_2 <> '-' then
2214: --
2215: -- Validate last sixth characters is '-'
2216: --
2217: if l_postal_code_2 <> '-' then
2218: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2219: hr_utility.raise_error;
2220: end if;
2221: else
2222: --
2215: -- Validate last sixth characters is '-'
2216: --
2217: if l_postal_code_2 <> '-' then
2218: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2219: hr_utility.raise_error;
2220: end if;
2221: else
2222: --
2223: -- If zip code is not 5 or 10 character long
2221: else
2222: --
2223: -- If zip code is not 5 or 10 character long
2224: --
2225: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2226: hr_utility.raise_error;
2227: end if;
2228: --
2229: -- If an invalid zip code character generates an
2222: --
2223: -- If zip code is not 5 or 10 character long
2224: --
2225: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2226: hr_utility.raise_error;
2227: end if;
2228: --
2229: -- If an invalid zip code character generates an
2230: -- exception
2230: -- exception
2231: --
2232: exception
2233: when others then
2234: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2235: hr_utility.raise_error;
2236: end;
2237:
2238: /*----------------------- Changes End for 5367066 ---------------------*/
2231: --
2232: exception
2233: when others then
2234: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2235: hr_utility.raise_error;
2236: end;
2237:
2238: /*----------------------- Changes End for 5367066 ---------------------*/
2239:
2246: -- 8 characters long
2247: --
2248: if p_style = 'GB' then
2249: if length(p_postal_code) > 8 then
2250: hr_utility.set_message(801, 'HR_7306_ADD_POST_CODE');
2251: hr_utility.raise_error;
2252: end if;
2253: --
2254: -- Check that the US postal code is either 5 or 10 character
2247: --
2248: if p_style = 'GB' then
2249: if length(p_postal_code) > 8 then
2250: hr_utility.set_message(801, 'HR_7306_ADD_POST_CODE');
2251: hr_utility.raise_error;
2252: end if;
2253: --
2254: -- Check that the US postal code is either 5 or 10 character
2255: --
2253: --
2254: -- Check that the US postal code is either 5 or 10 character
2255: --
2256: elsif p_style = 'US' and l_geocodes_installed = 'Y' then
2257: hr_utility.set_location(l_proc, 3);
2258: --
2259: begin
2260: --
2261: if length(p_postal_code) = 5 then
2258: --
2259: begin
2260: --
2261: if length(p_postal_code) = 5 then
2262: hr_utility.set_location(l_proc, 4);
2263: --
2264: -- Check if zip code is all numbers
2265: --
2266: for i in 1..5 loop
2265: --
2266: for i in 1..5 loop
2267: if(substr(p_postal_code,i,1)
2268: not between '0' and '9') then
2269: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2270: hr_utility.raise_error;
2271: end if;
2272: end loop;
2273: --
2266: for i in 1..5 loop
2267: if(substr(p_postal_code,i,1)
2268: not between '0' and '9') then
2269: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2270: hr_utility.raise_error;
2271: end if;
2272: end loop;
2273: --
2274: elsif length(p_postal_code) = 10 then
2271: end if;
2272: end loop;
2273: --
2274: elsif length(p_postal_code) = 10 then
2275: hr_utility.set_location(l_proc, 5);
2276: --
2277: -- Parse zip code to validate for correct format.
2278: --
2279: l_postal_code_1 := substr(p_postal_code,1,5);
2284: --
2285: for i in 1..5 loop
2286: if(substr(l_postal_code_1,i,1)
2287: not between '0' and '9') then
2288: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2289: hr_utility.raise_error;
2290: end if;
2291: end loop;
2292: hr_utility.set_location(l_proc, 6);
2285: for i in 1..5 loop
2286: if(substr(l_postal_code_1,i,1)
2287: not between '0' and '9') then
2288: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2289: hr_utility.raise_error;
2290: end if;
2291: end loop;
2292: hr_utility.set_location(l_proc, 6);
2293: --
2288: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2289: hr_utility.raise_error;
2290: end if;
2291: end loop;
2292: hr_utility.set_location(l_proc, 6);
2293: --
2294: -- Validate last 4 characters are numbers
2295: --
2296: for i in 1..4 loop
2295: --
2296: for i in 1..4 loop
2297: if(substr(l_postal_code_3,i,1)
2298: not between '0' and '9') then
2299: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2300: hr_utility.raise_error;
2301: end if;
2302: end loop;
2303: hr_utility.set_location(l_proc, 7);
2296: for i in 1..4 loop
2297: if(substr(l_postal_code_3,i,1)
2298: not between '0' and '9') then
2299: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2300: hr_utility.raise_error;
2301: end if;
2302: end loop;
2303: hr_utility.set_location(l_proc, 7);
2304: --
2299: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2300: hr_utility.raise_error;
2301: end if;
2302: end loop;
2303: hr_utility.set_location(l_proc, 7);
2304: --
2305: -- Validate last sixth characters is '-'
2306: --
2307: if l_postal_code_2 <> '-' then
2304: --
2305: -- Validate last sixth characters is '-'
2306: --
2307: if l_postal_code_2 <> '-' then
2308: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2309: hr_utility.raise_error;
2310: end if;
2311:
2312: else
2305: -- Validate last sixth characters is '-'
2306: --
2307: if l_postal_code_2 <> '-' then
2308: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2309: hr_utility.raise_error;
2310: end if;
2311:
2312: else
2313: --
2312: else
2313: --
2314: -- If zip code is not 5 or 10 character long
2315: --
2316: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2317: hr_utility.raise_error;
2318: end if;
2319: --
2320: -- If an invalid zip code character generates an
2313: --
2314: -- If zip code is not 5 or 10 character long
2315: --
2316: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2317: hr_utility.raise_error;
2318: end if;
2319: --
2320: -- If an invalid zip code character generates an
2321: -- exception
2321: -- exception
2322: --
2323: exception
2324: when others then
2325: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2326: hr_utility.raise_error;
2327: end;
2328: /* Bug 1677965
2329: elsif p_style = 'JP' then
2322: --
2323: exception
2324: when others then
2325: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2326: hr_utility.raise_error;
2327: end;
2328: /* Bug 1677965
2329: elsif p_style = 'JP' then
2330: hr_utility.set_location(l_proc, 8);
2326: hr_utility.raise_error;
2327: end;
2328: /* Bug 1677965
2329: elsif p_style = 'JP' then
2330: hr_utility.set_location(l_proc, 8);
2331: chk_jp_postal_code(p_postal_code);
2332: */
2333: end if;
2334: --
2337: end if;
2338: --
2339: end if;
2340: --
2341: hr_utility.set_location(' Leaving:'|| l_proc, 9);
2342: exception
2343: when app_exception.application_exception then
2344: if hr_multi_message.exception_add
2345: (p_associated_column1 => 'PER_ADDRESSES.POSTAL_CODE'
2343: when app_exception.application_exception then
2344: if hr_multi_message.exception_add
2345: (p_associated_column1 => 'PER_ADDRESSES.POSTAL_CODE'
2346: ) then
2347: hr_utility.set_location(' Leaving:'||l_proc,10);
2348: raise;
2349: end if;
2350: hr_utility.set_location(' Leaving:'||l_proc,11);
2351: --
2346: ) then
2347: hr_utility.set_location(' Leaving:'||l_proc,10);
2348: raise;
2349: end if;
2350: hr_utility.set_location(' Leaving:'||l_proc,11);
2351: --
2352: end chk_postal_code;
2353: --
2354: -- ---------------------------------------------------------------------------
2404: --
2405: begin
2406: if p_tax_address_zip is not null
2407: then
2408: hr_utility.set_location('Entering:'|| l_proc, 1);
2409: --
2410: -- Check mandatory parameters have been set
2411: --
2412: hr_api.mandatory_arg_error
2427: and nvl(per_add_shd.g_old_rec.add_information20, hr_api.g_varchar2) <>
2428: nvl(p_tax_address_zip, hr_api.g_varchar2)) or
2429: (NOT l_api_updating))
2430: then
2431: hr_utility.set_location(l_proc, 2);
2432: --
2433: -- Check if GEOCODES is installed for a US legislation
2434: --
2435: if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y'
2439: --
2440: begin
2441: --
2442: if length(p_tax_address_zip) = 5 then
2443: hr_utility.set_location(l_proc, 3);
2444: --
2445: -- Check if zip code is all numbers
2446: --
2447: for i in 1..5 loop
2446: --
2447: for i in 1..5 loop
2448: if(substr(p_tax_address_zip,i,1)
2449: not between '0' and '9') then
2450: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2451: hr_utility.raise_error;
2452: end if;
2453: end loop;
2454: --
2447: for i in 1..5 loop
2448: if(substr(p_tax_address_zip,i,1)
2449: not between '0' and '9') then
2450: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2451: hr_utility.raise_error;
2452: end if;
2453: end loop;
2454: --
2455: elsif length(p_tax_address_zip) = 10 then
2452: end if;
2453: end loop;
2454: --
2455: elsif length(p_tax_address_zip) = 10 then
2456: hr_utility.set_location(l_proc, 4);
2457: --
2458: -- Parse zip code to validate for correct format.
2459: --
2460: l_tax_address_zip_1 := substr(p_tax_address_zip,1,5);
2466: --
2467: for i in 1..5 loop
2468: if(substr(l_tax_address_zip_1,i,1)
2469: not between '0' and '9') then
2470: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2471: hr_utility.raise_error;
2472: end if;
2473: end loop;
2474: hr_utility.set_location(l_proc, 5);
2467: for i in 1..5 loop
2468: if(substr(l_tax_address_zip_1,i,1)
2469: not between '0' and '9') then
2470: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2471: hr_utility.raise_error;
2472: end if;
2473: end loop;
2474: hr_utility.set_location(l_proc, 5);
2475: --
2470: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2471: hr_utility.raise_error;
2472: end if;
2473: end loop;
2474: hr_utility.set_location(l_proc, 5);
2475: --
2476: -- Validate last 4 characters are numbers
2477: --
2478: for i in 1..4 loop
2477: --
2478: for i in 1..4 loop
2479: if(substr(l_tax_address_zip_3,i,1)
2480: not between '0' and '9') then
2481: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2482: hr_utility.raise_error;
2483: end if;
2484: end loop;
2485: hr_utility.set_location(l_proc, 6);
2478: for i in 1..4 loop
2479: if(substr(l_tax_address_zip_3,i,1)
2480: not between '0' and '9') then
2481: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2482: hr_utility.raise_error;
2483: end if;
2484: end loop;
2485: hr_utility.set_location(l_proc, 6);
2486: --
2481: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2482: hr_utility.raise_error;
2483: end if;
2484: end loop;
2485: hr_utility.set_location(l_proc, 6);
2486: --
2487: -- Validate last sixth characters is '-'
2488: --
2489: if l_tax_address_zip_2 <> '-' then
2486: --
2487: -- Validate last sixth characters is '-'
2488: --
2489: if l_tax_address_zip_2 <> '-' then
2490: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2491: hr_utility.raise_error;
2492: end if;
2493: else
2494: --
2487: -- Validate last sixth characters is '-'
2488: --
2489: if l_tax_address_zip_2 <> '-' then
2490: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2491: hr_utility.raise_error;
2492: end if;
2493: else
2494: --
2495: -- If zip code is not 5 or 10 character long
2493: else
2494: --
2495: -- If zip code is not 5 or 10 character long
2496: --
2497: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2498: hr_utility.raise_error;
2499: end if;
2500: --
2501: -- If an invalid zip code character generates an
2494: --
2495: -- If zip code is not 5 or 10 character long
2496: --
2497: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2498: hr_utility.raise_error;
2499: end if;
2500: --
2501: -- If an invalid zip code character generates an
2502: -- exception
2502: -- exception
2503: --
2504: exception
2505: when others then
2506: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2507: hr_utility.raise_error;
2508: end;
2509: end if;
2510: --
2503: --
2504: exception
2505: when others then
2506: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2507: hr_utility.raise_error;
2508: end;
2509: end if;
2510: --
2511: end if;
2510: --
2511: end if;
2512: --
2513: end if;
2514: hr_utility.set_location(' Leaving:'|| l_proc, 7);
2515: exception
2516: when app_exception.application_exception then
2517: if hr_multi_message.exception_add
2518: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION20'
2516: when app_exception.application_exception then
2517: if hr_multi_message.exception_add
2518: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION20'
2519: ) then
2520: hr_utility.set_location(' Leaving:'||l_proc,6);
2521: raise;
2522: end if;
2523: hr_utility.set_location(' Leaving:'||l_proc,7);
2524: --
2519: ) then
2520: hr_utility.set_location(' Leaving:'||l_proc,6);
2521: raise;
2522: end if;
2523: hr_utility.set_location(' Leaving:'||l_proc,7);
2524: --
2525: end chk_tax_address_zip;
2526: --
2527: -- ---------------------------------------------------------------------------
2582: from pay_us_counties
2583: where county_name = p_region_1;
2584: --
2585: begin
2586: hr_utility.set_location('Entering:'|| l_proc, 1);
2587: --
2588: -- Check mandatory parameters have been set
2589: --
2590: hr_api.mandatory_arg_error
2611: nvl(per_add_shd.g_old_rec.region_1, hr_api.g_varchar2) <>
2612: nvl(p_region_1, hr_api.g_varchar2)) or
2613: (NOT l_api_updating))
2614: then
2615: hr_utility.set_location(l_proc, 2);
2616: --
2617: -- Check for GEOCODES in a US legislation
2618: --
2619: if p_style = 'US'
2623: -- Check that the county is set
2624: --
2625: if p_region_1 is null then
2626: --
2627: hr_utility.set_message(800, 'PER_52984_ADD_NO_COUNTY_SET');
2628: hr_utility.raise_error;
2629: --
2630: end if;
2631: --
2624: --
2625: if p_region_1 is null then
2626: --
2627: hr_utility.set_message(800, 'PER_52984_ADD_NO_COUNTY_SET');
2628: hr_utility.raise_error;
2629: --
2630: end if;
2631: --
2632: hr_utility.set_location(l_proc, 5);
2628: hr_utility.raise_error;
2629: --
2630: end if;
2631: --
2632: hr_utility.set_location(l_proc, 5);
2633: open csr_valid_us_county;
2634: fetch csr_valid_us_county into l_exists;
2635: if csr_valid_us_county%notfound then
2636: --
2634: fetch csr_valid_us_county into l_exists;
2635: if csr_valid_us_county%notfound then
2636: --
2637: close csr_valid_us_county;
2638: hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
2639: hr_utility.raise_error;
2640: --
2641: end if;
2642: --
2635: if csr_valid_us_county%notfound then
2636: --
2637: close csr_valid_us_county;
2638: hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
2639: hr_utility.raise_error;
2640: --
2641: end if;
2642: --
2643: else
2644: --
2645: -- Check that value for region_1 is valid
2646: --
2647: if p_region_1 is not null then
2648: hr_utility.set_location(l_proc, 3);
2649: --
2650: if p_style = 'GB' then
2651: hr_utility.set_location(l_proc, 4);
2652: --
2647: if p_region_1 is not null then
2648: hr_utility.set_location(l_proc, 3);
2649: --
2650: if p_style = 'GB' then
2651: hr_utility.set_location(l_proc, 4);
2652: --
2653: if hr_api.not_exists_in_hr_lookups
2654: (p_effective_date => p_effective_date
2655: ,p_lookup_type => 'GB_COUNTY'
2655: ,p_lookup_type => 'GB_COUNTY'
2656: ,p_lookup_code => p_region_1
2657: ) then
2658: --
2659: hr_utility.set_message(801, 'HR_7307_ADD_GB_REGION_1');
2660: hr_utility.raise_error;
2661: --
2662: end if;
2663: --
2656: ,p_lookup_code => p_region_1
2657: ) then
2658: --
2659: hr_utility.set_message(801, 'HR_7307_ADD_GB_REGION_1');
2660: hr_utility.raise_error;
2661: --
2662: end if;
2663: --
2664: end if;
2667: --
2668: end if;
2669: --
2670: end if;
2671: hr_utility.set_location(' Leaving:'|| l_proc, 6);
2672: exception
2673: when app_exception.application_exception then
2674: if hr_multi_message.exception_add
2675: (p_associated_column1 => 'PER_ADDRESSES.REGION_1'
2673: when app_exception.application_exception then
2674: if hr_multi_message.exception_add
2675: (p_associated_column1 => 'PER_ADDRESSES.REGION_1'
2676: ) then
2677: hr_utility.set_location(' Leaving:'||l_proc,7);
2678: raise;
2679: end if;
2680: hr_utility.set_location(' Leaving:'||l_proc,8);
2681: --
2676: ) then
2677: hr_utility.set_location(' Leaving:'||l_proc,7);
2678: raise;
2679: end if;
2680: hr_utility.set_location(' Leaving:'||l_proc,8);
2681: --
2682: end chk_region_1;
2683: --
2684: --
2734: from pay_us_counties
2735: where county_name = p_tax_county;
2736: --
2737: begin
2738: hr_utility.set_location('Entering:'|| l_proc, 1);
2739: --
2740: -- Check mandatory parameters have been set
2741: --
2742: if p_tax_county is not null
2765: nvl(per_add_shd.g_old_rec.add_information19, hr_api.g_varchar2) <>
2766: nvl(p_tax_county, hr_api.g_varchar2)) or
2767: (NOT l_api_updating))
2768: then
2769: hr_utility.set_location(l_proc, 2);
2770: --
2771: -- Check for GEOCODES in a US legislation
2772: --
2773: if p_style = 'US'
2777: fetch csr_valid_us_county into l_exists;
2778: if csr_valid_us_county%notfound then
2779: --
2780: close csr_valid_us_county;
2781: hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
2782: hr_utility.raise_error;
2783: --
2784: end if;
2785: close csr_valid_us_county;
2778: if csr_valid_us_county%notfound then
2779: --
2780: close csr_valid_us_county;
2781: hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
2782: hr_utility.raise_error;
2783: --
2784: end if;
2785: close csr_valid_us_county;
2786: --
2789: end if;
2790: else
2791: null;
2792: end if;
2793: hr_utility.set_location(' Leaving:'|| l_proc, 6);
2794: exception
2795: when app_exception.application_exception then
2796: if hr_multi_message.exception_add
2797: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION19'
2795: when app_exception.application_exception then
2796: if hr_multi_message.exception_add
2797: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION19'
2798: ) then
2799: hr_utility.set_location(' Leaving:'||l_proc,7);
2800: raise;
2801: end if;
2802: hr_utility.set_location(' Leaving:'||l_proc,8);
2803: --
2798: ) then
2799: hr_utility.set_location(' Leaving:'||l_proc,7);
2800: raise;
2801: end if;
2802: hr_utility.set_location(' Leaving:'||l_proc,8);
2803: --
2804: end chk_tax_county;
2805: --
2806: -- ---------------------------------------------------------------------------
2861: from pay_us_states
2862: where state_abbrev = p_region_2;
2863: --
2864: begin
2865: hr_utility.set_location('Entering:'|| l_proc, 1);
2866: --
2867: -- Check mandatory parameters have been set.
2868: --
2869: hr_api.mandatory_arg_error
2891: nvl(per_add_shd.g_old_rec.region_2, hr_api.g_varchar2) <>
2892: nvl(p_region_2, hr_api.g_varchar2)) or
2893: (NOT l_api_updating))
2894: then
2895: hr_utility.set_location(l_proc, 2);
2896: --
2897: -- Check if GEOCODES is installed under a US legislation
2898: --
2899: if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y'
2897: -- Check if GEOCODES is installed under a US legislation
2898: --
2899: if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y'
2900: then
2901: hr_utility.set_location(l_proc, 5);
2902: --
2903: -- Check if the state is set
2904: --
2905: if p_region_2 is null then
2903: -- Check if the state is set
2904: --
2905: if p_region_2 is null then
2906: --
2907: hr_utility.set_message(800, 'PER_52985_ADD_NO_STATE_SET');
2908: hr_utility.raise_error;
2909: --
2910: end if;
2911: --
2904: --
2905: if p_region_2 is null then
2906: --
2907: hr_utility.set_message(800, 'PER_52985_ADD_NO_STATE_SET');
2908: hr_utility.raise_error;
2909: --
2910: end if;
2911: --
2912: open csr_valid_state;
2912: open csr_valid_state;
2913: fetch csr_valid_state into l_exists;
2914: if csr_valid_state%notfound then
2915: close csr_valid_state;
2916: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
2917: hr_utility.raise_error;
2918: end if;
2919: close csr_valid_state;
2920: --
2913: fetch csr_valid_state into l_exists;
2914: if csr_valid_state%notfound then
2915: close csr_valid_state;
2916: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
2917: hr_utility.raise_error;
2918: end if;
2919: close csr_valid_state;
2920: --
2921: else
2922: --
2923: -- Check that value for region_2 is valid.
2924: --
2925: if p_region_2 is not null then
2926: hr_utility.set_location(l_proc, 3);
2927: --
2928: if p_style = 'US'
2929: then
2930: hr_utility.set_location(l_proc, 4);
2926: hr_utility.set_location(l_proc, 3);
2927: --
2928: if p_style = 'US'
2929: then
2930: hr_utility.set_location(l_proc, 4);
2931: --
2932: if hr_api.not_exists_in_hr_lookups
2933: (p_effective_date => p_effective_date
2934: ,p_lookup_type => 'US_STATE'
2936: )
2937: then
2938: --
2939: -- Error: Invalid region 2.
2940: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
2941: hr_utility.raise_error;
2942: --
2943: end if;
2944: --
2937: then
2938: --
2939: -- Error: Invalid region 2.
2940: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
2941: hr_utility.raise_error;
2942: --
2943: end if;
2944: --
2945: end if;
2949: end if;
2950: --
2951: end if;
2952: --
2953: hr_utility.set_location(' Leaving:'|| l_proc, 10);
2954: exception
2955: when app_exception.application_exception then
2956: if hr_multi_message.exception_add
2957: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
2955: when app_exception.application_exception then
2956: if hr_multi_message.exception_add
2957: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
2958: ) then
2959: hr_utility.set_location(' Leaving:'||l_proc,11);
2960: raise;
2961: end if;
2962: hr_utility.set_location(' Leaving:'||l_proc,12);
2963: --
2958: ) then
2959: hr_utility.set_location(' Leaving:'||l_proc,11);
2960: raise;
2961: end if;
2962: hr_utility.set_location(' Leaving:'||l_proc,12);
2963: --
2964: end chk_region_2;
2965: --
2966: -- ---------------------------------------------------------------------------
3023: --
3024: begin
3025: if p_tax_state is not null
3026: then
3027: hr_utility.set_location('Entering:'|| l_proc, 1);
3028: --
3029: -- Check mandatory parameters have been set.
3030: --
3031: hr_api.mandatory_arg_error
3053: nvl(per_add_shd.g_old_rec.add_information17, hr_api.g_varchar2) <>
3054: nvl(p_tax_state, hr_api.g_varchar2)) or
3055: (NOT l_api_updating))
3056: then
3057: hr_utility.set_location(l_proc, 2);
3058: --
3059: -- Check if GEOCODES is installed under a US legislation
3060: --
3061: if hr_general.chk_geocodes_installed = 'Y'
3059: -- Check if GEOCODES is installed under a US legislation
3060: --
3061: if hr_general.chk_geocodes_installed = 'Y'
3062: then
3063: hr_utility.set_location(l_proc, 5);
3064: open csr_valid_state;
3065: fetch csr_valid_state into l_exists;
3066: if csr_valid_state%notfound then
3067: close csr_valid_state;
3064: open csr_valid_state;
3065: fetch csr_valid_state into l_exists;
3066: if csr_valid_state%notfound then
3067: close csr_valid_state;
3068: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
3069: hr_utility.raise_error;
3070: end if;
3071: close csr_valid_state;
3072: --
3065: fetch csr_valid_state into l_exists;
3066: if csr_valid_state%notfound then
3067: close csr_valid_state;
3068: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
3069: hr_utility.raise_error;
3070: end if;
3071: close csr_valid_state;
3072: --
3073: else
3073: else
3074: --
3075: -- Check that value for tax_state is valid.
3076: --
3077: hr_utility.set_location(l_proc, 4);
3078: --
3079: if hr_api.not_exists_in_hr_lookups
3080: (p_effective_date => p_effective_date
3081: ,p_lookup_type => 'US_STATE'
3083: )
3084: then
3085: --
3086: -- Error: Invalid tax_state.
3087: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
3088: hr_utility.raise_error;
3089: --
3090: end if;
3091: --
3084: then
3085: --
3086: -- Error: Invalid tax_state.
3087: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
3088: hr_utility.raise_error;
3089: --
3090: end if;
3091: --
3092: end if;
3093: --
3094: end if;
3095: end if;
3096: --
3097: hr_utility.set_location(' Leaving:'|| l_proc, 10);
3098: exception
3099: when app_exception.application_exception then
3100: if hr_multi_message.exception_add
3101: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3099: when app_exception.application_exception then
3100: if hr_multi_message.exception_add
3101: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3102: ) then
3103: hr_utility.set_location(' Leaving:'||l_proc,11);
3104: raise;
3105: end if;
3106: hr_utility.set_location(' Leaving:'||l_proc,12);
3107: --
3102: ) then
3103: hr_utility.set_location(' Leaving:'||l_proc,11);
3104: raise;
3105: end if;
3106: hr_utility.set_location(' Leaving:'||l_proc,12);
3107: --
3108: end chk_tax_state;
3109: --
3110: -- ---------------------------------------------------------------------------
3158: select null
3159: from pay_us_city_names
3160: where city_name = p_town_or_city;
3161: begin
3162: hr_utility.set_location('Entering:'|| l_proc, 1);
3163: --
3164: -- Check mandatory parameters have been set
3165: --
3166: hr_api.mandatory_arg_error
3181: nvl(per_add_shd.g_old_rec.town_or_city, hr_api.g_varchar2) <>
3182: nvl(p_town_or_city, hr_api.g_varchar2)) or
3183: (NOT l_api_updating))
3184: then
3185: hr_utility.set_location(l_proc, 2);
3186: --
3187: if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y' then
3188: --
3189: -- Check that the city is set
3189: -- Check that the city is set
3190: --
3191: if p_town_or_city is null then
3192: --
3193: hr_utility.set_message(800, 'PER_52986_ADD_NO_CITY_SET');
3194: hr_utility.raise_error;
3195: --
3196: end if;
3197: --
3190: --
3191: if p_town_or_city is null then
3192: --
3193: hr_utility.set_message(800, 'PER_52986_ADD_NO_CITY_SET');
3194: hr_utility.raise_error;
3195: --
3196: end if;
3197: --
3198: open csr_valid_town_or_city;
3198: open csr_valid_town_or_city;
3199: fetch csr_valid_town_or_city into l_exists;
3200: if csr_valid_town_or_city%notfound then
3201: close csr_valid_town_or_city;
3202: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3203: hr_utility.raise_error;
3204: end if;
3205: close csr_valid_town_or_city;
3206: hr_utility.set_location(l_proc, 4);
3199: fetch csr_valid_town_or_city into l_exists;
3200: if csr_valid_town_or_city%notfound then
3201: close csr_valid_town_or_city;
3202: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3203: hr_utility.raise_error;
3204: end if;
3205: close csr_valid_town_or_city;
3206: hr_utility.set_location(l_proc, 4);
3207: --
3202: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3203: hr_utility.raise_error;
3204: end if;
3205: close csr_valid_town_or_city;
3206: hr_utility.set_location(l_proc, 4);
3207: --
3208: end if;
3209: --
3210: end if;
3208: end if;
3209: --
3210: end if;
3211: --
3212: hr_utility.set_location(' Leaving:'|| l_proc, 5);
3213: exception
3214: when app_exception.application_exception then
3215: if hr_multi_message.exception_add
3216: (p_associated_column1 => 'PER_ADDRESSES.TOWN_OR_CITY'
3214: when app_exception.application_exception then
3215: if hr_multi_message.exception_add
3216: (p_associated_column1 => 'PER_ADDRESSES.TOWN_OR_CITY'
3217: ) then
3218: hr_utility.set_location(' Leaving:'||l_proc,6);
3219: raise;
3220: end if;
3221: hr_utility.set_location(' Leaving:'||l_proc,7);
3222: --
3217: ) then
3218: hr_utility.set_location(' Leaving:'||l_proc,6);
3219: raise;
3220: end if;
3221: hr_utility.set_location(' Leaving:'||l_proc,7);
3222: --
3223: end chk_town_or_city;
3224: --
3225: -- ---------------------------------------------------------------------------
3275: where city_name = p_tax_city;
3276: begin
3277: if p_tax_city is not null
3278: then
3279: hr_utility.set_location('Entering:'|| l_proc, 1);
3280: --
3281: -- Check mandatory parameters have been set
3282: --
3283: hr_api.mandatory_arg_error
3298: nvl(per_add_shd.g_old_rec.add_information18, hr_api.g_varchar2) <>
3299: nvl(p_tax_city, hr_api.g_varchar2)) or
3300: (NOT l_api_updating))
3301: then
3302: hr_utility.set_location(l_proc, 2);
3303: --
3304: if hr_general.chk_geocodes_installed = 'Y' then
3305: open csr_valid_tax_city;
3306: fetch csr_valid_tax_city into l_exists;
3305: open csr_valid_tax_city;
3306: fetch csr_valid_tax_city into l_exists;
3307: if csr_valid_tax_city%notfound then
3308: close csr_valid_tax_city;
3309: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3310: hr_utility.raise_error;
3311: end if;
3312: close csr_valid_tax_city;
3313: hr_utility.set_location(l_proc, 4);
3306: fetch csr_valid_tax_city into l_exists;
3307: if csr_valid_tax_city%notfound then
3308: close csr_valid_tax_city;
3309: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3310: hr_utility.raise_error;
3311: end if;
3312: close csr_valid_tax_city;
3313: hr_utility.set_location(l_proc, 4);
3314: --
3309: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3310: hr_utility.raise_error;
3311: end if;
3312: close csr_valid_tax_city;
3313: hr_utility.set_location(l_proc, 4);
3314: --
3315: end if;
3316: --
3317: end if;
3316: --
3317: end if;
3318: --
3319: end if;
3320: hr_utility.set_location(' Leaving:'|| l_proc, 5);
3321: exception
3322: when app_exception.application_exception then
3323: if hr_multi_message.exception_add
3324: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION18'
3322: when app_exception.application_exception then
3323: if hr_multi_message.exception_add
3324: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION18'
3325: ) then
3326: hr_utility.set_location(' Leaving:'||l_proc,6);
3327: raise;
3328: end if;
3329: hr_utility.set_location(' Leaving:'||l_proc,7);
3330: --
3325: ) then
3326: hr_utility.set_location(' Leaving:'||l_proc,6);
3327: raise;
3328: end if;
3329: hr_utility.set_location(' Leaving:'||l_proc,7);
3330: --
3331: end chk_tax_city;
3332: --
3333: -- ---------------------------------------------------------------------------
3441: and l_postal_code between zip.zip_start
3442: and zip.zip_end;
3443: --
3444: begin
3445: hr_utility.set_location('Entering:'|| l_proc, 10);
3446: --
3447: -- Only proceed with validation if :
3448: -- a) US address style and payroll is installed under US legislation and
3449: -- b) The current g_old_rec is current and
3463: (nvl(per_add_shd.g_old_rec.town_or_city, hr_api.g_varchar2) <>
3464: nvl(p_town_or_city, hr_api.g_varchar2))) or
3465: (NOT l_api_updating)) then
3466: --
3467: hr_utility.set_location(l_proc, 20);
3468: --
3469: -- Check if US payroll installed.
3470: --
3471: l_geocodes_installed := hr_general.chk_geocodes_installed;
3476: --
3477: if p_style = 'US'
3478: and l_geocodes_installed = 'Y'
3479: then
3480: hr_utility.set_location(l_proc, 30);
3481: --
3482: -- Extract the first 5 characters of the zip code
3483: --
3484: l_postal_code := substr(p_postal_code,1,5);
3490: (p_check_column1 => 'PER_ADDRESSES.REGION_2'
3491: ,p_check_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3492: ) then
3493: --
3494: hr_utility.set_location(l_proc, 40);
3495: --
3496: -- no county is given, so procede with validation which excluses it;
3497: -- validate state and city;
3498: --
3499: open csr_val_st_city;
3500: fetch csr_val_st_city into l_state_code;
3501: if(csr_val_st_city%notfound) then
3502: close csr_val_st_city;
3503: hr_utility.set_location(l_proc, 50);
3504: hr_utility.set_message(800, 'PER_52531_ADD_INV_STCI_COMB');
3505: hr_multi_message.add
3506: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3507: ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3500: fetch csr_val_st_city into l_state_code;
3501: if(csr_val_st_city%notfound) then
3502: close csr_val_st_city;
3503: hr_utility.set_location(l_proc, 50);
3504: hr_utility.set_message(800, 'PER_52531_ADD_INV_STCI_COMB');
3505: hr_multi_message.add
3506: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3507: ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3508: );
3507: ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3508: );
3509: else
3510: close csr_val_st_city;
3511: hr_utility.set_location(l_proc, 60);
3512: --
3513: -- check for a valid state, city, zip combination
3514: --
3515: if hr_multi_message.no_exclusive_error
3519: open csr_valid_zip_code_no_ncty;
3520: fetch csr_valid_zip_code_no_ncty into l_exists;
3521: if csr_valid_zip_code_no_ncty%notfound then
3522: close csr_valid_zip_code_no_ncty;
3523: hr_utility.set_location(l_proc, 70);
3524: hr_utility.set_message(800, 'PER_52532_ADD_INV_STCIZ_COMB');
3525: hr_multi_message.add
3526: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3527: ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3520: fetch csr_valid_zip_code_no_ncty into l_exists;
3521: if csr_valid_zip_code_no_ncty%notfound then
3522: close csr_valid_zip_code_no_ncty;
3523: hr_utility.set_location(l_proc, 70);
3524: hr_utility.set_message(800, 'PER_52532_ADD_INV_STCIZ_COMB');
3525: hr_multi_message.add
3526: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3527: ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3528: ,p_associated_column3 => 'PER_ADDRESSES.POSTAL_CODE'
3528: ,p_associated_column3 => 'PER_ADDRESSES.POSTAL_CODE'
3529: );
3530: else
3531: close csr_valid_zip_code_no_ncty;
3532: hr_utility.set_location(l_proc, 80);
3533: end if;
3534: --
3535: end if; -- no_exclusive_error check for POSTAL_CODE
3536: --
3548: (p_check_column1 => 'PER_ADDRESSES.REGION_1'
3549: ,p_check_column2 => 'PER_ADDRESSES.REGION_2'
3550: ) then
3551: --
3552: hr_utility.set_location(l_proc, 90);
3553: open csr_valid_state_county;
3554: fetch csr_valid_state_county into l_state_code, l_county_code;
3555: --
3556: if csr_valid_state_county%notfound then
3555: --
3556: if csr_valid_state_county%notfound then
3557: close csr_valid_state_county;
3558: --
3559: hr_utility.set_location(l_proc, 100);
3560: hr_utility.set_message(800, 'PER_52988_ADD_INV_STCOU_COMB');
3561: hr_multi_message.add
3562: (p_associated_column1 => 'PER_ADDRESSES.REGION_1'
3563: ,p_associated_column2 => 'PER_ADDRESSES.REGION_2'
3556: if csr_valid_state_county%notfound then
3557: close csr_valid_state_county;
3558: --
3559: hr_utility.set_location(l_proc, 100);
3560: hr_utility.set_message(800, 'PER_52988_ADD_INV_STCOU_COMB');
3561: hr_multi_message.add
3562: (p_associated_column1 => 'PER_ADDRESSES.REGION_1'
3563: ,p_associated_column2 => 'PER_ADDRESSES.REGION_2'
3564: );
3563: ,p_associated_column2 => 'PER_ADDRESSES.REGION_2'
3564: );
3565: else
3566: close csr_valid_state_county;
3567: hr_utility.set_location(l_proc, 110);
3568: end if;
3569: --
3570: -- Validate the state, county and city combination
3571: --
3577: --
3578: if csr_val_st_county_city%notfound then
3579: close csr_val_st_county_city;
3580: --
3581: hr_utility.set_location(l_proc, 120);
3582: hr_utility.set_message(800, 'PER_52987_ADD_INV_STCOCY_COMB');
3583: hr_multi_message.add
3584: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3585: ,p_associated_column2 => 'PER_ADDRESSES.REGION_1'
3578: if csr_val_st_county_city%notfound then
3579: close csr_val_st_county_city;
3580: --
3581: hr_utility.set_location(l_proc, 120);
3582: hr_utility.set_message(800, 'PER_52987_ADD_INV_STCOCY_COMB');
3583: hr_multi_message.add
3584: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3585: ,p_associated_column2 => 'PER_ADDRESSES.REGION_1'
3586: ,p_associated_column3 => 'PER_ADDRESSES.TOWN_OR_CITY'
3586: ,p_associated_column3 => 'PER_ADDRESSES.TOWN_OR_CITY'
3587: );
3588: else
3589: close csr_val_st_county_city;
3590: hr_utility.set_location(l_proc, 130);
3591: end if;
3592: --
3593: -- Validate the state, county, city and zip code combination
3594: --
3600: fetch csr_valid_zip_code into l_exists;
3601: if csr_valid_zip_code%notfound then
3602: close csr_valid_zip_code;
3603: --
3604: hr_utility.set_location(l_proc, 140);
3605: hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
3606: hr_multi_message.add
3607: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3608: ,p_associated_column2 => 'PER_ADDRESSES.REGION_1'
3601: if csr_valid_zip_code%notfound then
3602: close csr_valid_zip_code;
3603: --
3604: hr_utility.set_location(l_proc, 140);
3605: hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
3606: hr_multi_message.add
3607: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3608: ,p_associated_column2 => 'PER_ADDRESSES.REGION_1'
3609: ,p_associated_column3 => 'PER_ADDRESSES.TOWN_OR_CITY'
3610: ,p_associated_column4 => 'PER_ADDRESSES.POSTAL_CODE'
3611: );
3612: else
3613: close csr_valid_zip_code;
3614: hr_utility.set_location(l_proc, 150);
3615: end if;
3616: --
3617: end if; -- no_exclusive_error check for postal_code
3618: --
3624: --
3625: end if; -- end if for p_style = 'US'
3626: --
3627: end if; -- end if for api_updating check
3628: hr_utility.set_location(' Leaving:'|| l_proc, 160);
3629: --
3630: end chk_city_state_zip_comb;
3631: --
3632: --
3746: if p_tax_city is not null and
3747: p_tax_state is not null and
3748: p_tax_zip is not null
3749: then
3750: hr_utility.set_location('Entering:'|| l_proc, 10);
3751: --
3752: -- Only proceed with validation if :
3753: -- a) US address style and payroll is installed under US legislation and
3754: -- b) The current g_old_rec is current and
3769: (nvl(per_add_shd.g_old_rec.add_information18, hr_api.g_varchar2) <>
3770: nvl(p_tax_city, hr_api.g_varchar2))) or
3771: (NOT l_api_updating)) then
3772: --
3773: hr_utility.set_location(l_proc, 20);
3774: --
3775: -- Check if US payroll installed.
3776: --
3777: l_geocodes_installed := hr_general.chk_geocodes_installed;
3781: -- city, state and county.
3782: --
3783: If l_geocodes_installed = 'Y'
3784: then
3785: hr_utility.set_location(l_proc, 30);
3786: --
3787: -- Extract the first 5 characters of the zip code
3788: --
3789: l_postal_code := substr(p_tax_zip,1,5);
3793: if hr_multi_message.no_exclusive_error
3794: (p_check_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3795: ,p_check_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3796: ) then
3797: hr_utility.set_location(l_proc, 40);
3798: --
3799: -- no county is given, so procede with validation which excluses it;
3800: -- validate state and city;
3801: --
3802: open csr_val_st_city;
3803: fetch csr_val_st_city into l_state_code;
3804: if(csr_val_st_city%notfound) then
3805: close csr_val_st_city;
3806: hr_utility.set_location(l_proc, 50);
3807: hr_utility.set_message(800, 'PER_52531_ADD_INV_STCI_COMB');
3808: hr_multi_message.add
3809: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3810: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3803: fetch csr_val_st_city into l_state_code;
3804: if(csr_val_st_city%notfound) then
3805: close csr_val_st_city;
3806: hr_utility.set_location(l_proc, 50);
3807: hr_utility.set_message(800, 'PER_52531_ADD_INV_STCI_COMB');
3808: hr_multi_message.add
3809: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3810: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3811: );
3810: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3811: );
3812: else
3813: close csr_val_st_city;
3814: hr_utility.set_location(l_proc, 60);
3815: --
3816: -- check for a valid state, city, zip combination
3817: --
3818: if hr_multi_message.no_exclusive_error
3821: open csr_valid_zip_code_no_ncty;
3822: fetch csr_valid_zip_code_no_ncty into l_exists;
3823: if csr_valid_zip_code_no_ncty%notfound then
3824: close csr_valid_zip_code_no_ncty;
3825: hr_utility.set_location(l_proc, 70);
3826: hr_utility.set_message(800, 'PER_52532_ADD_INV_STCIZ_COMB');
3827: hr_multi_message.add
3828: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3829: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3822: fetch csr_valid_zip_code_no_ncty into l_exists;
3823: if csr_valid_zip_code_no_ncty%notfound then
3824: close csr_valid_zip_code_no_ncty;
3825: hr_utility.set_location(l_proc, 70);
3826: hr_utility.set_message(800, 'PER_52532_ADD_INV_STCIZ_COMB');
3827: hr_multi_message.add
3828: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3829: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3830: ,p_associated_column3 => 'PER_ADDRESSES.ADD_INFORMATION20'
3831: );
3832: else
3833: --
3834: close csr_valid_zip_code_no_ncty;
3835: hr_utility.set_location(l_proc, 80);
3836: --
3837: end if;
3838: --
3839: end if; -- no_exclusive_error for ADD_INFORMATION20(p_tax_zip)
3852: (p_check_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3853: ,p_check_column2 => 'PER_ADDRESSES.ADD_INFORMATION19'
3854: ) then
3855: --
3856: hr_utility.set_location(l_proc, 90);
3857: open csr_valid_state_county;
3858: fetch csr_valid_state_county into l_state_code, l_county_code;
3859: --
3860: if csr_valid_state_county%notfound then
3859: --
3860: if csr_valid_state_county%notfound then
3861: close csr_valid_state_county;
3862: --
3863: hr_utility.set_location(l_proc, 100);
3864: hr_utility.set_message(800, 'PER_52988_ADD_INV_STCOU_COMB');
3865: hr_multi_message.add
3866: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3867: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION19'
3860: if csr_valid_state_county%notfound then
3861: close csr_valid_state_county;
3862: --
3863: hr_utility.set_location(l_proc, 100);
3864: hr_utility.set_message(800, 'PER_52988_ADD_INV_STCOU_COMB');
3865: hr_multi_message.add
3866: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3867: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION19'
3868: );
3869: --
3870: else
3871: --
3872: close csr_valid_state_county;
3873: hr_utility.set_location(l_proc, 110);
3874: --
3875: end if;
3876: --
3877: -- Validate the state, county and city combination
3884: --
3885: if csr_val_st_county_city%notfound then
3886: close csr_val_st_county_city;
3887: --
3888: hr_utility.set_location(l_proc, 120);
3889: hr_utility.set_message(800, 'PER_52987_ADD_INV_STCOCY_COMB');
3890: hr_multi_message.add
3891: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3892: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3885: if csr_val_st_county_city%notfound then
3886: close csr_val_st_county_city;
3887: --
3888: hr_utility.set_location(l_proc, 120);
3889: hr_utility.set_message(800, 'PER_52987_ADD_INV_STCOCY_COMB');
3890: hr_multi_message.add
3891: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3892: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3893: ,p_associated_column3 => 'PER_ADDRESSES.ADD_INFORMATION19'
3895: --
3896: else
3897: --
3898: close csr_val_st_county_city;
3899: hr_utility.set_location(l_proc, 130);
3900: --
3901: end if;
3902: --
3903: -- Validate the state, county, city and zip code combination
3909: fetch csr_valid_zip_code into l_exists;
3910: if csr_valid_zip_code%notfound then
3911: close csr_valid_zip_code;
3912: --
3913: hr_utility.set_location(l_proc, 140);
3914: hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
3915: hr_multi_message.add
3916: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3917: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3910: if csr_valid_zip_code%notfound then
3911: close csr_valid_zip_code;
3912: --
3913: hr_utility.set_location(l_proc, 140);
3914: hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
3915: hr_multi_message.add
3916: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3917: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3918: ,p_associated_column3 => 'PER_ADDRESSES.ADD_INFORMATION19'
3921: --
3922: else
3923: --
3924: close csr_valid_zip_code;
3925: hr_utility.set_location(l_proc, 150);
3926: --
3927: end if;
3928: --
3929: end if; -- no_exclusive_error for ADD_INFORMATION20(p_tax_zip)
3937: end if;
3938: --
3939: end if;
3940: end if;
3941: hr_utility.set_location(' Leaving:'|| l_proc, 160);
3942: --
3943: end chk_tax_city_state_zip_comb;
3944: --
3945: -- ---------------------------------------------------------------------------
4002: and pa2.primary_flag = 'Y');
4003: --
4004: begin
4005: l_date := nvl(per_add_shd.g_old_rec.date_to, hr_api.g_eot);
4006: hr_utility.set_location('Entering:'|| l_proc, 1);
4007: --
4008: -- For primary addresses only
4009: -- ==========================
4010: --
4019: open csr_del_address;
4020: fetch csr_del_address into l_exists;
4021: if csr_del_address%found then
4022: close csr_del_address;
4023: hr_utility.set_message(801, 'HR_7308_ADD_PRIMARY_DEL');
4024: hr_utility.raise_error;
4025: end if;
4026: close csr_del_address;
4027: hr_utility.set_location(l_proc, 2);
4020: fetch csr_del_address into l_exists;
4021: if csr_del_address%found then
4022: close csr_del_address;
4023: hr_utility.set_message(801, 'HR_7308_ADD_PRIMARY_DEL');
4024: hr_utility.raise_error;
4025: end if;
4026: close csr_del_address;
4027: hr_utility.set_location(l_proc, 2);
4028: --
4023: hr_utility.set_message(801, 'HR_7308_ADD_PRIMARY_DEL');
4024: hr_utility.raise_error;
4025: end if;
4026: close csr_del_address;
4027: hr_utility.set_location(l_proc, 2);
4028: --
4029: -- Check that the deletion of a primary
4030: -- address does not break the contiguous
4031: -- nature of the address
4033: open csr_no_del_contig_add;
4034: fetch csr_no_del_contig_add into l_exists;
4035: if csr_no_del_contig_add%found then
4036: close csr_no_del_contig_add;
4037: hr_utility.set_message(801, 'HR_51030_ADDR_PRIM_GAP');
4038: hr_utility.raise_error;
4039: end if;
4040: close csr_no_del_contig_add;
4041: end if;
4034: fetch csr_no_del_contig_add into l_exists;
4035: if csr_no_del_contig_add%found then
4036: close csr_no_del_contig_add;
4037: hr_utility.set_message(801, 'HR_51030_ADDR_PRIM_GAP');
4038: hr_utility.raise_error;
4039: end if;
4040: close csr_no_del_contig_add;
4041: end if;
4042: --
4039: end if;
4040: close csr_no_del_contig_add;
4041: end if;
4042: --
4043: hr_utility.set_location(' Leaving:'|| l_proc, 3);
4044: hr_utility.set_location(' Leaving:'||l_proc,5);
4045: end chk_del_address;
4046: --
4047: -- ----------------------------------------------------------------------------
4040: close csr_no_del_contig_add;
4041: end if;
4042: --
4043: hr_utility.set_location(' Leaving:'|| l_proc, 3);
4044: hr_utility.set_location(' Leaving:'||l_proc,5);
4045: end chk_del_address;
4046: --
4047: -- ----------------------------------------------------------------------------
4048: -- |----------------------< check_non_updateable_args >-----------------------|
4075: --
4076: l_proc varchar2(72) := g_package||'check_non_updateable_args';
4077: --
4078: Begin
4079: hr_utility.set_location('Entering:'||l_proc, 5);
4080: --
4081: -- Only proceed with validation if a row exists for
4082: -- the current record in the HR Schema
4083: --
4083: --
4084: if not per_add_shd.api_updating
4085: (p_address_id => p_rec.address_id,
4086: p_object_version_number => p_rec.object_version_number) then
4087: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4088: hr_utility.set_message_token('PROCEDURE', l_proc);
4089: hr_utility.set_message_token('STEP', '5');
4090: end if;
4091: --
4084: if not per_add_shd.api_updating
4085: (p_address_id => p_rec.address_id,
4086: p_object_version_number => p_rec.object_version_number) then
4087: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4088: hr_utility.set_message_token('PROCEDURE', l_proc);
4089: hr_utility.set_message_token('STEP', '5');
4090: end if;
4091: --
4092: hr_utility.set_location(l_proc, 6);
4085: (p_address_id => p_rec.address_id,
4086: p_object_version_number => p_rec.object_version_number) then
4087: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4088: hr_utility.set_message_token('PROCEDURE', l_proc);
4089: hr_utility.set_message_token('STEP', '5');
4090: end if;
4091: --
4092: hr_utility.set_location(l_proc, 6);
4093: --
4088: hr_utility.set_message_token('PROCEDURE', l_proc);
4089: hr_utility.set_message_token('STEP', '5');
4090: end if;
4091: --
4092: hr_utility.set_location(l_proc, 6);
4093: --
4094: -- start of commenting the code for business_group_id and person_id
4095: -- are updateable if currently null
4096: /*
4101: ,p_argument => 'BUSINESS_GROUP_ID'
4102: ,p_base_table => per_add_shd.g_tab_nam
4103: );
4104: end if;
4105: hr_utility.set_location(l_proc, 7);
4106: --
4107: if nvl(p_rec.person_id, hr_api.g_number) <>
4108: per_add_shd.g_old_rec.person_id then
4109: hr_api.argument_changed_error
4114: end if;
4115: */
4116: -- end of commenting code
4117: --
4118: hr_utility.set_location(l_proc, 8);
4119: --
4120: if not g_called_from_form then
4121: if nvl(p_rec.primary_flag, hr_api.g_varchar2) <>
4122: per_add_shd.g_old_rec.primary_flag then
4126: ,p_base_table => per_add_shd.g_tab_nam
4127: );
4128: end if;
4129: end if;
4130: hr_utility.set_location(l_proc, 11);
4131: --
4132: hr_utility.set_location(' Leaving:'||l_proc, 12);
4133: end check_non_updateable_args;
4134: --
4128: end if;
4129: end if;
4130: hr_utility.set_location(l_proc, 11);
4131: --
4132: hr_utility.set_location(' Leaving:'||l_proc, 12);
4133: end check_non_updateable_args;
4134: --
4135: -- -----------------------------------------------------------------------------
4136: -- |--------------------------------< chk_df >---------------------------------|
4141: --
4142: l_proc varchar2(72) := g_package||'chk_df';
4143: --
4144: Begin
4145: hr_utility.set_location('Entering:'||l_proc, 5);
4146: --
4147: -- Check if the row is being inserted or updated and a
4148: -- value has changed
4149: --
4242: );
4243: --
4244: end if;
4245: --
4246: hr_utility.set_location(' Leaving:'||l_proc, 10);
4247: end chk_df;
4248: --
4249: -- -----------------------------------------------------------------------------
4250: -- |-------------------------------< chk_ddf >---------------------------------|
4256: l_proc varchar2(72) := g_package||'chk_ddf';
4257: l_error exception;
4258: --
4259: Begin
4260: hr_utility.set_location('Entering:'||l_proc, 5);
4261: --
4262: -- Check if the row is being inserted or updated and a
4263: -- value has changed
4264: --
4357: );
4358: --
4359: end if;
4360: --
4361: hr_utility.set_location(' Leaving:'||l_proc, 10);
4362: end chk_ddf;
4363: --
4364: -- ---------------------------------------------------------------------------
4365: -- |----------------------< df_update_validate >---------------------------|
4395: --
4396: l_proc varchar2(72) := g_package||'df_update_validate';
4397: --
4398: begin
4399: hr_utility.set_location('Entering:'||l_proc, 10);
4400: --
4401: if nvl(per_add_shd.g_old_rec.addr_attribute_category, hr_api.g_varchar2) <>
4402: nvl(p_rec.addr_attribute_category, hr_api.g_varchar2) or
4403: nvl(per_add_shd.g_old_rec.addr_attribute1, hr_api.g_varchar2) <>
4463: -- so we must call the flex stub
4464: per_add_flex.df(p_rec => p_rec);
4465: end if;
4466: --
4467: hr_utility.set_location(' Leaving:'||l_proc, 10);
4468: end df_update_validate;
4469: --
4470: -- ----------------------------------------------------------------------------
4471: -- |---------------------------< insert_validate >----------------------------|
4478: --
4479: l_proc varchar2(72) := g_package||'insert_validate';
4480: --
4481: Begin
4482: hr_utility.set_location('Entering:'||l_proc, 10);
4483: --
4484: -- Validate Important Attributes
4485: --
4486: -- Reset global variable that indicates if payroll is installed under
4501: p_business_group_id => p_rec.business_group_id
4502: ,p_associated_column1 => per_add_shd.g_tab_nam ||
4503: '.BUSINESS_GROUP_ID'
4504: );
4505: hr_utility.set_location(l_proc, 20);
4506: --
4507: -- After validating the set of important attributes,
4508: -- if Mulitple message detection is enabled and at least
4509: -- one error has been found then abort further validation.
4522: ,p_date_to => p_rec.date_to
4523: ,p_object_version_number => p_rec.object_version_number
4524: );
4525: --
4526: hr_utility.set_location(l_proc, 30);
4527: -- HR/TCA merge
4528: --
4529: -- Validate business_group_id
4530: --
4533: ,p_object_version_number => p_rec.object_version_number
4534: ,p_person_id => p_rec.person_id
4535: ,p_business_group_id => p_rec.business_group_id
4536: );
4537: hr_utility.set_location(l_proc, 35);
4538: --
4539: if p_rec.person_id is not null then
4540: --
4541: -- Validate person_id
4545: ,p_object_version_number => p_rec.object_version_number
4546: ,p_person_id => p_rec.person_id
4547: ,p_business_group_id => p_rec.business_group_id
4548: );
4549: hr_utility.set_location(l_proc, 40);
4550: end if;
4551: --
4552: -- Validate party_id
4553: --
4553: --
4554: chk_party_id
4555: (p_rec
4556: );
4557: hr_utility.set_location(l_proc, 45);
4558: --
4559: -- Validate primary flag
4560: --
4561: chk_primary_flag
4562: (p_address_id => p_rec.address_id
4563: ,p_object_version_number => p_rec.object_version_number
4564: ,p_primary_flag => p_rec.primary_flag
4565: );
4566: hr_utility.set_location(l_proc, 50);
4567: --
4568: if not g_called_from_form then
4569: --
4570: -- Validate date/address_type combination
4581: ,p_party_id => p_rec.party_id -- HR/TCA merge
4582: );
4583: end if;
4584: --
4585: hr_utility.set_location(l_proc, 60);
4586: --
4587: -- Validate style
4588: --
4589: chk_style
4588: --
4589: chk_style
4590: (p_style => p_rec.style
4591: );
4592: hr_utility.set_location(l_proc, 70);
4593: --
4594: -- Validate address type
4595: --
4596: chk_address_type
4599: ,p_date_from => p_rec.date_from
4600: ,p_effective_date => p_effective_date
4601: ,p_object_version_number => p_rec.object_version_number
4602: );
4603: hr_utility.set_location(l_proc, 80);
4604: --
4605: -- Validate country
4606: --
4607: chk_country
4609: ,p_style => p_rec.style
4610: ,p_address_id => p_rec.address_id
4611: ,p_object_version_number => p_rec.object_version_number
4612: );
4613: hr_utility.set_location(l_proc, 90);
4614: --
4615: -- Validate postal code.
4616: --
4617: chk_postal_code
4634: ,p_business_group_id => p_rec.business_group_id
4635: ,p_object_version_number => p_rec.object_version_number
4636: );
4637: End if;
4638: hr_utility.set_location(l_proc, 100);
4639: --
4640: -- Validation specific to GB, US, GENERIC
4641: --
4642: if p_rec.style = 'GB' or
4645: then
4646: --
4647: -- Check null attributes for address style.
4648: --
4649: hr_utility.set_location(l_proc, 110);
4650: chk_style_null_attr
4651: (p_address_id => p_rec.address_id
4652: ,p_object_version_number => p_rec.object_version_number
4653: ,p_style => p_rec.style
4654: ,p_region_2 => p_rec.region_2
4655: ,p_region_3 => p_rec.region_3
4656: ,p_telephone_number_3 => p_rec.telephone_number_3
4657: );
4658: hr_utility.set_location(l_proc, 120);
4659: --
4660: -- Validate address_line1
4661: --
4662: chk_address_line1
4664: ,p_style => p_rec.style
4665: ,p_address_line1 => p_rec.address_line1
4666: ,p_object_version_number => p_rec.object_version_number
4667: );
4668: hr_utility.set_location(l_proc, 130);
4669: --
4670: -- Validate date_to
4671: --
4672: -- No procedural call is made to the procedure
4698: ,p_effective_date => p_effective_date
4699: ,p_object_version_number => p_rec.object_version_number
4700: );
4701: End if;
4702: hr_utility.set_location(l_proc, 140);
4703: --
4704: -- Validate region 2.
4705: --
4706: chk_region_2
4710: ,p_business_group_id => p_rec.business_group_id
4711: ,p_effective_date => p_effective_date
4712: ,p_object_version_number => p_rec.object_version_number
4713: );
4714: hr_utility.set_location(l_proc, 150);
4715: --
4716: -- Validate tax_state(add_information17)
4717: --
4718: If p_rec.style = 'US' and
4725: ,p_business_group_id => p_rec.business_group_id
4726: ,p_effective_date => p_effective_date
4727: ,p_object_version_number => p_rec.object_version_number
4728: );
4729: hr_utility.set_location(l_proc, 155);
4730: End if;
4731: --
4732: -- Validate town or city.
4733: --
4737: ,p_town_or_city => p_rec.town_or_city
4738: ,p_business_group_id => p_rec.business_group_id
4739: ,p_object_version_number => p_rec.object_version_number
4740: );
4741: hr_utility.set_location(l_proc, 160);
4742: --
4743: -- Validate tax_city (add_information18).
4744: --
4745: If p_rec.style = 'US' and
4750: ,p_tax_city => p_rec.add_information18
4751: ,p_business_group_id => p_rec.business_group_id
4752: ,p_object_version_number => p_rec.object_version_number
4753: );
4754: hr_utility.set_location(l_proc, 165);
4755: End if;
4756: --
4757: --
4758: -- This is only applicable if payroll is installed under US legislation
4769: ,p_town_or_city => p_rec.town_or_city
4770: ,p_business_group_id => p_rec.business_group_id
4771: ,p_object_version_number => p_rec.object_version_number
4772: );
4773: hr_utility.set_location(l_proc, 170);
4774: --
4775: chk_tax_city_state_zip_comb
4776: (p_address_id => p_rec.address_id
4777: ,p_style => p_rec.style
4781: ,p_tax_city => p_rec.add_information18
4782: ,p_business_group_id => p_rec.business_group_id
4783: ,p_object_version_number => p_rec.object_version_number
4784: );
4785: hr_utility.set_location(l_proc, 175);
4786: --Validation specific to JP
4787: --
4788: /* Bug 1677965
4789: elsif p_rec.style = 'JP' then
4786: --Validation specific to JP
4787: --
4788: /* Bug 1677965
4789: elsif p_rec.style = 'JP' then
4790: hr_utility.set_location(l_proc, 21);
4791: --
4792: -- Check the combination checking for town_or_city(district_code)
4793: -- address_line1, and region_1
4794: --
4799: ,p_town_or_city => p_rec.town_or_city
4800: ,p_address_line1 => p_rec.address_line1
4801: ,p_region_1 => p_rec.region_1
4802: );
4803: hr_utility.set_location(l_proc, 180);
4804: --
4805: -- Validate region_2 according to address_line2
4806: --
4807: chk_address2_region2_comb
4809: ,p_object_version_number => p_rec.object_version_number
4810: ,p_address_line2 => p_rec.address_line2
4811: ,p_region_2 => p_rec.region_2
4812: );
4813: hr_utility.set_location(l_proc, 190);
4814: --
4815: -- Validate region_3 according to address_line3
4816: --
4817: chk_address3_region3_comb
4819: ,p_object_version_number => p_rec.object_version_number
4820: ,p_address_line3 => p_rec.address_line3
4821: ,p_region_3 => p_rec.region_3
4822: );
4823: hr_utility.set_location(l_proc, 200);
4824: */
4825: --
4826: -- DDF Validation other than GB, US, JP and GENERIC
4827: --
4825: --
4826: -- DDF Validation other than GB, US, JP and GENERIC
4827: --
4828: else
4829: hr_utility.set_location(l_proc, 205);
4830: --
4831: -- Validate the DDF
4832: --
4833: chk_ddf
4840: --
4841: chk_df
4842: (p_rec => p_rec
4843: );
4844: hr_utility.set_location(' Leaving:'||l_proc, 210);
4845: --
4846: End insert_validate;
4847: --
4848: -- ----------------------------------------------------------------------------
4857: --
4858: l_proc varchar2(72) := g_package||'update_validate';
4859: --
4860: Begin
4861: hr_utility.set_location('Entering:'||l_proc, 10);
4862: --
4863: -- Validate Important Attributes
4864: --
4865: -- Reset global variable that indicates if payroll is installed under
4873: -- Check that the columns which cannot
4874: -- be updated have not changed
4875: --
4876: check_non_updateable_args(p_rec => p_rec);
4877: hr_utility.set_location(l_proc, 15);
4878: --
4879: -- if person_id isn't specified,business_group_id isn't required.
4880: -- HR/TCA merge
4881: if p_rec.person_id is not null then
4886: p_business_group_id => p_rec.business_group_id
4887: ,p_associated_column1 => per_add_shd.g_tab_nam ||
4888: '.BUSINESS_GROUP_ID'
4889: );
4890: hr_utility.set_location(l_proc, 20);
4891: --
4892: -- After validating the set of important attributes,
4893: -- if Mulitple message detection is enabled and at least
4894: -- one error has been found then abort further validation.
4906: ,p_object_version_number => p_rec.object_version_number
4907: ,p_person_id => p_rec.person_id
4908: ,p_business_group_id => p_rec.business_group_id
4909: );
4910: hr_utility.set_location(l_proc, 22);
4911: --
4912: if p_rec.person_id is not null then -- If condition added for 2762677
4913: --
4914: -- Validate person_id
4918: ,p_object_version_number => p_rec.object_version_number
4919: ,p_person_id => p_rec.person_id
4920: ,p_business_group_id => p_rec.business_group_id
4921: );
4922: hr_utility.set_location(l_proc, 25);
4923: end if;
4924: --
4925: -- Validate Dependent Attributes
4926: --
4931: ,p_date_from => p_rec.date_from
4932: ,p_date_to => p_rec.date_to
4933: ,p_object_version_number => p_rec.object_version_number
4934: );
4935: hr_utility.set_location(l_proc, 30);
4936: --
4937: -- Validate country.
4938: --
4939: chk_country
4941: ,p_style => p_rec.style
4942: ,p_address_id => p_rec.address_id
4943: ,p_object_version_number => p_rec.object_version_number
4944: );
4945: hr_utility.set_location(l_proc, 40);
4946: --
4947: -- Validate address type.
4948: --
4949: chk_address_type
4952: ,p_date_from => p_rec.date_from
4953: ,p_effective_date => p_effective_date
4954: ,p_object_version_number => p_rec.object_version_number
4955: );
4956: hr_utility.set_location(l_proc, 50);
4957: --
4958: -- Validate primary flag
4959: --
4960: chk_primary_flag
4961: (p_address_id => p_rec.address_id
4962: ,p_object_version_number => p_rec.object_version_number
4963: ,p_primary_flag => p_rec.primary_flag
4964: );
4965: hr_utility.set_location(l_proc, 50);
4966: --
4967: --
4968: --
4969: if not g_called_from_form then
4983: ,p_party_id => p_rec.party_id -- HR/TCA merge
4984: );
4985: end if;
4986: --
4987: hr_utility.set_location(l_proc, 60);
4988: --
4989: -- Validate postal code.
4990: --
4991: chk_postal_code
4995: ,p_business_group_id => p_rec.business_group_id
4996: ,p_object_version_number => p_rec.object_version_number
4997: ,p_town_or_city => p_rec.town_or_city
4998: );
4999: hr_utility.set_location(l_proc, 70);
5000: --
5001: -- Validate tax address zip.
5002: --
5003: If p_rec.style = 'US' and
5008: ,p_tax_address_zip => p_rec.add_information20
5009: ,p_business_group_id => p_rec.business_group_id
5010: ,p_object_version_number => p_rec.object_version_number
5011: );
5012: hr_utility.set_location(l_proc, 71);
5013: End if;
5014: --
5015: -- Validate date to.
5016: --
5019: ,p_date_from => p_rec.date_from
5020: ,p_date_to => p_rec.date_to
5021: ,p_object_version_number => p_rec.object_version_number
5022: );
5023: hr_utility.set_location(l_proc, 80);
5024: --
5025: --
5026: -- Validation specific to GB, US, GENERIC
5027: --
5040: ,p_effective_date => p_effective_date
5041: ,p_object_version_number => p_rec.object_version_number
5042: ,p_validate_county => p_validate_county
5043: );
5044: hr_utility.set_location(l_proc, 90);
5045: If p_rec.style = 'US' and
5046: p_rec.primary_flag = 'Y' then
5047: chk_tax_county
5048: (p_address_id => p_rec.address_id
5051: ,p_business_group_id => p_rec.business_group_id
5052: ,p_effective_date => p_effective_date
5053: ,p_object_version_number => p_rec.object_version_number
5054: );
5055: hr_utility.set_location(l_proc, 91);
5056: End if;
5057: --
5058: -- Validate region 2.
5059: --
5064: ,p_business_group_id => p_rec.business_group_id
5065: ,p_effective_date => p_effective_date
5066: ,p_object_version_number => p_rec.object_version_number
5067: );
5068: hr_utility.set_location(l_proc, 100);
5069: --
5070: -- Validate tax_state(add_information17).
5071: --
5072: If p_rec.style = 'US' and
5078: ,p_business_group_id => p_rec.business_group_id
5079: ,p_effective_date => p_effective_date
5080: ,p_object_version_number => p_rec.object_version_number
5081: );
5082: hr_utility.set_location(l_proc, 105);
5083: End if;
5084: --
5085: -- Validate town or city.
5086: --
5090: ,p_town_or_city => p_rec.town_or_city
5091: ,p_business_group_id => p_rec.business_group_id
5092: ,p_object_version_number => p_rec.object_version_number
5093: );
5094: hr_utility.set_location(l_proc, 110);
5095: --
5096: -- Validate tax_city.
5097: --
5098: If p_rec.style = 'US' and
5103: ,p_tax_city => p_rec.add_information18
5104: ,p_business_group_id => p_rec.business_group_id
5105: ,p_object_version_number => p_rec.object_version_number
5106: );
5107: hr_utility.set_location(l_proc, 115);
5108: End if;
5109: --
5110: -- Validate address_line 1.
5111: --
5114: ,p_style => p_rec.style
5115: ,p_address_line1 => p_rec.address_line1
5116: ,p_object_version_number => p_rec.object_version_number
5117: );
5118: hr_utility.set_location(l_proc, 120);
5119: --
5120: -- Check null attributes for address style.
5121: --
5122: chk_style_null_attr
5126: ,p_region_2 => p_rec.region_2
5127: ,p_region_3 => p_rec.region_3
5128: ,p_telephone_number_3 => p_rec.telephone_number_3
5129: );
5130: hr_utility.set_location(l_proc, 130);
5131: --
5132: -- This is only applicable if payroll is installed under US legislation
5133: -- and address style is 'US'.
5134: -- Validate city(town_or_city) and state(region_2)
5143: ,p_town_or_city => p_rec.town_or_city
5144: ,p_business_group_id => p_rec.business_group_id
5145: ,p_object_version_number => p_rec.object_version_number
5146: );
5147: hr_utility.set_location(l_proc, 140);
5148: --
5149: If p_rec.style = 'US' and
5150: p_rec.primary_flag = 'Y' then
5151: chk_tax_city_state_zip_comb
5157: ,p_tax_city => p_rec.add_information18
5158: ,p_business_group_id => p_rec.business_group_id
5159: ,p_object_version_number => p_rec.object_version_number
5160: );
5161: hr_utility.set_location(l_proc, 145);
5162: End if;
5163: /* Bug 1677965
5164: elsif p_rec.style = 'JP' then
5165: hr_utility.set_location(l_proc, 150);
5161: hr_utility.set_location(l_proc, 145);
5162: End if;
5163: /* Bug 1677965
5164: elsif p_rec.style = 'JP' then
5165: hr_utility.set_location(l_proc, 150);
5166: --
5167: -- Check the combination checking for town_or_city(district_code)
5168: -- address_line1, and region_1
5169: --
5174: ,p_town_or_city => p_rec.town_or_city
5175: ,p_address_line1 => p_rec.address_line1
5176: ,p_region_1 => p_rec.region_1
5177: );
5178: hr_utility.set_location(l_proc, 160);
5179: --
5180: -- Validate region_2 according to address_line2
5181: --
5182: chk_address2_region2_comb
5184: ,p_object_version_number => p_rec.object_version_number
5185: ,p_address_line2 => p_rec.address_line2
5186: ,p_region_2 => p_rec.region_2
5187: );
5188: hr_utility.set_location(l_proc, 170);
5189: --
5190: -- Validate region_3 according to address_line3
5191: --
5192: chk_address3_region3_comb
5194: ,p_object_version_number => p_rec.object_version_number
5195: ,p_address_line3 => p_rec.address_line3
5196: ,p_region_3 => p_rec.region_3
5197: );
5198: hr_utility.set_location(l_proc, 180);
5199: */
5200: --
5201: -- DDF Validation other than GB, US, JP and GENERIC
5202: --
5200: --
5201: -- DDF Validation other than GB, US, JP and GENERIC
5202: --
5203: else
5204: hr_utility.set_location(l_proc, 185);
5205: --
5206: -- Validate the DDF
5207: --
5208: chk_ddf
5216: chk_df
5217: (p_rec => p_rec
5218: );
5219: --
5220: hr_utility.set_location(' Leaving:'||l_proc, 190);
5221: End update_validate;
5222: --
5223: -- ----------------------------------------------------------------------------
5224: -- |---------------------------< delete_validate >----------------------------|
5227: --
5228: l_proc varchar2(72) := g_package||'delete_validate';
5229: --
5230: Begin
5231: hr_utility.set_location('Entering:'||l_proc, 5);
5232: --
5233: -- Call all supporting business operations. Mapping to the
5234: -- appropriate business rules on peradd.bru is provided
5235: --
5238: -- as the primary address
5239: --
5240: chk_del_address;
5241: --
5242: hr_utility.set_location(' Leaving:'||l_proc, 10);
5243: End delete_validate;
5244: --
5245: -- ---------------------------------------------------------------------------
5246: -- |---------------------< return_legislation_code >-------------------------|
5264: --
5265: l_legislation_code varchar2(150);
5266: l_proc varchar2(72) := g_package||'return_legislation_code';
5267: begin
5268: hr_utility.set_location('Entering:'|| l_proc, 10);
5269: --
5270: -- Ensure that all the mandatory parameter are not null
5271: --
5272: hr_api.mandatory_arg_error(p_api_name => l_proc,
5279: -- call to this function. Just return the value in the global
5280: -- variable.
5281: --
5282: l_legislation_code := g_legislation_code;
5283: hr_utility.set_location(l_proc, 20);
5284: else
5285: --
5286: -- The ID is different to the last call to this function
5287: -- or this is the first call to this function.
5298: return null;
5299: --
5300: -- The primary key is invalid therefore we must error
5301: --
5302: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5303: hr_utility.set_location(l_proc, 30);
5304: hr_utility.raise_error;
5305: end if;
5306: --
5299: --
5300: -- The primary key is invalid therefore we must error
5301: --
5302: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5303: hr_utility.set_location(l_proc, 30);
5304: hr_utility.raise_error;
5305: end if;
5306: --
5307: close csr_leg_code;
5300: -- The primary key is invalid therefore we must error
5301: --
5302: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5303: hr_utility.set_location(l_proc, 30);
5304: hr_utility.raise_error;
5305: end if;
5306: --
5307: close csr_leg_code;
5308: --
5307: close csr_leg_code;
5308: --
5309: g_address_id:= p_address_id;
5310: g_legislation_code := l_legislation_code;
5311: hr_utility.set_location(' Leaving:'|| l_proc, 40);
5312: end if;
5313: return l_legislation_code;
5314: end return_legislation_code;
5315: --