平凡的幸福(備站）

CREATE OR REPLACE PROCEDURE APPS.DELETE_USER_RESP (p_userID IN NUMBER, p_respID IN NUMBER) IS
BEGIN
    FOR RS IN (
       SELECT FU.USER_NAME,
                       FR.RESPONSIBILITY_KEY
          FROM WF_LOCAL_USER_ROLES WLUR
                    , FND_USER   FU
                    , FND_RESPONSIBILITY  FR
          WHERE WLUR.ROLE_NAME LIKE 'FND_RESP|%' || FR.RESPONSIBILITY_KEY || '|%'
                AND WLUR.USER_NAME= FU.USER_NAME
                AND FU.USER_ID = p_userID
                AND FR.RESPONSIBILITY_ID = p_respID
    ) LOOP
   
        DELETE FROM WF_LOCAL_USER_ROLES
            WHERE USER_NAME=RS.USER_NAME
                 AND (ROLE_NAME LIKE 'FND_RESP%' || RS.RESPONSIBILITY_KEY || '%'
                    OR ROLE_NAME LIKE 'FND_RESP%' || p_respID || '%')
                 AND ROLE_ORIG_SYSTEM LIKE 'FND_RESP%'
                 AND ROLE_ORIG_SYSTEM_ID=p_respID;

        DELETE FROM WF_USER_ROLE_ASSIGNMENTS
            WHERE USER_NAME=RS.USER_NAME
                 AND (ROLE_NAME LIKE 'FND_RESP%' || RS.RESPONSIBILITY_KEY || '%'
                    OR ROLE_NAME LIKE 'FND_RESP%' || p_respID || '%')
                AND ROLE_ORIG_SYSTEM LIKE 'FND_RESP%'
                AND ROLE_ORIG_SYSTEM_ID=p_respID;

    END LOOP;

COMMIT;

EXCEPTION
WHEN OTHERS THEN
    ROLLBACK;
    COMMIT;
    RAISE_APPLICATION_ERROR(-20001, 'Unable to delete this responsiiblity from user.');
END;