6 Replies Latest reply: Jan 31, 2013 3:44 AM by user6772286 RSS

    Workflow-Attaching pdf from Custom Concurrent Request to email notification

      I was wondering if anyone has done something similar before and maybe have any suggestions?
      I am trying to attach a .pdf file that is generated by a custom concurrent request report to an email notification.
      The .pdf is generated correctly and I can access the file but I’m having problems trying to attach it to the email notification
      I have tried to use Wf_Engine.SetItemAttrText and Wf_Engine.SetItemAttrDocument but are unable to get it to attach the file.
      Does anyone know where I can find an example how to use either of the above API's? and a detailed explanation of them?
      Should the attachment always be in FND_LOBS to be picked up or can it be picked up from the file system?
      What should the Workflow - Notification - Performer Value be set to, that is referencing the message with the Document Attribute?
      I'm unable to find a detailed explanation of how this can be done in the Oracle Workflow Developers Guide Release 2.6.4.
      Any reference material or guidance as to where an example can be found will be greatly appreciated
        • 1. Re: Workflow-Attaching pdf from Custom Concurrent Request to email notification
          I have also tried to send the file through Workflow but after puttin so much hardwork no result, so we gone for Alert and it is workig fine. If possible then you can also try for ALERT.

          Shishu Paul
          • 2. Re: Workflow-Attaching pdf from Custom Concurrent Request to email notification
            Workflow now completes with errors:
            Failed Activity: XX Print PDF Attachment
            Activity Type Notice
            Error Name WF_ERROR

            Error Message [WF_ERROR] ERROR_MESSAGE=3835: Error '-20002 - ORA-20002: [WFMLR_DOCUMENT_ERROR]' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK= Wf_Notification.GetAttrblob(1311318, XX_POPRINT_ATCH, text/html) WF_XML.GetAttachment(1311318, text/html) WF_XML.GetAttachments(1311318, http://aaa10dev01.local.com:8222/pls/DEV1, 4590) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 1311318) WF_XML.Generate(oracle.apps.wf.notification.send, 1311318) WF_XML.Generate(oracle.apps.wf.notification.send, 1311318) Wf_Event.setMessage(oracle.apps.wf.notification.send, 1311318, WF_XML.Generate) Wf_Event.dispatch_internal()

            Anyone seen something similar before?
            • 3. Re: Workflow-Attaching pdf from Custom Concurrent Request to email notification
              To resolve the WFMLR_DOCUMENT_ERROR - I restarted the Concurrent Managers:
              Workflow Agent Listener Service
              Workflow Mailer Service
              Workflow Document Web Services Service

              This problem seems to happen randomly when doing changes to the workflow or
              any database packages that the workflow is using, after a restart of the above,
              the workflow is working again

              I were also able to get the attachment working.
              My workflow submits a concurrent request, which produces a pdf,
              but by default this is a .out file in the Concurrent Requests output directory:

              I've setup a dummy printer for the concurrent request that is calling a shell script
              to copy the file from $APPLCSF/$APPLOUT/*$1*.out (where $1 is my request id) to a temp dir
              - at the moment: /usr/tmp because this dir is defined in the orainit utl_file_dir.
              once the file is in /usr/tmp you can access it with PLSQL
              (I have a database directory that points to /usr/tmp as well)
              and you can attach the file directly from the filesystem - else if you need to,
              you can read the file from the filesystem, insert it into a table and then attach it

              In the workflow the Message Attribute for the attachement, has:
              default Type: Constant

              Send me your email addr, and I will email you the generate_pdf procedure

              • 4. Re: Workflow-Attaching pdf from Custom Concurrent Request to email notification
                The PLSQL procedure that generates the PL/SQL BLOB documents must follow standard API formats and a sample is shown below.

                procedure GENERATE_PDF(document_id in varchar2,
                content_type in varchar2,
                document in out nocopy blob,
                document_type in out nocopy varchar2)
                l_docid pls_integer;
                l_filename varchar2(100);
                l_errmsg varchar2(100) := 'The Document is not found in the Database';
                l_bdoc blob;
                l_data_type varchar2(100);


                l_docid := to_number(document_id);

                dbms_lob.CreateTemporary(l_bdoc, FALSE, DBMS_LOB.Session);

                SELECT file_data
                into l_bdoc
                FROM fnd_lobs
                WHERE file_id= l_docid;

                dbms_lob.Copy(document, l_bdoc, dbms_lob.getLength(l_bdoc));

                when others then
                dbms_lob.WriteAppend(document, length(l_errmsg), l_errmsg);
                end GENERATE_PDF;


                You can use FND_LOBS or any other table to get the content. The content must be stored into the corresponding table before using it.

                The following is the PLSQL script for generating BINARY data for a file

                l_bfile BFILE;
                l_temp_blob blob;
                l_docid number :='1000';
                l_ctype varchar2(300) :='application/pdf';
                l_filename varchar2(50) :='testfile.pdf';
                dbms_lob.createtemporary(l_temp_blob, true, dbms_lob.session);

                l_bfile := BFILENAME('OEX_INPATH',l_filename);

                dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);

                dbms_lob.loadfromfile(l_temp_blob, l_bfile, dbms_lob.getlength(l_bfile));

                INSERT INTO fnd_lobs(file_id, file_name, file_content_type,file_data,file_format)
                VALUES( l_docid,l_filename,l_ctype,l_temp_blob,'binary');


                OEX_INPATH - this is a database directory, you need to copy the pdf file which you want to convert to BINARY to the location which this directory points
                l_filename - name of the file which need to be converted to BINARY format
                • 5. Re: Workflow-Attaching pdf from Custom Concurrent Request to email notification

                  I have a problem with attachments in workflow. Please help.
                  So, let's begin.

                  I have a workflow with attachments: Att1, Att2 ... etc. When I try to open this attachment which i did today, it opens normally, but when i try to open it tomorrow, or day after tomorrow - it fails, do not open and gives an error like this "You are not authorized to see this information.".

                  What can i do? Help.

                  Thanks, Yermek.

                  my email: ermektul@gmail.com
                  • 6. Re: Workflow-Attaching pdf from Custom Concurrent Request to email notification
                    Hi Coenraad
                    I have a similar requirement to you. Would you please be kind enough to email the generate_pdf procedure to me please?
                    My email add is a_jerald(at the rate of )yahoo(dot)com