The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Insert_Row Procedure */
/*----------------------------------------------------------*/
/* --
-- Purpose
-- Insert a row into WSH_ITM_PARAMETER_SETUPS_B entity
-- Insert a row into WSH_ITM_PARAMETER_SETUPS_TL entity
-- Input Parameters
-- p_api_version
-- API version number (current version is 1.0)
-- p_init_msg_list (optional, default FND_API.G_FALSE)
-- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
-- if set to FND_API.G_TRUE
-- initialize error message list
-- if set to FND_API.G_FALSE - not initialize error
-- message list
-- p_commit (optional, default FND_API.G_FALSE)
-- whether or not to commit the changes to database
--
-- Input parameters
-- P_PARAMETER_ID Unique sequence generated parameter ID
-- P_PARAMETER_NAME Parameter Name (Internally identified)
-- P_VALUE User Defined Value for the Parameter.
-- P_DEFAULT_VALUE System defined Seeded Value for the Parameter.
-- P_USER_SETTABLE User can Override the default.
-- P_USER_PARAMETER_NAME User Parameter name
-- P_DESCRIPTION Brief Description of the Parameter.
--
-- Output Parameters
-- x_return_status
-- if the process succeeds, the value is
-- fnd_api.g_ret_sts_success;
procedure INSERT_ROW (
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
p_commit IN VARCHAR2 := fnd_api.g_false ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
P_PARAMETER_ID OUT NOCOPY NUMBER ,
P_PARAMETER_NAME IN VARCHAR2 ,
P_VALUE IN VARCHAR2 ,
P_DEFAULT_VALUE IN VARCHAR2 ,
P_USER_SETTABLE IN VARCHAR2 ,
P_USER_PARAMETER_NAME IN VARCHAR2 ,
P_DESCRIPTION IN VARCHAR2
) is
l_parameter_id NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
SELECT WSH_ITM_PARAMETER_SETUPS_S.NEXTVAL into l_parameter_id FROM dual;
insert into WSH_ITM_PARAMETER_SETUPS_B (
PARAMETER_ID,
PARAMETER_NAME,
VALUE,
DEFAULT_VALUE,
USER_SETTABLE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values (
l_PARAMETER_ID,
P_PARAMETER_NAME,
P_VALUE,
P_DEFAULT_VALUE,
P_USER_SETTABLE,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id
);
FND_MESSAGE.SET_NAME('WSH', 'WSH_INSERT_FAILED');
insert into WSH_ITM_PARAMETER_SETUPS_TL (
PARAMETER_ID,
USER_PARAMETER_NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
l_PARAMETER_ID,
P_USER_PARAMETER_NAME,
P_DESCRIPTION,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
FND_GLOBAL.login_id,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from WSH_ITM_PARAMETER_SETUPS_TL T
where T.PARAMETER_ID = l_PARAMETER_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
FND_MESSAGE.SET_NAME('WSH', 'WSH_INSERT_FAILED');
end INSERT_ROW;
cursor c is select
PARAMETER_NAME,
VALUE,
DEFAULT_VALUE,
USER_SETTABLE
from WSH_ITM_PARAMETER_SETUPS_B
where PARAMETER_ID = P_PARAMETER_ID
for update of PARAMETER_ID nowait;
cursor c1 is select
USER_PARAMETER_NAME,
DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from WSH_ITM_PARAMETER_SETUPS_TL
where PARAMETER_ID = P_PARAMETER_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of PARAMETER_ID nowait;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
/* Update_Row Procedure */
/*----------------------------------------------------------*/
/* --
-- Purpose
-- Update a row into WSH_ITM_parameter_setups_b entity
-- Update a row into WSH_ITM_parameter_setups_tl entity
-- for the given parameter id
--
-- Input Parameters
-- p_api_version
-- API version number (current version is 1.0)
-- p_init_msg_list (optional, default FND_API.G_FALSE)
-- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
-- if set to FND_API.G_TRUE
-- initialize error message list
-- if set to FND_API.G_FALSE - not initialize error
-- message list
-- p_commit (optional, default FND_API.G_FALSE)
-- whether or not to commit the changes to database
--
-- Input parameters for clear cross parameter setups informations
-- P_PARAMETER_ID Unique sequence generated parameter ID
-- P_PARAMETER_NAME Parameter Name (Internally identified)
-- P_VALUE User Defined Value for the Parameter.
-- P_DEFAULT_VALUE System defined Seeded Value for the Parameter.
-- P_USER_SETTABLE User can Override the default.
-- P_USER_PARAMETER_NAME User Parameter name
-- P_DESCRIPTION Brief Description of the Parameter.
--
-- Output Parameters
-- x_return_status
-- if the process succeeds, the value is
-- fnd_api.g_ret_sts_success;
procedure UPDATE_ROW (
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
p_commit IN VARCHAR2 := fnd_api.g_false ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
P_PARAMETER_ID IN NUMBER ,
P_PARAMETER_NAME IN VARCHAR2 ,
P_VALUE IN VARCHAR2 ,
P_DEFAULT_VALUE IN VARCHAR2 ,
P_USER_SETTABLE IN VARCHAR2 ,
P_USER_PARAMETER_NAME IN VARCHAR2 ,
P_DESCRIPTION IN VARCHAR2
) is
l_api_name CONSTANT VARCHAR2(30) := 'Update_Row' ;
update WSH_ITM_PARAMETER_SETUPS_B set
PARAMETER_NAME = P_PARAMETER_NAME,
VALUE = P_VALUE,
DEFAULT_VALUE = P_DEFAULT_VALUE,
USER_SETTABLE = P_USER_SETTABLE,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.login_id
where PARAMETER_ID = P_PARAMETER_ID;
FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_FAILED');
update WSH_ITM_PARAMETER_SETUPS_TL set
USER_PARAMETER_NAME = P_USER_PARAMETER_NAME,
DESCRIPTION = P_DESCRIPTION,
SOURCE_LANG = userenv('LANG')
where PARAMETER_ID = P_PARAMETER_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
FND_MESSAGE.SET_NAME('WSH', 'WSH_UPDATE_FAILED');
end UPDATE_ROW;
/* Delete_Row Procedure */
/*----------------------------------------------------------*/
/* --
-- Purpose
-- Delete a row from WSH_ITM_PARAMETER_SETUPS_B entity
-- Delete a row from WSH_ITM_PARAMETER_SETUPS_TL entity
-- for the given parameter id
--
-- Input Parameters
-- p_api_version
-- API version number (current version is 1.0)
-- p_init_msg_list (optional, default FND_API.G_FALSE)
-- Valid values: FND_API.G_FALSE or FND_API.G_TRUE.
-- if set to FND_API.G_TRUE
-- initialize error message list
-- if set to FND_API.G_FALSE - not initialize error
-- message list
-- p_commit (optional, default FND_API.G_FALSE)
-- whether or not to commit the changes to database
--
-- Input parameters for clear cross parameters informations
-- p_PARAMETER_ID -- parameter id
--
--
-- Output Parameters
-- x_return_status
-- if the process succeeds, the value is
-- fnd_api.g_ret_sts_success;
procedure DELETE_ROW (
p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := fnd_api.g_false ,
p_commit IN VARCHAR2 := fnd_api.g_false ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
P_PARAMETER_ID IN NUMBER
) is
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row' ;
delete from WSH_ITM_PARAMETER_SETUPS_TL
where PARAMETER_ID = P_PARAMETER_ID;
FND_MESSAGE.SET_NAME('WSH', 'WSH_DELETE_FAILED');
delete from WSH_ITM_PARAMETER_SETUPS_B
where PARAMETER_ID = p_PARAMETER_ID;
FND_MESSAGE.SET_NAME('WSH', 'WSH_DELETE_FAILED');
end DELETE_ROW;
delete from WSH_ITM_PARAMETER_SETUPS_TL T
where not exists
(select NULL
from WSH_ITM_PARAMETER_SETUPS_B B
where B.PARAMETER_ID = T.PARAMETER_ID
);
update WSH_ITM_PARAMETER_SETUPS_TL T set (
USER_PARAMETER_NAME,
DESCRIPTION
) = (select
B.USER_PARAMETER_NAME,
B.DESCRIPTION
from WSH_ITM_PARAMETER_SETUPS_TL B
where B.PARAMETER_ID = T.PARAMETER_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.PARAMETER_ID,
T.LANGUAGE
) in (select
SUBT.PARAMETER_ID,
SUBT.LANGUAGE
from WSH_ITM_PARAMETER_SETUPS_TL SUBB, WSH_ITM_PARAMETER_SETUPS_TL SUBT
where SUBB.PARAMETER_ID = SUBT.PARAMETER_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.USER_PARAMETER_NAME <> SUBT.USER_PARAMETER_NAME
or (SUBB.USER_PARAMETER_NAME is null and SUBT.USER_PARAMETER_NAME is not null)
or (SUBB.USER_PARAMETER_NAME is not null and SUBT.USER_PARAMETER_NAME is null)
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into WSH_ITM_PARAMETER_SETUPS_TL (
PARAMETER_ID,
USER_PARAMETER_NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.PARAMETER_ID,
B.USER_PARAMETER_NAME,
B.DESCRIPTION,
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_ITM_PARAMETER_SETUPS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from WSH_ITM_PARAMETER_SETUPS_TL T
where T.PARAMETER_ID = B.PARAMETER_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
UPDATE WSH_ITM_parameter_setups_tl SET
user_parameter_name = x_user_parameter_name,
description = x_description,
last_update_date = sysdate,
last_updated_by = Decode(x_owner, 'SEED', 1, 0),
last_update_login = 0,
source_lang = userenv('LANG')
WHERE parameter_id = fnd_number.canonical_to_number(x_parameter_id)
AND userenv('LANG') IN (language, source_lang);
SELECT Sysdate INTO l_sysdate FROM dual;
update WSH_ITM_PARAMETER_SETUPS_B set
PARAMETER_NAME = x_PARAMETER_NAME,
VALUE = x_VALUE,
DEFAULT_VALUE = x_DEFAULT_VALUE,
USER_SETTABLE = x_USER_SETTABLE,
LAST_UPDATE_DATE = l_sysdate,
LAST_UPDATED_BY = l_user_id,
LAST_UPDATE_LOGIN = 0
where PARAMETER_ID = l_PARAMETER_ID;
update WSH_ITM_PARAMETER_SETUPS_TL set
USER_PARAMETER_NAME = x_USER_PARAMETER_NAME,
DESCRIPTION = x_DESCRIPTION,
SOURCE_LANG = userenv('LANG')
where PARAMETER_ID = l_PARAMETER_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
insert into WSH_ITM_PARAMETER_SETUPS_B (
PARAMETER_ID,
PARAMETER_NAME,
VALUE,
DEFAULT_VALUE,
USER_SETTABLE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
values (
l_PARAMETER_ID,
x_PARAMETER_NAME,
x_VALUE,
x_DEFAULT_VALUE,
x_USER_SETTABLE,
l_sysdate,
l_user_id,
l_sysdate,
l_user_id,
0
);
insert into WSH_ITM_PARAMETER_SETUPS_TL (
PARAMETER_ID,
USER_PARAMETER_NAME,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
l_PARAMETER_ID,
x_USER_PARAMETER_NAME,
x_DESCRIPTION,
l_sysdate,
l_user_id,
l_sysdate,
l_user_id,
0,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from WSH_ITM_PARAMETER_SETUPS_TL T
where T.PARAMETER_ID = l_PARAMETER_ID
and T.LANGUAGE = L.LANGUAGE_CODE);