[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT WSH_REGIONS_s.nextval from dual;
SELECT WSH_REGIONS_INTERFACE_S.nextval from dual;
INSERT INTO WSH_REGIONS_INTERFACE (
REGION_ID,
REGION_TYPE,
PARENT_REGION_ID,
COUNTRY_CODE,
COUNTRY_REGION_CODE,
STATE_CODE,
CITY_CODE,
PORT_FLAG,
AIRPORT_FLAG,
ROAD_TERMINAL_FLAG,
RAIL_TERMINAL_FLAG,
LONGITUDE,
LATITUDE,
TIMEZONE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (
l_region_id,
p_region_type,
p_parent_region_id,
p_country_code,
p_country_region_code,
p_state_code,
p_city_code,
p_port_flag,
p_airport_flag,
p_road_terminal_flag,
p_rail_terminal_flag,
p_longitude,
p_latitude,
p_timezone,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
INSERT INTO WSH_REGIONS_TL_INTERFACE (
LANGUAGE,
REGION_ID,
CONTINENT,
COUNTRY,
COUNTRY_REGION,
STATE,
CITY,
ALTERNATE_NAME,
COUNTY,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (
p_lang_code,
l_region_id,
p_continent,
p_country,
p_country_region,
p_state,
p_city,
p_alternate_name,
p_county,
p_postal_code_from,
p_postal_code_to,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
INSERT INTO WSH_REGIONS (
REGION_ID,
REGION_TYPE,
PARENT_REGION_ID,
COUNTRY_CODE,
COUNTRY_REGION_CODE,
STATE_CODE,
CITY_CODE,
PORT_FLAG,
AIRPORT_FLAG,
ROAD_TERMINAL_FLAG,
RAIL_TERMINAL_FLAG,
LONGITUDE,
LATITUDE,
TIMEZONE,
ZONE_LEVEL,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DECONSOL_LOCATION_ID )
VALUES (
l_region_id,
p_region_type,
p_parent_region_id,
p_country_code,
p_country_region_code,
p_state_code,
p_city_code,
p_port_flag,
p_airport_flag,
p_road_terminal_flag,
p_rail_terminal_flag,
p_longitude,
p_latitude,
p_timezone,
p_region_type,
p_region_dff.attribute_category,
p_region_dff.attribute1,
p_region_dff.attribute2,
p_region_dff.attribute3,
p_region_dff.attribute4,
p_region_dff.attribute5,
p_region_dff.attribute6,
p_region_dff.attribute7,
p_region_dff.attribute8,
p_region_dff.attribute9,
p_region_dff.attribute10,
p_region_dff.attribute11,
p_region_dff.attribute12,
p_region_dff.attribute13,
p_region_dff.attribute14,
p_region_dff.attribute15,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_deconsol_location_id);
WSH_DEBUG_SV.logmsg(l_module_name, 'inserting region '||l_region_id);
INSERT INTO WSH_REGIONS_TL (
LANGUAGE,
REGION_ID,
CONTINENT,
COUNTRY,
COUNTRY_REGION,
STATE,
CITY,
ALTERNATE_NAME,
COUNTY,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (
p_lang_code,
l_region_id,
p_continent,
p_country,
p_country_region,
p_state,
p_city,
p_alternate_name,
p_county,
p_postal_code_from,
p_postal_code_to,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
p_insert_parent_flag IN VARCHAR2,
x_parent_region_info OUT NOCOPY wsh_regions_search_pkg.region_rec,
p_conc_request_flag IN VARCHAR2 DEFAULT 'N')
IS
l_parent_region_id_non_tl number;
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_PARENT_FLAG',P_INSERT_PARENT_FLAG);
IF (l_parent_region_id_tl = -1 AND p_interface_flag <> 'Y' AND p_insert_parent_flag = 'Y') THEN
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name, 'inserting parent');
INSERT_REGION(
p_country_code => p_country_code,
p_country_region_code => p_country_region_code,
p_state_code => p_state_code,
p_city_code => p_city_code,
p_port_flag => null,
p_airport_flag => null,
p_road_terminal_flag => null,
p_rail_terminal_flag => null,
p_longitude => null,
p_latitude => null,
p_timezone => null,
p_continent => null,
p_country => p_country,
p_country_region => p_country_region,
p_state => p_state,
p_city => p_city,
p_alternate_name => null,
p_county => null,
p_postal_code_from => null,
p_postal_code_to => null,
p_lang_code => p_lang_code,
p_interface_flag => p_interface_flag,
p_tl_only_flag => l_tl_only_flag,
p_region_id => l_existing_parent_region_id,
p_parent_region_id => null,
p_user_id => p_user_id,
p_insert_parent_flag => p_insert_parent_flag,
x_region_id => l_parent_region_id_non_tl,
x_status => l_status,
x_error_msg => l_error_msg,
p_conc_request_flag => p_conc_request_flag);
WSH_DEBUG_SV.logmsg(l_module_name, 'inserted parent id: '||l_parent_region_id_non_tl);
PROCEDURE Insert_Region (
p_country_code IN VARCHAR2,
p_country_region_code IN VARCHAR2,
p_state_code IN VARCHAR2,
p_city_code IN VARCHAR2,
p_port_flag IN VARCHAR2,
p_airport_flag IN VARCHAR2,
p_road_terminal_flag IN VARCHAR2,
p_rail_terminal_flag IN VARCHAR2,
p_longitude IN NUMBER,
p_latitude IN NUMBER,
p_timezone IN VARCHAR2,
p_continent IN VARCHAR2,
p_country IN VARCHAR2,
p_country_region IN VARCHAR2,
p_state IN VARCHAR2,
p_city IN VARCHAR2,
p_alternate_name IN VARCHAR2,
p_county IN VARCHAR2,
p_postal_code_from IN VARCHAR2,
p_postal_code_to IN VARCHAR2,
p_lang_code IN VARCHAR2,
p_interface_flag IN VARCHAR2,
p_tl_only_flag IN VARCHAR2,
p_region_id IN NUMBER,
p_parent_region_id IN NUMBER,
p_user_id IN NUMBER,
p_insert_parent_flag IN VARCHAR2,
p_region_dff IN REGION_DFF_REC DEFAULT NULL,
x_region_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2,
p_deconsol_location_id IN NUMBER DEFAULT NULL,
p_conc_request_flag IN VARCHAR2 DEFAULT 'N')
IS
l_region_type NUMBER := 0;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_REGION';
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_PARENT_FLAG',P_INSERT_PARENT_FLAG);
p_insert_parent_flag => p_insert_parent_flag,
x_parent_region_info => l_parent_region_info,
p_conc_request_flag => p_conc_request_flag);
p_insert_parent_flag => p_insert_parent_flag,
x_parent_region_info => l_parent_region_info,
p_conc_request_flag => p_conc_request_flag);
p_insert_parent_flag => p_insert_parent_flag,
x_parent_region_info => l_parent_region_info,
p_conc_request_flag => p_conc_request_flag);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.INSERT_GLOBAL_TABLE', WSH_DEBUG_SV.C_PROC_LEVEL);
Insert_Global_Table(
p_country => p_country,
p_state => p_state,
p_city => p_city,
p_country_code => p_country_code,
p_state_code => p_state_code,
p_city_code => p_city_code,
p_region_id => l_region_id,
p_region_type => l_region_type,
p_parent_region_id => l_parent_region_info.region_id,
p_postal_code_from => p_postal_code_from,
p_postal_code_to => p_postal_code_to,
p_tl_only_flag => p_tl_only_flag,
p_lang_code => p_lang_code,
x_return_status => l_return_status );
END Insert_Region;
PROCEDURE Update_Region (
p_insert_type IN VARCHAR2,
p_region_id IN NUMBER,
p_parent_region_id IN NUMBER,
p_continent IN VARCHAR2,
p_country IN VARCHAR2,
p_country_region IN VARCHAR2,
p_state IN VARCHAR2,
p_city IN VARCHAR2,
p_alternate_name IN VARCHAR2,
p_county IN VARCHAR2,
p_postal_code_from IN VARCHAR2,
p_postal_code_to IN VARCHAR2,
p_lang_code IN VARCHAR2,
p_country_code IN VARCHAR2,
p_country_region_code IN VARCHAR2,
p_state_code IN VARCHAR2,
p_city_code IN VARCHAR2,
p_port_flag IN VARCHAR2,
p_airport_flag IN VARCHAR2,
p_road_terminal_flag IN VARCHAR2,
p_rail_terminal_flag IN VARCHAR2,
p_longitude IN NUMBER,
p_latitude IN NUMBER,
p_timezone IN VARCHAR2,
p_interface_flag IN VARCHAR2,
p_user_id IN NUMBER,
p_insert_parent_flag IN VARCHAR2,
p_region_dff IN REGION_DFF_REC DEFAULT NULL,
x_region_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2,
p_deconsol_location_id IN NUMBER DEFAULT NULL,
p_conc_request_flag IN VARCHAR2 DEFAULT 'N')
IS
CURSOR child_regions(l_region_id NUMBER) IS
SELECT region_id, zone_level, parent_region_id
FROM wsh_regions
START WITH region_id = l_region_id
CONNECT BY PRIOR region_id = parent_region_id;
SELECT state_code, city_code
FROM wsh_regions
WHERE region_id =l_region_id;
SELECT zone_level
FROM wsh_regions
WHERE region_id = l_region_id;
l_update_state_code wsh_regions.state_code%type;
l_update_city_code wsh_regions.city_code%type;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_REGION';
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_PARENT_FLAG',P_INSERT_PARENT_FLAG);
IF (p_insert_type IN ('ADD','INSERT')) THEN
--
--BUG NUMBER : 3222165
--Unique region validation not done for data entered using Regions Interface Form.
--
IF (p_interface_flag<>'Y') THEN
--
-- Debug Statements
--
IF l_debug_on THEN
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_SEARCH_PKG.GET_REGION_INFO',WSH_DEBUG_SV.C_PROC_LEVEL);
INSERT_REGION(
p_country_code => p_country_code,
p_country_region_code => p_country_region_code,
p_state_code => p_state_code,
p_city_code => p_city_code,
p_port_flag => p_port_flag,
p_airport_flag => p_airport_flag,
p_road_terminal_flag => p_road_terminal_flag,
p_rail_terminal_flag => p_rail_terminal_flag,
p_longitude => p_longitude,
p_latitude => p_latitude,
p_timezone => p_timezone,
p_continent => p_continent,
p_country => p_country,
p_country_region => p_country_region,
p_state => p_state,
p_city => p_city,
p_alternate_name => p_alternate_name,
p_county => p_county,
p_postal_code_from => p_postal_code_from,
p_postal_code_to => p_postal_code_to,
p_lang_code => p_lang_code,
p_interface_flag => p_interface_flag,
p_tl_only_flag => l_tl_only_flag,
p_region_id => l_existing_region_id,
p_parent_region_id => p_parent_region_id,
p_user_id => p_user_id,
p_insert_parent_flag => p_insert_parent_flag,
p_region_dff => p_region_dff,
x_region_id => l_region_id,
x_status => l_status,
x_error_msg => l_error_msg,
p_deconsol_location_id => p_deconsol_location_id);
UPDATE WSH_REGIONS_INTERFACE
SET COUNTRY_CODE = p_country_code,
COUNTRY_REGION_CODE = p_country_region_code,
STATE_CODE = p_state_code,
CITY_CODE = p_city_code,
PORT_FLAG = p_port_flag,
AIRPORT_FLAG = p_airport_flag,
ROAD_TERMINAL_FLAG = p_road_terminal_flag,
RAIL_TERMINAL_FLAG = p_rail_terminal_flag,
PROCESSED_FLAG = null,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = p_user_id
WHERE REGION_ID = l_region_id;
UPDATE WSH_REGIONS_TL_INTERFACE
SET CONTINENT = p_continent,
COUNTRY = p_country,
COUNTRY_REGION = p_country_region,
STATE = p_state,
CITY = p_city,
ALTERNATE_NAME = p_alternate_name,
COUNTY = p_county,
POSTAL_CODE_FROM = p_postal_code_from,
POSTAL_CODE_TO = p_postal_code_to,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = p_user_id
WHERE REGION_ID = l_region_id
AND LANGUAGE = p_lang_code;
INSERT INTO WSH_REGIONS_TL_INTERFACE (
LANGUAGE,
REGION_ID,
CONTINENT,
COUNTRY,
COUNTRY_REGION,
STATE,
CITY,
ALTERNATE_NAME,
COUNTY,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (
p_lang_code,
l_region_id,
p_continent,
p_country,
p_country_region,
p_state,
p_city,
p_alternate_name,
p_county,
p_postal_code_from,
p_postal_code_to,
p_user_id,
sysdate,
p_user_id,
sysdate,
p_user_id);
UPDATE WSH_REGIONS
SET ATTRIBUTE_CATEGORY = p_region_dff.attribute_category,
ATTRIBUTE1 = p_region_dff.attribute1,
ATTRIBUTE2 = p_region_dff.attribute2,
ATTRIBUTE3 = p_region_dff.attribute3,
ATTRIBUTE4 = p_region_dff.attribute4,
ATTRIBUTE5 = p_region_dff.attribute5,
ATTRIBUTE6 = p_region_dff.attribute6,
ATTRIBUTE7 = p_region_dff.attribute7,
ATTRIBUTE8 = p_region_dff.attribute8,
ATTRIBUTE9 = p_region_dff.attribute9,
ATTRIBUTE10 = p_region_dff.attribute10,
ATTRIBUTE11 = p_region_dff.attribute11,
ATTRIBUTE12 = p_region_dff.attribute12,
ATTRIBUTE13 = p_region_dff.attribute13,
ATTRIBUTE14 = p_region_dff.attribute14,
ATTRIBUTE15 = p_region_dff.attribute15
WHERE region_id = l_region_id;
SELECT zone_level INTO l_parent_zone_level
FROM wsh_regions WHERE region_id = l_region_id;
/* select state_code
into l_update_state_code
from wsh_regions
where region_id = reg.parent_region_id ;
FETCH get_state_code INTO l_update_state_code, l_update_city_code;
update WSH_REGIONS set
COUNTRY_CODE = nvl(p_country_code, COUNTRY_CODE),
COUNTRY_REGION_CODE = nvl(p_country_region_code, COUNTRY_REGION_CODE),
STATE_CODE = decode(reg.zone_level, 2, l_update_state_code, 3, l_update_state_code,
decode(l_parent_zone_level, 1, p_state_code, nvl(p_state_code, STATE_CODE))),
CITY_CODE = decode(l_parent_zone_level, 2, p_city_code, nvl(p_city_code, CITY_CODE)),
PORT_FLAG = nvl(p_port_flag,PORT_FLAG),
AIRPORT_FLAG = nvl(p_airport_flag,AIRPORT_FLAG),
ROAD_TERMINAL_FLAG = nvl(p_road_terminal_flag, ROAD_TERMINAL_FLAG),
RAIL_TERMINAL_FLAG = nvl(p_rail_terminal_flag, RAIL_TERMINAL_FLAG),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = p_user_id,
-- bug 4509707 : deconsol_location_id should not be propagated to sub-regions, update it only for the current region (l_region_id)
-- deconsol_location_id = p_deconsol_location_id
deconsol_location_id = decode(reg.region_id, l_region_id, p_deconsol_location_id, deconsol_location_id)
where REGION_ID = reg.region_id;
update WSH_REGIONS_TL set
CONTINENT = nvl(p_continent, CONTINENT),
COUNTRY = nvl(p_country, COUNTRY),
COUNTRY_REGION = nvl(p_country_region, COUNTRY_REGION),
STATE = nvl(p_state, STATE),
CITY = nvl(p_city, CITY),
ALTERNATE_NAME = nvl(p_alternate_name, ALTERNATE_NAME),
COUNTY = nvl(p_county, COUNTY),
POSTAL_CODE_FROM = nvl(p_postal_code_from, POSTAL_CODE_FROM),
POSTAL_CODE_TO = nvl(p_postal_code_to, POSTAL_CODE_TO),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = p_user_id
where REGION_ID = reg.region_id and
LANGUAGE = p_lang_code;
IF (p_insert_type = 'SYNC') THEN
INSERT INTO WSH_REGIONS_TL (
LANGUAGE,
REGION_ID,
CONTINENT,
COUNTRY,
COUNTRY_REGION,
STATE,
CITY,
ALTERNATE_NAME,
COUNTY,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (
p_lang_code,
l_region_id,
p_continent,
p_country,
p_country_region,
p_state,
p_city,
p_alternate_name,
p_county,
p_postal_code_from,
p_postal_code_to,
p_user_id,
sysdate,
p_user_id,
sysdate,
p_user_id);
else --could not find the region to update
x_status := 2;
x_error_msg := 'WSH_CAT_CANNOT_UPDATE_REGION';
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.UPDATE_GLOBAL_TABLE', WSH_DEBUG_SV.C_PROC_LEVEL);
Update_Global_Table(
p_country => p_country,
p_state => p_state,
p_city => p_city,
p_country_code => p_country_code,
p_state_code => p_state_code,
p_city_code => p_city_code,
p_region_id => l_region_id,
p_postal_code_from => p_postal_code_from,
p_postal_code_to => p_postal_code_to,
p_parent_zone_level => l_parent_zone_level,
p_lang_code => p_lang_code,
x_return_status => l_return_status );
IF (p_insert_type = 'SYNC') THEN
INSERT_REGION(
p_country_code => p_country_code,
p_country_region_code => p_country_region_code,
p_state_code => p_state_code,
p_city_code => p_city_code,
p_port_flag => p_port_flag,
p_airport_flag => p_airport_flag,
p_road_terminal_flag => p_road_terminal_flag,
p_rail_terminal_flag => p_rail_terminal_flag,
p_longitude => p_longitude,
p_latitude => p_latitude,
p_timezone => p_timezone,
p_continent => p_continent,
p_country => p_country,
p_country_region => p_country_region,
p_state => p_state,
p_city => p_city,
p_alternate_name => p_alternate_name,
p_county => p_county,
p_postal_code_from => p_postal_code_from,
p_postal_code_to => p_postal_code_to,
p_lang_code => p_lang_code,
p_interface_flag => p_interface_flag,
p_tl_only_flag => l_tl_only_flag,
p_region_id => l_existing_region_id,
p_parent_region_id => p_parent_region_id,
p_user_id => p_user_id,
p_insert_parent_flag => p_insert_parent_flag,
p_region_dff => p_region_dff,
x_region_id => l_region_id,
x_status => l_status,
x_error_msg => l_error_msg,
p_deconsol_location_id => p_deconsol_location_id,
p_conc_request_flag => p_conc_request_flag);
x_error_msg := 'WSH_CAT_CANNOT_UPDATE_REGION';
END Update_Region;
PROCEDURE Delete_Region (
p_region_id IN NUMBER,
p_lang_code IN VARCHAR2,
p_interface_flag IN VARCHAR2,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR lock_rows IS
SELECT r.region_id
FROM wsh_regions_interface r, wsh_regions_tl_interface t
WHERE r.region_id = t.region_id AND
r.region_id = p_region_id AND
t.language = nvl(p_lang_code, t.language)
FOR UPDATE OF r.region_id NOWAIT;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'DELETE_REGION';
DELETE FROM wsh_regions_tl_interface WHERE region_id = l_region_id AND language= nvl(p_lang_code, language);
DELETE FROM wsh_regions_interface WHERE region_id = l_region_id;
x_error_msg := 'Cannot delete region';
END Delete_Region;
SELECT w.region_id, w.country_code, w.state_code,w.city_code,w.attribute_category,
w.attribute1,w.attribute2,w.attribute3,w.attribute4,w.attribute5,
w.attribute6,w.attribute7,w.attribute8,w.attribute9,w.attribute10,
w.attribute11,w.attribute12,w.attribute13,w.attribute14,w.attribute15,
w.deconsol_location_id
FROM wsh_regions w
WHERE w.region_id = p_region_id
FOR UPDATE OF w.region_id NOWAIT;
SELECT t.region_id, t.country, t.state, t.city, t.postal_code_from, t.postal_code_to
FROM wsh_regions_tl t
WHERE t.language = p_lang_code
AND t.region_id = p_region_id
FOR UPDATE OF t.region_id NOWAIT;
FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
SELECT w.region_id, w.country_code, w.state_code, w.city_code
FROM wsh_regions_interface w
WHERE w.region_id = p_region_id
FOR UPDATE OF w.region_id NOWAIT;
SELECT t.region_id, t.country, t.state, t.city, t.postal_code_from, t.postal_code_to
FROM wsh_regions_tl_interface t
WHERE t.language = p_lang_code
AND t.region_id = p_region_id
FOR UPDATE OF t.region_id NOWAIT;
FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
PROCEDURE Update_Zone (
p_insert_type IN VARCHAR2,
p_zone_id IN NUMBER,
p_zone_name IN VARCHAR2,
p_zone_level IN NUMBER,
p_lang_code IN VARCHAR2,
p_user_id IN NUMBER,
p_zone_dff IN REGION_DFF_REC DEFAULT NULL,
x_zone_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2,
p_deconsol_location_id IN NUMBER DEFAULT NULL) IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ZONE';
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
Update_Zone (
p_insert_type => p_insert_type,
p_zone_id => p_zone_id,
p_zone_name => p_zone_name,
p_zone_level => p_zone_level,
p_zone_type => 10,
p_lang_code => p_lang_code,
p_user_id => p_user_id,
p_zone_dff => p_zone_dff,
x_zone_id => x_zone_id,
x_status => x_status,
x_error_msg => x_error_msg,
p_deconsol_location_id => p_deconsol_location_id);
PROCEDURE Update_Zone (
p_insert_type IN VARCHAR2,
p_zone_id IN NUMBER,
p_zone_name IN VARCHAR2,
p_zone_level IN NUMBER,
p_zone_type IN NUMBER,
p_lang_code IN VARCHAR2,
p_user_id IN NUMBER,
p_zone_dff IN REGION_DFF_REC DEFAULT NULL,
x_zone_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2,
p_deconsol_location_id IN NUMBER DEFAULT NULL) IS
CURSOR check_zone IS
SELECT region_id
FROM wsh_regions_tl
WHERE zone = p_zone_name AND
language = p_lang_code;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ZONE';
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
IF (p_insert_type IN ('ADD','INSERT')) THEN
IF (l_zone_id <> 0) THEN
x_status := 2;
INSERT INTO WSH_REGIONS (
REGION_ID,
REGION_TYPE,
PARENT_REGION_ID,
ZONE_LEVEL,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DECONSOL_LOCATION_ID)
VALUES (
wsh_regions_s.nextval,
p_zone_type,
-1,
p_zone_level,
p_zone_dff.attribute_category,
p_zone_dff.attribute1,
p_zone_dff.attribute2,
p_zone_dff.attribute3,
p_zone_dff.attribute4,
p_zone_dff.attribute5,
p_zone_dff.attribute6,
p_zone_dff.attribute7,
p_zone_dff.attribute8,
p_zone_dff.attribute9,
p_zone_dff.attribute10,
p_zone_dff.attribute11,
p_zone_dff.attribute12,
p_zone_dff.attribute13,
p_zone_dff.attribute14,
p_zone_dff.attribute15,
p_user_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
p_deconsol_location_id)
RETURNING region_id
INTO l_zone_id;
INSERT INTO WSH_REGIONS_TL (
LANGUAGE,
REGION_ID,
ZONE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (
p_lang_code,
l_zone_id,
p_zone_name,
p_user_id,
sysdate,
p_user_id,
sysdate,
p_user_id);
ELSE -- for update
IF (l_zone_id >= 0 AND l_zone_id <> p_zone_id) THEN
x_status := 2;
UPDATE wsh_regions
SET zone_level = p_zone_level,
attribute_category = p_zone_dff.attribute_category,
attribute1 = p_zone_dff.attribute1,
attribute2 = p_zone_dff.attribute2,
attribute3 = p_zone_dff.attribute3,
attribute4 = p_zone_dff.attribute4,
attribute5 = p_zone_dff.attribute5,
attribute6 = p_zone_dff.attribute6,
attribute7 = p_zone_dff.attribute7,
attribute8 = p_zone_dff.attribute8,
attribute9 = p_zone_dff.attribute9,
attribute10 = p_zone_dff.attribute10,
attribute11 = p_zone_dff.attribute11,
attribute12 = p_zone_dff.attribute12,
attribute13 = p_zone_dff.attribute13,
attribute14 = p_zone_dff.attribute14,
attribute15 = p_zone_dff.attribute15,
last_updated_by = p_user_id,
last_update_date = sysdate,
deconsol_location_id = p_deconsol_location_id
WHERE region_id = p_zone_id;
UPDATE wsh_regions_tl
SET zone = p_zone_name,
language = p_lang_code,
last_updated_by = p_user_id,
last_update_date = sysdate
WHERE region_id = p_zone_id;
END Update_Zone;
SELECT w.region_id, t.zone, w.zone_level
FROM wsh_regions w, wsh_regions_tl t
WHERE w.region_id = t.region_id
AND t.language = p_lang_code
FOR UPDATE OF w.region_id, t.region_id NOWAIT;
FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
PROCEDURE Update_Zone_Region (
p_insert_type IN VARCHAR2,
p_zone_region_id IN NUMBER,
p_zone_id IN NUMBER,
p_country IN VARCHAR2,
p_state IN VARCHAR2,
p_city IN VARCHAR2,
p_postal_code_from IN VARCHAR2,
p_postal_code_to IN VARCHAR2,
p_lang_code IN VARCHAR2,
p_country_code IN VARCHAR2,
p_state_code IN VARCHAR2,
p_city_code IN VARCHAR2,
p_user_id IN NUMBER,
x_zone_region_id OUT NOCOPY NUMBER,
x_region_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
--
l_debug_on BOOLEAN;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ZONE_REGION';
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
Update_Zone_Region(
p_insert_type => p_insert_type,
p_zone_region_id => p_zone_region_id,
p_zone_id => p_zone_id,
p_region_id => null,
p_country => p_country,
p_state => p_state,
p_city => p_city,
p_postal_code_from => p_postal_code_from,
p_postal_code_to => p_postal_code_to,
p_lang_code => p_lang_code,
p_country_code => p_country_code,
p_state_code => p_state_code,
p_city_code => p_city_code,
p_user_id => p_user_id,
p_zone_type => '10',
x_zone_region_id => x_zone_region_id,
x_region_id => x_region_id,
x_status => x_status,
x_error_msg => x_error_msg);
END Update_Zone_Region;
PROCEDURE Update_Zone_Region (
p_insert_type IN VARCHAR2,
p_zone_region_id IN NUMBER,
p_zone_id IN NUMBER,
p_region_id IN NUMBER,
p_country IN VARCHAR2,
p_state IN VARCHAR2,
p_city IN VARCHAR2,
p_postal_code_from IN VARCHAR2,
p_postal_code_to IN VARCHAR2,
p_lang_code IN VARCHAR2,
p_country_code IN VARCHAR2,
p_state_code IN VARCHAR2,
p_city_code IN VARCHAR2,
p_user_id IN NUMBER,
p_zone_type IN VARCHAR2,
x_zone_region_id OUT NOCOPY NUMBER,
x_region_id OUT NOCOPY NUMBER,
x_status OUT NOCOPY NUMBER,
x_error_msg OUT NOCOPY VARCHAR2) IS
CURSOR check_regions_in_zone(l_region_id NUMBER, l_codeFrom VARCHAR2, l_codeTo VARCHAR2) IS
SELECT region_id
FROM wsh_zone_regions z
WHERE z.region_id in (
SELECT region_id
FROM wsh_regions
START WITH region_id = l_region_id
CONNECT BY PRIOR parent_region_id = region_id) AND
z.parent_region_id = p_zone_id
and ( nvl(l_codeFrom,'0') between nvl(z.postal_code_from,'0') and nvl(z.postal_code_to,'ZZZZZZZZZZZZZ')
or nvl(l_codeTo,'0') between nvl(z.postal_code_from,'0') and nvl(z.postal_code_to,'ZZZZZZZZZZZZZZ')
or nvl(z.postal_code_from,'0') between nvl(l_codeFrom,'0') and nvl(l_codeTo,'ZZZZZZZZZZZZ'));
SELECT region_id
FROM wsh_regions r
WHERE region_id = l_region_id
START WITH r.region_id in (
SELECT region_id
FROM wsh_zone_regions
WHERE parent_region_id = p_zone_id)
CONNECT BY PRIOR parent_region_id = region_id;
SELECT region_id
FROM wsh_zone_regions z
WHERE z.region_id in (
SELECT region_id
FROM wsh_regions
START WITH region_id = l_region_id
CONNECT BY PRIOR parent_region_id = region_id) AND
z.parent_region_id = p_zone_id AND
l_codeFrom = z.postal_code_from AND
l_codeTo = z.postal_code_to;
SELECT zone_level
FROM wsh_regions
WHERE region_id = p_zone_id;
SELECT region_type
FROM wsh_regions
WHERE region_id = p_region_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_ZONE_REGION';
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_TYPE',P_INSERT_TYPE);
IF (p_zone_region_id > 0 AND p_insert_type = 'DELETE') THEN
SELECT count(*)
INTO l_region_count
FROM wsh_zone_regions
WHERE parent_region_id = p_zone_id;
DELETE FROM wsh_zone_regions
WHERE zone_region_id = p_zone_region_id;
IF (p_insert_type IN ('ADD','INSERT')) THEN
INSERT INTO WSH_ZONE_REGIONS (
ZONE_REGION_ID,
REGION_ID,
PARENT_REGION_ID,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ZONE_FLAG,
PARTY_ID)
VALUES (
wsh_zone_regions_s.nextval,
l_region_id,
p_zone_id,
p_postal_code_from,
p_postal_code_to,
p_user_id,
sysdate,
p_user_id,
sysdate,
p_user_id,
'Y',
-1)
RETURNING zone_region_id
INTO x_zone_region_id;
UPDATE wsh_regions
SET zone_level = l_region_type
WHERE region_id = p_zone_id;
ELSIF (p_zone_region_id > 0 AND p_insert_type IN ('UPDATE','SYNC')) THEN
UPDATE wsh_zone_regions
SET region_id = l_region_id,
postal_code_from = p_postal_code_from,
postal_code_to = p_postal_code_to,
last_updated_by = p_user_id,
last_update_date = sysdate
WHERE zone_region_id = p_zone_region_id;
END Update_Zone_Region;
SELECT zone_region_id, parent_region_id , region_id
FROM wsh_zone_regions
WHERE zone_region_id = p_zone_region_id
FOR UPDATE OF zone_region_id NOWAIT;
FND_MESSAGE.Set_Name('FND','FORM_RECORD_DELETED');
SELECT R.REGION_ID,
R.COUNTRY_CODE,
R.COUNTRY_REGION_CODE,
R.STATE_CODE,
R.CITY_CODE,
R.PORT_FLAG,
R.AIRPORT_FLAG,
R.ROAD_TERMINAL_FLAG,
R.RAIL_TERMINAL_FLAG,
R.LONGITUDE,
R.LATITUDE,
R.TIMEZONE,
TL.CONTINENT,
TL.COUNTRY,
TL.COUNTRY_REGION,
TL.STATE,
TL.CITY,
TL.ALTERNATE_NAME,
TL.COUNTY,
TL.POSTAL_CODE_FROM,
TL.POSTAL_CODE_TO,
TL.LANGUAGE
FROM WSH_REGIONS_INTERFACE R,
WSH_REGIONS_TL_INTERFACE TL
WHERE R.REGION_ID = TL.REGION_ID
AND TL.ZONE IS NULL -- We are not processing zones here .
AND PROCESSED_FLAG is null
ORDER BY TL.COUNTRY, R.COUNTRY_CODE,
NVL(TL.STATE, 1), NVL(R.STATE_CODE, 1),
NVL(TL.CITY, 1), NVL(R.CITY_CODE, 1),
NVL(TL.POSTAL_CODE_FROM, 1);
WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_REGIONS_PKG.UPDATE_REGION', WSH_DEBUG_SV.C_PROC_LEVEL);
Update_Region(
p_insert_type => 'SYNC',
p_region_id => -1,
p_parent_region_id => -1,
p_continent => l_continent_rec(i),
p_country => l_country_rec(i),
p_country_region => l_country_region_rec(i),
p_state => l_state_rec(i),
p_city => l_city_rec(i),
p_alternate_name => l_alternate_name_rec(i),
p_county => l_county_rec(i),
p_postal_code_from => l_postal_code_from_rec(i),
p_postal_code_to => l_postal_code_to_rec(i),
p_lang_code => l_language_rec(i),
p_country_code => l_country_code_rec(i),
p_country_region_code => l_country_region_code_rec(i),
p_state_code => l_state_code_rec(i),
p_city_code => l_city_code_rec(i),
p_port_flag => l_port_flag_rec(i),
p_airport_flag => l_airport_flag_rec(i),
p_road_terminal_flag => l_road_terminal_flag_rec(i),
p_rail_terminal_flag => l_rail_terminal_flag_rec(i),
p_longitude => l_longitude_rec(i),
p_latitude => l_latitude_rec(i),
p_timezone => l_timezone_rec(i),
p_interface_flag => 'N',
p_user_id => -1,
p_insert_parent_flag => 'Y',
x_region_id => l_region_id,
x_status => l_status,
x_error_msg => l_error_msg,
p_conc_request_flag => 'Y'); -- p_conc_request_flag
UPDATE wsh_regions_interface
SET processed_flag = 'Y'
WHERE region_id = l_upd_region_id(i);
l_upd_region_id.DELETE;
DELETE FROM WSH_REGIONS_INTERFACE WHERE REGION_ID = l_del_region_id(i);
DELETE FROM WSH_REGIONS_TL_INTERFACE WHERE REGION_ID = l_del_region_id(i);
l_del_region_id.DELETE;
delete from wsh_regions_global_data;
delete from wsh_regions_global;
SELECT count(*)
FROM wsh_Regions_interface
WHERE processed_flag is null;
SELECT 1
FROM wsh_Regions_interface
WHERE processed_flag is null;
SELECT T.REGION_ID REGION_ID, T.COUNTRY COUNTRY, T.STATE STATE, T.CITY CITY,
ltrim(P.POSTAL_CODE_FROM,'0') PCODE_FROM,
ltrim(P.POSTAL_CODE_TO,'0') PCODE_TO
FROM WSH_REGIONS_TL T, WSH_ZONE_REGIONS P
WHERE T.LANGUAGE = p_lang
AND T.REGION_ID = P.REGION_ID
AND P.PARENT_REGION_ID = p_zoneId;
delete from WSH_REGIONS_TL T
where not exists
(select NULL
from WSH_REGIONS B
where B.REGION_ID = T.REGION_ID
);
update WSH_REGIONS_TL T set (
CONTINENT,
COUNTRY,
COUNTRY_REGION,
STATE,
CITY,
ZONE,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
ALTERNATE_NAME,
COUNTY
) = (select
B.CONTINENT,
B.COUNTRY,
B.COUNTRY_REGION,
B.STATE,
B.CITY,
B.ZONE,
B.POSTAL_CODE_FROM,
B.POSTAL_CODE_TO,
B.ALTERNATE_NAME,
B.COUNTY
from WSH_REGIONS_TL B
where B.REGION_ID = T.REGION_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.REGION_ID,
T.LANGUAGE
) in (select
SUBT.REGION_ID,
SUBT.LANGUAGE
from WSH_REGIONS_TL SUBB, WSH_REGIONS_TL SUBT
where SUBB.REGION_ID = SUBT.REGION_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.CONTINENT <> SUBT.CONTINENT
or (SUBB.CONTINENT is null and SUBT.CONTINENT is not null)
or (SUBB.CONTINENT is not null and SUBT.CONTINENT is null)
or SUBB.COUNTRY <> SUBT.COUNTRY
or (SUBB.COUNTRY is null and SUBT.COUNTRY is not null)
or (SUBB.COUNTRY is not null and SUBT.COUNTRY is null)
or SUBB.COUNTRY_REGION <> SUBT.COUNTRY_REGION
or (SUBB.COUNTRY_REGION is null and SUBT.COUNTRY_REGION is not null)
or (SUBB.COUNTRY_REGION is not null and SUBT.COUNTRY_REGION is null)
or SUBB.STATE <> SUBT.STATE
or (SUBB.STATE is null and SUBT.STATE is not null)
or (SUBB.STATE is not null and SUBT.STATE is null)
or SUBB.CITY <> SUBT.CITY
or (SUBB.CITY is null and SUBT.CITY is not null)
or (SUBB.CITY is not null and SUBT.CITY is null)
or SUBB.ZONE <> SUBT.ZONE
or (SUBB.ZONE is null and SUBT.ZONE is not null)
or (SUBB.ZONE is not null and SUBT.ZONE is null)
or SUBB.POSTAL_CODE_FROM <> SUBT.POSTAL_CODE_FROM
or (SUBB.POSTAL_CODE_FROM is null and SUBT.POSTAL_CODE_FROM is not null)
or (SUBB.POSTAL_CODE_FROM is not null and SUBT.POSTAL_CODE_FROM is null)
or SUBB.POSTAL_CODE_TO <> SUBT.POSTAL_CODE_TO
or (SUBB.POSTAL_CODE_TO is null and SUBT.POSTAL_CODE_TO is not null)
or (SUBB.POSTAL_CODE_TO is not null and SUBT.POSTAL_CODE_TO is null)
or SUBB.ALTERNATE_NAME <> SUBT.ALTERNATE_NAME
or (SUBB.ALTERNATE_NAME is null and SUBT.ALTERNATE_NAME is not null)
or (SUBB.ALTERNATE_NAME is not null and SUBT.ALTERNATE_NAME is null)
or SUBB.COUNTY <> SUBT.COUNTY
or (SUBB.COUNTY is null and SUBT.COUNTY is not null)
or (SUBB.COUNTY is not null and SUBT.COUNTY is null)
));
insert into WSH_REGIONS_TL (
REGION_ID,
CONTINENT,
COUNTRY,
COUNTRY_REGION,
STATE,
CITY,
ZONE,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
ALTERNATE_NAME,
COUNTY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.REGION_ID,
B.CONTINENT,
B.COUNTRY,
B.COUNTRY_REGION,
B.STATE,
B.CITY,
B.ZONE,
B.POSTAL_CODE_FROM,
B.POSTAL_CODE_TO,
B.ALTERNATE_NAME,
B.COUNTY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from WSH_REGIONS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from WSH_REGIONS_TL T
where T.REGION_ID = B.REGION_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
SELECT DISTINCT UPPER(STATE)
FROM WSH_REGIONS_TL
WHERE UPPER(CITY) = UPPER(p_city)
AND UPPER(COUNTRY) = UPPER(p_country);
SELECT DISTINCT UPPER(R.STATE_CODE)
FROM WSH_REGIONS R
WHERE ( UPPER(R.CITY_CODE) = UPPER(p_city_code) OR p_city_code is null )
AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
AND REGION_TYPE = 2;
DELETE FROM WSH_REGIONS_GLOBAL_DATA;
WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
INSERT INTO wsh_regions_global_data
( REGION_ID,
REGION_TYPE,
PARENT_REGION_ID,
COUNTRY,
STATE,
CITY,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
LANGUAGE )
SELECT R.REGION_ID,
R.REGION_TYPE,
R.PARENT_REGION_ID,
UPPER(TL.COUNTRY),
UPPER(TL.STATE),
UPPER(TL.CITY),
UPPER(R.COUNTRY_CODE),
UPPER(R.STATE_CODE),
UPPER(R.CITY_CODE),
TL.POSTAL_CODE_FROM,
TL.POSTAL_CODE_TO,
TL.LANGUAGE
FROM WSH_REGIONS R,
WSH_REGIONS_TL TL
WHERE R.REGION_ID = TL.REGION_ID
AND R.REGION_TYPE = 0
AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
OR UPPER(TL.COUNTRY) = UPPER(p_country) );
WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
DELETE FROM WSH_REGIONS_GLOBAL;
WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
INSERT INTO wsh_regions_global
( REGION_ID,
REGION_TYPE,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE )
SELECT R.REGION_ID,
R.REGION_TYPE,
UPPER(R.COUNTRY_CODE),
UPPER(R.STATE_CODE),
UPPER(R.CITY_CODE)
FROM WSH_REGIONS R
WHERE R.REGION_TYPE = 0
AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code);
WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
DELETE FROM WSH_REGIONS_GLOBAL_DATA
WHERE REGION_TYPE in ( 1, 2, 3 );
WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
INSERT INTO wsh_regions_global_data
( REGION_ID,
REGION_TYPE,
PARENT_REGION_ID,
COUNTRY,
STATE,
CITY,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
LANGUAGE )
SELECT R.REGION_ID,
R.REGION_TYPE,
R.PARENT_REGION_ID,
UPPER(TL.COUNTRY),
UPPER(TL.STATE),
UPPER(TL.CITY),
UPPER(R.COUNTRY_CODE),
UPPER(R.STATE_CODE),
UPPER(R.CITY_CODE),
TL.POSTAL_CODE_FROM,
TL.POSTAL_CODE_TO,
TL.LANGUAGE
FROM WSH_REGIONS R,
WSH_REGIONS_TL TL
WHERE R.REGION_ID = TL.REGION_ID
AND R.REGION_TYPE = 1
AND (
( ( UPPER(R.STATE_CODE) = UPPER(p_state_code)
OR ( UPPER(R.STATE_CODE) in ( l_tmp_state_code ) )
OR ( p_state_code is NULL and p_city_code is null ) ) )
OR ( ( UPPER(TL.STATE) = UPPER(p_state)
OR ( UPPER(TL.STATE) in ( l_tmp_state ) )
OR ( p_state is null and p_city is null) ) )
)
AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
OR UPPER(TL.COUNTRY) = UPPER(p_country) );
WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
DELETE FROM WSH_REGIONS_GLOBAL
WHERE REGION_TYPE in ( 1, 2, 3 );
WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
INSERT INTO wsh_regions_global
( REGION_ID,
REGION_TYPE,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE )
SELECT R.REGION_ID,
R.REGION_TYPE,
UPPER(R.COUNTRY_CODE),
UPPER(R.STATE_CODE),
UPPER(R.CITY_CODE)
FROM WSH_REGIONS R
WHERE R.REGION_TYPE = 1
AND ( UPPER(R.STATE_CODE) = UPPER(p_state_code)
OR ( UPPER(R.STATE_CODE) in ( l_tmp_state_code ) )
OR ( p_state_code is NULL and p_city_code is null) )
AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code);
WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
DELETE FROM WSH_REGIONS_GLOBAL_DATA
WHERE REGION_TYPE in ( 2, 3 );
WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global_data', sql%rowcount);
INSERT INTO wsh_regions_global_data
( REGION_ID,
REGION_TYPE,
PARENT_REGION_ID,
COUNTRY,
STATE,
CITY,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
LANGUAGE )
SELECT R.REGION_ID,
R.REGION_TYPE,
R.PARENT_REGION_ID,
UPPER(TL.COUNTRY),
UPPER(TL.STATE),
UPPER(TL.CITY),
UPPER(R.COUNTRY_CODE),
UPPER(R.STATE_CODE),
UPPER(R.CITY_CODE),
TL.POSTAL_CODE_FROM,
TL.POSTAL_CODE_TO,
TL.LANGUAGE
FROM WSH_REGIONS R,
WSH_REGIONS_TL TL
WHERE R.REGION_ID = TL.REGION_ID
AND (
( ( UPPER(R.CITY_CODE) = UPPER(p_city_code) OR p_city_code is NULL )
AND ( decode(p_city_code, null, UPPER(R.STATE_CODE), NVL(UPPER(R.STATE_CODE), UPPER(p_state_code) )) = UPPER(p_state_code)
OR ( p_state_code is NULL ) ) )
OR ( ( UPPER(TL.CITY) = UPPER(p_city) OR ( p_city is NULL ) )
AND ( decode( p_city, null, UPPER(TL.STATE), NVL(UPPER(TL.STATE), UPPER(p_state) )) = UPPER(p_state)
OR ( p_state is null ) ) )
)
AND ( UPPER(R.COUNTRY_CODE) = UPPER(p_country_code)
OR UPPER(TL.COUNTRY) = UPPER(p_country) );
WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
DELETE FROM WSH_REGIONS_GLOBAL
WHERE REGION_TYPE in ( 2, 3 );
WSH_DEBUG_SV.log(l_module_name, 'No of rows deleted from wsh_regions_global', sql%rowcount);
INSERT INTO wsh_regions_global
( REGION_ID,
REGION_TYPE,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE )
SELECT R.REGION_ID,
R.REGION_TYPE,
UPPER(R.COUNTRY_CODE),
UPPER(R.STATE_CODE),
UPPER(R.CITY_CODE)
FROM WSH_REGIONS R
WHERE R.REGION_TYPE = 2
AND ( UPPER(R.CITY_CODE) = UPPER(p_city_code)
OR ( p_city_code is NULL ) )
AND ( decode(p_city_code, null, UPPER(R.STATE_CODE), NVL(UPPER(R.STATE_CODE), UPPER(p_state_code) )) = UPPER(p_state_code)
OR ( p_state_code is NULL ) )
AND UPPER(R.COUNTRY_CODE) = UPPER(p_country_code);
WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
PROCEDURE Insert_Global_Table (
p_country IN VARCHAR2,
p_state IN VARCHAR2,
p_city IN VARCHAR2,
p_country_code IN VARCHAR2,
p_state_code IN VARCHAR2,
p_city_code IN VARCHAR2,
p_region_id IN NUMBER ,
p_region_type IN NUMBER ,
p_parent_region_id IN NUMBER ,
p_postal_code_from IN VARCHAR2,
p_postal_code_to IN VARCHAR2,
p_tl_only_flag IN VARCHAR2,
p_lang_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2 )
AS
CURSOR parent_region IS
SELECT parent_region_id
FROM WSH_REGIONS
WHERE REGION_ID = p_region_id;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'INSERT_GLOBAL_TABLE';
INSERT INTO wsh_regions_global_data
( REGION_ID,
REGION_TYPE,
PARENT_REGION_ID,
COUNTRY,
STATE,
CITY,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
LANGUAGE )
VALUES
( p_region_id,
p_region_type,
l_parent_region_id,
UPPER(p_country),
UPPER(p_state),
UPPER(p_city),
UPPER(p_country_code),
UPPER(p_state_code),
UPPER(p_city_code),
p_postal_code_from,
p_postal_code_to,
p_lang_code );
WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', sql%rowcount);
INSERT INTO wsh_regions_global
( REGION_ID,
REGION_TYPE,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE )
VALUES
( p_region_id,
p_region_type,
UPPER(p_country_code),
UPPER(p_state_code),
UPPER(p_city_code) );
WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', sql%rowcount);
fnd_file.put_line(fnd_file.log, 'INSERT_GLOBAL_TABLE EXCEPTION : ' || sqlerrm);
END Insert_Global_Table;
PROCEDURE Update_Global_Table (
p_country IN VARCHAR2,
p_state IN VARCHAR2,
p_city IN VARCHAR2,
p_country_code IN VARCHAR2,
p_state_code IN VARCHAR2,
p_city_code IN VARCHAR2,
p_region_id IN NUMBER ,
p_postal_code_from IN VARCHAR2,
p_postal_code_to IN VARCHAR2,
p_parent_zone_level IN NUMBER,
p_lang_code IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2 )
AS
CURSOR child_regions IS
SELECT region_id, region_type, parent_region_id
FROM wsh_regions_global_data
START WITH region_id = p_region_id
CONNECT BY PRIOR region_id = parent_region_id;
SELECT state_code, city_code
FROM wsh_regions
WHERE region_id = l_region_id;
l_update_state_code WSH_REGIONS.State_Code%TYPE;
l_update_city_code WSH_REGIONS.City_Code%TYPE;
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'UPDATE_GLOBAL_TABLE';
FETCH get_state_code INTO l_update_state_code, l_update_city_code;
UPDATE wsh_regions_global
SET COUNTRY_CODE = nvl(UPPER(p_country_code), COUNTRY_CODE),
state_code = decode(i.region_type,
2, UPPER(l_update_state_code),
3, UPPER(l_update_state_code),
decode(p_parent_zone_level,
1, UPPER(p_state_code),
nvl(UPPER(p_state_code), state_code))),
city_code = decode(p_parent_zone_level,
2, UPPER(p_city_code),
nvl(UPPER(p_city_code), city_code))
WHERE REGION_ID = i.region_id;
INSERT INTO wsh_regions_global
( REGION_ID,
REGION_TYPE,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE )
SELECT REGION_ID,
REGION_TYPE,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE
FROM WSH_REGIONS
WHERE REGION_ID = i.region_id;
UPDATE wsh_regions_global_data
SET country = nvl(UPPER(p_country), country),
state = nvl(UPPER(p_state), state),
city = nvl(UPPER(p_city), city),
country_code = nvl(UPPER(p_country_code), country_code),
state_code = decode(i.region_type,
2, UPPER(l_update_state_code),
3, UPPER(l_update_state_code),
decode(p_parent_zone_level,
1, UPPER(p_state_code),
nvl(UPPER(p_state_code), state_code))),
city_code = decode(p_parent_zone_level,
2, UPPER(p_city_code),
nvl(UPPER(p_city_code), city_code)),
postal_code_from = nvl(p_postal_code_from, postal_code_from),
postal_code_to = nvl(p_postal_code_to, postal_code_to)
WHERE region_id = i.region_id
AND language = p_lang_code;
INSERT INTO wsh_regions_global_data
( REGION_ID,
REGION_TYPE,
PARENT_REGION_ID,
COUNTRY,
STATE,
CITY,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
LANGUAGE )
SELECT R.REGION_ID,
R.REGION_TYPE,
R.PARENT_REGION_ID,
TL.COUNTRY,
TL.STATE,
TL.CITY,
R.COUNTRY_CODE,
R.STATE_CODE,
R.CITY_CODE,
TL.POSTAL_CODE_FROM,
TL.POSTAL_CODE_TO,
TL.LANGUAGE
FROM WSH_REGIONS R,
WSH_REGIONS_TL TL
WHERE TL.LANGUAGE = p_lang_code
AND TL.REGION_ID = R.REGION_ID
AND R.REGION_ID = i.region_id;
WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global', l_region_upd_cnt);
WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global', l_region_ins_cnt);
WSH_DEBUG_SV.log(l_module_name, 'No of rows updated in wsh_regions_global_data', l_region_data_upd_cnt);
WSH_DEBUG_SV.log(l_module_name, 'No of rows inserted in wsh_regions_global_data', l_region_data_ins_cnt);
fnd_file.put_line(fnd_file.log, 'UPDATE_GLOBAL_TABLE EXCEPTION : ' || sqlerrm);
END Update_Global_Table;