Tuesday, 20 February 2024

Uploading PO Attachments from EBS to FTP Server

 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

Uploading PO Attachments from EBS to FTP Server

 create or replace PROCEDURE xx_upload_po_attachment(errbuff out varchar2, retcode out number)  IS CURSOR cur_new_attmt IS    select ponumbe...