Apps - Send binary attachments with Notifications in Workflow

Main Menu

Receive Email for New Articles

Contributors

Hands-On-Training

Apps Book

Related Items

Home

Technical Articles

Oracle Workflows

Send binary attachments with Notifications in Workflow

We are glad to announce the launch of Forum for Customizations and Extensions. Click here to visit http://apps2fusion.com/forums This website has now moved to http://apps2fusion.com

Our OA Framework, BPEL Development & Apps DBA Trainings from USD 299 only [on weekends] . Click here for details.
Also see here fully verifiable feedbacks/testimonials

Send binary attachments with Notifications in Workflow

| Print |

E-mail

Written by Anil Passi

Friday, 01 September 2006

Below I have listed a 4 step solution to send MsWord or PDF or other types of Document Attachments with Oracle Workflows Notifications.

Using this simple technique, you can generate email notifications with attachments for Word or PDF or any other binary objects.
Oracle has made sending such documents as attachments with notifications very straightforward. In the below example, I am assuming only one attachment per notification. However, you can create as many Attachment attributes you wish, and also can programmatically control how many attachments are sent per transactions. Anyway, below are four simple steps for sending Workflow Notifications with Attachments.

Step 1
As in the picture, workflow Message “XX_G4G Notification With Attachment” is defined. This message will be referenced by the Notification that sends either MsWord or PDF or any other binary attachment as email.

Step 2.
Define an attribute to the message defined in Step 1. This is shown in the picture below. Following must be noted:-
Type: Document

Attribute for Attachment

Source: Send
Frame Target: New Window
Attach Content: Yes.

If you had dragged the Attribute from Workflow level into this message, then default sub-section will be populated automatically.

Step 3.
After executing the wf_engine.createprocess and before executing wf_engine.startprocess, we need to set a value for the attribute defined in Step 2. This can be done by calling wf_engine.setitemattrdocument as below
wf_engine.setitemattrdocument
(itemtype=>'XG4GWFIT'
, itemkey=>'XG4GWFIT' || l_item_key
, aname=>'XX_G4G_ATTACHMENT1'
, documentid =>'PLSQLBLOB:xx_g4g_package.xx_notif_attach_procedure/' || to_char(l_file_id));

Please note the manner in which parameter documented is assigned. The syntax is PLSQLBLOB:<package name>.<procedure name>/<unique id to identify binary file> . In my example, I am capturing the file_id from fnd_lobs. In your case this ID will be derived depending upon whether you are looking for PO Attachment or say an attachment to Oracle Sales Order or a Resume in iRecruitment or some course notes in oracle Learning Management.

Step 4.
Handle the execution of the procedure, in this case xx_notif_attach_procedure

PROCEDURE xx_notif_attach_procedure
(
    document_id   IN VARCHAR2
   ,display_type IN VARCHAR2
   ,document      IN OUT BLOB
   ,document_type IN OUT VARCHAR2
) IS
    lob_id       NUMBER;
    bdoc         BLOB;
    content_type VARCHAR2(100);
    filename     VARCHAR2(300);
BEGIN
    set_debug_context('xx_notif_attach_procedure');
    lob_id := to_number(document_id);

    -- Obtain the BLOB version of the document
    SELECT file_name
          ,file_content_type
          ,file_data
    INTO   filename
          ,content_type
          ,bdoc
    FROM   fnd_lobs
    WHERE file_id = lob_id;
    document_type := content_type || ';name=' || filename;
    dbms_lob.copy(document, bdoc, dbms_lob.getlength(bdoc));
EXCEPTION
    WHEN OTHERS THEN
      debug('ERROR ^^^^0018 ' || SQLERRM);
      wf_core.CONTEXT('xx_g4g_package'
                     ,'xx_notif_attach_procedure'
                     ,document_id
                     ,display_type);
      RAISE;
END xx_notif_attach_procedure;

Please note the following:-
Note 1. In step 3, the value of Unique File id that is passed in after / gets translated into document_id in step 4. This translation occurs within the workflow engine, when it splits the pl/sql name from parameter.

Note 2. Proecdure xx_notif_attach_procedure must be defined in the package specification too.

Note 3. This procedure has an out parameter “document IN OUT BLOB”

Note 4. The value from the Oracle Blob column is fetched into blob variable bdoc from fnd_lobs.

Note 5. Next you can use dbms_lob.copy to copy the value from blob variable into out parameter for notification.

Thanks for reading this article. Kindly share this knowledge with your colleagues as this happens to be a very useful feature of Oracle Workflows.

Thanks,
Anil Passi

Comments (28)

Subscribe to this comment's feed

...
written by nasir , November 14, 2006

let me try it out and get back here

...
written by nasir , November 14, 2006

let me try it out and get back here

...
written by Anil Passi , November 15, 2006

Hope it worked Naser, it should, as I implemented this for a OTA (OLM) workflow

...
written by Anil Passi , November 15, 2006

Hope it worked Naser, it should, as I implemented this for a OTA (OLM) workflow

...
written by Anil Passi , January 21, 2007

Hi Umesh,

I assume you are doing the below:-
You have subscribed to the event that gets fired after IRC vacancy creation. This Workflow subscription is then send notification with attachment.

Now to answer your question, well it is answered in Step 3 in this article. After the package dot procedure name you are passing the File ID from which blob can be derived. I guess IRC uses its own table to store those attachments[does not use FND_LOBS]. THe document id in this case will be the id that you assign to the attribute[of type document].

Regarding the package spec and body, you can define in whatever way you wish. However the package procedure that gets assigned to attribute will have parameters of
document_id IN VARCHAR2
,display_type IN VARCHAR2
,document IN OUT BLOB
,document_type IN OUT VARCHAR2

Thanks,
Anil Passi

...
written by umesh , January 21, 2007

hi anil,
can you tell me..
i> how in parameters document_id, display_type will get pass in the procedure xx_notif_attach_procedure
ii> how exactly package specificatin & body for xx_g4g_package will look like.

i will be very greatful if you can tell me, i am ref. this article for sending an attachment through notification after creation of vacancy in the irecruitment.

regards,
Umesh

...
written by Anil Passi , January 21, 2007

...
written by umesh , January 21, 2007

...
written by Vamsi Mohan , March 16, 2007

Hi Anil,

I tried this in 11.5.8 and i got the following error when i clicked on the attachment icon in the notification.

fnd_document_management.get_launch_document_url(plsqlblob:my_procedure1/10)
Wf_Notification.GetAttrDoc(134824, ACN_DOC_ATTR, text/html)
Wfa_Html.show_plsql_doc(134824, ACN_DOC_ATTR)

I have created the procedure as myprocedure1 and passing the constant 10.

When i looked at the signature of the procedure fnd_document_management.get_launch_document_url it has the following parameters

username
document_identifier
display_icon
launch_document_URL

but in the error stack i could see only the document URL. Do i need to do any setup?

Thanks
Vamsi

...
written by Ram , April 13, 2007

Dear Passi,
PO Approval Notification does not contain the Attachment icon, to view the attached documents in the PO.. (whereas, requisition notification contains this attachment view facility)
How do I customize this?? Can you help us please..
Regards,
Ram

...
written by Ram , April 13, 2007

...
written by Anil Passi , April 14, 2007

Hi Ram

This is due to the Workflow Message Attribute.
Have a look at below link
http://docs.google.com/Doc?id=dcfd8fsc_52dqr7kr

You may either delete it from Req or develop something similar for PO
The workflows in question are:-
POAPPRV
PO Approval

AND

REQAPPRV
PO Requisition Approval

Thanks,
Anil Passi

...
written by Anil Passi , July 03, 2007

Hi Sidh

It appears that either the attribute does not have 'PLSQLBLOB....' or the OUT parameter is not of type BLOB.

You can also have a look at API irc_notification_helper_pkg, within which procedure show_resume is used. You can base your example on that, as iRecruitment uses this API to attach resumes with notifications.

Thanks,
Anil Passi

...
written by Ajay Talluri , July 09, 2007

Hi Anil,
This is a great document. I have used the same to send the output of a concurrent program to an intended user using the above method. But When I am send the notification I am getting the following error.
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= xx_email_report.xx_notif_attach_procedure(831149, text/html) Wf_Notification.GetAttrblob(769915, XM_ATTACH, text/html) WF_XML.GetAttachment(769915, text/html) WF_XML.GetAttachments(769915, http://aperpppd.cellc.net:8002/pls/PPRD, 2136) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 769915) WF_XML.Generate(oracle.apps.wf.notification.send, 769915) WF_XML.Generate(oracle.apps.wf.notification.send, 769915) Wf_Event.setMessage(oracle.apps.wf.notification.send, 769915, WF_XML.Generate)
Wf_Event.dispatch_internal()
Error Stack =

Activity Id = 224318
Activity Label = XX_USER_ACCESS:XX_NOTIFICATION Result Code = #MAIL Notification Id = 769915 Assigned User = ATALLURI

Would you please help me in getting the problem solved.

Thanks in Advance.

...
written by Anil Passi , July 09, 2007

Hi Ajay

If you want to send the output of a concurrent program, then call the api below [it has overloaded parameters]
fnd_request.add_notification(user => xuser,
on_normal => xon_normal,
on_warning => xon_warning,
on_error => xon_error);
You will call this before invoking fnd_request.submit_request

OR, if you want this done during submission, then attach a role as below

By doing so, the desired user/users can receive email in their inbox with a link to the output of concurrent program

Thanks,
Anil Passi

...
written by vivek , September 13, 2007

Hi Anil,

I want to add Comments in the FYI Notification, and pass it to the next Approver, for this i have made two rules having category as 'FYI' and 'Approver'. My process is explained below,
A-->B-->C, over here 'A' is the initiator 'B' is the FYI reciever and 'C' is the final approver, i need to design the process so that when 'A' enters some comments it should be visible to 'B' and when 'B' enters some comments 'C' should be able to view all comments i.e. given by 'A' and 'B'.
Please provide some information on the approach basically i am unable to carry forward and write comments.

Regards,
Vivek

...
written by rahul pardeshi , October 18, 2007

Has anyone done this before

to runa concurrent program report from oracle workflow

and then attac the output of the report to a notification

the output of the report is PDF ( from XML Publisher)

Thanks
Rahul

Sending a Oracle report output as an attachment in WF
written by nagender , October 22, 2007

Hi Anil,
I need to send a report output as an attachment in WF (XML publisher output).
I have got the request id and the path of the output file.
But Now I am unable to send this output file as an attachment to the user using WF.
Can you help me....
I can send the URL(link) using fnd.request.add_notification() but I need to send it as an attachment.
Thanks,
Nagender

Hi nagendar
written by Rahulq , October 22, 2007

Please send me the part where you are arraching the URL for the concurent report through
workflow
my client nned it

greatly appreciate your help

thanks
rahul
rahul_pardeshi@yahoo.com

where to find concurrent program output file
written by mdmravi , October 25, 2007

Hi anil,
How do i find the path where concurrent program output PDF is stored. here u have used fnd_lobs. How can i capture , concurrent program o/p file here.

regards
ravi

Max size of Notification content.
written by SandeepSomvanshi , October 30, 2007

Hi Anil,

I have a question related with normal workflow notifications. Can i send more than 32k size message as a notification's content.

Regards,
Sandeep

...
written by Anil Passi , October 30, 2007

Hi Sandeep

With CLOB you can go beyond 32K
With BLOB too, you can go beyond 32K

Thanks
Anil Passi

...
written by subhankar , October 30, 2007

This is in continuation to one of the previous posts:written by Ajay Talluri , July 09, 2007

I am facing similar error
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'.
...
...

while trying to attach PDF documents(PLSQLBLOB) to the notificatons..any idea what can be possible causes for the same.

Thanks
Subhankar

Contrractor
written by Abhishek_Gupta , November 13, 2007

Hi,

I want to send FYI and approve notification for vacancy approval.

Can you tell me how i can do that?

My Req is once some approve vacancy i need to send FYI to third person.

...
written by agupta , November 13, 2007

Hi,

I have workflow working already for vacancy approval. What i need is when some one approves vacancy, it should send FYI to third person. So i can add this req in existing workflow so that i can achieve FYI notification along with approvals.

out put file of concurrent pgm
written by rose , November 16, 2007

Hi anil,

How do i find the path where concurrent program output PDF is stored. here u have used fnd_lobs. How can i capture , concurrent program o/p file here.

One more doubt
written by Sachin Ahuja , December 13, 2007

Hi Anil,

I've one more doubt again. How we are putting the data into FND_LOBS Table? i.e any api which I can use to insert data into FND_LOBS.

In Detail:

I have data in one table and will be sending notification based on data in it. Now as soon as notification is send we want to delete the records in table. I tried using CLOB for it. But as soon as records gets deleted previously send notifications also fails to open.

So I thought if I can put my data in FND_LOB as some rtf file and will base my attachment procedure to fetch the data from FND_LOBS and for this I need an api to insert the data into FND_LOBS.

Thanks & Regards
Sachin Ahuja