Escalation mechanism in Oracle workflow

For setting up escalation mechanism in workflow the following method could be followed.

Scenario: Notification goes for Approval to an approver. If the notification is not approved/rejected for 3 days, the notification is sent to the next level of approver.

1

SR reminder Approval Notification is the Notification sent for approval. Escalate if the function that handles the time out / escalation mechanism.

2

3

Following is the code for it.

PROCEDURE ESCALATE(ITEMTYPE  IN VARCHAR2,
ITEMKEY   IN VARCHAR2,
ACTID     IN NUMBER,
FUNCMODE  IN VARCHAR2,
RESULTOUT OUT VARCHAR2) is

p_shipment_header_id NUMBER;
l_reminder_count NUMBER;
p_req_header_id NUMBER;
p_approver_id NUMBER;
p_approver_name varchar2(50);

BEGIN

p_shipment_header_id := wf_engINe.getitemattrNUMBER(itemtype => itemtype,
itemkey  => itemkey,
aname    => ‘SHIPMENT_HEADER_ID’);

l_reminder_count := wf_engine.getitemattrNUMBER(itemtype => itemtype,
itemkey  => itemkey,
aname    => ‘REMINDER_COUNT’);

l_reminder_count := l_reminder_count + 1;

wf_engine.setitemattrnumber(ITEMTYPE,
ITEMKEY,
‘REMINDER_COUNT’,
l_reminder_count);

if l_reminder_count > 3 then

SELECT prl.requisition_header_id
INTO p_req_header_id
FROM rcv_transactions_interface rti,
po_distributions_all pod,
PO_REQ_DISTRIBUTIONS_ALL prd,
po_requisition_lines_all prl
WHERE rti.po_distribution_id = pod.po_distribution_id
AND pod.req_distribution_id  = prd.distribution_id
AND prd.REQUISITION_LINE_ID  = prl.REQUISITION_LINE_ID
AND rti.shipment_header_id   = p_shipment_header_id
AND rownum                   =1;

p_approver_id := MPL_PR_DH_GROUP_PKG.MPL_PR_DH_GROUP_APPROVER (p_req_header_id);

SELECT user_name INTO p_approver_name
from fnd_user where employee_id = p_approver_id;

wf_engine.setitemattrtext(ITEMTYPE,
ITEMKEY,
‘ESCALATION_ROLE’,
p_approver_name);

resultout := wf_engINe.eng_completed || ‘:’ || ‘Y’;

else
resultout := wf_engINe.eng_completed || ‘:’ || ‘N’;
END IF;

END ESCALATE;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s