Sending an an attached document via email

Hi @rkierstead8 ,

I've created an event action that attaches a document to an email that is stored in document management in our database.  I can attach the code tomorrow and you can modify it to meet your requirements.  

Regards,

William Klotz

Hi @rkierstead8 

In COMMAND_SYS there are methods you can use to insert attachments to emails which are in CLOB form. Have a look in that API and you will see lot of useful methods to use in your case.

Thanks

Hi @rkierstead8 ,

Here’s the event action PL/SQL code I wrote about last night.   We use this to send an email with document attached for any document within document class 100 to a mailbox so another process can take the document attachment and place it onto our corporate website.

DECLARE
   mailto_     VARCHAR2(800) := 'flow.admin@domain.com;
   mailfrom_   VARCHAR2(800) := 'noreply@domain.com';
   file_name_  VARCHAR2(2000);
   file_names_ VARCHAR2(2000);
   doc_type_   VARCHAR2(2000);
   f_          VARCHAR2(2000);
   error_msg_  VARCHAR2(32000);
   rec_ Plsqlap_Record_API.type_record_;

   doc_class_  VARCHAR2(100) := '&DOC_CLASS';
   doc_no_     VARCHAR2(100) := '&DOC_NO';
   doc_rev_    VARCHAR2(100) := '&DOC_REV';
   doc_sheet_  VARCHAR2(100) := '&DOC_SHEET';

   doc_ref_    VARCHAR2(32000);
   message_    VARCHAR2(32000);   

   CURSOR get_doc_reference IS
      SELECT doc_object_desc
        FROM DOC_REFERENCE_OBJECT
      WHERE doc_class = '&DOC_CLASS'
           AND doc_no =  '&DOC_NO'
           AND lu_name = 'DocFolder';

BEGIN

   OPEN get_doc_reference;
   FETCH get_doc_reference INTO doc_ref_;
   IF get_doc_reference%FOUND THEN
         EDM_FILE_API.REFERENCE_EXIST(f_, doc_class_, doc_no_, doc_sheet_, doc_rev_,'VIEW');
         IF f_ = 'TRUE' THEN
            doc_type_ := 'VIEW';
         ELSE
            doc_type_ := 'ORIGINAL';
         END IF;

         file_name_ := EDM_FILE_API.GET_NEW_LOCAL_FILE_NAME(doc_class_, doc_no_, doc_sheet_, doc_rev_,doc_type_,'1');
         BATCH_TRANSFER_HANDLER_API.Download_From_DB(error_msg_, doc_class_, doc_no_, doc_sheet_, doc_rev_, doc_type_, 1, file_name_);

         message_ := 'Document No.: &DOC_NO' || CHR(13) || CHR(10) ||
                               'Title: &TITLE ' || CHR(13) || CHR(10);

         COMMAND_SYS.MAIL(
            FROM_USER_NAME_ => mailfrom_,
            TO_USER_NAME_ => mailto_,
            TEXT_ => message_,
            SUBJECT_ =>'&DOC_NO ' || doc_ref_ || ' &TITLE',
            ATTACH_ =>  file_name_ );
   END IF;
   CLOSE get_doc_reference;
END;

Regards,

William Klotz

@rkierstead8 

> but I’m not sure how to make the files attach to an email since they are BLOBs in our database and not files on an FTP due to SOX requirements.

It’s actually a good thing :), for you, that the document files are in the database since then they can be easily accessed by PL/SQL code, as seen in the example from William above.

While on the topic of SOX, if you are thinking about the file operations log that were once only available for documents stored in the database, it is now available for documents stored in FTP or Shared repositories as well (from Apps 9 or 10 I think).

Of course, the logging is better when the documents are kept in the database. If they are kept on an FTP server, for example, we cannot guarantee that a person will not access the files by logging in to the FTP server outside IFS so to speak.
 

Thanks everyone. I found a way to do what I needed before seeing William’s response, but I may still check it out.

I decided to create a directory on the MW server and export the BLOB to PDF and then use a second procedure to send the email. A Windows task will clean the folder after a while. This might not be the cleanest way, but it works. I’m still growing and trying to to understand certain things about Oracle and PL/SQL and my mentors aren’t really accessible anymore, so I can usually get things done, but might be unorthodox lol.

Thanks for all your help!

That's how you learn and grow, by experimenting and trying things out 🙂💪🏻