create or replace PROCEDURE xx_upload_po_attachment(errbuff out varchar2, retcode out number)
IS
CURSOR cur_new_attmt IS
select ponumber,
creation_date,
file_name,
file_data,
file_length
from ( select pha.segment1 ponumber,pha.creation_date,
pha.segment1||'-'||fd.file_name file_name,
fl.file_data,dbms_lob.getlength(fl.file_data) file_length
from fnd_attached_documents fad,
po_headers_all pha,
fnd_documents fd,
fnd_lobs fl
where fad.entity_name in ('PO_HEADERS','PO_HEAD')
and pha.po_header_id = fad.pk1_value
and fd.document_id = fad.document_id
and fd.file_name is not null --which includes only attachments, no Long text or short text.
and fl.file_id = fd.media_id
--and pha.segment1 = '210031588'
and pha.revision_num = 0
and pha.authorization_status = 'APPROVED'
and pha.closed_code = 'OPEN'
and trunc(pha.approved_date) = trunc(sysdate)
UNION ALL
select pha.segment1 ponumber,pha.creation_date,
pha.segment1||'-'||pla.line_num||'-'||fd.file_name file_name,
fl.file_data,
dbms_lob.getlength(fl.file_data) file_length
from fnd_attached_documents fad,
po_headers_all pha,
po_lines_all pla,
fnd_documents fd,
fnd_lobs fl
where fad.entity_name in ('PO_LINES')
and pla.po_line_id = fad.pk1_value
and pha.po_header_id = pla.po_header_id
and fd.document_id = fad.document_id
and fd.file_name is not null --which includes only attachments, no Long text or short text.
and fl.file_id = fd.media_id
--and pha.segment1 = '210031588'
and pha.revision_num = 0
and pha.authorization_status = 'APPROVED'
and pha.closed_code = 'OPEN'
and trunc(pha.approved_date) = trunc(sysdate))
order by creation_date desc;
v_start NUMBER DEFAULT 1;
v_bytelen NUMBER DEFAULT 32000;
v_len_copy NUMBER;
v_raw_var RAW(32000);
v_output utl_file.file_type;
v_inv_file_name VARCHAR2(100);
v_position NUMBER;
v_first_rec BOOLEAN DEFAULT TRUE;
v_directory VARCHAR2(240);
BEGIN
v_position := 10;
select meaning
into v_directory
from fnd_lookups fl,v$instance vi
where lookup_type = 'UPLOAD_PO_ATTACHMENTS'
and upper(substr(lookup_code,1,6))like upper(substr(instance_name,1,6)) ;
FOR rec_inv IN cur_new_attmt
LOOP
BEGIN
v_inv_file_name := NULL;
v_inv_file_name := rec_inv.file_name;
v_position := 20;
-- define output directory AND OPEN THE file IN WRITE BYTE MODE
v_output := utl_file.fopen(v_directory,
v_inv_file_name,
'wb',
32760);
v_position := 30;
-- maximum size OF buffer parameter IS 32767 BEFORE
-- which you have TO flush your buffer
IF rec_inv.file_length < 32760
THEN
utl_file.put_raw(v_output,
rec_inv.file_data);
utl_file.fflush(v_output);
ELSE
v_position := 40;
v_start := 1;
v_bytelen := 32000;
v_len_copy := rec_inv.file_length;
WHILE v_start < rec_inv.file_length
AND v_bytelen > 0
LOOP
v_position := 50;
dbms_lob.READ(rec_inv.file_data,
v_bytelen,
v_start,
v_raw_var);
v_position := 60;
utl_file.put_raw(v_output,
v_raw_var);
v_position := 70;
utl_file.fflush(v_output);
v_start := v_start + v_bytelen;
v_len_copy := v_len_copy - v_bytelen;
IF v_len_copy < 32000
THEN
v_bytelen := v_len_copy;
END IF;
END LOOP;
v_position := 80;
utl_file.fclose(v_output);
END IF;
v_position := 90;
fnd_file.put_line(FND_FILE.LOG,' File_name :' || rec_inv.file_name);
dbms_output.put_line(' File_name :' || rec_inv.file_name);
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(FND_FILE.LOG,nvl(v_inv_file_name,'NA')|| ' Error : '|| substr(SQLERRM,1,100));
dbms_output.put_line(rpad(nvl(v_inv_file_name,'NA'),31)
|| rpad('ERROR', 21)
|| 'POSITION: '
|| v_position
|| 'Error :'
|| substr(SQLERRM,1,100));
END;
END LOOP;
END xx_upload_po_attachment;
No comments:
Post a Comment