The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqlbuf := 'SELECT category, severity, fnd_log_severity
FROM fnd_new_messages
WHERE ROWNUM < 2';
/***** Commented Update Statement
update FND_NEW_MESSAGES T set (
MESSAGE_TEXT
) = (select
B.MESSAGE_TEXT
from FND_NEW_MESSAGES B
where B.APPLICATION_ID = T.APPLICATION_ID
and B.LANGUAGE_CODE = T.LANGUAGE_CODE
and B.MESSAGE_NAME = T.MESSAGE_NAME
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.APPLICATION_ID,
T.LANGUAGE_CODE,
T.MESSAGE_NAME,
T.LANGUAGE
) in (select
SUBT.APPLICATION_ID,
SUBT.LANGUAGE_CODE,
SUBT.MESSAGE_NAME,
SUBT.LANGUAGE
from FND_NEW_MESSAGES SUBB, FND_NEW_MESSAGES SUBT
where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
and SUBB.LANGUAGE_CODE = SUBT.LANGUAGE_CODE
and SUBB.MESSAGE_NAME = SUBT.MESSAGE_NAME
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.MESSAGE_TEXT <> SUBT.MESSAGE_TEXT
));
insert into FND_NEW_MESSAGES (
FND_LOG_SEVERITY,
APPLICATION_ID,
LANGUAGE_CODE,
MESSAGE_NUMBER,
MESSAGE_NAME,
MESSAGE_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
DESCRIPTION,
TYPE,
MAX_LENGTH,
CATEGORY,
SEVERITY
) select -- Dropped ORDERED hint here
B.FND_LOG_SEVERITY,
B.APPLICATION_ID,
L.LANGUAGE_CODE,
B.MESSAGE_NUMBER,
B.MESSAGE_NAME,
B.MESSAGE_TEXT,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.DESCRIPTION,
B.TYPE,
B.MAX_LENGTH,
B.CATEGORY,
B.SEVERITY
from FND_NEW_MESSAGES B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE_CODE = userenv('LANG')
and not exists
(select NULL
from FND_NEW_MESSAGES T
where T.APPLICATION_ID = B.APPLICATION_ID
and T.LANGUAGE_CODE = L.LANGUAGE_CODE
and T.MESSAGE_NAME = B.MESSAGE_NAME);
sql_string := 'insert into FND_NEW_MESSAGES (
APPLICATION_ID,
LANGUAGE_CODE,
MESSAGE_NUMBER,
MESSAGE_NAME,
MESSAGE_TEXT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
DESCRIPTION,
TYPE,
MAX_LENGTH ';
') select /*+ ORDERED */
B.APPLICATION_ID,
L.LANGUAGE_CODE,
B.MESSAGE_NUMBER,
B.MESSAGE_NAME,
B.MESSAGE_TEXT,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.DESCRIPTION,
B.TYPE,
B.MAX_LENGTH ';
(select NULL
from FND_NEW_MESSAGES T
where T.APPLICATION_ID = B.APPLICATION_ID
and T.LANGUAGE_CODE = L.LANGUAGE_CODE
and T.MESSAGE_NAME = B.MESSAGE_NAME)';
X_LAST_UPDATE_DATE => null
);
X_LAST_UPDATE_DATE in VARCHAR2
) is
f_luby number; -- entity owner in file
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
select decode(X_MESSAGE_NUMBER, fnd_load_util.null_value, null,
null, X_MESSAGE_NUMBER,
TO_NUMBER(X_MESSAGE_NUMBER)),
decode(X_TYPE, fnd_load_util.null_value, null,
null, X_TYPE,
X_TYPE),
decode(X_DESCRIPTION, fnd_load_util.null_value, null,
null, X_DESCRIPTION,
X_DESCRIPTION),
decode(X_MAX_LENGTH, fnd_load_util.null_value, null,
null, X_MAX_LENGTH,
TO_NUMBER(X_MAX_LENGTH)),
decode(X_CATEGORY, fnd_load_util.null_value, null,
null, X_CATEGORY,
X_CATEGORY),
decode(X_SEVERITY, fnd_load_util.null_value, null,
null, X_SEVERITY,
X_SEVERITY),
decode(X_FND_LOG_SEVERITY, fnd_load_util.null_value, null,
null, X_FND_LOG_SEVERITY,
TO_NUMBER(X_FND_LOG_SEVERITY))
into message_number,
msg_type,
description,
max_length,
category,
severity,
fnd_log_severity
from dual;
select LAST_UPDATED_BY, LAST_UPDATE_DATE
into db_luby, db_ludate
from FND_NEW_MESSAGES
where application_id = app_id
and language_code = userenv('LANG')
and message_name = X_MESSAGE_NAME;
sql_string := 'update fnd_new_messages set
message_number = :1,
description = :2,
type = :3,
max_length = :4,';
'last_updated_by = :8,
last_update_date = :9,
last_update_login = 0
where application_id = :10
and message_name = :11';
sql_string := 'update fnd_new_messages set
message_text = :1
where application_id = :2
and message_name = :3
and language_code = ''' || userenv('LANG') || '''';
sql_string := 'insert into fnd_new_messages
(application_id,
language_code,
message_number,
message_name,
message_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
description,
type,
max_length ';
X_LAST_UPDATE_DATE => null
);
X_LAST_UPDATE_DATE in VARCHAR2
) is
f_luby number; -- entity owner in file
f_ludate date; -- entity update date in file
db_ludate date; -- entity update date in db
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 FND_NEW_MESSAGES
where application_id = app_id
and language_code = userenv('LANG')
and message_name = X_MESSAGE_NAME;
update fnd_new_messages set
message_text = nvl(X_MESSAGE_TEXT, message_text),
last_updated_by = f_luby,
last_update_date = f_ludate,
last_update_login = 0
where application_id = app_id
and language_code = userenv('LANG')
and message_name = X_MESSAGE_NAME;
sql_string := 'insert into fnd_new_messages
(application_id,
language_code,
message_number,
message_name,
message_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
description,
type,
max_length ';
') select
application_id,
''' || userenv('LANG') || ''', ' ||
'message_number,
message_name,
:1,
:2,
:3,
:4,
:5,
0,
description,
type,
max_length';
' from (select
application_id,
language_code,
message_number,
message_name,
message_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
description,
type,
max_length ';
(select L.language_code from fnd_languages L
where L.installed_flag = ''B''), 2,
3)
)
where rownum = 1';
procedure DELETE_ROW (
X_APPLICATION_ID in NUMBER,
X_LANGUAGE_CODE in VARCHAR2,
X_MESSAGE_NAME in VARCHAR2
) is
begin
delete from FND_NEW_MESSAGES
where APPLICATION_ID = X_APPLICATION_ID
and LANGUAGE_CODE = X_LANGUAGE_CODE
and MESSAGE_NAME = X_MESSAGE_NAME;
end DELETE_ROW;
select count(*) into count_category from fnd_lookups
where lookup_type = 'FND_KBF_CATEGORY';
select count(*) into count_category from fnd_lookups
where lookup_type = 'FND_KBF_CATEGORY' AND
lookup_code = X_CATEGORY;
select count(*) into count_severity from fnd_lookups
where lookup_type = 'FND_KBF_SEVERITY';
select count(*) into count_severity from fnd_lookups
where lookup_type = 'FND_KBF_SEVERITY' AND
lookup_code = X_SEVERITY;
select count(*) into count_severity from fnd_lookups
where lookup_type = 'AFLOG_LEVELS';
select count(*) into count_severity from fnd_lookups
where lookup_type = 'AFLOG_LEVELS' AND
lookup_code = X_FND_LOG_SEVERITY;