The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_operation_type VARCHAR2(10) := 'INSERT';
SELECT ibe_dsp_context_b_s1.NEXTVAL
FROM DUAL;
l_operation_type:='UPDATE';
IF l_operation_type = 'INSERT'
THEN
OPEN context_id_seq;
IF l_operation_type = 'INSERT'
THEN
INSERT INTO IBE_DSP_CONTEXT_B (
CONTEXT_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ACCESS_NAME,
CONTEXT_TYPE_CODE,
ITEM_ID,
COMPONENT_TYPE_CODE )
VALUES (
l_context_id,
1,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id,
p_display_context_rec.access_name,
p_display_context_rec.context_type,
l_deliverable_id,
p_display_context_rec.component_type_code);
INSERT INTO IBE_DSP_CONTEXT_TL (
CONTEXT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG )
SELECT l_context_id,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id,
1,
p_display_context_rec.display_name,
p_display_context_rec.description,
L.LANGUAGE_CODE,
USERENV('LANG')
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND NOT EXISTS (SELECT NULL
FROM IBE_DSP_CONTEXT_TL T
WHERE T.CONTEXT_ID = l_context_id
AND T.language = L.LANGUAGE_CODE);
ELSIF l_operation_type = 'UPDATE'
THEN
UPDATE IBE_DSP_CONTEXT_B
SET LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_login = FND_GLOBAL.user_id,
ACCESS_NAME = p_display_context_rec.access_name,
CONTEXT_TYPE_CODE = p_display_context_rec.context_type,
ITEM_ID = l_deliverable_id ,
COMPONENT_TYPE_CODE = p_display_context_rec.component_type_code,
OBJECT_VERSION_NUMBER =
p_display_context_rec.object_version_number + 1
WHERE CONTEXT_ID = p_display_context_rec.context_id
AND OBJECT_VERSION_NUMBER =
p_display_context_rec.object_version_number;
UPDATE IBE_DSP_CONTEXT_TL
SET NAME = DECODE( p_display_context_rec.display_name,
FND_API.G_MISS_CHAR, NAME,
p_display_context_rec.display_name),
DESCRIPTION = decode( p_display_context_rec.description,
FND_API.G_MISS_CHAR, DESCRIPTION,
p_display_context_rec.description),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.user_id,
LAST_UPDATE_LOGIN = FND_GLOBAL.user_id,
OBJECT_VERSION_NUMBER =
p_display_context_rec.object_version_number +1 ,
SOURCE_LANG = USERENV('LANG')
WHERE CONTEXT_id = p_display_context_rec.context_id
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
PROCEDURE delete_display_context(
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_display_context_rec IN OUT NOCOPY DISPLAY_CONTEXT_REC_TYPE )
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_display_context';
SAVEPOINT delete_display_context;
DELETE FROM IBE_DSP_CONTEXT_B
WHERE CONTEXT_ID = p_display_context_rec.context_id
AND CONTEXT_TYPE_code = p_display_context_rec.context_type
AND OBJECT_VERSION_NUMBER= p_display_context_rec.object_version_number;
DELETE FROM IBE_DSP_CONTEXT_TL
WHERE CONTEXT_ID = p_display_context_rec.context_id;
IBE_LogicalContent_GRP.delete_context(p_display_context_rec.context_id);
IBE_DSP_SECTION_GRP.Update_Dsp_Context_To_Null(
p_api_version,
FND_API.g_false,
FND_API.g_false,
FND_API.G_VALID_LEVEL_FULL,
p_display_context_rec.context_id,
x_return_status,
x_msg_count,
x_msg_data );
ROLLBACK TO delete_display_context;
ROLLBACK TO delete_display_context;
ROLLBACK TO delete_display_context;
END delete_display_context;
PROCEDURE save_delete_display_context(
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_display_context_tbl IN OUT NOCOPY DISPLAY_CONTEXT_TBL_TYPE )
IS
l_api_name CONSTANT VARCHAR2(30) := 'save_delete_display_context';
SAVEPOINT save_delete_display_context;
IF p_display_context_tbl(l_index).context_delete = FND_API.g_true
THEN
delete_display_context(
p_api_version,
FND_API.g_false,
FND_API.g_false,
l_return_status,
l_msg_count,
l_msg_data,
p_display_context_tbl(l_index));
ELSIF p_display_context_tbl(l_index).context_delete = FND_API.g_false
THEN
save_display_context(
p_api_version,
FND_API.g_false,
FND_API.g_false,
l_return_status,
l_msg_count,
l_msg_data,
p_display_context_tbl(l_index));
ROLLBACK TO save_delete_display_context;
ROLLBACK TO save_delete_display_context;
END save_delete_display_context;
PROCEDURE delete_deliverable(p_deliverable_id IN NUMBER )
IS
BEGIN
SAVEPOINT delete_deliverable;
UPDATE IBE_DSP_CONTEXT_B
SET item_id = NULL
WHERE item_id = p_deliverable_id;
ROLLBACK TO delete_deliverable;
END delete_deliverable;
/* ------ Begin Insert_row ------------- */
PROCEDURE INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_CONTEXT_ID in NUMBER,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_ACCESS_NAME in VARCHAR2,
X_CONTEXT_TYPE_CODE in VARCHAR2,
X_ITEM_ID in NUMBER,
X_NAME in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_COMPONENT_TYPE_CODE in VARCHAR2)
IS
CURSOR C IS
SELECT ROWID
FROM IBE_DSP_CONTEXT_B
WHERE CONTEXT_ID = X_CONTEXT_ID;
INSERT INTO IBE_DSP_CONTEXT_B (
CONTEXT_ID,
OBJECT_VERSION_NUMBER,
ACCESS_NAME,
CONTEXT_TYPE_CODE,
ITEM_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
COMPONENT_TYPE_CODE)
VALUES (
X_CONTEXT_ID,
X_OBJECT_VERSION_NUMBER,
X_ACCESS_NAME,
X_CONTEXT_TYPE_CODE,
X_ITEM_ID,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_COMPONENT_TYPE_CODE);
INSERT INTO IBE_DSP_CONTEXT_TL (
CONTEXT_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG)
SELECT
X_CONTEXT_ID,
X_OBJECT_VERSION_NUMBER,
X_CREATED_BY,
X_CREATION_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATE_LOGIN,
X_NAME,
X_DESCRIPTION,
L.LANGUAGE_CODE,
USERENV('LANG')
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B')
AND NOT EXISTS(
SELECT NULL
FROM IBE_DSP_CONTEXT_TL T
WHERE T.CONTEXT_ID = X_CONTEXT_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);
END INSERT_ROW;
SELECT
OBJECT_VERSION_NUMBER,
ACCESS_NAME,
CONTEXT_TYPE_CODE,
ITEM_ID
FROM IBE_DSP_CONTEXT_B
WHERE CONTEXT_ID = X_CONTEXT_ID
FOR UPDATE OF CONTEXT_ID NOWAIT;
SELECT NAME,
DESCRIPTION,
DECODE(LANGUAGE, userenv('LANG'), 'Y', 'N') baselang
FROM IBE_DSP_CONTEXT_TL
WHERE CONTEXT_ID = X_CONTEXT_ID
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG)
FOR UPDATE OF CONTEXT_ID NOWAIT;
FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_row (
x_context_id in number,
x_object_version_number in number,
x_access_name in varchar2,
x_context_type_code in varchar2,
x_item_id in number,
x_name in varchar2,
x_description in varchar2,
x_last_update_date in date,
x_last_updated_by in number,
x_last_update_login in number,
x_component_type_code in VARCHAR2)
IS
BEGIN
UPDATE IBE_DSP_CONTEXT_B
SET OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
ACCESS_NAME = X_ACCESS_NAME,
CONTEXT_TYPE_CODE = X_CONTEXT_TYPE_CODE,
ITEM_ID = X_ITEM_ID,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
COMPONENT_TYPE_CODE = x_component_type_code
WHERE CONTEXT_ID = X_CONTEXT_ID;
UPDATE IBE_DSP_CONTEXT_TL
SET NAME = X_NAME,
DESCRIPTION = X_DESCRIPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
WHERE CONTEXT_ID = X_CONTEXT_ID
AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
END UPDATE_ROW;
PROCEDURE DELETE_ROW (X_CONTEXT_ID IN NUMBER )
IS
BEGIN
DELETE FROM IBE_DSP_CONTEXT_TL
WHERE CONTEXT_ID = X_CONTEXT_ID;
DELETE FROM IBE_DSP_CONTEXT_B
WHERE CONTEXT_ID = X_CONTEXT_ID;
END DELETE_ROW;
X_LAST_UPDATE_DATE in VARCHAR2,
X_CUSTOM_MODE in VARCHAR2 )
IS
f_luby number; -- entity owner in file
f_ludate date; -- entity update date in file
f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
UPDATE ibe_dsp_context_tl
SET language = USERENV('LANG'),
source_lang = USERENV('LANG'),
name = X_NAME,
description = X_DESCRIPTION,
last_updated_by = decode(X_OWNER,'SEED',1,0),
last_update_date = f_ludate,
last_update_login= f_luby
WHERE USERENV('LANG') IN (language,source_lang)
AND context_id = X_CONTEXT_ID;
X_LAST_UPDATE_DATE in VARCHAR2,
X_CUSTOM_MODE in VARCHAR2,
X_COMPONENT_TYPE_CODE in VARCHAR2)
IS
Owner_id NUMBER := 0;
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
SELECT context_id
FROM ibe_dsp_context_b
WHERE access_name = c_access_name
AND context_type_code = c_context_type_code;
f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from IBE_DSP_CONTEXT_B
where CONTEXT_ID = X_CONTEXT_ID;
UPDATE_ROW (
X_CONTEXT_ID => X_CONTEXT_ID,
X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
X_ACCESS_NAME => X_ACCESS_NAME,
X_CONTEXT_TYPE_CODE => X_CONTEXT_TYPE_CODE,
X_ITEM_ID => X_ITEM_ID,
X_NAME => X_NAME,
X_DESCRIPTION => X_DESCRIPTION,
X_LAST_UPDATE_DATE => f_ludate,
X_LAST_UPDATED_BY => f_luby,
X_LAST_UPDATE_LOGIN => f_luby,
X_COMPONENT_TYPE_CODE => X_COMPONENT_TYPE_CODE);
INSERT_ROW(
X_ROWID => Row_id,
X_CONTEXT_ID => X_CONTEXT_ID,
X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
X_ACCESS_NAME => X_ACCESS_NAME,
X_CONTEXT_TYPE_CODE => X_CONTEXT_TYPE_CODE,
X_ITEM_ID => X_ITEM_ID,
X_NAME => X_NAME,
X_DESCRIPTION => X_DESCRIPTION,
X_CREATION_DATE => f_ludate,
X_CREATED_BY => f_luby,
X_LAST_UPDATE_DATE => f_ludate,
X_LAST_UPDATED_BY => f_luby,
X_LAST_UPDATE_LOGIN => f_luby,
X_COMPONENT_TYPE_CODE => X_COMPONENT_TYPE_CODE);
DELETE FROM IBE_DSP_CONTEXT_TL T
WHERE NOT EXISTS(
SELECT NULL
FROM IBE_DSP_CONTEXT_B B
WHERE B.CONTEXT_ID = T.CONTEXT_ID );
UPDATE IBE_DSP_CONTEXT_TL T
SET (NAME,DESCRIPTION) =
(SELECT B.NAME,
B.DESCRIPTION
FROM IBE_DSP_CONTEXT_TL B
WHERE B.CONTEXT_ID = T.CONTEXT_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (T.CONTEXT_ID,T.LANGUAGE) IN
(SELECT SUBT.CONTEXT_ID,
SUBT.LANGUAGE
FROM IBE_DSP_CONTEXT_TL SUBB,
IBE_DSP_CONTEXT_TL SUBT
WHERE SUBB.CONTEXT_ID = SUBT.CONTEXT_ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.NAME <> SUBT.NAME OR
(SUBB.NAME IS NULL AND SUBT.NAME IS NOT NULL)
OR (SUBB.NAME IS NOT NULL AND SUBT.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 IBE_DSP_CONTEXT_TL (
CONTEXT_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG )
SELECT
B.CONTEXT_ID,
B.OBJECT_VERSION_NUMBER,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.NAME,
B.DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG
FROM
IBE_DSP_CONTEXT_TL B,
FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS
(SELECT NULL
FROM IBE_DSP_CONTEXT_TL T
WHERE T.CONTEXT_ID = B.CONTEXT_ID
AND T.LANGUAGE = L.LANGUAGE_CODE);
X_LAST_UPDATE_DATE in VARCHAR2,
X_CUSTOM_MODE in VARCHAR2,
X_UPLOAD_MODE in VARCHAR2)
is
Begin
if ( x_upload_mode = 'NLS') then
IBE_DisplayContext_GRP.TRANSLATE_ROW(
to_number(X_CONTEXT_ID),
X_OWNER,
X_NAME,
X_DESCRIPTION,
X_LAST_UPDATE_DATE,
X_CUSTOM_MODE );
X_LAST_UPDATE_DATE,
X_CUSTOM_MODE,
X_COMPONENT_TYPE_CODE );