/* Access Control Static Permissions */
SELECT co.obj_name, perm.perm_id, perm.perm_value
  FROM (
  
  	    (
  	    /* All 'Y' User Permissions */	  
		
		/* Get all 'Y' permissions */
		SELECT DISTINCT rp.OBJ_ID, rp.PERM_ID, rp.PERM_VALUE
		  FROM USER_ROLES ur,
		  	   ROLE_PRIVILEGES rp,
			   ROLES ro
         WHERE ur.ROLE_ID = ro.ROLE_ID
		   AND rp.ROLE_ID = ro.ROLE_ID
		   AND ur.USER_ID = :USER_ID	
		   AND rp.PERM_VALUE = 'Y'	
		   AND ro.APP_ID = :APP_ID	   
		MINUS
		/* Revoke permissions if they set to 'N'*/
		SELECT DISTINCT rp.OBJ_ID, rp.PERM_ID, 'Y' AS PERM_VALUE
		  FROM USER_ROLES ur,
		  	   ROLE_PRIVILEGES rp,
			   ROLES ro
         WHERE ur.ROLE_ID = ro.ROLE_ID
		   AND rp.ROLE_ID = ro.ROLE_ID
		   AND ur.USER_ID = :USER_ID	
		   AND rp.PERM_VALUE = 'N'	
		   AND ro.APP_ID = :APP_ID
		   
		)
		
		MINUS    
		
		/* Role Variant Revokes */   
		SELECT DISTINCT rp.OBJ_ID, rp.perm_id, 'Y' AS perm_value
		  FROM role_privileges rp,
		       ROLES ro,
		       user_roles ur
		 WHERE ro.role_id = rp.role_id
		   AND ro.role_id = ur.role_id
		   AND rp.PERM_VALUE = 'N'
		   AND ro.IS_ROLE_VARIATION = 'Y'
		   AND ur.user_id = :USER_ID
		   AND ro.APP_ID = :APP_ID   
		   
  	   ) perm,
       control_objects co
       --page_control_objects pco,
       --application_pages ap	 
 WHERE perm.obj_id = co.obj_id
 
  -- AND ap.APP_ID = co.APP_ID
  -- AND ap.APP_ID = :APP_ID
   --AND pco.obj_id = co.obj_id
   --AND pco.PAGE_ID = ap.PAGE_ID
   --AND ap.page_name = :PAGE_NAME 
   /* Return uncomented lines to include use of APPLICATION_PAGES table */