[Home] [Help]
Skip to content
PACKAGE BODY: APPS.WSH_GEOCODING
Source
1 PACKAGE BODY WSH_GEOCODING AS
2 /*$Header: WSHGEOCB.pls 120.2 2006/09/06 06:27:30 skattama noship $*/
3
4 /*======================================================================+
5 | PROCEDURE |
6 | Get_Lat_Long_and_TimeZone |
7 | |
8 | DESCRIPTION |
9 | Get Lat, Long, Geometry and Timezone code given the |
10 | address element. |
11 | |
12 | |
13 | ARGUMENTS : IN: |
14 | p_api_version |
15 | p_init_msg_list |
16 | OUT: |
17 | x_msg_count |
18 | x_msg_data |
19 | x_return_status |
20 | IN/ OUT: |
21 | l_location |
22 | |
23 | NOTES: |
24 | The following hierarchy is used to get the latitude, longitude, |
25 | timezone code and geometry for a location |
26 | 1. City and Zip code |
27 | 2. Zip code |
28 | 3. City and County |
29 | 4. City and State |
30 | 5. County and State |
31 | 6. State |
32 | |
33 | MODIFICATION HISTORY |
34 | jnhuang - Mar 10, 2003 |
35 | Initial creation |
36 | musriniv - Aug 1, 2003 |
37 | Modified API to use WSH_LOCATIONS RECORD type |
38 | musriniv - Sep 20, 2003 |
39 | Modified based on table definition from NavTech |
40 | |
41 +---------------------------------------------------------------------*/
42
43 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_GEOCODING';
44
45 Procedure Get_Lat_Long_and_TimeZone(p_api_version IN NUMBER
46 , p_init_msg_list IN VARCHAR2 default fnd_api.g_false
47 , x_return_status OUT NOCOPY VARCHAR2
48 , x_msg_count OUT NOCOPY VARCHAR2
49 , x_msg_data OUT NOCOPY VARCHAR2
50 , l_location IN OUT NOCOPY WSH_LOCATIONS_PKG.LOCATION_REC_TYPE
51 ) is
52
53 x_latitude NUMBER;
54 x_longitude NUMBER;
55 x_dst_flag VARCHAR2(1);
56 x_gmt_offset NUMBER;
57 x_begin_dst_month VARCHAR2(3);
58 x_begin_dst_day NUMBER;
59 x_begin_dst_week_of_month NUMBER;
60 x_begin_dst_day_of_week NUMBER;
61 x_begin_dst_hour NUMBER;
62 x_end_dst_month VARCHAR2(3);
63 x_end_dst_day NUMBER;
64 x_end_dst_week_of_month NUMBER;
65 x_end_dst_day_of_week NUMBER;
66 x_end_dst_hour NUMBER;
67 x_timezone_id NUMBER;
68 x_timezone_name VARCHAR2(50);
69 x_timezone_code VARCHAR2(50);
70 x_geometry MDSYS.SDO_GEOMETRY;
71 x_record_found BOOLEAN := FALSE;
72
73 --
74 l_debug_on BOOLEAN;
75 --
76 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_Lat_Long_and_TimeZone';
77
78 p_SqlErrM VARCHAR2(2400);
79
80 BEGIN
81
82 x_return_status := NULL;
83 x_msg_count := NULL;
84 x_msg_data := NULL;
85
86 --
87 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
88 --
89 IF l_debug_on IS NULL
90 THEN
91 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
92 END IF;
93 --
94 --
95 -- Debug Statements
96 --
97 IF l_debug_on THEN
98 WSH_DEBUG_SV.push(l_module_name);
99 WSH_DEBUG_SV.log(l_module_name,'WSH_LOCATION_ID',l_location.WSH_LOCATION_ID);
100 WSH_DEBUG_SV.log(l_module_name,'LOCATION_SOURCE_CODE',l_location.LOCATION_SOURCE_CODE);
101 WSH_DEBUG_SV.log(l_module_name,'COUNTRY',l_location.COUNTRY);
102 WSH_DEBUG_SV.log(l_module_name,'PROVINCE',l_location.PROVINCE);
103 WSH_DEBUG_SV.log(l_module_name,'STATE',l_location.STATE);
104 WSH_DEBUG_SV.log(l_module_name,'COUNTY',l_location.COUNTY);
105 WSH_DEBUG_SV.log(l_module_name,'CITY',l_location.CITY);
106 WSH_DEBUG_SV.log(l_module_name,'POSTAL_CODE',l_location.POSTAL_CODE);
107 WSH_DEBUG_SV.log(l_module_name,'LATITUDE',l_location.LATITUDE);
108 WSH_DEBUG_SV.log(l_module_name,'LONGITUDE',l_location.LONGITUDE);
109 WSH_DEBUG_SV.log(l_module_name,'TIMEZONE_CODE',l_location.TIMEZONE_CODE);
110 END IF;
111
112 IF (l_location.postal_code IS NOT NULL) THEN
113
114 IF (l_location.city IS NOT NULL) THEN
115 BEGIN
116 x_record_found := TRUE;
117
118
119 SELECT trunc(c.zip_centroid.sdo_point.y, 5),
120 trunc(c.zip_centroid.sdo_point.x, 5),
121 c.dst_indicator, c.gmt_offset,
122 --to_char( c.dst_start_date, 'MM'),
123 to_char( to_number( to_char( c.dst_start_date, 'MM') ) ),
124 to_number( to_char(c.dst_start_date, 'DD') ),
125 to_number( to_char(c.dst_start_date, 'W') ),
126 to_number( to_char(c.dst_start_date, 'D') ),
127 to_number( to_char(c.dst_start_date, 'HH24') ),
128 to_char( c.dst_end_date, 'MM'),
129 to_number( to_char(c.dst_end_date, 'DD') ),
130 to_number( to_char(c.dst_end_date, 'W') ),
131 to_number( to_char(c.dst_end_date, 'D') ),
132 to_number( to_char(c.dst_end_date, 'HH24') ),
133 c.zip_centroid
134 INTO x_latitude, x_longitude, x_dst_flag,
138 x_end_dst_month, x_end_dst_day,
135 x_gmt_offset, x_begin_dst_month,
136 x_begin_dst_day, x_begin_dst_week_of_month,
137 x_begin_dst_day_of_week, x_begin_dst_hour,
139 x_end_dst_week_of_month, x_end_dst_day_of_week,
140 x_end_dst_hour, x_geometry
141 FROM wsh_location_data_ext c
142 WHERE c.ZIP_CODE = upper(l_location.postal_code) AND
143 c.CITY = upper(l_location.city);
144
145
146
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 x_record_found := FALSE;
150 END;
151
152 END IF;
153 IF ( (l_location.city IS NULL) OR (NOT x_record_found ) ) THEN
154 --ELSIF ( (l_location.city IS NULL) OR (NOT x_record_found ) ) THEN
155 BEGIN
156 x_record_found := TRUE;
157
158
159 SELECT trunc(c.zip_centroid.sdo_point.y, 5),
160 trunc(c.zip_centroid.sdo_point.x, 5),
161 c.dst_indicator, c.gmt_offset,
162 --to_char( c.dst_start_date, 'MM'),
163 to_char( to_number(to_char(c.dst_start_date, 'MM') ) ),
164 to_number( to_char(c.dst_start_date, 'DD') ),
165 to_number( to_char(c.dst_start_date, 'W') ),
166 to_number( to_char(c.dst_start_date, 'D') ),
167 to_number( to_char(c.dst_start_date, 'HH24') ),
168 to_char( c.dst_end_date, 'MM'),
169 to_number( to_char(c.dst_end_date, 'DD') ),
170 to_number( to_char(c.dst_end_date, 'W') ),
171 to_number( to_char(c.dst_end_date, 'D') ),
172 to_number( to_char(c.dst_end_date, 'HH24') ),
173 c.zip_centroid
174 INTO x_latitude, x_longitude, x_dst_flag,
175 x_gmt_offset, x_begin_dst_month,
176 x_begin_dst_day, x_begin_dst_week_of_month,
177 x_begin_dst_day_of_week, x_begin_dst_hour,
178 x_end_dst_month, x_end_dst_day,
179 x_end_dst_week_of_month, x_end_dst_day_of_week,
180 x_end_dst_hour, x_geometry
181 FROM wsh_location_data_ext c
182 WHERE c.ZIP_CODE = upper(l_location.postal_code);
183
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 x_record_found := FALSE;
187 END;
188
189 END IF;
190
191 END IF;
192 IF ( (l_location.postal_code IS NULL) AND (l_location.city IS NOT NULL) AND (NOT x_record_found ) ) THEN
193 --ELSIF ( (l_location.city IS NOT NULL) AND (NOT x_record_found ) ) THEN
194
195 IF (l_location.county IS NOT NULL) THEN
196 BEGIN
197 x_record_found := TRUE;
198
199 -- We use AVG of latitude and longitude here since
200 -- there will be multiple entries in NavTech for a
201 -- city/county combination. The average will get the
202 -- centroid
203
204
205 SELECT trunc(AVG(c.zip_centroid.sdo_point.y), 5),
206 trunc(AVG(c.zip_centroid.sdo_point.x), 5),
207 MAX(c.dst_indicator), MAX(c.gmt_offset),
208 --MAX( to_char( c.dst_start_date, 'MM') ),
209 MAX( to_char( to_number( to_char( c.dst_start_date, 'MM') ) ) ),
210 MAX( to_number( to_char(c.dst_start_date, 'DD') ) ),
211 MAX( to_number( to_char(c.dst_start_date, 'W') ) ),
212 MAX( to_number( to_char(c.dst_start_date, 'D') ) ),
213 MAX( to_number( to_char(c.dst_start_date, 'HH24') ) ),
214 MAX( to_char( c.dst_end_date, 'MM') ),
215 MAX( to_number( to_char(c.dst_end_date, 'DD') ) ),
216 MAX( to_number( to_char(c.dst_end_date, 'W') ) ),
220 x_gmt_offset, x_begin_dst_month,
217 MAX( to_number( to_char(c.dst_end_date, 'D') ) ),
218 MAX( to_number( to_char(c.dst_end_date, 'HH24') ) )
219 INTO x_latitude, x_longitude, x_dst_flag,
221 x_begin_dst_day, x_begin_dst_week_of_month,
222 x_begin_dst_day_of_week, x_begin_dst_hour,
223 x_end_dst_month, x_end_dst_day,
224 x_end_dst_week_of_month, x_end_dst_day_of_week,
225 x_end_dst_hour
226 FROM wsh_location_data_ext c
227 WHERE c.CITY = upper(l_location.city) AND
228 c.COUNTY = upper(l_location.county);
229
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 x_record_found := FALSE;
233 END;
234
235 END IF;
236 --ELSIF ( (l_location.county IS NULL AND l_location.state IS NOT NULL) OR
237 IF ( (l_location.county IS NULL AND l_location.state IS NOT NULL) OR
238 (NOT x_record_found ) ) THEN
239 BEGIN
240 x_record_found := TRUE;
241
242 -- We use AVG of latitude and longitude here since
243 -- there will be multiple entries in NavTech for a
244 -- city/state combination. The average will get the
245 -- centroid
246
247
248 SELECT trunc(AVG(c.zip_centroid.sdo_point.y), 5),
249 trunc(AVG(c.zip_centroid.sdo_point.x), 5),
250 MAX(c.dst_indicator), MAX(c.gmt_offset),
251 --MAX( to_char( c.dst_start_date, 'MM') ),
252 MAX( to_char( to_number( to_char( c.dst_start_date, 'MM') ) ) ),
253 MAX( to_number( to_char(c.dst_start_date, 'DD') ) ),
254 MAX( to_number( to_char(c.dst_start_date, 'W') ) ),
255 MAX( to_number( to_char(c.dst_start_date, 'D') ) ),
256 MAX( to_number( to_char(c.dst_start_date, 'HH24') ) ),
257 MAX( to_char( c.dst_end_date, 'MM') ),
258 MAX( to_number( to_char(c.dst_end_date, 'DD') ) ),
259 MAX( to_number( to_char(c.dst_end_date, 'W') ) ),
260 MAX( to_number( to_char(c.dst_end_date, 'D') ) ),
261 MAX( to_number( to_char(c.dst_end_date, 'HH24') ) )
262 INTO x_latitude, x_longitude, x_dst_flag,
263 x_gmt_offset, x_begin_dst_month,
264 x_begin_dst_day, x_begin_dst_week_of_month,
268 x_end_dst_hour
265 x_begin_dst_day_of_week, x_begin_dst_hour,
266 x_end_dst_month, x_end_dst_day,
267 x_end_dst_week_of_month, x_end_dst_day_of_week,
269 FROM wsh_location_data_ext c
270 WHERE c.CITY = upper(l_location.city) AND
271 c.STATE = upper(l_location.state);
272
273 EXCEPTION
274 WHEN NO_DATA_FOUND THEN
275 x_record_found := FALSE;
276 END;
277
278 END IF;
279
280 END IF;
281 --ELSIF ( (l_location.state IS NOT NULL) AND (NOT x_record_found ) ) THEN
282 IF ( (l_location.postal_code IS NULL) AND (l_location.city IS NULL) AND (l_location.state IS NOT NULL) AND (NOT x_record_found ) ) THEN
283
284 IF (l_location.county IS NOT NULL) THEN
285 BEGIN
286 x_record_found := TRUE;
287
288 -- We use AVG of latitude and longitude here since
289 -- there will be multiple entries in NavTech for a
290 -- county/state combination. The average will get the
291 -- centroid
292
293
294 SELECT trunc(AVG(c.zip_centroid.sdo_point.y), 5),
295 trunc(AVG(c.zip_centroid.sdo_point.x), 5),
296 MAX(c.dst_indicator), MAX(c.gmt_offset),
297 --MAX( to_char( c.dst_start_date, 'MM') ),
298 MAX( to_char( to_number( to_char( c.dst_start_date, 'MM') ) ) ),
299 MAX( to_number( to_char(c.dst_start_date, 'DD') ) ),
300 MAX( to_number( to_char(c.dst_start_date, 'W') ) ),
301 MAX( to_number( to_char(c.dst_start_date, 'D') ) ),
302 MAX( to_number( to_char(c.dst_start_date, 'HH24') ) ),
303 MAX( to_char( c.dst_end_date, 'MM') ),
304 MAX( to_number( to_char(c.dst_end_date, 'DD') ) ),
305 MAX( to_number( to_char(c.dst_end_date, 'W') ) ),
306 MAX( to_number( to_char(c.dst_end_date, 'D') ) ),
307 MAX( to_number( to_char(c.dst_end_date, 'HH24') ) )
308 INTO x_latitude, x_longitude, x_dst_flag,
309 x_gmt_offset, x_begin_dst_month,
313 x_end_dst_week_of_month, x_end_dst_day_of_week,
310 x_begin_dst_day, x_begin_dst_week_of_month,
311 x_begin_dst_day_of_week, x_begin_dst_hour,
312 x_end_dst_month, x_end_dst_day,
314 x_end_dst_hour
315 FROM wsh_location_data_ext c
316 WHERE c.STATE = upper(l_location.state) AND
317 c.COUNTY = upper(l_location.county);
318
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321 x_record_found := FALSE;
322 END;
323
324 END IF;
325 --ELSIF ( (l_location.county IS NULL) OR (NOT x_record_found) ) THEN
326 IF ( (l_location.county IS NULL) OR (NOT x_record_found) ) THEN
327 BEGIN
328 x_record_found := TRUE;
329
330 -- We use AVG of latitude and longitude here since
331 -- there will be multiple entries in NavTech for a
332 -- state. The average will get the centroid
333
334
335 SELECT trunc(AVG(c.zip_centroid.sdo_point.y), 5),
336 trunc(AVG(c.zip_centroid.sdo_point.x), 5),
337 MAX(c.dst_indicator), MAX(c.gmt_offset),
338 --MAX( to_char( c.dst_start_date, 'MM') ),
339 MAX( to_char( to_number( to_char( c.dst_start_date, 'MM') ) ) ),
340 MAX( to_number( to_char(c.dst_start_date, 'DD') ) ),
341 MAX( to_number( to_char(c.dst_start_date, 'W') ) ),
342 MAX( to_number( to_char(c.dst_start_date, 'D') ) ),
343 MAX( to_number( to_char(c.dst_start_date, 'HH24') ) ),
344 MAX( to_char( c.dst_end_date, 'MM') ),
345 MAX( to_number( to_char(c.dst_end_date, 'DD') ) ),
346 MAX( to_number( to_char(c.dst_end_date, 'W') ) ),
347 MAX( to_number( to_char(c.dst_end_date, 'D') ) ),
348 MAX( to_number( to_char(c.dst_end_date, 'HH24') ) )
349 INTO x_latitude, x_longitude, x_dst_flag,
350 x_gmt_offset, x_begin_dst_month,
351 x_begin_dst_day, x_begin_dst_week_of_month,
352 x_begin_dst_day_of_week, x_begin_dst_hour,
353 x_end_dst_month, x_end_dst_day,
354 x_end_dst_week_of_month, x_end_dst_day_of_week,
355 x_end_dst_hour
356 FROM wsh_location_data_ext c
357 WHERE c.STATE = upper(l_location.state);
358
359 EXCEPTION
360 WHEN NO_DATA_FOUND THEN
361 x_record_found := FALSE;
362 END;
363
364 END IF;
365
366 END IF;
367
368
369 IF (x_record_found) THEN
370
371 IF l_debug_on THEN
372 WSH_DEBUG_SV.log(l_module_name,'Calling program unit HZ_TIMEZONE_PUB.Get_Primary_Zone');
373 END IF;
374
375 /* Following workaround is because of non availability of NAVTECH data yet */
376 x_begin_dst_hour := 2;
377 x_end_dst_hour := 2;
378
379 /* Following is a workaround untill HZ fixes their API, data */
380 x_begin_dst_week_of_month := 1;
381 x_end_dst_week_of_month := -1;
382 -- Bug 5490063. Added below IF condition. For invalid locations or location with no ext setup all the below parameters would be null
383 -- So need not call HZ_TIMEZONE_PUB.get_primary_zone
384 IF(
385 (x_gmt_offset IS NOT NULL) OR (x_dst_flag IS NOT NULL) OR (x_begin_dst_day IS NOT NULL) OR
386 (x_begin_dst_day_of_week IS NOT NULL) OR (x_end_dst_month IS NOT NULL) OR (x_end_dst_day_of_week IS NOT NULL)
387 ) THEN
388 HZ_TIMEZONE_PUB.Get_Primary_Zone (
389 p_api_version,
390 p_init_msg_list,
391 x_gmt_offset,
392 x_dst_flag,
393 x_begin_dst_month,
394 --x_begin_dst_day,
395 null,
396 x_begin_dst_week_of_month,
397 x_begin_dst_day_of_week,
398 x_begin_dst_hour,
399 x_end_dst_month,
400 --x_end_dst_day,
401 null,
402 x_end_dst_week_of_month,
403 x_end_dst_day_of_week,
404 x_end_dst_hour,
405 x_timezone_id,
406 x_timezone_name,
407 x_timezone_code,
408 x_return_status,
409 x_msg_count,
410 x_msg_data );
411 ELSE
412 IF l_debug_on THEN
413 WSH_DEBUG_SV.log(l_module_name,'No record found in wsh_location_data_ext');
414 END IF;
415 END IF;
416 l_location.latitude := x_latitude;
417 l_location.longitude := x_longitude;
418 l_location.timezone_code := x_timezone_code;
419 l_location.geometry := x_geometry;
420 IF l_debug_on THEN
421 WSH_DEBUG_SV.log(l_module_name,'After calling HZ API ');
422 WSH_DEBUG_SV.log(l_module_name,'latitude : ' || l_location.latitude);
423 WSH_DEBUG_SV.log(l_module_name,'longitude : ' || l_location.longitude);
424 WSH_DEBUG_SV.log(l_module_name,'timezone_code : ' || l_location.timezone_code);
425 END IF;
426
427 ELSE
428
429 IF l_debug_on THEN
430 WSH_DEBUG_SV.log(l_module_name,'No record found in wsh_location_data_ext');
431 END IF;
432
433 END IF;
434
435 IF l_debug_on THEN
436 WSH_DEBUG_SV.pop(l_module_name);
437 END IF;
438
439 EXCEPTION
440 WHEN NO_DATA_FOUND THEN
441 p_SqlErrM := sqlerrm||'(Could not find entry for Location)';
445
442 IF l_debug_on THEN
443 WSH_DEBUG_SV.pop(l_module_name);
444 END IF;
446 END Get_Lat_Long_and_TimeZone;
447
448
449 END WSH_GEOCODING;
450