26: TYPE COUNTRY_CODE_TAB IS TABLE OF WSH_REGIONS_INTERFACE.COUNTRY_CODE%TYPE INDEX BY BINARY_INTEGER;
27: TYPE STATE_CODE_TAB IS TABLE OF WSH_REGIONS_INTERFACE.STATE_CODE%TYPE INDEX BY BINARY_INTEGER;
28: TYPE CITY_CODE_TAB IS TABLE OF WSH_REGIONS_INTERFACE.CITY_CODE%TYPE INDEX BY BINARY_INTEGER;
29:
30: TYPE LANGUAGE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.LANGUAGE%TYPE INDEX BY BINARY_INTEGER;
31: TYPE COUNTRY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.COUNTRY%TYPE INDEX BY BINARY_INTEGER;
32: TYPE STATE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.STATE%TYPE INDEX BY BINARY_INTEGER;
33: TYPE CITY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.CITY%TYPE INDEX BY BINARY_INTEGER;
34: TYPE POSTAL_CODE_FROM_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_FROM%TYPE INDEX BY BINARY_INTEGER;
27: TYPE STATE_CODE_TAB IS TABLE OF WSH_REGIONS_INTERFACE.STATE_CODE%TYPE INDEX BY BINARY_INTEGER;
28: TYPE CITY_CODE_TAB IS TABLE OF WSH_REGIONS_INTERFACE.CITY_CODE%TYPE INDEX BY BINARY_INTEGER;
29:
30: TYPE LANGUAGE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.LANGUAGE%TYPE INDEX BY BINARY_INTEGER;
31: TYPE COUNTRY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.COUNTRY%TYPE INDEX BY BINARY_INTEGER;
32: TYPE STATE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.STATE%TYPE INDEX BY BINARY_INTEGER;
33: TYPE CITY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.CITY%TYPE INDEX BY BINARY_INTEGER;
34: TYPE POSTAL_CODE_FROM_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_FROM%TYPE INDEX BY BINARY_INTEGER;
35: TYPE POSTAL_CODE_TO_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_TO%TYPE INDEX BY BINARY_INTEGER;
28: TYPE CITY_CODE_TAB IS TABLE OF WSH_REGIONS_INTERFACE.CITY_CODE%TYPE INDEX BY BINARY_INTEGER;
29:
30: TYPE LANGUAGE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.LANGUAGE%TYPE INDEX BY BINARY_INTEGER;
31: TYPE COUNTRY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.COUNTRY%TYPE INDEX BY BINARY_INTEGER;
32: TYPE STATE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.STATE%TYPE INDEX BY BINARY_INTEGER;
33: TYPE CITY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.CITY%TYPE INDEX BY BINARY_INTEGER;
34: TYPE POSTAL_CODE_FROM_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_FROM%TYPE INDEX BY BINARY_INTEGER;
35: TYPE POSTAL_CODE_TO_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_TO%TYPE INDEX BY BINARY_INTEGER;
36:
29:
30: TYPE LANGUAGE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.LANGUAGE%TYPE INDEX BY BINARY_INTEGER;
31: TYPE COUNTRY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.COUNTRY%TYPE INDEX BY BINARY_INTEGER;
32: TYPE STATE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.STATE%TYPE INDEX BY BINARY_INTEGER;
33: TYPE CITY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.CITY%TYPE INDEX BY BINARY_INTEGER;
34: TYPE POSTAL_CODE_FROM_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_FROM%TYPE INDEX BY BINARY_INTEGER;
35: TYPE POSTAL_CODE_TO_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_TO%TYPE INDEX BY BINARY_INTEGER;
36:
37:
30: TYPE LANGUAGE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.LANGUAGE%TYPE INDEX BY BINARY_INTEGER;
31: TYPE COUNTRY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.COUNTRY%TYPE INDEX BY BINARY_INTEGER;
32: TYPE STATE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.STATE%TYPE INDEX BY BINARY_INTEGER;
33: TYPE CITY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.CITY%TYPE INDEX BY BINARY_INTEGER;
34: TYPE POSTAL_CODE_FROM_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_FROM%TYPE INDEX BY BINARY_INTEGER;
35: TYPE POSTAL_CODE_TO_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_TO%TYPE INDEX BY BINARY_INTEGER;
36:
37:
38: --_______________________________________________________________________________________--
31: TYPE COUNTRY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.COUNTRY%TYPE INDEX BY BINARY_INTEGER;
32: TYPE STATE_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.STATE%TYPE INDEX BY BINARY_INTEGER;
33: TYPE CITY_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.CITY%TYPE INDEX BY BINARY_INTEGER;
34: TYPE POSTAL_CODE_FROM_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_FROM%TYPE INDEX BY BINARY_INTEGER;
35: TYPE POSTAL_CODE_TO_TAB IS TABLE OF WSH_REGIONS_TL_INTERFACE.POSTAL_CODE_TO%TYPE INDEX BY BINARY_INTEGER;
36:
37:
38: --_______________________________________________________________________________________--
39: --
71: --
72: -- FUNCTION GET_ZONE_ID
73: --
74: -- Purpose
75: -- Get the region_id of a zone from the wsh_regions_tl table.
76: --
77: -- IN Parameters
78: -- 1. p_zone_name: The name of the zone.
79: -- 2. p_exact_match: A boolean which specifies whether the match on zone_name
87: FUNCTION GET_ZONE_ID(p_zone_name IN VARCHAR2 ) RETURN NUMBER IS
88:
89: CURSOR GET_ZONE_ID(p_zone_name VARCHAR2) IS
90: SELECT region_id
91: FROM wsh_regions_tl
92: WHERE zone = p_zone_name
93: --BUG 6067174 : Zone ID returned should be specific to the instance language.
94: and language = userenv('lang');
95:
184: G_USER_ID,
185: SYSDATE,
186: G_USER_ID);
187:
188: INSERT INTO WSH_REGIONS_TL(
189: REGION_ID,
190: LANGUAGE,
191: ZONE,
192: LAST_UPDATE_DATE,
793: NULL);
794:
795: FORALL i in L_REGION_ID.FIRST..L_REGION_ID.LAST
796:
797: INSERT INTO WSH_REGIONS_TL_INTERFACE(
798: LANGUAGE,
799: REGION_ID,
800: COUNTRY,
801: STATE,