1 Reply Latest reply on Oct 4, 2012 9:26 AM by Saroja Kandepuneni-Oracle

    Workflow binary attachment

    Kok Aik

      I'm having difficulty in getting the attachment to appear in the Workflow Notification Reference section. Appreciate if you can point out if i missed out anything.
      The environment is on, and workflow

      I'm able to set the CLOB document attribute but not the BLOB. Here are what i did:

      Define attachment attribute
      1. Type : Document
      Frame Target: Full Window
      Attach Content: Checked
      Source : Send

      2. Drag the attribute to the message

      3. Define an initialize procedure to set the workflow attribute
           wf_engine.setitemattrdocument ( itemtype => itemtype,
                                         itemkey => itemkey,
                                         aname => 'HEADER_ATTACHMENT',
                                         documentid => 'PLSQLBLOB:RESM_SARAPPRV.GET_HEADER_ATTACHMENT/' ||  to_char(x_file_id)); 
      However, it doesn't seem to execute the GET_HEADER_ATTACHMENT at all as there is no log display.

      Here is the GET_HEADER_ATTACHMENT procedure
      PROCEDURE get_header_attachment (
                document_id               in varchar2,
              display_type                    in varchar2,
              document                         in out     NOCOPY BLOB,
              document_type          in out     NOCOPY varchar2
                x_sar_header_id               number;
                x_module_name               VARCHAR2(100);
                x_file_id                               number;
                x_attachment                  BLOB;
                x_file_name                     varchar2(240) := null;
                x_file_content_type      fnd_lobs.file_content_type%TYPE   := NULL;
                x_length                              number;
                l_itemtype                             wf_items.item_type%TYPE;
                l_itemkey                              wf_items.item_key%TYPE;
                dbms_lob.createTemporary(x_attachment, FALSE, dbms_lob.call);
                x_module_name := 'get_header_attachment';
                l_itemtype := substr(document_id, 1, instr(document_id, ':') - 1);
                l_itemkey := substr(document_id, instr(document_id, ':') + 1, length(document_id) - 2);
                IF (g_debug = 'Y') THEN
                     log(x_module_name || '-' || 'document_id='||document_id);                                        
                     log(x_module_name || '-' || 'l_itemtype='||l_itemtype);                                        
                     log(x_module_name || '-' || 'l_itemkey='||l_itemkey);                                        
                END IF;
                x_sar_header_id := wf_engine.GetItemAttrNumber (itemtype => l_itemtype,
                                                             itemkey  => l_itemkey,
                                                             aname    => 'SAR_HEADER_ID');
                IF (g_debug = 'Y') THEN
                     log(x_module_name || '-' || 'SAR_HEADER_ID='||x_sar_header_id);                                        
                END IF;
                x_file_id := to_number(document_id);
                          fnd_lobs fl
                     where  fl.file_id = x_file_id;
                     when others then
                x_length := DBMS_LOB.getlength (x_attachment);
                DBMS_LOB.COPY (document, x_attachment, x_length, 1, 1);
                document_type := x_file_content_type || '; name=' || x_file_name;
           END get_header_attachment ;
        • 1. Re: Workflow binary attachment
          Saroja Kandepuneni-Oracle
          Can you log the values for 'to_char(x_file_id)' ,document_id, document_type , x_length and provide us.

          Also change the following statements to

          dbms_lob.createTemporary(x_attachment, FALSE, dbms_lob.call);
          DBMS_LOB.COPY (document, x_attachment, x_length, 1, 1);

          to below statements which was working fine for me

          dbms_lob.createTemporary(x_attachment, FALSE, DBMS_LOB.Session);
          DBMS_LOB.COPY (document, x_attachment, x_length);

          and verify if its working fine?

          and I hope fl.file_data contains the binary data for the file that has to be attached to notification

          Edited by: sarojak on Oct 4, 2012 2:26 AM