Subversion Repositories DevTools

/* 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 ||' / '|| os.OS_NAME ||' / ' 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,
           HIDE_PRODUCTS hp
 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' 
   and os.OS_ID = hp.OS_ID
   and pv.PV_ID = hp.PROD_ID 
ORDER BY UPPER(pkg.pkg_name), UPPER(PROD_LOCATION), UPPER(pv.pkg_version)