[Home] [Help]
PACKAGE BODY: APPS.WSH_REGIONS_SEARCH_PKG
Source
1 PACKAGE BODY WSH_REGIONS_SEARCH_PKG AS
2 /* $Header: WSHRESEB.pls 120.12.12010000.2 2008/08/29 13:21:51 selsubra 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
228 --
229 -- Procedure: Get_Region_Info
230 --
231 -- Purpose: Obtains information of the region by matching all non-null
232 -- parameters that are passed in. If none, returns null in x_region_info
233 --
234
235 PROCEDURE Get_Region_Info (
236 p_country IN VARCHAR2,
237 p_country_region IN VARCHAR2,
238 p_state IN VARCHAR2,
239 p_city IN VARCHAR2,
240 p_postal_code_from IN VARCHAR2,
241 p_postal_code_to IN VARCHAR2,
242 p_zone IN VARCHAR2,
243 p_lang_code IN VARCHAR2,
244 p_country_code IN VARCHAR2,
245 p_country_region_code IN VARCHAR2,
246 p_state_code IN VARCHAR2,
247 p_city_code IN VARCHAR2,
248 p_region_type IN NUMBER,
249 p_interface_flag IN VARCHAR2,
250 p_search_flag IN VARCHAR2,
251 x_regions OUT NOCOPY region_table) IS
252
253 sql_string VARCHAR2(4000);
254 l_key VARCHAR2(32767);
255 v_dummy INTEGER;
256 l_region_info region_rec;
257 TYPE region_bind_type IS VARRAY(15) OF VARCHAR2(100);
258 region_bind region_bind_type := region_bind_type('','','','','','','','','','','','','','','');
259 cnt NUMBER := 0;
260
261 TYPE RegCurTyp IS REF CURSOR;
262 l_region_cur RegCurTyp;
263 l_return_status VARCHAR2(10);
264 l_region_type NUMBER := 0;
265
266 --
267 l_debug_on BOOLEAN;
268 --
269 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_REGION_INFO';
270 --
271 BEGIN
272
273 --
274 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
275 --
276 IF l_debug_on IS NULL
277 THEN
278 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
279 END IF;
280 --
281 --
282 -- Debug Statements
283 --
284 IF l_debug_on THEN
285 WSH_DEBUG_SV.push(l_module_name);
286 --
287 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
288 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
289 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
290 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
291 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
292 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
293 WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
294 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
295 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
296 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
297 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
298 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
299 WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
300 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
301 WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
302 END IF;
303 --
304 get_key(
305 p_country => p_country,
306 p_state => p_state,
307 p_city => p_city,
308 p_postal_code_from => p_postal_code_from,
309 p_postal_code_to => p_postal_code_to,
310 p_zone => p_zone,
311 p_lang_code => p_lang_code,
312 p_country_code => p_country_code,
313 p_state_code => p_state_code,
314 p_city_code => p_city_code,
315 p_region_type => l_region_type,
316 x_key => l_key,
317 x_return_status => l_return_status);
318
319 IF l_debug_on THEN
320 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from get_key : '|| l_return_status);
321 END IF;
322
323 IF g_regions_info_tab.EXISTS(l_key) THEN
324 x_regions := g_regions_info_tab(l_key);
325 IF l_debug_on THEN
326 wsh_debug_sv.log(l_module_name, 'used cache for key', l_key);
327 wsh_debug_sv.log(l_module_name, 'x_regions.count', x_regions.count);
328 END IF;
329 ELSE
330
331 IF (p_region_type IS NULL) THEN
332
333 IF (p_postal_code_from IS NOT NULL OR p_postal_code_to IS NOT NULL) THEN
334 l_region_type := 3;
335 ELSIF (p_city IS NOT NULL OR p_city_code IS NOT NULL) THEN
336 l_region_type := 2;
337 ELSIF (p_state IS NOT NULL OR p_state_code IS NOT NULL) THEN
338 l_region_type := 1;
339 ELSIF (p_zone IS NOT NULL) THEN
340 l_region_type := 10;
341 END IF;
342 ELSE
343 l_region_type := p_region_type;
344 END IF;
345
346 sql_string := 'select R.REGION_ID,
347 R.REGION_TYPE,
348 TL.COUNTRY,
349 TL.COUNTRY_REGION,
350 TL.STATE,
351 TL.CITY,
352 TL.POSTAL_CODE_FROM,
353 TL.POSTAL_CODE_TO,
354 TL.ZONE,
355 R.ZONE_LEVEL,
356 R.COUNTRY_CODE,
357 R.COUNTRY_REGION_CODE,
358 R.STATE_CODE,
359 R.CITY_CODE,
360 ''N'' ';
361
362 IF (p_interface_flag = 'Y') THEN
363 sql_string := sql_string ||
364 ' from WSH_REGIONS_INTERFACE R, WSH_REGIONS_TL_INTERFACE TL';
365 ELSE
366 sql_string := sql_string ||
367 ' from WSH_REGIONS R, WSH_REGIONS_TL TL';
368 END IF;
369
370 -- sql_string := sql_string ||
371 -- ' where R.REGION_ID = TL.REGION_ID and ROWNUM < 2 ';
372 sql_string := sql_string ||
373 ' where R.REGION_ID = TL.REGION_ID ';
374
375 sql_string := sql_string ||
376 ' and R.REGION_TYPE = :region_type ';
377 cnt := cnt + 1;
378 region_bind(cnt) := l_region_type;
379
380 IF (p_country_code IS NOT NULL) THEN
381 sql_string := sql_string ||
382 ' AND UPPER(R.COUNTRY_CODE) = UPPER(:country_code)';
383 cnt := cnt + 1;
384 region_bind(cnt) := p_country_code;
385 END IF;
386
387 IF (p_state_code IS NOT NULL) THEN
388
389 --Bugfix 2877445 adding nvl for state when searching for city regions
390
391 IF (p_city_code IS NOT NULL OR p_city IS NOT NULL) THEN
392 sql_string := sql_string ||
393 ' and NVL(UPPER(R.STATE_CODE),UPPER(:state_code)) = UPPER(:state_code)';
394 cnt := cnt + 1;
395 region_bind(cnt) := p_state_code;
396 ELSE
397 sql_string := sql_string ||
398 ' and UPPER(R.STATE_CODE) = UPPER(:state_code)';
399 END IF;
400
401 -- sql_string := sql_string ||
402 -- ' and UPPER(R.STATE_CODE) = UPPER(:state_code)';
403 cnt := cnt + 1;
404 region_bind(cnt) := p_state_code;
405
406 END IF;
407
408 IF (p_city_code IS NOT NULL) THEN
409
410
411 sql_string := sql_string ||
412 ' and UPPER(R.CITY_CODE) = UPPER(:city_code)';
413 cnt := cnt + 1;
414 region_bind(cnt) := p_city_code;
415 END IF;
416
417 IF (p_country IS NOT NULL) THEN
418
419 sql_string := sql_string ||
420 ' and UPPER(TL.COUNTRY) = UPPER(:country)';
421 cnt := cnt + 1;
422 region_bind(cnt) := p_country;
423
424 END IF;
425
426 IF (p_state IS NOT NULL) THEN
427
428
429 --Bugfix 2877445 adding nvl for state when searching for city regions
430
431 IF (p_city_code IS NOT NULL OR p_city IS NOT NULL) THEN
432 sql_string := sql_string ||
433 ' and NVL(UPPER(TL.state), UPPER(:state)) = UPPER(:state)';
434 cnt := cnt + 1;
435 region_bind(cnt) := p_state;
436 ELSE
437 sql_string := sql_string ||
438 ' and UPPER(TL.state) = UPPER(:state)';
439 END IF;
440
441 -- sql_string := sql_string ||
442 -- ' and UPPER(TL.state) = UPPER(:state)';
443 cnt := cnt + 1;
444 region_bind(cnt) := p_state;
445
446 END IF;
447
448 IF (p_city IS NOT NULL) THEN
449
450
451 sql_string := sql_string ||
452 ' and UPPER(TL.CITY) = UPPER(:city)';
453 cnt := cnt + 1;
454 region_bind(cnt) := p_city;
455 END IF;
456
457 -- both from and to have to be populated
458
459 IF (p_postal_code_from IS NOT NULL) THEN
460
461 -- check overlapping regions as well
462 IF (p_search_flag = 'N') THEN
463
464 sql_string := sql_string ||
465 ' and (( :postal_code_from between TL.POSTAL_CODE_FROM and TL.POSTAL_CODE_TO) '||
466 ' or ( :postal_code_to between TL.POSTAL_CODE_FROM and TL.POSTAL_CODE_TO ) '||
467 ' or (TL.POSTAL_CODE_FROM between :postal_code_from AND :postal_code_to)) ';
468
469 cnt := cnt + 1;
470 region_bind(cnt) := p_postal_code_from;
471 cnt := cnt + 1;
472 region_bind(cnt) := p_postal_code_to;
473 cnt := cnt + 1;
474 region_bind(cnt) := p_postal_code_from;
475 cnt := cnt + 1;
476 region_bind(cnt) := p_postal_code_to;
477 ELSE
478 -- check only the exactly matching regions
479 sql_string := sql_string ||
480 ' and ( :postal_code_from = TL.POSTAL_CODE_FROM and :postal_code_to = TL.POSTAL_CODE_TO) ';
481
482 cnt := cnt + 1;
483 region_bind(cnt) := p_postal_code_from;
484 cnt := cnt + 1;
485 region_bind(cnt) := p_postal_code_to;
486 END IF;
487
488 END IF;
489
490 IF (p_zone IS NOT NULL) THEN
491
492 sql_string := sql_string ||
493 ' and TL.zone = :zone';
494
495 cnt := cnt + 1;
496 region_bind(cnt) := p_zone;
497 END IF;
498
499 IF (p_lang_code IS NOT NULL) THEN
500
501 sql_string := sql_string ||
502 ' and TL.LANGUAGE = :lang_code ';
503 cnt := cnt + 1;
504 region_bind(cnt) := p_lang_code;
505
506 END IF;
507
508 -- Debug Statements
509 --
510 IF l_debug_on THEN
511 WSH_DEBUG_SV.logmsg(l_module_name, 'Get_Region_Id:: sql_string_length: ' || length(sql_string));
512 for i IN 1..length(sql_string) loop
513 if mod(i,100) = 0 then
514 WSH_DEBUG_SV.logmsg(l_module_name, substr(sql_string,i, 100));
515 end if;
516 end loop;
517 END IF;
518 IF (cnt = 0) THEN
519 OPEN l_region_cur FOR sql_string;
520 ELSIF (cnt = 1) THEN
521 OPEN l_region_cur FOR sql_string
522 USING region_bind(1);
523
524 ELSIF (cnt = 2) THEN
525 OPEN l_region_cur FOR sql_string
526 USING region_bind(1), region_bind(2);
527
528 ELSIF (cnt = 3) THEN
529 OPEN l_region_cur FOR sql_string
530 USING region_bind(1), region_bind(2), region_bind(3);
531
532 ELSIF (cnt = 4) THEN
533 OPEN l_region_cur FOR sql_string
534 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4);
535
536 ELSIF (cnt = 5) THEN
537 OPEN l_region_cur FOR sql_string
538 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5);
539
540 ELSIF (cnt = 6) THEN
541 OPEN l_region_cur FOR sql_string
542 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6);
543
544 ELSIF (cnt = 7) THEN
545 OPEN l_region_cur FOR sql_string
546 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7);
547
548 ELSIF (cnt = 8) THEN
549 OPEN l_region_cur FOR sql_string
550 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);
551
552 ELSIF (cnt = 9) THEN
553 OPEN l_region_cur FOR sql_string
554 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);
555
556 ELSIF (cnt = 10) THEN
557 OPEN l_region_cur FOR sql_string
558 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);
559
560 ELSIF (cnt = 11) THEN
561 OPEN l_region_cur FOR sql_string
562 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);
563
564 ELSIF (cnt = 12) THEN
565 OPEN l_region_cur FOR sql_string
566 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);
567
568 ELSIF (cnt = 13) THEN
569 OPEN l_region_cur FOR sql_string
570 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);
571
572 ELSIF (cnt = 14) THEN
573 OPEN l_region_cur FOR sql_string
574 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);
575
576 END IF;
577 cnt:=0;
578 LOOP
579 FETCH l_region_cur INTO l_region_info;
580 EXIT WHEN l_region_cur%NOTFOUND;
581 IF l_debug_on THEN
582 WSH_DEBUG_SV.logmsg(l_module_name, ' fetching region info of '||l_region_info.region_id);
583 WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt);
584 END IF;
585 x_regions(cnt) := l_region_info;
586 cnt :=cnt + 1;
587
588 -- process record
589 END LOOP;
590 CLOSE l_region_cur;
591 IF x_regions.count = 0 THEN
592 l_region_info.region_id := -1;
593 x_regions(0) := l_region_info;
594 END IF;
595 g_regions_info_tab(l_key) := x_regions;
596 END IF;
597
598
599 --
600 -- Debug Statements
601 --
602 IF l_debug_on THEN
603 WSH_DEBUG_SV.pop(l_module_name);
604 END IF;
605 --
606 EXCEPTION
607 WHEN OTHERS THEN
608 l_region_info.region_id := -1;
609 x_regions(0) := l_region_info;
610
611 --
612 -- Debug Statements
613 --
614 IF l_debug_on THEN
615 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
616 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
617 --Bug 4775798
618 ELSE
619 WSH_UTIL_CORE.printmsg('Unexpected error has occured in Get_Region_Info');
620 WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
621 END IF;
622 --
623 END Get_Region_Info;
624
625 PROCEDURE Get_Region_Info (
626 p_country IN VARCHAR2,
627 p_country_region IN VARCHAR2,
628 p_state IN VARCHAR2,
629 p_city IN VARCHAR2,
630 p_postal_code_from IN VARCHAR2,
631 p_postal_code_to IN VARCHAR2,
632 p_zone IN VARCHAR2,
633 p_lang_code IN VARCHAR2,
634 p_country_code IN VARCHAR2,
635 p_country_region_code IN VARCHAR2,
636 p_state_code IN VARCHAR2,
637 p_city_code IN VARCHAR2,
638 p_region_type IN NUMBER,
639 p_interface_flag IN VARCHAR2,
640 p_search_flag IN VARCHAR2,
641 x_region_info OUT NOCOPY region_rec) IS
642
643 p_region_info region_table;
644
645 BEGIN
646 Get_Region_Info (
647 p_country => p_country,
648 p_country_region => p_country_region,
649 p_state => p_state,
650 p_city => p_city,
651 p_postal_code_from => p_postal_code_from,
652 p_postal_code_to => p_postal_code_to,
653 p_zone => p_zone,
654 p_lang_code => p_lang_code,
655 p_country_code => p_country_code,
656 p_country_region_code => p_country_region_code,
657 p_state_code => p_state_code,
658 p_city_code => p_city_code,
659 p_region_type => p_region_type,
660 p_interface_flag => p_interface_flag,
661 p_search_flag => p_search_flag,
662 x_regions => p_region_info);
663
664 x_region_info := p_region_info(0);
665
666 END;
667
668
669
670 --
671 -- Procedure: Get_Region_Info
672 --
673 -- Purpose: Obtains region_id only of the region by matching all non-null
674 -- parameters that are passed in. If none, returns -1 in x_region_id
675 --
676
677 PROCEDURE Get_Region_Info (
678 p_country IN VARCHAR2,
679 p_country_region IN VARCHAR2,
680 p_state IN VARCHAR2,
681 p_city IN VARCHAR2,
682 p_postal_code_from IN VARCHAR2,
683 p_postal_code_to IN VARCHAR2,
684 p_zone IN VARCHAR2,
685 p_lang_code IN VARCHAR2,
686 p_country_code IN VARCHAR2,
687 p_country_region_code IN VARCHAR2,
688 p_state_code IN VARCHAR2,
689 p_city_code IN VARCHAR2,
690 p_region_type IN NUMBER,
691 p_interface_flag IN VARCHAR2,
692 p_search_flag IN VARCHAR2,
693 p_recursively_flag IN VARCHAR2,
694 x_region_id OUT NOCOPY NUMBER) IS
695
696 l_region_info region_rec;
697
698 --
699 l_debug_on BOOLEAN;
700 --
701 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_REGION_INFO';
702 --
703 BEGIN
704
705 --
706 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
707 --
708 IF l_debug_on IS NULL
709 THEN
710 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
711 END IF;
712 --
713 --
714 -- Debug Statements
715 --
716 IF l_debug_on THEN
717 WSH_DEBUG_SV.push(l_module_name);
718 --
719 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
720 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
721 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
722 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
723 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
724 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
725 WSH_DEBUG_SV.log(l_module_name,'P_ZONE',P_ZONE);
726 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
727 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
728 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
729 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
730 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
731 WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
732 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
733 WSH_DEBUG_SV.log(l_module_name,'P_SEARCH_FLAG',P_SEARCH_FLAG);
734 WSH_DEBUG_SV.log(l_module_name,'P_RECURSIVELY_FLAG',P_RECURSIVELY_FLAG);
735 END IF;
736 --
737 Get_Region_Info (
738 p_country => p_country,
739 p_country_region => p_country_region,
740 p_state => p_state,
741 p_city => p_city,
742 p_postal_code_from => p_postal_code_from,
743 p_postal_code_to => p_postal_code_to,
744 p_zone => p_zone,
745 p_lang_code => p_lang_code,
746 p_country_code => p_country_code,
747 p_country_region_code => p_country_region_code,
748 p_state_code => p_state_code,
749 p_city_code => p_city_code,
750 p_region_type => p_region_type,
751 p_interface_flag => p_interface_flag,
752 p_search_flag => p_search_flag,
753 x_region_info => l_region_info);
754
755 IF (l_region_info.region_id is not null AND l_region_info.region_id <> -1) THEN
756 x_region_id := l_region_info.region_id;
757 ELSE
758 x_region_id := -1;
759 END IF;
760
761 -- if cannot find region and recursively, remove postal code and call again
762 -- if postal codes exist, otherwise, do nothing
763 IF (x_region_id = -1 AND p_recursively_flag = 'Y' AND p_postal_code_from is not null) THEN
764
765 Get_Region_Info (
766 p_country => p_country,
767 p_country_region => p_country_region,
768 p_state => p_state,
769 p_city => p_city,
770 p_postal_code_from => null,
771 p_postal_code_to => null,
772 p_zone => p_zone,
773 p_lang_code => p_lang_code,
774 p_country_code => p_country_code,
775 p_country_region_code => p_country_region_code,
776 p_state_code => p_state_code,
777 p_city_code => p_city_code,
778 p_region_type => null,
779 p_interface_flag => p_interface_flag,
780 p_search_flag => p_search_flag,
781 x_region_info => l_region_info);
782
783 IF (l_region_info.region_id is not null AND l_region_info.region_id <> -1) THEN
784 x_region_id := l_region_info.region_id;
785 ELSE
786 x_region_id := -1;
787 END IF;
788
789 END IF;
790
791 --
792 -- Debug Statements
793 --
794 IF l_debug_on THEN
795 WSH_DEBUG_SV.pop(l_module_name);
796 END IF;
797 --
798 END Get_Region_Info;
799
800 --
801 -- Procedure: Get_Region_Id_Codes_Only
802 --
803 -- Purpose: Obtains information for the region by matching all non-null
804 -- parameters that are passed in, against the non-tl table.
805 -- If no region found, returns null in x_region_info
806 --
807
808 PROCEDURE Get_Region_Id_Codes_Only (
809 p_country_code IN VARCHAR2,
810 p_country_region_code IN VARCHAR2,
811 p_state_code IN VARCHAR2,
812 p_city_code IN VARCHAR2,
813 p_postal_code_from IN VARCHAR2,
814 p_postal_code_to IN VARCHAR2,
815 p_region_type IN NUMBER,
816 p_interface_flag IN VARCHAR2,
817 p_language_code IN VARCHAR2 DEFAULT NULL,
818 x_region_id OUT NOCOPY NUMBER) IS
819
820 TYPE C_TYPE IS REF CURSOR;
821 c_region C_TYPE;
822
823 sql_string VARCHAR2(4000);
824
825 l_region_id NUMBER;
826
827 TYPE region_bind_type IS VARRAY(15) OF VARCHAR2(100);
828 region_bind region_bind_type := region_bind_type('','','','','','','','','','','','','','','');
829 cnt NUMBER := 0;
830
831 --
832 l_debug_on BOOLEAN;
833 --
834 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_REGION_ID_CODES_ONLY';
835 --
836 BEGIN
837
838 --
839 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
840 --
841 IF l_debug_on IS NULL
842 THEN
843 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
844 END IF;
845 --
846 --
847 -- Debug Statements
848 --
849 IF l_debug_on THEN
850 WSH_DEBUG_SV.push(l_module_name);
851 --
852 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
853 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
854 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
855 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
856 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
857 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
858 WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
859 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
860 WSH_DEBUG_SV.log(l_module_name,'P_LANGUAGE_CODE',P_LANGUAGE_CODE);
861 END IF;
862 --
863 IF (p_country_code IS NULL AND p_state_code IS NULL AND p_city_code IS NULL ) THEN
864 l_region_id := -1;
865 --
866 -- Debug Statements
867 --
868 IF l_debug_on THEN
869 WSH_DEBUG_SV.pop(l_module_name);
870 END IF;
871 --
872 return;
873 END IF;
874
875 sql_string := 'select R.REGION_ID';
876
877 IF (p_interface_flag = 'Y') THEN
878 sql_string := sql_string ||
879 ' from WSH_REGIONS_INTERFACE R ';
880 ELSE
881 sql_string := sql_string ||
882 ' from WSH_REGIONS R ';
883 END IF;
884
885
886 IF (p_postal_code_from IS NOT NULL OR p_language_code IS NOT NULL) THEN
887
888 IF (p_interface_flag = 'Y') THEN
889
890 sql_string := sql_string ||
891 ', WSH_REGIONS_TL_INTERFACE TL ';
892
893 ELSE
894
895 sql_string := sql_string ||
896 ', WSH_REGIONS_TL TL ';
897
898 END IF;
899
900 END IF;
901
902 sql_string := sql_string ||
903 ' where R.REGION_TYPE = :region_type ';
904 cnt := cnt + 1;
905 region_bind(cnt) := p_region_type;
906
907 IF (p_postal_code_from IS NOT NULL OR p_language_code IS NOT NULL) THEN
908 sql_string := sql_string ||
909 ' and R.REGION_ID = TL.REGION_ID ';
910 END IF;
911
912 IF (p_country_code IS NOT NULL) THEN
913 sql_string := sql_string ||
914 ' AND UPPER(R.COUNTRY_CODE) = UPPER(:country_code)';
915 cnt := cnt + 1;
916 region_bind(cnt) := p_country_code;
917 END IF;
918
919 IF (p_state_code IS NOT NULL) THEN
920
921 --Bugfix 2877445 adding nvl for state when searching for city regions
922
923 IF (p_city_code IS NOT NULL) THEN
924 sql_string := sql_string ||
925 ' and NVL(UPPER(R.STATE_CODE),UPPER(:state_code)) = UPPER(:state_code)';
926 cnt := cnt + 1;
927 region_bind(cnt) := p_state_code;
928 ELSE
929 sql_string := sql_string ||
930 ' and UPPER(R.STATE_CODE) = UPPER(:state_code)';
931 END IF;
932
933 -- sql_string := sql_string ||
934 -- ' and UPPER(R.STATE_CODE) = UPPER(:state_code)';
935 cnt := cnt + 1;
936 region_bind(cnt) := p_state_code;
937
938 END IF;
939
940 IF (p_city_code IS NOT NULL) THEN
941
942 sql_string := sql_string ||
943 ' and UPPER(R.CITY_CODE) = UPPER(:city_code)';
944 cnt := cnt + 1;
945 region_bind(cnt) := p_city_code;
946 END IF;
947
948 -- both from and to have to be populated
949
950 IF (p_postal_code_from IS NOT NULL) THEN
951
952 -- check overlapping regions as well
953 sql_string := sql_string ||
954 ' and (( :postal_code_from between TL.POSTAL_CODE_FROM and TL.POSTAL_CODE_TO) '||
955 ' or ( :postal_code_to between TL.POSTAL_CODE_FROM and TL.POSTAL_CODE_TO ) '||
956 ' or (TL.POSTAL_CODE_FROM between :postal_code_from AND :postal_code_to)) ';
957
958 cnt := cnt + 1;
959 region_bind(cnt) := p_postal_code_from;
960 cnt := cnt + 1;
961 region_bind(cnt) := p_postal_code_to;
962 cnt := cnt + 1;
963 region_bind(cnt) := p_postal_code_from;
964 cnt := cnt + 1;
965 region_bind(cnt) := p_postal_code_to;
966
967 END IF;
968
969 IF (p_language_code IS NOT NULL) THEN
970
971 sql_string := sql_string ||
972 ' and TL.LANGUAGE = :lang_code ';
973 cnt := cnt + 1;
974 region_bind(cnt) := p_language_code;
975
976 END IF;
977
978 -- Debug Statements
979 --
980 IF l_debug_on THEN
981 WSH_DEBUG_SV.logmsg(l_module_name, 'Get_Region_Id:: sql_string_length: ' || length(sql_string));
982 WSH_DEBUG_SV.logmsg(l_module_name, substr(sql_string,101, 200));
983 END IF;
984
985 IF (cnt = 0) THEN
986 EXECUTE IMMEDIATE sql_string
987 INTO l_region_id;
988
989 ELSIF (cnt = 1) THEN
990 EXECUTE IMMEDIATE sql_string
991 INTO l_region_id
992 USING region_bind(1);
993
994 ELSIF (cnt = 2) THEN
995 EXECUTE IMMEDIATE sql_string
996 INTO l_region_id
997 USING region_bind(1), region_bind(2);
998
999 ELSIF (cnt = 3) THEN
1000 EXECUTE IMMEDIATE sql_string
1001 INTO l_region_id
1002 USING region_bind(1), region_bind(2), region_bind(3);
1003
1004 ELSIF (cnt = 4) THEN
1005 EXECUTE IMMEDIATE sql_string
1006 INTO l_region_id
1007 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4);
1008
1009 ELSIF (cnt = 5) THEN
1010 EXECUTE IMMEDIATE sql_string
1011 INTO l_region_id
1012 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5);
1013
1014 ELSIF (cnt = 6) THEN
1015 EXECUTE IMMEDIATE sql_string
1016 INTO l_region_id
1017 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6);
1018
1019 ELSIF (cnt = 7) THEN
1020 EXECUTE IMMEDIATE sql_string
1021 INTO l_region_id
1022 USING region_bind(1), region_bind(2), region_bind(3), region_bind(4), region_bind(5), region_bind(6), region_bind(7);
1023
1024 ELSIF (cnt = 8) THEN
1025 EXECUTE IMMEDIATE sql_string
1026 INTO l_region_id
1027 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);
1028
1029 ELSIF (cnt = 9) THEN
1030 EXECUTE IMMEDIATE sql_string
1031 INTO l_region_id
1032 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);
1033
1034 ELSIF (cnt = 10) THEN
1035 EXECUTE IMMEDIATE sql_string
1036 INTO l_region_id
1037 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);
1038
1039 END IF;
1040 --
1041 -- Debug Statements
1042 --
1043 IF l_debug_on THEN
1044 WSH_DEBUG_SV.logmsg(l_module_name, 'sql_string:: ' || sql_string);
1045 WSH_DEBUG_SV.logmsg(l_module_name, 'region_id = ' || l_region_id);
1046 END IF;
1047
1048 x_region_id := l_region_id;
1049
1050 --
1051 -- Debug Statements
1052 --
1053 IF l_debug_on THEN
1054 WSH_DEBUG_SV.pop(l_module_name);
1055 END IF;
1056 --
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 x_region_id := -1;
1060
1061 --
1062 -- Debug Statements
1063 --
1064 IF l_debug_on THEN
1065 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1066 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1067 END IF;
1068 --
1069 END Get_Region_Id_Codes_Only;
1070
1071 --
1072 -- Procedure: Get_Region_Id_Codes_Only
1073 --
1074 -- Purpose: Obtains information for the region by calling overloaded get_regioN_id_codes_only
1075 -- method which matches all non-null parameters that are passed in, against the non-tl table.
1076 -- If region found, it checks if a tl entry exists in that language
1077 -- This is so that uniqueness is held for a region_id and language combination
1078 --
1079
1080 PROCEDURE Get_Region_Id_Codes_Only (
1081 p_country_code IN VARCHAR2,
1082 p_country_region_code IN VARCHAR2,
1083 p_state_code IN VARCHAR2,
1084 p_city_code IN VARCHAR2,
1085 p_postal_code_from IN VARCHAR2,
1086 p_postal_code_to IN VARCHAR2,
1087 p_region_type IN NUMBER,
1088 p_interface_flag IN VARCHAR2,
1089 p_lang_code IN VARCHAR2,
1090 x_region_id_non_tl OUT NOCOPY NUMBER,
1091 x_region_id_with_tl OUT NOCOPY NUMBER) IS
1092
1093 TYPE C_TYPE IS REF CURSOR;
1094 c_region C_TYPE;
1095
1096 sql_string VARCHAR2(4000);
1097
1098 l_region_id NUMBER;
1099 l_region_type NUMBER := 0;
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_LANG_CODE',P_LANG_CODE);
1131 END IF;
1132 --
1133 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
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 IF (p_region_type IS NULL) THEN
1146
1147 IF (p_postal_code_from IS NOT NULL OR p_postal_code_to IS NOT NULL) THEN
1148 l_region_type := 3;
1149 ELSIF (p_city_code IS NOT NULL) THEN
1150 l_region_type := 2;
1151 ELSIF (p_state_code IS NOT NULL) THEN
1152 l_region_type := 1;
1153 END IF;
1154 ELSE
1155 l_region_type := p_region_type;
1156 END IF;
1157
1158 --
1159 -- Debug Statements
1160 --
1161 IF l_debug_on THEN
1162 WSH_DEBUG_SV.logmsg(l_module_name, 'getting non-tl id '||x_region_id_non_tl);
1163 WSH_DEBUG_SV.logmsg(l_module_name, 'country code '||p_country_code);
1164 END IF;
1165
1166 Get_Region_Id_Codes_Only (
1167 p_country_code => p_country_code,
1168 p_country_region_code => p_country_region_code,
1169 p_state_code => p_state_code,
1170 p_city_code => p_city_code,
1171 p_postal_code_from => p_postal_code_from,
1172 p_postal_code_to => p_postal_code_to,
1173 p_region_type => l_region_type,
1174 p_interface_flag => p_interface_flag,
1175 x_region_id => x_region_id_non_tl);
1176
1177 --
1178 -- Debug Statements
1179 --
1180 IF l_debug_on THEN
1181 WSH_DEBUG_SV.logmsg(l_module_name, 'got non-tl id '||x_region_id_non_tl);
1182 WSH_DEBUG_SV.logmsg(l_module_name, 'for region type '||l_region_type);
1183 END IF;
1184
1185 IF (x_region_id_non_tl = -1) THEN
1186 --
1187 -- Debug Statements
1188 --
1189 IF l_debug_on THEN
1190 WSH_DEBUG_SV.pop(l_module_name);
1191 END IF;
1192 --
1193 return;
1194 END IF;
1195
1196 Get_Region_Id_Codes_Only (
1197 p_country_code => p_country_code,
1198 p_country_region_code => p_country_region_code,
1199 p_state_code => p_state_code,
1200 p_city_code => p_city_code,
1201 p_postal_code_from => p_postal_code_from,
1202 p_postal_code_to => p_postal_code_to,
1203 p_region_type => l_region_type,
1204 p_interface_flag => p_interface_flag,
1205 p_language_code => p_lang_code,
1206 x_region_id => x_region_id_with_tl);
1207
1208 --
1209 -- Debug Statements
1210 --
1211 IF l_debug_on THEN
1212 WSH_DEBUG_SV.logmsg(l_module_name, 'checking tl id '||x_region_id_with_tl);
1213 WSH_DEBUG_SV.pop(l_module_name);
1214 END IF;
1215 --
1216 END Get_Region_Id_Codes_Only;
1217
1218
1219 --
1220 -- Function: Match_Location_Region
1221 --
1222 -- Purpose: Returns region id for region found when matching location
1223 -- to region
1224 --
1225
1226 FUNCTION Match_Location_Region (
1227 p_country IN VARCHAR2,
1228 p_country_code IN VARCHAR2,
1229 p_state IN VARCHAR2,
1230 p_city IN VARCHAR2,
1231 p_postal_code IN VARCHAR2,
1232 p_insert_flag IN VARCHAR2) RETURN NUMBER IS
1233
1234 l_region_info region_rec;
1235 l_region_type NUMBER := 0;
1236 l_region_rec1 region_rec;
1237 l_region_rec2 region_rec;
1238 l_country l_region_info.country%TYPE;
1239
1240 --
1241 l_debug_on BOOLEAN;
1242 --
1243 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MATCH_LOCATION_REGION';
1244 --
1245 BEGIN
1246
1247 --
1248 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1249 --
1250 IF l_debug_on IS NULL
1251 THEN
1252 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1253 END IF;
1254 --
1255 --
1256 -- Debug Statements
1257 --
1258 IF l_debug_on THEN
1259 WSH_DEBUG_SV.push(l_module_name);
1260 --
1261 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1262 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1263 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1264 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1265 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE',P_POSTAL_CODE);
1266 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
1267 END IF;
1268 --
1269 l_region_info.country_code := p_country_code;
1270 l_region_info.country := p_country;
1271
1272 -- this is done so that both country and country_code are not sent at same time
1273 IF (p_country_code IS NULL) THEN
1274 l_country := p_country;
1275 END IF;
1276
1277 --Bug 6670302 Removed the restriction on length of state and city
1278 l_region_info.state := p_state;
1279 l_region_info.city := p_city;
1280
1281
1282 l_region_info.postal_code_from := p_postal_code;
1283 l_region_info.postal_code_to := p_postal_code;
1284
1285 IF (p_postal_code IS NOT NULL) THEN
1286 l_region_type := 3;
1287 ELSIF (p_city IS NOT NULL) THEN
1288 l_region_type := 2;
1289 ELSIF (p_state IS NOT NULL) THEN
1290 l_region_type := 1;
1291 END IF;
1292
1293 Get_Region_Info (
1294 p_country => l_country,
1295 p_country_region => l_region_info.country_region,
1296 p_state => l_region_info.state,
1297 p_city => l_region_info.city,
1298 p_postal_code_from => l_region_info.postal_code_from,
1299 p_postal_code_to => l_region_info.postal_code_to,
1300 p_zone => null,
1301 --p_lang_code => null,
1302 p_lang_code => USERENV('LANG'),
1303 p_country_code => l_region_info.country_code,
1304 p_country_region_code => l_region_info.country_region_code,
1305 p_state_code => l_region_info.state_code,
1306 p_city_code => l_region_info.city_code,
1307 p_region_type => l_region_type,
1308 p_interface_flag => 'N',
1309 x_region_info => l_region_rec1);
1310
1311 IF (l_region_rec1.region_id = -1) THEN
1312
1313 IF (p_insert_flag = 'Y') THEN
1314
1315 Get_Region_Info (
1316 p_country => l_region_info.country,
1317 p_country_region => l_region_info.country_region,
1318 p_state => l_region_info.state,
1319 p_city => l_region_info.city,
1320 p_postal_code_from => l_region_info.postal_code_from,
1321 p_postal_code_to => l_region_info.postal_code_to,
1322 p_zone => null,
1323 --p_lang_code => null,
1324 p_lang_code => USERENV('LANG'),
1325 p_country_code => l_region_info.country_code,
1326 p_country_region_code => l_region_info.country_region_code,
1327 p_state_code => l_region_info.state_code,
1328 p_city_code => l_region_info.city_code,
1329 p_region_type => l_region_type,
1330 p_interface_flag => 'Y',
1331 x_region_info => l_region_rec2);
1332
1333 IF (l_region_rec2.region_id = -1) THEN
1334
1335 --
1336 -- Debug Statements
1337 --
1338 IF l_debug_on THEN
1339 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);
1340 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.ADD_REGION',WSH_DEBUG_SV.C_PROC_LEVEL);
1341 END IF;
1342 --
1343 Wsh_Regions_Pkg.Add_Region(
1344 p_country_code => l_region_info.country_code,
1345 p_country_region_code => l_region_info.country_region_code,
1346 p_state_code => l_region_info.state_code,
1347 p_city_code => l_region_info.city_code,
1348 p_port_flag => null,
1349 p_airport_flag => null,
1350 p_road_terminal_flag => null,
1351 p_rail_terminal_flag => null,
1352 p_longitude => null,
1353 p_latitude => null,
1354 p_timezone => null,
1355 p_continent => null,
1356 p_country => l_region_info.country,
1357 p_country_region => nvl(l_region_info.country_region, l_region_info.country_region_code),
1358 p_state => nvl(l_region_info.state,l_region_info.state_code),
1359 p_city => nvl(l_region_info.city,l_region_info.city_code),
1360 p_alternate_name => null,
1361 p_county => null,
1362 p_postal_code_from => l_region_info.postal_code_from,
1363 p_postal_code_to => l_region_info.postal_code_to,
1364 p_lang_code => nvl(userenv('LANG'),'US'),
1365 p_region_type => l_region_type,
1366 p_parent_region_id => null,
1367 p_interface_flag => 'Y',
1368 p_tl_only_flag => 'N',
1369 p_region_id => null,
1370 x_region_id => l_region_info.region_id);
1371
1372 END IF;
1373
1374 END IF;
1375
1376 IF (l_region_rec1.region_id = -1 AND p_insert_flag <> 'Y') THEN
1377 IF l_debug_on THEN
1378 wsh_debug_sv.logmsg(l_module_name,nvl(l_region_info.country,l_region_info.country_code)
1379 ||' '
1380 ||nvl(l_region_info.state,l_region_info.state_code)
1381 ||' '
1382 ||nvl(l_region_info.city,l_region_info.city_code)
1383 ||' '
1384 ||l_region_info.postal_code_from);
1385 END IF;
1386 END IF;
1387
1388 IF (l_region_rec2.region_id is not null) then
1389 l_region_rec1.region_id := l_region_rec2.region_id;
1390 END IF;
1391
1392 END IF;
1393
1394 --
1395 -- Debug Statements
1396 --
1397 IF l_debug_on THEN
1398 WSH_DEBUG_SV.pop(l_module_name);
1399 END IF;
1400 --
1401 RETURN l_region_rec1.region_id;
1402
1403 END Match_Location_Region;
1404
1405 --
1406 -- PROCEDURE : Map_Location_Region_Child
1407 --
1408 -- PURPOSE : Child program to Location to Region Mapping Concurrent request.
1409 -- Calls appropriate procedure depending upon value of mode parameter.
1410 -- New API added as a part of ECO - 4740786
1411
1412 PROCEDURE Map_Location_Region_Child(
1413 p_errbuf OUT NOCOPY VARCHAR2,
1414 p_retcode OUT NOCOPY NUMBER,
1415 p_mode IN VARCHAR2,
1416 p_location_type IN VARCHAR2,
1417 p_from_value IN NUMBER,
1418 p_to_value IN NUMBER,
1419 p_start_date IN VARCHAR2,
1420 p_end_date IN VARCHAR2,
1421 p_insert_flag IN VARCHAR2) IS
1422
1423
1424 CURSOR get_region_info(l_processing_date DATE) IS
1425 SELECT nvl(country, country_code) country,
1426 nvl(state,state_code) state,
1427 nvl(city,city_code) city,
1428 postal_code_from
1429 FROM wsh_regions_interface r, wsh_regions_tl_interface t
1430 WHERE r.region_id = t.region_id and r.creation_date >= l_processing_date
1431 ORDER BY country, state, city, postal_code_from;
1432
1433 CURSOR get_loc_info (l_start_date DATE, l_end_date DATE) IS
1434 SELECT t.territory_short_name, t.territory_code, nvl(l.state, l.province) state, l.city, l.postal_code
1435 FROM hz_locations l, fnd_territories_tl t
1436 WHERE t.territory_code = l.country and
1437 t.language = userenv('LANG') and
1438 l.last_update_date >= nvl(l_start_date, l.last_update_date) and
1439 l.last_update_date < nvl(l_end_date, l.last_update_date+1);
1440
1441 l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
1442 l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
1443 l_new_request_id NUMBER := 0;
1444 i NUMBER := 0;
1445 l_worker_min NUMBER := 0;
1446 l_worker_max NUMBER := 0;
1447 l_min NUMBER := 0;
1448 l_max NUMBER := 0;
1449 l_sqlcode NUMBER;
1450 l_sqlerr VARCHAR2(2000);
1451 l_return_status VARCHAR2(10);
1452 l_completion_status VARCHAR2(30);
1453 l_temp BOOLEAN;
1454 l_retcode NUMBER;
1455 l_errbuf VARCHAR2(2000);
1456 l_log_level NUMBER;
1457 l_num_of_instances NUMBER;
1458 l_location_type VARCHAR2(10);
1459 l_region_id NUMBER;
1460 l_num NUMBER;-- := p_number_processed;
1461 l_count NUMBER := 0;
1462 l_processing_date DATE;
1463 l_start_date DATE;
1464 l_end_date DATE;
1465
1466 l_debug_on BOOLEAN;
1467 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'MAP_LOCATION_REGION_CHILD';
1468
1469 BEGIN
1470 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
1471
1472 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1473 IF l_debug_on IS NULL THEN
1474 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1475 END IF;
1476
1477 --bug 4775798
1478 --changed WSH_UTIL_CORE.println to WSH_DEBUG_SV.log
1479 IF l_debug_on THEN
1480 WSH_DEBUG_SV.push(l_module_name);
1481 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
1482 WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
1483 WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
1484 END IF;
1485
1486 IF p_location_type IS NULL THEN
1487 l_location_type := 'BOTH';
1488 ELSE
1489 l_location_type := p_location_type;
1490 END IF;
1491
1492 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
1493 l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS') +1;
1494
1495 IF l_debug_on THEN
1496 WSH_DEBUG_SV.log(l_module_name,'l_START_DATE',l_START_DATE);
1497 WSH_DEBUG_SV.log(l_module_name,'l_END_DATE',l_END_DATE);
1498 END IF;
1499
1500 IF p_mode = 'CREATE' THEN
1501
1502 l_processing_date := sysdate;
1503 IF l_debug_on THEN
1504 wsh_debug_sv.logmsg(l_module_name,'Processing locations. Following locations could not be mapped to existing regions.');
1505 END IF;
1506
1507 IF (p_insert_flag = 'Y') THEN
1508 IF l_debug_on THEN
1509 wsh_debug_sv.logmsg(l_module_name,'These have been inserted in the regions interface tables');
1510 END IF;
1511
1512 ELSE
1513 IF l_debug_on THEN
1514 wsh_debug_sv.logmsg(l_module_name,'---------------------------------------------------------------');
1515 wsh_debug_sv.logmsg(l_module_name,'Country State City Postal Code');
1516 wsh_debug_sv.logmsg(l_module_name,'------- ----- ---- ------ ----');
1517 END IF;
1518
1519 END IF;
1520
1521 FOR loc IN get_loc_info (l_start_date, l_end_date) LOOP
1522
1523 --
1524 -- Debug Statements
1525 --
1526 IF l_debug_on THEN
1527 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.MATCH_LOCATION_REGION',WSH_DEBUG_SV.C_PROC_LEVEL);
1528 END IF;
1529 --
1530 l_region_id := Wsh_Regions_Search_Pkg.Match_Location_Region (
1531 p_country => loc.territory_short_name,
1532 p_country_code => loc.territory_code,
1533 p_state => loc.state,
1534 p_city => loc.city,
1535 p_postal_code => loc.postal_code,
1536 p_insert_flag => p_insert_flag);
1537
1538 IF (l_region_id = -1) THEN
1539 l_count := l_count + 1;
1540 END IF;
1541
1542 END LOOP;
1543
1544 IF (p_insert_flag = 'Y') THEN
1545 IF l_debug_on THEN
1546 wsh_debug_sv.logmsg(l_module_name,'Total number of interfaced regions '||l_count);
1547
1548 wsh_debug_sv.logmsg(l_module_name,'---------------------------------------------------------------');
1549 wsh_debug_sv.logmsg(l_module_name,'Country State City Postal Code');
1550 wsh_debug_sv.logmsg(l_module_name,'------- ----- ---- ------ ----');
1551 FOR loc IN get_region_info(l_processing_date) LOOP
1552 wsh_debug_sv.logmsg(l_module_name,loc.country||' '||loc.state||' '||loc.city||' '||loc.postal_code_from);
1553 END LOOP;
1554 END IF;
1555 END IF;
1556
1557 ELSIF p_mode = 'MAP' THEN
1558
1559 WSH_MAP_LOCATION_REGION_PKG.Mapping_Regions_Main (
1560 p_location_type => l_location_type,
1561 p_from_location => p_from_value,
1562 p_to_location => p_to_value,
1563 p_start_date => p_start_date,
1564 p_end_date => p_end_date,
1565 p_insert_flag => FALSE,
1566 x_return_status => l_return_status,
1567 x_sqlcode => l_sqlcode,
1568 x_sqlerr => l_sqlerr);
1569
1570
1571 IF l_debug_on THEN
1572 WSH_DEBUG_SV.logmsg(l_module_name,'Return Status from Mapping_Regions_Main : '|| l_return_status);
1573 END IF;
1574
1575 IF l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS THEN
1576
1577 WSH_UTIL_CORE.printmsg('Failed in Procedure Mapping_Regions_Main');
1578 WSH_UTIL_CORE.printmsg(l_sqlcode);
1579 WSH_UTIL_CORE.printmsg(l_sqlerr);
1580 l_completion_status := 'ERROR';
1581 END IF;
1582 END IF;
1583 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1584 IF l_debug_on THEN
1585 WSH_DEBUG_SV.log(l_module_name,'ERRBUF',p_errbuf);
1586 WSH_DEBUG_SV.log(l_module_name,'RETCODE',p_retcode);
1587 WSH_DEBUG_SV.pop(l_module_name);
1588 END IF;
1589
1590 EXCEPTION
1591
1592 WHEN No_Data_Found THEN
1593
1594 WSH_UTIL_CORE.printmsg('No matching records for the entered parameters');
1595 IF l_debug_on THEN
1596 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:NO_DATA_FOUND');
1597 END IF;
1598
1599 WHEN others THEN
1600 l_sqlcode := SQLCODE;
1601 l_sqlerr := SQLERRM;
1602
1603 WSH_UTIL_CORE.printmsg('Exception occurred in Map_Location_Region_Child Program');
1604 WSH_UTIL_CORE.printmsg('SQLCODE : ' || l_sqlcode);
1605 WSH_UTIL_CORE.printmsg('SQLERRM : ' || l_sqlerr);
1606
1607 l_completion_status := 'ERROR';
1608 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
1609 p_errbuf := 'Exception occurred in Map_Location_Region_Child Program';
1610 p_retcode := '2';
1611
1612
1613 IF l_debug_on THEN
1614 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1615 END IF;
1616
1617 END Map_Location_Region_Child;
1618
1619 --
1620 -- Procedure: Get_Child_Requests_Status
1621 --
1622 -- Purpose: Obtains the completion status of all the child requests
1623 -- and sets x_completion_status accordingly
1624 --
1625
1626 PROCEDURE Get_Child_Requests_Status
1627 (
1628 x_completion_status OUT NOCOPY VARCHAR2
1629 ) IS
1630
1631 CURSOR c_requests (p_parent_request_id NUMBER) IS
1632 SELECT request_id
1633 FROM FND_CONCURRENT_REQUESTS
1634 WHERE parent_request_id = p_parent_request_id
1635 AND NVL(is_sub_request, 'N') = 'Y';
1636
1637 l_child_req_ids WSH_UTIL_CORE.Id_Tab_Type;
1638 l_this_request NUMBER;
1639 l_errors NUMBER := 0;
1640 l_warnings NUMBER := 0;
1641 l_phase VARCHAR2(100);
1642 l_status VARCHAR2(100);
1643 l_dev_phase VARCHAR2(100);
1644 l_dev_status VARCHAR2(100);
1645 l_dummy BOOLEAN;
1646 j NUMBER;
1647 l_message VARCHAR2(2000);
1648 l_completion_status VARCHAR2(30);
1649 l_debug_on BOOLEAN;
1650 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_CHILD_REQUESTS_STATUS';
1651 BEGIN
1652
1653 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1654 --
1655 IF l_debug_on IS NULL
1656 THEN
1657 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1658 END IF;
1659
1660 IF l_debug_on THEN
1661 WSH_DEBUG_SV.push(l_module_name);
1662 END IF;
1663
1664 FND_PROFILE.Get('CONC_REQUEST_ID', l_this_request);
1665
1666 OPEN c_requests(l_this_request);
1667 FETCH c_requests BULK COLLECT INTO l_child_req_ids;
1668 CLOSE c_requests;
1669 l_errors := 0;
1670 l_warnings := 0;
1671 j := l_child_req_ids.FIRST;
1672 WHILE j IS NOT NULL LOOP
1673 l_dev_status := NULL;
1674 l_dummy := FND_CONCURRENT.get_request_status(
1675 request_id => l_child_req_ids(j),
1676 phase => l_phase,
1677 status => l_status,
1678 dev_phase => l_dev_phase,
1679 dev_status => l_dev_status,
1680 message => l_message);
1681
1682 IF l_dev_status = 'WARNING' THEN
1683 l_warnings:= l_warnings + 1;
1684 ELSIF l_dev_status <> 'NORMAL' THEN
1685 l_errors := l_errors + 1;
1686 END IF;
1687
1688 FND_MESSAGE.SET_NAME('WSH','WSH_CHILD_REQ_STATUS');
1689 FND_MESSAGE.SET_TOKEN('REQ_ID', to_char(l_child_req_ids(j)));
1690 FND_MESSAGE.SET_TOKEN('STATUS', l_status);
1691 WSH_UTIL_CORE.PrintMsg(FND_MESSAGE.GET);
1692 j := l_child_req_ids.NEXT(j);
1693 END LOOP;
1694
1695 IF l_errors = 0 AND l_warnings = 0 THEN
1696 l_completion_status := 'NORMAL';
1697 ELSIF (l_errors > 0 ) THEN
1698 l_completion_status := 'ERROR';
1699 ELSE
1700 l_completion_status := 'WARNING';
1701 END IF;
1702 x_completion_status := l_completion_status;
1703
1704 IF l_debug_on THEN
1705 WSH_DEBUG_SV.pop(l_module_name);
1706 END IF;
1707 EXCEPTION
1708 WHEN others THEN
1709 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_Child_Requests_Status');
1710 --
1711 IF l_debug_on THEN
1712 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1713 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1714 END IF;
1715
1716 END Get_Child_Requests_Status;
1717
1718 --
1719 -- Procedure: Process_All_Locations
1720 --
1721 -- Purpose: Calls Process_All_Locations with l_location_type='BOTH'
1722 --
1723 --
1724
1725 PROCEDURE Process_All_Locations (
1726 p_dummy1 IN VARCHAR2,
1727 p_dummy2 IN VARCHAR2,
1728 p_mode IN VARCHAR2 default g_mode,
1729 p_num_of_instances IN NUMBER,
1730 p_insert_flag IN VARCHAR2,
1731 p_start_date IN VARCHAR2,
1732 p_end_date IN VARCHAR2) IS
1733
1734 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_ALL_LOCATIONS';
1735 l_debug_on BOOLEAN;
1736
1737 BEGIN
1738
1739 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1740 --
1741 IF l_debug_on IS NULL
1742 THEN
1743 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1744 END IF;
1745
1746 IF l_debug_on THEN
1747 WSH_DEBUG_SV.push(l_module_name);
1748 END IF;
1749
1750 --
1751 --
1752 -- Debug Statements
1753 --
1754 IF l_debug_on THEN
1755 WSH_DEBUG_SV.push(l_module_name);
1756 WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
1757 WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
1758 WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
1759 WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
1760 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',p_insert_flag);
1761 WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
1762 WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
1763 END IF;
1764
1765 IF l_debug_on THEN
1766 WSH_DEBUG_SV.logmsg(l_module_name, 'CALLING PROCESS_ALL_LOCATIONS API with location_type=BOTH' ,WSH_DEBUG_SV.C_PROC_LEVEL);
1767 END IF;
1768
1769 WSH_REGIONS_SEARCH_PKG.Process_All_Locations(
1770 p_dummy1 => NULL,
1771 p_dummy2 => NULL,
1772 p_mode => p_mode,
1773 p_insert_flag => p_insert_flag,
1774 p_location_type => 'BOTH',
1775 p_start_date => p_start_date,
1776 p_end_date => p_end_date,
1777 p_num_of_instances => p_num_of_instances
1778 );
1779
1780 IF l_debug_on THEN
1781 WSH_DEBUG_SV.pop(l_module_name);
1782 END IF;
1783
1784 EXCEPTION
1785 WHEN others THEN
1786 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Process_All_Locations');
1787 --
1788 IF l_debug_on THEN
1789 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1790 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
1791 END IF;
1792
1793 END Process_All_Locations;
1794
1795
1796 --
1797 -- Procedure: Process_All_Locations
1798 --
1799 -- Purpose: Overloaded procedure introduced as a part of ECO - 4740786.
1800 -- Returns region id for region found when matching location
1801 -- to region
1802 --
1803
1804 PROCEDURE Process_All_Locations (
1805 p_dummy1 IN VARCHAR2,
1806 p_dummy2 IN VARCHAR2,
1807 p_mode IN VARCHAR2 default g_mode,
1808 p_num_of_instances IN NUMBER,
1809 p_insert_flag IN VARCHAR2,
1810 p_location_type IN VARCHAR2,
1811 p_start_date IN VARCHAR2,
1812 p_end_date IN VARCHAR2
1813 ) IS
1814
1815 --
1816 l_debug_on BOOLEAN;
1817 l_return_status VARCHAR2(10);
1818 l_completion_status VARCHAR2(30);
1819
1820 l_worker_min_tab WSH_UTIL_CORE.id_tab_type;
1821 l_worker_max_tab WSH_UTIL_CORE.id_tab_type;
1822 l_new_request_id NUMBER := 0;
1823 i NUMBER := 0;
1824 l_temp BOOLEAN;
1825 l_retcode NUMBER;
1826 l_errbuf VARCHAR2(2000);
1827 l_num_of_instances NUMBER;
1828 l_req_data VARCHAR2(50);
1829 l_start_date DATE;
1830 l_end_date DATE;
1831 l_import_start_date DATE;
1832 l_start_date1 VARCHAR2(50);
1833 l_end_date1 VARCHAR2(50);
1834
1835 --
1836 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_ALL_LOCATIONS1';
1837 --
1838 BEGIN
1839
1840 --
1841 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1842 --
1843 IF l_debug_on IS NULL
1844 THEN
1845 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1846 END IF;
1847
1848
1849 WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
1850 l_completion_status := 'NORMAL';
1851 --
1852 --
1853 -- Debug Statements
1854 --
1855 IF l_debug_on THEN
1856 WSH_DEBUG_SV.push(l_module_name);
1857 WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
1858 WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
1859 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
1860 WSH_DEBUG_SV.log(l_module_name,'P_START_DATE',P_START_DATE);
1861 WSH_DEBUG_SV.log(l_module_name,'P_END_DATE',P_END_DATE);
1862 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_TYPE',P_LOCATION_TYPE);
1863 WSH_DEBUG_SV.log(l_module_name,'P_MODE',P_MODE);
1864 WSH_DEBUG_SV.log(l_module_name,'P_NUM_OF_INSTANCES',p_num_of_instances);
1865 END IF;
1866 --
1867 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
1868 l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS') +1;
1869
1870 IF l_debug_on THEN
1871 WSH_DEBUG_SV.log(l_module_name,'l_START_DATE',l_START_DATE);
1872 WSH_DEBUG_SV.log(l_module_name,'l_END_DATE',l_END_DATE);
1873 END IF;
1874
1875 IF p_num_of_instances is null or p_num_of_instances = 0 then
1876 l_num_of_instances := 1;
1877 ELSE
1878 l_num_of_instances := p_num_of_instances;
1879 END IF;
1880
1881 -- ECO - 4740786 - based on the mode, obtain sets of records to be processed by the workers based on num_instances
1882 -- parameter
1883 IF p_mode = 'CREATE' THEN
1884 --{
1885 IF l_debug_on THEN
1886 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Map_Location_Region_Child',WSH_DEBUG_SV.C_PROC_LEVEL);
1887 END IF;
1888
1889 Map_Location_Region_Child (
1890 p_errbuf => l_errbuf,
1891 p_retcode => l_retcode,
1892 p_mode => p_mode,
1893 p_location_type => p_location_type,
1894 p_from_value => NULL,
1895 p_to_value => NULL,
1896 p_start_date => p_start_date,
1897 p_end_date => p_end_date,
1898 p_insert_flag => p_insert_flag);
1899
1900 IF l_debug_on THEN
1901 WSH_DEBUG_SV.logmsg(l_module_name,'Return Code from Map_Location_Region_Child : '||l_retcode);
1902 END IF;
1903
1904 IF l_retcode = '2' THEN
1905 l_completion_status := 'ERROR';
1906 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1907 ELSIF l_retcode = '1' THEN
1908 l_completion_status := 'WARNING';
1909 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1910 END IF;
1911 --}
1912 ELSIF p_mode = 'MAP' THEN
1913 --{
1914 l_req_data := fnd_conc_global.request_data;
1915
1916 IF l_debug_on THEN
1917 WSH_DEBUG_SV.log(l_module_name, ' l_req_data', l_req_data);
1918 END IF;
1919
1920 IF l_req_data IS NOT NULL THEN
1921 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');
1922 l_req_data := SUBSTR(l_req_data, 1,1);
1923 END IF;
1924
1925
1926 IF l_import_start_date IS NOT NULL THEN
1927 l_start_date1 := to_char(l_import_start_date,'YYYY/MM/DD HH24:MI:SS');
1928 l_end_date1 := to_char(sysdate,'YYYY/MM/DD HH24:MI:SS');
1929 ELSE
1930 l_start_date1 := p_start_date;
1931 l_end_date1 := p_end_date;
1932 END IF;
1933
1934 IF l_debug_on THEN
1935 wsh_debug_sv.log(l_module_name, 'l_req_data 1', l_req_data);
1936 wsh_debug_sv.log(l_module_name, 'l_start_date1', l_start_date1);
1937 wsh_debug_sv.log(l_module_name, 'l_end_date1', l_end_date1);
1938 END IF;
1939
1940 IF l_req_data IS NULL OR l_req_data = '1' THEN
1941 --{
1942 IF p_location_type = 'EXTERNAL' THEN
1943 --{
1944 IF l_debug_on THEN
1945 WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is External');
1946 END IF;
1947
1948 EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
1949 FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
1950 FROM HZ_LOCATIONS
1951 WHERE last_update_date >= nvl(:start_date, last_update_date)
1952 AND last_update_date < nvl(:end_date, last_update_date+1)
1953 )
1954 GROUP BY WORKER'
1955 BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
1956 USING l_num_of_instances, l_start_date, l_end_date;
1957
1958 ELSIF p_location_type = 'INTERNAL' THEN
1959
1960 IF l_debug_on THEN
1961 WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is Internal');
1962 END IF;
1963
1964 EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
1965 FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
1966 FROM HR_LOCATIONS_ALL
1967 WHERE last_update_date >= nvl(:start_date, last_update_date)
1968 AND last_update_date < nvl(:end_date, last_update_date+1)
1969 )
1970 GROUP BY WORKER'
1971 BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
1972 USING l_num_of_instances, l_start_date, l_end_date;
1973
1974 ELSIF p_location_type = 'BOTH' THEN
1975
1976 IF l_debug_on THEN
1977 WSH_DEBUG_SV.logmsg(l_module_name, 'Location Type is Both');
1978 END IF;
1979
1980 EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
1981 FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
1982 FROM WSH_HR_LOCATIONS_V
1983 WHERE last_update_date >= nvl(:start_date, last_update_date)
1984 AND last_update_date < nvl(:end_date, last_update_date+1)
1985 )
1986 GROUP BY WORKER'
1987 BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
1988 USING l_num_of_instances, l_start_date, l_end_date;
1989
1990 --}
1991 END IF;
1992
1993 IF l_worker_min_tab.count <>0 and p_num_of_instances > 0 THEN
1994 --{
1995 FOR i in 1..l_worker_min_tab.count
1996 LOOP
1997
1998 IF l_debug_on THEN
1999 WSH_DEBUG_SV.logmsg(l_module_name, 'Value of i : '|| i ||' l_worker_min : '||l_worker_min_tab(i)||
2000 ' l_worker_max : '||l_worker_max_tab(i));
2001 END IF;
2002
2003
2004 l_new_request_id := FND_REQUEST.SUBMIT_REQUEST(
2005 application => 'WSH',
2006 program => 'WSHLRMCD',
2007 description => 'Location To Region Mapping - Child '||to_char(i),
2008 start_time => NULL,
2009 sub_request => TRUE,
2010 argument1 => p_mode,
2011 argument2 => p_location_type,
2012 argument3 => l_worker_min_tab(i),
2013 argument4 => l_worker_max_tab(i),
2014 argument5 => l_start_date1,
2015 argument6 => l_end_date1,
2016 argument7 => p_insert_flag);
2017
2018 IF l_debug_on THEN
2019 WSH_DEBUG_SV.log(l_module_name, 'Child request ID ', l_new_request_id);
2020 END IF;
2021
2022 IF l_new_request_id = 0 THEN
2023 IF l_debug_on THEN
2024 WSH_debug_sv.logmsg(l_module_name,'Error Submitting concurrent request for worker : '||i);
2025 END IF;
2026 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2027 END IF;
2028
2029 END LOOP;
2030 FND_CONC_GLOBAL.Set_Req_Globals ( Conc_Status => 'PAUSED', Request_Data => to_char(2) );
2031 --}
2032 ELSIF l_worker_min_tab.count <>0 AND nvl(p_num_of_instances,0) = 0 THEN
2033 --{
2034 IF l_debug_on THEN
2035 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit Map_Location_Region_Child',WSH_DEBUG_SV.C_PROC_LEVEL);
2036 END IF;
2037
2038 Map_Location_Region_Child (
2039 p_errbuf => l_errbuf,
2040 p_retcode => l_retcode,
2041 p_mode => p_mode,
2042 p_location_type => p_location_type,
2043 p_from_value => l_worker_min_tab(1),
2044 p_to_value => l_worker_max_tab(1),
2045 p_start_date => l_start_date1,
2046 p_end_date => l_end_date1,
2047 p_insert_flag => p_insert_flag);
2048
2049 IF l_debug_on THEN
2050 WSH_DEBUG_SV.logmsg(l_module_name,'Return Code from Map_Location_Region_Child : '||l_retcode);
2051 END IF;
2052
2053 IF l_retcode = '2' THEN
2054 l_completion_status := 'ERROR';
2055 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2056 ELSIF l_retcode = '1' THEN
2057 l_completion_status := 'WARNING';
2058 l_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2059 END IF;
2060 --}
2061 END IF;
2062 --}
2063 ELSE
2064 get_child_requests_status(x_completion_status => l_completion_status);
2065 l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(l_completion_status,'');
2066 END IF;
2067 --}
2068 END IF;
2069
2070 IF l_completion_status = 'NORMAL' THEN
2071 l_errbuf := 'Location To Region Mapping Program completed successfully';
2072 l_retcode := '0';
2073 ELSIF l_completion_status = 'WARNING' THEN
2074 l_errbuf := 'Location To Region Mapping Program is completed with warning';
2075 l_retcode := '1';
2076 ELSIF l_completion_status = 'ERROR' THEN
2077 l_errbuf := 'Location To Region Mapping Program is completed with error';
2078 l_retcode := '2';
2079 END IF;
2080
2081 --
2082 -- Debug Statements
2083 --
2084 IF l_debug_on THEN
2085 WSH_DEBUG_SV.log(l_module_name,'ERRBUF', l_errbuf);
2086 WSH_DEBUG_SV.log(l_module_name,'RETCODE',l_retcode);
2087 WSH_DEBUG_SV.pop(l_module_name);
2088 END IF;
2089
2090 EXCEPTION
2091 WHEN others THEN
2092 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Process_All_Locations');
2093 --
2094 IF l_debug_on THEN
2095 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2096 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2097 END IF;
2098
2099 END Process_All_Locations;
2100
2101 --
2102 -- PROCEDURE : refresh_cache
2103 --
2104 -- PURPOSE : Clears regions cache g_regions_info_tab if session id changes
2105 --
2106
2107 PROCEDURE refresh_cache (
2108 p_country_code IN VARCHAR2,
2109 x_return_status OUT NOCOPY VARCHAR2 )
2110 IS
2111
2112 l_session_id NUMBER := 0;
2113 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2114 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'refresh_cache';
2115
2116 BEGIN
2117 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2118 --
2119 IF l_debug_on THEN
2120 wsh_debug_sv.push (l_module_name);
2121 END IF;
2122 --
2123
2124 l_session_id := userenv('sessionid');
2125
2126 IF l_debug_on THEN
2127 wsh_debug_sv.logmsg(l_module_name,'g_regions_info_tab count : '||g_regions_info_tab.COUNT);
2128 wsh_debug_sv.logmsg(l_module_name,'g_session_id : '||g_session_id||' Current session id : '||l_session_id);
2129 END IF;
2130
2131 IF g_session_id IS NULL OR l_session_id <> g_session_id
2132 OR g_country_code <> p_country_code OR g_regions_info_tab.count > 5000 THEN
2133 g_regions_info_tab := g_emp_reg_info_tab ; --Bug 7313093 Instead of deleting assigning empty table
2134 g_country_code := p_country_code;
2135 g_session_id := l_session_id;
2136
2137 IF l_debug_on THEN
2138 wsh_debug_sv.logmsg(l_module_name,'Regions Cache cleared');
2139 END IF;
2140
2141 END IF;
2142
2143 --
2144 IF l_debug_on THEN
2145 wsh_debug_sv.pop (l_module_name);
2146 END IF;
2147 --
2148
2149 EXCEPTION
2150 WHEN others THEN
2151 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.refresh_cache');
2152 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2153 --
2154 IF l_debug_on THEN
2155 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2156 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2157 END IF;
2158 --
2159 END refresh_cache;
2160
2161
2162 --
2163 -- Procedure: Get_All_Region_Matches
2164 --
2165 -- Purpose: Obtains all information for a region, and its parents
2166 -- and the zones it belongs to
2167 --
2168
2169 PROCEDURE Get_All_Region_Matches (
2170 p_country IN VARCHAR2,
2171 p_country_region IN VARCHAR2,
2172 p_state IN VARCHAR2,
2173 p_city IN VARCHAR2,
2174 p_postal_code_from IN VARCHAR2,
2175 p_postal_code_to IN VARCHAR2,
2176 p_country_code IN VARCHAR2,
2177 p_country_region_code IN VARCHAR2,
2178 p_state_code IN VARCHAR2,
2179 p_city_code IN VARCHAR2,
2180 p_lang_code IN VARCHAR2,
2181 p_location_id IN NUMBER,
2182 p_zone_flag IN VARCHAR2,
2183 p_more_matches IN BOOLEAN DEFAULT FALSE,
2184 x_status OUT NOCOPY NUMBER,
2185 x_regions OUT NOCOPY region_table
2186 ) IS
2187
2188
2189 CURSOR region_details(l_region_id NUMBER,l_location_id NUMBER,l_lang_code IN VARCHAR2) IS
2190 SELECT wr.region_id,
2191 wr.region_type,
2192 wrt.country,
2193 wrt.country_region,
2194 wrt.state,
2195 wrt.city,
2196 wrt.postal_code_from,
2197 wrt.postal_code_to,
2198 wrt.zone,
2199 wr.zone_level,
2200 wr.country_code,
2201 wr.country_region_code,
2202 wr.state_code,
2203 wr.city_code,
2204 decode(loc.parent_region_flag,'Y','N','N','Y')
2205 FROM wsh_regions wr,
2206 wsh_regions_tl wrt,
2207 wsh_region_locations loc
2208 WHERE wr.region_id = l_region_id AND
2209 loc.location_id = l_location_id AND
2210 wr.region_id = loc.region_id AND
2211 wrt.region_id = wr.region_id AND
2212 wrt.language = nvl(l_lang_code,wrt.language);
2213
2214 CURSOR zone_info(l_region_id NUMBER, l_lang_code IN VARCHAR2) IS
2215 SELECT distinct r.region_id,
2216 r.region_type,
2217 null,
2218 null,
2219 null,
2220 null,
2221 null,
2222 null,
2223 rt.zone,
2224 r.zone_level,
2225 null,
2226 null,
2227 null,
2228 null,
2229 'N'
2230 FROM wsh_regions r,
2231 wsh_regions_tl rt,
2232 wsh_zone_regions z
2233 WHERE r.region_id = z.parent_region_id AND
2234 z.region_id = l_region_id AND
2235 r.region_type = 10 AND
2236 rt.region_id = r.region_id AND
2237 rt.language = nvl(l_lang_code,rt.language);
2238
2239 CURSOR Check_Location_Exists(c_location_id IN NUMBER,c_lang_code IN VARCHAR2) IS
2240 SELECT 'exists'
2241 FROM wsh_region_locations wrl, wsh_regions_tl wrt,
2242 wsh_regions wr
2243 WHERE wrl.region_id is not null
2244 AND wrl.location_id = c_location_id
2245 AND wrl.region_id = wrt.region_id
2246 AND wrt.language = nvl(c_lang_code,wrt.language)
2247 AND wrt.region_id = wr.region_id
2248 AND rownum = 1;
2249
2250 -- TODO
2251 -- get rid of wsh_hr_locations_v
2252
2253 -- Bugfix 2877445
2254 -- Added NVL function to region_1
2255 -- region_1 : County
2256 -- region_2 : State
2257 -- region_3 : Province
2258 /*
2259 CURSOR get_location_data IS
2260 SELECT country, nvl(region_2, region_3), town_or_city, postal_code
2261 FROM wsh_hr_locations_v
2262 WHERE location_id = p_location_id;
2263 */
2264
2265 -- TODO
2266 -- check against definition of wsh_hr_locations_v
2267 CURSOR get_location_data(c_location_id IN NUMBER) IS
2268 SELECT country, nvl(state, province), city, postal_code
2269 FROM wsh_locations
2270 WHERE wsh_location_id = c_location_id;
2271
2272
2273 j NUMBER := 0;
2274 i NUMBER := 0;
2275 cnt NUMBER := 0;
2276 cnt_region NUMBER := 0;
2277 l_region_info region_rec;
2278 l_region_rec1 region_rec;
2279 l_region_rec2 region_rec;
2280 l_region_rec3 region_rec;
2281 l_region_type NUMBER := 0;
2282 l_parent_offset NUMBER ;
2283 l_exists VARCHAR2(10) := NULL;
2284 l_rgid_tab WSH_UTIL_CORE.Id_Tab_Type;
2285 is_first BOOLEAN := true;
2286 l_regions region_table;
2287 l_return_status VARCHAR2(1);
2288
2289 --
2290 l_debug_on BOOLEAN;
2291 --
2292 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_ALL_REGION_MATCHES';
2293 --
2294 BEGIN
2295
2296 --
2297 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2298 --
2299 IF l_debug_on IS NULL
2300 THEN
2301 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2302 END IF;
2303 --
2304 --
2305 -- Debug Statements
2306 --
2307 IF l_debug_on THEN
2308 WSH_DEBUG_SV.push(l_module_name);
2309 --
2310 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
2311 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
2312 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2313 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2314 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2315 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2316 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2317 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
2318 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2319 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2320 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2321 WSH_DEBUG_SV.log(l_module_name,'P_LOCATION_ID',P_LOCATION_ID);
2322 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_FLAG',P_ZONE_FLAG);
2323 END IF;
2324
2325
2326 IF g_regions_info_tab.count = 0 THEN
2327 g_country_code := p_country_code;
2328 END IF;
2329 --
2330 refresh_cache( p_country_code => p_country_code,
2331 x_return_status => l_return_status);
2332
2333 -- TODO
2334 -- should use p_location_id if passed in and should not
2335 -- search regions in that case
2336
2337 IF p_location_id IS NOT NULL THEN
2338 OPEN Check_Location_Exists(p_location_id,p_lang_code);
2339 FETCH Check_Location_Exists INTO l_exists;
2340 CLOSE Check_Location_Exists;
2341 END IF;
2342
2343 IF l_exists IS NOT NULL THEN
2344 /*
2345 SELECT wrl.region_id
2346 BULK COLLECT INTO l_rgid_tab
2347 FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
2348 WHERE wrl.location_id = p_location_id
2349 AND wrl.region_id is not null
2350 AND wrl.region_id = wrt.region_id
2351 AND wrt.language = nvl(p_lang_code,wrt.language)
2352 AND wrt.region_id = wr.region_id
2353 ORDER BY wrl.region_type DESC;
2354 */
2355
2356 Get_All_RegionId_Matches(
2357 p_location_id => p_location_id,
2358 p_use_cache => FALSE,
2359 p_lang_code => p_lang_code,
2360 x_region_tab => l_rgid_tab,
2361 x_return_status => l_return_status);
2362
2363 IF l_debug_on THEN
2364 WSH_DEBUG_SV.log(l_module_name,'Number of regions found in wrl : ',l_rgid_tab.COUNT);
2365 END IF;
2366 --
2367
2368 IF l_rgid_tab.COUNT > 0 THEN
2369 j := l_rgid_tab.FIRST;
2370 LOOP
2371 OPEN region_details(l_rgid_tab(j),p_location_id,p_lang_code);
2372 FETCH region_details INTO l_region_info;
2373 CLOSE region_details;
2374
2375 --
2376 IF l_debug_on THEN
2377 WSH_DEBUG_SV.log(l_module_name,'l_rgid_tab(j) : ',l_rgid_tab(j));
2378 WSH_DEBUG_SV.log(l_module_name,'l_region_info.region_id : ',l_region_info.region_id);
2379 WSH_DEBUG_SV.log(l_module_name,'l_region_info.region_type : ',l_region_info.region_type);
2380 WSH_DEBUG_SV.log(l_module_name,'cnt_region : ',cnt_region);
2381 END IF;
2382 --
2383
2384 cnt_region := cnt_region + 1;
2385 x_regions(cnt_region) := l_region_info;
2386
2387 -- for every region, find any zone it belongs in
2388
2389 IF p_zone_flag = 'Y' THEN
2390
2391 OPEN zone_info(l_region_info.region_id,p_lang_code);
2392 LOOP
2393 FETCH zone_info INTO l_region_rec3;
2394
2395 EXIT WHEN zone_info%NOTFOUND;
2396
2397 IF (l_region_rec3.region_id > 0) THEN
2398 cnt_region := cnt_region + 1;
2399
2400 -- Debug Statements
2401 --
2402 IF l_debug_on THEN
2403 WSH_DEBUG_SV.logmsg(l_module_name, ' fetching zone info of '||l_region_rec3.region_id);
2404 WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2405 END IF;
2406
2407 x_regions(cnt_region) := l_region_rec3;
2408
2409 END IF;
2410
2411 l_region_rec3.region_id := 0;
2412
2413 END LOOP;
2414
2415 CLOSE zone_info;
2416
2417 END IF;
2418
2419 EXIT WHEN j=l_rgid_tab.LAST;
2420 j := l_rgid_tab.NEXT(j);
2421 END LOOP;
2422 END IF;
2423
2424 ELSE -- if l_exists is null
2425 l_region_rec2.country := p_country;
2426 l_region_rec2.country_code := p_country_code;
2427 l_region_rec2.state := p_state;
2428 l_region_rec2.state_code := p_state_code;
2429 l_region_rec2.city := p_city;
2430 l_region_rec2.city_code := p_city_code;
2431 l_region_rec2.country_region := p_country_region;
2432 l_region_rec2.country_region_code := p_country_region_code;
2433 l_region_rec2.postal_code_from := p_postal_code_from;
2434 l_region_rec2.postal_code_to := p_postal_code_to;
2435
2436 IF (p_location_id is not null and p_country is null and p_country_code is null) THEN
2437
2438 OPEN get_location_data(p_location_id);
2439 FETCH get_location_data INTO l_region_rec2.country_code, l_region_rec2.state, l_region_rec2.city, l_region_rec2.postal_code_from;
2440
2441 l_region_rec2.postal_code_to := l_region_rec2.postal_code_from;
2442
2443 CLOSE get_location_data;
2444 --
2445 -- Debug Statements
2446 --
2447 IF l_debug_on THEN
2448 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);
2449 END IF;
2450
2451 END IF;
2452
2453 -- TODO
2454 -- check that only one of country/state/city value
2455 -- OR code is passed always
2456
2457 IF (length(l_region_rec2.country) <= 3 AND l_region_rec2.country_code IS NULL) THEN
2458 l_region_rec2.country_code := l_region_rec2.country;
2459 l_region_rec2.country := null;
2460 END IF;
2461
2462 --Bug 6670302 Removed validations on the length of names of city and state
2463
2464 IF (l_region_rec2.postal_code_from IS NOT NULL OR l_region_rec2.postal_code_to IS NOT NULL) THEN
2465 l_region_type := 3;
2466 ELSIF (l_region_rec2.city IS NOT NULL OR l_region_rec2.city_code IS NOT NULL) THEN
2467 l_region_type := 2;
2468 ELSIF (l_region_rec2.state IS NOT NULL OR l_region_rec2.state_code IS NOT NULL) THEN
2469 l_region_type := 1;
2470 END IF;
2471
2472 LOOP
2473 --
2474 -- Debug Statements
2475 --
2476 IF l_debug_on THEN
2477 WSH_DEBUG_SV.logmsg(l_module_name, ' region type '||l_region_type);
2478 WSH_DEBUG_SV.logmsg(l_module_name, ' city: '||l_region_rec2.city||' code: '||l_region_rec2.city_code);
2479 WSH_DEBUG_SV.logmsg(l_module_name, ' state: '||l_region_rec2.state||' code: '||l_region_rec2.state_code);
2480 WSH_DEBUG_SV.logmsg(l_module_name, ' country: '||l_region_rec2.country||' code: '||l_region_rec2.country_code);
2481 END IF;
2482
2483 cnt := cnt + 1;
2484
2485 -- Debug Statements
2486 --
2487 IF l_debug_on THEN
2488 WSH_DEBUG_SV.logmsg(l_module_name, 'number in loop '||cnt);
2489 END IF;
2490
2491 -- Bugfix 2877445 if region type is postal code then dont search with
2492 -- city and state information. This is under the assumption that postal
2493 -- codes are unique in a country.
2494
2495 -- Bug 4722963
2496 -- If key exists region info in cache, fetch regions info from there
2497 -- otherwise call get_region_info API.
2498
2499 IF (l_region_type = 3) then
2500
2501 Get_Region_Info (
2502 p_country => l_region_rec2.country,
2503 p_country_region => l_region_rec2.country_region,
2504 p_state => null,
2505 p_city => null,
2506 p_postal_code_from => l_region_rec2.postal_code_from,
2507 p_postal_code_to => l_region_rec2.postal_code_to,
2508 p_zone => null,
2509 p_lang_code => p_lang_code,
2510 p_country_code => l_region_rec2.country_code,
2511 p_country_region_code=> l_region_rec2.country_region_code,
2512 p_state_code => null,
2513 p_city_code => null,
2514 p_region_type => l_region_type,
2515 p_interface_flag => 'N',
2516 x_regions => l_regions);
2517
2518 ELSE
2519
2520 Get_Region_Info (
2521 p_country => l_region_rec2.country,
2522 p_country_region => l_region_rec2.country_region,
2523 p_state => l_region_rec2.state,
2524 p_city => l_region_rec2.city,
2525 p_postal_code_from => l_region_rec2.postal_code_from,
2526 p_postal_code_to => l_region_rec2.postal_code_to,
2527 p_zone => null,
2528 p_lang_code => p_lang_code,
2529 p_country_code => l_region_rec2.country_code,
2530 p_country_region_code => l_region_rec2.country_region_code,
2531 p_state_code => l_region_rec2.state_code,
2532 p_city_code => l_region_rec2.city_code,
2533 p_region_type => l_region_type,
2534 p_interface_flag => 'N',
2535 x_regions => l_regions);
2536
2537
2538 END IF;
2539
2540 l_region_rec1 := l_regions(0);
2541 l_parent_offset := 1;
2542
2543 -- TODO
2544 -- Will call get_region_info for all address components always
2545
2546 IF (l_region_type > 0) THEN
2547
2548 -- figure out the parent region offset
2549 -- IF (l_region_type > 0) THEN
2550
2551 IF (l_region_type = 2 AND
2552 l_region_rec2.state IS NULL AND
2553 l_region_rec2.state_code IS NULL) THEN
2554 l_parent_offset := l_parent_offset + 1;
2555
2556 ELSE
2557 IF (l_region_type = 3 AND
2558 l_region_rec2.city IS NULL AND
2559 l_region_rec2.city_code IS NULL) THEN
2560 l_parent_offset := l_parent_offset + 1;
2561
2562 IF (l_region_rec2.state IS NULL AND
2563 l_region_rec2.state_code IS NULL) THEN
2564 l_parent_offset := l_parent_offset + 1;
2565 END IF;
2566
2567 END IF;
2568
2569 END IF;
2570
2571 -- END IF;
2572
2573 IF (l_region_type = 3) THEN
2574 l_region_rec2.postal_code_from := null;
2575 l_region_rec2.postal_code_to := null;
2576 ELSIF (l_region_type = 2) THEN
2577 l_region_rec2.city := null;
2578 l_region_rec2.city_code := null;
2579 ELSIF (l_region_type = 1) THEN
2580 l_region_rec2.state := null;
2581 l_region_rec2.state_code := null;
2582 END IF;
2583
2584 -- Debug Statements
2585 --
2586 IF l_debug_on THEN
2587 WSH_DEBUG_SV.logmsg(l_module_name, ' parent offset '||l_parent_offset);
2588 END IF;
2589
2590 END IF;
2591
2592 l_region_type := l_region_type - l_parent_offset;
2593
2594 -- Debug Statements
2595 --
2596 IF l_debug_on THEN
2597 WSH_DEBUG_SV.logmsg(l_module_name, 'got region id '||l_region_rec1.region_id);
2598 END IF;
2599
2600 IF l_region_rec1.region_id > 0 THEN
2601
2602 cnt_region := cnt_region + 1;
2603 -- Debug Statements
2604 --
2605 IF l_debug_on THEN
2606 WSH_DEBUG_SV.logmsg(l_module_name, ' Using region info of '||l_region_rec1.region_id);
2607 WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2608 END IF;
2609
2610 IF is_first THEN
2611 l_region_rec1.is_input_type := 'Y'; -- Record is fetched based on the exact input match.
2612
2613 IF(l_regions.count > 1 AND p_more_matches) THEN
2614
2615 -- Populating the output table x_regions such that the index starts from '1' not from '0'.
2616 FOR i IN l_regions.FIRST.. l_regions.LAST
2617 LOOP
2618 IF l_debug_on THEN
2619 WSH_DEBUG_SV.logmsg(l_module_name, ' Using region info of '||l_regions(i).region_id);
2620 WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2621 END IF;
2622 l_regions(i).is_input_type := 'Y';
2623 x_regions(cnt_region) := l_regions(i);
2624 cnt_region := cnt_region + 1;
2625 END LOOP;
2626
2627 IF l_debug_on THEN
2628 WSH_DEBUG_SV.pop(l_module_name);
2629 END IF;
2630
2631 RETURN;
2632 END IF;
2633
2634 END IF;
2635 x_regions(cnt_region) := l_region_rec1;
2636
2637 -- for every region, find any zone it belongs in
2638 IF p_zone_flag = 'Y' THEN
2639
2640 OPEN zone_info(l_region_rec1.region_id,p_lang_code);
2641
2642 LOOP
2643 FETCH zone_info INTO l_region_rec3;
2644
2645 EXIT WHEN zone_info%NOTFOUND;
2646
2647 IF (l_region_rec3.region_id > 0) THEN
2648 cnt_region := cnt_region + 1;
2649
2650 -- Debug Statements
2651 --
2652 IF l_debug_on THEN
2653 WSH_DEBUG_SV.logmsg(l_module_name, ' fetching zone info of '||l_region_rec3.region_id);
2654 WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
2655 END IF;
2656
2657 x_regions(cnt_region) := l_region_rec3;
2658
2659 END IF;
2660
2661 l_region_rec3.region_id := 0;
2662
2663 END LOOP;
2664
2665 CLOSE zone_info;
2666
2667 END IF;
2668
2669 END IF;
2670
2671 -- TODO
2672 -- Will call get_region_info for all address components always
2673 IF (l_region_type < 0) THEN
2674 EXIT;
2675 END IF;
2676 is_first := false;
2677 END LOOP;
2678
2679 END IF; -- l_exists is not null
2680
2681 IF (x_regions.COUNT = 0) THEN
2682 x_status := 1;
2683 ELSE
2684 IF l_debug_on THEN
2685 WSH_DEBUG_SV.logmsg(l_module_name, ' x_regions.COUNT '||x_regions.COUNT);
2686 END IF;
2687 END IF;
2688
2689 --
2690 -- Debug Statements
2691 --
2692 IF l_debug_on THEN
2693 WSH_DEBUG_SV.pop(l_module_name);
2694 END IF;
2695 --
2696 --Bug 4775798
2697 EXCEPTION
2698 WHEN OTHERS THEN
2699 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches');
2700 IF l_debug_on THEN
2701 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2702 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2703 ELSE
2704 WSH_UTIL_CORE.printmsg('Unexpected error has occured in WSH_REGIONS_SEARCH_PKG.Get_All_Region_Matches');
2705 WSH_UTIL_CORE.printmsg(SQLERRM);
2706 END IF;
2707 END Get_All_Region_Matches;
2708
2709
2710 --
2711 -- Procedure: Get_All_RegionId_Matches
2712 --
2713 -- Purpose : The API derives Region id for an input location,
2714 -- using table WSH_REGION_LOCATIONS
2715 -- Cache is used for storing and retriving location region mappings.
2716 -- when p_use_cache is FALSE
2717
2718 PROCEDURE Get_All_RegionId_Matches(
2719 p_location_id IN NUMBER,
2720 p_use_cache IN BOOLEAN DEFAULT FALSE,
2721 p_lang_code IN VARCHAR2,
2722 x_region_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
2723 x_return_status OUT NOCOPY VARCHAR2) IS
2724
2725
2726 CURSOR c_get_all_region_id(l_location_id IN NUMBER, l_lang_code IN VARCHAR2) IS
2727 SELECT wrl.region_id
2728 FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
2729 WHERE wrl.location_id = l_location_id
2730 AND wrl.region_id is not null
2731 AND wrl.region_id = wrt.region_id
2732 AND wrt.language = nvl(l_lang_code,wrt.language)
2733 AND wrt.region_id = wr.region_id
2734 ORDER BY wrl.region_type DESC;
2735
2736 --
2737 l_region_id_tab WSH_UTIL_CORE.Id_Tab_Type;
2738 itr NUMBER;
2739 l_region_id_string VARCHAR2(32767);
2740 --
2741
2742 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_All_RegionId_Matches';
2743 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2744 l_return_status VARCHAR2(1);
2745
2746 BEGIN
2747
2748 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2749
2750 IF l_debug_on THEN
2751 wsh_debug_sv.push(l_module_name);
2752 END IF;
2753
2754 IF (p_use_cache) and (p_location_id < g_cache_max_size) THEN
2755
2756 IF (g_location_region_tab.EXISTS(p_location_id)) THEN
2757
2758
2759 WSH_UTIL_CORE.get_idtab_from_string(
2760 p_string => g_location_region_tab(p_location_id),
2761 x_id_tab => l_region_id_tab,
2762 x_return_status => l_return_status);
2763
2764 itr := l_region_id_tab.FIRST;
2765
2766 IF (l_region_id_tab(itr)<>-1) THEN
2767 x_region_tab := l_region_id_tab;
2768 END IF;
2769 IF l_debug_on THEN
2770 wsh_debug_sv.pop(l_module_name);
2771 END IF;
2772 RETURN;
2773 END IF;
2774 END IF;
2775
2776 --Location not present in the cache or p_use_cache is false.
2777 --Use cursor to get details.
2778
2779 OPEN c_get_all_region_id(p_location_id,p_lang_code);
2780 FETCH c_get_all_region_id BULK COLLECT INTO l_region_id_tab;
2781 CLOSE c_get_all_region_id;
2782
2783 x_region_tab := l_region_id_tab;
2784 --
2785 -- If p_use_cache is True then add to g_location_region_tab
2786 -- Return l_region_id_tab.
2787 --
2788
2789 IF (p_use_cache) and (p_location_id < g_cache_max_size) THEN
2790
2791 itr := l_region_id_tab.FIRST;
2792
2793 IF (itr ) IS NULL THEN
2794 -- Table contains no value,Set l_region_id_string to -1
2795 -- Do not return l_region_id_tab;
2796 l_region_id_string := '-1';
2797
2798 ELSE
2799 -- Return l_region_id_tab
2800 WSH_UTIL_CORE.get_string_from_idtab(
2801 p_id_tab => l_region_id_tab,
2802 x_string => l_region_id_string,
2803 x_return_status => l_return_status);
2804 END IF;
2805 g_location_region_tab(p_location_id) := l_region_id_string;
2806 END IF;
2807
2808 IF l_debug_on THEN
2809 WSH_DEBUG_SV.pop(l_module_name);
2810 END IF;
2811
2812 EXCEPTION
2813 WHEN others THEN
2814 IF c_get_all_region_id%ISOPEN THEN
2815 CLOSE c_get_all_region_id;
2816 END IF;
2817 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_RegionId_Matches');
2818 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2819 --
2820 IF l_debug_on THEN
2821 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2822 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2823 --Bug 4775798
2824 ELSE
2825 WSH_UTIL_CORE.printmsg('Unexpected error has occured in Get_All_RegionId_Matches');
2826 WSH_UTIL_CORE.printmsg('Oracle error message is '|| SQLERRM);
2827
2828 END IF;
2829 --
2830 END Get_All_RegionId_Matches;
2831
2832 --
2833 -- Procedure: Get_All_Zone_Matches
2834 --
2835 -- Purpose : The API derives Zones for an input Region .
2836 -- A cache is used for the region to zone mapping.
2837 --
2838
2839 PROCEDURE Get_All_Zone_Matches(
2840 p_region_id IN NUMBER,
2841 x_zone_tab OUT NOCOPY WSH_UTIL_CORE.Id_Tab_Type,
2842 x_return_status OUT NOCOPY VARCHAR2) IS
2843
2844 CURSOR c_get_zone_for_region(c_region_id IN NUMBER) IS
2845 SELECT wr.region_id
2846 FROM wsh_zone_regions wzr,wsh_regions wr
2847 WHERE wzr.region_id = c_region_id
2848 AND zone_flag = 'Y'
2849 AND wzr.parent_region_id = wr.region_id
2850 AND wr.region_type=10;
2851
2852 --
2853 l_zone_tab WSH_UTIL_CORE.Id_Tab_Type;
2854 l_zone_id_string VARCHAR2(32767);
2855 itr NUMBER;
2856 l_return_status VARCHAR2(1);
2857
2858 --
2859
2860 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'Get_All_Zone_Matches';
2861 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2862
2863 BEGIN
2864
2865 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2866
2867 IF l_debug_on THEN
2868 wsh_debug_sv.push(l_module_name);
2869 END IF;
2870
2871 IF (p_region_id < g_cache_max_size AND g_region_zone_tab.EXISTS(p_region_id)) THEN
2872
2873 WSH_UTIL_CORE.get_idtab_from_string(
2874 p_string => g_region_zone_tab(p_region_id),
2875 x_id_tab => l_zone_tab,
2876 x_return_status => l_return_status);
2877
2878 itr := l_zone_tab.FIRST;
2879
2880 IF (l_zone_tab(itr) <> -1) THEN
2881 x_zone_tab := l_zone_tab;
2882 END IF;
2883
2884 IF l_debug_on THEN
2885 wsh_debug_sv.pop(l_module_name);
2886 END IF;
2887 RETURN;
2888 END IF;
2889
2890 OPEN c_get_zone_for_region(p_region_id);
2891 FETCH c_get_zone_for_region BULK COLLECT INTO l_zone_tab;
2892 CLOSE c_get_zone_for_region;
2893
2894 x_zone_tab := l_zone_tab;
2895
2896 --
2897 -- Add zones to the global cache.
2898 -- If no zones are associated with the region
2899 -- 1) Store -1 in the cache.
2900 -- 2) x_zone_tab is NULL.
2901
2902
2903 IF (p_region_id < g_cache_max_size ) THEN
2904
2905 itr := l_zone_tab.FIRST;
2906
2907 IF (itr ) IS NULL THEN
2908 l_zone_id_string := '-1';
2909 ELSE
2910
2911 WSH_UTIL_CORE.get_string_from_idtab(
2912 p_id_tab => l_zone_tab,
2913 x_string => l_zone_id_string,
2914 x_return_status => l_return_status);
2915
2916 END IF;
2917
2918 g_region_zone_tab(p_region_id) := l_zone_id_string;
2919
2920 END IF;
2921
2922 IF l_debug_on THEN
2923 WSH_DEBUG_SV.pop(l_module_name);
2924 END IF;
2925
2926 EXCEPTION
2927 WHEN others THEN
2928 IF c_get_zone_for_region%ISOPEN THEN
2929 CLOSE c_get_zone_for_region;
2930 END IF;
2931 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.Get_All_Zone_Matches');
2932 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2933 --
2934 IF l_debug_on THEN
2935 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2936 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
2937 END IF;
2938 --
2939 END Get_All_Zone_Matches;
2940
2941 --***************************************************************************--
2942 --========================================================================
2943 -- PROCEDURE : get_all_region_deconsols
2944 --
2945 -- PARAMETERS: p_location_id Input delivery record
2946 -- p_use_cache Whether to use cache
2947 -- p_lang_code Language Code
2948 -- p_zone_flag Whether to perform search at zone level as well
2949 -- p_rule_to_zone_id zone id specified in the consolidation rule
2950 -- p_caller Caller of API
2951 -- x_region_consol_tab Table of regions containing info
2952 -- about deconsol locations for given location
2953 -- x_return_status Return status
2954 -- COMMENT : This procedure is used to perform for following actions
2955 -- Takes input location id
2956 -- Finds deconsolidation location for the location as defined on Regions and
2957 -- zones form
2958 --========================================================================
2959
2960 PROCEDURE get_all_region_deconsols (
2961 p_location_id IN NUMBER,
2962 p_use_cache IN BOOLEAN DEFAULT FALSE,
2963 p_lang_code IN VARCHAR2,
2964 p_zone_flag IN BOOLEAN DEFAULT FALSE,
2965 p_rule_to_zone_id IN NUMBER DEFAULT NULL,
2966 p_caller IN VARCHAR2 DEFAULT NULL,
2967 x_region_consol_tab OUT NOCOPY region_deconsol_Tab_Type,
2968 x_return_status OUT NOCOPY VARCHAR2)
2969 IS
2970
2971 l_region_id NUMBER;
2972 itr NUMBER;
2973 r_itr NUMBER;
2974 l_region_tab region_deconsol_Tab_Type;
2975 l_region_deconsol_tab region_deconsol_Tab_Type;
2976 l_zone_deconsol_tab region_deconsol_Tab_Type;
2977 l_reg_zon_deconsol_tab region_deconsol_Tab_Type;
2978 l_return_status VARCHAR2(1);
2979 z_itr NUMBER;
2980 rg_itr NUMBER;
2981 r_nrec NUMBER;
2982 i NUMBER;
2983 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'get_all_region_deconsols';
2984 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2985
2986 CURSOR c_get_all_regions_loc(c_location_id IN NUMBER, c_lang_code IN VARCHAR2) IS
2987 SELECT wrl.region_id,
2988 wr.region_type,
2989 wr.deconsol_location_id
2990 FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
2991 WHERE wrl.location_id = c_location_id
2992 AND wrl.region_id IS NOT NULL
2993 AND wrl.region_id = wrt.region_id
2994 AND wrt.language = nvl('US',wrt.language)
2995 AND wrt.region_id = wr.region_id
2996 --AND wr.deconsol_location_id IS NOT NULL
2997 ORDER BY wr.region_type DESC;
2998
2999 CURSOR c_get_zone_for_region(c_region_id IN NUMBER) IS
3000 SELECT wr.region_id,
3001 --null,
3002 wr.region_type,
3003 wr.deconsol_location_id
3004 FROM wsh_zone_regions wzr,wsh_regions wr
3005 WHERE wzr.region_id = c_region_id
3006 AND zone_flag = 'Y'
3007 AND wzr.parent_region_id = wr.region_id
3008 AND wr.region_type=10
3009 AND wr.deconsol_location_id IS NOT NULL
3010 ORDER BY wr.region_type DESC;
3011 BEGIN
3012 ----{
3013 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3014
3015 IF l_debug_on THEN
3016 wsh_debug_sv.push (l_module_name);
3017 END IF;
3018
3019 IF l_debug_on THEN
3020 WSH_DEBUG_SV.logmsg(l_module_name,'p_location_id: '|| p_location_id);
3021 END IF;
3022
3023 -- If region deconsolidation location tab exists in cache, return
3024 -- else query database through c_get_all_regions_loc cursor
3025
3026 IF p_use_cache = TRUE AND p_location_id < g_cache_max_size THEN
3027
3028 IF p_zone_flag = TRUE AND g_loc_region_zone_deconsol_tab.EXISTS(p_location_id) THEN
3029 x_region_consol_tab := g_loc_region_zone_deconsol_tab(p_location_id);
3030 IF l_debug_on THEN
3031 WSH_DEBUG_SV.pop(l_module_name);
3032 END IF;
3033 return;
3034 ELSIF g_loc_region_deconsol_tab.EXISTS(p_location_id) THEN
3035 x_region_consol_tab := g_loc_region_deconsol_tab(p_location_id);
3036 IF l_debug_on THEN
3037 WSH_DEBUG_SV.pop(l_module_name);
3038 END IF;
3039 return;
3040 ELSE
3041 OPEN c_get_all_regions_loc(p_location_id,p_lang_code);
3042 FETCH c_get_all_regions_loc BULK COLLECT INTO l_region_tab;
3043 CLOSE c_get_all_regions_loc;
3044 END IF;
3045 ELSIF (NOT g_loc_region_deconsol_tab.EXISTS(p_location_id)) OR p_use_cache = FALSE THEN
3046
3047 OPEN c_get_all_regions_loc(p_location_id,p_lang_code);
3048 FETCH c_get_all_regions_loc BULK COLLECT INTO l_region_tab;
3049 CLOSE c_get_all_regions_loc;
3050 END IF;
3051
3052 IF l_debug_on THEN
3053 WSH_DEBUG_SV.logmsg(l_module_name,'l_region_tab.COUNT: '|| l_region_tab.COUNT);
3054 END IF;
3055
3056 i := 1;
3057 IF p_use_cache = TRUE THEN
3058 itr := l_region_tab.FIRST;
3059 IF itr IS NOT NULL THEN
3060 LOOP
3061 IF l_region_tab(itr).Deconsol_location IS NOT NULL THEN
3062 l_region_deconsol_tab(i) := l_region_tab(itr);
3063 END IF;
3064
3065 EXIT WHEN itr = l_region_tab.LAST;
3066 itr := l_region_tab.NEXT(itr);
3067 i := i+1;
3068 END LOOP;
3069 END IF;
3070 g_loc_region_deconsol_tab(p_location_id) := l_region_deconsol_tab;
3071 END IF;
3072
3073
3074 -- If search for zone level deconsolidation locations is true
3075 -- Loop through regions tab to find corresponding zone level deconsolidation locations
3076
3077 IF p_zone_flag = TRUE THEN
3078 rg_itr := 0;
3079 r_itr := l_region_tab.FIRST;
3080 IF r_itr IS NOT NULL THEN
3081 LOOP
3082 rg_itr := rg_itr+1;
3083 l_region_id := l_region_tab(r_itr).region_id;
3084
3085 -- If deconsol location exists for region, then add it to deconsol_tab
3086 IF l_region_tab(r_itr).Deconsol_location IS NOT NULL THEN
3087 l_reg_zon_deconsol_tab(rg_itr) := l_region_tab(r_itr);
3088 IF l_debug_on THEN
3089 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);
3090 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);
3091 END IF;
3092 END IF;
3093
3094 IF p_use_cache = TRUE AND l_region_id < g_cache_max_size THEN
3095 IF (g_region_zone_deconsol_tab.EXISTS(l_region_id)) THEN
3096 l_zone_deconsol_tab := g_region_zone_deconsol_tab(l_region_id);
3097 --g_region_zone_deconsol_tab(l_region_id) := l_zone_deconsol_tab;
3098 ELSE
3099 OPEN c_get_zone_for_region(l_region_id);
3100 FETCH c_get_zone_for_region BULK COLLECT INTO l_zone_deconsol_tab;
3101 CLOSE c_get_zone_for_region;
3102 END IF;
3103
3104 IF l_debug_on THEN
3105 WSH_DEBUG_SV.logmsg(l_module_name,'l_zone_deconsol_tab.COUNT: '|| l_zone_deconsol_tab.COUNT);
3106 END IF;
3107 ELSIF NOT (g_region_zone_deconsol_tab.EXISTS(l_region_id)) OR p_use_cache = FALSE THEN
3108 OPEN c_get_zone_for_region(l_region_id);
3109 FETCH c_get_zone_for_region BULK COLLECT INTO l_zone_deconsol_tab;
3110 CLOSE c_get_zone_for_region;
3111 END IF;
3112
3113 IF l_debug_on THEN
3114 WSH_DEBUG_SV.logmsg(l_module_name,'l_zone_deconsol_tab.COUNT: '|| l_zone_deconsol_tab.COUNT);
3115 END IF;
3116 -- IF p_rule_to_zone_id IS NOT NULL:
3117 -- If caller is not WMS: If at any level of regions the zone(s) obtained do not match
3118 -- with input p_rule_to_zone_id, that level of zone should be skipped.
3119 --
3120 -- If caller is WMS: the zone level should be skipped completely if multiple
3121 -- zones found for any region.
3122 --
3123 IF p_rule_to_zone_id IS NOT NULL THEN
3124 IF NOT (p_caller like 'WMS%' AND l_zone_deconsol_tab.COUNT >1) THEN
3125 z_itr := l_zone_deconsol_tab.FIRST;
3126 IF z_itr IS NOT NULL THEN
3127 LOOP
3128 IF l_zone_deconsol_tab(z_itr).region_id = p_rule_to_zone_id THEN
3129 rg_itr := rg_itr+1;
3130 l_reg_zon_deconsol_tab(rg_itr) := l_zone_deconsol_tab(z_itr);
3131 IF l_debug_on THEN
3132 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);
3133 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);
3134 END IF;
3135 EXIT;
3136 END IF;
3137
3138 EXIT WHEN z_itr = l_zone_deconsol_tab.LAST;
3139 z_itr:= l_zone_deconsol_tab.NEXT(z_itr);
3140 END LOOP;
3141 END IF;
3142 END IF;
3143 ELSE
3144 z_itr := l_zone_deconsol_tab.FIRST;
3145 IF z_itr IS NOT NULL THEN
3146 LOOP
3147 rg_itr := rg_itr+1;
3148 l_reg_zon_deconsol_tab(rg_itr) := l_zone_deconsol_tab(z_itr);
3149 IF l_debug_on THEN
3150 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);
3151 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);
3152 END IF;
3153
3154 EXIT WHEN z_itr = l_zone_deconsol_tab.LAST;
3155 z_itr:= l_zone_deconsol_tab.NEXT(z_itr);
3156 END LOOP;
3157 END IF;
3158 END IF;
3159
3160 EXIT WHEN r_itr = l_region_tab.LAST;
3161 r_itr:= l_region_tab.NEXT(r_itr);
3162 END LOOP;
3163
3164 END IF;
3165 x_region_consol_tab := l_reg_zon_deconsol_tab;
3166
3167 IF p_zone_flag THEN
3168 g_loc_region_zone_deconsol_tab(p_location_id) := l_reg_zon_deconsol_tab;
3169 END IF;
3170 ELSE
3171 x_region_consol_tab := l_region_deconsol_tab;
3172 END IF;
3173
3174 IF l_debug_on THEN
3175 WSH_DEBUG_SV.logmsg(l_module_name,'x_region_consol_tab.COUNT: '|| x_region_consol_tab.COUNT);
3176 END IF;
3177
3178 IF l_debug_on THEN
3179 WSH_DEBUG_SV.logmsg(l_module_name,'x_return_status: '|| x_return_status);
3180 END IF;
3181
3182 IF l_debug_on THEN
3183 wsh_debug_sv.pop (l_module_name);
3184 END IF;
3185 ----}
3186
3187 EXCEPTION
3188
3189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3190 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3191 --
3192 IF l_debug_on THEN
3193 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3194 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3195 END IF;
3196
3197 WHEN OTHERS THEN
3198 IF c_get_all_regions_loc%ISOPEN THEN
3199 CLOSE c_get_all_regions_loc;
3200 END IF;
3201 IF c_get_zone_for_region%ISOPEN THEN
3202 CLOSE c_get_zone_for_region;
3203 END IF;
3204 WSH_UTIL_CORE.default_handler('WSH_REGIONS_SEARCH_PKG.get_all_region_deconsols');
3205 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3206 --
3207 IF l_debug_on THEN
3208 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3209 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:FND_API.G_EXC_UNEXPECTED_ERROR');
3210 END IF;
3211
3212 END get_all_region_deconsols;
3213
3214 -- Following procedure are added for Regions Interface Performance
3215
3216 --
3217 -- PROCEDURE : Check_Region_Info
3218 --
3219 -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data
3220 -- table based on parameters passed to it.
3221 PROCEDURE Check_Region_Info (
3222 p_country IN VARCHAR2,
3223 p_state IN VARCHAR2,
3224 p_city IN VARCHAR2,
3225 p_postal_code_from IN VARCHAR2,
3226 p_postal_code_to IN VARCHAR2,
3227 p_region_type IN NUMBER,
3228 p_search_flag IN VARCHAR2,
3229 p_lang_code IN VARCHAR2,
3230 x_return_status OUT NOCOPY VARCHAR2,
3231 x_region_info OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec)
3232 IS
3233
3234 CURSOR C1 IS
3235 SELECT REGION_ID
3236 FROM WSH_REGIONS_GLOBAL_DATA
3237 WHERE ( city = UPPER(p_city) or p_city is null )
3238 AND ( state = UPPER(p_state) or p_state is null )
3239 AND country = UPPER(p_country)
3240 AND ( p_postal_code_from is null
3241 OR ( ( p_postal_code_from between postal_code_from and postal_code_to )
3242 OR ( p_postal_code_to between postal_code_from and postal_code_to ) ) )
3243 AND region_type = p_region_type
3244 AND language = p_lang_code;
3245
3246 CURSOR C2 IS
3247 SELECT REGION_ID
3248 FROM WSH_REGIONS_GLOBAL_DATA
3249 WHERE ( city = UPPER(p_city) )
3250 AND ( nvl(state, UPPER(p_state)) = UPPER(p_state) )
3251 AND country = UPPER(p_country)
3252 AND ( p_postal_code_from is null
3253 OR ( ( p_postal_code_from between postal_code_from and postal_code_to )
3254 OR ( p_postal_code_to between postal_code_from and postal_code_to ) ) )
3255 AND region_type = p_region_type
3256 AND language = p_lang_code;
3257
3258 TYPE tmp_table is table of NUMBER index by binary_integer;
3259 tmp_region_id tmp_table;
3260 t1 NUMBER;
3261
3262 --
3263 l_debug_on BOOLEAN;
3264 --
3265 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_REGION_INFO';
3266 --
3267 BEGIN
3268 --
3269 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3270 --
3271 IF l_debug_on IS NULL
3272 THEN
3273 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3274 END IF;
3275 --
3276 --
3277 -- Debug Statements
3278 --
3279 IF l_debug_on THEN
3280 WSH_DEBUG_SV.push(l_module_name);
3281 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3282 WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3283 WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3284 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3285 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3286 WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3287 WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3288 WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3289 END IF;
3290 --
3291
3292 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3293
3294 t1 := dbms_utility.get_time;
3295
3296 IF ( p_state is not null and p_city is not null )
3297 THEN
3298 OPEN C2;
3299 FETCH C2 BULK COLLECT INTO TMP_REGION_ID;
3300 CLOSE C2;
3301 ELSE
3302 OPEN C1;
3303 FETCH C1 BULK COLLECT INTO TMP_REGION_ID;
3304 CLOSE C1;
3305 END IF;
3306
3307 IF ( TMP_REGION_ID.COUNT > 0 ) THEN
3308 x_region_info.region_id := TMP_REGION_ID(1);
3309 ELSE
3310 x_region_info.region_id := -1;
3311 END IF;
3312
3313 --
3314 -- Debug Statements
3315 --
3316 IF l_debug_on THEN
3317 WSH_DEBUG_SV.log(l_module_name, 'Region_Id Fetched', x_region_info.region_id);
3318 END IF;
3319 --
3320
3321 --
3322 -- Debug Statements
3323 --
3324 IF l_debug_on THEN
3325 WSH_DEBUG_SV.pop(l_module_name);
3326 END IF;
3327 --
3328
3329 -- Exception Handling part
3330 EXCEPTION
3331 WHEN OTHERS THEN
3332 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3333 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
3334 fnd_message.set_token('MSG_TEXT', SQLERRM);
3335 IF l_debug_on THEN
3336 wsh_debug_sv.logmsg(l_module_name, 'CHECK_REGION_INFO EXCEPTION : ' || sqlerrm);
3337 WSH_DEBUG_SV.pop(l_module_name);
3338 END IF;
3339
3340 END;
3341
3342 --
3343 -- PROCEDURE : Check_Region_Info_Code
3344 --
3345 -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data
3346 -- table based on parameters passed to it.
3347 PROCEDURE Check_Region_Info_Code (
3348 p_country IN VARCHAR2,
3349 p_state IN VARCHAR2,
3350 p_city IN VARCHAR2,
3351 p_country_code IN VARCHAR2,
3352 p_state_code IN VARCHAR2,
3353 p_city_code IN VARCHAR2,
3354 p_region_type IN NUMBER,
3355 p_search_flag IN VARCHAR2,
3356 p_lang_code IN VARCHAR2,
3357 x_return_status OUT NOCOPY VARCHAR2,
3358 x_region_info OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec)
3359 IS
3360
3361 CURSOR C1 IS
3362 SELECT REGION_ID
3363 FROM WSH_REGIONS_GLOBAL_DATA
3364 WHERE ( city = UPPER(p_city) or p_city is null )
3365 AND ( decode(p_city, null, state, nvl(state, UPPER(p_state))) = UPPER(p_state)
3366 OR ( p_state is null ) )
3367 AND country = UPPER(p_country)
3368 AND ( city_code = UPPER(p_city_code) or p_city_code is null )
3369 AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
3370 OR ( p_state_code is null ) )
3371 AND country_code = UPPER(p_country_code)
3372 AND region_type = p_region_type
3373 AND language = p_lang_code;
3374
3375 TYPE tmp_table is table of NUMBER index by binary_integer;
3376 tmp_region_id tmp_table;
3377
3378 t1 NUMBER;
3379
3380 --
3381 l_debug_on BOOLEAN;
3382 --
3383 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_REGION_INFO_CODE';
3384 --
3385 BEGIN
3386 --
3387 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3388 --
3389 IF l_debug_on IS NULL
3390 THEN
3391 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3392 END IF;
3393 --
3394 --
3395 -- Debug Statements
3396 --
3397 IF l_debug_on THEN
3398 WSH_DEBUG_SV.push(l_module_name);
3399 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
3400 WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
3401 WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
3402 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY-CODE', P_COUNTRY_CODE);
3403 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3404 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3405 WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3406 WSH_DEBUG_SV.log(l_module_name, 'P_SEARCH_FLAG', P_SEARCH_FLAG);
3407 WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
3408 END IF;
3409 --
3410
3411 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3412
3413 t1 := dbms_utility.get_time;
3414 OPEN C1;
3415 FETCH C1 BULK COLLECT INTO TMP_REGION_ID;
3416 CLOSE C1;
3417
3418 IF ( TMP_REGION_ID.COUNT > 0 ) THEN
3419 x_region_info.region_id := TMP_REGION_ID(1);
3420 ELSE
3421 x_region_info.region_id := -1;
3422 END IF;
3423
3424 --
3425 -- Debug Statements
3426 --
3427 IF l_debug_on THEN
3428 WSH_DEBUG_SV.log(l_module_name, 'Region_Id Fetched', x_region_info.region_id);
3429 END IF;
3430 --
3431
3432 --
3433 -- Debug Statements
3434 --
3435 IF l_debug_on THEN
3436 WSH_DEBUG_SV.pop(l_module_name);
3437 END IF;
3438 --
3439
3440 -- Exception Handling part
3441 EXCEPTION
3442 WHEN OTHERS THEN
3443 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3444 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
3445 fnd_message.set_token('MSG_TEXT', SQLERRM);
3446 IF l_debug_on THEN
3447 wsh_debug_sv.logmsg(l_module_name, 'CHECK_REGION_INFO_CODE EXCEPTION : ' || SUBSTR(SQLERRM,1,200));
3448 WSH_DEBUG_SV.pop(l_module_name);
3449 END IF;
3450
3451 END;
3452
3453 --
3454 -- PROCEDURE : Check_Region_Id_Codes_Only
3455 --
3456 -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data/
3457 -- Wsh_Regions_Global table based on parameters passed to it.
3458
3459 PROCEDURE Check_Region_Id_Codes_Only (
3460 p_country_code IN VARCHAR2,
3461 p_state_code IN VARCHAR2,
3462 p_city_code IN VARCHAR2,
3463 p_postal_code_from IN VARCHAR2,
3464 p_postal_code_to IN VARCHAR2,
3465 p_region_type IN NUMBER,
3466 p_language_code IN VARCHAR2 DEFAULT NULL,
3467 x_return_status OUT NOCOPY VARCHAR2,
3468 x_region_id OUT NOCOPY NUMBER )
3469 IS
3470 CURSOR C1 IS
3471 SELECT REGION_ID
3472 FROM WSH_REGIONS_GLOBAL_DATA
3473 WHERE ( city_code = UPPER(p_city_code) or p_city_code is null )
3474 AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
3475 OR ( p_state_code is null ) )
3476 AND country_code = UPPER(p_country_code)
3477 AND ( p_postal_code_from is null
3478 OR ( ( p_postal_code_from between postal_code_from and postal_code_to )
3479 OR ( p_postal_code_to between postal_code_from and postal_code_to ) ) )
3480 AND region_type = p_region_type
3481 AND language = nvl(p_language_code, language);
3482
3483 CURSOR C2 IS
3484 SELECT REGION_ID
3485 FROM WSH_REGIONS_GLOBAL
3486 WHERE ( city_code = UPPER(p_city_code) or p_city_code is null )
3487 AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
3488 OR ( p_state_code is null ) )
3489 AND country_code = UPPER(p_country_code)
3490 AND region_type = p_region_type;
3491
3492 TYPE tmp_table is table of NUMBER index by binary_integer;
3493 tmp_region_id tmp_table;
3494
3495 l_with_tl_flag BOOLEAN DEFAULT FALSE;
3496 l_non_tl_flag BOOLEAN DEFAULT FALSE;
3497 t1 NUMBER;
3498
3499 --
3500 l_debug_on BOOLEAN;
3501 --
3502 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'CHECK_REGION_ID_CODES_ONLY';
3503 --
3504 BEGIN
3505 --
3506 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3507 --
3508 IF l_debug_on IS NULL
3509 THEN
3510 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3511 END IF;
3512 --
3513 --
3514 -- Debug Statements
3515 --
3516 IF l_debug_on THEN
3517 WSH_DEBUG_SV.push(l_module_name);
3518 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
3519 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
3520 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
3521 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
3522 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
3523 WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
3524 WSH_DEBUG_SV.log(l_module_name, 'P_LANGUAGE_CODE', P_LANGUAGE_CODE);
3525 END IF;
3526 --
3527
3528 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
3529
3530 IF ( ( p_country_code is null and p_state_code is null and p_city_code is null)
3531 OR ( p_region_type = 2 and p_city_code is null )
3532 OR ( p_region_type = 1 and p_state_code is null )
3533 OR ( p_region_type = 0 and p_country_code is null ) )
3534 THEN
3535 x_region_id := -1;
3536 return;
3537 END IF;
3538
3539 IF ( p_postal_code_from is not null or p_language_code is not null ) THEN
3540 l_with_tl_flag := TRUE;
3541 ELSE
3542 l_non_tl_flag := TRUE;
3543 END IF;
3544
3545 IF ( l_with_tl_flag ) THEN
3546 t1 := dbms_utility.get_time;
3547 OPEN C1;
3548 FETCH C1 BULK COLLECT INTO TMP_REGION_ID;
3549 CLOSE C1;
3550
3551 IF ( TMP_REGION_ID.COUNT > 0 ) THEN
3552 x_region_id := TMP_REGION_ID(1);
3553 ELSE
3554 x_region_id := -1;
3555 END IF;
3556 ELSE
3557 t1 := dbms_utility.get_time;
3558 OPEN C2;
3559 FETCH C2 BULK COLLECT INTO TMP_REGION_ID;
3560 CLOSE C2;
3561
3562 IF ( TMP_REGION_ID.COUNT > 0 ) THEN
3563 x_region_id := TMP_REGION_ID(1);
3564 ELSE
3565 x_region_id := -1;
3566 END IF;
3567
3568 END IF;
3569
3570 --
3571 -- Debug Statements
3572 --
3573 IF l_debug_on THEN
3574 WSH_DEBUG_SV.log(l_module_name, 'Region_Id Fetched', x_region_id);
3575 END IF;
3576 --
3577
3578 --
3579 -- Debug Statements
3580 --
3581 IF l_debug_on THEN
3582 WSH_DEBUG_SV.pop(l_module_name);
3583 END IF;
3584 --
3585
3586 --wsh_debug_sv.logmsg(l_module_name, 'TOTAL TIME TAKEN FOR CHECK_REGION_ID_CODES_ONLY : ' || ((dbms_utility.get_time - t1)/100));
3587 -- Exception Handling part
3588 EXCEPTION
3589 WHEN OTHERS THEN
3590 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
3591 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
3592 fnd_message.set_token('MSG_TEXT', SQLERRM);
3593 IF l_debug_on THEN
3594 wsh_debug_sv.logmsg(l_module_name, 'CHECK_REGION_ID_CODES_ONLY EXCEPTION : ' || SUBSTR(SQLERRM,1,200));
3595 WSH_DEBUG_SV.pop(l_module_name);
3596 END IF;
3597
3598 END;
3599
3600
3601 END WSH_REGIONS_SEARCH_PKG;