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
- 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 '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.
- 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.
- 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.
- PLSQL
- PLSQLCLOB
- 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
