Oracle Workflow to show html table in the Message Notification

1. Create the package, body as attached.

1. Attribute

2. Function, message, attribute under it. 

3. Attribute under message.

4. Using attribute in Message Body

Package: 

CREATE OR REPLACE PACKAGE APPS.XX_BODY_TXN_INFO_DOC_PKG
as
PROCEDURE XX_create_DOC_WF----for action history
(
document_id     IN              VARCHAR2,
display_type    IN              VARCHAR2,
DOCUMENT        IN OUT NOCOPY   VARCHAR2,
DOCUMENT_TYPE   IN OUT NOCOPY   VARCHAR2
);
PROCEDURE XX_DOC_CALL  --for calling
(itemtype in varchar2,
ITEMKEY IN VARCHAR2,
actid in number,
FUNCMODE IN VARCHAR2,
resultout out varchar2) ;

end;

--Package  Body
/

CREATE OR REPLACE PACKAGE BODY APPS.XX_BODY_TXN_INFO_DOC_PKG
as
--for history
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 LINE_NUM,
    ASSET_NUMBER ,
    ENTITY,
    DEPARTMENT,
    UNITS,
    ASSET_DESCRIPTION,
    TAG_NUMBER,
    PO_NUMBER,
    SUPPLIER_NAME,
    ASSET_COST,
    ACCUMULATED_DEP,
    NET_BOOK_VALUE,
    ASSET_LIFE,
    DATE_PLACEDIN_SERVICE,
    ASSET_ID
FROM  XX_TABLE_APPROVALS_L
  WHERE DISPOSAL_ID = document_id
 ORDER BY LINE_NUM ASC;


BEGIN
/* TABLE HEADER*/
lv_details :=      lv_details
--|| '<h4> '
--|| 'Asset Details'
--|| '</H4>'
|| '<table border = "5" BORDERCOLOR="#B9D1EA"> <tr>'
--||'<b><caption align="left" BORDERCOLOR="#B9D1EA">Transaction Details</caption></b>' --if space in caption its coming in next line
|| '<th> '
|| 'Line'
|| '</th>'
|| '<th>'
|| 'Asset Number'
|| '</th>'
|| '</th>'
|| '<th>'
|| 'Entity'
|| '</th>'
|| '<th>'
|| 'Department'
|| '</th>'
|| '<th>'
|| 'Units'
|| '</th>'
|| '<th>'
|| 'Descriptin'
|| '</th>'
|| '<th>'
|| 'Tag Number'
|| '</th>'
|| '<th>'
|| 'PO Number'
|| '</th>'
|| '<th>'
|| 'Supplier Name'
|| '</th>'
|| '<th>'
|| 'Asset Cost'
|| '</th>'
|| '<th>'
|| 'Accumulated Dep'
|| '</th>'
|| '<th>'
|| 'NetBook Value'
|| '</th>'
|| '<th>'
|| 'Asset LIfe'
|| '</th>'
|| '<th>'
|| 'DatePlaceIn Service'
|| '</th>'
;

FOR CUR_QUALITF_REC IN CUR_QUALITF
loop
/*TABLE BODY */
lv_details:=        lv_details
|| '<tr>'
|| '<td>'
|| CUR_QUALITF_REC.LINE_NUM
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ASSET_NUMBER
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ENTITY
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.DEPARTMENT
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.UNITS
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ASSET_DESCRIPTION
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.TAG_NUMBER
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.PO_NUMBER
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.SUPPLIER_NAME
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ASSET_COST
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ACCUMULATED_DEP
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.NET_BOOK_VALUE
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.ASSET_LIFE
|| '</td>'
|| '<td>'
|| CUR_QUALITF_REC.DATE_PLACEDIN_SERVICE
|| '</td>'
|| '</tr>'
;

--INSERT INTO TESTA VALUES(1,2,3);

end loop;

document :=LV_DETAILS||'</table>';

/*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_BODY_TXN_INFO_DOC_PKG.XX_create_DOC_WF/' || ITEMKEY;

/*Setting Value to the Document Type Attribute */

wf_engine.setitemattrtext (itemtype      => itemtype,
itemkey       => itemkey,
ANAME         => 'BODY_TXN_DET_INFO',
avalue        => V_DOCUMENT_ID
);

end;
end XX_BODY_TXN_INFO_DOC_PKG;
/