SELECT pkg.pkg_name, comp.*, usr.FULL_NAME, usr.user_email
  FROM PACKAGES pkg, 
  	   USERS usr,
  	   (   
		SELECT dpv.pv_id AS pv_id, -1 AS old_pv_id, dpv.pkg_id, NULL AS pkg_version, dpv.pkg_version AS previous_version, 2 AS status, dpv.MODIFIER_ID 
		  FROM PACKAGE_VERSIONS dpv,
		  	   PACKAGE_DEPENDENCIES dep,
		       (
		        SELECT olddpv.pv_id AS previous_pv_id, newdpv.pv_id AS current_pv_id 
				  FROM PACKAGE_VERSIONS newdpv,
				  	   PACKAGE_DEPENDENCIES newdep,
				       PACKAGE_VERSIONS olddpv,
				  	   PACKAGE_DEPENDENCIES olddep
				 WHERE newdep.DPV_ID = newdpv.PV_ID 
				   AND olddep.DPV_ID = olddpv.PV_ID
				   AND newdep.pv_id = :PV_ID
				   AND olddep.pv_id = :OLD_PV_ID
				   AND newdpv.pkg_id = olddpv.pkg_id 
				   AND NVL( newdpv.v_ext , 'LINK_A_NULL' ) = NVL( olddpv.v_ext, 'LINK_A_NULL' )
		       ) diff
		 WHERE dep.DPV_ID = dpv.PV_ID
		   AND dep.pv_id = :OLD_PV_ID
		   AND dpv.pv_id = diff.previous_pv_id (+)  
		   AND (diff.current_pv_id IS NULL OR diff.previous_pv_id IS NULL)
		UNION
		SELECT dpv.pv_id AS pv_id, 0 AS old_pv_id, dpv.pkg_id, dpv.pkg_version, NULL AS previous_version, 3 AS status, dpv.MODIFIER_ID
		  FROM PACKAGE_VERSIONS dpv,
		  	   PACKAGE_DEPENDENCIES dep,
		       (
		        SELECT olddpv.pv_id AS previous_pv_id, 
				       newdpv.pv_id AS current_pv_id 
				  FROM PACKAGE_VERSIONS newdpv,
				  	   PACKAGE_DEPENDENCIES newdep,
				       PACKAGE_VERSIONS olddpv,
				  	   PACKAGE_DEPENDENCIES olddep
				 WHERE newdep.DPV_ID = newdpv.PV_ID 
				   AND olddep.DPV_ID = olddpv.PV_ID
				   AND newdep.pv_id = :PV_ID
				   AND olddep.pv_id = :OLD_PV_ID
				   AND newdpv.pkg_id = olddpv.pkg_id 
				   AND NVL( newdpv.v_ext , 'LINK_A_NULL' ) = NVL( olddpv.v_ext, 'LINK_A_NULL' )
		       ) diff
		 WHERE dep.DPV_ID = dpv.PV_ID
		   AND dep.pv_id = :PV_ID
		   AND dpv.pv_id = diff.current_pv_id (+)  
		   AND (diff.current_pv_id IS NULL OR diff.previous_pv_id IS NULL)   
		UNION
		SELECT newdpv.pv_id AS pv_id,
		       olddep.dpv_id AS old_pv_id, 
			   newdpv.pkg_id, newdpv.pkg_version,
			   DECODE( newdpv.pv_id, olddpv.pv_id, NULL, olddpv.pkg_version ) AS previous_version,
			   DECODE( newdpv.pv_id, olddpv.pv_id, 0, 1 ) AS status, newdpv.MODIFIER_ID
		  FROM PACKAGE_VERSIONS newdpv,
		  	   PACKAGE_DEPENDENCIES newdep,
		       PACKAGE_VERSIONS olddpv,
		  	   PACKAGE_DEPENDENCIES olddep
		 WHERE newdep.DPV_ID = newdpv.PV_ID 
		   AND olddep.DPV_ID = olddpv.PV_ID
		   AND newdep.pv_id = :PV_ID
		   AND olddep.pv_id = :OLD_PV_ID
		   AND newdpv.pkg_id = olddpv.pkg_id 
		   AND NVL( newdpv.v_ext , 'LINK_A_NULL' ) = NVL( olddpv.v_ext, 'LINK_A_NULL' )
		) comp
 WHERE comp.pkg_id = pkg.pkg_id  
   AND comp.modifier_id = usr.user_id
ORDER BY UPPER(pkg.pkg_name) 