Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Sunday, August 28, 2011

Define a Document Type Attribute in Oracle workflow

Document Type attribute is a very good feature Offered in Oracle Workflow.

Document Type has many more uses but here I will explain how to call pl/sql API from a workflow attribute with Document Type.

This feature is very helpful when there is requirement to call pl/sql package from workflow without adding a NEW ACTIVITY.In case just add an attribute in workflow and then call pl/sql package from attribute.

DOCUMENT TYPE ATTRIBUTE
You can integrate a document into a workflow process by defining an attribute of type document for an item type, message, or activity. Oracle Workflow supports document types called "PL/SQL" documents, "PL/SQL CLOB" documents, and "PL/SQL BLOB"documents. A PL/SQL document represents data as a character string.

The document-type
attribute tells Oracle Workflow how to construct a dynamic call to a PL/SQL procedure that generates the document. You can embed a
PL/SQL or PL/SQL CLOB document-type message attribute in a message body to display the document in a notification.

The PL/SQL procedure that generates a PL/SQL document must have the following standard API as shown below
------------------
procedure_name
(document_id in varchar2,
display_type in varchar2,
document in out nocopy varchar2,
document_type in out nocopy varchar2)
------------------

whenever you want to use document type , the corresponding procedure should have above 4 Parameters.

document_id - A string that uniquely identifies a document. I always used ITEM_TYPE value .But value can be anything that uniquely identifed the document and depend on requirement to requirement.

Please Refere Workflow Developer Guide from Oracle Development For More details.
OR REFER my example Below

display_type - It has value like
text/plain
text/html

document
The outbound text buffer where up to 32K of document text is returned.
document_type
The outbound text buffer where the document content type is returned. Also referred to as the returned type.

I will explain how to use document type in workflow with the example.

In my example , I will define a pl/sql API , and then will define a workflow and will call the pl/sql API from the workflow attribute (Attribute will be of type DOCUMENT).

1.Define pl/sql Package & procedure
2.Design workflow and Workflow Attribute of type document type
3.call the pl/sql API from the document type ATTRIBUTE.

PL/SQL package
------------------
create or replace package testpkg as
procedure testproc1
(document_id in varchar2,
display_type in varchar2,
document in out nocopy varchar2,
document_type in out nocopy varchar2);

procedure testproc2
(item_type in varchar2,
item_key in varchar2,
activity_id in number,
command in varchar2,
resultout in out varchar2);
end testpkg;
=============

create or replace package body testpkg as
procedure testproc1
(
document_id in varchar2,
display_type in varchar2,
document in out nocopy varchar2,
document_type in out nocopy varchar2) as
begin

document_type:='text/plain';
document :='Mytest value'; - you can build your logic and then populate value to this variable
end testproc1;

procedure testproc2
(item_type in varchar2,
item_key in varchar2,
activity_id in number,
command in varchar2,
resultout in out varchar2)
IS
begin
insert into test001 values ('BBB');
resultout := 'COMPLETE';
end;
end testpkg;
-----------------------------

My Workflow

Document Type Attribute - Attribute SRA1 is of type DOCUMENT .





As shown above
Default Type for Attribute SRA1 is Constant and
Default Value for Attribute SRA1 is plsql:package_name.procedure_name/&Attribute_name.

IMPORTANT - Please Note that the Attribute in
should be of format plsql:/
where

is pl/sql API (package_name.procedure_name)
-Any PL/SQL argument string that you want to pass directly to the procedure

From workflow point of view to make things easy I always prefer to pass values of the unique Attribute of workflow.

Please Note that in my example I have ITEMKEY as one of the attribute in my workflow and that has unique value.
BUT IF in your case there is no importance of the UNIQUE value, then you can even replace with a HARD CODED Constant value.
Other options to add a unique value are &#NID(Notification_id).

No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect