Subversion Repositories DevTools

/* Bom Removed Product Location - BASE ENV */ 
select * from (

    WITH newbom as ( 
        SELECT bec.PROD_ID,
                   CASE WHEN pkg.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,  
                   nt.NODE_ICON,
                   nn.NODE_ID,
                   nn.NODE_NAME,
                   os.OS_ID,
                   os.OS_NAME,
                   pkg.PKG_ID,
                   pkg.PKG_NAME,
                   pv.PKG_VERSION,
                   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,
                   NODE_TYPES nt,
                   BOM_CONTENTS bc,
                   PACKAGE_VERSIONS pv,
                   PACKAGES pkg
             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 nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
               AND pv.PKG_ID = pkg.PKG_ID
               AND bc.BOM_ID = :BOM_ID  
           ),
    oldbom as (
       SELECT bec.PROD_ID,
               CASE WHEN pkg.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,  
               nt.NODE_ICON,
               nn.NODE_ID,
               nn.NODE_NAME,
               os.OS_ID,
               os.OS_NAME,
               pkg.PKG_ID,
               pkg.PKG_NAME,
               pv.PKG_VERSION,
               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,
               NODE_TYPES nt,
               BOM_CONTENTS bc,
               PACKAGE_VERSIONS pv,
               PACKAGES pkg
         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 nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
           AND pv.PKG_ID = pkg.PKG_ID
           AND bc.BOM_ID = :COMPARE_BOM_ID      
       ),
    newNodes as (
      SELECT node_id from bom_contents where bom_id = :BOM_ID
    ),
    newOS as (
      SELECT os_id from operating_systems os, bom_contents bc where bc.bom_id = :BOM_ID and bc.node_id = os.node_id
    )
    SELECT newbom.*, oldbom.PROD_ID AS OPROD_ID, newNodes.node_id as nodeExists, newOS.os_id as osExists
    from newbom 
    full outer join oldbom on newbom.PK = oldbom.PK
    left outer join newNodes on newbom.node_id = newNodes.node_id
    left outer join newOS on newbom.os_id = newOS.os_id
) where 
      PROD_ID = :PROD_ID
      AND OPROD_ID IS NULL
      ORDER BY UPPER(PK)