[Home] [Help]
PACKAGE BODY: APPS.HZ_GNR_GEN_PKG
Source
1 PACKAGE BODY HZ_GNR_GEN_PKG AS
2 /*$Header: ARHGNRGB.pls 120.32 2007/11/15 01:40:53 nsinghai 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||''';');
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
449 IF L_MDU_TBL(j).GEOGRAPHY_TYPE = 'COUNTRY' THEN
450 l(' G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID||'('||j||').GEOGRAPHY_ID := '||g_country_geo_id||';');
451 END IF;
452
456 END IF;
453 EXIT WHEN j = L_MDU_TBL.LAST;
454 j := L_MDU_TBL.NEXT(j);
455 END LOOP;
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(' ');
626 li('END '||p_procName||';');
627 END procEnd;
628 --------------------------------------
629 /**
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 */
646
643 --------------------------------------
644 PROCEDURE get_usage_API_Body IS
645 l_funcName varchar2(30);
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,');
778 li(' P_COUNTRY IN VARCHAR2,');
779 li(' P_STATE IN VARCHAR2,');
780 li(' P_PROVINCE IN VARCHAR2,');
781 li(' P_COUNTY 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,');
798 li(' P_ADDR_VAL_LEVEL 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,');
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 i := 0;
879 IF L_MDU_TBL.COUNT > 0 THEN
880 i := L_MDU_TBL.FIRST;
881 LOOP
882 li(' l_value'||i||' VARCHAR2(360);');
883 li(' l_type'||i||' VARCHAR2(30);');
884
885 EXIT WHEN i = L_MDU_TBL.LAST;
886 i := L_MDU_TBL.NEXT(i);
887 END LOOP;
888 END IF;
889
890 l(' ');
891 li('BEGIN ');
892 li(' -- defaulting the sucess status');
893 l(' x_status := FND_API.g_ret_sts_success;');
894 l(' L_MDU_TBL := '||l_mdu_tbl_name||';');
895 l(' LL_MDU_TBL := '||l_mdu_tbl_name||';');
896 l(' --hk_debugl(''Validate HR Loc Start for the location_id :''||p_location_id);');
897 l(' --hk_debugl(''The MDU table structure'');');
898 l(' --hk_debugt(L_MDU_TBL);');
899 l(' ');
900 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
901 l(' hz_utility_v2pub.debug ');
902 l(' (p_message => ''Begin of validation for validateHrLoc.'',');
903 l(' p_prefix => l_debug_prefix,');
904 l(' p_msg_level => fnd_log.level_procedure,');
905 l(' p_module_prefix => l_module_prefix,');
906 l(' p_module => l_module');
907 l(' );');
908 l(' END IF; ');
909 l(' ');
910 l(' IF L_MDU_TBL.COUNT = 1 THEN');
911 l(' ');
912 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
913 l(' hz_utility_v2pub.debug ');
914 l(' (p_message => '' This means country is the only required mapped column for validation. Call create_gnr with map status S'',');
915 l(' p_prefix => l_debug_prefix,');
916 l(' p_msg_level => fnd_log.level_statement,');
917 l(' p_module_prefix => l_module_prefix,');
918 l(' p_module => l_module');
919 l(' );');
920 l(' END IF; ');
921 l(' ');
922 l(' -- This means country is the only required mapped column for validation.');
926 l(' IF P_LOCATION_ID IS NOT NULL THEN');
923 l(' -- and country is already populated in the L_MDU_TBL in the initialization section of this package.');
924 l(' --hk_debugt(L_MDU_TBL); ----- Code to display the output.');
925 l(' --hk_debugl(''Calling create_gnr With Map_status "S"'');');
927 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
928 l(' ''TAX'',''S'',l_loc_components_rec,''T'',L_MDU_TBL,l_status);');
929 l(' END IF;');
930 l(' x_status := FND_API.g_ret_sts_success;');
931 l(' RETURN;');
932 l(' END IF;');
933 l(' --hk_debugl(''L_MDU_TBL has count count more than 1'');');
934 l(' ');
935
936 i := 0;
937 IF L_MDU_TBL.COUNT > 0 THEN
938 i := L_MDU_TBL.FIRST;
939 l(' BEGIN ');
940 l(' SELECT '||L_MDU_TBL(i).LOC_COMPONENT);
941 LOOP
942 EXIT WHEN i = L_MDU_TBL.LAST;
943 i := L_MDU_TBL.NEXT(i);
944 l(' ,'||L_MDU_TBL(i).LOC_COMPONENT);
945 END LOOP;
946 i := L_MDU_TBL.FIRST;
947 l(' INTO L_MDU_TBL('||i||').LOC_COMPVAL');
948 LOOP
949 EXIT WHEN i = L_MDU_TBL.LAST;
950 i := L_MDU_TBL.NEXT(i);
951 l(' ,L_MDU_TBL('||i||').LOC_COMPVAL');
952 END LOOP;
953
954 l(' FROM HR_LOCATIONS_ALL ');
955 l(' WHERE LOCATION_ID = P_LOCATION_ID;');
956 l(' ');
957 l(' EXCEPTION WHEN OTHERS THEN ');
958 l(' x_status := FND_API.g_ret_sts_error;');
959 l(' END; ');
960 END IF;
961 l(' --hk_debugl(''The MDU table after location components populated`'');');
962 l(' --hk_debugt(L_MDU_TBL);');
963
964 l_open_cur := NULL;
965 l_fetch_cur:= NULL;
966
967 j := 0;
968 IF L_MDU_TBL.COUNT > 0 THEN
969 j := L_MDU_TBL.FIRST;
970 LOOP
971 IF j>1 THEN
972 l(' l_value'||j||' := NVL(L_MDU_TBL('||j||').LOC_COMPVAL,''X'') ;');
973 l(' IF l_value'||j||' = ''X'' THEN');
974 l(' l_type'||j||' := ''X'';');
975 l(' ELSE');
976 l(' l_type'||j||' := L_MDU_TBL('||j||').GEOGRAPHY_TYPE;');
977 l(' -- store the geography_type of the lowest address component that has a value passed in');
978 l(' l_geography_type := l_type'||j||';');
979 l(' END IF;');
980 l(' ');
981 l_open_cur := l_open_cur||',l_type'||j||',l_value'||j;
982 l_fetch_cur := l_fetch_cur||',LL_MDU_TBL('||j||').GEOGRAPHY_ID';
983 END IF;
984
985 EXIT WHEN j = L_MDU_TBL.LAST;
986 j := L_MDU_TBL.NEXT(j);
987 END LOOP;
988 END IF;
989 l(' ');
990 l_open_cur := l_open_cur||',l_geography_type;';
991 l_fetch_cur := l_fetch_cur||';';
992 l(' LL_MDU_TBL := L_MDU_TBL;');
993 l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MDU_TBL,L_MDU_TBL,x_status);');
994 l(' --hk_debugl(''The SQL query'');');
995 l(' --hk_debugl(l_sql);');
996 l(' ');
997 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
998 l(' hz_utility_v2pub.debug ');
999 l(' (p_message => '' The SQL query : ''||l_sql,');
1000 l(' p_prefix => l_debug_prefix,');
1001 l(' p_msg_level => fnd_log.level_statement,');
1002 l(' p_module_prefix => l_module_prefix,');
1003 l(' p_module => l_module');
1004 l(' );');
1005 l(' END IF; ');
1006 l(' ');
1007 l(' OPEN c_getGeo FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
1008 l(' '||l_open_cur);
1009 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MDU_TBL(1).GEOGRAPHY_ID');
1010 l(' '||l_fetch_cur);
1011 l(' IF c_getGeo%NOTFOUND THEN ');
1012 l(' --hk_debugl(''No Match found for the usage level search'');');
1013 l(' ');
1014 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1015 l(' hz_utility_v2pub.debug ');
1016 l(' (p_message => '' No Match found for the usage level search '',');
1017 l(' p_prefix => l_debug_prefix,');
1018 l(' p_msg_level => fnd_log.level_statement,');
1019 l(' p_module_prefix => l_module_prefix,');
1020 l(' p_module => l_module');
1021 l(' );');
1022 l(' END IF; ');
1023 l(' ');
1024 l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MDU_TBL,x_status);');
1025 l(' x_status := FND_API.G_RET_STS_ERROR;');
1026 l(' ELSE ');
1027 l(' --Fetching once more to see where there are multiple records');
1028 l(' ');
1029 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1030 l(' hz_utility_v2pub.debug ');
1031 l(' (p_message => '' Fetching once more to see where there are multiple records '',');
1032 l(' p_prefix => l_debug_prefix,');
1033 l(' p_msg_level => fnd_log.level_statement,');
1034 l(' p_module_prefix => l_module_prefix,');
1035 l(' p_module => l_module');
1036 l(' );');
1037 l(' END IF; ');
1038 l(' ');
1042 l(' --hk_debugl(''Multiple Match found for the usage level search'');');
1039 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MDU_TBL(1).GEOGRAPHY_ID');
1040 l(' '||l_fetch_cur);
1041 l(' IF c_getGeo%FOUND THEN -- not able to identify a unique record');
1043
1044
1045 l(' -- Get the query again with identifier type as NAME if multiple match found');
1046 l(' -- If it returns a record, we are able to derive a unique record for identifier type as NAME');
1047 l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MDU_TBL,L_MDU_TBL,x_status);');
1048 l(' OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
1049 l(' '||l_open_cur);
1050 l(' ');
1051 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1052 l(' hz_utility_v2pub.debug ');
1053 l(' (p_message => ''Before the fetch of the query with identifier type as NAME after multiple match found'',');
1054 l(' p_prefix => l_debug_prefix,');
1055 l(' p_msg_level => fnd_log.level_statement,');
1056 l(' p_module_prefix => l_module_prefix,');
1057 l(' p_module => l_module');
1058 l(' );');
1059 l(' END IF; ');
1060 l(' ');
1061 l(' --hk_debugt(LL_MDU_TBL);');
1062 l(' FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,LL_MDU_TBL(1).GEOGRAPHY_ID');
1063 l(' '||l_fetch_cur);
1064 l(' IF c_getGeo1%FOUND THEN ');
1065 l(' ');
1066 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1067 l(' hz_utility_v2pub.debug ');
1068 l(' (p_message => ''Able to found a unique record or a record with multiple parent flag = Y with identifier type as NAME'',');
1069 l(' p_prefix => l_debug_prefix,');
1070 l(' p_msg_level => fnd_log.level_statement,');
1071 l(' p_module_prefix => l_module_prefix,');
1072 l(' p_module => l_module');
1073 l(' );');
1074 l(' END IF; ');
1075 l(' ');
1076 l(' ELSE -- Not able to found a unique record with identifier type as NAME');
1077 l(' ');
1078 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1079 l(' hz_utility_v2pub.debug ');
1080 l(' (p_message => '' Not able to find a record with with identifier type as NAME. '',');
1081 l(' p_prefix => l_debug_prefix,');
1082 l(' p_msg_level => fnd_log.level_statement,');
1083 l(' p_module_prefix => l_module_prefix,');
1084 l(' p_module => l_module');
1085 l(' );');
1086 l(' END IF; ');
1087 l(' ');
1088 l(' LL_MDU_TBL := L_MDU_TBL;');
1089 l(' x_status := FND_API.G_RET_STS_ERROR;');
1090 l(' END IF; ');
1091 l(' CLOSE c_getGeo1;');
1092 l(' ');
1093
1094
1095 l(' ELSE -- a unique record or a record with multiple parent flag = Y is found');
1096 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1097 l(' hz_utility_v2pub.debug ');
1098 l(' (p_message => '' A unique record or a record with multiple parent flag = Y is found '',');
1099 l(' p_prefix => l_debug_prefix,');
1100 l(' p_msg_level => fnd_log.level_statement,');
1101 l(' p_module_prefix => l_module_prefix,');
1102 l(' p_module => l_module');
1103 l(' );');
1104 l(' END IF; ');
1105 l(' END IF;');
1106 l(' ');
1107 l(' IF l_multiple_parent_flag = ''Y'' AND x_status <> FND_API.G_RET_STS_ERROR THEN');
1108 l(' --hk_debugl(''Multiple Parent Match found for the usage level search'');');
1109 l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,LL_MDU_TBL) = TRUE THEN');
1110 l(' --hk_debugl(''Sucessfully Fixed the Multiple Parent Case '');');
1111 l(' NULL;');
1112 l(' ELSE -- Multiple parent case not able to find a unique record ');
1113 l(' ');
1114 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1115 l(' hz_utility_v2pub.debug ');
1116 l(' (p_message => '' Multiple parent case not able to find a unique record'',');
1117 l(' p_prefix => l_debug_prefix,');
1118 l(' p_msg_level => fnd_log.level_statement,');
1119 l(' p_module_prefix => l_module_prefix,');
1120 l(' p_module => l_module');
1121 l(' );');
1122 l(' END IF; ');
1123 l(' ');
1124 l(' --hk_debugl(''Unable to Fix the Multiple Parent Case '');');
1125 l(' x_status := FND_API.G_RET_STS_ERROR;');
1126 l(' END IF;');
1127 l(' ELSE -- a unique record is found');
1128 l(' --hk_debugl(''Successfully found a unique record '');');
1129 l(' ');
1130 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1131 l(' hz_utility_v2pub.debug ');
1135 l(' p_module_prefix => l_module_prefix,');
1132 l(' (p_message => '' A unique record is found '',');
1133 l(' p_prefix => l_debug_prefix,');
1134 l(' p_msg_level => fnd_log.level_statement,');
1136 l(' p_module => l_module');
1137 l(' );');
1138 l(' END IF; ');
1139 l(' ');
1140 l(' END IF;');
1141 l(' END IF;');
1142 l(' CLOSE c_getGeo;');
1143 l(' ');
1144 l(' IF x_status = FND_API.G_RET_STS_SUCCESS THEN ');
1145 l(' --Following call will try to derive missing lower level compoents ');
1146 l(' --hk_debugl(''LL_MDU Table before Fix_child '');');
1147 l(' --hk_debugt(LL_MDU_TBL);');
1148 l(' ');
1149 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1150 l(' hz_utility_v2pub.debug ');
1151 l(' (p_message => '' Calling fix_child. This call will try to derive missing lower level compoents.'',');
1152 l(' p_prefix => l_debug_prefix,');
1153 l(' p_msg_level => fnd_log.level_statement,');
1154 l(' p_module_prefix => l_module_prefix,');
1155 l(' p_module => l_module');
1156 l(' );');
1157 l(' END IF; ');
1158 l(' ');
1159 l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MDU_TBL) = FALSE THEN');
1160 l(' --hk_debugl(''LL_MDU Table after Fix_child '');');
1161 l(' --hk_debugt(LL_MDU_TBL);');
1162 l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MDU_TBL,L_MDU_TBL);');
1163 l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.get_usage_val_status '');');
1164 l(' --hk_debugt(LL_MDU_TBL);');
1165 l(' END IF;');
1166 l(' ');
1167 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1168 l(' hz_utility_v2pub.debug ');
1169 l(' (p_message => '' Return status after fix_child ''||x_status,');
1170 l(' p_prefix => l_debug_prefix,');
1171 l(' p_msg_level => fnd_log.level_statement,');
1172 l(' p_module_prefix => l_module_prefix,');
1173 l(' p_module => l_module');
1174 l(' );');
1175 l(' END IF; ');
1176 l(' ');
1177 l(' END IF;');
1178 l(' ');
1179 l(' HZ_GNR_UTIL_PKG.fill_values(LL_MDU_TBL);');
1180 l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.fill_values '');');
1181 l(' --hk_debugt(LL_MDU_TBL);');
1182 l(' ');
1183 l(' IF x_status = FND_API.g_ret_sts_success THEN');
1184 l(' ');
1185 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1186 l(' hz_utility_v2pub.debug ');
1187 l(' (p_message => '' Calling create_gnr with map status S.'',');
1188 l(' p_prefix => l_debug_prefix,');
1189 l(' p_msg_level => fnd_log.level_statement,');
1190 l(' p_module_prefix => l_module_prefix,');
1191 l(' p_module => l_module');
1192 l(' );');
1193 l(' END IF; ');
1194 l(' ');
1195 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1196 l(' ''TAX'',''S'',l_loc_components_rec,''T'',LL_MDU_TBL,l_status);');
1197 l(' ELSE ');
1198 l(' ');
1199 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1200 l(' hz_utility_v2pub.debug ');
1201 l(' (p_message => '' Calling create_gnr with map status E.'',');
1202 l(' p_prefix => l_debug_prefix,');
1203 l(' p_msg_level => fnd_log.level_statement,');
1204 l(' p_module_prefix => l_module_prefix,');
1205 l(' p_module => l_module');
1206 l(' );');
1207 l(' END IF; ');
1208 l(' ');
1209 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1210 l(' ''TAX'',''E'',l_loc_components_rec,''T'',LL_MDU_TBL,l_status);');
1211 l(' END IF;');
1212 l(' ');
1213 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1214 l(' hz_utility_v2pub.debug ');
1215 l(' (p_message => ''End of validation for validateHrLoc.'',');
1216 l(' p_prefix => l_debug_prefix,');
1217 l(' p_msg_level => fnd_log.level_procedure,');
1218 l(' p_module_prefix => l_module_prefix,');
1219 l(' p_module => l_module');
1220 l(' );');
1221 l(' END IF; ');
1222 l(' ');
1223 l(' --hk_debugt(LL_MDU_TBL); ----- Code to display the output.');
1224 ---- end procedure
1225 procEnd(l_procName);
1226 END validateHrBody;
1227 --------------------------------------
1228 /**
1229 * PRIVATE PROCEDURE validateBody
1230 *
1231 * DESCRIPTION
1232 * to generate body for srchGeo procedure
1233 *
1234 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1235 *
1236 * NOTES
1237 *
1238 * MODIFICATION HISTORY
1239 *
1240 *
1241 */
1242 --------------------------------------
1246 -- local variable declaration
1243 PROCEDURE validateBody (
1244 x_status OUT NOCOPY VARCHAR2) IS
1245
1247 l_procName varchar2(30);
1248 i number;
1249 j number;
1250 l_mdu_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
1251 l_open_cur varchar2(2000);
1252 l_fetch_cur varchar2(2000);
1253 l_usage_id number;
1254 l_usage_code varchar2(30);
1255 l_mdu_tbl_name varchar2(30);
1256
1257 BEGIN
1258 x_status := FND_API.g_ret_sts_success;
1259 l(' ');
1260 -- write the header comments, procedure name
1261 l_procName := 'validateForMap';
1262 procBegin(l_procName);
1263 li(' p_loc_components_rec IN HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE,');
1264 li(' x_map_dtls_tbl IN OUT NOCOPY HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE,');
1265 li(' X_CAUSE OUT NOCOPY VARCHAR2,');
1266 li(' X_STATUS OUT NOCOPY VARCHAR2) IS');
1267 l(' ');
1268 l(' ');
1269 li(' TYPE getGeo IS REF CURSOR;');
1270 li(' c_getGeo getGeo;');
1271 li(' c_getGeo1 getGeo;');
1272 l(' ');
1273 li(' l_multiple_parent_flag VARCHAR2(1);');
1274 li(' l_sql VARCHAR2(9000);');
1275 li(' l_status VARCHAR2(1);');
1276 li(' l_geography_type VARCHAR2(30);');
1277 li(' l_geography_id NUMBER;');
1278 li(' L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1279 l(' ');
1280 l(' l_module_prefix CONSTANT VARCHAR2(30) := ''HZ:ARHGNRGB:'||g_pkgName||''';');
1281 l(' l_module CONSTANT VARCHAR2(30) := ''ADDRESS_VALIDATION'';');
1282 l(' l_debug_prefix VARCHAR2(30);');
1283 l(' ');
1284
1285 j := 0;
1286 IF G_MAP_DTLS_TBL.COUNT > 0 THEN
1287 j := G_MAP_DTLS_TBL.FIRST;
1288 LOOP
1289 li(' l_value'||j||' VARCHAR2(360);');
1290 li(' l_type'||j||' VARCHAR2(30);');
1291
1292 EXIT WHEN j = G_MAP_DTLS_TBL.LAST;
1293 j := G_MAP_DTLS_TBL.NEXT(j);
1294 END LOOP;
1295 END IF;
1296 l(' ');
1297 -- procedure body
1298 li('BEGIN ');
1299 l(' ');
1300 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1301 l(' hz_utility_v2pub.debug ');
1302 l(' (p_message => ''Begin of Validate for Map'',');
1303 l(' p_prefix => l_debug_prefix,');
1304 l(' p_msg_level => fnd_log.level_procedure,');
1305 l(' p_module_prefix => l_module_prefix,');
1306 l(' p_module => l_module');
1307 l(' );');
1308 l(' END IF; ');
1309 l(' ');
1310 l(' --hk_debugl(''Validate for Map Start'');');
1311 li(' -- defaulting the sucess status');
1312 li(' x_status := FND_API.g_ret_sts_success;');
1313 l(' ');
1314 l(' L_MAP_DTLS_TBL := X_MAP_DTLS_TBL;');
1315 l(' --hk_debugl(''The Map table passed in with loc comp values'');');
1316 l(' --hk_debugt(L_MAP_DTLS_TBL);');
1317 l(' ');
1318 l(' IF L_MAP_DTLS_TBL.COUNT = 1 THEN');
1319 l(' -- This means country is the only required mapped column for validation.');
1320 l(' -- and country is already populated in the L_MAP_DTLS_TBL in the initialization section of this package.');
1321 l(' x_status := FND_API.g_ret_sts_success;');
1322 l(' RETURN;');
1323 l(' END IF;');
1324 l(' ');
1325 l(' IF HZ_GNR_UTIL_PKG.getLocCompCount(L_MAP_DTLS_TBL) = 0 THEN');
1326 l(' --hk_debugl(''HZ_GNR_UTIL_PKG.getLocCompCount = 0'');');
1327 l(' ');
1328 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1329 l(' hz_utility_v2pub.debug ');
1330 l(' (p_message => '' HZ_GNR_UTIL_PKG.getLocCompCount = 0 '',');
1331 l(' p_prefix => l_debug_prefix,');
1332 l(' p_msg_level => fnd_log.level_statement,');
1333 l(' p_module_prefix => l_module_prefix,');
1334 l(' p_module => l_module');
1335 l(' );');
1336 l(' END IF; ');
1337 l(' ');
1338 l(' --No other location component value other than country is passed. ');
1339 l(' --Following call will try to derive missing lower level compoents ');
1340 l(' IF HZ_GNR_UTIL_PKG.fix_child(L_MAP_DTLS_TBL) = FALSE THEN');
1341 l(' x_cause := ''MISSING_CHILD'';');
1342 l(' ');
1343 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1344 l(' hz_utility_v2pub.debug ');
1345 l(' (p_message => '' x_cause : ''||x_cause,');
1346 l(' p_prefix => l_debug_prefix,');
1347 l(' p_msg_level => fnd_log.level_statement,');
1348 l(' p_module_prefix => l_module_prefix,');
1349 l(' p_module => l_module');
1350 l(' );');
1351 l(' END IF; ');
1352 l(' ');
1353 -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1354 l(' x_status := FND_API.G_RET_STS_ERROR;');
1355 l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1356 l(' RETURN;');
1357 l(' ELSE');
1358 l(' ');
1359 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1363 l(' p_msg_level => fnd_log.level_statement,');
1360 l(' hz_utility_v2pub.debug ');
1361 l(' (p_message => '' Derived the missing lower level compoents '',');
1362 l(' p_prefix => l_debug_prefix,');
1364 l(' p_module_prefix => l_module_prefix,');
1365 l(' p_module => l_module');
1366 l(' );');
1367 l(' END IF; ');
1368 l(' ');
1369 -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1370 l(' x_status := FND_API.G_RET_STS_SUCCESS;');
1371 l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1372 l(' RETURN;');
1373 l(' END IF;');
1374 l(' END IF;');
1375 l(' ');
1376 l_open_cur := NULL;
1377 l_fetch_cur:= NULL;
1378
1379 j := 0;
1380 IF G_MAP_DTLS_TBL.COUNT > 0 THEN
1381 j := G_MAP_DTLS_TBL.FIRST;
1382 LOOP
1383 IF j>1 THEN
1384 l(' l_value'||j||' := NVL(L_MAP_DTLS_TBL('||j||').LOC_COMPVAL,''X'') ;');
1385 l(' IF l_value'||j||' = ''X'' THEN');
1386 l(' l_type'||j||' := ''X'';');
1387 l(' ELSE');
1388 l(' l_type'||j||' := L_MAP_DTLS_TBL('||j||').GEOGRAPHY_TYPE;');
1389 l(' -- store the geography_type of the lowest address component that has a value passed in');
1390 l(' l_geography_type := l_type'||j||';');
1391 l(' END IF;');
1392 l(' ');
1393 l_open_cur := l_open_cur||',l_type'||j||',l_value'||j;
1394 l_fetch_cur := l_fetch_cur||',L_MAP_DTLS_TBL('||j||').GEOGRAPHY_ID';
1395 END IF;
1396
1397 EXIT WHEN j = G_MAP_DTLS_TBL.LAST;
1398 j := G_MAP_DTLS_TBL.NEXT(j);
1399 END LOOP;
1400 END IF;
1401 l(' ');
1402 l_open_cur := l_open_cur||',l_geography_type;';
1403 l_fetch_cur := l_fetch_cur||';';
1404
1405 l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MAP_DTLS_TBL,x_status);');
1406 l(' --hk_debugl(''The SQL query'');');
1407 l(' --hk_debugl(l_sql);');
1408 l(' ');
1409 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1410 l(' hz_utility_v2pub.debug ');
1411 l(' (p_message => '' The SQL query : ''||l_sql,');
1412 l(' p_prefix => l_debug_prefix,');
1413 l(' p_msg_level => fnd_log.level_statement,');
1414 l(' p_module_prefix => l_module_prefix,');
1415 l(' p_module => l_module');
1416 l(' );');
1417 l(' END IF; ');
1418 l(' ');
1419 l(' OPEN c_getGeo FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
1420 l(' '||l_open_cur);
1421 l(' --hk_debugl(''Before the first fetch'');');
1422 l(' ');
1423 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1424 l(' hz_utility_v2pub.debug ');
1425 l(' (p_message => '' Before the first fetch'',');
1426 l(' p_prefix => l_debug_prefix,');
1427 l(' p_msg_level => fnd_log.level_statement,');
1428 l(' p_module_prefix => l_module_prefix,');
1429 l(' p_module => l_module');
1430 l(' );');
1431 l(' END IF; ');
1432 l(' ');
1433 l(' --hk_debugt(L_MAP_DTLS_TBL);');
1434 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
1435 l(' '||l_fetch_cur);
1436 l(' IF c_getGeo%NOTFOUND THEN ');
1437 l(' ');
1438 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1439 l(' hz_utility_v2pub.debug ');
1440 l(' (p_message => '' NOT FOUND of the first fetch'',');
1441 l(' p_prefix => l_debug_prefix,');
1442 l(' p_msg_level => fnd_log.level_statement,');
1443 l(' p_module_prefix => l_module_prefix,');
1444 l(' p_module => l_module');
1445 l(' );');
1446 l(' END IF; ');
1447 l(' ');
1448 l(' --hk_debugl(''NOT FOUND of the first fetch'');');
1449 l(' --hk_debugt(L_MAP_DTLS_TBL);');
1450 l(' x_cause := ''NO_MATCH'';');
1451 l(' HZ_GNR_UTIL_PKG.fix_no_match(L_MAP_DTLS_TBL,x_status);');
1452 l(' --hk_debugl(''Map_loc table after Fix'');');
1453 l(' --hk_debugt(L_MAP_DTLS_TBL);');
1454 l(' x_status := FND_API.G_RET_STS_ERROR;');
1455 l(' ELSE ');
1456 l(' --Fetching once more to see where there are multiple records');
1457 l(' ');
1458 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1459 l(' hz_utility_v2pub.debug ');
1460 l(' (p_message => '' Fetching once more to see where there are multiple records '',');
1461 l(' p_prefix => l_debug_prefix,');
1462 l(' p_msg_level => fnd_log.level_statement,');
1463 l(' p_module_prefix => l_module_prefix,');
1464 l(' p_module => l_module');
1465 l(' );');
1466 l(' END IF; ');
1467 l(' ');
1468 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
1469 l(' '||l_fetch_cur);
1473 l(' -- If it returns a record, we are able to derive a unique record for identifier type as NAME');
1470 l(' IF c_getGeo%FOUND THEN -- not able to identify a unique record');
1471 l(' ');
1472 l(' -- Get the query again with identifier type as NAME if multiple match found');
1474 l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MAP_DTLS_TBL,x_status);');
1475 l(' OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
1476 l(' '||l_open_cur);
1477 l(' ');
1478 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1479 l(' hz_utility_v2pub.debug ');
1480 l(' (p_message => ''Before the fetch of the query with identifier type as NAME after multiple match found'',');
1481 l(' p_prefix => l_debug_prefix,');
1482 l(' p_msg_level => fnd_log.level_statement,');
1483 l(' p_module_prefix => l_module_prefix,');
1484 l(' p_module => l_module');
1485 l(' );');
1486 l(' END IF; ');
1487 l(' ');
1488 l(' --hk_debugt(L_MAP_DTLS_TBL);');
1489 l(' FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
1490 l(' '||l_fetch_cur);
1491 l(' IF c_getGeo1%FOUND THEN ');
1492 ------
1493 l(' -- check if there is another row with same STANDARD_NAME, in that case it is error case ');
1494 l(' FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID ');
1495 l(' '||l_fetch_cur);
1496 l(' IF c_getGeo1%NOTFOUND THEN -- success (only 1 rec with same primary name exists)' );
1497 l(' ');
1498 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1499 l(' hz_utility_v2pub.debug ');
1500 l(' (p_message => ''MAP-Able to found a unique record or a record with multiple parent flag = Y with identifier type as NAME'',');
1501 l(' p_prefix => l_debug_prefix,');
1502 l(' p_msg_level => fnd_log.level_statement,');
1503 l(' p_module_prefix => l_module_prefix,');
1504 l(' p_module => l_module');
1505 l(' );');
1506 l(' END IF; ');
1507 l(' ');
1508 l(' ELSE -- Not able to find a unique record with identifier type as NAME ');
1509 l(' ');
1510 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1511 l(' hz_utility_v2pub.debug ');
1512 l(' (p_message => ''MAP-Not able to find a record with with identifier type as NAME. ''|| ');
1513 l(' '' More than 1 rec exists with same STANDARD NAME'', ');
1514 l(' p_prefix => l_debug_prefix, ');
1515 l(' p_msg_level => fnd_log.level_statement, ');
1516 l(' p_module_prefix => l_module_prefix, ');
1517 l(' p_module => l_module ');
1518 l(' ); ');
1519 l(' END IF; ');
1520 l(' ');
1521 l(' x_cause := ''MULTIPLE_MATCH''; ');
1522 l(' x_status := FND_API.G_RET_STS_ERROR; ');
1523 l(' RETURN; ');
1524 l(' END IF; ');
1525 l(' ');
1526 -------
1527 l(' ELSE -- Not able to found a unique record with identifier type as NAME');
1528 l(' x_cause := ''MULTIPLE_MATCH'';');
1529 l(' ');
1530 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1531 l(' hz_utility_v2pub.debug ');
1532 l(' (p_message => '' Not able to find a record with with identifier type as NAME. x_cause : ''||x_cause,');
1533 l(' p_prefix => l_debug_prefix,');
1534 l(' p_msg_level => fnd_log.level_statement,');
1535 l(' p_module_prefix => l_module_prefix,');
1536 l(' p_module => l_module');
1537 l(' );');
1538 l(' END IF; ');
1539 l(' ');
1540 l(' x_status := FND_API.G_RET_STS_ERROR;');
1541 l(' RETURN;');
1542 l(' END IF; ');
1543 l(' CLOSE c_getGeo1;');
1544 l(' ');
1545 l(' ELSE -- a unique record or a record with multiple parent flag = Y is found');
1546 l(' ');
1547 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1548 l(' hz_utility_v2pub.debug ');
1549 l(' (p_message => '' A unique record or a record with multiple parent flag = Y is found '',');
1550 l(' p_prefix => l_debug_prefix,');
1551 l(' p_msg_level => fnd_log.level_statement,');
1552 l(' p_module_prefix => l_module_prefix,');
1553 l(' p_module => l_module');
1554 l(' );');
1555 l(' END IF; ');
1556 l(' ');
1557 l(' END IF;');
1558 l(' ');
1559 l(' IF l_multiple_parent_flag = ''Y'' THEN');
1560 l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,L_MAP_DTLS_TBL) = TRUE THEN');
1561 l(' NULL; -- a unique record is found');
1562 l(' ELSE -- Multiple parent case not able to find a unique record ');
1563 l(' ');
1567 l(' p_prefix => l_debug_prefix,');
1564 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1565 l(' hz_utility_v2pub.debug ');
1566 l(' (p_message => '' Multiple parent case not able to find a unique record'',');
1568 l(' p_msg_level => fnd_log.level_statement,');
1569 l(' p_module_prefix => l_module_prefix,');
1570 l(' p_module => l_module');
1571 l(' );');
1572 l(' END IF; ');
1573 l(' ');
1574 l(' x_cause := ''MULTIPLE_PARENT'';');
1575 l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1576 l(' x_status := FND_API.G_RET_STS_ERROR;');
1577 l(' ');
1578 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1579 l(' hz_utility_v2pub.debug ');
1580 l(' (p_message => '' x_cause : ''||x_cause,');
1581 l(' p_prefix => l_debug_prefix,');
1582 l(' p_msg_level => fnd_log.level_statement,');
1583 l(' p_module_prefix => l_module_prefix,');
1584 l(' p_module => l_module');
1585 l(' );');
1586 l(' END IF; ');
1587 l(' ');
1588 l(' RETURN;');
1589 l(' END IF;');
1590 l(' ');
1591 l(' ELSE -- a unique record is found');
1592 l(' ');
1593 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1594 l(' hz_utility_v2pub.debug ');
1595 l(' (p_message => ''A unique record is found '',');
1596 l(' p_prefix => l_debug_prefix,');
1597 l(' p_msg_level => fnd_log.level_statement,');
1598 l(' p_module_prefix => l_module_prefix,');
1599 l(' p_module => l_module');
1600 l(' );');
1601 l(' END IF; ');
1602 l(' ');
1603 l(' END IF;');
1604 l(' ');
1605 l(' END IF;');
1606 l(' ');
1607 l(' CLOSE c_getGeo;');
1608 l(' ');
1609 l(' ');
1610 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1611 l(' hz_utility_v2pub.debug ');
1612 l(' (p_message => '' Calling fix_child. This call will try to derive missing lower level compoents.'',');
1613 l(' p_prefix => l_debug_prefix,');
1614 l(' p_msg_level => fnd_log.level_statement,');
1615 l(' p_module_prefix => l_module_prefix,');
1616 l(' p_module => l_module');
1617 l(' );');
1618 l(' END IF; ');
1619 l(' ');
1620 l(' --Following call will try to derive missing lower level compoents ');
1621 l(' IF HZ_GNR_UTIL_PKG.fix_child(L_MAP_DTLS_TBL) = FALSE THEN');
1622 l(' x_cause := ''MISSING_CHILD'';');
1623 l(' ');
1624 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1625 l(' hz_utility_v2pub.debug ');
1626 l(' (p_message => '' x_cause : ''||x_cause,');
1627 l(' p_prefix => l_debug_prefix,');
1628 l(' p_msg_level => fnd_log.level_statement,');
1629 l(' p_module_prefix => l_module_prefix,');
1630 l(' p_module => l_module');
1631 l(' );');
1632 l(' END IF; ');
1633 l(' ');
1634 -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1635 l(' x_status := FND_API.G_RET_STS_ERROR;');
1636 l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1637 l(' RETURN;');
1638 l(' END IF;');
1639 -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1640 l(' ');
1641 l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1642 l(' ');
1643 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1644 l(' hz_utility_v2pub.debug ');
1645 l(' (p_message => ''End of Validate for Map'',');
1646 l(' p_prefix => l_debug_prefix,');
1647 l(' p_msg_level => fnd_log.level_procedure,');
1648 l(' p_module_prefix => l_module_prefix,');
1649 l(' p_module => l_module');
1650 l(' );');
1651 l(' END IF; ');
1652 l(' ');
1653 ---- end procedure
1654 procEnd(l_procName);
1655
1656 i := 0;
1657 IF G_USAGE_TBL.COUNT > 0 THEN
1658 i := G_USAGE_TBL.FIRST;
1659 LOOP
1660 l_usage_id := G_USAGE_TBL(i).USAGE_ID;
1661 l_usage_code := G_USAGE_TBL(i).USAGE_CODE;
1662 l_mdu_tbl_name := 'G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID;
1663 -- name the procedure
1664 IF G_USAGE_TBL(i).USAGE_CODE = 'GEOGRAPHY' THEN
1665 l_procName := 'validateGeo';
1666 ELSIF G_USAGE_TBL(i).USAGE_CODE = 'TAX' THEN
1667 l_procName := 'validateTax';
1668 ELSE
1669 l_procName := 'validate'||G_USAGE_TBL(i).USAGE_ID;
1670 END IF;
1671
1672 l(' ');
1673 -- write the header comments, procedure name
1674 procBegin(l_procName);
1675
1676 -- write the parameters
1677 li(' P_LOCATION_ID IN NUMBER,');
1678 li(' P_COUNTRY IN VARCHAR2,');
1682 li(' P_CITY IN VARCHAR2,');
1679 li(' P_STATE IN VARCHAR2,');
1680 li(' P_PROVINCE IN VARCHAR2,');
1681 li(' P_COUNTY IN VARCHAR2,');
1683 li(' P_POSTAL_CODE IN VARCHAR2,');
1684 li(' P_POSTAL_PLUS4_CODE IN VARCHAR2,');
1685 li(' P_ATTRIBUTE1 IN VARCHAR2,');
1686 li(' P_ATTRIBUTE2 IN VARCHAR2,');
1687 li(' P_ATTRIBUTE3 IN VARCHAR2,');
1688 li(' P_ATTRIBUTE4 IN VARCHAR2,');
1689 li(' P_ATTRIBUTE5 IN VARCHAR2,');
1690 li(' P_ATTRIBUTE6 IN VARCHAR2,');
1691 li(' P_ATTRIBUTE7 IN VARCHAR2,');
1692 li(' P_ATTRIBUTE8 IN VARCHAR2,');
1693 li(' P_ATTRIBUTE9 IN VARCHAR2,');
1694 li(' P_ATTRIBUTE10 IN VARCHAR2,');
1695 li(' P_LOCK_FLAG IN VARCHAR2,');
1696 li(' X_CALL_MAP IN OUT NOCOPY VARCHAR2,');
1697 li(' P_CALLED_FROM IN VARCHAR2,');
1698 li(' P_ADDR_VAL_LEVEL IN VARCHAR2,');
1699 li(' X_ADDR_WARN_MSG OUT NOCOPY VARCHAR2,');
1700 li(' X_ADDR_VAL_STATUS OUT NOCOPY VARCHAR2,');
1701 li(' X_STATUS OUT NOCOPY VARCHAR2) IS');
1702 l(' ');
1703
1704 -- cursor or local variable declaration
1705 li(' l_loc_components_rec HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE;');
1706 l(' ');
1707 li(' TYPE getGeo IS REF CURSOR;');
1708 li(' c_getGeo getGeo;');
1709 li(' c_getGeo1 getGeo;');
1710 l(' ');
1711 li(' l_multiple_parent_flag VARCHAR2(1);');
1712 li(' l_sql VARCHAR2(9000);');
1713 li(' l_cause VARCHAR2(30);');
1714 li(' l_usage_code VARCHAR2(30);');
1715 li(' l_usage_id NUMBER;');
1716 li(' l_status VARCHAR2(1);');
1717 li(' l_get_addr_val VARCHAR2(1);');
1718 li(' l_geography_type VARCHAR2(30);');
1719 li(' l_geography_id NUMBER;');
1720 li(' L_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1721 li(' LL_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1722 li(' L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1723 l(' ');
1724 l(' l_module_prefix CONSTANT VARCHAR2(30) := ''HZ:ARHGNRGB:'||g_pkgName||''';');
1725 l(' l_module CONSTANT VARCHAR2(30) := ''ADDRESS_VALIDATION'';');
1726 l(' l_debug_prefix VARCHAR2(30) := p_location_id;');
1727 l(' ');
1728
1729 populate_mdu_tbl(G_USAGE_TBL(i).USAGE_ID, l_mdu_tbl);
1730 j := 0;
1731 IF L_MDU_TBL.COUNT > 0 THEN
1732 j := L_MDU_TBL.FIRST;
1733 LOOP
1734 li(' l_value'||j||' VARCHAR2(360);');
1735 li(' l_type'||j||' VARCHAR2(30);');
1736
1737 EXIT WHEN j = L_MDU_TBL.LAST;
1738 j := L_MDU_TBL.NEXT(j);
1739 END LOOP;
1740 END IF;
1741 l(' ');
1742
1743 -- procedure body
1744 li('BEGIN ');
1745 l(' ');
1746 li(' -- defaulting the sucess status');
1747 li(' x_status := FND_API.g_ret_sts_success;');
1748 l(' --hk_debugl(''Processing Location record with location_id :- ''||nvl(to_char(p_location_id),''NULL_LOCATION_ID''));');
1749 li(' l_loc_components_rec.COUNTRY := P_COUNTRY;');
1750 li(' l_loc_components_rec.STATE := P_STATE;');
1751 li(' l_loc_components_rec.PROVINCE := P_PROVINCE;');
1752 li(' l_loc_components_rec.COUNTY := P_COUNTY;');
1753 li(' l_loc_components_rec.CITY := P_CITY;');
1754 li(' l_loc_components_rec.POSTAL_CODE := P_POSTAL_CODE;');
1755 li(' l_loc_components_rec.POSTAL_PLUS4_CODE := P_POSTAL_PLUS4_CODE;');
1756 li(' l_loc_components_rec.ATTRIBUTE1 := P_ATTRIBUTE1;');
1757 li(' l_loc_components_rec.ATTRIBUTE2 := P_ATTRIBUTE2;');
1758 li(' l_loc_components_rec.ATTRIBUTE3 := P_ATTRIBUTE3;');
1759 li(' l_loc_components_rec.ATTRIBUTE4 := P_ATTRIBUTE4;');
1760 li(' l_loc_components_rec.ATTRIBUTE5 := P_ATTRIBUTE5;');
1761 li(' l_loc_components_rec.ATTRIBUTE6 := P_ATTRIBUTE6;');
1762 li(' l_loc_components_rec.ATTRIBUTE7 := P_ATTRIBUTE7;');
1763 li(' l_loc_components_rec.ATTRIBUTE8 := P_ATTRIBUTE8;');
1764 li(' l_loc_components_rec.ATTRIBUTE9 := P_ATTRIBUTE9;');
1765 li(' l_loc_components_rec.ATTRIBUTE10 := P_ATTRIBUTE10;');
1766
1767 l(' ');
1768 l(' L_USAGE_ID := '||l_usage_id||';');
1769 l(' L_USAGE_CODE := '''||l_usage_code||''';');
1770 l(' L_MDU_TBL := '||l_mdu_tbl_name||';');
1771 l(' L_MAP_DTLS_TBL := G_MAP_DTLS_TBL;');
1772 l(' l_get_addr_val := ''N'';');
1773 l(' ');
1774 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1775 l(' hz_utility_v2pub.debug ');
1776 l(' (p_message => ''Begin of validation for ''||L_USAGE_CODE,');
1780 l(' p_module => l_module');
1777 l(' p_prefix => l_debug_prefix,');
1778 l(' p_msg_level => fnd_log.level_procedure,');
1779 l(' p_module_prefix => l_module_prefix,');
1781 l(' );');
1782 l(' END IF; ');
1783 l(' ');
1784 l(' IF P_LOCATION_ID IS NOT NULL AND P_CALLED_FROM <> ''GNR'' THEN');
1785 l(' --hk_debugl(''Before check_GNR_For_Usage'');');
1786 l(' IF HZ_GNR_UTIL_PKG.check_GNR_For_Usage(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1787 l(' L_USAGE_CODE,L_MDU_TBL,x_status) = TRUE THEN');
1788 l(' --hk_debugl(''After check_GNR_For_Usage with status :- ''||x_status);');
1789 l(' ');
1790 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1791 l(' hz_utility_v2pub.debug ');
1792 l(' (p_message => ''There is already a procedded success record in GNR log table.'',');
1793 l(' p_prefix => l_debug_prefix,');
1794 l(' p_msg_level => fnd_log.level_statement,');
1795 l(' p_module_prefix => l_module_prefix,');
1796 l(' p_module => l_module');
1797 l(' );');
1798 l(' END IF; ');
1799 l(' ');
1800 l(' x_status := FND_API.g_ret_sts_success;');
1801 l(' X_ADDR_VAL_STATUS := x_status;');
1802 l(' RETURN;');
1803 l(' END IF;');
1804 l(' END IF;');
1805 l(' ');
1806 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1807 l(' hz_utility_v2pub.debug ');
1808 l(' (p_message => ''Not able to find an existing success record in GNR log table.'',');
1809 l(' p_prefix => l_debug_prefix,');
1810 l(' p_msg_level => fnd_log.level_statement,');
1811 l(' p_module_prefix => l_module_prefix,');
1812 l(' p_module => l_module');
1813 l(' );');
1814 l(' END IF; ');
1815 l(' ');
1816 l(' -- After the following call L_MAP_DTLS_TBL will have the components value populated.');
1817 l(' HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MAP_DTLS_TBL,x_status);');
1818 l(' ');
1819 l(' -- Below code will overwrite the LOC_COMPVAL for COUNTRY to COUNTRY_CODE');
1820 l(' -- This change is to update COUNTRY column in locations table with COUNTRY_CODE ');
1821 l(' -- even if the table has Country name in this column and the validation is success ');
1822 l(' L_MAP_DTLS_TBL(1).LOC_COMPVAL := G_MAP_REC.COUNTRY_CODE;');
1823 l(' ');
1824 l(' -- After the following call L_MDU_TBL will have the components value populated.');
1825 l(' HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MDU_TBL,x_status);');
1826 l(' ');
1827 l(' --hk_debugl('' value of X_CALL_MAP : ''||X_CALL_MAP);');
1828 l(' IF X_CALL_MAP = ''Y'' THEN');
1829 l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1830 l(' ');
1831 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1832 l(' hz_utility_v2pub.debug ');
1833 l(' (p_message => ''Before calling validate for Map '',');
1834 l(' p_prefix => l_debug_prefix,');
1835 l(' p_msg_level => fnd_log.level_statement,');
1836 l(' p_module_prefix => l_module_prefix,');
1837 l(' p_module => l_module');
1838 l(' );');
1839 l(' END IF; ');
1840 l(' ');
1841 l(' validateForMap(L_LOC_COMPONENTS_REC,LL_MAP_DTLS_TBL,l_cause,x_status);');
1842 l(' --hk_debugl(''Back from Validate for Map with status :- ''||x_status||''.. and case :''||l_cause);');
1843 l(' ');
1844 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1845 l(' hz_utility_v2pub.debug ');
1846 l(' (p_message => ''Back from Validate for Map with status : .''||x_status||''.. and case :''||l_cause,');
1847 l(' p_prefix => l_debug_prefix,');
1848 l(' p_msg_level => fnd_log.level_statement,');
1849 l(' p_module_prefix => l_module_prefix,');
1850 l(' p_module => l_module');
1851 l(' );');
1852 l(' END IF; ');
1853 l(' ');
1854 -- Check added on 19-APR-2006 (Part of fix for bug 5011366 ) by Nishant
1855 l(' -- This usage level check is required upfront because usage level validation will ignore ');
1856 l(' -- some of the passed in parameters for the complete mapping and may result in wrong status ');
1857 l(' IF (x_status = FND_API.g_ret_sts_error) THEN ');
1858 l(' ');
1859 l(' -- hk_debugl(''Trying to check if usage level validation is success even with map validation as error..''); ');
1860 l(' -- hk_debugl(''TABLE that is returned by Validate For Map''); ');
1861 l(' -- hk_debugt(LL_MAP_DTLS_TBL); ');
1862 l(' -- hk_debugl(''Usage Map Table With loc comp values''); ');
1863 l(' -- hk_debugt(L_MDU_TBL); ');
1864 l(' ');
1865 l(' IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_SUCCESS THEN ');
1866 l(' -- hk_debugl(''COMPLETE mapping is error but is sufficient for passed usage. So setting X_STATUS to success''); ');
1867 l(' x_status := FND_API.g_ret_sts_success; ');
1868 l(' ');
1869 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1870 l(' hz_utility_v2pub.debug ');
1871 l(' (p_message => ''COMPLETE mapping is error but is sufficient for passed usage. So setting X_STATUS to success'',');
1872 l(' p_prefix => l_debug_prefix,');
1873 l(' p_msg_level => fnd_log.level_statement,');
1874 l(' p_module_prefix => l_module_prefix,');
1875 l(' p_module => l_module');
1876 l(' );');
1877 l(' END IF; ');
1878 l(' ');
1879 l(' END IF; ');
1880 l(' END IF; ');
1881 l(' -------End of status check for usage level ----------+ ');
1882 l(' ');
1883 -- end of check addition (bug 5011366)
1884 l(' IF x_status = FND_API.g_ret_sts_success THEN');
1885 l(' --hk_debugt(LL_MAP_DTLS_TBL); ----- Code to display the output.');
1886 l(' -- Set the address validation status to success since x_statusis success ');
1887 l(' X_ADDR_VAL_STATUS := x_status;');
1888 l(' IF P_LOCATION_ID IS NOT NULL THEN');
1889 l(' ');
1890 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1891 l(' hz_utility_v2pub.debug ');
1892 l(' (p_message => '' Location id is not null. Call fill_values, create_gnr and Return back.'',');
1893 l(' p_prefix => l_debug_prefix,');
1894 l(' p_msg_level => fnd_log.level_statement,');
1895 l(' p_module_prefix => l_module_prefix,');
1896 l(' p_module => l_module');
1897 l(' );');
1898 l(' END IF; ');
1899 l(' ');
1900 l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1901 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1902 l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1903 l(' END IF;');
1904 l(' ');
1905 l(' X_CALL_MAP := ''N'';');
1906 l(' RETURN;');
1907 l(' ');
1908 l(' ELSE ');
1909 l(' ');
1910 l(' IF P_LOCATION_ID IS NOT NULL THEN');
1911 l(' --hk_debugl(''Table that is returned by Validate For Map'');');
1912 l(' --hk_debugt(LL_MAP_DTLS_TBL);');
1913 l(' --hk_debugl(''Usage Map Table With loc comp values'');');
1914 l(' --hk_debugt(L_MDU_TBL);');
1915 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');
1916 l(' ');
1917 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1918 l(' hz_utility_v2pub.debug ');
1919 l(' (p_message => '' No usage level validation is required. Call create_gnr with the map status'',');
1920 l(' p_prefix => l_debug_prefix,');
1921 l(' p_msg_level => fnd_log.level_statement,');
1922 l(' p_module_prefix => l_module_prefix,');
1923 l(' p_module => l_module');
1924 l(' );');
1925 l(' END IF; ');
1926 l(' ');
1927 l(' -- This means no usage level validation is required');
1928 l(' IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_ERROR THEN');
1929 l(' ');
1930 l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1931 l(' -- This below call is to derive the address validation status and set the message ');
1932 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);');
1933 l(' --hk_debugl(''Calling create_gnr With Map_status "E"'');');
1934 l(' ');
1935 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1936 l(' hz_utility_v2pub.debug ');
1937 l(' (p_message => '' Calling create_gnr with map status E.'',');
1938 l(' p_prefix => l_debug_prefix,');
1939 l(' p_msg_level => fnd_log.level_statement,');
1940 l(' p_module_prefix => l_module_prefix,');
1941 l(' p_module => l_module');
1942 l(' );');
1943 l(' END IF; ');
1944 l(' ');
1945 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1946 l(' L_USAGE_CODE,''E'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1947 l(' --hk_debugl(''Status after create_gnr : ''||l_status);');
1948 l(' ELSE ');
1949 l(' ');
1950 l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1951 l(' -- This below call is to derive the address validation status and set the message ');
1952 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);');
1953 l(' --hk_debugl(''Calling create_gnr With Map_status "S"'');');
1954 l(' ');
1955 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1956 l(' hz_utility_v2pub.debug ');
1957 l(' (p_message => '' Calling create_gnr with map status S.'',');
1958 l(' p_prefix => l_debug_prefix,');
1959 l(' p_msg_level => fnd_log.level_statement,');
1960 l(' p_module_prefix => l_module_prefix,');
1961 l(' p_module => l_module');
1962 l(' );');
1963 l(' END IF; ');
1964 l(' ');
1965 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1966 l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1967 l(' --hk_debugl(''Status after create_gnr : ''||l_status);');
1968 l(' x_status := FND_API.g_ret_sts_success;');
1969 l(' END IF;');
1970 l(' ');
1971 l(' X_CALL_MAP := ''N'';');
1972 l(' RETURN;');
1973 l(' ');
1974 l(' ELSE ');
1975 l(' NULL; -- do_usage_val has concluded that usage level validation has to go through.');
1976 l(' END IF;');
1977 l(' END IF;');
1978 l(' END IF;');
1979 l(' ');
1980 l(' l_get_addr_val := ''Y'';');
1981 l(' X_CALL_MAP := ''N'';');
1982 l(' ');
1983 l(' END IF;');
1984 l(' ');
1985 l(' IF L_MDU_TBL.COUNT = 1 THEN');
1986 l(' ');
1987 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1988 l(' hz_utility_v2pub.debug ');
1989 l(' (p_message => '' This means country is the only required mapped column for validation. Call create_gnr with map status S'',');
1990 l(' p_prefix => l_debug_prefix,');
1991 l(' p_msg_level => fnd_log.level_statement,');
1992 l(' p_module_prefix => l_module_prefix,');
1993 l(' p_module => l_module');
1994 l(' );');
1995 l(' END IF; ');
1996 l(' ');
1997 l(' -- This means country is the only required mapped column for validation.');
1998 l(' -- and country is already populated in the L_MDU_TBL in the initialization section of this package.');
1999 l(' --hk_debugt(L_MDU_TBL); ----- Code to display the output.');
2000 l(' --hk_debugl(''Calling create_gnr With Map_status "S"'');');
2001 l(' -- This below call is to derive the address validation status and set the message ');
2002 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);');
2003 l(' ');
2004 l(' IF P_LOCATION_ID IS NOT NULL THEN');
2005 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
2006 l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,L_MDU_TBL,l_status);');
2007 l(' END IF;');
2008 l(' ');
2009 l(' x_status := FND_API.g_ret_sts_success;');
2010 l(' RETURN;');
2011 l(' END IF;');
2012 l(' --hk_debugl(''L_MDU_TBL has count count more than 1'');');
2013 l(' ');
2014
2015 l_open_cur := NULL;
2016 l_fetch_cur:= NULL;
2017
2018 j := 0;
2019 IF G_MAP_DTLS_TBL.COUNT > 0 THEN
2020 j := G_MAP_DTLS_TBL.FIRST;
2021 LOOP
2022 IF j>1 THEN
2023 l_fetch_cur := l_fetch_cur||',LL_MAP_DTLS_TBL('||j||').GEOGRAPHY_ID';
2024 END IF;
2025 EXIT WHEN j = G_MAP_DTLS_TBL.LAST;
2026 j := G_MAP_DTLS_TBL.NEXT(j);
2027 END LOOP;
2028 END IF;
2029
2030 j := 0;
2031 IF L_MDU_TBL.COUNT > 0 THEN
2032 j := L_MDU_TBL.FIRST;
2033 LOOP
2034 IF j>1 THEN
2035 l(' l_value'||j||' := NVL(L_MDU_TBL('||j||').LOC_COMPVAL,''X'') ;');
2036 l(' IF l_value'||j||' = ''X'' THEN');
2037 l(' l_type'||j||' := ''X'';');
2038 l(' ELSE');
2039 l(' l_type'||j||' := L_MDU_TBL('||j||').GEOGRAPHY_TYPE;');
2040 l(' -- store the geography_type of the lowest address component that has a value passed in');
2041 l(' l_geography_type := l_type'||j||';');
2042 l(' END IF;');
2043 l(' ');
2044 l_open_cur := l_open_cur||',l_type'||j||',l_value'||j;
2045 END IF;
2046
2047 EXIT WHEN j = L_MDU_TBL.LAST;
2048 j := L_MDU_TBL.NEXT(j);
2049 END LOOP;
2050 END IF;
2051 l(' ');
2052 l_open_cur := l_open_cur||',l_geography_type;';
2053 l_fetch_cur := l_fetch_cur||';';
2054
2055 l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
2056 l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);');
2057 l(' --hk_debugl(''The SQL query'');');
2058 l(' --hk_debugl(l_sql);');
2059 l(' ');
2060 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2061 l(' hz_utility_v2pub.debug ');
2062 l(' (p_message => '' The SQL query : ''||l_sql,');
2063 l(' p_prefix => l_debug_prefix,');
2064 l(' p_msg_level => fnd_log.level_statement,');
2065 l(' p_module_prefix => l_module_prefix,');
2066 l(' p_module => l_module');
2067 l(' );');
2068 l(' END IF; ');
2069 l(' ');
2070 l(' OPEN c_getGeo FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
2071 l(' '||l_open_cur);
2072 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
2073 l(' '||l_fetch_cur);
2074 l(' IF c_getGeo%NOTFOUND THEN ');
2075 l(' --hk_debugl(''No Match found for the usage level search'');');
2076 l(' ');
2077 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2078 l(' hz_utility_v2pub.debug ');
2079 l(' (p_message => '' No Match found for the usage level search '',');
2080 l(' p_prefix => l_debug_prefix,');
2081 l(' p_msg_level => fnd_log.level_statement,');
2082 l(' p_module_prefix => l_module_prefix,');
2083 l(' p_module => l_module');
2084 l(' );');
2085 l(' END IF; ');
2086 l(' ');
2087 l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status);');
2088 l(' x_status := FND_API.G_RET_STS_ERROR;');
2089 l(' ELSE ');
2090 l(' --Fetching once more to see where there are multiple records');
2091 l(' ');
2092 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2093 l(' hz_utility_v2pub.debug ');
2094 l(' (p_message => '' Fetching once more to see where there are multiple records '',');
2095 l(' p_prefix => l_debug_prefix,');
2096 l(' p_msg_level => fnd_log.level_statement,');
2097 l(' p_module_prefix => l_module_prefix,');
2098 l(' p_module => l_module');
2099 l(' );');
2100 l(' END IF; ');
2101 l(' ');
2102 l(' FETCH c_getGeo INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
2103 l(' '||l_fetch_cur);
2104 l(' IF c_getGeo%FOUND THEN -- not able to identify a unique record');
2105 l(' ');
2106 l(' -- Get the query again with identifier type as NAME if multiple match found');
2107 l(' -- If it returns a record, we are able to derive a unique record for identifier type as NAME');
2108 l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);');
2109 l(' OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
2110 l(' '||l_open_cur);
2111 l(' ');
2112 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2113 l(' hz_utility_v2pub.debug ');
2114 l(' (p_message => ''Before the fetch of the query with identifier type as NAME after multiple match found'',');
2115 l(' p_prefix => l_debug_prefix,');
2116 l(' p_msg_level => fnd_log.level_statement,');
2117 l(' p_module_prefix => l_module_prefix,');
2121 l(' ');
2118 l(' p_module => l_module');
2119 l(' );');
2120 l(' END IF; ');
2122 l(' --hk_debugt(LL_MAP_DTLS_TBL);');
2123 l(' FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,LL_MAP_DTLS_TBL(1).GEOGRAPHY_ID');
2124 l(' '||l_fetch_cur);
2125 l(' IF c_getGeo1%FOUND THEN ');
2126 ---- Fix for Bug 5011366 (Nishant)
2127 l(' -- check if there is another row with same STANDARD_NAME, in that case it is error case ');
2128 l(' FETCH c_getGeo1 INTO l_geography_id,l_multiple_parent_flag,L_MAP_DTLS_TBL(1).GEOGRAPHY_ID ');
2129 l(' '||l_fetch_cur);
2130 l(' IF c_getGeo1%NOTFOUND THEN -- success (only 1 rec with same primary name exists)' );
2131 l(' ');
2132 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2133 l(' hz_utility_v2pub.debug ');
2134 l(' (p_message => ''GEO-Able to find a unique record or a record with multiple parent flag = Y with identifier type as NAME'',');
2135 l(' p_prefix => l_debug_prefix,');
2136 l(' p_msg_level => fnd_log.level_statement,');
2137 l(' p_module_prefix => l_module_prefix,');
2138 l(' p_module => l_module');
2139 l(' );');
2140 l(' END IF; ');
2141 l(' ');
2142 l(' ELSE -- Not able to find a unique record with identifier type as NAME ');
2143 l(' ');
2144 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2145 l(' hz_utility_v2pub.debug ');
2146 l(' (p_message => ''GEO-Not able to find a record with with identifier type as NAME. ''|| ');
2147 l(' '' More than 1 rec exists with same STANDARD NAME'', ');
2148 l(' p_prefix => l_debug_prefix, ');
2149 l(' p_msg_level => fnd_log.level_statement, ');
2150 l(' p_module_prefix => l_module_prefix, ');
2151 l(' p_module => l_module ');
2152 l(' ); ');
2153 l(' END IF; ');
2154 l(' ');
2155 l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL; ');
2156 l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status); ');
2157 l(' x_status := FND_API.G_RET_STS_ERROR; ');
2158 l(' END IF; ');
2159 l(' ');
2160 ----- End of fix for Bug 5011366 (Nishant)
2161 l(' ELSE -- Not able to found a unique record with identifier type as NAME');
2162 l(' ');
2163 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2164 l(' hz_utility_v2pub.debug ');
2165 l(' (p_message => '' Not able to find a record with with identifier type as NAME. '',');
2166 l(' p_prefix => l_debug_prefix,');
2167 l(' p_msg_level => fnd_log.level_statement,');
2168 l(' p_module_prefix => l_module_prefix,');
2169 l(' p_module => l_module');
2170 l(' );');
2171 l(' END IF; ');
2172 l(' ');
2173 l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
2174 l(' x_status := FND_API.G_RET_STS_ERROR;');
2175 l(' END IF; ');
2176 l(' CLOSE c_getGeo1;');
2177 l(' ');
2178 l(' ELSE -- a unique record or a record with multiple parent flag = Y is found');
2179 l(' ');
2180 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2181 l(' hz_utility_v2pub.debug ');
2182 l(' (p_message => '' A unique record or a record with multiple parent flag = Y is found '',');
2183 l(' p_prefix => l_debug_prefix,');
2184 l(' p_msg_level => fnd_log.level_statement,');
2185 l(' p_module_prefix => l_module_prefix,');
2186 l(' p_module => l_module');
2187 l(' );');
2188 l(' END IF; ');
2189 l(' ');
2190 l(' END IF;');
2191 l(' ');
2192 l(' IF l_multiple_parent_flag = ''Y'' AND x_status <> FND_API.G_RET_STS_ERROR THEN');
2193 l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,LL_MAP_DTLS_TBL) = TRUE THEN');
2194 l(' NULL;');
2195 l(' ELSE -- Multiple parent case not able to find a unique record ');
2196 l(' ');
2197 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2198 l(' hz_utility_v2pub.debug ');
2199 l(' (p_message => '' Multiple parent case not able to find a unique record'',');
2200 l(' p_prefix => l_debug_prefix,');
2201 l(' p_msg_level => fnd_log.level_statement,');
2202 l(' p_module_prefix => l_module_prefix,');
2203 l(' p_module => l_module');
2207 l(' x_status := FND_API.G_RET_STS_ERROR;');
2204 l(' );');
2205 l(' END IF; ');
2206 l(' ');
2208 l(' END IF;');
2209 l(' ');
2210 l(' ELSE -- a unique record is found');
2211 l(' ');
2212 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2213 l(' hz_utility_v2pub.debug ');
2214 l(' (p_message => '' A unique record is found '',');
2215 l(' p_prefix => l_debug_prefix,');
2216 l(' p_msg_level => fnd_log.level_statement,');
2217 l(' p_module_prefix => l_module_prefix,');
2218 l(' p_module => l_module');
2219 l(' );');
2220 l(' END IF; ');
2221 l(' ');
2222 l(' END IF;');
2223 l(' END IF;');
2224 l(' CLOSE c_getGeo;');
2225 l(' ');
2226 l(' --hk_debugl(''Return STatus after first fetch : ''||x_status);');
2227 l(' --Following call will try to derive missing lower level compoents ');
2228 l(' ');
2229 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2230 l(' hz_utility_v2pub.debug ');
2231 l(' (p_message => '' Return Status after first fetch : ''||x_status,');
2232 l(' p_prefix => l_debug_prefix,');
2233 l(' p_msg_level => fnd_log.level_statement,');
2234 l(' p_module_prefix => l_module_prefix,');
2235 l(' p_module => l_module');
2236 l(' );');
2237 l(' END IF; ');
2238 l(' ');
2239 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2240 l(' hz_utility_v2pub.debug ');
2241 l(' (p_message => '' Calling fix_child. This call will try to derive missing lower level compoents.'',');
2242 l(' p_prefix => l_debug_prefix,');
2243 l(' p_msg_level => fnd_log.level_statement,');
2244 l(' p_module_prefix => l_module_prefix,');
2245 l(' p_module => l_module');
2246 l(' );');
2247 l(' END IF; ');
2248 l(' ');
2249 l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MAP_DTLS_TBL) = FALSE THEN');
2250 l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL);');
2251 l(' END IF;');
2252 l(' ');
2253 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2254 l(' hz_utility_v2pub.debug ');
2255 l(' (p_message => '' Return status after fix_child ''||x_status,');
2256 l(' p_prefix => l_debug_prefix,');
2257 l(' p_msg_level => fnd_log.level_statement,');
2258 l(' p_module_prefix => l_module_prefix,');
2259 l(' p_module => l_module');
2260 l(' );');
2261 l(' END IF; ');
2262 l(' ');
2263 l(' --hk_debugl(''LL_MAP_DTLS_TBL before fill_values'');');
2264 l(' --hk_debugt(LL_MAP_DTLS_TBL);');
2265 l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
2266 l(' --hk_debugl(''LL_MAP_DTLS_TBL after fill_values'');');
2267 l(' --hk_debugt(LL_MAP_DTLS_TBL);');
2268 l(' ');
2269 l(' IF x_status = FND_API.g_ret_sts_success THEN');
2270 l(' -- We need to call the getAddrValStatus only once. All other cases we are looking into x_call_map ');
2271 l(' -- In some case the below code will execute with the x_call_map as N ');
2272 l(' IF l_get_addr_val = ''Y'' THEN');
2273 l(' -- This below call is to derive the address validation status and set the message ');
2274 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);');
2275 l(' END IF;');
2276 l(' ');
2277 l(' IF P_LOCATION_ID IS NOT NULL THEN');
2278 l(' ');
2279 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2280 l(' hz_utility_v2pub.debug ');
2281 l(' (p_message => '' Calling create_gnr with map status S.'',');
2282 l(' p_prefix => l_debug_prefix,');
2283 l(' p_msg_level => fnd_log.level_statement,');
2284 l(' p_module_prefix => l_module_prefix,');
2285 l(' p_module => l_module');
2286 l(' );');
2287 l(' END IF; ');
2288 l(' ');
2289 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
2290 l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
2291 l(' --hk_debugl(''Prceossed GNR With Status : S and returned with Status : ''||l_status);');
2292 l(' END IF;');
2293 l(' ELSE ');
2294 l(' -- We need to call the getAddrValStatus only once. All other cases we are looking into x_call_map ');
2295 l(' -- In some case the below code will execute with the x_call_map as N ');
2296 l(' IF l_get_addr_val = ''Y'' THEN');
2297 l(' -- This below call is to derive the address validation status and set the message ');
2298 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);');
2299 l(' END IF;');
2300 l(' ');
2301 l(' IF P_LOCATION_ID IS NOT NULL THEN');
2302 l(' ');
2303 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2304 l(' hz_utility_v2pub.debug ');
2305 l(' (p_message => '' Calling create_gnr with map status E.'',');
2306 l(' p_prefix => l_debug_prefix,');
2307 l(' p_msg_level => fnd_log.level_statement,');
2308 l(' p_module_prefix => l_module_prefix,');
2309 l(' p_module => l_module');
2310 l(' );');
2311 l(' END IF; ');
2312 l(' ');
2313 l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
2314 l(' L_USAGE_CODE,''E'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
2315 l(' --hk_debugl(''Prceossed GNR With Status : E and returned with Status : ''||l_status);');
2316 l(' END IF;');
2317 l(' END IF;');
2318 l(' ');
2319 l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2320 l(' hz_utility_v2pub.debug ');
2321 l(' (p_message => ''End of validation for ''||L_USAGE_CODE,');
2322 l(' p_prefix => l_debug_prefix,');
2323 l(' p_msg_level => fnd_log.level_procedure,');
2324 l(' p_module_prefix => l_module_prefix,');
2325 l(' p_module => l_module');
2326 l(' );');
2327 l(' END IF; ');
2328 l(' ');
2329 l(' --hk_debugt(LL_MAP_DTLS_TBL); ----- Code to display the output.');
2330 ---- end procedure
2331 procEnd(l_procName);
2332 EXIT WHEN i = G_USAGE_TBL.LAST;
2333 i := G_USAGE_TBL.NEXT(i);
2334 END LOOP;
2335 END IF;
2336
2337 END validateBody;
2338 --------------------------------------
2339 PROCEDURE genSpec(
2340 x_status OUT NOCOPY VARCHAR2) IS
2341 l_count number;
2342 l_procName varchar2(10);
2343 l_tmp varchar2(4);
2344
2345 BEGIN
2346 -- flow
2347 -- 1. create the package header
2348 -- 2. generate spec for srchGeo()
2349 -- 3. create the package tail
2350 --
2351
2352 -- initializing the retun value
2353 x_status := FND_API.G_RET_STS_SUCCESS;
2354
2355 genPkgSpecHdr(g_pkgName);
2356 -- known caveat: for srchGeoSpec - the procedure comments do not have all the
2357 -- input variables.
2358 IF g_map_rec.LOC_TBL_NAME = 'HR_LOCATIONS_ALL' THEN
2359 validateHrSpec();
2360 END IF;
2361 IF g_map_rec.LOC_TBL_NAME = 'HZ_LOCATIONS' THEN
2362 get_usage_API_Spec;
2363 validateSpec();
2364 END IF;
2365 genPkgSpecTail(g_pkgName);
2366
2367
2368 END genSpec;
2369 --------------------------------------
2370 PROCEDURE genBody(
2371 x_status OUT NOCOPY VARCHAR2) IS
2372
2373 l_count number;
2374 i number;
2375 l_procName varchar2(10);
2376 l_tmp varchar2(4);
2377 l_level number;
2378
2379 BEGIN
2380 /* flow
2381 1. Generate the package header
2382 2. validateBody()
2383 3. generate the package tail
2384 */
2385
2386 -- initializing the retun value
2387 x_status := FND_API.G_RET_STS_SUCCESS;
2388
2389 -- package header
2390 genPkgBdyHdr(g_pkgName);
2391
2392 ---- writing the global variables
2393 li('--------------------------------------');
2394 li(' -- declaration of private global varibles');
2395 li(' --------------------------------------');
2396 l(' ');
2397 li(' g_debug_count NUMBER := 0;');
2398 li(' g_country_geo_id NUMBER;');
2399 li(' G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;');
2400 li(' G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
2401 li(' G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;');
2402 li(' G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;');
2403 -- create global variables with mapping details per usage
2404 i:=0;
2405 IF G_USAGE_TBL.COUNT > 0 THEN
2406 i := G_USAGE_TBL.FIRST;
2407 LOOP
2408 li(' G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID||' HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
2409 EXIT WHEN i = G_USAGE_TBL.LAST;
2413 l(' ');
2410 i := G_USAGE_TBL.NEXT(i);
2411 END LOOP;
2412 END IF;
2414 li(' --------------------------------------');
2415 li(' -- declaration of private procedures and functions');
2416 li(' --------------------------------------');
2417 l(' ');
2418 li(' --------------------------------------');
2419 li(' -- private procedures and functions');
2420 li(' --------------------------------------');
2421 l(' ');
2422
2423 IF g_map_rec.LOC_TBL_NAME = 'HZ_LOCATIONS' THEN
2424 get_usage_API_Body;
2425 validateBody(x_status);
2426 END IF;
2427 IF g_map_rec.LOC_TBL_NAME = 'HR_LOCATIONS_ALL' THEN
2428 validateHrBody(x_status);
2429 END IF;
2430
2431 genPkgBdyInit(x_status);
2432 -- write the pkg end
2433 genPkgBdyTail(g_pkgName);
2434 END genBody;
2435
2436 --------------------------------------
2437 -- procedures and functions
2438 --------------------------------------
2439 --------------------------------------
2440 /**
2441 * PROCEDURE genPkg
2442 *
2443 * DESCRIPTION
2444 * This private procedure is used to generate map specific package with
2445 * GNR search procedures
2446 *
2447 *
2448 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2449 *
2450 * ARGUMENTS
2451 * IN:
2452 *
2453 * p_map_rec Mapping Record
2454 * p_map_dtls_tbl table of records of map details
2455 * p_usage_tbl Table of records of Usages
2456 * p_usage_dtls_tbl Table of records of all usages and their details populated for usage after other.
2457 *
2458 * OUT:
2459 *
2460 * x_pkgName generated package name
2461 * x_status indicates if the genPkg was sucessfull or not.
2462 *
2463 * NOTES
2464 *
2465 *
2466 * MODIFICATION HISTORY
2467 *
2468 *
2469 */
2470 --------------------------------------
2471 procedure genPkg(
2472 p_map_rec IN HZ_GNR_UTIL_PKG.map_rec_type,
2473 p_map_dtls_tbl IN HZ_GNR_UTIL_PKG.maploc_rec_tbl_type,
2474 p_usage_tbl IN HZ_GNR_UTIL_PKG.usage_tbl_type,
2475 p_usage_dtls_tbl IN HZ_GNR_UTIL_PKG.usage_dtls_tbl_type,
2476 x_pkgName OUT NOCOPY VARCHAR2,
2477 x_status OUT NOCOPY VARCHAR2) IS
2478
2479 BEGIN
2480
2481 -- flow
2482 -- prepare the packge name
2483 -- genSpec()
2484 -- genBody()
2485
2486 -- Hari 2 Lines
2487 g_type := 'S';
2488 -- initializing the retun value
2489 x_status := FND_API.G_RET_STS_SUCCESS;
2490
2491 -- prepare the packge name
2492 x_pkgName := 'HZ_GNR_MAP'||p_map_rec.map_id;
2493 g_map_rec := p_map_rec;
2494 g_map_dtls_tbl := p_map_dtls_tbl;
2495 g_usage_tbl := p_usage_tbl;
2496 g_usage_dtls_tbl := p_usage_dtls_tbl;
2497 g_pkgName := x_pkgName;
2498
2499 genSpec(x_status);
2500 IF x_status <> FND_API.G_RET_STS_SUCCESS THEN
2501 --dbms_output.put_line('genSpec in genPkg'||sqlerrm);
2502 RAISE FND_API.G_EXC_ERROR;
2503 END IF;
2504
2505 -- Hari 1 Line
2506 g_type := 'B';
2507 genBody(x_status);
2508 IF x_status <> FND_API.G_RET_STS_SUCCESS THEN
2509 --dbms_output.put_line('genBody in genPkg'||sqlerrm);
2510 RAISE FND_API.G_EXC_ERROR;
2511 END IF;
2512
2513 END genPkg;
2514 --------------------------------------
2515 procedure genPkg(
2516 p_map_id IN NUMBER,
2517 x_pkgName OUT NOCOPY VARCHAR2,
2518 x_status OUT NOCOPY VARCHAR2) IS
2519
2520 m number := 0;
2521 n number := 0;
2522
2523 l_map_rec HZ_GNR_UTIL_PKG.MAP_REC_TYPE;
2524 l_map_dtls_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
2525 l_usage_tbl HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;
2526 l_usage_dtls_tbl HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;
2527
2528 CURSOR c_map(p_map_id IN NUMBER) IS
2529 SELECT MAP_ID,COUNTRY_CODE,LOC_TBL_NAME,ADDRESS_STYLE
2530 FROM hz_geo_struct_map
2531 WHERE map_id = p_map_id;
2532
2533 CURSOR c_map_dtls(p_map_id IN NUMBER) IS
2534 SELECT MAP_ID,LOC_SEQ_NUM,LOC_COMPONENT,GEOGRAPHY_TYPE,GEO_ELEMENT_COL
2535 FROM hz_geo_struct_map_dtl
2536 WHERE map_id = p_map_id
2537 ORDER BY LOC_SEQ_NUM;
2538
2539 CURSOR c_usage(p_map_id IN NUMBER) IS
2540 SELECT MAP_ID,USAGE_ID,USAGE_CODE
2541 FROM hz_address_usages
2542 WHERE map_id = p_map_id
2543 AND status_flag = 'A'
2544 ORDER BY usage_id;
2545
2546 CURSOR c_usage_dtls(p_usage_id IN NUMBER) IS
2547 SELECT udtl.USAGE_ID,udtl.GEOGRAPHY_TYPE
2548 FROM hz_address_usage_dtls udtl
2549 WHERE USAGE_ID = p_usage_id;
2550
2551 l_map_exists varchar2(1);
2552 l_map_dtls_exists varchar2(1);
2553 l_usage_exists varchar2(1);
2554 l_usage_dtls_exists varchar2(1);
2555
2556 BEGIN
2557 -- initializing the retun value
2558 x_status := FND_API.G_RET_STS_SUCCESS;
2559 l_map_exists := 'N';
2563 l_map_rec.COUNTRY_CODE := l_c_map.COUNTRY_CODE;
2560 FOR l_c_map IN c_map(p_map_id) LOOP -- only one record will be fetched
2561 l_map_exists := 'Y';
2562 l_map_rec.MAP_ID := l_c_map.MAP_ID;
2564 l_map_rec.LOC_TBL_NAME := l_c_map.LOC_TBL_NAME;
2565 l_map_rec.ADDRESS_STYLE := l_c_map.ADDRESS_STYLE;
2566 l_map_dtls_exists := 'N';
2567 FOR l_c_map_dtls IN c_map_dtls(p_map_id) LOOP
2568 l_map_dtls_exists := 'Y';
2569 m := m+1;
2570 l_map_dtls_tbl(m).loc_seq_num := l_c_map_dtls.loc_seq_num;
2571 l_map_dtls_tbl(m).loc_component := l_c_map_dtls.loc_component;
2572 l_map_dtls_tbl(m).geography_type := l_c_map_dtls.geography_type;
2573 l_map_dtls_tbl(m).geo_element_col := l_c_map_dtls.geo_element_col;
2574 l_map_dtls_tbl(m).loc_compval := null;
2575 l_map_dtls_tbl(m).geography_id := null;
2576 END LOOP;
2577 IF l_map_dtls_exists = 'N' THEN
2578 x_status := FND_API.G_RET_STS_ERROR;
2579 RAISE FND_API.G_EXC_ERROR;
2580 END IF;
2581 m :=0;
2582 l_usage_exists := 'N';
2583 FOR l_c_usage in c_usage(p_map_id) LOOP
2584 l_usage_exists := 'Y';
2585 m := m+1;
2586 l_usage_tbl(m).USAGE_ID := l_c_usage.USAGE_ID;
2587 l_usage_tbl(m).MAP_ID := l_c_usage.MAP_ID;
2588 l_usage_tbl(m).USAGE_CODE := l_c_usage.USAGE_CODE;
2589 l_usage_dtls_exists := 'N';
2590 FOR l_c_usage_dtls IN c_usage_dtls(l_c_usage.usage_id) LOOP
2591 l_usage_dtls_exists := 'Y';
2592 n := n+1;
2593 l_usage_dtls_tbl(n).USAGE_ID := l_c_usage_dtls.USAGE_ID;
2594 l_usage_dtls_tbl(n).GEOGRAPHY_TYPE := l_c_usage_dtls.GEOGRAPHY_TYPE;
2595 END LOOP;
2596 IF l_usage_dtls_exists = 'N' THEN
2597 x_status := FND_API.G_RET_STS_ERROR;
2598 RAISE FND_API.G_EXC_ERROR;
2599 END IF;
2600 END LOOP;
2601 IF l_usage_exists = 'N' THEN
2602 x_status := FND_API.G_RET_STS_ERROR;
2603 RAISE FND_API.G_EXC_ERROR;
2604 END IF;
2605 END LOOP;
2606 IF l_map_exists = 'N' THEN
2607 x_status := FND_API.G_RET_STS_ERROR;
2608 RAISE FND_API.G_EXC_ERROR;
2609 END IF;
2610
2611 genpkg(l_map_rec,l_map_dtls_tbl,l_usage_tbl,l_usage_dtls_tbl,x_pkgname,x_status);
2612 END genPkg;
2613 END HZ_GNR_GEN_PKG;
2614