Script to update the PO Attachment from Back End
Introduction:
Script to update the Purchase Order Attachment from the back end.
Cause of the issue:
The Purchase Order Attachments are not loaded for the Migrated POs from one unit to another unit.
How do we solve:
The below is the Script to load the attachment of the Purchase order from one unit to another unit.
Script:
DECLARE
l_rowid ROWID;
l_attached_document_id NUMBER;
l_document_id NUMBER;
l_media_id NUMBER;
l_category_id NUMBER;
l_pk1_value fnd_attached_documents.pk1_value%TYPE;
l_description fnd_documents_tl.description%TYPE;
l_title fnd_documents_tl.title%TYPE;
l_filename VARCHAR2 (240);
l_file_path VARCHAR2 (240);
l_seq_num NUMBER;
l_blob_data BLOB;
l_blob BLOB;
l_bfile BFILE;
l_byte NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
l_file_type VARCHAR2 (20);
x_blob BLOB;
blob_length INTEGER;
l_entity_name VARCHAR2 (100) := ‘PO_HEADERS’;
l_category_name VARCHAR2 (100) := ‘To Approver’;
p_from_header_id NUMBER :=100; — From PO Header ID
p_to_header_id NUMBER :=101: — To PO Header ID (Load the attach from po _header_id)
—-Must be defined before or use existing ones.
CURSOR c_att
IS
(SELECT i.po_header_id, d.media_id, i.segment1, i.attribute9,
dtl.description, dtl.document_id, dtl.title, fl.file_name,
fl.file_content_type file_type, fl.file_data file_content
FROM apps.po_headers_all i,
apps.ap_suppliers v,
apps.ap_supplier_sites_all ps,
apps.fnd_attached_documents fad,
apps.fnd_documents d,
apps.fnd_documents_tl dtl,
fnd_lobs fl
WHERE i.vendor_id = v.vendor_id
AND i.vendor_site_id = ps.vendor_site_id
AND i.po_header_id = fad.pk1_value
AND fad.document_id = dtl.document_id
AND dtl.document_id = d.document_id
AND fad.entity_name = ‘PO_HEADERS’
AND i.po_header_id = p_from_header_id
AND fl.file_id = d.media_id);
BEGIN
—Enter USER_ID,RESP_ID,RESP_APPL_ID
l_pk1_value := p_to_header_id; –p_po_hdr_id;
fnd_global.apps_initialize
(user_id => fnd_profile.VALUE
(‘USER_ID’),
resp_id => fnd_profile.VALUE
(‘RESP_ID’),
resp_appl_id => fnd_profile.VALUE
(‘RESP_APPL_ID’),
security_group_id => 0
);
FOR i IN c_att
LOOP
BEGIN
SELECT fnd_documents_s.NEXTVAL
INTO l_document_id
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Deriving l_document_id Error’
|| ‘ – ‘
|| SQLERRM
);
END;
BEGIN
SELECT fnd_attached_documents_s.NEXTVAL
INTO l_attached_document_id
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Deriving l_attached_document_id Error’
|| ‘ – ‘
|| SQLERRM
);
END;
BEGIN
SELECT NVL (MAX (seq_num), 0) + 10
INTO l_seq_num
FROM fnd_attached_documents
WHERE pk1_value = l_pk1_value –p_po_hdr_id
AND entity_name = l_entity_name;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Deriving l_seq_num Error’ || ‘ – ‘ || SQLERRM
);
END;
—- Select User_id
BEGIN
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE user_name = ‘ERPSUPP’;
—Username who will be uploading file
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Deriving l_fnd_user_id Error’
|| ‘ – ‘
|| SQLERRM
);
END;
—- Get Data type id for Short Text types of attachments
BEGIN
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE NAME = ‘FILE’;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Deriving l_short_datatype_id Error’
|| ‘ – ‘
|| SQLERRM
);
END;
— Select Category id for Attachments
BEGIN
SELECT category_id
INTO l_category_id
FROM apps.fnd_document_categories_vl
WHERE user_name = l_category_name;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Deriving l_category_id Error’
|| ‘ – ‘
|| SQLERRM
);
END;
—- Select nexvalues of document id, attached document id and
BEGIN
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL
INTO l_document_id,
l_attached_document_id
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
‘Deriving l_document_id, l_attached_document_id Error’
|| ‘ – ‘
|| SQLERRM
);
END;
BEGIN
SELECT MAX (file_id) + 1
INTO l_media_id
FROM fnd_lobs;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Deriving l_media_id Error’ || ‘ – ‘ || SQLERRM
);
END;
BEGIN
INSERT INTO fnd_lobs
(file_id, file_name, file_content_type, upload_date,
expiration_date, program_name, program_tag, file_data,
LANGUAGE, oracle_charset, file_format
)
VALUES (l_media_id, i.file_name, i.file_type, SYSDATE,
NULL, ‘FNDATTCH’, NULL, i.file_content,
—EMPTY_BLOB (),
‘US’, ‘UTF8’, ‘binary’
)
RETURNING file_data
INTO x_blob;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Error While Inserting into FND_LOBS’
|| ‘ – ‘
|| SQLERRM
);
END;
DBMS_OUTPUT.put_line (‘FND_LOBS File Id Created is ‘ || l_media_id);
COMMIT;
BEGIN
fnd_documents_pkg.insert_row
(x_rowid => l_rowid,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => fnd_profile.VALUE
(‘USER_ID’),
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_profile.VALUE
(‘USER_ID’),
x_last_update_login => fnd_profile.VALUE
(‘LOGIN_ID’),
x_datatype_id => l_short_datatype_id,
x_security_id => 105,
—Security ID defined in your Attchments, Usaully SOB ID/ORG_ID
x_publish_flag => ‘Y’,
—This flag allow the file to share across multiple organization
x_category_id => l_category_id,
x_security_type => 1,
x_usage_type => ‘O’,
x_language => ‘US’,
x_description => i.description,
x_file_name => i.file_name,
x_media_id => l_media_id,
x_title => i.title
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Error in FND_DOCUMENTS_PKG.INSERT_ROW’
|| ‘ – ‘
|| SQLERRM
);
END;
COMMIT;
— Description informations will be stored in below table based on languages.
BEGIN
fnd_documents_pkg.insert_tl_row
(x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => fnd_profile.VALUE
(‘USER_ID’),
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_profile.VALUE
(‘USER_ID’),
x_last_update_login => fnd_profile.VALUE
(‘LOGIN_ID’),
–828296,–fnd_profile.VALUE(‘LOGIN_ID’),
x_language => ‘US’,
x_description => i.description,
x_title => i.title
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Error in FND_DOCUMENTS_PKG.INSERT_TL_ROW’
|| ‘ – ‘
|| SQLERRM
);
END;
COMMIT;
BEGIN
fnd_attached_documents_pkg.insert_row
(x_rowid => l_rowid,
x_attached_document_id => l_attached_document_id,
x_document_id => l_document_id,
x_creation_date => SYSDATE,
x_created_by => fnd_profile.VALUE
(‘USER_ID’),
x_last_update_date => SYSDATE,
x_last_updated_by => fnd_profile.VALUE
(‘USER_ID’),
x_last_update_login => fnd_profile.VALUE
(‘LOGIN_ID’),
–828296,–fnd_profile.VALUE(‘LOGIN_ID’),
x_seq_num => l_seq_num,
x_entity_name => l_entity_name,
x_column1 => NULL,
x_pk1_value => l_pk1_value,
–p_po_hdr_id,
x_pk2_value => NULL,
x_pk3_value => NULL,
x_pk4_value => NULL,
x_pk5_value => NULL,
x_automatically_added_flag => ‘N’,
x_datatype_id => l_short_datatype_id,
x_category_id => l_category_id,
x_security_type => 1,
x_security_id => 105,
—Security ID defined in your Attchments, Usaully SOB ID/ORG_ID
x_publish_flag => ‘Y’,
x_language => ‘US’,
x_description => i.description,
x_file_name => i.file_name,
x_media_id => l_media_id,
x_title => i.title
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
‘Error in FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW’
|| ‘ – ‘
|| SQLERRM
);
END;
COMMIT;
fnd_file.put_line (fnd_file.LOG, ‘MEDIA ID CREATED IS ‘ || l_media_id);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
‘Oracle Error in the attachment Program’
|| ‘ – ‘
|| SQLERRM
);
END;
