Sending Attachment using Oracle Workflow Notification- Oracle Apps R12
By Jag - February 04, 2014
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.
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.
0 comments