How to define document type Attribute in Oracle workflow

In a workflow process, we can attach documents generated by a PL/SQL procedure, which we call PL/SQL, PL/SQL CLOB, or PL/SQL BLOB documents. This is more useful when we have to generate a notification using the data fetched from a query.

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.

Create a workflow attribute with type as Document. This workflow attribute will contain the notification body.

1

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.

2

Call the PL/SQL Package for generating the Document CLOB

A function needs to be defined in the process for calling the PL/SQL package that creates the document.

3

Create PL/SQL code for HTML generation

Package Specification

CREATE OR REPLACE PACKAGE  XX_DOC_WF_PKG
as
PROCEDURE XX_create_DOC_WF
(
document_id     IN              VARCHAR2,
display_type    IN              VARCHAR2,
DOCUMENT        IN OUT NOCOPY   VARCHAR2,
DOCUMENT_TYPE   IN OUT NOCOPY   VARCHAR2
);
PROCEDURE XX_DOC_CALL
(itemtype in varchar2,
ITEMKEY IN VARCHAR2,
actid in number,
FUNCMODE IN VARCHAR2,
resultout out varchar2) ;

end;

Package  Body

CREATE OR REPLACE PACKAGE BODY XX_DOC_WF_PKG
as
PROCEDURE XX_create_DOC_WF
(
document_id     IN              VARCHAR2,
DISPLAY_TYPE    IN              VARCHAR2,
DOCUMENT        IN OUT NOCOPY   VARCHAR2,
document_type   IN OUT NOCOPY   VARCHAR2
)
IS
lv_details             VARCHAR2 (32767);
V_ITEMKEY          VARCHAR2(100);
amount number;

CURSOR CUR_QUALITF
IS
SELECT  PERSON_ID, TITLE
FROM PER_QUALIFICATIONS
WHERE  PERSON_ID = 4426;

BEGIN
/* TABLE HEADER*/
lv_details :=      lv_details
|| ‘<h4> ‘
|| ‘Details of the Qualification of employee ‘
|| ‘</H4>’
|| ‘<table border = “1”> <tr>’
|| ‘<th> ‘
|| ‘person id’
|| ‘</th>’
|| ‘<th>’
|| ‘Title’
|| ‘</th>’;

FOR CUR_QUALITF_REC IN CUR_QUALITF
loop
/*TABLE BODY */
lv_details:=        lv_details
|| ‘<tr>’
|| ‘<td>’
|| CUR_QUALITF_REC.person_id
|| ‘</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*/

—      document_type := ‘application/pdf;name=test.pdf’ ;  /* This syntax is used for PDF type of attachments */

document_type := ‘text/html’;

EXCEPTION
WHEN OTHERS
THEN
document := ‘<H4>Error ‘ || SQLERRM || ‘</H4>’;

END;

PROCEDURE XX_DOC_CALL
(
itemtype in varchar2,
ITEMKEY IN VARCHAR2,
actid in number,
funcmode in varchar2,
resultout out varchar2
)
IS
V_DOCUMENT_ID CLOB;
v_itemkey  NUMBER;

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      => itemtype,
itemkey       => itemkey,
ANAME         => ‘BODY’,
avalue        => V_DOCUMENT_ID
);

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      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>;
e.g.
document_type := ‘application/pdf;name=test.pdf’ ;
 
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.
 
Datatypes supported are:
VARCHAR2 – 32KB Size Limitation
CLOB – 4GB (possible limitations due to workflow engine limitation)
BLOB – 4GB (possible limitations due to workflow engine limitation)
Advertisements

8 thoughts on “How to define document type Attribute in Oracle workflow

  1. Thank you for this great demo, would you possibly know how to add upon this by sending a dynamic document to multiple supervisors listing information for only their employees?

  2. SELECT PERSON_ID, TITLE
    FROM PER_QUALIFICATIONS
    WHERE PERSON_ID = 4426; how can you hardcode a value?? how to pass dynamically..

      • Thank you for your reply.. dear I just wanted to add where clause in the query.. if I pass item key like below
        l_document_id := ‘PLSQL:APPS.K_WF_BPS_PKG.bps_detail_wf_doc/’ || itemkey;

        PROCEDURE btp_detail_wf_doc (
        document_id IN VARCHAR2,
        display_type IN VARCHAR2,
        document IN OUT NOCOPY VARCHAR2,
        document_type IN OUT NOCOPY VARCHAR2
        )
        ….

        FOR i IN (SELECT date, project, currency, amount,
        purpose
        FROM xxk_bps_add_projects
        WHERE bps_reference =???!!!!!!!

        =???!!!!!!!
        Please help me what I suppose to use here.. Really dying to solve this issue.

        is it to assign local variable and add where as “to_char(l_bta_number ))”

        please help me

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s