/* New Patches */
SELECT pkg.pkg_id,
	   pv.PV_ID AS PROD_ID,
	   os.os_id,
	   pkg.pkg_name AS PROD_NAME,
	   nt.NODE_ICON,
	   nn.NODE_NAME ||'&nbsp;/&nbsp;'|| os.OS_NAME ||'&nbsp;/&nbsp;' AS PROD_LOCATION,
	   pv.pkg_version AS PROD_VERSION,
	   pv.dlocked,
	   pv.comments
  FROM    
	   (  
          
		/* All patches from RM for all Products in BOM */
	    SELECT os.OS_ID, pp.PV_ID, pp.PATCH_ID
		  FROM OS_CONTENTS osc,
		  	   PACKAGE_PATCHES pp,
               OPERATING_SYSTEMS os,
               BOM_CONTENTS bc,
               PACKAGE_VERSIONS pv,
               PACKAGE_VERSIONS pppv
		 WHERE osc.PROD_ID = pp.PV_ID 
		   AND osc.OS_ID = os.OS_ID
           AND os.NODE_ID = bc.NODE_ID
           AND bc.BOM_ID = :BOM_ID 
           AND osc.PROD_ID = pv.PV_ID
           AND pv.IS_PATCH IS NULL
           AND pp.PATCH_ID = pppv.PV_ID  
           AND pppv.IS_OBSOLETE IS NULL  
		
           
		MINUS  
        	
        /* Bom Patches */
        SELECT os.OS_ID, pp.PV_ID, pp.PATCH_ID
		  FROM OS_CONTENTS osc,
		  	   PACKAGE_PATCHES pp,
               OPERATING_SYSTEMS os,
               BOM_CONTENTS bc,
               PACKAGE_VERSIONS pv
		 WHERE osc.PROD_ID = pp.PATCH_ID 
		   AND osc.OS_ID = os.OS_ID
           AND os.NODE_ID = bc.NODE_ID
           AND bc.BOM_ID = :BOM_ID 
           AND osc.PROD_ID = pv.PV_ID
           AND pv.IS_PATCH IS NOT NULL  
		   
           
	   ) pp,
       PACKAGES pkg,
       PACKAGE_VERSIONS pv,
  	   OPERATING_SYSTEMS os,
	   NETWORK_NODES nn,
	   NODE_TYPES nt
 WHERE pp.PATCH_ID = pv.PV_ID
   AND pv.pkg_id = pkg.pkg_id
   AND pp.OS_ID = os.OS_ID
   AND os.NODE_ID = nn.NODE_ID
   AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
   AND pv.DLOCKED = 'Y'  
ORDER BY UPPER(pkg.pkg_name), UPPER(pv.pkg_version)