Prerequisite for Sample WF Design:

Prerequisite for Sample WF Design:

Table Creation Script:

CREATE TABLE APPS.XXXX_PO_HEADERS
(
PO_ID NUMBER,
PO_DESCRIPTION VARCHAR2(240 BYTE),
PO_STATUS VARCHAR2(50 BYTE),
SEND_EMAIL_TO VARCHAR2(1000 BYTE)
);
Package Creation Script:

CREATE OR REPLACE PACKAGE apps.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 OR REPLACE PACKAGE BODY apps.xxxx_po_wf_training_pkg
IS
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;

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,
LANGUAGE => NULL,
territory => NULL,
role_description => 'ROLE DESC',
notification_preference => 'MAILHTML',
role_users => NULL
-- Comma or space delimited list
,
email_address => v_role_email,
fax => NULL,
status => 'ACTIVE',
expiration_date => NULL,
parent_orig_system => NULL,
parent_orig_system_id => NULL,
owner_tag => 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) := 'WRK_TRG';
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, 'PO_TRG');
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_REJECT'
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;
/