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.WSH_REGIONS_SEARCH_PKG dependencies on WSH_REGIONS

Line 1: PACKAGE BODY WSH_REGIONS_SEARCH_PKG AS

1: PACKAGE BODY WSH_REGIONS_SEARCH_PKG AS
2: /* $Header: WSHRESEB.pls 120.15.12020000.2 2012/07/04 10:39:02 suppal ship $ */
3:
4: --
5: -- Package

Line 6: -- WSH_REGIONS_SEARCH_PKG

2: /* $Header: WSHRESEB.pls 120.15.12020000.2 2012/07/04 10:39:02 suppal ship $ */
3:
4: --
5: -- Package
6: -- WSH_REGIONS_SEARCH_PKG
7: --
8: -- Purpose
9: --
10:

Line 56: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_REGIONS_SEARCH_PKG';

52: -- Purpose: call another Get_Region_Info with p_search_flag ='N'
53: --
54: --
55: --
56: G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_REGIONS_SEARCH_PKG';
57: --
58:
59: --
60: -- PROCEDURE : get_key

Line 136: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.get_key');

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

Line 434: -- 12564197: sql_string := 'select /+ index(Tl WSH_REGIONS_TL_N3) / R.REGION_ID,

430: END IF;
431:
432: --Modified for bug 11070829
433: IF (p_postal_code_from IS NOT NULL) THEN
434: -- 12564197: sql_string := 'select /*+ index(Tl WSH_REGIONS_TL_N3) */ R.REGION_ID,
435: sql_string := 'select /*+ leading(tl) index(tl WSH_REGIONS_TL_N3) use_nl(tl, r) use_concat */ R.REGION_ID,
436: R.REGION_TYPE,
437: TL.COUNTRY,
438: TL.COUNTRY_REGION,

Line 435: sql_string := 'select /+ leading(tl) index(tl WSH_REGIONS_TL_N3) use_nl(tl, r) use_concat / R.REGION_ID,

431:
432: --Modified for bug 11070829
433: IF (p_postal_code_from IS NOT NULL) THEN
434: -- 12564197: sql_string := 'select /*+ index(Tl WSH_REGIONS_TL_N3) */ R.REGION_ID,
435: sql_string := 'select /*+ leading(tl) index(tl WSH_REGIONS_TL_N3) use_nl(tl, r) use_concat */ R.REGION_ID,
436: R.REGION_TYPE,
437: TL.COUNTRY,
438: TL.COUNTRY_REGION,
439: TL.STATE,

Line 473: ' from WSH_REGIONS_INTERFACE R, WSH_REGIONS_TL_INTERFACE TL';

469:
470:
471: IF (p_interface_flag = 'Y') THEN
472: sql_string := sql_string ||
473: ' from WSH_REGIONS_INTERFACE R, WSH_REGIONS_TL_INTERFACE TL';
474: ELSE
475: sql_string := sql_string ||
476: ' from WSH_REGIONS R, WSH_REGIONS_TL TL';
477: END IF;

Line 476: ' from WSH_REGIONS R, WSH_REGIONS_TL TL';

472: sql_string := sql_string ||
473: ' from WSH_REGIONS_INTERFACE R, WSH_REGIONS_TL_INTERFACE TL';
474: ELSE
475: sql_string := sql_string ||
476: ' from WSH_REGIONS R, WSH_REGIONS_TL TL';
477: END IF;
478:
479:
480: -- sql_string := sql_string ||

Line 1149: ' from WSH_REGIONS_INTERFACE R ';

1145: sql_string := 'select R.REGION_ID';
1146:
1147: IF (p_interface_flag = 'Y') THEN
1148: sql_string := sql_string ||
1149: ' from WSH_REGIONS_INTERFACE R ';
1150: ELSE
1151: sql_string := sql_string ||
1152: ' from WSH_REGIONS R ';
1153: END IF;

Line 1152: ' from WSH_REGIONS R ';

1148: sql_string := sql_string ||
1149: ' from WSH_REGIONS_INTERFACE R ';
1150: ELSE
1151: sql_string := sql_string ||
1152: ' from WSH_REGIONS R ';
1153: END IF;
1154:
1155:
1156: IF (p_postal_code_from IS NOT NULL OR p_language_code IS NOT NULL) THEN

Line 1161: ', WSH_REGIONS_TL_INTERFACE TL ';

1157:
1158: IF (p_interface_flag = 'Y') THEN
1159:
1160: sql_string := sql_string ||
1161: ', WSH_REGIONS_TL_INTERFACE TL ';
1162:
1163: ELSE
1164:
1165: sql_string := sql_string ||

Line 1166: ', WSH_REGIONS_TL TL ';

1162:
1163: ELSE
1164:
1165: sql_string := sql_string ||
1166: ', WSH_REGIONS_TL TL ';
1167:
1168: END IF;
1169:
1170: END IF;

Line 1610: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.ADD_REGION',WSH_DEBUG_SV.C_PROC_LEVEL);

1606: -- Debug Statements
1607: --
1608: IF l_debug_on THEN
1609: WSH_DEBUG_SV.logmsg(l_module_name,nvl(l_region_info.country,l_region_info.country_code)||' '||nvl(l_region_info.state,l_region_info.state_code)||' '||nvl(l_region_info.city,l_region_info.city_code)||' '||l_region_info.postal_code_from);
1610: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.ADD_REGION',WSH_DEBUG_SV.C_PROC_LEVEL);
1611: END IF;
1612: --
1613: Wsh_Regions_Pkg.Add_Region(
1614: p_country_code => l_region_info.country_code,

Line 1613: Wsh_Regions_Pkg.Add_Region(

1609: WSH_DEBUG_SV.logmsg(l_module_name,nvl(l_region_info.country,l_region_info.country_code)||' '||nvl(l_region_info.state,l_region_info.state_code)||' '||nvl(l_region_info.city,l_region_info.city_code)||' '||l_region_info.postal_code_from);
1610: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.ADD_REGION',WSH_DEBUG_SV.C_PROC_LEVEL);
1611: END IF;
1612: --
1613: Wsh_Regions_Pkg.Add_Region(
1614: p_country_code => l_region_info.country_code,
1615: p_country_region_code => l_region_info.country_region_code,
1616: p_state_code => l_region_info.state_code,
1617: p_city_code => l_region_info.city_code,

Line 1699: FROM wsh_regions_interface r, wsh_regions_tl_interface t

1695: SELECT nvl(country, country_code) country,
1696: nvl(state,state_code) state,
1697: nvl(city,city_code) city,
1698: postal_code_from
1699: FROM wsh_regions_interface r, wsh_regions_tl_interface t
1700: WHERE r.region_id = t.region_id and r.creation_date >= l_processing_date
1701: ORDER BY country, state, city, postal_code_from;
1702:
1703: CURSOR get_loc_info (l_start_date DATE, l_end_date DATE) IS

Line 1797: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.MATCH_LOCATION_REGION',WSH_DEBUG_SV.C_PROC_LEVEL);

1793: --
1794: -- Debug Statements
1795: --
1796: IF l_debug_on THEN
1797: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.MATCH_LOCATION_REGION',WSH_DEBUG_SV.C_PROC_LEVEL);
1798: END IF;
1799: --
1800: l_region_id := Wsh_Regions_Search_Pkg.Match_Location_Region (
1801: p_country => loc.territory_short_name,

Line 1800: l_region_id := Wsh_Regions_Search_Pkg.Match_Location_Region (

1796: IF l_debug_on THEN
1797: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.MATCH_LOCATION_REGION',WSH_DEBUG_SV.C_PROC_LEVEL);
1798: END IF;
1799: --
1800: l_region_id := Wsh_Regions_Search_Pkg.Match_Location_Region (
1801: p_country => loc.territory_short_name,
1802: p_country_code => loc.territory_code,
1803: p_state => loc.state,
1804: p_city => loc.city,

Line 1979: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_Child_Requests_Status');

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

Line 2039: WSH_REGIONS_SEARCH_PKG.Process_All_Locations(

2035: IF l_debug_on THEN
2036: WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING PROCESS_ALL_LOCATIONS API with location_type=BOTH' ,WSH_DEBUG_SV.C_PROC_LEVEL);
2037: END IF;
2038:
2039: WSH_REGIONS_SEARCH_PKG.Process_All_Locations(
2040: p_dummy1 => NULL,
2041: p_dummy2 => NULL,
2042: p_mode => p_mode,
2043: p_insert_flag => p_insert_flag,

Line 2056: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Process_All_Locations');

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

Line 2362: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Process_All_Locations');

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

Line 2421: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.refresh_cache');

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

Line 2475: FROM wsh_regions wr,

2471: wr.country_region_code,
2472: wr.state_code,
2473: wr.city_code,
2474: decode(loc.parent_region_flag,'Y','N','N','Y')
2475: FROM wsh_regions wr,
2476: wsh_regions_tl wrt,
2477: wsh_region_locations loc
2478: WHERE wr.region_id = l_region_id AND
2479: loc.location_id = l_location_id AND

Line 2476: wsh_regions_tl wrt,

2472: wr.state_code,
2473: wr.city_code,
2474: decode(loc.parent_region_flag,'Y','N','N','Y')
2475: FROM wsh_regions wr,
2476: wsh_regions_tl wrt,
2477: wsh_region_locations loc
2478: WHERE wr.region_id = l_region_id AND
2479: loc.location_id = l_location_id AND
2480: wr.region_id = loc.region_id AND

Line 2500: FROM wsh_regions r,

2496: null,
2497: null,
2498: null,
2499: 'N'
2500: FROM wsh_regions r,
2501: wsh_regions_tl rt,
2502: wsh_zone_regions z
2503: WHERE r.region_id = z.parent_region_id AND
2504: z.region_id = l_region_id AND

Line 2501: wsh_regions_tl rt,

2497: null,
2498: null,
2499: 'N'
2500: FROM wsh_regions r,
2501: wsh_regions_tl rt,
2502: wsh_zone_regions z
2503: WHERE r.region_id = z.parent_region_id AND
2504: z.region_id = l_region_id AND
2505: r.region_type = 10 AND

Line 2511: FROM wsh_region_locations wrl, wsh_regions_tl wrt,

2507: rt.language = nvl(l_lang_code,rt.language);
2508:
2509: CURSOR Check_Location_Exists(c_location_id IN NUMBER,c_lang_code IN VARCHAR2) IS
2510: SELECT 'exists'
2511: FROM wsh_region_locations wrl, wsh_regions_tl wrt,
2512: wsh_regions wr
2513: WHERE wrl.region_id is not null
2514: AND wrl.location_id = c_location_id
2515: AND wrl.region_id = wrt.region_id

Line 2512: wsh_regions wr

2508:
2509: CURSOR Check_Location_Exists(c_location_id IN NUMBER,c_lang_code IN VARCHAR2) IS
2510: SELECT 'exists'
2511: FROM wsh_region_locations wrl, wsh_regions_tl wrt,
2512: wsh_regions wr
2513: WHERE wrl.region_id is not null
2514: AND wrl.location_id = c_location_id
2515: AND wrl.region_id = wrt.region_id
2516: AND wrt.language = nvl(c_lang_code,wrt.language)

Line 2618: FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr

2614: IF l_exists IS NOT NULL THEN
2615: /*
2616: SELECT wrl.region_id
2617: BULK COLLECT INTO l_rgid_tab
2618: FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
2619: WHERE wrl.location_id = p_location_id
2620: AND wrl.region_id is not null
2621: AND wrl.region_id = wrt.region_id
2622: AND wrt.language = nvl(p_lang_code,wrt.language)

Line 2970: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches');

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

Line 2975: WSH_UTIL_CORE.printmsg('Unexpected error has occured in 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
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:

Line 2999: FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr

2995:
2996:
2997: CURSOR c_get_all_region_id(l_location_id IN NUMBER, l_lang_code IN VARCHAR2) IS
2998: SELECT wrl.region_id
2999: FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
3000: WHERE wrl.location_id = l_location_id
3001: AND wrl.region_id is not null
3002: AND wrl.region_id = wrt.region_id
3003: AND wrt.language = nvl(l_lang_code,wrt.language)

Line 3088: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches');

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

Line 3117: FROM wsh_zone_regions wzr,wsh_regions wr

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
3117: FROM wsh_zone_regions wzr,wsh_regions wr
3118: WHERE wzr.region_id = c_region_id
3119: AND zone_flag = 'Y'
3120: AND wzr.parent_region_id = wr.region_id
3121: AND wr.region_type=10;

Line 3202: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches');

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

Line 3261: FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr

3257: CURSOR c_get_all_regions_loc(c_location_id IN NUMBER, c_lang_code IN VARCHAR2) IS
3258: SELECT wrl.region_id,
3259: wr.region_type,
3260: wr.deconsol_location_id
3261: FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
3262: WHERE wrl.location_id = c_location_id
3263: AND wrl.region_id IS NOT NULL
3264: AND wrl.region_id = wrt.region_id
3265: AND wrt.language = nvl('US',wrt.language)

Line 3275: FROM wsh_zone_regions wzr,wsh_regions wr

3271: SELECT wr.region_id,
3272: --null,
3273: wr.region_type,
3274: wr.deconsol_location_id
3275: FROM wsh_zone_regions wzr,wsh_regions wr
3276: WHERE wzr.region_id = c_region_id
3277: AND zone_flag = 'Y'
3278: AND wzr.parent_region_id = wr.region_id
3279: AND wr.region_type=10

Line 3475: WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.get_all_region_deconsols');

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

Line 3490: -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data

3486:
3487: --
3488: -- PROCEDURE : Check_Region_Info
3489: --
3490: -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data
3491: -- table based on parameters passed to it.
3492: PROCEDURE Check_Region_Info (
3493: p_country IN VARCHAR2,
3494: p_state IN VARCHAR2,

Line 3502: x_region_info OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec)

3498: p_region_type IN NUMBER,
3499: p_search_flag IN VARCHAR2,
3500: p_lang_code IN VARCHAR2,
3501: x_return_status OUT NOCOPY VARCHAR2,
3502: x_region_info OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec)
3503: IS
3504:
3505: CURSOR C1 IS
3506: SELECT REGION_ID

Line 3507: FROM WSH_REGIONS_GLOBAL_DATA

3503: IS
3504:
3505: CURSOR C1 IS
3506: SELECT REGION_ID
3507: FROM WSH_REGIONS_GLOBAL_DATA
3508: WHERE ( city = UPPER(p_city) or p_city is null )
3509: AND ( state = UPPER(p_state) or p_state is null )
3510: AND country = UPPER(p_country)
3511: AND ( p_postal_code_from is null

Line 3519: FROM WSH_REGIONS_GLOBAL_DATA

3515: AND language = p_lang_code;
3516:
3517: CURSOR C2 IS
3518: SELECT REGION_ID
3519: FROM WSH_REGIONS_GLOBAL_DATA
3520: WHERE ( city = UPPER(p_city) )
3521: AND ( nvl(state, UPPER(p_state)) = UPPER(p_state) )
3522: AND country = UPPER(p_country)
3523: AND ( p_postal_code_from is null

Line 3616: -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data

3612:
3613: --
3614: -- PROCEDURE : Check_Region_Info_Code
3615: --
3616: -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data
3617: -- table based on parameters passed to it.
3618: PROCEDURE Check_Region_Info_Code (
3619: p_country IN VARCHAR2,
3620: p_state IN VARCHAR2,

Line 3629: x_region_info OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec)

3625: p_region_type IN NUMBER,
3626: p_search_flag IN VARCHAR2,
3627: p_lang_code IN VARCHAR2,
3628: x_return_status OUT NOCOPY VARCHAR2,
3629: x_region_info OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec)
3630: IS
3631:
3632: CURSOR C1 IS
3633: SELECT REGION_ID

Line 3634: FROM WSH_REGIONS_GLOBAL_DATA

3630: IS
3631:
3632: CURSOR C1 IS
3633: SELECT REGION_ID
3634: FROM WSH_REGIONS_GLOBAL_DATA
3635: WHERE ( city = UPPER(p_city) or p_city is null )
3636: AND ( decode(p_city, null, state, nvl(state, UPPER(p_state))) = UPPER(p_state)
3637: OR ( p_state is null ) )
3638: AND country = UPPER(p_country)

Line 3727: -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data/

3723:
3724: --
3725: -- PROCEDURE : Check_Region_Id_Codes_Only
3726: --
3727: -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data/
3728: -- Wsh_Regions_Global table based on parameters passed to it.
3729:
3730: PROCEDURE Check_Region_Id_Codes_Only (
3731: p_country_code IN VARCHAR2,

Line 3728: -- Wsh_Regions_Global table based on parameters passed to it.

3724: --
3725: -- PROCEDURE : Check_Region_Id_Codes_Only
3726: --
3727: -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data/
3728: -- Wsh_Regions_Global table based on parameters passed to it.
3729:
3730: PROCEDURE Check_Region_Id_Codes_Only (
3731: p_country_code IN VARCHAR2,
3732: p_state_code IN VARCHAR2,

Line 3743: FROM WSH_REGIONS_GLOBAL_DATA

3739: x_region_id OUT NOCOPY NUMBER )
3740: IS
3741: CURSOR C1 IS
3742: SELECT REGION_ID
3743: FROM WSH_REGIONS_GLOBAL_DATA
3744: WHERE ( city_code = UPPER(p_city_code) or p_city_code is null )
3745: AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
3746: OR ( p_state_code is null ) )
3747: AND country_code = UPPER(p_country_code)

Line 3756: FROM WSH_REGIONS_GLOBAL

3752: AND language = nvl(p_language_code, language);
3753:
3754: CURSOR C2 IS
3755: SELECT REGION_ID
3756: FROM WSH_REGIONS_GLOBAL
3757: WHERE ( city_code = UPPER(p_city_code) or p_city_code is null )
3758: AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
3759: OR ( p_state_code is null ) )
3760: AND country_code = UPPER(p_country_code)

Line 3872: END WSH_REGIONS_SEARCH_PKG;

3868:
3869: END;
3870:
3871:
3872: END WSH_REGIONS_SEARCH_PKG;