Subversion Repositories DevTools

/* Bom Compare */
SELECT DISTINCT 
           
       pv.PV_ID AS PROD_ID,
           pkg.PKG_NAME,
           pv.PKG_VERSION,
           qry.CHANGE_TYPE,
           pd.IS_REJECTED,
           ( CASE 
             WHEN qry.CHANGE_TYPE = 'A' THEN
                        'LIMG_ADDED'
                 WHEN qry.CHANGE_TYPE = 'U' THEN
                        'LIMG_UPDATED'
                 ELSE
                        'LIMG_REMOVED'
                 END ) AS DIFF_ICON,
                 pkg.PKG_ID,
                 qry.node_id,
                 qry.node_name,
                 qry.node_icon,
                 qry.os_id,
                 qry.os_name,
                 qry.is_obsolete                         
  FROM (
  
                /* ADDED PATCHES ONLY */
                SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PK, 
                newbom.node_id, newbom.node_name, newbom.node_icon, newbom.os_id,
                newbom.os_name, newbom.is_obsolete 
                  FROM (
                                SELECT osc.PROD_ID,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           nn.NODE_ID,
                                           nn.NODE_NAME,
                                           nt.NODE_ICON,
                                                 os.OS_ID,
                                           os.OS_NAME,
                                           pv.IS_OBSOLETE               
                                  FROM OS_CONTENTS osc,
                                           OPERATING_SYSTEMS os,
                                           NETWORK_NODES nn,
                                           BOM_CONTENTS bc,
                                           PACKAGE_VERSIONS pv,
                                           NODE_TYPES nt
                                 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 NOT pv.IS_PATCH IS NULL
                                   AND pv.IS_OBSOLETE IS NULL
                                   AND bc.BOM_ID = :BOM_ID
                                   and nt.NODE_TYPE_ID = nn.NODE_TYPE_ID        
                           ) newbom,
                           (
                                SELECT osc.PROD_ID,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
                                  FROM OS_CONTENTS osc,
                                           OPERATING_SYSTEMS os,
                                           NETWORK_NODES nn,
                                           BOM_CONTENTS bc,
                                           PACKAGE_VERSIONS pv,
                                           NODE_TYPES nt
                                 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 NOT pv.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :COMPARE_BOM_ID      
                                   and nt.NODE_TYPE_ID = nn.NODE_TYPE_ID
                           ) oldbom
                 WHERE newbom.PK = oldbom.PK (+) 
                   AND newbom.PROD_ID = oldbom.PROD_ID (+)
                   AND oldbom.PK IS NULL
                   AND newbom.PROD_ID NOT IN 
                   (
                   
                   SELECT pv.PV_ID AS PROD_ID
           FROM bom_contents bc,
                operating_systems os,
                                network_nodes nn,
                                node_types nt,
                os_contents osc,
                PACKAGES pkg,
                package_versions pv,
                                product_details pd
          WHERE bc.bom_id = :current_bom_id
            AND os.node_id = bc.node_id
            AND osc.os_id = os.os_id
            AND osc.prod_id = pv.pv_id
            AND pkg.pkg_id = pv.pkg_id
                        and pd.PROD_ID(+) = osc.PROD_ID
                        and nn.node_id = os.node_id
                        and nt.NODE_TYPE_ID = nn.NODE_TYPE_ID
                        and pkg.pkg_id = :PKG_ID
                   )
                   
        
                   
       ) qry,
           PACKAGE_VERSIONS pv,
           PACKAGES pkg,
           PRODUCT_DETAILS pd  
 WHERE qry.PROD_ID = pv.PV_ID
   AND pv.PKG_ID = pkg.PKG_ID
   AND pd.PROD_ID (+) = qry.PROD_ID
   AND pkg.PKG_ID = :PKG_ID