/* User by Packages in this release */
SELECT DECODE( ev.PKG_STATE, NULL, 0, ev.PKG_STATE ) AS PKG_STATE,
	   pv.PV_ID,
	   pkg.PKG_NAME,
       pv.PKG_VERSION,
	   pv.DLOCKED,
	   pv.BUILD_TYPE,
	   igw.PV_ID AS IGNORE_WARN,
	   igw.IS_PATCH_IGNORE,
	   dpv.PV_ID AS DPV_ID,
	   dpkg.PKG_NAME AS DPKG_NAME,
	   dpv.PKG_VERSION AS DPKG_VERSION,
	   ev.ENV_AREA,
	   vi.VIEW_NAME,
	   usr.FULL_NAME,
	   usr.USER_EMAIL,
	   pv.MODIFIED_STAMP  
  FROM ENVIRONMENT_VIEW ev,
  	   PACKAGE_DEPENDENCIES dep,
	   PACKAGE_VERSIONS dpv,
	   PACKAGE_VERSIONS pv,
	   PACKAGES pkg,
	   PACKAGES dpkg,
	   USERS usr,
	   VIEWS vi,
	   ( 
	   
	     SELECT iw.PV_ID, iw.DPV_ID, iw.IS_PATCH_IGNORE
	       FROM IGNORE_WARNINGS iw 
		  WHERE iw.RTAG_ID = :RTAG_ID 
		  
	   ) igw	   
 WHERE ev.RTAG_ID = :RTAG_ID
   AND ev.PV_ID = dep.PV_ID
   AND dep.DPV_ID = dpv.PV_ID
   AND dpv.PKG_ID = :PKG_ID
   AND NVL( dpv.V_EXT, '|LINK_A_NULL|') = NVL( :V_EXT, '|LINK_A_NULL|')
   AND dep.PV_ID = pv.PV_ID
   AND pv.PKG_ID = pkg.PKG_ID
   AND dpv.PKG_ID = dpkg.PKG_ID
   AND ev.VIEW_ID = vi.VIEW_ID 
   AND pv.MODIFIER_ID = usr.USER_ID
   AND igw.PV_ID (+) = dep.PV_ID
   AND igw.DPV_ID (+) = dep.DPV_ID
 ORDER BY UPPER(pkg.PKG_NAME)    
   