Respond to workflow notification from a DB procedure

The  below script can be used to auto approve notifications through database procedure.

Write your business logic with in the below procedure ,register it as a concurrent program and schedule it as needed to run periodically. The same script can also be used in custom forms, custom or third party applications to respond to notifications from backend procedure.

PROCEDURE approve_reject_proc (pAction IN VARCHAR2, --APPROVE/REJECT

pComments IN VARCHAR2, -- User Comments -- Optional

pNotification_id IN NUMBER, -- Notification ID

pStatus OUT VARCHAR2,

pMessage OUT VARCHAR2

)

IS

l_user_name VARCHAR2(250);

BEGIN

BEGIN

SELECT RECIPIENT_ROLE

INTO l_user_name

FROM WF_NOTIFICATIONS

WHERE notification_id = pNotification_id;

EXCEPTION

WHEN OTHERS THEN

l_user_name := NULL;

END;

wf_notification.SETATTRTEXT(pNotification_id,'RESULT',pAction);

wf_notification.Respond(pNotification_id,

pComments,

l_user_name,

pAction);

pStatus := 'S';

pMessage := 'Successfully '||initcap(pAction);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

pStatus := 'E';

pMessage := 'Unexpected error while Approve/Reject the notification: '||SQLERRM;

END approve_reject_proc;