Oracle Application EBS Related Stuff: May 2017

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;

1) open workflow builder tool and connect to data base.

2) search for "standard" workflow

3) drag this to left hand side as below and click ok.

4)Right click on standard and click on new then Item type

5) give name and description And save

6)right click on process and New process. give details and save.

7) right click on attribute and new attribute. give details and save.

8) right click on message and new message. give all details and save.

9) go to result tab of the above window. select this details and save.

10) after creating message.Add comments attribute from attribute and paste it in the message and select attribute as respond type.

11) click on notification and new notification. give details and save.in this window select function as pl sql package and procedure name where we will write logic to make comments field mandatory.

12) Click on process, it will open blank window like below

13) copy start and end function from standard workflow and paste it under custom workflow function section.

drag both function to the process window

14) drag notification also in this window and connect these icons with each other as below and apply:

15) click on start activity and go to node tab. select value as start from drop down as below:

16) click on End activity and go to node tab. select value as end from drop down as below and Apply:

17) now workflow builder part is finished. Now we will see pl/sql part. first create sequence named as XX_SEQ in the data base which will be used to store item key value. This is the code below

CREATE OR REPLACE PACKAGE BODY XX_DEMO IS
PROCEDURE LAUNCH_WORKFLOW
IS
v_master_form_link varchar2(5000);
v_item_number varchar2(100);
v_add_item_id varchar2(5000);
error_code varchar2(100);
error_msg varchar2(5000);
v_item_key VARCHAR2(10) := to_char(xx_SEQ.nextval);
v_item_type varchar2(10) := 'XX_TEST';
v_process varchar2(20) := 'XX_PROC';
BEGIN

WF_ENGINE.CREATEPROCESS(v_item_type, v_item_key, v_process);

WF_ENGINE.STARTPROCESS(v_item_type, v_item_key);
commit;

EXCEPTION
WHEN OTHERS THEN
error_code := SQLCODE;
error_msg := SQLERRM(SQLCODE);
-- add dbms or fnd_output messages as required
END LAUNCH_WORKFLOW;

procedure check_comment_reject(ITEMTYPE IN VARCHAR2,
ITEMKEY IN VARCHAR2,
ACTID IN NUMBER,
FUNCMODE IN VARCHAR2,
RESULTOUT IN OUT VARCHAR2) is
l_notfication_result varchar2(1000);
l_sup_comments varchar2(10000);

begin

if (FUNCMODE = 'RESPOND') then
l_notfication_result := wf_notification.getAttrText(wf_engine.context_nid,
'RESULT');
l_sup_comments := wf_notification.GetAttrText(nid => wf_engine.context_nid,
aname => 'XX_COMMENT');

if (l_notfication_result = 'REJECTED') then

if l_sup_comments is null then
raise_application_error('-20002',
'Comments are mandatory while rejecting request');
else
RESULTOUT := 'COMPLETE:REJECT';
end if;
elsif (l_notfication_result = 'APPROVED') then
RESULTOUT := 'COMPLETE:APPROVE';
end if;
WF_ENGINE.SetItemAttrText(ITEMTYPE,
ITEMKEY,
'XX_COMMENT',
l_sup_comments);
end if;
exception
when others then
wf_core.context('XX_DEMO',
'check_comment_reject',
ITEMTYPE,
ITEMKEY,
ACTID,
FUNCMODE);
raise;

end;
END XX_DEMO;

Now if you are facing any difficulty, please put in comments, will respond you on the same.

Oracle Application EBS Related Stuff

Wednesday, May 3, 2017

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

How to make comments field mandatory for an approver in workflow notification

Search This Blog

Featured Post

How to Enable AME for GL Journal Approval

Blog Archive

About Me

Total Pageviews

Contact Form