[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');
615: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
616: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
617: --Bug 4775798
618: ELSE
619: WSH_UTIL_CORE.printmsg('Unexpected error has occured in Get_Region_Info');
620: WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
621: END IF;
622: --
623: END Get_Region_Info;
616: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
617: --Bug 4775798
618: ELSE
619: WSH_UTIL_CORE.printmsg('Unexpected error has occured in Get_Region_Info');
620: WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
621: END IF;
622: --
623: END Get_Region_Info;
624:
1437: t.language = userenv('LANG') and
1438: l.last_update_date >= nvl(l_start_date, l.last_update_date) and
1439: l.last_update_date < nvl(l_end_date, l.last_update_date+1);
1440:
1441: l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
1442: l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
1443: l_new_request_id NUMBER := 0;
1444: i NUMBER := 0;
1445: l_worker_min NUMBER := 0;
1438: l.last_update_date >= nvl(l_start_date, l.last_update_date) and
1439: l.last_update_date < nvl(l_end_date, l.last_update_date+1);
1440:
1441: l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
1442: l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
1443: l_new_request_id NUMBER := 0;
1444: i NUMBER := 0;
1445: l_worker_min NUMBER := 0;
1446: l_worker_max NUMBER := 0;
1466: l_debug_on BOOLEAN;
1467: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_LOCATION_REGION_CHILD';
1468:
1469: BEGIN
1470: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
1471:
1472: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1473: IF l_debug_on IS NULL THEN
1474: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1474: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1475: END IF;
1476:
1477: --bug 4775798
1478: --changed WSH_UTIL_CORE.println to WSH_DEBUG_SV.log
1479: IF l_debug_on THEN
1480: WSH_DEBUG_SV.push(l_module_name);
1481: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
1482: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
1571: IF l_debug_on THEN
1572: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from Mapping_Regions_Main : '|| l_return_status);
1573: END IF;
1574:
1575: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1576:
1577: WSH_UTIL_CORE.printmsg('Failed in Procedure Mapping_Regions_Main');
1578: WSH_UTIL_CORE.printmsg(l_sqlcode);
1579: WSH_UTIL_CORE.printmsg(l_sqlerr);
1573: END IF;
1574:
1575: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1576:
1577: WSH_UTIL_CORE.printmsg('Failed in Procedure Mapping_Regions_Main');
1578: WSH_UTIL_CORE.printmsg(l_sqlcode);
1579: WSH_UTIL_CORE.printmsg(l_sqlerr);
1580: l_completion_status := 'ERROR';
1581: END IF;
1574:
1575: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1576:
1577: WSH_UTIL_CORE.printmsg('Failed in Procedure Mapping_Regions_Main');
1578: WSH_UTIL_CORE.printmsg(l_sqlcode);
1579: WSH_UTIL_CORE.printmsg(l_sqlerr);
1580: l_completion_status := 'ERROR';
1581: END IF;
1582: END IF;
1575: IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1576:
1577: WSH_UTIL_CORE.printmsg('Failed in Procedure Mapping_Regions_Main');
1578: WSH_UTIL_CORE.printmsg(l_sqlcode);
1579: WSH_UTIL_CORE.printmsg(l_sqlerr);
1580: l_completion_status := 'ERROR';
1581: END IF;
1582: END IF;
1583: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1590: EXCEPTION
1591:
1592: WHEN No_Data_Found THEN
1593:
1594: WSH_UTIL_CORE.printmsg('No matching records for the entered parameters');
1595: IF l_debug_on THEN
1596: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1597: END IF;
1598:
1599: WHEN others THEN
1600: l_sqlcode := SQLCODE;
1601: l_sqlerr := SQLERRM;
1602:
1603: WSH_UTIL_CORE.printmsg('Exception occurred in Map_Location_Region_Child Program');
1604: WSH_UTIL_CORE.printmsg('SQLCODE : ' || l_sqlcode);
1605: WSH_UTIL_CORE.printmsg('SQLERRM : ' || l_sqlerr);
1606:
1607: l_completion_status := 'ERROR';
1600: l_sqlcode := SQLCODE;
1601: l_sqlerr := SQLERRM;
1602:
1603: WSH_UTIL_CORE.printmsg('Exception occurred in Map_Location_Region_Child Program');
1604: WSH_UTIL_CORE.printmsg('SQLCODE : ' || l_sqlcode);
1605: WSH_UTIL_CORE.printmsg('SQLERRM : ' || l_sqlerr);
1606:
1607: l_completion_status := 'ERROR';
1608: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1601: l_sqlerr := SQLERRM;
1602:
1603: WSH_UTIL_CORE.printmsg('Exception occurred in Map_Location_Region_Child Program');
1604: WSH_UTIL_CORE.printmsg('SQLCODE : ' || l_sqlcode);
1605: WSH_UTIL_CORE.printmsg('SQLERRM : ' || l_sqlerr);
1606:
1607: l_completion_status := 'ERROR';
1608: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1609: p_errbuf := 'Exception occurred in Map_Location_Region_Child Program';
1633: FROM FND_CONCURRENT_REQUESTS
1634: WHERE parent_request_id = p_parent_request_id
1635: AND NVL(is_sub_request, 'N') = 'Y';
1636:
1637: l_child_req_ids WSH_UTIL_CORE.Id_Tab_Type;
1638: l_this_request NUMBER;
1639: l_errors NUMBER := 0;
1640: l_warnings NUMBER := 0;
1641: l_phase VARCHAR2(100);
1687:
1688: FND_MESSAGE.SET_NAME('WSH','WSH_CHILD_REQ_STATUS');
1689: FND_MESSAGE.SET_TOKEN('REQ_ID', to_char(l_child_req_ids(j)));
1690: FND_MESSAGE.SET_TOKEN('STATUS', l_status);
1691: WSH_UTIL_CORE.PrintMsg(FND_MESSAGE.GET);
1692: j := l_child_req_ids.NEXT(j);
1693: END LOOP;
1694:
1695: IF l_errors = 0 AND l_warnings = 0 THEN
1705: WSH_DEBUG_SV.pop(l_module_name);
1706: END IF;
1707: EXCEPTION
1708: WHEN others THEN
1709: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_Child_Requests_Status');
1710: --
1711: IF l_debug_on THEN
1712: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1713: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1782: END IF;
1783:
1784: EXCEPTION
1785: WHEN others THEN
1786: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Process_All_Locations');
1787: --
1788: IF l_debug_on THEN
1789: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1790: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1816: l_debug_on BOOLEAN;
1817: l_return_status VARCHAR2(10);
1818: l_completion_status VARCHAR2(30);
1819:
1820: l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
1821: l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
1822: l_new_request_id NUMBER := 0;
1823: i NUMBER := 0;
1824: l_temp BOOLEAN;
1817: l_return_status VARCHAR2(10);
1818: l_completion_status VARCHAR2(30);
1819:
1820: l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
1821: l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
1822: l_new_request_id NUMBER := 0;
1823: i NUMBER := 0;
1824: l_temp BOOLEAN;
1825: l_retcode NUMBER;
1845: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1846: END IF;
1847:
1848:
1849: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
1850: l_completion_status := 'NORMAL';
1851: --
1852: --
1853: -- Debug Statements
1902: END IF;
1903:
1904: IF l_retcode = '2' THEN
1905: l_completion_status := 'ERROR';
1906: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1907: ELSIF l_retcode = '1' THEN
1908: l_completion_status := 'WARNING';
1909: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1910: END IF;
1905: l_completion_status := 'ERROR';
1906: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1907: ELSIF l_retcode = '1' THEN
1908: l_completion_status := 'WARNING';
1909: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1910: END IF;
1911: --}
1912: ELSIF p_mode = 'MAP' THEN
1913: --{
2022: IF l_new_request_id = 0 THEN
2023: IF l_debug_on THEN
2024: WSH_debug_sv.logmsg(l_module_name,'Error Submitting concurrent request for worker : '||i);
2025: END IF;
2026: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2027: END IF;
2028:
2029: END LOOP;
2030: FND_CONC_GLOBAL.Set_Req_Globals ( Conc_Status => 'PAUSED', Request_Data => to_char(2) );
2051: END IF;
2052:
2053: IF l_retcode = '2' THEN
2054: l_completion_status := 'ERROR';
2055: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2056: ELSIF l_retcode = '1' THEN
2057: l_completion_status := 'WARNING';
2058: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2059: END IF;
2054: l_completion_status := 'ERROR';
2055: l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2056: ELSIF l_retcode = '1' THEN
2057: l_completion_status := 'WARNING';
2058: l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2059: END IF;
2060: --}
2061: END IF;
2062: --}
2088: END IF;
2089:
2090: EXCEPTION
2091: WHEN others THEN
2092: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Process_All_Locations');
2093: --
2094: IF l_debug_on THEN
2095: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2096: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2113: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2114: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'refresh_cache';
2115:
2116: BEGIN
2117: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2118: --
2119: IF l_debug_on THEN
2120: wsh_debug_sv.push (l_module_name);
2121: END IF;
2147: --
2148:
2149: EXCEPTION
2150: WHEN others THEN
2151: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.refresh_cache');
2152: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2153: --
2154: IF l_debug_on THEN
2155: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2148:
2149: EXCEPTION
2150: WHEN others THEN
2151: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.refresh_cache');
2152: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2153: --
2154: IF l_debug_on THEN
2155: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2156: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2280: l_region_rec3 region_rec;
2281: l_region_type NUMBER := 0;
2282: l_parent_offset NUMBER ;
2283: l_exists VARCHAR2(10) := NULL;
2284: l_rgid_tab WSH_UTIL_CORE.Id_Tab_Type;
2285: is_first BOOLEAN := true;
2286: l_regions region_table;
2287: l_return_status VARCHAR2(1);
2288:
2695: --
2696: --Bug 4775798
2697: EXCEPTION
2698: WHEN OTHERS THEN
2699: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches');
2700: IF l_debug_on THEN
2701: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2702: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2703: ELSE
2700: IF l_debug_on THEN
2701: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2702: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2703: ELSE
2704: WSH_UTIL_CORE.printmsg('Unexpected error has occured in WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches');
2705: WSH_UTIL_CORE.printmsg(SQLERRM);
2706: END IF;
2707: END Get_All_Region_Matches;
2708:
2701: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2702: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2703: ELSE
2704: WSH_UTIL_CORE.printmsg('Unexpected error has occured in WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches');
2705: WSH_UTIL_CORE.printmsg(SQLERRM);
2706: END IF;
2707: END Get_All_Region_Matches;
2708:
2709:
2718: PROCEDURE Get_All_RegionId_Matches(
2719: p_location_id IN NUMBER,
2720: p_use_cache IN BOOLEAN DEFAULT FALSE,
2721: p_lang_code IN VARCHAR2,
2722: x_region_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
2723: x_return_status OUT NOCOPY VARCHAR2) IS
2724:
2725:
2726: CURSOR c_get_all_region_id(l_location_id IN NUMBER, l_lang_code IN VARCHAR2) IS
2733: AND wrt.region_id = wr.region_id
2734: ORDER BY wrl.region_type DESC;
2735:
2736: --
2737: l_region_id_tab WSH_UTIL_CORE.Id_Tab_Type;
2738: itr NUMBER;
2739: l_region_id_string VARCHAR2(32767);
2740: --
2741:
2744: l_return_status VARCHAR2(1);
2745:
2746: BEGIN
2747:
2748: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2749:
2750: IF l_debug_on THEN
2751: wsh_debug_sv.push(l_module_name);
2752: END IF;
2755:
2756: IF (g_location_region_tab.EXISTS(p_location_id)) THEN
2757:
2758:
2759: WSH_UTIL_CORE.get_idtab_from_string(
2760: p_string => g_location_region_tab(p_location_id),
2761: x_id_tab => l_region_id_tab,
2762: x_return_status => l_return_status);
2763:
2796: l_region_id_string := '-1';
2797:
2798: ELSE
2799: -- Return l_region_id_tab
2800: WSH_UTIL_CORE.get_string_from_idtab(
2801: p_id_tab => l_region_id_tab,
2802: x_string => l_region_id_string,
2803: x_return_status => l_return_status);
2804: END IF;
2813: WHEN others THEN
2814: IF c_get_all_region_id%ISOPEN THEN
2815: CLOSE c_get_all_region_id;
2816: END IF;
2817: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches');
2818: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2819: --
2820: IF l_debug_on THEN
2821: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2814: IF c_get_all_region_id%ISOPEN THEN
2815: CLOSE c_get_all_region_id;
2816: END IF;
2817: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches');
2818: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2819: --
2820: IF l_debug_on THEN
2821: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2822: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2821: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2822: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2823: --Bug 4775798
2824: ELSE
2825: WSH_UTIL_CORE.printmsg('Unexpected error has occured in Get_All_RegionId_Matches');
2826: WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
2827:
2828: END IF;
2829: --
2822: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2823: --Bug 4775798
2824: ELSE
2825: WSH_UTIL_CORE.printmsg('Unexpected error has occured in Get_All_RegionId_Matches');
2826: WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
2827:
2828: END IF;
2829: --
2830: END Get_All_RegionId_Matches;
2837: --
2838:
2839: PROCEDURE Get_All_Zone_Matches(
2840: p_region_id IN NUMBER,
2841: x_zone_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
2842: x_return_status OUT NOCOPY VARCHAR2) IS
2843:
2844: CURSOR c_get_zone_for_region(c_region_id IN NUMBER) IS
2845: SELECT wr.region_id
2849: AND wzr.parent_region_id = wr.region_id
2850: AND wr.region_type=10;
2851:
2852: --
2853: l_zone_tab WSH_UTIL_CORE.Id_Tab_Type;
2854: l_zone_id_string VARCHAR2(32767);
2855: itr NUMBER;
2856: l_return_status VARCHAR2(1);
2857:
2861: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2862:
2863: BEGIN
2864:
2865: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2866:
2867: IF l_debug_on THEN
2868: wsh_debug_sv.push(l_module_name);
2869: END IF;
2869: END IF;
2870:
2871: IF (p_region_id < g_cache_max_size AND g_region_zone_tab.EXISTS(p_region_id)) THEN
2872:
2873: WSH_UTIL_CORE.get_idtab_from_string(
2874: p_string => g_region_zone_tab(p_region_id),
2875: x_id_tab => l_zone_tab,
2876: x_return_status => l_return_status);
2877:
2907: IF (itr ) IS NULL THEN
2908: l_zone_id_string := '-1';
2909: ELSE
2910:
2911: WSH_UTIL_CORE.get_string_from_idtab(
2912: p_id_tab => l_zone_tab,
2913: x_string => l_zone_id_string,
2914: x_return_status => l_return_status);
2915:
2927: WHEN others THEN
2928: IF c_get_zone_for_region%ISOPEN THEN
2929: CLOSE c_get_zone_for_region;
2930: END IF;
2931: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches');
2932: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2933: --
2934: IF l_debug_on THEN
2935: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2928: IF c_get_zone_for_region%ISOPEN THEN
2929: CLOSE c_get_zone_for_region;
2930: END IF;
2931: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches');
2932: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2933: --
2934: IF l_debug_on THEN
2935: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2936: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3009: AND wr.deconsol_location_id IS NOT NULL
3010: ORDER BY wr.region_type DESC;
3011: BEGIN
3012: ----{
3013: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3014:
3015: IF l_debug_on THEN
3016: wsh_debug_sv.push (l_module_name);
3017: END IF;
3186:
3187: EXCEPTION
3188:
3189: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3190: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3191: --
3192: IF l_debug_on THEN
3193: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3194: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3200: END IF;
3201: IF c_get_zone_for_region%ISOPEN THEN
3202: CLOSE c_get_zone_for_region;
3203: END IF;
3204: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.get_all_region_deconsols');
3205: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3206: --
3207: IF l_debug_on THEN
3208: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3201: IF c_get_zone_for_region%ISOPEN THEN
3202: CLOSE c_get_zone_for_region;
3203: END IF;
3204: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.get_all_region_deconsols');
3205: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3206: --
3207: IF l_debug_on THEN
3208: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3209: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3288: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3289: END IF;
3290: --
3291:
3292: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3293:
3294: t1 := dbms_utility.get_time;
3295:
3296: IF ( p_state is not null and p_city is not null )
3328:
3329: -- Exception Handling part
3330: EXCEPTION
3331: WHEN OTHERS THEN
3332: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3333: fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
3334: fnd_message.set_token('MSG_TEXT', SQLERRM);
3335: IF l_debug_on THEN
3336: wsh_debug_sv.logmsg(l_module_name, 'CHECK_REGION_INFO EXCEPTION : ' || sqlerrm);
3407: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3408: END IF;
3409: --
3410:
3411: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3412:
3413: t1 := dbms_utility.get_time;
3414: OPEN C1;
3415: FETCH C1 BULK COLLECT INTO TMP_REGION_ID;
3439:
3440: -- Exception Handling part
3441: EXCEPTION
3442: WHEN OTHERS THEN
3443: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3444: fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
3445: fnd_message.set_token('MSG_TEXT', SQLERRM);
3446: IF l_debug_on THEN
3447: wsh_debug_sv.logmsg(l_module_name, 'CHECK_REGION_INFO_CODE EXCEPTION : ' || SUBSTR(SQLERRM,1,200));
3524: WSH_DEBUG_SV.log(l_module_name, 'P_LANGUAGE_CODE', P_LANGUAGE_CODE);
3525: END IF;
3526: --
3527:
3528: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3529:
3530: IF ( ( p_country_code is null and p_state_code is null and p_city_code is null)
3531: OR ( p_region_type = 2 and p_city_code is null )
3532: OR ( p_region_type = 1 and p_state_code is null )
3586: --wsh_debug_sv.logmsg(l_module_name, 'TOTAL TIME TAKEN FOR CHECK_REGION_ID_CODES_ONLY : ' || ((dbms_utility.get_time - t1)/100));
3587: -- Exception Handling part
3588: EXCEPTION
3589: WHEN OTHERS THEN
3590: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3591: fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
3592: fnd_message.set_token('MSG_TEXT', SQLERRM);
3593: IF l_debug_on THEN
3594: wsh_debug_sv.logmsg(l_module_name, 'CHECK_REGION_ID_CODES_ONLY EXCEPTION : ' || SUBSTR(SQLERRM,1,200));