3: --
4: --
5: -- This procedure is used to flag out and shows the details
6: -- of the addresses which have invalid zip codes in both
7: -- HR_LOCATIONS_ALL and PER_ADDRESSES tables.
8: -- This script holds procedures and functions that are
9: -- used to display all the invalid existing addresses in the hr_location_all
10: -- and per_addresses. It produces a report of all the addresses which
11: -- contain invlid zip code. This is done by calling the inval_addr
6: -- of the addresses which have invalid zip codes in both
7: -- HR_LOCATIONS_ALL and PER_ADDRESSES tables.
8: -- This script holds procedures and functions that are
9: -- used to display all the invalid existing addresses in the hr_location_all
10: -- and per_addresses. It produces a report of all the addresses which
11: -- contain invlid zip code. This is done by calling the inval_addr
12: -- function.
13: --
14: PROCEDURE inval_per_addr ( p_address_id in number,
180: per.region_1,
181: per.region_2,
182: per.town_or_city,
183: per.postal_code
184: from per_addresses per;
185:
186: --
187: cursor hrzipcur is
188: select hr.location_id,
211:
212:
213: type peraddrow is record
214: (
215: address_id per_addresses.address_id%TYPE,
216: region_1 per_addresses.region_1%TYPE,
217: region_2 per_addresses.region_2%TYPE,
218: town_or_city per_addresses.town_or_city%TYPE,
219: postal_code per_addresses.postal_code%TYPE
212:
213: type peraddrow is record
214: (
215: address_id per_addresses.address_id%TYPE,
216: region_1 per_addresses.region_1%TYPE,
217: region_2 per_addresses.region_2%TYPE,
218: town_or_city per_addresses.town_or_city%TYPE,
219: postal_code per_addresses.postal_code%TYPE
220:
213: type peraddrow is record
214: (
215: address_id per_addresses.address_id%TYPE,
216: region_1 per_addresses.region_1%TYPE,
217: region_2 per_addresses.region_2%TYPE,
218: town_or_city per_addresses.town_or_city%TYPE,
219: postal_code per_addresses.postal_code%TYPE
220:
221: /* Commented to change the type of the variables
214: (
215: address_id per_addresses.address_id%TYPE,
216: region_1 per_addresses.region_1%TYPE,
217: region_2 per_addresses.region_2%TYPE,
218: town_or_city per_addresses.town_or_city%TYPE,
219: postal_code per_addresses.postal_code%TYPE
220:
221: /* Commented to change the type of the variables
222: address_id number(15),
215: address_id per_addresses.address_id%TYPE,
216: region_1 per_addresses.region_1%TYPE,
217: region_2 per_addresses.region_2%TYPE,
218: town_or_city per_addresses.town_or_city%TYPE,
219: postal_code per_addresses.postal_code%TYPE
220:
221: /* Commented to change the type of the variables
222: address_id number(15),
223: region_1 varchar2(70),