Sending Attachment using Oracle Workflow Notification

While you are working with oracle workflow, sending notification with attachment is very common requirement. In this article I have tried to show
this simple process in step by step.

First create a workflow process like fig1 ->




Create one attribute "Attachment2" like fig2.




Drug and drop this attribute under "Attachment Message". Set the Attributes property like fig3.







Your workflow is complete. Now write the following PL/SQL code and run the workflow to get the notification.




CREATE OR REPLACE PACKAGE BODY RCT_EXAMPLE IS


    PROCEDURE start_training_wf(unique_id IN VARCHAR2) IS
        l_itemtype  VARCHAR2(30) := 'RCT_ITM1';
        l_itemkey   VARCHAR2(300);
        l_file_name VARCHAR2(100) := 'holidaylist.xls';
        l_unique_id VARCHAR2(50) := unique_id;
   
    BEGIN
        l_itemkey := 'RCT_ITM1 ' ||
                     to_char(SYSDATE,
                             'dd/mm/yyhh:mm:ss');
        wf_engine.createprocess(l_itemtype,
                                l_itemkey,
                                'RCT_PROCESS');
        wf_engine.setitemattrdocument(itemtype   => l_itemtype,
                                      itemkey    => l_itemkey,
                                      aname      => 'ATTACHMENT2',
                                      documentid => 'PLSQLBLOB:RCT_EXAMPLE.xx_notif_attach_procedure/' ||
                                                    l_file_name);
        wf_engine.startprocess(l_itemtype,
                               l_itemkey);
   
    END;


    PROCEDURE xx_notif_attach_procedure(document_id   IN VARCHAR2,
                                        display_type  IN VARCHAR2,
                                        document      IN OUT BLOB,
                                        document_type IN OUT VARCHAR2) IS
        l_file_name VARCHAR2(100) := document_id;
        bdoc        BLOB;
    BEGIN
       
        document_type := 'Excel' || ';name=' || l_file_name;
   
        SELECT stored_file
          INTO bdoc
          FROM BLOB_TABLE
         WHERE file_name = l_file_name;
   
        dbms_lob.copy(document,
                      bdoc,
                      dbms_lob.getlength(bdoc));
    EXCEPTION
        WHEN OTHERS THEN
           
            wf_core.CONTEXT('xx_g4g_package',
                            'xx_notif_attach_procedure',
                            'document_id',
                            'display_type');
            RAISE;
    END;


END RCT_EXAMPLE;






Note ->


        We are storing our blob file in a table called "blob_table". There is a file whose name is "holidaylist.xls". The table structure is as like fig4.


If you are facing some issues, you can leave a comment below.

Some more article on Oracle workflow, you can find here.

11 comments :

Anonymous said...

I am curious to see your solution but it seems your pictures are broken. Can you fix it please?

Txs,
Sunil

unknown writer said...

Sunil,
I have fixed the broken images. Now, you can check.

Anonymous said...

Appreciated. I need to attach quote to the notification using standard WF package aso_apr_wf_pvt. Your method will work for custom package. How can I achieve this without customizing?

Txs, Sunil

unknown writer said...

pardon me. "attach quote" means? can u clear a bit more.
If u want to send something using notification or modify standard notification, u need to modify the standard pkg or may be u can extend it.

Saād said...

Hi, I used your steps exactly, but can you please tell me how to run the procedure? and where will I get the attachment, as I hav not put any mail. Plzzz help...

unknown writer said...

Saad,
The above solution is developed in Oracle Application 12.1.1. Here you can create the procedure in APPS schema. In the notification, you need to set the "Role" property. This role will decide to whom this notification will go. Please read about some basic steps on notification item. Then it'll be clear to you.

Manisha said...

Hi, I want to know how to store the document in table blob_table. I am not able to see fig4 for the blob_table structure. I have a requirement to attach a document to a workflow notification. I understood the steps from workflow point of view. Could you please help me with storing the document into the table?

coolvizith said...

Hi,

Your Post is really helpful, But i am facing an issue, In the notification message the attribute which i wanted to display ,i shown like this



Hi Sir/Madam,

This is to bring in your notice that Mr/Mrs:&EMP_NAME has requested for a RFQ Approval. The details for the same are furnished below :

Performer

DETAILS
Requestor: &EMP_NAME
Reference: &RFQ_NUMBER
Date Active From: &START_DATE
Item: &ITEM
Price: &PRICE

Kindly approve/reject the above request.

The document has been attached for your reference.
&XX_PFSAP_RFQ_ATTATCHMENT

Thanks
Administrator

Here &XX_PFSAP_RFQ_ATTATCHMENT is the attatchment file link i wanted to send in notification but Notification is showing that feild as text,

Below is Oracle Notifications Information :

Administrator Workflow

Diagnostics Home Logout Preferences Help






Notifications >
Information



This notification has been closed with the result: APPROVE



RFQ00091 for approval








To Omay, Mr. Ronald B
Sent 26-Sep-2011 17:16:55
Closed 26-Sep-2011 17:21:41
ID 1115401
Responder Omay, Mr. Ronald B





Hi Sir/Madam,

This is to bring in your notice that Mr/Mrs:KALA has requested for a RFQ Approval. The details for the same are furnished below :

Performer

DETAILS
Requestor: KALA
Reference: RFQ00091
Date Active From: 01-DEC-2012
Item:
Price:

Kindly approve/reject the above request.

The document has been attached for your reference.
&XX_PFSAP_RFQ_ATTATCHMENT

Thanks
Administrator





Please look into this, It would be a great help,

Eagerly Waiting for your response,

Best Regards,
Vizith Kumar Bhogi

Krishnaja said...

Cannot see Fig4. Please fix it.

Thanks
Krishnaja

Shipra said...

Not able to see the Fig 4 ..please fix it.

Ranveer said...

Many thanks ..i was struggling for this.