The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_operation_type VARCHAR2(10) := 'INSERT';
SELECT jtf_dsp_context_b_s1.NEXTVAL
FROM DUAL;
l_operation_type:='UPDATE';
IF l_operation_type = 'INSERT'
THEN
---dbms_output.put_line('INSERT OPERATIOn ');
IF l_operation_type = 'INSERT'
THEN
INSERT INTO JTF_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
)
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);
insert into JTF_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 JTF_DSP_CONTEXT_TL T
where T.CONTEXT_ID =l_context_id
and T.LANGUAGE = L.LANGUAGE_CODE);
ELSIF l_operation_type = 'UPDATE'
THEN
UPDATE JTF_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 ,
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 JTF_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 VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2,
p_display_context_rec IN OUT DISPLAY_CONTEXT_REC_TYPE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_display_context';
SAVEPOINT delete_display_context;
DELETE FROM JTF_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 JTF_DSP_CONTEXT_TL WHERE
CONTEXT_ID = p_display_context_rec.context_id;
JTF_LogicalContent_GRP.delete_context(p_display_context_rec.context_id);
JTF_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 VARCHAR2,
x_msg_count OUT NUMBER,
x_msg_data OUT VARCHAR2,
p_display_context_tbl IN OUT 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 JTF_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 VARCHAR2,
X_CONTEXT_ID in NUMBER,
X_SECURITY_GROUP_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) IS
cursor C is select ROWID from JTF_DSP_CONTEXT_B
where CONTEXT_ID = X_CONTEXT_ID
;
insert into JTF_DSP_CONTEXT_B (
SECURITY_GROUP_ID,
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
) values (
X_SECURITY_GROUP_ID,
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
);
insert into JTF_DSP_CONTEXT_TL (
SECURITY_GROUP_ID,
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_SECURITY_GROUP_ID,
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 JTF_DSP_CONTEXT_TL T
where T.CONTEXT_ID = X_CONTEXT_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_ROW;
/* ---- End INSERT_ROW Procedure ----- */
/* ---- Start LOCK_ROW Procedue ------ */
procedure LOCK_ROW (
X_CONTEXT_ID in NUMBER,
X_SECURITY_GROUP_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
) IS
cursor c is select
SECURITY_GROUP_ID,
OBJECT_VERSION_NUMBER,
ACCESS_NAME,
CONTEXT_TYPE_CODE,
ITEM_ID
from JTF_DSP_CONTEXT_B
where CONTEXT_ID = X_CONTEXT_ID
for update of CONTEXT_ID nowait;
cursor c1 is select
NAME,
DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from JTF_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');
/* ------- start UPDATE_ROW Procedure ------- */
procedure UPDATE_ROW (
X_CONTEXT_ID in NUMBER,
X_SECURITY_GROUP_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
) IS
begin
update JTF_DSP_CONTEXT_B set
SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
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
where CONTEXT_ID = X_CONTEXT_ID;
update JTF_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;
/* ---- End UPDATE_ROW Procedure ---------- */
/* ---- Start DELETE_ROW Procedure --------- */
procedure DELETE_ROW (
X_CONTEXT_ID in NUMBER
) is
begin
delete from JTF_DSP_CONTEXT_TL
where CONTEXT_ID = X_CONTEXT_ID;
delete from JTF_DSP_CONTEXT_B
where CONTEXT_ID = X_CONTEXT_ID;
end DELETE_ROW;
/* --- End DELETE_ROW Procedure ---- */
/* -- Start TRANSLATE_ROW Procedure ---- */
procedure TRANSLATE_ROW (
X_CONTEXT_ID in NUMBER,
X_OWNER in VARCHAR2,
X_NAME in VARCHAR2,
X_DESCRIPTION in VARCHAR2 ) is
begin
update jtf_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 = sysdate,
last_update_login=0
Where userenv('LANG') in (language,source_lang)
and context_id = X_CONTEXT_ID;
UPDATE_ROW (
X_CONTEXT_ID => X_CONTEXT_ID,
X_SECURITY_GROUP_ID => X_SECURITY_GROUP_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 => sysdate,
X_LAST_UPDATED_BY => Owner_id,
X_LAST_UPDATE_LOGIN => 0);
INSERT_ROW(
X_ROWID => Row_id,
X_CONTEXT_ID => X_CONTEXT_ID,
X_SECURITY_GROUP_ID => X_SECURITY_GROUP_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 => sysdate,
X_CREATED_BY => Owner_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => Owner_id,
X_LAST_UPDATE_LOGIN => 0);
delete from JTF_DSP_CONTEXT_TL T
where not exists
(select NULL
from JTF_DSP_CONTEXT_B B
where B.CONTEXT_ID = T.CONTEXT_ID
);
update JTF_DSP_CONTEXT_TL T set (
NAME,
DESCRIPTION
) = (select
B.NAME,
B.DESCRIPTION
from JTF_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 JTF_DSP_CONTEXT_TL SUBB, JTF_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 JTF_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 JTF_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 JTF_DSP_CONTEXT_TL T
where T.CONTEXT_ID = B.CONTEXT_ID
and T.LANGUAGE = L.LANGUAGE_CODE);