The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW (
X_ROWID in OUT NOCOPY VARCHAR2,
X_USER_STATUS_ID in NUMBER,
X_DEFAULT_FLAG in VARCHAR2,
X_SEEDED_FLAG in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_SYSTEM_STATUS_TYPE in VARCHAR2,
X_SYSTEM_STATUS_CODE in VARCHAR2,
X_ENABLED_FLAG in VARCHAR2,
X_START_DATE_ACTIVE in DATE,
X_END_DATE_ACTIVE in DATE,
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_APPLICATION_ID in NUMBER DEFAULT '530'
) is
cursor C is select ROWID from AMS_USER_STATUSES_B
where USER_STATUS_ID = X_USER_STATUS_ID
;
insert into AMS_USER_STATUSES_B (
DEFAULT_FLAG,
SEEDED_FLAG,
USER_STATUS_ID,
OBJECT_VERSION_NUMBER,
SYSTEM_STATUS_TYPE,
SYSTEM_STATUS_CODE,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
APPLICATION_ID
) values (
X_DEFAULT_FLAG,
X_SEEDED_FLAG,
X_USER_STATUS_ID,
X_OBJECT_VERSION_NUMBER,
X_SYSTEM_STATUS_TYPE,
X_SYSTEM_STATUS_CODE,
X_ENABLED_FLAG,
X_START_DATE_ACTIVE,
X_END_DATE_ACTIVE,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_APPLICATION_ID
);
insert into AMS_USER_STATUSES_TL (
USER_STATUS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
X_USER_STATUS_ID,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
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 AMS_USER_STATUSES_TL T
where T.USER_STATUS_ID = X_USER_STATUS_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
end INSERT_ROW;
cursor c is select
DEFAULT_FLAG,
SEEDED_FLAG,
OBJECT_VERSION_NUMBER,
SYSTEM_STATUS_TYPE,
SYSTEM_STATUS_CODE,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE
from AMS_USER_STATUSES_B
where USER_STATUS_ID = X_USER_STATUS_ID
for update of USER_STATUS_ID nowait;
cursor c1 is select
NAME,
DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from AMS_USER_STATUSES_TL
where USER_STATUS_ID = X_USER_STATUS_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of USER_STATUS_ID nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW (
X_USER_STATUS_ID in NUMBER,
X_DEFAULT_FLAG in VARCHAR2,
X_SEEDED_FLAG in VARCHAR2,
X_OBJECT_VERSION_NUMBER in NUMBER,
X_SYSTEM_STATUS_TYPE in VARCHAR2,
X_SYSTEM_STATUS_CODE in VARCHAR2,
X_ENABLED_FLAG in VARCHAR2,
X_START_DATE_ACTIVE in DATE,
X_END_DATE_ACTIVE in DATE,
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_APPLICATION_ID in NUMBER DEFAULT '530'
) is
/*08-May-2006 mayjain fix for bug 5166318*/
cursor count_def_flag (P_SYSTEM_STATUS_TYPE VARCHAR2, P_SYSTEM_STATUS_CODE VARCHAR2)
IS
select count(1)
from AMS_USER_STATUSES_B
where SYSTEM_STATUS_TYPE = P_SYSTEM_STATUS_TYPE and
SYSTEM_STATUS_CODE = P_SYSTEM_STATUS_CODE and
ENABLED_FLAG = 'Y' and
DEFAULT_FLAG = 'Y' and
SEEDED_FLAG <> 'Y';
update AMS_USER_STATUSES_B set
DEFAULT_FLAG = l_default_flag, /*08-May-2006 mayjain fix for bug 5166318*/
SEEDED_FLAG = X_SEEDED_FLAG,
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
SYSTEM_STATUS_TYPE = X_SYSTEM_STATUS_TYPE,
SYSTEM_STATUS_CODE = X_SYSTEM_STATUS_CODE,
ENABLED_FLAG = X_ENABLED_FLAG,
START_DATE_ACTIVE = X_START_DATE_ACTIVE,
END_DATE_ACTIVE = X_END_DATE_ACTIVE,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
APPLICATION_ID = X_APPLICATION_ID
where USER_STATUS_ID = X_USER_STATUS_ID;
update AMS_USER_STATUSES_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 USER_STATUS_ID = X_USER_STATUS_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end UPDATE_ROW;
procedure DELETE_ROW (
X_USER_STATUS_ID in NUMBER
) is
begin
delete from AMS_USER_STATUSES_TL
where USER_STATUS_ID = X_USER_STATUS_ID;
delete from AMS_USER_STATUSES_B
where USER_STATUS_ID = X_USER_STATUS_ID;
end DELETE_ROW;
delete from AMS_USER_STATUSES_TL T
where not exists
(select NULL
from AMS_USER_STATUSES_B B
where B.USER_STATUS_ID = T.USER_STATUS_ID
);
update AMS_USER_STATUSES_TL T set (
NAME,
DESCRIPTION
) = (select
B.NAME,
B.DESCRIPTION
from AMS_USER_STATUSES_TL B
where B.USER_STATUS_ID = T.USER_STATUS_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.USER_STATUS_ID,
T.LANGUAGE
) in (select
SUBT.USER_STATUS_ID,
SUBT.LANGUAGE
from AMS_USER_STATUSES_TL SUBB, AMS_USER_STATUSES_TL SUBT
where SUBB.USER_STATUS_ID = SUBT.USER_STATUS_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 AMS_USER_STATUSES_TL (
USER_STATUS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
B.USER_STATUS_ID,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
B.NAME,
B.DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from AMS_USER_STATUSES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from AMS_USER_STATUSES_TL T
where T.USER_STATUS_ID = B.USER_STATUS_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
update AMS_USER_STATUSES_TL set
name = nvl(x_name, name),
description = nvl(x_description, description),
source_lang = userenv('LANG'),
last_update_date = sysdate,
last_updated_by = decode(x_owner, 'SEED', 1, 0),
last_update_login = 0
where user_status_id = x_user_status_id
and userenv('LANG') in (language, source_lang);
by comparing last updated by value to be
SEED/DATAMERGE(1), or
INITIAL SETUP/ORACLE (2), or
SYSTEM ADMINISTRATOR (0).or
2) modify the whole data when custom_mode is 'FORCE'
3) if the data in db is modified by customer, which can be found by
by comparing last updated by value to be not of 0,1,2, then
in that case modify only the user unexposed data with last updated by as 3 to
distinguish that data is updated by patch.
*/
procedure LOAD_ROW (
X_USER_STATUS_ID in NUMBER,
X_DEFAULT_FLAG in VARCHAR2 DEFAULT 'N',
X_SEEDED_FLAG in VARCHAR2 DEFAULT 'Y',
X_OBJECT_VERSION_NUMBER in NUMBER,
X_SYSTEM_STATUS_TYPE in VARCHAR2,
X_SYSTEM_STATUS_CODE in VARCHAR2,
X_ENABLED_FLAG in VARCHAR2 DEFAULT 'Y',
X_START_DATE_ACTIVE in DATE,
X_END_DATE_ACTIVE in DATE,
X_NAME in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_OWNER in VARCHAR2,
X_APPLICATION_ID in NUMBER DEFAULT '530',
X_CUSTOM_MODE in VARCHAR2
) IS
l_user_id number := 1;
select object_version_number
from AMS_USER_STATUSES_B
where user_status_id = X_USER_STATUS_ID;
select 'x'
from AMS_USER_STATUSES_B
where user_status_id = X_USER_STATUS_ID;
select AMS_USER_STATUSES_B_S.nextval
from dual;
select last_updated_by, nvl(object_version_number,1)
from AMS_USER_STATUSES_B
where user_status_id = X_USER_STATUS_ID;
AMS_USER_STATUSES_PKG.INSERT_ROW(
X_ROWID => l_row_id,
X_USER_STATUS_ID => l_user_status_id,
X_DEFAULT_FLAG => X_DEFAULT_FLAG,
X_SEEDED_FLAG => X_SEEDED_FLAG,
X_OBJECT_VERSION_NUMBER => l_obj_verno,
X_SYSTEM_STATUS_TYPE => X_SYSTEM_STATUS_TYPE,
X_SYSTEM_STATUS_CODE => X_SYSTEM_STATUS_CODE,
X_ENABLED_FLAG => X_ENABLED_FLAG,
X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
X_NAME => X_NAME,
X_DESCRIPTION => X_DESCRIPTION,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => 0,
X_APPLICATION_ID => X_APPLICATION_ID);
AMS_USER_STATUSES_PKG.UPDATE_ROW(
X_USER_STATUS_ID => l_user_status_id,
X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
X_DEFAULT_FLAG => X_DEFAULT_FLAG,
X_SEEDED_FLAG => X_SEEDED_FLAG,
X_SYSTEM_STATUS_TYPE => X_SYSTEM_STATUS_TYPE,
X_SYSTEM_STATUS_CODE => X_SYSTEM_STATUS_CODE,
X_ENABLED_FLAG => X_ENABLED_FLAG,
X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
X_NAME => X_NAME,
X_DESCRIPTION => X_DESCRIPTION,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => 0,
X_APPLICATION_ID => X_APPLICATION_ID);