[Home] [Help]
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
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:
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,
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:
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: --
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,
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,
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,
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,
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;
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: --
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,
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,
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;
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,
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,
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;
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,
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,
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;
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,
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,
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: --
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,
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;
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
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;
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);
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: --
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;
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
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);
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.
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))),
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),
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,
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,
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;
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;
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';
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
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:
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
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:
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;
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,
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,
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,
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,
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
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:
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')
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
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
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;
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:
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,
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,
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,
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,
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;
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
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,
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;
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;
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:
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
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;
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;
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),
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(
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',
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
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
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);
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:
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;
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
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;
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
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;
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:
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
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 (
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,
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)
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,
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
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:
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:
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 (
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,
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:
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:
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: --
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
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,
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)
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) );
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;
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: --
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
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,
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: --
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:
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
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
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,
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 (
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)
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
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
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
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,
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) )
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
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
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
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,
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 )
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)
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
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
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
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,
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)
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
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,
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;
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,
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
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,
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:
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,
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
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;
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: --
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),
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,
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;
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),
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,
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;
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:
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;
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: --
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:
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: --
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: