Send PO communication PDFs manually through Pl/Sql API

Query to find file attachment id

select * from FND_LOBS fl,FND_DOCUMENTS fd,FND_ATTACHED_DOCUMENTS fad
where fl.file_id = fd.media_id
and fd.document_id= fad.document_id
and fad.pk1_value=<po header id>
and fad.pk2_value=<rev num>


Identify what POs are failed in workflow notifications table , for whom you should send pdfs manually as a workaround, change the parameters as required.

SELECT *
from wf_notifications
where Mail_Status = 'MAIL'
and message_name ='EMAIL_PO_PDF'
and status= 'OPEN'
and sent_Date > (Sysdate - 2)
and recipient_role not like 'PO_OUTPUT%'
order by Notification_id;

Use the sample below stub to retrieve blob object and save as file in any oracle directory

declare
v_blob BLOB;
blob_length INTEGER;
out_file UTL_FILE.FILE_TYPE;
v_buffer RAW(32767);
chunk_size BINARY_INTEGER := 32767;
blob_position INTEGER := 1;
v_file_name varchar2(240);

BEGIN

SELECT FILE_DATA,file_name INTO v_blob,v_file_name FROM fnd_lobs WHERE FILE_ID = 12345;
blob_length:=DBMS_LOB.GETLENGTH(v_blob);

out_file := UTL_FILE.FOPEN ('oracle directory name', v_file_name, 'wb', chunk_size);

WHILE blob_position <= blob_length LOOP
IF blob_position + chunk_size - 1 > blob_length THEN
chunk_size := blob_length - blob_position + 1;
END IF;
DBMS_LOB.READ(v_blob, chunk_size, blob_position, v_buffer);
UTL_FILE.PUT_RAW(out_file, v_buffer, TRUE);
blob_position := blob_position + chunk_size;
END LOOP;

UTL_FILE.FCLOSE (out_file);
END;
/

Then use an utl_smtp api to send the file attachment to respective supplier email, email address you get in wf_notifications table. Query the recipient role with wf_roles to derive email address.

DECLARE
   v_From       VARCHAR2(80) := 'ab.cs@nergy.com';
   v_Recipient  VARCHAR2(80) := 'ni.ka@ergy.com';
   v_Subject    VARCHAR2(80) := 'test subject';
   v_Mail_Host  VARCHAR2(30) := 'host name';        -- take from v$instance
   v_Mail_Conn  utl_smtp.Connection;
   crlf         VARCHAR2(2)  := chr(13)||chr(10);
BEGIN
  v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

  utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

  utl_smtp.Mail(v_Mail_Conn, v_From);

  utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

  utl_smtp.Data(v_Mail_Conn,
    'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
    'From: '   || v_From || crlf ||
    'Subject: '|| v_Subject || crlf ||
    'To: '     || v_Recipient || crlf ||

    'MIME-Version: 1.0'|| crlf ||    -- Use MIME mail standard
    'Content-Type: multipart/mixed;'|| crlf ||
    ' boundary="-----SECBOUND"'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/plain;'|| crlf ||
    'Content-Transfer_Encoding: 7bit'|| crlf ||
    crlf ||
    'some message text'|| crlf ||    -- Message body
    'more message text'|| crlf ||
    crlf ||

    '-------SECBOUND'|| crlf ||
    'Content-Type: text/pdf;'|| crlf ||
    ' name="abc.pdf"'|| crlf ||
    'Content-Transfer_Encoding: 8bit'|| crlf ||
    'Content-Disposition: attachment;'|| crlf ||
    ' filename="/ass/ddd/fff/asdadS.pdf"'|| crlf ||
    crlf ||
    'pdf,file,attachement'|| crlf ||    -- Content of attachment
    crlf ||

    '-------SECBOUND--'            -- End MIME mail
  );

  utl_smtp.Quit(v_mail_conn);
EXCEPTION
  WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
    raise_application_error(-20000, 'Unable to send mail', TRUE);
   
END;
/