Sending Attachment With Notification
Sending Attachment With Notification
Sending Attachment With Notification
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 ->
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.
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.
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 approvers notification.
In 11.5.10 (Prior to R12), Requisition approval has attachment links. But, PO Approval does
not have attachment links in approvers 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).
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
SELECTfl.file_name,fl.file_data
FROMfnd_lobsfl,fnd_attached_documentsfad,fnd_documents_tlfdl
WHEREfad.pk1_value=TO_CHAR(:l_document_id)
ANDfad.pk2_value=TO_CHAR(l_revision_number)
ANDfdl.document_id=fad.document_id
ANDfdl.media_id=fl.file_id
ANDfad.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.
The syntax should be same as above to get value from fnd_lobs, fnd_attached_documents
and fnd_documents using the above mentioned query.
2. Create a message attribute in message in which you want to send the attachment
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.
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
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_idINVARCHAR2,
display_typeINVARCHAR2,
documentINOUTNOCOPYBLOB,
document_typeINOUTNOCOPYVARCHAR2)
Display_types would be
Ex: display_type := 'text/html'; --OR text/plain;
3)
For custom workflows generally we set the document attribute after creating the process i.e
before starting the process.
PSEUDO CODE:
Thisisthepseudocode.Modifythisaccordingtoyourrequirement
CREATEORREPLACEPACKAGEBODYxx_attach_package
AS
PROCEDURExx_notif_attach_procedure(
document_idINVARCHAR2,
display_typeINVARCHAR2,
documentINOUTBLOB,
document_typeINOUTVARCHAR2
)
IS
l_docidPLS_INTEGER;
l_temp_blobBLOB;
l_file_pathVARCHAR2(100):=
'/u02/DEV/apps/apps_st/appl/soncus/12.0.0/bin';
l_file_nameVARCHAR2(100);:='Son_attachment_doc.doc';
l_file_on_osBFILE;
l_dest_offsetNUMBER:=1;
l_src_offsetNUMBER:=1;
l_file_content_typeVARCHAR2(256);
BEGIN
l_docid:=TO_NUMBER(document_id);
/*Herewearereadingfromafile*/
DBMS_LOB.createtemporary(l_temp_blob,CACHE=>FALSE);
SELECTfile_name,file_content_type,file_data
INTOl_file_name,l_file_content_type,l_temp_blob
FROMfnd_lobs
WHEREfile_id=234562;Cangetitdynamicallyusingdocumentid
EXECUTEIMMEDIATE('CREATEORREPLACEDIRECTORYSON_ATTACHMENTSAS'''||
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_offsetinoutparameter
,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));
/*Wehavetodeterminedocument_typewhichisnothingbutthemimetype
document_type:='image/jpg;name=filename.jpg';
DependingontheextensionofthedocumenttheMIMEtypeisdetermined.For
simplicity
wearehardcodinghere*/
document_type:='application/pdf;name='||l_file_name;
commit;
EXCEPTION
WHENOTHERS
THEN
wf_core.CONTEXT('XX_ATTACH_PACKAGE',
'XX_NOTIF_ATTACH_PROCEDURE',
document_id,
display_type
);
RAISE;
ENDxx_notif_attach_procedure;
/****************************************************************/
PROCEDUREson_attachment_wf
IS
l_itemtypeVARCHAR2(30):='SONCUSAT';
l_itemkeyVARCHAR2(300);
l_file_nameVARCHAR2(100):='24Mar2010.xls';
l_document_idNUMBER:=234562;
Youcangetdocumentidfromthestandardworkflows(ex:po_header_id,
expense_report_header_id...)whilecustomizingstandardworkflows
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;
/****************************************************************/
ENDxx_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.
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.
Create the workflow message and attach the workflow attribute to this message
If the box Attach Content is checked then the content of this attribute is shown in the
notification as a link and if the notification is sent as an email it will appear as an email
attachment.
|| </td>
|| <td>
|| CUR_QUALITF_REC.TITLE
|| </tr> ;
end loop;
document :=LV_DETAILS;
/*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*/
BEGIN
V_DOCUMENT_ID :=PLSQL:XX_DOC_WF_PKG.XX_create_DOC_WF/ || ITEMKEY;
/*Setting Value to the Document Type Attribute */
wf_engine.setitemattrtext (itemtype
itemkey
=> itemkey,
ANAME
=> BODY,
avalue
=> V_DOCUMENT_ID
);
=> itemtype,
end;
end xx_doc_wf_pkg;
Since the output from the HTML generator procedure is text we are passing this value into the
variable as a CLOB. Hence the call, PLSQLCLOB: Had the output been BLOB then the call
would have PLSQLBLOB.
Note : V_DOCUMENT_ID :=PLSQL:XX_DOC_WF_PKG.XX_create_DOC_WF/ ||
ITEMKEY;
Please note the manner in which parameter documented is assigned. The syntax is
PLSQLBLOB:<package name>.<procedure name>/<unique id to identify binary file> .
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
Display_types would be
Ex: display_type := text/html; OR text/plain;