Oracle Application EBS Related Stuff: How to display table with multiple rows in a workflow notification along with additional text

Prerequisites:
1) Create Item Type
2) Create Process
3) Create Notification
4) Create message

Step1- Create an Attribute of type document. Give value as constant "plsqlclob:XX_DEMO.getclobdocument/&ITEM_KEY"

2) Copy this attribute and paste it under message created.

3) Click on the message and go to body tab.Give subject and body here.In Body include document type attribute you have created above.

4) Now we have to write a procedure which will create table with multiple rows data along with additional text.

procedure getClobDocument(document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT CLOB,
document_type IN OUT VARCHAR2)

IS
CURSOR cur_proj_invoice IS
select b.segment1 as proj_num,
a.customer_trx_id,
a.TRX_NUMBER,
trx_date,
a.CUSTOMER_REFERENCE,
b.INVOICE_COMMENT,
a.ATTRIBUTE15 as amount
from ra_customer_trx_all a, pa_projects_all b
where a.INTERFACE_HEADER_ATTRIBUTE1 = b.segment1
and rownum <= 2;

l_document VARCHAR2(32767);

cnt INTEGER;
l_docid pls_integer;
l_errmsg varchar2(100) := 'The Document is not found in the Database';
l_bdoc clob;

BEGIN

l_docid := to_number(document_id);

--l_docid := document_id;

dbms_lob.CreateTemporary(l_bdoc, FALSE, DBMS_LOB.CALL);

dbms_lob.createtemporary(document, FALSE, dbms_lob.CALL);

l_document := NULL;

l_document := ' ' || /*l_po_type*/
'Please review the below information for project ' ||
l_po_num || ' and provide your Approval. ';
l_document := l_document || 'Amount: INR ' || l_amt_total ||
' Tax Amount: INR ' || l_tax_amt ||
' Supplier: ' || l_VENDOR_NAME ||
' Supplier Site: ' || l_sup_site ||
' ';

l_document := l_document ||
'<table border="0" cellspacing="0" cellpadding="0" ><tr><td style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:11pt;font-weight:bold">Project Details </td></tr><tr><td><table width=100% border=0 cellpadding=0 cellspacing=0 ><tr><td>' ||
' ';
-- '<table width=80% border="0" cellspacing="0" cellpadding="0" ><tr><td style="color:black;font-family:Arial, Helvetica, Geneva, sans-serif;font-size:11pt;font-weight:bold">Release Shipment Details </td></tr><tr><td><table width=100% border=0 cellpadding=0 cellspacing=0 ><tr><td>';
l_document := l_document ||
'<table summary="" width=100% border=0 cellpadding=3 cellspacing=1 bgcolor=white >';
l_document := l_document ||
'<tr><th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE>Project No</th>';
l_document := l_document ||
'<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE>Customer Name.</th>';
l_document := l_document ||
'<th scope=col align=LEFT valign=baseline bgcolor= #7EC0EE>Project Lead.</th>';
l_document := l_document ||
'<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE>Project financial Analyst.</th>';
/*l_document := l_document ||
'<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE>CFC biller</th>';
l_document := l_document ||
'<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE>Quantity</th>';
l_document := l_document ||
'<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE>Unit Price</th>';*/
/*l_document := l_document ||
'<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE>Location</th></tr>';
l_document := l_document ||
'<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE>Ship-To Org</th>';
l_document := l_document ||
'<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE>Need-By Date</th></tr>';*/
l_document := l_document ||
'<th scope=col align=LEFT valign=baseline bgcolor=#7EC0EE>Amount</th></tr>';

FOR cnt IN cur_proj_invoice LOOP

l_document := l_document ||
'<tr><td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.proj_num || '</td>';
l_document := l_document ||
'<td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.customer_trx_id || '</td>';
l_document := l_document ||
'<td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.TRX_NUMBER || '</td>';
l_document := l_document ||
'<td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.trx_date || '</td>';
/* l_document := l_document ||
'<td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.CUSTOMER_REFERENCE || '</td>';
l_document := l_document ||
'<td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.INVOICE_COMMENT || '</td>';
l_document := l_document ||
'<td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.INVOICE_COMMENT || '</td>';*/
/* l_document := l_document ||
'<td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.location_code || '</td></tr>';
l_document := l_document ||
'<td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.name || '</td></tr>';
l_document := l_document ||
'<td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.need_by_date || '</td></tr>'; */
l_document := l_document ||
'<td align=LEFT valign=baseline bgcolor=#EEEEE0>' ||
cnt.INVOICE_COMMENT || '</td></tr>';
END LOOP;

l_document := l_document ||
'</table></td></tr></table></td></tr></table> ';

dbms_lob.writeappend(l_bdoc, length(l_document), l_document);

l_nm_clob_length := dbms_lob.getlength(l_bdoc);

dbms_lob.Copy(document, l_bdoc, l_nm_clob_length, 1, 1);

EXCEPTION
when others then
wf_core.context('XX_DEMO', 'getClobDocument', document_id);
raise;
end getClobDocument;

Oracle Application EBS Related Stuff

Wednesday, May 3, 2017

How to display table with multiple rows in a workflow notification along with additional text

No comments:

Post a Comment

Search This Blog

Featured Post

How to Enable AME for GL Journal Approval

Blog Archive

About Me

Total Pageviews

Contact Form