Subversion Repositories DevTools

/* Bom Compare - Base Configuration */
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          
  FROM (
  
        /* ADDED PATCHES ONLY - BASE CONFIGURATIONS*/
        SELECT 'A' AS CHANGE_TYPE, newbom.PROD_ID, newbom.PK 
          FROM (
                SELECT bec.PROD_ID,
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
                  FROM BASE_ENV_CONTENTS bec,
                       OPERATING_SYSTEMS os,
                       OS_BASE_ENV obe,
                       NETWORK_NODES nn,
                       BOM_CONTENTS bc,
                       PACKAGE_VERSIONS pv
                 WHERE obe.OS_ID = os.OS_ID
                   AND obe.BASE_ENV_ID = bec.BASE_ENV_ID
                   AND os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND bec.PROD_ID = pv.PV_ID
                   AND NOT pv.IS_PATCH IS NULL
                   AND bc.BOM_ID = :BOM_ID  
               ) newbom,
               (
                SELECT bec.PROD_ID,
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
                  FROM BASE_ENV_CONTENTS bec,
                       OPERATING_SYSTEMS os,
                       OS_BASE_ENV obe,
                       NETWORK_NODES nn,
                       BOM_CONTENTS bc,
                       PACKAGE_VERSIONS pv
                 WHERE obe.OS_ID = os.OS_ID
                   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID       
                   AND os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND bec.PROD_ID = pv.PV_ID
                   AND NOT pv.IS_PATCH IS NULL
                   AND bc.BOM_ID = :COMPARE_BOM_ID  
               ) oldbom
         WHERE newbom.PK = oldbom.PK (+) 
           AND newbom.PROD_ID = oldbom.PROD_ID (+)
           AND oldbom.PK IS NULL
           
        UNION 
           
--  Added, Updated and Removed Packages - Base Config
SELECT CASE WHEN PROD_ID is NULL then 'R' WHEN OPROD_ID is NULL THEN 'A' ELSE 'U' END as CHANGE_TYPE, 
       NVL2(prod_id, prod_id, oprod_id) as prod_id , 
       NVL2(pk, pk, opk)  as pk 
       from (
      WITH newbom as
      (
                SELECT bec.PROD_ID,
                       CASE WHEN PKG_ID = 11151 THEN nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || 'NOTES_' || bec.PROD_ID ELSE  nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || pv.PKG_ID ||  NVL( pv.V_EXT, '|.NULL|' ) END as PK,  
                       pv.PKG_ID,
                       NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
                       REPLACE(bec.base_env_comments,CHR(13)||CHR(10), '-') as COMMENTS
                  FROM BASE_ENV_CONTENTS bec,
                       OPERATING_SYSTEMS os,
                       OS_BASE_ENV obe,
                       NETWORK_NODES nn,
                       BOM_CONTENTS bc,
                       PACKAGE_VERSIONS pv
                 WHERE obe.OS_ID = os.OS_ID
                   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID       
                   AND os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND bec.PROD_ID = pv.PV_ID
                   AND pv.IS_PATCH IS NULL
                   AND bc.BOM_ID = :BOM_ID  
               ),
    oldbom as           
               (
                SELECT bec.PROD_ID,
                       CASE WHEN PKG_ID = 11151 THEN nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || 'NOTES_' || bec.PROD_ID ELSE  nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || pv.PKG_ID ||  NVL( pv.V_EXT, '|.NULL|' ) END as PK,  
                       pv.PKG_ID,
                       NVL( pv.V_EXT, '|LINK_A_NULL|' ) AS V_EXT,
                       REPLACE(bec.base_env_comments,CHR(13)||CHR(10), '-') as COMMENTS
                  FROM BASE_ENV_CONTENTS bec,
                       OPERATING_SYSTEMS os,
                       OS_BASE_ENV obe,
                       NETWORK_NODES nn,
                       BOM_CONTENTS bc,
                       PACKAGE_VERSIONS pv
                 WHERE obe.OS_ID = os.OS_ID
                   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
                   AND os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND bec.PROD_ID = pv.PV_ID
                   AND pv.IS_PATCH IS NULL
                   AND bc.BOM_ID = :COMPARE_BOM_ID  
               )
        SELECT newbom.PROD_ID, newbom.PK, newbom.pkg_id, newbom.comments, 
               oldbom.PROD_ID as OPROD_ID, oldbom.PK as OPK, oldbom.pkg_id as OPKG_ID, oldbom.comments as OCOMMENTS
            from newbom 
            full outer join oldbom on newbom.PK = oldbom.PK
      ) where 
            PROD_ID is NULL 
            or OPKG_ID is null
            or PROD_ID != OPROD_ID
            or COMMENTS != OCOMMENTS
           
        UNION   
           
        /* REMOVED PATCHES - BASE CONFIGURATIONS */    
        SELECT 'R' AS CHANGE_TYPE, oldbom.PROD_ID, oldbom.PK
          FROM (
                SELECT bec.PROD_ID,
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
                  FROM BASE_ENV_CONTENTS bec,
                       OPERATING_SYSTEMS os,
                       OS_BASE_ENV obe,
                       NETWORK_NODES nn,
                       BOM_CONTENTS bc,
                       PACKAGE_VERSIONS pv
                 WHERE obe.OS_ID = os.OS_ID
                   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
                   AND os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND bec.PROD_ID = pv.PV_ID
                   AND NOT pv.IS_PATCH IS NULL
                   AND bc.BOM_ID = :BOM_ID  
               ) newbom,
               (
                SELECT bec.PROD_ID,
                       nn.NODE_NAME ||'_'|| os.OS_NAME AS PK
                  FROM BASE_ENV_CONTENTS bec,
                       OPERATING_SYSTEMS os,
                       OS_BASE_ENV obe,
                       NETWORK_NODES nn,
                       BOM_CONTENTS bc,
                       PACKAGE_VERSIONS pv
                 WHERE obe.OS_ID = os.OS_ID
                   AND bec.BASE_ENV_ID = obe.BASE_ENV_ID
                   AND os.NODE_ID = nn.NODE_ID
                   AND bc.NODE_ID = nn.NODE_ID
                   AND bec.PROD_ID = pv.PV_ID
                   AND NOT pv.IS_PATCH IS NULL
                   AND bc.BOM_ID = :COMPARE_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)