eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HZ_GNR_GEN_PKG dependencies on HZ_GNR_UTIL_PKG

Line 6: G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;

2: /*$Header: ARHGNRGB.pls 120.32.12010000.2 2009/02/19 09:49:55 amstephe ship $ */
3: --------------------------------------
4: -- declaration of private global varibles
5: --------------------------------------
6: G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;
7: G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
8: G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;
9: G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;
10: g_pkgName VARCHAR2(30);

Line 7: G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;

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:

Line 8: G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;

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) := ' ';

Line 9: G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;

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

Line 318: PROCEDURE populate_mdu_tbl(p_usage_id in number,x_mdu_tbl OUT NOCOPY HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE) IS

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

Line 379: l_mdu_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;

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

Line 831: l_mdu_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;

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);

Line 870: li(' L_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');

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||''';');

Line 871: li(' LL_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');

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'';');

Line 872: li(' l_loc_components_rec HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE; -- not actually required here');

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;');

Line 928: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');

924: l(' -- and country is already populated in the L_MDU_TBL in the initialization section of this package.');
925: l(' --hk_debugt(L_MDU_TBL); ----- Code to display the output.');
926: l(' --hk_debugl(''Calling create_gnr With Map_status "S"'');');
927: l(' IF P_LOCATION_ID IS NOT NULL THEN');
928: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
929: l(' ''TAX'',''S'',l_loc_components_rec,''T'',L_MDU_TBL,l_status);');
930: l(' END IF;');
931: l(' x_status := FND_API.g_ret_sts_success;');
932: l(' RETURN;');

Line 984: l(' l_temp_postal_code := HZ_GNR_UTIL_PKG.postal_code_to_validate(P_COUNTRY_CODE => L_MDU_TBL(1).LOC_COMPVAL,');

980: l(' ');
981: l(' -- Fix for Bug 7240974 (Nishant) (ZIP+4 functionality) ');
982: l(' -- check if component is POSTAL_CODE, change from ZIP+4 format to ZIP for US based on setup');
983: l(' IF L_MDU_TBL('||j||').LOC_COMPONENT = ''POSTAL_CODE'' THEN ');
984: l(' l_temp_postal_code := HZ_GNR_UTIL_PKG.postal_code_to_validate(P_COUNTRY_CODE => L_MDU_TBL(1).LOC_COMPVAL,');
985: l(' P_POSTAL_CODE => L_MDU_TBL('||j||').LOC_COMPVAL); ');
986: l(' L_MDU_TBL('||j||').LOC_COMPVAL := l_temp_postal_code; ');
987: l(' l_value'||j||' := l_temp_postal_code; ');
988: l(' END IF ;');

Line 1004: l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MDU_TBL,L_MDU_TBL,x_status);');

1000: l(' ');
1001: l_open_cur := l_open_cur||',l_geography_type;';
1002: l_fetch_cur := l_fetch_cur||';';
1003: l(' LL_MDU_TBL := L_MDU_TBL;');
1004: l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MDU_TBL,L_MDU_TBL,x_status);');
1005: l(' --hk_debugl(''The SQL query'');');
1006: l(' --hk_debugl(l_sql);');
1007: l(' ');
1008: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1048: l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MDU_TBL,x_status);');

1044: l(' p_module => l_module');
1045: l(' );');
1046: l(' END IF; ');
1047: l(' ');
1048: l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MDU_TBL,x_status);');
1049: l(' x_status := FND_API.G_RET_STS_ERROR;');
1050: l(' ELSE ');
1051: l(' --Fetching once more to see where there are multiple records');
1052: l(' ');

Line 1071: l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MDU_TBL,L_MDU_TBL,x_status);');

1067:
1068:
1069: l(' -- Get the query again with identifier type as NAME if multiple match found');
1070: l(' -- If it returns a record, we are able to derive a unique record for identifier type as NAME');
1071: l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MDU_TBL,L_MDU_TBL,x_status);');
1072: l(' OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
1073: l(' '||l_open_cur);
1074: l(' ');
1075: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1133: l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,LL_MDU_TBL) = TRUE THEN');

1129: l(' END IF;');
1130: l(' ');
1131: l(' IF l_multiple_parent_flag = ''Y'' AND x_status <> FND_API.G_RET_STS_ERROR THEN');
1132: l(' --hk_debugl(''Multiple Parent Match found for the usage level search'');');
1133: l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,LL_MDU_TBL) = TRUE THEN');
1134: l(' --hk_debugl(''Sucessfully Fixed the Multiple Parent Case '');');
1135: l(' NULL;');
1136: l(' ELSE -- Multiple parent case not able to find a unique record ');
1137: l(' ');

Line 1183: l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MDU_TBL) = FALSE THEN');

1179: l(' p_module => l_module');
1180: l(' );');
1181: l(' END IF; ');
1182: l(' ');
1183: l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MDU_TBL) = FALSE THEN');
1184: l(' --hk_debugl(''LL_MDU Table after Fix_child '');');
1185: l(' --hk_debugt(LL_MDU_TBL);');
1186: l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MDU_TBL,L_MDU_TBL);');
1187: l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.get_usage_val_status '');');

Line 1186: l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MDU_TBL,L_MDU_TBL);');

1182: l(' ');
1183: l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MDU_TBL) = FALSE THEN');
1184: l(' --hk_debugl(''LL_MDU Table after Fix_child '');');
1185: l(' --hk_debugt(LL_MDU_TBL);');
1186: l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MDU_TBL,L_MDU_TBL);');
1187: l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.get_usage_val_status '');');
1188: l(' --hk_debugt(LL_MDU_TBL);');
1189: l(' END IF;');
1190: l(' ');

Line 1187: l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.get_usage_val_status '');');

1183: l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MDU_TBL) = FALSE THEN');
1184: l(' --hk_debugl(''LL_MDU Table after Fix_child '');');
1185: l(' --hk_debugt(LL_MDU_TBL);');
1186: l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MDU_TBL,L_MDU_TBL);');
1187: l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.get_usage_val_status '');');
1188: l(' --hk_debugt(LL_MDU_TBL);');
1189: l(' END IF;');
1190: l(' ');
1191: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1203: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MDU_TBL);');

1199: l(' END IF; ');
1200: l(' ');
1201: l(' END IF;');
1202: l(' ');
1203: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MDU_TBL);');
1204: l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.fill_values '');');
1205: l(' --hk_debugt(LL_MDU_TBL);');
1206: l(' ');
1207: l(' IF x_status = FND_API.g_ret_sts_success THEN');

Line 1204: l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.fill_values '');');

1200: l(' ');
1201: l(' END IF;');
1202: l(' ');
1203: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MDU_TBL);');
1204: l(' --hk_debugl(''LL_MDU Table after HZ_GNR_UTIL_PKG.fill_values '');');
1205: l(' --hk_debugt(LL_MDU_TBL);');
1206: l(' ');
1207: l(' IF x_status = FND_API.g_ret_sts_success THEN');
1208: l(' ');

Line 1219: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');

1215: l(' p_module => l_module');
1216: l(' );');
1217: l(' END IF; ');
1218: l(' ');
1219: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1220: l(' ''TAX'',''S'',l_loc_components_rec,''T'',LL_MDU_TBL,l_status);');
1221: l(' ELSE ');
1222: l(' ');
1223: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1233: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');

1229: l(' p_module => l_module');
1230: l(' );');
1231: l(' END IF; ');
1232: l(' ');
1233: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1234: l(' ''TAX'',''E'',l_loc_components_rec,''T'',LL_MDU_TBL,l_status);');
1235: l(' END IF;');
1236: l(' ');
1237: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1274: l_mdu_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;

1270: -- local variable declaration
1271: l_procName varchar2(30);
1272: i number;
1273: j number;
1274: l_mdu_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
1275: l_open_cur varchar2(2000);
1276: l_fetch_cur varchar2(2000);
1277: l_usage_id number;
1278: l_usage_code varchar2(30);

Line 1287: li(' p_loc_components_rec IN HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE,');

1283: l(' ');
1284: -- write the header comments, procedure name
1285: l_procName := 'validateForMap';
1286: procBegin(l_procName);
1287: li(' p_loc_components_rec IN HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE,');
1288: li(' x_map_dtls_tbl IN OUT NOCOPY HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE,');
1289: li(' X_CAUSE OUT NOCOPY VARCHAR2,');
1290: li(' X_STATUS OUT NOCOPY VARCHAR2) IS');
1291: l(' ');

Line 1288: li(' x_map_dtls_tbl IN OUT NOCOPY HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE,');

1284: -- write the header comments, procedure name
1285: l_procName := 'validateForMap';
1286: procBegin(l_procName);
1287: li(' p_loc_components_rec IN HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE,');
1288: li(' x_map_dtls_tbl IN OUT NOCOPY HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE,');
1289: li(' X_CAUSE OUT NOCOPY VARCHAR2,');
1290: li(' X_STATUS OUT NOCOPY VARCHAR2) IS');
1291: l(' ');
1292: l(' ');

Line 1302: li(' L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');

1298: li(' l_sql VARCHAR2(9000);');
1299: li(' l_status VARCHAR2(1);');
1300: li(' l_geography_type VARCHAR2(30);');
1301: li(' l_geography_id NUMBER;');
1302: li(' L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1303: l(' ');
1304: l(' l_module_prefix CONSTANT VARCHAR2(30) := ''HZ:ARHGNRGB:'||g_pkgName||''';');
1305: l(' l_module CONSTANT VARCHAR2(30) := ''ADDRESS_VALIDATION'';');
1306: l(' l_debug_prefix VARCHAR2(30);');

Line 1349: l(' IF HZ_GNR_UTIL_PKG.getLocCompCount(L_MAP_DTLS_TBL) = 0 THEN');

1345: l(' x_status := FND_API.g_ret_sts_success;');
1346: l(' RETURN;');
1347: l(' END IF;');
1348: l(' ');
1349: l(' IF HZ_GNR_UTIL_PKG.getLocCompCount(L_MAP_DTLS_TBL) = 0 THEN');
1350: l(' --hk_debugl(''HZ_GNR_UTIL_PKG.getLocCompCount = 0'');');
1351: l(' ');
1352: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1353: l(' hz_utility_v2pub.debug ');

Line 1350: l(' --hk_debugl(''HZ_GNR_UTIL_PKG.getLocCompCount = 0'');');

1346: l(' RETURN;');
1347: l(' END IF;');
1348: l(' ');
1349: l(' IF HZ_GNR_UTIL_PKG.getLocCompCount(L_MAP_DTLS_TBL) = 0 THEN');
1350: l(' --hk_debugl(''HZ_GNR_UTIL_PKG.getLocCompCount = 0'');');
1351: l(' ');
1352: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1353: l(' hz_utility_v2pub.debug ');
1354: l(' (p_message => '' HZ_GNR_UTIL_PKG.getLocCompCount = 0 '',');

Line 1354: l(' (p_message => '' HZ_GNR_UTIL_PKG.getLocCompCount = 0 '',');

1350: l(' --hk_debugl(''HZ_GNR_UTIL_PKG.getLocCompCount = 0'');');
1351: l(' ');
1352: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1353: l(' hz_utility_v2pub.debug ');
1354: l(' (p_message => '' HZ_GNR_UTIL_PKG.getLocCompCount = 0 '',');
1355: l(' p_prefix => l_debug_prefix,');
1356: l(' p_msg_level => fnd_log.level_statement,');
1357: l(' p_module_prefix => l_module_prefix,');
1358: l(' p_module => l_module');

Line 1364: l(' IF HZ_GNR_UTIL_PKG.fix_child(L_MAP_DTLS_TBL) = FALSE THEN');

1360: l(' END IF; ');
1361: l(' ');
1362: l(' --No other location component value other than country is passed. ');
1363: l(' --Following call will try to derive missing lower level compoents ');
1364: l(' IF HZ_GNR_UTIL_PKG.fix_child(L_MAP_DTLS_TBL) = FALSE THEN');
1365: l(' x_cause := ''MISSING_CHILD'';');
1366: l(' ');
1367: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1368: l(' hz_utility_v2pub.debug ');

Line 1377: -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');

1373: l(' p_module => l_module');
1374: l(' );');
1375: l(' END IF; ');
1376: l(' ');
1377: -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1378: l(' x_status := FND_API.G_RET_STS_ERROR;');
1379: l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1380: l(' RETURN;');
1381: l(' ELSE');

Line 1393: -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');

1389: l(' p_module => l_module');
1390: l(' );');
1391: l(' END IF; ');
1392: l(' ');
1393: -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1394: l(' x_status := FND_API.G_RET_STS_SUCCESS;');
1395: l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1396: l(' RETURN;');
1397: l(' END IF;');

Line 1429: l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MAP_DTLS_TBL,x_status);');

1425: l(' ');
1426: l_open_cur := l_open_cur||',l_geography_type;';
1427: l_fetch_cur := l_fetch_cur||';';
1428:
1429: l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MAP_DTLS_TBL,x_status);');
1430: l(' --hk_debugl(''The SQL query'');');
1431: l(' --hk_debugl(l_sql);');
1432: l(' ');
1433: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1475: l(' HZ_GNR_UTIL_PKG.fix_no_match(L_MAP_DTLS_TBL,x_status);');

1471: l(' ');
1472: l(' --hk_debugl(''NOT FOUND of the first fetch'');');
1473: l(' --hk_debugt(L_MAP_DTLS_TBL);');
1474: l(' x_cause := ''NO_MATCH'';');
1475: l(' HZ_GNR_UTIL_PKG.fix_no_match(L_MAP_DTLS_TBL,x_status);');
1476: l(' --hk_debugl(''Map_loc table after Fix'');');
1477: l(' --hk_debugt(L_MAP_DTLS_TBL);');
1478: l(' x_status := FND_API.G_RET_STS_ERROR;');
1479: l(' ELSE ');

Line 1498: l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MAP_DTLS_TBL,x_status);');

1494: l(' IF c_getGeo%FOUND THEN -- not able to identify a unique record');
1495: l(' ');
1496: l(' -- Get the query again with identifier type as NAME if multiple match found');
1497: l(' -- If it returns a record, we are able to derive a unique record for identifier type as NAME');
1498: l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MAP_DTLS_TBL,x_status);');
1499: l(' OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
1500: l(' '||l_open_cur);
1501: l(' ');
1502: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1584: l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,L_MAP_DTLS_TBL) = TRUE THEN');

1580: l(' ');
1581: l(' END IF;');
1582: l(' ');
1583: l(' IF l_multiple_parent_flag = ''Y'' THEN');
1584: l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,L_MAP_DTLS_TBL) = TRUE THEN');
1585: l(' NULL; -- a unique record is found');
1586: l(' ELSE -- Multiple parent case not able to find a unique record ');
1587: l(' ');
1588: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1645: l(' IF HZ_GNR_UTIL_PKG.fix_child(L_MAP_DTLS_TBL) = FALSE THEN');

1641: l(' );');
1642: l(' END IF; ');
1643: l(' ');
1644: l(' --Following call will try to derive missing lower level compoents ');
1645: l(' IF HZ_GNR_UTIL_PKG.fix_child(L_MAP_DTLS_TBL) = FALSE THEN');
1646: l(' x_cause := ''MISSING_CHILD'';');
1647: l(' ');
1648: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1649: l(' hz_utility_v2pub.debug ');

Line 1658: -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');

1654: l(' p_module => l_module');
1655: l(' );');
1656: l(' END IF; ');
1657: l(' ');
1658: -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1659: l(' x_status := FND_API.G_RET_STS_ERROR;');
1660: l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1661: l(' RETURN;');
1662: l(' END IF;');

Line 1663: -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');

1659: l(' x_status := FND_API.G_RET_STS_ERROR;');
1660: l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1661: l(' RETURN;');
1662: l(' END IF;');
1663: -- l(' HZ_GNR_UTIL_PKG.fill_values(L_MAP_DTLS_TBL);');
1664: l(' ');
1665: l(' X_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
1666: l(' ');
1667: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1729: li(' l_loc_components_rec HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE;');

1725: li(' X_STATUS OUT NOCOPY VARCHAR2) IS');
1726: l(' ');
1727:
1728: -- cursor or local variable declaration
1729: li(' l_loc_components_rec HZ_GNR_UTIL_PKG.LOC_COMPONENTS_REC_TYPE;');
1730: l(' ');
1731: li(' TYPE getGeo IS REF CURSOR;');
1732: li(' c_getGeo getGeo;');
1733: li(' c_getGeo1 getGeo;');

Line 1744: li(' L_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');

1740: li(' l_status VARCHAR2(1);');
1741: li(' l_get_addr_val VARCHAR2(1);');
1742: li(' l_geography_type VARCHAR2(30);');
1743: li(' l_geography_id NUMBER;');
1744: li(' L_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1745: li(' LL_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1746: li(' L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1747: l(' ');
1748: l(' l_module_prefix CONSTANT VARCHAR2(30) := ''HZ:ARHGNRGB:'||g_pkgName||''';');

Line 1745: li(' LL_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');

1741: li(' l_get_addr_val VARCHAR2(1);');
1742: li(' l_geography_type VARCHAR2(30);');
1743: li(' l_geography_id NUMBER;');
1744: li(' L_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1745: li(' LL_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1746: li(' L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1747: l(' ');
1748: l(' l_module_prefix CONSTANT VARCHAR2(30) := ''HZ:ARHGNRGB:'||g_pkgName||''';');
1749: l(' l_module CONSTANT VARCHAR2(30) := ''ADDRESS_VALIDATION'';');

Line 1746: li(' L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');

1742: li(' l_geography_type VARCHAR2(30);');
1743: li(' l_geography_id NUMBER;');
1744: li(' L_MDU_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1745: li(' LL_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1746: li(' L_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
1747: l(' ');
1748: l(' l_module_prefix CONSTANT VARCHAR2(30) := ''HZ:ARHGNRGB:'||g_pkgName||''';');
1749: l(' l_module CONSTANT VARCHAR2(30) := ''ADDRESS_VALIDATION'';');
1750: l(' l_debug_prefix VARCHAR2(30) := p_location_id;');

Line 1778: li(' l_loc_components_rec.POSTAL_CODE := HZ_GNR_UTIL_PKG.postal_code_to_validate(P_COUNTRY,P_POSTAL_CODE);');

1774: li(' l_loc_components_rec.STATE := P_STATE;');
1775: li(' l_loc_components_rec.PROVINCE := P_PROVINCE;');
1776: li(' l_loc_components_rec.COUNTY := P_COUNTY;');
1777: li(' l_loc_components_rec.CITY := P_CITY;');
1778: li(' l_loc_components_rec.POSTAL_CODE := HZ_GNR_UTIL_PKG.postal_code_to_validate(P_COUNTRY,P_POSTAL_CODE);');
1779: li(' l_loc_components_rec.POSTAL_PLUS4_CODE := P_POSTAL_PLUS4_CODE;');
1780: li(' l_loc_components_rec.ATTRIBUTE1 := P_ATTRIBUTE1;');
1781: li(' l_loc_components_rec.ATTRIBUTE2 := P_ATTRIBUTE2;');
1782: li(' l_loc_components_rec.ATTRIBUTE3 := P_ATTRIBUTE3;');

Line 1810: l(' IF HZ_GNR_UTIL_PKG.check_GNR_For_Usage(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');

1806: l(' END IF; ');
1807: l(' ');
1808: l(' IF P_LOCATION_ID IS NOT NULL AND P_CALLED_FROM <> ''GNR'' THEN');
1809: l(' --hk_debugl(''Before check_GNR_For_Usage'');');
1810: l(' IF HZ_GNR_UTIL_PKG.check_GNR_For_Usage(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1811: l(' L_USAGE_CODE,L_MDU_TBL,x_status) = TRUE THEN');
1812: l(' --hk_debugl(''After check_GNR_For_Usage with status :- ''||x_status);');
1813: l(' ');
1814: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1841: l(' HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MAP_DTLS_TBL,x_status);');

1837: l(' );');
1838: l(' END IF; ');
1839: l(' ');
1840: l(' -- After the following call L_MAP_DTLS_TBL will have the components value populated.');
1841: l(' HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MAP_DTLS_TBL,x_status);');
1842: l(' ');
1843: l(' -- Below code will overwrite the LOC_COMPVAL for COUNTRY to COUNTRY_CODE');
1844: l(' -- This change is to update COUNTRY column in locations table with COUNTRY_CODE ');
1845: l(' -- even if the table has Country name in this column and the validation is success ');

Line 1849: l(' HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MDU_TBL,x_status);');

1845: l(' -- even if the table has Country name in this column and the validation is success ');
1846: l(' L_MAP_DTLS_TBL(1).LOC_COMPVAL := G_MAP_REC.COUNTRY_CODE;');
1847: l(' ');
1848: l(' -- After the following call L_MDU_TBL will have the components value populated.');
1849: l(' HZ_GNR_UTIL_PKG.getLocCompValues(G_MAP_REC.LOC_TBL_NAME,L_LOC_COMPONENTS_REC,L_MDU_TBL,x_status);');
1850: l(' ');
1851: l(' --hk_debugl('' value of X_CALL_MAP : ''||X_CALL_MAP);');
1852: l(' IF X_CALL_MAP = ''Y'' THEN');
1853: l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');

Line 1889: l(' IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_SUCCESS THEN ');

1885: l(' -- hk_debugt(LL_MAP_DTLS_TBL); ');
1886: l(' -- hk_debugl(''Usage Map Table With loc comp values''); ');
1887: l(' -- hk_debugt(L_MDU_TBL); ');
1888: l(' ');
1889: l(' IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_SUCCESS THEN ');
1890: l(' -- hk_debugl(''COMPLETE mapping is error but is sufficient for passed usage. So setting X_STATUS to success''); ');
1891: l(' x_status := FND_API.g_ret_sts_success; ');
1892: l(' ');
1893: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 1925: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');

1921: l(' p_module => l_module');
1922: l(' );');
1923: l(' END IF; ');
1924: l(' ');
1925: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1926: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1927: l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1928: l(' END IF;');
1929: l(' ');

Line 1926: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');

1922: l(' );');
1923: l(' END IF; ');
1924: l(' ');
1925: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1926: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1927: l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1928: l(' END IF;');
1929: l(' ');
1930: l(' X_CALL_MAP := ''N'';');

Line 1940: l(' IF HZ_GNR_UTIL_PKG.do_usage_val(l_cause,L_MAP_DTLS_TBL,L_MDU_TBL,LL_MAP_DTLS_TBL,l_status) = FALSE THEN');

1936: l(' --hk_debugl(''Table that is returned by Validate For Map'');');
1937: l(' --hk_debugt(LL_MAP_DTLS_TBL);');
1938: l(' --hk_debugl(''Usage Map Table With loc comp values'');');
1939: l(' --hk_debugt(L_MDU_TBL);');
1940: l(' IF HZ_GNR_UTIL_PKG.do_usage_val(l_cause,L_MAP_DTLS_TBL,L_MDU_TBL,LL_MAP_DTLS_TBL,l_status) = FALSE THEN');
1941: l(' ');
1942: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1943: l(' hz_utility_v2pub.debug ');
1944: l(' (p_message => '' No usage level validation is required. Call create_gnr with the map status'',');

Line 1953: l(' IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_ERROR THEN');

1949: l(' );');
1950: l(' END IF; ');
1951: l(' ');
1952: l(' -- This means no usage level validation is required');
1953: l(' IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_ERROR THEN');
1954: l(' ');
1955: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1956: l(' -- This below call is to derive the address validation status and set the message ');
1957: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,''E'',x_status);');

Line 1955: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');

1951: l(' ');
1952: l(' -- This means no usage level validation is required');
1953: l(' IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_ERROR THEN');
1954: l(' ');
1955: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1956: l(' -- This below call is to derive the address validation status and set the message ');
1957: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,''E'',x_status);');
1958: l(' --hk_debugl(''Calling create_gnr With Map_status "E"'');');
1959: l(' ');

Line 1957: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,''E'',x_status);');

1953: l(' IF HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL) = FND_API.G_RET_STS_ERROR THEN');
1954: l(' ');
1955: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1956: l(' -- This below call is to derive the address validation status and set the message ');
1957: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,''E'',x_status);');
1958: l(' --hk_debugl(''Calling create_gnr With Map_status "E"'');');
1959: l(' ');
1960: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1961: l(' hz_utility_v2pub.debug ');

Line 1970: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');

1966: l(' p_module => l_module');
1967: l(' );');
1968: l(' END IF; ');
1969: l(' ');
1970: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1971: l(' L_USAGE_CODE,''E'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1972: l(' --hk_debugl(''Status after create_gnr : ''||l_status);');
1973: l(' ELSE ');
1974: l(' ');

Line 1975: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');

1971: l(' L_USAGE_CODE,''E'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1972: l(' --hk_debugl(''Status after create_gnr : ''||l_status);');
1973: l(' ELSE ');
1974: l(' ');
1975: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1976: l(' -- This below call is to derive the address validation status and set the message ');
1977: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,''S'',x_status);');
1978: l(' --hk_debugl(''Calling create_gnr With Map_status "S"'');');
1979: l(' ');

Line 1977: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,''S'',x_status);');

1973: l(' ELSE ');
1974: l(' ');
1975: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
1976: l(' -- This below call is to derive the address validation status and set the message ');
1977: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,''S'',x_status);');
1978: l(' --hk_debugl(''Calling create_gnr With Map_status "S"'');');
1979: l(' ');
1980: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
1981: l(' hz_utility_v2pub.debug ');

Line 1990: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');

1986: l(' p_module => l_module');
1987: l(' );');
1988: l(' END IF; ');
1989: l(' ');
1990: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
1991: l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
1992: l(' --hk_debugl(''Status after create_gnr : ''||l_status);');
1993: l(' x_status := FND_API.g_ret_sts_success;');
1994: l(' END IF;');

Line 2027: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(L_MDU_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,''S'',x_status);');

2023: l(' -- and country is already populated in the L_MDU_TBL in the initialization section of this package.');
2024: l(' --hk_debugt(L_MDU_TBL); ----- Code to display the output.');
2025: l(' --hk_debugl(''Calling create_gnr With Map_status "S"'');');
2026: l(' -- This below call is to derive the address validation status and set the message ');
2027: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(L_MDU_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,''S'',x_status);');
2028: l(' ');
2029: l(' IF P_LOCATION_ID IS NOT NULL THEN');
2030: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
2031: l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,L_MDU_TBL,l_status);');

Line 2030: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');

2026: l(' -- This below call is to derive the address validation status and set the message ');
2027: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(L_MDU_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,''S'',x_status);');
2028: l(' ');
2029: l(' IF P_LOCATION_ID IS NOT NULL THEN');
2030: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
2031: l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,L_MDU_TBL,l_status);');
2032: l(' END IF;');
2033: l(' ');
2034: l(' x_status := FND_API.g_ret_sts_success;');

Line 2081: l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);');

2077: l_open_cur := l_open_cur||',l_geography_type;';
2078: l_fetch_cur := l_fetch_cur||';';
2079:
2080: l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL;');
2081: l(' l_sql := HZ_GNR_UTIL_PKG.getQuery(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);');
2082: l(' --hk_debugl(''The SQL query'');');
2083: l(' --hk_debugl(l_sql);');
2084: l(' ');
2085: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 2112: l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status);');

2108: l(' p_module => l_module');
2109: l(' );');
2110: l(' END IF; ');
2111: l(' ');
2112: l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status);');
2113: l(' x_status := FND_API.G_RET_STS_ERROR;');
2114: l(' ELSE ');
2115: l(' --Fetching once more to see where there are multiple records');
2116: l(' ');

Line 2133: l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);');

2129: l(' IF c_getGeo%FOUND THEN -- not able to identify a unique record');
2130: l(' ');
2131: l(' -- Get the query again with identifier type as NAME if multiple match found');
2132: l(' -- If it returns a record, we are able to derive a unique record for identifier type as NAME');
2133: l(' l_sql := HZ_GNR_UTIL_PKG.getQueryforMultiMatch(L_MAP_DTLS_TBL,L_MDU_TBL,x_status);');
2134: l(' OPEN c_getGeo1 FOR l_sql USING G_MAP_REC.COUNTRY_CODE,g_country_geo_id');
2135: l(' '||l_open_cur);
2136: l(' ');
2137: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 2181: l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status); ');

2177: l(' ); ');
2178: l(' END IF; ');
2179: l(' ');
2180: l(' LL_MAP_DTLS_TBL := L_MAP_DTLS_TBL; ');
2181: l(' HZ_GNR_UTIL_PKG.fix_no_match(LL_MAP_DTLS_TBL,x_status); ');
2182: l(' x_status := FND_API.G_RET_STS_ERROR; ');
2183: l(' END IF; ');
2184: l(' ');
2185: ----- End of fix for Bug 5011366 (Nishant)

Line 2218: l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,LL_MAP_DTLS_TBL) = TRUE THEN');

2214: l(' ');
2215: l(' END IF;');
2216: l(' ');
2217: l(' IF l_multiple_parent_flag = ''Y'' AND x_status <> FND_API.G_RET_STS_ERROR THEN');
2218: l(' IF HZ_GNR_UTIL_PKG.fix_multiparent(l_geography_id,LL_MAP_DTLS_TBL) = TRUE THEN');
2219: l(' NULL;');
2220: l(' ELSE -- Multiple parent case not able to find a unique record ');
2221: l(' ');
2222: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 2274: l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MAP_DTLS_TBL) = FALSE THEN');

2270: l(' p_module => l_module');
2271: l(' );');
2272: l(' END IF; ');
2273: l(' ');
2274: l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MAP_DTLS_TBL) = FALSE THEN');
2275: l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL);');
2276: l(' END IF;');
2277: l(' ');
2278: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');

Line 2275: l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL);');

2271: l(' );');
2272: l(' END IF; ');
2273: l(' ');
2274: l(' IF HZ_GNR_UTIL_PKG.fix_child(LL_MAP_DTLS_TBL) = FALSE THEN');
2275: l(' x_status := HZ_GNR_UTIL_PKG.get_usage_val_status(LL_MAP_DTLS_TBL,L_MDU_TBL);');
2276: l(' END IF;');
2277: l(' ');
2278: l(' IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN ');
2279: l(' hz_utility_v2pub.debug ');

Line 2290: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');

2286: l(' END IF; ');
2287: l(' ');
2288: l(' --hk_debugl(''LL_MAP_DTLS_TBL before fill_values'');');
2289: l(' --hk_debugt(LL_MAP_DTLS_TBL);');
2290: l(' HZ_GNR_UTIL_PKG.fill_values(LL_MAP_DTLS_TBL);');
2291: l(' --hk_debugl(''LL_MAP_DTLS_TBL after fill_values'');');
2292: l(' --hk_debugt(LL_MAP_DTLS_TBL);');
2293: l(' ');
2294: l(' IF x_status = FND_API.g_ret_sts_success THEN');

Line 2299: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,x_status,x_status);');

2295: l(' -- We need to call the getAddrValStatus only once. All other cases we are looking into x_call_map ');
2296: l(' -- In some case the below code will execute with the x_call_map as N ');
2297: l(' IF l_get_addr_val = ''Y'' THEN');
2298: l(' -- This below call is to derive the address validation status and set the message ');
2299: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,x_status,x_status);');
2300: l(' END IF;');
2301: l(' ');
2302: l(' IF P_LOCATION_ID IS NOT NULL THEN');
2303: l(' ');

Line 2314: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');

2310: l(' p_module => l_module');
2311: l(' );');
2312: l(' END IF; ');
2313: l(' ');
2314: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
2315: l(' L_USAGE_CODE,''S'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
2316: l(' --hk_debugl(''Prceossed GNR With Status : S and returned with Status : ''||l_status);');
2317: l(' END IF;');
2318: l(' ELSE ');

Line 2323: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,x_status,x_status);');

2319: l(' -- We need to call the getAddrValStatus only once. All other cases we are looking into x_call_map ');
2320: l(' -- In some case the below code will execute with the x_call_map as N ');
2321: l(' IF l_get_addr_val = ''Y'' THEN');
2322: l(' -- This below call is to derive the address validation status and set the message ');
2323: l(' X_ADDR_VAL_STATUS := HZ_GNR_UTIL_PKG.getAddrValStatus(LL_MAP_DTLS_TBL,L_MDU_TBL,P_CALLED_FROM,P_ADDR_VAL_LEVEL,x_addr_warn_msg,x_status,x_status);');
2324: l(' END IF;');
2325: l(' ');
2326: l(' IF P_LOCATION_ID IS NOT NULL THEN');
2327: l(' ');

Line 2338: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');

2334: l(' p_module => l_module');
2335: l(' );');
2336: l(' END IF; ');
2337: l(' ');
2338: l(' HZ_GNR_UTIL_PKG.create_gnr(P_LOCATION_ID,G_MAP_REC.LOC_TBL_NAME,');
2339: l(' L_USAGE_CODE,''E'',L_LOC_COMPONENTS_REC,p_lock_flag,LL_MAP_DTLS_TBL,l_status);');
2340: l(' --hk_debugl(''Prceossed GNR With Status : E and returned with Status : ''||l_status);');
2341: l(' END IF;');
2342: l(' END IF;');

Line 2424: li(' G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;');

2420: li(' --------------------------------------');
2421: l(' ');
2422: li(' g_debug_count NUMBER := 0;');
2423: li(' g_country_geo_id NUMBER;');
2424: li(' G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;');
2425: li(' G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
2426: li(' G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;');
2427: li(' G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;');
2428: -- create global variables with mapping details per usage

Line 2425: li(' G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');

2421: l(' ');
2422: li(' g_debug_count NUMBER := 0;');
2423: li(' g_country_geo_id NUMBER;');
2424: li(' G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;');
2425: li(' G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
2426: li(' G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;');
2427: li(' G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;');
2428: -- create global variables with mapping details per usage
2429: i:=0;

Line 2426: li(' G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;');

2422: li(' g_debug_count NUMBER := 0;');
2423: li(' g_country_geo_id NUMBER;');
2424: li(' G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;');
2425: li(' G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
2426: li(' G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;');
2427: li(' G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;');
2428: -- create global variables with mapping details per usage
2429: i:=0;
2430: IF G_USAGE_TBL.COUNT > 0 THEN

Line 2427: li(' G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;');

2423: li(' g_country_geo_id NUMBER;');
2424: li(' G_MAP_REC HZ_GNR_UTIL_PKG.MAP_REC_TYPE;');
2425: li(' G_MAP_DTLS_TBL HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
2426: li(' G_USAGE_TBL HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;');
2427: li(' G_USAGE_DTLS_TBL HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;');
2428: -- create global variables with mapping details per usage
2429: i:=0;
2430: IF G_USAGE_TBL.COUNT > 0 THEN
2431: i := G_USAGE_TBL.FIRST;

Line 2433: li(' G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID||' HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');

2429: i:=0;
2430: IF G_USAGE_TBL.COUNT > 0 THEN
2431: i := G_USAGE_TBL.FIRST;
2432: LOOP
2433: li(' G_MDU_TBL'||G_USAGE_TBL(i).USAGE_ID||' HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;');
2434: EXIT WHEN i = G_USAGE_TBL.LAST;
2435: i := G_USAGE_TBL.NEXT(i);
2436: END LOOP;
2437: END IF;

Line 2497: p_map_rec IN HZ_GNR_UTIL_PKG.map_rec_type,

2493: *
2494: */
2495: --------------------------------------
2496: procedure genPkg(
2497: p_map_rec IN HZ_GNR_UTIL_PKG.map_rec_type,
2498: p_map_dtls_tbl IN HZ_GNR_UTIL_PKG.maploc_rec_tbl_type,
2499: p_usage_tbl IN HZ_GNR_UTIL_PKG.usage_tbl_type,
2500: p_usage_dtls_tbl IN HZ_GNR_UTIL_PKG.usage_dtls_tbl_type,
2501: x_pkgName OUT NOCOPY VARCHAR2,

Line 2498: p_map_dtls_tbl IN HZ_GNR_UTIL_PKG.maploc_rec_tbl_type,

2494: */
2495: --------------------------------------
2496: procedure genPkg(
2497: p_map_rec IN HZ_GNR_UTIL_PKG.map_rec_type,
2498: p_map_dtls_tbl IN HZ_GNR_UTIL_PKG.maploc_rec_tbl_type,
2499: p_usage_tbl IN HZ_GNR_UTIL_PKG.usage_tbl_type,
2500: p_usage_dtls_tbl IN HZ_GNR_UTIL_PKG.usage_dtls_tbl_type,
2501: x_pkgName OUT NOCOPY VARCHAR2,
2502: x_status OUT NOCOPY VARCHAR2) IS

Line 2499: p_usage_tbl IN HZ_GNR_UTIL_PKG.usage_tbl_type,

2495: --------------------------------------
2496: procedure genPkg(
2497: p_map_rec IN HZ_GNR_UTIL_PKG.map_rec_type,
2498: p_map_dtls_tbl IN HZ_GNR_UTIL_PKG.maploc_rec_tbl_type,
2499: p_usage_tbl IN HZ_GNR_UTIL_PKG.usage_tbl_type,
2500: p_usage_dtls_tbl IN HZ_GNR_UTIL_PKG.usage_dtls_tbl_type,
2501: x_pkgName OUT NOCOPY VARCHAR2,
2502: x_status OUT NOCOPY VARCHAR2) IS
2503:

Line 2500: p_usage_dtls_tbl IN HZ_GNR_UTIL_PKG.usage_dtls_tbl_type,

2496: procedure genPkg(
2497: p_map_rec IN HZ_GNR_UTIL_PKG.map_rec_type,
2498: p_map_dtls_tbl IN HZ_GNR_UTIL_PKG.maploc_rec_tbl_type,
2499: p_usage_tbl IN HZ_GNR_UTIL_PKG.usage_tbl_type,
2500: p_usage_dtls_tbl IN HZ_GNR_UTIL_PKG.usage_dtls_tbl_type,
2501: x_pkgName OUT NOCOPY VARCHAR2,
2502: x_status OUT NOCOPY VARCHAR2) IS
2503:
2504: BEGIN

Line 2548: l_map_rec HZ_GNR_UTIL_PKG.MAP_REC_TYPE;

2544:
2545: m number := 0;
2546: n number := 0;
2547:
2548: l_map_rec HZ_GNR_UTIL_PKG.MAP_REC_TYPE;
2549: l_map_dtls_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
2550: l_usage_tbl HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;
2551: l_usage_dtls_tbl HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;
2552:

Line 2549: l_map_dtls_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;

2545: m number := 0;
2546: n number := 0;
2547:
2548: l_map_rec HZ_GNR_UTIL_PKG.MAP_REC_TYPE;
2549: l_map_dtls_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
2550: l_usage_tbl HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;
2551: l_usage_dtls_tbl HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;
2552:
2553: CURSOR c_map(p_map_id IN NUMBER) IS

Line 2550: l_usage_tbl HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;

2546: n number := 0;
2547:
2548: l_map_rec HZ_GNR_UTIL_PKG.MAP_REC_TYPE;
2549: l_map_dtls_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
2550: l_usage_tbl HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;
2551: l_usage_dtls_tbl HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;
2552:
2553: CURSOR c_map(p_map_id IN NUMBER) IS
2554: SELECT MAP_ID,COUNTRY_CODE,LOC_TBL_NAME,ADDRESS_STYLE

Line 2551: l_usage_dtls_tbl HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;

2547:
2548: l_map_rec HZ_GNR_UTIL_PKG.MAP_REC_TYPE;
2549: l_map_dtls_tbl HZ_GNR_UTIL_PKG.MAPLOC_REC_TBL_TYPE;
2550: l_usage_tbl HZ_GNR_UTIL_PKG.USAGE_TBL_TYPE;
2551: l_usage_dtls_tbl HZ_GNR_UTIL_PKG.USAGE_DTLS_TBL_TYPE;
2552:
2553: CURSOR c_map(p_map_id IN NUMBER) IS
2554: SELECT MAP_ID,COUNTRY_CODE,LOC_TBL_NAME,ADDRESS_STYLE
2555: FROM hz_geo_struct_map