[Home] [Help]
PACKAGE BODY: APPS.WSH_REGIONS_PKG
Source
1 PACKAGE BODY WSH_REGIONS_PKG AS
2 /* $Header: WSHRETHB.pls 120.5 2010/11/09 22:56:29 skanduku ship $ */
3
4 --
5 -- Package
6 -- WSH_REGIONS_PKG
7 --
8 -- Purpose
9 --
10
11 --
12 -- PACKAGE TYPES
13 --
14
15 --
16 -- PUBLIC VARIABLES
17 --
18
19 --
20 -- PRIVATE FUNCTIONS/PROCEDURES
21 --
22
23 --
24 -- Procedure: Add_Region
25 --
26 -- Purpose: Inserts the region with appropriate data and returns the
27 -- region_id
28 --
29
30 --
31 G_PKG_NAME CONSTANT VARCHAR2(50) := 'WSH_REGIONS_PKG';
32 --
33 PROCEDURE Add_Region (
34 p_country_code IN VARCHAR2,
35 p_country_region_code IN VARCHAR2,
36 p_state_code IN VARCHAR2,
37 p_city_code IN VARCHAR2,
38 p_port_flag IN VARCHAR2,
39 p_airport_flag IN VARCHAR2,
40 p_road_terminal_flag IN VARCHAR2,
41 p_rail_terminal_flag IN VARCHAR2,
42 p_longitude IN NUMBER,
43 p_latitude IN NUMBER,
44 p_timezone IN VARCHAR2,
45 p_continent IN VARCHAR2,
46 p_country IN VARCHAR2,
47 p_country_region IN VARCHAR2,
48 p_state IN VARCHAR2,
49 p_city IN VARCHAR2,
50 p_alternate_name IN VARCHAR2,
51 p_county IN VARCHAR2,
52 p_postal_code_from IN VARCHAR2,
53 p_postal_code_to IN VARCHAR2,
54 p_lang_code IN VARCHAR2,
55 p_region_type IN NUMBER,
56 p_parent_region_id IN NUMBER,
57 p_interface_flag IN VARCHAR2,
58 p_tl_only_flag IN VARCHAR2,
59 p_region_id IN NUMBER,
60 p_region_dff IN REGION_DFF_REC DEFAULT NULL,
61 x_region_id OUT NOCOPY NUMBER,
62 p_deconsol_location_id IN NUMBER DEFAULT NULL) IS
63
64
65 CURSOR get_region_id IS
66 SELECT WSH_REGIONS_s.nextval from dual;
67
68 CURSOR get_interface_region_id IS
69 SELECT WSH_REGIONS_INTERFACE_S.nextval from dual;
70
71 l_region_id NUMBER;
72
73 --
74 l_debug_on BOOLEAN;
75 --
76 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'ADD_REGION';
77 --
78 BEGIN
79
80 --
81 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
82 --
83 IF l_debug_on IS NULL
84 THEN
85 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
86 END IF;
87 --
88 --
89 -- Debug Statements
90 --
91 IF l_debug_on THEN
92 WSH_DEBUG_SV.push(l_module_name);
93 --
94 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
95 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
96 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
97 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
98 WSH_DEBUG_SV.log(l_module_name,'P_PORT_FLAG',P_PORT_FLAG);
99 WSH_DEBUG_SV.log(l_module_name,'P_AIRPORT_FLAG',P_AIRPORT_FLAG);
100 WSH_DEBUG_SV.log(l_module_name,'P_ROAD_TERMINAL_FLAG',P_ROAD_TERMINAL_FLAG);
101 WSH_DEBUG_SV.log(l_module_name,'P_RAIL_TERMINAL_FLAG',P_RAIL_TERMINAL_FLAG);
102 WSH_DEBUG_SV.log(l_module_name,'P_LONGITUDE',P_LONGITUDE);
103 WSH_DEBUG_SV.log(l_module_name,'P_LATITUDE',P_LATITUDE);
104 WSH_DEBUG_SV.log(l_module_name,'P_TIMEZONE',P_TIMEZONE);
105 WSH_DEBUG_SV.log(l_module_name,'P_CONTINENT',P_CONTINENT);
106 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
107 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
108 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
109 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
110 WSH_DEBUG_SV.log(l_module_name,'P_ALTERNATE_NAME',P_ALTERNATE_NAME);
111 WSH_DEBUG_SV.log(l_module_name,'P_COUNTY',P_COUNTY);
112 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
113 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
114 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
115 WSH_DEBUG_SV.log(l_module_name,'P_REGION_TYPE',P_REGION_TYPE);
116 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_REGION_ID',P_PARENT_REGION_ID);
117 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
118 WSH_DEBUG_SV.log(l_module_name,'P_TL_ONLY_FLAG',P_TL_ONLY_FLAG);
119 WSH_DEBUG_SV.log(l_module_name,'P_REGION_ID',P_REGION_ID);
120 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE_CATEGORY',P_REGION_DFF.ATTRIBUTE_CATEGORY);
121 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE1',P_REGION_DFF.ATTRIBUTE1);
122 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE2',P_REGION_DFF.ATTRIBUTE2);
123 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE3',P_REGION_DFF.ATTRIBUTE3);
124 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE4',P_REGION_DFF.ATTRIBUTE4);
125 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE5',P_REGION_DFF.ATTRIBUTE5);
126 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE6',P_REGION_DFF.ATTRIBUTE6);
127 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE7',P_REGION_DFF.ATTRIBUTE7);
128 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE8',P_REGION_DFF.ATTRIBUTE8);
129 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE9',P_REGION_DFF.ATTRIBUTE9);
130 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE10',P_REGION_DFF.ATTRIBUTE10);
131 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE11',P_REGION_DFF.ATTRIBUTE11);
132 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE12',P_REGION_DFF.ATTRIBUTE12);
133 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE13',P_REGION_DFF.ATTRIBUTE13);
134 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE14',P_REGION_DFF.ATTRIBUTE14);
135 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE15',P_REGION_DFF.ATTRIBUTE15);
136 WSH_DEBUG_SV.log(l_module_name, 'P_DECONSOL_LOCATION_ID', P_DECONSOL_LOCATION_ID);
137 END IF;
138 --
139 IF (p_tl_only_flag = 'Y') THEN
140
141 l_region_id := p_region_id;
142
143 ELSE
144
145 IF (p_interface_flag = 'Y') THEN
146
147 OPEN get_interface_region_id;
148 FETCH get_interface_region_id INTO l_region_id;
149 CLOSE get_interface_region_id;
150
151 ELSE
152
153 OPEN get_region_id;
154 FETCH get_region_id INTO l_region_id;
155 CLOSE get_region_id;
156
157 END IF;
158
159 END IF;
160
161 IF (p_interface_flag = 'Y') THEN
162
163 IF (p_tl_only_flag <> 'Y') THEN
164
165 -- fnd_file.put_line(fnd_file.Log, 'inserting into fte regions interface, interface and tl only flag both = Y');
166
167 INSERT INTO WSH_REGIONS_INTERFACE (
168 REGION_ID,
169 REGION_TYPE,
170 PARENT_REGION_ID,
171 COUNTRY_CODE,
172 COUNTRY_REGION_CODE,
173 STATE_CODE,
174 CITY_CODE,
175 PORT_FLAG,
176 AIRPORT_FLAG,
177 ROAD_TERMINAL_FLAG,
178 RAIL_TERMINAL_FLAG,
179 LONGITUDE,
180 LATITUDE,
181 TIMEZONE,
182 CREATED_BY,
183 CREATION_DATE,
184 LAST_UPDATED_BY,
185 LAST_UPDATE_DATE,
186 LAST_UPDATE_LOGIN)
187 VALUES (
188 l_region_id,
189 p_region_type,
190 p_parent_region_id,
191 p_country_code,
192 p_country_region_code,
193 p_state_code,
194 p_city_code,
195 p_port_flag,
196 p_airport_flag,
197 p_road_terminal_flag,
198 p_rail_terminal_flag,
199 p_longitude,
200 p_latitude,
201 p_timezone,
202 fnd_global.user_id,
203 sysdate,
204 fnd_global.user_id,
205 sysdate,
206 fnd_global.login_id);
207
208 END IF;
209
210 INSERT INTO WSH_REGIONS_TL_INTERFACE (
211 LANGUAGE,
212 REGION_ID,
213 CONTINENT,
214 COUNTRY,
215 COUNTRY_REGION,
216 STATE,
217 CITY,
218 ALTERNATE_NAME,
219 COUNTY,
220 POSTAL_CODE_FROM,
221 POSTAL_CODE_TO,
222 CREATED_BY,
223 CREATION_DATE,
224 LAST_UPDATED_BY,
225 LAST_UPDATE_DATE,
226 LAST_UPDATE_LOGIN)
227 VALUES (
228 p_lang_code,
229 l_region_id,
230 p_continent,
231 p_country,
232 p_country_region,
233 p_state,
234 p_city,
235 p_alternate_name,
236 p_county,
237 p_postal_code_from,
238 p_postal_code_to,
239 fnd_global.user_id,
240 sysdate,
241 fnd_global.user_id,
242 sysdate,
243 fnd_global.login_id);
244
245 ELSE
246
247 IF (p_tl_only_flag <> 'Y') THEN
248
249 INSERT INTO WSH_REGIONS (
250 REGION_ID,
251 REGION_TYPE,
252 PARENT_REGION_ID,
253 COUNTRY_CODE,
254 COUNTRY_REGION_CODE,
255 STATE_CODE,
256 CITY_CODE,
257 PORT_FLAG,
258 AIRPORT_FLAG,
259 ROAD_TERMINAL_FLAG,
260 RAIL_TERMINAL_FLAG,
261 LONGITUDE,
262 LATITUDE,
263 TIMEZONE,
264 ZONE_LEVEL,
265 ATTRIBUTE_CATEGORY,
266 ATTRIBUTE1,
267 ATTRIBUTE2,
268 ATTRIBUTE3,
269 ATTRIBUTE4,
270 ATTRIBUTE5,
271 ATTRIBUTE6,
272 ATTRIBUTE7,
273 ATTRIBUTE8,
274 ATTRIBUTE9,
275 ATTRIBUTE10,
276 ATTRIBUTE11,
277 ATTRIBUTE12,
278 ATTRIBUTE13,
279 ATTRIBUTE14,
280 ATTRIBUTE15,
281 CREATED_BY,
282 CREATION_DATE,
283 LAST_UPDATED_BY,
284 LAST_UPDATE_DATE,
285 LAST_UPDATE_LOGIN,
286 DECONSOL_LOCATION_ID )
287 VALUES (
288 l_region_id,
289 p_region_type,
290 p_parent_region_id,
291 p_country_code,
292 p_country_region_code,
293 p_state_code,
294 p_city_code,
295 p_port_flag,
296 p_airport_flag,
297 p_road_terminal_flag,
298 p_rail_terminal_flag,
299 p_longitude,
300 p_latitude,
301 p_timezone,
302 p_region_type,
303 p_region_dff.attribute_category,
304 p_region_dff.attribute1,
305 p_region_dff.attribute2,
306 p_region_dff.attribute3,
307 p_region_dff.attribute4,
308 p_region_dff.attribute5,
309 p_region_dff.attribute6,
310 p_region_dff.attribute7,
311 p_region_dff.attribute8,
312 p_region_dff.attribute9,
313 p_region_dff.attribute10,
314 p_region_dff.attribute11,
315 p_region_dff.attribute12,
316 p_region_dff.attribute13,
317 p_region_dff.attribute14,
318 p_region_dff.attribute15,
319 fnd_global.user_id,
320 sysdate,
321 fnd_global.user_id,
322 sysdate,
323 fnd_global.login_id,
324 p_deconsol_location_id);
325
326 END IF;
327
328 -- Debug Statements
329 --
330 IF l_debug_on THEN
331 WSH_DEBUG_SV.logmsg(l_module_name, 'inserting region '||l_region_id);
332 END IF;
333
334 INSERT INTO WSH_REGIONS_TL (
335 LANGUAGE,
336 REGION_ID,
337 CONTINENT,
338 COUNTRY,
339 COUNTRY_REGION,
340 STATE,
341 CITY,
342 ALTERNATE_NAME,
343 COUNTY,
344 POSTAL_CODE_FROM,
345 POSTAL_CODE_TO,
346 CREATED_BY,
347 CREATION_DATE,
348 LAST_UPDATED_BY,
349 LAST_UPDATE_DATE,
350 LAST_UPDATE_LOGIN)
351 VALUES (
352 p_lang_code,
353 l_region_id,
354 p_continent,
355 p_country,
356 p_country_region,
357 p_state,
358 p_city,
359 p_alternate_name,
360 p_county,
361 p_postal_code_from,
362 p_postal_code_to,
363 fnd_global.user_id,
364 sysdate,
365 fnd_global.user_id,
366 sysdate,
367 fnd_global.login_id);
368
369 END IF;
370
371 x_region_id := l_region_id;
372
373 --
374 -- Debug Statements
375 --
376 IF l_debug_on THEN
377 WSH_DEBUG_SV.pop(l_module_name);
378 END IF;
379 --
380 END Add_Region;
381
382 --
383 -- PUBLIC FUNCTIONS/PROCEDURES
384 --
385
386 --
387 -- Procedure: Get_Parent_Region_Info
388 --
389 -- Purpose: Retrieves the region Id of the region passed in, and if it
390 -- does not exist, inserts into the database.
391 --
392 --
393
394 PROCEDURE Get_Parent_Region_Info(
395 p_parent_region_type IN NUMBER,
396 p_country_code IN VARCHAR2,
397 p_country_region_code IN VARCHAR2,
398 p_state_code IN VARCHAR2,
399 p_city_code IN VARCHAR2,
400 p_country IN VARCHAR2,
401 p_country_region IN VARCHAR2,
402 p_state IN VARCHAR2,
403 p_city IN VARCHAR2,
404 p_lang_code IN VARCHAR2,
405 p_interface_flag IN VARCHAR2,
406 p_user_id IN NUMBER,
407 p_insert_parent_flag IN VARCHAR2,
408 x_parent_region_info OUT NOCOPY wsh_regions_search_pkg.region_rec,
409 p_conc_request_flag IN VARCHAR2 DEFAULT 'N')
410 IS
411 l_parent_region_id_non_tl number;
412 l_parent_region_id_tl number;
413 l_check_tl_id number;
414 l_existing_parent_region_id number;
415 l_tl_only_flag varchar2(1);
416 l_status varchar2(1);
417 l_error_msg varchar2(200);
418 l_region_info wsh_regions_search_pkg.region_rec;
419 l_return_status varchar2(1);
420 --
421 l_debug_on BOOLEAN;
422 --
423 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_PARENT_REGION_INFO';
424 --
425 BEGIN
426
427 -- fnd_file.put_line(fnd_file.Log, 'in Get_Parent_Region_Id '||p_parent_region_type);
428
429 --
430 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
431 --
432 IF l_debug_on IS NULL THEN
433 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
434 END IF;
435 --
436 --
437 -- Debug Statements
438 --
439 IF l_debug_on THEN
440 WSH_DEBUG_SV.push(l_module_name);
441 --
442 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_REGION_TYPE',P_PARENT_REGION_TYPE);
443 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
444 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
445 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
446 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
447 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
448 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
449 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
450 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
451 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
452 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
453 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
454 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_PARENT_FLAG',P_INSERT_PARENT_FLAG);
455 WSH_DEBUG_SV.log(l_module_name, 'P_CONC_REQUEST_FLAG', P_CONC_REQUEST_FLAG);
456 END IF;
457 --
458 l_tl_only_flag := 'N';
459 l_existing_parent_region_id := -1;
460
461 IF ( p_conc_request_flag = 'Y' ) THEN
462 --
463 -- Debug Statements
464 --
465 IF l_debug_on THEN
466 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.CHECK_REGION_INFO_CODE', WSH_DEBUG_SV.C_PROC_LEVEL);
467 END IF;
468 --
469 WSH_REGIONS_SEARCH_PKG.Check_Region_Info_Code(
470 p_country => p_country,
471 p_state => p_state,
472 p_city => p_city,
473 p_country_code => p_country_code,
474 p_state_code => p_state_code,
475 p_city_code => p_city_code,
476 p_region_type => p_parent_region_type,
477 p_search_flag => 'N',
478 p_lang_code => p_lang_code,
479 x_return_status => l_return_status,
480 x_region_info => l_region_info);
481
482 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
483 fnd_file.put_line(fnd_file.log,'Error Occured in WSH_REGIONS_SEARCH_PKG.Check_Region_Info_Code');
484 fnd_file.put_line(fnd_file.log,'Error Message : '|| fnd_message.get_string('WSH', 'WSH_UTIL_MESSAGE_U') );
485 l_region_info.region_id := -1;
486 return;
487 END IF;
488 ELSE
489 --
490 -- Debug Statements
491 --
492 IF l_debug_on THEN
493 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
494 END IF;
495 --
496 Wsh_Regions_Search_Pkg.Get_Region_Info(
497 p_country => p_country,
498 p_country_region => p_country_region,
499 p_state => p_state,
500 p_city => p_city,
501 p_postal_code_from => null,
502 p_postal_code_to => null,
503 p_zone => null,
504 p_lang_code => p_lang_code,
505 p_country_code => p_country_code,
506 p_country_region_code => p_country_region_code,
507 p_state_code => p_state_code,
508 p_city_code => p_city_code,
509 p_region_type => p_parent_region_type,
510 p_interface_flag => p_interface_flag,
511 x_region_info => l_region_info);
512 END IF;
513
514 -- Debug Statements
515 --
516 IF l_debug_on THEN
517 WSH_DEBUG_SV.logmsg(l_module_name, ' got parent id '||l_region_info.region_id);
518 END IF;
519
520 l_parent_region_id_non_tl := l_region_info.region_id;
521
522 l_parent_region_id_tl := l_parent_region_id_non_tl;
523
524 IF (l_parent_region_id_non_tl = -1) THEN
525
526 IF ((p_city_code IS NULL OR p_city IS NOT NULL) AND
527 (p_state_code IS NULL OR p_state IS NOT NULL) AND
528 (p_country_code IS NULL OR p_country IS NOT NULL)) THEN
529
530 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
531 fnd_file.put_line(fnd_file.log,'1. Error Occured in WSH_REGIONS_SEARCH_PKG.Check_Region_Id_Codes_Only');
532 fnd_file.put_line(fnd_file.log,'1. Error Message : '|| fnd_message.get_string('WSH', 'WSH_UTIL_MESSAGE_U') );
533 l_region_info.region_id := -1;
534 return;
535 END IF;
536
537 IF (l_parent_region_id_non_tl <> -1) THEN
538 --
539 -- Debug Statements
540 --
541 IF l_debug_on THEN
542 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.CHECK_REGION_ID_CODES_ONLY WITH LANGUAGE', WSH_DEBUG_SV.C_PROC_LEVEL);
543 END IF;
544 --
545 WSH_REGIONS_SEARCH_PKG.Check_Region_Id_Codes_Only(
546 p_country_code => p_country_code,
547 p_state_code => p_state_code,
548 p_city_code => p_city_code,
549 p_postal_code_from => null,
550 p_postal_code_to => null,
551 p_region_type => p_parent_region_type,
552 p_language_code => p_lang_code,
553 x_return_status => l_return_status,
554 x_region_id => l_check_tl_id);
555
556 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS ) THEN
557 fnd_file.put_line(fnd_file.log,'2. Error Occured in WSH_REGIONS_SEARCH_PKG.Check_Region_Id_Codes_Only');
558 fnd_file.put_line(fnd_file.log,'2. Error Message : '|| fnd_message.get_string('WSH', 'WSH_UTIL_MESSAGE_U') );
559 l_region_info.region_id := -1;
560 return;
561 END IF;
562 END IF;
563 ELSE
564 --
565 -- Debug Statements
566 --
567 IF l_debug_on THEN
568 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_ID_CODES_ONLY',WSH_DEBUG_SV.C_PROC_LEVEL);
569 END IF;
570 --
571 Wsh_Regions_Search_Pkg.Get_Region_Id_Codes_Only(
572 p_country_code => p_country_code,
573 p_country_region_code => p_country_region_code,
574 p_state_code => p_state_code,
575 p_city_code => p_city_code,
576 p_postal_code_from => null,
577 p_postal_code_to => null,
578 p_region_type => p_parent_region_type,
579 p_interface_flag => p_interface_flag,
580 p_lang_code => p_lang_code,
581 x_region_id_non_tl => l_parent_region_id_non_tl,
582 x_region_id_with_tl => l_check_tl_id);
583 END IF;
584
585 l_region_info.country_code := p_country_code;
586 l_region_info.country_region_code := p_country_region_code;
587 l_region_info.state_code := p_state_code;
588 l_region_info.city_code := p_city_code;
589
590 END IF;
591
592 IF (l_parent_region_id_non_tl <> -1 AND l_check_tl_id <> -1) THEN
593 -- fnd_file.put_line(fnd_file.log,'parent region exists in tl table. cannot insert new one '||l_parent_region_id_non_tl||' x '||l_check_tl_id);
594 x_parent_region_info.region_id := -1;
595 --
596 -- Debug Statements
597 --
598 IF l_debug_on THEN
599 WSH_DEBUG_SV.pop(l_module_name);
600 END IF;
601 --
602 return;
603 END IF;
604
605 IF (l_parent_region_id_non_tl <> -1) THEN
606
607 -- Debug Statements
608 --
609 IF l_debug_on THEN
610 WSH_DEBUG_SV.logmsg(l_module_name, 'region exists in tl table');
611 END IF;
612
613 l_tl_only_flag := 'Y';
614 l_existing_parent_region_id := l_parent_region_id_non_tl;
615 END IF;
616
617
618 -- Debug Statements
619 --
620 IF l_debug_on THEN
621 WSH_DEBUG_SV.logmsg(l_module_name, 'in Get_Parent_Region_Id_tl id = ' || l_parent_region_id_tl);
622 END IF;
623
624 IF (l_parent_region_id_non_tl <> -1) THEN
625
626 l_tl_only_flag := 'Y';
627 l_existing_parent_region_id := l_parent_region_id_non_tl;
628
629 END IF;
630
631 IF (l_parent_region_id_tl = -1 AND p_interface_flag <> 'Y' AND p_insert_parent_flag = 'Y') THEN
632
633
634 -- Debug Statements
635 --
636 IF l_debug_on THEN
637 WSH_DEBUG_SV.logmsg(l_module_name, 'inserting parent');
638 END IF;
639
640 -- insert this parent region
641 INSERT_REGION(
642 p_country_code => p_country_code,
643 p_country_region_code => p_country_region_code,
644 p_state_code => p_state_code,
645 p_city_code => p_city_code,
646 p_port_flag => null,
647 p_airport_flag => null,
648 p_road_terminal_flag => null,
649 p_rail_terminal_flag => null,
650 p_longitude => null,
651 p_latitude => null,
652 p_timezone => null,
653 p_continent => null,
654 p_country => p_country,
655 p_country_region => p_country_region,
656 p_state => p_state,
657 p_city => p_city,
658 p_alternate_name => null,
659 p_county => null,
660 p_postal_code_from => null,
661 p_postal_code_to => null,
662 p_lang_code => p_lang_code,
663 p_interface_flag => p_interface_flag,
664 p_tl_only_flag => l_tl_only_flag,
665 p_region_id => l_existing_parent_region_id,
666 p_parent_region_id => null,
667 p_user_id => p_user_id,
668 p_insert_parent_flag => p_insert_parent_flag,
669 x_region_id => l_parent_region_id_non_tl,
670 x_status => l_status,
671 x_error_msg => l_error_msg,
672 p_conc_request_flag => p_conc_request_flag);
673
674 -- Debug Statements
675 --
676 IF l_debug_on THEN
677 WSH_DEBUG_SV.logmsg(l_module_name, 'inserted parent id: '||l_parent_region_id_non_tl);
678 END IF;
679
680 END IF;
681
682 l_region_info.region_id := l_parent_region_id_non_tl;
683 x_parent_region_info := l_region_info;
684
685 --
686 -- Debug Statements
687 --
688 IF l_debug_on THEN
689 WSH_DEBUG_SV.pop(l_module_name);
690 END IF;
691 --
692 END Get_Parent_Region_Info;
693
694 --
695 -- Procedure: Insert_Region
696 --
697 -- Purpose: Inserts the region with appropriate data, and recursively inserts
698 -- the parent region if it doesn't exist thru Get_Parent_Region_Info
699 --
700 -- Parameter P_CONC_REQUEST_FLAG added for "Regions Interface"
701 -- performance issue.
702
703 PROCEDURE Insert_Region (
704 p_country_code IN VARCHAR2,
705 p_country_region_code IN VARCHAR2,
706 p_state_code IN VARCHAR2,
707 p_city_code IN VARCHAR2,
708 p_port_flag IN VARCHAR2,
709 p_airport_flag IN VARCHAR2,
710 p_road_terminal_flag IN VARCHAR2,
711 p_rail_terminal_flag IN VARCHAR2,
712 p_longitude IN NUMBER,
713 p_latitude IN NUMBER,
714 p_timezone IN VARCHAR2,
715 p_continent IN VARCHAR2,
716 p_country IN VARCHAR2,
717 p_country_region IN VARCHAR2,
718 p_state IN VARCHAR2,
719 p_city IN VARCHAR2,
720 p_alternate_name IN VARCHAR2,
721 p_county IN VARCHAR2,
722 p_postal_code_from IN VARCHAR2,
723 p_postal_code_to IN VARCHAR2,
724 p_lang_code IN VARCHAR2,
725 p_interface_flag IN VARCHAR2,
726 p_tl_only_flag IN VARCHAR2,
727 p_region_id IN NUMBER,
728 p_parent_region_id IN NUMBER,
729 p_user_id IN NUMBER,
730 p_insert_parent_flag IN VARCHAR2,
731 p_region_dff IN REGION_DFF_REC DEFAULT NULL,
732 x_region_id OUT NOCOPY NUMBER,
733 x_status OUT NOCOPY NUMBER,
734 x_error_msg OUT NOCOPY VARCHAR2,
735 p_deconsol_location_id IN NUMBER DEFAULT NULL,
736 p_conc_request_flag IN VARCHAR2 DEFAULT 'N')
737 IS
738 l_region_type NUMBER := 0;
739 l_parent_offset NUMBER := 1;
740 l_parent_region_type NUMBER := 0;
741 l_parent_region_info wsh_regions_search_pkg.region_rec;
742 l_region_id NUMBER;
743 l_return_status VARCHAR2(1);
744
745 --
746 l_debug_on BOOLEAN;
747 --
748 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_REGION';
749 --
750 BEGIN
751
752 --
753 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
754 --
755 IF l_debug_on IS NULL
756 THEN
757 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
758 END IF;
759 --
760 --
761 -- Debug Statements
762 --
763 IF l_debug_on THEN
764 WSH_DEBUG_SV.push(l_module_name);
765 --
766 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
767 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
768 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
769 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
770 WSH_DEBUG_SV.log(l_module_name,'P_PORT_FLAG',P_PORT_FLAG);
771 WSH_DEBUG_SV.log(l_module_name,'P_AIRPORT_FLAG',P_AIRPORT_FLAG);
772 WSH_DEBUG_SV.log(l_module_name,'P_ROAD_TERMINAL_FLAG',P_ROAD_TERMINAL_FLAG);
773 WSH_DEBUG_SV.log(l_module_name,'P_RAIL_TERMINAL_FLAG',P_RAIL_TERMINAL_FLAG);
774 WSH_DEBUG_SV.log(l_module_name,'P_LONGITUDE',P_LONGITUDE);
775 WSH_DEBUG_SV.log(l_module_name,'P_LATITUDE',P_LATITUDE);
776 WSH_DEBUG_SV.log(l_module_name,'P_TIMEZONE',P_TIMEZONE);
777 WSH_DEBUG_SV.log(l_module_name,'P_CONTINENT',P_CONTINENT);
778 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
779 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
780 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
781 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
782 WSH_DEBUG_SV.log(l_module_name,'P_ALTERNATE_NAME',P_ALTERNATE_NAME);
783 WSH_DEBUG_SV.log(l_module_name,'P_COUNTY',P_COUNTY);
784 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
785 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
786 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
787 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
788 WSH_DEBUG_SV.log(l_module_name,'P_TL_ONLY_FLAG',P_TL_ONLY_FLAG);
789 WSH_DEBUG_SV.log(l_module_name,'P_REGION_ID',P_REGION_ID);
790 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_REGION_ID',P_PARENT_REGION_ID);
791 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
792 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_PARENT_FLAG',P_INSERT_PARENT_FLAG);
793 WSH_DEBUG_SV.log(l_module_name, 'P_CONC_REQUEST_FLAG', P_CONC_REQUEST_FLAG);
794 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE_CATEGORY',P_REGION_DFF.ATTRIBUTE_CATEGORY);
795 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE1',P_REGION_DFF.ATTRIBUTE1);
796 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE2',P_REGION_DFF.ATTRIBUTE2);
797 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE3',P_REGION_DFF.ATTRIBUTE3);
798 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE4',P_REGION_DFF.ATTRIBUTE4);
799 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE5',P_REGION_DFF.ATTRIBUTE5);
800 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE6',P_REGION_DFF.ATTRIBUTE6);
801 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE7',P_REGION_DFF.ATTRIBUTE7);
802 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE8',P_REGION_DFF.ATTRIBUTE8);
803 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE9',P_REGION_DFF.ATTRIBUTE9);
804 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE10',P_REGION_DFF.ATTRIBUTE10);
805 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE11',P_REGION_DFF.ATTRIBUTE11);
806 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE12',P_REGION_DFF.ATTRIBUTE12);
807 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE13',P_REGION_DFF.ATTRIBUTE13);
808 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE14',P_REGION_DFF.ATTRIBUTE14);
809 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE15',P_REGION_DFF.ATTRIBUTE15);
810 WSH_DEBUG_SV.log(l_module_name, 'P_DECONSOL_LOCATION_ID', P_DECONSOL_LOCATION_ID);
811 END IF;
812 --
813 l_parent_region_info.region_id := -1;
814 x_status := 0;
815 x_region_id := -1;
816 x_error_msg := NULL;
817
818 -- figure out region type
819 IF (p_postal_code_from IS NOT NULL) THEN
820 l_region_type := 3;
821
822 ELSIF (p_city_code IS NOT NULL OR p_city IS NOT NULL) THEN
823 l_region_type := 2;
824
825 ELSIF (p_state_code IS NOT NULL OR p_state IS NOT NULL) THEN
826 l_region_type := 1;
827
828 END IF;
829
830 -- figure out the parent region offset
831 IF (l_region_type > 0) THEN
832
833 IF (l_region_type = 2 AND
834 p_state IS NULL AND
835 p_state_code IS NULL) THEN
836 l_parent_offset := l_parent_offset + 1;
837
838 ELSE
839 IF (l_region_type = 3 AND
840 p_city IS NULL AND
841 p_city_code IS NULL) THEN
842 l_parent_offset := l_parent_offset + 1;
843
844 IF (p_state IS NULL AND
845 p_state_code IS NULL) THEN
846 l_parent_offset := l_parent_offset + 1;
847 END IF;
848
849 END IF;
850
851 END IF;
852
853 END IF;
854
855 IF (p_parent_region_id IS NULL OR p_parent_region_id = -1)
856 THEN
857 IF (l_region_type > 0)
858 THEN
859 BEGIN -- { Begin
860 l_parent_region_type := l_region_type - l_parent_offset;
861
862 -- Debug Statements
863 --
864 IF l_debug_on THEN
865 WSH_DEBUG_SV.logmsg(l_module_name, 'parent region type '||l_parent_region_type);
866 END IF;
867
868 IF (l_parent_region_type = 2) THEN
869
870 -- Debug Statements
871 --
872 IF l_debug_on THEN
873 WSH_DEBUG_SV.logmsg(l_module_name, 'getting parent for postal code');
874 END IF;
875
876 Get_Parent_Region_Info(
877 p_parent_region_type => l_parent_region_type,
878 p_country_code => p_country_code,
879 p_country_region_code => p_country_region_code,
880 p_state_code => p_state_code,
881 p_city_code => p_city_code,
882 p_country => p_country,
883 p_country_region => p_country_region,
884 p_state => p_state,
885 p_city => p_city,
886 p_lang_code => p_lang_code,
887 p_interface_flag => p_interface_flag,
888 p_user_id => p_user_id,
889 p_insert_parent_flag => p_insert_parent_flag,
890 x_parent_region_info => l_parent_region_info,
891 p_conc_request_flag => p_conc_request_flag);
892 ELSIF (l_parent_region_type = 1) THEN
893
894 -- Debug Statements
895 --
896 IF l_debug_on THEN
897 WSH_DEBUG_SV.logmsg(l_module_name, 'getting parent for city');
898 END IF;
899
900
901 Get_Parent_Region_Info(
902 p_parent_region_type => l_parent_region_type,
903 p_country_code => p_country_code,
904 p_country_region_code => p_country_region_code,
905 p_state_code => p_state_code,
906 p_city_code => null,
907 p_country => p_country,
908 p_country_region => p_country_region,
909 p_state => p_state,
910 p_city => null,
911 p_lang_code => p_lang_code,
912 p_interface_flag => p_interface_flag,
913 p_user_id => p_user_id,
914 p_insert_parent_flag => p_insert_parent_flag,
915 x_parent_region_info => l_parent_region_info,
916 p_conc_request_flag => p_conc_request_flag);
917 ELSIF (l_parent_region_type = 0) THEN
918
919 -- Debug Statements
920 --
921 IF l_debug_on THEN
922 WSH_DEBUG_SV.logmsg(l_module_name, 'getting parent for state');
923 END IF;
924
925 Get_Parent_Region_Info(
926 p_parent_region_type => l_parent_region_type,
927 p_country_code => p_country_code,
928 p_country_region_code => null,
929 p_state_code => null,
930 p_city_code => null,
931 p_country => p_country,
932 p_country_region => null,
933 p_state => null,
934 p_city => null,
935 p_lang_code => p_lang_code,
936 p_interface_flag => p_interface_flag,
937 p_user_id => p_user_id,
938 p_insert_parent_flag => p_insert_parent_flag,
939 x_parent_region_info => l_parent_region_info,
940 p_conc_request_flag => p_conc_request_flag);
941 END IF;
942
943 END;
944
945 END IF;
946
947
948 --make sure country code and name is populated
949 IF (p_country_code IS NULL AND l_region_type = 0) THEN
950
951 x_status := 2;
952 x_error_msg := 'WSH_CAT_COUNTRY_CODE_REQUIRED';
953 --
954 -- Debug Statements
955 --
956 IF l_debug_on THEN
957 WSH_DEBUG_SV.pop(l_module_name);
958 END IF;
959 --
960 return;
961
962 END IF;
963
964 IF ((p_country IS NULL AND l_region_type = 0) OR (p_country IS NULL AND p_country_code IS NULL)) THEN
965
966 x_status := 2;
967 x_error_msg := 'WSH_CAT_COUNTRY_REQUIRED';
968 --
969 -- Debug Statements
970 --
971 IF l_debug_on THEN
972 WSH_DEBUG_SV.pop(l_module_name);
973 END IF;
974 --
975 return;
976
977 END IF;
978
979 ELSE
980 l_parent_region_info.region_id := p_parent_region_id;
981
982 END IF;
983
984 -- Debug Statements
985 --
986 IF l_debug_on THEN
987 WSH_DEBUG_SV.logmsg(l_module_name, 'add region with codes '||p_country_code||', '||p_state_code||', '||p_city_code);
988 WSH_DEBUG_SV.logmsg(l_module_name, 'and name '||p_country||', '||p_state||', '||p_city||', '||p_postal_code_from);
989 WSH_DEBUG_SV.logmsg(l_module_name, 'and type '||l_region_type||', parent ID '||l_parent_region_info.region_id);
990 END IF;
991
992 IF ((l_parent_region_info.region_id <> -1 OR l_region_type = 0)) OR p_interface_flag = 'Y'
993 THEN
994
995 Add_Region (
996 p_country_code => nvl(l_parent_region_info.country_code,p_country_code),
997 p_country_region_code => p_country_region_code,
998 p_state_code => p_state_code,
999 p_city_code => p_city_code,
1000 p_port_flag => p_port_flag,
1001 p_airport_flag => p_airport_flag,
1002 p_road_terminal_flag => p_road_terminal_flag,
1003 p_rail_terminal_flag => p_rail_terminal_flag,
1004 p_longitude => p_longitude,
1005 p_latitude => p_latitude,
1006 p_timezone => p_timezone,
1007 p_continent => p_continent,
1008 p_country => p_country,
1009 p_country_region => p_country_region,
1010 p_state => p_state,
1011 p_city => p_city,
1012 p_alternate_name => p_alternate_name,
1013 p_county => p_county,
1014 p_postal_code_from => p_postal_code_from,
1015 p_postal_code_to => p_postal_code_to,
1016 p_lang_code => p_lang_code,
1017 p_region_type => l_region_type,
1018 p_parent_region_id => l_parent_region_info.region_id,
1019 p_interface_flag => p_interface_flag,
1020 p_tl_only_flag => p_tl_only_flag,
1021 p_region_id => p_region_id,
1022 p_region_dff => p_region_dff,
1023 x_region_id => l_region_id,
1024 p_deconsol_location_id => p_deconsol_location_id);
1025
1026 -- To insert the same in Global Temp Tables
1027 IF ( p_conc_request_flag = 'Y' )
1028 THEN
1029 --
1030 -- Debug Statements
1031 --
1032 IF l_debug_on THEN
1033 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.INSERT_GLOBAL_TABLE', WSH_DEBUG_SV.C_PROC_LEVEL);
1034 END IF;
1035 --
1036 Insert_Global_Table(
1037 p_country => p_country,
1038 p_state => p_state,
1039 p_city => p_city,
1040 p_country_code => p_country_code,
1041 p_state_code => p_state_code,
1042 p_city_code => p_city_code,
1043 p_region_id => l_region_id,
1044 p_region_type => l_region_type,
1045 p_parent_region_id => l_parent_region_info.region_id,
1046 p_postal_code_from => p_postal_code_from,
1047 p_postal_code_to => p_postal_code_to,
1048 p_tl_only_flag => p_tl_only_flag,
1049 p_lang_code => p_lang_code,
1050 x_return_status => l_return_status );
1051
1052 -- Error Handling Part
1053 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1054 THEN
1055 x_status := 2;
1056 x_error_msg := 'WSH_UTIL_MESSAGE_U';
1057 END IF;
1058 END IF;
1059 ELSE
1060 if nvl(p_interface_flag, 'N') = 'N' then
1061 x_status := 2;
1062 x_error_msg := 'WSH_CAT_PARENT_NOT_FOUND';
1063 end if;
1064
1065 END IF;
1066
1067 x_region_id := l_region_id;
1068
1069 --
1070 -- Debug Statements
1071 --
1072 IF l_debug_on THEN
1073 WSH_DEBUG_SV.pop(l_module_name);
1074 END IF;
1075 --
1076 END Insert_Region;
1077
1078 --
1079 -- Procedure: Update_Region
1080 --
1081 -- Purpose: Updates a region with new information if the region exists,
1082 -- otherwise calls Insert_Region to insert the region.
1083 --
1084
1085 PROCEDURE Update_Region (
1086 p_insert_type IN VARCHAR2,
1087 p_region_id IN NUMBER,
1088 p_parent_region_id IN NUMBER,
1089 p_continent IN VARCHAR2,
1090 p_country IN VARCHAR2,
1091 p_country_region IN VARCHAR2,
1092 p_state IN VARCHAR2,
1093 p_city IN VARCHAR2,
1094 p_alternate_name IN VARCHAR2,
1095 p_county IN VARCHAR2,
1096 p_postal_code_from IN VARCHAR2,
1097 p_postal_code_to IN VARCHAR2,
1098 p_lang_code IN VARCHAR2,
1099 p_country_code IN VARCHAR2,
1100 p_country_region_code IN VARCHAR2,
1101 p_state_code IN VARCHAR2,
1102 p_city_code IN VARCHAR2,
1103 p_port_flag IN VARCHAR2,
1104 p_airport_flag IN VARCHAR2,
1105 p_road_terminal_flag IN VARCHAR2,
1106 p_rail_terminal_flag IN VARCHAR2,
1107 p_longitude IN NUMBER,
1108 p_latitude IN NUMBER,
1109 p_timezone IN VARCHAR2,
1110 p_interface_flag IN VARCHAR2,
1111 p_user_id IN NUMBER,
1112 p_insert_parent_flag IN VARCHAR2,
1113 p_region_dff IN REGION_DFF_REC DEFAULT NULL,
1114 x_region_id OUT NOCOPY NUMBER,
1115 x_status OUT NOCOPY NUMBER,
1116 x_error_msg OUT NOCOPY VARCHAR2,
1117 p_deconsol_location_id IN NUMBER DEFAULT NULL,
1118 p_conc_request_flag IN VARCHAR2 DEFAULT 'N')
1119 IS
1120 CURSOR child_regions(l_region_id NUMBER) IS
1121 SELECT region_id, zone_level, parent_region_id
1122 FROM wsh_regions
1123 START WITH region_id = l_region_id
1124 CONNECT BY PRIOR region_id = parent_region_id;
1125 -- WHERE region_id = l_region_id
1126 -- FOR UPDATE NOWAIT;
1127
1128 CURSOR get_state_code(l_region_id NUMBER) IS
1129 SELECT state_code, city_code
1130 FROM wsh_regions
1131 WHERE region_id =l_region_id;
1132
1133 CURSOR get_zone_level(l_region_id NUMBER) IS
1134 SELECT zone_level
1135 FROM wsh_regions
1136 WHERE region_id = l_region_id;
1137
1138 l_region_id number;
1139 l_region_type number;
1140 l_check_tl_id number;
1141 l_existing_region_id number;
1142 l_tl_only_flag varchar2(1);
1143 l_status number;
1144 l_error_msg varchar2(200);
1145 l_region_info wsh_regions_search_pkg.region_rec;
1146 l_parent_zone_level number;
1147 l_update_state_code wsh_regions.state_code%type;
1148 l_update_city_code wsh_regions.city_code%type;
1149 l_return_status VARCHAR2(1);
1150
1151 --
1152 l_debug_on BOOLEAN;
1153 --
1154 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_REGION';
1155 --
1156
1157 BEGIN
1158
1159 --
1160 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1161 --
1162 IF l_debug_on IS NULL
1163 THEN
1164 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1165 END IF;
1166 --
1167 --
1168 -- Debug Statements
1169 --
1170 IF l_debug_on THEN
1171 WSH_DEBUG_SV.push(l_module_name);
1172 --
1173 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
1174 WSH_DEBUG_SV.log(l_module_name,'P_REGION_ID',P_REGION_ID);
1175 WSH_DEBUG_SV.log(l_module_name,'P_PARENT_REGION_ID',P_PARENT_REGION_ID);
1176 WSH_DEBUG_SV.log(l_module_name,'P_CONTINENT',P_CONTINENT);
1177 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
1178 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
1179 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
1180 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
1181 WSH_DEBUG_SV.log(l_module_name,'P_ALTERNATE_NAME',P_ALTERNATE_NAME);
1182 WSH_DEBUG_SV.log(l_module_name,'P_COUNTY',P_COUNTY);
1183 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
1184 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
1185 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
1186 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
1187 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
1188 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
1189 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
1190 WSH_DEBUG_SV.log(l_module_name,'P_PORT_FLAG',P_PORT_FLAG);
1191 WSH_DEBUG_SV.log(l_module_name,'P_AIRPORT_FLAG',P_AIRPORT_FLAG);
1192 WSH_DEBUG_SV.log(l_module_name,'P_ROAD_TERMINAL_FLAG',P_ROAD_TERMINAL_FLAG);
1193 WSH_DEBUG_SV.log(l_module_name,'P_RAIL_TERMINAL_FLAG',P_RAIL_TERMINAL_FLAG);
1194 WSH_DEBUG_SV.log(l_module_name,'P_LONGITUDE',P_LONGITUDE);
1195 WSH_DEBUG_SV.log(l_module_name,'P_LATITUDE',P_LATITUDE);
1196 WSH_DEBUG_SV.log(l_module_name,'P_TIMEZONE',P_TIMEZONE);
1197 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
1198 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
1199 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_PARENT_FLAG',P_INSERT_PARENT_FLAG);
1200 WSH_DEBUG_SV.log(l_module_name, 'P_CONC_REQUEST_FLAG', P_CONC_REQUEST_FLAG);
1201 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE_CATEGORY',P_REGION_DFF.ATTRIBUTE_CATEGORY);
1202 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE1',P_REGION_DFF.ATTRIBUTE1);
1203 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE2',P_REGION_DFF.ATTRIBUTE2);
1204 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE3',P_REGION_DFF.ATTRIBUTE3);
1205 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE4',P_REGION_DFF.ATTRIBUTE4);
1206 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE5',P_REGION_DFF.ATTRIBUTE5);
1207 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE6',P_REGION_DFF.ATTRIBUTE6);
1208 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE7',P_REGION_DFF.ATTRIBUTE7);
1209 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE8',P_REGION_DFF.ATTRIBUTE8);
1210 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE9',P_REGION_DFF.ATTRIBUTE9);
1211 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE10',P_REGION_DFF.ATTRIBUTE10);
1212 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE11',P_REGION_DFF.ATTRIBUTE11);
1213 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE12',P_REGION_DFF.ATTRIBUTE12);
1214 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE13',P_REGION_DFF.ATTRIBUTE13);
1215 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE14',P_REGION_DFF.ATTRIBUTE14);
1216 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE15',P_REGION_DFF.ATTRIBUTE15);
1217 WSH_DEBUG_SV.log(l_module_name, 'P_DECONSOL_LOCATION_ID', P_DECONSOL_LOCATION_ID);
1218
1219 END IF;
1220 --
1221 l_tl_only_flag := 'N';
1222 l_status := 0;
1223 l_error_msg := NULL;
1224 l_existing_region_id := -1;
1225
1226 IF (p_postal_code_from IS NOT NULL OR p_postal_code_to IS NOT NULL) THEN
1227 l_region_type := 3;
1228 ELSIF (p_city IS NOT NULL OR p_city_code IS NOT NULL) THEN
1229 l_region_type := 2;
1230 ELSIF (p_state IS NOT NULL OR p_state_code IS NOT NULL) THEN
1231 l_region_type := 1;
1232 ELSE
1233 l_region_type := 0;
1234 END IF;
1235
1236 /*
1237 -- Validation regarding missing parameters or wrong format
1238 -- Same validatin are in the When-Validate_Record trigger on the Region block
1239 -- In WSHRGZON.fmb form
1240 */
1241 -- Same validation is already in API Default_Regions for p_conc_request_flag = 'Y'
1242 IF ( p_conc_request_flag = 'N' )
1243 THEN
1244 --
1245 -- Debug Statements
1246 --
1247 IF l_debug_on THEN
1248 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.VALIDATE_REGION', WSH_DEBUG_SV.C_PROC_LEVEL);
1249 END IF;
1250 --
1251 Validate_Region( p_country => p_country,
1252 p_state => p_state,
1253 p_city => p_city,
1254 p_country_code => p_country_code,
1255 p_state_code => p_state_code,
1256 p_city_code => p_city_code,
1257 p_postal_code_from => p_postal_code_from,
1258 p_postal_code_to => p_postal_code_to,
1259 x_status => l_status,
1260 x_error_msg => l_error_msg );
1261
1262 IF ( l_status = 2 ) THEN
1263 x_status := l_status;
1264 x_error_msg := l_error_msg;
1265 x_region_id := -1;
1266 return;
1267 END IF;
1268 END IF;
1269
1270 -- Bug 3396077 : Validation not done for country code combination in Regions interface program
1271 IF (p_interface_flag<>'Y') THEN
1272 IF ( p_conc_request_flag = 'Y' )
1273 THEN -- { Concurrent Request
1274 --
1275 -- Debug Statements
1276 --
1277 IF l_debug_on THEN
1278 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.CHECK_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
1279 END IF;
1280 --
1281 WSH_REGIONS_SEARCH_PKG.Check_Region_Info(
1282 p_country => p_country,
1283 p_state => null,
1284 p_city => null,
1285 p_postal_code_from => null,
1286 p_postal_code_to => null,
1287 p_region_type => 0,
1288 p_search_flag => 'N',
1289 p_lang_code => p_lang_code,
1290 x_return_status => l_return_status,
1291 x_region_info => l_region_info);
1292
1293 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1294 THEN
1295 x_status := 2;
1296 x_error_msg := 'WSH_UTIL_MESSAGE_U';
1297 x_region_id := -1;
1298 END IF;
1299
1300 --
1301 -- Debug Statements
1302 --
1303 IF l_debug_on THEN
1304 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.CHECK_REGION_ID_CODES_ONLY', WSH_DEBUG_SV.C_PROC_LEVEL);
1305 END IF;
1306 --
1307 WSH_REGIONS_SEARCH_PKG.Check_Region_Id_Codes_Only(
1308 p_country_code => p_country_code,
1309 p_state_code => null,
1310 p_city_code => null,
1311 p_postal_code_from => null,
1312 p_postal_code_to => null,
1313 p_region_type => 0,
1314 p_language_code => null,
1315 x_return_status => l_return_status,
1316 x_region_id => l_region_id);
1317
1318 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1319 THEN
1320 x_status := 2;
1321 x_error_msg := 'WSH_UTIL_MESSAGE_U';
1322 x_region_id := -1;
1323 END IF;
1324
1325 IF (l_region_id <> -1) THEN
1326 --
1327 -- Debug Statements
1328 --
1329 IF l_debug_on THEN
1330 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.CHECK_REGION_ID_CODES_ONLY WITH LANGUAGE', WSH_DEBUG_SV.C_PROC_LEVEL);
1331 END IF;
1332 --
1333 WSH_REGIONS_SEARCH_PKG.Check_Region_Id_Codes_Only(
1334 p_country_code => p_country_code,
1335 p_state_code => null,
1336 p_city_code => null,
1337 p_postal_code_from => null,
1338 p_postal_code_to => null,
1339 p_region_type => 0,
1340 p_language_code => p_lang_code,
1341 x_return_status => l_return_status,
1342 x_region_id => l_check_tl_id);
1343
1344 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1345 THEN
1346 x_status := 2;
1347 x_error_msg := 'WSH_UTIL_MESSAGE_U';
1348 x_region_id := -1;
1349
1350 END IF;
1351 ELSE
1352 l_check_tl_id := -1;
1353 END IF;
1354
1355 ELSE
1356 --
1357 -- Debug Statements
1358 --
1359 IF l_debug_on THEN
1360 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
1361 END IF;
1362 --
1363 Wsh_Regions_Search_Pkg.Get_Region_Info(
1364 p_country => p_country,
1365 p_country_region => null,
1366 p_state => null,
1367 p_city => null,
1368 p_postal_code_from => null,
1369 p_postal_code_to => null,
1370 p_zone => null,
1371 p_lang_code => p_lang_code,
1372 p_country_code => null,
1373 p_country_region_code => null,
1374 p_state_code => null,
1375 p_city_code => null,
1376 p_region_type => 0,
1377 p_interface_flag => p_interface_flag,
1378 x_region_info => l_region_info);
1379
1380 --
1381 -- Debug Statements
1382 --
1383 IF l_debug_on THEN
1384 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_ID_CODES_ONLY',WSH_DEBUG_SV.C_PROC_LEVEL);
1385 END IF;
1386 --
1387 Wsh_Regions_Search_Pkg.Get_Region_Id_Codes_Only(
1388 p_country_code => p_country_code,
1389 p_country_region_code => null,
1390 p_state_code => null,
1391 p_city_code => null,
1392 p_postal_code_from => null,
1393 p_postal_code_to => null,
1394 p_region_type => 0,
1395 p_interface_flag => p_interface_flag,
1396 p_lang_code => p_lang_code,
1397 x_region_id_non_tl => l_region_id,
1398 x_region_id_with_tl => l_check_tl_id);
1399 END IF; -- } Concurrent Request
1400
1401 IF (nvl(l_region_id,-1) <> -1 AND nvl(l_check_tl_id,-1) <> -1 AND nvl(l_region_info.region_id,-1) = -1 ) OR
1402 -- Both country and country code are present.
1403 (nvl(l_region_id,-1) =-1 AND nvl(l_check_tl_id,-1) = -1 AND nvl(l_region_info.region_id,-1) <> -1 )
1404 --Both country and country code are not present.
1405 THEN
1406 x_status := 2;
1407 x_error_msg := 'WSH_CAT_REGION_EXISTS';
1408 x_region_id := -1;
1409 --
1410 -- Debug Statements
1411 --
1412 IF l_debug_on THEN
1413 WSH_DEBUG_SV.pop(l_module_name);
1414 END IF;
1415 return;
1416 END IF;
1417 END IF;
1418
1419 --End of Fix for Bug 3396077
1420
1421 IF (p_insert_type IN ('ADD','INSERT')) THEN
1422
1423 --
1424 --BUG NUMBER : 3222165
1425 --Unique region validation not done for data entered using Regions Interface Form.
1426 --
1427
1428 IF (p_interface_flag<>'Y') THEN
1429
1430 --
1431 -- Debug Statements
1432 --
1433 IF l_debug_on THEN
1434 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
1435 END IF;
1436 --
1437 Wsh_Regions_Search_Pkg.Get_Region_Info(
1438 p_country => p_country,
1439 p_country_region => p_country_region,
1440 p_state => p_state,
1441 p_city => p_city,
1442 p_postal_code_from => p_postal_code_from,
1443 p_postal_code_to => p_postal_code_to,
1444 p_zone => null,
1445 p_lang_code => p_lang_code,
1446 p_country_code => null,
1447 p_country_region_code => null,
1448 p_state_code => null,
1449 p_city_code => null,
1450 p_region_type => l_region_type,
1451 p_interface_flag => p_interface_flag,
1452 x_region_info => l_region_info);
1453
1454
1455 l_region_id := l_region_info.region_id;
1456
1457 IF (l_region_id = -1) THEN
1458
1459 IF ((p_city_code IS NULL OR p_city IS NOT NULL) AND
1460 (p_state_code IS NULL OR p_state IS NOT NULL) AND
1461 (p_country_code IS NULL OR p_country IS NOT NULL)) THEN
1462
1463 --
1464 -- Debug Statements
1465 --
1466 IF l_debug_on THEN
1467 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_ID_CODES_ONLY',WSH_DEBUG_SV.C_PROC_LEVEL);
1468 END IF;
1469 --
1470 Wsh_Regions_Search_Pkg.Get_Region_Id_Codes_Only(
1471 p_country_code => p_country_code,
1472 p_country_region_code => p_country_region_code,
1473 p_state_code => p_state_code,
1474 p_city_code => p_city_code,
1475 p_postal_code_from => p_postal_code_from,
1476 p_postal_code_to => p_postal_code_to,
1477 p_region_type => l_region_type,
1478 p_interface_flag => p_interface_flag,
1479 p_lang_code => p_lang_code,
1480 x_region_id_non_tl => l_region_id,
1481 x_region_id_with_tl => l_check_tl_id);
1482
1483 -- fnd_file.put_line(fnd_file.log,'after region id codes 1 '||l_region_id||' x '||l_check_tl_id);
1484 IF (l_region_id <> -1 AND l_check_tl_id <> -1) THEN
1485 x_status := 2;
1486 x_error_msg := 'WSH_CAT_REGION_EXISTS';
1487 x_region_id := -1;
1488 --
1489 -- Debug Statements
1490 --
1491 IF l_debug_on THEN
1492 WSH_DEBUG_SV.pop(l_module_name);
1493 END IF;
1494 --
1495 return;
1496 END IF;
1497
1498 END IF;
1499
1500 IF (l_region_id <> -1) THEN
1501
1502 -- Debug Statements
1503 --
1504 IF l_debug_on THEN
1505 WSH_DEBUG_SV.logmsg(l_module_name, 'region exists in tl table');
1506 END IF;
1507
1508 l_tl_only_flag := 'Y';
1509 l_existing_region_id := l_region_id;
1510 END IF;
1511
1512 ELSE
1513 x_status := 2;
1514 x_error_msg := 'WSH_CAT_REGION_EXISTS';
1515 x_region_id := -1;
1516 --
1517 -- Debug Statements
1518 --
1519 IF l_debug_on THEN
1520 WSH_DEBUG_SV.pop(l_module_name);
1521 END IF;
1522 --
1523 return;
1524
1525 END IF;
1526
1527 END IF;
1528
1529 INSERT_REGION(
1530 p_country_code => p_country_code,
1531 p_country_region_code => p_country_region_code,
1532 p_state_code => p_state_code,
1533 p_city_code => p_city_code,
1534 p_port_flag => p_port_flag,
1535 p_airport_flag => p_airport_flag,
1536 p_road_terminal_flag => p_road_terminal_flag,
1537 p_rail_terminal_flag => p_rail_terminal_flag,
1538 p_longitude => p_longitude,
1539 p_latitude => p_latitude,
1540 p_timezone => p_timezone,
1541 p_continent => p_continent,
1542 p_country => p_country,
1543 p_country_region => p_country_region,
1544 p_state => p_state,
1545 p_city => p_city,
1546 p_alternate_name => p_alternate_name,
1547 p_county => p_county,
1548 p_postal_code_from => p_postal_code_from,
1549 p_postal_code_to => p_postal_code_to,
1550 p_lang_code => p_lang_code,
1551 p_interface_flag => p_interface_flag,
1552 p_tl_only_flag => l_tl_only_flag,
1553 p_region_id => l_existing_region_id,
1554 p_parent_region_id => p_parent_region_id,
1555 p_user_id => p_user_id,
1556 p_insert_parent_flag => p_insert_parent_flag,
1557 p_region_dff => p_region_dff,
1558 x_region_id => l_region_id,
1559 x_status => l_status,
1560 x_error_msg => l_error_msg,
1561 p_deconsol_location_id => p_deconsol_location_id);
1562
1563 x_region_id := l_region_id;
1564 x_status := l_status;
1565 x_error_msg := l_error_msg;
1566 ELSE
1567 IF (p_region_id = -1 OR p_region_id IS NULL)
1568 THEN
1569 IF ( p_conc_request_flag = 'Y' )
1570 THEN -- { Concurrent Request
1571 --
1572 -- Debug Statements
1573 --
1574 IF l_debug_on THEN
1575 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.CHECK_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
1576 END IF;
1577 --
1578 WSH_REGIONS_SEARCH_PKG.Check_Region_Info(
1579 p_country => p_country,
1580 p_state => p_state,
1581 p_city => p_city,
1582 p_postal_code_from => p_postal_code_from,
1583 p_postal_code_to => p_postal_code_to,
1584 p_region_type => l_region_type,
1585 p_search_flag => 'N',
1586 p_lang_code => p_lang_code,
1587 x_return_status => l_return_status,
1588 x_region_info => l_region_info);
1589
1590 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1591 THEN
1592 x_status := 2;
1593 x_error_msg := 'WSH_UTIL_MESSAGE_U';
1594 x_region_id := -1;
1595 END IF;
1596 ELSE
1597 --
1598 -- Debug Statements
1599 --
1600 IF l_debug_on THEN
1601 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
1602 END IF;
1603 --
1604 Wsh_Regions_Search_Pkg.Get_Region_Info(
1605 p_country => p_country,
1606 p_country_region => p_country_region,
1607 p_state => p_state,
1608 p_city => p_city,
1609 p_postal_code_from => p_postal_code_from,
1610 p_postal_code_to => p_postal_code_to,
1611 p_zone => null,
1612 p_lang_code => p_lang_code,
1613 p_country_code => null,
1614 p_country_region_code => null,
1615 p_state_code => null,
1616 p_city_code => null,
1617 p_region_type => l_region_type,
1618 p_interface_flag => p_interface_flag,
1619 x_region_info => l_region_info);
1620 END IF;
1621
1622 l_region_id := l_region_info.region_id;
1623
1624 IF (l_region_id = -1 AND
1625 (p_city_code IS NULL OR p_city IS NOT NULL) AND
1626 (p_state_code IS NULL OR p_state IS NOT NULL) AND
1627 (p_country_code IS NULL OR p_country IS NOT NULL))
1628 THEN
1629 IF ( p_conc_request_flag = 'Y' )
1630 THEN -- { p_conc_request_flag
1631 --
1632 -- Debug Statements
1633 --
1634 IF l_debug_on THEN
1635 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.CHECK_REGION_ID_CODES_ONLY', WSH_DEBUG_SV.C_PROC_LEVEL);
1636 END IF;
1637 --
1638 WSH_REGIONS_SEARCH_PKG.Check_Region_Id_Codes_Only(
1639 p_country_code => p_country_code,
1640 p_state_code => p_state_code,
1641 p_city_code => p_city_code,
1642 p_postal_code_from => p_postal_code_from,
1643 p_postal_code_to => p_postal_code_to,
1644 p_region_type => l_region_type,
1645 p_language_code => null,
1646 x_return_status => l_return_status,
1647 x_region_id => l_region_id);
1648
1649 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1650 THEN
1651 x_status := 2;
1652 x_error_msg := 'WSH_UTIL_MESSAGE_U';
1653 x_region_id := -1;
1654 END IF;
1655
1656 IF (l_region_id <> -1) THEN
1657 --
1658 -- Debug Statements
1659 --
1660 IF l_debug_on THEN
1661 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.CHECK_REGION_ID_CODES_ONLY WITH LANGUAGE', WSH_DEBUG_SV.C_PROC_LEVEL);
1662 END IF;
1663 --
1664 WSH_REGIONS_SEARCH_PKG.Check_Region_Id_Codes_Only(
1665 p_country_code => p_country_code,
1666 p_state_code => p_state_code,
1667 p_city_code => p_city_code,
1668 p_postal_code_from => p_postal_code_from,
1669 p_postal_code_to => p_postal_code_to,
1670 p_region_type => l_region_type,
1671 p_language_code => p_lang_code,
1672 x_return_status => l_return_status,
1673 x_region_id => l_check_tl_id);
1674
1675 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1676 THEN
1677 x_status := 2;
1678 x_error_msg := 'WSH_UTIL_MESSAGE_U';
1679 x_region_id := -1;
1680 END IF;
1681 END IF;
1682 ELSE
1683 --
1684 -- Debug Statements
1685 --
1686 IF l_debug_on THEN
1687 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_ID_CODES_ONLY',WSH_DEBUG_SV.C_PROC_LEVEL);
1688 END IF;
1689 --
1690 Wsh_Regions_Search_Pkg.Get_Region_Id_Codes_Only(
1691 p_country_code => p_country_code,
1692 p_country_region_code => p_country_region_code,
1693 p_state_code => p_state_code,
1694 p_city_code => p_city_code,
1695 p_postal_code_from => p_postal_code_from,
1696 p_postal_code_to => p_postal_code_to,
1697 p_region_type => l_region_type,
1698 p_interface_flag => p_interface_flag,
1699 p_lang_code => p_lang_code,
1700 x_region_id_non_tl => l_region_id,
1701 x_region_id_with_tl => l_check_tl_id);
1702 END IF;
1703
1704 -- fnd_file.put_line(fnd_file.log,'after region id codes 2 '||l_region_id||' x '||l_check_tl_id);
1705 IF (l_region_id <> -1 AND l_check_tl_id <> -1) THEN
1706 x_status := 2;
1707 x_error_msg := 'WSH_CAT_DUPLICATE_REGION';
1708 x_region_id := -1;
1709 --
1710 -- Debug Statements
1711 --
1712 IF l_debug_on THEN
1713 WSH_DEBUG_SV.pop(l_module_name);
1714 END IF;
1715 --
1716 return;
1717 END IF;
1718
1719 END IF;
1720 ELSE
1721 l_region_id := p_region_id;
1722 END IF;
1723
1724 IF (l_region_id <> -1) THEN
1725 x_region_id := l_region_id;
1726
1727 IF (p_interface_flag = 'Y') THEN
1728 UPDATE WSH_REGIONS_INTERFACE
1729 SET COUNTRY_CODE = p_country_code,
1730 COUNTRY_REGION_CODE = p_country_region_code,
1731 STATE_CODE = p_state_code,
1732 CITY_CODE = p_city_code,
1733 PORT_FLAG = p_port_flag,
1734 AIRPORT_FLAG = p_airport_flag,
1735 ROAD_TERMINAL_FLAG = p_road_terminal_flag,
1736 RAIL_TERMINAL_FLAG = p_rail_terminal_flag,
1737 PROCESSED_FLAG = null,
1738 LAST_UPDATE_DATE = sysdate,
1739 LAST_UPDATED_BY = p_user_id
1740 WHERE REGION_ID = l_region_id;
1741
1742 UPDATE WSH_REGIONS_TL_INTERFACE
1743 SET CONTINENT = p_continent,
1744 COUNTRY = p_country,
1745 COUNTRY_REGION = p_country_region,
1746 STATE = p_state,
1747 CITY = p_city,
1748 ALTERNATE_NAME = p_alternate_name,
1749 COUNTY = p_county,
1750 POSTAL_CODE_FROM = p_postal_code_from,
1751 POSTAL_CODE_TO = p_postal_code_to,
1752 LAST_UPDATE_DATE = sysdate,
1753 LAST_UPDATED_BY = p_user_id
1754 WHERE REGION_ID = l_region_id
1755 AND LANGUAGE = p_lang_code;
1756
1757 IF (SQL%NOTFOUND) THEN
1758 INSERT INTO WSH_REGIONS_TL_INTERFACE (
1759 LANGUAGE,
1760 REGION_ID,
1761 CONTINENT,
1762 COUNTRY,
1763 COUNTRY_REGION,
1764 STATE,
1765 CITY,
1766 ALTERNATE_NAME,
1767 COUNTY,
1768 POSTAL_CODE_FROM,
1769 POSTAL_CODE_TO,
1770 CREATED_BY,
1771 CREATION_DATE,
1772 LAST_UPDATED_BY,
1773 LAST_UPDATE_DATE,
1774 LAST_UPDATE_LOGIN)
1775 VALUES (
1776 p_lang_code,
1777 l_region_id,
1778 p_continent,
1779 p_country,
1780 p_country_region,
1781 p_state,
1782 p_city,
1783 p_alternate_name,
1784 p_county,
1785 p_postal_code_from,
1786 p_postal_code_to,
1787 p_user_id,
1788 sysdate,
1789 p_user_id,
1790 sysdate,
1791 p_user_id);
1792 END IF;
1793 ELSE
1794 --
1795 -- Update the DFF attributes for the passed region.
1796 --
1797 UPDATE WSH_REGIONS
1798 SET ATTRIBUTE_CATEGORY = p_region_dff.attribute_category,
1799 ATTRIBUTE1 = p_region_dff.attribute1,
1800 ATTRIBUTE2 = p_region_dff.attribute2,
1801 ATTRIBUTE3 = p_region_dff.attribute3,
1802 ATTRIBUTE4 = p_region_dff.attribute4,
1803 ATTRIBUTE5 = p_region_dff.attribute5,
1804 ATTRIBUTE6 = p_region_dff.attribute6,
1805 ATTRIBUTE7 = p_region_dff.attribute7,
1806 ATTRIBUTE8 = p_region_dff.attribute8,
1807 ATTRIBUTE9 = p_region_dff.attribute9,
1808 ATTRIBUTE10 = p_region_dff.attribute10,
1809 ATTRIBUTE11 = p_region_dff.attribute11,
1810 ATTRIBUTE12 = p_region_dff.attribute12,
1811 ATTRIBUTE13 = p_region_dff.attribute13,
1812 ATTRIBUTE14 = p_region_dff.attribute14,
1813 ATTRIBUTE15 = p_region_dff.attribute15
1814 WHERE region_id = l_region_id;
1815
1816 /*
1817 SELECT zone_level INTO l_parent_zone_level
1818 FROM wsh_regions WHERE region_id = l_region_id;
1819 */
1820 -- Bug 3364618 : Replaced select statement by a cursor.
1821
1822 OPEN get_zone_level(l_region_id);
1823 FETCH get_zone_level INTO l_parent_zone_level;
1824 CLOSE get_zone_level;
1825
1826 -- --
1827 -- Update values for the other attributes. Update the value of child records
1828 --
1829
1830
1831 FOR reg IN child_regions(l_region_id) LOOP
1832
1833 -- Debug Statements
1834 --
1835 IF l_debug_on THEN
1836 WSH_DEBUG_SV.logmsg(l_module_name, ' updating region '||reg.region_id);
1837 END IF;
1838
1839 /* select state_code
1840 into l_update_state_code
1841 from wsh_regions
1842 where region_id = reg.parent_region_id ;
1843 */
1844
1845 --Bug 3364618 : Replaced select statement by a cursor.
1846
1847 OPEN get_state_code(reg.parent_region_id);
1848 FETCH get_state_code INTO l_update_state_code, l_update_city_code;
1849 CLOSE get_state_code;
1850
1851 update WSH_REGIONS set
1852 COUNTRY_CODE = nvl(p_country_code, COUNTRY_CODE),
1853 COUNTRY_REGION_CODE = nvl(p_country_region_code, COUNTRY_REGION_CODE),
1854 STATE_CODE = decode(reg.zone_level, 2, l_update_state_code, 3, l_update_state_code,
1855 decode(l_parent_zone_level, 1, p_state_code, nvl(p_state_code, STATE_CODE))),
1856 CITY_CODE = decode(l_parent_zone_level, 2, p_city_code, nvl(p_city_code, CITY_CODE)),
1857 PORT_FLAG = nvl(p_port_flag,PORT_FLAG),
1858 AIRPORT_FLAG = nvl(p_airport_flag,AIRPORT_FLAG),
1859 ROAD_TERMINAL_FLAG = nvl(p_road_terminal_flag, ROAD_TERMINAL_FLAG),
1860 RAIL_TERMINAL_FLAG = nvl(p_rail_terminal_flag, RAIL_TERMINAL_FLAG),
1861 LAST_UPDATE_DATE = sysdate,
1862 LAST_UPDATED_BY = p_user_id,
1863
1864 -- bug 4509707 : deconsol_location_id should not be propagated to sub-regions, update it only for the current region (l_region_id)
1865 -- deconsol_location_id = p_deconsol_location_id
1866 deconsol_location_id = decode(reg.region_id, l_region_id, p_deconsol_location_id, deconsol_location_id)
1867 where REGION_ID = reg.region_id;
1868
1869 update WSH_REGIONS_TL set
1870 CONTINENT = nvl(p_continent, CONTINENT),
1871 COUNTRY = nvl(p_country, COUNTRY),
1872 COUNTRY_REGION = nvl(p_country_region, COUNTRY_REGION),
1873 STATE = nvl(p_state, STATE),
1874 CITY = nvl(p_city, CITY),
1875 ALTERNATE_NAME = nvl(p_alternate_name, ALTERNATE_NAME),
1876 COUNTY = nvl(p_county, COUNTY),
1877 POSTAL_CODE_FROM = nvl(p_postal_code_from, POSTAL_CODE_FROM),
1878 POSTAL_CODE_TO = nvl(p_postal_code_to, POSTAL_CODE_TO),
1879 LAST_UPDATE_DATE = sysdate,
1880 LAST_UPDATED_BY = p_user_id
1881 where REGION_ID = reg.region_id and
1882 LANGUAGE = p_lang_code;
1883
1884 END LOOP;
1885
1886 IF (SQL%NOTFOUND) THEN
1887 IF (p_insert_type = 'SYNC') THEN
1888 INSERT INTO WSH_REGIONS_TL (
1889 LANGUAGE,
1890 REGION_ID,
1891 CONTINENT,
1892 COUNTRY,
1893 COUNTRY_REGION,
1894 STATE,
1895 CITY,
1896 ALTERNATE_NAME,
1897 COUNTY,
1898 POSTAL_CODE_FROM,
1899 POSTAL_CODE_TO,
1900 CREATED_BY,
1901 CREATION_DATE,
1902 LAST_UPDATED_BY,
1903 LAST_UPDATE_DATE,
1904 LAST_UPDATE_LOGIN)
1905 VALUES (
1906 p_lang_code,
1907 l_region_id,
1908 p_continent,
1909 p_country,
1910 p_country_region,
1911 p_state,
1912 p_city,
1913 p_alternate_name,
1914 p_county,
1915 p_postal_code_from,
1916 p_postal_code_to,
1917 p_user_id,
1918 sysdate,
1919 p_user_id,
1920 sysdate,
1921 p_user_id);
1922
1923 else --could not find the region to update
1924 x_status := 2;
1925 x_error_msg := 'WSH_CAT_CANNOT_UPDATE_REGION';
1926 x_region_id := -1;
1927 --
1928 -- Debug Statements
1929 --
1930 IF l_debug_on THEN
1931 WSH_DEBUG_SV.pop(l_module_name);
1932 END IF;
1933 --
1934 return;
1935 END IF;
1936 END IF;
1937
1938 IF ( p_conc_request_flag = 'Y' )
1939 THEN
1940 --
1941 -- Debug Statements
1942 --
1943 IF l_debug_on THEN
1944 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.UPDATE_GLOBAL_TABLE', WSH_DEBUG_SV.C_PROC_LEVEL);
1945 END IF;
1946 --
1947 Update_Global_Table(
1948 p_country => p_country,
1949 p_state => p_state,
1950 p_city => p_city,
1951 p_country_code => p_country_code,
1952 p_state_code => p_state_code,
1953 p_city_code => p_city_code,
1954 p_region_id => l_region_id,
1955 p_postal_code_from => p_postal_code_from,
1956 p_postal_code_to => p_postal_code_to,
1957 p_parent_zone_level => l_parent_zone_level,
1958 p_lang_code => p_lang_code,
1959 x_return_status => l_return_status );
1960
1961 -- Error Handling Part
1962 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1963 THEN
1964 x_status := 2;
1965 x_error_msg := 'WSH_UTIL_MESSAGE_U';
1966 x_region_id := -1;
1967 END IF;
1968 END IF;
1969 END IF;
1970 ELSE
1971 IF (p_insert_type = 'SYNC') THEN
1972
1973 INSERT_REGION(
1974 p_country_code => p_country_code,
1975 p_country_region_code => p_country_region_code,
1976 p_state_code => p_state_code,
1977 p_city_code => p_city_code,
1978 p_port_flag => p_port_flag,
1979 p_airport_flag => p_airport_flag,
1980 p_road_terminal_flag => p_road_terminal_flag,
1981 p_rail_terminal_flag => p_rail_terminal_flag,
1982 p_longitude => p_longitude,
1983 p_latitude => p_latitude,
1984 p_timezone => p_timezone,
1985 p_continent => p_continent,
1986 p_country => p_country,
1987 p_country_region => p_country_region,
1988 p_state => p_state,
1989 p_city => p_city,
1990 p_alternate_name => p_alternate_name,
1991 p_county => p_county,
1992 p_postal_code_from => p_postal_code_from,
1993 p_postal_code_to => p_postal_code_to,
1994 p_lang_code => p_lang_code,
1995 p_interface_flag => p_interface_flag,
1996 p_tl_only_flag => l_tl_only_flag,
1997 p_region_id => l_existing_region_id,
1998 p_parent_region_id => p_parent_region_id,
1999 p_user_id => p_user_id,
2000 p_insert_parent_flag => p_insert_parent_flag,
2001 p_region_dff => p_region_dff,
2002 x_region_id => l_region_id,
2003 x_status => l_status,
2004 x_error_msg => l_error_msg,
2005 p_deconsol_location_id => p_deconsol_location_id,
2006 p_conc_request_flag => p_conc_request_flag);
2007
2008 x_region_id := l_region_id;
2009 x_status := l_status;
2010 x_error_msg := l_error_msg;
2011 ELSE
2012 x_region_id := -1;
2013 x_status := 2;
2014 x_error_msg := 'WSH_CAT_CANNOT_UPDATE_REGION';
2015 --
2016 -- Debug Statements
2017 --
2018 IF l_debug_on THEN
2019 WSH_DEBUG_SV.pop(l_module_name);
2020 END IF;
2021 --
2022 return;
2023 END IF;
2024 END IF;
2025 END IF;
2026
2027 --
2028 -- Debug Statements
2029 --
2030 IF l_debug_on THEN
2031 WSH_DEBUG_SV.pop(l_module_name);
2032 END IF;
2033 --
2034 END Update_Region;
2035
2036 --
2037 -- Procedure: Delete_Region
2038 --
2039 -- Purpose: Deletes a region (for interface use only)
2040 --
2041
2042 PROCEDURE Delete_Region (
2043 p_region_id IN NUMBER,
2044 p_lang_code IN VARCHAR2,
2045 p_interface_flag IN VARCHAR2,
2046 x_status OUT NOCOPY NUMBER,
2047 x_error_msg OUT NOCOPY VARCHAR2) IS
2048
2049 CURSOR lock_rows IS
2050 SELECT r.region_id
2051 FROM wsh_regions_interface r, wsh_regions_tl_interface t
2052 WHERE r.region_id = t.region_id AND
2053 r.region_id = p_region_id AND
2054 t.language = nvl(p_lang_code, t.language)
2055 FOR UPDATE OF r.region_id NOWAIT;
2056
2057 l_region_id NUMBER;
2058
2059 --
2060 l_debug_on BOOLEAN;
2061 --
2062 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_REGION';
2063 --
2064 BEGIN
2065
2066 --
2067 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2068 --
2069 IF l_debug_on IS NULL
2070 THEN
2071 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2072 END IF;
2073 --
2074 --
2075 -- Debug Statements
2076 --
2077 IF l_debug_on THEN
2078 WSH_DEBUG_SV.push(l_module_name);
2079 --
2080 WSH_DEBUG_SV.log(l_module_name,'P_REGION_ID',P_REGION_ID);
2081 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2082 WSH_DEBUG_SV.log(l_module_name,'P_INTERFACE_FLAG',P_INTERFACE_FLAG);
2083 END IF;
2084 --
2085 IF (p_interface_flag = 'Y') THEN
2086
2087 OPEN lock_rows;
2088 FETCH lock_rows INTO l_region_id;
2089 CLOSE lock_rows;
2090
2091 DELETE FROM wsh_regions_tl_interface WHERE region_id = l_region_id AND language= nvl(p_lang_code, language);
2092 DELETE FROM wsh_regions_interface WHERE region_id = l_region_id;
2093
2094 ELSE
2095 x_status := 2;
2096 x_error_msg := 'Cannot delete region';
2097 END IF;
2098
2099 --
2100 -- Debug Statements
2101 --
2102 IF l_debug_on THEN
2103 WSH_DEBUG_SV.pop(l_module_name);
2104 END IF;
2105 --
2106 END Delete_Region;
2107
2108
2109 --
2110 -- Procedure: Lock_Region
2111 -- Parameters: p_region_id - region_id for region to be locked
2112 -- x_return_status - Status of procedure call
2113 -- Description: This procedure will lock a region record. It is
2114 -- specifically designed for use by the form.
2115 --
2116
2117 PROCEDURE Lock_Region
2118 ( p_region_id IN NUMBER,
2119 p_lang_code IN VARCHAR2,
2120 p_country IN VARCHAR2,
2121 p_state IN VARCHAR2,
2122 p_city IN VARCHAR2,
2123 p_postal_code_from IN VARCHAR2,
2124 p_postal_code_to IN VARCHAR2,
2125 p_country_code IN VARCHAR2,
2126 p_state_code IN VARCHAR2,
2127 p_city_code IN VARCHAR2,
2128 p_region_dff IN REGION_DFF_REC DEFAULT NULL,
2129 x_status OUT NOCOPY NUMBER,
2130 p_deconsol_location_id IN NUMBER DEFAULT NULL) IS
2131
2132 record_locked EXCEPTION;
2133 PRAGMA EXCEPTION_INIT(record_locked, -54);
2134
2135 CURSOR lock_row IS
2136 SELECT w.region_id, w.country_code, w.state_code,w.city_code,w.attribute_category,
2137 w.attribute1,w.attribute2,w.attribute3,w.attribute4,w.attribute5,
2138 w.attribute6,w.attribute7,w.attribute8,w.attribute9,w.attribute10,
2139 w.attribute11,w.attribute12,w.attribute13,w.attribute14,w.attribute15,
2140 w.deconsol_location_id
2141 FROM wsh_regions w
2142 WHERE w.region_id = p_region_id
2143 FOR UPDATE OF w.region_id NOWAIT;
2144
2145 CURSOR lock_row_tl IS
2146 SELECT t.region_id, t.country, t.state, t.city, t.postal_code_from, t.postal_code_to
2147 FROM wsh_regions_tl t
2148 WHERE t.language = p_lang_code
2149 AND t.region_id = p_region_id
2150 FOR UPDATE OF t.region_id NOWAIT;
2151
2152 Recinfo lock_row%ROWTYPE;
2153 Recinfo_tl lock_row_tl%ROWTYPE;
2154
2155 --
2156 l_debug_on BOOLEAN;
2157 --
2158 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_REGION';
2159 --
2160 BEGIN
2161
2162 --
2163 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2164 --
2165 IF l_debug_on IS NULL
2166 THEN
2167 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2168 END IF;
2169 --
2170 --
2171 -- Debug Statements
2172 --
2173 IF l_debug_on THEN
2174 WSH_DEBUG_SV.push(l_module_name);
2175 --
2176 WSH_DEBUG_SV.log(l_module_name,'P_REGION_ID',P_REGION_ID);
2177 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2178 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
2179 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2180 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2181 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2182 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2183 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2184 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2185 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2186 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE_CATEGORY',P_REGION_DFF.ATTRIBUTE_CATEGORY);
2187 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE1',P_REGION_DFF.ATTRIBUTE1);
2188 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE2',P_REGION_DFF.ATTRIBUTE2);
2189 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE3',P_REGION_DFF.ATTRIBUTE3);
2190 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE4',P_REGION_DFF.ATTRIBUTE4);
2191 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE5',P_REGION_DFF.ATTRIBUTE5);
2192 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE6',P_REGION_DFF.ATTRIBUTE6);
2193 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE7',P_REGION_DFF.ATTRIBUTE7);
2194 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE8',P_REGION_DFF.ATTRIBUTE8);
2195 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE9',P_REGION_DFF.ATTRIBUTE9);
2196 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE10',P_REGION_DFF.ATTRIBUTE10);
2197 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE11',P_REGION_DFF.ATTRIBUTE11);
2198 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE12',P_REGION_DFF.ATTRIBUTE12);
2199 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE13',P_REGION_DFF.ATTRIBUTE13);
2200 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE14',P_REGION_DFF.ATTRIBUTE14);
2201 WSH_DEBUG_SV.log(l_module_name,'P_REGION_DFF.ATTRIBUTE15',P_REGION_DFF.ATTRIBUTE15);
2202 WSH_DEBUG_SV.log(l_module_name, 'P_DECONSOL_LOCATION_ID', P_DECONSOL_LOCATION_ID);
2203 END IF;
2204 --
2205 OPEN lock_row;
2206 FETCH lock_row INTO Recinfo;
2207
2208 IF (lock_row%NOTFOUND) THEN
2209 CLOSE lock_row;
2210 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
2211 app_exception.raise_exception;
2212 END IF;
2213
2214 CLOSE lock_row;
2215
2216 IF (
2217 (Recinfo.region_id = p_region_id)
2218 AND ( (Recinfo.country_code = p_country_code)
2219 OR ( (Recinfo.country_code IS NULL) AND (p_country_code IS NULL)))
2220 AND ( (Recinfo.state_code = p_state_code)
2221 OR ( (Recinfo.state_code IS NULL) AND (p_state_code IS NULL)))
2222 AND ( (Recinfo.city_code = p_city_code)
2223 OR ( (Recinfo.city_code IS NULL) AND (p_city_code IS NULL)))
2224 AND ( (Recinfo.attribute_category = p_region_dff.attribute_category)
2225 OR ((Recinfo.attribute_category IS NULL) AND (p_region_dff.attribute_category IS NULL)))
2226 AND ( (Recinfo.attribute1 = p_region_dff.attribute1)
2227 OR ((Recinfo.attribute1 IS NULL) AND (p_region_dff.attribute1 IS NULL)))
2228 AND ( (Recinfo.attribute2 = p_region_dff.attribute2)
2229 OR ((Recinfo.attribute2 IS NULL) AND (p_region_dff.attribute2 IS NULL)))
2230 AND ( (Recinfo.attribute3 = p_region_dff.attribute3)
2231 OR ((Recinfo.attribute3 IS NULL) AND (p_region_dff.attribute3 IS NULL)))
2232 AND ( (Recinfo.attribute4 = p_region_dff.attribute4)
2233 OR ((Recinfo.attribute4 IS NULL) AND (p_region_dff.attribute4 IS NULL)))
2234 AND ( (Recinfo.attribute5 = p_region_dff.attribute5)
2235 OR ((Recinfo.attribute5 IS NULL) AND (p_region_dff.attribute5 IS NULL)))
2236 AND ( (Recinfo.attribute6 = p_region_dff.attribute6)
2237 OR ((Recinfo.attribute6 IS NULL) AND (p_region_dff.attribute6 IS NULL)))
2238 AND ( (Recinfo.attribute7 = p_region_dff.attribute7)
2239 OR ((Recinfo.attribute7 IS NULL) AND (p_region_dff.attribute7 IS NULL)))
2240 AND ( (Recinfo.attribute8 = p_region_dff.attribute8)
2241 OR ((Recinfo.attribute8 IS NULL) AND (p_region_dff.attribute8 IS NULL)))
2242 AND ( (Recinfo.attribute9 = p_region_dff.attribute9)
2243 OR ((Recinfo.attribute9 IS NULL) AND (p_region_dff.attribute9 IS NULL)))
2244 AND ( (Recinfo.attribute10 = p_region_dff.attribute10)
2245 OR ((Recinfo.attribute10 IS NULL) AND (p_region_dff.attribute10 IS NULL)))
2246 AND ( (Recinfo.attribute11 = p_region_dff.attribute11)
2247 OR ((Recinfo.attribute11 IS NULL) AND (p_region_dff.attribute11 IS NULL)))
2248 AND ( (Recinfo.attribute12 = p_region_dff.attribute12)
2249 OR ((Recinfo.attribute12 IS NULL) AND (p_region_dff.attribute12 IS NULL)))
2250 AND ( (Recinfo.attribute13 = p_region_dff.attribute13)
2251 OR ((Recinfo.attribute13 IS NULL) AND (p_region_dff.attribute13 IS NULL)))
2252 AND ( (Recinfo.attribute14 = p_region_dff.attribute14)
2253 OR ((Recinfo.attribute14 IS NULL) AND (p_region_dff.attribute14 IS NULL)))
2254 AND ( (Recinfo.attribute15 = p_region_dff.attribute15)
2255 OR ((Recinfo.attribute15 IS NULL) AND (p_region_dff.attribute15 IS NULL)))
2256 AND ( (Recinfo.deconsol_location_id = p_deconsol_location_id)
2257 OR ((Recinfo.deconsol_location_id IS NULL) AND (p_deconsol_location_id IS NULL)))
2258
2259 ) THEN
2260 --
2261 -- Debug Statements
2262 --
2263 IF l_debug_on THEN
2264 WSH_DEBUG_SV.pop(l_module_name);
2265 END IF;
2266 --
2267 RETURN;
2268 ELSE
2269 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2270 app_exception.raise_exception;
2271 END IF;
2272
2273 OPEN lock_row_tl;
2274 FETCH lock_row_tl INTO Recinfo_tl;
2275
2276 IF (lock_row_tl%NOTFOUND) THEN
2277 CLOSE lock_row_tl;
2278 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
2279 app_exception.raise_exception;
2280 END IF;
2281
2282 CLOSE lock_row_tl;
2283
2284 IF (
2285 (Recinfo_tl.region_id = p_region_id)
2286 AND ( (Recinfo_tl.country = p_country)
2287 OR ( (Recinfo_tl.country IS NULL) AND (p_country IS NULL)))
2288 AND ( (Recinfo_tl.state = p_state)
2289 OR ( (Recinfo_tl.state IS NULL) AND (p_state IS NULL)))
2290 AND ( (Recinfo_tl.city = p_city)
2291 OR ( (Recinfo_tl.city IS NULL) AND (p_city IS NULL)))
2292 AND ( (Recinfo_tl.postal_code_from = p_postal_code_from)
2293 OR ( (Recinfo_tl.postal_code_from IS NULL) AND (p_postal_code_from IS NULL)))
2294 AND ( (Recinfo_tl.postal_code_to = p_postal_code_to)
2295 OR ( (Recinfo_tl.postal_code_to IS NULL) AND (p_postal_code_to IS NULL)))
2296 ) THEN
2297 --
2298 -- Debug Statements
2299 --
2300 IF l_debug_on THEN
2301 WSH_DEBUG_SV.pop(l_module_name);
2302 END IF;
2303 --
2304 RETURN;
2305 ELSE
2306 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2307 app_exception.raise_exception;
2308 END IF;
2309
2310 --
2311 -- Debug Statements
2312 --
2313 IF l_debug_on THEN
2314 WSH_DEBUG_SV.pop(l_module_name);
2315 END IF;
2316 --
2317 EXCEPTION
2318 WHEN RECORD_LOCKED THEN
2319 if (lock_row%ISOPEN) then
2320 close lock_row;
2321 end if;
2322
2323 if (lock_row_tl%ISOPEN) then
2324 close lock_row_tl;
2325 end if;
2326 -- Fixing Lock message bug
2327 fnd_message.set_name('WSH', 'WSH_FORM_RECORD_IS_CHANGED');
2328 app_exception.raise_exception;
2329 --
2330 -- Debug Statements
2331 --
2332 IF l_debug_on THEN
2333 WSH_DEBUG_SV.logmsg(l_module_name,'RECORD_LOCKED exception has occured.',WSH_DEBUG_SV.C_EXCEP_LEVEL);
2334 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:RECORD_LOCKED');
2335 END IF;
2336 --
2337 WHEN others THEN
2338 if (lock_row%ISOPEN) then
2339 close lock_row;
2340 end if;
2341
2342 if (lock_row_tl%ISOPEN) then
2343 close lock_row_tl;
2344 end if;
2345
2346 --raise;
2347 app_exception.raise_exception;
2348
2349 --
2350 -- Debug Statements
2351 --
2352 IF l_debug_on THEN
2353 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2354 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2355 END IF;
2356 --
2357 END Lock_Region;
2358
2359 --
2360 -- Procedure: Lock_Region_Interface
2361 -- Parameters: p_region_id - region_id for region to be locked
2362 -- x_return_status - Status of procedure call
2363 -- Description: This procedure will lock a region interface record. It is
2364 -- specifically designed for use by the form.
2365 --
2366
2367 PROCEDURE Lock_Region_Interface
2368 ( p_region_id IN NUMBER,
2369 p_lang_code IN VARCHAR2,
2370 p_country IN VARCHAR2,
2371 p_state IN VARCHAR2,
2372 p_city IN VARCHAR2,
2373 p_postal_code_from IN VARCHAR2,
2374 p_postal_code_to IN VARCHAR2,
2375 p_country_code IN VARCHAR2,
2376 p_state_code IN VARCHAR2,
2377 p_city_code IN VARCHAR2,
2378 x_status OUT NOCOPY NUMBER) IS
2379
2380
2381 CURSOR lock_row IS
2382 SELECT w.region_id, w.country_code, w.state_code, w.city_code
2383 FROM wsh_regions_interface w
2384 WHERE w.region_id = p_region_id
2385 FOR UPDATE OF w.region_id NOWAIT;
2386
2387 CURSOR lock_row_tl IS
2388 SELECT t.region_id, t.country, t.state, t.city, t.postal_code_from, t.postal_code_to
2389 FROM wsh_regions_tl_interface t
2390 WHERE t.language = p_lang_code
2391 AND t.region_id = p_region_id
2392 FOR UPDATE OF t.region_id NOWAIT;
2393
2394 Recinfo lock_row%ROWTYPE;
2395 Recinfo_tl lock_row_tl%ROWTYPE;
2396
2397 --
2398 l_debug_on BOOLEAN;
2399 --
2400 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_REGION_INTERFACE';
2401 --
2402 BEGIN
2403
2404 --
2405 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2406 --
2407 IF l_debug_on IS NULL
2408 THEN
2409 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2410 END IF;
2411 --
2412 --
2413 -- Debug Statements
2414 --
2415 IF l_debug_on THEN
2416 WSH_DEBUG_SV.push(l_module_name);
2417 --
2418 WSH_DEBUG_SV.log(l_module_name,'P_REGION_ID',P_REGION_ID);
2419 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2420 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
2421 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
2422 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
2423 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
2424 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
2425 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
2426 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
2427 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
2428 END IF;
2429 --
2430 OPEN lock_row;
2431 FETCH lock_row INTO Recinfo;
2432
2433 IF (lock_row%NOTFOUND) THEN
2434 CLOSE lock_row;
2435 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
2436 app_exception.raise_exception;
2437 END IF;
2438
2439 CLOSE lock_row;
2440
2441 IF (
2442 (Recinfo.region_id = p_region_id)
2443 AND ( (Recinfo.country_code = p_country_code)
2444 OR ( (Recinfo.country_code IS NULL) AND (p_country_code IS NULL)))
2445 AND ( (Recinfo.state_code = p_state_code)
2446 OR ( (Recinfo.state_code IS NULL) AND (p_state_code IS NULL)))
2447 AND ( (Recinfo.city_code = p_city_code)
2448 OR ( (Recinfo.city_code IS NULL) AND (p_city_code IS NULL)))
2449 ) THEN
2450 --
2451 -- Debug Statements
2452 --
2453 IF l_debug_on THEN
2454 WSH_DEBUG_SV.pop(l_module_name);
2455 END IF;
2456 --
2457 RETURN;
2458 ELSE
2459 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2460 app_exception.raise_exception;
2461 END IF;
2462
2463 OPEN lock_row_tl;
2464 FETCH lock_row_tl INTO Recinfo_tl;
2465
2466 IF (lock_row_tl%NOTFOUND) THEN
2467 CLOSE lock_row_tl;
2468 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
2469 app_exception.raise_exception;
2470 END IF;
2471
2472 CLOSE lock_row_tl;
2473
2474 IF (
2475 (Recinfo_tl.region_id = p_region_id)
2476 AND ( (Recinfo_tl.country = p_country)
2477 OR ( (Recinfo_tl.country IS NULL) AND (p_country IS NULL)))
2478 AND ( (Recinfo_tl.state = p_state)
2479 OR ( (Recinfo_tl.state IS NULL) AND (p_state IS NULL)))
2480 AND ( (Recinfo_tl.city = p_city)
2481 OR ( (Recinfo_tl.city IS NULL) AND (p_city IS NULL)))
2482 AND ( (Recinfo_tl.postal_code_from = p_postal_code_from)
2483 OR ( (Recinfo_tl.postal_code_from IS NULL) AND (p_postal_code_from IS NULL)))
2484 AND ( (Recinfo_tl.postal_code_to = p_postal_code_to)
2485 OR ( (Recinfo_tl.postal_code_to IS NULL) AND (p_postal_code_to IS NULL)))
2486 ) THEN
2487 --
2488 -- Debug Statements
2489 --
2490 IF l_debug_on THEN
2491 WSH_DEBUG_SV.pop(l_module_name);
2492 END IF;
2493 --
2494 RETURN;
2495 ELSE
2496 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2497 app_exception.raise_exception;
2498 END IF;
2499
2500 --
2501 -- Debug Statements
2502 --
2503 IF l_debug_on THEN
2504 WSH_DEBUG_SV.pop(l_module_name);
2505 END IF;
2506 --
2507 EXCEPTION
2508 WHEN others THEN
2509 if (lock_row%ISOPEN) then
2510 close lock_row;
2511 end if;
2512
2513 if (lock_row_tl%ISOPEN) then
2514 close lock_row_tl;
2515 end if;
2516
2517 --
2518 -- Debug Statements
2519 --
2520 IF l_debug_on THEN
2521 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2522 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2523 END IF;
2524 --
2525 raise;
2526
2527 --
2528 -- Debug Statements
2529 --
2530 IF l_debug_on THEN
2531 WSH_DEBUG_SV.pop(l_module_name);
2532 END IF;
2533 --
2534 END Lock_Region_Interface;
2535
2536 --
2537 -- Procedure: Update_Zone (this is called from the Regions and Zones form)
2538 --
2539 -- Purpose: Updates or inserts a new zone
2540 --
2541
2542 PROCEDURE Update_Zone (
2543 p_insert_type IN VARCHAR2,
2544 p_zone_id IN NUMBER,
2545 p_zone_name IN VARCHAR2,
2546 p_zone_level IN NUMBER,
2547 p_lang_code IN VARCHAR2,
2548 p_user_id IN NUMBER,
2549 p_zone_dff IN REGION_DFF_REC DEFAULT NULL,
2550 x_zone_id OUT NOCOPY NUMBER,
2551 x_status OUT NOCOPY NUMBER,
2552 x_error_msg OUT NOCOPY VARCHAR2,
2553 p_deconsol_location_id IN NUMBER DEFAULT NULL) IS
2554
2555 --
2556 l_debug_on BOOLEAN;
2557 --
2558 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ZONE';
2559 --
2560 BEGIN
2561
2562 --
2563 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2564 --
2565 IF l_debug_on IS NULL
2566 THEN
2567 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2568 END IF;
2569 --
2570 --
2571 -- Debug Statements
2572 --
2573 IF l_debug_on THEN
2574 WSH_DEBUG_SV.push(l_module_name);
2575 --
2576 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
2577 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_ID',P_ZONE_ID);
2578 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_NAME',P_ZONE_NAME);
2579 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_LEVEL',P_ZONE_LEVEL);
2580 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2581 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
2582 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE_CATEGORY',P_ZONE_DFF.ATTRIBUTE_CATEGORY);
2583 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE1',P_ZONE_DFF.ATTRIBUTE1);
2584 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE2',P_ZONE_DFF.ATTRIBUTE2);
2585 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE3',P_ZONE_DFF.ATTRIBUTE3);
2586 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE4',P_ZONE_DFF.ATTRIBUTE4);
2587 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE5',P_ZONE_DFF.ATTRIBUTE5);
2588 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE6',P_ZONE_DFF.ATTRIBUTE6);
2589 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE7',P_ZONE_DFF.ATTRIBUTE7);
2590 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE8',P_ZONE_DFF.ATTRIBUTE8);
2591 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE9',P_ZONE_DFF.ATTRIBUTE9);
2592 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE10',P_ZONE_DFF.ATTRIBUTE10);
2593 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE11',P_ZONE_DFF.ATTRIBUTE11);
2594 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE12',P_ZONE_DFF.ATTRIBUTE12);
2595 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE13',P_ZONE_DFF.ATTRIBUTE13);
2596 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE14',P_ZONE_DFF.ATTRIBUTE14);
2597 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE15',P_ZONE_DFF.ATTRIBUTE15);
2598 END IF;
2599 --
2600 Update_Zone (
2601 p_insert_type => p_insert_type,
2602 p_zone_id => p_zone_id,
2603 p_zone_name => p_zone_name,
2604 p_zone_level => p_zone_level,
2605 p_zone_type => 10,
2606 p_lang_code => p_lang_code,
2607 p_user_id => p_user_id,
2608 p_zone_dff => p_zone_dff,
2609 x_zone_id => x_zone_id,
2610 x_status => x_status,
2611 x_error_msg => x_error_msg,
2612 p_deconsol_location_id => p_deconsol_location_id);
2613
2614 --
2615 -- Debug Statements
2616 --
2617 IF l_debug_on THEN
2618 WSH_DEBUG_SV.pop(l_module_name);
2619 END IF;
2620 --
2621 END;
2622
2623
2624 --
2625 -- Procedure: Update_Zone
2626 --
2627 -- Purpose: Updates or inserts a new zone
2628 --
2629
2630 PROCEDURE Update_Zone (
2631 p_insert_type IN VARCHAR2,
2632 p_zone_id IN NUMBER,
2633 p_zone_name IN VARCHAR2,
2634 p_zone_level IN NUMBER,
2635 p_zone_type IN NUMBER,
2636 p_lang_code IN VARCHAR2,
2637 p_user_id IN NUMBER,
2638 p_zone_dff IN REGION_DFF_REC DEFAULT NULL,
2639 x_zone_id OUT NOCOPY NUMBER,
2640 x_status OUT NOCOPY NUMBER,
2641 x_error_msg OUT NOCOPY VARCHAR2,
2642 p_deconsol_location_id IN NUMBER DEFAULT NULL) IS
2643
2644 CURSOR check_zone IS
2645 SELECT region_id
2646 FROM wsh_regions_tl
2647 WHERE zone = p_zone_name AND
2648 language = p_lang_code;
2649
2650 l_zone_id NUMBER;
2651
2652 --
2653 l_debug_on BOOLEAN;
2654 --
2655 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ZONE';
2656 --
2657 BEGIN
2658
2659 --
2660 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2661 --
2662 IF l_debug_on IS NULL
2663 THEN
2664 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2665 END IF;
2666 --
2667 --
2668 -- Debug Statements
2669 --
2670 IF l_debug_on THEN
2671 WSH_DEBUG_SV.push(l_module_name);
2672 --
2673 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
2674 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_ID',P_ZONE_ID);
2675 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_NAME',P_ZONE_NAME);
2676 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_LEVEL',P_ZONE_LEVEL);
2677 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_TYPE',P_ZONE_TYPE);
2678 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2679 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
2680 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE_CATEGORY',P_ZONE_DFF.ATTRIBUTE_CATEGORY);
2681 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE1',P_ZONE_DFF.ATTRIBUTE1);
2682 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE2',P_ZONE_DFF.ATTRIBUTE2);
2683 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE3',P_ZONE_DFF.ATTRIBUTE3);
2684 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE4',P_ZONE_DFF.ATTRIBUTE4);
2685 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE5',P_ZONE_DFF.ATTRIBUTE5);
2686 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE6',P_ZONE_DFF.ATTRIBUTE6);
2687 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE7',P_ZONE_DFF.ATTRIBUTE7);
2688 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE8',P_ZONE_DFF.ATTRIBUTE8);
2689 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE9',P_ZONE_DFF.ATTRIBUTE9);
2690 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE10',P_ZONE_DFF.ATTRIBUTE10);
2691 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE11',P_ZONE_DFF.ATTRIBUTE11);
2692 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE12',P_ZONE_DFF.ATTRIBUTE12);
2693 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE13',P_ZONE_DFF.ATTRIBUTE13);
2694 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE14',P_ZONE_DFF.ATTRIBUTE14);
2695 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_DFF.ATTRIBUTE15',P_ZONE_DFF.ATTRIBUTE15);
2696 WSH_DEBUG_SV.log(l_module_name, 'P_DECONSOL_LOCATION_ID', P_DECONSOL_LOCATION_ID);
2697
2698 END IF;
2699 --
2700 OPEN check_zone;
2701 FETCH check_zone INTO l_zone_id;
2702 CLOSE check_zone;
2703
2704 IF (p_insert_type IN ('ADD','INSERT')) THEN
2705
2706 IF (l_zone_id <> 0) THEN
2707 x_status := 2;
2708 x_error_msg := 'WSH_CAT_ZONE_EXISTS';
2709 --
2710 -- Debug Statements
2711 --
2712 IF l_debug_on THEN
2713 WSH_DEBUG_SV.pop(l_module_name);
2714 END IF;
2715 --
2716 RETURN;
2717 END IF;
2718
2719 INSERT INTO WSH_REGIONS (
2720 REGION_ID,
2721 REGION_TYPE,
2722 PARENT_REGION_ID,
2723 ZONE_LEVEL,
2724 ATTRIBUTE_CATEGORY,
2725 ATTRIBUTE1,
2726 ATTRIBUTE2,
2727 ATTRIBUTE3,
2728 ATTRIBUTE4,
2729 ATTRIBUTE5,
2730 ATTRIBUTE6,
2731 ATTRIBUTE7,
2732 ATTRIBUTE8,
2733 ATTRIBUTE9,
2734 ATTRIBUTE10,
2735 ATTRIBUTE11,
2736 ATTRIBUTE12,
2737 ATTRIBUTE13,
2738 ATTRIBUTE14,
2739 ATTRIBUTE15,
2740 CREATED_BY,
2741 CREATION_DATE,
2742 LAST_UPDATED_BY,
2743 LAST_UPDATE_DATE,
2744 LAST_UPDATE_LOGIN,
2745 DECONSOL_LOCATION_ID)
2746 VALUES (
2747 wsh_regions_s.nextval,
2748 p_zone_type,
2749 -1,
2750 p_zone_level,
2751 p_zone_dff.attribute_category,
2752 p_zone_dff.attribute1,
2753 p_zone_dff.attribute2,
2754 p_zone_dff.attribute3,
2755 p_zone_dff.attribute4,
2756 p_zone_dff.attribute5,
2757 p_zone_dff.attribute6,
2758 p_zone_dff.attribute7,
2759 p_zone_dff.attribute8,
2760 p_zone_dff.attribute9,
2761 p_zone_dff.attribute10,
2762 p_zone_dff.attribute11,
2763 p_zone_dff.attribute12,
2764 p_zone_dff.attribute13,
2765 p_zone_dff.attribute14,
2766 p_zone_dff.attribute15,
2767 p_user_id,
2768 sysdate,
2769 p_user_id,
2770 sysdate,
2771 p_user_id,
2772 p_deconsol_location_id)
2773 RETURNING region_id
2774 INTO l_zone_id;
2775
2776 INSERT INTO WSH_REGIONS_TL (
2777 LANGUAGE,
2778 REGION_ID,
2779 ZONE,
2780 CREATED_BY,
2781 CREATION_DATE,
2782 LAST_UPDATED_BY,
2783 LAST_UPDATE_DATE,
2784 LAST_UPDATE_LOGIN)
2785 VALUES (
2786 p_lang_code,
2787 l_zone_id,
2788 p_zone_name,
2789 p_user_id,
2790 sysdate,
2791 p_user_id,
2792 sysdate,
2793 p_user_id);
2794 ELSE -- for update
2795
2796 IF (l_zone_id >= 0 AND l_zone_id <> p_zone_id) THEN
2797 x_status := 2;
2798 x_error_msg := 'WSH_CAT_ZONE_EXISTS';
2799 --
2800 -- Debug Statements
2801 --
2802 IF l_debug_on THEN
2803 WSH_DEBUG_SV.pop(l_module_name);
2804 END IF;
2805 --
2806 RETURN;
2807 END IF;
2808
2809 UPDATE wsh_regions
2810 SET zone_level = p_zone_level,
2811 attribute_category = p_zone_dff.attribute_category,
2812 attribute1 = p_zone_dff.attribute1,
2813 attribute2 = p_zone_dff.attribute2,
2814 attribute3 = p_zone_dff.attribute3,
2815 attribute4 = p_zone_dff.attribute4,
2816 attribute5 = p_zone_dff.attribute5,
2817 attribute6 = p_zone_dff.attribute6,
2818 attribute7 = p_zone_dff.attribute7,
2819 attribute8 = p_zone_dff.attribute8,
2820 attribute9 = p_zone_dff.attribute9,
2821 attribute10 = p_zone_dff.attribute10,
2822 attribute11 = p_zone_dff.attribute11,
2823 attribute12 = p_zone_dff.attribute12,
2824 attribute13 = p_zone_dff.attribute13,
2825 attribute14 = p_zone_dff.attribute14,
2826 attribute15 = p_zone_dff.attribute15,
2827 last_updated_by = p_user_id,
2828 last_update_date = sysdate,
2829 deconsol_location_id = p_deconsol_location_id
2830 WHERE region_id = p_zone_id;
2831
2832 UPDATE wsh_regions_tl
2833 SET zone = p_zone_name,
2834 --language = p_lang_code,--bug 8513956 moved language in where clause
2835 last_updated_by = p_user_id,
2836 last_update_date = sysdate
2837 WHERE region_id = p_zone_id
2838 AND language = p_lang_code;--bug 8513956
2839
2840 l_zone_id := p_zone_id;
2841
2842 END IF;
2843
2844 x_zone_id := l_zone_id;
2845
2846 --
2847 -- Debug Statements
2848 --
2849 IF l_debug_on THEN
2850 WSH_DEBUG_SV.pop(l_module_name);
2851 END IF;
2852 --
2853 END Update_Zone;
2854
2855 --
2856 -- Procedure: Lock_Zone
2857 -- Parameters: p_zone_id - zone_id for zone to be locked
2858 -- x_return_status - Status of procedure call
2859 -- Description: This procedure will lock a zone record. It is
2860 -- specifically designed for use by the form.
2861 --
2862
2863 PROCEDURE Lock_Zone
2864 (p_zone_id IN NUMBER,
2865 p_lang_code IN VARCHAR2,
2866 p_zone_name IN VARCHAR2,
2867 p_zone_level IN VARCHAR2,
2868 x_status OUT NOCOPY NUMBER) IS
2869
2870 CURSOR lock_row IS
2871 SELECT w.region_id, t.zone, w.zone_level
2872 FROM wsh_regions w, wsh_regions_tl t
2873 WHERE w.region_id = t.region_id
2874 AND t.language = p_lang_code
2875 FOR UPDATE OF w.region_id, t.region_id NOWAIT;
2876
2877 Recinfo lock_row%ROWTYPE;
2878
2879 --
2880 l_debug_on BOOLEAN;
2881 --
2882 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ZONE';
2883 --
2884 BEGIN
2885
2886 --
2887 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2888 --
2889 IF l_debug_on IS NULL
2890 THEN
2891 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2892 END IF;
2893 --
2894 --
2895 -- Debug Statements
2896 --
2897 IF l_debug_on THEN
2898 WSH_DEBUG_SV.push(l_module_name);
2899 --
2900 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_ID',P_ZONE_ID);
2901 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2902 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_NAME',P_ZONE_NAME);
2903 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_LEVEL',P_ZONE_LEVEL);
2904 END IF;
2905 --
2906 OPEN lock_row;
2907 FETCH lock_row INTO Recinfo;
2908
2909 IF (lock_row%NOTFOUND) THEN
2910 CLOSE lock_row;
2911 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
2912 app_exception.raise_exception;
2913 END IF;
2914
2915 CLOSE lock_row;
2916
2917 IF (
2918 (Recinfo.region_id = p_zone_id)
2919 AND (Recinfo.zone = p_zone_name)
2920 AND ( (Recinfo.zone_level = p_zone_level)
2921 OR ( (Recinfo.zone_level IS NULL) AND (p_zone_level IS NULL)))
2922 ) THEN
2923 --
2924 -- Debug Statements
2925 --
2926 IF l_debug_on THEN
2927 WSH_DEBUG_SV.pop(l_module_name);
2928 END IF;
2929 --
2930 RETURN;
2931 ELSE
2932 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2933 app_exception.raise_exception;
2934 END IF;
2935
2936 --
2937 -- Debug Statements
2938 --
2939 IF l_debug_on THEN
2940 WSH_DEBUG_SV.pop(l_module_name);
2941 END IF;
2942 --
2943 EXCEPTION
2944 WHEN others THEN
2945 if (lock_row%ISOPEN) then
2946 close lock_row;
2947 end if;
2948
2949 --
2950 -- Debug Statements
2951 --
2952 IF l_debug_on THEN
2953 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2954 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2955 END IF;
2956 --
2957 raise;
2958
2959 --
2960 -- Debug Statements
2961 --
2962 IF l_debug_on THEN
2963 WSH_DEBUG_SV.pop(l_module_name);
2964 END IF;
2965 --
2966 END Lock_Zone;
2967
2968 --
2969 -- Procedure: Update_Zone_Region
2970 --
2971 -- Purpose: Updates or inserts a new zone region
2972 -- Call another Update_Zone_Region with default p_zone_type='10'
2973
2974 PROCEDURE Update_Zone_Region (
2975 p_insert_type IN VARCHAR2,
2976 p_zone_region_id IN NUMBER,
2977 p_zone_id IN NUMBER,
2978 p_country IN VARCHAR2,
2979 p_state IN VARCHAR2,
2980 p_city IN VARCHAR2,
2981 p_postal_code_from IN VARCHAR2,
2982 p_postal_code_to IN VARCHAR2,
2983 p_lang_code IN VARCHAR2,
2984 p_country_code IN VARCHAR2,
2985 p_state_code IN VARCHAR2,
2986 p_city_code IN VARCHAR2,
2987 p_user_id IN NUMBER,
2988 x_zone_region_id OUT NOCOPY NUMBER,
2989 x_region_id OUT NOCOPY NUMBER,
2990 x_status OUT NOCOPY NUMBER,
2991 x_error_msg OUT NOCOPY VARCHAR2) IS
2992 --
2993 l_debug_on BOOLEAN;
2994 --
2995 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ZONE_REGION';
2996 --
2997 BEGIN
2998 --
2999 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3000 --
3001 IF l_debug_on IS NULL
3002 THEN
3003 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3004 END IF;
3005 --
3006 --
3007 -- Debug Statements
3008 --
3009 IF l_debug_on THEN
3010 WSH_DEBUG_SV.push(l_module_name);
3011 --
3012 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
3013 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_REGION_ID',P_ZONE_REGION_ID);
3014 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_ID',P_ZONE_ID);
3015 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
3016 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
3017 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
3018 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
3019 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
3020 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
3021 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
3022 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
3023 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
3024 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
3025 END IF;
3026 --
3027 Update_Zone_Region(
3028 p_insert_type => p_insert_type,
3029 p_zone_region_id => p_zone_region_id,
3030 p_zone_id => p_zone_id,
3031 p_region_id => null,
3032 p_country => p_country,
3033 p_state => p_state,
3034 p_city => p_city,
3035 p_postal_code_from => p_postal_code_from,
3036 p_postal_code_to => p_postal_code_to,
3037 p_lang_code => p_lang_code,
3038 p_country_code => p_country_code,
3039 p_state_code => p_state_code,
3040 p_city_code => p_city_code,
3041 p_user_id => p_user_id,
3042 p_zone_type => '10',
3043 x_zone_region_id => x_zone_region_id,
3044 x_region_id => x_region_id,
3045 x_status => x_status,
3046 x_error_msg => x_error_msg);
3047
3048 --
3049 -- Debug Statements
3050 --
3051 IF l_debug_on THEN
3052 WSH_DEBUG_SV.pop(l_module_name);
3053 END IF;
3054 --
3055 END Update_Zone_Region;
3056
3057 --
3058 -- Procedure: Update_Zone_Region
3059 --
3060 -- Purpose: Updates or inserts a new zone region
3061 --
3062
3063 PROCEDURE Update_Zone_Region (
3064 p_insert_type IN VARCHAR2,
3065 p_zone_region_id IN NUMBER,
3066 p_zone_id IN NUMBER,
3067 p_region_id IN NUMBER,
3068 p_country IN VARCHAR2,
3069 p_state IN VARCHAR2,
3070 p_city IN VARCHAR2,
3071 p_postal_code_from IN VARCHAR2,
3072 p_postal_code_to IN VARCHAR2,
3073 p_lang_code IN VARCHAR2,
3074 p_country_code IN VARCHAR2,
3075 p_state_code IN VARCHAR2,
3076 p_city_code IN VARCHAR2,
3077 p_user_id IN NUMBER,
3078 p_zone_type IN VARCHAR2,
3079 x_zone_region_id OUT NOCOPY NUMBER,
3080 x_region_id OUT NOCOPY NUMBER,
3081 x_status OUT NOCOPY NUMBER,
3082 x_error_msg OUT NOCOPY VARCHAR2) IS
3083
3084 CURSOR check_regions_in_zone(l_region_id NUMBER, l_codeFrom VARCHAR2, l_codeTo VARCHAR2) IS
3085 SELECT region_id
3086 FROM wsh_zone_regions z
3087 WHERE z.region_id in (
3088 SELECT region_id
3089 FROM wsh_regions
3090 START WITH region_id = l_region_id
3091 CONNECT BY PRIOR parent_region_id = region_id) AND
3092 z.parent_region_id = p_zone_id
3093 and ( nvl(l_codeFrom,'0') between nvl(z.postal_code_from,'0') and nvl(z.postal_code_to,'ZZZZZZZZZZZZZ')
3094 or nvl(l_codeTo,'0') between nvl(z.postal_code_from,'0') and nvl(z.postal_code_to,'ZZZZZZZZZZZZZZ')
3095 or nvl(z.postal_code_from,'0') between nvl(l_codeFrom,'0') and nvl(l_codeTo,'ZZZZZZZZZZZZ'));
3096
3097 CURSOR check_regions_in_zone_down(l_region_id NUMBER) IS
3098 SELECT region_id
3099 FROM wsh_regions r
3100 WHERE region_id = l_region_id
3101 START WITH r.region_id in (
3102 SELECT region_id
3103 FROM wsh_zone_regions
3104 WHERE parent_region_id = p_zone_id)
3105 CONNECT BY PRIOR parent_region_id = region_id;
3106
3107 CURSOR check_same_region_in_zone(l_region_id NUMBER, l_codeFrom VARCHAR2, l_codeTo VARCHAR2) IS
3108 SELECT region_id
3109 FROM wsh_zone_regions z
3110 WHERE z.region_id in (
3111 SELECT region_id
3112 FROM wsh_regions
3113 START WITH region_id = l_region_id
3114 CONNECT BY PRIOR parent_region_id = region_id) AND
3115 z.parent_region_id = p_zone_id AND
3116 l_codeFrom = z.postal_code_from AND
3117 l_codeTo = z.postal_code_to;
3118
3119 CURSOR get_zone_level IS
3120 SELECT zone_level
3121 FROM wsh_regions
3122 WHERE region_id = p_zone_id;
3123
3124 CURSOR get_region_type IS
3125 SELECT region_type
3126 FROM wsh_regions
3127 WHERE region_id = p_region_id;
3128
3129 l_region_info wsh_regions_search_pkg.region_rec;
3130 l_existing_region_id NUMBER;
3131 l_zone_level NUMBER;
3132 l_region_count NUMBER;
3133
3134 l_region_id NUMBER;
3135 l_region_type NUMBER;
3136
3137 --
3138 l_debug_on BOOLEAN;
3139 --
3140 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ZONE_REGION';
3141 --
3142 BEGIN
3143
3144 --
3145 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3146 --
3147 IF l_debug_on IS NULL
3148 THEN
3149 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3150 END IF;
3151 --
3152 --
3153 -- Debug Statements
3154 --
3155 IF l_debug_on THEN
3156 WSH_DEBUG_SV.push(l_module_name);
3157 --
3158 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
3159 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_REGION_ID',P_ZONE_REGION_ID);
3160 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_ID',P_ZONE_ID);
3161 WSH_DEBUG_SV.log(l_module_name,'P_REGION_ID',P_REGION_ID);
3162 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
3163 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
3164 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
3165 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
3166 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
3167 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
3168 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
3169 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
3170 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
3171 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
3172 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_TYPE',P_ZONE_TYPE);
3173 END IF;
3174 --
3175 IF (p_zone_type = '10')
3176 THEN
3177 IF (p_region_id is null OR p_region_id = -1)
3178 THEN
3179 --
3180 -- Debug Statements
3181 --
3182 IF l_debug_on THEN
3183 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
3184 END IF;
3185 --
3186 Wsh_Regions_Search_Pkg.Get_Region_Info(
3187 p_country => p_country,
3188 p_country_region => null,
3189 p_state => p_state,
3190 p_city => p_city,
3191 p_postal_code_from => p_postal_code_from,
3192 p_postal_code_to => p_postal_code_to,
3193 p_zone => null,
3194 p_lang_code => p_lang_code,
3195 p_country_code => p_country_code,
3196 p_country_region_code => null,
3197 p_state_code => p_state_code,
3198 p_city_code => p_city_code,
3199 p_region_type => null,
3200 p_interface_flag => 'N',
3201 p_search_flag => 'Y',
3202 x_region_info => l_region_info);
3203
3204 l_region_id := l_region_info.region_id;
3205 l_region_type := l_region_info.region_type;
3206
3207 ELSE -- already have region_id, need to get region_type
3208
3209 OPEN get_region_type;
3210 FETCH get_region_type INTO l_region_type;
3211 CLOSE get_region_type;
3212
3213 l_region_id := p_region_id;
3214
3215 END IF;
3216
3217 ELSIF (p_zone_type = '11')
3218 THEN
3219 -- Bug 2418745
3220 -- For the zone of rating zone chart,
3221 -- Region_Id of the included postal code region should be of Country.
3222 -- If it is of State or city, the Parcel Carrier lanes can't be searched
3223 -- with postal codes.
3224
3225 --
3226 -- Debug Statements
3227 --
3228 IF l_debug_on THEN
3229 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
3230 END IF;
3231 --
3232 Wsh_Regions_Search_Pkg.Get_Region_Info(
3233 p_country => p_country,
3234 p_country_region => null,
3235 p_state => null,
3236 p_city => null,
3237 p_postal_code_from => null,
3238 p_postal_code_to => null,
3239 p_zone => null,
3240 p_lang_code => p_lang_code,
3241 p_country_code => p_country_code,
3242 p_country_region_code => null,
3243 p_state_code => null,
3244 p_city_code => null,
3245 p_region_type => 0,
3246 p_interface_flag => 'N',
3247 x_region_info => l_region_info);
3248
3249 l_region_id := l_region_info.region_id;
3250 l_region_type := l_region_info.region_type;
3251
3252 END IF;
3253
3254 x_region_id := l_region_id;
3255
3256 IF (l_region_id <= 0) THEN
3257
3258 x_status := 1;
3259 x_error_msg := 'WSH_REGION_NOT_FOUND';
3260 --
3261 -- Debug Statements
3262 --
3263 IF l_debug_on THEN
3264 WSH_DEBUG_SV.pop(l_module_name);
3265 END IF;
3266 --
3267 RETURN;
3268 END IF;
3269
3270 IF (p_zone_region_id > 0 AND p_insert_type = 'DELETE') THEN
3271
3272 SELECT count(*)
3273 INTO l_region_count
3274 FROM wsh_zone_regions
3275 WHERE parent_region_id = p_zone_id;
3276
3277 IF (l_region_count = 1) THEN
3278
3279 x_status := 1;
3280 x_error_msg := 'WSH_ZONE_NO_REGIONS';
3281 --
3282 -- Debug Statements
3283 --
3284 IF l_debug_on THEN
3285 WSH_DEBUG_SV.pop(l_module_name);
3286 END IF;
3287 --
3288 RETURN;
3289 END IF;
3290
3291 DELETE FROM wsh_zone_regions
3292 WHERE zone_region_id = p_zone_region_id;
3293
3294 --
3295 -- Debug Statements
3296 --
3297 IF l_debug_on THEN
3298 WSH_DEBUG_SV.pop(l_module_name);
3299 END IF;
3300 --
3301 RETURN;
3302
3303 END IF;
3304
3305 -- check to make sure this region and parents of this region
3306 -- are not already in this zone
3307
3308 OPEN check_regions_in_zone(l_region_id, p_postal_code_from, p_postal_code_to);
3309 FETCH check_regions_in_zone INTO l_existing_region_id;
3310 CLOSE check_regions_in_zone;
3311
3312 IF (l_existing_region_id IS NULL AND p_postal_code_from IS NULL) THEN
3313
3314 OPEN check_regions_in_zone_down(l_region_id);
3315 FETCH check_regions_in_zone_down INTO l_existing_region_id;
3316 CLOSE check_regions_in_zone_down;
3317
3318 IF (l_existing_region_id > 0) THEN
3319 x_status := 1;
3320 x_error_msg := 'WSH_REGION_EXISTS_IN_ZONE';
3321 --
3322 -- Debug Statements
3323 --
3324 IF l_debug_on THEN
3325 WSH_DEBUG_SV.pop(l_module_name);
3326 END IF;
3327 --
3328 RETURN;
3329 END IF;
3330
3331 END IF;
3332
3333 IF (l_existing_region_id >= 0) THEN
3334 x_status := 1;
3335 x_error_msg := 'WSH_REGION_EXISTS_IN_ZONE';
3336
3337 -- check if the region is exactly same with the existing one
3338 -- if it's the case, the error will be ignored in ZoneLoader
3339 -- to let the user load the same file without error (SYNC)
3340
3341 IF (p_postal_code_from IS NOT NULL) THEN
3342 l_existing_region_id := -1;
3343 OPEN check_same_region_in_zone(l_region_id,p_postal_code_from,p_postal_code_to );
3344 FETCH check_same_region_in_zone INTO l_existing_region_id;
3345 CLOSE check_same_region_in_zone;
3346
3347 IF (l_existing_region_id >= 0) THEN
3348 x_status := 1;
3349 x_error_msg := 'WSH_SAME_REGION_IN_ZONE';
3350 END IF;
3351 END IF;
3352
3353 --
3354 -- Debug Statements
3355 --
3356 IF l_debug_on THEN
3357 WSH_DEBUG_SV.pop(l_module_name);
3358 END IF;
3359 --
3360 RETURN;
3361 END IF;
3362
3363 IF (p_insert_type IN ('ADD','INSERT')) THEN
3364 INSERT INTO WSH_ZONE_REGIONS (
3365 ZONE_REGION_ID,
3366 REGION_ID,
3367 PARENT_REGION_ID,
3368 POSTAL_CODE_FROM,
3369 POSTAL_CODE_TO,
3370 CREATED_BY,
3371 CREATION_DATE,
3372 LAST_UPDATED_BY,
3373 LAST_UPDATE_DATE,
3374 LAST_UPDATE_LOGIN,
3375 ZONE_FLAG,
3376 PARTY_ID)
3377 VALUES (
3378 wsh_zone_regions_s.nextval,
3379 l_region_id,
3380 p_zone_id,
3381 p_postal_code_from,
3382 p_postal_code_to,
3383 p_user_id,
3384 sysdate,
3385 p_user_id,
3386 sysdate,
3387 p_user_id,
3388 'Y',
3389 -1)
3390 RETURNING zone_region_id
3391 INTO x_zone_region_id;
3392
3393 IF (x_zone_region_id IS NOT NULL) THEN
3394
3395 OPEN get_zone_level;
3396 FETCH get_zone_level INTO l_zone_level;
3397 CLOSE get_zone_level;
3398
3399 IF (l_region_type < nvl(l_zone_level,10000)) THEN
3400
3401 UPDATE wsh_regions
3402 SET zone_level = l_region_type
3403 WHERE region_id = p_zone_id;
3404
3405 END IF;
3406
3407 END IF;
3408
3409 ELSIF (p_zone_region_id > 0 AND p_insert_type IN ('UPDATE','SYNC')) THEN
3410
3411 UPDATE wsh_zone_regions
3412 SET region_id = l_region_id,
3413 postal_code_from = p_postal_code_from,
3414 postal_code_to = p_postal_code_to,
3415 last_updated_by = p_user_id,
3416 last_update_date = sysdate
3417 WHERE zone_region_id = p_zone_region_id;
3418
3419 END IF;
3420
3421 --
3422 -- Debug Statements
3423 --
3424 IF l_debug_on THEN
3425 WSH_DEBUG_SV.pop(l_module_name);
3426 END IF;
3427 --
3428 END Update_Zone_Region;
3429
3430 --
3431 -- Procedure: Lock_Zone_Region
3432 -- Parameters: p_zone_region_id - zone_region_id for zone region to be locked
3433 -- p_zone_id - zone id
3434 -- p_region_id - zone component region id
3435 -- x_return_status - Status of procedure call
3436 -- Description: This procedure will lock a zone component record. It is
3437 -- specifically designed for use by the form.
3438 --
3439
3440 PROCEDURE Lock_Zone_Region
3441 (p_zone_region_id IN NUMBER,
3442 p_zone_id IN NUMBER,
3443 p_region_id IN NUMBER,
3444 x_status OUT NOCOPY NUMBER) IS
3445
3446 CURSOR lock_row IS
3447 SELECT zone_region_id, parent_region_id , region_id
3448 FROM wsh_zone_regions
3449 WHERE zone_region_id = p_zone_region_id
3450 FOR UPDATE OF zone_region_id NOWAIT;
3451
3452 Recinfo lock_row%ROWTYPE;
3453
3454 --
3455 l_debug_on BOOLEAN;
3456 --
3457 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ZONE_REGION';
3458 --
3459 BEGIN
3460
3461 --
3462 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3463 --
3464 IF l_debug_on IS NULL
3465 THEN
3466 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3467 END IF;
3468 --
3469 --
3470 -- Debug Statements
3471 --
3472 IF l_debug_on THEN
3473 WSH_DEBUG_SV.push(l_module_name);
3474 --
3475 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_REGION_ID',P_ZONE_REGION_ID);
3476 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_ID',P_ZONE_ID);
3477 WSH_DEBUG_SV.log(l_module_name,'P_REGION_ID',P_REGION_ID);
3478 END IF;
3479 --
3480 OPEN lock_row;
3481 FETCH lock_row INTO Recinfo;
3482
3483 IF (lock_row%NOTFOUND) THEN
3484 CLOSE lock_row;
3485 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
3486 app_exception.raise_exception;
3487 END IF;
3488
3489 CLOSE lock_row;
3490
3491 IF (
3492 (Recinfo.zone_region_id = p_zone_region_id)
3493 AND (Recinfo.parent_region_id = p_zone_id)
3494 AND (Recinfo.region_id = p_region_id)
3495 ) THEN
3496 --
3497 -- Debug Statements
3498 --
3499 IF l_debug_on THEN
3500 WSH_DEBUG_SV.pop(l_module_name);
3501 END IF;
3502 --
3503 RETURN;
3504 ELSE
3505 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
3506 app_exception.raise_exception;
3507 END IF;
3508
3509 --
3510 -- Debug Statements
3511 --
3512 IF l_debug_on THEN
3513 WSH_DEBUG_SV.pop(l_module_name);
3514 END IF;
3515 --
3516 EXCEPTION
3517 WHEN others THEN
3518 if (lock_row%ISOPEN) then
3519 close lock_row;
3520 end if;
3521
3522 --
3523 -- Debug Statements
3524 --
3525 IF l_debug_on THEN
3526 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3527 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3528 END IF;
3529 --
3530 raise;
3531
3532 --
3533 -- Debug Statements
3534 --
3535 IF l_debug_on THEN
3536 WSH_DEBUG_SV.pop(l_module_name);
3537 END IF;
3538 --
3539 END Lock_Zone_Region;
3540
3541
3542 --
3543 -- Procedure: Load_Region
3544 --
3545 -- Purpose: Loads the region information into interface tables
3546 -- without any validation.
3547 --
3548
3549 PROCEDURE Load_Region (
3550 p_country_code IN VARCHAR2,
3551 p_country_region_code IN VARCHAR2,
3552 p_state_code IN VARCHAR2,
3553 p_city_code IN VARCHAR2,
3554 p_port_flag IN VARCHAR2,
3555 p_airport_flag IN VARCHAR2,
3556 p_road_terminal_flag IN VARCHAR2,
3557 p_rail_terminal_flag IN VARCHAR2,
3558 p_longitude IN NUMBER,
3559 p_latitude IN NUMBER,
3560 p_timezone IN VARCHAR2,
3561 p_continent IN VARCHAR2,
3562 p_country IN VARCHAR2,
3563 p_country_region IN VARCHAR2,
3564 p_state IN VARCHAR2,
3565 p_city IN VARCHAR2,
3566 p_alternate_name IN VARCHAR2,
3567 p_county IN VARCHAR2,
3568 p_postal_code_from IN VARCHAR2,
3569 p_postal_code_to IN VARCHAR2,
3570 p_lang_code IN VARCHAR2,
3571 p_deconsol_location_id IN NUMBER DEFAULT NULL) IS
3572
3573 l_region_id NUMBER;
3574
3575 --
3576 l_debug_on BOOLEAN;
3577 --
3578 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOAD_REGION';
3579 --
3580 BEGIN
3581
3582 --
3583 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3584 --
3585 IF l_debug_on IS NULL
3586 THEN
3587 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3588 END IF;
3589 --
3590 --
3591 -- Debug Statements
3592 --
3593 IF l_debug_on THEN
3594 WSH_DEBUG_SV.push(l_module_name);
3595 --
3596 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
3597 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
3598 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
3599 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
3600 WSH_DEBUG_SV.log(l_module_name,'P_PORT_FLAG',P_PORT_FLAG);
3601 WSH_DEBUG_SV.log(l_module_name,'P_AIRPORT_FLAG',P_AIRPORT_FLAG);
3602 WSH_DEBUG_SV.log(l_module_name,'P_ROAD_TERMINAL_FLAG',P_ROAD_TERMINAL_FLAG);
3603 WSH_DEBUG_SV.log(l_module_name,'P_RAIL_TERMINAL_FLAG',P_RAIL_TERMINAL_FLAG);
3604 WSH_DEBUG_SV.log(l_module_name,'P_LONGITUDE',P_LONGITUDE);
3605 WSH_DEBUG_SV.log(l_module_name,'P_LATITUDE',P_LATITUDE);
3606 WSH_DEBUG_SV.log(l_module_name,'P_TIMEZONE',P_TIMEZONE);
3607 WSH_DEBUG_SV.log(l_module_name,'P_CONTINENT',P_CONTINENT);
3608 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
3609 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
3610 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
3611 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
3612 WSH_DEBUG_SV.log(l_module_name,'P_ALTERNATE_NAME',P_ALTERNATE_NAME);
3613 WSH_DEBUG_SV.log(l_module_name,'P_COUNTY',P_COUNTY);
3614 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
3615 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
3616 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
3617 WSH_DEBUG_SV.log(l_module_name, 'P_DECONSOL_LOCATION_ID', P_DECONSOL_LOCATION_ID);
3618 END IF;
3619 --
3620 Add_Region(
3621 p_country_code => p_country_code,
3622 p_country_region_code => p_country_region_code,
3623 p_state_code => p_state_code,
3624 p_city_code => p_city_code,
3625 p_port_flag => p_port_flag,
3626 p_airport_flag => p_airport_flag,
3627 p_road_terminal_flag => p_road_terminal_flag,
3628 p_rail_terminal_flag => p_rail_terminal_flag,
3629 p_longitude => p_longitude,
3630 p_latitude => p_latitude,
3631 p_timezone => p_timezone,
3632 p_continent => p_continent,
3633 p_country => p_country,
3634 p_country_region => p_country_region,
3635 p_state => p_state,
3636 p_city => p_city,
3637 p_alternate_name => p_alternate_name,
3638 p_county => p_county,
3639 p_postal_code_from => p_postal_code_from,
3640 p_postal_code_to => p_postal_code_to,
3641 p_lang_code => p_lang_code,
3642 p_region_type => null,
3643 p_parent_region_id => null,
3644 p_interface_flag => 'Y',
3645 p_tl_only_flag => 'N',
3646 p_region_id => null,
3647 x_region_id => l_region_id,
3648 p_deconsol_location_id => p_deconsol_location_id);
3649
3650 --
3651 -- Debug Statements
3652 --
3653 IF l_debug_on THEN
3654 WSH_DEBUG_SV.pop(l_module_name);
3655 END IF;
3656 --
3657 END Load_Region;
3658
3659
3660 --
3661 -- Procedure: Default_Regions
3662 --
3663 -- Purpose: Select all the entries from the interface tables and
3664 -- call Update_Region on each entry to do validation
3665 --
3666
3667 PROCEDURE Default_Regions (
3668 x_country IN VARCHAR2,
3669 x_country_code IN VARCHAR2,
3670 x_state IN VARCHAR2,
3671 x_state_code IN VARCHAR2,
3672 x_status OUT NOCOPY NUMBER,
3673 x_regions_processed OUT NOCOPY NUMBER,
3674 x_error_msg_text OUT NOCOPY VARCHAR2 )
3675 IS
3676 CURSOR get_all_regions IS
3677 SELECT R.REGION_ID,
3678 R.COUNTRY_CODE,
3679 R.COUNTRY_REGION_CODE,
3680 R.STATE_CODE,
3681 R.CITY_CODE,
3682 R.PORT_FLAG,
3683 R.AIRPORT_FLAG,
3684 R.ROAD_TERMINAL_FLAG,
3685 R.RAIL_TERMINAL_FLAG,
3686 R.LONGITUDE,
3687 R.LATITUDE,
3688 R.TIMEZONE,
3689 TL.CONTINENT,
3690 TL.COUNTRY,
3691 TL.COUNTRY_REGION,
3692 TL.STATE,
3693 TL.CITY,
3694 TL.ALTERNATE_NAME,
3695 TL.COUNTY,
3696 TL.POSTAL_CODE_FROM,
3697 TL.POSTAL_CODE_TO,
3698 TL.LANGUAGE
3699 FROM WSH_REGIONS_INTERFACE R,
3700 WSH_REGIONS_TL_INTERFACE TL
3701 WHERE R.REGION_ID = TL.REGION_ID
3702 AND TL.ZONE IS NULL -- We are not processing zones here .
3703 AND PROCESSED_FLAG is null
3704 AND nvl(TL.STATE,'NULL') = decode(x_state,NULL,nvl(TL.STATE,'NULL'),x_state)
3705 AND nvl(R.STATE_CODE,'NULL') = decode(x_state_code,NULL , nvl(R.STATE_CODE,'NULL'),x_state_code)
3706 AND TL.COUNTRY = nvl(x_country,TL.COUNTRY)
3707 AND R.COUNTRY_CODE = nvl(x_country_code, R.COUNTRY_CODE)
3708 ORDER BY TL.COUNTRY, R.COUNTRY_CODE,
3709 NVL(TL.STATE, 1), NVL(R.STATE_CODE, 1),
3710 NVL(TL.CITY, 1), NVL(R.CITY_CODE, 1),
3711 NVL(TL.POSTAL_CODE_FROM, 1);
3712
3713 Rec_Region get_all_regions%ROWTYPE;
3714
3715 l_region_id NUMBER;
3716 l_num_regions NUMBER;
3717 l_status NUMBER;
3718 l_error_msg VARCHAR2(200);
3719 l_regions_processed NUMBER;
3720
3721 l_region_id_rec tab_region_id;
3722 l_country_code_rec tab_country_code;
3723 l_country_region_code_rec tab_country_region_code;
3724 l_state_code_rec tab_state_code;
3725 l_city_code_rec tab_city_code;
3726 l_port_flag_rec tab_port_flag;
3727 l_airport_flag_rec tab_airport_flag;
3728 l_road_terminal_flag_rec tab_road_terminal_flag;
3729 l_rail_terminal_flag_rec tab_rail_terminal_flag;
3730 l_longitude_rec tab_longitude;
3731 l_latitude_rec tab_latitude;
3732 l_timezone_rec tab_timezone;
3733 l_continent_rec tab_continent;
3734 l_country_rec tab_country;
3735 l_country_region_rec tab_country_region;
3736 l_state_rec tab_state;
3737 l_city_rec tab_city;
3738 l_alternate_name_rec tab_alternate_name;
3739 l_county_rec tab_county;
3740 l_postal_code_from_rec tab_postal_code_from;
3741 l_postal_code_to_rec tab_postal_code_to;
3742 l_language_rec tab_language;
3743
3744 l_prev_country WSH_REGIONS_TL.Country%TYPE;
3745 l_prev_state WSH_REGIONS_TL.State%TYPE;
3746 l_prev_city WSH_REGIONS_TL.City%TYPE;
3747 l_prev_country_code WSH_REGIONS.Country_Code%Type;
3748 l_prev_state_code WSH_REGIONS.State_Code%Type;
3749 l_prev_city_code WSH_REGIONS.City_Code%Type;
3750
3751 -- Variables for Updating and Deleting regions from Interface table in Bulk
3752 l_upd_region_id WSH_UTIL_CORE.Id_Tab_Type;
3753 l_del_region_id WSH_UTIL_CORE.Id_Tab_Type;
3754 l_upd_count NUMBER DEFAULT 0;
3755 l_del_count NUMBER DEFAULT 0;
3756
3757 l_return_status VARCHAR2(1);
3758 l_country_flag VARCHAR2(1);
3759 l_state_flag VARCHAR2(1);
3760 l_city_flag VARCHAR2(1);
3761 t1 NUMBER;
3762
3763 --
3764 l_debug_on BOOLEAN;
3765 --
3766 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DEFAULT_REGIONS';
3767 --
3768 BEGIN
3769
3770 --
3771 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3772 --
3773 IF l_debug_on IS NULL
3774 THEN
3775 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3776 END IF;
3777 --
3778 --
3779 -- Debug Statements
3780 --
3781 IF l_debug_on THEN
3782 WSH_DEBUG_SV.push(l_module_name);
3783 END IF;
3784 --
3785 x_status := 0;
3786 l_regions_processed := 0;
3787
3788 -- populate previous values to some impossible data ... like G_MISS..something.
3789 l_prev_country := FND_API.G_MISS_CHAR;
3790 l_prev_state := FND_API.G_MISS_CHAR;
3791 l_prev_city := FND_API.G_MISS_CHAR;
3792 l_prev_country_code := FND_API.G_MISS_CHAR;
3793 l_prev_state_code := FND_API.G_MISS_CHAR;
3794 l_prev_city_code := FND_API.G_MISS_CHAR;
3795
3796 OPEN get_all_regions;
3797 LOOP
3798 -- Fetching regions in bulk from Interface table (limit is 1000).
3799 FETCH get_all_regions BULK COLLECT INTO
3800 l_region_id_rec,
3801 l_country_code_rec,
3802 l_country_region_code_rec,
3803 l_state_code_rec,
3804 l_city_code_rec,
3805 l_port_flag_rec,
3806 l_airport_flag_rec,
3807 l_road_terminal_flag_rec,
3808 l_rail_terminal_flag_rec,
3809 l_longitude_rec,
3810 l_latitude_rec,
3811 l_timezone_rec,
3812 l_continent_rec,
3813 l_country_rec,
3814 l_country_region_rec,
3815 l_state_rec,
3816 l_city_rec,
3817 l_alternate_name_rec,
3818 l_county_rec,
3819 l_postal_code_from_rec,
3820 l_postal_code_to_rec,
3821 l_language_rec
3822 LIMIT 1000;
3823
3824 t1 := dbms_utility.get_time;
3825
3826 FOR I IN 1..l_region_id_rec.COUNT
3827 LOOP
3828 /*
3829 -- Validation regarding missing parameters or wrong format
3830 -- Same validatin are in the When-Validate-Record trigger on the Region block
3831 -- In WSHRGZON.fmb form
3832 */
3833 --
3834 -- Debug Statements
3835 --
3836 IF l_debug_on THEN
3837 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.VALIDATE_REGION', WSH_DEBUG_SV.C_PROC_LEVEL);
3838 END IF;
3839 --
3840 Validate_Region( p_country => l_country_rec(i),
3841 p_state => l_state_rec(i),
3842 p_city => l_city_rec(i),
3843 p_country_code => l_country_code_rec(i),
3844 p_state_code => l_state_code_rec(i),
3845 p_city_code => l_city_code_rec(i),
3846 p_postal_code_from => l_postal_code_from_rec(i),
3847 p_postal_code_to => l_postal_code_to_rec(i),
3848 x_status => l_status,
3849 x_error_msg => l_error_msg);
3850
3851 IF ( l_status = 0 ) THEN -- { IF VALIDATION SUCCESS
3852 l_country_flag := 'N';
3853 l_state_flag := 'N';
3854 l_city_flag := 'N';
3855
3856 -- Compare current values with previous values
3857 -- Current Country/Country_Code value is different from previous value
3858 IF ( nvl(l_prev_country, '-1') <> nvl(l_country_rec(i), '-1') OR
3859 nvl(l_prev_country_code, '-1') <> nvl(l_country_code_rec(i), '-1') )
3860 THEN
3861 l_country_flag := 'Y';
3862 -- Populate details of country, state and city in Global temp table
3863 -- if city or postal code is not null
3864 IF ( l_city_rec(i) is not null OR
3865 l_postal_code_from_rec(i) is not null )
3866 THEN
3867 l_state_flag := 'Y';
3868 l_city_flag := 'Y';
3869 ELSIF ( l_state_rec(i) is not null )
3870 THEN
3871 -- Populate details of country and state in Global temp table
3872 -- if state is not null
3873 l_state_flag := 'Y';
3874 END IF;
3875 -- Current State/State_Code value is different from previous value
3876 ELSIF ( nvl(l_prev_state, '-1') <> nvl(l_state_rec(i), '-1') OR
3877 nvl(l_prev_state_code, '-1') <> nvl(l_state_code_rec(i), '-1') )
3878 THEN
3879 l_state_flag := 'Y';
3880 -- Populate details of state and city in Global temp table
3881 -- if city or postal code is not null
3882 IF ( l_city_rec(i) is not null OR
3883 l_postal_code_from_rec(i) is not null )
3884 THEN
3885 l_city_flag := 'Y';
3886 END IF;
3887 -- Current City/City_Code value is different from previous value
3888 ELSIF ( nvl(l_prev_city, '-1') <> nvl(l_city_rec(i), '-1') OR
3889 nvl(l_prev_city_code, '-1') <> nvl(l_city_code_rec(i), '-1') )
3890 THEN
3891 -- Populate details of city in Global temp table
3892 l_city_flag := 'Y';
3893 END IF;
3894
3895 IF ( l_country_flag = 'Y' OR
3896 l_state_flag = 'Y' OR
3897 l_city_flag = 'Y' )
3898 THEN
3899 --
3900 -- Debug Statements
3901 --
3902 IF l_debug_on THEN
3903 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.INIT_GLOBAL_TABLE', WSH_DEBUG_SV.C_PROC_LEVEL);
3904 END IF;
3905 --
3906 -- To popualte Global temp tables
3907 Init_Global_Table(
3908 p_country => l_country_rec(i),
3909 p_state => l_state_rec(i),
3910 p_city => l_city_rec(i),
3911 p_country_code => l_country_code_rec(i),
3912 p_state_code => l_state_code_rec(i),
3913 p_city_code => l_city_code_rec(i),
3914 p_country_flag => l_country_flag,
3915 p_state_flag => l_state_flag,
3916 p_city_flag => l_city_flag,
3917 x_return_status => l_return_status );
3918
3919 -- Error Handling Part
3920 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
3921 THEN
3922 l_status := 2;
3923 l_error_msg := 'WSH_UTIL_MESSAGE_U';
3924 END IF;
3925 END IF;
3926
3927 IF ( l_status = 0 )
3928 THEN
3929 -- Storing current value into temp variable
3930 l_prev_country := l_country_rec(i);
3931 l_prev_state := l_state_rec(i);
3932 l_prev_city := l_city_rec(i);
3933 l_prev_country_code := l_country_code_rec(i);
3934 l_prev_state_code := l_state_code_rec(i);
3935 l_prev_city_code := l_city_code_rec(i);
3936
3937 --
3938 -- Debug Statements
3939 --
3940 IF l_debug_on THEN
3941 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.UPDATE_REGION', WSH_DEBUG_SV.C_PROC_LEVEL);
3942 END IF;
3943
3944 Update_Region(
3945 p_insert_type => 'SYNC',
3946 p_region_id => -1,
3947 p_parent_region_id => -1,
3948 p_continent => l_continent_rec(i),
3949 p_country => l_country_rec(i),
3950 p_country_region => l_country_region_rec(i),
3951 p_state => l_state_rec(i),
3952 p_city => l_city_rec(i),
3953 p_alternate_name => l_alternate_name_rec(i),
3954 p_county => l_county_rec(i),
3955 p_postal_code_from => l_postal_code_from_rec(i),
3956 p_postal_code_to => l_postal_code_to_rec(i),
3957 p_lang_code => l_language_rec(i),
3958 p_country_code => l_country_code_rec(i),
3959 p_country_region_code => l_country_region_code_rec(i),
3960 p_state_code => l_state_code_rec(i),
3961 p_city_code => l_city_code_rec(i),
3962 p_port_flag => l_port_flag_rec(i),
3963 p_airport_flag => l_airport_flag_rec(i),
3964 p_road_terminal_flag => l_road_terminal_flag_rec(i),
3965 p_rail_terminal_flag => l_rail_terminal_flag_rec(i),
3966 p_longitude => l_longitude_rec(i),
3967 p_latitude => l_latitude_rec(i),
3968 p_timezone => l_timezone_rec(i),
3969 p_interface_flag => 'N',
3970 p_user_id => -1,
3971 p_insert_parent_flag => 'Y',
3972 x_region_id => l_region_id,
3973 x_status => l_status,
3974 x_error_msg => l_error_msg,
3975 p_conc_request_flag => 'Y'); -- p_conc_request_flag
3976
3977 END IF;
3978 END IF; -- } IF VALIDATION SUCCESS
3979
3980 IF (l_status = 2) THEN
3981 x_status := 2;
3982 x_error_msg_text := l_error_msg;
3983
3984 fnd_file.put_line(fnd_file.log,'ERROR processing region: ' || l_country_rec(i) ||', '|| l_state_rec(i) || ', ' || l_city_rec(i) || ', ' || l_postal_code_from_rec(i));
3985 fnd_file.put_line(fnd_file.log,' error message: '||fnd_message.get_string('WSH',l_error_msg));
3986
3987 -- Bulk Update has to be done after processing 1000 records
3988 l_upd_count := l_upd_count + 1;
3989 l_upd_region_id(l_upd_count) := l_region_id_rec(i);
3990 ELSE
3991 l_regions_processed := l_regions_processed + 1;
3992
3993 -- Bulk Delete has to be done after processing 1000 records
3994 l_del_count := l_del_count + 1;
3995 l_del_region_id(l_del_count) := l_region_id_rec(i);
3996 END IF;
3997 END LOOP;
3998
3999 -- Bulk Updation in WSH_REGIONS_INTERFACE table
4000 IF ( l_upd_count > 0 ) THEN
4001 l_upd_count := 0;
4002
4003 FORALL i in l_upd_region_id.first..l_upd_region_id.last
4004 UPDATE wsh_regions_interface
4005 SET processed_flag = 'Y'
4006 WHERE region_id = l_upd_region_id(i);
4007
4008 -- Deleting region ids from array
4009 l_upd_region_id.DELETE;
4010 END IF;
4011
4012 -- Bulk Deletion in WSH_REGIONS_INTERFACE table
4013 IF ( l_del_count > 0 ) THEN
4014 l_del_count := 0;
4015 FORALL i in l_del_region_id.first..l_del_region_id.last
4016 DELETE FROM WSH_REGIONS_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4017
4018 FORALL i in l_del_region_id.first..l_del_region_id.last
4019 DELETE FROM WSH_REGIONS_TL_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4020
4021 -- Deleting region ids from array
4022 l_del_region_id.DELETE;
4023 END IF;
4024
4025 fnd_file.put_line(fnd_file.log, 'TIME TAKEN FOR PROCESSING 1000 RECORDS : ' || ((dbms_utility.get_time - t1)/100));
4026
4027 COMMIT;
4028 EXIT WHEN get_all_regions%NOTFOUND;
4029 END LOOP;
4030
4031 CLOSE get_all_regions;
4032
4033 x_regions_processed := l_regions_processed;
4034
4035 -- Truncating records from Global Temp Tables
4036 delete from wsh_regions_global_data;
4037 delete from wsh_regions_global;
4038 COMMIT;
4039 EXCEPTION
4040 WHEN OTHERS THEN
4041 x_status := 2;
4042 x_error_msg_text := 'Error ' || sqlcode || ': ' || sqlerrm;
4043
4044 --
4045 -- Debug Statements
4046 --
4047 IF l_debug_on THEN
4048 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4049 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4050 END IF;
4051 --
4052 END Default_Regions;
4053
4054 --
4055 -- Procedure: Default_Regions (for concurrent program usage)
4056 --
4057 -- Purpose: Copies regions from the interface tables to
4058 -- the real regions tables
4059 --
4060
4061 PROCEDURE Default_Regions (
4062 errbuf OUT NOCOPY VARCHAR2,
4063 retcode OUT NOCOPY VARCHAR2,
4064 p_country_code IN VARCHAR2 DEFAULT NULL,
4065 p_state IN VARCHAR2 DEFAULT NULL)
4066 IS
4067 l_status NUMBER;
4068 l_regions_processed NUMBER;
4069 l_error_msg_text VARCHAR2(1000);
4070 l_dummy_country VARCHAR2(50);
4071 l_country_code VARCHAR2(3);
4072 l_state VARCHAR2(50);
4073 l_dummy_state_code VARCHAR2(3);
4074
4075 CURSOR total_regions IS
4076 SELECT count(*)
4077 FROM wsh_Regions_interface
4078 WHERE processed_flag is null;
4079
4080 CURSOR more_regions IS
4081 SELECT 1
4082 FROM wsh_Regions_interface
4083 WHERE processed_flag is null;
4084
4085 l_tmp NUMBER;
4086 l_good_count NUMBER := 0;
4087 l_bad_count NUMBER := 0;
4088 l_total_regions NUMBER;
4089
4090 --
4091 l_debug_on BOOLEAN;
4092 --
4093 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DEFAULT_REGIONS';
4094 --
4095 BEGIN
4096
4097 --
4098 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4099 --
4100 IF l_debug_on IS NULL
4101 THEN
4102 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4103 END IF;
4104 --
4105 --
4106 -- Debug Statements
4107 --
4108 IF l_debug_on THEN
4109 WSH_DEBUG_SV.push(l_module_name);
4110 --
4111 WSH_DEBUG_SV.log(l_module_name,'P_Country_code',P_COUNTRY_CODE);
4112 WSH_DEBUG_SV.log(l_module_name,'P_State',P_STATE);
4113 END IF;
4114 --
4115 l_country_code := p_country_code;
4116 l_state := p_state;
4117
4118 OPEN total_regions;
4119 FETCH total_regions INTO l_total_regions;
4120 CLOSE total_regions;
4121 fnd_file.put_line(fnd_file.log,'Started the region upload process...');
4122
4123 IF (l_total_regions > 0) THEN
4124 Default_Regions(l_dummy_country,
4125 l_country_code,
4126 l_state,
4127 l_dummy_state_code,
4128 l_status,
4129 l_regions_processed,
4130 l_error_msg_text);
4131
4132 l_good_count := l_regions_processed;
4133 l_bad_count := l_total_regions - l_regions_processed;
4134
4135 fnd_file.put_line(fnd_file.log,'Summary: Total regions to be processed before submit = '||l_total_regions);
4136 fnd_file.put_line(fnd_file.log,'Summary: Number of new regions = '||l_good_count);
4137 fnd_file.put_line(fnd_file.log,'Summary: Number of regions with (not interfaced) = '||l_bad_count);
4138
4139 fnd_file.put_line(fnd_file.log,'Ended the region upload process.');
4140 errbuf := 'Submission is completed ';
4141 retcode := '0';
4142 ELSE
4143 fnd_file.put_line(fnd_file.log,'There are no valid regions in interface table. If any regions exist please correct them and resubmit the concurrent program.');
4144 errbuf := 'Submission is completed ';
4145 retcode := '0';
4146
4147 END IF;
4148
4149 --
4150 -- Debug Statements
4151 --
4152 IF l_debug_on THEN
4153 WSH_DEBUG_SV.pop(l_module_name);
4154 END IF;
4155 --
4156 END Default_Regions;
4157
4158
4159 -- This method in only for the purpose of submitting a request from the form
4160
4161 FUNCTION Load_All_Regions RETURN NUMBER IS
4162
4163 l_request_id NUMBER := 0;
4164
4165 --
4166 l_debug_on BOOLEAN;
4167 --
4168 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOAD_ALL_REGIONS';
4169 --
4170 BEGIN
4171 --
4172 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4173 --
4174 IF l_debug_on IS NULL
4175 THEN
4176 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4177 END IF;
4178 --
4179 --
4180 -- Debug Statements
4181 --
4182 IF l_debug_on THEN
4183 WSH_DEBUG_SV.push(l_module_name);
4184 END IF;
4185 --
4186 --
4187 -- Debug Statements
4188 --
4189 IF l_debug_on THEN
4190 WSH_DEBUG_SV.pop(l_module_name);
4191 END IF;
4192 --
4193 return fnd_request.submit_request('WSH','WSHRGINT','','',FALSE);
4194
4195 END Load_All_Regions;
4196
4197
4198 -- used by FTE_CAT_ZONE_LOV to display Regions that belong to a Zone
4199
4200 FUNCTION getZoneRegions(p_zoneId IN NUMBER, p_lang IN VARCHAR2)
4201 return VARCHAR2
4202 AS
4203 CURSOR zoneRegion_cur (p_zoneId number, p_lang varchar2)
4204 IS
4205 SELECT T.REGION_ID REGION_ID, T.COUNTRY COUNTRY, T.STATE STATE, T.CITY CITY,
4206 ltrim(P.POSTAL_CODE_FROM,'0') PCODE_FROM,
4207 ltrim(P.POSTAL_CODE_TO,'0') PCODE_TO
4208 FROM WSH_REGIONS_TL T, WSH_ZONE_REGIONS P
4209 WHERE T.LANGUAGE = p_lang
4210 AND T.REGION_ID = P.REGION_ID
4211 AND P.PARENT_REGION_ID = p_zoneId;
4212
4213 regions VARCHAR2(100) := null;
4214
4215 t_region VARCHAR2(300);
4216 t_region_list VARCHAR2(300);
4217 t_region_id VARCHAR2(10);
4218 t_country VARCHAR2(100);
4219 t_state VARCHAR2(100);
4220 t_city VARCHAR2(100);
4221 t_pcode_from VARCHAR2(100);
4222 t_pcode_to VARCHAR2(100);
4223
4224 --
4225 l_debug_on BOOLEAN;
4226 --
4227 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GETZONEREGIONS';
4228 --
4229 BEGIN
4230
4231 --
4232 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4233 --
4234 IF l_debug_on IS NULL
4235 THEN
4236 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4237 END IF;
4238 --
4239 --
4240 -- Debug Statements
4241 --
4242 IF l_debug_on THEN
4243 WSH_DEBUG_SV.push(l_module_name);
4244 --
4245 WSH_DEBUG_SV.log(l_module_name,'P_ZONEID',P_ZONEID);
4246 WSH_DEBUG_SV.log(l_module_name,'P_LANG',P_LANG);
4247 END IF;
4248 --
4249 for c1 in zoneRegion_cur(p_zoneId, p_lang) loop
4250
4251 t_region_id := trim(c1.region_id);
4252 t_country := trim(c1.country);
4253 t_state := trim(c1.state);
4254 t_city := trim(c1.city);
4255 t_pcode_from := trim(c1.pcode_from);
4256 t_pcode_to := trim(c1.pcode_to);
4257
4258 t_region := null;
4259 t_region_list := t_region_list || ':' || t_region_id;
4260
4261 if (t_country is not null) then
4262 t_region := t_country;
4263 end if;
4264
4265 if (t_state is not null) then
4266 t_region := t_region||' - '||t_state;
4267 end if;
4268
4269 if (t_city is not null) then
4270 t_region := t_region||' - '||t_city;
4271 end if;
4272
4273 if (t_pcode_from is not null) then
4274 t_region := t_region||' - '||t_pcode_from;
4275 end if;
4276
4277 if (t_pcode_to is not null) then
4278 t_region := t_region||' - '||t_pcode_to;
4279 end if;
4280
4281 if (t_region is not null) then
4282 if (regions is null) then
4283 regions := t_region;
4284 else
4285 regions := regions||', '||t_region;
4286 end if;
4287 end if;
4288 end loop;
4289
4290 if (p_lang <> 'US') then
4291
4292 for c1 in zoneRegion_cur(p_zoneId, 'US') loop
4293
4294 t_region_id := trim(c1.region_id);
4295 t_country := trim(c1.country);
4296 t_state := trim(c1.state);
4297 t_city := trim(c1.city);
4298 t_pcode_from := trim(c1.pcode_from);
4299 t_pcode_to := trim(c1.pcode_to);
4300
4301 t_region := null;
4302
4303 -- only if the region for p_lang is not there
4304 if (t_region_list is null OR
4305 instr(t_region_list, ':'||t_region_id) = 0)
4306 then
4307
4308 if (t_country is not null) then
4309 t_region := t_country;
4310 end if;
4311
4312 if (t_state is not null) then
4313 t_region := t_region||' - '||t_state;
4314 end if;
4315
4316 if (t_city is not null) then
4317 t_region := t_region||' - '||t_city;
4318 end if;
4319
4320 if (t_pcode_from is not null) then
4321 t_region := t_region||' - '||t_pcode_from;
4322 end if;
4323
4324 if (t_pcode_to is not null) then
4325 t_region := t_region||' - '||t_pcode_to;
4326 end if;
4327
4328 if (t_region is not null) then
4329 if (regions is null) then
4330 regions := t_region;
4331 else
4332 regions := regions||', '||t_region;
4333 end if;
4334 end if;
4335
4336 end if;
4337
4338 end loop;
4339
4340 end if;
4341
4342 --
4343 -- Debug Statements
4344 --
4345 IF l_debug_on THEN
4346 WSH_DEBUG_SV.pop(l_module_name);
4347 END IF;
4348 --
4349 RETURN regions;
4350 EXCEPTION when OTHERS then
4351 --
4352 -- Debug Statements
4353 --
4354 IF l_debug_on THEN
4355 WSH_DEBUG_SV.pop(l_module_name);
4356 END IF;
4357 --
4358 return regions||' ...';
4359 --
4360 -- Debug Statements
4361 --
4362 IF l_debug_on THEN
4363 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4364 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4365 END IF;
4366 --
4367 END;
4368
4369 /*----------------------------------------------------------*/
4370 /* Add_Language Procedure */
4371 /*----------------------------------------------------------*/
4372 procedure ADD_LANGUAGE
4373 is
4374 begin
4375 delete from WSH_REGIONS_TL T
4376 where not exists
4377 (select NULL
4378 from WSH_REGIONS B
4379 where B.REGION_ID = T.REGION_ID
4380 );
4381
4382 update WSH_REGIONS_TL T set (
4383 CONTINENT,
4384 COUNTRY,
4385 COUNTRY_REGION,
4386 STATE,
4387 CITY,
4388 ZONE,
4389 POSTAL_CODE_FROM,
4390 POSTAL_CODE_TO,
4391 ALTERNATE_NAME,
4392 COUNTY
4393 ) = (select
4394 B.CONTINENT,
4395 B.COUNTRY,
4396 B.COUNTRY_REGION,
4397 B.STATE,
4398 B.CITY,
4399 B.ZONE,
4400 B.POSTAL_CODE_FROM,
4401 B.POSTAL_CODE_TO,
4402 B.ALTERNATE_NAME,
4403 B.COUNTY
4404 from WSH_REGIONS_TL B
4405 where B.REGION_ID = T.REGION_ID
4406 and B.LANGUAGE = T.SOURCE_LANG)
4407 where (
4408 T.REGION_ID,
4409 T.LANGUAGE
4410 ) in (select
4411 SUBT.REGION_ID,
4412 SUBT.LANGUAGE
4413 from WSH_REGIONS_TL SUBB, WSH_REGIONS_TL SUBT
4414 where SUBB.REGION_ID = SUBT.REGION_ID
4415 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
4416 and (SUBB.CONTINENT <> SUBT.CONTINENT
4417 or (SUBB.CONTINENT is null and SUBT.CONTINENT is not null)
4418 or (SUBB.CONTINENT is not null and SUBT.CONTINENT is null)
4419 or SUBB.COUNTRY <> SUBT.COUNTRY
4420 or (SUBB.COUNTRY is null and SUBT.COUNTRY is not null)
4421 or (SUBB.COUNTRY is not null and SUBT.COUNTRY is null)
4422 or SUBB.COUNTRY_REGION <> SUBT.COUNTRY_REGION
4423 or (SUBB.COUNTRY_REGION is null and SUBT.COUNTRY_REGION is not null)
4424 or (SUBB.COUNTRY_REGION is not null and SUBT.COUNTRY_REGION is null)
4425 or SUBB.STATE <> SUBT.STATE
4426 or (SUBB.STATE is null and SUBT.STATE is not null)
4427 or (SUBB.STATE is not null and SUBT.STATE is null)
4428 or SUBB.CITY <> SUBT.CITY
4429 or (SUBB.CITY is null and SUBT.CITY is not null)
4430 or (SUBB.CITY is not null and SUBT.CITY is null)
4431 or SUBB.ZONE <> SUBT.ZONE
4432 or (SUBB.ZONE is null and SUBT.ZONE is not null)
4433 or (SUBB.ZONE is not null and SUBT.ZONE is null)
4434 or SUBB.POSTAL_CODE_FROM <> SUBT.POSTAL_CODE_FROM
4435 or (SUBB.POSTAL_CODE_FROM is null and SUBT.POSTAL_CODE_FROM is not null)
4436 or (SUBB.POSTAL_CODE_FROM is not null and SUBT.POSTAL_CODE_FROM is null)
4437 or SUBB.POSTAL_CODE_TO <> SUBT.POSTAL_CODE_TO
4438 or (SUBB.POSTAL_CODE_TO is null and SUBT.POSTAL_CODE_TO is not null)
4439 or (SUBB.POSTAL_CODE_TO is not null and SUBT.POSTAL_CODE_TO is null)
4440 or SUBB.ALTERNATE_NAME <> SUBT.ALTERNATE_NAME
4441 or (SUBB.ALTERNATE_NAME is null and SUBT.ALTERNATE_NAME is not null)
4442 or (SUBB.ALTERNATE_NAME is not null and SUBT.ALTERNATE_NAME is null)
4443 or SUBB.COUNTY <> SUBT.COUNTY
4444 or (SUBB.COUNTY is null and SUBT.COUNTY is not null)
4445 or (SUBB.COUNTY is not null and SUBT.COUNTY is null)
4446 ));
4447
4448 insert into WSH_REGIONS_TL (
4449 REGION_ID,
4450 CONTINENT,
4451 COUNTRY,
4452 COUNTRY_REGION,
4453 STATE,
4454 CITY,
4455 ZONE,
4456 POSTAL_CODE_FROM,
4457 POSTAL_CODE_TO,
4458 ALTERNATE_NAME,
4459 COUNTY,
4460 LAST_UPDATE_DATE,
4461 LAST_UPDATED_BY,
4462 CREATION_DATE,
4463 CREATED_BY,
4464 LAST_UPDATE_LOGIN,
4465 LANGUAGE,
4466 SOURCE_LANG
4467 ) select
4468 B.REGION_ID,
4469 B.CONTINENT,
4470 B.COUNTRY,
4471 B.COUNTRY_REGION,
4472 B.STATE,
4473 B.CITY,
4474 B.ZONE,
4475 B.POSTAL_CODE_FROM,
4476 B.POSTAL_CODE_TO,
4477 B.ALTERNATE_NAME,
4478 B.COUNTY,
4479 B.LAST_UPDATE_DATE,
4480 B.LAST_UPDATED_BY,
4481 B.CREATION_DATE,
4482 B.CREATED_BY,
4483 B.LAST_UPDATE_LOGIN,
4484 L.LANGUAGE_CODE,
4485 B.SOURCE_LANG
4486 from WSH_REGIONS_TL B, FND_LANGUAGES L
4487 where L.INSTALLED_FLAG in ('I', 'B')
4488 and B.LANGUAGE = userenv('LANG')
4489 and not exists
4490 (select NULL
4491 from WSH_REGIONS_TL T
4492 where T.REGION_ID = B.REGION_ID
4493 and T.LANGUAGE = L.LANGUAGE_CODE);
4494 end ADD_LANGUAGE;
4495
4496 -- Following procedure are added for Regions Interface Performance
4497
4498 --
4499 -- PROCEDURE : Validate_Region
4500 --
4501 -- PURPOSE : Validation regarding missing parameters or wrong format
4502 -- Same validatin are in the When-Validate-Record trigger
4503 -- on the Region block in WSHRGZON.fmb form
4504 PROCEDURE Validate_Region (
4505 p_country IN VARCHAR2,
4506 p_state IN VARCHAR2,
4507 p_city IN VARCHAR2,
4508 p_country_code IN VARCHAR2,
4509 p_state_code IN VARCHAR2,
4510 p_city_code IN VARCHAR2,
4511 p_postal_code_from IN VARCHAR2,
4512 p_postal_code_to IN VARCHAR2,
4513 x_status OUT NOCOPY NUMBER ,
4514 x_error_msg OUT NOCOPY VARCHAR2 )
4515 IS
4516 --
4517 l_debug_on BOOLEAN;
4518 --
4519 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_REGION';
4520 --
4521 BEGIN
4522 --
4523 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4524 --
4525 IF l_debug_on IS NULL
4526 THEN
4527 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4528 END IF;
4529 --
4530 --
4531 -- Debug Statements
4532 --
4533 IF l_debug_on THEN
4534 WSH_DEBUG_SV.push(l_module_name);
4535 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
4536 WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
4537 WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
4538 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
4539 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
4540 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
4541 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
4542 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
4543 END IF;
4544 --
4545
4546 IF (length(p_postal_code_from) <> length(p_postal_code_to)) THEN
4547 x_status := 2;
4548 x_error_msg := 'WSH_POSTAL_CODE_WRONG_FORMAT';
4549 --
4550 -- Debug Statements
4551 --
4552 IF l_debug_on THEN
4553 WSH_DEBUG_SV.pop(l_module_name);
4554 END IF;
4555 --
4556 return;
4557 END IF;
4558
4559 IF (p_postal_code_from > p_postal_code_to) THEN
4560 x_status := 2;
4561 x_error_msg := 'WSH_POSTAL_CODE_RANGE_ERR';
4562 --
4563 -- Debug Statements
4564 --
4565 IF l_debug_on THEN
4566 WSH_DEBUG_SV.pop(l_module_name);
4567 END IF;
4568 --
4569 return;
4570 END IF;
4571
4572 IF ( ( (p_postal_code_from is not null) AND (p_postal_code_to is null) ) OR
4573 ( (p_postal_code_from is null) AND (p_postal_code_to is not null) ) ) -- Same bug fixed in Pack J.
4574 THEN
4575 x_status := 2;
4576 x_error_msg := 'WSH_CAT_P_CODE_RANGE_INCOMP';
4577 --
4578 -- Debug Statements
4579 --
4580 IF l_debug_on THEN
4581 WSH_DEBUG_SV.pop(l_module_name);
4582 END IF;
4583 --
4584 return;
4585 END IF;
4586
4587 IF (p_country is null and p_country_code is not null ) THEN
4588 x_status := 2;
4589 x_error_msg := 'WSH_COUNTRY_OR_CODE_MISSING';
4590 --
4591 -- Debug Statements
4592 --
4593 IF l_debug_on THEN
4594 WSH_DEBUG_SV.pop(l_module_name);
4595 END IF;
4596 --
4597 return;
4598 END IF;
4599
4600 IF (p_state is null and p_state_code is not null ) THEN
4601 x_status := 2;
4602 x_error_msg := 'WSH_STATE_OR_CODE_MISSING';
4603 --
4604 -- Debug Statements
4605 --
4606 IF l_debug_on THEN
4607 WSH_DEBUG_SV.pop(l_module_name);
4608 END IF;
4609 --
4610 return;
4611 END IF;
4612
4613 IF (p_city is null and p_city_code is not null ) THEN
4614 x_status := 2;
4615 x_error_msg := 'WSH_CITY_OR_CODE_MISSING';
4616 --
4617 -- Debug Statements
4618 --
4619 IF l_debug_on THEN
4620 WSH_DEBUG_SV.pop(l_module_name);
4621 END IF;
4622 --
4623 return;
4624 END IF;
4625
4626 --
4627 -- Debug Statements
4628 --
4629 IF l_debug_on THEN
4630 WSH_DEBUG_SV.pop(l_module_name);
4631 END IF;
4632 --
4633
4634 x_status := 0;
4635
4636 -- Exception Handling part
4637 EXCEPTION
4638 WHEN OTHERS THEN
4639 x_status := 2;
4640 x_error_msg := 'WSH_UTIL_MESSAGE_U';
4641 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
4642 fnd_message.set_token('MSG_TEXT', SQLERRM);
4643 fnd_file.put_line(fnd_file.log, 'INSIDE VALIDATE_REGION EXCEPTION : ' || sqlerrm);
4644 END Validate_Region;
4645
4646 --
4647 -- PROCEDURE : Init_Global_Table
4648 --
4649 -- PURPOSE : Populates the data in Global Temp tables(Wsh_Regions_Global
4650 -- and Wsh_Regions_Global_Data) fetched from Wsh_Regions and
4651 -- Wsh_Regions_Tl based on parameter p_populate_type.
4652 --
4653
4654 PROCEDURE Init_Global_Table (
4655 p_country IN VARCHAR2,
4656 p_state IN VARCHAR2,
4657 p_city IN VARCHAR2,
4658 p_country_code IN VARCHAR2,
4659 p_state_code IN VARCHAR2,
4660 p_city_code IN VARCHAR2,
4661 p_country_flag IN VARCHAR2,
4662 p_state_flag IN VARCHAR2,
4663 p_city_flag IN VARCHAR2,
4664 x_return_status OUT NOCOPY VARCHAR2 )
4665 AS
4666
4667 CURSOR city_cur IS
4668 SELECT DISTINCT UPPER(STATE)
4669 FROM WSH_REGIONS_TL
4670 WHERE UPPER(CITY) = UPPER(p_city)
4671 AND UPPER(COUNTRY) = UPPER(p_country);
4672 -- AND LANGUAGE = p_lang_code;
4673
4674 CURSOR city_cur_code IS
4675 SELECT DISTINCT UPPER(R.STATE_CODE)
4676 FROM WSH_REGIONS R
4677 WHERE ( UPPER(R.CITY_CODE) = UPPER(p_city_code) OR p_city_code is null )
4678 AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4679 AND REGION_TYPE = 2;
4680
4681 TYPE tab_code is TABLE OF VARCHAR2(10) index by BINARY_INTEGER;
4682 TYPE tab_desc is TABLE OF VARCHAR2(60) index by BINARY_INTEGER;
4683
4684 tab_tmp_state tab_desc;
4685 tab_tmp_state_code tab_code;
4686
4687 l_tmp_state VARCHAR2(3000);
4688 l_tmp_state_code VARCHAR2(500);
4689 t1 NUMBER;
4690
4691 --
4692 l_debug_on BOOLEAN;
4693 --
4694 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INIT_GLOBAL_TABLE';
4695 --
4696 BEGIN
4697 --
4698 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4699 --
4700 IF l_debug_on IS NULL
4701 THEN
4702 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4703 END IF;
4704 --
4705 --
4706 -- Debug Statements
4707 --
4708 IF l_debug_on THEN
4709 WSH_DEBUG_SV.push(l_module_name);
4710 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
4711 WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
4712 WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
4713 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
4714 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
4715 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
4716 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_FLAG', P_COUNTRY_FLAG);
4717 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_FLAG', P_STATE_FLAG);
4718 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_FLAG', P_CITY_FLAG);
4719 END IF;
4720 --
4721
4722 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4723 t1 := dbms_utility.get_time;
4724
4725 IF ( p_country_flag = 'Y' ) THEN -- { Region type 0 i.e., Country
4726 DELETE FROM WSH_REGIONS_GLOBAL_DATA;
4727
4728 --
4729 -- Debug Statements
4730 --
4731 IF l_debug_on THEN
4732 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4733 END IF;
4734 --
4735
4736 INSERT INTO wsh_regions_global_data
4737 ( REGION_ID,
4738 REGION_TYPE,
4739 PARENT_REGION_ID,
4740 COUNTRY,
4741 STATE,
4742 CITY,
4743 COUNTRY_CODE,
4744 STATE_CODE,
4745 CITY_CODE,
4746 POSTAL_CODE_FROM,
4747 POSTAL_CODE_TO,
4748 LANGUAGE )
4749 SELECT R.REGION_ID,
4750 R.REGION_TYPE,
4751 R.PARENT_REGION_ID,
4752 UPPER(TL.COUNTRY),
4753 UPPER(TL.STATE),
4754 UPPER(TL.CITY),
4755 UPPER(R.COUNTRY_CODE),
4756 UPPER(R.STATE_CODE),
4757 UPPER(R.CITY_CODE),
4758 TL.POSTAL_CODE_FROM,
4759 TL.POSTAL_CODE_TO,
4760 TL.LANGUAGE
4761 FROM WSH_REGIONS R,
4762 WSH_REGIONS_TL TL
4763 WHERE R.REGION_ID = TL.REGION_ID
4764 AND R.REGION_TYPE = 0
4765 AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4766 OR UPPER(TL.COUNTRY) = UPPER(p_country) );
4767
4768 --
4769 -- Debug Statements
4770 --
4771 IF l_debug_on THEN
4772 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
4773 END IF;
4774 --
4775
4776 DELETE FROM WSH_REGIONS_GLOBAL;
4777
4778 --
4779 -- Debug Statements
4780 --
4781 IF l_debug_on THEN
4782 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
4783 END IF;
4784 --
4785
4786 INSERT INTO wsh_regions_global
4787 ( REGION_ID,
4788 REGION_TYPE,
4789 COUNTRY_CODE,
4790 STATE_CODE,
4791 CITY_CODE )
4792 SELECT R.REGION_ID,
4793 R.REGION_TYPE,
4794 UPPER(R.COUNTRY_CODE),
4795 UPPER(R.STATE_CODE),
4796 UPPER(R.CITY_CODE)
4797 FROM WSH_REGIONS R
4798 WHERE R.REGION_TYPE = 0
4799 AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code);
4800
4801 --
4802 -- Debug Statements
4803 --
4804 IF l_debug_on THEN
4805 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
4806 END IF;
4807 --
4808 END IF; -- } Region Type 0 ie., Country
4809
4810 IF ( p_state_flag = 'Y' ) THEN -- { Region type 1 i.e., State
4811
4812 -- If State is NULL and City is NOT NULL then we need get States
4813 -- corresponding to City passed.
4814 IF ( (p_city is not null and p_state is null) )
4815 THEN
4816 OPEN city_cur;
4817 FETCH city_cur BULK COLLECT INTO tab_tmp_state;
4818 CLOSE city_cur;
4819
4820 FOR i in 1..tab_tmp_state.COUNT
4821 LOOP
4822 IF ( i = 1 ) THEN
4823 l_tmp_state := tab_tmp_state(i);
4824 ELSE
4825 l_tmp_state := l_tmp_state || ', ' || tab_tmp_state(i);
4826 END IF;
4827 END LOOP;
4828 END IF;
4829
4830 -- If State_Code is NULL and City_Code is NOT NULL then we need get
4831 -- State_Codes corresponding to City_Code passed.
4832 IF ( (p_city_code is not null and p_state_code is null) )
4833 THEN
4834 OPEN city_cur_code;
4835 FETCH city_cur_code BULK COLLECT INTO tab_tmp_state_code;
4836 CLOSE city_cur_code;
4837
4838 FOR i in 1..tab_tmp_state_code.COUNT
4839 LOOP
4840 IF ( i = 1 ) THEN
4841 l_tmp_state_code := tab_tmp_state_code(i);
4842 ELSE
4843 l_tmp_state_code := l_tmp_state_code || ', ' || tab_tmp_state_code(i);
4844 END IF;
4845 END LOOP;
4846 END IF;
4847
4848 DELETE FROM WSH_REGIONS_GLOBAL_DATA
4849 WHERE REGION_TYPE in ( 1, 2, 3 );
4850
4851 --
4852 -- Debug Statements
4853 --
4854 IF l_debug_on THEN
4855 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4856 END IF;
4857 --
4858
4859 INSERT INTO wsh_regions_global_data
4860 ( REGION_ID,
4861 REGION_TYPE,
4862 PARENT_REGION_ID,
4863 COUNTRY,
4864 STATE,
4865 CITY,
4866 COUNTRY_CODE,
4867 STATE_CODE,
4868 CITY_CODE,
4869 POSTAL_CODE_FROM,
4870 POSTAL_CODE_TO,
4871 LANGUAGE )
4872 SELECT R.REGION_ID,
4873 R.REGION_TYPE,
4874 R.PARENT_REGION_ID,
4875 UPPER(TL.COUNTRY),
4876 UPPER(TL.STATE),
4877 UPPER(TL.CITY),
4878 UPPER(R.COUNTRY_CODE),
4879 UPPER(R.STATE_CODE),
4880 UPPER(R.CITY_CODE),
4881 TL.POSTAL_CODE_FROM,
4882 TL.POSTAL_CODE_TO,
4883 TL.LANGUAGE
4884 FROM WSH_REGIONS R,
4885 WSH_REGIONS_TL TL
4886 WHERE R.REGION_ID = TL.REGION_ID
4887 AND R.REGION_TYPE = 1
4888 AND (
4889 ( ( UPPER(R.STATE_CODE) = UPPER(p_state_code)
4890 OR ( UPPER(R.STATE_CODE) in ( l_tmp_state_code ) )
4891 AND ( p_state_code is NOT NULL OR p_city_code is NOT null ) ) ) --Modified for bug 10256328
4892 OR ( ( UPPER(TL.STATE) = UPPER(p_state)
4893 OR ( UPPER(TL.STATE) in ( l_tmp_state ) )
4894 AND ( p_state is NOT null OR p_city is NOT null) ) )--Modified for bug 10256328
4895 )
4896 AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4897 OR UPPER(TL.COUNTRY) = UPPER(p_country) );
4898
4899 --
4900 -- Debug Statements
4901 --
4902 IF l_debug_on THEN
4903 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
4904 END IF;
4905 --
4906
4907 DELETE FROM WSH_REGIONS_GLOBAL
4908 WHERE REGION_TYPE in ( 1, 2, 3 );
4909
4910 --
4911 -- Debug Statements
4912 --
4913 IF l_debug_on THEN
4914 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
4915 END IF;
4916 --
4917
4918 INSERT INTO wsh_regions_global
4919 ( REGION_ID,
4920 REGION_TYPE,
4921 COUNTRY_CODE,
4922 STATE_CODE,
4923 CITY_CODE )
4924 SELECT R.REGION_ID,
4925 R.REGION_TYPE,
4926 UPPER(R.COUNTRY_CODE),
4927 UPPER(R.STATE_CODE),
4928 UPPER(R.CITY_CODE)
4929 FROM WSH_REGIONS R
4930 WHERE R.REGION_TYPE = 1
4931 AND ( UPPER(R.STATE_CODE) = UPPER(p_state_code)
4932 OR ( UPPER(R.STATE_CODE) in ( l_tmp_state_code ) )
4933 OR ( p_state_code is NULL and p_city_code is null) )
4934 AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code);
4935
4936 --
4937 -- Debug Statements
4938 --
4939 IF l_debug_on THEN
4940 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
4941 END IF;
4942 --
4943
4944 END IF; -- } Region type 1 i.e., State
4945
4946 IF ( p_city_flag = 'Y' ) THEN -- { Region type 2,3 i.e., City, Postal Codes
4947
4948 DELETE FROM WSH_REGIONS_GLOBAL_DATA
4949 WHERE REGION_TYPE in ( 2, 3 );
4950
4951 --
4952 -- Debug Statements
4953 --
4954 IF l_debug_on THEN
4955 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4956 END IF;
4957 --
4958
4959 INSERT INTO wsh_regions_global_data
4960 ( REGION_ID,
4961 REGION_TYPE,
4962 PARENT_REGION_ID,
4963 COUNTRY,
4964 STATE,
4965 CITY,
4966 COUNTRY_CODE,
4967 STATE_CODE,
4968 CITY_CODE,
4969 POSTAL_CODE_FROM,
4970 POSTAL_CODE_TO,
4971 LANGUAGE )
4972 SELECT R.REGION_ID,
4973 R.REGION_TYPE,
4974 R.PARENT_REGION_ID,
4975 UPPER(TL.COUNTRY),
4976 UPPER(TL.STATE),
4977 UPPER(TL.CITY),
4978 UPPER(R.COUNTRY_CODE),
4979 UPPER(R.STATE_CODE),
4980 UPPER(R.CITY_CODE),
4981 TL.POSTAL_CODE_FROM,
4982 TL.POSTAL_CODE_TO,
4983 TL.LANGUAGE
4984 FROM WSH_REGIONS R,
4985 WSH_REGIONS_TL TL
4986 WHERE R.REGION_ID = TL.REGION_ID
4987 /* AND (
4988 ( ( UPPER(R.CITY_CODE) = UPPER(p_city_code) OR p_city_code is NULL )
4989 AND ( decode(p_city_code, null, UPPER(R.STATE_CODE), NVL(UPPER(R.STATE_CODE), UPPER(p_state_code) )) = UPPER(p_state_code)
4990 OR ( p_state_code is NULL ) ) )
4991 OR ( ( UPPER(TL.CITY) = UPPER(p_city) OR ( p_city is NULL ) )
4992 AND ( decode( p_city, null, UPPER(TL.STATE), NVL(UPPER(TL.STATE), UPPER(p_state) )) = UPPER(p_state)
4993 OR ( p_state is null ) ) )
4994 )
4995 AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4996 OR UPPER(TL.COUNTRY) = UPPER(p_country) );*/--Commented for bug 10256328
4997 AND (
4998 (
4999 UPPER(R.CITY_CODE)=UPPER(P_CITY_CODE)
5000 OR
5001 UPPER(TL.CITY)=UPPER(P_CITY)
5002 OR
5003 (R.CITY_CODE IS NULL AND TL.CITY IS NULL)
5004 )
5005 OR
5006 (
5007 P_CITY_CODE IS NULL
5008 AND
5009 P_CITY IS NULL
5010 )
5011 ) AND
5012 (
5013 (
5014 UPPER(R.STATE_CODE)=UPPER(P_STATE_CODE)
5015 OR
5016 UPPER(TL.STATE)=UPPER(P_STATE)
5017 OR
5018 (R.STATE_CODE IS NULL AND TL.STATE IS NULL)
5019 )
5020 OR
5021 (
5022 P_STATE_CODE IS NULL
5023 AND
5024 P_STATE IS NULL
5025 )
5026 ) AND
5027 (
5028 UPPER(R.COUNTRY_CODE) = UPPER(P_COUNTRY_CODE)
5029 OR
5030 UPPER(TL.COUNTRY) = UPPER(P_COUNTRY)
5031 )
5032 AND R.REGION_TYPE in (2,3);--Added for bug 10256328
5033
5034 --
5035 -- Debug Statements
5036 --
5037 IF l_debug_on THEN
5038 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
5039 END IF;
5040 --
5041
5042 DELETE FROM WSH_REGIONS_GLOBAL
5043 WHERE REGION_TYPE in ( 2, 3 );
5044
5045 --
5046 -- Debug Statements
5047 --
5048 IF l_debug_on THEN
5049 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
5050 END IF;
5051 --
5052
5053 INSERT INTO wsh_regions_global
5054 ( REGION_ID,
5055 REGION_TYPE,
5056 COUNTRY_CODE,
5057 STATE_CODE,
5058 CITY_CODE )
5059 SELECT R.REGION_ID,
5060 R.REGION_TYPE,
5061 UPPER(R.COUNTRY_CODE),
5062 UPPER(R.STATE_CODE),
5063 UPPER(R.CITY_CODE)
5064 FROM WSH_REGIONS R
5065 WHERE R.REGION_TYPE = 2
5066 AND ( UPPER(R.CITY_CODE) = UPPER(p_city_code)
5067 OR ( p_city_code is NULL ) )
5068 AND ( decode(p_city_code, null, UPPER(R.STATE_CODE), NVL(UPPER(R.STATE_CODE), UPPER(p_state_code) )) = UPPER(p_state_code)
5069 OR ( p_state_code is NULL ) )
5070 AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code);
5071
5072 --
5073 -- Debug Statements
5074 --
5075 IF l_debug_on THEN
5076 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
5077 END IF;
5078 --
5079
5080 END IF; -- } Region type 2,3 i.e., City, Postal Codes
5081
5082 --
5083 -- Debug Statements
5084 --
5085 IF l_debug_on THEN
5086 WSH_DEBUG_SV.pop(l_module_name);
5087 END IF;
5088 --
5089
5090 -- Exception Handling part
5091 EXCEPTION
5092 WHEN OTHERS THEN
5093 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5094 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
5095 fnd_message.set_token('MSG_TEXT', SQLERRM);
5096 fnd_file.put_line(fnd_file.log, 'Init_Global_Table EXCEPTION : ' || sqlerrm);
5097 END Init_Global_Table;
5098
5099 --
5100 -- PROCEDURE : Insert_Global_Table
5101 --
5102 -- PURPOSE : Inserts the data in Global Temp tables
5103 -- ( Wsh_Regions_Global_Data and Wsh_Regions_Global tables )
5104
5105 PROCEDURE Insert_Global_Table (
5106 p_country IN VARCHAR2,
5107 p_state IN VARCHAR2,
5108 p_city IN VARCHAR2,
5109 p_country_code IN VARCHAR2,
5110 p_state_code IN VARCHAR2,
5111 p_city_code IN VARCHAR2,
5112 p_region_id IN NUMBER ,
5113 p_region_type IN NUMBER ,
5114 p_parent_region_id IN NUMBER ,
5115 p_postal_code_from IN VARCHAR2,
5116 p_postal_code_to IN VARCHAR2,
5117 p_tl_only_flag IN VARCHAR2,
5118 p_lang_code IN VARCHAR2,
5119 x_return_status OUT NOCOPY VARCHAR2 )
5120 AS
5121
5122 CURSOR parent_region IS
5123 SELECT parent_region_id
5124 FROM WSH_REGIONS
5125 WHERE REGION_ID = p_region_id;
5126
5127 l_region_id NUMBER;
5128 l_parent_region_id NUMBER;
5129 t1 NUMBER;
5130
5131 --
5132 l_debug_on BOOLEAN;
5133 --
5134 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_GLOBAL_TABLE';
5135 --
5136 BEGIN
5137 --
5138 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5139 --
5140 IF l_debug_on IS NULL
5141 THEN
5142 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5143 END IF;
5144 --
5145 --
5146 -- Debug Statements
5147 --
5148 IF l_debug_on THEN
5149 WSH_DEBUG_SV.push(l_module_name);
5150 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
5151 WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
5152 WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
5153 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
5154 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
5155 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
5156 WSH_DEBUG_SV.log(l_module_name, 'P_REGION_ID', P_REGION_ID);
5157 WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
5158 WSH_DEBUG_SV.log(l_module_name, 'P_PARENT_REGION_ID', P_PARENT_REGION_ID);
5159 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
5160 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
5161 WSH_DEBUG_SV.log(l_module_name, 'P_TL_ONLY_FLAG', P_TL_ONLY_FLAG);
5162 WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
5163 END IF;
5164 --
5165
5166 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5167 t1 := dbms_utility.get_time;
5168 IF ( p_parent_region_id is null AND
5169 p_region_type = 0 )
5170 THEN
5171 l_parent_region_id := -1;
5172 ELSIF ( p_parent_region_id is null )
5173 THEN
5174 OPEN parent_region;
5175 FETCH parent_region INTO l_parent_region_id;
5176 CLOSE parent_region;
5177 ELSE
5178 l_parent_region_id := p_parent_region_id;
5179 END IF;
5180
5181 INSERT INTO wsh_regions_global_data
5182 ( REGION_ID,
5183 REGION_TYPE,
5184 PARENT_REGION_ID,
5185 COUNTRY,
5186 STATE,
5187 CITY,
5188 COUNTRY_CODE,
5189 STATE_CODE,
5190 CITY_CODE,
5191 POSTAL_CODE_FROM,
5192 POSTAL_CODE_TO,
5193 LANGUAGE )
5194 VALUES
5195 ( p_region_id,
5196 p_region_type,
5197 l_parent_region_id,
5198 UPPER(p_country),
5199 UPPER(p_state),
5200 UPPER(p_city),
5201 UPPER(p_country_code),
5202 UPPER(p_state_code),
5203 UPPER(p_city_code),
5204 p_postal_code_from,
5205 p_postal_code_to,
5206 p_lang_code );
5207
5208 --
5209 -- Debug Statements
5210 --
5211 IF l_debug_on THEN
5212 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
5213 END IF;
5214 --
5215
5216 IF ( p_tl_only_flag <> 'Y' AND
5217 p_region_type in ( 0, 1, 2 ) )
5218 THEN
5219 INSERT INTO wsh_regions_global
5220 ( REGION_ID,
5221 REGION_TYPE,
5222 COUNTRY_CODE,
5223 STATE_CODE,
5224 CITY_CODE )
5225 VALUES
5226 ( p_region_id,
5227 p_region_type,
5228 UPPER(p_country_code),
5229 UPPER(p_state_code),
5230 UPPER(p_city_code) );
5231 --
5232 -- Debug Statements
5233 --
5234 IF l_debug_on THEN
5235 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
5236 END IF;
5237 --
5238 END IF;
5239
5240 --
5241 -- Debug Statements
5242 --
5243 IF l_debug_on THEN
5244 WSH_DEBUG_SV.pop(l_module_name);
5245 END IF;
5246 --
5247
5248 -- Exception Handling part
5249 EXCEPTION
5250 WHEN OTHERS THEN
5251 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5252 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
5253 fnd_message.set_token('MSG_TEXT', SQLERRM);
5254 fnd_file.put_line(fnd_file.log, 'INSERT_GLOBAL_TABLE EXCEPTION : ' || sqlerrm);
5255 END Insert_Global_Table;
5256
5257 --
5258 -- PROCEDURE : Update_Global_Table
5259 --
5260 -- PURPOSE : Updates the data in Global Temp tables
5261 -- ( Wsh_Regions_Global_Data and Wsh_Regions_Global tables )
5262
5263 PROCEDURE Update_Global_Table (
5264 p_country IN VARCHAR2,
5265 p_state IN VARCHAR2,
5266 p_city IN VARCHAR2,
5267 p_country_code IN VARCHAR2,
5268 p_state_code IN VARCHAR2,
5269 p_city_code IN VARCHAR2,
5270 p_region_id IN NUMBER ,
5271 p_postal_code_from IN VARCHAR2,
5272 p_postal_code_to IN VARCHAR2,
5273 p_parent_zone_level IN NUMBER,
5274 p_lang_code IN VARCHAR2,
5275 x_return_status OUT NOCOPY VARCHAR2 )
5276 AS
5277
5278 CURSOR child_regions IS
5279 SELECT region_id, region_type, parent_region_id
5280 FROM wsh_regions_global_data
5281 START WITH region_id = p_region_id
5282 CONNECT BY PRIOR region_id = parent_region_id;
5283
5284 CURSOR get_state_code(l_region_id NUMBER) IS
5285 SELECT state_code, city_code
5286 FROM wsh_regions
5287 WHERE region_id = l_region_id;
5288
5289 l_region_id NUMBER;
5290 l_parent_region_id NUMBER;
5291 l_region_upd_cnt NUMBER;
5292 l_region_data_upd_cnt NUMBER;
5293 l_region_ins_cnt NUMBER;
5294 l_region_data_ins_cnt NUMBER;
5295 l_update_state_code WSH_REGIONS.State_Code%TYPE;
5296 l_update_city_code WSH_REGIONS.City_Code%TYPE;
5297 t1 NUMBER;
5298 --
5299 l_debug_on BOOLEAN;
5300 --
5301 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_GLOBAL_TABLE';
5302 --
5303 BEGIN
5304 --
5305 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5306 --
5307 IF l_debug_on IS NULL
5308 THEN
5309 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5310 END IF;
5311 --
5312 --
5313 -- Debug Statements
5314 --
5315 IF l_debug_on THEN
5316 WSH_DEBUG_SV.push(l_module_name);
5317 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
5318 WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
5319 WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
5320 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
5321 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
5322 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
5323 WSH_DEBUG_SV.log(l_module_name, 'P_REGION_ID', P_REGION_ID);
5324 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
5325 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
5326 WSH_DEBUG_SV.log(l_module_name, 'P_PARENT_ZONE_LEVEL', P_PARENT_ZONE_LEVEL);
5327 WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
5328 END IF;
5329 --
5330
5331 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5332 l_region_upd_cnt := 0;
5333 l_region_ins_cnt := 0;
5334 l_region_data_upd_cnt := 0;
5335 l_region_data_ins_cnt := 0;
5336 t1 := dbms_utility.get_time;
5337
5338 FOR i in child_regions
5339 LOOP
5340
5341 OPEN get_state_code(i.parent_region_id);
5342 FETCH get_state_code INTO l_update_state_code, l_update_city_code;
5343 CLOSE get_state_code;
5344
5345 IF ( i.region_type in ( 0, 1, 2 ) )
5346 THEN
5347 UPDATE wsh_regions_global
5348 SET COUNTRY_CODE = nvl(UPPER(p_country_code), COUNTRY_CODE),
5349 state_code = decode(i.region_type,
5350 2, UPPER(l_update_state_code),
5351 3, UPPER(l_update_state_code),
5352 decode(p_parent_zone_level,
5353 1, UPPER(p_state_code),
5354 nvl(UPPER(p_state_code), state_code))),
5355 city_code = decode(p_parent_zone_level,
5356 2, UPPER(p_city_code),
5357 nvl(UPPER(p_city_code), city_code))
5358 WHERE REGION_ID = i.region_id;
5359
5360 l_region_upd_cnt := l_region_upd_cnt + sql%rowcount;
5361
5362 IF ( sql%rowcount = 0 )
5363 THEN
5364 INSERT INTO wsh_regions_global
5365 ( REGION_ID,
5366 REGION_TYPE,
5367 COUNTRY_CODE,
5368 STATE_CODE,
5369 CITY_CODE )
5370 SELECT REGION_ID,
5371 REGION_TYPE,
5372 COUNTRY_CODE,
5373 STATE_CODE,
5374 CITY_CODE
5375 FROM WSH_REGIONS
5376 WHERE REGION_ID = i.region_id;
5377
5378 l_region_ins_cnt := l_region_ins_cnt + sql%rowcount;
5379 END IF;
5380 END IF;
5381
5382 UPDATE wsh_regions_global_data
5383 SET country = nvl(UPPER(p_country), country),
5384 state = nvl(UPPER(p_state), state),
5385 city = nvl(UPPER(p_city), city),
5386 country_code = nvl(UPPER(p_country_code), country_code),
5387 state_code = decode(i.region_type,
5388 2, UPPER(l_update_state_code),
5389 3, UPPER(l_update_state_code),
5390 decode(p_parent_zone_level,
5391 1, UPPER(p_state_code),
5392 nvl(UPPER(p_state_code), state_code))),
5393 city_code = decode(p_parent_zone_level,
5394 2, UPPER(p_city_code),
5395 nvl(UPPER(p_city_code), city_code)),
5396 postal_code_from = nvl(p_postal_code_from, postal_code_from),
5397 postal_code_to = nvl(p_postal_code_to, postal_code_to)
5398 WHERE region_id = i.region_id
5399 AND language = p_lang_code;
5400
5401 l_region_data_upd_cnt := l_region_data_upd_cnt + sql%rowcount;
5402
5403 IF ( sql%rowcount = 0 )
5404 THEN
5405 INSERT INTO wsh_regions_global_data
5406 ( REGION_ID,
5407 REGION_TYPE,
5408 PARENT_REGION_ID,
5409 COUNTRY,
5410 STATE,
5411 CITY,
5412 COUNTRY_CODE,
5413 STATE_CODE,
5414 CITY_CODE,
5415 POSTAL_CODE_FROM,
5416 POSTAL_CODE_TO,
5417 LANGUAGE )
5418 SELECT R.REGION_ID,
5419 R.REGION_TYPE,
5420 R.PARENT_REGION_ID,
5421 TL.COUNTRY,
5422 TL.STATE,
5423 TL.CITY,
5424 R.COUNTRY_CODE,
5425 R.STATE_CODE,
5426 R.CITY_CODE,
5427 TL.POSTAL_CODE_FROM,
5428 TL.POSTAL_CODE_TO,
5429 TL.LANGUAGE
5430 FROM WSH_REGIONS R,
5431 WSH_REGIONS_TL TL
5432 WHERE TL.LANGUAGE = p_lang_code
5433 AND TL.REGION_ID = R.REGION_ID
5434 AND R.REGION_ID = i.region_id;
5435
5436 l_region_data_ins_cnt := l_region_data_ins_cnt + sql%rowcount;
5437 END IF;
5438
5439 END LOOP;
5440
5441 --
5442 --
5443 -- Debug Statements
5444 --
5445 IF l_debug_on THEN
5446 WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global', l_region_upd_cnt);
5447 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', l_region_ins_cnt);
5448 WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global_data', l_region_data_upd_cnt);
5449 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', l_region_data_ins_cnt);
5450 END IF;
5451 --
5452
5453 --
5454 -- Debug Statements
5455 --
5456 IF l_debug_on THEN
5457 WSH_DEBUG_SV.pop(l_module_name);
5458 END IF;
5459 --
5460
5461 -- Exception Handling part
5462 EXCEPTION
5463 WHEN OTHERS THEN
5464 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5465 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
5466 fnd_message.set_token('MSG_TEXT', SQLERRM);
5467 fnd_file.put_line(fnd_file.log, 'UPDATE_GLOBAL_TABLE EXCEPTION : ' || sqlerrm);
5468 END Update_Global_Table;
5469
5470 END WSH_REGIONS_PKG;
5471