/* User Override Privileges */
SELECT roc.OBJ_ID, 
     roc.TYPE,
	   roc.OBJ_NAME,
	   roc.OBJ_DESCRIPTION,
	   perm_active.PERM_ID AS perm_active_id,
	   perm_active.PERM_VALUE AS active_value,
	   perm_visible.PERM_ID AS perm_visible_id,
	   perm_visible.PERM_VALUE AS visible_value,
	   DECODE ( data_filter.obj_id, NULL, 'LIMG_DATA_TABLE_OFF', 'LIMG_DATA_TABLE' ) AS data_filter
  FROM 
	   (
	   
	    SELECT DISTINCT
			   co.OBJ_ID,
			   co.OBJ_NAME,
			   co.OBJ_DESCRIPTION, 
         NVL2(rp.ROLE_ID, 'LIMG_SPEC_ROLE','LIMG_CONTROL')  as TYPE
		  FROM ROLE_PRIVILEGES rp,
		  	   CONTROL_OBJECTS co
		 WHERE rp.OBJ_ID (+)= co.OBJ_ID 
		   AND rp.ROLE_ID (+)= :ROLE_ID
		   AND co.APP_ID = :APP_ID

		 UNION  
		 
		/* Select data filters even if they are not set in role_privileges */ 
		SELECT DISTINCT co.OBJ_ID,
			   co.OBJ_NAME,
			   co.OBJ_DESCRIPTION, 'LIMG_CONTROL' as type
		  FROM DATA_PERMISSIONS dp,
		  	   DATA_TABLES dt,
		  	   CONTROL_OBJECTS co
		 WHERE dp.DT_ID = dt.DT_ID
		   AND dt.obj_id = co.obj_id
		   AND dp.ROLE_ID = :ROLE_ID 
		   AND co.APP_ID = :APP_ID
		   
		   
	   ) roc,
	   (
	   
	   	SELECT co.OBJ_ID,
			   rp.PERM_ID,
			   rp.PERM_VALUE
		  FROM ROLE_PRIVILEGES rp,
		  	   CONTROL_OBJECTS co
	     WHERE rp.OBJ_ID = co.obj_id
		   AND rp.ROLE_ID = :ROLE_ID
		   AND rp.PERM_ID = 1 
		   AND co.APP_ID = :APP_ID
		  
	   ) perm_visible,
	   (
	   
	   	SELECT co.OBJ_ID,
			   rp.PERM_ID,
			   rp.PERM_VALUE
		  FROM ROLE_PRIVILEGES rp,
		  	   CONTROL_OBJECTS co
	     WHERE rp.OBJ_ID = co.obj_id
		   AND rp.ROLE_ID = :ROLE_ID
		   AND rp.PERM_ID = 2 
		   AND co.APP_ID = :APP_ID
		  
	   ) perm_active,
	   (
	   
	    /* Get Data Filters for this role */
		SELECT DISTINCT 
			   co.OBJ_ID
		  FROM DATA_TABLES dt,
		  	   DATA_PERMISSIONS dp,
			   CONTROL_OBJECTS co
	     WHERE dp.DT_ID = dt.DT_ID
		   AND dt.OBJ_ID = co.OBJ_ID
		   AND co.APP_ID = :APP_ID
		   AND dp.ROLE_ID = :ROLE_ID
		   AND dp.REF_COLUMN_VAL != 0
	   
	   ) data_filter
	   
 WHERE perm_active.obj_id (+)= roc.obj_id
   AND perm_visible.obj_id (+)= roc.obj_id
   AND data_filter.obj_id (+) = roc.obj_id
   
ORDER BY UPPER(roc.OBJ_NAME)   

