1 PACKAGE BODY HZ_GEO_GET_PUB AS
2 /* $Header: ARHGEGEB.pls 120.12 2008/02/26 10:31:46 nshinde ship $ */
3
4 PROCEDURE get_zone
5 (p_location_table_name IN VARCHAR2,
6 p_location_id IN VARCHAR2,
7 p_zone_type IN VARCHAR2,
8 p_date IN DATE,
9 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10 x_zone_tbl OUT NOCOPY zone_tbl_type,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2) IS
14
15 l_ref_geo_type hz_geography_types_b.geography_type%TYPE;
16 l_count NUMBER;
17 l_gnr_count NUMBER;
18 l_geography_id NUMBER;
19 l_postal_code VARCHAR2(360);
20 l_postal_code_range_flag VARCHAR2(1);
21 l_geography_use hz_geography_types_b.geography_use%TYPE;
22
23 CURSOR included_geo_types(p_zone_type IN VARCHAR2) IS
24 SELECT object_type
25 FROM hz_relationship_types
26 WHERE subject_type = p_zone_type
27 AND status = 'A'
28 AND relationship_type = 'TAX';
29
30
31 CURSOR c_get_zones(c_object_type IN VARCHAR2, c_geography_id IN NUMBER) IS
32 SELECT subject_id
33 FROM hz_relationships
34 WHERE subject_type = p_zone_type
35 AND subject_table_name = 'HZ_GEOGRAPHIES'
36 AND object_type = c_object_type
37 AND object_id = c_geography_id
38 AND object_table_name = 'HZ_GEOGRAPHIES'
39 AND relationship_type = 'TAX'
40 AND directional_flag = 'F'
41 AND relationship_code = 'PARENT_OF'
42 AND p_date BETWEEN start_date and end_date;
43
44 l_zone_id number;
45 l_zone_tbl zone_tbl_type;
46 l_zone_tbl_final zone_tbl_type;
47 i number;
48 j number;
49 l_populate_tbl varchar2(1);
50 l_tbl_count number;
51 l_country_found VARCHAR2(1);
52 l_country_code VARCHAR2(80);
53
54 BEGIN
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56 i := 1;
57
58 -- Validate zone type
59
60 -- Bug 6842648 Perf fix to query
61 SELECT count(1)
62 INTO l_count
63 FROM hz_geography_types_b
64 WHERE geography_type = p_zone_type
65 AND geography_use <> 'MASTER_REF'
66 AND rownum = 1;
67
68 IF l_count = 0 THEN
69 x_return_status := FND_API.G_RET_STS_ERROR;
70 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_ZONE_TYPE_INVALID');
71 FND_MSG_PUB.ADD;
72 RAISE FND_API.G_EXC_ERROR;
73 END IF;
74
75 -- Validate that the zone type has associated reference
76 -- geography types
77
78 BEGIN
79 -- Bug 6842648 Perf fix to query
80 SELECT count(1)
81 INTO l_count
82 FROM hz_relationship_types
83 WHERE subject_type = p_zone_type
84 AND status = 'A'
85 AND rownum = 1;
86 END;
87
88 IF l_count = 0 THEN
89 x_return_status := FND_API.G_RET_STS_ERROR;
90 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_ASSOC_GEO_TYPES');
91 FND_MESSAGE.SET_TOKEN('GEO_TYPE', p_zone_type);
92 FND_MSG_PUB.ADD;
93 RAISE FND_API.G_EXC_ERROR;
94 END IF;
95
96 -- To get country_code for the location
97 BEGIN
98 IF p_location_table_name = 'HZ_LOCATIONS' THEN
99 SELECT country
100 INTO l_country_code
101 FROM hz_locations
102 WHERE location_id = p_location_id;
103 ELSIF p_location_table_name = 'HR_LOCATIONS_ALL' THEN
104 SELECT country
105 INTO l_country_code
106 FROM hr_locations
107 WHERE location_id = p_location_id;
108 END IF;
109 EXCEPTION WHEN others THEN
110 l_country_code := NULL;
111 END;
112
113 -- Make a check that geo name referencing has been
114 -- done for this location
115 -- Bug 6842648 Perf fix to query
116 SELECT count(1)
117 INTO l_gnr_count
118 FROM hz_geo_name_references
119 WHERE location_table_name = p_location_table_name
120 AND location_id = p_location_id
121 AND rownum = 1;
122
123 -- Below code is commenmted for bug # 5011582
124 -- Will display the message after the end loop of included_geo_types
125 -- IF l_count = 0 THEN
126 -- x_return_status := FND_API.G_RET_STS_ERROR;
127 -- FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_GEO_NAME_REF');
128 -- FND_MSG_PUB.ADD;
129 -- RAISE FND_API.G_EXC_ERROR;
130 -- END IF;
131
132 l_country_found := 'N';
133
134 -- Find zone
135
136 OPEN included_geo_types(p_zone_type);
137 LOOP
138 FETCH included_geo_types INTO l_ref_geo_type;
139 EXIT WHEN included_geo_types%NOTFOUND;
140
141 IF l_ref_geo_type = 'COUNTRY' THEN
142 l_country_found := 'Y';
143 END IF;
144
145 -- find the geography id from hz_geo_name_references
146 BEGIN
147 SELECT geography_id
148 INTO l_geography_id
149 FROM hz_geo_name_references
150 WHERE location_table_name = p_location_table_name
151 AND location_id = p_location_id
152 AND geography_type = l_ref_geo_type;
153
154 EXCEPTION WHEN NO_DATA_FOUND THEN
155 l_geography_id := NULL;
156
157 -- if l_ref_geo_type is COUNTRY get geography_id from hz_geograpies
158 IF l_ref_geo_type = 'COUNTRY' THEN
159 SELECT geography_id
160 INTO l_geography_id
161 FROM hz_geographies
162 WHERE country_code = l_country_code
163 AND geography_type = l_ref_geo_type
164 -- Bug 5410283 (Added on 07-Aug-2006)
165 AND p_date BETWEEN start_date AND end_date;
166 END IF;
167
168 END;
169
170 IF l_geography_id IS NOT NULL THEN
171
172 OPEN c_get_zones(l_ref_geo_type,l_geography_id);
173 LOOP
174 FETCH c_get_zones INTO l_zone_id;
175 EXIT WHEN c_get_zones%NOTFOUND;
176 l_zone_tbl(i).zone_id := l_zone_id;
177
178 IF l_zone_id IS NOT NULL THEN
179 SELECT postal_code_range_flag, geography_use
180 INTO l_postal_code_range_flag, l_geography_use
181 FROM hz_geography_types_b
182 WHERE geography_type = p_zone_type;
183
184
185 -- Postal code range flag is applicable only for eTax
186 -- if geography_use is not TAX then return the zone id
187
188 IF l_geography_use = 'TAX' THEN
189 IF l_postal_code_range_flag = 'N' THEN
190 -- return the zone_id
191 -- EXIT;
192 null;
193 ELSE
194
195 -- Bug 4639558 return the zone
196 -- if no range exists for the master_ref_geography
197 -- for that zone.
198
199 -- Bug 6842648 Perf fix to query
200 SELECT count(1) INTO l_count
201 FROM hz_geography_ranges
202 WHERE master_ref_geography_id = l_geography_id
203 AND geography_id = l_zone_id
204 AND geography_type = p_zone_type
205 AND geography_use = 'TAX'
206 AND rownum = 1;
207
208 IF l_count > 0 THEN
209
210 -- Removed PO_VENDOR_SITES_ALL from the below if condition. Bug # 4584465
211
212 IF p_location_table_name = 'HR_LOCATIONS_ALL' THEN
213
214 SELECT postal_code
215 INTO l_postal_code
216 FROM hr_locations_all
217 WHERE location_id = p_location_id;
218
219 ELSIF p_location_table_name = 'HZ_LOCATIONS' THEN
220
221 SELECT postal_code
222 INTO l_postal_code
223 FROM hz_locations
224 WHERE location_id = p_location_id;
225
226 END IF;
227
228 -- Bug 6842648 Perf fix to query
229 SELECT count(1)
230 INTO l_count
231 FROM hz_geography_ranges
232 WHERE geography_id = l_zone_id
233 AND master_ref_geography_id = l_geography_id
234 AND l_postal_code between geography_from and geography_to
235 AND p_date between start_date and end_date
236 AND rownum = 1;
237
238 IF l_count = 0 THEN
239 -- If count = 0,
240 l_zone_id := NULL;
241 l_zone_tbl(i).zone_id := l_zone_id;
242 i := i-1;
243 END IF;
244
245 ELSE -- no range record found
246 NULL; -- continue in the loop
247 END IF;
248
249 END IF; --postal code range flag
250
251 ELSE -- geography use not tax
252 NULL; -- continue in the loop to find out more zones can be found
253 END IF; -- geography use
254
255 ELSE -- x_zone_id is null
256 NULL; -- continue in the loop
257 END IF;
258 i := i+1;
259 END LOOP;
260 CLOSE c_get_zones;
261 ELSE -- l_geography_id is null
262 NULL; -- continue in the loop
263 END IF;
264
265 END LOOP;
266
267 -- If there is COUNTRY in included_geo_types and no GNR
268 IF (l_country_found = 'N' AND
269 l_gnr_count = 0) THEN
270 x_return_status := FND_API.G_RET_STS_ERROR;
271 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_GEO_NAME_REF');
272 FND_MSG_PUB.ADD;
273 RAISE FND_API.G_EXC_ERROR;
274 END IF;
275
276 IF l_zone_tbl.COUNT > 0 THEN
277 i := l_zone_tbl.FIRST;
278 LOOP
279 IF l_zone_tbl(i).zone_id IS NOT NULL THEN
280 IF l_zone_tbl_final.COUNT > 0 THEN
281 j := l_zone_tbl_final.FIRST;
282 LOOP
283 IF l_zone_tbl(i).zone_id = l_zone_tbl_final(j).zone_id then
284 l_populate_tbl := 'N';
285 EXIT;
286 ELSE
287 l_populate_tbl := 'Y';
288 END IF;
289 EXIT WHEN j = l_zone_tbl_final.LAST;
290 j := l_zone_tbl_final.NEXT(j);
291 END LOOP;
292 IF l_populate_tbl = 'Y' THEN
293 l_tbl_count := l_zone_tbl_final.COUNT;
294 SELECT geography_id, geography_code, geography_name
295 INTO l_zone_tbl_final(l_tbl_count+1).zone_id,
296 l_zone_tbl_final(l_tbl_count+1).zone_code,
297 l_zone_tbl_final(l_tbl_count+1).zone_name
298 FROM hz_geographies
299 WHERE geography_id = l_zone_tbl(i).zone_id
300 -- Bug 5410283 (Added on 07-Aug-2006)
301 AND p_date BETWEEN start_date AND end_date;
302 l_zone_tbl_final(l_tbl_count+1).zone_type := p_zone_type;
303 END IF;
304 ELSE
305 SELECT geography_id, geography_code, geography_name
306 INTO l_zone_tbl_final(i).zone_id, l_zone_tbl_final(i).zone_code, l_zone_tbl_final(i).zone_name
307 FROM hz_geographies
308 WHERE geography_id = l_zone_tbl(i).zone_id
309 -- Bug 5410283 (Added on 07-Aug-2006)
310 AND p_date BETWEEN start_date AND end_date;
311
312 l_zone_tbl_final(i).zone_type := p_zone_type;
313 END IF;
314 END IF;
315 EXIT WHEN i = l_zone_tbl.LAST;
316 i := l_zone_tbl.NEXT(i);
317 END LOOP;
318 END IF;
319
320 x_zone_tbl := l_zone_tbl_final;
321
322 EXCEPTION
323 WHEN FND_API.G_EXC_ERROR THEN
324 x_return_status := FND_API.G_RET_STS_ERROR;
325 FND_MSG_PUB.Count_And_Get(
326 p_encoded => FND_API.G_FALSE,
327 p_count => x_msg_count,
328 p_data => x_msg_data);
329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
331 FND_MSG_PUB.Count_And_Get(
332 p_encoded => FND_API.G_FALSE,
333 p_count => x_msg_count,
334 p_data => x_msg_data);
335
336 WHEN OTHERS THEN
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
338 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
339 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
340 FND_MSG_PUB.ADD;
341 FND_MSG_PUB.Count_And_Get(
342 p_encoded => FND_API.G_FALSE,
343 p_count => x_msg_count,
344 p_data => x_msg_data);
345
346 END get_zone;
347
348 Function get_conc_name(l_geo_id IN NUMBER)
349 return VARCHAR2 is
350 l_display_val VARCHAR2(3650);
351 l_country_id NUMBER;
352 l_geo_type VARCHAR2(30);
353 l_col VARCHAR2(100);
354 l_clause VARCHAR2(1000) := '';
355
356 TYPE ConcGeoName IS REF CURSOR;
357 conc_geo ConcGeoName;
358
359 cursor g_structure is
360 SELECT geography_element_column
361 FROM hz_geo_structure_levels hgsl
362 START WITH PARENT_GEOGRAPHY_TYPE = 'COUNTRY'
363 and hgsl.geography_id = l_country_id
364 CONNECT BY PRIOR hgsl.GEOGRAPHY_TYPE = hgsl.PARENT_GEOGRAPHY_TYPE
365 and hgsl.geography_id = l_country_id
366 and parent_geography_type <> l_geo_type;
367
368 Begin
369 Begin
370 select geography_element1_id, geography_type into l_country_id, l_geo_type
371 from hz_geographies
372 where geography_id = l_geo_id;
373 Exception
374 When no_data_found then
375 return hz_utility_v2pub.Get_LookupMeaning('AR_LOOKUPS', 'HZ_GEO_DISPLAY_ONLY', 'WORLD');
376 end;
377
378 l_clause := 'nvl2(geography_element1, geography_element1, geography_name)';
379
380 open g_structure;
381 loop
382 fetch g_structure into l_col;
383 EXIT WHEN g_structure%NOTFOUND;
384 l_clause := l_clause || '|| nvl2('||l_col||', '':''|| '||l_col||', '''')';
385 end loop;
386 close g_structure;
387
388 OPEN conc_geo FOR
389 'select '||l_clause||' from hz_geographies where geography_id = :1' USING l_geo_id;
390 fetch conc_geo into l_display_val;
391 close conc_geo;
392 return l_display_val;
393 end get_conc_name;
394 END;
[Home] [Help]