[Home] [Help]
16: -- PUBLIC VARIABLES
17: --
18:
19: --Global cache for regions and locations.
20: g_region_zone_tab WSH_UTIL_CORE.tbl_varchar;
21: g_location_region_tab WSH_UTIL_CORE.tbl_varchar;
22:
23: g_loc_region_deconsol_tab loc_region_deconsol_tab_type;
24: g_loc_region_zone_deconsol_tab loc_region_deconsol_tab_type;
17: --
18:
19: --Global cache for regions and locations.
20: g_region_zone_tab WSH_UTIL_CORE.tbl_varchar;
21: g_location_region_tab WSH_UTIL_CORE.tbl_varchar;
22:
23: g_loc_region_deconsol_tab loc_region_deconsol_tab_type;
24: g_loc_region_zone_deconsol_tab loc_region_deconsol_tab_type;
25: g_region_zone_deconsol_tab region_zone_deconsol_tab_type;
132: END IF;
133: x_return_status := l_return_status;
134: EXCEPTION
135: WHEN others THEN
136: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.get_key');
137: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
138: --
139: IF l_debug_on THEN
140: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
133: x_return_status := l_return_status;
134: EXCEPTION
135: WHEN others THEN
136: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.get_key');
137: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
138: --
139: IF l_debug_on THEN
140: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
141: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
884: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
885: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
886: --Bug 4775798
887: ELSE
888: WSH_UTIL_CORE.printmsg('Unexpected error has occured in Get_Region_Info');
889: WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
890: END IF;
891: --
892: END Get_Region_Info;
885: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
886: --Bug 4775798
887: ELSE
888: WSH_UTIL_CORE.printmsg('Unexpected error has occured in Get_Region_Info');
889: WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
890: END IF;
891: --
892: END Get_Region_Info;
893:
1707: t.language = userenv('LANG') and
1708: l.last_update_date >= nvl(l_start_date, l.last_update_date) and
1709: l.last_update_date < nvl(l_end_date, l.last_update_date+1);
1710:
1711: l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
1712: l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
1713: l_new_request_id NUMBER := 0;
1714: i NUMBER := 0;
1715: l_worker_min NUMBER := 0;
1708: l.last_update_date >= nvl(l_start_date, l.last_update_date) and
1709: l.last_update_date < nvl(l_end_date, l.last_update_date+1);
1710:
1711: l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
1712: l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
1713: l_new_request_id NUMBER := 0;
1714: i NUMBER := 0;
1715: l_worker_min NUMBER := 0;
1716: l_worker_max NUMBER := 0;
1736: l_debug_on BOOLEAN;
1737: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_LOCATION_REGION_CHILD';
1738:
1739: BEGIN
1740: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
1741:
1742: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1743: IF l_debug_on IS NULL THEN
1744: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1744: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1745: END IF;
1746:
1747: --bug 4775798
1748: --changed WSH_UTIL_CORE.println to WSH_DEBUG_SV.log
1749: IF l_debug_on THEN
1750: WSH_DEBUG_SV.push(l_module_name);
1751: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
1752: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
1841: IF l_debug_on THEN
1842: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from Mapping_Regions_Main : '|| l_return_status);
1843: END IF;
1844:
1845: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1846:
1847: WSH_UTIL_CORE.printmsg('Failed in Procedure Mapping_Regions_Main');
1848: WSH_UTIL_CORE.printmsg(l_sqlcode);
1849: WSH_UTIL_CORE.printmsg(l_sqlerr);
1843: END IF;
1844:
1845: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1846:
1847: WSH_UTIL_CORE.printmsg('Failed in Procedure Mapping_Regions_Main');
1848: WSH_UTIL_CORE.printmsg(l_sqlcode);
1849: WSH_UTIL_CORE.printmsg(l_sqlerr);
1850: l_completion_status := 'ERROR';
1851: END IF;
1844:
1845: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1846:
1847: WSH_UTIL_CORE.printmsg('Failed in Procedure Mapping_Regions_Main');
1848: WSH_UTIL_CORE.printmsg(l_sqlcode);
1849: WSH_UTIL_CORE.printmsg(l_sqlerr);
1850: l_completion_status := 'ERROR';
1851: END IF;
1852: END IF;
1845: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1846:
1847: WSH_UTIL_CORE.printmsg('Failed in Procedure Mapping_Regions_Main');
1848: WSH_UTIL_CORE.printmsg(l_sqlcode);
1849: WSH_UTIL_CORE.printmsg(l_sqlerr);
1850: l_completion_status := 'ERROR';
1851: END IF;
1852: END IF;
1853: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1860: EXCEPTION
1861:
1862: WHEN No_Data_Found THEN
1863:
1864: WSH_UTIL_CORE.printmsg('No matching records for the entered parameters');
1865: IF l_debug_on THEN
1866: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1867: END IF;
1868:
1869: WHEN others THEN
1870: l_sqlcode := SQLCODE;
1871: l_sqlerr := SQLERRM;
1872:
1873: WSH_UTIL_CORE.printmsg('Exception occurred in Map_Location_Region_Child Program');
1874: WSH_UTIL_CORE.printmsg('SQLCODE : ' || l_sqlcode);
1875: WSH_UTIL_CORE.printmsg('SQLERRM : ' || l_sqlerr);
1876:
1877: l_completion_status := 'ERROR';
1870: l_sqlcode := SQLCODE;
1871: l_sqlerr := SQLERRM;
1872:
1873: WSH_UTIL_CORE.printmsg('Exception occurred in Map_Location_Region_Child Program');
1874: WSH_UTIL_CORE.printmsg('SQLCODE : ' || l_sqlcode);
1875: WSH_UTIL_CORE.printmsg('SQLERRM : ' || l_sqlerr);
1876:
1877: l_completion_status := 'ERROR';
1878: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1871: l_sqlerr := SQLERRM;
1872:
1873: WSH_UTIL_CORE.printmsg('Exception occurred in Map_Location_Region_Child Program');
1874: WSH_UTIL_CORE.printmsg('SQLCODE : ' || l_sqlcode);
1875: WSH_UTIL_CORE.printmsg('SQLERRM : ' || l_sqlerr);
1876:
1877: l_completion_status := 'ERROR';
1878: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1879: p_errbuf := 'Exception occurred in Map_Location_Region_Child Program';
1903: FROM FND_CONCURRENT_REQUESTS
1904: WHERE parent_request_id = p_parent_request_id
1905: AND NVL(is_sub_request, 'N') = 'Y';
1906:
1907: l_child_req_ids WSH_UTIL_CORE.Id_Tab_Type;
1908: l_this_request NUMBER;
1909: l_errors NUMBER := 0;
1910: l_warnings NUMBER := 0;
1911: l_phase VARCHAR2(100);
1957:
1958: FND_MESSAGE.SET_NAME('WSH','WSH_CHILD_REQ_STATUS');
1959: FND_MESSAGE.SET_TOKEN('REQ_ID', to_char(l_child_req_ids(j)));
1960: FND_MESSAGE.SET_TOKEN('STATUS', l_status);
1961: WSH_UTIL_CORE.PrintMsg(FND_MESSAGE.GET);
1962: j := l_child_req_ids.NEXT(j);
1963: END LOOP;
1964:
1965: IF l_errors = 0 AND l_warnings = 0 THEN
1975: WSH_DEBUG_SV.pop(l_module_name);
1976: END IF;
1977: EXCEPTION
1978: WHEN others THEN
1979: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_Child_Requests_Status');
1980: --
1981: IF l_debug_on THEN
1982: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1983: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2052: END IF;
2053:
2054: EXCEPTION
2055: WHEN others THEN
2056: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Process_All_Locations');
2057: --
2058: IF l_debug_on THEN
2059: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2060: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2086: l_debug_on BOOLEAN;
2087: l_return_status VARCHAR2(10);
2088: l_completion_status VARCHAR2(30);
2089:
2090: l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
2091: l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
2092: l_new_request_id NUMBER := 0;
2093: i NUMBER := 0;
2094: l_temp BOOLEAN;
2087: l_return_status VARCHAR2(10);
2088: l_completion_status VARCHAR2(30);
2089:
2090: l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
2091: l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
2092: l_new_request_id NUMBER := 0;
2093: i NUMBER := 0;
2094: l_temp BOOLEAN;
2095: l_retcode NUMBER;
2115: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2116: END IF;
2117:
2118:
2119: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2120: l_completion_status := 'NORMAL';
2121: --
2122: --
2123: -- Debug Statements
2172: END IF;
2173:
2174: IF l_retcode = '2' THEN
2175: l_completion_status := 'ERROR';
2176: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2177: ELSIF l_retcode = '1' THEN
2178: l_completion_status := 'WARNING';
2179: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2180: END IF;
2175: l_completion_status := 'ERROR';
2176: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2177: ELSIF l_retcode = '1' THEN
2178: l_completion_status := 'WARNING';
2179: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2180: END IF;
2181: --}
2182: ELSIF p_mode = 'MAP' THEN
2183: --{
2292: IF l_new_request_id = 0 THEN
2293: IF l_debug_on THEN
2294: WSH_debug_sv.logmsg(l_module_name,'Error Submitting concurrent request for worker : '||i);
2295: END IF;
2296: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2297: END IF;
2298:
2299: END LOOP;
2300: FND_CONC_GLOBAL.Set_Req_Globals ( Conc_Status => 'PAUSED', Request_Data => to_char(2) );
2321: END IF;
2322:
2323: IF l_retcode = '2' THEN
2324: l_completion_status := 'ERROR';
2325: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2326: ELSIF l_retcode = '1' THEN
2327: l_completion_status := 'WARNING';
2328: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2329: END IF;
2324: l_completion_status := 'ERROR';
2325: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2326: ELSIF l_retcode = '1' THEN
2327: l_completion_status := 'WARNING';
2328: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2329: END IF;
2330: --}
2331: END IF;
2332: --}
2358: END IF;
2359:
2360: EXCEPTION
2361: WHEN others THEN
2362: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Process_All_Locations');
2363: --
2364: IF l_debug_on THEN
2365: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2366: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2383: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2384: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'refresh_cache';
2385:
2386: BEGIN
2387: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2388: --
2389: IF l_debug_on THEN
2390: wsh_debug_sv.push (l_module_name);
2391: END IF;
2417: --
2418:
2419: EXCEPTION
2420: WHEN others THEN
2421: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.refresh_cache');
2422: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2423: --
2424: IF l_debug_on THEN
2425: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2418:
2419: EXCEPTION
2420: WHEN others THEN
2421: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.refresh_cache');
2422: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2423: --
2424: IF l_debug_on THEN
2425: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2426: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2551: l_region_rec3 region_rec;
2552: l_region_type NUMBER := 0;
2553: l_parent_offset NUMBER ;
2554: l_exists VARCHAR2(10) := NULL;
2555: l_rgid_tab WSH_UTIL_CORE.Id_Tab_Type;
2556: is_first BOOLEAN := true;
2557: l_regions region_table;
2558: l_return_status VARCHAR2(1);
2559:
2966: --
2967: --Bug 4775798
2968: EXCEPTION
2969: WHEN OTHERS THEN
2970: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches');
2971: IF l_debug_on THEN
2972: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2973: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2974: ELSE
2971: IF l_debug_on THEN
2972: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2973: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2974: ELSE
2975: WSH_UTIL_CORE.printmsg('Unexpected error has occured in WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches');
2976: WSH_UTIL_CORE.printmsg(SQLERRM);
2977: END IF;
2978: END Get_All_Region_Matches;
2979:
2972: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2973: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2974: ELSE
2975: WSH_UTIL_CORE.printmsg('Unexpected error has occured in WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches');
2976: WSH_UTIL_CORE.printmsg(SQLERRM);
2977: END IF;
2978: END Get_All_Region_Matches;
2979:
2980:
2989: PROCEDURE Get_All_RegionId_Matches(
2990: p_location_id IN NUMBER,
2991: p_use_cache IN BOOLEAN DEFAULT FALSE,
2992: p_lang_code IN VARCHAR2,
2993: x_region_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
2994: x_return_status OUT NOCOPY VARCHAR2) IS
2995:
2996:
2997: CURSOR c_get_all_region_id(l_location_id IN NUMBER, l_lang_code IN VARCHAR2) IS
3004: AND wrt.region_id = wr.region_id
3005: ORDER BY wrl.region_type DESC;
3006:
3007: --
3008: l_region_id_tab WSH_UTIL_CORE.Id_Tab_Type;
3009: itr NUMBER;
3010: l_region_id_string VARCHAR2(32767);
3011: --
3012:
3015: l_return_status VARCHAR2(1);
3016:
3017: BEGIN
3018:
3019: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3020:
3021: IF l_debug_on THEN
3022: wsh_debug_sv.push(l_module_name);
3023: END IF;
3026:
3027: IF (g_location_region_tab.EXISTS(p_location_id)) THEN
3028:
3029:
3030: WSH_UTIL_CORE.get_idtab_from_string(
3031: p_string => g_location_region_tab(p_location_id),
3032: x_id_tab => l_region_id_tab,
3033: x_return_status => l_return_status);
3034:
3067: l_region_id_string := '-1';
3068:
3069: ELSE
3070: -- Return l_region_id_tab
3071: WSH_UTIL_CORE.get_string_from_idtab(
3072: p_id_tab => l_region_id_tab,
3073: x_string => l_region_id_string,
3074: x_return_status => l_return_status);
3075: END IF;
3084: WHEN others THEN
3085: IF c_get_all_region_id%ISOPEN THEN
3086: CLOSE c_get_all_region_id;
3087: END IF;
3088: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches');
3089: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3090: --
3091: IF l_debug_on THEN
3092: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3085: IF c_get_all_region_id%ISOPEN THEN
3086: CLOSE c_get_all_region_id;
3087: END IF;
3088: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches');
3089: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3090: --
3091: IF l_debug_on THEN
3092: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3093: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3092: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3093: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3094: --Bug 4775798
3095: ELSE
3096: WSH_UTIL_CORE.printmsg('Unexpected error has occured in Get_All_RegionId_Matches');
3097: WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
3098:
3099: END IF;
3100: --
3093: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3094: --Bug 4775798
3095: ELSE
3096: WSH_UTIL_CORE.printmsg('Unexpected error has occured in Get_All_RegionId_Matches');
3097: WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
3098:
3099: END IF;
3100: --
3101: END Get_All_RegionId_Matches;
3108: --
3109:
3110: PROCEDURE Get_All_Zone_Matches(
3111: p_region_id IN NUMBER,
3112: x_zone_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
3113: x_return_status OUT NOCOPY VARCHAR2) IS
3114:
3115: CURSOR c_get_zone_for_region(c_region_id IN NUMBER) IS
3116: SELECT wr.region_id
3120: AND wzr.parent_region_id = wr.region_id
3121: AND wr.region_type=10;
3122:
3123: --
3124: l_zone_tab WSH_UTIL_CORE.Id_Tab_Type;
3125: l_zone_id_string VARCHAR2(32767);
3126: itr NUMBER;
3127: l_return_status VARCHAR2(1);
3128:
3132: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3133:
3134: BEGIN
3135:
3136: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3137:
3138: IF l_debug_on THEN
3139: wsh_debug_sv.push(l_module_name);
3140: END IF;
3140: END IF;
3141:
3142: IF (p_region_id < g_cache_max_size AND g_region_zone_tab.EXISTS(p_region_id)) THEN
3143:
3144: WSH_UTIL_CORE.get_idtab_from_string(
3145: p_string => g_region_zone_tab(p_region_id),
3146: x_id_tab => l_zone_tab,
3147: x_return_status => l_return_status);
3148:
3178: IF (itr ) IS NULL THEN
3179: l_zone_id_string := '-1';
3180: ELSE
3181:
3182: WSH_UTIL_CORE.get_string_from_idtab(
3183: p_id_tab => l_zone_tab,
3184: x_string => l_zone_id_string,
3185: x_return_status => l_return_status);
3186:
3198: WHEN others THEN
3199: IF c_get_zone_for_region%ISOPEN THEN
3200: CLOSE c_get_zone_for_region;
3201: END IF;
3202: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches');
3203: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3204: --
3205: IF l_debug_on THEN
3206: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3199: IF c_get_zone_for_region%ISOPEN THEN
3200: CLOSE c_get_zone_for_region;
3201: END IF;
3202: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches');
3203: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3204: --
3205: IF l_debug_on THEN
3206: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3207: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3280: AND wr.deconsol_location_id IS NOT NULL
3281: ORDER BY wr.region_type DESC;
3282: BEGIN
3283: ----{
3284: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3285:
3286: IF l_debug_on THEN
3287: wsh_debug_sv.push (l_module_name);
3288: END IF;
3457:
3458: EXCEPTION
3459:
3460: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3461: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3462: --
3463: IF l_debug_on THEN
3464: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3465: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3471: END IF;
3472: IF c_get_zone_for_region%ISOPEN THEN
3473: CLOSE c_get_zone_for_region;
3474: END IF;
3475: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.get_all_region_deconsols');
3476: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3477: --
3478: IF l_debug_on THEN
3479: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3472: IF c_get_zone_for_region%ISOPEN THEN
3473: CLOSE c_get_zone_for_region;
3474: END IF;
3475: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.get_all_region_deconsols');
3476: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3477: --
3478: IF l_debug_on THEN
3479: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3480: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3559: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3560: END IF;
3561: --
3562:
3563: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3564:
3565: t1 := dbms_utility.get_time;
3566:
3567: IF ( p_state is not null and p_city is not null )
3599:
3600: -- Exception Handling part
3601: EXCEPTION
3602: WHEN OTHERS THEN
3603: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3604: fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
3605: fnd_message.set_token('MSG_TEXT', SQLERRM);
3606: IF l_debug_on THEN
3607: wsh_debug_sv.logmsg(l_module_name, 'CHECK_REGION_INFO EXCEPTION : ' || sqlerrm);
3678: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3679: END IF;
3680: --
3681:
3682: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3683:
3684: t1 := dbms_utility.get_time;
3685: OPEN C1;
3686: FETCH C1 BULK COLLECT INTO TMP_REGION_ID;
3710:
3711: -- Exception Handling part
3712: EXCEPTION
3713: WHEN OTHERS THEN
3714: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3715: fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
3716: fnd_message.set_token('MSG_TEXT', SQLERRM);
3717: IF l_debug_on THEN
3718: wsh_debug_sv.logmsg(l_module_name, 'CHECK_REGION_INFO_CODE EXCEPTION : ' || SUBSTR(SQLERRM,1,200));
3795: WSH_DEBUG_SV.log(l_module_name, 'P_LANGUAGE_CODE', P_LANGUAGE_CODE);
3796: END IF;
3797: --
3798:
3799: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3800:
3801: IF ( ( p_country_code is null and p_state_code is null and p_city_code is null)
3802: OR ( p_region_type = 2 and p_city_code is null )
3803: OR ( p_region_type = 1 and p_state_code is null )
3857: --wsh_debug_sv.logmsg(l_module_name, 'TOTAL TIME TAKEN FOR CHECK_REGION_ID_CODES_ONLY : ' || ((dbms_utility.get_time - t1)/100));
3858: -- Exception Handling part
3859: EXCEPTION
3860: WHEN OTHERS THEN
3861: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3862: fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
3863: fnd_message.set_token('MSG_TEXT', SQLERRM);
3864: IF l_debug_on THEN
3865: wsh_debug_sv.logmsg(l_module_name, 'CHECK_REGION_ID_CODES_ONLY EXCEPTION : ' || SUBSTR(SQLERRM,1,200));