[Home] [Help]
PACKAGE BODY: APPS.WSH_REGIONS_SEARCH_PKG
Source
1 PACKAGE BODY WSH_REGIONS_SEARCH_PKG AS
2 /* $Header: WSHRESEB.pls 120.15.12020000.2 2012/07/04 10:39:02 suppal ship $ */
3
4 --
5 -- Package
6 -- WSH_REGIONS_SEARCH_PKG
7 --
8 -- Purpose
9 --
10
11 --
12 -- PACKAGE TYPES
13 --
14
15 --
16 -- PUBLIC VARIABLES
17 --
18
19 --Global cache for regions and locations.
20 g_region_zone_tab WSH_UTIL_CORE.tbl_varchar;
21 g_location_region_tab WSH_UTIL_CORE.tbl_varchar;
22
23 g_loc_region_deconsol_tab loc_region_deconsol_tab_type;
24 g_loc_region_zone_deconsol_tab loc_region_deconsol_tab_type;
25 g_region_zone_deconsol_tab region_zone_deconsol_tab_type;
26
27 -- Bug - 4722963
28 -- constants defined to be used in regions cache g_regions_info_tab
29 TYPE regions_info_tab_type IS TABLE OF region_table INDEX BY VARCHAR2(32767);
30
31 g_regions_info_tab regions_info_tab_type;
32 g_emp_reg_info_tab regions_info_tab_type; --Bug 7313093
33 g_int_mask VARCHAR2(12) := 'S00000000000';
34 g_lpad_char VARCHAR2(1) := '0';
35 g_lpad_length NUMBER := 120; --Bug 7313093
36 g_lpad_code_length NUMBER := 5;
37 g_session_id NUMBER;
38 g_country_code VARCHAR2(2);
39 -- Bug - 4722963 end
40
41 --Cache size constant
42 g_cache_max_size NUMBER := power(2,31);
43 --
44
45 --
46 -- PUBLIC FUNCTIONS/PROCEDURES
47 --
48
49 --
50 -- Procedure: Get_Region_Info
51 --
52 -- Purpose: call another Get_Region_Info with p_search_flag ='N'
53 --
54 --
55 --
56 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_REGIONS_SEARCH_PKG';
57 --
58
59 --
60 -- PROCEDURE : get_key
61 -- ACESS LEVEL : PRIVATE
62 --
63 -- PURPOSE : This procedure returns cache key in the following format
64 -- region type-country-country code-state-state code-city-city code-postal code from-postal code to-zone-lang code
65
66 PROCEDURE get_key (
67 p_country IN VARCHAR2,
68 p_state IN VARCHAR2,
69 p_city IN VARCHAR2,
70 p_postal_code_from IN VARCHAR2,
71 p_postal_code_to IN VARCHAR2,
72 p_zone IN VARCHAR2,
73 p_lang_code IN VARCHAR2,
74 p_country_code IN VARCHAR2,
75 p_state_code IN VARCHAR2,
76 p_city_code IN VARCHAR2,
77 p_region_type IN NUMBER,
78 x_key OUT NOCOPY VARCHAR2,
79 x_return_status OUT NOCOPY VARCHAR2) IS
80
81 --
82 l_debug_on BOOLEAN;
83 l_key VARCHAR2(32767);
84 --
85 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_KEY';
86 l_return_status VARCHAR2(10);
87 BEGIN
88
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);
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;
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) ||'-'||
119 LPAD(P_STATE, g_lpad_length, g_lpad_char) ||'-'||
120 LPAD(P_STATE_CODE, g_lpad_code_length, g_lpad_char) ||'-'||
121 LPAD(P_CITY, g_lpad_length, g_lpad_char) ||'-'||
122 LPAD(P_CITY_CODE, g_lpad_code_length, g_lpad_char) ||'-'||
123 LPAD(P_POSTAL_CODE_FROM, g_lpad_length, g_lpad_char) ||'-'||
124 LPAD(P_POSTAL_CODE_TO, g_lpad_length, g_lpad_char) ||'-'||
125 LPAD(P_ZONE, g_lpad_length, g_lpad_char) ||'-'||
126 LPAD(P_LANG_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
145 PROCEDURE Get_Region_Info (
146 p_country IN VARCHAR2,
147 p_country_region IN VARCHAR2,
148 p_state IN VARCHAR2,
149 p_city IN VARCHAR2,
150 p_postal_code_from IN VARCHAR2,
151 p_postal_code_to IN VARCHAR2,
152 p_zone IN VARCHAR2,
153 p_lang_code IN VARCHAR2,
154 p_country_code IN VARCHAR2,
155 p_country_region_code IN VARCHAR2,
156 p_state_code IN VARCHAR2,
157 p_city_code IN VARCHAR2,
158 p_region_type IN NUMBER,
159 p_interface_flag IN VARCHAR2,
160 x_region_info OUT NOCOPY region_rec) IS
161 --
162 l_debug_on BOOLEAN;
163 l_regions region_table;
164 --
165 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_REGION_INFO';
166 --
167 BEGIN
168 --
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
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);
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);
196 END IF;
197 --
198
199 Get_Region_Info (
200 p_country => p_country,
201 p_country_region => p_country_region,
202 p_state => p_state,
203 p_city => p_city,
204 p_postal_code_from => p_postal_code_from,
205 p_postal_code_to => p_postal_code_to,
206 p_zone => p_zone,
207 p_lang_code => p_lang_code,
208 p_country_code => p_country_code,
209 p_country_region_code=> p_country_region_code,
210 p_state_code => p_state_code,
211 p_city_code => p_city_code,
212 p_region_type => p_region_type,
213 p_interface_flag => p_interface_flag,
214 p_search_flag => 'N',
215 x_region_info => x_region_info);
216
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
226 --
227 -- Procedure: Get_Region_Info
228 --
229 -- Purpose: Obtains information of the region by matching all non-null
230 -- parameters that are passed in. If none, returns null in x_region_info
231 --
232
233 PROCEDURE Get_Region_Info (
234 p_country IN VARCHAR2,
235 p_country_region IN VARCHAR2,
236 p_state IN VARCHAR2,
237 p_city IN VARCHAR2,
238 p_postal_code_from IN VARCHAR2,
239 p_postal_code_to IN VARCHAR2,
240 p_zone IN VARCHAR2,
241 p_lang_code IN VARCHAR2,
242 p_country_code IN VARCHAR2,
243 p_country_region_code IN VARCHAR2,
244 p_state_code IN VARCHAR2,
245 p_city_code IN VARCHAR2,
246 p_region_type IN NUMBER,
247 p_interface_flag IN VARCHAR2,
248 p_search_flag IN VARCHAR2,
249 x_regions OUT NOCOPY region_table) IS
250
251 sql_string VARCHAR2(4000);
252 --Added for bug 11070829
253 temp_sql_string VARCHAR2(4000);
254 l_cnt NUMBER := 0;
255 --End 11070829
256
257 l_key VARCHAR2(32767);
258 v_dummy INTEGER;
259 l_state VARCHAR2(60);
260 l_state_code VARCHAR2(10);
261 l_city VARCHAR2(60);
262 l_city_code VARCHAR2(10);
263 l_region_info region_rec;
264 --Modified for bug 11070829
265 TYPE region_bind_type IS VARRAY(35) OF VARCHAR2(100);
266 region_bind region_bind_type := region_bind_type('','','','','','','','','','',
267 '','','','','','','','','','',
268 '','','','','','','','','','',
269 '','','','','');
270 cnt NUMBER := 0;
271
272 TYPE RegCurTyp IS REF CURSOR;
273 l_region_cur RegCurTyp;
274 l_return_status VARCHAR2(10);
275 l_region_type NUMBER := 0;
276
277 --
278 l_debug_on BOOLEAN;
279 --
280 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_REGION_INFO';
281 --
282 BEGIN
283
284 --
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
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);
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);
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,
317 p_state => p_state,
318 p_city => p_city,
319 p_postal_code_from => p_postal_code_from,
320 p_postal_code_to => p_postal_code_to,
321 p_zone => p_zone,
322 p_lang_code => p_lang_code,
323 p_country_code => p_country_code,
324 p_state_code => p_state_code,
325 p_city_code => p_city_code,
326 p_region_type => l_region_type,
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);
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
343
344 IF (p_postal_code_from IS NOT NULL OR p_postal_code_to IS NOT NULL) THEN
345 l_region_type := 3;
346 ELSIF (p_city IS NOT NULL OR p_city_code IS NOT NULL) THEN
347 l_region_type := 2;
348 ELSIF (p_state IS NOT NULL OR p_state_code IS NOT NULL) THEN
349 l_region_type := 1;
350 ELSIF (p_zone IS NOT NULL) THEN
351 l_region_type := 10;
352 END IF;
353 ELSE
354 l_region_type := p_region_type;
355 END IF;
356 --Bug 14103251 : Added code loop through the 4 combinations of state/state_code and city/coty_code
357 -- based on the number of characters.
358 -- First Iteration : To match with State and city From Regions
359 -- Second Iteration : To Match with State and City_code from Regions
360 -- only if length of city is <=3
361 -- Else continue to Iteration 3.
362 -- Third Iteration : To Match with State and City_code from Regions
363 -- only if length of state is <3
364 -- Else continue to Iteration 4.
365 -- Fourth Iteration : To Match with State_code and City_code from Regions
366 -- only if length of state is <3 and length of city is <=3
367 -- Else Exit.
368
369 for i in 1..4 LOOP
370 --{ Start of 4 Iteration Loop for checking State/Statecode and City/CityCode combination.
371 l_state := NULL;
372 l_state_code := NULL;
373 l_city := NULL;
374 l_city_code := NULL;
375 if i =1 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.
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;
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
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;
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
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;
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;
431
432 --Modified for bug 11070829
433 IF (p_postal_code_from IS NOT NULL) THEN
434 -- 12564197: sql_string := 'select /*+ index(Tl WSH_REGIONS_TL_N3) */ R.REGION_ID,
435 sql_string := 'select /*+ leading(tl) index(tl WSH_REGIONS_TL_N3) use_nl(tl, r) use_concat */ R.REGION_ID,
436 R.REGION_TYPE,
437 TL.COUNTRY,
438 TL.COUNTRY_REGION,
439 TL.STATE,
440 TL.CITY,
441 TL.POSTAL_CODE_FROM,
442 TL.POSTAL_CODE_TO,
443 TL.ZONE,
444 R.ZONE_LEVEL,
445 R.COUNTRY_CODE,
446 R.COUNTRY_REGION_CODE,
447 R.STATE_CODE,
448 R.CITY_CODE,
449 ''N'' ';
450 ELSE
451 -- 12564197: sql_string := 'select R.REGION_ID,
452 sql_string := 'select /*+ leading(tl) index(tl) use_concat */ R.REGION_ID,
453 R.REGION_TYPE,
454 TL.COUNTRY,
455 TL.COUNTRY_REGION,
456 TL.STATE,
457 TL.CITY,
458 TL.POSTAL_CODE_FROM,
459 TL.POSTAL_CODE_TO,
460 TL.ZONE,
461 R.ZONE_LEVEL,
462 R.COUNTRY_CODE,
463 R.COUNTRY_REGION_CODE,
464 R.STATE_CODE,
465 R.CITY_CODE,
466 ''N'' ';
467 END IF;
468 --End 11070829
469
470
471 IF (p_interface_flag = 'Y') THEN
472 sql_string := sql_string ||
473 ' from WSH_REGIONS_INTERFACE R, WSH_REGIONS_TL_INTERFACE TL';
474 ELSE
475 sql_string := sql_string ||
476 ' from WSH_REGIONS R, WSH_REGIONS_TL TL';
477 END IF;
478
479
480 -- sql_string := sql_string ||
481 -- ' where R.REGION_ID = TL.REGION_ID and ROWNUM < 2 ';
482 sql_string := sql_string ||
483 ' where R.REGION_ID = TL.REGION_ID ';
484
485 sql_string := sql_string ||
486 ' and R.REGION_TYPE = :region_type ';
487 cnt := cnt + 1;
488 region_bind(cnt) := l_region_type;
489
490 IF (p_country_code IS NOT NULL) THEN
491 sql_string := sql_string ||
492 ' AND UPPER(R.COUNTRY_CODE) = UPPER(:country_code)';
493 cnt := cnt + 1;
494 region_bind(cnt) := p_country_code;
495 END IF;
496
497 IF (l_state_code IS NOT NULL) THEN
498
499 --Bugfix 2877445 adding nvl for state when searching for city regions
500 -- Bug 14103251 : Removed nvl condition on state_Code.
501 IF (l_city_code IS NOT NULL OR l_city IS NOT NULL) THEN
502 sql_string := sql_string ||
503 ' and ( ( UPPER(R.STATE_CODE) = UPPER(:state_code) ) OR (T.STATE IS NULL))';
504 --cnt := cnt + 1;
505 --region_bind(cnt) := l_state_code;
506 ELSE
507 sql_string := sql_string ||
508 ' and UPPER(R.STATE_CODE) = UPPER(:state_code)';
509 END IF;
510
511 -- sql_string := sql_string ||
512 -- ' and UPPER(R.STATE_CODE) = UPPER(:state_code)';
513 cnt := cnt + 1;
514 region_bind(cnt) := l_state_code;
515
516 END IF;
517
518 IF (l_city_code IS NOT NULL) THEN
519
520
521 sql_string := sql_string ||
522 ' and UPPER(R.CITY_CODE) = UPPER(:city_code)';
523 cnt := cnt + 1;
524 region_bind(cnt) := l_city_code;
525 END IF;
526
527 IF (p_country IS NOT NULL) THEN
528
529 sql_string := sql_string ||
530 ' and UPPER(TL.COUNTRY) = UPPER(:country)';
531 cnt := cnt + 1;
532 region_bind(cnt) := p_country;
533
534 END IF;
535
536 IF (l_state IS NOT NULL) THEN
537
538
539 --Bugfix 2877445 adding nvl for state when searching for city regions
540
541 IF (l_city_code IS NOT NULL OR l_city IS NOT NULL) THEN
542 sql_string := sql_string ||
543 ' and NVL(UPPER(TL.state), UPPER(:state)) = UPPER(:state)';
544 cnt := cnt + 1;
545 region_bind(cnt) := l_state;
546 ELSE
547 sql_string := sql_string ||
548 ' and UPPER(TL.state) = UPPER(:state)';
549 END IF;
550
551 -- sql_string := sql_string ||
552 -- ' and UPPER(TL.state) = UPPER(:state)';
553 cnt := cnt + 1;
554 region_bind(cnt) := l_state;
555
556 END IF;
557
558 IF (l_city IS NOT NULL) THEN
559
560
561 sql_string := sql_string ||
562 ' and UPPER(TL.CITY) = UPPER(:city)';
563 cnt := cnt + 1;
564 region_bind(cnt) := l_city;
565 END IF;
566
567 IF (p_zone IS NOT NULL) THEN
568
569 sql_string := sql_string ||
570 ' and TL.zone = :zone';
571
572 cnt := cnt + 1;
573 region_bind(cnt) := p_zone;
574 END IF;
575
576 IF (p_lang_code IS NOT NULL) THEN
577
578 sql_string := sql_string ||
579 ' and TL.LANGUAGE = :lang_code ';
580 cnt := cnt + 1;
581 region_bind(cnt) := p_lang_code;
582
583 END IF;
584
585 -- both from and to have to be populated
586 --Modified for bug 11070829
587 temp_sql_string := sql_string;
588
589 IF (p_postal_code_from IS NOT NULL) THEN
590
591 -- check overlapping regions as well
592 IF (p_search_flag = 'N') THEN
593
594 sql_string := temp_sql_string ||
595 ' and ( :postal_code_from between TL.POSTAL_CODE_FROM and TL.POSTAL_CODE_TO) '||
596 ' UNION ALL '||
597 temp_sql_string ||
598 ' and ( :postal_code_to between TL.POSTAL_CODE_FROM and TL.POSTAL_CODE_TO ) '||
599 ' UNION ALL '||
600 temp_sql_string ||
601 ' and (TL.POSTAL_CODE_FROM between :postal_code_from AND :postal_code_to) ';
602
603
604 l_cnt := cnt;
605 cnt := cnt + 1;
606 region_bind(cnt) := p_postal_code_from;
607
608
609 for i in 1..l_cnt LOOP
610 cnt := cnt + 1;
611 region_bind(cnt) := region_bind(i);
612 END LOOP;
613 cnt := cnt + 1;
614 region_bind(cnt) := p_postal_code_to;
615
616
617 for i in 1..l_cnt LOOP
618 cnt := cnt + 1;
619 region_bind(cnt) := region_bind(i);
620 END LOOP;
621
622
623 cnt := cnt + 1;
624 region_bind(cnt) := p_postal_code_from;
625 cnt := cnt + 1;
626 region_bind(cnt) := p_postal_code_to;
627
628 ELSE
629 -- check only the exactly matching regions
630 sql_string := sql_string ||
631 ' and ( :postal_code_from = TL.POSTAL_CODE_FROM and :postal_code_to = TL.POSTAL_CODE_TO) ';
632
633
634 cnt := cnt + 1;
635 region_bind(cnt) := p_postal_code_from;
636 cnt := cnt + 1;
637 region_bind(cnt) := p_postal_code_to;
638 END IF;
639
640 END IF;
641
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;
651 end loop;
652 END IF;
653 IF (cnt = 0) THEN
654 OPEN l_region_cur FOR sql_string;
655 ELSIF (cnt = 1) THEN
656 OPEN l_region_cur FOR sql_string
657 USING region_bind(1);
658
659 ELSIF (cnt = 2) THEN
660 OPEN l_region_cur FOR sql_string
661 USING region_bind(1), region_bind(2);
662
663 ELSIF (cnt = 3) THEN
664 OPEN l_region_cur FOR sql_string
665 USING region_bind(1), region_bind(2), region_bind(3);
666
667 ELSIF (cnt = 4) THEN
668 OPEN l_region_cur FOR sql_string
669 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4);
670
671 ELSIF (cnt = 5) THEN
672 OPEN l_region_cur FOR sql_string
673 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5);
674
675 ELSIF (cnt = 6) THEN
676 OPEN l_region_cur FOR sql_string
677 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6);
678
679 ELSIF (cnt = 7) THEN
680 OPEN l_region_cur FOR sql_string
681 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7);
682
683 ELSIF (cnt = 8) THEN
684 OPEN l_region_cur FOR sql_string
685 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8);
686
687 ELSIF (cnt = 9) THEN
688 OPEN l_region_cur FOR sql_string
689 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9);
690
691 ELSIF (cnt = 10) THEN
692 OPEN l_region_cur FOR sql_string
693 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10);
694
695 ELSIF (cnt = 11) THEN
696 OPEN l_region_cur FOR sql_string
697 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11);
698
699 ELSIF (cnt = 12) THEN
700 OPEN l_region_cur FOR sql_string
701 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12);
702
703 ELSIF (cnt = 13) THEN
704 OPEN l_region_cur FOR sql_string
705 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13);
706
707 ELSIF (cnt = 14) THEN
708 OPEN l_region_cur FOR sql_string
709 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14);
710 --Added for bug 11070829
711 ELSIF (cnt = 15) THEN
712 OPEN l_region_cur FOR sql_string
713 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
714 region_bind(15);
715
716 ELSIF (cnt = 16) THEN
717 OPEN l_region_cur FOR sql_string
718 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
719 region_bind(15),region_bind(16);
720
721 ELSIF (cnt = 17) THEN
722 OPEN l_region_cur FOR sql_string
723 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
724 region_bind(15),region_bind(16),region_bind(17) ;
725
726 ELSIF (cnt = 18) THEN
727 OPEN l_region_cur FOR sql_string
728 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
729 region_bind(15),region_bind(16),region_bind(17),region_bind(18);
730
731 ELSIF (cnt = 19) THEN
732 OPEN l_region_cur FOR sql_string
733 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
734 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19);
735
736 ELSIF (cnt = 20) THEN
737 OPEN l_region_cur FOR sql_string
738 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
739 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20);
740
741 ELSIF (cnt = 21) THEN
742 OPEN l_region_cur FOR sql_string
743 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
744 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),region_bind(21);
745
746 ELSIF (cnt = 22) THEN
747 OPEN l_region_cur FOR sql_string
748 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
749 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
750 region_bind(21),region_bind(22);
751
752 ELSIF (cnt = 23) THEN
753 OPEN l_region_cur FOR sql_string
754 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
755 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
756 region_bind(21),region_bind(22),region_bind(23);
757
758 ELSIF (cnt = 24) THEN
759 OPEN l_region_cur FOR sql_string
760 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
761 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
762 region_bind(21),region_bind(22),region_bind(23),region_bind(24);
763
764 ELSIF (cnt = 25) THEN
765 OPEN l_region_cur FOR sql_string
766 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
767 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
768 region_bind(21),region_bind(22),region_bind(23),region_bind(24),region_bind(25);
769
770 ELSIF (cnt = 26) THEN
771 OPEN l_region_cur FOR sql_string
772 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
773 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
774 region_bind(21),region_bind(22),region_bind(23),region_bind(24),region_bind(25),region_bind(26);
775
776 ELSIF (cnt = 27) THEN
777 OPEN l_region_cur FOR sql_string
778 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
779 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
780 region_bind(21),region_bind(22),region_bind(23),region_bind(24),region_bind(25),region_bind(26),
781 region_bind(27);
782
783 ELSIF (cnt = 28) THEN
784 OPEN l_region_cur FOR sql_string
785 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
786 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
787 region_bind(21),region_bind(22),region_bind(23),region_bind(24),region_bind(25),region_bind(26),
788 region_bind(27),region_bind(28);
789
790 ELSIF (cnt = 29) THEN
791 OPEN l_region_cur FOR sql_string
792 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
793 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
794 region_bind(21),region_bind(22),region_bind(23),region_bind(24),region_bind(25),region_bind(26),
795 region_bind(27),region_bind(28),region_bind(29);
796
797 ELSIF (cnt = 30) THEN
798 OPEN l_region_cur FOR sql_string
799 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
800 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
801 region_bind(21),region_bind(22),region_bind(23),region_bind(24),region_bind(25),region_bind(26),
802 region_bind(27),region_bind(28),region_bind(29),region_bind(30);
803
804 ELSIF (cnt = 31) THEN
805 OPEN l_region_cur FOR sql_string
806 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
807 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
808 region_bind(21),region_bind(22),region_bind(23),region_bind(24),region_bind(25),region_bind(26),
809 region_bind(27),region_bind(28),region_bind(29),region_bind(30),region_bind(31);
810
811 ELSIF (cnt = 32) THEN
812 OPEN l_region_cur FOR sql_string
813 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
814 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
815 region_bind(21),region_bind(22),region_bind(23),region_bind(24),region_bind(25),region_bind(26),
816 region_bind(27),region_bind(28),region_bind(29),region_bind(30),region_bind(31),region_bind(32);
817
818 ELSIF (cnt = 33) THEN
819 OPEN l_region_cur FOR sql_string
820 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
821 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
822 region_bind(21),region_bind(22),region_bind(23),region_bind(24),region_bind(25),region_bind(26),
823 region_bind(27),region_bind(28),region_bind(29),region_bind(30),region_bind(31),region_bind(32),region_bind(33);
824
825 ELSIF (cnt = 34) THEN
826 OPEN l_region_cur FOR sql_string
827 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9),region_bind(10), region_bind(11), region_bind(12), region_bind(13), region_bind(14),
828 region_bind(15),region_bind(16),region_bind(17),region_bind(18), region_bind(19),region_bind(20),
829 region_bind(21),region_bind(22),region_bind(23),region_bind(24),region_bind(25),region_bind(26),
830 region_bind(27),region_bind(28),region_bind(29),region_bind(30),region_bind(31),region_bind(32),region_bind(33),region_bind(34);
831
832 END IF;
833 --End of Modifications for bug 11070829
834 cnt:=0;
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;
844
845 -- process record
846 END LOOP;
847 CLOSE l_region_cur;
848 IF x_regions.count = 0 THEN
849 l_region_info.region_id := -1;
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
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;
865 END IF;
866
867
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
877 l_region_info.region_id := -1;
878 x_regions(0) := l_region_info;
879
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');
889 WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
890 END IF;
891 --
892 END Get_Region_Info;
893
894 PROCEDURE Get_Region_Info (
895 p_country IN VARCHAR2,
896 p_country_region IN VARCHAR2,
897 p_state IN VARCHAR2,
898 p_city IN VARCHAR2,
899 p_postal_code_from IN VARCHAR2,
900 p_postal_code_to IN VARCHAR2,
901 p_zone IN VARCHAR2,
902 p_lang_code IN VARCHAR2,
903 p_country_code IN VARCHAR2,
904 p_country_region_code IN VARCHAR2,
905 p_state_code IN VARCHAR2,
906 p_city_code IN VARCHAR2,
907 p_region_type IN NUMBER,
908 p_interface_flag IN VARCHAR2,
909 p_search_flag IN VARCHAR2,
910 x_region_info OUT NOCOPY region_rec) IS
911
912 p_region_info region_table;
913
914 BEGIN
915 Get_Region_Info (
916 p_country => p_country,
917 p_country_region => p_country_region,
918 p_state => p_state,
919 p_city => p_city,
920 p_postal_code_from => p_postal_code_from,
921 p_postal_code_to => p_postal_code_to,
922 p_zone => p_zone,
923 p_lang_code => p_lang_code,
924 p_country_code => p_country_code,
925 p_country_region_code => p_country_region_code,
926 p_state_code => p_state_code,
927 p_city_code => p_city_code,
928 p_region_type => p_region_type,
929 p_interface_flag => p_interface_flag,
930 p_search_flag => p_search_flag,
931 x_regions => p_region_info);
932
933 x_region_info := p_region_info(0);
934
935 END;
936
937
938
939 --
940 -- Procedure: Get_Region_Info
941 --
942 -- Purpose: Obtains region_id only of the region by matching all non-null
943 -- parameters that are passed in. If none, returns -1 in x_region_id
944 --
945
946 PROCEDURE Get_Region_Info (
947 p_country IN VARCHAR2,
948 p_country_region IN VARCHAR2,
949 p_state IN VARCHAR2,
950 p_city IN VARCHAR2,
951 p_postal_code_from IN VARCHAR2,
952 p_postal_code_to IN VARCHAR2,
953 p_zone IN VARCHAR2,
954 p_lang_code IN VARCHAR2,
955 p_country_code IN VARCHAR2,
956 p_country_region_code IN VARCHAR2,
957 p_state_code IN VARCHAR2,
958 p_city_code IN VARCHAR2,
959 p_region_type IN NUMBER,
960 p_interface_flag IN VARCHAR2,
961 p_search_flag IN VARCHAR2,
962 p_recursively_flag IN VARCHAR2,
963 x_region_id OUT NOCOPY NUMBER) IS
964
965 l_region_info region_rec;
966
967 --
968 l_debug_on BOOLEAN;
969 --
970 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_REGION_INFO';
971 --
972 BEGIN
973
974 --
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
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);
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);
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,
1008 p_country_region => p_country_region,
1009 p_state => p_state,
1010 p_city => p_city,
1011 p_postal_code_from => p_postal_code_from,
1012 p_postal_code_to => p_postal_code_to,
1013 p_zone => p_zone,
1014 p_lang_code => p_lang_code,
1015 p_country_code => p_country_code,
1016 p_country_region_code => p_country_region_code,
1017 p_state_code => p_state_code,
1018 p_city_code => p_city_code,
1019 p_region_type => p_region_type,
1020 p_interface_flag => p_interface_flag,
1021 p_search_flag => p_search_flag,
1022 x_region_info => l_region_info);
1023
1024 IF (l_region_info.region_id is not null AND l_region_info.region_id <> -1) THEN
1025 x_region_id := l_region_info.region_id;
1026 ELSE
1027 x_region_id := -1;
1028 END IF;
1029
1030 -- if cannot find region and recursively, remove postal code and call again
1031 -- if postal codes exist, otherwise, do nothing
1032 IF (x_region_id = -1 AND p_recursively_flag = 'Y' AND p_postal_code_from is not null) THEN
1033
1034 Get_Region_Info (
1035 p_country => p_country,
1036 p_country_region => p_country_region,
1037 p_state => p_state,
1038 p_city => p_city,
1039 p_postal_code_from => null,
1040 p_postal_code_to => null,
1041 p_zone => p_zone,
1042 p_lang_code => p_lang_code,
1043 p_country_code => p_country_code,
1044 p_country_region_code => p_country_region_code,
1045 p_state_code => p_state_code,
1046 p_city_code => p_city_code,
1047 p_region_type => null,
1048 p_interface_flag => p_interface_flag,
1049 p_search_flag => p_search_flag,
1050 x_region_info => l_region_info);
1051
1052 IF (l_region_info.region_id is not null AND l_region_info.region_id <> -1) THEN
1053 x_region_id := l_region_info.region_id;
1054 ELSE
1055 x_region_id := -1;
1056 END IF;
1057
1058 END IF;
1059
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
1069 --
1070 -- Procedure: Get_Region_Id_Codes_Only
1071 --
1072 -- Purpose: Obtains information for the region by matching all non-null
1073 -- parameters that are passed in, against the non-tl table.
1074 -- If no region found, returns null in x_region_info
1075 --
1076
1077 PROCEDURE Get_Region_Id_Codes_Only (
1078 p_country_code IN VARCHAR2,
1079 p_country_region_code IN VARCHAR2,
1080 p_state_code IN VARCHAR2,
1081 p_city_code IN VARCHAR2,
1082 p_postal_code_from IN VARCHAR2,
1083 p_postal_code_to IN VARCHAR2,
1084 p_region_type IN NUMBER,
1085 p_interface_flag IN VARCHAR2,
1086 p_language_code IN VARCHAR2 DEFAULT NULL,
1087 x_region_id OUT NOCOPY NUMBER) IS
1088
1089 TYPE C_TYPE IS REF CURSOR;
1090 c_region C_TYPE;
1091
1092 sql_string VARCHAR2(4000);
1093
1094 l_region_id NUMBER;
1095
1096 TYPE region_bind_type IS VARRAY(15) OF VARCHAR2(100);
1097 region_bind region_bind_type := region_bind_type('','','','','','','','','','','','','','','');
1098 cnt NUMBER := 0;
1099
1100
1101 --
1102 l_debug_on BOOLEAN;
1103 --
1104 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_REGION_ID_CODES_ONLY';
1105 --
1106 BEGIN
1107
1108 --
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
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);
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;
1144
1145 sql_string := 'select R.REGION_ID';
1146
1147 IF (p_interface_flag = 'Y') THEN
1148 sql_string := sql_string ||
1149 ' from WSH_REGIONS_INTERFACE R ';
1150 ELSE
1151 sql_string := sql_string ||
1152 ' from WSH_REGIONS R ';
1153 END IF;
1154
1155
1156 IF (p_postal_code_from IS NOT NULL OR p_language_code IS NOT NULL) THEN
1157
1158 IF (p_interface_flag = 'Y') THEN
1159
1160 sql_string := sql_string ||
1161 ', WSH_REGIONS_TL_INTERFACE TL ';
1162
1163 ELSE
1164
1165 sql_string := sql_string ||
1166 ', WSH_REGIONS_TL TL ';
1167
1168 END IF;
1169
1170 END IF;
1171
1172 sql_string := sql_string ||
1173 ' where R.REGION_TYPE = :region_type ';
1174 cnt := cnt + 1;
1175 region_bind(cnt) := p_region_type;
1176
1177 IF (p_postal_code_from IS NOT NULL OR p_language_code IS NOT NULL) THEN
1178 sql_string := sql_string ||
1179 ' and R.REGION_ID = TL.REGION_ID ';
1180 END IF;
1181
1182 IF (p_country_code IS NOT NULL) THEN
1183 sql_string := sql_string ||
1184 ' AND UPPER(R.COUNTRY_CODE) = UPPER(:country_code)';
1185 cnt := cnt + 1;
1186 region_bind(cnt) := p_country_code;
1187 END IF;
1188
1189 IF (p_state_code IS NOT NULL) THEN
1190
1191 --Bugfix 2877445 adding nvl for state when searching for city regions
1192
1193 IF (p_city_code IS NOT NULL) THEN
1194 sql_string := sql_string ||
1195 ' and NVL(UPPER(R.STATE_CODE),UPPER(:state_code)) = UPPER(:state_code)';
1196 cnt := cnt + 1;
1197 region_bind(cnt) := p_state_code;
1198 ELSE
1199 sql_string := sql_string ||
1200 ' and UPPER(R.STATE_CODE) = UPPER(:state_code)';
1201 END IF;
1202
1203 -- sql_string := sql_string ||
1204 -- ' and UPPER(R.STATE_CODE) = UPPER(:state_code)';
1205 cnt := cnt + 1;
1206 region_bind(cnt) := p_state_code;
1207
1208 END IF;
1209
1210 IF (p_city_code IS NOT NULL) THEN
1211
1212 sql_string := sql_string ||
1213 ' and UPPER(R.CITY_CODE) = UPPER(:city_code)';
1214 cnt := cnt + 1;
1215 region_bind(cnt) := p_city_code;
1216 END IF;
1217
1218 -- both from and to have to be populated
1219
1220 IF (p_postal_code_from IS NOT NULL) THEN
1221
1222 -- check overlapping regions as well
1223 sql_string := sql_string ||
1224 ' and (( :postal_code_from between TL.POSTAL_CODE_FROM and TL.POSTAL_CODE_TO) '||
1225 ' or ( :postal_code_to between TL.POSTAL_CODE_FROM and TL.POSTAL_CODE_TO ) '||
1226 ' or (TL.POSTAL_CODE_FROM between :postal_code_from AND :postal_code_to)) ';
1227
1228 cnt := cnt + 1;
1229 region_bind(cnt) := p_postal_code_from;
1230 cnt := cnt + 1;
1231 region_bind(cnt) := p_postal_code_to;
1232 cnt := cnt + 1;
1233 region_bind(cnt) := p_postal_code_from;
1234 cnt := cnt + 1;
1235 region_bind(cnt) := p_postal_code_to;
1236
1237 END IF;
1238
1239 IF (p_language_code IS NOT NULL) THEN
1240
1241 sql_string := sql_string ||
1242 ' and TL.LANGUAGE = :lang_code ';
1243 cnt := cnt + 1;
1244 region_bind(cnt) := p_language_code;
1245
1246 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
1256 EXECUTE IMMEDIATE sql_string
1257 INTO l_region_id;
1258
1259 ELSIF (cnt = 1) THEN
1260 EXECUTE IMMEDIATE sql_string
1261 INTO l_region_id
1262 USING region_bind(1);
1263
1264 ELSIF (cnt = 2) THEN
1265 EXECUTE IMMEDIATE sql_string
1266 INTO l_region_id
1267 USING region_bind(1), region_bind(2);
1268
1269 ELSIF (cnt = 3) THEN
1270 EXECUTE IMMEDIATE sql_string
1271 INTO l_region_id
1272 USING region_bind(1), region_bind(2), region_bind(3);
1273
1274 ELSIF (cnt = 4) THEN
1275 EXECUTE IMMEDIATE sql_string
1276 INTO l_region_id
1277 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4);
1278
1279 ELSIF (cnt = 5) THEN
1280 EXECUTE IMMEDIATE sql_string
1281 INTO l_region_id
1282 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5);
1283
1284 ELSIF (cnt = 6) THEN
1285 EXECUTE IMMEDIATE sql_string
1286 INTO l_region_id
1287 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6);
1288
1289 ELSIF (cnt = 7) THEN
1290 EXECUTE IMMEDIATE sql_string
1291 INTO l_region_id
1292 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7);
1293
1294 ELSIF (cnt = 8) THEN
1295 EXECUTE IMMEDIATE sql_string
1296 INTO l_region_id
1297 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8);
1298
1299 ELSIF (cnt = 9) THEN
1300 EXECUTE IMMEDIATE sql_string
1301 INTO l_region_id
1302 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9);
1303
1304 ELSIF (cnt = 10) THEN
1305 EXECUTE IMMEDIATE sql_string
1306 INTO l_region_id
1307 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7), region_bind(8), region_bind(9), region_bind(10);
1308
1309 END IF;
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;
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
1329 x_region_id := -1;
1330
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;
1340
1341 --
1342 -- Procedure: Get_Region_Id_Codes_Only
1343 --
1344 -- Purpose: Obtains information for the region by calling overloaded get_regioN_id_codes_only
1345 -- method which matches all non-null parameters that are passed in, against the non-tl table.
1346 -- If region found, it checks if a tl entry exists in that language
1347 -- This is so that uniqueness is held for a region_id and language combination
1348 --
1349
1350 PROCEDURE Get_Region_Id_Codes_Only (
1351 p_country_code IN VARCHAR2,
1352 p_country_region_code IN VARCHAR2,
1353 p_state_code IN VARCHAR2,
1354 p_city_code IN VARCHAR2,
1355 p_postal_code_from IN VARCHAR2,
1356 p_postal_code_to IN VARCHAR2,
1357 p_region_type IN NUMBER,
1358 p_interface_flag IN VARCHAR2,
1359 p_lang_code IN VARCHAR2,
1360 x_region_id_non_tl OUT NOCOPY NUMBER,
1361 x_region_id_with_tl OUT NOCOPY NUMBER) IS
1362
1363 TYPE C_TYPE IS REF CURSOR;
1364 c_region C_TYPE;
1365
1366 sql_string VARCHAR2(4000);
1367
1368 l_region_id NUMBER;
1369 l_region_type NUMBER := 0;
1370
1371 --
1372 l_debug_on BOOLEAN;
1373 --
1374 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_REGION_ID_CODES_ONLY';
1375 --
1376 BEGIN
1377
1378 --
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
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);
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;
1414
1415 IF (p_region_type IS NULL) THEN
1416
1417 IF (p_postal_code_from IS NOT NULL OR p_postal_code_to IS NOT NULL) THEN
1418 l_region_type := 3;
1419 ELSIF (p_city_code IS NOT NULL) THEN
1420 l_region_type := 2;
1421 ELSIF (p_state_code IS NOT NULL) THEN
1422 l_region_type := 1;
1423 END IF;
1424 ELSE
1425 l_region_type := p_region_type;
1426 END IF;
1427
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 (
1437 p_country_code => p_country_code,
1438 p_country_region_code => p_country_region_code,
1439 p_state_code => p_state_code,
1440 p_city_code => p_city_code,
1441 p_postal_code_from => p_postal_code_from,
1442 p_postal_code_to => p_postal_code_to,
1443 p_region_type => l_region_type,
1444 p_interface_flag => p_interface_flag,
1445 x_region_id => x_region_id_non_tl);
1446
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
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;
1465
1466 Get_Region_Id_Codes_Only (
1467 p_country_code => p_country_code,
1468 p_country_region_code => p_country_region_code,
1469 p_state_code => p_state_code,
1470 p_city_code => p_city_code,
1471 p_postal_code_from => p_postal_code_from,
1472 p_postal_code_to => p_postal_code_to,
1473 p_region_type => l_region_type,
1474 p_interface_flag => p_interface_flag,
1475 p_language_code => p_lang_code,
1476 x_region_id => x_region_id_with_tl);
1477
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;
1487
1488
1489 --
1490 -- Function: Match_Location_Region
1491 --
1492 -- Purpose: Returns region id for region found when matching location
1493 -- to region
1494 --
1495
1496 FUNCTION Match_Location_Region (
1497 p_country IN VARCHAR2,
1498 p_country_code IN VARCHAR2,
1499 p_state IN VARCHAR2,
1500 p_city IN VARCHAR2,
1501 p_postal_code IN VARCHAR2,
1502 p_insert_flag IN VARCHAR2) RETURN NUMBER IS
1503
1504 l_region_info region_rec;
1505 l_region_type NUMBER := 0;
1506 l_region_rec1 region_rec;
1507 l_region_rec2 region_rec;
1508 l_country l_region_info.country%TYPE;
1509
1510 --
1511 l_debug_on BOOLEAN;
1512 --
1513 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MATCH_LOCATION_REGION';
1514 --
1515 BEGIN
1516
1517 --
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
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);
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;
1541
1542 -- this is done so that both country and country_code are not sent at same time
1543 IF (p_country_code IS NULL) THEN
1544 l_country := p_country;
1545 END IF;
1546
1547 --Bug 6670302 Removed the restriction on length of state and city
1548 l_region_info.state := p_state;
1549 l_region_info.city := p_city;
1550
1551
1552 l_region_info.postal_code_from := p_postal_code;
1553 l_region_info.postal_code_to := p_postal_code;
1554
1555 IF (p_postal_code IS NOT NULL) THEN
1556 l_region_type := 3;
1557 ELSIF (p_city IS NOT NULL) THEN
1558 l_region_type := 2;
1559 ELSIF (p_state IS NOT NULL) THEN
1560 l_region_type := 1;
1561 END IF;
1562
1563 Get_Region_Info (
1564 p_country => l_country,
1565 p_country_region => l_region_info.country_region,
1566 p_state => l_region_info.state,
1567 p_city => l_region_info.city,
1568 p_postal_code_from => l_region_info.postal_code_from,
1569 p_postal_code_to => l_region_info.postal_code_to,
1570 p_zone => null,
1571 --p_lang_code => null,
1572 p_lang_code => USERENV('LANG'),
1573 p_country_code => l_region_info.country_code,
1574 p_country_region_code => l_region_info.country_region_code,
1575 p_state_code => l_region_info.state_code,
1576 p_city_code => l_region_info.city_code,
1577 p_region_type => l_region_type,
1578 p_interface_flag => 'N',
1579 x_region_info => l_region_rec1);
1580
1581 IF (l_region_rec1.region_id = -1) THEN
1582
1583 IF (p_insert_flag = 'Y') THEN
1584
1585 Get_Region_Info (
1586 p_country => l_region_info.country,
1587 p_country_region => l_region_info.country_region,
1588 p_state => l_region_info.state,
1589 p_city => l_region_info.city,
1590 p_postal_code_from => l_region_info.postal_code_from,
1591 p_postal_code_to => l_region_info.postal_code_to,
1592 p_zone => null,
1593 --p_lang_code => null,
1594 p_lang_code => USERENV('LANG'),
1595 p_country_code => l_region_info.country_code,
1596 p_country_region_code => l_region_info.country_region_code,
1597 p_state_code => l_region_info.state_code,
1598 p_city_code => l_region_info.city_code,
1599 p_region_type => l_region_type,
1600 p_interface_flag => 'Y',
1601 x_region_info => l_region_rec2);
1602
1603 IF (l_region_rec2.region_id = -1) THEN
1604
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(
1614 p_country_code => l_region_info.country_code,
1615 p_country_region_code => l_region_info.country_region_code,
1616 p_state_code => l_region_info.state_code,
1617 p_city_code => l_region_info.city_code,
1618 p_port_flag => null,
1619 p_airport_flag => null,
1620 p_road_terminal_flag => null,
1621 p_rail_terminal_flag => null,
1622 p_longitude => null,
1623 p_latitude => null,
1624 p_timezone => null,
1625 p_continent => null,
1626 p_country => l_region_info.country,
1627 p_country_region => nvl(l_region_info.country_region, l_region_info.country_region_code),
1628 p_state => nvl(l_region_info.state,l_region_info.state_code),
1629 p_city => nvl(l_region_info.city,l_region_info.city_code),
1630 p_alternate_name => null,
1631 p_county => null,
1632 p_postal_code_from => l_region_info.postal_code_from,
1633 p_postal_code_to => l_region_info.postal_code_to,
1634 p_lang_code => nvl(userenv('LANG'),'US'),
1635 p_region_type => l_region_type,
1636 p_parent_region_id => null,
1637 p_interface_flag => 'Y',
1638 p_tl_only_flag => 'N',
1639 p_region_id => null,
1640 x_region_id => l_region_info.region_id);
1641
1642 END IF;
1643
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)
1653 ||' '
1654 ||l_region_info.postal_code_from);
1655 END IF;
1656 END IF;
1657
1658 IF (l_region_rec2.region_id is not null) then
1659 l_region_rec1.region_id := l_region_rec2.region_id;
1660 END IF;
1661
1662 END IF;
1663
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
1673 END Match_Location_Region;
1674
1675 --
1676 -- PROCEDURE : Map_Location_Region_Child
1677 --
1678 -- PURPOSE : Child program to Location to Region Mapping Concurrent request.
1679 -- Calls appropriate procedure depending upon value of mode parameter.
1680 -- New API added as a part of ECO - 4740786
1681
1682 PROCEDURE Map_Location_Region_Child(
1683 p_errbuf OUT NOCOPY VARCHAR2,
1684 p_retcode OUT NOCOPY NUMBER,
1685 p_mode IN VARCHAR2,
1686 p_location_type IN VARCHAR2,
1687 p_from_value IN NUMBER,
1688 p_to_value IN NUMBER,
1689 p_start_date IN VARCHAR2,
1690 p_end_date IN VARCHAR2,
1691 p_insert_flag IN VARCHAR2) IS
1692
1693
1694 CURSOR get_region_info(l_processing_date DATE) IS
1695 SELECT nvl(country, country_code) country,
1696 nvl(state,state_code) state,
1697 nvl(city,city_code) city,
1698 postal_code_from
1699 FROM wsh_regions_interface r, wsh_regions_tl_interface t
1700 WHERE r.region_id = t.region_id and r.creation_date >= l_processing_date
1701 ORDER BY country, state, city, postal_code_from;
1702
1703 CURSOR get_loc_info (l_start_date DATE, l_end_date DATE) IS
1704 SELECT t.territory_short_name, t.territory_code, nvl(l.state, l.province) state, l.city, l.postal_code
1705 FROM hz_locations l, fnd_territories_tl t
1706 WHERE t.territory_code = l.country and
1707 t.language = userenv('LANG') and
1708 l.last_update_date >= nvl(l_start_date, l.last_update_date) and
1709 l.last_update_date < nvl(l_end_date, l.last_update_date+1);
1710
1711 l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
1712 l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
1713 l_new_request_id NUMBER := 0;
1714 i NUMBER := 0;
1715 l_worker_min NUMBER := 0;
1716 l_worker_max NUMBER := 0;
1717 l_min NUMBER := 0;
1718 l_max NUMBER := 0;
1719 l_sqlcode NUMBER;
1720 l_sqlerr VARCHAR2(2000);
1721 l_return_status VARCHAR2(10);
1722 l_completion_status VARCHAR2(30);
1723 l_temp BOOLEAN;
1724 l_retcode NUMBER;
1725 l_errbuf VARCHAR2(2000);
1726 l_log_level NUMBER;
1727 l_num_of_instances NUMBER;
1728 l_location_type VARCHAR2(10);
1729 l_region_id NUMBER;
1730 l_num NUMBER;-- := p_number_processed;
1731 l_count NUMBER := 0;
1732 l_processing_date DATE;
1733 l_start_date DATE;
1734 l_end_date DATE;
1735
1736 l_debug_on BOOLEAN;
1737 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_LOCATION_REGION_CHILD';
1738
1739 BEGIN
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
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';
1758 ELSE
1759 l_location_type := p_location_type;
1760 END IF;
1761
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
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
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
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
1791 FOR loc IN get_loc_info (l_start_date, l_end_date) LOOP
1792
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,
1802 p_country_code => loc.territory_code,
1803 p_state => loc.state,
1804 p_city => loc.city,
1805 p_postal_code => loc.postal_code,
1806 p_insert_flag => p_insert_flag);
1807
1808 IF (l_region_id = -1) THEN
1809 l_count := l_count + 1;
1810 END IF;
1811
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,'------- ----- ---- ------ ----');
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
1827 ELSIF p_mode = 'MAP' THEN
1828
1829 WSH_MAP_LOCATION_REGION_PKG.Mapping_Regions_Main (
1830 p_location_type => l_location_type,
1831 p_from_location => p_from_value,
1832 p_to_location => p_to_value,
1833 p_start_date => p_start_date,
1834 p_end_date => p_end_date,
1835 p_insert_flag => FALSE,
1836 x_return_status => l_return_status,
1837 x_sqlcode => l_sqlcode,
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
1847 WSH_UTIL_CORE.printmsg('Failed in Procedure Mapping_Regions_Main');
1848 WSH_UTIL_CORE.printmsg(l_sqlcode);
1849 WSH_UTIL_CORE.printmsg(l_sqlerr);
1850 l_completion_status := 'ERROR';
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
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;
1871 l_sqlerr := SQLERRM;
1872
1873 WSH_UTIL_CORE.printmsg('Exception occurred in Map_Location_Region_Child Program');
1874 WSH_UTIL_CORE.printmsg('SQLCODE : ' || l_sqlcode);
1875 WSH_UTIL_CORE.printmsg('SQLERRM : ' || l_sqlerr);
1876
1877 l_completion_status := 'ERROR';
1878 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1879 p_errbuf := 'Exception occurred in Map_Location_Region_Child Program';
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
1889 --
1890 -- Procedure: Get_Child_Requests_Status
1891 --
1892 -- Purpose: Obtains the completion status of all the child requests
1893 -- and sets x_completion_status accordingly
1894 --
1895
1896 PROCEDURE Get_Child_Requests_Status
1897 (
1898 x_completion_status OUT NOCOPY VARCHAR2
1899 ) IS
1900
1901 CURSOR c_requests (p_parent_request_id NUMBER) IS
1902 SELECT request_id
1903 FROM FND_CONCURRENT_REQUESTS
1904 WHERE parent_request_id = p_parent_request_id
1905 AND NVL(is_sub_request, 'N') = 'Y';
1906
1907 l_child_req_ids WSH_UTIL_CORE.Id_Tab_Type;
1908 l_this_request NUMBER;
1909 l_errors NUMBER := 0;
1910 l_warnings NUMBER := 0;
1911 l_phase VARCHAR2(100);
1912 l_status VARCHAR2(100);
1913 l_dev_phase VARCHAR2(100);
1914 l_dev_status VARCHAR2(100);
1915 l_dummy BOOLEAN;
1916 j NUMBER;
1917 l_message VARCHAR2(2000);
1918 l_completion_status VARCHAR2(30);
1919 l_debug_on BOOLEAN;
1920 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CHILD_REQUESTS_STATUS';
1921 BEGIN
1922
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);
1932 END IF;
1933
1934 FND_PROFILE.Get('CONC_REQUEST_ID', l_this_request);
1935
1936 OPEN c_requests(l_this_request);
1937 FETCH c_requests BULK COLLECT INTO l_child_req_ids;
1938 CLOSE c_requests;
1939 l_errors := 0;
1940 l_warnings := 0;
1941 j := l_child_req_ids.FIRST;
1942 WHILE j IS NOT NULL LOOP
1943 l_dev_status := NULL;
1944 l_dummy := FND_CONCURRENT.get_request_status(
1945 request_id => l_child_req_ids(j),
1946 phase => l_phase,
1947 status => l_status,
1948 dev_phase => l_dev_phase,
1949 dev_status => l_dev_status,
1950 message => l_message);
1951
1952 IF l_dev_status = 'WARNING' THEN
1953 l_warnings:= l_warnings + 1;
1954 ELSIF l_dev_status <> 'NORMAL' THEN
1955 l_errors := l_errors + 1;
1956 END IF;
1957
1958 FND_MESSAGE.SET_NAME('WSH','WSH_CHILD_REQ_STATUS');
1959 FND_MESSAGE.SET_TOKEN('REQ_ID', to_char(l_child_req_ids(j)));
1960 FND_MESSAGE.SET_TOKEN('STATUS', l_status);
1961 WSH_UTIL_CORE.PrintMsg(FND_MESSAGE.GET);
1962 j := l_child_req_ids.NEXT(j);
1963 END LOOP;
1964
1965 IF l_errors = 0 AND l_warnings = 0 THEN
1966 l_completion_status := 'NORMAL';
1967 ELSIF (l_errors > 0 ) THEN
1968 l_completion_status := 'ERROR';
1969 ELSE
1970 l_completion_status := 'WARNING';
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');
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
1988 --
1989 -- Procedure: Process_All_Locations
1990 --
1991 -- Purpose: Calls Process_All_Locations with l_location_type='BOTH'
1992 --
1993 --
1994
1995 PROCEDURE Process_All_Locations (
1996 p_dummy1 IN VARCHAR2,
1997 p_dummy2 IN VARCHAR2,
1998 p_mode IN VARCHAR2 default g_mode,
1999 p_num_of_instances IN NUMBER,
2000 p_insert_flag IN VARCHAR2,
2001 p_start_date IN VARCHAR2,
2002 p_end_date IN VARCHAR2) IS
2003
2004 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_ALL_LOCATIONS';
2005 l_debug_on BOOLEAN;
2006
2007 BEGIN
2008
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);
2018 END IF;
2019
2020 --
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);
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);
2037 END IF;
2038
2039 WSH_REGIONS_SEARCH_PKG.Process_All_Locations(
2040 p_dummy1 => NULL,
2041 p_dummy2 => NULL,
2042 p_mode => p_mode,
2043 p_insert_flag => p_insert_flag,
2044 p_location_type => 'BOTH',
2045 p_start_date => p_start_date,
2046 p_end_date => p_end_date,
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
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
2065
2066 --
2067 -- Procedure: Process_All_Locations
2068 --
2069 -- Purpose: Overloaded procedure introduced as a part of ECO - 4740786.
2070 -- Returns region id for region found when matching location
2071 -- to region
2072 --
2073
2074 PROCEDURE Process_All_Locations (
2075 p_dummy1 IN VARCHAR2,
2076 p_dummy2 IN VARCHAR2,
2077 p_mode IN VARCHAR2 default g_mode,
2078 p_num_of_instances IN NUMBER,
2079 p_insert_flag IN VARCHAR2,
2080 p_location_type IN VARCHAR2,
2081 p_start_date IN VARCHAR2,
2082 p_end_date IN VARCHAR2
2083 ) IS
2084
2085 --
2086 l_debug_on BOOLEAN;
2087 l_return_status VARCHAR2(10);
2088 l_completion_status VARCHAR2(30);
2089
2090 l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
2091 l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
2092 l_new_request_id NUMBER := 0;
2093 i NUMBER := 0;
2094 l_temp BOOLEAN;
2095 l_retcode NUMBER;
2096 l_errbuf VARCHAR2(2000);
2097 l_num_of_instances NUMBER;
2098 l_req_data VARCHAR2(50);
2099 l_start_date DATE;
2100 l_end_date DATE;
2101 l_import_start_date DATE;
2102 l_start_date1 VARCHAR2(50);
2103 l_end_date1 VARCHAR2(50);
2104
2105 --
2106 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_ALL_LOCATIONS1';
2107 --
2108 BEGIN
2109
2110 --
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;
2120 l_completion_status := 'NORMAL';
2121 --
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);
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;
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;
2147 ELSE
2148 l_num_of_instances := p_num_of_instances;
2149 END IF;
2150
2151 -- ECO - 4740786 - based on the mode, obtain sets of records to be processed by the workers based on num_instances
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,
2161 p_retcode => l_retcode,
2162 p_mode => p_mode,
2163 p_location_type => p_location_type,
2164 p_from_value => NULL,
2165 p_to_value => NULL,
2166 p_start_date => p_start_date,
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';
2176 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2177 ELSIF l_retcode = '1' THEN
2178 l_completion_status := 'WARNING';
2179 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2180 END IF;
2181 --}
2182 ELSIF p_mode = 'MAP' THEN
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');
2192 l_req_data := SUBSTR(l_req_data, 1,1);
2193 END IF;
2194
2195
2196 IF l_import_start_date IS NOT NULL THEN
2197 l_start_date1 := to_char(l_import_start_date,'YYYY/MM/DD HH24:MI:SS');
2198 l_end_date1 := to_char(sysdate,'YYYY/MM/DD HH24:MI:SS');
2199 ELSE
2200 l_start_date1 := p_start_date;
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
2210 IF l_req_data IS NULL OR l_req_data = '1' THEN
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
2220 FROM HZ_LOCATIONS
2221 WHERE last_update_date >= nvl(:start_date, last_update_date)
2222 AND last_update_date < nvl(:end_date, last_update_date+1)
2223 )
2224 GROUP BY WORKER'
2225 BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
2226 USING l_num_of_instances, l_start_date, l_end_date;
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
2236 FROM HR_LOCATIONS_ALL
2237 WHERE last_update_date >= nvl(:start_date, last_update_date)
2238 AND last_update_date < nvl(:end_date, last_update_date+1)
2239 )
2240 GROUP BY WORKER'
2241 BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
2242 USING l_num_of_instances, l_start_date, l_end_date;
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
2252 FROM WSH_HR_LOCATIONS_V
2253 WHERE last_update_date >= nvl(:start_date, last_update_date)
2254 AND last_update_date < nvl(:end_date, last_update_date+1)
2255 )
2256 GROUP BY WORKER'
2257 BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
2258 USING l_num_of_instances, l_start_date, l_end_date;
2259
2260 --}
2261 END IF;
2262
2263 IF l_worker_min_tab.count <>0 and p_num_of_instances > 0 THEN
2264 --{
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
2274 l_new_request_id := FND_REQUEST.SUBMIT_REQUEST(
2275 application => 'WSH',
2276 program => 'WSHLRMCD',
2277 description => 'Location To Region Mapping - Child '||to_char(i),
2278 start_time => NULL,
2279 sub_request => TRUE,
2280 argument1 => p_mode,
2281 argument2 => p_location_type,
2282 argument3 => l_worker_min_tab(i),
2283 argument4 => l_worker_max_tab(i),
2284 argument5 => l_start_date1,
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
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
2299 END LOOP;
2300 FND_CONC_GLOBAL.Set_Req_Globals ( Conc_Status => 'PAUSED', Request_Data => to_char(2) );
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,
2310 p_retcode => l_retcode,
2311 p_mode => p_mode,
2312 p_location_type => p_location_type,
2313 p_from_value => l_worker_min_tab(1),
2314 p_to_value => l_worker_max_tab(1),
2315 p_start_date => l_start_date1,
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';
2325 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2326 ELSIF l_retcode = '1' THEN
2327 l_completion_status := 'WARNING';
2328 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2329 END IF;
2330 --}
2331 END IF;
2332 --}
2333 ELSE
2334 get_child_requests_status(x_completion_status => l_completion_status);
2335 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2336 END IF;
2337 --}
2338 END IF;
2339
2340 IF l_completion_status = 'NORMAL' THEN
2341 l_errbuf := 'Location To Region Mapping Program completed successfully';
2342 l_retcode := '0';
2343 ELSIF l_completion_status = 'WARNING' THEN
2344 l_errbuf := 'Location To Region Mapping Program is completed with warning';
2345 l_retcode := '1';
2346 ELSIF l_completion_status = 'ERROR' THEN
2347 l_errbuf := 'Location To Region Mapping Program is completed with error';
2348 l_retcode := '2';
2349 END IF;
2350
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
2360 EXCEPTION
2361 WHEN others THEN
2362 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Process_All_Locations');
2363 --
2364 IF l_debug_on THEN
2365 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2366 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2367 END IF;
2368
2369 END Process_All_Locations;
2370
2371 --
2372 -- PROCEDURE : refresh_cache
2373 --
2374 -- PURPOSE : Clears regions cache g_regions_info_tab if session id changes
2375 --
2376
2377 PROCEDURE refresh_cache (
2378 p_country_code IN VARCHAR2,
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;
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');
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
2403 g_regions_info_tab := g_emp_reg_info_tab ; --Bug 7313093 Instead of deleting assigning empty table
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
2413 --
2414 IF l_debug_on THEN
2415 wsh_debug_sv.pop (l_module_name);
2416 END IF;
2417 --
2418
2419 EXCEPTION
2420 WHEN others THEN
2421 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.refresh_cache');
2422 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2423 --
2424 IF l_debug_on THEN
2425 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2426 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2427 END IF;
2428 --
2429 END refresh_cache;
2430
2431
2432 --
2433 -- Procedure: Get_All_Region_Matches
2434 --
2435 -- Purpose: Obtains all information for a region, and its parents
2436 -- and the zones it belongs to
2437 --
2438
2439 PROCEDURE Get_All_Region_Matches (
2440 p_country IN VARCHAR2,
2441 p_country_region IN VARCHAR2,
2442 p_state IN VARCHAR2,
2443 p_city IN VARCHAR2,
2444 p_postal_code_from IN VARCHAR2,
2445 p_postal_code_to IN VARCHAR2,
2446 p_country_code IN VARCHAR2,
2447 p_country_region_code IN VARCHAR2,
2448 p_state_code IN VARCHAR2,
2449 p_city_code IN VARCHAR2,
2450 p_lang_code IN VARCHAR2,
2451 p_location_id IN NUMBER,
2452 p_zone_flag IN VARCHAR2,
2453 p_more_matches IN BOOLEAN DEFAULT FALSE,
2454 x_status OUT NOCOPY NUMBER,
2455 x_regions OUT NOCOPY region_table
2456 ) IS
2457
2458
2459 CURSOR region_details(l_region_id NUMBER,l_location_id NUMBER,l_lang_code IN VARCHAR2) IS
2460 SELECT wr.region_id,
2461 wr.region_type,
2462 wrt.country,
2463 wrt.country_region,
2464 wrt.state,
2465 wrt.city,
2466 wrt.postal_code_from,
2467 wrt.postal_code_to,
2468 wrt.zone,
2469 wr.zone_level,
2470 wr.country_code,
2471 wr.country_region_code,
2472 wr.state_code,
2473 wr.city_code,
2474 decode(loc.parent_region_flag,'Y','N','N','Y')
2475 FROM wsh_regions wr,
2476 wsh_regions_tl wrt,
2477 wsh_region_locations loc
2478 WHERE wr.region_id = l_region_id AND
2479 loc.location_id = l_location_id AND
2480 wr.region_id = loc.region_id AND
2481 wrt.region_id = wr.region_id AND
2482 wrt.language = nvl(l_lang_code,wrt.language);
2483
2484 CURSOR zone_info(l_region_id NUMBER, l_lang_code IN VARCHAR2) IS
2485 SELECT distinct r.region_id,
2486 r.region_type,
2487 null,
2488 null,
2489 null,
2490 null,
2491 null,
2492 null,
2493 rt.zone,
2494 r.zone_level,
2495 null,
2496 null,
2497 null,
2498 null,
2499 'N'
2500 FROM wsh_regions r,
2501 wsh_regions_tl rt,
2502 wsh_zone_regions z
2503 WHERE r.region_id = z.parent_region_id AND
2504 z.region_id = l_region_id AND
2505 r.region_type = 10 AND
2506 rt.region_id = r.region_id AND
2507 rt.language = nvl(l_lang_code,rt.language);
2508
2509 CURSOR Check_Location_Exists(c_location_id IN NUMBER,c_lang_code IN VARCHAR2) IS
2510 SELECT 'exists'
2511 FROM wsh_region_locations wrl, wsh_regions_tl wrt,
2512 wsh_regions wr
2513 WHERE wrl.region_id is not null
2514 AND wrl.location_id = c_location_id
2515 AND wrl.region_id = wrt.region_id
2516 AND wrt.language = nvl(c_lang_code,wrt.language)
2517 AND wrt.region_id = wr.region_id
2518 AND rownum = 1;
2519
2520 -- TODO
2521 -- get rid of wsh_hr_locations_v
2522
2523 -- Bugfix 2877445
2524 -- Added NVL function to region_1
2525 -- region_1 : County
2526 -- region_2 : State
2527 -- region_3 : Province
2528 /*
2529 CURSOR get_location_data IS
2530 SELECT country, nvl(region_2, region_3), town_or_city, postal_code
2531 FROM wsh_hr_locations_v
2532 WHERE location_id = p_location_id;
2533 */
2534
2535 -- TODO
2536 -- check against definition of wsh_hr_locations_v
2537 CURSOR get_location_data(c_location_id IN NUMBER) IS
2538 SELECT country, nvl(state, province), city, postal_code
2539 FROM wsh_locations
2540 WHERE wsh_location_id = c_location_id;
2541
2542
2543 j NUMBER := 0;
2544 i NUMBER := 0;
2545 cnt NUMBER := 0;
2546
2547 cnt_region NUMBER := 0;
2548 l_region_info region_rec;
2549 l_region_rec1 region_rec;
2550 l_region_rec2 region_rec;
2551 l_region_rec3 region_rec;
2552 l_region_type NUMBER := 0;
2553 l_parent_offset NUMBER ;
2554 l_exists VARCHAR2(10) := NULL;
2555 l_rgid_tab WSH_UTIL_CORE.Id_Tab_Type;
2556 is_first BOOLEAN := true;
2557 l_regions region_table;
2558 l_return_status VARCHAR2(1);
2559
2560 --
2561 l_debug_on BOOLEAN;
2562 --
2563 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ALL_REGION_MATCHES';
2564 --
2565 BEGIN
2566
2567 --
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
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);
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;
2595
2596
2597 IF g_regions_info_tab.count = 0 THEN
2598 g_country_code := p_country_code;
2599 END IF;
2600 --
2601 refresh_cache( p_country_code => p_country_code,
2602 x_return_status => l_return_status);
2603
2604 -- TODO
2605 -- should use p_location_id if passed in and should not
2606 -- search regions in that case
2607
2608 IF p_location_id IS NOT NULL THEN
2609 OPEN Check_Location_Exists(p_location_id,p_lang_code);
2610 FETCH Check_Location_Exists INTO l_exists;
2611 CLOSE Check_Location_Exists;
2612 END IF;
2613
2614 IF l_exists IS NOT NULL THEN
2615 /*
2616 SELECT wrl.region_id
2617 BULK COLLECT INTO l_rgid_tab
2618 FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
2619 WHERE wrl.location_id = p_location_id
2620 AND wrl.region_id is not null
2621 AND wrl.region_id = wrt.region_id
2622 AND wrt.language = nvl(p_lang_code,wrt.language)
2623 AND wrt.region_id = wr.region_id
2624 ORDER BY wrl.region_type DESC;
2625 */
2626
2627 Get_All_RegionId_Matches(
2628 p_location_id => p_location_id,
2629 p_use_cache => FALSE,
2630 p_lang_code => p_lang_code,
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
2640 j := l_rgid_tab.FIRST;
2641 LOOP
2642 OPEN region_details(l_rgid_tab(j),p_location_id,p_lang_code);
2643 FETCH region_details INTO l_region_info;
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;
2653 --
2654
2655 cnt_region := cnt_region + 1;
2656 x_regions(cnt_region) := l_region_info;
2657
2658 -- for every region, find any zone it belongs in
2659
2660 IF p_zone_flag = 'Y' THEN
2661
2662 OPEN zone_info(l_region_info.region_id,p_lang_code);
2663 LOOP
2664 FETCH zone_info INTO l_region_rec3;
2665
2666 EXIT WHEN zone_info%NOTFOUND;
2667
2668 IF (l_region_rec3.region_id > 0) THEN
2669 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;
2679
2680 END IF;
2681
2682 l_region_rec3.region_id := 0;
2683
2684 END LOOP;
2685
2686 CLOSE zone_info;
2687
2688 END IF;
2689
2690 EXIT WHEN j=l_rgid_tab.LAST;
2691 j := l_rgid_tab.NEXT(j);
2692 END LOOP;
2693 END IF;
2694
2695 ELSE -- if l_exists is null
2696 l_region_rec2.country := p_country;
2697 l_region_rec2.country_code := p_country_code;
2698 l_region_rec2.state := p_state;
2699 l_region_rec2.state_code := p_state_code;
2700 l_region_rec2.city := p_city;
2701 l_region_rec2.city_code := p_city_code;
2702 l_region_rec2.country_region := p_country_region;
2703 l_region_rec2.country_region_code := p_country_region_code;
2704 l_region_rec2.postal_code_from := p_postal_code_from;
2705 l_region_rec2.postal_code_to := p_postal_code_to;
2706
2707 IF (p_location_id is not null and p_country is null and p_country_code is null) THEN
2708
2709 OPEN get_location_data(p_location_id);
2710 FETCH get_location_data INTO l_region_rec2.country_code, l_region_rec2.state, l_region_rec2.city, l_region_rec2.postal_code_from;
2711
2712 l_region_rec2.postal_code_to := l_region_rec2.postal_code_from;
2713
2714 CLOSE get_location_data;
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
2724 -- TODO
2725 -- check that only one of country/state/city value
2726 -- OR code is passed always
2727
2728 IF (length(l_region_rec2.country) <= 3 AND l_region_rec2.country_code IS NULL) THEN
2729 l_region_rec2.country_code := l_region_rec2.country;
2730 l_region_rec2.country := null;
2731 END IF;
2732
2733 --Bug 6670302 Removed validations on the length of names of city and state
2734
2735 IF (l_region_rec2.postal_code_from IS NOT NULL OR l_region_rec2.postal_code_to IS NOT NULL) THEN
2736 l_region_type := 3;
2737 ELSIF (l_region_rec2.city IS NOT NULL OR l_region_rec2.city_code IS NOT NULL) THEN
2738 l_region_type := 2;
2739 ELSIF (l_region_rec2.state IS NOT NULL OR l_region_rec2.state_code IS NOT NULL) THEN
2740 l_region_type := 1;
2741 END IF;
2742
2743 LOOP
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;
2753
2754 cnt := cnt + 1;
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
2764 -- codes are unique in a country.
2765
2766 -- Bug 4722963
2767 -- If key exists region info in cache, fetch regions info from there
2768 -- otherwise call get_region_info API.
2769
2770 IF (l_region_type = 3) then
2771
2772 Get_Region_Info (
2773 p_country => l_region_rec2.country,
2774 p_country_region => l_region_rec2.country_region,
2775 p_state => null,
2776 p_city => null,
2777 p_postal_code_from => l_region_rec2.postal_code_from,
2778 p_postal_code_to => l_region_rec2.postal_code_to,
2779 p_zone => null,
2780 p_lang_code => p_lang_code,
2781 p_country_code => l_region_rec2.country_code,
2782 p_country_region_code=> l_region_rec2.country_region_code,
2783 p_state_code => null,
2784 p_city_code => null,
2785 p_region_type => l_region_type,
2786 p_interface_flag => 'N',
2787 x_regions => l_regions);
2788
2789 ELSE
2790
2791 Get_Region_Info (
2792 p_country => l_region_rec2.country,
2793 p_country_region => l_region_rec2.country_region,
2794 p_state => l_region_rec2.state,
2795 p_city => l_region_rec2.city,
2796 p_postal_code_from => l_region_rec2.postal_code_from,
2797 p_postal_code_to => l_region_rec2.postal_code_to,
2798 p_zone => null,
2799 p_lang_code => p_lang_code,
2800 p_country_code => l_region_rec2.country_code,
2801 p_country_region_code => l_region_rec2.country_region_code,
2802 p_state_code => l_region_rec2.state_code,
2803 p_city_code => l_region_rec2.city_code,
2804 p_region_type => l_region_type,
2805 p_interface_flag => 'N',
2806 x_regions => l_regions);
2807
2808
2809 END IF;
2810
2811 l_region_rec1 := l_regions(0);
2812 l_parent_offset := 1;
2813
2814 -- TODO
2815 -- Will call get_region_info for all address components always
2816
2817 IF (l_region_type > 0) THEN
2818
2819 -- figure out the parent region offset
2820 -- IF (l_region_type > 0) THEN
2821
2822 IF (l_region_type = 2 AND
2823 l_region_rec2.state IS NULL AND
2824 l_region_rec2.state_code IS NULL) THEN
2825 l_parent_offset := l_parent_offset + 1;
2826
2827 ELSE
2828 IF (l_region_type = 3 AND
2829 l_region_rec2.city IS NULL AND
2830 l_region_rec2.city_code IS NULL) THEN
2831 l_parent_offset := l_parent_offset + 1;
2832
2833 IF (l_region_rec2.state IS NULL AND
2834 l_region_rec2.state_code IS NULL) THEN
2835 l_parent_offset := l_parent_offset + 1;
2836 END IF;
2837
2838 END IF;
2839
2840 END IF;
2841
2842 -- END IF;
2843
2844 IF (l_region_type = 3) THEN
2845 l_region_rec2.postal_code_from := null;
2846 l_region_rec2.postal_code_to := null;
2847 ELSIF (l_region_type = 2) THEN
2848 l_region_rec2.city := null;
2849 l_region_rec2.city_code := null;
2850 ELSIF (l_region_type = 1) THEN
2851 l_region_rec2.state := null;
2852 l_region_rec2.state_code := null;
2853 END IF;
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
2863 l_region_type := l_region_type - l_parent_offset;
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
2882 l_region_rec1.is_input_type := 'Y'; -- Record is fetched based on the exact input match.
2883
2884 IF(l_regions.count > 1 AND p_more_matches) THEN
2885
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);
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;
2904
2905 END IF;
2906 x_regions(cnt_region) := l_region_rec1;
2907
2908 -- for every region, find any zone it belongs in
2909 IF p_zone_flag = 'Y' THEN
2910
2911 OPEN zone_info(l_region_rec1.region_id,p_lang_code);
2912
2913 LOOP
2914 FETCH zone_info INTO l_region_rec3;
2915
2916 EXIT WHEN zone_info%NOTFOUND;
2917
2918 IF (l_region_rec3.region_id > 0) THEN
2919 cnt_region := cnt_region + 1;
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;
2929
2930 END IF;
2931
2932 l_region_rec3.region_id := 0;
2933
2934 END LOOP;
2935
2936 CLOSE zone_info;
2937
2938 END IF;
2939
2940 END IF;
2941
2942 -- TODO
2943 -- Will call get_region_info for all address components always
2944 IF (l_region_type < 0) THEN
2945 EXIT;
2946 END IF;
2947 is_first := false;
2948 END LOOP;
2949
2950 END IF; -- l_exists is not null
2951
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 --
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
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;
2978 END Get_All_Region_Matches;
2979
2980
2981 --
2982 -- Procedure: Get_All_RegionId_Matches
2983 --
2984 -- Purpose : The API derives Region id for an input location,
2985 -- using table WSH_REGION_LOCATIONS
2986 -- Cache is used for storing and retriving location region mappings.
2987 -- when p_use_cache is FALSE
2988
2989 PROCEDURE Get_All_RegionId_Matches(
2990 p_location_id IN NUMBER,
2991 p_use_cache IN BOOLEAN DEFAULT FALSE,
2992 p_lang_code IN VARCHAR2,
2993 x_region_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
2994 x_return_status OUT NOCOPY VARCHAR2) IS
2995
2996
2997 CURSOR c_get_all_region_id(l_location_id IN NUMBER, l_lang_code IN VARCHAR2) IS
2998 SELECT wrl.region_id
2999 FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
3000 WHERE wrl.location_id = l_location_id
3001 AND wrl.region_id is not null
3002 AND wrl.region_id = wrt.region_id
3003 AND wrt.language = nvl(l_lang_code,wrt.language)
3004 AND wrt.region_id = wr.region_id
3005 ORDER BY wrl.region_type DESC;
3006
3007 --
3008 l_region_id_tab WSH_UTIL_CORE.Id_Tab_Type;
3009 itr NUMBER;
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
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
3027 IF (g_location_region_tab.EXISTS(p_location_id)) THEN
3028
3029
3030 WSH_UTIL_CORE.get_idtab_from_string(
3031 p_string => g_location_region_tab(p_location_id),
3032 x_id_tab => l_region_id_tab,
3033 x_return_status => l_return_status);
3034
3035 itr := l_region_id_tab.FIRST;
3036
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;
3046
3047 --Location not present in the cache or p_use_cache is false.
3048 --Use cursor to get details.
3049
3050 OPEN c_get_all_region_id(p_location_id,p_lang_code);
3051 FETCH c_get_all_region_id BULK COLLECT INTO l_region_id_tab;
3052 CLOSE c_get_all_region_id;
3053
3054 x_region_tab := l_region_id_tab;
3055 --
3056 -- If p_use_cache is True then add to g_location_region_tab
3057 -- Return l_region_id_tab.
3058 --
3059
3060 IF (p_use_cache) and (p_location_id < g_cache_max_size) THEN
3061
3062 itr := l_region_id_tab.FIRST;
3063
3064 IF (itr ) IS NULL THEN
3065 -- Table contains no value,Set l_region_id_string to -1
3066 -- Do not return l_region_id_tab;
3067 l_region_id_string := '-1';
3068
3069 ELSE
3070 -- Return l_region_id_tab
3071 WSH_UTIL_CORE.get_string_from_idtab(
3072 p_id_tab => l_region_id_tab,
3073 x_string => l_region_id_string,
3074 x_return_status => l_return_status);
3075 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
3085 IF c_get_all_region_id%ISOPEN THEN
3086 CLOSE c_get_all_region_id;
3087 END IF;
3088 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches');
3089 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3090 --
3091 IF l_debug_on THEN
3092 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
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);
3098
3099 END IF;
3100 --
3101 END Get_All_RegionId_Matches;
3102
3103 --
3104 -- Procedure: Get_All_Zone_Matches
3105 --
3106 -- Purpose : The API derives Zones for an input Region .
3107 -- A cache is used for the region to zone mapping.
3108 --
3109
3110 PROCEDURE Get_All_Zone_Matches(
3111 p_region_id IN NUMBER,
3112 x_zone_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
3113 x_return_status OUT NOCOPY VARCHAR2) IS
3114
3115 CURSOR c_get_zone_for_region(c_region_id IN NUMBER) IS
3116 SELECT wr.region_id
3117 FROM wsh_zone_regions wzr,wsh_regions wr
3118 WHERE wzr.region_id = c_region_id
3119 AND zone_flag = 'Y'
3120 AND wzr.parent_region_id = wr.region_id
3121 AND wr.region_type=10;
3122
3123 --
3124 l_zone_tab WSH_UTIL_CORE.Id_Tab_Type;
3125 l_zone_id_string VARCHAR2(32767);
3126 itr NUMBER;
3127 l_return_status VARCHAR2(1);
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;
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
3144 WSH_UTIL_CORE.get_idtab_from_string(
3145 p_string => g_region_zone_tab(p_region_id),
3146 x_id_tab => l_zone_tab,
3147 x_return_status => l_return_status);
3148
3149 itr := l_zone_tab.FIRST;
3150
3151 IF (l_zone_tab(itr) <> -1) THEN
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
3161 OPEN c_get_zone_for_region(p_region_id);
3162 FETCH c_get_zone_for_region BULK COLLECT INTO l_zone_tab;
3163 CLOSE c_get_zone_for_region;
3164
3165 x_zone_tab := l_zone_tab;
3166
3167 --
3168 -- Add zones to the global cache.
3169 -- If no zones are associated with the region
3170 -- 1) Store -1 in the cache.
3171 -- 2) x_zone_tab is NULL.
3172
3173
3174 IF (p_region_id < g_cache_max_size ) THEN
3175
3176 itr := l_zone_tab.FIRST;
3177
3178 IF (itr ) IS NULL THEN
3179 l_zone_id_string := '-1';
3180 ELSE
3181
3182 WSH_UTIL_CORE.get_string_from_idtab(
3183 p_id_tab => l_zone_tab,
3184 x_string => l_zone_id_string,
3185 x_return_status => l_return_status);
3186
3187 END IF;
3188
3189 g_region_zone_tab(p_region_id) := l_zone_id_string;
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
3199 IF c_get_zone_for_region%ISOPEN THEN
3200 CLOSE c_get_zone_for_region;
3201 END IF;
3202 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches');
3203 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3204 --
3205 IF l_debug_on THEN
3206 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
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
3212 --***************************************************************************--
3213 --========================================================================
3214 -- PROCEDURE : get_all_region_deconsols
3215 --
3216 -- PARAMETERS: p_location_id Input delivery record
3217 -- p_use_cache Whether to use cache
3218 -- p_lang_code Language Code
3219 -- p_zone_flag Whether to perform search at zone level as well
3220 -- p_rule_to_zone_id zone id specified in the consolidation rule
3221 -- p_caller Caller of API
3222 -- x_region_consol_tab Table of regions containing info
3223 -- about deconsol locations for given location
3224 -- x_return_status Return status
3225 -- COMMENT : This procedure is used to perform for following actions
3226 -- Takes input location id
3227 -- Finds deconsolidation location for the location as defined on Regions and
3228 -- zones form
3229 --========================================================================
3230
3231 PROCEDURE get_all_region_deconsols (
3232 p_location_id IN NUMBER,
3233 p_use_cache IN BOOLEAN DEFAULT FALSE,
3234 p_lang_code IN VARCHAR2,
3235 p_zone_flag IN BOOLEAN DEFAULT FALSE,
3236 p_rule_to_zone_id IN NUMBER DEFAULT NULL,
3237 p_caller IN VARCHAR2 DEFAULT NULL,
3238 x_region_consol_tab OUT NOCOPY region_deconsol_Tab_Type,
3239 x_return_status OUT NOCOPY VARCHAR2)
3240 IS
3241
3242 l_region_id NUMBER;
3243 itr NUMBER;
3244 r_itr NUMBER;
3245 l_region_tab region_deconsol_Tab_Type;
3246 l_region_deconsol_tab region_deconsol_Tab_Type;
3247 l_zone_deconsol_tab region_deconsol_Tab_Type;
3248 l_reg_zon_deconsol_tab region_deconsol_Tab_Type;
3249 l_return_status VARCHAR2(1);
3250 z_itr NUMBER;
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,
3260 wr.deconsol_location_id
3261 FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
3262 WHERE wrl.location_id = c_location_id
3263 AND wrl.region_id IS NOT NULL
3264 AND wrl.region_id = wrt.region_id
3265 AND wrt.language = nvl('US',wrt.language)
3266 AND wrt.region_id = wr.region_id
3267 --AND wr.deconsol_location_id IS NOT NULL
3268 ORDER BY wr.region_type DESC;
3269
3270 CURSOR c_get_zone_for_region(c_region_id IN NUMBER) IS
3271 SELECT wr.region_id,
3272 --null,
3273 wr.region_type,
3274 wr.deconsol_location_id
3275 FROM wsh_zone_regions wzr,wsh_regions wr
3276 WHERE wzr.region_id = c_region_id
3277 AND zone_flag = 'Y'
3278 AND wzr.parent_region_id = wr.region_id
3279 AND wr.region_type=10
3280 AND wr.deconsol_location_id IS NOT NULL
3281 ORDER BY wr.region_type DESC;
3282 BEGIN
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);
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
3296
3297 IF p_use_cache = TRUE AND p_location_id < g_cache_max_size THEN
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);
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);
3313 FETCH c_get_all_regions_loc BULK COLLECT INTO l_region_tab;
3314 CLOSE c_get_all_regions_loc;
3315 END IF;
3316 ELSIF (NOT g_loc_region_deconsol_tab.EXISTS(p_location_id)) OR p_use_cache = FALSE THEN
3317
3318 OPEN c_get_all_regions_loc(p_location_id,p_lang_code);
3319 FETCH c_get_all_regions_loc BULK COLLECT INTO l_region_tab;
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
3329 itr := l_region_tab.FIRST;
3330 IF itr IS NOT NULL THEN
3331 LOOP
3332 IF l_region_tab(itr).Deconsol_location IS NOT NULL THEN
3333 l_region_deconsol_tab(i) := l_region_tab(itr);
3334 END IF;
3335
3336 EXIT WHEN itr = l_region_tab.LAST;
3337 itr := l_region_tab.NEXT(itr);
3338 i := i+1;
3339 END LOOP;
3340 END IF;
3341 g_loc_region_deconsol_tab(p_location_id) := l_region_deconsol_tab;
3342 END IF;
3343
3344
3345 -- If search for zone level deconsolidation locations is true
3346 -- Loop through regions tab to find corresponding zone level deconsolidation locations
3347
3348 IF p_zone_flag = TRUE THEN
3349 rg_itr := 0;
3350 r_itr := l_region_tab.FIRST;
3351 IF r_itr IS NOT NULL THEN
3352 LOOP
3353 rg_itr := rg_itr+1;
3354 l_region_id := l_region_tab(r_itr).region_id;
3355
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
3365 IF p_use_cache = TRUE AND l_region_id < g_cache_max_size THEN
3366 IF (g_region_zone_deconsol_tab.EXISTS(l_region_id)) THEN
3367 l_zone_deconsol_tab := g_region_zone_deconsol_tab(l_region_id);
3368 --g_region_zone_deconsol_tab(l_region_id) := l_zone_deconsol_tab;
3369 ELSE
3370 OPEN c_get_zone_for_region(l_region_id);
3371 FETCH c_get_zone_for_region BULK COLLECT INTO l_zone_deconsol_tab;
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.
3390 --
3391 -- If caller is WMS: the zone level should be skipped completely if multiple
3392 -- zones found for any region.
3393 --
3394 IF p_rule_to_zone_id IS NOT NULL THEN
3395 IF NOT (p_caller like 'WMS%' AND l_zone_deconsol_tab.COUNT >1) THEN
3396 z_itr := l_zone_deconsol_tab.FIRST;
3397 IF z_itr IS NOT NULL THEN
3398 LOOP
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;
3408
3409 EXIT WHEN z_itr = l_zone_deconsol_tab.LAST;
3410 z_itr:= l_zone_deconsol_tab.NEXT(z_itr);
3411 END LOOP;
3412 END IF;
3413 END IF;
3414 ELSE
3415 z_itr := l_zone_deconsol_tab.FIRST;
3416 IF z_itr IS NOT NULL THEN
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;
3426 z_itr:= l_zone_deconsol_tab.NEXT(z_itr);
3427 END LOOP;
3428 END IF;
3429 END IF;
3430
3431 EXIT WHEN r_itr = l_region_tab.LAST;
3432 r_itr:= l_region_tab.NEXT(r_itr);
3433 END LOOP;
3434
3435 END IF;
3436 x_region_consol_tab := l_reg_zon_deconsol_tab;
3437
3438 IF p_zone_flag THEN
3439 g_loc_region_zone_deconsol_tab(p_location_id) := l_reg_zon_deconsol_tab;
3440 END IF;
3441 ELSE
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);
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
3459
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
3469 IF c_get_all_regions_loc%ISOPEN THEN
3470 CLOSE c_get_all_regions_loc;
3471 END IF;
3472 IF c_get_zone_for_region%ISOPEN THEN
3473 CLOSE c_get_zone_for_region;
3474 END IF;
3475 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.get_all_region_deconsols');
3476 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3477 --
3478 IF l_debug_on THEN
3479 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
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
3485 -- Following procedure are added for Regions Interface Performance
3486
3487 --
3488 -- PROCEDURE : Check_Region_Info
3489 --
3490 -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data
3491 -- table based on parameters passed to it.
3492 PROCEDURE Check_Region_Info (
3493 p_country IN VARCHAR2,
3494 p_state IN VARCHAR2,
3495 p_city IN VARCHAR2,
3496 p_postal_code_from IN VARCHAR2,
3497 p_postal_code_to IN VARCHAR2,
3498 p_region_type IN NUMBER,
3499 p_search_flag IN VARCHAR2,
3500 p_lang_code IN VARCHAR2,
3501 x_return_status OUT NOCOPY VARCHAR2,
3502 x_region_info OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec)
3503 IS
3504
3505 CURSOR C1 IS
3506 SELECT REGION_ID
3507 FROM WSH_REGIONS_GLOBAL_DATA
3508 WHERE ( city = UPPER(p_city) or p_city is null )
3509 AND ( state = UPPER(p_state) or p_state is null )
3510 AND country = UPPER(p_country)
3511 AND ( p_postal_code_from is null
3512 OR ( ( p_postal_code_from between postal_code_from and postal_code_to )
3513 OR ( p_postal_code_to between postal_code_from and postal_code_to ) ) )
3514 AND region_type = p_region_type
3515 AND language = p_lang_code;
3516
3517 CURSOR C2 IS
3518 SELECT REGION_ID
3519 FROM WSH_REGIONS_GLOBAL_DATA
3520 WHERE ( city = UPPER(p_city) )
3521 AND ( nvl(state, UPPER(p_state)) = UPPER(p_state) )
3522 AND country = UPPER(p_country)
3523 AND ( p_postal_code_from is null
3524 OR ( ( p_postal_code_from between postal_code_from and postal_code_to )
3525 OR ( p_postal_code_to between postal_code_from and postal_code_to ) ) )
3526 AND region_type = p_region_type
3527 AND language = p_lang_code;
3528
3529 TYPE tmp_table is table of NUMBER index by binary_integer;
3530 tmp_region_id tmp_table;
3531 t1 NUMBER;
3532
3533 --
3534 l_debug_on BOOLEAN;
3535 --
3536 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_REGION_INFO';
3537 --
3538 BEGIN
3539 --
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
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);
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;
3564
3565 t1 := dbms_utility.get_time;
3566
3567 IF ( p_state is not null and p_city is not null )
3568 THEN
3569 OPEN C2;
3570 FETCH C2 BULK COLLECT INTO TMP_REGION_ID;
3571 CLOSE C2;
3572 ELSE
3573 OPEN C1;
3574 FETCH C1 BULK COLLECT INTO TMP_REGION_ID;
3575 CLOSE C1;
3576 END IF;
3577
3578 IF ( TMP_REGION_ID.COUNT > 0 ) THEN
3579 x_region_info.region_id := TMP_REGION_ID(1);
3580 ELSE
3581 x_region_info.region_id := -1;
3582 END IF;
3583
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 --
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
3601 EXCEPTION
3602 WHEN OTHERS THEN
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;
3612
3613 --
3614 -- PROCEDURE : Check_Region_Info_Code
3615 --
3616 -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data
3617 -- table based on parameters passed to it.
3618 PROCEDURE Check_Region_Info_Code (
3619 p_country IN VARCHAR2,
3620 p_state IN VARCHAR2,
3621 p_city IN VARCHAR2,
3622 p_country_code IN VARCHAR2,
3623 p_state_code IN VARCHAR2,
3624 p_city_code IN VARCHAR2,
3625 p_region_type IN NUMBER,
3626 p_search_flag IN VARCHAR2,
3627 p_lang_code IN VARCHAR2,
3628 x_return_status OUT NOCOPY VARCHAR2,
3629 x_region_info OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec)
3630 IS
3631
3632 CURSOR C1 IS
3633 SELECT REGION_ID
3634 FROM WSH_REGIONS_GLOBAL_DATA
3635 WHERE ( city = UPPER(p_city) or p_city is null )
3636 AND ( decode(p_city, null, state, nvl(state, UPPER(p_state))) = UPPER(p_state)
3637 OR ( p_state is null ) )
3638 AND country = UPPER(p_country)
3639 AND ( city_code = UPPER(p_city_code) or p_city_code is null )
3640 AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
3641 OR ( p_state_code is null ) )
3642 AND country_code = UPPER(p_country_code)
3643 AND region_type = p_region_type
3644 AND language = p_lang_code;
3645
3646 TYPE tmp_table is table of NUMBER index by binary_integer;
3647 tmp_region_id tmp_table;
3648
3649 t1 NUMBER;
3650
3651 --
3652 l_debug_on BOOLEAN;
3653 --
3654 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_REGION_INFO_CODE';
3655 --
3656 BEGIN
3657 --
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
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);
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;
3683
3684 t1 := dbms_utility.get_time;
3685 OPEN C1;
3686 FETCH C1 BULK COLLECT INTO TMP_REGION_ID;
3687 CLOSE C1;
3688
3689 IF ( TMP_REGION_ID.COUNT > 0 ) THEN
3690 x_region_info.region_id := TMP_REGION_ID(1);
3691 ELSE
3692 x_region_info.region_id := -1;
3693 END IF;
3694
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 --
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
3712 EXCEPTION
3713 WHEN OTHERS THEN
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;
3723
3724 --
3725 -- PROCEDURE : Check_Region_Id_Codes_Only
3726 --
3727 -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data/
3728 -- Wsh_Regions_Global table based on parameters passed to it.
3729
3730 PROCEDURE Check_Region_Id_Codes_Only (
3731 p_country_code IN VARCHAR2,
3732 p_state_code IN VARCHAR2,
3733 p_city_code IN VARCHAR2,
3734 p_postal_code_from IN VARCHAR2,
3735 p_postal_code_to IN VARCHAR2,
3736 p_region_type IN NUMBER,
3737 p_language_code IN VARCHAR2 DEFAULT NULL,
3738 x_return_status OUT NOCOPY VARCHAR2,
3739 x_region_id OUT NOCOPY NUMBER )
3740 IS
3741 CURSOR C1 IS
3742 SELECT REGION_ID
3743 FROM WSH_REGIONS_GLOBAL_DATA
3744 WHERE ( city_code = UPPER(p_city_code) or p_city_code is null )
3745 AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
3746 OR ( p_state_code is null ) )
3747 AND country_code = UPPER(p_country_code)
3748 AND ( p_postal_code_from is null
3749 OR ( ( p_postal_code_from between postal_code_from and postal_code_to )
3750 OR ( p_postal_code_to between postal_code_from and postal_code_to ) ) )
3751 AND region_type = p_region_type
3752 AND language = nvl(p_language_code, language);
3753
3754 CURSOR C2 IS
3755 SELECT REGION_ID
3756 FROM WSH_REGIONS_GLOBAL
3757 WHERE ( city_code = UPPER(p_city_code) or p_city_code is null )
3758 AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
3759 OR ( p_state_code is null ) )
3760 AND country_code = UPPER(p_country_code)
3761 AND region_type = p_region_type;
3762
3763 TYPE tmp_table is table of NUMBER index by binary_integer;
3764 tmp_region_id tmp_table;
3765
3766 l_with_tl_flag BOOLEAN DEFAULT FALSE;
3767 l_non_tl_flag BOOLEAN DEFAULT FALSE;
3768 t1 NUMBER;
3769
3770 --
3771 l_debug_on BOOLEAN;
3772 --
3773 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_REGION_ID_CODES_ONLY';
3774 --
3775 BEGIN
3776 --
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
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);
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;
3800
3801 IF ( ( p_country_code is null and p_state_code is null and p_city_code is null)
3802 OR ( p_region_type = 2 and p_city_code is null )
3803 OR ( p_region_type = 1 and p_state_code is null )
3804 OR ( p_region_type = 0 and p_country_code is null ) )
3805 THEN
3806 x_region_id := -1;
3807 return;
3808 END IF;
3809
3810 IF ( p_postal_code_from is not null or p_language_code is not null ) THEN
3811 l_with_tl_flag := TRUE;
3812 ELSE
3813 l_non_tl_flag := TRUE;
3814 END IF;
3815
3816 IF ( l_with_tl_flag ) THEN
3817 t1 := dbms_utility.get_time;
3818 OPEN C1;
3819 FETCH C1 BULK COLLECT INTO TMP_REGION_ID;
3820 CLOSE C1;
3821
3822 IF ( TMP_REGION_ID.COUNT > 0 ) THEN
3823 x_region_id := TMP_REGION_ID(1);
3824 ELSE
3825 x_region_id := -1;
3826 END IF;
3827 ELSE
3828 t1 := dbms_utility.get_time;
3829 OPEN C2;
3830 FETCH C2 BULK COLLECT INTO TMP_REGION_ID;
3831 CLOSE C2;
3832
3833 IF ( TMP_REGION_ID.COUNT > 0 ) THEN
3834 x_region_id := TMP_REGION_ID(1);
3835 ELSE
3836 x_region_id := -1;
3837 END IF;
3838
3839 END IF;
3840
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 --
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));
3858 -- Exception Handling part
3859 EXCEPTION
3860 WHEN OTHERS THEN
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;
3870
3871
3872 END WSH_REGIONS_SEARCH_PKG;