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; /