Subversion Repositories DevTools

/* Bom Removed Product Location */ 
select * from (
    WITH newbom AS (
        SELECT osc.PROD_ID,
               CASE WHEN pkg.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,
               nt.NODE_ICON,
               nn.NODE_ID,
               nn.NODE_NAME,
               os.OS_ID,
               os.OS_NAME,
               pkg.PKG_ID,
               pkg.PKG_NAME,
               pv.PKG_VERSION
          FROM OS_CONTENTS osc,
               OPERATING_SYSTEMS os,
               NETWORK_NODES nn,
               NODE_TYPES nt,
               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.PKG_ID = pkg.PKG_ID
           AND nn.NODE_TYPE_ID = nt.NODE_TYPE_ID
           AND bc.BOM_ID = :BOM_ID  
    ),
    oldbom as (
        SELECT osc.PROD_ID,
               CASE WHEN pkg.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,
               nt.NODE_ICON,
               nn.NODE_ID,
               nn.NODE_NAME,
               os.OS_ID,
               os.OS_NAME,
               pkg.PKG_ID,
               pkg.PKG_NAME,
               pv.PKG_VERSION
          FROM OS_CONTENTS osc,
               OPERATING_SYSTEMS os,
               NETWORK_NODES nn,
               NODE_TYPES nt,
               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.PKG_ID = pkg.PKG_ID
           AND nn.NODE_TYPE_ID = nt.NODE_TYPE_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 oldbom.*, newbom.PROD_ID AS NPROD_ID, 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 
          OPROD_ID = :PROD_ID
          AND NPROD_ID IS NULL
          ORDER BY UPPER(PK)