4 PROCEDURE get_zone
1 PACKAGE BODY HZ_GEO_GET_PUB AS
2 /* $Header: ARHGEGEB.pls 120.18 2011/11/29 04:40:20 rgokavar ship $ */
3
8 p_date IN DATE,
5 (p_location_table_name IN VARCHAR2,
6 p_location_id IN VARCHAR2,
7 p_zone_type IN VARCHAR2,
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 l_debug_prefix VARCHAR2(30) := ' ';
55
56 BEGIN
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58 i := 1;
59
60
61 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
62 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' get_zone (+)',
63 p_msg_level=>fnd_log.level_procedure);
64 END IF;
65
66 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
67 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' Param p_location_table_name '||p_location_table_name
68 ||' p_location_id '||p_location_id
69 ||' p_zone_type '||p_zone_type
70 ||' p_date '||p_date,
71 p_msg_level=>fnd_log.level_statement);
72 END IF;
73
74
75 -- Validate zone type
76
77 -- Bug 6842648 Perf fix to query
78 SELECT count(1)
79 INTO l_count
80 FROM hz_geography_types_b
81 WHERE geography_type = p_zone_type
82 AND geography_use <> 'MASTER_REF'
83 AND rownum = 1;
84
85 IF l_count = 0 THEN
86 x_return_status := FND_API.G_RET_STS_ERROR;
87 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_ZONE_TYPE_INVALID');
88 FND_MSG_PUB.ADD;
89 RAISE FND_API.G_EXC_ERROR;
90 END IF;
91
92 -- Validate that the zone type has associated reference
93 -- geography types
94
95 BEGIN
96 -- Bug 6842648 Perf fix to query
97 SELECT count(1)
98 INTO l_count
99 FROM hz_relationship_types
100 WHERE subject_type = p_zone_type
101 AND status = 'A'
102 AND rownum = 1;
103 END;
104
105 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
106 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'For Zone type '||p_zone_type||' Relationship type count is '||l_count,
107 p_msg_level=>fnd_log.level_statement);
108 END IF;
109
110 IF l_count = 0 THEN
111 x_return_status := FND_API.G_RET_STS_ERROR;
112 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_ASSOC_GEO_TYPES');
113 FND_MESSAGE.SET_TOKEN('GEO_TYPE', p_zone_type);
114 FND_MSG_PUB.ADD;
115 RAISE FND_API.G_EXC_ERROR;
116 END IF;
117
118 -- To get country_code for the location
119 BEGIN
120 IF p_location_table_name = 'HZ_LOCATIONS' THEN
121 SELECT country
122 INTO l_country_code
123 FROM hz_locations
124 WHERE location_id = p_location_id;
125 ELSIF p_location_table_name = 'HR_LOCATIONS_ALL' THEN
126 SELECT country
127 INTO l_country_code
128 FROM hr_locations
129 WHERE location_id = p_location_id;
130 END IF;
131
132 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
133 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' Country Code for location '||p_location_id||' is '||l_country_code,
134 p_msg_level=>fnd_log.level_statement);
135 END IF;
136
137
138 EXCEPTION WHEN others THEN
139 l_country_code := NULL;
140 END;
141
142 -- Make a check that geo name referencing has been
143 -- done for this location
144 -- Bug 6842648 Perf fix to query
145 SELECT count(1)
146 INTO l_gnr_count
147 FROM hz_geo_name_references
148 WHERE location_table_name = p_location_table_name
152 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
149 AND location_id = p_location_id
150 AND rownum = 1;
151
153 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' For location '||p_location_id||' Geo Name References count is '||l_gnr_count,
154 p_msg_level=>fnd_log.level_statement);
155 END IF;
156
157 -- Below code is commenmted for bug # 5011582
158 -- Will display the message after the end loop of included_geo_types
159 -- IF l_count = 0 THEN
160 -- x_return_status := FND_API.G_RET_STS_ERROR;
161 -- FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_GEO_NAME_REF');
162 -- FND_MSG_PUB.ADD;
163 -- RAISE FND_API.G_EXC_ERROR;
164 -- END IF;
165
166 l_country_found := 'N';
167
168 -- Find zone
169
170 OPEN included_geo_types(p_zone_type);
171 LOOP
172 FETCH included_geo_types INTO l_ref_geo_type;
173 EXIT WHEN included_geo_types%NOTFOUND;
174
175 IF l_ref_geo_type = 'COUNTRY' THEN
176 l_country_found := 'Y';
177 END IF;
178
179 -- find the geography id from hz_geo_name_references
180 BEGIN
181 SELECT geography_id
182 INTO l_geography_id
183 FROM hz_geo_name_references
184 WHERE location_table_name = p_location_table_name
185 AND location_id = p_location_id
186 AND geography_type = l_ref_geo_type;
187
188 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
189 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'For geography_type '||l_ref_geo_type||' and Location '||
190 p_location_id||', Geography_id is '||l_geography_id,
191 p_msg_level=>fnd_log.level_statement);
192 END IF;
193
194 EXCEPTION WHEN NO_DATA_FOUND THEN
195 l_geography_id := NULL;
196
197 -- if l_ref_geo_type is COUNTRY get geography_id from hz_geograpies
198 IF l_ref_geo_type = 'COUNTRY' THEN
199 SELECT geography_id
200 INTO l_geography_id
201 FROM hz_geographies
202 WHERE country_code = l_country_code
203 AND geography_type = l_ref_geo_type
204 -- Bug 5410283 (Added on 07-Aug-2006)
205 AND p_date BETWEEN start_date AND end_date;
206 END IF;
207 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
208 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'In Exception blk - For geography_type '||l_ref_geo_type||' and Location '||
209 p_location_id||', Geography_id is '||l_geography_id,
210 p_msg_level=>fnd_log.level_exception);
211 END IF;
212
213 END;
214
215 IF l_geography_id IS NOT NULL THEN
216
217 OPEN c_get_zones(l_ref_geo_type,l_geography_id);
218 LOOP
219 FETCH c_get_zones INTO l_zone_id;
220 EXIT WHEN C_GET_ZONES%NOTFOUND;
221 l_zone_tbl(i).zone_id := l_zone_id;
222
223 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
224 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' For Geo Type '||l_ref_geo_type
225 ||' and Geograpphy Id '||l_geography_id
226 ||' Zone information :'||l_zone_id,
227 p_msg_level=>fnd_log.level_statement);
228 END IF;
229
230 IF l_zone_id IS NOT NULL THEN
231 SELECT postal_code_range_flag, geography_use
232 INTO l_postal_code_range_flag, l_geography_use
233 FROM hz_geography_types_b
234 WHERE geography_type = p_zone_type;
235
236 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
237 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' For Geography Type '||p_zone_type
238 ||' postal_code_range_flag is '||l_postal_code_range_flag
239 ||' and geography_use is '||l_geography_use,
240 p_msg_level=>fnd_log.level_statement);
241 END IF;
242
243 -- Postal code range flag is applicable only for eTax
244 -- if geography_use is not TAX then return the zone id
245
246 IF l_geography_use = 'TAX' THEN
247 IF l_postal_code_range_flag = 'N' THEN
248 -- return the zone_id
249 -- EXIT;
250 null;
251 ELSE
252
253 -- Bug 4639558 return the zone
254 -- if no range exists for the master_ref_geography
255 -- for that zone.
256
257 -- Bug 6842648 Perf fix to query
258 -- Bug 7837051 Commented Master_ref_geography_id Join
259 SELECT count(1) INTO l_count
260 FROM hz_geography_ranges
261 WHERE -- master_ref_geography_id = l_geography_id AND
262 geography_id = l_zone_id
263 AND geography_type = p_zone_type
264 AND geography_use = 'TAX'
265 AND rownum = 1;
266
267 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
268 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' For Geo Type : '||p_zone_type
269 ||' and Geograpphy Id '||l_zone_id||' GEOGRAPHY Ranges count is ' ||l_count,
270 p_msg_level=>fnd_log.level_statement);
271 END IF;
272 IF l_count > 0 THEN
273
277
274 -- Removed PO_VENDOR_SITES_ALL from the below if condition. Bug # 4584465
275
276 IF p_location_table_name = 'HR_LOCATIONS_ALL' THEN
278 SELECT postal_code
279 INTO l_postal_code
280 FROM hr_locations_all
281 WHERE location_id = p_location_id;
282
283 ELSIF p_location_table_name = 'HZ_LOCATIONS' THEN
284
285 SELECT postal_code
286 INTO l_postal_code
287 FROM hz_locations
288 WHERE location_id = p_location_id;
289
290 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
291 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' For Location '||p_location_id||' Postal Code is '||l_postal_code,
292 p_msg_level=>fnd_log.level_statement);
293 END IF;
294
295
296 END IF;
297 --Bug13427431
298 --Getting postal_code based on profile value for 'HZ_VAL_FIRST_5_DIGIT_US_ZIP'
299 l_postal_code := HZ_GNR_UTIL_PKG.postal_code_to_validate(l_country_code,l_postal_code);
300
301 -- Bug 6842648 Perf fix to query
302 -- Bug 7837051 Commented Master_ref_geography_id Join
303 SELECT count(1)
304 INTO l_count
305 FROM hz_geography_ranges
306 WHERE geography_id = l_zone_id
307 --AND master_ref_geography_id = l_geography_id
308 AND l_postal_code between geography_from and geography_to
309 AND p_date between start_date and end_date
310 AND rownum = 1;
311
312
313 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
314 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' For Geo Type : '||p_zone_type
315 ||' and Postal code range Geography Ranges count is ' ||l_count,
316 p_msg_level=>fnd_log.level_statement);
317 END IF;
318
319
320
321 IF l_count = 0 THEN
322 -- If count = 0,
323 l_zone_id := NULL;
324 l_zone_tbl(i).zone_id := l_zone_id;
325 i := i-1;
326 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
327 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' Making Zone_id NULL in array. ',
328 p_msg_level=>fnd_log.level_statement);
329 END IF;
330 END IF;
331
332 ELSE -- no range record found
333 NULL; -- continue in the loop
334 END IF;
335
336 END IF; --postal code range flag
337
338 ELSE -- geography use not tax
339 NULL; -- continue in the loop to find out more zones can be found
340 END IF; -- geography use
341
342 ELSE -- x_zone_id is null
343 NULL; -- continue in the loop
344 END IF;
345 i := i+1;
346 END LOOP;
347 CLOSE c_get_zones;
348 ELSE -- l_geography_id is null
349 NULL; -- continue in the loop
350 END IF;
351
352 END LOOP;
353
354 -- If there is COUNTRY in included_geo_types and no GNR
355 IF (l_country_found = 'N' AND
356 l_gnr_count = 0) THEN
357 x_return_status := FND_API.G_RET_STS_ERROR;
358 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_GEO_NAME_REF');
359 FND_MSG_PUB.ADD;
360 RAISE FND_API.G_EXC_ERROR;
361 END IF;
362
363 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
364 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' Array l_zone_tbl Count is '||l_zone_tbl.COUNT,
365 p_msg_level=>fnd_log.level_statement);
366 END IF;
367
368 IF l_zone_tbl.COUNT > 0 THEN
369 i := l_zone_tbl.FIRST;
370 LOOP
371 IF l_zone_tbl(i).zone_id IS NOT NULL THEN
372 IF l_zone_tbl_final.COUNT > 0 THEN
373 j := l_zone_tbl_final.FIRST;
374 LOOP
375 IF l_zone_tbl(i).zone_id = l_zone_tbl_final(j).zone_id then
376 l_populate_tbl := 'N';
377 EXIT;
378 ELSE
379 l_populate_tbl := 'Y';
380 END IF;
381 EXIT WHEN j = l_zone_tbl_final.LAST;
382 j := l_zone_tbl_final.NEXT(j);
383 END LOOP;
384
385 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
386 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' For '||l_zone_tbl(i).zone_id||' populate_tbl flag is '||l_populate_tbl,
387 p_msg_level=>fnd_log.level_statement);
388 END IF;
389
390 IF l_populate_tbl = 'Y' THEN
391 l_tbl_count := l_zone_tbl_final.COUNT;
392 BEGIN
393 SELECT geography_id, geography_code, geography_name
394 INTO l_zone_tbl_final(l_tbl_count+1).zone_id,
395 l_zone_tbl_final(l_tbl_count+1).zone_code,
396 l_zone_tbl_final(l_tbl_count+1).zone_name
397 FROM hz_geographies
398 WHERE geography_id = l_zone_tbl(i).zone_id
399 -- Bug 5410283 (Added on 07-Aug-2006)
400 AND p_date BETWEEN start_date AND end_date;
401 l_zone_tbl_final(l_tbl_count+1).zone_type := p_zone_type;
402
403 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
404 HZ_UTILITY_V2PUB.DEBUG(P_PREFIX=>L_DEBUG_PREFIX,P_MESSAGE=>' Array Index : '||TO_CHAR(L_TBL_COUNT+1)
405 ||' Zone Id '||TO_CHAR(L_ZONE_TBL_FINAL(L_TBL_COUNT+1).ZONE_ID)
406 ||' Zone Code '||TO_CHAR(L_ZONE_TBL_FINAL(L_TBL_COUNT+1).ZONE_CODE)
407 ||' Zone Name '||TO_CHAR(L_ZONE_TBL_FINAL(L_TBL_COUNT+1).ZONE_NAME)
408 ||' Zone Type '||TO_CHAR(L_ZONE_TBL_FINAL(L_TBL_COUNT+1).ZONE_TYPE)
409 , p_msg_level=>fnd_log.level_statement);
410 END IF;
411 EXCEPTION
412 WHEN NO_DATA_FOUND THEN
413 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
414 HZ_UTILITY_V2PUB.DEBUG(P_PREFIX=>L_DEBUG_PREFIX,P_MESSAGE=>'Geography information is missing for geography_id '||l_zone_tbl(i).zone_id
415 ||'. Either data does not exists or out of date range.'
416 , p_msg_level=>fnd_log.level_exception);
417 END IF;
418 WHEN OTHERS THEN
419 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
420 HZ_UTILITY_V2PUB.DEBUG(P_PREFIX=>L_DEBUG_PREFIX,P_MESSAGE=>'Geography information is missing for geography_id ERRROR - '||SQLERRM
421 , p_msg_level=>fnd_log.level_exception);
422 END IF;
423
424 END;
425 END IF;
426 ELSE
427 BEGIN
428 SELECT geography_id, geography_code, geography_name
429 INTO l_zone_tbl_final(i).zone_id, l_zone_tbl_final(i).zone_code, l_zone_tbl_final(i).zone_name
430 FROM hz_geographies
431 WHERE geography_id = l_zone_tbl(i).zone_id
432 -- Bug 5410283 (Added on 07-Aug-2006)
433 AND p_date BETWEEN start_date AND end_date;
434
435 l_zone_tbl_final(i).zone_type := p_zone_type;
436
437 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
438 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' First insert in l_zone_tbl_final ',
439 p_msg_level=>fnd_log.level_statement);
440 END IF;
441
442 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
443 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' Array Index : '||i
444 ||' Zone Id '||l_zone_tbl_final(i).zone_id
445 ||' Zone Code '||l_zone_tbl_final(i).zone_code
446 ||' Zone Name '||l_zone_tbl_final(i).zone_name,
447 p_msg_level=>fnd_log.level_statement);
448 END IF;
449 EXCEPTION
450 WHEN NO_DATA_FOUND THEN
451 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
452 HZ_UTILITY_V2PUB.DEBUG(P_PREFIX=>L_DEBUG_PREFIX,P_MESSAGE=>'Geography information is missing for geography_id '||l_zone_tbl(i).zone_id
453 ||'. Either data does not exists or out of date range.'
454 , p_msg_level=>fnd_log.level_exception);
455 END IF;
456 WHEN OTHERS THEN
457 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
458 HZ_UTILITY_V2PUB.DEBUG(P_PREFIX=>L_DEBUG_PREFIX,P_MESSAGE=>'Geography information is missing for geography_id ERRROR - '||SQLERRM
459 , p_msg_level=>fnd_log.level_exception);
460 END IF;
461 END;
462 END IF;
463 END IF;
464 EXIT WHEN i = l_zone_tbl.LAST;
465 i := l_zone_tbl.NEXT(i);
466 END LOOP;
467 END IF;
468
469 x_zone_tbl := l_zone_tbl_final;
470 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
471 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>' get_zone (-)',
472 p_msg_level=>fnd_log.level_procedure);
473 END IF;
474
475 EXCEPTION
476 WHEN FND_API.G_EXC_ERROR THEN
477 x_return_status := FND_API.G_RET_STS_ERROR;
478 FND_MSG_PUB.Count_And_Get(
479 p_encoded => FND_API.G_FALSE,
480 p_count => x_msg_count,
481 p_data => x_msg_data);
482 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
484 FND_MSG_PUB.Count_And_Get(
485 p_encoded => FND_API.G_FALSE,
486 p_count => x_msg_count,
487 p_data => x_msg_data);
488
489 WHEN OTHERS THEN
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
491 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
492 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
493 FND_MSG_PUB.ADD;
494 FND_MSG_PUB.Count_And_Get(
495 p_encoded => FND_API.G_FALSE,
496 p_count => x_msg_count,
497 p_data => x_msg_data);
498
499 END get_zone;
500
501 Function get_conc_name(l_geo_id IN NUMBER)
502 return VARCHAR2 is
503 l_display_val VARCHAR2(3650);
504 l_country_id NUMBER;
505 l_geo_type VARCHAR2(30);
506 l_col VARCHAR2(100);
507 l_clause VARCHAR2(1000) := '';
508
509 TYPE ConcGeoName IS REF CURSOR;
510 conc_geo ConcGeoName;
511
512 cursor g_structure is
513 SELECT geography_element_column
514 FROM hz_geo_structure_levels hgsl
515 START WITH PARENT_GEOGRAPHY_TYPE = 'COUNTRY'
516 and hgsl.geography_id = l_country_id
517 CONNECT BY PRIOR hgsl.GEOGRAPHY_TYPE = hgsl.PARENT_GEOGRAPHY_TYPE
518 and hgsl.geography_id = l_country_id
519 and parent_geography_type <> l_geo_type;
520
521 Begin
522 Begin
523 select geography_element1_id, geography_type into l_country_id, l_geo_type
524 from hz_geographies
525 where geography_id = l_geo_id;
526 Exception
527 When no_data_found then
528 return hz_utility_v2pub.Get_LookupMeaning('AR_LOOKUPS', 'HZ_GEO_DISPLAY_ONLY', 'WORLD');
529 end;
530
531 l_clause := 'nvl2(geography_element1, geography_element1, geography_name)';
532
533 open g_structure;
534 loop
535 fetch g_structure into l_col;
536 EXIT WHEN g_structure%NOTFOUND;
537 l_clause := l_clause || '|| nvl2('||l_col||', '':''|| '||l_col||', '''')';
538 end loop;
539 close g_structure;
540
541 OPEN conc_geo FOR
542 'select '||l_clause||' from hz_geographies where geography_id = :1' USING l_geo_id;
543 fetch conc_geo into l_display_val;
544 close conc_geo;
545 return l_display_val;
546 end get_conc_name;
547 END;