The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW (
X_ROWID in out nocopy VARCHAR2,
X_AMOUNT_ITEM_FLAG in VARCHAR2,
X_ASSIGNMENT_ENABLED_FLAG in VARCHAR2,
X_DATA_SOURCE_ID in NUMBER,
X_DISPLAY_ENABLED_FLAG in VARCHAR2,
X_DISPLAY_LEVEL in VARCHAR2,
X_DISPLAY_PROMPT in VARCHAR2,
X_ITEM_CODE in VARCHAR2,
X_ITEM_DESCRIPTION in VARCHAR2,
X_ITEM_ID in NUMBER,
X_ITEM_IMAGE_FILENAME in VARCHAR2,
X_ITEM_MESSAGE_NAME in VARCHAR2,
X_ITEM_NAME in VARCHAR2,
X_ITEM_SOURCE in VARCHAR2,
X_ITEM_TEXT_VALUE in VARCHAR2,
X_ITEM_TYPE in VARCHAR2,
X_URL_ID in NUMBER,
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_DISPLAYED_MULTI_LEVEL_FLAG in VARCHAR2,
X_SEEDED_APPLICATION_ID in NUMBER,
X_TAX_ITEM_FLAG in VARCHAR2,
X_TOTALS_ENABLED_FLAG in VARCHAR2,
X_LINK_ENABLED_FLAG in VARCHAR2,
X_DATA_TYPE in VARCHAR2,
X_URLCFG_ENABLED_FLAG in VARCHAR2,
X_FLEXFIELD_ITEM_FLAG in VARCHAR2,
X_COLUMN_NAME in VARCHAR2
) is
cursor C is select ROWID from AR_BPA_ITEMS_B
where ITEM_ID = X_ITEM_ID
;
insert into AR_BPA_ITEMS_B (
ITEM_ID,
ITEM_CODE,
ITEM_SOURCE,
ITEM_TYPE,
DISPLAY_LEVEL,
DATA_SOURCE_ID,
DISPLAY_ENABLED_FLAG,
ASSIGNMENT_ENABLED_FLAG,
AMOUNT_ITEM_FLAG,
URL_ID,
ITEM_IMAGE_FILENAME,
ITEM_MESSAGE_NAME,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
DISPLAYED_MULTI_LEVEL_FLAG,
SEEDED_APPLICATION_ID,
TAX_ITEM_FLAG,
TOTALS_ENABLED_FLAG,
LINK_ENABLED_FLAG,
DATA_TYPE,
URLCFG_ENABLED_FLAG,
FLEXFIELD_ITEM_FLAG,
COLUMN_NAME
) values (
X_ITEM_ID,
X_ITEM_CODE,
X_ITEM_SOURCE,
X_ITEM_TYPE,
X_DISPLAY_LEVEL,
X_DATA_SOURCE_ID,
X_DISPLAY_ENABLED_FLAG,
X_ASSIGNMENT_ENABLED_FLAG,
X_AMOUNT_ITEM_FLAG,
X_URL_ID,
X_ITEM_IMAGE_FILENAME,
X_ITEM_MESSAGE_NAME,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_DISPLAYED_MULTI_LEVEL_FLAG,
X_SEEDED_APPLICATION_ID,
X_TAX_ITEM_FLAG,
X_TOTALS_ENABLED_FLAG,
X_LINK_ENABLED_FLAG,
X_DATA_TYPE,
X_URLCFG_ENABLED_FLAG,
X_FLEXFIELD_ITEM_FLAG,
X_COLUMN_NAME
);
insert into AR_BPA_ITEMS_TL (
ITEM_ID,
ITEM_NAME,
ITEM_TEXT_VALUE,
DISPLAY_PROMPT,
ITEM_DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
X_ITEM_ID,
X_ITEM_NAME,
X_ITEM_TEXT_VALUE,
X_DISPLAY_PROMPT,
X_ITEM_DESCRIPTION,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_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 AR_BPA_ITEMS_TL T
where T.ITEM_ID = X_ITEM_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_ROW;
cursor c is select
ITEM_CODE,
ITEM_SOURCE,
ITEM_TYPE,
DISPLAY_LEVEL,
DATA_SOURCE_ID,
DISPLAY_ENABLED_FLAG,
ASSIGNMENT_ENABLED_FLAG,
AMOUNT_ITEM_FLAG,
URL_ID,
ITEM_IMAGE_FILENAME,
ITEM_MESSAGE_NAME,
DISPLAYED_MULTI_LEVEL_FLAG,
SEEDED_APPLICATION_ID,
TAX_ITEM_FLAG,
TOTALS_ENABLED_FLAG,
LINK_ENABLED_FLAG,
DATA_TYPE,
URLCFG_ENABLED_FLAG,
FLEXFIELD_ITEM_FLAG,
COLUMN_NAME
from AR_BPA_ITEMS_B
where ITEM_ID = X_ITEM_ID
for update of ITEM_ID nowait;
cursor c1 is select
ITEM_NAME,
ITEM_TEXT_VALUE,
DISPLAY_PROMPT,
ITEM_DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from AR_BPA_ITEMS_TL
where ITEM_ID = X_ITEM_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of ITEM_ID nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_AMOUNT_ITEM_FLAG in VARCHAR2,
X_ASSIGNMENT_ENABLED_FLAG in VARCHAR2,
X_DATA_SOURCE_ID in NUMBER,
X_DISPLAY_ENABLED_FLAG in VARCHAR2,
X_DISPLAY_LEVEL in VARCHAR2,
X_DISPLAY_PROMPT in VARCHAR2,
X_ITEM_CODE in VARCHAR2,
X_ITEM_DESCRIPTION in VARCHAR2,
X_ITEM_ID in NUMBER,
X_ITEM_IMAGE_FILENAME in VARCHAR2,
X_ITEM_MESSAGE_NAME in VARCHAR2,
X_ITEM_NAME in VARCHAR2,
X_ITEM_SOURCE in VARCHAR2,
X_ITEM_TEXT_VALUE in VARCHAR2,
X_ITEM_TYPE in VARCHAR2,
X_URL_ID in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER,
X_DISPLAYED_MULTI_LEVEL_FLAG in VARCHAR2,
X_SEEDED_APPLICATION_ID in NUMBER,
X_TAX_ITEM_FLAG in VARCHAR2,
X_TOTALS_ENABLED_FLAG in VARCHAR2,
X_LINK_ENABLED_FLAG in VARCHAR2,
X_DATA_TYPE in VARCHAR2,
X_URLCFG_ENABLED_FLAG in VARCHAR2,
X_FLEXFIELD_ITEM_FLAG in VARCHAR2,
X_COLUMN_NAME in VARCHAR2
) is
begin
update AR_BPA_ITEMS_B set
ITEM_CODE = X_ITEM_CODE,
ITEM_SOURCE = X_ITEM_SOURCE,
ITEM_TYPE = X_ITEM_TYPE,
DISPLAY_LEVEL = X_DISPLAY_LEVEL,
DATA_SOURCE_ID = X_DATA_SOURCE_ID,
DISPLAY_ENABLED_FLAG = X_DISPLAY_ENABLED_FLAG,
ASSIGNMENT_ENABLED_FLAG = X_ASSIGNMENT_ENABLED_FLAG,
AMOUNT_ITEM_FLAG = X_AMOUNT_ITEM_FLAG,
URL_ID = X_URL_ID,
ITEM_IMAGE_FILENAME = X_ITEM_IMAGE_FILENAME,
ITEM_MESSAGE_NAME = X_ITEM_MESSAGE_NAME,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
DISPLAYED_MULTI_LEVEL_FLAG = X_DISPLAYED_MULTI_LEVEL_FLAG,
SEEDED_APPLICATION_ID = X_SEEDED_APPLICATION_ID,
TAX_ITEM_FLAG = X_TAX_ITEM_FLAG,
TOTALS_ENABLED_FLAG = X_TOTALS_ENABLED_FLAG,
LINK_ENABLED_FLAG = X_LINK_ENABLED_FLAG,
DATA_TYPE = X_DATA_TYPE,
URLCFG_ENABLED_FLAG = X_URLCFG_ENABLED_FLAG,
FLEXFIELD_ITEM_FLAG = X_FLEXFIELD_ITEM_FLAG,
COLUMN_NAME = X_COLUMN_NAME
where ITEM_ID = X_ITEM_ID;
update AR_BPA_ITEMS_TL set
ITEM_NAME = X_ITEM_NAME,
ITEM_TEXT_VALUE = X_ITEM_TEXT_VALUE,
DISPLAY_PROMPT = X_DISPLAY_PROMPT,
ITEM_DESCRIPTION = X_ITEM_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 ITEM_ID = X_ITEM_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end UPDATE_ROW;
procedure DELETE_ROW (
X_ITEM_ID in NUMBER
) is
begin
delete from AR_BPA_ITEMS_TL
where ITEM_ID = X_ITEM_ID;
delete from AR_BPA_ITEMS_B
where ITEM_ID = X_ITEM_ID;
end DELETE_ROW;
delete from AR_BPA_ITEMS_TL T
where not exists
(select NULL
from AR_BPA_ITEMS_B B
where B.ITEM_ID = T.ITEM_ID
);
update AR_BPA_ITEMS_TL T set (
ITEM_NAME,
ITEM_TEXT_VALUE,
DISPLAY_PROMPT,
ITEM_DESCRIPTION
) = (select
B.ITEM_NAME,
B.ITEM_TEXT_VALUE,
B.DISPLAY_PROMPT,
B.ITEM_DESCRIPTION
from AR_BPA_ITEMS_TL B
where B.ITEM_ID = T.ITEM_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.ITEM_ID,
T.LANGUAGE
) in (select
SUBT.ITEM_ID,
SUBT.LANGUAGE
from AR_BPA_ITEMS_TL SUBB, AR_BPA_ITEMS_TL SUBT
where SUBB.ITEM_ID = SUBT.ITEM_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.ITEM_NAME <> SUBT.ITEM_NAME
or (SUBB.ITEM_NAME is null and SUBT.ITEM_NAME is not null)
or (SUBB.ITEM_NAME is not null and SUBT.ITEM_NAME is null)
or SUBB.ITEM_TEXT_VALUE <> SUBT.ITEM_TEXT_VALUE
or (SUBB.ITEM_TEXT_VALUE is null and SUBT.ITEM_TEXT_VALUE is not null)
or (SUBB.ITEM_TEXT_VALUE is not null and SUBT.ITEM_TEXT_VALUE is null)
or SUBB.DISPLAY_PROMPT <> SUBT.DISPLAY_PROMPT
or (SUBB.DISPLAY_PROMPT is null and SUBT.DISPLAY_PROMPT is not null)
or (SUBB.DISPLAY_PROMPT is not null and SUBT.DISPLAY_PROMPT is null)
or SUBB.ITEM_DESCRIPTION <> SUBT.ITEM_DESCRIPTION
or (SUBB.ITEM_DESCRIPTION is null and SUBT.ITEM_DESCRIPTION is not null)
or (SUBB.ITEM_DESCRIPTION is not null and SUBT.ITEM_DESCRIPTION is null)
));
insert into AR_BPA_ITEMS_TL (
ITEM_ID,
ITEM_NAME,
ITEM_TEXT_VALUE,
DISPLAY_PROMPT,
ITEM_DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.ITEM_ID,
B.ITEM_NAME,
B.ITEM_TEXT_VALUE,
B.DISPLAY_PROMPT,
B.ITEM_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 AR_BPA_ITEMS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from AR_BPA_ITEMS_TL T
where T.ITEM_ID = B.ITEM_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
update AR_BPA_ITEMS_TL
set ITEM_NAME = X_ITEM_NAME,
ITEM_TEXT_VALUE = X_ITEM_TEXT_VALUE,
DISPLAY_PROMPT = X_DISPLAY_PROMPT,
ITEM_DESCRIPTION = X_ITEM_DESCRIPTION,
source_lang = userenv('LANG'),
last_update_date = sysdate,
last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
last_update_login = 0
where item_id = X_item_id
and userenv('LANG') in (language, source_lang);
AR_BPA_ITEMS_PKG.UPDATE_ROW (
X_AMOUNT_ITEM_FLAG => X_AMOUNT_ITEM_FLAG,
X_ASSIGNMENT_ENABLED_FLAG => X_ASSIGNMENT_ENABLED_FLAG,
X_DATA_SOURCE_ID => X_DATA_SOURCE_ID,
X_DISPLAY_ENABLED_FLAG => X_DISPLAY_ENABLED_FLAG,
X_DISPLAY_LEVEL => X_DISPLAY_LEVEL,
X_DISPLAY_PROMPT => X_DISPLAY_PROMPT,
X_ITEM_CODE => X_ITEM_CODE,
X_ITEM_DESCRIPTION => X_ITEM_DESCRIPTION,
X_ITEM_ID => X_ITEM_ID,
X_ITEM_IMAGE_FILENAME => X_ITEM_IMAGE_FILENAME,
X_ITEM_MESSAGE_NAME => X_ITEM_MESSAGE_NAME,
X_ITEM_NAME => X_ITEM_NAME,
X_ITEM_SOURCE => X_ITEM_SOURCE,
X_ITEM_TEXT_VALUE => X_ITEM_TEXT_VALUE,
X_ITEM_TYPE => X_ITEM_TYPE,
X_URL_ID => X_URL_ID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => user_id,
X_LAST_UPDATE_LOGIN => 0,
X_DISPLAYED_MULTI_LEVEL_FLAG => X_DISPLAYED_MULTI_LEVEL_FLAG,
X_SEEDED_APPLICATION_ID => X_SEEDED_APPLICATION_ID,
X_TAX_ITEM_FLAG => X_TAX_ITEM_FLAG,
X_TOTALS_ENABLED_FLAG => X_TOTALS_ENABLED_FLAG,
X_LINK_ENABLED_FLAG => X_LINK_ENABLED_FLAG,
X_DATA_TYPE => X_DATA_TYPE,
X_URLCFG_ENABLED_FLAG => X_URLCFG_ENABLED_FLAG,
X_FLEXFIELD_ITEM_FLAG => X_FLEXFIELD_ITEM_FLAG,
X_COLUMN_NAME => X_COLUMN_NAME );
AR_BPA_ITEMS_PKG.INSERT_ROW (
X_ROWID => row_id,
X_AMOUNT_ITEM_FLAG => X_AMOUNT_ITEM_FLAG,
X_ASSIGNMENT_ENABLED_FLAG => X_ASSIGNMENT_ENABLED_FLAG,
X_DATA_SOURCE_ID => X_DATA_SOURCE_ID,
X_DISPLAY_ENABLED_FLAG => X_DISPLAY_ENABLED_FLAG,
X_DISPLAY_LEVEL => X_DISPLAY_LEVEL,
X_DISPLAY_PROMPT => X_DISPLAY_PROMPT,
X_ITEM_CODE => X_ITEM_CODE,
X_ITEM_DESCRIPTION => X_ITEM_DESCRIPTION,
X_ITEM_ID => X_ITEM_ID,
X_ITEM_IMAGE_FILENAME => X_ITEM_IMAGE_FILENAME,
X_ITEM_MESSAGE_NAME => X_ITEM_MESSAGE_NAME,
X_ITEM_NAME => X_ITEM_NAME,
X_ITEM_SOURCE => X_ITEM_SOURCE,
X_ITEM_TEXT_VALUE => X_ITEM_TEXT_VALUE,
X_ITEM_TYPE => X_ITEM_TYPE,
X_URL_ID => X_URL_ID,
X_CREATION_DATE => sysdate,
X_CREATED_BY => user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => user_id,
X_LAST_UPDATE_LOGIN => 0,
X_DISPLAYED_MULTI_LEVEL_FLAG => X_DISPLAYED_MULTI_LEVEL_FLAG,
X_SEEDED_APPLICATION_ID => X_SEEDED_APPLICATION_ID,
X_TAX_ITEM_FLAG => X_TAX_ITEM_FLAG,
X_TOTALS_ENABLED_FLAG => X_TOTALS_ENABLED_FLAG,
X_LINK_ENABLED_FLAG => X_LINK_ENABLED_FLAG,
X_DATA_TYPE => X_DATA_TYPE,
X_URLCFG_ENABLED_FLAG => X_URLCFG_ENABLED_FLAG,
X_FLEXFIELD_ITEM_FLAG => X_FLEXFIELD_ITEM_FLAG,
X_COLUMN_NAME => X_COLUMN_NAME );