[Home] [Help]
PACKAGE BODY: APPS.WSH_REGIONS_PKG
Source
1 PACKAGE BODY WSH_REGIONS_PKG AS
2 /* $Header: WSHRETHB.pls 120.2 2005/12/02 12:53:18 parkhj noship $ */
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,
2835 last_updated_by = p_user_id,
2836 last_update_date = sysdate
2837 WHERE region_id = p_zone_id;
2838
2839 l_zone_id := p_zone_id;
2840
2841 END IF;
2842
2843 x_zone_id := l_zone_id;
2844
2845 --
2846 -- Debug Statements
2847 --
2848 IF l_debug_on THEN
2849 WSH_DEBUG_SV.pop(l_module_name);
2850 END IF;
2851 --
2852 END Update_Zone;
2853
2854 --
2855 -- Procedure: Lock_Zone
2856 -- Parameters: p_zone_id - zone_id for zone to be locked
2857 -- x_return_status - Status of procedure call
2858 -- Description: This procedure will lock a zone record. It is
2859 -- specifically designed for use by the form.
2860 --
2861
2862 PROCEDURE Lock_Zone
2863 (p_zone_id IN NUMBER,
2864 p_lang_code IN VARCHAR2,
2865 p_zone_name IN VARCHAR2,
2866 p_zone_level IN VARCHAR2,
2867 x_status OUT NOCOPY NUMBER) IS
2868
2869 CURSOR lock_row IS
2870 SELECT w.region_id, t.zone, w.zone_level
2871 FROM wsh_regions w, wsh_regions_tl t
2872 WHERE w.region_id = t.region_id
2873 AND t.language = p_lang_code
2874 FOR UPDATE OF w.region_id, t.region_id NOWAIT;
2875
2876 Recinfo lock_row%ROWTYPE;
2877
2878 --
2879 l_debug_on BOOLEAN;
2880 --
2881 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ZONE';
2882 --
2883 BEGIN
2884
2885 --
2886 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2887 --
2888 IF l_debug_on IS NULL
2889 THEN
2890 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2891 END IF;
2892 --
2893 --
2894 -- Debug Statements
2895 --
2896 IF l_debug_on THEN
2897 WSH_DEBUG_SV.push(l_module_name);
2898 --
2899 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_ID',P_ZONE_ID);
2900 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
2901 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_NAME',P_ZONE_NAME);
2902 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_LEVEL',P_ZONE_LEVEL);
2903 END IF;
2904 --
2905 OPEN lock_row;
2906 FETCH lock_row INTO Recinfo;
2907
2908 IF (lock_row%NOTFOUND) THEN
2909 CLOSE lock_row;
2910 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
2911 app_exception.raise_exception;
2912 END IF;
2913
2914 CLOSE lock_row;
2915
2916 IF (
2917 (Recinfo.region_id = p_zone_id)
2918 AND (Recinfo.zone = p_zone_name)
2919 AND ( (Recinfo.zone_level = p_zone_level)
2920 OR ( (Recinfo.zone_level IS NULL) AND (p_zone_level IS NULL)))
2921 ) THEN
2922 --
2923 -- Debug Statements
2924 --
2925 IF l_debug_on THEN
2926 WSH_DEBUG_SV.pop(l_module_name);
2927 END IF;
2928 --
2929 RETURN;
2930 ELSE
2931 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
2932 app_exception.raise_exception;
2933 END IF;
2934
2935 --
2936 -- Debug Statements
2937 --
2938 IF l_debug_on THEN
2939 WSH_DEBUG_SV.pop(l_module_name);
2940 END IF;
2941 --
2942 EXCEPTION
2943 WHEN others THEN
2944 if (lock_row%ISOPEN) then
2945 close lock_row;
2946 end if;
2947
2948 --
2949 -- Debug Statements
2950 --
2951 IF l_debug_on THEN
2952 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2953 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2954 END IF;
2955 --
2956 raise;
2957
2958 --
2959 -- Debug Statements
2960 --
2961 IF l_debug_on THEN
2962 WSH_DEBUG_SV.pop(l_module_name);
2963 END IF;
2964 --
2965 END Lock_Zone;
2966
2967 --
2968 -- Procedure: Update_Zone_Region
2969 --
2970 -- Purpose: Updates or inserts a new zone region
2971 -- Call another Update_Zone_Region with default p_zone_type='10'
2972
2973 PROCEDURE Update_Zone_Region (
2974 p_insert_type IN VARCHAR2,
2975 p_zone_region_id IN NUMBER,
2976 p_zone_id IN NUMBER,
2977 p_country IN VARCHAR2,
2978 p_state IN VARCHAR2,
2979 p_city IN VARCHAR2,
2980 p_postal_code_from IN VARCHAR2,
2981 p_postal_code_to IN VARCHAR2,
2982 p_lang_code IN VARCHAR2,
2983 p_country_code IN VARCHAR2,
2984 p_state_code IN VARCHAR2,
2985 p_city_code IN VARCHAR2,
2986 p_user_id IN NUMBER,
2987 x_zone_region_id OUT NOCOPY NUMBER,
2988 x_region_id OUT NOCOPY NUMBER,
2989 x_status OUT NOCOPY NUMBER,
2990 x_error_msg OUT NOCOPY VARCHAR2) IS
2991 --
2992 l_debug_on BOOLEAN;
2993 --
2994 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ZONE_REGION';
2995 --
2996 BEGIN
2997 --
2998 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2999 --
3000 IF l_debug_on IS NULL
3001 THEN
3002 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3003 END IF;
3004 --
3005 --
3006 -- Debug Statements
3007 --
3008 IF l_debug_on THEN
3009 WSH_DEBUG_SV.push(l_module_name);
3010 --
3011 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
3012 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_REGION_ID',P_ZONE_REGION_ID);
3013 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_ID',P_ZONE_ID);
3014 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
3015 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
3016 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
3017 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
3018 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
3019 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
3020 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
3021 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
3022 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
3023 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
3024 END IF;
3025 --
3026 Update_Zone_Region(
3027 p_insert_type => p_insert_type,
3028 p_zone_region_id => p_zone_region_id,
3029 p_zone_id => p_zone_id,
3030 p_region_id => null,
3031 p_country => p_country,
3032 p_state => p_state,
3033 p_city => p_city,
3034 p_postal_code_from => p_postal_code_from,
3035 p_postal_code_to => p_postal_code_to,
3036 p_lang_code => p_lang_code,
3037 p_country_code => p_country_code,
3038 p_state_code => p_state_code,
3039 p_city_code => p_city_code,
3040 p_user_id => p_user_id,
3041 p_zone_type => '10',
3042 x_zone_region_id => x_zone_region_id,
3043 x_region_id => x_region_id,
3044 x_status => x_status,
3045 x_error_msg => x_error_msg);
3046
3047 --
3048 -- Debug Statements
3049 --
3050 IF l_debug_on THEN
3051 WSH_DEBUG_SV.pop(l_module_name);
3052 END IF;
3053 --
3054 END Update_Zone_Region;
3055
3056 --
3057 -- Procedure: Update_Zone_Region
3058 --
3059 -- Purpose: Updates or inserts a new zone region
3060 --
3061
3062 PROCEDURE Update_Zone_Region (
3063 p_insert_type IN VARCHAR2,
3064 p_zone_region_id IN NUMBER,
3065 p_zone_id IN NUMBER,
3066 p_region_id IN NUMBER,
3067 p_country IN VARCHAR2,
3068 p_state IN VARCHAR2,
3069 p_city IN VARCHAR2,
3070 p_postal_code_from IN VARCHAR2,
3071 p_postal_code_to IN VARCHAR2,
3072 p_lang_code IN VARCHAR2,
3073 p_country_code IN VARCHAR2,
3074 p_state_code IN VARCHAR2,
3075 p_city_code IN VARCHAR2,
3076 p_user_id IN NUMBER,
3077 p_zone_type IN VARCHAR2,
3078 x_zone_region_id OUT NOCOPY NUMBER,
3079 x_region_id OUT NOCOPY NUMBER,
3080 x_status OUT NOCOPY NUMBER,
3081 x_error_msg OUT NOCOPY VARCHAR2) IS
3082
3083 CURSOR check_regions_in_zone(l_region_id NUMBER, l_codeFrom VARCHAR2, l_codeTo VARCHAR2) IS
3084 SELECT region_id
3085 FROM wsh_zone_regions z
3086 WHERE z.region_id in (
3087 SELECT region_id
3088 FROM wsh_regions
3089 START WITH region_id = l_region_id
3090 CONNECT BY PRIOR parent_region_id = region_id) AND
3091 z.parent_region_id = p_zone_id
3092 and ( nvl(l_codeFrom,'0') between nvl(z.postal_code_from,'0') and nvl(z.postal_code_to,'ZZZZZZZZZZZZZ')
3093 or nvl(l_codeTo,'0') between nvl(z.postal_code_from,'0') and nvl(z.postal_code_to,'ZZZZZZZZZZZZZZ')
3094 or nvl(z.postal_code_from,'0') between nvl(l_codeFrom,'0') and nvl(l_codeTo,'ZZZZZZZZZZZZ'));
3095
3096 CURSOR check_regions_in_zone_down(l_region_id NUMBER) IS
3097 SELECT region_id
3098 FROM wsh_regions r
3099 WHERE region_id = l_region_id
3100 START WITH r.region_id in (
3101 SELECT region_id
3102 FROM wsh_zone_regions
3103 WHERE parent_region_id = p_zone_id)
3104 CONNECT BY PRIOR parent_region_id = region_id;
3105
3106 CURSOR check_same_region_in_zone(l_region_id NUMBER, l_codeFrom VARCHAR2, l_codeTo VARCHAR2) IS
3107 SELECT region_id
3108 FROM wsh_zone_regions z
3109 WHERE z.region_id in (
3110 SELECT region_id
3111 FROM wsh_regions
3112 START WITH region_id = l_region_id
3113 CONNECT BY PRIOR parent_region_id = region_id) AND
3114 z.parent_region_id = p_zone_id AND
3115 l_codeFrom = z.postal_code_from AND
3116 l_codeTo = z.postal_code_to;
3117
3118 CURSOR get_zone_level IS
3119 SELECT zone_level
3120 FROM wsh_regions
3121 WHERE region_id = p_zone_id;
3122
3123 CURSOR get_region_type IS
3124 SELECT region_type
3125 FROM wsh_regions
3126 WHERE region_id = p_region_id;
3127
3128 l_region_info wsh_regions_search_pkg.region_rec;
3129 l_existing_region_id NUMBER;
3130 l_zone_level NUMBER;
3131 l_region_count NUMBER;
3132
3133 l_region_id NUMBER;
3134 l_region_type NUMBER;
3135
3136 --
3137 l_debug_on BOOLEAN;
3138 --
3139 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ZONE_REGION';
3140 --
3141 BEGIN
3142
3143 --
3144 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3145 --
3146 IF l_debug_on IS NULL
3147 THEN
3148 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3149 END IF;
3150 --
3151 --
3152 -- Debug Statements
3153 --
3154 IF l_debug_on THEN
3155 WSH_DEBUG_SV.push(l_module_name);
3156 --
3157 WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
3158 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_REGION_ID',P_ZONE_REGION_ID);
3159 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_ID',P_ZONE_ID);
3160 WSH_DEBUG_SV.log(l_module_name,'P_REGION_ID',P_REGION_ID);
3161 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
3162 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
3163 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
3164 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
3165 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
3166 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
3167 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
3168 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
3169 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
3170 WSH_DEBUG_SV.log(l_module_name,'P_USER_ID',P_USER_ID);
3171 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_TYPE',P_ZONE_TYPE);
3172 END IF;
3173 --
3174 IF (p_zone_type = '10')
3175 THEN
3176 IF (p_region_id is null OR p_region_id = -1)
3177 THEN
3178 --
3179 -- Debug Statements
3180 --
3181 IF l_debug_on THEN
3182 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
3183 END IF;
3184 --
3185 Wsh_Regions_Search_Pkg.Get_Region_Info(
3186 p_country => p_country,
3187 p_country_region => null,
3188 p_state => p_state,
3189 p_city => p_city,
3190 p_postal_code_from => p_postal_code_from,
3191 p_postal_code_to => p_postal_code_to,
3192 p_zone => null,
3193 p_lang_code => p_lang_code,
3194 p_country_code => p_country_code,
3195 p_country_region_code => null,
3196 p_state_code => p_state_code,
3197 p_city_code => p_city_code,
3198 p_region_type => null,
3199 p_interface_flag => 'N',
3200 p_search_flag => 'Y',
3201 x_region_info => l_region_info);
3202
3203 l_region_id := l_region_info.region_id;
3204 l_region_type := l_region_info.region_type;
3205
3206 ELSE -- already have region_id, need to get region_type
3207
3208 OPEN get_region_type;
3209 FETCH get_region_type INTO l_region_type;
3210 CLOSE get_region_type;
3211
3212 l_region_id := p_region_id;
3213
3214 END IF;
3215
3216 ELSIF (p_zone_type = '11')
3217 THEN
3218 -- Bug 2418745
3219 -- For the zone of rating zone chart,
3220 -- Region_Id of the included postal code region should be of Country.
3221 -- If it is of State or city, the Parcel Carrier lanes can't be searched
3222 -- with postal codes.
3223
3224 --
3225 -- Debug Statements
3226 --
3227 IF l_debug_on THEN
3228 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
3229 END IF;
3230 --
3231 Wsh_Regions_Search_Pkg.Get_Region_Info(
3232 p_country => p_country,
3233 p_country_region => null,
3234 p_state => null,
3235 p_city => null,
3236 p_postal_code_from => null,
3237 p_postal_code_to => null,
3238 p_zone => null,
3239 p_lang_code => p_lang_code,
3240 p_country_code => p_country_code,
3241 p_country_region_code => null,
3242 p_state_code => null,
3243 p_city_code => null,
3244 p_region_type => 0,
3245 p_interface_flag => 'N',
3246 x_region_info => l_region_info);
3247
3248 l_region_id := l_region_info.region_id;
3249 l_region_type := l_region_info.region_type;
3250
3251 END IF;
3252
3253 x_region_id := l_region_id;
3254
3255 IF (l_region_id <= 0) THEN
3256
3257 x_status := 1;
3258 x_error_msg := 'WSH_REGION_NOT_FOUND';
3259 --
3260 -- Debug Statements
3261 --
3262 IF l_debug_on THEN
3263 WSH_DEBUG_SV.pop(l_module_name);
3264 END IF;
3265 --
3266 RETURN;
3267 END IF;
3268
3269 IF (p_zone_region_id > 0 AND p_insert_type = 'DELETE') THEN
3270
3271 SELECT count(*)
3272 INTO l_region_count
3273 FROM wsh_zone_regions
3274 WHERE parent_region_id = p_zone_id;
3275
3276 IF (l_region_count = 1) THEN
3277
3278 x_status := 1;
3279 x_error_msg := 'WSH_ZONE_NO_REGIONS';
3280 --
3281 -- Debug Statements
3282 --
3283 IF l_debug_on THEN
3284 WSH_DEBUG_SV.pop(l_module_name);
3285 END IF;
3286 --
3287 RETURN;
3288 END IF;
3289
3290 DELETE FROM wsh_zone_regions
3291 WHERE zone_region_id = p_zone_region_id;
3292
3293 --
3294 -- Debug Statements
3295 --
3296 IF l_debug_on THEN
3297 WSH_DEBUG_SV.pop(l_module_name);
3298 END IF;
3299 --
3300 RETURN;
3301
3302 END IF;
3303
3304 -- check to make sure this region and parents of this region
3305 -- are not already in this zone
3306
3307 OPEN check_regions_in_zone(l_region_id, p_postal_code_from, p_postal_code_to);
3308 FETCH check_regions_in_zone INTO l_existing_region_id;
3309 CLOSE check_regions_in_zone;
3310
3311 IF (l_existing_region_id IS NULL AND p_postal_code_from IS NULL) THEN
3312
3313 OPEN check_regions_in_zone_down(l_region_id);
3314 FETCH check_regions_in_zone_down INTO l_existing_region_id;
3315 CLOSE check_regions_in_zone_down;
3316
3317 IF (l_existing_region_id > 0) THEN
3318 x_status := 1;
3319 x_error_msg := 'WSH_REGION_EXISTS_IN_ZONE';
3320 --
3321 -- Debug Statements
3322 --
3323 IF l_debug_on THEN
3324 WSH_DEBUG_SV.pop(l_module_name);
3325 END IF;
3326 --
3327 RETURN;
3328 END IF;
3329
3330 END IF;
3331
3332 IF (l_existing_region_id >= 0) THEN
3333 x_status := 1;
3334 x_error_msg := 'WSH_REGION_EXISTS_IN_ZONE';
3335
3336 -- check if the region is exactly same with the existing one
3337 -- if it's the case, the error will be ignored in ZoneLoader
3338 -- to let the user load the same file without error (SYNC)
3339
3340 IF (p_postal_code_from IS NOT NULL) THEN
3341 l_existing_region_id := -1;
3342 OPEN check_same_region_in_zone(l_region_id,p_postal_code_from,p_postal_code_to );
3343 FETCH check_same_region_in_zone INTO l_existing_region_id;
3344 CLOSE check_same_region_in_zone;
3345
3346 IF (l_existing_region_id >= 0) THEN
3347 x_status := 1;
3348 x_error_msg := 'WSH_SAME_REGION_IN_ZONE';
3349 END IF;
3350 END IF;
3351
3352 --
3353 -- Debug Statements
3354 --
3355 IF l_debug_on THEN
3356 WSH_DEBUG_SV.pop(l_module_name);
3357 END IF;
3358 --
3359 RETURN;
3360 END IF;
3361
3362 IF (p_insert_type IN ('ADD','INSERT')) THEN
3363 INSERT INTO WSH_ZONE_REGIONS (
3364 ZONE_REGION_ID,
3365 REGION_ID,
3366 PARENT_REGION_ID,
3367 POSTAL_CODE_FROM,
3368 POSTAL_CODE_TO,
3369 CREATED_BY,
3370 CREATION_DATE,
3371 LAST_UPDATED_BY,
3372 LAST_UPDATE_DATE,
3373 LAST_UPDATE_LOGIN,
3374 ZONE_FLAG,
3375 PARTY_ID)
3376 VALUES (
3377 wsh_zone_regions_s.nextval,
3378 l_region_id,
3379 p_zone_id,
3380 p_postal_code_from,
3381 p_postal_code_to,
3382 p_user_id,
3383 sysdate,
3384 p_user_id,
3385 sysdate,
3386 p_user_id,
3387 'Y',
3388 -1)
3389 RETURNING zone_region_id
3390 INTO x_zone_region_id;
3391
3392 IF (x_zone_region_id IS NOT NULL) THEN
3393
3394 OPEN get_zone_level;
3395 FETCH get_zone_level INTO l_zone_level;
3396 CLOSE get_zone_level;
3397
3398 IF (l_region_type < nvl(l_zone_level,10000)) THEN
3399
3400 UPDATE wsh_regions
3401 SET zone_level = l_region_type
3402 WHERE region_id = p_zone_id;
3403
3404 END IF;
3405
3406 END IF;
3407
3408 ELSIF (p_zone_region_id > 0 AND p_insert_type IN ('UPDATE','SYNC')) THEN
3409
3410 UPDATE wsh_zone_regions
3411 SET region_id = l_region_id,
3412 postal_code_from = p_postal_code_from,
3413 postal_code_to = p_postal_code_to,
3414 last_updated_by = p_user_id,
3415 last_update_date = sysdate
3416 WHERE zone_region_id = p_zone_region_id;
3417
3418 END IF;
3419
3420 --
3421 -- Debug Statements
3422 --
3423 IF l_debug_on THEN
3424 WSH_DEBUG_SV.pop(l_module_name);
3425 END IF;
3426 --
3427 END Update_Zone_Region;
3428
3429 --
3430 -- Procedure: Lock_Zone_Region
3431 -- Parameters: p_zone_region_id - zone_region_id for zone region to be locked
3432 -- p_zone_id - zone id
3433 -- p_region_id - zone component region id
3434 -- x_return_status - Status of procedure call
3435 -- Description: This procedure will lock a zone component record. It is
3436 -- specifically designed for use by the form.
3437 --
3438
3439 PROCEDURE Lock_Zone_Region
3440 (p_zone_region_id IN NUMBER,
3441 p_zone_id IN NUMBER,
3442 p_region_id IN NUMBER,
3443 x_status OUT NOCOPY NUMBER) IS
3444
3445 CURSOR lock_row IS
3446 SELECT zone_region_id, parent_region_id , region_id
3447 FROM wsh_zone_regions
3448 WHERE zone_region_id = p_zone_region_id
3449 FOR UPDATE OF zone_region_id NOWAIT;
3450
3451 Recinfo lock_row%ROWTYPE;
3452
3453 --
3454 l_debug_on BOOLEAN;
3455 --
3456 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOCK_ZONE_REGION';
3457 --
3458 BEGIN
3459
3460 --
3461 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3462 --
3463 IF l_debug_on IS NULL
3464 THEN
3465 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3466 END IF;
3467 --
3468 --
3469 -- Debug Statements
3470 --
3471 IF l_debug_on THEN
3472 WSH_DEBUG_SV.push(l_module_name);
3473 --
3474 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_REGION_ID',P_ZONE_REGION_ID);
3475 WSH_DEBUG_SV.log(l_module_name,'P_ZONE_ID',P_ZONE_ID);
3476 WSH_DEBUG_SV.log(l_module_name,'P_REGION_ID',P_REGION_ID);
3477 END IF;
3478 --
3479 OPEN lock_row;
3480 FETCH lock_row INTO Recinfo;
3481
3482 IF (lock_row%NOTFOUND) THEN
3483 CLOSE lock_row;
3484 FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
3485 app_exception.raise_exception;
3486 END IF;
3487
3488 CLOSE lock_row;
3489
3490 IF (
3491 (Recinfo.zone_region_id = p_zone_region_id)
3492 AND (Recinfo.parent_region_id = p_zone_id)
3493 AND (Recinfo.region_id = p_region_id)
3494 ) THEN
3495 --
3496 -- Debug Statements
3497 --
3498 IF l_debug_on THEN
3499 WSH_DEBUG_SV.pop(l_module_name);
3500 END IF;
3501 --
3502 RETURN;
3503 ELSE
3504 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
3505 app_exception.raise_exception;
3506 END IF;
3507
3508 --
3509 -- Debug Statements
3510 --
3511 IF l_debug_on THEN
3512 WSH_DEBUG_SV.pop(l_module_name);
3513 END IF;
3514 --
3515 EXCEPTION
3516 WHEN others THEN
3517 if (lock_row%ISOPEN) then
3518 close lock_row;
3519 end if;
3520
3521 --
3522 -- Debug Statements
3523 --
3524 IF l_debug_on THEN
3525 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3526 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3527 END IF;
3528 --
3529 raise;
3530
3531 --
3532 -- Debug Statements
3533 --
3534 IF l_debug_on THEN
3535 WSH_DEBUG_SV.pop(l_module_name);
3536 END IF;
3537 --
3538 END Lock_Zone_Region;
3539
3540
3541 --
3542 -- Procedure: Load_Region
3543 --
3544 -- Purpose: Loads the region information into interface tables
3545 -- without any validation.
3546 --
3547
3548 PROCEDURE Load_Region (
3549 p_country_code IN VARCHAR2,
3550 p_country_region_code IN VARCHAR2,
3551 p_state_code IN VARCHAR2,
3552 p_city_code IN VARCHAR2,
3553 p_port_flag IN VARCHAR2,
3554 p_airport_flag IN VARCHAR2,
3555 p_road_terminal_flag IN VARCHAR2,
3556 p_rail_terminal_flag IN VARCHAR2,
3557 p_longitude IN NUMBER,
3558 p_latitude IN NUMBER,
3559 p_timezone IN VARCHAR2,
3560 p_continent IN VARCHAR2,
3561 p_country IN VARCHAR2,
3562 p_country_region IN VARCHAR2,
3563 p_state IN VARCHAR2,
3564 p_city IN VARCHAR2,
3565 p_alternate_name IN VARCHAR2,
3566 p_county IN VARCHAR2,
3567 p_postal_code_from IN VARCHAR2,
3568 p_postal_code_to IN VARCHAR2,
3569 p_lang_code IN VARCHAR2,
3570 p_deconsol_location_id IN NUMBER DEFAULT NULL) IS
3571
3572 l_region_id NUMBER;
3573
3574 --
3575 l_debug_on BOOLEAN;
3576 --
3577 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOAD_REGION';
3578 --
3579 BEGIN
3580
3581 --
3582 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3583 --
3584 IF l_debug_on IS NULL
3585 THEN
3586 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3587 END IF;
3588 --
3589 --
3590 -- Debug Statements
3591 --
3592 IF l_debug_on THEN
3593 WSH_DEBUG_SV.push(l_module_name);
3594 --
3595 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_CODE',P_COUNTRY_CODE);
3596 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION_CODE',P_COUNTRY_REGION_CODE);
3597 WSH_DEBUG_SV.log(l_module_name,'P_STATE_CODE',P_STATE_CODE);
3598 WSH_DEBUG_SV.log(l_module_name,'P_CITY_CODE',P_CITY_CODE);
3599 WSH_DEBUG_SV.log(l_module_name,'P_PORT_FLAG',P_PORT_FLAG);
3600 WSH_DEBUG_SV.log(l_module_name,'P_AIRPORT_FLAG',P_AIRPORT_FLAG);
3601 WSH_DEBUG_SV.log(l_module_name,'P_ROAD_TERMINAL_FLAG',P_ROAD_TERMINAL_FLAG);
3602 WSH_DEBUG_SV.log(l_module_name,'P_RAIL_TERMINAL_FLAG',P_RAIL_TERMINAL_FLAG);
3603 WSH_DEBUG_SV.log(l_module_name,'P_LONGITUDE',P_LONGITUDE);
3604 WSH_DEBUG_SV.log(l_module_name,'P_LATITUDE',P_LATITUDE);
3605 WSH_DEBUG_SV.log(l_module_name,'P_TIMEZONE',P_TIMEZONE);
3606 WSH_DEBUG_SV.log(l_module_name,'P_CONTINENT',P_CONTINENT);
3607 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY',P_COUNTRY);
3608 WSH_DEBUG_SV.log(l_module_name,'P_COUNTRY_REGION',P_COUNTRY_REGION);
3609 WSH_DEBUG_SV.log(l_module_name,'P_STATE',P_STATE);
3610 WSH_DEBUG_SV.log(l_module_name,'P_CITY',P_CITY);
3611 WSH_DEBUG_SV.log(l_module_name,'P_ALTERNATE_NAME',P_ALTERNATE_NAME);
3612 WSH_DEBUG_SV.log(l_module_name,'P_COUNTY',P_COUNTY);
3613 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_FROM',P_POSTAL_CODE_FROM);
3614 WSH_DEBUG_SV.log(l_module_name,'P_POSTAL_CODE_TO',P_POSTAL_CODE_TO);
3615 WSH_DEBUG_SV.log(l_module_name,'P_LANG_CODE',P_LANG_CODE);
3616 WSH_DEBUG_SV.log(l_module_name, 'P_DECONSOL_LOCATION_ID', P_DECONSOL_LOCATION_ID);
3617 END IF;
3618 --
3619 Add_Region(
3620 p_country_code => p_country_code,
3621 p_country_region_code => p_country_region_code,
3622 p_state_code => p_state_code,
3623 p_city_code => p_city_code,
3624 p_port_flag => p_port_flag,
3625 p_airport_flag => p_airport_flag,
3626 p_road_terminal_flag => p_road_terminal_flag,
3627 p_rail_terminal_flag => p_rail_terminal_flag,
3628 p_longitude => p_longitude,
3629 p_latitude => p_latitude,
3630 p_timezone => p_timezone,
3631 p_continent => p_continent,
3632 p_country => p_country,
3633 p_country_region => p_country_region,
3634 p_state => p_state,
3635 p_city => p_city,
3636 p_alternate_name => p_alternate_name,
3637 p_county => p_county,
3638 p_postal_code_from => p_postal_code_from,
3639 p_postal_code_to => p_postal_code_to,
3640 p_lang_code => p_lang_code,
3641 p_region_type => null,
3642 p_parent_region_id => null,
3643 p_interface_flag => 'Y',
3644 p_tl_only_flag => 'N',
3645 p_region_id => null,
3646 x_region_id => l_region_id,
3647 p_deconsol_location_id => p_deconsol_location_id);
3648
3649 --
3650 -- Debug Statements
3651 --
3652 IF l_debug_on THEN
3653 WSH_DEBUG_SV.pop(l_module_name);
3654 END IF;
3655 --
3656 END Load_Region;
3657
3658
3659 --
3660 -- Procedure: Default_Regions
3661 --
3662 -- Purpose: Select all the entries from the interface tables and
3663 -- call Update_Region on each entry to do validation
3664 --
3665
3666 PROCEDURE Default_Regions (
3667 x_status OUT NOCOPY NUMBER,
3668 x_regions_processed OUT NOCOPY NUMBER,
3669 x_error_msg_text OUT NOCOPY VARCHAR2 )
3670 IS
3671 CURSOR get_all_regions IS
3672 SELECT R.REGION_ID,
3673 R.COUNTRY_CODE,
3674 R.COUNTRY_REGION_CODE,
3675 R.STATE_CODE,
3676 R.CITY_CODE,
3677 R.PORT_FLAG,
3678 R.AIRPORT_FLAG,
3679 R.ROAD_TERMINAL_FLAG,
3680 R.RAIL_TERMINAL_FLAG,
3681 R.LONGITUDE,
3682 R.LATITUDE,
3683 R.TIMEZONE,
3684 TL.CONTINENT,
3685 TL.COUNTRY,
3686 TL.COUNTRY_REGION,
3687 TL.STATE,
3688 TL.CITY,
3689 TL.ALTERNATE_NAME,
3690 TL.COUNTY,
3691 TL.POSTAL_CODE_FROM,
3692 TL.POSTAL_CODE_TO,
3693 TL.LANGUAGE
3694 FROM WSH_REGIONS_INTERFACE R,
3695 WSH_REGIONS_TL_INTERFACE TL
3696 WHERE R.REGION_ID = TL.REGION_ID
3697 AND TL.ZONE IS NULL -- We are not processing zones here .
3698 AND PROCESSED_FLAG is null
3699 ORDER BY TL.COUNTRY, R.COUNTRY_CODE,
3700 NVL(TL.STATE, 1), NVL(R.STATE_CODE, 1),
3701 NVL(TL.CITY, 1), NVL(R.CITY_CODE, 1),
3702 NVL(TL.POSTAL_CODE_FROM, 1);
3703
3704 Rec_Region get_all_regions%ROWTYPE;
3705
3706 l_region_id NUMBER;
3707 l_num_regions NUMBER;
3708 l_status NUMBER;
3709 l_error_msg VARCHAR2(200);
3710 l_regions_processed NUMBER;
3711
3712 l_region_id_rec tab_region_id;
3713 l_country_code_rec tab_country_code;
3714 l_country_region_code_rec tab_country_region_code;
3715 l_state_code_rec tab_state_code;
3716 l_city_code_rec tab_city_code;
3717 l_port_flag_rec tab_port_flag;
3718 l_airport_flag_rec tab_airport_flag;
3719 l_road_terminal_flag_rec tab_road_terminal_flag;
3720 l_rail_terminal_flag_rec tab_rail_terminal_flag;
3721 l_longitude_rec tab_longitude;
3722 l_latitude_rec tab_latitude;
3723 l_timezone_rec tab_timezone;
3724 l_continent_rec tab_continent;
3725 l_country_rec tab_country;
3726 l_country_region_rec tab_country_region;
3727 l_state_rec tab_state;
3728 l_city_rec tab_city;
3729 l_alternate_name_rec tab_alternate_name;
3730 l_county_rec tab_county;
3731 l_postal_code_from_rec tab_postal_code_from;
3732 l_postal_code_to_rec tab_postal_code_to;
3733 l_language_rec tab_language;
3734
3735 l_prev_country WSH_REGIONS_TL.Country%TYPE;
3736 l_prev_state WSH_REGIONS_TL.State%TYPE;
3737 l_prev_city WSH_REGIONS_TL.City%TYPE;
3738 l_prev_country_code WSH_REGIONS.Country_Code%Type;
3739 l_prev_state_code WSH_REGIONS.State_Code%Type;
3740 l_prev_city_code WSH_REGIONS.City_Code%Type;
3741
3742 -- Variables for Updating and Deleting regions from Interface table in Bulk
3743 l_upd_region_id WSH_UTIL_CORE.Id_Tab_Type;
3744 l_del_region_id WSH_UTIL_CORE.Id_Tab_Type;
3745 l_upd_count NUMBER DEFAULT 0;
3746 l_del_count NUMBER DEFAULT 0;
3747
3748 l_return_status VARCHAR2(1);
3749 l_country_flag VARCHAR2(1);
3750 l_state_flag VARCHAR2(1);
3751 l_city_flag VARCHAR2(1);
3752 t1 NUMBER;
3753
3754 --
3755 l_debug_on BOOLEAN;
3756 --
3757 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DEFAULT_REGIONS';
3758 --
3759 BEGIN
3760
3761 --
3762 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3763 --
3764 IF l_debug_on IS NULL
3765 THEN
3766 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3767 END IF;
3768 --
3769 --
3770 -- Debug Statements
3771 --
3772 IF l_debug_on THEN
3773 WSH_DEBUG_SV.push(l_module_name);
3774 END IF;
3775 --
3776 x_status := 0;
3777 l_regions_processed := 0;
3778
3779 -- populate previous values to some impossible data ... like G_MISS..something.
3780 l_prev_country := FND_API.G_MISS_CHAR;
3781 l_prev_state := FND_API.G_MISS_CHAR;
3782 l_prev_city := FND_API.G_MISS_CHAR;
3783 l_prev_country_code := FND_API.G_MISS_CHAR;
3784 l_prev_state_code := FND_API.G_MISS_CHAR;
3785 l_prev_city_code := FND_API.G_MISS_CHAR;
3786
3787 OPEN get_all_regions;
3788 LOOP
3789 -- Fetching regions in bulk from Interface table (limit is 1000).
3790 FETCH get_all_regions BULK COLLECT INTO
3791 l_region_id_rec,
3792 l_country_code_rec,
3793 l_country_region_code_rec,
3794 l_state_code_rec,
3795 l_city_code_rec,
3796 l_port_flag_rec,
3797 l_airport_flag_rec,
3798 l_road_terminal_flag_rec,
3799 l_rail_terminal_flag_rec,
3800 l_longitude_rec,
3801 l_latitude_rec,
3802 l_timezone_rec,
3803 l_continent_rec,
3804 l_country_rec,
3805 l_country_region_rec,
3806 l_state_rec,
3807 l_city_rec,
3808 l_alternate_name_rec,
3809 l_county_rec,
3810 l_postal_code_from_rec,
3811 l_postal_code_to_rec,
3812 l_language_rec
3813 LIMIT 1000;
3814
3815 t1 := dbms_utility.get_time;
3816
3817 FOR I IN 1..l_region_id_rec.COUNT
3818 LOOP
3819 /*
3820 -- Validation regarding missing parameters or wrong format
3821 -- Same validatin are in the When-Validate-Record trigger on the Region block
3822 -- In WSHRGZON.fmb form
3823 */
3824 --
3825 -- Debug Statements
3826 --
3827 IF l_debug_on THEN
3828 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.VALIDATE_REGION', WSH_DEBUG_SV.C_PROC_LEVEL);
3829 END IF;
3830 --
3831 Validate_Region( p_country => l_country_rec(i),
3832 p_state => l_state_rec(i),
3833 p_city => l_city_rec(i),
3834 p_country_code => l_country_code_rec(i),
3835 p_state_code => l_state_code_rec(i),
3836 p_city_code => l_city_code_rec(i),
3837 p_postal_code_from => l_postal_code_from_rec(i),
3838 p_postal_code_to => l_postal_code_to_rec(i),
3839 x_status => l_status,
3840 x_error_msg => l_error_msg);
3841
3842 IF ( l_status = 0 ) THEN -- { IF VALIDATION SUCCESS
3843 l_country_flag := 'N';
3844 l_state_flag := 'N';
3845 l_city_flag := 'N';
3846
3847 -- Compare current values with previous values
3848 -- Current Country/Country_Code value is different from previous value
3849 IF ( nvl(l_prev_country, '-1') <> nvl(l_country_rec(i), '-1') OR
3850 nvl(l_prev_country_code, '-1') <> nvl(l_country_code_rec(i), '-1') )
3851 THEN
3852 l_country_flag := 'Y';
3853 -- Populate details of country, state and city in Global temp table
3854 -- if city or postal code is not null
3855 IF ( l_city_rec(i) is not null OR
3856 l_postal_code_from_rec(i) is not null )
3857 THEN
3858 l_state_flag := 'Y';
3859 l_city_flag := 'Y';
3860 ELSIF ( l_state_rec(i) is not null )
3861 THEN
3862 -- Populate details of country and state in Global temp table
3863 -- if state is not null
3864 l_state_flag := 'Y';
3865 END IF;
3866 -- Current State/State_Code value is different from previous value
3867 ELSIF ( nvl(l_prev_state, '-1') <> nvl(l_state_rec(i), '-1') OR
3868 nvl(l_prev_state_code, '-1') <> nvl(l_state_code_rec(i), '-1') )
3869 THEN
3870 l_state_flag := 'Y';
3871 -- Populate details of state and city in Global temp table
3872 -- if city or postal code is not null
3873 IF ( l_city_rec(i) is not null OR
3874 l_postal_code_from_rec(i) is not null )
3875 THEN
3876 l_city_flag := 'Y';
3877 END IF;
3878 -- Current City/City_Code value is different from previous value
3879 ELSIF ( nvl(l_prev_city, '-1') <> nvl(l_city_rec(i), '-1') OR
3880 nvl(l_prev_city_code, '-1') <> nvl(l_city_code_rec(i), '-1') )
3881 THEN
3882 -- Populate details of city in Global temp table
3883 l_city_flag := 'Y';
3884 END IF;
3885
3886 IF ( l_country_flag = 'Y' OR
3887 l_state_flag = 'Y' OR
3888 l_city_flag = 'Y' )
3889 THEN
3890 --
3891 -- Debug Statements
3892 --
3893 IF l_debug_on THEN
3894 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.INIT_GLOBAL_TABLE', WSH_DEBUG_SV.C_PROC_LEVEL);
3895 END IF;
3896 --
3897 -- To popualte Global temp tables
3898 Init_Global_Table(
3899 p_country => l_country_rec(i),
3900 p_state => l_state_rec(i),
3901 p_city => l_city_rec(i),
3902 p_country_code => l_country_code_rec(i),
3903 p_state_code => l_state_code_rec(i),
3904 p_city_code => l_city_code_rec(i),
3905 p_country_flag => l_country_flag,
3906 p_state_flag => l_state_flag,
3907 p_city_flag => l_city_flag,
3908 x_return_status => l_return_status );
3909
3910 -- Error Handling Part
3911 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
3912 THEN
3913 l_status := 2;
3914 l_error_msg := 'WSH_UTIL_MESSAGE_U';
3915 END IF;
3916 END IF;
3917
3918 IF ( l_status = 0 )
3919 THEN
3920 -- Storing current value into temp variable
3921 l_prev_country := l_country_rec(i);
3922 l_prev_state := l_state_rec(i);
3923 l_prev_city := l_city_rec(i);
3924 l_prev_country_code := l_country_code_rec(i);
3925 l_prev_state_code := l_state_code_rec(i);
3926 l_prev_city_code := l_city_code_rec(i);
3927
3928 --
3929 -- Debug Statements
3930 --
3931 IF l_debug_on THEN
3932 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.UPDATE_REGION', WSH_DEBUG_SV.C_PROC_LEVEL);
3933 END IF;
3934
3935 Update_Region(
3936 p_insert_type => 'SYNC',
3937 p_region_id => -1,
3938 p_parent_region_id => -1,
3939 p_continent => l_continent_rec(i),
3940 p_country => l_country_rec(i),
3941 p_country_region => l_country_region_rec(i),
3942 p_state => l_state_rec(i),
3943 p_city => l_city_rec(i),
3944 p_alternate_name => l_alternate_name_rec(i),
3945 p_county => l_county_rec(i),
3946 p_postal_code_from => l_postal_code_from_rec(i),
3947 p_postal_code_to => l_postal_code_to_rec(i),
3948 p_lang_code => l_language_rec(i),
3949 p_country_code => l_country_code_rec(i),
3950 p_country_region_code => l_country_region_code_rec(i),
3951 p_state_code => l_state_code_rec(i),
3952 p_city_code => l_city_code_rec(i),
3953 p_port_flag => l_port_flag_rec(i),
3954 p_airport_flag => l_airport_flag_rec(i),
3955 p_road_terminal_flag => l_road_terminal_flag_rec(i),
3956 p_rail_terminal_flag => l_rail_terminal_flag_rec(i),
3957 p_longitude => l_longitude_rec(i),
3958 p_latitude => l_latitude_rec(i),
3959 p_timezone => l_timezone_rec(i),
3960 p_interface_flag => 'N',
3961 p_user_id => -1,
3962 p_insert_parent_flag => 'Y',
3963 x_region_id => l_region_id,
3964 x_status => l_status,
3965 x_error_msg => l_error_msg,
3966 p_conc_request_flag => 'Y'); -- p_conc_request_flag
3967
3968 END IF;
3969 END IF; -- } IF VALIDATION SUCCESS
3970
3971 IF (l_status = 2) THEN
3972 x_status := 2;
3973 x_error_msg_text := l_error_msg;
3974
3975 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));
3976 fnd_file.put_line(fnd_file.log,' error message: '||fnd_message.get_string('WSH',l_error_msg));
3977
3978 -- Bulk Update has to be done after processing 1000 records
3979 l_upd_count := l_upd_count + 1;
3980 l_upd_region_id(l_upd_count) := l_region_id_rec(i);
3981 ELSE
3982 l_regions_processed := l_regions_processed + 1;
3983
3984 -- Bulk Delete has to be done after processing 1000 records
3985 l_del_count := l_del_count + 1;
3986 l_del_region_id(l_del_count) := l_region_id_rec(i);
3987 END IF;
3988 END LOOP;
3989
3990 -- Bulk Updation in WSH_REGIONS_INTERFACE table
3991 IF ( l_upd_count > 0 ) THEN
3992 l_upd_count := 0;
3993
3994 FORALL i in l_upd_region_id.first..l_upd_region_id.last
3995 UPDATE wsh_regions_interface
3996 SET processed_flag = 'Y'
3997 WHERE region_id = l_upd_region_id(i);
3998
3999 -- Deleting region ids from array
4000 l_upd_region_id.DELETE;
4001 END IF;
4002
4003 -- Bulk Deletion in WSH_REGIONS_INTERFACE table
4004 IF ( l_del_count > 0 ) THEN
4005 l_del_count := 0;
4006 FORALL i in l_del_region_id.first..l_del_region_id.last
4007 DELETE FROM WSH_REGIONS_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4008
4009 FORALL i in l_del_region_id.first..l_del_region_id.last
4010 DELETE FROM WSH_REGIONS_TL_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4011
4012 -- Deleting region ids from array
4013 l_del_region_id.DELETE;
4014 END IF;
4015
4016 fnd_file.put_line(fnd_file.log, 'TIME TAKEN FOR PROCESSING 1000 RECORDS : ' || ((dbms_utility.get_time - t1)/100));
4017
4018 COMMIT;
4019 EXIT WHEN get_all_regions%NOTFOUND;
4020 END LOOP;
4021
4022 CLOSE get_all_regions;
4023
4024 x_regions_processed := l_regions_processed;
4025
4026 -- Truncating records from Global Temp Tables
4027 delete from wsh_regions_global_data;
4028 delete from wsh_regions_global;
4029 COMMIT;
4030 EXCEPTION
4031 WHEN OTHERS THEN
4032 x_status := 2;
4033 x_error_msg_text := 'Error ' || sqlcode || ': ' || sqlerrm;
4034
4035 --
4036 -- Debug Statements
4037 --
4038 IF l_debug_on THEN
4039 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4040 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4041 END IF;
4042 --
4043 END Default_Regions;
4044
4045 --
4046 -- Procedure: Default_Regions (for concurrent program usage)
4047 --
4048 -- Purpose: Copies regions from the interface tables to
4049 -- the real regions tables
4050 --
4051
4052 PROCEDURE Default_Regions (
4053 p_dummy1 IN VARCHAR2,
4054 p_dummy2 IN VARCHAR2 )
4055 IS
4056 l_status NUMBER;
4057 l_regions_processed NUMBER;
4058 l_error_msg_text VARCHAR2(1000);
4059
4060 CURSOR total_regions IS
4061 SELECT count(*)
4062 FROM wsh_Regions_interface
4063 WHERE processed_flag is null;
4064
4065 CURSOR more_regions IS
4066 SELECT 1
4067 FROM wsh_Regions_interface
4068 WHERE processed_flag is null;
4069
4070 l_tmp NUMBER;
4071 l_good_count NUMBER := 0;
4072 l_bad_count NUMBER := 0;
4073 l_total_regions NUMBER;
4074
4075 --
4076 l_debug_on BOOLEAN;
4077 --
4078 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DEFAULT_REGIONS';
4079 --
4080 BEGIN
4081
4082 --
4083 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4084 --
4085 IF l_debug_on IS NULL
4086 THEN
4087 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4088 END IF;
4089 --
4090 --
4091 -- Debug Statements
4092 --
4093 IF l_debug_on THEN
4094 WSH_DEBUG_SV.push(l_module_name);
4095 --
4096 WSH_DEBUG_SV.log(l_module_name,'P_DUMMY1',P_DUMMY1);
4097 WSH_DEBUG_SV.log(l_module_name,'P_DUMMY2',P_DUMMY2);
4098 END IF;
4099 --
4100
4101 OPEN total_regions;
4102 FETCH total_regions INTO l_total_regions;
4103 CLOSE total_regions;
4104 fnd_file.put_line(fnd_file.log,'Started the region upload process...');
4105
4106 IF (l_total_regions > 0) THEN
4107 Default_Regions(l_status, l_regions_processed, l_error_msg_text);
4108
4109 l_good_count := l_regions_processed;
4110 l_bad_count := l_total_regions - l_regions_processed;
4111
4112 fnd_file.put_line(fnd_file.log,'Summary: Total regions processed = '||l_total_regions);
4113 fnd_file.put_line(fnd_file.log,'Summary: Number of new regions = '||l_good_count);
4114 fnd_file.put_line(fnd_file.log,'Summary: Number of regions with errors (not interfaced) = '||l_bad_count);
4115
4116 fnd_file.put_line(fnd_file.log,'Ended the region upload process.');
4117 ELSE
4118 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.');
4119
4120 END IF;
4121
4122 --
4123 -- Debug Statements
4124 --
4125 IF l_debug_on THEN
4126 WSH_DEBUG_SV.pop(l_module_name);
4127 END IF;
4128 --
4129 END Default_Regions;
4130
4131
4132 -- This method in only for the purpose of submitting a request from the form
4133
4134 FUNCTION Load_All_Regions RETURN NUMBER IS
4135
4136 l_request_id NUMBER := 0;
4137
4138 --
4139 l_debug_on BOOLEAN;
4140 --
4141 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'LOAD_ALL_REGIONS';
4142 --
4143 BEGIN
4144 --
4145 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4146 --
4147 IF l_debug_on IS NULL
4148 THEN
4149 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4150 END IF;
4151 --
4152 --
4153 -- Debug Statements
4154 --
4155 IF l_debug_on THEN
4156 WSH_DEBUG_SV.push(l_module_name);
4157 END IF;
4158 --
4159 --
4160 -- Debug Statements
4161 --
4162 IF l_debug_on THEN
4163 WSH_DEBUG_SV.pop(l_module_name);
4164 END IF;
4165 --
4166 return fnd_request.submit_request('WSH','WSHRGINT','','',FALSE);
4167
4168 END Load_All_Regions;
4169
4170
4171 -- used by FTE_CAT_ZONE_LOV to display Regions that belong to a Zone
4172
4173 FUNCTION getZoneRegions(p_zoneId IN NUMBER, p_lang IN VARCHAR2)
4174 return VARCHAR2
4175 AS
4176 CURSOR zoneRegion_cur (p_zoneId number, p_lang varchar2)
4177 IS
4178 SELECT T.REGION_ID REGION_ID, T.COUNTRY COUNTRY, T.STATE STATE, T.CITY CITY,
4179 ltrim(P.POSTAL_CODE_FROM,'0') PCODE_FROM,
4180 ltrim(P.POSTAL_CODE_TO,'0') PCODE_TO
4181 FROM WSH_REGIONS_TL T, WSH_ZONE_REGIONS P
4182 WHERE T.LANGUAGE = p_lang
4183 AND T.REGION_ID = P.REGION_ID
4184 AND P.PARENT_REGION_ID = p_zoneId;
4185
4186 regions VARCHAR2(100) := null;
4187
4188 t_region VARCHAR2(300);
4189 t_region_list VARCHAR2(300);
4190 t_region_id VARCHAR2(10);
4191 t_country VARCHAR2(100);
4192 t_state VARCHAR2(100);
4193 t_city VARCHAR2(100);
4194 t_pcode_from VARCHAR2(100);
4195 t_pcode_to VARCHAR2(100);
4196
4197 --
4198 l_debug_on BOOLEAN;
4199 --
4200 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GETZONEREGIONS';
4201 --
4202 BEGIN
4203
4204 --
4205 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4206 --
4207 IF l_debug_on IS NULL
4208 THEN
4209 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4210 END IF;
4211 --
4212 --
4213 -- Debug Statements
4214 --
4215 IF l_debug_on THEN
4216 WSH_DEBUG_SV.push(l_module_name);
4217 --
4218 WSH_DEBUG_SV.log(l_module_name,'P_ZONEID',P_ZONEID);
4219 WSH_DEBUG_SV.log(l_module_name,'P_LANG',P_LANG);
4220 END IF;
4221 --
4222 for c1 in zoneRegion_cur(p_zoneId, p_lang) loop
4223
4224 t_region_id := trim(c1.region_id);
4225 t_country := trim(c1.country);
4226 t_state := trim(c1.state);
4227 t_city := trim(c1.city);
4228 t_pcode_from := trim(c1.pcode_from);
4229 t_pcode_to := trim(c1.pcode_to);
4230
4231 t_region := null;
4232 t_region_list := t_region_list || ':' || t_region_id;
4233
4234 if (t_country is not null) then
4235 t_region := t_country;
4236 end if;
4237
4238 if (t_state is not null) then
4239 t_region := t_region||' - '||t_state;
4240 end if;
4241
4242 if (t_city is not null) then
4243 t_region := t_region||' - '||t_city;
4244 end if;
4245
4246 if (t_pcode_from is not null) then
4247 t_region := t_region||' - '||t_pcode_from;
4248 end if;
4249
4250 if (t_pcode_to is not null) then
4251 t_region := t_region||' - '||t_pcode_to;
4252 end if;
4253
4254 if (t_region is not null) then
4255 if (regions is null) then
4256 regions := t_region;
4257 else
4258 regions := regions||', '||t_region;
4259 end if;
4260 end if;
4261 end loop;
4262
4263 if (p_lang <> 'US') then
4264
4265 for c1 in zoneRegion_cur(p_zoneId, 'US') loop
4266
4267 t_region_id := trim(c1.region_id);
4268 t_country := trim(c1.country);
4269 t_state := trim(c1.state);
4270 t_city := trim(c1.city);
4271 t_pcode_from := trim(c1.pcode_from);
4272 t_pcode_to := trim(c1.pcode_to);
4273
4274 t_region := null;
4275
4276 -- only if the region for p_lang is not there
4277 if (t_region_list is null OR
4278 instr(t_region_list, ':'||t_region_id) = 0)
4279 then
4280
4281 if (t_country is not null) then
4282 t_region := t_country;
4283 end if;
4284
4285 if (t_state is not null) then
4286 t_region := t_region||' - '||t_state;
4287 end if;
4288
4289 if (t_city is not null) then
4290 t_region := t_region||' - '||t_city;
4291 end if;
4292
4293 if (t_pcode_from is not null) then
4294 t_region := t_region||' - '||t_pcode_from;
4295 end if;
4296
4297 if (t_pcode_to is not null) then
4298 t_region := t_region||' - '||t_pcode_to;
4299 end if;
4300
4301 if (t_region is not null) then
4302 if (regions is null) then
4303 regions := t_region;
4304 else
4305 regions := regions||', '||t_region;
4306 end if;
4307 end if;
4308
4309 end if;
4310
4311 end loop;
4312
4313 end if;
4314
4315 --
4316 -- Debug Statements
4317 --
4318 IF l_debug_on THEN
4319 WSH_DEBUG_SV.pop(l_module_name);
4320 END IF;
4321 --
4322 RETURN regions;
4323 EXCEPTION when OTHERS then
4324 --
4325 -- Debug Statements
4326 --
4327 IF l_debug_on THEN
4328 WSH_DEBUG_SV.pop(l_module_name);
4329 END IF;
4330 --
4331 return regions||' ...';
4332 --
4333 -- Debug Statements
4334 --
4335 IF l_debug_on THEN
4336 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '|| SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
4337 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
4338 END IF;
4339 --
4340 END;
4341
4342 /*----------------------------------------------------------*/
4343 /* Add_Language Procedure */
4344 /*----------------------------------------------------------*/
4345 procedure ADD_LANGUAGE
4346 is
4347 begin
4348 delete from WSH_REGIONS_TL T
4349 where not exists
4350 (select NULL
4351 from WSH_REGIONS B
4352 where B.REGION_ID = T.REGION_ID
4353 );
4354
4355 update WSH_REGIONS_TL T set (
4356 CONTINENT,
4357 COUNTRY,
4358 COUNTRY_REGION,
4359 STATE,
4360 CITY,
4361 ZONE,
4362 POSTAL_CODE_FROM,
4363 POSTAL_CODE_TO,
4364 ALTERNATE_NAME,
4365 COUNTY
4366 ) = (select
4367 B.CONTINENT,
4368 B.COUNTRY,
4369 B.COUNTRY_REGION,
4370 B.STATE,
4371 B.CITY,
4372 B.ZONE,
4373 B.POSTAL_CODE_FROM,
4374 B.POSTAL_CODE_TO,
4375 B.ALTERNATE_NAME,
4376 B.COUNTY
4377 from WSH_REGIONS_TL B
4378 where B.REGION_ID = T.REGION_ID
4379 and B.LANGUAGE = T.SOURCE_LANG)
4380 where (
4381 T.REGION_ID,
4382 T.LANGUAGE
4383 ) in (select
4384 SUBT.REGION_ID,
4385 SUBT.LANGUAGE
4386 from WSH_REGIONS_TL SUBB, WSH_REGIONS_TL SUBT
4387 where SUBB.REGION_ID = SUBT.REGION_ID
4388 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
4389 and (SUBB.CONTINENT <> SUBT.CONTINENT
4390 or (SUBB.CONTINENT is null and SUBT.CONTINENT is not null)
4391 or (SUBB.CONTINENT is not null and SUBT.CONTINENT is null)
4392 or SUBB.COUNTRY <> SUBT.COUNTRY
4393 or (SUBB.COUNTRY is null and SUBT.COUNTRY is not null)
4394 or (SUBB.COUNTRY is not null and SUBT.COUNTRY is null)
4395 or SUBB.COUNTRY_REGION <> SUBT.COUNTRY_REGION
4396 or (SUBB.COUNTRY_REGION is null and SUBT.COUNTRY_REGION is not null)
4397 or (SUBB.COUNTRY_REGION is not null and SUBT.COUNTRY_REGION is null)
4398 or SUBB.STATE <> SUBT.STATE
4399 or (SUBB.STATE is null and SUBT.STATE is not null)
4400 or (SUBB.STATE is not null and SUBT.STATE is null)
4401 or SUBB.CITY <> SUBT.CITY
4402 or (SUBB.CITY is null and SUBT.CITY is not null)
4403 or (SUBB.CITY is not null and SUBT.CITY is null)
4404 or SUBB.ZONE <> SUBT.ZONE
4405 or (SUBB.ZONE is null and SUBT.ZONE is not null)
4406 or (SUBB.ZONE is not null and SUBT.ZONE is null)
4407 or SUBB.POSTAL_CODE_FROM <> SUBT.POSTAL_CODE_FROM
4408 or (SUBB.POSTAL_CODE_FROM is null and SUBT.POSTAL_CODE_FROM is not null)
4409 or (SUBB.POSTAL_CODE_FROM is not null and SUBT.POSTAL_CODE_FROM is null)
4410 or SUBB.POSTAL_CODE_TO <> SUBT.POSTAL_CODE_TO
4411 or (SUBB.POSTAL_CODE_TO is null and SUBT.POSTAL_CODE_TO is not null)
4412 or (SUBB.POSTAL_CODE_TO is not null and SUBT.POSTAL_CODE_TO is null)
4413 or SUBB.ALTERNATE_NAME <> SUBT.ALTERNATE_NAME
4414 or (SUBB.ALTERNATE_NAME is null and SUBT.ALTERNATE_NAME is not null)
4415 or (SUBB.ALTERNATE_NAME is not null and SUBT.ALTERNATE_NAME is null)
4416 or SUBB.COUNTY <> SUBT.COUNTY
4417 or (SUBB.COUNTY is null and SUBT.COUNTY is not null)
4418 or (SUBB.COUNTY is not null and SUBT.COUNTY is null)
4419 ));
4420
4421 insert into WSH_REGIONS_TL (
4422 REGION_ID,
4423 CONTINENT,
4424 COUNTRY,
4425 COUNTRY_REGION,
4426 STATE,
4427 CITY,
4428 ZONE,
4429 POSTAL_CODE_FROM,
4430 POSTAL_CODE_TO,
4431 ALTERNATE_NAME,
4432 COUNTY,
4433 LAST_UPDATE_DATE,
4434 LAST_UPDATED_BY,
4435 CREATION_DATE,
4436 CREATED_BY,
4437 LAST_UPDATE_LOGIN,
4438 LANGUAGE,
4439 SOURCE_LANG
4440 ) select
4441 B.REGION_ID,
4442 B.CONTINENT,
4443 B.COUNTRY,
4444 B.COUNTRY_REGION,
4445 B.STATE,
4446 B.CITY,
4447 B.ZONE,
4448 B.POSTAL_CODE_FROM,
4449 B.POSTAL_CODE_TO,
4450 B.ALTERNATE_NAME,
4451 B.COUNTY,
4452 B.LAST_UPDATE_DATE,
4453 B.LAST_UPDATED_BY,
4454 B.CREATION_DATE,
4455 B.CREATED_BY,
4456 B.LAST_UPDATE_LOGIN,
4457 L.LANGUAGE_CODE,
4458 B.SOURCE_LANG
4459 from WSH_REGIONS_TL B, FND_LANGUAGES L
4460 where L.INSTALLED_FLAG in ('I', 'B')
4461 and B.LANGUAGE = userenv('LANG')
4462 and not exists
4463 (select NULL
4464 from WSH_REGIONS_TL T
4465 where T.REGION_ID = B.REGION_ID
4466 and T.LANGUAGE = L.LANGUAGE_CODE);
4467 end ADD_LANGUAGE;
4468
4469 -- Following procedure are added for Regions Interface Performance
4470
4471 --
4472 -- PROCEDURE : Validate_Region
4473 --
4474 -- PURPOSE : Validation regarding missing parameters or wrong format
4475 -- Same validatin are in the When-Validate-Record trigger
4476 -- on the Region block in WSHRGZON.fmb form
4477 PROCEDURE Validate_Region (
4478 p_country IN VARCHAR2,
4479 p_state IN VARCHAR2,
4480 p_city IN VARCHAR2,
4481 p_country_code IN VARCHAR2,
4482 p_state_code IN VARCHAR2,
4483 p_city_code IN VARCHAR2,
4484 p_postal_code_from IN VARCHAR2,
4485 p_postal_code_to IN VARCHAR2,
4486 x_status OUT NOCOPY NUMBER ,
4487 x_error_msg OUT NOCOPY VARCHAR2 )
4488 IS
4489 --
4490 l_debug_on BOOLEAN;
4491 --
4492 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_REGION';
4493 --
4494 BEGIN
4495 --
4496 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4497 --
4498 IF l_debug_on IS NULL
4499 THEN
4500 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4501 END IF;
4502 --
4503 --
4504 -- Debug Statements
4505 --
4506 IF l_debug_on THEN
4507 WSH_DEBUG_SV.push(l_module_name);
4508 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
4509 WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
4510 WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
4511 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
4512 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
4513 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
4514 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
4515 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
4516 END IF;
4517 --
4518
4519 IF (length(p_postal_code_from) <> length(p_postal_code_to)) THEN
4520 x_status := 2;
4521 x_error_msg := 'WSH_POSTAL_CODE_WRONG_FORMAT';
4522 --
4523 -- Debug Statements
4524 --
4525 IF l_debug_on THEN
4526 WSH_DEBUG_SV.pop(l_module_name);
4527 END IF;
4528 --
4529 return;
4530 END IF;
4531
4532 IF (p_postal_code_from > p_postal_code_to) THEN
4533 x_status := 2;
4534 x_error_msg := 'WSH_POSTAL_CODE_RANGE_ERR';
4535 --
4536 -- Debug Statements
4537 --
4538 IF l_debug_on THEN
4539 WSH_DEBUG_SV.pop(l_module_name);
4540 END IF;
4541 --
4542 return;
4543 END IF;
4544
4545 IF ( ( (p_postal_code_from is not null) AND (p_postal_code_to is null) ) OR
4546 ( (p_postal_code_from is null) AND (p_postal_code_to is not null) ) ) -- Same bug fixed in Pack J.
4547 THEN
4548 x_status := 2;
4549 x_error_msg := 'WSH_CAT_P_CODE_RANGE_INCOMP';
4550 --
4551 -- Debug Statements
4552 --
4553 IF l_debug_on THEN
4554 WSH_DEBUG_SV.pop(l_module_name);
4555 END IF;
4556 --
4557 return;
4558 END IF;
4559
4560 IF (p_country is null and p_country_code is not null ) THEN
4561 x_status := 2;
4562 x_error_msg := 'WSH_COUNTRY_OR_CODE_MISSING';
4563 --
4564 -- Debug Statements
4565 --
4566 IF l_debug_on THEN
4567 WSH_DEBUG_SV.pop(l_module_name);
4568 END IF;
4569 --
4570 return;
4571 END IF;
4572
4573 IF (p_state is null and p_state_code is not null ) THEN
4574 x_status := 2;
4575 x_error_msg := 'WSH_STATE_OR_CODE_MISSING';
4576 --
4577 -- Debug Statements
4578 --
4579 IF l_debug_on THEN
4580 WSH_DEBUG_SV.pop(l_module_name);
4581 END IF;
4582 --
4583 return;
4584 END IF;
4585
4586 IF (p_city is null and p_city_code is not null ) THEN
4587 x_status := 2;
4588 x_error_msg := 'WSH_CITY_OR_CODE_MISSING';
4589 --
4590 -- Debug Statements
4591 --
4592 IF l_debug_on THEN
4593 WSH_DEBUG_SV.pop(l_module_name);
4594 END IF;
4595 --
4596 return;
4597 END IF;
4598
4599 --
4600 -- Debug Statements
4601 --
4602 IF l_debug_on THEN
4603 WSH_DEBUG_SV.pop(l_module_name);
4604 END IF;
4605 --
4606
4607 x_status := 0;
4608
4609 -- Exception Handling part
4610 EXCEPTION
4611 WHEN OTHERS THEN
4612 x_status := 2;
4613 x_error_msg := 'WSH_UTIL_MESSAGE_U';
4614 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
4615 fnd_message.set_token('MSG_TEXT', SQLERRM);
4616 fnd_file.put_line(fnd_file.log, 'INSIDE VALIDATE_REGION EXCEPTION : ' || sqlerrm);
4617 END Validate_Region;
4618
4619 --
4620 -- PROCEDURE : Init_Global_Table
4621 --
4622 -- PURPOSE : Populates the data in Global Temp tables(Wsh_Regions_Global
4623 -- and Wsh_Regions_Global_Data) fetched from Wsh_Regions and
4624 -- Wsh_Regions_Tl based on parameter p_populate_type.
4625 --
4626
4627 PROCEDURE Init_Global_Table (
4628 p_country IN VARCHAR2,
4629 p_state IN VARCHAR2,
4630 p_city IN VARCHAR2,
4631 p_country_code IN VARCHAR2,
4632 p_state_code IN VARCHAR2,
4633 p_city_code IN VARCHAR2,
4634 p_country_flag IN VARCHAR2,
4635 p_state_flag IN VARCHAR2,
4636 p_city_flag IN VARCHAR2,
4637 x_return_status OUT NOCOPY VARCHAR2 )
4638 AS
4639
4640 CURSOR city_cur IS
4641 SELECT DISTINCT UPPER(STATE)
4642 FROM WSH_REGIONS_TL
4643 WHERE UPPER(CITY) = UPPER(p_city)
4644 AND UPPER(COUNTRY) = UPPER(p_country);
4645 -- AND LANGUAGE = p_lang_code;
4646
4647 CURSOR city_cur_code IS
4648 SELECT DISTINCT UPPER(R.STATE_CODE)
4649 FROM WSH_REGIONS R
4650 WHERE ( UPPER(R.CITY_CODE) = UPPER(p_city_code) OR p_city_code is null )
4651 AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4652 AND REGION_TYPE = 2;
4653
4654 TYPE tab_code is TABLE OF VARCHAR2(10) index by BINARY_INTEGER;
4655 TYPE tab_desc is TABLE OF VARCHAR2(60) index by BINARY_INTEGER;
4656
4657 tab_tmp_state tab_desc;
4658 tab_tmp_state_code tab_code;
4659
4660 l_tmp_state VARCHAR2(3000);
4661 l_tmp_state_code VARCHAR2(500);
4662 t1 NUMBER;
4663
4664 --
4665 l_debug_on BOOLEAN;
4666 --
4667 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INIT_GLOBAL_TABLE';
4668 --
4669 BEGIN
4670 --
4671 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
4672 --
4673 IF l_debug_on IS NULL
4674 THEN
4675 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
4676 END IF;
4677 --
4678 --
4679 -- Debug Statements
4680 --
4681 IF l_debug_on THEN
4682 WSH_DEBUG_SV.push(l_module_name);
4683 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
4684 WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
4685 WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
4686 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
4687 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
4688 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
4689 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_FLAG', P_COUNTRY_FLAG);
4690 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_FLAG', P_STATE_FLAG);
4691 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_FLAG', P_CITY_FLAG);
4692 END IF;
4693 --
4694
4695 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4696 t1 := dbms_utility.get_time;
4697
4698 IF ( p_country_flag = 'Y' ) THEN -- { Region type 0 i.e., Country
4699 DELETE FROM WSH_REGIONS_GLOBAL_DATA;
4700
4701 --
4702 -- Debug Statements
4703 --
4704 IF l_debug_on THEN
4705 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4706 END IF;
4707 --
4708
4709 INSERT INTO wsh_regions_global_data
4710 ( REGION_ID,
4711 REGION_TYPE,
4712 PARENT_REGION_ID,
4713 COUNTRY,
4714 STATE,
4715 CITY,
4716 COUNTRY_CODE,
4717 STATE_CODE,
4718 CITY_CODE,
4719 POSTAL_CODE_FROM,
4720 POSTAL_CODE_TO,
4721 LANGUAGE )
4722 SELECT R.REGION_ID,
4723 R.REGION_TYPE,
4724 R.PARENT_REGION_ID,
4725 UPPER(TL.COUNTRY),
4726 UPPER(TL.STATE),
4727 UPPER(TL.CITY),
4728 UPPER(R.COUNTRY_CODE),
4729 UPPER(R.STATE_CODE),
4730 UPPER(R.CITY_CODE),
4731 TL.POSTAL_CODE_FROM,
4732 TL.POSTAL_CODE_TO,
4733 TL.LANGUAGE
4734 FROM WSH_REGIONS R,
4735 WSH_REGIONS_TL TL
4736 WHERE R.REGION_ID = TL.REGION_ID
4737 AND R.REGION_TYPE = 0
4738 AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4739 OR UPPER(TL.COUNTRY) = UPPER(p_country) );
4740
4741 --
4742 -- Debug Statements
4743 --
4744 IF l_debug_on THEN
4745 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
4746 END IF;
4747 --
4748
4749 DELETE FROM WSH_REGIONS_GLOBAL;
4750
4751 --
4752 -- Debug Statements
4753 --
4754 IF l_debug_on THEN
4755 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
4756 END IF;
4757 --
4758
4759 INSERT INTO wsh_regions_global
4760 ( REGION_ID,
4761 REGION_TYPE,
4762 COUNTRY_CODE,
4763 STATE_CODE,
4764 CITY_CODE )
4765 SELECT R.REGION_ID,
4766 R.REGION_TYPE,
4767 UPPER(R.COUNTRY_CODE),
4768 UPPER(R.STATE_CODE),
4769 UPPER(R.CITY_CODE)
4770 FROM WSH_REGIONS R
4771 WHERE R.REGION_TYPE = 0
4772 AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code);
4773
4774 --
4775 -- Debug Statements
4776 --
4777 IF l_debug_on THEN
4778 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
4779 END IF;
4780 --
4781 END IF; -- } Region Type 0 ie., Country
4782
4783 IF ( p_state_flag = 'Y' ) THEN -- { Region type 1 i.e., State
4784
4785 -- If State is NULL and City is NOT NULL then we need get States
4786 -- corresponding to City passed.
4787 IF ( (p_city is not null and p_state is null) )
4788 THEN
4789 OPEN city_cur;
4790 FETCH city_cur BULK COLLECT INTO tab_tmp_state;
4791 CLOSE city_cur;
4792
4793 FOR i in 1..tab_tmp_state.COUNT
4794 LOOP
4795 IF ( i = 1 ) THEN
4796 l_tmp_state := tab_tmp_state(i);
4797 ELSE
4798 l_tmp_state := l_tmp_state || ', ' || tab_tmp_state(i);
4799 END IF;
4800 END LOOP;
4801 END IF;
4802
4803 -- If State_Code is NULL and City_Code is NOT NULL then we need get
4804 -- State_Codes corresponding to City_Code passed.
4805 IF ( (p_city_code is not null and p_state_code is null) )
4806 THEN
4807 OPEN city_cur_code;
4808 FETCH city_cur_code BULK COLLECT INTO tab_tmp_state_code;
4809 CLOSE city_cur_code;
4810
4811 FOR i in 1..tab_tmp_state_code.COUNT
4812 LOOP
4813 IF ( i = 1 ) THEN
4814 l_tmp_state_code := tab_tmp_state_code(i);
4815 ELSE
4816 l_tmp_state_code := l_tmp_state_code || ', ' || tab_tmp_state_code(i);
4817 END IF;
4818 END LOOP;
4819 END IF;
4820
4821 DELETE FROM WSH_REGIONS_GLOBAL_DATA
4822 WHERE REGION_TYPE in ( 1, 2, 3 );
4823
4824 --
4825 -- Debug Statements
4826 --
4827 IF l_debug_on THEN
4828 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4829 END IF;
4830 --
4831
4832 INSERT INTO wsh_regions_global_data
4833 ( REGION_ID,
4834 REGION_TYPE,
4835 PARENT_REGION_ID,
4836 COUNTRY,
4837 STATE,
4838 CITY,
4839 COUNTRY_CODE,
4840 STATE_CODE,
4841 CITY_CODE,
4842 POSTAL_CODE_FROM,
4843 POSTAL_CODE_TO,
4844 LANGUAGE )
4845 SELECT R.REGION_ID,
4846 R.REGION_TYPE,
4847 R.PARENT_REGION_ID,
4848 UPPER(TL.COUNTRY),
4849 UPPER(TL.STATE),
4850 UPPER(TL.CITY),
4851 UPPER(R.COUNTRY_CODE),
4852 UPPER(R.STATE_CODE),
4853 UPPER(R.CITY_CODE),
4854 TL.POSTAL_CODE_FROM,
4855 TL.POSTAL_CODE_TO,
4856 TL.LANGUAGE
4857 FROM WSH_REGIONS R,
4858 WSH_REGIONS_TL TL
4859 WHERE R.REGION_ID = TL.REGION_ID
4860 AND R.REGION_TYPE = 1
4861 AND (
4862 ( ( UPPER(R.STATE_CODE) = UPPER(p_state_code)
4863 OR ( UPPER(R.STATE_CODE) in ( l_tmp_state_code ) )
4864 OR ( p_state_code is NULL and p_city_code is null ) ) )
4865 OR ( ( UPPER(TL.STATE) = UPPER(p_state)
4866 OR ( UPPER(TL.STATE) in ( l_tmp_state ) )
4867 OR ( p_state is null and p_city is null) ) )
4868 )
4869 AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4870 OR UPPER(TL.COUNTRY) = UPPER(p_country) );
4871
4872 --
4873 -- Debug Statements
4874 --
4875 IF l_debug_on THEN
4876 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
4877 END IF;
4878 --
4879
4880 DELETE FROM WSH_REGIONS_GLOBAL
4881 WHERE REGION_TYPE in ( 1, 2, 3 );
4882
4883 --
4884 -- Debug Statements
4885 --
4886 IF l_debug_on THEN
4887 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
4888 END IF;
4889 --
4890
4891 INSERT INTO wsh_regions_global
4892 ( REGION_ID,
4893 REGION_TYPE,
4894 COUNTRY_CODE,
4895 STATE_CODE,
4896 CITY_CODE )
4897 SELECT R.REGION_ID,
4898 R.REGION_TYPE,
4899 UPPER(R.COUNTRY_CODE),
4900 UPPER(R.STATE_CODE),
4901 UPPER(R.CITY_CODE)
4902 FROM WSH_REGIONS R
4903 WHERE R.REGION_TYPE = 1
4904 AND ( UPPER(R.STATE_CODE) = UPPER(p_state_code)
4905 OR ( UPPER(R.STATE_CODE) in ( l_tmp_state_code ) )
4906 OR ( p_state_code is NULL and p_city_code is null) )
4907 AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code);
4908
4909 --
4910 -- Debug Statements
4911 --
4912 IF l_debug_on THEN
4913 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
4914 END IF;
4915 --
4916
4917 END IF; -- } Region type 1 i.e., State
4918
4919 IF ( p_city_flag = 'Y' ) THEN -- { Region type 2,3 i.e., City, Postal Codes
4920
4921 DELETE FROM WSH_REGIONS_GLOBAL_DATA
4922 WHERE REGION_TYPE in ( 2, 3 );
4923
4924 --
4925 -- Debug Statements
4926 --
4927 IF l_debug_on THEN
4928 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4929 END IF;
4930 --
4931
4932 INSERT INTO wsh_regions_global_data
4933 ( REGION_ID,
4934 REGION_TYPE,
4935 PARENT_REGION_ID,
4936 COUNTRY,
4937 STATE,
4938 CITY,
4939 COUNTRY_CODE,
4940 STATE_CODE,
4941 CITY_CODE,
4942 POSTAL_CODE_FROM,
4943 POSTAL_CODE_TO,
4944 LANGUAGE )
4945 SELECT R.REGION_ID,
4946 R.REGION_TYPE,
4947 R.PARENT_REGION_ID,
4948 UPPER(TL.COUNTRY),
4949 UPPER(TL.STATE),
4950 UPPER(TL.CITY),
4951 UPPER(R.COUNTRY_CODE),
4952 UPPER(R.STATE_CODE),
4953 UPPER(R.CITY_CODE),
4954 TL.POSTAL_CODE_FROM,
4955 TL.POSTAL_CODE_TO,
4956 TL.LANGUAGE
4957 FROM WSH_REGIONS R,
4958 WSH_REGIONS_TL TL
4959 WHERE R.REGION_ID = TL.REGION_ID
4960 AND (
4961 ( ( UPPER(R.CITY_CODE) = UPPER(p_city_code) OR p_city_code is NULL )
4962 AND ( decode(p_city_code, null, UPPER(R.STATE_CODE), NVL(UPPER(R.STATE_CODE), UPPER(p_state_code) )) = UPPER(p_state_code)
4963 OR ( p_state_code is NULL ) ) )
4964 OR ( ( UPPER(TL.CITY) = UPPER(p_city) OR ( p_city is NULL ) )
4965 AND ( decode( p_city, null, UPPER(TL.STATE), NVL(UPPER(TL.STATE), UPPER(p_state) )) = UPPER(p_state)
4966 OR ( p_state is null ) ) )
4967 )
4968 AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4969 OR UPPER(TL.COUNTRY) = UPPER(p_country) );
4970
4971 --
4972 -- Debug Statements
4973 --
4974 IF l_debug_on THEN
4975 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
4976 END IF;
4977 --
4978
4979 DELETE FROM WSH_REGIONS_GLOBAL
4980 WHERE REGION_TYPE in ( 2, 3 );
4981
4982 --
4983 -- Debug Statements
4984 --
4985 IF l_debug_on THEN
4986 WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
4987 END IF;
4988 --
4989
4990 INSERT INTO wsh_regions_global
4991 ( REGION_ID,
4992 REGION_TYPE,
4993 COUNTRY_CODE,
4994 STATE_CODE,
4995 CITY_CODE )
4996 SELECT R.REGION_ID,
4997 R.REGION_TYPE,
4998 UPPER(R.COUNTRY_CODE),
4999 UPPER(R.STATE_CODE),
5000 UPPER(R.CITY_CODE)
5001 FROM WSH_REGIONS R
5002 WHERE R.REGION_TYPE = 2
5003 AND ( UPPER(R.CITY_CODE) = UPPER(p_city_code)
5004 OR ( p_city_code is NULL ) )
5005 AND ( decode(p_city_code, null, UPPER(R.STATE_CODE), NVL(UPPER(R.STATE_CODE), UPPER(p_state_code) )) = UPPER(p_state_code)
5006 OR ( p_state_code is NULL ) )
5007 AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code);
5008
5009 --
5010 -- Debug Statements
5011 --
5012 IF l_debug_on THEN
5013 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
5014 END IF;
5015 --
5016
5017 END IF; -- } Region type 2,3 i.e., City, Postal Codes
5018
5019 --
5020 -- Debug Statements
5021 --
5022 IF l_debug_on THEN
5023 WSH_DEBUG_SV.pop(l_module_name);
5024 END IF;
5025 --
5026
5027 -- Exception Handling part
5028 EXCEPTION
5029 WHEN OTHERS THEN
5030 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5031 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
5032 fnd_message.set_token('MSG_TEXT', SQLERRM);
5033 fnd_file.put_line(fnd_file.log, 'Init_Global_Table EXCEPTION : ' || sqlerrm);
5034 END Init_Global_Table;
5035
5036 --
5037 -- PROCEDURE : Insert_Global_Table
5038 --
5039 -- PURPOSE : Inserts the data in Global Temp tables
5040 -- ( Wsh_Regions_Global_Data and Wsh_Regions_Global tables )
5041
5042 PROCEDURE Insert_Global_Table (
5043 p_country IN VARCHAR2,
5044 p_state IN VARCHAR2,
5045 p_city IN VARCHAR2,
5046 p_country_code IN VARCHAR2,
5047 p_state_code IN VARCHAR2,
5048 p_city_code IN VARCHAR2,
5049 p_region_id IN NUMBER ,
5050 p_region_type IN NUMBER ,
5051 p_parent_region_id IN NUMBER ,
5052 p_postal_code_from IN VARCHAR2,
5053 p_postal_code_to IN VARCHAR2,
5054 p_tl_only_flag IN VARCHAR2,
5055 p_lang_code IN VARCHAR2,
5056 x_return_status OUT NOCOPY VARCHAR2 )
5057 AS
5058
5059 CURSOR parent_region IS
5060 SELECT parent_region_id
5061 FROM WSH_REGIONS
5062 WHERE REGION_ID = p_region_id;
5063
5064 l_region_id NUMBER;
5065 l_parent_region_id NUMBER;
5066 t1 NUMBER;
5067
5068 --
5069 l_debug_on BOOLEAN;
5070 --
5071 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_GLOBAL_TABLE';
5072 --
5073 BEGIN
5074 --
5075 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5076 --
5077 IF l_debug_on IS NULL
5078 THEN
5079 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5080 END IF;
5081 --
5082 --
5083 -- Debug Statements
5084 --
5085 IF l_debug_on THEN
5086 WSH_DEBUG_SV.push(l_module_name);
5087 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
5088 WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
5089 WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
5090 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
5091 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
5092 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
5093 WSH_DEBUG_SV.log(l_module_name, 'P_REGION_ID', P_REGION_ID);
5094 WSH_DEBUG_SV.log(l_module_name, 'P_REGION_TYPE', P_REGION_TYPE);
5095 WSH_DEBUG_SV.log(l_module_name, 'P_PARENT_REGION_ID', P_PARENT_REGION_ID);
5096 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
5097 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
5098 WSH_DEBUG_SV.log(l_module_name, 'P_TL_ONLY_FLAG', P_TL_ONLY_FLAG);
5099 WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
5100 END IF;
5101 --
5102
5103 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5104 t1 := dbms_utility.get_time;
5105 IF ( p_parent_region_id is null AND
5106 p_region_type = 0 )
5107 THEN
5108 l_parent_region_id := -1;
5109 ELSIF ( p_parent_region_id is null )
5110 THEN
5111 OPEN parent_region;
5112 FETCH parent_region INTO l_parent_region_id;
5113 CLOSE parent_region;
5114 ELSE
5115 l_parent_region_id := p_parent_region_id;
5116 END IF;
5117
5118 INSERT INTO wsh_regions_global_data
5119 ( REGION_ID,
5120 REGION_TYPE,
5121 PARENT_REGION_ID,
5122 COUNTRY,
5123 STATE,
5124 CITY,
5125 COUNTRY_CODE,
5126 STATE_CODE,
5127 CITY_CODE,
5128 POSTAL_CODE_FROM,
5129 POSTAL_CODE_TO,
5130 LANGUAGE )
5131 VALUES
5132 ( p_region_id,
5133 p_region_type,
5134 l_parent_region_id,
5135 UPPER(p_country),
5136 UPPER(p_state),
5137 UPPER(p_city),
5138 UPPER(p_country_code),
5139 UPPER(p_state_code),
5140 UPPER(p_city_code),
5141 p_postal_code_from,
5142 p_postal_code_to,
5143 p_lang_code );
5144
5145 --
5146 -- Debug Statements
5147 --
5148 IF l_debug_on THEN
5149 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
5150 END IF;
5151 --
5152
5153 IF ( p_tl_only_flag <> 'Y' AND
5154 p_region_type in ( 0, 1, 2 ) )
5155 THEN
5156 INSERT INTO wsh_regions_global
5157 ( REGION_ID,
5158 REGION_TYPE,
5159 COUNTRY_CODE,
5160 STATE_CODE,
5161 CITY_CODE )
5162 VALUES
5163 ( p_region_id,
5164 p_region_type,
5165 UPPER(p_country_code),
5166 UPPER(p_state_code),
5167 UPPER(p_city_code) );
5168 --
5169 -- Debug Statements
5170 --
5171 IF l_debug_on THEN
5172 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
5173 END IF;
5174 --
5175 END IF;
5176
5177 --
5178 -- Debug Statements
5179 --
5180 IF l_debug_on THEN
5181 WSH_DEBUG_SV.pop(l_module_name);
5182 END IF;
5183 --
5184
5185 -- Exception Handling part
5186 EXCEPTION
5187 WHEN OTHERS THEN
5188 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5189 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
5190 fnd_message.set_token('MSG_TEXT', SQLERRM);
5191 fnd_file.put_line(fnd_file.log, 'INSERT_GLOBAL_TABLE EXCEPTION : ' || sqlerrm);
5192 END Insert_Global_Table;
5193
5194 --
5195 -- PROCEDURE : Update_Global_Table
5196 --
5197 -- PURPOSE : Updates the data in Global Temp tables
5198 -- ( Wsh_Regions_Global_Data and Wsh_Regions_Global tables )
5199
5200 PROCEDURE Update_Global_Table (
5201 p_country IN VARCHAR2,
5202 p_state IN VARCHAR2,
5203 p_city IN VARCHAR2,
5204 p_country_code IN VARCHAR2,
5205 p_state_code IN VARCHAR2,
5206 p_city_code IN VARCHAR2,
5207 p_region_id IN NUMBER ,
5208 p_postal_code_from IN VARCHAR2,
5209 p_postal_code_to IN VARCHAR2,
5210 p_parent_zone_level IN NUMBER,
5211 p_lang_code IN VARCHAR2,
5212 x_return_status OUT NOCOPY VARCHAR2 )
5213 AS
5214
5215 CURSOR child_regions IS
5216 SELECT region_id, region_type, parent_region_id
5217 FROM wsh_regions_global_data
5218 START WITH region_id = p_region_id
5219 CONNECT BY PRIOR region_id = parent_region_id;
5220
5221 CURSOR get_state_code(l_region_id NUMBER) IS
5222 SELECT state_code, city_code
5223 FROM wsh_regions
5224 WHERE region_id = l_region_id;
5225
5226 l_region_id NUMBER;
5227 l_parent_region_id NUMBER;
5228 l_region_upd_cnt NUMBER;
5229 l_region_data_upd_cnt NUMBER;
5230 l_region_ins_cnt NUMBER;
5231 l_region_data_ins_cnt NUMBER;
5232 l_update_state_code WSH_REGIONS.State_Code%TYPE;
5233 l_update_city_code WSH_REGIONS.City_Code%TYPE;
5234 t1 NUMBER;
5235 --
5236 l_debug_on BOOLEAN;
5237 --
5238 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_GLOBAL_TABLE';
5239 --
5240 BEGIN
5241 --
5242 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
5243 --
5244 IF l_debug_on IS NULL
5245 THEN
5246 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
5247 END IF;
5248 --
5249 --
5250 -- Debug Statements
5251 --
5252 IF l_debug_on THEN
5253 WSH_DEBUG_SV.push(l_module_name);
5254 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY', P_COUNTRY);
5255 WSH_DEBUG_SV.log(l_module_name, 'P_STATE', P_STATE);
5256 WSH_DEBUG_SV.log(l_module_name, 'P_CITY', P_CITY);
5257 WSH_DEBUG_SV.log(l_module_name, 'P_COUNTRY_CODE', P_COUNTRY_CODE);
5258 WSH_DEBUG_SV.log(l_module_name, 'P_STATE_CODE', P_STATE_CODE);
5259 WSH_DEBUG_SV.log(l_module_name, 'P_CITY_CODE', P_CITY_CODE);
5260 WSH_DEBUG_SV.log(l_module_name, 'P_REGION_ID', P_REGION_ID);
5261 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_FROM', P_POSTAL_CODE_FROM);
5262 WSH_DEBUG_SV.log(l_module_name, 'P_POSTAL_CODE_TO', P_POSTAL_CODE_TO);
5263 WSH_DEBUG_SV.log(l_module_name, 'P_PARENT_ZONE_LEVEL', P_PARENT_ZONE_LEVEL);
5264 WSH_DEBUG_SV.log(l_module_name, 'P_LANG_CODE', P_LANG_CODE);
5265 END IF;
5266 --
5267
5268 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
5269 l_region_upd_cnt := 0;
5270 l_region_ins_cnt := 0;
5271 l_region_data_upd_cnt := 0;
5272 l_region_data_ins_cnt := 0;
5273 t1 := dbms_utility.get_time;
5274
5275 FOR i in child_regions
5276 LOOP
5277
5278 OPEN get_state_code(i.parent_region_id);
5279 FETCH get_state_code INTO l_update_state_code, l_update_city_code;
5280 CLOSE get_state_code;
5281
5282 IF ( i.region_type in ( 0, 1, 2 ) )
5283 THEN
5284 UPDATE wsh_regions_global
5285 SET COUNTRY_CODE = nvl(UPPER(p_country_code), COUNTRY_CODE),
5286 state_code = decode(i.region_type,
5287 2, UPPER(l_update_state_code),
5288 3, UPPER(l_update_state_code),
5289 decode(p_parent_zone_level,
5290 1, UPPER(p_state_code),
5291 nvl(UPPER(p_state_code), state_code))),
5292 city_code = decode(p_parent_zone_level,
5293 2, UPPER(p_city_code),
5294 nvl(UPPER(p_city_code), city_code))
5295 WHERE REGION_ID = i.region_id;
5296
5297 l_region_upd_cnt := l_region_upd_cnt + sql%rowcount;
5298
5299 IF ( sql%rowcount = 0 )
5300 THEN
5301 INSERT INTO wsh_regions_global
5302 ( REGION_ID,
5303 REGION_TYPE,
5304 COUNTRY_CODE,
5305 STATE_CODE,
5306 CITY_CODE )
5307 SELECT REGION_ID,
5308 REGION_TYPE,
5309 COUNTRY_CODE,
5310 STATE_CODE,
5311 CITY_CODE
5312 FROM WSH_REGIONS
5313 WHERE REGION_ID = i.region_id;
5314
5315 l_region_ins_cnt := l_region_ins_cnt + sql%rowcount;
5316 END IF;
5317 END IF;
5318
5319 UPDATE wsh_regions_global_data
5320 SET country = nvl(UPPER(p_country), country),
5321 state = nvl(UPPER(p_state), state),
5322 city = nvl(UPPER(p_city), city),
5323 country_code = nvl(UPPER(p_country_code), country_code),
5324 state_code = decode(i.region_type,
5325 2, UPPER(l_update_state_code),
5326 3, UPPER(l_update_state_code),
5327 decode(p_parent_zone_level,
5328 1, UPPER(p_state_code),
5329 nvl(UPPER(p_state_code), state_code))),
5330 city_code = decode(p_parent_zone_level,
5331 2, UPPER(p_city_code),
5332 nvl(UPPER(p_city_code), city_code)),
5333 postal_code_from = nvl(p_postal_code_from, postal_code_from),
5334 postal_code_to = nvl(p_postal_code_to, postal_code_to)
5335 WHERE region_id = i.region_id
5336 AND language = p_lang_code;
5337
5338 l_region_data_upd_cnt := l_region_data_upd_cnt + sql%rowcount;
5339
5340 IF ( sql%rowcount = 0 )
5341 THEN
5342 INSERT INTO wsh_regions_global_data
5343 ( REGION_ID,
5344 REGION_TYPE,
5345 PARENT_REGION_ID,
5346 COUNTRY,
5347 STATE,
5348 CITY,
5349 COUNTRY_CODE,
5350 STATE_CODE,
5351 CITY_CODE,
5352 POSTAL_CODE_FROM,
5353 POSTAL_CODE_TO,
5354 LANGUAGE )
5355 SELECT R.REGION_ID,
5356 R.REGION_TYPE,
5357 R.PARENT_REGION_ID,
5358 TL.COUNTRY,
5359 TL.STATE,
5360 TL.CITY,
5361 R.COUNTRY_CODE,
5362 R.STATE_CODE,
5363 R.CITY_CODE,
5364 TL.POSTAL_CODE_FROM,
5365 TL.POSTAL_CODE_TO,
5366 TL.LANGUAGE
5367 FROM WSH_REGIONS R,
5368 WSH_REGIONS_TL TL
5369 WHERE TL.LANGUAGE = p_lang_code
5370 AND TL.REGION_ID = R.REGION_ID
5371 AND R.REGION_ID = i.region_id;
5372
5373 l_region_data_ins_cnt := l_region_data_ins_cnt + sql%rowcount;
5374 END IF;
5375
5376 END LOOP;
5377
5378 --
5379 --
5380 -- Debug Statements
5381 --
5382 IF l_debug_on THEN
5383 WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global', l_region_upd_cnt);
5384 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', l_region_ins_cnt);
5385 WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global_data', l_region_data_upd_cnt);
5386 WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', l_region_data_ins_cnt);
5387 END IF;
5388 --
5389
5390 --
5391 -- Debug Statements
5392 --
5393 IF l_debug_on THEN
5394 WSH_DEBUG_SV.pop(l_module_name);
5395 END IF;
5396 --
5397
5398 -- Exception Handling part
5399 EXCEPTION
5400 WHEN OTHERS THEN
5401 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
5402 fnd_message.set_name('WSH', 'WSH_UTIL_MESSAGE_U');
5403 fnd_message.set_token('MSG_TEXT', SQLERRM);
5404 fnd_file.put_line(fnd_file.log, 'UPDATE_GLOBAL_TABLE EXCEPTION : ' || sqlerrm);
5405 END Update_Global_Table;
5406
5407 END WSH_REGIONS_PKG;
5408