This work is licensed under a Creative Commons Attribution-NonCommercial 2.5 License.


Scripts for Oracle Workflow Training Lesson 3



The wft file for Oracle Workflow Training Lesson 3 can be downloaded by right clicking here

It is assumed that you have already worked upon the scripts from Training lesson 2.

--Create the table in which transaction progress will be tracked
create table xx_po_wf_progress
(
po_id INTEGER
,p_wf_action_stage VARCHAR2(200)
,p_wf_action_name VARCHAR2(200)
) ;

--Create the pl/sql package
CREATE OR REPLACE PACKAGE xxxx_po_wf_training_pkg IS
PROCEDURE is_po_valid(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE start_training_wf(p_po_id IN INTEGER);

PROCEDURE xx_validate_response(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

PROCEDURE log_status_of_action(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2);

END xxxx_po_wf_training_pkg;
/

--create the package body
CREATE OR REPLACE PACKAGE BODY xxxx_po_wf_training_pkg IS

PROCEDURE debug(p_text IN VARCHAR2) IS
BEGIN
-- apassi1(p_text);
NULL;
END debug;

FUNCTION is_po_validated(p_po_id IN INTEGER) RETURN BOOLEAN IS
BEGIN
IF p_po_id > 0
THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END is_po_validated;

PROCEDURE is_po_valid(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
BEGIN
IF (funcmode != 'RUN')
THEN
RETURN;
END IF;
--Just a pseudo validation
--call a function here, and either assign COMPLETE:Y or COMPLETE:N

IF is_po_validated(p_po_id => wf_engine.getitemattrnumber(itemtype => itemtype
,itemkey => itemkey
,aname => 'PO_ID'))
THEN
RESULT := 'COMPLETE:Y';
ELSE
RESULT := 'COMPLETE:N';
END IF;
END is_po_valid;

PROCEDURE set_wf_approver_role(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
v_role_email xxxx_po_headers.send_email_to%TYPE;
n_ctr INTEGER := 0;
BEGIN
v_role_email := upper(wf_engine.getitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'SEND_TO_EMAIL'));
SELECT COUNT(*)
INTO n_ctr
FROM wf_local_roles
WHERE NAME = v_role_email;

IF n_ctr = 0
THEN
wf_directory.createadhocrole(role_name => v_role_email
,role_display_name => v_role_email
,role_description => v_role_email
,notification_preference => 'MAILHTML'
,email_address => v_role_email
,status => 'ACTIVE'
,expiration_date => NULL);
END IF;
wf_engine.setitemattrtext(itemtype => itemtype
,itemkey => itemkey
,aname => 'SEND_TO_ROLE'
,avalue => v_role_email);
RESULT := 'COMPLETE:Y';

RESULT := 'COMPLETE:Y';
END set_wf_approver_role;

PROCEDURE set_wf_status_to_validated(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
BEGIN
UPDATE xxxx_po_headers
SET po_status = 'VALIDATED'
WHERE po_id = wf_engine.getitemattrnumber(itemtype
,itemkey
,'PO_ID');
RESULT := 'COMPLETE:Y';
END set_wf_status_to_validated;

PROCEDURE start_training_wf(p_po_id IN INTEGER) IS
l_itemtype VARCHAR2(30) := 'XXXXPTR';
l_itemkey VARCHAR2(300) := 'TRAINING-' || p_po_id;
CURSOR c_get IS
SELECT * FROM xxxx_po_headers WHERE po_id = p_po_id;
p_get c_get%ROWTYPE;
BEGIN
OPEN c_get;
FETCH c_get
INTO p_get;
CLOSE c_get;

wf_engine.createprocess(l_itemtype
,l_itemkey
,'MAIN_TRAINING_PROCESS');

wf_engine.setitemuserkey(itemtype => l_itemtype
,itemkey => l_itemkey
,userkey => 'USERKEY: ' || l_itemkey);
wf_engine.setitemowner(itemtype => l_itemtype
,itemkey => l_itemkey
,owner => 'SYSADMIN');

wf_engine.setitemattrnumber(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'PO_ID'
,avalue => p_po_id);

wf_engine.setitemattrtext(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'SEND_TO_EMAIL'
,avalue => p_get.send_email_to);
wf_engine.setitemattrtext(itemtype => l_itemtype
,itemkey => l_itemkey
,aname => 'PO_DESCRIPTION'
,avalue => p_get.po_description);
wf_engine.startprocess(l_itemtype
,l_itemkey);

END start_training_wf;

PROCEDURE xx_validate_response(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
l_nid NUMBER;
l_activity_result_code VARCHAR2(200);
v_response_reason VARCHAR2(50);
BEGIN
IF (funcmode IN ('RESPOND'))
THEN
l_nid := wf_engine.context_nid;
l_activity_result_code := wf_notification.getattrtext(l_nid
,'XX_RESPONSE_ACTION');
v_response_reason := wf_notification.getattrtext(l_nid
,'XX_RESPONSE_REASON');
IF l_activity_result_code = 'XX_REJECTED' AND
v_response_reason IS NULL
THEN
RESULT := 'ERROR: You must enter rejection reason if rejecting.';
RETURN;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RESULT := SQLERRM;
END xx_validate_response;

FUNCTION get_request_id_from_item_key(p_item_key IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
RETURN substr(p_item_key
,instr(p_item_key
,'-') + 1);
END get_request_id_from_item_key;

PROCEDURE log_status_of_action(itemtype IN VARCHAR2
,itemkey IN VARCHAR2
,actid IN NUMBER
,funcmode IN VARCHAR2
,RESULT IN OUT VARCHAR2) IS
n_po_id INTEGER;
v_action_name VARCHAR2(200);
v_action_stage VARCHAR2(200);
BEGIN
IF funcmode = 'RUN'
THEN
n_po_id := get_request_id_from_item_key(itemkey);

v_action_stage := wf_engine.getactivityattrtext(itemtype => itemtype
,itemkey => itemkey
,actid => actid
,aname => 'ACTION_STAGE');

v_action_name := wf_engine.getactivityattrtext(itemtype => itemtype
,itemkey => itemkey
,actid => actid
,aname => 'ACTION_NAME');
INSERT INTO xx_po_wf_progress
(po_id
,p_wf_action_stage
,p_wf_action_name)
VALUES
(n_po_id
,v_action_stage
,v_action_name);
END IF;
RESULT := 'COMPLETE:Y';
END log_status_of_action;

END xxxx_po_wf_training_pkg;
/

--initiate the workflow now
DECLARE
n_new_po_id INTEGER;
BEGIN
SELECT oe_order_headers_s.NEXTVAL INTO n_new_po_id FROM dual;
INSERT INTO xxxx_po_headers
(po_id
,vendor_id
,suggested_vendor_id
,po_description
,po_status
,comments_from_approver
,send_email_to)
VALUES
(n_new_po_id --po_id
,10000 --vendor_id
,NULL --suggested_vendor_id
,'This is PO Training Description' --po_description
,'INITIAL' --po_status
,NULL --comments_from_approver
,'ANILPASSI@GMAIL.com' --send_email_to
);
xxxx_po_wf_training_pkg.start_training_wf(p_po_id => n_new_po_id);
COMMIT;
END;
/

Comments on ""

 

post a comment