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