/* ReleaseManager Products Unused Patches */
SELECT pv.PV_ID AS PROD_ID,
	   pkg.PKG_NAME,
	   pv.PKG_VERSION,
	   op.INSTALL_ORDER
  FROM PACKAGES pkg,
	   PACKAGE_VERSIONS pv,  		
	   (       
	   	/* All patches from RM for all Products in OS */
	    SELECT pp.PV_ID, pp.PATCH_ID
		  FROM OS_CONTENTS osc,
		  	   PACKAGE_PATCHES pp,
               PACKAGE_VERSIONS pv,
			   PACKAGE_VERSIONS ppv
		 WHERE osc.PROD_ID = pp.PV_ID 
           AND osc.PROD_ID = pv.PV_ID
		   AND pp.PATCH_ID = ppv.PV_ID
           AND pv.IS_PATCH IS NULL   
           AND osc.OS_ID = :OS_ID 
		   AND ppv.IS_OBSOLETE IS NULL
		MINUS  
        /* OS Patches */
        SELECT pp.PV_ID, pp.PATCH_ID
		  FROM OS_CONTENTS osc,
		  	   PACKAGE_PATCHES pp,
               PACKAGE_VERSIONS pv
		 WHERE osc.PROD_ID = pp.PATCH_ID 
           AND osc.PROD_ID = pv.PV_ID
           AND pv.IS_PATCH IS NOT NULL   
           AND osc.OS_ID = :OS_ID
	   ) pp,
	   PACKAGE_PATCHES op
 WHERE pv.PKG_ID = pkg.PKG_ID
   AND pp.PATCH_ID = PV.PV_ID
   AND pp.PV_ID = op.PV_ID
   AND pp.PATCH_ID = op.PATCH_ID
 ORDER BY op.INSTALL_ORDER 