[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: -- ---------------------------------------------------------------------------
1043: or (p_address_id is not null
1044: and p_address_id <> pa.address_id));
1045: --
1046: begin
1047: hr_utility.set_location('Entering:'|| l_proc, 10);
1048: --
1049: -- Check mandatory parameters have been set
1050: --
1051: hr_api.mandatory_arg_error
1066: (p_api_name => l_proc
1067: ,p_argument => 'primary_flag'
1068: ,p_argument_value => p_primary_flag
1069: );
1070: hr_utility.set_location(l_proc, 20);
1071: if hr_multi_message.no_all_inclusive_error
1072: (p_check_column1 => 'PER_ADDRESSES.DATE_FROM'
1073: ,p_check_column2 => 'PER_ADDRESSES.DATE_TO'
1074: ,p_check_column3 => 'PER_ADDRESSES.PERSON_ID'
1096: nvl(p_address_type, hr_api.g_varchar2))
1097: or
1098: (NOT l_api_updating))
1099: then
1100: hr_utility.set_location(l_proc, 30);
1101: --
1102: -- For all addresses
1103: -- =================
1104: -- Checks that the date_from, date_to values for a given address
1113: open csr_dup_add_type_exists;
1114: fetch csr_dup_add_type_exists into l_exists;
1115: if csr_dup_add_type_exists%found then
1116: close csr_dup_add_type_exists;
1117: hr_utility.set_message(801, 'HR_51139_ADD_TYPE_ALR_EXIST');
1118: hr_multi_message.add
1119: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1120: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1121: ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1129: end if;
1130: end if;
1131: end if;
1132: --
1133: hr_utility.set_location(l_proc, 4);
1134: --
1135: -- For primary addresses only
1136: -- ==========================
1137: --
1149: open csr_dup_prim_flag;
1150: fetch csr_dup_prim_flag into l_exists;
1151: if csr_dup_prim_flag%found then
1152: close csr_dup_prim_flag;
1153: hr_utility.set_message(801, 'HR_7327_ADD_PRIMARY_ADD_EXISTS');
1154: hr_multi_message.add
1155: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1156: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1157: ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1215: and l_recs_before
1216: and l_recs_after)
1217: or
1218: (l_good_recs < 1)) then
1219: hr_utility.set_message(801, 'HR_51030_ADDR_PRIM_GAP');
1220: hr_multi_message.add
1221: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1222: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1223: ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1242: open csr_invalid_non_prim;
1243: fetch csr_invalid_non_prim into l_exists;
1244: if csr_invalid_non_prim%found then
1245: close csr_invalid_non_prim;
1246: hr_utility.set_message(801, 'HR_7302_ADD_PRIMARY_DATES');
1247: hr_multi_message.add
1248: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1249: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1250: ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1271: open csr_no_primary;
1272: fetch csr_no_primary into l_exists;
1273: if csr_no_primary%notfound then
1274: close csr_no_primary;
1275: hr_utility.set_message(801, 'HR_7302_ADD_PRIMARY_DATES');
1276: hr_multi_message.add
1277: (p_associated_column1 => 'PER_ADDRESSES.DATE_FROM'
1278: ,p_associated_column2 => 'PER_ADDRESSES.DATE_TO'
1279: ,p_associated_column3 => 'PER_ADDRESSES.PERSON_ID'
1317: and address_id <> p_address_id
1318: and l_action_eff_date between date_from
1319: and nvl(date_to,l_action_eff_date);
1320: if l_address_count = 0 then
1321: hr_utility.set_message(800, 'PER_PAYROLL_EXISTS');
1322: hr_multi_message.add();
1323: end if;
1324: end if;
1325: end loop;
1327: end loop;
1328: end if;
1329: end if;
1330: --
1331: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1332: end chk_date_comb;
1333: --
1334: -- ---------------------------------------------------------------------------
1335: -- |-----------------------------< chk_style >------------------------------|
1383: -- 70.2 change c end.
1384: --
1385: --
1386: begin
1387: hr_utility.set_location('Entering:'|| l_proc, 1);
1388: --
1389: -- Check mandatory parameters have been set
1390: --
1391: hr_api.mandatory_arg_error
1392: (p_api_name => l_proc
1393: ,p_argument => 'style'
1394: ,p_argument_value => p_style
1395: );
1396: hr_utility.set_location(l_proc, 2);
1397: --
1398: -- Checks that the flex structure for the style
1399: -- selected exists in fnd_descr_flex_contents
1400: --
1401: open csr_valid_flex_struc;
1402: fetch csr_valid_flex_struc into l_exists;
1403: if csr_valid_flex_struc%notfound then
1404: close csr_valid_flex_struc;
1405: hr_utility.set_message(801, 'HR_7304_ADD_NO_FORMAT');
1406: hr_utility.raise_error;
1407: end if;
1408: close csr_valid_flex_struc;
1409: hr_utility.set_location(l_proc, 3);
1402: fetch csr_valid_flex_struc into l_exists;
1403: if csr_valid_flex_struc%notfound then
1404: close csr_valid_flex_struc;
1405: hr_utility.set_message(801, 'HR_7304_ADD_NO_FORMAT');
1406: hr_utility.raise_error;
1407: end if;
1408: close csr_valid_flex_struc;
1409: hr_utility.set_location(l_proc, 3);
1410: --
1405: hr_utility.set_message(801, 'HR_7304_ADD_NO_FORMAT');
1406: hr_utility.raise_error;
1407: end if;
1408: close csr_valid_flex_struc;
1409: hr_utility.set_location(l_proc, 3);
1410: --
1411: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1412: exception
1413: when app_exception.application_exception then
1407: end if;
1408: close csr_valid_flex_struc;
1409: hr_utility.set_location(l_proc, 3);
1410: --
1411: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1412: exception
1413: when app_exception.application_exception then
1414: if hr_multi_message.exception_add
1415: (p_associated_column1 => 'PER_ADDRESSES.STYLE'
1413: when app_exception.application_exception then
1414: if hr_multi_message.exception_add
1415: (p_associated_column1 => 'PER_ADDRESSES.STYLE'
1416: ) then
1417: hr_utility.set_location(' Leaving:'||l_proc,5);
1418: raise;
1419: end if;
1420: -- Call to raise any errors on multi-message list
1421: -- Taking STYLE as an important parameter.
1419: end if;
1420: -- Call to raise any errors on multi-message list
1421: -- Taking STYLE as an important parameter.
1422: hr_multi_message.end_validation_set;
1423: hr_utility.set_location(' Leaving:'||l_proc,6);
1424: --
1425: end chk_style;
1426: --
1427: -- ---------------------------------------------------------------------------
1471: l_api_updating boolean;
1472: l_proc varchar2(72) := g_package||'chk_style_null_attr';
1473: --
1474: begin
1475: hr_utility.set_location('Entering:'|| l_proc, 1);
1476: --
1477: -- Check 'GB' address style
1478: --
1479: if p_style = 'GB' then
1492: l_token := 'telephone_number_3';
1493: raise l_error;
1494: end if;
1495: --
1496: hr_utility.set_location(l_proc, 2);
1497: --
1498: -- Check 'US' address style
1499: --
1500: elsif p_style = 'US' then
1535: raise l_error;
1536: --
1537: end if;
1538: --
1539: hr_utility.set_location(l_proc, 3);
1540: --
1541: -- Check 'GENERIC' address style
1542: --
1543: elsif p_style = 'GENERIC' then
1548: end if;
1549: --
1550: exception
1551: when l_error then
1552: hr_utility.set_message(801, 'HR_7324_ADD_ADD_ATTRIBUTE_NULL');
1553: hr_utility.set_message_token('ARGUMENT', l_token);
1554: hr_multi_message.add(
1555: p_associated_column1 =>
1556: (per_add_shd.g_tab_nam || '.' || upper(l_token))
1549: --
1550: exception
1551: when l_error then
1552: hr_utility.set_message(801, 'HR_7324_ADD_ADD_ATTRIBUTE_NULL');
1553: hr_utility.set_message_token('ARGUMENT', l_token);
1554: hr_multi_message.add(
1555: p_associated_column1 =>
1556: (per_add_shd.g_tab_nam || '.' || upper(l_token))
1557: );
1557: );
1558: when others then
1559: raise;
1560: --
1561: hr_utility.set_location(' Leaving:'|| l_proc, 4);
1562: end chk_style_null_attr;
1563: --
1564: -- ---------------------------------------------------------------------------
1565: -- |------------------------< chk_address_line1 >----------------------------|
1601: l_proc varchar2(72) := g_package||'chk_address_line1';
1602: l_api_updating boolean;
1603:
1604: begin
1605: hr_utility.set_location('Entering:'|| l_proc, 1);
1606: --
1607: -- Check mandatory parameters have been set
1608: --
1609: hr_api.mandatory_arg_error
1623: if ((l_api_updating and
1624: nvl(per_add_shd.g_old_rec.address_line1, hr_api.g_varchar2) <>
1625: nvl(p_address_line1, hr_api.g_varchar2)) or
1626: (NOT l_api_updating)) then
1627: hr_utility.set_location(l_proc, 2);
1628: --
1629: -- Check that value for address_line1 is not null for 'US' and 'GB'
1630: -- style.
1631: --
1631: --
1632: if p_style = 'GB' or
1633: p_style = 'US' then
1634: --
1635: hr_utility.set_location(l_proc, 3);
1636: --
1637: if p_address_line1 is null then
1638: --
1639: hr_utility.set_message(801, 'HR_51233_ADD_ADD_LINE1_REQ');
1635: hr_utility.set_location(l_proc, 3);
1636: --
1637: if p_address_line1 is null then
1638: --
1639: hr_utility.set_message(801, 'HR_51233_ADD_ADD_LINE1_REQ');
1640: hr_utility.raise_error;
1641: end if;
1642: --
1643: end if;
1636: --
1637: if p_address_line1 is null then
1638: --
1639: hr_utility.set_message(801, 'HR_51233_ADD_ADD_LINE1_REQ');
1640: hr_utility.raise_error;
1641: end if;
1642: --
1643: end if;
1644: --
1643: end if;
1644: --
1645: end if;
1646: --
1647: hr_utility.set_location(' Leaving:'|| l_proc, 5);
1648: exception
1649: when app_exception.application_exception then
1650: if hr_multi_message.exception_add
1651: (p_associated_column1 => 'PER_ADDRESSES.ADDRESS_LINE1'
1649: when app_exception.application_exception then
1650: if hr_multi_message.exception_add
1651: (p_associated_column1 => 'PER_ADDRESSES.ADDRESS_LINE1'
1652: ) then
1653: hr_utility.set_location(' Leaving:'||l_proc,6);
1654: raise;
1655: end if;
1656: hr_utility.set_location(' Leaving:'||l_proc,7);
1657: --
1652: ) then
1653: hr_utility.set_location(' Leaving:'||l_proc,6);
1654: raise;
1655: end if;
1656: hr_utility.set_location(' Leaving:'||l_proc,7);
1657: --
1658: end chk_address_line1;
1659: --
1660: -- ----------------------------------------------------------------------------
1689: from per_business_groups pbg
1690: where pbg.business_group_id = p_business_group_id;
1691:
1692: begin
1693: hr_utility.set_location('Entering:'|| l_proc, 1);
1694: -- Bug 885806
1695: -- dbms_output.put_line('Top of dynamic sql . . .');
1696: hr_utility.trace('Top of dynamic sql . . .');
1697: open csr_bg;
1692: begin
1693: hr_utility.set_location('Entering:'|| l_proc, 1);
1694: -- Bug 885806
1695: -- dbms_output.put_line('Top of dynamic sql . . .');
1696: hr_utility.trace('Top of dynamic sql . . .');
1697: open csr_bg;
1698: fetch csr_bg into l_legislation_code;
1699:
1700: -- Only proceed with validation if :
1717: or
1718: (NOT l_api_updating))
1719: ) then
1720:
1721: hr_utility.set_location(l_proc, 2);
1722: --
1723: -- p_town_or_city(district_code) is not null
1724: --
1725: if p_town_or_city is not NULL then
1722: --
1723: -- p_town_or_city(district_code) is not null
1724: --
1725: if p_town_or_city is not NULL then
1726: hr_utility.set_location(l_proc, 3);
1727:
1728: l_dynamic_sql := 'select t.address_line_1, ' ||
1729: ' t.address_line_1_kana ' ||
1730: 'from per_jp_address_lookups t ' ||
1738: -- 3. Bind dynamic sql variables
1739: -- 4. Define dynamic sql columns
1740: -- 5. Execute and fetch dynamic sql
1741: --
1742: Hr_Utility.Set_Location(l_proc, 6);
1743: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1744: --
1745: Hr_Utility.Set_Location(l_proc, 10);
1746: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1741: --
1742: Hr_Utility.Set_Location(l_proc, 6);
1743: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1744: --
1745: Hr_Utility.Set_Location(l_proc, 10);
1746: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1747: --
1748: Hr_Utility.Set_Location(l_proc, 15);
1749: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1744: --
1745: Hr_Utility.Set_Location(l_proc, 10);
1746: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1747: --
1748: Hr_Utility.Set_Location(l_proc, 15);
1749: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1750: ':p_town_or_city', p_town_or_city);
1751:
1752: Hr_Utility.Set_Location(l_proc, 20);
1748: Hr_Utility.Set_Location(l_proc, 15);
1749: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1750: ':p_town_or_city', p_town_or_city);
1751:
1752: Hr_Utility.Set_Location(l_proc, 20);
1753: dbms_sql.define_column(l_sql_cursor, 1, l_address_line1, 60); -- Step 4
1754: dbms_sql.define_column(l_sql_cursor, 2, l_region_1, 70);
1755: --
1756: Hr_Utility.Set_Location(l_proc, 30);
1752: Hr_Utility.Set_Location(l_proc, 20);
1753: dbms_sql.define_column(l_sql_cursor, 1, l_address_line1, 60); -- Step 4
1754: dbms_sql.define_column(l_sql_cursor, 2, l_region_1, 70);
1755: --
1756: Hr_Utility.Set_Location(l_proc, 30);
1757: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1758:
1759: if l_rows = 0 then
1760: dbms_sql.close_cursor(l_sql_cursor);
1757: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1758:
1759: if l_rows = 0 then
1760: dbms_sql.close_cursor(l_sql_cursor);
1761: hr_utility.set_message(801, 'HR_72028_ADD_INVALID_DIST_CODE');
1762: hr_utility.raise_error;
1763:
1764: elsif l_rows = 1 then
1765: Hr_Utility.Set_Location(l_proc, 35);
1758:
1759: if l_rows = 0 then
1760: dbms_sql.close_cursor(l_sql_cursor);
1761: hr_utility.set_message(801, 'HR_72028_ADD_INVALID_DIST_CODE');
1762: hr_utility.raise_error;
1763:
1764: elsif l_rows = 1 then
1765: Hr_Utility.Set_Location(l_proc, 35);
1766: dbms_sql.column_value(l_sql_cursor, 1, l_address_line1);
1761: hr_utility.set_message(801, 'HR_72028_ADD_INVALID_DIST_CODE');
1762: hr_utility.raise_error;
1763:
1764: elsif l_rows = 1 then
1765: Hr_Utility.Set_Location(l_proc, 35);
1766: dbms_sql.column_value(l_sql_cursor, 1, l_address_line1);
1767: if p_address_line1 is not null and
1768: p_address_line1 <> l_address_line1 then
1769:
1767: if p_address_line1 is not null and
1768: p_address_line1 <> l_address_line1 then
1769:
1770: dbms_sql.close_cursor(l_sql_cursor);
1771: hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1772: hr_utility.raise_error;
1773: end if;
1774: p_address_line1 := l_address_line1;
1775:
1768: p_address_line1 <> l_address_line1 then
1769:
1770: dbms_sql.close_cursor(l_sql_cursor);
1771: hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1772: hr_utility.raise_error;
1773: end if;
1774: p_address_line1 := l_address_line1;
1775:
1776: else
1774: p_address_line1 := l_address_line1;
1775:
1776: else
1777: dbms_sql.close_cursor(l_sql_cursor);
1778: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1779: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1780: hr_utility.raise_error;
1781: end if;
1782:
1775:
1776: else
1777: dbms_sql.close_cursor(l_sql_cursor);
1778: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1779: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1780: hr_utility.raise_error;
1781: end if;
1782:
1783: elsif p_address_line1 is not NULL then
1776: else
1777: dbms_sql.close_cursor(l_sql_cursor);
1778: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1779: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1780: hr_utility.raise_error;
1781: end if;
1782:
1783: elsif p_address_line1 is not NULL then
1784: hr_utility.set_location(l_proc, 4);
1780: hr_utility.raise_error;
1781: end if;
1782:
1783: elsif p_address_line1 is not NULL then
1784: hr_utility.set_location(l_proc, 4);
1785:
1786: l_dynamic_sql :=
1787: 'select t.district_code,' ||
1788: ' t.address_line_1_kana ' ||
1797: -- 3. Bind dynamic sql variables
1798: -- 4. Define dynamic sql columns
1799: -- 5. Execute and fetch dynamic sql
1800: --
1801: Hr_Utility.Set_Location(l_proc, 6);
1802: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1803: --
1804: Hr_Utility.Set_Location(l_proc, 10);
1805: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1800: --
1801: Hr_Utility.Set_Location(l_proc, 6);
1802: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
1803: --
1804: Hr_Utility.Set_Location(l_proc, 10);
1805: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1806: --
1807: Hr_Utility.Set_Location(l_proc, 15);
1808: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1803: --
1804: Hr_Utility.Set_Location(l_proc, 10);
1805: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.v7); -- Step 2
1806: --
1807: Hr_Utility.Set_Location(l_proc, 15);
1808: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1809: ':p_address_line1', p_address_line1);
1810:
1811: Hr_Utility.Set_Location(l_proc, 20);
1807: Hr_Utility.Set_Location(l_proc, 15);
1808: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
1809: ':p_address_line1', p_address_line1);
1810:
1811: Hr_Utility.Set_Location(l_proc, 20);
1812: dbms_sql.define_column(l_sql_cursor, 1, l_town_or_city, 50); -- Step 4
1813: dbms_sql.define_column(l_sql_cursor, 2, l_region_1, 70);
1814: --
1815: Hr_Utility.Set_Location(l_proc, 30);
1811: Hr_Utility.Set_Location(l_proc, 20);
1812: dbms_sql.define_column(l_sql_cursor, 1, l_town_or_city, 50); -- Step 4
1813: dbms_sql.define_column(l_sql_cursor, 2, l_region_1, 70);
1814: --
1815: Hr_Utility.Set_Location(l_proc, 30);
1816: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1817:
1818: if l_rows = 0 then
1819: dbms_sql.close_cursor(l_sql_cursor);
1816: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
1817:
1818: if l_rows = 0 then
1819: dbms_sql.close_cursor(l_sql_cursor);
1820: hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1821: hr_utility.raise_error;
1822:
1823: elsif l_rows = 1 then
1824: Hr_Utility.Set_Location(l_proc, 35);
1817:
1818: if l_rows = 0 then
1819: dbms_sql.close_cursor(l_sql_cursor);
1820: hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1821: hr_utility.raise_error;
1822:
1823: elsif l_rows = 1 then
1824: Hr_Utility.Set_Location(l_proc, 35);
1825: dbms_sql.column_value(l_sql_cursor, 1, l_town_or_city);
1820: hr_utility.set_message(801, 'HR_72029_ADD_INVALID_LINE1');
1821: hr_utility.raise_error;
1822:
1823: elsif l_rows = 1 then
1824: Hr_Utility.Set_Location(l_proc, 35);
1825: dbms_sql.column_value(l_sql_cursor, 1, l_town_or_city);
1826: p_town_or_city := l_town_or_city;
1827:
1828: else
1826: p_town_or_city := l_town_or_city;
1827:
1828: else
1829: dbms_sql.close_cursor(l_sql_cursor);
1830: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1831: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1832: hr_utility.raise_error;
1833: end if;
1834: --
1827:
1828: else
1829: dbms_sql.close_cursor(l_sql_cursor);
1830: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1831: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1832: hr_utility.raise_error;
1833: end if;
1834: --
1835: --Both p_region_1 and p_address_line1 are null
1828: else
1829: dbms_sql.close_cursor(l_sql_cursor);
1830: hr_utility.set_message(801, 'HR_72030_ADD_OVERRAP_ROWS');
1831: hr_utility.set_message_token('TABLE_NAME', 'per_jp_address_lookups');
1832: hr_utility.raise_error;
1833: end if;
1834: --
1835: --Both p_region_1 and p_address_line1 are null
1836: --
1834: --
1835: --Both p_region_1 and p_address_line1 are null
1836: --
1837: else
1838: hr_utility.set_message(801, 'HR_72031_ADD_DIST_LINE1_NULL');
1839: hr_utility.raise_error;
1840: end if;
1841:
1842:
1835: --Both p_region_1 and p_address_line1 are null
1836: --
1837: else
1838: hr_utility.set_message(801, 'HR_72031_ADD_DIST_LINE1_NULL');
1839: hr_utility.raise_error;
1840: end if;
1841:
1842:
1843: dbms_sql.column_value(l_sql_cursor, 2, l_region_1);
1843: dbms_sql.column_value(l_sql_cursor, 2, l_region_1);
1844: if p_region_1 is not null and
1845: p_region_1 <> l_region_1 then
1846: dbms_sql.close_cursor(l_sql_cursor);
1847: hr_utility.set_message(801, 'HR_72032_ADD_INVALID_KANA1');
1848: hr_utility.raise_error;
1849: end if;
1850:
1851: p_region_1 := l_region_1;
1844: if p_region_1 is not null and
1845: p_region_1 <> l_region_1 then
1846: dbms_sql.close_cursor(l_sql_cursor);
1847: hr_utility.set_message(801, 'HR_72032_ADD_INVALID_KANA1');
1848: hr_utility.raise_error;
1849: end if;
1850:
1851: p_region_1 := l_region_1;
1852: dbms_sql.close_cursor(l_sql_cursor);
1850:
1851: p_region_1 := l_region_1;
1852: dbms_sql.close_cursor(l_sql_cursor);
1853: end if;
1854: hr_utility.set_location('Leaving:'|| l_proc, 5);
1855: -- Bug 885806
1856: -- dbms_output.put_line('Bottom of dynamic sql . . .');
1857: hr_utility.trace('Bottom of dynamic sql . . .');
1858: end chk_address1_towncity_comb;
1853: end if;
1854: hr_utility.set_location('Leaving:'|| l_proc, 5);
1855: -- Bug 885806
1856: -- dbms_output.put_line('Bottom of dynamic sql . . .');
1857: hr_utility.trace('Bottom of dynamic sql . . .');
1858: end chk_address1_towncity_comb;
1859: */
1860: --
1861: -- ----------------------------------------------------------------------------
1879: l_rgeflg varchar2(10);
1880: l_region_2 per_addresses.region_2%type;
1881: --
1882: begin
1883: hr_utility.set_location('Entering:'|| l_proc, 1);
1884:
1885: -- Only proceed with validation if :
1886: -- a) The current g_old_rec is current and
1887: -- b) The value for address_line2, or region_2 have changed
1896: nvl(per_add_shd.g_old_rec.region_2, hr_api.g_varchar2) <>
1897: nvl(p_region_2, hr_api.g_varchar2)
1898: )) or (NOT l_api_updating)) then
1899:
1900: hr_utility.set_location(l_proc, 2);
1901:
1902: if p_address_line2 is NULL and p_region_2 is not NULL then
1903: hr_utility.set_message(801, 'HR_72025_ADD_REGION2_NOT_NULL');
1904: hr_utility.raise_error;
1899:
1900: hr_utility.set_location(l_proc, 2);
1901:
1902: if p_address_line2 is NULL and p_region_2 is not NULL then
1903: hr_utility.set_message(801, 'HR_72025_ADD_REGION2_NOT_NULL');
1904: hr_utility.raise_error;
1905: end if;
1906: l_region_2 := p_region_2;
1907: hr_chkfmt.checkformat(value => l_region_2
1900: hr_utility.set_location(l_proc, 2);
1901:
1902: if p_address_line2 is NULL and p_region_2 is not NULL then
1903: hr_utility.set_message(801, 'HR_72025_ADD_REGION2_NOT_NULL');
1904: hr_utility.raise_error;
1905: end if;
1906: l_region_2 := p_region_2;
1907: hr_chkfmt.checkformat(value => l_region_2
1908: ,format => 'KANA'
1911: ,maximum => NULL
1912: ,nullok => 'Y'
1913: ,rgeflg => l_rgeflg
1914: ,curcode => NULL);
1915: hr_utility.set_location(' Calling hr_chkfmt.checkformat2', 1);
1916: end if;
1917:
1918: hr_utility.set_location('Leaving:'|| l_proc, 3);
1919: end chk_address2_region2_comb;
1914: ,curcode => NULL);
1915: hr_utility.set_location(' Calling hr_chkfmt.checkformat2', 1);
1916: end if;
1917:
1918: hr_utility.set_location('Leaving:'|| l_proc, 3);
1919: end chk_address2_region2_comb;
1920: */
1921: --
1922: -- ----------------------------------------------------------------------------
1940: l_rgeflg varchar2(10);
1941: l_region_3 per_addresses.region_3%type;
1942: --
1943: begin
1944: hr_utility.set_location('Entering:'|| l_proc, 1);
1945:
1946: -- Only proceed with validation if :
1947: -- a) The current g_old_rec is current and
1948: -- b) The value for address_line3, or region_3 have changed
1957: nvl(per_add_shd.g_old_rec.region_3, hr_api.g_varchar2) <>
1958: nvl(p_region_3, hr_api.g_varchar2)
1959: )) or (NOT l_api_updating)) then
1960:
1961: hr_utility.set_location(l_proc, 2);
1962:
1963: if p_address_line3 is NULL and p_region_3 is not NULL then
1964: hr_utility.set_message(801, 'HR_72026_ADD_REGION3_NOT_NULL');
1965: hr_utility.raise_error;
1960:
1961: hr_utility.set_location(l_proc, 2);
1962:
1963: if p_address_line3 is NULL and p_region_3 is not NULL then
1964: hr_utility.set_message(801, 'HR_72026_ADD_REGION3_NOT_NULL');
1965: hr_utility.raise_error;
1966: end if;
1967: l_region_3 := p_region_3;
1968: hr_chkfmt.checkformat(value => l_region_3
1961: hr_utility.set_location(l_proc, 2);
1962:
1963: if p_address_line3 is NULL and p_region_3 is not NULL then
1964: hr_utility.set_message(801, 'HR_72026_ADD_REGION3_NOT_NULL');
1965: hr_utility.raise_error;
1966: end if;
1967: l_region_3 := p_region_3;
1968: hr_chkfmt.checkformat(value => l_region_3
1969: ,format => 'KANA'
1972: ,maximum => NULL
1973: ,nullok => 'Y'
1974: ,rgeflg => l_rgeflg
1975: ,curcode => NULL);
1976: hr_utility.set_location(' Calling hr_chkfmt.checkformat3', 1);
1977: end if;
1978:
1979: hr_utility.set_location('Leaving:'|| l_proc, 1);
1980: end chk_address3_region3_comb;
1975: ,curcode => NULL);
1976: hr_utility.set_location(' Calling hr_chkfmt.checkformat3', 1);
1977: end if;
1978:
1979: hr_utility.set_location('Leaving:'|| l_proc, 1);
1980: end chk_address3_region3_comb;
1981: */
1982: --
1983: -- ----------------------------------------------------------------------------
1990: l_dynamic_sql varchar2(2000); -- Dynamic sql text
1991: l_rows integer; -- Num of rows returned
1992: l_dummy varchar2(1);
1993: begin
1994: hr_utility.set_location('Entering:'|| l_proc, 10);
1995: if p_postal_code is not null then
1996: --
1997: l_dynamic_sql := 'select null ' ||
1998: 'from per_jp_postal_codes p ' ||
2005: -- 3. Bind dynamic sql variables
2006: -- 4. Define dynamic sql columns
2007: -- 5. Execute and fetch dynamic sql
2008: --
2009: hr_utility.set_location(l_proc, 15);
2010: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
2011: --
2012: hr_utility.set_location(l_proc, 20);
2013: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.native); -- Step 2
2008: --
2009: hr_utility.set_location(l_proc, 15);
2010: l_sql_cursor := dbms_sql.open_cursor; -- Step 1
2011: --
2012: hr_utility.set_location(l_proc, 20);
2013: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.native); -- Step 2
2014: --
2015: hr_utility.set_location(l_proc, 25);
2016: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
2011: --
2012: hr_utility.set_location(l_proc, 20);
2013: dbms_sql.parse(l_sql_cursor, l_dynamic_sql, dbms_sql.native); -- Step 2
2014: --
2015: hr_utility.set_location(l_proc, 25);
2016: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
2017: ':p_postal_code', p_postal_code);
2018:
2019: hr_utility.set_location(l_proc, 30);
2015: hr_utility.set_location(l_proc, 25);
2016: dbms_sql.bind_variable(l_sql_cursor, -- Step 3
2017: ':p_postal_code', p_postal_code);
2018:
2019: hr_utility.set_location(l_proc, 30);
2020: dbms_sql.define_column(l_sql_cursor, 1, l_dummy, 1); -- Step 4
2021: --
2022: hr_utility.set_location(l_proc, 35);
2023: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
2018:
2019: hr_utility.set_location(l_proc, 30);
2020: dbms_sql.define_column(l_sql_cursor, 1, l_dummy, 1); -- Step 4
2021: --
2022: hr_utility.set_location(l_proc, 35);
2023: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
2024:
2025: if l_rows = 0 then
2026: dbms_sql.close_cursor(l_sql_cursor);
2023: l_rows := dbms_sql.execute_and_fetch(l_sql_cursor, false); -- Step 5
2024:
2025: if l_rows = 0 then
2026: dbms_sql.close_cursor(l_sql_cursor);
2027: hr_utility.set_message(801, 'HR_72027_ADD_INVALID_POST_CODE');
2028: hr_utility.raise_error;
2029: end if;
2030: dbms_sql.close_cursor(l_sql_cursor);
2031: end if;
2024:
2025: if l_rows = 0 then
2026: dbms_sql.close_cursor(l_sql_cursor);
2027: hr_utility.set_message(801, 'HR_72027_ADD_INVALID_POST_CODE');
2028: hr_utility.raise_error;
2029: end if;
2030: dbms_sql.close_cursor(l_sql_cursor);
2031: end if;
2032: hr_utility.set_location(' Leaving:'|| l_proc, 100);
2028: hr_utility.raise_error;
2029: end if;
2030: dbms_sql.close_cursor(l_sql_cursor);
2031: end if;
2032: hr_utility.set_location(' Leaving:'|| l_proc, 100);
2033: end chk_jp_postal_code;
2034: */
2035: --
2036: -- ---------------------------------------------------------------------------
2118: l_count number(10);
2119: l_city_address_data get_city_address%ROWTYPE;
2120: --
2121: begin
2122: hr_utility.set_location('Entering:'|| l_proc, 1);
2123: --
2124: -- Check mandatory parameters have been set
2125: --
2126: hr_api.mandatory_arg_error
2141: and nvl(per_add_shd.g_old_rec.postal_code, hr_api.g_varchar2) <>
2142: nvl(p_postal_code, hr_api.g_varchar2)) or
2143: (NOT l_api_updating))
2144: then
2145: hr_utility.set_location(l_proc, 2);
2146: --
2147: -- Check if GEOCODES is installed for a US legislation
2148: --
2149: if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y' then
2151: -- Check that the zip code is set
2152: --
2153: if p_postal_code is null then
2154: --
2155: hr_utility.set_message(800, 'PER_52991_ADD_NO_ZIP_SET');
2156: hr_utility.raise_error;
2157: --
2158: end if;
2159: /*----------------------- Changes start for 5367066 ---------------------*/
2152: --
2153: if p_postal_code is null then
2154: --
2155: hr_utility.set_message(800, 'PER_52991_ADD_NO_ZIP_SET');
2156: hr_utility.raise_error;
2157: --
2158: end if;
2159: /*----------------------- Changes start for 5367066 ---------------------*/
2160:
2158: end if;
2159: /*----------------------- Changes start for 5367066 ---------------------*/
2160:
2161: begin
2162: hr_utility.set_location('Postal code ='||substr(p_postal_code,1,5), 21);
2163: hr_utility.set_location('Town city ='||p_town_or_city, 22);
2164: open get_city_address;
2165: loop
2166: fetch get_city_address into l_city_address_data;
2159: /*----------------------- Changes start for 5367066 ---------------------*/
2160:
2161: begin
2162: hr_utility.set_location('Postal code ='||substr(p_postal_code,1,5), 21);
2163: hr_utility.set_location('Town city ='||p_town_or_city, 22);
2164: open get_city_address;
2165: loop
2166: fetch get_city_address into l_city_address_data;
2167: l_count := get_city_address%ROWCOUNT;
2164: open get_city_address;
2165: loop
2166: fetch get_city_address into l_city_address_data;
2167: l_count := get_city_address%ROWCOUNT;
2168: hr_utility.set_location('NO of rows returned from cursor = '||l_count, 23);
2169: exit when get_city_address%NOTFOUND;
2170: if (substr(p_postal_code,1,5) between l_city_address_data.zip_start and l_city_address_data.zip_end) then
2171: null;
2172: else
2169: exit when get_city_address%NOTFOUND;
2170: if (substr(p_postal_code,1,5) between l_city_address_data.zip_start and l_city_address_data.zip_end) then
2171: null;
2172: else
2173: hr_utility.set_message(800, 'HR_7786_ADDR_US_ZIP_OOR');
2174: hr_utility.set_message_token('ZIP_START',l_city_address_data.zip_start);
2175: hr_utility.set_message_token('ZIP_END',l_city_address_data.zip_end);
2176: hr_utility.raise_error;
2177: end if;
2170: if (substr(p_postal_code,1,5) between l_city_address_data.zip_start and l_city_address_data.zip_end) then
2171: null;
2172: else
2173: hr_utility.set_message(800, 'HR_7786_ADDR_US_ZIP_OOR');
2174: hr_utility.set_message_token('ZIP_START',l_city_address_data.zip_start);
2175: hr_utility.set_message_token('ZIP_END',l_city_address_data.zip_end);
2176: hr_utility.raise_error;
2177: end if;
2178: end loop;
2171: null;
2172: else
2173: hr_utility.set_message(800, 'HR_7786_ADDR_US_ZIP_OOR');
2174: hr_utility.set_message_token('ZIP_START',l_city_address_data.zip_start);
2175: hr_utility.set_message_token('ZIP_END',l_city_address_data.zip_end);
2176: hr_utility.raise_error;
2177: end if;
2178: end loop;
2179: --
2172: else
2173: hr_utility.set_message(800, 'HR_7786_ADDR_US_ZIP_OOR');
2174: hr_utility.set_message_token('ZIP_START',l_city_address_data.zip_start);
2175: hr_utility.set_message_token('ZIP_END',l_city_address_data.zip_end);
2176: hr_utility.raise_error;
2177: end if;
2178: end loop;
2179: --
2180: close get_city_address;
2178: end loop;
2179: --
2180: close get_city_address;
2181: if l_count = 0 then
2182: hr_utility.set_message(800, 'HR_51195_ADD_INVALID_ZIP_CODE');
2183: hr_utility.raise_error;
2184: end if;
2185: hr_utility.set_location('Leaveing the Cursor =', 24);
2186: end;
2179: --
2180: close get_city_address;
2181: if l_count = 0 then
2182: hr_utility.set_message(800, 'HR_51195_ADD_INVALID_ZIP_CODE');
2183: hr_utility.raise_error;
2184: end if;
2185: hr_utility.set_location('Leaveing the Cursor =', 24);
2186: end;
2187:
2181: if l_count = 0 then
2182: hr_utility.set_message(800, 'HR_51195_ADD_INVALID_ZIP_CODE');
2183: hr_utility.raise_error;
2184: end if;
2185: hr_utility.set_location('Leaveing the Cursor =', 24);
2186: end;
2187:
2188: begin
2189: --
2187:
2188: begin
2189: --
2190: if length(p_postal_code) = 5 then
2191: hr_utility.set_location(l_proc, 4);
2192: --
2193: -- Check if zip code is all numbers
2194: --
2195: for i in 1..5 loop
2194: --
2195: for i in 1..5 loop
2196: if(substr(p_postal_code,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: --
2195: for i in 1..5 loop
2196: if(substr(p_postal_code,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: --
2203: elsif length(p_postal_code) = 10 then
2200: end if;
2201: end loop;
2202: --
2203: elsif length(p_postal_code) = 10 then
2204: hr_utility.set_location(l_proc, 5);
2205: --
2206: -- Parse zip code to validate for correct format.
2207: --
2208: l_postal_code_1 := substr(p_postal_code,1,5);
2213: --
2214: for i in 1..5 loop
2215: if(substr(l_postal_code_1,i,1)
2216: not between '0' and '9') then
2217: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2218: hr_utility.raise_error;
2219: end if;
2220: end loop;
2221: hr_utility.set_location(l_proc, 6);
2214: for i in 1..5 loop
2215: if(substr(l_postal_code_1,i,1)
2216: not between '0' and '9') then
2217: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2218: hr_utility.raise_error;
2219: end if;
2220: end loop;
2221: hr_utility.set_location(l_proc, 6);
2222: --
2217: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2218: hr_utility.raise_error;
2219: end if;
2220: end loop;
2221: hr_utility.set_location(l_proc, 6);
2222: --
2223: -- Validate last 4 characters are numbers
2224: --
2225: for i in 1..4 loop
2224: --
2225: for i in 1..4 loop
2226: if(substr(l_postal_code_3,i,1)
2227: not between '0' and '9') then
2228: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2229: hr_utility.raise_error;
2230: end if;
2231: end loop;
2232: hr_utility.set_location(l_proc, 7);
2225: for i in 1..4 loop
2226: if(substr(l_postal_code_3,i,1)
2227: not between '0' and '9') then
2228: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2229: hr_utility.raise_error;
2230: end if;
2231: end loop;
2232: hr_utility.set_location(l_proc, 7);
2233: --
2228: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2229: hr_utility.raise_error;
2230: end if;
2231: end loop;
2232: hr_utility.set_location(l_proc, 7);
2233: --
2234: -- Validate last sixth characters is '-'
2235: --
2236: if l_postal_code_2 <> '-' then
2233: --
2234: -- Validate last sixth characters is '-'
2235: --
2236: if l_postal_code_2 <> '-' then
2237: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2238: hr_utility.raise_error;
2239: end if;
2240: else
2241: --
2234: -- Validate last sixth characters is '-'
2235: --
2236: if l_postal_code_2 <> '-' then
2237: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2238: hr_utility.raise_error;
2239: end if;
2240: else
2241: --
2242: -- If zip code is not 5 or 10 character long
2240: else
2241: --
2242: -- If zip code is not 5 or 10 character long
2243: --
2244: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2245: hr_utility.raise_error;
2246: end if;
2247: --
2248: -- If an invalid zip code character generates an
2241: --
2242: -- If zip code is not 5 or 10 character long
2243: --
2244: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2245: hr_utility.raise_error;
2246: end if;
2247: --
2248: -- If an invalid zip code character generates an
2249: -- exception
2249: -- exception
2250: --
2251: exception
2252: when others then
2253: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2254: hr_utility.raise_error;
2255: end;
2256:
2257: /*----------------------- Changes End for 5367066 ---------------------*/
2250: --
2251: exception
2252: when others then
2253: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2254: hr_utility.raise_error;
2255: end;
2256:
2257: /*----------------------- Changes End for 5367066 ---------------------*/
2258:
2265: -- 8 characters long
2266: --
2267: if p_style = 'GB' then
2268: if length(p_postal_code) > 8 then
2269: hr_utility.set_message(801, 'HR_7306_ADD_POST_CODE');
2270: hr_utility.raise_error;
2271: end if;
2272: --
2273: -- Check that the US postal code is either 5 or 10 character
2266: --
2267: if p_style = 'GB' then
2268: if length(p_postal_code) > 8 then
2269: hr_utility.set_message(801, 'HR_7306_ADD_POST_CODE');
2270: hr_utility.raise_error;
2271: end if;
2272: --
2273: -- Check that the US postal code is either 5 or 10 character
2274: --
2272: --
2273: -- Check that the US postal code is either 5 or 10 character
2274: --
2275: elsif p_style = 'US' and l_geocodes_installed = 'Y' then
2276: hr_utility.set_location(l_proc, 3);
2277: --
2278: begin
2279: --
2280: if length(p_postal_code) = 5 then
2277: --
2278: begin
2279: --
2280: if length(p_postal_code) = 5 then
2281: hr_utility.set_location(l_proc, 4);
2282: --
2283: -- Check if zip code is all numbers
2284: --
2285: for i in 1..5 loop
2284: --
2285: for i in 1..5 loop
2286: if(substr(p_postal_code,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: --
2285: for i in 1..5 loop
2286: if(substr(p_postal_code,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: --
2293: elsif length(p_postal_code) = 10 then
2290: end if;
2291: end loop;
2292: --
2293: elsif length(p_postal_code) = 10 then
2294: hr_utility.set_location(l_proc, 5);
2295: --
2296: -- Parse zip code to validate for correct format.
2297: --
2298: l_postal_code_1 := substr(p_postal_code,1,5);
2303: --
2304: for i in 1..5 loop
2305: if(substr(l_postal_code_1,i,1)
2306: not between '0' and '9') then
2307: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2308: hr_utility.raise_error;
2309: end if;
2310: end loop;
2311: hr_utility.set_location(l_proc, 6);
2304: for i in 1..5 loop
2305: if(substr(l_postal_code_1,i,1)
2306: not between '0' and '9') then
2307: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2308: hr_utility.raise_error;
2309: end if;
2310: end loop;
2311: hr_utility.set_location(l_proc, 6);
2312: --
2307: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2308: hr_utility.raise_error;
2309: end if;
2310: end loop;
2311: hr_utility.set_location(l_proc, 6);
2312: --
2313: -- Validate last 4 characters are numbers
2314: --
2315: for i in 1..4 loop
2314: --
2315: for i in 1..4 loop
2316: if(substr(l_postal_code_3,i,1)
2317: not between '0' and '9') then
2318: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2319: hr_utility.raise_error;
2320: end if;
2321: end loop;
2322: hr_utility.set_location(l_proc, 7);
2315: for i in 1..4 loop
2316: if(substr(l_postal_code_3,i,1)
2317: not between '0' and '9') then
2318: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2319: hr_utility.raise_error;
2320: end if;
2321: end loop;
2322: hr_utility.set_location(l_proc, 7);
2323: --
2318: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2319: hr_utility.raise_error;
2320: end if;
2321: end loop;
2322: hr_utility.set_location(l_proc, 7);
2323: --
2324: -- Validate last sixth characters is '-'
2325: --
2326: if l_postal_code_2 <> '-' then
2323: --
2324: -- Validate last sixth characters is '-'
2325: --
2326: if l_postal_code_2 <> '-' then
2327: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2328: hr_utility.raise_error;
2329: end if;
2330:
2331: else
2324: -- Validate last sixth characters is '-'
2325: --
2326: if l_postal_code_2 <> '-' then
2327: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2328: hr_utility.raise_error;
2329: end if;
2330:
2331: else
2332: --
2331: else
2332: --
2333: -- If zip code is not 5 or 10 character long
2334: --
2335: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2336: hr_utility.raise_error;
2337: end if;
2338: --
2339: -- If an invalid zip code character generates an
2332: --
2333: -- If zip code is not 5 or 10 character long
2334: --
2335: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2336: hr_utility.raise_error;
2337: end if;
2338: --
2339: -- If an invalid zip code character generates an
2340: -- exception
2340: -- exception
2341: --
2342: exception
2343: when others then
2344: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2345: hr_utility.raise_error;
2346: end;
2347: /* Bug 1677965
2348: elsif p_style = 'JP' then
2341: --
2342: exception
2343: when others then
2344: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2345: hr_utility.raise_error;
2346: end;
2347: /* Bug 1677965
2348: elsif p_style = 'JP' then
2349: hr_utility.set_location(l_proc, 8);
2345: hr_utility.raise_error;
2346: end;
2347: /* Bug 1677965
2348: elsif p_style = 'JP' then
2349: hr_utility.set_location(l_proc, 8);
2350: chk_jp_postal_code(p_postal_code);
2351: */
2352: end if;
2353: --
2356: end if;
2357: --
2358: end if;
2359: --
2360: hr_utility.set_location(' Leaving:'|| l_proc, 9);
2361: exception
2362: when app_exception.application_exception then
2363: if hr_multi_message.exception_add
2364: (p_associated_column1 => 'PER_ADDRESSES.POSTAL_CODE'
2362: when app_exception.application_exception then
2363: if hr_multi_message.exception_add
2364: (p_associated_column1 => 'PER_ADDRESSES.POSTAL_CODE'
2365: ) then
2366: hr_utility.set_location(' Leaving:'||l_proc,10);
2367: raise;
2368: end if;
2369: hr_utility.set_location(' Leaving:'||l_proc,11);
2370: --
2365: ) then
2366: hr_utility.set_location(' Leaving:'||l_proc,10);
2367: raise;
2368: end if;
2369: hr_utility.set_location(' Leaving:'||l_proc,11);
2370: --
2371: end chk_postal_code;
2372: --
2373: -- ---------------------------------------------------------------------------
2423: --
2424: begin
2425: if p_tax_address_zip is not null
2426: then
2427: hr_utility.set_location('Entering:'|| l_proc, 1);
2428: --
2429: -- Check mandatory parameters have been set
2430: --
2431: hr_api.mandatory_arg_error
2446: and nvl(per_add_shd.g_old_rec.add_information20, hr_api.g_varchar2) <>
2447: nvl(p_tax_address_zip, hr_api.g_varchar2)) or
2448: (NOT l_api_updating))
2449: then
2450: hr_utility.set_location(l_proc, 2);
2451: --
2452: -- Check if GEOCODES is installed for a US legislation
2453: --
2454: if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y'
2458: --
2459: begin
2460: --
2461: if length(p_tax_address_zip) = 5 then
2462: hr_utility.set_location(l_proc, 3);
2463: --
2464: -- Check if zip code is all numbers
2465: --
2466: for i in 1..5 loop
2465: --
2466: for i in 1..5 loop
2467: if(substr(p_tax_address_zip,i,1)
2468: not between '0' and '9') then
2469: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2470: hr_utility.raise_error;
2471: end if;
2472: end loop;
2473: --
2466: for i in 1..5 loop
2467: if(substr(p_tax_address_zip,i,1)
2468: not between '0' and '9') then
2469: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2470: hr_utility.raise_error;
2471: end if;
2472: end loop;
2473: --
2474: elsif length(p_tax_address_zip) = 10 then
2471: end if;
2472: end loop;
2473: --
2474: elsif length(p_tax_address_zip) = 10 then
2475: hr_utility.set_location(l_proc, 4);
2476: --
2477: -- Parse zip code to validate for correct format.
2478: --
2479: l_tax_address_zip_1 := substr(p_tax_address_zip,1,5);
2485: --
2486: for i in 1..5 loop
2487: if(substr(l_tax_address_zip_1,i,1)
2488: not between '0' and '9') then
2489: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2490: hr_utility.raise_error;
2491: end if;
2492: end loop;
2493: hr_utility.set_location(l_proc, 5);
2486: for i in 1..5 loop
2487: if(substr(l_tax_address_zip_1,i,1)
2488: not between '0' and '9') then
2489: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2490: hr_utility.raise_error;
2491: end if;
2492: end loop;
2493: hr_utility.set_location(l_proc, 5);
2494: --
2489: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2490: hr_utility.raise_error;
2491: end if;
2492: end loop;
2493: hr_utility.set_location(l_proc, 5);
2494: --
2495: -- Validate last 4 characters are numbers
2496: --
2497: for i in 1..4 loop
2496: --
2497: for i in 1..4 loop
2498: if(substr(l_tax_address_zip_3,i,1)
2499: not between '0' and '9') then
2500: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2501: hr_utility.raise_error;
2502: end if;
2503: end loop;
2504: hr_utility.set_location(l_proc, 6);
2497: for i in 1..4 loop
2498: if(substr(l_tax_address_zip_3,i,1)
2499: not between '0' and '9') then
2500: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2501: hr_utility.raise_error;
2502: end if;
2503: end loop;
2504: hr_utility.set_location(l_proc, 6);
2505: --
2500: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2501: hr_utility.raise_error;
2502: end if;
2503: end loop;
2504: hr_utility.set_location(l_proc, 6);
2505: --
2506: -- Validate last sixth characters is '-'
2507: --
2508: if l_tax_address_zip_2 <> '-' then
2505: --
2506: -- Validate last sixth characters is '-'
2507: --
2508: if l_tax_address_zip_2 <> '-' then
2509: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2510: hr_utility.raise_error;
2511: end if;
2512: else
2513: --
2506: -- Validate last sixth characters is '-'
2507: --
2508: if l_tax_address_zip_2 <> '-' then
2509: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2510: hr_utility.raise_error;
2511: end if;
2512: else
2513: --
2514: -- If zip code is not 5 or 10 character long
2512: else
2513: --
2514: -- If zip code is not 5 or 10 character long
2515: --
2516: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2517: hr_utility.raise_error;
2518: end if;
2519: --
2520: -- If an invalid zip code character generates an
2513: --
2514: -- If zip code is not 5 or 10 character long
2515: --
2516: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2517: hr_utility.raise_error;
2518: end if;
2519: --
2520: -- If an invalid zip code character generates an
2521: -- exception
2521: -- exception
2522: --
2523: exception
2524: when others then
2525: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2526: hr_utility.raise_error;
2527: end;
2528: end if;
2529: --
2522: --
2523: exception
2524: when others then
2525: hr_utility.set_message(801, 'HR_51195_ADD_INVALID_ZIP_CODE');
2526: hr_utility.raise_error;
2527: end;
2528: end if;
2529: --
2530: end if;
2529: --
2530: end if;
2531: --
2532: end if;
2533: hr_utility.set_location(' Leaving:'|| l_proc, 7);
2534: exception
2535: when app_exception.application_exception then
2536: if hr_multi_message.exception_add
2537: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION20'
2535: when app_exception.application_exception then
2536: if hr_multi_message.exception_add
2537: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION20'
2538: ) then
2539: hr_utility.set_location(' Leaving:'||l_proc,6);
2540: raise;
2541: end if;
2542: hr_utility.set_location(' Leaving:'||l_proc,7);
2543: --
2538: ) then
2539: hr_utility.set_location(' Leaving:'||l_proc,6);
2540: raise;
2541: end if;
2542: hr_utility.set_location(' Leaving:'||l_proc,7);
2543: --
2544: end chk_tax_address_zip;
2545: --
2546: -- ---------------------------------------------------------------------------
2601: from pay_us_counties
2602: where county_name = p_region_1;
2603: --
2604: begin
2605: hr_utility.set_location('Entering:'|| l_proc, 1);
2606: --
2607: -- Check mandatory parameters have been set
2608: --
2609: hr_api.mandatory_arg_error
2630: nvl(per_add_shd.g_old_rec.region_1, hr_api.g_varchar2) <>
2631: nvl(p_region_1, hr_api.g_varchar2)) or
2632: (NOT l_api_updating))
2633: then
2634: hr_utility.set_location(l_proc, 2);
2635: --
2636: -- Check for GEOCODES in a US legislation
2637: --
2638: if p_style = 'US'
2642: -- Check that the county is set
2643: --
2644: if p_region_1 is null then
2645: --
2646: hr_utility.set_message(800, 'PER_52984_ADD_NO_COUNTY_SET');
2647: hr_utility.raise_error;
2648: --
2649: end if;
2650: --
2643: --
2644: if p_region_1 is null then
2645: --
2646: hr_utility.set_message(800, 'PER_52984_ADD_NO_COUNTY_SET');
2647: hr_utility.raise_error;
2648: --
2649: end if;
2650: --
2651: hr_utility.set_location(l_proc, 5);
2647: hr_utility.raise_error;
2648: --
2649: end if;
2650: --
2651: hr_utility.set_location(l_proc, 5);
2652: open csr_valid_us_county;
2653: fetch csr_valid_us_county into l_exists;
2654: if csr_valid_us_county%notfound then
2655: --
2653: fetch csr_valid_us_county into l_exists;
2654: if csr_valid_us_county%notfound then
2655: --
2656: close csr_valid_us_county;
2657: hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
2658: hr_utility.raise_error;
2659: --
2660: end if;
2661: --
2654: if csr_valid_us_county%notfound then
2655: --
2656: close csr_valid_us_county;
2657: hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
2658: hr_utility.raise_error;
2659: --
2660: end if;
2661: --
2662: else
2663: --
2664: -- Check that value for region_1 is valid
2665: --
2666: if p_region_1 is not null then
2667: hr_utility.set_location(l_proc, 3);
2668: --
2669: if p_style = 'GB' then
2670: hr_utility.set_location(l_proc, 4);
2671: --
2666: if p_region_1 is not null then
2667: hr_utility.set_location(l_proc, 3);
2668: --
2669: if p_style = 'GB' then
2670: hr_utility.set_location(l_proc, 4);
2671: --
2672: if hr_api.not_exists_in_hr_lookups
2673: (p_effective_date => p_effective_date
2674: ,p_lookup_type => 'GB_COUNTY'
2674: ,p_lookup_type => 'GB_COUNTY'
2675: ,p_lookup_code => p_region_1
2676: ) then
2677: --
2678: hr_utility.set_message(801, 'HR_7307_ADD_GB_REGION_1');
2679: hr_utility.raise_error;
2680: --
2681: end if;
2682: --
2675: ,p_lookup_code => p_region_1
2676: ) then
2677: --
2678: hr_utility.set_message(801, 'HR_7307_ADD_GB_REGION_1');
2679: hr_utility.raise_error;
2680: --
2681: end if;
2682: --
2683: end if;
2686: --
2687: end if;
2688: --
2689: end if;
2690: hr_utility.set_location(' Leaving:'|| l_proc, 6);
2691: exception
2692: when app_exception.application_exception then
2693: if hr_multi_message.exception_add
2694: (p_associated_column1 => 'PER_ADDRESSES.REGION_1'
2692: when app_exception.application_exception then
2693: if hr_multi_message.exception_add
2694: (p_associated_column1 => 'PER_ADDRESSES.REGION_1'
2695: ) then
2696: hr_utility.set_location(' Leaving:'||l_proc,7);
2697: raise;
2698: end if;
2699: hr_utility.set_location(' Leaving:'||l_proc,8);
2700: --
2695: ) then
2696: hr_utility.set_location(' Leaving:'||l_proc,7);
2697: raise;
2698: end if;
2699: hr_utility.set_location(' Leaving:'||l_proc,8);
2700: --
2701: end chk_region_1;
2702: --
2703: --
2753: from pay_us_counties
2754: where county_name = p_tax_county;
2755: --
2756: begin
2757: hr_utility.set_location('Entering:'|| l_proc, 1);
2758: --
2759: -- Check mandatory parameters have been set
2760: --
2761: if p_tax_county is not null
2784: nvl(per_add_shd.g_old_rec.add_information19, hr_api.g_varchar2) <>
2785: nvl(p_tax_county, hr_api.g_varchar2)) or
2786: (NOT l_api_updating))
2787: then
2788: hr_utility.set_location(l_proc, 2);
2789: --
2790: -- Check for GEOCODES in a US legislation
2791: --
2792: if p_style = 'US'
2796: fetch csr_valid_us_county into l_exists;
2797: if csr_valid_us_county%notfound then
2798: --
2799: close csr_valid_us_county;
2800: hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
2801: hr_utility.raise_error;
2802: --
2803: end if;
2804: close csr_valid_us_county;
2797: if csr_valid_us_county%notfound then
2798: --
2799: close csr_valid_us_county;
2800: hr_utility.set_message(801, 'HR_7953_ADDR_NO_COUNTY_FOUND');
2801: hr_utility.raise_error;
2802: --
2803: end if;
2804: close csr_valid_us_county;
2805: --
2808: end if;
2809: else
2810: null;
2811: end if;
2812: hr_utility.set_location(' Leaving:'|| l_proc, 6);
2813: exception
2814: when app_exception.application_exception then
2815: if hr_multi_message.exception_add
2816: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION19'
2814: when app_exception.application_exception then
2815: if hr_multi_message.exception_add
2816: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION19'
2817: ) then
2818: hr_utility.set_location(' Leaving:'||l_proc,7);
2819: raise;
2820: end if;
2821: hr_utility.set_location(' Leaving:'||l_proc,8);
2822: --
2817: ) then
2818: hr_utility.set_location(' Leaving:'||l_proc,7);
2819: raise;
2820: end if;
2821: hr_utility.set_location(' Leaving:'||l_proc,8);
2822: --
2823: end chk_tax_county;
2824: --
2825: -- ---------------------------------------------------------------------------
2880: from pay_us_states
2881: where state_abbrev = p_region_2;
2882: --
2883: begin
2884: hr_utility.set_location('Entering:'|| l_proc, 1);
2885: --
2886: -- Check mandatory parameters have been set.
2887: --
2888: hr_api.mandatory_arg_error
2910: nvl(per_add_shd.g_old_rec.region_2, hr_api.g_varchar2) <>
2911: nvl(p_region_2, hr_api.g_varchar2)) or
2912: (NOT l_api_updating))
2913: then
2914: hr_utility.set_location(l_proc, 2);
2915: --
2916: -- Check if GEOCODES is installed under a US legislation
2917: --
2918: if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y'
2916: -- Check if GEOCODES is installed under a US legislation
2917: --
2918: if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y'
2919: then
2920: hr_utility.set_location(l_proc, 5);
2921: --
2922: -- Check if the state is set
2923: --
2924: if p_region_2 is null then
2922: -- Check if the state is set
2923: --
2924: if p_region_2 is null then
2925: --
2926: hr_utility.set_message(800, 'PER_52985_ADD_NO_STATE_SET');
2927: hr_utility.raise_error;
2928: --
2929: end if;
2930: --
2923: --
2924: if p_region_2 is null then
2925: --
2926: hr_utility.set_message(800, 'PER_52985_ADD_NO_STATE_SET');
2927: hr_utility.raise_error;
2928: --
2929: end if;
2930: --
2931: open csr_valid_state;
2931: open csr_valid_state;
2932: fetch csr_valid_state into l_exists;
2933: if csr_valid_state%notfound then
2934: close csr_valid_state;
2935: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
2936: hr_utility.raise_error;
2937: end if;
2938: close csr_valid_state;
2939: --
2932: fetch csr_valid_state into l_exists;
2933: if csr_valid_state%notfound then
2934: close csr_valid_state;
2935: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
2936: hr_utility.raise_error;
2937: end if;
2938: close csr_valid_state;
2939: --
2940: else
2941: --
2942: -- Check that value for region_2 is valid.
2943: --
2944: if p_region_2 is not null then
2945: hr_utility.set_location(l_proc, 3);
2946: --
2947: if p_style = 'US'
2948: then
2949: hr_utility.set_location(l_proc, 4);
2945: hr_utility.set_location(l_proc, 3);
2946: --
2947: if p_style = 'US'
2948: then
2949: hr_utility.set_location(l_proc, 4);
2950: --
2951: if hr_api.not_exists_in_hr_lookups
2952: (p_effective_date => p_effective_date
2953: ,p_lookup_type => 'US_STATE'
2955: )
2956: then
2957: --
2958: -- Error: Invalid region 2.
2959: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
2960: hr_utility.raise_error;
2961: --
2962: end if;
2963: --
2956: then
2957: --
2958: -- Error: Invalid region 2.
2959: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
2960: hr_utility.raise_error;
2961: --
2962: end if;
2963: --
2964: end if;
2968: end if;
2969: --
2970: end if;
2971: --
2972: hr_utility.set_location(' Leaving:'|| l_proc, 10);
2973: exception
2974: when app_exception.application_exception then
2975: if hr_multi_message.exception_add
2976: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
2974: when app_exception.application_exception then
2975: if hr_multi_message.exception_add
2976: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
2977: ) then
2978: hr_utility.set_location(' Leaving:'||l_proc,11);
2979: raise;
2980: end if;
2981: hr_utility.set_location(' Leaving:'||l_proc,12);
2982: --
2977: ) then
2978: hr_utility.set_location(' Leaving:'||l_proc,11);
2979: raise;
2980: end if;
2981: hr_utility.set_location(' Leaving:'||l_proc,12);
2982: --
2983: end chk_region_2;
2984: --
2985: -- ---------------------------------------------------------------------------
3042: --
3043: begin
3044: if p_tax_state is not null
3045: then
3046: hr_utility.set_location('Entering:'|| l_proc, 1);
3047: --
3048: -- Check mandatory parameters have been set.
3049: --
3050: hr_api.mandatory_arg_error
3072: nvl(per_add_shd.g_old_rec.add_information17, hr_api.g_varchar2) <>
3073: nvl(p_tax_state, hr_api.g_varchar2)) or
3074: (NOT l_api_updating))
3075: then
3076: hr_utility.set_location(l_proc, 2);
3077: --
3078: -- Check if GEOCODES is installed under a US legislation
3079: --
3080: if hr_general.chk_geocodes_installed = 'Y'
3078: -- Check if GEOCODES is installed under a US legislation
3079: --
3080: if hr_general.chk_geocodes_installed = 'Y'
3081: then
3082: hr_utility.set_location(l_proc, 5);
3083: open csr_valid_state;
3084: fetch csr_valid_state into l_exists;
3085: if csr_valid_state%notfound then
3086: close csr_valid_state;
3083: open csr_valid_state;
3084: fetch csr_valid_state into l_exists;
3085: if csr_valid_state%notfound then
3086: close csr_valid_state;
3087: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
3088: hr_utility.raise_error;
3089: end if;
3090: close csr_valid_state;
3091: --
3084: fetch csr_valid_state into l_exists;
3085: if csr_valid_state%notfound then
3086: close csr_valid_state;
3087: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
3088: hr_utility.raise_error;
3089: end if;
3090: close csr_valid_state;
3091: --
3092: else
3092: else
3093: --
3094: -- Check that value for tax_state is valid.
3095: --
3096: hr_utility.set_location(l_proc, 4);
3097: --
3098: if hr_api.not_exists_in_hr_lookups
3099: (p_effective_date => p_effective_date
3100: ,p_lookup_type => 'US_STATE'
3102: )
3103: then
3104: --
3105: -- Error: Invalid tax_state.
3106: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
3107: hr_utility.raise_error;
3108: --
3109: end if;
3110: --
3103: then
3104: --
3105: -- Error: Invalid tax_state.
3106: hr_utility.set_message(801, 'HR_7952_ADDR_NO_STATE_CODE');
3107: hr_utility.raise_error;
3108: --
3109: end if;
3110: --
3111: end if;
3112: --
3113: end if;
3114: end if;
3115: --
3116: hr_utility.set_location(' Leaving:'|| l_proc, 10);
3117: exception
3118: when app_exception.application_exception then
3119: if hr_multi_message.exception_add
3120: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3118: when app_exception.application_exception then
3119: if hr_multi_message.exception_add
3120: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3121: ) then
3122: hr_utility.set_location(' Leaving:'||l_proc,11);
3123: raise;
3124: end if;
3125: hr_utility.set_location(' Leaving:'||l_proc,12);
3126: --
3121: ) then
3122: hr_utility.set_location(' Leaving:'||l_proc,11);
3123: raise;
3124: end if;
3125: hr_utility.set_location(' Leaving:'||l_proc,12);
3126: --
3127: end chk_tax_state;
3128: --
3129: -- ---------------------------------------------------------------------------
3177: select null
3178: from pay_us_city_names
3179: where city_name = p_town_or_city;
3180: begin
3181: hr_utility.set_location('Entering:'|| l_proc, 1);
3182: --
3183: -- Check mandatory parameters have been set
3184: --
3185: hr_api.mandatory_arg_error
3200: nvl(per_add_shd.g_old_rec.town_or_city, hr_api.g_varchar2) <>
3201: nvl(p_town_or_city, hr_api.g_varchar2)) or
3202: (NOT l_api_updating))
3203: then
3204: hr_utility.set_location(l_proc, 2);
3205: --
3206: if p_style = 'US' and hr_general.chk_geocodes_installed = 'Y' then
3207: --
3208: -- Check that the city is set
3208: -- Check that the city is set
3209: --
3210: if p_town_or_city is null then
3211: --
3212: hr_utility.set_message(800, 'PER_52986_ADD_NO_CITY_SET');
3213: hr_utility.raise_error;
3214: --
3215: end if;
3216: --
3209: --
3210: if p_town_or_city is null then
3211: --
3212: hr_utility.set_message(800, 'PER_52986_ADD_NO_CITY_SET');
3213: hr_utility.raise_error;
3214: --
3215: end if;
3216: --
3217: open csr_valid_town_or_city;
3217: open csr_valid_town_or_city;
3218: fetch csr_valid_town_or_city into l_exists;
3219: if csr_valid_town_or_city%notfound then
3220: close csr_valid_town_or_city;
3221: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3222: hr_utility.raise_error;
3223: end if;
3224: close csr_valid_town_or_city;
3225: hr_utility.set_location(l_proc, 4);
3218: fetch csr_valid_town_or_city into l_exists;
3219: if csr_valid_town_or_city%notfound then
3220: close csr_valid_town_or_city;
3221: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3222: hr_utility.raise_error;
3223: end if;
3224: close csr_valid_town_or_city;
3225: hr_utility.set_location(l_proc, 4);
3226: --
3221: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3222: hr_utility.raise_error;
3223: end if;
3224: close csr_valid_town_or_city;
3225: hr_utility.set_location(l_proc, 4);
3226: --
3227: end if;
3228: --
3229: end if;
3227: end if;
3228: --
3229: end if;
3230: --
3231: hr_utility.set_location(' Leaving:'|| l_proc, 5);
3232: exception
3233: when app_exception.application_exception then
3234: if hr_multi_message.exception_add
3235: (p_associated_column1 => 'PER_ADDRESSES.TOWN_OR_CITY'
3233: when app_exception.application_exception then
3234: if hr_multi_message.exception_add
3235: (p_associated_column1 => 'PER_ADDRESSES.TOWN_OR_CITY'
3236: ) then
3237: hr_utility.set_location(' Leaving:'||l_proc,6);
3238: raise;
3239: end if;
3240: hr_utility.set_location(' Leaving:'||l_proc,7);
3241: --
3236: ) then
3237: hr_utility.set_location(' Leaving:'||l_proc,6);
3238: raise;
3239: end if;
3240: hr_utility.set_location(' Leaving:'||l_proc,7);
3241: --
3242: end chk_town_or_city;
3243: --
3244: -- ---------------------------------------------------------------------------
3294: where city_name = p_tax_city;
3295: begin
3296: if p_tax_city is not null
3297: then
3298: hr_utility.set_location('Entering:'|| l_proc, 1);
3299: --
3300: -- Check mandatory parameters have been set
3301: --
3302: hr_api.mandatory_arg_error
3317: nvl(per_add_shd.g_old_rec.add_information18, hr_api.g_varchar2) <>
3318: nvl(p_tax_city, hr_api.g_varchar2)) or
3319: (NOT l_api_updating))
3320: then
3321: hr_utility.set_location(l_proc, 2);
3322: --
3323: if hr_general.chk_geocodes_installed = 'Y' then
3324: open csr_valid_tax_city;
3325: fetch csr_valid_tax_city into l_exists;
3324: open csr_valid_tax_city;
3325: fetch csr_valid_tax_city into l_exists;
3326: if csr_valid_tax_city%notfound then
3327: close csr_valid_tax_city;
3328: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3329: hr_utility.raise_error;
3330: end if;
3331: close csr_valid_tax_city;
3332: hr_utility.set_location(l_proc, 4);
3325: fetch csr_valid_tax_city into l_exists;
3326: if csr_valid_tax_city%notfound then
3327: close csr_valid_tax_city;
3328: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3329: hr_utility.raise_error;
3330: end if;
3331: close csr_valid_tax_city;
3332: hr_utility.set_location(l_proc, 4);
3333: --
3328: hr_utility.set_message(801, 'HR_51276_ADD_INVALID_CITY');
3329: hr_utility.raise_error;
3330: end if;
3331: close csr_valid_tax_city;
3332: hr_utility.set_location(l_proc, 4);
3333: --
3334: end if;
3335: --
3336: end if;
3335: --
3336: end if;
3337: --
3338: end if;
3339: hr_utility.set_location(' Leaving:'|| l_proc, 5);
3340: exception
3341: when app_exception.application_exception then
3342: if hr_multi_message.exception_add
3343: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION18'
3341: when app_exception.application_exception then
3342: if hr_multi_message.exception_add
3343: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION18'
3344: ) then
3345: hr_utility.set_location(' Leaving:'||l_proc,6);
3346: raise;
3347: end if;
3348: hr_utility.set_location(' Leaving:'||l_proc,7);
3349: --
3344: ) then
3345: hr_utility.set_location(' Leaving:'||l_proc,6);
3346: raise;
3347: end if;
3348: hr_utility.set_location(' Leaving:'||l_proc,7);
3349: --
3350: end chk_tax_city;
3351: --
3352: -- ---------------------------------------------------------------------------
3460: and l_postal_code between zip.zip_start
3461: and zip.zip_end;
3462: --
3463: begin
3464: hr_utility.set_location('Entering:'|| l_proc, 10);
3465: --
3466: -- Only proceed with validation if :
3467: -- a) US address style and payroll is installed under US legislation and
3468: -- b) The current g_old_rec is current and
3482: (nvl(per_add_shd.g_old_rec.town_or_city, hr_api.g_varchar2) <>
3483: nvl(p_town_or_city, hr_api.g_varchar2))) or
3484: (NOT l_api_updating)) then
3485: --
3486: hr_utility.set_location(l_proc, 20);
3487: --
3488: -- Check if US payroll installed.
3489: --
3490: l_geocodes_installed := hr_general.chk_geocodes_installed;
3495: --
3496: if p_style = 'US'
3497: and l_geocodes_installed = 'Y'
3498: then
3499: hr_utility.set_location(l_proc, 30);
3500: --
3501: -- Extract the first 5 characters of the zip code
3502: --
3503: l_postal_code := substr(p_postal_code,1,5);
3509: (p_check_column1 => 'PER_ADDRESSES.REGION_2'
3510: ,p_check_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3511: ) then
3512: --
3513: hr_utility.set_location(l_proc, 40);
3514: --
3515: -- no county is given, so procede with validation which excluses it;
3516: -- validate state and city;
3517: --
3518: open csr_val_st_city;
3519: fetch csr_val_st_city into l_state_code;
3520: if(csr_val_st_city%notfound) then
3521: close csr_val_st_city;
3522: hr_utility.set_location(l_proc, 50);
3523: hr_utility.set_message(800, 'PER_52531_ADD_INV_STCI_COMB');
3524: hr_multi_message.add
3525: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3526: ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3519: fetch csr_val_st_city into l_state_code;
3520: if(csr_val_st_city%notfound) then
3521: close csr_val_st_city;
3522: hr_utility.set_location(l_proc, 50);
3523: hr_utility.set_message(800, 'PER_52531_ADD_INV_STCI_COMB');
3524: hr_multi_message.add
3525: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3526: ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3527: );
3526: ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3527: );
3528: else
3529: close csr_val_st_city;
3530: hr_utility.set_location(l_proc, 60);
3531: --
3532: -- check for a valid state, city, zip combination
3533: --
3534: if hr_multi_message.no_exclusive_error
3538: open csr_valid_zip_code_no_ncty;
3539: fetch csr_valid_zip_code_no_ncty into l_exists;
3540: if csr_valid_zip_code_no_ncty%notfound then
3541: close csr_valid_zip_code_no_ncty;
3542: hr_utility.set_location(l_proc, 70);
3543: hr_utility.set_message(800, 'PER_52532_ADD_INV_STCIZ_COMB');
3544: hr_multi_message.add
3545: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3546: ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3539: fetch csr_valid_zip_code_no_ncty into l_exists;
3540: if csr_valid_zip_code_no_ncty%notfound then
3541: close csr_valid_zip_code_no_ncty;
3542: hr_utility.set_location(l_proc, 70);
3543: hr_utility.set_message(800, 'PER_52532_ADD_INV_STCIZ_COMB');
3544: hr_multi_message.add
3545: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3546: ,p_associated_column2 => 'PER_ADDRESSES.TOWN_OR_CITY'
3547: ,p_associated_column3 => 'PER_ADDRESSES.POSTAL_CODE'
3547: ,p_associated_column3 => 'PER_ADDRESSES.POSTAL_CODE'
3548: );
3549: else
3550: close csr_valid_zip_code_no_ncty;
3551: hr_utility.set_location(l_proc, 80);
3552: end if;
3553: --
3554: end if; -- no_exclusive_error check for POSTAL_CODE
3555: --
3567: (p_check_column1 => 'PER_ADDRESSES.REGION_1'
3568: ,p_check_column2 => 'PER_ADDRESSES.REGION_2'
3569: ) then
3570: --
3571: hr_utility.set_location(l_proc, 90);
3572: open csr_valid_state_county;
3573: fetch csr_valid_state_county into l_state_code, l_county_code;
3574: --
3575: if csr_valid_state_county%notfound then
3574: --
3575: if csr_valid_state_county%notfound then
3576: close csr_valid_state_county;
3577: --
3578: hr_utility.set_location(l_proc, 100);
3579: hr_utility.set_message(800, 'PER_52988_ADD_INV_STCOU_COMB');
3580: hr_multi_message.add
3581: (p_associated_column1 => 'PER_ADDRESSES.REGION_1'
3582: ,p_associated_column2 => 'PER_ADDRESSES.REGION_2'
3575: if csr_valid_state_county%notfound then
3576: close csr_valid_state_county;
3577: --
3578: hr_utility.set_location(l_proc, 100);
3579: hr_utility.set_message(800, 'PER_52988_ADD_INV_STCOU_COMB');
3580: hr_multi_message.add
3581: (p_associated_column1 => 'PER_ADDRESSES.REGION_1'
3582: ,p_associated_column2 => 'PER_ADDRESSES.REGION_2'
3583: );
3582: ,p_associated_column2 => 'PER_ADDRESSES.REGION_2'
3583: );
3584: else
3585: close csr_valid_state_county;
3586: hr_utility.set_location(l_proc, 110);
3587: end if;
3588: --
3589: -- Validate the state, county and city combination
3590: --
3596: --
3597: if csr_val_st_county_city%notfound then
3598: close csr_val_st_county_city;
3599: --
3600: hr_utility.set_location(l_proc, 120);
3601: hr_utility.set_message(800, 'PER_52987_ADD_INV_STCOCY_COMB');
3602: hr_multi_message.add
3603: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3604: ,p_associated_column2 => 'PER_ADDRESSES.REGION_1'
3597: if csr_val_st_county_city%notfound then
3598: close csr_val_st_county_city;
3599: --
3600: hr_utility.set_location(l_proc, 120);
3601: hr_utility.set_message(800, 'PER_52987_ADD_INV_STCOCY_COMB');
3602: hr_multi_message.add
3603: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3604: ,p_associated_column2 => 'PER_ADDRESSES.REGION_1'
3605: ,p_associated_column3 => 'PER_ADDRESSES.TOWN_OR_CITY'
3605: ,p_associated_column3 => 'PER_ADDRESSES.TOWN_OR_CITY'
3606: );
3607: else
3608: close csr_val_st_county_city;
3609: hr_utility.set_location(l_proc, 130);
3610: end if;
3611: --
3612: -- Validate the state, county, city and zip code combination
3613: --
3619: fetch csr_valid_zip_code into l_exists;
3620: if csr_valid_zip_code%notfound then
3621: close csr_valid_zip_code;
3622: --
3623: hr_utility.set_location(l_proc, 140);
3624: hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
3625: hr_multi_message.add
3626: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3627: ,p_associated_column2 => 'PER_ADDRESSES.REGION_1'
3620: if csr_valid_zip_code%notfound then
3621: close csr_valid_zip_code;
3622: --
3623: hr_utility.set_location(l_proc, 140);
3624: hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
3625: hr_multi_message.add
3626: (p_associated_column1 => 'PER_ADDRESSES.REGION_2'
3627: ,p_associated_column2 => 'PER_ADDRESSES.REGION_1'
3628: ,p_associated_column3 => 'PER_ADDRESSES.TOWN_OR_CITY'
3629: ,p_associated_column4 => 'PER_ADDRESSES.POSTAL_CODE'
3630: );
3631: else
3632: close csr_valid_zip_code;
3633: hr_utility.set_location(l_proc, 150);
3634: end if;
3635: --
3636: end if; -- no_exclusive_error check for postal_code
3637: --
3643: --
3644: end if; -- end if for p_style = 'US'
3645: --
3646: end if; -- end if for api_updating check
3647: hr_utility.set_location(' Leaving:'|| l_proc, 160);
3648: --
3649: end chk_city_state_zip_comb;
3650: --
3651: --
3765: if p_tax_city is not null and
3766: p_tax_state is not null and
3767: p_tax_zip is not null
3768: then
3769: hr_utility.set_location('Entering:'|| l_proc, 10);
3770: --
3771: -- Only proceed with validation if :
3772: -- a) US address style and payroll is installed under US legislation and
3773: -- b) The current g_old_rec is current and
3788: (nvl(per_add_shd.g_old_rec.add_information18, hr_api.g_varchar2) <>
3789: nvl(p_tax_city, hr_api.g_varchar2))) or
3790: (NOT l_api_updating)) then
3791: --
3792: hr_utility.set_location(l_proc, 20);
3793: --
3794: -- Check if US payroll installed.
3795: --
3796: l_geocodes_installed := hr_general.chk_geocodes_installed;
3800: -- city, state and county.
3801: --
3802: If l_geocodes_installed = 'Y'
3803: then
3804: hr_utility.set_location(l_proc, 30);
3805: --
3806: -- Extract the first 5 characters of the zip code
3807: --
3808: l_postal_code := substr(p_tax_zip,1,5);
3812: if hr_multi_message.no_exclusive_error
3813: (p_check_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3814: ,p_check_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3815: ) then
3816: hr_utility.set_location(l_proc, 40);
3817: --
3818: -- no county is given, so procede with validation which excluses it;
3819: -- validate state and city;
3820: --
3821: open csr_val_st_city;
3822: fetch csr_val_st_city into l_state_code;
3823: if(csr_val_st_city%notfound) then
3824: close csr_val_st_city;
3825: hr_utility.set_location(l_proc, 50);
3826: hr_utility.set_message(800, 'PER_52531_ADD_INV_STCI_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_val_st_city into l_state_code;
3823: if(csr_val_st_city%notfound) then
3824: close csr_val_st_city;
3825: hr_utility.set_location(l_proc, 50);
3826: hr_utility.set_message(800, 'PER_52531_ADD_INV_STCI_COMB');
3827: hr_multi_message.add
3828: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3829: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3830: );
3829: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3830: );
3831: else
3832: close csr_val_st_city;
3833: hr_utility.set_location(l_proc, 60);
3834: --
3835: -- check for a valid state, city, zip combination
3836: --
3837: if hr_multi_message.no_exclusive_error
3840: open csr_valid_zip_code_no_ncty;
3841: fetch csr_valid_zip_code_no_ncty into l_exists;
3842: if csr_valid_zip_code_no_ncty%notfound then
3843: close csr_valid_zip_code_no_ncty;
3844: hr_utility.set_location(l_proc, 70);
3845: hr_utility.set_message(800, 'PER_52532_ADD_INV_STCIZ_COMB');
3846: hr_multi_message.add
3847: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3848: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3841: fetch csr_valid_zip_code_no_ncty into l_exists;
3842: if csr_valid_zip_code_no_ncty%notfound then
3843: close csr_valid_zip_code_no_ncty;
3844: hr_utility.set_location(l_proc, 70);
3845: hr_utility.set_message(800, 'PER_52532_ADD_INV_STCIZ_COMB');
3846: hr_multi_message.add
3847: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3848: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3849: ,p_associated_column3 => 'PER_ADDRESSES.ADD_INFORMATION20'
3850: );
3851: else
3852: --
3853: close csr_valid_zip_code_no_ncty;
3854: hr_utility.set_location(l_proc, 80);
3855: --
3856: end if;
3857: --
3858: end if; -- no_exclusive_error for ADD_INFORMATION20(p_tax_zip)
3871: (p_check_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3872: ,p_check_column2 => 'PER_ADDRESSES.ADD_INFORMATION19'
3873: ) then
3874: --
3875: hr_utility.set_location(l_proc, 90);
3876: open csr_valid_state_county;
3877: fetch csr_valid_state_county into l_state_code, l_county_code;
3878: --
3879: if csr_valid_state_county%notfound then
3878: --
3879: if csr_valid_state_county%notfound then
3880: close csr_valid_state_county;
3881: --
3882: hr_utility.set_location(l_proc, 100);
3883: hr_utility.set_message(800, 'PER_52988_ADD_INV_STCOU_COMB');
3884: hr_multi_message.add
3885: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3886: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION19'
3879: if csr_valid_state_county%notfound then
3880: close csr_valid_state_county;
3881: --
3882: hr_utility.set_location(l_proc, 100);
3883: hr_utility.set_message(800, 'PER_52988_ADD_INV_STCOU_COMB');
3884: hr_multi_message.add
3885: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3886: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION19'
3887: );
3888: --
3889: else
3890: --
3891: close csr_valid_state_county;
3892: hr_utility.set_location(l_proc, 110);
3893: --
3894: end if;
3895: --
3896: -- Validate the state, county and city combination
3903: --
3904: if csr_val_st_county_city%notfound then
3905: close csr_val_st_county_city;
3906: --
3907: hr_utility.set_location(l_proc, 120);
3908: hr_utility.set_message(800, 'PER_52987_ADD_INV_STCOCY_COMB');
3909: hr_multi_message.add
3910: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3911: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3904: if csr_val_st_county_city%notfound then
3905: close csr_val_st_county_city;
3906: --
3907: hr_utility.set_location(l_proc, 120);
3908: hr_utility.set_message(800, 'PER_52987_ADD_INV_STCOCY_COMB');
3909: hr_multi_message.add
3910: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3911: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3912: ,p_associated_column3 => 'PER_ADDRESSES.ADD_INFORMATION19'
3914: --
3915: else
3916: --
3917: close csr_val_st_county_city;
3918: hr_utility.set_location(l_proc, 130);
3919: --
3920: end if;
3921: --
3922: -- Validate the state, county, city and zip code combination
3928: fetch csr_valid_zip_code into l_exists;
3929: if csr_valid_zip_code%notfound then
3930: close csr_valid_zip_code;
3931: --
3932: hr_utility.set_location(l_proc, 140);
3933: hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
3934: hr_multi_message.add
3935: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3936: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3929: if csr_valid_zip_code%notfound then
3930: close csr_valid_zip_code;
3931: --
3932: hr_utility.set_location(l_proc, 140);
3933: hr_utility.set_message(801, 'HR_51282_ADD_INV_ZIP_FOR_CITY');
3934: hr_multi_message.add
3935: (p_associated_column1 => 'PER_ADDRESSES.ADD_INFORMATION17'
3936: ,p_associated_column2 => 'PER_ADDRESSES.ADD_INFORMATION18'
3937: ,p_associated_column3 => 'PER_ADDRESSES.ADD_INFORMATION19'
3940: --
3941: else
3942: --
3943: close csr_valid_zip_code;
3944: hr_utility.set_location(l_proc, 150);
3945: --
3946: end if;
3947: --
3948: end if; -- no_exclusive_error for ADD_INFORMATION20(p_tax_zip)
3956: end if;
3957: --
3958: end if;
3959: end if;
3960: hr_utility.set_location(' Leaving:'|| l_proc, 160);
3961: --
3962: end chk_tax_city_state_zip_comb;
3963: --
3964: -- ---------------------------------------------------------------------------
4021: and pa2.primary_flag = 'Y');
4022: --
4023: begin
4024: l_date := nvl(per_add_shd.g_old_rec.date_to, hr_api.g_eot);
4025: hr_utility.set_location('Entering:'|| l_proc, 1);
4026: --
4027: -- For primary addresses only
4028: -- ==========================
4029: --
4038: open csr_del_address;
4039: fetch csr_del_address into l_exists;
4040: if csr_del_address%found then
4041: close csr_del_address;
4042: hr_utility.set_message(801, 'HR_7308_ADD_PRIMARY_DEL');
4043: hr_utility.raise_error;
4044: end if;
4045: close csr_del_address;
4046: hr_utility.set_location(l_proc, 2);
4039: fetch csr_del_address into l_exists;
4040: if csr_del_address%found then
4041: close csr_del_address;
4042: hr_utility.set_message(801, 'HR_7308_ADD_PRIMARY_DEL');
4043: hr_utility.raise_error;
4044: end if;
4045: close csr_del_address;
4046: hr_utility.set_location(l_proc, 2);
4047: --
4042: hr_utility.set_message(801, 'HR_7308_ADD_PRIMARY_DEL');
4043: hr_utility.raise_error;
4044: end if;
4045: close csr_del_address;
4046: hr_utility.set_location(l_proc, 2);
4047: --
4048: -- Check that the deletion of a primary
4049: -- address does not break the contiguous
4050: -- nature of the address
4052: open csr_no_del_contig_add;
4053: fetch csr_no_del_contig_add into l_exists;
4054: if csr_no_del_contig_add%found then
4055: close csr_no_del_contig_add;
4056: hr_utility.set_message(801, 'HR_51030_ADDR_PRIM_GAP');
4057: hr_utility.raise_error;
4058: end if;
4059: close csr_no_del_contig_add;
4060: end if;
4053: fetch csr_no_del_contig_add into l_exists;
4054: if csr_no_del_contig_add%found then
4055: close csr_no_del_contig_add;
4056: hr_utility.set_message(801, 'HR_51030_ADDR_PRIM_GAP');
4057: hr_utility.raise_error;
4058: end if;
4059: close csr_no_del_contig_add;
4060: end if;
4061: --
4058: end if;
4059: close csr_no_del_contig_add;
4060: end if;
4061: --
4062: hr_utility.set_location(' Leaving:'|| l_proc, 3);
4063: hr_utility.set_location(' Leaving:'||l_proc,5);
4064: end chk_del_address;
4065: --
4066: -- ----------------------------------------------------------------------------
4059: close csr_no_del_contig_add;
4060: end if;
4061: --
4062: hr_utility.set_location(' Leaving:'|| l_proc, 3);
4063: hr_utility.set_location(' Leaving:'||l_proc,5);
4064: end chk_del_address;
4065: --
4066: -- ----------------------------------------------------------------------------
4067: -- |----------------------< check_non_updateable_args >-----------------------|
4094: --
4095: l_proc varchar2(72) := g_package||'check_non_updateable_args';
4096: --
4097: Begin
4098: hr_utility.set_location('Entering:'||l_proc, 5);
4099: --
4100: -- Only proceed with validation if a row exists for
4101: -- the current record in the HR Schema
4102: --
4102: --
4103: if not per_add_shd.api_updating
4104: (p_address_id => p_rec.address_id,
4105: p_object_version_number => p_rec.object_version_number) then
4106: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4107: hr_utility.set_message_token('PROCEDURE', l_proc);
4108: hr_utility.set_message_token('STEP', '5');
4109: end if;
4110: --
4103: if not per_add_shd.api_updating
4104: (p_address_id => p_rec.address_id,
4105: p_object_version_number => p_rec.object_version_number) then
4106: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4107: hr_utility.set_message_token('PROCEDURE', l_proc);
4108: hr_utility.set_message_token('STEP', '5');
4109: end if;
4110: --
4111: hr_utility.set_location(l_proc, 6);
4104: (p_address_id => p_rec.address_id,
4105: p_object_version_number => p_rec.object_version_number) then
4106: hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
4107: hr_utility.set_message_token('PROCEDURE', l_proc);
4108: hr_utility.set_message_token('STEP', '5');
4109: end if;
4110: --
4111: hr_utility.set_location(l_proc, 6);
4112: --
4107: hr_utility.set_message_token('PROCEDURE', l_proc);
4108: hr_utility.set_message_token('STEP', '5');
4109: end if;
4110: --
4111: hr_utility.set_location(l_proc, 6);
4112: --
4113: -- start of commenting the code for business_group_id and person_id
4114: -- are updateable if currently null
4115: /*
4120: ,p_argument => 'BUSINESS_GROUP_ID'
4121: ,p_base_table => per_add_shd.g_tab_nam
4122: );
4123: end if;
4124: hr_utility.set_location(l_proc, 7);
4125: --
4126: if nvl(p_rec.person_id, hr_api.g_number) <>
4127: per_add_shd.g_old_rec.person_id then
4128: hr_api.argument_changed_error
4133: end if;
4134: */
4135: -- end of commenting code
4136: --
4137: hr_utility.set_location(l_proc, 8);
4138: --
4139: if not g_called_from_form then
4140: if nvl(p_rec.primary_flag, hr_api.g_varchar2) <>
4141: per_add_shd.g_old_rec.primary_flag then
4145: ,p_base_table => per_add_shd.g_tab_nam
4146: );
4147: end if;
4148: end if;
4149: hr_utility.set_location(l_proc, 11);
4150: --
4151: hr_utility.set_location(' Leaving:'||l_proc, 12);
4152: end check_non_updateable_args;
4153: --
4147: end if;
4148: end if;
4149: hr_utility.set_location(l_proc, 11);
4150: --
4151: hr_utility.set_location(' Leaving:'||l_proc, 12);
4152: end check_non_updateable_args;
4153: --
4154: -- -----------------------------------------------------------------------------
4155: -- |--------------------------------< chk_df >---------------------------------|
4160: --
4161: l_proc varchar2(72) := g_package||'chk_df';
4162: --
4163: Begin
4164: hr_utility.set_location('Entering:'||l_proc, 5);
4165: --
4166: -- Check if the row is being inserted or updated and a
4167: -- value has changed
4168: --
4261: );
4262: --
4263: end if;
4264: --
4265: hr_utility.set_location(' Leaving:'||l_proc, 10);
4266: end chk_df;
4267: --
4268: -- -----------------------------------------------------------------------------
4269: -- |-------------------------------< chk_ddf >---------------------------------|
4275: l_proc varchar2(72) := g_package||'chk_ddf';
4276: l_error exception;
4277: --
4278: Begin
4279: hr_utility.set_location('Entering:'||l_proc, 5);
4280: --
4281: -- Check if the row is being inserted or updated and a
4282: -- value has changed
4283: --
4376: );
4377: --
4378: end if;
4379: --
4380: hr_utility.set_location(' Leaving:'||l_proc, 10);
4381: end chk_ddf;
4382: --
4383: -- ---------------------------------------------------------------------------
4384: -- |----------------------< df_update_validate >---------------------------|
4414: --
4415: l_proc varchar2(72) := g_package||'df_update_validate';
4416: --
4417: begin
4418: hr_utility.set_location('Entering:'||l_proc, 10);
4419: --
4420: if nvl(per_add_shd.g_old_rec.addr_attribute_category, hr_api.g_varchar2) <>
4421: nvl(p_rec.addr_attribute_category, hr_api.g_varchar2) or
4422: nvl(per_add_shd.g_old_rec.addr_attribute1, hr_api.g_varchar2) <>
4482: -- so we must call the flex stub
4483: per_add_flex.df(p_rec => p_rec);
4484: end if;
4485: --
4486: hr_utility.set_location(' Leaving:'||l_proc, 10);
4487: end df_update_validate;
4488: --
4489: -- ----------------------------------------------------------------------------
4490: -- |---------------------------< insert_validate >----------------------------|
4497: --
4498: l_proc varchar2(72) := g_package||'insert_validate';
4499: --
4500: Begin
4501: hr_utility.set_location('Entering:'||l_proc, 10);
4502: --
4503: -- Validate Important Attributes
4504: --
4505: -- Reset global variable that indicates if payroll is installed under
4520: p_business_group_id => p_rec.business_group_id
4521: ,p_associated_column1 => per_add_shd.g_tab_nam ||
4522: '.BUSINESS_GROUP_ID'
4523: );
4524: hr_utility.set_location(l_proc, 20);
4525: --
4526: -- After validating the set of important attributes,
4527: -- if Mulitple message detection is enabled and at least
4528: -- one error has been found then abort further validation.
4541: ,p_date_to => p_rec.date_to
4542: ,p_object_version_number => p_rec.object_version_number
4543: );
4544: --
4545: hr_utility.set_location(l_proc, 30);
4546: -- HR/TCA merge
4547: --
4548: -- Validate business_group_id
4549: --
4552: ,p_object_version_number => p_rec.object_version_number
4553: ,p_person_id => p_rec.person_id
4554: ,p_business_group_id => p_rec.business_group_id
4555: );
4556: hr_utility.set_location(l_proc, 35);
4557: --
4558: if p_rec.person_id is not null then
4559: --
4560: -- Validate person_id
4564: ,p_object_version_number => p_rec.object_version_number
4565: ,p_person_id => p_rec.person_id
4566: ,p_business_group_id => p_rec.business_group_id
4567: );
4568: hr_utility.set_location(l_proc, 40);
4569: end if;
4570: --
4571: -- Validate party_id
4572: --
4572: --
4573: chk_party_id
4574: (p_rec
4575: );
4576: hr_utility.set_location(l_proc, 45);
4577: --
4578: -- Validate primary flag
4579: --
4580: chk_primary_flag
4581: (p_address_id => p_rec.address_id
4582: ,p_object_version_number => p_rec.object_version_number
4583: ,p_primary_flag => p_rec.primary_flag
4584: );
4585: hr_utility.set_location(l_proc, 50);
4586: --
4587: if not g_called_from_form then
4588: --
4589: -- Validate date/address_type combination
4600: ,p_party_id => p_rec.party_id -- HR/TCA merge
4601: );
4602: end if;
4603: --
4604: hr_utility.set_location(l_proc, 60);
4605: --
4606: -- Validate style
4607: --
4608: chk_style
4607: --
4608: chk_style
4609: (p_style => p_rec.style
4610: );
4611: hr_utility.set_location(l_proc, 70);
4612: --
4613: -- Validate address type
4614: --
4615: chk_address_type
4618: ,p_date_from => p_rec.date_from
4619: ,p_effective_date => p_effective_date
4620: ,p_object_version_number => p_rec.object_version_number
4621: );
4622: hr_utility.set_location(l_proc, 80);
4623: --
4624: -- Validate country
4625: --
4626: chk_country
4628: ,p_style => p_rec.style
4629: ,p_address_id => p_rec.address_id
4630: ,p_object_version_number => p_rec.object_version_number
4631: );
4632: hr_utility.set_location(l_proc, 90);
4633: --
4634: -- Validate postal code.
4635: --
4636: chk_postal_code
4653: ,p_business_group_id => p_rec.business_group_id
4654: ,p_object_version_number => p_rec.object_version_number
4655: );
4656: End if;
4657: hr_utility.set_location(l_proc, 100);
4658: --
4659: -- Validation specific to GB, US, GENERIC
4660: --
4661: if p_rec.style = 'GB' or
4664: then
4665: --
4666: -- Check null attributes for address style.
4667: --
4668: hr_utility.set_location(l_proc, 110);
4669: chk_style_null_attr
4670: (p_address_id => p_rec.address_id
4671: ,p_object_version_number => p_rec.object_version_number
4672: ,p_style => p_rec.style
4673: ,p_region_2 => p_rec.region_2
4674: ,p_region_3 => p_rec.region_3
4675: ,p_telephone_number_3 => p_rec.telephone_number_3
4676: );
4677: hr_utility.set_location(l_proc, 120);
4678: --
4679: -- Validate address_line1
4680: --
4681: chk_address_line1
4683: ,p_style => p_rec.style
4684: ,p_address_line1 => p_rec.address_line1
4685: ,p_object_version_number => p_rec.object_version_number
4686: );
4687: hr_utility.set_location(l_proc, 130);
4688: --
4689: -- Validate date_to
4690: --
4691: -- No procedural call is made to the procedure
4717: ,p_effective_date => p_effective_date
4718: ,p_object_version_number => p_rec.object_version_number
4719: );
4720: End if;
4721: hr_utility.set_location(l_proc, 140);
4722: --
4723: -- Validate region 2.
4724: --
4725: chk_region_2
4729: ,p_business_group_id => p_rec.business_group_id
4730: ,p_effective_date => p_effective_date
4731: ,p_object_version_number => p_rec.object_version_number
4732: );
4733: hr_utility.set_location(l_proc, 150);
4734: --
4735: -- Validate tax_state(add_information17)
4736: --
4737: If p_rec.style = 'US' and
4744: ,p_business_group_id => p_rec.business_group_id
4745: ,p_effective_date => p_effective_date
4746: ,p_object_version_number => p_rec.object_version_number
4747: );
4748: hr_utility.set_location(l_proc, 155);
4749: End if;
4750: --
4751: -- Validate town or city.
4752: --
4756: ,p_town_or_city => p_rec.town_or_city
4757: ,p_business_group_id => p_rec.business_group_id
4758: ,p_object_version_number => p_rec.object_version_number
4759: );
4760: hr_utility.set_location(l_proc, 160);
4761: --
4762: -- Validate tax_city (add_information18).
4763: --
4764: If p_rec.style = 'US' and
4769: ,p_tax_city => p_rec.add_information18
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, 165);
4774: End if;
4775: --
4776: --
4777: -- This is only applicable if payroll is installed under US legislation
4788: ,p_town_or_city => p_rec.town_or_city
4789: ,p_business_group_id => p_rec.business_group_id
4790: ,p_object_version_number => p_rec.object_version_number
4791: );
4792: hr_utility.set_location(l_proc, 170);
4793: --
4794: chk_tax_city_state_zip_comb
4795: (p_address_id => p_rec.address_id
4796: ,p_style => p_rec.style
4800: ,p_tax_city => p_rec.add_information18
4801: ,p_business_group_id => p_rec.business_group_id
4802: ,p_object_version_number => p_rec.object_version_number
4803: );
4804: hr_utility.set_location(l_proc, 175);
4805: --Validation specific to JP
4806: --
4807: /* Bug 1677965
4808: elsif p_rec.style = 'JP' then
4805: --Validation specific to JP
4806: --
4807: /* Bug 1677965
4808: elsif p_rec.style = 'JP' then
4809: hr_utility.set_location(l_proc, 21);
4810: --
4811: -- Check the combination checking for town_or_city(district_code)
4812: -- address_line1, and region_1
4813: --
4818: ,p_town_or_city => p_rec.town_or_city
4819: ,p_address_line1 => p_rec.address_line1
4820: ,p_region_1 => p_rec.region_1
4821: );
4822: hr_utility.set_location(l_proc, 180);
4823: --
4824: -- Validate region_2 according to address_line2
4825: --
4826: chk_address2_region2_comb
4828: ,p_object_version_number => p_rec.object_version_number
4829: ,p_address_line2 => p_rec.address_line2
4830: ,p_region_2 => p_rec.region_2
4831: );
4832: hr_utility.set_location(l_proc, 190);
4833: --
4834: -- Validate region_3 according to address_line3
4835: --
4836: chk_address3_region3_comb
4838: ,p_object_version_number => p_rec.object_version_number
4839: ,p_address_line3 => p_rec.address_line3
4840: ,p_region_3 => p_rec.region_3
4841: );
4842: hr_utility.set_location(l_proc, 200);
4843: */
4844: --
4845: -- DDF Validation other than GB, US, JP and GENERIC
4846: --
4844: --
4845: -- DDF Validation other than GB, US, JP and GENERIC
4846: --
4847: else
4848: hr_utility.set_location(l_proc, 205);
4849: --
4850: -- Validate the DDF
4851: --
4852: chk_ddf
4859: --
4860: chk_df
4861: (p_rec => p_rec
4862: );
4863: hr_utility.set_location(' Leaving:'||l_proc, 210);
4864: --
4865: End insert_validate;
4866: --
4867: -- ----------------------------------------------------------------------------
4876: --
4877: l_proc varchar2(72) := g_package||'update_validate';
4878: --
4879: Begin
4880: hr_utility.set_location('Entering:'||l_proc, 10);
4881: --
4882: -- Validate Important Attributes
4883: --
4884: -- Reset global variable that indicates if payroll is installed under
4892: -- Check that the columns which cannot
4893: -- be updated have not changed
4894: --
4895: check_non_updateable_args(p_rec => p_rec);
4896: hr_utility.set_location(l_proc, 15);
4897: --
4898: -- if person_id isn't specified,business_group_id isn't required.
4899: -- HR/TCA merge
4900: if p_rec.person_id is not null then
4905: p_business_group_id => p_rec.business_group_id
4906: ,p_associated_column1 => per_add_shd.g_tab_nam ||
4907: '.BUSINESS_GROUP_ID'
4908: );
4909: hr_utility.set_location(l_proc, 20);
4910: --
4911: -- After validating the set of important attributes,
4912: -- if Mulitple message detection is enabled and at least
4913: -- one error has been found then abort further validation.
4925: ,p_object_version_number => p_rec.object_version_number
4926: ,p_person_id => p_rec.person_id
4927: ,p_business_group_id => p_rec.business_group_id
4928: );
4929: hr_utility.set_location(l_proc, 22);
4930: --
4931: if p_rec.person_id is not null then -- If condition added for 2762677
4932: --
4933: -- Validate person_id
4937: ,p_object_version_number => p_rec.object_version_number
4938: ,p_person_id => p_rec.person_id
4939: ,p_business_group_id => p_rec.business_group_id
4940: );
4941: hr_utility.set_location(l_proc, 25);
4942: end if;
4943: --
4944: -- Validate Dependent Attributes
4945: --
4950: ,p_date_from => p_rec.date_from
4951: ,p_date_to => p_rec.date_to
4952: ,p_object_version_number => p_rec.object_version_number
4953: );
4954: hr_utility.set_location(l_proc, 30);
4955: --
4956: -- Validate country.
4957: --
4958: chk_country
4960: ,p_style => p_rec.style
4961: ,p_address_id => p_rec.address_id
4962: ,p_object_version_number => p_rec.object_version_number
4963: );
4964: hr_utility.set_location(l_proc, 40);
4965: --
4966: -- Validate address type.
4967: --
4968: chk_address_type
4971: ,p_date_from => p_rec.date_from
4972: ,p_effective_date => p_effective_date
4973: ,p_object_version_number => p_rec.object_version_number
4974: );
4975: hr_utility.set_location(l_proc, 50);
4976: --
4977: -- Validate primary flag
4978: --
4979: chk_primary_flag
4980: (p_address_id => p_rec.address_id
4981: ,p_object_version_number => p_rec.object_version_number
4982: ,p_primary_flag => p_rec.primary_flag
4983: );
4984: hr_utility.set_location(l_proc, 50);
4985: --
4986: --
4987: --
4988: if not g_called_from_form then
5002: ,p_party_id => p_rec.party_id -- HR/TCA merge
5003: );
5004: end if;
5005: --
5006: hr_utility.set_location(l_proc, 60);
5007: --
5008: -- Validate postal code.
5009: --
5010: chk_postal_code
5014: ,p_business_group_id => p_rec.business_group_id
5015: ,p_object_version_number => p_rec.object_version_number
5016: ,p_town_or_city => p_rec.town_or_city
5017: );
5018: hr_utility.set_location(l_proc, 70);
5019: --
5020: -- Validate tax address zip.
5021: --
5022: If p_rec.style = 'US' and
5027: ,p_tax_address_zip => p_rec.add_information20
5028: ,p_business_group_id => p_rec.business_group_id
5029: ,p_object_version_number => p_rec.object_version_number
5030: );
5031: hr_utility.set_location(l_proc, 71);
5032: End if;
5033: --
5034: -- Validate date to.
5035: --
5038: ,p_date_from => p_rec.date_from
5039: ,p_date_to => p_rec.date_to
5040: ,p_object_version_number => p_rec.object_version_number
5041: );
5042: hr_utility.set_location(l_proc, 80);
5043: --
5044: --
5045: -- Validation specific to GB, US, GENERIC
5046: --
5059: ,p_effective_date => p_effective_date
5060: ,p_object_version_number => p_rec.object_version_number
5061: ,p_validate_county => p_validate_county
5062: );
5063: hr_utility.set_location(l_proc, 90);
5064: If p_rec.style = 'US' and
5065: p_rec.primary_flag = 'Y' then
5066: chk_tax_county
5067: (p_address_id => p_rec.address_id
5070: ,p_business_group_id => p_rec.business_group_id
5071: ,p_effective_date => p_effective_date
5072: ,p_object_version_number => p_rec.object_version_number
5073: );
5074: hr_utility.set_location(l_proc, 91);
5075: End if;
5076: --
5077: -- Validate region 2.
5078: --
5083: ,p_business_group_id => p_rec.business_group_id
5084: ,p_effective_date => p_effective_date
5085: ,p_object_version_number => p_rec.object_version_number
5086: );
5087: hr_utility.set_location(l_proc, 100);
5088: --
5089: -- Validate tax_state(add_information17).
5090: --
5091: If p_rec.style = 'US' and
5097: ,p_business_group_id => p_rec.business_group_id
5098: ,p_effective_date => p_effective_date
5099: ,p_object_version_number => p_rec.object_version_number
5100: );
5101: hr_utility.set_location(l_proc, 105);
5102: End if;
5103: --
5104: -- Validate town or city.
5105: --
5109: ,p_town_or_city => p_rec.town_or_city
5110: ,p_business_group_id => p_rec.business_group_id
5111: ,p_object_version_number => p_rec.object_version_number
5112: );
5113: hr_utility.set_location(l_proc, 110);
5114: --
5115: -- Validate tax_city.
5116: --
5117: If p_rec.style = 'US' and
5122: ,p_tax_city => p_rec.add_information18
5123: ,p_business_group_id => p_rec.business_group_id
5124: ,p_object_version_number => p_rec.object_version_number
5125: );
5126: hr_utility.set_location(l_proc, 115);
5127: End if;
5128: --
5129: -- Validate address_line 1.
5130: --
5133: ,p_style => p_rec.style
5134: ,p_address_line1 => p_rec.address_line1
5135: ,p_object_version_number => p_rec.object_version_number
5136: );
5137: hr_utility.set_location(l_proc, 120);
5138: --
5139: -- Check null attributes for address style.
5140: --
5141: chk_style_null_attr
5145: ,p_region_2 => p_rec.region_2
5146: ,p_region_3 => p_rec.region_3
5147: ,p_telephone_number_3 => p_rec.telephone_number_3
5148: );
5149: hr_utility.set_location(l_proc, 130);
5150: --
5151: -- This is only applicable if payroll is installed under US legislation
5152: -- and address style is 'US'.
5153: -- Validate city(town_or_city) and state(region_2)
5162: ,p_town_or_city => p_rec.town_or_city
5163: ,p_business_group_id => p_rec.business_group_id
5164: ,p_object_version_number => p_rec.object_version_number
5165: );
5166: hr_utility.set_location(l_proc, 140);
5167: --
5168: If p_rec.style = 'US' and
5169: p_rec.primary_flag = 'Y' then
5170: chk_tax_city_state_zip_comb
5176: ,p_tax_city => p_rec.add_information18
5177: ,p_business_group_id => p_rec.business_group_id
5178: ,p_object_version_number => p_rec.object_version_number
5179: );
5180: hr_utility.set_location(l_proc, 145);
5181: End if;
5182: /* Bug 1677965
5183: elsif p_rec.style = 'JP' then
5184: hr_utility.set_location(l_proc, 150);
5180: hr_utility.set_location(l_proc, 145);
5181: End if;
5182: /* Bug 1677965
5183: elsif p_rec.style = 'JP' then
5184: hr_utility.set_location(l_proc, 150);
5185: --
5186: -- Check the combination checking for town_or_city(district_code)
5187: -- address_line1, and region_1
5188: --
5193: ,p_town_or_city => p_rec.town_or_city
5194: ,p_address_line1 => p_rec.address_line1
5195: ,p_region_1 => p_rec.region_1
5196: );
5197: hr_utility.set_location(l_proc, 160);
5198: --
5199: -- Validate region_2 according to address_line2
5200: --
5201: chk_address2_region2_comb
5203: ,p_object_version_number => p_rec.object_version_number
5204: ,p_address_line2 => p_rec.address_line2
5205: ,p_region_2 => p_rec.region_2
5206: );
5207: hr_utility.set_location(l_proc, 170);
5208: --
5209: -- Validate region_3 according to address_line3
5210: --
5211: chk_address3_region3_comb
5213: ,p_object_version_number => p_rec.object_version_number
5214: ,p_address_line3 => p_rec.address_line3
5215: ,p_region_3 => p_rec.region_3
5216: );
5217: hr_utility.set_location(l_proc, 180);
5218: */
5219: --
5220: -- DDF Validation other than GB, US, JP and GENERIC
5221: --
5219: --
5220: -- DDF Validation other than GB, US, JP and GENERIC
5221: --
5222: else
5223: hr_utility.set_location(l_proc, 185);
5224: --
5225: -- Validate the DDF
5226: --
5227: chk_ddf
5235: chk_df
5236: (p_rec => p_rec
5237: );
5238: --
5239: hr_utility.set_location(' Leaving:'||l_proc, 190);
5240: End update_validate;
5241: --
5242: -- ----------------------------------------------------------------------------
5243: -- |---------------------------< delete_validate >----------------------------|
5246: --
5247: l_proc varchar2(72) := g_package||'delete_validate';
5248: --
5249: Begin
5250: hr_utility.set_location('Entering:'||l_proc, 5);
5251: --
5252: -- Call all supporting business operations. Mapping to the
5253: -- appropriate business rules on peradd.bru is provided
5254: --
5257: -- as the primary address
5258: --
5259: chk_del_address;
5260: --
5261: hr_utility.set_location(' Leaving:'||l_proc, 10);
5262: End delete_validate;
5263: --
5264: -- ---------------------------------------------------------------------------
5265: -- |---------------------< return_legislation_code >-------------------------|
5283: --
5284: l_legislation_code varchar2(150);
5285: l_proc varchar2(72) := g_package||'return_legislation_code';
5286: begin
5287: hr_utility.set_location('Entering:'|| l_proc, 10);
5288: --
5289: -- Ensure that all the mandatory parameter are not null
5290: --
5291: hr_api.mandatory_arg_error(p_api_name => l_proc,
5298: -- call to this function. Just return the value in the global
5299: -- variable.
5300: --
5301: l_legislation_code := g_legislation_code;
5302: hr_utility.set_location(l_proc, 20);
5303: else
5304: --
5305: -- The ID is different to the last call to this function
5306: -- or this is the first call to this function.
5317: return null;
5318: --
5319: -- The primary key is invalid therefore we must error
5320: --
5321: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5322: hr_utility.set_location(l_proc, 30);
5323: hr_utility.raise_error;
5324: end if;
5325: --
5318: --
5319: -- The primary key is invalid therefore we must error
5320: --
5321: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5322: hr_utility.set_location(l_proc, 30);
5323: hr_utility.raise_error;
5324: end if;
5325: --
5326: close csr_leg_code;
5319: -- The primary key is invalid therefore we must error
5320: --
5321: hr_utility.set_message(801, 'HR_7220_INVALID_PRIMARY_KEY');
5322: hr_utility.set_location(l_proc, 30);
5323: hr_utility.raise_error;
5324: end if;
5325: --
5326: close csr_leg_code;
5327: --
5326: close csr_leg_code;
5327: --
5328: g_address_id:= p_address_id;
5329: g_legislation_code := l_legislation_code;
5330: hr_utility.set_location(' Leaving:'|| l_proc, 40);
5331: end if;
5332: return l_legislation_code;
5333: end return_legislation_code;
5334: --