How to copy attachment files from One record to another

Test Procedure for Copy Attachments(Short, Long, File, URL)

PROCEDURE copy_attachment (p_sow_number VARCHAR2)
IS
CURSOR c_long (l_sow_number VARCHAR2)
IS
SELECT ad.seq_num, dct.category_id, dt.description, dat.datatype_id,
dlt.long_text, af.function_name,
det.data_object_code entity_name, ad.pk1_value, d.media_id
FROM fnd_document_datatypes dat,
fnd_document_entities_tl det,
fnd_documents_tl dt,
fnd_documents d,
fnd_document_categories_tl dct,
fnd_attached_documents ad,
fnd_documents_long_text dlt,
fnd_doc_category_usages dcu,
fnd_attachment_functions af
WHERE d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dct.category_id = d.category_id
AND d.datatype_id = dat.datatype_id
AND ad.entity_name = det.data_object_code
AND dlt.media_id = d.media_id
AND dcu.category_id = d.category_id
AND dcu.attachment_function_id = af.attachment_function_id
AND function_name = ‘xxx_DTL_FUN’
AND dcu.enabled_flag = ‘Y’
AND dat.NAME = ‘LONG_TEXT’
AND pk1_value = l_sow_number;

CURSOR c_short (l_sow_number VARCHAR2)
IS
SELECT ad.seq_num, dct.category_id, dt.description, dat.datatype_id,
dlt.short_text, af.function_name,
det.data_object_code entity_name, ad.pk1_value, d.media_id
FROM fnd_document_datatypes dat,
fnd_document_entities_tl det,
fnd_documents_tl dt,
fnd_documents d,
fnd_document_categories_tl dct,
fnd_attached_documents ad,
fnd_documents_short_text dlt,
fnd_doc_category_usages dcu,
fnd_attachment_functions af
WHERE d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dct.category_id = d.category_id
AND d.datatype_id = dat.datatype_id
AND ad.entity_name = det.data_object_code
AND dlt.media_id = d.media_id
AND dcu.category_id = d.category_id
AND dcu.attachment_function_id = af.attachment_function_id
AND function_name = ‘xxx_DTL_FUN’
AND dcu.enabled_flag = ‘Y’
AND dat.NAME = ‘SHORT_TEXT’
AND pk1_value = l_sow_number;

CURSOR c_file (l_sow_number VARCHAR2)
IS
SELECT ad.seq_num, dct.category_id, dt.description, dat.datatype_id,
af.function_name, ad.entity_name, ad.pk1_value, d.media_id,
l.file_name
FROM fnd_document_datatypes dat,
fnd_document_entities_tl det,
fnd_documents_tl dt,
fnd_documents d,
fnd_document_categories_tl dct,
fnd_attached_documents ad,
fnd_lobs l,
fnd_doc_category_usages dcu,
fnd_attachment_functions af
WHERE d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dct.category_id = d.category_id
AND d.datatype_id = dat.datatype_id
AND ad.entity_name = det.data_object_code
AND l.file_id = d.media_id
AND dcu.category_id = d.category_id
AND dcu.attachment_function_id = af.attachment_function_id
AND function_name = ‘xxx_DTL_FUN’
AND dat.NAME = ‘FILE’
AND dcu.enabled_flag = ‘Y’
AND pk1_value = l_sow_number;

CURSOR c_url (l_sow_number VARCHAR2)
IS
SELECT ad.seq_num, dct.category_id, dt.description, dat.datatype_id,
af.function_name, ad.entity_name, ad.pk1_value, d.media_id,
d.url, d.file_name
FROM fnd_document_datatypes dat,
fnd_document_entities_tl det,
fnd_documents_tl dt,
fnd_documents d,
fnd_document_categories_tl dct,
fnd_attached_documents ad,
fnd_doc_category_usages dcu,
fnd_attachment_functions af
WHERE d.document_id = ad.document_id
AND dt.document_id = d.document_id
AND dct.category_id = d.category_id
AND d.datatype_id = dat.datatype_id
AND ad.entity_name = det.data_object_code
AND dcu.category_id = d.category_id
AND dcu.attachment_function_id = af.attachment_function_id
AND dat.NAME = ‘WEB_PAGE’
AND dcu.enabled_flag = ‘Y’
AND function_name = ‘xxx_DTL_FUN’
AND pk1_value = l_sow_number;
BEGIN
FOR rec_long IN c_long (p_sow_number)
LOOP
fnd_webattch.add_attachment
(seq_num => rec_long.seq_num,
category_id => rec_long.category_id,
document_description => rec_long.description,
datatype_id => rec_long.datatype_id,
text => rec_long.long_text,
file_name => NULL,
url => NULL,
function_name => rec_long.function_name,
entity_name => rec_long.entity_name,
pk1_value => :header_block.sow_number,
–rec_long.pk1_value,
pk2_value => NULL,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => rec_long.media_id,
user_id => :header_block.created_by,
usage_type => ‘O’
);
END LOOP;

FOR rec_short IN c_short (p_sow_number)
LOOP
fnd_webattch.add_attachment
(seq_num => rec_short.seq_num,
category_id => rec_short.category_id,
document_description => rec_short.description,
datatype_id => rec_short.datatype_id,
text => rec_short.short_text,
file_name => NULL,
url => NULL,
function_name => rec_short.function_name,
entity_name => rec_short.entity_name,
pk1_value => :header_block.sow_number,
–rec_short.pk1_value,
pk2_value => NULL,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => rec_short.media_id,
user_id => :header_block.created_by,
usage_type => ‘O’
);
END LOOP;

FOR rec_file IN c_file (p_sow_number)
LOOP
fnd_webattch.add_attachment
(seq_num => rec_file.seq_num,
category_id => rec_file.category_id,
document_description => rec_file.description,
datatype_id => rec_file.datatype_id,
text => NULL,
file_name => rec_file.file_name,
url => NULL,
function_name => rec_file.function_name,
entity_name => rec_file.entity_name,
pk1_value => :header_block.sow_number,
–rec_short.pk1_value,
pk2_value => NULL,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => rec_file.media_id,
user_id => :header_block.created_by,
usage_type => ‘O’
);
END LOOP;

FOR rec_url IN c_url (p_sow_number)
LOOP
fnd_webattch.add_attachment
(seq_num => rec_url.seq_num,
category_id => rec_url.category_id,
document_description => rec_url.description,
datatype_id => rec_url.datatype_id,
text => NULL,
file_name => rec_url.file_name,
url => rec_url.url,
function_name => rec_url.function_name,
entity_name => rec_url.entity_name,
pk1_value => :header_block.sow_number,
–rec_short.pk1_value,
pk2_value => NULL,
pk3_value => NULL,
pk4_value => NULL,
pk5_value => NULL,
media_id => rec_url.media_id,
user_id => :header_block.created_by,
usage_type => ‘O’
);
END LOOP;
END;