Oracle Technical Information: Work Flow Builder Tutorial for beginners

Work Flow Builder Tutorial for beginners
Learn Work Flow builder usage with step by step instructions.

Pre Requisite:

Work Flow Builder installed on your system.
A customized form where you want to implement it.

Scenario:
We start with a simple scenario, we have a module where when a document is completed should go for approval to concern department head. We have to pass the primary key to workflow and then a notification will be emailed to concerned department head and then we will update the document flag based on the primary key passed to the workflow on the selection of Approval/Reject button.

Package Used:

CREATE OR REPLACE PACKAGE APPS.cust_sample_notify
IS

PROCEDURE run_cust_wf (sample_id IN NUMBER);

PROCEDURE appr_sam ( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2);

PROCEDURE rej_sam ( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2);

PROCEDURE get_sample_details (
document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT NOCOPY CLOB,
document_type IN OUT NOCOPY VARCHAR2
);

END cust_sample_notify;
/

------------------------------------------------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY APPS.cust_sample_notify
IS

PROCEDURE run_cust_wf (sample_id IN NUMBER)
IS
itemtype VARCHAR2 (100) := 'NEW_TYPE';
itemkey VARCHAR2 (100)
:= sample_id || '_' || poc_wf_appr_seq.NEXTVAL;
wfprocess VARCHAR2 (100) := 'NEW_PROCESS';

p_user_id NUMBER;
p_requestor VARCHAR2 (100);
p_resp_id NUMBER;
p_appl_id NUMBER;
p_org_id NUMBER;
ERROR_CODE VARCHAR2 (100);
error_msg VARCHAR2 (1000);
vSampleDetails CLOB;
nl VARCHAR2 (10) := fnd_global.NEWLINE;

BEGIN
p_user_id := 1110;--fnd_profile.VALUE ('user_id');
p_resp_id := 21623; --fnd_profile.VALUE ('resp_id');
p_appl_id := 660; --fnd_profile.VALUE ('resp_appl_id');
p_org_id := 81;--fnd_profile.VALUE ('org_id');


SELECT user_name
INTO p_requestor
FROM fnd_user
WHERE user_id = p_user_id;

fnd_global.apps_initialize (p_user_id, p_resp_id, p_appl_id);
wf_engine.createprocess (itemtype => itemtype,
itemkey => itemkey,
process => wfprocess
);
wf_engine.SETITEMATTRTEXT ( itemtype => itemtype,
itemkey => itemkey,
aname => 'REQUESTOR',
avalue => p_requestor);

wf_engine.SETITEMATTRNUMBER ( itemtype => itemtype,
itemkey => itemkey,
aname => 'SAMPLEID',
avalue => sample_id);


vSampleDetails := 'PLSQL:CUST_SAMPLE_NOTIFY.GET_SAMPLE_DETAILS/' || sample_id;

wf_engine.SETITEMATTRTEXT ( itemtype => itemtype,
itemkey => itemkey,
aname => 'MSG',
avalue => vSampleDetails);

wf_engine.startprocess (itemtype => itemtype, itemkey => itemkey);

COMMIT;

EXCEPTION
WHEN OTHERS
THEN
ERROR_CODE := SQLCODE;
error_msg := SQLERRM (SQLCODE);
fnd_file.put_line (fnd_file.output, ERROR_CODE);
fnd_file.put_line (fnd_file.output, error_msg);
END run_cust_wf;

PROCEDURE appr_sam(
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2)
IS
BEGIN





UPDATE custusg.cust_sample_temp
SET flag = 'Y'
where id = wf_engine.GETITEMATTRTEXT ( itemtype => itemtype,
itemkey => itemkey,
aname => 'SAMPLEID'
);

COMMIT;

EXCEPTION
WHEN OTHERS
THEN
-- ERROR_CODE := SQLCODE;
-- error_msg := SQLERRM (SQLCODE);
fnd_file.put_line (fnd_file.output, SQLCODE);
fnd_file.put_line (fnd_file.output, SQLERRM (SQLCODE));
END ;



PROCEDURE rej_sam (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2)
IS
BEGIN

UPDATE custusg.cust_sample_temp
SET flag = 'N'
where id = wf_engine.GETITEMATTRTEXT ( itemtype => itemtype,
itemkey => itemkey,
aname => 'SAMPLEID'
);

COMMIT;
END;




PROCEDURE get_sample_details (
document_id IN VARCHAR2,
display_type IN VARCHAR2,
document IN OUT NOCOPY CLOB,
document_type IN OUT NOCOPY VARCHAR2
)
IS
l_body clob;
inq_id NUMBER;
nl VARCHAR2 (1) := fnd_global.NEWLINE;
l_sr_no NUMBER := 0;
--l_apprv VARCHAR2 (100);
BEGIN
document_type := 'text/html';
--
l_body := nl || nl ||'<P><B><U>';
l_body :=
l_body
|| '<TABLE border=0 cellpadding=0 cellspacing=0 <width=100%><TR>
<td align=LEFT valign=baseline ><font color=#336699 face="Arial, Helvetica, Geneva, sans-serif" size=2>'
|| '<u><b>'
|| 'POC Fabric Lines'
|| '</u></b>'
|| '</TD></TR></TABLE>'
|| nl;
l_body := l_body || '</B>'
|| nl
|| '</P>';
l_body :=
l_body
|| '<table cellspacing="2" ; cellpadding="5"; width=100%> <thead><tr>'
|| '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
|| 'SR No.'
|| '</TH>'
|| nl
|| '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
|| 'Item Description'
|| '</TH>'
|| nl
|| '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
|| 'UOM'
|| '</TH>'
|| nl
|| '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
|| 'Material Type'
|| '</TH>'
|| nl
|| '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
|| 'Quantity'
|| '</TH>'
|| nl
|| '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
|| 'Rate'
|| '</TH>'
|| nl
|| '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
|| 'Cost Amount'
|| '</TH>'
|| nl
|| '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
|| 'Amount (FCY)'
|| '</TH>'
|| nl
|| '<TH bgcolor="#d9e5ef"><font-family:Arial,Helvetica,Geneva,sans-serif; color:#000001><font size=2>'
|| 'Description / Remarks'
|| '</TH>'
|| nl
|| '</thead>
</tr>
<tbody>';

FOR i IN (SELECT rawmaterial_id,measure_unit_id,remarks,quantity
from cust_ont_sample_lines
WHERE PERCOSTING_ID = 104
order by percosting_id)
LOOP
BEGIN
l_sr_no := l_sr_no + 1;
l_body :=
l_body
|| '<tr> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
|| l_sr_no
|| '</td> <td nowrap align=left font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
|| i.rawmaterial_id
|| '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
|| i.measure_unit_id
|| '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
|| i.remarks
|| '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
|| i.quantity
|| '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
|| NULL
|| '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
|| NULL
|| '</td> <td nowrap align=middle font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
|| NULL
|| '</td> <td nowrap align=left font color=#000000 face="Arial, Helvetica, Geneva, sans-serif" bgcolor="#eeeeee"><font size=2>'
|| i.remarks
|| '</td> </tr> </tbody> ';
END;
END LOOP;

l_body := l_body || '</table>';
document := l_body;
--
--Setting document type which is nothing but MIME type
--
document_type := 'text/html';
EXCEPTION
WHEN OTHERS
THEN
document := '<H4>Error: ' || SQLERRM || '</H4>';
END get_sample_details;



END cust_sample_notify;
/

Tags: Oracle Work Flow Builder,

Oracle Technical Information

Monday 24 November 2014

Work Flow Builder Tutorial for beginners

No comments:

Post a Comment