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          
  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 = :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 = :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
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 
                   osc.PROD_ID,
                   CASE WHEN PKG_ID = 11151 THEN nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || 'NOTES_' || osc.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(osc.product_comments,CHR(13)||CHR(10), '-') as COMMENTS
                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 as           
               (
                SELECT osc.PROD_ID,
                   CASE WHEN PKG_ID = 11151 THEN nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || 'NOTES_' || osc.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(osc.product_comments,CHR(13)||CHR(10),'-') as COMMENTS
                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    
               )
        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 */    
        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 = :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 = :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)