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,
        pv.IS_PATCH                      
  FROM (
  
                /* ADDED PATCHES ONLY */
                SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PK 
                  FROM (
                                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
                                 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      
                           ) 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
                                 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 = :BOM_ID      
                           ) oldbom
                 WHERE newbom.PK = oldbom.PK (+) 
                   AND newbom.PROD_ID = oldbom.PROD_ID (+)
                   AND oldbom.PK IS NULL
                   
                UNION 
                   
                /* ADDED PRODUCTS ONLY */
                SELECT DECODE( oldbom.PROD_ID,
                                           NULL, 'A', 'U' ) AS CHANGE_TYPE, 
                           newbom.PROD_ID, newbom.PK
                  FROM (
                                SELECT osc.PROD_ID,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           pv.PKG_ID,
                                           NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
                                  FROM OS_CONTENTS osc,
                                           OPERATING_SYSTEMS os,
                                           NETWORK_NODES nn,
                                           BOM_CONTENTS bc,
                                           PACKAGE_VERSIONS pv
                                 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.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :COMPARE_BOM_ID      
                           ) newbom,
                           (
                                SELECT osc.PROD_ID,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           pv.PKG_ID,
                                           NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
                                  FROM OS_CONTENTS osc,
                                           OPERATING_SYSTEMS os,
                                           NETWORK_NODES nn,
                                           BOM_CONTENTS bc,
                                           PACKAGE_VERSIONS pv
                                 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.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :BOM_ID      
                           ) oldbom
                 WHERE newbom.PK = oldbom.PK (+) 
                   --AND newbom.PROD_ID = oldbom.PROD_ID (+)
                   AND newbom.PKG_ID  = oldbom.PKG_ID (+)
                   AND newbom.V_EXT  = oldbom.V_EXT (+) 
                   AND ( oldbom.PROD_ID != newbom.PROD_ID  OR oldbom.PROD_ID IS NULL )
                   AND newbom.PROD_ID NOT IN 
                   (
                           SELECT osc.PROD_ID
                           FROM OS_CONTENTS osc,
                                    OPERATING_SYSTEMS os,
                                        NETWORK_NODES nn,
                                        BOM_CONTENTS bc,
                                        PACKAGE_VERSIONS pv
                          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.IS_PATCH IS NULL
                                AND bc.BOM_ID = :BOM_ID 
                  )        

                UNION
                
                /* REMOVED PRODUCTS ONLY*/    
                SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
                  FROM (
                                SELECT osc.PROD_ID,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           pv.PKG_ID,
                                           NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
                                  FROM OS_CONTENTS osc,
                                           OPERATING_SYSTEMS os,
                                           NETWORK_NODES nn,
                                           BOM_CONTENTS bc,
                                           PACKAGE_VERSIONS pv
                                 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.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :COMPARE_BOM_ID      
                           ) newbom,
                           (
                                SELECT osc.PROD_ID,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           pv.PKG_ID,
                                           NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT
                                  FROM OS_CONTENTS osc,
                                           OPERATING_SYSTEMS os,
                                           NETWORK_NODES nn,
                                           BOM_CONTENTS bc,
                                           PACKAGE_VERSIONS pv
                                 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.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :BOM_ID      
                           ) oldbom
                 WHERE newbom.PK (+) = oldbom.PK  
                   --AND newbom.PROD_ID (+) = oldbom.PROD_ID
                   AND newbom.PKG_ID (+) = oldbom.PKG_ID
                   AND newbom.V_EXT (+) = oldbom.V_EXT   
                   AND newbom.PK IS NULL  
                   
                UNION   
                   
                /* REMOVED PATCHES */    
                SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
                  FROM (
                                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
                                 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      
                           ) 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
                                 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 = :BOM_ID      
                           ) oldbom
                 WHERE newbom.PK (+) = oldbom.PK  
                   AND newbom.PROD_ID (+) = oldbom.PROD_ID  
                   AND newbom.PK IS NULL                   
                   
       ) 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
 ORDER BY UPPER(pkg.PKG_NAME)