[Home] [Help]
PACKAGE BODY: APPS.HR_US_PYZIPCHK
Source
1 PACKAGE BODY HR_US_PYZIPCHK AS
2 /* $Header: pyzipchk.pkb 120.0 2005/05/29 10:37:14 appldev noship $ */
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
186 --
187 cursor hrzipcur is
188 select hr.location_id,
189 hr.region_1,
190 hr.region_2,
191 hr.town_or_city,
192 hr.postal_code
193 from hr_locations_all hr ;
194
195 type hrlocrow is record
196 (
197 location_id hr_locations_all.location_id%TYPE,
198 region_1 hr_locations_all.region_1%TYPE,
199 region_2 hr_locations_all.region_2%TYPE,
200 town_or_city hr_locations_all.town_or_city%TYPE,
201 postal_code hr_locations_all.postal_code%TYPE
202
203 /* commented to change the type
204 location_id number(15),
205 region_1 varchar2(70),
206 region_2 varchar2(70),
207 town_or_city varchar2(60),
208 postal_code varchar2(60)*/
209 );
210
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
220
221 /* Commented to change the type of the variables
222 address_id number(15),
223 region_1 varchar2(70),
224 region_2 varchar2(70),
225 town_or_city varchar2(30),
226 postal_code varchar2(30)*/
227
228 );
229
230 l_hrlocs hrlocrow;
231 l_peraddr peraddrow;
232
233 begin
234
235 open hrzipcur;
236 loop
237 fetch hrzipcur into l_hrlocs;
238 exit when hrzipcur%notfound;
239 --
240 --
241 begin
242 -- call the inval_hr_addr procedure.
243 inval_hr_addr (l_hrlocs.location_id
244 ,l_hrlocs.region_2
245 ,l_hrlocs.region_1
246 ,l_hrlocs.town_or_city
247 ,l_hrlocs.postal_code);
248 --
249 exception
250 when others then
251 hr_utility.oracle_error(sqlcode);
252 --
253 end;
254 end loop;
255 close hrzipcur;
256
257 --
258 --
259 -- Now get all the invalid per_ addresses
260 --
261
262 open perzipcur;
263 loop
264 fetch perzipcur into l_peraddr;
265 exit when perzipcur%notfound;
266 --
267 --
268 -- Call the inval_per_addr procedure
269
270 begin
271 inval_per_addr (l_peraddr.address_id
272 ,l_peraddr.region_2
273 ,l_peraddr.region_1
274 ,l_peraddr.town_or_city
275 ,l_peraddr.postal_code);
276
277 exception
278 when others then
279 hr_utility.oracle_error(sqlcode);
280
281 end;
282 end loop;
283 close perzipcur;
284
285 end chkzipcode;
286
287
288 end HR_US_PYZIPCHK;