[Home] [Help]
89: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
90: --
91: IF l_debug_on IS NULL
92: THEN
93: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
94: END IF;
95:
96: l_return_status := FND_API.G_RET_STS_SUCCESS;
97: --
98: --
99: -- Debug Statements
100: --
101: IF l_debug_on THEN
102: WSH_DEBUG_SV.push(l_module_name);
103: --
104: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
105: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
106: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
100: --
101: IF l_debug_on THEN
102: WSH_DEBUG_SV.push(l_module_name);
103: --
104: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
105: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
106: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
107: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
108: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
101: IF l_debug_on THEN
102: WSH_DEBUG_SV.push(l_module_name);
103: --
104: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
105: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
106: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
107: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
108: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
109: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
102: WSH_DEBUG_SV.push(l_module_name);
103: --
104: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
105: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
106: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
107: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
108: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
109: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
110: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
103: --
104: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
105: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
106: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
107: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
108: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
109: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
110: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
111: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
104: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
105: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
106: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
107: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
108: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
109: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
110: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
111: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
112: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
105: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
106: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
107: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
108: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
109: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
110: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
111: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
112: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
113: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
106: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
107: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
108: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
109: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
110: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
111: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
112: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
113: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
114: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
107: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
108: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
109: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
110: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
111: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
112: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
113: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
114: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
115: END IF;
108: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
109: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
110: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
111: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
112: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
113: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
114: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
115: END IF;
116: l_key := TO_CHAR(P_REGION_TYPE, g_int_mask) ||'-'||
109: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
110: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
111: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
112: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
113: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
114: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
115: END IF;
116: l_key := TO_CHAR(P_REGION_TYPE, g_int_mask) ||'-'||
117: LPAD(P_COUNTRY, g_lpad_length, g_lpad_char) ||'-'||
110: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
111: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
112: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
113: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
114: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
115: END IF;
116: l_key := TO_CHAR(P_REGION_TYPE, g_int_mask) ||'-'||
117: LPAD(P_COUNTRY, g_lpad_length, g_lpad_char) ||'-'||
118: LPAD(P_COUNTRY_CODE, g_lpad_code_length, g_lpad_char) ||'-'||
127:
128: x_key := l_key;
129:
130: IF l_debug_on THEN
131: WSH_DEBUG_SV.pop(l_module_name);
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);
141: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
142: END IF;
143: END get_key;
144:
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');
142: END IF;
143: END get_key;
144:
145: PROCEDURE Get_Region_Info (
169: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
170: --
171: IF l_debug_on IS NULL
172: THEN
173: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
174: END IF;
175: --
176: --
177: -- Debug Statements
176: --
177: -- Debug Statements
178: --
179: IF l_debug_on THEN
180: WSH_DEBUG_SV.push(l_module_name);
181: --
182: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
183: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
184: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
178: --
179: IF l_debug_on THEN
180: WSH_DEBUG_SV.push(l_module_name);
181: --
182: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
183: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
184: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
185: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
186: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
179: IF l_debug_on THEN
180: WSH_DEBUG_SV.push(l_module_name);
181: --
182: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
183: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
184: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
185: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
186: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
187: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
180: WSH_DEBUG_SV.push(l_module_name);
181: --
182: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
183: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
184: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
185: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
186: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
187: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
188: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
181: --
182: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
183: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
184: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
185: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
186: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
187: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
188: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
189: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
182: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
183: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
184: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
185: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
186: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
187: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
188: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
189: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
190: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
183: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
184: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
185: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
186: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
187: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
188: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
189: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
190: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
191: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
184: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
185: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
186: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
187: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
188: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
189: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
190: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
191: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
192: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
185: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
186: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
187: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
188: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
189: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
190: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
191: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
192: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
193: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
186: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
187: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
188: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
189: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
190: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
191: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
192: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
193: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
194: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
187: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
188: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
189: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
190: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
191: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
192: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
193: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
194: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
195: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
188: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
189: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
190: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
191: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
192: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
193: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
194: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
195: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
196: END IF;
189: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
190: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
191: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
192: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
193: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
194: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
195: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
196: END IF;
197: --
190: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
191: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
192: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
193: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
194: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
195: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
196: END IF;
197: --
198:
191: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
192: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
193: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
194: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
195: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
196: END IF;
197: --
198:
199: Get_Region_Info (
217: --
218: -- Debug Statements
219: --
220: IF l_debug_on THEN
221: WSH_DEBUG_SV.pop(l_module_name);
222: END IF;
223: --
224: END Get_Region_Info;
225:
285: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
286: --
287: IF l_debug_on IS NULL
288: THEN
289: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
290: END IF;
291: --
292: --
293: -- Debug Statements
292: --
293: -- Debug Statements
294: --
295: IF l_debug_on THEN
296: WSH_DEBUG_SV.push(l_module_name);
297: --
298: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
299: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
300: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
294: --
295: IF l_debug_on THEN
296: WSH_DEBUG_SV.push(l_module_name);
297: --
298: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
299: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
300: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
301: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
302: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
295: IF l_debug_on THEN
296: WSH_DEBUG_SV.push(l_module_name);
297: --
298: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
299: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
300: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
301: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
302: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
303: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
296: WSH_DEBUG_SV.push(l_module_name);
297: --
298: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
299: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
300: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
301: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
302: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
303: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
304: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
297: --
298: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
299: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
300: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
301: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
302: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
303: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
304: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
305: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
298: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
299: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
300: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
301: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
302: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
303: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
304: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
305: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
306: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
299: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
300: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
301: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
302: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
303: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
304: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
305: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
306: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
307: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
300: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
301: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
302: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
303: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
304: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
305: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
306: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
307: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
308: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
301: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
302: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
303: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
304: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
305: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
306: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
307: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
308: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
309: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
302: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
303: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
304: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
305: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
306: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
307: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
308: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
309: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
310: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
303: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
304: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
305: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
306: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
307: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
308: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
309: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
310: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
311: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
304: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
305: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
306: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
307: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
308: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
309: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
310: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
311: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
312: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
305: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
306: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
307: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
308: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
309: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
310: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
311: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
312: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
313: END IF;
306: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
307: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
308: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
309: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
310: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
311: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
312: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
313: END IF;
314: --
307: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
308: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
309: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
310: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
311: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
312: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
313: END IF;
314: --
315: get_key(
308: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
309: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
310: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
311: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
312: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
313: END IF;
314: --
315: get_key(
316: p_country => p_country,
327: x_key => l_key,
328: x_return_status => l_return_status);
329:
330: IF l_debug_on THEN
331: WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from get_key : '|| l_return_status);
332: END IF;
333:
334: IF g_regions_info_tab.EXISTS(l_key) THEN
335: x_regions := g_regions_info_tab(l_key);
333:
334: IF g_regions_info_tab.EXISTS(l_key) THEN
335: x_regions := g_regions_info_tab(l_key);
336: IF l_debug_on THEN
337: wsh_debug_sv.log(l_module_name, 'used cache for key', l_key);
338: wsh_debug_sv.log(l_module_name, 'x_regions.count', x_regions.count);
339: END IF;
340: ELSE
341:
334: IF g_regions_info_tab.EXISTS(l_key) THEN
335: x_regions := g_regions_info_tab(l_key);
336: IF l_debug_on THEN
337: wsh_debug_sv.log(l_module_name, 'used cache for key', l_key);
338: wsh_debug_sv.log(l_module_name, 'x_regions.count', x_regions.count);
339: END IF;
340: ELSE
341:
342: IF (p_region_type IS NULL) THEN
376: --First Iteration : State and City on Location are matched with State and City of Region
377: l_state := p_state;
378: l_city := p_city;
379: if l_debug_on then
380: WSH_DEBUG_SV.logmsg(l_module_name, 'Iteration 1');
381: end if;
382: ELSIF i=2 THEN
383: --Second Iteration : State and City on Location are matched with State and City_Code of Region
384: -- based on length of city.
382: ELSIF i=2 THEN
383: --Second Iteration : State and City on Location are matched with State and City_Code of Region
384: -- based on length of city.
385: if l_debug_on then
386: WSH_DEBUG_SV.logmsg(l_module_name, 'Iteration 2');
387: end if;
388: if length(p_city ) <=2 OR (length(p_city)=3 and UPPER(p_city) = p_city) THEN
389: l_state := p_state;
390: l_city_code := p_city;
390: l_city_code := p_city;
391: ELSE
392: --Move to next iteration if Length of city is not <=3
393: if l_debug_on then
394: WSH_DEBUG_SV.logmsg(l_module_name, 'Iteration 2 : Continue to Iteration 3 ');
395: end if;
396: CONTINUE;
397: END IF;
398: ELSIF i=3 THEN
398: ELSIF i=3 THEN
399: --Third Iteration : State and City on Location are matched with State_Code and City of Region
400: -- based on length of State.
401: if l_debug_on then
402: WSH_DEBUG_SV.logmsg(l_module_name, 'Iteration 3');
403: end if;
404: if length(p_state ) <=2 THEN
405: l_state_code := p_state;
406: l_city := p_city;
406: l_city := p_city;
407: ELSE
408: --Exit if Length of State is not <3
409: if l_debug_on then
410: WSH_DEBUG_SV.logmsg(l_module_name, 'Iteration 3:Exit out of loop as length of state is not < 3 ');
411: end if;
412: EXIT;
413: END IF;
414: ELSE
414: ELSE
415: --Fourth Iteration : State and City on Location are matched with State_Code and City_code of Region
416: -- based on length of State and City.
417: if l_debug_on then
418: WSH_DEBUG_SV.logmsg(l_module_name, 'Iteration 4');
419: end if;
420: if length(p_state ) <=2 AND (length(p_city ) <=2 OR (length(p_city)=3 and UPPER(p_city) = p_city))THEN
421: l_state_code := p_state;
422: l_city_code := p_city;
422: l_city_code := p_city;
423: ELSE
424: --Exit out of loop if Length of State is not <3 or length of city is not <=3
425: if l_debug_on then
426: WSH_DEBUG_SV.logmsg(l_module_name, 'Iteration 4 : Exit out of Loop');
427: end if;
428: EXIT;
429: END IF;
430: END IF;
642:
643: -- Debug Statements
644: --
645: IF l_debug_on THEN
646: WSH_DEBUG_SV.logmsg(l_module_name, 'Get_Region_Id:: sql_string_length: ' || length(sql_string));
647: for i IN 1..length(sql_string) loop
648: if mod(i,100) = 0 then
649: WSH_DEBUG_SV.logmsg(l_module_name, substr(sql_string,i, 100));
650: end if;
645: IF l_debug_on THEN
646: WSH_DEBUG_SV.logmsg(l_module_name, 'Get_Region_Id:: sql_string_length: ' || length(sql_string));
647: for i IN 1..length(sql_string) loop
648: if mod(i,100) = 0 then
649: WSH_DEBUG_SV.logmsg(l_module_name, substr(sql_string,i, 100));
650: end if;
651: end loop;
652: END IF;
653: IF (cnt = 0) THEN
835: LOOP
836: FETCH l_region_cur INTO l_region_info;
837: EXIT WHEN l_region_cur%NOTFOUND;
838: IF l_debug_on THEN
839: WSH_DEBUG_SV.logmsg(l_module_name, ' fetching region info of '||l_region_info.region_id);
840: WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt);
841: END IF;
842: x_regions(cnt) := l_region_info;
843: cnt :=cnt + 1;
836: FETCH l_region_cur INTO l_region_info;
837: EXIT WHEN l_region_cur%NOTFOUND;
838: IF l_debug_on THEN
839: WSH_DEBUG_SV.logmsg(l_module_name, ' fetching region info of '||l_region_info.region_id);
840: WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt);
841: END IF;
842: x_regions(cnt) := l_region_info;
843: cnt :=cnt + 1;
844:
850: x_regions(0) := l_region_info;
851: END IF;
852: IF l_region_info.region_id <> -1 THEN
853: IF l_debug_on THEN
854: WSH_DEBUG_SV.logmsg(l_module_name, ' Found Region ,Exiting out of Loop in iteration '||i);
855: END IF;
856: EXIT;
857: END IF;
858:
856: EXIT;
857: END IF;
858:
859: IF l_debug_on THEN
860: WSH_DEBUG_SV.logmsg(l_module_name, ' Region Could not be found in iteration'||i);
861: END IF;
862: --}End of 4 Iteration Loop for checking State/Statecode and City/CityCode combination.
863: END LOOP;
864: g_regions_info_tab(l_key) := x_regions;
868: --
869: -- Debug Statements
870: --
871: IF l_debug_on THEN
872: WSH_DEBUG_SV.pop(l_module_name);
873: END IF;
874: --
875: EXCEPTION
876: WHEN OTHERS THEN
880: --
881: -- Debug Statements
882: --
883: IF l_debug_on THEN
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');
881: -- Debug Statements
882: --
883: IF l_debug_on THEN
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);
975: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
976: --
977: IF l_debug_on IS NULL
978: THEN
979: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
980: END IF;
981: --
982: --
983: -- Debug Statements
982: --
983: -- Debug Statements
984: --
985: IF l_debug_on THEN
986: WSH_DEBUG_SV.push(l_module_name);
987: --
988: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
989: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
990: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
984: --
985: IF l_debug_on THEN
986: WSH_DEBUG_SV.push(l_module_name);
987: --
988: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
989: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
990: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
991: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
992: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
985: IF l_debug_on THEN
986: WSH_DEBUG_SV.push(l_module_name);
987: --
988: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
989: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
990: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
991: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
992: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
993: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
986: WSH_DEBUG_SV.push(l_module_name);
987: --
988: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
989: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
990: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
991: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
992: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
993: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
994: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
987: --
988: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
989: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
990: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
991: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
992: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
993: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
994: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
995: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
988: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
989: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
990: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
991: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
992: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
993: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
994: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
995: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
996: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
989: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
990: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
991: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
992: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
993: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
994: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
995: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
996: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
997: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
990: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
991: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
992: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
993: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
994: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
995: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
996: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
997: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
998: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
991: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
992: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
993: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
994: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
995: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
996: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
997: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
998: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
999: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
992: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
993: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
994: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
995: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
996: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
997: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
998: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
999: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1000: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
993: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
994: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
995: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
996: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
997: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
998: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
999: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1000: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1001: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
994: WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
995: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
996: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
997: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
998: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
999: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1000: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1001: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1002: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
995: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
996: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
997: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
998: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
999: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1000: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1001: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1002: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
1003: WSH_DEBUG_SV.log(l_module_name,'P_RECURSIVELY_FLAG',P_RECURSIVELY_FLAG);
996: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
997: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
998: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
999: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1000: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1001: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1002: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
1003: WSH_DEBUG_SV.log(l_module_name,'P_RECURSIVELY_FLAG',P_RECURSIVELY_FLAG);
1004: END IF;
997: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
998: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
999: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1000: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1001: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1002: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
1003: WSH_DEBUG_SV.log(l_module_name,'P_RECURSIVELY_FLAG',P_RECURSIVELY_FLAG);
1004: END IF;
1005: --
998: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
999: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1000: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1001: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1002: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
1003: WSH_DEBUG_SV.log(l_module_name,'P_RECURSIVELY_FLAG',P_RECURSIVELY_FLAG);
1004: END IF;
1005: --
1006: Get_Region_Info (
999: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1000: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1001: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1002: WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
1003: WSH_DEBUG_SV.log(l_module_name,'P_RECURSIVELY_FLAG',P_RECURSIVELY_FLAG);
1004: END IF;
1005: --
1006: Get_Region_Info (
1007: p_country => p_country,
1060: --
1061: -- Debug Statements
1062: --
1063: IF l_debug_on THEN
1064: WSH_DEBUG_SV.pop(l_module_name);
1065: END IF;
1066: --
1067: END Get_Region_Info;
1068:
1109: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1110: --
1111: IF l_debug_on IS NULL
1112: THEN
1113: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1114: END IF;
1115: --
1116: --
1117: -- Debug Statements
1116: --
1117: -- Debug Statements
1118: --
1119: IF l_debug_on THEN
1120: WSH_DEBUG_SV.push(l_module_name);
1121: --
1122: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1123: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1124: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1118: --
1119: IF l_debug_on THEN
1120: WSH_DEBUG_SV.push(l_module_name);
1121: --
1122: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1123: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1124: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1125: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1126: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1119: IF l_debug_on THEN
1120: WSH_DEBUG_SV.push(l_module_name);
1121: --
1122: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1123: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1124: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1125: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1126: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1127: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1120: WSH_DEBUG_SV.push(l_module_name);
1121: --
1122: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1123: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1124: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1125: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1126: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1127: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1128: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1121: --
1122: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1123: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1124: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1125: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1126: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1127: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1128: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1129: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1122: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1123: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1124: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1125: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1126: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1127: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1128: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1129: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1130: WSH_DEBUG_SV.log(l_module_name,'P_LANGUAGE_CODE',P_LANGUAGE_CODE);
1123: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1124: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1125: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1126: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1127: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1128: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1129: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1130: WSH_DEBUG_SV.log(l_module_name,'P_LANGUAGE_CODE',P_LANGUAGE_CODE);
1131: END IF;
1124: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1125: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1126: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1127: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1128: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1129: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1130: WSH_DEBUG_SV.log(l_module_name,'P_LANGUAGE_CODE',P_LANGUAGE_CODE);
1131: END IF;
1132: --
1125: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1126: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1127: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1128: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1129: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1130: WSH_DEBUG_SV.log(l_module_name,'P_LANGUAGE_CODE',P_LANGUAGE_CODE);
1131: END IF;
1132: --
1133: IF (p_country_code IS NULL AND p_state_code IS NULL AND p_city_code IS NULL ) THEN
1126: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1127: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1128: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1129: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1130: WSH_DEBUG_SV.log(l_module_name,'P_LANGUAGE_CODE',P_LANGUAGE_CODE);
1131: END IF;
1132: --
1133: IF (p_country_code IS NULL AND p_state_code IS NULL AND p_city_code IS NULL ) THEN
1134: l_region_id := -1;
1135: --
1136: -- Debug Statements
1137: --
1138: IF l_debug_on THEN
1139: WSH_DEBUG_SV.pop(l_module_name);
1140: END IF;
1141: --
1142: return;
1143: END IF;
1247:
1248: -- Debug Statements
1249: --
1250: IF l_debug_on THEN
1251: WSH_DEBUG_SV.logmsg(l_module_name, 'Get_Region_Id:: sql_string_length: ' || length(sql_string));
1252: WSH_DEBUG_SV.logmsg(l_module_name, substr(sql_string,101, 200));
1253: END IF;
1254:
1255: IF (cnt = 0) THEN
1248: -- Debug Statements
1249: --
1250: IF l_debug_on THEN
1251: WSH_DEBUG_SV.logmsg(l_module_name, 'Get_Region_Id:: sql_string_length: ' || length(sql_string));
1252: WSH_DEBUG_SV.logmsg(l_module_name, substr(sql_string,101, 200));
1253: END IF;
1254:
1255: IF (cnt = 0) THEN
1256: EXECUTE IMMEDIATE sql_string
1310: --
1311: -- Debug Statements
1312: --
1313: IF l_debug_on THEN
1314: WSH_DEBUG_SV.logmsg(l_module_name, 'sql_string:: ' || sql_string);
1315: WSH_DEBUG_SV.logmsg(l_module_name, 'region_id = ' || l_region_id);
1316: END IF;
1317:
1318: x_region_id := l_region_id;
1311: -- Debug Statements
1312: --
1313: IF l_debug_on THEN
1314: WSH_DEBUG_SV.logmsg(l_module_name, 'sql_string:: ' || sql_string);
1315: WSH_DEBUG_SV.logmsg(l_module_name, 'region_id = ' || l_region_id);
1316: END IF;
1317:
1318: x_region_id := l_region_id;
1319:
1320: --
1321: -- Debug Statements
1322: --
1323: IF l_debug_on THEN
1324: WSH_DEBUG_SV.pop(l_module_name);
1325: END IF;
1326: --
1327: EXCEPTION
1328: WHEN OTHERS THEN
1331: --
1332: -- Debug Statements
1333: --
1334: IF l_debug_on THEN
1335: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1336: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1337: END IF;
1338: --
1339: END Get_Region_Id_Codes_Only;
1332: -- Debug Statements
1333: --
1334: IF l_debug_on THEN
1335: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1336: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1337: END IF;
1338: --
1339: END Get_Region_Id_Codes_Only;
1340:
1379: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1380: --
1381: IF l_debug_on IS NULL
1382: THEN
1383: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1384: END IF;
1385: --
1386: --
1387: -- Debug Statements
1386: --
1387: -- Debug Statements
1388: --
1389: IF l_debug_on THEN
1390: WSH_DEBUG_SV.push(l_module_name);
1391: --
1392: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1393: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1394: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1388: --
1389: IF l_debug_on THEN
1390: WSH_DEBUG_SV.push(l_module_name);
1391: --
1392: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1393: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1394: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1395: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1396: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1389: IF l_debug_on THEN
1390: WSH_DEBUG_SV.push(l_module_name);
1391: --
1392: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1393: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1394: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1395: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1396: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1397: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1390: WSH_DEBUG_SV.push(l_module_name);
1391: --
1392: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1393: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1394: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1395: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1396: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1397: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1398: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1391: --
1392: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1393: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1394: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1395: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1396: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1397: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1398: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1399: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1392: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1393: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1394: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1395: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1396: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1397: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1398: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1399: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1400: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
1393: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1394: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1395: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1396: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1397: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1398: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1399: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1400: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
1401: END IF;
1394: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1395: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1396: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1397: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1398: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1399: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1400: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
1401: END IF;
1402: --
1395: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1396: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1397: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1398: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1399: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1400: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
1401: END IF;
1402: --
1403: IF ((p_country_code IS NULL AND p_state_code IS NULL AND p_city_code IS NULL ) OR ( p_region_type = 2 AND p_city_code IS NULL) OR (p_region_type=1 and p_state_code IS NULL) OR (p_region_type = 0 AND p_country_code IS NULL)) THEN
1396: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1397: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1398: WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
1399: WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1400: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
1401: END IF;
1402: --
1403: IF ((p_country_code IS NULL AND p_state_code IS NULL AND p_city_code IS NULL ) OR ( p_region_type = 2 AND p_city_code IS NULL) OR (p_region_type=1 and p_state_code IS NULL) OR (p_region_type = 0 AND p_country_code IS NULL)) THEN
1404: l_region_id := -1;
1405: --
1406: -- Debug Statements
1407: --
1408: IF l_debug_on THEN
1409: WSH_DEBUG_SV.pop(l_module_name);
1410: END IF;
1411: --
1412: return;
1413: END IF;
1428: --
1429: -- Debug Statements
1430: --
1431: IF l_debug_on THEN
1432: WSH_DEBUG_SV.logmsg(l_module_name, 'getting non-tl id '||x_region_id_non_tl);
1433: WSH_DEBUG_SV.logmsg(l_module_name, 'country code '||p_country_code);
1434: END IF;
1435:
1436: Get_Region_Id_Codes_Only (
1429: -- Debug Statements
1430: --
1431: IF l_debug_on THEN
1432: WSH_DEBUG_SV.logmsg(l_module_name, 'getting non-tl id '||x_region_id_non_tl);
1433: WSH_DEBUG_SV.logmsg(l_module_name, 'country code '||p_country_code);
1434: END IF;
1435:
1436: Get_Region_Id_Codes_Only (
1437: p_country_code => p_country_code,
1447: --
1448: -- Debug Statements
1449: --
1450: IF l_debug_on THEN
1451: WSH_DEBUG_SV.logmsg(l_module_name, 'got non-tl id '||x_region_id_non_tl);
1452: WSH_DEBUG_SV.logmsg(l_module_name, 'for region type '||l_region_type);
1453: END IF;
1454:
1455: IF (x_region_id_non_tl = -1) THEN
1448: -- Debug Statements
1449: --
1450: IF l_debug_on THEN
1451: WSH_DEBUG_SV.logmsg(l_module_name, 'got non-tl id '||x_region_id_non_tl);
1452: WSH_DEBUG_SV.logmsg(l_module_name, 'for region type '||l_region_type);
1453: END IF;
1454:
1455: IF (x_region_id_non_tl = -1) THEN
1456: --
1456: --
1457: -- Debug Statements
1458: --
1459: IF l_debug_on THEN
1460: WSH_DEBUG_SV.pop(l_module_name);
1461: END IF;
1462: --
1463: return;
1464: END IF;
1478: --
1479: -- Debug Statements
1480: --
1481: IF l_debug_on THEN
1482: WSH_DEBUG_SV.logmsg(l_module_name, 'checking tl id '||x_region_id_with_tl);
1483: WSH_DEBUG_SV.pop(l_module_name);
1484: END IF;
1485: --
1486: END Get_Region_Id_Codes_Only;
1479: -- Debug Statements
1480: --
1481: IF l_debug_on THEN
1482: WSH_DEBUG_SV.logmsg(l_module_name, 'checking tl id '||x_region_id_with_tl);
1483: WSH_DEBUG_SV.pop(l_module_name);
1484: END IF;
1485: --
1486: END Get_Region_Id_Codes_Only;
1487:
1518: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1519: --
1520: IF l_debug_on IS NULL
1521: THEN
1522: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1523: END IF;
1524: --
1525: --
1526: -- Debug Statements
1525: --
1526: -- Debug Statements
1527: --
1528: IF l_debug_on THEN
1529: WSH_DEBUG_SV.push(l_module_name);
1530: --
1531: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1532: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1533: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1527: --
1528: IF l_debug_on THEN
1529: WSH_DEBUG_SV.push(l_module_name);
1530: --
1531: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1532: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1533: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1534: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1535: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1528: IF l_debug_on THEN
1529: WSH_DEBUG_SV.push(l_module_name);
1530: --
1531: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1532: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1533: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1534: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1535: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1536: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
1529: WSH_DEBUG_SV.push(l_module_name);
1530: --
1531: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1532: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1533: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1534: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1535: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1536: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
1537: END IF;
1530: --
1531: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1532: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1533: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1534: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1535: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1536: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
1537: END IF;
1538: --
1531: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1532: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1533: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1534: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1535: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1536: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
1537: END IF;
1538: --
1539: l_region_info.country_code := p_country_code;
1532: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1533: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1534: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1535: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1536: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
1537: END IF;
1538: --
1539: l_region_info.country_code := p_country_code;
1540: l_region_info.country := p_country;
1605: --
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(
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,
1644: END IF;
1645:
1646: IF (l_region_rec1.region_id = -1 AND p_insert_flag <> 'Y') THEN
1647: IF l_debug_on THEN
1648: wsh_debug_sv.logmsg(l_module_name,nvl(l_region_info.country,l_region_info.country_code)
1649: ||' '
1650: ||nvl(l_region_info.state,l_region_info.state_code)
1651: ||' '
1652: ||nvl(l_region_info.city,l_region_info.city_code)
1664: --
1665: -- Debug Statements
1666: --
1667: IF l_debug_on THEN
1668: WSH_DEBUG_SV.pop(l_module_name);
1669: END IF;
1670: --
1671: RETURN l_region_rec1.region_id;
1672:
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;
1745: END IF;
1746:
1747: --bug 4775798
1748: --changed WSH_UTIL_CORE.println to WSH_DEBUG_SV.log
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);
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);
1753: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
1754: END IF;
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);
1753: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
1754: END IF;
1755:
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);
1753: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
1754: END IF;
1755:
1756: IF p_location_type IS NULL THEN
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);
1753: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
1754: END IF;
1755:
1756: IF p_location_type IS NULL THEN
1757: l_location_type := 'BOTH';
1762: l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
1763: l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS') +1;
1764:
1765: IF l_debug_on THEN
1766: WSH_DEBUG_SV.log(l_module_name,'l_START_DATE',l_START_DATE);
1767: WSH_DEBUG_SV.log(l_module_name,'l_END_DATE',l_END_DATE);
1768: END IF;
1769:
1770: IF p_mode = 'CREATE' THEN
1763: l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS') +1;
1764:
1765: IF l_debug_on THEN
1766: WSH_DEBUG_SV.log(l_module_name,'l_START_DATE',l_START_DATE);
1767: WSH_DEBUG_SV.log(l_module_name,'l_END_DATE',l_END_DATE);
1768: END IF;
1769:
1770: IF p_mode = 'CREATE' THEN
1771:
1770: IF p_mode = 'CREATE' THEN
1771:
1772: l_processing_date := sysdate;
1773: IF l_debug_on THEN
1774: wsh_debug_sv.logmsg(l_module_name,'Processing locations. Following locations could not be mapped to existing regions.');
1775: END IF;
1776:
1777: IF (p_insert_flag = 'Y') THEN
1778: IF l_debug_on THEN
1775: END IF;
1776:
1777: IF (p_insert_flag = 'Y') THEN
1778: IF l_debug_on THEN
1779: wsh_debug_sv.logmsg(l_module_name,'These have been inserted in the regions interface tables');
1780: END IF;
1781:
1782: ELSE
1783: IF l_debug_on THEN
1780: END IF;
1781:
1782: ELSE
1783: IF l_debug_on THEN
1784: wsh_debug_sv.logmsg(l_module_name,'---------------------------------------------------------------');
1785: wsh_debug_sv.logmsg(l_module_name,'Country State City Postal Code');
1786: wsh_debug_sv.logmsg(l_module_name,'------- ----- ---- ------ ----');
1787: END IF;
1788:
1781:
1782: ELSE
1783: IF l_debug_on THEN
1784: wsh_debug_sv.logmsg(l_module_name,'---------------------------------------------------------------');
1785: wsh_debug_sv.logmsg(l_module_name,'Country State City Postal Code');
1786: wsh_debug_sv.logmsg(l_module_name,'------- ----- ---- ------ ----');
1787: END IF;
1788:
1789: END IF;
1782: ELSE
1783: IF l_debug_on THEN
1784: wsh_debug_sv.logmsg(l_module_name,'---------------------------------------------------------------');
1785: wsh_debug_sv.logmsg(l_module_name,'Country State City Postal Code');
1786: wsh_debug_sv.logmsg(l_module_name,'------- ----- ---- ------ ----');
1787: END IF;
1788:
1789: END IF;
1790:
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,
1812: END LOOP;
1813:
1814: IF (p_insert_flag = 'Y') THEN
1815: IF l_debug_on THEN
1816: wsh_debug_sv.logmsg(l_module_name,'Total number of interfaced regions '||l_count);
1817:
1818: wsh_debug_sv.logmsg(l_module_name,'---------------------------------------------------------------');
1819: wsh_debug_sv.logmsg(l_module_name,'Country State City Postal Code');
1820: wsh_debug_sv.logmsg(l_module_name,'------- ----- ---- ------ ----');
1814: IF (p_insert_flag = 'Y') THEN
1815: IF l_debug_on THEN
1816: wsh_debug_sv.logmsg(l_module_name,'Total number of interfaced regions '||l_count);
1817:
1818: wsh_debug_sv.logmsg(l_module_name,'---------------------------------------------------------------');
1819: wsh_debug_sv.logmsg(l_module_name,'Country State City Postal Code');
1820: wsh_debug_sv.logmsg(l_module_name,'------- ----- ---- ------ ----');
1821: FOR loc IN get_region_info(l_processing_date) LOOP
1822: wsh_debug_sv.logmsg(l_module_name,loc.country||' '||loc.state||' '||loc.city||' '||loc.postal_code_from);
1815: IF l_debug_on THEN
1816: wsh_debug_sv.logmsg(l_module_name,'Total number of interfaced regions '||l_count);
1817:
1818: wsh_debug_sv.logmsg(l_module_name,'---------------------------------------------------------------');
1819: wsh_debug_sv.logmsg(l_module_name,'Country State City Postal Code');
1820: wsh_debug_sv.logmsg(l_module_name,'------- ----- ---- ------ ----');
1821: FOR loc IN get_region_info(l_processing_date) LOOP
1822: wsh_debug_sv.logmsg(l_module_name,loc.country||' '||loc.state||' '||loc.city||' '||loc.postal_code_from);
1823: END LOOP;
1816: wsh_debug_sv.logmsg(l_module_name,'Total number of interfaced regions '||l_count);
1817:
1818: wsh_debug_sv.logmsg(l_module_name,'---------------------------------------------------------------');
1819: wsh_debug_sv.logmsg(l_module_name,'Country State City Postal Code');
1820: wsh_debug_sv.logmsg(l_module_name,'------- ----- ---- ------ ----');
1821: FOR loc IN get_region_info(l_processing_date) LOOP
1822: wsh_debug_sv.logmsg(l_module_name,loc.country||' '||loc.state||' '||loc.city||' '||loc.postal_code_from);
1823: END LOOP;
1824: END IF;
1818: wsh_debug_sv.logmsg(l_module_name,'---------------------------------------------------------------');
1819: wsh_debug_sv.logmsg(l_module_name,'Country State City Postal Code');
1820: wsh_debug_sv.logmsg(l_module_name,'------- ----- ---- ------ ----');
1821: FOR loc IN get_region_info(l_processing_date) LOOP
1822: wsh_debug_sv.logmsg(l_module_name,loc.country||' '||loc.state||' '||loc.city||' '||loc.postal_code_from);
1823: END LOOP;
1824: END IF;
1825: END IF;
1826:
1838: x_sqlerr => l_sqlerr);
1839:
1840:
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:
1851: END IF;
1852: END IF;
1853: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1854: IF l_debug_on THEN
1855: WSH_DEBUG_SV.log(l_module_name,'ERRBUF',p_errbuf);
1856: WSH_DEBUG_SV.log(l_module_name,'RETCODE',p_retcode);
1857: WSH_DEBUG_SV.pop(l_module_name);
1858: END IF;
1859:
1852: END IF;
1853: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1854: IF l_debug_on THEN
1855: WSH_DEBUG_SV.log(l_module_name,'ERRBUF',p_errbuf);
1856: WSH_DEBUG_SV.log(l_module_name,'RETCODE',p_retcode);
1857: WSH_DEBUG_SV.pop(l_module_name);
1858: END IF;
1859:
1860: EXCEPTION
1853: l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1854: IF l_debug_on THEN
1855: WSH_DEBUG_SV.log(l_module_name,'ERRBUF',p_errbuf);
1856: WSH_DEBUG_SV.log(l_module_name,'RETCODE',p_retcode);
1857: WSH_DEBUG_SV.pop(l_module_name);
1858: END IF;
1859:
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;
1880: p_retcode := '2';
1881:
1882:
1883: IF l_debug_on THEN
1884: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1885: END IF;
1886:
1887: END Map_Location_Region_Child;
1888:
1923: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1924: --
1925: IF l_debug_on IS NULL
1926: THEN
1927: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1928: END IF;
1929:
1930: IF l_debug_on THEN
1931: WSH_DEBUG_SV.push(l_module_name);
1927: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1928: END IF;
1929:
1930: IF l_debug_on THEN
1931: WSH_DEBUG_SV.push(l_module_name);
1932: END IF;
1933:
1934: FND_PROFILE.Get('CONC_REQUEST_ID', l_this_request);
1935:
1971: END IF;
1972: x_completion_status := l_completion_status;
1973:
1974: IF l_debug_on 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');
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');
1984: END IF;
1985:
1986: END Get_Child_Requests_Status;
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');
1984: END IF;
1985:
1986: END Get_Child_Requests_Status;
1987:
2009: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2010: --
2011: IF l_debug_on IS NULL
2012: THEN
2013: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2014: END IF;
2015:
2016: IF l_debug_on THEN
2017: WSH_DEBUG_SV.push(l_module_name);
2013: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2014: END IF;
2015:
2016: IF l_debug_on THEN
2017: WSH_DEBUG_SV.push(l_module_name);
2018: END IF;
2019:
2020: --
2021: --
2021: --
2022: -- Debug Statements
2023: --
2024: IF l_debug_on THEN
2025: WSH_DEBUG_SV.push(l_module_name);
2026: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2027: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2028: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2029: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2022: -- Debug Statements
2023: --
2024: IF l_debug_on THEN
2025: WSH_DEBUG_SV.push(l_module_name);
2026: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2027: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2028: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2029: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2030: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',p_insert_flag);
2023: --
2024: IF l_debug_on THEN
2025: WSH_DEBUG_SV.push(l_module_name);
2026: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2027: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2028: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2029: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2030: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',p_insert_flag);
2031: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2024: IF l_debug_on THEN
2025: WSH_DEBUG_SV.push(l_module_name);
2026: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2027: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2028: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2029: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2030: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',p_insert_flag);
2031: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2032: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2025: WSH_DEBUG_SV.push(l_module_name);
2026: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2027: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2028: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2029: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2030: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',p_insert_flag);
2031: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2032: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2033: END IF;
2026: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2027: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2028: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2029: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2030: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',p_insert_flag);
2031: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2032: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2033: END IF;
2034:
2027: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2028: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2029: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2030: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',p_insert_flag);
2031: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2032: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2033: END IF;
2034:
2035: IF l_debug_on THEN
2028: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2029: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2030: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',p_insert_flag);
2031: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2032: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2033: END IF;
2034:
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);
2032: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2033: END IF;
2034:
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,
2047: p_num_of_instances => p_num_of_instances
2048: );
2049:
2050: IF l_debug_on THEN
2051: WSH_DEBUG_SV.pop(l_module_name);
2052: END IF;
2053:
2054: EXCEPTION
2055: WHEN others THEN
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');
2061: END IF;
2062:
2063: END Process_All_Locations;
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');
2061: END IF;
2062:
2063: END Process_All_Locations;
2064:
2111: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2112: --
2113: IF l_debug_on IS NULL
2114: THEN
2115: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2116: END IF;
2117:
2118:
2119: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
2122: --
2123: -- Debug Statements
2124: --
2125: IF l_debug_on THEN
2126: WSH_DEBUG_SV.push(l_module_name);
2127: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2128: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2129: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
2130: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2123: -- Debug Statements
2124: --
2125: IF l_debug_on THEN
2126: WSH_DEBUG_SV.push(l_module_name);
2127: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2128: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2129: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
2130: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2131: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2124: --
2125: IF l_debug_on THEN
2126: WSH_DEBUG_SV.push(l_module_name);
2127: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2128: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2129: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
2130: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2131: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2132: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
2125: IF l_debug_on THEN
2126: WSH_DEBUG_SV.push(l_module_name);
2127: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2128: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2129: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
2130: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2131: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2132: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
2133: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2126: WSH_DEBUG_SV.push(l_module_name);
2127: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2128: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2129: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
2130: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2131: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2132: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
2133: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2134: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2127: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
2128: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2129: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
2130: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2131: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2132: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
2133: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2134: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2135: END IF;
2128: WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
2129: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
2130: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2131: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2132: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
2133: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2134: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2135: END IF;
2136: --
2129: WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
2130: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2131: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2132: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
2133: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2134: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2135: END IF;
2136: --
2137: l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
2130: WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
2131: WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
2132: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
2133: WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
2134: WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
2135: END IF;
2136: --
2137: l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
2138: l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS') +1;
2137: l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
2138: l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS') +1;
2139:
2140: IF l_debug_on THEN
2141: WSH_DEBUG_SV.log(l_module_name,'l_START_DATE',l_START_DATE);
2142: WSH_DEBUG_SV.log(l_module_name,'l_END_DATE',l_END_DATE);
2143: END IF;
2144:
2145: IF p_num_of_instances is null or p_num_of_instances = 0 then
2138: l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS') +1;
2139:
2140: IF l_debug_on THEN
2141: WSH_DEBUG_SV.log(l_module_name,'l_START_DATE',l_START_DATE);
2142: WSH_DEBUG_SV.log(l_module_name,'l_END_DATE',l_END_DATE);
2143: END IF;
2144:
2145: IF p_num_of_instances is null or p_num_of_instances = 0 then
2146: l_num_of_instances := 1;
2152: -- parameter
2153: IF p_mode = 'CREATE' THEN
2154: --{
2155: IF l_debug_on THEN
2156: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Map_Location_Region_Child',WSH_DEBUG_SV.C_PROC_LEVEL);
2157: END IF;
2158:
2159: Map_Location_Region_Child (
2160: p_errbuf => l_errbuf,
2167: p_end_date => p_end_date,
2168: p_insert_flag => p_insert_flag);
2169:
2170: IF l_debug_on THEN
2171: WSH_DEBUG_SV.logmsg(l_module_name,'Return Code from Map_Location_Region_Child : '||l_retcode);
2172: END IF;
2173:
2174: IF l_retcode = '2' THEN
2175: l_completion_status := 'ERROR';
2183: --{
2184: l_req_data := fnd_conc_global.request_data;
2185:
2186: IF l_debug_on THEN
2187: WSH_DEBUG_SV.log(l_module_name, ' l_req_data', l_req_data);
2188: END IF;
2189:
2190: IF l_req_data IS NOT NULL THEN
2191: l_import_start_date := to_date(SUBSTR(l_req_data, INSTR(l_req_data,':',1,1)+1, LENGTH(l_req_data)),'YYYY/MM/DD HH24:MI:SS');
2201: l_end_date1 := p_end_date;
2202: END IF;
2203:
2204: IF l_debug_on THEN
2205: wsh_debug_sv.log(l_module_name, 'l_req_data 1', l_req_data);
2206: wsh_debug_sv.log(l_module_name, 'l_start_date1', l_start_date1);
2207: wsh_debug_sv.log(l_module_name, 'l_end_date1', l_end_date1);
2208: END IF;
2209:
2202: END IF;
2203:
2204: IF l_debug_on THEN
2205: wsh_debug_sv.log(l_module_name, 'l_req_data 1', l_req_data);
2206: wsh_debug_sv.log(l_module_name, 'l_start_date1', l_start_date1);
2207: wsh_debug_sv.log(l_module_name, 'l_end_date1', l_end_date1);
2208: END IF;
2209:
2210: IF l_req_data IS NULL OR l_req_data = '1' THEN
2203:
2204: IF l_debug_on THEN
2205: wsh_debug_sv.log(l_module_name, 'l_req_data 1', l_req_data);
2206: wsh_debug_sv.log(l_module_name, 'l_start_date1', l_start_date1);
2207: wsh_debug_sv.log(l_module_name, 'l_end_date1', l_end_date1);
2208: END IF;
2209:
2210: IF l_req_data IS NULL OR l_req_data = '1' THEN
2211: --{
2211: --{
2212: IF p_location_type = 'EXTERNAL' THEN
2213: --{
2214: IF l_debug_on THEN
2215: WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is External');
2216: END IF;
2217:
2218: EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
2219: FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
2227:
2228: ELSIF p_location_type = 'INTERNAL' THEN
2229:
2230: IF l_debug_on THEN
2231: WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is Internal');
2232: END IF;
2233:
2234: EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
2235: FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
2243:
2244: ELSIF p_location_type = 'BOTH' THEN
2245:
2246: IF l_debug_on THEN
2247: WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is Both');
2248: END IF;
2249:
2250: EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
2251: FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
2265: FOR i in 1..l_worker_min_tab.count
2266: LOOP
2267:
2268: IF l_debug_on THEN
2269: WSH_DEBUG_SV.logmsg(l_module_name, 'Value of i : '|| i ||' l_worker_min : '||l_worker_min_tab(i)||
2270: ' l_worker_max : '||l_worker_max_tab(i));
2271: END IF;
2272:
2273:
2285: argument6 => l_end_date1,
2286: argument7 => p_insert_flag);
2287:
2288: IF l_debug_on THEN
2289: WSH_DEBUG_SV.log(l_module_name, 'Child request ID ', l_new_request_id);
2290: END IF;
2291:
2292: IF l_new_request_id = 0 THEN
2293: IF l_debug_on THEN
2290: END IF;
2291:
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:
2301: --}
2302: ELSIF l_worker_min_tab.count <>0 AND nvl(p_num_of_instances,0) = 0 THEN
2303: --{
2304: IF l_debug_on THEN
2305: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Map_Location_Region_Child',WSH_DEBUG_SV.C_PROC_LEVEL);
2306: END IF;
2307:
2308: Map_Location_Region_Child (
2309: p_errbuf => l_errbuf,
2316: p_end_date => l_end_date1,
2317: p_insert_flag => p_insert_flag);
2318:
2319: IF l_debug_on THEN
2320: WSH_DEBUG_SV.logmsg(l_module_name,'Return Code from Map_Location_Region_Child : '||l_retcode);
2321: END IF;
2322:
2323: IF l_retcode = '2' THEN
2324: l_completion_status := 'ERROR';
2351: --
2352: -- Debug Statements
2353: --
2354: IF l_debug_on THEN
2355: WSH_DEBUG_SV.log(l_module_name,'ERRBUF', l_errbuf);
2356: WSH_DEBUG_SV.log(l_module_name,'RETCODE',l_retcode);
2357: WSH_DEBUG_SV.pop(l_module_name);
2358: END IF;
2359:
2352: -- Debug Statements
2353: --
2354: IF l_debug_on THEN
2355: WSH_DEBUG_SV.log(l_module_name,'ERRBUF', l_errbuf);
2356: WSH_DEBUG_SV.log(l_module_name,'RETCODE',l_retcode);
2357: WSH_DEBUG_SV.pop(l_module_name);
2358: END IF;
2359:
2360: EXCEPTION
2353: --
2354: IF l_debug_on THEN
2355: WSH_DEBUG_SV.log(l_module_name,'ERRBUF', l_errbuf);
2356: WSH_DEBUG_SV.log(l_module_name,'RETCODE',l_retcode);
2357: WSH_DEBUG_SV.pop(l_module_name);
2358: END IF;
2359:
2360: EXCEPTION
2361: WHEN others THEN
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');
2367: END IF;
2368:
2369: END Process_All_Locations;
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');
2367: END IF;
2368:
2369: END Process_All_Locations;
2370:
2379: x_return_status OUT NOCOPY VARCHAR2 )
2380: IS
2381:
2382: l_session_id NUMBER := 0;
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;
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;
2392: --
2393:
2394: l_session_id := userenv('sessionid');
2393:
2394: l_session_id := userenv('sessionid');
2395:
2396: IF l_debug_on THEN
2397: wsh_debug_sv.logmsg(l_module_name,'g_regions_info_tab count : '||g_regions_info_tab.COUNT);
2398: wsh_debug_sv.logmsg(l_module_name,'g_session_id : '||g_session_id||' Current session id : '||l_session_id);
2399: END IF;
2400:
2401: IF g_session_id IS NULL OR l_session_id <> g_session_id
2394: l_session_id := userenv('sessionid');
2395:
2396: IF l_debug_on THEN
2397: wsh_debug_sv.logmsg(l_module_name,'g_regions_info_tab count : '||g_regions_info_tab.COUNT);
2398: wsh_debug_sv.logmsg(l_module_name,'g_session_id : '||g_session_id||' Current session id : '||l_session_id);
2399: END IF;
2400:
2401: IF g_session_id IS NULL OR l_session_id <> g_session_id
2402: OR g_country_code <> p_country_code OR g_regions_info_tab.count > 5000 THEN
2404: g_country_code := p_country_code;
2405: g_session_id := l_session_id;
2406:
2407: IF l_debug_on THEN
2408: wsh_debug_sv.logmsg(l_module_name,'Regions Cache cleared');
2409: END IF;
2410:
2411: END IF;
2412:
2411: END IF;
2412:
2413: --
2414: IF l_debug_on THEN
2415: wsh_debug_sv.pop (l_module_name);
2416: END IF;
2417: --
2418:
2419: EXCEPTION
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');
2427: END IF;
2428: --
2429: END 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');
2427: END IF;
2428: --
2429: END refresh_cache;
2430:
2568: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2569: --
2570: IF l_debug_on IS NULL
2571: THEN
2572: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2573: END IF;
2574: --
2575: --
2576: -- Debug Statements
2575: --
2576: -- Debug Statements
2577: --
2578: IF l_debug_on THEN
2579: WSH_DEBUG_SV.push(l_module_name);
2580: --
2581: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
2582: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
2583: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2577: --
2578: IF l_debug_on THEN
2579: WSH_DEBUG_SV.push(l_module_name);
2580: --
2581: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
2582: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
2583: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2584: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2585: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2578: IF l_debug_on THEN
2579: WSH_DEBUG_SV.push(l_module_name);
2580: --
2581: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
2582: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
2583: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2584: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2585: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2586: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2579: WSH_DEBUG_SV.push(l_module_name);
2580: --
2581: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
2582: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
2583: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2584: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2585: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2586: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2587: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2580: --
2581: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
2582: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
2583: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2584: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2585: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2586: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2587: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2588: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
2581: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
2582: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
2583: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2584: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2585: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2586: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2587: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2588: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
2589: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2582: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
2583: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2584: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2585: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2586: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2587: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2588: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
2589: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2590: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2583: WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2584: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2585: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2586: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2587: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2588: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
2589: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2590: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2591: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2584: WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2585: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2586: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2587: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2588: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
2589: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2590: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2591: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2592: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
2585: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2586: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2587: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2588: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
2589: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2590: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2591: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2592: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
2593: WSH_DEBUG_SV.log(l_module_name,'P_ZONE_FLAG',P_ZONE_FLAG);
2586: WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2587: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2588: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
2589: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2590: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2591: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2592: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
2593: WSH_DEBUG_SV.log(l_module_name,'P_ZONE_FLAG',P_ZONE_FLAG);
2594: END IF;
2587: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2588: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
2589: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2590: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2591: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2592: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
2593: WSH_DEBUG_SV.log(l_module_name,'P_ZONE_FLAG',P_ZONE_FLAG);
2594: END IF;
2595:
2588: WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
2589: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2590: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2591: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2592: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
2593: WSH_DEBUG_SV.log(l_module_name,'P_ZONE_FLAG',P_ZONE_FLAG);
2594: END IF;
2595:
2596:
2589: WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2590: WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2591: WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2592: WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
2593: WSH_DEBUG_SV.log(l_module_name,'P_ZONE_FLAG',P_ZONE_FLAG);
2594: END IF;
2595:
2596:
2597: IF g_regions_info_tab.count = 0 THEN
2631: x_region_tab => l_rgid_tab,
2632: x_return_status => l_return_status);
2633:
2634: IF l_debug_on THEN
2635: WSH_DEBUG_SV.log(l_module_name,'Number of regions found in wrl : ',l_rgid_tab.COUNT);
2636: END IF;
2637: --
2638:
2639: IF l_rgid_tab.COUNT > 0 THEN
2644: CLOSE region_details;
2645:
2646: --
2647: IF l_debug_on THEN
2648: WSH_DEBUG_SV.log(l_module_name,'l_rgid_tab(j) : ',l_rgid_tab(j));
2649: WSH_DEBUG_SV.log(l_module_name,'l_region_info.region_id : ',l_region_info.region_id);
2650: WSH_DEBUG_SV.log(l_module_name,'l_region_info.region_type : ',l_region_info.region_type);
2651: WSH_DEBUG_SV.log(l_module_name,'cnt_region : ',cnt_region);
2652: END IF;
2645:
2646: --
2647: IF l_debug_on THEN
2648: WSH_DEBUG_SV.log(l_module_name,'l_rgid_tab(j) : ',l_rgid_tab(j));
2649: WSH_DEBUG_SV.log(l_module_name,'l_region_info.region_id : ',l_region_info.region_id);
2650: WSH_DEBUG_SV.log(l_module_name,'l_region_info.region_type : ',l_region_info.region_type);
2651: WSH_DEBUG_SV.log(l_module_name,'cnt_region : ',cnt_region);
2652: END IF;
2653: --
2646: --
2647: IF l_debug_on THEN
2648: WSH_DEBUG_SV.log(l_module_name,'l_rgid_tab(j) : ',l_rgid_tab(j));
2649: WSH_DEBUG_SV.log(l_module_name,'l_region_info.region_id : ',l_region_info.region_id);
2650: WSH_DEBUG_SV.log(l_module_name,'l_region_info.region_type : ',l_region_info.region_type);
2651: WSH_DEBUG_SV.log(l_module_name,'cnt_region : ',cnt_region);
2652: END IF;
2653: --
2654:
2647: IF l_debug_on THEN
2648: WSH_DEBUG_SV.log(l_module_name,'l_rgid_tab(j) : ',l_rgid_tab(j));
2649: WSH_DEBUG_SV.log(l_module_name,'l_region_info.region_id : ',l_region_info.region_id);
2650: WSH_DEBUG_SV.log(l_module_name,'l_region_info.region_type : ',l_region_info.region_type);
2651: WSH_DEBUG_SV.log(l_module_name,'cnt_region : ',cnt_region);
2652: END IF;
2653: --
2654:
2655: cnt_region := cnt_region + 1;
2670:
2671: -- Debug Statements
2672: --
2673: IF l_debug_on THEN
2674: WSH_DEBUG_SV.logmsg(l_module_name, ' fetching zone info of '||l_region_rec3.region_id);
2675: WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2676: END IF;
2677:
2678: x_regions(cnt_region) := l_region_rec3;
2671: -- Debug Statements
2672: --
2673: IF l_debug_on THEN
2674: WSH_DEBUG_SV.logmsg(l_module_name, ' fetching zone info of '||l_region_rec3.region_id);
2675: WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2676: END IF;
2677:
2678: x_regions(cnt_region) := l_region_rec3;
2679:
2715: --
2716: -- Debug Statements
2717: --
2718: IF l_debug_on THEN
2719: WSH_DEBUG_SV.logmsg(l_module_name, ' fetched location data city '||l_region_rec2.city||' country code '||l_region_rec2.country_code||'State '||l_region_rec2.state||' postal_code '||l_region_rec2.postal_code_from);
2720: END IF;
2721:
2722: END IF;
2723:
2744: --
2745: -- Debug Statements
2746: --
2747: IF l_debug_on THEN
2748: WSH_DEBUG_SV.logmsg(l_module_name, ' region type '||l_region_type);
2749: WSH_DEBUG_SV.logmsg(l_module_name, ' city: '||l_region_rec2.city||' code: '||l_region_rec2.city_code);
2750: WSH_DEBUG_SV.logmsg(l_module_name, ' state: '||l_region_rec2.state||' code: '||l_region_rec2.state_code);
2751: WSH_DEBUG_SV.logmsg(l_module_name, ' country: '||l_region_rec2.country||' code: '||l_region_rec2.country_code);
2752: END IF;
2745: -- Debug Statements
2746: --
2747: IF l_debug_on THEN
2748: WSH_DEBUG_SV.logmsg(l_module_name, ' region type '||l_region_type);
2749: WSH_DEBUG_SV.logmsg(l_module_name, ' city: '||l_region_rec2.city||' code: '||l_region_rec2.city_code);
2750: WSH_DEBUG_SV.logmsg(l_module_name, ' state: '||l_region_rec2.state||' code: '||l_region_rec2.state_code);
2751: WSH_DEBUG_SV.logmsg(l_module_name, ' country: '||l_region_rec2.country||' code: '||l_region_rec2.country_code);
2752: END IF;
2753:
2746: --
2747: IF l_debug_on THEN
2748: WSH_DEBUG_SV.logmsg(l_module_name, ' region type '||l_region_type);
2749: WSH_DEBUG_SV.logmsg(l_module_name, ' city: '||l_region_rec2.city||' code: '||l_region_rec2.city_code);
2750: WSH_DEBUG_SV.logmsg(l_module_name, ' state: '||l_region_rec2.state||' code: '||l_region_rec2.state_code);
2751: WSH_DEBUG_SV.logmsg(l_module_name, ' country: '||l_region_rec2.country||' code: '||l_region_rec2.country_code);
2752: END IF;
2753:
2754: cnt := cnt + 1;
2747: IF l_debug_on THEN
2748: WSH_DEBUG_SV.logmsg(l_module_name, ' region type '||l_region_type);
2749: WSH_DEBUG_SV.logmsg(l_module_name, ' city: '||l_region_rec2.city||' code: '||l_region_rec2.city_code);
2750: WSH_DEBUG_SV.logmsg(l_module_name, ' state: '||l_region_rec2.state||' code: '||l_region_rec2.state_code);
2751: WSH_DEBUG_SV.logmsg(l_module_name, ' country: '||l_region_rec2.country||' code: '||l_region_rec2.country_code);
2752: END IF;
2753:
2754: cnt := cnt + 1;
2755:
2755:
2756: -- Debug Statements
2757: --
2758: IF l_debug_on THEN
2759: WSH_DEBUG_SV.logmsg(l_module_name, 'number in loop '||cnt);
2760: END IF;
2761:
2762: -- Bugfix 2877445 if region type is postal code then dont search with
2763: -- city and state information. This is under the assumption that postal
2854:
2855: -- Debug Statements
2856: --
2857: IF l_debug_on THEN
2858: WSH_DEBUG_SV.logmsg(l_module_name, ' parent offset '||l_parent_offset);
2859: END IF;
2860:
2861: END IF;
2862:
2864:
2865: -- Debug Statements
2866: --
2867: IF l_debug_on THEN
2868: WSH_DEBUG_SV.logmsg(l_module_name, 'got region id '||l_region_rec1.region_id);
2869: END IF;
2870:
2871: IF l_region_rec1.region_id > 0 THEN
2872:
2873: cnt_region := cnt_region + 1;
2874: -- Debug Statements
2875: --
2876: IF l_debug_on THEN
2877: WSH_DEBUG_SV.logmsg(l_module_name, ' Using region info of '||l_region_rec1.region_id);
2878: WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2879: END IF;
2880:
2881: IF is_first THEN
2874: -- Debug Statements
2875: --
2876: IF l_debug_on THEN
2877: WSH_DEBUG_SV.logmsg(l_module_name, ' Using region info of '||l_region_rec1.region_id);
2878: WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2879: END IF;
2880:
2881: IF is_first THEN
2882: l_region_rec1.is_input_type := 'Y'; -- Record is fetched based on the exact input match.
2886: -- Populating the output table x_regions such that the index starts from '1' not from '0'.
2887: FOR i IN l_regions.FIRST.. l_regions.LAST
2888: LOOP
2889: IF l_debug_on THEN
2890: WSH_DEBUG_SV.logmsg(l_module_name, ' Using region info of '||l_regions(i).region_id);
2891: WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2892: END IF;
2893: l_regions(i).is_input_type := 'Y';
2894: x_regions(cnt_region) := l_regions(i);
2887: FOR i IN l_regions.FIRST.. l_regions.LAST
2888: LOOP
2889: IF l_debug_on THEN
2890: WSH_DEBUG_SV.logmsg(l_module_name, ' Using region info of '||l_regions(i).region_id);
2891: WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2892: END IF;
2893: l_regions(i).is_input_type := 'Y';
2894: x_regions(cnt_region) := l_regions(i);
2895: cnt_region := cnt_region + 1;
2895: cnt_region := cnt_region + 1;
2896: END LOOP;
2897:
2898: IF l_debug_on THEN
2899: WSH_DEBUG_SV.pop(l_module_name);
2900: END IF;
2901:
2902: RETURN;
2903: END IF;
2920:
2921: -- Debug Statements
2922: --
2923: IF l_debug_on THEN
2924: WSH_DEBUG_SV.logmsg(l_module_name, ' fetching zone info of '||l_region_rec3.region_id);
2925: WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2926: END IF;
2927:
2928: x_regions(cnt_region) := l_region_rec3;
2921: -- Debug Statements
2922: --
2923: IF l_debug_on THEN
2924: WSH_DEBUG_SV.logmsg(l_module_name, ' fetching zone info of '||l_region_rec3.region_id);
2925: WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2926: END IF;
2927:
2928: x_regions(cnt_region) := l_region_rec3;
2929:
2952: IF (x_regions.COUNT = 0) THEN
2953: x_status := 1;
2954: ELSE
2955: IF l_debug_on THEN
2956: WSH_DEBUG_SV.logmsg(l_module_name, ' x_regions.COUNT '||x_regions.COUNT);
2957: END IF;
2958: END IF;
2959:
2960: --
2960: --
2961: -- Debug Statements
2962: --
2963: IF l_debug_on THEN
2964: WSH_DEBUG_SV.pop(l_module_name);
2965: END IF;
2966: --
2967: --Bug 4775798
2968: EXCEPTION
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
2975: WSH_UTIL_CORE.printmsg('Unexpected error has occured in WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches');
2976: WSH_UTIL_CORE.printmsg(SQLERRM);
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
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;
3010: l_region_id_string VARCHAR2(32767);
3011: --
3012:
3013: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_All_RegionId_Matches';
3014: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3015: l_return_status VARCHAR2(1);
3016:
3017: BEGIN
3018:
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;
3024:
3025: IF (p_use_cache) and (p_location_id < g_cache_max_size) THEN
3026:
3037: IF (l_region_id_tab(itr)<>-1) THEN
3038: x_region_tab := l_region_id_tab;
3039: END IF;
3040: IF l_debug_on THEN
3041: wsh_debug_sv.pop(l_module_name);
3042: END IF;
3043: RETURN;
3044: END IF;
3045: END IF;
3076: g_location_region_tab(p_location_id) := l_region_id_string;
3077: END IF;
3078:
3079: IF l_debug_on THEN
3080: WSH_DEBUG_SV.pop(l_module_name);
3081: END IF;
3082:
3083: EXCEPTION
3084: WHEN others THEN
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');
3094: --Bug 4775798
3095: ELSE
3096: WSH_UTIL_CORE.printmsg('Unexpected error has occured in 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');
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);
3128:
3129: --
3130:
3131: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_All_Zone_Matches';
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;
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;
3141:
3142: IF (p_region_id < g_cache_max_size AND g_region_zone_tab.EXISTS(p_region_id)) THEN
3143:
3152: x_zone_tab := l_zone_tab;
3153: END IF;
3154:
3155: IF l_debug_on THEN
3156: wsh_debug_sv.pop(l_module_name);
3157: END IF;
3158: RETURN;
3159: END IF;
3160:
3190:
3191: END IF;
3192:
3193: IF l_debug_on THEN
3194: WSH_DEBUG_SV.pop(l_module_name);
3195: END IF;
3196:
3197: EXCEPTION
3198: WHEN others THEN
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');
3208: END IF;
3209: --
3210: END 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');
3208: END IF;
3209: --
3210: END Get_All_Zone_Matches;
3211:
3251: rg_itr NUMBER;
3252: r_nrec NUMBER;
3253: i NUMBER;
3254: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_all_region_deconsols';
3255: l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
3256:
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,
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;
3289:
3290: IF l_debug_on THEN
3291: WSH_DEBUG_SV.logmsg(l_module_name,'p_location_id: '|| p_location_id);
3287: wsh_debug_sv.push (l_module_name);
3288: END IF;
3289:
3290: IF l_debug_on THEN
3291: WSH_DEBUG_SV.logmsg(l_module_name,'p_location_id: '|| p_location_id);
3292: END IF;
3293:
3294: -- If region deconsolidation location tab exists in cache, return
3295: -- else query database through c_get_all_regions_loc cursor
3298:
3299: IF p_zone_flag = TRUE AND g_loc_region_zone_deconsol_tab.EXISTS(p_location_id) THEN
3300: x_region_consol_tab := g_loc_region_zone_deconsol_tab(p_location_id);
3301: IF l_debug_on THEN
3302: WSH_DEBUG_SV.pop(l_module_name);
3303: END IF;
3304: return;
3305: ELSIF g_loc_region_deconsol_tab.EXISTS(p_location_id) THEN
3306: x_region_consol_tab := g_loc_region_deconsol_tab(p_location_id);
3304: return;
3305: ELSIF g_loc_region_deconsol_tab.EXISTS(p_location_id) THEN
3306: x_region_consol_tab := g_loc_region_deconsol_tab(p_location_id);
3307: IF l_debug_on THEN
3308: WSH_DEBUG_SV.pop(l_module_name);
3309: END IF;
3310: return;
3311: ELSE
3312: OPEN c_get_all_regions_loc(p_location_id,p_lang_code);
3320: CLOSE c_get_all_regions_loc;
3321: END IF;
3322:
3323: IF l_debug_on THEN
3324: WSH_DEBUG_SV.logmsg(l_module_name,'l_region_tab.COUNT: '|| l_region_tab.COUNT);
3325: END IF;
3326:
3327: i := 1;
3328: IF p_use_cache = TRUE THEN
3356: -- If deconsol location exists for region, then add it to deconsol_tab
3357: IF l_region_tab(r_itr).Deconsol_location IS NOT NULL THEN
3358: l_reg_zon_deconsol_tab(rg_itr) := l_region_tab(r_itr);
3359: IF l_debug_on THEN
3360: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).region_id: '|| l_reg_zon_deconsol_tab(rg_itr).region_id);
3361: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).deconsol_location: '|| l_reg_zon_deconsol_tab(rg_itr).Deconsol_location);
3362: END IF;
3363: END IF;
3364:
3357: IF l_region_tab(r_itr).Deconsol_location IS NOT NULL THEN
3358: l_reg_zon_deconsol_tab(rg_itr) := l_region_tab(r_itr);
3359: IF l_debug_on THEN
3360: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).region_id: '|| l_reg_zon_deconsol_tab(rg_itr).region_id);
3361: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).deconsol_location: '|| l_reg_zon_deconsol_tab(rg_itr).Deconsol_location);
3362: END IF;
3363: END IF;
3364:
3365: IF p_use_cache = TRUE AND l_region_id < g_cache_max_size THEN
3372: CLOSE c_get_zone_for_region;
3373: END IF;
3374:
3375: IF l_debug_on THEN
3376: WSH_DEBUG_SV.logmsg(l_module_name,'l_zone_deconsol_tab.COUNT: '|| l_zone_deconsol_tab.COUNT);
3377: END IF;
3378: ELSIF NOT (g_region_zone_deconsol_tab.EXISTS(l_region_id)) OR p_use_cache = FALSE THEN
3379: OPEN c_get_zone_for_region(l_region_id);
3380: FETCH c_get_zone_for_region BULK COLLECT INTO l_zone_deconsol_tab;
3381: CLOSE c_get_zone_for_region;
3382: END IF;
3383:
3384: IF l_debug_on THEN
3385: WSH_DEBUG_SV.logmsg(l_module_name,'l_zone_deconsol_tab.COUNT: '|| l_zone_deconsol_tab.COUNT);
3386: END IF;
3387: -- IF p_rule_to_zone_id IS NOT NULL:
3388: -- If caller is not WMS: If at any level of regions the zone(s) obtained do not match
3389: -- with input p_rule_to_zone_id, that level of zone should be skipped.
3399: IF l_zone_deconsol_tab(z_itr).region_id = p_rule_to_zone_id THEN
3400: rg_itr := rg_itr+1;
3401: l_reg_zon_deconsol_tab(rg_itr) := l_zone_deconsol_tab(z_itr);
3402: IF l_debug_on THEN
3403: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).region_id: '|| l_reg_zon_deconsol_tab(rg_itr).region_id);
3404: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).deconsol_location: '|| l_reg_zon_deconsol_tab(rg_itr).Deconsol_location);
3405: END IF;
3406: EXIT;
3407: END IF;
3400: rg_itr := rg_itr+1;
3401: l_reg_zon_deconsol_tab(rg_itr) := l_zone_deconsol_tab(z_itr);
3402: IF l_debug_on THEN
3403: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).region_id: '|| l_reg_zon_deconsol_tab(rg_itr).region_id);
3404: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).deconsol_location: '|| l_reg_zon_deconsol_tab(rg_itr).Deconsol_location);
3405: END IF;
3406: EXIT;
3407: END IF;
3408:
3417: LOOP
3418: rg_itr := rg_itr+1;
3419: l_reg_zon_deconsol_tab(rg_itr) := l_zone_deconsol_tab(z_itr);
3420: IF l_debug_on THEN
3421: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).region_id: '|| l_reg_zon_deconsol_tab(rg_itr).region_id);
3422: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).deconsol_location: '|| l_reg_zon_deconsol_tab(rg_itr).Deconsol_location);
3423: END IF;
3424:
3425: EXIT WHEN z_itr = l_zone_deconsol_tab.LAST;
3418: rg_itr := rg_itr+1;
3419: l_reg_zon_deconsol_tab(rg_itr) := l_zone_deconsol_tab(z_itr);
3420: IF l_debug_on THEN
3421: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).region_id: '|| l_reg_zon_deconsol_tab(rg_itr).region_id);
3422: WSH_DEBUG_SV.logmsg(l_module_name,'l_reg_zon_deconsol_tab(rg_itr).deconsol_location: '|| l_reg_zon_deconsol_tab(rg_itr).Deconsol_location);
3423: END IF;
3424:
3425: EXIT WHEN z_itr = l_zone_deconsol_tab.LAST;
3426: z_itr:= l_zone_deconsol_tab.NEXT(z_itr);
3442: x_region_consol_tab := l_region_deconsol_tab;
3443: END IF;
3444:
3445: IF l_debug_on THEN
3446: WSH_DEBUG_SV.logmsg(l_module_name,'x_region_consol_tab.COUNT: '|| x_region_consol_tab.COUNT);
3447: END IF;
3448:
3449: IF l_debug_on THEN
3450: WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status: '|| x_return_status);
3446: WSH_DEBUG_SV.logmsg(l_module_name,'x_region_consol_tab.COUNT: '|| x_region_consol_tab.COUNT);
3447: END IF;
3448:
3449: IF l_debug_on THEN
3450: WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status: '|| x_return_status);
3451: END IF;
3452:
3453: IF l_debug_on THEN
3454: wsh_debug_sv.pop (l_module_name);
3450: WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status: '|| x_return_status);
3451: END IF;
3452:
3453: IF l_debug_on THEN
3454: wsh_debug_sv.pop (l_module_name);
3455: END IF;
3456: ----}
3457:
3458: EXCEPTION
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');
3466: END IF;
3467:
3468: WHEN OTHERS 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');
3466: END IF;
3467:
3468: WHEN OTHERS THEN
3469: IF c_get_all_regions_loc%ISOPEN THEN
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');
3481: END IF;
3482:
3483: END 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');
3481: END IF;
3482:
3483: END get_all_region_deconsols;
3484:
3540: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3541: --
3542: IF l_debug_on IS NULL
3543: THEN
3544: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3545: END IF;
3546: --
3547: --
3548: -- Debug Statements
3547: --
3548: -- Debug Statements
3549: --
3550: IF l_debug_on THEN
3551: WSH_DEBUG_SV.push(l_module_name);
3552: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3553: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3554: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3555: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3548: -- Debug Statements
3549: --
3550: IF l_debug_on THEN
3551: WSH_DEBUG_SV.push(l_module_name);
3552: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3553: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3554: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3555: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3556: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3549: --
3550: IF l_debug_on THEN
3551: WSH_DEBUG_SV.push(l_module_name);
3552: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3553: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3554: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3555: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3556: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3557: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3550: IF l_debug_on THEN
3551: WSH_DEBUG_SV.push(l_module_name);
3552: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3553: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3554: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3555: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3556: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3557: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3558: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3551: WSH_DEBUG_SV.push(l_module_name);
3552: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3553: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3554: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3555: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3556: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3557: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3558: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3559: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3552: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3553: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3554: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3555: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3556: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3557: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3558: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3559: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3560: END IF;
3553: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3554: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3555: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3556: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3557: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3558: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3559: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3560: END IF;
3561: --
3554: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3555: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3556: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3557: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3558: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3559: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3560: END IF;
3561: --
3562:
3555: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3556: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3557: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3558: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
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;
3584: --
3585: -- Debug Statements
3586: --
3587: IF l_debug_on THEN
3588: WSH_DEBUG_SV.log(l_module_name, 'Region_Id Fetched', x_region_info.region_id);
3589: END IF;
3590: --
3591:
3592: --
3592: --
3593: -- Debug Statements
3594: --
3595: IF l_debug_on THEN
3596: WSH_DEBUG_SV.pop(l_module_name);
3597: END IF;
3598: --
3599:
3600: -- Exception Handling part
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);
3608: WSH_DEBUG_SV.pop(l_module_name);
3609: END IF;
3610:
3611: END;
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);
3608: WSH_DEBUG_SV.pop(l_module_name);
3609: END IF;
3610:
3611: END;
3612:
3658: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3659: --
3660: IF l_debug_on IS NULL
3661: THEN
3662: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3663: END IF;
3664: --
3665: --
3666: -- Debug Statements
3665: --
3666: -- Debug Statements
3667: --
3668: IF l_debug_on THEN
3669: WSH_DEBUG_SV.push(l_module_name);
3670: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3671: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3672: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3673: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY-CODE', P_COUNTRY_CODE);
3666: -- Debug Statements
3667: --
3668: IF l_debug_on THEN
3669: WSH_DEBUG_SV.push(l_module_name);
3670: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3671: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3672: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3673: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY-CODE', P_COUNTRY_CODE);
3674: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3667: --
3668: IF l_debug_on THEN
3669: WSH_DEBUG_SV.push(l_module_name);
3670: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3671: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3672: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3673: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY-CODE', P_COUNTRY_CODE);
3674: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3675: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3668: IF l_debug_on THEN
3669: WSH_DEBUG_SV.push(l_module_name);
3670: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3671: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3672: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3673: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY-CODE', P_COUNTRY_CODE);
3674: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3675: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3676: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3669: WSH_DEBUG_SV.push(l_module_name);
3670: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3671: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3672: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3673: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY-CODE', P_COUNTRY_CODE);
3674: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3675: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3676: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3677: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3670: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3671: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3672: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3673: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY-CODE', P_COUNTRY_CODE);
3674: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3675: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3676: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3677: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3678: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3671: WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3672: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3673: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY-CODE', P_COUNTRY_CODE);
3674: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3675: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3676: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3677: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3678: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3679: END IF;
3672: WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3673: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY-CODE', P_COUNTRY_CODE);
3674: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3675: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3676: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3677: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3678: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3679: END IF;
3680: --
3673: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY-CODE', P_COUNTRY_CODE);
3674: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3675: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3676: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3677: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3678: WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3679: END IF;
3680: --
3681:
3674: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3675: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3676: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3677: WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
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;
3695: --
3696: -- Debug Statements
3697: --
3698: IF l_debug_on THEN
3699: WSH_DEBUG_SV.log(l_module_name, 'Region_Id Fetched', x_region_info.region_id);
3700: END IF;
3701: --
3702:
3703: --
3703: --
3704: -- Debug Statements
3705: --
3706: IF l_debug_on THEN
3707: WSH_DEBUG_SV.pop(l_module_name);
3708: END IF;
3709: --
3710:
3711: -- Exception Handling part
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));
3719: WSH_DEBUG_SV.pop(l_module_name);
3720: END IF;
3721:
3722: END;
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));
3719: WSH_DEBUG_SV.pop(l_module_name);
3720: END IF;
3721:
3722: END;
3723:
3777: l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3778: --
3779: IF l_debug_on IS NULL
3780: THEN
3781: l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3782: END IF;
3783: --
3784: --
3785: -- Debug Statements
3784: --
3785: -- Debug Statements
3786: --
3787: IF l_debug_on THEN
3788: WSH_DEBUG_SV.push(l_module_name);
3789: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
3790: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3791: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3792: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3785: -- Debug Statements
3786: --
3787: IF l_debug_on THEN
3788: WSH_DEBUG_SV.push(l_module_name);
3789: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
3790: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3791: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3792: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3793: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3786: --
3787: IF l_debug_on THEN
3788: WSH_DEBUG_SV.push(l_module_name);
3789: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
3790: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3791: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3792: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3793: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3794: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3787: IF l_debug_on THEN
3788: WSH_DEBUG_SV.push(l_module_name);
3789: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
3790: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3791: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3792: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3793: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3794: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3795: WSH_DEBUG_SV.log(l_module_name, 'P_LANGUAGE_CODE', P_LANGUAGE_CODE);
3788: WSH_DEBUG_SV.push(l_module_name);
3789: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
3790: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3791: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3792: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3793: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3794: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3795: WSH_DEBUG_SV.log(l_module_name, 'P_LANGUAGE_CODE', P_LANGUAGE_CODE);
3796: END IF;
3789: WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
3790: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3791: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3792: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3793: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3794: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3795: WSH_DEBUG_SV.log(l_module_name, 'P_LANGUAGE_CODE', P_LANGUAGE_CODE);
3796: END IF;
3797: --
3790: WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3791: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3792: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3793: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3794: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3795: WSH_DEBUG_SV.log(l_module_name, 'P_LANGUAGE_CODE', P_LANGUAGE_CODE);
3796: END IF;
3797: --
3798:
3791: WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3792: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3793: WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3794: WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
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;
3841: --
3842: -- Debug Statements
3843: --
3844: IF l_debug_on THEN
3845: WSH_DEBUG_SV.log(l_module_name, 'Region_Id Fetched', x_region_id);
3846: END IF;
3847: --
3848:
3849: --
3849: --
3850: -- Debug Statements
3851: --
3852: IF l_debug_on THEN
3853: WSH_DEBUG_SV.pop(l_module_name);
3854: END IF;
3855: --
3856:
3857: --wsh_debug_sv.logmsg(l_module_name, 'TOTAL TIME TAKEN FOR CHECK_REGION_ID_CODES_ONLY : ' || ((dbms_utility.get_time - t1)/100));
3853: WSH_DEBUG_SV.pop(l_module_name);
3854: END IF;
3855: --
3856:
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;
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));
3866: WSH_DEBUG_SV.pop(l_module_name);
3867: END IF;
3868:
3869: END;
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));
3866: WSH_DEBUG_SV.pop(l_module_name);
3867: END IF;
3868:
3869: END;
3870: