Scripts for Workflow Training Lesson 2 This page contains all the required Scripts and Workflow files prepared for Workflow Training Lesson 2. Click here if you wish to return to Oracle Workflow Training Lesson 2 Workflow .wft File For Training Lesson 2 Rightclick to download the above file and save this to database. --create the table Create table xxxx_po_headers ( po_id INTEGER ,vendor_id INTEGER ,suggested_vendor_id INTEGER ,po_description VARCHAR2(250) ,po_status VARCHAR2(30) ,comments_from_approver VARCHAR2(2000) ,send_email_to VARCHAR2(300) ) ; ----------------- --Create the 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 ); 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 debug('funcmode is ' || funcmode); IF (funcmode IN ('RESPOND')) THEN l_nid := wf_engine.context_nid; l_activity_result_code := wf_notification.getattrtext(l_nid ,'XX_RESPONSE_ACTION'); debug('l_activity_result_code is ' || l_activity_result_code); v_response_reason := wf_notification.getattrtext(l_nid ,'XX_RESPONSE_REASON'); debug('v_response_reason is ' || v_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; END xxxx_po_wf_training_pkg; / ------ --Now Run the Workflow --Ensure that wft file has been loaded ------ 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; /