1 PACKAGE BODY HR_US_PYZIPCHK AS
2 /* $Header: pyzipchk.pkb 120.0.12010000.2 2009/10/08 05:08:16 emunisek ship $ */
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
12 -- function.
13 --
14 PROCEDURE inval_per_addr ( p_address_id in number,
15 p_state_abbrev in varchar2 default null,
16 p_county_name in varchar2 default null,
17 p_city_name in varchar2 default null,
18 p_zip_code in varchar2 default null)
19
20 is
21
22 l_per_addr varchar2(11);
23 l_add_valid number;
24 --
25 cursor ca_address(p_city_name varchar2
26 ,p_county_name varchar2
27 ,p_zip_code varchar2 ) is
28
29 /* rmonge bug 4000003 */
30
31 select count(*)
32 from pay_us_counties co,
33 pay_ca_cities_v ct,
34 pay_us_zip_codes pc
35 where co.state_code = '70'
36 and co.county_code = ct.province_code
37 and co.county_code = pc.county_code
38 and pc.state_code = '70'
39 and ct.city_code = pc.city_code
40 and ct.city_name = p_city_name
41 and co.county_abbrev = p_county_name
42 and substr(p_zip_code,1,3) between substr(pc.zip_start,1,3) and substr(pc.zip_end,1,3)
43 and substr(p_zip_code,5,3) between '0A0' and '9Z9';
44
45 /* Commenting out this script as it has some performance problems.
46 select count(*)
47 from pay_ca_provinces_v pr
48 , pay_ca_cities_v ct
49 , pay_ca_postal_codes_v pc
50 where pr.province_code = ct.province_code
51 and pr.province_code = pc.province_code
52 and ct.city_code = pc.city_code
53 and ct.city_name = p_city_name
54 and pr.province_abbrev = p_county_name
55 and substr(p_zip_code,1,3) between substr(pc.code_start,1,3)
56 and substr(pc.code_end,1,3)
57 and substr(p_zip_code,5,3) between '0A0' and '9Z9';
58 */
59
60 --
61 begin
62 --hr_utility.trace('p_address_id: '||p_address_id);
63 --hr_utility.trace('p_state_abbrev: '||p_state_abbrev);
64 --hr_utility.trace('p_county_name: '||p_county_name);
65 --hr_utility.trace('p_city_name: '||p_city_name);
66 --hr_utility.trace('p_zip_code: '||p_zip_code);
67
68 IF length(p_zip_code) <> 7 THEN -- US addresses
69 l_per_addr := hr_us_ff_udfs.addr_val (p_state_abbrev
70 ,p_county_name
71 ,p_city_name
72 ,p_zip_code
73 ,'Y');
74 if (l_per_addr = '00-000-0000') then
75 insert into per_us_inval_addresses (address_id)
76 values (p_address_id);
77 commit;
78 end if;
79 ELSIF length(p_zip_code) = 7 THEN -- Canadian addresses
80 /* For Canada p_city_name = City Name,
81 p_county_name = Province Abbreviation,
82 p_zip_code = Postal Code */
83 OPEN ca_address(p_city_name, p_county_name, p_zip_code);
84 FETCH ca_address INTO l_add_valid;
85 IF l_add_valid = 0 THEN
86 insert into per_us_inval_addresses (address_id)
87 values (p_address_id);
88 commit;
89 END IF;
90 CLOSE ca_address;
91 END IF;
92
93 end inval_per_addr;
94
95
96 --
97 --
98 -- This procedure gets the invalid locations from hr_location_all
99 --
100
101 PROCEDURE inval_hr_addr (p_location_id in number,
102 p_state_abbrev in varchar2 default null,
103 p_county_name in varchar2 default null,
104 p_city_name in varchar2 default null,
105 p_zip_code in varchar2 default null)
106
107 is
108
109 l_hr_addr varchar2(11);
110 l_loc_valid number;
111 --
112 cursor ca_locations (p_city_name varchar2
113 ,p_county_name varchar2
114 ,p_zip_code varchar2) is
115
116 /* rmonge 4000003 */
117
118 select count(*)
119 from pay_us_counties co,
120 pay_ca_cities_v ct,
121 pay_us_zip_codes pc
122 where co.state_code = '70'
123 and co.county_code = ct.province_code
124 and co.county_code = pc.county_code
125 and pc.state_code = '70'
126 and ct.city_code = pc.city_code
127 and ct.city_name = p_city_name
128 and co.county_abbrev = p_county_name
129 and substr(p_zip_code,1,3) between substr(pc.zip_start,1,3) and substr(pc.zip_end,1,3)
130 and substr(p_zip_code,5,3) between '0A0' and '9Z9';
131 --
132 /*
133 select count(*)
134 from pay_ca_provinces_v pr
135 , pay_ca_cities_v ct
136 , pay_ca_postal_codes_v pc
137 where pr.province_code = ct.province_code
138 and pr.province_code = pc.province_code
139 and ct.city_code = pc.city_code
140 and ct.city_name = p_city_name
141 and pr.province_abbrev = p_county_name
142 and substr(p_zip_code,1,3) between substr(pc.code_start,1,3)
143 and substr(pc.code_end,1,3)
144 and substr(p_zip_code,5,3) between '0A0' and '9Z9';
145 */
146
147 begin
148 IF length(p_zip_code) <> 7 THEN
149 l_hr_addr := hr_us_ff_udfs.addr_val (p_state_abbrev
150 ,p_county_name
151 ,p_city_name
152 ,p_zip_code
153 ,'Y');
154 if (l_hr_addr = '00-000-0000') then
155 --
156 insert into per_us_inval_locations (location_id)
157 values (p_location_id);
158 commit;
159 end if;
160 ELSIF length(p_zip_code) = 7 THEN
161 /* For Canada p_city_name = City Name,
162 p_county_name = Province Abbreviation,
163 p_zip_code = Postal Code */
164 OPEN ca_locations(p_city_name, p_county_name, p_zip_code);
165 FETCH ca_locations INTO l_loc_valid;
166 IF l_loc_valid = 0 THEN
167 insert into per_us_inval_locations (location_id)
168 values (p_location_id);
169 commit;
170 END IF;
171 CLOSE ca_locations;
172 END IF;
173
174 end inval_hr_addr;
175
176
177 PROCEDURE chkzipcode is
178 cursor perzipcur is
179 select per.address_id,
180 per.region_1,
181 per.region_2,
182 per.town_or_city,
183 per.postal_code
184 from per_addresses per
185 where per.style in ('US','CA') or per.country in ('US','CA') ;--Added for Bug#8982883
186
187 --
188 cursor hrzipcur is
189 select hr.location_id,
190 hr.region_1,
191 hr.region_2,
192 hr.town_or_city,
193 hr.postal_code
194 from hr_locations_all hr
195 where hr.style in ('US','CA') or hr.country in ('US','CA') ;--Added for Bug#8982883
196
197 type hrlocrow is record
198 (
199 location_id hr_locations_all.location_id%TYPE,
200 region_1 hr_locations_all.region_1%TYPE,
201 region_2 hr_locations_all.region_2%TYPE,
202 town_or_city hr_locations_all.town_or_city%TYPE,
203 postal_code hr_locations_all.postal_code%TYPE
204
205 /* commented to change the type
206 location_id number(15),
207 region_1 varchar2(70),
208 region_2 varchar2(70),
209 town_or_city varchar2(60),
210 postal_code varchar2(60)*/
211 );
212
213
214
215 type peraddrow is record
216 (
217 address_id per_addresses.address_id%TYPE,
218 region_1 per_addresses.region_1%TYPE,
219 region_2 per_addresses.region_2%TYPE,
220 town_or_city per_addresses.town_or_city%TYPE,
221 postal_code per_addresses.postal_code%TYPE
222
223 /* Commented to change the type of the variables
224 address_id number(15),
225 region_1 varchar2(70),
226 region_2 varchar2(70),
227 town_or_city varchar2(30),
228 postal_code varchar2(30)*/
229
230 );
231
232 l_hrlocs hrlocrow;
233 l_peraddr peraddrow;
234
235 begin
236
237 open hrzipcur;
238 loop
239 fetch hrzipcur into l_hrlocs;
240 exit when hrzipcur%notfound;
241 --
242 --
243 begin
244 -- call the inval_hr_addr procedure.
245 inval_hr_addr (l_hrlocs.location_id
246 ,l_hrlocs.region_2
247 ,l_hrlocs.region_1
248 ,l_hrlocs.town_or_city
249 ,l_hrlocs.postal_code);
250 --
251 exception
252 when others then
253 hr_utility.oracle_error(sqlcode);
254 --
255 end;
256 end loop;
257 close hrzipcur;
258
259 --
260 --
261 -- Now get all the invalid per_ addresses
262 --
263
264 open perzipcur;
265 loop
266 fetch perzipcur into l_peraddr;
267 exit when perzipcur%notfound;
268 --
269 --
270 -- Call the inval_per_addr procedure
271
272 begin
273 inval_per_addr (l_peraddr.address_id
274 ,l_peraddr.region_2
275 ,l_peraddr.region_1
276 ,l_peraddr.town_or_city
277 ,l_peraddr.postal_code);
278
279 exception
280 when others then
281 hr_utility.oracle_error(sqlcode);
282
283 end;
284 end loop;
285 close perzipcur;
286
287 end chkzipcode;
288
289
290 end HR_US_PYZIPCHK;
[Home] [Help]