[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT WSH_REGIONS_S.NEXTVAL FROM DUAL;
SELECT region_id
FROM wsh_regions_tl
WHERE zone = p_zone_name
--BUG 6067174 : Zone ID returned should be specific to the instance language.
and language = userenv('lang');
INSERT INTO WSH_REGIONS(
REGION_ID,
PARENT_REGION_ID,
REGION_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES(
l_zone_id,
-1,
p_region_type,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID);
INSERT INTO WSH_REGIONS_TL(
REGION_ID,
LANGUAGE,
ZONE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY)
VALUES(
l_zone_id,
USERENV('LANG'),
p_zone_name,
SYSDATE,
G_USER_ID,
SYSDATE,
G_USER_ID);
FUNCTION INSERT_PARTY_REGION(p_region_id IN NUMBER,
p_parent_region_id IN NUMBER,
p_supplier_id IN NUMBER,
p_validate_flag IN BOOLEAN,
p_postal_code_from IN NUMBER,
p_postal_code_to IN NUMBER)
RETURN NUMBER IS
l_result NUMBER := -1;
SELECT
zone_region_id
FROM
wsh_zone_regions
WHERE
region_id = p_region_id AND
parent_region_id = p_parent_region_id AND
party_id = p_supplier_id AND
(p_postal_code_from IS NULL OR postal_code_from = p_postal_code_from) AND
(p_postal_code_to IS NULL OR postal_code_to = p_postal_code_to) ;
SELECT WSH_ZONE_REGIONS_S.NEXTVAL FROM DUAL;
l_module_name CONSTANT VARCHAR2(100) := 'FTE.PLSQL.' || G_PKG_NAME || '.INSERT_PARTY_REGION';
INSERT INTO WSH_ZONE_REGIONS(
ZONE_REGION_ID,
REGION_ID,
PARENT_REGION_ID,
PARTY_ID,
ZONE_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
POSTAL_CODE_FROM,
POSTAL_CODE_TO)
VALUES(
l_party_region_id,
p_region_id,
p_parent_region_id,
p_supplier_id,
l_zone_flag,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
l_postal_code_from,
l_postal_code_to);
END INSERT_PARTY_REGION;
WSH_REGIONS_PKG.UPDATE_ZONE_REGION(p_insert_type => 'INSERT',
p_zone_region_id => l_region_id,
p_zone_id => l_zone_id,
p_region_id => l_region_id,
p_country => l_region_rec.country,
p_state => l_region_rec.state,
p_city => l_region_rec.city,
p_postal_code_from => l_region_rec.postal_code_from,
p_postal_code_to => l_region_rec.postal_code_to,
p_lang_code => USERENV('LANG'),
p_country_code => '',
p_state_code => '',
p_city_code => '',
p_user_id => -1,
p_zone_type => p_region_type,
x_zone_region_id => l_temp_id,
x_region_id => l_region_id,
x_status => x_status,
x_error_msg => x_error_msg);
FTE_UTIL_PKG.Write_LogFile(l_module_name,'WSH_REGIONS_PKG.UPDATE_ZONE_REGION returned with error',x_error_msg);
SELECT WSH_REGIONS_INTERFACE_S.NEXTVAL
FROM DUAL;
INSERT INTO WSH_REGIONS_INTERFACE(
REGION_ID,
PARENT_REGION_ID,
COUNTRY_CODE,
STATE_CODE,
CITY_CODE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROCESSED_FLAG)
VALUES(
L_REGION_ID(i),
-1,
L_COUNTRY_CODE(i),
L_STATE_CODE(i),
L_CITY_CODE(i),
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
NULL);
INSERT INTO WSH_REGIONS_TL_INTERFACE(
LANGUAGE,
REGION_ID,
COUNTRY,
STATE,
CITY,
POSTAL_CODE_FROM,
POSTAL_CODE_TO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES(
L_LANGUAGE(i),
L_REGION_ID(i),
L_COUNTRY(i),
L_STATE(i),
L_CITY(i),
L_POSTAL_CODE_FROM(i),
L_POSTAL_CODE_TO(i),
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID);