Various files are attached in the documents like PO, Requisition, Invoice etc. If an attachment is a text type then it is possible to print the content of the attachment .
One of the example various PO terms and conditions are added in the PO header or line level. The following script will help print in the report.
/* Formatted on 2013/01/11 23:36 (Formatter Plus v4.8.8) */
DECLARE
CURSOR cur_file_info (i_hdr_id NUMBER)
IS
SELECT DISTINCT seq_num, media_id
FROM fnd_attached_docs_form_vl
WHERE pk1_value = i_hdr_id
AND category_description = 'To Supplier'
AND datatype_name = 'File'
ORDER BY seq_num;
BEGIN
BEGIN
l_file_content := NULL;
FOR get_file_content IN cur_file_info (:po_header_id)
LOOP
SELECT file_id, file_name
INTO l_file_id, l_file_name
FROM fnd_lobs
WHERE file_content_type = 'text/plain'
AND file_id = get_file_content.media_id;
populate_file_content (l_file_id, l_file_content);
END LOOP;
END;
END;
PROCEDURE populate_file_content (i_file_id NUMBER, i_file_content OUT VARCHAR2)
IS
l_blob BLOB;
l_clob CLOB := EMPTY_CLOB ();
l_src_offset NUMBER := 1;
l_dest_offset NUMBER := 1;
l_blob_csid INTEGER := 0;
v_lang_context INTEGER := 0;
l_warning NUMBER;
l_amount BINARY_INTEGER;
l_buffer_size CONSTANT BINARY_INTEGER := 32767;
l_buffer VARCHAR2 (32767) := NULL;
--l_file_content VARCHAR2 (32767) := NULL;
l_offset NUMBER := 1;
BEGIN
l_buffer := NULL;
DBMS_LOB.createtemporary (l_blob, TRUE);
DBMS_LOB.createtemporary (l_clob, TRUE);
SELECT file_data
INTO l_blob
FROM fnd_lobs
WHERE file_id = i_file_id;
IF DBMS_LOB.getlength (l_blob) > 0
THEN
DBMS_LOB.converttoclob (l_clob,
l_blob,
DBMS_LOB.getlength (l_blob),
l_dest_offset,
l_src_offset,
1,
v_lang_context,
l_warning
);
END IF;
l_amount := l_buffer_size;
WHILE l_amount >= l_buffer_size
LOOP
BEGIN
DBMS_LOB.READ (lob_loc => l_clob,
amount => l_amount,
offset => l_offset,
buffer => l_buffer
);
l_buffer := REPLACE (l_buffer, CHR (13), '');
l_offset := l_offset + l_amount;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
i_file_content := l_buffer;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
One of the example various PO terms and conditions are added in the PO header or line level. The following script will help print in the report.
/* Formatted on 2013/01/11 23:36 (Formatter Plus v4.8.8) */
DECLARE
CURSOR cur_file_info (i_hdr_id NUMBER)
IS
SELECT DISTINCT seq_num, media_id
FROM fnd_attached_docs_form_vl
WHERE pk1_value = i_hdr_id
AND category_description = 'To Supplier'
AND datatype_name = 'File'
ORDER BY seq_num;
BEGIN
BEGIN
l_file_content := NULL;
FOR get_file_content IN cur_file_info (:po_header_id)
LOOP
SELECT file_id, file_name
INTO l_file_id, l_file_name
FROM fnd_lobs
WHERE file_content_type = 'text/plain'
AND file_id = get_file_content.media_id;
populate_file_content (l_file_id, l_file_content);
END LOOP;
END;
END;
PROCEDURE populate_file_content (i_file_id NUMBER, i_file_content OUT VARCHAR2)
IS
l_blob BLOB;
l_clob CLOB := EMPTY_CLOB ();
l_src_offset NUMBER := 1;
l_dest_offset NUMBER := 1;
l_blob_csid INTEGER := 0;
v_lang_context INTEGER := 0;
l_warning NUMBER;
l_amount BINARY_INTEGER;
l_buffer_size CONSTANT BINARY_INTEGER := 32767;
l_buffer VARCHAR2 (32767) := NULL;
--l_file_content VARCHAR2 (32767) := NULL;
l_offset NUMBER := 1;
BEGIN
l_buffer := NULL;
DBMS_LOB.createtemporary (l_blob, TRUE);
DBMS_LOB.createtemporary (l_clob, TRUE);
SELECT file_data
INTO l_blob
FROM fnd_lobs
WHERE file_id = i_file_id;
IF DBMS_LOB.getlength (l_blob) > 0
THEN
DBMS_LOB.converttoclob (l_clob,
l_blob,
DBMS_LOB.getlength (l_blob),
l_dest_offset,
l_src_offset,
1,
v_lang_context,
l_warning
);
END IF;
l_amount := l_buffer_size;
WHILE l_amount >= l_buffer_size
LOOP
BEGIN
DBMS_LOB.READ (lob_loc => l_clob,
amount => l_amount,
offset => l_offset,
buffer => l_buffer
);
l_buffer := REPLACE (l_buffer, CHR (13), '');
l_offset := l_offset + l_amount;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
END LOOP;
i_file_content := l_buffer;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
Thanks again, very very useful for my job..
ReplyDeleteSir, I am Shyamsundar From MCC. Sir can you help one problem like i want upload word file and download the same file using forms. how to do this, please help me.
ReplyDelete