[Home] [Help]
1: PACKAGE BODY WSH_REGIONS_PKG AS
2: /* $Header: WSHRETHB.pls 120.5 2010/11/09 22:56:29 skanduku ship $ */
3:
4: --
5: -- Package
2: /* $Header: WSHRETHB.pls 120.5 2010/11/09 22:56:29 skanduku ship $ */
3:
4: --
5: -- Package
6: -- WSH_REGIONS_PKG
7: --
8: -- Purpose
9: --
10:
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,--bug 8513956 moved language in where clause
2835: last_updated_by = p_user_id,
2836: last_update_date = sysdate
2868: x_status OUT NOCOPY NUMBER) IS
2869:
2870: CURSOR lock_row IS
2871: SELECT w.region_id, t.zone, w.zone_level
2872: FROM wsh_regions w, wsh_regions_tl t
2873: WHERE w.region_id = t.region_id
2874: AND t.language = p_lang_code
2875: FOR UPDATE OF w.region_id, t.region_id NOWAIT;
2876:
3085: SELECT region_id
3086: FROM wsh_zone_regions z
3087: WHERE z.region_id in (
3088: SELECT region_id
3089: FROM wsh_regions
3090: START WITH region_id = l_region_id
3091: CONNECT BY PRIOR parent_region_id = region_id) AND
3092: z.parent_region_id = p_zone_id
3093: and ( nvl(l_codeFrom,'0') between nvl(z.postal_code_from,'0') and nvl(z.postal_code_to,'ZZZZZZZZZZZZZ')
3095: or nvl(z.postal_code_from,'0') between nvl(l_codeFrom,'0') and nvl(l_codeTo,'ZZZZZZZZZZZZ'));
3096:
3097: CURSOR check_regions_in_zone_down(l_region_id NUMBER) IS
3098: SELECT region_id
3099: FROM wsh_regions r
3100: WHERE region_id = l_region_id
3101: START WITH r.region_id in (
3102: SELECT region_id
3103: FROM wsh_zone_regions
3108: SELECT region_id
3109: FROM wsh_zone_regions z
3110: WHERE z.region_id in (
3111: SELECT region_id
3112: FROM wsh_regions
3113: START WITH region_id = l_region_id
3114: CONNECT BY PRIOR parent_region_id = region_id) AND
3115: z.parent_region_id = p_zone_id AND
3116: l_codeFrom = z.postal_code_from AND
3117: l_codeTo = z.postal_code_to;
3118:
3119: CURSOR get_zone_level IS
3120: SELECT zone_level
3121: FROM wsh_regions
3122: WHERE region_id = p_zone_id;
3123:
3124: CURSOR get_region_type IS
3125: SELECT region_type
3122: WHERE region_id = p_zone_id;
3123:
3124: CURSOR get_region_type IS
3125: SELECT region_type
3126: FROM wsh_regions
3127: WHERE region_id = p_region_id;
3128:
3129: l_region_info wsh_regions_search_pkg.region_rec;
3130: l_existing_region_id NUMBER;
3125: SELECT region_type
3126: FROM wsh_regions
3127: WHERE region_id = p_region_id;
3128:
3129: l_region_info wsh_regions_search_pkg.region_rec;
3130: l_existing_region_id NUMBER;
3131: l_zone_level NUMBER;
3132: l_region_count NUMBER;
3133:
3179: --
3180: -- Debug Statements
3181: --
3182: IF l_debug_on THEN
3183: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
3184: END IF;
3185: --
3186: Wsh_Regions_Search_Pkg.Get_Region_Info(
3187: p_country => p_country,
3182: IF l_debug_on THEN
3183: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
3184: END IF;
3185: --
3186: Wsh_Regions_Search_Pkg.Get_Region_Info(
3187: p_country => p_country,
3188: p_country_region => null,
3189: p_state => p_state,
3190: p_city => p_city,
3225: --
3226: -- Debug Statements
3227: --
3228: IF l_debug_on THEN
3229: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
3230: END IF;
3231: --
3232: Wsh_Regions_Search_Pkg.Get_Region_Info(
3233: p_country => p_country,
3228: IF l_debug_on THEN
3229: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
3230: END IF;
3231: --
3232: Wsh_Regions_Search_Pkg.Get_Region_Info(
3233: p_country => p_country,
3234: p_country_region => null,
3235: p_state => null,
3236: p_city => null,
3397: CLOSE get_zone_level;
3398:
3399: IF (l_region_type < nvl(l_zone_level,10000)) THEN
3400:
3401: UPDATE wsh_regions
3402: SET zone_level = l_region_type
3403: WHERE region_id = p_zone_id;
3404:
3405: END IF;
3695: TL.COUNTY,
3696: TL.POSTAL_CODE_FROM,
3697: TL.POSTAL_CODE_TO,
3698: TL.LANGUAGE
3699: FROM WSH_REGIONS_INTERFACE R,
3700: WSH_REGIONS_TL_INTERFACE TL
3701: WHERE R.REGION_ID = TL.REGION_ID
3702: AND TL.ZONE IS NULL -- We are not processing zones here .
3703: AND PROCESSED_FLAG is null
3696: TL.POSTAL_CODE_FROM,
3697: TL.POSTAL_CODE_TO,
3698: TL.LANGUAGE
3699: FROM WSH_REGIONS_INTERFACE R,
3700: WSH_REGIONS_TL_INTERFACE TL
3701: WHERE R.REGION_ID = TL.REGION_ID
3702: AND TL.ZONE IS NULL -- We are not processing zones here .
3703: AND PROCESSED_FLAG is null
3704: AND nvl(TL.STATE,'NULL') = decode(x_state,NULL,nvl(TL.STATE,'NULL'),x_state)
3740: l_postal_code_from_rec tab_postal_code_from;
3741: l_postal_code_to_rec tab_postal_code_to;
3742: l_language_rec tab_language;
3743:
3744: l_prev_country WSH_REGIONS_TL.Country%TYPE;
3745: l_prev_state WSH_REGIONS_TL.State%TYPE;
3746: l_prev_city WSH_REGIONS_TL.City%TYPE;
3747: l_prev_country_code WSH_REGIONS.Country_Code%Type;
3748: l_prev_state_code WSH_REGIONS.State_Code%Type;
3741: l_postal_code_to_rec tab_postal_code_to;
3742: l_language_rec tab_language;
3743:
3744: l_prev_country WSH_REGIONS_TL.Country%TYPE;
3745: l_prev_state WSH_REGIONS_TL.State%TYPE;
3746: l_prev_city WSH_REGIONS_TL.City%TYPE;
3747: l_prev_country_code WSH_REGIONS.Country_Code%Type;
3748: l_prev_state_code WSH_REGIONS.State_Code%Type;
3749: l_prev_city_code WSH_REGIONS.City_Code%Type;
3742: l_language_rec tab_language;
3743:
3744: l_prev_country WSH_REGIONS_TL.Country%TYPE;
3745: l_prev_state WSH_REGIONS_TL.State%TYPE;
3746: l_prev_city WSH_REGIONS_TL.City%TYPE;
3747: l_prev_country_code WSH_REGIONS.Country_Code%Type;
3748: l_prev_state_code WSH_REGIONS.State_Code%Type;
3749: l_prev_city_code WSH_REGIONS.City_Code%Type;
3750:
3743:
3744: l_prev_country WSH_REGIONS_TL.Country%TYPE;
3745: l_prev_state WSH_REGIONS_TL.State%TYPE;
3746: l_prev_city WSH_REGIONS_TL.City%TYPE;
3747: l_prev_country_code WSH_REGIONS.Country_Code%Type;
3748: l_prev_state_code WSH_REGIONS.State_Code%Type;
3749: l_prev_city_code WSH_REGIONS.City_Code%Type;
3750:
3751: -- Variables for Updating and Deleting regions from Interface table in Bulk
3744: l_prev_country WSH_REGIONS_TL.Country%TYPE;
3745: l_prev_state WSH_REGIONS_TL.State%TYPE;
3746: l_prev_city WSH_REGIONS_TL.City%TYPE;
3747: l_prev_country_code WSH_REGIONS.Country_Code%Type;
3748: l_prev_state_code WSH_REGIONS.State_Code%Type;
3749: l_prev_city_code WSH_REGIONS.City_Code%Type;
3750:
3751: -- Variables for Updating and Deleting regions from Interface table in Bulk
3752: l_upd_region_id WSH_UTIL_CORE.Id_Tab_Type;
3745: l_prev_state WSH_REGIONS_TL.State%TYPE;
3746: l_prev_city WSH_REGIONS_TL.City%TYPE;
3747: l_prev_country_code WSH_REGIONS.Country_Code%Type;
3748: l_prev_state_code WSH_REGIONS.State_Code%Type;
3749: l_prev_city_code WSH_REGIONS.City_Code%Type;
3750:
3751: -- Variables for Updating and Deleting regions from Interface table in Bulk
3752: l_upd_region_id WSH_UTIL_CORE.Id_Tab_Type;
3753: l_del_region_id WSH_UTIL_CORE.Id_Tab_Type;
3833: --
3834: -- Debug Statements
3835: --
3836: IF l_debug_on THEN
3837: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.VALIDATE_REGION', WSH_DEBUG_SV.C_PROC_LEVEL);
3838: END IF;
3839: --
3840: Validate_Region( p_country => l_country_rec(i),
3841: p_state => l_state_rec(i),
3899: --
3900: -- Debug Statements
3901: --
3902: IF l_debug_on THEN
3903: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.INIT_GLOBAL_TABLE', WSH_DEBUG_SV.C_PROC_LEVEL);
3904: END IF;
3905: --
3906: -- To popualte Global temp tables
3907: Init_Global_Table(
3937: --
3938: -- Debug Statements
3939: --
3940: IF l_debug_on THEN
3941: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.UPDATE_REGION', WSH_DEBUG_SV.C_PROC_LEVEL);
3942: END IF;
3943:
3944: Update_Region(
3945: p_insert_type => 'SYNC',
3995: l_del_region_id(l_del_count) := l_region_id_rec(i);
3996: END IF;
3997: END LOOP;
3998:
3999: -- Bulk Updation in WSH_REGIONS_INTERFACE table
4000: IF ( l_upd_count > 0 ) THEN
4001: l_upd_count := 0;
4002:
4003: FORALL i in l_upd_region_id.first..l_upd_region_id.last
4000: IF ( l_upd_count > 0 ) THEN
4001: l_upd_count := 0;
4002:
4003: FORALL i in l_upd_region_id.first..l_upd_region_id.last
4004: UPDATE wsh_regions_interface
4005: SET processed_flag = 'Y'
4006: WHERE region_id = l_upd_region_id(i);
4007:
4008: -- Deleting region ids from array
4008: -- Deleting region ids from array
4009: l_upd_region_id.DELETE;
4010: END IF;
4011:
4012: -- Bulk Deletion in WSH_REGIONS_INTERFACE table
4013: IF ( l_del_count > 0 ) THEN
4014: l_del_count := 0;
4015: FORALL i in l_del_region_id.first..l_del_region_id.last
4016: DELETE FROM WSH_REGIONS_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4012: -- Bulk Deletion in WSH_REGIONS_INTERFACE table
4013: IF ( l_del_count > 0 ) THEN
4014: l_del_count := 0;
4015: FORALL i in l_del_region_id.first..l_del_region_id.last
4016: DELETE FROM WSH_REGIONS_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4017:
4018: FORALL i in l_del_region_id.first..l_del_region_id.last
4019: DELETE FROM WSH_REGIONS_TL_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4020:
4015: FORALL i in l_del_region_id.first..l_del_region_id.last
4016: DELETE FROM WSH_REGIONS_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4017:
4018: FORALL i in l_del_region_id.first..l_del_region_id.last
4019: DELETE FROM WSH_REGIONS_TL_INTERFACE WHERE REGION_ID = l_del_region_id(i);
4020:
4021: -- Deleting region ids from array
4022: l_del_region_id.DELETE;
4023: END IF;
4032:
4033: x_regions_processed := l_regions_processed;
4034:
4035: -- Truncating records from Global Temp Tables
4036: delete from wsh_regions_global_data;
4037: delete from wsh_regions_global;
4038: COMMIT;
4039: EXCEPTION
4040: WHEN OTHERS THEN
4033: x_regions_processed := l_regions_processed;
4034:
4035: -- Truncating records from Global Temp Tables
4036: delete from wsh_regions_global_data;
4037: delete from wsh_regions_global;
4038: COMMIT;
4039: EXCEPTION
4040: WHEN OTHERS THEN
4041: x_status := 2;
4073: l_dummy_state_code VARCHAR2(3);
4074:
4075: CURSOR total_regions IS
4076: SELECT count(*)
4077: FROM wsh_Regions_interface
4078: WHERE processed_flag is null;
4079:
4080: CURSOR more_regions IS
4081: SELECT 1
4078: WHERE processed_flag is null;
4079:
4080: CURSOR more_regions IS
4081: SELECT 1
4082: FROM wsh_Regions_interface
4083: WHERE processed_flag is null;
4084:
4085: l_tmp NUMBER;
4086: l_good_count NUMBER := 0;
4204: IS
4205: SELECT T.REGION_ID REGION_ID, T.COUNTRY COUNTRY, T.STATE STATE, T.CITY CITY,
4206: ltrim(P.POSTAL_CODE_FROM,'0') PCODE_FROM,
4207: ltrim(P.POSTAL_CODE_TO,'0') PCODE_TO
4208: FROM WSH_REGIONS_TL T, WSH_ZONE_REGIONS P
4209: WHERE T.LANGUAGE = p_lang
4210: AND T.REGION_ID = P.REGION_ID
4211: AND P.PARENT_REGION_ID = p_zoneId;
4212:
4371: /*----------------------------------------------------------*/
4372: procedure ADD_LANGUAGE
4373: is
4374: begin
4375: delete from WSH_REGIONS_TL T
4376: where not exists
4377: (select NULL
4378: from WSH_REGIONS B
4379: where B.REGION_ID = T.REGION_ID
4374: begin
4375: delete from WSH_REGIONS_TL T
4376: where not exists
4377: (select NULL
4378: from WSH_REGIONS B
4379: where B.REGION_ID = T.REGION_ID
4380: );
4381:
4382: update WSH_REGIONS_TL T set (
4378: from WSH_REGIONS B
4379: where B.REGION_ID = T.REGION_ID
4380: );
4381:
4382: update WSH_REGIONS_TL T set (
4383: CONTINENT,
4384: COUNTRY,
4385: COUNTRY_REGION,
4386: STATE,
4400: B.POSTAL_CODE_FROM,
4401: B.POSTAL_CODE_TO,
4402: B.ALTERNATE_NAME,
4403: B.COUNTY
4404: from WSH_REGIONS_TL B
4405: where B.REGION_ID = T.REGION_ID
4406: and B.LANGUAGE = T.SOURCE_LANG)
4407: where (
4408: T.REGION_ID,
4409: T.LANGUAGE
4410: ) in (select
4411: SUBT.REGION_ID,
4412: SUBT.LANGUAGE
4413: from WSH_REGIONS_TL SUBB, WSH_REGIONS_TL SUBT
4414: where SUBB.REGION_ID = SUBT.REGION_ID
4415: and SUBB.LANGUAGE = SUBT.SOURCE_LANG
4416: and (SUBB.CONTINENT <> SUBT.CONTINENT
4417: or (SUBB.CONTINENT is null and SUBT.CONTINENT is not null)
4444: or (SUBB.COUNTY is null and SUBT.COUNTY is not null)
4445: or (SUBB.COUNTY is not null and SUBT.COUNTY is null)
4446: ));
4447:
4448: insert into WSH_REGIONS_TL (
4449: REGION_ID,
4450: CONTINENT,
4451: COUNTRY,
4452: COUNTRY_REGION,
4482: B.CREATED_BY,
4483: B.LAST_UPDATE_LOGIN,
4484: L.LANGUAGE_CODE,
4485: B.SOURCE_LANG
4486: from WSH_REGIONS_TL B, FND_LANGUAGES L
4487: where L.INSTALLED_FLAG in ('I', 'B')
4488: and B.LANGUAGE = userenv('LANG')
4489: and not exists
4490: (select NULL
4487: where L.INSTALLED_FLAG in ('I', 'B')
4488: and B.LANGUAGE = userenv('LANG')
4489: and not exists
4490: (select NULL
4491: from WSH_REGIONS_TL T
4492: where T.REGION_ID = B.REGION_ID
4493: and T.LANGUAGE = L.LANGUAGE_CODE);
4494: end ADD_LANGUAGE;
4495:
4645:
4646: --
4647: -- PROCEDURE : Init_Global_Table
4648: --
4649: -- PURPOSE : Populates the data in Global Temp tables(Wsh_Regions_Global
4650: -- and Wsh_Regions_Global_Data) fetched from Wsh_Regions and
4651: -- Wsh_Regions_Tl based on parameter p_populate_type.
4652: --
4653:
4646: --
4647: -- PROCEDURE : Init_Global_Table
4648: --
4649: -- PURPOSE : Populates the data in Global Temp tables(Wsh_Regions_Global
4650: -- and Wsh_Regions_Global_Data) fetched from Wsh_Regions and
4651: -- Wsh_Regions_Tl based on parameter p_populate_type.
4652: --
4653:
4654: PROCEDURE Init_Global_Table (
4647: -- PROCEDURE : Init_Global_Table
4648: --
4649: -- PURPOSE : Populates the data in Global Temp tables(Wsh_Regions_Global
4650: -- and Wsh_Regions_Global_Data) fetched from Wsh_Regions and
4651: -- Wsh_Regions_Tl based on parameter p_populate_type.
4652: --
4653:
4654: PROCEDURE Init_Global_Table (
4655: p_country IN VARCHAR2,
4665: AS
4666:
4667: CURSOR city_cur IS
4668: SELECT DISTINCT UPPER(STATE)
4669: FROM WSH_REGIONS_TL
4670: WHERE UPPER(CITY) = UPPER(p_city)
4671: AND UPPER(COUNTRY) = UPPER(p_country);
4672: -- AND LANGUAGE = p_lang_code;
4673:
4672: -- AND LANGUAGE = p_lang_code;
4673:
4674: CURSOR city_cur_code IS
4675: SELECT DISTINCT UPPER(R.STATE_CODE)
4676: FROM WSH_REGIONS R
4677: WHERE ( UPPER(R.CITY_CODE) = UPPER(p_city_code) OR p_city_code is null )
4678: AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4679: AND REGION_TYPE = 2;
4680:
4722: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
4723: t1 := dbms_utility.get_time;
4724:
4725: IF ( p_country_flag = 'Y' ) THEN -- { Region type 0 i.e., Country
4726: DELETE FROM WSH_REGIONS_GLOBAL_DATA;
4727:
4728: --
4729: -- Debug Statements
4730: --
4728: --
4729: -- Debug Statements
4730: --
4731: IF l_debug_on THEN
4732: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4733: END IF;
4734: --
4735:
4736: INSERT INTO wsh_regions_global_data
4732: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4733: END IF;
4734: --
4735:
4736: INSERT INTO wsh_regions_global_data
4737: ( REGION_ID,
4738: REGION_TYPE,
4739: PARENT_REGION_ID,
4740: COUNTRY,
4757: UPPER(R.CITY_CODE),
4758: TL.POSTAL_CODE_FROM,
4759: TL.POSTAL_CODE_TO,
4760: TL.LANGUAGE
4761: FROM WSH_REGIONS R,
4762: WSH_REGIONS_TL TL
4763: WHERE R.REGION_ID = TL.REGION_ID
4764: AND R.REGION_TYPE = 0
4765: AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4758: TL.POSTAL_CODE_FROM,
4759: TL.POSTAL_CODE_TO,
4760: TL.LANGUAGE
4761: FROM WSH_REGIONS R,
4762: WSH_REGIONS_TL TL
4763: WHERE R.REGION_ID = TL.REGION_ID
4764: AND R.REGION_TYPE = 0
4765: AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
4766: OR UPPER(TL.COUNTRY) = UPPER(p_country) );
4768: --
4769: -- Debug Statements
4770: --
4771: IF l_debug_on THEN
4772: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
4773: END IF;
4774: --
4775:
4776: DELETE FROM WSH_REGIONS_GLOBAL;
4772: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
4773: END IF;
4774: --
4775:
4776: DELETE FROM WSH_REGIONS_GLOBAL;
4777:
4778: --
4779: -- Debug Statements
4780: --
4778: --
4779: -- Debug Statements
4780: --
4781: IF l_debug_on THEN
4782: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
4783: END IF;
4784: --
4785:
4786: INSERT INTO wsh_regions_global
4782: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
4783: END IF;
4784: --
4785:
4786: INSERT INTO wsh_regions_global
4787: ( REGION_ID,
4788: REGION_TYPE,
4789: COUNTRY_CODE,
4790: STATE_CODE,
4793: R.REGION_TYPE,
4794: UPPER(R.COUNTRY_CODE),
4795: UPPER(R.STATE_CODE),
4796: UPPER(R.CITY_CODE)
4797: FROM WSH_REGIONS R
4798: WHERE R.REGION_TYPE = 0
4799: AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code);
4800:
4801: --
4801: --
4802: -- Debug Statements
4803: --
4804: IF l_debug_on THEN
4805: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
4806: END IF;
4807: --
4808: END IF; -- } Region Type 0 ie., Country
4809:
4844: END IF;
4845: END LOOP;
4846: END IF;
4847:
4848: DELETE FROM WSH_REGIONS_GLOBAL_DATA
4849: WHERE REGION_TYPE in ( 1, 2, 3 );
4850:
4851: --
4852: -- Debug Statements
4851: --
4852: -- Debug Statements
4853: --
4854: IF l_debug_on THEN
4855: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4856: END IF;
4857: --
4858:
4859: INSERT INTO wsh_regions_global_data
4855: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4856: END IF;
4857: --
4858:
4859: INSERT INTO wsh_regions_global_data
4860: ( REGION_ID,
4861: REGION_TYPE,
4862: PARENT_REGION_ID,
4863: COUNTRY,
4880: UPPER(R.CITY_CODE),
4881: TL.POSTAL_CODE_FROM,
4882: TL.POSTAL_CODE_TO,
4883: TL.LANGUAGE
4884: FROM WSH_REGIONS R,
4885: WSH_REGIONS_TL TL
4886: WHERE R.REGION_ID = TL.REGION_ID
4887: AND R.REGION_TYPE = 1
4888: AND (
4881: TL.POSTAL_CODE_FROM,
4882: TL.POSTAL_CODE_TO,
4883: TL.LANGUAGE
4884: FROM WSH_REGIONS R,
4885: WSH_REGIONS_TL TL
4886: WHERE R.REGION_ID = TL.REGION_ID
4887: AND R.REGION_TYPE = 1
4888: AND (
4889: ( ( UPPER(R.STATE_CODE) = UPPER(p_state_code)
4899: --
4900: -- Debug Statements
4901: --
4902: IF l_debug_on THEN
4903: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
4904: END IF;
4905: --
4906:
4907: DELETE FROM WSH_REGIONS_GLOBAL
4903: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
4904: END IF;
4905: --
4906:
4907: DELETE FROM WSH_REGIONS_GLOBAL
4908: WHERE REGION_TYPE in ( 1, 2, 3 );
4909:
4910: --
4911: -- Debug Statements
4910: --
4911: -- Debug Statements
4912: --
4913: IF l_debug_on THEN
4914: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
4915: END IF;
4916: --
4917:
4918: INSERT INTO wsh_regions_global
4914: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
4915: END IF;
4916: --
4917:
4918: INSERT INTO wsh_regions_global
4919: ( REGION_ID,
4920: REGION_TYPE,
4921: COUNTRY_CODE,
4922: STATE_CODE,
4925: R.REGION_TYPE,
4926: UPPER(R.COUNTRY_CODE),
4927: UPPER(R.STATE_CODE),
4928: UPPER(R.CITY_CODE)
4929: FROM WSH_REGIONS R
4930: WHERE R.REGION_TYPE = 1
4931: AND ( UPPER(R.STATE_CODE) = UPPER(p_state_code)
4932: OR ( UPPER(R.STATE_CODE) in ( l_tmp_state_code ) )
4933: OR ( p_state_code is NULL and p_city_code is null) )
4936: --
4937: -- Debug Statements
4938: --
4939: IF l_debug_on THEN
4940: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
4941: END IF;
4942: --
4943:
4944: END IF; -- } Region type 1 i.e., State
4944: END IF; -- } Region type 1 i.e., State
4945:
4946: IF ( p_city_flag = 'Y' ) THEN -- { Region type 2,3 i.e., City, Postal Codes
4947:
4948: DELETE FROM WSH_REGIONS_GLOBAL_DATA
4949: WHERE REGION_TYPE in ( 2, 3 );
4950:
4951: --
4952: -- Debug Statements
4951: --
4952: -- Debug Statements
4953: --
4954: IF l_debug_on THEN
4955: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4956: END IF;
4957: --
4958:
4959: INSERT INTO wsh_regions_global_data
4955: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
4956: END IF;
4957: --
4958:
4959: INSERT INTO wsh_regions_global_data
4960: ( REGION_ID,
4961: REGION_TYPE,
4962: PARENT_REGION_ID,
4963: COUNTRY,
4980: UPPER(R.CITY_CODE),
4981: TL.POSTAL_CODE_FROM,
4982: TL.POSTAL_CODE_TO,
4983: TL.LANGUAGE
4984: FROM WSH_REGIONS R,
4985: WSH_REGIONS_TL TL
4986: WHERE R.REGION_ID = TL.REGION_ID
4987: /* AND (
4988: ( ( UPPER(R.CITY_CODE) = UPPER(p_city_code) OR p_city_code is NULL )
4981: TL.POSTAL_CODE_FROM,
4982: TL.POSTAL_CODE_TO,
4983: TL.LANGUAGE
4984: FROM WSH_REGIONS R,
4985: WSH_REGIONS_TL TL
4986: WHERE R.REGION_ID = TL.REGION_ID
4987: /* AND (
4988: ( ( UPPER(R.CITY_CODE) = UPPER(p_city_code) OR p_city_code is NULL )
4989: AND ( decode(p_city_code, null, UPPER(R.STATE_CODE), NVL(UPPER(R.STATE_CODE), UPPER(p_state_code) )) = UPPER(p_state_code)
5034: --
5035: -- Debug Statements
5036: --
5037: IF l_debug_on THEN
5038: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
5039: END IF;
5040: --
5041:
5042: DELETE FROM WSH_REGIONS_GLOBAL
5038: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
5039: END IF;
5040: --
5041:
5042: DELETE FROM WSH_REGIONS_GLOBAL
5043: WHERE REGION_TYPE in ( 2, 3 );
5044:
5045: --
5046: -- Debug Statements
5045: --
5046: -- Debug Statements
5047: --
5048: IF l_debug_on THEN
5049: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
5050: END IF;
5051: --
5052:
5053: INSERT INTO wsh_regions_global
5049: WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
5050: END IF;
5051: --
5052:
5053: INSERT INTO wsh_regions_global
5054: ( REGION_ID,
5055: REGION_TYPE,
5056: COUNTRY_CODE,
5057: STATE_CODE,
5060: R.REGION_TYPE,
5061: UPPER(R.COUNTRY_CODE),
5062: UPPER(R.STATE_CODE),
5063: UPPER(R.CITY_CODE)
5064: FROM WSH_REGIONS R
5065: WHERE R.REGION_TYPE = 2
5066: AND ( UPPER(R.CITY_CODE) = UPPER(p_city_code)
5067: OR ( p_city_code is NULL ) )
5068: AND ( decode(p_city_code, null, UPPER(R.STATE_CODE), NVL(UPPER(R.STATE_CODE), UPPER(p_state_code) )) = UPPER(p_state_code)
5072: --
5073: -- Debug Statements
5074: --
5075: IF l_debug_on THEN
5076: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
5077: END IF;
5078: --
5079:
5080: END IF; -- } Region type 2,3 i.e., City, Postal Codes
5099: --
5100: -- PROCEDURE : Insert_Global_Table
5101: --
5102: -- PURPOSE : Inserts the data in Global Temp tables
5103: -- ( Wsh_Regions_Global_Data and Wsh_Regions_Global tables )
5104:
5105: PROCEDURE Insert_Global_Table (
5106: p_country IN VARCHAR2,
5107: p_state IN VARCHAR2,
5120: AS
5121:
5122: CURSOR parent_region IS
5123: SELECT parent_region_id
5124: FROM WSH_REGIONS
5125: WHERE REGION_ID = p_region_id;
5126:
5127: l_region_id NUMBER;
5128: l_parent_region_id NUMBER;
5177: ELSE
5178: l_parent_region_id := p_parent_region_id;
5179: END IF;
5180:
5181: INSERT INTO wsh_regions_global_data
5182: ( REGION_ID,
5183: REGION_TYPE,
5184: PARENT_REGION_ID,
5185: COUNTRY,
5208: --
5209: -- Debug Statements
5210: --
5211: IF l_debug_on THEN
5212: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
5213: END IF;
5214: --
5215:
5216: IF ( p_tl_only_flag <> 'Y' AND
5215:
5216: IF ( p_tl_only_flag <> 'Y' AND
5217: p_region_type in ( 0, 1, 2 ) )
5218: THEN
5219: INSERT INTO wsh_regions_global
5220: ( REGION_ID,
5221: REGION_TYPE,
5222: COUNTRY_CODE,
5223: STATE_CODE,
5231: --
5232: -- Debug Statements
5233: --
5234: IF l_debug_on THEN
5235: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
5236: END IF;
5237: --
5238: END IF;
5239:
5257: --
5258: -- PROCEDURE : Update_Global_Table
5259: --
5260: -- PURPOSE : Updates the data in Global Temp tables
5261: -- ( Wsh_Regions_Global_Data and Wsh_Regions_Global tables )
5262:
5263: PROCEDURE Update_Global_Table (
5264: p_country IN VARCHAR2,
5265: p_state IN VARCHAR2,
5276: AS
5277:
5278: CURSOR child_regions IS
5279: SELECT region_id, region_type, parent_region_id
5280: FROM wsh_regions_global_data
5281: START WITH region_id = p_region_id
5282: CONNECT BY PRIOR region_id = parent_region_id;
5283:
5284: CURSOR get_state_code(l_region_id NUMBER) IS
5282: CONNECT BY PRIOR region_id = parent_region_id;
5283:
5284: CURSOR get_state_code(l_region_id NUMBER) IS
5285: SELECT state_code, city_code
5286: FROM wsh_regions
5287: WHERE region_id = l_region_id;
5288:
5289: l_region_id NUMBER;
5290: l_parent_region_id NUMBER;
5291: l_region_upd_cnt NUMBER;
5292: l_region_data_upd_cnt NUMBER;
5293: l_region_ins_cnt NUMBER;
5294: l_region_data_ins_cnt NUMBER;
5295: l_update_state_code WSH_REGIONS.State_Code%TYPE;
5296: l_update_city_code WSH_REGIONS.City_Code%TYPE;
5297: t1 NUMBER;
5298: --
5299: l_debug_on BOOLEAN;
5292: l_region_data_upd_cnt NUMBER;
5293: l_region_ins_cnt NUMBER;
5294: l_region_data_ins_cnt NUMBER;
5295: l_update_state_code WSH_REGIONS.State_Code%TYPE;
5296: l_update_city_code WSH_REGIONS.City_Code%TYPE;
5297: t1 NUMBER;
5298: --
5299: l_debug_on BOOLEAN;
5300: --
5343: CLOSE get_state_code;
5344:
5345: IF ( i.region_type in ( 0, 1, 2 ) )
5346: THEN
5347: UPDATE wsh_regions_global
5348: SET COUNTRY_CODE = nvl(UPPER(p_country_code), COUNTRY_CODE),
5349: state_code = decode(i.region_type,
5350: 2, UPPER(l_update_state_code),
5351: 3, UPPER(l_update_state_code),
5360: l_region_upd_cnt := l_region_upd_cnt + sql%rowcount;
5361:
5362: IF ( sql%rowcount = 0 )
5363: THEN
5364: INSERT INTO wsh_regions_global
5365: ( REGION_ID,
5366: REGION_TYPE,
5367: COUNTRY_CODE,
5368: STATE_CODE,
5371: REGION_TYPE,
5372: COUNTRY_CODE,
5373: STATE_CODE,
5374: CITY_CODE
5375: FROM WSH_REGIONS
5376: WHERE REGION_ID = i.region_id;
5377:
5378: l_region_ins_cnt := l_region_ins_cnt + sql%rowcount;
5379: END IF;
5378: l_region_ins_cnt := l_region_ins_cnt + sql%rowcount;
5379: END IF;
5380: END IF;
5381:
5382: UPDATE wsh_regions_global_data
5383: SET country = nvl(UPPER(p_country), country),
5384: state = nvl(UPPER(p_state), state),
5385: city = nvl(UPPER(p_city), city),
5386: country_code = nvl(UPPER(p_country_code), country_code),
5401: l_region_data_upd_cnt := l_region_data_upd_cnt + sql%rowcount;
5402:
5403: IF ( sql%rowcount = 0 )
5404: THEN
5405: INSERT INTO wsh_regions_global_data
5406: ( REGION_ID,
5407: REGION_TYPE,
5408: PARENT_REGION_ID,
5409: COUNTRY,
5426: R.CITY_CODE,
5427: TL.POSTAL_CODE_FROM,
5428: TL.POSTAL_CODE_TO,
5429: TL.LANGUAGE
5430: FROM WSH_REGIONS R,
5431: WSH_REGIONS_TL TL
5432: WHERE TL.LANGUAGE = p_lang_code
5433: AND TL.REGION_ID = R.REGION_ID
5434: AND R.REGION_ID = i.region_id;
5427: TL.POSTAL_CODE_FROM,
5428: TL.POSTAL_CODE_TO,
5429: TL.LANGUAGE
5430: FROM WSH_REGIONS R,
5431: WSH_REGIONS_TL TL
5432: WHERE TL.LANGUAGE = p_lang_code
5433: AND TL.REGION_ID = R.REGION_ID
5434: AND R.REGION_ID = i.region_id;
5435:
5442: --
5443: -- Debug Statements
5444: --
5445: IF l_debug_on THEN
5446: WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global', l_region_upd_cnt);
5447: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', l_region_ins_cnt);
5448: WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global_data', l_region_data_upd_cnt);
5449: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', l_region_data_ins_cnt);
5450: END IF;
5443: -- Debug Statements
5444: --
5445: IF l_debug_on THEN
5446: WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global', l_region_upd_cnt);
5447: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', l_region_ins_cnt);
5448: WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global_data', l_region_data_upd_cnt);
5449: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', l_region_data_ins_cnt);
5450: END IF;
5451: --
5444: --
5445: IF l_debug_on THEN
5446: WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global', l_region_upd_cnt);
5447: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', l_region_ins_cnt);
5448: WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global_data', l_region_data_upd_cnt);
5449: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', l_region_data_ins_cnt);
5450: END IF;
5451: --
5452:
5445: IF l_debug_on THEN
5446: WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global', l_region_upd_cnt);
5447: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', l_region_ins_cnt);
5448: WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global_data', l_region_data_upd_cnt);
5449: WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', l_region_data_ins_cnt);
5450: END IF;
5451: --
5452:
5453: --
5466: fnd_message.set_token('MSG_TEXT', SQLERRM);
5467: fnd_file.put_line(fnd_file.log, 'UPDATE_GLOBAL_TABLE EXCEPTION : ' || sqlerrm);
5468: END Update_Global_Table;
5469:
5470: END WSH_REGIONS_PKG;
5471: