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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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;