/* Role Object Pairs for User Permissions */
SELECT DISTINCT ro.ROLE_ID,
       rp.OBJ_ID
  FROM ROLES ro,
  	   USER_ROLES ur,
       ROLE_PRIVILEGES rp
 WHERE ur.ROLE_ID = ro.ROLE_ID
   AND rp.ROLE_ID = ro.ROLE_ID	
   AND ro.APP_ID = :APP_ID
   AND ur.USER_ID = :USER_ID 


MINUS

/* Take away roles overwritten by role variation */
SELECT DISTINCT ro.ROLE_ID,
       rp.OBJ_ID
  FROM ROLES ro,
  	   USER_ROLES ur,
       ROLE_PRIVILEGES rp,
	   (
	    /* Objects for Role Variation Only */
		SELECT rp.OBJ_ID
		  FROM ROLES ro,
		  	   USER_ROLES ur,
			   ROLE_PRIVILEGES rp
		 WHERE ur.ROLE_ID = ro.ROLE_ID
		   AND rp.ROLE_ID = ro.ROLE_ID	
		   AND ro.APP_ID = :APP_ID
		   AND ur.USER_ID = :USER_ID
		   AND ro.IS_ROLE_VARIATION = 'Y'
	   ) rvco
 WHERE ur.ROLE_ID = ro.ROLE_ID
   AND rp.ROLE_ID = ro.ROLE_ID	
   AND ro.APP_ID = :APP_ID
   AND ur.USER_ID = :USER_ID
   AND rp.OBJ_ID = rvco.OBJ_ID
   AND ro.IS_ROLE_VARIATION IS NULL

