Subversion Repositories DevTools

/* Bom Removed Product Location */ 
select * from (
    WITH newbom AS (
        -- A set of elements from the NEW BOM that have no corresponding element in the old bom
        -- Element are matched on a Full-Key (FK) made up of the NODE_NAME, OS_NAME and PROD_ID
        -- Note. NODE_ID and OS_ID are not global, but are local to the BOM and are thus
        --       useless for matching purposes.
        SELECT * FROM (
        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,
               nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK,  
               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
                 ) 
                 WHERE FK NOT IN (
                                 SELECT 
                   nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK
                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
                 )           
    ),
    oldbom as (
        -- A set of elements from the OLD BOM that have no corresponding element in the NEW bom
        -- Element are matched on a Full-Key (FK) made up of the NODE_NAME, OS_NAME and PROD_ID
        -- Note. NODE_ID and OS_ID are not global, but are local to the BOM and are thus
        --       useless for matching purposes.
        SELECT * FROM (
        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,
               nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK,  
               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 
           ) 
                 WHERE FK NOT IN (
                                 SELECT 
                   nn.NODE_NAME ||'_'|| os.OS_NAME || '_'  || osc.PROD_ID as FK
                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
                 )
    ),
    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)