The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_document_id IN OUT NOCOPY NUMBER,
X_creation_date DATE,
X_created_by NUMBER,
X_last_update_date DATE,
X_last_updated_by NUMBER,
X_last_update_login NUMBER DEFAULT NULL,
X_datatype_id NUMBER,
X_category_id NUMBER,
X_security_type NUMBER,
X_security_id NUMBER DEFAULT NULL,
X_publish_flag VARCHAR2,
X_image_type VARCHAR2 DEFAULT NULL,
X_storage_type NUMBER DEFAULT NULL,
X_usage_type VARCHAR2,
X_start_date_active DATE DEFAULT NULL,
X_end_date_active DATE DEFAULT NULL,
X_request_id NUMBER DEFAULT NULL,
X_program_application_id NUMBER DEFAULT NULL,
X_program_id NUMBER DEFAULT NULL,
X_program_update_date DATE DEFAULT NULL,
X_language VARCHAR2,
X_description VARCHAR2 DEFAULT NULL,
X_file_name VARCHAR2 DEFAULT NULL,
X_media_id IN OUT NOCOPY NUMBER,
X_Attribute_Category VARCHAR2 DEFAULT NULL,
X_Attribute1 VARCHAR2 DEFAULT NULL,
X_Attribute2 VARCHAR2 DEFAULT NULL,
X_Attribute3 VARCHAR2 DEFAULT NULL,
X_Attribute4 VARCHAR2 DEFAULT NULL,
X_Attribute5 VARCHAR2 DEFAULT NULL,
X_Attribute6 VARCHAR2 DEFAULT NULL,
X_Attribute7 VARCHAR2 DEFAULT NULL,
X_Attribute8 VARCHAR2 DEFAULT NULL,
X_Attribute9 VARCHAR2 DEFAULT NULL,
X_Attribute10 VARCHAR2 DEFAULT NULL,
X_Attribute11 VARCHAR2 DEFAULT NULL,
X_Attribute12 VARCHAR2 DEFAULT NULL,
X_Attribute13 VARCHAR2 DEFAULT NULL,
X_Attribute14 VARCHAR2 DEFAULT NULL,
X_Attribute15 VARCHAR2 DEFAULT NULL,
X_create_doc VARCHAR2 DEFAULT 'N',
X_url VARCHAR2 DEFAULT NULL,
X_title VARCHAR2 DEFAULT NULL
) IS
CURSOR C IS SELECT rowid
FROM fnd_documents
WHERE document_id = X_document_id;
SELECT fnd_documents_s.nextval
INTO X_document_id
FROM dual;
SELECT fnd_documents_short_text_s.nextval
INTO X_media_id
FROM dual;
insert into fnd_documents_short_text
(media_id, short_text, app_source_version)
select X_media_id, short_text, app_source_version
from fnd_documents_short_text
where media_id = l_media_id;
SELECT fnd_documents_long_text_s.nextval
INTO X_media_id
FROM dual;
select long_text,app_source_version into l_longtxt,l_app_s_v from fnd_documents_long_text
where media_id = l_media_id;
insert into fnd_documents_long_text
(media_id,long_text,app_source_version)
values (X_media_id,l_longtxt,l_app_s_v);
SELECT fnd_documents_long_raw_s.nextval
INTO X_media_id
FROM dual;
SELECT fnd_lobs_s.nextval
INTO X_media_id
FROM dual;
INSERT INTO fnd_documents(
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
datatype_id,
category_id,
security_type,
security_id,
publish_flag,
image_type,
storage_type,
usage_type,
start_date_active,
end_date_active,
request_id,
program_application_id,
program_id,
program_update_date,
url,
media_id,
file_name) VALUES (
X_document_id,
X_creation_date,
X_created_by,
X_last_update_date,
X_last_updated_by,
X_last_update_login,
X_datatype_id,
X_category_id,
X_security_type,
X_security_id,
X_publish_flag,
X_image_type,
X_storage_type,
X_usage_type,
X_start_date_active,
X_end_date_active,
X_request_id,
X_program_application_id,
X_program_id,
X_program_update_date,
X_url,
X_media_id,
X_file_name);
insert_tl_row(X_document_id => X_document_id,
X_creation_date => X_creation_date,
X_created_by => X_created_by,
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login,
X_language => X_language,
X_description => X_description,
X_request_id => X_request_id,
X_program_application_id => X_program_application_id,
X_program_id => X_program_id,
X_program_update_date => X_program_update_date,
X_attribute_category => X_attribute_category,
X_attribute1 => X_attribute1,
X_attribute2 => X_attribute2,
X_attribute3 => X_attribute3,
X_attribute4 => X_attribute4,
X_attribute5 => X_attribute5,
X_attribute6 => X_attribute6,
X_attribute7 => X_attribute7,
X_attribute8 => X_attribute8,
X_attribute9 => X_attribute9,
X_attribute10 => X_attribute10,
X_attribute11 => X_attribute11,
X_attribute12 => X_attribute12,
X_attribute13 => X_attribute13,
X_attribute14 => X_attribute14,
X_attribute15 => X_attribute15,
X_title => X_title);
END Insert_Row;
PROCEDURE insert_tl_row(X_document_id NUMBER,
X_creation_date DATE,
X_created_by NUMBER,
X_last_update_date DATE,
X_last_updated_by NUMBER,
X_last_update_login NUMBER DEFAULT NULL,
X_language VARCHAR2,
X_description VARCHAR2 DEFAULT NULL,
X_request_id NUMBER DEFAULT NULL,
X_program_application_id NUMBER DEFAULT NULL,
X_program_id NUMBER DEFAULT NULL,
X_program_update_date DATE DEFAULT NULL,
X_Attribute_Category VARCHAR2 DEFAULT NULL,
X_Attribute1 VARCHAR2 DEFAULT NULL,
X_Attribute2 VARCHAR2 DEFAULT NULL,
X_Attribute3 VARCHAR2 DEFAULT NULL,
X_Attribute4 VARCHAR2 DEFAULT NULL,
X_Attribute5 VARCHAR2 DEFAULT NULL,
X_Attribute6 VARCHAR2 DEFAULT NULL,
X_Attribute7 VARCHAR2 DEFAULT NULL,
X_Attribute8 VARCHAR2 DEFAULT NULL,
X_Attribute9 VARCHAR2 DEFAULT NULL,
X_Attribute10 VARCHAR2 DEFAULT NULL,
X_Attribute11 VARCHAR2 DEFAULT NULL,
X_Attribute12 VARCHAR2 DEFAULT NULL,
X_Attribute13 VARCHAR2 DEFAULT NULL,
X_Attribute14 VARCHAR2 DEFAULT NULL,
X_Attribute15 VARCHAR2 DEFAULT NULL,
X_title VARCHAR2 DEFAULT NULL)
IS
BEGIN
-- insert into "language" specific table
INSERT INTO fnd_Documents_tl (
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
language,
description,
request_id,
program_application_id,
program_id,
program_update_date,
doc_attribute_category,
doc_attribute1,
doc_attribute2,
doc_attribute3,
doc_attribute4,
doc_attribute5,
doc_attribute6,
doc_attribute7,
doc_attribute8,
doc_attribute9,
doc_attribute10,
doc_attribute11,
doc_attribute12,
doc_attribute13,
doc_attribute14,
doc_attribute15,
source_lang,
title) SELECT
X_document_id,
X_creation_date,
X_created_by,
X_last_update_date,
X_last_updated_by,
X_last_update_login,
L.language_code,
X_description,
X_request_id,
X_program_application_id,
X_program_id,
X_program_update_date,
X_attribute_category,
X_attribute1,
X_attribute2,
X_attribute3,
X_attribute4,
X_attribute5,
X_attribute6,
X_attribute7,
X_attribute8,
X_attribute9,
X_attribute10,
X_attribute11,
X_attribute12,
X_attribute13,
X_attribute14,
X_attribute15,
userenv('LANG'),
X_title
FROM fnd_languages L
WHERE l.installed_flag IN ('I','B')
AND NOT EXISTS (SELECT null
FROM fnd_documents_tl TL
WHERE document_id = x_document_id
AND TL.language = l.language_code);
END Insert_tl_Row;
SELECT *
FROM fnd_documents
WHERE document_id = X_document_id
FOR UPDATE of document_id NOWAIT;
SELECT *
FROM fnd_documents_tl
WHERE document_id = X_document_id
AND language = X_language
FOR UPDATE of language NOWAIT;
PROCEDURE Update_Row(X_document_id NUMBER,
X_last_update_date DATE,
X_last_updated_by NUMBER,
X_last_update_login NUMBER,
X_datatype_id NUMBER,
X_category_id NUMBER,
X_security_type NUMBER,
X_security_id NUMBER,
X_publish_flag VARCHAR2,
X_image_type VARCHAR2,
X_storage_type NUMBER,
X_usage_type VARCHAR2,
X_start_date_active DATE,
X_end_date_active DATE,
X_language VARCHAR2,
X_description VARCHAR2,
X_file_name VARCHAR2,
X_media_id NUMBER,
X_Attribute_Category VARCHAR2 DEFAULT NULL,
X_Attribute1 VARCHAR2 DEFAULT NULL,
X_Attribute2 VARCHAR2 DEFAULT NULL,
X_Attribute3 VARCHAR2 DEFAULT NULL,
X_Attribute4 VARCHAR2 DEFAULT NULL,
X_Attribute5 VARCHAR2 DEFAULT NULL,
X_Attribute6 VARCHAR2 DEFAULT NULL,
X_Attribute7 VARCHAR2 DEFAULT NULL,
X_Attribute8 VARCHAR2 DEFAULT NULL,
X_Attribute9 VARCHAR2 DEFAULT NULL,
X_Attribute10 VARCHAR2 DEFAULT NULL,
X_Attribute11 VARCHAR2 DEFAULT NULL,
X_Attribute12 VARCHAR2 DEFAULT NULL,
X_Attribute13 VARCHAR2 DEFAULT NULL,
X_Attribute14 VARCHAR2 DEFAULT NULL,
X_Attribute15 VARCHAR2 DEFAULT NULL,
X_url VARCHAR2 DEFAULT NULL,
X_title VARCHAR2 DEFAULT NULL
) IS
BEGIN
UPDATE fnd_documents
SET document_id = X_document_id,
last_update_date = X_last_update_date,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login,
datatype_id = X_datatype_id,
category_id = X_category_id,
security_type = X_security_type,
security_id = X_security_id,
publish_flag = X_publish_flag,
image_type = X_image_type,
storage_type = X_storage_type,
usage_type = X_usage_type,
start_date_active = X_start_date_Active,
end_date_active = X_end_date_Active,
url = X_url,
media_id = X_media_id,
file_name = X_file_name
WHERE document_id = X_document_id;
Update_tl_Row(X_document_id => X_document_id,
X_last_update_date => X_last_update_date,
X_last_updated_by => X_last_updated_by,
X_last_update_login => X_last_update_login,
X_language => X_language,
X_description => X_description,
X_Attribute_Category => X_Attribute_Category,
X_Attribute1 => X_Attribute1,
X_Attribute2 => X_Attribute2,
X_Attribute3 => X_Attribute3,
X_Attribute4 => X_Attribute4,
X_Attribute5 => X_Attribute5,
X_Attribute6 => X_Attribute6,
X_Attribute7 => X_Attribute7,
X_Attribute8 => X_Attribute8,
X_Attribute9 => X_Attribute9,
X_Attribute10 => X_Attribute10,
X_Attribute11 => X_Attribute11,
X_Attribute12 => X_Attribute12,
X_Attribute13 => X_Attribute13,
X_Attribute14 => X_Attribute14,
X_Attribute15 => X_Attribute15,
X_title => X_title);
END Update_Row;
PROCEDURE Update_tl_Row(X_document_id NUMBER,
X_last_update_date DATE,
X_last_updated_by NUMBER,
X_last_update_login NUMBER DEFAULT NULL,
X_language VARCHAR2,
X_description VARCHAR2 DEFAULT NULL,
X_Attribute_Category VARCHAR2 DEFAULT NULL,
X_Attribute1 VARCHAR2 DEFAULT NULL,
X_Attribute2 VARCHAR2 DEFAULT NULL,
X_Attribute3 VARCHAR2 DEFAULT NULL,
X_Attribute4 VARCHAR2 DEFAULT NULL,
X_Attribute5 VARCHAR2 DEFAULT NULL,
X_Attribute6 VARCHAR2 DEFAULT NULL,
X_Attribute7 VARCHAR2 DEFAULT NULL,
X_Attribute8 VARCHAR2 DEFAULT NULL,
X_Attribute9 VARCHAR2 DEFAULT NULL,
X_Attribute10 VARCHAR2 DEFAULT NULL,
X_Attribute11 VARCHAR2 DEFAULT NULL,
X_Attribute12 VARCHAR2 DEFAULT NULL,
X_Attribute13 VARCHAR2 DEFAULT NULL,
X_Attribute14 VARCHAR2 DEFAULT NULL,
X_Attribute15 VARCHAR2 DEFAULT NULL,
X_title VARCHAR2 DEFAULT NULL
) IS
BEGIN
UPDATE fnd_documents_tl
SET document_id = X_document_id,
last_update_date = X_last_update_date,
last_updated_by = X_last_updated_by,
last_update_login = X_last_update_login,
language = X_language,
description = X_description,
doc_attribute_category = X_Attribute_Category,
doc_attribute1 = X_Attribute1,
doc_attribute2 = X_Attribute2,
doc_attribute3 = X_Attribute3,
doc_attribute4 = X_Attribute4,
doc_attribute5 = X_Attribute5,
doc_attribute6 = X_Attribute6,
doc_attribute7 = X_Attribute7,
doc_attribute8 = X_Attribute8,
doc_attribute9 = X_Attribute9,
doc_attribute10 = X_Attribute10,
doc_attribute11 = X_Attribute11,
doc_attribute12 = X_Attribute12,
doc_attribute13 = X_Attribute13,
doc_attribute14 = X_Attribute14,
doc_attribute15 = X_Attribute15,
title = X_title
WHERE document_id = X_document_id
AND language = X_language;
END Update_tl_Row;
PROCEDURE Delete_Row(X_document_id NUMBER,
X_datatype_id NUMBER,
delete_ref_Flag VARCHAR2 DEFAULT 'N') IS
BEGIN
-- need to delete in this order for R10-10SC compatibility
-- triggers to operate properly
-- 1. fnd_attached_documents
-- 2. fnd_documents_short_text/long_text/long_raw
-- 3. fnd_documents_tl
-- 4. fnd_documents
-- Delete the Reference if flag set to Y
IF (delete_ref_flag = 'Y') THEN
DELETE FROM fnd_attached_documents
WHERE document_id = X_document_id;
DELETE FROM fnd_documents_short_text
WHERE media_id IN
(SELECT media_id
FROM fnd_documents
WHERE document_id = x_document_id);
DELETE FROM fnd_documents_long_text
WHERE media_id IN
(SELECT media_id
FROM fnd_documents
WHERE document_id = x_document_id);
DELETE FROM fnd_documents_long_raw
WHERE media_id IN
(SELECT media_id
FROM fnd_documents
WHERE document_id = x_document_id);
DELETE FROM fnd_lobs
WHERE file_id in
(SELECT media_id
from fnd_documents
WHERE document_id = x_document_id);
DELETE FROM fnd_documents_tl
WHERE document_id = X_document_id;
DELETE FROM fnd_documents
WHERE document_id = X_document_id;
END Delete_Row;
/* The following delete and update statements are commented out */
/* as a quick workaround to fix the time-consuming table handler issue */
/* Eventually we'll need to turn them into a separate fix_language procedure */
/*
delete from FND_DOCUMENTS_TL T
where not exists
(select NULL
from FND_DOCUMENTS B
where B.DOCUMENT_ID = T.DOCUMENT_ID
);
update FND_DOCUMENTS_TL T set (
DESCRIPTION
) = (select
B.DESCRIPTION
from FND_DOCUMENTS_TL B
where B.DOCUMENT_ID = T.DOCUMENT_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.DOCUMENT_ID,
T.LANGUAGE
) in (select
SUBT.DOCUMENT_ID,
SUBT.LANGUAGE
from FND_DOCUMENTS_TL SUBB, FND_DOCUMENTS_TL SUBT
where SUBB.DOCUMENT_ID = SUBT.DOCUMENT_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (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 /*+ append parallel(tt) */ into FND_DOCUMENTS_TL tt (
DOCUMENT_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
DESCRIPTION,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
DOC_ATTRIBUTE_CATEGORY,
DOC_ATTRIBUTE1,
DOC_ATTRIBUTE2,
DOC_ATTRIBUTE3,
DOC_ATTRIBUTE4,
DOC_ATTRIBUTE5,
DOC_ATTRIBUTE6,
DOC_ATTRIBUTE7,
DOC_ATTRIBUTE8,
DOC_ATTRIBUTE9,
DOC_ATTRIBUTE10,
DOC_ATTRIBUTE11,
DOC_ATTRIBUTE12,
DOC_ATTRIBUTE13,
DOC_ATTRIBUTE14,
DOC_ATTRIBUTE15,
APP_SOURCE_VERSION,
SHORT_TEXT,
LANGUAGE,
SOURCE_LANG)
select /*+ parallel(v) parallel(t) use_nl(t) */ v.*
from( SELECT /*+ no_merge ordered parellel(b) */
B.DOCUMENT_ID,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_LOGIN,
B.DESCRIPTION,
B.REQUEST_ID,
B.PROGRAM_APPLICATION_ID,
B.PROGRAM_ID,
B.PROGRAM_UPDATE_DATE,
B.DOC_ATTRIBUTE_CATEGORY,
B.DOC_ATTRIBUTE1,
B.DOC_ATTRIBUTE2,
B.DOC_ATTRIBUTE3,
B.DOC_ATTRIBUTE4,
B.DOC_ATTRIBUTE5,
B.DOC_ATTRIBUTE6,
B.DOC_ATTRIBUTE7,
B.DOC_ATTRIBUTE8,
B.DOC_ATTRIBUTE9,
B.DOC_ATTRIBUTE10,
B.DOC_ATTRIBUTE11,
B.DOC_ATTRIBUTE12,
B.DOC_ATTRIBUTE13,
B.DOC_ATTRIBUTE14,
B.DOC_ATTRIBUTE15,
B.APP_SOURCE_VERSION,
B.SHORT_TEXT,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from FND_DOCUMENTS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
) v, fnd_documents_tl t
where T.DOCUMENT_ID(+) = v.DOCUMENT_ID
and T.LANGUAGE(+) = v.LANGUAGE_CODE
and t.document_id IS NULL;