The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW (
X_ROWID in out nocopy VARCHAR2,
X_BUSINESS_EVENT_TYPE_ID in NUMBER,
X_ACTION_ID in NUMBER,
X_ACTION_ITEM_ID in NUMBER,
X_ENABLED_FLAG in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_MEANING 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 AML_BUSINESS_EVENT_TYPES_B
where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID
;
insert into AML_BUSINESS_EVENT_TYPES_B (
BUSINESS_EVENT_TYPE_ID,
ACTION_ID,
ACTION_ITEM_ID,
ENABLED_FLAG,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) values (
X_BUSINESS_EVENT_TYPE_ID,
X_ACTION_ID,
X_ACTION_ITEM_ID,
X_ENABLED_FLAG,
X_OBJECT_VERSION_NUMBER,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN
);
insert into AML_BUSINESS_EVENT_TYPES_TL (
BUSINESS_EVENT_TYPE_ID,
MEANING,
DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
X_BUSINESS_EVENT_TYPE_ID,
X_MEANING,
X_DESCRIPTION,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
userenv('LANG')
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
(select NULL
from AML_BUSINESS_EVENT_TYPES_TL T
where T.BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_ROW;
cursor c is select
ACTION_ID,
ACTION_ITEM_ID,
ENABLED_FLAG,
OBJECT_VERSION_NUMBER
from AML_BUSINESS_EVENT_TYPES_B
where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID
for update of BUSINESS_EVENT_TYPE_ID nowait;
cursor c1 is select
MEANING,
DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from AML_BUSINESS_EVENT_TYPES_TL
where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of BUSINESS_EVENT_TYPE_ID nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_BUSINESS_EVENT_TYPE_ID in NUMBER,
X_ACTION_ID in NUMBER,
X_ACTION_ITEM_ID in NUMBER,
X_ENABLED_FLAG in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_MEANING 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 AML_BUSINESS_EVENT_TYPES_B set
ACTION_ID = X_ACTION_ID,
ACTION_ITEM_ID = X_ACTION_ITEM_ID,
ENABLED_FLAG = X_ENABLED_FLAG,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID;
update AML_BUSINESS_EVENT_TYPES_TL set
MEANING = X_MEANING,
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 BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end UPDATE_ROW;
procedure DELETE_ROW (
X_BUSINESS_EVENT_TYPE_ID in NUMBER
) is
begin
delete from AML_BUSINESS_EVENT_TYPES_TL
where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID;
delete from AML_BUSINESS_EVENT_TYPES_B
where BUSINESS_EVENT_TYPE_ID = X_BUSINESS_EVENT_TYPE_ID;
end DELETE_ROW;
delete from AML_BUSINESS_EVENT_TYPES_TL T
where not exists
(select NULL
from AML_BUSINESS_EVENT_TYPES_B B
where B.BUSINESS_EVENT_TYPE_ID = T.BUSINESS_EVENT_TYPE_ID
);
update AML_BUSINESS_EVENT_TYPES_TL T set (
MEANING,
DESCRIPTION
) = (select
B.MEANING,
B.DESCRIPTION
from AML_BUSINESS_EVENT_TYPES_TL B
where B.BUSINESS_EVENT_TYPE_ID = T.BUSINESS_EVENT_TYPE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.BUSINESS_EVENT_TYPE_ID,
T.LANGUAGE
) in (select
SUBT.BUSINESS_EVENT_TYPE_ID,
SUBT.LANGUAGE
from AML_BUSINESS_EVENT_TYPES_TL SUBB, AML_BUSINESS_EVENT_TYPES_TL SUBT
where SUBB.BUSINESS_EVENT_TYPE_ID = SUBT.BUSINESS_EVENT_TYPE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.MEANING <> SUBT.MEANING
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 AML_BUSINESS_EVENT_TYPES_TL (
BUSINESS_EVENT_TYPE_ID,
MEANING,
DESCRIPTION,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select /*+ ORDERED */
B.BUSINESS_EVENT_TYPE_ID,
B.MEANING,
B.DESCRIPTION,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from AML_BUSINESS_EVENT_TYPES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from AML_BUSINESS_EVENT_TYPES_TL T
where T.BUSINESS_EVENT_TYPE_ID = B.BUSINESS_EVENT_TYPE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
CURSOR c_get_last_updated (c_BUSINESS_EVENT_TYPE_ID NUMBER) IS
SELECT last_updated_by, OBJECT_VERSION_NUMBER
FROM AML_BUSINESS_EVENT_TYPES_B
WHERE BUSINESS_EVENT_TYPE_ID = c_BUSINESS_EVENT_TYPE_ID;
l_last_updated_by NUMBER;
OPEN c_get_last_updated (px_BUSINESS_EVENT_TYPE_ID);
FETCH c_get_last_updated INTO l_last_updated_by, l_object_version_number;
CLOSE c_get_last_updated;
IF nvl(l_last_updated_by, 1) = 1
THEN
if (p_OWNER = 'SEED') then
l_user_id := 1;
Update_Row(x_BUSINESS_EVENT_TYPE_ID => px_BUSINESS_EVENT_TYPE_ID,
x_ACTION_ID => p_ACTION_ID,
x_ACTION_ITEM_ID => p_ACTION_ITEM_ID,
x_ENABLED_FLAG => p_ENABLED_FLAG,
x_OBJECT_VERSION_NUMBER => l_object_version_number,
x_MEANING => p_MEANING,
x_DESCRIPTION => p_DESCRIPTION,
x_LAST_UPDATE_DATE => sysdate,
x_LAST_UPDATED_BY => l_user_id,
x_LAST_UPDATE_LOGIN => 0
);
Insert_Row(x_ROWID => l_row_id,
x_BUSINESS_EVENT_TYPE_ID => px_BUSINESS_EVENT_TYPE_ID,
x_ACTION_ID => p_ACTION_ID,
x_ACTION_ITEM_ID => p_ACTION_ITEM_ID,
x_ENABLED_FLAG => p_ENABLED_FLAG,
x_OBJECT_VERSION_NUMBER => 1,
x_MEANING => p_MEANING,
x_DESCRIPTION => p_DESCRIPTION,
x_CREATION_DATE => sysdate,
x_CREATED_BY => 0,
x_LAST_UPDATE_DATE => sysdate,
x_LAST_UPDATED_BY => l_user_id,
x_LAST_UPDATE_LOGIN => 0
);
UPDATE AML_BUSINESS_EVENT_TYPES_TL
SET
MEANING = NVL(p_MEANING, MEANING),
DESCRIPTION = NVL(p_DESCRIPTION, DESCRIPTION),
SOURCE_LANG = userenv('LANG'),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = decode(p_owner, 'SEED', 1, 0),
LAST_UPDATE_LOGIN = 0
WHERE BUSINESS_EVENT_TYPE_ID = p_BUSINESS_EVENT_TYPE_ID
AND userenv('LANG') IN (language, source_lang);