1 PACKAGE BODY HZ_GNR_MAP10000 AS
2
3 /*=======================================================================+
4 | Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | NAME HZ_GNR_MAP10000
8 |
9 | DESCRIPTION
10 | This package body is generated by TCA for geoName referencing.
11 |
12 | HISTORY
13 | 11/06/2008 03:21:24 Generated.
14 |
15 *=======================================================================*/
16
17 --------------------------------------
18 -- declaration of private global varibles
19 --------------------------------------
20
21 g_debug_count NUMBER := 0;
22 g_country_geo_id NUMBER;
23 G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;
24 G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
25 G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;
26 G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;
27 G_MDU_TBL10000 HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
28 G_MDU_TBL10006 HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
29
30 --------------------------------------
31 -- declaration of private procedures and functions
32 --------------------------------------
33
34 --------------------------------------
35 -- private procedures and functions
36 --------------------------------------
37
38 FUNCTION get_usage_API(
39 P_USAGE_CODE VARCHAR2) RETURN VARCHAR2 IS
40 i number;
41 l_API_Name varchar2(30);
42
43 BEGIN
44 i := 0;
45 IF G_USAGE_TBL.COUNT > 0 THEN
46 i := G_USAGE_TBL.FIRST;
47 LOOP
48 IF G_USAGE_TBL(i).USAGE_CODE = P_USAGE_CODE THEN
49 IF G_USAGE_TBL(i).USAGE_CODE = 'GEOGRAPHY' THEN
50 l_API_Name := 'validateGeo';
51 ELSIF G_USAGE_TBL(i).USAGE_CODE = 'TAX' THEN
52 l_API_Name := 'validateTax';
53 ELSE
54 l_API_Name := 'validate'||G_USAGE_TBL(i).USAGE_ID;
55 END IF;
56 END IF;
57 EXIT WHEN i = G_USAGE_TBL.LAST;
58 i := G_USAGE_TBL.NEXT(i);
59 END LOOP;
60 END IF;
61
62 RETURN l_API_Name;
63
64 END get_usage_API;
65
66
67 PROCEDURE validateForMap(
68 p_loc_components_rec IN HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE,
69 x_map_dtls_tbl IN OUT NOCOPY HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE,
70 X_CAUSE OUT NOCOPY VARCHAR2,
71 X_STATUS OUT NOCOPY VARCHAR2) IS
72
73
74 TYPE getGeo IS REF CURSOR;
75 c_getGeo getGeo;
76 c_getGeo1 getGeo;
77
78 l_multiple_parent_flag VARCHAR2(1);
79 l_sql VARCHAR2(9000);
80 l_status VARCHAR2(1);
81 l_geography_type VARCHAR2(30);
82 l_geography_id NUMBER;
83 L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
84
85 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRGB:HZ_GNR_MAP10000';
86 l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
87 l_debug_prefix VARCHAR2(30);
88
89 l_value1 VARCHAR2(360);
90 l_type1 VARCHAR2(30);
91 l_value2 VARCHAR2(360);
92 l_type2 VARCHAR2(30);
93 l_value3 VARCHAR2(360);
94 l_type3 VARCHAR2(30);
95 l_value4 VARCHAR2(360);
96 l_type4 VARCHAR2(30);
97 l_value5 VARCHAR2(360);
98 l_type5 VARCHAR2(30);
99
100 BEGIN
101
102 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
103 hz_utility_v2pub.debug
104 (p_message => 'Begin of Validate for Map',
105 p_prefix => l_debug_prefix,
106 p_msg_level => fnd_log.level_procedure,
107 p_module_prefix => l_module_prefix,
108 p_module => l_module
109 );
110 END IF;
111
112 --hk_debugl('Validate for Map Start');
113 -- defaulting the sucess status
114 x_status := FND_API.g_ret_sts_success;
115
116 L_MAP_DTLS_TBL := X_MAP_DTLS_TBL;
117 --hk_debugl('The Map table passed in with loc comp values');
118 --hk_debugt(L_MAP_DTLS_TBL);
119
120 IF L_MAP_DTLS_TBL.COUNT = 1 THEN
121 -- This means country is the only required mapped column for validation.
122 -- and country is already populated in the L_MAP_DTLS_TBL in the initialization section of this package.
123 x_status := FND_API.g_ret_sts_success;
124 RETURN;
125 END IF;
126
127 IF HZ_GNR_UTIL_PKG.getLocCompCount(L_MAP_DTLS_TBL) = 0 THEN
128 --hk_debugl('HZ_GNR_UTIL_PKG.getLocCompCount = 0');
129
130 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
131 hz_utility_v2pub.debug
132 (p_message => ' HZ_GNR_UTIL_PKG.getLocCompCount = 0 ',
133 p_prefix => l_debug_prefix,
134 p_msg_level => fnd_log.level_statement,
135 p_module_prefix => l_module_prefix,
136 p_module => l_module
137 );
138 END IF;
139
140 --No other location component value other than country is passed.
141 --Following call will try to derive missing lower level compoents
142 IF HZ_GNR_UTIL_PKG.fix_child(L_MAP_DTLS_TBL) = FALSE THEN
143 x_cause := 'MISSING_CHILD';
144
145 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
146 hz_utility_v2pub.debug
147 (p_message => ' x_cause : '||x_cause,
148 p_prefix => l_debug_prefix,
149 p_msg_level => fnd_log.level_statement,
150 p_module_prefix => l_module_prefix,
151 p_module => l_module
152 );
153 END IF;
154
155 x_status := FND_API.G_RET_STS_ERROR;
156 X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
157 RETURN;
158 ELSE
159
160 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
161 hz_utility_v2pub.debug
162 (p_message => ' Derived the missing lower level compoents ',
163 p_prefix => l_debug_prefix,
164 p_msg_level => fnd_log.level_statement,
165 p_module_prefix => l_module_prefix,
166 p_module => l_module
167 );
168 END IF;
169
170 x_status := FND_API.G_RET_STS_SUCCESS;
171 X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
172 RETURN;
173 END IF;
174 END IF;
175
176 l_value2 := NVL(L_MAP_DTLS_TBL(2).LOC_COMPVAL,'X') ;
177 IF l_value2 = 'X' THEN
178 l_type2 := 'X';
179 ELSE
180 l_type2 := L_MAP_DTLS_TBL(2).GEOGRAPHY_TYPE;
181 -- store the geography_type of the lowest address component that has a value passed in
182 l_geography_type := l_type2;
183 END IF;
184
185 l_value3 := NVL(L_MAP_DTLS_TBL(3).LOC_COMPVAL,'X') ;
186 IF l_value3 = 'X' THEN
187 l_type3 := 'X';
188 ELSE
189 l_type3 := L_MAP_DTLS_TBL(3).GEOGRAPHY_TYPE;
190 -- store the geography_type of the lowest address component that has a value passed in
191 l_geography_type := l_type3;
192 END IF;
193
194 l_value4 := NVL(L_MAP_DTLS_TBL(4).LOC_COMPVAL,'X') ;
195 IF l_value4 = 'X' THEN
196 l_type4 := 'X';
197 ELSE
198 l_type4 := L_MAP_DTLS_TBL(4).GEOGRAPHY_TYPE;
199 -- store the geography_type of the lowest address component that has a value passed in
200 l_geography_type := l_type4;
201 END IF;
202
203 l_value5 := NVL(L_MAP_DTLS_TBL(5).LOC_COMPVAL,'X') ;
204 IF l_value5 = 'X' THEN
205 l_type5 := 'X';
206 ELSE
207 l_type5 := L_MAP_DTLS_TBL(5).GEOGRAPHY_TYPE;
208 -- store the geography_type of the lowest address component that has a value passed in
209 l_geography_type := l_type5;
210 END IF;
211
212
213 l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MAP_DTLS_TBL,x_status);
214 --hk_debugl('The SQL query');
215 --hk_debugl(l_sql);
216
217 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
218 hz_utility_v2pub.debug
219 (p_message => ' The SQL query : '||l_sql,
220 p_prefix => l_debug_prefix,
221 p_msg_level => fnd_log.level_statement,
222 p_module_prefix => l_module_prefix,
223 p_module => l_module
224 );
225 END IF;
226
227 OPEN c_getGeo FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id
228 ,l_type2,l_value2,l_type3,l_value3,l_type4,l_value4,l_type5,l_value5,l_geography_type;
229 --hk_debugl('Before the first fetch');
230
231 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
232 hz_utility_v2pub.debug
233 (p_message => ' Before the first fetch',
234 p_prefix => l_debug_prefix,
235 p_msg_level => fnd_log.level_statement,
236 p_module_prefix => l_module_prefix,
237 p_module => l_module
238 );
239 END IF;
240
241 --hk_debugt(L_MAP_DTLS_TBL);
242 FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID
243 ,L_MAP_DTLS_TBL(2).GEOGRAPHY_ID,L_MAP_DTLS_TBL(3).GEOGRAPHY_ID,L_MAP_DTLS_TBL(4).GEOGRAPHY_ID,L_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
244 IF c_getGeo%NOTFOUND THEN
245
246 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
247 hz_utility_v2pub.debug
248 (p_message => ' NOT FOUND of the first fetch',
249 p_prefix => l_debug_prefix,
250 p_msg_level => fnd_log.level_statement,
251 p_module_prefix => l_module_prefix,
252 p_module => l_module
253 );
254 END IF;
255
256 --hk_debugl('NOT FOUND of the first fetch');
257 --hk_debugt(L_MAP_DTLS_TBL);
258 x_cause := 'NO_MATCH';
259 HZ_GNR_UTIL_PKG.fix_no_match(L_MAP_DTLS_TBL,x_status);
260 --hk_debugl('Map_loc table after Fix');
261 --hk_debugt(L_MAP_DTLS_TBL);
262 x_status := FND_API.G_RET_STS_ERROR;
263 ELSE
264 --Fetching once more to see where there are multiple records
265
266 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
267 hz_utility_v2pub.debug
268 (p_message => ' Fetching once more to see where there are multiple records ',
269 p_prefix => l_debug_prefix,
270 p_msg_level => fnd_log.level_statement,
271 p_module_prefix => l_module_prefix,
272 p_module => l_module
273 );
274 END IF;
275
276 FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID
277 ,L_MAP_DTLS_TBL(2).GEOGRAPHY_ID,L_MAP_DTLS_TBL(3).GEOGRAPHY_ID,L_MAP_DTLS_TBL(4).GEOGRAPHY_ID,L_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
278 IF c_getGeo%FOUND THEN -- not able to identify a unique record
279
280 -- Get the query again with identifier type as NAME if multiple match found
281 -- If it returns a record, we are able to derive a unique record for identifier type as NAME
282 l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MAP_DTLS_TBL,x_status);
283 OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id
284 ,l_type2,l_value2,l_type3,l_value3,l_type4,l_value4,l_type5,l_value5,l_geography_type;
285
286 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
287 hz_utility_v2pub.debug
288 (p_message => 'Before the fetch of the query with identifier type as NAME after multiple match found',
289 p_prefix => l_debug_prefix,
290 p_msg_level => fnd_log.level_statement,
291 p_module_prefix => l_module_prefix,
292 p_module => l_module
293 );
294 END IF;
295
296 --hk_debugt(L_MAP_DTLS_TBL);
297 FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID
298 ,L_MAP_DTLS_TBL(2).GEOGRAPHY_ID,L_MAP_DTLS_TBL(3).GEOGRAPHY_ID,L_MAP_DTLS_TBL(4).GEOGRAPHY_ID,L_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
299 IF c_getGeo1%FOUND THEN
300 -- check if there is another row with same STANDARD_NAME, in that case it is error case
301 FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID
302 ,L_MAP_DTLS_TBL(2).GEOGRAPHY_ID,L_MAP_DTLS_TBL(3).GEOGRAPHY_ID,L_MAP_DTLS_TBL(4).GEOGRAPHY_ID,L_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
303 IF c_getGeo1%NOTFOUND THEN -- success (only 1 rec with same primary name exists)
304
305 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
306 hz_utility_v2pub.debug
307 (p_message => 'MAP-Able to found a unique record or a record with multiple parent flag = Y with identifier type as NAME',
308 p_prefix => l_debug_prefix,
309 p_msg_level => fnd_log.level_statement,
310 p_module_prefix => l_module_prefix,
311 p_module => l_module
312 );
313 END IF;
314
315 ELSE -- Not able to find a unique record with identifier type as NAME
316
317 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
318 hz_utility_v2pub.debug
319 (p_message => 'MAP-Not able to find a record with with identifier type as NAME. '||
320 ' More than 1 rec exists with same STANDARD NAME',
321 p_prefix => l_debug_prefix,
322 p_msg_level => fnd_log.level_statement,
323 p_module_prefix => l_module_prefix,
324 p_module => l_module
325 );
326 END IF;
327
328 x_cause := 'MULTIPLE_MATCH';
329 x_status := FND_API.G_RET_STS_ERROR;
330 RETURN;
331 END IF;
332
333 ELSE -- Not able to found a unique record with identifier type as NAME
334 x_cause := 'MULTIPLE_MATCH';
335
336 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
337 hz_utility_v2pub.debug
338 (p_message => ' Not able to find a record with with identifier type as NAME. x_cause : '||x_cause,
339 p_prefix => l_debug_prefix,
340 p_msg_level => fnd_log.level_statement,
341 p_module_prefix => l_module_prefix,
342 p_module => l_module
343 );
344 END IF;
345
346 x_status := FND_API.G_RET_STS_ERROR;
347 RETURN;
348 END IF;
349 CLOSE c_getGeo1;
350
351 ELSE -- a unique record or a record with multiple parent flag = Y is found
352
353 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
354 hz_utility_v2pub.debug
355 (p_message => ' A unique record or a record with multiple parent flag = Y is found ',
356 p_prefix => l_debug_prefix,
357 p_msg_level => fnd_log.level_statement,
358 p_module_prefix => l_module_prefix,
359 p_module => l_module
360 );
361 END IF;
362
363 END IF;
364
365 IF l_multiple_parent_flag = 'Y' THEN
369
366 IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,L_MAP_DTLS_TBL) = TRUE THEN
367 NULL; -- a unique record is found
368 ELSE -- Multiple parent case not able to find a unique record
370 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
371 hz_utility_v2pub.debug
372 (p_message => ' Multiple parent case not able to find a unique record',
373 p_prefix => l_debug_prefix,
374 p_msg_level => fnd_log.level_statement,
375 p_module_prefix => l_module_prefix,
376 p_module => l_module
377 );
378 END IF;
379
380 x_cause := 'MULTIPLE_PARENT';
381 X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
382 x_status := FND_API.G_RET_STS_ERROR;
383
384 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
385 hz_utility_v2pub.debug
386 (p_message => ' x_cause : '||x_cause,
387 p_prefix => l_debug_prefix,
388 p_msg_level => fnd_log.level_statement,
389 p_module_prefix => l_module_prefix,
390 p_module => l_module
391 );
392 END IF;
393
394 RETURN;
395 END IF;
396
397 ELSE -- a unique record is found
398
399 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
400 hz_utility_v2pub.debug
401 (p_message => 'A unique record is found ',
402 p_prefix => l_debug_prefix,
403 p_msg_level => fnd_log.level_statement,
404 p_module_prefix => l_module_prefix,
405 p_module => l_module
406 );
407 END IF;
408
409 END IF;
410
411 END IF;
412
413 CLOSE c_getGeo;
414
415
416 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
417 hz_utility_v2pub.debug
418 (p_message => ' Calling fix_child. This call will try to derive missing lower level compoents.',
419 p_prefix => l_debug_prefix,
420 p_msg_level => fnd_log.level_statement,
421 p_module_prefix => l_module_prefix,
422 p_module => l_module
423 );
424 END IF;
425
426 --Following call will try to derive missing lower level compoents
427 IF HZ_GNR_UTIL_PKG.fix_child(L_MAP_DTLS_TBL) = FALSE THEN
428 x_cause := 'MISSING_CHILD';
429
430 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
431 hz_utility_v2pub.debug
432 (p_message => ' x_cause : '||x_cause,
433 p_prefix => l_debug_prefix,
434 p_msg_level => fnd_log.level_statement,
435 p_module_prefix => l_module_prefix,
436 p_module => l_module
437 );
438 END IF;
439
440 x_status := FND_API.G_RET_STS_ERROR;
441 X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
442 RETURN;
443 END IF;
444
445 X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
446
447 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
448 hz_utility_v2pub.debug
449 (p_message => 'End of Validate for Map',
450 p_prefix => l_debug_prefix,
451 p_msg_level => fnd_log.level_procedure,
452 p_module_prefix => l_module_prefix,
453 p_module => l_module
454 );
455 END IF;
456
457
458 END validateForMap;
459
460 PROCEDURE validateGeo(
461 P_LOCATION_ID IN NUMBER,
462 P_COUNTRY IN VARCHAR2,
463 P_STATE IN VARCHAR2,
464 P_PROVINCE IN VARCHAR2,
465 P_COUNTY IN VARCHAR2,
466 P_CITY IN VARCHAR2,
467 P_POSTAL_CODE IN VARCHAR2,
468 P_POSTAL_PLUS4_CODE IN VARCHAR2,
469 P_ATTRIBUTE1 IN VARCHAR2,
470 P_ATTRIBUTE2 IN VARCHAR2,
471 P_ATTRIBUTE3 IN VARCHAR2,
472 P_ATTRIBUTE4 IN VARCHAR2,
473 P_ATTRIBUTE5 IN VARCHAR2,
474 P_ATTRIBUTE6 IN VARCHAR2,
475 P_ATTRIBUTE7 IN VARCHAR2,
476 P_ATTRIBUTE8 IN VARCHAR2,
477 P_ATTRIBUTE9 IN VARCHAR2,
478 P_ATTRIBUTE10 IN VARCHAR2,
479 P_LOCK_FLAG IN VARCHAR2,
480 X_CALL_MAP IN OUT NOCOPY VARCHAR2,
481 P_CALLED_FROM IN VARCHAR2,
482 P_ADDR_VAL_LEVEL IN VARCHAR2,
483 X_ADDR_WARN_MSG OUT NOCOPY VARCHAR2,
484 X_ADDR_VAL_STATUS OUT NOCOPY VARCHAR2,
485 X_STATUS OUT NOCOPY VARCHAR2) IS
486
487 l_loc_components_rec HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE;
488
489 TYPE getGeo IS REF CURSOR;
490 c_getGeo getGeo;
491 c_getGeo1 getGeo;
492
493 l_multiple_parent_flag VARCHAR2(1);
497 l_usage_id NUMBER;
494 l_sql VARCHAR2(9000);
495 l_cause VARCHAR2(30);
496 l_usage_code VARCHAR2(30);
498 l_status VARCHAR2(1);
499 l_get_addr_val VARCHAR2(1);
500 l_geography_type VARCHAR2(30);
501 l_geography_id NUMBER;
502 L_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
503 LL_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
504 L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
505
506 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRGB:HZ_GNR_MAP10000';
507 l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
508 l_debug_prefix VARCHAR2(30) := p_location_id;
509
510 l_value1 VARCHAR2(360);
511 l_type1 VARCHAR2(30);
512 l_value2 VARCHAR2(360);
513 l_type2 VARCHAR2(30);
514 l_value3 VARCHAR2(360);
515 l_type3 VARCHAR2(30);
516 l_value4 VARCHAR2(360);
517 l_type4 VARCHAR2(30);
518
519 BEGIN
520
521 -- defaulting the sucess status
522 x_status := FND_API.g_ret_sts_success;
523 --hk_debugl('Processing Location record with location_id :- '||nvl(to_char(p_location_id),'NULL_LOCATION_ID'));
524 l_loc_components_rec.COUNTRY := P_COUNTRY;
525 l_loc_components_rec.STATE := P_STATE;
526 l_loc_components_rec.PROVINCE := P_PROVINCE;
527 l_loc_components_rec.COUNTY := P_COUNTY;
528 l_loc_components_rec.CITY := P_CITY;
529 l_loc_components_rec.POSTAL_CODE := P_POSTAL_CODE;
530 l_loc_components_rec.POSTAL_PLUS4_CODE := P_POSTAL_PLUS4_CODE;
531 l_loc_components_rec.ATTRIBUTE1 := P_ATTRIBUTE1;
532 l_loc_components_rec.ATTRIBUTE2 := P_ATTRIBUTE2;
533 l_loc_components_rec.ATTRIBUTE3 := P_ATTRIBUTE3;
534 l_loc_components_rec.ATTRIBUTE4 := P_ATTRIBUTE4;
535 l_loc_components_rec.ATTRIBUTE5 := P_ATTRIBUTE5;
536 l_loc_components_rec.ATTRIBUTE6 := P_ATTRIBUTE6;
537 l_loc_components_rec.ATTRIBUTE7 := P_ATTRIBUTE7;
538 l_loc_components_rec.ATTRIBUTE8 := P_ATTRIBUTE8;
539 l_loc_components_rec.ATTRIBUTE9 := P_ATTRIBUTE9;
540 l_loc_components_rec.ATTRIBUTE10 := P_ATTRIBUTE10;
541
542 L_USAGE_ID := 10000;
543 L_USAGE_CODE := 'GEOGRAPHY';
544 L_MDU_TBL := G_MDU_TBL10000;
545 L_MAP_DTLS_TBL := G_MAP_DTLS_TBL;
546 l_get_addr_val := 'N';
547
548 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
549 hz_utility_v2pub.debug
550 (p_message => 'Begin of validation for '||L_USAGE_CODE,
551 p_prefix => l_debug_prefix,
552 p_msg_level => fnd_log.level_procedure,
553 p_module_prefix => l_module_prefix,
554 p_module => l_module
555 );
556 END IF;
557
558 IF P_LOCATION_ID IS NOT NULL AND P_CALLED_FROM <> 'GNR' THEN
559 --hk_debugl('Before check_GNR_For_Usage');
560 IF HZ_GNR_UTIL_PKG.check_GNR_For_Usage(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
561 L_USAGE_CODE,L_MDU_TBL,x_status) = TRUE THEN
562 --hk_debugl('After check_GNR_For_Usage with status :- '||x_status);
563
564 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
565 hz_utility_v2pub.debug
566 (p_message => 'There is already a procedded success record in GNR log table.',
567 p_prefix => l_debug_prefix,
568 p_msg_level => fnd_log.level_statement,
569 p_module_prefix => l_module_prefix,
570 p_module => l_module
571 );
572 END IF;
573
574 x_status := FND_API.g_ret_sts_success;
575 X_ADDR_VAL_STATUS := x_status;
576 RETURN;
577 END IF;
578 END IF;
579
580 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
581 hz_utility_v2pub.debug
582 (p_message => 'Not able to find an existing success record in GNR log table.',
583 p_prefix => l_debug_prefix,
584 p_msg_level => fnd_log.level_statement,
585 p_module_prefix => l_module_prefix,
586 p_module => l_module
587 );
588 END IF;
589
590 -- After the following call L_MAP_DTLS_TBL will have the components value populated.
591 HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MAP_DTLS_TBL,x_status);
592
593 -- Below code will overwrite the LOC_COMPVAL for COUNTRY to COUNTRY_CODE
594 -- This change is to update COUNTRY column in locations table with COUNTRY_CODE
595 -- even if the table has Country name in this column and the validation is success
596 L_MAP_DTLS_TBL(1).LOC_COMPVAL := G_MAP_REC.COUNTRY_CODE;
597
598 -- After the following call L_MDU_TBL will have the components value populated.
599 HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MDU_TBL,x_status);
600
601 --hk_debugl(' value of X_CALL_MAP : '||X_CALL_MAP);
602 IF X_CALL_MAP = 'Y' THEN
603 LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
604
608 p_prefix => l_debug_prefix,
605 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
606 hz_utility_v2pub.debug
607 (p_message => 'Before calling validate for Map ',
609 p_msg_level => fnd_log.level_statement,
610 p_module_prefix => l_module_prefix,
611 p_module => l_module
612 );
613 END IF;
614
615 validateForMap(L_LOC_COMPONENTS_REC,LL_MAP_DTLS_TBL,l_cause,x_status);
616 --hk_debugl('Back from Validate for Map with status :- '||x_status||'.. and case :'||l_cause);
617
618 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
619 hz_utility_v2pub.debug
620 (p_message => 'Back from Validate for Map with status : .'||x_status||'.. and case :'||l_cause,
621 p_prefix => l_debug_prefix,
622 p_msg_level => fnd_log.level_statement,
623 p_module_prefix => l_module_prefix,
624 p_module => l_module
625 );
626 END IF;
627
628 -- This usage level check is required upfront because usage level validation will ignore
629 -- some of the passed in parameters for the complete mapping and may result in wrong status
630 IF (x_status = FND_API.g_ret_sts_error) THEN
631
632 -- hk_debugl('Trying to check if usage level validation is success even with map validation as error..');
633 -- hk_debugl('TABLE that is returned by Validate For Map');
634 -- hk_debugt(LL_MAP_DTLS_TBL);
635 -- hk_debugl('Usage Map Table With loc comp values');
636 -- hk_debugt(L_MDU_TBL);
637
638 IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_SUCCESS THEN
639 -- hk_debugl('COMPLETE mapping is error but is sufficient for passed usage. So setting X_STATUS to success');
640 x_status := FND_API.g_ret_sts_success;
641
642 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
643 hz_utility_v2pub.debug
644 (p_message => 'COMPLETE mapping is error but is sufficient for passed usage. So setting X_STATUS to success',
645 p_prefix => l_debug_prefix,
646 p_msg_level => fnd_log.level_statement,
647 p_module_prefix => l_module_prefix,
648 p_module => l_module
649 );
650 END IF;
651
652 END IF;
653 END IF;
654 -------End of status check for usage level ----------+
655
656 IF x_status = FND_API.g_ret_sts_success THEN
657 --hk_debugt(LL_MAP_DTLS_TBL); ----- Code to display the output.
658 -- Set the address validation status to success since x_statusis success
659 X_ADDR_VAL_STATUS := x_status;
660 IF P_LOCATION_ID IS NOT NULL THEN
661
662 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
663 hz_utility_v2pub.debug
664 (p_message => ' Location id is not null. Call fill_values, create_gnr and Return back.',
665 p_prefix => l_debug_prefix,
666 p_msg_level => fnd_log.level_statement,
667 p_module_prefix => l_module_prefix,
668 p_module => l_module
669 );
670 END IF;
671
672 HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);
673 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
674 L_USAGE_CODE,'S',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);
675 END IF;
676
677 X_CALL_MAP := 'N';
678 RETURN;
679
680 ELSE
681
682 IF P_LOCATION_ID IS NOT NULL THEN
683 --hk_debugl('Table that is returned by Validate For Map');
684 --hk_debugt(LL_MAP_DTLS_TBL);
685 --hk_debugl('Usage Map Table With loc comp values');
686 --hk_debugt(L_MDU_TBL);
687 IF HZ_GNR_UTIL_PKG.do_usage_val(l_cause,L_MAP_DTLS_TBL,L_MDU_TBL,LL_MAP_DTLS_TBL,l_status) = FALSE THEN
688
689 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
690 hz_utility_v2pub.debug
691 (p_message => ' No usage level validation is required. Call create_gnr with the map status',
692 p_prefix => l_debug_prefix,
693 p_msg_level => fnd_log.level_statement,
694 p_module_prefix => l_module_prefix,
695 p_module => l_module
696 );
697 END IF;
698
699 -- This means no usage level validation is required
700 IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_ERROR THEN
701
702 HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);
703 -- This below call is to derive the address validation status and set the message
704 X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,'E',x_status);
705 --hk_debugl('Calling create_gnr With Map_status "E"');
706
707 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
708 hz_utility_v2pub.debug
709 (p_message => ' Calling create_gnr with map status E.',
710 p_prefix => l_debug_prefix,
714 );
711 p_msg_level => fnd_log.level_statement,
712 p_module_prefix => l_module_prefix,
713 p_module => l_module
715 END IF;
716
717 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
718 L_USAGE_CODE,'E',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);
719 --hk_debugl('Status after create_gnr : '||l_status);
720 ELSE
721
722 HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);
723 -- This below call is to derive the address validation status and set the message
724 X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,'S',x_status);
725 --hk_debugl('Calling create_gnr With Map_status "S"');
726
727 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
728 hz_utility_v2pub.debug
729 (p_message => ' Calling create_gnr with map status S.',
730 p_prefix => l_debug_prefix,
731 p_msg_level => fnd_log.level_statement,
732 p_module_prefix => l_module_prefix,
733 p_module => l_module
734 );
735 END IF;
736
737 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
738 L_USAGE_CODE,'S',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);
739 --hk_debugl('Status after create_gnr : '||l_status);
740 x_status := FND_API.g_ret_sts_success;
741 END IF;
742
743 X_CALL_MAP := 'N';
744 RETURN;
745
746 ELSE
747 NULL; -- do_usage_val has concluded that usage level validation has to go through.
748 END IF;
749 END IF;
750 END IF;
751
752 l_get_addr_val := 'Y';
753 X_CALL_MAP := 'N';
754
755 END IF;
756
757 IF L_MDU_TBL.COUNT = 1 THEN
758
759 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
760 hz_utility_v2pub.debug
761 (p_message => ' This means country is the only required mapped column for validation. Call create_gnr with map status S',
762 p_prefix => l_debug_prefix,
763 p_msg_level => fnd_log.level_statement,
764 p_module_prefix => l_module_prefix,
765 p_module => l_module
766 );
767 END IF;
768
769 -- This means country is the only required mapped column for validation.
770 -- and country is already populated in the L_MDU_TBL in the initialization section of this package.
771 --hk_debugt(L_MDU_TBL); ----- Code to display the output.
772 --hk_debugl('Calling create_gnr With Map_status "S"');
773 -- This below call is to derive the address validation status and set the message
774 X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(L_MDU_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,'S',x_status);
775
776 IF P_LOCATION_ID IS NOT NULL THEN
777 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
778 L_USAGE_CODE,'S',L_LOC_COMPONENTS_REC,p_lock_flag,L_MDU_TBL,l_status);
779 END IF;
780
781 x_status := FND_API.g_ret_sts_success;
782 RETURN;
783 END IF;
784 --hk_debugl('L_MDU_TBL has count count more than 1');
785
786 l_value2 := NVL(L_MDU_TBL(2).LOC_COMPVAL,'X') ;
787 IF l_value2 = 'X' THEN
788 l_type2 := 'X';
789 ELSE
790 l_type2 := L_MDU_TBL(2).GEOGRAPHY_TYPE;
791 -- store the geography_type of the lowest address component that has a value passed in
792 l_geography_type := l_type2;
793 END IF;
794
795 l_value3 := NVL(L_MDU_TBL(3).LOC_COMPVAL,'X') ;
796 IF l_value3 = 'X' THEN
797 l_type3 := 'X';
798 ELSE
799 l_type3 := L_MDU_TBL(3).GEOGRAPHY_TYPE;
800 -- store the geography_type of the lowest address component that has a value passed in
801 l_geography_type := l_type3;
802 END IF;
803
804 l_value4 := NVL(L_MDU_TBL(4).LOC_COMPVAL,'X') ;
805 IF l_value4 = 'X' THEN
806 l_type4 := 'X';
807 ELSE
808 l_type4 := L_MDU_TBL(4).GEOGRAPHY_TYPE;
809 -- store the geography_type of the lowest address component that has a value passed in
810 l_geography_type := l_type4;
811 END IF;
812
813
814 LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
815 l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);
816 --hk_debugl('The SQL query');
817 --hk_debugl(l_sql);
818
819 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
820 hz_utility_v2pub.debug
821 (p_message => ' The SQL query : '||l_sql,
822 p_prefix => l_debug_prefix,
823 p_msg_level => fnd_log.level_statement,
824 p_module_prefix => l_module_prefix,
825 p_module => l_module
826 );
827 END IF;
828
829 OPEN c_getGeo FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id
833 IF c_getGeo%NOTFOUND THEN
830 ,l_type2,l_value2,l_type3,l_value3,l_type4,l_value4,l_geography_type;
831 FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID
832 ,LL_MAP_DTLS_TBL(2).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(3).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(4).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
834 --hk_debugl('No Match found for the usage level search');
835
836 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
837 hz_utility_v2pub.debug
838 (p_message => ' No Match found for the usage level search ',
839 p_prefix => l_debug_prefix,
840 p_msg_level => fnd_log.level_statement,
841 p_module_prefix => l_module_prefix,
842 p_module => l_module
843 );
844 END IF;
845
846 HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status);
847 x_status := FND_API.G_RET_STS_ERROR;
848 ELSE
849 --Fetching once more to see where there are multiple records
850
851 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
852 hz_utility_v2pub.debug
853 (p_message => ' Fetching once more to see where there are multiple records ',
854 p_prefix => l_debug_prefix,
855 p_msg_level => fnd_log.level_statement,
856 p_module_prefix => l_module_prefix,
857 p_module => l_module
858 );
859 END IF;
860
861 FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID
862 ,LL_MAP_DTLS_TBL(2).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(3).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(4).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
863 IF c_getGeo%FOUND THEN -- not able to identify a unique record
864
865 -- Get the query again with identifier type as NAME if multiple match found
866 -- If it returns a record, we are able to derive a unique record for identifier type as NAME
867 l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);
868 OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id
869 ,l_type2,l_value2,l_type3,l_value3,l_type4,l_value4,l_geography_type;
870
871 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
872 hz_utility_v2pub.debug
873 (p_message => 'Before the fetch of the query with identifier type as NAME after multiple match found',
874 p_prefix => l_debug_prefix,
875 p_msg_level => fnd_log.level_statement,
876 p_module_prefix => l_module_prefix,
877 p_module => l_module
878 );
879 END IF;
880
881 --hk_debugt(LL_MAP_DTLS_TBL);
882 FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID
883 ,LL_MAP_DTLS_TBL(2).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(3).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(4).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
884 IF c_getGeo1%FOUND THEN
885 -- check if there is another row with same STANDARD_NAME, in that case it is error case
886 FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID
887 ,LL_MAP_DTLS_TBL(2).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(3).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(4).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
888 IF c_getGeo1%NOTFOUND THEN -- success (only 1 rec with same primary name exists)
889
890 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
891 hz_utility_v2pub.debug
892 (p_message => 'GEO-Able to find a unique record or a record with multiple parent flag = Y with identifier type as NAME',
893 p_prefix => l_debug_prefix,
894 p_msg_level => fnd_log.level_statement,
895 p_module_prefix => l_module_prefix,
896 p_module => l_module
897 );
898 END IF;
899
900 ELSE -- Not able to find a unique record with identifier type as NAME
901
902 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
903 hz_utility_v2pub.debug
904 (p_message => 'GEO-Not able to find a record with with identifier type as NAME. '||
905 ' More than 1 rec exists with same STANDARD NAME',
906 p_prefix => l_debug_prefix,
907 p_msg_level => fnd_log.level_statement,
908 p_module_prefix => l_module_prefix,
909 p_module => l_module
910 );
911 END IF;
912
913 LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
914 HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status);
915 x_status := FND_API.G_RET_STS_ERROR;
916 END IF;
917
918 ELSE -- Not able to found a unique record with identifier type as NAME
919
920 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
921 hz_utility_v2pub.debug
922 (p_message => ' Not able to find a record with with identifier type as NAME. ',
923 p_prefix => l_debug_prefix,
924 p_msg_level => fnd_log.level_statement,
928 END IF;
925 p_module_prefix => l_module_prefix,
926 p_module => l_module
927 );
929
930 LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
931 x_status := FND_API.G_RET_STS_ERROR;
932 END IF;
933 CLOSE c_getGeo1;
934
935 ELSE -- a unique record or a record with multiple parent flag = Y is found
936
937 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
938 hz_utility_v2pub.debug
939 (p_message => ' A unique record or a record with multiple parent flag = Y is found ',
940 p_prefix => l_debug_prefix,
941 p_msg_level => fnd_log.level_statement,
942 p_module_prefix => l_module_prefix,
943 p_module => l_module
944 );
945 END IF;
946
947 END IF;
948
949 IF l_multiple_parent_flag = 'Y' AND x_status <> FND_API.G_RET_STS_ERROR THEN
950 IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,LL_MAP_DTLS_TBL) = TRUE THEN
951 NULL;
952 ELSE -- Multiple parent case not able to find a unique record
953
954 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
955 hz_utility_v2pub.debug
956 (p_message => ' Multiple parent case not able to find a unique record',
957 p_prefix => l_debug_prefix,
958 p_msg_level => fnd_log.level_statement,
959 p_module_prefix => l_module_prefix,
960 p_module => l_module
961 );
962 END IF;
963
964 x_status := FND_API.G_RET_STS_ERROR;
965 END IF;
966
967 ELSE -- a unique record is found
968
969 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
970 hz_utility_v2pub.debug
971 (p_message => ' A unique record is found ',
972 p_prefix => l_debug_prefix,
973 p_msg_level => fnd_log.level_statement,
974 p_module_prefix => l_module_prefix,
975 p_module => l_module
976 );
977 END IF;
978
979 END IF;
980 END IF;
981 CLOSE c_getGeo;
982
983 --hk_debugl('Return STatus after first fetch : '||x_status);
984 --Following call will try to derive missing lower level compoents
985
986 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
987 hz_utility_v2pub.debug
988 (p_message => ' Return Status after first fetch : '||x_status,
989 p_prefix => l_debug_prefix,
990 p_msg_level => fnd_log.level_statement,
991 p_module_prefix => l_module_prefix,
992 p_module => l_module
993 );
994 END IF;
995
996 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
997 hz_utility_v2pub.debug
998 (p_message => ' Calling fix_child. This call will try to derive missing lower level compoents.',
999 p_prefix => l_debug_prefix,
1000 p_msg_level => fnd_log.level_statement,
1001 p_module_prefix => l_module_prefix,
1002 p_module => l_module
1003 );
1004 END IF;
1005
1006 IF HZ_GNR_UTIL_PKG.fix_child(LL_MAP_DTLS_TBL) = FALSE THEN
1007 x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL);
1008 END IF;
1009
1010 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1011 hz_utility_v2pub.debug
1012 (p_message => ' Return status after fix_child '||x_status,
1013 p_prefix => l_debug_prefix,
1014 p_msg_level => fnd_log.level_statement,
1015 p_module_prefix => l_module_prefix,
1016 p_module => l_module
1017 );
1018 END IF;
1019
1020 --hk_debugl('LL_MAP_DTLS_TBL before fill_values');
1021 --hk_debugt(LL_MAP_DTLS_TBL);
1022 HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);
1023 --hk_debugl('LL_MAP_DTLS_TBL after fill_values');
1024 --hk_debugt(LL_MAP_DTLS_TBL);
1025
1026 IF x_status = FND_API.g_ret_sts_success THEN
1027 -- We need to call the getAddrValStatus only once. All other cases we are looking into x_call_map
1028 -- In some case the below code will execute with the x_call_map as N
1029 IF l_get_addr_val = 'Y' THEN
1030 -- This below call is to derive the address validation status and set the message
1031 X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,x_status,x_status);
1032 END IF;
1033
1034 IF P_LOCATION_ID IS NOT NULL THEN
1035
1036 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1037 hz_utility_v2pub.debug
1038 (p_message => ' Calling create_gnr with map status S.',
1039 p_prefix => l_debug_prefix,
1040 p_msg_level => fnd_log.level_statement,
1041 p_module_prefix => l_module_prefix,
1042 p_module => l_module
1046 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
1043 );
1044 END IF;
1045
1047 L_USAGE_CODE,'S',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);
1048 --hk_debugl('Prceossed GNR With Status : S and returned with Status : '||l_status);
1049 END IF;
1050 ELSE
1051 -- We need to call the getAddrValStatus only once. All other cases we are looking into x_call_map
1052 -- In some case the below code will execute with the x_call_map as N
1053 IF l_get_addr_val = 'Y' THEN
1054 -- This below call is to derive the address validation status and set the message
1055 X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,x_status,x_status);
1056 END IF;
1057
1058 IF P_LOCATION_ID IS NOT NULL THEN
1059
1060 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1061 hz_utility_v2pub.debug
1062 (p_message => ' Calling create_gnr with map status E.',
1063 p_prefix => l_debug_prefix,
1064 p_msg_level => fnd_log.level_statement,
1065 p_module_prefix => l_module_prefix,
1066 p_module => l_module
1067 );
1068 END IF;
1069
1070 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
1071 L_USAGE_CODE,'E',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);
1072 --hk_debugl('Prceossed GNR With Status : E and returned with Status : '||l_status);
1073 END IF;
1074 END IF;
1075
1076 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1077 hz_utility_v2pub.debug
1078 (p_message => 'End of validation for '||L_USAGE_CODE,
1079 p_prefix => l_debug_prefix,
1080 p_msg_level => fnd_log.level_procedure,
1081 p_module_prefix => l_module_prefix,
1082 p_module => l_module
1083 );
1084 END IF;
1085
1086 --hk_debugt(LL_MAP_DTLS_TBL); ----- Code to display the output.
1087
1088 END validateGeo;
1089
1090 PROCEDURE validateTax(
1091 P_LOCATION_ID IN NUMBER,
1092 P_COUNTRY IN VARCHAR2,
1093 P_STATE IN VARCHAR2,
1094 P_PROVINCE IN VARCHAR2,
1095 P_COUNTY IN VARCHAR2,
1096 P_CITY IN VARCHAR2,
1097 P_POSTAL_CODE IN VARCHAR2,
1098 P_POSTAL_PLUS4_CODE IN VARCHAR2,
1099 P_ATTRIBUTE1 IN VARCHAR2,
1100 P_ATTRIBUTE2 IN VARCHAR2,
1101 P_ATTRIBUTE3 IN VARCHAR2,
1102 P_ATTRIBUTE4 IN VARCHAR2,
1103 P_ATTRIBUTE5 IN VARCHAR2,
1104 P_ATTRIBUTE6 IN VARCHAR2,
1105 P_ATTRIBUTE7 IN VARCHAR2,
1106 P_ATTRIBUTE8 IN VARCHAR2,
1107 P_ATTRIBUTE9 IN VARCHAR2,
1108 P_ATTRIBUTE10 IN VARCHAR2,
1109 P_LOCK_FLAG IN VARCHAR2,
1110 X_CALL_MAP IN OUT NOCOPY VARCHAR2,
1111 P_CALLED_FROM IN VARCHAR2,
1112 P_ADDR_VAL_LEVEL IN VARCHAR2,
1113 X_ADDR_WARN_MSG OUT NOCOPY VARCHAR2,
1114 X_ADDR_VAL_STATUS OUT NOCOPY VARCHAR2,
1115 X_STATUS OUT NOCOPY VARCHAR2) IS
1116
1117 l_loc_components_rec HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE;
1118
1119 TYPE getGeo IS REF CURSOR;
1120 c_getGeo getGeo;
1121 c_getGeo1 getGeo;
1122
1123 l_multiple_parent_flag VARCHAR2(1);
1124 l_sql VARCHAR2(9000);
1125 l_cause VARCHAR2(30);
1126 l_usage_code VARCHAR2(30);
1127 l_usage_id NUMBER;
1128 l_status VARCHAR2(1);
1129 l_get_addr_val VARCHAR2(1);
1130 l_geography_type VARCHAR2(30);
1131 l_geography_id NUMBER;
1132 L_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
1133 LL_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
1134 L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
1135
1136 l_module_prefix CONSTANT VARCHAR2(30) := 'HZ:ARHGNRGB:HZ_GNR_MAP10000';
1137 l_module CONSTANT VARCHAR2(30) := 'ADDRESS_VALIDATION';
1138 l_debug_prefix VARCHAR2(30) := p_location_id;
1139
1140 l_value1 VARCHAR2(360);
1141 l_type1 VARCHAR2(30);
1142 l_value2 VARCHAR2(360);
1143 l_type2 VARCHAR2(30);
1144 l_value3 VARCHAR2(360);
1145 l_type3 VARCHAR2(30);
1146 l_value4 VARCHAR2(360);
1147 l_type4 VARCHAR2(30);
1148
1149 BEGIN
1150
1151 -- defaulting the sucess status
1152 x_status := FND_API.g_ret_sts_success;
1153 --hk_debugl('Processing Location record with location_id :- '||nvl(to_char(p_location_id),'NULL_LOCATION_ID'));
1154 l_loc_components_rec.COUNTRY := P_COUNTRY;
1155 l_loc_components_rec.STATE := P_STATE;
1156 l_loc_components_rec.PROVINCE := P_PROVINCE;
1157 l_loc_components_rec.COUNTY := P_COUNTY;
1158 l_loc_components_rec.CITY := P_CITY;
1162 l_loc_components_rec.ATTRIBUTE2 := P_ATTRIBUTE2;
1159 l_loc_components_rec.POSTAL_CODE := P_POSTAL_CODE;
1160 l_loc_components_rec.POSTAL_PLUS4_CODE := P_POSTAL_PLUS4_CODE;
1161 l_loc_components_rec.ATTRIBUTE1 := P_ATTRIBUTE1;
1163 l_loc_components_rec.ATTRIBUTE3 := P_ATTRIBUTE3;
1164 l_loc_components_rec.ATTRIBUTE4 := P_ATTRIBUTE4;
1165 l_loc_components_rec.ATTRIBUTE5 := P_ATTRIBUTE5;
1166 l_loc_components_rec.ATTRIBUTE6 := P_ATTRIBUTE6;
1167 l_loc_components_rec.ATTRIBUTE7 := P_ATTRIBUTE7;
1168 l_loc_components_rec.ATTRIBUTE8 := P_ATTRIBUTE8;
1169 l_loc_components_rec.ATTRIBUTE9 := P_ATTRIBUTE9;
1170 l_loc_components_rec.ATTRIBUTE10 := P_ATTRIBUTE10;
1171
1172 L_USAGE_ID := 10006;
1173 L_USAGE_CODE := 'TAX';
1174 L_MDU_TBL := G_MDU_TBL10006;
1175 L_MAP_DTLS_TBL := G_MAP_DTLS_TBL;
1176 l_get_addr_val := 'N';
1177
1178 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1179 hz_utility_v2pub.debug
1180 (p_message => 'Begin of validation for '||L_USAGE_CODE,
1181 p_prefix => l_debug_prefix,
1182 p_msg_level => fnd_log.level_procedure,
1183 p_module_prefix => l_module_prefix,
1184 p_module => l_module
1185 );
1186 END IF;
1187
1188 IF P_LOCATION_ID IS NOT NULL AND P_CALLED_FROM <> 'GNR' THEN
1189 --hk_debugl('Before check_GNR_For_Usage');
1190 IF HZ_GNR_UTIL_PKG.check_GNR_For_Usage(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
1191 L_USAGE_CODE,L_MDU_TBL,x_status) = TRUE THEN
1192 --hk_debugl('After check_GNR_For_Usage with status :- '||x_status);
1193
1194 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1195 hz_utility_v2pub.debug
1196 (p_message => 'There is already a procedded success record in GNR log table.',
1197 p_prefix => l_debug_prefix,
1198 p_msg_level => fnd_log.level_statement,
1199 p_module_prefix => l_module_prefix,
1200 p_module => l_module
1201 );
1202 END IF;
1203
1204 x_status := FND_API.g_ret_sts_success;
1205 X_ADDR_VAL_STATUS := x_status;
1206 RETURN;
1207 END IF;
1208 END IF;
1209
1210 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1211 hz_utility_v2pub.debug
1212 (p_message => 'Not able to find an existing success record in GNR log table.',
1213 p_prefix => l_debug_prefix,
1214 p_msg_level => fnd_log.level_statement,
1215 p_module_prefix => l_module_prefix,
1216 p_module => l_module
1217 );
1218 END IF;
1219
1220 -- After the following call L_MAP_DTLS_TBL will have the components value populated.
1221 HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MAP_DTLS_TBL,x_status);
1222
1223 -- Below code will overwrite the LOC_COMPVAL for COUNTRY to COUNTRY_CODE
1224 -- This change is to update COUNTRY column in locations table with COUNTRY_CODE
1225 -- even if the table has Country name in this column and the validation is success
1226 L_MAP_DTLS_TBL(1).LOC_COMPVAL := G_MAP_REC.COUNTRY_CODE;
1227
1228 -- After the following call L_MDU_TBL will have the components value populated.
1229 HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MDU_TBL,x_status);
1230
1231 --hk_debugl(' value of X_CALL_MAP : '||X_CALL_MAP);
1232 IF X_CALL_MAP = 'Y' THEN
1233 LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
1234
1235 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1236 hz_utility_v2pub.debug
1237 (p_message => 'Before calling validate for Map ',
1238 p_prefix => l_debug_prefix,
1239 p_msg_level => fnd_log.level_statement,
1240 p_module_prefix => l_module_prefix,
1241 p_module => l_module
1242 );
1243 END IF;
1244
1245 validateForMap(L_LOC_COMPONENTS_REC,LL_MAP_DTLS_TBL,l_cause,x_status);
1246 --hk_debugl('Back from Validate for Map with status :- '||x_status||'.. and case :'||l_cause);
1247
1248 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1249 hz_utility_v2pub.debug
1250 (p_message => 'Back from Validate for Map with status : .'||x_status||'.. and case :'||l_cause,
1251 p_prefix => l_debug_prefix,
1252 p_msg_level => fnd_log.level_statement,
1253 p_module_prefix => l_module_prefix,
1254 p_module => l_module
1255 );
1256 END IF;
1257
1258 -- This usage level check is required upfront because usage level validation will ignore
1259 -- some of the passed in parameters for the complete mapping and may result in wrong status
1260 IF (x_status = FND_API.g_ret_sts_error) THEN
1261
1262 -- hk_debugl('Trying to check if usage level validation is success even with map validation as error..');
1263 -- hk_debugl('TABLE that is returned by Validate For Map');
1264 -- hk_debugt(LL_MAP_DTLS_TBL);
1265 -- hk_debugl('Usage Map Table With loc comp values');
1266 -- hk_debugt(L_MDU_TBL);
1267
1271
1268 IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_SUCCESS THEN
1269 -- hk_debugl('COMPLETE mapping is error but is sufficient for passed usage. So setting X_STATUS to success');
1270 x_status := FND_API.g_ret_sts_success;
1272 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1273 hz_utility_v2pub.debug
1274 (p_message => 'COMPLETE mapping is error but is sufficient for passed usage. So setting X_STATUS to success',
1275 p_prefix => l_debug_prefix,
1276 p_msg_level => fnd_log.level_statement,
1277 p_module_prefix => l_module_prefix,
1278 p_module => l_module
1279 );
1280 END IF;
1281
1282 END IF;
1283 END IF;
1284 -------End of status check for usage level ----------+
1285
1286 IF x_status = FND_API.g_ret_sts_success THEN
1287 --hk_debugt(LL_MAP_DTLS_TBL); ----- Code to display the output.
1288 -- Set the address validation status to success since x_statusis success
1289 X_ADDR_VAL_STATUS := x_status;
1290 IF P_LOCATION_ID IS NOT NULL THEN
1291
1292 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1293 hz_utility_v2pub.debug
1294 (p_message => ' Location id is not null. Call fill_values, create_gnr and Return back.',
1295 p_prefix => l_debug_prefix,
1296 p_msg_level => fnd_log.level_statement,
1297 p_module_prefix => l_module_prefix,
1298 p_module => l_module
1299 );
1300 END IF;
1301
1302 HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);
1303 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
1304 L_USAGE_CODE,'S',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);
1305 END IF;
1306
1307 X_CALL_MAP := 'N';
1308 RETURN;
1309
1310 ELSE
1311
1312 IF P_LOCATION_ID IS NOT NULL THEN
1313 --hk_debugl('Table that is returned by Validate For Map');
1314 --hk_debugt(LL_MAP_DTLS_TBL);
1315 --hk_debugl('Usage Map Table With loc comp values');
1316 --hk_debugt(L_MDU_TBL);
1317 IF HZ_GNR_UTIL_PKG.do_usage_val(l_cause,L_MAP_DTLS_TBL,L_MDU_TBL,LL_MAP_DTLS_TBL,l_status) = FALSE THEN
1318
1319 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1320 hz_utility_v2pub.debug
1321 (p_message => ' No usage level validation is required. Call create_gnr with the map status',
1322 p_prefix => l_debug_prefix,
1323 p_msg_level => fnd_log.level_statement,
1324 p_module_prefix => l_module_prefix,
1325 p_module => l_module
1326 );
1327 END IF;
1328
1329 -- This means no usage level validation is required
1330 IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_ERROR THEN
1331
1332 HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);
1333 -- This below call is to derive the address validation status and set the message
1334 X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,'E',x_status);
1335 --hk_debugl('Calling create_gnr With Map_status "E"');
1336
1337 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1338 hz_utility_v2pub.debug
1339 (p_message => ' Calling create_gnr with map status E.',
1340 p_prefix => l_debug_prefix,
1341 p_msg_level => fnd_log.level_statement,
1342 p_module_prefix => l_module_prefix,
1343 p_module => l_module
1344 );
1345 END IF;
1346
1347 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
1348 L_USAGE_CODE,'E',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);
1349 --hk_debugl('Status after create_gnr : '||l_status);
1350 ELSE
1351
1352 HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);
1353 -- This below call is to derive the address validation status and set the message
1354 X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,'S',x_status);
1355 --hk_debugl('Calling create_gnr With Map_status "S"');
1356
1357 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1358 hz_utility_v2pub.debug
1359 (p_message => ' Calling create_gnr with map status S.',
1360 p_prefix => l_debug_prefix,
1361 p_msg_level => fnd_log.level_statement,
1362 p_module_prefix => l_module_prefix,
1363 p_module => l_module
1364 );
1365 END IF;
1366
1367 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
1368 L_USAGE_CODE,'S',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);
1372
1369 --hk_debugl('Status after create_gnr : '||l_status);
1370 x_status := FND_API.g_ret_sts_success;
1371 END IF;
1373 X_CALL_MAP := 'N';
1374 RETURN;
1375
1376 ELSE
1377 NULL; -- do_usage_val has concluded that usage level validation has to go through.
1378 END IF;
1379 END IF;
1380 END IF;
1381
1382 l_get_addr_val := 'Y';
1383 X_CALL_MAP := 'N';
1384
1385 END IF;
1386
1387 IF L_MDU_TBL.COUNT = 1 THEN
1388
1389 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1390 hz_utility_v2pub.debug
1391 (p_message => ' This means country is the only required mapped column for validation. Call create_gnr with map status S',
1392 p_prefix => l_debug_prefix,
1393 p_msg_level => fnd_log.level_statement,
1394 p_module_prefix => l_module_prefix,
1395 p_module => l_module
1396 );
1397 END IF;
1398
1399 -- This means country is the only required mapped column for validation.
1400 -- and country is already populated in the L_MDU_TBL in the initialization section of this package.
1401 --hk_debugt(L_MDU_TBL); ----- Code to display the output.
1402 --hk_debugl('Calling create_gnr With Map_status "S"');
1403 -- This below call is to derive the address validation status and set the message
1404 X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(L_MDU_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,'S',x_status);
1405
1406 IF P_LOCATION_ID IS NOT NULL THEN
1407 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
1408 L_USAGE_CODE,'S',L_LOC_COMPONENTS_REC,p_lock_flag,L_MDU_TBL,l_status);
1409 END IF;
1410
1411 x_status := FND_API.g_ret_sts_success;
1412 RETURN;
1413 END IF;
1414 --hk_debugl('L_MDU_TBL has count count more than 1');
1415
1416 l_value2 := NVL(L_MDU_TBL(2).LOC_COMPVAL,'X') ;
1417 IF l_value2 = 'X' THEN
1418 l_type2 := 'X';
1419 ELSE
1420 l_type2 := L_MDU_TBL(2).GEOGRAPHY_TYPE;
1421 -- store the geography_type of the lowest address component that has a value passed in
1422 l_geography_type := l_type2;
1423 END IF;
1424
1425 l_value3 := NVL(L_MDU_TBL(3).LOC_COMPVAL,'X') ;
1426 IF l_value3 = 'X' THEN
1427 l_type3 := 'X';
1428 ELSE
1429 l_type3 := L_MDU_TBL(3).GEOGRAPHY_TYPE;
1430 -- store the geography_type of the lowest address component that has a value passed in
1431 l_geography_type := l_type3;
1432 END IF;
1433
1434 l_value4 := NVL(L_MDU_TBL(4).LOC_COMPVAL,'X') ;
1435 IF l_value4 = 'X' THEN
1436 l_type4 := 'X';
1437 ELSE
1438 l_type4 := L_MDU_TBL(4).GEOGRAPHY_TYPE;
1439 -- store the geography_type of the lowest address component that has a value passed in
1440 l_geography_type := l_type4;
1441 END IF;
1442
1443
1444 LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
1445 l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);
1446 --hk_debugl('The SQL query');
1447 --hk_debugl(l_sql);
1448
1449 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1450 hz_utility_v2pub.debug
1451 (p_message => ' The SQL query : '||l_sql,
1452 p_prefix => l_debug_prefix,
1453 p_msg_level => fnd_log.level_statement,
1454 p_module_prefix => l_module_prefix,
1455 p_module => l_module
1456 );
1457 END IF;
1458
1459 OPEN c_getGeo FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id
1460 ,l_type2,l_value2,l_type3,l_value3,l_type4,l_value4,l_geography_type;
1461 FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID
1462 ,LL_MAP_DTLS_TBL(2).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(3).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(4).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
1463 IF c_getGeo%NOTFOUND THEN
1464 --hk_debugl('No Match found for the usage level search');
1465
1466 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1467 hz_utility_v2pub.debug
1468 (p_message => ' No Match found for the usage level search ',
1469 p_prefix => l_debug_prefix,
1470 p_msg_level => fnd_log.level_statement,
1471 p_module_prefix => l_module_prefix,
1472 p_module => l_module
1473 );
1474 END IF;
1475
1476 HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status);
1477 x_status := FND_API.G_RET_STS_ERROR;
1478 ELSE
1479 --Fetching once more to see where there are multiple records
1480
1481 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1482 hz_utility_v2pub.debug
1483 (p_message => ' Fetching once more to see where there are multiple records ',
1484 p_prefix => l_debug_prefix,
1485 p_msg_level => fnd_log.level_statement,
1486 p_module_prefix => l_module_prefix,
1487 p_module => l_module
1488 );
1489 END IF;
1490
1494
1491 FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID
1492 ,LL_MAP_DTLS_TBL(2).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(3).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(4).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
1493 IF c_getGeo%FOUND THEN -- not able to identify a unique record
1495 -- Get the query again with identifier type as NAME if multiple match found
1496 -- If it returns a record, we are able to derive a unique record for identifier type as NAME
1497 l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);
1498 OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id
1499 ,l_type2,l_value2,l_type3,l_value3,l_type4,l_value4,l_geography_type;
1500
1501 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1502 hz_utility_v2pub.debug
1503 (p_message => 'Before the fetch of the query with identifier type as NAME after multiple match found',
1504 p_prefix => l_debug_prefix,
1505 p_msg_level => fnd_log.level_statement,
1506 p_module_prefix => l_module_prefix,
1507 p_module => l_module
1508 );
1509 END IF;
1510
1511 --hk_debugt(LL_MAP_DTLS_TBL);
1512 FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID
1513 ,LL_MAP_DTLS_TBL(2).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(3).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(4).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
1514 IF c_getGeo1%FOUND THEN
1515 -- check if there is another row with same STANDARD_NAME, in that case it is error case
1516 FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID
1517 ,LL_MAP_DTLS_TBL(2).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(3).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(4).GEOGRAPHY_ID,LL_MAP_DTLS_TBL(5).GEOGRAPHY_ID;
1518 IF c_getGeo1%NOTFOUND THEN -- success (only 1 rec with same primary name exists)
1519
1520 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1521 hz_utility_v2pub.debug
1522 (p_message => 'GEO-Able to find a unique record or a record with multiple parent flag = Y with identifier type as NAME',
1523 p_prefix => l_debug_prefix,
1524 p_msg_level => fnd_log.level_statement,
1525 p_module_prefix => l_module_prefix,
1526 p_module => l_module
1527 );
1528 END IF;
1529
1530 ELSE -- Not able to find a unique record with identifier type as NAME
1531
1532 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1533 hz_utility_v2pub.debug
1534 (p_message => 'GEO-Not able to find a record with with identifier type as NAME. '||
1535 ' More than 1 rec exists with same STANDARD NAME',
1536 p_prefix => l_debug_prefix,
1537 p_msg_level => fnd_log.level_statement,
1538 p_module_prefix => l_module_prefix,
1539 p_module => l_module
1540 );
1541 END IF;
1542
1543 LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
1544 HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status);
1545 x_status := FND_API.G_RET_STS_ERROR;
1546 END IF;
1547
1548 ELSE -- Not able to found a unique record with identifier type as NAME
1549
1550 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1551 hz_utility_v2pub.debug
1552 (p_message => ' Not able to find a record with with identifier type as NAME. ',
1553 p_prefix => l_debug_prefix,
1554 p_msg_level => fnd_log.level_statement,
1555 p_module_prefix => l_module_prefix,
1556 p_module => l_module
1557 );
1558 END IF;
1559
1560 LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;
1561 x_status := FND_API.G_RET_STS_ERROR;
1562 END IF;
1563 CLOSE c_getGeo1;
1564
1565 ELSE -- a unique record or a record with multiple parent flag = Y is found
1566
1567 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1568 hz_utility_v2pub.debug
1569 (p_message => ' A unique record or a record with multiple parent flag = Y is found ',
1570 p_prefix => l_debug_prefix,
1571 p_msg_level => fnd_log.level_statement,
1572 p_module_prefix => l_module_prefix,
1573 p_module => l_module
1574 );
1575 END IF;
1576
1577 END IF;
1578
1579 IF l_multiple_parent_flag = 'Y' AND x_status <> FND_API.G_RET_STS_ERROR THEN
1580 IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,LL_MAP_DTLS_TBL) = TRUE THEN
1581 NULL;
1582 ELSE -- Multiple parent case not able to find a unique record
1583
1584 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1585 hz_utility_v2pub.debug
1586 (p_message => ' Multiple parent case not able to find a unique record',
1587 p_prefix => l_debug_prefix,
1588 p_msg_level => fnd_log.level_statement,
1592 END IF;
1589 p_module_prefix => l_module_prefix,
1590 p_module => l_module
1591 );
1593
1594 x_status := FND_API.G_RET_STS_ERROR;
1595 END IF;
1596
1597 ELSE -- a unique record is found
1598
1599 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1600 hz_utility_v2pub.debug
1601 (p_message => ' A unique record is found ',
1602 p_prefix => l_debug_prefix,
1603 p_msg_level => fnd_log.level_statement,
1604 p_module_prefix => l_module_prefix,
1605 p_module => l_module
1606 );
1607 END IF;
1608
1609 END IF;
1610 END IF;
1611 CLOSE c_getGeo;
1612
1613 --hk_debugl('Return STatus after first fetch : '||x_status);
1614 --Following call will try to derive missing lower level compoents
1615
1616 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1617 hz_utility_v2pub.debug
1618 (p_message => ' Return Status after first fetch : '||x_status,
1619 p_prefix => l_debug_prefix,
1620 p_msg_level => fnd_log.level_statement,
1621 p_module_prefix => l_module_prefix,
1622 p_module => l_module
1623 );
1624 END IF;
1625
1626 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1627 hz_utility_v2pub.debug
1628 (p_message => ' Calling fix_child. This call will try to derive missing lower level compoents.',
1629 p_prefix => l_debug_prefix,
1630 p_msg_level => fnd_log.level_statement,
1631 p_module_prefix => l_module_prefix,
1632 p_module => l_module
1633 );
1634 END IF;
1635
1636 IF HZ_GNR_UTIL_PKG.fix_child(LL_MAP_DTLS_TBL) = FALSE THEN
1637 x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL);
1638 END IF;
1639
1640 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1641 hz_utility_v2pub.debug
1642 (p_message => ' Return status after fix_child '||x_status,
1643 p_prefix => l_debug_prefix,
1644 p_msg_level => fnd_log.level_statement,
1645 p_module_prefix => l_module_prefix,
1646 p_module => l_module
1647 );
1648 END IF;
1649
1650 --hk_debugl('LL_MAP_DTLS_TBL before fill_values');
1651 --hk_debugt(LL_MAP_DTLS_TBL);
1652 HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);
1653 --hk_debugl('LL_MAP_DTLS_TBL after fill_values');
1654 --hk_debugt(LL_MAP_DTLS_TBL);
1655
1656 IF x_status = FND_API.g_ret_sts_success THEN
1657 -- We need to call the getAddrValStatus only once. All other cases we are looking into x_call_map
1658 -- In some case the below code will execute with the x_call_map as N
1659 IF l_get_addr_val = 'Y' THEN
1660 -- This below call is to derive the address validation status and set the message
1661 X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,x_status,x_status);
1662 END IF;
1663
1664 IF P_LOCATION_ID IS NOT NULL THEN
1665
1666 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1667 hz_utility_v2pub.debug
1668 (p_message => ' Calling create_gnr with map status S.',
1669 p_prefix => l_debug_prefix,
1670 p_msg_level => fnd_log.level_statement,
1671 p_module_prefix => l_module_prefix,
1672 p_module => l_module
1673 );
1674 END IF;
1675
1676 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
1677 L_USAGE_CODE,'S',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);
1678 --hk_debugl('Prceossed GNR With Status : S and returned with Status : '||l_status);
1679 END IF;
1680 ELSE
1681 -- We need to call the getAddrValStatus only once. All other cases we are looking into x_call_map
1682 -- In some case the below code will execute with the x_call_map as N
1683 IF l_get_addr_val = 'Y' THEN
1684 -- This below call is to derive the address validation status and set the message
1685 X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,x_status,x_status);
1686 END IF;
1687
1688 IF P_LOCATION_ID IS NOT NULL THEN
1689
1690 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1691 hz_utility_v2pub.debug
1692 (p_message => ' Calling create_gnr with map status E.',
1693 p_prefix => l_debug_prefix,
1694 p_msg_level => fnd_log.level_statement,
1695 p_module_prefix => l_module_prefix,
1696 p_module => l_module
1697 );
1698 END IF;
1699
1700 HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,
1701 L_USAGE_CODE,'E',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);
1702 --hk_debugl('Prceossed GNR With Status : E and returned with Status : '||l_status);
1703 END IF;
1704 END IF;
1705
1706 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1707 hz_utility_v2pub.debug
1708 (p_message => 'End of validation for '||L_USAGE_CODE,
1709 p_prefix => l_debug_prefix,
1710 p_msg_level => fnd_log.level_procedure,
1711 p_module_prefix => l_module_prefix,
1712 p_module => l_module
1713 );
1714 END IF;
1715
1716 --hk_debugt(LL_MAP_DTLS_TBL); ----- Code to display the output.
1717
1718 END validateTax;
1719
1720 BEGIN
1721 G_COUNTRY_GEO_ID := 1;
1722 G_MAP_REC.MAP_ID := 10000;
1723 G_MAP_REC.COUNTRY_CODE := 'US';
1724 G_MAP_REC.LOC_TBL_NAME := 'HZ_LOCATIONS';
1725 G_MAP_REC.ADDRESS_STYLE := 'NULL';
1726
1727 G_MAP_DTLS_TBL(1).LOC_SEQ_NUM := 1;
1728 G_MAP_DTLS_TBL(1).LOC_COMPONENT := 'COUNTRY';
1729 G_MAP_DTLS_TBL(1).GEOGRAPHY_TYPE := 'COUNTRY';
1730 G_MAP_DTLS_TBL(1).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT1';
1731 G_MAP_DTLS_TBL(1).GEOGRAPHY_ID := 1;
1732 G_MAP_DTLS_TBL(2).LOC_SEQ_NUM := 2;
1733 G_MAP_DTLS_TBL(2).LOC_COMPONENT := 'STATE';
1734 G_MAP_DTLS_TBL(2).GEOGRAPHY_TYPE := 'STATE';
1735 G_MAP_DTLS_TBL(2).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT2';
1736 G_MAP_DTLS_TBL(3).LOC_SEQ_NUM := 3;
1737 G_MAP_DTLS_TBL(3).LOC_COMPONENT := 'COUNTY';
1738 G_MAP_DTLS_TBL(3).GEOGRAPHY_TYPE := 'COUNTY';
1739 G_MAP_DTLS_TBL(3).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT3';
1740 G_MAP_DTLS_TBL(4).LOC_SEQ_NUM := 4;
1741 G_MAP_DTLS_TBL(4).LOC_COMPONENT := 'CITY';
1742 G_MAP_DTLS_TBL(4).GEOGRAPHY_TYPE := 'CITY';
1743 G_MAP_DTLS_TBL(4).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT4';
1744 G_MAP_DTLS_TBL(5).LOC_SEQ_NUM := 5;
1745 G_MAP_DTLS_TBL(5).LOC_COMPONENT := 'POSTAL_CODE';
1746 G_MAP_DTLS_TBL(5).GEOGRAPHY_TYPE := 'POSTAL_CODE';
1747 G_MAP_DTLS_TBL(5).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT5';
1748
1749 G_USAGE_TBL(1).USAGE_ID := 10000;
1750 G_USAGE_TBL(1).MAP_ID := 10000;
1751 G_USAGE_TBL(1).USAGE_CODE := 'GEOGRAPHY';
1752 G_USAGE_DTLS_TBL(1).USAGE_ID := 10000;
1753 G_USAGE_DTLS_TBL(1).GEOGRAPHY_TYPE := 'CITY';
1754 G_USAGE_DTLS_TBL(2).USAGE_ID := 10000;
1755 G_USAGE_DTLS_TBL(2).GEOGRAPHY_TYPE := 'COUNTRY';
1756 G_USAGE_DTLS_TBL(3).USAGE_ID := 10000;
1757 G_USAGE_DTLS_TBL(3).GEOGRAPHY_TYPE := 'COUNTY';
1758 G_USAGE_DTLS_TBL(4).USAGE_ID := 10000;
1759 G_USAGE_DTLS_TBL(4).GEOGRAPHY_TYPE := 'STATE';
1760 G_MDU_TBL10000(1).LOC_SEQ_NUM := 1;
1761 G_MDU_TBL10000(1).LOC_COMPONENT := 'COUNTRY';
1762 G_MDU_TBL10000(1).GEOGRAPHY_TYPE := 'COUNTRY';
1763 G_MDU_TBL10000(1).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT1';
1764 G_MDU_TBL10000(1).GEOGRAPHY_ID := 1;
1765 G_MDU_TBL10000(2).LOC_SEQ_NUM := 2;
1766 G_MDU_TBL10000(2).LOC_COMPONENT := 'STATE';
1767 G_MDU_TBL10000(2).GEOGRAPHY_TYPE := 'STATE';
1768 G_MDU_TBL10000(2).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT2';
1769 G_MDU_TBL10000(3).LOC_SEQ_NUM := 3;
1770 G_MDU_TBL10000(3).LOC_COMPONENT := 'COUNTY';
1771 G_MDU_TBL10000(3).GEOGRAPHY_TYPE := 'COUNTY';
1772 G_MDU_TBL10000(3).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT3';
1773 G_MDU_TBL10000(4).LOC_SEQ_NUM := 4;
1774 G_MDU_TBL10000(4).LOC_COMPONENT := 'CITY';
1775 G_MDU_TBL10000(4).GEOGRAPHY_TYPE := 'CITY';
1776 G_MDU_TBL10000(4).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT4';
1777
1778 G_USAGE_TBL(2).USAGE_ID := 10006;
1779 G_USAGE_TBL(2).MAP_ID := 10000;
1780 G_USAGE_TBL(2).USAGE_CODE := 'TAX';
1781 G_USAGE_DTLS_TBL(5).USAGE_ID := 10006;
1782 G_USAGE_DTLS_TBL(5).GEOGRAPHY_TYPE := 'CITY';
1783 G_USAGE_DTLS_TBL(6).USAGE_ID := 10006;
1784 G_USAGE_DTLS_TBL(6).GEOGRAPHY_TYPE := 'COUNTRY';
1785 G_USAGE_DTLS_TBL(7).USAGE_ID := 10006;
1786 G_USAGE_DTLS_TBL(7).GEOGRAPHY_TYPE := 'COUNTY';
1787 G_USAGE_DTLS_TBL(8).USAGE_ID := 10006;
1788 G_USAGE_DTLS_TBL(8).GEOGRAPHY_TYPE := 'STATE';
1789 G_MDU_TBL10006(1).LOC_SEQ_NUM := 1;
1790 G_MDU_TBL10006(1).LOC_COMPONENT := 'COUNTRY';
1791 G_MDU_TBL10006(1).GEOGRAPHY_TYPE := 'COUNTRY';
1792 G_MDU_TBL10006(1).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT1';
1793 G_MDU_TBL10006(1).GEOGRAPHY_ID := 1;
1794 G_MDU_TBL10006(2).LOC_SEQ_NUM := 2;
1795 G_MDU_TBL10006(2).LOC_COMPONENT := 'STATE';
1796 G_MDU_TBL10006(2).GEOGRAPHY_TYPE := 'STATE';
1797 G_MDU_TBL10006(2).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT2';
1798 G_MDU_TBL10006(3).LOC_SEQ_NUM := 3;
1799 G_MDU_TBL10006(3).LOC_COMPONENT := 'COUNTY';
1800 G_MDU_TBL10006(3).GEOGRAPHY_TYPE := 'COUNTY';
1801 G_MDU_TBL10006(3).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT3';
1802 G_MDU_TBL10006(4).LOC_SEQ_NUM := 4;
1803 G_MDU_TBL10006(4).LOC_COMPONENT := 'CITY';
1804 G_MDU_TBL10006(4).GEOGRAPHY_TYPE := 'CITY';
1805 G_MDU_TBL10006(4).GEO_ELEMENT_COL := 'GEOGRAPHY_ELEMENT4';
1806
1807 END HZ_GNR_MAP10000;
[Home] [Help]