Subversion Repositories DevTools

/* Bom Removed Product Location */ 
SELECT newloc.*
  FROM (
  
                SELECT osc.PROD_ID,
                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                           nn.NODE_ID,
                           nn.NODE_NAME,
                           os.OS_ID,
                           os.OS_NAME,
                           pkg.PKG_NAME,
                           pv.PKG_VERSION
                  FROM OS_CONTENTS osc,
                           OPERATING_SYSTEMS os,
                           NETWORK_NODES nn,
                           BOM_CONTENTS bc,
                           PACKAGE_VERSIONS pv,
                           PACKAGES pkg
                 WHERE osc.OS_ID = os.OS_ID
                   AND os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND osc.PROD_ID = pv.PV_ID
                   AND pv.PKG_ID = pkg.PKG_ID
                   AND bc.BOM_ID = :BOM_ID      
                   AND pv.PV_ID = :PROD_ID
                   
           ) newbom,
           (
           
                SELECT osc.PROD_ID,
                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                           nn.NODE_ID,
                           nn.NODE_NAME,
                           os.OS_ID,
                           os.OS_NAME,
                           pkg.PKG_NAME,
                           pv.PKG_VERSION
                  FROM OS_CONTENTS osc,
                           OPERATING_SYSTEMS os,
                           NETWORK_NODES nn,
                           BOM_CONTENTS bc,
                           PACKAGE_VERSIONS pv,
                           PACKAGES pkg
                 WHERE osc.OS_ID = os.OS_ID
                   AND os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND osc.PROD_ID = pv.PV_ID
                   AND pv.PKG_ID = pkg.PKG_ID
                   AND bc.BOM_ID = :COMPARE_BOM_ID
                   AND pv.PV_ID = :PROD_ID
                   
                   
           ) oldbom,
           (
           
                SELECT nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                           nt.NODE_ICON,
                           nn.NODE_ID,
                           nn.NODE_NAME,
                           os.OS_ID,
                           os.OS_NAME
                  FROM OPERATING_SYSTEMS os,
                           NETWORK_NODES nn,
                           NODE_TYPES nt,
                           BOM_CONTENTS bc
                 WHERE os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
                   AND bc.BOM_ID = :BOM_ID      
                   
           ) newloc
 WHERE newbom.PK (+) = oldbom.PK 
   AND newbom.PROD_ID (+) = oldbom.PROD_ID
   AND newloc.PK = oldbom.PK
   AND newbom.PROD_ID IS NULL

   AND newloc.PK NOT IN (
                SELECT nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
                  FROM OS_CONTENTS osc,
                           OPERATING_SYSTEMS os,
                           NETWORK_NODES nn,
                           BOM_CONTENTS bc,
                           PACKAGE_VERSIONS pv,
                           PACKAGES pkg
                 WHERE osc.OS_ID = os.OS_ID
                   AND os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND osc.PROD_ID = pv.PV_ID
                   AND pv.PKG_ID = pkg.PKG_ID
                   AND bc.BOM_ID = :BOM_ID      
                   AND pkg.PKG_NAME = :PKG_NAME

                           MINUS

                SELECT nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
                  FROM OS_CONTENTS osc,
                           OPERATING_SYSTEMS os,
                           NETWORK_NODES nn,
                           BOM_CONTENTS bc,
                           PACKAGE_VERSIONS pv,
                           PACKAGES pkg
                 WHERE osc.OS_ID = os.OS_ID
                   AND os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND osc.PROD_ID = pv.PV_ID
                   AND pv.PKG_ID = pkg.PKG_ID
                   AND bc.BOM_ID = :BOM_ID      
                   AND pkg.PKG_NAME = :PKG_NAME
                   AND pv.IS_PATCH IS NOT NULL
           )
ORDER BY UPPER(oldbom.NODE_NAME), UPPER(oldbom.OS_NAME)