Attachments in workflow notifications

Hi,

I am going to explain you about the attachments in workflow notifications. For example, while raising a Purchase Order, user/buyer may attach multiple documents to the approver (for the proof/to review….). In these cases we need to send all the attached documents to the approver in approver’s notification.

In 11.5.10 (Prior to R12), Requisition approval has attachment links. But, PO Approval does not have attachment links in approver’s notification. (Except the reference attachments…explained below).

Oracle has given this option in R12. If the client version is 11i or prior and if the client is asking for the attachment links in PO approval then we need to customize the workflow.

Let us make it as generic rather than module specific. A simple workflow with two types of attachments is shown below:

There are two types of attachments in a notification. One is attachment link and the other is reference attachments (reading/encoding…).

Creating Attachment Links:

Every document which is attached in forms will be saved in fnd_lobs table and with the unique id (document id) in fnd_attached_documents and fnd_documents

Look at the below screenshot to attach any document from forms.

Query to get the attached document:

      SELECT fl.file_name, fl.file_data

  FROM fnd_lobs fl, fnd_attached_documents fad, fnd_documents_tl fdl

  WHERE fad.pk1_value = TO_CHAR (:l_document_id)

   AND fad.pk2_value = TO_CHAR (l_revision_number)

   AND fdl.document_id = fad.document_id

   AND fdl.media_id = fl.file_id

   AND fad.entity_name = :l_entity_name;

 

In above query,

 l_document_id is the primary key of the module attachment. i.e if you are attaching in PO header level then document id will be PO_HEADER_ID value and if the user is attaching documents in line level then document id will be PO_LINE_ID value.

l_entity_name

There are various entity names for each module in header and line level.

Ex:  

REQ_HEADERS                        PA_PROJECTS

REQ_LINES                              PER_ABSENCE_ATTENDANCES                                              

PO_HEADERS                          OIE_HEADER_ATTACHMENTS

PO_HEAD                                OIE_LINE_ATTACHMENTS

PO_LINES                                PA_CONTROL_ITEMS

l_revision_number

Revision number is the attachment revision number, generally starts with ‘0’.

Attachment link looks like below

 

Steps to get the attachment link in notification from fnd_lobs:

1. Create a attribute in workflow with the type as ‘Document’

Where Value of the attribute hardcode to ‘FND:entity=OIE_LINE_ATTACHMENTS&pk1name=REPORT_LINE_ID&pk1value=556161’

*We can set it in code for dynamic pick.

The syntax should be same as above to get value from fnd_lobs, fnd_attached_documents and fnd_documents using the above mentioned query.

One more example of syntax for PO line attachments:

FND:entity=PO_LINES&pk1name=PO_LINE_ID&pk1value=XXXXX (po_line_id value)

The value for 'entity' can be obtained from DATA_OBJECT_CODE in FND_DOCUMENT_ENTITIES.

The value for 'pk1name' can be obtained from PK1_COLUMN in FND_DOCUMENT_ENTITIES.
The value for 'pk1value' can be obtained from PK1_VALUE in FND_ATTACHED_DOCUMENTS.

and similiarly for pk2, pk3 and so on respectively from the said tables.

Please refer below comments for more info.

2. Create a message attribute in message in which you want to send the attachment

Message attribute internal name should be ‘#ATTACHMENTS’.

No need to check the attach content check box.

Give default type as ‘Item attribute’ and select the document type item attribute which was just created in step 1.

3. This is it. You can run the workflow to check the attachment links.

                No limitation for the number of attachments.

                No restriction for the type of the document, you can attach any file.

                Files should present in the tables for each document id.

*********************************************************************************

*********************************************************************************

Other type of attachment is which we can write/read/encode the documents and five it as an attachment.

Same process as above to create attributes, but the syntax will differ and should check the ‘attach content’ check box in message attributes. ‘#ATTACHEMENTS’ is not required.

Separate pl/sql code (with standard parameters) is there to execute the logic.

Steps to create clob/blob attachment:

1)

Create an attribute of type ‘Document’

Drag and drop OR Copy the document and paste it in Message attributes.

 Check the ‘Attach Content’ Check box.

Attribute value is hardcoded in the below screenshot. You can set it in your custom code.

2)

There are 3 various types of syntaxes available to create an attachment.

1. PLSQL 
2. PLSQLCLOB
3. PLSQLBLOB

Syntax: PLSQLCLOB:<Package.Procedure>/<unique_key>

Ex: PLSQLCLOB:xx_attach_package.xx_notif_attach_procedure/234562

PLSQL is for simple plain texts, tables…not for large objects.

If we set the above value to any document type of attribute, whenever the user tries to access the attribute the package.procedure will call automatically. [This is standard functionality given by Oracle – calling dynamic SQL by doing substring from colon (:) to slash (/)].

There are some standard parameters to be used whenever we use the procedure in between : and /.

Those parameters are

(document_id   IN VARCHAR2,

 display_type  IN VARCHAR2,

 document      IN OUT NOCOPY BLOB,

 document_type IN OUT NOCOPY VARCHAR2)

Where document id would be unique id like po_header_id,po_requisition_header_id, project_id …etc…

Display_types would be

Ex: display_type := 'text/html';  --OR ‘text/plain’;

Document could be CLOB/BLOB

The datatype of the file_data column in fnd_lobs is BLOB. So if you are using PLSQLCLOB then convert/encode the document using WF_MAIL_UTIL.encodeblob package.

Document type would be mime type. This could be pdf/excel/html/msword…..etc…

Syntax for pdf:

document_type :='application/pdf;name='||<file_name>;

3)

For custom workflows  generally we set the document attribute after creating the process i.e before starting the process.

 Launch Process = Create Process + Start Process (In wf_engine API)

              

PSEUDO CODE:

-- This is the pseudo code. Modify this according to your requirement

CREATE OR REPLACE PACKAGE BODY xx_attach_package

AS

   PROCEDURE xx_notif_attach_procedure (

      document_id     IN       VARCHAR2,

      display_type    IN       VARCHAR2,

      document        IN OUT   BLOB,

      document_type   IN OUT   VARCHAR2

   )

   IS

      l_docid               PLS_INTEGER;

      l_temp_blob           BLOB;

--l_file_path VARCHAR2(100) := '/u02/DEV/apps/apps_st/appl/soncus/12.0.0/bin';

      l_file_name           VARCHAR2 (100);   -- := 'Son_attachment_doc.doc';

      l_file_on_os          BFILE;

      l_dest_offset         NUMBER         := 1;

      l_src_offset          NUMBER         := 1;

      l_file_content_type   VARCHAR2 (256);

   BEGIN

      l_docid := TO_NUMBER (document_id);

/*Here we are reading  from a file*/

      DBMS_LOB.createtemporary (l_temp_blob, CACHE => FALSE);

      SELECT file_name, file_content_type, file_data

        INTO l_file_name, l_file_content_type, l_temp_blob

        FROM fnd_lobs

       WHERE file_id = 234562; -- Can get it dynamically using document id

--EXECUTE IMMEDIATE('CREATE OR REPLACE DIRECTORY SON_ATTACHMENTS AS '''||l_file_path||'''');

--l_file_on_os:=bfilename('',l_file_name);

      --dbms_lob.fileopen(l_file_on_os,dbms_lob.file_readonly);

      -- DBMS_LOB.LOADBLOBFROMFILE (dest_lob => l_temp_blob,

--                            src_bfile=>l_file_on_os,

--                            amount=>dbms_lob.getlength(l_file_on_os)

--                            ,dest_offset=>l_dest_offset  --in out parameter

--                            ,src_offset =>l_src_offset);

      --dbms_lob.fileclose(l_file_on_os);

      DBMS_LOB.createtemporary (document, CACHE => FALSE);

      DBMS_LOB.COPY (document, l_temp_blob, DBMS_LOB.getlength (l_temp_blob));

/*We have to determine document_type which is nothing but the mime type

document_type := 'image/jpg; name=filename.jpg';

Depending on the extension of the document the MIME type is determined. For simplicity

we are hard coding here*/

      document_type := 'application/pdf;name=' || l_file_name;

--commit;

   EXCEPTION

      WHEN OTHERS

      THEN

         wf_core.CONTEXT ('XX_ATTACH_PACKAGE',

                          'XX_NOTIF_ATTACH_PROCEDURE',

                          document_id,

                          display_type

                         );

         RAISE;

   END xx_notif_attach_procedure;

/****************************************************************/

   PROCEDURE son_attachment_wf

   IS

      l_itemtype      VARCHAR2 (30)  := 'SONCUSAT';

      l_itemkey       VARCHAR2 (300);

      -- l_file_name VARCHAR2(100) := '24Mar2010.xls';

      l_document_id   NUMBER         := 234562;

   -- You can get document id from the standard workflows (ex: po_header_id, expense_report_header_id...) while customizing standard workflows

   BEGIN

      l_itemkey := 'SONCUSAT' || TO_CHAR (SYSDATE, 'dd/mm/yyhh:mm:ss');

      wf_engine.createprocess (l_itemtype,

                               l_itemkey,

                               'SON_CUS_ATTACH_PROCESS'

                              );

      wf_engine.setitemattrdocument

         (itemtype        => l_itemtype,

          itemkey         => l_itemkey,

          aname           => 'SON_ATTACHMENT_ATTRIBUTE',

          documentid      =>    'PLSQLBLOB:xx_attach_package.xx_notif_attach_procedure/'

                             || l_document_id

         );

      wf_engine.startprocess (l_itemtype, l_itemkey);

   END;

/****************************************************************/

END xx_attach_package;

/

[*A PL/SQL CLOB document that you include as an attachment to a notification can contain a PDF or RTF document or, if your database version is Oracle9i Database or higher, other binary data that is encoded to base64. You should first store the document in the database as a binary large object (BLOB) and then convert the document into a CLOB as part of the PL/SQL procedure that generates the CLOB. You can use the UTL_RAW.Cast_To_VARCHAR2 function to convert the data from the BLOB into VARCHAR2 data that you write to a CLOB. If your database version is Oracle9i Database or higher, you can optionally use the WF_MAIL_UTIL.EncodeBLOB procedure to encode the binary data to base64.

Note: You can call WF_NOTIFICATION.WriteToClob() to help build a CLOB by appending a string of character data to it.]

You can write using HTML tags to create tables/column header/data in notification using PLSQL:<package.procedure>/<document_id>

Compared to the above attachment process, attachment links are simple to create.

Whenever the user wants to give the data (whatever he/she has entered) as an attachment (which is not yet saved in fnd_lobs/fnd_documents) then we can use the above syntax to write the data/creating the document/send as an attachment.

 Your suggestions are most welcome.

Thanks,

Rajesh