Subversion Repositories DevTools

/* Release Diff */
SELECT qry.*
  FROM (
  
                /* Removed Patches*/
                SELECT 'R' AS CHANGE_TYPE,
                           oldbom.PKG_ID,
                           oldbom.PV_ID AS PV_ID_A,
                       oldbom.PKG_NAME AS PKG_NAME_A,
                           oldbom.PKG_VERSION AS PKG_VERSION_A,
                           oldbom.IS_PATCH AS PATCH_A,
                           NULL AS PV_ID_B,
                           NULL AS PKG_NAME_B,
                           NULL AS PKG_VERSION_B,
                           NULL AS PATCH_B
                  FROM (
                                SELECT osc.PROD_ID, pv.IS_PATCH,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           pv.PKG_VERSION, pv.PV_ID, pkg.PKG_NAME, pv.PKG_ID    
                                  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 NOT pv.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :SBOM_B
                                   AND pkg.PKG_ID = pv.PKG_ID                                   
                           ) newbom,
                           (
                                SELECT osc.PROD_ID, pv.IS_PATCH,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           pv.PKG_VERSION, pv.PV_ID, pkg.PKG_NAME, pv.PKG_ID                                       
                                  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 NOT pv.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :SBOM_A      
                                   AND pkg.PKG_ID = pv.PKG_ID

                           ) oldbom
                 WHERE newbom.PK (+) = oldbom.PK  
                   AND newbom.PROD_ID (+) = oldbom.PROD_ID  
                   AND newbom.PK IS NULL  
                   AND :REMOVED IS NULL            
                                
                
                UNION

                /* Removed Products*/
                SELECT 'R' AS CHANGE_TYPE,
                           oldbom.PKG_ID,
                           oldbom.PV_ID AS PV_ID_A,
                       oldbom.PKG_NAME AS PKG_NAME_A,
                           oldbom.PKG_VERSION AS PKG_VERSION_A,
                           oldbom.IS_PATCH AS PATCH_A,
                           NULL AS PV_ID_B,
                           NULL AS PKG_NAME_B,
                           NULL AS PKG_VERSION_B,
                           NULL AS PATCH_B
                         FROM (                    
                                SELECT osc.PROD_ID,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           pv.PKG_ID, pv.PKG_VERSION, pkg.PKG_NAME, pv.PV_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,
                                           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.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :SBOM_B      
                                   AND pkg.PKG_ID = pv.PKG_ID
                           ) newbom,
                           (
                                SELECT osc.PROD_ID, PV.IS_PATCH,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           pv.PKG_ID, pv.PKG_VERSION, pkg.PKG_NAME, pv.PV_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,
                                           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.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :SBOM_A
                                   AND pkg.PKG_ID = pv.PKG_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
                   AND :REMOVED IS NULL                         
                
                UNION
                   
                   
                /* Added Patches */   
                SELECT 'A' AS CHANGE_TYPE,
                           newbom.PKG_ID,
                           NULL AS PV_ID_A,
                           NULL AS PKG_NAME_A,
                           NULL AS PKG_VERSION_A,
                           NULL AS PATCH_A,
                           newbom.PV_ID AS PV_ID_B,
                           newbom.PKG_NAME AS PKG_NAME_B,
                           newbom.PKG_VERSION AS PKG_VERSION_B, 
                           newbom.IS_PATCH AS PATCH_B
                  FROM (
                                SELECT osc.PROD_ID, pv.IS_PATCH,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           pv.PKG_VERSION, pv.PV_ID, pkg.PKG_NAME, pv.PKG_ID
                                  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 NOT pv.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :SBOM_B
                                   AND pkg.PKG_ID = pv.PKG_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 = :SBOM_A
                           ) oldbom
                 WHERE newbom.PK = oldbom.PK (+) 
                   AND newbom.PROD_ID = oldbom.PROD_ID (+)
                   AND oldbom.PK IS NULL           
                   AND :ADDED IS NULL
                   
                UNION

                /* Added Products */   
                SELECT 'A' AS CHANGE_TYPE,
                           newbom.PKG_ID,
                           NULL AS PV_ID_A,
                           NULL AS PKG_NAME_A,
                           NULL AS PKG_VERSION_A,
                           NULL AS PATCH_A,
                           newbom.PV_ID AS PV_ID_B,
                           newbom.PKG_NAME AS PKG_NAME_B,
                           newbom.PKG_VERSION AS PKG_VERSION_B,
                           newbom.IS_PATCH AS PATCH_B
                  FROM (
                                SELECT osc.PROD_ID, pv.IS_PATCH,
                                           nn.NODE_NAME ||'_'|| os.OS_NAME AS PK,
                                           pv.PKG_ID, pv.PKG_VERSION, pkg.PKG_NAME, pv.PV_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,
                                           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.IS_PATCH IS NULL
                                   AND bc.BOM_ID = :SBOM_B
                                   AND pkg.PKG_ID = pv.PKG_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 = :SBOM_A
                           ) 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 IS NULL   
                   AND :ADDED IS NULL               

                   
                UNION
                   
                /* UPDATED Packages */   
                SELECT ( 
                                CASE WHEN ra.PV_ID < rb.PV_ID THEN
                                        'UW'
                                ELSE
                                        'U'
                                END
                                ) AS CHANGE_TYPE,
                           ra.PKG_ID,
                           ra.PV_ID AS PV_ID_A,
                       ra.PKG_NAME AS PKG_NAME_A,
                           ra.PKG_VERSION AS PKG_VERSION_A,
                           ra.IS_PATCH AS PATCH_A,
                           rb.PV_ID AS PV_ID_B,
                           rb.PKG_NAME AS PKG_NAME_B,
                           rb.PKG_VERSION AS PKG_VERSION_B,
                           rb.IS_PATCH AS PATCH_B
                  FROM (
                           SELECT osc.PROD_ID, pv.IS_PATCH, pv.PV_ID, pv.V_EXT, pv.PKG_VERSION, pkg.PKG_NAME, pv.PKG_ID
                           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.IS_PATCH IS NULL
                                AND bc.BOM_ID = :SBOM_A
                                AND pkg.PKG_ID = pv.PKG_ID
                       ) ra,
                           (
                           SELECT osc.PROD_ID, pv.IS_PATCH, pv.PV_ID, pv.V_EXT, pv.PKG_VERSION, pkg.PKG_NAME, pv.PKG_ID
                           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.IS_PATCH IS NULL
                                AND bc.BOM_ID = :SBOM_B 
                                AND pv.PKG_ID = pkg.PKG_ID
                                AND osc.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 = :SBOM_A
                                )
                           ) rb
                 WHERE ra.PKG_ID = rb.PKG_ID
                   AND NVL( ra.V_EXT, '|LINK_A_NULL|') = NVL( rb.V_EXT, '|LINK_A_NULL|')  
                   AND ra.PV_ID != rb.PV_ID 
                   AND :UPDATED IS NULL 
                ) qry
 ORDER BY UPPER(qry.PKG_NAME_A)