SET PATH *LIBL ; 

CREATE OR REPLACE PROCEDURE SAILPOINT.REMOVE_ES_USER_FROM_ROLE ( 
	IN ENVID CHAR(2) , 
	IN USERNAME CHAR(10) DEFAULT  ''  , 
	IN USERROLE CHAR(10) DEFAULT  ''  ) 
	LANGUAGE SQL 
	SPECIFIC SAILPOINT.ES5312R1SP 
	NOT DETERMINISTIC 
	MODIFIES SQL DATA 
	CALLED ON NULL INPUT 
	PROGRAM TYPE SUB 
	SET OPTION  ALWBLK = *ALLREAD , 
	ALWCPYDTA = *OPTIMIZE , 
	COMMIT = *NONE , 
	DECRESULT = (31, 31, 00) , 
	DYNDFTCOL = *NO , 
	DYNUSRPRF = *USER , 
	SRTSEQ = *HEX   
	BEGIN 
DECLARE AUDIT_FLAG CHAR ( 1 ) DEFAULT '' ; 
DECLARE LIBL VARCHAR ( 1000 ) DEFAULT '' ; 
SET LIBL = CISTOOLS . SET_LIBRARY_LIST ( ENVID ) ; 
CALL SAILPOINT . REMOVE_ES_USER_ROLE_RECORD ( USERROLE , USERNAME ) ; 
CALL SAILPOINT . CHECK_ES_AUDIT_FLAG ( AUDIT_FLAG ) ; 
IF AUDIT_FLAG = 'Y' THEN 
INSERT INTO ESDETAIL ( 
ESENVD , ESBUCD , ESUGID , ESSKXX , ESBTXX , ESACTN , ESREAS , 
ESDATE , ESTIME , ESUSER , ESPGM , ESREF ) 
SELECT ENVID ,  -- ESENVD - environment, 
USERNAME ,  -- ESBUCD - user being removed 
RG . RGGRUP ,  -- ESUGID - group id 
MB . BRSKXX ,  -- ESSKXX - task id 
MB . BRBTXX ,  -- ESBTXX - subtsk 
'R' ,  -- ESACTN - action (Remove) 
'13' ,  -- ESREAS - reason (user removed from role) 
CURRENT_DATE ,  -- ESDATE 
CURRENT_TIME ,  -- ESTIME 
CURRENT_USER ,  -- ESUSER - user making change 
'ES5312R1SP' ,  -- ESPGM  - program name 
''  -- ESREF  - reference id 
FROM ESROGP RG 
INNER JOIN MZBRRES0 MB 
ON MB . BRBUCD = RG . RGGRUP 
WHERE RG . RGROLE = USERROLE 
-- Only include groups that this user won't have through other roles 
AND NOT EXISTS ( 
SELECT 1 
FROM ESROGL1 R2 
INNER JOIN ESUSRL2 U2 
ON U2 . U2ROLE = R2 . G1ROLE 
WHERE R2 . G1GRUP = RG . RGGRUP 
AND R2 . G1ROLE <> USERROLE 
AND U2 . U2USER = USERNAME  -- Same user 
) ; 
END IF ; 
DELETE FROM USRGRPL0 WHERE BUCDD7 = USERNAME 
AND UGIDD7 IN ( SELECT RG . RGGRUP 
FROM ESROGP RG 
WHERE RG . RGROLE = USERROLE 
AND NOT EXISTS ( 
-- Check if user has this group through another role 
SELECT 1 
FROM ESROGL1 R2 
INNER JOIN ESUSRL2 U2 
ON U2 . U2ROLE = R2 . G1ROLE 
WHERE R2 . G1GRUP = RG . RGGRUP 
AND R2 . G1ROLE <> USERROLE  -- Different role 
AND U2 . U2USER = USERNAME  -- Same user 
) ) ; 
DELETE FROM ESUSRL2 WHERE U2ROLE = USERROLE 
AND U2USER = USERNAME ; 
DELETE FROM ESUSRP WHERE EUUSER = USERNAME 
AND EUROLE = USERROLE ; 
END  ; 

GRANT ALTER , EXECUTE   
ON SPECIFIC PROCEDURE SAILPOINT.ES5312R1SP 
TO AMAPICS WITH GRANT OPTION ; 

GRANT EXECUTE   
ON SPECIFIC PROCEDURE SAILPOINT.ES5312R1SP 
TO PUBLIC ;