[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_operation VARCHAR2,-- 'INSERT' | 'UPDATE'
p_version VARCHAR2 -- 'SO_10SC' | 'SO_R10'
) IS
x_document_id NUMBER;
INSERT INTO so_note_replication (
source_id,
datatype_id,
source_code,
operation_code,
version
) VALUES (
p_src_id,
decode(p_short_long,'S',1,'L',2),
p_source,
p_operation,
p_version
);
p_operation VARCHAR2,-- 'INSERT' | 'UPDATE'
p_version VARCHAR2 -- 'SO_10SC' | 'SO_R10'
) IS
BEGIN
DELETE FROM so_note_replication
WHERE datatype_id = decode(p_short_long,'S',1,'L',2)
AND source_code = p_source
AND operation_code = p_operation
AND version = p_version;
SELECT SUBSTR(description,1,30)
FROM fnd_documents_tl
WHERE document_id = p_document_id
AND language = x_language;
SELECT 1
FROM sys.dual
WHERE EXISTS (SELECT 1
FROM so_notes
WHERE name = x_doc_desc);
IF (p_msg_name = 'OE_NOT_UPDATE_NOTE') THEN
fnd_message.set_name('OE', p_msg_name);
SELECT NOTE_TYPE_CODE, OVERRIDE_ALLOWED_FLAG
INTO x_note_type_code, x_override_flag
FROM so_notes
WHERE note_id = p_note_id;
SELECT order_category
FROM so_headers
WHERE header_id = p_header_id;
SELECT snu.category_id
INTO p_category_id
FROM so_note_usages snu
WHERE snu.usage_id = p_usage_id;
SELECT snu.usage_id
INTO p_usage_id
FROM fnd_document_categories fdc,
so_note_usages snu
WHERE fdc.category_id = snu.category_id
AND fdc.category_id = p_category_id;
SELECT fd.datatype_id, fdt.media_id
INTO p_datatype_id, p_media_id
FROM fnd_documents_tl fdt,
fnd_documents fd
WHERE fdt.language = fnd_global.current_language
AND fdt.document_id = fd.document_id
AND fd.document_id = p_document_id;
SELECT fdc.application_id
FROM fnd_document_categories fdc,
fnd_documents fd
WHERE fd.category_id = fdc.category_id
AND fd.document_id = p_document_id;
SELECT application_id
FROM fnd_document_categories
WHERE category_id = p_category_id;
SELECT fd.document_id, fd.category_id,
fd.usage_type, fdc.application_id
FROM fnd_document_categories fdc,
fnd_documents_tl fdt,
fnd_documents fd
WHERE fdt.media_id = p_media_id
AND fdt.document_id = fd.document_id
AND fd.datatype_id = decode (p_short_long, 'S', 1, 2) --short/long
AND fd.category_id = fdc.category_id;
PROCEDURE insert_document (
p_document_id NUMBER,
p_app_source_version VARCHAR2
) IS
x_category_id NUMBER;
x_last_update_date DATE;
x_last_updated_by NUMBER;
x_last_update_login NUMBER;
SELECT
note_id, last_update_date,
last_updated_by, last_update_login,
creation_date, created_by,
name, usage_id,
start_date_active, end_date_active,
context, attribute1,
attribute2, attribute3,
attribute4, attribute5,
attribute6, attribute7,
attribute8, attribute9,
attribute10, attribute11,
attribute12, attribute13,
attribute14, attribute15
INTO
x_note_id, x_last_update_date,
x_last_updated_by, x_last_update_login,
x_creation_date, x_created_by,
x_name, x_usage_id,
x_start_date_active, x_end_date_active,
x_context, 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
FROM so_notes
WHERE document_id = p_document_id;
SELECT note
INTO x_note
FROM so_notes
WHERE document_id = p_document_id;
x_note := so_att.get_note_error('OE_NOT_UPDATE_NOTE');
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, storage_type,
usage_type, app_source_version,
start_date_active, end_date_active )
SELECT
p_document_id, x_creation_date,
x_created_by, sysdate,
1, 1,
x_datatype_id, x_category_id,
4, NULL,
'N', NULL,
x_document_usage_type, p_app_source_version,
x_start_date_active, x_end_date_active
FROM dual;
SELECT fnd_documents_short_text_s.nextval
INTO x_media_id
FROM dual;
SELECT fnd_documents_long_text_s.nextval
INTO x_media_id
FROM dual;
INSERT INTO fnd_documents_tl (
document_id, creation_date,
created_by, last_update_date,
last_updated_by, last_update_login,
language, description,
file_name, media_id,
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, source_lang )
SELECT
p_document_id, x_creation_date,
x_created_by, sysdate,
1, 1,
fnd_global.current_language, x_name,
NULL, x_media_id,
x_context, 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,
p_app_source_version, fnd_global.current_language
FROM dual;
INSERT INTO fnd_documents_short_text (
media_id, short_text, app_source_version )
SELECT
x_media_id, x_note, p_app_source_version
FROM dual;
INSERT INTO fnd_documents_long_text (
media_id, long_text, app_source_version )
VALUES (
x_media_id, x_note, p_app_source_version);
PROCEDURE update_document (
p_document_id NUMBER,
p_app_source_version VARCHAR2
) IS
x_category_id NUMBER;
x_last_update_date DATE;
x_last_updated_by NUMBER;
x_last_update_login NUMBER;
SELECT
note_id, last_update_date,
last_updated_by, last_update_login,
creation_date, created_by,
name, usage_id, note_type_code,
start_date_active, end_date_active,
context, attribute1,
attribute2, attribute3,
attribute4, attribute5,
attribute6, attribute7,
attribute8, attribute9,
attribute10, attribute11,
attribute12, attribute13,
attribute14, attribute15,
override_allowed_flag
INTO
x_note_id, x_last_update_date,
x_last_updated_by, x_last_update_login,
x_creation_date, x_created_by,
x_name, x_usage_id, x_note_type_code,
x_start_date_active, x_end_date_active,
x_context, 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,
x_override_flag
FROM so_notes
WHERE
document_id = p_document_id;
SELECT note
INTO x_note
FROM so_notes
WHERE document_id = p_document_id;
UPDATE fnd_documents
SET app_source_version = 'SO_R10',
category_id = x_category_id,
usage_type = DECODE(x_note_type_code,'OT','O',
DECODE(x_override_flag,'Y','T',
'S')),
start_date_active = x_start_date_active,
end_date_active = x_end_date_active
WHERE document_id = p_document_id;
UPDATE fnd_documents_tl
SET app_source_version = 'SO_R10',
description = x_name,
doc_attribute_category = x_context,
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
WHERE document_id = p_document_id
AND language = fnd_global.current_language;
UPDATE fnd_documents_short_text
SET app_source_version = 'SO_R10',
short_text = substr (x_note, 1, 1998)
WHERE media_id = x_media_id;
UPDATE fnd_documents_long_text
SET app_source_version = 'SO_R10',
long_text = x_note
WHERE media_id = x_media_id;
PROCEDURE delete_document (
p_document_id NUMBER
) IS
x_media_id NUMBER;
DELETE FROM fnd_documents
WHERE document_id = p_document_id;
DELETE FROM fnd_documents_tl
WHERE document_id = p_document_id;
DELETE FROM fnd_documents_short_text
WHERE media_id = x_media_id;
DELETE FROM fnd_documents_long_text
WHERE media_id = x_media_id;
PROCEDURE insert_attached_document (
p_creation_date DATE,
p_created_by NUMBER,
p_note_id NUMBER,
p_usage_id NUMBER,
p_automatically_added_flag VARCHAR2,
p_header_id NUMBER,
p_line_id NUMBER,
p_program_application_id NUMBER,
p_program_id NUMBER,
p_program_update_date DATE,
p_request_id NUMBER,
p_sequence_number NUMBER,
p_context VARCHAR2,
p_attribute1 VARCHAR2,
p_attribute2 VARCHAR2,
p_attribute3 VARCHAR2,
p_attribute4 VARCHAR2,
p_attribute5 VARCHAR2,
p_attribute6 VARCHAR2,
p_attribute7 VARCHAR2,
p_attribute8 VARCHAR2,
p_attribute9 VARCHAR2,
p_attribute10 VARCHAR2,
p_attribute11 VARCHAR2,
p_attribute12 VARCHAR2,
p_attribute13 VARCHAR2,
p_attribute14 VARCHAR2,
p_attribute15 VARCHAR2,
p_app_source_version VARCHAR2,
p_attached_document_id IN OUT NUMBER
) IS
x_document_id NUMBER;
SELECT document_id
INTO x_document_id
FROM so_notes
WHERE note_id = p_note_id;
SELECT fnd_attached_documents_s.nextval
INTO p_attached_document_id
FROM dual;
INSERT INTO fnd_attached_documents (
attached_document_id, document_id,
creation_date, created_by,
last_update_date, last_updated_by,
last_update_login, seq_num,
entity_name, pk1_value,
pk2_value,
program_application_id, program_id,
program_update_date, request_id,
automatically_added_flag, attribute_category,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11, attribute12,
attribute13, attribute14,
attribute15, app_source_version )
SELECT
p_attached_document_id, x_document_id,
p_creation_date, p_created_by,
sysdate, 1,
1, p_sequence_number,
x_entity_name, to_char(p_header_id),
to_char(p_line_id),
p_program_application_id, p_program_id,
p_program_update_date, p_request_id,
p_automatically_added_flag, p_context,
p_attribute1, p_attribute2,
p_attribute3, p_attribute4,
p_attribute5, p_attribute6,
p_attribute7, p_attribute8,
p_attribute9, p_attribute10,
p_attribute11, p_attribute12,
p_attribute13, p_attribute14,
p_attribute15, p_app_source_version
FROM dual;
PROCEDURE update_attached_document (
p_attached_document_id NUMBER,
p_creation_date DATE,
p_created_by NUMBER,
p_note_id NUMBER,
p_usage_id NUMBER,
p_automatically_added_flag VARCHAR2,
p_header_id NUMBER,
p_line_id NUMBER,
p_program_application_id NUMBER,
p_program_id NUMBER,
p_program_update_date DATE,
p_request_id NUMBER,
p_sequence_number NUMBER,
p_context VARCHAR2,
p_attribute1 VARCHAR2,
p_attribute2 VARCHAR2,
p_attribute3 VARCHAR2,
p_attribute4 VARCHAR2,
p_attribute5 VARCHAR2,
p_attribute6 VARCHAR2,
p_attribute7 VARCHAR2,
p_attribute8 VARCHAR2,
p_attribute9 VARCHAR2,
p_attribute10 VARCHAR2,
p_attribute11 VARCHAR2,
p_attribute12 VARCHAR2,
p_attribute13 VARCHAR2,
p_attribute14 VARCHAR2,
p_attribute15 VARCHAR2,
p_app_source_version VARCHAR2
) IS
x_document_id NUMBER;
SELECT document_id
INTO x_document_id
FROM so_notes
WHERE note_id = p_note_id;
UPDATE fnd_attached_documents
SET last_update_date = SYSDATE,
seq_num = p_sequence_number,
document_id = x_document_id,
attribute_category = p_context,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15,
app_source_version = 'SO_R10'
WHERE attached_document_id = p_attached_document_id;
PROCEDURE delete_attached_document (
p_attached_document_id NUMBER
) IS
BEGIN
DELETE FROM fnd_attached_documents
WHERE attached_document_id = p_attached_document_id;
PROCEDURE insert_category (
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_name VARCHAR2,
p_description VARCHAR2,
p_start_date_active DATE,
p_end_date_active DATE,
p_context VARCHAR2,
p_attribute1 VARCHAR2,
p_attribute2 VARCHAR2,
p_attribute3 VARCHAR2,
p_attribute4 VARCHAR2,
p_attribute5 VARCHAR2,
p_attribute6 VARCHAR2,
p_attribute7 VARCHAR2,
p_attribute8 VARCHAR2,
p_attribute9 VARCHAR2,
p_attribute10 VARCHAR2,
p_attribute11 VARCHAR2,
p_attribute12 VARCHAR2,
p_attribute13 VARCHAR2,
p_attribute14 VARCHAR2,
p_attribute15 VARCHAR2,
p_app_source_version VARCHAR2,
p_category_id IN OUT NUMBER
) IS
attach_function_id NUMBER;
SELECT fnd_document_categories_s.nextval
INTO p_category_id
FROM dual;
INSERT INTO fnd_document_categories (
category_id,
application_id, creation_date,
created_by, last_update_date,
last_updated_by, last_update_login,
name,
start_date_active, end_date_active,
default_datatype_id,
attribute_category,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11, attribute12,
attribute13, attribute14,
attribute15)
SELECT
p_category_id,
300, p_creation_date,
p_created_by, p_last_update_date,
p_last_updated_by, p_last_update_login,
p_name,
p_start_date_active, p_end_date_active,
1,
p_context,
p_attribute1, p_attribute2,
p_attribute3, p_attribute4,
p_attribute5, p_attribute6,
p_attribute7, p_attribute8,
p_attribute9, p_attribute10,
p_attribute11, p_attribute12,
p_attribute13, p_attribute14,
p_attribute15
FROM dual;
INSERT INTO fnd_document_categories_tl (
category_id, language,
name, user_name,
creation_date,
created_by, last_update_date,
last_updated_by, last_update_login,
app_source_version, source_lang )
SELECT
p_category_id, fnd_global.current_language,
p_name, p_name,
p_creation_date,
p_created_by, p_last_update_date,
p_last_updated_by, p_last_update_login,
p_app_source_version, fnd_global.current_language
FROM dual;
SELECT attachment_function_id
INTO attach_function_id
FROM fnd_attachment_functions
WHERE function_name = 'OEXOEMOE'
AND function_type = 'O';
INSERT INTO fnd_doc_category_usages (
doc_category_usage_id,
category_id,
attachment_function_id, enabled_flag,
creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
VALUES (
fnd_doc_category_usages_s.nextval,
p_category_id,
attach_function_id, 'Y',
SYSDATE, 1,
SYSDATE, 1, 1);
PROCEDURE update_category (
p_category_id NUMBER,
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_name VARCHAR2,
p_description VARCHAR2,
p_start_date_active DATE,
p_end_date_active DATE,
p_context VARCHAR2,
p_attribute1 VARCHAR2,
p_attribute2 VARCHAR2,
p_attribute3 VARCHAR2,
p_attribute4 VARCHAR2,
p_attribute5 VARCHAR2,
p_attribute6 VARCHAR2,
p_attribute7 VARCHAR2,
p_attribute8 VARCHAR2,
p_attribute9 VARCHAR2,
p_attribute10 VARCHAR2,
p_attribute11 VARCHAR2,
p_attribute12 VARCHAR2,
p_attribute13 VARCHAR2,
p_attribute14 VARCHAR2,
p_attribute15 VARCHAR2,
p_app_source_version VARCHAR2
) IS
BEGIN
UPDATE fnd_document_categories
SET last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
name = p_name,
start_date_active = p_start_date_active,
end_date_active = p_end_date_active,
attribute_category = p_context,
attribute1 = p_attribute1,
attribute2 = p_attribute2,
attribute3 = p_attribute3,
attribute4 = p_attribute4,
attribute5 = p_attribute5,
attribute6 = p_attribute6,
attribute7 = p_attribute7,
attribute8 = p_attribute8,
attribute9 = p_attribute9,
attribute10 = p_attribute10,
attribute11 = p_attribute11,
attribute12 = p_attribute12,
attribute13 = p_attribute13,
attribute14 = p_attribute14,
attribute15 = p_attribute15
WHERE category_id = p_category_id;
UPDATE fnd_document_categories_tl
SET last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
name = p_name,
user_name = p_name,
app_source_version = 'SO_R10'
WHERE category_id = p_category_id;
PROCEDURE insert_usage (
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_name VARCHAR2,
p_user_name VARCHAR2,
p_category_id NUMBER,
p_app_source_version VARCHAR2,
p_usage_id IN OUT NUMBER
) IS
CURSOR c IS
SELECT 1
FROM so_note_usages
WHERE name = substr(p_user_name,1,15);
SELECT so_note_usages_s.nextval
INTO p_usage_id
FROM dual;
INSERT INTO so_note_usages (
usage_id, creation_date,
created_by, last_update_date,
last_updated_by, last_update_login,
name, description,
start_date_active, end_date_active,
context,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11, attribute12,
attribute13, attribute14,
attribute15,
category_id, app_source_version )
SELECT
p_usage_id, p_creation_date,
p_created_by, p_last_update_date,
p_last_updated_by, p_last_update_login,
SUBSTR(p_user_name,1,15), SUBSTR(p_user_name,1,80),
fdc.start_date_active, fdc.end_date_active,
fdc.attribute_category,
fdc.attribute1, fdc.attribute2,
fdc.attribute3, fdc.attribute4,
fdc.attribute5, fdc.attribute6,
fdc.attribute7, fdc.attribute8,
fdc.attribute9, fdc.attribute10,
fdc.attribute11, fdc.attribute12,
fdc.attribute13, fdc.attribute14,
fdc.attribute15,
p_category_id, p_app_source_version
FROM fnd_document_categories fdc
WHERE category_id = p_category_id;
PROCEDURE update_usage (
p_category_id NUMBER,
p_creation_date DATE,
p_created_by NUMBER,
p_last_update_date DATE,
p_last_updated_by NUMBER,
p_last_update_login NUMBER,
p_name VARCHAR2,
p_user_name VARCHAR2,
p_app_source_version VARCHAR2
) IS
x_start_date_active DATE;
SELECT 1
FROM so_note_usages
WHERE name = substr(p_user_name,1,15)
AND category_id <> p_category_id;
SELECT start_date_active, end_date_active,
attribute_category,
attribute1, attribute2,
attribute3, attribute4,
attribute5, attribute6,
attribute7, attribute8,
attribute9, attribute10,
attribute11, attribute12,
attribute13, attribute14,
attribute15
INTO
x_start_date_active, x_end_date_active,
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
FROM fnd_document_categories
WHERE category_id = p_category_id;
UPDATE so_note_usages
SET last_update_date = p_last_update_date,
last_updated_by = p_last_updated_by,
last_update_login = p_last_update_login,
name = SUBSTR(p_user_name,1,15),
start_date_active = x_start_date_active,
end_date_active = x_end_date_active,
context = x_attribute_category,
attribute1 = x_attribute1,
attribute2 = x_attribute2,
attribute3 = x_attribute3,
attribute4 = x_attribute4,
attribute5 = x_attribute5,
attribute6 = x_attribute6,
attribute7 = x_attribute7,
attribute8 = x_attribute8,
attribute9 = x_attribute9,
attribute10 = x_attribute10,
attribute11 = x_attribute11,
attribute12 = x_attribute12,
attribute13 = x_attribute13,
attribute14 = x_attribute14,
attribute15 = x_attribute15,
app_source_version = 'SO_R10'
WHERE category_id = p_category_id;