[Home] [Help]
PACKAGE BODY: APPS.ZX_TCM_GEO_JUR_PKG
Source
1 PACKAGE BODY ZX_TCM_GEO_JUR_PKG AS
2 /* $Header: zxcjurb.pls 120.42.12010000.1 2008/07/28 13:29:06 appldev ship $ */
3
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'ZX.PLSQL.ZX_TCM_GEO_JUR_PKG';
13
14 TYPE geography_id_type is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
15 l_jurisdiction_rec_tmp_tbl tax_jurisdiction_rec_tbl_type;
16
17 g_geography_use_info_tbl zx_global_Structures_pkg.geography_use_info_tbl_type;
18 -- this table is used in jurisdictions API to get geography types and uses for a given tax
19 -- and this structure is value for the whole session.
20
21 g_geo_name_references_tbl zx_global_structures_pkg.geo_name_references_tbl_type;
22 -- this table is used in get_zone API to get geo name reference for a given location_id
23 -- and this structure is value for the whole session.
24
25 PROCEDURE get_location_info(p_location_id IN NUMBER,
26 p_location_table_name IN VARCHAR2 default NULL,
27 x_location_info_rec OUT NOCOPY zx_global_Structures_pkg.loc_info_rec_type,
28 x_return_status OUT NOCOPY VARCHAR2);
29
30 PROCEDURE get_pos_loc_or_site(p_party_type IN VARCHAR2,
31 x_loc_tbl OUT NOCOPY VARCHAR2,
32 x_loc_site OUT NOCOPY VARCHAR2,
33 x_return_status OUT NOCOPY VARCHAR2) IS
34 -- Logging Infra
35 l_procedure_name CONSTANT VARCHAR2(30) := 'get_pos_loc_or_site';
36 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
37 l_party_type_tbl_idx binary_integer;
38 l_idx BINARY_INTEGER;
39
40 CURSOR c_get_party_type_info
41 IS
42 SELECT
43 PARTY_TYPE_CODE,
44 upper(PARTY_SOURCE_TABLE) PARTY_SOURCE_TABLE,
45 upper(PARTY_SOURCE_COLUMN)PARTY_SOURCE_COLUMN,
46 APPLICABLE_TO_EVNT_CLS_FLAG,
47 PARTY_SITE_TYPE,
48 upper(LOCATION_SOURCE_TABLE) LOCATION_SOURCE_TABLE,
49 upper(LOCATION_SOURCE_COLUMN) LOCATION_SOURCE_COLUMN
50 FROM
51 ZX_PARTY_TYPES;
52
53 BEGIN
54 -- Logging Infra: Setting up runtime level
55 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
56 -- Logging Infra: Procedure level
57 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
58 l_log_msg := l_procedure_name||'(+)';
59 l_log_msg := l_log_msg ||' B: SEL zx_party_types: in: p_party_type='||p_party_type;
60 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
61 END IF;
62
63 -- Initialize API return status to success.
64
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66
67 l_party_type_tbl_idx := dbms_utility.get_hash_value(p_party_type, 1, 8192);
68
69 IF (ZX_GLOBAL_STRUCTURES_PKG.ZX_PARTY_TYPES_CACHE.exists(l_party_type_tbl_idx)) THEN
70 x_loc_tbl := ZX_GLOBAL_STRUCTURES_PKG.ZX_PARTY_TYPES_CACHE(l_party_type_tbl_idx).LOCATION_SOURCE_TABLE;
71
72 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
73 l_log_msg := 'Found the party type information in cache for:'||p_party_type;
74 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
75 END IF;
76
77
78 ELSE
79 FOR c_party_type_rec IN c_get_party_type_info
80 LOOP
81
82 l_idx := dbms_utility.get_hash_value(c_party_type_rec.party_type_code, 1, 8192);
83
84 ZX_GLOBAL_STRUCTURES_PKG.ZX_PARTY_TYPES_CACHE(l_idx).PARTY_TYPE_CODE
85 := c_party_type_rec.PARTY_TYPE_CODE;
86 ZX_GLOBAL_STRUCTURES_PKG.ZX_PARTY_TYPES_CACHE(l_idx).PARTY_SOURCE_TABLE
87 := c_party_type_rec.PARTY_SOURCE_TABLE;
88 ZX_GLOBAL_STRUCTURES_PKG.ZX_PARTY_TYPES_CACHE(l_idx).PARTY_SOURCE_COLUMN
89 := c_party_type_rec.PARTY_SOURCE_COLUMN;
90 ZX_GLOBAL_STRUCTURES_PKG.ZX_PARTY_TYPES_CACHE(l_idx).APPLICABLE_TO_EVNT_CLS_FLAG
91 := c_party_type_rec.APPLICABLE_TO_EVNT_CLS_FLAG;
92 ZX_GLOBAL_STRUCTURES_PKG.ZX_PARTY_TYPES_CACHE(l_idx).PARTY_SITE_TYPE
93 := c_party_type_rec.PARTY_SITE_TYPE;
94 ZX_GLOBAL_STRUCTURES_PKG.ZX_PARTY_TYPES_CACHE(l_idx).LOCATION_SOURCE_TABLE
95 := c_party_type_rec.LOCATION_SOURCE_TABLE;
96 ZX_GLOBAL_STRUCTURES_PKG.ZX_PARTY_TYPES_CACHE(l_idx).LOCATION_SOURCE_COLUMN
97 := c_party_type_rec.LOCATION_SOURCE_COLUMN;
98 END LOOP;
99 x_loc_tbl := ZX_GLOBAL_STRUCTURES_PKG.ZX_PARTY_TYPES_CACHE(l_party_type_tbl_idx).LOCATION_SOURCE_TABLE;
100
101 END IF;
102
103
104 /* 3471450 -- SITE_ID changed to PARTY_SITE_ID */
105 IF x_loc_tbl = 'HZ_LOCATIONS' THEN
106 x_loc_site := 'LOCATION_ID';
107 ELSIF x_loc_tbl = 'HR_LOCATIONS_ALL' THEN
108 x_loc_site := 'LOCATION_ID';
109 ELSIF x_loc_tbl = 'PO_VENDOR_SITES_ALL' THEN
110 x_loc_site := 'PARTY_SITE_ID';
111 END IF;
112
113 -- Logging Infra: Statement level: "R" means "R"eturned value to a caller
114 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
115 l_log_msg := 'R: SEL zx_party_types: out: x_loc_tbl='||x_loc_tbl||', x_loc_site='||x_loc_site;
116 l_log_msg := l_log_msg ||' '||l_procedure_name||'(-)';
117 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
118 END IF;
119
120 EXCEPTION WHEN OTHERS THEN
121 -- Party type information could not be retrived.
122 x_return_status := FND_API.G_RET_STS_ERROR;
123 FND_MESSAGE.SET_NAME('ZX', 'ZX_TCM_NO_PARTY_TYPE_INFO');
124
125 -- Logging Infra: Statement level
126 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
127 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'E: EXC: OTHERS: '||SQLCODE||': '||SQLERRM);
128 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'E: EXC: OTHERS: p_party_type='||p_party_type);
129 END IF;
130
131 END get_pos_loc_or_site;
132
133 PROCEDURE check_location_exists(p_location_id IN NUMBER,
134 x_location_table_name IN OUT NOCOPY VARCHAR2) IS
135 l_hr_location_exists VARCHAR2(5);
136 l_hz_location_exists VARCHAR2(5);
137 l_procedure_name CONSTANT VARCHAR2(30) := 'check_location_exists';
138 BEGIN
139 IF x_location_table_name = 'HR_LOCATIONS_ALL' THEN
140 BEGIN
141 SELECT 'TRUE'
142 INTO l_hr_location_exists
143 FROM hr_locations_all
144 WHERE location_id = p_location_id;
145
146 EXCEPTION WHEN NO_DATA_FOUND THEN
147 l_hr_location_exists := 'FALSE';
148 END;
149 IF l_hr_location_exists = 'FALSE' THEN
150 BEGIN
151 SELECT 'TRUE'
152 INTO l_hz_location_exists
153 FROM hz_locations
154 WHERE location_id = p_location_id;
155
156 EXCEPTION WHEN NO_DATA_FOUND THEN
157 l_hz_location_exists := 'FALSE';
158 END;
159 IF l_hz_location_exists = 'TRUE' THEN
160 x_location_table_name := 'HZ_LOCATIONS';
161 ELSE
162 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
163 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'B: Invalid Location_id');
164 END IF;
165 END IF;
166 END IF;
167 ELSIF x_location_table_name = 'HZ_LOCATIONS' THEN
168 BEGIN
169 SELECT 'TRUE'
170 INTO l_hz_location_exists
171 FROM hz_locations
172 WHERE location_id = p_location_id;
173
174 EXCEPTION WHEN NO_DATA_FOUND THEN
175 l_hz_location_exists := 'FALSE';
176 END;
177 IF l_hz_location_exists = 'FALSE' THEN
178 BEGIN
179 SELECT 'TRUE'
180 INTO l_hr_location_exists
181 FROM hr_locations_all
182 WHERE location_id = p_location_id;
183
184 EXCEPTION WHEN NO_DATA_FOUND THEN
185 l_hr_location_exists := 'FALSE';
186 END;
187 IF l_hr_location_exists = 'TRUE' THEN
188 x_location_table_name := 'HR_LOCATIONS_ALL';
189 ELSE
190 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
191 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'B: Invalid Location_id');
192 END IF;
193 END IF;
194 END IF;
195 END IF;
196
197 END;
198
199 PROCEDURE get_location_info( p_location_id IN NUMBER,
200 p_location_table_name IN VARCHAR2 default NULL,
201 x_location_info_rec OUT NOCOPY zx_global_Structures_pkg.loc_info_rec_type,
202 x_return_status OUT NOCOPY VARCHAR2)
203 IS
204 l_loc_country_code hz_locations.country%type;
205 l_procedure_name CONSTANT VARCHAR2(30) := 'get_location_info';
206 l_log_msg VARCHAR2(2000);
207 BEGIN
208
209 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
210 l_log_msg := l_procedure_name||'(+)';
211 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name || '.begin', l_log_msg);
212 END IF;
213
214 x_return_status := FND_API.G_RET_STS_SUCCESS;
215 IF ZX_GLOBAL_STRUCTURES_PKG.Loc_info_tbl.exists(to_char(p_location_id)) then
216
217 x_location_info_rec := zx_global_Structures_pkg.loc_info_tbl(to_char(p_location_id));
218
219 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
220 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,
221 'Found the location info in cache for location id: '||p_location_id||' for table '||
222 x_location_info_rec.location_table_name);
223 END IF;
224
225 ELSE
226 IF p_location_table_name = 'HR_LOCATIONS_ALL' THEN
227 BEGIN
228 SELECT country
229 INTO l_loc_country_code
230 FROM hr_locations_all
231 WHERE location_id = p_location_id;
232
233 x_location_info_rec.country_code := l_loc_country_code;
234 x_location_info_rec.location_table_name := 'HR_LOCATIONS_ALL';
235 x_location_info_rec.location_id := p_location_id;
236
237 zx_global_Structures_pkg.loc_info_tbl(to_char(p_location_id)) := x_location_info_rec;
238
239 EXCEPTION WHEN NO_DATA_FOUND THEN
240
241 BEGIN
242 SELECT country
243 INTO l_loc_country_code
244 FROM hz_locations
245 WHERE location_id = p_location_id;
246
247 x_location_info_rec.country_code := l_loc_country_code;
248 x_location_info_rec.location_table_name := 'HZ_LOCATIONS';
249 x_location_info_rec. location_id := p_location_id;
250
251 zx_global_Structures_pkg.loc_info_tbl(to_char(p_location_id)) := x_location_info_rec;
252
253
254 EXCEPTION WHEN NO_DATA_FOUND THEN
255 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
256 l_log_msg := 'B: Invalid Location_id';
257 l_log_msg := l_log_msg ||' E: SEL country_code: no_data_found: p_location_id='||p_location_id;
258 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
259 END IF;
260
261 x_return_status := FND_API.G_RET_STS_ERROR;
262 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_LOC_REC');
263 FND_MESSAGE.SET_TOKEN('LOC_ID', p_location_id);
264 FND_MESSAGE.SET_TOKEN('TABLE_NAME', p_location_table_name);
265 RAISE FND_API.G_EXC_ERROR;
266 END;
267 END;
268
269 ELSIF p_location_table_name = 'HZ_LOCATIONS' THEN
270 BEGIN
271 SELECT country
272 INTO l_loc_country_code
273 FROM hz_locations
274 WHERE location_id = p_location_id;
275
276 x_location_info_rec.country_code := l_loc_country_code;
277 x_location_info_rec.location_table_name := 'HZ_LOCATIONS';
278 x_location_info_rec. location_id := p_location_id;
279
280 zx_global_Structures_pkg.loc_info_tbl(to_char(p_location_id)) := x_location_info_rec;
281
282 EXCEPTION WHEN NO_DATA_FOUND THEN
283
284 BEGIN
285 SELECT country
286 INTO l_loc_country_code
287 FROM hr_locations_all
288 WHERE location_id = p_location_id;
289
290 x_location_info_rec.country_code := l_loc_country_code;
291 x_location_info_rec.location_table_name := 'HR_LOCATIONS_ALL';
292 x_location_info_rec. location_id := p_location_id;
293
294 zx_global_Structures_pkg.loc_info_tbl(to_char(p_location_id)) := x_location_info_rec;
295
296
297 EXCEPTION WHEN NO_DATA_FOUND THEN
298 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
299 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'B: Invalid Location_id');
300 END IF;
301
302 -- Logging Infra: "E" means "E"rror
303 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
304 l_log_msg := 'E: SEL country_code: no_data_found: p_location_id='||p_location_id;
305 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
306 END IF;
307
308 x_return_status := FND_API.G_RET_STS_ERROR;
309 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_LOC_REC');
310 FND_MESSAGE.SET_TOKEN('LOC_ID', p_location_id);
311 FND_MESSAGE.SET_TOKEN('TABLE_NAME', p_location_table_name);
312 RAISE FND_API.G_EXC_ERROR;
313 END;
314 END;
315 END IF;
316 END IF; -- Loc_info_tbl.exists
317
318 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
319 l_log_msg := l_procedure_name||'(-)';
320 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME || l_procedure_name || '.end', l_log_msg);
321 END IF;
322
323 END get_location_info;
324
325 PROCEDURE get_location_table_name(p_location_type IN VARCHAR2,
326 x_location_table_name OUT NOCOPY VARCHAR2) IS
327 l_loc_site VARCHAR2(30);
328 x_return_status VARCHAR2(1);
329 -- Logging Infra
330 l_procedure_name CONSTANT VARCHAR2(30) := 'get_location_table_name';
331 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
332
333 BEGIN
334 -- Logging Infra: Statement level
335 -- No need to initialize g_current_runtime_level as it is done at the caller (get_zone or get_master_geo)
336 -- Logging Infra: Statement level: "B" means "B"reak point
337 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
338 l_log_msg := l_procedure_name||'(+)';
339 l_log_msg := l_log_msg || 'B: p_location_type= '||p_location_type;
340 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name || '.begin', l_log_msg);
341 END IF;
342
343 -- Logging Infra: Intentionally, not implementing log infra around get_pos_loc_or_site procedure
344 -- as it internally logs its messages.
345 IF p_location_type = 'SHIP_FROM' THEN
346 get_pos_loc_or_site(
347 zx_valid_init_params_pkg.source_rec.ship_from_party_type, x_location_table_name,
348 l_loc_site,
349 x_return_status);
350
351 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
352 RAISE FND_API.G_EXC_ERROR;
353 END IF;
354
355 ELSIF p_location_type = 'SHIP_TO' THEN
356 get_pos_loc_or_site(
357 zx_valid_init_params_pkg.source_rec.ship_to_party_type,
358 x_location_table_name,
359 l_loc_site,
360 x_return_status);
361
362 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
363 RAISE FND_API.G_EXC_ERROR;
364 END IF;
365
366 ELSIF p_location_type = 'BILL_FROM' THEN
367 get_pos_loc_or_site(
368 zx_valid_init_params_pkg.source_rec.bill_from_party_type, x_location_table_name,
369 l_loc_site,
370 x_return_status);
371 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374
375 ELSIF p_location_type = 'BILL_TO' THEN
376 get_pos_loc_or_site(
377 zx_valid_init_params_pkg.source_rec.bill_to_party_type,
378 x_location_table_name,
379 l_loc_site,
380 x_return_status);
381 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
382 RAISE FND_API.G_EXC_ERROR;
383 END IF;
384
385 ELSIF p_location_type = 'POA' THEN
386 get_pos_loc_or_site(
387 zx_valid_init_params_pkg.source_rec.poa_party_type,
388 x_location_table_name,
389 l_loc_site,
390 x_return_status);
391 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
392 RAISE FND_API.G_EXC_ERROR;
393 END IF;
394
395 ELSIF p_location_type = 'POO' THEN
396 get_pos_loc_or_site(
397 zx_valid_init_params_pkg.source_rec.poo_party_type,
398 x_location_table_name,
399 l_loc_site,
400 x_return_status);
401 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
402 RAISE FND_API.G_EXC_ERROR;
403 END IF;
404
405 ELSIF p_location_type = 'TRADING_HQ' THEN
406 get_pos_loc_or_site(
407 zx_valid_init_params_pkg.source_rec.trad_hq_party_type,
408 x_location_table_name,
409 l_loc_site,
410 x_return_status);
411 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
412 RAISE FND_API.G_EXC_ERROR;
413 END IF;
414
415 ELSIF p_location_type = 'OWN_HQ' THEN
416 get_pos_loc_or_site(
417 zx_valid_init_params_pkg.source_rec.own_hq_party_type, x_location_table_name,
418 l_loc_site,
419 x_return_status);
420 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
421 RAISE FND_API.G_EXC_ERROR;
422 END IF;
423
424 ELSIF p_location_type = 'TITLE_TRANS' THEN
425 get_pos_loc_or_site(
426 zx_valid_init_params_pkg.source_rec.ttl_trns_party_type,
427 x_location_table_name,
428 l_loc_site,
429 x_return_status);
430 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
431 RAISE FND_API.G_EXC_ERROR;
432 END IF;
433
434 ELSIF p_location_type = 'PAYING' THEN
435 get_pos_loc_or_site(
436 zx_valid_init_params_pkg.source_rec.paying_party_type,
437 x_location_table_name,
438 l_loc_site,
439 x_return_status);
440 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
441 RAISE FND_API.G_EXC_ERROR;
442 END IF;
443
444 ELSIF p_location_type = 'POC' THEN
445 /*get_pos_loc_or_site(
446 zx_valid_init_params_pkg.source_rec.ship_from_party_type, x_location_table_name,
447 l_loc_site,
448 x_return_status);
449 */
450 null;
451
452 ELSIF p_location_type = 'POI' THEN
453 get_pos_loc_or_site(
454 zx_valid_init_params_pkg.source_rec.poi_party_type,
455 x_location_table_name,
456 l_loc_site,
457 x_return_status);
458 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
459 RAISE FND_API.G_EXC_ERROR;
460 END IF;
461
462 ELSIF p_location_type = 'POD' THEN
463 get_pos_loc_or_site(
464 zx_valid_init_params_pkg.source_rec.pod_party_type,
465 x_location_table_name,
466 l_loc_site,
467 x_return_status);
468 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
469 RAISE FND_API.G_EXC_ERROR;
470 END IF;
471
472 ELSIF p_location_type = 'INTERNAL_ORG' THEN
473 x_location_table_name := 'HR_LOCATIONS_ALL';
474
475 END IF;
476
477 -- Logging Infra: Statement level:"R" means "R"eturned value to a caller
478 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
479 l_log_msg := 'R: x_location_table_name='||x_location_table_name;
480 l_log_msg := l_log_msg || l_procedure_name||'(-)';
481 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
482 END IF;
483
484 EXCEPTION
485
486 WHEN FND_API.G_EXC_ERROR THEN
487 x_return_status :=FND_API.G_RET_STS_ERROR;
488
489 END get_location_table_name;
490
491
492 PROCEDURE get_jurisdiction(p_tax IN VARCHAR2,
493 p_tax_regime_code IN VARCHAR2,
494 p_geography_id IN NUMBER,
495 p_date IN DATE,
496 p_inner_city_jurisdiction_flag IN VARCHAR2) IS
497 i BINARY_INTEGER;
498 -- Logging Infra
499 l_procedure_name CONSTANT VARCHAR2(30) := 'get_jurisdiction';
500 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
501 -----x_jurisdiction_rec_tmp_tbl tax_jurisdiction_rec_tbl_type;
502 CURSOR get_jur_info_c
503 ( c_tax VARCHAR2,
504 c_tax_regime_code VARCHAR2,
505 c_geography_id NUMBER,
506 c_date DATE,
507 c_inner_city_jurisdiction_flag VARCHAR2
508 )
509 IS
510 SELECT tax_jurisdiction_id,
511 tax_jurisdiction_code,
512 tax_regime_code,
513 tax,
514 precedence_level
515 FROM zx_jurisdictions_b
516 WHERE effective_from <= c_date
517 AND (effective_to >= c_date or effective_to is null)
518 AND tax = c_tax
519 AND tax_regime_code = c_tax_regime_code
520 AND zone_geography_id = c_geography_id
521 AND (((inner_city_jurisdiction_flag = c_inner_city_jurisdiction_flag) OR
522 (inner_city_jurisdiction_flag IS NULL AND
523 c_inner_city_jurisdiction_flag IS NULL)) OR
524 (inner_city_jurisdiction_flag is null and
525 c_inner_city_jurisdiction_flag is not null) OR
526 (inner_city_jurisdiction_flag is not null and c_inner_city_jurisdiction_flag is null));
527
528
529
530 BEGIN
531
532 -- Logging Infra: Setting up runtime level
533 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
534
535 -- Logging Infra: Procedure level
536 -- Logging Infra: Statement level: "B" means "B"reak point
537 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
538 l_log_msg := l_procedure_name||'(+)';
539 l_log_msg := l_log_msg ||' B: SEL zx_jurisdictions: in: p_tax='||p_tax||', p_date='||p_date||', p_tax_regime_code='||p_tax_regime_code||', p_geography_id='||p_geography_id;
540 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name || '.begin', l_log_msg);
541 END IF;
542
543 IF l_jurisdiction_rec_tmp_tbl.count = 0 THEN
544 i := 1;
545 ELSE
546 i := l_jurisdiction_rec_tmp_tbl.last + 1;
547 END IF;
548
549
550 OPEN get_jur_info_c
551 ( p_tax,
552 p_tax_regime_code,
553 p_geography_id,
554 p_date,
555 p_inner_city_jurisdiction_flag
556 );
557
558 FETCH get_jur_info_c INTO
559 l_jurisdiction_rec_tmp_tbl(i).tax_jurisdiction_id,
560 l_jurisdiction_rec_tmp_tbl(i).tax_jurisdiction_code,
561 l_jurisdiction_rec_tmp_tbl(i).tax_regime_code,
562 l_jurisdiction_rec_tmp_tbl(i).tax,
563 l_jurisdiction_rec_tmp_tbl(i).precedence_level;
564 CLOSE get_jur_info_c;
565
566
567 -- Logging Infra: Statement level: "R" means "R"eturned value to a caller
568 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
569 l_log_msg := 'R: SEL zx_jurisdictions: out: tax_jurisdiction_id='||l_jurisdiction_rec_tmp_tbl(i).tax_jurisdiction_id||
570 ', tax_jurisdiction_code='||l_jurisdiction_rec_tmp_tbl(i).tax_jurisdiction_code;
571
572 l_log_msg := l_log_msg ||' '||l_procedure_name||'(-)';
573 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
574 END IF;
575
576 EXCEPTION WHEN NO_DATA_FOUND THEN
577 NULL;
578 -- Logging Infra: Procedure level
579 ---- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
580 ---- l_log_msg := 'S: EXC: NO_DATA_FOUND: tax_jurisdiction_id='||g_jurisdiction_rec_tbl.tax_jurisdiction_id(i)||
581 ---- ', tax_jurisdiction_code='||g_jurisdiction_rec_tbl.tax_jurisdiction_code(i);
582 ---- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
583 ---- END IF;
584
585 END get_jurisdiction;
586
587 PROCEDURE get_zone
588 ( p_location_id IN NUMBER,
589 p_location_type IN VARCHAR2,
590 p_zone_type IN VARCHAR2,
591 p_trx_date IN DATE,
592 x_zone_tbl OUT NOCOPY HZ_GEO_GET_PUB.zone_tbl_type,
593 x_return_status OUT NOCOPY VARCHAR2) IS
594
595
596
597 CURSOR C_GEOGRAPHY_TYPES (c_geography_type in VARCHAR2) IS
598 SELECT geography_type,
599 geography_use,
600 limited_by_geography_id
601 FROM hz_geography_types_b
602 WHERE geography_type = c_geography_type;
603
604
605 x_location_table_name VARCHAR2(30);
606 -- Bug 4551957
607 l_count_geo NUMBER;
608 l_loc_country_code VARCHAR2(30);
609 l_zone_type_country_code VARCHAR2(30);
610
611 -- Logging Infra
612 l_procedure_name CONSTANT VARCHAR2(30) := 'get_zone';
613 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
614
615 -- The following 3 variables are added to make the external api (HZ_GEO_GET_PUB.get_zone)
616 -- calling place to compile. Needs to be removed later...
617 l_init_msg_list VARCHAR2(30);
618 x_msg_count NUMBER;
619 x_msg_data VARCHAR2(2000);
620 l_location_info_rec ZX_GLOBAL_STRUCTURES_PKG.loc_info_rec_type;
621
622 l_geography_type_index BINARY_INTEGER;
623 l_geography_type hz_geography_types_b.geography_type%TYPE;
624 l_geography_use hz_geography_types_b.geography_use%TYPE;
625 l_limited_by_geography_id hz_geography_types_b.limited_by_geography_id%TYPE;
626
627 l_tbl_index BINARY_INTEGER;
628 BEGIN
629 -- Logging Infra: Setting up runtime level
630 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
631 -- Logging Infra: Procedure level
632 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
633 l_log_msg := l_procedure_name||'(+)';
634 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name || '.begin', l_log_msg);
635 END IF;
636
637 -- Initialize API return status to success.
638
639 x_return_status := FND_API.G_RET_STS_SUCCESS;
640
641 -- Derive location table name based on location_type
642 IF ZX_GLOBAL_STRUCTURES_PKG.Loc_info_tbl.exists(to_char(p_location_id)) then
643
644 l_loc_country_code := ZX_GLOBAL_STRUCTURES_PKG.Loc_info_tbl(to_char(p_location_id)).country_code;
645 x_location_table_name := ZX_GLOBAL_STRUCTURES_PKG.Loc_info_tbl(to_char(p_location_id)).location_table_name;
646 ELSE
647
648 get_location_table_name(p_location_type,
649 x_location_table_name);
650
651 -- Logging Infra: Statement level: "B" means "B"reak point
652 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
653 l_log_msg := 'B: hz_geo_get_pub.get_zone: in: '||'p_location_table_name='||x_location_table_name||
654 ', p_location_id='||p_location_id||', p_zone_type=' ||p_zone_type||', p_date='||p_trx_date;
655 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
656 END IF;
657
658 get_location_info(p_location_id, x_location_table_name, l_location_info_rec, x_return_status);
659 x_location_table_name := ZX_GLOBAL_STRUCTURES_PKG.Loc_info_tbl(to_char(p_location_id)).location_table_name;
660
661 l_loc_country_code := l_location_info_rec.country_code;
662
663 END IF;
664
665 l_geography_type_index := dbms_utility.get_hash_value(p_zone_type, 1, 8192);
666
667 IF ZX_GLOBAL_STRUCTURES_PKG.g_geography_type_info_tbl.exists(l_geography_type_index) THEN
668 l_limited_by_geography_id :=
669 ZX_GLOBAL_STRUCTURES_PKG.g_geography_type_info_tbl(l_geography_type_index).limited_by_geography_id;
670
671 ELSE
672
673 open C_GEOGRAPHY_TYPES(p_zone_type);
674 fetch C_GEOGRAPHY_TYPES into
675 l_geography_type,
676 l_geography_use,
677 l_limited_by_geography_id;
678 close C_GEOGRAPHY_TYPES;
679
680 ZX_GLOBAL_STRUCTURES_PKG.g_geography_type_info_tbl(l_geography_type_index).geography_type := l_geography_type;
681 ZX_GLOBAL_STRUCTURES_PKG.g_geography_type_info_tbl(l_geography_type_index).geography_use := l_geography_use;
682 ZX_GLOBAL_STRUCTURES_PKG.g_geography_type_info_tbl(l_geography_type_index).limited_by_geography_id := l_limited_by_geography_id;
683
684 END IF;
685
686 IF l_limited_by_geography_id IS NOT NULL THEN
687 SELECT country_code
688 INTO l_zone_type_country_code
689 FROM hz_geographies
690 WHERE geography_id = l_limited_by_geography_id;
691 END IF;
692
693 IF l_limited_by_geography_id IS NULL OR
694 l_loc_country_code = l_zone_type_country_code THEN
695
696 IF l_limited_by_geography_id IS NOT NULL THEN
697 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
698 l_log_msg := 'R: Location country and Zone Type country are matching';
699 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
700 END IF;
701
702 -- check if geo name reference information is there in cache
703 l_tbl_index := dbms_utility.get_hash_value(
704 TO_CHAR(p_location_id),
705 1,
706 8192);
707
708 IF g_geo_name_references_tbl.EXISTS(l_tbl_index) then
709 -- The geo name reference information is there in cache.
710 -- No need to read from tables
711 IF (g_level_statement >= g_current_runtime_level ) THEN
712 FND_LOG.STRING(g_level_statement,
713 G_MODULE_NAME || l_procedure_name,
714 'Found geo name reference information in cache ');
715 END IF;
716 l_count_geo := g_geo_name_references_tbl(l_tbl_index).ref_count;
717
718 ELSE
719 IF (g_level_statement >= g_current_runtime_level ) THEN
720 FND_LOG.STRING(g_level_statement,
721 G_MODULE_NAME || l_procedure_name,
722 'Not found geo name reference information in cache, read from table');
723 END IF;
724 -- Bug 4551957
725 BEGIN
726 SELECT 1 into l_count_geo
727 FROM hz_geo_name_references
728 WHERE location_id = p_location_id
729 AND rownum = 1;
730 EXCEPTION WHEN NO_DATA_FOUND THEN
731 l_count_geo := 0;
732 END;
733 g_geo_name_references_tbl(l_tbl_index).ref_count := l_count_geo;
734
735 END IF;
736
737 IF l_count_geo = 0 THEN
738 x_return_status := FND_API.G_RET_STS_ERROR;
739 FND_MESSAGE.SET_NAME('ZX', 'ZX_GEO_NO_GEO_NAME_REF');
740 RAISE FND_API.G_EXC_ERROR;
741 END IF;
742 END IF;
743
744 -- Once table name is got, call TCA's get_zone API
745
746 HZ_GEO_GET_PUB.get_zone(
747 x_location_table_name,
748 p_location_id,
749 p_zone_type,
750 p_trx_date,
751 l_init_msg_list,
752 x_zone_tbl,
753 x_return_status,
754 x_msg_count,
755 x_msg_data);
756
757 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
758 ZX_API_PUB.G_EXTERNAL_API_CALL := 'Y';
759 END IF;
760
761 -- Logging Infra: Statement level: "R" means "R"eturned value to a caller
762 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
763 IF x_zone_tbl.count > 0 THEN
764 FOR i in x_zone_tbl.first..x_zone_tbl.last LOOP
765 l_log_msg := 'R: hz_geo_get_pub.get_zone: out: x_zone_id='||x_zone_tbl(i).zone_id||', x_zone_code='||x_zone_tbl(i).zone_code||
766 ', x_zone_name='||x_zone_tbl(i).zone_name||', x_return_status='||x_return_status;
767 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
768 END LOOP;
769 END IF;
770 END IF;
771 ELSE
772 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
773 l_log_msg := 'R: Location country and Zone Type country are not matching';
774 l_log_msg := l_log_msg ||' '|| l_procedure_name||'(-)';
775 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
776 END IF;
777
778 END IF;
779
780 EXCEPTION
781 WHEN FND_API.G_EXC_ERROR THEN
782 x_return_status := FND_API.G_RET_STS_ERROR;
783
784 -- Logging Infra: Statement level: "E" means "E"rror
785 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
786 IF x_zone_tbl.count > 0 THEN
787 FOR i in x_zone_tbl.first..x_zone_tbl.last LOOP
788 l_log_msg := 'E: EXC: FND_API.G_EXC_ERROR: x_zone_id='||x_zone_tbl(i).zone_id||', x_zone_code='||x_zone_tbl(i).zone_code||
789 ', x_zone_name='||x_zone_tbl(i).zone_name||', x_return_status='||x_return_status;
790 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
791 END LOOP;
792 END IF;
793 END IF;
794
795 WHEN OTHERS THEN
796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
797 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
798 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
799
800 IF C_GEOGRAPHY_TYPES%ISOPEN THEN
801 close C_GEOGRAPHY_TYPES;
802 END IF;
803
804 -- Logging Infra: Statement level: "E" means "E"rror
805 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
806 IF x_zone_tbl.count > 0 THEN
807 FOR i in x_zone_tbl.first..x_zone_tbl.last LOOP
808 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'E: EXC: OTHERS: '||SQLCODE||': '|| SQLERRM);
809 l_log_msg := 'E: EXC: OTHERS: x_zone_id='||x_zone_tbl(i).zone_id||', x_zone_code='||x_zone_tbl(i).zone_code||
810 ', x_zone_name='||x_zone_tbl(i).zone_name||', x_return_status='||x_return_status;
811 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
812 END LOOP;
813 END IF;
814 END IF;
815
816 END get_zone;
817
818
819
820 PROCEDURE get_tax_jurisdictions
821 ( p_location_id IN NUMBER,
822 p_location_type IN VARCHAR2,
823 p_tax IN VARCHAR2,
824 p_tax_regime_code IN VARCHAR2,
825 p_trx_date IN DATE,
826 x_tax_jurisdiction_rec OUT NOCOPY tax_jurisdiction_rec_type,
827 x_jurisdictions_found OUT NOCOPY VARCHAR2,
828 x_return_status OUT NOCOPY VARCHAR2) IS
829
830 x_geography_id NUMBER;
831 x_geography_code VARCHAR2(30);
832 x_geography_name VARCHAR2(360);
833 TYPE geography_type_use_type is TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
834 --TYPE geography_id_type is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
835 TYPE geography_code_type is TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
836 TYPE geography_name_type is TABLE OF VARCHAR2(360) INDEX BY BINARY_INTEGER;
837 TYPE geography_type_num_type is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
838 l_geography_id NUMBER;
839 l_geography_code VARCHAR2(30);
840 l_geography_name VARCHAR2(360);
841 l_tmp_count NUMBER;
842 l_tax_id NUMBER;
843 l_error_buffer VARCHAR2(200);
844
845
846 l_geography_type geography_type_use_type;
847 l_geography_use geography_type_use_type;
848 l_geography_type_num geography_type_num_type;
849 l_inner_city_jurisdiction_flag VARCHAR2(30);
850 l_zone_tbl HZ_GEO_GET_PUB.zone_tbl_type;
851 l_country_or_group_code VARCHAR2(30);
852 l_regime_country_code VARCHAR2(30);
853 l_loc_country_code VARCHAR2(30);
854 l_country_regime_flag VARCHAR2(1);
855 l_same_regime_loc_country VARCHAR2(1);
856 x_location_table_name VARCHAR2(30);
857 -- Logging Infra
858 l_procedure_name CONSTANT VARCHAR2(30) := 'get_tax_jurisdictions';
859 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
860 l_tax_rec ZX_TDS_UTILITIES_PKG.zx_tax_info_cache_rec;
861 l_tax_regime_rec zx_global_structures_pkg.tax_regime_rec_type;
862
863 l_tbl_index BINARY_INTEGER;
864 i NUMBER;
865 l_location_info_rec ZX_GLOBAL_STRUCTURES_PKG.loc_info_rec_type;
866
867 BEGIN
868
869 -- Logging Infra: Setting up runtime message level
870 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
871 -- Logging Infra: Procedure level
872 -- Logging Infra: Statement level: "B" means "B"reak point
873 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
874 l_log_msg := l_procedure_name||'(+)';
875 l_log_msg := l_log_msg ||' B: SEL hz_geography_types: in: p_tax='||p_tax||', p_tax_regime_code='||p_tax_regime_code;
876 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name || '.begin', l_log_msg);
877 END IF;
878
879
880 -- Initialize API return status to success.
881
882 x_return_status := FND_API.G_RET_STS_SUCCESS;
883
884 l_jurisdiction_rec_tmp_tbl.delete;
885 x_jurisdictions_found := 'N';
886
887 ZX_TDS_UTILITIES_PKG.get_regime_cache_info(
888 p_tax_regime_code,
889 p_trx_date,
890 l_tax_regime_rec,
891 x_return_status,
892 l_error_buffer);
893
894 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
895 IF (g_level_exception >= g_current_runtime_level ) THEN
896
897 l_log_msg := 'Incorrect return_status after calling ' ||
898 'ZX_TDS_UTILITIES_PKG.get_regime_cache_info
899 RETURN_STATUS = ' || x_return_status||' Error Buffer = '||l_error_buffer||
900 ', '||l_procedure_name||' (-)';
901
902 FND_LOG.STRING(g_level_exception,
903 G_MODULE_NAME || l_procedure_name||'.END',
904 l_log_msg);
905 END IF;
906 RETURN;
907 END IF;
908
909 --SELECT country_or_group_code, country_code
910 --INTO l_country_or_group_code, l_regime_country_code
911 --FROM zx_regimes_b
912 --WHERE tax_regime_code = p_tax_regime_code;
913
914 l_country_or_group_code := l_tax_regime_rec.country_or_group_code;
915 l_regime_country_code := l_tax_regime_rec.country_code;
916
917 /*IF (g_level_statement >= g_current_runtime_level ) THEN
918 FND_LOG.STRING(g_level_statement,
919 G_MODULE_NAME || l_procedure_name,
920 'Country_or_group_code = ' ||l_country_or_group_code||
921 'Country_code = '||l_regime_country_code);
922 END If;
923 */
924
925 IF l_country_or_group_code <> 'COUNTRY' THEN
926 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
927 l_log_msg := 'R: Tax Regime is created for a tax zone';
928 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
929 END IF;
930 l_country_regime_flag := 'N';
931 ELSE
932 l_country_regime_flag := 'Y';
933 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
934 l_log_msg := 'R: Tax Regime is created for a country';
935 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
936 END IF;
937 get_location_table_name(p_location_type,
938 x_location_table_name);
939
940 get_location_info(p_location_id, x_location_table_name, l_location_info_rec, x_return_status);
941
942 l_loc_country_code := l_location_info_rec.country_code;
943 /* nipatel location caching
944 check_location_exists(p_location_id, x_location_table_name);
945 IF x_location_table_name = 'HZ_LOCATIONS' THEN
946 SELECT country
947 INTO l_loc_country_code
948 FROM hz_locations
949 WHERE location_id = p_location_id;
950 ELSIF x_location_table_name = 'HR_LOCATIONS_ALL' THEN
951 SELECT country
952 INTO l_loc_country_code
953 FROM hr_locations_all
954 WHERE location_id = p_location_id;
955 END IF;
956 */
957
958 IF l_regime_country_code = l_loc_country_code THEN
959 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
960 l_log_msg := 'R: Country of Tax Regime is same as the country of the location';
961 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
962 END IF;
963 l_same_regime_loc_country := 'Y';
964 ELSE
965 null;
966 l_same_regime_loc_country := 'N';
967 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
968 l_log_msg := 'R: Country of Tax Regime is not the same as the country of the location';
969 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
970 END IF;
971 END IF;
972 END IF;
973 IF l_country_regime_flag = 'N' or l_same_regime_loc_country = 'Y' THEN
974
975 BEGIN
976 l_tbl_index := dbms_utility.get_hash_value(
977 p_tax_regime_code||p_tax||'1',
978 1,
979 8192);
980
981 IF g_geography_use_info_tbl.EXISTS(l_tbl_index) then
982 -- The Gography usage information is there in cache. No need to read from tables
983
984
985 IF (g_level_statement >= g_current_runtime_level ) THEN
986 FND_LOG.STRING(g_level_statement,
987 G_MODULE_NAME || l_procedure_name,
988 'Found Geography usage information in cache ');
989 END IF;
990
991 ELSE
992
993 -- Local variables; no need to initialize
994 -- l_geography_type.delete;
995 -- l_geography_use.delete;
996 -- l_geography_type_num.delete;
997
998 -- get tax_id
999 ZX_TDS_UTILITIES_PKG.get_tax_cache_info(
1000 p_tax_regime_code,
1001 p_tax,
1002 p_trx_date,
1003 l_tax_rec,
1004 x_return_status,
1005 l_error_buffer);
1006
1007 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1008 IF (g_level_exception >= g_current_runtime_level ) THEN
1009
1010 l_log_msg := 'Incorrect return_status after calling ' ||
1011 'ZX_TDS_UTILITIES_PKG.get_regime_cache_info
1012 RETURN_STATUS = ' || x_return_status||' Error Buffer = '||l_error_buffer||
1013 ', '||l_procedure_name||' (-)';
1014
1015 FND_LOG.STRING(g_level_exception,
1016 G_MODULE_NAME || l_procedure_name||'.END',
1017 l_log_msg);
1018 END IF;
1019
1020 RETURN;
1021 END IF;
1022
1023 l_tax_id := l_tax_rec.tax_id;
1024
1025 IF (l_tax_rec.zone_geography_type IS NOT NULL OR
1026 l_tax_rec.override_geography_type IS NOT NULL) THEN
1027
1028 SELECT geography_type, geography_use, geography_type_num
1029 BULK COLLECT INTO l_geography_type, l_geography_use, l_geography_type_num
1030 FROM
1031 (SELECT gt.geography_type geography_type, gt.geography_use geography_use, 1 geography_type_num
1032 FROM hz_geography_types_b gt
1033 WHERE l_tax_rec.zone_geography_type = gt.geography_type
1034 UNION
1035 SELECT gt.geography_type geography_type, gt.geography_use geography_use, 2 geography_type_num
1036 FROM hz_geography_types_b gt
1037 WHERE l_tax_rec.override_geography_type = gt.geography_type
1038 UNION
1039 SELECT rt.object_type geography_type,
1040 gt.geography_use geography_use,
1041 2+rownum geography_type_num
1042 FROM hz_relationship_types rt,
1043 hz_geography_types_b gt
1044 WHERE l_tax_rec.override_geography_type = rt.subject_type
1045 AND rt.object_type = gt.geography_type)
1046 ORDER BY 3 desc;
1047
1048 For i in nvl(l_geography_type.FIRST,0)..nvl(l_geography_type.LAST,-1) LOOP
1049
1050 l_tbl_index := dbms_utility.get_hash_value(
1051 p_tax_regime_code||p_tax||to_char(i),
1052 1,
1053 8192);
1054 g_geography_use_info_tbl(l_tbl_index).tax_id := l_tax_id;
1055 g_geography_use_info_tbl(l_tbl_index).GEOGRAPHY_TYPE_NUM := i;
1056 g_geography_use_info_tbl(l_tbl_index).GEOGRAPHY_TYPE := l_geography_type(i);
1057 g_geography_use_info_tbl(l_tbl_index).GEOGRAPHY_USE := l_geography_use(i);
1058
1059 END LOOP;
1060
1061 END IF;
1062
1063 END IF;
1064
1065 EXCEPTION WHEN NO_DATA_FOUND THEN
1066 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1067 l_log_msg := 'B: SEL hz_geography_type: Not Found for Tax';
1068 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1069 END IF;
1070 END;
1071
1072 -- IF l_geography_type.count > 0 THEN
1073 i := 1; -- counter for geography_type_num
1074
1075 WHILE TRUE LOOP
1076
1077 l_tbl_index := dbms_utility.get_hash_value(
1078 p_tax_regime_code||p_tax||to_char(i),
1079 1,
1080 8192);
1081
1082 IF NOT g_geography_use_info_tbl.EXISTS(l_tbl_index) then
1083
1084 EXIT; -- exit the loop
1085
1086 ELSE
1087 -- Logging Infra: Statement level: "B" means "B"reak point
1088 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1089 l_log_msg := 'B: SEL hz_geography_type: out: p_location_id='||p_location_id||',
1090 p_location_type='||p_location_type||
1091 ', l_geography_type='||g_geography_use_info_tbl(l_tbl_index).geography_type||
1092 ', l_geography_use='||g_geography_use_info_tbl(l_tbl_index).geography_use;
1093 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1094 END IF;
1095 IF g_geography_use_info_tbl(l_tbl_index).geography_use = 'TAX' THEN
1096 get_zone(p_location_id,
1097 p_location_type,
1098 g_geography_use_info_tbl(l_tbl_index).geography_type,
1099 p_trx_date,
1100 l_zone_tbl,
1101 -----l_geography_id(i),
1102 x_return_status);
1103 -- Logging Infra: Statement level: "B" means "B"reak point
1104 ----- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1105 ----- l_log_msg := 'B: geography info: geography_id='||l_geography_id(i)||', geography_name='||l_geography_name(i)||
1106 ----- ', x_return_status='||x_return_status;
1107 ----- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1108 ----- END IF;
1109 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1110 IF l_zone_tbl.count > 0 THEN
1111 FOR j in l_zone_tbl.first..l_zone_tbl.last LOOP
1112 l_geography_id := l_zone_tbl(j).zone_id;
1113 get_jurisdiction(p_tax,
1114 p_tax_regime_code,
1115 l_geography_id ,
1116 p_trx_date,
1117 l_inner_city_jurisdiction_flag);
1118 END LOOP;
1119 END IF;
1120 END IF;
1121 ELSIF g_geography_use_info_tbl(l_tbl_index).geography_use = 'MASTER_REF' THEN
1122 get_master_geography(
1123 p_location_id,
1124 p_location_type,
1125 g_geography_use_info_tbl(l_tbl_index).geography_type,
1126 l_geography_id,
1127 l_geography_code,
1128 l_geography_name,
1129 x_return_status);
1130 -- Logging Infra: Statement level: "B" means "B"reak point
1131 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1132 l_log_msg := 'B: geography info: geography_id='||l_geography_id||', geography_name='||l_geography_name||
1133 ', x_return_status='||x_return_status;
1134 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1135 END IF;
1136 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1137 IF l_geography_id IS NOT NULL THEN
1138 get_jurisdiction(p_tax,
1139 p_tax_regime_code,
1140 l_geography_id ,
1141 p_trx_date,
1142 l_inner_city_jurisdiction_flag);
1143 END IF;
1144 END IF;
1145
1146 END IF; -- _geography_use
1147 END IF; -- g_geography_use_info_tbl.EXISTS
1148 i := i + 1; -- counter for geography_type_num
1149 END LOOP;
1150
1151 -- END IF; -- l_geography_type.count > 0
1152 END IF;
1153
1154
1155 IF l_jurisdiction_rec_tmp_tbl.count = 1 THEN
1156 x_tax_jurisdiction_rec.tax_jurisdiction_id := l_jurisdiction_rec_tmp_tbl(1).tax_jurisdiction_id;
1157 x_tax_jurisdiction_rec.tax_jurisdiction_code := l_jurisdiction_rec_tmp_tbl(1).tax_jurisdiction_code;
1158 x_tax_jurisdiction_rec.tax_regime_code := l_jurisdiction_rec_tmp_tbl(1).tax_regime_code;
1159 x_tax_jurisdiction_rec.tax := l_jurisdiction_rec_tmp_tbl(1).tax;
1160 x_tax_jurisdiction_rec.precedence_level := l_jurisdiction_rec_tmp_tbl(1).precedence_level;
1161 x_jurisdictions_found := 'Y';
1162 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1163 l_log_msg := 'R: Single jurisdiction has been found';
1164 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1165 END IF;
1166 ELSIF l_jurisdiction_rec_tmp_tbl.count > 1 THEN
1167 FOR n in l_jurisdiction_rec_tmp_tbl.first..l_jurisdiction_rec_tmp_tbl.last LOOP
1168 INSERT INTO zx_jurisdictions_gt
1169 (TAX_JURISDICTION_ID,
1170 TAX_JURISDICTION_CODE,
1171 TAX_REGIME_CODE,
1172 TAX,
1173 PRECEDENCE_LEVEL)
1174 SELECT
1175 l_jurisdiction_rec_tmp_tbl(n).tax_jurisdiction_id,
1176 l_jurisdiction_rec_tmp_tbl(n).tax_jurisdiction_code,
1177 l_jurisdiction_rec_tmp_tbl(n).tax_regime_code,
1178 l_jurisdiction_rec_tmp_tbl(n).tax,
1179 l_jurisdiction_rec_tmp_tbl(n).precedence_level
1180 FROM dual
1181 WHERE NOT EXISTS (SELECT '1'
1182 FROM zx_jurisdictions_gt
1183 WHERE tax_jurisdiction_id = l_jurisdiction_rec_tmp_tbl(n).tax_jurisdiction_id);
1184
1185 END LOOP;
1186 x_jurisdictions_found := 'Y';
1187
1188 -- Logging Infra: Statement level: "R" means "R"eturned value to a caller
1189 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1190 l_log_msg := 'R: Inserted into zx_jurisdictions_gt table)' ;
1191 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1192 END IF;
1193 ELSIF l_jurisdiction_rec_tmp_tbl.count = 0 THEN
1194 x_jurisdictions_found := 'N';
1195 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1196 l_log_msg := 'R: No jurisdiction has been found';
1197 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1198 END IF;
1199 END IF;
1200
1201
1202 -- Logging Infra: Statement level: "R" means "R"eturned value to a caller
1203 ---- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1204 ---- l_log_msg := 'R: tax_jurisdiction_id='||g_jurisdiction_rec_tbl(i).tax_jurisdiction_id||
1205 ---- ', tax_jurisdiction_code='||g_jurisdiction_rec_tbl(i).tax_jurisdiction_code;
1206 ---- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1207 ---- END IF;
1208 -- Logging Infra: Procedure level
1209 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1210 l_log_msg := l_procedure_name||'(-)';
1211 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name || '.end', l_log_msg);
1212 END IF;
1213 EXCEPTION
1214 WHEN FND_API.G_EXC_ERROR THEN
1215 x_return_status := FND_API.G_RET_STS_ERROR;
1216
1217 -- Logging Infra: Statement level
1218 ---- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1219 ---- l_log_msg := 'E: EXC: FND_API.G_EXEC_ERROR: tax_jurisdiction_id='||g_jurisdiction_rec_tbl.tax_jurisdiction_id(i)||
1220 ---- ', tax_jurisdiction_code='||g_jurisdiction_rec_tbl.tax_jurisdiction_code(i);
1221 ---- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1222 ---- END IF;
1223
1224
1225 WHEN OTHERS THEN
1226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1227 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1228 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1229
1230 -- Logging Infra: Statement level
1231 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1232 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'E: EXC: OTHERS: '||SQLCODE||': '||SQLERRM);
1233 ---- l_log_msg := 'E: EXC: OTHERS: tax_jurisdiction_id='||g_jurisdiction_rec_tbl.tax_jurisdiction_id(i)||
1234 ---- ', tax_jurisdiction_code='||g_jurisdiction_rec_tbl.tax_jurisdiction_code(i);
1235 ---- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1236 END IF;
1237
1238 END get_tax_jurisdictions;
1239
1240 PROCEDURE get_master_geography
1241 (p_location_id IN VARCHAR2,
1242 p_location_type IN VARCHAR2,
1243 p_geography_type IN VARCHAR2,
1244 x_geography_id OUT NOCOPY NUMBER,
1245 x_geography_code OUT NOCOPY VARCHAR2,
1246 x_geography_name OUT NOCOPY VARCHAR2,
1247 x_return_status OUT NOCOPY VARCHAR2) IS
1248
1249 l_count NUMBER;
1250 x_location_table_name VARCHAR2(30);
1251 l_country_code VARCHAR2(30);
1252 -- Logging Infra
1253 l_procedure_name CONSTANT VARCHAR2(30) := 'get_master_geography';
1254 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1255
1256 l_location_info_rec ZX_GLOBAL_STRUCTURES_PKG.loc_info_rec_type;
1257 l_geography_use hz_geographies.geography_use%type;
1258 l_tbl_index binary_integer;
1259 l_tbl_country_index binary_integer;
1260
1261 BEGIN
1262 -- Logging Infra: Setting up runtime level
1263 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1264
1265 -- Logging Infra: Procedure level
1266 -- Logging Infra: Statement level: "B" means "B"reak point
1267 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1268 l_log_msg := l_procedure_name||'(+)';
1269 l_log_msg := l_log_msg||' B: SEL hz_geography_types: in: p_geography_type='||p_geography_type;
1270 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name || '.begin', l_log_msg);
1271 END IF;
1272
1273 x_return_status := FND_API.G_RET_STS_SUCCESS;
1274
1275 /* nipatel commented out this validation because the call to get_master_geography is
1276 being made from within this package itself and there we are already fetching the
1277 goegoraphy type from hz_geography_types_b and the call is made only when geograpy_use
1278 is 'MASTER_REF'
1279
1280 -- Validate geography type
1281 SELECT count(*)
1282 INTO l_count
1283 FROM hz_geography_types_b
1284 WHERE geography_type = p_geography_type
1285 AND geography_use = 'MASTER_REF';
1286
1287 -- ---------------------
1288 IF l_count = 0 THEN
1289 -- Logging Infra: Statement level: "E" means "E"rror
1290 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1291 l_log_msg := 'E: SEL hz_geography_types: no_data_found: '||'p_geography_type='||p_geography_type;
1292 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1293 END IF;
1294
1295 --x_return_status := FND_API.G_RET_STS_ERROR;
1296 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_TYPE_INVALID');
1297 RAISE FND_API.G_EXC_ERROR;
1298 END IF;
1299 -- ---------------------
1300 */
1301 get_location_table_name(p_location_type,
1302 x_location_table_name);
1303 get_location_info(p_location_id, x_location_table_name, l_location_info_rec,x_return_status);
1304 x_location_table_name := ZX_GLOBAL_STRUCTURES_PKG.Loc_info_tbl(to_char(p_location_id)).location_table_name;
1305
1306
1307 -- Logging Infra: Statement level: "B" means "B"reak point
1308 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1309 l_log_msg := 'B: SEL hz_geo_name_ref: in: p_location_id='||p_location_id||', p_geography_type='||p_geography_type||
1310 ', x_location_table_name='||x_location_table_name;
1311 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1312 END IF;
1313
1314 -- Find geography id from gnr table
1315 -- ---------------------------------
1316
1317 l_tbl_index := dbms_utility.get_hash_value(
1318 to_char(p_location_id)||p_geography_type,
1319 1,
1320 8192);
1321
1322 IF zx_global_structures_pkg.Loc_geography_info_tbl.exists(l_tbl_index) AND
1323 (zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).location_id = p_location_id AND
1324 zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).geography_type = p_geography_type) THEN
1325
1326 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1327 l_log_msg := 'Found the geography id and geography name info in cache';
1328 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name, l_log_msg);
1329 END IF;
1330
1331 x_geography_id := zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).geography_id;
1332 x_geography_code := zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).geography_code;
1333 x_geography_name := zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).geography_name;
1334
1335 ELSE
1336
1337 BEGIN
1338 SELECT gnr.geography_id, geo.geography_code, geo.geography_name, geo.geography_use
1339 INTO x_geography_id, x_geography_code, x_geography_name, l_geography_use
1340 FROM hz_geo_name_references gnr, hz_geographies geo
1341 WHERE gnr.location_table_name = x_location_table_name
1342 AND gnr.location_id = p_location_id
1343 AND gnr.geography_type = p_geography_type
1344 AND geo.geography_id = gnr.geography_id;
1345
1346 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1347 l_log_msg := 'B: SEL hz_geo_name_ref: out: x_geography_id='||x_geography_id;
1348 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1349 END IF;
1350
1351 zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).location_id := p_location_id;
1352 zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).geography_type := p_geography_type;
1353 zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).geography_id := x_geography_id;
1354 zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).geography_code := x_geography_code;
1355 zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).geography_name := x_geography_name;
1356 zx_global_structures_pkg.Loc_geography_info_tbl(l_tbl_index).geography_use := l_geography_use;
1357
1358
1359 EXCEPTION WHEN NO_DATA_FOUND THEN
1360
1361 -- Logging Infra: Statement level: "S" means "S"uccess. Geography code can be NULL.
1362 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1363 l_log_msg := 'S: SEL hz_geo_name_ref: no_data_found: '||'x_location_table_name='||x_location_table_name||' B: x_geography_id='||x_geography_id||', p_geography_type='||p_geography_type||
1364 ', p_location_id='||p_location_id||', p_geography_type='||p_geography_type;
1365 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1366 END IF;
1367 END;
1368
1369 END IF;
1370
1371 IF x_geography_id IS NULL THEN
1372 IF p_geography_type = 'COUNTRY' THEN
1373 -- access location tables to get the country.
1374
1375 l_country_code := l_location_info_rec.country_code;
1376
1377 -- Logging Infra: "B" means "B"reak point
1378 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1379 l_log_msg := 'B: SEL hz_geographies: in: l_country_code='||l_country_code;
1380 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1381 END IF;
1382
1383 BEGIN
1384 SELECT geography_id, geography_code, geography_name
1385 INTO x_geography_id, x_geography_code, x_geography_name
1386 FROM hz_geographies
1387 WHERE geography_code = l_country_code
1388 AND geography_type = 'COUNTRY';
1389
1390 EXCEPTION WHEN NO_DATA_FOUND THEN
1391
1392 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1393 l_log_msg := 'E: SEL hz_geographies: no_data_found: '||'l_country_code='||l_country_code;
1394 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1395 END IF;
1396
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_COUNTRY');
1399 FND_MESSAGE.SET_TOKEN('LOC_ID', p_location_id);
1400 FND_MESSAGE.SET_TOKEN('TABLE_NAME', x_location_table_name);
1401 RAISE FND_API.G_EXC_ERROR;
1402 END;
1403 ELSE
1404
1405
1406 /* 3471450 -- Logging Infra: return_status changed to success status */
1407 -- Logging Infra: Statement level: "S" means "S"uccess. Geography code can be NULL.
1408 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1409 l_log_msg := 'S: hz_geo_no_geo_name_ref: p_geography_type='||p_geography_type;
1410 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1411 END IF;
1412
1413 x_return_status := FND_API.G_RET_STS_SUCCESS;
1414
1415 END IF; -- p_geography_type = 'COUNTRY'
1416 END IF; --x_geography_id IS NULL
1417
1418 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1419 l_log_msg := 'R: x_geography_id='||x_geography_id||', x_gegraphy_code='||x_geography_code||
1420 ', x_geography_name='||x_geography_name;
1421 l_log_msg := l_log_msg || '- '||l_procedure_name||'(-)';
1422 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1423 END IF;
1424
1425 EXCEPTION
1426 WHEN FND_API.G_EXC_ERROR THEN
1427 x_return_status := FND_API.G_RET_STS_ERROR;
1428
1429 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1430 l_log_msg := 'E: EXC: FND_API.G_EXC_ERROR: x_geography_id='||
1431 x_geography_id||', x_gegraphy_code='||x_geography_code||',
1432 x_geography_name='||x_geography_name;
1433 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||l_procedure_name,l_log_msg);
1434 END IF;
1435
1436 WHEN OTHERS THEN
1437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438 FND_MESSAGE.Set_Name ('ZX','ZX_GENERIC_MESSAGE');
1439 FND_MESSAGE.Set_Token('GENERIC_TEXT', SQLERRM);
1440
1441 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1442 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name,
1443 'E: EXC: OTHERS: '|| SQLCODE||': '||SQLERRM);
1444 l_log_msg := 'E: EXC: OTHERS: x_geography_id='||x_geography_id||',
1445 x_gegraphy_code='||x_geography_code||
1446 ', x_geography_name='||x_geography_name;
1447 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME ||l_procedure_name,l_log_msg);
1448 END IF;
1449 END;
1450
1451 PROCEDURE RETRIEVE_GEO_VALUE( p_event_class_mapping_id IN ZX_LINES_DET_FACTORS.event_class_mapping_id%type,
1452 p_trx_id IN ZX_LINES_DET_FACTORS.trx_id%type,
1453 p_trx_line_id IN ZX_LINES_DET_FACTORS.trx_line_id%type,
1454 p_trx_level_type IN ZX_LINES_DET_FACTORS.trx_level_type%type,
1455 p_location_type IN VARCHAR2,
1456 p_location_id IN ZX_LINES_DET_FACTORS.ship_to_location_id%type,
1457 p_geography_type IN VARCHAR2,
1458 x_geography_value OUT NOCOPY VARCHAR2,
1459 x_geography_id OUT NOCOPY NUMBER,
1460 x_geo_val_found OUT NOCOPY BOOLEAN) IS
1461
1462 hash_string varchar2(1000);
1463 TABLE_SIZE BINARY_INTEGER := 65636;
1464 TABLEIDX binary_integer;
1465 loc_info_idx binary_integer;
1466 HASH_VALUE binary_integer;
1467
1468 BEGIN
1469
1470 hash_string := to_char(p_event_class_mapping_id)||'|'||
1471 to_char(p_trx_id)||'|'||
1472 to_char(p_trx_line_id)||'|'||
1473 p_trx_level_type||'|'||
1474 p_location_type||'|'||
1475 to_char(p_location_id)||'|'||
1476 p_geography_type;
1477
1478 TABLEIDX := dbms_utility.get_hash_value(hash_string,1,TABLE_SIZE);
1479
1480 IF (ZX_GLOBAL_STRUCTURES_PKG.location_hash_tbl.EXISTS(TABLEIDX)) THEN
1481 loc_info_idx := ZX_GLOBAL_STRUCTURES_PKG.location_hash_tbl(TABLEIDX);
1482 x_geography_value := ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.geography_value(loc_info_idx);
1483 x_geography_id:= ZX_GLOBAL_STRUCTURES_PKG.location_info_tbl.geography_id(loc_info_idx);
1484 x_geo_val_found := TRUE;
1485 ELSE
1486 x_geo_val_found := FALSE;
1487 END IF;
1488
1489 end RETRIEVE_GEO_VALUE;
1490
1491 PROCEDURE populate_loc_geography_info
1492 (EVENT_CLASS_MAPPING_ID IN NUMBER,
1493 TRX_ID IN NUMBER,
1494 TRX_LINE_ID IN NUMBER,
1495 TRX_LEVEL_TYPE IN VARCHAR2,
1496 LOCATION_TYPE_TBL IN ZX_TCM_GEO_JUR_PKG.LOCATION_TYPE_TBL_TYPE,
1497 LOCATION_ID_TBL IN ZX_TCM_GEO_JUR_PKG.LOCATION_ID_TBL_TYPE,
1498 x_return_status OUT NOCOPY VARCHAR2
1499 ) IS
1500 n NUMBER;
1501 k BINARY_INTEGER;
1502 x_loc_tbl VARCHAR2(30);
1503 l_error_flag VARCHAR2(1) := 'N';
1504 geography_found VARCHAR2(6) := 'FALSE';
1505 idx1 VARCHAR2(100);
1506 hash_string VARCHAR2(1000);
1507 TABLEIDX binary_integer;
1508 TABLE_SIZE BINARY_INTEGER := 65636;
1509 TYPE GEO_ID_TBL_TYPE IS TABLE OF NUMBER index by BINARY_INTEGER;
1510 l_geography_id GEO_ID_TBL_TYPE;
1511
1512 TYPE GEO_TYPE_TBL_TYPE IS TABLE OF VARCHAR2(30) index by BINARY_INTEGER;
1513 l_geography_type GEO_TYPE_TBL_TYPE;
1514
1515 TYPE GEO_NAME_TBL_TYPE IS TABLE OF VARCHAR2(360) index by BINARY_INTEGER;
1516 l_geography_name GEO_NAME_TBL_TYPE;
1517 -- Logging Infra
1518 l_procedure_name CONSTANT VARCHAR2(30) := 'populate_loc_geography_info';
1519 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1520 BEGIN
1521 -- Logging Infra: Setting up runtime level
1522 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1523
1524 -- Logging Infra: Procedure level
1525 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1526 l_log_msg := l_procedure_name||'(+)';
1527 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME || l_procedure_name || '.begin', l_log_msg);
1528 END IF;
1529
1530 -- Initialize API return status to success.
1531
1532 x_return_status := FND_API.G_RET_STS_SUCCESS;
1533
1534 IF location_type_tbl.count > 0 THEN
1535
1536 FOR i IN location_type_tbl.first..location_type_tbl.last LOOP
1537 ZX_TCM_GEO_JUR_PKG.get_location_table_name(location_type_tbl(i), x_loc_tbl);
1538
1539 check_location_exists(location_id_tbl(i), x_loc_tbl);
1540
1541 -- Logging Infra: Statement level: "B" means "B"reak point
1542 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1543 -- l_log_msg := 'B: get_location_table_name: out: x_loc_tbl='||x_loc_tbl;
1544 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1545 -- END IF;
1546
1547 n := 1;
1548 idx1 := to_char(event_class_mapping_id)||location_type_tbl(i)||location_id_tbl(i) ||x_loc_tbl|| to_char(n);
1549 k := ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.event_class_mapping_id.last;
1550 IF k IS NOT NULL THEN
1551 k := k + 1;
1552 ELSE
1553 k := 1;
1554 END IF;
1555 LOOP
1556
1557 IF g_geography_id_tbl.exists(idx1) THEN
1558 geography_found := 'TRUE';
1559 -- Logging Infra: Statement level: "B" means "B"reak point
1560 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1561 l_log_msg := 'B: Geography found ';
1562 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1563 END IF;
1564 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.event_class_mapping_id(k) :=
1565 event_class_mapping_id;
1566 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.trx_id(k) := trx_id;
1567 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.trx_line_id(k) := trx_line_id;
1568 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.trx_level_type(k) := trx_level_type;
1569 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.location_type(k) := location_type_tbl(i);
1570 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.location_table_name(k) := x_loc_tbl;
1571 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.location_id (k) := location_id_tbl(i);
1572 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.geography_id(k) := g_geography_id_tbl(idx1);
1573
1574 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.geography_type(k) := g_geography_type_tbl(idx1);
1575 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.geography_value(k) := g_geography_name_tbl(idx1);
1576
1577
1578 hash_string := to_char(event_class_mapping_id)||'|'||
1579 to_char(trx_id)||'|'||
1580 to_char(trx_line_id)||'|'||
1581 trx_level_type||'|'||
1582 location_type_tbl(i)||'|'||
1583 to_char(location_id_tbl(i))||'|'||
1584 g_geography_type_tbl(idx1);
1585
1586 TABLEIDX := dbms_utility.get_hash_value(hash_string,1,TABLE_SIZE);
1587
1588 ZX_GLOBAL_STRUCTURES_PKG.location_hash_tbl(TABLEIDX) := k;
1589
1590
1591 -- Logging Infra: Statement level: "B" means "B"reak point
1592 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1593 l_log_msg := 'B: Geography found ='||event_class_mapping_id||trx_id||trx_line_id||
1594 trx_level_type||x_loc_tbl||location_id_tbl(i)||g_geography_id_tbl(idx1)
1595 ||g_geography_type_tbl(idx1)||g_geography_name_tbl(idx1);
1596 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1597 END IF;
1598 n := n + 1;
1599 k := k + 1;
1600 idx1 := to_char(event_class_mapping_id)||location_type_tbl(i)||location_id_tbl(i) ||x_loc_tbl|| to_char(n);
1601 ELSE
1602 -- Logging Infra: Statement level: "B" means "B"reak point
1603 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1604 l_log_msg := 'B: Geography NOT existing in cache ';
1605 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1606 END IF;
1607 EXIT;
1608 END IF;
1609 END LOOP;
1610 IF geography_found <> 'TRUE' THEN
1611 -- Logging Infra: Statement level: "B" means "B"reak point
1612 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1613 l_log_msg := 'B: Fetching geography info from gnr. The Location id is'||to_char(location_id_tbl(i));
1614 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1615 END IF;
1616 BEGIN
1617 l_error_flag := 'N';
1618 SELECT gnr.geography_id, gnr.geography_type, g.geography_name
1619 BULK COLLECT INTO l_geography_id, l_geography_type, l_geography_name
1620 FROM hz_geo_name_references gnr, hz_geographies g
1621 WHERE location_table_name = x_loc_tbl
1622 AND location_id = location_id_tbl(i)
1623 AND gnr.geography_id = g.geography_id
1624 ORDER BY gnr.geography_id;
1625 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1626 l_log_msg := 'B: After GNR fetch ';
1627 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1628 END IF;
1629 EXCEPTION
1630 WHEN OTHERS THEN
1631 -- Logging Infra: Statement level
1632 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1633 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, 'E: EXC: OTHERS: '
1634 || SQLCODE||': '||SQLERRM);
1635
1636 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1637 END IF;
1638 l_error_flag := 'Y';
1639 END;
1640 IF l_error_flag <> 'Y' then
1641 n := 1;
1642 idx1 := to_char(event_class_mapping_id)||location_type_tbl(i)||location_id_tbl(i) ||x_loc_tbl|| to_char(n);
1643 IF l_geography_id.count > 0 THEN
1644 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1645 l_log_msg := 'B: Entering assignment loop after GNR fetch';
1646 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1647 END IF;
1648
1649
1650 FOR m IN l_geography_id.first..l_geography_id.last LOOP
1651 -----for each geography element found above add row in location_info_tbl, g_geography_id_tbl;
1652 g_geography_id_tbl(idx1) := l_geography_id(m);
1653
1654 g_geography_type_tbl(idx1) := l_geography_type(m);
1655
1656 g_geography_name_tbl(idx1) := l_geography_name(m);
1657
1658 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.event_class_mapping_id(k) := event_class_mapping_id;
1659
1660 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.trx_id(k) := trx_id;
1661
1662 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.trx_line_id(k) := trx_line_id;
1663
1664 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.trx_level_type(k) := trx_level_type;
1665
1666 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.location_type(k) := location_type_tbl(i);
1667
1668 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.location_table_name(k) := x_loc_tbl;
1669
1670 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.location_id (k) := location_id_tbl(i);
1671
1672 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.geography_id(k) := g_geography_id_tbl(idx1);
1673
1674 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.geography_type(k) := g_geography_type_tbl(idx1);
1675
1676 ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.geography_value(k) := g_geography_name_tbl(idx1);
1677
1678 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1679 l_log_msg := 'B: g_geography_id_tbl(idx1): l_geography_id(m)'||
1680 g_geography_id_tbl(idx1)|| l_geography_id(m);
1681 l_log_msg := l_log_msg ||' B: g_geography_type_tbl(idx1): l_geography_type(m)'
1682 ||g_geography_type_tbl(idx1)|| l_geography_type(m);
1683 l_log_msg := l_log_msg ||' B: g_geography_name_tbl(idx1): l_geography_name(m)'
1684 ||g_geography_name_tbl(idx1)|| l_geography_name(m);
1685 l_log_msg := l_log_msg ||' B: LOCATION_INFO_TBL.event_class_mapping_id(k)'
1686 || ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.event_class_mapping_id(k);
1687 l_log_msg := l_log_msg ||' B: LOCATION_INFO_TBL.trx_id(k)'
1688 ||ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.trx_id(k);
1689 l_log_msg := l_log_msg ||' B: LOCATION_INFO_TBL.trx_line_id(k)'
1690 ||ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.trx_line_id(k);
1691 l_log_msg := l_log_msg ||' B: LOCATION_INFO_TBL.trx_level_type(k)'
1692 ||ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.trx_level_type(k);
1693 l_log_msg := l_log_msg ||' B: LOCATION_INFO_TBL.location_type(k)'
1694 ||ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.location_type(k);
1695 l_log_msg := l_log_msg ||' B: LOCATION_INFO_TBL.location_table_name(k)'
1696 ||ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.location_table_name(k);
1697 l_log_msg := l_log_msg ||' B: LOCATION_INFO_TBL.location_id(k)'
1698 ||ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.location_id(k);
1699 l_log_msg := l_log_msg ||' B: LOCATION_INFO_TBL.geography_id(k)'
1700 ||ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.geography_id(k);
1701 l_log_msg := l_log_msg ||' B: LOCATION_INFO_TBL.geography_type(k)'
1702 ||ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.geography_type(k);
1703 l_log_msg := l_log_msg || 'B: LOCATION_INFO_TBL.geography_value(k)'
1704 ||ZX_GLOBAL_STRUCTURES_PKG.LOCATION_INFO_TBL.geography_value(k);
1705 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1706 END IF;
1707
1708 hash_string := to_char(event_class_mapping_id)||'|'||
1709 to_char(trx_id)||'|'||
1710 to_char(trx_line_id)||'|'||
1711 trx_level_type||'|'||
1712 location_type_tbl(i)||'|'||
1713 to_char(location_id_tbl(i))||'|'||
1714 g_geography_type_tbl(idx1);
1715
1716 TABLEIDX := dbms_utility.get_hash_value(hash_string,1,TABLE_SIZE);
1717
1718 ZX_GLOBAL_STRUCTURES_PKG.location_hash_tbl(TABLEIDX) := k;
1719
1720 -- Logging Infra: Statement level: "B" means "B"reak point
1721 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1722 l_log_msg := 'B: Geography details populated'|| g_geography_id_tbl(idx1)
1723 ||g_geography_type_tbl(idx1)||g_geography_name_tbl(idx1);
1724 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1725 END IF;
1726
1727 n := n + 1;
1728 k := k + 1;
1729 idx1 := to_char(event_class_mapping_id)||location_type_tbl(i)||location_id_tbl(i) ||x_loc_tbl|| to_char(n);
1730
1731 END LOOP;
1732 END IF;
1733 END IF;
1734 END IF;
1735 END LOOP;
1736 END IF;
1737 END;
1738
1739 END;