Subversion Repositories DevTools

/* Bom Updated 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,
               REPLACE(osc.product_comments,CHR(13)||CHR(10), '-') as COMMENTS
          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,
               nn.NODE_ID,
               nn.NODE_NAME,
               os.OS_ID,
               os.OS_NAME,
               pkg.PKG_ID,
               pkg.PKG_NAME,
               pv.PKG_VERSION,
               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,
               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 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 , oldbom.comments as OCOMMENTS,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 ( PROD_ID != OPROD_ID or COMMENTS != OCOMMENTS)
      ORDER BY UPPER(PK)