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 (
        -- A set of NODE_NAMES and NODE_ID from the new BOM
        -- Will be used to:
        --      Determine if the NODE exists in the new bom
        --      Map NODE_ID from the old bom to the new bom for display
      SELECT nn.NODE_ID, NODE_NAME from bom_contents bc, NETWORK_NODES nn where bc.bom_id = :BOM_ID AND bc.NODE_ID = nn.NODE_ID
    ),
    newOS as (
        -- A set of OS_ID, OS_NAME and NODE_NAME from the new BOM
        -- Will be used to:
        --      Determine if the OS_ID exists in the new bom
        --      Map OS_ID from the old bom to the new bom for display
      SELECT OS_ID, OS_NAME, NODE_NAME from operating_systems os, bom_contents bc, NETWORK_NODES nn where bc.bom_id = :BOM_ID and bc.node_id = os.node_id  AND bc.NODE_ID = nn.NODE_ID
    )
    SELECT 
        oldbom.PROD_ID as PROD_ID,
        newNodes.NODE_ID as NODE_ID,
        oldbom.NODE_ICON,
        oldbom.NODE_NAME,
        newOS.OS_ID as OS_ID,
        oldbom.OS_NAME,
        oldbom.PKG_ID,
        oldbom.PKG_NAME,
        oldbom.PKG_VERSION,
        oldbom.PK,
        newbom.PROD_ID AS NPROD_ID, 
        NVL2(newNodes.NODE_NAME,1,null) as nodeExists, 
        NVL2(newOS.OS_NAME,1,null) as osExists 
    from newbom 
    full outer join oldbom on newbom.PK = oldbom.PK
    left outer join newNodes on oldbom.NODE_NAME = newNodes.NODE_NAME
    left outer join newOS on oldbom.OS_NAME = newOS.OS_NAME and oldbom.NODE_NAME = newOS.NODE_NAME 
    ) where 
      PROD_ID = :PROD_ID
      AND NPROD_ID IS NULL
      ORDER BY UPPER(PK)