Following API can replace username of specific workflow type through one execution which reduce the manual updating work of workflow.
Sometimes some users are end dated for any reason during that time if workflow is not assigned to any responsible active user then workflow might be going in error so through this API can be replace end dated user with new active user in frequent of second.
DECLARE
REPLACE_VALUE VARCHAR2 (100) := 'TOUSERNAME';
ITEM_TYPE VARCHAR2 (10) := 'ITEMTYEPOFWORKFLOW';
USER_NAME VARCHAR2 (100) := 'USERWHOWANTCHANGE';
CURSOR LCU_GET_ITEM_KEY
IS
SELECT A.ITEM_KEY
FROM WF_ITEM_ATTRIBUTE_VALUES a
WHERE a.name = '#FROM_ROLE' AND a.text_value = REPLACE_VALUE
AND EXISTS
(SELECT ITEM_KEY
FROM WF_ITEM_ACTIVITY_STATUSES
WHERE ITEM_KEY = A.ITEM_KEY
AND ITEM_TYPE = A.ITEM_TYPE
AND ACTIVITY_STATUS != 'COMPLETE');
BEGIN
FOR L_GET_ITEM_KEY IN LCU_GET_ITEM_KEY
LOOP
WF_ENGINE.SETITEMATTRTEXT (ITEMTYPE => ITEM_TYPE,
ITEMKEY => L_GET_ITEM_KEY.ITEM_KEY,
ANAME => '#FROM_ROLE',
AVALUE => USER_NAME);
DBMS_OUTPUT.PUT_LINE (L_GET_ITEM_KEY.ITEM_KEY);
END LOOP;
COMMIT;
END;