[Home] [Help]
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,
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,
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;
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 ||
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 ||
1162:
1163: ELSE
1164:
1165: sql_string := sql_string ||
1166: ', WSH_REGIONS_TL TL ';
1167:
1168: END IF;
1169:
1170: END IF;
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
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
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
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
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)
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)
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)