[Home] [Help]
PACKAGE BODY: APPS.HZ_GNR_GEN_PKG
Source
1 PACKAGE BODY HZ_GNR_GEN_PKG AS
2 /*$Header: ARHGNRGB.pls 120.32.12010000.2 2009/02/19 09:49:55 amstephe ship $ */
3 --------------------------------------
4 -- declaration of private global varibles
5 --------------------------------------
6 G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;
7 G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
8 G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;
9 G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;
10 g_pkgName VARCHAR2(30);
11
12 g_indent CONSTANT VARCHAR2(2) := ' ';
13 -- Hari 2 Lines
14 g_country_geo_id number;
15 g_type varchar2(1); -- need to be removed
16 --------------------------------------
17 -- forward referencing of private procedures
18 FUNCTION useCode (
19 p_geo_element IN VARCHAR2
20 ) RETURN boolean;
21
22 PROCEDURE genSpec(
23 x_status OUT NOCOPY VARCHAR2);
24
25 PROCEDURE genBody(
26 x_status OUT NOCOPY VARCHAR2);
27
28 -- PROCEDURE enable_debug;
29 -- PROCEDURE disable_debug;
30
31
32 PROCEDURE l(
33 str IN VARCHAR2
34 );
35
36 PROCEDURE li(
37 str IN VARCHAR2
38 );
39
40 PROCEDURE genPkgSpecHdr (
41 p_package_name IN VARCHAR2
42 );
43
44 PROCEDURE genPkgSpecTail (
45 p_package_name IN VARCHAR2
46 );
47
48 PROCEDURE procBegin (
49 p_procName IN VARCHAR2
50 );
51
52 PROCEDURE procEnd (
53 p_procName IN VARCHAR2
54 );
55
56 PROCEDURE funcBegin (
57 p_funcName IN VARCHAR2
58 );
59
60 PROCEDURE funcEnd (
61 p_funcName IN VARCHAR2
62 );
63
64 PROCEDURE validateSpec;
65
66 PROCEDURE validateBody (
67 x_status OUT NOCOPY VARCHAR2);
68
69 --------------------------------------
70 -- private procedures
71 /**
72 * PRIVATE PROCEDURE l, li, ll, lli, fp
73 *
74 * DESCRIPTION
75 * Utilities to write line or format line.
76 *
77 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
78 *
79 * ARGUMENTS
80 * IN:
81 * p_package_name Package name.
82 *
83 * NOTES
84 *
85 * MODIFICATION HISTORY
86 *
87 */
88 --------------------------------------
89 -- This would write a line in the buffer.
90 -- This would also introduce a new line char at the end of line.
91 PROCEDURE l(
92 str IN VARCHAR2
93 ) IS
94 BEGIN
95 HZ_GEN_PLSQL.add_line(str);
96 -- FND_FILE.PUT_LINE(FND_FILE.LOG,str);
97 -- dbms_output.put_line(str);
98 -- Hari 1 Line
99 --hk_store_plsql_proc(G_MAP_REC.MAP_ID,str||fnd_global.local_chr(10),G_TYPE);
100 END l;
101 --------------------------------------
102 -- This would write a line preceeded by an indent and line ends with
103 -- a new line char.
104 PROCEDURE li(
105 str IN VARCHAR2
106 ) IS
107 BEGIN
108 HZ_GEN_PLSQL.add_line(g_indent||str);
109 -- dbms_output.put_line(g_indent||str);
110 -- FND_FILE.PUT_LINE(FND_FILE.LOG,g_indent||str);
111 -- Hari 1 Line
112 --hk_store_plsql_proc(G_MAP_REC.MAP_ID,g_indent||str||fnd_global.local_chr(10),G_TYPE);
113
114 END li;
115 --------------------------------------
116 -- this would write the line in the buffer without the new line char at eol.
117 PROCEDURE ll(
118 str IN VARCHAR2
119 ) IS
120 BEGIN
121 HZ_GEN_PLSQL.add_line(str, false);
122 -- FND_FILE.PUT(FND_FILE.LOG,str);
123 -- dbms_output.put_line(str);
124 -- Hari 1 Line
125 --hk_store_plsql_proc(G_MAP_REC.MAP_ID,str,G_TYPE);
126 END ll;
127 --------------------------------------
128 -- this would write a line by preceeding with an indent and no new line char
129 -- at the end.
130 PROCEDURE lli(
131 str IN VARCHAR2
132 ) IS
133 BEGIN
134 HZ_GEN_PLSQL.add_line(g_indent||str, false);
135 -- FND_FILE.PUT(FND_FILE.LOG,g_indent||str);
136 -- dbms_output.put_line(g_indent||str);
137 -- Hari 1 Line
138 --hk_store_plsql_proc(G_MAP_REC.MAP_ID,g_indent||str,G_TYPE);
139 END lli;
140 --------------------------------------
141 FUNCTION fp(
142 p_parameter IN VARCHAR2
143 ) RETURN VARCHAR2 IS
144 BEGIN
145 RETURN RPAD(p_parameter,35);
146 END fp;
147 --------------------------------------
148 /**
149 * PRIVATE function useCode
150 *
151 * DESCRIPTION
152 * This function is used to inform if the GEO_ELEMENT_CODE
153 * column can be used in the where clause for any given
154 * geo element.
155 * This is needed as currently only geo elements 1 through 5 have
156 * code columns in the geography data model. This function
157 * need not be used once the ER#2907223.
158 * After that this function call will be unnecessary.
159 *
160 * ARGUMENTS
161 * IN:
162 * p_geo_element geo element column that must be parsed
163 *
164 * NOTES
165 *
166 * MODIFICATION HISTORY
167 *
168 */
169 --------------------------------------
170 FUNCTION useCode (
171 p_geo_element IN VARCHAR2
172 ) RETURN boolean is
173 BEGIN
174 IF (SUBSTR(p_geo_element, 18, 2) IN ('1','2','3','4','5')) THEN
175 -- dbms_output.put_line('using the code');
176 RETURN TRUE;
177 ELSE
178 -- dbms_output.put_line('not using the code');
179 RETURN FALSE;
180 END IF;
181 END useCode;
182 --------------------------------------
183
184 --------------------------------------
185 /**
186 * PRIVATE PROCEDURE genPkgSpecHdr
187 *
188 * DESCRIPTION
189 * Generate package header.
190 *
191 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
192 *
193 * ARGUMENTS
194 * IN:
195 * p_package_name Package name.
196 *
197 * NOTES
198 *
199 * MODIFICATION HISTORY
200 *
201 */
202 --------------------------------------
203 PROCEDURE genPkgSpecHdr (
204 p_package_name IN VARCHAR2
205 ) IS
206 BEGIN
207 -- new a package body object
208 HZ_GEN_PLSQL.new(p_package_name, 'PACKAGE');
209 l('CREATE OR REPLACE PACKAGE '||p_package_name||' AS');
210 l('');
211 l('/*=======================================================================+');
212 l(' | Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA|');
213 l(' | All rights reserved. |');
214 l(' +=======================================================================+');
215 l(' | NAME '||p_package_name);
216 l(' |');
217 l(' | DESCRIPTION');
218 l(' | This package body is generated by TCA for geoName referencing. ');
219 l(' |');
220 l(' | HISTORY');
221 l(' | '||TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MI:SS')||' Generated.');
222 l(' |');
223 l(' *=======================================================================*/');
224 l('');
225 END genPkgSpecHdr;
226 --------------------------------------
227 /**
228 * PRIVATE PROCEDURE genPkgBdyHdr
229 *
230 * DESCRIPTION
231 * Generate package Body header.
232 *
233 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
234 *
235 * ARGUMENTS
236 * IN:
237 * p_package_name Package name.
238 *
239 * NOTES
240 *
241 * MODIFICATION HISTORY
242 *
243 */
244 --------------------------------------
245 PROCEDURE genPkgBdyHdr (
246 p_package_name IN VARCHAR2
247 ) IS
248 BEGIN
249 -- new a package body object
250 HZ_GEN_PLSQL.new(p_package_name, 'PACKAGE BODY');
251 l('CREATE OR REPLACE PACKAGE BODY '||p_package_name||' AS');
252 l('');
253 l('/*=======================================================================+');
254 l(' | Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA|');
255 l(' | All rights reserved. |');
256 l(' +=======================================================================+');
257 l(' | NAME '||p_package_name);
258 l(' |');
259 l(' | DESCRIPTION');
260 l(' | This package body is generated by TCA for geoName referencing. ');
261 l(' |');
262 l(' | HISTORY');
263 l(' | '||TO_CHAR(SYSDATE,'MM/DD/YYYY HH:MI:SS')||' Generated.');
264 l(' |');
265 l(' *=======================================================================*/');
266 l(' ');
267 END genPkgBdyHdr;
268 --------------------------------------
269 /**
270 * PRIVATE PROCEDURE genPkgSpecTail
271 *
272 * DESCRIPTION
273 * Generate package tail.
274 *
275 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
276 *
277 * ARGUMENTS
278 * IN:
279 * p_package_name Package name.
280 *
281 * NOTES
282 *
283 * MODIFICATION HISTORY
284 *
285 *
286 */
287 --------------------------------------
288 PROCEDURE genPkgSpecTail (
289 p_package_name IN VARCHAR2
290 ) IS
291 BEGIN
292 l('END '||p_package_name||';');
293 -- compile the package.
294 HZ_GEN_PLSQL.compile_code;
295 END genPkgSpecTail;
296 --------------------------------------
297 /**
298 * PRIVATE PROCEDURE populate_mdu_tbl
299 *
300 * DESCRIPTION
301 * This populates a table of records of map_loc_rec for a usage.
302 *
303 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
304 *
305 * ARGUMENTS
306 * IN:
307 * p_usage_id usage_id
308 * OUT:
309 * x_mdu_tbl Table of records of map_loc_rec type
310 *
311 * NOTES
312 *
313 * MODIFICATION HISTORY
314 *
315 *
316 */
317 --------------------------------------
318 PROCEDURE populate_mdu_tbl(p_usage_id in number,x_mdu_tbl OUT NOCOPY HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE) IS
319 i number := 0;
320 FUNCTION exists_mapping(p_usage_id in number,p_geography_type in varchar2) return boolean IS
321 j NUMBER;
322 BEGIN
323 j:= 0;
324 IF G_USAGE_DTLS_TBL.COUNT > 0 THEN
325 j := G_USAGE_DTLS_TBL.FIRST;
326 LOOP
327 IF G_USAGE_DTLS_TBL(j).USAGE_ID = p_usage_id AND G_USAGE_DTLS_TBL(j).GEOGRAPHY_TYPE = p_geography_type THEN
328 RETURN TRUE;
329 END IF;
330 EXIT WHEN j = G_USAGE_DTLS_TBL.LAST;
331 j := G_USAGE_DTLS_TBL.NEXT(j);
332 END LOOP;
333 END IF;
334 RETURN FALSE;
335 END exists_mapping;
336 BEGIN
337 i := 0;
338 IF G_MAP_DTLS_TBL.COUNT > 0 THEN
339 i := G_MAP_DTLS_TBL.FIRST;
340 LOOP
341 IF exists_mapping(P_USAGE_ID,G_MAP_DTLS_TBL(i).GEOGRAPHY_TYPE) THEN
342 X_MDU_TBL(i).LOC_SEQ_NUM := G_MAP_DTLS_TBL(i).LOC_SEQ_NUM;
343 X_MDU_TBL(i).LOC_COMPONENT := G_MAP_DTLS_TBL(i).LOC_COMPONENT;
344 X_MDU_TBL(i).GEOGRAPHY_TYPE := G_MAP_DTLS_TBL(i).GEOGRAPHY_TYPE;
345 X_MDU_TBL(i).GEO_ELEMENT_COL := G_MAP_DTLS_TBL(i).GEO_ELEMENT_COL;
346 END IF;
347 EXIT WHEN i = G_MAP_DTLS_TBL.LAST;
348 i := G_MAP_DTLS_TBL.NEXT(i);
349 END LOOP;
350 END IF;
351
352 END populate_mdu_tbl;
353
354 --------------------------------------
355 /**
356 * PRIVATE PROCEDURE genPkgBdyInit
357 *
358 * DESCRIPTION
359 * Generate package Initialization section.
360 *
361 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
362 *
363 * ARGUMENTS
364 * IN:
365 * p_map_id Map_id
366 *
367 * NOTES
368 *
369 * MODIFICATION HISTORY
370 *
371 *
372 */
373 --------------------------------------
374 PROCEDURE genPkgBdyInit (
375 x_status OUT NOCOPY VARCHAR2) IS
376
377 i NUMBER := 0;
378 j NUMBER := 0;
379 l_mdu_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
380
381
382 BEGIN
383 -- initializing the retun value
384 x_status := FND_API.G_RET_STS_SUCCESS;
385
386 l(' ');
387 l('BEGIN ');
388
389 SELECT GEOGRAPHY_ID
390 INTO g_country_geo_id
391 FROM HZ_GEOGRAPHIES
392 WHERE COUNTRY_CODE = G_MAP_REC.COUNTRY_CODE
393 AND GEOGRAPHY_TYPE = 'COUNTRY';
394
395 l(' G_COUNTRY_GEO_ID := '||g_country_geo_id||';');
396 l(' G_MAP_REC.MAP_ID := '||G_MAP_REC.MAP_ID||';');
397 l(' G_MAP_REC.COUNTRY_CODE := '''||G_MAP_REC.COUNTRY_CODE||''';');
398 l(' G_MAP_REC.LOC_TBL_NAME := '''||G_MAP_REC.LOC_TBL_NAME||''';');
399 l(' G_MAP_REC.ADDRESS_STYLE := '''||NVL(G_MAP_REC.ADDRESS_STYLE,'NULL')||''';');
400 l(' ');
401
402 IF G_MAP_DTLS_TBL.COUNT > 0 THEN
403 i := G_MAP_DTLS_TBL.FIRST;
404 LOOP -- Populate the global variable for all the mapped columns.
405 l(' G_MAP_DTLS_TBL('||i||').LOC_SEQ_NUM := '||G_MAP_DTLS_TBL(i).LOC_SEQ_NUM||';');
406 l(' G_MAP_DTLS_TBL('||i||').LOC_COMPONENT := '''||G_MAP_DTLS_TBL(i).LOC_COMPONENT||''';');
407 l(' G_MAP_DTLS_TBL('||i||').GEOGRAPHY_TYPE := '''||G_MAP_DTLS_TBL(i).GEOGRAPHY_TYPE||''';');
408 l(' G_MAP_DTLS_TBL('||i||').GEO_ELEMENT_COL := '''||G_MAP_DTLS_TBL(i).GEO_ELEMENT_COL||''';');
409 IF G_MAP_DTLS_TBL(i).GEOGRAPHY_TYPE = 'COUNTRY' THEN
410 l(' G_MAP_DTLS_TBL('||i||').GEOGRAPHY_ID := '||g_country_geo_id||';');
411 END IF;
412 EXIT WHEN i = G_MAP_DTLS_TBL.LAST;
413 i := G_MAP_DTLS_TBL.NEXT(i);
414 END LOOP;
415 END IF;
416
417 i := 0;
418 IF G_USAGE_TBL.COUNT > 0 THEN
419 i := G_USAGE_TBL.FIRST;
420 LOOP -- Populate the global variable for all the Usagees
421 l(' ');
422 l(' G_USAGE_TBL('||i||').USAGE_ID := '||G_USAGE_TBL(i).USAGE_ID||';');
423 l(' G_USAGE_TBL('||i||').MAP_ID := '||G_USAGE_TBL(i).MAP_ID||';');
424 l(' G_USAGE_TBL('||i||').USAGE_CODE := '''||G_USAGE_TBL(i).USAGE_CODE||''';');
425
426 IF G_USAGE_DTLS_TBL.COUNT > 0 THEN
427 j := G_USAGE_DTLS_TBL.FIRST;
428 LOOP -- Populate the global variable for all the Usages and Details
429 IF G_USAGE_TBL(i).USAGE_ID = G_USAGE_DTLS_TBL(j).USAGE_ID THEN
430 l(' G_USAGE_DTLS_TBL('||j||').USAGE_ID := '||G_USAGE_DTLS_TBL(j).USAGE_ID||';');
431 l(' G_USAGE_DTLS_TBL('||j||').GEOGRAPHY_TYPE := '''||G_USAGE_DTLS_TBL(j).GEOGRAPHY_TYPE||''';');
432 END IF;
433 EXIT WHEN j = G_USAGE_DTLS_TBL.LAST;
434 j := G_USAGE_DTLS_TBL.NEXT(j);
435 END LOOP;
436 END IF;
437
438 -- Populate the G_MDU_TBL||Usage_id table
439 populate_mdu_tbl(G_USAGE_TBL(i).USAGE_ID, l_mdu_tbl);
440 j := 0;
441 IF L_MDU_TBL.COUNT > 0 THEN
442 j := L_MDU_TBL.FIRST;
443 LOOP
444 l(' G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID||'('||j||').LOC_SEQ_NUM := '||L_MDU_TBL(j).LOC_SEQ_NUM||';');
445 l(' G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID||'('||j||').LOC_COMPONENT := '''||L_MDU_TBL(j).LOC_COMPONENT||''';');
449 IF L_MDU_TBL(j).GEOGRAPHY_TYPE = 'COUNTRY' THEN
446 l(' G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID||'('||j||').GEOGRAPHY_TYPE := '''||L_MDU_TBL(j).GEOGRAPHY_TYPE||''';');
447 l(' G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID||'('||j||').GEO_ELEMENT_COL := '''||L_MDU_TBL(j).GEO_ELEMENT_COL||''';');
448
450 l(' G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID||'('||j||').GEOGRAPHY_ID := '||g_country_geo_id||';');
451 END IF;
452
453 EXIT WHEN j = L_MDU_TBL.LAST;
454 j := L_MDU_TBL.NEXT(j);
455 END LOOP;
456 END IF;
457
458 EXIT WHEN i = G_USAGE_TBL.LAST;
459 i := G_USAGE_TBL.NEXT(i);
460 END LOOP;
461 END IF;
462
463 l(' ');
464
465 END genPkgBdyInit;
466 --------------------------------------
467 /**
468 * PRIVATE PROCEDURE genPkgBdyTail
469 *
470 * DESCRIPTION
471 * Generate package tail.
472 *
473 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
474 *
475 * ARGUMENTS
476 * IN:
477 * p_package_name Package name.
478 *
479 * NOTES
480 *
481 * MODIFICATION HISTORY
482 *
483 *
484 */
485 --------------------------------------
486 PROCEDURE genPkgBdyTail (
487 p_package_name IN VARCHAR2
488 ) IS
489 BEGIN
490 l('END '||p_package_name||';');
491 -- compile the package.
492 HZ_GEN_PLSQL.compile_code;
493 END genPkgBdyTail;
494 --------------------------------------
495 /**
496 * PRIVATE PROCEDURE procBegin
497 *
498 * DESCRIPTION
499 * Generate procedure hdr
500 *
501 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
502 *
503 * ARGUMENTS
504 * IN:
505 * p_procName procedure name.
506 *
507 * NOTES
508 *
509 * MODIFICATION HISTORY
510 *
511 *
512 */
513 --------------------------------------
514 PROCEDURE funcBegin (
515 p_funcName IN VARCHAR2
516 ) IS
517 BEGIN
518 -- Hari li('--------------------------------------');
519 -- Hari li('/**');
520 -- Hari li(' * FUNCTION '||p_funcName);
521 -- Hari li(' *');
522 -- Hari li(' * DESCRIPTION');
523 -- Hari li(' * This is a private function ');
524 -- Hari li(' *');
525 -- Hari li(' */');
526 -- Hari l(' ');
527 li('FUNCTION '||p_funcName||'(');
528 END funcBegin;
529 --------------------------------------
530 /**
531 * PRIVATE PROCEDURE funcEnd
532 *
533 * DESCRIPTION
534 * Generate function tail
535 *
536 *
537 * ARGUMENTS
538 * IN:
539 * p_funcName function name.
540 *
541 * NOTES
542 *
543 * MODIFICATION HISTORY
544 *
545 *
546 */
547 --------------------------------------
548 PROCEDURE funcEnd (
549 p_funcName IN VARCHAR2
550 ) IS
551 BEGIN
552 l(' ');
553 li('END '||p_funcName||';');
554 END funcEnd;
555 --------------------------------------
556
557 --------------------------------------
558 /**
559 * PRIVATE PROCEDURE procBegin
560 *
561 * DESCRIPTION
562 * Generate procedure hdr
563 *
564 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
565 *
566 * ARGUMENTS
567 * IN:
568 * p_procName procedure name.
569 *
570 * NOTES
571 *
572 * MODIFICATION HISTORY
573 *
574 *
575 */
576 --------------------------------------
577 PROCEDURE procBegin (
578 p_procName IN VARCHAR2
579 ) IS
580 BEGIN
581 -- Hari li('--------------------------------------');
582 -- Hari li('/**');
583 -- Hari li(' * PROCEDURE '||p_procName);
584 -- Hari li(' *');
585 -- Hari li(' * DESCRIPTION');
586 -- Hari li(' * This map specific private procedure is used to identify the');
587 -- Hari li(' * geography ids ');
588 -- Hari li(' *');
589 -- Hari li(' * ARGUMENTS');
590 -- Hari li(' *');
591 -- Hari li(' * IN OUT:');
592 -- Hari li(' * x_mapTbl Table of records that has location sequence number,');
593 -- Hari li(' * geo element, type and loc components and their values');
594 -- Hari li(' * OUT:');
595 -- Hari li(' * x_status indicates if the srchGeo was sucessfull or not.');
596 -- Hari li(' *');
597 -- Hari li(' */');
598 -- Hari l(' ');
599 li('PROCEDURE '||p_procName||'(');
600 END procBegin;
601 --------------------------------------
602 /**
603 * PRIVATE PROCEDURE procEnd
604 *
605 * DESCRIPTION
606 * Generate procedure tail
607 *
608 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
609 *
610 * ARGUMENTS
611 * IN:
612 * p_procName procedure name.
613 *
614 * NOTES
615 *
616 * MODIFICATION HISTORY
617 *
618 *
619 */
620 --------------------------------------
621 PROCEDURE procEnd (
622 p_procName IN VARCHAR2
623 ) IS
624 BEGIN
625 l(' ');
629 /**
626 li('END '||p_procName||';');
627 END procEnd;
628 --------------------------------------
630 * PRIVATE PROCEDURE get_usage_API_Body
631 *
632 * DESCRIPTION
633 * to generate Body for get_usage_API function
634 *
635 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
636 *
637 * NOTES
638 *
639 * MODIFICATION HISTORY
640 *
641 *
642 */
643 --------------------------------------
644 PROCEDURE get_usage_API_Body IS
645 l_funcName varchar2(30);
646
647 BEGIN
648 -- name the function
649 l_funcName := 'get_usage_API';
650 -- write the header comments, function name
651 funcBegin(l_funcName);
652 -- write parameters
653 li(' P_USAGE_CODE VARCHAR2) RETURN VARCHAR2 IS');
654 -- write function body
655 l(' i number;');
656 l(' l_API_Name varchar2(30);');
657 l(' ');
658 l(' BEGIN');
659 l(' i := 0;');
660 l(' IF G_USAGE_TBL.COUNT > 0 THEN');
661 l(' i := G_USAGE_TBL.FIRST;');
662 l(' LOOP');
663 l(' IF G_USAGE_TBL(i).USAGE_CODE = P_USAGE_CODE THEN');
664 l(' IF G_USAGE_TBL(i).USAGE_CODE = ''GEOGRAPHY'' THEN');
665 l(' l_API_Name := ''validateGeo'';');
666 l(' ELSIF G_USAGE_TBL(i).USAGE_CODE = ''TAX'' THEN');
667 l(' l_API_Name := ''validateTax'';');
668 l(' ELSE');
669 l(' l_API_Name := ''validate''||G_USAGE_TBL(i).USAGE_ID;');
670 l(' END IF;');
671 l(' END IF;');
672 l(' EXIT WHEN i = G_USAGE_TBL.LAST;');
673 l(' i := G_USAGE_TBL.NEXT(i);');
674 l(' END LOOP;');
675 l(' END IF;');
676 l(' ');
677 l(' RETURN l_API_Name;');
678 funcEnd(l_funcName);
679 l(' ');
680 END get_usage_API_Body;
681 --------------------------------------
682 --------------------------------------
683 /**
684 * PRIVATE PROCEDURE get_usage_API_Spec
685 *
686 * DESCRIPTION
687 * to generate spec for get_usage_API function
688 *
689 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
690 *
691 * NOTES
692 *
693 * MODIFICATION HISTORY
694 *
695 *
696 */
697 --------------------------------------
698 PROCEDURE get_usage_API_spec IS
699 l_funcName varchar2(30);
700
701 BEGIN
702 -- name the function
703 l_funcName := 'get_usage_API';
704 -- write the header comments, function name
705 funcBegin(l_funcName);
706 -- write parameters
707 li(' P_USAGE_CODE VARCHAR2) RETURN VARCHAR2;');
708 l(' ');
709 END get_usage_API_Spec;
710 --------------------------------------
711 /**
712 * PRIVATE PROCEDURE validateHrSpec
713 *
714 * DESCRIPTION
715 * to generate spec for validateSpec procedure
716 *
717 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
718 *
719 * NOTES
720 *
721 * MODIFICATION HISTORY
722 *
723 *
724 */
725 --------------------------------------
726 PROCEDURE validateHrSpec IS
727 l_procName varchar2(30);
728 i number;
729 BEGIN
730 l_procName := 'validateHrLoc';
731 procBegin(l_procName);
732
733 -- write the parameters
734 li(' P_LOCATION_ID IN NUMBER,');
735 li(' X_STATUS OUT NOCOPY VARCHAR2);');
736 l(' ');
737
738 END validateHrSpec;
739
740 --------------------------------------
741 /**
742 * PRIVATE PROCEDURE validateSpec
743 *
744 * DESCRIPTION
745 * to generate spec for validateSpec procedure
746 *
747 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
748 *
749 * NOTES
750 *
751 * MODIFICATION HISTORY
752 *
753 *
754 */
755 --------------------------------------
756 PROCEDURE validateSpec IS
757 l_procName varchar2(30);
758 i number;
759 BEGIN
760 i := 0;
761 IF G_USAGE_TBL.COUNT > 0 THEN
762 i := G_USAGE_TBL.FIRST;
763 LOOP
764 -- name the procedure
765 IF G_USAGE_TBL(i).USAGE_CODE = 'GEOGRAPHY' THEN
766 l_procName := 'validateGeo';
767 ELSIF G_USAGE_TBL(i).USAGE_CODE = 'TAX' THEN
768 l_procName := 'validateTax';
769 ELSE
770 l_procName := 'validate'||G_USAGE_TBL(i).USAGE_ID;
771 END IF;
772
773 -- write the header comments, procedure name
774 procBegin(l_procName);
775
776 -- write the parameters
777 li(' P_LOCATION_ID IN NUMBER,');
781 li(' P_COUNTY IN VARCHAR2,');
778 li(' P_COUNTRY IN VARCHAR2,');
779 li(' P_STATE IN VARCHAR2,');
780 li(' P_PROVINCE IN VARCHAR2,');
782 li(' P_CITY IN VARCHAR2,');
783 li(' P_POSTAL_CODE IN VARCHAR2,');
784 li(' P_POSTAL_PLUS4_CODE IN VARCHAR2,');
785 li(' P_ATTRIBUTE1 IN VARCHAR2,');
786 li(' P_ATTRIBUTE2 IN VARCHAR2,');
787 li(' P_ATTRIBUTE3 IN VARCHAR2,');
788 li(' P_ATTRIBUTE4 IN VARCHAR2,');
789 li(' P_ATTRIBUTE5 IN VARCHAR2,');
790 li(' P_ATTRIBUTE6 IN VARCHAR2,');
791 li(' P_ATTRIBUTE7 IN VARCHAR2,');
792 li(' P_ATTRIBUTE8 IN VARCHAR2,');
793 li(' P_ATTRIBUTE9 IN VARCHAR2,');
794 li(' P_ATTRIBUTE10 IN VARCHAR2,');
795 li(' P_LOCK_FLAG IN VARCHAR2 DEFAULT FND_API.G_TRUE,');
796 li(' X_CALL_MAP IN OUT NOCOPY VARCHAR2,');
797 li(' P_CALLED_FROM IN VARCHAR2,');
798 li(' P_ADDR_VAL_LEVEL IN VARCHAR2,');
799 li(' X_ADDR_WARN_MSG OUT NOCOPY VARCHAR2,');
800 li(' X_ADDR_VAL_STATUS OUT NOCOPY VARCHAR2,');
801 li(' X_STATUS OUT NOCOPY VARCHAR2);');
802 l(' ');
803
804 EXIT WHEN i = G_USAGE_TBL.LAST;
805 i := G_USAGE_TBL.NEXT(i);
806 END LOOP;
807 END IF;
808
809 END validateSpec;
810 --------------------------------------
811 /**
812 * PRIVATE PROCEDURE validateHrBody
813 *
814 * DESCRIPTION
815 * to generate body for srchGeo procedure
816 *
817 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
818 *
819 * NOTES
820 *
821 * MODIFICATION HISTORY
822 *
823 *
824 */
825 --------------------------------------
826 PROCEDURE validateHrBody (
827 x_status OUT NOCOPY VARCHAR2) IS
828
829 -- local variable declaration
830 l_procName varchar2(30);
831 l_mdu_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
832 l_usage_id number;
833 l_open_cur varchar2(2000);
834 l_fetch_cur varchar2(2000);
835 l_mdu_tbl_name varchar2(30);
836 i number;
837 j number;
838
839 BEGIN
840 x_status := FND_API.g_ret_sts_success;
841 i := 0;
842 IF G_USAGE_TBL.COUNT > 0 THEN
843 i := G_USAGE_TBL.FIRST;
844 LOOP
845 IF G_USAGE_TBL(i).USAGE_CODE = 'TAX' THEN
846 l_usage_id := G_USAGE_TBL(i).USAGE_ID;
847 l_mdu_tbl_name := 'G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID;
848 END IF;
849 EXIT WHEN i = G_USAGE_TBL.LAST;
850 i := G_USAGE_TBL.NEXT(i);
851 END LOOP;
852 END IF;
853 populate_mdu_tbl(l_usage_id, l_mdu_tbl);
854 l(' ');
855 -- write the header comments, procedure name
856 l_procName := 'validateHrLoc';
857 procBegin(l_procName);
858 li(' P_LOCATION_ID IN NUMBER,');
859 li(' X_STATUS OUT NOCOPY VARCHAR2) IS');
860 l(' ');
861 li(' TYPE getGeo IS REF CURSOR;');
862 li(' c_getGeo getGeo;');
863 li(' c_getGeo1 getGeo;');
864 l(' ');
865 li(' l_multiple_parent_flag VARCHAR2(1);');
866 li(' l_sql VARCHAR2(9000);');
867 li(' l_status VARCHAR2(1);');
868 li(' l_geography_type VARCHAR2(30);');
869 li(' l_geography_id NUMBER;');
870 li(' L_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
871 li(' LL_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
872 li(' l_loc_components_rec HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE; -- not actually required here');
873 l(' ');
874 l(' l_module_prefix CONSTANT VARCHAR2(30) := ''HZ:ARHGNRGB:'||g_pkgName||''';');
875 l(' l_module CONSTANT VARCHAR2(30) := ''ADDRESS_VALIDATION'';');
876 l(' l_debug_prefix VARCHAR2(30) := p_location_id;');
877 l(' ');
878 l(' l_temp_postal_code VARCHAR2(360);');
882 LOOP
879 i := 0;
880 IF L_MDU_TBL.COUNT > 0 THEN
881 i := L_MDU_TBL.FIRST;
883 li(' l_value'||i||' VARCHAR2(360);');
884 li(' l_type'||i||' VARCHAR2(30);');
885
886 EXIT WHEN i = L_MDU_TBL.LAST;
887 i := L_MDU_TBL.NEXT(i);
888 END LOOP;
889 END IF;
890
891 l(' ');
892 li('BEGIN ');
893 li(' -- defaulting the sucess status');
894 l(' x_status := FND_API.g_ret_sts_success;');
895 l(' L_MDU_TBL := '||l_mdu_tbl_name||';');
896 l(' LL_MDU_TBL := '||l_mdu_tbl_name||';');
897 l(' --hk_debugl(''Validate HR Loc Start for the location_id :''||p_location_id);');
898 l(' --hk_debugl(''The MDU table structure'');');
899 l(' --hk_debugt(L_MDU_TBL);');
900 l(' ');
901 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
902 l(' hz_utility_v2pub.debug ');
903 l(' (p_message => ''Begin of validation for validateHrLoc.'',');
904 l(' p_prefix => l_debug_prefix,');
905 l(' p_msg_level => fnd_log.level_procedure,');
906 l(' p_module_prefix => l_module_prefix,');
907 l(' p_module => l_module');
908 l(' );');
909 l(' END IF; ');
910 l(' ');
911 l(' IF L_MDU_TBL.COUNT = 1 THEN');
912 l(' ');
913 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
914 l(' hz_utility_v2pub.debug ');
915 l(' (p_message => '' This means country is the only required mapped column for validation. Call create_gnr with map status S'',');
916 l(' p_prefix => l_debug_prefix,');
917 l(' p_msg_level => fnd_log.level_statement,');
918 l(' p_module_prefix => l_module_prefix,');
919 l(' p_module => l_module');
920 l(' );');
921 l(' END IF; ');
922 l(' ');
923 l(' -- This means country is the only required mapped column for validation.');
924 l(' -- and country is already populated in the L_MDU_TBL in the initialization section of this package.');
925 l(' --hk_debugt(L_MDU_TBL); ----- Code to display the output.');
926 l(' --hk_debugl(''Calling create_gnr With Map_status "S"'');');
927 l(' IF P_LOCATION_ID IS NOT NULL THEN');
928 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
929 l(' ''TAX'',''S'',l_loc_components_rec,''T'',L_MDU_TBL,l_status);');
930 l(' END IF;');
931 l(' x_status := FND_API.g_ret_sts_success;');
932 l(' RETURN;');
933 l(' END IF;');
934 l(' --hk_debugl(''L_MDU_TBL has count count more than 1'');');
935 l(' ');
936
937 i := 0;
938 IF L_MDU_TBL.COUNT > 0 THEN
939 i := L_MDU_TBL.FIRST;
940 l(' BEGIN ');
941 l(' SELECT '||L_MDU_TBL(i).LOC_COMPONENT);
942 LOOP
943 EXIT WHEN i = L_MDU_TBL.LAST;
944 i := L_MDU_TBL.NEXT(i);
945 l(' ,'||L_MDU_TBL(i).LOC_COMPONENT);
946 END LOOP;
947 i := L_MDU_TBL.FIRST;
948 l(' INTO L_MDU_TBL('||i||').LOC_COMPVAL');
949 LOOP
950 EXIT WHEN i = L_MDU_TBL.LAST;
951 i := L_MDU_TBL.NEXT(i);
952 l(' ,L_MDU_TBL('||i||').LOC_COMPVAL');
953 END LOOP;
954
955 l(' FROM HR_LOCATIONS_ALL ');
956 l(' WHERE LOCATION_ID = P_LOCATION_ID;');
957 l(' ');
958 l(' EXCEPTION WHEN OTHERS THEN ');
959 l(' x_status := FND_API.g_ret_sts_error;');
960 l(' END; ');
961 END IF;
962 l(' --hk_debugl(''The MDU table after location components populated`'');');
963 l(' --hk_debugt(L_MDU_TBL);');
964
965 l_open_cur := NULL;
966 l_fetch_cur:= NULL;
967
968 j := 0;
969 IF L_MDU_TBL.COUNT > 0 THEN
970 j := L_MDU_TBL.FIRST;
971 LOOP
972 IF j>1 THEN
973 l(' l_value'||j||' := NVL(L_MDU_TBL('||j||').LOC_COMPVAL,''X'') ;');
974 l(' IF l_value'||j||' = ''X'' THEN');
975 l(' l_type'||j||' := ''X'';');
976 l(' ELSE');
977 l(' l_type'||j||' := L_MDU_TBL('||j||').GEOGRAPHY_TYPE;');
978 l(' -- store the geography_type of the lowest address component that has a value passed in');
979 l(' l_geography_type := l_type'||j||';');
980 l(' ');
981 l(' -- Fix for Bug 7240974 (Nishant) (ZIP+4 functionality) ');
982 l(' -- check if component is POSTAL_CODE, change from ZIP+4 format to ZIP for US based on setup');
983 l(' IF L_MDU_TBL('||j||').LOC_COMPONENT = ''POSTAL_CODE'' THEN ');
984 l(' l_temp_postal_code := HZ_GNR_UTIL_PKG.postal_code_to_validate(P_COUNTRY_CODE => L_MDU_TBL(1).LOC_COMPVAL,');
985 l(' P_POSTAL_CODE => L_MDU_TBL('||j||').LOC_COMPVAL); ');
986 l(' L_MDU_TBL('||j||').LOC_COMPVAL := l_temp_postal_code; ');
987 l(' l_value'||j||' := l_temp_postal_code; ');
988 l(' END IF ;');
989 l(' ');
993 l_fetch_cur := l_fetch_cur||',LL_MDU_TBL('||j||').GEOGRAPHY_ID';
990 l(' END IF;');
991 l(' ');
992 l_open_cur := l_open_cur||',l_type'||j||',l_value'||j;
994 END IF;
995
996 EXIT WHEN j = L_MDU_TBL.LAST;
997 j := L_MDU_TBL.NEXT(j);
998 END LOOP;
999 END IF;
1000 l(' ');
1001 l_open_cur := l_open_cur||',l_geography_type;';
1002 l_fetch_cur := l_fetch_cur||';';
1003 l(' LL_MDU_TBL := L_MDU_TBL;');
1004 l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MDU_TBL,L_MDU_TBL,x_status);');
1005 l(' --hk_debugl(''The SQL query'');');
1006 l(' --hk_debugl(l_sql);');
1007 l(' ');
1008 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1009 l(' hz_utility_v2pub.debug ');
1010 l(' (p_message => '' The SQL query : ''||l_sql,');
1011 l(' p_prefix => l_debug_prefix,');
1012 l(' p_msg_level => fnd_log.level_statement,');
1013 l(' p_module_prefix => l_module_prefix,');
1014 l(' p_module => l_module');
1015 l(' );');
1016 l(' END IF; ');
1017 l(' ');
1018 -- BEGIN NS
1019 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1020 l(' hz_utility_v2pub.debug ');
1021 l(' (p_message => '' The BIND values are : ''||G_MAP_REC.COUNTRY_CODE||'':''||g_country_geo_id ');
1022 l(' '||replace(replace(l_open_cur,',','||'':''||'),';',','));
1023 l(' p_prefix => l_debug_prefix,');
1024 l(' p_msg_level => fnd_log.level_statement,');
1025 l(' p_module_prefix => l_module_prefix,');
1026 l(' p_module => l_module');
1027 l(' );');
1028 l(' END IF; ');
1029 l(' ');
1030 -- END NS
1031 l(' OPEN c_getGeo FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
1032 l(' '||l_open_cur);
1033 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MDU_TBL(1).GEOGRAPHY_ID');
1034 l(' '||l_fetch_cur);
1035 l(' IF c_getGeo%NOTFOUND THEN ');
1036 l(' --hk_debugl(''No Match found for the usage level search'');');
1037 l(' ');
1038 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1039 l(' hz_utility_v2pub.debug ');
1040 l(' (p_message => '' No Match found for the usage level search '',');
1041 l(' p_prefix => l_debug_prefix,');
1042 l(' p_msg_level => fnd_log.level_statement,');
1043 l(' p_module_prefix => l_module_prefix,');
1044 l(' p_module => l_module');
1045 l(' );');
1046 l(' END IF; ');
1047 l(' ');
1048 l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MDU_TBL,x_status);');
1049 l(' x_status := FND_API.G_RET_STS_ERROR;');
1050 l(' ELSE ');
1051 l(' --Fetching once more to see where there are multiple records');
1052 l(' ');
1053 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1054 l(' hz_utility_v2pub.debug ');
1055 l(' (p_message => '' Fetching once more to see where there are multiple records '',');
1056 l(' p_prefix => l_debug_prefix,');
1057 l(' p_msg_level => fnd_log.level_statement,');
1058 l(' p_module_prefix => l_module_prefix,');
1059 l(' p_module => l_module');
1060 l(' );');
1061 l(' END IF; ');
1062 l(' ');
1063 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MDU_TBL(1).GEOGRAPHY_ID');
1064 l(' '||l_fetch_cur);
1065 l(' IF c_getGeo%FOUND THEN -- not able to identify a unique record');
1066 l(' --hk_debugl(''Multiple Match found for the usage level search'');');
1067
1068
1069 l(' -- Get the query again with identifier type as NAME if multiple match found');
1070 l(' -- If it returns a record, we are able to derive a unique record for identifier type as NAME');
1071 l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MDU_TBL,L_MDU_TBL,x_status);');
1072 l(' OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
1073 l(' '||l_open_cur);
1074 l(' ');
1075 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1076 l(' hz_utility_v2pub.debug ');
1077 l(' (p_message => ''Before the fetch of the query with identifier type as NAME after multiple match found'',');
1078 l(' p_prefix => l_debug_prefix,');
1079 l(' p_msg_level => fnd_log.level_statement,');
1080 l(' p_module_prefix => l_module_prefix,');
1081 l(' p_module => l_module');
1082 l(' );');
1083 l(' END IF; ');
1084 l(' ');
1085 l(' --hk_debugt(LL_MDU_TBL);');
1086 l(' FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,LL_MDU_TBL(1).GEOGRAPHY_ID');
1087 l(' '||l_fetch_cur);
1088 l(' IF c_getGeo1%FOUND THEN ');
1089 l(' ');
1090 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1091 l(' hz_utility_v2pub.debug ');
1092 l(' (p_message => ''Able to found a unique record or a record with multiple parent flag = Y with identifier type as NAME'',');
1096 l(' p_module => l_module');
1093 l(' p_prefix => l_debug_prefix,');
1094 l(' p_msg_level => fnd_log.level_statement,');
1095 l(' p_module_prefix => l_module_prefix,');
1097 l(' );');
1098 l(' END IF; ');
1099 l(' ');
1100 l(' ELSE -- Not able to found a unique record with identifier type as NAME');
1101 l(' ');
1102 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1103 l(' hz_utility_v2pub.debug ');
1104 l(' (p_message => '' Not able to find a record with with identifier type as NAME. '',');
1105 l(' p_prefix => l_debug_prefix,');
1106 l(' p_msg_level => fnd_log.level_statement,');
1107 l(' p_module_prefix => l_module_prefix,');
1108 l(' p_module => l_module');
1109 l(' );');
1110 l(' END IF; ');
1111 l(' ');
1112 l(' LL_MDU_TBL := L_MDU_TBL;');
1113 l(' x_status := FND_API.G_RET_STS_ERROR;');
1114 l(' END IF; ');
1115 l(' CLOSE c_getGeo1;');
1116 l(' ');
1117
1118
1119 l(' ELSE -- a unique record or a record with multiple parent flag = Y is found');
1120 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1121 l(' hz_utility_v2pub.debug ');
1122 l(' (p_message => '' A unique record or a record with multiple parent flag = Y is found '',');
1123 l(' p_prefix => l_debug_prefix,');
1124 l(' p_msg_level => fnd_log.level_statement,');
1125 l(' p_module_prefix => l_module_prefix,');
1126 l(' p_module => l_module');
1127 l(' );');
1128 l(' END IF; ');
1129 l(' END IF;');
1130 l(' ');
1131 l(' IF l_multiple_parent_flag = ''Y'' AND x_status <> FND_API.G_RET_STS_ERROR THEN');
1132 l(' --hk_debugl(''Multiple Parent Match found for the usage level search'');');
1133 l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,LL_MDU_TBL) = TRUE THEN');
1134 l(' --hk_debugl(''Sucessfully Fixed the Multiple Parent Case '');');
1135 l(' NULL;');
1136 l(' ELSE -- Multiple parent case not able to find a unique record ');
1137 l(' ');
1138 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1139 l(' hz_utility_v2pub.debug ');
1140 l(' (p_message => '' Multiple parent case not able to find a unique record'',');
1141 l(' p_prefix => l_debug_prefix,');
1142 l(' p_msg_level => fnd_log.level_statement,');
1143 l(' p_module_prefix => l_module_prefix,');
1144 l(' p_module => l_module');
1145 l(' );');
1146 l(' END IF; ');
1147 l(' ');
1148 l(' --hk_debugl(''Unable to Fix the Multiple Parent Case '');');
1149 l(' x_status := FND_API.G_RET_STS_ERROR;');
1150 l(' END IF;');
1151 l(' ELSE -- a unique record is found');
1152 l(' --hk_debugl(''Successfully found a unique record '');');
1153 l(' ');
1154 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1155 l(' hz_utility_v2pub.debug ');
1156 l(' (p_message => '' A unique record is found '',');
1157 l(' p_prefix => l_debug_prefix,');
1158 l(' p_msg_level => fnd_log.level_statement,');
1159 l(' p_module_prefix => l_module_prefix,');
1160 l(' p_module => l_module');
1161 l(' );');
1162 l(' END IF; ');
1163 l(' ');
1164 l(' END IF;');
1165 l(' END IF;');
1166 l(' CLOSE c_getGeo;');
1167 l(' ');
1168 l(' IF x_status = FND_API.G_RET_STS_SUCCESS THEN ');
1169 l(' --Following call will try to derive missing lower level compoents ');
1170 l(' --hk_debugl(''LL_MDU Table before Fix_child '');');
1171 l(' --hk_debugt(LL_MDU_TBL);');
1172 l(' ');
1173 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1174 l(' hz_utility_v2pub.debug ');
1175 l(' (p_message => '' Calling fix_child. This call will try to derive missing lower level compoents.'',');
1176 l(' p_prefix => l_debug_prefix,');
1177 l(' p_msg_level => fnd_log.level_statement,');
1178 l(' p_module_prefix => l_module_prefix,');
1179 l(' p_module => l_module');
1180 l(' );');
1181 l(' END IF; ');
1182 l(' ');
1183 l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MDU_TBL) = FALSE THEN');
1184 l(' --hk_debugl(''LL_MDU Table after Fix_child '');');
1185 l(' --hk_debugt(LL_MDU_TBL);');
1186 l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MDU_TBL,L_MDU_TBL);');
1187 l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.get_usage_val_status '');');
1188 l(' --hk_debugt(LL_MDU_TBL);');
1189 l(' END IF;');
1190 l(' ');
1191 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1192 l(' hz_utility_v2pub.debug ');
1196 l(' p_module_prefix => l_module_prefix,');
1193 l(' (p_message => '' Return status after fix_child ''||x_status,');
1194 l(' p_prefix => l_debug_prefix,');
1195 l(' p_msg_level => fnd_log.level_statement,');
1197 l(' p_module => l_module');
1198 l(' );');
1199 l(' END IF; ');
1200 l(' ');
1201 l(' END IF;');
1202 l(' ');
1203 l(' HZ_GNR_UTIL_PKG.fill_values(LL_MDU_TBL);');
1204 l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.fill_values '');');
1205 l(' --hk_debugt(LL_MDU_TBL);');
1206 l(' ');
1207 l(' IF x_status = FND_API.g_ret_sts_success THEN');
1208 l(' ');
1209 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1210 l(' hz_utility_v2pub.debug ');
1211 l(' (p_message => '' Calling create_gnr with map status S.'',');
1212 l(' p_prefix => l_debug_prefix,');
1213 l(' p_msg_level => fnd_log.level_statement,');
1214 l(' p_module_prefix => l_module_prefix,');
1215 l(' p_module => l_module');
1216 l(' );');
1217 l(' END IF; ');
1218 l(' ');
1219 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1220 l(' ''TAX'',''S'',l_loc_components_rec,''T'',LL_MDU_TBL,l_status);');
1221 l(' ELSE ');
1222 l(' ');
1223 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1224 l(' hz_utility_v2pub.debug ');
1225 l(' (p_message => '' Calling create_gnr with map status E.'',');
1226 l(' p_prefix => l_debug_prefix,');
1227 l(' p_msg_level => fnd_log.level_statement,');
1228 l(' p_module_prefix => l_module_prefix,');
1229 l(' p_module => l_module');
1230 l(' );');
1231 l(' END IF; ');
1232 l(' ');
1233 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1234 l(' ''TAX'',''E'',l_loc_components_rec,''T'',LL_MDU_TBL,l_status);');
1235 l(' END IF;');
1236 l(' ');
1237 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1238 l(' hz_utility_v2pub.debug ');
1239 l(' (p_message => ''End of validation for validateHrLoc.'',');
1240 l(' p_prefix => l_debug_prefix,');
1241 l(' p_msg_level => fnd_log.level_procedure,');
1242 l(' p_module_prefix => l_module_prefix,');
1243 l(' p_module => l_module');
1244 l(' );');
1245 l(' END IF; ');
1246 l(' ');
1247 l(' --hk_debugt(LL_MDU_TBL); ----- Code to display the output.');
1248 ---- end procedure
1249 procEnd(l_procName);
1250 END validateHrBody;
1251 --------------------------------------
1252 /**
1253 * PRIVATE PROCEDURE validateBody
1254 *
1255 * DESCRIPTION
1256 * to generate body for srchGeo procedure
1257 *
1258 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1259 *
1260 * NOTES
1261 *
1262 * MODIFICATION HISTORY
1263 *
1264 *
1265 */
1266 --------------------------------------
1267 PROCEDURE validateBody (
1268 x_status OUT NOCOPY VARCHAR2) IS
1269
1270 -- local variable declaration
1271 l_procName varchar2(30);
1272 i number;
1273 j number;
1274 l_mdu_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
1275 l_open_cur varchar2(2000);
1276 l_fetch_cur varchar2(2000);
1277 l_usage_id number;
1278 l_usage_code varchar2(30);
1279 l_mdu_tbl_name varchar2(30);
1280
1281 BEGIN
1282 x_status := FND_API.g_ret_sts_success;
1283 l(' ');
1284 -- write the header comments, procedure name
1285 l_procName := 'validateForMap';
1286 procBegin(l_procName);
1287 li(' p_loc_components_rec IN HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE,');
1288 li(' x_map_dtls_tbl IN OUT NOCOPY HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE,');
1289 li(' X_CAUSE OUT NOCOPY VARCHAR2,');
1290 li(' X_STATUS OUT NOCOPY VARCHAR2) IS');
1291 l(' ');
1292 l(' ');
1293 li(' TYPE getGeo IS REF CURSOR;');
1294 li(' c_getGeo getGeo;');
1295 li(' c_getGeo1 getGeo;');
1296 l(' ');
1297 li(' l_multiple_parent_flag VARCHAR2(1);');
1298 li(' l_sql VARCHAR2(9000);');
1299 li(' l_status VARCHAR2(1);');
1300 li(' l_geography_type VARCHAR2(30);');
1301 li(' l_geography_id NUMBER;');
1302 li(' L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1303 l(' ');
1304 l(' l_module_prefix CONSTANT VARCHAR2(30) := ''HZ:ARHGNRGB:'||g_pkgName||''';');
1305 l(' l_module CONSTANT VARCHAR2(30) := ''ADDRESS_VALIDATION'';');
1306 l(' l_debug_prefix VARCHAR2(30);');
1307 l(' ');
1308
1309 j := 0;
1310 IF G_MAP_DTLS_TBL.COUNT > 0 THEN
1311 j := G_MAP_DTLS_TBL.FIRST;
1315
1312 LOOP
1313 li(' l_value'||j||' VARCHAR2(360);');
1314 li(' l_type'||j||' VARCHAR2(30);');
1316 EXIT WHEN j = G_MAP_DTLS_TBL.LAST;
1317 j := G_MAP_DTLS_TBL.NEXT(j);
1318 END LOOP;
1319 END IF;
1320 l(' ');
1321 -- procedure body
1322 li('BEGIN ');
1323 l(' ');
1324 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1325 l(' hz_utility_v2pub.debug ');
1326 l(' (p_message => ''Begin of Validate for Map'',');
1327 l(' p_prefix => l_debug_prefix,');
1328 l(' p_msg_level => fnd_log.level_procedure,');
1329 l(' p_module_prefix => l_module_prefix,');
1330 l(' p_module => l_module');
1331 l(' );');
1332 l(' END IF; ');
1333 l(' ');
1334 l(' --hk_debugl(''Validate for Map Start'');');
1335 li(' -- defaulting the sucess status');
1336 li(' x_status := FND_API.g_ret_sts_success;');
1337 l(' ');
1338 l(' L_MAP_DTLS_TBL := X_MAP_DTLS_TBL;');
1339 l(' --hk_debugl(''The Map table passed in with loc comp values'');');
1340 l(' --hk_debugt(L_MAP_DTLS_TBL);');
1341 l(' ');
1342 l(' IF L_MAP_DTLS_TBL.COUNT = 1 THEN');
1343 l(' -- This means country is the only required mapped column for validation.');
1344 l(' -- and country is already populated in the L_MAP_DTLS_TBL in the initialization section of this package.');
1345 l(' x_status := FND_API.g_ret_sts_success;');
1346 l(' RETURN;');
1347 l(' END IF;');
1348 l(' ');
1349 l(' IF HZ_GNR_UTIL_PKG.getLocCompCount(L_MAP_DTLS_TBL) = 0 THEN');
1350 l(' --hk_debugl(''HZ_GNR_UTIL_PKG.getLocCompCount = 0'');');
1351 l(' ');
1352 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1353 l(' hz_utility_v2pub.debug ');
1354 l(' (p_message => '' HZ_GNR_UTIL_PKG.getLocCompCount = 0 '',');
1355 l(' p_prefix => l_debug_prefix,');
1356 l(' p_msg_level => fnd_log.level_statement,');
1357 l(' p_module_prefix => l_module_prefix,');
1358 l(' p_module => l_module');
1359 l(' );');
1360 l(' END IF; ');
1361 l(' ');
1362 l(' --No other location component value other than country is passed. ');
1363 l(' --Following call will try to derive missing lower level compoents ');
1364 l(' IF HZ_GNR_UTIL_PKG.fix_child(L_MAP_DTLS_TBL) = FALSE THEN');
1365 l(' x_cause := ''MISSING_CHILD'';');
1366 l(' ');
1367 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1368 l(' hz_utility_v2pub.debug ');
1369 l(' (p_message => '' x_cause : ''||x_cause,');
1370 l(' p_prefix => l_debug_prefix,');
1371 l(' p_msg_level => fnd_log.level_statement,');
1372 l(' p_module_prefix => l_module_prefix,');
1373 l(' p_module => l_module');
1374 l(' );');
1375 l(' END IF; ');
1376 l(' ');
1377 -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1378 l(' x_status := FND_API.G_RET_STS_ERROR;');
1379 l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1380 l(' RETURN;');
1381 l(' ELSE');
1382 l(' ');
1383 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1384 l(' hz_utility_v2pub.debug ');
1385 l(' (p_message => '' Derived the missing lower level compoents '',');
1386 l(' p_prefix => l_debug_prefix,');
1387 l(' p_msg_level => fnd_log.level_statement,');
1388 l(' p_module_prefix => l_module_prefix,');
1389 l(' p_module => l_module');
1390 l(' );');
1391 l(' END IF; ');
1392 l(' ');
1393 -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1394 l(' x_status := FND_API.G_RET_STS_SUCCESS;');
1395 l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1396 l(' RETURN;');
1397 l(' END IF;');
1398 l(' END IF;');
1399 l(' ');
1400 l_open_cur := NULL;
1401 l_fetch_cur:= NULL;
1402
1403 j := 0;
1404 IF G_MAP_DTLS_TBL.COUNT > 0 THEN
1405 j := G_MAP_DTLS_TBL.FIRST;
1406 LOOP
1407 IF j>1 THEN
1408 l(' l_value'||j||' := NVL(L_MAP_DTLS_TBL('||j||').LOC_COMPVAL,''X'') ;');
1409 l(' IF l_value'||j||' = ''X'' THEN');
1410 l(' l_type'||j||' := ''X'';');
1411 l(' ELSE');
1412 l(' l_type'||j||' := L_MAP_DTLS_TBL('||j||').GEOGRAPHY_TYPE;');
1413 l(' -- store the geography_type of the lowest address component that has a value passed in');
1414 l(' l_geography_type := l_type'||j||';');
1415 l(' END IF;');
1416 l(' ');
1417 l_open_cur := l_open_cur||',l_type'||j||',l_value'||j;
1418 l_fetch_cur := l_fetch_cur||',L_MAP_DTLS_TBL('||j||').GEOGRAPHY_ID';
1419 END IF;
1420
1421 EXIT WHEN j = G_MAP_DTLS_TBL.LAST;
1422 j := G_MAP_DTLS_TBL.NEXT(j);
1423 END LOOP;
1424 END IF;
1425 l(' ');
1426 l_open_cur := l_open_cur||',l_geography_type;';
1430 l(' --hk_debugl(''The SQL query'');');
1427 l_fetch_cur := l_fetch_cur||';';
1428
1429 l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MAP_DTLS_TBL,x_status);');
1431 l(' --hk_debugl(l_sql);');
1432 l(' ');
1433 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1434 l(' hz_utility_v2pub.debug ');
1435 l(' (p_message => '' The SQL query : ''||l_sql,');
1436 l(' p_prefix => l_debug_prefix,');
1437 l(' p_msg_level => fnd_log.level_statement,');
1438 l(' p_module_prefix => l_module_prefix,');
1439 l(' p_module => l_module');
1440 l(' );');
1441 l(' END IF; ');
1442 l(' ');
1443 l(' OPEN c_getGeo FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
1444 l(' '||l_open_cur);
1445 l(' --hk_debugl(''Before the first fetch'');');
1446 l(' ');
1447 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1448 l(' hz_utility_v2pub.debug ');
1449 l(' (p_message => '' Before the first fetch'',');
1450 l(' p_prefix => l_debug_prefix,');
1451 l(' p_msg_level => fnd_log.level_statement,');
1452 l(' p_module_prefix => l_module_prefix,');
1453 l(' p_module => l_module');
1454 l(' );');
1455 l(' END IF; ');
1456 l(' ');
1457 l(' --hk_debugt(L_MAP_DTLS_TBL);');
1458 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
1459 l(' '||l_fetch_cur);
1460 l(' IF c_getGeo%NOTFOUND THEN ');
1461 l(' ');
1462 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1463 l(' hz_utility_v2pub.debug ');
1464 l(' (p_message => '' NOT FOUND of the first fetch'',');
1465 l(' p_prefix => l_debug_prefix,');
1466 l(' p_msg_level => fnd_log.level_statement,');
1467 l(' p_module_prefix => l_module_prefix,');
1468 l(' p_module => l_module');
1469 l(' );');
1470 l(' END IF; ');
1471 l(' ');
1472 l(' --hk_debugl(''NOT FOUND of the first fetch'');');
1473 l(' --hk_debugt(L_MAP_DTLS_TBL);');
1474 l(' x_cause := ''NO_MATCH'';');
1475 l(' HZ_GNR_UTIL_PKG.fix_no_match(L_MAP_DTLS_TBL,x_status);');
1476 l(' --hk_debugl(''Map_loc table after Fix'');');
1477 l(' --hk_debugt(L_MAP_DTLS_TBL);');
1478 l(' x_status := FND_API.G_RET_STS_ERROR;');
1479 l(' ELSE ');
1480 l(' --Fetching once more to see where there are multiple records');
1481 l(' ');
1482 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1483 l(' hz_utility_v2pub.debug ');
1484 l(' (p_message => '' Fetching once more to see where there are multiple records '',');
1485 l(' p_prefix => l_debug_prefix,');
1486 l(' p_msg_level => fnd_log.level_statement,');
1487 l(' p_module_prefix => l_module_prefix,');
1488 l(' p_module => l_module');
1489 l(' );');
1490 l(' END IF; ');
1491 l(' ');
1492 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
1493 l(' '||l_fetch_cur);
1494 l(' IF c_getGeo%FOUND THEN -- not able to identify a unique record');
1495 l(' ');
1496 l(' -- Get the query again with identifier type as NAME if multiple match found');
1497 l(' -- If it returns a record, we are able to derive a unique record for identifier type as NAME');
1498 l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MAP_DTLS_TBL,x_status);');
1499 l(' OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
1500 l(' '||l_open_cur);
1501 l(' ');
1502 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1503 l(' hz_utility_v2pub.debug ');
1504 l(' (p_message => ''Before the fetch of the query with identifier type as NAME after multiple match found'',');
1505 l(' p_prefix => l_debug_prefix,');
1506 l(' p_msg_level => fnd_log.level_statement,');
1507 l(' p_module_prefix => l_module_prefix,');
1508 l(' p_module => l_module');
1509 l(' );');
1510 l(' END IF; ');
1511 l(' ');
1512 l(' --hk_debugt(L_MAP_DTLS_TBL);');
1513 l(' FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
1514 l(' '||l_fetch_cur);
1515 l(' IF c_getGeo1%FOUND THEN ');
1516 ------
1517 l(' -- check if there is another row with same STANDARD_NAME, in that case it is error case ');
1518 l(' FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID ');
1519 l(' '||l_fetch_cur);
1520 l(' IF c_getGeo1%NOTFOUND THEN -- success (only 1 rec with same primary name exists)' );
1521 l(' ');
1522 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1523 l(' hz_utility_v2pub.debug ');
1527 l(' p_module_prefix => l_module_prefix,');
1524 l(' (p_message => ''MAP-Able to found a unique record or a record with multiple parent flag = Y with identifier type as NAME'',');
1525 l(' p_prefix => l_debug_prefix,');
1526 l(' p_msg_level => fnd_log.level_statement,');
1528 l(' p_module => l_module');
1529 l(' );');
1530 l(' END IF; ');
1531 l(' ');
1532 l(' ELSE -- Not able to find a unique record with identifier type as NAME ');
1533 l(' ');
1534 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1535 l(' hz_utility_v2pub.debug ');
1536 l(' (p_message => ''MAP-Not able to find a record with with identifier type as NAME. ''|| ');
1537 l(' '' More than 1 rec exists with same STANDARD NAME'', ');
1538 l(' p_prefix => l_debug_prefix, ');
1539 l(' p_msg_level => fnd_log.level_statement, ');
1540 l(' p_module_prefix => l_module_prefix, ');
1541 l(' p_module => l_module ');
1542 l(' ); ');
1543 l(' END IF; ');
1544 l(' ');
1545 l(' x_cause := ''MULTIPLE_MATCH''; ');
1546 l(' x_status := FND_API.G_RET_STS_ERROR; ');
1547 l(' RETURN; ');
1548 l(' END IF; ');
1549 l(' ');
1550 -------
1551 l(' ELSE -- Not able to found a unique record with identifier type as NAME');
1552 l(' x_cause := ''MULTIPLE_MATCH'';');
1553 l(' ');
1554 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1555 l(' hz_utility_v2pub.debug ');
1556 l(' (p_message => '' Not able to find a record with with identifier type as NAME. x_cause : ''||x_cause,');
1557 l(' p_prefix => l_debug_prefix,');
1558 l(' p_msg_level => fnd_log.level_statement,');
1559 l(' p_module_prefix => l_module_prefix,');
1560 l(' p_module => l_module');
1561 l(' );');
1562 l(' END IF; ');
1563 l(' ');
1564 l(' x_status := FND_API.G_RET_STS_ERROR;');
1565 l(' RETURN;');
1566 l(' END IF; ');
1567 l(' CLOSE c_getGeo1;');
1568 l(' ');
1569 l(' ELSE -- a unique record or a record with multiple parent flag = Y is found');
1570 l(' ');
1571 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1572 l(' hz_utility_v2pub.debug ');
1573 l(' (p_message => '' A unique record or a record with multiple parent flag = Y is found '',');
1574 l(' p_prefix => l_debug_prefix,');
1575 l(' p_msg_level => fnd_log.level_statement,');
1576 l(' p_module_prefix => l_module_prefix,');
1577 l(' p_module => l_module');
1578 l(' );');
1579 l(' END IF; ');
1580 l(' ');
1581 l(' END IF;');
1582 l(' ');
1583 l(' IF l_multiple_parent_flag = ''Y'' THEN');
1584 l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,L_MAP_DTLS_TBL) = TRUE THEN');
1585 l(' NULL; -- a unique record is found');
1586 l(' ELSE -- Multiple parent case not able to find a unique record ');
1587 l(' ');
1588 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1589 l(' hz_utility_v2pub.debug ');
1590 l(' (p_message => '' Multiple parent case not able to find a unique record'',');
1591 l(' p_prefix => l_debug_prefix,');
1592 l(' p_msg_level => fnd_log.level_statement,');
1593 l(' p_module_prefix => l_module_prefix,');
1594 l(' p_module => l_module');
1595 l(' );');
1596 l(' END IF; ');
1597 l(' ');
1598 l(' x_cause := ''MULTIPLE_PARENT'';');
1599 l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1600 l(' x_status := FND_API.G_RET_STS_ERROR;');
1601 l(' ');
1602 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1603 l(' hz_utility_v2pub.debug ');
1604 l(' (p_message => '' x_cause : ''||x_cause,');
1605 l(' p_prefix => l_debug_prefix,');
1606 l(' p_msg_level => fnd_log.level_statement,');
1607 l(' p_module_prefix => l_module_prefix,');
1608 l(' p_module => l_module');
1609 l(' );');
1610 l(' END IF; ');
1611 l(' ');
1612 l(' RETURN;');
1613 l(' END IF;');
1614 l(' ');
1615 l(' ELSE -- a unique record is found');
1616 l(' ');
1617 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1618 l(' hz_utility_v2pub.debug ');
1619 l(' (p_message => ''A unique record is found '',');
1620 l(' p_prefix => l_debug_prefix,');
1621 l(' p_msg_level => fnd_log.level_statement,');
1625 l(' END IF; ');
1622 l(' p_module_prefix => l_module_prefix,');
1623 l(' p_module => l_module');
1624 l(' );');
1626 l(' ');
1627 l(' END IF;');
1628 l(' ');
1629 l(' END IF;');
1630 l(' ');
1631 l(' CLOSE c_getGeo;');
1632 l(' ');
1633 l(' ');
1634 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1635 l(' hz_utility_v2pub.debug ');
1636 l(' (p_message => '' Calling fix_child. This call will try to derive missing lower level compoents.'',');
1637 l(' p_prefix => l_debug_prefix,');
1638 l(' p_msg_level => fnd_log.level_statement,');
1639 l(' p_module_prefix => l_module_prefix,');
1640 l(' p_module => l_module');
1641 l(' );');
1642 l(' END IF; ');
1643 l(' ');
1644 l(' --Following call will try to derive missing lower level compoents ');
1645 l(' IF HZ_GNR_UTIL_PKG.fix_child(L_MAP_DTLS_TBL) = FALSE THEN');
1646 l(' x_cause := ''MISSING_CHILD'';');
1647 l(' ');
1648 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1649 l(' hz_utility_v2pub.debug ');
1650 l(' (p_message => '' x_cause : ''||x_cause,');
1651 l(' p_prefix => l_debug_prefix,');
1652 l(' p_msg_level => fnd_log.level_statement,');
1653 l(' p_module_prefix => l_module_prefix,');
1654 l(' p_module => l_module');
1655 l(' );');
1656 l(' END IF; ');
1657 l(' ');
1658 -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1659 l(' x_status := FND_API.G_RET_STS_ERROR;');
1660 l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1661 l(' RETURN;');
1662 l(' END IF;');
1663 -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1664 l(' ');
1665 l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1666 l(' ');
1667 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1668 l(' hz_utility_v2pub.debug ');
1669 l(' (p_message => ''End of Validate for Map'',');
1670 l(' p_prefix => l_debug_prefix,');
1671 l(' p_msg_level => fnd_log.level_procedure,');
1672 l(' p_module_prefix => l_module_prefix,');
1673 l(' p_module => l_module');
1674 l(' );');
1675 l(' END IF; ');
1676 l(' ');
1677 ---- end procedure
1678 procEnd(l_procName);
1679
1680 i := 0;
1681 IF G_USAGE_TBL.COUNT > 0 THEN
1682 i := G_USAGE_TBL.FIRST;
1683 LOOP
1684 l_usage_id := G_USAGE_TBL(i).USAGE_ID;
1685 l_usage_code := G_USAGE_TBL(i).USAGE_CODE;
1686 l_mdu_tbl_name := 'G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID;
1687 -- name the procedure
1688 IF G_USAGE_TBL(i).USAGE_CODE = 'GEOGRAPHY' THEN
1689 l_procName := 'validateGeo';
1690 ELSIF G_USAGE_TBL(i).USAGE_CODE = 'TAX' THEN
1691 l_procName := 'validateTax';
1692 ELSE
1693 l_procName := 'validate'||G_USAGE_TBL(i).USAGE_ID;
1694 END IF;
1695
1696 l(' ');
1697 -- write the header comments, procedure name
1698 procBegin(l_procName);
1699
1700 -- write the parameters
1701 li(' P_LOCATION_ID IN NUMBER,');
1702 li(' P_COUNTRY IN VARCHAR2,');
1703 li(' P_STATE IN VARCHAR2,');
1704 li(' P_PROVINCE IN VARCHAR2,');
1705 li(' P_COUNTY IN VARCHAR2,');
1706 li(' P_CITY IN VARCHAR2,');
1707 li(' P_POSTAL_CODE IN VARCHAR2,');
1708 li(' P_POSTAL_PLUS4_CODE IN VARCHAR2,');
1709 li(' P_ATTRIBUTE1 IN VARCHAR2,');
1710 li(' P_ATTRIBUTE2 IN VARCHAR2,');
1711 li(' P_ATTRIBUTE3 IN VARCHAR2,');
1712 li(' P_ATTRIBUTE4 IN VARCHAR2,');
1713 li(' P_ATTRIBUTE5 IN VARCHAR2,');
1714 li(' P_ATTRIBUTE6 IN VARCHAR2,');
1715 li(' P_ATTRIBUTE7 IN VARCHAR2,');
1716 li(' P_ATTRIBUTE8 IN VARCHAR2,');
1717 li(' P_ATTRIBUTE9 IN VARCHAR2,');
1718 li(' P_ATTRIBUTE10 IN VARCHAR2,');
1719 li(' P_LOCK_FLAG IN VARCHAR2,');
1720 li(' X_CALL_MAP IN OUT NOCOPY VARCHAR2,');
1721 li(' P_CALLED_FROM IN VARCHAR2,');
1722 li(' P_ADDR_VAL_LEVEL IN VARCHAR2,');
1723 li(' X_ADDR_WARN_MSG OUT NOCOPY VARCHAR2,');
1724 li(' X_ADDR_VAL_STATUS OUT NOCOPY VARCHAR2,');
1725 li(' X_STATUS OUT NOCOPY VARCHAR2) IS');
1726 l(' ');
1727
1728 -- cursor or local variable declaration
1729 li(' l_loc_components_rec HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE;');
1730 l(' ');
1731 li(' TYPE getGeo IS REF CURSOR;');
1732 li(' c_getGeo getGeo;');
1733 li(' c_getGeo1 getGeo;');
1734 l(' ');
1738 li(' l_usage_code VARCHAR2(30);');
1735 li(' l_multiple_parent_flag VARCHAR2(1);');
1736 li(' l_sql VARCHAR2(9000);');
1737 li(' l_cause VARCHAR2(30);');
1739 li(' l_usage_id NUMBER;');
1740 li(' l_status VARCHAR2(1);');
1741 li(' l_get_addr_val VARCHAR2(1);');
1742 li(' l_geography_type VARCHAR2(30);');
1743 li(' l_geography_id NUMBER;');
1744 li(' L_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1745 li(' LL_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1746 li(' L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1747 l(' ');
1748 l(' l_module_prefix CONSTANT VARCHAR2(30) := ''HZ:ARHGNRGB:'||g_pkgName||''';');
1749 l(' l_module CONSTANT VARCHAR2(30) := ''ADDRESS_VALIDATION'';');
1750 l(' l_debug_prefix VARCHAR2(30) := p_location_id;');
1751 l(' ');
1752
1753 populate_mdu_tbl(G_USAGE_TBL(i).USAGE_ID, l_mdu_tbl);
1754 j := 0;
1755 IF L_MDU_TBL.COUNT > 0 THEN
1756 j := L_MDU_TBL.FIRST;
1757 LOOP
1758 li(' l_value'||j||' VARCHAR2(360);');
1759 li(' l_type'||j||' VARCHAR2(30);');
1760
1761 EXIT WHEN j = L_MDU_TBL.LAST;
1762 j := L_MDU_TBL.NEXT(j);
1763 END LOOP;
1764 END IF;
1765 l(' ');
1766
1767 -- procedure body
1768 li('BEGIN ');
1769 l(' ');
1770 li(' -- defaulting the sucess status');
1771 li(' x_status := FND_API.g_ret_sts_success;');
1772 l(' --hk_debugl(''Processing Location record with location_id :- ''||nvl(to_char(p_location_id),''NULL_LOCATION_ID''));');
1773 li(' l_loc_components_rec.COUNTRY := P_COUNTRY;');
1774 li(' l_loc_components_rec.STATE := P_STATE;');
1775 li(' l_loc_components_rec.PROVINCE := P_PROVINCE;');
1776 li(' l_loc_components_rec.COUNTY := P_COUNTY;');
1777 li(' l_loc_components_rec.CITY := P_CITY;');
1778 li(' l_loc_components_rec.POSTAL_CODE := HZ_GNR_UTIL_PKG.postal_code_to_validate(P_COUNTRY,P_POSTAL_CODE);');
1779 li(' l_loc_components_rec.POSTAL_PLUS4_CODE := P_POSTAL_PLUS4_CODE;');
1780 li(' l_loc_components_rec.ATTRIBUTE1 := P_ATTRIBUTE1;');
1781 li(' l_loc_components_rec.ATTRIBUTE2 := P_ATTRIBUTE2;');
1782 li(' l_loc_components_rec.ATTRIBUTE3 := P_ATTRIBUTE3;');
1783 li(' l_loc_components_rec.ATTRIBUTE4 := P_ATTRIBUTE4;');
1784 li(' l_loc_components_rec.ATTRIBUTE5 := P_ATTRIBUTE5;');
1785 li(' l_loc_components_rec.ATTRIBUTE6 := P_ATTRIBUTE6;');
1786 li(' l_loc_components_rec.ATTRIBUTE7 := P_ATTRIBUTE7;');
1787 li(' l_loc_components_rec.ATTRIBUTE8 := P_ATTRIBUTE8;');
1788 li(' l_loc_components_rec.ATTRIBUTE9 := P_ATTRIBUTE9;');
1789 li(' l_loc_components_rec.ATTRIBUTE10 := P_ATTRIBUTE10;');
1790
1791 l(' ');
1792 l(' L_USAGE_ID := '||l_usage_id||';');
1793 l(' L_USAGE_CODE := '''||l_usage_code||''';');
1794 l(' L_MDU_TBL := '||l_mdu_tbl_name||';');
1795 l(' L_MAP_DTLS_TBL := G_MAP_DTLS_TBL;');
1796 l(' l_get_addr_val := ''N'';');
1797 l(' ');
1798 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1799 l(' hz_utility_v2pub.debug ');
1800 l(' (p_message => ''Begin of validation for ''||L_USAGE_CODE,');
1801 l(' p_prefix => l_debug_prefix,');
1802 l(' p_msg_level => fnd_log.level_procedure,');
1803 l(' p_module_prefix => l_module_prefix,');
1804 l(' p_module => l_module');
1805 l(' );');
1806 l(' END IF; ');
1807 l(' ');
1808 l(' IF P_LOCATION_ID IS NOT NULL AND P_CALLED_FROM <> ''GNR'' THEN');
1809 l(' --hk_debugl(''Before check_GNR_For_Usage'');');
1810 l(' IF HZ_GNR_UTIL_PKG.check_GNR_For_Usage(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1811 l(' L_USAGE_CODE,L_MDU_TBL,x_status) = TRUE THEN');
1812 l(' --hk_debugl(''After check_GNR_For_Usage with status :- ''||x_status);');
1813 l(' ');
1814 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1815 l(' hz_utility_v2pub.debug ');
1816 l(' (p_message => ''There is already a procedded success record in GNR log table.'',');
1817 l(' p_prefix => l_debug_prefix,');
1818 l(' p_msg_level => fnd_log.level_statement,');
1819 l(' p_module_prefix => l_module_prefix,');
1820 l(' p_module => l_module');
1821 l(' );');
1822 l(' END IF; ');
1823 l(' ');
1824 l(' x_status := FND_API.g_ret_sts_success;');
1825 l(' X_ADDR_VAL_STATUS := x_status;');
1826 l(' RETURN;');
1827 l(' END IF;');
1828 l(' END IF;');
1829 l(' ');
1830 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1831 l(' hz_utility_v2pub.debug ');
1835 l(' p_module_prefix => l_module_prefix,');
1832 l(' (p_message => ''Not able to find an existing success record in GNR log table.'',');
1833 l(' p_prefix => l_debug_prefix,');
1834 l(' p_msg_level => fnd_log.level_statement,');
1836 l(' p_module => l_module');
1837 l(' );');
1838 l(' END IF; ');
1839 l(' ');
1840 l(' -- After the following call L_MAP_DTLS_TBL will have the components value populated.');
1841 l(' HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MAP_DTLS_TBL,x_status);');
1842 l(' ');
1843 l(' -- Below code will overwrite the LOC_COMPVAL for COUNTRY to COUNTRY_CODE');
1844 l(' -- This change is to update COUNTRY column in locations table with COUNTRY_CODE ');
1845 l(' -- even if the table has Country name in this column and the validation is success ');
1846 l(' L_MAP_DTLS_TBL(1).LOC_COMPVAL := G_MAP_REC.COUNTRY_CODE;');
1847 l(' ');
1848 l(' -- After the following call L_MDU_TBL will have the components value populated.');
1849 l(' HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MDU_TBL,x_status);');
1850 l(' ');
1851 l(' --hk_debugl('' value of X_CALL_MAP : ''||X_CALL_MAP);');
1852 l(' IF X_CALL_MAP = ''Y'' THEN');
1853 l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1854 l(' ');
1855 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1856 l(' hz_utility_v2pub.debug ');
1857 l(' (p_message => ''Before calling validate for Map '',');
1858 l(' p_prefix => l_debug_prefix,');
1859 l(' p_msg_level => fnd_log.level_statement,');
1860 l(' p_module_prefix => l_module_prefix,');
1861 l(' p_module => l_module');
1862 l(' );');
1863 l(' END IF; ');
1864 l(' ');
1865 l(' validateForMap(L_LOC_COMPONENTS_REC,LL_MAP_DTLS_TBL,l_cause,x_status);');
1866 l(' --hk_debugl(''Back from Validate for Map with status :- ''||x_status||''.. and case :''||l_cause);');
1867 l(' ');
1868 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1869 l(' hz_utility_v2pub.debug ');
1870 l(' (p_message => ''Back from Validate for Map with status : .''||x_status||''.. and case :''||l_cause,');
1871 l(' p_prefix => l_debug_prefix,');
1872 l(' p_msg_level => fnd_log.level_statement,');
1873 l(' p_module_prefix => l_module_prefix,');
1874 l(' p_module => l_module');
1875 l(' );');
1876 l(' END IF; ');
1877 l(' ');
1878 -- Check added on 19-APR-2006 (Part of fix for bug 5011366 ) by Nishant
1879 l(' -- This usage level check is required upfront because usage level validation will ignore ');
1880 l(' -- some of the passed in parameters for the complete mapping and may result in wrong status ');
1881 l(' IF (x_status = FND_API.g_ret_sts_error) THEN ');
1882 l(' ');
1883 l(' -- hk_debugl(''Trying to check if usage level validation is success even with map validation as error..''); ');
1884 l(' -- hk_debugl(''TABLE that is returned by Validate For Map''); ');
1885 l(' -- hk_debugt(LL_MAP_DTLS_TBL); ');
1886 l(' -- hk_debugl(''Usage Map Table With loc comp values''); ');
1887 l(' -- hk_debugt(L_MDU_TBL); ');
1888 l(' ');
1889 l(' IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_SUCCESS THEN ');
1890 l(' -- hk_debugl(''COMPLETE mapping is error but is sufficient for passed usage. So setting X_STATUS to success''); ');
1891 l(' x_status := FND_API.g_ret_sts_success; ');
1892 l(' ');
1893 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1894 l(' hz_utility_v2pub.debug ');
1895 l(' (p_message => ''COMPLETE mapping is error but is sufficient for passed usage. So setting X_STATUS to success'',');
1896 l(' p_prefix => l_debug_prefix,');
1897 l(' p_msg_level => fnd_log.level_statement,');
1898 l(' p_module_prefix => l_module_prefix,');
1899 l(' p_module => l_module');
1900 l(' );');
1901 l(' END IF; ');
1902 l(' ');
1903 l(' END IF; ');
1904 l(' END IF; ');
1905 l(' -------End of status check for usage level ----------+ ');
1906 l(' ');
1907
1908 -- end of check addition (bug 5011366)
1909 l(' IF x_status = FND_API.g_ret_sts_success THEN');
1910 l(' --hk_debugt(LL_MAP_DTLS_TBL); ----- Code to display the output.');
1911 l(' -- Set the address validation status to success since x_statusis success ');
1912 l(' X_ADDR_VAL_STATUS := x_status;');
1913 l(' IF P_LOCATION_ID IS NOT NULL THEN');
1914 l(' ');
1915 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1916 l(' hz_utility_v2pub.debug ');
1917 l(' (p_message => '' Location id is not null. Call fill_values, create_gnr and Return back.'',');
1921 l(' p_module => l_module');
1918 l(' p_prefix => l_debug_prefix,');
1919 l(' p_msg_level => fnd_log.level_statement,');
1920 l(' p_module_prefix => l_module_prefix,');
1922 l(' );');
1923 l(' END IF; ');
1924 l(' ');
1925 l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1926 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1927 l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1928 l(' END IF;');
1929 l(' ');
1930 l(' X_CALL_MAP := ''N'';');
1931 l(' RETURN;');
1932 l(' ');
1933 l(' ELSE ');
1934 l(' ');
1935 l(' IF P_LOCATION_ID IS NOT NULL THEN');
1936 l(' --hk_debugl(''Table that is returned by Validate For Map'');');
1937 l(' --hk_debugt(LL_MAP_DTLS_TBL);');
1938 l(' --hk_debugl(''Usage Map Table With loc comp values'');');
1939 l(' --hk_debugt(L_MDU_TBL);');
1940 l(' 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');
1941 l(' ');
1942 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1943 l(' hz_utility_v2pub.debug ');
1944 l(' (p_message => '' No usage level validation is required. Call create_gnr with the map status'',');
1945 l(' p_prefix => l_debug_prefix,');
1946 l(' p_msg_level => fnd_log.level_statement,');
1947 l(' p_module_prefix => l_module_prefix,');
1948 l(' p_module => l_module');
1949 l(' );');
1950 l(' END IF; ');
1951 l(' ');
1952 l(' -- This means no usage level validation is required');
1953 l(' IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_ERROR THEN');
1954 l(' ');
1955 l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1956 l(' -- This below call is to derive the address validation status and set the message ');
1957 l(' 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);');
1958 l(' --hk_debugl(''Calling create_gnr With Map_status "E"'');');
1959 l(' ');
1960 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1961 l(' hz_utility_v2pub.debug ');
1962 l(' (p_message => '' Calling create_gnr with map status E.'',');
1963 l(' p_prefix => l_debug_prefix,');
1964 l(' p_msg_level => fnd_log.level_statement,');
1965 l(' p_module_prefix => l_module_prefix,');
1966 l(' p_module => l_module');
1967 l(' );');
1968 l(' END IF; ');
1969 l(' ');
1970 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1971 l(' L_USAGE_CODE,''E'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1972 l(' --hk_debugl(''Status after create_gnr : ''||l_status);');
1973 l(' ELSE ');
1974 l(' ');
1975 l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1976 l(' -- This below call is to derive the address validation status and set the message ');
1977 l(' 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);');
1978 l(' --hk_debugl(''Calling create_gnr With Map_status "S"'');');
1979 l(' ');
1980 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1981 l(' hz_utility_v2pub.debug ');
1982 l(' (p_message => '' Calling create_gnr with map status S.'',');
1983 l(' p_prefix => l_debug_prefix,');
1984 l(' p_msg_level => fnd_log.level_statement,');
1985 l(' p_module_prefix => l_module_prefix,');
1986 l(' p_module => l_module');
1987 l(' );');
1988 l(' END IF; ');
1989 l(' ');
1990 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1991 l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1992 l(' --hk_debugl(''Status after create_gnr : ''||l_status);');
1993 l(' x_status := FND_API.g_ret_sts_success;');
1994 l(' END IF;');
1995 l(' ');
1996 l(' X_CALL_MAP := ''N'';');
1997 l(' RETURN;');
1998 l(' ');
1999 l(' ELSE ');
2000 l(' NULL; -- do_usage_val has concluded that usage level validation has to go through.');
2001 l(' END IF;');
2002 l(' END IF;');
2003 l(' END IF;');
2004 l(' ');
2008 l(' END IF;');
2005 l(' l_get_addr_val := ''Y'';');
2006 l(' X_CALL_MAP := ''N'';');
2007 l(' ');
2009 l(' ');
2010 l(' IF L_MDU_TBL.COUNT = 1 THEN');
2011 l(' ');
2012 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2013 l(' hz_utility_v2pub.debug ');
2014 l(' (p_message => '' This means country is the only required mapped column for validation. Call create_gnr with map status S'',');
2015 l(' p_prefix => l_debug_prefix,');
2016 l(' p_msg_level => fnd_log.level_statement,');
2017 l(' p_module_prefix => l_module_prefix,');
2018 l(' p_module => l_module');
2019 l(' );');
2020 l(' END IF; ');
2021 l(' ');
2022 l(' -- This means country is the only required mapped column for validation.');
2023 l(' -- and country is already populated in the L_MDU_TBL in the initialization section of this package.');
2024 l(' --hk_debugt(L_MDU_TBL); ----- Code to display the output.');
2025 l(' --hk_debugl(''Calling create_gnr With Map_status "S"'');');
2026 l(' -- This below call is to derive the address validation status and set the message ');
2027 l(' 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);');
2028 l(' ');
2029 l(' IF P_LOCATION_ID IS NOT NULL THEN');
2030 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
2031 l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,L_MDU_TBL,l_status);');
2032 l(' END IF;');
2033 l(' ');
2034 l(' x_status := FND_API.g_ret_sts_success;');
2035 l(' RETURN;');
2036 l(' END IF;');
2037 l(' --hk_debugl(''L_MDU_TBL has count count more than 1'');');
2038 l(' ');
2039
2040 l_open_cur := NULL;
2041 l_fetch_cur:= NULL;
2042
2043 j := 0;
2044 IF G_MAP_DTLS_TBL.COUNT > 0 THEN
2045 j := G_MAP_DTLS_TBL.FIRST;
2046 LOOP
2047 IF j>1 THEN
2048 l_fetch_cur := l_fetch_cur||',LL_MAP_DTLS_TBL('||j||').GEOGRAPHY_ID';
2049 END IF;
2050 EXIT WHEN j = G_MAP_DTLS_TBL.LAST;
2051 j := G_MAP_DTLS_TBL.NEXT(j);
2052 END LOOP;
2053 END IF;
2054
2055 j := 0;
2056 IF L_MDU_TBL.COUNT > 0 THEN
2057 j := L_MDU_TBL.FIRST;
2058 LOOP
2059 IF j>1 THEN
2060 l(' l_value'||j||' := NVL(L_MDU_TBL('||j||').LOC_COMPVAL,''X'') ;');
2061 l(' IF l_value'||j||' = ''X'' THEN');
2062 l(' l_type'||j||' := ''X'';');
2063 l(' ELSE');
2064 l(' l_type'||j||' := L_MDU_TBL('||j||').GEOGRAPHY_TYPE;');
2065 l(' -- store the geography_type of the lowest address component that has a value passed in');
2066 l(' l_geography_type := l_type'||j||';');
2067 l(' END IF;');
2068 l(' ');
2069 l_open_cur := l_open_cur||',l_type'||j||',l_value'||j;
2070 END IF;
2071
2072 EXIT WHEN j = L_MDU_TBL.LAST;
2073 j := L_MDU_TBL.NEXT(j);
2074 END LOOP;
2075 END IF;
2076 l(' ');
2077 l_open_cur := l_open_cur||',l_geography_type;';
2078 l_fetch_cur := l_fetch_cur||';';
2079
2080 l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
2081 l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);');
2082 l(' --hk_debugl(''The SQL query'');');
2083 l(' --hk_debugl(l_sql);');
2084 l(' ');
2085 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2086 l(' hz_utility_v2pub.debug ');
2087 l(' (p_message => '' The SQL query : ''||l_sql,');
2088 l(' p_prefix => l_debug_prefix,');
2089 l(' p_msg_level => fnd_log.level_statement,');
2090 l(' p_module_prefix => l_module_prefix,');
2091 l(' p_module => l_module');
2092 l(' );');
2093 l(' END IF; ');
2094 l(' ');
2095 l(' OPEN c_getGeo FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
2096 l(' '||l_open_cur);
2097 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
2098 l(' '||l_fetch_cur);
2099 l(' IF c_getGeo%NOTFOUND THEN ');
2100 l(' --hk_debugl(''No Match found for the usage level search'');');
2101 l(' ');
2102 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2103 l(' hz_utility_v2pub.debug ');
2104 l(' (p_message => '' No Match found for the usage level search '',');
2105 l(' p_prefix => l_debug_prefix,');
2106 l(' p_msg_level => fnd_log.level_statement,');
2107 l(' p_module_prefix => l_module_prefix,');
2108 l(' p_module => l_module');
2109 l(' );');
2113 l(' x_status := FND_API.G_RET_STS_ERROR;');
2110 l(' END IF; ');
2111 l(' ');
2112 l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status);');
2114 l(' ELSE ');
2115 l(' --Fetching once more to see where there are multiple records');
2116 l(' ');
2117 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2118 l(' hz_utility_v2pub.debug ');
2119 l(' (p_message => '' Fetching once more to see where there are multiple records '',');
2120 l(' p_prefix => l_debug_prefix,');
2121 l(' p_msg_level => fnd_log.level_statement,');
2122 l(' p_module_prefix => l_module_prefix,');
2123 l(' p_module => l_module');
2124 l(' );');
2125 l(' END IF; ');
2126 l(' ');
2127 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
2128 l(' '||l_fetch_cur);
2129 l(' IF c_getGeo%FOUND THEN -- not able to identify a unique record');
2130 l(' ');
2131 l(' -- Get the query again with identifier type as NAME if multiple match found');
2132 l(' -- If it returns a record, we are able to derive a unique record for identifier type as NAME');
2133 l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);');
2134 l(' OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
2135 l(' '||l_open_cur);
2136 l(' ');
2137 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2138 l(' hz_utility_v2pub.debug ');
2139 l(' (p_message => ''Before the fetch of the query with identifier type as NAME after multiple match found'',');
2140 l(' p_prefix => l_debug_prefix,');
2141 l(' p_msg_level => fnd_log.level_statement,');
2142 l(' p_module_prefix => l_module_prefix,');
2143 l(' p_module => l_module');
2144 l(' );');
2145 l(' END IF; ');
2146 l(' ');
2147 l(' --hk_debugt(LL_MAP_DTLS_TBL);');
2148 l(' FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
2149 l(' '||l_fetch_cur);
2150 l(' IF c_getGeo1%FOUND THEN ');
2151 ---- Fix for Bug 5011366 (Nishant)
2152 l(' -- check if there is another row with same STANDARD_NAME, in that case it is error case ');
2153 l(' FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID ');
2154 l(' '||l_fetch_cur);
2155 l(' IF c_getGeo1%NOTFOUND THEN -- success (only 1 rec with same primary name exists)' );
2156 l(' ');
2157 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2158 l(' hz_utility_v2pub.debug ');
2159 l(' (p_message => ''GEO-Able to find a unique record or a record with multiple parent flag = Y with identifier type as NAME'',');
2160 l(' p_prefix => l_debug_prefix,');
2161 l(' p_msg_level => fnd_log.level_statement,');
2162 l(' p_module_prefix => l_module_prefix,');
2163 l(' p_module => l_module');
2164 l(' );');
2165 l(' END IF; ');
2166 l(' ');
2167 l(' ELSE -- Not able to find a unique record with identifier type as NAME ');
2168 l(' ');
2169 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2170 l(' hz_utility_v2pub.debug ');
2171 l(' (p_message => ''GEO-Not able to find a record with with identifier type as NAME. ''|| ');
2172 l(' '' More than 1 rec exists with same STANDARD NAME'', ');
2173 l(' p_prefix => l_debug_prefix, ');
2174 l(' p_msg_level => fnd_log.level_statement, ');
2175 l(' p_module_prefix => l_module_prefix, ');
2176 l(' p_module => l_module ');
2177 l(' ); ');
2178 l(' END IF; ');
2179 l(' ');
2180 l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL; ');
2181 l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status); ');
2182 l(' x_status := FND_API.G_RET_STS_ERROR; ');
2183 l(' END IF; ');
2184 l(' ');
2185 ----- End of fix for Bug 5011366 (Nishant)
2186 l(' ELSE -- Not able to found a unique record with identifier type as NAME');
2187 l(' ');
2188 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2189 l(' hz_utility_v2pub.debug ');
2190 l(' (p_message => '' Not able to find a record with with identifier type as NAME. '',');
2194 l(' p_module => l_module');
2191 l(' p_prefix => l_debug_prefix,');
2192 l(' p_msg_level => fnd_log.level_statement,');
2193 l(' p_module_prefix => l_module_prefix,');
2195 l(' );');
2196 l(' END IF; ');
2197 l(' ');
2198 l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
2199 l(' x_status := FND_API.G_RET_STS_ERROR;');
2200 l(' END IF; ');
2201 l(' CLOSE c_getGeo1;');
2202 l(' ');
2203 l(' ELSE -- a unique record or a record with multiple parent flag = Y is found');
2204 l(' ');
2205 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2206 l(' hz_utility_v2pub.debug ');
2207 l(' (p_message => '' A unique record or a record with multiple parent flag = Y is found '',');
2208 l(' p_prefix => l_debug_prefix,');
2209 l(' p_msg_level => fnd_log.level_statement,');
2210 l(' p_module_prefix => l_module_prefix,');
2211 l(' p_module => l_module');
2212 l(' );');
2213 l(' END IF; ');
2214 l(' ');
2215 l(' END IF;');
2216 l(' ');
2217 l(' IF l_multiple_parent_flag = ''Y'' AND x_status <> FND_API.G_RET_STS_ERROR THEN');
2218 l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,LL_MAP_DTLS_TBL) = TRUE THEN');
2219 l(' NULL;');
2220 l(' ELSE -- Multiple parent case not able to find a unique record ');
2221 l(' ');
2222 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2223 l(' hz_utility_v2pub.debug ');
2224 l(' (p_message => '' Multiple parent case not able to find a unique record'',');
2225 l(' p_prefix => l_debug_prefix,');
2226 l(' p_msg_level => fnd_log.level_statement,');
2227 l(' p_module_prefix => l_module_prefix,');
2228 l(' p_module => l_module');
2229 l(' );');
2230 l(' END IF; ');
2231 l(' ');
2232 l(' x_status := FND_API.G_RET_STS_ERROR;');
2233 l(' END IF;');
2234 l(' ');
2235 l(' ELSE -- a unique record is found');
2236 l(' ');
2237 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2238 l(' hz_utility_v2pub.debug ');
2239 l(' (p_message => '' A unique record is found '',');
2240 l(' p_prefix => l_debug_prefix,');
2241 l(' p_msg_level => fnd_log.level_statement,');
2242 l(' p_module_prefix => l_module_prefix,');
2243 l(' p_module => l_module');
2244 l(' );');
2245 l(' END IF; ');
2246 l(' ');
2247 l(' END IF;');
2248 l(' END IF;');
2249 l(' CLOSE c_getGeo;');
2250 l(' ');
2251 l(' --hk_debugl(''Return STatus after first fetch : ''||x_status);');
2252 l(' --Following call will try to derive missing lower level compoents ');
2253 l(' ');
2254 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2255 l(' hz_utility_v2pub.debug ');
2256 l(' (p_message => '' Return Status after first fetch : ''||x_status,');
2257 l(' p_prefix => l_debug_prefix,');
2258 l(' p_msg_level => fnd_log.level_statement,');
2259 l(' p_module_prefix => l_module_prefix,');
2260 l(' p_module => l_module');
2261 l(' );');
2262 l(' END IF; ');
2263 l(' ');
2264 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2265 l(' hz_utility_v2pub.debug ');
2266 l(' (p_message => '' Calling fix_child. This call will try to derive missing lower level compoents.'',');
2267 l(' p_prefix => l_debug_prefix,');
2268 l(' p_msg_level => fnd_log.level_statement,');
2269 l(' p_module_prefix => l_module_prefix,');
2270 l(' p_module => l_module');
2271 l(' );');
2272 l(' END IF; ');
2273 l(' ');
2274 l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MAP_DTLS_TBL) = FALSE THEN');
2275 l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL);');
2276 l(' END IF;');
2277 l(' ');
2278 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2279 l(' hz_utility_v2pub.debug ');
2280 l(' (p_message => '' Return status after fix_child ''||x_status,');
2281 l(' p_prefix => l_debug_prefix,');
2282 l(' p_msg_level => fnd_log.level_statement,');
2283 l(' p_module_prefix => l_module_prefix,');
2284 l(' p_module => l_module');
2285 l(' );');
2286 l(' END IF; ');
2287 l(' ');
2291 l(' --hk_debugl(''LL_MAP_DTLS_TBL after fill_values'');');
2288 l(' --hk_debugl(''LL_MAP_DTLS_TBL before fill_values'');');
2289 l(' --hk_debugt(LL_MAP_DTLS_TBL);');
2290 l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
2292 l(' --hk_debugt(LL_MAP_DTLS_TBL);');
2293 l(' ');
2294 l(' IF x_status = FND_API.g_ret_sts_success THEN');
2295 l(' -- We need to call the getAddrValStatus only once. All other cases we are looking into x_call_map ');
2296 l(' -- In some case the below code will execute with the x_call_map as N ');
2297 l(' IF l_get_addr_val = ''Y'' THEN');
2298 l(' -- This below call is to derive the address validation status and set the message ');
2299 l(' 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);');
2300 l(' END IF;');
2301 l(' ');
2302 l(' IF P_LOCATION_ID IS NOT NULL THEN');
2303 l(' ');
2304 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2305 l(' hz_utility_v2pub.debug ');
2306 l(' (p_message => '' Calling create_gnr with map status S.'',');
2307 l(' p_prefix => l_debug_prefix,');
2308 l(' p_msg_level => fnd_log.level_statement,');
2309 l(' p_module_prefix => l_module_prefix,');
2310 l(' p_module => l_module');
2311 l(' );');
2312 l(' END IF; ');
2313 l(' ');
2314 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
2315 l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
2316 l(' --hk_debugl(''Prceossed GNR With Status : S and returned with Status : ''||l_status);');
2317 l(' END IF;');
2318 l(' ELSE ');
2319 l(' -- We need to call the getAddrValStatus only once. All other cases we are looking into x_call_map ');
2320 l(' -- In some case the below code will execute with the x_call_map as N ');
2321 l(' IF l_get_addr_val = ''Y'' THEN');
2322 l(' -- This below call is to derive the address validation status and set the message ');
2323 l(' 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);');
2324 l(' END IF;');
2325 l(' ');
2326 l(' IF P_LOCATION_ID IS NOT NULL THEN');
2327 l(' ');
2328 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2329 l(' hz_utility_v2pub.debug ');
2330 l(' (p_message => '' Calling create_gnr with map status E.'',');
2331 l(' p_prefix => l_debug_prefix,');
2332 l(' p_msg_level => fnd_log.level_statement,');
2333 l(' p_module_prefix => l_module_prefix,');
2334 l(' p_module => l_module');
2335 l(' );');
2336 l(' END IF; ');
2337 l(' ');
2338 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
2339 l(' L_USAGE_CODE,''E'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
2340 l(' --hk_debugl(''Prceossed GNR With Status : E and returned with Status : ''||l_status);');
2341 l(' END IF;');
2342 l(' END IF;');
2343 l(' ');
2344 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2345 l(' hz_utility_v2pub.debug ');
2346 l(' (p_message => ''End of validation for ''||L_USAGE_CODE,');
2347 l(' p_prefix => l_debug_prefix,');
2348 l(' p_msg_level => fnd_log.level_procedure,');
2349 l(' p_module_prefix => l_module_prefix,');
2350 l(' p_module => l_module');
2351 l(' );');
2352 l(' END IF; ');
2353 l(' ');
2354 l(' --hk_debugt(LL_MAP_DTLS_TBL); ----- Code to display the output.');
2355 ---- end procedure
2356 procEnd(l_procName);
2357 EXIT WHEN i = G_USAGE_TBL.LAST;
2358 i := G_USAGE_TBL.NEXT(i);
2359 END LOOP;
2360 END IF;
2361
2362 END validateBody;
2363 --------------------------------------
2364 PROCEDURE genSpec(
2365 x_status OUT NOCOPY VARCHAR2) IS
2366 l_count number;
2367 l_procName varchar2(10);
2368 l_tmp varchar2(4);
2369
2370 BEGIN
2371 -- flow
2372 -- 1. create the package header
2373 -- 2. generate spec for srchGeo()
2374 -- 3. create the package tail
2375 --
2376
2377 -- initializing the retun value
2378 x_status := FND_API.G_RET_STS_SUCCESS;
2379
2380 genPkgSpecHdr(g_pkgName);
2381 -- known caveat: for srchGeoSpec - the procedure comments do not have all the
2382 -- input variables.
2383 IF g_map_rec.LOC_TBL_NAME = 'HR_LOCATIONS_ALL' THEN
2384 validateHrSpec();
2385 END IF;
2389 END IF;
2386 IF g_map_rec.LOC_TBL_NAME = 'HZ_LOCATIONS' THEN
2387 get_usage_API_Spec;
2388 validateSpec();
2390 genPkgSpecTail(g_pkgName);
2391
2392
2393 END genSpec;
2394 --------------------------------------
2395 PROCEDURE genBody(
2396 x_status OUT NOCOPY VARCHAR2) IS
2397
2398 l_count number;
2399 i number;
2400 l_procName varchar2(10);
2401 l_tmp varchar2(4);
2402 l_level number;
2403
2404 BEGIN
2405 /* flow
2406 1. Generate the package header
2407 2. validateBody()
2408 3. generate the package tail
2409 */
2410
2411 -- initializing the retun value
2412 x_status := FND_API.G_RET_STS_SUCCESS;
2413
2414 -- package header
2415 genPkgBdyHdr(g_pkgName);
2416
2417 ---- writing the global variables
2418 li('--------------------------------------');
2419 li(' -- declaration of private global varibles');
2420 li(' --------------------------------------');
2421 l(' ');
2422 li(' g_debug_count NUMBER := 0;');
2423 li(' g_country_geo_id NUMBER;');
2424 li(' G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;');
2425 li(' G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
2426 li(' G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;');
2427 li(' G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;');
2428 -- create global variables with mapping details per usage
2429 i:=0;
2430 IF G_USAGE_TBL.COUNT > 0 THEN
2431 i := G_USAGE_TBL.FIRST;
2432 LOOP
2433 li(' G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID||' HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
2434 EXIT WHEN i = G_USAGE_TBL.LAST;
2435 i := G_USAGE_TBL.NEXT(i);
2436 END LOOP;
2437 END IF;
2438 l(' ');
2439 li(' --------------------------------------');
2440 li(' -- declaration of private procedures and functions');
2441 li(' --------------------------------------');
2442 l(' ');
2443 li(' --------------------------------------');
2444 li(' -- private procedures and functions');
2445 li(' --------------------------------------');
2446 l(' ');
2447
2448 IF g_map_rec.LOC_TBL_NAME = 'HZ_LOCATIONS' THEN
2449 get_usage_API_Body;
2450 validateBody(x_status);
2451 END IF;
2452 IF g_map_rec.LOC_TBL_NAME = 'HR_LOCATIONS_ALL' THEN
2453 validateHrBody(x_status);
2454 END IF;
2455
2456 genPkgBdyInit(x_status);
2457 -- write the pkg end
2458 genPkgBdyTail(g_pkgName);
2459 END genBody;
2460
2461 --------------------------------------
2462 -- procedures and functions
2463 --------------------------------------
2464 --------------------------------------
2465 /**
2466 * PROCEDURE genPkg
2467 *
2468 * DESCRIPTION
2469 * This private procedure is used to generate map specific package with
2470 * GNR search procedures
2471 *
2472 *
2473 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2474 *
2475 * ARGUMENTS
2476 * IN:
2477 *
2478 * p_map_rec Mapping Record
2479 * p_map_dtls_tbl table of records of map details
2480 * p_usage_tbl Table of records of Usages
2481 * p_usage_dtls_tbl Table of records of all usages and their details populated for usage after other.
2482 *
2483 * OUT:
2484 *
2485 * x_pkgName generated package name
2486 * x_status indicates if the genPkg was sucessfull or not.
2487 *
2488 * NOTES
2489 *
2490 *
2491 * MODIFICATION HISTORY
2492 *
2493 *
2494 */
2495 --------------------------------------
2496 procedure genPkg(
2497 p_map_rec IN HZ_GNR_UTIL_PKG.map_rec_type,
2498 p_map_dtls_tbl IN HZ_GNR_UTIL_PKG.maploc_rec_tbl_type,
2499 p_usage_tbl IN HZ_GNR_UTIL_PKG.usage_tbl_type,
2500 p_usage_dtls_tbl IN HZ_GNR_UTIL_PKG.usage_dtls_tbl_type,
2501 x_pkgName OUT NOCOPY VARCHAR2,
2502 x_status OUT NOCOPY VARCHAR2) IS
2503
2504 BEGIN
2505
2506 -- flow
2507 -- prepare the packge name
2508 -- genSpec()
2509 -- genBody()
2510
2511 -- Hari 2 Lines
2512 g_type := 'S';
2513 -- initializing the retun value
2514 x_status := FND_API.G_RET_STS_SUCCESS;
2515
2516 -- prepare the packge name
2517 x_pkgName := 'HZ_GNR_MAP'||p_map_rec.map_id;
2518 g_map_rec := p_map_rec;
2519 g_map_dtls_tbl := p_map_dtls_tbl;
2520 g_usage_tbl := p_usage_tbl;
2521 g_usage_dtls_tbl := p_usage_dtls_tbl;
2522 g_pkgName := x_pkgName;
2523
2524 genSpec(x_status);
2525 IF x_status <> FND_API.G_RET_STS_SUCCESS THEN
2526 --dbms_output.put_line('genSpec in genPkg'||sqlerrm);
2527 RAISE FND_API.G_EXC_ERROR;
2528 END IF;
2529
2530 -- Hari 1 Line
2531 g_type := 'B';
2532 genBody(x_status);
2533 IF x_status <> FND_API.G_RET_STS_SUCCESS THEN
2534 --dbms_output.put_line('genBody in genPkg'||sqlerrm);
2535 RAISE FND_API.G_EXC_ERROR;
2536 END IF;
2537
2538 END genPkg;
2539 --------------------------------------
2540 procedure genPkg(
2541 p_map_id IN NUMBER,
2542 x_pkgName OUT NOCOPY VARCHAR2,
2543 x_status OUT NOCOPY VARCHAR2) IS
2544
2545 m number := 0;
2546 n number := 0;
2547
2548 l_map_rec HZ_GNR_UTIL_PKG.MAP_REC_TYPE;
2552
2549 l_map_dtls_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
2550 l_usage_tbl HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;
2551 l_usage_dtls_tbl HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;
2553 CURSOR c_map(p_map_id IN NUMBER) IS
2554 SELECT MAP_ID,COUNTRY_CODE,LOC_TBL_NAME,ADDRESS_STYLE
2555 FROM hz_geo_struct_map
2556 WHERE map_id = p_map_id;
2557
2558 CURSOR c_map_dtls(p_map_id IN NUMBER) IS
2559 SELECT MAP_ID,LOC_SEQ_NUM,LOC_COMPONENT,GEOGRAPHY_TYPE,GEO_ELEMENT_COL
2560 FROM hz_geo_struct_map_dtl
2561 WHERE map_id = p_map_id
2562 ORDER BY LOC_SEQ_NUM;
2563
2564 CURSOR c_usage(p_map_id IN NUMBER) IS
2565 SELECT MAP_ID,USAGE_ID,USAGE_CODE
2566 FROM hz_address_usages
2567 WHERE map_id = p_map_id
2568 AND status_flag = 'A'
2569 ORDER BY usage_id;
2570
2571 CURSOR c_usage_dtls(p_usage_id IN NUMBER) IS
2572 SELECT udtl.USAGE_ID,udtl.GEOGRAPHY_TYPE
2573 FROM hz_address_usage_dtls udtl
2574 WHERE USAGE_ID = p_usage_id;
2575
2576 l_map_exists varchar2(1);
2577 l_map_dtls_exists varchar2(1);
2578 l_usage_exists varchar2(1);
2579 l_usage_dtls_exists varchar2(1);
2580
2581 BEGIN
2582 -- initializing the retun value
2583 x_status := FND_API.G_RET_STS_SUCCESS;
2584 l_map_exists := 'N';
2585 FOR l_c_map IN c_map(p_map_id) LOOP -- only one record will be fetched
2586 l_map_exists := 'Y';
2587 l_map_rec.MAP_ID := l_c_map.MAP_ID;
2588 l_map_rec.COUNTRY_CODE := l_c_map.COUNTRY_CODE;
2589 l_map_rec.LOC_TBL_NAME := l_c_map.LOC_TBL_NAME;
2590 l_map_rec.ADDRESS_STYLE := l_c_map.ADDRESS_STYLE;
2591 l_map_dtls_exists := 'N';
2592 FOR l_c_map_dtls IN c_map_dtls(p_map_id) LOOP
2593 l_map_dtls_exists := 'Y';
2594 m := m+1;
2595 l_map_dtls_tbl(m).loc_seq_num := l_c_map_dtls.loc_seq_num;
2596 l_map_dtls_tbl(m).loc_component := l_c_map_dtls.loc_component;
2597 l_map_dtls_tbl(m).geography_type := l_c_map_dtls.geography_type;
2598 l_map_dtls_tbl(m).geo_element_col := l_c_map_dtls.geo_element_col;
2599 l_map_dtls_tbl(m).loc_compval := null;
2600 l_map_dtls_tbl(m).geography_id := null;
2601 END LOOP;
2602 IF l_map_dtls_exists = 'N' THEN
2603 x_status := FND_API.G_RET_STS_ERROR;
2604 RAISE FND_API.G_EXC_ERROR;
2605 END IF;
2606 m :=0;
2607 l_usage_exists := 'N';
2608 FOR l_c_usage in c_usage(p_map_id) LOOP
2609 l_usage_exists := 'Y';
2610 m := m+1;
2611 l_usage_tbl(m).USAGE_ID := l_c_usage.USAGE_ID;
2612 l_usage_tbl(m).MAP_ID := l_c_usage.MAP_ID;
2613 l_usage_tbl(m).USAGE_CODE := l_c_usage.USAGE_CODE;
2614 l_usage_dtls_exists := 'N';
2615 FOR l_c_usage_dtls IN c_usage_dtls(l_c_usage.usage_id) LOOP
2616 l_usage_dtls_exists := 'Y';
2617 n := n+1;
2618 l_usage_dtls_tbl(n).USAGE_ID := l_c_usage_dtls.USAGE_ID;
2619 l_usage_dtls_tbl(n).GEOGRAPHY_TYPE := l_c_usage_dtls.GEOGRAPHY_TYPE;
2620 END LOOP;
2621 IF l_usage_dtls_exists = 'N' THEN
2622 x_status := FND_API.G_RET_STS_ERROR;
2623 RAISE FND_API.G_EXC_ERROR;
2624 END IF;
2625 END LOOP;
2626 IF l_usage_exists = 'N' THEN
2627 x_status := FND_API.G_RET_STS_ERROR;
2628 RAISE FND_API.G_EXC_ERROR;
2629 END IF;
2630 END LOOP;
2631 IF l_map_exists = 'N' THEN
2632 x_status := FND_API.G_RET_STS_ERROR;
2633 RAISE FND_API.G_EXC_ERROR;
2634 END IF;
2635
2636 genpkg(l_map_rec,l_map_dtls_tbl,l_usage_tbl,l_usage_dtls_tbl,x_pkgname,x_status);
2637 END genPkg;
2638 END HZ_GNR_GEN_PKG;
2639