Search This Blog

Monday, December 14, 2020

Oracle Workflow to show html table in the Message Notification

  1. Create the package, body as attached.

  1. Attribute

cid:image001.png@01D6D218.4735E210


  1. Function, message, attribute under it. 


cid:image006.jpg@01D6D218.7532CD40



  1. Attribute under message.

cid:image004.png@01D6D218.4735E210


4. Using attribute in Message Body

cid:image005.png@01D6D218.4735E210



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;
/

No comments:

Post a Comment